Noetix 6.0.1 to 6.4.1 Noetix Views Migration ~ Running a Seeded Regeneration

I have been busy working on my Noetix 6.0.1 to 6.4.1 Noetix Views migration and I am supposed to crank through this project pretty quickly. I have been setting up my test environment.  My approach to setting-up my 6.4.1 Noetix View schema is as follows:

• Run a 6.4.1 Noetix Views regeneration “out of the box”on an existing Noetix schema (6.0.1 to be exact).
• Run a 6.4.1 Noetix Views regeneration with all my xu2 hook scripts.
• Run a 6.4.1 Noetix Views regeneration with all my xu5 hook scripts.

With the “out of the box” regeneration, I noticed that popview.sql and ycrcal.sql appear to have bugs (Magnitude supplied me with a corrected popview.sql script).

Here is the error for the ycrcal.sql:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 ( l_effective_date,
20 TRIM(to_char(l_effective_date,'MONTH')),
21 TRIM(to_char(l_effective_date,'DAY')),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'WW')),
24 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'Q')),
25 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'J')),
26 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'MM')),
27 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'W')),
28 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'DD')),
29 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'D')));
30 l_effective_date := l_effective_date + 1;
31
32 exit when l_effective_date = to_date('01-JAN-2100','DD-MON-YYYY') ;
33 end loop;
34 commit;
35 end;
36 /
declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 6

This install script is taking l_effective_date, which is a date and casting it as date (I have not seen that done successfully nor is this a great idea).

Here is a reproduction of that error in a more trivial example:

NOETIX_SYS@erp>select to_date(sysdate,'dd-mon-yyyy')
2 from
3 dual;
select to_date(sysdate,'dd-mon-yyyy')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

When I correct this casting of a date as a date issue, the script looks like this:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 (l_effective_date,
20 to_char(l_effective_date,'MONTH'),
21 to_char(l_effective_date,'DAY'),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(l_effective_date,'WW')),
24 to_number(to_char(l_effective_date,'Q')),
25 to_number(to_char(l_effective_date,'J')),
26 to_number(to_char(l_effective_date,'MM')),
27 to_number(to_char(l_effective_date,'W')),
28 to_number(to_char(l_effective_date,'DD')),
29 to_number(to_char(l_effective_date,'D')));

Presently, I am waiting for Magnitude to acknowledge the problem and fix it.

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

--This is a re-post

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 and did not do anything with it. 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.

Anyway, 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

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.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

Get-ChildItem -Path "\\cmntx02\NoetixViews 6.0.1 - ERP\Installs\NOETIX_SYS_erpdev" -Filter "*xu2.lst" | Select-String -pattern "ORA-"

Explanation: 

  

  The Get-ChildItem cmdlet gets the items in one or more specified locations. In this context it obtains all the xu2.lst spooled files and pipes the results to the Select-String cmdlet which finds matches to the 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.

                                                                       

Administration Digression: My Favorite Kind of New Oracle Function, LISTAGG

With the administration of my Discoverer / Noetix environment, I find that sometimes I need to compose comma separated concatenated list of some text column (e.g. for the purpose of creating documentation).

In Oracle Database 11g release 2, the LISTAGG function was introduced which is well suited for this type of task (and no custom function needs to be created).


This function has two flavors, analytic (non-aggregate) and aggregate.Here is a description of the function (see link above for full description in 11g R2):


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

-expression is the expression one wishes to concatenate
-delimeter is self-explanatory (e.g. ',')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The analytic flavor is invoked as follows:

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

 [OVER (PARTITION BY partition_columns)]


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


With some documentation I am creating, I need to create a concatenated list of Discoverer responsibiities.


Here is my invocation of this function using the aggregate version of this function:


 EUL5_US@erpdev> SELECT LISTAGG(responsibility_name, ', ') WITHIN GROUP (
  2  ORDER BY responsibility_name) Responsibility_list
  3  FROM applsys.fnd_responsibility_tl
  4  WHERE 1      =1
  5  AND language = SYS_CONTEXT ('USERENV', 'LANG')
  6  AND regexp_like( responsibility_name, 'Discoverer.+ - \w{2}')
  7  /

Responsibility_list
------------------------------------------------------------------------
Discoverer Commissions - CS, Discoverer Viewer - CA, Discoverer Viewer - CM, Discoverer Viewer - CS, Discoverer Viewer - CW

Concurrent Program Query to View Scheduled and Pending Noetix Concurrent Programs

Simple concurrent program query to view scheduled and pending Noetix concurrent programs:

SELECT fcr.*
FROM applsys.fnd_concurrent_requests fcr,
  applsys.fnd_application fa
WHERE 1                        =1
AND fcr.program_application_id = fa.application_id
AND fa.application_short_name  = 'XXNAO'
AND fcr.phase_code             = 'P'
AND fcr.status_code            = 'Q'

This is nice to check to monitor these programs through SQL.

Digression: Querying for Organizations, Operating Units, Legal Entities and Ledgers in an R12 Environment

Invariably in an Oracle Applications environment, one needs to be aware of and know the relationship between organizations, operating units, legal entities and ledgers.

This query is just a variation of the apps.org_organization_definitions Oracle Applications view whose results provide this insight.  I take no credit for this:

 select
 hou.organization_id organization_id,
    mp.organization_code organization_code,
    lgr.ledger_id,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information3), to_number(null)) operating_unit,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information2), null) legal_entity
  from hr.hr_all_organization_units hou,
    hr.hr_organization_information hoi1,
    hr.hr_organization_information hoi2,
    inv.mtl_parameters mp,
    gl.gl_ledgers lgr
  where hou.organization_id = hoi1.organization_id
  and hou.organization_id   = hoi2.organization_id
  and hou.organization_id   = mp.organization_id
  and hoi1.org_information1 = 'INV'
  and hoi1.org_information2 = 'Y'
  and ( hoi1.org_information_context || '') = 'CLASS'
  and ( hoi2.org_information_context || '') ='Accounting Information'
  and to_number(decode(rtrim(translate(hoi2.org_information1,'0123456789',' ')), null, hoi2.org_information1,-99999)) = lgr.ledger_id
  and lgr.object_type_code ='L'
  and nvl(lgr.complete_flag,'Y') ='Y'

Troublshooting The ORA-04021 Error in a Development Environment

I notice that my regeneration was stalling through the install4.sql script in a development environment. Finally, the regeneration timed out.

Next, I check for the last spool file and it is the file, ycrenvph.lst. This script creates the header for the package, noetix_env_pkg.

I notice the error, ORA-04021 being thrown. This is the timeout error.

I query the v$session sessions owned by apps or noetix_sys which could be locking this package. Nothing stands out.

Next, I query the V$access table and I notice that there is session that has a lock on this object.

SELECT * FROM V$ACCESS WHERE OBJECT = 'NOETIX_ENV_PKG';

It is important to be cognizant of what sessions you intend to kill or let run its course.  It is important to know the details regarding a specific session (who, what, where, how and why) before any course of action is taken.  No matter what environment one is in, it usually is better to let something run its course than to initiate terminating a session.   

Next, I find this session's detail:

SELECT * FROM V$SESSION WHERE SID = 1098;

and kill the session.

ALTER SYSTEM KILL SESSION '1098,61537';

The Lazy Noetix Developer: More on Quick Error Checking of XU2/XU4 Scripts

A lot of Noetix people have migrated to Noetix Workbench for the modification
of their Noetix View environment.  We have not. 

Anyways, I am a Vimmer and a Noetix dude (administration, support and developer) for a
6.0.1 environment and I have found that this process below to work quite well for
capturing errors with my customized xu2/xu4 scripts:

1. Type out xu2/xu4 script modifications.  Usually, I have an existing xu2/xu4
file.

2. With Vim, I just place a mark at the beginning of my new DML. Marking just is a
way to reference a row in a document for easy, quick access. Typically, I use
the mark, b, which I associated with the beginning of my noteworthy selection.

3. When I am done with my modifications to my xu2/xu4 script, I use the Vim ex
command:

:'b,$s/^COMMIT;/--COMMIT;/g

Explanation:
-'b,$ is a range for the s (substitute) command.
-^ is an anchor.  Anchors are used in regular expressions to identify the location.  In this context, the anchor, ^, is used to indicate the beginning of a line.
-'^COMMIT' is the searched for expression.
-'--COMMIT' is the replaced texts.

I perform this substitute for all of the commits so that I can rollback the command after it is executed. section.

4. Yank the text from this modification section using this Vim ex
command:

:'b,$y*

This just means, from the mark, b, to the end of the document, $, yank
(translation place contents in register).  With my Vim instance, *, refers to
the Windows clipboard.  Thus, this command moves the text from my selected
area and places this in the clipboard. 

5. Start-up Windows Powershell (which I have set my profile variable,
$profile, to automagically start-up SQL Plus). I issue the spool C:\temp.lst
command in SQL Plus.

6. Paste my new modification script to SQL Plus using the Noetix
System user account.

7. Issue the spool off command.

8. Undo the substitution command in step #3 using the u normal mode
command (undo).  This results in the COMMIT commands being non-commented.

9. Perform a rollback in SQL Plus so that my DML does not get committed.

9. Lastly, I peruse my spool file, C:\temp.lst, for errors using the Vim
normal mode search command:

 /ORA-[0-9]\+

In summary, this is a nice, efficient way to capture errors in new DML
xu2/xu4 scripts without needing to kick-off a regeneration.