VIEW: APPS.OKL_BATCH_RECEIPTS_SUMMARY_UV

Source

View Text - Preformatted

SELECT TCR.CHECK_NUMBER RECEIPT_NUMBER, TCR.AMOUNT RECEIPT_AMOUNT, HP.PARTY_NAME CUSTOMER_NAME, HCA.ACCOUNT_NUMBER, HP.PARTY_ID CUSTOMER_ID, TRA.ILE_ID CUST_ACCOUNT_ID, KHR.CONTRACT_NUMBER, KHR.ID CONTRACT_ID, NULL INVOICE_NUMBER, NULL INVOICE_ID, TCB.ID BATCH_ID, TCR.ID RECEIPT_ID, TCR.CURRENCY_CODE RECEIPT_CURRENCY, TCR.DATE_EFFECTIVE RECEIPT_DATE, TCR.RCPT_STATUS_CODE, NULL INVOICE_TYPE FROM OKL_TRX_CSH_BATCH_B TCB, OKL_TRX_CSH_RECEIPT_V TCR, OKL_TXL_RCPT_APPS_V TRA, HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, OKC_K_HEADERS_B KHR WHERE TCB.ID = TCR.BTC_ID AND TCR.ID = TRA.RCT_ID_DETAILS AND TRA.KHR_ID = KHR.ID AND HP.PARTY_ID = HCA.PARTY_ID AND HCA.CUST_ACCOUNT_ID = TRA.ILE_ID AND TRA.KHR_ID IS NOT NULL UNION SELECT TCR.CHECK_NUMBER RECEIPT_NUMBER, TCR.AMOUNT RECEIPT_AMOUNT, HP.PARTY_NAME CUSTOMER_NAME, HCA.ACCOUNT_NUMBER, HP.PARTY_ID CUSTOMER_ID, TRA.ILE_ID CUST_ACCOUNT_ID, NULL CONTRACT_NUMBER, NULL CONTRACT_ID, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER, TRA.CNR_ID INVOICE_ID, TCB.ID BATCH_ID, TCR.ID RECEIPT_ID, TCR.CURRENCY_CODE RECEIPT_CURRENCY, TCR.DATE_EFFECTIVE RECEIPT_DATE, TCR.RCPT_STATUS_CODE, 'CONS' INVOICE_TYPE FROM OKL_TRX_CSH_BATCH_B TCB, OKL_TRX_CSH_RECEIPT_V TCR, OKL_TXL_RCPT_APPS_V TRA, HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, OKL_CNSLD_AR_HDRS_B CNR WHERE TCB.ID = TCR.BTC_ID AND TCR.ID = TRA.RCT_ID_DETAILS AND TRA.CNR_ID = CNR.ID AND HP.PARTY_ID = HCA.PARTY_ID AND HCA.CUST_ACCOUNT_ID = TRA.ILE_ID AND TRA.CNR_ID IS NOT NULL UNION SELECT TCR.CHECK_NUMBER RECEIPT_NUMBER, TCR.AMOUNT RECEIPT_AMOUNT, HP.PARTY_NAME CUSTOMER_NAME, HCA.ACCOUNT_NUMBER, HP.PARTY_ID CUSTOMER_ID, TRA.ILE_ID CUST_ACCOUNT_ID, NULL CONTRACT_NUMBER, NULL CONTRACT_ID, TRX.TRX_NUMBER INVOICE_NUMBER, TRX.CUSTOMER_TRX_ID INVOICE_ID, TCB.ID BATCH_ID, TCR.ID RECEIPT_ID, TCR.CURRENCY_CODE RECEIPT_CURRENCY, TCR.DATE_EFFECTIVE RECEIPT_DATE, TCR.RCPT_STATUS_CODE, 'AR' INVOICE_TYPE FROM OKL_TRX_CSH_BATCH_B TCB, OKL_TRX_CSH_RECEIPT_V TCR, OKL_TXL_RCPT_APPS_V TRA, HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, RA_CUSTOMER_TRX_ALL TRX WHERE TCB.ID = TCR.BTC_ID AND TCR.ID = TRA.RCT_ID_DETAILS AND TRA.AR_INVOICE_ID = TRX.CUSTOMER_TRX_ID AND HP.PARTY_ID = HCA.PARTY_ID AND HCA.CUST_ACCOUNT_ID = TRA.ILE_ID AND TRA.AR_INVOICE_ID IS NOT NULL

View Text - HTML Formatted

SELECT TCR.CHECK_NUMBER RECEIPT_NUMBER
, TCR.AMOUNT RECEIPT_AMOUNT
, HP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER
, HP.PARTY_ID CUSTOMER_ID
, TRA.ILE_ID CUST_ACCOUNT_ID
, KHR.CONTRACT_NUMBER
, KHR.ID CONTRACT_ID
, NULL INVOICE_NUMBER
, NULL INVOICE_ID
, TCB.ID BATCH_ID
, TCR.ID RECEIPT_ID
, TCR.CURRENCY_CODE RECEIPT_CURRENCY
, TCR.DATE_EFFECTIVE RECEIPT_DATE
, TCR.RCPT_STATUS_CODE
, NULL INVOICE_TYPE
FROM OKL_TRX_CSH_BATCH_B TCB
, OKL_TRX_CSH_RECEIPT_V TCR
, OKL_TXL_RCPT_APPS_V TRA
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, OKC_K_HEADERS_B KHR
WHERE TCB.ID = TCR.BTC_ID
AND TCR.ID = TRA.RCT_ID_DETAILS
AND TRA.KHR_ID = KHR.ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = TRA.ILE_ID
AND TRA.KHR_ID IS NOT NULL UNION SELECT TCR.CHECK_NUMBER RECEIPT_NUMBER
, TCR.AMOUNT RECEIPT_AMOUNT
, HP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER
, HP.PARTY_ID CUSTOMER_ID
, TRA.ILE_ID CUST_ACCOUNT_ID
, NULL CONTRACT_NUMBER
, NULL CONTRACT_ID
, CNR.CONSOLIDATED_INVOICE_NUMBER INVOICE_NUMBER
, TRA.CNR_ID INVOICE_ID
, TCB.ID BATCH_ID
, TCR.ID RECEIPT_ID
, TCR.CURRENCY_CODE RECEIPT_CURRENCY
, TCR.DATE_EFFECTIVE RECEIPT_DATE
, TCR.RCPT_STATUS_CODE
, 'CONS' INVOICE_TYPE
FROM OKL_TRX_CSH_BATCH_B TCB
, OKL_TRX_CSH_RECEIPT_V TCR
, OKL_TXL_RCPT_APPS_V TRA
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, OKL_CNSLD_AR_HDRS_B CNR
WHERE TCB.ID = TCR.BTC_ID
AND TCR.ID = TRA.RCT_ID_DETAILS
AND TRA.CNR_ID = CNR.ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = TRA.ILE_ID
AND TRA.CNR_ID IS NOT NULL UNION SELECT TCR.CHECK_NUMBER RECEIPT_NUMBER
, TCR.AMOUNT RECEIPT_AMOUNT
, HP.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER
, HP.PARTY_ID CUSTOMER_ID
, TRA.ILE_ID CUST_ACCOUNT_ID
, NULL CONTRACT_NUMBER
, NULL CONTRACT_ID
, TRX.TRX_NUMBER INVOICE_NUMBER
, TRX.CUSTOMER_TRX_ID INVOICE_ID
, TCB.ID BATCH_ID
, TCR.ID RECEIPT_ID
, TCR.CURRENCY_CODE RECEIPT_CURRENCY
, TCR.DATE_EFFECTIVE RECEIPT_DATE
, TCR.RCPT_STATUS_CODE
, 'AR' INVOICE_TYPE
FROM OKL_TRX_CSH_BATCH_B TCB
, OKL_TRX_CSH_RECEIPT_V TCR
, OKL_TXL_RCPT_APPS_V TRA
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, RA_CUSTOMER_TRX_ALL TRX
WHERE TCB.ID = TCR.BTC_ID
AND TCR.ID = TRA.RCT_ID_DETAILS
AND TRA.AR_INVOICE_ID = TRX.CUSTOMER_TRX_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCA.CUST_ACCOUNT_ID = TRA.ILE_ID
AND TRA.AR_INVOICE_ID IS NOT NULL