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_LOAN_PAYMENT_SUMMARY_V

Source

View Text - Preformatted


SELECT loan.LOAN_ID ,loan.FUNDED_AMOUNT - (select NVL(SUM(ABS(NVL(psa.AMOUNT_APPLIED, 0)) - NVL(psa.AMOUNT_ADJUSTED, 0) + ABS(NVL(psa.AMOUNT_CREDITED, 0))), 0) from ar_payment_schedules_all psa, LNS_AMORTIZATION_SCHEDS am1 WHERE psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am1.principal_trx_id and am1.loan_id = loan.loan_id and am1.principal_trx_id is not null and am1.PAYMENT_NUMBER <= loan.LAST_PAYMENT_NUMBER and nvl(am1.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') AND ( am1.REVERSED_FLAG IS NULL OR am1.REVERSED_FLAG = 'N' ) AND am1.REAMORTIZATION_AMOUNT IS NULL) ,(CASE WHEN (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT') THEN (select due_date from LNS_AMORTIZATION_SCHEDS where AMORTIZATION_SCHEDULE_ID = loan.LAST_AMORTIZATION_ID) ELSE to_date(null) END) ,(CASE WHEN (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT') THEN (select nvl(sum(PRINCIPAL_AMOUNT), 0) + nvl(sum(INTEREST_AMOUNT), 0) + nvl(sum(FEE_AMOUNT), 0) from LNS_AMORTIZATION_SCHEDS am where loan_id = loan.loan_id and (REVERSED_FLAG is null or REVERSED_FLAG = 'N') and REAMORTIZATION_AMOUNT is null and PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER and nvl(PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM')) ELSE 0 END) ,(CASE WHEN (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT') THEN nvl((select sum(sch.AMOUNT_DUE_REMAINING) from ar_payment_schedules_all sch, LNS_AMORTIZATION_SCHEDS am where sch.org_id = loan.org_id and am.loan_id = loan.loan_id and ((sch.customer_trx_id = am.principal_trx_id) OR (sch.customer_trx_id = am.interest_trx_id) OR (sch.customer_trx_id = am.fee_trx_id)) and trunc(am.due_date) < trunc(sysdate) and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N') and nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') and am.REAMORTIZATION_AMOUNT is null), 0) ELSE 0 END) ,(CASE WHEN (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT') THEN (select max(due_date) from LNS_AMORTIZATION_SCHEDS am where loan_id = loan.loan_id and trunc(due_date) < trunc(sysdate) and (REVERSED_FLAG is null or REVERSED_FLAG = 'N') and REAMORTIZATION_AMOUNT is null and nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') and nvl((select sum(AMOUNT_DUE_REMAINING) from ar_payment_schedules_all where org_id = loan.org_id and ((customer_trx_id = am.principal_trx_id) OR (customer_trx_id = am.interest_trx_id) OR (customer_trx_id = am.fee_trx_id))), 0) > 0) ELSE to_date(null) END) ,(CASE WHEN (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT') THEN (select min(due_date) from LNS_AMORTIZATION_SCHEDS am where loan_id = loan.loan_id and trunc(due_date) < trunc(sysdate) and (REVERSED_FLAG is null or REVERSED_FLAG = 'N') and REAMORTIZATION_AMOUNT is null and nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') and nvl((select sum(AMOUNT_DUE_REMAINING) from ar_payment_schedules_all where org_id = loan.org_id and ((customer_trx_id = am.principal_trx_id) OR (customer_trx_id = am.interest_trx_id) OR (customer_trx_id = am.fee_trx_id))), 0) > 0) ELSE to_date(null) END) ,nvl((select amount_applied from ar_receivable_applications_all where RECEIVABLE_APPLICATION_ID = (select max(RECEIVABLE_APPLICATION_ID) from ar_receivable_applications_all rec, LNS_AMORTIZATION_SCHEDS am where am.loan_id = loan.loan_id and rec.org_id = loan.org_id and rec.APPLIED_CUSTOMER_TRX_ID in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and rec.display = 'Y' and nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') and rec.application_type = 'CASH')), 0) ,(select APPLY_DATE from ar_receivable_applications_all where RECEIVABLE_APPLICATION_ID = (select max(RECEIVABLE_APPLICATION_ID) from ar_receivable_applications_all rec, LNS_AMORTIZATION_SCHEDS am where am.loan_id = loan.loan_id and rec.org_id = loan.org_id and rec.APPLIED_CUSTOMER_TRX_ID in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and rec.display = 'Y' and nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') and rec.application_type = 'CASH')) ,loan.org_id ,loan.LEGAL_ENTITY_ID ,nvl(loan.CURRENT_PHASE, 'TERM') from lns_loan_headers_all loan

View Text - HTML Formatted

SELECT LOAN.LOAN_ID
, LOAN.FUNDED_AMOUNT - (SELECT NVL(SUM(ABS(NVL(PSA.AMOUNT_APPLIED
, 0)) - NVL(PSA.AMOUNT_ADJUSTED
, 0) + ABS(NVL(PSA.AMOUNT_CREDITED
, 0)))
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND ( AM1.REVERSED_FLAG IS NULL OR AM1.REVERSED_FLAG = 'N' )
AND AM1.REAMORTIZATION_AMOUNT IS NULL)
, (CASE WHEN (LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT') THEN (SELECT DUE_DATE
FROM LNS_AMORTIZATION_SCHEDS
WHERE AMORTIZATION_SCHEDULE_ID = LOAN.LAST_AMORTIZATION_ID) ELSE TO_DATE(NULL) END)
, (CASE WHEN (LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT') THEN (SELECT NVL(SUM(PRINCIPAL_AMOUNT)
, 0) + NVL(SUM(INTEREST_AMOUNT)
, 0) + NVL(SUM(FEE_AMOUNT)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID
AND (REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND NVL(PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')) ELSE 0 END)
, (CASE WHEN (LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT') THEN NVL((SELECT SUM(SCH.AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL SCH
, LNS_AMORTIZATION_SCHEDS AM
WHERE SCH.ORG_ID = LOAN.ORG_ID
AND AM.LOAN_ID = LOAN.LOAN_ID
AND ((SCH.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR (SCH.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR (SCH.CUSTOMER_TRX_ID = AM.FEE_TRX_ID))
AND TRUNC(AM.DUE_DATE) < TRUNC(SYSDATE)
AND (AM.REVERSED_FLAG IS NULL OR AM.REVERSED_FLAG = 'N')
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND AM.REAMORTIZATION_AMOUNT IS NULL)
, 0) ELSE 0 END)
, (CASE WHEN (LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT') THEN (SELECT MAX(DUE_DATE)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID
AND TRUNC(DUE_DATE) < TRUNC(SYSDATE)
AND (REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND NVL((SELECT SUM(AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND ((CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR (CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR (CUSTOMER_TRX_ID = AM.FEE_TRX_ID)))
, 0) > 0) ELSE TO_DATE(NULL) END)
, (CASE WHEN (LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT') THEN (SELECT MIN(DUE_DATE)
FROM LNS_AMORTIZATION_SCHEDS AM
WHERE LOAN_ID = LOAN.LOAN_ID
AND TRUNC(DUE_DATE) < TRUNC(SYSDATE)
AND (REVERSED_FLAG IS NULL OR REVERSED_FLAG = 'N')
AND REAMORTIZATION_AMOUNT IS NULL
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND NVL((SELECT SUM(AMOUNT_DUE_REMAINING)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND ((CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID) OR (CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID) OR (CUSTOMER_TRX_ID = AM.FEE_TRX_ID)))
, 0) > 0) ELSE TO_DATE(NULL) END)
, NVL((SELECT AMOUNT_APPLIED
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = (SELECT MAX(RECEIVABLE_APPLICATION_ID)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
, LNS_AMORTIZATION_SCHEDS AM
WHERE AM.LOAN_ID = LOAN.LOAN_ID
AND REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLIED_CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND REC.DISPLAY = 'Y'
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND REC.APPLICATION_TYPE = 'CASH'))
, 0)
, (SELECT APPLY_DATE
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE RECEIVABLE_APPLICATION_ID = (SELECT MAX(RECEIVABLE_APPLICATION_ID)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
, LNS_AMORTIZATION_SCHEDS AM
WHERE AM.LOAN_ID = LOAN.LOAN_ID
AND REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLIED_CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND REC.DISPLAY = 'Y'
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
AND REC.APPLICATION_TYPE = 'CASH'))
, LOAN.ORG_ID
, LOAN.LEGAL_ENTITY_ID
, NVL(LOAN.CURRENT_PHASE
, 'TERM')
FROM LNS_LOAN_HEADERS_ALL LOAN