ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US CA2F0F46793FA3A4E0530100007F0C73 with -- Not using fa_balances_reports_gt as it does not have enough columns -- to cater for running the reports across multiple book type codes and balance types (COST + RESERVE) fa_balances_report_q as ( select mfq.char1 book_type_code , mfq.char2 balance_type , mfq.number1 asset_id , mfq.number2 distribution_ccid , mfq.number3 adjustment_ccid , mfq.char3 category_books_account , mfq.char4 source_type_code , mfq.number4 amount , mfq.char5 cost_account , mfq.number5 cost_begin_balance , mfq.number6 group_asset_id , mfq.number7 set_of_books_id , mfq.number8 impairment_amt , mfq.number9 impairment_beg_rsv , mfq.number10 impairment_end_rsv , mfq.char6 impairment_beg_rsv_acct , mfq.char7 impairment_end_rsv_acct from msc_form_query mfq ) -- -- Main Query -- select x.company_name company_name, x.ledger ledger, x.book book, x.currency currency, x.balancing_segment balancing_segment, x.reserve_account reserve_account, x.cost_center cost_center, x.asset_number asset_number, x.asset_description asset_description, x.begin_amount beginning_balance, x.addition_amount additions, x.depreciation_amount depreciation, x.adjustment_amount adjustments, x.retirement_amount retirements, x.reclass_amount reclassifications, x.transfer_amount transfers, x.end_amount ending_balance, x.out_of_balance_flag out_of_balance_flag, case nvl(x.end_amount,0) - (nvl(x.begin_amount,0) + nvl(x.addition_amount,0) + nvl(x.depreciation_amount,0) + nvl(x.reclass_amount,0) - nvl(x.retirement_amount,0) + nvl(x.adjustment_amount,0) + nvl(x.transfer_amount,0)) when 0 then to_number(null) else nvl(x.end_amount,0) - (nvl(x.begin_amount,0) + nvl(x.addition_amount,0) + nvl(x.depreciation_amount,0) + nvl(x.reclass_amount,0) - nvl(x.retirement_amount,0) + nvl(x.adjustment_amount,0) + nvl(x.transfer_amount,0)) end out_of_balance_amount, -- case x.reval_tax_flag when 'R' then 'Revaluation Adjustment' when 'T' then 'Tax Adjustment' when 'B' then 'Revaluation and Tax Adjustments' end adjustment_type, case fa_fasrsves_xmlp_pkg.reval_tax_flagformula ( sum(x.revaluation_amount) over (partition by x.book,x.balancing_segment,x.reserve_account,x.cost_center) , sum(x.tax_amount) over (partition by x.book,x.balancing_segment,x.reserve_account,x.cost_center) ) when 'R' then 'Revaluation Adjustment' when 'T' then 'Tax Adjustment' when 'B' then 'Revaluation and Tax Adjustments' end cost_ctr_adj_type, -- -- Impairments -- x.begin_impairment, x.impairment, x.end_impairment, x.impair_reserve_acct impairment_reserve_account, case when x.impair_reserve_acct != x.impair_reserve_acct_old then '*' else null end impairment_rsv_acct_chg_flag, -- x.company_name || ': ' || x.book || ' (' || x.currency || ')' comp_book_curr_label from ( select fsc.company_name, gl.name ledger, fbrg.book_type_code book, gl.currency_code currency, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg','SQLGL','GL#',gcc_dh.chart_of_accounts_id,null,gcc_dh.code_combination_id,'GL_BALANCING','Y','VALUE') balancing_segment, nvl2(gcc_aj.code_combination_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg','SQLGL','GL#',gcc_aj.chart_of_accounts_id,null,gcc_aj.code_combination_id,'GL_ACCOUNT','Y','VALUE'), fbrg.category_books_account ) reserve_account, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gcc_dh.chart_of_accounts_id,null,gcc_dh.code_combination_id,'FA_COST_CTR','Y','VALUE') cost_center, fa.asset_number, fa.description asset_description, nvl(round(sum(decode(fbrg.source_type_code, 'BEGIN' , nvl(fbrg.amount,0) , null)), fc.precision),0) begin_amount, round(sum(decode(fbrg.source_type_code, 'ADDITION' , nvl(fbrg.amount,0) , null)), fc.precision) addition_amount, round(sum(decode(fbrg.source_type_code, 'DEPRECIATION', nvl(fbrg.amount,0) , null)), fc.precision) depreciation_amount, round(sum(decode(fbrg.source_type_code, 'REVALUATION' , nvl(fbrg.amount,0) , null)), fc.precision) revaluation_amount, round(sum(decode(fbrg.source_type_code, 'TAX' , nvl(fbrg.amount,0) , null)), fc.precision) tax_amount, round(sum(decode(fbrg.source_type_code, 'RETIREMENT' , -nvl(fbrg.amount,0), null)), fc.precision) retirement_amount, round(sum(decode(fbrg.source_type_code, 'RECLASS' , nvl(fbrg.amount,0) , null)), fc.precision) reclass_amount, round(sum(decode(fbrg.source_type_code, 'TRANSFER' , nvl(fbrg.amount,0) , null)), fc.precision) transfer_amount, nvl(round(sum(decode(fbrg.source_type_code, 'END' , nvl(fbrg.amount,0) , null)), fc.precision),0) end_amount, -- nvl(round(sum(decode(fbrg.source_type_code, 'TAX' , nvl(fbrg.amount,0),null)), fc.precision),0) + nvl(round(sum(decode(fbrg.source_type_code, 'REVALUATION' , nvl(fbrg.amount,0),null)), fc.precision),0) adjustment_amount, -- fa_fasrsves_xmlp_pkg.out_of_balanceformula ( nvl(round(sum(decode(fbrg.source_type_code, 'BEGIN' , nvl(fbrg.amount,0) , null)), fc.precision),0) , round(sum(decode(fbrg.source_type_code, 'ADDITION' , nvl(fbrg.amount,0) , null)), fc.precision) , round(sum(decode(fbrg.source_type_code, 'DEPRECIATION', nvl(fbrg.amount,0) , null)), fc.precision) , round(sum(decode(fbrg.source_type_code, 'RECLASS' , nvl(fbrg.amount,0) , null)), fc.precision) , round(sum(decode(fbrg.source_type_code, 'RETIREMENT' , - nvl(fbrg.amount,0), null)), fc.precision) , nvl(round(sum(decode(fbrg.source_type_code, 'TAX' , nvl(fbrg.amount,0),null)), fc.precision),0) + nvl(round(sum(decode(fbrg.source_type_code, 'REVALUATION' , nvl(fbrg.amount,0),null)), fc.precision),0) , round(sum(decode(fbrg.source_type_code, 'TRANSFER' , nvl(fbrg.amount,0) , null)), fc.precision) , nvl(round(sum(decode(fbrg.source_type_code, 'END' , nvl(fbrg.amount,0) , null)), fc.precision),0) ) out_of_balance_flag, fa_fasrsves_xmlp_pkg.reval_tax_flagformula ( round(sum(decode(fbrg.source_type_code, 'REVALUATION', nvl(fbrg.amount,0), null)), fc.precision) , round(sum(decode(fbrg.source_type_code, 'TAX' , nvl(fbrg.amount,0), null)), fc.precision) ) reval_tax_flag, -- -- Impairments -- nvl(round(sum(decode(fbrg.source_type_code,'BEGIN',nvl(fbrg.impairment_beg_rsv,0) , null)), fc.precision),0) begin_impairment, nvl(round(sum(decode(fbrg.source_type_code,'DEPRECIATION',nvl(fbrg.impairment_amt,0) , null)), fc.precision),0) impairment, nvl(round(sum(decode(fbrg.source_type_code,'END',nvl(fbrg.impairment_end_rsv,0) , null)), fc.precision),0) end_impairment, max(decode(fbrg.source_type_code,'BEGIN',fbrg.impairment_beg_rsv_acct)) impair_reserve_acct_old, max(decode(fbrg.source_type_code,'END',fbrg.impairment_end_rsv_acct)) impair_reserve_acct from fa_system_controls fsc, gl_ledgers gl, fnd_currencies fc, fa_balances_report_q fbrg, fa_additions fa, gl_code_combinations gcc_dh, gl_code_combinations gcc_aj where gl.ledger_id = fbrg.set_of_books_id and fc.currency_code = gl.currency_code and fa.asset_id = fbrg.asset_id and gcc_dh.code_combination_id = fbrg.distribution_ccid and gcc_aj.code_combination_id (+) = fbrg.adjustment_ccid and 1=1 group by fsc.company_name, gl.name, fbrg.book_type_code, gl.currency_code, fc.precision, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg','SQLGL','GL#',gcc_dh.chart_of_accounts_id,null,gcc_dh.code_combination_id,'GL_BALANCING','Y','VALUE'), nvl2(gcc_aj.code_combination_id, fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg','SQLGL','GL#',gcc_aj.chart_of_accounts_id,null,gcc_aj.code_combination_id,'GL_ACCOUNT','Y','VALUE'), fbrg.category_books_account ), fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg','SQLGL','GL#',gcc_dh.chart_of_accounts_id,null,gcc_dh.code_combination_id,'FA_COST_CTR','Y','VALUE'), fa.asset_number, fa.description ) x order by x.company_name, x.ledger, x.book, x.balancing_segment, x.reserve_account, x.cost_center, x.asset_number Y #,##0.00;[Red]-#,##0.00 XXEN_FA_FAS_XMLP ( :Set_of_Books is not null or :Book is not null ) US FA Depreciation Reserve Depreciation Reserve Summary/Detail Report Equivalent to Oracle Standard Reports: Reserve Summary Report Reserve Detail Report DB package: XXEN_FA_FAS_XMLP Please enter a Set of Books and/or an Asset Book Enginatics R12 only 1=1 :p_book :p_book1 :p_book2 :p_book3 :p_book4 :p_ca_set_of_books_id :p_calendar_type :p_conc_request_id :p_currency :p_fas_code :p_min_precision :p_mrcsobtype :p_period1 :p_period2 :p_period_count :p_period_name :p_report_type :p_run_id :p_show_cost :p_show_impairments :p_show_reserve 1 -100 :p_fas_code Char FASRSVES F Code FAS US FAS Code 2 10 :p_ca_set_of_books_id LOV custom Y select gsob.set_of_books_id id, gsob.name || ' - ' || gsob.currency_code value, null description from gl_sets_of_books gsob where gsob.set_of_books_id in (select fbcs.set_of_books_id from fa_book_controls_sec fbcs union all select fmbc.set_of_books_id from fa_book_controls_sec fbcs, fa_mc_book_controls fmbc where fbcs.book_type_code = fmbc.book_type_code ) order by value select nvl(xxen_util.previous_parameter_value(:parameter_id),fnd_profile.value('GL_SET_OF_BKS_ID')) from dual AR الدفتر نوع الدفتر D AfA-Buch Art AfA-Buch E Libro Tipo de Libro F Livre Type de livre I Registro Tipo di registro JA 台帳 台帳タイプ KO 장부 장부 유형 PTB Livro Tipo de Livro RU Книга Тип книги S Bok Boktyp TR Defter Defter Tipi US Set of Books Set of Books ZHS 帐簿 账簿类型 3 20 1=1 fbrg.book_type_code=:p_book LOV custom Y select fbcs.book_type_code id, fbcs.book_type_code value, fbcs.book_type_name description from fa_book_controls_sec fbcs where (:$flex$.set_of_books is null or to_char(fbcs.set_of_books_id) = :$flex$.set_of_books or exists (select null from fa_mc_book_controls fmbc where fmbc.book_type_code=fbcs.book_type_code and to_char(fmbc.set_of_books_id) = :$flex$.set_of_books) ) order by value xxen_util.previous_parameter_value(:parameter_id) AR عملة دفاتر الحسابات عملة دفاتر الحسابات D Währung Buchungsstruktur Währung Buchungsstruktur E Divisa de Juego de Libros Divisa de Juego de Libros F Ensemble de livres Monnaie Devise de l'entité comptable I Divisa set of books Divisa set of books JA 会計帳簿通貨 会計帳簿通貨 KO 회계장부 통화 회계장부 통화 PTB Moeda do Conjunto de Livros Moeda do Conjunto de Livros RU Валюта набора книг Валюта набора книг S Räkenskapsvaluta Räkenskapsvaluta TR Yasal Muhasebe Birimi Para Birimi Yasal Muhasebe Birimi Para Birimi US Book Book Type ZHS 帐套币种 帐套币种 4 30 :p_period1 LOV custom Y select distinct fdp.period_name id, fdp.period_name value, null description, fdp.period_counter from fa_deprn_periods fdp where (xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y' or (:$flex$.book is null and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs where fbcs.set_of_books_id = :$flex$.set_of_books union select fbcs.book_type_code from fa_book_controls_sec fbcs, fa_mc_book_controls fmbc where fmbc.book_type_code=fbcs.book_type_code and fmbc.set_of_books_id = :$flex$.set_of_books ) ) ) and fdp.period_counter> ( select min(fdp2.period_counter) from fa_deprn_periods fdp2 where (xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y' or (:$flex$.book is null and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs where fbcs.set_of_books_id = :$flex$.set_of_books union select fbcs.book_type_code from fa_book_controls_sec fbcs, fa_mc_book_controls fmbc where fmbc.book_type_code=fbcs.book_type_code and fmbc.set_of_books_id = :$flex$.set_of_books ) ) ) ) order by period_counter desc select fdp.period_name from fa_deprn_periods fdp where xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y' and fdp.period_counter= (select max(fdp2.period_counter) from fa_deprn_periods fdp2 where xxen_util.contains(:$flex$.book,fdp2.book_type_code)='Y' and fdp2.period_close_date is not null ) Y AR من الفترة فترة البداية D Von Periode Von Periode E De Período Período de Inicio F De la période Période de début I Periodo - Da Periodo iniziale JA 期間:自 期間:自 KO 기간: 부터 시작 기간 PTB Período - De Período Inicial RU Начальный период Начальный период S Från period Startperiod TR İlk Dönem Başlangıç Dönemi US From Period Start Period ZHS 自期间 起始期间 5 40 :p_period2 LOV custom Y select distinct fdp.period_name id, fdp.period_name value, null description, fdp.period_counter from fa_deprn_periods fdp where (xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y' or (:$flex$.book is null and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs where fbcs.set_of_books_id = :$flex$.set_of_books union select fbcs.book_type_code from fa_book_controls_sec fbcs, fa_mc_book_controls fmbc where fmbc.book_type_code=fbcs.book_type_code and fmbc.set_of_books_id = :$flex$.set_of_books ) ) ) and fdp.period_counter >= ( select distinct fdp2.period_counter from fa_deprn_periods fdp2 where (xxen_util.contains(:$flex$.book,fdp.book_type_code)='Y' or (:$flex$.book is null and fdp.book_type_code in (select fbcs.book_type_code from fa_book_controls_sec fbcs where fbcs.set_of_books_id = :$flex$.set_of_books union select fbcs.book_type_code from fa_book_controls_sec fbcs, fa_mc_book_controls fmbc where fmbc.book_type_code=fbcs.book_type_code and fmbc.set_of_books_id = :$flex$.set_of_books ) ) ) and fdp2.period_name= :$flex$.from_period ) order by period_counter desc :$flex$.from_period Y AR إلى الفترة فترة النهاية D Bis Periode Bis Periode E A Período Período de Finalización F À la période Fin de période I Periodo - A Periodo finale JA 期間:至 期間:至 KO 기간: 까지 종료 기간 PTB Período - Até Per. Final RU Конечный период Конечный период S Till period Slutperiod TR Son Dönem Bitiş Dönemi US To Period End Period ZHS 至期间 终止期间 6 50 :p_show_impairments LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual F Afficher les dépréciations US Show Impairments :$flex$.book Book From Period :$flex$.book Book To Period :$flex$.from_period From Period To Period :$flex$.set_of_books Set of Books Book :$flex$.set_of_books Set of Books From Period :$flex$.set_of_books Set of Books To Period Pivot: Default Template Reserve Summary (Pivot) with Reserve Details by Asset SYSADMIN 11 ADDITIONS 13 ADJUSTMENTS 20 ADJUSTMENT_TYPE 9 ASSET_DESCRIPTION 8 ASSET_NUMBER 7 5 BALANCING_SEGMENT 4 10 BEGINNING_BALANCE 3 BOOK 3 1 COMPANY_NAME 1 22 COMP_BOOK_CURR_LABEL 7 COST_CENTER 6 21 COST_CTR_ADJ_TYPE 4 CURRENCY 12 DEPRECIATION 17 ENDING_BALANCE 2 LEDGER 2 19 OUT_OF_BALANCE_AMOUNT 18 OUT_OF_BALANCE_FLAG 15 RECLASSIFICATIONS 6 RESERVE_ACCOUNT 5 14 RETIREMENTS 16 TRANSFERS ADDITIONS VALUE 2 SUM ADJUSTMENTS VALUE 4 SUM BALANCING_SEGMENT ROW 2 BEGINNING_BALANCE VALUE 1 SUM COMP_BOOK_CURR_LABEL ROW 1 COST_CENTER ROW 4 DEPRECIATION VALUE 3 SUM ENDING_BALANCE VALUE 8 SUM OUT_OF_BALANCE_AMOUNT VALUE 9 SUM RECLASSIFICATIONS VALUE 6 SUM RESERVE_ACCOUNT ROW 3 RETIREMENTS VALUE 5 SUM TRANSFERS VALUE 7 SUM S Site Pivot: Reserve Summary with Impairments Reserve Summary (Pivot) with Reserve Details by Asset including Impairments SYSADMIN 11 ADDITIONS 13 ADJUSTMENTS 20 ADJUSTMENT_TYPE 9 ASSET_DESCRIPTION 8 ASSET_NUMBER 8 5 BALANCING_SEGMENT 4 10 BEGINNING_BALANCE 23 BEGIN_IMPAIRMENT 3 BOOK 3 1 COMPANY_NAME 1 27 COMP_BOOK_CURR_LABEL 7 COST_CENTER 7 21 COST_CTR_ADJ_TYPE 4 CURRENCY 12 DEPRECIATION 17 ENDING_BALANCE 25 END_IMPAIRMENT 24 IMPAIRMENT 22 IMPAIRMENT_RESERVE_ACCOUNT 26 IMPAIRMENT_RSV_ACCT_CHG_FLAG -6.0007 IMPAIR_RESERVE_ACCT 6 2 LEDGER 2 19 OUT_OF_BALANCE_AMOUNT 18 OUT_OF_BALANCE_FLAG 15 RECLASSIFICATIONS 6 RESERVE_ACCOUNT 5 14 RETIREMENTS 16 TRANSFERS ADDITIONS VALUE 2 SUM ADJUSTMENTS VALUE 4 SUM BALANCING_SEGMENT ROW 2 BEGINNING_BALANCE VALUE 1 SUM BEGIN_IMPAIRMENT VALUE 10 SUM COMP_BOOK_CURR_LABEL ROW 1 COST_CENTER ROW 5 DEPRECIATION VALUE 3 SUM ENDING_BALANCE VALUE 8 SUM END_IMPAIRMENT VALUE 12 SUM IMPAIRMENT VALUE 11 SUM IMPAIRMENT_RESERVE_ACCOUNT ROW 4 IMPAIR_RESERVE_ACCT ROW 4 OUT_OF_BALANCE_AMOUNT VALUE 9 SUM RECLASSIFICATIONS VALUE 6 SUM RESERVE_ACCOUNT ROW 3 RETIREMENTS VALUE 5 SUM TRANSFERS VALUE 7 SUM Show Impairments Y S Site Reserve Detail Reserve Detail Report by Asset SYSADMIN 11 ADDITIONS 13 ADJUSTMENTS 20 ADJUSTMENT_TYPE 9 ASSET_DESCRIPTION 8 ASSET_NUMBER 7 5 BALANCING_SEGMENT 4 10 BEGINNING_BALANCE 23 BEGIN_IMPAIRMENT 3 BOOK 3 1 COMPANY_NAME 1 Y 27 COMP_BOOK_CURR_LABEL 7 COST_CENTER 6 21 COST_CTR_ADJ_TYPE 4 CURRENCY 12 DEPRECIATION 17 ENDING_BALANCE 25 END_IMPAIRMENT 24 IMPAIRMENT 22 IMPAIRMENT_RESERVE_ACCOUNT 26 IMPAIRMENT_RSV_ACCT_CHG_FLAG -21.0022 IMPAIR_RESERVE_ACCT 2 LEDGER 2 19 OUT_OF_BALANCE_AMOUNT 18 OUT_OF_BALANCE_FLAG 15 RECLASSIFICATIONS 6 RESERVE_ACCOUNT 5 14 RETIREMENTS 16 TRANSFERS S Site Reserve Summary Reserve Summary Report by Balancing Segment, Reserve Account, and Cost Center SYSADMIN 11 ADDITIONS SUM 13 ADJUSTMENTS SUM 20 ADJUSTMENT_TYPE MAX 9 ASSET_DESCRIPTION 8 ASSET_NUMBER 7 5 BALANCING_SEGMENT 4 10 BEGINNING_BALANCE SUM 23 BEGIN_IMPAIRMENT SUM 3 BOOK 3 1 COMPANY_NAME 1 Y 27 COMP_BOOK_CURR_LABEL 7 COST_CENTER 6 21 COST_CTR_ADJ_TYPE MAX 4 CURRENCY 12 DEPRECIATION SUM 17 ENDING_BALANCE SUM 25 END_IMPAIRMENT SUM 24 IMPAIRMENT SUM 22 IMPAIRMENT_RESERVE_ACCOUNT 26 IMPAIRMENT_RSV_ACCT_CHG_FLAG MAX -21.0022 IMPAIR_RESERVE_ACCT 2 LEDGER 2 19 OUT_OF_BALANCE_AMOUNT SUM 18 OUT_OF_BALANCE_FLAG MAX 15 RECLASSIFICATIONS SUM 6 RESERVE_ACCOUNT 5 14 RETIREMENTS SUM 16 TRANSFERS SUM S Site SYSADMIN Reserve Summary ERIC.CLEGG Pivot: Reserve Summary with Impairments Pivot: Default Template