VIEW: APPS.POS_LINES_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')