VIEW: APPS.POS_SHIPMENTS_V

Source

View Text - Preformatted

SELECT poh.SEGMENT1 PO_NUMBER, prl.RELEASE_NUM, decode(prl.PO_RELEASE_ID, NULL, poh.SEGMENT1, poh.SEGMENT1 || '-' || to_char(prl.RELEASE_NUM)) DISPLAY_PO_NUMBER, poh.PO_HEADER_ID, poh.VENDOR_ID, poh.VENDOR_SITE_ID, poh.VENDOR_CONTACT_ID, pol.LINE_NUM, pov.VENDOR_NAME, pol.ITEM_ID, msi.CONCATENATED_SEGMENTS ITEM_NUM, pol.ITEM_REVISION, pol.ITEM_DESCRIPTION, pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER, pol.UNIT_PRICE, NVL(pll.PROMISED_DATE, pll.NEED_BY_DATE) DUE_DATE, pll.RECEIPT_DAYS_EXCEPTION_CODE, pll.DAYS_EARLY_RECEIPT_ALLOWED, pll.DAYS_LATE_RECEIPT_ALLOWED, pol.UNIT_MEAS_LOOKUP_CODE, mum.uom_class, TO_NUMBER(NULL) SHIPMENT_QUANTITY, TO_NUMBER(NULL) TOLERABLE_QUANTITY, PLL.QUANTITY QUANTITY_ORDERED, PLL.QUANTITY_RECEIVED, nvl(hrl.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,20)) SHIP_TO_LOCATION, pvs.VENDOR_SITE_CODE, pvs.PAY_SITE_FLAG, poh.CURRENCY_CODE, poh.RATE_TYPE, poh.RATE, poh.RATE_DATE, pol.PO_LINE_ID, pll.LINE_LOCATION_ID, pll.SHIP_TO_ORGANIZATION_ID ORG_ID, pll.SHIPMENT_NUM, pll.QTY_RCV_EXCEPTION_CODE, pll.TAXABLE_FLAG, '' TAX_NAME, pll.ESTIMATED_TAX_AMOUNT, poh.REVISION_NUM, NULL ORGANIZATION_CODE, hro2.NAME SHIP_TO_ORG_NAME, hro.NAME ORGANIZATION_NAME, pll.ORG_ID, pll.PO_RELEASE_ID, PLL.MATCH_OPTION, PVS.pay_on_code, pll.CONSIGNED_FLAG, pll.SHIP_TO_LOCATION_ID, pol.SUPPLIER_REF_NUMBER, poh.shipping_control TRANSPORTATION_ARRANGED_BY, plc.displayed_field TAB_Display, pll.promised_date, pll.need_by_date, NVL(MSI.PRIMARY_UNIT_OF_MEASURE,pol.UNIT_MEAS_LOOKUP_CODE) 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_tl hrl, hz_locations hz, po_vendors pov, MTL_SYSTEM_ITEMS_KFV MSI, mtl_units_of_measure mum, HR_ALL_ORGANIZATION_UNITS_TL hro, HR_ALL_ORGANIZATION_UNITS_TL hro2, PO_LOOKUP_CODES PLC 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 hrl.LANGUAGE(+) = USERENV('LANG') and hz.location_id(+) = pll.ship_to_location_id and NVL(pll.approved_flag, 'N') = 'Y' and NVL(pll.CANCEL_FLAG, 'N') = 'N' and NVL(POH.FROZEN_FLAG, 'N') = 'N' and NVL(pll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING', 'CANCELLED') and (pll.QUANTITY*(1+nvl(pll.qty_rcv_tolerance, 0)/100)) > (nvl(pll.QUANTITY_RECEIVED, 0) + nvl(pll.QUANTITY_SHIPPED, 0) + nvl(pll.QUANTITY_CANCELLED, 0)) and pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and pol.item_id = msi.inventory_item_id (+) and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID and hro2.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID and hro2.LANGUAGE (+)= userenv('LANG') and hro.ORGANIZATION_ID (+) = poh.ORG_ID and hro.LANGUAGE (+)= userenv('LANG') and DECODE(PLL.PO_RELEASE_ID, NULL, NVL(POH.CONSIGNED_CONSUMPTION_FLAG, 'N'), NVL(PRL.CONSIGNED_CONSUMPTION_FLAG, 'N')) != 'Y' and NVL(PLL.payment_type, ' ') not in ('ADVANCE', 'DELIVERY') and plc.lookup_type='SHIPPING CONTROL' and nvl(poh.shipping_control,'SUPPLIER') = plc.lookup_code and pll.outsourced_assembly <> 1 and pll.payment_type is null

View Text - HTML Formatted

SELECT POH.SEGMENT1 PO_NUMBER
, PRL.RELEASE_NUM
, DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) DISPLAY_PO_NUMBER
, POH.PO_HEADER_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.VENDOR_CONTACT_ID
, POL.LINE_NUM
, POV.VENDOR_NAME
, POL.ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER
, POL.UNIT_PRICE
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) DUE_DATE
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, POL.UNIT_MEAS_LOOKUP_CODE
, MUM.UOM_CLASS
, TO_NUMBER(NULL) SHIPMENT_QUANTITY
, TO_NUMBER(NULL) TOLERABLE_QUANTITY
, PLL.QUANTITY QUANTITY_ORDERED
, PLL.QUANTITY_RECEIVED
, NVL(HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) SHIP_TO_LOCATION
, PVS.VENDOR_SITE_CODE
, PVS.PAY_SITE_FLAG
, POH.CURRENCY_CODE
, POH.RATE_TYPE
, POH.RATE
, POH.RATE_DATE
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID ORG_ID
, PLL.SHIPMENT_NUM
, PLL.QTY_RCV_EXCEPTION_CODE
, PLL.TAXABLE_FLAG
, '' TAX_NAME
, PLL.ESTIMATED_TAX_AMOUNT
, POH.REVISION_NUM
, NULL ORGANIZATION_CODE
, HRO2.NAME SHIP_TO_ORG_NAME
, HRO.NAME ORGANIZATION_NAME
, PLL.ORG_ID
, PLL.PO_RELEASE_ID
, PLL.MATCH_OPTION
, PVS.PAY_ON_CODE
, PLL.CONSIGNED_FLAG
, PLL.SHIP_TO_LOCATION_ID
, POL.SUPPLIER_REF_NUMBER
, POH.SHIPPING_CONTROL TRANSPORTATION_ARRANGED_BY
, PLC.DISPLAYED_FIELD TAB_DISPLAY
, PLL.PROMISED_DATE
, PLL.NEED_BY_DATE
, NVL(MSI.PRIMARY_UNIT_OF_MEASURE
, POL.UNIT_MEAS_LOOKUP_CODE)
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_TL HRL
, HZ_LOCATIONS HZ
, PO_VENDORS POV
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_UNITS_OF_MEASURE MUM
, HR_ALL_ORGANIZATION_UNITS_TL HRO
, HR_ALL_ORGANIZATION_UNITS_TL HRO2
, PO_LOOKUP_CODES PLC
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 HRL.LANGUAGE(+) = USERENV('LANG')
AND HZ.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POH.FROZEN_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED'
, 'CLOSED'
, 'CLOSED FOR RECEIVING'
, 'CANCELLED')
AND (PLL.QUANTITY*(1+NVL(PLL.QTY_RCV_TOLERANCE
, 0)/100)) > (NVL(PLL.QUANTITY_RECEIVED
, 0) + NVL(PLL.QUANTITY_SHIPPED
, 0) + NVL(PLL.QUANTITY_CANCELLED
, 0))
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND NVL(MSI.ORGANIZATION_ID
, PLL.SHIP_TO_ORGANIZATION_ID) = PLL.SHIP_TO_ORGANIZATION_ID
AND HRO2.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID
AND HRO2.LANGUAGE (+)= USERENV('LANG')
AND HRO.ORGANIZATION_ID (+) = POH.ORG_ID
AND HRO.LANGUAGE (+)= USERENV('LANG')
AND DECODE(PLL.PO_RELEASE_ID
, NULL
, NVL(POH.CONSIGNED_CONSUMPTION_FLAG
, 'N')
, NVL(PRL.CONSIGNED_CONSUMPTION_FLAG
, 'N')) != 'Y'
AND NVL(PLL.PAYMENT_TYPE
, ' ') NOT IN ('ADVANCE'
, 'DELIVERY')
AND PLC.LOOKUP_TYPE='SHIPPING CONTROL'
AND NVL(POH.SHIPPING_CONTROL
, 'SUPPLIER') = PLC.LOOKUP_CODE
AND PLL.OUTSOURCED_ASSEMBLY <> 1
AND PLL.PAYMENT_TYPE IS NULL