FND Design Data [Home] [Help]

View: LNS_AMORTIZATION_SCHEDULES_V

Product: LNS - Loans
Description: This view contains billing and payment history information for every loan in the system
Implementation/DBA Data: ViewAPPS.LNS_AMORTIZATION_SCHEDULES_V
View Text

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')

Columns

Name
LOAN_ID
AMORTIZATION_SCHEDULE_ID
PAYMENT_NUMBER
DUE_DATE
PRINCIPAL_AMOUNT
INTEREST_AMOUNT
FEE_AMOUNT
PRIN_CASH
PRIN_LOAN_PMT_CREDIT
PRIN_NET_CREDIT
PRIN_OTHER_CREDIT
PRIN_CREDIT
PRIN_ADJ
PRINCIPAL_PAID
INT_CASH
INT_LOAN_PMT_CREDIT
INT_NET_CREDIT
INT_OTHER_CREDIT
INT_CREDIT
INT_ADJ
INTEREST_PAID
FEE_CASH
FEE_LOAN_PMT_CREDIT
FEE_NET_CREDIT
FEE_OTHER_CREDIT
FEE_CREDIT
FEE_ADJ
FEE_PAID
PRINCIPAL_BALANCE
FUNDED_AMOUNT
TOTAL_BILLED_AMOUNT
TOTAL_AR_BILLED_AMOUNT
TOTAL_REMAINING_AMOUNT
LAST_APPLIED_DATE
REVERSED_CODE
REVERSED_DESC
CREATION_DATE
PARENT_AMORTIZATION_ID
BILL_TYPE_CODE
BILL_TYPE_DESC
CREDIT_ENABLED_CODE
REBILL_ENABLED_CODE
PRINCIPAL_REMAINING
INTEREST_REMAINING
FEE_REMAINING
PRINCIPAL_TRX_ID
INTEREST_TRX_ID
FEE_TRX_ID
STATEMENT_XML
LOAN_STATUS
ORG_ID
CURRENCY
LEGAL_ENTITY_ID
PHASE