Are You Going to the Noetix View Customization Certification Class? Know about Template Tables

Template tables (tables in Noetix schema ending with the word, ‘TEMPLATES’) are like an object oriented programming language class (the non-template tables are an intermediate step).  The Noetix Views are the result of the Noetix View Administrator "instantiating" the template tables based on:

-Your Noetix Views purchased

-Your implementation of Oracle Applications

-Your version of Oracle Applications.

We will look at the view regeneration process as it pertains to the view label, 'INV_Items'.

 In stage 4 of the regeneration process, we see the following activities being completed (sort contents of the Noetix Views install home folder by creation dates):

-ycrtmpl.sql creates the n_%_templates tables.

-dat%.log files show the data loaded into the template tables (including 'INV_Items')

-wnoetxu2.sql is invoked (modifications to template tables, n_%_templates)

   Example text of what could be in an xu2 script for this view:

    INSERT INTO n_view_table_template
(view_label
,query_position
,table_alias
,from_clause_position
,application_label
,table_name
,product_version
,base_table_flag
,subquery_flag
,gen_search_by_col_flag
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Items' -- view_label
,1 -- query_position
,'CT' -- table_alias
,29 -- from_clause_position
,'APPS' -- application_label
,'MTL_DESCR_ELEMENT_VALUES' -- table_name
,'%' -- product_version
,'N' -- base_table_flag
,'N' -- subquery_flag
,'N' -- gen_search_by_col_flag
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;
COMMIT;


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
('INV_Items' -- view_label
,1 -- query_position
,405 -- where_clause_position
,'AND CT.INVENTORY_ITEM_ID(+) = ITEM.INVENTORY_ITEM_ID' -- where_clause
,'' -- profile_option
,'%' -- product_version
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;

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
('INV_Items' -- view_label
,1 -- query_position
,406 -- where_clause_position
,'AND CT.ELEMENT_NAME(+) = ''Coating''' -- where_clause
,'' -- profile_option
,'%' -- product_version
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;
COMMIT;

INSERT INTO n_view_column_templates
(view_label
,query_position
,column_label
,table_alias
,column_expression
,column_position
,column_type
,description
,group_by_flag
,gen_search_by_col_flag
,profile_option
,product_version
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('INV_Items' -- view_label
,1 -- query_position
,'Coating' -- column_label
,'CT' -- table_alias
,'ELEMENT_VALUE' -- column_expression
,100 -- column_position
,'COL' -- column_type
,'Coating --(XXACME Column)' -- description
,'N' -- group_by_flag
,'N' -- gen_search_by_col_flag
,'' -- profile_option
,'%' -- product_version
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date
,'FLINSTONEF' -- last_updated_by
,SYSDATE) -- last_update_date
;

COMMIT;

-Noetix role prefixes are edited and query users are set-up:

..(more will be documented)

Knowing how this “instantiation” is done (sequence) is important in choosing which scripts to use.

Think of this fact as your primary node of information about Noetix and keep adding child nodes around this.

Are You Going to the Noetix View Customization Certification course? Review How to Use SQL*Plus

The Noetix View Customization Certification course uses SQL*Plus in lieu of the more user friendly GUI querying tools.

Are you used to nice GUI database querying tools? While there are non-database “tricks” to obtain metadata about the Noetix schema objects (e.g. examine various spool files), my personal feeling is to just be comfortable with sql statements that expose information from the data dictionary. In all honesty, some of these GUI tools encourage mental lethargy!

Suggestions:

Set-up SQL*Plus in way that enables you to work efficiently. 

-Use a login.sql script to provide some formatting

Here is the contents of my login.sql file:

--******login.sql start******************************

define _editor='C:\Program Files\Vim\vim73\gvim.exe'

set serveroutput on size 1000000
set trimspool on
set long 5000

set linesize 100
set pagesize 9999

column plan_plus_exp format a80
set termout off
SET SQLPROMPT '&_user@&_CONNECT_IDENTIFIER> '
set termout

--******login.sql end********************************

I personally like command mode in lieu of the GUI windows version of SQL*Plus.

With the editor set for Vim (or whatever text editor you want to use).

When you want to edit the SQL*Plus buffer, you would just type, edit

After your editing is complete, save and close your editor.

Next, type "r" or "/" to execute the contents of the buffer.  This is switch from the nice GUI tools, but after working with this for a little while, one can probably have a similar efficiency to a nice GUI tool.

I personally do all my text editing in Vim (or Notepad++).

************************************

Sample Data Dictionary Related Queries (here are more):

Use the dbms_metadata package if they are using the Oracle 9i Client (or later):

select

dbms_metadata.get_ddl('TABLE','N_VIEW_COLUMNS','NOETIX_SYS')

from

dual;

or for a view:

select

dbms_metadata.get_ddl('VIEW','INVG_ITEMS','NOETIX_SYS')

from
dual;

Otherwise use the DESC command to access a description of the columns of a view or table.

e.g.

DESC INVG_ITEMS

For the DDL associated with a view, this works well:

select

text

from
Dba_views
where
view_name = 'INVG_ITEMS'

Here is a query against constraints (a common question when executing DML against the template and non-template tables):  

select

*

from

dba_constraints

where 1=1

and owner = 'NOETIX_SYS'

and table_name like :table_name

;

/* types of constraints
C (check constraint on a table)

P (primary key)

U (unique key)

R (referential integrity)

V (with check option, on a view)

O (with read only, on a view)

*/

That is all.

Oracle Applications 12.1.3 and Noetix 6.0.1

We went live with Oracle Applications 12.1.3 using the 6.0.1 Noetix Views during the 4th of July holiday weekend. The direction I had received from Noetix (per the NVA documentation and talks and emails with various Noetix staff) was to do the following:

- Regenerate NoetixViews (stages 2, 3 &4)

- EUL Generation (Extract Views)

- Update Security

Just to be on the safe side (in case I was missing something), I submitted a ticket to Noetix to confirm these tasks. They corroborated the previous direction I had received.

After I performed these steps, I proceeded, with the usual post re-generation steps which I have posted here, Post Regeneration Steps with 6.01 Noetix Views.

Since our implementation, we have noticed that some of our planning (ASCP module) reports running somewhat slower.  Our DBA has indicated that there are a handful of indexes that are being put into an "unusable" state by partition maintenance.  While this is a problem, we have not identified any issue with our Noetix Views.

That is all for now.

Custom LOVs in Noetix

We have implemented Noetix with Discoverer, consequently we are at the mercy of what Noetix provides for its LOVs out of the box. In general, for non-Noetix Platform reporting tools, this is how Noetix generates LOVs:

1. Columns in a view that are indices have duplicate columns and classes created with this naming convention, ‘A$’ || column_name.

2. In the role associated with the view, a LOV object (e.g. in Discoverer, a class) is created by using the table associated with the indexed column in the Noetix View.

The problem with this that it is not always very elegant.  For example the class, A$Customer_Name (HZ_PARTIES), which creates the LOVs for a customer's name uses party_name from the table, AR.HZ_PARTIES. Specifically, if one looks at the party_name column, the result set is more than just a list of customers' names.  Fortunately, Noetix does have its own LOV Noetix Views generated during view generation including the view, ARX0_LOV_CUSTOMERS, but classes in Discoverer (objects used for LOVs in Discoverer) are not created. 

One of these views, ARX0_LOV_CUSTOMERS, appears to meet our needs quite well.  Consequently, I use this view for my custom class, XXACME_CUSTOMERS.  More than likely, for most of these LOV scenarios, Noetix has a LOV view which should meet your needs and probably is correctly shared with the appropriate Noetix role (e.g. the ARX0_LOV_CUSTOMERS view is shared with my ONTX0_ORDER_ENTRY role).

Unfortunately, after  I have created these custom classes (or LOVs), the mapping between the item (Noetix View column) and the custom class are blown away every time I use the EUL Generator.

I posed this problem to Noetix and they provided me with a script and a package to preserve these mappings associated with these custom classes during an EUL Generation.

The package they provided is titled, "n_eul_gen_item_class_pkg".  The script they provide is titled, n_crscript.sql. 

The methodology for using this script and package is the following:

1. Pre-EUL generation, run the script, n_crscript.sql, in Noetix System account, which creates a spool file titled, n_updlinks.sql.
2. Run the EUL Generator.
3. Run the n_updlinks.sql script in Noetix System account to recreate link between custom class and Noetix Views items.

Consequently, I run the script before I run the EUL Generator to keep track of these mappings between the columns and the custom classes (which are manually created).  It then spools this association with an invocation to the package, n_eul_gen_item_class_pkg.

Here is an example of one spooled row:
execute n_eul_gen_item_class_pkg.update_column_item_class('ONTX0_Order_Freight_Charges','A$Customer_Name','XXACME_CUSTOMERS');

While I was waiting for a regeneration to complete last night, it occurred to me that I do not need to ever manually associate my custom classes with Noetix View columns. Specifically, I just need to leverage this n_eul_gen_item_class_pkg package to do the heavy lifting.

Specifically, I do the following:

1. Create a custom class in a given Noetix role (Discoverer Business Area). It is important to know that Noetix does create a number of views matching the pattern ‘%Lov% ‘ which are used in Noetix Platform (yet, one can easily create a custom class from them). None of these views are used for non-Noetix Platform reporting tools.

2. I create a spool file with this script:

select
'execute n_eul_gen_item_class_pkg.update_column_item_class('''vc.view_name''','''vc.column_name
''',''xxacme_customers'');'
from
noetix_sys.n_roles r,
noetix_sys.n_role_views rv,
noetix_sys.n_view_columns vc
where
r.role_name = 'ONTX0_ORDER_ENTRY'
and r.role_name = rv.role_name
and rv.view_name = vc.view_name
and vc.view_name not like '%Lov%'
and VC.VIEW_NAME not like '%Base'
and upper(column_name) in ('CUSTOMER_NAME', 'CUSTOMER','A$CUSTOMER_NAME');


Of course, replace my constants with bind variables and this script is then more broadly useable.


Consequently, when I spool, I obtain this:

execute n_eul_gen_item_class_pkg.update_column_item_class('ONTX0_Order_Freight_Charges','A$Customer_Name','xxacme_customers');

execute n_eul_gen_item_class_pkg.update_column_item_class('ONTX0_Order_Freight_Charges','A$Customer_Name','xxacme_customers');

(and many more records)…


Using this method enables me to create a mapping of my Discoverer class to a view column quickly without all of the manual labor.

Cloning a View in Noetix

I cannot claim that I came up with this idea/approach, but it is very similar to the method I described regarding creating a new view in Noetix. What do I mean by cloning a view? It means taking the DDL of an existing view and copying it (typically with the intent of modifying it enough to justify a new name).

Here is an example:

Noetix provided an INV_Alias_Transactions view, but it did not go down to the lot granularity level. It is/was a perfectly fine view, but my user community insists (provided a requirement) that dictated that they need a view which provides lot information.

This leads me to have to make a decision. Do I build a new view from scratch? Do I just make a clone of the existing view and add lot information. Per the Noetix View Customization Certification course, changing the cardinality of a view is a very bad idea for a lot of reasons.

Here are some of them: it affects the joins (how will the totals behave once the modification has occurred?), how will the quantities behave once the new column(s) have been added. Will the quantities in the view be incorrect or misleading after the change?

It goes without saying that this method of cloning views is not supported by Noetix.

Let us begin

Just like creating a new view from scratch, the sequence of DML statements needed to create a cloned view follow a sequence determined by the foreign key constraints set-up in the template tables.

First enter your N_VIEW_TEMPLATES DML statements.

Notice the following Foreign Key Constraints with this table. In the blog entry titled, "Step by Step Creating a New View", I go into more detail about examing these constraints.  When composing this statement, be mindful that the N_VIEW_TEMPLATES_FK1 requires that your application label exist. Also, if the profile option is not null, then N_VIEW_TEMPLATES_FK2 requires that the option be set-up in N_PROFILE_OPTION_TEMPLATES.

-- output to INV_Alias_Lot_Trnsctns_xu2.lst file

@utlspon INV_Alias_Lot_Trnsctns_xu2

-- -----------------------------------------------------------------------------
-- Clone INV_Alias_Transactions view template to INV_Alias_Lot_Trnsctns
-- -----------------------------------------------------------------------------

INSERT INTO n_view_templates
(view_label,
application_label,
description,
profile_option,
essay,
keywords,
product_version,
include_flag,
export_view,
security_code,
special_process_code,
sort_layer,
freeze_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
original_version,
current_version
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
application_label,
description,
profile_option,
essay,
keywords,
product_version,
include_flag,
export_view,
security_code,
special_process_code,
sort_layer,
freeze_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date,
original_version,
current_version
FROM n_view_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

--customize essay and description
UPDATE n_view_templates
SET description = 'ACME Custom - Modified version '||
'of INV_Alias_Lot_Trnsctns'||
essay = 'Big essay on what this view does. '||
CHR(10) ||CHR(10)||
'This view differs from the INV_Alias_Transaction '||
'view in that it does...'
WHERE view_label = 'INV_Alias_Lot_Trnsctns'
;

COMMIT;


Now that a view template record exists, I can then add arguments to insert records to the N_ROLE_VIEW_TEMPLATES table. Why this table?

Records added to this table require that an N_VIEW_TEMPLATES record exist for the view we are creating.

-- -----------------------------------------------------------------------------------------

-- Include INV_Alias_Lot_Trnsctns in all Roles that currently contain INV_Alias_Transactions



-- -----------------------------------------------------------------------------------------

INSERT INTO n_role_view_templates
(role_label,
view_label,
product_version,
include_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT role_label,
'INV_Alias_Lot_Trnsctns' view_label,
product_version,
include_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM n_role_view_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

Next, records need to be added to the N_VIEW_QUERY_TEMPLATES table.

If you look at the foreign key constraints on this table, you will see that the view you are adding has a record in the N_VIEW_TEMPLATES and a record (if not null) in the N_PROFILE_OPTION_TEMPLATES table.

Here is my insertion argument to this table:

INSERT INTO n_view_query_templates
(view_label,
query_position,
union_minus_intersection,
group_by_flag,
profile_option,
product_version,
include_flag,
view_comment,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
union_minus_intersection,
group_by_flag,
profile_option,
product_version,
include_flag,
view_comment,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM n_view_query_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

We are finally in familiar territory when we get to the N_VIEW_TABLE_TEMPLATES table (from a documentation perspective).

With this table, there is no shortage of foreign key constraints so it is imperative that the other records were added prior to this step.

If you look at this DML statement, you will see that our previous commands have allowed us to run this without throwing a foreign key constraint error.

Here is my insertion statement for this table:

INSERT INTO n_view_table_templates
(view_label,
query_position,
table_alias,
from_clause_position,
application_label,
table_name,
profile_option,
product_version,
include_flag,
base_table_flag,
key_view_label,
subquery_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
gen_search_by_col_flag
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
table_alias,
from_clause_position,
application_label,
table_name,
profile_option,
product_version,
include_flag,
base_table_flag,
key_view_label,
subquery_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date,
gen_search_by_col_flag
FROM n_view_table_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;

Lastly, here is my insertion statement for N_VIEW_WHERE_TEMPLATES and N_VIEW_COLUMN_TEMPLATES:

INSERT INTO n_view_where_templates
(view_label,
query_position,
where_clause_position,
where_clause,
profile_option,
product_version,
include_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
where_clause_position,
where_clause,
profile_option,
product_version,
include_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM N_VIEW_WHERE_TEMPLATES
WHERE VIEW_LABEL = 'INV_Alias_Transactions'
);

COMMIT;


INSERT INTO n_view_column_templates
(view_label,
query_position,
column_label,
table_alias,
column_expression,
column_position,
column_type,
description,
ref_application_label,
ref_table_name,
key_view_label,
ref_lookup_column_name,
ref_description_column_name,
ref_lookup_type,
id_flex_application_id,
id_flex_code,
group_by_flag,
format_mask,
format_class,
gen_search_by_col_flag,
lov_view_label,
lov_column_label,
profile_option,
product_version,
include_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
(SELECT 'INV_Alias_Lot_Trnsctns' view_label,
query_position,
column_label,
table_alias,
column_expression,
column_position,
column_type,
description,
ref_application_label,
ref_table_name,
key_view_label,
ref_lookup_column_name,
ref_description_column_name,
ref_lookup_type,
id_flex_application_id,
id_flex_code,
group_by_flag,
format_mask,
format_class,
gen_search_by_col_flag,
lov_view_label,
lov_column_label,
profile_option,
product_version,
include_flag,
'Flinstonef' created_by,
sysdate creation_date,
'Flinstonef' last_updated_by,
sysdate last_update_date
FROM n_view_column_templates
WHERE view_label = 'INV_Alias_Transactions'
);

COMMIT;


After you are done with this xu2 script,  I would run a regeneration with the intent of making sure no errors are thrown.  Next, one can add the additional components that would unacceptable if the changes where made in the existing view. 

That is all for now.

Reflections on Noetix Views with an Emphasis on the Inventory Module

What has Noetix brought us?


-A security framework that reflects our ledger/legal entities/operating units that is robust.

-Breadth of views. Usually these views are not exactly what our users want. They get a little frustrated and then someone from our user group submits a request to have a modification.

-A means to stay current with Oracle Applications.

Today I want to reflect on the Noetix Views regarding the Inventory module.

There are about 16 views we use with some frequency. Most views which are used have at least some modifications.

Here they are:

INV_Item_Onhand_By_Lot, INV_Onhand_Quantities, INV_Customer_Items, INV_Item_Purchasing_Attribut, INV_Onhand_Period_End, INV_Category_COSTING, INV_Item_Onhand_By_Lot_Loc, INV_Lot_Details, INV_Manufacturer_Item_Detail, INV_Category_Inventory, INV_Item_Planning_Attributes, INV_Item_Revisions,
INV_Batch_Transactions, INV_Customer_Items, INV_Item_Cross_References,
INV_Lot_Status_History

Some of these views are custom views (clonings and some home grown creations):

INV_Onhand_Period_End, INV_Lot_Status_History, INV_Item_Onhand_By_Lot_Loc, INV_Batch_Transactions, INV_Lot_Status_History

The point of this post is to step back and reflect. What has Noetix done for us as it pertains to this module? If you look at all the views that we do use, one can immediately conclude that Noetix has provided a spectrum of views based on historical needs. Not only this, we can feel good about the software development lifecycle of these views. Specifically, we know that Noetix does a good job of designing and testing their views.

While a spectrum of views has been provided, it is not complete. 4 out 16 of these views are custom views. Also, of the 12 views remaining (that are heavily used), more than ½ of them needed to be customized. Around here, I heard someone say, “Noetix provides about 80% of our needs.” In this module, Noetix has only met about 75% of our needs. In addition to this, when we say 80%, we mean that a given view has about 80% of what we need and the remainder of what we need, we need to customize.

Is this disparaging? No. Nobody is that good that they can sell you an off the shelf software package with no need for customizations. Noetix has done a good job and most people here feel good about Noetix. It is robust. Their views provide breadth. Their views are well designed and provide us with some peace of mind that they are well thought out.

Step by Step Creating a New View

The sequence of DML statements needed to create a new view follow an order determined by the foreign key constraints set-up in the template tables.


First enter your N_VIEW_TEMPLATES DML statements.  Prior to creating a DML statement to insert records to this template table, it would be advisable to look at the foreign key constraints.

Here we query for the foreign key constraints with this table.

select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_VIEW_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

Here are the results:

constraint_name,                         r_constraint_name

N_VIEW_TEMPLATES_FK1 N_APPL_OWNER_TEMPLATES_PK
N_VIEW_TEMPLATES_FK2 N_PROFILE_OPTION_TEMPLATES_PK

When composing this statement, be mindful that the N_VIEW_TEMPLATES_FK1 requires that your application label exist. Also, if the profile option is not null, then N_VIEW_TEMPLATES_FK2 requires that the option be set-up in N_PROFILE_OPTION_TEMPLATES. If you look at the example below, I am creating a custom view associated with our custom Trucking and Shipping Delivery application (TADS). I used ‘OE’ application because this is logical place for me to place our “Trucking and Shipping Delivery“ custom application view (from a role perspective).

INSERT INTO N_VIEW_TEMPLATES
(view_label
, application_label
, description
, profile_option
, essay
, keywords
, product_version
, include_flag
, export_view
, security_code
, special_process_code
, sort_layer
, freeze_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
, original_version
, current_version
)
VALUES
( 'OE_CS_Tads_Loads_Details' --view_label
,'OE' -- application_label
, TO_CHAR(NULL) --description
, TO_CHAR(NULL) --profile_option
, 'This table will be used to track loads that are created '
'within the TADS system when a truck driver signs in.' --essay
,'K{\footnote Tad Load Details}' --keywords
,'*' --product_version
,'Y' --include_flag
,'Y' --export_view
, TO_CHAR(NULL) --security_code
,'XOPORG' --special_process_code
, TO_NUMBER(NULL) --sort_layer
,'Y' --freeze_flag
,'bob' --created_by
, SYSDATE --creation_date
,'bob' --last_updated_by
, SYSDATE --last_update_date
, '6.0.0.849' --original_version
, '6.0.0.849' --current_version
);
COMMIT;

Now that a view template record exists, I can then add arguments to insert records to the N_ROLE_VIEW_TEMPLATES table. Why this table?

Records added to this table require that an N_VIEW_TEMPLATES record exist for the view we are creating.

Again, we check constraints on the table which we want to insert records.
select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_ROLE_VIEW_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

Here are the results:

constraint_name,      r_constraint_name
N_ROLE_VIEW_TEMPLATES_FK1 N_ROLE_TEMPLATES_PK
N_ROLE_VIEW_TEMPLATES_FK2 N_VIEW_TEMPLATES_PK


If you look at my insertion argument, you will see that I have used an existing role and my new view:

INSERT INTO N_ROLE_VIEW_TEMPLATES
( role_label
, view_label
, product_version
, include_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
( 'ORDER_ENTRY' --role_label
,'OE_CS_Tads_Loads_Details' --view_label
,'*' --product_version
,'Y' --include_flag
,'bob' --created_by
, SYSDATE --creation_date
,'bob' --last_updated_by
, SYSDATE --last_update_date
);

COMMIT;


Next, records need to be added to the N_VIEW_QUERY_TEMPLATES table. We look at the foreign key constraints on this table:

select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_VIEW_QUERY_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

Here are the results:

constraint_name,  r_constraint_nam
N_VIEW_QUERY_TEMPLATES_FK1 N_VIEW_TEMPLATES_PK
N_VIEW_QUERY_TEMPLATES_FK2 N_PROFILE_OPTION_TEMPLATES_PK


If you look at the foreign key constraints on this table, you will see that the view you are adding has a record in the N_VIEW_TEMPLATES and a record (if not null) in the N_PROFILE_OPTION_TEMPLATES table.


Here is my insertion argument to this table:


INSERT INTO N_VIEW_QUERY_TEMPLATES
(
view_label
, query_position
, union_minus_intersection
, group_by_flag
, profile_option
, product_version
, include_flag
, view_comment
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
( 'OE_CS_Tads_Loads_Details' --view_label
,1 --query_position
, TO_CHAR(NULL) --union_minus_intersection
,'N' --group_by_flag
, TO_CHAR(NULL) --profile_option
,'*' --product_version
,'Y' --include_flag
,TO_CHAR(NULL) --view_comment
, 'bob' -- created_by
, SYSDATE --creation_date
,'bob' --last_updated_by
, SYSDATE --last_update_date
);

COMMIT;


We are finally in familiar territory when we get to the N_VIEW_TABLE_TEMPLATES table (from a documentation perspective).

With this table, there is no shortage of foreign key constraints so it is imperative that the other records were added prior to this step. Here they are:

select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_VIEW_TABLE_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

The results of this query are:

constraint_name, r_constraint_name

N_VIEW_TABLE_TEMPLATES_FK1 N_VIEW_QUERY_TEMPLATES_PK
N_VIEW_TABLE_TEMPLATES_FK2 N_APPL_OWNER_TEMPLATES_PK
N_VIEW_TABLE_TEMPLATES_FK3 N_PROFILE_OPTION_TEMPLATES_PK
N_VIEW_TABLE_TEMPLATES_FK4 N_VIEW_TEMPLATES_PK

If you look at this DML statement, you will see that our previous commands have allowed us to run this without throwing a foreign key constraint error. In looking at other ‘OE’ application views, I see that they include the OE_OU_ACL_MAP_BASE views. This is a key component to maintain consistency with other ‘OE’ Noetix Views, so I add this table first.


INSERT INTO N_VIEW_TABLE_TEMPLATES
(view_label
, query_position
, table_alias
, from_clause_position
, application_label
, table_name
, product_version
, base_table_flag
, subquery_flag
, gen_search_by_col_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
('OE_CS_Tads_Loads_Details' --view_label
,1 --query_position
,'XMAP' -- table_alias
,1.1 --from_clause_position
,'OE' -- application_label
,'OE_OU_ACL_MAP_BASE' --table_name
,'*' --product_version
,'Y' --base_table_flag
,'N' --sub_query_flag
,'Y' --gen_search_by_col_flag
,'bob' --created_by
, SYSDATE --created_date
,'bob' --last_updated_by
, SYSDATE --last_updated_date
);

COMMIT;

After adding this table, I proceed to add additional tables. From a foreign key constraint perspective, one needs to add records first to the N_VIEW_WHERE_TEMPLATES table and then to the N_VIEW_COLUMNS_TEMPLATES table. The steps to do this as well as methodology are well documented.

My biggest concerns when I venture into creating a new view are the following:

1. Just because a new view can be created and one can leverage the Noetix View Administrator to regenerate views (and leverage its strengths and the consistency from an end user perspective), should you?

2. Is this really in the best interest of my company from a support perspective? Sometimes it is and sometimes it is not and one needs to really weigh the pros and cons. Who will maintain this?

3. Some components of this view set-up require are not obvious nor documented. My methodology is to pick a view that is as similar as possible and it really needs to be from the same Noetix role because one wants these components to behave like the “out of the box” Noetix Views for that role .

4. Are you prepared to support your view as the Noetix Views evolve?

5. Perhaps a clone of Noetix View would better suit your company’s best interest. At some future time I can describe how a clone could be created and weigh the pros and cons of this effort.

That is all for now.