eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.LNS_PAY_SUM_OVERDUE_V

Source

View Text - Preformatted


SELECT loan.loan_id, SUM( CASE WHEN(loan.loan_status = 'ACTIVE' OR loan.loan_status = 'DEFAULT' OR loan.loan_status = 'DELINQUENT' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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' OR loan.loan_status = 'INCOMPLETE' OR loan.loan_status = 'PENDING' OR loan.loan_status = 'APPROVED') 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), MAX(nvl( (SELECT amount_applied FROM ar_receivable_applications_all WHERE RECEIVABLE_APPLICATION_ID = (select max(RECEIVABLE_APPLICATION_ID) from ar_receivable_applications_all rec where rec.APPLIED_CUSTOMER_TRX_ID in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and rec.display = 'Y' and rec.application_type = 'CASH')), 0)) ,max((select APPLY_DATE from ar_receivable_applications_all where RECEIVABLE_APPLICATION_ID = (select max(RECEIVABLE_APPLICATION_ID) from ar_receivable_applications_all rec where rec.APPLIED_CUSTOMER_TRX_ID in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and rec.display = 'Y' and rec.application_type = 'CASH'))) 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

View Text - HTML Formatted

SELECT LOAN.LOAN_ID
, SUM( CASE WHEN(LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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' OR LOAN.LOAN_STATUS = 'INCOMPLETE' OR LOAN.LOAN_STATUS = 'PENDING' OR LOAN.LOAN_STATUS = 'APPROVED') 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)
, MAX(NVL( (SELECT AMOUNT_APPLIED
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = (SELECT MAX(RECEIVABLE_APPLICATION_ID)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.APPLIED_CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND REC.DISPLAY = 'Y'
AND REC.APPLICATION_TYPE = 'CASH'))
, 0))
, MAX((SELECT APPLY_DATE
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = (SELECT MAX(RECEIVABLE_APPLICATION_ID)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.APPLIED_CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND REC.DISPLAY = 'Y'
AND REC.APPLICATION_TYPE = 'CASH')))
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