Inventory Transfer to GL

As a part of reconciliation with GL the inventory details are transferred to General Ledger. Oracle has provided the users with a standard program: Transfer Transactions to GL which can be used to transfer the inventory information to general ledger.

On running this program all the accounting information in the INV module is transferred to General Ledger. The Program picks up all the records from the table mtl_transaction_accounts having gl_batch_id = -1 for the entered program parameters and transfers them to gl interface.

The Program is generally run as a part of period end process when account reconciliations need to be done.

Like the other modules when the request is run all the accounting information is transferred to GL Interface. Users can subsequently run the Journal Import and Journal Posting Programs to create journals and post them into Ledger.

When submitting the Inventory Transfer to GL Program the user has an option of transferring the information at a summary level or detailed level

Summary: The accounting information of the relevant transactions are summarized and                summary information is transferred, as summary information is being transferred the process is faster and takes less time.

Detail: In case of detail a detailed information is transferred. Detailed accounting records are created for each transaction of Inventory. As the mode since , since detailed transactions are created for individual Inventory transactions the process is more time consuming and thus processing times are higher.

If none is selected, no transfer of accounting information to GL is done for this organization.

The Program is generally run as a part of period closing , however we can perform the general ledger transfer at any time during an open period--not just at period close. The transfer loads summary or detail accounting activity for any open period into the general ledger interface, including both inventory and work in process entries. When more than one period is open, the transfer selects transactions from the first open period up to the entered transfer date, and passes the correct accounting date and financial information into the general ledger interface.

As we have mentioned the Program picks up the relevant records from the table mtl_transaction accounts having a gl_batch_id = -1, When the transactions are passed to the gl_interface it is at this time that the batch is given it’s proper batch number. This is used to overwrite the gl_batch_id and update it.

AP to GL Transfer (Payables Transfer to General Ledger )

Accounts Payable Module in Oracle is concerned with the creation of Payables Invoices, Making payments, bill payments, and expenses and performing other financial activities related to all the expenditures and liabilities of an organization. This is a sub-ledger module in Oracle and all the data recorded in this module need to be transferred into General Ledger so that these are reflected in the financial statements of the organization. Any data which is not transferred to General Ledger will not be reflected in the financial statements. Oracle has provided a standard Program: Payables Transfer to General Ledger to Transfer the Data from AP to GL.

This Program when run in Oracle will transfer all the eligible records from AP to General Ledger. On the Invoices front once the invoices have been created in the system, Validated and Accounted for we run the Payables transfer to General Ledger Program to transfer the invoice details to General Ledger.

When the payable accounting entries are created, then run the program called 'Payables Transfer to GL' Program. Which sends the invoice entries and payable entries to GL  interface .Then submit a request called Journal import to import journal entries to GL

After transferring the data to General Ledger Payables retains the accounting entries, so you can continue to review them in Payables. Also, after you post journal entries in Oracle General Ledger, you can drill down to the related accounting entries or transactions in Payables.

Before Submitting the Payables transfer to General Ledger Program the user needs to ensure that certain prerequisites are met:

1.       The Period of which data is being transferred to general ledger must be open

2.       The Invoices which are to be transferred to General Ledger must be validated and accounted.

Payables Transfer to General Ledger Program takes a number of parameters as input. The values of these parameters determine what activities will be performed by the program.

The Parameters taken in by the Program are discussed below:

1.       Set of Books Name: Specifies the set of books for which the user wishes to transfer the data to general ledger. If you do not define a secondary set of books in the Payables Options window, Payables automatically enters the name of your primary set of books and you cannot change the default. If we have defined a primary and reporting set of books then payables will display Both as the default meaning that an entry will be created for both the set of books , In case the user wishes to perform transfer for a given set of books he needs to change the value manually.

2.       Transfer Reporting books : When transferring the data should the data be posted in the reporting set of books also

3.       Batch Name: When the data is transferred to General Ledger Oracle will create a journal batch for these records. The Batch name specified here will be used when creating a batch in general ledger so that the user can easily identify their payables batch in general ledger. If no batch name is specified General Ledger uses the journal source, request id , the Actual Flag (A, B, or E for actual, budget, or encumbrance), and Group ID to automatically create a name for your journal entry batch . In case the user supplies a batch name this batch name will be appended to the oracle generated batch name.

4.       From Date: Payables will transfer all the eligible records having GL date on or after this date.

5.       To Date: Payables will transfer all the eligible records having GL date on or before this date.

6.       Journal Entry Category: We can select from the LOV

a.       Invoices: This will transfer only the eligible invoices to general ledger

b.      Payments: Payables transfers journal entry information for the payments.

c.       All: Payables will transfer the journal information of both , Invoices and Payments .

7.       Validate Accounts: When transferring the records from Payables to General Ledger if the parameter value is ‘YES’ Oracle will check for the validity of the accounts in general ledger and ensure that these accounts are active in GL , If the user selects a value ‘NO’ , the data will be simply transferred to General Ledger and the account validation will be done In the General Ledger Module later

8.       Transfer to GL Interface: This parameter is used to specify whether we wish to transfer summarized or detailed accounting entry lines. Even if the user chooses summary he still has the option to drill down from General Ledger to individual transactions in Payables.

a.       In Detail: Do not summarize the entries. Transfer one accounting entry for each accounting entry.

b.      Summarize by Accounting Date: Summarize the accounting lines by account and date.

c.       Summarize by Accounting Period: Summarize the accounting lines by account and accounting              period.

9.       Submit Journal Import: This parameter specifies if Oracle should run Journal Import after Transferring data into the GL Interface.

a.       YES : Once the data has been transferred to GL Interface , Oracle will also run the Journal Import to create Journals for the transferred records

b.      NO  : Do not submit the Journal Import , the data will be available in the GL Interface for user reference.

Even After transferring the data to General Ledger the AR module will still retain all the entries that have been transferred and can be used by for future reference.

How to Identify the Invoices that are transferred to GL Interface ?

 Oracle has provided us with 2 columns : GL_SL_LINK_ID and GL_SL_LINK_TABLE

GL_SL_LINK_TABLE: value is APECL for Payables actuals, and APENCL for Payables encumbrances.

GL_SL_LINK_ID: value is a unique, sequential number

User can use this combination to fetch the details of the transaction which has been transferred to GL

Below is list of the reference columns from Oracle site which get populated when the Payables data is transferred to GL Interface.

When you do a transfer in details , these information get populated in GL_Interface.

Purchase Invoices: Records for the Purchase Invoices journal category debit the Expense account (including exchange rate variance and invoice price variance accounting entries), and credit the Liability account.

·         Reference21: supplier name

·         Reference22: invoice ID

·         Reference23: distribution line number

·         Reference25: invoice number

·         Reference26: AP Invoices

·         Reference27: set of books ID

·         Reference30: type of account charged: Liability or Expense

Payments: Records for the Payments journal category debit the Liability account, credit the Cash account, and are charged to the Discount, Realized Gain/Loss, Future Payment, and Rounding accounts.

USER_JE_CATEGORY_NAME: Payments
USER_JE_SOURCE_NAME : Payables

·         Reference21: supplier name

·         Reference22: invoice ID

·         Reference23: check ID

·         Reference24: check number

·         Reference25: Paid invoice number

·         Reference26: AP Payments

·         Reference27: set of books ID

·         Reference28: invoice distribution line number

·         Reference29: invoice payment ID

·         Reference30: account charged: Liability, Cash, Discount, Exchange Gain, Exchange Loss, Future Pay, or Rounding

Reconciled Payments: Records for the Reconciled Payments journal category are charged to the Cash Clearing and Reconciliation Accounting accounts. The Payables Transfer to General Ledger program populates GL Interface reference columns with reconciled payment information as follows:

·         Reference21: supplier name

·         Reference23: check ID

·         Reference24: check number

·         Reference26: AP Reconciled Payments

·         Reference27: set of books ID

·         Reference30: account charged: Cash, Cash Clearing, Charges, Errors, Exchange Gain, Exchange Loss, or Rounding

AP to GL transfer process is required for transferring all the AP data to GL so that financial reports can be prepared for all the expected revenue, receivables for the business.

AR to GL Transfer (General Ledger Transfer Program)

AR to GL Transfer is a process which is concerned with the transfer to data from A to the GL Module.

The General Ledger Transfer Program is a standard spawned program provided by Oracle to transfer the data from AR to GL. The program picks up all the eligible records and transfers them to the gl_interface. All the transactions that have been accounted and are complete, and have not been transferred to GL will be picked up.

When submitting the program to transfer the records to GL form AR user can determine the transactions to be transferred by specifying a General Ledger Date Range when submitting the Interface Program. The user specifies a GL date that Receivables will use to select the transactions for posting.

When you run General Ledger Interface, Receivables transfers transaction data into the GL_INTERFACE table and generates the Posting Execution Report. This report provides the list of transactions make up the entries to the general ledger.

Note: If you are using the Oracle Applications Multiple Reporting Currencies (MRC) feature, you must run the General Ledger Interface program for your primary set of books and each of your reporting set of books

The General Ledger Transfer Program consists of a number of Parameters some of which are discussed below:

Post in Summary:

When the user runs the General Ledger Transfer Program he has the option of choosing a parameter to perform a Summarized or Detailed Transfer

Choose a Posting Detail of Summary or Detail.

This controls how Receivables creates journal entries for your transactions in the interface table.

• If you select Detail(No), then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission.

• If you select Summary(Yes), then the program creates one journal entry for each general ledger account.

Run Journal Import: If the user selects a value = ‘YES’ Oracle will submit the Journal Import process and create Journals for the transferred records. If the user selects the parameter to be ‘NO’ Oracle will not submit the Journal Import Program and the user will be able to see the data in the GL Interface.   

When Submitting the General Ledger Transfer Program Oracle will also check if all the submitted code combinations are valid or not. In case there are any code combinations which are inactive or invalid in GL the Program will report it to the user and process the remaining records.

How to Identify if a record is transferred to GL: Once a transaction has been transferred to General Ledger the user must be abe to identify that the transactions have been transferred to GL. In order to enable the user to find out these details Oracle has specified a field: POSTING_CONTROL_ID in the table : ra_cust_trx_line_gl_dist , If the value of the columns is -3 it means that the record has not been transferred to GL.

RA_CUST_TRX_LINE_GL_DIST_ALL: POSTING_CONTROL_ID NOT NULL Receivables posting batch identifier:

–1 means the record was posted by the old posting program (ARXGLP);

–2 means it was posted from old Release 8 Revenue Accounting;

–3 means it was not posted;

–4 means it was posted by the Release 9 RAPOST program.

How to identify a transaction which has been transferred to General Ledger?

The import puts rows into gl_import references REFERENCE21 to REFERENCE30

If the journal is imported in detail these are added to REFERENCE1 to REFERENCE10 in GL_JE_LINES. In summary mode the references map from there to GL_IMPORT_REFERENCES as there is no 1 to 1 relationship between the lines in gl and there source references.

Below is list from Oracle on which reference fields are populated with what values depending on the transaction category.

If you are customizing it should be note that these are subject to change without warning or notice.

USER_JE_CATEGORY_NAME = Adjustment

Reference21 : POSTING_CONTROL_ID
Reference22 : AR_ADJUSTMENTS.ADJUSTMENT_ID
Reference23 : AR_DISTRIBUTIONS.LINE_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : AR_ADJUSTMENTS.ADJUSTMENT_NUMBER
Reference26 : RA_CUST_TRX_TYPES.TYPE
Reference27 : RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID
Reference28 : 'ADJ'
Reference29 : 'ADJ_'||AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_ADJUSTMENTS'

USER_JE_CATEGORY_NAME = Sales Invoice

Reference21 : POSTING_CONTROL_ID
Reference22 : RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
Reference23 : RA_CUST_TRX_LINE_GL_DIST.CUST_TRX_LINE_GL_DIST_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER
Reference26 : 'CUSTOMER'
Reference27 : RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID
Reference28 : 'INV'
Reference29 : 'INV_'||RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS
Reference30 : 'RA_CUST_TRX_LINE_GL_DIST'

USER_JE_CATEGORY_NAME = Credit Memo

Reference21 : POSTING_CONTROL_ID
Reference22 : RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
Reference23 : RA_CUST_TRX_LINE_GL_DIST.CUST_TRX_LINE_GL_DIST_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER
Reference26 : 'CUSTOMER'
Reference27 : RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID
Reference28 : 'CM'
Reference29 : 'CM_'||RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS
Reference30 : 'RA_CUST_TRX_LINE_GL_DIST'

USER_JE_CATEGORY_NAME = Debit Memo

Reference21 : POSTING_CONTROL_ID
Reference22 : RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
Reference23 : RA_CUST_TRX_LINE_GL_DIST.CUST_TRX_LINE_GL_DIST_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER
Reference26 : 'CUSTOMER'
Reference27 : RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID
Reference28 : 'DM'
Reference29 : 'DM_'||RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS
Reference30 : 'RA_CUST_TRX_LINE_GL_DIST'

USER_JE_CATEGORY_NAME = Chargeback

Reference21 : POSTING_CONTROL_ID
Reference22 : RA_CUSTOMER_TRX.CUSTOMER_TRX_ID
Reference23 : RA_CUST_TRX_LINE_GL_DIST.CUST_TRX_LINE_GL_DIST_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER
Reference26 : 'CUSTOMER'
Reference27 : RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID
Reference28 : 'CB'
Reference29 : 'CB_'||RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS
Reference30 : 'RA_CUST_TRX_LINE_GL_DIST'

USER_JE_CATEGORY_NAME = Receipts

Receipts Journal
Reference21 : POSTING_CONTROL_ID
Reference22 : AR_CASH_RECEIPTS.CASH_RECEIPT_ID||'C'
||AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID
Reference23 : AR_DISTRIBUTIONS.LINE_ID
Reference24 : AR_CASH_RECEIPTS.RECEIPT_NUMBER
Reference25 : Null
Reference26 : Null
Reference27 : AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER
Reference28 : 'TRADE'
Reference29 : 'TRADE_'|| AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_CASH_RECEIPT_HISTORY'

Application Journal
Reference21 : POSTING_CONTROL_ID
Reference22 : AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID||'C'
||AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID
Reference23 : AR_DISTRIBUTIONS.LINE_ID
Reference24 : AR_CASH_RECEIPTS.RECIPT_NUMBER
Reference25 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference26 : RA_CUST_TRX_TYPES.TYPE
Reference27 : AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER
Reference28 : 'TRADE' or 'CCURR'
Reference29 : 'TRADE_' or 'CCURR_'|| AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_RECEIVABLE_APPLICATIONS'

USER_JE_CATEGORY_NAME = Misc Receipts

Header
Reference21 : POSTING_CONTROL_ID
Reference22 : AR_CASH_RECEIPTS.CASH_RECEIPT_ID
Reference23 : AR_DISTRIBUTIONS.LINE_ID
Reference24 : AR_CASH_RECEIPTS.RECEIPT_NUMBER
Reference25 : AR_CASH_RECEIPT_HISTORY.CASH_RECEIPT_HISTORY_ID
Reference26 : Null
Reference27 : AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER
Reference28 : 'MISC'
Reference29 : 'MISC_' || AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_CASH_RECEIPT_HISTORY'

Distributions
Reference21 : POSTING_CONTROL_ID
Reference22 : AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID
Reference23 : AR_DISTRIBUTIONS_ALL.LINE_ID
Reference24 : AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER
Reference25 : AR_MISC_CASH_DISTRIBUTIONS_ALL.MISC_CASH_DISTRIBUTION_ID
Reference26 : null
Reference27 : null
Reference28 : 'MISC'
Reference29 : 'MISC_'AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_MISC_CASH_DISTRIBUTIONS'

USER_JE_CATEGORY_NAME = Credit Memo Application

Reference21 : POSTING_CONTROL_ID
Reference22 : AR_RECEIVABLES_APPLICATIONS.RECEIVABLE_APPLICATION_ID
Reference23 : AR_DISTRIBUTIONS_ALL.LINE_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference26 : RA_CUST_TRX_TYPES.TYPE
Reference27 : RA_CUSTOMER_TRX.BILL_TO_CUSTOMER_ID
Reference28 : 'CMAPP'
Reference29 : 'CMAPP_'||AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_RECEIVABLE_APPLICATIONS'

USER_JE_CATEGORY_NAME = Bills Receivable

Reference21 : POSTING_CONTROL_ID
Reference22 : AR_TRANSACTION_HISTORY.TRANSACTION_HISTORY_ID
Reference23 : AR_DISTRIBUTIONS.LINE_ID
Reference24 : RA_CUSTOMER_TRX.TRX_NUMBER
Reference25 : AR_TRANSACTION_HISTORY.CUSTOMER_TRX_ID
Reference26 : RA_CUST_TRX_TYPES.TYPE
Reference27 : RA_CUSTOMER_TRX.DRAWEE_ID
Reference28 : 'BR'
Reference29 : 'BR_'||AR_DISTRIBUTIONS.SOURCE_TYPE
Reference30 : 'AR_TRANSACTION_HISTORY'

Upon running the Program the data will be interfaced to the GL Module in the table GL Interface where the user can identify any of the transferred record using the above references.

Even After transferring the data to General Ledger the AR module will still retain all the entries that have been transferred and can be used by for future reference

AR to GL transfer process is required for transferring all the AR data to GL so that financial reports can be prepared for all the expected revenue, receivables for the business.

Overveiw of Oracle Receivables

Oracle Receivables is the module which is used to perform most of  day-to-day Accounts Receivable operations.
The transactions workbench is used to process invoices, debit memos, credit memos, on-account credits, chargebacks, and adjustments.
The Receipts Workbench to perform receipt-related tasks.
The Bills Receivable Workbench lets you create, update, remit, and manage your bills receivable.

These work benches provide  users the option of querying the information in flexible ways.

Receipts Workbench

The Receipts Workbench is used to create receipt batches and enter, apply, reverse, reapply, and delete individual receipts. Users can enter receipts manually, import them using AutoLockbox, or create them automatically. Users can also use this workbench to clear or risk eliminate factored receipts, remit automatic receipts, create chargebacks and adjustments, and submit Post QuickCash to automatically update your customer's account balance.

A receipt is a document which species inflow of cash into the organization. Money coming into the organization are recorded by means of receipts. Once the receipts are created users have the option of applying the receipt to one or multiple invoices.

As mentioned earlier receipts can either be entered manually from the receipt creation screen , created via programs or also created using the autolock box process.

Transactions Workbench

Transactions workbench is the set of screens which allow the users to enter, query the different receivable transactions into the system.
The  Different types of transactions i.e. Invoices , Debit memos,Credit memos etc are created using  the transactions workbench.
The workbench provides the option of entering the line details for the transactions, the distributions and also viewing the same for history transactions.

Bills Receivable Workbench:

 The Bills Receivable Workbench is used to create, update, remit, and manage bills receivable. Users can create a bill receivable and assign transactions to the bill either manually or automatically. They can also use this workbench to review bills receivable, update the status of a bill, and create and maintain bills receivable remittance batches. The Bills Receivable Workbench also manages creating and applying receipts, and eliminating risk on remitted bills receivable.
Users can also exchange a transaction for a bill receivable in the Transactions Workbench, and use the Receipts Workbench to reverse or unapply receipts applied to bills receivable.

In the coming posts we will discuss in detail about the different workbenches and their uses.