Fully paid prepayment showing "Unpaid" Status

Issue:

Fix:

Invalid data in AP_INVOICES_ALL

select * from AP_INVOICES_ALL where invoice_id =&invoice_id find the wrong data

PAYMENT_STATUS_FLAG P

if the invoice is fully paid, the status PAYMENT_STATUS_FLAG should be Y.

To implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:
.
create table SR_XXX_aia as
select *
from ap_invoices_all
where invoice_id =&invoice_id;
.
update ap_invoices_all
set payment_status_flag = 'Y'
where invoice_id =&invoice_id;

commit;

-- one row updated

3. If you are satisfied with the results, issue a commit.

4. Confirm that the data is corrected when viewed in the Oracle Applications.

Difference & Use of Fiscal Calendar, Depreciation Calendar and Prorate Calendar

Fiscal year where you define your fiscal year start and end date.

Depreciation calendar is the dividing the fiscal year in to different periods like 12 periods, 24 periods etc. Based on teh number of periods you define in depreciation calendar you need to run depreciation for those many periods

Prorate calendar is used only for internal depreciation calculation purpose like if you want depreciation to be based on by days then your prorate calendar has to be a daily calendar. If you want the depreciation only a monthly basis then it has to be a monthly calendar

Cannot Add or Update AP Invoice Distribution of PO Matched Invoices

Cannot change the charge account if PO has a destination type = Inventory or shop floor. This means that AP must use the accrual account

If user were allowed to change the account on the invoice, this would cause the accrual account to be out of balance. As a result the application restricts the user from changing the account at this point in time.

The user would need to discard the invoice line - which would reverse the invoice distribution in question. Then the user would need to correct the PO so the destination item was not inventory or shop floor. Then re-match the invoice.

This is intended functionality

Common Payable Invoice Issues with Fixes

INVOICES

INV.1 : Amount is null for reversed distribution. In this case, invoice cannot be accounted. Accounting fails with error There is no entered amount for the subledger journal entry line."

Resolution :Fix script will update the amount on the original and reversed distribution with 0.

INV.2: Payables Posted Invoice Register errors out due to invoice description containing Junk characters cause

Resolution :Fix script will remove the junk characters from the description.


INV.3: The default CCID on invoice line is -1. When this invoice line is queried on invoice workbench, the following error is displayed:
" APP-FND-00756: Cannot find combination CCID=-1.."

Resolution :Fix script will null out the default CCID.

RCA Bug: Preventive fix 11716947


INV.4: Mismatching operating unit on batches and its invoices.

Resolution :Fix script will null out org_id from batch.


INV.5: Invoice_type_lookup_code NULL for upgraded invoices

Resolution :Fix script will set the invoice type to 'STANDARD'


INV.6: Party_id or Party_site_id mismatch between invoices and ap_suppliers. Such invoices cannot be queried on Invoice workbench when searching on Supplier details.

Resolution :Fix script will set the party_id/party_site_id from Supplier records.


INV.7: Generate_dists flag incorrect for Expense report invoice. For such invoice, on clicking Distribution button, error "APP-SQLAP-10000: ORA-00001: unique
constraint (AP.AP_INVOICE_DISTRIBUTIONS_U1) violated Gen_Dists_From_Registration..." is thrown.

Resolution :Fix script will correct the generate_dists flag.


INV.8: Accrual/cash_posted_flag null for unaccounted distributions

Resolution :Fix script will update the accrual_posted_flag and cash_posted_flag to N.


INV.9: Discarded line has active distributions. In this case, invoice cannot be cancelled.

Resolution :Fix script will mark the discarded line as active.

User Action:User needs to re-discard the line.

RCA: 10226172

INVOICE CANCEL

CAN.1: Included tax amount is non-zero for cancelled invoice.

Resolution :Included_tax_amount is set to zero by the fix script.


CAN.2: Approval status for cancelled invoice is incorrect.

Resolution :Fix script will update the correct approval status.

INVOICE MATCHING


MAT.1: PO is not matched, but quantities/amounts are stamped on them.

Resolution :Fix script will clear out the quantity/amount columns on the PO.


MAT.2: UOM code mismatches between PO and AP.

Resolution :Fix script will sync up UOM codes between PO and AP.


MAT.3: Quantity_invoiced mismatches between invoice line and distribution.

Resolution :Fix script will sync the quantity_invoiced on invoice distributions with invoice line.

User Action:No Action required

RCA Bug    :13691308, 9651195, 19314744


MAT.4: Rcv_transaction_id is null for Invoice distributions matched to receipts.

Resolution :Fix script will stamp rcv_transaction on the invoice distributions.

RCA Bug: 10287715

TAX_AWT


TAX.1: AWT distributions of functional currency invoice have mismatching amount and base_amounts. Such an invoice cannot be accounted.

Resolution :Fix script will correct the base_amount column with the for the identified distributions.


TAX.2: 1099 Payment Report shows double amounts due to inactive supplier site, that is still marked as reportable.

Resolution :Fix script will clear the tax_reporting_site_flag for the inactive supplier sites identified.

RCA Bug: 9115178:R12.POS.A

PREPAYMENT


PRE.1: Operating unit/ Currency mismatches between the Prepayment applied and the standard invoice. Such an invoice cannot be accounted.

Resolution :Fix script will clear the prepayment applied details from the standard invoice.

User Action:Re-apply correct prepayment invoice.

RCA: 6394865


PRE.2: Prepayment unapplication date is before the prepayment application event date. The standard invoice cannotbe accounted.

Resolution :Fix script will update the prepayment events with correct dates.

RCA Bug: 12615938


PRE.3: Validated prepayment invoice has no data in ZX_DET_LINE_FACTORS.

Resolution :Fix script will mark the invoice for force re-validation.

User Action:

1. Validate the invoice.

2. Create and backup:

AP_TEMP_DATA_DRIVER_13579759 data driver table.

AP_DATA_DRIVER_13579759_BKP - Temporary data driver backup

AP_INV_DISTS_13579759_BKP - For below corruptions:

INV.1, INV.2, INV.8, MAT.2, MAT.3, MAT.4, TAX.1, PRE.1

AP_INV_LINES_13579759_BKP - For below corruptions:

INV.2, INV.3, INV.7, INV.9, CAN.1, MAT.2, PRE.1

AP_INVOICES_13579759_BKP - For below corruptions:

INV.5, INV.6, CAN.2, PRE.3

AP_BATCHES_13579759_BKP - For corruption : INV.4

AP_PREP_HIST_13579759_BKP - For corruptions: PRE.1,PRE.2

AP_XLA_EVENTS_13579759_BKP  - For corruptions: PRE.1,PRE.2

AP_XLA_HEADERS_13579759_BKP - For corruptions: PRE.1,INV.2

AP_XLA_LINES_13579759_BKP - For corruptions: PRE.1,INV.2

AP_SITES_13579759_BKP - For corruption : TAX.2

3.  Correct the identified transactions.

Note: The patch referenced in this article is the data fix portion.  It does not eliminate the root cause, therefore the issue could recur.  Please refer to the Root Cause table below and ensure the Root Cause patches are applied as soon a possible to eliminate future recurrences of the problem.


Why it is needed?

Various invoice corruptions can occur due to various invoice bugs. The GDF will correct the above corruptions and create appropriate backup tables.
Script: selection script : ap_one_off_scripts_sel.sql
fix script       : ap_one_off_scripts_fix.sql
The above mentioned issues are identified and corrected once the selection and fix scripts are executed.

When to use it?

This Generic Data Fix patch may have one or more root causes that may cause the condition that this patch corrects. This patch should only be applied when:

Referenced in a published Note as the data fix solution for the issue in that note,
When you are instructed to run it by Support or Development through a Service Request, or,
When it is referenced as a solution in a Diagnostic Script featured in a published Note (e.g: Note 1360390.1).
If you have applied the code fix patches and the data fix patch per instructions from this Note and the issue continues to recur please log an SR with support.

How to use it?

Warning: Run the scripts in test instance, verify the results and only then apply to the production instance.

1. Download and apply patch, it will not run any script.

  AP.A Customers:
  Download and apply Patch 21497508:R12.AP.A  for R12.0.x

  AP.B Customers:
  Download and apply Patch 21497508:R12.AP.B for R12.1.x      

  AP.C Customers:  
  Download Patch 21497508:R12.AP.C and follow note 1582525.1 for ADOP enabled R12.2.x instances.

Note: If a password is required to download the patch, please open a service request to get the proper password.

2. The patch delivers 2 scripts and the latest datafix package code:

ap_one_off_scripts_sel.sql - Select script used to find and report the problem data.
ap_one_off_scripts_fix.sql - Fix script used to fix the problem data.
apgdfalb.pls and apgdfals.pls - Code that delivers the latest datafix package code, package name = AP_Acctg_Data_Fix_PKG. This package provides procedures and functions used by the GDF e.g. backing up the data, displaying the data, etc..

Note: This patch does NOT alter ANY Financials application code.

3. Apply the patch. Run script ap_one_off_scripts_sel.sql.

It expects several parameters to be passed:

1. Start_Date for selection of invoices from a given date format (dd/mm/yyyy) (Eg 01/11/2012)
2. End_Date for selection of invoices to a given date     format (dd/mm/yyyy) (Eg 22/12/2013)
3. Component value :Please enter any one of the values 'INVOICES' ,'CANCEL','MATCHING', 'TAX', 'PREPAY'
4. Email ID as an optional parameter if, user wants to receive generated log file on their email id.
It will create the following:

a) Driver table AP_TEMP_DATA_DRIVER_13579759

b) log file 13579759-diag-<DD_MI_SS>.html which displays the affected transactions.

Note: The instance does not need to be brought down to apply this patch, you can apply it in hotpatch mode e.g. adpatch option=hotpatch.

4.  For the component entered, the corresponding transactions are identified.

5.  Check the report 13579759-diag-<DD_MI_SS>.html to identify/review the affected transactions.

6.  Update process_flag in each driver table AP_TEMP_DATA_DRIVER_13579759 to Y for all the transactions that should be operated on.

7.  To correct the identified records in Step 3 have to be corrected, then run ap_one_off_scripts_fix.sql

    Example:  sqlplus apps/apps@finpqe10
    @$AP_TOP/patch/115/sql/ap_one_off_scripts_fix.sql

Following parameters are required to execute this script

1. User Name  Format Eg.CBROWN
2. Responsibility Format Eg. Payables Manager
3. Email ID as an optional parameter if, user wants to receive generated log file on their email id.

    This script will generate log file 13579759-fix-<DD_MI_SS>.html.
    This log will also list the backup table names

8.  Please note, for corruptions across different components, the selection script should be run each time with the correct component parameter value.
    Run the fix script to fix the transactions in the driver table. Re-run the selection script for each component.

9.  Check the results. Ensure all the 'User Action' listed under the corruptions in 'Description' are carried out.

10.  To verify if fix script corrected mismatch, run the  selection script ap_one_off_scripts_sel.sql for the same component. The script will not pick up transactions that were corrected by the fix script.

11. For any issues with the script/results please contact Oracle support and supply files 13579759-diag-<DD_MI_SS>.html and 13579759-fix-<DD_MI_SS>.html.
    Log files will be created in the following directory:

    SELECT decode(instr(value,','),0,value,
           SUBSTR (value,1,instr(value,',') - 1))
    FROM   v$parameter
    WHERE  name = 'utl_file_dir';

12. Please apply the listed RCA patches for each corruption type

Note:  GDF#17558081 is must post request patch for MAT1.1, MAT1.2,MAT1.3 corruption types.

Knowledge sources from Oracle support.

AutoInvoice Date Derivation: GL Date, Invoice Date, Due Date, Ship Date, Billing Date, Rule Date

During the process of AutoInvoice you can enter the General Ledger (GL) date and the Transaction date for a particular transaction explicitly in the relevant columns of the RA_INTERFACE_LINES_ALL table or you can allow the AutoInvoice process to derive the dates.

If the dates are provided, AutoInvoice will typically use them. There are a few scenarios where dates cannot be provided as input parameters; these scenarios are discussed in section 2.e titled GL Date Validation.

If the dates are not provided, they will be derived. Date Derivation of the General Ledger date and Transaction date during the process of AutoInvoice depends on the following:


1. The columns that are populated in the RA_INTERFACE_LINES_ALL table, namely:


GL_DATE
TRX_DATE
SHIP_DATE_ACTUAL
SALES_ORDER_DATE
RULE_START_DATE

2. Selection of the Derive Date Option in the Accounting Information Tab of the Transaction Sources window.

3. Whether or not Invoicing Rules and Accounting Rules are used.

4. Setting of the GL Date in a Closed Period option in the AutoInvoice Options tab of the particular Transaction Batch Source.

5. Default Date entered while submitting the AutoInvoice Program.

Once imported as a transaction in receivables, the GL date used by AutoInvoice is stored in the table.column: RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE.



Note: The GL date discussed in this note is for the initial accounting entry booked to record the creation of the invoice. For invoices with rules, Revenue Recognition will generate additional accounting entries and may impact the dates seen by users when reviewing transactions in Receivables.