ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN EA0CC29A7E6C2B7BE0530100007F78FEY with pii as ( select /*+ materialize*/ cicd.organization_id, cicd.inventory_item_id, cct.cost_type pii_cost_type, sum(cicd.item_cost) pii_item_cost, sum(cicd.item_cost)*decode(sign(:p_sign_pii),-1,-1,1) corrected_pii_item_cost from mtl_parameters mp, cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where 3=3 and 5=5 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=cicd.organization_id and cicd.cost_type_id=cct.cost_type_id and cicd.resource_id=br.resource_id &only_no_pii group by cicd.organization_id, cicd.inventory_item_id, cct.cost_type ), wpii as ( select distinct y.organization_id, y.wip_entity_id, y.primary_item_id from ( select x.organization_id, x.wip_entity_id, x.primary_item_id, x.inventory_item_id from ( select wdj.organization_id, wdj.wip_entity_id, wdj.primary_item_id, wro.inventory_item_id, wro.quantity_issued from wip_discrete_jobs wdj, wip_requirement_operations wro, pii where (wdj.date_closed is null and wdj.creation_date<:p_trx_date_to+1 or wdj.date_closed>=:p_trx_date_to+1) and wdj.wip_entity_id=wro.wip_entity_id and wdj.organization_id=wro.organization_id and wro.wip_supply_type not in (4,6) and wro.organization_id=pii.organization_id and wro.inventory_item_id=pii.inventory_item_id and pii.pii_item_cost<>0 union all select wdj.organization_id, wdj.wip_entity_id, wdj.primary_item_id, mmt.inventory_item_id, mmt.primary_quantity from wip_discrete_jobs wdj, wip_requirement_operations wro, mtl_material_transactions mmt, pii where (wdj.date_closed is null and wdj.creation_date<:p_trx_date_to+1 or wdj.date_closed>=:p_trx_date_to+1) and wdj.wip_entity_id=wro.wip_entity_id and wdj.organization_id=wro.organization_id and wro.wip_supply_type not in (4,6) and wdj.wip_entity_id=mmt.transaction_source_id and wdj.organization_id=mmt.organization_id and mmt.transaction_source_type_id=5 and mmt.transaction_type_id in (35,43) and wro.operation_seq_num=mmt.operation_seq_num and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_date>=:p_trx_date_to+1 and mmt.organization_id=pii.organization_id and mmt.inventory_item_id=pii.inventory_item_id and pii.pii_item_cost<>0 ) x group by x.organization_id, x.wip_entity_id, x.primary_item_id, x.inventory_item_id having nvl(sum(x.quantity_issued),0)=0 ) y ) select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, x.organization_code org_code, haouv_ou_from.name from_ou, haouv_ou_to.name to_ou, xxen_util.meaning(x.fob_point,'MTL_FOB_POINT',700) fob_point, mp_from.organization_code ship_from_org, mp_to.organization_code ship_to_org, oap.period_name, &segment_columns msiv.concatenated_segments item_number, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, (select misv.inventory_item_status_code_tl from mtl_item_status_vl misv where msiv.inventory_item_status_code=misv.inventory_item_status_code) item_status, xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, &category_columns xxen_util.meaning(x.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, mtt.transaction_type_name transaction_type, mtst.transaction_source_type_name transaction_source, &p_show_subinventory &p_show_project &p_show_wip_job msiv.primary_uom_code uom_code, sum(x.primary_quantity) quantity, gl.currency_code, sum(x.matl_amount) material_amount, sum(x.matl_ovhd_amount) material_overhead_amount, sum(x.resource_amount) resource_amount, sum(x.osp_amount) outside_processing_amount, sum(x.overhead_amount) overhead_amount, sum(x.mta_amount) amount &p_show_pii from &mtl_sla_tables &subinventory_table &project_tables ( select mta.organization_id, mp.organization_code, case when mmt.transaction_action_id in (3,9,13,21,22) then mmt.organization_id when mmt.transaction_action_id in (10,12,15,17) then nvl(mmt.transfer_organization_id,mmt.organization_id) end xfer_from_org_id, case when mmt.transaction_action_id in (3,9,13,21,22) then nvl(mmt.transfer_organization_id,mmt.organization_id) when mmt.transaction_action_id in (10,12,15,17) then mmt.organization_id end xfer_to_org_id, mmt.fob_point, mmt.acct_period_id, mta.reference_account, mta.inv_sub_ledger_id, mta.inventory_item_id, mmt.transaction_source_type_id, mmt.transaction_action_id, mmt.transaction_type_id, mta.accounting_line_type, mmt.transaction_id, mmt.transfer_transaction_id, mmt.parent_transaction_id, mmt.trx_source_line_id, mmt.cost_update_id, mta.transaction_date, mta.creation_date, mmt.created_by, mmt.reason_id, regexp_replace(mmt.transaction_reference,'[^[:alnum:]'' '']',null) transaction_reference, decode(mta.accounting_line_type,1, decode(mmt.transaction_action_id, 2,decode(sign(mta.primary_quantity),1,mmt.transfer_subinventory,mmt.subinventory_code), 3,decode(mmt.organization_id,mta.organization_id,mmt.subinventory_code,mmt.transfer_subinventory), 21,decode(sign(mta.primary_quantity),1,mmt.transfer_subinventory,mmt.subinventory_code), 22,decode(sign(mta.primary_quantity),1,mmt.transfer_subinventory,mmt.subinventory_code), 28,decode(sign(mta.primary_quantity),1,mmt.transfer_subinventory,mmt.subinventory_code), mmt.subinventory_code) ) subinventory_code, mmt.transfer_subinventory, mmt.cost_group_id, mmt.transfer_cost_group_id, mmt.transaction_source_id, mmt.project_id, mmt.task_id, mmt.pm_cost_collected, we.wip_entity_name job, wac.class_code wip_class, xxen_util.meaning(wac.class_type,'WIP_CLASS_TYPE',700) class_type, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) wip_type, msiv_fg.concatenated_segments fg_item, msiv_fg.description fg_description, decode(mmt.transaction_action_id,24,mmt.quantity_adjusted,mta.primary_quantity) primary_quantity, mta.cost_element_id, mta.resource_id, decode(mta.cost_element_id,1,mta.base_transaction_value,0) matl_amount, decode(mta.cost_element_id,2,mta.base_transaction_value,0) matl_ovhd_amount, decode(mta.cost_element_id,3,mta.base_transaction_value,0) resource_amount, decode(mta.cost_element_id,4,mta.base_transaction_value,0) osp_amount, decode(mta.cost_element_id,5,mta.base_transaction_value,0) overhead_amount, mta.base_transaction_value mta_amount, case when mmt.transaction_action_id=24 then 0 when mta.accounting_line_type in (1,2,21,22,23,24,35,36) then decode(mta.cost_element_id,2,0,3,0,4,0,5,0,1) when mta.accounting_line_type=7 then decode(mta.cost_element_id,1,1,0) when mta.accounting_line_type in (5,9,10,14,16,20,29,30,31,34) then 1 else 0 end pii_function from mtl_parameters mp, mtl_transaction_accounts mta, mtl_material_transactions mmt, ( select we.wip_entity_id, we.organization_id, we.wip_entity_name, we.entity_type, we.primary_item_id, nvl(wdj.class_code,wfs.class_code) class_code from wip_entities we, wip_discrete_jobs wdj, wip_flow_schedules wfs where we.wip_entity_id=wdj.wip_entity_id(+) and we.wip_entity_id=wfs.wip_entity_id(+) ) we, wip_accounting_classes wac, mtl_system_items_vl msiv_fg where 2=2 and 3=3 and 4=4 and 6=6 and 7=7 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=mta.organization_id and mta.transaction_id=mmt.transaction_id and mta.inventory_item_id=mmt.inventory_item_id and decode(mta.transaction_source_type_id,5,mta.transaction_source_id)=we.wip_entity_id(+) and we.class_code=wac.class_code(+) and we.organization_id=wac.organization_id(+) and we.primary_item_id=msiv_fg.inventory_item_id(+) and we.organization_id=msiv_fg.organization_id(+) ) x, mtl_system_items_vl msiv, org_acct_periods oap, mtl_transaction_types mtt, mtl_txn_source_types mtst, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, mtl_parameters mp_from, mtl_parameters mp_to, hr_organization_information hoi_from, hr_all_organization_units_vl haouv_ou_from, hr_organization_information hoi_to, hr_all_organization_units_vl haouv_ou_to, pii, wpii, gl_code_combinations gcc where 1=1 and x.organization_id=msiv.organization_id and x.inventory_item_id=msiv.inventory_item_id and x.acct_period_id=oap.acct_period_id and x.transaction_type_id=mtt.transaction_type_id and x.transaction_source_type_id=mtst.transaction_source_type_id and x.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and x.xfer_from_org_id=mp_from.organization_id(+) and x.xfer_to_org_id=mp_to.organization_id(+) and nvl(x.xfer_from_org_id,x.organization_id)=hoi_from.organization_id and hoi_from.org_information_context='Accounting Information' and to_number(hoi_from.org_information3)=haouv_ou_from.organization_id and nvl(x.xfer_to_org_id,x.organization_id)=hoi_to.organization_id and hoi_to.org_information_context='Accounting Information' and to_number(hoi_to.org_information3)=haouv_ou_to.organization_id and x.organization_id=pii.organization_id(+) and x.inventory_item_id=pii.inventory_item_id(+) and x.organization_id=wpii.organization_id(+) and decode(x.transaction_source_type_id,5,x.transaction_source_id)=wpii.wip_entity_id(+) and x.inventory_item_id=wpii.primary_item_id(+) &subinv_table_joins &project_table_joins &mtl_sla_table_joins &p_show_non_sla group by nvl(gl.short_name,gl.name), haouv_ou.name, x.organization_code, haouv_ou_from.name, haouv_ou_to.name, x.fob_point, mp_from.organization_code, mp_to.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, msiv.description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, x.organization_id, x.inventory_item_id, x.accounting_line_type, mtt.transaction_type_name, mtst.transaction_source_type_name, &group_by_subinventory &group_by_project &group_by_wip_job &group_by_pii gl.currency_code order by nvl(gl.short_name,gl.name), haouv_ou.name, x.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, x.accounting_line_type, mtt.transaction_type_name &order_by_subinventory &order_by_project &order_by_wip_job ,x.organization_id Rebuilt to the Blitz Report SQL standards: one aggregation instead of two, accounting lines of type -1 (transferred to the general ledger, but without an accounting line type name) are no longer dropped, with Show SLA Accounting the amounts are those of the organization ledger (subledger lines of secondary and reporting ledgers were added in), Show WIP Job keeps the WIP transactions of jobs without an assembly or accounting class (non-standard jobs, repetitive schedules), the Numeric Sign for PII parameter takes effect and applies to the Net Amount, PII Zero Component Quantity is shown only for WIP valuation lines of completions and scrap, component returns after the period end are added back when testing that quantity. 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. US CAC ICP PII Material Account Summary Use this report to eliminate your internal profit in inventory (PII) at month-end. This report to sums up the material accounting entries for each item, organization, subinventory with the original amount, profit in inventory and net amounts. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. Use Show Subinventories to display or not display the subinventory information. Use Show Projects to display or not display the project number and name and use Show WIP Job to display or not display the WIP job information (WIP class, class type, WIP job, description, assembly number and assembly description). For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Both Flow and Workorderless show up as the WIP Type "Flow schedule". And for WIP Completions and WIP Completion Returns, this report also has a column "PII Zero Component Quantity" indicating if the underlying components, which have PII, were not issued to the job. If not, the PII amount for WIP completions, for WIP jobs that were open, may be overstated as the PII is actually still sitting in the onhand inventory. Note: there is a hidden parameter, Numeric Sign for PII, which allows you to set the sign of the profit in inventory amounts. You can specify positive or negative values based on how you enter PII amounts into your PII Cost Type. Defaulted as positive (+1). Note: The SQL logic and code for this report is identical to the CAC Material Account Summary report. /* +=============================================================================+ -- | Copyright 2009- 2022 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. -- +=============================================================================+ -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 06 Nov 2009 Douglas Volz Initial Coding -- | 1.27 07 Oct 2022 Douglas Volz Correction for period name joins for interorg transactions. -- | 1.28 16 Oct 2022 Douglas Volz Correction for quantity calculations and PII logic. -- | 1.29 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &category_columns &group_by_pii &group_by_project &group_by_subinventory &group_by_wip_job &mtl_sla_table_joins &mtl_sla_tables &only_no_pii &order_by_project &order_by_subinventory &order_by_wip_job &p_show_non_sla &p_show_pii &p_show_project &p_show_subinventory &p_show_wip_job &project_table_joins &project_tables &segment_columns &segment_columns_grp &subinv_table_joins &subinventory_table 1=1 2=2 3=3 4=4 5=5 6=6 7=7 :p_sign_pii :p_trx_date_to 1 -30 &p_show_pii , xxen_util.yes(case when x.accounting_line_type=7 and x.transaction_type_id in (17,44,90,91) and wpii.wip_entity_id is not null then 'Y' end) pii_zero_component_quantity, pii.pii_cost_type, nvl(pii.pii_item_cost,0) pii_item_cost, round(sum(x.primary_quantity)*nvl(pii.pii_item_cost,0)*x.pii_function*decode(sum(x.mta_amount),0,0,1),2) pii_amount, sum(x.mta_amount)-round(sum(x.primary_quantity)*nvl(pii.corrected_pii_item_cost,0)*x.pii_function*decode(sum(x.mta_amount),0,0,1),2) net_amount LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Show Profit in Inventory 2 &group_by_pii pii.pii_cost_type, pii.pii_item_cost, pii.corrected_pii_item_cost, x.pii_function, xxen_util.yes(case when x.accounting_line_type=7 and x.transaction_type_id in (17,44,90,91) and wpii.wip_entity_id is not null then 'Y' end), Y US Show Profit in Inventory 3 &only_no_pii and 1=2 N US Show Profit in Inventory 4 -20 :p_sign_pii Number 1 US Numeric Sign for PII 5 -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 6 &segment_columns_grp 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 7 10 5=5 cct.cost_type=:p_pii_cost_type LOV CST Cost Type (No AvgRates) 91D3167A335B1204E053BB6B63585EE3 select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null) order by cct.cost_type desc PII Y US PII Cost Type 8 20 5=5 br.resource_code=:p_pii_sub_element LOV CST PII Sub-Elements E234C6573B214FDAE05369FB090533D7 select br.resource_code value, br.description from bom_resources br where br.allow_costs_flag<>2 and (br.resource_code like '%PII%' or br.resource_code like '%ICP%') and (:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y')) and nvl(br.disable_date,sysdate)>=sysdate order by br.resource_code PII Y US PII Sub-Element 9 30 2=2 mta.transaction_date>=:p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date From 10 40 2=2 mta.transaction_date<:p_trx_date_to+1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date To 11 50 &mtl_sla_table_joins and x.inv_sub_ledger_id=xdl.source_distribution_id_num_1 and xdl.source_distribution_type='MTL_TRANSACTION_ACCOUNTS' and xdl.application_id=707 and xdl.ae_header_id=xal.ae_header_id and xdl.ae_line_num=xal.ae_line_num and xal.application_id=707 and xal.ae_header_id=xah.ae_header_id and xah.application_id=707 and xah.ledger_id=gl.ledger_id and xal.code_combination_id=gcc.code_combination_id(+) LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Show SLA Accounting 12 &mtl_sla_tables xla_distribution_links xdl, xla_ae_headers xah, xla_ae_lines xal, Y US Show SLA Accounting 13 &p_show_non_sla and x.reference_account=gcc.code_combination_id(+) N US Show SLA Accounting 14 60 &p_show_subinventory msub.secondary_inventory_name subinventory, msub.description subinventory_description, LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Show Subinventory 15 &group_by_subinventory msub.secondary_inventory_name, msub.description, Y US Show Subinventory 16 &order_by_subinventory ,msub.secondary_inventory_name Y US Show Subinventory 17 &subinv_table_joins and x.subinventory_code=msub.secondary_inventory_name(+) and x.organization_id=msub.organization_id(+) Y US Show Subinventory 18 &subinventory_table mtl_secondary_inventories msub, Y US Show Subinventory 19 70 &p_show_project pp.segment1 project_number, pp.name project_name, LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Show Projects 20 &group_by_project pp.segment1, pp.name, Y US Show Projects 21 &order_by_project ,pp.segment1 ,pp.name Y US Show Projects 22 &project_table_joins and x.project_id=pp.project_id(+) Y US Show Projects 23 &project_tables pa_projects_all pp, Y US Show Projects 24 80 &p_show_wip_job x.wip_class, x.class_type, x.wip_type, x.job wip_job, x.fg_item assembly_number, x.fg_description assembly_description, LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y US Show WIP Job 25 &group_by_wip_job x.wip_class, x.class_type, x.wip_type, x.transaction_source_id, x.job, x.fg_item, x.fg_description, Y US Show WIP Job 26 &order_by_wip_job ,x.wip_class ,x.wip_type ,x.job Y US Show WIP Job 27 90 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') 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 28 100 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') 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 29 110 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') 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 30 120 4=4 mta.inventory_item_id in (select msiv.inventory_item_id from mtl_system_items_vl msiv where mta.organization_id=msiv.organization_id and 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) and msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US Item Number 31 130 3=3 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 32 140 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 33 150 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 0F952148E8CF5BF2AD2E4B7B867FBB20 Pivot by Org Y MFG 25 ACCOUNTING_LINE_TYPE 36 AMOUNT -27.0037 ASSEMBLY_DESCRIPTION -27.0036 ASSEMBLY_NUMBER 12 Account -27.0033 CLASS_TYPE 30 CURRENCY_CODE 23 Class 10 Company 11 Department 6 FOB_POINT 4 FROM_OU 22 Family 16 ITEM_DESCRIPTION 15 ITEM_NUMBER 18 ITEM_STATUS 17 ITEM_TYPE 24 Inv.Items Description 1 LEDGER 19 MAKE_BUY_CODE 31 MATERIAL_AMOUNT 32 MATERIAL_OVERHEAD_AMOUNT 41 NET_AMOUNT 2 OPERATING_UNIT 3 ORG_CODE 34 OUTSIDE_PROCESSING_AMOUNT 35 OVERHEAD_AMOUNT 9 PERIOD_NAME 40 PII_AMOUNT 38 PII_COST_TYPE 39 PII_ITEM_COST 37 PII_ZERO_COMPONENT_QUANTITY -27.0031 PROJECT_NAME -27.003 PROJECT_NUMBER 14 Product 20 Product Cat 21 Product Description 29 QUANTITY 33 RESOURCE_AMOUNT 7 SHIP_FROM_ORG 8 SHIP_TO_ORG -27.0028 SUBINVENTORY -27.0029 SUBINVENTORY_DESCRIPTION 13 Sub-Account 5 TO_OU 27 TRANSACTION_SOURCE 26 TRANSACTION_TYPE 28 UOM_CODE -27.0032 WIP_CLASS -27.0035 WIP_JOB -38.0039 WIP_PII_COMPONENT_QUANTITY -27.0034 WIP_TYPE ACCOUNTING_LINE_TYPE ROW 4 AMOUNT VALUE 7 SUM LEDGER ROW 1 MATERIAL_AMOUNT VALUE 2 SUM MATERIAL_OVERHEAD_AMOUNT VALUE 3 SUM NET_AMOUNT VALUE 9 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 OUTSIDE_PROCESSING_AMOUNT VALUE 5 SUM OVERHEAD_AMOUNT VALUE 6 SUM PII_AMOUNT VALUE 8 SUM QUANTITY VALUE 1 SUM RESOURCE_AMOUNT VALUE 4 SUM S Site 0F952148E8CF5BF2AD2E4B7B867FBB20