Saturday, November 6, 2010
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.
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.
Payable Transfer to GL
Troubleshooting AP to GL Program
Bug & Enhancement Design
Developer: Avinash Borade
The information contained in this document is considered confidential in nature and is offered for the exclusive consumption of PTC business partners, clients, and prospective clients. Any other use of this material without prior written consent of PTC is expressly prohibited.
Prepared by: Avinash Borade
Last Updated: 2/19/09 1:24 PM
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:
Payables Transfer to General Ledger APPPST (AP)
General Ledger Transfer Program ARGLTP(AR)
Transfer transactions to GL INCTGL(Inv)
Payroll Transfer to GL(PAY)
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:
GL_SL_LINK_ID: value is a unique, sequential number
GL_SL_LINK_TABLE: value is APECL for Payables actuals, and APENCL for Payables encumbrances.
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 InvoicesRecords 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
PaymentsRecords for the Payments journal category debit the Liability account, credit the Cash account, and are charged to the Discount, Realized Gain/Loss, Future Payment, and Rounding accounts.
USER_JE_CATEGORY_NAME: PaymentsUSER_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 PaymentsRecords 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.
SELECT glcc.segment1 "Company" , gjjlv.period_name "PERIOD" , gjb.name "JOURNAL BATCH NAME" , gjjlv.header_name "JOURNAL SOURCE" , gjjlv.line_reference_1 "SUPPLIER NAME" , gjjlv.currency_code "CURRENCY" , invoice_type_lookup_code "TRANSACTION TYPE" , gjjlv.line_reference_5 "TRANSACTION NUMBER" , aia.invoice_date "TRANSACTION DATE"-- , gjjlv.je_source "SOURCE" , gjjlv.line_entered_dr "ENTERED DEBIT" , gjjlv.line_entered_cr "ENTERED CREDIT" , gjjlv.line_accounted_dr "ACCOUNTED_DEBIT" , gjjlv.line_accounted_cr "ACCOUNTED_CREDIT" ,glcc.concatenated_segments "CHARGE ACCOUNT"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv , gl_je_lines gjl , ap_ae_lines_all aala , gl_je_headers gjh , gl_je_batches gjb , ap_invoices_all aia , apps.gl_code_combinations_KFV glcc , po_vendors pv
WHERE gjl.gl_sl_link_table = 'APECL'AND gjl.period_name ='NOV-2008'--period--and gjjlv.currency_code = --currency codeAND gjb.je_batch_id = gjh.je_batch_idAND gjh.je_header_id = gjl.je_header_idAND gjh.period_name = gjl.period_nameAND gjh.set_of_books_id = gjl.set_of_books_idAND glcc.code_combination_id = gjl.code_combination_idAND gjjlv.je_batch_id = gjh.je_batch_idAND gjjlv.je_header_id = gjh.je_header_idAND gjjlv.line_je_line_num = gjl.je_line_numAND gjh.period_name = gjjlv.period_nameAND gjh.set_of_books_id = gjjlv.set_of_books_idAND glcc.code_combination_id = gjjlv.line_code_combination_idAND aala.code_combination_id = gjl.code_combination_idAND aala.gl_sl_link_id = gjl.gl_sl_link_idAND aala.reference5 = aia.invoice_numAND gjh.set_of_books_id = aia.set_of_books_idAND pv.vendor_id = aia.vendor_idAND gjjlv.line_reference_1 = pv.vendor_nameORDER BY aia.invoice
Troubleshooting AP To GL Program
Issue 1 :Why 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.
Invoice Hold Report - displays all hold invoices.
Posting Hold Report - only displays invoices with holds that prevent posting.
Matching Hold Report - only displays invoices with matching holds.
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:
Run Invoice Validation process
then run Payables Accounting Process and you have the option of transferring to GL
you may also submit payables transfers to GL later.
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:
one section shows accounting entries that could not be transferred because they were in an error status
second sectiom shows transferred due to a discrepancy between the accounted account and the account in the general ledger.
Bug & Enhancement Design
Developer: Avinash Borade
The information contained in this document is considered confidential in nature and is offered for the exclusive consumption of PTC business partners, clients, and prospective clients. Any other use of this material without prior written consent of PTC is expressly prohibited.
Prepared by: Avinash Borade
Last Updated: 2/19/09 1:24 PM
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:
Payables Transfer to General Ledger APPPST (AP)
General Ledger Transfer Program ARGLTP(AR)
Transfer transactions to GL INCTGL(Inv)
Payroll Transfer to GL(PAY)
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:
GL_SL_LINK_ID: value is a unique, sequential number
GL_SL_LINK_TABLE: value is APECL for Payables actuals, and APENCL for Payables encumbrances.
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 InvoicesRecords 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
PaymentsRecords for the Payments journal category debit the Liability account, credit the Cash account, and are charged to the Discount, Realized Gain/Loss, Future Payment, and Rounding accounts.
USER_JE_CATEGORY_NAME: PaymentsUSER_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 PaymentsRecords 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.
SELECT glcc.segment1 "Company" , gjjlv.period_name "PERIOD" , gjb.name "JOURNAL BATCH NAME" , gjjlv.header_name "JOURNAL SOURCE" , gjjlv.line_reference_1 "SUPPLIER NAME" , gjjlv.currency_code "CURRENCY" , invoice_type_lookup_code "TRANSACTION TYPE" , gjjlv.line_reference_5 "TRANSACTION NUMBER" , aia.invoice_date "TRANSACTION DATE"-- , gjjlv.je_source "SOURCE" , gjjlv.line_entered_dr "ENTERED DEBIT" , gjjlv.line_entered_cr "ENTERED CREDIT" , gjjlv.line_accounted_dr "ACCOUNTED_DEBIT" , gjjlv.line_accounted_cr "ACCOUNTED_CREDIT" ,glcc.concatenated_segments "CHARGE ACCOUNT"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv , gl_je_lines gjl , ap_ae_lines_all aala , gl_je_headers gjh , gl_je_batches gjb , ap_invoices_all aia , apps.gl_code_combinations_KFV glcc , po_vendors pv
WHERE gjl.gl_sl_link_table = 'APECL'AND gjl.period_name ='NOV-2008'--period--and gjjlv.currency_code = --currency codeAND gjb.je_batch_id = gjh.je_batch_idAND gjh.je_header_id = gjl.je_header_idAND gjh.period_name = gjl.period_nameAND gjh.set_of_books_id = gjl.set_of_books_idAND glcc.code_combination_id = gjl.code_combination_idAND gjjlv.je_batch_id = gjh.je_batch_idAND gjjlv.je_header_id = gjh.je_header_idAND gjjlv.line_je_line_num = gjl.je_line_numAND gjh.period_name = gjjlv.period_nameAND gjh.set_of_books_id = gjjlv.set_of_books_idAND glcc.code_combination_id = gjjlv.line_code_combination_idAND aala.code_combination_id = gjl.code_combination_idAND aala.gl_sl_link_id = gjl.gl_sl_link_idAND aala.reference5 = aia.invoice_numAND gjh.set_of_books_id = aia.set_of_books_idAND pv.vendor_id = aia.vendor_idAND gjjlv.line_reference_1 = pv.vendor_nameORDER BY aia.invoice
Troubleshooting AP To GL Program
Issue 1 :Why 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.
Invoice Hold Report - displays all hold invoices.
Posting Hold Report - only displays invoices with holds that prevent posting.
Matching Hold Report - only displays invoices with matching holds.
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:
Run Invoice Validation process
then run Payables Accounting Process and you have the option of transferring to GL
you may also submit payables transfers to GL later.
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:
one section shows accounting entries that could not be transferred because they were in an error status
second sectiom shows transferred due to a discrepancy between the accounted account and the account in the general ledger.
Subscribe to:
Posts (Atom)