University of Technology, Sydney

General Ledger Transaction Report Procedures

Table of Contents

Introduction

Requirements

Logging In

Oracle

Excel

Introduction

The General Ledger Transaction Reportallows the user in a Faculty or Division to extract on financial transactions recorded in the University’s Oracle Financials system (neo) into a Microsoft Excel worksheet for further analysis.

This manual will guide the user through the procedures in extracting this information and analysing the results.

Please direct all requests for assistance to the ITD Helpdesk at or call ext. 2222.

Requirements

Before starting Oracle General Ledger Transaction Report, the following conditions need to have been met:

  1. Oracle JInitiator has been successfully installed on the user’s computer (Contact the IT Helpdesk on ext. 2222 or e-mail if this has not yet been done);
  2. Configure Microsoft Internet Explorer as your default browser to access Oracle Financials;
  3. The user has been issued with UTS GL Inquiry nnnaccess to Oracle Financials where nnn is the Faculty or Division (The Finance User Access Form can be found on );

Logging In

Open Internet Explorer and navigate to and select the click here to login to neo button.

Alternately, you can go directly to Oracle Financials by entering in Internet Explorer. Bookmark this address to allow for easier and quicker access in future.

Oracle

In the Navigator GL Enquiry UTSnnn yyyyyscreen, selectOther → Requests

In the Find Requests screen, select the Find button

In the Requests screen, select the Submit a New Request button

In the Submit a New Request screen, select the option and press OK

In the Submit Request screen, select the dropdown box in the Name parameter

The Reports screen will open and show the list of available reports. Scroll down to the UTSC: GL Transaction Detail Excel Report and select OK

In the Parameters screen, select the dropdowns for the Starting and Ending Period, Flexfield From and To fields

In the Starting Period screen, scroll down to the starting month of the period to be reported on and select OK

In the Ending Period screen, scroll down to the ending month of the period to be reported on and select OK

In the UTS Accounting Flexfield screen, enter the account segment ranges for this report. To capture all occurrences within a segment, enter 0 as the low and z as the high values for the range.

Please note that you will need to restrict the Natural Account ranges to 40000 as the low and 99999 as the high values for this range. Failure to restrict this field will result in your report not equaling your monthly reports.

In the Parameters screen, select the OK button when all the relevant report criteria have been defined

In the Submit Request screen, select Submit button to execute this report

The Requests screen will show the status of this report. Press the Refresh Data button to update the request status until the Phase field changes to Completed.

Select the View Output button to view the report output and select the data with Edit→Select All or Ctrl-A.

Copy the highlighted ranges with Edit→Copy or Ctrl-C.

Excel

In Microsoft Excel, paste the report data with Edit Paste or Ctrl-V

Convert the data with Data → Text to Columns

In the Convert Text to Columns Wizard – Step 1 of 3screen, choose Delimitedand select the Next button

In the Convert Text to Columns Wizard – Step 2 of 3screen, choose Comma as a Delimiter and select the Next button

In the Convert Text to Columns Wizard – Step 3 of 3screen, scroll to each of the Co, Org Unit, Loc, Activity, Account, Future and Interco columns and set the Column Data Format to Text. Select the Finish button when complete.

In the converted worksheet, highlight column P and insert a new column with Insert → Columns

Title this new column Net and enter +N2-O2 as the formula. Copy this formula to the end of the worksheet.

A Pivot Table will allow the user to analyse the data in this report. Select Data → PivotTable and PivotChart Report.

In the PivotTable and PivotChart Wizard – Step 1 of 3 screen, select the Next button

In the PivotTable and PivotChart Wizard – Step 2 of 3 screen, select the Next button

In the PivotTable and PivotChart Wizard – Step 3 of 3 screen, select the Layout button

In the PivotTable and PivotChart Wizard – Layout screen drag the Org Unit and Activity fields to the Rowarea and Net field to the Dataarea. Select OK when complete.

In the PivotTable and PivotChart Wizard – Step 3 of 3 screen, select the Finish button.

The completed Excel worksheet ready for further analysis.

Page 1 of 23