Lease Activity Detail Report

Lease Accounting

The accounting activity on each lease in a period — interest accretion, liability reduction, right-of-use amortization, and any remeasurement — so finance can see how each lease's liability and ROU asset moved and tie it to the journals.

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

Sample build · illustrative

Filters

Sample

Change Typ

Sample

From Amnd Date

2026-02-28

Lease Name

Globex Holdings

To Amnd Date

2026-02-28

240

Active leases

$31.2M

Lease liability

$118K

Period interest

Lease	Period	Opening Liability	Interest	Payment	Closing Liability	Rou Amortization
Sample	APR-26	Sample	Sample	Sample	Sample	Sample
—	MAR-26	—	—	—	—	—
Sample	FEB-26	Sample	Sample	Sample	Sample	Sample
—	JAN-26	—	—	—	—	—
Sample	DEC-25	Sample	Sample	Sample	Sample	Sample
Sample	APR-26	Sample	Sample	Sample	Sample	Sample

AI Analyst · active

reading

The report reads the lease schedules in FLA_SCHEDULES and FLA_SCHEDULE_HEADERS, rolling each lease's opening liability, interest, and payment into a closing position.

flag

A few leases show a liability change with no schedule line — a mid-term remeasurement (a CPI or term change) that re-cut the schedule, which is correct but worth confirming the new terms.

root cause & next step

Verify the remeasured leases against the amendment; an unexpected remeasurement usually means a CPI index or option assumption changed in the lease setup.

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

FLA_LEASES_ALL
FLA_SCHEDULES
FLA_SCHEDULE_HEADERS
FLA_LEASE_DETAILS
GL_DAILY_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.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

SELECT
LEASE_ID
,LEASE_REF LEASE_REF
,LEASE_NUM LEASE_NUM
,LEGAL_ENTITY LEGAL_ENTITY
,AMENDMENT_DATE AMENDMENT_DATE
,AMEND_MONTH AMEND_MONTH
,Amend_month_1
,NVL(Change_Type,'New Lease') AMEND_SUBTYPE
,VERSION_NUM
,prior_ver
,LEASE_DETAIL_ID
,LEASE_CLASSIFICATION_CODE
, Lease_Classification
,Prior_day
, ((LAG(Liability_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM))* NVL(USD_Conversion_rate, 1)) PRIOR_LIAB_USD
, (LAG(Liability_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM)) PRIOR_LIAB_LOC
,Liability_Aft_Chg AFTER_LIAB_BAL_LOC
,(Liability_Aft_Chg * NVL(USD_Conversion_rate, 1)) AFTER_LIAB_BAL_USD
,ROU_Aft_Chg AFTER_ROU_BAL_LOC
, (LAG(ROU_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM)) PRIOR_ROU_LOC
,(ROU_Aft_Chg * NVL(USD_Conversion_rate, 1)) AFTER_ROU_BAL_USD
,((LAG(ROU_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM)) * NVL(USD_Conversion_rate, 1)) PRIOR_ROU_USD
, (ROU_Aft_Chg  - (LAG(ROU_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM))) NET_CHANGE_GROSS_ROU_LOC
, ((ROU_Aft_Chg  - (LAG(ROU_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM))) * NVL(USD_Conversion_rate, 1)) NET_CHANGE_GROSS_ROU_USD
, (CASE WHEN (ROU_Aft_Chg  - (LAG(ROU_Aft_Chg,1,0) OVER (PARTITION BY lease_id	ORDER BY VERSION_NUM))) <> 0 THEN 'Yes' ELSE 'No' END) IMPCT_ROU_FLAG
,REGIME_CODE
,CURRENCY_CODE CURRENCY_CODE,
NVL(USD_Conversion_rate, 1) USD_Conversion_rate,
Curr_Symbol
FROM(
WITH FLA_SCHEDULES AS(
SELECT DISTINCT
LEASE_ID,
(CASE WHEN CURRENCY_CODE = 'GBP' THEN 'ASC842' ELSE REGIME_CODE END) REGIME_CODE,
CURRENCY_CODE
FROM FLA_SCHEDULE_HEADERS)

SELECT 
FLA.LEASE_ID,
TRIM(FLA.LEASE_NAME) LEASE_REF,
FLA.LEASE_NUMBER LEASE_NUM,
(SELECT LEGAL_ENTITY_IDENTIFIER FROM XLE_ENTITY_PROFILES XEP WHERE XEP.LEGAL_ENTITY_ID = FLA.LEGAL_ENTITY_ID) LEGAL_ENTITY,
FLD.COMMENCEMENT_DATE AMENDMENT_DATE,
DECODE(LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0'),'01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','Jun','07','Jul','08','Aug','09','Sep','10','Oct','11','Nov','12','Dec')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) AMEND_MONTH,
LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) Amend_month_1,
CASE WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='EXPIRY' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='IMPAIRMENT' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='TERMINATION' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='SCOPE DECREASE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)
ELSE FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE) END Change_Type,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1 prior_ver,
FLD.LEASE_DETAIL_ID,
FLD.LEASE_CLASSIFICATION_CODE,
fla_common_utils_pkg.get_lookup_meaning('ORA_FLA_LEASE_CLASSIFICATION',fld.lease_classification_code) Lease_Classification,
(FLD.COMMENCEMENT_DATE - 1) Prior_day,
SUM(CASE WHEN FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_OPENING_LIABILITY_AMOUNT ELSE FSSV.IFRS_OPENING_LIABILITY_AMOUNT END) Liability_Aft_Chg,
SUM(CASE WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_OPENING_OPER_ROU_AMOUNT WHEN FLD.LEASE_CLASSIFICATION_CODE = 'FINANCE' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_OPENING_FIN_ROU_AMOUNT 
WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'IFRS16' THEN FSSV.IFRS_OPENING_OPER_ROU_AMOUNT ELSE FSSV.IFRS_OPENING_ROU_AMOUNT END) ROU_Aft_Chg,
FS.REGIME_CODE,
FS.CURRENCY_CODE,
(CASE WHEN FS.CURRENCY_CODE = 'USD' THEN 1 ELSE (SELECT GDR.CONVERSION_RATE FROM GL_DAILY_RATES GDR WHERE GDR.FROM_CURRENCY = FS.CURRENCY_CODE AND GDR.TO_CURRENCY = 'USD' AND GDR.CONVERSION_DATE = LAST_DAY(FLD.COMMENCEMENT_DATE) AND UPPER(GDR.CONVERSION_TYPE) = 'CORPORATE') END) USD_Conversion_rate,
(SELECT SYMBOL FROM FND_CURRENCIES WHERE CURRENCY_CODE = FS.CURRENCY_CODE) Curr_Symbol

FROM 
FLA_LEASES_ALL FLA,
FLA_LEASE_DETAILS_HIST FLD,
fla_schedule_summary_v FSSV,
FLA_SCHEDULES FS--,
--fla_schedule_summary_v FSSV_ROU

WHERE 1=1 
--FLD.LEASE_ID = 300000006201967--100000029457993 
AND FS.LEASE_ID = FLD.LEASE_ID
AND FLD.VERSION_TYPE_CODE = 'H'
AND FLA.LEASE_ID = FLD.LEASE_ID
AND FSSV.LEASE_ID = FLD.LEASE_ID
--AND FSSV_ROU.LEASE_ID = FLD.LEASE_ID
--AND FSSV_ROU.PAYMENT_NUMBER = FSSV.PAYMENT_NUMBER
AND FLD.COMMENCEMENT_DATE IN (FSSV.SCHEDULE_START_DATE, FSSV.SCHEDULE_END_DATE+1)
--AND FSSV_ROU.SCHEDULE_END_DATE = FLD.COMMENCEMENT_DATE - 1
AND LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) = FSSV.PERIOD_NAME

--Parameters

 AND TRUNC(FLD.COMMENCEMENT_DATE) BETWEEN TRUNC(:P_FROM_AMND_DATE) 
AND TRUNC(:P_TO_AMND_DATE)
AND (FLA.ORG_ID IN (:P_BU) OR 'All' IN ('All'||:P_BU))
AND (TRIM(FLA.LEASE_NAME) IN (:P_LEASE_NAME) OR 'All' IN ('All'||:P_LEASE_NAME)) 
--AND (NVL(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE),'New Lease') IN (:P_CHANGE_TYP) OR 'All' IN ('All'||:P_CHANGE_TYP)) --Multi-select is not functional for this filter
AND FS.REGIME_CODE = 'ASC842'
GROUP BY
FLA.LEASE_ID,
FLD.LEASE_CLASSIFICATION_CODE,
TRIM(FLA.LEASE_NAME),
FLA.LEASE_NUMBER,
FLA.LEGAL_ENTITY_ID,
FLD.COMMENCEMENT_DATE,
FLD.COMMENCEMENT_DATE - 1,
FLD.TRANSACTION_TYPE_CODE,
FLD.TRANSACTION_SUBTYPE_CODE,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1,
FLD.LEASE_DETAIL_ID,
FS.REGIME_CODE,
FS.CURRENCY_CODE

UNION 

SELECT 
FLA.LEASE_ID,
TRIM(FLA.LEASE_NAME) LEASE_REF,
FLA.LEASE_NUMBER LEASE_NUM,
(SELECT LEGAL_ENTITY_IDENTIFIER FROM XLE_ENTITY_PROFILES XEP WHERE XEP.LEGAL_ENTITY_ID = FLA.LEGAL_ENTITY_ID) LEGAL_ENTITY,
FLD.COMMENCEMENT_DATE AMENDMENT_DATE,
DECODE(LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0'),'01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','Jun','07','Jul','08','Aug','09','Sep','10','Oct','11','Nov','12','Dec')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) AMEND_MONTH,
LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) Amend_month_1,
CASE WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='EXPIRY' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='IMPAIRMENT' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='TERMINATION' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='SCOPE DECREASE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)
ELSE FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE) END Change_Type,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1 prior_ver,
FLD.LEASE_DETAIL_ID,
fld.LEASE_CLASSIFICATION_CODE,
fla_common_utils_pkg.get_lookup_meaning('ORA_FLA_LEASE_CLASSIFICATION',fld.lease_classification_code) Lease_Classification,
(FLD.COMMENCEMENT_DATE - 1) Prior_day,
SUM(CASE WHEN FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_LIABILITY_AMOUNT ELSE FSSV.IFRS_CLOSING_LIABILITY_AMOUNT END) Liability_Aft_Chg,
SUM(CASE WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_OPER_ROU_AMOUNT WHEN FLD.LEASE_CLASSIFICATION_CODE = 'FINANCE' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_FIN_ROU_AMOUNT 
WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'IFRS16' THEN FSSV.IFRS_CLOSING_OPER_ROU_AMOUNT ELSE FSSV.IFRS_CLOSING_ROU_AMOUNT END)ROU_Aft_Chg,
FS.REGIME_CODE,
FS.CURRENCY_CODE,
(CASE WHEN FS.CURRENCY_CODE = 'USD' THEN 1 ELSE (SELECT GDR.CONVERSION_RATE FROM GL_DAILY_RATES GDR WHERE GDR.FROM_CURRENCY = FS.CURRENCY_CODE AND GDR.TO_CURRENCY = 'USD' AND GDR.CONVERSION_DATE = LAST_DAY(FLD.COMMENCEMENT_DATE) AND UPPER(GDR.CONVERSION_TYPE) = 'CORPORATE') END) USD_Conversion_rate,
(SELECT SYMBOL FROM FND_CURRENCIES WHERE CURRENCY_CODE = FS.CURRENCY_CODE) Curr_Symbol
FROM 
FLA_LEASES_ALL FLA,
FLA_LEASE_DETAILS FLD,
fla_schedule_summary_v FSSV,
FLA_SCHEDULES FS

WHERE 1= 1 
--AND FLD.LEASE_ID IN ('100000110595026','100000115027526','300000016341453')
AND FS.LEASE_ID = FLD.LEASE_ID
AND FLD.VERSION_TYPE_CODE = 'F'
AND UPPER(FLD.TRANSACTION_TYPE_CODE)= 'IMPAIRMENT'
AND FLA.LEASE_ID = FLD.LEASE_ID
AND FSSV.LEASE_ID = FLD.LEASE_ID
AND FLD.COMMENCEMENT_DATE =FSSV.SCHEDULE_END_DATE
AND FS.REGIME_CODE = 'ASC842'
--Parameters
AND TRUNC(FLD.COMMENCEMENT_DATE) BETWEEN TRUNC(:P_FROM_AMND_DATE) 
AND TRUNC(:P_TO_AMND_DATE)
AND (FLA.ORG_ID IN (:P_BU) OR 'All' IN ('All'||:P_BU))
AND (TRIM(FLA.LEASE_NAME) IN (:P_LEASE_NAME) OR 'All' IN ('All'||:P_LEASE_NAME))

GROUP BY
FLA.LEASE_ID,
FLD.LEASE_CLASSIFICATION_CODE,
TRIM(FLA.LEASE_NAME),
FLA.LEASE_NUMBER,
FLA.LEGAL_ENTITY_ID,
FLD.COMMENCEMENT_DATE,
FLD.COMMENCEMENT_DATE - 1,
FLD.TRANSACTION_TYPE_CODE,
FLD.TRANSACTION_SUBTYPE_CODE,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1,
FLD.LEASE_DETAIL_ID,
FS.REGIME_CODE,
FS.CURRENCY_CODE

UNION

SELECT 
FLA.LEASE_ID,
TRIM(FLA.LEASE_NAME) LEASE_REF,
FLA.LEASE_NUMBER LEASE_NUM,
(SELECT LEGAL_ENTITY_IDENTIFIER FROM XLE_ENTITY_PROFILES XEP WHERE XEP.LEGAL_ENTITY_ID = FLA.LEGAL_ENTITY_ID) LEGAL_ENTITY,
FLD.COMMENCEMENT_DATE AMENDMENT_DATE,
DECODE(LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0'),'01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','Jun','07','Jul','08','Aug','09','Sep','10','Oct','11','Nov','12','Dec')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) AMEND_MONTH,
LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) Amend_month_1,
CASE WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='EXPIRY' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='IMPAIRMENT' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='TERMINATION' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='SCOPE DECREASE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)
ELSE FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE) END Change_Type,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1 prior_ver,
FLD.LEASE_DETAIL_ID,
fld.LEASE_CLASSIFICATION_CODE,
fla_common_utils_pkg.get_lookup_meaning('ORA_FLA_LEASE_CLASSIFICATION',fld.lease_classification_code) Lease_Classification,
(FLD.COMMENCEMENT_DATE - 1) Prior_day,
SUM(CASE WHEN FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_LIABILITY_AMOUNT ELSE FSSV.IFRS_CLOSING_LIABILITY_AMOUNT END) Liability_Aft_Chg,
SUM(CASE WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_OPER_ROU_AMOUNT WHEN FLD.LEASE_CLASSIFICATION_CODE = 'FINANCE' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_FIN_ROU_AMOUNT 
WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'IFRS16' THEN FSSV.IFRS_CLOSING_OPER_ROU_AMOUNT ELSE FSSV.IFRS_CLOSING_ROU_AMOUNT END) ROU_Aft_Chg,
FS.REGIME_CODE,
FS.CURRENCY_CODE,
(CASE WHEN FS.CURRENCY_CODE = 'USD' THEN 1 ELSE (SELECT GDR.CONVERSION_RATE FROM GL_DAILY_RATES GDR WHERE GDR.FROM_CURRENCY = FS.CURRENCY_CODE AND GDR.TO_CURRENCY = 'USD' AND GDR.CONVERSION_DATE = LAST_DAY(FLD.COMMENCEMENT_DATE) AND UPPER(GDR.CONVERSION_TYPE) = 'CORPORATE') END) USD_Conversion_rate,
(SELECT SYMBOL FROM FND_CURRENCIES WHERE CURRENCY_CODE = FS.CURRENCY_CODE) Curr_Symbol
FROM 
FLA_LEASES_ALL FLA,
FLA_LEASE_DETAILS FLD,
fla_schedule_summary_v FSSV,
FLA_SCHEDULES FS--,
--fla_schedule_summary_v FSSV_prior

WHERE 1= 1 --FLD.LEASE_ID = 300000006201967 --100000029457993 
AND FS.LEASE_ID = FLD.LEASE_ID
AND FLD.VERSION_TYPE_CODE = 'F'
AND UPPER(FLD.TRANSACTION_TYPE_CODE)='EXPIRY'
AND FLA.LEASE_ID = FLD.LEASE_ID
AND FSSV.LEASE_ID = FLD.LEASE_ID
AND FLD.COMMENCEMENT_DATE =FSSV.SCHEDULE_END_DATE+1
--AND LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) = FSSV.PERIOD_NAME
AND FS.REGIME_CODE = 'ASC842'
--Parameters
 AND TRUNC(FLD.COMMENCEMENT_DATE) BETWEEN TRUNC(:P_FROM_AMND_DATE) 
AND TRUNC(:P_TO_AMND_DATE)
AND (FLA.ORG_ID IN (:P_BU) OR 'All' IN ('All'||:P_BU))
AND (TRIM(FLA.LEASE_NAME) IN (:P_LEASE_NAME) OR 'All' IN ('All'||:P_LEASE_NAME)) 
--AND (NVL(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE),'New Lease') IN (:P_CHANGE_TYP) OR 'All' IN ('All'||:P_CHANGE_TYP)) --Multi-select is not functional for this filter

/* AND FSSV_prior.LEASE_ID = FLD.LEASE_ID
--AND FSSV_prior.SCHEDULE_END_DATE = DECODE(FLD.VERSION_NUM, 1, FSSV_prior.SCHEDULE_END_DATE, FLD.COMMENCEMENT_DATE - 1)
AND FSSV_prior.SCHEDULE_END_DATE = FLD.COMMENCEMENT_DATE - 1
AND FSSV_PRIOR.VERSION_NUM = FLD.VERSION_NUM -1
AND FSSV.PAYMENT_NUMBER = pb.PAYMENT_NUMBER (+)*/
GROUP BY
FLA.LEASE_ID,
FLD.LEASE_CLASSIFICATION_CODE,
TRIM(FLA.LEASE_NAME),
FLA.LEASE_NUMBER,
FLA.LEGAL_ENTITY_ID,
FLD.COMMENCEMENT_DATE,
FLD.COMMENCEMENT_DATE - 1,
FLD.TRANSACTION_TYPE_CODE,
FLD.TRANSACTION_SUBTYPE_CODE,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1,
FLD.LEASE_DETAIL_ID,
FS.REGIME_CODE,
FS.CURRENCY_CODE

UNION
 

SELECT 
FLA.LEASE_ID,
TRIM(FLA.LEASE_NAME) LEASE_REF,
FLA.LEASE_NUMBER LEASE_NUM,
(SELECT LEGAL_ENTITY_IDENTIFIER FROM XLE_ENTITY_PROFILES XEP WHERE XEP.LEGAL_ENTITY_ID = FLA.LEGAL_ENTITY_ID) LEGAL_ENTITY,
FLD.COMMENCEMENT_DATE AMENDMENT_DATE,
DECODE(LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0'),'01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','Jun','07','Jul','08','Aug','09','Sep','10','Oct','11','Nov','12','Dec')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) AMEND_MONTH,
LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) Amend_month_1,
CASE WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='EXPIRY' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='IMPAIRMENT' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='TERMINATION' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='SCOPE DECREASE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)
ELSE FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE) END Change_Type,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1 prior_ver,
FLD.LEASE_DETAIL_ID,
fld.LEASE_CLASSIFICATION_CODE,
fla_common_utils_pkg.get_lookup_meaning('ORA_FLA_LEASE_CLASSIFICATION',fld.lease_classification_code) Lease_Classification,
(FLD.COMMENCEMENT_DATE - 1) Prior_day,
SUM(CASE WHEN FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_LIABILITY_AMOUNT ELSE FSSV.IFRS_CLOSING_LIABILITY_AMOUNT END) Liability_Aft_Chg,
SUM(CASE WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_OPER_ROU_AMOUNT WHEN FLD.LEASE_CLASSIFICATION_CODE = 'FINANCE' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_CLOSING_FIN_ROU_AMOUNT 
WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'IFRS16' THEN FSSV.IFRS_CLOSING_OPER_ROU_AMOUNT ELSE FSSV.IFRS_CLOSING_ROU_AMOUNT END) ROU_Aft_Chg,
FS.REGIME_CODE,
FS.CURRENCY_CODE,
(CASE WHEN FS.CURRENCY_CODE = 'USD' THEN 1 ELSE (SELECT GDR.CONVERSION_RATE FROM GL_DAILY_RATES GDR WHERE GDR.FROM_CURRENCY = FS.CURRENCY_CODE AND GDR.TO_CURRENCY = 'USD' AND GDR.CONVERSION_DATE = LAST_DAY(FLD.COMMENCEMENT_DATE) AND UPPER(GDR.CONVERSION_TYPE) = 'CORPORATE') END) USD_Conversion_rate,
(SELECT SYMBOL FROM FND_CURRENCIES WHERE CURRENCY_CODE = FS.CURRENCY_CODE) Curr_Symbol
FROM 
FLA_LEASES_ALL FLA,
FLA_LEASE_DETAILS FLD,
fla_schedule_summary_v FSSV,
FLA_SCHEDULES FS--,
--fla_schedule_summary_v FSSV_prior

WHERE 1= 1 --FLD.LEASE_ID = 300000006201967 --100000029457993 
AND FS.LEASE_ID = FLD.LEASE_ID
AND FLD.VERSION_TYPE_CODE = 'F'
AND UPPER(FLD.TRANSACTION_TYPE_CODE)='TERMINATION'
AND UPPER(FLD.TRANSACTION_SUBTYPE_CODE)='SCOPE_DECREASE'
AND FLA.LEASE_ID = FLD.LEASE_ID
AND FSSV.LEASE_ID = FLD.LEASE_ID
AND FLD.COMMENCEMENT_DATE =FSSV.SCHEDULE_END_DATE
AND FSSV.SCHEDULE_END_DATE=FSSV.SCHEDULE_START_DATE
AND LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) = FSSV.PERIOD_NAME
AND FS.REGIME_CODE = 'ASC842'
--Parameters
 AND TRUNC(FLD.COMMENCEMENT_DATE) BETWEEN TRUNC(:P_FROM_AMND_DATE) 
AND TRUNC(:P_TO_AMND_DATE)
AND (FLA.ORG_ID IN (:P_BU) OR 'All' IN ('All'||:P_BU))
AND (TRIM(FLA.LEASE_NAME) IN (:P_LEASE_NAME) OR 'All' IN ('All'||:P_LEASE_NAME)) 
--AND (NVL(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE),'New Lease') IN (:P_CHANGE_TYP) OR 'All' IN ('All'||:P_CHANGE_TYP)) --Multi-select is not functional for this filter

/* AND FSSV_prior.LEASE_ID = FLD.LEASE_ID
--AND FSSV_prior.SCHEDULE_END_DATE = DECODE(FLD.VERSION_NUM, 1, FSSV_prior.SCHEDULE_END_DATE, FLD.COMMENCEMENT_DATE - 1)
AND FSSV_prior.SCHEDULE_END_DATE = FLD.COMMENCEMENT_DATE - 1
AND FSSV_PRIOR.VERSION_NUM = FLD.VERSION_NUM -1
AND FSSV.PAYMENT_NUMBER = pb.PAYMENT_NUMBER (+)*/
GROUP BY
FLA.LEASE_ID,
FLD.LEASE_CLASSIFICATION_CODE,
TRIM(FLA.LEASE_NAME),
FLA.LEASE_NUMBER,
FLA.LEGAL_ENTITY_ID,
FLD.COMMENCEMENT_DATE,
FLD.COMMENCEMENT_DATE - 1,
FLD.TRANSACTION_TYPE_CODE,
FLD.TRANSACTION_SUBTYPE_CODE,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1,
FLD.LEASE_DETAIL_ID,
FS.REGIME_CODE,
FS.CURRENCY_CODE

UNION

SELECT 
FLA.LEASE_ID,
TRIM(FLA.LEASE_NAME) LEASE_REF,
FLA.LEASE_NUMBER LEASE_NUM,
(SELECT LEGAL_ENTITY_IDENTIFIER FROM XLE_ENTITY_PROFILES XEP WHERE XEP.LEGAL_ENTITY_ID = FLA.LEGAL_ENTITY_ID) LEGAL_ENTITY,
FLD.COMMENCEMENT_DATE AMENDMENT_DATE,
DECODE(LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0'),'01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','Jun','07','Jul','08','Aug','09','Sep','10','Oct','11','Nov','12','Dec')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) AMEND_MONTH,
LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) Amend_month_1,
CASE WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='EXPIRY' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='IMPAIRMENT' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='NO SCOPE CHANGE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)

WHEN UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE))='TERMINATION' AND UPPER(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE))='SCOPE DECREASE'
THEN FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_ACCT_TRN_TYPE',FLD.TRANSACTION_TYPE_CODE)
ELSE FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE) END Change_Type,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1 prior_ver,
FLD.LEASE_DETAIL_ID,
fld.LEASE_CLASSIFICATION_CODE,
fla_common_utils_pkg.get_lookup_meaning('ORA_FLA_LEASE_CLASSIFICATION',fld.lease_classification_code) Lease_Classification,
(FLD.COMMENCEMENT_DATE - 1) Prior_day,
SUM(CASE WHEN FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_OPENING_LIABILITY_AMOUNT ELSE FSSV.IFRS_OPENING_LIABILITY_AMOUNT END) Liability_Aft_Chg,
SUM(CASE WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_OPENING_OPER_ROU_AMOUNT WHEN FLD.LEASE_CLASSIFICATION_CODE = 'FINANCE' AND FS.REGIME_CODE = 'ASC842' THEN FSSV.ASC_OPENING_FIN_ROU_AMOUNT 
WHEN FLD.LEASE_CLASSIFICATION_CODE = 'OPERATING' AND FS.REGIME_CODE = 'IFRS16' THEN FSSV.IFRS_OPENING_OPER_ROU_AMOUNT ELSE FSSV.IFRS_OPENING_ROU_AMOUNT END) ROU_Aft_Chg,
FS.REGIME_CODE,
FS.CURRENCY_CODE,
(CASE WHEN FS.CURRENCY_CODE = 'USD' THEN 1 ELSE (SELECT GDR.CONVERSION_RATE FROM GL_DAILY_RATES GDR WHERE GDR.FROM_CURRENCY = FS.CURRENCY_CODE AND GDR.TO_CURRENCY = 'USD' AND GDR.CONVERSION_DATE = LAST_DAY(FLD.COMMENCEMENT_DATE) AND UPPER(GDR.CONVERSION_TYPE) = 'CORPORATE') END) USD_Conversion_rate,
(SELECT SYMBOL FROM FND_CURRENCIES WHERE CURRENCY_CODE = FS.CURRENCY_CODE) Curr_Symbol
FROM 
FLA_LEASES_ALL FLA,
FLA_LEASE_DETAILS FLD,
fla_schedule_summary_v FSSV,
FLA_SCHEDULES FS--,
--fla_schedule_summary_v FSSV_prior

WHERE 1= 1 --FLD.LEASE_ID = 300000006201967 --100000029457993 
AND FS.LEASE_ID = FLD.LEASE_ID
AND FLD.VERSION_TYPE_CODE = 'F'
AND FLA.LEASE_ID = FLD.LEASE_ID
AND FSSV.LEASE_ID = FLD.LEASE_ID
AND FLD.COMMENCEMENT_DATE =FSSV.SCHEDULE_START_DATE
AND LPAD(EXTRACT( MONTH FROM FLD.COMMENCEMENT_DATE),2,'0')||'-'|| SUBSTR(EXTRACT( YEAR FROM FLD.COMMENCEMENT_DATE),3,2) = FSSV.PERIOD_NAME
AND FS.REGIME_CODE = 'ASC842'
AND UPPER(FLD.TRANSACTION_TYPE_CODE)!='TERMINATION'
--Parameters
 AND TRUNC(FLD.COMMENCEMENT_DATE) BETWEEN TRUNC(:P_FROM_AMND_DATE) 
AND TRUNC(:P_TO_AMND_DATE)
AND (FLA.ORG_ID IN (:P_BU) OR 'All' IN ('All'||:P_BU))
AND (TRIM(FLA.LEASE_NAME) IN (:P_LEASE_NAME) OR 'All' IN ('All'||:P_LEASE_NAME)) 
--AND (NVL(FLA_COMMON_UTILS_PKG.GET_LOOKUP_MEANING('ORA_FLA_TRN_SUBTYPE',FLD.TRANSACTION_SUBTYPE_CODE),'New Lease') IN (:P_CHANGE_TYP) OR 'All' IN ('All'||:P_CHANGE_TYP)) --Multi-select is not functional for this filter
/* AND FSSV_prior.LEASE_ID = FLD.LEASE_ID
--AND FSSV_prior.SCHEDULE_END_DATE = DECODE(FLD.VERSION_NUM, 1, FSSV_prior.SCHEDULE_END_DATE, FLD.COMMENCEMENT_DATE - 1)
AND FSSV_prior.SCHEDULE_END_DATE = FLD.COMMENCEMENT_DATE - 1
AND FSSV_PRIOR.VERSION_NUM = FLD.VERSION_NUM -1
AND FSSV.PAYMENT_NUMBER = pb.PAYMENT_NUMBER (+)*/
GROUP BY
FLA.LEASE_ID,
FLD.LEASE_CLASSIFICATION_CODE,
TRIM(FLA.LEASE_NAME),
FLA.LEASE_NUMBER,
FLA.LEGAL_ENTITY_ID,
FLD.COMMENCEMENT_DATE,
FLD.COMMENCEMENT_DATE - 1,
FLD.TRANSACTION_TYPE_CODE,
FLD.TRANSACTION_SUBTYPE_CODE,
FLD.VERSION_NUM,
FLD.VERSION_NUM - 1,
FLD.LEASE_DETAIL_ID,
FS.REGIME_CODE,
FS.CURRENCY_CODE)
WHERE 1=1
AND (NVL(Change_Type,'New Lease') IN (:P_CHANGE_TYP) OR 'All' IN ('All'||:P_CHANGE_TYP))
ORDER BY LEASE_ID, VERSION_NUM

:P_BU :P_CHANGE_TYP :P_FROM_AMND_DATE :P_LEASE_NAME :P_TO_AMND_DATE

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
FLA_SCHEDULES	dimension	dimension
FLA_SCHEDULE_HEADERS	dimension	dimension
FLA_LEASE_DETAILS	dimension	dimension
GL_DAILY_RATES	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.

Lease Accounting 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
FLA_LEASES_ALL	Setup / configuration table — joined for reference, not exposed for analytics
FLA_SCHEDULES	Setup / configuration table — joined for reference, not exposed for analytics
FLA_SCHEDULE_HEADERS	Setup / configuration table — joined for reference, not exposed for analytics
FLA_LEASE_DETAILS	Setup / configuration table — joined for reference, not exposed for analytics
GL_DAILY_RATES	1	10

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.