select nvl(gl.short_name, gl.name) Ledger,
haou2.name Operating_Unit,
mp.organization_code Owning_Org,
haou.name Organization_Name,
mp2.organization_code From_Org,
mp3.organization_code To_Org,
ml.meaning FOB_Point,
&segment_columns
msiv.concatenated_segments Item_Number,
msiv.description Item_Description,
fcl.meaning Item_Type,
-- Revision for version 1.11
misv.inventory_item_status_code_tl Item_Status,
-- Revision for version 1.8
&category_columns
-- End revision for version 1.8
civv.shipment_num Shipment_Number,
civv.receipt_num Receipt_Number,
-- civv.freight_carrier_code Freight_Carrier,
-- civv.waybill_airbill_num Waybill,
trunc(civv.need_by_date) Need_by_Date,
-- civv.expected_delivery_date Expected_Delivery_Date,
trunc(civv.shipped_date) Shipped_Date,
case
when (sysdate - civv.shipped_date) < 31 then '30 days'
when (sysdate - civv.shipped_date) < 61 then '60 days'
when (sysdate - civv.shipped_date) < 91 then '90 days'
when (sysdate - civv.shipped_date) < 121 then '120 days'
when (sysdate - civv.shipped_date) < 151 then '150 days'
when (sysdate - civv.shipped_date) < 181 then '180 days'
else 'Over 180 days'
end Aging_Date,
trunc(civv.receipt_date) Arrival_Date,
-- Revision for version 1.11
muomv.uom_code UOM_Code,
sum(civv.to_org_primary_quantity) Intransit_Quantity,
gl.currency_code Currency_Code,
sum(civv.to_org_primary_quantity * nvl(cic.item_cost,0)) Intransit_Value
from
-- Revision for version 1.11
-- Need to derive the FOB_Point from mtl_supply as
-- the Shipping Network settings may have changed
-- after the intransit transaction has happened
-- cst_intransit_value_view civv,
(select sup.intransit_owning_org_id,
nvl(sup.cost_group_id,
decode(mp.primary_cost_method,
1, null,
2, mp.default_cost_group_id,
5, mp.default_cost_group_id,
6, mp.default_cost_group_id
)
) cost_group_id,
sup.from_organization_id,
sup.to_organization_id,
case
when sup.intransit_owning_org_id = sup.to_organization_id then 1
when sup.intransit_owning_org_id = sup.from_organization_id then 2
else 2
end fob_point,
rsh.shipment_num,
rsh.receipt_num,
sup.item_id inventory_item_id,
sup.item_revision,
sup.to_org_primary_quantity,
sup.to_org_primary_uom,
sup.receipt_date,
sup.need_by_date,
sup.expected_delivery_date,
rsh.shipped_date,
sup.destination_type_code
from rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
mtl_supply sup,
mtl_parameters mp
where sup.supply_type_code in ('SHIPMENT', 'RECEIVING')
and rsh.shipment_header_id = sup.shipment_header_id
and rsl.shipment_line_id = sup.shipment_line_id
and mp.organization_id = sup.intransit_owning_org_id
) civv,
mtl_interorg_parameters mip,
mtl_system_items_vl msiv,
-- Revision for version 1.11
mtl_units_of_measure_vl muomv,
mtl_item_status_vl misv,
-- End revision for version 1.11
cst_item_costs cic,
mfg_lookups ml,
mfg_lookups ml2,
fnd_common_lookups fcl,
mtl_parameters mp, -- owning org
mtl_parameters mp2, -- from org
mtl_parameters mp3, -- to org
gl_code_combinations gcc,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2,-- operating unit
gl_ledgers gl
where cic.organization_id = civv.intransit_owning_org_id
and cic.inventory_item_id = civv.inventory_item_id
and cic.inventory_item_id = msiv.inventory_item_id
and cic.organization_id = msiv.organization_id
and cic.cost_type_id = mp.primary_cost_method
-- Revision for version 1.11
and msiv.primary_uom_code = muomv.uom_code
and misv.inventory_item_status_code = msiv.inventory_item_status_code
-- End revision for version 1.11
and mip.from_organization_id = civv.from_organization_id
and mip.to_organization_id = civv.to_organization_id
and gcc.code_combination_id = mip.intransit_inv_account
and mp.organization_id = civv.intransit_owning_org_id
and mp2.organization_id = civv.from_organization_id
and mp3.organization_id = civv.to_organization_id
-- ===================================================================
-- -- joins for the lookup codes
-- ===================================================================
and ml.lookup_type = 'MTL_FOB_POINT'
and ml.lookup_code = civv.fob_point
and ml2.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and ml2.lookup_code = msiv.planning_make_buy_code
and fcl.lookup_type (+) = 'ITEM_TYPE'
and fcl.lookup_code (+) = msiv.item_type
-- =============================================================
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- =============================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mp.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = hoi.org_information3 -- this gets the operating unit id
and hoi.org_information1 = gl.ledger_id -- this gets the ledger id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_ledger, p_operating_unit, p_org_code
-- avoid selecting disabled inventory organizations
and sysdate < nvl(haou.date_to, sysdate + 1)
group by
nvl(gl.short_name, gl.name),
haou2.name,
mp.organization_code,
haou.name,
mp2.organization_code,
mp3.organization_code,
ml.meaning,
&segment_columns2
msiv.concatenated_segments,
msiv.description,
fcl.meaning,
-- Revision for version 1.11
misv.inventory_item_status_code_tl, -- Item_Status
civv.shipment_num,
civv.receipt_num,
-- civv.freight_carrier_code,
-- civv.waybill_airbill_num,
trunc(civv.need_by_date),
-- civv.expected_delivery_date,
trunc(civv.shipped_date),
case
when (sysdate - civv.shipped_date) < 31 then '30 days'
when (sysdate - civv.shipped_date) < 61 then '60 days'
when (sysdate - civv.shipped_date) < 91 then '90 days'
when (sysdate - civv.shipped_date) < 121 then '120 days'
when (sysdate - civv.shipped_date) < 151 then '150 days'
when (sysdate - civv.shipped_date) < 181 then '180 days'
else 'Over 180 days'
end,
trunc(civv.receipt_date),
-- Revision for version 1.11
muomv.uom_code,
gl.currency_code,
-- Needed for category column inline selects
msiv.inventory_item_id,
msiv.organization_id
-- order by ledger, owning org, operating unit, from org, to org, account segments and aging date
order by
nvl(gl.short_name, gl.name), -- Ledger
haou2.name, -- Operating_Unit
mp.organization_code, -- Owning_Org
mp2.organization_code, -- From_Org
mp3.organization_code, -- To_Org
&segment_columns2
msiv.concatenated_segments, -- Item_Number
case
when (sysdate - civv.shipped_date) < 31 then '30 days'
when (sysdate - civv.shipped_date) < 61 then '60 days'
when (sysdate - civv.shipped_date) < 91 then '90 days'
when (sysdate - civv.shipped_date) < 121 then '120 days'
when (sysdate - civv.shipped_date) < 151 then '150 days'
when (sysdate - civv.shipped_date) < 181 then '180 days'
else 'Over 180 days'
end -- Aging Date |