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:

SELECT *
FROM DBA_CONS_COLUMNS
WHERE 1        =1
AND OWNER      = 'NOETIX_SYS'
AND TABLE_NAME = 'N_VIEW_COLUMN_TEMPLATES';

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, http://blogs.technet.com/b/heyscriptingguy/, 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

NAME

    Get-ChildItem

   

SYNOPSIS

    Gets the items and child items in one or more specified locations.

   

   

SYNTAX

    Get-ChildItem [[-Path] ] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction] []

   

    Get-ChildItem [-LiteralPath] [[-Filter] ] [-Exclude ] [-Force] [-Include ] [-Name] [-Recurse] [-UseTransaction] []

   

   

DESCRIPTION

    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.

   

RELATED LINKS

    Online version: http://go.microsoft.com/fwlink/?LinkID=113308

    about_Providers

    Get-Item

    Get-Alias

    Get-Location

    Get-Process

REMARKS

    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.

                                                                       

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

@N_CS_OE_UTL_PKG_HDR_xu2.pkh;

@N_CS_OE_UTL_PKG_HDR_xu2.pkb;

-- Date 28 SEP 10 - F. Flintstone

-- Note:  these scripts create functions that are prerequisites for OE_Lines_Basic.

--

@N_OE_GET_TRIPS.sql;

@N_OE_GET_DELIVERIES.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--custom and customizing base views

-- jan 2010 F. Flintstone

@OE_Lot_Serial_Base.sql;

-- Date 13 Jan 2011 - F. Flintstone added

@OE_Delivery_Base_xu2.sql;

-- Date 15 Jun 10 - F. Flintstone

@INV_Lot_Status_Hist_Base_xu2.sql;

-- Date 12 Apr 2011 - F. Flintstone

@PO_Item_Acct_Cost_Base_xu2.sql;

-- Date 12 Apr 2011 - F. Flintstone

@GMF_Item_Acct_Costs_Base_xu2.sql;

-- Date 17 May 2011 F. Flintstone

@OE_Coil_Carrier_Base_xu2.sql;

@OE_Header_Short_Text_Base_xu2.sql;

-- Date 17 Jan 12 F. Flintstone Added

@AR_Customers_Base_xu2.sql;

@OE_Contacts_Base_xu2.sql;

-- Date 02 Feb 12 F. Flintstone Added

@GMD_Category_Processing_Base_xu2.sql;

@GMD_Category_Costing_Base_xu2.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--LOV Views

-- feb 2010

@AR_Lov_Cust_Bill_Ship.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--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

@OE_Lines_xu2.sql;

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

-- Date 21 OCT 10 - F. Flintstone

@GMD_Formulas_xu2.sql;

--was moved to cloned section because clone

--depends on changes made here prior to

--creation of the clone

-- Date 14 MAY 10 - F. Flintstone

@GME_Batch_Headers_xu2.sql

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

-- Date 24 AUG 10 - F. Flintstone

@INV_Item_Onhand_By_Lot_Loc_xu2.sql;

-- Date 24 SEP 10 - F. Flintstone

@OE_Lines_Basic_xu2.sql;

-- Date 22 OCT 10 - F. Flintstone

@GME_Batch_Headers_Summary_xu2.sql;

-- Date 01 NOV 10 - F. Flintstone

@GMD_Product_Formulas_xu2.sql;

-- Date 13 Jan 2011 - F. Flintstone

@OE_Lines_Shipping_Basic_xu2.sql;

--17 jan 2012 F. Flintstone

@INV_CS_Lot_Onhand_By_Batch_xu2.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--Custom Views Not Cloned

-- Date 15 Jun 10 - F. Flintstone

@INV_Lot_Status_History_xu2.sql;

@INV_Alias_Lot_Trnsctns_xu2.sql;

@INV_Batch_Transactions_xu2.sql;

-- Date 08 SEP 10 - F. Flintstone

@AR_CS_Commission_Rates_xu2.sql;

-- Date 10 SEP 10 - F. Flintstone

@AR_CS_Collection_Notes_xu2.sql;

-- Date 16 SEP 10 - F. Flintstone

@GMF_CS_Item_Cost_xu2.sql;

-- Date 20 SEP 10 - F. Flintstone

@INV_Onhand_Period_End_xu2.sql;

-- Date 01 OCT 10 - F. Flintstone

@OE_CS_Order_Book_Changes_xu2.sql;

-- Date 14 OCT 10 - F. Flintstone

@GMD_CS_Customer_Spec_Reviews_xu2.sql;

-- Date 19 OCT 10 - F. Flintstone

@GMD_CS_Cust_Item_Spec_Reviews_xu2.sql;

-- Date 12 Apr 2011 - F. Flintstone

@GMF_Processing_WIP_Batches_xu2.sql;

@PO_Inter_Org_Shipments_xu2.sql;

--@GMD_Billet_Formula_Base_xu2.sql;

--@GMD_CS_Indented_Formulas_xu2.sql;

-- 17 May 2011 F. Flintstone

@OE_CS_Transportation_xu2.sql;

@OE_CS_List_Lines_All_xu2.sql;

-- 18 Aug 2011 F. Flintstone

@AR_CS_Cust_Freight_Details_xu2.sql;

--18 Oct 2011 F. Flintstone

@GME_CS_MES_Melt_Batch_Xfer_xu2.sql;

@GME_CS_MES_Proc_Batch_Xfer_xu2.sql;

@GME_CS_MES_Roll_Batch_Xfer_xu2.sql;

-- Date 12 Mar 12 - F. Flintstone

@OE_CS_Tads_Loads_Details_xu2.sql;

--17 jan 2012 F. Flintstone

@EAM_CS_Asset_Log_xu2.sql;

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--Standard Modification of Views

-- Date 23 OCT 09

@INV_Items_xu2.sql;

--@OE_Lines_xu2.sql;

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

@OE_Order_Territories_xu2.sql;

@OE_Backlog_xu2.sql;

-- Dec 2009

@EAM_Work_Orders_xu2.sql;

@PO_Blanket_PO_Lines_xu2.sql;

@OE_Pick_Slips.sql;

@OE_Backordered_Picking_Lines.sql;

@OE_Returns.sql;

@OE_Book_Bill_Backlog.sql;

@OE_Deliveries_xu2.sql;

@PO_PO_Lines.sql;

@RA_Customers_xu2.sql;

--F. Flintstonejan 2010

@OE_Picking_Line_Details_xu2.sql;

--  Date 07 APR 10 - F. Flintstone

@EAM_Asset_Downtime_xu2.sql;

@EAM_Work_Requests_xu2.sql;

@OE_Delivery_Departure_Details_xu2;

@OE_Departures_xu2;

--  Date 08 APR 10 - F. Flintstone

@CS_Service_Requests_xu2.sql;

@INV_Lot_Transactions_xu2.sql;

-- Date 20 APR 10 - F. Flintstone

@EAM_Asset_Activity_Assoc_xu2.sql;

-- Date 28 APR 10 - F. Flintstone

@EAM_Work_Order_Resources_xu2.sql;

-- Date 29 APR 10 - F. Flintstone

@AP_Invoice_Pay_Schedules_xu2.sql;

-- Date 30 APR 10 - F. Flintstone

@OE_Order_Invoices_xu2.sql;

@OE_Returns_xu2.sql;

-- Date 14 MAY 10 - F. Flintstone

--@GME_Batch_Headers_xu2.sql

--was moved to cloned section because clones

--depend on changes made here prior to

--creation of the clones

-- Date 15 Jun 10 - F. Flintstone

@INV_Customer_Items_xu2.sql

-- Date 23 AUG 10 - F. Flintstone

@PO_Receipts_xu2.sql;

-- Date 26 AUG 10 - F. Flintstone

@AR_Disputes_xu2.sql;

-- Date 30 AUG 10 - F. Flintstone

@AR_Transaction_Line_Dtls_xu2.sql;

@AR_Tran_Dist_SLA_GL_Je_xu2.sql;

-- Date 10 SEP 10 - F. Flintstone

@AR_Cash_Receipts_xu2.sql;

-- Date 13 SEP 10 - F. Flintstone

@AP_Checks_xu2.sql;

-- Date 14 SEP 10 - F. Flintstone

@AR_Customer_Balance_Aging_xu2.sql;

-- Date 16 SEP 10 - F. Flintstone

@INV_Item_Onhand_By_Lot_xu2.sql;

-- Date 30 SEP 10 - F. Flintstone

@OE_Delivery_Assignments_xu2.sql;

-- Date 04 OCT 10 - F. Flintstone

@OE_List_Lines_xu2.sql;

-- Date 06 OCT 10 - F. Flintstone

@AP_Inv_Dist_SLA_GL_Je_xu2.sql;

-- Date 07 OCT 10 - F. Flintstone

@CS_Charge_Details_xu2.sql;

-- Date 08 OCT 10 - F. Flintstone

@GMD_QC_Specifications_xu2.sql;

-- Date 12 OCT 10 - F. Flintstone

@GMD_QC_Results_xu2.sql;

@GMD_QC_Tests_xu2.sql;

@INV_Item_Catalogs_xu2.sql;

-- Date 19 OCT 10 - F. Flintstone

@GMD_QC_Cust_Deviation_Samples_xu2.sql;

-- Date 21 OCT 10 - F. Flintstone

@INV_Category_xu2.sql;

--@GMD_Formulas_xu2.sql;

--was moved to cloned section because clone

--depends on changes made here prior to

--creation of the clone

@GMD_Recipes_xu2.sql;

@GMD_Routings_xu2.sql;

-- Date 25 OCT 10 - F. Flintstone

@INV_Lot_Details_xu2.sql;

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

@OE_Orders_xu2.sql;

-- 27 Jun 2011 F. Flintstone

@GME_Batch_Step_Rsrc_Actvty_xu2.sql;

@GMF_Resource_Costs_xu2.sql;

-- 22 Jul 2011 F. Flintstone

@B24485.sql;

-- END Additions

- 25 Aug 2011 F. Flintstone

@CSG_Charge_Details_xu2.sql;

--01 nov 2011 F. Flintstone

@PO_Vendors_xu2.sql;

@GL_Journal_Entries_xu2.sql;

-- end wnoetxu2.sql

Developing a Workaround for Date Columns Which are Indexed

With the Noetix EUL Generator for Discoverer, all columns which are indexed are associated with a class.  Classes are used to generate LOVs.  While this approach is generally helpful, end users find that parameters created on these indexed date columns "muck up" the select by calendar feature.  Here is an example of this:

 

Notice that only the current date is available.

Proposed solution:

1. Contact Noetix and request an enhancement.  I probably should do this at some point.
2. Manually correct this after a regeneration using the Discoverer Administrator.    

select

count(1)

from

eul5_expressions

where 1=1
and exp_type = 'CO'

and exp_data_type =4

and it_dom_id is not null;

I calculate 2,255 scenarios where this problem occurs.  I would rather not take this approach.

3. Create a DML script which updates these columns that fit this scenario.

Pros:  Efficient, if the table(s) are modified correctly.

Cons:  Possibly corrupt the EUL. 

Planning for approach #3:

I modified a date column which is indexed (back and forth) using the Discoverer Administrator and the two changes I have identified are as follows:

Nullify the EUL5_EXPRESSIONS.IT_DOM_ID column and increment the NOTM column (number of times modified).

I check the EUL5_EXPRESSIONS table for constraints associated with these columns using this query:

SELECT cols.table_name,

  cols.column_name,

  cols.position,

  CONS.constraint_type

FROM all_constraints cons,

  all_cons_columns cols

WHERE cols.table_name    = 'EUL5_EXPRESSIONS'

AND cons.constraint_name = 'EUL5_IT_DOM_FK'

AND cons.owner           = 'EUL5_US'

AND cons.constraint_type = 'R'

AND cons.constraint_name = cols.constraint_name

AND CONS.OWNER           = COLS.OWNER

ORDER BY cols.table_name,

  cols.position;

I do notice the foreign key constraint, EUL5_IT_DOM_FK, associated with the EUL5_EXPRESSIONS table.  Consequently, if the IT_COM_ID  is not null, then there needs to be referential integrity with the EUL5_DOMAINS table (the DOM_ID column to be exact).

Based on this analysis, I execute and commit this DML script:

update eul5_expressions

set it_dom_id = null,

notm = notm + 1

where 1=1
and exp_type = 'CO'

and exp_data_type =4

and it_dom_id is not null;

I presently am testing this in a development environment and I would like to confer with other Discoverer users (e.g. using the Discoverer Forum).

Summary

Updating the EUL using scripts is usually to be avoided.  In fact, it should be a last resort.  I am going to contact Noetix on this, but I suspect I will be left having to implement approach 3 (after more research and testing).