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.

[Home] [Help]

View: LNS_PAYMENTS_SUMMARY_V

Product:	LNS - Loans
Description:	This view contains billing and payment summary information for every loan in the system
Implementation/DBA Data:	APPS.LNS_PAYMENTS_SUMMARY_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)
, (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)
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 (SELECT NVL(SUM(INTEREST_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 (SELECT 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 (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)
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 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 AM1.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID) OR (AM1.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER))
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.INTEREST_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.INTEREST_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PSA
, LNS_AMORTIZATION_SCHEDS AM1
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM1.FEE_TRX_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.FEE_TRX_ID IS NOT NULL
AND AM1.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 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'))
, LOAN.FUNDED_AMOUNT - (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 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'))
, LOAN.FUNDED_AMOUNT - (SELECT NVL(SUM(PSA.AMOUNT_APPLIED)
, 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 AM1.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID) OR (AM1.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER))
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, LOAN.FUNDED_AMOUNT
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID
AND AM.PRINCIPAL_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID
AND (TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY')-1)
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND AM.INTEREST_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID
AND (TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY')-1)
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.FEE_TRX_ID
AND AM.FEE_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID
AND (TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(SYSDATE
, 'YYYY')
AND TRUNC(ADD_MONTHS(SYSDATE
, 12)
, 'YYYY')-1)
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID
AND AM.PRINCIPAL_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID
AND (TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY')-1))
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID
AND AM.INTEREST_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID
AND (TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY')-1))
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (SELECT NVL(SUM(REC.AMOUNT_APPLIED)
, 0)
FROM LNS_AMORTIZATION_SCHEDS AM
, AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE REC.ORG_ID = LOAN.ORG_ID
AND REC.APPLICATION_TYPE = 'CASH'
AND REC.APPLIED_CUSTOMER_TRX_ID = AM.FEE_TRX_ID
AND AM.FEE_TRX_ID IS NOT NULL
AND AM.LOAN_ID = LOAN.LOAN_ID
AND (TRUNC(REC.APPLY_DATE) BETWEEN TRUNC(ADD_MONTHS(SYSDATE
, -12)
, 'YYYY')
AND TRUNC(TRUNC(SYSDATE
, 'YYYY')-1))
AND NVL(AM.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, (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
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 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.INTEREST_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 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.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 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 NVL((SELECT COUNT(1)
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)
, 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

Columns

Name
LOAN_ID
NEXT_PAYMENT_AMORTIZATION_ID
NEXT_PAYMENT_NUMBER
NEXT_PAYMENT_DUE_DATE
NEXT_PAYMENT_PRINCIPAL_DUE
NEXT_PAYMENT_INTEREST_DUE
NEXT_PAYMENT_FEE_DUE
NEXT_PAYMENT_TOTAL_DUE
PRINCIPAL_PAID_TODATE
INTEREST_PAID_TODATE
FEE_PAID_TODATE
TOTAL_PRIN_PAID_TODATE
TOTAL_PRINCIPAL_BALANCE
PRINCIPAL_BALANCE
FUNDED_AMOUNT
PRINCIPAL_PAID_YTD
INTEREST_PAID_YTD
FEE_PAID_YTD
PRINCIPAL_PAID_LAST_YEAR
INTEREST_PAID_LAST_YEAR
FEE_PAID_LAST_YEAR
PRINCIPAL_OVERDUE
INTEREST_OVERDUE
FEE_OVERDUE
TOTAL_OVERDUE
NUMBER_OVERDUE_BILLS
LAST_OVERDUE_DATE
OLDEST_OVERDUE_DATE
LAST_PAYMENT_AMOUNT
LAST_PAYMENT_DATE
ORG_ID
LEGAL_ENTITY_ID
CURRENT_PHASE