FLA_ACCOUNTING_BALANCES_V

Details

• Schema: FUSION

• Object owner: FUN

• Object type: VIEW

Columns

Name

TRANSACTION_ID LEASE_ID LEASE_DETAIL_ID ORG_ID VERSION_NUM TRANSACTION_TYPE_CODE TRANSACTION_NUMBER TRANSACTION_DATE ACCOUNTING_DATE PAYMENT_NUMBER REGIME_CODE LEDGER_ID CURRENCY_CODE PERIOD_NAME EVENT_ID HEADER_REVERSED_FLAG PARENT_TRANSACTION_ID ACCOUNTED_IN_GL_FLAG BALANCE_SYNC_REQUEST_ID TRANSACTION_LINE_ID LINE_NUM DESCRIPTION SCHEDULE_TYPE_CODE DR_ACCOUNT_CLASS_CODE CR_ACCOUNT_CLASS_CODE ENTERED_AMOUNT ACCOUNTED_AMOUNT DR_CODE_COMBINATION_ID CR_CODE_COMBINATION_ID CONVERSION_RATE CONVERSION_DATE CONVERSION_RATE_TYPE_CODE SCHEDULE_HEADER_ID ACCRUAL_SCHEDULE_ID PAYMENT_ITEM_ID APPLIED_TO_LINE_ID LINE_REVERSED_FLAG PARENT_TRANSACTION_LINE_ID PREVIOUS_CONVERSION_RATE PREVIOUS_CONVERSION_DATE PREVIOUS_CONV_RATE_TYPE_CODE BOOKED_LIABILITY_AMOUNT ADJUSTED_LIABILITY_AMOUNT REDUCED_LIABILITY_AMOUNT TERMINATED_LIABILITY_AMOUNT ACCRUED_LIABILITY_AMOUNT CONTINGENT_AMOUNT PAYMENT_AMOUNT BOOKED_ROU_AMOUNT ADJUSTED_ROU_AMOUNT REDUCED_ROU_AMOUNT TERMINATED_ROU_AMOUNT ACCRUED_ROU_AMOUNT EXPIRED_ROU_AMOUNT FIN_DR_ROU FIN_CR_ROU OPER_DR_ROU OPER_CR_ROU DR_LIABILITY CR_LIABILITY ACCUM_AMORT DR_RESERVE CR_RESERVE AP_CLEARING AC_BOOKED_LIABILITY_AMOUNT AC_ADJUSTED_LIABILITY_AMOUNT AC_REDUCED_LIABILITY_AMOUNT AC_TERMINATED_LIABILITY_AMOUNT AC_ACCRUED_LIABILITY_AMOUNT AC_CONTINGENT_AMOUNT AC_PAYMENT_AMOUNT AC_BOOKED_ROU_AMOUNT AC_ADJUSTED_ROU_AMOUNT AC_REDUCED_ROU_AMOUNT AC_TERMINATED_ROU_AMOUNT AC_ACCRUED_ROU_AMOUNT AC_EXPIRED_ROU_AMOUNT AC_FIN_DR_ROU AC_FIN_CR_ROU AC_OPER_DR_ROU AC_OPER_CR_ROU AC_DR_LIABILITY AC_CR_LIABILITY AC_ACCUM_AMORT AC_DR_RESERVE AC_CR_RESERVE AC_AP_CLEARING

Query

SQL_Statement

SELECT fah.transaction_id, fah.lease_id, fah.lease_detail_id, fah.org_id, fah.version_num, fah.transaction_type_code, fah.transaction_number, fah.transaction_date, fah.accounting_date, fal.payment_number, fah.regime_code, fah.ledger_id, fal.currency_code, fah.period_name, fah.event_id, fah.reversed_flag header_reversed_flag, fah.parent_transaction_id, fah.accounted_in_gl_flag, fah.balance_sync_request_id, fal.transaction_line_id, fal.line_num, fal.description, fal.schedule_type_code, fal.dr_account_class_code, fal.cr_account_class_code, fal.entered_amount, fal.accounted_amount, fal.dr_code_combination_id, fal.cr_code_combination_id, fal.conversion_rate, fal.conversion_date, fal.conversion_rate_type_code, fal.schedule_header_id, fal.accrual_schedule_id, fal.payment_item_id, fal.applied_to_line_id, fal.reversed_flag line_reversed_flag, fal.parent_transaction_line_id, fal.previous_conversion_rate, fal.previous_conversion_date, fal.previous_conv_rate_type_code, CASE WHEN fah.transaction_type_code IN ( 'BOOKING', 'REVISION' ) AND sh.parent_record_id IS NULL AND fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) ELSE 0 END booked_liability_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND sh.parent_record_id IS NOT NULL AND fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) ELSE 0 END adjusted_liability_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND fal.schedule_type_code = 'LIABILITY_REDUCTION' AND fal.dr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) ELSE 0 END reduced_liability_amount, CASE WHEN fah.transaction_type_code = 'TERMINATION' AND fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) * - 1 ELSE 0 END terminated_liability_amount, CASE WHEN fal.schedule_type_code = 'INTEREST_EXPENSE' THEN nvl(fal.entered_amount, 0) ELSE 0 END accrued_liability_amount, CASE WHEN fah.transaction_type_code = 'PAYMENT_APPROVAL' AND fal.dr_account_class_code = 'CONTINGENT_EXPENSE' THEN nvl(fal.entered_amount, 0) ELSE 0 END contingent_amount, CASE WHEN fah.transaction_type_code = 'PAYMENT_APPROVAL' AND fal.dr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) ELSE 0 END payment_amount, CASE WHEN fah.transaction_type_code IN ( 'BOOKING', 'REVISION' ) AND sh.parent_record_id IS NULL AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) ELSE 0 END booked_rou_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND sh.parent_record_id IS NOT NULL AND fal.schedule_type_code IN ('LEASE_LIABILITY', 'RESERVE') AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) ELSE 0 END adjusted_rou_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND fal.schedule_type_code = 'LIABILITY_REDUCTION' AND fal.cr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) WHEN fah.transaction_type_code = 'REVISION' AND fal.schedule_type_code = 'GAIN_LOSS' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) * - 1 WHEN fah.transaction_type_code = 'IMPAIRMENT' AND fal.schedule_type_code = 'IMPAIRMENT_LOSS' THEN nvl(fal.entered_amount, 0) ELSE 0 END reduced_rou_amount, CASE WHEN fah.transaction_type_code = 'TERMINATION' AND fal.schedule_type_code NOT IN ( 'ACCUMULATED_AMORT', 'RIGHT_OF_USE' ) AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) * - 1 WHEN fah.transaction_type_code = 'TERMINATION' AND fal.schedule_type_code = 'GAIN_LOSS' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) * - 1 ELSE 0 END terminated_rou_amount, CASE WHEN fal.schedule_type_code = 'AMORTIZATION' THEN nvl(fal.entered_amount, 0) ELSE 0 END accrued_rou_amount, CASE WHEN fah.transaction_type_code = 'TERMINATION' AND fal.schedule_type_code = 'ACCUMULATED_AMORT' THEN nvl(fal.entered_amount, 0) * - 1 WHEN fah.transaction_type_code = 'IMPAIRMENT' AND fal.schedule_type_code = 'IMPAIRMENT_ACCUM_AMORT' THEN nvl(fal.entered_amount, 0) * -1 ELSE 0 END expired_rou_amount, CASE WHEN sh.lease_classification_code = 'FINANCE' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) ELSE 0 END fin_dr_rou, CASE WHEN sh.lease_classification_code = 'FINANCE' AND fal.cr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) ELSE 0 END fin_cr_rou, CASE WHEN sh.lease_classification_code = 'OPERATING' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) ELSE 0 END oper_dr_rou, CASE WHEN sh.lease_classification_code = 'OPERATING' AND fal.cr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.entered_amount, 0) ELSE 0 END oper_cr_rou, CASE WHEN fal.dr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) ELSE 0 END dr_liability, CASE WHEN fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.entered_amount, 0) ELSE 0 END cr_liability, CASE WHEN fal.cr_account_class_code = 'ACCUMULATED_AMORTIZATION' THEN nvl(fal.entered_amount, 0) ELSE 0 END accum_amort, CASE WHEN fal.dr_account_class_code = 'RESERVE' THEN nvl(fal.entered_amount, 0) ELSE 0 END dr_reserve, CASE WHEN fal.cr_account_class_code = 'RESERVE' THEN nvl(fal.entered_amount, 0) ELSE 0 END cr_reserve, CASE WHEN fal.cr_account_class_code = 'AP_CLEARING' THEN nvl(fal.entered_amount, 0) ELSE 0 END ap_clearing, CASE WHEN fah.transaction_type_code IN ( 'BOOKING', 'REVISION' ) AND sh.parent_record_id IS NULL AND fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_booked_liability_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND sh.parent_record_id IS NOT NULL AND fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_adjusted_liability_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND fal.schedule_type_code = 'LIABILITY_REDUCTION' AND fal.dr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_reduced_liability_amount, CASE WHEN fah.transaction_type_code = 'TERMINATION' AND fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) * - 1 ELSE 0 END ac_terminated_liability_amount, CASE WHEN fal.schedule_type_code = 'INTEREST_EXPENSE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_accrued_liability_amount, CASE WHEN fah.transaction_type_code = 'PAYMENT_APPROVAL' AND fal.dr_account_class_code = 'CONTINGENT_EXPENSE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_contingent_amount, CASE WHEN fah.transaction_type_code = 'PAYMENT_APPROVAL' AND fal.dr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_payment_amount, CASE WHEN fah.transaction_type_code IN ( 'BOOKING', 'REVISION' ) AND sh.parent_record_id IS NULL AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_booked_rou_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND sh.parent_record_id IS NOT NULL AND fal.schedule_type_code <> 'RIGHT_OF_USE' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_adjusted_rou_amount, CASE WHEN fah.transaction_type_code = 'REVISION' AND fal.schedule_type_code = 'LIABILITY_REDUCTION' AND fal.cr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) WHEN fah.transaction_type_code = 'REVISION' AND fal.schedule_type_code = 'GAIN_LOSS' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) * - 1 WHEN fah.transaction_type_code = 'IMPAIRMENT' AND fal.schedule_type_code = 'IMPAIRMENT_LOSS' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_reduced_rou_amount, CASE WHEN fah.transaction_type_code = 'TERMINATION' AND fal.schedule_type_code NOT IN ( 'ACCUMULATED_AMORT', 'RIGHT_OF_USE' ) AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) * - 1 WHEN fah.transaction_type_code = 'TERMINATION' AND fal.schedule_type_code = 'GAIN_LOSS' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) * - 1 ELSE 0 END ac_terminated_rou_amount, CASE WHEN fal.schedule_type_code = 'AMORTIZATION' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_accrued_rou_amount, CASE WHEN fah.transaction_type_code = 'TERMINATION' AND fal.schedule_type_code = 'ACCUMULATED_AMORT' THEN nvl(fal.accounted_amount, 0) * - 1 WHEN fah.transaction_type_code = 'IMPAIRMENT' AND fal.schedule_type_code = 'IMPAIRMENT_ACCUM_AMORT' THEN nvl(fal.accounted_amount, 0) * -1 ELSE 0 END ac_expired_rou_amount, CASE WHEN sh.lease_classification_code = 'FINANCE' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_fin_dr_rou, CASE WHEN sh.lease_classification_code = 'FINANCE' AND fal.cr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_fin_cr_rou, CASE WHEN sh.lease_classification_code = 'OPERATING' AND fal.dr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_oper_dr_rou, CASE WHEN sh.lease_classification_code = 'OPERATING' AND fal.cr_account_class_code = 'RIGHT_OF_USE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_oper_cr_rou, CASE WHEN fal.dr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_dr_liability, CASE WHEN fal.cr_account_class_code = 'LEASE_LIABILITY' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_cr_liability, CASE WHEN fal.cr_account_class_code = 'ACCUMULATED_AMORTIZATION' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_accum_amort, CASE WHEN fal.dr_account_class_code = 'RESERVE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_dr_reserve, CASE WHEN fal.cr_account_class_code = 'RESERVE' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_cr_reserve, CASE WHEN fal.cr_account_class_code = 'AP_CLEARING' THEN nvl(fal.accounted_amount, 0) ELSE 0 END ac_ap_clearing FROM fla_accounting_headers_all fah, fla_accounting_lines fal, fla_schedule_headers sh WHERE fah.transaction_id = fal.transaction_id AND fal.schedule_header_id = sh.schedule_header_id (+) UNION ALL SELECT fah.transaction_id, fah.lease_id, fah.lease_detail_id, fah.org_id, fah.version_num, fah.transaction_type_code, fah.transaction_number, fah.transaction_date, fah.accounting_date, fal.payment_number, fah.regime_code, fah.ledger_id, fal.currency_code, fah.period_name, fah.event_id, fah.reversed_flag header_reversed_flag, fah.parent_transaction_id, fah.accounted_in_gl_flag, fah.balance_sync_request_id, fal.transaction_line_id, fal.line_num, fal.description, fal.schedule_type_code, fal.dr_account_class_code, fal.cr_account_class_code, fal.entered_amount*-1, fal.accounted_amount*-1, fal.dr_code_combination_id, fal.cr_code_combination_id, fal.conversion_rate, fal.conversion_date, fal.conversion_rate_type_code, fal.schedule_header_id, fal.accrual_schedule_id, fal.payment_item_id, fal.applied_to_line_id, fal.reversed_flag line_reversed_flag, fal.parent_transaction_line_id, fal.previous_conversion_rate, fal.previous_conversion_date, fal.previous_conv_rate_type_code, 0 booked_liability_amount, 0 adjusted_liability_amount, 0 reduced_liability_amount, 0 terminated_liability_amount, 0 accrued_liability_amount, CASE WHEN fal.dr_account_class_code='CONTINGENT_EXPENSE' THEN NVL(fal.entered_amount,0)*-1 ELSE 0 END contingent_amount, CASE WHEN fal.dr_account_class_code='LEASE_LIABILITY' THEN NVL(fal.entered_amount,0)*-1 ELSE 0 END payment_amount, 0 booked_rou_amount, 0 adjusted_rou_amount, 0 reduced_rou_amount, 0 terminated_rou_amount, 0 accrued_rou_amount, 0 expired_rou_amount, 0 fin_dr_rou, 0 fin_cr_rou, 0 oper_dr_rou, 0 oper_cr_rou, CASE WHEN fal.dr_account_class_code='LEASE_LIABILITY' THEN NVL(fal.entered_amount,0)*-1 ELSE 0 END dr_liability, CASE WHEN fal.cr_account_class_code='LEASE_LIABILITY' THEN NVL(fal.entered_amount,0)*-1 ELSE 0 END cr_liability, 0 accum_amort, 0 dr_reserve, 0 cr_reserve, CASE WHEN fal.cr_account_class_code='AP_CLEARING' THEN NVL(fal.entered_amount,0)*-1 ELSE 0 END ap_clearing, 0 ac_booked_liability_amount, 0 ac_adjusted_liability_amount, 0 ac_reduced_liability_amount, 0 ac_terminated_liability_amount, 0 ac_accrued_liability_amount, CASE WHEN fal.dr_account_class_code='CONTINGENT_EXPENSE' THEN NVL(fal.accounted_amount,0)*-1 ELSE 0 END ac_contingent_amount, CASE WHEN fal.dr_account_class_code='LEASE_LIABILITY' THEN NVL(fal.accounted_amount,0)*-1 ELSE 0 END ac_payment_amount, 0 ac_booked_rou_amount, 0 ac_adjusted_rou_amount, 0 ac_reduced_rou_amount, 0 ac_terminated_rou_amount, 0 ac_accrued_rou_amount, 0 ac_expired_rou_amount, 0 ac_fin_dr_rou, 0 ac_fin_cr_rou, 0 ac_oper_dr_rou, 0 ac_oper_cr_rou, CASE WHEN fal.dr_account_class_code='LEASE_LIABILITY' THEN NVL(fal.accounted_amount,0)*-1 ELSE 0 END ac_dr_liability, CASE WHEN fal.cr_account_class_code='LEASE_LIABILITY' THEN NVL(fal.accounted_amount,0)*-1 ELSE 0 END ac_cr_liability, 0 ac_accum_amort, 0 ac_dr_reserve, 0 ac_cr_reserve, CASE WHEN fal.cr_account_class_code='AP_CLEARING' THEN NVL(fal.accounted_amount,0)*-1 ELSE 0 END ac_ap_clearing FROM fla_accounting_headers_all fah, fla_accounting_lines fal WHERE fah.parent_transaction_id = fal.transaction_id AND fah.transaction_type_code = 'PAYMENT_APPROVAL' AND fah.parent_transaction_id IS NOT NULL