Note Taking During the Production Regeneration Process

I perform my production regenerations at night (usually 2 or 3 times a month).  It is usually not the most focused time (my 2 year daughter and my wife typically distract me from being singularly focused on my regeneration).  

While at a high level, a regeneration of views can be a relatively simple thing (especially if you have all your new xu2/xu5 scripts added to the install home during the day).   Yet, there are enough opportunities to mess things ups (script versioning and retention, adherence to standard regeneration process).

It is extremely important that this regeneration process for production is well defined and consistently implemented.

What I have been doing the last couple of regenerations is creating a “regeneration notes” document and annotating any issues.  Should there be any issues when you push all of your development through a software development lifecycle (good design, unit testing and UAT)?  No, but does it happen?  Yes. 

I document moving all my scripts that are being promoted to PVCS (my versioning software), Noetix Custom folder, and production instance folder.  I document any problems I notice during my regeneration (e.g. missing grants).

 

The value I find in doing this is that when I start preparing for my next regeneration in production, I can check my notes (to make sure all code was promoted to the appropriate places…PVCS, Noetix Custom folder and instance homes).  If I had any problems which need to be fixed, it reminds me of these problems so that I resolve the problem with my next regeneration. 

 The day after production regeneration, I like to compare my production instance home with my Noetix custom folder to make sure there are no discrepancies.   I love using Microsoft PowerShell to do this.

Here is my power shell script:

$pathNoetixCustom = Get-Item "\\cmntx02\NOETIX_HOME\NoetixViews 6.0.1 - ERP\Installs\NOETIX_SYS_erpdev\*xu2.sql"

# this is my production install home

foreach ($xu2ScriptCustom in $pathNoetixCustom)

{$NameNoetixCustom =  $xu2ScriptCustom.Name

 $LastWriteCustom =$xu2ScriptCustom.LastWriteTime

 $pathInstance = Get-Item  "\\cmntx02\NOETIX_HOME\NoetixViews 6.0.1 - ERP\Master\Custom\*xu2.sql"

# this is my Noetix custom folder

 foreach ($xu2ScriptInstance in $pathInstance)

 {$NameInstance = $xu2ScriptInstance.Name

  $LastWriteInstance =$xu2ScriptInstance.LastWriteTime

    if ($NameNoetixCustom -eq $NameInstance -And -Not($LastWriteCustom -eq $LastWriteInstance)) {$NameNoetixCustom

                            $xu2ScriptCustom | Select-Object LastWriteTime

                            $xu2ScriptInstance | Select-Object LastWriteTime

     }

 }

}

You would want to replace the Noetix custom home and the install home paths with yours.  I could have used a parameter (this is a small change), but I have not done this here.

In summary, note taking after regeneration can help hone the process and makes me more equipped to be cognizant and fix recurring problems (even minor).   My user community demand high availability and a stable environment and note taking helps me do this.

Are You Going to the NVCC Class?

Since Noetix is changing (e.g. global views with Noetix 6 or the new hook script tool which I have heard about), it sure would be nice if anyone wants to step forward and submit posts to this blog regarding this class' content.

Consequently, if you are planning on taking the course in May of 2012 and you like blogging, let me know and we can arrange for you to place entries in this blog on the course.

When and How to Create View Joins in Noetix

Before going into describing how to create joins in Noetix, I want to express my philosophy on joins and Noetix Views.  

First, Noetix Views in many ways are designed to encapsulate the needs of a reporting requirement(s).  If one goes into the Help file (or Noetix Search) for a given view, a description of the view’s content is provided.  It has a very specific business need and the view was designed to enable users to achieve this.  My point is that more often than not, the view should satisfy an entire requirement.

While a given Noetix View should meet a given business requirement (one triangulates between the user’s requirement/Noetix’s description of the view and the DDL), sometimes Oracle Application’s shared entities are lacking (e.g. Item master, custom master, vendor master) in a given Noetix View. 

This type of scenario in my opinion is the best time to use a view Join.  Let’s investigate.  An example of this might be INV_Items (item master view) and the OE_Lines view.

First what type of join would this be; an inner join or outer join?

Let us confirm that there is a not null constraint on inventory_item_id.

NOETIX_SYS@erptst> SELECT search_condition

  2  FROM dba_constraints

  3  WHERE owner    = 'ONT'

  4  AND table_name = 'OE_ORDER_LINES_ALL';

SEARCH_CONDITION

================================================================================

"LINE_ID" IS NOT NULL

"HEADER_ID" IS NOT NULL

"LINE_TYPE_ID" IS NOT NULL

"LINE_NUMBER" IS NOT NULL

"INVENTORY_ITEM_ID" IS NOT NULL

"SHIPMENT_NUMBER" IS NOT NULL

"CREATION_DATE" IS NOT NULL

"CREATED_BY" IS NOT NULL

"LAST_UPDATE_DATE" IS NOT NULL

"LAST_UPDATED_BY" IS NOT NULL

"LINE_CATEGORY_CODE" IS NOT NULL

"OPEN_FLAG" IS NOT NULL

"BOOKED_FLAG" IS NOT NULL

13 rows selected

Sure enough, there is.  Consequently, an inner join appears to be appropriate.

Let us look at an ERD of these two views:

In Discoverer nomenclature, we would make the INVX0_Items view the master view and the ONTX0_Lines view as the details view (one-to-many relationship with the minimum cardinality on the lines view being zero).

Okay, how do we join them?  Noetix advises that if a table is a base table (integral to the view and not outer joined within the view) then it can be a candidate for creating a Z$ column (the join columns).  The way to communicate this to the Noetix View Administrator tool is to set the base_table_flag = ‘Y’.

When we query the tables that meet this criteria on the only query block available for this view, we see the following results:

NOETIX_SYS@erptst> select

  2  view_label,

  3  query_position,

  4  base_table_flag,

  5  table_name

  6  from

  7   n_view_table_templates

  8   where

  9   view_label like 'OE_Lines'

 10  and base_table_flag = 'Y';

VIEW_LABEL                     QUERY_POSITION B TABLE_NAME

============================== ============== = ==============================

OE_Lines                                    2 Y HZ_CUST_ACCOUNTS

OE_Lines                                    2 Y OE_ORDER_HEADERS_ALL

OE_Lines                                    2 Y MTL_SYSTEM_ITEMS_B

OE_Lines                                    2 Y OE_ORDER_LINES_ALL

OE_Lines                                    2 Y HZ_PARTIES

OE_Lines                                    2 Y OE_OU_ACL_MAP_BASE

6 rows selected

Consequently, we see that MTL_SYSTEM_ITEMS_B has been designated as a candidate for a join column.

Okay, why does it never get beyond candidacy (this is my US election plug)?  Let us look at the n_view_tables (the instantiation of the template table, n_view_table_templates).

NOETIX_SYS@erptst> SELECT view_name,

  2    key_view_name,

  3    column_name,

  4    omit_flag

  5  FROM n_view_columns

  6  where

  7   view_name like 'ONTX0_Lines'

  8  and key_view_name is not null

  9  ORDER BY 1,

 10    2;

VIEW_NAME                      KEY_VIEW_NAME                  COLUMN_NAME                    O

============================== ============================== ============================== =

ONTX0_Lines                    INVX0_Items                    Z$INVX0_Items                  Y

ONTX0_Lines                    ONTX0_Lines                    Z$ONTX0_Lines

ONTX0_Lines                    ONTX0_Orders                   Z$ONTX0_Orders

ONTX0_Lines                    RAAX0_Customers                Z$RAAX0_Customers              Y

Notice that the join procedures in the NVA concluded that the join associated with the customer master and the item master should be omitted (omit_flag = ‘Y’). 

In a development environment, I executed this xu5 script and I have been examining the resulting joins that are now exposed/generated without having to manually create them. 

-- output to Z_Columns_xu5.lst file
@utlspon Z_Columns_xu5

update n_view_columns
set omit_flag = to_char(null)
where column_expression ='rowid'
and column_type = 'GEN'
and group_by_flag = 'N'
and application_instance = 'X0';

commit;
 
@utlspoff

At this time I would not want to run a script like this in production (more testing and perhaps discussion with Noetix).  This script did provide joins that I did desire with no negative consequences that I can see as of now.  As you can see, this script only changes the 'X0' instance.

Typically, I have seen and have been trained to give up if the base_table_flag approach fails (or use an XU5 script to manually enter the join column and then I have to manually create the join in my Discoverer environment). 

I want to explore this scenario in future posts. 

I know that if I update the non-template table to set the omit_flag in the n_view_columns table, that Noetix will then create a join.   I have tested this approach with some views and this works.  I would like to discuss this with Noetix’s Help staff to see what they have to say regarding this.

My suspicion is that this join is omitted because both view_templates need to be shared by each other’s role label:

NOETIX_SYS@erptst> select

  2  role_label

  3  from

  4  n_role_view_templates

  5  where

  6  view_label = 'INV_Items';

ROLE_LABEL

========================

DEPOT_REPAIR

FIELD_SERVICE

INSTALL_BASE

INVENTORY

ORDER_ENTRY

PURCHASING

RECEIVABLES

SERVICE_CONTRACTS

TELESERVICE

PRODUCT_DEVELOPMENT

PROCESS_EXECUTION

11 rows selected.

NOETIX_SYS@erptst> select

  2  role_label

  3  from

  4  n_role_view_templates

  5  where

  6  view_label = 'OE_Lines';

ROLE_LABEL

========================

ORDER_ENTRY

Since this is not the case, I suspect that it is rejected and a join candidate due to this security issue.   

That is all for now.