Account Receivables
Below table lists that are involved in AR Invoices, Receipts (Cash and Receivable Transactions)
| AR Invoices | Receipts |
|---|---|
| RA_CUSTOMER_TRX_ALL | AR_CASH_RECEIPT_HISTORY_ALL |
| RA_CUSTOMER_TRX_LINES_ALL | AR_CASH_RECEIPTS_ALL |
| AR_DISTRIBUTIONS_ALL | AR_RECEIVABLE_APPLICATIONS_ALL |
Queries to join AR Invoices & Receipts
Query to get the CASH transactions
SELECT AR_DISTRIBUTIONS_ALL.LINE_ID , AR_DISTRIBUTIONS_ALL.SOURCE_ID , AR_DISTRIBUTIONS_ALL.SOURCE_TABLE , AR_DISTRIBUTIONS_ALL.SOURCE_TYPE , AR_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID CCID , AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_DR , AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_CR , AR_DISTRIBUTIONS_ALL.ORG_ID , AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID , AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID AS APP_ID , AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_CUSTOMER_TRX_ID , HR_ALL_ORGANIZATION_UNITS_VL.NAME AS BU_NAME , AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_ID , AR_CASH_RECEIPTS_ALL.STATUS , AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER , AR_CASH_RECEIPTS_ALL.TYPE , AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER , AR_CASH_RECEIPTS_ALL.CUSTOMER_SITE_USE_ID , RA_CUSTOMER_TRX_ALL.TRX_NUMBER , 'AR_DISTRIBUTIONS_ALL' SOURCE_DISTRIBUTION_TYPE , AR_CASH_RECEIPTS_ALL.RECEIPT_METHOD_ID FROM AR_DISTRIBUTIONS_ALL AR_DISTRIBUTIONS_ALL , AR_CASH_RECEIPT_HISTORY_ALL AR_CASH_RECEIPT_HISTORY_ALL , HR_ALL_ORGANIZATION_UNITS_VL HR_ALL_ORGANIZATION_UNITS_VL , AR_CASH_RECEIPTS_ALL AR_CASH_RECEIPTS_ALL , AR_RECEIVABLE_APPLICATIONS_ALL AR_RECEIVABLE_APPLICATIONS_ALL , RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL WHERE 1=1 AND AR_DISTRIBUTIONS_ALL.SOURCE_TABLE = 'CRH' AND AR_DISTRIBUTIONS_ALL.SOURCE_ID = AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID AND AR_DISTRIBUTIONS_ALL.ORG_ID = HR_ALL_ORGANIZATION_UNITS_VL.ORGANIZATION_ID AND AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID AND AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID = AR_RECEIVABLE_APPLICATIONS_ALL.CASH_RECEIPT_ID AND AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_CUSTOMER_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID Query to get the Receivable transactions SELECT AR_DISTRIBUTIONS_ALL.LINE_ID , AR_DISTRIBUTIONS_ALL.SOURCE_ID , AR_DISTRIBUTIONS_ALL.SOURCE_TABLE , AR_DISTRIBUTIONS_ALL.SOURCE_TYPE , AR_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID CCID , AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_DR , AR_DISTRIBUTIONS_ALL.ACCTD_AMOUNT_CR , AR_DISTRIBUTIONS_ALL.ORG_ID , AR_DISTRIBUTIONS_ALL.THIRD_PARTY_ID , AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID APP_ID , AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_CUSTOMER_TRX_ID , HR_ALL_ORGANIZATION_UNITS_VL.NAME AS BU_NAME , AR_RECEIVABLE_APPLICATIONS_ALL.CASH_RECEIPT_ID , AR_CASH_RECEIPTS_ALL.STATUS , AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER , AR_CASH_RECEIPTS_ALL.TYPE , AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER , AR_CASH_RECEIPTS_ALL.CUSTOMER_SITE_USE_ID , RA_CUSTOMER_TRX_ALL.TRX_NUMBER , 'AR_DISTRIBUTIONS_ALL' SOURCE_DISTRIBUTION_TYPE , AR_CASH_RECEIPTS_ALL.RECEIPT_METHOD_ID FROM AR_DISTRIBUTIONS_ALL AR_DISTRIBUTIONS_ALL , AR_RECEIVABLE_APPLICATIONS_ALL AR_RECEIVABLE_APPLICATIONS_ALL , HR_ALL_ORGANIZATION_UNITS_VL HR_ALL_ORGANIZATION_UNITS_VL , AR_CASH_RECEIPTS_ALL AR_CASH_RECEIPTS_ALL , RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_ALL WHERE 1=1 AND AR_DISTRIBUTIONS_ALL.SOURCE_TABLE = 'RA' AND AR_DISTRIBUTIONS_ALL.SOURCE_ID = AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID AND AR_DISTRIBUTIONS_ALL.ORG_ID = HR_ALL_ORGANIZATION_UNITS_VL.ORGANIZATION_ID AND AR_RECEIVABLE_APPLICATIONS_ALL.CASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID AND AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_CUSTOMER_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
Comments
Post a Comment