Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
7.1 kB
2
Indexable
Never
/*
************************* 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';