View: EAM_WORK_ORDER_REQ_PO_V

Product:	EAM - Enterprise Asset Management
Description:	View shows purchase order and requisition details of a work order.
Implementation/DBA Data:	APPS.EAM_WORK_ORDER_REQ_PO_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)
, 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
, PD.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED )
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, PD.ITEM_ID
, PD.WIP_RESOURCE_SEQ_NUM
, DECODE(POH.VENDOR_ID
, NULL
, RQL.VENDOR_ID
, POH.VENDOR_ID )
, NVL(POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME)
, NVL(POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID)
, NVL(POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION)
, NVL(POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID)
, NVL(DECODE(POVC.FIRST_NAME
, NULL
, POVC.LAST_NAME
, DECODE( POVC.LAST_NAME
, NULL
, POVC.FIRST_NAME
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME) )
, RQL.SUGGESTED_VENDOR_CONTACT )
, NVL(POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE)
, NVL(PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE)
, 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)
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED)
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.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
, POL.VENDOR_PRODUCT_NUM
, PD1.PO_RELEASE_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, 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
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
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(RQH.AUTHORIZATION_STATUS
, 'APPROVED') ) NOT IN ('CANCELLED'
, 'REJECTED'
, 'SYSTEM_SAVED')
AND UPPER(NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')) = 'N'
AND PD.DESTINATION_TYPE_CODE = 'SHOP FLOOR'
AND UPPER(NVL(PD.CANCEL_FLAG
, 'N')) <> 'Y'
AND POH.VENDOR_ID = POV. VENDOR_ID (+)
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND POH.VENDOR_CONTACT_ID = POVC.VENDOR_CONTACT_ID (+)
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
, POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME
, POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION
, POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID
, POVC.FIRST_NAME
, POVC.LAST_NAME
, RQL.SUGGESTED_VENDOR_CONTACT
, POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE
, PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH .PO_HEADER_ID
, PD.PO_RELEASE_ID 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
, PD.QUANTITY PO_LINE_QTY
, SUM(PD.QUANTITY_DELIVERED)
, SUM(PD.QUANTITY_ORDERED)
, SUM(PD.QUANTITY_CANCELLED)
, RQL.ITEM_ID
, RQL.WIP_RESOURCE_SEQ_NUM
, DECODE(POH.VENDOR_ID
, NULL
, RQL.VENDOR_ID
, POH.VENDOR_ID)
, NVL(POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME)
, NVL(POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID)
, NVL(POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION)
, NVL(POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID)
, NVL(DECODE(POVC.FIRST_NAME
, NULL
, POVC.LAST_NAME
, DECODE( POVC.LAST_NAME
, NULL
, POVC.FIRST_NAME
, POVC.LAST_NAME || '
, ' || POVC.FIRST_NAME) )
, RQL.SUGGESTED_VENDOR_CONTACT )
, NVL(POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE)
, NVL(PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE)
, 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)
, SUM(RQL.AMOUNT)
, SUM(PD.AMOUNT_ORDERED)
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.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
, POL.VENDOR_PRODUCT_NUM
, PD1.PO_RELEASE_ID
, PD1.WIP_RESOURCE_SEQ_NUM
, 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
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
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(RQH.AUTHORIZATION_STATUS
, 'APPROVED') ) NOT IN ('CANCELLED'
, 'REJECTED'
, 'SYSTEM_SAVED')
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 POH.VENDOR_ID = POV.VENDOR_ID (+)
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID (+)
AND POH.VENDOR_CONTACT_ID = POVC.VENDOR_CONTACT_ID (+)
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
, POV.VENDOR_NAME
, RQL.SUGGESTED_VENDOR_NAME
, POH.VENDOR_SITE_ID
, RQL.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, RQL.SUGGESTED_VENDOR_LOCATION
, POH.VENDOR_CONTACT_ID
, RQL.VENDOR_CONTACT_ID
, POVC.FIRST_NAME
, POVC.LAST_NAME
, RQL.SUGGESTED_VENDOR_CONTACT
, POVC.PHONE
, RQL.SUGGESTED_VENDOR_PHONE
, PD.VENDOR_PRODUCT_NUM
, RQL.SUGGESTED_VENDOR_PRODUCT_CODE
, RQL.REQUISITION_LINE_ID
, PD.PO_LINE_ID
, RQH.REQUISITION_HEADER_ID
, POH.PO_HEADER_ID
, PD.PO_RELEASE_ID

Columns