Receivable to GL

AR to GL Transfer
PURPOSE
The purpose of this document is to represent functional as well as Techanical overview of AR to GL Transfer process. The information presented here specifically techancial details are complementary to issues in PTC

SCOPE & APPLICATION
This bulletin is directed to Oracle Recievables functional/Techanical people who are trying to import the records from AR to GL.
Oracle Financials Accounts Receivables Transfer Process
The AR Transfer process is a SRS based Concurrent Process which can be used for transfer process from AR to GL . This Program knows as ARGLTP.

Posting Journals to GL
Your Navigation for transfer process would be
Interfaces> General ledger(11i)
You can run the General Ledger Interface program to transfer Receivables Transaction Accounting Distributions to the GL Interface table (GL_INTERFACE) and create either Detailed Journal batches or summary Journal batches . Receivables lets you create Unposted Journal Entries in GL when you run General Ledger Interface.
Here is five simple step to complete the process:
Navigate to the Run General Ledger Interface window.
Choose the Posting Detail or Summary. Chose Detail in the Posting Detail field. If you transfer transactions in detail, the General Ledger Journal Import Program creates at least one journal entry for each transaction in your posting submission. (If you transfer in summary, it creates one journal entry for each general ledger account)
Enter the GL Posted Date for this submission. The default is the current date, but you can change it. Receivables updates all of the posted transactions that you transfer to the general ledger interface area with the GL posted date you enter.
Enter the range of GL Dates for your submission. The dates must be within both an open receivables period and an open or future General Ledger period. When you enter a start date, the default GL end date is the last day of the period that you entered for the GL start date.
Receivables creates the Posting Execution Report. Use this report to see a summary of transactions that are imported into the GL_INTERFACE table. Transactions that failed validation appear in the Unposted Items Report.
A note on Posting Execution Report
You can use this report to view a Summary of all Transactions by category and currency that make up your Entries to general ledger.
The good is that AR automatically generates this report when you run General Ledger Interface.
The sum of the entries in the General Ledger Journal Report is equal to the sum of all of the categories of transactions that the Posting Execution Report includes for the same period. The report tells you if posting discovered Errors in your Journals or in your Journal lines.
What Category we have in GL
Trade Receipts
Misc Receipts
Adjustments
Sales Invoices
Debit Memos
Chargebacks
Credit Memos
CM Applications
Sales Invoices, Debit Memos ,Chargeback’s and Credit Memos are tracked back with Customer Num








CM Applications are tracked backed with Inv Num
Trade Receipts or Misc Receipt are tracked back with Receipt Number.
Connecting World :Link between GL to AR
When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns that is then pushed the detailes in GL_JE_LINES table. Here are the details for Refrence columns.
Adjustments
REFERENCE21 :posting_control_id
REFERENCE22 :adjustment_id
REFERENCE23 :line_id
REFERENCE24 :trx_number
REFERENCE25 :adjustment_number
REFERENCE26 :cust_trx_type
REFERENCE27 :bill_to_customer_id
REFERENCE28 :ADJ
REFERENCE29 :source_type prefixed by 'ADJ'
REFERENCE30 :AR_ADJUSTMENTS
Transactions
REFERENCE21 :posting_control_id
REFERENCE22 :customer_trx_id
REFERENCE23 :cust_trx_line_gl_dist_id
REFERENCE24 :trx_number
REFERENCE25 :cust.account_number
REFERENCE26 :CUSTOMER
REFERENCE27 :bill_to_customer_id
REFERENCE28 :type(CM/DM/CB/INV)
REFERENCE29 :typeaccount_class
REFERENCE30 :RA_CUST_TRX_LINE_GL_DIST
Applications
REFERENCE21 :posting_control_id
REFERENCE22 :cash_receipt_idreceivable_application_id for CASH /receivable_application_id for CM
REFERENCE23 :line_id
REFERENCE24 :receipt_number for CASH / trx_number for CM
REFERENCE25 :trx_number if status = ¿APP¿ / NULL for unapplied records
REFERENCE26 :cust_trx_type
REFERENCE27 :pay_from_customer for CASH / bill_to_customer_id for CM
REFERENCE28 :application_type (TRADE or CCURR for CASH / CMAPP for CM)
REFERENCE29 :application_typesource_type
REFERENCE30 :AR_RECEIVABLE_APPLICATIONS
Bills Receivable
REFERENCE21 :posting_control_id
REFERENCE22 :transaction_history_id
REFERENCE23 :line_id
REFERENCE24 :trx_number
REFERENCE25 :customer_Trx_id
REFERENCE26 :cust_trx_type
REFERENCE27 :drawee_id
REFERENCE28 :cust_trx_type
REFERENCE29 :BR_source_type
REFERENCE30 :AR_TRANSACTION_HISTORY
Key Tables
In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.
ra_customer_trx_all: Transactions accounting
ra_cust_trx_line_gl_dist_all: Transactions accounting
ar_adjustments_all:Adjustments accounting
ar_distributions_all:Adjustments accounting
ar_cash_receipt_history_all:Receitps accounting
ar_distributions_all: Receitps accounting
ar_receivable_applications_all: Receipt applications accounting
ar_distributions_all:Receipt applications accounting & misc receipts accounting
ar_misc_cash_distributions_all:Misc receipts accounting
Query for Subledger Transfer to GL
If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.Here is the query for Transaction. SELECT gjjlv.period_name "Period Name" , gjb.name "Batch Name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" ,glcc.concatenated_segments "Accounts" , NVL(gjjlv.line_entered_dr,0) "Entered Debit" , NVL(gjjlv.line_entered_cr,0) "Entered Credit" , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit" , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit" , gjjlv.currency_code "Currency" , rctype.name "Trx type" , rcta.trx_number "Trx Number" , rcta.trx_date "Trx Date" , RA.CUSTOMER_NAME "Trx Reference" , gjh.STATUS "Posting Status" , TRUNC(gjh.DATE_CREATED) "GL Transfer Dt" , gjjlv.created_by "Transfer By"FROM apps.GL_JE_JOURNAL_LINES_V gjjlv , gl_je_lines gje , gl_je_headers gjh , gl_je_batches gjb , ra_customer_trx_all rcta , apps.ra_customers ra , apps.gl_code_combinations_kfv glcc , ra_cust_trx_types_all rctypeWHERE gjh.period_name IN ('OCT-2008','NOV-2008')AND glcc.code_combination_id = gje.code_combination_idAND gjh.je_batch_id = gjb.je_batch_idAND gjh.je_header_id = gje.je_header_idAND gjh.period_name = gjb.default_period_nameAND gjh.period_name = gje.period_nameAND gjjlv.period_name = gjh.period_nameAND gjjlv.je_batch_id = gjh.je_batch_idAND gjjlv.je_header_id = gjh.je_header_idAND gjjlv.line_je_line_num = gje.je_line_numAND gjjlv.line_code_combination_id = glcc.code_combination_idAND gjjlv.line_reference_4 = rcta.trx_numberAND rcta.cust_trx_type_id = rctype.cust_trx_type_idAND rcta.org_id = rctype.org_idAND ra.customer_id = rcta.bill_to_customer_id

Receipt Details
GL Transfer Can Capture the Fingerprints (Link between GL to AR )
When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns that is then pushed the detailes in GL_JE_LINES table. Here are the details for Reference columns
REFERENCE21 :posting_control_id
REFERENCE22 :cash_receipt_idcash_receipt_history_id or cash_receipt_id for MISC
REFERENCE23 :line_id
REFERENCE24 :receipt_number
REFERENCE25 :null for CASH / cash_receipt_history_id for MISC
REFERENCE26 :null
REFERENCE27 :pay_from_customer
REFERENCE28 :MISC / TRADE
REFERENCE29 :MISC_source type or TRADE_source_type
REFERENCE30 :AR_CASH_RECEIPT_HISTORY
Key Tables
In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.
ar_cash_receipt_history_all:Receitps accounting
ar_distributions_all: Receitps accounting
ar_receivable_applications_all: Receipt applications accounting
ar_distributions_all:Receipt applications accounting & misc receipts accounting
ar_misc_cash_distributions_all:Misc receipts accounting
Query for Subledger Transfer to GL
If you want to get details of different journals transferred to GL, use this to get the result. You can also fine tune with period , currency or clearing company code or Journal Type.Here is the query for Transaction.

SELECT gjjlv.period_name "Period" , gjb.name "Batch name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" , glcc.concatenated_segments "Accounts" , gjjlv.line_entered_dr "Entered Debit" , gjjlv.line_entered_cr "Entered Credit" , gjjlv.line_accounted_dr "Accounted Debit" , gjjlv.line_accounted_cr "Accounted Credit" , gjjlv.currency_code "Currency" , arm.name "Payment Method" , acra.receipt_number "Receipt Num" , acra.receipt_date "Receipt Date" , RA.CUSTOMER_NAME "Reference" , gjjlv.created_by "Gl Transfer By"FROM apps.gl_je_journal_lines_v gjjlv , gl_je_lines gje , gl_je_headers gjh , gl_je_batches gjb , ar_cash_receipts_all acra , apps.ra_customers ra , apps.gl_code_combinations_kfv glcc , ar_receipt_methods armWHERE gjh.period_name IN ('OCT-2007','NOV-2007')AND glcc.code_combination_id = gje.code_combination_idAND gjh.JE_BATCH_ID = gjb.JE_BATCH_IDAND gjh.JE_HEADER_ID = gje.JE_HEADER_IDAND gjh.period_name = gjb.default_period_nameAND gjh.period_name = gje.period_nameAND gjjlv.period_name = gjh.period_nameAND gjjlv.je_batch_id = gjh.je_batch_idAND gjjlv.je_header_id = gjh.je_header_idAND gjjlv.LINE_JE_LINE_NUM = gje.je_line_numAND gjjlv.line_code_combination_id = glcc.code_combination_idAND gjjlv.line_reference_4 = acra.receipt_numberAND ra.customer_id = acra.pay_from_customerAND acra.receipt_method_id = arm.receipt_method_idAND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUEAND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID





Run ARGLTP module: General Ledger Transfer Program
If your program is running bit slow , you can run this in debug mode and investigate why performance is done.
Based out of experince , the performance hit would be related to the number of records. If you are trying to determine why records are not imported you could run a smaller range than a week and run that in degbug mode. If you are trying to determine performance issues the debug is likely not going to help.