View: WIP_EAM_DIRECT_ITEMS_V

Product:	WIP - Work in Process
Description:	Direct Items for EAM work orders
Implementation/DBA Data:	APPS.WIP_EAM_DIRECT_ITEMS_V

View Text

SELECT RQL.WIP_ENTITY_ID
, RQL.WIP_OPERATION_SEQ_NUM
, RQL.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1
, 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)
, TO_NUMBER(NULL) AS PO_HEADER_ID
, RQH.REQUISITION_HEADER_ID
, WED.DIRECT_ITEM_SEQUENCE_ID
, RQL.CATEGORY_ID
, TO_NUMBER(NULL)
, RQL.REQUISITION_LINE_ID
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.AMOUNT
, TO_NUMBER(NULL) AS AMOUNT_DELIVERED
FROM PO_REQUISITION_LINES_ALL RQL
, PO_REQUISITION_HEADERS_ALL RQH
, PO_LINE_TYPES PLT
, MTL_UNITS_OF_MEASURE UOM
, PO_VENDORS POV
, WIP_EAM_DIRECT_ITEMS WED
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')) = 'N'
AND RQL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND RQL.WIP_ENTITY_ID IS NOT NULL
AND RQL.ITEM_DESCRIPTION = WED.DESCRIPTION (+)
AND RQL.WIP_ENTITY_ID = WED.WIP_ENTITY_ID (+)
AND RQL.WIP_OPERATION_SEQ_NUM = WED.OPERATION_SEQ_NUM (+)
AND RQL.DESTINATION_ORGANIZATION_ID = WED.ORGANIZATION_ID (+) UNION ( SELECT PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1 REQ_NUMBER
, POH.SEGMENT1 PO_NUMBER
, PD.ITEM_DESCRIPTION
, UOM.UOM_CODE
, PD.UNIT_PRICE
, POH.CURRENCY_CODE
, RQL.QUANTITY REQ_LINE_QTY
, SUM(PD.QUANTITY_ORDERED) 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)
, PD.CANCEL_FLAG
, PD.ITEM_ID
, RQL.CLOSED_CODE
, PD.CLOSED_CODE
, RQH.AUTHORIZATION_STATUS
, POH.AUTHORIZATION_STATUS
, PD.PO_LINE_ID
, POH.PO_HEADER_ID
, RQH.REQUISITION_HEADER_ID
, WED.DIRECT_ITEM_SEQUENCE_ID
, PD.CATEGORY_ID
, PD.PO_RELEASE_ID
, RQL.REQUISITION_LINE_ID
, PD.ORDER_TYPE_LOOKUP_CODE
, PD.AMOUNT_ORDERED
, PD.AMOUNT_DELIVERED
FROM PO_LINE_TYPES PLT
, MTL_UNITS_OF_MEASURE UOM
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL POLLOC
, PO_VENDORS POV
, PO_REQUISITION_HEADERS_ALL RQH
, PO_REQUISITION_LINES_ALL RQL
, PO_REQ_DISTRIBUTIONS_ALL RQD
, WIP_EAM_DIRECT_ITEMS WED
, (SELECT PD1.WIP_ENTITY_ID
, PD1.WIP_OPERATION_SEQ_NUM
, PD1.DESTINATION_ORGANIZATION_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_PRICE
, POL.QUANTITY
, PD1.QUANTITY_DELIVERED
, PD1.QUANTITY_ORDERED
, PD1.QUANTITY_CANCELLED
, POL.CLOSED_CODE
, POL.PO_LINE_ID
, POL.PO_HEADER_ID
, PD1.REQ_DISTRIBUTION_ID
, PD1.LINE_LOCATION_ID
, POL.LINE_TYPE_ID
, PD1.DESTINATION_TYPE_CODE
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.CANCEL_FLAG
, POL.ITEM_ID
, POL.CATEGORY_ID
, PD1.PO_RELEASE_ID
, POL.ORDER_TYPE_LOOKUP_CODE
, PD1.AMOUNT_ORDERED
, PD1.AMOUNT_DELIVERED
FROM PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL PD1
WHERE POL.PO_LINE_ID = PD1.PO_LINE_ID) PD
WHERE POH.PO_HEADER_ID = PD.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 = PD.PO_LINE_ID
AND POLLOC.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PD.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')) = 'N'
AND PD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND PD.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE (+)
AND UPPER(NVL(PD.CANCEL_FLAG
, 'N')) <> 'Y'
AND PD.WIP_ENTITY_ID IS NOT NULL
AND PD.ITEM_DESCRIPTION = WED.DESCRIPTION (+)
AND PD.WIP_ENTITY_ID = WED.WIP_ENTITY_ID (+)
AND PD.WIP_OPERATION_SEQ_NUM = WED. OPERATION_SEQ_NUM (+)
AND PD.DESTINATION_ORGANIZATION_ID = WED.ORGANIZATION_ID (+) GROUP BY PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1
, POH.SEGMENT1
, PD.ITEM_DESCRIPTION
, UOM.UOM_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE
, PD.ORDER_TYPE_LOOKUP_CODE
, PD.UNIT_PRICE
, RQL.AMOUNT
, PD.AMOUNT_ORDERED
, PD.AMOUNT_DELIVERED
, POH.CURRENCY_CODE
, RQL.QUANTITY
, PD.QUANTITY
, POV.VENDOR_NAME
, POV.SEGMENT1
, PD.CANCEL_FLAG
, PD.ITEM_ID
, RQL.CLOSED_CODE
, PD.CLOSED_CODE
, RQH.AUTHORIZATION_STATUS
, POH.AUTHORIZATION_STATUS
, PD.PO_LINE_ID
, POH.PO_HEADER_ID
, RQH.REQUISITION_HEADER_ID
, WED.DIRECT_ITEM_SEQUENCE_ID
, PD.CATEGORY_ID
, PD.PO_RELEASE_ID
, RQL.REQUISITION_LINE_ID )

Columns