- OracleApps Epicenter - http://www.oracleappshub.com -

AR to GL Transfer

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

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.

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

Key Tables

In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.

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.

  1.  
  2.  
  3.  
  4. SELECT [1] 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 [2](gjjlv.line_entered_dr,0) "Entered Debit"
  10. , NVL [2](gjjlv.line_entered_cr,0) "Entered Credit"
  11. , NVL [2](gjjlv.line_accounted_dr,0) "Accounted Debit"
  12. , NVL [2](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 [3](gjh.DATE_CREATED) "GL Transfer Dt"
  20. , gjjlv.created_by "Transfer By"
  21. FROM [4] 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 [5] gjh.period_name IN [6] ('OCT-2008','NOV-2008')
  30. AND [7] glcc.code_combination_id = gje.code_combination_id
  31. AND [7] gjh.je_batch_id = gjb.je_batch_id
  32. AND [7] gjh.je_header_id = gje.je_header_id
  33. AND [7] gjh.period_name = gjb.default_period_name
  34. AND [7] gjh.period_name = gje.period_name
  35. AND [7] gjjlv.period_name = gjh.period_name
  36. AND [7] gjjlv.je_batch_id = gjh.je_batch_id
  37. AND [7] gjjlv.je_header_id = gjh.je_header_id
  38. AND [7] gjjlv.line_je_line_num = gje.je_line_num
  39. AND [7] gjjlv.line_code_combination_id = glcc.code_combination_id
  40. AND [7] gjjlv.line_reference_4 = rcta.trx_number
  41. AND [7] rcta.cust_trx_type_id = rctype.cust_trx_type_id
  42. AND [7] rcta.org_id = rctype.org_id
  43. AND [7] 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.

12 Comments (Open | Close)

12 Comments To "AR to GL Transfer"

#1 Comment By reddy On December 9, 2008 @ 12:17 pm

Hey Man

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

fine
bye
reddy

#2 Comment By sangi On December 10, 2008 @ 9:28 pm

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

#3 Pingback By AR to GL Transfer : Part II | OracleApps Epicenter On December 11, 2008 @ 7:44 am

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

#4 Comment By Donald Ferreira On December 16, 2008 @ 5:48 am

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 Comment By sj On January 7, 2009 @ 9:40 am

how about a query for r12

#6 Comment By Guptha On February 27, 2009 @ 3:08 am

Hi Boss,

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

Thanks
Guptha

#7 Comment By Paul Andrew On March 4, 2009 @ 8:34 am

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 Comment By Stanley On March 9, 2009 @ 12:42 pm

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 Comment By Sanjit Anand On March 14, 2009 @ 2:00 am

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 Comment By Padhi On May 20, 2009 @ 6:02 am

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 Comment By Reddy On September 24, 2009 @ 12:11 am

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 Comment By Sunil Nair On July 5, 2010 @ 11:13 am

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