VIEW: APPS.RCV_VRC_HDS_V

Source

View Text - Preformatted

SELECT RSH.ROWID, RSH.CREATION_DATE, RSH.CREATED_BY, RSH.LAST_UPDATE_LOGIN, RSH.LAST_UPDATE_DATE, RSH.LAST_UPDATED_BY, RSH.RECEIPT_NUM, RSH.CREATION_DATE RECEIPT_DATE, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE, /* SOURCE CODE: INTERNAL ORDER, INVENTORY, VENDOR */ DECODE(RSH.RECEIPT_SOURCE_CODE, 'VENDOR', RSH.VENDOR_ID, 'CUSTOMER', NULL, RSH.ORGANIZATION_ID) SUPPLIER_ID, DECODE(RSH.RECEIPT_SOURCE_CODE, 'VENDOR', POV.VENDOR_NAME, 'CUSTOMER', NULL, ORG.NAME) SUPPLIER, RSH.VENDOR_ID, DECODE(RSH.RECEIPT_SOURCE_CODE, 'CUSTOMER', NULL, POV.VENDOR_NAME) VENDOR, RSH.VENDOR_SITE_ID SUPPLIER_SITE_ID, DECODE(RSH.RECEIPT_SOURCE_CODE, 'CUSTOMER', NULL, POVS.VENDOR_SITE_CODE) SUPPLIER_SITE, RSH.ORGANIZATION_ID, /* SOURCE ORG */ ORG.NAME SOURCE_ORG, RSH.SHIPMENT_NUM, RSH.SHIP_TO_LOCATION_ID, HRL.LOCATION_CODE SHIP_TO_LOCATION, RSH.BILL_OF_LADING, RSH.PACKING_SLIP, RSH.SHIPPED_DATE, RSH.FREIGHT_CARRIER_CODE, RSH.EXPECTED_RECEIPT_DATE, RSH.EMPLOYEE_ID, PO_INQ_SV.GET_PERSON_NAME(RSH.EMPLOYEE_ID) RECEIVER, RSH.NUM_OF_CONTAINERS, RSH.WAYBILL_AIRBILL_NUM, RSH.COMMENTS, RSH.USSGL_TRANSACTION_CODE, RSH.GOVERNMENT_CONTEXT, RSH.SHIPMENT_HEADER_ID, PLC.DISPLAYED_FIELD, RSH.ATTRIBUTE_CATEGORY, RSH.ATTRIBUTE1, RSH.ATTRIBUTE2, RSH.ATTRIBUTE3, RSH.ATTRIBUTE4, RSH.ATTRIBUTE5, RSH.ATTRIBUTE6, RSH.ATTRIBUTE7, RSH.ATTRIBUTE8, RSH.ATTRIBUTE9, RSH.ATTRIBUTE10, RSH.ATTRIBUTE11, RSH.ATTRIBUTE12, RSH.ATTRIBUTE13, RSH.ATTRIBUTE14, RSH.ATTRIBUTE15, RSH.CUSTOMER_ID, RSH.CUSTOMER_SITE_ID, SUBSTRB(HZP.PARTY_NAME,1,240) FROM RCV_SHIPMENT_HEADERS RSH, HZ_CUST_ACCOUNTS HZCA, HZ_PARTIES HZP, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, HR_ALL_ORGANIZATION_UNITS_TL ORG, HR_LOCATIONS_ALL_TL HRL, PO_LOOKUP_CODES PLC WHERE RSH.RECEIPT_NUM IS NOT NULL AND POV.VENDOR_ID (+) = RSH.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID AND USERENV('LANG') = ORG.LANGUAGE(+) AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID AND HZCA.PARTY_ID = HZP.PARTY_ID(+) AND HRL.LOCATION_ID (+) = RSH.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV('LANG') AND ORG.ORGANIZATION_ID (+) = RSH.ORGANIZATION_ID AND RSH.RECEIPT_SOURCE_CODE = PLC.LOOKUP_CODE AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND NOT EXISTS(SELECT 1 FROM rcv_shipment_lines rsl, po_lines_all pol WHERE rsl.shipment_header_id = rsh.shipment_header_id AND rsl.po_line_id = pol.po_line_id AND pol.order_type_lookup_code = 'RATE' AND pol.purchase_basis = 'TEMP LABOR')

View Text - HTML Formatted

SELECT RSH.ROWID
, RSH.CREATION_DATE
, RSH.CREATED_BY
, RSH.LAST_UPDATE_LOGIN
, RSH.LAST_UPDATE_DATE
, RSH.LAST_UPDATED_BY
, RSH.RECEIPT_NUM
, RSH.CREATION_DATE RECEIPT_DATE
, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE
, /* SOURCE CODE: INTERNAL ORDER
, INVENTORY
, VENDOR */ DECODE(RSH.RECEIPT_SOURCE_CODE
, 'VENDOR'
, RSH.VENDOR_ID
, 'CUSTOMER'
, NULL
, RSH.ORGANIZATION_ID) SUPPLIER_ID
, DECODE(RSH.RECEIPT_SOURCE_CODE
, 'VENDOR'
, POV.VENDOR_NAME
, 'CUSTOMER'
, NULL
, ORG.NAME) SUPPLIER
, RSH.VENDOR_ID
, DECODE(RSH.RECEIPT_SOURCE_CODE
, 'CUSTOMER'
, NULL
, POV.VENDOR_NAME) VENDOR
, RSH.VENDOR_SITE_ID SUPPLIER_SITE_ID
, DECODE(RSH.RECEIPT_SOURCE_CODE
, 'CUSTOMER'
, NULL
, POVS.VENDOR_SITE_CODE) SUPPLIER_SITE
, RSH.ORGANIZATION_ID
, /* SOURCE ORG */ ORG.NAME SOURCE_ORG
, RSH.SHIPMENT_NUM
, RSH.SHIP_TO_LOCATION_ID
, HRL.LOCATION_CODE SHIP_TO_LOCATION
, RSH.BILL_OF_LADING
, RSH.PACKING_SLIP
, RSH.SHIPPED_DATE
, RSH.FREIGHT_CARRIER_CODE
, RSH.EXPECTED_RECEIPT_DATE
, RSH.EMPLOYEE_ID
, PO_INQ_SV.GET_PERSON_NAME(RSH.EMPLOYEE_ID) RECEIVER
, RSH.NUM_OF_CONTAINERS
, RSH.WAYBILL_AIRBILL_NUM
, RSH.COMMENTS
, RSH.USSGL_TRANSACTION_CODE
, RSH.GOVERNMENT_CONTEXT
, RSH.SHIPMENT_HEADER_ID
, PLC.DISPLAYED_FIELD
, RSH.ATTRIBUTE_CATEGORY
, RSH.ATTRIBUTE1
, RSH.ATTRIBUTE2
, RSH.ATTRIBUTE3
, RSH.ATTRIBUTE4
, RSH.ATTRIBUTE5
, RSH.ATTRIBUTE6
, RSH.ATTRIBUTE7
, RSH.ATTRIBUTE8
, RSH.ATTRIBUTE9
, RSH.ATTRIBUTE10
, RSH.ATTRIBUTE11
, RSH.ATTRIBUTE12
, RSH.ATTRIBUTE13
, RSH.ATTRIBUTE14
, RSH.ATTRIBUTE15
, RSH.CUSTOMER_ID
, RSH.CUSTOMER_SITE_ID
, SUBSTRB(HZP.PARTY_NAME
, 1
, 240)
FROM RCV_SHIPMENT_HEADERS RSH
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, HR_LOCATIONS_ALL_TL HRL
, PO_LOOKUP_CODES PLC
WHERE RSH.RECEIPT_NUM IS NOT NULL
AND POV.VENDOR_ID (+) = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID
AND USERENV('LANG') = ORG.LANGUAGE(+)
AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND HRL.LOCATION_ID (+) = RSH.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND ORG.ORGANIZATION_ID (+) = RSH.ORGANIZATION_ID
AND RSH.RECEIPT_SOURCE_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND NOT EXISTS(SELECT 1
FROM RCV_SHIPMENT_LINES RSL
, PO_LINES_ALL POL
WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.ORDER_TYPE_LOOKUP_CODE = 'RATE'
AND POL.PURCHASE_BASIS = 'TEMP LABOR')