MERGE INTO LEDGER_STAT TARGET
USING (
SELECT C.*
FROM (
SELECT 11002.000000 identity_code, 1994 year_s,'D'
accum_type_cd,100 consolidation_cd, 101 balance_type_cd, 'USD'
ISO_CURRENCY_CD, 457.000000 FINANCIAL_ELEM_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, 821.000000 COMMON_COA_ID, PRODUCT_ID, (
SOURCE_AMOUNT + leaf_amount )*1 BALANCE_AMOUNT
from (SELECT /*+ parallel(ledger_stat, 8) */row_number() over (partition by a.PRODUCT_ID,
a.GL_ACCOUNT_ID, a.ORG_UNIT_ID order by a.PRODUC T_ID, a.GL_ACCOUNT_ID, a.ORG_UNIT_ID)rw , SUM(DECODE(COMMON_COA_ID, 101, 0,
DECODE(a.year_s, 1994, a.month_03, 0)) ) over (partition by
a.PRODUCT_ID, a.GL_ACCOUNT_ID, a.ORG_UNIT_ID) SOURCE_AMOUNT,
SUM(DECODE(COMMON_COA_ID, 101, DECODE(a.year_s, 1994, a.month_03,
0), 0)) over ( partition by a.PRODUCT_ID, a.GL_ACCOUNT_ID,
a.ORG_UNIT_ID ) leaf_amount , a.PRODUCT_ID, a.GL_ACCOUNT_ID,
a.ORG_UNIT_ID
from LEDGER_STAT a
where(a.COMMON_COA_ID=820
OR a.COMMON_COA_ID=101)
and (((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 (11002)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type in ('S' , 'D') )
and a.year_s IN (1994) )
where rw = 1 ) C ) 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 = 11002.000000
AND TARGET.ISO_CURRENCY_CD = SOURCE.ISO_CURRENCY_CD
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 ,
SOURCE.ISO_CURRENCY_CD, 11002.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)