View: LNS_PAY_SUM_OVERDUE_V

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

View Text

SELECT LOAN.LOAN_ID
, SUM (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) ELSE 0 END )
, SUM (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) ELSE 0 END )
, SUM (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0) ELSE 0 END )
, SUM (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0) ELSE 0 END )
, COUNT (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( ( NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0) ) > 0 ) ) THEN AM.AMORTIZATION_SCHEDULE_ID ELSE NULL END )
, MAX (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( ( NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0) ) > 0 ) ) THEN AM.DUE_DATE ELSE TO_DATE(NULL) END )
, MIN (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( ( NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0) + NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0) ) > 0 ) ) THEN AM.DUE_DATE ELSE TO_DATE(NULL) END )
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 NVL(AM.PHASE(+)
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND TRUNC(AM.DUE_DATE(+)) < TRUNC(SYSDATE)
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

Columns