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.

Oracle Payables Overview

Payables architecture in oracle is concerned with all the activities related to all the liabilities of the business. It is concerned with all the expenses/payments that the business incurs in course of Operation. Payables keeps a track of the the payments to be made and also the related entities involved in the whole cycle

Oracle Payables are used for 5 main functions

1.       Supplier Entry

2.       Invoice Entry/Import

3.       Invoice Validation

4.       Invoice Payment

5.       Invoice and payment Accounting

To enter and pay invoices, first enter suppliers and supplier sites.  Payables processes many different invoice types including standard invoices, credit memos, debit memos and expense reports.  After invoices are entered and validated, they can be paid.  After invoices are validated or paid, subledger accounting entries are generated in Subledger Accounting and those entries are transferred to General Ledger.

Supplier Entry

In order to proceed in payables suppliers need to be setup in the system. Users need to enter

·        Enter suppliers, their addresses, and business information such as payment terms, payment              method, and supplier bank account information.

·         Enter supplier sites and related data, which defaults to invoices entered for that site.

·         Review supplier information online, such as supplier balance.

·         Merge duplicate suppliers

Major Tables Affected

·         Po_vendors_all

·         Po_vendor_sites

Invoice Entry

Once the Suppliers have been entered in the system the user can enter the invoices and related information

There are different ways of entering invoices in the system

Manual Entry: Manually enter invoices  from the invoices screen. When entering invoices manuall users can choose to create invoice batches or create individual invoices.

Import: System can use the Payables import program to import invoices into the system.This can be done to electronically invoices sent by the suppliers.

Automatically generated.  Oracle Payables automatically generates some invoice types including:  withholding tax invoices to pay tax authorities, interest invoices, and payment on receipt invoices. 

Recurring invoices. You can set up Oracle Payables to generate regularly scheduled invoices such as rent.

Major Tables Affected

·         Ap_invoices

·         Ap_invoice_distributions

·         Ap_holds

·         Additional tables affected for invoice  import:

o   Ap_invoices_interface

o   Ap_invoice_lines_interface

Invoice Matching

Oracle provides an option of Invoice Matching. Invoice Matching is a process in which users/business has an option of matching the invoice details against the purchase order or Receipt. In case the invoices are created via  the invoice import program business might want to perform the matching of the invoice against the PO or PO Receipt before proceeding further in invoice processing. In invoice matching the details of the invoice are matched against the PO or PO receipt to check for data correctness.

During invoice matching accounted stored in purchase order/purchase order receipt are copied to the invoice

Invoice Validation

Once the invoice is entered the data validity needs to be performed by Oracle by Applying the standard rules to confirm that all the details are valid and in sync with the data present in the system.Oracle has defined a standard invoice validation Program to Validate all the invoices. This program when run will validate the invoices and in case any validation fails the invoice is put in hold with the respective  hold details.

When an invoice is placed on hold , we need to release them. Some holds might be released manuall but for some the users need to perform corrective action then only the holds will be released. These checks help  oracle maintain the integrity of the system

Invoice Payment

Once invoices are validated, they can be paid.  Payables provides the information that you need to make effective payment decisions, stay in control of payments to suppliers and employees, and keep your accounting records up-to-date so that you always know your cash position.  Payables handles every form of payment, including checks, manual payments, wire transfers, EDI payments, bank drafts, and electronic funds transfers. Payables integrates with Oracle Payments to define payment methods, and integrates with Oracle Cash Management to support automatic or manual reconciliation of your payments with bank statements sent by the bank.  With Payables you can:

·         Ensure duplicate invoice payments never occur

·         Pay only invoices that are due, and automatically take the maximum discount available

·         Select invoices for payment using a wide variety of criteria

·         Record stop payments

·         Record void payments

·         Review information on line on the status of every payment

·         Process positive pay

Invoice and Payment Accounting

Once the invoice has been created and validated or payments have been done for the invoice the data needs to be transferred to GL for reporting and reconciliation purposes. When transferring the data to GL 2 activities need to be performed

·         Create Accounting : when an invoice Is created a business event occurs which will affect the business financially, thus which type of financial activity has taken place and how will it affect the business needs to be ascertained. The Create Accounting action will take care of this

When Create Accounting is performed data is populated into the tables :

o   ap_ae_headers

o    ap_ae_lines

·         Run Payables Transfer to General Ledger Program : This program is run for a particular set of books and when the program is run the data from pyables is interface to General Ledger. When running the program there is a parameter : ‘Submit Journal Import’ If the value of this parameter is passed to YES then oracle will run the journal import for the records transferred to GL and also create journals for them if the parameter = ‘NO’ then Oracle will only transfer the data to GL and not submit journal import, thuse data will be present in GL_INTERFACE

Tables Affected

·         ‘Submit Journal Import’ = ‘YES’ (Journal entries will be created for the transferred data)

o   Gl_je_headers

o   Gl_je_lines

·         ‘Submit Journal Import’ = ‘NO (Journal entries will not be created for the transferred data)

o   Gl_interface