View: LNS_PAY_SUM_V

Product:	LNS - Loans
Description:	Payment Summary View
Implementation/DBA Data:	APPS.LNS_PAY_SUM_V

View Text

SELECT LOAN.LOAN_ID
, (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN LOAN.LAST_AMORTIZATION_ID ELSE NULL END )
, (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN LOAN.LAST_PAYMENT_NUMBER ELSE NULL END )
, MAX (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN AM.DUE_DATE ELSE TO_DATE(NULL) END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.PRINCIPAL_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.INTEREST_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.FEE_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.PRINCIPAL_AMOUNT
, 0) + NVL(AM.INTEREST_AMOUNT
, 0) + NVL(AM.FEE_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND AM.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID ) OR ( AM.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(PSA_PRIN.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN NVL(PSA_INT.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN NVL(PSA_FEE.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN NVL(PSA_PRIN.AMOUNT_APPLIED
, 0) ELSE 0 END )
, (LOAN.FUNDED_AMOUNT - SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN NVL(PSA_PRIN.AMOUNT_APPLIED
, 0) ELSE 0 END ) )
, (LOAN.FUNDED_AMOUNT - SUM (CASE WHEN ( ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND AM.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID ) OR ( AM.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(PSA_PRIN.AMOUNT_APPLIED
, 0) ELSE 0 END ) )
, LOAN.FUNDED_AMOUNT
, NVL(LOAN.CURRENT_PHASE
, 'TERM') CURRENT_PHASE
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, 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 LOAN.LAST_AMORTIZATION_ID = AM.AMORTIZATION_SCHEDULE_ID(+)
AND NVL(AM.PHASE(+)
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND ( AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N' )
AND AM.REAMORTIZATION_AMOUNT IS NULL
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 GROUP BY LOAN.LOAN_ID
, LOAN.LOAN_STATUS
, LOAN.LAST_AMORTIZATION_ID
, LOAN.CURRENT_PHASE
, LOAN.LAST_PAYMENT_NUMBER
, LOAN.FUNDED_AMOUNT

Columns