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 ACCRUAL_METHOD_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, 'OTHERS', pa.account_ccid), NULL)) gain_loss, MAX(DECODE(ph.account_class_code, 'REVENUE', DECODE(ph.payment_category_code,'RENT',aa.account_id, 'OTHERS', pa.account_ccid), NULL)) revenue, MAX(DECODE(ph.account_class_code, 'ACCOUNTS_RECEIVABLE', DECODE(ph.payment_category_code,'RENT',aa.account_id, 'OTHERS', pa.account_ccid), NULL)) accounts_receivable, MAX(DECODE(ph.account_class_code, 'RECEIVABLES_CLEARING',DECODE(ph.payment_category_code,'RENT',aa.account_id, 'OTHERS', pa.account_ccid), NULL)) receivables_clearing, MAX(DECODE(ph.account_class_code, 'ACCRUED_ASSET', DECODE(ph.payment_category_code,'RENT',aa.account_id, 'OTHERS', pa.account_ccid), NULL)) accrued_asset, ph.accrual_method_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 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 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, null accrual_method_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