FLA_ROU_CURRENCY_CONVERSIONS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_NUMBER SCHEDULE_HEADER_ID PAYMENT_NUMBER MIGRATED_FLAG PAYMENT_CURRENCY_CODE LEDGER_CURRENCY_CODE MIGRATION_CONV_RATE LEDGER_CONV_TYPE_CODE LEDGER_USER_CONV_RATE PRIMARY_FLAG REGIME_CODE VERSION_NUM TRANSACTION_DATE TRANSACTION_TYPE_CODE PC_REVALUED_DR PC_REVALUED_CR AC_REVALUED_DR AC_REVALUED_CR PC_REDUCED_DR PC_REDUCED_CR AC_REDUCED_DR AC_REDUCED_CR PC_ADJUSTED_DR PC_ADJUSTED_CR AC_ADJUSTED_DR AC_ADJUSTED_CR PC_ACCRUED_DR PC_ACCRUED_CR AC_ACCRUED_DR AC_ACCRUED_CR PC_PRIOR_OPENING_ROU_AMOUNT PC_PRIOR_ROU_AMOUNT PC_REDUCED_ROU_AMOUNT PC_CLOSING_ROU_AMOUNT PC_ADJUSTED_ROU_AMOUNT PC_OPENING_ROU_AMOUNT PC_PRIOR_ACCRUED_AMOUNT PC_CURRENT_ACCRUED_AMOUNT ROU_CURRENCY_CONVERSION_CODE ROU_TRX_DATE_CURR_CONV_RATE ROU_PRIOR_COMPILED_CONV_RATE ROU_REDUCTION_CURR_CONV_RATE ROU_ADJUSTMENT_CURR_CONV_RATE ROU_COMPILED_CURR_CONV_RATE PARENT_ROU_COMPILED_RATE

Name

LEASE_ID

LEASE_NUMBER

SCHEDULE_HEADER_ID

PAYMENT_NUMBER

MIGRATED_FLAG

PAYMENT_CURRENCY_CODE

LEDGER_CURRENCY_CODE

MIGRATION_CONV_RATE

LEDGER_CONV_TYPE_CODE

LEDGER_USER_CONV_RATE

PRIMARY_FLAG

REGIME_CODE

VERSION_NUM

TRANSACTION_DATE

TRANSACTION_TYPE_CODE

PC_REVALUED_DR

PC_REVALUED_CR

AC_REVALUED_DR

AC_REVALUED_CR

PC_REDUCED_DR

PC_REDUCED_CR

AC_REDUCED_DR

AC_REDUCED_CR

PC_ADJUSTED_DR

PC_ADJUSTED_CR

AC_ADJUSTED_DR

AC_ADJUSTED_CR

PC_ACCRUED_DR

PC_ACCRUED_CR

AC_ACCRUED_DR

AC_ACCRUED_CR

PC_PRIOR_OPENING_ROU_AMOUNT

PC_PRIOR_ROU_AMOUNT

PC_REDUCED_ROU_AMOUNT

PC_CLOSING_ROU_AMOUNT

PC_ADJUSTED_ROU_AMOUNT

PC_OPENING_ROU_AMOUNT

PC_PRIOR_ACCRUED_AMOUNT

PC_CURRENT_ACCRUED_AMOUNT

ROU_CURRENCY_CONVERSION_CODE

ROU_TRX_DATE_CURR_CONV_RATE

ROU_PRIOR_COMPILED_CONV_RATE

ROU_REDUCTION_CURR_CONV_RATE

ROU_ADJUSTMENT_CURR_CONV_RATE

ROU_COMPILED_CURR_CONV_RATE

PARENT_ROU_COMPILED_RATE

Query

SQL_Statement
SELECT a.lease_id, a.lease_number, a.schedule_header_id, a.payment_number, a.migrated_flag, a.payment_currency_code, a.ledger_currency_code, a.migration_conv_rate, a.ledger_conv_type_code, a.ledger_user_conv_rate, a.primary_flag, a.regime_code, a.version_num, a.transaction_date, a.transaction_type_code, /* ROU Revaluation Details / CASE WHEN a.rou_currency_conversion_code IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN ABS(a.pc_prior_rou_amount) ELSE 0 END pc_revalued_dr, CASE WHEN a.rou_currency_conversion_code IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN ABS(a.pc_prior_rou_amount) ELSE 0 END pc_revalued_cr, ROUND( CASE WHEN a.rou_currency_conversion_code NOT IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN 0 WHEN a.pc_prior_rou_amount >= 0 THEN a.pc_prior_rou_amounta.rou_trx_date_curr_conv_rate ELSE a.pc_prior_rou_amount-1a.parent_rou_compiled_rate END, a.precision) ac_revalued_dr, ROUND( CASE WHEN a.rou_currency_conversion_code NOT IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN 0 WHEN a.pc_prior_rou_amount >= 0 THEN a.pc_prior_rou_amounta.parent_rou_compiled_rate ELSE a.pc_prior_rou_amount-1a.rou_trx_date_curr_conv_rate END, a.precision) ac_revalued_cr, / ROU Reduction Details / CASE WHEN a.pc_reduced_rou_amount >= 0 THEN 0 ELSE a.pc_reduced_rou_amount-1 END pc_reduced_dr, CASE WHEN a.pc_reduced_rou_amount >= 0 THEN a.pc_reduced_rou_amount ELSE 0 END pc_reduced_cr, ROUND( CASE WHEN a.pc_reduced_rou_amount >= 0 THEN 0 ELSE a.pc_reduced_rou_amount-1NVL(a.rou_reduction_curr_conv_rate, a.rou_trx_date_curr_conv_rate) END, a.precision) ac_reduced_dr, ROUND( CASE WHEN a.pc_reduced_rou_amount >= 0 THEN a.pc_reduced_rou_amountNVL(a.rou_reduction_curr_conv_rate, a.rou_trx_date_curr_conv_rate) ELSE 0 END, a.precision) ac_reduced_cr, / ROU Adjustment Details / CASE WHEN a.pc_adjusted_rou_amount >= 0 THEN a.pc_adjusted_rou_amount ELSE 0 END pc_adjusted_dr, CASE WHEN a.pc_adjusted_rou_amount >= 0 THEN 0 ELSE a.pc_adjusted_rou_amount-1 END pc_adjusted_cr, ROUND( CASE WHEN a.pc_adjusted_rou_amount >= 0 THEN a.pc_adjusted_rou_amountNVL(a.rou_adjustment_curr_conv_rate, a.rou_trx_date_curr_conv_rate) ELSE 0 END, a.precision) ac_adjusted_dr, ROUND( CASE WHEN a.pc_adjusted_rou_amount >= 0 THEN 0 ELSE a.pc_adjusted_rou_amount-1NVL(a.rou_adjustment_curr_conv_rate, a.rou_trx_date_curr_conv_rate) END, a.precision) ac_adjusted_cr, / ROU Accrual Details / CASE WHEN a.pc_total_rou_accrued_amount >= 0 THEN 0 ELSE a.pc_total_rou_accrued_amount-1 END pc_accrued_dr, CASE WHEN a.pc_total_rou_accrued_amount >= 0 THEN a.pc_total_rou_accrued_amount ELSE 0 END pc_accrued_cr, ROUND( CASE WHEN a.pc_total_rou_accrued_amount >= 0 THEN 0 ELSE a.pc_total_rou_accrued_amount-1NVL(a.rou_compiled_curr_conv_rate, a.rou_trx_date_curr_conv_rate) END, a.precision) ac_accrued_dr, ROUND( CASE WHEN a.pc_total_rou_accrued_amount >= 0 THEN a.pc_total_rou_accrued_amount*NVL(a.rou_compiled_curr_conv_rate, a.rou_trx_date_curr_conv_rate) ELSE 0 END, a.precision) ac_accrued_cr, a.pc_prior_opening_rou_amount, a.pc_prior_rou_amount, a.pc_reduced_rou_amount, a.pc_closing_rou_amount, a.pc_adjusted_rou_amount, a.pc_opening_rou_amount, NVL(a.pc_prior_accured_amount, 0) pc_prior_accrued_amount, NVL(a.pc_accrued_amount, 0) pc_current_accrued_amount, a.rou_currency_conversion_code, a.rou_trx_date_curr_conv_rate, a.rou_prior_compiled_conv_rate, a.rou_reduction_curr_conv_rate, a.rou_adjustment_curr_conv_rate, a.rou_compiled_curr_conv_rate, a.parent_rou_compiled_rate FROM ( SELECT sh.lease_id, sh.lease_number, sh.schedule_header_id, sh.payment_number, sh.migrated_flag, sh.payment_currency_code, ld.currency_code ledger_currency_code, DECODE(rd.primary_flag, 'Y', sh.migration_conversion_rate, TO_NUMBER(NULL)) migration_conv_rate, DECODE(rd.primary_flag, 'Y', sh.conversion_type_code, glr.alc_default_conv_rate_type) ledger_conv_type_code, DECODE(rd.primary_flag, 'Y', sh.conversion_rate, TO_NUMBER(NULL)) ledger_user_conv_rate, rd.primary_flag, sh.regime_code, sh.version_num, sh.transaction_type_code, sh.transaction_date, sh.pc_prior_rou_amount, sh.pc_reduced_rou_amount, sh.pc_closing_rou_amount, DECODE(sh.parent_record_id, NULL, NVL(sh.pc_opening_rou_amount,0) + + NVL(sh.pc_prior_accured_amount,0), NVL(sh.pc_adjusted_rou_amount,0)) pc_adjusted_rou_amount, sh.pc_opening_rou_amount, sh.pc_prior_accured_amount, sh.pc_accrued_amount, DECODE(sh.parent_record_id, NULL, NVL(sh.pc_opening_rou_amount,0) + NVL(sh.pc_prior_accured_amount,0), NVL(sh.pc_adjusted_rou_amount,0)) pc_total_rou_addition_amount, sh.pc_reduced_rou_amount pc_total_rou_reduction_amount, NVL(sh.pc_prior_accured_amount,0) + NVL(sh.pc_accrued_amount, 0) pc_total_rou_accrued_amount, sh.rou_currency_conversion_code, CASE WHEN sh.rou_trx_date_curr_conv_rate IS NOT NULL THEN sh.rou_trx_date_curr_conv_rate WHEN sh.payment_currency_code = ld.currency_code THEN 1 WHEN rd.primary_flag = 'Y' AND sh.migrated_flag = 'Y' AND sh.transaction_type_code = 'BOOKING' THEN sh.migration_conversion_rate WHEN rd.primary_flag = 'Y' AND sh.conversion_type_code = 'User' THEN sh.conversion_rate WHEN rd.primary_flag = 'Y' THEN NULLIF(NULLIF(gl_currency_api.get_rate_sql(sh.payment_currency_code, ld.currency_code, sh.transaction_date, sh.conversion_type_code),-1),-2) ELSE NULLIF(NULLIF(gl_currency_api.get_rate_sql(sh.payment_currency_code, ld.currency_code, sh.transaction_date, glr.alc_default_conv_rate_type),-1),-2) END rou_trx_date_curr_conv_rate, sh.rou_prior_compiled_conv_rate, sh.rou_reduction_curr_conv_rate, sh.rou_adjustment_curr_conv_rate, sh.rou_compiled_curr_conv_rate, CASE WHEN psh.lease_classification_code = 'OPERATING' THEN psh.forecasted_oper_rou_amount ELSE psh.forecasted_fin_rou_amount END pc_prior_opening_rou_amount, CASE WHEN psh.schedule_header_id IS NULL THEN 0 WHEN psh.currency_code = ld.currency_code THEN 1 WHEN psh.rou_compiled_curr_conv_rate IS NOT NULL THEN psh.rou_compiled_curr_conv_rate WHEN rd.primary_flag = 'Y' AND sh.migrated_flag = 'Y' AND psh.transaction_type_code = 'BOOKING' THEN sh.migration_conversion_rate WHEN rd.primary_flag = 'Y' AND sh.conversion_type_code = 'User' THEN sh.conversion_rate WHEN rd.primary_flag = 'Y' THEN NULLIF(NULLIF(gl_currency_api.get_rate_sql(psh.currency_code, ld.currency_code, psh.transaction_date, sh.conversion_type_code),-1),-2) ELSE NULLIF(NULLIF(gl_currency_api.get_rate_sql(psh.currency_code, ld.currency_code, psh.transaction_date, glr.alc_default_conv_rate_type),-1),-2) END parent_rou_compiled_rate, c.precision FROM ( SELECT s.lease_id, la.org_id, s.schedule_header_id, la.lease_number, s.payment_number, ph.currency_code payment_currency_code, ph.migrated_flag, ph.migration_conversion_rate, ph.conversion_type_code, ph.conversion_rate, s.regime_code, s.version_num, s.transaction_type_code, s.transaction_date, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.operating_accum_amort_amount ELSE s.finance_accum_amort_amount END pc_prior_accured_amount, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.prior_oper_rou_amount ELSE s.prior_fin_rou_amount END pc_prior_rou_amount, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.reduced_oper_rou_amount ELSE s.reduced_fin_rou_amount END pc_reduced_rou_amount, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.closing_oper_rou_amount ELSE s.closing_fin_rou_amount END pc_closing_rou_amount, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.adjusted_oper_rou_amount ELSE s.adjusted_fin_rou_amount END pc_adjusted_rou_amount, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.forecasted_oper_rou_amount ELSE s.forecasted_fin_rou_amount END pc_opening_rou_amount, SUM(CASE WHEN s.lease_classification_code = 'OPERATING' THEN NVL(acs.schedule2_amount,0) ELSE NVL(acs.schedule1_amount,0) END) pc_accrued_amount, s.rou_currency_conversion_code, s.rou_trx_date_curr_conv_rate, s.rou_prior_compiled_conv_rate, s.rou_reduction_curr_conv_rate, s.rou_adjustment_curr_conv_rate, s.rou_compiled_curr_conv_rate, s.parent_record_id FROM fla_leases_all la, fla_schedule_headers s, fla_lease_payment_headers ph, fla_accrual_schedules acs WHERE la.lease_id = s.lease_id AND s.lease_detail_id = ph.lease_detail_id AND s.payment_number = ph.payment_number AND ph.rou_asset_flag = 'Y' AND s.schedule_header_id = acs.schedule_header_id(+) AND s.lease_classification_code <> 'EXEMPT' AND accrual_reversal_flag(+) = 'N' GROUP BY s.lease_id, la.org_id, la.lease_number, s.schedule_header_id, s.payment_number, ph.currency_code, ph.migrated_flag, ph.migration_conversion_rate, ph.conversion_type_code, ph.conversion_rate, s.regime_code, s.version_num, s.transaction_type_code, s.transaction_date, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.operating_accum_amort_amount ELSE s.finance_accum_amort_amount END, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.prior_oper_rou_amount ELSE s.prior_fin_rou_amount END, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.reduced_oper_rou_amount ELSE s.reduced_fin_rou_amount END, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.closing_oper_rou_amount ELSE s.closing_fin_rou_amount END, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.adjusted_oper_rou_amount ELSE s.adjusted_fin_rou_amount END, CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.forecasted_oper_rou_amount ELSE s.forecasted_fin_rou_amount END, s.rou_currency_conversion_code, s.rou_trx_date_curr_conv_rate, s.rou_prior_compiled_conv_rate, s.rou_reduction_curr_conv_rate, s.rou_adjustment_curr_conv_rate, s.rou_compiled_curr_conv_rate, s.parent_record_id ) sh, fla_schedule_headers psh, fla_system_options_all so, fla_regime_details_v rd, gl_ledgers ld, fnd_currencies_b c, gl_ledger_relationships glr WHERE sh.parent_record_id = psh.schedule_header_id(+) AND sh.lease_id = psh.lease_id(+) AND sh.org_id = so.org_id AND sh.org_id = rd.org_id AND sh.regime_code = rd.regime_code AND rd.ledger_id = ld.ledger_id AND ld.currency_code = c.currency_code AND so.ledger_id = glr.source_ledger_id(+) AND so.secondary_ledger_id = glr.target_ledger_id(+) AND glr.target_ledger_category_code(+) = 'SECONDARY' AND glr.relationship_type_code(+) = 'SUBLEDGER' AND glr.relationship_enabled_flag(+) = 'Y' AND so.ledger_id = glr.source_ledger_id(+) ) a

SQL_Statement

SELECT a.lease_id,

a.lease_number,

a.schedule_header_id,

a.payment_number,

a.migrated_flag,

a.payment_currency_code,

a.ledger_currency_code,

a.migration_conv_rate,

a.ledger_conv_type_code,

a.ledger_user_conv_rate,

a.primary_flag,

a.regime_code,

a.version_num,

a.transaction_date,

a.transaction_type_code,

/* ROU Revaluation Details */

CASE

WHEN a.rou_currency_conversion_code IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN ABS(a.pc_prior_rou_amount)

ELSE 0

END pc_revalued_dr,

CASE

WHEN a.rou_currency_conversion_code IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN ABS(a.pc_prior_rou_amount)

ELSE 0

END pc_revalued_cr,

ROUND(

CASE

WHEN a.rou_currency_conversion_code NOT IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN 0

WHEN a.pc_prior_rou_amount >= 0 THEN a.pc_prior_rou_amount*a.rou_trx_date_curr_conv_rate

ELSE a.pc_prior_rou_amount*-1*a.parent_rou_compiled_rate

END, a.precision) ac_revalued_dr,

ROUND(

CASE

WHEN a.rou_currency_conversion_code NOT IN ('SPOT_FOR_ENTIRE_BALANCE', 'NO_COMPILED_RATE') THEN 0

WHEN a.pc_prior_rou_amount >= 0 THEN a.pc_prior_rou_amount*a.parent_rou_compiled_rate

ELSE a.pc_prior_rou_amount*-1*a.rou_trx_date_curr_conv_rate

END, a.precision) ac_revalued_cr,

/* ROU Reduction Details */

CASE WHEN a.pc_reduced_rou_amount >= 0 THEN 0 ELSE a.pc_reduced_rou_amount*-1 END pc_reduced_dr,

CASE WHEN a.pc_reduced_rou_amount >= 0 THEN a.pc_reduced_rou_amount ELSE 0 END pc_reduced_cr,

ROUND(

CASE

WHEN a.pc_reduced_rou_amount >= 0 THEN 0

ELSE a.pc_reduced_rou_amount*-1*NVL(a.rou_reduction_curr_conv_rate, a.rou_trx_date_curr_conv_rate)

END, a.precision) ac_reduced_dr,

ROUND(

CASE

WHEN a.pc_reduced_rou_amount >= 0 THEN a.pc_reduced_rou_amount*NVL(a.rou_reduction_curr_conv_rate, a.rou_trx_date_curr_conv_rate)

ELSE 0

END, a.precision) ac_reduced_cr,

/* ROU Adjustment Details */

CASE WHEN a.pc_adjusted_rou_amount >= 0 THEN a.pc_adjusted_rou_amount ELSE 0 END pc_adjusted_dr,

CASE WHEN a.pc_adjusted_rou_amount >= 0 THEN 0 ELSE a.pc_adjusted_rou_amount*-1 END pc_adjusted_cr,

ROUND(

CASE

WHEN a.pc_adjusted_rou_amount >= 0 THEN a.pc_adjusted_rou_amount*NVL(a.rou_adjustment_curr_conv_rate, a.rou_trx_date_curr_conv_rate)

ELSE 0

END, a.precision) ac_adjusted_dr,

ROUND(

CASE

WHEN a.pc_adjusted_rou_amount >= 0 THEN 0

ELSE a.pc_adjusted_rou_amount*-1*NVL(a.rou_adjustment_curr_conv_rate, a.rou_trx_date_curr_conv_rate)

END, a.precision) ac_adjusted_cr,

/* ROU Accrual Details */

CASE WHEN a.pc_total_rou_accrued_amount >= 0 THEN 0 ELSE a.pc_total_rou_accrued_amount*-1 END pc_accrued_dr,

CASE WHEN a.pc_total_rou_accrued_amount >= 0 THEN a.pc_total_rou_accrued_amount ELSE 0 END pc_accrued_cr,

ROUND(

CASE

WHEN a.pc_total_rou_accrued_amount >= 0 THEN 0

ELSE a.pc_total_rou_accrued_amount*-1*NVL(a.rou_compiled_curr_conv_rate, a.rou_trx_date_curr_conv_rate)

END, a.precision) ac_accrued_dr,

ROUND(

CASE

WHEN a.pc_total_rou_accrued_amount >= 0 THEN a.pc_total_rou_accrued_amount*NVL(a.rou_compiled_curr_conv_rate, a.rou_trx_date_curr_conv_rate)

ELSE 0

END, a.precision) ac_accrued_cr,

a.pc_prior_opening_rou_amount,

a.pc_prior_rou_amount,

a.pc_reduced_rou_amount,

a.pc_closing_rou_amount,

a.pc_adjusted_rou_amount,

a.pc_opening_rou_amount,

NVL(a.pc_prior_accured_amount, 0) pc_prior_accrued_amount,

NVL(a.pc_accrued_amount, 0) pc_current_accrued_amount,

a.rou_currency_conversion_code,

a.rou_trx_date_curr_conv_rate,

a.rou_prior_compiled_conv_rate,

a.rou_reduction_curr_conv_rate,

a.rou_adjustment_curr_conv_rate,

a.rou_compiled_curr_conv_rate,

a.parent_rou_compiled_rate

FROM (

SELECT sh.lease_id,

sh.lease_number,

sh.schedule_header_id,

sh.payment_number,

sh.migrated_flag,

sh.payment_currency_code,

ld.currency_code ledger_currency_code,

DECODE(rd.primary_flag, 'Y', sh.migration_conversion_rate, TO_NUMBER(NULL)) migration_conv_rate,

DECODE(rd.primary_flag, 'Y', sh.conversion_type_code, glr.alc_default_conv_rate_type) ledger_conv_type_code,

DECODE(rd.primary_flag, 'Y', sh.conversion_rate, TO_NUMBER(NULL)) ledger_user_conv_rate,

rd.primary_flag,

sh.regime_code,

sh.version_num,

sh.transaction_type_code,

sh.transaction_date,

sh.pc_prior_rou_amount,

sh.pc_reduced_rou_amount,

sh.pc_closing_rou_amount,

DECODE(sh.parent_record_id, NULL, NVL(sh.pc_opening_rou_amount,0) + + NVL(sh.pc_prior_accured_amount,0), NVL(sh.pc_adjusted_rou_amount,0)) pc_adjusted_rou_amount,

sh.pc_opening_rou_amount,

sh.pc_prior_accured_amount,

sh.pc_accrued_amount,

DECODE(sh.parent_record_id, NULL, NVL(sh.pc_opening_rou_amount,0) + NVL(sh.pc_prior_accured_amount,0), NVL(sh.pc_adjusted_rou_amount,0)) pc_total_rou_addition_amount,

sh.pc_reduced_rou_amount pc_total_rou_reduction_amount,

NVL(sh.pc_prior_accured_amount,0) + NVL(sh.pc_accrued_amount, 0) pc_total_rou_accrued_amount,

sh.rou_currency_conversion_code,

CASE

WHEN sh.rou_trx_date_curr_conv_rate IS NOT NULL THEN sh.rou_trx_date_curr_conv_rate

WHEN sh.payment_currency_code = ld.currency_code THEN 1

WHEN rd.primary_flag = 'Y' AND sh.migrated_flag = 'Y' AND sh.transaction_type_code = 'BOOKING' THEN sh.migration_conversion_rate

WHEN rd.primary_flag = 'Y' AND sh.conversion_type_code = 'User' THEN sh.conversion_rate

WHEN rd.primary_flag = 'Y' THEN NULLIF(NULLIF(gl_currency_api.get_rate_sql(sh.payment_currency_code, ld.currency_code, sh.transaction_date, sh.conversion_type_code),-1),-2)

ELSE NULLIF(NULLIF(gl_currency_api.get_rate_sql(sh.payment_currency_code, ld.currency_code, sh.transaction_date, glr.alc_default_conv_rate_type),-1),-2)

END rou_trx_date_curr_conv_rate,

sh.rou_prior_compiled_conv_rate,

sh.rou_reduction_curr_conv_rate,

sh.rou_adjustment_curr_conv_rate,

sh.rou_compiled_curr_conv_rate,

CASE WHEN psh.lease_classification_code = 'OPERATING' THEN psh.forecasted_oper_rou_amount ELSE psh.forecasted_fin_rou_amount END pc_prior_opening_rou_amount,

CASE

WHEN psh.schedule_header_id IS NULL THEN 0

WHEN psh.currency_code = ld.currency_code THEN 1

WHEN psh.rou_compiled_curr_conv_rate IS NOT NULL THEN psh.rou_compiled_curr_conv_rate

WHEN rd.primary_flag = 'Y' AND sh.migrated_flag = 'Y' AND psh.transaction_type_code = 'BOOKING' THEN sh.migration_conversion_rate

WHEN rd.primary_flag = 'Y' AND sh.conversion_type_code = 'User' THEN sh.conversion_rate

WHEN rd.primary_flag = 'Y' THEN NULLIF(NULLIF(gl_currency_api.get_rate_sql(psh.currency_code, ld.currency_code, psh.transaction_date, sh.conversion_type_code),-1),-2)

ELSE NULLIF(NULLIF(gl_currency_api.get_rate_sql(psh.currency_code, ld.currency_code, psh.transaction_date, glr.alc_default_conv_rate_type),-1),-2)

END parent_rou_compiled_rate,

c.precision

FROM (

SELECT s.lease_id,

la.org_id,

s.schedule_header_id,

la.lease_number,

s.payment_number,

ph.currency_code payment_currency_code,

ph.migrated_flag,

ph.migration_conversion_rate,

ph.conversion_type_code,

ph.conversion_rate,

s.regime_code,

s.version_num,

s.transaction_type_code,

s.transaction_date,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.operating_accum_amort_amount ELSE s.finance_accum_amort_amount END pc_prior_accured_amount,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.prior_oper_rou_amount ELSE s.prior_fin_rou_amount END pc_prior_rou_amount,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.reduced_oper_rou_amount ELSE s.reduced_fin_rou_amount END pc_reduced_rou_amount,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.closing_oper_rou_amount ELSE s.closing_fin_rou_amount END pc_closing_rou_amount,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.adjusted_oper_rou_amount ELSE s.adjusted_fin_rou_amount END pc_adjusted_rou_amount,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.forecasted_oper_rou_amount ELSE s.forecasted_fin_rou_amount END pc_opening_rou_amount,

SUM(CASE WHEN s.lease_classification_code = 'OPERATING' THEN NVL(acs.schedule2_amount,0) ELSE NVL(acs.schedule1_amount,0) END) pc_accrued_amount,

s.rou_currency_conversion_code,

s.rou_trx_date_curr_conv_rate,

s.rou_prior_compiled_conv_rate,

s.rou_reduction_curr_conv_rate,

s.rou_adjustment_curr_conv_rate,

s.rou_compiled_curr_conv_rate,

s.parent_record_id

FROM fla_leases_all la,

fla_schedule_headers s,

fla_lease_payment_headers ph,

fla_accrual_schedules acs

WHERE la.lease_id = s.lease_id

AND s.lease_detail_id = ph.lease_detail_id

AND s.payment_number = ph.payment_number

AND ph.rou_asset_flag = 'Y'

AND s.schedule_header_id = acs.schedule_header_id(+)

AND s.lease_classification_code <> 'EXEMPT'

AND accrual_reversal_flag(+) = 'N'

GROUP BY s.lease_id,

la.org_id,

la.lease_number,

s.schedule_header_id,

s.payment_number,

ph.currency_code,

ph.migrated_flag,

ph.migration_conversion_rate,

ph.conversion_type_code,

ph.conversion_rate,

s.regime_code,

s.version_num,

s.transaction_type_code,

s.transaction_date,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.operating_accum_amort_amount ELSE s.finance_accum_amort_amount END,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.prior_oper_rou_amount ELSE s.prior_fin_rou_amount END,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.reduced_oper_rou_amount ELSE s.reduced_fin_rou_amount END,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.closing_oper_rou_amount ELSE s.closing_fin_rou_amount END,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.adjusted_oper_rou_amount ELSE s.adjusted_fin_rou_amount END,

CASE WHEN s.lease_classification_code = 'OPERATING' THEN s.forecasted_oper_rou_amount ELSE s.forecasted_fin_rou_amount END,

s.rou_currency_conversion_code,

s.rou_trx_date_curr_conv_rate,

s.rou_prior_compiled_conv_rate,

s.rou_reduction_curr_conv_rate,

s.rou_adjustment_curr_conv_rate,

s.rou_compiled_curr_conv_rate,

s.parent_record_id

) sh,

fla_schedule_headers psh,

fla_system_options_all so,

fla_regime_details_v rd,

gl_ledgers ld,

fnd_currencies_b c,

gl_ledger_relationships glr

WHERE sh.parent_record_id = psh.schedule_header_id(+)

AND sh.lease_id = psh.lease_id(+)

AND sh.org_id = so.org_id

AND sh.org_id = rd.org_id

AND sh.regime_code = rd.regime_code

AND rd.ledger_id = ld.ledger_id

AND ld.currency_code = c.currency_code

AND so.ledger_id = glr.source_ledger_id(+)

AND so.secondary_ledger_id = glr.target_ledger_id(+)

AND glr.target_ledger_category_code(+) = 'SECONDARY'

AND glr.relationship_type_code(+) = 'SUBLEDGER'

AND glr.relationship_enabled_flag(+) = 'Y'

AND so.ledger_id = glr.source_ledger_id(+)

) a