After being a deadlock victim, rerun VAT1001 SQR report

User just suffered from a pain:

"Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim".

Simply re-issuing the report will cause another problem - in VAT1001 SQR Report (even Process Monitor showed "Success" and "Posted"), it just shows:

PROCESS_INSTANCE = 123456789
No rows found to round in PS_VAT_TXN_TMP_TBL

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

Oracle Metalink has an article explains how to run the VAT report, which is:
EGL: How to Set Up And Run a VAT Report ? [ID 620419.1]

However, from VAT1001.SQR, we can see more:

begin-procedure Main-Processing
    #debugp do Debug-Msg('Main-Processing')

    do Mark-Txn-Lines
    do Insert-vat-txn-tmp-tbl
    #ifdef MICROSOFT
       do Update-Statistics
    #else
       #ifdef INFORMIX
         do Update-Statistics
       #else
          #ifdef ORACLE
             do Update-Statistics
          #end-if
       #end-if
    #end-if
    do delete-frm-vat-txn-tbl
    do Fill-Line-Info
    do Get-Report-Defn-Header
    do Get-Home-Country
    do Create-VAT-Rpt-Inst1
    do Process-Report-Lines
    do Insert-vat-rpt-err1
    do Check-for-Unselected
    do Update-In-Process-Flag
    do insert-vat-txn-tbl
    do delete-vat-txn-tmp-tbl
    do Process-Scheduler-Closing

    #debugp display 'Leaving Main-Processing'
end-procedure ! Main-Processing

In the process Mark-Txn-Lines, we can see, the SQR marks the lines by some criteria, but here I just want to point out 3 conditions for the lines being reported:

• In table PS_VAT_TXN_TBL, field IN_PROCESS_FLG is set as 'N';

• No result should be found by the SQL, if found, delete the lines having PROCESS_INSTANCE = [## the failed PI##] the from table PS_VAT_RPT_INST2; or, change their PROCESS_INSTANCE to a easy number to trace back (say, -1122334455?) :

SELECT * FROM PS_VAT_TXN_TBL, PS_VAT_RPT_INST2 B, PS_VAT_RPT_INST1 C
  WHERE  PS_VAT_TXN_TBL.PROCESS_INSTANCE = [## the failed PI##]
  AND B.PROCESS_INSTANCE = C.PROCESS_INSTANCE
  AND B.VAT_RPT_ID = C.VAT_RPT_ID
  AND PS_VAT_TXN_TBL.VAT_ENTITY = B.VAT_ENTITY
  AND PS_VAT_TXN_TBL.COUNTRY_VAT_RPTG = B.COUNTRY_VAT_RPTG
  AND PS_VAT_TXN_TBL.VAT_DCMNT_ID = B.VAT_DCMNT_ID
  AND PS_VAT_TXN_TBL.LINE_NBR = B.LINE_NBR
  --AND C2.VAT_RPT_TYPE = $rqst_vat_rpt_type;

• Cross your finger to wish NO result be found by the SQL below (otherwise, you need to check what caused a previous reporting error - check PeopleBooks???):

SELECT * FROM PS_VAT_TXN_TBL, PS_VAT_RPT_ERR2 B2, PS_VAT_RPT_INST1 C2
  WHERE B2.PROCESS_INSTANCE = C2.PROCESS_INSTANCE
  AND PS_VAT_TXN_TBL.VAT_ENTITY = B2.VAT_ENTITY
  AND PS_VAT_TXN_TBL.COUNTRY_VAT_RPTG = B2.COUNTRY_VAT_RPTG
  AND PS_VAT_TXN_TBL.VAT_DCMNT_ID = B2.VAT_DCMNT_ID
  AND PS_VAT_TXN_TBL.LINE_NBR = B2.LINE_NBR
  --AND C2.VAT_RPT_TYPE = $rqst_vat_rpt_type
  AND B2.VAT_RPT_ASSOC_FLG IN ('T', 'A')   

Then re-run VAT1001.