Oracle Fusion · Fixed Assets
Asset Accounting and Location Report
Fixed Assets
Each asset's accounting and physical location together — cost, accumulated depreciation, and net book value alongside the assigned location and depreciation account — so finance can tie the asset register to both the GL and the physical inventory.
Sample build of the Asset Accounting and Location Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Asset Accounting and Location Report
Sample build · illustrativeFilters
Book Code
CORP
Asset Category
Vehicles
Asset Selection
Sample
Cost Center
$96,400.00
Entity Code
Globex Holdings
Period1
FEB-26
3,840
Active assets
$48.2M
Net book value
37
No location
| Asset Number | Description | Location | Cost | Accumulated Depreciation | Net Book Value | Expense Account |
|---|---|---|---|---|---|---|
| 1001 | Sample | Sample | $1,240,500.00 | Sample | CORP | 1000-2100-000 |
| 1002 | — | — | $842,150.75 | — | TAX FEDERAL | 1000-5400-000 |
| 1003 | Sample | Sample | $96,400.00 | Sample | CORP | 1000-1410-000 |
| 1004 | — | — | $1,005,233.10 | — | IFRS | 2000-2100-000 |
| 1005 | Sample | Sample | $58,720.40 | Sample | TAX STATE | 1000-6300-000 |
| 1001 | Sample | Sample | $1,240,500.00 | Sample | CORP | 1000-2100-000 |
AI Analyst · active
reading
The report joins FA_DEPRN_DETAIL and FA_BOOK_CONTROLS for the accounting figures and the assignment tables for location, per book and period.
flag
37 active assets have no physical location assigned — they cannot be found in a physical count, which is the first thing an asset auditor tests.
root cause & next step
Assign locations to the 37 assets; a block of unlocated assets usually traces to a mass addition loaded without the location segment.
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_DEPRN_PERIODS
- FA_BOOK_CONTROLS
- FA_DEPRN_DETAIL
- Q_PERIOD
- FA_ASSET_INVOICES
- FA_RETIREMENTS
- FA_TRANSACTION_HEADERS
- FA_METHODS
- PER_PERSON_NAMES_F
- FA_TRX_EXTRACT
- FA_ASSET_HISTORY
- FA_FLAT_RATES
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
<![CDATA[WITH 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
)
, FA_INVOICES AS
(
SELECT DISTINCT
LISTAGG(DISTINCT FEEDER_SYSTEM_NAME, ',') WITHIN GROUP (ORDER BY ASSET_ID) OVER (PARTITION BY ASSET_ID) FEEDER_SYSTEM_NAME
, ASSET_ID
FROM FA_ASSET_INVOICES
)
, FA_RETIREMENT_DET AS
(
SELECT DISTINCT
RET.RETIREMENT_ID
, RET.ASSET_ID
, RET.BOOK_TYPE_CODE
, RET.UNITS RET_UNITS
, RET.DATE_RETIRED
, RET.DATE_EFFECTIVE
, RET.TRANSACTION_HEADER_ID_IN
, RET.TRANSACTION_HEADER_ID_OUT
, TO_CHAR(FTH.TRANSACTION_DATE_ENTERED, 'MONTH')||'-'|| SUBSTR(EXTRACT(YEAR FROM FTH.TRANSACTION_DATE_ENTERED),3,2) PRD
, FDP.PERIOD_COUNTER
FROM
FA_RETIREMENTS RET
, FA_TRANSACTION_HEADERS FTH
, FA_DEPRN_PERIODS FDP
WHERE
FTH.TRANSACTION_HEADER_ID =RET.TRANSACTION_HEADER_ID_IN
AND FTH.ASSET_ID =RET.ASSET_ID
AND TRUNC(RET.DATE_RETIRED) BETWEEN TRUNC(SYSDATE,'YEAR') AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'), 12)-1
AND FDP.PERIOD_NAME = NVL(TO_CHAR(FTH.TRANSACTION_DATE_ENTERED, 'MONTH')||'-'|| SUBSTR(EXTRACT(YEAR FROM FTH.TRANSACTION_DATE_ENTERED),3,2),FDP.PERIOD_NAME)
)
, Q_MAIN AS
(
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DEPRN_EXPENSE_ACCOUNT_CCID,
CB.RESERVE_ACCOUNT_CCID RESERVE_ACCOUNT_CCID,
CB.ASSET_COST_ACCOUNT_CCID ,
BOOKS.BOOK_TYPE_CODE BOOK,
BOOKS.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
ROUND(BOOKS.SALVAGE_VALUE,2) SALVAGE_VALUE,
BOOKS.DEPRECIATE_FLAG,
(SELECT
NVL(MAX(CASE WHEN FTH.AMORTIZATION_START_DATE IS NOT NULL THEN 'Yes' END),'No')
FROM
FA_TRANSACTION_HEADERS FTH
WHERE
BOOKS.ASSET_ID =FTH.ASSET_ID
AND BOOKS.BOOK_TYPE_CODE=FTH.BOOK_TYPE_CODE
) AMORTIZE_FLAG,
MTH.METHOD_CODE METHOD_CODE,
MTH.LIFE_IN_MONTHS LIFE,
FCT.PRORATE_CONVENTION_CODE ,
FR.ADJUSTED_RATE RATE,
DD_BONUS.COST COST,
decode (DD_BONUS.PERIOD_COUNTER, upc, DD_BONUS.DEPRN_AMOUNT - DD_BONUS.BONUS_DEPRN_AMOUNT, 0) DEPRN_AMOUNT,
decode (sign (tpc - DD_BONUS.PERIOD_COUNTER), 1, 0, DD_BONUS.YTD_DEPRN - DD_BONUS.BONUS_YTD_DEPRN)
YTD_DEPRN,
DD_BONUS.DEPRN_RESERVE - DD_BONUS.BONUS_DEPRN_RESERVE DEPRN_RESERVE,
decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') TRANSACTION_TYPE,
DD_BONUS.PERIOD_COUNTER PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd) DATE_EFFECTIVE,
DH.UNITS_ASSIGNED UNITS,
decode(dd_bonus.deprn_source_code,'B', dd_bonus.addition_cost_to_clear, dd_bonus.cost) - DD_bonus.DEPRN_RESERVE - nvl(DD_BONUS.IMPAIRMENT_RESERVE,0) PERIOD_END_NBV,
MTH.USE_LIFE_IN_PERIODS_FLAG USE_LIFE_IN_PERIODS_FLAG,
/*CASE
WHEN (
SELECT
life_in_months
FROM
fa_methods
WHERE
method_id = BOOKS.method_id
) - ceil((sysdate - BOOKS.date_placed_in_service) / 30.4167) > 0 THEN
(
SELECT
life_in_months
FROM
fa_methods
WHERE
method_id = BOOKS.method_id
) - ceil((sysdate - BOOKS.date_placed_in_service) / 30.4167)
ELSE
0
END REMAINING_USEFUL_LIFE,*/
CASE WHEN BOOKS.BOOK_TYPE_CODE='IT FA STAT BOOK EUR' THEN
(CASE
WHEN GREATEST(0,MTH.LIFE_IN_MONTHS - (TRUNC(MONTHS_BETWEEN(LAST_DAY(TO_DATE(TRIM(:P_PERIOD1) || '12', 'YYYYMM')),LAST_DAY(BOOKS.date_placed_in_service))/12) * 12)) > 0 THEN
GREATEST(0,MTH.LIFE_IN_MONTHS - (TRUNC(MONTHS_BETWEEN(LAST_DAY(TO_DATE(TRIM(:P_PERIOD1) || '12', 'YYYYMM')),LAST_DAY(BOOKS.date_placed_in_service))/12) * 12))
ELSE
0
END)
ELSE
(CASE
WHEN MTH.LIFE_IN_MONTHS - ROUND(MONTHS_BETWEEN(LAST_DAY(TO_DATE(:P_PERIOD1,'MM-YY')),LAST_DAY(BOOKS.date_placed_in_service))) > 0 THEN
MTH.LIFE_IN_MONTHS - ROUND(MONTHS_BETWEEN(LAST_DAY(TO_DATE(:P_PERIOD1,'MM-YY')),LAST_DAY(BOOKS.date_placed_in_service)))
ELSE
0
END) END REMAINING_USEFUL_LIFE,
--MAX(DEPRN_RUN_DATE) OVER (PARTITION BY DH.ASSET_ID, BOOKS.BOOK_TYPE_CODE) DEPRN_RUN_DATE ,
FL.segment1||'.'||FL.segment2||'.'||FL.segment3||'.'||FL.segment4||'.'||FL.segment5 LOCATION,
FRT.DATE_RETIRED DATE_RETIRED,
FRT.RET_UNITS UNITS_RETIRED,
(
SELECT
DISTINCT PPNF.DISPLAY_NAME
FROM
PER_PERSON_NAMES_F PPNF
, FA_TRX_EXTRACT FTE
WHERE
FTE.EMPLOYEE_ID =PPNF.PERSON_ID
AND PPNF.NAME_TYPE ='GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND FTE.ASSET_ID =BOOKS.ASSET_ID
AND FTE.BOOK_TYPE_CODE =BOOKS.BOOK_TYPE_CODE
AND FTE.BOOKS_TRANSACTION_HEADER_ID =BOOKS.TRANSACTION_HEADER_ID_IN
) EMPLOYEE
FROM
FA_DEPRN_DETAIL DD_BONUS,
FA_ASSET_HISTORY AH,
FA_METHODS MTH,
FA_FLAT_RATES FR,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS BOOKS,
FA_BOOK_CONTROLS FBC,
FA_DISTRIBUTION_HISTORY DH,
FA_LOCATIONS FL,
FA_CATEGORY_BOOKS CB,
FA_CONVENTION_TYPES FCT,
Q_PERIOD QP,
Q_DD QDD,
FA_RETIREMENT_DET FRT
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
TH_RT.BOOK_TYPE_CODE = :BOOK_CODE AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND TH_RT.ASSET_ID = FRT.ASSET_ID(+)
AND TH_RT.TRANSACTION_HEADER_ID = FRT.TRANSACTION_HEADER_ID_IN(+)
AND
BOOKS.BOOK_TYPE_CODE = :BOOK_CODE AND
BOOKS.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
BOOKS.METHOD_ID = MTH.METHOD_ID AND
BOOKS.METHOD_ID = FR.METHOD_ID(+) AND
BOOKS.FLAT_RATE_ID = FR.FLAT_RATE_ID(+)
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 BOOKS.CONVENTION_TYPE_ID = FCT.CONVENTION_TYPE_ID
AND BOOKS.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND DH.LOCATION_ID = FL.LOCATION_ID
UNION ALL
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DEPRN_EXPENSE_ACCOUNT_CCID,
CB.BONUS_RESERVE_ACCT_CCID RESERVE_ACCOUNT_CCID,
CB.ASSET_COST_ACCOUNT_CCID,
BOOKS.BOOK_TYPE_CODE BOOK,
BOOKS.DATE_PLACED_IN_SERVICE DATE_PLACED_IN_SERVICE,
ROUND(BOOKS.SALVAGE_VALUE,2) SALVAGE_VALUE,
BOOKS.DEPRECIATE_FLAG,
(SELECT
NVL(MAX(CASE WHEN FTH.AMORTIZATION_START_DATE IS NOT NULL THEN 'Yes' END),'No')
FROM
FA_TRANSACTION_HEADERS FTH
WHERE
BOOKS.ASSET_ID =FTH.ASSET_ID
AND BOOKS.BOOK_TYPE_CODE=FTH.BOOK_TYPE_CODE
) AMORTIZE_FLAG,
MTH.METHOD_CODE METHOD_CODE,
MTH.LIFE_IN_MONTHS LIFE,
FCT.PRORATE_CONVENTION_CODE,
FR.ADJUSTED_RATE RATE,
0 COST,
decode (DD.PERIOD_COUNTER, upc, DD.BONUS_DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.BONUS_YTD_DEPRN)
YTD_DEPRN,
DD.BONUS_DEPRN_RESERVE DEPRN_RESERVE,
0 PERCENT,
'B' TRANSACTION_TYPE,
DD.PERIOD_COUNTER PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd) DATE_EFFECTIVE,
DH.UNITS_ASSIGNED UNITS,
decode(dd.deprn_source_code,'B', dd.addition_cost_to_clear, dd.cost) - DD.DEPRN_RESERVE PERIOD_END_NBV,
MTH.USE_LIFE_IN_PERIODS_FLAG USE_LIFE_IN_PERIODS_FLAG,
/* CASE
WHEN (
SELECT
life_in_months
FROM
fa_methods
WHERE
method_id = BOOKS.method_id
) - ceil((sysdate - BOOKS.date_placed_in_service) / 30.4167) > 0 THEN
(
SELECT
life_in_months
FROM
fa_methods
WHERE
method_id = BOOKS.method_id
) - ceil((sysdate - BOOKS.date_placed_in_service) / 30.4167)
ELSE
0
END REMAINING_USEFUL_LIFE, */
CASE WHEN BOOKS.BOOK_TYPE_CODE='IT FA STAT BOOK EUR' THEN
(CASE
WHEN GREATEST(0,MTH.LIFE_IN_MONTHS - (TRUNC(MONTHS_BETWEEN(LAST_DAY(TO_DATE(TRIM(:P_PERIOD1) || '12', 'YYYYMM')),LAST_DAY(BOOKS.date_placed_in_service))/12) * 12)) > 0 THEN
GREATEST(0,MTH.LIFE_IN_MONTHS - (TRUNC(MONTHS_BETWEEN(LAST_DAY(TO_DATE(TRIM(:P_PERIOD1) || '12', 'YYYYMM')),LAST_DAY(BOOKS.date_placed_in_service))/12) * 12))
ELSE
0
END)
ELSE
(CASE
WHEN MTH.LIFE_IN_MONTHS - ROUND(MONTHS_BETWEEN(LAST_DAY(TO_DATE(:P_PERIOD1,'MM-YY')),LAST_DAY(BOOKS.date_placed_in_service))) > 0 THEN
MTH.LIFE_IN_MONTHS - ROUND(MONTHS_BETWEEN(LAST_DAY(TO_DATE(:P_PERIOD1,'MM-YY')),LAST_DAY(BOOKS.date_placed_in_service)))
ELSE
0
END) END REMAINING_USEFUL_LIFE,
--MAX(DEPRN_RUN_DATE) OVER (PARTITION BY DH.ASSET_ID, BOOKS.BOOK_TYPE_CODE) DEPRN_RUN_DATE,
FL.segment1||'.'||FL.segment2||'.'||FL.segment3||'.'||FL.segment4||'.'||FL.segment5 LOCATION,
FRT.DATE_RETIRED DATE_RETIRED,
FRT.RET_UNITS UNITS_RETIRED,
(
SELECT
DISTINCT PPNF.DISPLAY_NAME
FROM
PER_PERSON_NAMES_F PPNF
, FA_TRX_EXTRACT FTE
WHERE
FTE.EMPLOYEE_ID =PPNF.PERSON_ID
AND PPNF.NAME_TYPE ='GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND FTE.ASSET_ID =BOOKS.ASSET_ID
AND FTE.BOOK_TYPE_CODE =BOOKS.BOOK_TYPE_CODE
AND FTE.BOOKS_TRANSACTION_HEADER_ID =BOOKS.TRANSACTION_HEADER_ID_IN
) EMPLOYEE
FROM
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_METHODS MTH,
FA_FLAT_RATES FR,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS BOOKS,
FA_BOOK_CONTROLS FBC,
FA_DISTRIBUTION_HISTORY DH,
FA_LOCATIONS FL,
FA_CATEGORY_BOOKS CB,
FA_CONVENTION_TYPES FCT,
Q_PERIOD QP,
Q_DD QDD,
FA_RETIREMENT_DET FRT
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
TH_RT.BOOK_TYPE_CODE = :BOOK_CODE AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND TH_RT.ASSET_ID = FRT.ASSET_ID(+)
AND TH_RT.TRANSACTION_HEADER_ID = FRT.TRANSACTION_HEADER_ID_IN(+)
AND
BOOKS.BOOK_TYPE_CODE = :BOOK_CODE AND
BOOKS.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
BOOKS.METHOD_ID = MTH.METHOD_ID AND
BOOKS.METHOD_ID = FR.METHOD_ID(+) AND
BOOKS.FLAT_RATE_ID = FR.FLAT_RATE_ID(+)
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 BOOKS.CONVENTION_TYPE_ID = FCT.CONVENTION_TYPE_ID
AND BOOKS.BOOK_TYPE_CODE = FBC.BOOK_TYPE_CODE
AND DH.LOCATION_ID = FL.LOCATION_ID
)
SELECT
ASSET_NUMBER
, SERIAL_NUMBER
, MODEL_NUMBER
, TAG_NUMBER
, MANUFACTURER
, LEASE_NUMBER
, DESCRIPTION
, MAJOR_CAT
, MINOR_CAT
, BOOK
, DATE_OF_ACQUISITION
, SALVAGE_VALUE
, REMAINING_USEFUL_LIFE
, DEPRECIATE_FLAG
, DEPRN_METHOD
, LIFE_IN_MONTHS
, PRORATE_CONVENTION
, ASSET_COST_ACCOUNT
, DEPRECIATION_EXPENSE_ACCT
, BALANCING_SEGMENT
, TOOL_PROCESS_CODE
, BRAND
, TOOL_CLASS
, SKU
, INTERCO_ASSET_TRANSFER
, LEGACY_ASSET_NUMBER
, TOOL_TONNAGE
, TOOL_MAKER
, AMORTIZE_FLAG
, SOURCE
, PROJECT_NAME
, PROJECT_NUMBER
, UNITS
, SUM(COST) COST
, SUM(YTD_DEPRN) YTD_DEPRECIATION
, BONUS_DEPRECIATION
, SUM(DEPRN_RESERVE) LTD_DEPRECIATION
, SUM(PERIOD_END_NBV) NET_BOOK_VALUE
, LOCATION
, DATE_RETIRED
, UNITS_RETIRED
, EMPLOYEE
FROM
(
/*All Assets*/
SELECT
AD.ASSET_NUMBER ASSET_NUMBER
, AD.SERIAL_NUMBER SERIAL_NUMBER
, AD.MODEL_NUMBER MODEL_NUMBER
, AD.TAG_NUMBER TAG_NUMBER
, AD.MANUFACTURER_NAME MANUFACTURER
, AD.ATTRIBUTE1 LEASE_NUMBER
, ADTL.DESCRIPTION DESCRIPTION
, FCAT.SEGMENT1 MAJOR_CAT
, FCAT.SEGMENT2 MINOR_CAT
, RSV.BOOK
, RSV.DATE_PLACED_IN_SERVICE DATE_OF_ACQUISITION
, RSV.SALVAGE_VALUE
, RSV.REMAINING_USEFUL_LIFE
, RSV.DEPRECIATE_FLAG
, RSV.METHOD_CODE DEPRN_METHOD
, RSV.LIFE LIFE_IN_MONTHS
, RSV.PRORATE_CONVENTION_CODE PRORATE_CONVENTION
, (SELECT DISTINCT GCC.SEGMENT4
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = RSV.ASSET_COST_ACCOUNT_CCID)
ASSET_COST_ACCOUNT
, CC.SEGMENT1||'.'||CC.SEGMENT2||'.'||CC.SEGMENT3||'.'||CC.SEGMENT4||'.'||CC.SEGMENT5||'.'||CC.SEGMENT6||'.'||CC.SEGMENT7||'.'||CC.SEGMENT8||'.'||CC.SEGMENT9 DEPRECIATION_EXPENSE_ACCT
, CC.SEGMENT1 BALANCING_SEGMENT
, AK.SEGMENT1 TOOL_PROCESS_CODE
, AK.segment2 BRAND
, AK.SEGMENT3 TOOL_CLASS
, AK.SEGMENT4 SKU
, AK.SEGMENT5 INTERCO_ASSET_TRANSFER
, AK.SEGMENT6 LEGACY_ASSET_NUMBER
, AK.SEGMENT7 TOOL_TONNAGE
, AK.SEGMENT8 TOOL_MAKER
, RSV.AMORTIZE_FLAG
, FINV.FEEDER_SYSTEM_NAME SOURCE
, (SELECT DISTINCT
PROJECT.NAME
FROM
PJF_PROJECTS_ALL_VL PROJECT
, FA_ASSET_INVOICES FAI
WHERE
FAI.PROJECT_ID =PROJECT.PROJECT_ID
AND FAI.ASSET_ID =AD.ASSET_ID
) PROJECT_NAME
, (SELECT DISTINCT
PROJECT.SEGMENT1
FROM
PJF_PROJECTS_ALL_VL PROJECT
, FA_ASSET_INVOICES FAI
WHERE
FAI.PROJECT_ID =PROJECT.PROJECT_ID
AND FAI.ASSET_ID =AD.ASSET_ID
) PROJECT_NUMBER
, RSV.UNITS UNITS
, DECODE(TRANSACTION_TYPE,'B',NULL,COST) COST
, RSV.YTD_DEPRN
, ROUND(DS.BONUS_DEPRN_AMOUNT,2) BONUS_DEPRECIATION
, RSV.DEPRN_RESERVE
, DECODE(TRANSACTION_TYPE,'B',NULL,RSV.PERIOD_END_NBV) PERIOD_END_NBV
, RSV.LOCATION
, RSV.DATE_RETIRED
, RSV.UNITS_RETIRED
, RSV.EMPLOYEE
FROM
Q_MAIN RSV
, FA_ADDITIONS_B AD
, FA_ADDITIONS_TL ADTL
, GL_CODE_COMBINATIONS CC
, GL_CODE_COMBINATIONS CCRA
, FA_CATEGORIES_B FCAT
, FA_ASSET_KEYWORDS AK
, FA_DEPRN_SUMMARY DS
, FA_INVOICES FINV
WHERE
RSV.ASSET_ID =AD.ASSET_ID
AND RSV.DEPRN_EXPENSE_ACCOUNT_CCID =CC.CODE_COMBINATION_ID
AND RSV.RESERVE_ACCOUNT_CCID =CCRA.CODE_COMBINATION_ID
AND RSV.PERIOD_COUNTER =DS.PERIOD_COUNTER(+)
AND DS.BOOK_TYPE_CODE(+) =:BOOK_CODE
AND RSV.ASSET_ID =DS.ASSET_ID (+)
AND AD.ASSET_CATEGORY_ID =FCAT.CATEGORY_ID
AND AD.ASSET_ID =ADTL.ASSET_ID
AND ADTL.LANGUAGE =USERENV('LANG')
AND AD.ASSET_KEY_CCID =AK.CODE_COMBINATION_ID(+)
AND AD.ASSET_ID =FINV.ASSET_ID(+)
--PARAMETERS
AND (FCAT.CATEGORY_ID in (:P_ASSET_CATEGORY) OR 'All' IN ('All'||:P_ASSET_CATEGORY))
AND (CC.SEGMENT1 IN (:P_ENTITY_CODE) OR 'All' IN ('All'||:P_ENTITY_CODE))
AND (CC.SEGMENT3 IN (:P_COST_CENTER) OR 'All' IN ('All'||:P_COST_CENTER))
AND :P_ASSET_SELECTION ='ALL_ASSETS'
UNION
/*ACTIVE Assets*/
SELECT
AD.ASSET_NUMBER ASSET_NUMBER
, AD.SERIAL_NUMBER SERIAL_NUMBER
, AD.MODEL_NUMBER MODEL_NUMBER
, AD.TAG_NUMBER TAG_NUMBER
, AD.MANUFACTURER_NAME MANUFACTURER
, AD.ATTRIBUTE1 LEASE_NUMBER
, ADTL.DESCRIPTION DESCRIPTION
, FCAT.SEGMENT1 MAJOR_CAT
, FCAT.SEGMENT2 MINOR_CAT
, RSV.BOOK
, RSV.DATE_PLACED_IN_SERVICE DATE_OF_ACQUISITION
, RSV.SALVAGE_VALUE
, RSV.REMAINING_USEFUL_LIFE
, RSV.DEPRECIATE_FLAG
, RSV.METHOD_CODE DEPRN_METHOD
, RSV.LIFE LIFE_IN_MONTHS
, RSV.PRORATE_CONVENTION_CODE PRORATE_CONVENTION
, (SELECT DISTINCT GCC.SEGMENT4
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID = RSV.ASSET_COST_ACCOUNT_CCID)
ASSET_COST_ACCOUNT
, CC.SEGMENT1||'.'||CC.SEGMENT2||'.'||CC.SEGMENT3||'.'||CC.SEGMENT4||'.'||CC.SEGMENT5||'.'||CC.SEGMENT6||'.'||CC.SEGMENT7||'.'||CC.SEGMENT8||'.'||CC.SEGMENT9 DEPRECIATION_EXPENSE_ACCT
, CC.SEGMENT1 BALANCING_SEGMENT
, AK.SEGMENT1 TOOL_PROCESS_CODE
, AK.segment2 BRAND
, AK.SEGMENT3 TOOL_CLASS
, AK.SEGMENT4 SKU
, AK.SEGMENT5 INTERCO_ASSET_TRANSFER
, AK.SEGMENT6 LEGACY_ASSET_NUMBER
, AK.SEGMENT7 TOOL_TONNAGE
, AK.SEGMENT8 TOOL_MAKER
, RSV.AMORTIZE_FLAG
, FINV.FEEDER_SYSTEM_NAME SOURCE
, (SELECT DISTINCT
PROJECT.NAME
FROM
PJF_PROJECTS_ALL_VL PROJECT
, FA_ASSET_INVOICES FAI
WHERE
FAI.PROJECT_ID =PROJECT.PROJECT_ID
AND FAI.ASSET_ID =AD.ASSET_ID
) PROJECT_NAME
, (SELECT DISTINCT
PROJECT.SEGMENT1
FROM
PJF_PROJECTS_ALL_VL PROJECT
, FA_ASSET_INVOICES FAI
WHERE
FAI.PROJECT_ID =PROJECT.PROJECT_ID
AND FAI.ASSET_ID =AD.ASSET_ID
) PROJECT_NUMBER
, RSV.UNITS UNITS
, DECODE(TRANSACTION_TYPE,'B',NULL,COST) COST
, RSV.YTD_DEPRN
, ROUND(DS.BONUS_DEPRN_AMOUNT,2) BONUS_DEPRECIATION
, RSV.DEPRN_RESERVE
, DECODE(TRANSACTION_TYPE,'B',NULL,RSV.PERIOD_END_NBV) PERIOD_END_NBV
, RSV.LOCATION
, RSV.DATE_RETIRED
, RSV.UNITS_RETIRED
, RSV.EMPLOYEE
FROM
Q_MAIN RSV
, FA_ADDITIONS_B AD
, FA_ADDITIONS_TL ADTL
, GL_CODE_COMBINATIONS CC
, GL_CODE_COMBINATIONS CCRA
, FA_CATEGORIES_B FCAT
, FA_ASSET_KEYWORDS AK
, FA_DEPRN_SUMMARY DS
, FA_INVOICES FINV
WHERE
RSV.ASSET_ID =AD.ASSET_ID
AND RSV.DEPRN_EXPENSE_ACCOUNT_CCID =CC.CODE_COMBINATION_ID
AND RSV.RESERVE_ACCOUNT_CCID =CCRA.CODE_COMBINATION_ID
AND RSV.PERIOD_COUNTER =DS.PERIOD_COUNTER(+)
AND DS.BOOK_TYPE_CODE(+) =:BOOK_CODE
AND RSV.ASSET_ID =DS.ASSET_ID(+)
AND AD.ASSET_CATEGORY_ID =FCAT.CATEGORY_ID
AND AD.ASSET_ID =ADTL.ASSET_ID
AND ADTL.LANGUAGE =USERENV('LANG')
AND AD.ASSET_KEY_CCID =AK.CODE_COMBINATION_ID(+)
AND AD.ASSET_ID =FINV.ASSET_ID(+)
--PARAMETERS
AND (FCAT.CATEGORY_ID in (:P_ASSET_CATEGORY) OR 'All' IN ('All'||:P_ASSET_CATEGORY))
AND (CC.SEGMENT1 IN (:P_ENTITY_CODE) OR 'All' IN ('All'||:P_ENTITY_CODE))
AND (CC.SEGMENT3 IN (:P_COST_CENTER) OR 'All' IN ('All'||:P_COST_CENTER))
--AND FTH.TRANSACTION_TYPE_CODE <> 'FULL RETIREMENT'
AND :P_ASSET_SELECTION = 'ACTIVE_ASSETS_ONLY'
-- Remove Reitired Assests
AND NOT EXISTS (SELECT RT.ASSET_ID FROM FA_RETIREMENTS RT, FA_TRANSACTION_HEADERS FT
WHERE AD.ASSET_ID =RT.ASSET_ID
--AND RT.DATE_RETIRED<TRUNC(SYSDATE,'YEAR')
AND FT.ASSET_ID =RT.ASSET_ID
AND RSV.BOOK =RT.BOOK_TYPE_CODE
AND FT.TRANSACTION_HEADER_ID =RT.TRANSACTION_HEADER_ID_IN
AND FT.TRANSACTION_TYPE_CODE ='FULL RETIREMENT'
AND RT.STATUS!= 'DELETED'
)
)
GROUP BY
ASSET_NUMBER
, SERIAL_NUMBER
, MODEL_NUMBER
, TAG_NUMBER
, MANUFACTURER
, LEASE_NUMBER
, DESCRIPTION
, MAJOR_CAT
, MINOR_CAT
, BOOK
, DATE_OF_ACQUISITION
, SALVAGE_VALUE
, REMAINING_USEFUL_LIFE
, DEPRECIATE_FLAG
, DEPRN_METHOD
, LIFE_IN_MONTHS
, PRORATE_CONVENTION
, ASSET_COST_ACCOUNT
, DEPRECIATION_EXPENSE_ACCT
, BALANCING_SEGMENT
, TOOL_PROCESS_CODE
, BRAND
, TOOL_CLASS
, SKU
, INTERCO_ASSET_TRANSFER
, LEGACY_ASSET_NUMBER
, TOOL_TONNAGE
, TOOL_MAKER
, AMORTIZE_FLAG
, SOURCE
, PROJECT_NAME
, PROJECT_NUMBER
, UNITS
, BONUS_DEPRECIATION
, LOCATION
, DATE_RETIRED
, UNITS_RETIRED
, EMPLOYEE]]>:BOOK_CODE :P_ASSET_CATEGORY :P_ASSET_SELECTION :P_COST_CENTER :P_ENTITY_CODE :P_PERIOD1
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_BOOK_CONTROLS | dimension | dimension |
| FA_DEPRN_DETAIL | dimension | dimension |
| Q_PERIOD | dimension | dimension |
| FA_ASSET_INVOICES | dimension | dimension |
| Cost | measure | measure |
| Net Book Value | 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_DEPRN_PERIODS | 1 | 8 |
| FA_BOOK_CONTROLS | 29 | 13 |
| FA_DEPRN_DETAIL | 16 | 2 |
| Q_PERIOD | Setup / configuration table — joined for reference, not exposed for analytics | |
| FA_ASSET_INVOICES | 15 | 2 |
| FA_RETIREMENTS | Setup / configuration table — joined for reference, not exposed for analytics | |
| FA_TRANSACTION_HEADERS | 7 | 5 |
| FA_METHODS | 8 | 2 |
| PER_PERSON_NAMES_F | 160 | 0 |
| FA_TRX_EXTRACT | Setup / configuration table — joined for reference, not exposed for analytics | |
| FA_ASSET_HISTORY | Setup / configuration table — joined for reference, not exposed for analytics | |
| FA_FLAT_RATES | 3 | 1 |
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.