FLA_XLA_ACCOUNTING_LINES

Details

Schema: FUSION
Object owner: FUN
Object type: VIEW

Columns

Name
LINES_LEASE_DETAIL_ID ACCOUNTED_AMOUNT DEBIT_ACCOUNT_CLASS CREDIT_ACCOUNT_CLASS ROU_ACCOUNT LIABILITY_ACCOUNT RESERVE_ACCOUNT INT_EXP_ACCOUNT AMORT_ACCOUNT LEASE_EXP_ACCOUNT GAIN_LOSS_ACCOUNT ACCUM_AMORT_ACCOUNT PAY_CLEAR_ACCOUNT ROU_ADJ_LOSS_ACCOUNT FOREX_GAIN_LOSS_ACCOUNT CURRENCY_CODE CONVERSION_DATE CONVERSION_RATE CONVERSION_RATE_TYPE_CODE SCHEDULE_TYPE_CODE ENTERED_AMOUNT PAYMENT_NUMBER LINE_NUMBER LINES_PAYMENT_ITEM_ID TRANSACTION_LINE_ID APPLIED_TO_LINE_ID PARENT_TRANSACTION_LINE_ID REVERSAL_LINE_INDICATOR PREVIOUS_CONVERSION_DATE PREVIOUS_CONVERSION_RATE PREVIOUS_CONV_RATE_TYPE_CODE EVENT_ID SHORT_TERM_LIAB_ACCOUNT LONG_TERM_LIAB_ACCOUNT RECEIVABLES_ACCOUNT REVENUE_ACCOUNT RECEIVABLES_CLEARING_ACCOUNT ACCRUED_ASSET_ACCOUNT REVENUE_DEBIT_ACCOUNT_CLASS REVENUE_CREDIT_ACCOUNT_CLASS NON_COMP_EXP_ACCOUNT UPFRONT_TAX_RESERVE_ACCOUNT UPFRONT_RECOVER_TAX_ACCOUNT TERM_ACCUM_AMORT_LINE_NUM

Name

LINES_LEASE_DETAIL_ID

ACCOUNTED_AMOUNT

DEBIT_ACCOUNT_CLASS

CREDIT_ACCOUNT_CLASS

ROU_ACCOUNT

LIABILITY_ACCOUNT

RESERVE_ACCOUNT

INT_EXP_ACCOUNT

AMORT_ACCOUNT

LEASE_EXP_ACCOUNT

GAIN_LOSS_ACCOUNT

ACCUM_AMORT_ACCOUNT

PAY_CLEAR_ACCOUNT

ROU_ADJ_LOSS_ACCOUNT

FOREX_GAIN_LOSS_ACCOUNT

CURRENCY_CODE

CONVERSION_DATE

CONVERSION_RATE

CONVERSION_RATE_TYPE_CODE

SCHEDULE_TYPE_CODE

ENTERED_AMOUNT

PAYMENT_NUMBER

LINE_NUMBER

LINES_PAYMENT_ITEM_ID

TRANSACTION_LINE_ID

APPLIED_TO_LINE_ID

PARENT_TRANSACTION_LINE_ID

REVERSAL_LINE_INDICATOR

PREVIOUS_CONVERSION_DATE

PREVIOUS_CONVERSION_RATE

PREVIOUS_CONV_RATE_TYPE_CODE

EVENT_ID

SHORT_TERM_LIAB_ACCOUNT

LONG_TERM_LIAB_ACCOUNT

RECEIVABLES_ACCOUNT

REVENUE_ACCOUNT

RECEIVABLES_CLEARING_ACCOUNT

ACCRUED_ASSET_ACCOUNT

REVENUE_DEBIT_ACCOUNT_CLASS

REVENUE_CREDIT_ACCOUNT_CLASS

NON_COMP_EXP_ACCOUNT

UPFRONT_TAX_RESERVE_ACCOUNT

UPFRONT_RECOVER_TAX_ACCOUNT

TERM_ACCUM_AMORT_LINE_NUM

Query

SQL_Statement
SELECT flath.lease_detail_id lines_lease_detail_id, flatl.accounted_amount accounted_amount, DECODE(fla.lease_type_code, 'EXPENSE', flatl.dr_account_class_code, NULL) debit_account_class, DECODE(fla.lease_type_code, 'EXPENSE', flatl.cr_account_class_code, NULL) credit_account_class, fpav.right_of_use rou_account, fpav.lease_liability liability_account, fpav.reserve reserve_account, fpav.interest_expense int_exp_account, fpav.amortization amort_account, fpav.lease_expense lease_exp_account, fpav.gain_loss gain_loss_account, fpav.accumulated_amortization accum_amort_account, fpav.ap_clearing pay_clear_account, fpav.rou_adj_loss rou_adj_loss_account, fpav.forex_gain_loss forex_gain_loss_account, flatl.currency_code currency_code, flatl.conversion_date conversion_date, flatl.conversion_rate conversion_rate, flatl.conversion_rate_type_code conversion_rate_type_code, flatl.schedule_type_code schedule_type_code, flatl.entered_amount entered_amount, flatl.payment_number payment_number, flatl.line_num line_number, flatl.payment_item_id lines_payment_item_id, flatl.transaction_line_id transaction_line_id, flatl.applied_to_line_id applied_to_line_id, flatl.parent_transaction_line_id parent_transaction_line_id, decode(flatl.parent_transaction_line_id,NULL,'N','Y') reversal_line_indicator, previous_conversion_date, nvl( flatl.previous_conversion_rate,flatl.conversion_rate ) previous_conversion_rate, nvl( flatl.previous_conv_rate_type_code,flatl.conversion_rate_type_code ) previous_conv_rate_type_code, flath.event_id event_id, fpav.stl_reclass short_term_liab_account, fpav.ltl_reclass long_term_liab_account, fpav.accounts_receivable receivables_account, fpav.revenue revenue_account, fpav.receivables_clearing receivables_clearing_account, fpav.accrued_asset accrued_asset_account, DECODE(fla.lease_type_code, 'REVENUE', flatl.dr_account_class_code, NULL) revenue_debit_account_class, DECODE(fla.lease_type_code, 'REVENUE', flatl.cr_account_class_code, NULL) revenue_credit_account_class, fpav.non_compliance_expense non_comp_exp_account, fpav.upfront_tax_reserve upfront_tax_reserve_account, fpav.upfront_recoverable_tax upfront_recover_tax_account, (CASE WHEN flath.transaction_type_code = 'TERMINATION' AND flatl.schedule_type_code = 'ACCUMULATED_AMORT' THEN (SELECT min(line_num) FROM fla_accounting_lines l WHERE l.transaction_id = flatl.transaction_id AND l.schedule_type_code = flatl.schedule_type_code AND l.payment_number = flatl.payment_number) ELSE to_number(NULL) END) term_accum_amort_line_num FROM fla_accounting_headers_all flath, (SELECT fal.transaction_id, fal.accounted_amount, fal.dr_account_class_code, fal.cr_account_class_code, fal.currency_code, fal.conversion_date, fal.conversion_rate, fal.conversion_rate_type_code, fal.schedule_type_code, fal.entered_amount, fal.payment_number, fal.line_num, fal.payment_item_id, fal.transaction_line_id, fal.applied_to_line_id, fal.parent_transaction_line_id, nvl(fal.previous_conversion_date, decode(fsh.parent_record_id,NULL,fsh.amortization_start_date,( SELECT fshp.amortization_start_date FROM fla_schedule_headers fshp WHERE fshp.schedule_header_id = fsh.parent_record_id ))) previous_conversion_date, fal.previous_conversion_rate, fal.previous_conv_rate_type_code FROM fla_accounting_lines fal, fla_schedule_headers fsh WHERE fsh.schedule_header_id = fal.schedule_header_id UNION ALL SELECT fal.transaction_id, fal.accounted_amount, fal.dr_account_class_code, fal.cr_account_class_code, fal.currency_code, fal.conversion_date, fal.conversion_rate, fal.conversion_rate_type_code, fal.schedule_type_code, fal.entered_amount, fal.payment_number, fal.line_num, fal.payment_item_id, fal.transaction_line_id, fal.applied_to_line_id, fal.parent_transaction_line_id, nvl(fal.previous_conversion_date, decode(ftt.parent_record_id,NULL,ftt.commencement_date,( SELECT fttp.commencement_date FROM fla_tax_transactions fttp WHERE fttp.tax_transaction_id = ftt.parent_record_id ))) previous_conversion_date, fal.previous_conversion_rate, fal.previous_conv_rate_type_code FROM fla_accounting_lines fal, fla_tax_transactions ftt WHERE ftt.tax_transaction_id = fal.source_reference_id1) flatl, fla_xla_payment_accounts_v fpav, fla_leases_all fla WHERE flatl.transaction_id = flath.transaction_id AND flath.lease_id = fpav.lease_id AND flath.lease_detail_id = fpav.lease_detail_id AND flath.ledger_id = fpav.ledger_id AND flatl.payment_number = fpav.payment_number AND flath.lease_id = fla.lease_id

SQL_Statement

SELECT

flath.lease_detail_id lines_lease_detail_id,

flatl.accounted_amount accounted_amount,

DECODE(fla.lease_type_code, 'EXPENSE', flatl.dr_account_class_code, NULL) debit_account_class,

DECODE(fla.lease_type_code, 'EXPENSE', flatl.cr_account_class_code, NULL) credit_account_class,

fpav.right_of_use rou_account,

fpav.lease_liability liability_account,

fpav.reserve reserve_account,

fpav.interest_expense int_exp_account,

fpav.amortization amort_account,

fpav.lease_expense lease_exp_account,

fpav.gain_loss gain_loss_account,

fpav.accumulated_amortization accum_amort_account,

fpav.ap_clearing pay_clear_account,

fpav.rou_adj_loss rou_adj_loss_account,

fpav.forex_gain_loss forex_gain_loss_account,

flatl.currency_code currency_code,

flatl.conversion_date conversion_date,

flatl.conversion_rate conversion_rate,

flatl.conversion_rate_type_code conversion_rate_type_code,

flatl.schedule_type_code schedule_type_code,

flatl.entered_amount entered_amount,

flatl.payment_number payment_number,

flatl.line_num line_number,

flatl.payment_item_id lines_payment_item_id,

flatl.transaction_line_id transaction_line_id,

flatl.applied_to_line_id applied_to_line_id,

flatl.parent_transaction_line_id parent_transaction_line_id,

decode(flatl.parent_transaction_line_id,NULL,'N','Y') reversal_line_indicator,

previous_conversion_date,

nvl(

flatl.previous_conversion_rate,flatl.conversion_rate

) previous_conversion_rate,

nvl(

flatl.previous_conv_rate_type_code,flatl.conversion_rate_type_code

) previous_conv_rate_type_code,

flath.event_id event_id,

fpav.stl_reclass short_term_liab_account,

fpav.ltl_reclass long_term_liab_account,

fpav.accounts_receivable receivables_account,

fpav.revenue revenue_account,

fpav.receivables_clearing receivables_clearing_account,

fpav.accrued_asset accrued_asset_account,

DECODE(fla.lease_type_code, 'REVENUE', flatl.dr_account_class_code, NULL) revenue_debit_account_class,

DECODE(fla.lease_type_code, 'REVENUE', flatl.cr_account_class_code, NULL) revenue_credit_account_class,

fpav.non_compliance_expense non_comp_exp_account,

fpav.upfront_tax_reserve upfront_tax_reserve_account,

fpav.upfront_recoverable_tax upfront_recover_tax_account,

(CASE

WHEN flath.transaction_type_code = 'TERMINATION' AND flatl.schedule_type_code = 'ACCUMULATED_AMORT' THEN

(SELECT min(line_num) FROM fla_accounting_lines l

WHERE

l.transaction_id = flatl.transaction_id

AND l.schedule_type_code = flatl.schedule_type_code

AND l.payment_number = flatl.payment_number)

ELSE to_number(NULL)

END) term_accum_amort_line_num

FROM

fla_accounting_headers_all flath,

(SELECT

fal.transaction_id,

fal.accounted_amount,

fal.dr_account_class_code,

fal.cr_account_class_code,

fal.currency_code,

fal.conversion_date,

fal.conversion_rate,

fal.conversion_rate_type_code,

fal.schedule_type_code,

fal.entered_amount,

fal.payment_number,

fal.line_num,

fal.payment_item_id,

fal.transaction_line_id,

fal.applied_to_line_id,

fal.parent_transaction_line_id,

nvl(fal.previous_conversion_date, decode(fsh.parent_record_id,NULL,fsh.amortization_start_date,(

SELECT

fshp.amortization_start_date

FROM

fla_schedule_headers fshp

WHERE

fshp.schedule_header_id = fsh.parent_record_id

))) previous_conversion_date,

fal.previous_conversion_rate,

fal.previous_conv_rate_type_code

FROM

fla_accounting_lines fal,

fla_schedule_headers fsh

WHERE

fsh.schedule_header_id = fal.schedule_header_id

UNION ALL

SELECT

fal.transaction_id,

fal.accounted_amount,

fal.dr_account_class_code,

fal.cr_account_class_code,

fal.currency_code,

fal.conversion_date,

fal.conversion_rate,

fal.conversion_rate_type_code,

fal.schedule_type_code,

fal.entered_amount,

fal.payment_number,

fal.line_num,

fal.payment_item_id,

fal.transaction_line_id,

fal.applied_to_line_id,

fal.parent_transaction_line_id,

nvl(fal.previous_conversion_date, decode(ftt.parent_record_id,NULL,ftt.commencement_date,(

SELECT

fttp.commencement_date

FROM

fla_tax_transactions fttp

WHERE

fttp.tax_transaction_id = ftt.parent_record_id

))) previous_conversion_date,

fal.previous_conversion_rate,

fal.previous_conv_rate_type_code

FROM

fla_accounting_lines fal,

fla_tax_transactions ftt

WHERE

ftt.tax_transaction_id = fal.source_reference_id1) flatl,

fla_xla_payment_accounts_v fpav,

fla_leases_all fla

WHERE

flatl.transaction_id = flath.transaction_id

AND flath.lease_id = fpav.lease_id

AND flath.lease_detail_id = fpav.lease_detail_id

AND flath.ledger_id = fpav.ledger_id

AND flatl.payment_number = fpav.payment_number

AND flath.lease_id = fla.lease_id