Improving the Noetix Views by Logging Tickets

Sometimes I get really busy and I have no time to submit issues to Noetix regarding issues I have identified with one of their views.  Lately, I have been taking the time to submit tickets.

All of us whom use Noetix Views will be better off if we submit tickets and the views improve to address identified issues.

 Here is one I have submitted to Noetix lately based on user ticket I received.

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

Error in Design of the View, AR_Tran_Dist_Sla_Gl_Je

 Oracle Applications 12.1.3/Noetix Views 6.0.1

My Discoverer users identified some missing records from this view when the ar transaction type has no "set of books" associated with it.

Here is my analysis of this problem with the view:

The unique key for the table,AR.RA_CUST_TRX_TYPES_ALL, is not used (only 1/2
of it) when it is joined to the AR.RA_CUSTOMER_TRX_ALL
table like this:

AND TYPE.CUST_TRX_TYPE_ID = TRAN.CUST_TRX_TYPE_ID
AND TYPE.SET_OF_BOOKS_ID = TRAN.SET_OF_BOOKS_ID

I would advocate this addition line (to use the complete unique index for the transaction type table as seen below):

AND TYPE.ORG_ID = TRAN.ORG_ID


If one queries the data dictionary view regarding column indicies, we this:

select
index_name,
column_name
from
all_ind_columns
where
table_name = 'RA_CUST_TRX_TYPES_ALL'
;

index_name column_name
---------------------------------------
RA_CUST_TRX_TYPES_U1 CUST_TRX_TYPE_ID
RA_CUST_TRX_TYPES_U1 ORG_ID


This view does have this line which actually removed some ar transactions from the result set (which does not seem to be a good choice):

AND TYPE.SET_OF_BOOKS_ID = TRAN.SET_OF_BOOKS_ID

I would advocate removing this line.
There is no foreign key constraint on this:

select
index_name,
column_name
from
all_cons_columns
where
table_name = 'RA_CUST_TRX_TYPES_ALL'
and column_name = 'SET_OF_BOOKS_ID'
;

This query above returns a null set....which means that this column is optional and in my implementation, is NULL for some transaction types. 

Oracle 11g Database and Capturing Error Logging During a Noetix Regeneration

During the Noetix Views regeneration process, spool files are created for almost all SQL scripts invoked. Consequently, if one wanted to examined errors during a regeneration process, one could perform a search of these files to assess the errors being thrown and their risk to "messing up" the regeneration process.

Noetix's approach to finding these errors is to invoke during the regeneration process the fnderr.* scripts (the exact script called is dependent on the operating system being used on the server which has the Noetix Administrator installed).  This is an effective approach.

It is important to know that they do not search for Sql*Plus errors (e.g. of the form 'SP-[0-9]\+'), yet it is helpful to be cognizant of these errors and address them (at least in NVA 6.01).

There would be great value in submitting an enhancement request to capture these errors using the fnderr.* scripts.  In any event, I thought it would be fun to use the new SQL*Plus environment variable, errorlogging, and set it to "on" to capture these errors using a different approach.  This environment variable is new to the Oracle 11g database (one can read about this in the SQL*Plus® User's Guide and Reference). The value of this is that the errors are then logged to the SPERRORLOG table (ownership of this table is dependent on the database user account)


Method to track these errors:

1. Update the "set errorlogging on" with the login.sql script. Of course, I am referring to login.sql script in the Noetix Views script home.

2.  Run the Noetix Views regeneration process.

3. Query the table in the NOETIX_SYS account, SPERRORLOG after the regeneration.

This is just another tool to find errors in a regeneration and performs almost identical function to the existing fnderr.* scripts with the exception of the SQL*Plus error searching (e.g. of the form 'SP-[0-9]\+').

Of course, I do all these modifications in non-production environment.

Regeneration of Noetix Views Using SQL*Plus Via Windows Command Line

Some time ago, I always used the Noetix View Administrator to perform all of my regenerations.  In the last year or so, I started running my regenerations using SQL*Plus in Microsoft command line.

Why? Is there anything to gain from this? Not really that I can tell.  The NVA is just a nice GUI tool that presents the spooled script output.  Of course, it has its own tool bar which can speed up things such as updating security and checking the last regeneration statistics.

I use SQL*Plus in Microsoft command line for a number of task such as object compilation (with VIM as my default text editor).  I love the ease, efficiency and extensibility of VIM as a text editor and so I find myself using SQL*Plus often.  I throw all my metadata queries in my default folder and I find a similar degree of efficiency that I would find with a GUI querying tool (but of course, my analysis of big, honking queries is sooooo much easier with a powerful text editor like VIM). 

The main value I see from this approach is that a layer of abstraction is removed and the Noetix Administrator is a little "closer" to the processes associated with the view regeneration. I suppose the difference between the NVA and using SQL*Plus in command mode is perhaps like the difference between driving a car with an automatic transmission vis a vis driving a car with a manual transmission.

Using this approach makes me more cognizant of the processes and scripts which are being invoked during the stages of regeneration.  This has some value.



Here are my brief notes for running only stage 4 of the Noetix Views regeneration process (from a regeneration in a development environment which I would advocate):

 
Navigate to script repository home:

C:\>cd "Program Files (x86)\Noetix Corporation\NoetixViews\Installs\NOETIX_SYS_ERPPJ3"

Start sqlplus command line:

C:\Program Files (x86)\Noetix Corporation\NoetixViews\Installs\NOETIX_SYS_ERPPJ3>sqlplus 

Execute install4.sql

NOETIX_SYS@erppj3> @install4.sql
NoetixViews Installation Process (4)

Noetix Application                               - NoetixViews
"          Scripts Version                         - 6.0.1.922
"          Metadata Version                     - 6.0.1.922
Application Object Library User          - APPLSYS
Noetix SysAdmin User                        - NOETIX_SYS

Enter noetix sys password:
NOETIX_SYS@erppj3> Please enter the Oracle User Password for the Noetix SysAdmin User Enter Password:

Enter connect string:

NOETIX_SYS@erppj3> Please enter the Database Connect String for the Database default [ERPPJ3]: ERPPJ3
NOETIX_SYS@erpjp3> ******************************** Value Entered: ERPPJ3 

Enter tablespace for noetix sys database account:

Enter default language:

  

Identify views to be regenerated:

Edit prefix (tupdprfx.sql) files?

Regeneration preferences (help files and answer builder):

Confirm regeneration preferences:

Enter APPS password:

NOETIX_SYS@erppj3> Enter Password for APPS:
Connected.

Project multicurrency option entered:

Success confirmation:

Discoverer Digression: Changing The Owner of a Workbook in a Development Environment

When it comes to this blog, I try to consistently blog on just Noetix related issues.

Today, I thought it would be helpful to some people to document how I change ownership of Discoverer workbooks quickly in a Development environment (The protocol that Oracle advices to do this is to use the Discoverer Administrator). 

Whenever you are touching the EUL schema tables, it is always important to be cognizant that it is best practice (and supported) to use the Discoverer Administrator to perform the task you are intending to perform because you can easily render the EUL unusable).

Here is my script:

update eul5_us.eul5_documents
set doc_eu_id =  :target_eu_id
where
doc_eu_id in
(select
euluser.eu_id
from
eul5_us.eul5_eul_users euluser,
applsys.fnd_user fu
-- oracle apps Disco implementation
where
euluser.eu_username = '#'|| to_char(fu.user_id)
and fu.user_name in (list of "from" users));



Notes: one can identify the target user id by using this statement:

NOETIX_SYS@erptst> variable target varchar2(100);
NOETIX_SYS@erptst> exec :target := 'DISCOADMIN'

PL/SQL procedure successfully completed.

NOETIX_SYS@erptst> select
  2  fu.user_name oracle_app_user,
  3  euluser.eu_id
  4  from
  5  eul5_us.eul5_eul_users euluser,
  6  applsys.fnd_user fu
  7  where
  8  euluser.eu_username = '#'|| to_char(fu.user_id)
  9  and fu.user_name in (:target /* e.g. 'discoadmin'*/)
 10  /

ORACLE_APP_USER             EU_ID
=========================================================
DISCOADMIN                  308285


Of course, your EUL owner schema would more than likely be different from mine above.

That is all.

Debugging Noetix View Administrator's View Regeneration Process

I find that after a cloning of an instance (especially a development or testing environment), my xu2 or xu5 invocations cause various errors during a Noetix View Administrator (NVA) view regeneration.  

Typically, when working in newly cloned instance (especially in a development or testing environment), there are set-ups associated with my new development (e.g. grants, missing objects like a package which a new xu2 or new xu5 script might depend) that should be done before one would want to run a regeneration of the Noetix Views.  In most cases, it would makes sense to have a MD120 documents for all new development (set-up and installation document created as you work on new development ....why would you not?).  If you do not document these set-ups, I find that this is common source of problem when I receive errors that occur when the makeview*.sql files are executed.

The standard xu2 or xu5 spool files are pretty easy to read and search for errors (see other posts), but I wanted to post on situations that seem more opaque.  Specifically, what if you are not receiving any errors in your xu2 or xu5 spool files, yet the Noetix View Administrator is throwing an error during the regeneration process. 

The purpose of this post is to document an approach to addressing these opaque situations (no xu2 or xu5 error, yet the regeneration process is failing).

Here are simple things that can be done to address this:

1.       Other Sessions causing problems: I have noticed that a number of problems with the NVA session terminating can be attributed with another user's database sessions with locks on various records in the Noetix schema.  With this problem, one would want to resolve/identify how to have those locks removed (e.g. have users complete their work and commit or rollback).

2.        Identify the last Script Invoked prior to the termination of the NVA session: I am assuming that you are working with an NVA installed on a Window server.  With this scenario, you might want to investigate what script was last invoked before the Noetix View Administrator terminated. I just start a Windows Explorer session (using a details view) and find the last spooled file (*.lst) updated in the script home.

3.       Look at the finderr.lst file:  Another approach that one can use is a variation of using the “seeded” approach of using the error messages at the end of an NVA regeneration process (specifically stage 4).  Suppose you logged out of the NVA and have returned to your work later.  One can just look at the finder.lst file (again in the script home file) and exhaustively examine every error thrown.

4.        Look at the last text associated with terminal output (with the NVA) and query the file using powershell (comes with windows 7 desktops):

e.g. find the SQL exception

Get-ChildItem -Path "\\server_name\Noetix Corporation\NoetixViews\Installs\NOETIX_SYS_ERPTST" -Filter "*.lst" | Select-String -pattern "ORA-[0-9]+"

e.g. find the last terminal output text

Get-ChildItem -Path "\\server_name\Noetix Corporation\NoetixViews\Installs\NOETIX_SYS_ERPTST" -Filter "*.sql" | Select-String -pattern " Add columns from base tables”

5.       Leverage the terminal output script, noetixd.sql.  This is a very uncomplicated script which one can change the level of the NVA’s terminal output for the purpose of debugging.  Of course, make a copy of the original file and then enable the level of debugging that one wants.  I personally find modifying the level of output very interesting (just to be cognizant of what scripts are being invoked at any given time).  Chances are, if you are seriously considering this choice in terms of your debugging, you probably should also be considering submitting a help desk ticket to Noetix.

Happy debugging!

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. 

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:

UPDATE N_VIEW_COLUMNS
SET OMIT_FLAG ='N'
WHERE COLUMN_NAME = 'Z$INVX0_Items'
AND column_label = 'MTL_SYSTEM_ITEMS_B'
AND (view_label LIKE 'AR%'
OR view_label LIKE 'OE%'
OR view_label LIKE 'PO%')
;
COMMIT;

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