1. Sign into the Peoplesoft reporting instance using your SSO ID and password (To verify that you are in reporting, make sure the URL reads: https://fsrpt.umsystem.edu/psp/rpt/?cmd=login as you will not be able to access most queries in the production instance.)
2. From the main menu, select Reporting Tools > Query Viewer:
When prompted, type in the name of your query and any other required information (i.e. Business Unit, Journal ID, etc.). You can run your query in Excel or HTML format.
Below is a list of queries designed to address AR/BI reporting needs. The queries have been sorted into categories. The queries can be run in RPT unless otherwise designated.
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. AR_ACTIVITY_DEPTID This query produces a list of all AR activity by DeptID for a particular Business Unit.
5. 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.
6. AR_ACTIVITY_TYPE This query produces a list of all AR activity by Bill Type for a particular Business Unit.
7. 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.
8. AR_PAYMENT_SEARCH This query allows you to search for a payment by amount.
9. BILL_PAYMENT_SEARCH This query allows you to search for an invoice amount in the event you don’t know the invoice number.
10. 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.
11. PAYMENT_SEARCH_BY_CUSTID This allows you to search for all payments that have posted to the customer number.
12. ARBI_PROJECT_LOOKUP Provides the user with project end dates based on the active or inactive file in PS.
13. 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.
14. AR_CUSTOMER_ACCOUNT (PRD) 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.
15. 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.
16. 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.
17. AR_WORKC_LEDGER_BAL (PRD) 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.
18. 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.
19. 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.
20. DC_BILLING_DETAIL_ITEM_DST (PRD/RPT) Query that can be used to look up the details of a BI.
