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

Payables Transfer to GL

Transfer Program

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.

Here are the 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:

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.

  1.  
  2. SELECT [1] glcc.segment1 "Company"
  3. , gjjlv.period_name "PERIOD"
  4. , gjb.name "JOURNAL BATCH NAME"
  5. , gjjlv.header_name "JOURNAL SOURCE"
  6. , gjjlv.line_reference_1 "SUPPLIER NAME"
  7. , gjjlv.currency_code "CURRENCY"
  8. , invoice_type_lookup_code "TRANSACTION TYPE"
  9. , gjjlv.line_reference_5 "TRANSACTION NUMBER"
  10. , aia.invoice_date "TRANSACTION DATE"
  11. -- , gjjlv.je_source "SOURCE"
  12. , gjjlv.line_entered_dr "ENTERED DEBIT"
  13. , gjjlv.line_entered_cr "ENTERED CREDIT"
  14. , gjjlv.line_accounted_dr "ACCOUNTED_DEBIT"
  15. , gjjlv.line_accounted_cr "ACCOUNTED_CREDIT"
  16. ,glcc.concatenated_segments "CHARGE ACCOUNT"
  17. FROM [2] apps.GL_JE_JOURNAL_LINES_V gjjlv
  18. , gl_je_lines gjl
  19. , ap_ae_lines_all aala
  20. , gl_je_headers gjh
  21. , gl_je_batches gjb
  22. , ap_invoices_all aia
  23. , apps.gl_code_combinations_KFV glcc
  24. , po_vendors pv
  25. WHERE [3] gjl.gl_sl_link_table = 'APECL'
  26. AND [4] gjl.period_name ='NOV-2008'--period
  27. --and gjjlv.currency_code = --currency code
  28. AND [4] gjb.je_batch_id = gjh.je_batch_id
  29. AND [4] gjh.je_header_id = gjl.je_header_id
  30. AND [4] gjh.period_name = gjl.period_name
  31. AND [4] gjh.set_of_books_id = gjl.set_of_books_id
  32. AND [4] glcc.code_combination_id = gjl.code_combination_id
  33. AND [4] gjjlv.je_batch_id = gjh.je_batch_id
  34. AND [4] gjjlv.je_header_id = gjh.je_header_id
  35. AND [4] gjjlv.line_je_line_num = gjl.je_line_num
  36. AND [4] gjh.period_name = gjjlv.period_name
  37. AND [4] gjh.set_of_books_id = gjjlv.set_of_books_id
  38. AND [4] glcc.code_combination_id = gjjlv.line_code_combination_id
  39. AND [4] aala.code_combination_id = gjl.code_combination_id
  40. AND [4] aala.gl_sl_link_id = gjl.gl_sl_link_id
  41. AND [4] aala.reference5 = aia.invoice_num
  42. AND [4] gjh.set_of_books_id = aia.set_of_books_id
  43. AND [4] pv.vendor_id = aia.vendor_id
  44. AND [4] gjjlv.line_reference_1 = pv.vendor_name
  45. ORDER [5] BY [6] aia.invoice
  46.  

 

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.

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?

Resolutions: 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:

  1. Run Invoice Validation process
  2. 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:

20 Comments (Open | Close)

20 Comments To "Payables Transfer to GL"

#1 Comment By sj On November 27, 2008 @ 1:14 pm

Can you explain how this process works in Release 12 ?

#2 Comment By sangi On November 27, 2008 @ 11:14 pm

nice document anand.

#3 Comment By Sabya On January 2, 2009 @ 6:07 pm

The query is really good. It saves a great amount of time if u tune it at critical prod support time. Good Job

#4 Comment By Steve On February 14, 2009 @ 10:57 am

Is it possible to transfer a payables dff to a general ledger dff?

Thanks,
Steve

#5 Comment By khalid On February 16, 2009 @ 10:50 pm

thanks a lot freind
Sanjit Anand

my dear, our AP trial balance not balancing with GL Liability Account
i run AP Trial balance in diffrent periods but give me same result.
do you sink that we may need to run script to rebuild the trial balance ?

#6 Comment By khalid On February 18, 2009 @ 10:04 pm

thanx for your help and advice
Rgrds

#7 Comment By Sergey On February 25, 2009 @ 7:17 am

Thank you, Sanjit Anand. It’s a really good article. Due to the article that my issue was solved

#8 Comment By khalid On March 10, 2009 @ 10:05 pm

Hi Anand,

How can I stop working with Bank clearance account, and pay direct from cash account.

#9 Comment By khalid On March 10, 2009 @ 10:11 pm

can you explain accounting that the apps create starting from Prchasing and inventory up to match the PO in the Payable

thanx, Rgds

#10 Comment By Sanjit Anand On March 14, 2009 @ 12:59 am

Hi khalid ,

the accounting setup , i shared some long time back, just browse some old notes or use search features .

you will get it

#11 Comment By Altaf Shaikh On August 13, 2009 @ 5:45 am

It is really good to have such scripts shared across.It has helped My friend Rohit to develope query and understand table links in more detail.

#12 Comment By Altaf Shaikh On August 13, 2009 @ 5:46 am

Thanks Sanjit Anand

#13 Comment By Arun On September 7, 2009 @ 5:51 am

Great Post.. I love all your posts..

#14 Comment By Tom On December 11, 2009 @ 10:28 am

Thanks, very helpful site.

#15 Comment By Naveen On January 19, 2010 @ 12:14 pm

Great Post. I always like your post. Just Wanted to know why there is not GL_POSTED_DATE in AP as it is there in AR at distribution level.

How do we find when the invoice is posted without going to GL_JE_LINES ?

Please ,let me know .

Naveen

#16 Comment By Brigita On December 5, 2010 @ 9:55 pm

Very helpful post. Thank you! I have one question – when we transfer to GL in summary can we see if a particular invoice was transferred to the GL_JE tables and what is the linking field? Thank you!

#17 Comment By Help with re-submitting Payable Transfer to GL On June 6, 2011 @ 4:36 am

Hello
I previously run the ‘Payable Transfer to GL’ report. l subsequently deleted the transfered invoice – ‘Journal’ in GL because of some problems with the figure.
Eversince that time l have been trying to run a new ‘Payable transfer to GL’ and “keep getting 0 accounting entries transfered to GL”
Is this due to the fact that l have previously run the report for that specific period?

Please can anyone help.

#18 Comment By Anand On August 22, 2011 @ 12:45 am

is there any column like set_of_books_id_id in GL_JE_HEADERS, can u please verify and let me know the result.

#19 Comment By surendra On September 14, 2011 @ 6:33 am

Hi Frnds,

I am in to Oracle 11i Implementation. I have a doubt that after i transferring the data from sub-modules to General Ledger, the JV are posted in a batch. Now I want to know what information that really transfer to GL from other modules and how does it consider the fields where and what should be get posted in GL?

#20 Comment By vino On January 18, 2013 @ 1:15 am

Can any one say where Payable accounting process Concurrent program is registered ?