2/17

DROP VIEW "3060_FIN_FC_PRED"."CAPEX_FC_LOAD_1_KPI_AGG";
CREATE VIEW "3060_FIN_FC_PRED"."CAPEX_FC_LOAD_1_KPI_AGG" AS
SELECT
TO_DATE(A.FC_TIMESTAMP, 'YYYY-MM-DD') AS FC_TIMESTAMP,
A."Country Text",
ITEM,
A.UNIT,
A.DATE,
SUM(A.VALUE * 1000000) AS VALUE_LC 
FROM "3060_FIN_FC_PRED"."CAPEX_LARGE_GROWTH_1_DATE_PROCESSING_TBL" AS A 
WHERE LOCATE(UPPER(ITEM), 'BACKLOG') <> 0 
AND A.VALUE IS NOT NULL 
AND A.VALUE <> 0 
AND LEFT(A.DATE, 4) = LEFT(A.FC_TIMESTAMP, 4) AND SUBSTRING(A.FC_TIMESTAMP, 6, 2) <> '12' 
AND "Country Text" IS NOT NULL 
AND KPI_LEVEL1 = 'Capex'
GROUP BY
TO_DATE(A.FC_TIMESTAMP, 'YYYY-MM-DD'),
A."Country Text",
ITEM,
A.UNIT,
A.DATE
UNION ALL
SELECT
'2020-12-01' AS FC_TIMESTAMP,
"Country Text",
ITEM,
UNIT,
DATE,
VALUE_LC 
FROM "3060_FIN_FC_PRED"."BUDGET_2021_FINAL_TBL" 
WHERE ITEM IN (SELECT ITEM FROM "3060_FIN_FC_PRED"."FC_ITEM_HIERARCHY" WHERE KPI_LEVEL1 = 'Capex')