-- 203 DPI AR Down Payment Invoice
-- CALL TmSp_QRCodeSPGenerated_PT_203(:DocKey, :ObjectId);
-- DROP PROCEDURE TmSp_QRCodeSPGenerated_PT_203;
CREATE PROCEDURE TmSp_QRCodeSPGenerated_PT_203 (in DocKey integer, in ObjectID integer)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

SELECT 'A:' || A || '*' || 'B:' || B || '*' || 'C:' || C || '*' || 'D:' || D || '*' || 'E:' || E || '*' || 'F:' || F || '*' || 'G:' || G1 || ' ' || G2 || '/' || G3 || '*' || 'H:' || ATCUD ||
 '*' || CAST('I1:' AS varchar(500)) || 'PT' || '*' || CASE WHEN SUM(I2) IS NULL OR SUM(IFNULL(I2, 0)) = 0 THEN '' ELSE 'I2:' || rtrim(CAST(CAST(SUM(I2) AS decimal(20, 2)) AS char)) ||
 '*' END || CASE WHEN SUM(I3) IS NULL OR SUM(I3) = 0 THEN '' ELSE 'I3:' || rtrim(CAST(CAST(SUM(I3) AS decimal(20, 2)) AS char)) || '*' END || CASE WHEN SUM(I4) IS NULL OR SUM(I4) = 0 THEN
 '' ELSE 'I4:' || rtrim(CAST(CAST(SUM(I4) AS decimal(20, 2)) AS char)) || '*' END || CASE WHEN SUM(I5) IS NULL OR SUM(I5) = 0 THEN '' ELSE 'I5:' || rtrim(CAST(CAST(SUM(I5) 
AS decimal(20, 2)) AS char)) || '*' END || CASE WHEN SUM(I6) IS NULL OR SUM(I6) = 0 THEN '' ELSE 'I6:' || rtrim(CAST(CAST(SUM(I6) AS decimal(20, 2)) AS char)) || '*' END || 
CASE WHEN SUM(I7) IS NULL OR SUM(I7) = 0 THEN '' ELSE 'I7:' || rtrim(CAST(CAST(SUM(I7) AS decimal(20, 2)) AS char)) || '*' END || 
CASE WHEN SUM(I8) IS NULL OR SUM(I8) = 0 THEN '' ELSE 'I8:' || rtrim(CAST(CAST(SUM(I8) AS decimal(20, 2)) AS char)) || '*' END || 
CASE WHEN SUM(L) IS NULL OR SUM(L) = 0 THEN '' ELSE 'L:' || rtrim(CAST(CAST(SUM(L) AS decimal(20, 2)) AS char)) || '*' END || 'N:' || rtrim(CAST(CAST(G AS decimal(12, 2)) AS char)) ||
 '*' || 'O:' || rtrim(CAST(CAST(H AS decimal(12, 2)) AS char)) || '*' || CASE WHEN SUM("WTSum") IS NULL OR SUM("WTSum") = 0 THEN '' ELSE 
'P:' || CAST(SUM("WTSum") AS varchar(20)) || '*' END || "SignD" || '*' || 
'R:' || '0130' 
FROM (SELECT substring(T3."TaxIdNum", 3, 11) AS "A", (CASE WHEN T1."LicTradNum" <> ' ' THEN substring(T1."LicTradNum", 3, 11) WHEN T2."LicTradNum" <> ' ' 
THEN substring(T2."LicTradNum", 3, 11) ELSE '999999990' END) AS "B", (CASE WHEN T0."CountryB" IS NOT NULL THEN T0."CountryB" ELSE 'PT' END) AS "C", 
(CASE WHEN T1."ObjType" IN (13, 203) THEN 'FT' 
	  WHEN T1."ObjType" = 14 THEN 'NC' 
	  WHEN T1."ObjType" = 15 THEN 'GR' 
	  WHEN T1."ObjType" = 16 THEN 'GD' 
	  WHEN T1."ObjType" = 17 THEN 'NE' 
	  WHEN T1."ObjType" = 23 THEN 'OR' 
	  ELSE '' END) AS "D", 
(CASE WHEN T1."CANCELED" = 'Y' OR T1."CANCELED" = 'C' THEN 'A' ELSE 'N' END) AS "E", 
(TO_CHAR (T1."DocDate" , 'YYYYMMDD')) AS "F", T1."ObjType" AS "G1", CAST(T1."Series" AS varchar) AS "G2", CAST(T1."DocNum" AS varchar) AS "G3", 
(CASE WHEN UPPER(T7."ReportCode") LIKE 'M%' OR UPPER(T7."ReportCode") = 'OUTROS / ISE' OR UPPER(T7."ReportCode") = 'UE / ISE' OR UPPER(T7."ReportCode") = 'IVACON-ISE' THEN IFNULL(T5."BaseSum", 0) ELSE 0 END) AS "I2",
CASE WHEN UPPER(T7."ReportCode") = 'C-RED' OR UPPER(T7."ReportCode") = 'IVACON-RED' THEN T5."BaseSum" ELSE 0 END AS "I3", 
CASE WHEN UPPER(T7."ReportCode") = 'C-RED' OR UPPER(T7."ReportCode") = 'IVACON-RED' THEN T5."VatSum" ELSE 0 END AS "I4", 
CASE WHEN UPPER(T7."ReportCode") = 'C-INT' OR UPPER(T7."ReportCode") = 'IVACON-INT' THEN T5."BaseSum" ELSE 0 END AS "I5", 
CASE WHEN UPPER(T7."ReportCode") = 'C-INT' OR UPPER(T7."ReportCode") = 'IVACON-INT' THEN T5."VatSum" ELSE 0 END AS "I6", 
CASE WHEN UPPER(T7."ReportCode") = 'C-NOR' OR UPPER(T7."ReportCode") = 'IVACON-NOR' THEN COALESCE(T5."BaseSum", 0) ELSE 0 END AS "I7", 
CASE WHEN UPPER(T7."ReportCode") = 'C-NOR' OR UPPER(T7."ReportCode") = 'IVACON-NOR' THEN T5."VatSum" ELSE 0 END AS "I8", 
CASE WHEN T5."ExtVatSum" <> '0' THEN T5."ExtVatSum" ELSE 0 END AS "L", (T1."VatSum") AS "G", (T1."DocTotal") AS "H", (T1."WTSum") AS "WTSum", 'Q:' ||
 substring(T1."SignDigest", 1, 1) || substring(T1."SignDigest", 11, 1) || substring(T1."SignDigest", 21, 1) || substring(T1."SignDigest", 31, 1) AS "SignD",
CASE WHEN IFNULL(T8."AssignedID", '') = '' THEN '0' ELSE T8."AssignedID" || '-' || T1."DocNum" END AS "ATCUD"
FROM DPI12 T0 
INNER JOIN ODPI T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN OCRD T2 ON T1."CardCode" = T2."CardCode" 
INNER JOIN OTAX T6 ON T6."SrcObjType" = T1."ObjType" AND T6."SrcObjAbs" = T1."DocEntry" 
INNER JOIN TAX1 T5 ON T5."AbsEntry" = T6."AbsEntry" 
INNER JOIN OVTG T7 ON T5."TaxCode" = T7."Code"
INNER JOIN NNM1 T8 ON T1."Series" = T8."Series", OADM T3 WHERE T1."DocEntry" = :DocKey) 

AS tblData 
GROUP BY A, B, C, D, E, F, G1, G2, G3, G, H, "SignD", "ATCUD";

END;