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;