Running the Print Expense Report Connected Query BFS_EXPN_RPT

Purpose: Use this document as a resource on how to run the BFS_EXPN_RPT Expense Report Connected Query which can be used for auditing purposes.

Audience: Expenses Admins, Accounting Staff and Auditors

You must have at least one of these local college managed security roles:

  • ZD_DS_QRY_EXPENSES

If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.

You need to know the Expense Report ID that you want to print before you run the query as there are no look up fields, only a Report ID field.

The BFS_EXPN_RPT Expense Report Connected Query is a Print Expense Report Connected query that contains important information that you may need for auditing purposes. It contains the following information:

Expense Report Header information: Report ID, Report Description, Entered by User, Total Employee Expenses, Employee Name, Employee ID, Creation Date, Non-Reimbursable Expenses, Reference#, Business Purpose, Department, Report Status, Amount Due to Employee, Travel Auth ID, Total Auth Amount, Cash Advance ID, Cash Advance Amount.

Payment Information: Date, Doc ID, Advice Combo, Doc Seq, Payment ID Ref, Payment ID, Pay Cycle, Pay Cycle Seq Nbr, Payment Method, Amount.

Expense Lines Details: Expense Date, Line#, Expense Type, Status, Description, Location, Payment Type, Amount, Account Details (Chartfield Values), Exception Comments: Per Diem, No Receipt, Old.

Expense Approval History: Action, Role, Name, Date/Time, Comments(if any).

Expense Notes: Notes, Name, Role, Action Date/Time.

Expense Report Header Attachments: File Name, Description, Date/Time Stamp.

Expense Report Line Attachments: File Name, Description, Line Nbr, Date/Time Stamp.

Travel Authorization Information: Travel Auth ID, Travel Dates, Location, Status, Total Amount, Projected Expenses Information (Line#, Date, Expense Type, Description, Location, Amount).

Travel Authorization Approval History: Action, Role, Name, Date/Time, Comments.

Travel Authorization Header Attachments: File Name, Description, Date/Time Stamp.

Travel Authorization Line Attachments: File Name, Description, Line Nbr, Date/Time Stamp

Cash Advance Information: Cash Advance ID, Submit Dt, Purpose, Source, Description, Amount Requested, Amount Applied.

Navigation: Reporting Tools > BI Publisher > BIP Query Report Scheduler

  1. The Query Report Scheduler search page displays.
  2. If you have run this process or report in the past, select the Find an Existing Value tab to enter an existing Run Control ID and select the Search button. If this is the first time running this process or report, select the Add a New Value tab to create a new Run Control ID.
  3. The Query Report Scheduler Report Definition page displays.
  4. In the Data Source Type drop-down, select Connected Query.
  5. In the Report Name field, enter or search for BFS_EXPN_RPT.
  6. The pop up window Prompt for Query CQFS_EX_EXPENSE_REPORT_P will display. Enter your Unit and enter or search for the Report ID of the Expense Report that you want to print. Select the OK button.
  7. The pop up window Prompt for Query CQFS_EX_EXPENSE_REPORT_2 will display. Enter your Unit and select the OK button.
  8. The pop up window Prompt for Query CQFS_EX_EXPENSE_REPORT_5 will display. Enter your Unit and select the OK button.
  9. The pop up window Prompt for Query CQFS_EX_EXPENSE_REPORT_9 will display. Enter your Unit and select the OK button.
  10. The pop up window Prompt for Query CQFS_EX_EXPENSE_REPORT_10 will display. Enter your Unit and select the OK button.
  11. The pop up window Prompt for Query CQFS_EX_EXPENSE_REPORT_11 will display. Enter your Unit and select the OK button.
  12. Select the Save button.
  13. Select the Run button to begin the launch of the process in the Process Scheduler.
  14. Select the OK button on the Process Scheduler Request pop-up window to launch the process.
  15. Select the Process Monitor link to view the process execution status.
  16. When the Run Status = "Success" and Distribution Status = "Posted", the process execution is complete and ready to be viewed.
  17. Select the Report Manager link to open the Report Manager page.
  18. On the Report Manager page, select the report link: BFS_EXPN_RPT.pdf to open the Report Index page.
  19. On the Report Index page, under the File List, select the report name: BFS_EXPN_RPT.pdf
  20. The enhanced Expense Report will appear as a 2-page document that you can download and save to a local or network drive.
Query Report Scheduler page with Connected Query and Report Name fields populated according to the instructions. The Query Parameters are shown and the run button is highlighted.

As these documents contain both Names and EMPLIDs they may NOT be sent via email unless encrypted.

  1. The Expense Report Header Attachments section may contain one or more active links for attached documents in the ctcLink system.
  2. Click each document link to open the document(s) to save for the auditor in addition to the enhanced Expense Report if the auditor does not have access to the system.
Expense Report Line Attachements page with the File Name field highlighted.
  1. Below is the ctcLink page accessed when clicking the linked attachment document referenced in the Expense Report above.
  2. In this example, the attachments are at the line level, so you need to click on the Expense Details (top right) blue hyperlinked text to open the Line details page. Alternatively, if the attachments are not at the Line level, you can click the Attachments link from the main Expense Report window to open the Expense Report Attachments pop-up window. Click the document links to open each attachment and download those for the auditor to view.
Line Details page with the Expense Details link highlighted.
  1. On the Expense Details page, look for the paperclip icon that does not have a green plus sign, this means that this line has one or more attachments. Click on the paperclip icon to open the Expense Line Attachments Window to download the attachment(s) for the auditor to view.
Expense Details page with the paperclip icon column highlighted.
Expense Line Attachments page with the File Name column highlighted.

In the Query, you can also select on the blue hyperlink text for the Travel Authorization attachments as well. The instructions are similar to the ER instructions above.

As a reminder, these documents contain both Names and EMPLIDs they may NOT be sent via email unless encrypted.

To print this query for a different Expense Report, navigate back to the Query Report Scheduler page, select the already created Run Control ID and click the Update Parameters link to enter a different Report ID.

Image of a generated expense report.

Video Tutorial

The video below demonstrates the process actions described in the steps listed above. There is no audio included with this video. Select the play button to start the video.

Video Tutorial via Panopto

View the external link to Running the Print Expense Report Connected Query BFS_EXPN_RPT. This link will open in a new tab/window.

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.