VIEW: APPS.SO_DROP_SHIP_LINKS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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