View: LNS_AM_SCHEDS_V

Product:	LNS - Loans
Description:	Amortization Schedules View
Implementation/DBA Data:	APPS.LNS_AM_SCHEDS_V

View Text

SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, AM.DUE_DATE
, AM.PRINCIPAL_AMOUNT
, AM.INTEREST_AMOUNT
, AM.FEE_AMOUNT
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_APPLIED
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_APPLIED
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_APPLIED
, 0))
, NVL(AM.PRINCIPAL_BALANCE
, LOAN.FUNDED_AMOUNT) - SUM(NVL(PSA_PRIN.AMOUNT_APPLIED
, 0)) OVER (PARTITION BY AM.LOAN_ID
, NVL(AM.PHASE
, 'TERM') ORDER BY NVL(AM.PHASE
, 'TERM')
, AM.PAYMENT_NUMBER
, AM.AMORTIZATION_SCHEDULE_ID ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
, NVL(AM.PRINCIPAL_BALANCE
, LOAN.FUNDED_AMOUNT)
, (NVL(AM.PRINCIPAL_AMOUNT
, 0) + NVL(AM.INTEREST_AMOUNT
, 0) + NVL(AM.FEE_AMOUNT
, 0))
, NVL(PSA_PRIN.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_INT.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_FEE.AMOUNT_DUE_ORIGINAL
, 0)
, NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0)
, (SELECT MAX(REC.APPLY_DATE)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID AND REC.APPLIED_CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID) AND REC.APPLICATION_TYPE = 'CASH')
, DECODE(AM.REVERSED_FLAG
, NULL
, 'N'
, AM.REVERSED_FLAG)
, LOK1.MEANING
, AM.CREATION_DATE
, AM.PARENT_AMORTIZATION_ID
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, LOK2.MEANING
, DECODE(NVL(AM.REVERSED_FLAG
, 'N')
, 'Y'
, 'CREDIT_DISABLED'
, DECODE(DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, 'MANUAL'
, DECODE((NVL(PSA_PRIN.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_INT.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_FEE.AMOUNT_DUE_ORIGINAL
, 0)) - (NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0))
, 0
, 'CREDIT_ENABLED'
, 'CREDIT_DISABLED' )
, DECODE(LOAN.LAST_PAYMENT_NUMBER
, AM.PAYMENT_NUMBER
, DECODE((NVL(PSA_PRIN.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_INT.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_FEE.AMOUNT_DUE_ORIGINAL
, 0)) - (NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0))
, 0
, 'CREDIT_ENABLED'
, 'CREDIT_DISABLED' )
, 'CREDIT_DISABLED' ) ) )
, DECODE(NVL(AM.REVERSED_FLAG
, 'N')
, 'Y'
, 'REBILL_DISABLED'
, DECODE(DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, 'MANUAL'
, 'REBILL_DISABLED'
, DECODE(LOAN.LAST_PAYMENT_NUMBER
, AM.PAYMENT_NUMBER
, DECODE((NVL(PSA_PRIN.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_INT.AMOUNT_DUE_ORIGINAL
, 0) + NVL(PSA_FEE.AMOUNT_DUE_ORIGINAL
, 0)) - (NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0))
, 0
, 'REBILL_ENABLED'
, 'REBILL_DISABLED' )
, 'REBILL_DISABLED' ) ) )
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0))
, AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID
, AM.STATEMENT_XML
, LOAN.LOAN_STATUS
, LOAN.ORG_ID
, LOAN.LOAN_CURRENCY
, LOAN.LEGAL_ENTITY_ID
, NVL(AM.PHASE
, 'TERM')
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, FND_LOOKUPS LOK1
, LNS_LOOKUPS LOK2
, AR_PAYMENT_SCHEDULES_ALL PSA_PRIN
, AR_PAYMENT_SCHEDULES_ALL PSA_INT
, AR_PAYMENT_SCHEDULES_ALL PSA_FEE
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND AM.REAMORTIZATION_AMOUNT IS NULL
AND LOK1.LOOKUP_TYPE = 'YES_NO'
AND LOK1.LOOKUP_CODE = NVL(AM.REVERSED_FLAG
, 'N')
AND LOK2.LOOKUP_TYPE = 'BILL_TYPE'
AND LOK2.LOOKUP_CODE = DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
AND PSA_PRIN.CUSTOMER_TRX_ID(+) = AM.PRINCIPAL_TRX_ID
AND PSA_INT.CUSTOMER_TRX_ID(+) = AM.INTEREST_TRX_ID
AND PSA_FEE.CUSTOMER_TRX_ID(+) = AM.FEE_TRX_ID

Columns