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.
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.
No comments:
Post a Comment