View: LNS_AM_SCHEDS_DTL_V

Product:	LNS - Loans
Description:	Amortization Schedule Details View
Implementation/DBA Data:	APPS.LNS_AM_SCHEDS_DTL_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))
, 0
, 0
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_CREDITED
, 0))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_CREDITED
, 0))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_ADJUSTED
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_APPLIED
, 0))
, 0
, 0
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_CREDITED
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_CREDITED
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_ADJUSTED
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_APPLIED
, 0))
, 0
, 0
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_CREDITED
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_CREDITED
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_ADJUSTED
, 0))
, (NVL(AM.PRINCIPAL_AMOUNT
, 0) + NVL(AM.INTEREST_AMOUNT
, 0) + NVL(AM.FEE_AMOUNT
, 0))
, DECODE(AM.REVERSED_FLAG
, NULL
, 'N'
, AM.REVERSED_FLAG)
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, LOK2.MEANING
, 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))
, NVL(AM.PHASE
, 'TERM')
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, 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 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