Wednesday, September 26, 2012

Musing about Noetix Workbench

After viewing Paul Winterstein's presentation regarding Noetix Workbench, I have concluded that this is a "must have" for Noetix environments.  In many ways, this completes their product.  Specifically, without a tool like this, I think a lot of Noetix customers are left to make some enhancements in a less than ideal way.  Examples of this are new views without any true integration with the Noetix Views.  Without such a tool, one needs to perform heroics to add one's own views so that they are "integrated" with the Noetix Views (e.g. integrated security with the Noetix Views and metadata included in the Noetix Help/Noetix Search files). 

 One needs to perform many customizations to make their Noetix environment work well (well, at least this is what I have seen).

Specifics about the New Noetix Workbench:

-It greatly simplifies the view modification and view development process, while placing controls on how the modification is performed.

-It has its own script repository with version retention. 

-It provides a degree of uniformity in the scripts that might be much harder to perform without such a tool.  As time goes on in an IT department, possibly many people might be responsible for the enhancement and modification of views.  A tool like this encourages uniformity which is very valuable.

How would one make an argument to your business to buy this tool?

One would need to document the opportunity cost of either hand coding xu2 scripts that would build a Noetix View (so that one would be able to utilize Noetix Help or Noetix Search and rely on Noetix's security components) or not tightly integrating your new custom views with the Noetix Views (maintenance cost might be higher).  This cost would need to consider not only the initial development (e.g 2 to 3 days of creating scripts to generate a view), but also the maintenance of these views over their lifetime (or not integrating these views into the Noetix environment). 

For me, I have created some Java classes to translate a designed view (with standard Noetix security components) into xu2 scripts and so my development time for scripting a newly designed view takes only a couple of hours.  After one considers the rate of these new view requirements, one can start to come-up with reasonable argument for purchasing this tool.

New view creation is very common at my Oracle Application's shop. This week, I just released to production a new Demantra view and a custom advanced pricing view.  I am not surprised if I receive 4 or 5 new view requirements a year (minimum). 

Without such a tool, most Noetix customers find the lack of ability to create their own views and integrate them within the Noetix seeded views as discouraging and daunting. 

Tuesday, August 14, 2012

View Joins Associated with Shared Entity Views (e.g. INV_Items) Across Noetix Roles

The Noetix Generator appears to have been designed to not have views shared across Noetix roles.

An example of this is the shared entity view, INV_Items, which is shared with multiple Noetix roles, but does not join with views of another role in my Noetix Views 6.0.1 implementation.  Perhaps this is an undocumented bug.

One can see this lack of joins when I peruse the OE X0 role and select a column from the INVX0_Items view and yet see no joins with any of the OE X0 views:

I am not fond of this behavior and I like to leverage the content of this shared entity view across multiple views (one of the few good reasons to join views).

I probably should discuss this problem with Noetix support at some point.

Using a xu5 script, I do the following:

AND column_label = 'MTL_SYSTEM_ITEMS_B'
AND (view_label LIKE 'AR%'
OR view_label LIKE 'OE%'
OR view_label LIKE 'PO%')

This results in a plethora of view joins associated with this shared entity view that “failed” through the standard regeneration process across Noetix roles associated with the applications, AR, OE and PO.

Why did I do this with only the AR, OE and PO roles?

With my implementation, I have shared the INV_Items view with all instantiated roles associated with AR, OE and PO, so I do not see a security or generation “risk” in adding this modification to N_VIEW_COLUMNS. One can see that I have no shortage of join options now (and rightly so with a shared entity).

Thursday, May 17, 2012

When and How to Create View Joins Continued

In an earlier blog, I discussed creating view joins using an xu2 or xu5 script. Specifically, in writing about adding a join to a view by setting the base_table_flag to 'Y' for a specific table's record in the n_view_table_templates table, I indicated this (When and How to Create View Joins):

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’.

 My understanding of how to create a view join (specifically the Z$ column with the resulting view joins created by the Generator) has changed since this last post. I now realized that it is prevalent to not only set the base_table_flag to 'Y' (documented approach), but also set the key_view_label as well.

What happened?
The other day, I “cleaned-up” an xu2 script which creates a custom view.  I originally created the view prior to taking the Noetix View Customization Certification course.

My methodology for cleaning-up this xu2 script was to use a custom “copy the Noetix Template records” package.  It creates the insertion arguments in the xu2 script and spool the results.  It follows the same insertion format that is presented in the class for n_view_table_templates, n_view_where_templates and n_view_column_templates.  This became my new script. 

Anyways, my original custom view xu2 script was inserting a non-null value into the  key_view_label column of the n_view_table_templates table where the base_table_flag was set to ‘Y’ and my new spooled custom xu2 script did not include the non-null values into the key view_label column for my table records. 

The end result was that my join columns associated with the item master table, INV.MTL_SYSTEM_ITEMS_B, failed. To be clear, the view regenerated, but the join column was missing. 

When I compared my old and new script,  I noticed the omission and I added ‘INV_Items’  value for the key_view_label column for the n_view_table_templates table insertion argument for the item master table, INV.MTL_SYSTEM_ITEMS_B. I then included the 'INV_Items' information in the key_view_label_column.  This time, my join columns generated.  

 Next, I thought I would peruse the prevalence of the usage of the key_view_label column wherever the INV.MTL_SYSTEM_ITEMS_B table occurred in the n_view_table_templates table records:

NOETIX_SYS@erptst> list
  1  select key_view_label,
  2  application_label,
  3  count(1)
  4  from
  5  n_view_table_templates
  6  where
  7  base_table_flag = 'Y'
  8  and application_label in ('INV')
  9  and table_name = 'MTL_SYSTEM_ITEMS_B'
 10  and nvl(key_view_label,'INV_Items') in ('INV_Items')
 11  and include_flag = 'Y'
 12  group by
 13   key_view_label,
 14  application_label
 15* order by 2, 1
NOETIX_SYS@erptst> /

============================== ========== ==========
INV_Items                      INV               161
                               INV                21

NOETIX_SYS@erptst> spool off;

From this analysis, it appears to occur more often than not (it would interesting to analyze this more…at a later date). 

To conclude (for those of us not using NoetixViews Workbench), I will be cognizant in the future that the key_view_label column is used by Noetix components to generate joins.

Wednesday, April 18, 2012

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.

Thursday, April 12, 2012

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';


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';

============================== ============== = ==============================
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';


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';


11 rows selected.

NOETIX_SYS@erptst> select
  2  role_label
  3  from
  4  n_role_view_templates
  5  where
  6  view_label = 'OE_Lines';


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.

Monday, March 26, 2012

Lot Status History View

On Andy’s blog,, he documented his method of creating a material status history view.  I too have had a similar requirement and I think it could be of help to some people if I post the DDL here:

Create a base view:

    'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________,
    HIST.CREATION_DATE Creation_Date,
    HIST.STATUS_ID Current_Lot_Status_ID,
    HIST.LOT_NUMBER Lot_Number,
    HIST.PRIMARY_ONHAND Primary_Onhand,
    REASN.DESCRIPTION Reason_Description,
    REASN.REASON_NAME Reason_Name,
    FUSER.USER_NAME Reason_Updated_By,
    HIST.SECONDARY_ONHAND Secondary_Onhand
  WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL
  AND REASN.REASON_ID(+)                          = HIST.UPDATE_REASON_ID
  AND HIST.LAST_UPDATED_BY                        = FUSER.USER_ID ;

Create the material status view:

    ITEM.ITEM_TYPE A$Item_Type,
    LOTS.LOT_NUMBER A$Lot_Number,
    XMAP.ORGANIZATION_NAME A$Organization_Name,
    LOTS.PARENT_LOT_NUMBER A$Parent_Lot_Number,
    'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________,
    MATSC.DESCRIPTION Current_Lot_Status,
    MATSC.STATUS_CODE Current_Lot_Status_Code,
    LOTS.GRADE_CODE Grade_Code,
    ITEML.DESCRIPTION Item_Description,
    ITEM.ITEM_TYPE Item_Type,
    LOTS.C_ATTRIBUTE7 LOTS$Actual_Billet_Weight__LBS,
    LOTS.C_ATTRIBUTE4 LOTS$Billet_Cross_Section,
    LOTS.C_ATTRIBUTE1 LOTS$Billet_Length__IN,
    LOTS.C_ATTRIBUTE18 LOTS$Conversion_Indicator,
    LOTS.C_ATTRIBUTE9 LOTS$Customer_Lot_Number,
    LOTS.C_ATTRIBUTE11 LOTS$Customer_Name,
    LOTS.C_ATTRIBUTE13 LOTS$Desired_Cycle,
    LOTS.C_ATTRIBUTE10 LOTS$Deviation_Number,
    LOTS.C_ATTRIBUTE5 LOTS$Disposition_Code,
    LOTS.C_ATTRIBUTE2 LOTS$Input_Child_Lot,
    LOTS.C_ATTRIBUTE20 LOTS$Input_GC_Grandparent,
    LOTS.C_ATTRIBUTE19 LOTS$Input_PC_Grandparent,
    LOTS.C_ATTRIBUTE8 LOTS$Input_Parent_Lot_Number,
    LOTS.C_ATTRIBUTE6 LOTS$PBL_Supplier_Parent_Lot,
    LOTS.C_ATTRIBUTE12 LOTS$Previous_Furnace_Load,
    LOTS.AGE Lot_Age,
    LOTS.BEST_BY_DATE Lot_Best_By_Date,
    LOTS.CREATION_DATE Lot_Creation_Date,
    LOTS.DESCRIPTION Lot_Description,
      THEN 'Y'
      ELSE 'N'
    END) Lot_Disabled_Flag,
    LOTS.EXPIRATION_ACTION_CODE Lot_Expiration_Action_Code,
    LOTS.EXPIRATION_ACTION_DATE Lot_Expiration_Action_Date,
    LOTS.EXPIRATION_DATE Lot_Expiration_Date,
    LOTS.HOLD_DATE Lot_Hold_Date,
    LOTS.ITEM_SIZE Lot_Item_Size,
    LOTS.LENGTH Lot_Length,
    DECODE(LOTS.LENGTH_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN','PSI' ,'PSI','QUA','QUA','TON','TON','USD','USD',LOTS.LENGTH_UOM) Lot_Length_UOM,
    LOTS.MATURITY_DATE Lot_Maturity_Date,
    LOTS.LOT_NUMBER Lot_Number,
    LOTS.ORIGINATION_DATE Lot_Origination_Date,
    DECODE(LOTS.ORIGINATION_TYPE,'0','Lot Master','1','Production','2', 'Quantities','3','Receiving','4','Inventory','5','Returns','6','Other','7', 'Lot Split','8' ,'Lot Merge','9','Lot Translate',LOTS.ORIGINATION_TYPE) Lot_Origination_Type,
    LOTS.RECYCLED_CONTENT Lot_Recycled_Content,
    LOTS.RETEST_DATE Lot_Retest_Date,
    HIST.CREATION_DATE Lot_Status_Creation_Date,
    LOTS.THICKNESS Lot_Thickness,
    DECODE(LOTS.THICKNESS_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME', 'DRM','DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC', 'HRS','Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL' ,'PCS','PCS','PEN','PEN','PSI','PSI' ,'QUA','QUA','TON','TON','USD','USD',LOTS.THICKNESS_UOM) Lot_Thickness_UOM,
    LOTS.VOLUME Lot_Volume,
    DECODE(LOTS.VOLUME_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN','PSI' ,'PSI','QUA','QUA','TON','TON','USD','USD',LOTS.VOLUME_UOM) Lot_Volume_UOM,
    LOTS.WIDTH Lot_Width,
    DECODE(LOTS.WIDTH_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN', 'PSI','PSI','QUA','QUA','TON','TON','USD','USD',LOTS.WIDTH_UOM) Lot_Width_UOM,
    XMAP.ORGANIZATION_NAME Organization_Name,
    LOTS.PARENT_LOT_NUMBER Parent_Lot_Number,
    HIST.PRIMARY_ONHAND Primary_Onhand,
    MATSP.DESCRIPTION Prior_Lot_Status,
    MATSP.STATUS_CODE Prior_Lot_Status_Code,
    HIST.SECONDARY_ONHAND Secondary_Onhand,
    LOTS.SUPPLIER_LOT_NUMBER Supplier_Lot_Number,
    LOTS.VENDOR_NAME Supplier_Name,
    LOTS.TERRITORY_CODE Territory_Code
    NOETIX_SYS.INVG_Lot_Status_Hist_Base HIST,
  WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL
  AND XMAP.APPLICATION_LABEL                      = 'INV'
  AND XMAP.APPLICATION_INSTANCE                   = 'G0'
  AND HIST.LOT_NUMBER                             = LOTS.LOT_NUMBER
  AND MATSC.STATUS_ID(+)                          = HIST.CURRENT_LOT_STATUS_ID
  AND MATSP.STATUS_ID(+)                          = HIST.PRIOR_LOT_STATUS_ID


Friday, February 24, 2012

Capturing Errors in XU2 Scripts Early in Stage 4 of a View Regeneration Using MS PowerShell

I posted on this previously and documented a MS DOS command to find errors in my XU2 scripts (here).The xu2 scripts are invoked during stage 4 of the Noetix View Administrator's regeneration process.  This is right after SQL Loader is finished loading seeded template table records. The errors that are thrown during this part of the regeneration are typically associated with constraint errors.  If you examine the constraints that are associated with a given Noetix template table, one can see that they are quite robustly written to help make sure that you do not add a record in an erroneous way.

One can typically examine the root cause of these errors if one looks at this query:

WHERE 1        =1

Today I wanted to document another way to find these errors.  More than a year ago, I noticed that Microsoft had an updated shell, called PowerShell, and so I placed it on my laptop.  Today, I will go through how one can invoke a command (well, actually a cmdlet) to find Oracle thrown errors that occur while the Noetix View Administrator is in stage 4 of a regeneration.

First, a little background information on PowerShell.

It is rather nice because it is object oriented and its piping is similar to Unix/Linux.  Specifically, the piping can return objects and then the next cmdlet (that is what they call their commands in this new shell) manipulates it, one object at a time through the pipe kind of like an embedded function.  This is very dissimilar to MS DOS where it completes the first command and then pushes the result set to the next command.

I noticed that it was quite different from MS Dos and I looked at it for a little bit and talked to some peers.  My peers heard of it, but thought that it really had gained little traction in terms of use by administrators and other IT staff.

For one reason or another, I stumbled on the blog,, and it really made me reconsider using this shell environment in lieu of MS DOS where practical.

The two main commands one needs to know in this shell are as follows:

Get-Alias  MS_DOS_Command

Notes on this:  This cmdlet just helps you translate your MS DOS command to the PowerShell cmdlet.

Here is an example:

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-Alias cd
This returns:
CommandType     Name                                                                                           Definition                                                                                    
-----------     ----                                                                                           ----------                                                                                    
Alias           cd                                                                                             Set-Location         

The other command that is necessary is the following:
Get-Help cmdlet

Here is an example

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-Help Get-ChildItem

    Gets the items and child items in one or more specified locations.
    Get-ChildItem [[-Path] ] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction] []
    Get-ChildItem [-LiteralPath] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction] []
    The Get-ChildItem cmdlet gets the items in one or more specified locations. If the item is a container, it gets the items inside the container, known as child items. You can use the Recurse parameter  to get items in all child containers.
    A location can be a file system location, such as a directory, or a location exposed by another provider, such as a registry hive or a certificate store.

    Online version:

    To see the examples, type: "get-help Get-ChildItem -examples".
    For more information, type: "get-help Get-ChildItem -detailed".
    For technical information, type: "get-help Get-ChildItem -full".

Anyways, I use this shell as my main shell that I use for various tasks.  Here is my cmdlet to watch for errors being thrown right before GUI prompt for the APPS password (which is right after the wnoetxu2.sql script has run):

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-ChildItem -Path "\\cmntx02\NoetixViews 6.0.1 - ERP\Installs\NOETIX_SYS_erpdev" -Filter "*xu2.lst" | Select-String -pattern "ORA-"

I invoke this script on my laptop and it peruses the files in the path I have identified.  I suppose you might take a look at this script and think; I will never type a path that long!  Well, if you are not aware, you can drag a file from MS explorer to your shell environment and Windows will “type it in” for you.

Tuesday, February 21, 2012

Approach to Placing xu2 Script Invocations in a wnoetxu2.sql File

In previous entries, I discussed how the template tables are the tables where the metadata associated with the view "classes" are kept.  These classes are instantiated using the Noetix View Administrator  to correspond with the Noetix Views purchased as well as your implementation of Oracle Application (e.g.  the set-ups associated with your company’s ledger(s)/legal entity (ies)/operating unit(s)/organization(s)).

Most of your hook scripts will be associated with modifications to these tables using the wnoetxu2.sql script.  After more than a trivial amount of invocations are created, some logic and structure is advised.

My approach is to sequentially enter scripts dependent on what modification(s) are being made:
1.       Section for packages, functions and procedures:  The motivations for having this section first is that it is more likely to have other sections below depend on it.  

2.       Section for custom and customizing base view:  Base views are Noetix's answer to the inline view/derived table.  It is useful because it is easier to evaluate its performance than an inline view/derived table.  Again, many of the sections following this might need to depend on this section, consequently I see it as important to lump these base view modifications together.
3.       Section for LOV Views:  While I have created custom views which are specifically for generating LOV (very much like Noetix does), I have not really used this section much.  It is driven by customer requirement (and it has not been required).

4.       Section for Clone Views: In general, I like to clone directly off of the "out of the box" Noetix Views as opposed to a Noetix View which has been modified.  It is important to be cognizant of the sequence of invocation regarding the parent and child views associated with a cloning.  This sequence usually can be the difference between an error being thrown or not.

5.       Section for Custom Views Not Cloned:  I just find it valuable to keep these view separate because whenever a migration needs to occur to another version of Noetix Views or Oracle Applications, this section needs special attention.

6.       Standard Modification of Views:  These are the modifications that one can get certified to change. All the other view changes above (except the base view modifications) really are not encouraged to be modified. That is Noetix really does not encourage clones or custom views nor is it taught in their Noetix View Customization Certification.

Here is an example of a wnoetxu2.sql script following this organizational method:

-- packages/functions/procedures

-- Date 21 APR 10 - F. Flintstone

-- Date 28 SEP 10 - F. Flintstone
-- Note:  these scripts create functions that are prerequisites for OE_Lines_Basic.

--custom and customizing base views

-- jan 2010 F. Flintstone

-- Date 13 Jan 2011 - F. Flintstone added

-- Date 15 Jun 10 - F. Flintstone

-- Date 12 Apr 2011 - F. Flintstone

-- Date 12 Apr 2011 - F. Flintstone

-- Date 17 May 2011 F. Flintstone

-- Date 17 Jan 12 F. Flintstone Added

-- Date 02 Feb 12 F. Flintstone Added


--LOV Views

-- feb 2010

--Clone Views

--Mostly clones with some source views included
--because clone script depends on initial changes to source view

-- RJ Adding additional customizations
-- Date 23 OCT 09
--was moved to cloned section because clones
--depend on changes made here prior to
--creation of the clones

-- Date 21 OCT 10 - F. Flintstone
--was moved to cloned section because clone
--depends on changes made here prior to
--creation of the clone

-- Date 14 MAY 10 - F. Flintstone
--was moved to cloned section because clones
--depend on changes made here prior to
--creation of the clones

-- Date 24 AUG 10 - F. Flintstone

-- Date 24 SEP 10 - F. Flintstone

-- Date 22 OCT 10 - F. Flintstone

-- Date 01 NOV 10 - F. Flintstone

-- Date 13 Jan 2011 - F. Flintstone

--17 jan 2012 F. Flintstone

--Custom Views Not Cloned

-- Date 15 Jun 10 - F. Flintstone

-- Date 08 SEP 10 - F. Flintstone

-- Date 10 SEP 10 - F. Flintstone

-- Date 16 SEP 10 - F. Flintstone

-- Date 20 SEP 10 - F. Flintstone

-- Date 01 OCT 10 - F. Flintstone

-- Date 14 OCT 10 - F. Flintstone

-- Date 19 OCT 10 - F. Flintstone

-- Date 12 Apr 2011 - F. Flintstone

-- 17 May 2011 F. Flintstone

-- 18 Aug 2011 F. Flintstone

--18 Oct 2011 F. Flintstone

-- Date 12 Mar 12 - F. Flintstone

--17 jan 2012 F. Flintstone

--Standard Modification of Views

-- Date 23 OCT 09
--was moved to cloned section because clones
--depend on changes made here prior to
--creation of the clones


-- Dec 2009

--F. Flintstonejan 2010

--  Date 07 APR 10 - F. Flintstone

--  Date 08 APR 10 - F. Flintstone

-- Date 20 APR 10 - F. Flintstone

-- Date 28 APR 10 - F. Flintstone

-- Date 29 APR 10 - F. Flintstone

-- Date 30 APR 10 - F. Flintstone

-- Date 14 MAY 10 - F. Flintstone
--was moved to cloned section because clones
--depend on changes made here prior to
--creation of the clones

-- Date 15 Jun 10 - F. Flintstone

-- Date 23 AUG 10 - F. Flintstone

-- Date 26 AUG 10 - F. Flintstone

-- Date 30 AUG 10 - F. Flintstone

-- Date 10 SEP 10 - F. Flintstone

-- Date 13 SEP 10 - F. Flintstone

-- Date 14 SEP 10 - F. Flintstone

-- Date 16 SEP 10 - F. Flintstone

-- Date 30 SEP 10 - F. Flintstone

-- Date 04 OCT 10 - F. Flintstone

-- Date 06 OCT 10 - F. Flintstone

-- Date 07 OCT 10 - F. Flintstone

-- Date 08 OCT 10 - F. Flintstone

-- Date 12 OCT 10 - F. Flintstone

-- Date 19 OCT 10 - F. Flintstone

-- Date 21 OCT 10 - F. Flintstone

--was moved to cloned section because clone
--depends on changes made here prior to
--creation of the clone


-- Date 25 OCT 10 - F. Flintstone

-- Date 07 Jan 2011 - Praveen/F. Flintstone

-- 27 Jun 2011 F. Flintstone

-- 22 Jul 2011 F. Flintstone
-- END Additions

- 25 Aug 2011 F. Flintstone

--01 nov 2011 F. Flintstone

-- end wnoetxu2.sql