WITH C AS (
SELECT * FROM (
SELECT PRODUCT_ID,
PLATFORM,
SUB_PLATFORM,
PROD_CATEGORY,
MATERIAL_NO,
MATERIAL_UNIT,
MATERIAL_DESC,
MATERIAL_PRICE,
ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID, MATERIAL_NO ORDER BY MATERIAL_TYPE DESC) AS RN
FROM VW_MC_MERGE_MATERIAL
WHERE MERGE_MC_ID =1937059976879095809
AND MATERIAL_TYPE IN ('Z006', 'Z007')) C1 WHERE C1.rn =1
),
A as(
SELECT * FROM VW_RD_MERGE_WIP A1 WHERE A1.MERGE_MC_ID = 1937059976879095809
),
B as(
SELECT * FROM VW_TINY_RE_LOT_AMOUNT_DATA B1 WHERE B1.MERGE_MC_ID = 1937059976879095809
)
SELECT NVL(A.PRODUCT_ID, 'NA') AS PRODUCT_ID,
NVL(C.PROD_CATEGORY, 'NA') AS PROD_CATEGORY,
NVL(C.PLATFORM, 'NA') AS PLATFORM,
NVL(C.SUB_PLATFORM, 'NA') AS SUB_PLATFORM,
NVL(LOT.COMPANY, 'NA') AS VAL_A,
A.LOT_ID,
COALESCE(OEM.LOT_FAB,LOT.FAB, 'NA') AS LOT_FAB,
A.LOT_STATUS,
NVL(A.LOT_TYPE, 'NA') AS LOT_TYPE,
NVL(C.MATERIAL_NO, 'NA') AS FINISHED_MATERIAL_NO,
'13020000' AS GENERAL_LEDGER_ACCOUNT,
'在产品' AS GENERAL_LEDGER_ACCOUNT_DESC,
'CNY' AS CURRENCY,
NVL(A.COMPONENT_QTY, 0) AS TOTAL_STOCK,
NVL(A.COMPONENT_QTY, 0) AS ORDER_NUMBER,
NVL(C.MATERIAL_DESC, 'NA') AS CHI_DESC,
NVL(C.MATERIAL_UNIT, 'NA') AS UNIT,
NVL((CASE A.LOT_STATUS WHEN 'SHIPPED' THEN A.COMPONENT_QTY ELSE 0 END), 0) AS FINISHED_NUMBER,
NVL((CASE WHEN A.LOT_STATUS IN ('SCRAPPED', 'TERMINATED') THEN A.COMPONENT_QTY ELSE 0 END), 0) AS BAD_NUMBER,
NVL((CASE A.LOT_STATUS WHEN 'BONDED' THEN A.COMPONENT_QTY ELSE 0 END), 0) AS BONDED_NUMBER,
NVL((CASE WHEN A.LOT_STATUS NOT IN('SHIPPED', 'SCRAPPED', 'TERMINATED', 'BONDED') THEN A.COMPONENT_QTY ELSE 0 END), 0) AS PROCESS_NUMBER,
NVL(B.AMT_S43ACS01, 0) AS AMT_S43ACS01,
NVL(B.AMT_S43ACS02, 0) AS AMT_S43ACS02,
NVL(B.AMT_S43ACS03, 0) AS AMT_S43ACS03,
NVL(B.AMT_S43ACS04, 0) AS AMT_S43ACS04,
NVL(B.AMT_S43ACS05, 0) AS AMT_S43ACS05,
NVL(B.AMT_S43ACS06, 0) AS AMT_S43ACS06,
NVL(B.AMT_S43ACS07, 0) AS AMT_S43ACS07,
NVL(B.AMT_S43ACS08, 0) AS AMT_S43ACS08,
NVL(B.AMT_S43ACS09, 0) AS AMT_S43ACS09,
NVL(B.AMT_S43ACS10, 0) AS AMT_S43ACS10,
NVL(B.AMT_S43ACS11, 0) AS AMT_S43ACS11,
NVL(B.AMT_S43ACS12, 0) AS AMT_S43ACS12,
NVL(B.AMT_S43ACS13, 0) AS AMT_S43ACS13,
NVL(B.AMT_S43ACS14, 0) AS AMT_S43ACS14,
NVL(B.AMT_S43ACS15, 0) AS AMT_S43ACS15,
NVL(B.AMT_S43ACS16, 0) AS AMT_S43ACS16,
NVL(B.AMT_S43ACS17, 0) AS AMT_S43ACS17,
NVL(B.AMT_S43ACS18, 0) AS AMT_S43ACS18,
NVL(B.AMT_S43ACS19, 0) AS AMT_S43ACS19,
NVL(B.AMT_S43ACS20, 0) AS AMT_S43ACS20,
NVL(B.AMT_S43ACS21, 0) AS AMT_S43ACS21,
NVL(B.AMT_S43ACS22, 0) AS AMT_S43ACS22,
NVL(B.AMT_S43ACS23, 0) AS AMT_S43ACS23,
NVL(B.AMT_S43ACS24, 0) AS AMT_S43ACS24,
NVL(B.AMT_S43ACS25, 0) AS AMT_S43ACS25,
NVL(B.AMT_S43ACS26, 0) AS AMT_S43ACS26,
NVL(B.AMT_S43ACS27, 0) AS AMT_S43ACS27,
NVL(B.AMT_S43ACS28, 0) AS AMT_S43ACS28,
NVL(B.AMT_S43ACS29, 0) AS AMT_S43ACS29,
NVL(B.AMT_S43ACS30, 0) AS AMT_S43ACS30,
NVL(B.AMT_S43ACS31, 0) AS AMT_S43ACS31,
NVL(B.SUM_S43ACS01, 0) AS SUM_S43ACS01,
NVL(B.SUM_S43ACS02, 0) AS SUM_S43ACS02,
NVL(B.SUM_S43ACS03, 0) AS SUM_S43ACS03,
NVL(B.SUM_S43ACS04, 0) AS SUM_S43ACS04,
NVL(B.SUM_S43ACS05, 0) AS SUM_S43ACS05,
NVL(B.SUM_S43ACS06, 0) AS SUM_S43ACS06,
NVL(B.SUM_S43ACS07, 0) AS SUM_S43ACS07,
NVL(B.SUM_S43ACS08, 0) AS SUM_S43ACS08,
NVL(B.SUM_S43ACS09, 0) AS SUM_S43ACS09,
NVL(B.SUM_S43ACS10, 0) AS SUM_S43ACS10,
NVL(B.SUM_S43ACS11, 0) AS SUM_S43ACS11,
NVL(B.SUM_S43ACS12, 0) AS SUM_S43ACS12,
(NVL(B.SUM_S43ACS01, 0) + NVL(B.SUM_S43ACS02, 0) +
NVL(B.SUM_S43ACS03, 0) + NVL(B.SUM_S43ACS04, 0) +
NVL(B.SUM_S43ACS05, 0) + NVL(B.SUM_S43ACS06, 0) +
NVL(B.SUM_S43ACS07, 0) + NVL(B.SUM_S43ACS08, 0) +
NVL(B.SUM_S43ACS09, 0) + NVL(B.SUM_S43ACS10, 0) +
NVL(B.SUM_S43ACS11, 0) + NVL(B.SUM_S43ACS12, 0)) AS ACTUAL_COST,
(NVL(C.MATERIAL_PRICE, 0) * NVL(A.COMPONENT_QTY, 0)) AS STANDARD_COST
FROM A
LEFT JOIN B ON A.MERGE_MC_ID=B.MERGE_MC_ID AND A.LOT_ID=B.LOT_ID
LEFT JOIN C ON C.PRODUCT_ID = A.PRODUCT_ID AND C.RN = 1
LEFT JOIN VW_CFG_FAB_LOT LOT ON LOT.LOT_ID = (CASE WHEN INSTR(A.LOT_ID, '.') > 0 THEN SUBSTR(A.LOT_ID, 0, INSTR(A.LOT_ID, '.') - 1) ELSE A.LOT_ID END)
LEFT JOIN (SELECT DISTINCT MASTER_OEM_LOT, MASTER_ORIGINAL_LOT, ORIGINAL_FAB AS LOT_FAB FROM VW_LOT_TRANSFER) OEM ON OEM.MASTER_OEM_LOT = (CASE WHEN INSTR(A.LOT_ID, '.') > 0 THEN SUBSTR(A.LOT_ID, 0, INSTR(A.LOT_ID, '.') - 1) ELSE A.LOT_ID END)
优化这个Oracle查询
扫一扫
6363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
