INV Inventory Packing Slip

select  distinct	
  	h.shipment_num SHIP_NUMBER,
	l.line_num line_no,
	nvl(mip.packslip_number,0) old_packslip_number,
	decode(mip.packslip_number,null, fnd_date.date_to_chardate(sysdate,2),
    fnd_date.date_to_chardate(mip.packslip_date,2)) DOC_DATE,
    hou.name name,
	substr(hrloc.address_line_1,1,60)  rep_ent_add1, 
	substr(hrloc.address_line_2,1,60)  rep_ent_add2,
	nvl(substr(hrloc.address_line_3,1,60),hrloc.town_or_city || decode(hrloc.town_or_city,'','',' ') || hrloc.postal_code || decode(hrloc.postal_code,'','',' ') || hrloc.country) rep_ent_add3,
	decode(hrloc.address_line_3,'','',hrloc.town_or_city || decode(hrloc.town_or_city,'','',' ') || hrloc.postal_code || decode(hrloc.town_or_city,'','',' ')  || hrloc.country) rep_ent_add4,
	NULL vat_registration_num,
	decode(hrl.GLOBAL_ATTRIBUTE_CATEGORY, 'JE.IT.PERWSLOC.LOC' ,hrl.global_attribute1, NULL)  fiscal_code,
	substr(hrl3.description,1,40) shipmaint_wharehouse,
	substr(hrl3.address_line_1,1,60)  shipmaint_address1, 
	substr(hrl3.address_line_2,1,60)  shipmaint_address2,
	nvl(substr(hrl3.address_line_3,1,60),hrl3.town_or_city || decode(hrl3.town_or_city,'','',' ') || hrl3.postal_code || decode(hrl3.postal_code,'','',' ') || hrl3.country) shipmaint_address3,
	decode(hrl3.address_line_3,'','',hrl3.town_or_city || decode(hrl3.town_or_city,'','',' ') || hrl3.postal_code || decode(hrl3.postal_code,'','',' ') || hrl3.country) shipmaint_address4,
	substr(hou.name,1,40) shipfrom_wharehouse,
	substr(hrl.address_line_1,1,60)  shipfrom_add1,
	substr(hrl.address_line_2,1,60)  shipfrom_add2,
	nvl(substr(hrl.address_line_3,1,60),hrl.town_or_city || decode(hrl.town_or_city,'','',' ') || hrl.postal_code || decode(hrl.postal_code,'','',' ') || hrl.country) shipfrom_add3,
	decode(hrl.address_line_3,'','',hrl.town_or_city || decode(hrl.town_or_city,'','',' ') || hrl.postal_code || decode(hrl.postal_code,'','',' ') || hrl.country) shipfrom_add4,
	substr(hou2.name,1,40) shipto_wharehouse,
	substr(hrl2.address_line_1,1,60)  shipto_add1, 
	substr(hrl2.address_line_2,1,60)  shipto_add2,
	nvl(substr(hrl2.address_line_3,1,60),hrl2.town_or_city || decode(hrl2.town_or_city,'','',' ') || hrl2.postal_code || decode(hrl3.postal_code,'','',' ') || hrl2.country) shipto_add3,
	decode(hrl2.address_line_3,'','',hrl2.town_or_city || decode(hrl2.town_or_city,'','',' ') || hrl2.postal_code || decode(hrl2.postal_code,'','',' ') || hrl2.country) shipto_add4,
    mtt.transaction_type_name reason,
	h.shipment_header_id ship_header_id,
	h.shipped_date,
	substr(l.item_description,1,40) item,
	mtluom.uom_code UOM,
	l.quantity_shipped QTY_SHIP,
	--orgf.description freight_carrier,Commented for bug 18842431
	h.num_of_containers num_containers,
	substr(h.comments,1,40) HEADER_COMMENTS, 
	INV_INVINPSR_XMLP_PKG.pslip_number(nvl ( mip.packslip_number , 0 ), l.line_num,h.shipment_header_id) C_PACKSLIP_NUM,
	--INV_INVINPSR_XMLP_PKG.ins_pkslip_rec(nvl ( mip.packslip_number , 0 ), l.line_num, :C_PACKSLIP_NUM, h.shipment_header_id) C_INS_PKSLIP_REC
	 INV_INVINPSR_XMLP_PKG.CF_FREIGHT_CODE(l.from_organization_id,h.freight_carrier_code) freight_carrier
from 
    rcv_shipment_headers h,
	mtl_material_transactions mmt,
	mtl_transaction_types mtt,
	rcv_shipment_lines l, 
	hr_all_organization_units hou,
	hr_all_organization_units hou2,
	hr_locations_all hrl,
	hr_locations_all hrl2,
	hr_locations_all hrl3,
	fnd_territories_vl ft,
	fnd_territories_vl ft2,
	fnd_territories_vl ft3,
	--org_freight orgf, Commented for bug 18842431
	mtl_inv_packslips mip,
	mtl_units_of_measure mtluom,
	hr_locations_all hrloc
where	h.shipment_header_id = l.shipment_header_id
and	h.shipment_num(+) = mmt.shipment_number
and l.mmt_transaction_id(+) = mmt.transaction_id
and	mmt.transaction_type_id = mtt.transaction_type_id
and mmt.organization_id = l.from_organization_id
and mmt.transaction_action_id = 21
and	upper(hrloc.office_site_flag) like 'Y%'
and	hrl3.location_id(+) = h.ship_to_location_id
and	hrl3.country = ft3.territory_code(+)
and hrloc.location_id 	= hou.location_id
and	hou.organization_id = l.from_organization_id
and	hou2.organization_id = l.to_organization_id
and	hrl.location_id = hou.location_id
and	hrl2.location_id = hou2.location_id
and	hrl.country = ft.territory_code(+)
and	hrl2.country = ft2.territory_code(+)
---and	orgf.freight_code(+) = h.freight_carrier_code Commented for bug 18842431
---and orgf.organization_id = :P_ISSUING_ORG    --add for bug 18072625--Commented for bug 18842431
and	mip.shipment_header_id(+) = h.shipment_header_id
and	mtluom.unit_of_measure(+) = l.unit_of_measure
and	l.from_organization_id = :P_ISSUING_ORG
&C_SHIPNUM_WHERE
&C_TRANS_TYPE_WHERE
&C_PACKSLIP_WHERE
&C_SHIP_VIA_WHERE
&C_RECEIVING_ORG_WHERE
ORDER BY 1,2