Solution on TEST server for orphan payments

http://aporaclepayables.blogspot.com/2016/02/solution-on-test-server-for-orphan.html

To Delete Check from System:

I was applied patch p10025422_R12.AP.A_R12_GENERIC.zip but before applying the patch I have some changes in /apps/10025422/ap/patch/115/sql/ap_invalid_check_sel.sql . Please check the below query i have comment one line APH.POSTED_FLAG <>''Y''.
After that I had applied /apps/10025422/ap/patch/115/sql/ap_invalid_check_fix.sql and my problem is resolved .

Note : I had applied above action plan on TEST, can I follow the same as on Production.

'CREATE TABLE AP_TEMP_DRIVER_10025422 AS
 SELECT AC.CHECK_ID,
 AC.CHECK_NUMBER,
 AC.AMOUNT,
 AC.STATUS_LOOKUP_CODE,
 APH.ACCOUNTING_EVENT_ID,
 APH.POSTED_FLAG,
 AC.VENDOR_ID,
 AC.VENDOR_SITE_ID,
 AC.PAYMENT_ID,
 AC.ORG_ID,
 AC.PAYMENT_DOCUMENT_ID,
 ''Y'' PROCESS_FLAG
 FROM AP_CHECKS_ALL AC,
 AP_PAYMENT_HISTORY_ALL APH
 WHERE AC.PAYMENT_ID IS NULL
 AND AC.STATUS_LOOKUP_CODE NOT IN (''OVERFLOW'',''SET UP'',''SPOILED'')
 AND AC.CHECK_ID=APH.CHECK_ID
 AND NVL(APH.HISTORICAL_FLAG,''N'') <> ''Y''
 --AND APH.POSTED_FLAG <>''Y''
 AND NOT EXISTS (SELECT 1
 FROM AP_INVOICE_PAYMENTS_ALL AIP
 WHERE AIP.CHECK_ID = AC.CHECK_ID)
AND EXISTS (SELECT 1
 FROM AP_INVOICE_PAYMENTS_ALL AIP1,
 AP_CHECKS_ALL AC1
 WHERE AIP1.CHECK_ID = AC1.CHECK_ID
 AND AC.ORG_ID = AC1.ORG_ID
 AND ((AC.VENDOR_ID = AC1.VENDOR_ID
 AND AC.VENDOR_SITE_ID = AC1.VENDOR_SITE_ID)
 OR AC.PAYMENT_DOCUMENT_ID =AC1.PAYMENT_DOCUMENT_ID)
 AND AC.CHECK_NUMBER = AC1.CHECK_NUMBER ) ';


cd /apps/10025422/ap/patch/115/sql
Please follow the instruction on README.txt for patch 10025422

connect apps/apps
run apgdfals.pls  
run apgdfalb.pls   

For 12.2.4
Apply GDF infrastructure patch 21816876:R12.AP.C to get the latest data fix package files (apgdfals.pls and apgdfalb.pls). 

run ap_invalid_check_sel.sql

--before fix the effected transaction please run beow command on production server

/*Update process_flag in AP_TEMP_DRIVER_10025422 to 'N'
   for transactions that should not be operated on*

run ap_invalid_check_fix.sql

Note: Do not need to apply patch you just follow the above action plan.

--------------------------------------------------------------------------------------------------------------------------

To Void Check without any Accounting:

Please Download the script from the below link

https://drive.google.com/open?id=0B30-kT1pIOm_dGJUOFRFNFpzNmM

Please execute the script:ap_void_check_mark_np_no_aip.sql in the cloned instance. 

Pass the check_id : 

Detailed steps: 
Verify whether Payment Voided and update us with the results 

1.Apply the fix:ap_void_check_mark_np_no_aip.sql .The fix script will do the following 

a.The Payment Cancellation event will be generated for the missing check_id in ap_invoice_payments which is existed in ap_payment_history. 

b.The XLA_EVENTS status will be set to N/P for the Cancellation event. 

c.The posted_flags in ap_payment_history_all,ap_invoice_payments_all,ap_invoice_distributions set to Y 

d.Finally,the status of the check will be set to "Voided" 

2.Retest the issue 

3.Migrate the solution to the appropriate environments