1. Tables and Views for Financials
  2. FLA_XLA_REV_EQP_ASSET_PAYACT_V

FLA_XLA_REV_EQP_ASSET_PAYACT_V

Details

  • Schema: FUSION

  • Object owner: FUN

  • Object type: VIEW

Columns

Name

LEASE_ID

LEASE_DETAIL_ID

LEDGER_ID

PAYMENT_NUMBER

ASSET_NUMBER

GAIN_LOSS

REVENUE

ACCOUNTS_RECEIVABLE

RECEIVABLES_CLEARING

ACCRUED_ASSET

RECEIVABLES_CLEARING_INT_RENT

INTERIM_RENT_ASSET

RECEIVABLES_INTERIM_RENT

SUBSIDY_ASSET

SUBSIDY_INCOME

ORIGINATION_INCOME

ACCRUAL_METHOD_CODE

PAYMENT_CATEGORY_CODE

PAYMENT_PHASE_CODE

Query

SQL_Statement

SELECT ph.lease_id,

ph.lease_detail_id,

ph.ledger_id,

ph.payment_number,

ph.asset_number,

MAX(DECODE(ph.account_class_code, 'GAIN_LOSS', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) gain_loss,

MAX(DECODE(ph.account_class_code, 'REVENUE', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) revenue,

MAX(DECODE(ph.account_class_code, 'ACCOUNTS_RECEIVABLE', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) accounts_receivable,

MAX(DECODE(ph.account_class_code, 'RECEIVABLES_CLEARING', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) receivables_clearing,

MAX(DECODE(ph.account_class_code, 'ACCRUED_ASSET', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) accrued_asset,

MAX(DECODE(ph.account_class_code, 'RECEIVABLES_CLEARING_INT_RENT', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) RECEIVABLES_CLEARING_INT_RENT,

MAX(DECODE(ph.account_class_code, 'INTERIM_RENT_ASSET', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) INTERIM_RENT_ASSET,

MAX(DECODE(ph.account_class_code, 'RECEIVABLES_INTERIM_RENT', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) RECEIVABLES_INTERIM_RENT,

MAX(DECODE(ph.account_class_code, 'SUBSIDY_ASSET', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) SUBSIDY_ASSET,

MAX(DECODE(ph.account_class_code, 'SUBSIDY_INCOME', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) SUBSIDY_INCOME,

MAX(DECODE(ph.account_class_code, 'ORIGINATION_INCOME', DECODE(ph.payment_category_code,'RENT',aa.account_id, pa.account_ccid), NULL)) ORIGINATION_INCOME,

ph.accrual_method_code,

ph.payment_category_code,

ph.payment_phase_code

FROM (

SELECT flph.lease_id,

flph.lease_detail_id,

so.ledger_id,

flph.payment_number,

flph.asset_number,

flph.payment_category_code,

fl.lookup_code account_class_code,

DECODE(so.primary_flag , 'Y', flph.primary_accrual_method_code, flph.secondary_accrual_method_code) accrual_method_code,

flph.payment_phase_code

FROM fla_leases_all la,

fla_regime_details_v so,

fla_lease_details ld,

fla_lease_payment_headers flph,

(SELECT DISTINCT lookup_code FROM fnd_lookups WHERE lookup_type = 'ORA_FLA_ACCOUNT_CLASS_REV_EQP' 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_lease_asset_accounts aa

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.lease_id = aa.lease_id(+)

AND ph.lease_detail_id = aa.lease_detail_id(+)

AND ph.asset_number = aa.asset_number(+)

AND ph.ledger_id = aa.ledger_id(+)

AND ph.account_class_code = aa.account_class_code(+)

GROUP BY ph.lease_id,

ph.lease_detail_id,

ph.ledger_id,

ph.payment_number,

ph.asset_number,

ph.accrual_method_code,

ph.payment_category_code,

ph.payment_phase_code

UNION ALL

SELECT

aa.lease_id,

aa.lease_detail_id,

aa.ledger_id,

acc.payment_number,

aa.asset_number,

MAX(DECODE(aa.account_class_code, 'GAIN_LOSS', aa.account_id, NULL)) gain_loss,

MAX(DECODE(aa.account_class_code, 'REVENUE', aa.account_id, NULL)) revenue,

MAX(DECODE(aa.account_class_code, 'ACCOUNTS_RECEIVABLE', aa.account_id, NULL)) accounts_receivable,

MAX(DECODE(aa.account_class_code, 'RECEIVABLES_CLEARING', aa.account_id, NULL)) receivables_clearing,

MAX(DECODE(aa.account_class_code, 'ACCRUED_ASSET', aa.account_id, NULL)) accrued_asset,

MAX(DECODE(aa.account_class_code, 'RECEIVABLES_CLEARING_INT_RENT', aa.account_id, NULL)) RECEIVABLES_CLEARING_INT_RENT,

MAX(DECODE(aa.account_class_code, 'INTERIM_RENT_ASSET', aa.account_id, NULL)) INTERIM_RENT_ASSET,

MAX(DECODE(aa.account_class_code, 'RECEIVABLES_INTERIM_RENT', aa.account_id, NULL)) RECEIVABLES_INTERIM_RENT,

MAX(DECODE(aa.account_class_code, 'SUBSIDY_ASSET', aa.account_id, NULL)) SUBSIDY_ASSET,

MAX(DECODE(aa.account_class_code, 'SUBSIDY_INCOME', aa.account_id, NULL)) SUBSIDY_INCOME,

MAX(DECODE(aa.account_class_code, 'ORIGINATION_INCOME', aa.account_id, NULL)) ORIGINATION_INCOME,

NULL accrual_method_code,

NULL payment_category_code,

NULL payment_phase_code

FROM (

SELECT la.lease_id,

ld.lease_detail_id,

so.ledger_id,

'00000' payment_number,

fl.lookup_code account_class_code

FROM fla_leases_all la,

fla_regime_details_v so,

fla_lease_details ld,

(SELECT DISTINCT lookup_code FROM fnd_lookups WHERE lookup_type = 'ORA_FLA_ACCOUNT_CLASS_REV_EQP' AND enabled_flag = 'Y') fl

WHERE la.lease_id = ld.lease_id

AND ld.version_type_code = 'F'

AND la.org_id = so.org_id

AND so.ledger_id IS NOT NULL

) acc,

fla_lease_asset_accounts aa

WHERE aa.lease_id = acc.lease_id

AND aa.lease_detail_id = acc.lease_detail_id

AND aa.ledger_id = acc.ledger_id(+)

AND aa.account_class_code = acc.account_class_code (+)

GROUP BY

aa.lease_id,

aa.lease_detail_id,

aa.ledger_id,

acc.payment_number,

aa.asset_number