with t as (SELECT ZZ1.AUDIT_TIMESTAMP AUDIT_TIMESTAMP, ZZ1.AUDIT_TRANSACTION_TYPE AUDIT_TRANSACTION_TYPE, ZZ1.AUDIT_USER_NAME AUDIT_USER_NAME, ZZ1.AUDIT_TRUE_NULLS AUDIT_TRUE_NULLS, ZZ1.AGENCY_CODE AGENCY_CODE, ZZ1.AGENCY_DESC AGENCY_DESC, ZZ1.AGENCY_STREET AGENCY_STREET, ZZ1.AGENCY_CITY AGENCY_CITY, ZZ1.AGENCY_STATE AGENCY_STATE, ZZ1.AGENCY_ZIP AGENCY_ZIP, ZZ1.AGENCY_COUNTY AGENCY_COUNTY, ZZ1.AGENCY_FAX AGENCY_FAX, ZZ1.AGENCY_PHONE AGENCY_PHONE, ZZ1.ORI_NUMBER ORI_NUMBER, ZZ1.INTERNAL_OFFENSES INTERNAL_OFFENSES, ZZ1.DEF_LEA_SEC_LEVEL DEF_LEA_SEC_LEVEL, ZZ1.DISPATCH_ENABLED DISPATCH_ENABLED, ZZ1.PWD_EXPIRE_DAYS PWD_EXPIRE_DAYS, ZZ1.LABEL_PRINTER LABEL_PRINTER, ZZ1.UCR_REQUIRED UCR_REQUIRED, ZZ1.LEA_CASE_UPDATE LEA_CASE_UPDATE, ZZ1.RECORDS_AGENCY_CODE RECORDS_AGENCY_CODE, ZZ1.OFFICE_CODE OFFICE_CODE, ZZ1.UCR_CLERK UCR_CLERK, ZZ1.CAD_AGENCY_ID CAD_AGENCY_ID, ZZ1.JRD_AGENCY_TYPE JRD_AGENCY_TYPE, ZZ1.LEVEL2_AGENCY_CODE LEVEL2_AGENCY_CODE, ZZ1.POC_TITLE_CODE POC_TITLE_CODE, ZZ1.POC_NAME POC_NAME, ZZ1.AGENCY_DISPLAY_CODE AGENCY_DISPLAY_CODE, ZZ1.DEFAULT_AGENCY_ONLY_FLG DEFAULT_AGENCY_ONLY_FLG, ZZ1.DEFAULT_TIME_ZONE_CODE DEFAULT_TIME_ZONE_CODE, ZZ1.AGENCY_IMAGE_LOCATION AGENCY_IMAGE_LOCATION, ZZ1.JRD_AGENCY_TYPE_TYPE JRD_AGENCY_TYPE_TYPE, ZZ1.OFFICE_CODE_TYPE OFFICE_CODE_TYPE, ZZ1.POC_TITLE_CODE_TYPE POC_TITLE_CODE_TYPE, ZZ1.WEBSITE_URL WEBSITE_URL, ZZ1.DEFAULT_DSPLY_NOTIFY_ITEMS DEFAULT_DSPLY_NOTIFY_ITEMS, ZZ1.TIME_ZONE_CODE_TYPE TIME_ZONE_CODE_TYPE, ZZ1.REC_ID REC_ID, ZZ1.NIBR_CITY_INDICATOR NIBR_CITY_INDICATOR, ZZ1.IMAGE_ID IMAGE_ID, ZZ1.JS_ORGANIZATION JS_ORGANIZATION, ZZ1.CASE_GOOD_STANDING_DAYS CASE_GOOD_STANDING_DAYS, ZZ1.NO_OF_COMMENTS_TO_DISPLAY NO_OF_COMMENTS_TO_DISPLAY, ZZ1.DS_AGENCY_COUNTY DS_AGENCY_COUNTY, ZZ1.LATITUDE LATITUDE, ZZ1.LONGITUDE LONGITUDE, ZZ1.SUPPORT_CODE SUPPORT_CODE, ZZ1.SUPPORT_CODE_TYPE SUPPORT_CODE_TYPE, ZZ1.LICENSED_USERS LICENSED_USERS, ZZ1.SOLR_STATE SOLR_STATE, ZZ1.OPTION_CODE_TYPE OPTION_CODE_TYPE, ZZ1.OPTION_CODE OPTION_CODE, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.UPDATOR_DATE UPDATOR_DATE FROM ZZ_AGENCY_CODES ZZ1 union all select sysdate, 'C', null, null, ZZ2.AGENCY_CODE, ZZ2.AGENCY_DESC, ZZ2.AGENCY_STREET, ZZ2.AGENCY_CITY, ZZ2.AGENCY_STATE, ZZ2.AGENCY_ZIP, ZZ2.AGENCY_COUNTY, ZZ2.AGENCY_FAX, ZZ2.AGENCY_PHONE, ZZ2.ORI_NUMBER, ZZ2.INTERNAL_OFFENSES, ZZ2.DEF_LEA_SEC_LEVEL, ZZ2.DISPATCH_ENABLED, ZZ2.PWD_EXPIRE_DAYS, ZZ2.LABEL_PRINTER, ZZ2.UCR_REQUIRED, ZZ2.LEA_CASE_UPDATE, ZZ2.RECORDS_AGENCY_CODE, ZZ2.OFFICE_CODE, ZZ2.UCR_CLERK, ZZ2.CAD_AGENCY_ID, ZZ2.JRD_AGENCY_TYPE, ZZ2.LEVEL2_AGENCY_CODE, ZZ2.POC_TITLE_CODE, ZZ2.POC_NAME, ZZ2.AGENCY_DISPLAY_CODE, ZZ2.DEFAULT_AGENCY_ONLY_FLG, ZZ2.DEFAULT_TIME_ZONE_CODE, ZZ2.AGENCY_IMAGE_LOCATION, ZZ2.JRD_AGENCY_TYPE_TYPE, ZZ2.OFFICE_CODE_TYPE, ZZ2.POC_TITLE_CODE_TYPE, ZZ2.WEBSITE_URL, ZZ2.DEFAULT_DSPLY_NOTIFY_ITEMS, ZZ2.TIME_ZONE_CODE_TYPE, ZZ2.REC_ID, ZZ2.NIBR_CITY_INDICATOR, ZZ2.IMAGE_ID, ZZ2.JS_ORGANIZATION, ZZ2.CASE_GOOD_STANDING_DAYS, ZZ2.NO_OF_COMMENTS_TO_DISPLAY, ZZ2.DS_AGENCY_COUNTY, ZZ2.LATITUDE, ZZ2.LONGITUDE, ZZ2.SUPPORT_CODE, ZZ2.SUPPORT_CODE_TYPE, ZZ2.LICENSED_USERS, ZZ2.SOLR_STATE, ZZ2.OPTION_CODE_TYPE, ZZ2.OPTION_CODE, ZZ2.CREATOR_ID, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_ID, ZZ2.UPDATOR_DATE FROM AGENCY_CODES ZZ2 where exists ( select * from ZZ_AGENCY_CODES where AGENCY_CODE= ZZ2.AGENCY_CODE )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, AGENCY_CODE, AGENCY_DESC, AGENCY_STREET, AGENCY_CITY, AGENCY_STATE, AGENCY_ZIP, AGENCY_COUNTY, AGENCY_FAX, AGENCY_PHONE, ORI_NUMBER, INTERNAL_OFFENSES, DEF_LEA_SEC_LEVEL, DISPATCH_ENABLED, PWD_EXPIRE_DAYS, LABEL_PRINTER, UCR_REQUIRED, LEA_CASE_UPDATE, RECORDS_AGENCY_CODE, OFFICE_CODE, UCR_CLERK, CAD_AGENCY_ID, JRD_AGENCY_TYPE, LEVEL2_AGENCY_CODE, POC_TITLE_CODE, POC_NAME, AGENCY_DISPLAY_CODE, DEFAULT_AGENCY_ONLY_FLG, DEFAULT_TIME_ZONE_CODE, AGENCY_IMAGE_LOCATION, JRD_AGENCY_TYPE_TYPE, OFFICE_CODE_TYPE, POC_TITLE_CODE_TYPE, WEBSITE_URL, DEFAULT_DSPLY_NOTIFY_ITEMS, TIME_ZONE_CODE_TYPE, REC_ID, NIBR_CITY_INDICATOR, IMAGE_ID, JS_ORGANIZATION, CASE_GOOD_STANDING_DAYS, NO_OF_COMMENTS_TO_DISPLAY, DS_AGENCY_COUNTY, LATITUDE, LONGITUDE, SUPPORT_CODE, SUPPORT_CODE_TYPE, LICENSED_USERS, SOLR_STATE, OPTION_CODE_TYPE, OPTION_CODE, CREATOR_ID, CREATOR_DATE, UPDATOR_ID, UPDATOR_DATE from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, AGENCY_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_DESC ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),6,1),'N') ='N' THEN lead(AGENCY_DESC ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_DESC,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_STREET ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),7,1),'N') ='N' THEN lead(AGENCY_STREET ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_STREET,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_CITY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),8,1),'N') ='N' THEN lead(AGENCY_CITY ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_CITY,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_STATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),9,1),'N') ='N' THEN lead(AGENCY_STATE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_STATE,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_ZIP ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),10,1),'N') ='N' THEN lead(AGENCY_ZIP ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_ZIP,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_COUNTY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),11,1),'N') ='N' THEN lead(AGENCY_COUNTY ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_COUNTY,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_FAX ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),12,1),'N') ='N' THEN lead(AGENCY_FAX ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_FAX,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_PHONE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),13,1),'N') ='N' THEN lead(AGENCY_PHONE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_PHONE,( CASE WHEN audit_transaction_type in ( 'C','D') then ORI_NUMBER ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),14,1),'N') ='N' THEN lead(ORI_NUMBER ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) ORI_NUMBER,( CASE WHEN audit_transaction_type in ( 'C','D') then INTERNAL_OFFENSES ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),15,1),'N') ='N' THEN lead(INTERNAL_OFFENSES ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) INTERNAL_OFFENSES,( CASE WHEN audit_transaction_type in ( 'C','D') then DEF_LEA_SEC_LEVEL ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),16,1),'N') ='N' THEN lead(DEF_LEA_SEC_LEVEL ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) DEF_LEA_SEC_LEVEL,( CASE WHEN audit_transaction_type in ( 'C','D') then DISPATCH_ENABLED ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),17,1),'N') ='N' THEN lead(DISPATCH_ENABLED ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) DISPATCH_ENABLED,( CASE WHEN audit_transaction_type in ( 'C','D') then PWD_EXPIRE_DAYS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),18,1),'N') ='N' THEN lead(PWD_EXPIRE_DAYS ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) PWD_EXPIRE_DAYS,( CASE WHEN audit_transaction_type in ( 'C','D') then LABEL_PRINTER ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),19,1),'N') ='N' THEN lead(LABEL_PRINTER ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) LABEL_PRINTER,( CASE WHEN audit_transaction_type in ( 'C','D') then UCR_REQUIRED ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),20,1),'N') ='N' THEN lead(UCR_REQUIRED ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) UCR_REQUIRED,( CASE WHEN audit_transaction_type in ( 'C','D') then LEA_CASE_UPDATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),21,1),'N') ='N' THEN lead(LEA_CASE_UPDATE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) LEA_CASE_UPDATE,( CASE WHEN audit_transaction_type in ( 'C','D') then RECORDS_AGENCY_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),22,1),'N') ='N' THEN lead(RECORDS_AGENCY_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) RECORDS_AGENCY_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then OFFICE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),23,1),'N') ='N' THEN lead(OFFICE_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) OFFICE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then UCR_CLERK ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),24,1),'N') ='N' THEN lead(UCR_CLERK ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) UCR_CLERK,( CASE WHEN audit_transaction_type in ( 'C','D') then CAD_AGENCY_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),25,1),'N') ='N' THEN lead(CAD_AGENCY_ID ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) CAD_AGENCY_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then JRD_AGENCY_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),26,1),'N') ='N' THEN lead(JRD_AGENCY_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) JRD_AGENCY_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then LEVEL2_AGENCY_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),27,1),'N') ='N' THEN lead(LEVEL2_AGENCY_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) LEVEL2_AGENCY_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then POC_TITLE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),28,1),'N') ='N' THEN lead(POC_TITLE_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) POC_TITLE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then POC_NAME ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),29,1),'N') ='N' THEN lead(POC_NAME ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) POC_NAME,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_DISPLAY_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),30,1),'N') ='N' THEN lead(AGENCY_DISPLAY_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_DISPLAY_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then DEFAULT_AGENCY_ONLY_FLG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),31,1),'N') ='N' THEN lead(DEFAULT_AGENCY_ONLY_FLG ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) DEFAULT_AGENCY_ONLY_FLG,( CASE WHEN audit_transaction_type in ( 'C','D') then DEFAULT_TIME_ZONE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),32,1),'N') ='N' THEN lead(DEFAULT_TIME_ZONE_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) DEFAULT_TIME_ZONE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_IMAGE_LOCATION ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),33,1),'N') ='N' THEN lead(AGENCY_IMAGE_LOCATION ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) AGENCY_IMAGE_LOCATION,( CASE WHEN audit_transaction_type in ( 'C','D') then JRD_AGENCY_TYPE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),34,1),'N') ='N' THEN lead(JRD_AGENCY_TYPE_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) JRD_AGENCY_TYPE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then OFFICE_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),35,1),'N') ='N' THEN lead(OFFICE_CODE_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) OFFICE_CODE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then POC_TITLE_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),36,1),'N') ='N' THEN lead(POC_TITLE_CODE_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) POC_TITLE_CODE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then WEBSITE_URL ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),37,1),'N') ='N' THEN lead(WEBSITE_URL ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) WEBSITE_URL,( CASE WHEN audit_transaction_type in ( 'C','D') then DEFAULT_DSPLY_NOTIFY_ITEMS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),38,1),'N') ='N' THEN lead(DEFAULT_DSPLY_NOTIFY_ITEMS ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) DEFAULT_DSPLY_NOTIFY_ITEMS,( CASE WHEN audit_transaction_type in ( 'C','D') then TIME_ZONE_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),39,1),'N') ='N' THEN lead(TIME_ZONE_CODE_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) TIME_ZONE_CODE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),40,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) REC_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then NIBR_CITY_INDICATOR ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),41,1),'N') ='N' THEN lead(NIBR_CITY_INDICATOR ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) NIBR_CITY_INDICATOR,( CASE WHEN audit_transaction_type in ( 'C','D') then IMAGE_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),42,1),'N') ='N' THEN lead(IMAGE_ID ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) IMAGE_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then JS_ORGANIZATION ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),43,1),'N') ='N' THEN lead(JS_ORGANIZATION ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) JS_ORGANIZATION,( CASE WHEN audit_transaction_type in ( 'C','D') then CASE_GOOD_STANDING_DAYS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),44,1),'N') ='N' THEN lead(CASE_GOOD_STANDING_DAYS ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) CASE_GOOD_STANDING_DAYS,( CASE WHEN audit_transaction_type in ( 'C','D') then NO_OF_COMMENTS_TO_DISPLAY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),45,1),'N') ='N' THEN lead(NO_OF_COMMENTS_TO_DISPLAY ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) NO_OF_COMMENTS_TO_DISPLAY,( CASE WHEN audit_transaction_type in ( 'C','D') then DS_AGENCY_COUNTY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),46,1),'N') ='N' THEN lead(DS_AGENCY_COUNTY ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) DS_AGENCY_COUNTY,( CASE WHEN audit_transaction_type in ( 'C','D') then LATITUDE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),47,1),'N') ='N' THEN lead(LATITUDE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) LATITUDE,( CASE WHEN audit_transaction_type in ( 'C','D') then LONGITUDE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),48,1),'N') ='N' THEN lead(LONGITUDE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) LONGITUDE,( CASE WHEN audit_transaction_type in ( 'C','D') then SUPPORT_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),49,1),'N') ='N' THEN lead(SUPPORT_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) SUPPORT_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then SUPPORT_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),50,1),'N') ='N' THEN lead(SUPPORT_CODE_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) SUPPORT_CODE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then LICENSED_USERS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),51,1),'N') ='N' THEN lead(LICENSED_USERS ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) LICENSED_USERS,( CASE WHEN audit_transaction_type in ( 'C','D') then SOLR_STATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),52,1),'N') ='N' THEN lead(SOLR_STATE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) SOLR_STATE,( CASE WHEN audit_transaction_type in ( 'C','D') then OPTION_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),53,1),'N') ='N' THEN lead(OPTION_CODE_TYPE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) OPTION_CODE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then OPTION_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),54,1),'N') ='N' THEN lead(OPTION_CODE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) OPTION_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),55,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) CREATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),56,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) CREATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then UPDATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),57,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) UPDATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then UPDATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by AGENCY_CODE order by audit_timestamp asc),58,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by AGENCY_CODE order by audit_timestamp asc) END) END) UPDATOR_DATE,row_number() over (partition by AGENCY_CODE order by audit_timestamp asc) as rn from t )