University of Missouri

Useful ARBI queries

This page provides a list of useful ARBI related queries.

Important: If your offices would like to modify these queries to better suit your needs please save them with a different name, so that the original query will not be modified. These are intended for use by everyone using ARBI.

  1. AR_ACTIVITY_DEPTID – This query produces a list of all AR activity by DeptID for a particular Business Unit.
  2. AR_ACTIVITY_TYPE – This query produces a list of all AR activity by Bill Type for a particular Business Unit.
  3. AR_CUSTOMER_ACCOUNT – This query prompts for customer number and returns a list of invoices and OAs 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 for a breakdown by invoice. This query must be run in Production.
  4. AR_OPEN_INVOICES – This query 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.
  5. 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 FIN Search Options on the Administrative Web Applications website.
  6. AR_OPEN_ONACCOUNT_ITEMS – This query prompts for a Business Unit and lists items with an OA prefix not in a closed status.
  7. AR_PAYMENT_SEARCH – This query allows you to search for a payment by amount.
  8. 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 Business Unit 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.
  9. ARBI_ALL_INVOICES_OPRID – This query prompts for Business Unit, Invoice Dates, and Operator ID and provides the user with a list of invoices created by the Operator ID within the time frame specified.
  10. ARBI_JOURNAL_LOOKUP – This query prompts you for the journal number for an ARBI transaction, e.g., AR0# or BI0 #, to find out what invoice or reference numbers are included in that journal number.
  11. ARBI_PROJECT_LOOKUP – This query provides the user with project end dates based on the active or inactive file in PS.
  12. ARBI_WRITEOFFS – This query prompts for Business Unit and returns a list of all write-offs and reverse write-offs done in PS. You may sort by Business Unit for locating the write-offs of an individual department. This query must be run in Production.
  13. BCM_ERRORS_BI – This query produces a list of all invoices containing BCM errors. These errors must be corrected prior to the invoice printing or posting to the GL.
  14. BI_INV_OPERID – This query prompts for an invoice number and will return the Operator ID for an invoice. (This would be the creator or the last person to update an invoice.)
  15. BI_INVOICE_CANCEL_ADJUST – This query produces a list of all invoices that have either been cancelled or adjusted.
  16. BI_INVOICES_NOT_PRINTED – This query produces a list of all non-cancelled invoices that have not been printed. This could mean the invoice has just been created, has a BCM error, or is in hold status. A prompt is used to select a particular Business Unit.
  17. BILL_PAYMENT_SEARCH – This query allows you to search for an invoice amount in the event you do not know the invoice number.
  18. CUSTOMER_BALANCE_BU – This query produces a list of current balances. The Business Unit is prompted; therefore it can be used for any Business Unit. This query must be run in Production.
  19. CUSTOMER_BALANCES_UMKC – This query produces a list of current balances for all UMKC customers. This query may be modified to fit your campus.
  20. CUSTOMER_CREDIT_BALANCES – This query produces a list of customers with credit balances. This would assist with refund processing.
  21. CUSTOMER_LIST_ACTIVE – This query pulls up all active customers for ARBI.
  22. PAYMENT_SEARCH_BY_CK_NUMBER – This query allows to you to search for a payment by entering the check number, which needs to be 15 characters starting with CK or CC.
  23. PAYMENT_SEARCH_BY_CUSTID – This query allows you to search for all payments that have posted to the customer number.