Payment Listing applied AP Credit Note amount Query

[kedalenechong]

Hi All

How to edit this Query to show Paid To Invoice Amount in negative when Document Type is AP Credit Note? 

SELECT T0."DocDate" AS "Payment Date", T0."DocNum" AS "Payment DocNum",
CASE
WHEN T1."InvType"=18 THEN 'PU'
WHEN T1."InvType"=30 THEN 'JE'
WHEN T1."InvType"=19 THEN 'PC'
END AS "DocType"
,CASE
WHEN T1."InvType"=18 THEN T3."DocDate"
WHEN T1."InvType"=30 THEN T4."RefDate"
WHEN T1."InvType"=19 THEN T5."DocDate"
END AS "DocDate"
,CASE
WHEN T1."InvType"=18 THEN T3."DocNum"
WHEN T1."InvType"=30 THEN T4."Number"
WHEN T1."InvType"=19 THEN T5."DocNum"
END AS "DocNum"
,T0."CardCode" , T0."CardName",
CASE
WHEN T1."InvType"=18 THEN T3."NumAtCard"
WHEN T1."InvType"=30 THEN T4."Ref2"
WHEN T1."InvType"=19 THEN T5."NumAtCard"
END AS "Ref Num"
,T0."TrsfrSum" ,T0."DocCurr",T0."TrsfrSumFC",T1."SumApplied", T1."AppliedFC"

FROM OVPM T0
INNER JOIN VPM2 T1 ON T0."DocEntry" = T1."DocNum"
LEFT JOIN VPM1 T2 ON T1."DocNum" = T2."DocNum"
LEFT JOIN OPCH T3 ON T1."DocEntry" = T3."DocEntry"
LEFT JOIN OJDT T4 ON T1."DocEntry"= T4."TransId"
LEFT JOIN ORPC T5 ON T1."DocEntry" = T5."DocEntry"

WHERE T0."DocDate" >= [%0] AND T0."DocDate" <=[%1]

Kedalene Chong

[jitin_chawla · Accepted solution]

Hi,

Check below:

SELECT T0."DocDate" AS "Payment Date", T0."DocNum" AS "Payment DocNum",
CASE
WHEN T1."InvType"=18 THEN 'PU'
WHEN T1."InvType"=30 THEN 'JE'
WHEN T1."InvType"=19 THEN 'PC'
END AS "DocType"
,CASE
WHEN T1."InvType"=18 THEN T3."DocDate"
WHEN T1."InvType"=30 THEN T4."RefDate"
WHEN T1."InvType"=19 THEN T5."DocDate"
END AS "DocDate"
,CASE
WHEN T1."InvType"=18 THEN T3."DocNum"
WHEN T1."InvType"=30 THEN T4."Number"
WHEN T1."InvType"=19 THEN T5."DocNum"
END AS "DocNum"
,T0."CardCode" , T0."CardName",
CASE
WHEN T1."InvType"=18 THEN T3."NumAtCard"
WHEN T1."InvType"=30 THEN T4."Ref2"
WHEN T1."InvType"=19 THEN T5."NumAtCard"
END AS "Ref Num",
CASE
WHEN T1."InvType"=18 THEN T0."TrsfrSum"
WHEN T1."InvType"=30 THEN T0."TrsfrSum"
WHEN T1."InvType"=19 THEN T0."TrsfrSum"*-1
END AS "Transfer Sum"
,T0."DocCurr",
CASE
WHEN T1."InvType"=18 THEN T0."TrsfrSumFC"
WHEN T1."InvType"=30 THEN T0."TrsfrSumFC"
WHEN T1."InvType"=19 THEN T0."TrsfrSumFC"*-1
END AS "Transfer Sum FC"
,
CASE
WHEN T1."InvType"=18 THEN T1."SumApplied"
WHEN T1."InvType"=30 THEN T1."SumApplied"
WHEN T1."InvType"=19 THEN T1."SumApplied"*-1
END AS "SumApplied", 
CASE
WHEN T1."InvType"=18 THEN T1."AppliedFC"
WHEN T1."InvType"=30 THEN T1."AppliedFC"
WHEN T1."InvType"=19 THEN T1."AppliedFC"*-1
END AS "AppliedFC"

FROM OVPM T0
INNER JOIN VPM2 T1 ON T0."DocEntry" = T1."DocNum"
LEFT JOIN VPM1 T2 ON T1."DocNum" = T2."DocNum"
LEFT JOIN OPCH T3 ON T1."DocEntry" = T3."DocEntry"
LEFT JOIN OJDT T4 ON T1."DocEntry"= T4."TransId"
LEFT JOIN ORPC T5 ON T1."DocEntry" = T5."DocEntry"
WHERE T0."DocDate" >= [%0] AND T0."DocDate" <=[%1]

[BattleshipCobra]

Just like @sean_pvm is suggesting, you can use a CASE statement, this should work for you:

SELECT 
	 T0."DocDate" AS "Payment Date"
	,T0."DocNum" AS "Payment DocNum"
	,CASE
		WHEN T1."InvType" = 18 THEN 'PU'
		WHEN T1."InvType" = 30 THEN 'JE'
		WHEN T1."InvType" = 19 THEN 'PC'
	 END AS "DocType"
	,CASE
		WHEN T1."InvType" = 18 THEN T3."DocDate"
		WHEN T1."InvType" = 30 THEN T4."RefDate"
		WHEN T1."InvType" = 19 THEN T5."DocDate"
	 END AS "DocDate"
	,CASE
		WHEN T1."InvType" = 18 THEN T3."DocNum"
		WHEN T1."InvType" = 30 THEN T4."Number"
		WHEN T1."InvType" = 19 THEN T5."DocNum"
	 END AS "DocNum"
	,T0."CardCode" 
	,T0."CardName"
	,CASE
		WHEN T1."InvType" = 18 THEN T3."NumAtCard"
		WHEN T1."InvType" = 30 THEN T4."Ref2"
		WHEN T1."InvType" = 19 THEN T5."NumAtCard"
	 END AS "Ref Num"
	,CASE
		WHEN T1."InvType" = 19 THEN T0."TrsfrSum" * -1
		ELSE T0."TrsfrSum" 
	 END AS "Transfer Amount"
	,T0."DocCurr"
	,CASE
		WHEN T1."InvType" = 19 THEN T0."TrsfrSumFC" * -1
		ELSE T0."TrsfrSumFC"
	 END AS "Transfer Amount (FC)"
	,CASE
		WHEN T1."InvType" = 19 THEN T1."SumApplied" * -1
		ELSE T1."SumApplied"
	 END AS "Paid to Invoice"
	,CASE
		WHEN T1."InvType" = 19 THEN T1."AppliedFC" * -1
		ELSE T1."AppliedFC"
	 END AS "Paid in FC"

FROM 
	OVPM T0
	INNER JOIN VPM2 T1 ON T0."DocEntry" = T1."DocNum"
	LEFT JOIN VPM1 T2 ON T1."DocNum" = T2."DocNum"
	LEFT JOIN OPCH T3 ON T1."DocEntry" = T3."DocEntry"
	LEFT JOIN OJDT T4 ON T1."DocEntry"= T4."TransId"
	LEFT JOIN ORPC T5 ON T1."DocEntry" = T5."DocEntry"

WHERE T0."DocDate" >= [%0] AND T0."DocDate" <=[%1]

 Good luck!

M

[sean_pvm]

Hi,

Have you tried adding a set of

CASE

WHEN InvType = "nn" THEN Amt

WHEN InvType = "yy" Amt * -1

END AS "Paid_To_Invoice_Amount"

statement as necessary?

Substitute the appropriate InvType values ...