COLLABORATE 14 Copyright 2014 by Kristin Piskulic Page 1

COLLABORATE Item Creation and Maintenance Made Easy With Form Personalizations, SharePoint and More4Apps Whitepaper

Kristin Piskulic Centric Group, Inc.

Abstract:

Our company recently completed a project to migrate from an old AS400 based ERP system to Oracle R12.1.3 for our largest business unit. This project encompassed moving item data to 30 inventory organizations + MSTR Org + Validation Org including over 18K items being converted. End users were overwhelmed by the number of screen touches that were needed to set-up and maintain the items at each inventory organization. By implementing various DFFs, form personalizations, a database trigger and an in-house MS SharePoint solution alongside a 3rd party tool by More4Apps we have enabled our users to take control of new item setups and item maintenance.

Items

With our conversion from the AS400 based ERP system, we had over 18,000 unique items being converted. These items were set up across our 30 inventory organizations. Items were all configured for the MAS Master organization, Validation organization and the various inventory organizations which resulted in over 80,000 item-organization setups. In the old system, each item-unit-of-measure (UOM) had a unique item number; in the new system those multiple items became 1 item with multiple UOMs. Our company decided to also implement Oracle planning and forecasting, which meant the item setup planning data at each inventory organization now mattered.

Initial Reaction by our End Users Prior to Go-Live

Our initial session with the end users over training on item setups and maintenance took place a few months prior to Go-Live. This meeting proved to be rougher than expected. Needless to say, when we showed them the screens they would have to touch and the expected number of times they would have to update a single item-organization combination, the task seemed clunky and overwhelming. Mass changes for item maintenance scared them even more. We identified the areas we needed to address with respect to the item setups and maintenance based on our configuration of inventory that would result in the most pain and the most time spent. Those areas included the following:

1. End users would have to alter both the Cost of Goods Sold and the Sales Account which is derived from the organization setup and an item product code (this is tied to our 1st, 2nd, 3rd and 5th segments of the GL string) for each item/org combination

2. Set-up as many as 8 categories for each new item (most at the Master level, a few at the organization level)

3. Enter both the UPC/GTIN 4. Maintain planner codes at the organization level 5. Freight Class code this was an attribute that is built based on the different UOMs for each item.

This string of data was extracted and built from the AS400 system and inserted with the item load for each item. These strings were complex and would be difficult to key into the system for each item.

COLLABORATE 14 Copyright 2014 by Kristin Piskulic Page 2

Examples: [832|60|FOOD ITEMS|][825|60|FOOD ITEMS|][600|60|FOOD ITEMS|][100|60|FOOD ITEMS|][EA|65|EACHES (FOOD & SHAMPOO ITEMS)|] [CS|60|FOOD ITEMS|][EA|65|EACHES (FOOD & SHAMPOO ITEMS)|]

6. Default Receiving Sub-Inventory for specific organizations 7. Mass item updates

Solution Initial Needs

After meeting with our users, we also discovered the need to streamline and simplify setting up new items. With the current migration, there were several items identified that would need to be created very soon after Go-Live and could not be brought in from the old system. The current process to request and create a new item entailed an MS Excel spreadsheet passed among several groups. It was deemed cumbersome and faulty. We needed a better process to streamline the entering of new items into Oracle while still obtaining all the information needed from each area of business.

Mass data changes and maintenance was another initial need for our company soon after Go-Live. Primarily to handle those last minute production changes.

Ideally the final solution would address both the creation and maintenance of inventory items. We needed a tool that would streamline our processes and be straight forward and easy for the end user to manage and run. The end goal for our end users meant not having to engage our software development group for item updates or item creation as well as keeping the screen touches to a minimum.

Solution Options

We looked at several different solutions from developing tools in-house, form personalizations, nightly jobs, database triggers and 3rd party solutions to ease the burden of item setups and maintenance. Our final solution(s) must be flexible and not allow bad data into the system. One of the primary goals was to allow the end user to update a lot of data without having to involve software development. Would this be an in-house developed tool or externally purchased product? There were pros/cons with both of these options.

Starting down the list of maintenance items that we identified prior, we started brainstorming options for the solution(s). Form personalizations, nightly concurrent requests, triggers are solutions we could easily develop in-house and would accomplish many of the new item maintenance updates that were needed such as the COGS and Sales Account GL strings. Adding a new required DFF for the product code upon item creation, allowed the implementation of a database trigger to automatically derive the COGS and Sales Account strings. A database trigger was also used for the creation of the categories for each new item. Forms personalization handled the validation of UPC numbers and forced the entry of several fields to uppercase when necessary. Using a new lookup based on inventory organization, we were able to create a nightly concurrent program to update the default receiving sub-inventory. And, the potentially long and cumbersome freight class string that our users really expressed concern over, would be built nightly by another concurrent request program.

The two items on our list that turned out to be the most difficult to accomplish were the mass item updates and creation of new items. One of our team members had prior experience with a custom solution to feed in item updates through use of an excel template. However, the validation and entry was subject to a lot

COLLABORATE 14 Copyright 2014 by Kristin Piskulic Page 3

of failure points. There were also issues as the tool was not flexible enough to handle different field updates easily. We still continued down the path of the home grown solution thinking it was the best way to create and update items the easiest.

It wasnt until we started digging deeper into the current business process of adding new items did we start thinking outside the box for a solution. As the complexity of the excel spreadsheet solution was growing, more thought was moving into the idea of automating the handoff of the item creation question form. The brainstorming process began and a mockup utility was created in MS Access to display the possibility of automating the item creation input to the various groups in the company before the item would be loaded into the system. This utility established a predefined workflow for each new item being requested and would notify the individual groups when it was their turn to add in the new item data relative to their area. It kept track of new item requests in an organized manner. No items would be loaded into Oracle unless all data areas for that item had been completed.

As we started getting estimates back from our development team on a utility of some sort to load and update items, we found the quotes were much higher than expected and the concern was still the flexibility of the tool. We began looking into 3rd party tools to assist in the validation and import of changes to items in Oracle. The hope was to find a ready-made solution that was flexible.

Solution Mass Item Updates and Item Creation

We found a product called Item Wizard by More4Apps to handle the item updates and initial item creation. This tool worked from MS Excel and appeared to have a very flexible user interface to allow the user to pick and choose which fields needed to be updated. It also used standard Oracle APIs and the standard Oracle validation as well as Oracle data dependencies between fields. The design solution we ended up with came from the ease of use of this tool and the fact that it works in MS Excel. After testing out this product, we knew it would be the answer to our biggest issue of mass item updates and mass item creation by our end users.

Item Wizard turned out to have a simple setup to our Oracle servers as well as no client setup needed for our end users. It integrated with the Oracle sign in and the individual user responsibilities in Oracle. It also allowed for the item updates and item creation data to be submitted to the concurrent manager via a simple click of the button. The end user would even be able to view the results of the item update/creation job from the screen in MS Excel / Item Wizard.

After working with Item Wizard, we were then able to formalize our requirements for the item creation workflow tool that we had previously created a mock up for in MS Access (for gathering information on new item setups across our groups). Now, we knew if we could get the tool to handle the updates from various groups in the company and be able to create the extract of the data in the proper MS Excel format, that we would have the solution to our biggest issue.

We ended up finalizing our workflow tool requirements and using MS Sharepoint as the tool of choice to implement it. This tool allowed any purchasing agent to initiate the addition of a new item to our inventory. Once the item was requested, it initiated a workflow by emailing the respective department upon the completion of each step of item creation information gathering. This workflow was passed between 5-6 groups for information before the item was ready to be loaded into Oracle. Once all the data was entered from each area, the item administrator was able to extract the new items in a specific MS Excel format. The extracted data is simply cut and pasted into the template created in Item Wizard and the data can then be validated prior to load. Once the data passes the validations, the job is submitted

COLLABORATE 14 Copyright 2014 by Kristin Piskulic Page 4

from Item Wizard to import the items into inventory. The end user is able to view the item import job after it has finished checking for any errors or warnings similar to the Requests form in Oracle.

Product Use Today / Examples

Today our users are managing all their own new item requests as well as the majority of the data updates with minimal development staff intervention. In addition, we found with Item Wizard, another tool came with it called Item Extension Wizard. This tool allowed us to do other item related data updates such as adding UOMs to each item or setting up customer item cross references. We have since incorporated it as well to our new item setup process.

Since we have implemented these solutions, the end users have executed multiple mass item updates using Item Wizard updating as many as 30K items. They have updated numerous categories, planning attributes, UOM updates, item cross reference updates, customer item cross reference updates, and more. The end users have created over 1000 items to date. All of these were completed without intervention of the development staff.

The combined solution encompassing our company built item creation request system alongside the Item Wizard and Item Extension Wizard has proven to be a very efficient and low maintenance solution. Currently, the majority of all mass item updates are handled using both Item Wizard / Item Extension Wizard with only a handful of users needed to make those changes.

Conclusion

Oracle eBusiness offers many options and tools to maintain the data integrity. By using various triggers, DFFs, backend processes, and form personalizations, we were able to solve many of the potentially time consuming item maintenance tasks. We found we had to be flexible and creative while always keeping the end goal in mind: Making item maintenance and creation easier for our end users. When all other options were exhausted or proved to be costly, we had to look outside of our known set of tools. We ended up finding a stronger solution by combining an in-house tool and a 3rd party tool, Item Wizard. All while streamlining and improving an existing process.

With the solutions we implemented, we saved our end users hours of frustration, hours of maintenance and gave them more ownership of their data. The solutions have proven to put the majority of the updates in the hands of the end users and out of the hands of the development staff.