ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN D2E7E3F633351F0DE0530100007F46EFY with j as ( select /*+ materialize*/ nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code, gl.currency_code, oap.period_name, oap.period_start_date, oap.schedule_close_date, case when wdj.date_closed>=oap.period_start_date and wdj.date_closed<oap.schedule_close_date+1 then 'Variance' else 'Valuation' end report_type, wdj.organization_id, wdj.wip_entity_id, wdj.class_code, wac.class_type, wdj.status_type, wdj.project_id, wdj.primary_item_id, wdj.lot_number, wdj.creation_date, wdj.scheduled_start_date, wdj.date_released, wdj.date_completed, trunc(wdj.date_closed) date_closed, wdj.last_update_date, wdj.start_quantity, wdj.material_account, wdj.material_overhead_account, wdj.resource_account, wdj.outside_processing_account, wdj.overhead_account, wdj.quantity_completed-nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_type_id in (17,44) and mmt.transaction_date>=oap.schedule_close_date+1),0) quantity_completed, wdj.quantity_scrapped-nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_type_id in (90,91) and mmt.transaction_date>=oap.schedule_close_date+1),0) quantity_scrapped, cct.cost_type_id, cct.cost_type, mp.primary_cost_method, nvl((select cic.cost_type_id from cst_item_costs cic where cct.cost_type_id=cic.cost_type_id and wdj.organization_id=cic.organization_id and wdj.primary_item_id=cic.inventory_item_id),mp.primary_cost_method) assembly_cost_type_id from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, cst_cost_types cct, org_acct_periods oap, wip_discrete_jobs wdj, wip_accounting_classes wac where 1=1 and 2=2 and 3=3 and 4=4 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id<>mp.master_organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and sysdate<nvl(haou.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and cct.cost_type_id=nvl((select cct2.cost_type_id from cst_cost_types cct2 where cct2.cost_type=:p_cost_type),mp.primary_cost_method) and mp.organization_id=oap.organization_id and mp.organization_id=wdj.organization_id and wdj.class_code=wac.class_code and wdj.organization_id=wac.organization_id and wac.class_type in (1,3,5) and ( wdj.date_closed>=oap.period_start_date and wdj.date_closed<oap.schedule_close_date+1 and :p_report_option in ('Closed jobs','All jobs') or (wdj.date_closed is null or wdj.date_closed>=oap.schedule_close_date+1) and :p_report_option in ('Open jobs','All jobs') and ( exists (select null from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_date>=oap.period_start_date and mmt.transaction_date<oap.schedule_close_date+1) or wdj.creation_date<oap.schedule_close_date+1 and not exists (select null from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id) ) ) ), c as ( select /*+ materialize*/ j.wip_entity_id, j.organization_id, wro.operation_seq_num, wro.inventory_item_id, wro.wip_supply_type, nvl(wro.basis_type,1) basis_type, decode(nvl(wro.basis_type,1),2,nvl(wro.required_quantity,1),nvl(wro.quantity_per_assembly,0)/nvl(wro.component_yield_factor,1)) quantity_per_assembly, round( case when :p_use_completion_qtys='Y' then decode(nvl(wro.basis_type,1),2,nvl(wro.quantity_per_assembly,0),nvl(wro.quantity_per_assembly,0)/nvl(wro.component_yield_factor,1)*decode(j.class_type,5,j.quantity_completed,j.quantity_completed+decode(:p_include_scrap,'Y',j.quantity_scrapped,0))) else wro.required_quantity-decode(:p_include_scrap,'Y',0,nvl(wro.quantity_per_assembly,0)*j.quantity_scrapped) end,6) total_req_quantity, nvl(wro.quantity_issued,0)+nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wro.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wro.organization_id=mmt.organization_id and wro.operation_seq_num=mmt.operation_seq_num and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_type_id in (35,43) and mmt.transaction_date>=j.schedule_close_date+1),0) quantity_issued, case when not exists (select null from mtl_material_transactions mmt, mtl_cst_actual_cost_details mcacd where wro.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wro.organization_id=mmt.organization_id and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_date>=j.period_start_date and mmt.transaction_date<j.schedule_close_date+1 and mmt.transaction_id=mcacd.transaction_id) then nvl((select cic.cost_type_id from cst_item_costs cic where j.cost_type_id=cic.cost_type_id and wro.organization_id=cic.organization_id and wro.inventory_item_id=cic.inventory_item_id),j.primary_cost_method) end standard_cost_type_id from j, wip_requirement_operations wro where 6=6 and j.wip_entity_id=wro.wip_entity_id and j.organization_id=wro.organization_id and wro.wip_supply_type<>6 and wro.operation_seq_num>0 ) select x.report_type, x.ledger, x.operating_unit, x.organization_code org_code, x.period_name, &segment_columns x.class_code wip_class, xxen_util.meaning(x.class_type,'WIP_CLASS_TYPE',700) class_type, we.wip_entity_name wip_job, (select ppa.segment1 from pa_projects_all ppa where x.project_id=ppa.project_id) project_number, xxen_util.meaning(x.status_type,'WIP_JOB_STATUS',700) job_status, x.creation_date, x.scheduled_start_date, x.date_released, x.date_completed, x.date_closed, x.last_update_date, msiv.primary_uom_code uom_code, msiv.std_lot_size item_std_lot_size, x.cost_type lot_size_cost_type, nvl(cic.lot_size,1) costing_lot_size, x.start_quantity, x.quantity_completed assembly_quantity_completed, x.quantity_scrapped assembly_quantity_scrapped, x.quantity_completed+x.quantity_scrapped total_assembly_quantity, msiv.concatenated_segments assembly, msiv.description assembly_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, msiv.inventory_item_status_code item_status, xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, &category_columns x.lot_number, x.operation_seq_num operation_seq_number, bd.department_code department, msiv_comp.concatenated_segments component_number, msiv_comp.description component_description, xxen_util.meaning(msiv_comp.item_type,'ITEM_TYPE',3) component_item_type, msiv_comp.inventory_item_status_code component_status_code, xxen_util.meaning(msiv_comp.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) component_make_buy_code, xxen_util.meaning(x.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type, xxen_util.meaning(x.basis_type,'CST_BASIS',700) component_basis_type, nvl2(x.cost_element_id,x.cost_type,null) cost_type, x.currency_code, nvl(xxen_util.meaning(x.cost_element_id,'CST_COST_CODE_TYPE',700),'No Cost') cost_element_type, x.item_cost, msiv_comp.primary_uom_code uom_code, x.quantity_per_assembly, round(x.total_req_quantity,3) total_required_quantity, nvl(to_char(x.last_transaction_date,'DD-Mon-RRRR HH24:MI:SS'),'No WIP Issue') last_transaction_date, nvl(x.last_transaction_type,'No WIP Issue') last_transaction_type, round(x.quantity_issued,3) quantity_issued, round(x.quantity_issued-x.total_req_quantity,3) quantity_left_in_wip, round(x.total_req_quantity*x.item_cost,fc.precision) wip_standard_component_value, round(x.quantity_issued*x.item_cost,fc.precision) applied_component_value, case when x.quantity_completed+x.quantity_scrapped=0 and x.quantity_issued*x.item_cost=0 then 0 else (x.quantity_issued-x.total_req_quantity)*x.item_cost end material_usage_variance, xxen_util.yes(nvl2(cicd.last_rollup_date,'Y',null)) rolled_up, cicd.last_rollup_date last_cost_rollup from ( select j.*, c.operation_seq_num, c.inventory_item_id, c.wip_supply_type, c.basis_type, c.quantity_per_assembly, c.total_req_quantity, c.quantity_issued, e.cost_element_id, nvl(e.item_cost,0) item_cost, (select max(mmt.transaction_date) from mtl_material_transactions mmt where c.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and c.organization_id=mmt.organization_id and c.inventory_item_id=mmt.inventory_item_id and mmt.transaction_date<j.schedule_close_date+1) last_transaction_date, (select max(mtt.transaction_type_name) keep (dense_rank last order by mmt.transaction_date) from mtl_material_transactions mmt, mtl_transaction_types mtt where c.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and c.organization_id=mmt.organization_id and c.inventory_item_id=mmt.inventory_item_id and mmt.transaction_date<j.schedule_close_date+1 and mmt.transaction_type_id=mtt.transaction_type_id) last_transaction_type, (select wo.department_id from wip_operations wo where c.wip_entity_id=wo.wip_entity_id and c.operation_seq_num=wo.operation_seq_num) department_id, decode(e.cost_element_id,2,j.material_overhead_account,3,j.resource_account,4,j.outside_processing_account,5,j.overhead_account,j.material_account) account from j, c, ( select y.wip_entity_id, y.inventory_item_id, y.cost_element_id, sum(y.item_cost) item_cost from ( select mmt.transaction_source_id wip_entity_id, mmt.inventory_item_id, mcacd.cost_element_id, max(nvl(mcacd.actual_cost,0)) item_cost from j, mtl_material_transactions mmt, mtl_cst_actual_cost_details mcacd where j.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and j.organization_id=mmt.organization_id and mmt.transaction_date>=j.period_start_date and mmt.transaction_date<j.schedule_close_date+1 and mmt.transaction_id=mcacd.transaction_id group by mmt.transaction_source_id, mmt.inventory_item_id, mcacd.cost_element_id, mcacd.level_type union all select c.wip_entity_id, c.inventory_item_id, cicd.cost_element_id, cicd.item_cost from c, cst_item_cost_details cicd where c.organization_id=cicd.organization_id and c.inventory_item_id=cicd.inventory_item_id and c.standard_cost_type_id=cicd.cost_type_id ) y group by y.wip_entity_id, y.inventory_item_id, y.cost_element_id ) e where j.wip_entity_id=c.wip_entity_id and c.wip_entity_id=e.wip_entity_id(+) and c.inventory_item_id=e.inventory_item_id(+) ) x, wip_entities we, mtl_system_items_vl msiv, mtl_system_items_vl msiv_comp, cst_item_costs cic, (select cicd.organization_id, cicd.inventory_item_id, cicd.cost_type_id, max(cicd.creation_date) last_rollup_date from cst_item_cost_details cicd where cicd.rollup_source_type=3 and cicd.attribute15 is null group by cicd.organization_id, cicd.inventory_item_id, cicd.cost_type_id) cicd, bom_departments bd, fnd_currencies fc, gl_code_combinations gcc where 5=5 and x.quantity_per_assembly+round(x.quantity_issued,3)<>0 and x.wip_entity_id=we.wip_entity_id and x.organization_id=msiv.organization_id and x.primary_item_id=msiv.inventory_item_id and x.organization_id=msiv_comp.organization_id and x.inventory_item_id=msiv_comp.inventory_item_id and x.organization_id=cic.organization_id and x.primary_item_id=cic.inventory_item_id and x.assembly_cost_type_id=cic.cost_type_id and decode(cic.based_on_rollup_flag,1,cic.organization_id)=cicd.organization_id(+) and cic.inventory_item_id=cicd.inventory_item_id(+) and cic.cost_type_id=cicd.cost_type_id(+) and x.department_id=bd.department_id(+) and x.currency_code=fc.currency_code and x.account=gcc.code_combination_id(+) order by x.report_type, x.ledger, x.operating_unit, x.organization_code, x.class_code, we.wip_entity_name, x.operation_seq_num, component_number, department Rebuilt to the Blitz Report SQL standards: one row per job, operation, component and cost element, quantities no longer double when two cost rows of an element carry the same cost, standard cost details with equal amounts are no longer collapsed into one, completion returns, component returns and assembly scrap after the period end are rolled back with the right sign, the Component Number parameter applies to the component, rows without a make or buy code are no longer missing, Rolled Up shows Yes or blank. 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 WIP Material Usage Variance Report your material usage variances for your open and closed WIP jobs. This report replicates the Material Variance Section for the Oracle Discrete Job Value - Standard Costing report. If the job is open the Report Type column displays "Valuation", as this WIP job and potential material usage variance is still in your WIP inventory balances. If the job has been closed during the reporting period, the Report Type column displays "Variance", as this WIP job was written off on a WIP Job Close Variance transaction. You can report prior periods and this report will automatically adjust the assembly completion quantities and component issue quantities to reflect the quantities for the specified accounting period, as well as report only jobs which were open or closed during that prior period. Closed, Pending Close, Cancelled, Complete and Complete No Charges WIP job statuses use the completion quantities. All other WIP jobs use the parameter "Use Completion Quantities". Parameters: ========== Report Option: Open jobs, Closed jobs or All jobs. Use this to limit the size of the report. (mandatory) Period Name: the accounting period you wish to report. (mandatory) Cost Type: defaults to your Costing Method; if the cost type is missing component costs the report will find any missing item costs from your Costing Method cost type. (optional) Include Scrap Quantities: for calculating your completion quantities and component quantity requirements, include or exclude any scrapped assembly quantities. (mandatory) Include Unreleased Jobs: include jobs which have not been released and are not started. (mandatory) Include Bulk Supply Items: include Bulk items to match the results from the Oracle Discrete Job Value Report; exclude knowing that Bulk items are usually not issued to the WIP job. (mandatory) Use Completion Qtys: for jobs in a released status, use the completion quantities for the material usage and configuration variance calculations. Useful if you backflush your materials based on your completion quantities. Complete, Complete - No Charges, Cancelled, Closed, Pending Close or Failed Close alway use the completion quantities for the variance calculations. (mandatory) Category Set 1: any item category you wish (optional). Category Set 2: any item category you wish (optional). Class Code: specific type of WIP class to report (optional). Job Status: specific WIP job status (optional). WIP Job: specific WIP job (optional). Assembly Number: specific assembly number you wish to report (optional) Component Number: specific component item you wish to report (optional) Organization Code: any inventory organization, defaults to your session's inventory organization (optional). Operating Unit: specific operating unit (optional) Ledger: specific ledger (optional) /* +=============================================================================+ -- | Copyright 2009 - 2024 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is acknowledged. -- | 1.26 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+ -- | Version Modified on Modified by Description -- | ======= =========== =============== ========================================= -- | 1.0 12 Oct 2020 Douglas Volz Initial Coding Based on ICP WIP Component -- | Variances and ICP WIP Component Valuation -- | 1.24 02 Feb 2022 Douglas Volz Fix for non-standard jobs, there are no rows in wip_operations if there is no routing. -- | 1.25 20 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and inventory org access controls. -- | ======= =========== =============== ========================================= Enginatics R12 only &category_columns &segment_columns 1=1 2=2 3=3 4=4 5=5 6=6 :p_cost_type :p_include_scrap :p_report_option :p_use_completion_qtys 1 -20 &segment_columns select 'gcc.'||lower(fifsv.application_column_name)||' "'||fifsv.form_left_prompt||'",' 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 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 10 :p_report_option LOV CST WIP Report Option D5CA808540E0FA18E05369FB090580A4 select 'Open jobs' value, 'Report only open jobs' description from dual union all select 'All jobs' value, 'Report all jobs' description from dual union all select 'Closed jobs' value, 'Report only closed jobs' description from dual order by 1 All jobs Y US Report Option 3 20 4=4 oap.period_name=:p_period_name LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name select distinct oap.period_name value from org_acct_periods oap, (select max(oap.schedule_close_date) default_date from org_acct_periods oap, org_organization_definitions ood where ood.organization_id = oap.organization_id and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date and rownum = 1 Y US Period Name 4 30 :p_cost_type LOV CST Cost Type (No AvgRates No PII) A486BD05719A2A82E053BB6B6358FC8E select cct.cost_type value, cct.description from cst_cost_types cct where nvl(cct.disable_date,sysdate)>=sysdate and cct.organization_id is null and (cct.cost_type not like '%PII%' or cct.cost_type like '%ICP%') 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 select cct.cost_type from cst_cost_types cct, mtl_parameters mp where cct.cost_type_id = mp.primary_cost_method and mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Cost Type 5 40 :p_include_scrap 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 US Include Scrap Quantities 6 50 4=4 wdj.status_type<>1 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 N N Y US Include Unreleased Jobs 7 60 6=6 nvl(wro.wip_supply_type,1)<>4 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 N N Y US Include Bulk Supply Items 8 70 :p_use_completion_qtys 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 N US Use Completion Quantities 9 80 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x', p_item_id_column=>'primary_item_id') 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 10 90 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x', p_item_id_column=>'primary_item_id') 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 11 100 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x', p_item_id_column=>'primary_item_id') 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 12 110 2=2 mp.organization_code=:p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 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 (:$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 ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 13 120 4=4 wdj.class_code=:p_class_code LOV WIP Class Code (org dependent) E7CCB27F39561272E0530100007F62B4 select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US Class Code 14 130 4=4 wdj.status_type=xxen_util.lookup_code(:p_job_status,'WIP_JOB_STATUS',700) LOV WIP Job Status 8E2FF36EDE9579D2E0530100007F1FF2 select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='WIP_JOB_STATUS' order by ml.meaning US Job Status 15 140 4=4 wdj.wip_entity_id in (select we.wip_entity_id from wip_entities we where we.wip_entity_name=:p_wip_job) LOV WIP Job 8E2FF36EDEC379D2E0530100007F1FF2 select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US WIP Job 16 150 5=5 msiv_comp.concatenated_segments=:p_component_number LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 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') ) order by msiv.concatenated_segments US Component Number 17 160 3=3 wdj.primary_item_id in (select msiv.inventory_item_id from mtl_system_items_vl msiv where msiv.concatenated_segments=:p_assembly_number) LOV INV Item Number (costing enabled only items) EC5CECA520510FC8E0530100007F5EB3 select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id = mp.organization_id and (xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or (:$flex$.organization_code 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' or mp.process_enabled_flag='Y') order by msiv.concatenated_segments US Assembly Number 18 170 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 19 180 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 519C96938A3132E7B3DB534C93C5126B Pivot by Org by WIP Class Y Pivot by Report Type, Ledger, Operating Unit, Org Code, Period Name and WIP Class. MFG 59 APPLIED_COMPONENT_VALUE 30 ASSEMBLY 31 ASSEMBLY_DESCRIPTION 27 ASSEMBLY_QUANTITY_COMPLETED 28 ASSEMBLY_QUANTITY_SCRAPPED 8 Account 12 CLASS_TYPE 49 COMPONENT_BASIS_TYPE 44 COMPONENT_DESCRIPTION 45 COMPONENT_ITEM_TYPE 47 COMPONENT_MAKE_BUY_CODE 43 COMPONENT_NUMBER 46 COMPONENT_STATUS_CODE 25 COSTING_LOT_SIZE 50 COST_TYPE 16 CREATION_DATE 51 CURRENCY_CODE 38 Class 6 Company 20 DATE_CLOSED 19 DATE_COMPLETED 18 DATE_RELEASED 42 DEPARTMENT 7 Department 37 Family 52 ITEM_COST 33 ITEM_STATUS 23 ITEM_STD_LOT_SIZE 32 ITEM_TYPE 39 Inv.Items Description 15 JOB_STATUS 62 LAST_COST_ROLLUP 55 LAST_TRANSACTION_DATE 21 LAST_UPDATE_DATE 2 LEDGER 40 LOT_NUMBER 24 LOT_SIZE_COST_TYPE 34 MAKE_BUY_CODE 60 MATERIAL_USAGE_VARIANCE 3 OPERATING_UNIT 41 OPERATION_SEQ_NUMBER 4 ORG_CODE 5 PERIOD_NAME 14 PROJECT_NUMBER 10 Product 35 Product Cat 36 Product Description 56 QUANTITY_ISSUED 57 QUANTITY_LEFT_IN_WIP 53 QUANTITY_PER_ASSEMBLY 1 REPORT_TYPE 61 ROLLED_UP 17 SCHEDULED_START_DATE 26 START_QUANTITY 9 Sub-Account 29 TOTAL_ASSEMBLY_QUANTITY 54 TOTAL_REQUIRED_QUANTITY 22 UOM_CODE 11 WIP_CLASS 13 WIP_JOB 58 WIP_STANDARD_COMPONENT_VALUE 48 WIP_SUPPLY_TYPE APPLIED_COMPONENT_VALUE VALUE 2 SUM LEDGER ROW 2 MATERIAL_USAGE_VARIANCE VALUE 3 SUM OPERATING_UNIT ROW 3 ORG_CODE ROW 4 REPORT_TYPE ROW 1 WIP_CLASS ROW 5 WIP_STANDARD_COMPONENT_VALUE VALUE 1 SUM S Site D2E647B3DF53C996731A0C0666D0AA35 Pivot by Org by WIP Job Y Pivot by Report Type, Ledger, Operating Unit, Org Code and Period Name. MFG 59 APPLIED_COMPONENT_VALUE 30 ASSEMBLY 31 ASSEMBLY_DESCRIPTION 27 ASSEMBLY_QUANTITY_COMPLETED 28 ASSEMBLY_QUANTITY_SCRAPPED 8 Account 12 CLASS_TYPE 49 COMPONENT_BASIS_TYPE 44 COMPONENT_DESCRIPTION 45 COMPONENT_ITEM_TYPE 47 COMPONENT_MAKE_BUY_CODE 43 COMPONENT_NUMBER 46 COMPONENT_STATUS_CODE 25 COSTING_LOT_SIZE 50 COST_TYPE 16 CREATION_DATE 51 CURRENCY_CODE 38 Class 6 Company 20 DATE_CLOSED 19 DATE_COMPLETED 18 DATE_RELEASED 42 DEPARTMENT 7 Department 37 Family 52 ITEM_COST 33 ITEM_STATUS 23 ITEM_STD_LOT_SIZE 32 ITEM_TYPE 39 Inv.Items Description 15 JOB_STATUS 62 LAST_COST_ROLLUP 55 LAST_TRANSACTION_DATE 21 LAST_UPDATE_DATE 2 LEDGER 40 LOT_NUMBER 24 LOT_SIZE_COST_TYPE 34 MAKE_BUY_CODE 60 MATERIAL_USAGE_VARIANCE 3 OPERATING_UNIT 41 OPERATION_SEQ_NUMBER 4 ORG_CODE 5 PERIOD_NAME 14 PROJECT_NUMBER 10 Product 35 Product Cat 36 Product Description 56 QUANTITY_ISSUED 57 QUANTITY_LEFT_IN_WIP 53 QUANTITY_PER_ASSEMBLY 1 REPORT_TYPE 61 ROLLED_UP 17 SCHEDULED_START_DATE 26 START_QUANTITY 9 Sub-Account 29 TOTAL_ASSEMBLY_QUANTITY 54 TOTAL_REQUIRED_QUANTITY 22 UOM_CODE 11 WIP_CLASS 13 WIP_JOB 58 WIP_STANDARD_COMPONENT_VALUE 48 WIP_SUPPLY_TYPE APPLIED_COMPONENT_VALUE VALUE 2 SUM LEDGER ROW 2 MATERIAL_USAGE_VARIANCE VALUE 3 SUM OPERATING_UNIT ROW 3 ORG_CODE ROW 4 REPORT_TYPE ROW 1 WIP_JOB ROW 5 WIP_STANDARD_COMPONENT_VALUE VALUE 1 SUM S Site ENGINATICS D2E647B3DF53C996731A0C0666D0AA35 519C96938A3132E7B3DB534C93C5126B