MERGE INTO LEDGER_STAT TARGET
USING (
SELECT 11040.000000 identity_code, 1994 year_s, 'D'
accum_type_cd , 100 consolidation_cd , 101 balance_type_cd,'USD'
ISO_CURRENCY_CD, 10900.000000 FINANCIAL_ELEM_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, SOURCE_AMOUNT * 1
BALANCE_AMOUNT
from (SELECT row_number() over (partition by a.PRODUCT_ID,
a.COMMON_COA_ID
order by a.PRODUCT_ID, a.COMMON_COA_ID)rw, SUM(DECODE(a.year_s, 1994,
a.month_03, 0) * 2 ) over (partition by a.PRODUCT_ID,
a.COMMON_COA_ID) SOURCE_AMOUNT , a.PRODUCT_ID, a.COMMON_COA_ID ,
a.GL_ACCOUNT_ID, a.ORG_UNIT_ID
from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID=2100
and a.GL_ACCOUNT_ID IN (SELECT /*+ parallel (ofsa_idt_rollup,
8) */ 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 (11040)
and a.as_of_date = '03/ 31/ 1994'
and a.src_drv_type = 'S' )
and a.year_s IN (1994) )
where rw = 1 ) SOURCE ON (TARGET.FINANCIAL_ELEM_ID = 10900.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = SOURCE.GL_ACCOUNT_ID
AND TARGET.COMMON_COA_ID = SOURCE.COMMON_COA_ID
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.YEAR_S = 1994
AND TARGET.IDENTITY_CODE = 11040.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* 2) +
SOURCE.BALANCE_AMOUNT )/ 3 , ytd_04 = ((ytd_03* 3) + month_04 )/
4 , ytd_05 = ((ytd_04* 4) + month_05 )/ 5 , ytd_06 = ((ytd_05* 5)
+ month_06 )/ 6 , ytd_07 = ((ytd_06* 6) + month_07 )/ 7 , ytd_08
= ((ytd_07* 7) + month_08 )/ 8 , ytd_09 = ((ytd_08* 8) + month_09
/ 9 , ytd_10 = ((ytd_09* 9) + month_10 )/ 10 , ytd_11 =
((ytd_10* 10) + month_11 )/ 11 , ytd_12 = ((ytd_11* 11) +
month_12 )/ 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 ( 10900.000000, SOURCE.ORG_UNIT_ID, SOURCE.GL_ACCOUNT_ID,
SOURCE.COMMON_COA_ID, SOURCE.PRODUCT_ID, 'D' , 101 , 100 , 0 ,
SOURCE.ISO_CURRENCY_CD, 11040.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) / 3, ((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4, ((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5, ((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6, ((((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6)* 6) / 7,
(((((((((( (SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) /
6)* 6) / 7)* 7) / 8, (((((((((((( (SOURCE.BALANCE_AMOUNT) / 3)*
3) / 4)* 4) / 5)* 5) / 6)* 6) / 7)* 7) / 8)* 8) / 9,
(((((((((((((( (SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5)
/ 6)* 6) / 7)* 7) / 8)* 8) / 9)* 9) / 10, ((((((((((((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6)* 6) / 7)*
7) / 8)* 8) / 9)* 9) / 10)* 10) / 11, ((((((((((((((((((
(SOURCE.BALANCE_AMOUNT) / 3)* 3) / 4)* 4) / 5)* 5) / 6)* 6) / 7)*
7) / 8)* 8) / 9)* 9) / 10)* 10) / 11)* 11) / 12 , 1994)