FLA_EQP_ASSET_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_AMORT_PRORATION_CODE LEASE_TERMINATION_FLAG ASSET_NUMBER ASSET_STATUS_CODE ASSET_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 PAYMENT_CATEGORY_CODE CLOSED_FLAG CHANGE_TYPE_CODE MIGRATION_SCHEDULE_FLAG ACCRUED_ASSET_AMOUNT ASSET_CLASSIFICATION 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 SCHEDULE_LEVEL_CODE

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_AMORT_PRORATION_CODE

LEASE_TERMINATION_FLAG

ASSET_NUMBER

ASSET_STATUS_CODE

ASSET_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

PAYMENT_CATEGORY_CODE

CLOSED_FLAG

CHANGE_TYPE_CODE

MIGRATION_SCHEDULE_FLAG

ACCRUED_ASSET_AMOUNT

ASSET_CLASSIFICATION

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

SCHEDULE_LEVEL_CODE

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_amort_proration_code, a.lease_termination_flag, a.asset_number, a.asset_status_code, a.asset_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.payment_category_code, a.closed_flag, a.change_type_code, a.migration_schedule_flag, a.accrued_asset_amount, a.asset_classification, 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, a.schedule_level_code FROM ( SELECT pay.*, CASE WHEN fsh.schedule_header_id IS NULL THEN GREATEST(pay.commencement_date, pay.asset_start_date) WHEN fsh.schedule_header_id IS NOT NULL THEN GREATEST(pay.commencement_date, fsh.amortization_start_date) END dv_amortization_start_date, pay.asset_amortization_end_date 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.asset_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 pay.asset_classification = 'FINANCE' AND NVL(pay.accrued_asset_amount, 0) <> NVL(dsh.prior_fin_rou_amount, 0) THEN 'Y' WHEN pay.migration_schedule_flag = 'Y' AND pay.asset_classification = 'OPERATING' AND NVL(pay.accrued_asset_amount, 0) <> NVL(dsh.prior_oper_rou_amount, 0) THEN 'Y' WHEN pay.migration_schedule_flag = 'Y' AND pay.asset_classification <> NVL(dsh.lease_classification_code, pay.asset_classification) 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 ld.lease_id, ld.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 pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') 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, pas.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, pas.asset_currency_code, -1 bill_to_customer_id, -1 bill_to_site_use_id, -1 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, pas.change_type_code, pas.schedules_impact_flag, 'Y' payment_items_impact_flag, 'LEASE_REVENUE' revenue_method_code, 'RENT' payment_category_code, CASE WHEN pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') THEN 'I' WHEN pas.asset_status_code IN ('EXP', 'ACQ', 'TER') THEN 'Y' ELSE 'N' END closed_flag, CASE WHEN pas.migrated_flag = 'Y' AND NVL(ld.transaction_type_code, 'BOOKING') = 'BOOKING' THEN 'Y' ELSE 'N' END migration_schedule_flag, DECODE(rgd.primary_flag, 'Y', pas.primary_accrued_asset_amount, pas.secondary_accrued_asset_amount) accrued_asset_amount, DECODE(rgd.primary_flag, 'Y', pas.primary_classification_code, pas.secondary_classification_code) asset_classification, rgd.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 ELSE 1 END interest_compounding_days, CASE WHEN ld.arrears_pay_dt_treatment_code = 'END_OF_DAY' THEN 'Y' ELSE 'N' END end_of_day_payment_flag, 'ASSET' schedule_level_code FROM fla_leases_all la, fla_lease_details ld, fla_lease_details fd, fla_lease_assets pas, fla_regime_details_v rgd WHERE la.org_id = rgd.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 = pas.lease_detail_id ) pay, fla_schedule_headers dsh, fla_schedule_headers fsh WHERE pay.lease_detail_id = dsh.lease_detail_id(+) AND pay.asset_number = dsh.asset_number(+) AND pay.regime_code = dsh.regime_code(+) AND dsh.schedule_level_code(+) = 'ASSET' AND pay.final_lease_detail_id = fsh.lease_detail_id(+) AND pay.asset_number = fsh.asset_number(+) AND pay.regime_code = fsh.regime_code(+) AND pay.payment_category_code = 'RENT' AND fsh.active_flag(+) = 'Y' AND fsh.schedule_level_code(+) = 'ASSET' ) 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_amort_proration_code,

a.lease_termination_flag,

a.asset_number,

a.asset_status_code,

a.asset_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.payment_category_code,

a.closed_flag,

a.change_type_code,

a.migration_schedule_flag,

a.accrued_asset_amount,

a.asset_classification,

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,

a.schedule_level_code

FROM (

SELECT pay.*,

CASE

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

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

END dv_amortization_start_date,

pay.asset_amortization_end_date 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.asset_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 pay.asset_classification = 'FINANCE' AND

NVL(pay.accrued_asset_amount, 0) <> NVL(dsh.prior_fin_rou_amount, 0) THEN 'Y'

WHEN pay.migration_schedule_flag = 'Y' AND pay.asset_classification = 'OPERATING' AND

NVL(pay.accrued_asset_amount, 0) <> NVL(dsh.prior_oper_rou_amount, 0) THEN 'Y'

WHEN pay.migration_schedule_flag = 'Y' AND

pay.asset_classification <> NVL(dsh.lease_classification_code, pay.asset_classification) 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 ld.lease_id,

ld.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 pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') 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,

pas.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,

pas.asset_currency_code,

-1 bill_to_customer_id,

-1 bill_to_site_use_id,

-1 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,

pas.change_type_code,

pas.schedules_impact_flag,

'Y' payment_items_impact_flag,

'LEASE_REVENUE' revenue_method_code,

'RENT' payment_category_code,

CASE WHEN pas.asset_status_code IN ('TER_IN_PROGRESS', 'EXP_IN_PROGRESS', 'ACQ_IN_PROGRESS') THEN 'I'

WHEN pas.asset_status_code IN ('EXP', 'ACQ', 'TER') THEN 'Y'

ELSE 'N'

END closed_flag,

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

DECODE(rgd.primary_flag, 'Y', pas.primary_accrued_asset_amount, pas.secondary_accrued_asset_amount) accrued_asset_amount,

DECODE(rgd.primary_flag, 'Y', pas.primary_classification_code, pas.secondary_classification_code) asset_classification,

rgd.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

ELSE 1

END interest_compounding_days,

CASE WHEN ld.arrears_pay_dt_treatment_code = 'END_OF_DAY' THEN 'Y' ELSE 'N' END end_of_day_payment_flag,

'ASSET' schedule_level_code

FROM fla_leases_all la,

fla_lease_details ld,

fla_lease_details fd,

fla_lease_assets pas,

fla_regime_details_v rgd

WHERE la.org_id = rgd.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 = pas.lease_detail_id

) pay,

fla_schedule_headers dsh,

fla_schedule_headers fsh

WHERE pay.lease_detail_id = dsh.lease_detail_id(+)

AND pay.asset_number = dsh.asset_number(+)

AND pay.regime_code = dsh.regime_code(+)

AND dsh.schedule_level_code(+) = 'ASSET'

AND pay.final_lease_detail_id = fsh.lease_detail_id(+)

AND pay.asset_number = fsh.asset_number(+)

AND pay.regime_code = fsh.regime_code(+)

AND pay.payment_category_code = 'RENT'

AND fsh.active_flag(+) = 'Y'

AND fsh.schedule_level_code(+) = 'ASSET'

) a