VIEW: APPS.AP_PO_SHIPMENTS_V

Source

View Text - Preformatted

SELECT decode(prl.PO_RELEASE_ID, NULL, poh.SEGMENT1 || '-' || to_char(pol.LINE_NUM) || '-' || to_char(pll.SHIPMENT_NUM), poh.SEGMENT1 || '-' || to_char(prl.RELEASE_NUM)) PO_NUMBER_DISPLAY, poh.SEGMENT1 PO_NUMBER, poh.PO_HEADER_ID, poh.AGENT_ID, poh.CREATION_DATE, poh.VENDOR_ID, poh.VENDOR_SITE_ID, poh.VENDOR_CONTACT_ID, pol.LINE_NUM, pov.VENDOR_NAME, pov.SEGMENT1, pol.ITEM_ID, pol.ITEM_DESCRIPTION, pol.UNIT_PRICE, NVL(pll.PROMISED_DATE, pll.NEED_BY_DATE) DUE_DATE, pol.UNIT_MEAS_LOOKUP_CODE, mum.UOM_CLASS, PLL.QUANTITY QUANTITY_ORDERED, PLL.QUANTITY_RECEIVED, PLL.QUANTITY_BILLED, hrl.LOCATION_CODE SHIP_TO_LOCATION, pvs.VENDOR_SITE_CODE, pvs.PAY_SITE_FLAG, poh.CURRENCY_CODE, pol.PO_LINE_ID, pll.LINE_LOCATION_ID, pll.SHIP_TO_ORGANIZATION_ID SHIPTO_ORG_ID, pll.SHIPMENT_NUM, pll.TAXABLE_FLAG, pll.TAX_NAME, pll.TAX_CODE_ID, hro.NAME ORGANIZATION_NAME, pll.ORG_ID, pll.PO_RELEASE_ID, msi.CONCATENATED_SEGMENTS Item_Number, pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER FROM PO_HEADERS_ALL poh, PO_LINES_ALL pol, PO_LINE_LOCATIONS_ALL pll, PO_RELEASES_ALL prl, PO_VENDOR_SITES_ALL pvs, HR_LOCATIONS_ALL hrl, PO_VENDORS pov, MTL_UNITS_OF_MEASURE mum, MTL_SYSTEM_ITEMS_KFV MSI, HR_ALL_ORGANIZATION_UNITS_TL hro WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID and POH.AUTHORIZATION_STATUS = 'APPROVED' and POL.PO_LINE_ID = PLL.PO_LINE_ID and pol.unit_meas_lookup_code = mum.unit_of_measure (+) and PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID(+) and pov.vendor_id = poh.vendor_id and pvs.vendor_site_id = poh.vendor_site_id and hrl.location_id = pll.ship_to_location_id and NVL(pll.approved_flag, 'N') = 'Y' and NVL(pll.CANCEL_FLAG, 'N') = 'N' and POH.pcard_id is null and NVL(pll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CANCELLED') and pll.shipment_type in ('STANDARD','BLANKET') and NVL(pll.match_option, 'P') = 'P' and hro.ORGANIZATION_ID = poh.ORG_ID and hro.LANGUAGE = userenv('LANG') and pol.item_id = MSI.inventory_item_id (+) and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID /* Bug fix:2662505 Consigned Inventory Project Impact */ and nvl(pll.consigned_flag,'N') <> 'Y'

View Text - HTML Formatted

SELECT DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1 || '-' || TO_CHAR(POL.LINE_NUM) || '-' || TO_CHAR(PLL.SHIPMENT_NUM)
, POH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUMBER_DISPLAY
, POH.SEGMENT1 PO_NUMBER
, POH.PO_HEADER_ID
, POH.AGENT_ID
, POH.CREATION_DATE
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.VENDOR_CONTACT_ID
, POL.LINE_NUM
, POV.VENDOR_NAME
, POV.SEGMENT1
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_PRICE
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) DUE_DATE
, POL.UNIT_MEAS_LOOKUP_CODE
, MUM.UOM_CLASS
, PLL.QUANTITY QUANTITY_ORDERED
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_BILLED
, HRL.LOCATION_CODE SHIP_TO_LOCATION
, PVS.VENDOR_SITE_CODE
, PVS.PAY_SITE_FLAG
, POH.CURRENCY_CODE
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID SHIPTO_ORG_ID
, PLL.SHIPMENT_NUM
, PLL.TAXABLE_FLAG
, PLL.TAX_NAME
, PLL.TAX_CODE_ID
, HRO.NAME ORGANIZATION_NAME
, PLL.ORG_ID
, PLL.PO_RELEASE_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PRL
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL
, PO_VENDORS POV
, MTL_UNITS_OF_MEASURE MUM
, MTL_SYSTEM_ITEMS_KFV MSI
, HR_ALL_ORGANIZATION_UNITS_TL HRO
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.AUTHORIZATION_STATUS = 'APPROVED'
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE (+)
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID(+)
AND POV.VENDOR_ID = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND HRL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
AND NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND POH.PCARD_ID IS NULL
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CANCELLED')
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET')
AND NVL(PLL.MATCH_OPTION
, 'P') = 'P'
AND HRO.ORGANIZATION_ID = POH.ORG_ID
AND HRO.LANGUAGE = USERENV('LANG')
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND NVL(MSI.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID /* BUG FIX:2662505 CONSIGNED INVENTORY PROJECT IMPACT */
AND NVL(PLL.CONSIGNED_FLAG
, 'N') <> 'Y'