View: SO_DROP_SHIP_LINKS_V

Product:	OE - Order Entry
Description:
Implementation/DBA Data:	APPS.SO_DROP_SHIP_LINKS_V

View Text

SELECT SOL.LINE_ID
, POH.TYPE_LOOKUP_CODE
, 1 TYPE
, SL.MEANING DOCUMENT_TYPE
, POH.PO_HEADER_ID PO_REQ_HEADER_ID
, POH.SEGMENT1 PO_REQ_NUMBER
, POH.REVISION_NUM
, TO_NUMBER(NULL)
, POL.LINE_NUM
, PERP.FULL_NAME BUYER_NAME
, POV.VENDOR_NAME VENDOR_NAME
, OE_DROP_SHIP_PVT.GET_PO_STATUS(POH.PO_HEADER_ID) 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
, POL.UNIT_MEAS_LOOKUP_CODE RECEIVED_UOM
, POLL.QUANTITY_RECEIVED
FROM PO_HEADERS POH
, PO_LINES POL
, PER_PEOPLE_F PERP
, PO_VENDORS POV
, HR_LOCATIONS HRL
, PO_LINE_LOCATIONS POLL
, SO_LOOKUPS SL
, SO_DROP_SHIP_SOURCES SODSS
, SO_LINES SOL
WHERE SOL.LINE_ID = SODSS.LINE_ID
AND POH.PO_HEADER_ID = SODSS.PO_HEADER_ID
AND POL.PO_LINE_ID = SODSS.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = SODSS.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 PERP.BUSINESS_GROUP_ID + 0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID)
, 0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PERP.EFFECTIVE_START_DATE
AND PERP.EFFECTIVE_END_DATE
AND PERP.EMPLOYEE_NUMBER IS NOT NULL
AND SODSS.PO_RELEASE_ID IS NULL
AND SL.LOOKUP_TYPE = 'DOCUMENT TYPE'
AND SL.LOOKUP_CODE = 'PURCHASE ORDER' UNION SELECT SOL.LINE_ID
, PRH.TYPE_LOOKUP_CODE
, 2 TYPE
, SL.MEANING DOCUMENT_TYPE
, PRH.REQUISITION_HEADER_ID PO_REQ_HEADER_ID
, PRH.SEGMENT1 PO_REQ_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PRL.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
, TO_CHAR(NULL)
, PRL.NEED_BY_DATE
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
FROM GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PER_PEOPLE_F PERP
, SO_LOOKUPS SL
, SO_DROP_SHIP_SOURCES SODSS
, SO_LINES SOL
WHERE SOL.LINE_ID = SODSS.LINE_ID
AND PRH.REQUISITION_HEADER_ID = SODSS.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = SODSS.REQUISITION_LINE_ID
AND PERP.PERSON_ID (+) = PRL.SUGGESTED_BUYER_ID
AND ((PERP.EMPLOYEE_NUMBER IS NOT NULL
AND PERP.BUSINESS_GROUP_ID + 0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID)
, 0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PERP.EFFECTIVE_START_DATE
AND PERP.EFFECTIVE_END_DATE) OR PERP.PERSON_ID IS NULL)
AND SODSS.PO_LINE_ID IS NULL
AND SL.LOOKUP_TYPE = 'DOCUMENT TYPE'
AND SL.LOOKUP_CODE = 'PURCHASE REQUISITION'
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID UNION SELECT SOL.LINE_ID
, POH.TYPE_LOOKUP_CODE
, 3 TYPE
, SL.MEANING DOCUMENT_TYPE
, POR.PO_RELEASE_ID PO_REQ_HEADER_ID
, POH.SEGMENT1 PO_REQ_NUMBER
, POH.REVISION_NUM
, POR.RELEASE_NUM RELEASE_NUM
, POL.LINE_NUM
, PERP.FULL_NAME BUYER_NAME
, POV.VENDOR_NAME VENDOR_NAME
, OE_DROP_SHIP_PVT.GET_RELEASE_STATUS(POR.PO_RELEASE_ID) 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
, POL.UNIT_MEAS_LOOKUP_CODE RECEIVED_UOM
, POLL.QUANTITY_RECEIVED
FROM PO_HEADERS POH
, PO_RELEASES POR
, SO_LOOKUPS SL
, PER_PEOPLE_F PERP
, PO_VENDORS POV
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, HR_LOCATIONS HRL
, SO_DROP_SHIP_SOURCES SODSS
, SO_LINES SOL
WHERE SOL.LINE_ID = SODSS.LINE_ID
AND POH.PO_HEADER_ID = SODSS.PO_HEADER_ID
AND POL.PO_LINE_ID = SODSS.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = SODSS.LINE_LOCATION_ID
AND POR.PO_RELEASE_ID = SODSS.PO_RELEASE_ID
AND SL.LOOKUP_TYPE = 'DOCUMENT TYPE'
AND SL.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 PERP.BUSINESS_GROUP_ID + 0 = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID)
, 0)
FROM FINANCIALS_SYSTEM_PARAMETERS FSP)
AND TRUNC(SYSDATE) BETWEEN PERP.EFFECTIVE_START_DATE
AND PERP.EFFECTIVE_END_DATE
AND PERP.EMPLOYEE_NUMBER IS NOT NULL

Columns