Multiple Site Use Statuses Show-up in View, AR_CUSTOMER_ADDRESSES

Our user community noticed that the Noetix View, ARX0_CUSTOMER_ADDRESSES, shows all site use statuses, yet the site use status is not a column in this view.  This results in the view potentially having multiple rows with the same site use information.

Specifically, this view has the table, HZ_CUST_SITE_USES_ALL (alias of SITE), in its DDL. This table has a column titled, STATUS, which shows a value of ‘A’ if it is active and ‘I’ if it is inactive, yet there is no site_use_status column in this view.

To illistrate this multiple statuses issue, if you run the query below, there will be a non-null result set (at least with our implementation of Oracle Apps 12.1.3 and the 6.01 Noetix Views):



SELECT
SITE_NUMBER,
CUSTOMER_LOCATION,
CONTACT_LAST_NAME,
CUSTOMER,
BUSINESS_PURPOSE,
COUNT(1)

FROM
ARX0_CUSTOMER_ADDRESSES

GROUP BY SITE_NUMBER,
CUSTOMER_LOCATION,
CONTACT_LAST_NAME,
CUSTOMER,
BUSINESS_PURPOSE
HAVING COUNT(1) > 1;

I submitted a ticket to Noetix suggesting for them to modify this view so that it exposes the site use status to let the report end user decide which site use statuses they want to see.

If you notice this behavior, you might want to make this modification. Our Sales and Accounting staff were performing a customer master “clean-up” project and noticed this aberrant behavior.

Adding this column

I perform a query to see if Noetix exposes this column with other Noetix Views as follows:



SELECT
VCT.*

FROM
NOETIX_SYS.N_VIEW_TABLE_TEMPLATES VTT,
NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES VCT

WHERE 1=1
AND VTT.QUERY_POSITION = VCT.QUERY_POSITION
AND VTT.VIEW_LABEL = VCT.VIEW_LABEL
AND VTT.TABLE_ALIAS = VCT.TABLE_ALIAS
AND TABLE_NAME ='HZ_CUST_SITE_USES_ALL'
AND COLUMN_LABEL LIKE '%Status';

The view, AR_Correspondences, does do this. This is a column of type, “LOOK”. You can see how Noetix adds this column type with this query:

SELECT
*
FROM
NOETIX_SYS.N_VIEW_COLUMN_TEMPLATES

WHERE
1=1

AND TABLE_ALIAS LIKE 'SITE'

AND VIEW_LABLE = 'AR_Customer_Addresses';

I add this column as follows:




INSERT INTO n_view_column_templates

(view_label

,query_position
,column_label
,table_alias
,column_expression
,column_position
,column_type
,description
,group_by_flag
,profile_option
,product_version
,ref_application_label
,ref_table_name
,ref_lookup_column_name
,ref_description_column_name
,ref_lookup_type
,created_by
,creation_date
,last_updated_by
,last_update_date)
VALUES
('AR_Customer_Addresses' -- view_label
,2 -- query_position
,'Site_Use_Status' -- column_label
,'SITE' -- table_alias
,'STATUS' -- column_expression
,47 -- column_position
,'LOOK' -- column_type
,'Site use status. --XXACME' -- description
,NULL -- group_by_flag
,NULL -- profile_option
,'%' -- product_version
,'NOETIX' -- ref_application_label
,'N_AR_LOOKUPS_VL' -- ref_table_name
,NULL -- ref_lookup_column_name
,NULL -- ref_description_column_name
,'CODE_STATUS' -- ref_lookup_type
,'FLINSTONEF' -- created_by
,SYSDATE -- creation_date

,'FLINTSTONEF' -- last_updated_by

,SYSDATE) -- last_update_date
;
COMMIT;

That is all.