View: POS_LINES_V

Product:	POS - iSupplier Portal
Description:	used to select the line-level info for the Advance Shipment Notice
Implementation/DBA Data:	APPS.POS_LINES_V

View Text

SELECT RTI.PACKING_SLIP
, RTI.COMMENTS
, RTI.CONTAINER_NUM
, RTI.COUNTRY_OF_ORIGIN_CODE
, RTI.BARCODE_LABEL
, RTI.TRUCK_NUM
, DOCUMENT_LINE_NUM
, TO_NUMBER(NULL)
, RTI.ITEM_DESCRIPTION
, RTI.VENDOR_LOT_NUM
, RTI.USE_MTL_SERIAL
, RTI.VENDOR_CUM_SHIPPED_QTY
, RTI.DELIVER_TO_LOCATION_CODE
, HE.FULL_NAME
, RTI.DESTINATION_TYPE_CODE
, RTI.LOCATOR
, RTI.NUM_OF_CONTAINERS
, POH.REVISION_NUM
, RTI.RMA_REFERENCE
, RTI.SUBINVENTORY
, RTI.TRANSFER_COST
, RTI.ACTUAL_COST
, RTI.TRANSPORTATION_ACCOUNT_ID
, RTI.TRANSPORTATION_COST
, RTI.PO_HEADER_ID
, RTI.PO_LINE_ID
, RTI.PO_LINE_LOCATION_ID
, RTI.QUANTITY
, RTI.UNIT_OF_MEASURE
, RTI.UOM_CODE
, RTI.ITEM_ID
, RTI.SHIP_TO_LOCATION_CODE
, RTI.TO_ORGANIZATION_ID
, RTI.PO_UNIT_PRICE
, RTI.VENDOR_ID
, RTI.VENDOR_ITEM_NUM
, PVS.VENDOR_SITE_CODE
, RTI.VENDOR_SITE_ID
, '' ATTRIBUTE15
, POV.VENDOR_NAME
, RHI.SHIPMENT_NUM
, '' LINE_SEQUENCE
, DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUMBER
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, '' QUANTITY_INVOICED
, '' TAXABLE_FLAG
, RTI.TAX_NAME
, RTI.TAX_AMOUNT
, RTI.CURRENCY_CODE
, RTI.CURRENCY_CONVERSION_RATE
, RTI.CURRENCY_CONVERSION_DATE
, '' LINE_IDENTIFIER
, TER.TERRITORY_SHORT_NAME COUNTRY_OF_ORIGIN_DISPLAYED
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) DUE_DATE
, PLL.QUANTITY QUANTITY_ORDERED
, PLL.QUANTITY_RECEIVED
, RTI.HEADER_INTERFACE_ID
, RTI.PROCESSING_STATUS_CODE
, RTI.TRANSACTION_STATUS_CODE
, '' CANCELLATION_STATUS
, PLL.ORG_ID
, PLL.PO_RELEASE_ID
, NVL(PRL.AGENT_ID
, POH.AGENT_ID)
FROM RCV_TRANSACTIONS_INTERFACE RTI
, RCV_HEADERS_INTERFACE RHI
, PO_HEADERS_ALL POH
, FND_TERRITORIES_VL TER
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PRL
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, PER_ALL_PEOPLE_F HE
WHERE POH.PO_HEADER_ID (+) = RTI.PO_HEADER_ID
AND TER.TERRITORY_CODE (+) = RTI.COUNTRY_OF_ORIGIN_CODE
AND RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID
AND PLL.LINE_LOCATION_ID = RTI.PO_LINE_LOCATION_ID
AND RTI.VENDOR_ID = POV.VENDOR_ID (+)
AND PVS.VENDOR_SITE_ID (+) = RTI.VENDOR_SITE_ID
AND MSI.ORGANIZATION_ID (+) = RTI.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RTI.ITEM_ID
AND HE.PERSON_ID (+) = NVL(RTI.DELIVER_TO_PERSON_ID
, -1)
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+)
AND HE.EFFECTIVE_END_DATE (+)
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID (+) UNION SELECT RSL.PACKING_SLIP
, RSL.COMMENTS
, RSL.CONTAINER_NUM
, RSL.COUNTRY_OF_ORIGIN_CODE
, RSL.BAR_CODE_LABEL BARCODE_LABEL
, RSL.TRUCK_NUM
, RSL.LINE_NUM DOCUMENT_LINE_NUM
, TO_NUMBER(NULL)
, RSL.ITEM_DESCRIPTION
, RSL.VENDOR_LOT_NUM
, TO_NUMBER('') USE_MTL_SERIAL
, TO_NUMBER('') VENDOR_CUM_SHIPPED_QTY
, '' DELIVER_TO_LOCATION_CODE
, HE.FULL_NAME DELIVER_TO_PERSON_NAME
, RSL.DESTINATION_TYPE_CODE
, '' LOCATOR
, RSH.NUM_OF_CONTAINERS
, POH.REVISION_NUM PO_REVISION_NUM
, '' RMA_REFERENCE
, RSL.TO_SUBINVENTORY SUBINVENTORY
, RSL.TRANSFER_COST
, TO_NUMBER('') ACTUAL_COST
, RSL.TRANSPORTATION_ACCOUNT_ID
, RSL.TRANSPORTATION_COST
, RSL.PO_HEADER_ID
, RSL.PO_LINE_ID
, RSL.PO_LINE_LOCATION_ID
, RSL.QUANTITY_SHIPPED QUANTITY
, RSL.UNIT_OF_MEASURE
, '' UOM_CODE
, RSL.ITEM_ID
, NVL( HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) SHIP_TO_LOCATION_CODE
, RSL.TO_ORGANIZATION_ID
, SHIPMENT_UNIT_PRICE PO_UNIT_PRICE
, POH.VENDOR_ID
, RSL.VENDOR_ITEM_NUM
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, PVS.VENDOR_SITE_ID
, RSL.ATTRIBUTE15
, POV.VENDOR_NAME
, RSH.SHIPMENT_NUM
, '' LINE_SEQUENCE
, DECODE(PRL.PO_RELEASE_ID
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || TO_CHAR(PRL.RELEASE_NUM)) PO_NUMBER
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, '' QUANTITY_INVOICED
, '' TAXABLE_FLAG
, RSL.TAX_NAME
, RSL.TAX_AMOUNT
, POH.CURRENCY_CODE
, POH.RATE
, POH.RATE_DATE
, '' LINE_IDENTIFIER
, TER.TERRITORY_SHORT_NAME COUNTRY_OF_ORIGIN_DISPLAYED
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) DUE_DATE
, PLL.QUANTITY QUANTITY_ORDERED
, PLL.QUANTITY_RECEIVED
, RSL.SHIPMENT_HEADER_ID HEADER_INTERFACE_ID
, '' PROCESSING_STATUS_CODE
, '' TRANSACTION_STATUS_CODE
, POS_CANCEL_ASN.GET_LINE_STATUS(RSL.SHIPMENT_LINE_ID) CANCELLATION_STATUS
, PLL.ORG_ID
, PLL.PO_RELEASE_ID
, NVL(PRL.AGENT_ID
, POH.AGENT_ID)
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_HEADERS_ALL POH
, FND_TERRITORIES_VL TER
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PRL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL_TL HRL
, HZ_LOCATIONS HZ
, MTL_SYSTEM_ITEMS_KFV MSI
, PER_ALL_PEOPLE_F HE
WHERE NVL(PLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED')
AND POH.PO_HEADER_ID (+) = RSL.PO_HEADER_ID
AND TER.TERRITORY_CODE (+) = RSL.COUNTRY_OF_ORIGIN_CODE
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND PLL.LINE_LOCATION_ID = RSL.PO_LINE_LOCATION_ID
AND POH.VENDOR_ID = POV.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND HRL.LOCATION_ID (+) = RSL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND HZ.LOCATION_ID(+) = RSL.SHIP_TO_LOCATION_ID
AND HE.PERSON_ID (+) = RSL.DELIVER_TO_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+)
AND HE.EFFECTIVE_END_DATE (+)
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID (+)
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN')

Columns