1 The SAS System 10:51 Tuesday, May 16, 2017 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program111'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGPDF TEMP; 12 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=printer SAS; WARNING: Unsupported device 'ACTIVEX' for PDF(EGPDF) destination. Using device 'ACTXIMG'. NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF". 13 FILENAME EGSR TEMP; 14 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue 14 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE 14 ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on"); NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 15 16 GOPTIONS ACCESSIBLE; 17 proc sql; 18 connect to oracle as myconn (user=bb83666 password=XXXXXXXXXXX path='CMI1p' readbuff=100000); 19 select * 20 from connection to myconn( 21 SELECT 22 to_number(OLS_DYN_APPLICATIONS.APPLICATION_ID), 23 OLS_VW_MIN_STATUS_DATE.MIN_DATE_OF_EFFECT, 24 trunc(OLS_VW_APP_SUBMITTED_DATE.MIN_DATE_OF_EFFECT), 25 OLS_DYN_APPLICATIONS.CREATOR_ID, 26 OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE, 27 OLS_ST_APPROVAL_TYPE.APPROVAL_TYPE_DESC, 28 OLS_DYN_APPLICATION_PRODUCTS.LOAN_AMOUNT, 29 OLS_VW_PRODUCT_DETAILS.LOAN_TYPE_DESCRIPTION, 30 OLS_ST_PRODUCT_STATUSES.PRODUCT_STATUS_DESCRIPTION, 31 OLS_DYN_APP_PROD_STATUSES.DATE_OF_EFFECT, 32 HISTORY_STATUSES.PRODUCT_STATUS_DESCRIPTION, 33 ORIGINATORS.SYSTEM_USER_ID, 34 case when length(ORIGINATORS.COST_CENTRE) = 7 then 35 '0' || substr(ORIGINATORS.COST_CENTRE,5,(length(ORIGINATORS.COST_CENTRE)-4)) 36 else 37 substr(ORIGINATORS.COST_CENTRE,5,(length(ORIGINATORS.COST_CENTRE)-4)) 38 end, 39 case when length(ORIGINATING_ORG_UNITS.COST_CENTRE) = 7 then 40 '0' || substr(ORIGINATING_ORG_UNITS.COST_CENTRE,5,(length(ORIGINATING_ORG_UNITS.COST_CENTRE)-4)) 41 else 42 substr(ORIGINATING_ORG_UNITS.COST_CENTRE,5,(length(ORIGINATING_ORG_UNITS.COST_CENTRE)-4)) 43 end, 44 OLS_DYN_LEGAL_ENTITIES.CIF_KEY, 45 OLS_DYN_LEGAL_ENTITIES.LEGAL_ENTITY_ID, 46 OLS_VW_PRODUCT_DETAILS.PRODUCT_NAME, 47 OLS_DYN_APPLICATION_PRODUCTS.APPLICATION_PRODUCT_ID, 48 ORIGINATOR_ST_SALES_CHANNEL.SALES_CHANNEL_DESC, 49 OLS_DYN_LOAN_APPLICATIONS.LOAN_TO_VALUE_RATIO, 50 ORIGINATING_ORG_UNITS.ORGANISATIONAL_UNIT_NAME, 51 trunc(DYN_STOPPED_APPS_HIST.STOP_DATETIME), 2 The SAS System 10:51 Tuesday, May 16, 2017 52 ST_STOP_APP_RSN_TYPES_HIST.DESCRIPTION, 53 ( ORIGINATORS.FAMILY_NAME ) || ', ' ||( ORIGINATORS.FIRST_NAME ) || DECODE(( ORIGINATORS.MIDDLE_NAMES ),NULL,NULL,' ' 53 ! || ( ORIGINATORS.MIDDLE_NAMES )), 54 ORIGINATOR_ORG_UNITS.COST_CENTRE, 55 OLS_DYN_APPLICATION_PRODUCTS.PROMO_CODE 56 FROM 57 OLS_DYN_APPLICATIONS, 58 OLS_VW_MIN_STATUS_DATE, 59 OLS_VW_APP_SUBMITTED_DATE, 60 OLS_ST_APPROVAL_TYPE, 61 OLS_DYN_APPLICATION_PRODUCTS, 62 OLS_VW_PRODUCT_DETAILS, 63 OLS_ST_PRODUCT_STATUSES, 64 OLS_DYN_APP_PROD_STATUSES, 65 OLS_ST_PRODUCT_STATUSES HISTORY_STATUSES, 66 OLS_DYN_SYSTEM_USERS ORIGINATORS, 67 OLS_DYN_ORGANISATIONAL_UNITS ORIGINATING_ORG_UNITS, 68 OLS_DYN_LEGAL_ENTITIES, 69 OLS_ST_SALES_CHANNEL ORIGINATOR_ST_SALES_CHANNEL, 70 OLS_DYN_LOAN_APPLICATIONS, 71 OLS_DYN_STOPPED_APPLICATIONS DYN_STOPPED_APPS_HIST, 72 OLS_ST_STOP_APP_REASON_TYPES ST_STOP_APP_RSN_TYPES_HIST, 73 OLS_DYN_ORGANISATIONAL_UNITS ORIGINATOR_ORG_UNITS, 74 OLS_DYN_STOPPED_APP_REASONS DYN_STOPPED_APP_RSNS_HIST 75 WHERE 76 ( OLS_DYN_APPLICATIONS.APPLICATION_ID=OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID ) 77 AND ( OLS_DYN_APPLICATIONS.APPLICATION_ID=OLS_DYN_LEGAL_ENTITIES.APPLICATION_ID ) 78 AND ( OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID=OLS_DYN_APPLICATION_PRODUCTS.APPLICATION_ID ) 79 AND ( ORIGINATORS.ORGANISATIONAL_UNIT_ID=ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_ID ) 80 AND ( OLS_DYN_APPLICATIONS.ORIGINATOR_ID=ORIGINATORS.SYSTEM_USER_ID ) 81 AND ( OLS_DYN_APPLICATIONS.ORIGINATING_UNIT_ID=ORIGINATING_ORG_UNITS.ORGANISATIONAL_UNIT_ID ) 82 AND ( OLS_DYN_APPLICATION_PRODUCTS.PRODUCT_ID=OLS_VW_PRODUCT_DETAILS.PRODUCT_ID ) 83 AND ( OLS_DYN_APPLICATION_PRODUCTS.CURRENT_STATUS=OLS_ST_PRODUCT_STATUSES.PRODUCT_STATUS ) 84 AND ( OLS_DYN_APP_PROD_STATUSES.STATUS=HISTORY_STATUSES.PRODUCT_STATUS ) 85 AND ( OLS_DYN_APPLICATION_PRODUCTS.APPLICATION_PRODUCT_ID=OLS_DYN_APP_PROD_STATUSES.APPLICATION_PRODUCT_ID ) 86 AND ( OLS_VW_MIN_STATUS_DATE.APPLICATION_ID=OLS_DYN_APPLICATIONS.APPLICATION_ID ) 87 AND ( OLS_VW_APP_SUBMITTED_DATE.APPLICATION_ID=OLS_DYN_APPLICATIONS.APPLICATION_ID ) 88 AND ( OLS_DYN_APPLICATIONS.APPROVAL_TYPE=OLS_ST_APPROVAL_TYPE.APPROVAL_TYPE ) 89 AND ( ST_STOP_APP_RSN_TYPES_HIST.REASON_ID=DYN_STOPPED_APP_RSNS_HIST.REASON_ID ) 90 AND ( DYN_STOPPED_APP_RSNS_HIST.STOP_ID=DYN_STOPPED_APPS_HIST.STOP_ID ) 91 AND ( DYN_STOPPED_APPS_HIST.APPLICATION_ID=OLS_DYN_LOAN_APPLICATIONS.APPLICATION_ID ) 92 AND ( ORIGINATOR_ST_SALES_CHANNEL.SALES_CHANNEL=ORIGINATORS.SALES_CHANNEL ) 93 AND ( 94 ( ( ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_NAME ) != 'OLS Project Team' And ( 94 ! ORIGINATOR_ORG_UNITS.ORGANISATIONAL_UNIT_NAME ) != 'Mortgages - OLS/HOLO Support Desk' ) 95 AND ( OLS_DYN_LEGAL_ENTITIES.LEGAL_ENTITY_ID = (select min(DLE2.LEGAL_ENTITY_ID) from OLS_DYN_LEGAL_ENTITIES DLE2 96 where DLE2.APPLICATION_ID = OLS_DYN_APPLICATIONS.APPLICATION_ID) 97 ) 98 AND ORIGINATOR_ORG_UNITS.COST_CENTRE NOT LIKE '306:143' 99 AND ( OLS_DYN_APPLICATIONS.CREATOR_ID NOT IN 99 ! ('bb24177','bb33297','bb33934','bb40078','bb46177','bb82721','bb86636','bb93445','wadminuat') 100 ) 101 AND OLS_VW_PRODUCT_DETAILS.PRODUCT_NAME NOT IN ('Indicative Reverse Mortgage', 'Indicative Housing Loan', 101 ! 'Indicative Equity Line') 102 AND (trunc(OLS_DYN_APP_PROD_STATUSES.DATE_OF_EFFECT) BETWEEN trunc(sysdate-30) AND trunc(sysdate-1) 103 OR trunc(OLS_DYN_APPLICATIONS.PRE_TO_FULL_APPROVAL_DATE) BETWEEN trunc(sysdate-30) AND trunc(sysdate-1)) 104 ); 105 3 The SAS System 10:51 Tuesday, May 16, 2017 106 disconnect from oracle; 107 quit; 108 109 GOPTIONS NOACCESSIBLE; 110 %LET _CLIENTTASKLABEL=; 111 %LET _CLIENTPROJECTPATH=; 112 %LET _CLIENTPROJECTNAME=; 113 %LET _SASPROGRAMFILE=; 114 115 ;*';*";*/;quit;run; 116 ODS _ALL_ CLOSE; 117 118 119 QUIT; RUN; 120