View: EAM_WO_REQ_PO_LITE_V

Product:	EAM - Enterprise Asset Management
Description:	This is a light weight version of EAM_WORK_ORDER_REQ_PO_V.
Implementation/DBA Data:	APPS.EAM_WO_REQ_PO_LITE_V

View Text

SELECT PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1 REQ_NUMBER
, POH.SEGMENT1 PO_NUMBER
, NVL (PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION) ITEM_DESCRIPTION
, NVL(UOM.UOM_CODE
, RQLUOM.UOM_CODE) UOM_CODE
, NVL(PD.UNIT_PRICE
, RQL.UNIT_PRICE) UNIT_PRICE
, NVL(POH.CURRENCY_CODE
, RQL.CURRENCY_CODE) CURRENCY_CODE
, RQL.QUANTITY REQ_LINE_QTY
, ( CASE WHEN (UPPER(RQH.AUTHORIZATION_STATUS) IN('REJECTED'
, 'SYSTEM_SAVED'
, 'CANCELLED')) THEN RQL.QUANTITY WHEN (UPPER(RQH.AUTHORIZATION_STATUS) = 'APPROVED'
AND RQL.CANCEL_FLAG = 'Y') THEN RQL.QUANTITY ELSE 0 END) REQ_LINE_QTY_CANCELLED
, PD.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED ) QUANTITY_RECEIVED
, SUM(PD.QUANTITY_ORDERED)-SUM(PD.QUANTITY_CANCELLED) PO_QUANTITY_ORDERED
, SUM(PD.QUANTITY_CANCELLED) PO_QUANTITY_CANCELLED
, PD.ITEM_ID
, PD.WIP_RESOURCE_SEQ_NUM
, DECODE(POH.VENDOR_ID
, NULL
, RQL.VENDOR_ID
, POH.VENDOR_ID ) VENDOR_ID
, RQL.SUGGESTED_VENDOR_NAME
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID
, NVL(PD.CATEGORY_ID
, RQL.CATEGORY_ID) CATEGORY_ID
, NVL(POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE) NEED_BY_DATE
, NVL(PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE) ORDER_TYPE_LOOKUP_CODE
, DECODE ( PD.ORDER_TYPE_LOOKUP_CODE
, NULL
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED) ) AMOUNT
, SUM(PD.AMOUNT_DELIVERED) AMOUNT_DELIVERED
, SUM( RQL.AMOUNT) RQL_AMOUNT_ORDERED
, SUM(PD.AMOUNT_ORDERED) PO_AMOUNT_ORDERED
, CASE WHEN (RQH.AUTHORIZATION_STATUS = 'APPROVED'
AND RQL.CANCEL_FLAG ='Y') THEN 'CANCELLED' ELSE RQH.AUTHORIZATION_STATUS END REQUISITION_STATUS
, CASE WHEN ( POH.AUTHORIZATION_STATUS IS NULL
AND POH.PO_HEADER_ID <> NULL ) THEN 'INCOMPLETE' WHEN (POH.AUTHORIZATION_STATUS = 'APPROVED'
AND PD.CANCEL_FLAG ='Y') THEN 'CANCELLED' WHEN (POH.AUTHORIZATION_STATUS = 'APPROVED'
AND PD. CLOSED_FLAG ='Y') THEN 'CLOSED' ELSE POH.AUTHORIZATION_STATUS END PO_STATUS
FROM PO_LINE_TYPES PLT
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL POLLOC
, PO_REQUISITION_HEADERS_ALL RQH
, PO_REQUISITION_LINES_ALL RQL
, MTL_UNITS_OF_MEASURE UOM
, MTL_UNITS_OF_MEASURE RQLUOM
, ( 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.ITEM_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, POL.VENDOR_PRODUCT_NUM
, PD1.PO_LINE_ID
, PD1.PO_RELEASE_ID
, POL.CATEGORY_ID
, POL.ORDER_TYPE_LOOKUP_CODE
, PD1.AMOUNT_ORDERED
, PD1.AMOUNT_DELIVERED
, PD1.PO_HEADER_ID
, PD1.LINE_LOCATION_ID
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.LINE_TYPE_ID
, PD1.DESTINATION_TYPE_CODE
, POL.CANCEL_FLAG
, POL.CLOSED_FLAG
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 POLLOC.PO_LINE_ID = PD.PO_LINE_ID
AND POLLOC.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND POLLOC.LINE_LOCATION_ID = RQL.LINE_LOCATION_ID (+)
AND PD.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE (+)
AND RQL.UNIT_MEAS_LOOKUP_CODE = RQLUOM.UNIT_OF_MEASURE (+)
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 PD.WIP_ENTITY_ID IS NOT NULL GROUP BY PD.WIP_ENTITY_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1
, POH.SEGMENT1
, PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION
, UOM.UOM_CODE
, RQLUOM.UOM_CODE
, PD.CATEGORY_ID
, RQL.CATEGORY_ID
, PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.UNIT_PRICE
, PD.UNIT_PRICE
, POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE
, RQL.CURRENCY_CODE
, POH.CURRENCY_CODE
, RQL.QUANTITY
, PD.QUANTITY
, PD.ITEM_ID
, PD.WIP_RESOURCE_SEQ_NUM
, RQL.VENDOR_ID
, POH.VENDOR_ID
, RQL.SUGGESTED_VENDOR_NAME
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID
, RQH.AUTHORIZATION_STATUS
, RQL.CANCEL_FLAG
, PD.CANCEL_FLAG
, PD.CLOSED_FLAG
, POH.AUTHORIZATION_STATUS UNION SELECT RQL.WIP_ENTITY_ID
, RQL.WIP_OPERATION_SEQ_NUM
, RQL.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1 REQ_NUMBER
, POH.SEGMENT1 PO_NUMBER
, NVL(PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION )
, NVL(UOM.UOM_CODE
, RQLUOM.UOM_CODE)
, NVL(PD.UNIT_PRICE
, RQL.UNIT_PRICE)
, NVL(POH.CURRENCY_CODE
, RQL.CURRENCY_CODE)
, RQL.QUANTITY REQ_LINE_QTY
, ( CASE WHEN (UPPER(RQH.AUTHORIZATION_STATUS) IN('REJECTED'
, 'SYSTEM_SAVED'
, 'CANCELLED')) THEN RQL.QUANTITY WHEN (UPPER(RQH.AUTHORIZATION_STATUS) = 'APPROVED'
AND RQL.CANCEL_FLAG = 'Y') THEN RQL.QUANTITY ELSE 0 END) REQ_LINE_QTY_CANCELLED
, PD.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED) QUANTITY_RECEIVED
, SUM(PD.QUANTITY_ORDERED)-SUM(PD.QUANTITY_CANCELLED) PO_QUANTITY_ORDERED
, SUM(PD.QUANTITY_CANCELLED) PO_QUANTITY_CANCELLED
, RQL.ITEM_ID
, RQL.WIP_RESOURCE_SEQ_NUM
, DECODE(POH.VENDOR_ID
, NULL
, RQL.VENDOR_ID
, POH.VENDOR_ID)
, RQL.SUGGESTED_VENDOR_NAME
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID
, NVL(PD.CATEGORY_ID
, RQL.CATEGORY_ID)
, NVL(POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE)
, NVL(PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE)
, DECODE ( PD.ORDER_TYPE_LOOKUP_CODE
, NULL
, SUM( RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED))
, SUM(PD.AMOUNT_DELIVERED ) AMOUNT_DELIVERED
, SUM(RQL.AMOUNT) RQL_AMOUNT_ORDERED
, SUM(PD.AMOUNT_ORDERED) PO_AMOUNT_ORDERED
, CASE WHEN ( RQH.AUTHORIZATION_STATUS = 'APPROVED'
AND RQL.CANCEL_FLAG ='Y' ) THEN 'CANCELLED' ELSE RQH.AUTHORIZATION_STATUS END REQUISITION_STATUS
, CASE WHEN ( POH.AUTHORIZATION_STATUS IS NULL
AND POH.PO_HEADER_ID <> NULL ) THEN 'INCOMPLETE' WHEN (POH.AUTHORIZATION_STATUS = 'APPROVED'
AND PD.CANCEL_FLAG ='Y') THEN 'CANCELLED' WHEN (POH.AUTHORIZATION_STATUS = 'APPROVED '
AND PD.CLOSED_FLAG ='Y') THEN 'CLOSED' ELSE POH.AUTHORIZATION_STATUS END PO_STATUS
FROM PO_LINE_TYPES PLT
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL POLLOC
, PO_REQUISITION_HEADERS_ALL RQH
, PO_REQUISITION_LINES_ALL RQL
, MTL_UNITS_OF_MEASURE UOM
, MTL_UNITS_OF_MEASURE RQLUOM
, ( SELECT PD1.PO_HEADER_ID
, PD1.PO_LINE_ID
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.ORDER_TYPE_LOOKUP_CODE
, POL.UNIT_PRICE
, POL.QUANTITY
, PD1.PO_RELEASE_ID
, PD1.LINE_LOCATION_ID
, POL.VENDOR_PRODUCT_NUM
, PD1.QUANTITY_DELIVERED
, PD1.QUANTITY_ORDERED
, PD1.QUANTITY_CANCELLED
, PD1.AMOUNT_ORDERED
, PD1.AMOUNT_DELIVERED
, POL.CANCEL_FLAG
, POL.CLOSED_FLAG
FROM PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL PD1
WHERE POL.PO_LINE_ID = PD1.PO_LINE_ID ) PD
WHERE PD.PO_HEADER_ID = POH.PO_HEADER_ID(+)
AND RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID (+)
AND RQL.LINE_LOCATION_ID = POLLOC.LINE_LOCATION_ID (+)
AND POLLOC.PO_LINE_ID = PD.PO_LINE_ID (+)
AND POLLOC.LINE_LOCATION_ID = PD.LINE_LOCATION_ID (+)
AND PD.UNIT_MEAS_LOOKUP_CODE = UOM.UNIT_OF_MEASURE (+)
AND RQL.UNIT_MEAS_LOOKUP_CODE = RQLUOM.UNIT_OF_MEASURE(+)
AND RQL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N') ) = 'N'AND RQL.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND UPPER(NVL(RQH.CANCEL_FLAG
, 'N')) <> 'Y'
AND RQL.WIP_ENTITY_ID IS NOT NULL GROUP BY RQL.WIP_ENTITY_ID
, RQL.WIP_OPERATION_SEQ_NUM
, RQL.DESTINATION_ORGANIZATION_ID
, RQH.SEGMENT1
, POH.SEGMENT1
, PD.ITEM_DESCRIPTION
, RQL.ITEM_DESCRIPTION
, RQLUOM.UOM_CODE
, UOM.UOM_CODE
, PD.CATEGORY_ID
, RQL.CATEGORY_ID
, PD.ORDER_TYPE_LOOKUP_CODE
, RQL.ORDER_TYPE_LOOKUP_CODE
, RQL.UNIT_PRICE
, PD.UNIT_PRICE
, POLLOC.NEED_BY_DATE
, RQL.NEED_BY_DATE
, RQL.CURRENCY_CODE
, POH.CURRENCY_CODE
, RQL.QUANTITY
, PD.QUANTITY
, RQL.ITEM_ID
, RQL.WIP_RESOURCE_SEQ_NUM
, RQL.VENDOR_ID
, POH.VENDOR_ID
, RQL.SUGGESTED_VENDOR_NAME
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID
, RQH.AUTHORIZATION_STATUS
, RQL.CANCEL_FLAG
, PD.CANCEL_FLAG
, PD.CLOSED_FLAG
, POH.AUTHORIZATION_STATUS

Columns