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:
- Create collection element type - CGT_SOFT_OPTIONS
- Create collection sixty elements which represent properties of a Generator e.g. Frame, KVA, KW, Temp_Rise, Winding, Core Length etc.
- Create collection elements like SOFT_OPTION_TYPE, CUSTOMER, SERIAL_NO
- Create collection plan type -CGT_SOFT_OPTIONS
- Create collection plan - SOFT_OPTIONS
- Other than 60 properties add following collection elements
- 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 - Purchase Order Number - Reference Info
- Sales Order Number - Reference Info
- Sales Order Line - Reference Info
- WIP_JOB_NAME (Custom Element)
- SO_CUSTOMER_ITEM (Custom Element)
- SOFT_OPTION_TYPE (Custom element)- This can have values This can have values
- This creates view Q_SOFT_OPTIONS_V which help accessing SOFT_OPTIONS quality plan data.
- 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.
- 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
Quality Plan Name | Description |
SOFT_OPTIONS | Store Machine specific characteristics for a given Item, Sales Order, Purchase Order, WIP Job, Serial No etc |
RATING_BOOK_SORT_ORDER | Rating Book Sort order is used in Configurator Extension which store sort order for various Winding, Frame, Core Length Combinations |
OPTION_PRICING_PARAMS | Used for Custom Pricing Logic based on Option Classes |
OPTION_PRICING_VALUES | Used for Custom Pricing Logic based on Option Classes |
CONNECTION_STYLE_SEARCH | Used in Configurator extension to dynamically choose columns as filter based on Connection Style selection |
ENDSOUT_WINDING_XREF | Used in Configurator extension and store ENDS_OUT and WINDING cross reference |
FRAME_TYPES | Used in Configurator extension and stores Special and Standard Frame Types |
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:
- Oracle Apps E-Business Suite 11.5.10 Documentation Library
- 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
Apps2Fusion Articles 
Comments
Regards
Sreer am.H
It is very nice article, many people not aware of Quality module.
We have avoided some customizations using this palns.
My qestion is: can we call the Collection plan - Results window from different modules, this will give more comforness to the user.
-> need not to login to Quality module
-> from the Technical point: it becomes complete customization since it is available in respective modules.
thank s & regards
Ravi Kumar thota
Technical Consultant
It is possible to call Quality Screens from other modules. Just Create new Form Functions based "Update QA Results Function" with one of the parameter as Collection Plan Name. Then using Forms Personalization s you can call this newly registered form function to display quality data. We are using such personalization s to call Quality Screen from Purchasing, WIP, Order Management and Inventory.
Reg ards,
Prasad Bhogle
Thanks for faast reply.,
but, can you give one example on the same.
thanks in advance
Ravi kumar thota
i have to admit this is an Awesome Piece Of Information. My Requirement was to Capture Detail Information For Insurance Claims while Doing the Receiving Transactions.
After the Inspection when some of material is Accepted , and some of the material Gets Rejected, Our Insurance people needed to allot the rejected Material against a lookup code (Valid for insurance, claim lower than premium, Not acceptable for insurance) ( in our case a collection plan ) and enter detail Of Claim Inspection information for the lookup code.
i think i can call a form via personlization at this inspection step and enter details for the lookup code for entering Claim Inspection results.
will research solution. anyways this is Great Info dude.
May Almighty Increase Your Tribe.
Yours lTruly
Abdul Rahman
2.Logi n as Application Developer and On Form Function entry screen query "QA - View QA Results"
3.Crea te another Form Function Entry based on same form with different function name and parameters as follows:
MODE-' UPDATE' TXN_MODE='-1' ORG_ID='109' SERF_SERVICE_PL AN_NAME='SOFT_O PTION'
Change Quality plan name and Org ID as per your project.
3.Now on PO entry screen Enable action menu using forms personalization
4.Call this newly created Form Function from this new Menu Entry using Personalization you can call this form with parameter called WHERE_CLAUSE='C HARACTER1='||'1 2121' so that you can display specific transaction/PO record.
I hope this helps
how to do it from start to finish.
Thanks
Nina
I would like to know how to control duplicate entries in enter quality results.
For ex.
In my collection plan i have element called serial number
Same serial can be called in multiple line with multiple status
how to control this duplication of particular element in next line.
Need all your help
Regards
H ussain
Can you please know how to import the data to collection plans using plsql?
If import is happening with APIs/
Let me know the API name
I have created collection element using statement, which is used in collection plan. Now I want to use this plan
in creating PAreto charts but not able to do so. Can you help ?
I am working on Oracle Quality Plan form (Enter Quality Results,Update Quality Plan) these are oracle standard Quality plan forms. These two forms are called from service contracts authoring form using zoom functionality.I am able to bring this using Zoom functionality.
But according to requirement, I have restrict this form(form has 2 blocks 1.Q_RES_HEAD 2.Q_RES.) second block which has multiple lines displayed in the standard form.
Now I need to restrict this to display one record only.How can we do that.Need to customize standard form.
I tried to do this by changing no of records displayed in Q_RES block
In Q_RES block initiaily it was NO of Records displayed = 9 in standard form
Now I changed to NO of Records displayed =1 in customized form.
But it gave me weird form. all the columns got stacked up on and looks like singe column ,I tried to drag the columns ,i need to get all columns .
Pl suggest me How can I do this.
Pl Urgent
Can We Mapped sample plan with element level intead of collection plan?
Regards
Aditya
why haven't you used specifications for the collection element values....
do you directly prefer collection plans rather than any specifications? ??
This is a very useful article. I have one requirement, wherein in OQM the NCR records should appear in the HTML version instead of Oracle form. Can this be achieved and if yes, How?
I was successful in defining one and it stops the receiving transaction for zero cost items.
But the issue is the quality Plan pops up for all transactions ( for non zero cost items also)
Any one has suggestions where we can stop the plan from stopping for all transaction and only get triggered for zero cost items?
Great article - the problem I am facing is that if I create QA plan in the Item Master org,
when I try to zoom into the QA function froma Sales Order or whichever responsibility that does not have org access to the Item Master - then it doesnt work.
I can create it in a different organizations, but not all responsilibitie s have a common inv org.
Any suggestions ? Can we have a QA plan that is shown in all inv orgs somehow?
Will appreciate any comment,
Aaron
how to do this step PO Inspection plans can represent multiple item numbers for inspection if the field MULTIPLE_ITEMS is equal to ‘YES’ in the collection plan
I am on Oracle Apps R12.
Thanks
D avid
Can any one describe me the standard Alerts provided in Oracle Quality Module & how to setup them?
Awaiting ...
Adeel
Thanks , this is an informatic paper.
i have the requiremnt that the quality results from MFG should check based on the sampling plan. How should i apply sampling plan to the collection plan triggered for the WIP completion transaction.
T hanks for all the valuble information. My mail id: saradhi.janyavula
Is there any API/process/Met hod to delete all recores from qa_result from specific quality plan.
pls reply
Shirish K@Pune
There is no API available to delete the data from quality table, however, I had read an Oracle note which suggest you can delete the data from the qa_results table selectively.
T hanks,
Nimish
I have four fields to enter numeric quality results. Any one of the four fields could have null value. The 5th column I want to display the sum of the four fields.
Please advise!!
I need to move daily quailty results from a PC to Oracle Quality Plan Results.
Can anyone give me a example on how this could be done?
Thanks,
Best Regards,
-Manny
Can you suggest any solution for this issue?
Thanks
Excellent article, very helpful, looking forward to more of your articles.
i was able to close a requirement in half a day :)
excellent, keep it up :)
Abdulrahman Mohammed
Excellent article, very helpful, looking forward to more of your articles.
i was able to close a requirement in half a day :)
excellent, keep it up :)
Abdulrahman Mohammed
Your post was helpful. We need to create quality collection plan in which if a user 'A' enters data in some fields and provides another user 'B' reference, it will trigger a mail notification alert to that user 'B' to enter data in remaining fields of that collection plan record and save the same. Is there any way we can accomplish the same?
I am new to this forum.
I can provide the solution for all Oracle Quality related questions.
Your issue can easily be accomplished. for sending email notificaiton to User B to enter his part of data.
First You need to create an element either as lookup or free text for user B. You can create an event based alert through alert manager based on the qa_resutls collecton inputs. In alert you can write SQL script to search the null fields where user B is supposed to enter the data. Capture the output variable for rows & email Ids of that users to send the details through email.
to work with above solution You should be familier with Alert Manager.
This is very nice paper. My client has a requirement where they need a process for capturing quality data without receipt transactions being performed. A link to the custom quality plan will be needed. The link will be in the form of a button or menu option from the Tools. This should allow to go to the quality plan to enter information.
Can someone share some sample code and some thoughts about this requirement.
Thanks,
Suneetha
I don't know who you are but definitely you're going to a famous blogger if you aren't already ;
) Cheers!
RSS feed for comments to this post