View: WIP_EAM_OSP_V

Product:	WIP - Work in Process
Description:	This view shows all the resources for outside processing along with the requisitions and purchase orders information
Implementation/DBA Data:	APPS.WIP_EAM_OSP_V

View Text

SELECT RQL.WIP_ENTITY_ID
, RQL.WIP_OPERATION_SEQ_NUM
, RQL.WIP_RESOURCE_SEQ_NUM
, RQL.ORG_ID
, TO_NUMBER(NULL)
, RQH.SEGMENT1
, RQH.REQUISITION_HEADER_ID
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, RQL.ITEM_DESCRIPTION
, UOM.UOM_CODE
, RQL.UNIT_PRICE
, RQL.CURRENCY_CODE
, RQL.QUANTITY
, RQL.QUANTITY
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, POV.VENDOR_NAME
, POV.SEGMENT1
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, RQL.ITEM_ID
, RQL.CLOSED_CODE
, TO_CHAR(NULL)
, RQH.AUTHORIZATION_STATUS
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
FROM PO_REQUISITION_LINES_ALL RQL
, PO_REQUISITION_HEADERS_ALL RQH
, PO_LINE_TYPES PLT
, MTL_UNITS_OF_MEASURE UOM
, PO_VENDORS POV
WHERE RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
AND RQL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND RQL.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE
AND RQL.VENDOR_ID = POV.VENDOR_ID (+)
AND UPPER(RQH.AUTHORIZATION_STATUS) NOT IN ('CANCELLED'
, 'REJECTED'
, 'SYSTEM_SAVED')
AND RQL.LINE_LOCATION_ID IS NULL
AND UPPER(NVL(RQL.CANCEL_FLAG
, 'N')) <> 'Y'
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')) = 'Y'
AND RQL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND RQL.WIP_ENTITY_ID IS NOT NULL UNION ( SELECT PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.WIP_RESOURCE_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, PD.PO_RELEASE_ID
, RQH.SEGMENT1 REQ_NUMBER
, RQH.REQUISITION_HEADER_ID
, POH.SEGMENT1 PO_NUMBER
, POH.PO_HEADER_ID
, POL.ITEM_DESCRIPTION
, UOM.UOM_CODE
, POL.UNIT_PRICE
, POH.CURRENCY_CODE
, RQL.QUANTITY REQ_LINE_QTY
, POL.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED)
, TO_DATE(NULL)
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, POV.VENDOR_NAME
, POV.SEGMENT1 VENDOR_NO
, TO_NUMBER(NULL)
, POL.CANCEL_FLAG
, POL.ITEM_ID
, RQL.CLOSED_CODE
, POL.CLOSED_CODE
, RQH.AUTHORIZATION_STATUS
, POH.AUTHORIZATION_STATUS
, POL.PO_LINE_ID
FROM PO_DISTRIBUTIONS_ALL PD
, PO_LINE_TYPES PLT
, MTL_UNITS_OF_MEASURE UOM
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLLOC
, PO_VENDORS POV
, PO_REQUISITION_HEADERS_ALL RQH
, PO_REQUISITION_LINES_ALL RQL
, PO_REQ_DISTRIBUTIONS_ALL RQD
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID(+)
AND RQD.REQUISITION_LINE_ID = RQL.REQUISITION_LINE_ID(+)
AND RQD.DISTRIBUTION_ID(+) = PD.REQ_DISTRIBUTION_ID
AND POLLOC.PO_LINE_ID = POL.PO_LINE_ID
AND POLLOC.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')) = 'Y'
AND PD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND POL.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE (+)
AND UPPER(NVL(POL.CANCEL_FLAG
, 'N')) <> 'Y'
AND PD.WIP_ENTITY_ID IS NOT NULL GROUP BY PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.WIP_RESOURCE_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, PD.PO_RELEASE_ID
, RQH.SEGMENT1
, RQH.REQUISITION_HEADER_ID
, POH.SEGMENT1
, POH.PO_HEADER_ID
, POL.ITEM_DESCRIPTION
, UOM.UOM_CODE
, POL.UNIT_PRICE
, POH.CURRENCY_CODE
, RQL.QUANTITY
, POL.QUANTITY
, POV.VENDOR_NAME
, POV.SEGMENT1
, POL.CANCEL_FLAG
, POL.ITEM_ID
, RQL.CLOSED_CODE
, POL.CLOSED_CODE
, RQH.AUTHORIZATION_STATUS
, POH.AUTHORIZATION_STATUS
, POL.PO_LINE_ID )

Columns