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.DESCRIPTION DESCRIPTION, ZZ1.SYSTEM_REQ SYSTEM_REQ, ZZ1.ACTIVE ACTIVE, ZZ1.NOTES NOTES, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.UPDATOR_DATE UPDATOR_DATE, ZZ1.SORT_ORDER SORT_ORDER, ZZ1.DATA_TYPE DATA_TYPE, ZZ1.REC_ID REC_ID, ZZ1.SCREEN_PROMPT SCREEN_PROMPT, ZZ1.EXCLUSIVE_FLG EXCLUSIVE_FLG, ZZ1.AGENCY_FLAG AGENCY_FLAG FROM ZZ_EJS_CODES ZZ1 union all select sysdate, 'C', null, null, ZZ2.CODE_TYPE, ZZ2.CODE, ZZ2.DESCRIPTION, ZZ2.SYSTEM_REQ, ZZ2.ACTIVE, ZZ2.NOTES, ZZ2.CREATOR_ID, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_ID, ZZ2.UPDATOR_DATE, ZZ2.SORT_ORDER, ZZ2.DATA_TYPE, ZZ2.REC_ID, ZZ2.SCREEN_PROMPT, ZZ2.EXCLUSIVE_FLG, ZZ2.AGENCY_FLAG FROM EJS_CODES ZZ2 where exists ( select * from ZZ_EJS_CODES where CODE_TYPE= ZZ2.CODE_TYPE AND CODE= ZZ2.CODE )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, CODE_TYPE, CODE, DESCRIPTION, SYSTEM_REQ, ACTIVE, NOTES, CREATOR_ID, CREATOR_DATE, UPDATOR_ID, UPDATOR_DATE, SORT_ORDER, DATA_TYPE, REC_ID, SCREEN_PROMPT, EXCLUSIVE_FLG, AGENCY_FLAG from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, CODE_TYPE, CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then DESCRIPTION ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),7,1),'N') ='N' THEN lead(DESCRIPTION ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) DESCRIPTION,( CASE WHEN audit_transaction_type in ( 'C','D') then SYSTEM_REQ ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),8,1),'N') ='N' THEN lead(SYSTEM_REQ ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) SYSTEM_REQ,( CASE WHEN audit_transaction_type in ( 'C','D') then ACTIVE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),9,1),'N') ='N' THEN lead(ACTIVE ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) ACTIVE,( CASE WHEN audit_transaction_type in ( 'C','D') then NOTES ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),10,1),'N') ='N' THEN lead(NOTES ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) NOTES,( 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 order by audit_timestamp asc),11,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by CODE_TYPE,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 CODE_TYPE,CODE order by audit_timestamp asc),12,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by CODE_TYPE,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 order by audit_timestamp asc),13,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by CODE_TYPE,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 order by audit_timestamp asc),14,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) UPDATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then SORT_ORDER ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),15,1),'N') ='N' THEN lead(SORT_ORDER ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) SORT_ORDER,( CASE WHEN audit_transaction_type in ( 'C','D') then DATA_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),16,1),'N') ='N' THEN lead(DATA_TYPE ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) DATA_TYPE,( 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 order by audit_timestamp asc),17,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) REC_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then SCREEN_PROMPT ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),18,1),'N') ='N' THEN lead(SCREEN_PROMPT ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) SCREEN_PROMPT,( CASE WHEN audit_transaction_type in ( 'C','D') then EXCLUSIVE_FLG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),19,1),'N') ='N' THEN lead(EXCLUSIVE_FLG ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) EXCLUSIVE_FLG,( CASE WHEN audit_transaction_type in ( 'C','D') then AGENCY_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE_TYPE,CODE order by audit_timestamp asc),20,1),'N') ='N' THEN lead(AGENCY_FLAG ignore nulls) over (partition by CODE_TYPE,CODE order by audit_timestamp asc) END) END) AGENCY_FLAG,row_number() over (partition by CODE_TYPE,CODE order by audit_timestamp asc) as rn from t )