Collection Element 'REV' has no LOV in R12 Oracle Quality

In Oracle Quality module, the Quality Element 'Revision' on Enter Quality Results window has no list of values after R12 upgrade, where it was available in 11i. The users who want to capture the 'Revision' for an item on quality plans have no option to select the list of values for Revision. 

According to Oracle, this is expected functionality in R12. The Item revision collection element would be enabled and editable only for a Revision controlled Item. 

The behavior in 11.5.10 was a bug because of which even though the revision field was grayed out, the user could still enter Revision values by invoking the LOV. This was fixed in R12.

The following are the three possible options available to resolve the issue:

Srl#	Option	Remarks
1	Activate “Revision Control Code” for the items in question: All items for which Revision needs to be captured in the Quality Collection Plan’s results, using an LOV, we need to active the Revision Control code for the items in inventory.	The drawback of this option is that all material transactions would mandate the entry of Revision of the item, which makes it very cumbersome for the Inventory and other modules’ users. Hence, may not be a viable option
2	Go with the new functionality suggested by Oracle	This also may not be a good option if users need to capture the Revision of items on QC plans
3	Creation of a new Quality Collection Element that has the capability of accepting Revision of items; In this option, the “Revision Control” code for items need not be activated.	This workaround would enable the users to capture Revision of items during the entry of Quality Results. This option is explained in detail in the subsequent section.

  

Solution for Option 3: Create new collection Element

1. Create a new collection element with SQL Validation statement to populate the LOV. The following screen shot show the details of the new custom collection element to be created

     Navigation:  Quality  Setup  Collection Elements

The “select” statement to fetch the item’s revisions within the LOV is as under: 

  

2.   Identify all the QA Plans that use the standard / seeded QA element “Revision” for all the inventory organizations 

3.   Add the new custom QA element to all the QA Plans (stored in the table QA_PLAN_CHARS) identified in step 2. This could be programmatically done using an open API

4.  Using standard APIs, copy the existing data present in the “Revision” column of QA_RESULTS table into the column of newly added “custom revision” of the same table. Once the change is activated, the data captured under the new “custom revision” column would be stored in one of the custom (user-defined) columns titled CHARACTER1 through CHARACTER100. 

 

5.   Finally, once the copying of the Revision data is completed into the QA_RESULTS table (via Oracle APIs), nullify the data in “Revision” column

 

6.   Modification of Reports: All reports (Oracle Reports / SQL / Discoverer based) that were referring to “Revision” column should now be referring to the custom / user-defined columns defined for the new “custom revision” QA collection element.