eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.POS_SHIPMENTS_V

Source

View Text - Preformatted


SELECT poh.SEGMENT1 PO_NUMBER, prl.RELEASE_NUM, decode(prl.PO_RELEASE_ID, NULL, poh.CLM_DOCUMENT_NUMBER, poh.CLM_DOCUMENT_NUMBER || '-' || 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, CASE WHEN ( pol.item_id IS NOT NULL ) THEN (SELECT msi1.concatenated_segments FROM mtl_system_items_kfv msi1 WHERE msi1.inventory_item_id = pol.item_id AND msi1.organization_id = pll.ship_to_organization_id) END 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, CASE WHEN ( pol.item_id IS NOT NULL ) THEN Nvl((SELECT msi2.primary_unit_of_measure FROM mtl_system_items_kfv msi2 WHERE msi2.inventory_item_id = pol.item_id AND msi2.organization_id = pll.ship_to_organization_id), pol.unit_meas_lookup_code) ELSE pol.unit_meas_lookup_code END primary_unit_of_measure, Nvl(pol.LINE_NUM_DISPLAY,pol.LINE_NUM) LINE_NUMBER, NVL(PDSH.CLM_FLAG,'N') CLM_FLAG , To_number(NULL) QUANTITY_SHIPPED, Decode(pol.clm_undef_flag,'Y','Yes','No') undef_sts, pol.order_type_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_units_of_measure mum, HR_ALL_ORGANIZATION_UNITS_TL hro, HR_ALL_ORGANIZATION_UNITS_TL hro2, PO_LOOKUP_CODES PLC, PO_DOC_STYLE_HEADERS PDSH WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID 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)+ Nvl(pos_asn_create_pvt.Get_po_pending_asn_quantity(line_location_id), 0)) and pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED') 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 and NVL(POH.STYLE_ID, 1) = PDSH.STYLE_ID (+) and PDSH.STATUS (+) = 'ACTIVE' and Decode(NVL(POL.CLM_OPTION_INDICATOR,'Y'),'Y','Y', 'B','Y', 'O', Decode(NVL(POL.CLM_EXERCISED_FLAG,'N'),'Y','Y', 'N'))='Y' and NVL(POL.CLM_INFO_FLAG, 'N' ) = 'N'

View Text - HTML Formatted

SELECT POH.SEGMENT1 PO_NUMBER
, PRL.RELEASE_NUM
, DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.CLM_DOCUMENT_NUMBER
, POH.CLM_DOCUMENT_NUMBER || '-' || 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
, CASE WHEN ( POL.ITEM_ID IS NOT NULL ) THEN (SELECT MSI1.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSI1
WHERE MSI1.INVENTORY_ITEM_ID = POL.ITEM_ID
AND MSI1.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID) END 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
, CASE WHEN ( POL.ITEM_ID IS NOT NULL ) THEN NVL((SELECT MSI2.PRIMARY_UNIT_OF_MEASURE
FROM MTL_SYSTEM_ITEMS_KFV MSI2
WHERE MSI2.INVENTORY_ITEM_ID = POL.ITEM_ID
AND MSI2.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID)
, POL.UNIT_MEAS_LOOKUP_CODE) ELSE POL.UNIT_MEAS_LOOKUP_CODE END PRIMARY_UNIT_OF_MEASURE
, NVL(POL.LINE_NUM_DISPLAY
, POL.LINE_NUM) LINE_NUMBER
, NVL(PDSH.CLM_FLAG
, 'N') CLM_FLAG
, TO_NUMBER(NULL) QUANTITY_SHIPPED
, DECODE(POL.CLM_UNDEF_FLAG
, 'Y'
, 'YES'
, 'NO') UNDEF_STS
, POL.ORDER_TYPE_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_UNITS_OF_MEASURE MUM
, HR_ALL_ORGANIZATION_UNITS_TL HRO
, HR_ALL_ORGANIZATION_UNITS_TL HRO2
, PO_LOOKUP_CODES PLC
, PO_DOC_STYLE_HEADERS PDSH
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
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)+ NVL(POS_ASN_CREATE_PVT.GET_PO_PENDING_ASN_QUANTITY(LINE_LOCATION_ID)
, 0))
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
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
AND NVL(POH.STYLE_ID
, 1) = PDSH.STYLE_ID (+)
AND PDSH.STATUS (+) = 'ACTIVE'
AND DECODE(NVL(POL.CLM_OPTION_INDICATOR
, 'Y')
, 'Y'
, 'Y'
, 'B'
, 'Y'
, 'O'
, DECODE(NVL(POL.CLM_EXERCISED_FLAG
, 'N')
, 'Y'
, 'Y'
, 'N'))='Y'
AND NVL(POL.CLM_INFO_FLAG
, 'N' ) = 'N'