Related Posts Plugin for WordPress, Blogger...

AR TO GL Transfer

Written By Jagadeesan M on 10 August 2012 |

Oracle Financials Accounts Receivables Transfer Process
The AR Transfer process is a SRS based Concurrent Process which can be used for transfer process from
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:
1.    Navigate to the Run General Ledger Interface window.
2.    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)
3.    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.
4.    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.
5.    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.
Vanila Oracle AR Transfer Process : Addressing user Need
AR to General ledger Transfer Program(ARGLTP) does not have any provision to run with specific categories like Invoices, Receipts, etc.
In real time scenarios finance user need to transfer AR’s datas outside the sales invoice category any time. Because The sales invoice category can not be transferred before reconciliation with customer , So user need to transfer other datas outside of sales invoice category, thus they may request you for category options for that you need to modify the General Ledger interface Program by adding some new parameters Category From /Category To.
What Category we have in GL
  • Trade Receipts
  • Misc Receipts
  • Adjustments
  • Sales Invoices
  • Debit Memos
  • Chargebacks
  • Credit Memos
  • CM Applications
Sales Invoices ,Debit Memos ,Chargebacks 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.
  • 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
  • REFERENCE29 :source_type prefixed by ‘ADJ’
  • REFERENCE21 :posting_control_id
  • REFERENCE22 :customer_trx_id
  • REFERENCE23 :cust_trx_line_gl_dist_id
  • REFERENCE24 :trx_number
  • REFERENCE25 :cust.account_number
  • REFERENCE27 :bill_to_customer_id
  • REFERENCE29 :type||account_class
  • REFERENCE21 :posting_control_id
  • REFERENCE22 :cash_receipt_id||receivable_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_type||source_type
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
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"
        ,                      "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"
        ,                    "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 rctype
where     gjh.period_name IN ('OCT-2008','NOV-2008')
and       glcc.code_combination_id = gje.code_combination_id
and       gjh.je_batch_id = gjb.je_batch_id
and       gjh.je_header_id = gje.je_header_id
and       gjh.period_name = gjb.default_period_name
and       gjh.period_name = gje.period_name
and       gjjlv.period_name = gjh.period_name
and       gjjlv.je_batch_id = gjh.je_batch_id
and       gjjlv.je_header_id = gjh.je_header_id
and       gjjlv.line_je_line_num  = gje.je_line_num
and       gjjlv.line_code_combination_id = glcc.code_combination_id
and       gjjlv.line_reference_4 = rcta.trx_number
and       rcta.cust_trx_type_id = rctype.cust_trx_type_id
and       rcta.org_id = rctype.org_id
and       ra.customer_id = rcta.bill_to_customer_id
--and       glcc.segment1 ='30D


Post a Comment