Oracle EBS应付发票验证不了：Invoice Validation Errors with The sum of the Percentage Recovery Rates is great

EBS版本：12.0.6

 

1、问题症状：

发票验证时提示以下错误提示

中文环境

 

英文环境

 

2、引起原因猜测：

计税时，用户习惯性地双击鼠标造成的。

3、跑GDF(参考官方文档1360390.1)，可以看到有以下错误

 

SELECT /*+ ordered use_nl(ai, ah) */       ai.invoice_id,       ai.invoice_num,       ai.invoice_date,       ai.org_id,       ah.hold_lookup_code        FROM (SELECT /*+ qb_name(iview) */               :p_invoice_id ---->有问题的INVOICE_ID                FROM dual) invs,             ap_invoices_all ai,             ap_holds_all ah       WHERE ai.invoice_id = invs.invoice_id         AND nvl(ai.historical_flag,'N') <> 'Y'         AND ai.cancelled_dateis null         AND ah.invoice_id = ai.invoice_id         AND ah.hold_lookup_codeIN ('DIST VARIANCE','LINE VARIANCE')         AND ah.release_lookup_codeis null         AND EXISTS       (SELECT /*+ no_unnest                   index(zl zx_lines_n4)                   index(zd zx_rec_nrec_dist_u2) */               null                FROM zx_lines zl, zx_rec_nrec_dist zd               WHERE zl.application_id =200                 AND zl.trx_id = ai.invoice_id                 AND zd.tax_line_id = zl.tax_line_id                 AND zd.application_id = zl.application_id                 AND zd.entity_code = zl.entity_code                 AND zd.event_class_code = zl.event_class_code                 AND zd.trx_id = zl.trx_id               GROUP BY zd.tax_line_id, zl.tax_amt              HAVING zl.tax_amt <>SUM(zd.rec_nrec_tax_amt))

4、参考文档：EB-Tax Data Corruption Issues &Recommended Solutions (Doc ID 1316316.1)

查看Section 5:Missing Reversal Tax Distributions for Tax Distributions/ Tax lines fordiscarded or cancelled Transaction Lines are not marked as canceled causingInvoice to be placed on Hold / Error AP_ERR_TAX_DIST_SYNC during invoicecancellation

其中代码如下：本次处理的问题出在2）的语句

IDENTIFICATION SQLs -------------------   -- 1.Invoices with tax lines of discarded or cancelled item lines that are not marked as canceled or do not have zero tax amount SELECT/*+ leading(ai) parallel(ai) index(ail AP_INVOICE_LINES_U1)*/          DISTINCT ai.invoice_id,          ai.invoice_date,          ai.org_id     FROM ap_invoices_all ai,          ap_invoice_lines_all ail,          ap_holds_all ah    WHERENVL(ai.historical_flag,'N') <>'Y'      AND ai.cancelled_dateISNULL      AND ah.invoice_id = ai.invoice_id      AND ah.hold_lookup_codeIN ('DIST VARIANCE','LINE VARIANCE')      AND ah.release_lookup_codeISNULL      AND ail.invoice_id = ai.invoice_id      AND ail.line_type_lookup_code <>'TAX'      AND (NVL(ail.discarded_flag,'N') = 'Y'OR           NVL(ail.cancelled_flag,'N') ='Y')      ANDEXISTS          (SELECT/*+ first_rows(1) index(zl ZX_LINES_U1) */                  1             FROM zx_lines zl            WHERE zl.application_id =200              AND zl.entity_code ='AP_INVOICES'              AND zl.event_class_codeIN                   ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')              AND zl.trx_id = ai.invoice_id              AND zl.trx_level_type ='LINE'              AND zl.trx_line_id = ail.line_number              AND (NVL(zl.cancel_flag,'N') <> 'Y'OR zl.tax_amt <>0));   -- 2.Invoices on Dist Variance or Line Variance hold where tax lines tax amount is not matching with the sum of amounts of their distributions SELECT/*+ leading ah */DISTINCT ai.invoice_id,          ai.invoice_date,          ai.org_id     FROM ap_invoices_all ai,          ap_holds_all ah    WHERENVL(ai.historical_flag,'N') <>'Y'      AND ai.cancelled_dateISNULL      AND ai.invoice_id = ah.invoice_id      AND ah.hold_lookup_codeIN ('DIST VARIANCE','LINE VARIANCE')      AND ah.release_lookup_codeISNULL      ANDEXISTS          (SELECT/*+ first_rows(1) leading zl index(zl ZX_LINES_N4) index(zd ZX_REC_NREC_DIST_U2) */                  zd.tax_line_id,                  zl.tax_amt,                  SUM(zd.rec_nrec_tax_amt) tot_rec_nrec_amt             FROM zx_lines zl,                  zx_rec_nrec_dist zd            WHERE zl.application_id =200              AND zl.trx_id = ai.invoice_id              AND zd.tax_line_id = zl.tax_line_id              AND zd.application_id = zl.application_id              AND zd.entity_code = zl.entity_code              AND zd.event_class_code = zl.event_class_code              AND zd.trx_id = zl.trx_id            GROUPBY zd.tax_line_id, zl.tax_amt           HAVING zl.tax_amt <>SUM(zd.rec_nrec_tax_amt)); -- 3.Invoices on Dist Variance or Line Variance hold where amount of tax distrbutions in ZX is not matching with the amount of tax distributions in AP SELECT/*+ leading ah */DISTINCT ai.invoice_id,          ai.invoice_date,          ai.org_id     FROM ap_invoices_all ai,          ap_holds_all ah    WHERENVL(ai.historical_flag,'N') <>'Y'      AND ai.cancelled_dateISNULL      AND ai.invoice_id = ah.invoice_id      AND ah.hold_lookup_codeIN ('DIST VARIANCE','LINE VARIANCE')      AND ah.release_lookup_codeISNULL      AND (EXISTS            (SELECT/*+ first_rows(1) leading aid index(zd ZX_REC_NREC_DIST_U1) */                    aid.detail_tax_dist_id,                    zd.rec_nrec_tax_amt,                    SUM(aid.amount) ap_rec_nrec_amt               FROM ap_invoice_distributions_all aid,                    zx_rec_nrec_dist zd              WHERE aid.invoice_id = ai.invoice_id                AND aid.line_type_lookup_codeIN                     ('REC_TAX','NONREC_TAX','TRV','TIPV','TERV')                AND aid.detail_tax_dist_idISNOTNULL                AND zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id              GROUPBY aid.detail_tax_dist_id, zd.rec_nrec_tax_amt             HAVING zd.rec_nrec_tax_amt <>SUM(aid.amount))           OR           EXISTS            (SELECT/*+ first_rows(1) leading asad index(zd ZX_REC_NREC_DIST_U1) */                    1               FROM ap_self_assessed_tax_dist_all asad,                    zx_rec_nrec_dist zd              WHERE asad.invoice_id = ai.invoice_id                AND asad.line_type_lookup_codeIN ('REC_TAX','NONREC_TAX')                AND asad.detail_tax_dist_idISNOTNULL                AND zd.rec_nrec_tax_dist_id = asad.detail_tax_dist_id                AND zd.rec_nrec_tax_amt <> asad.amount));

执行2）的语句有以下结果返回

因此参考Section 5最后面的文档信息

根据EBS版本下载相应的补丁（以12.0.6版本为例）: Patch 17603319:R12.ZX.A for 12.0.x，并且参考Note2089108.1进行处理，具体文档信息如下，虽然文档信息标识着[12.2.X]，但是根据上面的RecommendedSolution的提示，该文档是适用的。

R12: AP:Oracle E-Business Tax - DIST/LINE VARIANCE HOLD ON AP INVOICES BECAUSE OF TAXDATA (Datafix patch) application on ADOP enabled instances[12.2.X](Doc ID 2089108.1)，以下是文档内容

R12: AP: Oracle E-Business Tax - DIST/LINE VARIANCE HOLD ON AP INVOICES BECAUSE OF TAX DATA (Datafix patch) application on ADOP enabled instances [12.2.X] (Doc ID 2089108.1) To BottomTo Bottom  Modified: 27-Apr-2017  Type: HOWTO  Rate this document Email link to this document Open document in new window Printable Page In this Document Goal Solution References Applies to: Oracle Payables - Version 12.0.0 and later Oracle E-Business Tax - Version 12.0.0 and later Information in this document applies to any platform. Goal In 12.2, the patch application process has changed for normal code fix patches and GDFs (data fix patches) The goal of this document is to provide the steps to apply the GDF patches in customer 12.2 instances. It is recommended that you review "Oracle E-Business Suite Release 12.2: Online Patching FAQ (Doc ID 1583902.1)", for details on AD Online Patching (ADOP) Feature. Solution             1.  Pre-requisite Code-fix patches                 Apply the following mandatory pre-requisite code-fix patches before applying this data fix patch.                      -- For Dist Variance Issue --                          Patch 18910468:R12.ZX.C                      -- For Undo Accounting --                          Patch 17637775:R12.AP.C    2. Download the Patch 22202141:R12.ZX.C and apply patch as HOT PATCH.            a. Download and unzip the patch                  under /u01/R122_EBS/fs_ne/EBSapps/patch                b. Connect to the RUN edition environment.            c. Execute the patch through HOTPATCH phase                       adop phase=APPLY patches=<patchnumber> hotpatch=yes           If any active Patch edition (PREPARE phase is done, but CUTOVER is pending), then no patch can be applied as HOTPATCH. Customer has to wait till CUTOVER get completes.       3. IF customer cannot wait till CUTOVER to Apply the GDF patch,then they can apply the patch though normal process (files will be copied on patch edition).         Then copy them to  execute the scripts on RUN edition manually .         Follow below steps carefully.   PRESENT STATE:        PREPARE phase is done, APPLY phase is in progress and CUTOVER is pending.         The instance has active PATCH and RUN editions.   STEPS:                a.  Note down names of the files delivered by the GDF.             b.  Apply the GDF patch as normal patch through APPLY phase.         Patch application copies the files from the GDF patch to the APPL_TOP of the PATCH edition file system.          c. After applying the patch, take a copy of all the files, delivered by the GDF, from PATCH edition and place them under any fs_ne directory (non edition file system).            4.After successful patch application, Connect to the RUN edition environment and check if following files exists with below versions:                              Path File                                       Name                                         Version              ---------------------               ----------------------------                    ----------------           $ZX_TOP/patch/115/sql              zx_dist_var_crt.sql                           120.0.12020000.2           $ZX_TOP/patch/115/sql              zx_dist_var_pop.sql                         120.0.12020000.2           $ZX_TOP/patch/115/sql              zx_dist_var_undo_pop.sql               120.0.12020000.2           $ZX_TOP/patch/115/sql              zx_dist_var_undo_wrapper.sql         120.0.12020000.2           $ZX_TOP/patch/115/sql              zx_dist_var_fix.sql                            120.0.12020000.2                   These scripts should be executed manually from RUN EDITION       Please follow the instructions mentioned below to apply the data-fix. ----------------------------------------------------------------------------- *** STEPS TO APPLY THE DATA-FIX *** -----------------------------------------------------------------------------    1. Execute script zx_dist_var_crt.sql        This script will create the following entities:          Driver Table :            ZX_DIST_VAR_DRV_TBL      Undo Driver Tables :            AP_UNDO_INV_DRV_TBL            AP_UNDO_PMNT_DRV_TBL      Backup Tables :             ZX_LINES_DV_BKP             ZX_SUMMARY_DV_BKP             ZX_DIST_DV_BKP             AP_INV_LINE_DV_BKP             AP_INV_DIST_DV_BKP             AP_SA_DIST_DV_BKP      Logging entities :                   Log Table : ZX_DV_DFIX_LOG_MESSAGES                   Log Sequence : ZX_DV_DFIX_LOG_SEQ                   Log Package : ZX_DV_DFIX_LOG       2. Execute script zx_dist_var_pop.sql              This script will populate the driver table ZX_DIST_VAR_DRV_TBL with all the problematic invoices.   The script will set the following flags in the driver table:     (1) POSTED_ENCUMBERED_FLAG to 'Y' for all those invoices that are  accounted or partially accounted or encumbered.     (2) PAYMENT_POSTED_FLAG to 'Y' for all those invoices that are paid and payment is accounted.     (3) PROCESS_FLAG to 'Y' for all the invoices.   3. Check if Undo Accounting is required                Run the following SQL statement to check whether the Undo Accounting is required for the identified invoices or not.       SELECT COUNT(INVOICE_ID) FROM ZX_DIST_VAR_DRV_TBL          WHERE PROCESS_FLAG = 'Y' AND (POSTED_ENCUMBERED_FLAG = 'Y' OR          PAYMENT_POSTED_FLAG = 'Y');           If the above SQL returns COUNT greater than 0 the continue with the next step, else skip step 4 and 5(undo scripts).   4. Execute script zx_dist_var_undo_pop.sql             This script will populate the Undo Driver tables with the accounting events that need to be reversed.               Driver Table AP_UNDO_INV_DRV_TBL will be populated with accounting events of the invoices which will have POSTED_ENCUMBERED_FLAG = 'Y' in driver             table ZX_DIST_VAR_DRV_TBL.               Driver Table AP_UNDO_PMNT_DRV_TBL will be populated with accounting events of the payments of the invoices which will have PAYMENT_POSTED_FLAG = 'Y'             in the driver table ZX_DIST_VAR_DRV_TBL.     This script accepts PROPOSED_UNDO_DATE as input parameters. Read the instructions in the script to pass the value of PROPOSED_UNDO_DATE.     ----------------------------------------------------------------------     *** USER NOTE : HOW TO SET PROPOSED UNDO DATE FOR SPECIFIC INVOICE ***   ----------------------------------------------------------------------       If User wants to set the proposed undo date to some specific value for a specific invoice, then he/she has to set the proposed undo date in   the Undo Driver Tables by manually running an UPDATE statement   ----------------------------------------------------------------------   5. Execute script zx_dist_var_undo_wrapper.sql             Make sure that the pre-requisite patches mentioned above have been             applied before running this script.        Run the following SQL command in the SQL session where you are going to execute the script zx_dist_var_undo_wrapper.sql.                ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';        Run the script zx_dist_var_undo_wrapper.sql to undo the payment accounting and invoice accounting for the identified invoices. This script accepts 3      input parameters. Please read the instructions to pass input parameters in the script.        After successful undo, this script will set the POSTED_ENCUMBERED_FLAG and PAYMENT_POSTED_FLAG to 'N' in the driver table for all those invoices        which have been successfully processed by the Undo Process.        Take the FND Debug Log for undo process. The script will also print the      location of the output file.        --------------------------------------------------------------       *** USER NOTE : HOW TO TAKE FND DEBUG LOG FOR UNDO PROCESS ***      --------------------------------------------------------------      Set the following Profile options at User Level for the USER that will be passed as parameter to this script.               FND: Debug Log Enabled -> Yes             FND: Debug Log Level -> Statement             FND: Debug Log Module -> %        Take the max log sequence before running the script:             SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES; -- LOQ_SEQ1        Run the zx_dist_var_undo_wrapper.sql script for undo.        Take the max log sequence after script completes:              SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES; -- LOQ_SEQ2        Take the Undo Process debug Log using following query:              SELECT MODULE, MESSAGE_TEXT MESSAGE FROM FND_LOG_MESSAGES              WHERE LOG_SEQUENCE BETWEEN &LOQ_SEQ1 AND &LOQ_SEQ2  ORDER BY LOG_SEQUENCE;         --------------------------------------------------------------   6. Execute script zx_dist_var_fix.sql                 This script will rectify the problematic invoices. PROCESS_FLAG will be updated to 'D' in the driver table ZX_DIST_VAR_DRV_TBL for fixed invoices.   7. Validate the fixed invoices. If issue persists or if there are any         unprocessed invoices then provide the following data for DEV Team's analysis.            (1) Output of following queries:                     SELECT * FROM zx_dist_var_drv_tbl;                     SELECT * FROM ap_undo_inv_drv_tbl;                     SELECT * FROM ap_undo_pmnt_drv_tbl;                     SELECT * FROM zx_dv_dfix_log_messages ORDER BY log_sequence;           (2) Undo Process FND Debug Log (Refer to Step 5)           (3) Undo Process Output file (Refer to Step 5)             (4) Send also the AP list for the unprocessed invoices and invoices where the issue persists. Refer to: Doc ID 148388.1     8. Apply the data-fix given in the Patch 17505148:R12.AP.C to delete orphan       events that may exist in system after undo accounting. You can skip this       step if Undo Accounting is not performed.   9. Run 'TRANSFER JOURNAL ENTRIES TO GL' concurrent program to transfer the           reversal accounting entries to GL created during undo process.

重要的话说3遍

！！！注意：请严格根据文档指示操作，如果没有把握的话，请一定先在测试环境先处理。

！！！注意：请严格根据文档指示操作，如果没有把握的话，请一定先在测试环境先处理。

！！！注意：请严格根据文档指示操作，如果没有把握的话，请一定先在测试环境先处理。

！！！这里仅描述个人本次案例解决的主要步骤。

解压补丁文件17603319后，在以下相对目录下可以看到以下文件

重要的话说3遍

！！！根据文档的以下信息进行操作，即可修复此问题（请严格按照文档操作）

！！！根据文档的以下信息进行操作，即可修复此问题（请严格按照文档操作）

！！！根据文档的以下信息进行操作，即可修复此问题（请严格按照文档操作）

这个案例中本人只需做以下几步就修复问题了，具体根据问题情况选择相应的操作处理。

1. Execute script zx_dist_var_crt.sql

2. Execute script zx_dist_var_pop.sql

3. Check if Undo Accounting is required

Skip step 4 & 5

6. Execute script zx_dist_var_fix.sql

7.Validate the fixed invoices

Skip step 8 & 9 

----------------------------------------------------------------------------- *** STEPS TO APPLY THE DATA-FIX *** -----------------------------------------------------------------------------  1. Execute script zx_dist_var_crt.sql        This script will create the following entities:        Driver Table :            ZX_DIST_VAR_DRV_TBL    Undo Driver Tables :            AP_UNDO_INV_DRV_TBL            AP_UNDO_PMNT_DRV_TBL    Backup Tables :             ZX_LINES_DV_BKP             ZX_SUMMARY_DV_BKP             ZX_DIST_DV_BKP             AP_INV_LINE_DV_BKP             AP_INV_DIST_DV_BKP             AP_SA_DIST_DV_BKP    Logging entities :                   Log Table : ZX_DV_DFIX_LOG_MESSAGES                   Log Sequence : ZX_DV_DFIX_LOG_SEQ                   Log Package : ZX_DV_DFIX_LOG   2. Execute script zx_dist_var_pop.sql              This script will populate the driver table ZX_DIST_VAR_DRV_TBL with all the problematic invoices. The script will set the following flags in the driver table:     (1) POSTED_ENCUMBERED_FLAG to 'Y' for all those invoices that are  accounted or partially accounted or encumbered.     (2) PAYMENT_POSTED_FLAG to 'Y' for all those invoices that are paid and payment is accounted.     (3) PROCESS_FLAG to 'Y' for all the invoices. 3. Check if Undo Accounting is required                Run the following SQL statement to check whether the Undo Accounting is required for the identified invoices or not.     SELECT COUNT(INVOICE_ID) FROM ZX_DIST_VAR_DRV_TBL          WHERE PROCESS_FLAG = 'Y' AND (POSTED_ENCUMBERED_FLAG = 'Y' OR          PAYMENT_POSTED_FLAG = 'Y');       If the above SQL returns COUNT greater than 0 the continue with the next step, else skip step 4 and 5(undo scripts). 4. Execute script zx_dist_var_undo_pop.sql             This script will populate the Undo Driver tables with the accounting events that need to be reversed.             Driver Table AP_UNDO_INV_DRV_TBL will be populated with accounting events of the invoices which will have POSTED_ENCUMBERED_FLAG = 'Y' in driver             table ZX_DIST_VAR_DRV_TBL.             Driver Table AP_UNDO_PMNT_DRV_TBL will be populated with accounting events of the payments of the invoices which will have PAYMENT_POSTED_FLAG = 'Y'             in the driver table ZX_DIST_VAR_DRV_TBL.   This script accepts PROPOSED_UNDO_DATE as input parameters. Read the instructions in the script to pass the value of PROPOSED_UNDO_DATE.   ----------------------------------------------------------------------     *** USER NOTE : HOW TO SET PROPOSED UNDO DATE FOR SPECIFIC INVOICE ***   ----------------------------------------------------------------------   If User wants to set the proposed undo date to some specific value for a specific invoice, then he/she has to set the proposed undo date in   the Undo Driver Tables by manually running an UPDATE statement   ---------------------------------------------------------------------- 5. Execute script zx_dist_var_undo_wrapper.sql             Make sure that the pre-requisite patches mentioned above have been             applied before running this script.      Run the following SQL command in the SQL session where you are going to execute the script zx_dist_var_undo_wrapper.sql.                ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';      Run the script zx_dist_var_undo_wrapper.sql to undo the payment accounting and invoice accounting for the identified invoices. This script accepts 3      input parameters. Please read the instructions to pass input parameters in the script.      After successful undo, this script will set the POSTED_ENCUMBERED_FLAG and PAYMENT_POSTED_FLAG to 'N' in the driver table for all those invoices        which have been successfully processed by the Undo Process.      Take the FND Debug Log for undo process. The script will also print the      location of the output file.      --------------------------------------------------------------       *** USER NOTE : HOW TO TAKE FND DEBUG LOG FOR UNDO PROCESS ***      --------------------------------------------------------------      Set the following Profile options at User Level for the USER that will be passed as parameter to this script.             FND: Debug Log Enabled -> Yes             FND: Debug Log Level -> Statement             FND: Debug Log Module -> %      Take the max log sequence before running the script:             SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES; -- LOQ_SEQ1      Run the zx_dist_var_undo_wrapper.sql script for undo.      Take the max log sequence after script completes:              SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGES; -- LOQ_SEQ2      Take the Undo Process debug Log using following query:              SELECT MODULE, MESSAGE_TEXT MESSAGE FROM FND_LOG_MESSAGES              WHERE LOG_SEQUENCE BETWEEN &LOQ_SEQ1 AND &LOQ_SEQ2  ORDER BY LOG_SEQUENCE;     -------------------------------------------------------------- 6. Execute script zx_dist_var_fix.sql                 This script will rectify the problematic invoices. PROCESS_FLAG will be updated to 'D' in the driver table ZX_DIST_VAR_DRV_TBL for fixed invoices. 7. Validate the fixed invoices. If issue persists or if there are any         unprocessed invoices then provide the following data for DEV Team's analysis.          (1) Output of following queries:                     SELECT * FROM zx_dist_var_drv_tbl;                     SELECT * FROM ap_undo_inv_drv_tbl;                     SELECT * FROM ap_undo_pmnt_drv_tbl;                     SELECT * FROM zx_dv_dfix_log_messages ORDER BY log_sequence;           (2) Undo Process FND Debug Log (Refer to Step 5)           (3) Undo Process Output file (Refer to Step 5)           (4) Send also the AP list for the unprocessed invoices and invoices where the issue persists. Refer to: Doc ID 148388.1 8. Apply the data-fix given in the Patch 17505148:R12.AP.C to delete orphan       events that may exist in system after undo accounting. You can skip this       step if Undo Accounting is not performed. 9. Run 'TRANSFER JOURNAL ENTRIES TO GL' concurrent program to transfer the           reversal accounting entries to GL created during undo process.