In Oracle Applications, modules such as AP, AR, PO, INV ,PA and WIP have concurrent processes that take the information from database structures in which the business activity is stored and inserts it into the GL_INTERFACE table.
List of some of the transferring concurrent programs are:
Journal categories used while AP to GL Transfer
As you know journal categories classifications used to indicate the purpose or nature of your journal entry.Therefore in Payable the categories which are used are Invoices (also called Purchase Invoices), Payments, and All (both Invoices and Payments) and Reconciled Payments (for Payment Clearing and Payment Unclearing Events). More Important the type of journal category you can select depends on the accounting method that you have selected for your set of books.
Doing a Transfer to GL from AP
After you create accounting entries in Payables, submit the Payables Transfer to General Ledger program which send invoice and payment accounting entries to the general ledger interface which is triggered with Journal Import program.
How and what Payables Populates into GL
Except Oracle Fixed Assets module, most of subledger transfer program moved via GL_Interface table. Once you submit the payables transfer to General Ledger program , the data get populates into GL Interface table with accounting information for Payables transactions.
If you use Oracle General Ledger, then Journal Import uses the data in the GL Interface to create journal entries and populates the General Ledger GL_JE_BATCHES, GL_JE_HEADERS,GL_JE_LINES, and GL_IMPORT_REFERENCES tables.
When the Payables Transfer to General Ledger program transfers accounting information to the GL Interface, it always populates the following columns:
- GL_SL_LINK_ID: value is a unique, sequential number
- GL_SL_LINK_TABLE: value is APECL for Payables actuals, and APENCL for Payables encumbrances.
When you submit the Payables Transfer to General Ledger program you specify whether you want to transfer accounting information in summary or detail, and you specify the journal category. The parameters you select affect what is transferred to GL Interface columns REFERENCE21–REFERENCE30.
When you transfer in Details
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, RealizedGain/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
Query for Subledger Transfer to GL
If you want to get details of payments and invoices on the journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code.
select glcc.segment1 "Company"
, gjjlv.period_name "PERIOD"
, gjb.name "JOURNAL BATCH NAME"
, gjjlv.header_name "JOURNAL SOURCE"
, gjjlv.line_reference_1 "SUPPLIER NAME"
, gjjlv.currency_code "CURRENCY"
, invoice_type_lookup_code "TRANSACTION TYPE"
, gjjlv.line_reference_5 "TRANSACTION NUMBER"
, aia.invoice_date "TRANSACTION DATE"
-- , gjjlv.je_source "SOURCE"
, gjjlv.line_entered_dr "ENTERED DEBIT"
, gjjlv.line_entered_cr "ENTERED CREDIT"
, gjjlv.line_accounted_dr "ACCOUNTED_DEBIT"
, gjjlv.line_accounted_cr "ACCOUNTED_CREDIT"
,glcc.concatenated_segments "CHARGE ACCOUNT"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gjl
, ap_ae_lines_all aala
, gl_je_headers gjh
, gl_je_batches gjb
, ap_invoices_all aia
, apps.gl_code_combinations_KFV glcc
, po_vendors pv
WHERE gjl.gl_sl_link_table = 'APECL'
AND gjl.period_name ='NOV-2008'—period
AND gjjlv.currency_code = --currency code
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.period_name = gjl.period_name
AND gjh.set_of_books_id = gjl.set_of_books_id
AND glcc.code_combination_id = gjl.code_combination_id
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gjl.je_line_num
AND gjh.period_name = gjjlv.period_name
AND gjh.set_of_books_id = gjjlv.set_of_books_id
AND glcc.code_combination_id = gjjlv.line_code_combination_id
AND aala.code_combination_id = gjl.code_combination_id
AND aala.gl_sl_link_id = gjl.gl_sl_link_id
AND aala.reference5 = aia.invoice_num
AND gjh.set_of_books_id = aia.set_of_books_id
AND pv.vendor_id = aia.vendor_id
AND gjjlv.line_reference_1 = pv.vendor_name
ORDER BY aia.invoice
Troubleshooting AP To GL Program
Issue 1 :Why my payments and/or invoices that are not posting to GL and you believe they should post.
Resolution:1) Invoice should not be Hold :Make sure invoices are ready to post. An invoice must be approved and/or have no posting holds in order to be selected for posting. To ensure that your invoices are ready to post, you should run autoapproval and review the hold reports.
- Invoice Hold Report - displays all hold invoices.
- Posting Hold Report - only displays invoices with holds that prevent posting.
- Matching Hold Report - only displays invoices with matching holds.
2) Ensure payments are made. You must insure that all your payment batches have been completed in all sense , they should be either confirmed or canceled.
Make sure period will not close if there are payment batches in a status other than confirmed or canceled.
Issue 2: Sometime transfer AP Transfer to GL takes Longer time?
Resolution :
This should not be a case, if you are experincing the program takes longer check any locks from database side.
Issue 3: Is there any Pre-requisite for AP to Gl transfer?
Resolution:
Yes, Here is the Sequence to follow:
- Run Invoice Validation process
- then run Payables Accounting Process and you have the option of transferring to GL
You may also submit payables transfers to GL later.
Issue 4: While Transfer why my DFF not populated?
Resolution:
The DFF in the AP Invoice will not get transferred to the GL and this is an Intended functionality till 11i.
A note on Payables Transfer to General Ledger Report
This report has a summary section which gives totals of the accounting entries transferred to the gl interface table.
It has two sections which show exceptions:
- one section shows accounting entries that could not be transferred because they were in an error status
- second sectiom shows transferred due to a discrepancy between the accounted account and the account in the general ledger.
