VIEW: APPS.MRP_COMPONENTS_SC_V

Source

View Text - Preformatted

SELECT MFQ.QUERY_ID , ASSY.INVENTORY_ITEM_ID , COMP_NAME.ITEM_NUMBER , ASSY_NAME.ITEM_NUMBER , ASSY.ORGANIZATION_ID , PARAM.ORGANIZATION_CODE , ASSY.COMPILE_DESIGNATOR , ASSY.CHANGE_NOTICE , ASSY.USING_ASSEMBLY_ID , ASSY.OPERATION_SEQ_NUM , ASSY.LAST_UPDATE_DATE , ASSY.LAST_UPDATED_BY , ASSY.CREATION_DATE , ASSY.CREATED_BY , ASSY.REVISION , DECODE(ASSY.FROM_END_ITEM_UNIT_NUMBER, NULL, ASSY.EFFECTIVITY_DATE, TO_DATE(NULL)) , DECODE(ASSY.DRIVING_ITEM_ID, NULL, TO_DATE(NULL), DRV.INVENTORY_USE_UP_DATE) , ASSY.USE_UP_CODE , 0 , ASSY.OPERATION_OFFSET_PERCENT , ASSY.DRIVING_ITEM_ID , ASSY.OPTIONAL_COMPONENT , ASSY.DISABLE_DATE , LU.MEANING , ASSY.ALTERNATE_BOM_DESIGNATOR , ASSY.FROM_END_ITEM_UNIT_NUMBER , ASSY.TO_END_ITEM_UNIT_NUMBER FROM MRP_SYSTEM_ITEMS DRV, MTL_ITEM_FLEXFIELDS ASSY_NAME, MTL_ITEM_FLEXFIELDS COMP_NAME, MFG_LOOKUPS LU, MRP_BOM_COMPONENTS ASSY, MTL_PARAMETERS PARAM, MRP_FORM_QUERY MFQ WHERE LU.LOOKUP_TYPE = 'WIP_SUPPLY' AND LU.LOOKUP_CODE = ASSY.WIP_SUPPLY_TYPE AND NVL(ASSY.DRIVING_ITEM_ID, ASSY.INVENTORY_ITEM_ID) = DRV.INVENTORY_ITEM_ID AND ASSY.COMPILE_DESIGNATOR = DRV.COMPILE_DESIGNATOR AND ASSY.ORGANIZATION_ID = DRV.ORGANIZATION_ID AND ASSY.DISABLE_DATE IS NOT NULL AND ASSY.USING_ASSEMBLY_ID = ASSY_NAME.INVENTORY_ITEM_ID AND ASSY.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID AND ASSY.INVENTORY_ITEM_ID = COMP_NAME.INVENTORY_ITEM_ID AND ASSY.ORGANIZATION_ID = COMP_NAME.ORGANIZATION_ID AND PARAM.ORGANIZATION_ID = ASSY.ORGANIZATION_ID AND MFQ.NUMBER1 = ASSY.USING_ASSEMBLY_ID AND MFQ.NUMBER2 = ASSY.ORGANIZATION_ID AND MFQ.CHAR1 = ASSY.COMPILE_DESIGNATOR UNION ALL SELECT MFQ.QUERY_ID , ASSY.INVENTORY_ITEM_ID , COMP_NAME.ITEM_NUMBER , ASSY_NAME.ITEM_NUMBER , ASSY.ORGANIZATION_ID , PARAM.ORGANIZATION_CODE , ASSY.COMPILE_DESIGNATOR , ASSY.CHANGE_NOTICE , ASSY.USING_ASSEMBLY_ID , ASSY.OPERATION_SEQ_NUM , ASSY.LAST_UPDATE_DATE , ASSY.LAST_UPDATED_BY , ASSY.CREATION_DATE , ASSY.CREATED_BY , ASSY.REVISION , DECODE(ASSY.FROM_END_ITEM_UNIT_NUMBER, NULL, ASSY.EFFECTIVITY_DATE, TO_DATE(NULL)) , DECODE(ASSY.DRIVING_ITEM_ID, NULL, TO_DATE(NULL), DRV.INVENTORY_USE_UP_DATE) , ASSY.USE_UP_CODE , ASSY.USAGE_QUANTITY , ASSY.OPERATION_OFFSET_PERCENT , ASSY.DRIVING_ITEM_ID , ASSY.OPTIONAL_COMPONENT , ASSY.DISABLE_DATE , LU.MEANING , ASSY.ALTERNATE_BOM_DESIGNATOR , ASSY.FROM_END_ITEM_UNIT_NUMBER , ASSY.TO_END_ITEM_UNIT_NUMBER FROM MRP_SYSTEM_ITEMS DRV, MTL_ITEM_FLEXFIELDS ASSY_NAME, MTL_ITEM_FLEXFIELDS COMP_NAME, MFG_LOOKUPS LU, MRP_BOM_COMPONENTS ASSY, MTL_PARAMETERS PARAM, MRP_FORM_QUERY MFQ WHERE LU.LOOKUP_TYPE = 'WIP_SUPPLY' AND LU.LOOKUP_CODE = ASSY.WIP_SUPPLY_TYPE AND NVL(ASSY.DRIVING_ITEM_ID, ASSY.INVENTORY_ITEM_ID) = DRV.INVENTORY_ITEM_ID AND ASSY.COMPILE_DESIGNATOR = DRV.COMPILE_DESIGNATOR AND ASSY.ORGANIZATION_ID = DRV.ORGANIZATION_ID AND ASSY.USING_ASSEMBLY_ID = ASSY_NAME.INVENTORY_ITEM_ID AND ASSY.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID AND ASSY.INVENTORY_ITEM_ID = COMP_NAME.INVENTORY_ITEM_ID AND ASSY.ORGANIZATION_ID = COMP_NAME.ORGANIZATION_ID AND PARAM.ORGANIZATION_ID = ASSY.ORGANIZATION_ID AND MFQ.NUMBER1 = ASSY.USING_ASSEMBLY_ID AND MFQ.NUMBER2 = ASSY.ORGANIZATION_ID AND MFQ.CHAR1 = ASSY.COMPILE_DESIGNATOR AND ASSY.DISABLE_DATE IS NULL

View Text - HTML Formatted

SELECT MFQ.QUERY_ID
, ASSY.INVENTORY_ITEM_ID
, COMP_NAME.ITEM_NUMBER
, ASSY_NAME.ITEM_NUMBER
, ASSY.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, ASSY.COMPILE_DESIGNATOR
, ASSY.CHANGE_NOTICE
, ASSY.USING_ASSEMBLY_ID
, ASSY.OPERATION_SEQ_NUM
, ASSY.LAST_UPDATE_DATE
, ASSY.LAST_UPDATED_BY
, ASSY.CREATION_DATE
, ASSY.CREATED_BY
, ASSY.REVISION
, DECODE(ASSY.FROM_END_ITEM_UNIT_NUMBER
, NULL
, ASSY.EFFECTIVITY_DATE
, TO_DATE(NULL))
, DECODE(ASSY.DRIVING_ITEM_ID
, NULL
, TO_DATE(NULL)
, DRV.INVENTORY_USE_UP_DATE)
, ASSY.USE_UP_CODE
, 0
, ASSY.OPERATION_OFFSET_PERCENT
, ASSY.DRIVING_ITEM_ID
, ASSY.OPTIONAL_COMPONENT
, ASSY.DISABLE_DATE
, LU.MEANING
, ASSY.ALTERNATE_BOM_DESIGNATOR
, ASSY.FROM_END_ITEM_UNIT_NUMBER
, ASSY.TO_END_ITEM_UNIT_NUMBER
FROM MRP_SYSTEM_ITEMS DRV
, MTL_ITEM_FLEXFIELDS ASSY_NAME
, MTL_ITEM_FLEXFIELDS COMP_NAME
, MFG_LOOKUPS LU
, MRP_BOM_COMPONENTS ASSY
, MTL_PARAMETERS PARAM
, MRP_FORM_QUERY MFQ
WHERE LU.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU.LOOKUP_CODE = ASSY.WIP_SUPPLY_TYPE
AND NVL(ASSY.DRIVING_ITEM_ID
, ASSY.INVENTORY_ITEM_ID) = DRV.INVENTORY_ITEM_ID
AND ASSY.COMPILE_DESIGNATOR = DRV.COMPILE_DESIGNATOR
AND ASSY.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND ASSY.DISABLE_DATE IS NOT NULL
AND ASSY.USING_ASSEMBLY_ID = ASSY_NAME.INVENTORY_ITEM_ID
AND ASSY.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID
AND ASSY.INVENTORY_ITEM_ID = COMP_NAME.INVENTORY_ITEM_ID
AND ASSY.ORGANIZATION_ID = COMP_NAME.ORGANIZATION_ID
AND PARAM.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
AND MFQ.NUMBER1 = ASSY.USING_ASSEMBLY_ID
AND MFQ.NUMBER2 = ASSY.ORGANIZATION_ID
AND MFQ.CHAR1 = ASSY.COMPILE_DESIGNATOR UNION ALL SELECT MFQ.QUERY_ID
, ASSY.INVENTORY_ITEM_ID
, COMP_NAME.ITEM_NUMBER
, ASSY_NAME.ITEM_NUMBER
, ASSY.ORGANIZATION_ID
, PARAM.ORGANIZATION_CODE
, ASSY.COMPILE_DESIGNATOR
, ASSY.CHANGE_NOTICE
, ASSY.USING_ASSEMBLY_ID
, ASSY.OPERATION_SEQ_NUM
, ASSY.LAST_UPDATE_DATE
, ASSY.LAST_UPDATED_BY
, ASSY.CREATION_DATE
, ASSY.CREATED_BY
, ASSY.REVISION
, DECODE(ASSY.FROM_END_ITEM_UNIT_NUMBER
, NULL
, ASSY.EFFECTIVITY_DATE
, TO_DATE(NULL))
, DECODE(ASSY.DRIVING_ITEM_ID
, NULL
, TO_DATE(NULL)
, DRV.INVENTORY_USE_UP_DATE)
, ASSY.USE_UP_CODE
, ASSY.USAGE_QUANTITY
, ASSY.OPERATION_OFFSET_PERCENT
, ASSY.DRIVING_ITEM_ID
, ASSY.OPTIONAL_COMPONENT
, ASSY.DISABLE_DATE
, LU.MEANING
, ASSY.ALTERNATE_BOM_DESIGNATOR
, ASSY.FROM_END_ITEM_UNIT_NUMBER
, ASSY.TO_END_ITEM_UNIT_NUMBER
FROM MRP_SYSTEM_ITEMS DRV
, MTL_ITEM_FLEXFIELDS ASSY_NAME
, MTL_ITEM_FLEXFIELDS COMP_NAME
, MFG_LOOKUPS LU
, MRP_BOM_COMPONENTS ASSY
, MTL_PARAMETERS PARAM
, MRP_FORM_QUERY MFQ
WHERE LU.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU.LOOKUP_CODE = ASSY.WIP_SUPPLY_TYPE
AND NVL(ASSY.DRIVING_ITEM_ID
, ASSY.INVENTORY_ITEM_ID) = DRV.INVENTORY_ITEM_ID
AND ASSY.COMPILE_DESIGNATOR = DRV.COMPILE_DESIGNATOR
AND ASSY.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND ASSY.USING_ASSEMBLY_ID = ASSY_NAME.INVENTORY_ITEM_ID
AND ASSY.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID
AND ASSY.INVENTORY_ITEM_ID = COMP_NAME.INVENTORY_ITEM_ID
AND ASSY.ORGANIZATION_ID = COMP_NAME.ORGANIZATION_ID
AND PARAM.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
AND MFQ.NUMBER1 = ASSY.USING_ASSEMBLY_ID
AND MFQ.NUMBER2 = ASSY.ORGANIZATION_ID
AND MFQ.CHAR1 = ASSY.COMPILE_DESIGNATOR
AND ASSY.DISABLE_DATE IS NULL