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.KEYWORD KEYWORD, ZZ1.EFFECTIVE_DATE EFFECTIVE_DATE, ZZ1.VALUE VALUE, ZZ1.END_DATE END_DATE, ZZ1.DESCRIPTION DESCRIPTION, ZZ1.DATA_TYPE DATA_TYPE, ZZ1.LOV_TABLE LOV_TABLE, ZZ1.LOV_COLUMN_1 LOV_COLUMN_1, ZZ1.LOV_COLUMN_2 LOV_COLUMN_2, ZZ1.LOV_COLUMN_3 LOV_COLUMN_3, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.UPDATOR_DATE UPDATOR_DATE, ZZ1.APPLICATION APPLICATION, ZZ1.NAME NAME, ZZ1.REC_ID REC_ID FROM ZZ_EJS_SUB_MAINT ZZ1 union all select sysdate, 'C', null, null, ZZ2.KEYWORD, ZZ2.EFFECTIVE_DATE, ZZ2.VALUE, ZZ2.END_DATE, ZZ2.DESCRIPTION, ZZ2.DATA_TYPE, ZZ2.LOV_TABLE, ZZ2.LOV_COLUMN_1, ZZ2.LOV_COLUMN_2, ZZ2.LOV_COLUMN_3, ZZ2.CREATOR_ID, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_ID, ZZ2.UPDATOR_DATE, ZZ2.APPLICATION, ZZ2.NAME, ZZ2.REC_ID FROM EJS_SUB_MAINT ZZ2 where exists ( select * from ZZ_EJS_SUB_MAINT where KEYWORD= ZZ2.KEYWORD AND EFFECTIVE_DATE= ZZ2.EFFECTIVE_DATE AND APPLICATION= ZZ2.APPLICATION AND NAME= ZZ2.NAME )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, KEYWORD, EFFECTIVE_DATE, VALUE, END_DATE, DESCRIPTION, DATA_TYPE, LOV_TABLE, LOV_COLUMN_1, LOV_COLUMN_2, LOV_COLUMN_3, CREATOR_ID, CREATOR_DATE, UPDATOR_ID, UPDATOR_DATE, APPLICATION, NAME, REC_ID from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, KEYWORD, EFFECTIVE_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then VALUE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),7,1),'N') ='N' THEN lead(VALUE ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) VALUE,( CASE WHEN audit_transaction_type in ( 'C','D') then END_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),8,1),'N') ='N' THEN lead(END_DATE ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) END_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then DESCRIPTION ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),9,1),'N') ='N' THEN lead(DESCRIPTION ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) DESCRIPTION,( CASE WHEN audit_transaction_type in ( 'C','D') then DATA_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),10,1),'N') ='N' THEN lead(DATA_TYPE ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) DATA_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then LOV_TABLE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),11,1),'N') ='N' THEN lead(LOV_TABLE ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) LOV_TABLE,( CASE WHEN audit_transaction_type in ( 'C','D') then LOV_COLUMN_1 ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),12,1),'N') ='N' THEN lead(LOV_COLUMN_1 ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) LOV_COLUMN_1,( CASE WHEN audit_transaction_type in ( 'C','D') then LOV_COLUMN_2 ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),13,1),'N') ='N' THEN lead(LOV_COLUMN_2 ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) LOV_COLUMN_2,( CASE WHEN audit_transaction_type in ( 'C','D') then LOV_COLUMN_3 ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),14,1),'N') ='N' THEN lead(LOV_COLUMN_3 ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) LOV_COLUMN_3,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),15,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME 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 KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),16,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME 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 KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),17,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME 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 KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),18,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) UPDATOR_DATE, APPLICATION, NAME,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc),21,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) END) END) REC_ID,row_number() over (partition by KEYWORD,EFFECTIVE_DATE,APPLICATION,NAME order by audit_timestamp asc) as rn from t )