ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 3A376D8F472FAEF6E06362FB0905E9E3Y select :period_name period_name, :balance_type balance_type, :average_balance_type average_balance_type, :template_name, :actual_flag actual_flag, :ledger ledger_name, :budget_name budget_name, :currency_code currency_code, :currency_type, :conversion_type, :amount_type, :encumbrance_type encumbrance_type, :account_type account_type, :movement movement, :translated_flag translated_flag, :gl_segments gl_segments, :fsg_report, :fsg_converter, :gl_segments_cb gl_segments_cb, :get_balance_function get_balance_function, :balance_drilldown_function balance_drilldown_function, :expand_segment_function expand_segment_function, :period_offset_function period_offset_function, :hierarchy_name, :custom_properties, :default_hierarchy, :gl_segment, :gl_segment_value, :next_segment_value, :discover_period_name, :daily_rates, :period_year, :period_num, :journal_source, :journal_category, :gl_segment1 gl_segment1, :gl_segment2 gl_segment2, :gl_segment3 gl_segment3, :gl_segment4 gl_segment4, :gl_segment5 gl_segment5, :gl_segment6 gl_segment6, :gl_segment7 gl_segment7, :gl_segment8 gl_segment8, :gl_segment9 gl_segment9, :gl_segment10 gl_segment10, :gl_segment11, :gl_segment12, :gl_segment13, :gl_segment14, :gl_segment15, :gl_segment16, :gl_segment17, :gl_segment18, :gl_segment19, :gl_segment20, :create_lov create_lov, :responsibility_name responsibility_name, :responsibility_key, :balance_drilldown_new, :gl_journal_drilldown, :gl_subledger_details, :gl_journal_drilldown_m, :gl_subledger_details_m, :gl_full_journal_drilldown, :gl_journal_attachment_dd, :drilldown_resp, :batch_name, :journal_name, :document_number, :posting_status, :fund_status, :created_by, :effective_date from dual where 1=1 F D651DE2041652BFFE0530100007F0134 US GL Financial Statement and Drilldown (FSG) (Backup) The GL Financial Statement and Drilldown (FSG) Report empowers users to generate comprehensive reports on financial balances while providing detailed insights through drilldown capabilities. This tool allows users to link Excel cells directly to Oracle data via built-in functions, ensuring that data can be refreshed as needed to reflect the most up-to-date information. Key features include: 1. Balance and Detail Reporting: Generate high-level balance reports and drill down into the details, including journal entries and subledger transactions. 2. Oracle Data Integration: Seamlessly link Excel cells to Oracle data, with the ability to refresh the data for real-time updates. 3. Drilldown Functionality: Access detailed information at various levels, including balances, journal entries, and subledger details. 4. Migration Tools: Converters are available to migrate reports from Oracle FSG, GL Wand, and Spreadsheet Server to this solution. For a quick demonstration, refer to our YouTube video. https://youtu.be/dsRWXT2bem8 Important: Please do not delete the "Financial Statement Generator" sheet or modify the Advanced Custom Properties in the Excel output, as these are essential for the proper functioning of the report. 1=1 :account_type :actual_flag :amount_type :average_balance_type :balance_drilldown_function :balance_drilldown_new :balance_type :batch_name :budget_name :conversion_type :create_lov :created_by :currency_code :currency_type :custom_properties :daily_rates :default_hierarchy :discover_period_name :document_number :drilldown_resp :effective_date :encumbrance_type :expand_segment_function :fsg_converter :fsg_report :fund_status :get_balance_function :gl_full_journal_drilldown :gl_journal_attachment_dd :gl_journal_drilldown :gl_journal_drilldown_m :gl_segment :gl_segment1 :gl_segment10 :gl_segment11 :gl_segment12 :gl_segment13 :gl_segment14 :gl_segment15 :gl_segment16 :gl_segment17 :gl_segment18 :gl_segment19 :gl_segment2 :gl_segment20 :gl_segment3 :gl_segment4 :gl_segment5 :gl_segment6 :gl_segment7 :gl_segment8 :gl_segment9 :gl_segment_value :gl_segments :gl_segments_cb :gl_subledger_details :gl_subledger_details_m :hierarchy_name :journal_category :journal_name :journal_source :ledger :movement :next_segment_value :period_name :period_num :period_offset_function :period_year :posting_status :responsibility_key :responsibility_name :template_name :translated_flag 1 -680 :effective_date LOV custom select 'value' value, 'description' description from dual US EFFECTIVE_DATE LOV 2 -670 :created_by LOV custom select fu.user_name id, fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') and exists (select * from gl_je_headers gjh where gjh.created_by=fu.user_id) order by fu.inactive desc, fu.user_name US CREATED_BY LOV 3 -660 :fund_status LOV custom select flv.meaning id, flv.meaning value, flv.description description from fnd_lookup_values flv where flv.lookup_type='JE_BATCH_BC_STATUS' and flv.view_application_id=101 and flv.language='US' and flv.enabled_flag='Y' and flv.security_group_id=0 US FUND_STATUS LOV 4 -650 :posting_status LOV custom select x.value id, x.value, x.description from ( select xxen_util.meaning('P','BATCH_STATUS',101) value, null description from dual union all select xxen_util.meaning('U','BATCH_STATUS',101) value, null description from dual union all select xxen_util.meaning('E','VALIDATION_STATUS',101) value, null description from dual ) x US POSTING_STATUS LOV 5 -640 :journal_name LOV custom select distinct gjh.name id, gjh.name value, gjh.description from gl_je_headers gjh where gjh.period_name=:$flex$.pn and (:$flex$.bn is null or gjh.je_batch_id in (select gjb.je_batch_id from gl_je_batches gjb where xxen_util.contains(:$flex$.bn,gjb.name)='Y')) and gjh.ledger_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ln,gl.name)='Y') order by gjh.name US JOURNAL_NAME LOV 6 -630 :batch_name LOV custom select distinct gjb.name id, gjb.name value, gjb.description from gl_je_batches gjb where gjb.je_batch_id in (select gjh.je_batch_id from gl_je_headers gjh where gjh.period_name=:$flex$.pn and (:$flex$.jn is null or gjh.name=:$flex$.jn) and gjh.ledger_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ln,gl.name)='Y')) order by gjb.name US BATCH_NAME LOV 7 -620 :gl_subledger_details_m LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_REPORT_SD''), ''GL Account Analysis (Drilldown)''), p_template_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_TEMPLATE_SD''), ''FSG Subgledger Details''), parameter1=>''GL Flex Value Security''||''~^''||''x'', parameter2=>''Ledger ID''||''~^''||:$flex$.ln, parameter3=>''Period''||''~^''||case when :$flex$.bt not in (''QTD'',''CTD'',''YTD'',''FYS'',''FYE'',''JED'',''JEDU'',''JEDP'') then :$flex$.pn end, parameter4=>''Status''||''~^''||''Posted'', parameter5=>''Concatenated Segments''||''~^''||:$flex$.cs, parameter6=>''Balance Type''||''~^''||case when :$flex$.af=''A'' then ''Actual'' when :$flex$.af=''B'' then ''Budget'' else ''Encumbrance'' end, parameter7=>''Budget Name''||''~^''||:$flex$.bn, parameter8=>''Encumbrance Type''||''~^''||:$flex$.et, parameter9=>''Journal Source''||''~^''||:$flex$.js, parameter10=>''Journal Category''||''~^''||:$flex$.jc, parameter11=>''Multiple Periods String''||''~^''||case when :$flex$.bt in (''QTD'',''CTD'',''YTD'',''FYS'',''FYE'') then :$flex$.bt||:$flex$.pn end, parameter12=>''Effective Dates String''||''~^''||case when instr(:$flex$.bt,''JED'')>0 then :$flex$.pn end, p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US SDM Drilldown 8 -610 :gl_journal_drilldown_m LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_REPORT_GJ''), ''GL Journals (Drilldown)''), p_template_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_TEMPLATE_GJ''), ''GL Journal Drilldown''), parameter1=>''GL Flex Value Security''||''~^''||''x'', parameter2=>''Ledger ID''||''~^''||:$flex$.ln, parameter3=>''Period''||''~^''||case when :$flex$.bt not in (''QTD'',''CTD'',''YTD'',''FYS'',''FYE'',''JED'',''JEDU'',''JEDP'') then :$flex$.pn end, parameter4=>''Posting Status''||''~^''||case when :$flex$.ps is not null then :$flex$.ps when :$flex$.bt=''JED'' then '''' when :$flex$.bt=''JEDU'' then ''Unposted'' else ''Posted'' end, parameter5=>''Code Combination ID''||''~^''||:$flex$.cs, parameter6=>''Show Attachment Details''||''~^''||''N'', parameter7=>''Balance Type''||''~^''||case when :$flex$.af=''A'' then ''Actual'' when :$flex$.af=''B'' then ''Budget'' else ''Encumbrance'' end, parameter8=>''Budget Name''||''~^''||:$flex$.bn, parameter9=>''Encumbrance Type''||''~^''||:$flex$.et, parameter10=>''Journal Source''||''~^''||:$flex$.js, parameter11=>''Journal Category''||''~^''||:$flex$.jc, parameter12=>''Multiple Periods String''||''~^''||case when :$flex$.bt in (''QTD'',''CTD'',''YTD'',''FYS'',''FYE'') then :$flex$.bt||:$flex$.pn end, parameter13=>''Amount Type''||''~^''||:$flex$.bt, parameter14=>''Batch''||''~^''||:$flex$.batch, parameter15=>''Journal''||''~^''||:$flex$.jn, parameter16=>''Creation Date From''||''~^''||:$flex$.dcf, parameter17=>''Creation Date To''||''~^''||:$flex$.dct, parameter18=>''Posted Date From''||''~^''||:$flex$.pdf, parameter19=>''Posted Date To''||''~^''||:$flex$.pdt, parameter20=>''Created By''||''~^''||:$flex$.cd, parameter21=>''Funds Status''||''~^''||:$flex$.fs, parameter22=>''Reference''||''~^''||:$flex$.er, parameter23=>''Effective Dates String''||''~^''||case when instr(:$flex$.bt,''JED'')>0 then :$flex$.pn end, p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US GJM Drilldown 9 -600 :balance_drilldown_new LOV custom select 'xxen_fsg.balance_drilldown( ''GL_BALANCE_DRILLDOWN_NEW'', :$flex$.ln, :$flex$.pn, :$flex$.bt, :$flex$.cc, :$flex$.tf, :$flex$.af, :$flex$.bn, :$flex$.et, :$flex$.mo, :$flex$.mu, :$flex$.s1, :$flex$.s2, :$flex$.s3, :$flex$.s4, :$flex$.s5, :$flex$.s6, :$flex$.s7, :$flex$.s8, :$flex$.s9, :$flex$.s10, :$flex$.s11, :$flex$.s12, :$flex$.s13, :$flex$.s14, :$flex$.s15, :$flex$.s16, :$flex$.s17, :$flex$.s18, :$flex$.s19, :$flex$.s20, :$flex$.js, :$flex$.jc, :$flex$.ccid, :p_balance_data )' id, null value, null description from dual US GL_BALANCE_DRILLDOWN_NEW Drilldown 10 -590 :fsg_report LOV custom select rrv.name id, rrv.name value, rrv.report_title description from rg_reports_v rrv order by rrv.name US FSG_REPORT DD 11 -580 :fsg_converter LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_ORACLE_TO_BLITZ_CONVERTER''), ''GL Oracle FSG Converter''), parameter1=>''Report Name''||''~^''||:$flex$.report_name, parameter2=>''Ledger''||''~^''||:$flex$.ledger, p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US FSG_CONVERTER Drilldown 12 -570 :gl_segment LOV custom Y with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.application_column_name=:$flex$.gl_segment ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT Segments 13 -560 :drilldown_resp LOV custom select x.id,x.value,x.description from ( select 'Default' id, 'Default' value, 'Default' description, 1 sort_no from dual union all select frv.responsibility_key id, frv.responsibility_name value, fav.application_short_name description, 2 sort_no from fnd_form_functions_vl fffv, fnd_application_vl fav, fnd_responsibility_vl frv, fnd_compiled_menu_functions fcmf where 1=1 and fffv.function_name='XLA_LINESINQ_GL_DRILLDOWN' and fffv.function_id=fcmf.function_id and frv.menu_id=fcmf.menu_id and nvl(fcmf.grant_flag,'Y')='Y' and frv.application_id=fav.application_id(+) and (frv.application_id,frv.responsibility_id) in ( select furg.responsibility_application_id, furg.responsibility_id from fnd_user fu, fnd_user_resp_groups furg where fu.user_name=:$flex$.user_name_like and fu.user_id=furg.user_id ) ) x order by x.sort_no, x.id US DRILLDOWN_RESP DD 14 -550 :template_name LOV custom select xrtv.template_name id, null value, null description from xxen_report_templates_v xrtv, xxen_reports_v xrv where xrv.report_name=:$flex$.report_name and xrtv.report_id=xrv.report_id and (xrtv.owner_id=xxen_util.user_id(:$flex$.user_name) or xrtv.access_allowed='Y' or fnd_profile.value('XXEN_REPORT_ACCESS') in ('D','S') or fnd_profile.value('XXEN_REPORT_TEMPLATE_ACCESS')='Super User') order by xrtv.template_name US TEMPLATE_NAME DD 15 -540 :custom_properties LOV custom select x.* from ( select 'TIMEOUT' id, nvl(fnd_profile.value('XXEN_FSG_WEBSERVICE_TIMEOUT'),'120') value, null description from dual union all select 'MAX_FUNCTION' id, nvl(fnd_profile.value('XXEN_FSG_WEBSERVICE_MAXIMUM_FUNCTIONS'),'100') value, null description from dual union all select 'UPPER_LIMIT' id, nvl(fnd_profile.value('XXEN_FSG_WEBSERVICE_FUNCTIONS_UPPER_LIMIT'),'10000') value, null description from dual union all select 'REPORT_GJ' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_REPORT_GJ'),'GL Journals (Drilldown)') value, null description from dual union all select 'REPORT_GJA' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_REPORT_GJA'),'GL Journals (Drilldown)') value, null description from dual union all select 'REPORT_GFJ' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_REPORT_GFJ'),'GL Journals (Drilldown)') value, null description from dual union all select 'REPORT_SD' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_REPORT_SD'),'GL Account Analysis (Drilldown)') value, null description from dual union all select 'TEMPLATE_GJ' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TEMPLATE_GJ'),'GL Journal Drilldown') value, null description from dual union all select 'TEMPLATE_GJA' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TEMPLATE_GJA'),'GL Journal Drilldown (Attachment)') value, null description from dual union all select 'TEMPLATE_GFJ' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TEMPLATE_GFJ'),'GL Full Journal Drilldown') value, null description from dual union all select 'TEMPLATE_SD' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TEMPLATE_SD'),'FSG Subgledger Details') value, null description from dual union all select 'LATEST_OPEN_PERIOD' id, xxen_util.latest_open_period(:$flex$.ledger) value, null description from dual union all select 'FUNCTIONAL_CURRENCY' id, xxen_util.functional_currency_code(:$flex$.ledger) value, null description from dual union all select 'FSG_VERSION' id, xxen_fsg.fsg_version value, null description from dual union all select 'DRILLDOWN_SAME_WORKBOOK' id, nvl(fnd_profile.value('XXEN_FSG_DRILLDOWN_TO_SAME_WORKBOOK'),'N') value, null description from dual union all select 'DOUBLECLICK_TO_FUNCTION' id, nvl(fnd_profile.value('XXEN_FSG_DOUBLECLICK_TO_BALANCE_FUNCTION'),'N') value, null description from dual union all select 'ENABLE_SSO' id, nvl(fnd_profile.value('XXEN_REPORT_SSO_ENABLED'),'N') value, null description from dual ) x US CUSTOM_PROPERTIES DD 16 -530 :gl_segment20 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=20 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT20 Segments 17 -450 :gl_segment12 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=12 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT12 Segments 18 -440 :gl_segment11 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=11 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT11 Segments 19 -430 :default_hierarchy LOV custom select distinct x.form_left_prompt||' ('||x.segment_num||')' id, max(x.parent_flex_value) keep (dense_rank last order by x.count) over (partition by x.form_left_prompt) value, segment_num description from ( select distinct ffvh.parent_flex_value, fifsv.form_left_prompt, fifsv.segment_num, count(*) over (partition by fifsv.form_left_prompt,ffvh.parent_flex_value) count from fnd_flex_value_hierarchies ffvh, fnd_id_flex_segments_vl fifsv where ffvh.flex_value_set_id=fifsv.flex_value_set_id and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) ) x order by x.segment_num US DEFAULT_HIERARCHY DD 20 -420 :period_name LOV custom select gps.period_name id, gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_period_statuses gps where gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 order by gps.effective_period_num desc US PERIOD_NAME LOV 21 -410 :gl_segment10 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=10 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT10 Segments 22 -400 :gl_segment9 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=9 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT9 Segments 23 -390 :gl_segment8 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=8 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT8 Segments 24 -380 :gl_segment7 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=7 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT7 Segments 25 -370 :gl_segment6 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=6 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT6 Segments 26 -360 :gl_segment5 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=5 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT5 Segments 27 -350 :gl_segment4 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=4 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT4 Segments 28 -340 :gl_segment3 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=3 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT3 Segments 29 -330 :gl_segment2 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=2 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT2 Segments 30 -320 :gl_segment1 LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and fifs.segment_num=1 ) select case when connect_by_isleaf is null then 'P' else 'C' end id, ffvnh.flex_value value, replace(ffvv.description,chr(9),' ') description from ( select gl.segment_num, gl.segment_name, gl.application_column_name, ffvnh.parent_flex_value flex_value, ffvnh.child_flex_value_low, ffvnh.child_flex_value_high, ffvnh.range_attribute, ffvnh.flex_value_set_id, ffvnh.created_by, ffvnh.creation_date, ffvnh.last_updated_by, ffvnh.last_update_date from fnd_flex_value_norm_hierarchy ffvnh, gl where :$flex$.hierarchy_name is not null and ffvnh.flex_value_set_id=gl.flex_value_set_id union all select gl.segment_num, gl.segment_name, gl.application_column_name, ffv2.flex_value flex_value, null child_flex_value_low, null child_flex_value_high, 'x' range_attribute, ffv2.flex_value_set_id, ffv2.created_by, ffv2.creation_date, ffv2.last_updated_by, ffv2.last_update_date from fnd_flex_values ffv2, gl where :$flex$.hierarchy_name is not null and ffv2.summary_flag='N' and ffv2.flex_value_set_id=gl.flex_value_set_id ) ffvnh, fnd_flex_values_vl ffvv where ffvnh.flex_value=ffvv.flex_value and gl_security_pkg.validate_segval(ffvnh.segment_num,null,ffvv.flex_value)='TRUE' and ffvnh.flex_value_set_id=ffvv.flex_value_set_id connect by nocycle ffvnh.flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and decode(nvl(prior ffvnh.range_attribute,'P'),'P','Y','N')=ffvv.summary_flag start with ffvnh.flex_value=:$flex$.hierarchy_name union all select x.* from ( select decode(ffvv.summary_flag,'Y','P','C') id, ffvv.flex_value value, replace(ffvv.description,chr(9),' ') description from gl, fnd_flex_values_vl ffvv where gl.flex_value_set_id=ffvv.flex_value_set_id and gl_security_pkg.validate_segval(gl.segment_num,null,ffvv.flex_value)='TRUE' and :$flex$.hierarchy_name is null order by ffvv.flex_value ) x US GL_SEGMENT1 Segments 31 -310 :currency_code LOV custom select fcv.currency_code id, fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code US CURRENCY_CODE LOV 32 -300 :journal_category LOV custom select gjcv.user_je_category_name id, gjcv.user_je_category_name value, gjcv.description from gl_je_categories_vl gjcv order by gjcv.user_je_category_name US JOURNAL_CATEGORY LOV 33 -290 :journal_source LOV custom select gjsv.user_je_source_name id, gjsv.user_je_source_name value, gjsv.description from gl_je_sources_vl gjsv order by gjsv.user_je_source_name US JOURNAL_SOURCE LOV 34 -280 :gl_segments LOV custom select x.id, x.value, row_number() over(partition by 1 order by segment_num) description from (select distinct 'GL_'||fifsv.application_column_name id, fifsv.form_left_prompt||' ('||min(regexp_replace(fifsv.application_column_name, '[^0-9]')) over (partition by fifsv.application_column_name, fifsv.form_left_prompt)||')' value, lower(fifsv.application_column_name) description, to_number(regexp_replace(fifsv.application_column_name, '[^0-9]')) segment_num from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and :$flex$.ledger is not null and fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) union all select 'PERIOD_NAME' id, 'Period Name' value, null description, null segment_num from dual order by segment_num ) x US GL_SEGS DD 35 -270 :encumbrance_type LOV custom select get.encumbrance_type id, get.encumbrance_type value, get.description description from gl_encumbrance_types get order by id US ENCUMBRANCE_TYPE LOV 36 -260 :budget_name LOV custom select gbv.budget_name id, gbv.budget_name value, gbv.budget_type || ' (' || xxen_util.meaning(gbv.status,'PLAN/VERSION STATUS',101) || ')' description from gl_ledgers gl, gl_budgets gb, gl_budget_versions gbv where gl.ledger_id=gb.ledger_id and gb.budget_name=gbv.budget_name and gb.budget_type=gbv.budget_type and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') order by value US BUDGET_NAME LOV 37 -250 :balance_type LOV custom select flvv.lookup_code id, flvv.lookup_code value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type='BATCH_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0 order by flvv.lookup_code US BALANCE_TYPE LOV 38 -240 :responsibility_key LOV custom select frv.responsibility_key value, responsibility_name description from fnd_responsibility_vl frv where frv.application_id=fnd_global.resp_appl_id and frv.responsibility_id=fnd_global.resp_id US Responsibility Key 39 -230 :average_balance_type LOV custom select flv.lookup_code id, flv.lookup_code value, flv.description description from fnd_lookup_values flv where flv.lookup_type='AVERAGE_BALANCE_TYPE' and flv.language='US' and flv.enabled_flag='Y' US AVERAGE_BALANCE_TYPE LOV 40 -220 :amount_type LOV custom select * from ( select gl.lookup_code id, gl.meaning value, gl.description description from gl_lookups gl where gl.lookup_type='PTD_YTD' order by upper(gl.meaning) ) union all select 'CTD' id, 'CTD' value, 'Custom-to-Date' description from dual union all select 'FYS' id, 'FYS' value, 'Financial Year Start (Actuals)' description from dual union all select 'FYE' id, 'FYE' value, 'Financial Year End' description from dual union all select 'JED' id, 'JED' value, 'Journal Effective Date' description from dual union all select 'JEDP' id, 'JEDP' value, '(Posted) Journal Effective Date' description from dual union all select 'JEDU' id, 'JEDU' value, '(Unposted) Journal Effective Date' description from dual US AMOUNT_TYPE LOV 41 -210 :movement LOV custom select gl.meaning id, gl.meaning value, gl.description description from gl_lookups gl where gl.lookup_type in('DR_CR_NET_CODE') order by upper(gl.meaning) US MOVEMENT LOV 42 -200 :currency_type LOV custom select lookup_code id, lookup_code value, description description from gl_lookups where lookup_type='GL_CURRENCY_TYPE' order by meaning US CURRENCY_TYPE LOV 43 -190 :create_lov LOV custom select y.id, y.value, y.description from ( select distinct case when x.description is not null then x.description else xrpv.parameter_name end id, case when x.value is not null then x.value||' ('||x.id||')' else xxen_report.underscore_to_space(xrpv.parameter_name) end value, case when x.description is not null then 'Segment'||x.id||' LOV' else xxen_report.underscore_to_space(xrpv.parameter_name)||' LOV' end description, xrpv.description tag, x.id id_ from xxen_reports xr, xxen_report_parameters_v xrpv, (select distinct min(to_number(regexp_replace(fifsv.application_column_name, '[^0-9]'))) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) id, fifsv.form_left_prompt value, 'GL_'||fifsv.application_column_name description 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 in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.ledger is null and gl.ledger_category_code='PRIMARY' or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and gl.object_type_code='L' and gl.complete_flag='Y' ))x where xr.report_id=xrpv.report_id and xr.type='F' and xrpv.description in ('LOV','Segments') and xrpv.parameter_name=x.description(+) and 1=1 ) y where y.id_ is not null and y.tag='Segments' or y.tag='LOV' order by y.id_, y.tag desc, y.id US CREATE_LOV DD 44 -180 :responsibility_name LOV custom select frv.responsibility_key id, frv.responsibility_name value, fav.application_short_name description from fnd_form_functions_vl fffv, fnd_application_vl fav, fnd_responsibility_vl frv, fnd_compiled_menu_functions fcmf where 1=1 and fffv.function_name='GLXIQACC' and fffv.function_id=fcmf.function_id and frv.menu_id=fcmf.menu_id and nvl(fcmf.grant_flag,'Y')='Y' and frv.application_id=fav.application_id(+) and (frv.application_id,frv.responsibility_id) in ( select furg.responsibility_application_id, furg.responsibility_id from fnd_user fu, fnd_user_resp_groups furg where fu.user_name=:$flex$.user_name_like and fu.user_id=furg.user_id ) order by frv.responsibility_name US CHANGE_RESP DD 45 -170 :gl_segments_cb LOV custom select y.id, y.value, y.description from ( select distinct case when x.description is not null then x.description else xrpv.parameter_name end id, case when x.value is not null then x.value||' ('||x.id||')' else xxen_report.underscore_to_space(xrpv.parameter_name) end value, case when x.description is not null then 'Segment'||x.id||' LOV' else xxen_report.underscore_to_space(xrpv.parameter_name)||' LOV' end description, xrpv.description tag, x.id id_ from xxen_reports xr, xxen_report_parameters_v xrpv, (select distinct min(to_number(regexp_replace(fifsv.application_column_name, '[^0-9]'))) over (partition by fifsv.application_column_name, fifsv.form_left_prompt) id, fifsv.form_left_prompt value, 'GL_'||fifsv.application_column_name description 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 in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.ledger is null and gl.ledger_category_code='PRIMARY' or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and gl.object_type_code='L' and gl.complete_flag='Y' ))x where xr.report_id=xrpv.report_id and xr.type='F' and xrpv.description='Segments' and xrpv.parameter_name=x.description(+) and 1=1 ) y where y.id_ is not null and y.tag='Segments' order by y.id_, y.tag desc US GL_SEGS_SEG_DESC CB 46 -160 :get_balance_function LOV custom select 'xxen_fsg.balance( ''FN_GET_BALANCE'', :$flex$.c, :$flex$.ln, :$flex$.pn, :$flex$.bt, :$flex$.cc, :$flex$.tf, :$flex$.af, :$flex$.bn, :$flex$.et, :$flex$.mo, :$flex$.mu, :$flex$.s1, :$flex$.s2, :$flex$.s3, :$flex$.s4, :$flex$.s5, :$flex$.s6, :$flex$.s7, :$flex$.s8, :$flex$.s9, :$flex$.s10, :$flex$.s11, :$flex$.s12, :$flex$.s13, :$flex$.s14, :$flex$.s15, :$flex$.s16, :$flex$.s17, :$flex$.s18, :$flex$.s19, :$flex$.s20, :$flex$.js, :$flex$.jc, :p_balance_data, :p_cell )' id, null value, null description from dual US FN_GET_BALANCE Function 47 -150 :gl_journal_attachment_dd LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_REPORT_GJA''), ''GL Journals (Drilldown)''), p_template_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_TEMPLATE_GJA''), ''GL Journal Drilldown (Attachment)''), parameter1=>''GL Flex Value Security''||''~^''||''x'', parameter2=>''Ledger ID''||''~^''||:$flex$.ledger, parameter3=>''Period''||''~^''||:$flex$.period_name, parameter4=>''Posting Status''||''~^''||''Posted'', parameter5=>''Batch ID''||''~^''||:$flex$.bi, parameter6=>''Journal Header ID''||''~^''||:$flex$.jhi, parameter7=>''Code Combination ID''||''~^''||:$flex$.concat_segs, parameter8=>''Show Attachment Details''||''~^''||''Y'', parameter9=>''Balance Type''||''~^''||case when :$flex$.actual_flag=''A'' then ''Actual'' when :$flex$.actual_flag=''B'' then ''Budget'' when :$flex$.actual_flag=''E'' then ''Encumbrance'' else null end, parameter10=>''Budget Name''||''~^''||:$flex$.budget_name, parameter11=>''Encumbrance Type''||''~^''||:$flex$.encumbrance_type, parameter12=>''Journal Source''||''~^''||:$flex$.js, parameter13=>''Journal Category''||''~^''||:$flex$.jc, parameter14=>''Amount Type''||''~^''||:$flex$.balance_type, parameter15=>''Journal Line Num''||''~^''||:$flex$.jln, parameter16=>''je_header_id|je_line_num''||''~^''||:$flex$.jhijln, p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US GJA Drilldown 48 -140 :gl_full_journal_drilldown LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_REPORT_GFJ''), ''GL Journals (Drilldown)''), p_template_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_TEMPLATE_GFJ''), ''GL Full Journal Drilldown''), parameter1=>''GL Flex Value Security''||''~^''||''x'', parameter2=>''Ledger ID''||''~^''||:$flex$.ledger, parameter3=>''Period''||''~^''||:$flex$.period_name, parameter4=>''Posting Status''||''~^''||''Posted'', parameter5=>''Batch ID''||''~^''||:$flex$.bi, parameter6=>''Journal Header ID''||''~^''||:$flex$.jhi, parameter7=>''Show Attachment Details''||''~^''||''N'', parameter8=>''Journal Source''||''~^''||:$flex$.js, parameter9=>''Journal Category''||''~^''||:$flex$.jc, parameter10=>''Amount Type''||''~^''||:$flex$.balance_type, p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US GFJ Drilldown 49 -130 :gl_journal_drilldown LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_REPORT_GJ''), ''GL Journals (Drilldown)''), p_template_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_TEMPLATE_GJ''), ''GL Journal Drilldown''), parameter1=>''GL Flex Value Security''||''~^''||''x'', parameter2=>''Ledger ID''||''~^''||:$flex$.ledger, parameter3=>''Period''||''~^''||case when :$flex$.balance_type not in (''QTD'',''CTD'',''YTD'',''FYS'',''FYE'',''JED'',''JEDU'',''JEDP'') then :$flex$.period_name end, parameter4=>''Posting Status''||''~^''||case when :$flex$.bt=''JED'' then '''' when :$flex$.bt=''JEDU'' then ''Unposted'' else ''Posted'' end, parameter5=>''Code Combination ID''||''~^''||:$flex$.concat_segs, parameter6=>''Show Attachment Details''||''~^''||''N'', parameter7=>''Balance Type''||''~^''||case when :$flex$.actual_flag=''A'' then ''Actual'' when :$flex$.actual_flag=''B'' then ''Budget'' else ''Encumbrance'' end, parameter8=>''Budget Name''||''~^''||:$flex$.budget_name, parameter9=>''Encumbrance Type''||''~^''||:$flex$.encumbrance_type, parameter10=>''Journal Source''||''~^''||:$flex$.js, parameter11=>''Journal Category''||''~^''||:$flex$.jc, parameter12=>''Multiple Periods String''||''~^''||case when :$flex$.balance_type in (''QTD'',''CTD'',''YTD'',''FYS'',''FYE'') then :$flex$.balance_type||:$flex$.period_name end, parameter13=>''Amount Type''||''~^''||:$flex$.balance_type, parameter14=>''Effective Dates String''||''~^''||case when instr(:$flex$.balance_type,''JED'')>0 then :$flex$.period_name end, p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US GJ Drilldown 50 -120 :gl_subledger_details LOV custom select 'xxen_fsg.get_drilldown_data( p_report_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_REPORT_SD''), ''GL Account Analysis (Drilldown)''), p_template_name=>nvl(fnd_profile.value(''XXEN_FSG_DRILLDOWN_TEMPLATE_SD''), ''FSG Subgledger Details''), parameter1=>''GL Flex Value Security''||''~^''||''x'', parameter2=>''je_header_id|je_line_num''||''~^''||:$flex$.jhi, parameter3=>''Status''||''~^''||''Posted'', p_drilldown_data=>:p_drilldown_data )' id, null value, null description from dual US SD Drilldown 51 -110 :balance_drilldown_function LOV custom select 'xxen_fsg.balance_drilldown( ''GL_BALANCE_DRILLDOWN'', :$flex$.ledger, :$flex$.period_name, :$flex$.balance_type, :$flex$.currency_code, :$flex$.translated_flag, :$flex$.actual_flag, :$flex$.budget_name, :$flex$.encumbrance_type, :$flex$.movement, :$flex$.multiplier, :$flex$.segment1, :$flex$.segment2, :$flex$.segment3, :$flex$.segment4, :$flex$.segment5, :$flex$.segment6, :$flex$.segment7, :$flex$.segment8, :$flex$.segment9, :$flex$.segment10, :$flex$.segment11, :$flex$.segment12, :$flex$.segment13, :$flex$.segment14, :$flex$.segment15, :$flex$.segment16, :$flex$.segment17, :$flex$.segment18, :$flex$.segment19, :$flex$.segment20, :$flex$.js, :$flex$.jc, :$flex$.ccid, :p_balance_data )' id, null value, null description from dual US GL_BALANCE_DRILLDOWN Drilldown 52 -100 :expand_segment_function LOV custom with gl as ( select fifs.flex_value_set_id, fifs.application_column_name, fifs.segment_name, fifs.segment_num from fnd_id_flex_segments fifs, fnd_id_flex_segments_tl fifst where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.id_flex_num=(select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and fifs.application_id=fifst.application_id and fifs.id_flex_code=fifst.id_flex_code and fifs.id_flex_num=fifst.id_flex_num and fifs.application_column_name=fifst.application_column_name and fifst.language='US' and (:$flex$.segment_num is null or fifs.segment_num=:$flex$.segment_num) and (:$flex$.gl_segment is null or fifs.application_column_name=:$flex$.gl_segment) ) select z.* from ( select case when instr(:$flex$.segment_range,'~')>0 then '~' end || case when :$flex$.child_only='Y' then x.column_value else substr(substr(x.column_value,1,instr(x.column_value,':',1,2)-1),instr(x.column_value,':')+1) end id, case when :$flex$.child_only='N' then substr(x.column_value,1,instr(x.column_value,':')-1)-1 end value, substr(x.column_value,instr(x.column_value,':',1,2)+1) description from ( select case when instr(:$flex$.segment_range,'~')>0 then '~' end || ffv2.flex_value id, gl.flex_value_set_id, gl.segment_num from fnd_flex_values ffv2, gl, ( select replace(:$flex$.segment_range,'~','') segment_range from dual where xxen_fsg.is_parent_flex(:$flex$.ledger,replace(:$flex$.segment_range,'~',''),nvl(:$flex$.segment_num,regexp_replace(:$flex$.gl_segment, '[^0-9]')))<>'Y' union all select replace(:$flex$.segment_range,'~','') segment_range from dual where xxen_fsg.is_parent_flex(:$flex$.ledger,replace(:$flex$.segment_range,'~',''),nvl(:$flex$.segment_num,regexp_replace(:$flex$.gl_segment, '[^0-9]')))='Y' ) x where ffv2.flex_value_set_id=gl.flex_value_set_id and (( instr(:$flex$.segment_range,'-')>0 and ffv2.flex_value>=substr(x.segment_range,1,instr(x.segment_range,'-')-1) and ffv2.flex_value<=substr(x.segment_range,instr(x.segment_range,'-')+1,length(x.segment_range)-instr(x.segment_range,'-')+1)) or (instr(:$flex$.segment_range,'-')=0 and ffv2.flex_value=x.segment_range) ) ) y, table(xxen_fsg.child_flex_value_range(y.flex_value_set_id,replace(y.id,'~',''),y.segment_num,:$flex$.child_only))(+) x ) z where z.value>0 or z.value is null or instr(:$flex$.segment_range,'-')>0 US FN_EXPAND_SEGMENT DD 53 -90 :period_offset_function LOV custom select nvl(case when :$flex$.rr is not null then case when y.offset_months>0 then y.period_name||' - '||gps2.period_name else gps2.period_name||' - '||y.period_name end else gps2.period_name end, ' ') id, :$flex$.c value, null description from gl_period_statuses gps2, ( select x.*, x.period_num+x.offset_months actual_, case when :$flex$.adj is null then case when x.period_num+x.offset_months=0 then -1 when x.period_num+x.offset_months=x.regular_periods+x.adjustment_periods then 0 else floor((x.period_num+x.offset_months)/(x.regular_periods+x.adjustment_periods)) end else case when (x.period_num+x.offset_months)=0 then -1 when (x.period_num+x.offset_months)=x.regular_periods then 0 else floor((x.period_num+x.offset_months)/x.regular_periods) end end year_, case when :$flex$.adj is null then case when x.period_num+x.offset_months>x.regular_periods+x.adjustment_periods then x.period_num+x.offset_months-x.regular_periods-x.adjustment_periods when x.period_num+x.offset_months<=0 then x.regular_periods+x.adjustment_periods+x.period_num+x.offset_months else x.period_num+x.offset_months end else case when x.period_num+x.offset_months>x.regular_periods then x.period_num+x.offset_months-x.regular_periods when x.period_num+x.offset_months<=0 then x.regular_periods+x.period_num+x.offset_months else x.period_num+x.offset_months end end period_num_ from ( select nvl(:$flex$.os,0) offset_months, count(decode(gps.adjustment_period_flag,'N',1)) over (partition by gps.period_year) regular_periods, count(decode(gps.adjustment_period_flag,'Y',1)) over (partition by gps.period_year) adjustment_periods, gps.* from gl_period_statuses gps where gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ln) and gps.application_id=101 ) x where x.period_name=nvl(:$flex$.pn,x.period_name) and x.period_year=nvl(:$flex$.py,x.period_year) and x.period_num=nvl(:$flex$.pnu,x.period_num) and (:$flex$.ed is null or :$flex$.ed between x.start_date and x.end_date and x.start_date<>x.end_date) ) y where gps2.application_id=101 and gps2.ledger_id=y.ledger_id and gps2.period_year=y.period_year+y.year_ and gps2.period_num=y.period_num_ US FN_PERIOD_OFFSET Function 54 -80 :hierarchy_name LOV custom select x.id, x.id value, x.description from ( select distinct ffvnh.parent_flex_value id, (select replace(ffvv.description,chr(9),' ') description from fnd_flex_values_vl ffvv where ffvnh.flex_value_set_id=ffvv.flex_value_set_id and ffvv.parent_flex_value_low is null and ffvnh.parent_flex_value=ffvv.flex_value)||' ('||nvl2(ffvnh.is_child,null,xxen_util.meaning('TOP','TOP_BOTTOM_PERF',283)||' ')||xxen_util.meaning('CCONTAIN','ICX_CONDITIONS',0)||' '||xxen_util.meaning(ffvnh.max_range_attribute,'RANGE_ATTRIBUTE',0)||')' description, ffvnh.is_child, ffvnh.max_range_attribute from ( select distinct ffvnh.flex_value_set_id, ffvnh.parent_flex_value, (select 'Y' from gl_seg_val_norm_hierarchy gsvnh where ffvnh.parent_flex_value=gsvnh.child_flex_value and ffvnh.flex_value_set_id=gsvnh.flex_value_set_id and rownum=1) is_child, max(ffvnh.range_attribute) over (partition by ffvnh.parent_flex_value) max_range_attribute from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id in ( select fifsv.flex_value_set_id from fnd_id_flex_segments_vl fifsv where fifsv.application_column_name=:$flex$.hierarchy_segment and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y') ) ) ffvnh order by ffvnh.is_child desc, ffvnh.max_range_attribute desc, length(ffvnh.parent_flex_value), ffvnh.parent_flex_value ) x US HIERARCHY_NAME CB 55 -70 :gl_segment_value LOV custom select * from ( select ffvv.flex_value id, ffvv.description value, ffvv.summary_flag||'~'||ffvv.enabled_flag||'~'||xxen_util.meaning(substr(ffvv.compiled_value_attributes,5,1),'ACCOUNT_TYPE',0) description from fnd_id_flex_segments_vl fifsgv, (select row_number() over (partition by 1 order by ffvv.flex_value) row_id_,ffvv.* from fnd_flex_values_vl ffvv) ffvv where gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.application_column_name=:$flex$.gl_segment and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.summary_flag=nvl(:$flex$.summary_flag,ffvv.summary_flag) and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and ( :$flex$.direction='DA' and ffvv.flex_value>=nvl(:$flex$.segment_value,ffvv.flex_value) or :$flex$.direction='RA' and ffvv.flex_value>=nvl(:$flex$.segment_value,ffvv.flex_value) or :$flex$.direction='D' and ffvv.flex_value>=nvl(:$flex$.segment_value,ffvv.flex_value) and rownum<=nvl(:$flex$.row_num,3) or :$flex$.direction='R' and ffvv.flex_value>=nvl(:$flex$.segment_value,ffvv.flex_value) and rownum<=nvl(:$flex$.row_num,1) ) order by ffvv.row_id_ ) union all select * from ( select ffvv.flex_value id, ffvv.description value, ffvv.summary_flag||'~'||ffvv.enabled_flag||'~'||xxen_util.meaning(substr(ffvv.compiled_value_attributes,5,1),'ACCOUNT_TYPE',0) description from fnd_id_flex_segments_vl fifsgv, (select row_number() over (partition by 1 order by ffvv.flex_value desc) row_id_,ffvv.* from fnd_flex_values_vl ffvv) ffvv where gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.application_column_name=:$flex$.gl_segment and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.summary_flag=nvl(:$flex$.summary_flag,ffvv.summary_flag) and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and :$flex$.direction='L' and ffvv.flex_value<=nvl(:$flex$.segment_value,ffvv.flex_value) and rownum<=nvl(:$flex$.row_num,1) order by ffvv.row_id_ ) union all select * from ( select ffvv.flex_value id, ffvv.description value, ffvv.summary_flag||'~'||ffvv.enabled_flag||'~'||xxen_util.meaning(substr(ffvv.compiled_value_attributes,5,1),'ACCOUNT_TYPE',0) description from fnd_id_flex_segments_vl fifsgv, (select row_number() over (partition by 1 order by ffvv.flex_value desc) row_id_,ffvv.* from fnd_flex_values_vl ffvv) ffvv where gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.application_column_name=:$flex$.gl_segment and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.summary_flag=nvl(:$flex$.summary_flag,ffvv.summary_flag) and fifsgv.id_flex_num in (select gl.chart_of_accounts_id from gl_ledgers gl where gl.name=:$flex$.ledger) and :$flex$.direction='U' and ffvv.flex_value<=nvl(:$flex$.segment_value,ffvv.flex_value) and rownum<=nvl(:$flex$.row_num,3) order by ffvv.row_id_ desc ) US FN_GL_SEGMENT_VALUES DD 56 -60 :discover_period_name LOV custom select gps.period_name id, gps.period_name value, null description from ( select max(decode(upper(gps.period_name),upper(:$flex$.period_name),gps.period_number)) over () start_period_number, max(case when gps.period_year<=to_char(sysdate,'YYYY') then gps.period_number end) over () max_year_period_number, gps.* from ( select row_number() over (order by gps.effective_period_num) period_number, gps.* from gl_period_statuses gps where gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 ) gps ) gps where ( :$flex$.direction in ('DA','RA') and gps.period_number between gps.start_period_number+1 and gps.max_year_period_number or :$flex$.direction in ('D','R') and gps.period_number between gps.start_period_number+1 and gps.start_period_number+:$flex$.row_num or :$flex$.direction in ('U','L') and gps.period_number between gps.start_period_number-:$flex$.row_num and gps.start_period_number-1 ) order by case when :$flex$.direction in ('U','L') then -gps.period_number else gps.period_number end US FN_DISCOVER_PERIOD_NAME DD 57 -50 :next_segment_value LOV custom select ffvv.flex_value id, ffvv.description value, ffvv.summary_flag||'~'||fifsgv.enabled_flag||'~'||xxen_util.meaning(substr(ffvv.compiled_value_attributes,5,1),'ACCOUNT_TYPE',0) description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.application_column_name=:$flex$.gl_segment and ffvv.flex_value>:$flex$.segment_value and :$flex$.direction='N' and rownum=1 and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and fifsgv.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsgv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) ) ) union all select ffvv.flex_value id, ffvv.description value, ffvv.summary_flag||'~'||fifsgv.enabled_flag||'~'||xxen_util.meaning(substr(ffvv.compiled_value_attributes,5,1),'ACCOUNT_TYPE',0) description from fnd_id_flex_segments_vl fifsgv, fnd_flex_values_vl ffvv where gl_security_pkg.validate_segval(fifsgv.segment_num,null,ffvv.flex_value)='TRUE' and fifsgv.application_id=101 and fifsgv.id_flex_code='GL#' and fifsgv.application_column_name=:$flex$.gl_segment and ffvv.flex_value<:$flex$.segment_value and :$flex$.direction='P' and rownum=1 and fifsgv.flex_value_set_id=ffvv.flex_value_set_id and ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) and fifsgv.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsgv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) ) ) US FN_NEXT_SEGMENT_VALUE Function 58 -40 :daily_rates LOV custom select gdr.conversion_rate id, :$flex$.c value, null description from gl_daily_conversion_types gdct, gl_daily_rates gdr where gdr.from_currency=:$flex$.fc and gdr.to_currency=:$flex$.tc and gdct.user_conversion_type=:$flex$.ct and gdr.conversion_date=:$flex$.cd and gdct.conversion_type=gdr.conversion_type US FN_DAILY_RATES Function 59 -30 :conversion_type LOV custom select gdct.user_conversion_type id, gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 US CONVERSION_TYPE LOV 60 -20 :period_year LOV custom select distinct gps.period_year id, gps.period_year value, null description from gl_period_statuses gps where gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 order by gps.period_year US PERIOD_YEAR LOV 61 -10 :period_num LOV custom select distinct gps.period_num id, gps.period_num value, null description from gl_period_statuses gps where gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 order by gps.period_num US PERIOD_NUM LOV 62 10 :ledger LOV custom Y select gl.name id, gl.name value, gl.ledger_id||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv, gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav, fnd_responsibility_vl frv where (frv.responsibility_key=:$flex$.responsibility_key or (:$flex$.responsibility_key is null and frv.responsibility_id=fnd_global.resp_id)) and gasna.access_set_id=fnd_profile.value_specific('GL_ACCESS_SET_ID',null,frv.responsibility_id,frv.application_id,null,null) and gasna.ledger_id=glsnav.ledger_set_id(+)and gl.ledger_id = nvl(glsnav.ledger_id,gasna.ledger_id) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by id xxen_util.default_ledger Y US Ledger LOV