Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

AR to GL Transfer

Posted on December 6th, 2008 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

dgreybarrowOracle 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.

dgreybarrowPosting Journals to GL

Your Navigation for transfer process would be

Interfaces> General ledger(11i)

AR to GL transfer1

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.

dgreybarrowA 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.

dgreybarrowVanila 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.

dgreybarrowWhat 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.

dgreybarrowConnecting World :Link between GL to AR

TRANSFER

 

 

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 :type||account_class
  • REFERENCE30 :RA_CUST_TRX_LINE_GL_DIST

Applications

  • 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
  • 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

dgreybarrowKey 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

dgreybarrowQuery 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.

  1.  
  2.  
  3.  
  4. SELECT gjjlv.period_name "Period Name"
  5. , gjb.name "Batch Name"
  6. , gjjlv.header_name "Journal Entry For"
  7. , gjjlv.je_source "Source"
  8. ,glcc.concatenated_segments "Accounts"
  9. , NVL(gjjlv.line_entered_dr,0) "Entered Debit"
  10. , NVL(gjjlv.line_entered_cr,0) "Entered Credit"
  11. , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
  12. , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
  13. , gjjlv.currency_code "Currency"
  14. , rctype.name "Trx type"
  15. , rcta.trx_number "Trx Number"
  16. , rcta.trx_date "Trx Date"
  17. , RA.CUSTOMER_NAME "Trx Reference"
  18. , gjh.STATUS "Posting Status"
  19. , TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
  20. , gjjlv.created_by "Transfer By"
  21. FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
  22. , gl_je_lines gje
  23. , gl_je_headers gjh
  24. , gl_je_batches gjb
  25. , ra_customer_trx_all rcta
  26. , apps.ra_customers ra
  27. , apps.gl_code_combinations_kfv glcc
  28. , ra_cust_trx_types_all rctype
  29. WHERE gjh.period_name IN ('OCT-2008','NOV-2008')
  30. AND glcc.code_combination_id = gje.code_combination_id
  31. AND gjh.je_batch_id = gjb.je_batch_id
  32. AND gjh.je_header_id = gje.je_header_id
  33. AND gjh.period_name = gjb.default_period_name
  34. AND gjh.period_name = gje.period_name
  35. AND gjjlv.period_name = gjh.period_name
  36. AND gjjlv.je_batch_id = gjh.je_batch_id
  37. AND gjjlv.je_header_id = gjh.je_header_id
  38. AND gjjlv.line_je_line_num = gje.je_line_num
  39. AND gjjlv.line_code_combination_id = glcc.code_combination_id
  40. AND gjjlv.line_reference_4 = rcta.trx_number
  41. AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
  42. AND rcta.org_id = rctype.org_id
  43. AND ra.customer_id = rcta.bill_to_customer_id
  44. --and glcc.segment1 ='30D
  45.  
  46.  
  47.  
  48.  

Will discuss for Receipt Transfer query in another post.

Related Posts

Posted in Oracle Receivable | 12 Comments »Email This Post Email This Post | Print This Post Print This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
12 Responses
  1. reddy Says:

    Hey Man

    Every thing u have give,
    Excellent. infact i am wodering about your material,
    wonder, how is it posible.

    fine
    bye
    reddy

  2. sangi Says:

    you have done wonderfull work annad. It can help full to all Consultants.

  3. AR to GL Transfer : Part II | OracleApps Epicenter Says:

    […] to GL Transfer : Part IIAR to GL TransferInventory Transfer To GLFixed Asset Transfer to GLPayables Transfer to GLEBS Approvals goes […]

  4. Donald Ferreira Says:

    Hi Sanjit,

    Thanks for posting this useful queries. WOnderful work, keep it up.

    I have a question. You say at the start this ….SRS based Concurrent Process…. know as ARGLTP

    This concurrent program is a Spawned program.

    Can you let me know how do you read such a program?

    Thanks and have a nice day

    Kind regards
    Donald

  5. sj Says:

    how about a query for r12

  6. Guptha Says:

    Hi Boss,

    I am new to Finance module, your postings are really helping me.

    Thanks
    Guptha

  7. Paul Andrew Says:

    Sanjit, thank you for the information.
    Can you tell me if the ARGLTP
    can populate a custom GL Interface table instead of the GL Interface? Example: AR_GL_interface…

  8. Stanley Says:

    Hi,
    This tranfer process takes over 4 hours and locks the GL_INTERFACE table. Is there a way of using another interface table or shortening the process?
    Thanks…
    Stanley

  9. Sanjit Anand Says:

    Hi Paul,

    Oracle seeded Program does not have capability to manage via custom table.

    If you have specfic case , you can modify and alter the code. I am not sure about the underline entire source code , but you must check it in irep.

    Such scope is always there business to business case by case, where need to modify the seeded process by embedding some extra logic, by mean of changing the code, adding custom table etc…

    The most important for you , you must verify the api’s and underline code , so that any changes in nexxt version or point/family release cann’t affect the customization flow.

  10. Padhi Says:

    Hi
    This article is very helpfull for me. I am having a question as follows,

    In APPLICATION part you mentioned the REFRENCE23 IS LINE_ID. This LINE_ID is what and in which table?

    Your answer will help me a lot..

    thanks
    Padhi

  11. Reddy Says:

    Hi,

    Its very good material, U explained every thing, but not discussed the interface packages, which packages involves in this transfer.

    Thanks and Regards,
    Reddy

  12. Sunil Nair Says:

    Hi, this was a gret work and thanks for the same.

    I have a issue in my production instance
    Issue: I have a unposted journal batch in GL, which i dont wantto post and also i dont weant any accounting impact by that, hence I revered this batch and it created another batch in reversed status as expected. both are in un posted status.
    I dont want them to be in GL for ever. Can I just delete them? what can be the impact if any?.

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.