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

     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))

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));

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.

-----------------------------------------------------------------------------
*** 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.