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_V

Source

View Text - Preformatted


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 ) , MAX (CASE WHEN ( loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT' ) THEN am.due_date ELSE to_date(null) END ) , SUM (CASE WHEN ( ( loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT' ) AND ( am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER ) ) THEN nvl(am.PRINCIPAL_AMOUNT, 0) ELSE 0 END ) , SUM (CASE WHEN ( ( loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT' ) AND ( am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER ) ) THEN nvl(am.INTEREST_AMOUNT, 0) ELSE 0 END ) , SUM (CASE WHEN ( ( loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT' ) AND ( am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER ) ) THEN nvl(am.FEE_AMOUNT, 0) ELSE 0 END ) , SUM (CASE WHEN ( ( loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'DEFAULT' OR loan.LOAN_STATUS = 'DELINQUENT' ) AND ( am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER ) ) THEN nvl(am.PRINCIPAL_AMOUNT, 0) + nvl(am.INTEREST_AMOUNT, 0) + nvl(am.FEE_AMOUNT, 0) ELSE 0 END ) , SUM (CASE WHEN ( ( am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER AND am.AMORTIZATION_SCHEDULE_ID <= loan.LAST_AMORTIZATION_ID ) OR ( am.PAYMENT_NUMBER < loan.LAST_PAYMENT_NUMBER ) ) THEN abs(nvl(psa_prin.AMOUNT_APPLIED, 0)) - nvl(psa_prin.AMOUNT_ADJUSTED, 0) + abs(nvl(psa_prin.AMOUNT_CREDITED, 0)) ELSE 0 END ) , (CASE WHEN (loan.LOAN_STATUS not in ('ACTIVE', 'DEFAULT', 'DELINQUENT')) THEN 0 ELSE SUM (CASE WHEN ( am.PAYMENT_NUMBER <= loan.LAST_PAYMENT_NUMBER ) THEN abs(nvl(psa_int.AMOUNT_APPLIED, 0)) - nvl(psa_int.AMOUNT_ADJUSTED, 0) + abs(nvl(psa_int.AMOUNT_CREDITED, 0)) ELSE 0 END ) END ) , SUM (CASE WHEN ( am.PAYMENT_NUMBER <= loan.LAST_PAYMENT_NUMBER ) THEN abs(nvl(psa_fee.AMOUNT_APPLIED, 0)) - nvl(psa_fee.AMOUNT_ADJUSTED, 0) + abs(nvl(psa_fee.AMOUNT_CREDITED, 0)) ELSE 0 END ) , (CASE WHEN (loan.LOAN_STATUS not in ('ACTIVE', 'DEFAULT', 'DELINQUENT')) THEN 0 ELSE SUM (CASE WHEN ( am.PAYMENT_NUMBER <= loan.LAST_PAYMENT_NUMBER ) THEN abs(nvl(psa_prin.AMOUNT_APPLIED, 0)) - nvl(psa_prin.AMOUNT_ADJUSTED, 0) + abs(nvl(psa_prin.AMOUNT_CREDITED, 0)) ELSE 0 END ) END ) , (CASE WHEN (loan.LOAN_STATUS = 'CANCELLED' AND nvl(loan.CURRENT_PHASE, 'TERM') = 'TERM') or (loan.LOAN_STATUS not in ('ACTIVE', 'DEFAULT', 'DELINQUENT')) THEN 0 ELSE (MAX(decode(nvl(loan.BILL_ON_APPR_AMOUNT_FLAG, 'N'), 'Y', (loan.requested_amount + nvl(loan.ADD_REQUESTED_AMOUNT, 0)), loan.FUNDED_AMOUNT)) - SUM (CASE WHEN ( am.PAYMENT_NUMBER <= loan.LAST_PAYMENT_NUMBER ) THEN abs(nvl(psa_prin.AMOUNT_APPLIED, 0)) - nvl(psa_prin.AMOUNT_ADJUSTED, 0) + abs(nvl(psa_prin.AMOUNT_CREDITED, 0)) - nvl(psa_prin.TAX_ORIGINAL, 0) ELSE 0 END ) ) END ) , 0 , loan.FUNDED_AMOUNT , nvl(loan.CURRENT_PHASE, 'TERM') CURRENT_PHASE 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 loan.last_amortization_id >= am.amortization_schedule_id(+) AND nvl(am.PHASE(+), 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM') 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 , loan.LOAN_STATUS , loan.LAST_AMORTIZATION_ID , loan.CURRENT_PHASE , loan.LAST_PAYMENT_NUMBER , loan.FUNDED_AMOUNT

View Text - HTML Formatted

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 )
, MAX (CASE WHEN ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' ) THEN AM.DUE_DATE ELSE TO_DATE(NULL) END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.PRINCIPAL_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.INTEREST_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.FEE_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( LOAN.LOAN_STATUS = 'ACTIVE' OR LOAN.LOAN_STATUS = 'DEFAULT' OR LOAN.LOAN_STATUS = 'DELINQUENT' )
AND ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER ) ) THEN NVL(AM.PRINCIPAL_AMOUNT
, 0) + NVL(AM.INTEREST_AMOUNT
, 0) + NVL(AM.FEE_AMOUNT
, 0) ELSE 0 END )
, SUM (CASE WHEN ( ( AM.PAYMENT_NUMBER = LOAN.LAST_PAYMENT_NUMBER
AND AM.AMORTIZATION_SCHEDULE_ID <= LOAN.LAST_AMORTIZATION_ID ) OR ( AM.PAYMENT_NUMBER < LOAN.LAST_PAYMENT_NUMBER ) ) THEN ABS(NVL(PSA_PRIN.AMOUNT_APPLIED
, 0)) - NVL(PSA_PRIN.AMOUNT_ADJUSTED
, 0) + ABS(NVL(PSA_PRIN.AMOUNT_CREDITED
, 0)) ELSE 0 END )
, (CASE WHEN (LOAN.LOAN_STATUS NOT IN ('ACTIVE'
, 'DEFAULT'
, 'DELINQUENT')) THEN 0 ELSE SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN ABS(NVL(PSA_INT.AMOUNT_APPLIED
, 0)) - NVL(PSA_INT.AMOUNT_ADJUSTED
, 0) + ABS(NVL(PSA_INT.AMOUNT_CREDITED
, 0)) ELSE 0 END ) END )
, SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN ABS(NVL(PSA_FEE.AMOUNT_APPLIED
, 0)) - NVL(PSA_FEE.AMOUNT_ADJUSTED
, 0) + ABS(NVL(PSA_FEE.AMOUNT_CREDITED
, 0)) ELSE 0 END )
, (CASE WHEN (LOAN.LOAN_STATUS NOT IN ('ACTIVE'
, 'DEFAULT'
, 'DELINQUENT')) THEN 0 ELSE SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN ABS(NVL(PSA_PRIN.AMOUNT_APPLIED
, 0)) - NVL(PSA_PRIN.AMOUNT_ADJUSTED
, 0) + ABS(NVL(PSA_PRIN.AMOUNT_CREDITED
, 0)) ELSE 0 END ) END )
, (CASE WHEN (LOAN.LOAN_STATUS = 'CANCELLED'
AND NVL(LOAN.CURRENT_PHASE
, 'TERM') = 'TERM') OR (LOAN.LOAN_STATUS NOT IN ('ACTIVE'
, 'DEFAULT'
, 'DELINQUENT')) THEN 0 ELSE (MAX(DECODE(NVL(LOAN.BILL_ON_APPR_AMOUNT_FLAG
, 'N')
, 'Y'
, (LOAN.REQUESTED_AMOUNT + NVL(LOAN.ADD_REQUESTED_AMOUNT
, 0))
, LOAN.FUNDED_AMOUNT)) - SUM (CASE WHEN ( AM.PAYMENT_NUMBER <= LOAN.LAST_PAYMENT_NUMBER ) THEN ABS(NVL(PSA_PRIN.AMOUNT_APPLIED
, 0)) - NVL(PSA_PRIN.AMOUNT_ADJUSTED
, 0) + ABS(NVL(PSA_PRIN.AMOUNT_CREDITED
, 0)) - NVL(PSA_PRIN.TAX_ORIGINAL
, 0) ELSE 0 END ) ) END )
, 0
, LOAN.FUNDED_AMOUNT
, NVL(LOAN.CURRENT_PHASE
, 'TERM') CURRENT_PHASE
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 LOAN.LAST_AMORTIZATION_ID >= AM.AMORTIZATION_SCHEDULE_ID(+)
AND NVL(AM.PHASE(+)
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM')
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
, LOAN.LOAN_STATUS
, LOAN.LAST_AMORTIZATION_ID
, LOAN.CURRENT_PHASE
, LOAN.LAST_PAYMENT_NUMBER
, LOAN.FUNDED_AMOUNT