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.