FLA_BILLING_IMPACTS

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID VERSION_TYPE_CODE VERSION_NUM APPROVAL_STATUS_CODE TRANSACTION_TYPE_CODE TRANSACTION_SUBTYPE_CODE SCHEDULES_REFRESH_NEEDED_FLAG DV_LEASE_CLASSIFICATION_CODE DV_AMORT_PRORATION_CODE LEASE_TERMINATION_FLAG PAYMENT_NUMBER OPTION_FLAG PAYMENT_START_DATE PAYMENT_END_DATE PAYMENT_STATUS_CODE OPTION_STATUS_CODE ASSET_NUMBER ASSET_STATUS_CODE CURRENCY_CODE BILL_TO_CUSTOMER_ID BILL_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID ASSET_TERMINATION_FLAG SCHEDULES_IMPACT_FLAG PAYMENT_ITEMS_IMPACT_FLAG REVENUE_METHOD_CODE CLOSED_FLAG CHANGE_TYPE_CODE CHANGED_IN_VERSION_NUM CONTINGENT_ONLY_IMPACT_FLAG MIGRATION_SCHEDULE_FLAG MIGRATED_FIN_UER_BALANCE MIGRATED_OPER_UER_BALANCE REGIME_CODE DV_AMORTIZATION_START_DATE DV_AMORTIZATION_END_DATE DRAFT_SCHEDULES_EXISTS_FLAG DRAFT_SCHEDULE_HEADER_ID DRAFT_PARENT_RECORD_ID REGENERATE_SCHEDULES_FLAG DELETE_DRAFT_SCHEDULES_FLAG NO_SCHEDULES_FLAG MIGRATION_INFO_UPDATED_FLAG CUSTOMER_INFO_UPDATED_FLAG ONETIME_INFO_UPDATED_FLAG DSH_AMORTIZATION_START_DATE DSH_AMORTIZATION_END_DATE FSH_AMORTIZATION_START_DATE FSH_AMORTIZATION_END_DATE INTEREST_COMPOUNDING_CODE INTEREST_COMPOUNDING_DAYS END_OF_DAY_PAYMENT_FLAG

Name

LEASE_ID

LEASE_DETAIL_ID

VERSION_TYPE_CODE

VERSION_NUM

APPROVAL_STATUS_CODE

TRANSACTION_TYPE_CODE

TRANSACTION_SUBTYPE_CODE

SCHEDULES_REFRESH_NEEDED_FLAG

DV_LEASE_CLASSIFICATION_CODE

DV_AMORT_PRORATION_CODE

LEASE_TERMINATION_FLAG

PAYMENT_NUMBER

OPTION_FLAG

PAYMENT_START_DATE

PAYMENT_END_DATE

PAYMENT_STATUS_CODE

OPTION_STATUS_CODE

ASSET_NUMBER

ASSET_STATUS_CODE

CURRENCY_CODE

BILL_TO_CUSTOMER_ID

BILL_TO_SITE_USE_ID

SHIP_TO_SITE_USE_ID

ASSET_TERMINATION_FLAG

SCHEDULES_IMPACT_FLAG

PAYMENT_ITEMS_IMPACT_FLAG

REVENUE_METHOD_CODE

CLOSED_FLAG

CHANGE_TYPE_CODE

CHANGED_IN_VERSION_NUM

CONTINGENT_ONLY_IMPACT_FLAG

MIGRATION_SCHEDULE_FLAG

MIGRATED_FIN_UER_BALANCE

MIGRATED_OPER_UER_BALANCE

REGIME_CODE

DV_AMORTIZATION_START_DATE

DV_AMORTIZATION_END_DATE

DRAFT_SCHEDULES_EXISTS_FLAG

DRAFT_SCHEDULE_HEADER_ID

DRAFT_PARENT_RECORD_ID

REGENERATE_SCHEDULES_FLAG

DELETE_DRAFT_SCHEDULES_FLAG

NO_SCHEDULES_FLAG

MIGRATION_INFO_UPDATED_FLAG

CUSTOMER_INFO_UPDATED_FLAG

ONETIME_INFO_UPDATED_FLAG

DSH_AMORTIZATION_START_DATE

DSH_AMORTIZATION_END_DATE

FSH_AMORTIZATION_START_DATE

FSH_AMORTIZATION_END_DATE

INTEREST_COMPOUNDING_CODE

INTEREST_COMPOUNDING_DAYS

END_OF_DAY_PAYMENT_FLAG

Query

SQL_Statement
SELECT a.lease_id, a.lease_detail_id, a.version_type_code, a.version_num, a.approval_status_code, a.transaction_type_code, a.transaction_subtype_code, a.schedules_refresh_needed_flag, a.dv_lease_classification_code, a.dv_amort_proration_code, a.lease_termination_flag, a.payment_number, a.option_flag, a.payment_start_date, a.payment_end_date, a.payment_status_code, a.option_status_code, a.asset_number, a.asset_status_code, a.currency_code, a.bill_to_customer_id, a.bill_to_site_use_id, a.ship_to_site_use_id, a.asset_termination_flag, a.schedules_impact_flag, a.payment_items_impact_flag, a.revenue_method_code, a.closed_flag, a.change_type_code, a.changed_in_version_num, a.contingent_only_impact_flag, a.migration_schedule_flag, a.finance_rou_balance_amount migrated_fin_uer_balance, a.operating_rou_balance_amount migrated_oper_uer_balance, a.regime_code, a.dv_amortization_start_date, a.dv_amortization_end_date, a.draft_schedules_exists_flag, a.draft_schedule_header_id, a.draft_parent_record_id, CASE WHEN a.closed_flag = 'Y' THEN 'N' WHEN a.schedules_impact_flag = 'Y' AND a.payment_items_impact_flag = 'Y' THEN 'Y' WHEN a.customer_info_updated_flag = 'Y' THEN 'Y' WHEN a.no_schedules_flag = 'Y' THEN 'Y' WHEN a.onetime_info_updated_flag = 'Y' THEN 'Y' WHEN a.migration_info_updated_flag = 'Y' THEN 'Y' WHEN a.final_schedules_exists_flag = 'N' AND a.dv_amortization_start_date <> a.dsh_amortization_start_date THEN 'Y' WHEN a.dv_amortization_end_date <> NVL(a.dsh_amortization_end_date, a.fsh_amortization_end_date) THEN 'Y' ELSE 'N' END regenerate_schedules_flag, CASE WHEN a.change_type_code = 'DELETE' THEN 'Y' WHEN a.schedules_impact_flag = 'Y' THEN 'Y' ELSE 'N' END delete_draft_schedules_flag, a.no_schedules_flag, a.migration_info_updated_flag, a.customer_info_updated_flag, a.onetime_info_updated_flag, a.dsh_amortization_start_date, a.dsh_amortization_end_date, a.fsh_amortization_start_date, a.fsh_amortization_end_date, a.interest_compounding_code, a.interest_compounding_days, a.end_of_day_payment_flag FROM ( SELECT pay.*, CASE WHEN pay.transaction_type_code = 'CANCELLED' THEN pay.commencement_date WHEN fsh.schedule_header_id IS NULL AND pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NULL THEN pay.commencement_date WHEN fsh.schedule_header_id IS NULL AND pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NOT NULL THEN GREATEST(pay.commencement_date, pay.asset_start_date) WHEN fsh.schedule_header_id IS NULL AND pay.revenue_method_code = 'NON_LEASE_REVENUE' THEN GREATEST(pay.commencement_date, pay.payment_start_date) WHEN fsh.schedule_header_id IS NOT NULL THEN GREATEST(pay.commencement_date, fsh.amortization_start_date) END dv_amortization_start_date, CASE WHEN pay.transaction_type_code = 'CANCELLED' THEN pay.commencement_date WHEN fsh.schedule_header_id IS NOT NULL AND pay.closed_flag = 'I' THEN pay.commencement_date WHEN pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NULL THEN pay.lease_amortization_end_date WHEN pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NOT NULL THEN pay.asset_amortization_end_date WHEN pay.revenue_method_code = 'NON_LEASE_REVENUE' AND pay.asset_number IS NULL THEN LEAST(pay.lease_amortization_end_date, pay.payment_end_date) WHEN pay.revenue_method_code = 'NON_LEASE_REVENUE' AND pay.asset_number IS NOT NULL THEN LEAST(pay.asset_amortization_end_date, pay.payment_end_date) END dv_amortization_end_date, CASE WHEN dsh.schedule_header_id IS NULL AND fsh.schedule_header_id IS NULL THEN 'N' WHEN pay.bill_to_customer_id <> NVL(dsh.bill_to_customer_id, fsh.bill_to_customer_id) THEN 'Y' WHEN pay.bill_to_site_use_id <> NVL(dsh.bill_to_site_use_id, fsh.bill_to_site_use_id) THEN 'Y' WHEN pay.ship_to_site_use_id <> NVL(dsh.ship_to_site_use_id, fsh.ship_to_site_use_id) THEN 'Y' ELSE 'N' END customer_info_updated_flag, CASE WHEN dsh.schedule_header_id IS NOT NULL AND pay.dv_amort_proration_code <> dsh.amortization_proration_code THEN 'Y' WHEN dsh.schedule_header_id IS NOT NULL AND pay.revenue_method_code <> dsh.revenue_method_code THEN 'Y' WHEN dsh.schedule_header_id IS NOT NULL AND pay.currency_code <> dsh.currency_code THEN 'Y' ELSE 'N' END onetime_info_updated_flag, CASE WHEN pay.final_lease_detail_id IS NOT NULL THEN 'N' WHEN pay.migration_schedule_flag = 'Y' AND NVL(pay.operating_rou_balance_amount, 0) <> NVL(dsh.prior_oper_rou_amount, 0) THEN 'Y' ELSE 'N' END migration_info_updated_flag, DECODE(NVL(dsh.schedule_header_id, fsh.schedule_header_id), NULL, 'Y', 'N') no_schedules_flag, NVL2(dsh.schedule_header_id, 'Y', 'N') draft_schedules_exists_flag, NVL2(fsh.schedule_header_id, 'Y', 'N') final_schedules_exists_flag, dsh.schedule_header_id draft_schedule_header_id, dsh.parent_record_id draft_parent_record_id, dsh.amortization_start_date dsh_amortization_start_date, dsh.amortization_end_date dsh_amortization_end_date, fsh.schedule_header_id final_schedule_header_id, fsh.amortization_start_date fsh_amortization_start_date, fsh.amortization_end_date fsh_amortization_end_date FROM ( SELECT ph.lease_id, ph.lease_detail_id, DECODE(ld.transaction_type_code, 'BOOKING', la.amortization_start_date, ld.commencement_date) commencement_date, ld.amortization_end_date lease_amortization_end_date, ld.version_type_code, ld.version_num, ld.approval_status_code, CASE WHEN ld.transaction_type_code = 'BOOKING' THEN 'BOOKING' WHEN NVL(ph.closed_flag, 'N') = 'I' AND ph.revenue_method_code = 'NON_LEASE_REVENUE' AND ld.commencement_date < ph.payment_start_date THEN 'CANCELLED' WHEN NVL(ph.closed_flag, 'N') = 'I' THEN 'TERMINATION' ELSE 'REVISION' END transaction_type_code, ld.transaction_subtype_code transaction_subtype_code, ld.schedules_refresh_needed_flag, ld.lease_classification_code dv_lease_classification_code, la.amortization_proration_code dv_amort_proration_code, NVL(ld.lease_termination_flag,'N') lease_termination_flag, ph.payment_number, ph.option_flag, ph.payment_start_date, ph.payment_end_date, ph.payment_status_code, ph.option_status_code, ph.asset_number, pas.start_date asset_start_date, pas.end_date asset_end_date, pas.amortization_end_date asset_amortization_end_date, pas.asset_status_code, ph.currency_code, ph.bill_to_customer_id, ph.bill_to_site_use_id, ph.ship_to_site_use_id, CASE WHEN pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') THEN 'Y' ELSE 'N' END asset_termination_flag, ph.change_type_code, ph.changed_in_version_num, ph.schedules_impact_flag, ph.payment_items_impact_flag, ph.revenue_method_code, NVL(ph.closed_flag, 'N') closed_flag, NVL(ph.contingent_only_impact_flag,'N') contingent_only_impact_flag, CASE WHEN ph.migrated_flag = 'Y' AND NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING' THEN 'Y' ELSE 'N' END migration_schedule_flag, ph.finance_rou_balance_amount, ph.operating_rou_balance_amount, DECODE(r.column_value, 1, so.primary_regime_code, so.secondary_regime_code) regime_code, DECODE(fd.lease_detail_id, 'N', 'Y') has_final, fd.lease_detail_id final_lease_detail_id, DECODE(ld.interest_method_code, 'DAILY', 'DAILY', 'MONTHLY') interest_compounding_code, CASE WHEN ld.interest_method_code = 'DAILY' THEN 1 WHEN ph.payment_frequency_code = 'MONTHLY' THEN 30 WHEN ph.payment_frequency_code = 'QUARTERLY' THEN 90 WHEN ph.payment_frequency_code = 'SEMI_ANNUALLY' THEN 180 WHEN ph.payment_frequency_code = 'ANNUALLY' THEN 360 ELSE 1 END interest_compounding_days, CASE WHEN ld.arrears_pay_dt_treatment_code = 'END_OF_DAY' AND ph.arrears_flag = 'Y' THEN 'Y' ELSE 'N' END end_of_day_payment_flag FROM fla_leases_all la, fla_system_options_all so, fla_lease_details ld, fla_lease_details fd, fla_lease_payment_headers ph, fla_lease_assets pas, TABLE(fla_lease_utils_pkg.generate_rows(NVL2(so.secondary_regime_code, 2, 1))) r WHERE la.org_id = so.org_id AND la.lease_id = ld.lease_id AND ld.version_type_code = 'D' AND ld.approval_status_code != 'FINALIZED' AND la.lease_id = fd.lease_id(+) AND fd.version_type_code(+) = 'F' AND ld.lease_detail_id = ph.lease_detail_id AND ld.lease_id = ph.lease_id AND ph.lease_detail_id = pas.lease_detail_id(+) AND ph.asset_number = pas.asset_number(+) AND ph.schedule_type_code = 'BILLING' AND ph.option_flag = 'N' ) pay, fla_schedule_headers dsh, fla_schedule_headers fsh WHERE pay.lease_detail_id = dsh.lease_detail_id(+) AND pay.payment_number = dsh.payment_number(+) AND pay.regime_code = dsh.regime_code(+) AND pay.final_lease_detail_id = fsh.lease_detail_id(+) AND pay.payment_number = fsh.payment_number(+) AND pay.regime_code = fsh.regime_code(+) AND fsh.active_flag(+) = 'Y' ) a

SQL_Statement

SELECT a.lease_id,

a.lease_detail_id,

a.version_type_code,

a.version_num,

a.approval_status_code,

a.transaction_type_code,

a.transaction_subtype_code,

a.schedules_refresh_needed_flag,

a.dv_lease_classification_code,

a.dv_amort_proration_code,

a.lease_termination_flag,

a.payment_number,

a.option_flag,

a.payment_start_date,

a.payment_end_date,

a.payment_status_code,

a.option_status_code,

a.asset_number,

a.asset_status_code,

a.currency_code,

a.bill_to_customer_id,

a.bill_to_site_use_id,

a.ship_to_site_use_id,

a.asset_termination_flag,

a.schedules_impact_flag,

a.payment_items_impact_flag,

a.revenue_method_code,

a.closed_flag,

a.change_type_code,

a.changed_in_version_num,

a.contingent_only_impact_flag,

a.migration_schedule_flag,

a.finance_rou_balance_amount migrated_fin_uer_balance,

a.operating_rou_balance_amount migrated_oper_uer_balance,

a.regime_code,

a.dv_amortization_start_date,

a.dv_amortization_end_date,

a.draft_schedules_exists_flag,

a.draft_schedule_header_id,

a.draft_parent_record_id,

CASE

WHEN a.closed_flag = 'Y' THEN 'N'

WHEN a.schedules_impact_flag = 'Y' AND a.payment_items_impact_flag = 'Y' THEN 'Y'

WHEN a.customer_info_updated_flag = 'Y' THEN 'Y'

WHEN a.no_schedules_flag = 'Y' THEN 'Y'

WHEN a.onetime_info_updated_flag = 'Y' THEN 'Y'

WHEN a.migration_info_updated_flag = 'Y' THEN 'Y'

WHEN a.final_schedules_exists_flag = 'N' AND a.dv_amortization_start_date <> a.dsh_amortization_start_date THEN 'Y'

WHEN a.dv_amortization_end_date <> NVL(a.dsh_amortization_end_date, a.fsh_amortization_end_date) THEN 'Y'

ELSE 'N'

END regenerate_schedules_flag,

CASE

WHEN a.change_type_code = 'DELETE' THEN 'Y'

WHEN a.schedules_impact_flag = 'Y' THEN 'Y'

ELSE 'N'

END delete_draft_schedules_flag,

a.no_schedules_flag,

a.migration_info_updated_flag,

a.customer_info_updated_flag,

a.onetime_info_updated_flag,

a.dsh_amortization_start_date,

a.dsh_amortization_end_date,

a.fsh_amortization_start_date,

a.fsh_amortization_end_date,

a.interest_compounding_code,

a.interest_compounding_days,

a.end_of_day_payment_flag

FROM (

SELECT pay.*,

CASE

WHEN pay.transaction_type_code = 'CANCELLED' THEN pay.commencement_date

WHEN fsh.schedule_header_id IS NULL AND pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NULL THEN pay.commencement_date

WHEN fsh.schedule_header_id IS NULL AND pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NOT NULL THEN GREATEST(pay.commencement_date, pay.asset_start_date)

WHEN fsh.schedule_header_id IS NULL AND pay.revenue_method_code = 'NON_LEASE_REVENUE' THEN GREATEST(pay.commencement_date, pay.payment_start_date)

WHEN fsh.schedule_header_id IS NOT NULL THEN GREATEST(pay.commencement_date, fsh.amortization_start_date)

END dv_amortization_start_date,

CASE

WHEN pay.transaction_type_code = 'CANCELLED' THEN pay.commencement_date

WHEN fsh.schedule_header_id IS NOT NULL AND pay.closed_flag = 'I' THEN pay.commencement_date

WHEN pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NULL THEN pay.lease_amortization_end_date

WHEN pay.revenue_method_code = 'LEASE_REVENUE' AND pay.asset_number IS NOT NULL THEN pay.asset_amortization_end_date

WHEN pay.revenue_method_code = 'NON_LEASE_REVENUE' AND pay.asset_number IS NULL THEN LEAST(pay.lease_amortization_end_date, pay.payment_end_date)

WHEN pay.revenue_method_code = 'NON_LEASE_REVENUE' AND pay.asset_number IS NOT NULL THEN LEAST(pay.asset_amortization_end_date, pay.payment_end_date)

END dv_amortization_end_date,

CASE

WHEN dsh.schedule_header_id IS NULL AND fsh.schedule_header_id IS NULL THEN 'N'

WHEN pay.bill_to_customer_id <> NVL(dsh.bill_to_customer_id, fsh.bill_to_customer_id) THEN 'Y'

WHEN pay.bill_to_site_use_id <> NVL(dsh.bill_to_site_use_id, fsh.bill_to_site_use_id) THEN 'Y'

WHEN pay.ship_to_site_use_id <> NVL(dsh.ship_to_site_use_id, fsh.ship_to_site_use_id) THEN 'Y'

ELSE 'N'

END customer_info_updated_flag,

CASE

WHEN dsh.schedule_header_id IS NOT NULL AND pay.dv_amort_proration_code <> dsh.amortization_proration_code THEN 'Y'

WHEN dsh.schedule_header_id IS NOT NULL AND pay.revenue_method_code <> dsh.revenue_method_code THEN 'Y'

WHEN dsh.schedule_header_id IS NOT NULL AND pay.currency_code <> dsh.currency_code THEN 'Y'

ELSE 'N'

END onetime_info_updated_flag,

CASE

WHEN pay.final_lease_detail_id IS NOT NULL THEN 'N'

WHEN pay.migration_schedule_flag = 'Y' AND NVL(pay.operating_rou_balance_amount, 0) <> NVL(dsh.prior_oper_rou_amount, 0) THEN 'Y'

ELSE 'N'

END migration_info_updated_flag,

DECODE(NVL(dsh.schedule_header_id, fsh.schedule_header_id), NULL, 'Y', 'N') no_schedules_flag,

NVL2(dsh.schedule_header_id, 'Y', 'N') draft_schedules_exists_flag,

NVL2(fsh.schedule_header_id, 'Y', 'N') final_schedules_exists_flag,

dsh.schedule_header_id draft_schedule_header_id,

dsh.parent_record_id draft_parent_record_id,

dsh.amortization_start_date dsh_amortization_start_date,

dsh.amortization_end_date dsh_amortization_end_date,

fsh.schedule_header_id final_schedule_header_id,

fsh.amortization_start_date fsh_amortization_start_date,

fsh.amortization_end_date fsh_amortization_end_date

FROM (

SELECT ph.lease_id,

ph.lease_detail_id,

DECODE(ld.transaction_type_code, 'BOOKING', la.amortization_start_date, ld.commencement_date) commencement_date,

ld.amortization_end_date lease_amortization_end_date,

ld.version_type_code,

ld.version_num,

ld.approval_status_code,

CASE

WHEN ld.transaction_type_code = 'BOOKING' THEN 'BOOKING'

WHEN NVL(ph.closed_flag, 'N') = 'I' AND ph.revenue_method_code = 'NON_LEASE_REVENUE' AND ld.commencement_date < ph.payment_start_date THEN 'CANCELLED'

WHEN NVL(ph.closed_flag, 'N') = 'I' THEN 'TERMINATION'

ELSE 'REVISION'

END transaction_type_code,

ld.transaction_subtype_code transaction_subtype_code,

ld.schedules_refresh_needed_flag,

ld.lease_classification_code dv_lease_classification_code,

la.amortization_proration_code dv_amort_proration_code,

NVL(ld.lease_termination_flag,'N') lease_termination_flag,

ph.payment_number,

ph.option_flag,

ph.payment_start_date,

ph.payment_end_date,

ph.payment_status_code,

ph.option_status_code,

ph.asset_number,

pas.start_date asset_start_date,

pas.end_date asset_end_date,

pas.amortization_end_date asset_amortization_end_date,

pas.asset_status_code,

ph.currency_code,

ph.bill_to_customer_id,

ph.bill_to_site_use_id,

ph.ship_to_site_use_id,

CASE WHEN pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') THEN 'Y' ELSE 'N' END asset_termination_flag,

ph.change_type_code,

ph.changed_in_version_num,

ph.schedules_impact_flag,

ph.payment_items_impact_flag,

ph.revenue_method_code,

NVL(ph.closed_flag, 'N') closed_flag,

NVL(ph.contingent_only_impact_flag,'N') contingent_only_impact_flag,

CASE WHEN ph.migrated_flag = 'Y' AND NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING' THEN 'Y' ELSE 'N' END migration_schedule_flag,

ph.finance_rou_balance_amount,

ph.operating_rou_balance_amount,

DECODE(r.column_value, 1, so.primary_regime_code, so.secondary_regime_code) regime_code,

DECODE(fd.lease_detail_id, 'N', 'Y') has_final,

fd.lease_detail_id final_lease_detail_id,

DECODE(ld.interest_method_code, 'DAILY', 'DAILY', 'MONTHLY') interest_compounding_code,

CASE

WHEN ld.interest_method_code = 'DAILY' THEN 1

WHEN ph.payment_frequency_code = 'MONTHLY' THEN 30

WHEN ph.payment_frequency_code = 'QUARTERLY' THEN 90

WHEN ph.payment_frequency_code = 'SEMI_ANNUALLY' THEN 180

WHEN ph.payment_frequency_code = 'ANNUALLY' THEN 360

ELSE 1

END interest_compounding_days,

CASE WHEN ld.arrears_pay_dt_treatment_code = 'END_OF_DAY' AND ph.arrears_flag = 'Y' THEN 'Y' ELSE 'N' END end_of_day_payment_flag

FROM fla_leases_all la,

fla_system_options_all so,

fla_lease_details ld,

fla_lease_details fd,

fla_lease_payment_headers ph,

fla_lease_assets pas,

TABLE(fla_lease_utils_pkg.generate_rows(NVL2(so.secondary_regime_code, 2, 1))) r

WHERE la.org_id = so.org_id

AND la.lease_id = ld.lease_id

AND ld.version_type_code = 'D'

AND ld.approval_status_code != 'FINALIZED'

AND la.lease_id = fd.lease_id(+)

AND fd.version_type_code(+) = 'F'

AND ld.lease_detail_id = ph.lease_detail_id

AND ld.lease_id = ph.lease_id

AND ph.lease_detail_id = pas.lease_detail_id(+)

AND ph.asset_number = pas.asset_number(+)

AND ph.schedule_type_code = 'BILLING'

AND ph.option_flag = 'N'

) pay,

fla_schedule_headers dsh,

fla_schedule_headers fsh

WHERE pay.lease_detail_id = dsh.lease_detail_id(+)

AND pay.payment_number = dsh.payment_number(+)

AND pay.regime_code = dsh.regime_code(+)

AND pay.final_lease_detail_id = fsh.lease_detail_id(+)

AND pay.payment_number = fsh.payment_number(+)

AND pay.regime_code = fsh.regime_code(+)

AND fsh.active_flag(+) = 'Y'

) a