Oracle Fusion · Control Reports
Asset Category Book Report
Control Reports
The depreciation defaults each asset category carries in each book — method, life, bonus rule, and prorate convention — so finance can confirm new assets will depreciate the way policy intends.
Sample build of the Asset Category Book Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Asset Category Book Report
Sample build · illustrativeFilters
Mi
Sample
Ss
Sample
148
Category-book rules
37
Categories
4
Missing defaults
| Category | Book | Depreciation Method | Life (months) | Bonus Rule |
|---|---|---|---|---|
| Computer-Hardware | CORP | Standard | 60 | Standard |
| Buildings | TAX FEDERAL | Corporate | 240 | Corporate |
| Vehicles | CORP | Standard | 84 | Standard |
| Furniture-Fixtures | IFRS | Default | 120 | Default |
| Machinery | TAX STATE | Standard | 36 | Standard |
| Computer-Hardware | CORP | Standard | 60 | Standard |
AI Analyst · active
reading
The report reads FA_CATEGORY_BOOKS with the methods in FA_METHODS and category names in FA_CATEGORIES_TL, showing the default method and life for every category-book combination.
flag
Four category-book pairs have no method default — assets added to those categories will not depreciate automatically and will sit at cost until corrected.
root cause & next step
Set the depreciation method and life on those four pairs in FA_CATEGORY_BOOKS so newly added assets pick up the policy default.
Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst —
runs on your warehouse. See it live →
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
- FA_CATEGORY_BOOKS
- FA_CATEGORIES_B
- FA_CATEGORIES_TL
- GL_CODE_COMBINATIONS
- FA_METHODS
- FA_BONUS_RULES
- FA_CONVENTION_TYPES
- FA_FLAT_RATES
- FA_CATEGORY_BOOK_DEFAULTS
How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.
Show / hide SQL
SELECT
BOOK_TYPE_CODE,
segment1,
segment2,
ENABLED_FLAG,
CATEGORY_TYPE,
DESCRIPTION,
BONUS_RULE,
PERCENT_SALVAGE_VALUE,
LIFE_IN_YEARS,
LIFE_IN_MONTHS,
PRORATE_CONVENTION_CODE,
RETIREMENT_CONVENTION_TYPE,
DEPRECIATE_FLAG,
REPLACE(NAME, CHR(10), ' ') as NAME,
MINIMUM_LIFE_IN_MONTHS,
DEFAULT_EXPENSE_CCID_FLAG,
ASSET_COST_CCID,
ASSET_CLEARING_ACCOUNT_CCID,
WIP_COST_ACCOUNT_CCID,
WIP_CLEARING_ACCOUNT_CCID,
DEPRN_EXPENSE_ACCOUNT_CCID,
RESERVE_ACCOUNT_CCID,
BONUS_EXPENSE_ACCOUNT_CCID,
BONUS_RESERVE_ACCT_CCID,
IMPAIR_EXPENSE_ACCOUNT_CCID,
IMPAIR_RESERVE_ACCOUNT_CCID,
TO_CHAR(CREATION_DATE,'dd-MM-YYYY HH24:MI:SS AM','NLS_DATE_LANGUAGE=English') Creation_Date,
Created_By,
TO_CHAR(LAST_UPDATE_DATE,'dd-MM-YYYY HH24:MI:SS AM','NLS_DATE_LANGUAGE=English') LAST_UPDATE_DATE,
LAST_UPDATED_BY
FROM
(
SELECT
FCB.BOOK_TYPE_CODE,
FCAT.segment1,
FCAT.segment2,
FCAT.ENABLED_FLAG,
FCAT.CATEGORY_TYPE,
FCT.DESCRIPTION,
FA_CAT_DEF.BONUS_RULE,
FA_CAT_DEF.PERCENT_SALVAGE_VALUE*100 PERCENT_SALVAGE_VALUE ,
CASE WHEN FA_CAT_DEF.METHOD_ID IS NULL
THEN FLOOR((FA_CAT_DEF.LIFE_IN_MONTHS)/12)
ELSE (FA_CAT_DEF.BASIC_RATE*100) END AS LIFE_IN_YEARS,
CASE WHEN FA_CAT_DEF.METHOD_ID IS NULL
THEN MOD(FA_CAT_DEF.LIFE_IN_MONTHS,12)
ELSE (FA_CAT_DEF.ADJUSTED_RATE*100) END AS LIFE_IN_MONTHS,
FA_CAT_DEF.PRORATE_CONVENTION_CODE,
FA_CAT_DEF.RETIREMENT_CONVENTION_TYPE,
FA_CAT_DEF.DEPRECIATE_FLAG,
FA_CAT_DEF.NAME,
FA_CAT_DEF.MINIMUM_LIFE_IN_MONTHS,
FCB.DEFAULT_EXPENSE_CCID_FLAG,
GCC2.CONCATENATED_SEGMENTS ASSET_COST_CCID,
GCC3.CONCATENATED_SEGMENTS ASSET_CLEARING_ACCOUNT_CCID,
GCC4.CONCATENATED_SEGMENTS WIP_COST_ACCOUNT_CCID,
GCC5.CONCATENATED_SEGMENTS WIP_CLEARING_ACCOUNT_CCID,
GCC6.CONCATENATED_SEGMENTS DEPRN_EXPENSE_ACCOUNT_CCID,
GCC7.CONCATENATED_SEGMENTS RESERVE_ACCOUNT_CCID,
GCC8.CONCATENATED_SEGMENTS BONUS_EXPENSE_ACCOUNT_CCID,
GCC9.CONCATENATED_SEGMENTS BONUS_RESERVE_ACCT_CCID,
GCC16.CONCATENATED_SEGMENTS IMPAIR_EXPENSE_ACCOUNT_CCID,
GCC17.CONCATENATED_SEGMENTS IMPAIR_RESERVE_ACCOUNT_CCID,
FCB.Creation_Date,
FCB.Created_By,
FCB.LAST_UPDATE_DATE,
FCB.LAST_UPDATED_BY
--FCB.CATEGORY_ID
FROM
FA_CATEGORY_BOOKS FCB,
FA_CATEGORIES_B FCAT,
FA_CATEGORIES_TL FCT,
GL_CODE_COMBINATIONS GCC2,
GL_CODE_COMBINATIONS GCC3,
GL_CODE_COMBINATIONS GCC4,
GL_CODE_COMBINATIONS GCC5,
GL_CODE_COMBINATIONS GCC6,
GL_CODE_COMBINATIONS GCC7,
GL_CODE_COMBINATIONS GCC8,
GL_CODE_COMBINATIONS GCC9,
GL_CODE_COMBINATIONS GCC16,
GL_CODE_COMBINATIONS GCC17,
(SELECT
DISTINCT FCBD.CATEGORY_ID,
FCBD.DEPRECIATE_FLAG,
FCBD.MINIMUM_LIFE_IN_MONTHS,
FCBD.BOOK_TYPE_CODE,
FM.NAME,
FBR.BONUS_RULE,
FCBD.PERCENT_SALVAGE_VALUE,
FM.LIFE_IN_MONTHS,
FCT.PRORATE_CONVENTION_CODE,
FCT1.PRORATE_CONVENTION_CODE RETIREMENT_CONVENTION_TYPE,
FFR.METHOD_ID,
FFR.BASIC_RATE,
FFR.ADJUSTED_RATE
FROM
FA_CATEGORY_BOOK_DEFAULTS FCBD,
FA_METHODS FM,
FA_BONUS_RULES FBR,
FA_CONVENTION_TYPES FCT,
FA_CONVENTION_TYPES FCT1,
FA_FLAT_RATES FFR
WHERE 1=1
AND FCBD.METHOD_ID=FM.METHOD_ID(+)
AND FCBD.BONUS_RULE_ID=FBR.BONUS_RULE_ID(+)
AND FCBD.CONVENTION_TYPE_ID=FCT.CONVENTION_TYPE_ID(+)
AND FCBD.RETIREMENT_CONVENTION_TYPE_ID=FCT1.CONVENTION_TYPE_ID(+)
AND FCBD.METHOD_ID=FFR.METHOD_ID(+)
AND FCBD.FLAT_RATE_ID=FFR.FLAT_RATE_ID(+)
--AND FCBD.BOOK_TYPE_CODE='AU FA CORP BOOK AUD'
) FA_CAT_DEF
WHERE 1=1
AND FCB.CATEGORY_ID=FCAT.CATEGORY_ID
AND FCAT.CATEGORY_ID=FCT.CATEGORY_ID
AND FCT.language='US'
AND FCB.CATEGORY_ID=FA_CAT_DEF.CATEGORY_ID(+)
AND FCB.BOOK_TYPE_CODE=FA_CAT_DEF.BOOK_TYPE_CODE(+)
AND FCB.ASSET_COST_ACCOUNT_CCID=GCC2.CODE_COMBINATION_ID(+)
AND FCB.ASSET_CLEARING_ACCOUNT_CCID=GCC3.CODE_COMBINATION_ID(+)
AND FCB.WIP_COST_ACCOUNT_CCID=GCC4.CODE_COMBINATION_ID(+)
AND FCB.WIP_CLEARING_ACCOUNT_CCID=GCC5.CODE_COMBINATION_ID(+)
AND FCB.DEPRN_EXPENSE_ACCOUNT_CCID=GCC6.CODE_COMBINATION_ID(+)
AND FCB.RESERVE_ACCOUNT_CCID=GCC7.CODE_COMBINATION_ID(+)
AND FCB.BONUS_EXPENSE_ACCOUNT_CCID=GCC8.CODE_COMBINATION_ID(+)
AND FCB.BONUS_RESERVE_ACCT_CCID=GCC9.CODE_COMBINATION_ID(+)
AND FCB.IMPAIR_EXPENSE_ACCOUNT_CCID=GCC16.CODE_COMBINATION_ID(+)
AND FCB.IMPAIR_RESERVE_ACCOUNT_CCID=GCC17.CODE_COMBINATION_ID(+)
--and FCB.BOOK_TYPE_CODE='AU FA CORP BOOK AUD'
UNION
SELECT
'' BOOK_TYPE_CODE,
'' segment1,
'' segment2,
'No Data' ENABLED_FLAG,
'' CATEGORY_TYPE,
'' DESCRIPTION,
'' BONUS_RULE,
TO_NUMBER('') PERCENT_SALVAGE_VALUE,
TO_NUMBER('') LIFE_IN_YEARS,
TO_NUMBER('') LIFE_IN_MONTHS,
'' PRORATE_CONVENTION_CODE,
'' RETIREMENT_CONVENTION_TYPE,
'' DEPRECIATE_FLAG,
'' NAME,
TO_NUMBER('') MINIMUM_LIFE_IN_MONTHS,
'' DEFAULT_EXPENSE_CCID_FLAG,
'' ASSET_COST_CCID,
'' ASSET_CLEARING_ACCOUNT_CCID,
'' WIP_COST_ACCOUNT_CCID,
'' WIP_CLEARING_ACCOUNT_CCID,
'' DEPRN_EXPENSE_ACCOUNT_CCID,
'' RESERVE_ACCOUNT_CCID,
'' BONUS_EXPENSE_ACCOUNT_CCID,
'' BONUS_RESERVE_ACCT_CCID,
'' IMPAIR_EXPENSE_ACCOUNT_CCID,
'' IMPAIR_RESERVE_ACCOUNT_CCID,
TO_DATE('') Creation_Date,
'' Created_By,
TO_DATE('') LAST_UPDATE_DATE,
'' LAST_UPDATED_BY
FROM
FA_CATEGORY_BOOK_DEFAULTS
WHERE NOT EXISTS(
SELECT
FCB.BOOK_TYPE_CODE,
FCAT.segment1,
FCAT.segment2,
FCAT.ENABLED_FLAG,
FCAT.CATEGORY_TYPE,
FCT.DESCRIPTION,
FA_CAT_DEF.BONUS_RULE,
FA_CAT_DEF.PERCENT_SALVAGE_VALUE*100 PERCENT_SALVAGE_VALUE ,
CASE WHEN FA_CAT_DEF.METHOD_ID IS NULL
THEN FLOOR((FA_CAT_DEF.LIFE_IN_MONTHS)/12)
ELSE (FA_CAT_DEF.BASIC_RATE*100) END AS LIFE_IN_YEARS,
CASE WHEN FA_CAT_DEF.METHOD_ID IS NULL
THEN MOD(FA_CAT_DEF.LIFE_IN_MONTHS,12)
ELSE (FA_CAT_DEF.ADJUSTED_RATE*100) END AS LIFE_IN_MONTHS,
FA_CAT_DEF.PRORATE_CONVENTION_CODE,
FA_CAT_DEF.RETIREMENT_CONVENTION_TYPE,
FA_CAT_DEF.DEPRECIATE_FLAG,
FA_CAT_DEF.NAME,
FA_CAT_DEF.MINIMUM_LIFE_IN_MONTHS,
FCB.DEFAULT_EXPENSE_CCID_FLAG,
GCC2.CONCATENATED_SEGMENTS ASSET_COST_CCID,
GCC3.CONCATENATED_SEGMENTS ASSET_CLEARING_ACCOUNT_CCID,
GCC4.CONCATENATED_SEGMENTS WIP_COST_ACCOUNT_CCID,
GCC5.CONCATENATED_SEGMENTS WIP_CLEARING_ACCOUNT_CCID,
GCC6.CONCATENATED_SEGMENTS DEPRN_EXPENSE_ACCOUNT_CCID,
GCC7.CONCATENATED_SEGMENTS RESERVE_ACCOUNT_CCID,
GCC8.CONCATENATED_SEGMENTS BONUS_EXPENSE_ACCOUNT_CCID,
GCC9.CONCATENATED_SEGMENTS BONUS_RESERVE_ACCT_CCID,
GCC16.CONCATENATED_SEGMENTS IMPAIR_EXPENSE_ACCOUNT_CCID,
GCC17.CONCATENATED_SEGMENTS IMPAIR_RESERVE_ACCOUNT_CCID,
FCB.Creation_Date,
FCB.Created_By,
FCB.LAST_UPDATE_DATE,
FCB.LAST_UPDATED_BY
--FCB.CATEGORY_ID
FROM
FA_CATEGORY_BOOKS FCB,
FA_CATEGORIES_B FCAT,
FA_CATEGORIES_TL FCT,
GL_CODE_COMBINATIONS GCC2,
GL_CODE_COMBINATIONS GCC3,
GL_CODE_COMBINATIONS GCC4,
GL_CODE_COMBINATIONS GCC5,
GL_CODE_COMBINATIONS GCC6,
GL_CODE_COMBINATIONS GCC7,
GL_CODE_COMBINATIONS GCC8,
GL_CODE_COMBINATIONS GCC9,
GL_CODE_COMBINATIONS GCC16,
GL_CODE_COMBINATIONS GCC17,
(SELECT
DISTINCT FCBD.CATEGORY_ID,
FCBD.DEPRECIATE_FLAG,
FCBD.MINIMUM_LIFE_IN_MONTHS,
FCBD.BOOK_TYPE_CODE,
FM.NAME,
FBR.BONUS_RULE,
FCBD.PERCENT_SALVAGE_VALUE,
FM.LIFE_IN_MONTHS,
FCT.PRORATE_CONVENTION_CODE,
FCT1.PRORATE_CONVENTION_CODE RETIREMENT_CONVENTION_TYPE,
FFR.METHOD_ID,
FFR.BASIC_RATE,
FFR.ADJUSTED_RATE
FROM
FA_CATEGORY_BOOK_DEFAULTS FCBD,
FA_METHODS FM,
FA_BONUS_RULES FBR,
FA_CONVENTION_TYPES FCT,
FA_CONVENTION_TYPES FCT1,
FA_FLAT_RATES FFR
WHERE 1=1
AND FCBD.METHOD_ID=FM.METHOD_ID(+)
AND FCBD.BONUS_RULE_ID=FBR.BONUS_RULE_ID(+)
AND FCBD.CONVENTION_TYPE_ID=FCT.CONVENTION_TYPE_ID(+)
AND FCBD.RETIREMENT_CONVENTION_TYPE_ID=FCT1.CONVENTION_TYPE_ID(+)
AND FCBD.METHOD_ID=FFR.METHOD_ID(+)
AND FCBD.FLAT_RATE_ID=FFR.FLAT_RATE_ID(+)
--AND FCBD.BOOK_TYPE_CODE='AU FA CORP BOOK AUD'
) FA_CAT_DEF
WHERE 1=1
AND FCB.CATEGORY_ID=FCAT.CATEGORY_ID
AND FCAT.CATEGORY_ID=FCT.CATEGORY_ID
AND FCT.language='US'
AND FCB.CATEGORY_ID=FA_CAT_DEF.CATEGORY_ID(+)
AND FCB.BOOK_TYPE_CODE=FA_CAT_DEF.BOOK_TYPE_CODE(+)
AND FCB.ASSET_COST_ACCOUNT_CCID=GCC2.CODE_COMBINATION_ID(+)
AND FCB.ASSET_CLEARING_ACCOUNT_CCID=GCC3.CODE_COMBINATION_ID(+)
AND FCB.WIP_COST_ACCOUNT_CCID=GCC4.CODE_COMBINATION_ID(+)
AND FCB.WIP_CLEARING_ACCOUNT_CCID=GCC5.CODE_COMBINATION_ID(+)
AND FCB.DEPRN_EXPENSE_ACCOUNT_CCID=GCC6.CODE_COMBINATION_ID(+)
AND FCB.RESERVE_ACCOUNT_CCID=GCC7.CODE_COMBINATION_ID(+)
AND FCB.BONUS_EXPENSE_ACCOUNT_CCID=GCC8.CODE_COMBINATION_ID(+)
AND FCB.BONUS_RESERVE_ACCT_CCID=GCC9.CODE_COMBINATION_ID(+)
AND FCB.IMPAIR_EXPENSE_ACCOUNT_CCID=GCC16.CODE_COMBINATION_ID(+)
AND FCB.IMPAIR_RESERVE_ACCOUNT_CCID=GCC17.CODE_COMBINATION_ID(+)
)
)
WHERE 1=1
ORDER BY
BOOK_TYPE_CODE,
segment1,
segment2:MI :SS
The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
●— fact → dimension join
| Element | Type | Definition |
|---|---|---|
| FA_CATEGORIES_B | dimension | dimension |
| FA_CATEGORIES_TL | dimension | dimension |
| GL_CODE_COMBINATIONS | dimension | dimension |
| FA_METHODS | dimension | dimension |
| Amount | measure | measure |
Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.
How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →
See the complete model
How this report's fact and dimensions fit the full picture, via conformed keys.
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| FA_CATEGORY_BOOKS | Setup / configuration table — joined for reference, not exposed for analytics | |
| FA_CATEGORIES_B | 5 | 10 |
| FA_CATEGORIES_TL | 1 | 10 |
| GL_CODE_COMBINATIONS | 7 | 61 |
| FA_METHODS | 8 | 2 |
| FA_BONUS_RULES | 2 | 1 |
| FA_CONVENTION_TYPES | 2 | 2 |
| FA_FLAT_RATES | 3 | 1 |
| FA_CATEGORY_BOOK_DEFAULTS | Setup / configuration table — joined for reference, not exposed for analytics | |
Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.