ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B9E8FC1CED8511DCE053BB6B6358E005Y select x.ledger, x.operating_unit, x.org_code, x.wip_class, x.class_type, x.wip_job, x.job_status, x.date_released, x.date_completed, x.last_update_date, x.completion_subinventory, x.assembly_number, x.assembly_description, x.item_type, x.item_status, x.make_buy_code, &category_columns x.uom_code, x.start_quantity, x.quantity_completed, x.quantity_scrapped, x.total_quantity, case when x.done_quantity=0 then xxen_util.meaning('N','YES_NO_ALL',0) when x.open_quantity>0 then xxen_util.meaning(2,'SYS_RANGE',700) else xxen_util.meaning('A','YES_NO_ALL',0) end quantities_completed, xxen_util.yes(x.open_material_quantities) open_material_quantities, xxen_util.yes(x.no_earned_osp) no_earned_osp, x.currency_code, x.wip_value, x.wip_costs_in, round(x.wip_value_unrounded/decode(x.wip_costs_in_unrounded,0,1,x.wip_costs_in_unrounded),3)*100 wip_variance_percent, xxen_util.yes(x.unprocessed_material) unprocessed_material, xxen_util.yes(x.uncosted_material) uncosted_material, xxen_util.yes(x.pending_wip_costing) pending_wip_costing, xxen_util.yes(x.pending_receiving) pending_receiving, xxen_util.yes(x.pending_requisitions) pending_requisitions, xxen_util.yes(x.pending_material) pending_material, xxen_util.yes(x.pending_shop_floor_move) pending_shop_floor_move, xxen_util.yes(x.uncosted_split_merge_txn) uncosted_split_merge_txn, xxen_util.yes(x.unprocessed_wsm_txn_interface) unprocessed_wsm_txn_interface, xxen_util.yes(x.pending_resulting_lots) pending_resulting_lots, xxen_util.yes(x.pending_wsm_job_lots) pending_wsm_job_lots from ( select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, wac.class_code wip_class, xxen_util.meaning(wac.class_type,'WIP_CLASS_TYPE',700) class_type, we.wip_entity_name wip_job, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, wdj.date_released, wdj.date_completed, wdj.last_update_date, wdj.completion_subinventory, msiv.concatenated_segments assembly_number, 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, msiv.primary_uom_code uom_code, nvl(wdj.start_quantity,0) start_quantity, nvl(wdj.quantity_completed,0) quantity_completed, nvl(wdj.quantity_scrapped,0) quantity_scrapped, nvl(wdj.quantity_completed,0)+nvl(wdj.quantity_scrapped,0) total_quantity, nvl(wdj.start_quantity,0)-nvl(wdj.quantity_completed,0)-decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped,0)) open_quantity, nvl(wdj.quantity_completed,0)+decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped,0)) done_quantity, case when wdj.wip_entity_id in ( select wro.wip_entity_id from wip_requirement_operations wro, mtl_system_items_b msib where 2=2 and wdj.wip_entity_id=wro.wip_entity_id and wdj.organization_id=wro.organization_id and wro.wip_supply_type<>6 and wro.organization_id=msib.organization_id and wro.inventory_item_id=msib.inventory_item_id and msib.inventory_asset_flag='Y' and round(decode(wro.basis_type,2,nvl(wro.required_quantity,1),decode(wro.basis_type,1,nvl(wro.quantity_per_assembly,wdj.start_quantity),nvl(wro.quantity_per_assembly,0))/nvl(wro.component_yield_factor,1)*(nvl(wdj.quantity_completed,0)+decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped,0)))),3)-round(nvl(wro.quantity_issued,0),3)>0 ) then 'Y' end open_material_quantities, case when wdj.wip_entity_id in (select wor.wip_entity_id from wip_operation_resources wor where wdj.wip_entity_id=wor.wip_entity_id and wdj.organization_id=wor.organization_id and wor.autocharge_type in (3,4) and nvl(wor.applied_resource_units,0)=0) then 'Y' end no_earned_osp, gl.currency_code, wpb.wip_value, wpb.wip_value_unrounded, wpb.wip_costs_in, wpb.wip_costs_in_unrounded, case when wdj.wip_entity_id in (select mmtt.transaction_source_id from mtl_material_transactions_temp mmtt where wdj.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=5) then 'Y' end unprocessed_material, case when wdj.wip_entity_id in (select mmt.transaction_source_id from mtl_material_transactions mmt where wdj.organization_id=mmt.organization_id and mmt.transaction_source_type_id=5 and mmt.costed_flag is not null) then 'Y' end uncosted_material, case when wdj.wip_entity_id in (select wcti.wip_entity_id from wip_cost_txn_interface wcti where wdj.organization_id=wcti.organization_id) then 'Y' end pending_wip_costing, case when wdj.wip_entity_id in (select rti.wip_entity_id from rcv_transactions_interface rti where wdj.organization_id=rti.to_organization_id) then 'Y' end pending_receiving, case when wdj.wip_entity_id in (select pri.wip_entity_id from po_requisitions_interface pri, wip_operation_resources wor where wdj.organization_id=pri.destination_organization_id and pri.wip_entity_id=wor.wip_entity_id and pri.destination_organization_id=wor.organization_id and wor.autocharge_type in (3,4) and nvl(wor.applied_resource_units,0)=0) then 'Y' end pending_requisitions, case when wdj.wip_entity_id in (select mti.transaction_source_id from mtl_transactions_interface mti where wdj.organization_id=mti.organization_id and mti.transaction_source_type_id=5 and mti.process_flag<>9) then 'Y' end pending_material, case when wdj.wip_entity_id in (select wmti.wip_entity_id from wip_move_txn_interface wmti where wdj.organization_id=wmti.organization_id) then 'Y' end pending_shop_floor_move, case when wdj.wip_entity_id in (select wssj.wip_entity_id from wsm_sm_starting_jobs wssj, wsm_split_merge_transactions wsmt where wdj.organization_id=wssj.organization_id and wssj.transaction_id=wsmt.transaction_id and wsmt.costed<>4 union all select wsrj.wip_entity_id from wsm_sm_resulting_jobs wsrj, wsm_split_merge_transactions wsmt where wdj.organization_id=wsrj.organization_id and wsrj.transaction_id=wsmt.transaction_id and wsmt.costed<>4) then 'Y' end uncosted_split_merge_txn, case when wdj.wip_entity_id in (select wsji.wip_entity_id from wsm_starting_jobs_interface wsji, wsm_split_merge_txn_interface wsmti where wdj.organization_id=wsji.organization_id and wsji.header_id=wsmti.header_id and wsmti.process_status<>4) or (we.wip_entity_name,we.organization_id) in (select wrji.wip_entity_name, wrji.organization_id from wsm_resulting_jobs_interface wrji, wsm_split_merge_txn_interface wsmti where wrji.header_id=wsmti.header_id and wsmti.process_status<>4) then 'Y' end unprocessed_wsm_txn_interface, case when wdj.wip_entity_id in (select wrli.wip_entity_id from wsm_resulting_lots_interface wrli, wsm_lot_split_merges_interface wlsmi where wdj.organization_id=wrli.organization_id and wrli.header_id=wlsmi.header_id and wlsmi.wip_flag=1 and wlsmi.process_status<>4) then 'Y' end pending_resulting_lots, case when wdj.wip_entity_id in (select wlji.wip_entity_id from wsm_lot_job_interface wlji where wdj.organization_id=wlji.organization_id and wlji.process_status<>4) then 'Y' end pending_wsm_job_lots, msiv.inventory_item_id, msiv.organization_id from wip_discrete_jobs wdj, wip_entities we, wip_accounting_classes wac, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, mtl_system_items_vl msiv, ( select wpb.wip_entity_id, round(sum(nvl(wpb.pl_material_in,0)-nvl(wpb.tl_material_out,0)-nvl(wpb.pl_material_out,0)-nvl(wpb.tl_material_var,0)-nvl(wpb.pl_material_var,0)+ nvl(wpb.pl_material_overhead_in,0)-nvl(wpb.tl_material_overhead_out,0)-nvl(wpb.pl_material_overhead_out,0)-nvl(wpb.tl_material_overhead_var,0)-nvl(wpb.pl_material_overhead_var,0)+ nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)-nvl(wpb.tl_resource_out,0)-nvl(wpb.pl_resource_out,0)-nvl(wpb.tl_resource_var,0)-nvl(wpb.pl_resource_var,0)+ nvl(wpb.tl_outside_processing_in,0)+nvl(wpb.pl_outside_processing_in,0)-nvl(wpb.tl_outside_processing_out,0)-nvl(wpb.pl_outside_processing_out,0)-nvl(wpb.tl_outside_processing_var,0)-nvl(wpb.pl_outside_processing_var,0)+ nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)-nvl(wpb.tl_overhead_out,0)-nvl(wpb.pl_overhead_out,0)-nvl(wpb.tl_overhead_var,0)-nvl(wpb.pl_overhead_var,0)+ nvl(wpb.tl_scrap_in,0)-nvl(wpb.tl_scrap_out,0)-nvl(wpb.tl_scrap_var,0)),2) wip_value, sum(nvl(wpb.pl_material_in,0)-nvl(wpb.tl_material_out,0)-nvl(wpb.pl_material_out,0)-nvl(wpb.tl_material_var,0)-nvl(wpb.pl_material_var,0)+ nvl(wpb.pl_material_overhead_in,0)-nvl(wpb.tl_material_overhead_out,0)-nvl(wpb.pl_material_overhead_out,0)-nvl(wpb.tl_material_overhead_var,0)-nvl(wpb.pl_material_overhead_var,0)+ nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)-nvl(wpb.tl_resource_out,0)-nvl(wpb.pl_resource_out,0)-nvl(wpb.tl_resource_var,0)-nvl(wpb.pl_resource_var,0)+ nvl(wpb.tl_outside_processing_in,0)+nvl(wpb.pl_outside_processing_in,0)-nvl(wpb.tl_outside_processing_out,0)-nvl(wpb.pl_outside_processing_out,0)-nvl(wpb.tl_outside_processing_var,0)-nvl(wpb.pl_outside_processing_var,0)+ nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)-nvl(wpb.tl_overhead_out,0)-nvl(wpb.pl_overhead_out,0)-nvl(wpb.tl_overhead_var,0)-nvl(wpb.pl_overhead_var,0)+ nvl(wpb.tl_scrap_in,0)-nvl(wpb.tl_scrap_out,0)-nvl(wpb.tl_scrap_var,0)) wip_value_unrounded, round(sum(nvl(wpb.pl_material_in,0)+nvl(wpb.pl_material_overhead_in,0)+nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)+nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)+nvl(wpb.tl_scrap_in,0))) wip_costs_in, sum(nvl(wpb.pl_material_in,0)+nvl(wpb.pl_material_overhead_in,0)+nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)+nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)+nvl(wpb.tl_scrap_in,0)) wip_costs_in_unrounded from wip_period_balances wpb group by wpb.wip_entity_id ) wpb where 1=1 and wdj.status_type=4 and wdj.date_completed is not null and wdj.date_closed is null and wdj.wip_entity_id=we.wip_entity_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.organization_id=mp.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 to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and wdj.organization_id=msiv.organization_id and wdj.primary_item_id=msiv.inventory_item_id and wdj.wip_entity_id=wpb.wip_entity_id ) x where coalesce(x.open_material_quantities,x.no_earned_osp,x.unprocessed_material,x.uncosted_material,x.pending_wip_costing,x.pending_receiving,x.pending_requisitions,x.pending_material,x.pending_shop_floor_move,x.uncosted_split_merge_txn,x.unprocessed_wsm_txn_interface,x.pending_resulting_lots,x.pending_wsm_job_lots,case when x.open_quantity>0 then 'Y' end) is null and abs(x.wip_value_unrounded)<:p_wip_var_threshold and abs(round(x.wip_value_unrounded/decode(x.wip_costs_in_unrounded,0,1,x.wip_costs_in_unrounded),3)*100)<:p_wip_var_percent order by x.ledger, x.operating_unit, x.org_code, x.wip_job Rebuilt to the Blitz Report SQL standards: the Ready and Not Ready for Close reports use one set of checks, so every completed job is in exactly one of them (jobs with unprocessed or uncosted material, or with one under-issued component while others were over-issued, were in neither), flags show Yes or blank, the job is read once instead of grouping all of its period balance rows with the job attributes. Organization security as before. #,##0.00000;[Red](#,##0.00000) US CAC WIP Jobs With Complete Status Which Are Ready for Close Report WIP jobs which have a status of "Complete", do not exceed variance tolerances, have completed or exceeded the WIP start quantity, with no open material requirements, no unearned OSP (outside processing) charges and no stuck transactions in interfaces. When you include scrap quantities, any scrapped assemblies are counted with the completed units. Note that for material requirements, expense items are ignored. /* +=============================================================================+ -- | Copyright 2017 - 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 A. Volz -- | -- | Version Modified on Modified by Description -- | ======= =========== ============= ========================================= -- | 1.0 16 MAR 2017 Douglas Volz Initial Coding -- | 1.1 19 MAR 2017 Douglas Volz Added interface conditions for eligibility -- | and check for no applied OSP -- | 1.3 27 MAR 2017 Douglas Volz Fix "return more than one row" error for -- | correlated sub-query on OSP and add in check for purchase requisitions -- | 1.4 27 APR 2017 Douglas Volz Fix for cross-joining results -- | 1.6 25 Oct 2017 Douglas Volz Remove p_date_completed parameter, not needed -- | 1.7 25 Jul 2018 Douglas Volz Removed all categories except Inventory -- | 1.8 25 Jul 2018 Douglas Volz Removed all category values -- | 1.9 11 Dec 2020 Douglas Volz Now for Standard, Lot Based Standard and Non- -- | Standard Asset Jobs. Added another category. -- | 1.10 26 Jan 2021 Douglas Volz Check for unissued materials and WIP scrap controls -- | 1.11 11 Feb 2021 Douglas Volz Added parameter to include scrap for requirements -- | 1.12 05 Mar 2021 Douglas Volz Added parameter to include bulk items for requirements. -- | 1.13 12 Mar 2021 Douglas Volz Add logic to ignore Phantom WIP Supply Types as -- | these requirements are never issued. -- | 1.14 15 Apr 2021 Douglas Volz Added Date Released -- | 1.15 10 Jul 2022 Douglas Volz Added WIP Variance Percentage parameter. -- | 1.16 21 Jan 2024 Douglas Volz Bug fix for Pending Material and Pending Shop Floor -- | Move. Remove tabs and add inventory access controls. -- | 1.17 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &category_columns 1=1 2=2 :p_include_scrap :p_wip_var_percent :p_wip_var_threshold 1 10 :p_wip_var_threshold Number 500 Y US Variance Amount Threshold 2 20 :p_wip_var_percent Number 20 Y US Variance Percent Threshold 3 30 :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 4 40 2=2 wro.wip_supply_type<>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 5 50 &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 6 60 &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 7 70 &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 8 80 1=1 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 9 90 1=1 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 10 100 1=1 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 11 110 1=1 msiv.concatenated_segments=:p_item_number LOV INV Item (master, costing enabled only) 91D022B15B12FFB9E053BB6B63587F0B select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.organization_id in (select 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 Assembly Number 12 120 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 13 130 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