MERGE INTO FSI_D_MERCHANT_CARDS TARGET USING (SELECT SUM
(IVW_SOURCE.IVW_TARGET_AMOUNT) TARGET_AMOUNT , IDENTITY_CODE,
ID_NUMBER
FROM(
SELECT ( Src.SOURCE_AMOUNT * Drv.drv_factor) IVW_TARGET_AMOUNT,
Drv.IDENTITY_CODE, Drv.ID_NUMBER
FROM (SELECT SUM(DECODE(a.year_s, 1994, a.month_03, 0) )
SOURCE_AMOUNT
FROM LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID=2100
and a.GL_ACCOUNT_ID IN (SELECT leaf_node
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.sys_id_num = 2000779
and (OFSA_IDT_ROLLUP.leaf_node = 101301))
and (((a.CONSOLIDATION_CD = '100'))
and((a.IDENTITY_CODE = 0)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10316)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994)) Src , (SELECT ( ColVal / mTotal )
drv_factor , IDENTITY_CODE, ID_NUMBER
from (SELECT /*+ parallel(FSI_D_MERCHANT_CARDS, 8) */
a.IDENTITY_CODE, a.ID_NUMBER, (TOTAL_TRANSACTIONS) ColVal, (SUM
(TOTAL_TRANSACTIONS) OVER()) mTotal
from FSI_D_MERCHANT_CARDS a
where a.GL_ACCOUNT_ID=101301
and a.as_of_date='03/ 31/ 1994')
WHERE mTotal <> 0) Drv) IVW_SOURCE
WHERE IVW_TARGET_AMOUNT <> 0
GROUP BY IDENTITY_CODE , ID_NUMBER ) SOURCE ON
(SOURCE.IDENTITY_CODE = TARGET.IDENTITY_CODE
AND SOURCE.ID_NUMBER = TARGET.ID_NUMBER ) WHEN MATCHED THEN
UPDATE
SET IDENTITY_CODE_CHG = 10316.000000 , TARGET.ATM_EXP =
DECODE(identity_code_chg, 10316.000000, ATM_EXP, 0)+
(SOURCE.TARGET_AMOUNT * 1)