WITH
-- 第一步：获取计划信息，并判断是 Queen 还是 300works（IUI）下线
PLAN_DATA AS (
-- 原始数据
SELECT B.TECHNOLOGY TECH, A.productid PROD, 'IUI Start' REMARK
FROM DR01.SDB_TB_FABCITE_CREATELOT A
LEFT JOIN (SELECT UNIQUE PRODUCTNAME, TECHNOLOGY FROM SDB_TB_INFO_PRODUCT) B
ON A.productid = B.PRODUCTNAME
WHERE A.productid NOT LIKE 'XP%'
UNION ALL
-- 新增的虚拟行
SELECT '48NOR' AS TECH, '0A7K' AS PROD, 'Manual Input' AS REMARK
FROM DUAL
),
-- 第二步：获取 PROD 与 ZEROPRODUCT 映射（MES/IUI）
T1 AS (
SELECT *
FROM (
-- MES 中的映射
SELECT 'MES' SOURCE, a.TECHNOLOGY, PROD, STARTMATERIAL, ZEROPRODUCT, SUM(STARTQTY) AS TOTAL_STARTQTY
FROM (
SELECT TECHNOLOGY, SUBSTR(PRODUCTNAME, 1, 4) AS PROD, STARTMATERIAL, ZEROPRODUCT, STARTQTY
FROM BJTYS.SDB_TB_INFO_PRODUCT
WHERE STARTQTY > 0
) a
GROUP BY a.TECHNOLOGY, PROD, STARTMATERIAL, ZEROPRODUCT
UNION ALL
-- IUI 中的映射
SELECT 'IUI' SOURCE, a.TECHNOLOGY, PROD, STARTMATERIAL, ZEROPRODUCT, SUM(STARTQTY) AS TOTAL_STARTQTY
FROM (
SELECT TECHNOLOGY, SUBSTR(PRODUCTNAME, 1, 4) AS PROD, STARTMATERIAL, ZEROPRODUCT, STARTQTY
FROM DW01.SDB_TB_INFO_PRODUCT_P1@B2MFGDBP1
WHERE STARTQTY > 0
) a
GROUP BY a.TECHNOLOGY, PROD, STARTMATERIAL, ZEROPRODUCT
)
),
-- 第三步：获取当前库存中 LOT 的 MATERIALNO 信息（EX/EY 和 SOURCEE*）
T2 AS (
SELECT UNIQUE
AA.*,
BB.foupid,
CC.Plocation,
CASE
WHEN SubStr(AA.lotid, 2, 1) IN ('X', 'Y') THEN 'ZeroFlow'
WHEN SubStr(AA.lotid, 1, 6) = 'SOURCE' THEN 'RawMaterial'
ELSE ''
END AS CAT
FROM (
-- EX/EY 库存物料
SELECT
A.lotid,
A.componentqty QTY,
A.status,
A.productname PROD,
B.vendorid VENDOR,
B.comsumedmaterial materialno,
SubStr(b.inventoryid, 2, Length(b.inventoryid)) wafertype,
B.parentid,
B.qty parentqty,
A.startdate || ' ' || A.starttime starttime
FROM dw01.sdb_tb_wip_ship_P1@B2MFGDBP1 A
LEFT JOIN DW01.sdb_tb_inventory_lot_infor_P1@B2MFGDBP1 B
ON SUBSTR(A.LOTID, 1, 8) = SUBSTR(B.LOTID, 1, 8)
WHERE SubStr(A.lotid, 1, 2) IN ('EX', 'EY')
AND status = 'Inventory'
UNION ALL
-- SOURCEE* 原材料
SELECT
source_lot LOTID,
componentqty QTY,
'Inventory' STATUS,
sourceprod PROD,
vendorid VENDOR,
material_no materialno,
wafertype,
vendorlotid parentid,
componentqty parentqty,
createdatetime STARTTIME
FROM dw01.rpt_ms_source_lot_material_info@b2mfgdbp1
WHERE SubStr(source_lot, 1, 7) = 'SOURCEE'
) AA
LEFT JOIN (
SELECT foupid, lotid
FROM DW01.sdb_tb_smic_foup_slotmap_P1@B2MFGDBP1
) BB ON AA.LOTID = BB.LOTID
LEFT JOIN (
SELECT foupid, location Plocation
FROM DW01.sdb_tb_smic_foup_P1@B2MFGDBP1
) CC ON BB.FOUPID = CC.FOUPID
WHERE CC.Plocation IS NULL OR SubStr(CC.plocation, 1, 2) IN ('ES','EB','FS','FB','GS','GB','2S','US','UB')
)
-- 主查询：串联三张表，并应用 VENDOR 过滤规则
SELECT DISTINCT
P.TECH,
P.PROD, -- 原始计划产品
T1.TECHNOLOGY AS MASTER_TECH,
T1.PROD AS MASTER_PROD, -- 大 flow 主产品（通常和 PLAN_PROD 一致）
T1.ZEROPRODUCT,
T2.PROD AS CURRENT_MATCHED_PROD, -- 实际匹配到的 PROD（如 XPE00800120BNA1）
T2.MATERIALNO,
T2.VENDOR
FROM PLAN_DATA P
JOIN T1 ON SUBSTR(P.PROD, 1, 4) = T1.PROD
JOIN T2
ON (T1.ZEROPRODUCT = T2.PROD OR (T1.ZEROPRODUCT = 'XPE00800120BN' AND T2.PROD = 'XPE00800120BNA1'))
WHERE
T2.vendor in (SELECT vendor FROM dr01.sdb_tb_wfs_vendors_constraint WHERE prod=P.prod)
ORDER BY P.TECH DESC, P.PROD, T1.ZEROPRODUCT, T2.MATERIALNO;
我唯一的改动是WHERE
T2.vendor in (SELECT vendor FROM dr01.sdb_tb_wfs_vendors_constraint WHERE prod=P.prod)我想从得到的结果中筛掉vendors constraint表中不能用的vendor
最新发布
扫一扫
7172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
