INV Purchasing Packing Slip

		 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