VIEW: APPS.LNS_REC_ACT_CASH_CM_V

Source

View Text - Preformatted

SELECT distinct loan.loan_id , am.AMORTIZATION_SCHEDULE_ID , am.PAYMENT_NUMBER , am.DUE_DATE , trx_psa.customer_trx_id , trx_psa.payment_schedule_id , aml.line_type , lok2.meaning ,decode(rec.applied_customer_trx_line_id, null, 'Entire Transaction', lines.DESCRIPTION) , trx_psa.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 , 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_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_psa.customer_trx_id = aml.cust_trx_id AND trx_psa.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 ((rec.applied_customer_trx_line_id IS NULL) or (aml.cust_trx_line_id = rec.applied_customer_trx_line_id AND rec.applied_customer_trx_line_id is not null))

View Text - HTML Formatted

SELECT DISTINCT LOAN.LOAN_ID
, AM.AMORTIZATION_SCHEDULE_ID
, AM.PAYMENT_NUMBER
, AM.DUE_DATE
, TRX_PSA.CUSTOMER_TRX_ID
, TRX_PSA.PAYMENT_SCHEDULE_ID
, AML.LINE_TYPE
, LOK2.MEANING
, DECODE(REC.APPLIED_CUSTOMER_TRX_LINE_ID
, NULL
, 'ENTIRE TRANSACTION'
, LINES.DESCRIPTION)
, TRX_PSA.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
, 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_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_PSA.CUSTOMER_TRX_ID = AML.CUST_TRX_ID
AND TRX_PSA.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 ((REC.APPLIED_CUSTOMER_TRX_LINE_ID IS NULL) OR (AML.CUST_TRX_LINE_ID = REC.APPLIED_CUSTOMER_TRX_LINE_ID
AND REC.APPLIED_CUSTOMER_TRX_LINE_ID IS NOT NULL))