Modifying an ALOOK Column Type with an Xu2 Script

Back in June, I identified (and notified Noetix) two columns that are not populating (are always NULL) in our Noetix 5.87/Oracle Apps version 12.06 of the view GMD_Routing_Resources. Here they are:

Resource_Process_UOM
Resource_Usage_UOM

When I research the table they are associated with,GMD_OPERATION_RESOURCES, it appears that between Oracle and our implementers the following holds:

-GMD_OPERATION_RESOURCES.RESOURCE_PROCESS_UOM stores UOM information on resource processes and not GMD_OPERATION_RESOURCES.PROCESS_UOM.

-GMD_OPERATION_RESOURCES.RESOURCE_USAGE_UOM stores resource usage UOM information and not GMD_OPERATION_RESOURCES.USAGE_UM.

When I query how the n_view_column_templates table has these columns set-up, I see the following information:

Resource_Process_UOM

The column, view_label, is GMD_Routing_Resources
The column, column_expression, is PROCESS_UOM.
The column, Column_type, is ALOOK.
The column, ref_application_label, is INV.
The column, ref_table_name, is MTL_UNITS_OF_MEASURE_TL
The column, ref_lookup_column_name, is UOM_CODE.
The column, ref_description_column_name, is UOM_CODE.

Resource_Usage_UOM

The column, view_label, is GMD_Routing_Resources.
The column, column_expression, is USAGE_UM.
The column, Column_type, is ALOOK.
The column, ref_application_label, is INV.
The column, ref_table_name, is MTL_UNITS_OF_MEASURE_TL
The column, ref_lookup_column_name, is UOM_CODE.
The column, ref_description_column_name, is UOM_CODE.



It is always good to have some perspective on how this undocumented column type, ‘ALOOK’, behaves by looking at this query:

select
*
from
n_view_column_templates
where
column_type= 'ALOOK'

I looked up how these column types express themselves in the views and in the end user layer.
In examining these columns, it appears that these are very dissimilar to the LOOK column type. It appears that reference columns exist for the purpose of communicating to the reporting tool information on the list of values. I will have to do more examining of this.
In any event, for this particular view (and associated with our version of apps and our implementation), these columns are wrong and need to be fixed.

While I wait for Noetix to provide a correction to this, here is my correction script (called by xu2):

update N_view_column_templates
set column_expression = 'RESOURCE_PROCESS_UOM'
where view_label = 'GMD_Routing_Resources'
and column_label = 'Resource_Process_UOM';

COMMIT;


update N_view_column_templates
set column_expression = 'RESOURCE_USAGE_UOM'
where view_label = 'GMD_Routing_Resources'
and column_label = 'Resource_Usage_UOM';

COMMIT;

I first run this script using SQL Developer just to confirm the syntactical accuracy. Next, I run this script through the NVA. My testing of this validates its accuracy.