VIEW: APPS.LNS_AM_SCHEDS_DTL_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, abs(nvl(psa_prin.AMOUNT_APPLIED, 0))) ,0 ,0 ,decode(am.principal_trx_id, null, 0, abs(nvl(psa_prin.AMOUNT_CREDITED, 0))) ,decode(am.principal_trx_id, null, 0, abs(nvl(psa_prin.AMOUNT_CREDITED, 0))) ,decode(am.principal_trx_id, null, 0, nvl(psa_prin.AMOUNT_ADJUSTED, 0)) ,decode(am.interest_trx_id, null, 0, abs(nvl(psa_int.AMOUNT_APPLIED, 0))) ,0 ,0 ,decode(am.interest_trx_id, null, 0, abs(nvl(psa_int.AMOUNT_CREDITED, 0))) ,decode(am.interest_trx_id, null, 0, abs(nvl(psa_int.AMOUNT_CREDITED, 0))) ,decode(am.interest_trx_id, null, 0, nvl(psa_int.AMOUNT_ADJUSTED, 0)) ,decode(am.fee_trx_id, null, 0, abs(nvl(psa_fee.AMOUNT_APPLIED, 0))) ,0 ,0 ,decode(am.fee_trx_id, null, 0, abs(nvl(psa_fee.AMOUNT_CREDITED, 0))) ,decode(am.fee_trx_id, null, 0, abs(nvl(psa_fee.AMOUNT_CREDITED, 0))) ,decode(am.fee_trx_id, null, 0, nvl(psa_fee.AMOUNT_ADJUSTED, 0)) ,(nvl(am.PRINCIPAL_AMOUNT, 0) + nvl(am.INTEREST_AMOUNT, 0) + nvl(am.FEE_AMOUNT, 0)) ,decode(am.REVERSED_FLAG, null, 'N', am.REVERSED_FLAG) ,decode(am.PARENT_AMORTIZATION_ID, null, 'SCHEDULED', 'MANUAL') ,lok2.MEANING ,decode(am.principal_trx_id, null, 0, nvl(psa_prin.AMOUNT_DUE_REMAINING, 0)) ,decode(am.interest_trx_id, null, 0, nvl(psa_int.AMOUNT_DUE_REMAINING, 0)) ,decode(am.fee_trx_id, null, 0, nvl(psa_fee.AMOUNT_DUE_REMAINING, 0)) ,nvl(am.PHASE, 'TERM') from lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,LNS_LOOKUPS lok2 ,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 am.REAMORTIZATION_AMOUNT is null and lok2.lookup_type = 'BILL_TYPE' and lok2.lookup_code = decode(am.PARENT_AMORTIZATION_ID, null, 'SCHEDULED', 'MANUAL') 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

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
, ABS(NVL(PSA_PRIN.AMOUNT_APPLIED
, 0)))
, 0
, 0
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_PRIN.AMOUNT_CREDITED
, 0)))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_PRIN.AMOUNT_CREDITED
, 0)))
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_ADJUSTED
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_INT.AMOUNT_APPLIED
, 0)))
, 0
, 0
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_INT.AMOUNT_CREDITED
, 0)))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_INT.AMOUNT_CREDITED
, 0)))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_ADJUSTED
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_FEE.AMOUNT_APPLIED
, 0)))
, 0
, 0
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_FEE.AMOUNT_CREDITED
, 0)))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, ABS(NVL(PSA_FEE.AMOUNT_CREDITED
, 0)))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_ADJUSTED
, 0))
, (NVL(AM.PRINCIPAL_AMOUNT
, 0) + NVL(AM.INTEREST_AMOUNT
, 0) + NVL(AM.FEE_AMOUNT
, 0))
, DECODE(AM.REVERSED_FLAG
, NULL
, 'N'
, AM.REVERSED_FLAG)
, DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
, LOK2.MEANING
, DECODE(AM.PRINCIPAL_TRX_ID
, NULL
, 0
, NVL(PSA_PRIN.AMOUNT_DUE_REMAINING
, 0))
, DECODE(AM.INTEREST_TRX_ID
, NULL
, 0
, NVL(PSA_INT.AMOUNT_DUE_REMAINING
, 0))
, DECODE(AM.FEE_TRX_ID
, NULL
, 0
, NVL(PSA_FEE.AMOUNT_DUE_REMAINING
, 0))
, NVL(AM.PHASE
, 'TERM')
FROM LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, LNS_LOOKUPS LOK2
, 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 AM.REAMORTIZATION_AMOUNT IS NULL
AND LOK2.LOOKUP_TYPE = 'BILL_TYPE'
AND LOK2.LOOKUP_CODE = DECODE(AM.PARENT_AMORTIZATION_ID
, NULL
, 'SCHEDULED'
, 'MANUAL')
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