Fix Code: Banca FV

create or replace PACKAGE BODY PKG_ANP_BANCA_FV AS --DONE

PROCEDURE MAIN(cur_month_yyyymm in NUMBER) AS
BEGIN
    BANCA_CUSTOMERS(cur_month_yyyymm);
    CNT_BANCA_CONTRACT(cur_month_yyyymm);
    NEW_CONTRACTS(cur_month_yyyymm);
    TOT_APE_AND_SUM_ASSURED(cur_month_yyyymm);
    LAST_BANCA_CONTRACT_SINCE(cur_month_yyyymm);
    COVERAGE(cur_month_yyyymm);
    FREELOOK_CONTRACTS(cur_month_yyyymm);

END MAIN;

 PROCEDURE BANCA_CUSTOMERS(cur_month_yyyymm in NUMBER) AS
    BEGIN
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'BANCA_CUSTOMERS', 'Begin processing: '||cur_month_yyyymm);
    DELETE FROM BANCA_FV WHERE MONTH_ID = cur_month_yyyymm;
    COMMIT;
   
    INSERT  INTO BANCA_FV (CST_NBR, MONTH_ID)
    SELECT DISTINCT CST_NBR, cur_month_yyyymm FROM TBL_BANCA WHERE CST_NBR IS NOT NULL;
    COMMIT;
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'BANCA_CUSTOMERS', 'End processing: '||cur_month_yyyymm);
  END BANCA_CUSTOMERS;
 
--#####################################################################################################################
 -- this procedure create 4 feature vector:
 --CNT_BANCA_CONTRACT_3M, CNT_BANCA_CONTRACT_6M, CNT_BANCA_CONTRACT_1Y,  CNT_BANCA_CONTRACT_EOM , CNT_BANCA_CONTRACT_2Y,
 -- BANCA_HOLD_2Y, BANCA_HOLD_1Y, BANCA_HOLD_9M, BANCA_HOLD_6M, BANCA_HOLD_3M, BANCA_HOLD_EOM,

   PROCEDURE CNT_BANCA_CONTRACT(cur_month_yyyymm in NUMBER) AS
    v_to_month number;
    v_from_month_3 number;
    v_from_month_6 number;
    v_from_month_12 number;
    v_from_month_24 number;
    BEGIN
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'CNT_BANCA_CONTRACT', 'Begin processing: '||cur_month_yyyymm);
    v_to_month := cur_month_yyyymm;
    v_from_month_3 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-2 ),'yyyymm'));
    v_from_month_6 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-5 ),'yyyymm'));
    v_from_month_12 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-11 ),'yyyymm'));
    v_from_month_24 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-23 ),'yyyymm'));
    ----------------------------------------------------------
    MERGE INTO BANCA_FV A
    USING (
        with dat1 as (
        select cst_nbr, ar_id
        from TBL_BANCA
        where cst_nbr is not null
        AND status IN ('VALID')
        and month_id  = v_to_month
        ),
        dat2 as (
        select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_EOM
        from dat1
        group by cst_nbr
        )
        select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_EOM ,
        CASE
        WHEN CNT_BANCA_CONTRACT_EOM >0  THEN 1
        ELSE 0
        END AS BANCA_HOLD_EOM
        FROM DAT2
        ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
    A.CNT_BANCA_CONTRACT_EOM = B.CNT_BANCA_CONTRACT_EOM , A.BANCA_HOLD_EOM = B.BANCA_HOLD_EOM
    WHEN NOT MATCHED then
    INSERT (A.CST_NBR,   A.MONTH_ID, A.CNT_BANCA_CONTRACT_EOM, A.BANCA_HOLD_EOM)
    VALUES (B.CST_NBR,   B.MONTH_ID, B.CNT_BANCA_CONTRACT_EOM, B.BANCA_HOLD_EOM) ;
    COMMIT;
   
    ----------------------------------------------------------
    MERGE INTO BANCA_FV A
    USING (
        with dat1 as (
        select cst_nbr, ar_id
        from TBL_BANCA
        where cst_nbr is not null
        AND status IN ('VALID')
        and month_id  between v_from_month_3 AND v_to_month
        ),
        dat2 as (
        select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_3M
        from dat1
        group by cst_nbr
        )
        select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_3M ,
        CASE
        WHEN CNT_BANCA_CONTRACT_3M >0  THEN 1
        ELSE 0
        END AS BANCA_HOLD_3M
        FROM DAT2
        ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
    A.CNT_BANCA_CONTRACT_3M = B.CNT_BANCA_CONTRACT_3M , A.BANCA_HOLD_3M = B.BANCA_HOLD_3M
    WHEN NOT MATCHED then
    INSERT (A.CST_NBR,   A.MONTH_ID, A.CNT_BANCA_CONTRACT_3M, A.BANCA_HOLD_3M)
    VALUES (B.CST_NBR,   B.MONTH_ID, B.CNT_BANCA_CONTRACT_3M, B.BANCA_HOLD_3M) ;
    COMMIT;
   
    -------------------------------------------------------------
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    select cst_nbr, ar_id
    from TBL_BANCA
    where cst_nbr is not null
    AND status IN ('VALID')
    and month_id  between v_from_month_6 AND v_to_month
    ),
    dat2 as (
    select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_6M
    from dat1
    group by cst_nbr
    )
    select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_6M,
     CASE
        WHEN CNT_BANCA_CONTRACT_6M >0  THEN 1
        ELSE 0
        END AS BANCA_HOLD_6M
    FROM DAT2
    ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.CNT_BANCA_CONTRACT_6M = B.CNT_BANCA_CONTRACT_6M , A.BANCA_HOLD_6M = B.BANCA_HOLD_6M
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.CNT_BANCA_CONTRACT_6M, A.BANCA_HOLD_6M)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.CNT_BANCA_CONTRACT_6M, B.BANCA_HOLD_6M) ;
    COMMIT;
    -------------------------------------------------------------
    -------------------------------------------------------------
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    select cst_nbr, ar_id
    from TBL_BANCA
    where CST_NBR IS NOT NULL
    AND status IN ('VALID')
    and month_id  between v_from_month_12 AND v_to_month
    ),
    dat2 as (
    select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_1Y
    from dat1
    group by cst_nbr
    )
    select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_1Y,
    CASE
        WHEN CNT_BANCA_CONTRACT_1Y >0  THEN 1
        ELSE 0
        END AS BANCA_HOLD_1Y
    FROM DAT2
    ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.CNT_BANCA_CONTRACT_1Y = B.CNT_BANCA_CONTRACT_1Y , A.BANCA_HOLD_1Y = B.BANCA_HOLD_1Y
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.CNT_BANCA_CONTRACT_1Y, A.BANCA_HOLD_1Y)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.CNT_BANCA_CONTRACT_1Y, B.BANCA_HOLD_1Y) ;
    COMMIT;
    -------------------------------------------------------------
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    select cst_nbr, ar_id
    from TBL_BANCA
    where CST_NBR IS NOT NULL
    AND status IN ('VALID')
    and month_id  between v_from_month_24 AND v_to_month
    ),
    dat2 as (
    select cst_nbr, count(distinct ar_id) as CNT_BANCA_CONTRACT_2Y
    from dat1
    group by cst_nbr
    )
    select cst_nbr, v_to_month as month_id, CNT_BANCA_CONTRACT_2Y,
    CASE
        WHEN CNT_BANCA_CONTRACT_2Y >0  THEN 1
        ELSE 0
        END AS BANCA_HOLD_2Y
    FROM DAT2
    ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.CNT_BANCA_CONTRACT_2Y = B.CNT_BANCA_CONTRACT_2Y , A.BANCA_HOLD_2Y =B.BANCA_HOLD_2Y
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.CNT_BANCA_CONTRACT_2Y, A.BANCA_HOLD_2Y)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.CNT_BANCA_CONTRACT_2Y, B.BANCA_HOLD_2Y) ;
    COMMIT;
   PKG_LOGS.log('PKG_ANP_BANCA_FV', 'CNT_BANCA_CONTRACT', 'End processing: '||cur_month_yyyymm);
  END CNT_BANCA_CONTRACT;
------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
  PROCEDURE NEW_CONTRACTS(cur_month_yyyymm in NUMBER) AS
    v_to_month number;
    v_from_month_3 number;
    v_from_month_6 number;
    v_from_month_12 number;
    v_from_month_24 number;
    BEGIN
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'NEW_CONTRACTS', 'Begin processing: '||cur_month_yyyymm);
   
    v_to_month := cur_month_yyyymm;
    v_from_month_3 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-2 ),'yyyymm'));
    v_from_month_6 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-5 ),'yyyymm'));
    v_from_month_12 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-11 ),'yyyymm'));
    v_from_month_24 := to_number(to_char(add_months(to_date(cur_month_yyyymm,'yyyymm'),-23 ),'yyyymm'));
 
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    SELECT CST_NBR, AR_ID, APE, sum_assured
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) = v_to_month
    and month_id =v_to_month
    AND STATUS IN ('VALID')
    )
    select cst_nbr, v_to_month AS MONTH_ID,
    SUM(APE) as TOT_APE_EOM ,
    COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_EOM,
    SUM(sum_assured) AS TOT_SUM_ASSURED_EOM
    from dat1
    group by cst_nbr
    ) B
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.TOT_APE_EOM = B.TOT_APE_EOM ,
        A.CNT_NEW_BANCA_CONTRACT_EOM = B.CNT_NEW_BANCA_CONTRACT_EOM,
        A.TOT_SUM_ASSURED_EOM = B.TOT_SUM_ASSURED_EOM
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.TOT_APE_EOM, A.CNT_NEW_BANCA_CONTRACT_EOM, A.TOT_SUM_ASSURED_EOM)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.TOT_APE_EOM, B.CNT_NEW_BANCA_CONTRACT_EOM, B.TOT_SUM_ASSURED_EOM) ;
    COMMIT;
--------------------------
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    SELECT CST_NBR, AR_ID, APE,  sum_assured
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) BETWEEN v_from_month_3 AND v_to_month
    AND STATUS IN ('VALID')
    AND MONTH_ID = to_number(to_char(NO_FREELOOK_DATE,'yyyymm'))
    )
    select cst_nbr, v_to_month AS MONTH_ID,
    SUM(APE) as TOT_APE_3M ,
    COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_3M,
    SUM(sum_assured) AS TOT_SUM_ASSURED_3M
   
    from dat1
    group by cst_nbr
    ) B
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.TOT_APE_3M = B.TOT_APE_3M , A.CNT_NEW_BANCA_CONTRACT_3M = B.CNT_NEW_BANCA_CONTRACT_3M, A.TOT_SUM_ASSURED_3M = B.TOT_SUM_ASSURED_3M
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.TOT_APE_3M, A.CNT_NEW_BANCA_CONTRACT_3M, A.TOT_SUM_ASSURED_3M)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.TOT_APE_3M, B.CNT_NEW_BANCA_CONTRACT_3M, B.TOT_SUM_ASSURED_3M) ;
    COMMIT;
--------------------------
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    SELECT CST_NBR, AR_ID, APE, SUM_ASSURED
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) BETWEEN v_from_month_6 AND v_to_month
    AND STATUS IN ('VALID')
    AND MONTH_ID = to_number(to_char(NO_FREELOOK_DATE,'yyyymm'))
    )
    select cst_nbr, v_to_month AS MONTH_ID,
    SUM(APE) as TOT_APE_6M ,
    COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_6M,
    SUM(sum_assured) AS TOT_SUM_ASSURED_6M
    from dat1
    group by cst_nbr
    ) B
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.TOT_APE_6M = B.TOT_APE_6M , A.CNT_NEW_BANCA_CONTRACT_6M = B.CNT_NEW_BANCA_CONTRACT_6M, A.TOT_SUM_ASSURED_6M = B.TOT_SUM_ASSURED_6M
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.TOT_APE_6M, A.CNT_NEW_BANCA_CONTRACT_6M, A.TOT_SUM_ASSURED_6M)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.TOT_APE_6M, B.CNT_NEW_BANCA_CONTRACT_6M, B.TOT_SUM_ASSURED_6M) ;
    COMMIT;
--------------------------
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    SELECT CST_NBR, AR_ID, APE, sum_assured
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) BETWEEN v_from_month_12 AND v_to_month
    AND STATUS IN ('VALID')
    AND MONTH_ID = to_number(to_char(NO_FREELOOK_DATE,'yyyymm'))
    )
    select cst_nbr, v_to_month AS MONTH_ID,
    SUM(APE) as TOT_APE_1Y ,
    COUNT(DISTINCT AR_ID) AS CNT_NEW_BANCA_CONTRACT_1Y,
    SUM(sum_assured) AS TOT_SUM_ASSURED_1Y
    from dat1
    group by cst_nbr
    ) B
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.TOT_APE_1Y = B.TOT_APE_1Y , A.CNT_NEW_BANCA_CONTRACT_1Y = B.CNT_NEW_BANCA_CONTRACT_1Y, A.TOT_SUM_ASSURED_1Y = B.TOT_SUM_ASSURED_1Y
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.TOT_APE_1Y, A.CNT_NEW_BANCA_CONTRACT_1Y, A.TOT_SUM_ASSURED_1Y)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.TOT_APE_1Y, B.CNT_NEW_BANCA_CONTRACT_1Y, B.TOT_SUM_ASSURED_1Y) ;
    COMMIT;
--------------------------
  END NEW_CONTRACTS;
-----------------------------------------------------------------------
PROCEDURE TOT_APE_AND_SUM_ASSURED(cur_month_yyyymm in NUMBER) AS

  BEGIN

    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    SELECT CST_NBR, AR_ID, APE, SUM_ASSURED
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND STATUS IN ('VALID')
    AND MONTH_ID = cur_month_yyyymm
    )
    select cst_nbr, cur_month_yyyymm AS MONTH_ID,
    SUM(APE) as TOT_APE ,
    SUM(sum_assured) AS TOT_SUM_ASSURED
    from dat1
    group by cst_nbr
       ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.TOT_APE = B.TOT_APE, A.TOT_SUM_ASSURED = B.TOT_SUM_ASSURED
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.TOT_APE, A.TOT_SUM_ASSURED)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.TOT_APE, B.TOT_SUM_ASSURED) ;
    COMMIT;
  PKG_LOGS.log('PKG_ANP_BANCA_FV', 'NEW_CONTRACTS', 'End processing: '||cur_month_yyyymm); 
  END TOT_APE_AND_SUM_ASSURED;
------------------------------------------------------------------------------------------------------------------------------
  PROCEDURE LAST_BANCA_CONTRACT_SINCE(cur_month_yyyymm in NUMBER) AS
  v_to_month number;
  BEGIN
  PKG_LOGS.log('PKG_ANP_BANCA_FV', 'LAST_BANCA_CONTRACT_SINCE', 'Begin processing: '||cur_month_yyyymm);
  v_to_month := cur_month_yyyymm;
 
    MERGE INTO BANCA_FV A
    USING (
    WITH DAT1 AS (
    SELECT DISTINCT CST_NBR, AR_ID, no_freelook_date
    FROM TBL_BANCA
    WHERE STATUS IN ('VALID')
    and to_number(to_char(NO_FREELOOK_DATE,'yyyymm')) <= v_to_month
    ),
    DAT2 AS (
    SELECT DAT1.*,
    ROW_NUMBER() OVER (PARTITION BY CST_NBR ORDER BY no_freelook_date desc) AS date_ORDER
    FROM DAT1
    )
    select cst_nbr, v_to_month AS MONTH_ID, last_day (to_date(v_to_month,'YYYYMM')) - no_freelook_date as LAST_BANCA_CONTRACT_SINCE
    from dat2 where date_order =1
    ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.LAST_BANCA_CONTRACT_SINCE = B.LAST_BANCA_CONTRACT_SINCE
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.LAST_BANCA_CONTRACT_SINCE)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.LAST_BANCA_CONTRACT_SINCE) ;
    COMMIT;
  PKG_LOGS.log('PKG_ANP_BANCA_FV', 'LAST_BANCA_CONTRACT_SINCE', 'End processing: '||cur_month_yyyymm); 
  END LAST_BANCA_CONTRACT_SINCE;


PROCEDURE COVERAGE (cur_month_yyyymm in NUMBER) AS   
    BEGIN
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'COVERAGE', 'Begin processing: '||cur_month_yyyymm);
   
    MERGE INTO BANCA_FV A
    USING (
    WITH DAT1 AS (
    SELECT * FROM (
    SELECT DISTINCT CST_NBR, AR_ID, RELATIONSHIP
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND STATUS IN ('VALID')
    AND MONTH_ID = cur_month_yyyymm
    )
    PIVOT (
    COUNT(DISTINCT AR_ID)
    FOR RELATIONSHIP
    IN (1 AS A1,2 AS A2)
    )
    ),
    DAT2 AS (
    SELECT CST_NBR, A1, A2,
     CASE
     WHEN A1 >0  AND A2 = 0 THEN 1
     WHEN A1 =0 AND A2 > 0 THEN 2
     WHEN A1 >= 0 AND A2 >= 0 THEN 3
     ELSE NULL
     END AS FL_COVERAGE
     FROM DAT1
    )
    SELECT CST_NBR, cur_month_yyyymm AS MONTH_ID, FL_COVERAGE
    FROM DAT2
    ) B
   
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.FL_COVERAGE = B.FL_COVERAGE
       
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.FL_COVERAGE)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.FL_COVERAGE) ;
    COMMIT;
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'COVERAGE', 'End processing: '||cur_month_yyyymm);
  END COVERAGE;
  --------------------------------------------------------------
 
  PROCEDURE FREELOOK_CONTRACTS(cur_month_yyyymm in NUMBER) AS
   
    BEGIN
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'FREELOOK_CONTRACTS', 'Begin processing: '||cur_month_yyyymm);
   
    MERGE INTO BANCA_FV A
    USING (
    with dat1 as (
    SELECT CST_NBR, AR_ID
    FROM TBL_BANCA
    WHERE CST_NBR IS NOT NULL
    AND STATUS IN ('FREELOOK')
    AND MONTH_ID = cur_month_yyyymm
    )
    select cst_nbr, cur_month_yyyymm AS MONTH_ID,
    COUNT(DISTINCT AR_ID) AS CNT_FREELOOK_CONTRACTS
    from dat1
    group by cst_nbr
    ) B
    ON (A.CST_NBR = B.CST_NBR AND  A.MONTH_ID = B.MONTH_ID)
    WHEN MATCHED THEN UPDATE SET
        A.CNT_FREELOOK_CONTRACTS = B.CNT_FREELOOK_CONTRACTS 
    WHEN NOT MATCHED then
     INSERT (A.CST_NBR,   A.MONTH_ID, A.CNT_FREELOOK_CONTRACTS)
     VALUES (B.CST_NBR,   B.MONTH_ID, B.CNT_FREELOOK_CONTRACTS) ;
    COMMIT;
    PKG_LOGS.log('PKG_ANP_BANCA_FV', 'FREELOOK_CONTRACTS', 'End processing: '||cur_month_yyyymm);
    END FREELOOK_CONTRACTS ;
--------END OF PACKAGE--------
END PKG_ANP_BANCA_FV;

Fix Code: Package Card Transaction

create or replace PACKAGE BODY PKG_ANP_ALL_CARD_TXN AS --DONE

  PROCEDURE ALL_CARD_TXN(cur_month_yyyymm IN number) AS
  BEGIN
 
  PKG_LOGS.log('PKG_ANP_ALL_CARD_TXN', 'ALL_CARD_TXN', 'Begin processing: '||cur_month_yyyymm);
  delete from TBL_FULL_CARD_TXN where MONTH_ID = cur_month_yyyymm;
  commit;
 
    INSERT /*+ APPEND NOLOGGING */ INTO TBL_FULL_CARD_TXN (CUSID, MONTH_ID, CARD_TYPE, ENTERTAINMENT_AMT, ENTERTAINMENT_CNT, RESTAURANT_AMT, RESTAURANT_CNT, HOTEL_AMT, HOTEL_CNT, HOSPITAL_AMT, HOSPITAL_CNT, SPA_AMT, SPA_CNT, SUPERMARKET_AMT, SUPERMARKET_CNT, SHOPPING_AMT, SHOPPING_CNT, KID_AMT, KID_CNT, BANK_AMT, BANK_CNT, OTHER_AMT, OTHER_CNT, SCHOOL_AMT, SCHOOL_CNT, AIRLINE_AMT, AIRLINE_CNT, VEHICLE_AMT, VEHICLE_CNT, OnlPay_AMT, OnlPay_CNT, TAX_AMT, TAX_CNT, FASHION_AMT, FASHION_CNT, HomeSer_AMT, HomeSer_CNT, PUBTRANS_AMT, PUBTRANS_CNT, PRITRANS_AMT, PRITRANS_CNT, FINANCE_AMT, FINANCE_CNT, PerSer_AMT, PerSer_CNT, OFFSER_AMT, OFFSER_CNT, EDUCATION_AMT, EDUCATION_CNT)


    with dat as
    (
      SELECT C.cst_nbr cusid,
          A.CDR_MTH_ID month_id,
          case when b.ACS_FCY_TP_CODE  IN ('CR_CARD') then 'CR_CARD' else 'DB_CARD' end CARD_TYPE,
          F.MCC_ANP_LVV2,
   
          SUM(TOT_TXN_AMT_LCY) TOT_TXN_AMT_LCY,
          sum(TOT_TXN_AMT_FCY) TOT_TXN_FCY,
          SUM(TOT_NBR_OF_TXN) TOT_NBR_OF_TXN
        FROM EDW_DMT.CMS_TXN_ANL_FCT_MO a ,
              EDW_DMT.AR_ACS_FCY_DIM b,
              EDW_DMT.CST_DIM C,
              EDW_DMT.ccy_dim d,
              anp_mcc_202001 F
        WHERE 1 =1
        AND A.CDR_MTH_ID = cur_month_yyyymm---UPDATE TIME WINDOW
        AND a.ar_dim_id         =b.ar_dim_id
        AND a.CST_DIM_ID        =c.CST_DIM_ID
        and a.ccy_dim_id = d.ccy_dim_id     
        and c.BSN_LINE = 'RETAIL' --Tung them ne :)
        AND a.MRCH_CGY_DIM_ID   =F.MRCH_CGY_DIM_ID
        AND b.ACS_FCY_TP_CODE  IN ('CR_CARD','LDR_CARD','DB_CARD') ----card only
        AND A.TXN_CGY_DIM_ID   IN (502, 541) ----withdraw, purchase
        AND A.TXN_LCS_TP_DIM_ID =1 --SUCCESS TXN
        AND a.tot_nbr_of_txn    >0
       
        GROUP BY C.cst_nbr,
          A.CDR_MTH_ID,
          case when b.ACS_FCY_TP_CODE  IN ('CR_CARD') then 'CR_CARD' else 'DB_CARD' end,
   
          F.MCC_ANP_LVV2
    ) , dat1 as
    (
    SELECT * FROM (SELECT cusid,month_id ,CARD_TYPE,MCC_ANP_LVV2,TOT_TXN_AMT_LCY,TOT_NBR_OF_TXN FROM DAT)
        PIVOT
        (SUM(TOT_TXN_AMT_LCY) AMT,
        SUM(TOT_NBR_OF_TXN) CNT
   
        FOR MCC_ANP_LVV2 IN
        ('Entertainment' Entertainment,
        'RESTAURANT' RESTAURANT,'Hotel' Hotel,'Hospital' Hospital,'Healthcare/Spa' SPA,'Supermarket' Supermarket,'Shopping' Shopping,
          'Kid' Kid,'Bank' Bank,'Other' Other,'Office_School_Supplies' School,'Airline' Airline,'Vehicle' Vehicle,'Online_payment' OnlPay,'Tax' Tax,'Fashion' Fashion,'Home_services' HomeSer,
          'PUBLIC_TRANSPORT' PUBTRANS,'Private_TRANSPORT' PRITRANS,'Finance' Finance,'Personal_services' PerSer,'Office_services'OffSer,'Education' Education
        )
        )
    ) select * from dat1;
  PKG_LOGS.log('PKG_ANP_ALL_CARD_TXN', 'ALL_CARD_TXN', 'End processing: '||cur_month_yyyymm); 
  commit;
  END ALL_CARD_TXN;

END PKG_ANP_ALL_CARD_TXN;

SQL Sever LOOP

select a.Customer_id
,'201801' as month_id
,b.segment_final
,case
when c.cussince < '20180101' then 'Existing'
else 'New'
end Cus_type
,SUM (a.NOTRANS_1M) no_tran
from [DC-ACI-DB].MONTHLY.dbo.CURRACCOUNT_201801 a
left join [DC-ACI-DB].MONTHLY.dbo.BUDGET2020_CUS_SEGMENT_201907 b
on a.Customer_id= b.cusid
left join [DC-ACI-DB].ACI_Common.dbo.customers_dwh c
on a.Customer_id= c.cusid
group by a.Customer_id
,b.segment_final
,case
when c.cussince < '20180101' then 'Existing' else 'New' end

----------------------------------------------------------------------------
drop TABLE #TMP_CAS_0712
CREATE TABLE #TMP_CAS_0712
(
CUSID VARCHAR(20),
MONTH_ID VARCHAR(20),
segment_final VARCHAR(20),
custype VARCHAR(20),
no_trans float

)

DECLARE @TU_NGAY VARCHAR(20)
DECLARE @DEN_NGAY VARCHAR(20)
DECLARE @TBL_TOI VARCHAR(1000)

SET @TU_NGAY = '201807'
SET @DEN_NGAY = '201812'
WHILE @TU_NGAY <= @DEN_NGAY
BEGIN
PRINT @TU_NGAY
SET @TBL_TOI = '[DC-ACI-DB].MONTHLY.dbo.CURRACCOUNT_' + @TU_NGAY
PRINT @TBL_TOI
EXEC
('
INSERT INTO #TMP_CAS_0712(CUSID, MONTH_ID, segment_final,custype,no_trans)
SELECT a.Customer_id
,'+@TU_NGAY+' as month_id
,b.segment_final
,case when c.cussince < ''20180101'' then ''Existing''
else ''New''
end Cus_type
,SUM (a.NOTRANS_1M) no_tran
from '+@TBL_TOI+' a
LEFT JOIN [DC-ACI-DB].MONTHLY.dbo.BUDGET2020_CUS_SEGMENT_201907 b
ON a.Customer_id= b.cusid
LEFT JOIN [DC-ACI-DB].ACI_Common.dbo.customers_dwh c
ON a.Customer_id= c.cusid
GROUP BY a.Customer_id
,b.segment_final
,case when c.cussince < ''20180101'' then ''Existing'' else ''New'' end
')
SET @TU_NGAY = CONVERT(VARCHAR(6), DATEADD(MONTH,1,@TU_NGAY + '01'),112)
END
SELECT *
INTO ANL.dbo.PHU_CASA_BUONBUON_0712
FROM #TMP_CAS_0712;

---------------------------------------------
CREATE TABLE #TMP_CAS_0712
(
CUSID VARCHAR(20),
MONTH_ID VARCHAR(20),
segment_final VARCHAR(20),
custype VARCHAR(20),
no_trans float

)

DECLARE @TU_NGAY VARCHAR(20)
DECLARE @DEN_NGAY VARCHAR(20)
DECLARE @TBL_TOI VARCHAR(1000)

SET @TU_NGAY = '201807'
SET @DEN_NGAY = '201812'
WHILE @TU_NGAY <= @DEN_NGAY
BEGIN
PRINT @TU_NGAY
SET @TBL_TOI = '[DC-ACI-DB].MONTHLY.dbo.CURRACCOUNT_' + @TU_NGAY
PRINT @TBL_TOI
EXEC
('
INSERT INTO #TMP_CAS_0712(CUSID, MONTH_ID, segment_final,custype,no_trans)
SELECT a.Customer_id
,'+@TU_NGAY+' as month_id
,b.segment_final
,case when c.cussince < ''20180101'' then ''Existing''
else ''New''
end Cus_type
,SUM (a.NOTRANS_1M) no_tran
from '+@TBL_TOI+' a
LEFT JOIN [DC-ACI-DB].MONTHLY.dbo.BUDGET2020_CUS_SEGMENT_201907 b
ON a.Customer_id= b.cusid
LEFT JOIN [DC-ACI-DB].ACI_Common.dbo.customers_dwh c
ON a.Customer_id= c.cusid
GROUP BY a.Customer_id
,b.segment_final
,case when c.cussince < ''20180101'' then ''Existing'' else ''New'' end
')
SET @TU_NGAY = CONVERT(VARCHAR(6), DATEADD(MONTH,1,@TU_NGAY + '01'),112)
END
SELECT *
INTO ANL.dbo.PHU_CASA_BUONBUON_0712
FROM #TMP_CAS_0712;

----------------------------
SELECT month_id, count (distinct cusid)
FROM ANL.dbo.PHU_CASA_BUONBUON_071218 group by month_id
--------------------------------------------------------------

CREATE TABLE #TMP_CAS_010619
(
CUSID VARCHAR(20),
MONTH_ID VARCHAR(20),
segment_final VARCHAR(20),
custype VARCHAR(20),
no_trans float

)

DECLARE @TU_NGAY VARCHAR(20)
DECLARE @DEN_NGAY VARCHAR(20)
DECLARE @TBL_TOI VARCHAR(1000)

SET @TU_NGAY = '201901'
SET @DEN_NGAY = '201906'
WHILE @TU_NGAY <= @DEN_NGAY
BEGIN
PRINT @TU_NGAY
SET @TBL_TOI = '[DC-ACI-DB].MONTHLY.dbo.CURRACCOUNT_' + @TU_NGAY
PRINT @TBL_TOI
EXEC
('
INSERT INTO #TMP_CAS_010619(CUSID, MONTH_ID, segment_final,custype,no_trans)
SELECT a.Customer_id
,'+@TU_NGAY+' as month_id
,b.segment_final
,case when c.cussince < ''20190101'' then ''Existing''
else ''New''
end Cus_type
,SUM (a.NOTRANS_1M) no_tran
from '+@TBL_TOI+' a
LEFT JOIN [DC-ACI-DB].MONTHLY.dbo.BUDGET2020_CUS_SEGMENT_201907 b
ON a.Customer_id= b.cusid
LEFT JOIN [DC-ACI-DB].ACI_Common.dbo.customers_dwh c
ON a.Customer_id= c.cusid
GROUP BY a.Customer_id
,b.segment_final
,case when c.cussince < ''20190101'' then ''Existing'' else ''New'' end
')
SET @TU_NGAY = CONVERT(VARCHAR(6), DATEADD(MONTH,1,@TU_NGAY + '01'),112)
END
SELECT *
INTO ANL.dbo.PHU_CASA_BUONBUON_010619
FROM #TMP_CAS_010619;

select month_id, count(distinct cusid) from ANL.dbo.PHU_CASA_BUONBUON_010619 group by month_id ;

select cusid, min_start_date from [DC-ACI-DB].ACI_Echannel.[dbo].[cus_ebank_20190930];

SELECT * FROM ANL.DBO.CUS_ALL_MASK WHERE CUSID ='33725918'

select * from [DC-ACI-DB].[aci_COMMON].