AP Create Accounting Issue

Issue :

• Issue in Create accounting in Payable and got following error :
  An internal error has occurred in the program xla_ap_acct_hooks_pkg.main. Technical problem : Error encountered in product API for extract
  ORA-01403: no data found occurred in
  AP_Accounting_Pay_Pkg.Do_Pay_Accounting<- AP_SLA_PROCESSING_PKG.EXTRACT
  with parameters (&PARAMETERS)

Solution :

if following query return any row go and apply datafix : Doc ID 802929.1

•  SELECT distinct c.check_id , p.check_id correct_check_id
  FROM ap_checks_all c,
  ap_invoice_payments_all p,
  iby_docs_payable_all dp
  WHERE dp.calling_app_doc_unique_ref1 = p.check_id
  AND dp.calling_app_doc_unique_ref4 = p.invoice_payment_id
  AND c.payment_id = dp.payment_id
  AND c.check_id <> p.check_id AND dp.org_id = c.org_id

Accounts Receivable Reconciliation - Part one

Transaction Register for the Period
Applied Receipts Register for the Period
Un-Applied Receipts Register for the Period
Adjustments Register for the Period
Invoice Exceptions for the Period
Rounding Differences for the Period (*)
Credit Memo gain/loss for the Period(*)
(*) These can occur only in the case of foreign currency transactions.
The documentation details the following on how they derive the beginning and ending balances is here:

***--Quote--***
Beginning and Ending Balance:

---

To get the beginning and ending balances the report uses the same procedure. The As of Date for the Beginning period is GL Date Low-1 where as the As Of date for the Ending balance is GL Date High.

The report selects all transactions from AR_PAYMENTS_SCHEDULES_ALL table which are not receipts and have a GL Date equal to or less than the As Of date, AND a GL Date Closed which is greater than the As Of date.

For all records returned which are not credit memos, the report calculates and sums up all the receipt and credit memo applications which have a GL date of greater than the As Of date from the AR_RECEIVABLE_APPLICATIONS_ALL table. This total is termed as Accounted_Amount_Applied_To.

For all credit memos, the report calculates and sums up all the applications which have a GL date of greater than the AS Of date from the AR_RECEIVABLE_APPLICATIONS_ALL table. This is called the Accounted_Amount_Applied_From.

Any approved adjustments tied to these transactions are also calculated
and summed up that have a GL date of greater than the As Of date.

The report also sums up the Accounted Amount Due Remaining for all
transactions.

The final value the report calculates to get the Beginning/Ending Balance is the sum of all Unapplied, On-Account and Unidentified Receipts which were closed after the As Of date.

The Beginning and Ending balance is calculated using the following
equation:
Beginning/Ending Amount = Sum of all Accounted Amount Due Remaining
+ sum of Accounted_Amount_Applied_To
- sum of Accounted_Amount_Applied_From
- sum of all approved adjustements
+ ( - sum of all unapplied receipts)
***--End Quote--***

I am a novice when it comes to understanding the database structure of Oracle 11i and the above details are a little spotty on where and what fields are being summed up. So far I have written these (again note that I am trying to tie to the report…adding the functionality to select Domestic verses Export will come later and is not an issue) SQL statements which should give me an individual number for each part for which I can do calculations with to get the actual balance:

--AR Beginning Balance Queries --Sum ACCTD_AMOUNT_DUE_REMAINING
Select Sum(AR_PAYMENT_SCHEDULES_ALL.ACCTD_AMOUNT_DUE_REMAINING) as ACCTD_AMOUNT_DUE_REMAINING
From AR_PAYMENT_SCHEDULES_ALL
Where AR_PAYMENT_SCHEDULES_ALL.GL_DATE <= :date1
and AR_PAYMENT_SCHEDULES_ALL.GL_DATE_CLOSED > :date1
and AR_PAYMENT_SCHEDULES_ALL.org_id = :org
;

-- Sum ACCTD_AMOUNT_APPLIED_TOSelect Sum(AR_RECEIVABLE_APPLICATIONS_ALL.ACCTD_AMOUNT_APPLIED_TO) as ACCTD_AMOUNT_APPLIED_TO
From AR_RECEIVABLE_APPLICATIONS_ALL
Where AR_RECEIVABLE_APPLICATIONS_ALL.GL_DATE > :date1
and AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE 'CM'
and org_id = :org
;

--Sum ACCT_AMOUNT_APPLIED_FROMSelect Sum(AR_RECEIVABLE_APPLICATIONS_ALL.ACCTD_AMOUNT_APPLIED_FROM) as ACCTD_AMOUNT_APPLIED_FROM
From AR_RECEIVABLE_APPLICATIONS_ALL
Where AR_RECEIVABLE_APPLICATIONS_ALL.GL_DATE > :date1
and AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE = 'CM'
and org_id = :org
;

--AR Beginning Balance Queries (End) I am not sure how to calculate the “approved adjustments” or what they consider “unapplied receipts” can someone please help me? Also if you believe my above queries aren't pulling the correct totals please elaborate on that as well.

Thanks in advance!

UPDATE:
I think that I have figured out the “approved adjustments” & the “unapplied receipts” as the balances I am getting with those queires (below) are the same balances as the "Adjustment Register" and the "Unapplied Receipts Register" so that leads me to think one of the other queries I have above is wrong...can some one help me with that...pretty please!

--Adjustment Register Subtotal
Select Sum(AR_ADJUSTMENTS_ALL.ACCTD_AMOUNT) as Adjustment_Register_Subtotal
From AR_ADJUSTMENTS_ALL
Where AR_ADJUSTMENTS_ALL.GL_DATE between :date1 and :date2
and org_id = :org
;

-- Unapplied Receipts Register Subtotal
Select Sum(AR_RECEIVABLE_APPLICATIONS_ALL.ACCTD_AMOUNT_APPLIED_FROM) as Unapplied_Rec_Reg_Subtotal
From AR_RECEIVABLE_APPLICATIONS_ALL
Where AR_RECEIVABLE_APPLICATIONS_ALL.GL_DATE between :date1 and :date2
and AR_RECEIVABLE_APPLICATIONS_ALL.STATUS in ('ACC', 'UNAPP', 'UNID')
and AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE 'CM'
and org_id = :org

AR : Back to Basic(Technical Foundation)

This post is more on basic table structure of Oracle Account Receivable.
1.Table that holds AR Invoice data
The following tables can give most of the invoice information.

• RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
• RA_CUSTOMERS - Customer information
• RA_CUST_TRX_TYPES_ALL - Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• RA_CUSTOMER_TRX_LINES_ALL - Transaction Line information
• MTL_SYSTEM_ITEMS - Base table for item

The ER Diagram for a customer Transaction can be easily understood as:

2.Customer Payment
These are the main tables which holds Customer Payment information

• AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
• AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
• AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
• AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
• AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:

3. Accounting Link between the tables

• For Invoice:

ra_customers (This is for capturing customers information)
ra_customer_trx_all,
ra_cust_trx_types_all,
ar_payment_schedules_all,
ra_customer_trx_lines_all
and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .
ra_cust_trx_types_all.type IN ('INV')

• For the Receipts:

ar_receivable_applications_all,
ra_customer_trx_all,
ra_customer_trx_lines_all,
ar_cash_receipts_all
ar_receivable_applications_all.application_type = 'CASH',
ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,
ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id

Reference : http://www.oracleappshub.com/accounts-receivable/ar-back-to-basictechnical-foundation/

AR Receipts Issue

Issue :

Error in query  Receipts : CE_BANK_AND_ACCOUNT_UTIL.GET_MASKED_BANK_ACCT_NUM not found.

Solution  :

update  REMIT_BANK_ACCT_USE_ID with correct ID in  ar_cash_receipts_all