create or replace view TEST.V_P1RP_EV_TO_RELATEDRR as -- We need to find the last P1 RR ADD UPD operation immediately preceeding -- each other ADD UPD event type -- this is for when a NON RR event column is populated from the 'associated' RR record select P1RPEV.ORGANISATION_CODE as P1RPRRR_ORGID ,P1RPEV.BATCH_RECEIVED_BATCH_ID as P1RPRRR_BID ,P1RPEV.REF_CORRELATION_ID as P1RPRRR_EV_CORRID -- ,P1RPEV.REF_LOCAL_REF_IDENTIFIER as P1RPRRR_LRID -- ,P1RPEV.REF_CURRENT_EVENT_ID as P1RPRRR_EVID ,P1RPRR.REF_CORRELATION_ID as P1RPRRR_RR_CORRID from NPFP1.NPF_REFERRAL_PROCESS P1RPEV join NPFP1.NPF_REFERRAL_PROCESS P1RPRR on P1RPRR.ORGANISATION_CODE = P1RPEV.ORGANISATION_CODE and P1RPRR.REF_LOCAL_REF_IDENTIFIER = P1RPEV.REF_LOCAL_REF_IDENTIFIER and P1RPRR.EVENT_DOMAIN_ID = 1 and P1RPRR.EVENT_OPERATION_ID < 3 where 1 = 1 -- and P1RPEV.REF_CORRELATION_ID = 'NPF-256BPUPDCA0049' and P1RPRR.REF_TRANS_TS = ( select MAX(REF_TRANS_TS) as MAXTS from NPFP1.NPF_REFERRAL_PROCESS RP where RP.ORGANISATION_CODE = P1RPEV.ORGANISATION_CODE and RP.REF_LOCAL_REF_IDENTIFIER = P1RPEV.REF_LOCAL_REF_IDENTIFIER -- **Correlated** and RP.EVENT_DOMAIN_ID = 1 and RP.EVENT_OPERATION_ID < 3 and RP.REF_TRANS_TS <= P1RPEV.REF_TRANS_TS -- **Correlated** )@