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