CSE Bill of Lading

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