1 The SAS System 07:32 Wednesday, March 30, 2016 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTNAME=''; 7 %LET _SASPROGRAMFILE=; 8 9 ODS _ALL_ CLOSE; 10 OPTIONS DEV=ACTIVEX; 11 GOPTIONS XPIXELS=0 YPIXELS=0; 12 ODS LISTING GPATH=&sasworklocation; SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "/sas_work/Batch/SAS_workBEC400003960_ph0236p.theaa.local/SAS_work13BB00003960_ph0236p.theaa.local/" 13 14 GOPTIONS ACCESSIBLE; 15 /**************************************************************************** 16 * Job: FACT_WORK A5VECZJZ.BE0000BF * 17 * Description: * 18 * * 19 * Metadata Server: ph0236p * 20 * Port: 8561 * 21 * Location: /BCW/4.0 Jobs/4.4 Mart Load * 22 * * 23 * Server: SASApp A5VECZJZ.AS000002 * 24 * * 25 * Source Tables: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * 26 * bcw_dtl.ETL_WORK_ITEM_NUM * 27 * ETL_WORK_ITEM - bcw_dtl.ETL_WORK_ITEM A5VECZJZ.BQ00007K * 28 * ETL_WORK_ITEM - bcw_dtl.ETL_WORK_ITEM A5VECZJZ.BQ00007K * 29 * ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * 30 * bcw_dtl.ETL_WORK_ITEM_NUM * 31 * ETL_QUOTE - bcw_dtl.ETL_QUOTE A5VECZJZ.BQ00007A * 32 * FACT_WORK (New Helper) - A5VECZJZ.BQ0000AE * 33 * bcw_mart.FACT_WORK * 34 * ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * 35 * bcw_dtl.ETL_WORK_ITEM_NUM * 36 * ETL_QUOTE - bcw_dtl.ETL_QUOTE A5VECZJZ.BQ00007A * 37 * DEF_REASON - bcw_dtl.DEF_REASON A5VECZJZ.BQ00008J * 38 * RIM_DATE - bcw_mart.RIM_DATE A5VECZJZ.BQ00009B * 39 * REF_WORK_ITEM_SUB_TYPE - A5VECZJZ.BQ00008W * 40 * bcw_dtl.REF_WORK_ITEM_SUB_TYPE * 41 * REF_CUSTOMER - bcw_dtl.REF_CUSTOMER A5VECZJZ.BQ00007X * 42 * Target Tables: FACT_WORK - bcw_mart.FACT_WORK A5VECZJZ.BQ0000AD * 43 * FACT_WORK (New Update Helper) - A5VECZJZ.BQ0000AF * 44 * bcw_mart.FACT_WORK * 45 * * 46 * Generated on: Tuesday, 29 March 2016 13:59:40 o'clock BST * 47 * Generated by: bcwetl * SYMBOLGEN: Macro variable SYSJOBID resolves to 14688 48 * Version: SAS Data Integration Studio 4.901 * 49 ****************************************************************************/ 50 51 /* Generate the process id for job */ 52 %put Process ID: &SYSJOBID; Process ID: 14688 53 54 /* General macro variables */ 2 The SAS System 07:32 Wednesday, March 30, 2016 55 %let jobID = %quote(A5VECZJZ.BE0000BF); 56 %let etls_jobName = %nrquote(FACT_WORK); 57 %let etls_userID = %nrquote(bcwetl); 58 59 /* Setup to capture return codes */ 60 %global job_rc trans_rc sqlrc; 61 %let sysrc = 0; 62 %let job_rc = 0; 63 %let trans_rc = 0; 64 %let sqlrc = 0; 65 %global etls_stepStartTime; 66 /* initialize syserr to 0 */ 67 data _null_; run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 68 69 %macro rcSet(error); 70 %if (&error gt &trans_rc) %then 71 %let trans_rc = &error; 72 %if (&error gt &job_rc) %then 73 %let job_rc = &error; 74 %mend rcSet; 75 76 %macro rcSetDS(error); 77 if &error gt input(symget('trans_rc'),12.) then 78 call symput('trans_rc',trim(left(put(&error,12.)))); 79 if &error gt input(symget('job_rc'),12.) then 80 call symput('job_rc',trim(left(put(&error,12.)))); 81 %mend rcSetDS; 82 83 /* Create metadata macro variables */ 84 %let IOMServer = %nrquote(SASApp); 85 %let metaPort = %nrquote(8561); 86 %let metaServer = %nrquote(ph0236p.theaa.local); 87 88 /* Set metadata options */ 89 options metaport = &metaPort SYMBOLGEN: Macro variable METAPORT resolves to 8561 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. 90 metaserver = "&metaServer"; SYMBOLGEN: Macro variable METASERVER resolves to ph0236p.theaa.local SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. 91 92 /* Setup for capturing job status */ 93 %let etls_startTime = %sysfunc(datetime(),datetime.); 94 %let etls_recordsBefore = 0; 95 %let etls_recordsAfter = 0; 96 %let etls_lib = 0; 97 %let etls_table = 0; 98 99 %global etls_debug; 100 %macro etls_setDebug; 101 %if %str(&etls_debug) ne 0 %then 102 OPTIONS MPRINT%str(;); 3 The SAS System 07:32 Wednesday, March 30, 2016 103 %mend; 104 %etls_setDebug; MLOGIC(ETLS_SETDEBUG): Beginning execution. SYMBOLGEN: Macro variable ETLS_DEBUG resolves to MLOGIC(ETLS_SETDEBUG): %IF condition &etls_debug ne 0 is TRUE MPRINT(ETLS_SETDEBUG): OPTIONS MPRINT; MLOGIC(ETLS_SETDEBUG): Ending execution. 105 106 /*---- Start of Pre-Process Code ----*/ 107 108 %get_avail_dates(load_type=mart_control, load_name=FACT_WORK); MLOGIC(GET_AVAIL_DATES): Beginning execution. MLOGIC(GET_AVAIL_DATES): This macro was compiled from the autocall file /sas/sas94/config/Lev1/SASApp//SASEnvironment//SASMacro/get_avail_dates.sas MLOGIC(GET_AVAIL_DATES): Parameter LOAD_TYPE has value mart_control MLOGIC(GET_AVAIL_DATES): Parameter LOAD_NAME has value FACT_WORK MLOGIC(GET_AVAIL_DATES): %GLOBAL GET_MIN_DATE MLOGIC(GET_AVAIL_DATES): %GLOBAL GET_MAX_DATE MLOGIC(GET_AVAIL_DATES): %GLOBAL MIN_LOAD_DTTM MLOGIC(GET_AVAIL_DATES): %GLOBAL MAX_LOAD_DTTM MLOGIC(GET_AVAIL_DATES): %GLOBAL ROWS_LOADED MLOGIC(GET_LOAD_DATES): Beginning execution. MLOGIC(GET_LOAD_DATES): This macro was compiled from the autocall file /sas/sas94/config/Lev1/SASApp//SASEnvironment//SASMacro/get_load_dates.sas SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK MLOGIC(GET_LOAD_DATES): Parameter LOAD_TYPE has value mart_control MLOGIC(GET_LOAD_DATES): Parameter LOAD_NAME has value FACT_WORK MLOGIC(GET_LOAD_DATES): %GLOBAL LIBRARY MLOGIC(GET_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:load_type=&load_type SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_LOAD_DATES SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control NOTE:(GET_LOAD_DATES):PARAMETER:load_type=mart_control MLOGIC(GET_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:load_name=&load_name SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_LOAD_DATES SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK NOTE:(GET_LOAD_DATES):PARAMETER:load_name=FACT_WORK MLOGIC(GET_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_LOAD_DATES): %LET (variable name is LIBRARY) MPRINT(GET_LOAD_DATES): proc sql noprint; SYMBOLGEN: Macro variable LIBRARY resolves to control SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK MPRINT(GET_LOAD_DATES): select count(1) into :running_status from control.mart_control where load_name="FACT_WORK" and status=1; MPRINT(GET_LOAD_DATES): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds SYMBOLGEN: Macro variable RUNNING_STATUS resolves to 0 MLOGIC(GET_LOAD_DATES): %IF condition &running_status eq 0 is TRUE MPRINT(GET_LOAD_DATES): proc sql noprint; 4 The SAS System 07:32 Wednesday, March 30, 2016 SYMBOLGEN: Macro variable LIBRARY resolves to control SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK MPRINT(GET_LOAD_DATES): select count(1) into :ready_status from control.mart_control where load_name="FACT_WORK" and status=0; MPRINT(GET_LOAD_DATES): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds SYMBOLGEN: Macro variable READY_STATUS resolves to 1 MLOGIC(GET_LOAD_DATES): %IF condition &ready_status ge 1 is TRUE MPRINT(GET_LOAD_DATES): proc sql noprint OUTOBS=1; SYMBOLGEN: Macro variable LIBRARY resolves to control SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK MPRINT(GET_LOAD_DATES): select datepart(load_from_dt) format=8., datepart(load_to_dt) format=8. into :min_dt, :max_dt from control.mart_control where load_name="FACT_WORK" and status=0 order by load_from_dt; NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause. MPRINT(GET_LOAD_DATES): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.02 seconds MLOGIC(GET_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):OUTPUT:min_dt=&min_dt i.e. %sysfunc(putn(&min_dt, date9.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_LOAD_DATES SYMBOLGEN: Macro variable MIN_DT resolves to 20541 SYMBOLGEN: Macro variable MIN_DT resolves to 20541 NOTE:(GET_LOAD_DATES):OUTPUT:min_dt= 20541 i.e. 28MAR2016 MLOGIC(GET_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):OUTPUT:max_dt=&max_dt i.e. %sysfunc(putn(&max_dt, date9.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_LOAD_DATES SYMBOLGEN: Macro variable MAX_DT resolves to 20541 SYMBOLGEN: Macro variable MAX_DT resolves to 20541 NOTE:(GET_LOAD_DATES):OUTPUT:max_dt= 20541 i.e. 28MAR2016 MLOGIC(GET_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_LOAD_DATES): %LET (variable name is GET_MIN_DATE) SYMBOLGEN: Macro variable MIN_DT resolves to 20541 MLOGIC(GET_LOAD_DATES): %LET (variable name is GET_MAX_DATE) SYMBOLGEN: Macro variable MAX_DT resolves to 20541 MLOGIC(GET_LOAD_DATES): Ending execution. MPRINT(GET_AVAIL_DATES): ; SYMBOLGEN: Macro variable GET_MIN_DATE resolves to 20541 SYMBOLGEN: Macro variable GET_MAX_DATE resolves to 20541 MLOGIC(GET_AVAIL_DATES): %IF condition (&get_min_date eq . OR &get_max_date eq .) is FALSE MLOGIC(GET_AVAIL_DATES): %LET (variable name is ROWS_LOADED) MLOGIC(GET_AVAIL_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_AVAIL_DATES): %PUT NOTE:(&SYSMACRONAME):DEBUG:get_min_date=&get_min_date i.e. %sysfunc(putn(&get_min_date, date9.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_AVAIL_DATES SYMBOLGEN: Macro variable GET_MIN_DATE resolves to 20541 SYMBOLGEN: Macro variable GET_MIN_DATE resolves to 20541 5 The SAS System 07:32 Wednesday, March 30, 2016 NOTE:(GET_AVAIL_DATES):DEBUG:get_min_date=20541 i.e. 28MAR2016 MLOGIC(GET_AVAIL_DATES): %PUT NOTE:(&SYSMACRONAME):DEBUG:get_max_date=&get_max_date i.e. %sysfunc(putn(&get_max_date, date9.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_AVAIL_DATES SYMBOLGEN: Macro variable GET_MAX_DATE resolves to 20541 SYMBOLGEN: Macro variable GET_MAX_DATE resolves to 20541 NOTE:(GET_AVAIL_DATES):DEBUG:get_max_date=20541 i.e. 28MAR2016 MLOGIC(GET_AVAIL_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(UPD_LOAD_DATES): Beginning execution. MLOGIC(UPD_LOAD_DATES): This macro was compiled from the autocall file /sas/sas94/config/Lev1/SASApp//SASEnvironment//SASMacro/upd_load_dates.sas SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK SYMBOLGEN: Macro variable GET_MIN_DATE resolves to 20541 SYMBOLGEN: Macro variable GET_MAX_DATE resolves to 20541 MLOGIC(UPD_LOAD_DATES): Parameter LOAD_TYPE has value mart_control MLOGIC(UPD_LOAD_DATES): Parameter LOAD_NAME has value FACT_WORK MLOGIC(UPD_LOAD_DATES): Parameter LOAD_FROM_DT has value 20541 MLOGIC(UPD_LOAD_DATES): Parameter LOAD_TO_DT has value 20541 MLOGIC(UPD_LOAD_DATES): Parameter OLD_STATUS has value 0 MLOGIC(UPD_LOAD_DATES): Parameter NEW_STATUS has value 1 MLOGIC(UPD_LOAD_DATES): Parameter RUN_START_DTTM has value . MLOGIC(UPD_LOAD_DATES): Parameter RUN_END_DTTM has value . MLOGIC(UPD_LOAD_DATES): Parameter ROWS_LOADED has value null MLOGIC(UPD_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:load_type=&load_type SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control NOTE:(UPD_LOAD_DATES):PARAMETER:load_type=mart_control MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:load_name=&load_name SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK NOTE:(UPD_LOAD_DATES):PARAMETER:load_name=FACT_WORK MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:load_from_dt=&load_from_dt i.e. %sysfunc(putn(&load_from_dt, date9.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 NOTE:(UPD_LOAD_DATES):PARAMETER:load_from_dt=20541 i.e. 28MAR2016 MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:load_to_dt=&load_to_dt i.e. %sysfunc(putn(&load_to_dt, date9.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 NOTE:(UPD_LOAD_DATES):PARAMETER:load_to_dt=20541 i.e. 28MAR2016 MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:run_start_dttm=&run_start_dttm i.e. %sysfunc(putn(&run_start_dttm, datetime21.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable RUN_START_DTTM resolves to . SYMBOLGEN: Macro variable RUN_START_DTTM resolves to . NOTE:(UPD_LOAD_DATES):PARAMETER:run_start_dttm=. i.e. . MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:run_end_dttm=&run_end_dttm i.e. %sysfunc(putn(&run_end_dttm, datetime21.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable RUN_END_DTTM resolves to . SYMBOLGEN: Macro variable RUN_END_DTTM resolves to . NOTE:(UPD_LOAD_DATES):PARAMETER:run_end_dttm=. i.e. . 6 The SAS System 07:32 Wednesday, March 30, 2016 MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:old_status=&old_status SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable OLD_STATUS resolves to 0 NOTE:(UPD_LOAD_DATES):PARAMETER:old_status=0 MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:new_status=&new_status SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable NEW_STATUS resolves to 1 NOTE:(UPD_LOAD_DATES):PARAMETER:new_status=1 MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):PARAMETER:rows_loaded=&rows_loaded SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable ROWS_LOADED resolves to null NOTE:(UPD_LOAD_DATES):PARAMETER:rows_loaded=null MLOGIC(UPD_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK SYMBOLGEN: Macro variable OLD_STATUS resolves to 0 SYMBOLGEN: Macro variable NEW_STATUS resolves to 1 SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 MLOGIC(UPD_LOAD_DATES): %IF condition &load_name ne  and &old_status ne . and &new_status ne . and &load_from_dt ne . and &load_to_dt ne . is TRUE MPRINT(UPD_LOAD_DATES): data _null_; SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 MPRINT(UPD_LOAD_DATES): if intck('day', 20541, 20541) lt 0 then do; SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 MPRINT(UPD_LOAD_DATES): call symput("load_from_dt", 20541); SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 MPRINT(UPD_LOAD_DATES): call symput("load_to_dt", 20541); MPRINT(UPD_LOAD_DATES): end; MPRINT(UPD_LOAD_DATES): run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 108:2 108:2 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(UPD_LOAD_DATES): proc sql noprint; SYMBOLGEN: Macro variable LIBRARY resolves to control SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 SYMBOLGEN: Macro variable OLD_STATUS resolves to 0 MPRINT(UPD_LOAD_DATES): select count(1) into :obs_exist from control.mart_control where load_name="FACT_WORK" and datepart(load_from_dt) eq 20541 and datepart(load_to_dt) eq 20541 and status=0; MPRINT(UPD_LOAD_DATES): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.02 seconds SYMBOLGEN: Macro variable OBS_EXIST resolves to 1 MLOGIC(UPD_LOAD_DATES): %IF condition &obs_exist eq 1 is TRUE SYMBOLGEN: Macro variable NEW_STATUS resolves to 1 7 The SAS System 07:32 Wednesday, March 30, 2016 MLOGIC(UPD_LOAD_DATES): %IF condition &new_status eq 1 is TRUE SYMBOLGEN: Macro variable RUN_START_DTTM resolves to . MLOGIC(UPD_LOAD_DATES): %IF condition &run_start_dttm eq . is TRUE MPRINT(UPD_LOAD_DATES): data _null_; MPRINT(UPD_LOAD_DATES): new_start_dttm=datetime(); MPRINT(UPD_LOAD_DATES): new_end_dttm=.; MPRINT(UPD_LOAD_DATES): call symput("run_start_dttm", new_start_dttm); MPRINT(UPD_LOAD_DATES): call symput("run_end_dttm", new_end_dttm); MPRINT(UPD_LOAD_DATES): run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 108:158 108:218 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MLOGIC(UPD_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):DEBUG:run_start_dttm=&run_start_dttm i.e. %sysfunc(putn(&run_start_dttm, datetime21.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable RUN_START_DTTM resolves to 1774943484.8 SYMBOLGEN: Macro variable RUN_START_DTTM resolves to 1774943484.8 NOTE:(UPD_LOAD_DATES):DEBUG:run_start_dttm=1774943484.8 i.e. 30MAR2016:07:51:25 MLOGIC(UPD_LOAD_DATES): %PUT NOTE:(&SYSMACRONAME):DEBUG:run_end_dttm=&run_end_dttm i.e. %sysfunc(putn(&run_end_dttm, datetime21.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to UPD_LOAD_DATES SYMBOLGEN: Macro variable RUN_END_DTTM resolves to . SYMBOLGEN: Macro variable RUN_END_DTTM resolves to . NOTE:(UPD_LOAD_DATES):DEBUG:run_end_dttm= . i.e. . MLOGIC(UPD_LOAD_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(UPD_LOAD_DATES): %LET (variable name is RUN_START_DTTMF) SYMBOLGEN: Macro variable RUN_START_DTTM resolves to 1774943484.8 MLOGIC(UPD_LOAD_DATES): %LET (variable name is RUN_START_DTTMO) SYMBOLGEN: Macro variable RUN_START_DTTMF resolves to 30MAR2016:07:51:25 SYMBOLGEN: Macro variable RUN_END_DTTM resolves to . MLOGIC(UPD_LOAD_DATES): %IF condition &run_end_dttm ne . is FALSE MLOGIC(UPD_LOAD_DATES): %LET (variable name is RUN_END_DTTMO) MLOGIC(UPD_LOAD_DATES): %LET (variable name is LOAD_FROM_DTF) SYMBOLGEN: Macro variable LOAD_FROM_DT resolves to 20541 MLOGIC(UPD_LOAD_DATES): %LET (variable name is LOAD_FROM_DTO) SYMBOLGEN: Macro variable LOAD_FROM_DTF resolves to 28MAR2016 MLOGIC(UPD_LOAD_DATES): %LET (variable name is LOAD_TO_DTF) SYMBOLGEN: Macro variable LOAD_TO_DT resolves to 20541 MLOGIC(UPD_LOAD_DATES): %LET (variable name is LOAD_TO_DTO) SYMBOLGEN: Macro variable LOAD_TO_DTF resolves to 28MAR2016 MLOGIC(UPD_LOAD_DATES): %LET (variable name is LOAD_NAMEO) SYMBOLGEN: Macro variable LOAD_NAME resolves to FACT_WORK MLOGIC(UPD_LOAD_DATES): %LET (variable name is CONTROLUSER1) SYMBOLGEN: Macro variable CONTROLSCHEMA resolves to CONTROL MLOGIC(UPD_LOAD_DATES): %LET (variable name is CONTROLPASSWORD1) SYMBOLGEN: Macro variable CONTROLPASSWORD resolves to "{sas002}75F37A291410BEE33B341E240A0A560059D455BF" MLOGIC(UPD_LOAD_DATES): %LET (variable name is CONTROLPATH1) SYMBOLGEN: Macro variable CONTROLPATH resolves to DBCW MPRINT(UPD_LOAD_DATES): proc sql; 8 The SAS System 07:32 Wednesday, March 30, 2016 SYMBOLGEN: Macro variable CONTROLUSER1 resolves to CONTROL SYMBOLGEN: Macro variable CONTROLPASSWORD1 resolves to "{sas002}75F37A291410BEE33B341E240A0A560059D455BF" SYMBOLGEN: Macro variable CONTROLPATH1 resolves to DBCW MPRINT(UPD_LOAD_DATES): connect to oracle(user = CONTROL password = "{sas002}75F37A291410BEE33B341E240A0A560059D455BF" path = DBCW); SYMBOLGEN: Macro variable LOAD_TYPE resolves to mart_control SYMBOLGEN: Macro variable RUN_START_DTTMO resolves to to_date('30MAR2016:07:51:25','DDMONYYYY:HH24:MI:SS') SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable RUN_END_DTTMO resolves to null SYMBOLGEN: Macro variable NEW_STATUS resolves to 1 SYMBOLGEN: Macro variable ROWS_LOADED resolves to null SYMBOLGEN: Macro variable LOAD_NAMEO resolves to 'FACT_WORK' SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable LOAD_FROM_DTO resolves to to_char(to_date('28MAR2016','DDMONYYYY'),'DDMONYYYY') SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable LOAD_TO_DTO resolves to to_char(to_date('28MAR2016','DDMONYYYY'),'DDMONYYYY') SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable OLD_STATUS resolves to 0 MPRINT(UPD_LOAD_DATES): exec( update mart_control set run_start_dttm=to_date('30MAR2016:07:51:25','DDMONYYYY:HH24:MI:SS'), run_end_dttm=null, status=1, rows_loaded=null where load_name='FACT_WORK' and to_char(load_from_dt,'DDMONYYYY') = to_char(to_date('28MAR2016','DDMONYYYY'),'DDMONYYYY') and to_char(load_to_dt,'DDMONYYYY') = to_char(to_date('28MAR2016','DDMONYYYY'),'DDMONYYYY') and status=0 ) by oracle; MPRINT(UPD_LOAD_DATES): disconnect from oracle; MPRINT(UPD_LOAD_DATES): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds MLOGIC(UPD_LOAD_DATES): Ending execution. MPRINT(GET_AVAIL_DATES): ; MLOGIC(GET_AVAIL_DATES): %LET (variable name is MIN_LOAD_DTTM) SYMBOLGEN: Macro variable GET_MIN_DATE resolves to 20541 MPRINT(GET_AVAIL_DATES): data _null_; SYMBOLGEN: Macro variable GET_MAX_DATE resolves to 20541 MPRINT(GET_AVAIL_DATES): max_load_dttm=dhms(intnx('day', 20541, 1),00,00,00); MPRINT(GET_AVAIL_DATES): call symput("max_load_dttm", max_load_dttm); MPRINT(GET_AVAIL_DATES): run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 108:120 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MLOGIC(GET_AVAIL_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_AVAIL_DATES): %PUT NOTE:(&SYSMACRONAME):DEBUG:min_load_dttm=&min_load_dttm i.e. %sysfunc(putn(&min_load_dttm, datetime21.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_AVAIL_DATES SYMBOLGEN: Macro variable MIN_LOAD_DTTM resolves to 1774742400 SYMBOLGEN: Macro variable MIN_LOAD_DTTM resolves to 1774742400 NOTE:(GET_AVAIL_DATES):DEBUG:min_load_dttm=1774742400 i.e. 28MAR2016:00:00:00 MLOGIC(GET_AVAIL_DATES): %PUT NOTE:(&SYSMACRONAME):DEBUG:max_load_dttm=&max_load_dttm i.e. %sysfunc(putn(&max_load_dttm, datetime21.)) SYMBOLGEN: Macro variable SYSMACRONAME resolves to GET_AVAIL_DATES 9 The SAS System 07:32 Wednesday, March 30, 2016 SYMBOLGEN: Macro variable MAX_LOAD_DTTM resolves to 1774828800 SYMBOLGEN: Macro variable MAX_LOAD_DTTM resolves to 1774828800 NOTE:(GET_AVAIL_DATES):DEBUG:max_load_dttm= 1774828800 i.e. 29MAR2016:00:00:00 MLOGIC(GET_AVAIL_DATES): %PUT  *----------------------------------------------------------------------------------------------------; MLOGIC(GET_AVAIL_DATES): Ending execution. 109 %global etls_recordsBefore; 110 %global etls_recordsAfter; 111 /*---- End of Pre-Process Code ----*/ 112 113 %rcSet(&syserr); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SYSERR resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 114 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 115 116 /*==========================================================================* 117 * Step: Extract current interface date(s) A5VECZJZ.BJ0000TO * 118 * Transform: Extract * 119 * Description: * 120 * * 121 * Source Table: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * 122 * bcw_dtl.ETL_WORK_ITEM_NUM * 123 * Target Table: Extract Target - A5VECZJZ.BK0000JA * 124 * work.W667OQ0R_rule1_1 * 125 *==========================================================================*/ 126 127 %let transformID = %quote(A5VECZJZ.BJ0000TO); 128 %let trans_rc = 0; 129 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 130 131 %let SYSLAST = %nrquote(bcw_dtl.ETL_WORK_ITEM_NUM); 132 133 %global etls_sql_pushDown; 134 %let etls_sql_pushDown = -1; 135 option DBIDIRECTEXEC; 136 137 /*---- Map the columns ----*/ 138 proc datasets lib = work nolist nowarn memtype = (data view); 139 delete W667OQ0R_rule1_1; 10 The SAS System 07:32 Wednesday, March 30, 2016 140 quit; NOTE: Deleting WORK.W667OQ0R_RULE1_1 (memtype=VIEW). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 141 142 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 143 proc sql; 144 create view work.W667OQ0R_rule1_1 as 145 select 146 WORK_ITEM_REFERENCE_KEY, 147 CUSTOMER_ID, 148 WORK_ITEM_ID, 149 WORK_ITEM_KEY, 150 INTERFACE_DTTM 151 from &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to BCW_DTL.ETL_WORK_ITEM_NUM 152 where INTERFACE_DTTM >= &min_load_dttm and INTERFACE_DTTM < &max_load_dttm SYMBOLGEN: Macro variable MIN_LOAD_DTTM resolves to 1774742400 SYMBOLGEN: Macro variable MAX_LOAD_DTTM resolves to 1774828800 153 and work_item_id in ( 154 6032488, 155 6032491 156 ) 157 order by 158 WORK_ITEM_ID, 159 WORK_ITEM_REFERENCE_KEY 160 ; NOTE: SQL view WORK.W667OQ0R_RULE1_1 has been defined. 161 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 162 163 %let SYSLAST = work.W667OQ0R_rule1_1; 164 165 %global etls_sql_pushDown; 166 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 167 168 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 169 11 The SAS System 07:32 Wednesday, March 30, 2016 170 171 172 /** Step end Extract current interface date(s) **/ 173 174 /*==========================================================================* 175 * Step: Dedupe by work_item_id A5VECZJZ.BJ0000TP * 176 * Transform: Sort * 177 * Description: * 178 * * 179 * Source Table: Extract Target - A5VECZJZ.BK0000JA * 180 * work.W667OQ0R_rule1_1 * 181 * Target Table: Sort Target - work.W667OQT8_rule1_2 A5VECZJZ.BK0000JB * 182 *==========================================================================*/ 183 184 %let transformID = %quote(A5VECZJZ.BJ0000TP); 185 %let trans_rc = 0; 186 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 187 188 %let SYSLAST = %nrquote(work.W667OQ0R_rule1_1); 189 190 proc datasets lib = work nolist nowarn memtype = (data view); 191 delete W667OQT8_rule1_2; 192 quit; NOTE: Deleting WORK.W667OQT8_RULE1_2 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 193 194 proc sort data = &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W667OQ0R_RULE1_1 195 out = work.W667OQT8_rule1_2 196 NODUPKEY ; 197 by 198 WORK_ITEM_ID 199 ; 200 run; NOTE: There were 4 observations read from the data set WORK.W667OQ0R_RULE1_1. NOTE: 2 observations with duplicate key values were deleted. NOTE: The data set WORK.W667OQT8_RULE1_2 has 2 observations and 5 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 201 202 %rcSet(&syserr); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SYSERR resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 12 The SAS System 07:32 Wednesday, March 30, 2016 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 203 204 205 206 /** Step end Dedupe by work_item_id **/ 207 208 /*==========================================================================* 209 * Step: Get latest version of work item data A5VECZJZ.BJ0000TQ * 210 * Transform: Join * 211 * Description: * 212 * * 213 * Source Tables: ETL_WORK_ITEM - bcw_dtl.ETL_WORK_ITEM A5VECZJZ.BQ00007K * 214 * Sort Target - work.W667OQT8_rule1_2 A5VECZJZ.BK0000JB * 215 * Target Table: SQL Target - work.W667OVS7_rule2 A5VECZJZ.BK0000JC * 216 *==========================================================================*/ 217 218 %let transformID = %quote(A5VECZJZ.BJ0000TQ); 219 %let trans_rc = 0; 220 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 221 222 %global etls_sql_pushDown; 223 %let etls_sql_pushDown = -1; 224 option DBIDIRECTEXEC; 225 226 proc datasets lib = work nolist nowarn memtype = (data view); 227 delete W667OVS7_rule2; 228 quit; NOTE: Deleting WORK.W667OVS7_RULE2 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 229 230 proc sql; 231 create table work.W667OVS7_rule2 as 232 select 233 W667OQT8_rule1_2.WORK_ITEM_REFERENCE_KEY length = 8 234 format = 12. 235 informat = 12., 236 W667OQT8_rule1_2.CUSTOMER_ID length = 8 237 format = 12. 238 informat = 12., 239 W667OQT8_rule1_2.WORK_ITEM_ID length = 8 240 format = 12. 241 informat = 12., 242 W667OQT8_rule1_2.WORK_ITEM_KEY length = 8 243 format = 12. 244 informat = 12., 245 W667OQT8_rule1_2.INTERFACE_DTTM length = 8 246 format = DATETIME20. 247 informat = DATETIME20., 248 ETL_WORK_ITEM.COMPLETED_DTTM as WORK_COMPLETE_DTTM length = 8 249 format = DATETIME20. 250 informat = DATETIME20., 251 ETL_WORK_ITEM.CREATED_DTTM as WORK_CREATED_DTTM length = 8 13 The SAS System 07:32 Wednesday, March 30, 2016 252 format = DATETIME20. 253 informat = DATETIME20., 254 case 255 when ETL_WORK_ITEM.PROM_CODE_KEY_SOURCE is null and ETL_WORK_ITEM.PROM_CODE_ID_SOURCE is null 256 then 0 257 when ETL_WORK_ITEM.PROM_CODE_KEY_SOURCE is null and ETL_WORK_ITEM.PROM_CODE_ID_SOURCE is not null 258 then -1 259 else ETL_WORK_ITEM.PROM_CODE_KEY_SOURCE 260 end as PROM_CODE_KEY_SOURCE length = 8 261 format = 11. 262 informat = 11., 263 ETL_WORK_ITEM.REASON_ID as REASON_ID_WORK length = 8 264 format = 11. 265 informat = 11., 266 ETL_WORK_ITEM.REFERRAL_STAFF_NAME length = 50 267 format = $50. 268 informat = $50., 269 ETL_WORK_ITEM.REFERRAL_STAFF_NO length = 10 270 format = $10. 271 informat = $10., 272 ETL_WORK_ITEM.REFERRAL_STAFF_DEPT length = 50 273 format = $50. 274 informat = $50., 275 ETL_WORK_ITEM.REFERRAL_STAFF_MANAGER length = 50 276 format = $50. 277 informat = $50., 278 case 279 when ETL_WORK_ITEM.STATUS_KEY is null and ETL_WORK_ITEM.STATUS_ID is null 280 then 0 281 when ETL_WORK_ITEM.STATUS_KEY is null and ETL_WORK_ITEM.STATUS_ID is not null 282 then -1 283 else ETL_WORK_ITEM.STATUS_KEY 284 end as STATUS_KEY_WORK length = 8 285 format = 11. 286 informat = 11., 287 case 288 when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is null 289 then 0 290 when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is not null 291 then -1 292 else ETL_WORK_ITEM.TEAM_KEY_OWNER 293 end as TEAM_KEY_OWNER_LTST length = 8 294 format = 11. 295 informat = 11., 296 case 297 when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is null 298 then 0 299 when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is not null 300 then -1 301 else ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER 302 end as TEAM_MEMBER_KEY_OWNER_LTST length = 8 303 format = 11. 304 informat = 11., 305 case 306 when ETL_WORK_ITEM.WORK_ITEM_TYPE_KEY is null and ETL_WORK_ITEM.WORK_ITEM_TYPE_ID is null 307 then 0 308 when ETL_WORK_ITEM.WORK_ITEM_TYPE_KEY is null and ETL_WORK_ITEM.WORK_ITEM_TYPE_ID is not null 309 then -1 14 The SAS System 07:32 Wednesday, March 30, 2016 310 else ETL_WORK_ITEM.WORK_ITEM_TYPE_KEY 311 end as WORK_ITEM_TYPE_KEY length = 8 312 format = 11. 313 informat = 11., 314 ETL_WORK_ITEM.WORK_ITEM_SUB_TYPE_ID length = 8 315 format = 11. 316 informat = 11., 317 ETL_WORK_ITEM.WORK_ITEM_TYPE_ID length = 8 318 format = 11. 319 informat = 11. 320 from 321 work.W667OQT8_rule1_2 left join 322 bcw_dtl.ETL_WORK_ITEM 323 on 324 ( 325 W667OQT8_rule1_2.WORK_ITEM_KEY = ETL_WORK_ITEM.WORK_ITEM_KEY 326 ) 327 ; NOTE: Table WORK.W667OVS7_RULE2 created, with 2 rows and 19 columns. 328 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds cpu time 0.02 seconds 329 330 %global etls_sql_pushDown; 331 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 332 333 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 334 335 336 337 /** Step end Get latest version of work item data **/ 338 339 /*==========================================================================* 340 * Step: Get first version of work item data A5VECZJZ.BJ0000TR * 341 * Transform: Join * 342 * Description: * 343 * * 344 * Source Tables: ETL_WORK_ITEM - bcw_dtl.ETL_WORK_ITEM A5VECZJZ.BQ00007K * 345 * SQL Target - work.W667OVS7_rule2 A5VECZJZ.BK0000JC * 346 * Target Table: SQL Target - work.W667P4W9_rule3_1 A5VECZJZ.BK0000JD * 347 *==========================================================================*/ 348 349 %let transformID = %quote(A5VECZJZ.BJ0000TR); 15 The SAS System 07:32 Wednesday, March 30, 2016 350 %let trans_rc = 0; 351 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 352 353 %global etls_sql_pushDown; 354 %let etls_sql_pushDown = -1; 355 option DBIDIRECTEXEC; 356 357 proc datasets lib = work nolist nowarn memtype = (data view); 358 delete W667P4W9_rule3_1; 359 quit; NOTE: Deleting WORK.W667P4W9_RULE3_1 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 360 361 proc sql; 362 create table work.W667P4W9_rule3_1 as 363 select 364 W667OVS7_rule2.WORK_ITEM_REFERENCE_KEY length = 8 365 format = 12. 366 informat = 12., 367 W667OVS7_rule2.CUSTOMER_ID length = 8 368 format = 12. 369 informat = 12., 370 W667OVS7_rule2.WORK_ITEM_ID length = 8 371 format = 12. 372 informat = 12., 373 W667OVS7_rule2.WORK_ITEM_KEY length = 8 374 format = 12. 375 informat = 12., 376 W667OVS7_rule2.INTERFACE_DTTM length = 8 377 format = DATETIME20. 378 informat = DATETIME20., 379 W667OVS7_rule2.WORK_COMPLETE_DTTM length = 8 380 format = DATETIME20. 381 informat = DATETIME20., 382 W667OVS7_rule2.WORK_CREATED_DTTM length = 8 383 format = DATETIME20. 384 informat = DATETIME20., 385 W667OVS7_rule2.PROM_CODE_KEY_SOURCE length = 8 386 format = 11. 387 informat = 11., 388 W667OVS7_rule2.REASON_ID_WORK length = 8 389 format = 11. 390 informat = 11., 391 W667OVS7_rule2.REFERRAL_STAFF_NAME length = 50 392 format = $50. 393 informat = $50., 394 W667OVS7_rule2.REFERRAL_STAFF_NO length = 10 395 format = $10. 396 informat = $10., 397 W667OVS7_rule2.REFERRAL_STAFF_DEPT length = 50 398 format = $50. 399 informat = $50., 400 W667OVS7_rule2.REFERRAL_STAFF_MANAGER length = 50 16 The SAS System 07:32 Wednesday, March 30, 2016 401 format = $50. 402 informat = $50., 403 W667OVS7_rule2.STATUS_KEY_WORK length = 8 404 format = 11. 405 informat = 11., 406 W667OVS7_rule2.TEAM_KEY_OWNER_LTST length = 8 407 format = 11. 408 informat = 11., 409 W667OVS7_rule2.TEAM_MEMBER_KEY_OWNER_LTST length = 8 410 format = 11. 411 informat = 11., 412 W667OVS7_rule2.WORK_ITEM_TYPE_KEY length = 8 413 format = 11. 414 informat = 11., 415 W667OVS7_rule2.WORK_ITEM_SUB_TYPE_ID length = 8 416 format = 11. 417 informat = 11., 418 W667OVS7_rule2.WORK_ITEM_TYPE_ID length = 8 419 format = 11. 420 informat = 11., 421 case 422 when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is null 423 then 0 424 when ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_MEMBER_ID is not null 425 then -1 426 else ETL_WORK_ITEM.TEAM_MEMBER_KEY_OWNER 427 end as TEAM_MEMBER_KEY_OWNER_FRST length = 8 428 format = 11. 429 informat = 11., 430 case 431 when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is null 432 then 0 433 when ETL_WORK_ITEM.TEAM_KEY_OWNER is null and ETL_WORK_ITEM.OWNER_TEAM_ID is not null 434 then -1 435 else ETL_WORK_ITEM.TEAM_KEY_OWNER 436 end as TEAM_KEY_OWNER_FRST length = 8 437 format = 11. 438 informat = 11., 439 ETL_WORK_ITEM.VALID_FROM_DTTM length = 8 440 format = DATETIME20. 441 informat = DATETIME20. 442 from 443 work.W667OVS7_rule2 left join 444 bcw_dtl.ETL_WORK_ITEM 445 on 446 ( 447 W667OVS7_rule2.WORK_ITEM_ID = ETL_WORK_ITEM.WORK_ITEM_ID 448 ) 449 order by 450 W667OVS7_rule2.WORK_ITEM_ID, 451 ETL_WORK_ITEM.VALID_FROM_DTTM 452 ; NOTE: Table WORK.W667P4W9_RULE3_1 created, with 2 rows and 22 columns. 453 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.02 seconds 17 The SAS System 07:32 Wednesday, March 30, 2016 454 455 %global etls_sql_pushDown; 456 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 457 458 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 459 460 461 462 /** Step end Get first version of work item data **/ 463 464 /*==========================================================================* 465 * Step: Dedupe to keep version of work item A5VECZJZ.BJ0000TS * 466 * with earliest valid from * 467 * Transform: Sort * 468 * Description: * 469 * * 470 * Source Table: SQL Target - work.W667P4W9_rule3_1 A5VECZJZ.BK0000JD * 471 * Target Table: Sort Target - work.W667P7T5_rule3_2 A5VECZJZ.BK0000JE * 472 *==========================================================================*/ 473 474 %let transformID = %quote(A5VECZJZ.BJ0000TS); 475 %let trans_rc = 0; 476 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 477 478 %let SYSLAST = %nrquote(work.W667P4W9_rule3_1); 479 480 proc datasets lib = work nolist nowarn memtype = (data view); 481 delete W667P7T5_rule3_2; 482 quit; NOTE: Deleting WORK.W667P7T5_RULE3_2 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 483 484 proc sort data = &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W667P4W9_RULE3_1 485 out = work.W667P7T5_rule3_2 486 NODUPKEY ; 487 by 488 WORK_ITEM_ID 489 ; 490 run; 18 The SAS System 07:32 Wednesday, March 30, 2016 NOTE: There were 2 observations read from the data set WORK.W667P4W9_RULE3_1. NOTE: 0 observations with duplicate key values were deleted. NOTE: The data set WORK.W667P7T5_RULE3_2 has 2 observations and 22 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 491 492 %rcSet(&syserr); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SYSERR resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 493 494 495 496 /** Step end Dedupe to keep version of work item with earliest valid from **/ 497 498 /*==========================================================================* 499 * Step: Get latest version of the quote A5VECZJZ.BJ0000TT * 500 * Transform: Join * 501 * Description: * 502 * * 503 * Source Tables: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * 504 * bcw_dtl.ETL_WORK_ITEM_NUM * 505 * ETL_QUOTE - bcw_dtl.ETL_QUOTE A5VECZJZ.BQ00007A * 506 * Sort Target - work.W667P7T5_rule3_2 A5VECZJZ.BK0000JE * 507 * Target Table: SQL Target - work.W667PHXS_RULE4_1 A5VECZJZ.BK0000JF * 508 *==========================================================================*/ 509 510 %let transformID = %quote(A5VECZJZ.BJ0000TT); 511 %let trans_rc = 0; 512 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 513 514 %global etls_sql_pushDown; 515 %let etls_sql_pushDown = -1; 516 option DBIDIRECTEXEC; 517 518 proc datasets lib = work nolist nowarn memtype = (data view); 519 delete W667PHXS_RULE4_1; 520 quit; NOTE: Deleting WORK.W667PHXS_RULE4_1 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 521 522 proc sql; 19 The SAS System 07:32 Wednesday, March 30, 2016 523 create table work.W667PHXS_RULE4_1 as 524 select 525 W667P7T5_rule3_2.WORK_ITEM_REFERENCE_KEY length = 8 526 format = 12. 527 informat = 12., 528 W667P7T5_rule3_2.CUSTOMER_ID length = 8 529 format = 12. 530 informat = 12., 531 W667P7T5_rule3_2.WORK_ITEM_ID length = 8 532 format = 12. 533 informat = 12., 534 W667P7T5_rule3_2.WORK_ITEM_KEY length = 8 535 format = 12. 536 informat = 12., 537 W667P7T5_rule3_2.INTERFACE_DTTM length = 8 538 format = DATETIME20. 539 informat = DATETIME20., 540 W667P7T5_rule3_2.WORK_COMPLETE_DTTM length = 8 541 format = DATETIME20. 542 informat = DATETIME20., 543 W667P7T5_rule3_2.WORK_CREATED_DTTM length = 8 544 format = DATETIME20. 545 informat = DATETIME20., 546 W667P7T5_rule3_2.PROM_CODE_KEY_SOURCE length = 8 547 format = 11. 548 informat = 11., 549 W667P7T5_rule3_2.REASON_ID_WORK length = 8 550 format = 11. 551 informat = 11., 552 W667P7T5_rule3_2.REFERRAL_STAFF_NAME length = 50 553 format = $50. 554 informat = $50., 555 W667P7T5_rule3_2.REFERRAL_STAFF_NO length = 10 556 format = $10. 557 informat = $10., 558 W667P7T5_rule3_2.REFERRAL_STAFF_DEPT length = 50 559 format = $50. 560 informat = $50., 561 W667P7T5_rule3_2.REFERRAL_STAFF_MANAGER length = 50 562 format = $50. 563 informat = $50., 564 W667P7T5_rule3_2.STATUS_KEY_WORK length = 8 565 format = 11. 566 informat = 11., 567 W667P7T5_rule3_2.TEAM_KEY_OWNER_LTST length = 8 568 format = 11. 569 informat = 11., 570 W667P7T5_rule3_2.TEAM_MEMBER_KEY_OWNER_LTST length = 8 571 format = 11. 572 informat = 11., 573 W667P7T5_rule3_2.WORK_ITEM_TYPE_KEY length = 8 574 format = 11. 575 informat = 11., 576 W667P7T5_rule3_2.WORK_ITEM_SUB_TYPE_ID length = 8 577 format = 11. 578 informat = 11., 579 W667P7T5_rule3_2.WORK_ITEM_TYPE_ID length = 8 580 format = 11. 20 The SAS System 07:32 Wednesday, March 30, 2016 581 informat = 11., 582 W667P7T5_rule3_2.TEAM_MEMBER_KEY_OWNER_FRST length = 8 583 format = 11. 584 informat = 11., 585 W667P7T5_rule3_2.TEAM_KEY_OWNER_FRST length = 8 586 format = 11. 587 informat = 11., 588 IFN(ETL_WORK_ITEM_NUM.COUNT_OF_ALL_QUOTES IS NULL,0,ETL_WORK_ITEM_NUM.COUNT_OF_ALL_QUOTES ) as 588 ! CNT_ALL_QUOTES length = 8 589 format = 12. 590 informat = 12., 591 IFN(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS ) as 591 ! CNT_CUR_UNITS_LTST length = 8 592 format = 12. 593 informat = 12., 594 IFN(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS ) as 594 ! CNT_MAX_UNITS_LTST length = 8 595 format = 12. 596 informat = 12., 597 IFN(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS ) as 597 ! CNT_REQ_UNITS_LTST length = 8 598 format = 11. 599 informat = 11., 600 ETL_WORK_ITEM_NUM.VALID_FROM_DTTM as WORK_VALID_FROM_DTTM length = 8 601 format = DATETIME20. 602 informat = DATETIME20., 603 case 604 when ETL_WORK_ITEM_NUM.QUOTE_ID is null and ETL_QUOTE.QUOTE_KEY is null 605 then 0 606 when ETL_WORK_ITEM_NUM.QUOTE_ID is not null and ETL_QUOTE.QUOTE_KEY is null 607 then -1 608 else ETL_QUOTE.QUOTE_KEY 609 end as QUOTE_KEY_LTST length = 8 610 format = 11. 611 informat = 11., 612 ETL_QUOTE.QUOTE_ID length = 8 613 format = 11. 614 informat = 11., 615 case 616 when ETL_QUOTE.PROM_CODE_KEY_QUOTE is null and ETL_QUOTE.PROM_CODE_ID_QUOTE is null 617 then 0 618 when ETL_QUOTE.PROM_CODE_KEY_QUOTE is null and ETL_QUOTE.PROM_CODE_ID_QUOTE is not null 619 then -1 620 else ETL_QUOTE.PROM_CODE_KEY_QUOTE 621 end as PROM_CODE_KEY_QUOTE length = 8 622 format = 11. 623 informat = 11., 624 ETL_QUOTE.QUOTE_STARTED_DTTM length = 8 625 format = DATETIME20. 626 informat = DATETIME20., 627 ETL_QUOTE.QUOTE_ACCEPTED_DTTM length = 8 628 format = DATETIME20. 629 informat = DATETIME20., 630 IFN(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX IS NULL,0,ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX ) as QUOTE_PRICE_INC_TAX_LTST 630 ! length = 8 631 format = 21.4 632 informat = 21.4, 633 ifn(ETL_QUOTE.QUOTE_PRICE_WITH_TAX is null,0,ETL_QUOTE.QUOTE_PRICE_WITH_TAX ) as QUOTE_ORIG_PRICE_LTST length = 8 21 The SAS System 07:32 Wednesday, March 30, 2016 634 format = 21.4 635 informat = 21.4, 636 IFN(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX IS NULL,0, ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX ) as QUOTE_PRICE_EXC_TAX_LTST 636 ! length = 8 637 format = 21.4 638 informat = 21.4, 639 IFN(ETL_QUOTE.DISCOUNT_TOTAL IS NULL,0,ETL_QUOTE.DISCOUNT_TOTAL ) as QUOTE_DISC_TOT_LTST length = 8 640 format = 21.4 641 informat = 21.4, 642 ETL_QUOTE.VALID_FROM_DTTM as QUOTE_VALID_FROM_DTTM length = 8 643 format = DATETIME20. 644 informat = DATETIME20., 645 ETL_QUOTE.NEXT_RENEWAL_DTTM length = 8 646 format = DATETIME20. 647 informat = DATETIME20., 648 ETL_QUOTE.NOT_PROCEEDED_WITH_DTTM length = 8 649 format = DATETIME20. 650 informat = DATETIME20., 651 ETL_QUOTE.QUOTE_EXPIRED_DTTM length = 8 652 format = DATETIME20. 653 informat = DATETIME20., 654 case 655 when ETL_QUOTE.QUOTE_ACCEPTED_DTTM IS NOT NULL 656 then 3 657 when ETL_QUOTE.NOT_PROCEEDED_WITH_DTTM IS NULL AND ETL_QUOTE.QUOTE_KEY IS NOT NULL 658 then 2 659 else 1 660 end as SORT_SEQ length = 8, 661 IFN(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.BILLING_KEY IS NULL, -1,ETL_QUOTE.BILLING_KEY 661 ! ),ETL_QUOTE.BILLING_KEY) as BILLING_KEY length = 8 662 label = 'BILLING_KEY', 663 IFN(ETL_QUOTE.CONTRACT_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.CONTRACT_KEY IS NULL, -1,ETL_QUOTE.CONTRACT_KEY 663 ! ),ETL_QUOTE.CONTRACT_KEY) as CONTRACT_KEY length = 8 664 label = 'CONTRACT_KEY' 665 from 666 work.W667P7T5_rule3_2 left join 667 bcw_dtl.ETL_WORK_ITEM_NUM 668 on 669 ( 670 W667P7T5_rule3_2.WORK_ITEM_ID = ETL_WORK_ITEM_NUM.WORK_ITEM_ID 671 ) left join 672 bcw_dtl.ETL_QUOTE 673 on 674 ( 675 ETL_WORK_ITEM_NUM.QUOTE_KEY = ETL_QUOTE.QUOTE_KEY 676 and W667P7T5_rule3_2.WORK_ITEM_ID = ETL_WORK_ITEM_NUM.WORK_ITEM_ID 677 ) 678 ; NOTE: Table WORK.W667PHXS_RULE4_1 created, with 4 rows and 42 columns. 679 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.05 seconds cpu time 0.04 seconds 680 681 %global etls_sql_pushDown; 22 The SAS System 07:32 Wednesday, March 30, 2016 682 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 683 684 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 685 686 687 688 /** Step end Get latest version of the quote **/ 689 690 /*==========================================================================* 691 * Step: Determine which quote record to keep A5VECZJZ.BJ0000TU * 692 * Transform: Sort * 693 * Description: * 694 * * 695 * Source Table: SQL Target - work.W667PHXS_RULE4_1 A5VECZJZ.BK0000JF * 696 * Target Table: Sort Target - work.W667SLQK_RULE4_2 A5VECZJZ.BK0000JG * 697 *==========================================================================*/ 698 699 %let transformID = %quote(A5VECZJZ.BJ0000TU); 700 %let trans_rc = 0; 701 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 702 703 %let SYSLAST = %nrquote(work.W667PHXS_RULE4_1); 704 705 proc datasets lib = work nolist nowarn memtype = (data view); 706 delete W667SLQK_RULE4_2; 707 quit; NOTE: Deleting WORK.W667SLQK_RULE4_2 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 708 709 /*---- Map the columns ----*/ 710 proc datasets lib = work nolist nowarn memtype = (data view); 711 delete W1WSE7K; 712 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 713 714 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 23 The SAS System 07:32 Wednesday, March 30, 2016 715 proc sql; 716 create view work.W1WSE7K as 717 select 718 WORK_ITEM_REFERENCE_KEY, 719 CUSTOMER_ID, 720 WORK_ITEM_ID, 721 WORK_ITEM_KEY, 722 INTERFACE_DTTM, 723 WORK_COMPLETE_DTTM, 724 WORK_CREATED_DTTM, 725 PROM_CODE_KEY_SOURCE, 726 REASON_ID_WORK, 727 REFERRAL_STAFF_NAME, 728 REFERRAL_STAFF_NO, 729 REFERRAL_STAFF_DEPT, 730 REFERRAL_STAFF_MANAGER, 731 STATUS_KEY_WORK, 732 TEAM_KEY_OWNER_LTST, 733 TEAM_MEMBER_KEY_OWNER_LTST, 734 WORK_ITEM_TYPE_KEY, 735 WORK_ITEM_SUB_TYPE_ID, 736 WORK_ITEM_TYPE_ID, 737 TEAM_MEMBER_KEY_OWNER_FRST, 738 TEAM_KEY_OWNER_FRST, 739 CNT_ALL_QUOTES, 740 CNT_CUR_UNITS_LTST, 741 CNT_MAX_UNITS_LTST, 742 CNT_REQ_UNITS_LTST, 743 WORK_VALID_FROM_DTTM, 744 QUOTE_KEY_LTST, 745 QUOTE_ID, 746 PROM_CODE_KEY_QUOTE, 747 QUOTE_STARTED_DTTM, 748 QUOTE_ACCEPTED_DTTM, 749 QUOTE_PRICE_INC_TAX_LTST, 750 QUOTE_ORIG_PRICE_LTST, 751 QUOTE_PRICE_EXC_TAX_LTST, 752 QUOTE_DISC_TOT_LTST, 753 QUOTE_VALID_FROM_DTTM, 754 NEXT_RENEWAL_DTTM, 755 NOT_PROCEEDED_WITH_DTTM, 756 QUOTE_EXPIRED_DTTM, 757 SORT_SEQ 758 from &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W667PHXS_RULE4_1 759 ; NOTE: SQL view WORK.W1WSE7K has been defined. 760 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 761 762 %let SYSLAST = work.W1WSE7K; 763 764 proc sort data = &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W1WSE7K 24 The SAS System 07:32 Wednesday, March 30, 2016 765 out = work.W667SLQK_RULE4_2; 766 by 767 WORK_ITEM_ID 768 descending SORT_SEQ 769 descending QUOTE_STARTED_DTTM 770 descending QUOTE_VALID_FROM_DTTM 771 descending WORK_VALID_FROM_DTTM 772 ; 773 run; NOTE: There were 4 observations read from the data set WORK.W1WSE7K. NOTE: The data set WORK.W667SLQK_RULE4_2 has 4 observations and 40 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 774 775 %rcSet(&syserr); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SYSERR resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 776 777 proc datasets lib = work nolist nowarn memtype = (data view); 778 delete W1WSE7K; 779 quit; NOTE: Deleting WORK.W1WSE7K (memtype=VIEW). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 780 781 782 783 /** Step end Determine which quote record to keep **/ 784 785 /*==========================================================================* 786 * Step: Dedupe keeping only required latest A5VECZJZ.BJ0000TV * 787 * quote record * 788 * Transform: Sort * 789 * Description: * 790 * * 791 * Source Table: Sort Target - work.W667SLQK_RULE4_2 A5VECZJZ.BK0000JG * 792 * Target Table: Sort Target - work.W667SN06_RULE4_3 A5VECZJZ.BK0000JH * 793 *==========================================================================*/ 794 795 %let transformID = %quote(A5VECZJZ.BJ0000TV); 796 %let trans_rc = 0; 797 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 25 The SAS System 07:32 Wednesday, March 30, 2016 798 799 %let SYSLAST = %nrquote(work.W667SLQK_RULE4_2); 800 801 proc datasets lib = work nolist nowarn memtype = (data view); 802 delete W667SN06_RULE4_3; 803 quit; NOTE: Deleting WORK.W667SN06_RULE4_3 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 804 805 proc sort data = &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W667SLQK_RULE4_2 806 out = work.W667SN06_RULE4_3 807 NODUPKEY ; 808 by 809 WORK_ITEM_ID 810 ; 811 run; NOTE: There were 4 observations read from the data set WORK.W667SLQK_RULE4_2. NOTE: 2 observations with duplicate key values were deleted. NOTE: The data set WORK.W667SN06_RULE4_3 has 2 observations and 40 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 812 813 %rcSet(&syserr); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SYSERR resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 814 815 816 817 /** Step end Dedupe keeping only required latest quote record **/ 818 819 /*==========================================================================* 820 * Step: Get earliest version of quote A5VECZJZ.BJ0000TW * 821 * Transform: Join * 822 * Description: * 823 * * 824 * Source Tables: ETL_WORK_ITEM_NUM - A5VECZJZ.BQ00007L * 825 * bcw_dtl.ETL_WORK_ITEM_NUM * 826 * ETL_QUOTE - bcw_dtl.ETL_QUOTE A5VECZJZ.BQ00007A * 827 * Sort Target - work.W667SN06_RULE4_3 A5VECZJZ.BK0000JH * 828 * Target Table: SQL Target - work.W667TARG_RULE5_1 A5VECZJZ.BK0000JI * 26 The SAS System 07:32 Wednesday, March 30, 2016 829 *==========================================================================*/ 830 831 %let transformID = %quote(A5VECZJZ.BJ0000TW); 832 %let trans_rc = 0; 833 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 834 835 %global etls_sql_pushDown; 836 %let etls_sql_pushDown = -1; 837 option DBIDIRECTEXEC; 838 839 proc datasets lib = work nolist nowarn memtype = (data view); 840 delete W667TARG_RULE5_1; 841 quit; NOTE: Deleting WORK.W667TARG_RULE5_1 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 842 843 data _null_; 844 put "NOTE: The following column(s) do not have a column mapping, so the" 845 " value(s) will be set to missing: QUOTE_TYPE_KEY, STATUS_KEY_QUOTE," 846 " REASON_ID_QUOTE"; 847 run; 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 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 848 849 proc sql _tree _method; 850 create table work.W667TARG_RULE5_1 as 851 select 852 W667SN06_RULE4_3.WORK_ITEM_REFERENCE_KEY length = 8 853 format = 12. 854 informat = 12., 855 W667SN06_RULE4_3.CUSTOMER_ID length = 8 856 format = 12. 857 informat = 12., 858 W667SN06_RULE4_3.WORK_ITEM_ID length = 8 859 format = 12. 860 informat = 12., 861 W667SN06_RULE4_3.WORK_ITEM_KEY length = 8 862 format = 12. 863 informat = 12., 864 W667SN06_RULE4_3.INTERFACE_DTTM length = 8 865 format = DATETIME20. 866 informat = DATETIME20., 867 W667SN06_RULE4_3.WORK_COMPLETE_DTTM length = 8 868 format = DATETIME20. 869 informat = DATETIME20., 870 W667SN06_RULE4_3.WORK_CREATED_DTTM length = 8 871 format = DATETIME20. 27 The SAS System 07:32 Wednesday, March 30, 2016 872 informat = DATETIME20., 873 W667SN06_RULE4_3.PROM_CODE_KEY_SOURCE length = 8 874 format = 11. 875 informat = 11., 876 W667SN06_RULE4_3.REASON_ID_WORK length = 8 877 format = 11. 878 informat = 11., 879 W667SN06_RULE4_3.REFERRAL_STAFF_NAME length = 50 880 format = $50. 881 informat = $50., 882 W667SN06_RULE4_3.REFERRAL_STAFF_NO length = 10 883 format = $10. 884 informat = $10., 885 W667SN06_RULE4_3.REFERRAL_STAFF_DEPT length = 50 886 format = $50. 887 informat = $50., 888 W667SN06_RULE4_3.REFERRAL_STAFF_MANAGER length = 50 889 format = $50. 890 informat = $50., 891 W667SN06_RULE4_3.STATUS_KEY_WORK length = 8 892 format = 11. 893 informat = 11., 894 W667SN06_RULE4_3.TEAM_KEY_OWNER_LTST length = 8 895 format = 11. 896 informat = 11., 897 W667SN06_RULE4_3.TEAM_MEMBER_KEY_OWNER_LTST length = 8 898 format = 11. 899 informat = 11., 900 W667SN06_RULE4_3.WORK_ITEM_TYPE_KEY length = 8 901 format = 11. 902 informat = 11., 903 W667SN06_RULE4_3.WORK_ITEM_SUB_TYPE_ID length = 8 904 format = 11. 905 informat = 11., 906 W667SN06_RULE4_3.WORK_ITEM_TYPE_ID length = 8 907 format = 11. 908 informat = 11., 909 W667SN06_RULE4_3.TEAM_MEMBER_KEY_OWNER_FRST length = 8 910 format = 11. 911 informat = 11., 912 W667SN06_RULE4_3.TEAM_KEY_OWNER_FRST length = 8 913 format = 11. 914 informat = 11., 915 W667SN06_RULE4_3.CNT_ALL_QUOTES length = 8 916 format = 12. 917 informat = 12., 918 W667SN06_RULE4_3.CNT_CUR_UNITS_LTST length = 8 919 format = 12. 920 informat = 12., 921 W667SN06_RULE4_3.CNT_MAX_UNITS_LTST length = 8 922 format = 12. 923 informat = 12., 924 W667SN06_RULE4_3.CNT_REQ_UNITS_LTST length = 8 925 format = 11. 926 informat = 11., 927 W667SN06_RULE4_3.QUOTE_KEY_LTST length = 8 928 format = 11. 929 informat = 11., 28 The SAS System 07:32 Wednesday, March 30, 2016 930 W667SN06_RULE4_3.QUOTE_ID length = 8 931 format = 11. 932 informat = 11., 933 . as QUOTE_TYPE_KEY length = 8 934 format = 11. 935 informat = 11., 936 W667SN06_RULE4_3.PROM_CODE_KEY_QUOTE length = 8 937 format = 11. 938 informat = 11., 939 W667SN06_RULE4_3.QUOTE_STARTED_DTTM length = 8 940 format = DATETIME20. 941 informat = DATETIME20., 942 W667SN06_RULE4_3.QUOTE_ACCEPTED_DTTM length = 8 943 format = DATETIME20. 944 informat = DATETIME20., 945 W667SN06_RULE4_3.QUOTE_PRICE_INC_TAX_LTST length = 8 946 format = 21.4 947 informat = 21.4, 948 W667SN06_RULE4_3.QUOTE_ORIG_PRICE_LTST length = 8 949 format = 21.4 950 informat = 21.4, 951 W667SN06_RULE4_3.QUOTE_PRICE_EXC_TAX_LTST length = 8 952 format = 21.4 953 informat = 21.4, 954 W667SN06_RULE4_3.QUOTE_DISC_TOT_LTST length = 8 955 format = 21.4 956 informat = 21.4, 957 . as STATUS_KEY_QUOTE length = 8 958 format = 11. 959 informat = 11., 960 W667SN06_RULE4_3.NEXT_RENEWAL_DTTM length = 8 961 format = DATETIME20. 962 informat = DATETIME20., 963 W667SN06_RULE4_3.NOT_PROCEEDED_WITH_DTTM length = 8 964 format = DATETIME20. 965 informat = DATETIME20., 966 W667SN06_RULE4_3.QUOTE_EXPIRED_DTTM length = 8 967 format = DATETIME20. 968 informat = DATETIME20., 969 . as REASON_ID_QUOTE length = 8, 970 IFN(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS is null,0,ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS ) as 970 ! CNT_CUR_UNITS_FRST length = 8 971 format = 12. 972 informat = 12., 973 IFN(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS is null,0,ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS ) as 973 ! CNT_MAX_UNITS_FRST length = 8 974 format = 12. 975 informat = 12., 976 IFN(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS IS NULL,0,ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS ) as 976 ! CNT_REQ_UNITS_FRST length = 8 977 format = 11. 978 informat = 11., 979 ETL_WORK_ITEM_NUM.VALID_FROM_DTTM as WORK_VALID_FROM_DTTM length = 8 980 format = DATETIME20. 981 informat = DATETIME20., 982 case 983 when ETL_WORK_ITEM_NUM.QUOTE_ID is null and ETL_QUOTE.QUOTE_KEY is null 984 then 0 29 The SAS System 07:32 Wednesday, March 30, 2016 985 when ETL_WORK_ITEM_NUM.QUOTE_ID is not null and ETL_QUOTE.QUOTE_KEY is null 986 then -1 987 else ETL_QUOTE.QUOTE_KEY 988 end as QUOTE_KEY_FRST length = 8 989 format = 11. 990 informat = 11., 991 IFN(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX is null,0,ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX ) as QUOTE_PRICE_INC_TAX_FRST 991 ! length = 8 992 format = 21.4 993 informat = 21.4, 994 IFN(ETL_QUOTE.QUOTE_PRICE_WITH_TAX is null,0, ETL_QUOTE.QUOTE_PRICE_WITH_TAX ) as QUOTE_ORIG_PRICE_FRST length = 8 995 format = 21.4 996 informat = 21.4, 997 IFN(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX is null,0, ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX ) as QUOTE_PRICE_EXC_TAX_FRST 997 ! length = 8 998 format = 21.4 999 informat = 21.4, 1000 IFN(ETL_QUOTE.DISCOUNT_TOTAL is null,0,ETL_QUOTE.DISCOUNT_TOTAL ) as QUOTE_DISC_TOT_FRST length = 8 1001 format = 21.4 1002 informat = 21.4, 1003 IFN(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.BILLING_KEY IS NULL, -1,ETL_QUOTE.BILLING_KEY 1003 ! ),ETL_QUOTE.BILLING_KEY) as BILLING_KEY length = 8 1004 label = 'BILLING_KEY', 1005 IFN(ETL_QUOTE.CONTRACT_PROFILE_ID IS NULL, 0, IFN(ETL_QUOTE.CONTRACT_KEY IS NULL, -1,ETL_QUOTE.CONTRACT_KEY 1005 ! ),ETL_QUOTE.CONTRACT_KEY) as CONTRACT_KEY length = 8 1006 label = 'CONTRACT_KEY', 1007 ETL_QUOTE.VALID_FROM_DTTM as QUOTE_VALID_FROM_DTTM length = 8 1008 format = DATETIME20. 1009 informat = DATETIME20. 1010 from 1011 work.W667SN06_RULE4_3 left join 1012 bcw_dtl.ETL_WORK_ITEM_NUM 1013 on 1014 ( 1015 W667SN06_RULE4_3.WORK_ITEM_ID = ETL_WORK_ITEM_NUM.WORK_ITEM_ID 1016 ) left join 1017 bcw_dtl.ETL_QUOTE 1018 on 1019 ( 1020 ETL_WORK_ITEM_NUM.QUOTE_KEY = ETL_QUOTE.QUOTE_KEY 1021 ) 1022 order by 1023 W667SN06_RULE4_3.WORK_ITEM_ID, 1024 W667SN06_RULE4_3.QUOTE_STARTED_DTTM, 1025 W667SN06_RULE4_3.QUOTE_ID, 1026 ETL_QUOTE.VALID_FROM_DTTM, 1027 ETL_WORK_ITEM_NUM.VALID_FROM_DTTM 1028 ; NOTE: SQL execution methods chosen are: sqxcrta sqxsort sqxfil sqxjndx sqxjm sqxsort sqxsrc( BCW_DTL.ETL_WORK_ITEM_NUM ) 30 The SAS System 07:32 Wednesday, March 30, 2016 sqxsrc( WORK.W667SN06_RULE4_3 ) sqxsrc( BCW_DTL.ETL_QUOTE ) Tree as planned. /-SYM-M-(WORK_ITEM_REFERENCE_KEY:1 flag=0005) /-OBJ----| | |--SYM-M-(CUSTOMER_ID:2 flag=0005) | |--SYM-M-(WORK_ITEM_ID:3 flag=0005) | |--SYM-M-(WORK_ITEM_KEY:4 flag=0005) | |--SYM-M-(INTERFACE_DTTM:5 flag=0005) | |--SYM-M-(WORK_COMPLETE_DTTM:6 flag=0005) | |--SYM-M-(WORK_CREATED_DTTM:7 flag=0005) | |--SYM-M-(PROM_CODE_KEY_SOURCE:8 flag=0005) | |--SYM-M-(REASON_ID_WORK:9 flag=0005) | |--SYM-M-(REFERRAL_STAFF_NAME:10 flag=0005) | |--SYM-M-(REFERRAL_STAFF_NO:11 flag=0005) | |--SYM-M-(REFERRAL_STAFF_DEPT:12 flag=0005) | |--SYM-M-(REFERRAL_STAFF_MANAGER:13 flag=0005) | |--SYM-M-(STATUS_KEY_WORK:14 flag=0005) | |--SYM-M-(TEAM_KEY_OWNER_LTST:15 flag=0005) | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_LTST:16 flag=0005) | |--SYM-M-(WORK_ITEM_TYPE_KEY:17 flag=0005) | |--SYM-M-(WORK_ITEM_SUB_TYPE_ID:18 flag=0005) | |--SYM-M-(WORK_ITEM_TYPE_ID:19 flag=0005) | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_FRST:20 flag=0005) | |--SYM-M-(TEAM_KEY_OWNER_FRST:21 flag=0005) | |--SYM-M-(CNT_ALL_QUOTES:22 flag=0005) | |--SYM-M-(CNT_CUR_UNITS_LTST:23 flag=0005) | |--SYM-M-(CNT_MAX_UNITS_LTST:24 flag=0005) | |--SYM-M-(CNT_REQ_UNITS_LTST:25 flag=0005) | |--SYM-M-(QUOTE_KEY_LTST:26 flag=0005) | |--SYM-M-(QUOTE_ID:27 flag=0005) | |--SYM-A-(QUOTE_TYPE_KEY:28 flag=0031) | |--SYM-M-(PROM_CODE_KEY_QUOTE:29 flag=0005) | |--SYM-M-(QUOTE_STARTED_DTTM:30 flag=0005) | |--SYM-M-(QUOTE_ACCEPTED_DTTM:31 flag=0005) | |--SYM-M-(QUOTE_PRICE_INC_TAX_LTST:32 flag=0005) | |--SYM-M-(QUOTE_ORIG_PRICE_LTST:33 flag=0005) | |--SYM-M-(QUOTE_PRICE_EXC_TAX_LTST:34 flag=0005) | |--SYM-M-(QUOTE_DISC_TOT_LTST:35 flag=0005) | |--SYM-A-(STATUS_KEY_QUOTE:36 flag=0031) | |--SYM-M-(NEXT_RENEWAL_DTTM:37 flag=0005) | |--SYM-M-(NOT_PROCEEDED_WITH_DTTM:38 flag=0005) | |--SYM-M-(QUOTE_EXPIRED_DTTM:39 flag=0005) | |--SYM-A-(REASON_ID_QUOTE:40 flag=0031) | |--SYM-A-(CNT_CUR_UNITS_FRST:41 flag=0031) | |--SYM-A-(CNT_MAX_UNITS_FRST:42 flag=0031) | |--SYM-A-(CNT_REQ_UNITS_FRST:43 flag=0031) | |--SYM-M-(WORK_VALID_FROM_DTTM:44 flag=0005) | |--SYM-A-(QUOTE_KEY_FRST:45 flag=0031) | |--SYM-A-(QUOTE_PRICE_INC_TAX_FRST:46 flag=0031) | |--SYM-A-(QUOTE_ORIG_PRICE_FRST:47 flag=0031) | |--SYM-A-(QUOTE_PRICE_EXC_TAX_FRST:48 flag=0031) | |--SYM-A-(QUOTE_DISC_TOT_FRST:49 flag=0031) | |--SYM-A-(BILLING_KEY:50 flag=0031) | |--SYM-A-(CONTRACT_KEY:51 flag=0031) | \-SYM-M-(QUOTE_VALID_FROM_DTTM:52 flag=0005) /-SORT---| 31 The SAS System 07:32 Wednesday, March 30, 2016 | | /-SYM-M-(WORK_ITEM_REFERENCE_KEY:1 flag=0005) | | /-OBJ----| | | | |--SYM-M-(CUSTOMER_ID:2 flag=0005) | | | |--SYM-M-(WORK_ITEM_ID:3 flag=0005) | | | |--SYM-M-(WORK_ITEM_KEY:4 flag=0005) | | | |--SYM-M-(INTERFACE_DTTM:5 flag=0005) | | | |--SYM-M-(WORK_COMPLETE_DTTM:6 flag=0005) | | | |--SYM-M-(WORK_CREATED_DTTM:7 flag=0005) | | | |--SYM-M-(PROM_CODE_KEY_SOURCE:8 flag=0005) | | | |--SYM-M-(REASON_ID_WORK:9 flag=0005) | | | |--SYM-M-(REFERRAL_STAFF_NAME:10 flag=0005) | | | |--SYM-M-(REFERRAL_STAFF_NO:11 flag=0005) | | | |--SYM-M-(REFERRAL_STAFF_DEPT:12 flag=0005) | | | |--SYM-M-(REFERRAL_STAFF_MANAGER:13 flag=0005) | | | |--SYM-M-(STATUS_KEY_WORK:14 flag=0005) | | | |--SYM-M-(TEAM_KEY_OWNER_LTST:15 flag=0005) | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_LTST:16 flag=0005) | | | |--SYM-M-(WORK_ITEM_TYPE_KEY:17 flag=0005) | | | |--SYM-M-(WORK_ITEM_SUB_TYPE_ID:18 flag=0005) | | | |--SYM-M-(WORK_ITEM_TYPE_ID:19 flag=0005) | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_FRST:20 flag=0005) | | | |--SYM-M-(TEAM_KEY_OWNER_FRST:21 flag=0005) | | | |--SYM-M-(CNT_ALL_QUOTES:22 flag=0005) | | | |--SYM-M-(CNT_CUR_UNITS_LTST:23 flag=0005) | | | |--SYM-M-(CNT_MAX_UNITS_LTST:24 flag=0005) | | | |--SYM-M-(CNT_REQ_UNITS_LTST:25 flag=0005) | | | |--SYM-M-(QUOTE_KEY_LTST:26 flag=0005) | | | |--SYM-M-(QUOTE_ID:27 flag=0005) | | | |--SYM-A-(QUOTE_TYPE_KEY:28 flag=0031) | | | |--SYM-M-(PROM_CODE_KEY_QUOTE:29 flag=0005) | | | |--SYM-M-(QUOTE_STARTED_DTTM:30 flag=0005) | | | |--SYM-M-(QUOTE_ACCEPTED_DTTM:31 flag=0005) | | | |--SYM-M-(QUOTE_PRICE_INC_TAX_LTST:32 flag=0005) | | | |--SYM-M-(QUOTE_ORIG_PRICE_LTST:33 flag=0005) | | | |--SYM-M-(QUOTE_PRICE_EXC_TAX_LTST:34 flag=0005) | | | |--SYM-M-(QUOTE_DISC_TOT_LTST:35 flag=0005) | | | |--SYM-A-(STATUS_KEY_QUOTE:36 flag=0031) | | | |--SYM-M-(NEXT_RENEWAL_DTTM:37 flag=0005) | | | |--SYM-M-(NOT_PROCEEDED_WITH_DTTM:38 flag=0005) | | | |--SYM-M-(QUOTE_EXPIRED_DTTM:39 flag=0005) | | | |--SYM-A-(REASON_ID_QUOTE:40 flag=0031) | | | |--SYM-A-(CNT_CUR_UNITS_FRST:41 flag=0031) | | | |--SYM-A-(CNT_MAX_UNITS_FRST:42 flag=0031) | | | |--SYM-A-(CNT_REQ_UNITS_FRST:43 flag=0031) | | | |--SYM-M-(WORK_VALID_FROM_DTTM:44 flag=0005) | | | |--SYM-A-(QUOTE_KEY_FRST:45 flag=0031) | | | |--SYM-A-(QUOTE_PRICE_INC_TAX_FRST:46 flag=0031) | | | |--SYM-A-(QUOTE_ORIG_PRICE_FRST:47 flag=0031) | | | |--SYM-A-(QUOTE_PRICE_EXC_TAX_FRST:48 flag=0031) | | | |--SYM-A-(QUOTE_DISC_TOT_FRST:49 flag=0031) | | | |--SYM-A-(BILLING_KEY:50 flag=0031) | | | |--SYM-A-(CONTRACT_KEY:51 flag=0031) | | | \-SYM-M-(QUOTE_VALID_FROM_DTTM:52 flag=0005) | |--FIL----| | | | /-SYM-M-(WORK_ITEM_REFERENCE_KEY:1 flag=0001) | | | /-OBJ----| | | | | |--SYM-M-(CUSTOMER_ID:2 flag=0001) | | | | |--SYM-M-(WORK_ITEM_ID:3 flag=0001) 32 The SAS System 07:32 Wednesday, March 30, 2016 | | | | |--SYM-M-(WORK_ITEM_KEY:4 flag=0001) | | | | |--SYM-M-(INTERFACE_DTTM:5 flag=0001) | | | | |--SYM-M-(WORK_COMPLETE_DTTM:6 flag=0001) | | | | |--SYM-M-(WORK_CREATED_DTTM:7 flag=0001) | | | | |--SYM-M-(PROM_CODE_KEY_SOURCE:8 flag=0001) | | | | |--SYM-M-(REASON_ID_WORK:9 flag=0001) | | | | |--SYM-M-(REFERRAL_STAFF_NAME:10 flag=0001) | | | | |--SYM-M-(REFERRAL_STAFF_NO:11 flag=0001) | | | | |--SYM-M-(REFERRAL_STAFF_DEPT:12 flag=0001) | | | | |--SYM-M-(REFERRAL_STAFF_MANAGER:13 flag=0001) | | | | |--SYM-M-(STATUS_KEY_WORK:14 flag=0001) | | | | |--SYM-M-(TEAM_KEY_OWNER_LTST:15 flag=0001) | | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_LTST:16 flag=0001) | | | | |--SYM-M-(WORK_ITEM_TYPE_KEY:17 flag=0001) | | | | |--SYM-M-(WORK_ITEM_SUB_TYPE_ID:18 flag=0001) | | | | |--SYM-M-(WORK_ITEM_TYPE_ID:19 flag=0001) | | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_FRST:20 flag=0001) | | | | |--SYM-M-(TEAM_KEY_OWNER_FRST:21 flag=0001) | | | | |--SYM-M-(CNT_ALL_QUOTES:22 flag=0001) | | | | |--SYM-M-(CNT_CUR_UNITS_LTST:23 flag=0001) | | | | |--SYM-M-(CNT_MAX_UNITS_LTST:24 flag=0001) | | | | |--SYM-M-(CNT_REQ_UNITS_LTST:25 flag=0001) | | | | |--SYM-M-(QUOTE_KEY_LTST:26 flag=0001) | | | | |--SYM-M-(QUOTE_ID:27 flag=0001) | | | | |--SYM-M-(PROM_CODE_KEY_QUOTE:29 flag=0001) | | | | |--SYM-M-(QUOTE_STARTED_DTTM:30 flag=0001) | | | | |--SYM-M-(QUOTE_ACCEPTED_DTTM:31 flag=0001) | | | | |--SYM-M-(QUOTE_PRICE_INC_TAX_LTST:32 flag=0001) | | | | |--SYM-M-(QUOTE_ORIG_PRICE_LTST:33 flag=0001) | | | | |--SYM-M-(QUOTE_PRICE_EXC_TAX_LTST:34 flag=0001) | | | | |--SYM-M-(QUOTE_DISC_TOT_LTST:35 flag=0001) | | | | |--SYM-M-(NEXT_RENEWAL_DTTM:37 flag=0001) | | | | |--SYM-M-(NOT_PROCEEDED_WITH_DTTM:38 flag=0001) | | | | |--SYM-M-(QUOTE_EXPIRED_DTTM:39 flag=0001) | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS:3 flag=0001) | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS:5 flag=0001) | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS:7 flag=0001) | | | | |--SYM-M-(WORK_VALID_FROM_DTTM:44 flag=0001) | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_ID:6 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.QUOTE_KEY:1 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX:10 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITH_TAX:22 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX:23 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.DISCOUNT_TOTAL:4 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID:42 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.BILLING_KEY:43 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.CONTRACT_PROFILE_ID:44 flag=0001) | | | | |--SYM-V-(ETL_QUOTE.CONTRACT_KEY:45 flag=0001) | | | | \-SYM-M-(QUOTE_VALID_FROM_DTTM:52 flag=0001) | | |--OTRJ---| | | | | /-SYM-M-(WORK_ITEM_REFERENCE_KEY:1 flag=0001) | | | | /-OBJ----| | | | | | |--SYM-M-(CUSTOMER_ID:2 flag=0001) | | | | | |--SYM-M-(WORK_ITEM_ID:3 flag=0001) | | | | | |--SYM-M-(WORK_ITEM_KEY:4 flag=0001) | | | | | |--SYM-M-(INTERFACE_DTTM:5 flag=0001) | | | | | |--SYM-M-(WORK_COMPLETE_DTTM:6 flag=0001) | | | | | |--SYM-M-(WORK_CREATED_DTTM:7 flag=0001) 33 The SAS System 07:32 Wednesday, March 30, 2016 | | | | | |--SYM-M-(PROM_CODE_KEY_SOURCE:8 flag=0001) | | | | | |--SYM-M-(REASON_ID_WORK:9 flag=0001) | | | | | |--SYM-M-(REFERRAL_STAFF_NAME:10 flag=0001) | | | | | |--SYM-M-(REFERRAL_STAFF_NO:11 flag=0001) | | | | | |--SYM-M-(REFERRAL_STAFF_DEPT:12 flag=0001) | | | | | |--SYM-M-(REFERRAL_STAFF_MANAGER:13 flag=0001) | | | | | |--SYM-M-(STATUS_KEY_WORK:14 flag=0001) | | | | | |--SYM-M-(TEAM_KEY_OWNER_LTST:15 flag=0001) | | | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_LTST:16 flag=0001) | | | | | |--SYM-M-(WORK_ITEM_TYPE_KEY:17 flag=0001) | | | | | |--SYM-M-(WORK_ITEM_SUB_TYPE_ID:18 flag=0001) | | | | | |--SYM-M-(WORK_ITEM_TYPE_ID:19 flag=0001) | | | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_FRST:20 flag=0001) | | | | | |--SYM-M-(TEAM_KEY_OWNER_FRST:21 flag=0001) | | | | | |--SYM-M-(CNT_ALL_QUOTES:22 flag=0001) | | | | | |--SYM-M-(CNT_CUR_UNITS_LTST:23 flag=0001) | | | | | |--SYM-M-(CNT_MAX_UNITS_LTST:24 flag=0001) | | | | | |--SYM-M-(CNT_REQ_UNITS_LTST:25 flag=0001) | | | | | |--SYM-M-(QUOTE_KEY_LTST:26 flag=0001) | | | | | |--SYM-M-(QUOTE_ID:27 flag=0001) | | | | | |--SYM-M-(PROM_CODE_KEY_QUOTE:29 flag=0001) | | | | | |--SYM-M-(QUOTE_STARTED_DTTM:30 flag=0001) | | | | | |--SYM-M-(QUOTE_ACCEPTED_DTTM:31 flag=0001) | | | | | |--SYM-M-(QUOTE_PRICE_INC_TAX_LTST:32 flag=0001) | | | | | |--SYM-M-(QUOTE_ORIG_PRICE_LTST:33 flag=0001) | | | | | |--SYM-M-(QUOTE_PRICE_EXC_TAX_LTST:34 flag=0001) | | | | | |--SYM-M-(QUOTE_DISC_TOT_LTST:35 flag=0001) | | | | | |--SYM-M-(NEXT_RENEWAL_DTTM:37 flag=0001) | | | | | |--SYM-M-(NOT_PROCEEDED_WITH_DTTM:38 flag=0001) | | | | | |--SYM-M-(QUOTE_EXPIRED_DTTM:39 flag=0001) | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS:3 flag= 0001) | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS:5 flag= 0001) | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS:7 flag= 0001) | | | | | |--SYM-M-(WORK_VALID_FROM_DTTM:44 flag=0001) | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_ID:6 flag=0001) | | | | | \-SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_KEY:8 flag=0001) | | | | /-OTRJ---| | | | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS:3 flag=0001) | | | | | | /-OBJ----| | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS:5 flag=0001) | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS:7 flag=0001) | | | | | | | |--SYM-M-(WORK_VALID_FROM_DTTM:44 flag= 0001) | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_ID:6 flag=0001) | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_KEY:8 flag=0001) | | | | | | | \-SYM-V-(ETL_WORK_ITEM_NUM.WORK_ITEM_ID:9 flag=0001) | | | | | | /-SORT---| | | | | | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS:3 flag=0001) 34 The SAS System 07:32 Wednesday, March 30, 2016 | | | | | | | | /-OBJ----| | | | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS:5 flag=0001) | | | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS:7 flag=0001) | | | | | | | | | |--SYM-M-(WORK_VALID_FROM_DTTM: 44 flag=0001) | | | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_ID:6 flag=0001) | | | | | | | | | |--SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_KEY:8 flag=0001) | | | | | | | | | \-SYM-V-(ETL_WORK_ITEM_NUM.WORK_ITEM_ID:9 flag=0001) | | | | | | | |--SRC----| | | | | | | | | \-TABL[BCW_DTL].ETL_WORK_ITEM_NUM opt='' | | | | | | | |--empty- | | | | | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.WORK_ITEM_ID:9) | | | | | | | | /-ASC----| | | | | | | | \-ORDR---| | | | | | |--FROM---| | | | | | | | /-SYM-M-(WORK_ITEM_REFERENCE_KEY:1 flag= 0001) | | | | | | | /-OBJ----| | | | | | | | | |--SYM-M-(CUSTOMER_ID:2 flag=0001) | | | | | | | | |--SYM-M-(WORK_ITEM_ID:3 flag=0001) | | | | | | | | |--SYM-M-(WORK_ITEM_KEY:4 flag=0001) | | | | | | | | |--SYM-M-(INTERFACE_DTTM:5 flag=0001) | | | | | | | | |--SYM-M-(WORK_COMPLETE_DTTM:6 flag=0001) | | | | | | | | |--SYM-M-(WORK_CREATED_DTTM:7 flag=0001) | | | | | | | | |--SYM-M-(PROM_CODE_KEY_SOURCE:8 flag=0001) | | | | | | | | |--SYM-M-(REASON_ID_WORK:9 flag=0001) | | | | | | | | |--SYM-M-(REFERRAL_STAFF_NAME:10 flag=0001) | | | | | | | | |--SYM-M-(REFERRAL_STAFF_NO:11 flag=0001) | | | | | | | | |--SYM-M-(REFERRAL_STAFF_DEPT:12 flag=0001) | | | | | | | | |--SYM-M-(REFERRAL_STAFF_MANAGER:13 flag= 0001) | | | | | | | | |--SYM-M-(STATUS_KEY_WORK:14 flag=0001) | | | | | | | | |--SYM-M-(TEAM_KEY_OWNER_LTST:15 flag=0001) | | | | | | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_LTST:16 flag=0001) | | | | | | | | |--SYM-M-(WORK_ITEM_TYPE_KEY:17 flag=0001) | | | | | | | | |--SYM-M-(WORK_ITEM_SUB_TYPE_ID:18 flag= 0001) | | | | | | | | |--SYM-M-(WORK_ITEM_TYPE_ID:19 flag=0001) | | | | | | | | |--SYM-M-(TEAM_MEMBER_KEY_OWNER_FRST:20 flag=0001) | | | | | | | | |--SYM-M-(TEAM_KEY_OWNER_FRST:21 flag=0001) | | | | | | | | |--SYM-M-(CNT_ALL_QUOTES:22 flag=0001) | | | | | | | | |--SYM-M-(CNT_CUR_UNITS_LTST:23 flag=0001) | | | | | | | | |--SYM-M-(CNT_MAX_UNITS_LTST:24 flag=0001) | | | | | | | | |--SYM-M-(CNT_REQ_UNITS_LTST:25 flag=0001) 35 The SAS System 07:32 Wednesday, March 30, 2016 | | | | | | | | |--SYM-M-(QUOTE_KEY_LTST:26 flag=0001) | | | | | | | | |--SYM-M-(QUOTE_ID:27 flag=0001) | | | | | | | | |--SYM-M-(PROM_CODE_KEY_QUOTE:29 flag=0001) | | | | | | | | |--SYM-M-(QUOTE_STARTED_DTTM:30 flag=0001) | | | | | | | | |--SYM-M-(QUOTE_ACCEPTED_DTTM:31 flag=0001) | | | | | | | | |--SYM-M-(QUOTE_PRICE_INC_TAX_LTST:32 flag=0001) | | | | | | | | |--SYM-M-(QUOTE_ORIG_PRICE_LTST:33 flag= 0001) | | | | | | | | |--SYM-M-(QUOTE_PRICE_EXC_TAX_LTST:34 flag=0001) | | | | | | | | |--SYM-M-(QUOTE_DISC_TOT_LTST:35 flag=0001) | | | | | | | | |--SYM-M-(NEXT_RENEWAL_DTTM:37 flag=0001) | | | | | | | | |--SYM-M-(NOT_PROCEEDED_WITH_DTTM:38 flag= 0001) | | | | | | | | \-SYM-M-(QUOTE_EXPIRED_DTTM:39 flag=0001) | | | | | | \-SRC----| | | | | | | \-TABL[WORK].W667SN06_RULE4_3 opt='' | | | | | |--empty- | | | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.WORK_ITEM_ID:9) | | | | | |--CEQ----| | | | | | | \-SYM-V-(W667SN06_RULE4_3.WORK_ITEM_ID:3) | | | | | \-JTAG(jds=1, tagfrom=0001, flags=0) | | | |--FROM---| | | | | | /-SYM-V-(ETL_QUOTE.QUOTE_KEY:1 flag=0001) | | | | | /-OBJ----| | | | | | | |--SYM-V-(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX:10 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITH_TAX:22 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX:23 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.DISCOUNT_TOTAL:4 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID:42 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.BILLING_KEY:43 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.CONTRACT_PROFILE_ID:44 flag=0001) | | | | | | |--SYM-V-(ETL_QUOTE.CONTRACT_KEY:45 flag=0001) | | | | | | \-SYM-M-(QUOTE_VALID_FROM_DTTM:52 flag=0001) | | | | \-SRC----| | | | | \-TABL[BCW_DTL].ETL_QUOTE opt='' | | | |--empty- | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_KEY:8) | | | |--CEQ----| | | | | \-SYM-V-(ETL_QUOTE.QUOTE_KEY:1) | | | \-JTAG(jds=1, tagfrom=0003, flags=0) | | |--empty- | | |--empty- | | |--empty- | | |--empty- | | | /-SYM-A-(QUOTE_TYPE_KEY:28 flag=0031) | | | /-ASGN---| | | | | \-LITN(.) | | \-OBJE---| | | | /-SYM-A-(STATUS_KEY_QUOTE:36 flag=0031) | | |--ASGN---| | | | \-LITN(.) | | | /-SYM-A-(REASON_ID_QUOTE:40 flag=0031) | | |--ASGN---| 36 The SAS System 07:32 Wednesday, March 30, 2016 | | | \-LITN(.) | | | /-SYM-A-(CNT_CUR_UNITS_FRST:41 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:1) | | | \-FLST---| | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS:3) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_WORK_ITEM_NUM.CURRENT_NO_OF_UNITS:3) | | | /-SYM-A-(CNT_MAX_UNITS_FRST:42 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:2) | | | \-FLST---| | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS:5) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_WORK_ITEM_NUM.MAXIMUM_NO_OF_UNITS:5) | | | /-SYM-A-(CNT_REQ_UNITS_FRST:43 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:3) | | | \-FLST---| | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS:7) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_WORK_ITEM_NUM.REQUIRED_NO_OF_UNITS:7) | | | /-SYM-A-(QUOTE_KEY_FRST:45 flag=0031) | | |--ASGN---| | | | | /-empty- | | | \-CASE---| | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_ID:6) | | | | /-ISNL---| | | | | /-LAND---| | | | | | | /-SYM-V-(ETL_QUOTE.QUOTE_KEY:1) | | | | | \-ISNL---| | | | |--WHEN---| | | | | \-LITN(0) | | | | /-SYM-V-(ETL_WORK_ITEM_NUM.QUOTE_ID:6) | | | | /-NTNL---| | | | | /-LAND---| | | | | | | /-SYM-V-(ETL_QUOTE.QUOTE_KEY:1) | | | | | \-ISNL---| | | | |--WHEN---| | | | | \-LITN(-1) | | | | /-empty- | | | \-WHEN---| | | | \-SYM-V-(ETL_QUOTE.QUOTE_KEY:1) | | | /-SYM-A-(QUOTE_PRICE_INC_TAX_FRST:46 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:4) | | | \-FLST---| 37 The SAS System 07:32 Wednesday, March 30, 2016 | | | | /-SYM-V-(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX:10) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_QUOTE.ORIGINAL_PRICE_WITH_TAX:10) | | | /-SYM-A-(QUOTE_ORIG_PRICE_FRST:47 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:5) | | | \-FLST---| | | | | /-SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITH_TAX:22) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITH_TAX:22) | | | /-SYM-A-(QUOTE_PRICE_EXC_TAX_FRST:48 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:6) | | | \-FLST---| | | | | /-SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX:23) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_QUOTE.QUOTE_PRICE_WITHOUT_TAX:23) | | | /-SYM-A-(QUOTE_DISC_TOT_FRST:49 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:7) | | | \-FLST---| | | | | /-SYM-V-(ETL_QUOTE.DISCOUNT_TOTAL:4) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | \-SYM-V-(ETL_QUOTE.DISCOUNT_TOTAL:4) | | | /-SYM-A-(BILLING_KEY:50 flag=0031) | | |--ASGN---| | | | | /-SYM-F-(IFN:9) | | | \-FLST---| | | | | /-SYM-V-(ETL_QUOTE.RENEWAL_BILLING_PROFILE_ID:42) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(0) | | | | /-SYM-F-(IFN:8) | | | |--FLST---| | | | | | /-SYM-V-(ETL_QUOTE.BILLING_KEY:43) | | | | | /-ISNL---| | | | | |--LTON---| | | | | |--LITN(-1) | | | | \-SYM-V-(ETL_QUOTE.BILLING_KEY:43) | | | \-SYM-V-(ETL_QUOTE.BILLING_KEY:43) | | | /-SYM-A-(CONTRACT_KEY:51 flag=0031) | | \-ASGN---| | | | /-SYM-F-(IFN:11) | | \-FLST---| | | | /-SYM-V-(ETL_QUOTE.CONTRACT_PROFILE_ID:44) | | | /-ISNL---| | | |--LTON---| | | |--LITN(0) | | | /-SYM-F-(IFN:10) | | |--FLST---| 38 The SAS System 07:32 Wednesday, March 30, 2016 | | | | /-SYM-V-(ETL_QUOTE.CONTRACT_KEY:45) | | | | /-ISNL---| | | | |--LTON---| | | | |--LITN(-1) | | | \-SYM-V-(ETL_QUOTE.CONTRACT_KEY:45) | | \-SYM-V-(ETL_QUOTE.CONTRACT_KEY:45) | |--empty- | | /-SYM-V-(W667SN06_RULE4_3.WORK_ITEM_ID:3) | | /-ASC----| | \-ORDR---| | | /-SYM-V-(W667SN06_RULE4_3.QUOTE_STARTED_DTTM:30) | |--ASC----| | | /-SYM-V-(W667SN06_RULE4_3.QUOTE_ID:28) | |--ASC----| | | /-SYM-V-(ETL_QUOTE.VALID_FROM_DTTM:67) | |--ASC----| | | /-SYM-V-(ETL_WORK_ITEM_NUM.VALID_FROM_DTTM:11) | \-ASC----| --SSEL---| NOTE: Table WORK.W667TARG_RULE5_1 created, with 4 rows and 52 columns. 1029 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.07 seconds cpu time 0.06 seconds 1030 1031 %global etls_sql_pushDown; 1032 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 1033 1034 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 1035 1036 1037 1038 /** Step end Get earliest version of quote **/ 1039 1040 /*==========================================================================* 1041 * Step: Dedupe keeping only required earliest A5VECZJZ.BJ0000TX * 1042 * quote record * 1043 * Transform: Sort * 1044 * Description: * 1045 * * 1046 * Source Table: SQL Target - work.W667TARG_RULE5_1 A5VECZJZ.BK0000JI * 1047 * Target Table: Sort Target - work.W667TSCQ_RULE5_2 A5VECZJZ.BK0000JJ * 39 The SAS System 07:32 Wednesday, March 30, 2016 1048 *==========================================================================*/ 1049 1050 %let transformID = %quote(A5VECZJZ.BJ0000TX); 1051 %let trans_rc = 0; 1052 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1053 1054 %let SYSLAST = %nrquote(work.W667TARG_RULE5_1); 1055 1056 proc datasets lib = work nolist nowarn memtype = (data view); 1057 delete W667TSCQ_RULE5_2; 1058 quit; NOTE: Deleting WORK.W667TSCQ_RULE5_2 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1059 1060 proc sort data = &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W667TARG_RULE5_1 1061 out = work.W667TSCQ_RULE5_2 1062 NODUPKEY ; 1063 by 1064 WORK_ITEM_ID 1065 ; 1066 run; NOTE: There were 4 observations read from the data set WORK.W667TARG_RULE5_1. NOTE: 2 observations with duplicate key values were deleted. NOTE: The data set WORK.W667TSCQ_RULE5_2 has 2 observations and 52 variables. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1067 1068 %rcSet(&syserr); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SYSERR resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 1069 1070 1071 1072 /** Step end Dedupe keeping only required earliest quote record **/ 1073 1074 /*==========================================================================* 1075 * Step: Lookup dimension keys A5VECZJZ.BJ0000TY * 1076 * Transform: Join * 1077 * Description: * 1078 * * 40 The SAS System 07:32 Wednesday, March 30, 2016 1079 * Source Tables: DEF_REASON - bcw_dtl.DEF_REASON A5VECZJZ.BQ00008J * 1080 * RIM_DATE - bcw_mart.RIM_DATE A5VECZJZ.BQ00009B * 1081 * REF_WORK_ITEM_SUB_TYPE - A5VECZJZ.BQ00008W * 1082 * bcw_dtl.REF_WORK_ITEM_SUB_TYPE * 1083 * REF_CUSTOMER - bcw_dtl.REF_CUSTOMER A5VECZJZ.BQ00007X * 1084 * Sort Target - work.W667TSCQ_RULE5_2 A5VECZJZ.BK0000JJ * 1085 * Target Table: SQL Target - work.W66880IZ_rule6_4 A5VECZJZ.BK0000JK * 1086 * * 1087 * Warnings: * 1088 * Mapped source columns do not match columns used in expression. * 1089 * Mapped source columns do not match columns used in expression. * 1090 * Mapped source columns do not match columns used in expression. * 1091 * Mapped source columns do not match columns used in expression. * 1092 * Mapped source columns do not match columns used in expression. * 1093 *==========================================================================*/ 1094 1095 %let transformID = %quote(A5VECZJZ.BJ0000TY); 1096 %let trans_rc = 0; 1097 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1098 1099 %global etls_sql_pushDown; 1100 %let etls_sql_pushDown = -1; 1101 option DBIDIRECTEXEC; 1102 1103 proc datasets lib = work nolist nowarn memtype = (data view); 1104 delete W66880IZ_rule6_4; 1105 quit; NOTE: Deleting WORK.W66880IZ_RULE6_4 (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 1106 1107 data _null_; 1108 put "NOTE: Mapped source columns do not match columns used in expression."; 1109 run; NOTE: Mapped source columns do not match columns used in expression. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 1110 1111 proc sql; 1112 create table work.W66880IZ_rule6_4 as 1113 select 1114 W667TSCQ_RULE5_2.CNT_ALL_QUOTES length = 8 1115 format = 12. 1116 informat = 12., 1117 W667TSCQ_RULE5_2.CNT_CUR_UNITS_FRST length = 8 1118 format = 12. 1119 informat = 12., 1120 W667TSCQ_RULE5_2.CNT_CUR_UNITS_LTST length = 8 1121 format = 12. 1122 informat = 12., 41 The SAS System 07:32 Wednesday, March 30, 2016 1123 W667TSCQ_RULE5_2.CNT_MAX_UNITS_FRST length = 8 1124 format = 12. 1125 informat = 12., 1126 W667TSCQ_RULE5_2.CNT_MAX_UNITS_LTST length = 8 1127 format = 12. 1128 informat = 12., 1129 W667TSCQ_RULE5_2.CNT_REQ_UNITS_FRST length = 8 1130 format = 11. 1131 informat = 11., 1132 W667TSCQ_RULE5_2.CNT_REQ_UNITS_LTST length = 8 1133 format = 11. 1134 informat = 11., 1135 W667TSCQ_RULE5_2.CUSTOMER_ID length = 8 1136 format = 12. 1137 informat = 12., 1138 case 1139 when W667TSCQ_RULE5_2.CUSTOMER_ID IS NULL 1140 then 0 1141 when W667TSCQ_RULE5_2.CUSTOMER_ID IS NOT NULL AND REF_CUSTOMER.CUSTOMER_KEY IS NULL 1142 then -1 1143 else REF_CUSTOMER.CUSTOMER_KEY 1144 end as CUSTOMER_KEY length = 8 1145 format = 11. 1146 informat = 11., 1147 case 1148 when W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM IS NULL 1149 then 0 1150 when W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM is not null and RIM_DATE.DATE_KEY is null 1151 then -1 1152 else RIM_DATE.DATE_KEY 1153 end as DATE_KEY_QUOTE_ACCEPTED length = 8 1154 format = 11. 1155 informat = 11., 1156 case 1157 when W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM IS NULL 1158 then 0 1159 when W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM is not null and RIM_DATE.DATE_KEY is null 1160 then -1 1161 else RIM_DATE.DATE_KEY 1162 end as DATE_KEY_QUOTE_EXPIRED length = 8 1163 format = 11. 1164 informat = 11., 1165 case 1166 when W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM IS NULL 1167 then 0 1168 when W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM is not null and RIM_DATE.DATE_KEY is null 1169 then -1 1170 else RIM_DATE.DATE_KEY 1171 end as DATE_KEY_QUOTE_NOT_PROCEED length = 8 1172 format = 11. 1173 informat = 11., 1174 case 1175 when W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM IS NULL 1176 then 0 1177 when W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM is not null and RIM_DATE.DATE_KEY is null 1178 then -1 1179 else RIM_DATE.DATE_KEY 1180 end as DATE_KEY_QUOTE_STARTED length = 8 42 The SAS System 07:32 Wednesday, March 30, 2016 1181 format = 11. 1182 informat = 11., 1183 case 1184 when W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM IS NULL 1185 then 0 1186 when W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM is not null and RIM_DATE.DATE_KEY is null 1187 then -1 1188 else RIM_DATE.DATE_KEY 1189 end as DATE_KEY_WORK_COMPLETE length = 8 1190 format = 11. 1191 informat = 11., 1192 case 1193 when W667TSCQ_RULE5_2.WORK_CREATED_DTTM IS NULL 1194 then 0 1195 when W667TSCQ_RULE5_2.WORK_CREATED_DTTM is not null and RIM_DATE.DATE_KEY is null 1196 then -1 1197 else RIM_DATE.DATE_KEY 1198 end as DATE_KEY_WORK_CREATED length = 8 1199 format = 11. 1200 informat = 11., 1201 W667TSCQ_RULE5_2.INTERFACE_DTTM length = 8 1202 format = DATETIME20. 1203 informat = DATETIME20., 1204 W667TSCQ_RULE5_2.NEXT_RENEWAL_DTTM length = 8 1205 format = DATETIME20. 1206 informat = DATETIME20., 1207 W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM length = 8 1208 format = DATETIME20. 1209 informat = DATETIME20., 1210 W667TSCQ_RULE5_2.PROM_CODE_KEY_QUOTE length = 8 1211 format = 11. 1212 informat = 11., 1213 W667TSCQ_RULE5_2.PROM_CODE_KEY_SOURCE length = 8 1214 format = 11. 1215 informat = 11., 1216 W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM length = 8 1217 format = DATETIME20. 1218 informat = DATETIME20., 1219 W667TSCQ_RULE5_2.QUOTE_DISC_TOT_FRST length = 8 1220 format = 21.4 1221 informat = 21.4, 1222 W667TSCQ_RULE5_2.QUOTE_DISC_TOT_LTST length = 8 1223 format = 21.4 1224 informat = 21.4, 1225 W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM length = 8 1226 format = DATETIME20. 1227 informat = DATETIME20., 1228 W667TSCQ_RULE5_2.BILLING_KEY length = 8 1229 label = 'BILLING_KEY', 1230 W667TSCQ_RULE5_2.CONTRACT_KEY length = 8 1231 label = 'CONTRACT_KEY', 1232 W667TSCQ_RULE5_2.QUOTE_ID length = 8 1233 format = 11. 1234 informat = 11., 1235 W667TSCQ_RULE5_2.QUOTE_KEY_FRST length = 8 1236 format = 11. 1237 informat = 11., 1238 W667TSCQ_RULE5_2.QUOTE_KEY_LTST length = 8 43 The SAS System 07:32 Wednesday, March 30, 2016 1239 format = 11. 1240 informat = 11., 1241 W667TSCQ_RULE5_2.QUOTE_ORIG_PRICE_FRST length = 8 1242 format = 21.4 1243 informat = 21.4, 1244 W667TSCQ_RULE5_2.QUOTE_ORIG_PRICE_LTST length = 8 1245 format = 21.4 1246 informat = 21.4, 1247 W667TSCQ_RULE5_2.QUOTE_PRICE_EXC_TAX_FRST length = 8 1248 format = 21.4 1249 informat = 21.4, 1250 W667TSCQ_RULE5_2.QUOTE_PRICE_EXC_TAX_LTST length = 8 1251 format = 21.4 1252 informat = 21.4, 1253 W667TSCQ_RULE5_2.QUOTE_PRICE_INC_TAX_FRST length = 8 1254 format = 21.4 1255 informat = 21.4, 1256 W667TSCQ_RULE5_2.QUOTE_PRICE_INC_TAX_LTST length = 8 1257 format = 21.4 1258 informat = 21.4, 1259 W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM length = 8 1260 format = DATETIME20. 1261 informat = DATETIME20., 1262 W667TSCQ_RULE5_2.QUOTE_VALID_FROM_DTTM length = 8 1263 format = DATETIME20. 1264 informat = DATETIME20., 1265 W667TSCQ_RULE5_2.REASON_ID_QUOTE length = 8, 1266 W667TSCQ_RULE5_2.REASON_ID_WORK length = 8 1267 format = 11. 1268 informat = 11., 1269 case 1270 when W667TSCQ_RULE5_2.REASON_ID_WORK IS NULL 1271 then 0 1272 when W667TSCQ_RULE5_2.REASON_ID_WORK IS NOT NULL AND DEF_REASON_WORK.REASON_KEY is null 1273 then -1 1274 else DEF_REASON_WORK.REASON_KEY 1275 end as REASON_KEY_WORK length = 8 1276 format = 11. 1277 informat = 11., 1278 W667TSCQ_RULE5_2.REFERRAL_STAFF_DEPT length = 50 1279 format = $50. 1280 informat = $50., 1281 W667TSCQ_RULE5_2.REFERRAL_STAFF_MANAGER length = 50 1282 format = $50. 1283 informat = $50., 1284 W667TSCQ_RULE5_2.REFERRAL_STAFF_NAME length = 50 1285 format = $50. 1286 informat = $50., 1287 W667TSCQ_RULE5_2.REFERRAL_STAFF_NO length = 10 1288 format = $10. 1289 informat = $10., 1290 W667TSCQ_RULE5_2.STATUS_KEY_WORK length = 8 1291 format = 11. 1292 informat = 11., 1293 W667TSCQ_RULE5_2.TEAM_KEY_OWNER_FRST length = 8 1294 format = 11. 1295 informat = 11., 1296 W667TSCQ_RULE5_2.TEAM_KEY_OWNER_LTST length = 8 44 The SAS System 07:32 Wednesday, March 30, 2016 1297 format = 11. 1298 informat = 11., 1299 W667TSCQ_RULE5_2.TEAM_MEMBER_KEY_OWNER_FRST length = 8 1300 format = 11. 1301 informat = 11., 1302 W667TSCQ_RULE5_2.TEAM_MEMBER_KEY_OWNER_LTST length = 8 1303 format = 11. 1304 informat = 11., 1305 W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM length = 8 1306 format = DATETIME20. 1307 informat = DATETIME20., 1308 W667TSCQ_RULE5_2.WORK_CREATED_DTTM length = 8 1309 format = DATETIME20. 1310 informat = DATETIME20., 1311 W667TSCQ_RULE5_2.WORK_ITEM_ID length = 8 1312 format = 12. 1313 informat = 12., 1314 W667TSCQ_RULE5_2.WORK_ITEM_KEY length = 8 1315 format = 12. 1316 informat = 12., 1317 W667TSCQ_RULE5_2.WORK_ITEM_REFERENCE_KEY length = 8 1318 format = 12. 1319 informat = 12., 1320 case 1321 when W667TSCQ_RULE5_2.WORK_ITEM_SUB_TYPE_ID IS NULL 1322 then 0 1323 when W667TSCQ_RULE5_2.WORK_ITEM_SUB_TYPE_ID IS NOT NULL AND REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_SUB_TYPE_KEY IS 1323 ! NULL 1324 then -1 1325 else REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_SUB_TYPE_KEY 1326 end as WORK_ITEM_SUB_TYPE_KEY length = 8, 1327 W667TSCQ_RULE5_2.WORK_ITEM_TYPE_KEY length = 8 1328 format = 11. 1329 informat = 11., 1330 W667TSCQ_RULE5_2.WORK_VALID_FROM_DTTM length = 8 1331 format = DATETIME20. 1332 informat = DATETIME20. 1333 from 1334 work.W667TSCQ_RULE5_2 left join 1335 bcw_dtl.DEF_REASON as DEF_REASON_WORK 1336 on 1337 ( 1338 W667TSCQ_RULE5_2.REASON_ID_WORK = DEF_REASON_WORK.REASON_ID 1339 and (W667TSCQ_RULE5_2.INTERFACE_DTTM BETWEEN DEF_REASON_WORK.VALID_FROM_DTTM 1340 and DEF_REASON_WORK.VALID_TO_DTTM ) 1341 ) left join 1342 bcw_mart.RIM_DATE 1343 on 1344 ( 1345 dhms(datepart(W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM ),0,0,0) = RIM_DATE.THE_DATE 1346 and W667TSCQ_RULE5_2.QUOTE_STARTED_DTTM NOT IS NULL 1347 ) left join 1348 bcw_mart.RIM_DATE as RIM_DATE_EXPIRED 1349 on 1350 ( 1351 dhms(datepart(W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM ),0,0,0) = RIM_DATE_EXPIRED.THE_DATE 1352 and W667TSCQ_RULE5_2.QUOTE_EXPIRED_DTTM NOT IS NULL 1353 ) left join 45 The SAS System 07:32 Wednesday, March 30, 2016 1354 bcw_mart.RIM_DATE as RIM_DATE_ACCEPTED 1355 on 1356 ( 1357 dhms(datepart(W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM) ,0,0,0) = RIM_DATE_ACCEPTED.THE_DATE 1358 and W667TSCQ_RULE5_2.QUOTE_ACCEPTED_DTTM NOT IS NULL 1359 ) left join 1360 bcw_mart.RIM_DATE as RIM_DATE_NOT_PROCEED 1361 on 1362 ( 1363 dhms(datepart(W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM ),0,0,0) = RIM_DATE_NOT_PROCEED.THE_DATE 1364 and W667TSCQ_RULE5_2.NOT_PROCEEDED_WITH_DTTM NOT IS NULL 1365 ) left join 1366 bcw_mart.RIM_DATE as RIM_DATE_WORK_CREATED 1367 on 1368 ( 1369 dhms(datepart(W667TSCQ_RULE5_2.WORK_CREATED_DTTM ),0,0,0) = RIM_DATE_WORK_CREATED.THE_DATE 1370 and W667TSCQ_RULE5_2.WORK_CREATED_DTTM NOT IS NULL 1371 ) left join 1372 bcw_mart.RIM_DATE as RIM_DATE_WORK_COMPLETED 1373 on 1374 ( 1375 dhms(datepart(W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM ),0,0,0) = RIM_DATE_WORK_COMPLETED.THE_DATE 1376 and W667TSCQ_RULE5_2.WORK_COMPLETE_DTTM NOT IS NULL 1377 ) left join 1378 bcw_dtl.REF_WORK_ITEM_SUB_TYPE 1379 on 1380 ( 1381 W667TSCQ_RULE5_2.WORK_ITEM_SUB_TYPE_ID = REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_SUB_TYPE_ID 1382 and W667TSCQ_RULE5_2.WORK_ITEM_TYPE_ID = REF_WORK_ITEM_SUB_TYPE.WORK_ITEM_TYPE_ID 1383 and (W667TSCQ_RULE5_2.INTERFACE_DTTM BETWEEN REF_WORK_ITEM_SUB_TYPE.VALID_FROM_DTTM 1384 and REF_WORK_ITEM_SUB_TYPE.VALID_TO_DTTM ) 1385 ) left join 1386 bcw_dtl.REF_CUSTOMER 1387 on 1388 ( 1389 W667TSCQ_RULE5_2.CUSTOMER_ID = REF_CUSTOMER.CUSTOMER_ID 1390 and (W667TSCQ_RULE5_2.INTERFACE_DTTM BETWEEN REF_CUSTOMER.VALID_FROM_DTTM 1391 and REF_CUSTOMER.VALID_TO_DTTM ) 1392 ) 1393 order by 1394 WORK_ITEM_ID 1395 ; NOTE: Invalid (or missing) arguments to the DATEPART function have caused the function to return a missing value. NOTE: Invalid (or missing) arguments to the DHMS function have caused the function to return a missing value. NOTE: Table WORK.W66880IZ_RULE6_4 created, with 2 rows and 57 columns. 1396 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.43 seconds cpu time 0.10 seconds 1397 1398 %global etls_sql_pushDown; 1399 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 1400 1401 %rcSet(&sqlrc); 46 The SAS System 07:32 Wednesday, March 30, 2016 MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 1402 1403 1404 1405 /** Step end Lookup dimension keys **/ 1406 1407 /*==========================================================================* 1408 * Step: Derive Measures A5VECZJZ.BJ0000TZ * 1409 * Transform: Extract * 1410 * Description: * 1411 * * 1412 * Source Table: SQL Target - work.W66880IZ_rule6_4 A5VECZJZ.BK0000JK * 1413 * Target Table: Extract Target - work.W668AOWI A5VECZJZ.BK0000JL * 1414 *==========================================================================*/ 1415 1416 %let transformID = %quote(A5VECZJZ.BJ0000TZ); 1417 %let trans_rc = 0; 1418 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1419 1420 %let SYSLAST = %nrquote(work.W66880IZ_rule6_4); 1421 1422 %global etls_sql_pushDown; 1423 %let etls_sql_pushDown = -1; 1424 option DBIDIRECTEXEC; 1425 1426 /*---- Map the columns ----*/ 1427 proc datasets lib = work nolist nowarn memtype = (data view); 1428 delete W668AOWI; 1429 quit; NOTE: Deleting WORK.W668AOWI (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 1430 1431 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1432 proc sql; 1433 create table work.W668AOWI as 1434 select 1435 CNT_ALL_QUOTES, 1436 CNT_CUR_UNITS_FRST, 1437 CNT_CUR_UNITS_LTST, 1438 CNT_MAX_UNITS_FRST, 1439 CNT_MAX_UNITS_LTST, 1440 CNT_REQ_UNITS_FRST, 1441 CNT_REQ_UNITS_LTST, 47 The SAS System 07:32 Wednesday, March 30, 2016 1442 (1) as CNT_WORK length = 8, 1443 CUSTOMER_KEY, 1444 DATE_KEY_QUOTE_ACCEPTED, 1445 DATE_KEY_QUOTE_EXPIRED, 1446 DATE_KEY_QUOTE_NOT_PROCEED, 1447 DATE_KEY_QUOTE_STARTED, 1448 DATE_KEY_WORK_COMPLETE, 1449 DATE_KEY_WORK_CREATED, 1450 (CNT_CUR_UNITS_LTST - CNT_CUR_UNITS_FRST) as DIFF_QUOTE_CUR_UNITS length = 8, 1451 (QUOTE_DISC_TOT_LTST - QUOTE_DISC_TOT_FRST) as DIFF_QUOTE_DISC_TOT length = 8, 1452 (CNT_MAX_UNITS_LTST - CNT_MAX_UNITS_FRST) as DIFF_QUOTE_MAX_UNITS length = 8, 1453 (QUOTE_ORIG_PRICE_LTST - QUOTE_ORIG_PRICE_FRST) as DIFF_QUOTE_ORIG_PRICE length = 8, 1454 (QUOTE_PRICE_EXC_TAX_LTST - QUOTE_PRICE_EXC_TAX_FRST) as DIFF_QUOTE_PRICE_EXC_TAX length = 8, 1455 (QUOTE_PRICE_INC_TAX_LTST - QUOTE_PRICE_INC_TAX_FRST) as DIFF_QUOTE_PRICE_INC_TAX length = 8, 1456 (CNT_REQ_UNITS_LTST - CNT_REQ_UNITS_FRST) as DIFF_QUOTE_REQ_UNITS length = 8, 1457 PROM_CODE_KEY_QUOTE, 1458 PROM_CODE_KEY_SOURCE, 1459 (CASE 1460 when QUOTE_ACCEPTED_DTTM is not null then (datepart(QUOTE_ACCEPTED_DTTM) -datepart(QUOTE_STARTED_DTTM)) 1461 when NOT_PROCEEDED_WITH_DTTM is not null then (datepart(NOT_PROCEEDED_WITH_DTTM) - 1461 ! datepart(QUOTE_STARTED_DTTM) ) 1462 when QUOTE_EXPIRED_DTTM is not null then (datepart(QUOTE_EXPIRED_DTTM) -datepart(QUOTE_STARTED_DTTM )) 1463 else 0 1464 END) as QUOTE_INIT_TO_QUOTE_COMP_DAYS length = 8, 1465 QUOTE_KEY_FRST, 1466 QUOTE_KEY_LTST, 1467 REASON_KEY_WORK, 1468 REFERRAL_STAFF_DEPT, 1469 REFERRAL_STAFF_MANAGER, 1470 REFERRAL_STAFF_NAME, 1471 REFERRAL_STAFF_NO, 1472 STATUS_KEY_WORK, 1473 TEAM_KEY_OWNER_FRST, 1474 TEAM_KEY_OWNER_LTST, 1475 TEAM_MEMBER_KEY_OWNER_FRST, 1476 TEAM_MEMBER_KEY_OWNER_LTST, 1477 (ifn(WORK_COMPLETE_DTTM is not null,(datepart(WORK_COMPLETE_DTTM) -datepart(WORK_CREATED_DTTM) ),0)) as 1477 ! WORK_CREATE_TO_COMP_DAYS length = 8, 1478 WORK_ITEM_ID, 1479 WORK_ITEM_TYPE_KEY, 1480 INTERFACE_DTTM, 1481 (DATETIME()) as PROCESS_DTTM length = 8 1482 format = DATETIME20. 1483 informat = DATETIME20., 1484 WORK_ITEM_REFERENCE_KEY, 1485 CUSTOMER_ID, 1486 WORK_ITEM_KEY, 1487 WORK_COMPLETE_DTTM, 1488 WORK_CREATED_DTTM, 1489 REASON_ID_WORK, 1490 QUOTE_ID, 1491 QUOTE_STARTED_DTTM, 1492 QUOTE_ACCEPTED_DTTM, 1493 QUOTE_PRICE_INC_TAX_LTST, 1494 QUOTE_ORIG_PRICE_LTST, 1495 QUOTE_PRICE_EXC_TAX_LTST, 1496 QUOTE_DISC_TOT_LTST, 1497 NEXT_RENEWAL_DTTM, 48 The SAS System 07:32 Wednesday, March 30, 2016 1498 BILLING_KEY, 1499 CONTRACT_KEY, 1500 NOT_PROCEEDED_WITH_DTTM, 1501 QUOTE_EXPIRED_DTTM, 1502 REASON_ID_QUOTE, 1503 WORK_VALID_FROM_DTTM, 1504 QUOTE_PRICE_INC_TAX_FRST, 1505 QUOTE_ORIG_PRICE_FRST, 1506 QUOTE_PRICE_EXC_TAX_FRST, 1507 QUOTE_DISC_TOT_FRST, 1508 QUOTE_VALID_FROM_DTTM, 1509 WORK_ITEM_SUB_TYPE_KEY 1510 from &SYSLAST SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W66880IZ_RULE6_4 1511 ; NOTE: Table WORK.W668AOWI created, with 2 rows and 68 columns. 1512 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.02 seconds 1513 1514 %let SYSLAST = work.W668AOWI; 1515 1516 %global etls_sql_pushDown; 1517 %let etls_sql_pushDown = &sys_sql_ip_all; SYMBOLGEN: Macro variable SYS_SQL_IP_ALL resolves to -1 1518 1519 %rcSet(&sqlrc); MLOGIC(RCSET): Beginning execution. SYMBOLGEN: Macro variable SQLRC resolves to 0 MLOGIC(RCSET): Parameter ERROR has value 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &trans_rc) is FALSE SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MLOGIC(RCSET): %IF condition (&error gt &job_rc) is FALSE MLOGIC(RCSET): Ending execution. 1520 1521 1522 1523 /** Step end Derive Meas 1524 1525 GOPTIONS NOACCESSIBLE; 1526 %LET _CLIENTTASKLABEL=; 1527 %LET _CLIENTPROCESSFLOWNAME=; 1528 %LET _CLIENTPROJECTPATH=; 1529 %LET _CLIENTPROJECTNAME=; 1530 %LET _SASPROGRAMFILE=; 1531 1532 ;*';*";*/;quit;run; 1533 ODS _ALL_ CLOSE; 1534 1535 1536 QUIT; RUN; 49 The SAS System 07:32 Wednesday, March 30, 2016 1537