BOM和Rounting检核是否建立或建立是否完整

BOM和Rounting检核是否建立或建立是否完整
SELECT
A.INVENTORY_ITEM_ID,
A.ORGANIZATION_ID,
A.ASSEMBLY,
A.WIP_SUPPLY_TYPE,
A.MAKE_BUY,
A.DESCRIPTION,
A.ATTRIBUTE1,
A.ATTRIBUTE5,
SUM(A.BOM) BOM,
SUM(A.ROUNTING) ROUNTING
FROM
(
SELECT MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 ASSEMBLY,
MSI.PLANNING_MAKE_BUY_CODE MAKE_BUY,
MSI.WIP_SUPPLY_TYPE,
MSI.DESCRIPTION,
MSI.ATTRIBUTE1,
MSI.ATTRIBUTE5,
1 BOM,
0 ROUNTING
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_STATUS_CODE NOT LIKE 'Inactive'
AND MSI.PLANNING_MAKE_BUY_CODE=1
AND MSI.ORGANIZATION_ID=104
--AND MSI.WIP_SUPPLY_TYPE<>6
AND NOT EXISTS
(SELECT 1
FROM BOM_BILL_OF_MATERIALS BOM
WHERE BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND BOM.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND BOM.ASSEMBLY_ITEM_ID=MSI.INVENTORY_ITEM_ID
)
UNION ALL
SELECT MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 ASSEMBLY,
MSI.PLANNING_MAKE_BUY_CODE MAKE_BUY,
MSI.WIP_SUPPLY_TYPE,
MSI.DESCRIPTION,
MSI.ATTRIBUTE1,
MSI.ATTRIBUTE5,
1 BOM,
0 ROUNTING
FROM MTL_SYSTEM_ITEMS_B MSI,
BOM_BILL_OF_MATERIALS BOM
WHERE MSI.INVENTORY_ITEM_STATUS_CODE NOT LIKE 'Inactive'
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND MSI.PLANNING_MAKE_BUY_CODE=1
AND MSI.ORGANIZATION_ID=104
--AND MSI.WIP_SUPPLY_TYPE<>6
AND MSI.INVENTORY_ITEM_ID=BOM.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID=BOM.ORGANIZATION_ID
AND NOT EXISTS
(SELECT 1
FROM BOM_INVENTORY_COMPONENTS BIC
WHERE ((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 BIC.BILL_SEQUENCE_ID=BOM.BILL_SEQUENCE_ID
)
UNION ALL
SELECT MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 ASSEMBLY,
MSI.PLANNING_MAKE_BUY_CODE MAKE_BUY,
MSI.WIP_SUPPLY_TYPE,
MSI.DESCRIPTION,
MSI.ATTRIBUTE1,
MSI.ATTRIBUTE5,
0 BOM,
1 ROUNTING
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_STATUS_CODE NOT LIKE 'Inactive'
AND MSI.PLANNING_MAKE_BUY_CODE=1
AND MSI.ORGANIZATION_ID=104
--AND MSI.WIP_SUPPLY_TYPE<>6
AND NOT EXISTS
(SELECT 1
FROM BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND BOR.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID=MSI.INVENTORY_ITEM_ID
)
UNION ALL
SELECT MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 ASSEMBLY,
MSI.PLANNING_MAKE_BUY_CODE MAKE_BUY,
MSI.WIP_SUPPLY_TYPE,
MSI.DESCRIPTION,
MSI.ATTRIBUTE1,
MSI.ATTRIBUTE5,
0 BOM,
1 ROUNTING
FROM MTL_SYSTEM_ITEMS_B MSI,
BOM_OPERATIONAL_ROUTINGS BOR
WHERE MSI.INVENTORY_ITEM_STATUS_CODE NOT LIKE 'Inactive'
AND MSI.PLANNING_MAKE_BUY_CODE=1
AND MSI.ORGANIZATION_ID=104
--AND MSI.WIP_SUPPLY_TYPE<>6
AND MSI.INVENTORY_ITEM_ID=BOR.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID= BOR.ORGANIZATION_ID
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND NOT EXISTS
(SELECT 1
FROM BOM_OPERATION_SEQUENCES BOS
WHERE ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
) OR
(TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND BOS.CHANGE_NOTICE IS NOT NULL))
AND BOS.ROUTING_SEQUENCE_ID=BOR.ROUTING_SEQUENCE_ID
)
UNION ALL
SELECT MSI.INVENTORY_ITEM_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 ASSEMBLY,
MSI.PLANNING_MAKE_BUY_CODE MAKE_BUY,
MSI.WIP_SUPPLY_TYPE,
MSI.DESCRIPTION,
MSI.ATTRIBUTE1,
MSI.ATTRIBUTE5,
0 BOM,
1 ROUNTING
FROM MTL_SYSTEM_ITEMS_B MSI,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS
WHERE MSI.INVENTORY_ITEM_STATUS_CODE NOT LIKE 'Inactive'
AND MSI.PLANNING_MAKE_BUY_CODE=1
AND MSI.ORGANIZATION_ID=104
--AND MSI.WIP_SUPPLY_TYPE<>6
AND MSI.INVENTORY_ITEM_ID=BOR.ASSEMBLY_ITEM_ID
AND MSI.ORGANIZATION_ID= BOR.ORGANIZATION_ID
AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND BOR.ROUTING_SEQUENCE_ID =BOS.ROUTING_SEQUENCE_ID
AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
) OR
(TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND BOS.CHANGE_NOTICE IS NOT NULL))
AND NOT EXISTS
(SELECT 1
FROM BOM_OPERATION_RESOURCES
WHERE OPERATION_SEQUENCE_ID=
BOS.OPERATION_SEQUENCE_ID
)
) A
GROUP BY
A.ORGANIZATION_ID,A.INVENTORY_ITEM_ID,A.ASSEMBLY,A.WIP_SUPPLY_TYPE,A.MAKE_BUY,A.DESCRIPTION,
A.ATTRIBUTE1,A.ATTRIBUTE5

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41594/viewspace-50556/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41594/viewspace-50556/