FLA_XLA_PAYMENT_ACCOUNTS_V

Details

• Schema: FUSION

• Object owner: FUN

• Object type: VIEW

Columns

Name
LEASE_ID LEASE_DETAIL_ID LEDGER_ID PAYMENT_NUMBER RIGHT_OF_USE LEASE_LIABILITY RESERVE AP_LIABILITY INTEREST_EXPENSE AMORTIZATION LEASE_EXPENSE NON_COMPLIANCE_EXPENSE GAIN_LOSS ACCUMULATED_AMORTIZATION ACCRUED_LIABILITY AP_CLEARING ROU_ADJ_LOSS FOREX_GAIN_LOSS STL_RECLASS LTL_RECLASS REVENUE ACCOUNTS_RECEIVABLE RECEIVABLES_CLEARING ACCRUED_ASSET UPFRONT_TAX_RESERVE UPFRONT_RECOVERABLE_TAX

Query

SQL_Statement

SELECT ph.lease_id, ph.lease_detail_id, ph.ledger_id, ph.payment_number, MAX(DECODE(ph.account_class_code, 'RIGHT_OF_USE', pa.account_ccid, NULL)) right_of_use, MAX(DECODE(ph.account_class_code, 'LEASE_LIABILITY', pa.account_ccid, NULL)) lease_liability, MAX(DECODE(ph.account_class_code, 'RESERVE', pa.account_ccid, NULL)) reserve, MAX(DECODE(ph.account_class_code, 'AP_LIABILITY', pa.account_ccid, NULL)) ap_liability, MAX(DECODE(ph.account_class_code, 'INTEREST_EXPENSE', pa.account_ccid, NULL)) interest_expense, MAX(DECODE(ph.account_class_code, 'AMORTIZATION', pa.account_ccid, NULL)) amortization, MAX(DECODE(ph.account_class_code, 'LEASE_EXPENSE', pa.account_ccid, NULL)) lease_expense, MAX(DECODE(ph.account_class_code, 'NON_COMPLIANCE_EXPENSE', pa.account_ccid, NULL)) non_compliance_expense, MAX(DECODE(ph.account_class_code, 'GAIN_LOSS', pa.account_ccid, NULL)) gain_loss, MAX(DECODE(ph.account_class_code, 'ACCUMULATED_AMORTIZATION', pa.account_ccid, NULL)) accumulated_amortization, MAX(DECODE(ph.account_class_code, 'ACCRUED_LIABILITY', pa.account_ccid, NULL)) accrued_liability, MAX(DECODE(ph.account_class_code, 'AP_CLEARING', pa.account_ccid, NULL)) ap_clearing, MAX(DECODE(ph.account_class_code, 'ROU_ADJ_LOSS', pa.account_ccid, NULL)) rou_adj_loss, MAX(DECODE(ph.account_class_code, 'FOREX_GAIN_LOSS', pa.account_ccid, NULL)) forex_gain_loss, MAX(DECODE(ph.account_class_code, 'STL_RECLASS', NVL(pa.account_ccid, DECODE(pt.active_flag, 'Y', pta.account_id, NULL)), NULL)) stl_reclass, MAX(DECODE(ph.account_class_code, 'LTL_RECLASS', NVL(pa.account_ccid, DECODE(pt.active_flag, 'Y', pta.account_id, NULL)), NULL)) ltl_reclass, MAX(DECODE(ph.account_class_code, 'REVENUE', pa.account_ccid, NULL)) revenue, MAX(DECODE(ph.account_class_code, 'ACCOUNTS_RECEIVABLE', pa.account_ccid, NULL)) accounts_receivable, MAX(DECODE(ph.account_class_code, 'RECEIVABLES_CLEARING', pa.account_ccid, NULL)) receivables_clearing, MAX(DECODE(ph.account_class_code, 'ACCRUED_ASSET', pa.account_ccid, NULL)) accrued_asset, MAX(DECODE(ph.account_class_code, 'UPFRONT_TAX_RESERVE', pa.account_ccid, NULL)) upfront_tax_reserve, MAX(DECODE(ph.account_class_code, 'UPFRONT_RECOVERABLE_TAX', pa.account_ccid, NULL)) upfront_recoverable_tax FROM ( SELECT flph.lease_id, flph.lease_detail_id, so.ledger_id, flph.payment_number, fl.lookup_code account_class_code, flph.payment_template_id FROM fla_leases_all la, ( SELECT org_id, DECODE(regime_num ,1, ledger_id, secondary_ledger_id) ledger_id FROM fla_system_options_all, (SELECT LEVEL AS regime_num FROM dual CONNECT BY LEVEL < 3) ro ) so, fla_lease_details ld, fla_lease_payment_headers flph, (SELECT DISTINCT lookup_code FROM fnd_lookups WHERE lookup_type IN ( 'ORA_FLA_ACCOUNT_CLASS', 'ORA_FLA_ACCOUNT_CLASS_REV' ) AND enabled_flag = 'Y') fl WHERE la.lease_id = ld.lease_id AND la.org_id = so.org_id AND so.ledger_id IS NOT NULL AND ld.lease_detail_id = flph.lease_detail_id AND ld.version_type_code = 'F' ) ph, fla_lease_payment_accounts pa, fla_payment_templates_all pt, fla_payment_template_accounts pta WHERE ph.lease_id = pa.lease_id(+) AND ph.lease_detail_id = pa.lease_detail_id(+) AND ph.payment_number = pa.payment_number(+) AND ph.ledger_id = pa.ledger_id(+) AND ph.account_class_code = pa.account_class_code(+) AND ph.payment_template_id = pta.payment_template_id(+) AND ph.payment_template_id = pt.payment_template_id(+) AND ph.ledger_id = pta.ledger_id(+) AND ph.account_class_code = pta.account_class_code(+) GROUP BY ph.lease_id, ph.lease_detail_id, ph.ledger_id, ph.payment_number