VIEW: APPS.JAI_PO_SHIPMENT_DTLS_V

Source

View Text - Preformatted

SELECT DISTINCT to_char(sh.shipped_date, 'DD-MON-RRRR') shipped_date, sh.shipment_header_id, sh.shipment_num , to_char(sh.expected_receipt_date, 'DD-MON-RRRR') expected_receipt_date, sl.from_organization_id, ood.organization_name, sl.to_organization_id, 'Organization' Type FROM rcv_shipment_headers sh, rcv_shipment_lines sl, org_organization_Definitions ood, mtl_supply ms WHERE sh.shipment_num is not null AND sh.receipt_source_code in ('INVENTORY','INTERNAL ORDER') AND sh.shipment_header_id + 0 = sl.shipment_header_id AND sl.from_organization_id = ood.organization_id(+) AND ms.to_organization_id = sl.to_organization_id AND ms.shipment_header_id = sh.shipment_header_id AND ms.supply_type_code = 'SHIPMENT' AND sh.shipment_num is not null AND sh.shipped_date is not null UNION ALL SELECT DISTINCT to_char(sh.shipped_date, 'DD-MON-YYYY') shipped_date, sh.shipment_header_id, sh.shipment_num, to_char(sh.expected_receipt_date, 'DD-MON-RRRR') expected_receipt_date, sh.vendor_id from_organization_id, pov.vendor_name organization_name, to_number(NULL), 'Vendor' Type FROM rcv_shipment_headers sh, rcv_shipment_lines sl, po_vendors pov WHERE sh.shipment_num is not null AND sh.receipt_source_code = 'VENDOR' AND sl.shipment_line_status_code <> 'CANCELLED' AND sh.shipment_header_id = sl.shipment_header_id AND sh.vendor_id = pov.vendor_id (+) AND sh.shipped_date is not null

View Text - HTML Formatted

SELECT DISTINCT TO_CHAR(SH.SHIPPED_DATE
, 'DD-MON-RRRR') SHIPPED_DATE
, SH.SHIPMENT_HEADER_ID
, SH.SHIPMENT_NUM
, TO_CHAR(SH.EXPECTED_RECEIPT_DATE
, 'DD-MON-RRRR') EXPECTED_RECEIPT_DATE
, SL.FROM_ORGANIZATION_ID
, OOD.ORGANIZATION_NAME
, SL.TO_ORGANIZATION_ID
, 'ORGANIZATION' TYPE
FROM RCV_SHIPMENT_HEADERS SH
, RCV_SHIPMENT_LINES SL
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_SUPPLY MS
WHERE SH.SHIPMENT_NUM IS NOT NULL
AND SH.RECEIPT_SOURCE_CODE IN ('INVENTORY'
, 'INTERNAL ORDER')
AND SH.SHIPMENT_HEADER_ID + 0 = SL.SHIPMENT_HEADER_ID
AND SL.FROM_ORGANIZATION_ID = OOD.ORGANIZATION_ID(+)
AND MS.TO_ORGANIZATION_ID = SL.TO_ORGANIZATION_ID
AND MS.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID
AND MS.SUPPLY_TYPE_CODE = 'SHIPMENT'
AND SH.SHIPMENT_NUM IS NOT NULL
AND SH.SHIPPED_DATE IS NOT NULL UNION ALL SELECT DISTINCT TO_CHAR(SH.SHIPPED_DATE
, 'DD-MON-YYYY') SHIPPED_DATE
, SH.SHIPMENT_HEADER_ID
, SH.SHIPMENT_NUM
, TO_CHAR(SH.EXPECTED_RECEIPT_DATE
, 'DD-MON-RRRR') EXPECTED_RECEIPT_DATE
, SH.VENDOR_ID FROM_ORGANIZATION_ID
, POV.VENDOR_NAME ORGANIZATION_NAME
, TO_NUMBER(NULL)
, 'VENDOR' TYPE
FROM RCV_SHIPMENT_HEADERS SH
, RCV_SHIPMENT_LINES SL
, PO_VENDORS POV
WHERE SH.SHIPMENT_NUM IS NOT NULL
AND SH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND SL.SHIPMENT_LINE_STATUS_CODE <> 'CANCELLED'
AND SH.SHIPMENT_HEADER_ID = SL.SHIPMENT_HEADER_ID
AND SH.VENDOR_ID = POV.VENDOR_ID (+)
AND SH.SHIPPED_DATE IS NOT NULL