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!