ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5168C2E17E02409DBA42F72D94F876BFY select x.ledger, x.book, x.period, x.currency, x.balancing_segment, x.balancing_segment_description, x.expense_account, x.expense_account_description, x.cost_center, x.cost_center_description, x.asset_cost_account, x.asset_cost_account_description, x.reserve_account, x.reserve_account_description, x.asset_category, x.asset_category_description, x.major_category, x.major_category_description, x.minor_category, x.minor_category_description, x.category_segment, x.category_segment_description, x.asset_number, x.asset_description, x.tag_number, x.serial_number, x.asset_key, x.location, x.group_asset_number, x.inventorial, x.employee_name, x.employee_number, x.units, x.percent, x.date_placed_in_service, x.depreciation_method, x."Life Yr.Mo", x.adjusted_rate, x.cost, x.salvage_value, x.depreciation_amount, x.ytd_depreciation, x.ltd_depreciation, x.nbv_beginning_fiscal_year, x.nbv_at_period_end from ( select gl.name ledger, :p_book book, :p_period1 period, gl.currency_code currency, gcc.balancing_segment, gcc.balancing_segment_description, gcc.expense_account, gcc.expense_account_description, gcc.cost_center, gcc.cost_center_description, fcb.asset_cost_acct asset_cost_account, xxen_util.segment_description(fcb.asset_cost_acct,gcc.account_column_name,gcc.chart_of_accounts_id) asset_cost_account_description, frlg.deprn_reserve_acct reserve_account, xxen_util.segment_description(frlg.deprn_reserve_acct,gcc.account_column_name,gcc.chart_of_accounts_id) reserve_account_description, fcv.asset_category, fcv.asset_category_description, fcv.major_category, fcv.major_category_description, fcv.minor_category, fcv.minor_category_description, fcv.category_segment, fcv.category_segment_description, fa.asset_number, fa.description asset_description, fa.tag_number, fa.serial_number, (select fakk.concatenated_segments from fa_asset_keywords_kfv fakk where fa.asset_key_ccid=fakk.code_combination_id) asset_key, (select flk.concatenated_segments from fa_locations_kfv flk where fdh.location_id=flk.location_id) location, decode(fa.asset_type,'GROUP',fa.asset_number,nvl(fa_grp.asset_number,(select flv.meaning from fa_lookups_vl flv where fa.asset_type=flv.lookup_code and flv.lookup_type='ASSET TYPE'))) group_asset_number, xxen_util.yes(case when fa.inventorial='YES' then 'Y' end) inventorial, substrb(papf.full_name,1,50) employee_name, substrb(papf.employee_number,1,15) employee_number, fdh.units_assigned units, nvl(fdh.units_assigned,0)/nvl(fah.units,1)*100 percent, frlg.date_placed_in_service, frlg.method_code depreciation_method, floor(frlg.life/12)+mod(frlg.life,12)/100 "Life Yr.Mo", frlg.rate adjusted_rate, frlg.cost, fb.salvage_value, frlg.deprn_amount depreciation_amount, frlg.ytd_deprn ytd_depreciation, frlg.deprn_reserve ltd_depreciation, frlg.cost-frlg.deprn_reserve-nvl(frlg.impairment_reserve,0)+frlg.ytd_deprn nbv_beginning_fiscal_year, frlg.cost-frlg.deprn_reserve-nvl(frlg.impairment_reserve,0) nbv_at_period_end from fa_book_controls fbc, fa_deprn_periods fdp, gl_ledgers gl, fa_reserve_ledger_gt frlg, fa_distribution_history fdh, fa_additions fa, fa_additions fa_grp, fa_asset_history fah, fa_fiscal_year ffy, fa_category_books fcb, ( select /*+ no_merge */ gcc.code_combination_id, gcc.chart_of_accounts_id, fifs.application_column_name account_column_name, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_bal_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'GL_BALANCING','Y','VALUE') balancing_segment, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_bal_seg_d','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'GL_BALANCING','Y','DESCRIPTION') balancing_segment_description, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_acct_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'GL_ACCOUNT','Y','VALUE') expense_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_acct_seg_d','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'GL_ACCOUNT','Y','DESCRIPTION') expense_account_description, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_cc_seg','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'FA_COST_CTR','Y','VALUE') cost_center, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_cc_seg_d','SQLGL','GL#',gcc.chart_of_accounts_id,null,gcc.code_combination_id,'FA_COST_CTR','Y','DESCRIPTION') cost_center_description from gl_code_combinations gcc, fnd_id_flex_segments fifs, fnd_segment_attribute_values fsav where gcc.code_combination_id in (select frlg.dh_ccid from fa_reserve_ledger_gt frlg) and gcc.chart_of_accounts_id=fifs.id_flex_num and fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.application_id=fsav.application_id and fifs.id_flex_code=fsav.id_flex_code and fifs.id_flex_num=fsav.id_flex_num and fifs.application_column_name=fsav.application_column_name and fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y' ) gcc, ( select /*+ no_merge */ fcv.category_id, fcv.property_type_code, fcv.description asset_category_description, (select fcbk.concatenated_segments from fa_categories_b_kfv fcbk where fcv.category_id=fcbk.category_id) asset_category, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_maj_cat','OFA','CAT#',fsc.category_flex_structure,null,fcv.category_id,'BASED_CATEGORY','N','VALUE') major_category, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_maj_cat_d','OFA','CAT#',fsc.category_flex_structure,null,fcv.category_id,'BASED_CATEGORY','N','DESCRIPTION') major_category_description, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_min_cat','OFA','CAT#',fsc.category_flex_structure,null,fcv.category_id,'MINOR_CATEGORY','N','VALUE') minor_category, fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_min_cat_d','OFA','CAT#',fsc.category_flex_structure,null,fcv.category_id,'MINOR_CATEGORY','N','DESCRIPTION') minor_category_description, case when :category_segment_number is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_cat_seg','OFA','CAT#',fsc.category_flex_structure,null,fcv.category_id,:category_segment_number,'N','VALUE') end category_segment, case when :category_segment_number is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_book_cat_seg_d','OFA','CAT#',fsc.category_flex_structure,null,fcv.category_id,:category_segment_number,'N','DESCRIPTION') end category_segment_description from fa_system_controls fsc, fa_categories_vl fcv where fcv.category_id in (select fcb.category_id from fa_category_books fcb where fcb.book_type_code=:p_book) ) fcv, fa_books fb, fa_category_book_defaults fcbd, per_all_people_f papf where fbc.book_type_code=:p_book and fbc.set_of_books_id=gl.ledger_id and fdp.book_type_code=:p_book and fdp.period_name=:p_period1 and frlg.asset_id=fa.asset_id and frlg.asset_id=fdh.asset_id and frlg.dh_ccid=fdh.code_combination_id and frlg.distribution_id=fdh.distribution_id and fdh.date_effective<frlg.date_effective and nvl(fdh.date_ineffective,sysdate)>=frlg.date_effective and frlg.dh_ccid=gcc.code_combination_id and fcb.book_type_code=:p_book and fcb.category_id=fah.category_id and fah.asset_id=fa.asset_id and fah.date_effective<frlg.date_effective and nvl(fah.date_ineffective,sysdate)>=frlg.date_effective and fb.group_asset_id=fa_grp.asset_id(+) and ffy.fiscal_year_name=fbc.fiscal_year_name and frlg.date_placed_in_service between ffy.start_date and ffy.end_date and frlg.date_placed_in_service<=trunc(fdp.calendar_period_close_date) and fb.book_type_code=:p_book and fb.asset_id=frlg.asset_id and fb.date_effective<frlg.date_effective and nvl(fb.date_ineffective,sysdate)>=frlg.date_effective and fcv.category_id=fah.category_id and fcbd.category_id=fah.category_id and fcbd.book_type_code=:p_book and frlg.date_placed_in_service between fcbd.start_dpis and nvl(fcbd.end_dpis,frlg.date_placed_in_service) and fdh.assigned_to=papf.person_id(+) and trunc(sysdate) between papf.effective_start_date(+) and papf.effective_end_date(+) and (fb.group_asset_id is null or :p_drill_down is not null or exists ( select null from fa_books fb_grp, fa_transaction_headers fth, fa_deprn_periods fdp_grp where fb.transaction_header_id_in=fb_grp.transaction_header_id_out and fb_grp.transaction_header_id_out=fth.transaction_header_id and fb_grp.group_asset_id is null and fdp_grp.book_type_code=:p_book and fdp_grp.period_counter=frlg.period_counter and fth.date_effective between fdp_grp.period_open_date and nvl(fdp_grp.period_close_date,fth.date_effective) )) and 1=1 ) x where 2=2 New report converted from the Oracle standard RXi: Fixed Assets Book (concurrent programs FARXBK and RX-only RXFABK, extraction FARX_C_DP.BOOK_RUN -> FARX_DP.DEPRN_RUN). fa_reserve_ledger_gt is populated by xxen_fa_fas_xmlp.beforereport through fa_rx_shared_pkg.fa_rsvldg, the same procedure the Oracle report uses, driven by the new FAS Code RXFABK. The main SQL reproduces FARX_DP's select, from and where clauses plus the values its after_fetch trigger derives. The two seeded attribute sets are shipped as the templates Default and Book Details. Verified against RX-only: Fixed Assets Book on OPS CORP / May-08: request 6028176 (729 rows), 6028181 with drill down (731 rows) and 6028184 with a category segment range (729 rows), each matching row for row with no value difference on any comparable column. Three columns are populated here where the Oracle report leaves them empty or wrong, so they do not match it: the flexfield segment descriptions, which fa_rx_flex_pkg.get_description returns as the bare code for every value after the first one looked up per value set; Category Segment and its description, which FARX_DP never fills because it tests the segment number with <> '' ; and Inventorial, shown as Yes or blank per the Blitz Report convention rather than YES/NO. The segment ranges are applied per bound. The Oracle report ignores the balancing segment, account and cost center ranges unless both bounds are given, and fails with ORA-01006 when a category segment is chosen without both value bounds. XXEN_FA_FAS_XMLP US FA Fixed Assets Book Assets held in a depreciation book with their cost, depreciation and net book value as at the end of the selected period. Replaces the Oracle standard RXi: Fixed Assets Book report. The rows are extracted by the same routine its RX-only: Fixed Assets Book data program uses, so cost and depreciation figures are identical. The two seeded RXi attribute sets, Default and Book Details, are shipped as the templates of the same names. One row per asset distribution, so an asset split over several cost centers or employees appears once per distribution. Units and Percent give the share of the asset in that distribution; Cost and the depreciation amounts are the distribution's share, not the asset total. Cost, YTD Depreciation and LTD Depreciation are the balances of the last depreciation run on or before the selected period. Depreciation Amount is filled only where that run is the selected period itself, and is zero for an asset whose last run is earlier. NBV at Period End is Cost less LTD Depreciation less impairment reserve, so it is lower than Cost less LTD Depreciation for an impaired asset. NBV at Beginning of Fiscal Year adds YTD Depreciation back. Assets placed in service after the end of the selected period are excluded, as are assets fully retired before the start of the fiscal year that period falls in. Drill Down into Group Assets adds the member assets of group assets, which are otherwise represented by their group asset alone. The account, cost center, category and segment description columns are resolved directly from the flexfield value sets. The Oracle report caches only the first value it looks up per value set and falls back to printing the code itself for the rest, so these descriptions are populated here where the Oracle report leaves the bare code. Enginatics 1=1 2=2 :category_segment_number :p_book :p_drill_down :p_fas_code :p_period1 1 -10 :p_fas_code Char RXFABK US FAS Code 2 10 :p_book LOV FA Book B12587B17F666485E0530100007FA7F4 Y select x.value, x.description from ( select fbcs.book_type_code value, fbcs.book_type_name||' ('||fbcs.book_class||')' description from fa_book_controls_sec fbcs where :$flex$.ledger is null or fbcs.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y') and nvl(:$flex$.mrc_flag,'P') != 'R' union select fbcs.book_type_code value, fbcs.book_type_name||' ('||fbcs.book_class||')' description from fa_book_controls_sec fbcs, fa_mc_book_controls fmbc where fbcs.book_type_code = fmbc.book_type_code and fbcs.set_of_books_id = fmbc.primary_set_of_books_id and :$flex$.ledger is null or fmbc.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y') and nvl(:$flex$.mrc_flag,'P') = 'R' ) x order by x.value Y US Book 3 20 :p_period1 LOV FA Period B12835F5EC2F6F85E0530100007F7410 Y select x.value, x.description, x.fiscal_year, x.period_num from ( select fdp.period_name value, fbcs.deprn_calendar || ': ' || fdp.fiscal_year||' - '||fdp.period_num description, fdp.fiscal_year, fdp.period_num, fbcs.deprn_calendar from fa_book_controls_sec fbcs, fa_deprn_periods fdp where fbcs.book_type_code = fdp.book_type_code and (:$flex$.ledger is null or fdp.book_type_code in (select fbcs.book_type_code from gl_ledgers gl, fa_book_controls_sec fbcs where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id)) and (:$flex$.asset_book is null or xxen_util.contains(:$flex$.asset_book,fdp.book_type_code)='Y') and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y') and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and nvl(:$flex$.mrc_flag,'P') != 'R' union select fdpmv.period_name value, fbcs.deprn_calendar || ': ' || fdpmv.fiscal_year||' - '||fdpmv.period_num description, fdpmv.fiscal_year, fdpmv.period_num, fbcs.deprn_calendar from fa_book_controls_sec fbcs, fa_deprn_periods_mrc_v fdpmv where fbcs.book_type_code = fdpmv.book_type_code and (:$flex$.ledger is null or fdpmv.book_type_code in (select fbcmv.book_type_code from gl_ledgers gl, fa_book_controls_mrc_v fbcmv where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcmv.set_of_books_id)) and (:$flex$.asset_book is null or xxen_util.contains(:$flex$.asset_book,fdpmv.book_type_code)='Y') and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fdpmv.book_type_code)='Y') and fdpmv.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and nvl(:$flex$.mrc_flag,'P') = 'R' ) x order by x.fiscal_year desc, x.period_num desc, x.deprn_calendar select fdp.period_name from fa_deprn_periods fdp where fdp.book_type_code=:$flex$.book and fdp.period_counter=(select max(fdp2.period_counter) from fa_deprn_periods fdp2 where fdp2.book_type_code=:$flex$.book and fdp2.period_close_date is not null) Y US Period 4 30 :p_drill_down LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Drill Down into Group Assets Also lists the member assets of group assets. Left empty, a group asset is reported as one row and its members are left out. 5 40 2=2 x.balancing_segment>=:balancing_segment_from LOV GL Balancing F26C78167A214EFBE0530100007F336B Y select distinct substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select fifs.id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.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 ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Balancing Segment From 6 50 2=2 x.balancing_segment<=:balancing_segment_to LOV GL Balancing F26C78167A214EFBE0530100007F336B Y select distinct substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select fifs.id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.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 ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Balancing Segment To 7 60 2=2 x.expense_account>=:expense_account_from LOV GL Account F26C78167A224EFBE0530100007F336B Y select distinct substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select fifs.id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.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 ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Expense Account From 8 70 2=2 x.expense_account<=:expense_account_to LOV GL Account F26C78167A224EFBE0530100007F336B Y select distinct substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select fifs.id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.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 ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Expense Account To 9 80 2=2 x.cost_center>=:cost_center_from LOV GL Cost Center C2E95E107C034779E0530100007F5F55 Y select distinct substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select fifs.id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='FA_COST_CTR' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.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 ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Cost Center From 10 90 2=2 x.cost_center<=:cost_center_to LOV GL Cost Center C2E95E107C034779E0530100007F5F55 Y select distinct substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select fifs.id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='FA_COST_CTR' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.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 ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'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(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Cost Center To 11 100 2=2 x.major_category>=:major_category_from Char US Major Category From 12 110 2=2 x.major_category<=:major_category_to Char US Major Category To 13 120 2=2 x.minor_category>=:minor_category_from Char US Minor Category From 14 130 2=2 x.minor_category<=:minor_category_to Char US Minor Category To 15 140 :category_segment_number LOV FA Category Segment 9F68E821F72745B6BCB4C8BB0B267E64 Y select fifsv.segment_num id, fifsv.segment_name value, fifsv.description from fnd_id_flex_segments_vl fifsv where fifsv.application_id=140 and fifsv.id_flex_code='CAT#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fsc.category_flex_structure from fa_system_controls fsc) order by fifsv.segment_num US Category Segment Category flexfield segment to report in the Category Segment column and to filter with Category Segment Value From/To. Major Category and Minor Category have their own columns and do not need it. 16 150 2=2 x.category_segment>=:category_segment_value_from Char US Category Segment Value From Filters the segment chosen under Category Segment, and does nothing while that is empty. 17 160 2=2 x.category_segment<=:category_segment_value_to Char US Category Segment Value To 18 170 1=1 fcv.property_type_code=:property_type LOV Oracle FA_PROPERTY_TYPE Y select lookup_code id, description value, null description from fa_lookups where lookup_type='PROPERTY TYPE' order by description US Property Type 19 180 1=1 fa.asset_number>=:asset_number_from LOV FA Asset Number 53CBABC9B7B42FBCE0630100007F5445 Y Y select fa.asset_number value, fa.description description from fa_additions fa, fa_books fb where fa.asset_id=fb.asset_id and fb.date_ineffective is null and fb.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and (:$flex$.ledger is null or fb.book_type_code in (select fbcs.book_type_code from gl_ledgers gl, fa_book_controls_sec fbcs where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id)) and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fb.book_type_code)='Y') order by fa.asset_number US Asset Number From 20 190 1=1 fa.asset_number<=:asset_number_to LOV FA Asset Number 53CBABC9B7B42FBCE0630100007F5445 Y Y select fa.asset_number value, fa.description description from fa_additions fa, fa_books fb where fa.asset_id=fb.asset_id and fb.date_ineffective is null and fb.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs) and (:$flex$.ledger is null or fb.book_type_code in (select fbcs.book_type_code from gl_ledgers gl, fa_book_controls_sec fbcs where xxen_util.contains(:$flex$.ledger,gl.name)='Y' and gl.ledger_id=fbcs.set_of_books_id)) and (:$flex$.book is null or xxen_util.contains(:$flex$.book,fb.book_type_code)='Y') order by fa.asset_number US Asset Number To 210E77C518594344826EF72886529249 Book Details Layout of the Book Details attribute set of the Oracle RXi: Fixed Assets Book report. SYSADMIN 13 ADJUSTED_RATE 12 ASSET_DESCRIPTION 11 ASSET_NUMBER 3 BALANCING_SEGMENT 3 1 BOOK 1 16 COST 6 COST_CENTER 6 7 COST_CENTER_DESCRIPTION 7 15 DATE_PLACED_IN_SERVICE 17 DEPRECIATION_AMOUNT 14 DEPRECIATION_METHOD 8 EXPENSE_ACCOUNT 8 9 EXPENSE_ACCOUNT_DESCRIPTION 9 19 LTD_DEPRECIATION 4 MAJOR_CATEGORY 4 5 MINOR_CATEGORY 5 20 PERCENT 2 PERIOD 2 10 RESERVE_ACCOUNT 18 YTD_DEPRECIATION S Site 13E16308537F4D13BCA9720F404E90A2 Default Layout of the Default attribute set of the Oracle RXi: Fixed Assets Book report. SYSADMIN 6 ASSET_CATEGORY 6 8 ASSET_DESCRIPTION 7 ASSET_NUMBER 3 BALANCING_SEGMENT 3 1 BOOK 1 9 COST 5 COST_CENTER 5 13 DATE_PLACED_IN_SERVICE 12 DEPRECIATION_METHOD 4 EXPENSE_ACCOUNT 4 10 NBV_AT_PERIOD_END 2 PERIOD 2 11 UNITS 14 YTD_DEPRECIATION S Site 13E16308537F4D13BCA9720F404E90A2