Assets Entry In Oracle Fixed Assets An Automated Solution

1
Assets Entry In Oracle Fixed Assets An
Automated Solution

• Francois Strydom
• Audio Visual Services Corporation

2
Overview

• Abstract
• About AVSC
• The Oracle Mass Additions Process
• Limitations of the Mass Additions Process
• The Problem for AVSC
• The old AVSC Process

3
Overview (cont.)

• The Solution for AVSC
• Design Constraints
• Overview of the Solution
• The Solution in Detail
• Identify items that require a barcode and serial
  number
• Storing manufacturer, model and purchasing
  category details

4
Overview (cont.)

• The Solution in Detail (cont.)
• Capital Items have asset clearing account and
  asset category
• Custom receipts form
• Custom Receipts traveler
• Receiving Transaction processor
• Modified Mass Additions Create process

5
Overview (cont.)

• The Solution in Detail (cont.)
• 2 Mass Addition reports
• Oracle Alerts
• Constraints
• Lessons learned
• Results
• Questions

6
Abstract

• Does the effort of capturing Asset data into
  Oracle get you down? This presentation will show
  you how we automated our asset capturing process
  through capturing barcodes and serial numbers
  during purchase order receiving as well as
  pulling information from purchasing and payables
  into Fixed Assets.

7
About Audio Visual Services Corporation (AVSC)

• What Business Are We In?
• How Big is the Meeting Market
• AV Expenditures
• AVSC Business Lines
• Presentation Services
• AVHQ Rental Services
• AVHQ Meeting Services
• AVHQ Show Solutions

8
What Business Are We In?

• The Meeting Business
• Supplying Audio Visual Support
• Projection
• Audio
• Lighting
• Staging
• Computers
• Conference Equipment

9
How Big is the Meetings Market?
10
AV Expenditures

• Total Market for AV is 1.767 Billion

11
AVSC Has Four Business Lines

• PSAV Presentation Services
• AVHQ Rental Services
• AVHQ Meeting Services
• AVHQ Show Solutions

12
Presentation Services

• Operates in-house at over 500 hotels and resorts
• Provides audiovisual equipment and knowledgeable
  technical staff for small meetings to ballroom
  events
• Other services include business centers, cyber
  cafes and high-speed internet access

13
AVHQ Rental Services

• Located in over 30 cities across the United
  States
• Offering state-of-the-art audiovisual equipment
  to the traveling meeting planner
• Available on-site technical crew

14
AVHQ Meeting Services

• Specializes in major association conventions and
  trade shows
• Working with some of Americas most prominent
  meeting planners, operating as an extension of
  their team
• Offering speaker-ready rooms, cyber cafes,
  multi-room networks and one of the largest
  computer departments in the industry
• Awarded by Expo Readers Choice as the 2003
  Supplier of the Year

15
AVHQ Show Solutions

• Specializes in planning, producing and executing
  complex presentations and business theater events
  that require cutting-edge technology
• Our staging professionals coordinate events with
  a broad range of services, including rigging,
  concert sound, digital broadcasts, video
  projection, video walls and intelligent lighting
• If you can dream it, we can do it!

16
The Oracle Mass Additions Process

• The Mass Additions interface
• Facilitate in the process of capturing assets
• From external sources
• From Project Accounting
• From Accounts Payable

17
The Oracle Mass Additions Process (cont.)

• Creating Mass Additions from Accounts Payable
• From invoice distribution lines
• Track as asset flag
• User selects the flag on the form
• Asset natural account

18
The Oracle Mass Additions Process (cont.)

• Discount, freight and tax qualify also
• Import into Mass Additions interface through
  Create Mass Additions
• Assets accounting group process through Prepare
  Mass Additions form.

19
The Oracle Mass Additions Process (cont.)

• Information populated from AP
• Invoice number
• Line number
• Queue
• Description
• Units
• Cost
• Supplier name number

20
The Oracle Mass Additions Process (cont.)

• Information populated from AP (cont.)
• Create batch
• Create date
• Source system
• Asset clearing account
• Asset type
• Book
• Date in service

21
The Oracle Mass Additions Process (cont.)

• Functionality increases if Purchasing is used
  with AP
• If Inventory items are used POs matched to
  invoices
• Information populated from PO
• Description
• Asset Category
• PO number
• Units

22
The Oracle Mass Additions Process (cont.)

• Defaulted fields that can be changed
• Description
• Asset Category
• PO number
• Asset type
• Units
• Cost
• Date in Service

23
The Oracle Mass Additions Process (cont.)

• Information populated by user
• Employee name (optional)
• Expense account (required)
• Location (required)
• Asset number (optional)
• Tag number (barcode) (optional)
• Serial number (optional)
• Asset Key (depends on setup)
• Manufacturer (optional)
• Model (optional)

24
The Oracle Mass Additions Process (cont.)

• User can perform the following additional
  actions
• Split Mass Addition rows
• Merge Mass Addition rows
• Add cost to existing asset
• Delete Mass Additions

25
The Oracle Mass Additions Process (cont.)

• Once required information is complete change
  asset status to POST
• Run Post Mass Additions
• Creates Assets from Mass Additions

26
Limitations of the Oracle Mass Additions Process

• Decentralized companies
• Physically received and captured in Oracle at
  different locations
• Example
• Data captured twice
• Increased risk of incorrect data
• Increased risk of lost data
• Matching data is difficult

27
Limitations of the Oracle Mass Additions Process
(cont.)

• Capturing data in Mass Additions that already
  exist elsewhere in Oracle
• Example 1
• Example 2
• Shortcomings can be time consuming and expensive

28
The Problem for AVSC

• AVSC is a decentralized organization
• AVSC business needs
• Barcode (tag number)
• Serial number
• Manufacturer
• Model
• Asset Key purchasing category

29
The old AVSC process
30
The old AVSC process (cont.)

• Create PO
• Receive _at_ warehouse manual warehouse receiving
  sheet
• Fax to accounting department
• Match invoice to PO
• Create Mass Additions
• Capture data from warehouse receiving sheet into
  Mass Additions

31
The old AVSC process (cont.)

• Warehouse Receiving Sheet Contains

• PO number
• Receipt number
• Vendor
• Receipt date
• Deliver to location

• Item description
• Manufacturer
• Model
• Serial number
• Barcode

32
The old AVSC process (cont.)

• Labor intensive!
• Slow!
• Errors!

33
The Solution for AVSC

• Requirements for AVSC
• Capture barcode serial number in Oracle during
  receiving
• Populate barcodes and serial numbers in Mass
  Additions automatically
• Receipt traveler that shows barcodes and serial
  numbers by deliver-to location

34
The Solution for AVSC (cont.)

• Requirements for AVSC (cont.)
• Populate the following data automatically in Mass
  Additions
• Description
• Asset category
• Asset Key
• Manufacturer
• Model
• Expense account
• Location

35
The Solution for AVSC (cont.)

• Requirements for AVSC (cont.)
• 2 Mass Addition reports
• Completed Mass Additions
• Incomplete Mass Additions
• These two reports should replace Unposted Mass
  Additions report

36
Design Constraints

• Mechanism to identify items that should be
  bar-coded
• Item Master does not have fields for manufacturer
  and model
• Receipt Returns
• Discount arrives after asset is created

37
Design Constraints

• Mass Additions with quantity gt 1 needs to be
  split before barcode serial number can be added
• Expense account/location/asset key may not exist
  yet

38
Overview of the Solution
39
Overview of the Solution (cont.)

• Create PO normal Oracle process
• Custom receiving form
• Barcodes and serial numbers
• Custom receipts traveler
• Custom receipt returns form
• Data feeds from custom receipts through interface
  into standard receiving (concurrent request set)

40
Overview of the Solution (cont.)

• Standard invoice matched to PO (3 way matching)
• Custom Create Mass Additions process consists
  of 4 programs
• Standard Create Mass Additions from invoice
  distribution rows
• Update Mass Additions
• Add discount to asset
• Split add barcodes/serial numbers

41
Overview of the Solution (cont.)

• User review Mass Additions change status to
  POST
• Use Oracle Alerts to monitor the process

42
The Solution in Detail

• Identify items that require a barcode and serial
  number
• Storing manufacturer, model and purchasing
  category details
• Capital items have asset clearing account and
  asset category

43
The Solution in Detail (cont.)

• Custom receipts form
• Capturing barcodes and serial numbers
• Custom receipts traveler
• Custom receipt returns form
• Receiving transaction processor
• Modified mass additions create process

44
The Solution in Detail (cont.)

• Completed and Exceptions Mass Additions reports
• Monitoring of vital components (Oracle Alerts)

45
Identify items that require a barcode and serial
number
46
Identify items that require a barcode and serial
number (cont.)

• Navigate Purchasing Super User ? Items ? Master
  Items
• Capital vs. Expense Items
• Use Descriptive Flexfield next to description

47
Storing manufacturer, model and purchasing
category details

• Navigate Purchasing Super User ? Items ? Master
  Items
• Comma separated
• Category, sub-category, manufacturer, model,
  description

48
Capital items have asset clearing account and
asset category

• Navigate
• Purchasing Super User ? Items ? Master Items

49
Custom receipts form
50
Custom receipts form (cont.)

• An error message will let the user know if the PO
  cannot be received

51
Custom receipts form (cont.)
52
Custom receipts form (cont.)
53
Custom receipts form (cont.)
54
Custom receipts form (cont.)
55
Custom receipts form (cont.)
56
Custom receipts form (cont.)
57
Custom receipts form (cont.)
58
Custom receipts form (cont.)
59
Custom receipts form (cont.)
60
Custom receipt returns form

• Standard returns form return by item quantity
• Need to return specific items
• View by barcode/serial number
• Return by barcode/serial number

61
Custom receipts traveler
62
Custom receipts traveler (cont.)
63
Receiving transaction processor

• Concurrent request set
• Custom receipts ? Standard Oracle Receiving
• 1st Request
• Move data from custom tables to
  RCV_HEADERS_INTERFACE RCV_TRANSACTIONS_INTERFACE
  tables
• 2nd Request
• Move data from interface tables into standard
  RCV_ SHIPMENT_HEADERS RCV_SHIPMENT_LINES

64
Modified Mass Additions Create process

• Custom concurrent request set with the following
  requests
• Standard Oracle Create Mass Additions
• Import invoice distribution lines
• Update Mass Additions
• Merge discount rows
• Merge freight tax rows
• Update description, manufacturer model
• Insert depreciation expense account
• Insert location
• Insert asset key

65
Modified Mass Additions Create process (cont.)

• Custom concurrent request set with the following
  requests (cont.)
• Add discount to assets
• Add discount to existing assets
• Split add barcode/serial number
• Split Mass Additions rows where quantity gt 1
• Insert barcode serial number
• Submitted from AP FA

66
2 Mass Additions reports

• Completed Mass Additions report
• Mass Additions that are ready to be posted
• Exceptions Mass Additions report
• Mass Additions that are not ready to be posted

67
Monitoring of vital components (Oracle Alerts)

• Requirements for solution to work
• Correct description for items (4 commas)
• Asset clearing account for capital items
• Valid asset category for capital items
• Location must exist in FA_LOCATIONS
• Expense account must exist in GL_CODE_COMBINATIONS
  
• Asset key must exist in FA_ASSET_KEYWORDS

68
Monitoring of vital components (Oracle Alerts)
(cont.)

• If required components are not in place, process
  will fail
• Use Oracle Alerts to monitor components
• E-mail alerts

69
Monitoring of vital components (Oracle Alerts)
(cont.)
70
Monitoring of vital components (Oracle Alerts)
(cont.)
71
Monitoring of vital components (Oracle Alerts)
(cont.)
72
Constraints

• Customizations supported by Oracle
• Cannot directly update standard Oracle objects
• Use interfaces
• Develop custom forms
• Drawback to using interfaces processing in
  batch mode. Not realtime

73
Lessons learned

• Barcode duplication should be allowed after all!
  but only when the order is un-received.

74
Results

• Streamlined asset creation process
• Saving time and money
• User function is not to capture data anymore
• Barcode scanners (keyboard emulators) zero
  keystrokes in the warehouse!
• No more user errors or lost information
• Barcodes and serial numbers are all correct!

75
Questions

• Questions?