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;