/* Generate the process id for job */ %put Process ID: &SYSJOBID; /* General macro variables */ %let jobID = %quote(A5VECZJZ.BE0000BF); %let etls_jobName = %nrquote(FACT_WORK); %let etls_userID = %nrquote(bcwetl); /* Setup to capture return codes */ %global job_rc trans_rc sqlrc; %let sysrc = 0; %let job_rc = 0; %let trans_rc = 0; %let sqlrc = 0; %global etls_stepStartTime; /* initialize syserr to 0 */ data _null_; run; %macro rcSet(error); %if (&error gt &trans_rc) %then %let trans_rc = &error; %if (&error gt &job_rc) %then %let job_rc = &error; %mend rcSet; %macro rcSetDS(error); if &error gt input(symget('trans_rc'),12.) then call symput('trans_rc',trim(left(put(&error,12.)))); if &error gt input(symget('job_rc'),12.) then call symput('job_rc',trim(left(put(&error,12.)))); %mend rcSetDS; /* Create metadata macro variables */ %let IOMServer = %nrquote(SASApp); %let metaPort = %nrquote(8561); %let metaServer = %nrquote(ph0236p.theaa.local); /* Set metadata options */ options metaport = &metaPort metaserver = "&metaServer"; /* Setup for capturing job status */ %let etls_startTime = %sysfunc(datetime(),datetime.); %let etls_recordsBefore = 0; %let etls_recordsAfter = 0; %let etls_lib = 0; %let etls_table = 0; %global etls_debug; %macro etls_setDebug; %if %str(&etls_debug) ne 0 %then OPTIONS MPRINT%str(;); %mend; %etls_setDebug; /*---- Start of Pre-Process Code ----*/ %get_avail_dates(load_type=mart_control, load_name=FACT_WORK); %global etls_recordsBefore; %global etls_recordsAfter; /*---- End of Pre-Process Code ----*/ %rcSet(&syserr); %rcSet(&sqlrc); /*==========================================================================* * Step: Extract current interface date(s) A5VECZJZ.BJ0000TO * * Transform: Extract * * Description: * * * * Source Table: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * * bcw_dtl.ETL_WORK_ITEM_NUM * * Target Table: Extract Target - A5VECZJZ.BK0000JA * * work.W667OQ0R_rule1_1 * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TO); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(bcw_dtl.ETL_WORK_ITEM_NUM); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W667OQ0R_rule1_1; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W667OQ0R_rule1_1 as select WORK_ITEM_REFERENCE_KEY, CUSTOMER_ID, WORK_ITEM_ID, WORK_ITEM_KEY, INTERFACE_DTTM from &SYSLAST where INTERFACE_DTTM >= &min_load_dttm and INTERFACE_DTTM < &max_load_dttm and work_item_id in ( 6032488, 6032491 ) order by WORK_ITEM_ID, WORK_ITEM_REFERENCE_KEY ; quit; %let SYSLAST = work.W667OQ0R_rule1_1; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Extract current interface date(s) **/ /*==========================================================================* * Step: Dedupe by work_item_id A5VECZJZ.BJ0000TP * * Transform: Sort * * Description: * * * * Source Table: Extract Target - A5VECZJZ.BK0000JA * * work.W667OQ0R_rule1_1 * * Target Table: Sort Target - work.W667OQT8_rule1_2 A5VECZJZ.BK0000JB * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TP); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(work.W667OQ0R_rule1_1); proc datasets lib = work nolist nowarn memtype = (data view); delete W667OQT8_rule1_2; quit; proc sort data = &SYSLAST out = work.W667OQT8_rule1_2 NODUPKEY ; by WORK_ITEM_ID ; run; %rcSet(&syserr); /** Step end Dedupe by work_item_id **/ /*==========================================================================* * Step: Get latest version of work item data A5VECZJZ.BJ0000TQ * * Transform: Join * * Description: * * * * Source Tables: ETL_WORK_ITEM - bcw_dtl.ETL_WORK_ITEM A5VECZJZ.BQ00007K * * Sort Target - work.W667OQT8_rule1_2 A5VECZJZ.BK0000JB * * Target Table: SQL Target - work.W667OVS7_rule2 A5VECZJZ.BK0000JC * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TQ); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W667OVS7_rule2; quit; proc sql; create table work.W667OVS7_rule2 as select W667OQT8_rule1_2.WORK_ITEM_REFERENCE_KEY length = 8 format = 12. informat = 12., W667OQT8_rule1_2.CUSTOMER_ID length = 8 format = 12. informat = 12., W667OQT8_rule1_2.WORK_ITEM_ID length = 8 format = 12. informat = 12., W667OQT8_rule1_2.WORK_ITEM_KEY length = 8 format = 12. informat = 12., W667OQT8_rule1_2.INTERFACE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., ETL_WORK_ITEM.COMPLETED_DTTM as WORK_COMPLETE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., ETL_WORK_ITEM.CREATED_DTTM as WORK_CREATED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., case when ETL_WORK_ITEM.PROM_CODE_KEY_SOURCE is null and ETL_WORK_ITEM.PROM_CODE_ID_SOURCE is null then 0 when ETL_WORK_ITEM.PROM_CODE_KEY_SOURCE is null and ETL_WORK_ITEM.PROM_CODE_ID_SOURCE is not null then -1 else ETL_WORK_ITEM.PROM_CODE_KEY_SOURCE end as PROM_CODE_KEY_SOURCE length = 8 format = 11. informat = 11., ETL_WORK_ITEM.REASON_ID as REASON_ID_WORK length = 8 format = 11. informat = 11., ETL_WORK_ITEM.REFERRAL_STAFF_NAME length = 50 format = $50. informat = $50., ETL_WORK_ITEM.REFERRAL_STAFF_NO length = 10 format = $10. informat = $10., ETL_WORK_ITEM.REFERRAL_STAFF_DEPT length = 50 format = $50. informat = $50., ETL_WORK_ITEM.REFERRAL_STAFF_MANAGER length = 50 format = $50. informat = $50., case when ETL_WORK_ITEM.STATUS_KEY is null and ETL_WORK_ITEM.STATUS_ID is null then 0 when ETL_WORK_ITEM.STATUS_KEY is null and ETL_WORK_ITEM.STATUS_ID is not null then -1 else ETL_WORK_ITEM.STATUS_KEY end as STATUS_KEY_WORK length = 8 format = 11. informat = 11., case when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is null then 0 when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is not null then -1 else ETL_WORK_ITEM.TEAM_KEY_OWNER end as TEAM_KEY_OWNER_LTST length = 8 format = 11. informat = 11., case when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is null then 0 when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is not null then -1 else ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER end as TEAM_MEMBER_KEY_OWNER_LTST length = 8 format = 11. informat = 11., case when ETL_WORK_ITEM.WORK_ITEM_TYPE_KEY is null and ETL_WORK_ITEM.WORK_ITEM_TYPE_ID is null then 0 when ETL_WORK_ITEM.WORK_ITEM_TYPE_KEY is null and ETL_WORK_ITEM.WORK_ITEM_TYPE_ID is not null then -1 else ETL_WORK_ITEM.WORK_ITEM_TYPE_KEY end as WORK_ITEM_TYPE_KEY length = 8 format = 11. informat = 11., ETL_WORK_ITEM.WORK_ITEM_SUB_TYPE_ID length = 8 format = 11. informat = 11., ETL_WORK_ITEM.WORK_ITEM_TYPE_ID length = 8 format = 11. informat = 11. from work.W667OQT8_rule1_2 left join bcw_dtl.ETL_WORK_ITEM on ( W667OQT8_rule1_2.WORK_ITEM_KEY = ETL_WORK_ITEM.WORK_ITEM_KEY ) ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Get latest version of work item data **/ /*==========================================================================* * Step: Get first version of work item data A5VECZJZ.BJ0000TR * * Transform: Join * * Description: * * * * Source Tables: ETL_WORK_ITEM - bcw_dtl.ETL_WORK_ITEM A5VECZJZ.BQ00007K * * SQL Target - work.W667OVS7_rule2 A5VECZJZ.BK0000JC * * Target Table: SQL Target - work.W667P4W9_rule3_1 A5VECZJZ.BK0000JD * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TR); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W667P4W9_rule3_1; quit; proc sql; create table work.W667P4W9_rule3_1 as select W667OVS7_rule2.WORK_ITEM_REFERENCE_KEY length = 8 format = 12. informat = 12., W667OVS7_rule2.CUSTOMER_ID length = 8 format = 12. informat = 12., W667OVS7_rule2.WORK_ITEM_ID length = 8 format = 12. informat = 12., W667OVS7_rule2.WORK_ITEM_KEY length = 8 format = 12. informat = 12., W667OVS7_rule2.INTERFACE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667OVS7_rule2.WORK_COMPLETE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667OVS7_rule2.WORK_CREATED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667OVS7_rule2.PROM_CODE_KEY_SOURCE length = 8 format = 11. informat = 11., W667OVS7_rule2.REASON_ID_WORK length = 8 format = 11. informat = 11., W667OVS7_rule2.REFERRAL_STAFF_NAME length = 50 format = $50. informat = $50., W667OVS7_rule2.REFERRAL_STAFF_NO length = 10 format = $10. informat = $10., W667OVS7_rule2.REFERRAL_STAFF_DEPT length = 50 format = $50. informat = $50., W667OVS7_rule2.REFERRAL_STAFF_MANAGER length = 50 format = $50. informat = $50., W667OVS7_rule2.STATUS_KEY_WORK length = 8 format = 11. informat = 11., W667OVS7_rule2.TEAM_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667OVS7_rule2.TEAM_MEMBER_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667OVS7_rule2.WORK_ITEM_TYPE_KEY length = 8 format = 11. informat = 11., W667OVS7_rule2.WORK_ITEM_SUB_TYPE_ID length = 8 format = 11. informat = 11., W667OVS7_rule2.WORK_ITEM_TYPE_ID length = 8 format = 11. informat = 11., case when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is null then 0 when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is not null then -1 else ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER end as TEAM_MEMBER_KEY_OWNER_FRST length = 8 format = 11. informat = 11., case when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is null then 0 when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is not null then -1 else ETL_WORK_ITEM.TEAM_KEY_OWNER end as TEAM_KEY_OWNER_FRST length = 8 format = 11. informat = 11., ETL_WORK_ITEM.VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20. from work.W667OVS7_rule2 left join bcw_dtl.ETL_WORK_ITEM on ( W667OVS7_rule2.WORK_ITEM_ID = ETL_WORK_ITEM.WORK_ITEM_ID ) order by W667OVS7_rule2.WORK_ITEM_ID, ETL_WORK_ITEM.VALID_FROM_DTTM ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Get first version of work item data **/ /*==========================================================================* * Step: Dedupe to keep version of work item A5VECZJZ.BJ0000TS * * with earliest valid from * * Transform: Sort * * Description: * * * * Source Table: SQL Target - work.W667P4W9_rule3_1 A5VECZJZ.BK0000JD * * Target Table: Sort Target - work.W667P7T5_rule3_2 A5VECZJZ.BK0000JE * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TS); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(work.W667P4W9_rule3_1); proc datasets lib = work nolist nowarn memtype = (data view); delete W667P7T5_rule3_2; quit; proc sort data = &SYSLAST out = work.W667P7T5_rule3_2 NODUPKEY ; by WORK_ITEM_ID ; run; %rcSet(&syserr); /** Step end Dedupe to keep version of work item with earliest valid from **/ /*==========================================================================* * Step: Get latest version of the quote A5VECZJZ.BJ0000TT * * Transform: Join * * Description: * * * * Source Tables: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * * bcw_dtl.ETL_WORK_ITEM_NUM * * ETL_QUOTE - bcw_dtl.ETL_QUOTE A5VECZJZ.BQ00007A * * Sort Target - work.W667P7T5_rule3_2 A5VECZJZ.BK0000JE * * Target Table: SQL Target - work.W667PHXS_RULE4_1 A5VECZJZ.BK0000JF * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TT); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W667PHXS_RULE4_1; quit; proc sql; create table work.W667PHXS_RULE4_1 as select W667P7T5_rule3_2.WORK_ITEM_REFERENCE_KEY length = 8 format = 12. informat = 12., W667P7T5_rule3_2.CUSTOMER_ID length = 8 format = 12. informat = 12., W667P7T5_rule3_2.WORK_ITEM_ID length = 8 format = 12. informat = 12., W667P7T5_rule3_2.WORK_ITEM_KEY length = 8 format = 12. informat = 12., W667P7T5_rule3_2.INTERFACE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667P7T5_rule3_2.WORK_COMPLETE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667P7T5_rule3_2.WORK_CREATED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667P7T5_rule3_2.PROM_CODE_KEY_SOURCE length = 8 format = 11. informat = 11., W667P7T5_rule3_2.REASON_ID_WORK length = 8 format = 11. informat = 11., W667P7T5_rule3_2.REFERRAL_STAFF_NAME length = 50 format = $50. informat = $50., W667P7T5_rule3_2.REFERRAL_STAFF_NO length = 10 format = $10. informat = $10., W667P7T5_rule3_2.REFERRAL_STAFF_DEPT length = 50 format = $50. informat = $50., W667P7T5_rule3_2.REFERRAL_STAFF_MANAGER length = 50 format = $50. informat = $50., W667P7T5_rule3_2.STATUS_KEY_WORK length = 8 format = 11. informat = 11., W667P7T5_rule3_2.TEAM_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667P7T5_rule3_2.TEAM_MEMBER_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667P7T5_rule3_2.WORK_ITEM_TYPE_KEY length = 8 format = 11. informat = 11., W667P7T5_rule3_2.WORK_ITEM_SUB_TYPE_ID length = 8 format = 11. informat = 11., W667P7T5_rule3_2.WORK_ITEM_TYPE_ID length = 8 format = 11. informat = 11., W667P7T5_rule3_2.TEAM_MEMBER_KEY_OWNER_FRST length = 8 format = 11. informat = 11., W667P7T5_rule3_2.TEAM_KEY_OWNER_FRST length = 8 format = 11. informat = 11., IFN(ETL_WORK_ITEM_NUM.COUNT_OF_ALL_QUOTES IS NULL,0,ETL_WORK_ITEM_NUM.COUNT_OF_ALL_QUOTES ) as CNT_ALL_QUOTES length = 8 format = 12. informat = 12., IFN(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS ) as CNT_CUR_UNITS_LTST length = 8 format = 12. informat = 12., IFN(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS ) as CNT_MAX_UNITS_LTST length = 8 format = 12. informat = 12., IFN(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS ) as CNT_REQ_UNITS_LTST length = 8 format = 11. informat = 11., ETL_WORK_ITEM_NUM.VALID_FROM_DTTM as WORK_VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20., case when ETL_WORK_ITEM_NUM.QUOTE_ID is null and ETL_QUOTE.QUOTE_KEY is null then 0 when ETL_WORK_ITEM_NUM.QUOTE_ID is not null and ETL_QUOTE.QUOTE_KEY is null then -1 else ETL_QUOTE.QUOTE_KEY end as QUOTE_KEY_LTST length = 8 format = 11. informat = 11., ETL_QUOTE.QUOTE_ID length = 8 format = 11. informat = 11., case when ETL_QUOTE.PROM_CODE_KEY_QUOTE is null and ETL_QUOTE.PROM_CODE_ID_QUOTE is null then 0 when ETL_QUOTE.PROM_CODE_KEY_QUOTE is null and ETL_QUOTE.PROM_CODE_ID_QUOTE is not null then -1 else ETL_QUOTE.PROM_CODE_KEY_QUOTE end as PROM_CODE_KEY_QUOTE length = 8 format = 11. informat = 11., ETL_QUOTE.QUOTE_STARTED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., ETL_QUOTE.QUOTE_ACCEPTED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., IFN(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX IS NULL,0,ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX ) as QUOTE_PRICE_INC_TAX_LTST length = 8 format = 21.4 informat = 21.4, ifn(ETL_QUOTE.QUOTE_PRICE_WITH_TAX is null,0,ETL_QUOTE.QUOTE_PRICE_WITH_TAX ) as QUOTE_ORIG_PRICE_LTST length = 8 format = 21.4 informat = 21.4, IFN(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX IS NULL,0, ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX ) as QUOTE_PRICE_EXC_TAX_LTST length = 8 format = 21.4 informat = 21.4, IFN(ETL_QUOTE.DISCOUNT_TOTAL IS NULL,0,ETL_QUOTE.DISCOUNT_TOTAL ) as QUOTE_DISC_TOT_LTST length = 8 format = 21.4 informat = 21.4, ETL_QUOTE.VALID_FROM_DTTM as QUOTE_VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20., ETL_QUOTE.NEXT_RENEWAL_DTTM length = 8 format = DATETIME20. informat = DATETIME20., ETL_QUOTE.NOT_PROCEEDED_WITH_DTTM length = 8 format = DATETIME20. informat = DATETIME20., ETL_QUOTE.QUOTE_EXPIRED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., case when ETL_QUOTE.QUOTE_ACCEPTED_DTTM IS NOT NULL then 3 when ETL_QUOTE.NOT_PROCEEDED_WITH_DTTM IS NULL AND ETL_QUOTE.QUOTE_KEY IS NOT NULL then 2 else 1 end as SORT_SEQ length = 8, IFN(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.BILLING_KEY IS NULL, -1,ETL_QUOTE.BILLING_KEY ),ETL_QUOTE.BILLING_KEY) as BILLING_KEY length = 8 label = 'BILLING_KEY', IFN(ETL_QUOTE.CONTRACT_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.CONTRACT_KEY IS NULL, -1,ETL_QUOTE.CONTRACT_KEY ),ETL_QUOTE.CONTRACT_KEY) as CONTRACT_KEY length = 8 label = 'CONTRACT_KEY' from work.W667P7T5_rule3_2 left join bcw_dtl.ETL_WORK_ITEM_NUM on ( W667P7T5_rule3_2.WORK_ITEM_ID = ETL_WORK_ITEM_NUM.WORK_ITEM_ID ) left join bcw_dtl.ETL_QUOTE on ( ETL_WORK_ITEM_NUM.QUOTE_KEY = ETL_QUOTE.QUOTE_KEY and W667P7T5_rule3_2.WORK_ITEM_ID = ETL_WORK_ITEM_NUM.WORK_ITEM_ID ) ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Get latest version of the quote **/ /*==========================================================================* * Step: Determine which quote record to keep A5VECZJZ.BJ0000TU * * Transform: Sort * * Description: * * * * Source Table: SQL Target - work.W667PHXS_RULE4_1 A5VECZJZ.BK0000JF * * Target Table: Sort Target - work.W667SLQK_RULE4_2 A5VECZJZ.BK0000JG * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TU); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(work.W667PHXS_RULE4_1); proc datasets lib = work nolist nowarn memtype = (data view); delete W667SLQK_RULE4_2; quit; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W1WSE7K; quit; %put %str(NOTE: Mapping columns ...); proc sql; create view work.W1WSE7K as select WORK_ITEM_REFERENCE_KEY, CUSTOMER_ID, WORK_ITEM_ID, WORK_ITEM_KEY, INTERFACE_DTTM, WORK_COMPLETE_DTTM, WORK_CREATED_DTTM, PROM_CODE_KEY_SOURCE, REASON_ID_WORK, REFERRAL_STAFF_NAME, REFERRAL_STAFF_NO, REFERRAL_STAFF_DEPT, REFERRAL_STAFF_MANAGER, STATUS_KEY_WORK, TEAM_KEY_OWNER_LTST, TEAM_MEMBER_KEY_OWNER_LTST, WORK_ITEM_TYPE_KEY, WORK_ITEM_SUB_TYPE_ID, WORK_ITEM_TYPE_ID, TEAM_MEMBER_KEY_OWNER_FRST, TEAM_KEY_OWNER_FRST, CNT_ALL_QUOTES, CNT_CUR_UNITS_LTST, CNT_MAX_UNITS_LTST, CNT_REQ_UNITS_LTST, WORK_VALID_FROM_DTTM, QUOTE_KEY_LTST, QUOTE_ID, PROM_CODE_KEY_QUOTE, QUOTE_STARTED_DTTM, QUOTE_ACCEPTED_DTTM, QUOTE_PRICE_INC_TAX_LTST, QUOTE_ORIG_PRICE_LTST, QUOTE_PRICE_EXC_TAX_LTST, QUOTE_DISC_TOT_LTST, QUOTE_VALID_FROM_DTTM, NEXT_RENEWAL_DTTM, NOT_PROCEEDED_WITH_DTTM, QUOTE_EXPIRED_DTTM, SORT_SEQ from &SYSLAST ; quit; %let SYSLAST = work.W1WSE7K; proc sort data = &SYSLAST out = work.W667SLQK_RULE4_2; by WORK_ITEM_ID descending SORT_SEQ descending QUOTE_STARTED_DTTM descending QUOTE_VALID_FROM_DTTM descending WORK_VALID_FROM_DTTM ; run; %rcSet(&syserr); proc datasets lib = work nolist nowarn memtype = (data view); delete W1WSE7K; quit; /** Step end Determine which quote record to keep **/ /*==========================================================================* * Step: Dedupe keeping only required latest A5VECZJZ.BJ0000TV * * quote record * * Transform: Sort * * Description: * * * * Source Table: Sort Target - work.W667SLQK_RULE4_2 A5VECZJZ.BK0000JG * * Target Table: Sort Target - work.W667SN06_RULE4_3 A5VECZJZ.BK0000JH * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TV); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(work.W667SLQK_RULE4_2); proc datasets lib = work nolist nowarn memtype = (data view); delete W667SN06_RULE4_3; quit; proc sort data = &SYSLAST out = work.W667SN06_RULE4_3 NODUPKEY ; by WORK_ITEM_ID ; run; %rcSet(&syserr); /** Step end Dedupe keeping only required latest quote record **/ /*==========================================================================* * Step: Get earliest version of quote A5VECZJZ.BJ0000TW * * Transform: Join * * Description: * * * * Source Tables: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * * bcw_dtl.ETL_WORK_ITEM_NUM * * ETL_QUOTE - bcw_dtl.ETL_QUOTE A5VECZJZ.BQ00007A * * Sort Target - work.W667SN06_RULE4_3 A5VECZJZ.BK0000JH * * Target Table: SQL Target - work.W667TARG_RULE5_1 A5VECZJZ.BK0000JI * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TW); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W667TARG_RULE5_1; quit; data _null_; put "NOTE: The following column(s) do not have a column mapping, so the" " value(s) will be set to missing: QUOTE_TYPE_KEY, STATUS_KEY_QUOTE," " REASON_ID_QUOTE"; run; proc sql _tree _method; create table work.W667TARG_RULE5_1 as select W667SN06_RULE4_3.WORK_ITEM_REFERENCE_KEY length = 8 format = 12. informat = 12., W667SN06_RULE4_3.CUSTOMER_ID length = 8 format = 12. informat = 12., W667SN06_RULE4_3.WORK_ITEM_ID length = 8 format = 12. informat = 12., W667SN06_RULE4_3.WORK_ITEM_KEY length = 8 format = 12. informat = 12., W667SN06_RULE4_3.INTERFACE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.WORK_COMPLETE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.WORK_CREATED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.PROM_CODE_KEY_SOURCE length = 8 format = 11. informat = 11., W667SN06_RULE4_3.REASON_ID_WORK length = 8 format = 11. informat = 11., W667SN06_RULE4_3.REFERRAL_STAFF_NAME length = 50 format = $50. informat = $50., W667SN06_RULE4_3.REFERRAL_STAFF_NO length = 10 format = $10. informat = $10., W667SN06_RULE4_3.REFERRAL_STAFF_DEPT length = 50 format = $50. informat = $50., W667SN06_RULE4_3.REFERRAL_STAFF_MANAGER length = 50 format = $50. informat = $50., W667SN06_RULE4_3.STATUS_KEY_WORK length = 8 format = 11. informat = 11., W667SN06_RULE4_3.TEAM_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667SN06_RULE4_3.TEAM_MEMBER_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667SN06_RULE4_3.WORK_ITEM_TYPE_KEY length = 8 format = 11. informat = 11., W667SN06_RULE4_3.WORK_ITEM_SUB_TYPE_ID length = 8 format = 11. informat = 11., W667SN06_RULE4_3.WORK_ITEM_TYPE_ID length = 8 format = 11. informat = 11., W667SN06_RULE4_3.TEAM_MEMBER_KEY_OWNER_FRST length = 8 format = 11. informat = 11., W667SN06_RULE4_3.TEAM_KEY_OWNER_FRST length = 8 format = 11. informat = 11., W667SN06_RULE4_3.CNT_ALL_QUOTES length = 8 format = 12. informat = 12., W667SN06_RULE4_3.CNT_CUR_UNITS_LTST length = 8 format = 12. informat = 12., W667SN06_RULE4_3.CNT_MAX_UNITS_LTST length = 8 format = 12. informat = 12., W667SN06_RULE4_3.CNT_REQ_UNITS_LTST length = 8 format = 11. informat = 11., W667SN06_RULE4_3.QUOTE_KEY_LTST length = 8 format = 11. informat = 11., W667SN06_RULE4_3.QUOTE_ID length = 8 format = 11. informat = 11., . as QUOTE_TYPE_KEY length = 8 format = 11. informat = 11., W667SN06_RULE4_3.PROM_CODE_KEY_QUOTE length = 8 format = 11. informat = 11., W667SN06_RULE4_3.QUOTE_STARTED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.QUOTE_ACCEPTED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.QUOTE_PRICE_INC_TAX_LTST length = 8 format = 21.4 informat = 21.4, W667SN06_RULE4_3.QUOTE_ORIG_PRICE_LTST length = 8 format = 21.4 informat = 21.4, W667SN06_RULE4_3.QUOTE_PRICE_EXC_TAX_LTST length = 8 format = 21.4 informat = 21.4, W667SN06_RULE4_3.QUOTE_DISC_TOT_LTST length = 8 format = 21.4 informat = 21.4, . as STATUS_KEY_QUOTE length = 8 format = 11. informat = 11., W667SN06_RULE4_3.NEXT_RENEWAL_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.NOT_PROCEEDED_WITH_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667SN06_RULE4_3.QUOTE_EXPIRED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., . as REASON_ID_QUOTE length = 8, IFN(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS is null,0,ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS ) as CNT_CUR_UNITS_FRST length = 8 format = 12. informat = 12., IFN(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS is null,0,ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS ) as CNT_MAX_UNITS_FRST length = 8 format = 12. informat = 12., IFN(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS ) as CNT_REQ_UNITS_FRST length = 8 format = 11. informat = 11., ETL_WORK_ITEM_NUM.VALID_FROM_DTTM as WORK_VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20., case when ETL_WORK_ITEM_NUM.QUOTE_ID is null and ETL_QUOTE.QUOTE_KEY is null then 0 when ETL_WORK_ITEM_NUM.QUOTE_ID is not null and ETL_QUOTE.QUOTE_KEY is null then -1 else ETL_QUOTE.QUOTE_KEY end as QUOTE_KEY_FRST length = 8 format = 11. informat = 11., IFN(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX is null,0,ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX ) as QUOTE_PRICE_INC_TAX_FRST length = 8 format = 21.4 informat = 21.4, IFN(ETL_QUOTE.QUOTE_PRICE_WITH_TAX is null,0, ETL_QUOTE.QUOTE_PRICE_WITH_TAX ) as QUOTE_ORIG_PRICE_FRST length = 8 format = 21.4 informat = 21.4, IFN(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX is null,0, ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX ) as QUOTE_PRICE_EXC_TAX_FRST length = 8 format = 21.4 informat = 21.4, IFN(ETL_QUOTE.DISCOUNT_TOTAL is null,0,ETL_QUOTE.DISCOUNT_TOTAL ) as QUOTE_DISC_TOT_FRST length = 8 format = 21.4 informat = 21.4, IFN(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.BILLING_KEY IS NULL, -1,ETL_QUOTE.BILLING_KEY ),ETL_QUOTE.BILLING_KEY) as BILLING_KEY length = 8 label = 'BILLING_KEY', IFN(ETL_QUOTE.CONTRACT_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.CONTRACT_KEY IS NULL, -1,ETL_QUOTE.CONTRACT_KEY ),ETL_QUOTE.CONTRACT_KEY) as CONTRACT_KEY length = 8 label = 'CONTRACT_KEY', ETL_QUOTE.VALID_FROM_DTTM as QUOTE_VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20. from work.W667SN06_RULE4_3 left join bcw_dtl.ETL_WORK_ITEM_NUM on ( W667SN06_RULE4_3.WORK_ITEM_ID = ETL_WORK_ITEM_NUM.WORK_ITEM_ID ) left join bcw_dtl.ETL_QUOTE on ( ETL_WORK_ITEM_NUM.QUOTE_KEY = ETL_QUOTE.QUOTE_KEY ) order by W667SN06_RULE4_3.WORK_ITEM_ID, W667SN06_RULE4_3.QUOTE_STARTED_DTTM, W667SN06_RULE4_3.QUOTE_ID, ETL_QUOTE.VALID_FROM_DTTM, ETL_WORK_ITEM_NUM.VALID_FROM_DTTM ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Get earliest version of quote **/ /*==========================================================================* * Step: Dedupe keeping only required earliest A5VECZJZ.BJ0000TX * * quote record * * Transform: Sort * * Description: * * * * Source Table: SQL Target - work.W667TARG_RULE5_1 A5VECZJZ.BK0000JI * * Target Table: Sort Target - work.W667TSCQ_RULE5_2 A5VECZJZ.BK0000JJ * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TX); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(work.W667TARG_RULE5_1); proc datasets lib = work nolist nowarn memtype = (data view); delete W667TSCQ_RULE5_2; quit; proc sort data = &SYSLAST out = work.W667TSCQ_RULE5_2 NODUPKEY ; by WORK_ITEM_ID ; run; %rcSet(&syserr); /** Step end Dedupe keeping only required earliest quote record **/ /*==========================================================================* * Step: Lookup dimension keys A5VECZJZ.BJ0000TY * * Transform: Join * * Description: * * * * Source Tables: DEF_REASON - bcw_dtl.DEF_REASON A5VECZJZ.BQ00008J * * RIM_DATE - bcw_mart.RIM_DATE A5VECZJZ.BQ00009B * * REF_WORK_ITEM_SUB_TYPE - A5VECZJZ.BQ00008W * * bcw_dtl.REF_WORK_ITEM_SUB_TYPE * * REF_CUSTOMER - bcw_dtl.REF_CUSTOMER A5VECZJZ.BQ00007X * * Sort Target - work.W667TSCQ_RULE5_2 A5VECZJZ.BK0000JJ * * Target Table: SQL Target - work.W66880IZ_rule6_4 A5VECZJZ.BK0000JK * * * * Warnings: * * Mapped source columns do not match columns used in expression. * * Mapped source columns do not match columns used in expression. * * Mapped source columns do not match columns used in expression. * * Mapped source columns do not match columns used in expression. * * Mapped source columns do not match columns used in expression. * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TY); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; proc datasets lib = work nolist nowarn memtype = (data view); delete W66880IZ_rule6_4; quit; data _null_; put "NOTE: Mapped source columns do not match columns used in expression."; run; proc sql; create table work.W66880IZ_rule6_4 as select W667TSCQ_RULE5_2.CNT_ALL_QUOTES length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.CNT_CUR_UNITS_FRST length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.CNT_CUR_UNITS_LTST length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.CNT_MAX_UNITS_FRST length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.CNT_MAX_UNITS_LTST length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.CNT_REQ_UNITS_FRST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.CNT_REQ_UNITS_LTST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.CUSTOMER_ID length = 8 format = 12. informat = 12., case when W667TSCQ_RULE5_2.CUSTOMER_ID IS NULL then 0 when W667TSCQ_RULE5_2.CUSTOMER_ID IS NOT NULL AND REF_CUSTOMER.CUSTOMER_KEY IS NULL then -1 else REF_CUSTOMER.CUSTOMER_KEY end as CUSTOMER_KEY length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM IS NULL then 0 when W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM is not null and RIM_DATE.DATE_KEY is null then -1 else RIM_DATE.DATE_KEY end as DATE_KEY_QUOTE_ACCEPTED length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM IS NULL then 0 when W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM is not null and RIM_DATE.DATE_KEY is null then -1 else RIM_DATE.DATE_KEY end as DATE_KEY_QUOTE_EXPIRED length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM IS NULL then 0 when W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM is not null and RIM_DATE.DATE_KEY is null then -1 else RIM_DATE.DATE_KEY end as DATE_KEY_QUOTE_NOT_PROCEED length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM IS NULL then 0 when W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM is not null and RIM_DATE.DATE_KEY is null then -1 else RIM_DATE.DATE_KEY end as DATE_KEY_QUOTE_STARTED length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM IS NULL then 0 when W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM is not null and RIM_DATE.DATE_KEY is null then -1 else RIM_DATE.DATE_KEY end as DATE_KEY_WORK_COMPLETE length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.WORK_CREATED_DTTM IS NULL then 0 when W667TSCQ_RULE5_2.WORK_CREATED_DTTM is not null and RIM_DATE.DATE_KEY is null then -1 else RIM_DATE.DATE_KEY end as DATE_KEY_WORK_CREATED length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.INTERFACE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.NEXT_RENEWAL_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.PROM_CODE_KEY_QUOTE length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.PROM_CODE_KEY_SOURCE length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.QUOTE_DISC_TOT_FRST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_DISC_TOT_LTST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.BILLING_KEY length = 8 label = 'BILLING_KEY', W667TSCQ_RULE5_2.CONTRACT_KEY length = 8 label = 'CONTRACT_KEY', W667TSCQ_RULE5_2.QUOTE_ID length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.QUOTE_KEY_FRST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.QUOTE_KEY_LTST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.QUOTE_ORIG_PRICE_FRST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_ORIG_PRICE_LTST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_PRICE_EXC_TAX_FRST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_PRICE_EXC_TAX_LTST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_PRICE_INC_TAX_FRST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_PRICE_INC_TAX_LTST length = 8 format = 21.4 informat = 21.4, W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.QUOTE_VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.REASON_ID_QUOTE length = 8, W667TSCQ_RULE5_2.REASON_ID_WORK length = 8 format = 11. informat = 11., case when W667TSCQ_RULE5_2.REASON_ID_WORK IS NULL then 0 when W667TSCQ_RULE5_2.REASON_ID_WORK IS NOT NULL AND DEF_REASON_WORK.REASON_KEY is null then -1 else DEF_REASON_WORK.REASON_KEY end as REASON_KEY_WORK length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.REFERRAL_STAFF_DEPT length = 50 format = $50. informat = $50., W667TSCQ_RULE5_2.REFERRAL_STAFF_MANAGER length = 50 format = $50. informat = $50., W667TSCQ_RULE5_2.REFERRAL_STAFF_NAME length = 50 format = $50. informat = $50., W667TSCQ_RULE5_2.REFERRAL_STAFF_NO length = 10 format = $10. informat = $10., W667TSCQ_RULE5_2.STATUS_KEY_WORK length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.TEAM_KEY_OWNER_FRST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.TEAM_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.TEAM_MEMBER_KEY_OWNER_FRST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.TEAM_MEMBER_KEY_OWNER_LTST length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.WORK_CREATED_DTTM length = 8 format = DATETIME20. informat = DATETIME20., W667TSCQ_RULE5_2.WORK_ITEM_ID length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.WORK_ITEM_KEY length = 8 format = 12. informat = 12., W667TSCQ_RULE5_2.WORK_ITEM_REFERENCE_KEY length = 8 format = 12. informat = 12., case when W667TSCQ_RULE5_2.WORK_ITEM_SUB_TYPE_ID IS NULL then 0 when W667TSCQ_RULE5_2.WORK_ITEM_SUB_TYPE_ID IS NOT NULL AND REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_SUB_TYPE_KEY IS NULL then -1 else REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_SUB_TYPE_KEY end as WORK_ITEM_SUB_TYPE_KEY length = 8, W667TSCQ_RULE5_2.WORK_ITEM_TYPE_KEY length = 8 format = 11. informat = 11., W667TSCQ_RULE5_2.WORK_VALID_FROM_DTTM length = 8 format = DATETIME20. informat = DATETIME20. from work.W667TSCQ_RULE5_2 left join bcw_dtl.DEF_REASON as DEF_REASON_WORK on ( W667TSCQ_RULE5_2.REASON_ID_WORK = DEF_REASON_WORK.REASON_ID and (W667TSCQ_RULE5_2.INTERFACE_DTTM BETWEEN DEF_REASON_WORK.VALID_FROM_DTTM and DEF_REASON_WORK.VALID_TO_DTTM ) ) left join bcw_mart.RIM_DATE on ( dhms(datepart(W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM ),0,0,0) = RIM_DATE.THE_DATE and W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM NOT IS NULL ) left join bcw_mart.RIM_DATE as RIM_DATE_EXPIRED on ( dhms(datepart(W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM ),0,0,0) = RIM_DATE_EXPIRED.THE_DATE and W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM NOT IS NULL ) left join bcw_mart.RIM_DATE as RIM_DATE_ACCEPTED on ( dhms(datepart(W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM) ,0,0,0) = RIM_DATE_ACCEPTED.THE_DATE and W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM NOT IS NULL ) left join bcw_mart.RIM_DATE as RIM_DATE_NOT_PROCEED on ( dhms(datepart(W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM ),0,0,0) = RIM_DATE_NOT_PROCEED.THE_DATE and W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM NOT IS NULL ) left join bcw_mart.RIM_DATE as RIM_DATE_WORK_CREATED on ( dhms(datepart(W667TSCQ_RULE5_2.WORK_CREATED_DTTM ),0,0,0) = RIM_DATE_WORK_CREATED.THE_DATE and W667TSCQ_RULE5_2.WORK_CREATED_DTTM NOT IS NULL ) left join bcw_mart.RIM_DATE as RIM_DATE_WORK_COMPLETED on ( dhms(datepart(W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM ),0,0,0) = RIM_DATE_WORK_COMPLETED.THE_DATE and W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM NOT IS NULL ) left join bcw_dtl.REF_WORK_ITEM_SUB_TYPE on ( W667TSCQ_RULE5_2.WORK_ITEM_SUB_TYPE_ID = REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_SUB_TYPE_ID and W667TSCQ_RULE5_2.WORK_ITEM_TYPE_ID = REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_TYPE_ID and (W667TSCQ_RULE5_2.INTERFACE_DTTM BETWEEN REF_WORK_ITEM_SUB_TYPE.VALID_FROM_DTTM and REF_WORK_ITEM_SUB_TYPE.VALID_TO_DTTM ) ) left join bcw_dtl.REF_CUSTOMER on ( W667TSCQ_RULE5_2.CUSTOMER_ID = REF_CUSTOMER.CUSTOMER_ID and (W667TSCQ_RULE5_2.INTERFACE_DTTM BETWEEN REF_CUSTOMER.VALID_FROM_DTTM and REF_CUSTOMER.VALID_TO_DTTM ) ) order by WORK_ITEM_ID ; quit; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Lookup dimension keys **/ /*==========================================================================* * Step: Derive Measures A5VECZJZ.BJ0000TZ * * Transform: Extract * * Description: * * * * Source Table: SQL Target - work.W66880IZ_rule6_4 A5VECZJZ.BK0000JK * * Target Table: Extract Target - work.W668AOWI A5VECZJZ.BK0000JL * *==========================================================================*/ %let transformID = %quote(A5VECZJZ.BJ0000TZ); %let trans_rc = 0; %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); %let SYSLAST = %nrquote(work.W66880IZ_rule6_4); %global etls_sql_pushDown; %let etls_sql_pushDown = -1; option DBIDIRECTEXEC; /*---- Map the columns ----*/ proc datasets lib = work nolist nowarn memtype = (data view); delete W668AOWI; quit; %put %str(NOTE: Mapping columns ...); proc sql; create table work.W668AOWI as select CNT_ALL_QUOTES, CNT_CUR_UNITS_FRST, CNT_CUR_UNITS_LTST, CNT_MAX_UNITS_FRST, CNT_MAX_UNITS_LTST, CNT_REQ_UNITS_FRST, CNT_REQ_UNITS_LTST, (1) as CNT_WORK length = 8, CUSTOMER_KEY, DATE_KEY_QUOTE_ACCEPTED, DATE_KEY_QUOTE_EXPIRED, DATE_KEY_QUOTE_NOT_PROCEED, DATE_KEY_QUOTE_STARTED, DATE_KEY_WORK_COMPLETE, DATE_KEY_WORK_CREATED, (CNT_CUR_UNITS_LTST - CNT_CUR_UNITS_FRST) as DIFF_QUOTE_CUR_UNITS length = 8, (QUOTE_DISC_TOT_LTST - QUOTE_DISC_TOT_FRST) as DIFF_QUOTE_DISC_TOT length = 8, (CNT_MAX_UNITS_LTST - CNT_MAX_UNITS_FRST) as DIFF_QUOTE_MAX_UNITS length = 8, (QUOTE_ORIG_PRICE_LTST - QUOTE_ORIG_PRICE_FRST) as DIFF_QUOTE_ORIG_PRICE length = 8, (QUOTE_PRICE_EXC_TAX_LTST - QUOTE_PRICE_EXC_TAX_FRST) as DIFF_QUOTE_PRICE_EXC_TAX length = 8, (QUOTE_PRICE_INC_TAX_LTST - QUOTE_PRICE_INC_TAX_FRST) as DIFF_QUOTE_PRICE_INC_TAX length = 8, (CNT_REQ_UNITS_LTST - CNT_REQ_UNITS_FRST) as DIFF_QUOTE_REQ_UNITS length = 8, PROM_CODE_KEY_QUOTE, PROM_CODE_KEY_SOURCE, (CASE when QUOTE_ACCEPTED_DTTM is not null then (datepart(QUOTE_ACCEPTED_DTTM) -datepart(QUOTE_STARTED_DTTM)) when NOT_PROCEEDED_WITH_DTTM is not null then (datepart(NOT_PROCEEDED_WITH_DTTM) - datepart(QUOTE_STARTED_DTTM) ) when QUOTE_EXPIRED_DTTM is not null then (datepart(QUOTE_EXPIRED_DTTM) -datepart(QUOTE_STARTED_DTTM )) else 0 END) as QUOTE_INIT_TO_QUOTE_COMP_DAYS length = 8, QUOTE_KEY_FRST, QUOTE_KEY_LTST, REASON_KEY_WORK, REFERRAL_STAFF_DEPT, REFERRAL_STAFF_MANAGER, REFERRAL_STAFF_NAME, REFERRAL_STAFF_NO, STATUS_KEY_WORK, TEAM_KEY_OWNER_FRST, TEAM_KEY_OWNER_LTST, TEAM_MEMBER_KEY_OWNER_FRST, TEAM_MEMBER_KEY_OWNER_LTST, (ifn(WORK_COMPLETE_DTTM is not null,(datepart(WORK_COMPLETE_DTTM) -datepart(WORK_CREATED_DTTM) ),0)) as WORK_CREATE_TO_COMP_DAYS length = 8, WORK_ITEM_ID, WORK_ITEM_TYPE_KEY, INTERFACE_DTTM, (DATETIME()) as PROCESS_DTTM length = 8 format = DATETIME20. informat = DATETIME20., WORK_ITEM_REFERENCE_KEY, CUSTOMER_ID, WORK_ITEM_KEY, WORK_COMPLETE_DTTM, WORK_CREATED_DTTM, REASON_ID_WORK, QUOTE_ID, QUOTE_STARTED_DTTM, QUOTE_ACCEPTED_DTTM, QUOTE_PRICE_INC_TAX_LTST, QUOTE_ORIG_PRICE_LTST, QUOTE_PRICE_EXC_TAX_LTST, QUOTE_DISC_TOT_LTST, NEXT_RENEWAL_DTTM, BILLING_KEY, CONTRACT_KEY, NOT_PROCEEDED_WITH_DTTM, QUOTE_EXPIRED_DTTM, REASON_ID_QUOTE, WORK_VALID_FROM_DTTM, QUOTE_PRICE_INC_TAX_FRST, QUOTE_ORIG_PRICE_FRST, QUOTE_PRICE_EXC_TAX_FRST, QUOTE_DISC_TOT_FRST, QUOTE_VALID_FROM_DTTM, WORK_ITEM_SUB_TYPE_KEY from &SYSLAST ; quit; %let SYSLAST = work.W668AOWI; %global etls_sql_pushDown; %let etls_sql_pushDown = &sys_sql_ip_all; %rcSet(&sqlrc); /** Step end Derive Meas