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.

Analyzing a View to Understand Unexpected/Erroneous Behavior

It is fairly common for me to analyze a single record where unexpected behavior is occurring with a particular view.

Here is a trivial view (this is not a Noetix View):

CREATE OR REPLACE VIEW ONTX0_ORDER_LINE
AS
SELECT
    HEAD.ORDER_NUMBER,
    ITEM.SEGMENT1 ITEM,
    ITEM.DESCRIPTION ITEM_DESCRIPTION,
    LINE.ORDERED_QUANTITY
FROM
    ONT.OE_ORDER_HEADERS_ALL HEAD,
    ONT.OE_ORDER_LINES_ALL LINE,
    INV.MTL_SYSTEM_ITEMS_B ITEM,
    NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
 WHERE 1=1
    AND LINE.ORG_ID=XMAP.ORG_ID
    AND LINE.HEADER_ID = HEAD.HEADER_ID
    AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;


Now suppose through testing, we know that we receive multiple records in our results set with ORDER_NUMBER = '30066929', though we know there should only be one record.  We know this to be the case with most records, yet this record is the first one which comes back in our result set.


What is the most efficient approach to troubleshooting this problem?  Again, with our trivial view, we can find the solution quickly; yet there is value in thinking about this problem as a process and trying to refine/speed-up this process.

As with most problem solving, one should identify the problem precisely:

"For ORDER_NUMBER, '30066929', I am getting three records when I anticipate one record.  In fact, I see this triplication problem systemically".

Now that this problem has been identified, it is good to look at the most recent changes and rollback them (if the issue is a new issue).

If this approach does not work, I wanted to present my quick, brute force solution.


    1. Make a copy of the view definition (but just keep the SQL statement).


    SELECT
        HEAD.ORDER_NUMBER,
        ITEM.SEGMENT1 ITEM,
        ITEM.DESCRIPTION ITEM_DESCRIPTION,
        LINE.ORDERED_QUANTITY
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
        INV.MTL_SYSTEM_ITEMS_B ITEM,
        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;



    2. Next, comment all line except the SELECT, FROM and WHERE lines as follows:


    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
    FROM
--        ONT.OE_ORDER_HEADERS_ALL HEAD,
--        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
--        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
--        AND LINE.ORG_ID=XMAP.ORG_ID
--        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;

      If you are using a text editor and it happens to be VIM, you can just invoke this EX command:

        %s/^/--/c

        Where % identifies the range of lines where I intend to substitute characters (all lines).
        The '^' character is an anchor conveying to VIM that I want to find the beginning of the line.
        The '--' characters are going to placed on all lines in this range of lines.
        The 'c' option allows me to confirm which lines I want to perform this replace.

    3.  Add a condition to the where clause to more closely examine the error/problem identified.

        In this case, it should be as follows, "AND HEAD.ORDER_NUMBER = '30066929'". Since our condition is on the header table, let us uncomment all lines associated with these two tables in both the from and where clause.


        Also, we should have visibility of the primary key of the driving table (here it is "LINE.LINE_ID").  Thus we add this line in the select clause.

      
    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
        LINE.LINE_ID
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
--        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
--        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
--      testing
        AND HEAD.ORDER_NUMBER = '30066929';

    Optimally, our condition should be on the driving table (LINE), and it should restrict the result set for the driving table to one record.  In this trivial case, we are not doing this (one could have multiple lines for a given header).



    4. Next, we uncomment tables in the From clause and their associated Where clause lines.  Here, we do this with the XMAP base view:


    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
        LINE.LINE_ID
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
--      testing
        AND HEAD.ORDER_NUMBER = '30066929';


    5. After the XMAP base view being uncommented, we procedd to uncomment the ITEM table.  Here we will find that we are missing the organization_id column in the join between LINE and ITEM.


I have found this approach can work fairly quickly (20 minutes to quickly assess/analyze a view).

While this is fairly common task, it is important to approach it with speed and precision and clarity.

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.