INV Purchasing Packing Slip

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Purchasing Packing Slip (XML)
Short Name: INVPOPSR_XML
DB package: INV_INVPOPSR_XMLP_PKG
		 select a.*,INV_INVPOPSR_XMLP_PKG.cf_doc_numformula(a.old_packslip_number,a.vencnt, a.vendor ) CF_DOC_NUM
		 from (
		 select 
	v.vendor_id   vendor,
	decode(mpp.packslip_date,null, sysdate, mpp.packslip_date) doc_date,
	nvl(mpp.packslip_number,0) old_packslip_number,
	hou.name shipfrom_name,
	substr(hrl.address_line_1,1,60)  shipfrom_line1, 
	substr(hrl.address_line_2,1,60)  shipfrom_line2,
	nvl(substr(hrl.address_line_3,1,60),hrl.town_or_city||' '||hrl.postal_code||' '||hrl.country) shipfrom_line3,
	decode(hrl.address_line_3,'','',hrl.town_or_city||' '||hrl.postal_code||' '||hrl.country) shipfrom_line4,
	NULL shipfrom_vatregno,
	decode(hrl.GLOBAL_ATTRIBUTE_CATEGORY, 'JE.IT.PERWSLOC.LOC' ,hrl.global_attribute1, NULL) shipfrom_fiscal_code,
	v.vendor_name shipto_name,
	substr(vs.address_line1,1,35)   shipto_line1, 
	substr(vs.address_line2,1,35)    shipto_line2,
	nvl(substr(vs.address_line3,1,35),vs.city||' '||vs.zip||' '||vs.country) shipto_line3,
	decode(vs.address_line3,'','',vs.city||' '||vs.zip||' '||vs.country) shipto_line4,
	NULL  dest_name, 
	NULL  dest_line1,
	NULL  dest_line2,
	NULL  dest_line3,
	NULL  dest_line4,
	(ph.segment1||'/'||pl.line_num||'/'||rh.receipt_num||'/'||rl.line_num)  shipment_number,
	trans.transaction_date  trans_date,
	msi.description   item_descript,
	mtluom.uom_code   uom,
	trans.quantity   qty_shipped,
	trans.transaction_id trans_id, 
	FND_DATE.DATE_TO_CHARDATE(trans.transaction_date,2) CF_SHIPPED_DATE,
	FND_DATE.DATE_TO_CHARDATE(decode(mpp.packslip_date,null, sysdate, mpp.packslip_date),2) CF_DOC_DATE,
	count(distinct v.vendor_id) over(partition by v.vendor_id) vencnt
	--&CF_doc_num CF_DOCUMENT_NO
from
	rcv_transactions trans,
	rcv_shipment_headers rh,
	rcv_shipment_lines rl,
	po_headers_all ph,
	po_lines_all pl,
	hr_locations_all hrl,
	hr_locations_all hrldest,
	po_vendors v,
	po_vendor_sites_all vs,
	mtl_system_items msi,
	mtl_units_of_measure mtluom,
	mtl_po_packslips mpp,
	hr_all_organization_units hou
where
rh.shipment_header_id = trans.shipment_header_id
and  rl.shipment_line_id = trans.shipment_line_id
and ph.po_header_id = trans.po_header_id
and pl.po_line_id = trans.po_line_id
and trans.transaction_type = 'RETURN TO VENDOR'
and trans.transaction_id = mpp.transaction_id(+)
and upper(hrl.office_site_flag) like 'Y%'
and ph.vendor_id = v.vendor_id
and ph.vendor_site_id = trans.vendor_site_id
and v.vendor_id = vs.vendor_id
and msi.inventory_item_id = pl.item_id
and mtluom.unit_of_measure(+) = rl.unit_of_measure
and trans.organization_id = msi.organization_id
and trans.vendor_site_id = vs.vendor_site_id
and hou.organization_id = trans.organization_id
AND hou.location_id  = hrl.location_id 
and vs.ship_to_location_id = hrldest.location_id(+)
and msi.organization_id =  :P_ORG_ID
&C_PACKSLIP_WHERE
&C_ITEM_WHERE
&C_VENDOR_WHERE
&C_SHIPMENT_DATE_WHERE 
ORDER BY 1 ASC,2 ASC,3 
  ASC,9 ASC,7 ASC,15 ASC,4 ASC,5 ASC,6 ASC,8 ASC,16 ASC,17 ASC,18 ASC,11 ASC,
  12 ASC,14 ASC,13 ASC,19 ASC,10 ASC , v.vendor_id , trans.transaction_id) a
Parameter Name SQL text Validation
Packslip Notes
 
Carrier
 
LOV Oracle
To
 
Number
Packslips From
 
Number
To
 
Date
Return Dates From
 
Date
To
 
LOV Oracle
Suppliers From
 
LOV Oracle
To
 
LOV Oracle
Items From
 
LOV Oracle
Organization
 
LOV Oracle