Untitled
unknown
plain_text
2 years ago
7.1 kB
4
Indexable
/*
************************* DATA WAREHOUSE SQL SCRART ***************************
SCRIPT-TYPE: STORED PROCEDURE
USE: RISKDB_B
NAME: PE_PRXNVCM
LIBRARY: DWHCDE
PROCESS: 852
STEP SEQUENCE: 1
DESCRIPTION: LOAD Proxima Invoice ARs - Monthly
AFFECTED TABLES: RISKDB_B.PRXMINVCM
PARAMETERS: @I_BSNDT: BUSINESS DATE, DATE
AUTHOR: A.Seira
DATE CREATED: 2023-05-23
DATE MODIFIED: -
*******************************************************************************
*/
---------- DROP PROCEDURE IF IT EXISTS ----------
CALL DWHCDE.PU_DROP('PROCEDURE','DWHCDE.PE_PRXNVCM');
---------- PROCEDURE CODE ----------
CREATE PROCEDURE DWHCDE.PE_PRXNVCM (
IN @I_BSNDT DATE
)
LANGUAGE SQL
SPECIFIC DWHCDE.PE_PRXNVCM
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
---------- VARIABLE DECLARATIONS ----------
DECLARE @FSTMOCDRDT DATE;
DECLARE @VN_CL006DWHNMNLINVCVAL INT;
DECLARE @VN_CL006DWHNETINVCVAL INT;
DECLARE @VN_CL006DWHFNINVCAMT INT;
DECLARE @VN_CL006DWHNETINVCINR INT;
DECLARE @VN_CL004PRXMINVC INT;
SET @FSTMOCDRDT = LEFT(CHAR(@I_BSNDT),8)||'01';
SET @VN_CL006DWHNMNLINVCVAL = DWHCDE.FU_GETCLID(6,'DWHNMNLINVCVAL');--45286
SET @VN_CL006DWHNETINVCVAL = DWHCDE.FU_GETCLID(6,'DWHNETINVCVAL');--45285
SET @VN_CL006DWHFNINVCAMT = DWHCDE.FU_GETCLID(6,'DWHFNINVCAMT');--45287
SET @VN_CL006DWHNETINVCINR = DWHCDE.FU_GETCLID(6,'DWHNETINVCINR');--45288
SET @VN_CL004PRXMINVC = DWHCDE.FU_GETCLID(4,'PRXMINVC');--45253
---------- MAIN PROGRAM ----------
INSERT INTO RISKDB_B.PRXMINVCM
(
IPCD,
ARCD,
ARSTSFLG,
ACTVOVRDFLG,
NOTSTLDFLG,
INVCPDCD,
INVCPDDSC,
INVCCCYCD,
ISSDT,
DDT,
INVCAMT,
VLDDT,
FNNCDT,
PMTDT,
CRNTSAMT,
FNNCAMT,
CMNAMT,
BNMRTCD,
BNMRTTNR,
DSCRT,
APBNMRT,
SPRT,
N128RT,
DCNTMD,
FNNCPRD,
CSTACCCD,
SPPLRCD,
SPPLRNM,
SPPLRIBAN,
SPPLRIPCD
)
WITH
--- CURRENCY MNEMONIC
RS_CCY (CCYCD, CCYID) AS
(SELECT CLCD, CLID
FROM DWHSORTRG.CL02T
WHERE CLSCMID = 29),
--- BENCHMARK RATE
RS_BNMR (BNMRTCD, ARID) AS
(SELECT
CNGEN.UNQID AS BNMRTCD,
AR12T.ARID AS ARID
FROM DWHSORTRG.AR12T AR12T
LEFT OUTER
JOIN DWHSORSTG.CNGEN CNGEN
ON CNGEN.CNID = AR12T.BNMRTID
WHERE CNGEN.SSID = @VN_CL004PRXMINVC--45253
),
--- UNIVERSE FUTURE DUE DATES AND SETTLED WITHIN THE CURRENT MONTH
RS_UNV AS
(SELECT
AR59T.*,
ROW_NUMBER()OVER(PARTITION BY AR59T.ARID ORDER BY EFFDT DESC) AS ROW_NUM
FROM DWHSORTRG.AR59T AR59T
WHERE
(AR59T.NOTSTLDFLG = 0 AND AR59T.PMTDT BETWEEN @FSTMOCDRDT AND @I_BSNDT)
OR (AR59T.NOTSTLDFLG = 1 AND AR59T.DDT >= @I_BSNDT AND @I_BSNDT BETWEEN AR59T.EFFDT AND AR59T.ENDDT)
),
RS_JQACC AS
(SELECT
IFNULL(ARGEN.UNQID,'-100') AS CSTACCCD,
AR02T.CSTID AS CSTID,
AR02T.ORICCYID AS ORICCYID,
ROW_NUMBER()OVER(PARTITION BY AR01T.ARID ORDER BY AR01T.EFFDT DESC) AS ROW_NUM
FROM DWHSORTRG.PD01T PD01T
INNER
JOIN DWHSORTRG.AR02T AR02T
ON AR02T.PDID = PD01T.PDID
LEFT OUTER
JOIN DWHSORSTG.ARGEN ARGEN
ON ARGEN.ARID = AR02T.PDARID
INNER
JOIN DWHSORTRG.AR01T AR01T
ON AR01T.ARID = AR02T.PDARID
AND ACLENDDT = '9999-12-31'
WHERE PD01T.UNQID = '7136' --'3999'
)
SELECT
IFNULL(IPGEN.UNQID,'-100') AS IPCD,
ARGEN.UNQID AS ARCD,
CASE
WHEN RS_UNV.VLDLCTVFLG = 1
THEN 1
WHEN RS_UNV.ISFNFLG = 1
THEN 2
ELSE -1
END AS ARSTSFLG,
RS_UNV.ACTOVRDFLG AS ACTVOVRDFLG,
RS_UNV.NOTSTLDFLG AS NOTSTLDFLG,
RS_UNV.INVCPDCD AS INVCPDCD,
RS_UNV.INVCPDDSC AS INVCPDDSC,
IFNULL(RS_CCY.CCYCD,'') AS INVCCCYCD,
RS_UNV.ISSDT AS ISSDT,
RS_UNV.DDT AS DDT,
AU01T_NML.AMT AS INVCAMT,
RS_UNV.VLDDT AS VLDDT,
RS_UNV.FNNCDT AS FNNCDT,
RS_UNV.PMTDT AS PMTDT,
AU01T_NET.AMT AS CRNTSAMT,
AU01T_FNC.AMT AS FNNCAMT,
AU01T_INR.AMT AS CMNAMT,
IFNULL(RS_BNMR.BNMRTCD,'') AS BNMRTCD,
-100 AS BNMRTTNR,
AR12T.BNMSPRPTG AS DSCRT,
AR12T.BNMRTPTG AS APBNMRT,
AR12T.BNMSPRPTG AS SPRT,
AR12T.ADTCHGPTG AS N128RT,
AR12T.DCMTD AS DCNTMD,
RS_UNV.FNNCPRD AS FNNCPRD,
IFNULL(RS_JQACC.CSTACCCD,'') AS CSTACCCD,
RS_UNV.SPPLRCD AS SPPLRCD,
RS_UNV.SPPLRNM AS SPPLRNM,
RS_UNV.SPPLRIBAN AS SPPLRIBAN,
RS_UNV.SPPLRIPCD AS SPPLRIPCD
FROM RS_UNV RS_UNV
LEFT OUTER
JOIN RS_JQACC RS_JQACC
ON RS_JQACC.CSTID = RS_UNV.IPID
AND RS_JQACC.ORICCYID = RS_UNV.INVCCCYID
AND RS_JQACC.ROW_NUM = 1
LEFT OUTER
JOIN DWHSORSTG.IPGEN IPGEN
ON IPGEN.IPID = RS_UNV.IPID
LEFT OUTER
JOIN DWHSORSTG.ARGEN ARGEN
ON ARGEN.ARID = RS_UNV.ARID
LEFT OUTER
JOIN RS_CCY RS_CCY
ON RS_CCY.CCYID = RS_UNV.INVCCCYID
LEFT OUTER
JOIN DWHSORTRG.AU01T AU01T_NML
ON AU01T_NML.ARID = RS_UNV.ARID
AND AU01T_NML.AUTPID = @VN_CL006DWHNMNLINVCVAL--45286
AND AU01T_NML.ENDDT = '9999-12-31'
LEFT OUTER
JOIN DWHSORTRG.AU01T AU01T_NET
ON AU01T_NET.ARID = RS_UNV.ARID
AND AU01T_NET.AUTPID = @VN_CL006DWHNETINVCVAL--45285
AND AU01T_NET.ENDDT = '9999-12-31'
LEFT OUTER
JOIN DWHSORTRG.AU01T AU01T_FNC
ON AU01T_FNC.ARID = RS_UNV.ARID
AND AU01T_FNC.AUTPID = @VN_CL006DWHFNINVCAMT--45287
AND AU01T_FNC.ENDDT = '9999-12-31'
LEFT OUTER
JOIN DWHSORTRG.AU01T AU01T_INR
ON AU01T_INR.ARID = RS_UNV.ARID
AND AU01T_INR.AUTPID = @VN_CL006DWHNETINVCINR--45288
AND AU01T_INR.ENDDT = '9999-12-31'
LEFT OUTER
JOIN RS_BNMR RS_BNMR
ON RS_BNMR.ARID = RS_UNV.ARID
LEFT OUTER
JOIN DWHSORTRG.AR12T AR12T
ON AR12T.ARID = RS_UNV.ARID
AND (@I_BSNDT BETWEEN AR12T.EFFDT AND AR12T.ENDDT)
WHERE RS_UNV.ROW_NUM = 1
;
END;
---------- PROCEDURE COMMENT ----------
COMMENT ON SPECIFIC PROCEDURE DWHCDE.PE_PRXNVCM
IS 'LOAD Proxima Invoice ARs - Monthly';
Editor is loading...