ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 1A5739C5D9090375E0630100007F5142Y with b as ( select /*+ materialize*/ mp.organization_id, mp.organization_code, to_number(hoi.org_information2) legal_entity_id, oap.period_name, oap.period_start_date, oap.schedule_close_date, gbh.batch_id, gbh.batch_no, gbh.batch_status, gbh.laboratory_ind, gbh.formula_id, gbh.routing_id, gbh.recipe_validity_rule_id, gbh.creation_date, gbh.plan_start_date, gbh.actual_start_date, gbh.due_date, gbh.actual_cmplt_date, gbh.batch_close_date from mtl_parameters mp, hr_organization_information hoi, org_acct_periods oap, gme_batch_header gbh where 2=2 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.process_enabled_flag='Y' and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and mp.organization_id=oap.organization_id and mp.organization_id=gbh.organization_id and (gbh.batch_close_date is null and nvl(gbh.actual_start_date,sysdate)<oap.schedule_close_date+1 or gbh.batch_close_date>=oap.period_start_date) ) select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, y.organization_code org_code, y.period_name, &segment_columns xxen_util.meaning(y.laboratory_ind,'GME_LABORATORY_IND',553) batch_type, y.batch_no batch_number, xxen_util.meaning(y.batch_status,'GME_BATCH_STATUS',553) batch_status, &p_show_batch_dtls y.creation_date, y.plan_start_date planned_start_date, y.actual_start_date, y.due_date, y.actual_cmplt_date actual_completion_date, y.batch_close_date, case when y.schedule_close_date-y.creation_date<31 then '30 days' when y.schedule_close_date-y.creation_date<61 then '60 days' when y.schedule_close_date-y.creation_date<91 then '90 days' when y.schedule_close_date-y.creation_date<121 then '120 days' when y.schedule_close_date-y.creation_date<151 then '150 days' when y.schedule_close_date-y.creation_date<181 then '180 days' else 'Over 180 days' end aged_creation_date, case when y.release_days<31 then '30 days' when y.release_days<61 then '60 days' when y.release_days<91 then '90 days' when y.release_days<121 then '120 days' when y.release_days<151 then '150 days' when y.release_days<181 then '180 days' else 'Over 180 days' end aged_compln_vs_release_date, case when y.creation_days<31 then '30 days' when y.creation_days<61 then '60 days' when y.creation_days<91 then '90 days' when y.creation_days<121 then '120 days' when y.creation_days<151 then '150 days' when y.creation_days<181 then '180 days' else 'Over 180 days' end aged_compln_vs_creation_date, msiv.primary_uom_code uom_code, (select sum(decode(mmt.transaction_type_id,44,mmt.primary_quantity,17,mmt.primary_quantity,0)) from mtl_material_transactions mmt where mmt.transaction_source_type_id=5 and y.organization_id=mmt.organization_id and y.batch_id=mmt.transaction_source_id and mmt.transaction_date<y.schedule_close_date+1) product_quantity_completed, (select sum(decode(mmt.transaction_type_id,1002,mmt.primary_quantity,1003,mmt.primary_quantity,0)) from mtl_material_transactions mmt where mmt.transaction_source_type_id=5 and y.organization_id=mmt.organization_id and y.batch_id=mmt.transaction_source_id and mmt.transaction_date<y.schedule_close_date+1) byproduct_quantity_completed, msiv.concatenated_segments "Product Number", msiv.description "Product Description", xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, &category_columns xxen_util.meaning(y.accounting_class_code,'XLA_ACCOUNTING_CLASS',602) accounting_class, &p_show_txn_dtls gl.currency_code, y.wip_costs_in, y.wip_costs_out, y.wip_relief, y.wip_value from ( select b.organization_id, b.organization_code, b.period_name, b.schedule_close_date, b.batch_id, b.batch_no, b.batch_status, b.laboratory_ind, b.formula_id, b.routing_id, b.recipe_validity_rule_id, b.creation_date, b.plan_start_date, b.actual_start_date, b.due_date, b.actual_cmplt_date, b.batch_close_date, nvl(b.actual_cmplt_date,b.schedule_close_date)-nvl(b.actual_start_date,nvl(b.actual_cmplt_date,sysdate)) release_days, nvl(b.actual_cmplt_date,b.schedule_close_date)-b.creation_date creation_days, a.inventory_item_id, a.code_combination_id, a.accounting_class_code, &p_show_txn_dtls2 sum(a.wip_costs_in) wip_costs_in, sum(a.wip_costs_out) wip_costs_out, sum(a.wip_relief) wip_relief, sum(a.wip_value) wip_value from b, ( select grt.doc_id batch_id, gxeh.inventory_item_id, xal.code_combination_id, xal.accounting_class_code, gxeh.event_class_code, gxeh.transaction_type_id, gxeh.transaction_id, gxeh.transaction_date, nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0) wip_costs_in, 0 wip_costs_out, 0 wip_relief, nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0) wip_value from b, gme_resource_txns grt, gmf_xla_extract_headers gxeh, gmf_fiscal_policies gfp, xla_ae_headers xah, xla_ae_lines xal where b.batch_id=grt.doc_id and grt.trans_date>=b.actual_start_date and grt.trans_date<b.schedule_close_date+1 and grt.poc_trans_id=gxeh.transaction_id and grt.doc_id=gxeh.source_document_id and grt.line_id=gxeh.source_line_id and grt.resources=gxeh.resources and b.organization_id=gxeh.organization_id and gxeh.txn_source='PM' and gxeh.entity_code='PRODUCTION' and gxeh.event_class_code='BATCH_RESOURCE' and b.legal_entity_id=gfp.legal_entity_id and gxeh.valuation_cost_type_id=gfp.cost_type_id and gxeh.event_id=xah.event_id and xah.application_id=555 and gfp.ledger_id=xah.ledger_id and xah.ae_header_id=xal.ae_header_id and xah.application_id=xal.application_id and xah.ledger_id=xal.ledger_id and xal.accounting_class_code='WIP_VALUATION' union all select b.batch_id, we.primary_item_id inventory_item_id, xal.code_combination_id, xal.accounting_class_code, gxeh.event_class_code, gxeh.transaction_type_id, gxeh.transaction_id, gxeh.transaction_date, 0 wip_costs_in, 0 wip_costs_out, -(nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0)) wip_relief, nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0) wip_value from b, wip_entities we, gmf_xla_extract_headers gxeh, gmf_fiscal_policies gfp, xla_ae_headers xah, xla_ae_lines xal where b.batch_status=4 and b.batch_id=we.wip_entity_id and b.organization_id=we.organization_id and b.batch_id=gxeh.transaction_id and b.batch_id=gxeh.source_document_id and b.organization_id=gxeh.organization_id and gxeh.txn_source='PM' and gxeh.entity_code='PRODUCTION' and gxeh.event_class_code='BATCH_CLOSE' and gxeh.event_type_code='CLOS' and gxeh.transaction_date>=b.period_start_date and gxeh.transaction_date<b.schedule_close_date+1 and b.legal_entity_id=gfp.legal_entity_id and gxeh.valuation_cost_type_id=gfp.cost_type_id and gxeh.event_id=xah.event_id and xah.application_id=555 and gfp.ledger_id=xah.ledger_id and xah.ae_header_id=xal.ae_header_id and xah.application_id=xal.application_id and xah.ledger_id=xal.ledger_id and xal.accounting_class_code='WIP_VALUATION' union all select b.batch_id, we.primary_item_id inventory_item_id, xal.code_combination_id, xal.accounting_class_code, gxeh.event_class_code, gxeh.transaction_type_id, gxeh.transaction_id, gxeh.transaction_date, decode(mmt.transaction_action_id,31,0,32,0,nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0)) wip_costs_in, -decode(mmt.transaction_action_id,31,nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0),32,nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0),0) wip_costs_out, 0 wip_relief, nvl(xal.accounted_dr,0)-nvl(xal.accounted_cr,0) wip_value from b, wip_entities we, mtl_material_transactions mmt, gmf_xla_extract_headers gxeh, gmf_fiscal_policies gfp, xla_ae_headers xah, xla_ae_lines xal where b.batch_id=we.wip_entity_id and b.batch_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and b.organization_id=mmt.organization_id and mmt.transaction_date>=b.actual_start_date and mmt.transaction_date<b.schedule_close_date+1 and mmt.transaction_id=gxeh.transaction_id and b.organization_id=gxeh.organization_id and gxeh.txn_source='PM' and gxeh.entity_code='PRODUCTION' and gxeh.event_class_code='BATCH_MATERIAL' and b.legal_entity_id=gfp.legal_entity_id and gxeh.valuation_cost_type_id=gfp.cost_type_id and gxeh.event_id=xah.event_id and xah.application_id=555 and gfp.ledger_id=xah.ledger_id and xah.ae_header_id=xal.ae_header_id and xah.application_id=xal.application_id and xah.ledger_id=xal.ledger_id and xal.accounting_class_code='WIP_VALUATION' ) a where b.batch_id=a.batch_id group by b.organization_id, b.organization_code, b.period_name, b.schedule_close_date, b.batch_id, b.batch_no, b.batch_status, b.laboratory_ind, b.formula_id, b.routing_id, b.recipe_validity_rule_id, b.creation_date, b.plan_start_date, b.actual_start_date, b.due_date, b.actual_cmplt_date, b.batch_close_date, a.inventory_item_id, a.code_combination_id, &p_show_txn_dtls2_grp a.accounting_class_code ) y, mtl_system_items_vl msiv, gl_code_combinations gcc, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl where 1=1 and y.organization_id=msiv.organization_id and y.inventory_item_id=msiv.inventory_item_id and y.code_combination_id=gcc.code_combination_id(+) and y.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 order by ledger, operating_unit, org_code, y.period_name, batch_type, batch_number, "Product Number", accounting_class Rebuilt to the Blitz Report SQL standards: the batches of the period are selected once for the resource, material and close accounting, a batch closed on the last day of the period after midnight shows its close relief in that period, the product and byproduct completed quantities are as of the period end, the event class name is read for the OPM event classes only. 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.00;[Red](#,##0.00) US CAC OPM WIP Account Value Report to show WIP values for process manufacturing (OPM), in summary by inventory organization and batch, with batch status, name and other details. The valuation accounts come from the cumulative WIP Valuation accounting entries, as processed by Create Accounting. Parameters: =========== Period Name: the inventory accounting period you wish to report (mandatory). Include Lab Batches: enter Yes to include laboratory batches. Enter No to exclude them. Defaults to No (mandatory). Show Batch Details: enter Yes to display the formula, routing and recipe numbers and versions. Defaults to No (mandatory). Show Transaction Details: enter Yes to show the Event Class, Transaction Type, Transaction ID and Transaction Date. Defaults to No (mandatory). Batch Number: enter a specific batch number to report (optional). Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional). Category Set 2: The second item category set to report, typically the Inventory Category Set (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 2014 - 2024 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) -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 22-Oct-2014 Douglas Volz Initial version. -- | 1.1 06-Jul-2024 Douglas Volz Cumulative changes plus format for Blitz Report. -- | 1.2 31-Jul-2024 Douglas Volz Fix to get current and prior month's transactions -- | 1.3 06-Aug-2024 Douglas Volz Add Batch and Txn Detail parameters. -- | 1.4 08-Aug-2024 Douglas Volz Add Product and Byproduct completion quantity columns. -- | and inventory access control security. -- | 1.5 17-Aug-2024 Douglas Volz Fix for reporting batches closed after the reported period. -- | Add Batch Number parameter. Display the Ledger short name. -- | 1.6 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics R12 only &category_columns &p_show_batch_dtls &p_show_txn_dtls &p_show_txn_dtls2 &p_show_txn_dtls2_grp &segment_columns 1=1 2=2 1 -10 &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')) US Chart of Accounts 2 10 2=2 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 3 20 2=2 nvl(gbh.laboratory_ind,0)=0 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 Lab Batches 4 30 &p_show_batch_dtls (select ffmv.formula_no from fm_form_mst_vl ffmv where y.formula_id=ffmv.formula_id) formula_number, (select ffmv.formula_vers from fm_form_mst_vl ffmv where y.formula_id=ffmv.formula_id) formula_version, (select frh.routing_no from fm_rout_hdr frh where y.routing_id=frh.routing_id) routing_number, (select frh.routing_vers from fm_rout_hdr frh where y.routing_id=frh.routing_id) routing_version, (select gr.recipe_no from gmd_recipe_validity_rules grvr, gmd_recipes gr where y.recipe_validity_rule_id=grvr.recipe_validity_rule_id and grvr.recipe_id=gr.recipe_id) recipe_number, (select gr.recipe_version from gmd_recipe_validity_rules grvr, gmd_recipes gr where y.recipe_validity_rule_id=grvr.recipe_validity_rule_id and grvr.recipe_id=gr.recipe_id) recipe_version, 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 Batch Details 5 40 &p_show_txn_dtls (select xecv.name from xla_event_classes_vl xecv where xecv.application_id=555 and y.event_class_code=xecv.event_class_code) event_class, (select mtt.transaction_type_name from mtl_transaction_types mtt where y.transaction_type_id=mtt.transaction_type_id) material_transaction_type, y.transaction_id, y.transaction_date, 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 Transaction Details 6 &p_show_txn_dtls2 a.event_class_code, a.transaction_type_id, a.transaction_id, a.transaction_date, Y US Show Transaction Details 7 &p_show_txn_dtls2_grp a.event_class_code, a.transaction_type_id, a.transaction_id, a.transaction_date, Y US Show Transaction Details 8 50 2=2 gbh.batch_no=:p_batch_no LOV GME Batch Number 1A573DC3BA400377E0630100007FE5A2 select gbh.batch_no value, gl.description description from gme_batch_header gbh, mtl_parameters mp, org_organization_definitions ood, org_acct_periods oap, gem_lookups gl where gbh.organization_id=mp.organization_id and ood.organization_id=mp.organization_id and 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 and gl.lookup_type (+) = 'GME_BATCH_STATUS' and gl.lookup_code (+) = gbh.batch_status and oap.organization_id = mp.organization_id and (:$flex$.period_name is not null and oap.period_name = :$flex$.period_name) and ((gbh.batch_close_date is null and nvl(gbh.actual_start_date, sysdate) < oap.schedule_close_date + 1) or (gbh.batch_close_date is not null and gbh.batch_close_date >= oap.period_start_date) ) US Batch Number 9 60 &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 10 70 &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 11 80 &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 12 90 2=2 mp.organization_code=:p_org_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and 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') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) 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 and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) 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 100 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 14 110 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 CE6FF05881FCA89000D9C94C7CD0FD61 Pivot by Org Y Pivot by Ledger, Operating Unit, Org Code and Period Name MFG 37 ACCOUNTING_CLASS 7 ACCT 23 ACTUAL_COMPLETION_DATE 21 ACTUAL_START_DATE 27 AGED_COMPLN_VS_CREATION_DATE 26 AGED_COMPLN_VS_RELEASE_DATE 25 AGED_CREATION_DATE 24 BATCH_CLOSE_DATE 11 BATCH_NUMBER 12 BATCH_STATUS 10 BATCH_TYPE 6 CC 5 CO 19 CREATION_DATE 38 CURRENCY_CODE 35 Class 22 DUE_DATE 13 FORMULA_NUMBER 14 FORMULA_VERSION 9 FUTR 34 Family 31 ITEM_TYPE 36 Inv.Items Description 1 LEDGER 2 OPERATING_UNIT 3 ORG_CODE 4 PERIOD_NAME 20 PLANNED_START_DATE 8 PROD CLS 29 PRODUCT_DESCRIPTION 28 PRODUCT_NUMBER 32 Product Cat 33 Product Description 17 RECIPE_NUMBER 18 RECIPE_VERSION 15 ROUTING_NUMBER 16 ROUTING_VERSION 30 UOM_CODE 39 WIP_COSTS_IN 40 WIP_COSTS_OUT 41 WIP_RELIEF 42 WIP_VALUE LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 PERIOD_NAME ROW 4 WIP_COSTS_IN VALUE 1 SUM WIP_COSTS_OUT VALUE 2 SUM WIP_RELIEF VALUE 3 SUM WIP_VALUE VALUE 4 SUM S Site CFF37AC18EA15DF0945F82CFA0C0B587 Pivot by Org by Batch Y Pivot by Ledger, Operating Unit, Org Code, Period Name and Batch MFG 32 ACCOUNTING_CLASS 7 ACCT 17 ACTUAL_COMPLETION_DATE 15 ACTUAL_START_DATE 21 AGED_COMPLN_VS_CREATION_DATE 20 AGED_COMPLN_VS_RELEASE_DATE 19 AGED_CREATION_DATE 18 BATCH_CLOSE_DATE 11 BATCH_NUMBER 12 BATCH_STATUS 10 BATCH_TYPE 24 BYPRODUCT_QUANTITY_COMPLETED 6 CC 5 CO 13 CREATION_DATE 33 CURRENCY_CODE 30 Class 16 DUE_DATE -12.0013 FORMULA_NUMBER -12.0014 FORMULA_VERSION 9 FUTR 29 Family 26 ITEM_TYPE 31 Inv.Items Description 1 LEDGER 2 OPERATING_UNIT 3 ORG_CODE 4 PERIOD_NAME 14 PLANNED_START_DATE 8 PROD CLS -27.0029 PRODUCT_DESCRIPTION -27.0028 PRODUCT_NUMBER 23 PRODUCT_QUANTITY_COMPLETED 27 Product Cat 28 Product Description 25 Product Number -12.0017 RECIPE_NUMBER -12.0018 RECIPE_VERSION -12.0015 ROUTING_NUMBER -12.0016 ROUTING_VERSION 22 UOM_CODE 34 WIP_COSTS_IN 35 WIP_COSTS_OUT 36 WIP_RELIEF 37 WIP_VALUE BATCH_NUMBER ROW 6 BATCH_TYPE ROW 5 LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE ROW 3 PERIOD_NAME ROW 4 WIP_COSTS_IN VALUE 1 SUM WIP_COSTS_OUT VALUE 2 SUM WIP_RELIEF VALUE 3 SUM WIP_VALUE VALUE 4 SUM S Site CFF37AC18EA15DF0945F82CFA0C0B587