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.