ARBI Query List
Below is a list of queries designed to address AR/BI reporting needs. The queries have been sorted into categories.
Please note: If your offices would like to modify these queries to better suit your needs PLEASE save them with a different name, so that original query will not be corrupted. These are intended for use by everyone using ARBI
Either Campus Accounting Offices or Campus Departments:
1. BI_INVOICES_NOT_PRINTED - This query produces a list of all non-cancelled invoices that have not been printed. This would mean the invoice has just been created, has a BCM error, or is in hold status.
2. BI_INVOICE_CANCEL_ADJUST - This query produces a list of all invoices that have been either cancelled or adjusted.
3. BCM_ERRORS_BI - This query produces a list of all invoices containing BCM errors. These errors must be corrected prior the invoice printing or posting to the GL.
4. CUSTOMER_BALANCES_UMKC - This query produces a list of current balances for all UMKC customers. This query may be modified to fit your campus.
5. AR_ACTIVITY_DEPTID - This query produces a list of all AR activity by DeptID for a particular Business Unit.
6. AR_ACTIVITY_DEPTID_DT_RANGE - This query produces a list of all AR activity by DeptID for a particular Business Unit. Prompts for date range and you need to use the day prior to your begin date and the day after your end date to get the full range you need.
7. AR_ACTIVITY_TYPE - This query produces a list of all AR activity by Bill Type for a particular Business Unit.
8. AR_OPEN_ITEMS_BI_SPECIALIST – This query lists outstanding invoices and prompts by Billing Specialist. An alternative report would be the Aging Report located in the PS Search Options on the MIS Web Applications site.
9. AR_PAYMENT_SEARCH This query allows you to search for a payment by amount.
10. BILL_PAYMENT_SEARCH This query allows you to search for an invoice amount in the event you don’t know the invoice number.
11. PAYMENT_SEARCH_BY_CK_NUMBER This query allows to you search for a payment by entering the check number, which needs to be 15 characters starting with CK or CC.
12. PAYMENT_SEARCH_BY_CUSTID This allows you to search for all payments that have posted to the customer number.
13. ARBI_PROJECT_LOOKUP Provides the user with project end dates based on the active or inactive file in PS.
14. ARBI_WRITEOFFS Prompts for business unit and returns a list of all Write Offs and Reverse Write Offs done in PS. May sort by business unit for locating the write offs of an individual department. Query must be run in Production.
15. AR_CUSTOMER_ACCOUNT This query prompts for customer number and returns a list of invoices and OA’s sorted by group id. It is useful in identifying OA matches to invoices without having to use PS panels and also returns check numbers when the payment was directly applied. This may be beneficial when researching a customer’s payment history and needing a breakdown by invoice. Query must be run in Production.
16. ARBI_ALL_INVOICES_OPRID Prompts for Business Unit, Invoice Dates, and Operator Id. Provides the user with a list of invoices created by the operator id within the time frame specified.
17. AR_OPEN_INVOICES Provides the user with a current list of all open AR items in the system (not including on accounts or credit invoices). This can be a useful tool when searching for an invoice solely by amount.
18. AR_WORKC_LEDGER_BAL – This query is to be run monthly to determine which items remain in the BU WORKC at the end of every month. The items listed in the query results must be transferred to a campus BU at the end of every month in order for the month to close with WORKC in balance. The Ledger query identifies the amount. The Customer query identifies the customer(s). The campuses are to run these queries periodically throughout the month to keep the balance at a minimum.
19. CUSTOMER_BALANCE_BU – This query produces a list of current balances. The BU is prompted, therefore it can be used for any BU. This query is to be run in Production.
20. ARBI_CREDIT_REVIEW_BY_BILLER - Query prompts for billings specialist and date range and returns all credit invoices within that date range. Let me know when the list is updated so I can print off a copy.
21. DC_BILLING_DETAIL_ITEM_DST - query that can be used to look up the details of a BI.