select DISTINCT mtrh.request_number,
mtrh.header_status,
mtrh.header_id HEADER_ID1,
hrl.address_line_1 || decode(hrl.address_line_2, null, '', hrl.address_line_2 || ', ') || hrl.address_line_3 || ', ' || hrl.town_or_city || ', ' || hrl.region_2 || ' ' || hrl.postal_code || ', ' || te.territory_short_name address_line,
hr2.name,
mln.meaning
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
hr_locations hrl,
hr_organization_units hr2,
fnd_territories_vl te,
mtl_trx_types_view mttv,
mtl_material_transactions mmt,
mfg_lookups mln
WHERE mtrh.ship_to_location_id = hrl.location_id
AND te.territory_code = hrl.country
AND mtrh.header_id = mtrl.header_id
AND hr2.organization_id = mtrh.organization_id
AND mtrh.header_status = mln.LOOKUP_CODE
AND mln.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
AND mln.meaning in ('Approved', 'Closed', 'Canceled')
AND mtrl.quantity_delivered > 0
AND mmt.move_order_line_id = mtrl.line_id
AND mttv.transaction_action_id = 1
AND mttv.transaction_source_type_id = 4
AND mttv.type_class = 1
AND mttv.transaction_type_id = mtrh.transaction_type_id
&L_WHERE_CLAUSE1
ORDER BY mtrh.request_number |