Asset Addition and Cost Adjustment Report

Fixed Assets

Asset additions and cost adjustments for a period — new assets capitalized and changes to existing asset cost — with who made each change, so finance can review capital activity and confirm adjustments are authorized.

Sample build of the Asset Addition and Cost Adjustment Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Period1

FEB-26

Period2

FEB-26

Book

CORP

Major Cat

Sample

Minor Cat

Sample

Xdo User Name

Globex Holdings

142

Additions

$6.40M

Capitalized

Adjustments

Asset Number	Transaction Type	Date	Cost Change	Adjusted Cost	Performed By
1001	Standard	2026-04-30	$1,240,500.00	$1,240,500.00	Sample
1002	Corporate	2026-03-31	$842,150.75	$842,150.75	—
1003	Standard	2026-02-28	$96,400.00	$96,400.00	Sample
1004	Default	2026-01-31	$1,005,233.10	$1,005,233.10	—
1005	Standard	2025-12-31	$58,720.40	$58,720.40	Sample
1001	Standard	2026-04-30	$1,240,500.00	$1,240,500.00	Sample

AI Analyst · active

reading

The report reads FA_ADJUSTMENTS with the transaction and user tables, separating additions from cost adjustments in the period.

flag

28 cost adjustments were posted after initial capitalization, several by the same user without a second approver — cost adjustments change depreciation and are an audit focus.

root cause & next step

Confirm the adjustments had a reason and approval; if segregation of duties is weak, add an approval step on FA cost adjustments.

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

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.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

<![CDATA[WITH SEC_TBL AS
(
select 
	distinct 
	FBC.BOOK_TYPE_CODE
	from 
	FA_BOOK_CONTROLS FBC,
	fun_user_role_data_asgnmnts FURDA,
	per_roles_dn PRD,
	per_user_roles PUR,
	per_users PU
where 
	1=1
	AND FURDA.BOOK_ID = FBC.BOOK_CONTROL_ID
	AND UPPER(FURDA.role_name) in ('XXC_ASSET_MANAGER_JOB','XXC_ASSET_ACCOUNTANT_JOB','XXC_ASSET_INQUIRY_JOB')
	AND UPPER(FURDA.active_flag) = 'Y'
	AND UPPER(FURDA.role_name) = UPPER(PRD.role_common_name)
	AND PRD.role_id = PUR.role_id
	AND PUR.user_id = PU.user_id
	AND upper(pu.username) = upper(:xdo_user_name)
	and pu.user_guid = FURDA.user_guid
  
)

--working code for additions

SELECT /*+parallel(12)*/
ASSET_MAJOR_MINOR_CATEGORY,
ASSET_NUMBER,
ASSET_DESCRIPTION,
BOOK_TYPE_CODE,
DATE_PLACED_IN_SERVICE,
MANUFACTURER_NAME,
TAG_NUMBER,
SERIAL_NUMBER,
LEASE_NUMBER,
LEASE_NAME,
SOURCE_TYPE_CODE,
LIFE_IN_MONTHS,
ASSET_KEY_PROCESS_CODE,
ASSET_KEY_GBU,
ASSET_KEY_TOOL_CLASS,
ASSET_KEY_SKU,
ASSET_KEY_TRANSFER,
ASSET_KEY_LEGACY_ANUM,
ASSET_KEY_TOOL_TONNAGE,

ASSET_KEY_TOOL_MAKER,
ENABLED_FLAG,
ASSET_COUNTRY,
ASSET_STATE,
ASSET_CITY,
ASSET_BUILDING,
ASSET_SITE,

DEPRN_EXPENSE_ACCOUNTS,
BRAND_DESC,
PARENT_BRAND,
MAIN_COST,
CURRENCY_CODE,
PERIOD_NAME
FROM
(

SELECT
FC.SEGMENT1||'-'||FC.SEGMENT2 ASSET_MAJOR_MINOR_CATEGORY,
FAB.ASSET_NUMBER AS ASSET_NUMBER,
FAT.DESCRIPTION AS ASSET_DESCRIPTION,
FB.BOOK_TYPE_CODE,
TO_CHAR(FB.DATE_PLACED_IN_SERVICE, 'MM/DD/YYYY') DATE_PLACED_IN_SERVICE,
FAB.MANUFACTURER_NAME,
FAB.TAG_NUMBER,
FAB.SERIAL_NUMBER,
FAB.ATTRIBUTE1 LEASE_NUMBER,
FAB.ATTRIBUTE2 LEASE_NAME,
FTH.TRANSACTION_TYPE_CODE SOURCE_TYPE_CODE,
FM.LIFE_IN_MONTHS,
FAK.SEGMENT1 ASSET_KEY_PROCESS_CODE,
FAK.SEGMENT2 ASSET_KEY_GBU,
FAK.SEGMENT3 ASSET_KEY_TOOL_CLASS,
FAK.SEGMENT4 ASSET_KEY_SKU,
FAK.SEGMENT5 ASSET_KEY_TRANSFER,
FAK.SEGMENT6 ASSET_KEY_LEGACY_ANUM,
FAK.SEGMENT7 ASSET_KEY_TOOL_TONNAGE,
FAK.SEGMENT8  ASSET_KEY_TOOL_MAKER,
FL.ENABLED_FLAG,
FL.SEGMENT1 ASSET_COUNTRY,
FL.SEGMENT2 ASSET_STATE,
FL.SEGMENT3 ASSET_CITY,
FL.SEGMENT4 ASSET_BUILDING,
FL.SEGMENT5 ASSET_SITE,
GCCI.SEGMENT1||'.'||GCCI.SEGMENT2||'.'||GCCI.SEGMENT3||'.'||GCCI.SEGMENT4||'.'||GCCI.SEGMENT5||'.'|| GCCI.SEGMENT6||'.'||GCCI.SEGMENT7||'.'||GCCI.SEGMENT8||'.'||GCCI.SEGMENT9 DEPRN_EXPENSE_ACCOUNTS,
FVTVV.DESCRIPTION BRAND_DESC,
(SELECT  distinct
	 fvtv.description
	from
	gl_seg_val_hier_cf     cft,
	fnd_vs_typed_values_vl fvtv
	WHERE
	1=1
	  AND cft.DEP29_PK1_VALUE = fvtv.value (+)
	  AND cft.DEP29_PK2_VALUE = fvtv.value_set_code (+)
	  and UPPER(TREE_CODE)='BRAND'
	  and cft.dep0_pk1_value = GCCI.segment6
) PARENT_BRAND,
--nvl(fdd.COST,fb.cost) MAIN_COST,
(select sum( nvl(decode(adj.debit_credit_flag,'DR',1,-1)
                 * adj.ADJUSTMENT_AMOUNT,
                 FDD.ADDITION_COST_TO_CLEAR) )                  COST
				 from
				 fa_adjustments adj
				 where 
				 1=1
				 AND adj.ASSET_ID = FAB.ASSET_ID
				 AND ((adj.source_type_code = 'CIP ADDITION' and adj.adjustment_type = 'CIP COST') 
				 or	(adj.source_type_code = 'ADDITION' and adj.adjustment_type = 'COST'))
				 AND FDH.distribution_id = adj.distribution_id
				 AND FDH.book_type_code = adj.book_type_code
				 ) MAIN_COST,
GL.CURRENCY_CODE,
FDP.PERIOD_NAME

FROM
FA_ADDITIONS_B FAB ,
FA_CATEGORIES_B FC,
FA_ADDITIONS_TL FAT,
FA_BOOKS FB,
FA_BOOKS_SUMMARY FBS,
FA_DEPRN_PERIODS FDP,
FA_BOOK_CONTROLS FBC,
FA_CATEGORY_BOOKS FCB,
FA_TRANSACTION_HEADERS FTH,
FA_METHODS FM,
FA_ASSET_KEYWORDS FAK,
FA_DISTRIBUTION_HISTORY FDH,
FA_LOCATIONS FL,
GL_CODE_COMBINATIONS GCCI,
FND_VS_TYPED_VALUES_VL FVTVV,
GL_LEDGERS GL,
FA_DEPRN_DETAIL FDD,
FA_DEPRN_SUMMARY	FDS,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND ST.BOOK_TYPE_CODE 				= FB.BOOK_TYPE_CODE --SECURITY
AND FC.CATEGORY_ID 					= FAB.ASSET_CATEGORY_ID
AND FAB.ASSET_ID 					= FAT.ASSET_ID
AND FAT.LANGUAGE 					= USERENV('LANG')
AND FAB.ASSET_ID 					= FB.ASSET_ID
AND FB.BOOK_TYPE_CODE				= FBS.BOOK_TYPE_CODE
AND FB.ASSET_ID 					= FBS.ASSET_ID
AND FB.BOOK_TYPE_CODE				= FBC.BOOK_TYPE_CODE
AND FBS.BOOK_TYPE_CODE				= FDP.BOOK_TYPE_CODE
AND FBS.PERIOD_COUNTER				= FDP.PERIOD_COUNTER
AND FBS.CREATION_DATE				= (SELECT MIN(CREATION_DATE) FROM FA_BOOKS_SUMMARY FBSS WHERE FBSS.ASSET_ID = FBS.ASSET_ID AND FBSS.BOOK_TYPE_CODE = FBS.BOOK_TYPE_CODE)
AND FB.BOOK_TYPE_CODE 				= FCB.BOOK_TYPE_CODE
AND FC.CATEGORY_ID 					= FCB.CATEGORY_ID
AND FAB.ASSET_ID 					= FTH.ASSET_ID
AND FB.BOOK_TYPE_CODE 				= FTH.BOOK_TYPE_CODE
AND FTH.TRANSACTION_TYPE_CODE 		= 'ADDITION'
AND FB.TRANSACTION_HEADER_ID_IN 	= FTH.TRANSACTION_HEADER_ID
AND FB.METHOD_ID					= FM.METHOD_ID
AND FAB.ASSET_KEY_CCID				= FAK.CODE_COMBINATION_ID(+)
AND FAB.ASSET_ID					= FDH.ASSET_ID
AND FB.BOOK_TYPE_CODE				= FDH.BOOK_TYPE_CODE
--AND FDH.DATE_INEFFECTIVE 			IS not NULL
AND FDH.LOCATION_ID					= FL.LOCATION_ID
AND FDH.CODE_COMBINATION_ID			= GCCI.CODE_COMBINATION_ID
AND GCCI.SEGMENT6					= FVTVV.VALUE 
AND FVTVV.VALUE_SET_CODE			= 'BRAND VALUE SET'
AND FBC.SET_OF_BOOKS_ID				= GL.LEDGER_ID
AND FTH.TRANSACTION_KEY 			IS NULL
AND FDH.DISTRIBUTION_ID 			= FDD.DISTRIBUTION_ID
AND FDH.ASSET_ID 					= FDD.ASSET_ID
AND FDH.BOOK_TYPE_CODE 				= FDD.BOOK_TYPE_CODE
AND FBS.BOOK_TYPE_CODE 				= FDS.BOOK_TYPE_CODE
--AND FBS.PERIOD_COUNTER 				= FDS.PERIOD_COUNTER
--AND 'BOOKS'							= FDS.DEPRN_SOURCE_CODE(+)
AND FBS.ASSET_ID 					= FDS.ASSET_ID
AND FDD.BOOK_TYPE_CODE 				= FDS.BOOK_TYPE_CODE
AND FDD.PERIOD_COUNTER 				= FDS.PERIOD_COUNTER
AND FDD.ASSET_ID 					= FDS.ASSET_ID
--AND FAB.ASSET_NUMBER 				= 23
AND FDP.PERIOD_COUNTER				BETWEEN :PERIOD1 AND :PERIOD2
AND (FC.SEGMENT1                    IN (:P_MAJOR_CAT)
OR  'All'                       	IN (:P_MAJOR_CAT||'All'))	 
AND (FC.SEGMENT2                    IN (:P_MINOR_CAT)
OR  'All'                       	IN (:P_MINOR_CAT||'All'))	
AND (FB.BOOK_TYPE_CODE 			IN (:P_BOOK) OR LEAST (:P_BOOK) IS NULL)

UNION

SELECT /*+parallel(12)*/
FC.SEGMENT1||'-'||FC.SEGMENT2 ASSET_MAJOR_MINOR_CATEGORY,
FAB.ASSET_NUMBER AS ASSET_NUMBER,
FAT.DESCRIPTION AS ASSET_DESCRIPTION,
FB.BOOK_TYPE_CODE,
TO_CHAR(FB.DATE_PLACED_IN_SERVICE, 'MM/DD/YYYY') DATE_PLACED_IN_SERVICE,
FAB.MANUFACTURER_NAME,
FAB.TAG_NUMBER,
FAB.SERIAL_NUMBER,
FAB.ATTRIBUTE1 LEASE_NUMBER,
FAB.ATTRIBUTE2 LEASE_NAME,
FTH.TRANSACTION_TYPE_CODE SOURCE_TYPE_CODE,
FM.LIFE_IN_MONTHS,
FAK.SEGMENT1 ASSET_KEY_PROCESS_CODE,
FAK.SEGMENT2 ASSET_KEY_GBU,
FAK.SEGMENT3 ASSET_KEY_TOOL_CLASS,
FAK.SEGMENT4 ASSET_KEY_SKU,
FAK.SEGMENT5 ASSET_KEY_TRANSFER,
FAK.SEGMENT6 ASSET_KEY_LEGACY_ANUM,
FAK.SEGMENT7 ASSET_KEY_TOOL_TONNAGE,
FAK.SEGMENT8  ASSET_KEY_TOOL_MAKER,
FL.ENABLED_FLAG,
FL.SEGMENT1 ASSET_COUNTRY,
FL.SEGMENT2 ASSET_STATE,
FL.SEGMENT3 ASSET_CITY,
FL.SEGMENT4 ASSET_BUILDING,
FL.SEGMENT5 ASSET_SITE,
GCCI.SEGMENT1||'.'||GCCI.SEGMENT2||'.'||GCCI.SEGMENT3||'.'||GCCI.SEGMENT4||'.'||GCCI.SEGMENT5||'.'|| GCCI.SEGMENT6||'.'||GCCI.SEGMENT7||'.'||GCCI.SEGMENT8||'.'||GCCI.SEGMENT9 DEPRN_EXPENSE_ACCOUNTS,
FVTVV.DESCRIPTION BRAND_DESC,
(SELECT  distinct
	 fvtv.description
	from
	gl_seg_val_hier_cf     cft,
	fnd_vs_typed_values_vl fvtv
	WHERE
	1=1
	  AND cft.DEP29_PK1_VALUE = fvtv.value (+)
	  AND cft.DEP29_PK2_VALUE = fvtv.value_set_code (+)
	  and UPPER(TREE_CODE)='BRAND'
	  and cft.dep0_pk1_value = GCCI.segment6
) PARENT_BRAND,
(FB.COST - FBP.COST) MAIN_COST,
GL.CURRENCY_CODE,
FDP.PERIOD_NAME

FROM
FA_ADDITIONS_B FAB ,
FA_CATEGORIES_B FC,
FA_ADDITIONS_TL FAT,
FA_BOOKS FB,
FA_DEPRN_PERIODS FDP,
FA_BOOK_CONTROLS FBC,
FA_CATEGORY_BOOKS FCB,
FA_TRANSACTION_HEADERS FTH,
FA_METHODS FM,
FA_ASSET_KEYWORDS FAK,
FA_DISTRIBUTION_HISTORY FDH,
FA_LOCATIONS FL,
GL_CODE_COMBINATIONS GCCI,
FND_VS_TYPED_VALUES_VL FVTVV,
GL_LEDGERS GL,
FA_BOOKS FBP,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND ST.BOOK_TYPE_CODE 				= FB.BOOK_TYPE_CODE --SECURITY
AND FC.CATEGORY_ID 					= FAB.ASSET_CATEGORY_ID
AND FAB.ASSET_ID 					= FAT.ASSET_ID
AND FAT.LANGUAGE 					= USERENV('LANG')
AND FAB.ASSET_ID 					= FB.ASSET_ID
AND FB.BOOK_TYPE_CODE				= FBC.BOOK_TYPE_CODE
AND FB.BOOK_TYPE_CODE 				= FCB.BOOK_TYPE_CODE
AND FC.CATEGORY_ID 					= FCB.CATEGORY_ID
AND FAB.ASSET_ID 					= FTH.ASSET_ID
AND FB.BOOK_TYPE_CODE 				= FTH.BOOK_TYPE_CODE
AND FTH.TRANSACTION_TYPE_CODE 		IN ('ADJUSTMENT','CIP ADJUSTMENT')
AND FB.TRANSACTION_HEADER_ID_IN 	= FTH.TRANSACTION_HEADER_ID
AND FB.METHOD_ID					= FM.METHOD_ID
AND FAB.ASSET_KEY_CCID				= FAK.CODE_COMBINATION_ID(+)
AND FAB.ASSET_ID					= FDH.ASSET_ID
AND FB.BOOK_TYPE_CODE				= FDH.BOOK_TYPE_CODE
AND FTH.BOOK_TYPE_CODE				= FDP.BOOK_TYPE_CODE
AND FTH.DATE_EFFECTIVE 				BETWEEN FDP.PERIOD_OPEN_DATE AND NVL(FDP.PERIOD_CLOSE_DATE, SYSDATE)
AND FDH.LOCATION_ID					= FL.LOCATION_ID
AND FDH.CODE_COMBINATION_ID			= GCCI.CODE_COMBINATION_ID
AND GCCI.SEGMENT6					= FVTVV.VALUE 
AND FVTVV.VALUE_SET_CODE			= 'BRAND VALUE SET'
AND FBC.SET_OF_BOOKS_ID				= GL.LEDGER_ID
--AND FAB.ASSET_NUMBER 				= 24
AND FTH.TRANSACTION_KEY 			IS NULL
AND FAB.ASSET_ID 					= FBP.ASSET_ID
AND FBP.BOOK_TYPE_CODE				= FB.BOOK_TYPE_CODE
AND FB.TRANSACTION_HEADER_ID_IN		= FBP.TRANSACTION_HEADER_ID_OUT
AND FTH.TRANSACTION_HEADER_ID >=    FDH.TRANSACTION_HEADER_ID_IN       
AND FTH.TRANSACTION_HEADER_ID <     NVL(FDH.TRANSACTION_HEADER_ID_OUT, FTH.TRANSACTION_HEADER_ID + 1)
AND FDP.PERIOD_COUNTER				BETWEEN :PERIOD1 AND :PERIOD2
AND (FC.SEGMENT1                    IN (:P_MAJOR_CAT)
OR  'All'                       	IN (:P_MAJOR_CAT||'All'))	 
AND (FC.SEGMENT2                    IN (:P_MINOR_CAT)
OR  'All'                       	IN (:P_MINOR_CAT||'All'))	
AND (FB.BOOK_TYPE_CODE 			IN (:P_BOOK) OR LEAST (:P_BOOK) IS NULL)
)
WHERE
MAIN_COST IS NOT NULL
AND MAIN_COST <> 0
ORDER BY
ASSET_NUMBER]]>

:PERIOD1 :PERIOD2 :P_BOOK :P_MAJOR_CAT :P_MINOR_CAT :xdo_user_name

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
FUN_USER_ROLE_DATA_ASGNMNTS	dimension	dimension
PER_ROLES_DN	dimension	dimension
PER_USER_ROLES	dimension	dimension
PER_USERS	dimension	dimension
Cost Change	measure	measure
Adjusted Cost	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.

Fixed Assets data model →Enterprise model →

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_BOOK_CONTROLS	29	13
FUN_USER_ROLE_DATA_ASGNMNTS	Setup / configuration table — joined for reference, not exposed for analytics
PER_ROLES_DN	Setup / configuration table — joined for reference, not exposed for analytics
PER_USER_ROLES	16	0
PER_USERS	22	27
GL_SEG_VAL_HIER_CF	13	38
FND_VS_TYPED_VALUES_VL	1	5
FA_ADJUSTMENTS	8	3
FA_ADDITIONS_B	15	9
FA_CATEGORIES_B	5	10
FA_ADDITIONS_TL	1	8
FA_BOOKS	20	2

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.