VIEW: APPS.LNS_RECEIVABLE_ACTIVITIES_V

Source

View Text - Preformatted

SELECT loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,am.DUE_DATE ,trx.customer_trx_id ,trx_psa.payment_schedule_id ,aml.CUST_TRX_LINE_ID ,aml.line_type ,lok2.meaning ,decode(rec.applied_customer_trx_line_id, null, 'Entire Transaction', lines.DESCRIPTION) ,trx.trx_number ,arl_cla.lookup_code ,arl_cla.meaning || decode(trx1.reason_code, null, '', ' - ' || arl_cla1.meaning) ,-rec.amount_applied ,rec_psa.trx_number ,rec.apply_date ,loan.funded_amount + (select nvl(sum(-rec1.amount_applied), 0) from ar_receivable_applications_all rec1, LNS_AMORTIZATION_SCHEDS am1 WHERE rec1.application_type = 'CASH' and rec1.apply_date <= rec.apply_date and rec1.APPLIED_CUSTOMER_TRX_ID = am1.principal_trx_id and rec1.org_id = loan.org_id and am1.loan_id = loan.loan_id and am1.principal_trx_id is not null and nvl(am1.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM')) ,rec.RECEIVABLE_APPLICATION_ID ,rec.CASH_RECEIPT_ID ,to_number(null) ,rec.creation_date ,rec.created_by ,rec.last_update_login ,rec.last_update_date ,rec.last_updated_by ,trx_psa.INVOICE_CURRENCY_CODE ,decode(rec.application_type, 'CASH', nvl(-rec.AMOUNT_APPLIED_FROM, -rec.amount_applied), to_number(null)) ,decode(rec.application_type, 'CASH', rec_psa.INVOICE_CURRENCY_CODE, null) ,decode(rec.application_type, 'CASH', nvl(rec.TRANS_TO_RECEIPT_RATE, 1), to_number(null)) ,loan.ORG_ID ,loan.LEGAL_ENTITY_ID ,nvl(am.PHASE, 'TERM') from RA_CUSTOMER_TRX_ALL trx ,RA_CUSTOMER_TRX_LINES_ALL lines ,lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,LNS_AMORTIZATION_LINES aml ,ar_payment_schedules_all trx_psa ,ar_payment_schedules_all rec_psa ,ar_receivable_applications_all rec ,LNS_LOOKUPS lok2 ,ar_lookups arl_cla ,ar_lookups arl_cla1 ,RA_CUSTOMER_TRX_ALL trx1 where loan.loan_id = am.loan_id and aml.loan_id = loan.loan_id and aml.AMORTIZATION_SCHEDULE_ID = am.AMORTIZATION_SCHEDULE_ID and trx.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and trx.customer_trx_id = aml.cust_trx_id and trx.customer_trx_id = trx_psa.customer_trx_id and trx.customer_trx_id = rec.APPLIED_CUSTOMER_TRX_ID and rec.CUSTOMER_TRX_ID = trx1.CUSTOMER_TRX_ID(+) and rec.payment_schedule_id = rec_psa.payment_schedule_id and lines.customer_trx_line_id = aml.cust_trx_line_id and rec.display = 'Y' and lok2.lookup_type = 'PAYMENT_APPLICATION_TYPE' and lok2.lookup_code = aml.line_type and arl_cla.lookup_code = rec_psa.class and arl_cla.lookup_type = 'INV/CM' and arl_cla1.lookup_code(+) = trx1.reason_code and arl_cla1.lookup_type(+) = 'CREDIT_MEMO_REASON' and (aml.cust_trx_line_id = decode( rec.applied_customer_trx_line_id, null, (select cust_trx_line_id from LNS_AMORTIZATION_LINES where loan_id = loan.loan_id and AMORTIZATION_SCHEDULE_ID = am.AMORTIZATION_SCHEDULE_ID and line_type = aml.line_type and rownum = 1), rec.applied_customer_trx_line_id)) UNION ALL select loan.loan_id ,am.AMORTIZATION_SCHEDULE_ID ,am.PAYMENT_NUMBER ,am.DUE_DATE ,trx.customer_trx_id ,trx_psa.payment_schedule_id ,aml.CUST_TRX_LINE_ID ,aml.line_type ,lok2.meaning ,decode(adj.customer_trx_line_id, null, 'Entire Transaction', lines.DESCRIPTION) ,trx.trx_number ,arl_cla.lookup_code ,arl_cla.meaning || ' - ' || rec_trx.name ,adj.amount ,adj.adjustment_number ,adj.apply_date ,loan.funded_amount + (select nvl(sum(-rec1.amount_applied), 0) from ar_receivable_applications_all rec1, LNS_AMORTIZATION_SCHEDS am1 where rec1.application_type = 'CASH' and rec1.apply_date <= adj.apply_date and rec1.APPLIED_CUSTOMER_TRX_ID = am1.principal_trx_id and rec1.org_id = loan.org_id and am1.loan_id = loan.loan_id and am1.principal_trx_id is not null and nvl(am1.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM')) ,to_number(null) ,to_number(null) ,adj.adjustment_id ,adj.creation_date ,adj.created_by ,adj.last_update_login ,adj.last_update_date ,adj.last_updated_by ,trx_psa.INVOICE_CURRENCY_CODE ,to_number(null) ,null ,to_number(null) ,loan.ORG_ID ,loan.LEGAL_ENTITY_ID ,nvl(am.PHASE, 'TERM') from RA_CUSTOMER_TRX_ALL trx ,RA_CUSTOMER_TRX_LINES_ALL lines ,ar_payment_schedules_all trx_psa ,ar_adjustments_all adj ,AR_RECEIVABLES_TRX_ALL rec_trx ,lns_loan_headers_all loan ,LNS_AMORTIZATION_SCHEDS am ,LNS_AMORTIZATION_LINES aml ,LNS_LOOKUPS lok2 ,ar_lookups arl_cla where loan.loan_id = am.loan_id and aml.loan_id = loan.loan_id and aml.AMORTIZATION_SCHEDULE_ID = am.AMORTIZATION_SCHEDULE_ID and trx.customer_trx_id in (am.principal_trx_id, am.interest_trx_id, am.fee_trx_id) and trx.customer_trx_id = aml.cust_trx_id and trx.customer_trx_id = trx_psa.customer_trx_id and trx.customer_trx_id = lines.customer_trx_id and trx_psa.payment_schedule_id = adj.payment_schedule_id and lines.customer_trx_line_id = aml.cust_trx_line_id and adj.RECEIVABLES_TRX_ID = rec_trx.RECEIVABLES_TRX_ID and adj.ORG_ID = rec_trx.ORG_ID and lok2.lookup_type = 'PAYMENT_APPLICATION_TYPE' and lok2.lookup_code = aml.line_type and arl_cla.lookup_code = 'ADJUSTMENT' and arl_cla.lookup_type = 'MISC_PHRASES' and adj.postable = 'Y' and adj.status = 'A' and (aml.cust_trx_line_id = decode( adj.customer_trx_line_id, null, (select cust_trx_line_id from LNS_AMORTIZATION_LINES where loan_id = loan.loan_id and AMORTIZATION_SCHEDULE_ID = am.AMORTIZATION_SCHEDULE_ID and line_type = aml.line_type and rownum = 1), adj.customer_trx_line_id))

View Text - HTML Formatted

SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, AM.DUE_DATE
, TRX.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, AML.CUST_TRX_LINE_ID
, AML.LINE_TYPE
, LOK2.MEANING
, DECODE(REC.APPLIED_CUSTOMER_TRX_LINE_ID
, NULL
, 'ENTIRE TRANSACTION'
, LINES.DESCRIPTION)
, TRX.TRX_NUMBER
, ARL_CLA.LOOKUP_CODE
, ARL_CLA.MEANING || DECODE(TRX1.REASON_CODE
, NULL
, ''
, ' - ' || ARL_CLA1.MEANING)
, -REC.AMOUNT_APPLIED
, REC_PSA.TRX_NUMBER
, REC.APPLY_DATE
, LOAN.FUNDED_AMOUNT + (SELECT NVL(SUM(-REC1.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE REC1.APPLICATION_TYPE = 'CASH'
AND REC1.APPLY_DATE <= REC.APPLY_DATE
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND REC1.ORG_ID = LOAN.ORG_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, REC.RECEIVABLE_APPLICATION_ID
, REC.CASH_RECEIPT_ID
, TO_NUMBER(NULL)
, REC.CREATION_DATE
, REC.CREATED_BY
, REC.LAST_UPDATE_LOGIN
, REC.LAST_UPDATE_DATE
, REC.LAST_UPDATED_BY
, TRX_PSA.INVOICE_CURRENCY_CODE
, DECODE(REC.APPLICATION_TYPE
, 'CASH'
, NVL(-REC.AMOUNT_APPLIED_FROM
, -REC.AMOUNT_APPLIED)
, TO_NUMBER(NULL))
, DECODE(REC.APPLICATION_TYPE
, 'CASH'
, REC_PSA.INVOICE_CURRENCY_CODE
, NULL)
, DECODE(REC.APPLICATION_TYPE
, 'CASH'
, NVL(REC.TRANS_TO_RECEIPT_RATE
, 1)
, TO_NUMBER(NULL))
, LOAN.ORG_ID
, LOAN.LEGAL_ENTITY_ID
, NVL(AM.PHASE
, 'TERM')
FROM RA_CUSTOMER_TRX_ALL TRX
, RA_CUSTOMER_TRX_LINES_ALL LINES
, LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, LNS_AMORTIZATION_LINES AML
, AR_PAYMENT_SCHEDULES_ALL TRX_PSA
, AR_PAYMENT_SCHEDULES_ALL REC_PSA
, AR_RECEIVABLE_APPLICATIONS_ALL REC
, LNS_LOOKUPS LOK2
, AR_LOOKUPS ARL_CLA
, AR_LOOKUPS ARL_CLA1
, RA_CUSTOMER_TRX_ALL TRX1
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND AML.LOAN_ID = LOAN.LOAN_ID
AND AML.AMORTIZATION_SCHEDULE_ID = AM.AMORTIZATION_SCHEDULE_ID
AND TRX.CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND TRX.CUSTOMER_TRX_ID = AML.CUST_TRX_ID
AND TRX.CUSTOMER_TRX_ID = TRX_PSA.CUSTOMER_TRX_ID
AND TRX.CUSTOMER_TRX_ID = REC.APPLIED_CUSTOMER_TRX_ID
AND REC.CUSTOMER_TRX_ID = TRX1.CUSTOMER_TRX_ID(+)
AND REC.PAYMENT_SCHEDULE_ID = REC_PSA.PAYMENT_SCHEDULE_ID
AND LINES.CUSTOMER_TRX_LINE_ID = AML.CUST_TRX_LINE_ID
AND REC.DISPLAY = 'Y'
AND LOK2.LOOKUP_TYPE = 'PAYMENT_APPLICATION_TYPE'
AND LOK2.LOOKUP_CODE = AML.LINE_TYPE
AND ARL_CLA.LOOKUP_CODE = REC_PSA.CLASS
AND ARL_CLA.LOOKUP_TYPE = 'INV/CM'
AND ARL_CLA1.LOOKUP_CODE(+) = TRX1.REASON_CODE
AND ARL_CLA1.LOOKUP_TYPE(+) = 'CREDIT_MEMO_REASON'
AND (AML.CUST_TRX_LINE_ID = DECODE( REC.APPLIED_CUSTOMER_TRX_LINE_ID
, NULL
, (SELECT CUST_TRX_LINE_ID
FROM LNS_AMORTIZATION_LINES
WHERE LOAN_ID = LOAN.LOAN_ID
AND AMORTIZATION_SCHEDULE_ID = AM.AMORTIZATION_SCHEDULE_ID
AND LINE_TYPE = AML.LINE_TYPE
AND ROWNUM = 1)
, REC.APPLIED_CUSTOMER_TRX_LINE_ID)) UNION ALL SELECT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, AM.DUE_DATE
, TRX.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, AML.CUST_TRX_LINE_ID
, AML.LINE_TYPE
, LOK2.MEANING
, DECODE(ADJ.CUSTOMER_TRX_LINE_ID
, NULL
, 'ENTIRE TRANSACTION'
, LINES.DESCRIPTION)
, TRX.TRX_NUMBER
, ARL_CLA.LOOKUP_CODE
, ARL_CLA.MEANING || ' - ' || REC_TRX.NAME
, ADJ.AMOUNT
, ADJ.ADJUSTMENT_NUMBER
, ADJ.APPLY_DATE
, LOAN.FUNDED_AMOUNT + (SELECT NVL(SUM(-REC1.AMOUNT_APPLIED)
, 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL REC1
, LNS_AMORTIZATION_SCHEDS AM1
WHERE REC1.APPLICATION_TYPE = 'CASH'
AND REC1.APPLY_DATE <= ADJ.APPLY_DATE
AND REC1.APPLIED_CUSTOMER_TRX_ID = AM1.PRINCIPAL_TRX_ID
AND REC1.ORG_ID = LOAN.ORG_ID
AND AM1.LOAN_ID = LOAN.LOAN_ID
AND AM1.PRINCIPAL_TRX_ID IS NOT NULL
AND NVL(AM1.PHASE
, 'TERM') = NVL(LOAN.CURRENT_PHASE
, 'TERM'))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ADJ.ADJUSTMENT_ID
, ADJ.CREATION_DATE
, ADJ.CREATED_BY
, ADJ.LAST_UPDATE_LOGIN
, ADJ.LAST_UPDATE_DATE
, ADJ.LAST_UPDATED_BY
, TRX_PSA.INVOICE_CURRENCY_CODE
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, LOAN.ORG_ID
, LOAN.LEGAL_ENTITY_ID
, NVL(AM.PHASE
, 'TERM')
FROM RA_CUSTOMER_TRX_ALL TRX
, RA_CUSTOMER_TRX_LINES_ALL LINES
, AR_PAYMENT_SCHEDULES_ALL TRX_PSA
, AR_ADJUSTMENTS_ALL ADJ
, AR_RECEIVABLES_TRX_ALL REC_TRX
, LNS_LOAN_HEADERS_ALL LOAN
, LNS_AMORTIZATION_SCHEDS AM
, LNS_AMORTIZATION_LINES AML
, LNS_LOOKUPS LOK2
, AR_LOOKUPS ARL_CLA
WHERE LOAN.LOAN_ID = AM.LOAN_ID
AND AML.LOAN_ID = LOAN.LOAN_ID
AND AML.AMORTIZATION_SCHEDULE_ID = AM.AMORTIZATION_SCHEDULE_ID
AND TRX.CUSTOMER_TRX_ID IN (AM.PRINCIPAL_TRX_ID
, AM.INTEREST_TRX_ID
, AM.FEE_TRX_ID)
AND TRX.CUSTOMER_TRX_ID = AML.CUST_TRX_ID
AND TRX.CUSTOMER_TRX_ID = TRX_PSA.CUSTOMER_TRX_ID
AND TRX.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID
AND TRX_PSA.PAYMENT_SCHEDULE_ID = ADJ.PAYMENT_SCHEDULE_ID
AND LINES.CUSTOMER_TRX_LINE_ID = AML.CUST_TRX_LINE_ID
AND ADJ.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID
AND ADJ.ORG_ID = REC_TRX.ORG_ID
AND LOK2.LOOKUP_TYPE = 'PAYMENT_APPLICATION_TYPE'
AND LOK2.LOOKUP_CODE = AML.LINE_TYPE
AND ARL_CLA.LOOKUP_CODE = 'ADJUSTMENT'
AND ARL_CLA.LOOKUP_TYPE = 'MISC_PHRASES'
AND ADJ.POSTABLE = 'Y'
AND ADJ.STATUS = 'A'
AND (AML.CUST_TRX_LINE_ID = DECODE( ADJ.CUSTOMER_TRX_LINE_ID
, NULL
, (SELECT CUST_TRX_LINE_ID
FROM LNS_AMORTIZATION_LINES
WHERE LOAN_ID = LOAN.LOAN_ID
AND AMORTIZATION_SCHEDULE_ID = AM.AMORTIZATION_SCHEDULE_ID
AND LINE_TYPE = AML.LINE_TYPE
AND ROWNUM = 1)
, ADJ.CUSTOMER_TRX_LINE_ID))