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_AMORTIZATION_SCHEDULES_V

Source

View Text - Preformatted


SELECT loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,am.DUE_DATE ,am.PRINCIPAL_AMOUNT ,am.INTEREST_AMOUNT ,am.FEE_AMOUNT ,decode(am.principal_trx_id, null, 0, nvl((select psa.AMOUNT_APPLIED from ar_payment_schedules_all psa WHERE psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.principal_trx_id), 0)) ,0 ,0 ,decode(am.principal_trx_id, null, 0, nvl((select psa.AMOUNT_CREDITED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.principal_trx_id), 0)) ,decode(am.principal_trx_id, null, 0, nvl((select psa.AMOUNT_CREDITED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.principal_trx_id), 0)) ,decode(am.principal_trx_id, null, 0, nvl((select psa.AMOUNT_ADJUSTED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.principal_trx_id), 0)) ,decode(am.principal_trx_id, null, 0, nvl((select psa.AMOUNT_APPLIED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.principal_trx_id), 0)) ,decode(am.interest_trx_id, null, 0, nvl((select psa.AMOUNT_APPLIED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.interest_trx_id), 0)) ,0 ,0 ,decode(am.interest_trx_id, null, 0, nvl((select psa.AMOUNT_CREDITED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.interest_trx_id), 0)) ,decode(am.interest_trx_id, null, 0, nvl((select psa.AMOUNT_CREDITED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.interest_trx_id), 0)) ,decode(am.interest_trx_id, null, 0, nvl((select psa.AMOUNT_ADJUSTED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.interest_trx_id), 0)) ,decode(am.interest_trx_id, null, 0, nvl((select psa.AMOUNT_APPLIED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.interest_trx_id), 0)) ,decode(am.fee_trx_id, null, 0, nvl((select psa.AMOUNT_APPLIED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.fee_trx_id), 0)) ,0 ,0 ,decode(am.fee_trx_id, null, 0, nvl((select psa.AMOUNT_CREDITED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.fee_trx_id), 0)) ,decode(am.fee_trx_id, null, 0, nvl((select psa.AMOUNT_CREDITED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.fee_trx_id), 0)) ,decode(am.fee_trx_id, null, 0, nvl((select psa.AMOUNT_ADJUSTED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.fee_trx_id), 0)) ,decode(am.fee_trx_id, null, 0, nvl((select psa.AMOUNT_APPLIED from ar_payment_schedules_all psa where psa.org_id = loan.org_id and psa.CUSTOMER_TRX_ID = am.fee_trx_id), 0)) ,nvl(am.PRINCIPAL_BALANCE, 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 = am.loan_id and am1.principal_trx_id is not null and ((am1.PAYMENT_NUMBER = am.PAYMENT_NUMBER and am1.AMORTIZATION_SCHEDULE_ID <= am.AMORTIZATION_SCHEDULE_ID) or (am1.PAYMENT_NUMBER < am.PAYMENT_NUMBER)) and nvl(am.PHASE, 'TERM') = nvl(am1.PHASE, 'TERM')) ,nvl(am.PRINCIPAL_BALANCE, loan.funded_amount) ,(nvl(am.PRINCIPAL_AMOUNT, 0) + nvl(am.INTEREST_AMOUNT, 0) + nvl(am.FEE_AMOUNT, 0)) ,(select sum(nvl(AMOUNT_DUE_ORIGINAL, 0)) from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id)) ,(select sum(nvl(AMOUNT_DUE_REMAINING, 0)) from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id)) ,(select max(rec.apply_date) from ar_receivable_applications_all rec where 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.application_type = 'CASH') ,decode(am.REVERSED_FLAG, null, 'N', am.REVERSED_FLAG) ,lok1.MEANING ,am.CREATION_DATE ,am.PARENT_AMORTIZATION_ID ,decode(am.PARENT_AMORTIZATION_ID, null, 'SCHEDULED', 'MANUAL') ,lok2.MEANING ,decode(nvl(am.REVERSED_FLAG, 'N'), 'Y', 'CREDIT_DISABLED', decode(decode(am.PARENT_AMORTIZATION_ID, null, 'SCHEDULED', 'MANUAL'), 'MANUAL', decode((select nvl(sum(AMOUNT_DUE_ORIGINAL), 0) - nvl(sum(AMOUNT_DUE_REMAINING), 0) from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id)), 0, 'CREDIT_ENABLED', 'CREDIT_DISABLED' ), decode(loan.LAST_PAYMENT_NUMBER, am.PAYMENT_NUMBER, decode((select nvl(sum(AMOUNT_DUE_ORIGINAL), 0) - nvl(sum(AMOUNT_DUE_REMAINING), 0) from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id)), 0, 'CREDIT_ENABLED', 'CREDIT_DISABLED' ), 'CREDIT_DISABLED' ) ) ) ,decode(nvl(am.REVERSED_FLAG, 'N'), 'Y', 'REBILL_DISABLED', decode(decode(am.PARENT_AMORTIZATION_ID, null, 'SCHEDULED', 'MANUAL'), 'MANUAL', 'REBILL_DISABLED', decode(loan.LAST_PAYMENT_NUMBER, am.PAYMENT_NUMBER, decode((select nvl(sum(AMOUNT_DUE_ORIGINAL), 0) - nvl(sum(AMOUNT_DUE_REMAINING), 0) from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id)), 0, 'REBILL_ENABLED', 'REBILL_DISABLED' ), 'REBILL_DISABLED' ) ) ) ,nvl((select AMOUNT_DUE_REMAINING from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id = am.principal_trx_id), 0) ,nvl((select AMOUNT_DUE_REMAINING from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id = am.interest_trx_id), 0) ,nvl((select AMOUNT_DUE_REMAINING from ar_payment_schedules_all where org_id = loan.org_id and customer_trx_id = am.fee_trx_id), 0) ,am.PRINCIPAL_TRX_ID ,am.INTEREST_TRX_ID ,am.FEE_TRX_ID ,am.STATEMENT_XML ,loan.LOAN_STATUS ,loan.ORG_ID ,loan.LOAN_CURRENCY ,loan.LEGAL_ENTITY_ID ,nvl(am.PHASE, 'TERM') from lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,FND_LOOKUPS lok1 ,LNS_LOOKUPS lok2 where loan.loan_id = am.loan_id and am.REAMORTIZATION_AMOUNT is null and lok1.lookup_type = 'YES_NO' and lok1.lookup_code = nvl(am.REVERSED_FLAG, 'N') and lok2.lookup_type = 'BILL_TYPE' and lok2.lookup_code = decode(am.PARENT_AMORTIZATION_ID, null, 'SCHEDULED', 'MANUAL')

View Text - HTML Formatted

SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, AM.DUE_DATE
, AM.PRINCIPAL_AMOUNT
, AM.INTEREST_AMOUNT
, AM.FEE_AMOUNT
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_APPLIED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID)
, 0))
, 0
, 0
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_CREDITED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID)
, 0))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_CREDITED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID)
, 0))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_ADJUSTED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID)
, 0))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_APPLIED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID)
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_APPLIED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID)
, 0))
, 0
, 0
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_CREDITED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID)
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_CREDITED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID)
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_ADJUSTED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID)
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_APPLIED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID)
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_APPLIED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.FEE_TRX_ID)
, 0))
, 0
, 0
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_CREDITED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.FEE_TRX_ID)
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_CREDITED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.FEE_TRX_ID)
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_ADJUSTED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.FEE_TRX_ID)
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL((SELECT PSA.AMOUNT_APPLIED
FROM AR_PAYMENT_SCHEDULES_ALL PSA
WHERE PSA.ORG_ID = LOAN.ORG_ID
AND PSA.CUSTOMER_TRX_ID = AM.FEE_TRX_ID)
, 0))
, NVL(AM.PRINCIPAL_BALANCE
, 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 = AM.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL
AND ((AM1.PAYMENT_NUMBER = AM.PAYMENT_NUMBER
AND AM1.AMORTIZATION_SCHEDULE_ID <= AM.AMORTIZATION_SCHEDULE_ID) OR (AM1.PAYMENT_NUMBER < AM.PAYMENT_NUMBER))
AND NVL(AM.PHASE
, 'TERM') = NVL(AM1.PHASE
, 'TERM'))
, NVL(AM.PRINCIPAL_BALANCE
, LOAN.FUNDED_AMOUNT)
, (NVL(AM.PRINCIPAL_AMOUNT
, 0) + NVL(AM.INTEREST_AMOUNT
, 0) + NVL(AM.FEE_AMOUNT
, 0))
, (SELECT SUM(NVL(AMOUNT_DUE_ORIGINAL
, 0))
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID))
, (SELECT SUM(NVL(AMOUNT_DUE_REMAINING
, 0))
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID))
, (SELECT MAX(REC.APPLY_DATE)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC
WHERE 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.APPLICATION_TYPE = 'CASH')
, DECODE(AM.REVERSED_FLAG
, NULL
, 'N'
, AM.REVERSED_FLAG)
, LOK1.MEANING
, AM.CREATION_DATE
, AM.PARENT_AMORTIZATION_ID
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, LOK2.MEANING
, DECODE(NVL(AM.REVERSED_FLAG
, 'N')
, 'Y'
, 'CREDIT_DISABLED'
, DECODE(DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, 'MANUAL'
, DECODE((SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL)
, 0) - NVL(SUM(AMOUNT_DUE_REMAINING)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID))
, 0
, 'CREDIT_ENABLED'
, 'CREDIT_DISABLED' )
, DECODE(LOAN.LAST_PAYMENT_NUMBER
, AM.PAYMENT_NUMBER
, DECODE((SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL)
, 0) - NVL(SUM(AMOUNT_DUE_REMAINING)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID))
, 0
, 'CREDIT_ENABLED'
, 'CREDIT_DISABLED' )
, 'CREDIT_DISABLED' ) ) )
, DECODE(NVL(AM.REVERSED_FLAG
, 'N')
, 'Y'
, 'REBILL_DISABLED'
, DECODE(DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, 'MANUAL'
, 'REBILL_DISABLED'
, DECODE(LOAN.LAST_PAYMENT_NUMBER
, AM.PAYMENT_NUMBER
, DECODE((SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL)
, 0) - NVL(SUM(AMOUNT_DUE_REMAINING)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID))
, 0
, 'REBILL_ENABLED'
, 'REBILL_DISABLED' )
, 'REBILL_DISABLED' ) ) )
, NVL((SELECT AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID = AM.PRINCIPAL_TRX_ID)
, 0)
, NVL((SELECT AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID = AM.INTEREST_TRX_ID)
, 0)
, NVL((SELECT AMOUNT_DUE_REMAINING
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE ORG_ID = LOAN.ORG_ID
AND CUSTOMER_TRX_ID = AM.FEE_TRX_ID)
, 0)
, AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID
, AM.STATEMENT_XML
, LOAN.LOAN_STATUS
, LOAN.ORG_ID
, LOAN.LOAN_CURRENCY
, LOAN.LEGAL_ENTITY_ID
, NVL(AM.PHASE
, 'TERM')
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, FND_LOOKUPS LOK1
, LNS_LOOKUPS LOK2
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND AM.REAMORTIZATION_AMOUNT IS NULL
AND LOK1.LOOKUP_TYPE = 'YES_NO'
AND LOK1.LOOKUP_CODE = NVL(AM.REVERSED_FLAG
, 'N')
AND LOK2.LOOKUP_TYPE = 'BILL_TYPE'
AND LOK2.LOOKUP_CODE = DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')