select a.category_flex,
b.concatenated_segments reval_amortization_account,
c.concatenated_segments impair_expense_account,
d.concatenated_segments impair_reserve_account,
e.concatenated_segments capital_adj_account,
f.concatenated_segments general_fund_account,
decode(a.validation_flag,'Y','SUCCESS:Category has qualified for UK Local Authority Accounting Category Implementation',
'R','WARNING:Revaluation Amortization account should be the same as Capital Adjustment account',
'ERROR:Impairment Expense account or Accumulated Impairment account not assigned'
) status,
count(a.category_flex) category_count
from fa_sorp_upg_cat a,
GL_CODE_COMBINATIONS_KFV b,
GL_CODE_COMBINATIONS_KFV c,
GL_CODE_COMBINATIONS_KFV d,
GL_CODE_COMBINATIONS_KFV e,
GL_CODE_COMBINATIONS_KFV f
where a.reval_amort_account_ccid= b.code_combination_id(+) and
a.impair_expense_account_ccid = c.code_combination_id(+) and
a.impair_reserve_account_ccid = d.code_combination_id(+) and
a.capital_adj_account_ccid = e.code_combination_id(+) and
a.general_fund_account_ccid = f.code_combination_id(+) and
e.chart_of_accounts_id = :P_ACCT_FLEX_STRUCT and
a.book_type_code = :P_FA_BOOK
group by a.category_flex,
b.concatenated_segments,
c.concatenated_segments,
d.concatenated_segments,
e.concatenated_segments,
f.concatenated_segments,
a.validation_flag
order by a.validation_flag desc |