MERGE /*+ parallel(ledger_stat, 4) */ INTO LEDGER_STAT TARGET
USING (SELECT (SUM (IVW_SOURCE.IVWSRC_AMOUNT) * 1 )
BALANCE_AMOUNT, ORG_UNIT_ID, GL_ACCOUNT_ID, PRODUCT_ID
FROM (SELECT (Src.src_amount * Drv.drv_factor) IVWSRC_AMOUNT,
Src.ORG_UNIT_ID, Src.GL_ACCOUNT_ID, Src.PRODUCT_ID
FROM (SELECT SUM(DECODE(a.year_s, 1994, a.month_03, 0) )
src_amount , a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID
from LEDGER_STAT a
where (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10993)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994)
GROUP BY a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID) Src JOIN
(SELECT (ColVal/ mTotal) Drv_factor, ORG_UNIT_ID
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY a.ORG_UNIT_ID
ORDER BY a.ORG_UNIT_ID) rw, (SUM(DECODE(a.year_s, 1994,
a.month_03, 0)) over (PARTITION BY a.ORG_UNIT_ID ))ColVal,
(SUM(DECODE(a.year_s, 1994, a.month_03, 0)) over(PARTITION BY
a.ORG_UNIT_ID ))mTotal, a.ORG_UNIT_ID
from LEDGER_STAT a
where (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and a.year_s IN (1994) AND(a.ORG_UNIT_ID) IN (SELECT ORG_UNIT_ID
FROM (SELECT SUM(DECODE(a.year_s, 1994, a.month_03, 0) )
src_amount , a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID
from LEDGER_STAT a
where (((a.IDENTITY_CODE = 0)
or (a.IDENTITY_CODE = 1)
or (a.IDENTITY_CODE = 700)
or (a.IDENTITY_CODE = 779)))
and (a.identity_code) IN (select distinct a.parent_identity_code
from FSI_M_DATA_IDENTITY_DETAIL a
where a.identity_code in (10993)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994)
GROUP BY a.GL_ACCOUNT_ID, a.PRODUCT_ID, a.ORG_UNIT_ID) ))
WHERE RW = 1
AND mTotal <> 0
AND ColVal <> 0) Drv ON (Src.ORG_UNIT_ID = Drv.ORG_UNIT_ID) )
IVW_SOURCE
where IVWSRC_AMOUNT <> 0
GROUP BY ORG_UNIT_ID, GL_ACCOUNT_ID, PRODUCT_ID ) SOURCE ON
(TARGET.FINANCIAL_ELEM_ID = 457.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = SOURCE.GL_ACCOUNT_ID
AND TARGET.COMMON_COA_ID = 821.000000
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.YEAR_S = 1994
AND TARGET.IDENTITY_CODE = 10993.000000
AND TARGET.ISO_CURRENCY_CD = 'USD'
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D' ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_03 = TARGET.MONTH_03 + SOURCE.BALANCE_AMOUNT,
BALANCE_TYPE_CD = 101, ytd_03 = ytd_02 + SOURCE.BALANCE_AMOUNT ,
ytd_04 = ytd_03 + month_04 , ytd_05 = ytd_04 + month_05 , ytd_06
= ytd_05 + month_06 , ytd_07 = ytd_06 + month_07 , ytd_08 =
ytd_07 + month_08 , ytd_09 = ytd_08 + month_09 , ytd_10 = ytd_09
+ month_10 , ytd_11 = ytd_10 + month_11 , ytd_12 = ytd_11 +
month_12 WHEN NOT MATCHED THEN
INSERT (FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, ACCUM_TYPE_CD, BALANCE_TYPE_CD,
CONSOLIDATION_CD, CURRENCY_TYPE_CD, ISO_CURRENCY_CD,
IDENTITY_CODE, MONTH_01, MONTH_02, MONTH_03, MONTH_04, MONTH_05,
MONTH_06, MONTH_07, MONTH_08, MONTH_09, MONTH_10, MONTH_11,
MONTH_12, YTD_01, YTD_02, YTD_03, YTD_04, YTD_05, YTD_06, YTD_07,
YTD_08, YTD_09, YTD_10, YTD_11, YTD_12, YEAR_S )
VALUES ( 457.000000, SOURCE.ORG_UNIT_ID, SOURCE.GL_ACCOUNT_ID,
821.000000, SOURCE.PRODUCT_ID, 'D' , 101 , 100 , 0 , 'USD' ,
10993.000000 , 0.0, 0.0, SOURCE.BALANCE_AMOUNT, 0.0, 0.0, 0.0,
0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0, 0, SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , 1994)