View: POS_HEADERS_V

Product:	POS - iSupplier Portal
Description:	used to select the header info for Advance Shipment Notice created
Implementation/DBA Data:	APPS.POS_HEADERS_V

View Text

SELECT RHI.SHIPMENT_NUM
, RHI.SHIPPED_DATE
, RHI.EXPECTED_RECEIPT_DATE
, FREIGHT_TERMS
, RHI.FREIGHT_CARRIER_CODE
, FREIGHT_BILL_NUMBER
, RHI.NUM_OF_CONTAINERS
, RHI.BILL_OF_LADING
, RHI.WAYBILL_AIRBILL_NUM
, RHI.INVOICE_NUM
, RHI.INVOICE_DATE
, RHI.TOTAL_INVOICE_AMOUNT
, FREIGHT_AMOUNT
, PAYMENT_TERMS_NAME
, RHI.COMMENTS
, RHI.PACKING_SLIP
, CARRIER_EQUIPMENT
, CARRIER_METHOD
, HAZARD_CLASS
, HAZARD_CODE
, HAZARD_DESCRIPTION
, PACKAGING_CODE
, RECEIPT_NUM
, NVL(HR.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) LOCATION_CODE
, SHIP_TO_ORGANIZATION_CODE
, SPECIAL_HANDLING_CODE
, RHI.VENDOR_NAME
, RHI.VENDOR_ID
, RHI.VENDOR_NUM
, RHI.VENDOR_SITE_CODE
, RHI.VENDOR_SITE_ID
, TAR_WEIGHT
, TAR_WEIGHT_UOM_CODE
, NET_WEIGHT
, NET_WEIGHT_UOM_CODE
, GROSS_WEIGHT
, GROSS_WEIGHT_UOM_CODE
, USGGL_TRANSACTION_CODE
, RHI.CURRENCY_CODE
, TO_NUMBER(NULL) CONVERSION_RATE
, CONVERSION_RATE_DATE
, CONVERSION_RATE_TYPE
, INVOICE_STATUS_CODE
, RHI.TAX_NAME
, RHI.TAX_AMOUNT
, SHIP_TO_ORGANIZATION_ID
, RHI.HEADER_INTERFACE_ID HEADER_ID
, RHI.ASN_TYPE
, RHI.REMIT_TO_SITE_ID
FROM RCV_HEADERS_INTERFACE RHI
, RCV_TRANSACTIONS_INTERFACE RTI
, HR_LOCATIONS_ALL_TL HR
, HZ_LOCATIONS HZ
WHERE RTI.HEADER_INTERFACE_ID = RHI.HEADER_INTERFACE_ID
AND HR.LOCATION_ID(+) = RHI.LOCATION_ID
AND HR.LANGUAGE(+) = USERENV('LANG')
AND HZ.LOCATION_ID(+) = RHI.LOCATION_ID UNION SELECT SHIPMENT_NUM
, SHIPPED_DATE
, EXPECTED_RECEIPT_DATE
, FREIGHT_TERMS
, FREIGHT_CARRIER_CODE
, FREIGHT_BILL_NUMBER
, NUM_OF_CONTAINERS
, BILL_OF_LADING
, WAYBILL_AIRBILL_NUM
, INVOICE_NUM
, INVOICE_DATE
, INVOICE_AMOUNT TOTAL_INVOICE_AMOUNT
, FREIGHT_AMOUNT
, AP.NAME PAYMENT_TERMS_NAME
, COMMENTS
, PACKING_SLIP
, CARRIER_EQUIPMENT
, CARRIER_METHOD
, HAZARD_CLASS
, HAZARD_CODE
, HAZARD_DESCRIPTION
, PACKAGING_CODE
, RECEIPT_NUM
, NVL(HR.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) LOCATION_CODE
, '' SHIP_TO_ORGANIZATION_CODE
, SPECIAL_HANDLING_CODE
, POV.VENDOR_NAME VENDOR_NAME
, RSH.VENDOR_ID
, '' VENDOR_NUM
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, RSH.VENDOR_SITE_ID
, TAR_WEIGHT
, TAR_WEIGHT_UOM_CODE
, NET_WEIGHT
, NET_WEIGHT_UOM_CODE
, GROSS_WEIGHT
, GROSS_WEIGHT_UOM_CODE
, USSGL_TRANSACTION_CODE
, CURRENCY_CODE
, TO_NUMBER(NULL) CONVERSION_RATE
, CONVERSION_DATE
, CONVERSION_RATE_TYPE
, INVOICE_STATUS_CODE
, TAX_NAME
, TAX_AMOUNT
, SHIP_TO_ORG_ID
, SHIPMENT_HEADER_ID HEADER_ID
, RSH.ASN_TYPE
, RSH.REMIT_TO_SITE_ID
FROM RCV_SHIPMENT_HEADERS RSH
, HR_LOCATIONS_ALL_TL HR
, HZ_LOCATIONS HZ
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, AP_TERMS_VL AP
WHERE RSH.ASN_TYPE IN ('ASN'
, 'ASBN')
AND (RECEIPT_SOURCE_CODE = 'VENDOR'
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN'))
AND HR.LOCATION_ID(+) = RSH.SHIP_TO_LOCATION_ID
AND HR.LANGUAGE (+) = USERENV('LANG')
AND HZ.LOCATION_ID(+) = RSH.SHIP_TO_LOCATION_ID
AND AP.TERM_ID (+) = RSH.PAYMENT_TERMS_ID
AND POV.VENDOR_ID (+) = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID

Columns