Asset Audit History Detail Report

Fixed Assets

The audit trail of changes to asset attributes — cost, category, life, depreciation method, and location — with before and after values and who changed them, so finance can prove the asset register's integrity to auditors.

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

Sample build · illustrative

Filters

Attribute

Sample

Book

CORP

From

Sample

Xdo User Name

Globex Holdings

410

Assets changed

1,290

Attribute changes

Method/life changes

Asset Number	Attribute Changed	Old Value	New Value	Changed By	Change Date
1001	Sample	Sample	Sample	Sample	2026-04-30
1002	—	—	—	—	2026-03-31
1003	Sample	Sample	Sample	Sample	2026-02-28
1004	—	—	—	—	2026-01-31
1005	Sample	Sample	Sample	Sample	2025-12-31
1001	Sample	Sample	Sample	Sample	2026-04-30

AI Analyst · active

reading

The report reads the FA additions and books audit tables, listing each attribute change with its old and new value and the user.

flag

34 changes altered depreciation method or life — these directly change expense, and a cluster of them late in the period is worth confirming before close.

root cause & next step

Review the method and life changes for authorization; unexplained late-period changes to depreciation parameters are a common restatement trigger.

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_BOOK_CONTROLS
FUN_USER_ROLE_DATA_ASGNMNTS
PER_ROLES_DN
PER_USER_ROLES
PER_USERS
FA_ADDITIONS_B_
FA_BOOKS
FA_ADDITIONS_B
FA_TRANSACTION_HEADERS
FA_METHODS
FA_BONUS_RULES
FA_ASSET_HISTORY

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
    
)

SELECT
-- SERIAL_NUMBER
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FAB1.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(ABC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,NVL((SELECT MAX(A.SERIAL_NUMBER) FROM FA_ADDITIONS_B_ A, FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') OLD_VALUE
,NVL((SELECT MAX(A.SERIAL_NUMBER) FROM FA_ADDITIONS_B  A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') NEW_VALUE
,(SELECT MAX(A.LAST_UPDATE_DATE) FROM FA_ADDITIONS_B_ A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE) LAST_UPDATE_DATE
,FAB.ASSET_ID
,'SERIAL_NUMBER' ATTRIBUTE
,FBI.BOOK_TYPE_CODE
FROM 
FA_ADDITIONS_B_ FAB,
FA_BOOKS FBI
WHERE 1=1
AND FAB.ASSET_ID = FBI.ASSET_ID
AND FAB.AUDIT_ACTION_TYPE_ <> 'HISTORY') ABC,

FA_ADDITIONS_B_ FAB1,	
FA_ADDITIONS_B FABB,
FA_BOOKS FB,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
--AND FAB1.ASSET_NUMBER = '141534' 
AND FAB1.ASSET_ID = FABB.ASSET_ID
AND FAB1.ASSET_ID = FB.ASSET_ID
AND FAB1.ASSET_ID = ABC.ASSET_ID
AND ABC.OLD_VALUE <> ABC.NEW_VALUE
AND ABC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND ST.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE --SECURITY
AND (FB.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FAB1.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

GROUP BY
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

 union

SELECT
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FABB.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(FABB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,(SELECT max( A.SERIAL_NUMBER) FROM FA_ADDITIONS_B_ A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID) OLD_VALUE
,(SELECT A.SERIAL_NUMBER FROM FA_ADDITIONS_B A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID) NEW_VALUE
,FAB.ASSET_ID
,'SERIAL_NUMBER' ATTRIBUTE
FROM 
FA_ADDITIONS_B FAB
WHERE 1=1 ) ABC,

FA_ADDITIONS_B_ FAB1,
FA_ADDITIONS_B FABB,
FA_TRANSACTION_HEADERS FTH,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND FABB.ASSET_ID = FAB1.ASSET_ID(+)
AND FAB1.ASSET_ID IS NULL
AND FABB.ASSET_ID = FTH.ASSET_ID
AND FABB.ASSET_ID = ABC.ASSET_ID
AND ABC.NEW_VALUE IS NOT NULL
AND ST.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE -- SECURITY
AND (FTH.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FABB.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO
GROUP BY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE 

UNION

SELECT
-- MODEL_NUMBER
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FAB1.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(ABC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,NVL((SELECT MAX(A.MODEL_NUMBER) FROM FA_ADDITIONS_B_ A, FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') OLD_VALUE
,NVL((SELECT MAX(A.MODEL_NUMBER) FROM FA_ADDITIONS_B_  A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') NEW_VALUE
,(SELECT MAX(A.LAST_UPDATE_DATE) FROM FA_ADDITIONS_B_ A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE) LAST_UPDATE_DATE
,FAB.ASSET_ID
,'MODEL_NUMBER' ATTRIBUTE
,FBI.BOOK_TYPE_CODE
FROM 
FA_ADDITIONS_B_ FAB,
FA_BOOKS FBI
WHERE 1=1
AND FAB.ASSET_ID = FBI.ASSET_ID
AND FAB.AUDIT_ACTION_TYPE_ <> 'HISTORY') ABC,

FA_ADDITIONS_B_ FAB1,	
FA_ADDITIONS_B FABB,
FA_BOOKS FB,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
--AND FAB1.ASSET_NUMBER = '141534' 
AND FAB1.ASSET_ID = FABB.ASSET_ID
AND FAB1.ASSET_ID = FB.ASSET_ID
AND FAB1.ASSET_ID = ABC.ASSET_ID
AND ABC.OLD_VALUE <> ABC.NEW_VALUE
AND ABC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND ST.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE --SECURITY
AND (FB.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FAB1.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

GROUP BY
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

UNION

SELECT
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FABB.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(FABB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,(SELECT max( A.MODEL_NUMBER) FROM FA_ADDITIONS_B_ A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID) OLD_VALUE
,(SELECT A.MODEL_NUMBER FROM FA_ADDITIONS_B A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID) NEW_VALUE
,FAB.ASSET_ID
,'MODEL_NUMBER' ATTRIBUTE
FROM 
FA_ADDITIONS_B FAB
WHERE 1=1 ) ABC,

FA_ADDITIONS_B_ FAB1,
FA_ADDITIONS_B FABB,
FA_TRANSACTION_HEADERS FTH,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND FABB.ASSET_ID = FAB1.ASSET_ID(+)
AND FAB1.ASSET_ID IS NULL
AND FABB.ASSET_ID = FTH.ASSET_ID
AND FABB.ASSET_ID = ABC.ASSET_ID
AND ABC.NEW_VALUE IS NOT NULL
AND ST.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE --SECURITY
AND (FTH.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FABB.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO
GROUP BY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

UNION

SELECT
-- TAG_NUMBER
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FAB1.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(ABC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,NVL((SELECT MAX(A.TAG_NUMBER) FROM FA_ADDITIONS_B_ A, FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') OLD_VALUE
,NVL((SELECT MAX(A.TAG_NUMBER) FROM FA_ADDITIONS_B_  A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') NEW_VALUE
,(SELECT MAX(A.LAST_UPDATE_DATE) FROM FA_ADDITIONS_B_ A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE) LAST_UPDATE_DATE
,FAB.ASSET_ID
,'TAG_NUMBER' ATTRIBUTE
,FBI.BOOK_TYPE_CODE
FROM 
FA_ADDITIONS_B_ FAB,
FA_BOOKS FBI
WHERE 1=1
AND FAB.ASSET_ID = FBI.ASSET_ID
AND FAB.AUDIT_ACTION_TYPE_ <> 'HISTORY') ABC,

FA_ADDITIONS_B_ FAB1,	
FA_ADDITIONS_B FABB,
FA_BOOKS FB,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
--AND FAB1.ASSET_NUMBER = '141534' 
AND FAB1.ASSET_ID = FABB.ASSET_ID
AND FAB1.ASSET_ID = FB.ASSET_ID
AND FAB1.ASSET_ID = ABC.ASSET_ID
AND ABC.OLD_VALUE <> ABC.NEW_VALUE
AND ABC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND ST.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE --SECURITY
AND (FB.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FAB1.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

GROUP BY
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

UNION

SELECT
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FABB.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(FABB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,(SELECT MAX ( A.TAG_NUMBER) FROM FA_ADDITIONS_B_ A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID) OLD_VALUE
,(SELECT A.TAG_NUMBER FROM FA_ADDITIONS_B A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID) NEW_VALUE
,FAB.ASSET_ID
,'TAG_NUMBER' ATTRIBUTE
FROM 
FA_ADDITIONS_B FAB
WHERE 1=1 ) ABC,

FA_ADDITIONS_B_ FAB1,
FA_ADDITIONS_B FABB,
FA_TRANSACTION_HEADERS FTH,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND FABB.ASSET_ID = FAB1.ASSET_ID(+)
AND FAB1.ASSET_ID IS NULL
AND FABB.ASSET_ID = FTH.ASSET_ID
AND FABB.ASSET_ID = ABC.ASSET_ID
AND ABC.NEW_VALUE IS NOT NULL
AND ST.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE --SECURITY
AND (FTH.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FABB.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO
GROUP BY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

UNION

SELECT
--ASSET KEY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FAB1.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(ABC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,FAK_OLD.SEGMENT1||'-'||FAK_OLD.SEGMENT2||'-'||FAK_OLD.SEGMENT3||'-'||FAK_OLD.SEGMENT4||'-'||FAK_OLD.SEGMENT5||'-'||FAK_OLD.SEGMENT6
||'-'||FAK_OLD.SEGMENT7||'-'||FAK_OLD.SEGMENT8 OLD_VALUE
,FAK_NEW.SEGMENT1||'-'||FAK_NEW.SEGMENT2||'-'||FAK_NEW.SEGMENT3||'-'||FAK_NEW.SEGMENT4||'-'||FAK_NEW.SEGMENT5||'-'||FAK_NEW.SEGMENT6
||'-'||FAK_NEW.SEGMENT7 ||'-'||FAK_NEW.SEGMENT8 NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,NVL((SELECT MAX(A.ASSET_KEY_CCID) FROM FA_ADDITIONS_B_ A, FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),1) OLD_VALUE
,NVL((SELECT MAX(A.ASSET_KEY_CCID) FROM FA_ADDITIONS_B_  A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),1) NEW_VALUE
,(SELECT MAX(A.LAST_UPDATE_DATE) FROM FA_ADDITIONS_B_ A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE) LAST_UPDATE_DATE
,FAB.ASSET_ID
,'ASSET_KEY' ATTRIBUTE
,FBI.BOOK_TYPE_CODE
FROM 
FA_ADDITIONS_B_ FAB,
FA_BOOKS FBI
WHERE 1=1
AND FAB.ASSET_ID = FBI.ASSET_ID
AND FAB.AUDIT_ACTION_TYPE_ <> 'HISTORY') ABC,

FA_ADDITIONS_B_ FAB1,
FA_ADDITIONS_B FABB,
FA_TRANSACTION_HEADERS FTH,
FA_ASSET_KEYWORDS FAK_OLD, 
FA_ASSET_KEYWORDS FAK_NEW,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND FAB1.ASSET_ID = FABB.ASSET_ID
AND FAB1.ASSET_ID = FTH.ASSET_ID
AND FAB1.ASSET_ID = ABC.ASSET_ID
AND ABC.OLD_VALUE <> ABC.NEW_VALUE
AND ABC.OLD_VALUE = FAK_OLD.CODE_COMBINATION_ID
AND ABC.NEW_VALUE = FAK_NEW.CODE_COMBINATION_ID
AND ST.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE --SECURITY
AND (FTH.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FAB1.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

GROUP BY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE
,FAK_OLD.SEGMENT1
,FAK_OLD.SEGMENT2
,FAK_OLD.SEGMENT3
,FAK_OLD.SEGMENT4
,FAK_OLD.SEGMENT5
,FAK_OLD.SEGMENT6
,FAK_OLD.SEGMENT7
,FAK_OLD.SEGMENT8
,FAK_NEW.SEGMENT1
,FAK_NEW.SEGMENT2
,FAK_NEW.SEGMENT3
,FAK_NEW.SEGMENT4
,FAK_NEW.SEGMENT5
,FAK_NEW.SEGMENT6
,FAK_NEW.SEGMENT7
,FAK_NEW.SEGMENT8

UNION

SELECT
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FABB.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(FABB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,NULL OLD_VALUE
,FAK_NEW.SEGMENT1||'-'||FAK_NEW.SEGMENT2||'-'||FAK_NEW.SEGMENT3||'-'||FAK_NEW.SEGMENT4||'-'||FAK_NEW.SEGMENT5||'-'||FAK_NEW.SEGMENT6
||'-'||FAK_NEW.SEGMENT7 ||'-'||FAK_NEW.SEGMENT8 NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,null OLD_VALUE
,(SELECT A.ASSET_KEY_CCID FROM FA_ADDITIONS_B A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID) NEW_VALUE
,FAB.ASSET_ID
,'ASSET_KEY' ATTRIBUTE
FROM 
FA_ADDITIONS_B FAB
WHERE 1=1 ) ABC,

FA_ADDITIONS_B_ FAB1,
FA_ADDITIONS_B FABB,
FA_TRANSACTION_HEADERS FTH,
FA_ASSET_KEYWORDS FAK_NEW,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND FABB.ASSET_ID = FAB1.ASSET_ID(+)
AND FAB1.ASSET_ID IS NULL
AND FABB.ASSET_ID = FTH.ASSET_ID
AND FABB.ASSET_ID = ABC.ASSET_ID
AND ABC.NEW_VALUE IS NOT NULL
AND ABC.NEW_VALUE = FAK_NEW.CODE_COMBINATION_ID
AND ST.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE --SECURITY
AND (FTH.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FABB.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO
GROUP BY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE
,FAK_NEW.SEGMENT1
,FAK_NEW.SEGMENT2
,FAK_NEW.SEGMENT3
,FAK_NEW.SEGMENT4
,FAK_NEW.SEGMENT5
,FAK_NEW.SEGMENT6
,FAK_NEW.SEGMENT7
,FAK_NEW.SEGMENT8

UNION

SELECT
-- MANUFACTURER_NAME
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FAB1.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(ABC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,NVL((SELECT MAX(A.MANUFACTURER_NAME) FROM FA_ADDITIONS_B_ A, FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') OLD_VALUE
,NVL((SELECT MAX(A.MANUFACTURER_NAME) FROM FA_ADDITIONS_B_  A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE),' ') NEW_VALUE
,(SELECT MAX(A.LAST_UPDATE_DATE) FROM FA_ADDITIONS_B_ A,FA_BOOKS FBB WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID AND FBB.ASSET_ID = A.ASSET_ID AND FBB.BOOK_TYPE_CODE = FBI.BOOK_TYPE_CODE) LAST_UPDATE_DATE
,FAB.ASSET_ID
,'MANUFACTURER_NAME' ATTRIBUTE
,FBI.BOOK_TYPE_CODE
FROM 
FA_ADDITIONS_B_ FAB,
FA_BOOKS FBI
WHERE 1=1
AND FAB.ASSET_ID = FBI.ASSET_ID
AND FAB.AUDIT_ACTION_TYPE_ <> 'HISTORY') ABC,

FA_ADDITIONS_B_ FAB1,	
FA_ADDITIONS_B FABB,
FA_BOOKS FB,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
--AND FAB1.ASSET_NUMBER = '141534' 
AND FAB1.ASSET_ID = FABB.ASSET_ID
AND FAB1.ASSET_ID = FB.ASSET_ID
AND FAB1.ASSET_ID = ABC.ASSET_ID
AND ABC.OLD_VALUE <> ABC.NEW_VALUE
AND ABC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND ST.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE --SECURITY
AND (FB.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FAB1.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

GROUP BY
FABB.ASSET_NUMBER
,FB.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

union
--MANUFACTURER_NAME
SELECT
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,MAX(FABB.CREATION_DATE) CREATION_DATE
,ABC.CREATED_BY
,MAX(FABB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE

FROM
(SELECT 
FAB.LAST_UPDATED_BY
,FAB.CREATED_BY
,(SELECT max( A.MANUFACTURER_NAME) FROM FA_ADDITIONS_B_ A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER -1) AND A.ASSET_ID = FAB.ASSET_ID) OLD_VALUE
,(SELECT A.MANUFACTURER_NAME FROM FA_ADDITIONS_B A WHERE A.OBJECT_VERSION_NUMBER = (FAB.OBJECT_VERSION_NUMBER ) AND A.ASSET_ID = FAB.ASSET_ID) NEW_VALUE
,FAB.ASSET_ID
,'MANUFACTURER_NAME' ATTRIBUTE
FROM 
FA_ADDITIONS_B FAB
WHERE 1=1 ) ABC,

FA_ADDITIONS_B_ FAB1,
FA_ADDITIONS_B FABB,
FA_TRANSACTION_HEADERS FTH,
SEC_TBL ST --SECURITY ACCESS 
WHERE
1=1
AND FABB.ASSET_ID = FAB1.ASSET_ID(+)
AND FAB1.ASSET_ID IS NULL
AND FABB.ASSET_ID = FTH.ASSET_ID
AND FABB.ASSET_ID = ABC.ASSET_ID
AND ABC.NEW_VALUE IS NOT NULL
AND ST.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE --SECURITY
AND (FTH.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(FABB.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO
GROUP BY
FABB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,ABC.CREATED_BY
,ABC.LAST_UPDATED_BY
,ABC.OLD_VALUE
,ABC.NEW_VALUE
,ABC.ATTRIBUTE

UNION

SELECT
--LIFE_IN_MONTHS
ABC.ASSET_NUMBER
,ABC.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,ABC.CREATION_DATE
,ABC.CREATED_BY
,ABC.LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,TO_CHAR(XYZ.OLD_VALUE ) OLD_VALUE
,TO_CHAR(ABC.NEW_VALUE) NEW_VALUE

FROM

(SELECT DISTINCT
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,'LIFE_IN_MONTHS' ATTRIBUTE
,MAX(FTH.CREATION_DATE) CREATION_DATE
,FTH.CREATED_BY
,MAX(FTH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,FTH.LAST_UPDATED_BY
,FM.LIFE_IN_MONTHS NEW_VALUE 
,FAB.ASSET_ID NEW_ASSET_ID
,FB.TRANSACTION_HEADER_ID_IN

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH,
FA_METHODS FM

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.METHOD_ID = FM.METHOD_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND (FB.DATE_INEFFECTIVE IS NULL 
              OR FB.TRANSACTION_HEADER_ID_IN = (SELECT FB1.TRANSACTION_HEADER_ID_OUT
                                                 FROM FA_BOOKS FB1 
												 WHERE FB.TRANSACTION_HEADER_ID_IN = FB1.TRANSACTION_HEADER_ID_OUT))

GROUP BY
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,FTH.CREATED_BY
,FTH.LAST_UPDATED_BY
,FM.LIFE_IN_MONTHS
,FAB.ASSET_ID
,FB.TRANSACTION_HEADER_ID_IN

) ABC,

(SELECT 
FM.LIFE_IN_MONTHS OLD_VALUE
,FB.TRANSACTION_HEADER_ID_OUT
,FAB.ASSET_ID OLD_ASSET_ID

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH,
FA_METHODS FM

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.METHOD_ID = FM.METHOD_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND FB.DATE_INEFFECTIVE IS NOT NULL
) XYZ,
SEC_TBL ST --SECURITY ACCESS 

WHERE 
1=1
AND ABC.NEW_ASSET_ID = XYZ.OLD_ASSET_ID
AND XYZ.TRANSACTION_HEADER_ID_OUT = ABC.TRANSACTION_HEADER_ID_IN
AND XYZ.OLD_VALUE <> ABC.NEW_VALUE
AND ST.BOOK_TYPE_CODE = ABC.BOOK_TYPE_CODE --SECURITY
--AND NVL(XYZ.OLD_VALUE,-9999) <> NVL(ABC.NEW_VALUE,-9999)
AND (ABC.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(ABC.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

UNION

SELECT
--DEPRECIATION_METHOD
ABC.ASSET_NUMBER
,ABC.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,ABC.CREATION_DATE
,ABC.CREATED_BY
,ABC.LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,XYZ.OLD_VALUE OLD_VALUE
,ABC.NEW_VALUE NEW_VALUE

FROM

(SELECT DISTINCT
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,'DEPRECIATION_METHOD' ATTRIBUTE
,MAX(FTH.CREATION_DATE) CREATION_DATE
,FTH.CREATED_BY
,MAX(FTH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,FTH.LAST_UPDATED_BY
,FM.METHOD_CODE NEW_VALUE 
,FAB.ASSET_ID NEW_ASSET_ID
,FB.TRANSACTION_HEADER_ID_IN

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH,
FA_METHODS FM

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.METHOD_ID = FM.METHOD_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND (FB.DATE_INEFFECTIVE IS NULL 
              OR FB.TRANSACTION_HEADER_ID_IN = (SELECT FB1.TRANSACTION_HEADER_ID_OUT
                                                 FROM FA_BOOKS FB1 
												 WHERE FB.TRANSACTION_HEADER_ID_IN = FB1.TRANSACTION_HEADER_ID_OUT))

GROUP BY
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,FTH.CREATED_BY
,FTH.LAST_UPDATED_BY
,FM.METHOD_CODE
,FAB.ASSET_ID

,FB.TRANSACTION_HEADER_ID_IN

) ABC,

(SELECT FM.METHOD_CODE OLD_VALUE
,FB.TRANSACTION_HEADER_ID_OUT
,FAB.ASSET_ID OLD_ASSET_ID

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH,
FA_METHODS FM

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.METHOD_ID = FM.METHOD_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND FB.DATE_INEFFECTIVE IS NOT NULL
) XYZ,
SEC_TBL ST --SECURITY ACCESS 

WHERE 
1=1
AND ABC.NEW_ASSET_ID = XYZ.OLD_ASSET_ID
AND XYZ.TRANSACTION_HEADER_ID_OUT = ABC.TRANSACTION_HEADER_ID_IN
AND XYZ.OLD_VALUE <> ABC.NEW_VALUE
AND ST.BOOK_TYPE_CODE = ABC.BOOK_TYPE_CODE --SECURITY
--AND NVL(XYZ.OLD_VALUE,'$$$$') <> NVL(ABC.NEW_VALUE,'$$$$')
AND (ABC.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(ABC.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

UNION

SELECT
--DEPRECIATION_BONUS_RULE
ABC.ASSET_NUMBER
,ABC.BOOK_TYPE_CODE
,ABC. ATTRIBUTE
,ABC.CREATION_DATE
,ABC.CREATED_BY
,ABC.LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,XYZ.OLD_VALUE OLD_VALUE
,ABC.NEW_VALUE NEW_VALUE

FROM

(SELECT DISTINCT
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,'DEPRECIATION_BONUS_RULE' ATTRIBUTE
,MAX(FTH.CREATION_DATE) CREATION_DATE
,FTH.CREATED_BY
,MAX(FTH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,FTH.LAST_UPDATED_BY
,FBR.BONUS_RULE NEW_VALUE 
,FAB.ASSET_ID NEW_ASSET_ID
,FB.TRANSACTION_HEADER_ID_IN

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH,
FA_BONUS_RULES FBR

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.BONUS_RULE_ID = FBR.BONUS_RULE_ID (+)
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND (FB.DATE_INEFFECTIVE IS NULL 
              OR FB.TRANSACTION_HEADER_ID_IN = (SELECT FB1.TRANSACTION_HEADER_ID_OUT
                                                 FROM FA_BOOKS FB1 
												 WHERE FB.TRANSACTION_HEADER_ID_IN = FB1.TRANSACTION_HEADER_ID_OUT))

GROUP BY
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,FTH.CREATED_BY
,FTH.LAST_UPDATED_BY
,FBR.BONUS_RULE
,FAB.ASSET_ID

,FB.TRANSACTION_HEADER_ID_IN

) ABC,

(SELECT DISTINCT
FBR.BONUS_RULE OLD_VALUE
,FB.TRANSACTION_HEADER_ID_OUT
,FAB.ASSET_ID OLD_ASSET_ID

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH,
FA_BONUS_RULES FBR

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.BONUS_RULE_ID = FBR.BONUS_RULE_ID (+)
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND FB.DATE_INEFFECTIVE IS NOT NULL
) XYZ,
SEC_TBL ST --SECURITY ACCESS 

WHERE 
1=1
AND ABC.NEW_ASSET_ID = XYZ.OLD_ASSET_ID
AND XYZ.TRANSACTION_HEADER_ID_OUT = ABC.TRANSACTION_HEADER_ID_IN
AND ABC.NEW_VALUE  IS NOT NULL
AND ST.BOOK_TYPE_CODE = ABC.BOOK_TYPE_CODE --SECURITY
AND (ABC.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(ABC.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

UNION

SELECT
--DATE_PLACED_IN_SERVICE
ABC.ASSET_NUMBER
,ABC.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,ABC.CREATION_DATE
,ABC.CREATED_BY
,ABC.LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,TO_CHAR(XYZ.OLD_VALUE, 'MM/DD/YYYY') OLD_VALUE
,TO_CHAR(ABC.NEW_VALUE, 'MM/DD/YYYY') NEW_VALUE

FROM

(SELECT DISTINCT
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,'DATE_PLACED_IN_SERVICE' ATTRIBUTE
,MAX(FTH.CREATION_DATE) CREATION_DATE
,FTH.CREATED_BY
,MAX(FTH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,FTH.LAST_UPDATED_BY
,FB.DATE_PLACED_IN_SERVICE NEW_VALUE 
,FAB.ASSET_ID NEW_ASSET_ID
,FB.TRANSACTION_HEADER_ID_IN

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND (FB.DATE_INEFFECTIVE IS NULL 
              OR FB.TRANSACTION_HEADER_ID_IN = (SELECT FB1.TRANSACTION_HEADER_ID_OUT
                                                 FROM FA_BOOKS FB1 
												 WHERE FB.TRANSACTION_HEADER_ID_IN = FB1.TRANSACTION_HEADER_ID_OUT))

GROUP BY
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,FTH.CREATED_BY
,FTH.LAST_UPDATED_BY
,FB.DATE_PLACED_IN_SERVICE
,FAB.ASSET_ID

,FB.TRANSACTION_HEADER_ID_IN

) ABC,

(SELECT DISTINCT
FB.DATE_PLACED_IN_SERVICE OLD_VALUE
,FB.TRANSACTION_HEADER_ID_OUT
,FAB.ASSET_ID OLD_ASSET_ID

FROM
FA_ADDITIONS_B FAB,
FA_BOOKS FB,
FA_TRANSACTION_HEADERS FTH

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND FB.DATE_INEFFECTIVE IS NOT NULL
) XYZ,
SEC_TBL ST --SECURITY ACCESS 

WHERE 
1=1
AND ABC.NEW_ASSET_ID = XYZ.OLD_ASSET_ID
AND XYZ.TRANSACTION_HEADER_ID_OUT = ABC.TRANSACTION_HEADER_ID_IN
AND XYZ.OLD_VALUE <> ABC.NEW_VALUE
AND ST.BOOK_TYPE_CODE = ABC.BOOK_TYPE_CODE --SECURITY
--AND NVL(XYZ.OLD_VALUE,'$$$$') <> NVL(ABC.NEW_VALUE,'$$$$')
AND (ABC.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(ABC.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

UNION

SELECT
--AMORTAIZATION_CHECK_BOX
ABC.ASSET_NUMBER
,ABC.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,ABC.CREATION_DATE
,ABC.CREATED_BY
,ABC.LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,XYZ.OLD_VALUE OLD_VALUE
,ABC.NEW_VALUE NEW_VALUE

FROM

(
	SELECT DISTINCT
	FAB.ASSET_NUMBER
	,FTH.BOOK_TYPE_CODE
	,'AMORTAIZATION_CHECK_BOX' ATTRIBUTE
	,MAX(FTH.CREATION_DATE) CREATION_DATE
	,FTH.CREATED_BY
	,MAX(FTH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
	,FTH.LAST_UPDATED_BY
	,(CASE WHEN FTH.AMORTIZATION_START_DATE IS NOT NULL THEN 'Y' ELSE 'N' END) NEW_VALUE 
	,FAB.ASSET_ID NEW_ASSET_ID

	FROM
	FA_ADDITIONS_B FAB,
	FA_TRANSACTION_HEADERS FTH

	WHERE
	1=1
	AND FAB.asset_id = FTH.asset_id
	AND FTH.DATE_EFFECTIVE = (select MAX(FTH1.DATE_EFFECTIVE) from FA_TRANSACTION_HEADERS FTH1 where FTH1.asset_id=FAB.asset_id)  										 												 
	GROUP BY
	FAB.ASSET_NUMBER
	,FTH.BOOK_TYPE_CODE
	,FTH.CREATED_BY
	,FTH.LAST_UPDATED_BY
	,FTH.AMORTIZATION_START_DATE
	,FAB.ASSET_ID

) ABC,

(
	SELECT DISTINCT
	(CASE WHEN FTH.AMORTIZATION_START_DATE IS NOT NULL THEN 'Y' ELSE 'N' END) OLD_VALUE
	,FAB.ASSET_ID OLD_ASSET_ID

	FROM
	FA_ADDITIONS_B FAB,
	
	FA_TRANSACTION_HEADERS FTH

	WHERE
	1=1
	AND FAB.ASSET_ID = FTH.ASSET_ID
	AND FTH.DATE_EFFECTIVE = (select MAX(FTH1.DATE_EFFECTIVE) from FA_TRANSACTION_HEADERS FTH1 where FTH1.DATE_EFFECTIVE not in (select MAX(DATE_EFFECTIVE) from FA_TRANSACTION_HEADERS FTH2 where FTH2.asset_id = FAB.asset_id) AND FTH1.asset_id=FAB.asset_id	)
) XYZ,
SEC_TBL ST --SECURITY ACCESS 

WHERE 
1=1
AND ABC.NEW_ASSET_ID = XYZ.OLD_ASSET_ID
AND XYZ.OLD_VALUE <> ABC.NEW_VALUE
--AND NVL(XYZ.OLD_VALUE,'$$$$') <> NVL(ABC.NEW_VALUE,'$$$$')
AND ST.BOOK_TYPE_CODE = ABC.BOOK_TYPE_CODE --SECURITY
AND (ABC.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(ABC.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO

UNION

SELECT
--UNITS
ABC.ASSET_NUMBER
,ABC.BOOK_TYPE_CODE
,ABC.ATTRIBUTE
,ABC.CREATION_DATE
,ABC.CREATED_BY
,ABC.LAST_UPDATE_DATE
,ABC.LAST_UPDATED_BY
,TO_CHAR(XYZ.OLD_VALUE) OLD_VALUE
,TO_CHAR(ABC.NEW_VALUE) NEW_VALUE

FROM

(SELECT DISTINCT
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,'UNITS' ATTRIBUTE
,MAX(FTH.CREATION_DATE) CREATION_DATE
,FTH.CREATED_BY
,MAX(FTH.LAST_UPDATE_DATE) LAST_UPDATE_DATE
,FTH.LAST_UPDATED_BY
,FB.UNITS NEW_VALUE 
,FAB.ASSET_ID NEW_ASSET_ID
,FB.TRANSACTION_HEADER_ID_IN

FROM
FA_ADDITIONS_B FAB,
FA_ASSET_HISTORY FB,
FA_TRANSACTION_HEADERS FTH

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND (FB.DATE_INEFFECTIVE IS NULL 
              OR FB.TRANSACTION_HEADER_ID_IN = (SELECT FB1.TRANSACTION_HEADER_ID_OUT
                                                 FROM FA_ASSET_HISTORY FB1 
												 WHERE FB.TRANSACTION_HEADER_ID_IN = FB1.TRANSACTION_HEADER_ID_OUT))

GROUP BY
FAB.ASSET_NUMBER
,FTH.BOOK_TYPE_CODE
,FTH.CREATED_BY
,FTH.LAST_UPDATED_BY
,FB.UNITS
,FAB.ASSET_ID

,FB.TRANSACTION_HEADER_ID_IN

) ABC,

(SELECT DISTINCT
FB.UNITS OLD_VALUE
,FB.TRANSACTION_HEADER_ID_OUT
,FAB.ASSET_ID OLD_ASSET_ID

FROM
FA_ADDITIONS_B_ FAB,
FA_ASSET_HISTORY FB,
FA_TRANSACTION_HEADERS FTH

WHERE
1=1
AND FAB.ASSET_ID = FB.ASSET_ID
AND FB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND FB.DATE_INEFFECTIVE IS NOT NULL
) XYZ,
SEC_TBL ST --SECURITY ACCESS 

WHERE 
1=1
AND ABC.NEW_ASSET_ID = XYZ.OLD_ASSET_ID
AND XYZ.TRANSACTION_HEADER_ID_OUT = ABC.TRANSACTION_HEADER_ID_IN
AND XYZ.OLD_VALUE <> ABC.NEW_VALUE
AND ST.BOOK_TYPE_CODE = ABC.BOOK_TYPE_CODE --SECURITY
--AND NVL(XYZ.OLD_VALUE,'$$$$') <> NVL(ABC.NEW_VALUE,'$$$$')
AND (ABC.BOOK_TYPE_CODE IN (:P_BOOK) OR 'All' IN ('All'||:P_BOOK))
AND (ABC.ATTRIBUTE IN (:P_ATTRIBUTE) OR 'All' IN ('All'||:P_ATTRIBUTE))
AND TRUNC(ABC.LAST_UPDATE_DATE) BETWEEN :P_FROM AND :P_TO]]>

:P_ATTRIBUTE :P_BOOK :P_FROM :P_TO :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
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.

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
FA_ADDITIONS_B_	Setup / configuration table — joined for reference, not exposed for analytics
FA_BOOKS	20	2
FA_ADDITIONS_B	15	9
FA_TRANSACTION_HEADERS	7	5
FA_METHODS	8	2
FA_BONUS_RULES	2	1
FA_ASSET_HISTORY	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.