R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
1
Accounting Entries Payables (AP) (Oracle Document Reference)
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
2
Types of Accounts Account Type Liability
Prepayment
Bill Payable
Description
Accounting statement which tracks how much a person or business owes a creditor. A common example of a liability is accounts payable. Accounts payable arise when a company purchases goods or services on credit from a supplier. When the company pays the supplier, the company's accounts payable is reduced. A prepayment is a type of invoice you enter to pay an advance payment for to a supplier or employee. This account captures this payment information. For example, you may need to pay a deposit on a lease, or you may need to pay an employee an advance for travel expenses. You can later apply the prepayment to one or more invoices or expense reports you receive from the supplier or employee to offset the amount paid to them. Types of Prepayment: o Temporary o Permanent Temporary: - Temporary prepayments can be applied to invoices or expense reports you receive. - For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel's invoice arrives, apply the prepayment to the invoice to reduce the amount you pay. Permanent: - Permanent prepayments cannot be applied to invoices. - For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.
You can use Bills Payable to control the timing of your payments and, therefore, control your cash flow. A bill payable, formerly known as a future dated payment, instructs your bank to disburse funds to your supplier's bank on a specific date known as the maturity date. Other commonly used terms for this type of payment are notes payable or promissory notes.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
3
Account Type Discount Taken
Description
PO Rate Variance Gain/Loss
Expense Clearing
Miscellaneous
Retainage
Realized Gain/Loss
Payables use this account to record the discounts you take on payments. Use the Discounts Taken and Lost report to identify payments for which you could have taken a discount, but did not. If you find that you are losing discounts, you can change your system and supplier defaults and modify your payment batch selection criteria to make sure that you take all valid discounts. Payables use these accounts to record the exchange rate variance gains/losses between your purchase order and invoice. Payables calculate these amounts during Approval. You can view the distribution amount for these variances in the Distribution Inquiry window. This will be a default liability account for iExpenses expenses reports imported into Oracle Payables. The Expense Clearing Account field is also available in the Card Program window. If you define the Expense Clearing Account field in the Card Program window, the value you define there will take precedence over the value in the Oracle Payables Financial Options window. Enter a Miscellaneous account if you want to use a specific account for miscellaneous charges. If you leave this value blank, then miscellaneous amounts are prorated across the Item lines and charged to the Item line accounts. Retainage represents funds withheld from payment to ensure that the contractor finishes work as agreed. The buying organization releases these funds only after verifying that the contractor has fulfilled all contractual obligations. Retainage is also called "retention" or "contractual withholds". With Oracle's Complex Work feature, contract administrator can negotiate retainage terms with the contractor and capture these as part of the contract. These terms include Retainage Rate and Maximum Retainage Amount. When you pay a foreign currency invoice, if the exchange rate changes between invoice entry and payment, Payables automatically calculates the realized gain or loss and records it in this account. If you reconcile your payments, Payables also records a realized gain or loss if there is a difference in the exchange rate between the payment date and reconciliation date.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
4
Account Type Rounding
Description
Freight
Cash Clearing
Cash
Bank Charges
Bank Error
Payables use this account when you use multiple currencies and the payment currency does not round within the payment currency precision. For example, if a payment rounds to 100.001 UKS, the payment will be for 100.00 UKS, and the rounding error of .001 will be recorded in this account. If you enable the Automatically Create Freight Distribution Payables option, you must enter a freight account. Payables use this account as the default freight account for an invoice. You can override this account during invoice entry. The cash clearing account you associate with a payment document. You use this account if you integrate Oracle Payables with Oracle Cash Management, or if you generate future dated payment documents. Oracle Payables credits this account instead of your Asset (Cash) account and debits your Liability account when you post uncleared payments. Oracle Payables debits this account and credits your Asset (Cash) account once you clear your payments in Oracle Cash Management. You must enable the Allow Reconciliation Accounting Payables option to be able to enter a cash clearing account for a bank account and payment document. Cash Management enables you to reconcile payments you created in Oracle Payables against your bank statements. You can use Cash Management to: Automatically account for the delay in bank clearing of payments by crediting a cash clearing account when you issue a payment, and clear the balance with a cash account when you reconcile the payment. If you have enabled the Allow Reconciliation Accounting Payables option and you are using Oracle Cash Management to reconcile your payments, enter the bank charges account you are associating with a bank account. When you reconcile your invoice payments using Oracle Cash Management, Payables creates accounting entries to record your bank charges using this account. The account you enter here defaults to the Bank Charges account field in the GL Accounts region of the Payment Documents window. If you have enabled the Allow Reconciliation Accounting Payables option and you are using Oracle Cash Management to reconcile your payments, enter the bank errors account you are associating with a bank account. When you reconcile your invoice payment using Oracle Cash Management, Payables creates accounting entries to record any bank errors using this account. The account you enter here defaults to the Bank Errors account field in the GL Accounts region of the Payment Documents window.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
5
Accounting Setups & Controls Accounting Setups: Payables Accounts are setup & controlled at, Sno. 1. 2. 3. 4. 5. 6. 7.
Setups Financial Options Payable Options Supplier Site Bank Accounts Withholding Tax Invoice ebusinss-Tax
Accounting Derivation Hierarchy: Since accounts are defined @ multiple levels, the below hierarchy is used to derive the same.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
6
For some accounts the accounting details will be defaulted from Payables Organization Level setups i.e. how the account should behave or which method should follow they are, Default Account Sno. 1. 2. 3. 4. 5.
Prepayment Bills Payable Discount Interest Withholding Tax
Setup Level
Payables Option Accounting Options (tab) Payables Option Withholding (tab)
Types of Accounts & Setup levels: Account Type Liability Prepayment Bills Payable Discount Taken PO Rate variance Gain / Loss Expense Clearing Miscellaneous Retainage Realized Gain / Loss Rounding Fright Tax Difference Internet Expense & Internet Liability Cash & Cash Clearing Bank Charges & Bank Errors
Financial Options
Payables Options
Supplier Site
Bank Withholding Accounts Tax
Invoice
eBizTax
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
7
Accounts & Its Qualifiers:
Account Type
Segment Qualifier
Liability Prepayment Bills Payable Discount Taken PO Rate variance Gain PO Rate variance Loss Expense Clearing Miscellaneous Retainage Realized Gain Realized Loss Rounding Fright Tax Difference Internet Expense Internet Liability Cash Cash Clearing Bank Charges Bank Errors
Liability Asset Liability Expense Revenue Expense Expense Expense Liability Revenue Expense Expense Expense Liability Expense Liability Asset Asset Expense Expense
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
8
Setups Details:
Financial Options:
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
9
Payables Option:
Accounting Option:
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
10
Currency:
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
11
Invoice:
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
12
Interest:
Supplier Site:
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
13
Bank Account:
Withholding Tax:
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
14
Identifying Accounting Events in Payables
Predefined Event Entities:
Application Oracle Payables Oracle Payables
Entity Name AP Invoices AP Payments
Description Invoices Payments
Gapless Event Processing No No
Event Classes:
Entity AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Invoices AP Payments AP Payments AP Payments AP Payments
Event Class Name Credit Memos Debit Memos Expense Reports Invoices Invoice_Burden Prepayment Applications Prepayment Invoices Prepayments Prepayment_Burden Prepay_Application_Burden Standard Invoices Future Dated Payments Payments Reconciled Payments Refunds
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
15
Process Categories: Below are the payables predefined process categories.
Process Categories Invoice Payments Third Party Merge
Accounting Event Class Options: For the below even classes accounting entries will be created. Event Class
Process Category
Default Journal Category
Balance Type
Burden for Invoices Burden for Prepayment Applications Burden for Prepayments Credit Memos
Invoices Invoices
Purchase Invoices Purchase Invoices
Encumbrance Encumbrance
Invoices Invoices
Purchase Invoices Purchase Invoices
Debit Memos
Invoices
Purchase Invoices
Invoices
Invoices
Purchase Invoices
Prepayment Applications
Invoices
Purchase Invoices
Prepayments
Invoices
Purchase Invoices
Future Dated Payments Payments
Payments Payments
Payments Payments
Reconciled Payments
Payments
Reconciled Payments
Refunds
Payments
Payments
Encumbrance Actual and Encumbrance Actual and Encumbrance Actual and Encumbrance Actual and Encumbrance Actual and Encumbrance Actual Actual and Encumbrance Actual and Encumbrance Actual and Encumbrance
Actual balance is the actual balance available in the account. Encumbrance is the fund reserved for pre-expenditures such as purchase requisition etc. (which has not reflected in the actual balance yet). R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
16
Payables Accounting Definitions First let’s u dersta d types of Accounting Method’s and its usage. There are two different types of accounting methods,
Sno. Principal Accounting Method 1. Cash Basis 2. Accrual
Cash basis Accounting - Cash-based accounting recognizes income when money is received. - An expense is recognized when it's paid.
Accrual Accounting - Accrual-based accounting recognizes income when goods are shipped or services are rendered. - An expense is recognized when the business is obligated to pay it.
Example 1: Your computer installation business finishes a job in November, and doesn't get paid until three months later in January. o Under the cash method, you would record the payment in January. o Under the accrual method, you would record the income in your November books.
Example 2: You purchase a new laser printer on credit in May and pay $1,000 for it in July, two months later. o Using the cash method, you would record a $1,000 payment for the month of July, the month when the money is actually paid. o Under the accrual method, you would record the $1,000 payment in May, when you take the laser printer and become obligated to pay for it.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
17
Advantages and disadvantages of the accrual method:
- While the accrual method shows the ebb and flow of business income and debts more accurately, it may leave you in the dark as to what cash reserves are available, which could result in a serious cash flow problem. - For instance, your income ledger may show thousands of dollars in sales, while in reality your bank account is empty because your customers haven't paid you yet.
Advantages and disadvantages of the cash method:
- Though the cash method provides a more accurate picture of how much actual cash your business has, it may offer a misleading picture of longer-term profitability. - Under the cash method, for instance, your books may show one month to be spectacularly profitable, when actually sales have been slow and, by coincidence, a lot of credit customers paid their bills in that month.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
18
Subledger Accounting Components:
There are 5 major components involved,
O e of the ajor o po e ts is Subledger Accounting Method (SLAM . This is othi g ut the Principal Accounting Methods we discussed above.
Five Subledger Accounting Methods are seeded in the Application. Application Accounting Definitions Encumbrance Accrual Encumbrance Cash Accrual Basis Cash Basis
Subledger Accounting Methods Accrual with Encumbrance Accounting Cash with Encumbrance Accounting Standard Accrual Standard Cash
When we want budgetary control to be associated with accounting method choose Encumbrance Accounting .
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
19
Subledger Accounting Method (and) Create Accounting:
Accrual Basis: Event Class Assignments Credit Memos Debit Memos Future Dated Payments Invoices Payments Prepayment Applications Prepayments Reconciled Payments Refunds
Event Type Assignments All All All
Create Accounting Yes Yes Yes
All All All
Yes Yes Yes
All All
Yes Yes
All
Yes
Journal Line Definition Assignments Accrual, Credit Memos All Accrual, Debit Memos All Accrual, Future Dated Payments All Accrual, Invoices All Accrual, Payments All Accrual, Prepayment Applications All Accrual, Prepayments All Accrual, Reconciled Payments All Accrual, Refunds All
Cash Basis: Event Class Assignments Credit Memos Debit Memos Future Dated Payments Invoices Payments Prepayment Applications Prepayments Reconciled Payments Refunds
Event Type Assignments All All All
Create Accounting No No Yes
All All All
No Yes Yes
All All All
No Yes Yes
Journal Line Definition Assignments
Cash, Future Dated Payments All Cash, Payments All Cash, Prepayment Applications All Cash, Reconciled Payments All Cash, Refunds All
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
20
Let’s see hat happe s he budgetary control i.e. with Encumbrance Accounting is enabled.
Accrual with Encumbrance:
Event Class Assignments Burden for Invoices
Event Type Create Journal Line Definition Assignments Assignments Accounting All Yes Invoice Burden and Project Encumbrance ALL Burden for Prepayment All Yes Prepayment Application Burden and Applications Project Encumbrance ALL Burden for All Yes Prepayment Burden and Project Prepayments Encumbrance ALL Credit Memos All Yes Accrual, Credit Memos All; Encumbrance Credit Memos All; Encumbrance Reversal for Credit Memo Debit Memos All Yes Accrual, Debit Memos All; Encumbrance Debit Memos All; Encumbrance Reversal for Debit Memos Future Dated Payments All Yes Accrual, Future Dated Payments All Invoices All Yes Accrual, Invoices All; Encumbrance for Invoices; Encumbrance Reversal for Invoices Payments All Yes Accrual, Payments All Prepayment All Yes Accrual, Prepayment Applications All; Applications Encumbrance Reversal for Prepayment Applications; Encumbrance Prepayment Applications Prepayments All Yes Accrual, Prepayments All; Encumbrance for Prepayments All; Encumbrance Reversal for Prepayments Reconciled Payments All Yes Accrual, Reconciled Payments All Refunds All Yes Accrual, Refunds All
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
21
Cash Basis with Encumbrance:
Event Class Assignments Burden for Invoices
Event Type Assignments All
Burden for Prepayment Applications Burden for Prepayments Credit Memos Debit Memos Future Dated Payments Invoices Payments
All
Create Journal Line Definition Assignments Accounting Yes Invoice Burden and Project Encumbrance ALL Yes Prepayment Application Burden and Project Encumbrance ALL
All
Yes
All All All
Yes Yes Yes
All All
Yes Yes
Prepayment Applications
All
Yes
Prepayments Reconciled Payments
All All
Yes Yes
Refunds
All
Yes
Prepayment Burden and Project Encumbrance ALL Encumbrance Credit Memos All Encumbrance Debit Memos All Cash, Future Dated Payments All Encumbrance for Invoices Cash, Payments All; Encumbrance Reversal for Payments Encumbrance Reversal for Prepayment Applications; Cash, Prepayment Applications All; Encumbrance Prepayment Applications Encumbrance for Prepayments All Cash, Reconciled Payments All; Encumbrance Reversal for Reconciled Payments Cash, Refunds All; Encumbrance Reversal for Refunds
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
22
Accounting Hits for Payables Event Classes Invoice
Accounts Expense (AP Accrual)
Debit
Tax
Freight Retainage
Credit
Comments Used: During Manual Invoice Creation. Defaulted: AP Accrual is defaulted when invoice is matched to PO/Receipt for goods items. Defined @ Oracle eBiz-Tax & inserted upon invoice validation Defaulted: he li e type is Freight . Defaulted: When invoice is matched to Service Procurement PO/Receipts. Note** it is Debited when invoice of type Retainage Release is reated.
Liability Invoice Price Variance
Sourced from supplier site. When invoice is matched to PO/Receipts. Account hit: Price entered in invoice is different from that specified in PO. For Inventory items Inventory AP Accrual account defined @ Organization Parameters.
Quantity Variance
For Expense items Expense AP Accrual Account When invoice is matched to PO/Receipts. Account hit: Quantity entered in invoice is different from that specified in PO. For Inventory items Invoice Price Variance defined @ Organization Parameters.
PO Exchange Rate Variance
For Expense items PO Charge Account When invoice is matched to PO/Receipts. Account hit: Foreign Currency rate in invoice
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
23
is different from PO (at the time of creation). For Inventory items Exchange PO Rate Variance / Loss Account For Expense items PO Charge Account When invoice is matched to PO/Receipts. Tax on invoice is different than PO.
Tax Variance
Below tax variance can occur, 1. Tax Rate Variance (TRV) 2. Tax Invoice Price Variance (TIPV) 3. Tax Exchange Rate Variance (TERV) Tax Rate Variance: The difference b/w invoice price & PO Distribution due to difference in tax rate. Tax Invoice Price Variance: The difference b/w invoice & PO Distribution price. For the above variances (TRV & TIPV) the accounting hit as below, For Inventory items Invoice Price Variance Account @ Organization Parameters. For Expense items PO Charge Account Tax Exchange Rate Variance: The difference b/w invoice & PO Distribution exchange rate.
Internet Expense Internet Liability
For Inventory items Exchange PO Rate Variance / Loss Account For Expense items PO Charge Account. During: Whe i oi e type Interest is created on condition that Interest = System Account. If prorate across invoice then Original Invoice Distribution will be debited.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
24
Prepayment
Accounts Prepayment Liability
Debit
Credit
Comments Sourced from supplier site.
Prepayment Application
Accounts Liability Prepayment Tax Difference
Debit
Credit
Comments Sourced from supplier site. Defaulted: When tax applied for invoice applied for prepayment.
>
Tax applied for invoice is < applied for prepayment then, We need to make sure Tax difference is not credited.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
25
Payment
Maturity Date: In finance, maturity or maturity date refers to the final payment date of a loan or other financial instrument, at which point the principal (and all remaining interest) is due to be paid.
Accounts Liability Cash Clearing Discount Taken Withholding Tax Realized Gain / Loss
Accounts Liability Withholding Tax Bills Payables
Maturity Date = Current Date Debit Credit Comments Sourced from bank account. Defaulted: When accelerated cash payment term is made during discount period. Created when payment on condition that apply withhold tax during payment time. It is hit when foreign currency rate upon payment is different upon accrual (Invoice).
Maturity Date = Future Date Debit Credit Comments Defaulted: From bank account / supplier site based on options setup @ Payables Options form.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
26
Payment Maturity
Accounts Bills Payables Cash Clearing Discount Taken
Debit
On Payment Date Credit Comments
Clearing
Accounts Cash Clearing Bank Charger / Bank Errors
Debit
Credit
Comments It is used for difference b/w cleared amount and transaction amount. Cash Management checks the Tolerance differences parameter to determine whether the difference is due to charges / errors.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
27
Create Accounting Payables (AP) (Oracle Document Reference)
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
28
About Create Accounting Why Create Accounting Program?
It picks eligible accounting events and creates sub-ledger journal entries. Also, ge erates a report alled Subledger Accounting Program Report to do u e t the results.
Methods to create Accounting Events: Two ways we can generate accounting entries for Payables (AP) module. 1. Using Create Accounting Program 2. Create online Accounting (Either for Single / batch Transactions)
How to view the Generated Accounting Entries? Generated entries are available as below,
Oracle Apps Forms in View Accounting / View Accounting Entries Window. “u it Subledger Accounting Program Report . Usage of Subledger Accounting Report: o Lists Successful events and the subledger journal entries created for the events. o Errors for failed events
What are the documents for which create accounting program can be run in Payables (AP)?
Invoice Payments Invoice Batch Payment Batch
Modes of Creation Accounting program,
Draft Final Final and Post R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
29
Can we adjust the Accounting Entries Generated? Yes, only if the accounting program is run in Draft Mode.
Reports to review accounting information: Below is the seeded reports used to review accounting entries details,
Journal Entries report Account Analysis report Third Party Balances report Multi period Accounting reports Subledger Period Close Exceptions reports Open Account Balances Listing
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
30
Create Accounting Program - Parameters
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
31
Parameter Ledger
Process Category End Date Mode Errors Only Report Transfer to GL Post in GL GL Batch Name Include User Transaction identifier
Required? Yes
Description Ledger for which accounting events to be generated. Ca restri t the a ou ti g e e ts ased o profile SLA: Enable Data Access Security Optional We can restrict events based on specific process category. Yes Process only those events <= End Date. Yes Select below modes based on business case, DRAFT, FINAL & FINAL POST. Yes If you want to process the failed events with status error. Yes Select report to be generated in Summary / detailed report. Yes, based Applies When Mode = Final. on mode Yes, based Applies when Mode = Final Post. on mode Optional In real-time batch name will be given to filter the records and if Transfer to GL option is given. Yes If the report to be displayed with user identifier names and values.
Note: GL General Ledger
Transfer Journal Entries to GL: To transfer journal entries from SLA to GL run the program
Run Transfer Journal Entries to GL program. Creation Accounting Progra ith Tra sfer to GL as Yes
Transfer Journal Entries to GL Report: Run the report Transfer to Journal Entries to GL Report a d list the elo , o Transfer to GL Summary o General Errors Journal Posting: Post the Jour al e tries i to Ora le GL usi g progra
Journal Import .
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
32
Accrual & Accounting Process
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
33
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
34
Accounting Entries – Flow
Let’s
ap the a o e pro ess ith o urre t progra s,
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
35
Accounting Entries Table Hits:
Note** The link between Sub-ledgers and XLS_Distribution_Links is based on the Source distribution Type. Source Distribution type is nothing but the AP Distribution Tables like AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_HIST_DISTS.
Similarly for rest of the modules the distributions are linked.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
36
Period End Closing - Payables Sno. Activity
Details
1
Complete all transactions
2
Invoice & Payment Batches
3
Future Dated Payments
4
Essential Reports
Invoices & Credits, prepayments, expense reports, Invoice Imports & payments. Approve all un-approved invoices manually (or) submit program "Payables Approval". Confirm or Cancel all incomplete payment batches. If you use future dated payments, submit the Update Matured Future Dated Payment Status Program. This will update the status of matured future dated payments to Negotiable so you can account for them. Invoice Hold Report: Lists holds applied to an invoice and for which journal entries & posting cannot be created.
5
Unaccounted Transactions
6
Transfer to GL
7
Reconciliation
Journal Entries Report: It lists the accounting information that has been accounted with Error in detail/summary listing. Resolve all unaccounted transactions by submitting report Unaccounted Transactions Report . It lists all transaction with reasons why accounting entries cannot be generated for the same. Transfer all accounting entries to GL by submitting Transfer Journal Entries to GL . Run the below reports to reconcile, Account Payable Trial Balance Report Posted Invoice Register Posted payment Register Bank Statement reconciliation, Bank Statement Import Auto Reconciliation Program (or) Bank Statement Import & AutoReconciliation
8
Sweep Program
Ru the progra Unposted Invoice and Payment Sweep . It tra sfers all u a ou ted tra sa tio s fro one accounting period to another. This program will not forward accounting transactions with errors.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
37
Payables Reconciliation Process Steps: We can perform below reconciliation activity, 1. Reconcile Payables Transactions 2. Reconcile Accounts Payables Trial Balance (for given period) 3. Reconcile Payable Transactions to GL
Reconcile Payables Transactions
Pre-requisites: before executing of the below reports ross he k Transfer Journal Entries to GL is su itted already. Run below reports, Account Payable Trial Balance Report Posted Invoice Register Posted payment Register Note** run trial balance report for last period & current period.
Reconcile Accounts Payables Trial Balance Current Period Trial Balance = Last Period AP Trial Balance
+ Current Period Posted Invoice Register
Current Period Posted Payment Register For example, consider we are closing accounting period of June month, June Trial Balance = May Period AP Trial
Reconcile Payable Transactions to GL
Balance + June Posted Invoice Register - June Posted Payment Register Ru the report Account Payables Trial Balance Report . The o pare this account balance with GL account balance for current period.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
38
Create Accounting - Errors & Solutions There are various stages at which errors might occur, below are the possibilities.
Case 1: Program Create Accounting Program Create Accounting Report
Completion Status Success Error
Case 2: Errors during Online Accounting.
Case 3: Program Create Accounting Program
Completion Status Error
Case 4: Period Closing Errors
Case 5: Program Accounts Payable Trial Balance Report
Completion Status Error
Let see the troubleshooting steps for each cases.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
39
Case 1 Case Description:
Program Create Accounting Program Create Accounting Report
Completion Status Success Error
Possible Errors:
Error Code
Error Message
Error 95353
Subledger Accounting was unable to derive an accounting code combination. The total debits and the total credits do not balance for this subledger journal entry The applied-to sources provided for this line are invalid or incomplete. The segment value for the account code combination specified in line XX is not assigned to the ledger There are either non-accountable events existing in the system The account code combination id &CCID specified for line &LINE_NUM does not exist. The GL date &GL_DATE is not in an open or a future enterable period. This line cannot be accounted until the accounting event for the application Payables that it references has been fully accounted. The log of Accounting Program shows error: The following REVERSAL events could not be processed. Error: AP_ACCTG_EVENT_SKIPPED
Error 95340 Error 0 Error 95311 Error 95937 Error 95318 Error 95325 ----
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
40
Error Code
Error Message
Error 95353
Subledger Accounting was unable to derive an accounting code combination.
Cause: Mostly this error occurs when a derivation rule does not exists and system is unable to derive a rule for specific accounts defined @OU level setups. The accounts as below,
Future Dated Payments Discount Account Cash Clearing Account Liability Account Realize Loss Account Exchange Rate & Tax Exchange Rate Variances Tax Difference Account Bank Error Account
Solution: Check all accounts are setup properly @organization level like Financial Options, Payables Options, Payables System Setups and Bank Setups
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
41
Error Code
Error Message
Error 95340
The total debits and the total credits do not balance for this subledger journal entry
Cause: For each Event Class & for its type a Journal Line type exists. Each Journal type associated with conditions to create a balance entry. This error occurs when,
If oracle Code fix Patches not applied Journal Line types not defined properly for Event class & its types. If conditions associated with journal line type is not correct.
Below are some of the event class & its types, for which this error triggered.
Event Class Reconcile Payments Prepayment Applications Prepayment Applications Payment Payment Payment Refunds Future Dated Payments
Event Types Payment Cleared Prepayment Applied Prepayment Application Adjusted Payment Created Payment Cancelled Payment Adjusted Refunds Recorded Payments Matured
Solution: Oracle suggested if required patch is applied it resolves most of these balancing issues. The details of patches as below, Event Type Payment Created Payment Adjusted Payment Matured Payment Cancelled Prepayment Applied Prepayment Adjusted Refund recorded Payment Cleared Payment Clearing Adjustment
Solution Patch 10179705 and Patch 10278211 Patch 9699315 Patch 7626300:R12.XLA.A Patch 8319065:R12.XLA.A Patch 11772495 and Patch 10173936 R12.1 Patch 9474821:R12.AP.B Patch 10623481 Patch 18964136 Patch 18964136
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
42
Error Code
Error Message
Error 0
The applied-to sources provided for this line are invalid or incomplete.
Cause: The error triggers when,
The transaction line may not be applied to a previous transaction (or) The journal line definition is incorrectly defined to support business flow functionality.
Solution:
Oracle provided required code fix patches to resolve these issues. Even applying the patches if the issues exists, cross check the journal line definitions.
Error Code
Error Message
Error 95311
The segment value for the account code combination specified in line XX is not assigned to the ledger
Cause: There are numerous causes for this error some of them are,
AP_SYSTEM_PARAMETERS columns might contain -1 Value. Create Accounting error while cancelling Invoices. Cash clearing account might not be defined properly (or) the accounting combination might be restricted using CVR / SR. The BSV/Company Code segment is not valid for that ledger.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
43
Solution:
When defining a new OU, properly define Organization level setups like Financial Options, Payables Options, payables System Setup and bank account setups. If secondary ledger is used, cross check whether it is properly mapped with primary ledger. If any incorrect LE/BSV is used correct it.
Error Code
Error Message
Error 95937
There are either non-accountable events existing in the system or events which could not be processed because no data could be found on transaction objects.
Cause:
There are events ready for accounting but they are not picked because the subledger extract data is incomplete or invalid. Due to Code issues. FND_CONNECTION_TAGGING issue.
Solution:
Apply the code fix patch 8744290. To find the transactions/accounting events which may be causing the issue, use the following query
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
44
xe.application_id , xte.entity_code "Transaction Type" , xte.source_id_int_1 "Transaction Id", xte.transaction_number "Transaction Number", xe.event_id, xet.event_class_code , xe.event_type_code , xe.event_status_code , xe.process_status_Code , xe.budgetary_control_flag FROM xla_events xe , xla_transaction_entities_upg xte , xla_event_types_b xet WHERE xte.application_id = &P_APPLICATION_ID and xte.entity_id = xe.entity_id and xet.application_id = xe.application_id and xet.event_type_code = xe.event_type_code and xe.application_id = &P_APPLICATION_ID and xe.request_id = &P_CREATE_ACCT_REQUEST_ID and NOT EXISTS ( select 1 from xla_ae_headers xah where xah.event_id = xe.event_id and xah.application_id = &P_APPLICATION_ID ); SELECT
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
45
Error Code
Error Message
Error 95318
The account code combination id &CCID specified for line &LINE_NUM does not exist.
Cause:
Invoices goes to FUND_CHECK_HOLD due to invalid CCID on NONREC_TAX distributions.
Solution:
This is a bug if this exists apply Patch: 9021265:R12.AP.A.
Error Code
Error Message
Error 95325
The GL date &GL_DATE is not in an open or a future enterable period.
Cause:
Period was not opened for all ledgers used.
Solution:
Open GL periods for appropriate ledgers.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
46
Error Code Error
Error Message This line cannot be accounted until the accounting event for the application Payables that it references has been fully accounted.
Cause: There are many numerous causes for this error, below are the possible ones.
Affected Transactions Payment
Payment Created/Clearing Invoices Payment Adjustment & Payment Clearing Adjustment
Prepayment
Error Code Error
Cause Null invoice line number on tax lines Accounting events not synchronized. Journal Line Definition not defined properly. Accounting ID is not synchronized between AP_INVOICE_PAYMENTS_ALL & AP_INVOICE_HISTORY_ALL Historical data might not be upgraded during migration in table XLA_DISTRIBUTION_LINKS_TABLE Total credit & debit does not match. Amount entered does not match. Proper events classes & types might not be defined for account derivation. There might be deferred tax on prepayment invoice
Error Message The log of Accounting Program shows error: The following REVERSAL events could not be processed.
When:
Cannot account for Payment Unclearing of a check Cannot account for Invoice Cancellation of an invoice
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
47
Cause:
The cause of the current issue is invalid / incorrect data in XLA_TRANSACTION_ENTITIES. The invoice or payment has 2 rows in XLA_TRANSACTION_ENTITIES and PAYMENT CLEARED event has different entity_id in xla_events than the PAYMENT CREATED and PAYMENT UNCLEARED events.
Solution:
Apply patch 9651687:R12.XLA.B.
Error Code Error
Error Message Error: AP_ACCTG_EVENT_SKIPPED
Cause:
If any corrupted data in the payments results in failure of "Create Accounting" program. To skip those corrupted data logic is built such that those events would be stamped with error "AP_ACCTG_EVENT_SKIPPED".
Solution:
Check for the other error in XLA_ACCOUNTING_ERRORS,this would be the actual error which prevents the payment from accounting.So the accounting error "AP_ACCTG_EVENT_SKIPPED" can be ignored.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
48
Case 2 Case Description: Errors during Online Accounting. Cause: For some of the transactions online accounting cannot be done they are prepayment applications, voided payments etc.
Solution: Ru the Payables Accounting process .
Case 3
Case Description: Program Create Accounting Program
Completion Status Error
Cause: When the Create Accounting Program completes with Error status.
Solution: Review the log files XLAACCUP and XLAACCPB.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
49
Case 4 Case Description: Period Closing Errors
Below errors will be triggered while trying to close the period.
Error: APP-SQLAP 10304: You cannot close the period because exceptions exist: This is the standard message displayed when trying to close the period while not all transactions been accounted. Solution: Run the Period Close Exception Report and try to account them.
ORA-20001: APP-XLA-95103: An internal error occurred: When you have exceptions reported and the unaccounted transactions program spawned by Sweep program errors out. Solution: Assign the profile option "Initialization SQL Statement - Custom" to value "begin null; end;" at the User level
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
50
Case 5 Case Description:
Program Accounts Payable Trial Balance Report
Completion Status Error
Cause: There are many, mentioned few below,
Invalid reference XLA_SRS_SUBLEDGERS in value set attached to segment Report Definition. Invoices with Voided Payments or Unapplied Prepayments are Reported Incorrectly on the Accounts Payable Trial Balance. Performance issues with the report.
Solution: Check below patches are applied.
Patch 8340128 Patches 6996476 and 7293021 Patch.9602525:R12.XLA.A.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
51
Create Accounting – Performance Issues Issue Description:
Sometimes the program runs for long time to complete (or) runs slowly.
Below bugs been identified for this performance issue. Bug# Bug 9366101
Bug 9351919
Description Create accounting process takes 2 hours of time, of which around 1 hour is taken to execute the FUN queries. The cause of the poor performance is due to the lack of the join condition on the LEDGER_ID column. Filters on Application_ID were missing in several queries, and leading/index hints were also missing.
Solution:
Step 1: Download & apply below patches.
Patch 9366101:R12.FUN.A Patch 9351919:R12.XLA.A.
Step 2: Cross the below files & its versions.
$FUN_TOP/patch/115/sql/funbalpkgb.pls 120.31.12000000.15 $XLA_TOP/patch/115/sql/xlajebal.pkb 120.153.12000000.28
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
52
GL Transfer (SLA GL) (Oracle Document Reference)
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
53
Process:
Data Corruption Issues during SLA GL Transfer
Once the accounting entries are generated for all transactions in sub-ledgers it has to be transferred from Sub-ledger to General Ledger.
During the transfer process some data corruption issues might occur some of them are listed below. 1. 2. 3. 4. 5.
Data with negative LEDGER_ID in the General Ledger (GL) tables. Data marked as "Transferred" in SLA but which has not reached GL. Data marked as "Not Transferred" in SLA, but which has reached GL. Multiple posting issues. Data which is in GL but which has been deleted from SLA (This is NOT applicable for 11i data) 6. Journal Import failing with EP01 error while running the Transfer Journal Entries to GL program 7. Data accounted in 11i and transferred to GL in R12 which is missing gl sl link id.
Note** Never cancel the Journal Import program. R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
54
How to identify data corruption issues in GL Transfer? Run XLA GL Diagnostics test.
How to check whether Diagnostics test can be performer or not? It can be performed only when below patches are applied.
for 12.0.x versions: Patch 8513940:R12.XLA.A for 12.1.x versions: Patch 8765953:R12.XLA.B
Steps to run Diagnostics tests:
Navigation: Application Diagnostics Diagnostic (Tab) Follow the below steps,
No li k Select Application a d sele t Subledger Accounting . In GL_DIAGNOSTICS group, select the test "XLA GL Diagnostics".
Note** The XLA GL Diagnostics test has to be run for all the ledgers associated to a primary ledger. To identify the ledgers use the below query, SELECT gled.ledger_id as LEDGER_ID FROM gl_ledger_relationships glr, gl_ledgers gled WHERE glr.primary_ledger_id = &p_ledger_id -- Put primary ledger id here AND glr.application_id = 101 AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC') AND glr.relationship_type_code = 'SUBLEDGER') OR (glr.target_ledger_category_code IN ('PRIMARY') AND glr.relationship_type_code = 'NONE')) AND glr.target_ledger_id = gled.ledger_id AND Nvl(gled.complete_flag,'Y') = 'Y' GROUP BY gled.ledger_id;
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
55
Input Parameters:
Below are the required parameters,
Responsibility ID Ledger Id Application Id Je Source Name Start Date in YYYY-MM-DD End Date in YYYY-MM-DD
To get the Application id and corresponding je_source_name, run the query below : SELECT application_id "Application id" ,je_source_name "Je Source" FROM xla_subledgers;
Note** to avoid perfor a ce issues please ru script Gather Schema Statistics .
Test Output Details:
The output contains below information,
Version of GL TRANSFER Pkg Key Patches GL_LEDGERS GL_LEDGER_RELATIONSHIPS Negative LEDGER_ID in GL Accounted in 11i but Transferred to GL in R12 with Null GL_SL_LINK_ID Marked as Transferred in XLA but NOT in GL Marked as transferred in XLA but REVERSED in GL Data Exists in GL but Its Corresponding Data Is Deleted in XLA Data in GL but Deleted From XLA Showing XLA Reference Columns Marked as Not Transferred in XLA but in GL Multiple Transfer to GL
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
56
FAQ: Transfer to GL (Oracle Document Reference)
What are the tables involved in Transfer to GL and GL Posting? XLA_AE_HEADERS XLA_AE_LINES XLA_DISTRIBUTION_LINKS GL_INTERFACE XLA_GLT_
GL_JE_BATCHES GL_JE_HEADERS GL_JE_LINES GL_IMPORT_TEFERENCES
Subledger Tables Interface Tables
GL Tables
What are all different modes available to transfer journals from SLA to GL? Modes
Description
ONLINE
During Online accounting use Final Post optio for spe ifi document i.e. invoice etc. Submit Create Accounting program with Transfer to GL parameter as Y . “u itti g progra Transfer Journal Entries to GL separately / i standalone mode. This method uses interface table XLA_GLT_
BATCH CONCURRENT REQUEST
How to find the transfer status of accounting entries in SLA? XLA_AE_HEADERS Column Value gl_transfer_status_code Y gl_transfer_date Populated with a date value i.e. not null column group_id Populated with a group id i.e. not null column
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
57
How to find transfer status and differentiate 11i Vs. R12 accounting entries after upgrade? Column gl_transfer_status_code gl_transfer_date group_id upg_batch_id
XLA_AE_HEADERS Value Y Populated with a date value i.e. not null column Populated with a group id i.e. not null column Not null column. This indicates 11i data.
How to merge accounting lines in XLA_AE_LINES table? In Journal Line Type setups, the accounting lines in XLA_AE_LINES tables can be merged. Navigation: Accounting Setup Subledger Accounting Setup Accounting Methods Builder Journal Entry Setups Define Journal Line types Choose Field Merge Matching Lines . This field can have below options.
Merge Options Description ALL
Lines in XLA_DISTRIBUTION_LINKS (specific Accounting Header) + Journal Line Type (Specific like liability etc.) = XLA_AE_LINES Records
DR/CR
Lines in XLA_DISTRIBUTION_LINKS (specific Accounting Header) + Journal Line Type (Specific like liability etc.) + Debit Side Entries = XLA_AE_LINES Records No merging happens.
NO
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
58
What are all different setup options to transfer journals in Detail / Summary format? Tables involved: The journals from XLA tables will be transferred to GL table. XLA_AE_HEADERS, XLA_AE_LINES GL_JE_HEADERS, GL_JE_LINES
Below are the setup options available which decides whether to Merge the transactions in GL tables or not.
Subledger Accounting Options: This setup to determine accounting header can be summarized or not.
Journal Line Types: This setup determines whether accounting lines in XLA_AE_LINES can be summarized or not.
Subledger Accounting Option:
Navigation: Accounting Setup Ledger Setup Define Accounting Setups Query for the ledger & Click Update icon. Go to Subledger Accounting Options, in Primary ledger section click update icon. “etup the field General Ledger Journal Entry Summarization
ith o e of the below values,
Summarize by GL Period Summarize by GL Date No Summarization
Journal Line Types:
Navigation: Accounting Setup Subledger Accounting Setup Accounting Methods Builder Journal Entry Setups Define Journal Line types Choose Field Transfer to GL . Note** Lines cannot be summarized if the headers are not summarized.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
59
General Ledger Journal Entry Summarization Summarize(By GL Date or Period)
JLT- Transfer to GL
System Expected Behavior
Summary
Summarize(By GL Date or Period)
Detail
No Summarization
Detail
No Summarization
Summary
Group by GL Period/GL Date
Detail / Summary
Summarized Level at, GL_JE_HEADERS GL_JE_LINES Summarized Level at, GL_JE_HEADERS Detail Level at, GL_JE_LINES Detail Level at, GL_JE_HEADERS GL_JE_LINES Detail Level at, GL_JE_HEADERS GL_JE_LINES Merged on condition Same Journal Category & GL Period/ GL Date, GL_JE_HEADERS Detail Level at, gl_sl_link_id will be populated GL_JE_LINES
How do you link GL Data with SLA Data? Usi g ta le GL_IMPORT_REFERENCES . The joi GL_IMPORT_REFERENCES.gl_sl_link_id GL_JE_BATCHES.group_id
o ditio as elo .
=> XLA_AE_LINES.gl_sl_link_id => XLA_AE_HEADERS.group_id
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
60
If gl_sl_link_id does not exist in table GL_IMPORT_REFERENCES how relationship is derived between GL & SLA data?
Usually using REFERENCE Columns i.e. columns from REFERENCE5 to REFERENCE10 in GL_IMPORT_REFERENCES we can able to identify the relationship.
These columns populated with entity_id, event_id, ae_header_id, ae_line_num, accounted_cr, accounted_dr.
Note** But sometimes these REFERENCE columns might not be populated during transfer process. This happens when the patch 7512923:R12.XLA.A (for R12.0.x) is not applied.
How many group_ids are created during the transfer process when there is a primary ledger and associated ALC/reporting ledger?
The primary ledger data and associated ALC/reporting ledger data goes into one group_id. Data from this one group_id can be split across multiple GL Batches based on other criteria, such as je_category/date/period. However, one GL Batch can only be associated to one group_id.
How many group_ids are created during transfer process when there is a primary ledger and a secondary ledger?
Two group IDs are created--One for the primary ledger and one for the secondary ledger. Two journal import processes are spawned in this case, one for each group_id. If there is failure in the journal import for either ledger, the transfer is rolled back for both ledgers.
Is there a setup to prevent spawning the Journal Import on instances where a third party (e.g., Peoplesoft) GL is used? The profile option "Disable Journal Import" (when set to Yes) prevents the GL Transfer from invoking the Journal Import. The interface table used in this case is GL_INTERFACE. Setting this profile option is not recommended if you are using Oracle General Ledger. The profile option is hidden and disabled in the latest code.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
61
When data will be available in XLA_TRIAL_BALANCES table? When Journal Import program successfully completed, the data will be available in this table.
Note**
Data from XLA_DISTRIBUTION_LINKS is used to populate the XLA_TRIAL_BALANCES table. It can also be repopulated during a trial balance rebuild using the "Open Account Balances Data Manager" process.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
62
Journal Import Errors (Oracle Document Reference)
Below are the possible stages where we frequently face issues/errors during journal import.
Importing Journals from SLA / Create Accounting. Transfer to GL does not pick rows/fails.
When e ru
Journal Import Execution Report the elo errors
ight o ur,
Errors like EP01, EF01, EE01, EU02, EC12 etc.
Journal Import Execution Report Ends in ERROR but no errors are displayed in output also data is marked as transferred in SLA
Error 'LEZL0023: Journal Import can only process data from one table at a time'.
Error 'ORA-01460: unimplemented or unreasonable conversion requested'.
Transfer to GL or Create Accounting concurrent request was cancelled after Journal Import was spawned.
GL transfer is not picking data when ledger is secondary and is disabled.
Let’s ie steps to trou leshoot a o e errors.
Step 1: Get the below details.
Ledger ID Application ID JE Source Start Date & End Date
Use the below query to get the above details. SELECT application_id, je_source_name FROM xla_subledgers;
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
63
Error: Journal Import Execution Report shows errors like EP01, EF01 etc. Errors
Solution
If the Journal Import Execution Report shows Check Period is open if not open it. Period Error EP01 this date is not in any open or future enterable period. EP03 this date is not within any period in an open encumbrance year. EP04 this date is not a business day. EP05 There is no business days in this period. If the Journal Import Execution Report shows Cross check the account setup i.e. whether Flexfield Errors like any CVR (Cross validation rule) exists for this. EF01 This Accounting Flexfield is inactive for this accounting date. EF02 Detail posting not allowed for this Accounting Flexfield. EF03 Disabled Accounting Flexfield. EF04 this is an invalid Accounting Flexfield. Check your cross-validation rules and segment values. EF05 There is no Accounting Flexfield with this Code Combination ID.
Error: Journal Import Execution Report Ends in ERROR but no errors are displayed in output also data is marked as transferred in SLA
Check the Patch 6748312:R12.GL.A is applied, if not apply it.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
64
Error: Journal Import Execution Report shows error 'LEZL0023: Journal Import can only process data from one table at a time' Error: Journal Import Execution Report shows error 'ORA-01460: unimplemented or unreasonable conversion requested'. Error: Transfer to GL or Create Accounting concurrent request was cancelled after Journal Import was spawned/ DB got shutdown while running Journal Import But you can see XLA_AE_Headers. Gl_transfer_status_code='S'
For all a o e error’s he k hether the latest Patch 8691650:R12.XLA.A is applied. If not apply it & resubmit the program.
Error: Another common issue seen is GL transfer is not picking data and ledger is secondary ledger Secondary ledger is disabled meaning relationship_enabled_flag in gl ledger is 'N'. Because of this GL Tra sfer does ’t pi k se o dary ledger data. Execute the below update query to fix the above error.
UPDATE XLA_AE_HEADERS XAH SET XAH.GL_TRANSFER_STATUS_CODE = 'NT' WHERE XAH.APPLICATION_ID = &p_application_id AND XAH.LEDGER_ID = &p_ledger_id -- ledger id of secondary ledger which is disabled AND XAH.GL_TRANSFER_STATUS_CODE = 'N' AND XAH.ACCOUNTING_ENTRY_STATUS_CODE = 'F' AND XAH.ACCOUNTING_DATE BETWEEN TO_DATE('&p_start_date','DD-MM-YYYY') AND TO_DATE('&p_end_date','DD-MM-YYYY') AND EXISTS ( SELECT 1 FROM XLA_EVENTS XE WHERE XE.APPLICATION_ID = &p_application_id AND XE.EVENT_ID = XAH.EVENT_ID AND XE.EVENT_ID = XAH.EVENT_ID AND XE.EVENT_STATUS_CODE = 'P' AND XE.PROCESS_STATUS_CODE = 'P'); R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
65
FAQ’s: Journal Entry & Reversal Journal Entry:
How to Delete a Journal batch (or) How to delete a Unposted Journal batch? Query for the journal Batch, follow the below steps.
Journals Enter Close the Form Click Review Batch button. Query for the batch. Using delete icon deletes the unposted journal.
In Journal Entry form why Accounting Periods is not getting listed? The Accounting period LOV lists o ly period ith status Open & Future Enterable .
When a Journal batch cannot be Deleted / modified? Below are the scenarios where journal batch cannot be deleted / modified. 1. 2. 3. 4.
Source is frozen Batch has fund reversed or in process of reserving. Batch is Posted (or) in process of posting. Batch is Approved (or) in process of approval.
If the journal batch comes from Sub-Ledgers it should not be updated. Updating the same may not synchronize the accounting information between Primary ledger & Sub-Ledger. Alternate Solution: Instead of updating a new journal can be defined & adjusted.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
66
Can I restrict users from entering negative amounts in journal lines? As of now no standard functionality available, since some of the GL programs allows negative amounts. Using form personalization we can implement the same.
Can we copy journal entries? No, we cannot.
Is it possible to hide batch & Headers appearing in journal screen? No.
Journal Reversing: Instead of reversing single journal entry, the complete batch is reversed and posted. How this can be corrected? If Unposted: Delete the unposted reversal batches. If Posted: Reverse the Reversal batch and repost them.
In R12, the Unposted Journals can be reversed? In R12, only posted journals can be reversed.
Why a journal entry with a source setup for automatic reversal was not reversed?
General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted again manually to pick the new entered journals.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
67
Does the reversal program generate separate reversal batches for each journal in a batch that is reversed?
Yes. When a batch is reversed, General Ledger creates one reversing batch, with one single journal entry, for each journal entry in the reversed batch. Therefore a single batch with multiple journals generates multiple reversal batches with a single journal.
In R12, why can I not modify an Unposted reversal journal?
This is the default functionality in Release 12, as a reversal should match the original journal. Using the profile GL: Edit Reverse Journals can be set to allow the modification.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
68
Period Close (AP) (Oracle Document Reference)
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
69
Payables Period Close: Diagnostics Script to be used: ap_pclose_detect_pkg.sql
Current Version of Script: 120.13 (Updated on 16-Jul-2014), check for latest & execute. Purpose of Script:
It allows us to identify the transactions which affects period close with the corrective action suggested resolving the same. No data is created, updated, or deleted by this script. This will not close the period. It produces a HTML output all errors/problems related to transactions along with corrective steps to be taken.
Belo is the Wrapper “ ripts a aila le i side ap_pclose_detect_pkg.sql .
period_close.sql: This script can be used to submit the validation procedure for a particular period. ap_gdf_detect_pkg.sql: To run the AP Data Validation Report and to check for data corruptions and the corresponding GDFs available. period_close.sql: To validate all invoices, payments, in a specified period for specified operating units.
Steps for Diagnostics:
Step 1: Run the wrapper SQL file "period_close.sql" to submit the detection procedure. The detection procedure will create an output file of the format AP-PCLOSE-.html and a log file of the format AP-PCLOSE-.log. Also, it will indicate the location of this file and the log file.
Step 2: Review the output file to determine if any transactions have any issue, if yes follow the corrective action steps else it is safe to close the current period.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
70
Examples to execute the script,
Script Usage To check all transactions stopping period close for a particular period To validate all transactions for one of your operating units
To run the same validations but including both org1 and org2
To run the diagnostics along with the AP Data Validation Report to check for more corruptions
Script Execution SQL> BEGIN ap_pclose_detect_pkg.main_pc( p_per_name => '', p_ledger_id => ); END; SQL> BEGIN ap_pclose_detect_pkg.main_pc( p_per_name => '', p_ledger_id => , p_org_ids => ''); END; SQL> BEGIN ap_pclose_detect_pkg.main_pc( p_per_name => '', p_ledger_id => , p_org_ids => ','); END; SQL> BEGIN ap_gdf_detect_pkg.main_pc( p_per_name => '', p_ledger_id => , p_master_gdf => 'Y'); END;
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
71
Error:
Common issues during Period Closing in AP
APP-SQLAP 10304 Cannot Close Period - No Data On Period Exception Report, No Unaccounted Transactions
When: While trying to close the period, where exception report display no data. Cause: There as a issue i pa kage AP_PERIOD_CLOSE_PKG
hile retur i g alue.
Fix: Apply Pat h Patch 9509700 .
Error: AP_SWEEP_ACCESS_ERROR/Insufficient Access Error Appears Attempting to Sweep From Control Payables Period Form
When: User su
its Unaccounted Transaction Sweep fro
Co trol paya les period for .
Cause: Sometimes MOAC Setups is not done properly (or) responsibility does not have access to all OU’s assig ed to ledger. Fix: Che k profile MO: Security Profile is set properly & he k for MOAC steps.
Error: APP-SQLAP-10304 Cannot Close Period And No Data On Period Exception Report.
When: No Exceptions is listed i Period Close Exception Report message still some exceptions exists.
ut the syste
triggers
Cause: For some invoice lines, distributions might be missing.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
72
Fix:
Revalidate the Invoice & Execute Create Accounting for the same (or) Delete the invoice.
Error: R12 Orphan Accounting Events Preventing Closing Period in Payables.
When: The Period Close Exception Report errors out e ause of U Errors.
a ted A ou ti g
Cause: Due to budgetary issues like fund check hold in invoice etc. Fix:
Check PSA critical Patch (14563642:R12.PSA.A R12.0 / 14563642:R12.PSA.B R12.1) is applied. “u it Budgetary Control Optimizer Program . This ill lea -up the orphan processed/unprocessed BC Events.
Error: Cannot Close Period Due To Untransferred Headers, Multiperiod Accounting.
When: Some of the Header information for accounting events is not transferred i.e. Transfer Journal Entries to GL concurrent program does not pick up the problem XLA headers. Cause:
Headers might have created before period open (or)
Fix: Ru the Complete Multiperiod Accounting program . This program checks,
The GL dates of all incomplete journal entries that have a GL date that is on or before the end GL date specified in the request parameters and completes these entries as their GL dates fall into open periods.
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
73
Please rate this material @ www.planetsource-code.com.
Thank you
R12: P2P Accounting & Troubleshooting Notes - by Dinesh Kumar S
74