Oracle Fusion · Lease Accounting
Lease Disclosure Report
Lease Accounting
The lease figures financial statements require — future minimum payments by year, the maturity of lease liabilities, and the weighted-average discount rate and remaining term — formatted for the lease note under ASC 842 and IFRS 16.
Related Built for the ASC 842 / IFRS 16 lease note — pairs with the Lease Details and Lease Activity Detail reports.
Sample build of the Lease Disclosure Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Lease Disclosure Report
Sample build · illustrativeFilters
Rou Legal Entity Id
1003
Rou Year
Sample
Year
Sample
P Fy Comp Total Expense
$96,400.00
P Py Interest Fin Amount
$96,400.00
P Py Interest Opr Amount
$96,400.00
$36.4M
Total undiscounted
$31.2M
Present value
6.2 yrs
Wtd avg term
| Maturity Year | Minimum Payments | Imputed Interest | Present Value | Lease Class |
|---|---|---|---|---|
| Sample | Sample | Sample | Sample | Standard |
| — | — | — | — | Corporate |
| Sample | Sample | Sample | Sample | Standard |
| — | — | — | — | Default |
| Sample | Sample | Sample | Sample | Standard |
| Sample | Sample | Sample | Sample | Standard |
AI Analyst · active
reading
The report aggregates the lease schedules into the maturity bands the disclosure note needs and computes the weighted-average rate and term.
flag
The undiscounted-to-present-value gap is the imputed interest the note must show; one finance-lease cluster carries an above-portfolio discount rate that drives most of it.
root cause & next step
Confirm the discount rates on the finance-lease cluster against the incremental borrowing rate used; a stale rate distorts both the liability and the disclosed maturity.
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_SUMMARY_V
- 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.
Show / hide SQL
SELECT
-- func_currency cash_fun_cur,
:P_ROU_YEAR fy_year,
max(fy_end_date)fy_end_date,
--lease_number,
max(calander_1year) calander_1year,
max(calander_2year) calander_2year,
max(calander_3year) calander_3year,
max(calander_4year) calander_4year,
max(calander_5year) calander_5year,
max(fc_fy_tot_exempt_amount) fc_fy_tot_exempt_amount,
max(fc_fy_tot_nonliability_amount) fc_fy_tot_nonliability_amount,
max(fc_fy_tot_variable_amount) fc_fy_tot_variable_amount,
nvl(max(fc_fy_tot_exempt_amount),0) + nvl(max(fc_fy_tot_nonliability_amount),0) + nvl(max(fc_fy_tot_variable_amount),0) fc_fy_tot_nc_amount,
:p_fy_comp_total_expense + nvl(max(fc_fy_tot_exempt_amount),0) + nvl(max(fc_fy_tot_nonliability_amount),0) + nvl(max(fc_fy_tot_variable_amount),0) fc_fy_tot_amount,
nvl(max(fc_fy_fin_amount),0) - :p_py_interest_fin_amount fc_fy_prin_fin_amount,
:p_py_interest_fin_amount fc_fy_interest_fin_amount,
nvl(max(fc_fy_opr_amount),0) - :p_py_interest_opr_amount fc_fy_prin_opr_amount,
:p_py_interest_opr_amount fc_fy_interest_opr_amount,
:p_fy_comp_total_expense fc_fy_comp_total_expense,
max(fc_fy_long_exempt_amount) fc_fy_long_exempt_amount,
max(fc_fy_short_exempt_amount) fc_fy_short_exempt_amount,
nvl(max(fc_fy_fin_amount),0) + nvl(max(fc_fy_opr_amount),0) + nvl(max(fc_fy_long_exempt_amount),0) + nvl(max(fc_fy_short_exempt_amount),0) + nvl(max(fc_fy_tot_variable_amount),0) fc_fy_tot_cashflow,
-- 3. Future Minimum Lease Payments
max(fc_1year_fin_amount) fc_1year_fin_amount,
max(fc_2year_fin_amount) fc_2year_fin_amount,
max(fc_3year_fin_amount) fc_3year_fin_amount,
max(fc_4year_fin_amount) fc_4year_fin_amount,
max(fc_5year_fin_amount) fc_5year_fin_amount,
max(fc_5plus_fin_amount) fc_5plus_fin_amount,
max(fc_total_fut_fin_amount) fc_total_fut_fin_amount,
/*nvl(max(fc_total_fut_fin_amount),0) -*/ nvl(max(fc_dce_fut_total_fin_amount),0) fc_imputed_fin_amountt,
nvl(max(fc_total_fut_fin_amount),0) - nvl(max(fc_dce_fut_total_fin_amount),0) fc_pv_fin_amount,
--max(fc_dce_fut_total_fin_amount) fc_pv_fin_amount,
max(fc_1year_opr_amount) fc_1year_opr_amount,
max(fc_2year_opr_amount) fc_2year_opr_amount,
max(fc_3year_opr_amount) fc_3year_opr_amount,
max(fc_4year_opr_amount) fc_4year_opr_amount,
max(fc_5year_opr_amount) fc_5year_opr_amount,
max(fc_5plus_opr_amount) fc_5plus_opr_amount,
max(fc_total_fut_opr_amount) fc_total_fut_opr_amount,
/*nvl(max(fc_total_fut_opr_amount),0) -*/ nvl(max(fc_dce_fut_total_opr_amount),0) fc_imputed_opr_amount,
nvl(max(fc_total_fut_opr_amount),0) - nvl(max(fc_dce_fut_total_opr_amount),0) fc_pv_opr_amount,
--max(fc_dce_fut_total_opr_amount) fc_pv_opr_amount,
max(fc_1year_fin_amount) + max(fc_1year_opr_amount) fc_1year_amount,
max(fc_2year_fin_amount) + max(fc_2year_opr_amount) fc_2year_amount,
max(fc_3year_fin_amount) + max(fc_3year_opr_amount) fc_3year_amount,
max(fc_4year_fin_amount) + max(fc_4year_opr_amount) fc_4year_amount,
max(fc_5year_fin_amount) + max(fc_5year_opr_amount) fc_5year_amount,
max(fc_5plus_fin_amount) + max(fc_5plus_opr_amount) fc_5plus_amount,
max(fc_total_fut_fin_amount) + max(fc_total_fut_opr_amount) fc_total_fut_amount,
(nvl(max(fc_total_fut_fin_amount),0) - nvl(max(fc_dce_fut_total_fin_amount),0))+(nvl(max(fc_total_fut_opr_amount),0) - nvl(max(fc_dce_fut_total_opr_amount),0)) /*fc_imputed_amountt*/ fc_pv_amount,
max(fc_dce_fut_total_fin_amount) + max(fc_dce_fut_total_opr_amount) fc_imputed_amountt, --fc_pv_amount,
max(fc_dce_1year_prop_fin_amount) fc_dce_1year_prop_fin_amount,
max(fc_dce_1plus_prop_fin_amount) fc_dce_1plus_prop_fin_amount,
max(fc_dce_1year_prop_opr_amount) fc_dce_1year_prop_opr_amount,
max(fc_dce_1plus_prop_opr_amount) fc_dce_1plus_prop_opr_amount,
max(fc_dce_1year_eqp_fin_amount) fc_dce_1year_eqp_fin_amount,
max(fc_dce_1plus_eqp_fin_amount) fc_dce_1plus_eqp_fin_amount,
max(fc_dce_1year_eqp_opr_amount) fc_dce_1year_eqp_opr_amount,
max(fc_dce_1plus_eqp_opr_amount) fc_dce_1plus_eqp_opr_amount,
max(fc_1year_related_amount) fc_1year_related_amount,
max(fc_future_cashflow_amount) fc_future_cashflow_amount
FROM
(SELECT
--func_currency,
--LEASE_number,
fy_year,
fy_end_date,
calander_1year,
calander_2year,
calander_3year,
calander_4year,
calander_5year,
SUM(
CASE
WHEN compliance_enabled_flag = 'N'
AND option_flag = 'N'
AND lease_classification_code = 'EXEMPT'
AND due_date between fy_start_date and fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_tot_exempt_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'N'
AND option_flag = 'N'
AND lease_classification_code <> 'EXEMPT'
AND due_date between fy_start_date and fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_tot_nonliability_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND regime_code = 'ASC842'
AND due_date between fy_start_date and fy_end_date THEN
nvl(fc_contingent_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_tot_variable_amount,
SUM(
CASE
WHEN (compliance_enabled_flag = 'N'
OR lease_classification_code = 'EXEMPT' )
AND option_flag = 'N'
AND lease_duration > 12
AND due_date between fy_start_date and fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_long_exempt_amount,
SUM(
CASE
WHEN (compliance_enabled_flag = 'N'
OR lease_classification_code = 'EXEMPT')
AND option_flag = 'N'
AND lease_duration <= 12
AND due_date between fy_start_date and fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_short_exempt_amount,
SUM(decode(compliance_enabled_flag, 'Y', nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END), 0)) fc_total_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date BETWEEN fy_start_date AND fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_fin_amount,
NULL fc_total_fut_fin_amount,
NULL fc_1year_fin_amount,
NULL fc_2year_fin_amount,
NULL fc_3year_fin_amount,
NULL fc_4year_fin_amount,
NULL fc_5year_fin_amount,
NULL fc_5plus_fin_amount,
NULL fc_total_fut_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date BETWEEN fy_start_date AND fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_fy_opr_amount,
NULL fc_1year_opr_amount,
NULL fc_2year_opr_amount,
NULL fc_3year_opr_amount,
NULL fc_4year_opr_amount,
NULL fc_5year_opr_amount,
NULL fc_5plus_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND intercompany_flag = 'Y'
AND option_flag = 'N'
AND due_date BETWEEN fy_start_date AND fy_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_1year_related_amount,
NULL fc_future_cashflow_amount,
SUM(decode(compliance_enabled_flag, 'Y', nvl(fc_discounted_amount, 0), 0)) fc_dc_total_amount,
NULL fc_dce_fut_total_fin_amount,
NULL fc_dce_fut_total_opr_amount,
NULL fc_dce_1year_prop_fin_amount,
NULL fc_dce_1plus_prop_fin_amount,
NULL fc_dce_1year_prop_opr_amount,
NULL fc_dce_1plus_prop_opr_amount,
NULL fc_dce_1year_eqp_fin_amount,
NULL fc_dce_1plus_eqp_fin_amount,
NULL fc_dce_1year_eqp_opr_amount,
NULL fc_dce_1plus_eqp_opr_amount,
NULL fc_dce_1year_related_amount
FROM
(
SELECT
pay.*,
round(
CASE
WHEN pay.compliance_enabled_flag = 'Y'
AND pay.due_date > pay.fy_end_date THEN
round((pay.actual_amount * power((1 +(round(power((1 + nvl(pay.discount_rate, 0) / 100),(1 / pay.days_per_year
)) - 1, 8))),(- 1 * decode(pay.amortization_proration_code, '999',(pay.due_date - pay.fy_end_date)-1, fla_schedule_utils_pkg
.get_no_of_days_360_rule(pay.fy_end_date, pay.due_date) - 1)))), pay.fc_precision)
ELSE
0
END
*(CASE WHEN pay.CURRENCY_CODE = 'USD' THEN 1 ELSE pay.conversion_rate END), pay.fc_precision) fc_discounted_amount,
round(pay.actual_amount * (CASE WHEN pay.CURRENCY_CODE = 'USD' THEN 1 ELSE pay.conversion_rate END), fc_precision) fc_amount,
round(pay.contingent_amount * (CASE WHEN pay.CURRENCY_CODE = 'USD' THEN 1 ELSE pay.conversion_rate END), fc_precision) fc_contingent_amount
FROM
(SELECT
ll.lease_id,
ll.org_id,
ll.lease_number,
ll.lease_name,
ll.lease_start_date,
ll.lease_end_date,
ll.lease_duration,
ll.LEASE_ASSET_TYPE_CODE,
ll.discount_rate,
ll.func_currency,
ll.fc_precision,
ll.payment_number,
ll.option_flag,
ll.currency_code,
ll.days_per_year,
ll.calander_py_end_date,
ll.fy_start_date,
ll.fy_end_date,
ll.calander_1year_end_date,
ll.calander_2year_end_date,
ll.calander_3year_end_date,
ll.calander_4year_end_date,
ll.calander_5year_end_date,
ll.py_year,
ll.fy_year,
ll.calander_1year,
ll.calander_2year,
ll.calander_3year,
ll.calander_4year,
ll.calander_5year,
ll.intercompany_flag,
pv.due_date,
ll.commencement_date,
ll.amortization_start_date,
ll.amortization_end_date,
ll.amortization_proration_code,
ll.lease_classification_code,
ll.regime_code,
ll.ledger_id,
ll.version_num,
ll.lease_detail_id,
nvl(pv.actual_amount, 0) actual_amount,
nvl(pv.CONTINGENT_AMOUNT,0) contingent_amount,
CASE
WHEN pv.due_date > ll.fy_end_date THEN
ll.conversion_rate
WHEN pv.due_date <= ll.fy_end_date
AND ll.func_currency = ll.currency_code THEN
1
WHEN pv.due_date <= ll.fy_end_date
AND ll.user_conversion_rate IS NOT NULL THEN
ll.user_conversion_rate
ELSE
(
SELECT
round(MAX(decode(from_currency, ll.currency_code, conversion_rate, 1 / conversion_rate)) KEEP
(DENSE_RANK FIRST ORDER BY conversion_date DESC, decode(from_currency, ll.currency_code, 1, 2
)), 8)
FROM
gl_daily_rates
WHERE
conversion_type = ll.conversion_type_code
AND ll.currency_code IN (
from_currency,
to_currency
)
AND ll.func_currency IN (
from_currency,
to_currency
)
AND conversion_date <= ll.fy_end_date
AND status_code = 'C'
)
END conversion_rate,
CASE
WHEN ll.compliance_enabled_flag = 'Y'
AND pv.due_date BETWEEN ll.amortization_start_date AND ll.amortization_end_date THEN
'Y'
ELSE
'N'
END compliance_enabled_flag
, ll.pa_CONVERSION_RATE
FROM
(
SELECT
la.lease_id,
ld.lease_detail_id,
la.lease_number,
la.lease_name,
la.lease_start_date,
ld.termination_date lease_end_date,
ld.commencement_date,
months_between(ld.termination_date + 1, la.lease_start_date) lease_duration,
ld.lease_classification_code,
la.LEASE_ASSET_TYPE_CODE,
la.amortization_proration_code,
decode(la.amortization_proration_code, '360', 360, 365) days_per_year,
la.amortization_start_date,
nvl(pa.amortization_end_date, ld.amortization_end_date) amortization_end_date,
ph.payment_number,
nvl(ph.option_flag,'N') option_flag,
ph.payment_type_code,
ph.payment_purpose_code,
ph.conversion_rate user_conversion_rate,
ph.conversion_type_code,
ld.version_num,
nvl(ph.intercompany_flag,'N') intercompany_flag,
CASE
WHEN ph.rou_asset_flag = 'Y'
OR ph.liability_flag = 'Y' THEN
'Y'
ELSE
'N'
END compliance_enabled_flag,
ph.currency_code currency_code,
nvl(ld.prim_regime_interest_rate, 0) + nvl(ld.prim_regime_adder_rate, 0) discount_rate,
CASE
WHEN per.func_currency = ph.currency_code THEN
1
WHEN ph.conversion_rate IS NOT NULL THEN
ph.conversion_rate
ELSE
(
SELECT
round(MAX(decode(from_currency, ph.currency_code, conversion_rate, 1 / conversion_rate
)) KEEP(DENSE_RANK FIRST ORDER BY conversion_date DESC, decode(from_currency, ph.currency_code
, 1, 2)), 8)
FROM
gl_daily_rates
WHERE
conversion_type = ph.conversion_type_code
AND ph.currency_code IN (
from_currency,
to_currency
)
AND per.func_currency IN (
from_currency,
to_currency
)
AND conversion_date <= per.fy_end_date
AND status_code = 'C'
)
END conversion_rate,
per.*
FROM
FLA_LEASES_ALL LA,
FLA_LEASE_DETAILS LD,
FLA_LEASE_PAYMENT_HEADERS PH,
FLA_LEASE_ASSETS PA,
( SELECT
x.org_id,
x.ledger_id,
x.regime_code,
x.func_currency,
x.fc_precision,
x.fy_start_date,
x.fy_end_date,
x.period_start_date,
x.period_end_date,
x.period_name,
x.calander_py_end_date,
x.calander_1year_end_date,
x.calander_2year_end_date,
x.calander_3year_end_date,
x.calander_4year_end_date,
x.calander_5year_end_date,
x.py_year,
x.fy_year,
x.calander_1year,
x.calander_2year,
x.calander_3year,
x.calander_4year,
x.calander_5year,
/* Final monthly rate with carry-forward, seeded from prior year */
CASE
WHEN x.func_currency = 'USD' THEN 1
ELSE
LAST_VALUE(
NVL(
x.base_rate, /* rate on this month-end, if present */
CASE WHEN x.period_end_date = x.first_end_date /* seed only on the first month row */
THEN x.seed_rate
END
)
IGNORE NULLS
) OVER (
PARTITION BY x.org_id, x.ledger_id
ORDER BY x.period_end_date /* keep monthly order */
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
END AS pa_conversion_rate
FROM (
SELECT
so.org_id,
gld.ledger_id,
CASE
WHEN gld.ledger_id = so.ledger_id THEN so.PRIMARY_REGIME_CODE
ELSE so.SECONDARY_REGIME_CODE
END AS regime_code,
gld.currency_code AS func_currency,
NVL(fc.precision, 2) AS fc_precision,
/* === Corrected fiscal year boundaries (year-wise) === */
MIN(gp.start_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year) AS fy_start_date,
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year) AS fy_end_date,
/* === Period-wise dates (new) === */
gp.start_date AS period_start_date,
gp.end_date AS period_end_date,
gp.period_name AS period_name,
/* Calendar-related outputs based on FY dates */
(MIN(gp.start_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year) - 1) AS calander_py_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
12) AS calander_1year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
24) AS calander_2year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
36) AS calander_3year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
48) AS calander_4year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
60) AS calander_5year_end_date,
(gp.period_year - 1) AS py_year,
gp.period_year AS fy_year,
(gp.period_year + 1) AS calander_1year,
(gp.period_year + 2) AS calander_2year,
(gp.period_year + 3) AS calander_3year,
(gp.period_year + 4) AS calander_4year,
(gp.period_year + 5) AS calander_5year,
/* This month’s direct rate (USD=1 handled in outer layer) */
r.conversion_rate AS base_rate,
/* Seed: last available rate before fiscal year start (per currency) */
seed.seed_rate,
/* First month-end date for the ledger in this year (per partition) */
MIN(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id) AS first_end_date
FROM
fla_system_options_all so,
gl_ledgers gld,
fnd_currencies_b fc,
gl_periods gp,
/* ===== Corporate rates for exact month-end (left join) ===== */
(
SELECT
gdr.from_currency,
gdr.conversion_date,
gdr.conversion_rate
FROM
gl_daily_rates gdr,
gl_daily_conversion_types gdc
WHERE
gdc.conversion_type = gdr.conversion_type
AND UPPER(gdc.user_conversion_type) = 'CORPORATE'
AND gdr.to_currency = 'USD'
AND gdr.from_currency <> gdr.to_currency
) r,
/* ===== Seed rates: last rate prior to fiscal year start (per currency, per FY start) ===== */
(
SELECT
p.from_currency,
p.year_start_date,
/* pick rate corresponding to the MAX(conversion_date) < year_start_date */
MAX(gdr.conversion_rate)
KEEP (DENSE_RANK LAST ORDER BY gdr.conversion_date) AS seed_rate
FROM
(
/* Build the set of (currency, FY start) pairs for the requested year */
SELECT DISTINCT
gld2.currency_code AS from_currency,
gp2.year_start_date
FROM
gl_ledgers gld2,
gl_periods gp2
WHERE
gp2.period_year = :P_YEAR
AND gp2.adjustment_period_flag = 'N'
AND gld2.period_set_name = gp2.period_set_name
AND gld2.accounted_period_type = gp2.period_type
) p,
gl_daily_rates gdr,
gl_daily_conversion_types gdc
WHERE
gdc.conversion_type = gdr.conversion_type
AND UPPER(gdc.user_conversion_type) = 'CORPORATE'
AND gdr.to_currency = 'USD'
AND gdr.from_currency = p.from_currency
AND gdr.conversion_date < p.year_start_date
GROUP BY
p.from_currency, p.year_start_date
) seed,
fnd_lookup_values_vl flv
WHERE
/* Ledger scope - regimes */
gld.ledger_id IN (so.ledger_id, so.SECONDARY_LEDGER_ID)
AND ( UPPER(so.PRIMARY_REGIME_CODE) = 'ASC842'
OR UPPER(so.SECONDARY_REGIME_CODE) = 'ASC842' )
/* Ledger setup and periods (monthly, non-adjustment) */
AND gld.currency_code = fc.currency_code
AND gld.period_set_name = gp.period_set_name
AND gld.accounted_period_type = gp.period_type
AND gp.adjustment_period_flag = 'N'
AND gp.period_year = :P_YEAR
/* Left join to the monthly rate for that month-end date */
AND r.from_currency(+) = gld.currency_code
AND r.conversion_date(+) = gp.end_date
/* Left join seed by currency + fiscal year start */
AND seed.from_currency(+) = gld.currency_code
AND seed.year_start_date(+) = gp.year_start_date
/* US GAAP ledgers constraint */
AND flv.lookup_type = 'FLA_LEDGER_USGAAP'
AND gld.name IN (flv.meaning)
) X ) PER
WHERE
LA.LEASE_ID = LD.LEASE_ID
AND LD.VERSION_TYPE_CODE = 'F'
AND LA.ORG_ID = PER.ORG_ID
AND LD.LEASE_DETAIL_ID = PH.LEASE_DETAIL_ID
AND LD.LEASE_ID = PH.LEASE_ID
AND PH.ASSET_NUMBER = PA.ASSET_NUMBER (+)
AND PH.LEASE_DETAIL_ID = PA.LEASE_DETAIL_ID (+)
AND PH.LEASE_ID = PA.LEASE_ID (+)
) LL,
FLA_PAYMENT_ITEMS PV
WHERE
LL.LEASE_DETAIL_ID = PV.LEASE_DETAIL_ID
AND LL.LEASE_ID = PV.LEASE_ID
AND LL.PAYMENT_NUMBER = PV.PAYMENT_NUMBER
AND LL.PERIOD_NAME = PV.PERIOD_NAME
AND PV.DUE_DATE > ADD_MONTHS(LL.CALANDER_PY_END_DATE, - 12)
AND NVL(PV.ACTUAL_AMOUNT, PV.ESTIMATED_AMOUNT) <> 0
--AND ll.ledger_id = '300000011103515'
) pay
)
GROUP BY
--func_currency,
--lease_number,
fy_year,
fy_end_date,
calander_1year,
calander_2year,
calander_3year,
calander_4year,
calander_5year
UNION
SELECT
--func_currency,
--LEASE_number,
fy_year,
fy_end_date,
calander_1year,
calander_2year,
calander_3year,
calander_4year,
calander_5year,
NULL fc_fy_tot_exempt_amount,
NULL fc_fy_tot_nonliability_amount,
NULL fc_fy_tot_variable_amount,
NULL fc_fy_long_exempt_amount,
NULL fc_fy_short_exempt_amount,
NULL fc_total_amount,
NULL fc_fy_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND due_date > fy_end_date
AND lease_classification_code = 'FINANCE' THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_total_fut_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date BETWEEN fy_end_date + 1 AND calander_1year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_1year_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date BETWEEN calander_1year_end_date + 1 AND calander_2year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_2year_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date BETWEEN calander_2year_end_date + 1 AND calander_3year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_3year_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date BETWEEN calander_3year_end_date + 1 AND calander_4year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_4year_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date BETWEEN calander_4year_end_date + 1 AND calander_5year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_5year_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'FINANCE'
AND due_date > calander_5year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_5plus_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND due_date > fy_end_date
AND lease_classification_code = 'OPERATING' THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_total_fut_opr_amount,
NULL fc_fy_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date BETWEEN fy_end_date + 1 AND calander_1year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_1year_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date BETWEEN calander_1year_end_date + 1 AND calander_2year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_2year_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date BETWEEN calander_2year_end_date + 1 AND calander_3year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_3year_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date BETWEEN calander_3year_end_date + 1 AND calander_4year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_4year_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date BETWEEN calander_4year_end_date + 1 AND calander_5year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_5year_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND lease_classification_code = 'OPERATING'
AND due_date > calander_5year_end_date THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_5plus_opr_amount,
NULL fc_1year_related_amount,
SUM(
CASE
WHEN lease_start_date > fy_end_date
AND option_flag = 'N'
THEN
nvl(fc_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE pa_CONVERSION_RATE END)
ELSE
0
END
) fc_future_cashflow_amount,
NULL fc_dc_total_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND due_date > fy_end_date
AND lease_classification_code = 'FINANCE' THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_fut_total_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND option_flag = 'N'
AND due_date > fy_end_date
AND lease_classification_code = 'OPERATING' THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_fut_total_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'FINANCE'
AND LEASE_ASSET_TYPE_CODE = 'PROPERTY'
AND intercompany_flag = 'N'
AND due_date BETWEEN fy_end_date AND calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1year_prop_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'FINANCE'
AND LEASE_ASSET_TYPE_CODE = 'PROPERTY'
AND intercompany_flag = 'N'
AND due_date > calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1plus_prop_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'OPERATING'
AND LEASE_ASSET_TYPE_CODE = 'PROPERTY'
AND intercompany_flag = 'N'
AND due_date BETWEEN fy_end_date AND calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1year_prop_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'OPERATING'
AND LEASE_ASSET_TYPE_CODE = 'PROPERTY'
AND intercompany_flag = 'N'
AND due_date > calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1plus_prop_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'FINANCE'
AND LEASE_ASSET_TYPE_CODE = 'EQUIPMENT'
AND intercompany_flag = 'N'
AND due_date BETWEEN fy_end_date AND calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1year_eqp_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'FINANCE'
AND LEASE_ASSET_TYPE_CODE = 'EQUIPMENT'
AND intercompany_flag = 'N'
AND due_date > calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1plus_eqp_fin_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'OPERATING'
AND LEASE_ASSET_TYPE_CODE = 'EQUIPMENT'
AND intercompany_flag = 'N'
AND due_date BETWEEN fy_end_date AND calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1year_eqp_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND lease_classification_code = 'OPERATING'
AND LEASE_ASSET_TYPE_CODE = 'EQUIPMENT'
AND intercompany_flag = 'N'
AND due_date > calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1plus_eqp_opr_amount,
SUM(
CASE
WHEN compliance_enabled_flag = 'Y'
AND intercompany_flag = 'Y'
AND due_date BETWEEN fy_end_date+1 AND calander_1year_end_date THEN
nvl(fc_discounted_amount, 0)*(CASE WHEN CURRENCY_CODE = 'USD' THEN 1 ELSE AVG_CONVERSION_RATE END)
ELSE
0
END
) fc_dce_1year_related_amount
FROM
(
SELECT
pay.*,
/* Undiscounted FC amount (for reference) */
ROUND(
pay.actual_amount
* CASE WHEN pay.currency_code = 'USD' THEN 1 ELSE pay.conversion_rate END
, pay.fc_precision) AS fc_amount,
/* Contingent in FC */
ROUND(
pay.contingent_amount
* CASE WHEN pay.currency_code = 'USD' THEN 1 ELSE pay.conversion_rate END
, pay.fc_precision) AS fc_contingent_amount
FROM
(
SELECT
ll.lease_id,
ll.org_id,
ll.lease_number,
ll.lease_name,
ll.lease_start_date,
ll.lease_end_date,
ll.lease_duration,
ll.LEASE_ASSET_TYPE_CODE,
ll.discount_rate,
ll.func_currency,
ll.fc_precision,
ll.payment_number,
ll.option_flag,
ll.currency_code,
ll.days_per_year,
ll.calander_py_end_date,
ll.fy_start_date,
ll.fy_end_date,
ll.calander_1year_end_date,
ll.calander_2year_end_date,
ll.calander_3year_end_date,
ll.calander_4year_end_date,
ll.calander_5year_end_date,
ll.py_year,
ll.fy_year,
ll.calander_1year,
ll.calander_2year,
ll.calander_3year,
ll.calander_4year,
ll.calander_5year,
ll.intercompany_flag,
pv.due_date,
ll.commencement_date,
ll.amortization_start_date,
ll.amortization_end_date,
ll.amortization_proration_code,
ll.lease_classification_code,
ll.regime_code,
ll.ledger_id,
ll.version_num,
ll.lease_detail_id,
nvl(pv.actual_amount, 0) actual_amount,
nvl(stg.asc_int_am_sum_lr, 0) fc_discounted_amount,
nvl(pv.CONTINGENT_AMOUNT,0) contingent_amount,
CASE
WHEN pv.due_date > ll.fy_end_date THEN
ll.conversion_rate
WHEN pv.due_date <= ll.fy_end_date
AND ll.func_currency = ll.currency_code THEN
1
WHEN pv.due_date <= ll.fy_end_date
AND ll.user_conversion_rate IS NOT NULL THEN
ll.user_conversion_rate
ELSE
(
SELECT
round(MAX(decode(from_currency, ll.currency_code, conversion_rate, 1 / conversion_rate)) KEEP
(DENSE_RANK FIRST ORDER BY conversion_date DESC, decode(from_currency, ll.currency_code, 1, 2
)), 8)
FROM
gl_daily_rates
WHERE
conversion_type = ll.conversion_type_code
AND ll.currency_code IN (
from_currency,
to_currency
)
AND ll.func_currency IN (
from_currency,
to_currency
)
AND conversion_date <= ll.fy_end_date
AND status_code = 'C'
)
END conversion_rate,
CASE
WHEN ll.compliance_enabled_flag = 'Y'
AND pv.due_date BETWEEN ll.amortization_start_date AND ll.amortization_end_date THEN
'Y'
ELSE
'N'
END compliance_enabled_flag
, ll.pa_CONVERSION_RATE
, ll.Avg_CONVERSION_RATE
FROM
(
SELECT
la.lease_id,
ld.lease_detail_id,
la.lease_number,
la.lease_name,
la.lease_start_date,
ld.termination_date lease_end_date,
ld.commencement_date,
months_between(ld.termination_date + 1, la.lease_start_date) lease_duration,
ld.lease_classification_code,
la.LEASE_ASSET_TYPE_CODE,
la.amortization_proration_code,
decode(la.amortization_proration_code, '360', 360, 365) days_per_year,
la.amortization_start_date,
nvl(pa.amortization_end_date, ld.amortization_end_date) amortization_end_date,
ph.payment_number,
nvl(ph.option_flag,'N') option_flag,
ph.payment_type_code,
ph.payment_purpose_code,
ph.conversion_rate user_conversion_rate,
ph.conversion_type_code,
ld.version_num,
nvl(ph.intercompany_flag,'N') intercompany_flag,
CASE
WHEN ph.rou_asset_flag = 'Y'
OR ph.liability_flag = 'Y' THEN
'Y'
ELSE
'N'
END compliance_enabled_flag,
ph.currency_code currency_code,
nvl(ld.prim_regime_interest_rate, 0) + nvl(ld.prim_regime_adder_rate, 0) discount_rate,
CASE
WHEN per.func_currency = ph.currency_code THEN
1
WHEN ph.conversion_rate IS NOT NULL THEN
ph.conversion_rate
ELSE
(
SELECT
round(MAX(decode(from_currency, ph.currency_code, conversion_rate, 1 / conversion_rate
)) KEEP(DENSE_RANK FIRST ORDER BY conversion_date DESC, decode(from_currency, ph.currency_code
, 1, 2)), 8)
FROM
gl_daily_rates
WHERE
conversion_type = ph.conversion_type_code
AND ph.currency_code IN (
from_currency,
to_currency
)
AND per.func_currency IN (
from_currency,
to_currency
)
AND conversion_date <= per.fy_end_date
AND status_code = 'C'
)
END conversion_rate,
per.*
FROM
fla_leases_all la,
fla_lease_details ld,
(
SELECT *
FROM (
SELECT
x.org_id,
x.ledger_id,
x.regime_code,
x.func_currency,
x.fc_precision,
x.fy_start_date,
x.fy_end_date,
x.period_start_date,
x.period_end_date,
x.period_name,
x.calander_py_end_date,
x.calander_1year_end_date,
x.calander_2year_end_date,
x.calander_3year_end_date,
x.calander_4year_end_date,
x.calander_5year_end_date,
x.py_year,
x.fy_year,
x.calander_1year,
x.calander_2year,
x.calander_3year,
x.calander_4year,
x.calander_5year,
/* Final monthly rate with carry-forward, seeded from prior year */
CASE
WHEN x.func_currency = 'USD' THEN 1
ELSE
LAST_VALUE(
NVL(
x.base_rate, /* rate on this month-end, if present */
CASE WHEN x.period_end_date = x.first_end_date /* seed only on the first month row */
THEN x.seed_rate
END
)
IGNORE NULLS
) OVER (
PARTITION BY x.org_id, x.ledger_id
ORDER BY x.period_end_date /* keep monthly order */
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
END AS pa_conversion_rate,
/* Final monthly Avg rate with carry-forward, seeded from prior year */
CASE
WHEN x.func_currency = 'USD' THEN 1
ELSE
LAST_VALUE(
NVL(
x.Avg_base_rate, /* rate on this month-end, if present */
CASE WHEN x.period_end_date = x.first_end_date /* seed only on the first month row */
THEN x.Avg_seed_rate
END
)
IGNORE NULLS
) OVER (
PARTITION BY x.org_id, x.ledger_id
ORDER BY x.period_end_date /* keep monthly order */
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
END AS Avg_conversion_rate
FROM (
SELECT
so.org_id,
gld.ledger_id,
CASE
WHEN gld.ledger_id = so.ledger_id THEN so.PRIMARY_REGIME_CODE
ELSE so.SECONDARY_REGIME_CODE
END AS regime_code,
gld.currency_code AS func_currency,
NVL(fc.precision, 2) AS fc_precision,
/* === Corrected fiscal year boundaries (year-wise) === */
MIN(gp.start_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year) AS fy_start_date,
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year) AS fy_end_date,
/* === Period-wise dates (new) === */
gp.start_date AS period_start_date,
gp.end_date AS period_end_date,
gp.period_name AS period_name,
/* Calendar-related outputs based on FY dates */
(MIN(gp.start_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year) - 1) AS calander_py_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
12) AS calander_1year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
24) AS calander_2year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
36) AS calander_3year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
48) AS calander_4year_end_date,
ADD_MONTHS(
MAX(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id, gp.period_year),
60) AS calander_5year_end_date,
(gp.period_year - 1) AS py_year,
gp.period_year AS fy_year,
(gp.period_year + 1) AS calander_1year,
(gp.period_year + 2) AS calander_2year,
(gp.period_year + 3) AS calander_3year,
(gp.period_year + 4) AS calander_4year,
(gp.period_year + 5) AS calander_5year,
/* This month’s direct rate (USD=1 handled in outer layer) */
r.conversion_rate AS base_rate,
Avg_r.conversion_rate AS Avg_base_rate,
/* Seed: last available rate before fiscal year start (per currency) */
seed.seed_rate,
Avg_seed.seed_rate Avg_seed_rate,
/* First month-end date for the ledger in this year (per partition) */
MIN(gp.end_date)
OVER (PARTITION BY so.org_id, gld.ledger_id) AS first_end_date
FROM
fla_system_options_all so,
gl_ledgers gld,
fnd_currencies_b fc,
gl_periods gp,
/* ===== Corporate rates for exact month-end (left join) ===== */
(
SELECT
gdr.from_currency,
gdr.conversion_date,
gdr.conversion_rate
FROM
gl_daily_rates gdr,
gl_daily_conversion_types gdc
WHERE
gdc.conversion_type = gdr.conversion_type
AND UPPER(gdc.user_conversion_type) = 'CORPORATE'
AND gdr.to_currency = 'USD'
AND gdr.from_currency <> gdr.to_currency
) r,
/* ===== Seed rates: last rate prior to fiscal year start (per currency, per FY start) ===== */
(
SELECT
p.from_currency,
p.year_start_date,
/* pick rate corresponding to the MAX(conversion_date) < year_start_date */
MAX(gdr.conversion_rate)
KEEP (DENSE_RANK LAST ORDER BY gdr.conversion_date) AS seed_rate
FROM
(
/* Build the set of (currency, FY start) pairs for the requested year */
SELECT DISTINCT
gld2.currency_code AS from_currency,
gp2.year_start_date
FROM
gl_ledgers gld2,
gl_periods gp2
WHERE
gp2.period_year = :P_YEAR
AND gp2.adjustment_period_flag = 'N'
AND gld2.period_set_name = gp2.period_set_name
AND gld2.accounted_period_type = gp2.period_type
) p,
gl_daily_rates gdr,
gl_daily_conversion_types gdc
WHERE
gdc.conversion_type = gdr.conversion_type
AND UPPER(gdc.user_conversion_type) = 'CORPORATE'
AND gdr.to_currency = 'USD'
AND gdr.from_currency = p.from_currency
AND gdr.conversion_date < p.year_start_date
GROUP BY
p.from_currency, p.year_start_date
) seed,
(
SELECT
gdr.from_currency,
gdr.conversion_date,
gdr.conversion_rate
FROM
gl_daily_rates gdr,
gl_daily_conversion_types gdc
WHERE
gdc.conversion_type = gdr.conversion_type
AND UPPER(gdc.user_conversion_type) = 'PERIOD AVERAGE'
AND gdr.to_currency = 'USD'
AND gdr.from_currency <> gdr.to_currency
) Avg_r,
/* ===== Seed rates: last rate prior to fiscal year start (per currency, per FY start) ===== */
(
SELECT
p.from_currency,
p.year_start_date,
/* pick rate corresponding to the MAX(conversion_date) < year_start_date */
MAX(gdr.conversion_rate)
KEEP (DENSE_RANK LAST ORDER BY gdr.conversion_date) AS seed_rate
FROM
(
/* Build the set of (currency, FY start) pairs for the requested year */
SELECT DISTINCT
gld2.currency_code AS from_currency,
gp2.year_start_date
FROM
gl_ledgers gld2,
gl_periods gp2
WHERE
gp2.period_year = :P_YEAR
AND gp2.adjustment_period_flag = 'N'
AND gld2.period_set_name = gp2.period_set_name
AND gld2.accounted_period_type = gp2.period_type
) p,
gl_daily_rates gdr,
gl_daily_conversion_types gdc
WHERE
gdc.conversion_type = gdr.conversion_type
AND UPPER(gdc.user_conversion_type) = 'PERIOD AVERAGE'
AND gdr.to_currency = 'USD'
AND gdr.from_currency = p.from_currency
AND gdr.conversion_date < p.year_start_date
GROUP BY
p.from_currency, p.year_start_date
) Avg_seed,
fnd_lookup_values_vl flv
WHERE
/* Ledger scope - regimes */
gld.ledger_id IN (so.ledger_id, so.SECONDARY_LEDGER_ID)
AND ( UPPER(so.PRIMARY_REGIME_CODE) = 'ASC842'
OR UPPER(so.SECONDARY_REGIME_CODE) = 'ASC842' )
/* Ledger setup and periods (monthly, non-adjustment) */
AND gld.currency_code = fc.currency_code
AND gld.period_set_name = gp.period_set_name
AND gld.accounted_period_type = gp.period_type
AND gp.adjustment_period_flag = 'N'
AND gp.period_year = :P_YEAR
/* Left join to the monthly rate for that month-end date */
AND r.from_currency(+) = gld.currency_code
AND r.conversion_date(+) = gp.end_date
/* Left join seed by currency + fiscal year start */
AND seed.from_currency(+) = gld.currency_code
AND seed.year_start_date(+) = gp.year_start_date
/* Left join to the monthly rate for that month-end date */
AND Avg_r.from_currency(+) = gld.currency_code
AND Avg_r.conversion_date(+) = gp.end_date
/* Left join seed by currency + fiscal year start */
AND Avg_seed.from_currency(+) = gld.currency_code
AND Avg_seed.year_start_date(+) = gp.year_start_date
/* US GAAP ledgers constraint */
AND flv.lookup_type = 'FLA_LEDGER_USGAAP'
AND gld.name IN (flv.meaning)
) X
/* NOTE: intentionally NO filter here on period_end_date/fy_end_date,
so the analytic window sees all months and can carry forward properly. */
) Y
WHERE Y.PERIOD_END_DATE = (
SELECT MAX(gp2.end_date)
FROM gl_periods gp2
WHERE gp2.period_set_name = (
SELECT gld2.period_set_name
FROM gl_ledgers gld2
WHERE gld2.ledger_id = Y.ledger_id
)
AND gp2.period_type = (
SELECT gld3.accounted_period_type
FROM gl_ledgers gld3
WHERE gld3.ledger_id = Y.ledger_id
)
AND gp2.adjustment_period_flag = 'N'
AND gp2.period_year = Y.fy_year
)
) per,
fla_lease_payment_headers ph,
fla_lease_assets pa
WHERE
la.lease_id = ld.lease_id
AND ld.version_type_code = 'F'
AND la.org_id = per.org_id
AND ld.lease_detail_id = ph.lease_detail_id
AND ld.lease_id = ph.lease_id
--AND la.legal_entity_id = DECODE(:P_ROU_LEGAL_ENTITY_ID , -1 , la.legal_entity_id, :P_ROU_LEGAL_ENTITY_ID)
AND ph.asset_number = pa.asset_number (+)
AND ph.lease_detail_id = pa.lease_detail_id (+)
AND ph.lease_id = pa.lease_id (+)
) ll,
fla_payment_items pv,
(SELECT s.lease_id,
s.lease_detail_id,
s.currency_code,
s.period_name,
s.schedule_date,
SUM(s.asc_interest_expense_amount) asc_int_am_sum_lr
FROM fusion.fla_on_demand_sch_summary_v s
WHERE
s.DATA_DESC = 'ondemand_schedules'
GROUP BY s.lease_id,
s.lease_detail_id,
s.currency_code,
s.period_name,
s.schedule_date) stg
WHERE
ll.lease_detail_id = pv.lease_detail_id
AND ll.lease_id = pv.lease_id
AND ll.payment_number = pv.payment_number
AND pv.due_date > add_months(ll.calander_py_end_date, - 12)
AND ll.lease_id = stg.lease_id(+)
AND ll.lease_detail_id = stg.lease_detail_id(+)
AND stg.SCHEDULE_DATE(+) Between PV.START_DATE and PV.END_DATE
AND nvl(pv.actual_amount, pv.estimated_amount) <> 0
--AND ll.ledger_id = '300000011103515'
) pay
)
GROUP BY
--func_currency,
--lease_number,
fy_year,
fy_end_date,
calander_1year,
calander_2year,
calander_3year,
calander_4year,
calander_5year
):P_ROU_LEGAL_ENTITY_ID :P_ROU_YEAR :P_YEAR :p_fy_comp_total_expense :p_py_interest_fin_amount :p_py_interest_opr_amount
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_SUMMARY_V | 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.
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_SUMMARY_V | 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.