View: CSP_EOO_CANCEL_RECOMM_V

Product:	CSP - Spares Management
Description:	This view lists orders that can be canceled due to an excess on order notification.
Implementation/DBA Data:	APPS.CSP_EOO_CANCEL_RECOMM_V

View Text

SELECT NVL(SUP.TO_ORG_PRIMARY_QUANTITY
, 0) QUANTITY
, SUP.ITEM_ID ITEM_ID
, SUP.TO_ORGANIZATION_ID ORGANIZATION_ID
, REQ.SEGMENT1 REQUISITION
, ORD.ORDER_NUMBER INTERNAL_ORDER
, TO_CHAR(NULL) PURCHASE_ORDER
, TO_CHAR(NULL) WIP_ORDER
, DECODE(SUPPLY_TYPE_CODE
, 'RECEIVING'
, FL1.MEANING
, 'SHIPMENT'
, FL2.MEANING
, 'REQ'
, DECODE(REQ.TRANSFERRED_TO_OE_FLAG
, 'Y'
, OL.MEANING
, PLC.DISPLAYED_FIELD)) STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM MTL_SUPPLY SUP
, PO_REQUISITION_HEADERS_ALL REQ
, PO_REQUISITION_LINES_ALL PRL
, OE_ORDER_HEADERS_ALL ORD
, OE_ORDER_LINES_ALL OEL
, OE_LOOKUPS OL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
, FND_LOOKUPS FL1
, FND_LOOKUPS FL2
WHERE SUP.SUPPLY_TYPE_CODE IN ('REQ'
, 'SHIPMENT'
, 'RECEIVING')
AND SUP.DESTINATION_TYPE_CODE = 'INVENTORY'
AND REQ.REQUISITION_HEADER_ID = SUP.REQ_HEADER_ID
AND PRL.REQUISITION_LINE_ID = SUP.REQ_LINE_ID
AND PRL.REQUISITION_HEADER_ID = SUP.REQ_HEADER_ID
AND ORD.ORIG_SYS_DOCUMENT_REF(+) = REQ.SEGMENT1
AND ORD.ORDER_SOURCE_ID(+) = 10
AND OEL.ORDER_SOURCE_ID(+) = 10
AND OEL.SOURCE_DOCUMENT_ID(+) = PRL.REQUISITION_HEADER_ID
AND OEL.ORIG_SYS_LINE_REF(+) = PRL.LINE_NUM
AND OEL.FLOW_STATUS_CODE = OL.LOOKUP_CODE(+)
AND OL.LOOKUP_TYPE(+) = 'LINE_FLOW_STATUS'
AND PLC.LOOKUP_CODE = REQ.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND FL1.LOOKUP_CODE = 'RECEIVING'
AND FL1.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE'
AND FL2.LOOKUP_CODE = 'SHIPMENT'
AND FL2.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE' UNION SELECT NVL(SUP.TO_ORG_PRIMARY_QUANTITY
, 0) QUANTITY
, SUP.ITEM_ID ITEM_ID
, SUP.TO_ORGANIZATION_ID ORGANIZATION_ID
, TO_CHAR(NULL) REQUISITION
, TO_NUMBER(NULL) INTERNAL_ORDER
, PO.SEGMENT1 PURCHASE_ORDER
, TO_CHAR(NULL) WIP_ORDER
, DECODE(SUP.SUPPLY_TYPE_CODE
, 'RECEIVING'
, FL1.MEANING
, 'SHIPMENT'
, FL2.MEANING
, 'PO'
, NVL(PO_HEADERS_SV3.GET_PO_STATUS(SUP.PO_HEADER_ID)
, PLC.DISPLAYED_FIELD)) STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM MTL_SUPPLY SUP
, PO_HEADERS_ALL PO
, PO_LINES_ALL POL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
, FND_LOOKUPS FL1
, FND_LOOKUPS FL2
WHERE PO.PO_HEADER_ID = SUP.PO_HEADER_ID
AND SUP.SUPPLY_TYPE_CODE IN ('PO'
, 'SHIPMENT'
, 'RECEIVING')
AND SUP.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PLC.LOOKUP_CODE = 'INCOMPLETE'
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND POL.PO_LINE_ID = SUP.PO_LINE_ID
AND POL.PO_HEADER_ID = PO.PO_HEADER_ID
AND PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND FL1.LOOKUP_CODE = 'RECEIVING'
AND FL1.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE'
AND FL2.LOOKUP_CODE = 'SHIPMENT'
AND FL2.LOOKUP_TYPE = 'CSP_SUPPLY_TYPE' UNION SELECT POL.QUANTITY QUANTITY
, POL.ITEM_ID ITEM_ID
, POL.DESTINATION_ORGANIZATION_ID ORGANIZATION_ID
, POH.SEGMENT1 REQUISITION
, TO_NUMBER(NULL) INTERNAL_ORDER
, TO_CHAR(NULL) PURCHASE_ORDER
, TO_CHAR(NULL) WIP_ORDER
, PLC.DISPLAYED_FIELD STATUS
, PLT.LINE_TYPE LINE_TYPE
FROM PO_REQUISITION_HEADERS_ALL POH
, PO_REQUISITION_LINES_ALL POL
, PO_LOOKUP_CODES PLC
, PO_LINE_TYPES PLT
WHERE POH.AUTHORIZATION_STATUS = 'INCOMPLETE'
AND POL.REQUISITION_HEADER_ID = POH.REQUISITION_HEADER_ID
AND POL.DESTINATION_TYPE_CODE = 'INVENTORY'
AND PLC.LOOKUP_CODE = POH.AUTHORIZATION_STATUS
AND PLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PLT.LINE_TYPE_ID = POL.LINE_TYPE_ID UNION SELECT (NVL(START_QUANTITY
, 0) - NVL(QUANTITY_COMPLETED
, 0) - NVL(QUANTITY_SCRAPPED
, 0)) QUANTITY
, WDJ.PRIMARY_ITEM_ID ITEM_ID
, WDJ.ORGANIZATION_ID ORGANIZATION_ID
, TO_CHAR(NULL) REQUISITION
, TO_NUMBER(NULL) INTERNAL_ORDER
, TO_CHAR(NULL) PURCHASE_ORDER
, WE.WIP_ENTITY_NAME WIP_ORDER
, ML.MEANING STATUS
, WDJ.CLASS_CODE LINE_TYPE
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, MFG_LOOKUPS ML
WHERE JOB_TYPE IN (1
, 3)
AND STATUS_TYPE IN (1
, 3)
AND (NVL(START_QUANTITY
, 0) - NVL(QUANTITY_COMPLETED
, 0) - NVL(QUANTITY_SCRAPPED
, 0)) > 0
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML.LOOKUP_CODE = STATUS_TYPE
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID UNION SELECT (DAILY_PRODUCTION_RATE * LEAST(0
, PROCESSING_WORK_DAYS) - QUANTITY_COMPLETED) QUANTITY
, WRI.PRIMARY_ITEM_ID ITEM_ID
, WRI.ORGANIZATION_ID ORGANIZATION_ID
, TO_CHAR(NULL) REQUISITION
, TO_NUMBER(NULL) INTERNAL_ORDER
, TO_CHAR(NULL) PURCHASE_ORDER
, WE.WIP_ENTITY_NAME WIP_ORDER
, ML.MEANING STATUS
, WRI.CLASS_CODE LINE_TYPE
FROM WIP_REPETITIVE_ITEMS WRI
, WIP_REPETITIVE_SCHEDULES WRS
, WIP_ENTITIES WE
, MFG_LOOKUPS ML
WHERE WRS.STATUS_TYPE IN (1
, 3)
AND WRS.ORGANIZATION_ID = WRI.ORGANIZATION_ID
AND WRS.WIP_ENTITY_ID = WRI.WIP_ENTITY_ID
AND WRS.LINE_ID = WRI.LINE_ID
AND (DAILY_PRODUCTION_RATE * LEAST(0
, PROCESSING_WORK_DAYS)- QUANTITY_COMPLETED) > 0
AND ML.LOOKUP_CODE = WRS.STATUS_TYPE
AND ML.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND WE.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID

Columns