Oracle Physical Inventory Training Materials

All departments are required to perform a complete inventory of their capital equipment once per year. This process is an important control activity designed to safeguard the assets of the University. In addition, when all assets are correctly recorded in the University’s record keeping system, the accuracy of the University’s financial statements is enhanced.

Oracle fixed asset Parent Departments are used to group related departments for physical inventory purposes. The Parent Department drives the issuance of inventory worksheets, inventory group names, as well as the Oracle alerts related to inventory. Each Fixed Asset Parent Department will have designated representatives who will be responsible for completing the capital equipment physical inventory and ensuring the certification form is submitted timely.

Inventory representatives will access the new process via a custom responsibility created in Oracle. The new responsibility will be used to download equipment inventory data to an Excel spreadsheet using an Application Desktop Integrator – Oracle (ADI) process. Inventory data will be updated as applicable and designation will be made that items were inventoried. When inventory representatives have completed necessary updates to the worksheets they will upload the (ADI) worksheet through Oracle and send a completed certification form to Property Accounting. Property Accounting will review completed inventory and only reach out to departments if there are any questions.

Unfortunately at this time, this ADI process is not able to be used with Mac computers. A user will need to sign into Oracle using a PC, download the worksheet, save file on network accessible drive or email the file to themselves to work on. File will need to be uploaded using PC under the users Oracle sign-on.

FA Physical Inventory User Responsibility

• Responsibility has access only to the fixed asset parent departments for which the user is designated as DIR (Department Inventory Representative) or DIR2. A user may have more than one fixed asset parent department assigned.
• Only one Oracle function contained in this responsibility, ‘Download FA Physical Inventory Data Interface’.
• Users needing this responsibility request via the Request changes to RIT Physical Inventory Department Inventory Representatives (DIR) form.

Fixed Asset Parent Departments

Format of Fixed Asset Parent Department Numbers:

• Range of F0000 – F9999
• Definition of number:
  • F = fixed asset inventory parent department
  • First three digits = First three digits of department area
  • Last digit = designates if there is more than one person responsible for department area (1, 2, 3 etc.)

Contact Information

If you have Property Accounting questions or requests, please contact the RIT Service Center:

• Visiting the RIT Service Center Self-Service Portal
• Calling 585-475-5000

Inventory Cycles

Prior to downloading the inventory spread sheet, users will need to verify certain settings in Microsoft Excel. The following steps will allow for Microsoft Excel to function with the ADI spreadsheet.

• Step 1:
  • Open Excel > Open Blank Workbook
  • Select File > Options
  • Select Trust Center > Trust Center Settings Select Macro Settings Make sure the box for Trust access to the VBA project object model is checked
• Step 2:
  • Open Excel > Open Blank Workbook
  • Select File > Options > Trust Centre > Trust Centre Settings > Protected View
  • Uncheck all options

Opening of Inventory

DIR and DIR2 receive email from Oracle Workflow indicating their respective inventory groups are available to perform physical inventory at beginning of cycle. Email contains list of the inventory parent department(s). No response needed to email.

Options for Managing Worksheets

• Complete inventory in single session of Oracle log-in after downloading:
  • Do not close out Excel inventory template file.
• Complete inventory at later time after downloading:
  1. Save ADI template to network drive where department inventory files are saved
     • Make changes in worksheet as inventory is taken
     • Prepare to upload to Oracle when complete
  2. Print out original Excel file and make notes off-line on paper:
     • Use original Excel template make changes resulting from physical inventory
     • Prepare to upload changes in Oracle

Potential Errors When Downloading

• Oracle error message or blank Excel file
  • The specific fixed asset parent department entered is not assigned to user as either DIR or DIR2
    • Retry if number was entered incorrectly
    • Contact property accounting to determine which user is assigned to specific fixed asset parent department, request change if applicable
  • The specific fixed asset parent department did not have assets, will result in a blank worksheet.
    • Retry if number was entered incorrectly
    • Contact property accounting to verify no assets are under parent department

Saving Excel ADI Worksheet for Use

• Select ‘File’ -> ‘Save As’ in Excel inventory ADI template.
• Browse to network location you will save file.
• Name file and change ‘save as type’ to ‘Excel Macro-Enabled Workbook(*.xlsm)’ from drop down.

Using Excel ADI Worksheet

• Utilize the parent department group inventory worksheet and locate the assets listed; noting correct location, description, manufacturer, model number, serial number and tag number. Columns shaded in blue are read-only and cannot be modified.
• Do not add rows to worksheet.
• If any editable fields are either blank or have ‘00000’, enter correct information to update in Oracle.
• Department number changes, tag number changes, splitting and merging of assets, and addition of assets will be addressed in next section. These functions cannot be performed on worksheet.
• Inventory Excel worksheet columns:
  • Upl - flag icon will appear if line is updated (do not edit this cell manually).
  • Inventory Group - parent department name plus date inventory was opened (read-only).
  • Parent - inventory parent department number as described on page 2 (read-only).
  • Entity - RIT entity (read-only).
  • Department - Department number which the asset is currently recorded under (read-only). Change in department numbers will be addressed separate from this Excel file.
  • Building - Code for building location. Will be validated upon import, if unknown, follow process below:
    • Look up valid building code by accessing Oracle -> RIT-Assets – Reports & Inquiry-> Financial Information -> Find Assets -> click on ‘Location’ field’s list of values button.
    • Click on list of values button for ‘building’, then browse for correct code.
  • Room - Code for room location. Will be validated upon import, if unknown, follow process below:
    • Look up valid room code by accessing Oracle -> RIT-Assets – Reports & Inquiry-> Financial Information -> Find Assets -> click on ‘Location’ field’s list of values button.
    • Click on list of values button for ‘room’, then browse for correct code.
  • Asset # - Preassigned Oracle asset number (read-only).
  • Description - Current asset description listed in Oracle. Make necessary changes.
  • Category - Assigned asset type category in Oracle (read-only).
  • Manufacturer - Current manufacturer name listed in Oracle. Make necessary changes.
  • Model - Current model number listed in Oracle. Make necessary changes.
  • Serial # - Current model number listed in Oracle. Make necessary changes.
  • Tag # - Current RIT inventory asset tag number in Oracle (read-only). Submit tag# changes to Property Accounting via the form Ask a Property Control general question
  • PO # - Purchase order number associated with original asset purchase (read-only).
  • Cost – Current cost listed in Oracle Assets, based on original purchase (read-only).
  • Retire – Use this column to indicate if an asset is retired by changing to ‘Y’ (default is ‘N’).
  • Inventoried - User must take action on this field. When individual asset has been addressed (either located or retired) user types ‘Y’ in this field. If the field is not set to ‘Y’ upon upload, Oracle will not recognize the asset as being inventoried and will trigger past due inventory alerts.

Departments are required to certify their annual capital equipment physical inventory. After fully complete inventory worksheets are successfully uploaded to Oracle:

• An automated Oracle capital equipment physical inventory certification email will be sent to the supervisor on record for the inventory department parent.
• Supervisor will need to respond to Oracle alert to approve inventory.
• Property Accounting will be notified of supervisor's response via Oracle (no emails need to be sent).
  • Property Accounting will reach out to DIR’s with any questions on inventory.
  • Department’s inventories will be closed when no questions/issues exist.
• No formal notification is given to departments that inventory is closed.

Users should retain an original copy of downloaded Excel ADI inventory worksheet as well as the updated worksheet uploaded to system indicating successful upload and certification form. The Excel files do not need to be emailed to Property Accounting. Typed notes on the far right side of the template i.e. ‘Message’ column, will disappear upon upload so it is advised to copy the file/tab before uploading for archiving your notes. The copy is useful for sending to Property Accounting to request manual changes (see ‘Requested Changes for Read-Only Fields’ section below).

As in previous capital equipment physical inventory process, email alerts will be sent to users, but now through Oracle Workflow. Alerts will flow as follows:

• Contact name updates – DIR, DIR2, supervisor and fiscal representative. Sent 2 weeks before cycle opening.
• Inventory opened – to DIR, DIR2, supervisor and fiscal representative
• Due in 1 week – to DIR, DIR2, supervisor and fiscal representative
• Overdue 1 week - DIR, DIR2, supervisor and fiscal representative
• Overdue 2 weeks - DIR, DIR2, supervisor, fiscal representative and manager accounting and internal controls

As noted in the Using Excel ADI Worksheet section, certain fields are read-only. Changes are not allowed via the worksheet due to the complexity in Oracle functionality. Departments can submit change requests via the RIT Service Center to Property Accounting for the following fields using the following forms:

• Department - If an asset has been transferred to new department; provide the following information:
  • Asset number
  • Tag number
  • Old department number
  • New department number
  • New Building and room location if not already changed on inventory worksheet
• Tag Number - If change in original tag number or providing missing tag number; provide the following information:
  • Asset number
  • New or revised tag number
• Splitting Single Asset into Multiple - If an individual asset is actually two or more combined and department wants asset separated; provide the following information:
  • Asset or tag number
  • Identify how asset should be split (qty./cost per unit)
• Merge Several Assets into One - If many assets should be combined into one asset; provide the following information:
  • Asset or tag numbers of assets to be combined
  • Identify how asset should be combined (qty./cost per unit)
  • Final tag number that will be used for combined asset
• Addition of Asset Not on Inventory Worksheet - Capital asset that is not on department listing, these cannot be added to worksheet by department. Provide the following information in request:
  • Purchase order number, or
  • Purchase information from procurement card, Digital Den/Photo Shop
  • Donor if gift-in-kind and approximate date received