View: ITG_UPDEL_LINES_V

Product:	ITG - Internet Procurement Enterprise Connector
Description:	This view is used to generate XML at line level for Update Delivery XML
Implementation/DBA Data:	APPS.ITG_UPDEL_LINES_V

View Text

SELECT T.SHIPMENT_HEADER_ID SHIPMENT_HEADER_ID
, L.LINE_NUM LINE_NUM
, T.TRANSACTION_ID TRANSACTION_ID
, T.REQUEST_ID REQUEST_ID
, T.QUANTITY QUANT_RECV_QUANTITY
, UOM.UOM_CODE QUANT_RECV_UOMCODE
, T.TRANSACTION_DATE TRANSACTION_DATE_DELIVACT
, DECODE(L.ITEM_ID
, NULL
, NULL
, ITG_X_UTILS.SEGSTRING( 401
, 'MSTK'
, 101
, MI.SEGMENT1
, MI.SEGMENT2
, MI.SEGMENT3
, MI.SEGMENT4
, MI.SEGMENT5
, MI.SEGMENT6
, MI.SEGMENT7
, MI.SEGMENT8
, MI.SEGMENT9
, MI.SEGMENT10
, MI.SEGMENT11
, MI.SEGMENT12
, MI.SEGMENT13
, MI.SEGMENT14
, MI.SEGMENT15
, MI.SEGMENT16
, MI.SEGMENT17
, MI.SEGMENT18
, MI.SEGMENT19
, MI.SEGMENT20)) ITEM
, TO_CHAR(L.LINE_NUM ) RECLINENUM
, L.ITEM_DESCRIPTION DECSRIPTN
, L.ITEM_REVISION ITEMRV
, L.VENDOR_ITEM_NUM ITEMX
, L.COMMENTS LINE_NOTES
, DECODE(PH.SEGMENT1
, NULL
, NULL
, PH.SEGMENT1) POID
, TO_CHAR(PL.LINE_NUM) POLINENUM
, TO_CHAR(R.RELEASE_NUM) PORELEASE
, TO_CHAR(LOC.SHIPMENT_NUM) PSCLINENUM
, T.TRANSACTION_TYPE RECEPTDISP
, ORG.ORGANIZATION_NAME LINE_UA_ORG
, T.SUBINVENTORY LINE_UA_SUBINV
, DECODE(T.LOCATOR_ID
, NULL
, NULL
, ITG_X_UTILS.SEGSTRING( 401
, 'MTLL'
, 101
, ML.SEGMENT1
, ML.SEGMENT2
, ML.SEGMENT3
, ML.SEGMENT4
, ML.SEGMENT5
, ML.SEGMENT6
, ML.SEGMENT7
, ML.SEGMENT8
, ML.SEGMENT9
, ML.SEGMENT10
, ML.SEGMENT11
, ML.SEGMENT12
, ML.SEGMENT13
, ML.SEGMENT14
, ML.SEGMENT15
, ML.SEGMENT16
, ML.SEGMENT17
, ML.SEGMENT18
, ML.SEGMENT19
, ML.SEGMENT20)) LINE_UA_LOCATOR
, PER.FULL_NAME LINE_UA_DELIVERTO
, HRTL.LOCATION_CODE LINE_UA_DELVLOC
, T.TRANSACTION_ID LINE_UA_RCVTRANID
, T.DESTINATION_TYPE_CODE LINE_UA_DESTTYPE
, T.INSPECTION_STATUS_CODE LINE_UA_INSPSTS
, L.PACKING_SLIP LINE_UA_PACKINGSLIP
, T.RMA_REFERENCE LINE_UA_RMA
, ITG_X_UTILS.SIGNOF(T.QUANTITY)|| DECODE(MI.INVENTORY_ITEM_ID
, NULL
, 'N'
, 'Y')|| NVL(MI.INVENTORY_ASSET_FLAG
, 'N')|| NVL(MI.INVENTORY_ITEM_FLAG
, 'N')|| T.TRANSACTION_TYPE|| T.DESTINATION_TYPE_CODE LINE_UA_XREF
, HR2TL.LOCATION_CODE LINE_UA_SHIPTOLOC
, REASON.REASON_NAME LINE_UA_REASONCODE
, T.ATTRIBUTE1 LINE_UA_ATTRIBUTE1
, T.ATTRIBUTE2 LINE_UA_ATTRIBUTE2
, T.ATTRIBUTE3 LINE_UA_ATTRIBUTE3
, T.ATTRIBUTE4 LINE_UA_ATTRIBUTE4
, T.ATTRIBUTE5 LINE_UA_ATTRIBUTE5
, T.ATTRIBUTE6 LINE_UA_ATTRIBUTE6
, T.ATTRIBUTE7 LINE_UA_ATTRIBUTE7
, T.ATTRIBUTE8 LINE_UA_ATTRIBUTE8
, T.ATTRIBUTE9 LINE_UA_ATTRIBUTE9
, T.ATTRIBUTE10 LINE_UA_ATTRIBUTE10
, T.ATTRIBUTE11 LINE_UA_ATTRIBUTE11
, T.ATTRIBUTE12 LINE_UA_ATTRIBUTE12
, T.ATTRIBUTE13 LINE_UA_ATTRIBUTE13
, T.ATTRIBUTE14 LINE_UA_ATTRIBUTE14
, T.ATTRIBUTE15 LINE_UA_ATTRIBUTE15
, T.UNIT_OF_MEASURE TXN_UOM
, PH.SHIP_VIA_LOOKUP_CODE
, MI.SEGMENT1
FROM MTL_TRANSACTION_REASONS REASON
, MTL_SYSTEM_ITEMS MI
, MTL_ITEM_LOCATIONS ML
, MTL_UNITS_OF_MEASURE UOM
, PO_LINE_LOCATIONS_ALL LOC
, PO_RELEASES_ALL R
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PER_PEOPLE_F PER
, HR_LOCATIONS_ALL_TL HRTL
, HR_LOCATIONS_ALL_TL HR2TL
, ORG_ORGANIZATION_DEFINITIONS ORG
, RCV_SHIPMENT_HEADERS H
, RCV_SHIPMENT_LINES L
, RCV_TRANSACTIONS T
WHERE REASON.REASON_ID (+) = T.REASON_ID
AND UOM.UNIT_OF_MEASURE = T.UNIT_OF_MEASURE
AND ML.INVENTORY_LOCATION_ID (+) = T.LOCATOR_ID
AND ML.ORGANIZATION_ID (+) = T.ORGANIZATION_ID
AND MI.INVENTORY_ITEM_ID (+) = L.ITEM_ID
AND MI.ORGANIZATION_ID (+) = L.TO_ORGANIZATION_ID
AND R.PO_RELEASE_ID (+) = L.PO_RELEASE_ID
AND HR2TL.LOCATION_ID (+) = LOC.SHIP_TO_LOCATION_ID
AND NVL(HR2TL.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND LOC.LINE_LOCATION_ID (+) = L.PO_LINE_LOCATION_ID
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PL.PO_LINE_ID (+) = L.PO_LINE_ID
AND T.TRANSACTION_DATE BETWEEN NVL(PER.EFFECTIVE_START_DATE
, T.TRANSACTION_DATE)
AND NVL(PER.EFFECTIVE_END_DATE
, T.TRANSACTION_DATE)
AND PER.PERSON_ID (+) = T.DELIVER_TO_PERSON_ID
AND HRTL.LOCATION_ID (+) = T.LOCATION_ID
AND NVL(HRTL.LANGUAGE
, USERENV('LANG')) =USERENV('LANG')
AND ORG.ORGANIZATION_ID (+) = T.ORGANIZATION_ID
AND T.SHIPMENT_LINE_ID (+) = L.SHIPMENT_LINE_ID
AND H.SHIPMENT_HEADER_ID = L.SHIPMENT_HEADER_ID
AND ((T.TRANSACTION_TYPE = 'DELIVER'
AND T.DESTINATION_TYPE_CODE IN ('INVENTORY'
, 'EXPENSE')) OR (T.DESTINATION_TYPE_CODE = 'RECEIVING'
AND T.TRANSACTION_TYPE IN ('CORRECT'
, 'RETURN TO VENDOR')))

Columns