QASP-1

Table of Contents

QUESTION:

ANALYSIS:

SOLVE:

POST-PROSESSING:

---

QUESTION:

    CAJOB-RETL_NEWUAT_H3_ETLCA_RECON_N FAILD

ANALYSIS:

    1.DB

--have data:
SELECT COUNT(*) FROM SSA_F_GTD_N_AGG WHERE cnty_cd='H3' AND mth_perd='20240131' ;
SELECT COUNT(*) FROM  SOR_F_VO WHERE cnty_cd='H3' AND mth_perd='20240131' and load_sch='N' AND BUS_RULE IS NULL;
SELECT COUNT(*) FROM  SMA_F_JV_TXN WHERE cnty_cd='H3' AND mth_perd='20240131' and load_sch='N' AND BUS_RULE IS NULL;

--no data:
SELECT count(*) FROM  ADS_F_SAP_DETAIL_JV WHERE cnty_cd='H3' AND mth_perd='20240131' and load_sch='N' ;
SELECT COUNT(*) FROM  ADS_F_SAP_JV WHERE  mth_perd='20240131' and load_sch='N' ;
SELECT COUNT(*) FROM  ADS_F_SAP_JV_ITEM WHERE  mth_perd='20240131' and load_sch='N' ;

    2.DS
        2.1 KEY POINT:DSjob-I_Ex_FDS_SMA_F_JV_TXN
          

 select    
                CURR.CURR_CD,CS.COLL_STAT,DEPT.DEPT_CD_1,DEPT.DEPT_CD_2,COMP.COMPANY_CD,TER.TER_CD,COA.OLD_ACCT_CD,JV.PK_JV_TXN_REC_ID_S,JV.FK_CURR_REC_ID_S,JV.FK_COLL_STAT_REC_ID_S,JV.FK_DEPT_REC_ID_S,JV.FK_ORGN_REC_ID_S,JV.FK_COA_REC_ID_S,JV.FK_TIME_REC_ID_S,JV.VO_AMT,JV.FK_BUS_COND_REC_ID_S,JV.FK_BUS_REC_ID_S,JV.CUR_MTH_DT,JV.VO_EFF_YR,JV.VO_EFF_MTH,JV.FK_VO_REC_ID_S,JV.CREATE_TS,JV.CREATE_EVENT_ID,JV.LAST_MODIFIED_EVENT_ID,JV.LAST_MODIFIED_TS,JV.LAST_MODIFIED_BY,JV.VO_NO,JV.VO_DT,JV.VO_DESC,JV.VO_TYPE_CD,JV.VO_TXN_CD,JV.VO_PAR_CD,JV.REC_TYPE_CD,JV.MTH_PERD,JV.LOAD_SCH,JV.CNTY_CD,JV.HDR_SUB_KEY,JV.DOC_TYPE,JV.DOC_DT,JV.POSTING_DT,JV.TRANSLATION_DT,JV.REF,JV.DOC_HDR_TXT,JV.EXCHANGE_RT,JV.LEDGER_GRP,JV.POSTING_KEY,JV.TXN_TYPE,JV.CALCULATE_TAX,JV.ASSIGNMENT,JV.TXT,JV.REF_KEY_3,JV.LOCAL_FUND_CD,JV.FK_BASIC_PLAN_REC_ID_S,JV.FK_RIDER_PLAN_REC_ID_S,JV.FUND_CD,JV.PCCI_CD,JV.BUS_RULE,JV.SUM_TYPE,JV.NEG_POSTING_IND, JV.ASSET_CD,JV.PREM_AGING_INDICATOR,JV.SACCODE_TYPE,JV.SAP_OCOA_NO,JV.CHEQUE_NO,JV.REINSURER_CD,JV.VAL_DT,JV.BASIC_RIDER_IND,JV.CURRENCY2,JV.NEW_COMPANY_CD,JV.LOCAL_AMT,JV.TAX_AMT,JV.LOCAL_TAX_AMT,JV.TAX_CD,JV.INTERNAL_ORDER,JV.TREATY_CD,JV.USER_FIELD_9,JV.USER_FIELD_10,JV.USER_FIELD_11,JV.USER_FIELD_12,JV.USER_FIELD_13,JV.USER_FIELD_14,JV.ETL_USER_FIELD_1,JV.ETL_USER_FIELD_2,JV.ETL_USER_FIELD_3,JV.ETL_USER_FIELD_4,JV.ETL_USER_FIELD_5,JV.ETL_USER_FIELD_6,JV.ETL_USER_FIELD_7,JV.ETL_USER_FIELD_8,JV.ETL_USER_FIELD_9,JV.ETL_USER_FIELD_10,JV.ETL_USER_FIELD_11,JV.ETL_USER_FIELD_12,JV.ETL_USER_FIELD_13,JV.ETL_USER_FIELD_14,JV.ETL_USER_FIELD_15
            from SMA_F_JV_TXN JV, SOR_D_CURR_CD CURR, SOR_D_COLL_STAT CS, SOR_D_DEPT_CD DEPT, SOR_D_ORGN_DESC OD, SOR_D_COMPANY_CD COMP, SOR_D_TER_CD TER, SOR_D_COA COA
            where JV.FK_CURR_REC_ID_S = CURR.PK_CURR_REC_ID_S
                and JV.FK_COLL_STAT_REC_ID_S = CS.PK_COLL_STAT_REC_ID_S
                and JV.FK_DEPT_REC_ID_S = DEPT.PK_DEPT_REC_ID_S
                and JV.FK_ORGN_REC_ID_S = OD.PK_ORGN_REC_ID_S
                and OD.FK_COMPANY_REC_ID_S = COMP.PK_COMPANY_REC_ID_S
                and OD.FK_TER_REC_ID_S = TER.PK_TER_REC_ID_S
                and JV.FK_COA_REC_ID_S = COA.PK_COA_REC_ID_S
                and JV.FK_TIME_REC_ID_S = '20240131'      --key point
                and JV.LOAD_SCH = 'N'
                and JV.CNTY_CD='H3' ;

        2.2 DSjob-I_Tr_FDS_SOR_F_VO_10.LKP_01
            FK_TIME_REC_ID_S get from sor_d_time.pk_time_rec_id_s

SOLVE:

    1.DB
        INSERT INTO I_Tr_FDS_SOR_F_VO_10 Corresponding Date
         

Insert into HFDSSSOR.sor_d_time (PK_TIME_REC_ID_S,TIME_VAL_DT,LAST_MODIFIED_TS,LAST_MODIFIED_BY,MTH_END_VAL,CREATE_TS,CREATE_EVENT_ID,LAST_MODIFIED_EVENT_ID,CNTY_CD) 
values (20240131,to_timestamp('31-JAN-24','DD-MON-RR HH.MI.SS.FF AM'),to_timestamp('21-JUN-19 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),'C','20240131',to_timestamp('21-JUN-17 12.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM'),-1,-1,'G*');
commit;

    2.RERUN CYCLE FROM AC4

POST-PROSESSING:

    DECLARE
        x date;
        sdate   varchar2(8 char);
        edate   varchar2(8 char);
    BEGIN
        sdate   := '20240101';
        edate   := '20241231';
        x       := to_date(sdate,'YYYYMMDD');
        WHILE x <= to_date(edate,'YYYYMMDD')
        LOOP
            Insert into HFDSSSOR.sor_d_time (PK_TIME_REC_ID_S,TIME_VAL_DT,LAST_MODIFIED_TS,LAST_MODIFIED_BY,MTH_END_VAL,CREATE_TS,CREATE_EVENT_ID,LAST_MODIFIED_EVENT_ID,CNTY_CD) 
                values (sdate,x,systimestamp,'C',sdate,systimestamp,-1,-1,'G*');
            --DBMS_OUTPUT.PUT_LINE('x=' || x);
            x     := x + 1;
            sdate := to_char(x,'YYYYMMDD');
        END LOOP;
        COMMIT;
        --DBMS_OUTPUT.PUT_LINE('end loop x=' || x);
    END;