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.CODE_TYPE CODE_TYPE, ZZ1.CODE CODE, ZZ1.NIBRS_CODE NIBRS_CODE, ZZ1.EC_CODE_TYPE EC_CODE_TYPE, ZZ1.EC_CODE EC_CODE, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.UPDATOR_DATE UPDATOR_DATE, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.REC_ID REC_ID FROM ZZ_EJS_CODE_NIBRS_MAPPINGS ZZ1 union all select sysdate, 'C', null, null, ZZ2.CODE_TYPE, ZZ2.CODE, ZZ2.NIBRS_CODE, ZZ2.EC_CODE_TYPE, ZZ2.EC_CODE, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_ID, ZZ2.UPDATOR_DATE, ZZ2.CREATOR_ID, ZZ2.REC_ID FROM EJS_CODE_NIBRS_MAPPINGS ZZ2 where exists ( select * from ZZ_EJS_CODE_NIBRS_MAPPINGS where CODE_TYPE= ZZ2.CODE_TYPE AND CODE= ZZ2.CODE AND NIBRS_CODE= ZZ2.NIBRS_CODE AND EC_CODE_TYPE= ZZ2.EC_CODE_TYPE AND EC_CODE= ZZ2.EC_CODE )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, CODE_TYPE, CODE, NIBRS_CODE, EC_CODE_TYPE, EC_CODE, CREATOR_DATE, UPDATOR_ID, UPDATOR_DATE, CREATOR_ID, REC_ID from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, CODE_TYPE, CODE, NIBRS_CODE, EC_CODE_TYPE, EC_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc),10,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_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 CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc),11,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_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 CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc),12,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc) END) END) UPDATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc),13,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc) END) END) CREATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc),14,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc) END) END) REC_ID,row_number() over (partition by CODE_TYPE,CODE,NIBRS_CODE,EC_CODE_TYPE,EC_CODE order by audit_timestamp asc) as rn from t )