[Home] [Help]
SELECT OKPH.PARTY_ID PARTY_ID, OKPH.CUSTOMER_ID CUSTOMER_ID, OKPH.CUSTOMER_NAME CUSTOMER_NAME, OKPH.CURRENCY_CODE CURRENCY_CODE, OKPH.LINE_NAME LINE_NAME, OKPH.CUSTOMER_NUMBER CUSTOMER_NUMBER, OKPH.CONSOLIDATED_INVOICE_ID CONSOLIDATED_INVOICE_ID, OKPH.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER, OKPH.CONSOLIDATED_LINE_NUMBER CONSOLIDATED_LINE_NUMBER, OKPH.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID, OKPH.RECEIVABLES_INVOICE_NUMBER RECEIVABLES_INVOICE_NUMBER, OKPH.REFERENCE_NUMBER REFERENCE_NUMBER, OKPH.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID, OKPH.CASH_RECEIPT_ID CASH_RECEIPT_ID, OKPH.CONTRACT_ID CONTRACT_ID, OKPH.CONTRACT_NUMBER CONTRACT_NUMBER, OKPH.CONTRACT_LINE_ID CONTRACT_LINE_ID, OKPH.STREAM_ID STREAM_ID, OKPH.STREAM_TYPE_ID STREAM_TYPE_ID, OKPH.STREAM_NAME STREAM_NAME, OKPH.DUE_DATE DUE_DATE, OKPH.APPLY_DATE APPLY_DATE, OKPH.AMOUNT_APPLIED AMOUNT_APPLIED, OKPH.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL, OKPH.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING, OKPH.CLASS CLASS, OKPH.STATUS STATUS, OKPH.CREATED_BY CREATED_BY, OKPH.CREATION_DATE CREATION_DATE, OKPH.LAST_UPDATED_BY LAST_UPDATED_BY, OKPH.LAST_UPDATE_DATE LAST_UPDATE_DATE, OKPH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, IEPAY.PAYMENT_ID PAYMENT_ID, IEPAY.PAYEE_ID PAYEE_ID, IEPAY.TANGIBLE_ID TANGIBLE_ID, OKPH.payment_method_dsp PAYMENT_METHOD_DSP, IEPAY.PAYMENT_METHOD PAYMENT_METHOD, IEPAY.PAYMENT_METHOD_ID PAYMENT_METHOD_ID, IEPAY.RESOURCE_ID RESOURCE_ID, DECODE(IEPAY.RESOURCE_ID, NULL, 'OKL', REX.SOURCE_NAME) RESOURCE_NAME, CAS.CAS_ID CASE_ID, CAS.CASE_NUMBER CASE_NUMBER, NULL DELINQUENCY_ID, (select decode(count(del1.delinquency_id),0, 'CURRENT','DELINQUENT') from iex_Case_objects ico1, iex_delinquencies del1, okl_cnsld_Ar_strms_b lsm1, okc_k_headers_b okc1 where del1.transaction_id =lsm1.receivables_invoice_id and lsm1.khr_id =okc1.id and okc1.id =ico1.object_id and ico1.cas_id =CAS.CAS_ID and del1.status ='DELINQUENT') CASE_STATUS, (select decode(count(del1.delinquency_id),0, 'CURRENT','DELINQUENT') from iex_Case_objects ico1, iex_delinquencies del1, okl_cnsld_Ar_strms_b lsm1, okc_k_headers_b okc1 where del1.transaction_id =lsm1.receivables_invoice_id and lsm1.khr_id =okc1.id and okc1.id =ico1.object_id and ico1.cas_id =CAS.CAS_ID and del1.status ='DELINQUENT') CASE_STATUS_DSP, SU.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, SU.location LOCATION, SU.ADDRESS ADDRESS, SU.PARTY_SITE_NUMBER PARTY_SITE_NUMBER, OKPH.receipt_status RECEIPT_STATUS, OKPH.receipt_status_dsp RECEIPT_STATUS_DSP, OKPH.reversal_gl_date REVERSAL_GL_DATE, cas.org_id ORG_ID, MO_GLOBAL.GET_OU_NAME(cas.org_id) OPERATING_UNIT FROM IEX_OKL_PAYMENTS_V OKPH, IEX_PAYMENTS IEPAY, JTF_RS_RESOURCE_EXTNS REX, IEX_CASES_VL CAS, IEX_CASE_OBJECTS CAO, IEX_CUST_SITE_USES_V SU WHERE OKPH.REFERENCE_NUMBER = 'IEX_OKL_' || IEPAY.PAYMENT_ID(+) AND IEPAY.RESOURCE_ID = REX.RESOURCE_ID(+) and OKPH.CONTRACT_ID = CAO.OBJECT_ID and CAO.OBJECT_CODE = 'CONTRACTS' and CAO.CAS_ID = CAS.CAS_ID AND SU.CUSTOMER_SITE_USE_ID = OKPH.CUSTOMER_SITE_USE_ID
SELECT OKPH.PARTY_ID PARTY_ID
, OKPH.CUSTOMER_ID CUSTOMER_ID
, OKPH.CUSTOMER_NAME CUSTOMER_NAME
, OKPH.CURRENCY_CODE CURRENCY_CODE
, OKPH.LINE_NAME LINE_NAME
, OKPH.CUSTOMER_NUMBER CUSTOMER_NUMBER
, OKPH.CONSOLIDATED_INVOICE_ID CONSOLIDATED_INVOICE_ID
, OKPH.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, OKPH.CONSOLIDATED_LINE_NUMBER CONSOLIDATED_LINE_NUMBER
, OKPH.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, OKPH.RECEIVABLES_INVOICE_NUMBER RECEIVABLES_INVOICE_NUMBER
, OKPH.REFERENCE_NUMBER REFERENCE_NUMBER
, OKPH.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
, OKPH.CASH_RECEIPT_ID CASH_RECEIPT_ID
, OKPH.CONTRACT_ID CONTRACT_ID
, OKPH.CONTRACT_NUMBER CONTRACT_NUMBER
, OKPH.CONTRACT_LINE_ID CONTRACT_LINE_ID
, OKPH.STREAM_ID STREAM_ID
, OKPH.STREAM_TYPE_ID STREAM_TYPE_ID
, OKPH.STREAM_NAME STREAM_NAME
, OKPH.DUE_DATE DUE_DATE
, OKPH.APPLY_DATE APPLY_DATE
, OKPH.AMOUNT_APPLIED AMOUNT_APPLIED
, OKPH.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, OKPH.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, OKPH.CLASS CLASS
, OKPH.STATUS STATUS
, OKPH.CREATED_BY CREATED_BY
, OKPH.CREATION_DATE CREATION_DATE
, OKPH.LAST_UPDATED_BY LAST_UPDATED_BY
, OKPH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OKPH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, IEPAY.PAYMENT_ID PAYMENT_ID
, IEPAY.PAYEE_ID PAYEE_ID
, IEPAY.TANGIBLE_ID TANGIBLE_ID
, OKPH.PAYMENT_METHOD_DSP PAYMENT_METHOD_DSP
, IEPAY.PAYMENT_METHOD PAYMENT_METHOD
, IEPAY.PAYMENT_METHOD_ID PAYMENT_METHOD_ID
, IEPAY.RESOURCE_ID RESOURCE_ID
, DECODE(IEPAY.RESOURCE_ID
, NULL
, 'OKL'
, REX.SOURCE_NAME) RESOURCE_NAME
, CAS.CAS_ID CASE_ID
, CAS.CASE_NUMBER CASE_NUMBER
, NULL DELINQUENCY_ID
, (SELECT DECODE(COUNT(DEL1.DELINQUENCY_ID)
, 0
, 'CURRENT'
, 'DELINQUENT')
FROM IEX_CASE_OBJECTS ICO1
, IEX_DELINQUENCIES DEL1
, OKL_CNSLD_AR_STRMS_B LSM1
, OKC_K_HEADERS_B OKC1
WHERE DEL1.TRANSACTION_ID =LSM1.RECEIVABLES_INVOICE_ID
AND LSM1.KHR_ID =OKC1.ID
AND OKC1.ID =ICO1.OBJECT_ID
AND ICO1.CAS_ID =CAS.CAS_ID
AND DEL1.STATUS ='DELINQUENT') CASE_STATUS
, (SELECT DECODE(COUNT(DEL1.DELINQUENCY_ID)
, 0
, 'CURRENT'
, 'DELINQUENT')
FROM IEX_CASE_OBJECTS ICO1
, IEX_DELINQUENCIES DEL1
, OKL_CNSLD_AR_STRMS_B LSM1
, OKC_K_HEADERS_B OKC1
WHERE DEL1.TRANSACTION_ID =LSM1.RECEIVABLES_INVOICE_ID
AND LSM1.KHR_ID =OKC1.ID
AND OKC1.ID =ICO1.OBJECT_ID
AND ICO1.CAS_ID =CAS.CAS_ID
AND DEL1.STATUS ='DELINQUENT') CASE_STATUS_DSP
, SU.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, SU.LOCATION LOCATION
, SU.ADDRESS ADDRESS
, SU.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, OKPH.RECEIPT_STATUS RECEIPT_STATUS
, OKPH.RECEIPT_STATUS_DSP RECEIPT_STATUS_DSP
, OKPH.REVERSAL_GL_DATE REVERSAL_GL_DATE
, CAS.ORG_ID ORG_ID
, MO_GLOBAL.GET_OU_NAME(CAS.ORG_ID) OPERATING_UNIT
FROM IEX_OKL_PAYMENTS_V OKPH
, IEX_PAYMENTS IEPAY
, JTF_RS_RESOURCE_EXTNS REX
, IEX_CASES_VL CAS
, IEX_CASE_OBJECTS CAO
, IEX_CUST_SITE_USES_V SU
WHERE OKPH.REFERENCE_NUMBER = 'IEX_OKL_' || IEPAY.PAYMENT_ID(+)
AND IEPAY.RESOURCE_ID = REX.RESOURCE_ID(+)
AND OKPH.CONTRACT_ID = CAO.OBJECT_ID
AND CAO.OBJECT_CODE = 'CONTRACTS'
AND CAO.CAS_ID = CAS.CAS_ID
AND SU.CUSTOMER_SITE_USE_ID = OKPH.CUSTOMER_SITE_USE_ID
|
|
|
|