Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program


Invoices in the status of 'Selected for Validation' after cancelling Invoice Validation Program

Here we can segregate this issue into 2 Parts.

1. Invoices in status of Selected for validation when cancelled Invoice Validation Program it will be stamped with Validation_request_id.
2. Invoice Validation program keep on running from hours together and want to cancel the Request

When ever we cancel the invoice validation program when its running, it should roolback all the invoices back to its normal status but some/many times it will not rollback the invoices and it will stamp the cancelled request id on all the invoices under column VALIDATION_REQUEST_ID of AP_INVOICE_HEADERS_ALL.

What ever the invoices stamped with request_id can be get with this query to know the count or list of invoices.
SQL:
select * from ap_invoices_all where validation_request_id = 'XXXXX';-- or where validation_request_id is not null;

Once we extract these invoices we need to fix the invoices with the help of data-fix to rollback stamped invoices.

Solution: (Reference Patch#17428522)

1. Cancel the  request (If the request is not yet cancel the request)

A.  Cancel present child request #46981209

B.  Wait for few minutes to let parent request complete, it might initiate other new child requests in couple of minutes.

C.  If parent request do not initiate other child requests and parent request not completes in few minutes, cancel parent request as well.

2. Apply patch#17428522 and Run script: ap_inv_val_prb_sel.sql

Path: $AP_TOP/patch/115/sql/ ap_inv_val_prb_sel.sql

Temp Driver Table:
AP_TEMP_DATA_DRIVER_9327208

If you want to extract the invoices from the driver table use this query

select * from AP_TEMP_DATA_DRIVER_9327208

3. Run update statement:

update AP_TEMP_DATA_DRIVER_9327208
set process_flag=’N’
where validation_request_id is null;

commit;
/
4. Run fix script: ap_inv_val_prb_fix.sql

Path:$AP_TOP/patch/115/sql/ ap_inv_val_prb_fix.sql

Once the fix completed successfully, run the below again here you should get '0' records

Query:
select count(invoice_id) from ap_invoices_all where validation_request_id is not null;

Reference From Note ID

Description
1. Description


       Old Behavior
       -------------
            If Invoice Validation ends in error then
            validation_request_id  remain stamped on all the invoices
            selected in that run.Such invoices cannot be modified further
            on workbench.


       New Behavior
       -------------
            The GDF will update the validation_request_id to null for
            the invoices whose Invoice validation is completed with error .

     NOTE : code fix done in the bug 8990763
   
2.   Pre-requisite or Co-requisite which is not included with this patch:
     None

3.   Patch Applicable for versions/releases:
     Patch must be applied over patchset R12.AP.B
     It is a GDF patch.

4.   Dependent Applications:
     Oracle Payables

5.   Patch to be applied:
     Server

6.   Patch applicable only when a particular application installed:
     Payables

7.   Pre-patch application steps
     None

8.   Post patch application steps
     None

9.   Successful patch installation checks:
     Check the file versions, they have to be the same as mentioned above.
     patch/115/sql/ap_inv_val_prb_sel.sql 120.0.12010000.3
     patch/115/sql/ap_inv_val_prb_fix.sql 120.0.12010000.3
 
Additional Patch Information
=============================
1. Objective:
-------------

Bug #17428522  is a data fix patch, which will help you to solve the
following issues.

1. Detect the invoices whose validation_request_id still remain
   stamped after Invoice validation is completed with error or
   those whose validation_request_id still remain stamped but
   do not exist anymore in FND table due to purge initiated by user.

2. Fix script will update the validation_request_id to null for
   the invoices which were identified by the selection script

-------------------------------------------------------------------------------
                             DISCLAIMER
-------------------------------------------------------------------------------
     This GDF is going to update the WHO columns (Last_updated_by,
     Last_update_date) with  USER_ID of User and SYSDATE
     on the transaction tables  that will be fixed.

     This may impact purging of those transactions as Payables Purge
     program considers the last_update_date to Purge the data.
-------------------------------------------------------------------------------


2. EMAIL Feature
---------------------------------------
This is an optional feature provided to customers. After execution of GDF
selection and fix scripts, output files will be emailed to a user. To make use
of this feature please Follow the Metalink Note 1582550.1 and pass valid email
at the time of script execution. This feature can be ignored by not passing
email id at the time of script execution.

Note: Due to technical/security issues if email is not delivered then
customers are advised to run the scripts by not passing email id at the time
of script execution. This will not impact actual behavior of datafix.


3. Instructions:
----------------

Kindly follow the instructions given below to apply the data fix patch.

1. Download the patch. And run the selection script
   ap_inv_val_prb_sel.sql



 a) Creates driver table AP_TEMP_DATA_DRIVER_9327208
    with the problematic invoices

 b) log file 9327208-diag-HH24:MI:SS.html
    which shows debug messages for selection.

2. Check the report 9327208-diag-HH24:MI:SS.html
   to debug. you can find the file under the path given by below query.

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

   Also you can find all the affected transactions in table
   AP_TEMP_DATA_DRIVER_9327208  .

3. Update process_flag in AP_TEMP_DATA_DRIVER_9327208
   to 'N' for transactions that should not be operated on.
   By default the process_flag column is Y.

4. Identified records in table AP_TEMP_DATA_DRIVER_9327208
   with process_flag 'Y' can be corrected by running the fix script.

   Example:  Sqlplus apps/apps@finpqe10
   @$AP_TOP/patch/115/sql/ap_inv_val_prb_fix.sql
   to fix the same. This will generate log file 9327208-fix-HH24:MI:SS.html.


7. Backup of the fixed transactions is taken into following table
   AP_TMP_DATA_DRIVER_9327208_BKP once the fix script run for
   the transactions.

8. For any issues with the script/results please contact Oracle support and
   supply files
   9327208-diag-HH24:MI:SS.html and 9327208-fix-HH24: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';

9. Once the fix done, to ensure that the issue got resolved, please run the
   below selection query and the query should return no rows.

    SELECT /*+ parallel(api) */ api.*
    FROM ap_invoices_all api
    WHERE api.validation_request_id IS NOT NULL
    AND api.validation_request_id > 0
    AND (EXISTS
    (SELECT 'Request Completed'
     FROM fnd_concurrent_requests fcr
     WHERE fcr.request_id = api.validation_request_id
     AND fcr.phase_code = 'C' )
    OR not exists
     (SELECT 'Request Completed'
     FROM fnd_concurrent_requests fcr
     WHERE fcr.request_id = api.validation_request_id))
     AND invoice_id in (select invoice_id from AP_TEMP_DATA_DRIVER_9327208 atd
                        where atd.process_flag='Y');

Comments

Post a Comment

Popular posts from this blog

Oracle Subledger Accounting (SLA) Tables, Views

Oracle Subledger Accounting Tables: TABLE NAME DESCRIPTION XLA_AAD_GROUPS The XLA_AAD_GROUPS table stores the merge dependencies analyzed during the merge analysis.  All application accounting definitions with the same GROUP_NUM must be merged together. XLA_AAD_HDR_ACCT_ATTRS The XLA_AAD_HDR_ACCT_ATTRS stores standard, system and custom sources assigned to an accounting attribute at the AAD level. XLA_AAD_HEADER_AC_ASSGNS Store the analytical criteria for the application accounting definitions. XLA_AAD_LINE_DEFN_ASSGNS This table stores the journal lines definitions for the application accounting definitions. XLA_AAD_LOADER_DEFNS_T The XLA_AAD_LOADER_DEFNS_T table is the interface table that facilitates the data transfer from data files and the database. XLA_AAD_LOADER_LOGS The XLA_AAD_LOADER_LOGS table stores the errors and logs generated by the application accounting definitions loader. XLA_AAD_SOURCES XLA_AAD_SOURCES table stores a list of sources used by an Application Accounti...
Read more

Understanding Item Import and Debugging Problems with Item Import (Doc ID 268968.1)

  PURPOSE Item Import Overview ~~~~~~~~~~~~~~~~~~~~~~~~~ Use the item import process to import items from your legacy system or PDM system. You can import items from any source into Oracle Inventory and Oracle Engineering. When you import items through the Item Interface, you create new items in your Item Master organization or assign existing items to additional organizations. You can specify values for all the item attributes, or you can specify just a few attributes and let the remainder default or remain Null. You can also specify an item template for each item and inherit attribute values from the template. The Item Interface also lets you import revision details, including past and future revisions and effectivity dates. Validation of imported items is done using the same rules as the item definition forms, so you are insured of valid items. The Item Interface reads data from two tables for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE table for new...
Read more