View: OE_DROP_SHIP_LINKS_V

Product:	ONT - Order Management
Description:	This view is used for Dropship Tab in the additional line information of the Sales Orders form.
Implementation/DBA Data:	APPS.OE_DROP_SHIP_LINKS_V

View Text

SELECT OL.LINE_ID LINE_ID
, POH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, 1 TYPE
, OLK.MEANING DOCUMENT_TYPE
, POH.PO_HEADER_ID PO_REQ_HEADER_ID
, POH.SEGMENT1 PO_REQ_NUMBER
, POH.REVISION_NUM REVISION_NUM
, NULL RELEASE_NUM
, POL.LINE_NUM LINE_NUM
, PERP.FULL_NAME BUYER_NAME
, POV.VENDOR_NAME VENDOR_NAME
, NULL STATUS
, POH.CREATION_DATE
, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM
, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, POL.QUANTITY
, POH.CURRENCY_CODE
, POL.UNIT_PRICE
, HRL.LOCATION_CODE
, POLL.NEED_BY_DATE SCHEDULE_DATE
, POL.UNIT_MEAS_LOOKUP_CODE RECEIVED_UOM
, POLL.QUANTITY_RECEIVED
, POLL.SHIPMENT_NUM
, POLL.PROMISED_DATE
, POH.ORG_ID
, POL.SECONDARY_UOM SECONDARY_UNIT_OF_MEASURE
, POL.SECONDARY_QTY SECONDARY_QUANTITY
, POLL.SECONDARY_QUANTITY_RECEIVED SECONDARY_QUANTITY_RECEIVED
, POL.PREFERRED_GRADE PREFERRED_GRADE
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PER_PEOPLE_F PERP
, PO_VENDORS POV
, HR_LOCATIONS HRL
, PO_LINE_LOCATIONS_ALL POLL
, OE_LOOKUPS OLK
, OE_DROP_SHIP_SOURCES OEDSS
, OE_ORDER_LINES_ALL OL
WHERE OL.LINE_ID = OEDSS.LINE_ID
AND POH.PO_HEADER_ID = OEDSS.PO_HEADER_ID
AND POL.PO_LINE_ID = OEDSS.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = OEDSS.LINE_LOCATION_ID
AND HRL.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID
AND POV.VENDOR_ID (+) = POH.VENDOR_ID
AND POH.AGENT_ID = PERP.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PERP.EFFECTIVE_START_DATE
AND PERP.EFFECTIVE_END_DATE /*
AND PERP.EMPLOYEE_NUMBER IS NOT NULL COMMENTED FOR 6923597 */
AND OEDSS.PO_RELEASE_ID IS NULL
AND OLK.LOOKUP_TYPE = 'DOCUMENT TYPE'
AND OLK.LOOKUP_CODE = 'PURCHASE ORDER' UNION SELECT OL.LINE_ID LINE_ID
, PRH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, 2 TYPE
, OLK.MEANING DOCUMENT_TYPE
, PRH.REQUISITION_HEADER_ID PO_REQ_HEADER_ID
, PRH.SEGMENT1 PO_REQ_NUMBER
, NULL REVISION_NUM
, NULL RELEASE_NUM
, PRL.LINE_NUM LINE_NUM
, PERP.FULL_NAME BUYER_NAME
, PRL.SUGGESTED_VENDOR_NAME VENDOR_NAME
, PRH.AUTHORIZATION_STATUS STATUS
, PRH.CREATION_DATE
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE SUPPLIER_ITEM
, PRL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, PRL.QUANTITY
, NVL(PRL.CURRENCY_CODE
, GSB.CURRENCY_CODE)
, PRL.UNIT_PRICE
, NULL
, PRL.NEED_BY_DATE SCHEDULE_DATE
, NULL
, NULL
, NULL
, NULL
, PRH.ORG_ID
, PRL.SECONDARY_UNIT_OF_MEASURE SECONDARY_UNIT_OF_MEASURE
, PRL.SECONDARY_QUANTITY SECONDARY_QUANTITY
, NULL
, OL.PREFERRED_GRADE PREFERRED_GRADE
FROM GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, PER_PEOPLE_F PERP
, OE_LOOKUPS OLK
, OE_DROP_SHIP_SOURCES OEDSS
, OE_ORDER_LINES_ALL OL
WHERE OL.LINE_ID = OEDSS.LINE_ID
AND PRH.REQUISITION_HEADER_ID = OEDSS.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = OEDSS.REQUISITION_LINE_ID
AND PERP.PERSON_ID (+) = PRL.SUGGESTED_BUYER_ID
AND (( /* PERP.EMPLOYEE_NUMBER IS NOT NULL COMMENTED FOR 6923597 */ TRUNC(SYSDATE) BETWEEN PERP.EFFECTIVE_START_DATE
AND PERP.EFFECTIVE_END_DATE) OR PERP.PERSON_ID IS NULL)
AND OEDSS.PO_LINE_ID IS NULL
AND OLK.LOOKUP_TYPE = 'DOCUMENT TYPE'
AND OLK.LOOKUP_CODE = 'PURCHASE REQUISITION'
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND FSP.ORG_ID = PRH.ORG_ID UNION SELECT OL.LINE_ID LINE_ID
, POH.TYPE_LOOKUP_CODE TYPE_LOOKUP_CODE
, 3 TYPE
, OLK.MEANING DOCUMENT_TYPE
, POR.PO_RELEASE_ID PO_REQ_HEADER_ID
, POH.SEGMENT1 PO_REQ_NUMBER
, POH.REVISION_NUM REVISION_NUM
, POR.RELEASE_NUM RELEASE_NUM
, POL.LINE_NUM LINE_NUM
, PERP.FULL_NAME BUYER_NAME
, POV.VENDOR_NAME VENDOR_NAME
, NULL STATUS
, POR.CREATION_DATE CREATION_DATE
, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM
, POL.UNIT_MEAS_LOOKUP_CODE ORDERED_UOM
, POLL.QUANTITY
, POH.CURRENCY_CODE
, POLL.PRICE_OVERRIDE UNIT_PRICE
, HRL.LOCATION_CODE
, POLL.NEED_BY_DATE SCHEDULE_DATE
, POL.UNIT_MEAS_LOOKUP_CODE RECEIVED_UOM
, POLL.QUANTITY_RECEIVED
, POLL.SHIPMENT_NUM
, POLL.PROMISED_DATE
, POR.ORG_ID
, POL.SECONDARY_UNIT_OF_MEASURE SECONDARY_UNIT_OF_MEASURE
, POLL.SECONDARY_QUANTITY SECONDARY_QUANTITY
, POLL.SECONDARY_QUANTITY_RECEIVED SECONDARY_QUANTITY_RECEIVED
, POLL.PREFERRED_GRADE PREFERRED_GRADE
FROM PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, OE_LOOKUPS OLK
, PER_PEOPLE_F PERP
, PO_VENDORS POV
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, HR_LOCATIONS HRL
, OE_DROP_SHIP_SOURCES OEDSS
, OE_ORDER_LINES_ALL OL
WHERE OL.LINE_ID = OEDSS.LINE_ID
AND POH.PO_HEADER_ID = OEDSS.PO_HEADER_ID
AND POL.PO_LINE_ID = OEDSS.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = OEDSS.LINE_LOCATION_ID
AND POR.PO_RELEASE_ID = OEDSS.PO_RELEASE_ID
AND OLK.LOOKUP_TYPE = 'DOCUMENT TYPE'
AND OLK.LOOKUP_CODE = 'BLANKET RELEASE'
AND HRL.LOCATION_ID (+) = POH.SHIP_TO_LOCATION_ID
AND POV.VENDOR_ID (+) = POH.VENDOR_ID
AND POR.AGENT_ID = PERP.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PERP.EFFECTIVE_START_DATE AND PERP.EFFECTIVE_END_DATE /*
AND PERP.EMPLOYEE_NUMBER IS NOT NULL COMMENTED FOR 6923597 */

Columns