Apps To Fusion

Executive Summary

Generally every business has certain processes which are not available in Oracle E-Business suite. Without these processes business cannot function hence implementation team has to build bolt-on applications/custom applications in Oracle Apps instance to support these data needs. Traditionally, most of the Oracle Apps consultants use Common Lookup or Profiles to store this additional information if data needs are small and simple. Common Lookups can be used for mapping activities like Legacy system code gets mapped to Oracle code etc. Profiles can used for user specific/ responsibility specific/ Application specific data needs.

If data needs become complicated the general suggestion is to create a Custom Database Table which will store data in required format and structure. When any new database table comes into technical design of a solution it comes with additional customization needs i.e. to provide front end screen (form) for data manipulation activities, build reports on custom tables and incase of Mass updates additional technical components can be needed for mass loading of data. It is quite possible that these unsupported business processes needed data for certain type of Oracle ERP transactions.

This white paper talks about using Oracle Quality Module to avoid any custom tables for additional data needs. This white paper describes the Oracle Quality Modules functional setups, technical details, its unconventional use and a case study.

Oracle Quality Brief Description

Oracle Quality is most neglected module in Oracle E-Business Suite. As a part of seeded functionality Quality Module integrates Inventory, Receiving, Order Management, Work in Process, Purchasing, Workflow, Alerts and Application Object Library. Collection plans and collection elements constitute the data collection mechanism of Oracle Quality.

Oracle Quality Functional Setups

Quality Module Components:

Quality Module setups include following components:

• Collection Element Types: There are various seeded types e.g.
  • Attributes- Used to stored characteristics of any object
  • Variable- Used to numeric measurements
  • Reference Information. There reference to common objects in Oracle Applications like PO,SO, WIP etc.
• Collection Elements- We can define unlimited number of collection elements, which you can add to collection plans to determine what quality results data will be collected and tracked.
• Collection Plan Types - This is used to categories collection plans. This is the structure of dataset which needs to be captured for a given type of transaction. The transaction can be Material Transaction, Receiving Transaction, WIP Job creation etc.

Quality plans are generally setup for inventory organizations. Quality module can act as repository of all the additional transactional data which is not captured in Module specific Oracle Tables. It is better to setup Collection plans for Item Master Organization though nothing stops us from setting Collection plans for Child Organization. The main reason for setting collection plans for Item Master Organization is we cannot use same collection plan name in different organizations which negates the feature of setting up of collection plans for different child organization.

Before we can collect the data with Oracle Quality, one must define a collection plan. You begin by defining collection elements which define the characteristics of the product, process that you want to record monitor and analyze. The data entry can be validated by static values or SQL statement or creating specifications. These validations come into effect while doing data entry using Quality insert/update screens as well as using plsql APIs like QA_RESULTS_API or Quality Data collection import concurrent program.

Standard setups/seeded plans can be used to collect data during various transaction processing. After performing data manipulations of quality plans we can launch alerts, start workflows, send email notifications through quality plan setup.

Oracle Quality Technical details

Collection elements and collection plans for major part of Quality module setups. Collection elements details can be access view QA_CHARS_V while collection plan details can be accessed using QA_PLAN_CHARS_V. This view gives details of which collection elements are included in a given plan.

All the Quality transaction data is stored in QA_RESULTS table. The data is segregated by Quality Collection Plans. QA_RESULTS table has references to Item, Purchase Order, Sales Order, WIP job, Receiving Transactions etc. Other than reference columns QA_RESULTS table has 100 VARCHAR2 columns from CHARACTER1-CHARACTER100. Based on quality plan definition, these character columns store different data.

When ever a new plan is created, Oracle Applications creates two views automatically with names Q_<plan name>_V and Q_<plan name>_IV. View Q_<plan name>_V is based on QA_RESULTS table while Q_<plan name>_IV is based on QA_RESULTS_INTERFACE table which is used for importing huge data in Quality. QA_RESULTS table stores data for all quality plans and data is segregated by PLAN_ID column. The Character1-100 columns are mapped to quality plan elements based on plan structure. Since Oracle Apps automatically creates respective views for each plan, a developer doesn't have to maintain mapping between Character fields and data elements. View columns have plan element names which are mapped to character1 to 100 columns. Views have only those columns which are present in plan and not all 100 qa_results columns.

For data manipulation activities, Quality module dynamically create form which has labels same as plan element. Quality module provides one form function for Entering new records for a given Quality Plan and one form function for Updating existing data.

Quality Module usage in business processes not available in Oracle E-Business Suite

If you closely take a look at the design or structure of quality module, Collection Elements are similar to the columns in database table and Collection plan is similar to a table structure which has data elements.

Every Oracle E-business Suite implementation project needs some custom tables for storing some master data which cannot be stored using seeded oracle functionality. Instead of creating custom tables, we can define collection elements and collection plans in quality module. Quality module dynamically creates views based on quality plans. Quality module has dynamic forms for data entry and data updates.

E.g. One Quality plan PRODUCT_INFO has two custom elements COLOUR, SIZE and ITEM (Reference element). After creating this plan, two views Q_PRODUCT_INFO_V and Q_PRODUCT_INFO_IV get created with columns COLOUR pointing to CHARACTER1 and SIZE pointing to CHARACTER2 of QA_RESULTS table.

Using Oracle Quality module following things can be achieved:

• Build Quality Plans (Mostly for Item Master Organization) with structure same as Custom Table
• During setup enter valid values/SQL statements for each quality plan data element to validate data entry (through API as well as Quality forms)
• Write custom code which use newly created views related to quality plans Q_<plan name>_V
• Use APIs like QA_RESULTS_API and QA_VALIDATIONS_API to perform DML operations against QA_RESULTS table which is the repository of all transactional data for quality plans
• Use forms personalization to display this quality plan data in Oracle Apps seeded screens like Purchase Order screen, Order Entry screen etc wherever needed.

To summarize if we use quality module in this manner, we can avoid building of custom tables which are important for some business logic, data entry screen on these custom tables.

Case Study- Generator Manufacturing Company- Soft Options Customization

Problem Definition:

Generator Company manufactures Alternators/Generators which are needed in Gensets. An alternator has 60 different properties which are accepted as input from Customer thru' Oracle Configurator. These generator properties need to be stored at order line level and finally get printed on final assembly document, machine label and test certificate.

Solution:

1. Create collection element type - CGT_SOFT_OPTIONS
2. Create collection sixty elements which represent properties of a Generator e.g. Frame, KVA, KW, Temp_Rise, Winding, Core Length etc.
3. Create collection elements like SOFT_OPTION_TYPE, CUSTOMER, SERIAL_NO
4. Create collection plan type -CGT_SOFT_OPTIONS
5. Create collection plan - SOFT_OPTIONS
6. Other than 60 properties add following collection elements
   1. SOFT_OPTION_TYPE (Custom element)- This can have values This can have values
      i. SO- Sales Order Related Soft Option data
      ii. PO-Purchase Order Related Soft Option data
      iii. WIP - WIP Job related Soft Option data
      iv. ITEM - Default Item related Soft Option Data
      v. CI - Customer Item related Soft Option Data
   2. Purchase Order Number - Reference Info
   3. Sales Order Number - Reference Info
   4. Sales Order Line - Reference Info
   5. WIP_JOB_NAME (Custom Element)
   6. SO_CUSTOMER_ITEM (Custom Element)
7. This creates view Q_SOFT_OPTIONS_V which help accessing SOFT_OPTIONS quality plan data.
8. Custom plsql API which used QA_RESULTS_API and QA_RESULT_VALIDATION_API doing read/write operations of quality data for SOFT_OPTIONS quality plan.
9. Create forms personalization for screens like Purchase Order, Sales Order, Discrete Jobs, Item master to call Update Quality plan screen to display respective soft options.

For this client, Oracle Apps E-business Suite 11i implementation, Quality Module is used extensively for storing following Quality plan data

Conclusion

To conclude this white paper, I would like stress on using Quality Module for any additional data needs which are requirement by business but not provided by Oracle Applications. This helps reduce the development time for building custom tables, related user interfaces (forms and reports). Maintenance becomes easy Quality Module allows addition of new elements dynamically. It provides seeded APIs, Forms, and Reports which are based in Quality Tables. We can enhance monitoring of this quality transaction data by using firing Action Alerts and workflows from Quality Plans.

References:

1. Oracle Apps E-Business Suite 11.5.10 Documentation Library
2. Oracle E-Business Manufacturing & Supply Chain Management - Oracle Press

Annexure I: Quality Setups Screen Shots

Define Collection Element Types:

Responsibility: Quality
Menu: Setup--> Collection Element Types

Define Collection Elements:

Responsibility: Quality
Menu: Setup --> Collection Elements

Define Collection Elements Values:

Responsibility: Quality
Menu: Setup -->Collection Elements (Value Button)

Define Collection Plan Types

Responsibility: Quality
Menu: Setup -> Collection Plan Types

Define Collection Plans

Responsibility: Quality
Menu: Setup ' Collection Plans

Dynamically Created Quality Screen for data entry

Oracle dynamically creates screen for data entry for INSERT/UPDATE/DELETE operations based on collection plan structure

Responsibility: Quality
Menu: Results : Entry->Update Quality Results

Comments (26)

Subscribe to this comment's feed

Write comment

Name

Email

Title

Comment

smaller | bigger

Subscribe via email (Registered users only)

Write the displayed characters

Add Comment