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 |
