View: SO_PO_REPORT_V

View Text

SELECT SOH.ORDER_NUMBER
, SOH.HEADER_ID
, SOH.DATE_ORDERED
, SOT.NAME
, RAC.CUSTOMER_NAME
, SOH.CUSTOMER_ID
, RAC.CUSTOMER_NUMBER
, OH.ORDER_HOLD_ID
, OE_DROP_SHIP_PVT.GET_HOLD_NAME(SHS.HOLD_ID)
, SOH.CURRENCY_CODE
, SOL.LINE_NUMBER
, SOL.LINE_ID
, SOL.INVENTORY_ITEM_ID
, (NVL(SOL.ORDERED_QUANTITY
, 0) - NVL(SOL.CANCELLED_QUANTITY
, 0))
, MSI.SEGMENT1
, SOL.SCHEDULE_DATE
, SOL.UNIT_CODE
, SOL.SELLING_PRICE
, SOL.SHIP_TO_SITE_USE_ID
, RASU.LOCATION
, RASU1.LOCATION
, POH.PO_HEADER_ID
, POH.SEGMENT1
, POH.REVISION_NUM
, POH.CURRENCY_CODE
, POL.LINE_NUM
, (NVL(POLL.QUANTITY
, 0) - NVL(POLL.QUANTITY_CANCELLED
, 0))
, POL.UNIT_PRICE
, POLL.UNIT_MEAS_LOOKUP_CODE
, POLL.NEED_BY_DATE
, PERP.FULL_NAME
, POV.VENDOR_NAME
, POLL.SHIPMENT_NUM
, MSI1.SEGMENT1
, POH.USER_HOLD_FLAG
FROM SO_HEADERS SOH
, SO_LINES SOL
, RA_CUSTOMERS RAC
, SO_ORDER_HOLDS OH
, MTL_SYSTEM_ITEMS MSI
, RA_SITE_USES RASU
, RA_SITE_USES RASU1
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PER_PEOPLE_F PERP
, PO_VENDORS POV
, SO_DROP_SHIP_SOURCES SODSS
, SO_HOLD_SOURCES SHS
, PO_LOCATION_ASSOCIATIONS POLA
, SO_ORDER_TYPES SOT
, MTL_SYSTEM_ITEMS MSI1
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 POH.SHIP_TO_LOCATION_ID = POLL.SHIP_TO_LOCATION_ID
AND POH.SHIP_TO_LOCATION_ID = POLA.LOCATION_ID
AND POLA.SITE_USE_ID = RASU1.SITE_USE_ID
AND RASU1.SITE_USE_CODE = 'SHIP_TO'
AND SOL.SHIP_TO_SITE_USE_ID = RASU.SITE_USE_ID
AND RASU.SITE_USE_CODE = 'SHIP_TO'
AND SOH.CUSTOMER_ID = RAC.CUSTOMER_ID
AND MSI.INVENTORY_ITEM_ID = SOL.INVENTORY_ITEM_ID
AND SOT.ORDER_TYPE_ID = SOH.ORDER_TYPE_ID
AND POL.ITEM_ID = MSI1.INVENTORY_ITEM_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 (OE_DROP_SHIP_PVT.COMPARE_PO_SO(RASU.SITE_USE_ID
, RASU1.SITE_USE_ID
, SOL.UNIT_CODE
, POL.UNIT_MEAS_LOOKUP_CODE
, SOL.SCHEDULE_DATE
, POLL.NEED_BY_DATE
, SOL.ORDERED_QUANTITY
, SOL.CANCELLED_QUANTITY
, SOL.SHIPPED_QUANTITY
, 'PO_LINE_LOCATIONS'
, SOH.HEADER_ID
, SOL.LINE_ID) = 'Y')
AND OH.HEADER_ID(+) = SOH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL
AND SOL.HEADER_ID = SOH.HEADER_ID
AND OH.HOLD_SOURCE_ID = SHS.HOLD_SOURCE_ID(+)
AND SOH.OPEN_FLAG|| '' = 'Y' /* CLOSED HEADER */
AND SOL.OPEN_FLAG|| '' = 'Y' /* CLOSED LINE */ UNION SELECT SOH.ORDER_NUMBER
, SOH.HEADER_ID
, SOH.DATE_ORDERED
, SOT.NAME
, RAC.CUSTOMER_NAME
, SOH.CUSTOMER_ID
, RAC.CUSTOMER_NUMBER
, OH.ORDER_HOLD_ID
, OE_DROP_SHIP_PVT.GET_HOLD_NAME(SHS.HOLD_ID)
, SOH.CURRENCY_CODE
, SOL.LINE_NUMBER
, SOL.LINE_ID
, SOL.INVENTORY_ITEM_ID
, (NVL(SOL.ORDERED_QUANTITY
, 0) - NVL(SOL.CANCELLED_QUANTITY
, 0))
, MSI.SEGMENT1
, SOL.SCHEDULE_DATE
, SOL.UNIT_CODE
, SOL.SELLING_PRICE
, SOL.SHIP_TO_SITE_USE_ID
, RASU.LOCATION
, RASU1.LOCATION
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, TO_NUMBER(NULL)
, PRL.CURRENCY_CODE
, PRL.LINE_NUM
, (NVL(PRL.QUANTITY
, 0) - NVL(PRL.QUANTITY_CANCELLED
, 0))
, PRL.UNIT_PRICE
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.NEED_BY_DATE
, PERP.FULL_NAME
, PRL.SUGGESTED_VENDOR_NAME
, TO_NUMBER(NULL)
, MSI1.SEGMENT1
, TO_CHAR(NULL)
FROM SO_HEADERS SOH
, SO_LINES SOL
, RA_CUSTOMERS RAC
, SO_ORDER_HOLDS OH
, MTL_SYSTEM_ITEMS MSI
, RA_SITE_USES RASU
, RA_SITE_USES RASU1
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PER_PEOPLE_F PERP
, SO_DROP_SHIP_SOURCES SODSS
, SO_HOLD_SOURCES SHS
, PO_LOCATION_ASSOCIATIONS POLA
, SO_ORDER_TYPES SOT
, MTL_SYSTEM_ITEMS MSI1
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 PRL.DELIVER_TO_LOCATION_ID = POLA.LOCATION_ID
AND POLA.SITE_USE_ID = RASU1.SITE_USE_ID
AND RASU1.SITE_USE_CODE = 'SHIP_TO'
AND SOL.SHIP_TO_SITE_USE_ID = RASU.SITE_USE_ID
AND RASU.SITE_USE_CODE = 'SHIP_TO'
AND SOH.CUSTOMER_ID = RAC.CUSTOMER_ID
AND SOT.ORDER_TYPE_ID = SOH.ORDER_TYPE_ID
AND MSI.INVENTORY_ITEM_ID = SOL.INVENTORY_ITEM_ID
AND MSI1.INVENTORY_ITEM_ID = PRL.ITEM_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 (OE_DROP_SHIP_PVT.COMPARE_PO_SO(RASU.SITE_USE_ID
, RASU1.SITE_USE_ID
, SOL.UNIT_CODE
, PRL.UNIT_MEAS_LOOKUP_CODE
, SOL.SCHEDULE_DATE
, PRL.NEED_BY_DATE
, SOL.ORDERED_QUANTITY
, SOL.CANCELLED_QUANTITY
, SOL.SHIPPED_QUANTITY
, 'PO_REQUISITION_LINES'
, SOH.HEADER_ID
, SOL.LINE_ID) = 'Y')
AND OH.HEADER_ID(+) = SOH.HEADER_ID
AND OH.HOLD_RELEASE_ID IS NULL
AND SOL.HEADER_ID = SOH.HEADER_ID
AND OH.HOLD_SOURCE_ID = SHS.HOLD_SOURCE_ID(+)
AND SOH.OPEN_FLAG|| '' = 'Y' /* CLOSED HEADER */
AND SOL.OPEN_FLAG|| '' = 'Y' /* CLOSED LINE */

Columns