When and How to Create View Joins in Noetix

Before going into describing how to create joins in Noetix, I want to express my philosophy on joins and Noetix Views.  

First, Noetix Views in many ways are designed to encapsulate the needs of a reporting requirement(s).  If one goes into the Help file (or Noetix Search) for a given view, a description of the view’s content is provided.  It has a very specific business need and the view was designed to enable users to achieve this.  My point is that more often than not, the view should satisfy an entire requirement.

While a given Noetix View should meet a given business requirement (one triangulates between the user’s requirement/Noetix’s description of the view and the DDL), sometimes Oracle Application’s shared entities are lacking (e.g. Item master, custom master, vendor master) in a given Noetix View. 

This type of scenario in my opinion is the best time to use a view Join.  Let’s investigate.  An example of this might be INV_Items (item master view) and the OE_Lines view.

First what type of join would this be; an inner join or outer join?

Let us confirm that there is a not null constraint on inventory_item_id.

NOETIX_SYS@erptst> SELECT search_condition

  2  FROM dba_constraints

  3  WHERE owner    = 'ONT'

  4  AND table_name = 'OE_ORDER_LINES_ALL';

SEARCH_CONDITION

================================================================================

"LINE_ID" IS NOT NULL

"HEADER_ID" IS NOT NULL

"LINE_TYPE_ID" IS NOT NULL

"LINE_NUMBER" IS NOT NULL

"INVENTORY_ITEM_ID" IS NOT NULL

"SHIPMENT_NUMBER" IS NOT NULL

"CREATION_DATE" IS NOT NULL

"CREATED_BY" IS NOT NULL

"LAST_UPDATE_DATE" IS NOT NULL

"LAST_UPDATED_BY" IS NOT NULL

"LINE_CATEGORY_CODE" IS NOT NULL

"OPEN_FLAG" IS NOT NULL

"BOOKED_FLAG" IS NOT NULL

13 rows selected

Sure enough, there is.  Consequently, an inner join appears to be appropriate.

Let us look at an ERD of these two views:

In Discoverer nomenclature, we would make the INVX0_Items view the master view and the ONTX0_Lines view as the details view (one-to-many relationship with the minimum cardinality on the lines view being zero).

Okay, how do we join them?  Noetix advises that if a table is a base table (integral to the view and not outer joined within the view) then it can be a candidate for creating a Z$ column (the join columns).  The way to communicate this to the Noetix View Administrator tool is to set the base_table_flag = ‘Y’.

When we query the tables that meet this criteria on the only query block available for this view, we see the following results:

NOETIX_SYS@erptst> select

  2  view_label,

  3  query_position,

  4  base_table_flag,

  5  table_name

  6  from

  7   n_view_table_templates

  8   where

  9   view_label like 'OE_Lines'

 10  and base_table_flag = 'Y';

VIEW_LABEL                     QUERY_POSITION B TABLE_NAME

============================== ============== = ==============================

OE_Lines                                    2 Y HZ_CUST_ACCOUNTS

OE_Lines                                    2 Y OE_ORDER_HEADERS_ALL

OE_Lines                                    2 Y MTL_SYSTEM_ITEMS_B

OE_Lines                                    2 Y OE_ORDER_LINES_ALL

OE_Lines                                    2 Y HZ_PARTIES

OE_Lines                                    2 Y OE_OU_ACL_MAP_BASE

6 rows selected

Consequently, we see that MTL_SYSTEM_ITEMS_B has been designated as a candidate for a join column.

Okay, why does it never get beyond candidacy (this is my US election plug)?  Let us look at the n_view_tables (the instantiation of the template table, n_view_table_templates).

NOETIX_SYS@erptst> SELECT view_name,

  2    key_view_name,

  3    column_name,

  4    omit_flag

  5  FROM n_view_columns

  6  where

  7   view_name like 'ONTX0_Lines'

  8  and key_view_name is not null

  9  ORDER BY 1,

 10    2;

VIEW_NAME                      KEY_VIEW_NAME                  COLUMN_NAME                    O

============================== ============================== ============================== =

ONTX0_Lines                    INVX0_Items                    Z$INVX0_Items                  Y

ONTX0_Lines                    ONTX0_Lines                    Z$ONTX0_Lines

ONTX0_Lines                    ONTX0_Orders                   Z$ONTX0_Orders

ONTX0_Lines                    RAAX0_Customers                Z$RAAX0_Customers              Y

Notice that the join procedures in the NVA concluded that the join associated with the customer master and the item master should be omitted (omit_flag = ‘Y’). 

In a development environment, I executed this xu5 script and I have been examining the resulting joins that are now exposed/generated without having to manually create them. 

-- output to Z_Columns_xu5.lst file
@utlspon Z_Columns_xu5

update n_view_columns
set omit_flag = to_char(null)
where column_expression ='rowid'
and column_type = 'GEN'
and group_by_flag = 'N'
and application_instance = 'X0';

commit;
 
@utlspoff

At this time I would not want to run a script like this in production (more testing and perhaps discussion with Noetix).  This script did provide joins that I did desire with no negative consequences that I can see as of now.  As you can see, this script only changes the 'X0' instance.

Typically, I have seen and have been trained to give up if the base_table_flag approach fails (or use an XU5 script to manually enter the join column and then I have to manually create the join in my Discoverer environment). 

I want to explore this scenario in future posts. 

I know that if I update the non-template table to set the omit_flag in the n_view_columns table, that Noetix will then create a join.   I have tested this approach with some views and this works.  I would like to discuss this with Noetix’s Help staff to see what they have to say regarding this.

My suspicion is that this join is omitted because both view_templates need to be shared by each other’s role label:

NOETIX_SYS@erptst> select

  2  role_label

  3  from

  4  n_role_view_templates

  5  where

  6  view_label = 'INV_Items';

ROLE_LABEL

========================

DEPOT_REPAIR

FIELD_SERVICE

INSTALL_BASE

INVENTORY

ORDER_ENTRY

PURCHASING

RECEIVABLES

SERVICE_CONTRACTS

TELESERVICE

PRODUCT_DEVELOPMENT

PROCESS_EXECUTION

11 rows selected.

NOETIX_SYS@erptst> select

  2  role_label

  3  from

  4  n_role_view_templates

  5  where

  6  view_label = 'OE_Lines';

ROLE_LABEL

========================

ORDER_ENTRY

Since this is not the case, I suspect that it is rejected and a join candidate due to this security issue.   

That is all for now.