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 |
