INV Inventory Packing Slip

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Inventory Packing Slip (XML)
Short Name: INVINPSR_XML
DB package: INV_INVINPSR_XMLP_PKG
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
Parameter Name SQL text Validation
Print Shipper Details {Yes/No}
 
LOV Oracle
To
 
LOV Oracle
Reasons From
 
LOV Oracle
To
 
LOV Oracle
Packing Slips From
 
LOV Oracle
To
 
LOV Oracle
Freight Carriers From
 
LOV Oracle
To
 
LOV Oracle
Receiving Warehouses From
 
LOV Oracle
To
 
LOV Oracle
Shipment Numbers From
 
LOV Oracle
Issuing_Warehouse
 
LOV Oracle