ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 2E888FE5926328ACE0630100007FCF1DY with ic as ( select /*+ materialize*/ x.organization_id, x.inventory_item_id, x.cost_type, x.cost_type_desc, round(sum(x.item_cost),9) item_cost from ( select mp.organization_id, gic.inventory_item_id, cmm.cost_mthd_code cost_type, cmm.cost_mthd_desc cost_type_desc, gic.acctg_cost item_cost from mtl_parameters mp, hr_organization_information hoi, cm_whse_asc cwa, gmf_fiscal_policies gfp, gmf_period_statuses gps, cm_mthd_mst cmm, gl_item_cst gic where :p_cost_type is null and 2=2 and 4=4 and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and mp.organization_id=cwa.organization_id(+) and to_number(hoi.org_information2)=gfp.legal_entity_id and gfp.legal_entity_id=gps.legal_entity_id and gfp.cost_type_id=gps.cost_type_id and gps.cost_type_id=cmm.cost_type_id and gps.period_id=gic.period_id and gps.cost_type_id=gic.cost_type_id and nvl(cwa.cost_organization_id,mp.organization_id)=gic.organization_id and gic.delete_mark=0 union all select mp.organization_id, ccd.inventory_item_id, cmm.cost_mthd_code cost_type, cmm.cost_mthd_desc cost_type_desc, ccd.cmpnt_cost item_cost from mtl_parameters mp, hr_organization_information hoi, cm_whse_asc cwa, gmf_period_statuses gps, cm_mthd_mst cmm, cm_cmpt_dtl ccd, cm_cmpt_mst_b ccmb where :p_cost_type is not null and 2=2 and 3=3 and 4=4 and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and mp.organization_id=cwa.organization_id(+) and to_number(hoi.org_information2)=gps.legal_entity_id and gps.cost_type_id=cmm.cost_type_id and gps.period_id=ccd.period_id and gps.cost_type_id=ccd.cost_type_id and nvl(cwa.cost_organization_id,mp.organization_id)=ccd.organization_id and ccd.delete_mark=0 and ccd.cost_cmpntcls_id=ccmb.cost_cmpntcls_id and ccmb.product_cost_ind=1 ) x group by x.organization_id, x.inventory_item_id, x.cost_type, x.cost_type_desc ) select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, :p_calendar_code calendar_code, :p_period_code period_code, gbh.batch_no batch_number, gbh.plan_start_date batch_start_date, gbh.plan_cmplt_date planned_completion_date, gbh.actual_cmplt_date actual_completion_date, gbh.batch_close_date closed_date, xxen_util.meaning(gbh.batch_status,'GME_BATCH_STATUS',553) batch_status, msiv.concatenated_segments item_number, msiv.description item_description, xxen_util.meaning(gmd.line_type,'LINE_TYPE',555) row_type, 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_segments1 &category_segments2 &category_segments3 gmd.original_qty*mucv.conversion_rate primary_original_quantity, gmd.plan_qty*mucv.conversion_rate primary_planned_quantity, msiv.primary_uom_code, gmd.original_qty original_transacted_quantity, gmd.plan_qty planned_transacted_quantity, gmd.dtl_um planned_transacted_uom, gmd.actual_qty actual_transacted_quantity, gmd.actual_qty*mucv.conversion_rate primary_actual_quantity, ic.cost_type, ic.cost_type_desc cost_type_description, ic.item_cost from mtl_parameters mp, org_acct_periods oap, hr_organization_information hoi, hr_all_organization_units_vl haouv, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, gme_batch_header gbh, gme_material_details gmd, mtl_system_items_vl msiv, mtl_uom_conversions_view mucv, ic where 1=1 and 2=2 and 5=5 and 6=6 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<>mp.master_organization_id and mp.organization_id=oap.organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haouv.organization_id and sysdate<nvl(haouv.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=gbh.organization_id and gbh.delete_mark=0 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) and gbh.batch_id=gmd.batch_id and gmd.organization_id=msiv.organization_id and gmd.inventory_item_id=msiv.inventory_item_id and gmd.organization_id=mucv.organization_id and gmd.inventory_item_id=mucv.inventory_item_id and gmd.dtl_um=mucv.uom_code and gmd.organization_id=ic.organization_id(+) and gmd.inventory_item_id=ic.inventory_item_id(+) order by ledger, operating_unit, org_code, batch_number, row_type, item_number Rebuilt to the Blitz Report SQL standards: every batch line was listed twice when two legal entities use the OPM calendar (the period table was not joined to the batch), batch lines whose item has no cost in the period are listed with a blank cost instead of being left out, the duplicate Transacted UOM Code, Primary UOM Code and Primary UOM columns are removed. 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 OPM Batch Material Summary Report showing Batch materials in summary for each product, byproduct and ingredient. Displaying batches which were open during the monthly inventory accounting period or batches which were closed during the monthly inventory accounting period. If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end item costs. Parameters: ========== Period Name: enter the monthly inventory accounting period you wish to report (mandatory). Cost Type: enter a Cost Type to value the quantities using the Cost Type item costs; if Cost Type is not entered the report will use the Cost Type from your Fiscal Policies (optional). OPM Calendar Code: choose the OPM Calendar Code which corresponds to the period costs you wish to report (mandatory). OPM Period Code: enter the OPM Period Code which corresponds to the period costs and OPM Calendar Code you wish to report (mandatory). Category Sets 1 - 3: enter up to three item category sets you wish to report (optional). Item Number: specific Product, By-Product or Ingredient you wish to report (optional). Batch Number: enter any batch number which is open or was closed within the date range of the OPM Period Code and Calendar Code (optional). Batch Status: to minimize the report size, specify the batch statuses you wish to report (optional). Batch Number From: (optional). Batch Number To: (optional). Organization Code: enter the inventory organization(s), defaults to your session's inventory organization (optional). Operating Unit: enter the operating unit(s) you wish to report (optional). Ledger: enter the ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2025 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 22 Jan 2025 Douglas Volz Initial Coding based on client's sample report. -- | 1.1 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================*/ Enginatics R12 only &category_segments1 &category_segments2 &category_segments3 1=1 2=2 3=3 4=4 5=5 6=6 :p_calendar_code :p_cost_type :p_period_code 1 10 1=1 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 2 20 3=3 cmm.cost_mthd_code=:p_cost_type LOV Cost Type (OPM Orgs Only) 184A903C40A16B3FE0630100007F2880 select x.value, x.description from ( select cmm.cost_mthd_code value, cmm.cost_mthd_desc description from cm_mthd_mst cmm where exists (select 'x' from gmf_calendar_assignments gca, org_organization_definitions ood, mtl_parameters mp where gca.legal_entity_id = ood.legal_entity and gca.cost_type_id = cmm.cost_type_id and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or mp.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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id and mp.process_enabled_flag = 'Y' ) ) x group by x.value, x.description order by value US Cost Type 3 30 4=4 gps.calendar_code=:p_calendar_code LOV OPM Calendar Code 19508EAA42357240E0630100007FD049 select ccht.calendar_code value, ccht.calendar_desc description from cm_cldr_hdr_tl ccht where ccht.language = userenv('lang') and exists (select 'x' from gmf_calendar_assignments gca, gmf_period_statuses gps, org_organization_definitions ood, mtl_parameters mp where gca.calendar_code = gps.calendar_code and gca.legal_entity_id = gps.legal_entity_id and gca.legal_entity_id = ood.legal_entity and gca.calendar_code = ccht.calendar_code and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or mp.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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id ) order by value desc select ccht.calendar_code value, ccht.calendar_desc description from cm_cldr_hdr_tl ccht where ccht.language = userenv('lang') and ccht.calendar_code = (select gps.calendar_code from gmf_calendar_assignments gca, gmf_period_statuses gps, ic_cldr_dtl icd, org_acct_periods oap, org_organization_definitions ood, mtl_parameters mp where gca.calendar_code = gps.calendar_code and gca.legal_entity_id = gps.legal_entity_id and gca.legal_entity_id = ood.legal_entity and gca.calendar_code = ccht.calendar_code -- added to tie the OPM period_id with the inventory acct_period_id and icd.fiscal_year = oap.period_year and icd.period_end_date = oap.schedule_close_date and icd.period = oap.period_num and icd.period_id = gps.period_id and oap.organization_id = mp.organization_id and oap.period_name = :$flex$.period_name and (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or mp.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 mp.organization_id = ood.organization_id and nvl(ood.disable_date,sysdate) >= sysdate and mp.organization_id <> mp.master_organization_id group by gps.calendar_code ) order by value desc Y US OPM Calendar Code 4 40 4=4 gps.period_code=:p_period_code LOV OPM Period Code 19508EAA42367240E0630100007FD049 select ccd.period_code value, ccd.period_desc description from cm_cldr_dtl ccd where exists (select 1 from gmf_period_statuses gps where gps.calendar_code = :$flex$.opm_calendar_code and gps.legal_entity_id in (select ood.legal_entity from org_organization_definitions ood where (ood.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID') or ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV'))) and gps.period_code = ccd.period_code and gps.calendar_code = ccd.calendar_code ) order by ccd.end_date desc Y US OPM Period Code 5 50 5=5 gbh.batch_status=(select xxen_util.lookup_code(:p_batch_status,'GME_BATCH_STATUS',553) from dual) LOV OPM Batch Status 53860F993F6B7167E0630100007F1025 select to_char(flv.lookup_code) id, flv.meaning value, null description from fnd_lookup_values_vl flv where flv.lookup_type='GME_BATCH_STATUS' order by flv.lookup_code value US Batch Status 6 60 1=1 gbh.batch_no=:p_batch_number 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 7 70 1=1 gbh.batch_no>=:p_batch_number_from 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 From 8 80 1=1 gbh.batch_no<=:p_batch_number_to LOV GME Batch Number To 2EC46130C328100BE06369FB0905137D 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 (gbh.batch_no >= :$flex$.batch_number_from or :$flex$.batch_number_from is null) 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 To 9 90 &category_segments1 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 100 &category_segments2 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 110 &category_segments3 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 120 6=6 msiv.concatenated_segments=:p_item_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 Item Number 13 130 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 14 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 15 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 23EEDC2D0B830DE1CA8999265B669A7A Count by OPM Org MFG 9 ACTUAL_COMPLETION_DATE 27 ACTUAL_TRANSACTED_QUANTITY 6 BATCH_NUMBER 7 BATCH_START_DATE 11 BATCH_STATUS 4 CALENDAR_CODE 10 CLOSED_DATE 31 COST_TYPE 32 COST_TYPE_DESCRIPTION 33 ITEM_COST 13 ITEM_DESCRIPTION 12 ITEM_NUMBER 16 ITEM_STATUS 15 ITEM_TYPE 20 Inv.Items Description 1 LEDGER 17 MAKE_BUY_CODE -19.002 Major Category -19.0021 Minor Category 2 OPERATING_UNIT 3 ORG_CODE 24 ORIGINAL_TRANSACTED_QUANTITY 5 PERIOD_CODE 8 PLANNED_COMPLETION_DATE 25 PLANNED_TRANSACTED_QUANTITY 26 PLANNED_TRANSACTED_UOM 29 PRIMARY_ACTUAL_QUANTITY 21 PRIMARY_ORIGINAL_QUANTITY 22 PRIMARY_PLANNED_QUANTITY 23 PRIMARY_UOM_CODE 18 Product Cat 19 Product Description 14 ROW_TYPE CALENDAR_CODE ROW 4 LEDGER ROW 1 OPERATING_UNIT ROW 2 ORG_CODE VALUE 1 COUNT ORG_CODE ROW 3 PERIOD_CODE ROW 5 S Site 23EEDC2D0B830DE1CA8999265B669A7A