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 |
