MERGE INTO TA_EP110 A
USING (SELECT SALE_DATE AS PLAN_DATE,
BRAND,
SUM(DECODE(PART_DIV, 'D', NVL(RSALE_AMT, 0), 0)) AS D_RUN_AMT,
SUM(DECODE(PART_DIV, 'G', NVL(RSALE_AMT, 0), 0)) AS G_RUN_AMT,
SUM(DECODE(PART_DIV, 'K', NVL(RSALE_AMT, 0), 0)) AS K_RUN_AMT,
SUM(DECODE(PART_DIV, 'M', NVL(RSALE_AMT, 0), 0)) AS M_RUN_AMT,
SUM(DECODE(PART_DIV, 'T', NVL(RSALE_AMT, 0), 0)) AS T_RUN_AMT
FROM TA_EE020
WHERE SALE_DATE = TO_DATE(IN_DATE, 'YYYYMMDD')
GROUP BY SALE_DATE,
BRAND) B
ON (A.PLAN_DATE = B.PLAN_DATE AND
A.BRAND = B.BRAND)
WHEN MATCHED THEN
UPDATE SET A.D_RUN_AMT = B.D_RUN_AMT,
A.G_RUN_AMT = B.G_RUN_AMT,
A.K_RUN_AMT = B.K_RUN_AMT,
A.M_RUN_AMT = B.M_RUN_AMT,
A.T_RUN_AMT = B.T_RUN_AMT
WHEN NOT MATCHED THEN
INSERT VALUES ( B.PLAN_DATE,
B.BRAND,
0,
0,
0,
0,
0,
B.D_RUN_AMT,
B.G_RUN_AMT,
B.K_RUN_AMT,
B.M_RUN_AMT,
B.T_RUN_AMT
);