View: LNS_PAY_SUM_YEARLY_V

Product:	LNS - Loans
Description:	YTD and Last Year Payment Summary View
Implementation/DBA Data:	APPS.LNS_PAY_SUM_YEARLY_V

View Text

SELECT LOAN.LOAN_ID
, SUM (CASE WHEN ( TRUNC(REC_PRIN.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY')-1 ) THEN NVL(REC_PRIN.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( TRUNC(REC_INT.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY')-1 ) THEN NVL(REC_INT.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( TRUNC(REC_FEE.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY')-1 ) THEN NVL(REC_FEE.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( TRUNC(REC_PRIN.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY')-1) ) THEN NVL(REC_PRIN.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( TRUNC(REC_INT.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY')-1) ) THEN NVL(REC_INT.AMOUNT_APPLIED
, 0) ELSE 0 END )
, SUM (CASE WHEN ( TRUNC(REC_FEE.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY')-1) ) THEN NVL(REC_FEE.AMOUNT_APPLIED
, 0) ELSE 0 END )
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC_PRIN
, AR_RECEIVABLE_APPLICATIONS_ALL REC_INT
, AR_RECEIVABLE_APPLICATIONS_ALL REC_FEE
WHERE LOAN.LOAN_ID = AM.LOAN_ID(+)
AND NVL(AM.PHASE(+)
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND REC_PRIN.APPLIED_CUSTOMER_TRX_ID(+) = AM.PRINCIPAL_TRX_ID
AND REC_INT.APPLIED_CUSTOMER_TRX_ID(+) = AM.INTEREST_TRX_ID
AND REC_FEE.APPLIED_CUSTOMER_TRX_ID(+) = AM.FEE_TRX_ID
AND REC_PRIN.APPLICATION_TYPE(+) = 'CASH'
AND REC_INT.APPLICATION_TYPE(+) = 'CASH'
AND REC_FEE.APPLICATION_TYPE(+) = 'CASH'
AND REC_PRIN.DISPLAY(+) = 'Y'
AND REC_INT.DISPLAY(+) = 'Y'
AND REC_FEE.DISPLAY(+) = 'Y' GROUP BY LOAN.LOAN_ID

Columns