Outgoing Payment Query

[bbranco]

Can someone give me some guidance on creating an outgoing payment query showing the check #, check date, bank name and all of the AP invoices and AP Credits associated to the payment?

I need to run it by a check date for just AP Checks.

[jitin_chawla]

Hi,

Check this for start

SELECT distinct T0.DocNum as 'Outgoing Payment Number', T1.[CheckNum] as 'Check Number',
T3.BankName as 'Bank Name', T2.SumApplied as 'Amount Paid to Document',
T1.[CheckSum] as 'Total Check Amount', T1.[DueDate] as 'Check Date in Payment Means',
CASE WHEN T2.InvType = 18 THEN 'PU'
WHEN T2.InvType = 19 THEN 'PC'
WHEN T2.InvType = 30 THEN 'JE'
WHEN T2.InvType = 204 THEN 'DPR'
WHEN T2.InvType = 203 THEN 'DPI'
ELSE NULL
END AS 'Document Type',
CASE WHEN T4."TransType" = 18 THEN T5."BaseRef"
WHEN T4.TransType= 19 THEN T5."BaseRef"
WHEN T4.TransType = 30 THEN T5."BaseRef"
WHEN T4.TransType = 204 THEN T5."BaseRef"
WHEN T4.TransType = 203 THEN T5."BaseRef"
ELSE NULL
END AS 'Document Number',
(CONCAT(CASE WHEN T2.InvType = 18 THEN 'PU'
WHEN T2.InvType = 19 THEN 'PC'
WHEN T2.InvType = 30 THEN 'JE'
WHEN T2.InvType = 204 THEN 'DPR'
WHEN T2.InvType = 203 THEN 'DPI'
ELSE NULL
END ,'  ',CASE WHEN T4."TransType" = 18 THEN T5."BaseRef"
WHEN T4.TransType= 19 THEN T5."BaseRef"
WHEN T4.TransType = 30 THEN T5."BaseRef"
WHEN T4.TransType = 204 THEN T5."BaseRef"
WHEN T4.TransType = 203 THEN T5."BaseRef"
ELSE NULL
END)),
CASE WHEN T0."CheckSum" > 0 THEN 'Check'
ELSE 'Other' END AS 'Paid By'
FROM OVPM T0  
INNER JOIN VPM1 T1 ON T0.[DocEntry] = T1.[DocNum] 
INNER JOIN VPM2 T2 ON T0.[DocEntry] = T2.[DocNum]
INNER JOIN JDT1 T4 ON T4."TransId" = T2."DocTransId"
INNER JOIN OJDT T5 ON T5."TransId" = T4."TransId"
INNER JOIN ODSC T3 ON T3.BankCode = T1.BankCode
WHERE T0."Canceled" <> 'C'
ORDER BY T0.DocNum ASC


*** HANA - Includes more scenarios like Payment on Account in Outgoing Payment, Incoming Payment included in Outgoing Payment****



SELECT distinct T0."DocNum" as "Outgoing Payment Number", T1."CheckNum" as "Check Number",
T3."BankName" as "Bank Name", T2."SumApplied" as "Amount Paid to Document",
T1."CheckSum" as "Total Check Amount", T1."DueDate" as "Check Date in Payment Means",
CASE WHEN T2."InvType" = 18 THEN 'PU'
WHEN T2."InvType" = 19 THEN 'PC'
WHEN T2."InvType" = 30 THEN 'JE'
WHEN T2."InvType" = 46 THEN 'Payment On Account JE'
WHEN T2."InvType" = 24 THEN 'RC - Incoming Payment'
WHEN T2."InvType" = 204 THEN 'DPR'
WHEN T2."InvType" = 203 THEN 'DPI'
ELSE NULL
END AS "Document Type",
CASE WHEN T4."TransType" = 18 THEN T5."BaseRef"
WHEN T4."TransType"= 19 THEN T5."BaseRef"
WHEN T4."TransType" = 30 THEN T5."BaseRef"
WHEN T4."TransType" = 46 THEN T5."BaseRef"
WHEN T4."TransType" = 24 THEN T5."BaseRef"
WHEN T4."TransType" = 204 THEN T5."BaseRef"
WHEN T4."TransType" = 203 THEN T5."BaseRef"
ELSE NULL
END AS "Document Number",
(CASE WHEN T2."InvType" = 18 THEN 'PU'
WHEN T2."InvType" = 19 THEN 'PC'
WHEN T2."InvType" = 30 THEN 'JE'
WHEN T2."InvType" = 46 THEN 'PS'
WHEN T2."InvType" = 24 THEN 'RC'
WHEN T2."InvType" = 204 THEN 'DPR'
WHEN T2."InvType" = 203 THEN 'DPI'
ELSE NULL
END ||'  ' ||CASE WHEN T4."TransType" = 18 THEN T5."BaseRef"
WHEN T4."TransType"= 19 THEN T5."BaseRef"
WHEN T4."TransType" = 30 THEN T5."BaseRef"
WHEN T4."TransType" = 46 THEN T5."BaseRef"
WHEN T4."TransType" = 24 THEN T5."BaseRef"
WHEN T4."TransType" = 204 THEN T5."BaseRef"
WHEN T4."TransType" = 203 THEN T5."BaseRef"
ELSE NULL
END) As "Document #",
CASE WHEN T0."CheckSum" > 0 THEN 'Check'
ELSE 'Other' END AS "Paid By"
FROM OVPM T0  
INNER JOIN VPM1 T1 ON T0."DocEntry" = T1."DocNum" 
INNER JOIN VPM2 T2 ON T0."DocEntry" = T2."DocNum"
INNER JOIN JDT1 T4 ON T4."TransId" = T2."DocTransId"
INNER JOIN OJDT T5 ON T5."TransId" = T4."TransId"
INNER JOIN ODSC T3 ON T3."BankCode" = T1."BankCode"
WHERE T0."DocType" = 'S' AND T0."Canceled" <> 'C'
ORDER BY T0."DocNum" ASC

Kr,

Jitin