Modifying Views with an Xu2 Script

I ran into a problem with the EAM_Work_Order_Resources view label recently. In working with Noetix Support, I created documentation to re-create the problem and correct this view. The end result is that the where clause has a slight problem.

My conclusion was the following:

This statement should be commented out:

" AND EMP.EFFECTIVE_START_DATE(+) = BREMP.EFFECTIVE_START_DATE"

And replaced with:

" AND EMP.START_DATE(+) = BREMP.EFFECTIVE_START_DATE"

While I wait for Noetix to fix this, I thought I would provide my users with a temporary fix to correct this.

Ultimately, I want to just comment out a line and add a new line. Here is a query I created to find this line:

select
*
from
n_view_where_templates
where 1=1
and Upper(view_label) = 'EAM_WORK_ORDER_RESOURCES'
and where_clause = 'AND EMP.EFFECTIVE_END_DATE(+) = BREMP.EFFECTIVE_END_DATE'
order by where_clause_position;

Here is my temporary XU2 script which adds a new where clause statement to all views which share this view label (while I wait for Noetix):


INSERT INTO n_view_where_templates
(view_label
,query_position
,where_clause_position
,where_clause
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('EAM_Work_Order_Resources' -- view_label
,1 -- query_position
,19.5 -- where_clause_position
,'AND EMP.START_DATE(+) = BREMP.EFFECTIVE_START_DATE' -- where_clause
, TO_CHAR(NULL) --profile_option
,'%' --product_version
,'flintstonef' --created_by
, SYSDATE --creation_date
,'flintstonef' --last_updated_by
, SYSDATE --last_update_date
);

Because I want to perpetuate this change to all views associated with this view label, an xu2 script should be used. I had problems with originally using an xu2 script to modify this and the certification course spent a lot more time regarding the modification of specific views (xu5 script). Consequently, I erroneously thought the best approach was to use an xu5 script.

This has the form:

update n_view_where_templates
set
where_clause = '--'|| where_clause
where
view_label = 'EAM_Work_Order_Resources'
and where_clause = 'AND EMP.EFFECTIVE_START_DATE(+) = BREMP.EFFECTIVE_START_DATE';

COMMIT;


I just throw these statements in SQL Developer and make sure they do not throw an error. Now that I know that there is no syntactical error, I throw these scripts into the Noetix View Administrator.