CAC Intransit Value (Real-Time)

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to show intransit values across all ledgers for current onhand balances and current costing method costs. This is a "real-time" report, showing the quantities and values at the time you run this report. (Used the cst_intransit_value_view to simplify the design.)

/* +=============================================================================+
-- | Copyright 2009-20 Douglas Vol ...  Report to show intransit values across all ledgers for current onhand balances and current costing method costs. This is a "real-time" report, showing the quantities and values at the time you run this report. (Used the cst_intransit_value_view to simplify the design.)

/* +=============================================================================+
-- | Copyright 2009-20 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise is included in this |
-- | permission. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_intransit_value_report.sql
-- |
-- | Parameters:
-- | p_org_code -- Specific inventory organization you wish to report (optional)
-- | p_operating_unit -- Operating Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- |
-- | Description:
-- | Report to show intransit values across all ledgers, for current onhand
-- | balances and current costing method costs. This is a "real-time" report,
-- | showing the quantities and values at the time you run this report.
-- | (Note: Used the cst_intransit_value_view to simplify the design.)
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 18 Nov 2008 Douglas Volz Initial Coding
-- | 1.1 10 Dec 2009 Douglas Volz Modified for Celgene
-- | 1.2 04 Jan 2010 Douglas Volz Added intransit value accounts
-- | 1.3 03 Mar 2010 Douglas Volz Set the company account number based on
-- | the item's cost of goods sold account
-- | 1.4 04 Mar 2010 Douglas Volz Screen out invalid Intransit balances from
-- | Dec 2009 transactions for Ledger CHE_EUR_PL.
-- | These intransit balances were written-off
-- | in the G/L in December 2009. Was not able
-- | to get these entries fixed in Oracle.
-- | 1.10 23 Apr 2020 Douglas Volz Changed to multi-language views for the item
-- | master, item categories and operating units.
-- | 1.11 23 Aug 2020 Douglas Volz Modified to get intransit_owning_org_id directly
-- | from mtl_supply; if you change the shipping
-- | network FOB Point and you can no longer get
-- | the FOB Point from cst_intransit_value_view.
-- +=============================================================================+*/   more
Run CAC Intransit Value (Real-Time) and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Category Set 1 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:category_set_name1 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substrb(mcsv.category_set_name||' Description',1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name1
 LOV
Category Set 2 
select x.* from (
select
'(select min(mcb.'||lower(fifsv.application_column_name)||') keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_b mcb where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mcb.category_id) "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv,
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
mcsv.category_set_name=:category_set_name2 and
mcsv.structure_id=fifsv.id_flex_num and
fifsv.application_id=401 and
fifsv.id_flex_code='MCAT' and
fifsv.enabled_flag='Y'
order by
fifsv.id_flex_num,
fifsv.segment_num
) x
union all
select
'(select min(mct.description) keep (dense_rank first order by mic.category_id) from mtl_item_categories mic, mtl_categories_tl mct where mic.category_set_id='||mcsv.category_set_id||' and msiv.organization_id=mic.organization_id and msiv.inventory_item_id=mic.inventory_item_id and mic.category_id=mct.category_id and mct.language=userenv(''lang'')) "'||substrb(mcsv.category_set_name||' Description',1,xxen_report.max_column_length)||'",' sql_text
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_name=:category_set_name2
 LOV
Item Number 
msiv.concatenated_segments = :p_item_number
 LOV
Organization Code 
mp.organization_code = :p_org_code
 LOV
Operating Unit 
haou2.name = :p_operating_unit
 LOV
Ledger 
gl.name = :p_ledger
 LOV
Download
 
Related Blitz Reports
CAC ICP PII Material Account Detail CAC Material Account Detail CAC Receiving Value (Period-End) MRP Pegging CAC PO Price vs. Costing Method Comparison CAC Purchase Price Variance CAC Material Account Summary CAC Inventory Pending Cost Adjustment CAC ICP PII Inventory Pending Cost Adjustment
Blitz Report™

Blitz Report™ provides multiple benefits: