IGI Inflation Accounting : Asset Revaluation Preview Summary - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Inflation Accounting : Asset Revaluation Preview Summary Report (XML) - Not Supported: Reserved For Future Use
Short Name: IGIIARPS_XML
DB package: IGI_IGIIARPS_XMLP_PKG
SELECT   BC.BOOK_TYPE_CODE BOOK_TYPE_CODE1,
           fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_company_flex_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')   COMPANY,
            fnd_flex_xml_publisher_apis.process_kff_combination_1('major_cat_flex', 'OFA', 'CAT#', 101, NULL, fc.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE')    MAJOR_CATEGORY,
          &MINOR_CAT_FLEX    MINOR_CATEGORY,
            FA.ASSET_CATEGORY_ID,
            fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_cost_flex_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')  COST_CENTER,
           BAL.DATA_SOURCE BALANCE_TYPE,
           BAL.DISPLAY_ORDER,
           NVL(SUM(DECODE(BAL.Source_Type_Code, 
	 	              'COST', NVL(BAL.Amount,0), NULL)), 0)         REVALUED_COST,
           NVL(SUM(DECODE(BAL.Source_Type_Code,
                      'REVAL_RSV', NVL(BAL.Amount,0), NULL)), 0)    REVALUATION_RESERVE,
           NVL(SUM(DECODE(BAL.Source_Type_Code,
                      'OP_EXPENSE', NVL(BAL.Amount,0), NULL)), 0)    OPERATING_ACCT,
           NVL(SUM(DECODE(BAL.Source_Type_Code,
                      'DEP_RSV', NVL(BAL.Amount,0), NULL)), 0)    ACCUMULATED_DEPRECIATION,
           NVL(SUM(DECODE(BAL.Source_Type_Code,
                      'BACKLOG', NVL(BAL.Amount,0), NULL)), 0)    ACCUMULATED_BACKLOG, 
	IGI_IGIIARPS_XMLP_PKG.cf_rep_toleranceformula(:s_rep_b_reval_cost, :s_rep_a_reval_cost) CF_rep_tolerance,
	IGI_IGIIARPS_XMLP_PKG.CP_rep_format_diff_p CP_rep_format_diff, 
	IGI_IGIIARPS_XMLP_PKG.cf_co_toleranceformula(:s_co_b_reval_cost, :s_co_a_reval_cost) CF_co_tolerance,
	IGI_IGIIARPS_XMLP_PKG.CP_co_format_diff_p CP_co_format_diff, 
	IGI_IGIIARPS_XMLP_PKG.cf_mc_toleranceformula(:s_mc_b_reval_cost, :s_mc_a_reval_cost) CF_mc_tolerance,
	IGI_IGIIARPS_XMLP_PKG.CP_mc_format_diff_p CP_mc_format_diff, 
	IGI_IGIIARPS_XMLP_PKG.cf_major_catformula(fnd_flex_xml_publisher_apis.process_kff_combination_1('major_cat_flex', 'OFA', 'CAT#', 101, NULL, fc.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE')) CF_major_cat,
	IGI_IGIIARPS_XMLP_PKG.cf_cat_toleranceformula(:s_cat_b_reval_cost, :s_cat_a_reval_cost) CF_cat_tolerance,
IGI_IGIIARPS_XMLP_PKG.CF_calc_acct_valueFormula(FA.ASSET_CATEGORY_ID,fnd_flex_xml_publisher_apis.process_kff_combination_1('cp_account_segment', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')) CF_calc_acct_value,
		IGI_IGIIARPS_XMLP_PKG.CP_cost_acct_p CP_cost_acct,
		IGI_IGIIARPS_XMLP_PKG.CP_reval_res_acct_p CP_reval_res_acct,
		IGI_IGIIARPS_XMLP_PKG.CP_acc_deprn_acct_p CP_acc_deprn_acct,
		IGI_IGIIARPS_XMLP_PKG.CP_operating_acct_p CP_operating_acct,
		IGI_IGIIARPS_XMLP_PKG.CP_backlog_acct_p CP_backlog_acct, 
	IGI_IGIIARPS_XMLP_PKG.f_minor_category_p f_minor_category,
	IGI_IGIIARPS_XMLP_PKG.CP_major_category_p CP_major_category,
	IGI_IGIIARPS_XMLP_PKG.CP_cat_format_diff_p CP_cat_format_diff,
	IGI_IGIIARPS_XMLP_PKG.cf_cc_toleranceformula(:s_cc_b_reval_cost, :s_cc_a_reval_cost) CF_cc_tolerance,
	IGI_IGIIARPS_XMLP_PKG.CP_cc_format_diff_p CP_cc_format_diff,
	IGI_IGIIARPS_XMLP_PKG.CP_a_format_diff_p CP_a_format_diff, 
	IGI_IGIIARPS_XMLP_PKG.cf_calc_placeholdersformula() CF_calc_placeholders, 
	IGI_IGIIARPS_XMLP_PKG.b_revalued_costformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'COST' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) b_revalued_cost, 
	IGI_IGIIARPS_XMLP_PKG.a_revalued_costformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'COST' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) a_revalued_cost, 
	IGI_IGIIARPS_XMLP_PKG.b_reval_reserveformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'REVAL_RSV' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) b_reval_reserve, 
	IGI_IGIIARPS_XMLP_PKG.a_reval_reserveformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'REVAL_RSV' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) a_reval_reserve, 
	IGI_IGIIARPS_XMLP_PKG.b_operating_acctformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'OP_EXPENSE' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) b_operating_acct, 
	IGI_IGIIARPS_XMLP_PKG.a_operating_acctformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'OP_EXPENSE' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) a_operating_acct, 
	IGI_IGIIARPS_XMLP_PKG.b_acc_deprnformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'DEP_RSV' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) b_acc_deprn, 
	IGI_IGIIARPS_XMLP_PKG.a_acc_deprnformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'DEP_RSV' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) a_acc_deprn, 
	IGI_IGIIARPS_XMLP_PKG.b_acc_blogformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'BACKLOG' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) b_acc_blog, 
	IGI_IGIIARPS_XMLP_PKG.a_acc_blogformula(BAL.DATA_SOURCE, NVL ( SUM ( DECODE ( BAL.Source_Type_Code , 'BACKLOG' , NVL ( BAL.Amount , 0 ) , NULL ) ) , 0 )) a_acc_blog,
	IGI_IGIIARPS_XMLP_PKG.f_reval_reserve_diff_p f_reval_reserve_diff,
	IGI_IGIIARPS_XMLP_PKG.f_reval_cost_diff_p f_reval_cost_diff,
	IGI_IGIIARPS_XMLP_PKG.f_operating_acct_diff_p f_operating_acct_diff,
	IGI_IGIIARPS_XMLP_PKG.f_acc_deprn_diff_p f_acc_deprn_diff,
	IGI_IGIIARPS_XMLP_PKG.f_acc_backlog_diff_p f_acc_backlog_diff
FROM    IGI_IAC_BALANCES_REPORT BAL,
             FA_CATEGORIES FC,
             GL_CODE_COMBINATIONS CC,
             FA_DISTRIBUTION_HISTORY DH,
             FA_ADDITIONS FA,
             FA_BOOKS  BC
WHERE   BAL.ASSET_ID = DH.ASSET_ID
AND     BAL.distribution_ccid = DH.code_combination_id
AND     DH.code_combination_id = CC.code_combination_id
AND     DH.asset_id = FA.asset_id
AND     FA.asset_id = BAL.asset_id
AND     FA.asset_category_id = FC.category_id
AND     FA.asset_id = BC.asset_id
AND     BC.date_ineffective IS NULL
AND     BC.transaction_header_id_out IS NULL
AND     DH.date_ineffective IS NULL
AND     DH.transaction_header_id_out IS NULL
AND     :do_insert = 1 
 and BC.BOOK_TYPE_CODE=:book_type_code
GROUP BY BC.BOOK_TYPE_CODE,
                 fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_company_flex_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
                 fnd_flex_xml_publisher_apis.process_kff_combination_1('major_cat_flex', 'OFA', 'CAT#', 101, NULL, fc.CATEGORY_ID, 'BASED_CATEGORY', 'Y', 'VALUE'),
                 &MINOR_CAT_FLEX,
				 FA.ASSET_CATEGORY_ID,
                 fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_cost_flex_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
                 BAL.DATA_SOURCE,
                 BAL.DISPLAY_ORDER,
				fnd_flex_xml_publisher_apis.process_kff_combination_1('cp_account_segment', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')
ORDER BY 1,2,3,5,7