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.
Thursday, July 22, 2010
1099 Supplier Overview
In the United States, you must report to the Internal Revenue Service certain types of payments you make to 1099 reportable suppliers. In the supplier window, you can designate suppliers as federally reportable. When you enter invoices for the supplier, you classify invoice distributions by 1099 MISC type using the Income Tax Type field. At year end, you can then report, in standard formats, your accumulated 1099 payment information to the Internal Revenue Service, other tax agencies, and your suppliers. For each reporting entity, you assign one or more balancing segment values. When you submit 1099 reports for a tax entity, Payables sums up the paid invoice distributions that have these company balancing segment values in their accounts. To create and report on 1099 supplier payments: • If you want to use combined federal and state 1099 filing, and want to produce K–Records and B–Records, enable the Combined Filing Program Payables option. • If you use combined federal and state filing, define your tax regions. • Define your tax reporting entities. 2. Enter 1099 details for suppliers: 3. Enter and pay 1099 invoices: • When you enter invoices for 1099 suppliers, enter an Income Tax Type and Income Tax Region for appropriate invoice distributions, or accept the default from the supplier • Pay invoices. 4. Identify and resolve 1099 exceptions: • Submit for each reporting entity the 1099 Invoice Exceptions Report to identify the following exceptions: – 1099 invoice distributions with no Income Tax Type – 1099 invoice distributions with invalid or missing income tax regions – non–1099 invoice distributions with an Income Tax Type Resolve exceptions for each invoice distribution. • Submit the 1099 Supplier Exceptions Report: to identify and resolve the following exceptions: – suppliers with null or invalid state abbreviations – suppliers that will be flagged as foreign in the 1099 Electronic Media report – suppliers with missing or non–standard Tax Identification Numbers Resolve any exceptions in the Suppliers window. • Print a Tax Information Verification Letter for each supplier who has not yet furnished or confirmed its tax identification number or tax reporting region. After a supplier provides this information, update the Supplier Verification Date in the Tax Region of the Suppliers window. 5. Optionally withhold tax from suppliers if they have a missing or invalid Tax Identification Number (TIN) and if you have not met the legal requirements of requesting a valid TIN from them. 6. Update 1099 Payment Information. • Adjust Invoice Distributions manually in the Invoice Workbench, or use the Update Income Tax Details Utility. 7. Generate 1099 Reports. • 1099 Forms. Submit to the Internal Revenue Service for each 1099 supplier, and send a copy to the supplier. • 1096 Form. Submit to the Internal Revenue Service for each 1099 MISC type paid during the calendar year. • 1099 Electronic Media. If you submit 250 or more 1099 forms, the Internal Revenue Service requires you to file your 1099 payment information on magnetic media or electronically • Form 941. Send this form to the Internal Revenue Service to report total supplier withholding for a quarter. Your Accounts Payable department should create a manual payment for the total amount withheld, and you should remit this amount and the 1096 Form to the Internal Revenue Service. • 1099 Payments Report. Run this report for your reference to review 1099 payments by supplier or MISC type
Wednesday, July 21, 2010
Payable -Invoice Technical Process View
This Post will give Detail Information about Technical Overview of Invoice Creation detail Process
Steps :-
1)Create Invoice :Base Table affected
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTION_ALL
AP_PAYMENT_SCHEDULE_ALL
Till now no accounting is generated.
2)Validate the Invoice:
When you validated the invoices the accounting event is generated in
AP_ACCOUNTING_EVENT_ALL table
Still no accounting is generated in the accounting tables
3)Create the Accounting :
Online accounting process finally insert the data into
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
This accounting tables are giving actual credit and debit information about each invoices.When you run payable transfer to gl or Payable accounting process data is pick up from this accounting tables and inserted to GL base tables through interface table.
Steps :-
1)Create Invoice :Base Table affected
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTION_ALL
AP_PAYMENT_SCHEDULE_ALL
Till now no accounting is generated.
2)Validate the Invoice:
When you validated the invoices the accounting event is generated in
AP_ACCOUNTING_EVENT_ALL table
Still no accounting is generated in the accounting tables
3)Create the Accounting :
Online accounting process finally insert the data into
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
This accounting tables are giving actual credit and debit information about each invoices.When you run payable transfer to gl or Payable accounting process data is pick up from this accounting tables and inserted to GL base tables through interface table.
AP Period Close Process
Business Requirements
We must reconcile the accounts payable activity for the accounting period that is to be closed. The following steps are taken in performing period-end processing for Oracle Payables. The posting level for Oracle Payables must be determined, when planning period-end procedures. Where detail level accounting transactions are required to be posted to the general ledger, there may be technical constraints involved, relating to the physical data storage volume, and posting and report processing speed degradation caused by the sheer volume of posted transactions. Functionally, however, detail posting to the general ledger provides enhanced reporting opportunities in some cases.
Steps
Complete All Transactions for the Period Being Closed:
Keep on Running queries from Apatghealthcheck.sql which will tell you about Orphan Records.
Ensure that all transactions have been entered for the period being closed.
Completing all transactions for Oracle Payables:
Complete Invoicing and Credits
Complete Prepayments
Complete Expense Reports
Complete Invoice Import
Note: If you import transactions from an external system, or you are using Internet Expenses or Xpense Xpress, ensure you have imported all transactions, and reviewed all audit trails for completeness.
Payables Approval Process
Run the Payables Approval Process(Invoice Validation Program in 11i) for All Invoices/Invoice Batches.
The Payables Approval process is run to try to approve all unapproved invoices in the system, so that they can be paid and posted to the general ledger. Invoice Validation Program will take care of all the Validations.
General Ledger
Review and Resolve Amounts to Post to the General Ledger.
Review the Invoice on Hold Report
If there is any Hold on the Invoice it will not accounted. So we need to remove all the holds on the invoices.
After Running Invoice Validation We need to run the Invoice on Hold report.
This report enables review of any holds currently applied to invoices that would prevent the payment and/or posting of these invoices. The report details all invoices for which Oracle Payables cannot select for posting and therefore cannot create journal entries. Where possible resolve any identified holds which will prevent posting to the general ledger, and re-run the Payables Approval process to approve these invoices.
Review the Payables Accounting Entries Report
This report enables review and analysis of accounting entries in the Payables subledger. You can request the report for a specific run of the Payables Accounting Process or the Payables Transfer to General Ledger program. Using the report parameters, you can produce a detailed or summary listing of the accounting information you want to review.
The report also lists in detail any transactions that have been accounted with error and all entries that could not be transferred to the general ledger interface. When a transaction is accounted with errors, use the Update Accounting Entries window to update any invalid accounts.
Payables groups the report by set of books, journal category, and bank accounts. Data is then sorted by accounting date, event type, supplier name, document number, and voucher number.
Review the Unaccounted Transactions Report
This report enables review of all unaccounted invoice and payment transactions and see the reason that Payables cannot account for a transaction. Payables sorts the report by transaction type (invoice or payment), exception, supplier, transaction currency, and transaction number.
Run this report after you have run the Payables Accounting Process. The report will then show only transactions that had problems that prevented accounting. You can then correct the problems and resubmit the accounting process.
Note that this report does not include invoices that have no distributions.
Optionally Run a Month End Payment Batch
By running a month end payment batch, you may arrange a payment for as many due invoices as possible.
Run Updated Mature Dated Future Payments Program to Change the status of the Payments to Negotiable so that we can account these Payments.
Confirm all Payment Batches
Optionally run the Final Payment Register for the period that is to be closed, or finalise any outstanding payment batches. Use the Payment Batches window to confirm any unconfirmed payment batches.
Note: The Final Payments Register lists each payment created for a payment batch. Actual payments can be compared against this register to verify that Oracle Payables has recorded payments correctly. The report lists each payment in a payment batch, including setup and overflow payment documents, in ascending order, by payment number.
Attention : Oracle Payables prevents the closing of a period in which all payment batches have not been confirmed.
Optionally Run the Payments Registers
Optionally, run the Payment Register.
This report details payments printed in a particular accounting period. The report can be used to review payment activity for each bank account used during the specified time period.
Warning: The report total only shows the net (less discounts and voids) payment amount, whereas the Posted Payment Register total is the total payment amount, including discounts. If only verifying report totals, these two reports would not balance. Therefore it is necessary to subtract the discounts taken from the report total and then compare this calculated amount to the total displayed on the Payment Register Report.
Reconcile Payments
Reconcile Payments to Bank Statement Activity for the Period.
(Refer to Period-End Procedures for Oracle Cash Management)
Attention: The Payables Accounting process must be re-run for any reconciliation accounting entries generated to be Transferred to the General Ledger.
Transfer All Approved Invoices and Payments
Transfer All Approved Invoices and Payments to the General Ledger.
The Request Submit window facilitates the transfer of Oracle Payables accounting entries to the General Ledger and creates unposted journal entries.
Oracle Payables creates detail or summary journal entries from the invoice and payment activity when you post. The journal entries are posted in Oracle General Ledger to update account balances.
Submit the Payables Accounting Process to transfer approved invoice and payment accounting entries to the Oracle General Ledger interface tables.
Invoice journal entries, debit the expense or other account entered on an invoice distribution line, and credit the liability account nominated on the invoice.
Payment journal entries, debit the liability account and credit the cash account of the bank account used to pay and invoice.
The Payables Accounting Process transfers data to the General Ledger interface tables, creating journal entries for these invoice and payment transactions, if the option Submit Transfer to GL is set to Yes in the Parameter window.
The Payables Accounting Process will initiate the General Ledger Journal Import process, if the option Submit Journal Import is set to Yes in the Parameter window
Attention :The generated journal batch needs to be posted from within Oracle General Ledger. The journal batch may be automatically posted in General Ledger if the Source of Payables has been included in the Automatic Posting options established in General Ledger.
Review the Payables
Review the Payables to General Ledger Posting process After Completion.
The following reports can optionally be run to review the invoices and payments that were posted to Oracle General Ledger, from Oracle Payables, for the period that is to be closed, i.e. the current accounting period.
Payables Accounting Process Report
Payables Accounting Process Report Use this report to review accounting entries created by the Payables Accounting Process. The report has two sections:
Accounting Entries Audit Report. The audit report provides, in detail or summary, a listing of accounting entries created by the accounting process.
Accounting Entries Exception Report. The exception report lists in detail all accounting entries that were created with an error status and a description of that error. The Entries Exception Report is generated only when the accounting process encounters accounting entries that fail validation.
The Posted Invoices Register
This report is used to review invoices for which information has been posted to Oracle General Ledger to create journal entries, and can be used to verify that the total invoice amount equals the total invoice distribution amount. The report lists each Accounts Payable Liability Accounting Flexfield and the invoices posted to the account.
The Posted Payments Register
This report is used to review the payments posted to Oracle General Ledger during a particular accounting period, and can be used to review the payment activity for each bank account used during that period.
Submit the Unaccounted Transactions
Submit the Unaccounted Transactions Sweep Program.
The Unaccounted Transactions Sweep Program transfers unaccounted transactions from one accounting period to another. Because you cannot close a Payables period that has unaccounted transactions in it, if your accounting practices permit it, you might want to use this program to change the accounting date of the transactions to the next open period. For example, you have invoices for which you cannot resolve holds before the close, and your accounting practices allow you to change invoice distribution GL dates. Submit the program to change invoice distribution GL dates to the first day of the next open period so you can close the current period.
The Unaccounted Transactions Sweep Program will not roll forward accounted transactions, or accounted transactions with error. To create successful accounting entries for accounted transactions with error, correct any accounting errors and resubmit the Payables Accounting Process.
The program transfers unaccounted transactions to the period you specify by updating the GL dates to the first day of the new period. You can then close the accounting period from which Payables moved the invoices and payments.
In the Control Payables Periods window if you try to close a period and unaccounted transactions exist, then Payables opens a window. From the window you can submit the Unaccounted Transactions Sweep Program or you can submit a report to review accounting transactions that would be swept by the program. When you submit the Unaccounted Transactions Sweep Program, Payables automatically produces the Unaccounted Transactions Sweep Report to identify transactions that were redated and identify any transactions that need updating. If you submit the report in preliminary sweep mode the Unaccounted Transactions Sweep Review report shows which transactions will be redated if you submit the Unaccounted Transactions Sweep Program.
Close the Current Oracle Payables Period
Close the accounting period by using the Control Payables Periods window to set the Period Status to Closed.
Run the Accounts Payables Trial Balance Report
This report is used to facilitate reconciliation of the total accounts payable liabilities in Oracle Payables, with the Oracle General Ledger Creditors Control Account, for a specific accounting period. This report lists, and sub-totals, by vendor, all unpaid and partially paid invoices for which Oracle Payables created journal entries (i.e. posted invoices). These invoices represent the outstanding accounts payable liability for the organisation.
To obtain the most up-to-date trial balance for a given period, journal entries should be posted for the invoice and payment activity for the period, prior to running the report.
For reconciliation of Oracle Payables and Oracle General Ledger when posting is only performed at period end, the following reconciliation method can be used:
To the previous periods Accounts Payables Trial Balance, add the current period’s posted invoices (total amount from the Posted Invoices Register) , and subtract the current period’s posted payments (total cash plus discounts taken, from the Posted Payments Register) . The calculated amount should equal the balance for the current period’s Accounts Payables Trial Balance.
Attention: However, when posting to the general ledger from Oracle Payables is performed multiple times throughout each accounting period, the Posted Invoices Register and Posted Payments Register reports must be run after each posting run, for reconciliation of Oracle Payables liabilities with Oracle General Ledger’s Creditor Control.
[edit]Run Mass Additions Transfer to Oracle Assets
After you have completed all Payables transaction entry, and confirmed all invoice holds, and carry forwards, submit the Mass Additions Create progam to transfer capital invoice line distributions from Oracle Payables to Oracle Assets.
For foreign currency assets, Payables sends the invoice distribution amount in the converted functional currency. The mass addition line appears in Oracle Assets with the functional currency amount. Oracle Assets creates journal entries for the functional currency amount, so you must clear the foreign currency amount in your general ledger manually.
After you create mass additions, you can review them in the Prepare Mass Additions window in Oracle Assets.
It is recommended to do a final Mass Additions Create after the period close to ensure that all Payables invoices are 1) included in the correct period; and 2) any additional invoicing will become part of the next periods invoice and asset processing period.
Suggestion: If the volume of transactions in Accounts Payable requiring Assets update is large, you should consider running the Mass Additions Create process on a more regular basis.
Open the Next Oracle Payables Period
Open the next accounting period by using the Control Payables Periods window to set the Period Status to Open.
Run Reports for Tax Reporting Purposes (Optional)
A variety of standard reports can be used to provide tax information which is required to be reported to the relevant Tax Authority, including withholding tax.
The Financial Tax Register can be used to view the output from the Tax Reporting Ledger using Reports Exchange and Application Desktop Integrator (ADI). Using these products you can change the layout of the report, publish the report in different formats, and export the data to a tab delimited or HTML file.
The Tax Reporting Ledger consists of accounting information created in Oracle Receivables, Oracle Payables, and Oracle General Ledger. The Financial Tax Register uses this data to generate Tax Register reports using the Rxi reporting tool.
The following tax registers are available:
Tax Register
Interim Tax Register
Nonrecoverable Tax Register
The following summary levels are available within each Tax Register:
Transaction Header
Transaction Line
Accounting Line
We must reconcile the accounts payable activity for the accounting period that is to be closed. The following steps are taken in performing period-end processing for Oracle Payables. The posting level for Oracle Payables must be determined, when planning period-end procedures. Where detail level accounting transactions are required to be posted to the general ledger, there may be technical constraints involved, relating to the physical data storage volume, and posting and report processing speed degradation caused by the sheer volume of posted transactions. Functionally, however, detail posting to the general ledger provides enhanced reporting opportunities in some cases.
Steps
Complete All Transactions for the Period Being Closed:
Keep on Running queries from Apatghealthcheck.sql which will tell you about Orphan Records.
Ensure that all transactions have been entered for the period being closed.
Completing all transactions for Oracle Payables:
Complete Invoicing and Credits
Complete Prepayments
Complete Expense Reports
Complete Invoice Import
Note: If you import transactions from an external system, or you are using Internet Expenses or Xpense Xpress, ensure you have imported all transactions, and reviewed all audit trails for completeness.
Payables Approval Process
Run the Payables Approval Process(Invoice Validation Program in 11i) for All Invoices/Invoice Batches.
The Payables Approval process is run to try to approve all unapproved invoices in the system, so that they can be paid and posted to the general ledger. Invoice Validation Program will take care of all the Validations.
General Ledger
Review and Resolve Amounts to Post to the General Ledger.
Review the Invoice on Hold Report
If there is any Hold on the Invoice it will not accounted. So we need to remove all the holds on the invoices.
After Running Invoice Validation We need to run the Invoice on Hold report.
This report enables review of any holds currently applied to invoices that would prevent the payment and/or posting of these invoices. The report details all invoices for which Oracle Payables cannot select for posting and therefore cannot create journal entries. Where possible resolve any identified holds which will prevent posting to the general ledger, and re-run the Payables Approval process to approve these invoices.
Review the Payables Accounting Entries Report
This report enables review and analysis of accounting entries in the Payables subledger. You can request the report for a specific run of the Payables Accounting Process or the Payables Transfer to General Ledger program. Using the report parameters, you can produce a detailed or summary listing of the accounting information you want to review.
The report also lists in detail any transactions that have been accounted with error and all entries that could not be transferred to the general ledger interface. When a transaction is accounted with errors, use the Update Accounting Entries window to update any invalid accounts.
Payables groups the report by set of books, journal category, and bank accounts. Data is then sorted by accounting date, event type, supplier name, document number, and voucher number.
Review the Unaccounted Transactions Report
This report enables review of all unaccounted invoice and payment transactions and see the reason that Payables cannot account for a transaction. Payables sorts the report by transaction type (invoice or payment), exception, supplier, transaction currency, and transaction number.
Run this report after you have run the Payables Accounting Process. The report will then show only transactions that had problems that prevented accounting. You can then correct the problems and resubmit the accounting process.
Note that this report does not include invoices that have no distributions.
Optionally Run a Month End Payment Batch
By running a month end payment batch, you may arrange a payment for as many due invoices as possible.
Run Updated Mature Dated Future Payments Program to Change the status of the Payments to Negotiable so that we can account these Payments.
Confirm all Payment Batches
Optionally run the Final Payment Register for the period that is to be closed, or finalise any outstanding payment batches. Use the Payment Batches window to confirm any unconfirmed payment batches.
Note: The Final Payments Register lists each payment created for a payment batch. Actual payments can be compared against this register to verify that Oracle Payables has recorded payments correctly. The report lists each payment in a payment batch, including setup and overflow payment documents, in ascending order, by payment number.
Attention : Oracle Payables prevents the closing of a period in which all payment batches have not been confirmed.
Optionally Run the Payments Registers
Optionally, run the Payment Register.
This report details payments printed in a particular accounting period. The report can be used to review payment activity for each bank account used during the specified time period.
Warning: The report total only shows the net (less discounts and voids) payment amount, whereas the Posted Payment Register total is the total payment amount, including discounts. If only verifying report totals, these two reports would not balance. Therefore it is necessary to subtract the discounts taken from the report total and then compare this calculated amount to the total displayed on the Payment Register Report.
Reconcile Payments
Reconcile Payments to Bank Statement Activity for the Period.
(Refer to Period-End Procedures for Oracle Cash Management)
Attention: The Payables Accounting process must be re-run for any reconciliation accounting entries generated to be Transferred to the General Ledger.
Transfer All Approved Invoices and Payments
Transfer All Approved Invoices and Payments to the General Ledger.
The Request Submit window facilitates the transfer of Oracle Payables accounting entries to the General Ledger and creates unposted journal entries.
Oracle Payables creates detail or summary journal entries from the invoice and payment activity when you post. The journal entries are posted in Oracle General Ledger to update account balances.
Submit the Payables Accounting Process to transfer approved invoice and payment accounting entries to the Oracle General Ledger interface tables.
Invoice journal entries, debit the expense or other account entered on an invoice distribution line, and credit the liability account nominated on the invoice.
Payment journal entries, debit the liability account and credit the cash account of the bank account used to pay and invoice.
The Payables Accounting Process transfers data to the General Ledger interface tables, creating journal entries for these invoice and payment transactions, if the option Submit Transfer to GL is set to Yes in the Parameter window.
The Payables Accounting Process will initiate the General Ledger Journal Import process, if the option Submit Journal Import is set to Yes in the Parameter window
Attention :The generated journal batch needs to be posted from within Oracle General Ledger. The journal batch may be automatically posted in General Ledger if the Source of Payables has been included in the Automatic Posting options established in General Ledger.
Review the Payables
Review the Payables to General Ledger Posting process After Completion.
The following reports can optionally be run to review the invoices and payments that were posted to Oracle General Ledger, from Oracle Payables, for the period that is to be closed, i.e. the current accounting period.
Payables Accounting Process Report
Payables Accounting Process Report Use this report to review accounting entries created by the Payables Accounting Process. The report has two sections:
Accounting Entries Audit Report. The audit report provides, in detail or summary, a listing of accounting entries created by the accounting process.
Accounting Entries Exception Report. The exception report lists in detail all accounting entries that were created with an error status and a description of that error. The Entries Exception Report is generated only when the accounting process encounters accounting entries that fail validation.
The Posted Invoices Register
This report is used to review invoices for which information has been posted to Oracle General Ledger to create journal entries, and can be used to verify that the total invoice amount equals the total invoice distribution amount. The report lists each Accounts Payable Liability Accounting Flexfield and the invoices posted to the account.
The Posted Payments Register
This report is used to review the payments posted to Oracle General Ledger during a particular accounting period, and can be used to review the payment activity for each bank account used during that period.
Submit the Unaccounted Transactions
Submit the Unaccounted Transactions Sweep Program.
The Unaccounted Transactions Sweep Program transfers unaccounted transactions from one accounting period to another. Because you cannot close a Payables period that has unaccounted transactions in it, if your accounting practices permit it, you might want to use this program to change the accounting date of the transactions to the next open period. For example, you have invoices for which you cannot resolve holds before the close, and your accounting practices allow you to change invoice distribution GL dates. Submit the program to change invoice distribution GL dates to the first day of the next open period so you can close the current period.
The Unaccounted Transactions Sweep Program will not roll forward accounted transactions, or accounted transactions with error. To create successful accounting entries for accounted transactions with error, correct any accounting errors and resubmit the Payables Accounting Process.
The program transfers unaccounted transactions to the period you specify by updating the GL dates to the first day of the new period. You can then close the accounting period from which Payables moved the invoices and payments.
In the Control Payables Periods window if you try to close a period and unaccounted transactions exist, then Payables opens a window. From the window you can submit the Unaccounted Transactions Sweep Program or you can submit a report to review accounting transactions that would be swept by the program. When you submit the Unaccounted Transactions Sweep Program, Payables automatically produces the Unaccounted Transactions Sweep Report to identify transactions that were redated and identify any transactions that need updating. If you submit the report in preliminary sweep mode the Unaccounted Transactions Sweep Review report shows which transactions will be redated if you submit the Unaccounted Transactions Sweep Program.
Close the Current Oracle Payables Period
Close the accounting period by using the Control Payables Periods window to set the Period Status to Closed.
Run the Accounts Payables Trial Balance Report
This report is used to facilitate reconciliation of the total accounts payable liabilities in Oracle Payables, with the Oracle General Ledger Creditors Control Account, for a specific accounting period. This report lists, and sub-totals, by vendor, all unpaid and partially paid invoices for which Oracle Payables created journal entries (i.e. posted invoices). These invoices represent the outstanding accounts payable liability for the organisation.
To obtain the most up-to-date trial balance for a given period, journal entries should be posted for the invoice and payment activity for the period, prior to running the report.
For reconciliation of Oracle Payables and Oracle General Ledger when posting is only performed at period end, the following reconciliation method can be used:
To the previous periods Accounts Payables Trial Balance, add the current period’s posted invoices (total amount from the Posted Invoices Register) , and subtract the current period’s posted payments (total cash plus discounts taken, from the Posted Payments Register) . The calculated amount should equal the balance for the current period’s Accounts Payables Trial Balance.
Attention: However, when posting to the general ledger from Oracle Payables is performed multiple times throughout each accounting period, the Posted Invoices Register and Posted Payments Register reports must be run after each posting run, for reconciliation of Oracle Payables liabilities with Oracle General Ledger’s Creditor Control.
[edit]Run Mass Additions Transfer to Oracle Assets
After you have completed all Payables transaction entry, and confirmed all invoice holds, and carry forwards, submit the Mass Additions Create progam to transfer capital invoice line distributions from Oracle Payables to Oracle Assets.
For foreign currency assets, Payables sends the invoice distribution amount in the converted functional currency. The mass addition line appears in Oracle Assets with the functional currency amount. Oracle Assets creates journal entries for the functional currency amount, so you must clear the foreign currency amount in your general ledger manually.
After you create mass additions, you can review them in the Prepare Mass Additions window in Oracle Assets.
It is recommended to do a final Mass Additions Create after the period close to ensure that all Payables invoices are 1) included in the correct period; and 2) any additional invoicing will become part of the next periods invoice and asset processing period.
Suggestion: If the volume of transactions in Accounts Payable requiring Assets update is large, you should consider running the Mass Additions Create process on a more regular basis.
Open the Next Oracle Payables Period
Open the next accounting period by using the Control Payables Periods window to set the Period Status to Open.
Run Reports for Tax Reporting Purposes (Optional)
A variety of standard reports can be used to provide tax information which is required to be reported to the relevant Tax Authority, including withholding tax.
The Financial Tax Register can be used to view the output from the Tax Reporting Ledger using Reports Exchange and Application Desktop Integrator (ADI). Using these products you can change the layout of the report, publish the report in different formats, and export the data to a tab delimited or HTML file.
The Tax Reporting Ledger consists of accounting information created in Oracle Receivables, Oracle Payables, and Oracle General Ledger. The Financial Tax Register uses this data to generate Tax Register reports using the Rxi reporting tool.
The following tax registers are available:
Tax Register
Interim Tax Register
Nonrecoverable Tax Register
The following summary levels are available within each Tax Register:
Transaction Header
Transaction Line
Accounting Line
Saturday, June 12, 2010
Global Intercompany System(Centra/GIS)-
PURPOSE
-------
Global InterCompany System Setup Release 11i SCOPE & APPLICATION-----Financial consultant, Oralce General Ledger Users
Overview
--------
The Global Interompany System manages intercompany transactionsbetween multiple Subsidiaries within a Global Organization.The main advantage of using GIS is to manage intercompany transactions that occur between sets of books. It streamlines the transaction and saves time bypreventing unbalanced intercompany transactions.Unbalanced entries could occur if two subsidiaries enter the transactions for services that occurs between them, using different exchange rates or currencies GIS prevents this from occurring by requiring the sender and the receiver (the two subsidiaries) to approve the amount, exchange rate and date.Once the transaction is approved each subsidiary then transfers and posts the balanced transaction to its operating set of books .
Features
--------
Account Generation: Define Auto Accounting Rules that establish pre set rules to automaticallygenerate account code combinations for the sender /receiver distribution and clearing lines.
Security
--------
This is enabled by assigning a specific responsibility for each GIS subsidiary by the System Administrator. The GIS subsidiary name is entered in the profile option: Intercompany :Subsidiary Systemat the responsibility level.Currency-------- When the currency of two subsidiaries are different and they enter transactions in GIS, the currency is converted to the respective target set of books currency.
Currency
--------
When the currency of two subsidiaries are different and they enter transactions in GIS, the currency is converted to the respective target set of books currency.
Import of Intercompany transactions from outside sources
--------------------------------------------------------
GIS is flexible enough to include an open interface table to import andvalidate inter company transactions into the GIS system. The intercompanylines can be automatically generated according to the auto accounting rulesdefined for these transactions. Once they are uploaded successfully they can be viewed in the GIS Enter Intercompany Transactions window .
------------------------------------------------------------------
Implementation Scenario 1: GIS With a Corporate Chart Of Accounts
------------------------------------------------------------------
If corporate and all the subsidiaries in a Global company use the same standard chart of accounts in their respective set of books, then the same standard chart of accounts can be used to create one common Inter companySet of Books in Oracle General Ledger and this will be used within GIS by all the subsidiaries to enter transactions between each other, and also with thecorporate company.Each subsidiary is set up as a GIS subsidiary giving it a unique name and the corresponding balancing segment value to enter transactions using the same Intercompany Set of books in GIS Example:
1. Create a set of books named 'Intercompany Set of Books' in Oracle General ledger. Assign a responsibility to this set of books. If you are not using the MRC feature of Oracle General Ledger when creating this set of books the option 'Neither' should be checked meaning this is neither a Primary or Reporting set of books .
2. Define this as a Subsidiary in GIS with Parent privileges, call it 'Corporate Intercompany'. Note for the GIS system every company should be defined as a subsidiary. Assign the following values in the 'Define Subsidiary Window'.
Subsidiary Corporate Intercompany
Chart of Accounts Standard Chart of accounts
Calendar Standard calendar
Currency USD
Company 01 (balancing segment value)
Repeat step two for subsidiary 2, subsidiary 3, subsidiary 4 and so on with different Name, Currency and a unique balancing segment value for the company field .
3. In the subsidiaries set of books field enter the respective standard set of books name for each of these subsidiaries . This is the set of books to which the transactions from GIS will be transferred once they are balanced and approved . The set of books entered here can be on the same local instance of Oracle system where the GIS is accessed or a remote Oracle instance (Physically on a another server or in a different country, also a non Oracle General Ledger system)
----------------------------------------------------------------------
Implementations Scenario 2: GIS without a Corporate Chart of Accounts
----------------------------------------------------------------------
This set up scenario can be used where all the given subsidiaries of a Global company and the Corporate company do NOT use the same standard chart of accountsIn this case each subsidiary may be at a different location,using different set of books and chart of accounts and different calendars .To facilitate intercompany transactions within GIS each of these Subsidiarieshave to have an additional set of books defined for the purpose of using it with the GIS system, which is separate and specific to each subsidiary.This example requires 6 set of books defined in total.Three of which are for GIS purpose defined in the instance where GIS is used andthe other three are respective standard accounting set of books and can be local or remote to the GIS instance.
Examples
--------
1.US Intercompany :Intercompany set of books (for entering GIS transactions)
2.US Corporate Office :US Set of books (GIS transactions get transferred to this SOB)3.Japan Intercompany :Japan intercompany set of books (for entering GIS transactions)
4.Japan Subsidiary :Japan Set of books (GIS transactions get transferred to this SOB)5.UK Intercompany :UK Intercompany books (for entering GIS transactions )
6.UK Subsidiary(remote) :UK Set of books (transactions get transferred to this SOB)
When a transaction is entered in GIS by the Japan Subsidiary for the UK subsidiary, the Japan subsidiary responsibility enters the transaction in GIS Japan intercompany SOB. The UK subsidiary responsibility approves this transaction in the GIS UK Intercompany SOB. The corporate responsibility OR the individual subsidiaryresponsibilities can run the "Transfer Intercompany Transactions " program totransfer the transactions to their corresponding set of books, which in this case would be 'Japan Subsidiary' set of books and 'UK Subsidiary(remote)' set of books'.
-------
Global InterCompany System Setup Release 11i SCOPE & APPLICATION-----Financial consultant, Oralce General Ledger Users
Overview
--------
The Global Interompany System manages intercompany transactionsbetween multiple Subsidiaries within a Global Organization.The main advantage of using GIS is to manage intercompany transactions that occur between sets of books. It streamlines the transaction and saves time bypreventing unbalanced intercompany transactions.Unbalanced entries could occur if two subsidiaries enter the transactions for services that occurs between them, using different exchange rates or currencies GIS prevents this from occurring by requiring the sender and the receiver (the two subsidiaries) to approve the amount, exchange rate and date.Once the transaction is approved each subsidiary then transfers and posts the balanced transaction to its operating set of books .
Features
--------
Account Generation: Define Auto Accounting Rules that establish pre set rules to automaticallygenerate account code combinations for the sender /receiver distribution and clearing lines.
Security
--------
This is enabled by assigning a specific responsibility for each GIS subsidiary by the System Administrator. The GIS subsidiary name is entered in the profile option: Intercompany :Subsidiary Systemat the responsibility level.Currency-------- When the currency of two subsidiaries are different and they enter transactions in GIS, the currency is converted to the respective target set of books currency.
Currency
--------
When the currency of two subsidiaries are different and they enter transactions in GIS, the currency is converted to the respective target set of books currency.
Import of Intercompany transactions from outside sources
--------------------------------------------------------
GIS is flexible enough to include an open interface table to import andvalidate inter company transactions into the GIS system. The intercompanylines can be automatically generated according to the auto accounting rulesdefined for these transactions. Once they are uploaded successfully they can be viewed in the GIS Enter Intercompany Transactions window .
------------------------------------------------------------------
Implementation Scenario 1: GIS With a Corporate Chart Of Accounts
------------------------------------------------------------------
If corporate and all the subsidiaries in a Global company use the same standard chart of accounts in their respective set of books, then the same standard chart of accounts can be used to create one common Inter companySet of Books in Oracle General Ledger and this will be used within GIS by all the subsidiaries to enter transactions between each other, and also with thecorporate company.Each subsidiary is set up as a GIS subsidiary giving it a unique name and the corresponding balancing segment value to enter transactions using the same Intercompany Set of books in GIS Example:
1. Create a set of books named 'Intercompany Set of Books' in Oracle General ledger. Assign a responsibility to this set of books. If you are not using the MRC feature of Oracle General Ledger when creating this set of books the option 'Neither' should be checked meaning this is neither a Primary or Reporting set of books .
2. Define this as a Subsidiary in GIS with Parent privileges, call it 'Corporate Intercompany'. Note for the GIS system every company should be defined as a subsidiary. Assign the following values in the 'Define Subsidiary Window'.
Subsidiary Corporate Intercompany
Chart of Accounts Standard Chart of accounts
Calendar Standard calendar
Currency USD
Company 01 (balancing segment value)
Repeat step two for subsidiary 2, subsidiary 3, subsidiary 4 and so on with different Name, Currency and a unique balancing segment value for the company field .
3. In the subsidiaries set of books field enter the respective standard set of books name for each of these subsidiaries . This is the set of books to which the transactions from GIS will be transferred once they are balanced and approved . The set of books entered here can be on the same local instance of Oracle system where the GIS is accessed or a remote Oracle instance (Physically on a another server or in a different country, also a non Oracle General Ledger system)
----------------------------------------------------------------------
Implementations Scenario 2: GIS without a Corporate Chart of Accounts
----------------------------------------------------------------------
This set up scenario can be used where all the given subsidiaries of a Global company and the Corporate company do NOT use the same standard chart of accountsIn this case each subsidiary may be at a different location,using different set of books and chart of accounts and different calendars .To facilitate intercompany transactions within GIS each of these Subsidiarieshave to have an additional set of books defined for the purpose of using it with the GIS system, which is separate and specific to each subsidiary.This example requires 6 set of books defined in total.Three of which are for GIS purpose defined in the instance where GIS is used andthe other three are respective standard accounting set of books and can be local or remote to the GIS instance.
Examples
--------
1.US Intercompany :Intercompany set of books (for entering GIS transactions)
2.US Corporate Office :US Set of books (GIS transactions get transferred to this SOB)3.Japan Intercompany :Japan intercompany set of books (for entering GIS transactions)
4.Japan Subsidiary :Japan Set of books (GIS transactions get transferred to this SOB)5.UK Intercompany :UK Intercompany books (for entering GIS transactions )
6.UK Subsidiary(remote) :UK Set of books (transactions get transferred to this SOB)
When a transaction is entered in GIS by the Japan Subsidiary for the UK subsidiary, the Japan subsidiary responsibility enters the transaction in GIS Japan intercompany SOB. The UK subsidiary responsibility approves this transaction in the GIS UK Intercompany SOB. The corporate responsibility OR the individual subsidiaryresponsibilities can run the "Transfer Intercompany Transactions " program totransfer the transactions to their corresponding set of books, which in this case would be 'Japan Subsidiary' set of books and 'UK Subsidiary(remote)' set of books'.
Subscribe to:
Posts (Atom)