FA to GL Reconciliation Report

Fixed Assets

Reconciles the Assets subledger to the general ledger — comparing asset cost, accumulated depreciation, and depreciation expense in FA to what posted in GL — and isolating the entries that explain any difference.

Related Part of the subledger-to-GL reconciliation family — see also the Payables to Ledger and Receivables to General Ledger reconciliation reports.

Sample build of the FA to GL Reconciliation Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Book Code

CORP

Entity

Globex Holdings

Period1

FEB-26

Xdo User Name

Globex Holdings

$72.4M

FA cost in GL

Variance

Reconciled accounts

Account	Period	Fa Balance	Gl Balance	Variance	Status
1000-2100-000	APR-26	$1,240,500.00	$1,240,500.00	Sample	Open
1000-5400-000	MAR-26	$842,150.75	$842,150.75	—	Posted
1000-1410-000	FEB-26	$96,400.00	$96,400.00	Sample	Validated
2000-2100-000	JAN-26	$1,005,233.10	$1,005,233.10	—	Open
1000-6300-000	DEC-25	$58,720.40	$58,720.40	Sample	Paid
1000-2100-000	APR-26	$1,240,500.00	$1,240,500.00	Sample	Open

AI Analyst · active

reading

The report ties FA depreciation and distribution history to the GL code combinations, comparing FA-side and GL-side balances per account and period.

flag

Cost and depreciation reconcile to zero this period. The line to watch is the asset-clearing account, where a manual GL journal posted directly last period created a variance that took two periods to clear.

root cause & next step

Keep manual journals off the asset and accumulated-depreciation accounts — let FA post them — so this reconciliation stays at zero by design.

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

WITH COSTACC AS ( 
SELECT DISTINCT
	ACCOUNT.segment4
	,fcb.book_type_code  
FROM 
	fa_category_books fcb
	,gl_code_combinations ACCOUNT
WHERE 1=1
	AND fcb.deprn_expense_account_ccid=ACCOUNT.code_combination_id
	AND fcb.book_type_code=:BOOK_CODE
GROUP BY 
	ACCOUNT.segment4
	,fcb.book_type_code
	
union

SELECT
	DISTINCT 
	GCC.segment4
	,FDH.book_type_code
FROM
	FA_DISTRIBUTION_HISTORY FDH
	,GL_CODE_COMBINATIONS GCC
WHERE
	1=1
	AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
	AND FDH.BOOK_TYPE_CODE = :BOOK_CODE
),
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
)--SECURITY TABLE

, Q_PERIOD AS 
(
	SELECT
				BC.DISTRIBUTION_SOURCE_BOOK             dbk
			,	nvl (DP.PERIOD_CLOSE_DATE, sysdate)     ucd
			,	DP.PERIOD_COUNTER                       upc
			,	min (DP_FY.PERIOD_OPEN_DATE)            tod
			,	min (DP_FY.PERIOD_COUNTER)              tpc
	FROM
				FA_DEPRN_PERIODS   						DP
			,	FA_DEPRN_PERIODS   						DP_FY
			,	FA_BOOK_CONTROLS       		 			BC
	WHERE
				DP.BOOK_TYPE_CODE       =:BOOK_CODE              
			AND DP.PERIOD_NAME          =:P_PERIOD1              
			AND	DP_FY.BOOK_TYPE_CODE    =:BOOK_CODE              
			AND	DP_FY.FISCAL_YEAR       =DP.FISCAL_YEAR
			AND	BC.BOOK_TYPE_CODE       =:BOOK_CODE
	
	GROUP BY
			BC.DISTRIBUTION_SOURCE_BOOK,
			DP.PERIOD_CLOSE_DATE,
			DP.PERIOD_COUNTER
)
, Q_DD AS 
(
	SELECT  
			dd.Asset_ID,
			dd.Distribution_ID,
			MAX (dd.Period_Counter) mpc
			
	FROM    
			fa_deprn_detail dd,
			Q_PERIOD  QP2
			
	WHERE   
			dd.Book_Type_Code  =  :BOOK_CODE
	AND   dd.Period_Counter  <= QP2.upc
	
	GROUP BY 
			dd.Asset_ID, 
			dd.Distribution_ID
)
, EXPENSE_DETAILS AS (

SELECT
		ASSET_NUMBER
	,	BOOK_TYPE_CODE
	,	EXPENSE_ACCOUNT
	,	EXPENSE_ENTITY
	,	SUM(YTD_DEPRN) deprn_ytd
FROM 
(
SELECT
		AD.ASSET_NUMBER,
		BOOKS.BOOK_TYPE_CODE,
		decode (sign (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN) YTD_DEPRN,
		(SELECT DISTINCT GCC.SEGMENT4
           FROM gl_code_combinations GCC
          WHERE GCC.code_combination_id = DH.CODE_COMBINATION_ID) 	EXPENSE_ACCOUNT,
		(SELECT DISTINCT GCC.SEGMENT1
           FROM gl_code_combinations GCC
          WHERE GCC.code_combination_id = DH.CODE_COMBINATION_ID) 	EXPENSE_ENTITY
 
FROM
		FA_ADDITIONS_B         	AD,
        FA_DEPRN_DETAIL    		DD_BONUS,
        FA_ASSET_HISTORY        AH,
        FA_TRANSACTION_HEADERS  TH,
        FA_BOOKS           		BOOKS,
        FA_DISTRIBUTION_HISTORY DH,
		FA_CATEGORY_BOOKS       CB,
        Q_PERIOD                QP,
        Q_DD                    QDD
		
WHERE
		CB.BOOK_TYPE_CODE               =:BOOK_CODE                   
	AND	CB.CATEGORY_ID                  =AH.CATEGORY_ID
	AND AH.ASSET_ID                     =DH.ASSET_ID         
    AND AH.DATE_EFFECTIVE               <nvl(TH.DATE_EFFECTIVE, QP.ucd)  
    AND nvl(AH.DATE_INEFFECTIVE,sysdate)>=nvl(TH.DATE_EFFECTIVE, QP.ucd)  
    AND AH.ASSET_TYPE                   ='CAPITALIZED'
    AND AH.BOOK_TYPE_CODE               =:BOOK_CODE
	AND	DD_BONUS.BOOK_TYPE_CODE     	=:BOOK_CODE                   
    AND DD_BONUS.DISTRIBUTION_ID     	=DH.DISTRIBUTION_ID        
    AND DD_BONUS.Period_Counter      	=QDD.mpc 
    AND DD_BONUS.Distribution_ID     	=QDD.Distribution_ID 
    AND DD_BONUS.Asset_ID            	=QDD.Asset_ID
	AND	BOOKS.BOOK_TYPE_CODE            =:BOOK_CODE                 
    AND BOOKS.ASSET_ID                  =DH.ASSET_ID                
    AND AD.ASSET_ID                  	=DH.ASSET_ID                
    AND	nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, QP.upc) >= QP.tpc            
    AND BOOKS.DATE_EFFECTIVE            <=nvl(TH.DATE_EFFECTIVE, QP.ucd)
    AND nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, QP.ucd) 
	AND TH.BOOK_TYPE_CODE (+)           =:BOOK_CODE                     
    AND TH.TRANSACTION_HEADER_ID (+)    =DH.TRANSACTION_HEADER_ID_OUT
    AND TH.DATE_EFFECTIVE (+)           BETWEEN tod and ucd
	AND DH.BOOK_TYPE_CODE               =:BOOK_CODE            
    AND DH.DATE_EFFECTIVE               <=QP.ucd
    AND nvl(DH.DATE_INEFFECTIVE, sysdate)>QP.tod
	AND ((SELECT distinct GCC.segment1
           FROM gl_code_combinations GCC
          WHERE GCC.code_combination_id = DH.CODE_COMBINATION_ID) IN (:P_ENTITY) OR :P_ENTITY=1)

UNION ALL

SELECT 
		AD.ASSET_NUMBER,
		BOOKS.BOOK_TYPE_CODE ,
		decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.BONUS_YTD_DEPRN) YTD_DEPRN,
		(SELECT distinct GCC.SEGMENT4
           FROM gl_code_combinations GCC
          WHERE GCC.code_combination_id = DH.CODE_COMBINATION_ID) EXPENSE_ACCOUNT,
		(SELECT distinct GCC.SEGMENT1
           FROM gl_code_combinations GCC
          WHERE GCC.code_combination_id = DH.CODE_COMBINATION_ID) 	EXPENSE_ENTITY

FROM
		FA_ADDITIONS_B         	AD,
        FA_DEPRN_DETAIL    		DD,
        FA_ASSET_HISTORY        AH,
        FA_TRANSACTION_HEADERS  TH,
        FA_BOOKS           		BOOKS,
        FA_DISTRIBUTION_HISTORY DH,
        FA_CATEGORY_BOOKS       CB,
        Q_PERIOD                QP,
        Q_DD                    QDD

WHERE
			CB.BOOK_TYPE_CODE               =:BOOK_CODE                     
		AND CB.CATEGORY_ID                  =AH.CATEGORY_ID
		AND	AH.ASSET_ID                     =DH.ASSET_ID            
        AND AH.DATE_EFFECTIVE               <nvl(TH.DATE_EFFECTIVE, QP.ucd)
        AND nvl(AH.DATE_INEFFECTIVE,sysdate)>= nvl(TH.DATE_EFFECTIVE, QP.ucd) 
        AND AH.ASSET_TYPE                   ='CAPITALIZED' 
        AND AH.BOOK_TYPE_CODE               =:BOOK_CODE
		AND	DD.BOOK_TYPE_CODE               =:BOOK_CODE                          
        AND DD.DISTRIBUTION_ID           	=DH.DISTRIBUTION_ID        
        AND DD.Period_Counter      			=QDD.mpc 
        AND DD.Distribution_ID     			=QDD.Distribution_ID 
        AND DD.Asset_ID            			=QDD.Asset_ID
		AND	BOOKS.BOOK_TYPE_CODE            =:BOOK_CODE                          
		AND BOOKS.ASSET_ID                  =DH.ASSET_ID
		AND AD.ASSET_ID                  	=DH.ASSET_ID 		
		AND nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= QP.tpc             
        AND	BOOKS.DATE_EFFECTIVE            <= nvl(TH.DATE_EFFECTIVE, QP.ucd)  
        AND nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, QP.ucd) 
		AND	BOOKS.BONUS_RULE_ID IS NOT NULL
		AND	TH.BOOK_TYPE_CODE (+)           =:BOOK_CODE                     
		AND	TH.TRANSACTION_HEADER_ID (+)    =DH.TRANSACTION_HEADER_ID_OUT 
        AND TH.DATE_EFFECTIVE (+) BETWEEN QP.tod and QP.ucd
		AND	DH.BOOK_TYPE_CODE               =:BOOK_CODE                     
		AND DH.DATE_EFFECTIVE               <= QP.ucd 
		AND	nvl(DH.DATE_INEFFECTIVE, sysdate)> QP.tod
		AND ((SELECT distinct GCC.segment1
           FROM gl_code_combinations GCC
          WHERE GCC.code_combination_id = DH.CODE_COMBINATION_ID) IN (:P_ENTITY) OR :P_ENTITY=1)

)
GROUP BY
			ASSET_NUMBER, 
			EXPENSE_ACCOUNT,
			EXPENSE_ENTITY,
			BOOK_TYPE_CODE

)

select book_type_code
,	segment4_exp
,	NVL(sum(END_BAL_EXP),0)		end_bal_exp
,	NVL(sum(FA_COST),0)		E_6

FROM 
(
SELECT book_type_code
,	segment4_exp
,	end_bal_exp
,	0 FA_COST
from
(				  
SELECT 
       
      GCC.segment4 segment4_exp
	, GB.period_name
	, FBC.book_type_code
      ,SUM((NVL(GB.begin_balance_dr_beq,0))-(NVL(GB.begin_balance_cr_beq,0))+(NVL(GB.period_net_dr_beq,0))-(NVL(GB.period_net_cr_beq,0))) end_bal_exp

    FROM  gl_balances GB
     ,gl_code_combinations GCC
     ,gl_ledgers GL
     ,fa_book_controls FBC
     ,COSTACC
	 --,SEC_TBL ST
WHERE 1=1
--AND ST.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND GB.code_combination_id=GCC.code_combination_id
AND GB.ledger_id=GL.ledger_id 
AND GCC.chart_of_accounts_id=GL.chart_of_accounts_id
AND GL.ledger_id=FBC.set_of_books_id
AND GB.actual_flag='A' 
AND GB.period_name=:P_PERIOD1
AND FBC.book_type_code=:BOOK_CODE
AND (GCC.segment1 IN (:P_ENTITY)
OR  :P_ENTITY=1)
AND GCC.segment4=COSTACC.segment4
AND FBC.book_type_code=COSTACC.book_type_code
GROUP BY GCC.segment4, GB.period_name, FBC.book_type_code
)

UNION 

SELECT 	
		BOOK_TYPE_CODE 	   
	,	EXPENSE_ACCOUNT    segment4_exp
    ,	0				   end_bal_exp
	,	SUM(deprn_ytd) 	   FA_COST
FROM
		
		EXPENSE_DETAILS		ED

GROUP BY
		
		BOOK_TYPE_CODE
	,	EXPENSE_ACCOUNT
	
)

GROUP BY 
	book_type_code
,	segment4_exp

:BOOK_CODE :P_ENTITY :P_PERIOD1 :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
GL_CODE_COMBINATIONS	dimension	dimension
FA_DISTRIBUTION_HISTORY	dimension	dimension
FA_BOOK_CONTROLS	dimension	dimension
FUN_USER_ROLE_DATA_ASGNMNTS	dimension	dimension
Fa Balance	measure	measure
Gl Balance	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_CATEGORY_BOOKS	Setup / configuration table — joined for reference, not exposed for analytics
GL_CODE_COMBINATIONS	7	61
FA_DISTRIBUTION_HISTORY	6	2
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_DEPRN_PERIODS	1	8
FA_DEPRN_DETAIL	16	2
Q_PERIOD	Setup / configuration table — joined for reference, not exposed for analytics
FA_ADDITIONS_B	15	9

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.