用户需求是想通过WIP之BOM与标准BOM之间的差异来计算损耗。所以需要先展开BOM。

      曾试过一个SQL展开BOM情况，速度比较慢，而且还比较麻烦，于是根据公司标准BOM中最多2层虚拟BOM，自己写一个速度快很多。

1.建立一个TABLE,用来根据工单生产料件进行展开BOM，并插入其中
CREATE TABLE CUX_WIP_BOM
(
ORGANIZATION_ID NUMBER,
ASSEMBLY_ITEM_ID NUMBER,
COMPONENT_ITEM_ID NUMBER,
COMPONENT_QUANTITY NUMBER,
COMPONENT_EXTEND_QUANTITY NUMBER,
PARA_DATE VARCHAR(6),
RUN_DATA DATE
)
2. 写一个procedure，用来展开工单生产料件BOM，并插入上面table

CREATE OR REPLACE PROCEDURE CUX_WIP_BOM_EXPLORE(P_YEARMONTH IN VARCHAR2) AS
I NUMBER;
J NUMBER;
H NUMBER;
F NUMBER;
E NUMBER;
BEGIN
DELETE FROM CUX_WIP_BOM;

COMMIT;

BEGIN
FOR K IN --根据工单机型号，展开第一阶标准BOM
(SELECT DISTINCT --剔除重复记录

WE.ORGANIZATION_ID,
WE.PRIMARY_ITEM_ID ASSEMBLY_ITEM_ID,
MSI.SEGMENT1,
BIC.COMPONENT_ITEM_ID,
BIC.COMPONENT_QUANTITY,
BIC.COMPONENT_YIELD_FACTOR,
ITEM.ITEM_TYPE
FROM
WIP_DISCRETE_JOBS WDJ ,
WIP_ENTITIES WE,
MTL_SYSTEM_ITEMS_B MSI,
BOM_BILL_OF_MATERIALS BOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B ITEM
WHERE WE.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND WE.PRIMARY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MSI.SEGMENT1 NOT LIKE 'X%'
AND TO_CHAR(WDJ.DATE_CLOSED,'YYYYMM')=P_YEARMONTH
AND MSI.ORGANIZATION_ID=BOM.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID=BOM.ASSEMBLY_ITEM_ID
AND BOM.BILL_SEQUENCE_ID=BIC.BILL_SEQUENCE_ID
AND ((TO_CHAR(BIC.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BIC.DISABLE_DATE IS NULL OR TO_CHAR(BIC.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
)
OR (TO_CHAR(BIC.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BIC.CHANGE_NOTICE IS NOT NULL )
)
AND TO_CHAR(BIC.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND ITEM.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND ITEM.INVENTORY_ITEM_ID=BIC.COMPONENT_ITEM_ID
) LOOP
I:=K.COMPONENT_QUANTITY/K.COMPONENT_YIELD_FACTOR;
IF K.ITEM_TYPE<>'XNJ' THEN
INSERT INTO CUX_WIP_BOM VALUES(K.ORGANIZATION_ID,
K.ASSEMBLY_ITEM_ID,
K.COMPONENT_ITEM_ID,
K.COMPONENT_QUANTITY,
I,
P_YEARMONTH,
SYSDATE
);
COMMIT;

ELSE

FOR L IN (SELECT --展开第一层中含虚拟BOM
BOM.ORGANIZATION_ID,
BOM.ASSEMBLY_ITEM_ID,
BIC.COMPONENT_ITEM_ID,
BIC.COMPONENT_QUANTITY,
BIC.COMPONENT_YIELD_FACTOR,
ITEM.SEGMENT1,
ITEM.ITEM_TYPE
FROM BOM_BILL_OF_MATERIALS BOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B ITEM
WHERE 1=1
AND BOM.BILL_SEQUENCE_ID=BIC.BILL_SEQUENCE_ID
AND ((TO_CHAR(BIC.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BIC.DISABLE_DATE IS NULL OR TO_CHAR(BIC.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
)
OR (TO_CHAR(BIC.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BIC.CHANGE_NOTICE IS NOT NULL )
)
AND TO_CHAR(BIC.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND ITEM.INVENTORY_ITEM_ID=BIC.COMPONENT_ITEM_ID
AND ITEM.ORGANIZATION_ID=K.ORGANIZATION_ID
AND BOM.ORGANIZATION_ID=K.ORGANIZATION_ID
AND BOM.ASSEMBLY_ITEM_ID=K.COMPONENT_ITEM_ID ) LOOP

J:=L.COMPONENT_QUANTITY/L.COMPONENT_YIELD_FACTOR;
H:=I*J;

IF L.ITEM_TYPE<>'XNJ' THEN
INSERT INTO CUX_WIP_BOM VALUES(K.ORGANIZATION_ID,
K.ASSEMBLY_ITEM_ID,
L.COMPONENT_ITEM_ID,
L.COMPONENT_QUANTITY,
H,
P_YEARMONTH,
SYSDATE);
COMMIT;

 

ELSE
FOR M IN (SELECT --展开第二层中含虚拟BOM
BOM.ORGANIZATION_ID,
BOM.ASSEMBLY_ITEM_ID,
BIC.COMPONENT_ITEM_ID,
BIC.COMPONENT_QUANTITY,
BIC.COMPONENT_YIELD_FACTOR,
ITEM.SEGMENT1,
ITEM.ITEM_TYPE
FROM BOM_BILL_OF_MATERIALS BOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B ITEM
WHERE 1=1
AND BOM.BILL_SEQUENCE_ID=BIC.BILL_SEQUENCE_ID
AND ((TO_CHAR(BIC.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BIC.DISABLE_DATE IS NULL OR TO_CHAR(BIC.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
)
OR (TO_CHAR(BIC.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd') AND BIC.CHANGE_NOTICE IS NOT NULL )
)
AND TO_CHAR(BIC.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND ITEM.INVENTORY_ITEM_ID=BIC.COMPONENT_ITEM_ID
AND ITEM.ORGANIZATION_ID=L.ORGANIZATION_ID
AND BOM.ORGANIZATION_ID=L.ORGANIZATION_ID
AND BOM.ASSEMBLY_ITEM_ID=L.COMPONENT_ITEM_ID
AND ITEM.SEGMENT1 NOT LIKE 'X%' ) LOOP

F:=M.COMPONENT_QUANTITY/M.COMPONENT_YIELD_FACTOR;
E:=F*H;

INSERT INTO CUX_WIP_BOM VALUES(K.ORGANIZATION_ID,
K.ASSEMBLY_ITEM_ID,
M.COMPONENT_ITEM_ID,
M.COMPONENT_QUANTITY,
E,
P_YEARMONTH,
SYSDATE);

END LOOP;

END IF;
END LOOP;

END IF ;

END LOOP;

END;
--END ;
END CUX_WIP_BOM_EXPLORE;