SQL - Create SCD2 table using Master and activity Log table

Question

I have two table one is Master table and second one is a activity log table which keeps all the activity log (insert,update,delete) of master table. I need to create third SCD2 type table which will be formed by using these two tables. I have created a query which updates the dates correctly, but the column values are not getting updated properly. Need some suggestions to improve the logic of my query which will give the correct output. Table details and expected output is given below for more understanding.

Master Table - MASTER

BANK_ID	CCY_NUMB_CODE	CCY_ALPHA_CODE	ISIN_CLOSE_DATE	ISIS_CLOSE_PRICE	ISIN_STATUS	CCY_SHORT_NAME	DEL_FLAG	LAST_MAN_MDFCN
1	9	INE079A01016	28-01-2000	148.6	A	GLOBAL TELE 21/4/99	Y	23-06-2015

Activity Log table - AlOG

BANK_ID	MODULE_ID	INSERT_DATE	REQUEST_DATE	USERID	TABLE_NAME	PRIMARY_KEY_BUFFER	FIELD_LABEL	ORA_FIELD_NAME	NEW_VALUE	OLD_VALUE	DESCRIPTION
1	6	18-10-1999	18-10-1999	MM	MASTER	1,9	null	null	null	null	NEW RECORD INSERTED
1	6	18-10-1999	18-10-1999	MM	MASTER	1,9	ISIN Status	ISIN_STATUS	A	I	UPDATED ISIN STATUS
1	6	20-10-1999	20-10-1999	MM	MASTER	1,9	ISIN Description	CCY_SHORT_NAME	GLOBAL TELE 21/4/99	GLOBAL TELE EQ.NPP	UPDATED SHORT NAME
1	6	31-01-2000	31-01-2000	MM	MASTER	1,9	Redemption Price	ISIS_CLOSE_PRICE	1387.77	540	UPDATED CLOSE PRICE
1	6	31-01-2000	31-01-2000	MM	MASTER	1,9	Close Date	ISIN_CLOSE_DATE	28-01-2000	15-10-1999	UPDATED CLOSE DATE
1	6	23-06-2015	23-06-2015	MM	MASTER	1,9	DEL_FLAG	DEL_FLAG	Y	N	UPDATED DEL FLAG

NOTE - PRIMARY_KEY_BUFFER is the combination of BANK_ID and CCY_NUMB_CODE

Expected Output -

BANK_ID	CCY_NUMB_CODE	CCY_ALPHA_CODE	ISIN_CLOSE_DATE	ISIS_CLOSE_PRICE	ISIN_STATUS	CCY_SHORT_NAME	DEL_FLAG	LAST_MAN_MDFCN	START_DATE	END_DATE
1	9	INE079A01016	15-10-1999	540	I	GLOBAL TELE EQ.NPP	N	18-10-1999	18-10-1999	18-10-1999
1	9	INE079A01016	15-10-1999	540	A	GLOBAL TELE EQ.NPP	N	18-10-1999	18-10-1999	19-10-1999
1	9	INE079A01016	15-10-1999	540	A	GLOBAL TELE 21/4/99	N	20-10-1999	20-10-1999	30-01-2000
1	9	INE079A01016	28-01-2000	1387.77	A	GLOBAL TELE 21/4/99	N	31-01-2000	31-01-2000	22-06-2015
1	9	INE079A01016	28-01-2000	1387.77	A	GLOBAL TELE 21/4/99	Y	23-06-2015	23-06-2015	31-12-9999

Below is the query I am trying with to get desire output as don't want to use MERGE or UPDATE option

SELECT 
    p.BANK_ID, 
    CCY_NUMB_CODE, 
    CCY_ALPHA_CODE,
    (REQUEST_DATE -1) AS ISIN_CLOSE_DATE,
    ISIN_CLOSE_PRICE, 
    REQUEST_DATE AS LAST_MAN_MDFCN, 
    REQUEST_DATE AS START_DATE,
    NEW_VALUE,
    OLD_VALUE,
    DATE_LAST_MDFCN,
    LEAD(REQUEST_DATE -1 ) OVER (PARTITION BY p.BANK_ID, p.CCY_NUMB_CODE ORDER BY REQUEST_DATE) AS END_DATE
    --ROW_NUMBER() OVER (PARTITION BY p.BANK_ID, p.CCY_NUMB_CODE ORDER BY REQUEST_DATE) AS RN
  FROM MASTER p
  LEFT JOIN ALOG l
  ON l.PRIMARY_KEY_BUFFER = p.BANK_ID ||','|| p.CCY_NUMB_CODE
    where
    TABLE_NAME = 'MASTER' AND p.BANK_ID=1 AND p.CCY_NUMB_CODE=9
    order by insert_date

Answer 1

select * from
(SELECT 
    p.BANK_ID, 
    CCY_NUMB_CODE, 
    CCY_ALPHA_CODE,
    (REQUEST_DATE -1) AS ISIN_CLOSE_DATE,
    ISIN_CLOSE_PRICE, 
    REQUEST_DATE AS LAST_MAN_MDFCN, 
    REQUEST_DATE AS START_DATE,
    NEW_VALUE,
    OLD_VALUE,
    DATE_LAST_MDFCN,
    --LEAD(REQUEST_DATE -1 ) OVER (PARTITION BY p.BANK_ID, p.CCY_NUMB_CODE ORDER BY --REQUEST_DATE) AS END_DATE
    ROW_NUMBER() OVER (PARTITION BY p.BANK_ID, p.CCY_NUMB_CODE,REQUEST_DATE ORDER REQUEST_DATE) AS RN
  FROM MASTER p
  LEFT JOIN ALOG l
  ON l.PRIMARY_KEY_BUFFER = p.BANK_ID ||','|| p.CCY_NUMB_CODE
    where
    TABLE_NAME = 'MASTER' AND p.BANK_ID=1 AND p.CCY_NUMB_CODE=9
) x
where x.rn=1