此SQL用于ERP上线之初检测Routing 建立情况
SELECT OOD.ORGANIZATION_CODE,
MSI.SEGMENT1 ITEM,
BOR.ALTERNATE_ROUTING_DESIGNATOR ,
BOS.OPERATION_SEQ_NUM,
-- BSO.OPERATION_CODE,
BOS.REFERENCE_FLAG,
BD.DEPARTMENT_CODE,
BOR.COMPLETION_SUBINVENTORY,
BORE.RESOURCE_SEQ_NUM,
BR.RESOURCE_CODE,
BR.UNIT_OF_MEASURE,
DECODE(BORE.BASIS_TYPE,1,'ITEM',2,'LOT') BASIS_TYPE,
BORE.USAGE_RATE_OR_AMOUNT,
BORE.USAGE_RATE_OR_AMOUNT_INVERSE,
DECODE(BDR.AVAILABLE_24_HOURS_FLAG,1,'Y',2,'N') AVAILABLE_24_HOURS_FLAG,
BORE.ASSIGNED_UNITS,
DECODE(BORE.SCHEDULE_FLAG,1,'Y',2,'N') SCHEDULE_FLAG,
ML.MEANING AUTOCHARGE_TYPE
FROM BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
BOM_OPERATION_RESOURCES BORE,
--BOM_STANDARD_OPERATIONS BSO,
BOM_DEPARTMENTS BD,
BOM_RESOURCES BR,
BOM_DEPARTMENT_RESOURCES BDR,
MFG_LOOKUPS ML,
MTL_SYSTEM_ITEMS_B MSI,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE BOR.ROUTING_SEQUENCE_ID=BOS.ROUTING_SEQUENCE_ID
AND BORE.OPERATION_SEQUENCE_ID=BOS.OPERATION_SEQUENCE_ID
--AND BSO.STANDARD_OPERATION_ID=BOS.STANDARD_OPERATION_ID
AND BOS.DEPARTMENT_ID=BD.DEPARTMENT_ID
AND BD.ORGANIZATION_ID=X
AND BORE.RESOURCE_ID=BR.RESOURCE_ID
AND BOR.ORGANIZATION_ID=BR.ORGANIZATION_ID
AND BORE.RESOURCE_ID=BDR.RESOURCE_ID
AND BD.DEPARTMENT_ID=BDR.DEPARTMENT_ID
AND BOR.ASSEMBLY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND BOR.ORGANIZATION_ID=MSI.ORGANIZATION_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 TO_CHAR(BOS.IMPLEMENTATION_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
AND BORE.AUTOCHARGE_TYPE=ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE='BOM_AUTOCHARGE_TYPE'
and bor.ORGANIZATION_ID=X
AND MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID
ORDER BY OOD.ORGANIZATION_CODE,MSI.SEGMENT1,BOS.OPERATION_SEQ_NUM,BORE.RESOURCE_SEQ_NUM