VIEW: APPS.ITG_UPDEL_LINES_V

Source

View Text - Preformatted

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 NVL(loc.shipment_type,'xx') <> 'PREPAYMENT'

View Text - HTML Formatted

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 NVL(LOC.SHIPMENT_TYPE
, 'XX') <> 'PREPAYMENT'