ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN A3FC6364E545E72DE053BB6B6358E0C9Y select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code owning_org, haouv.name organization_name, mp_from.organization_code from_org, mp_to.organization_code to_org, xxen_util.meaning(x.fob_point,'MTL_FOB_POINT',700) fob_point, &segment_columns msiv.concatenated_segments item_number, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, misv.inventory_item_status_code_tl item_status, &category_columns x.shipment_num shipment_number, x.receipt_num receipt_number, x.need_by_date, x.shipped_date, x.aging_date, x.arrival_date, msiv.primary_uom_code uom_code, sum(x.to_org_primary_quantity) intransit_quantity, gl.currency_code, sum(x.to_org_primary_quantity*nvl(cic.item_cost,0)) intransit_value from ( select ms.intransit_owning_org_id, ms.from_organization_id, ms.to_organization_id, decode(ms.intransit_owning_org_id,ms.to_organization_id,1,2) fob_point, ms.item_id, ms.to_org_primary_quantity, rsh.shipment_num, rsh.receipt_num, trunc(ms.need_by_date) need_by_date, trunc(rsh.shipped_date) shipped_date, case when sysdate-rsh.shipped_date<31 then '30 days' when sysdate-rsh.shipped_date<61 then '60 days' when sysdate-rsh.shipped_date<91 then '90 days' when sysdate-rsh.shipped_date<121 then '120 days' when sysdate-rsh.shipped_date<151 then '150 days' when sysdate-rsh.shipped_date<181 then '180 days' else 'Over 180 days' end aging_date, trunc(ms.receipt_date) arrival_date from mtl_supply ms, rcv_shipment_headers rsh where ms.supply_type_code in ('SHIPMENT','RECEIVING') and ms.shipment_header_id=rsh.shipment_header_id ) x, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, mtl_parameters mp_from, mtl_parameters mp_to, mtl_interorg_parameters mip, gl_code_combinations gcc, cst_item_costs cic, mtl_system_items_vl msiv, mtl_item_status_vl misv where 1=1 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and x.intransit_owning_org_id=mp.organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haouv.organization_id and nvl(haouv.date_to,sysdate+1)>sysdate and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and x.from_organization_id=mp_from.organization_id and x.to_organization_id=mp_to.organization_id and x.from_organization_id=mip.from_organization_id and x.to_organization_id=mip.to_organization_id and mip.intransit_inv_account=gcc.code_combination_id and x.intransit_owning_org_id=cic.organization_id and x.item_id=cic.inventory_item_id and mp.primary_cost_method=cic.cost_type_id and cic.organization_id=msiv.organization_id and cic.inventory_item_id=msiv.inventory_item_id and msiv.inventory_item_status_code=misv.inventory_item_status_code group by nvl(gl.short_name,gl.name), haouv_ou.name, mp.organization_code, haouv.name, mp_from.organization_code, mp_to.organization_code, x.fob_point, &segment_columns2 msiv.concatenated_segments, msiv.description, msiv.item_type, misv.inventory_item_status_code_tl, x.shipment_num, x.receipt_num, x.need_by_date, x.shipped_date, x.aging_date, x.arrival_date, msiv.primary_uom_code, gl.currency_code, msiv.inventory_item_id, msiv.organization_id &lp_min_intransit_qty_clause order by ledger, operating_unit, owning_org, from_org, to_org, &segment_columns2 item_number, aging_date Rebuilt to the Blitz Report SQL standards: unused cost group, make/buy lookup and shipment line joins removed, the aging bucket is computed once. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. #,##0.00000;[Red](#,##0.00000) US CAC Intransit Value (Real-Time) 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 (doug@volzconsulting.com) -- | -- | 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. -- | 1.12 27 Nov 2024 Eric Clegg. Added Minimum Absolute Intransit Quantity parameter -- | 1.13 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &category_columns &lp_min_intransit_qty_clause &segment_columns &segment_columns2 1=1 1 -10 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) Y US Chart of Accounts 2 &segment_columns2 select 'gcc.'||lower(fifsv.application_column_name)||',' text from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num US Chart of Accounts 3 10 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 4 20 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 5 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 6 40 1=1 msiv.concatenated_segments=:p_item_number LOV INV Item (master, costing enabled only) 91D022B15B12FFB9E053BB6B63587F0B select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US Item Number 7 50 1=1 mp.organization_code=:p_org_code LOV custom select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 8 60 1=1 haouv_ou.name=:p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and (:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 9 70 1=1 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger 10 80 &lp_min_intransit_qty_clause having abs(sum(x.to_org_primary_quantity))>=abs(:p_abs_min_qty) Number US Minimum Absolute Intransit Qty