-- 14 INV AR Credit Memo
-- CALL TmSp_QRCodeSPGenerated_ES_14(:DocKey, :ObjectId);
-- DROP PROCEDURE TmSp_QRCodeSPGenerated_ES_14;

--- testing server URL
--- https://prewww2.aeat.es/wlpl/TIKE-CONT/ValidarQR?nif=XXXXXXXXY&numserie=YYYY...YYYY&fecha=DD-MMAAAA&importe=NNNNNNNNN.DD
--- production server URL
--- https://www2.agenciatributaria.gob.es/wlpl/TIKECONT/ValidarQR?nif=XXXXXXXXY&numserie=YYYY...YYYY&fecha=DD-MMAAAA&importe=NNNNNNNNN.DD

CREATE PROCEDURE TmSp_QRCodeSPGenerated_ES_14 (in DocKey integer, in ObjectID integer)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

SELECT  F || '?nif=' || A || '&numserie=' || B || '-' || C || '&fecha=' || D || '&importe=' || E

FROM (
	SELECT

	substring (T5."TaxIdNum", 3, 9) AS "A",
	CAST(T1."Series" AS varchar) as "B", 
	CAST(T0."DocNum" AS varchar) as "C",
	TO_CHAR(T0."DocDate", 'DD-MM-YYYY') as "D",
	CAST(CAST(T0."DocTotal" AS decimal(14, 2)) AS varchar) as "E",
	(SELECT top 1 CAST(T3."ParamValue" AS VARCHAR(500)) from ECM1 T3 where T3."Code" = '5' and T3."LineNum" = -511 and T3."BPLId" = -1) as "F"
	
	FROM ORIN T0
	INNER JOIN NNM1 T1 ON T0."Series" = T1."Series"
	INNER JOIN ECM2 T2 ON T2."SrcObjAbs" = T0."DocEntry"  and T2."SrcObjType" = T0."ObjType" and T2."Code" = '5',  -- code 5 is VERI*Factu
	OADM T5
	WHERE T0."DocEntry" = :DocKey
	) AS tblData

GROUP BY A, B, C, D, E, F;

END;