I have discovered the issue with ACS item 3.9 and 3.91.  The SQL that is being used, relies on RA_CONTACTS to have information populated in the LAST_NAME field – and that is where comparisons are made.  Unfortunately, the AHA oracle applications environment does NOT ensure that every RA_CUSTOMERS record has a RA_CONTACTS row with appropriate LAST_NAME and FIRST_NAME fields.  

 

The queries that are identified below need to be changed to eliminate RA_CONTACTS from the query, and compare on CUSTOMER_NAME from the RA_CUSTOMERS table in a query similar to or exactly the same as below:

 

SELECT DISTINCT CUSTOMER.CUSTOMER_ID, CUSTOMER.CUSTOMER_NUMBER, CUSTOMER.CUSTOMER_NAME, ADDRESS.STATE

FROM AHA_SHW_SHOWS SHOW, AHA_SHW_CLASSES CLASS, AHA_SHW_RESULTS RESULT, RA_CUSTOMERS CUSTOMER,

RA_ADDRESSES ADDRESS WHERE SHOW.SHOW_ID = CLASS.SHOW_ID AND

RESULT.SHOW_CLASS_ID = CLASS.SHOW_CLASS_ID AND SHOW.SHOW_STATUS =  2 AND

RESULT.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID AND CUSTOMER.CUSTOMER_ID = ADDRESS.CUSTOMER_ID

AND UPPER(CUSTOMER_NAME) LIKE ' exhibitorLastName%'

ORDER BY UPPER(CUSTOMER.CUSTOMER_NAME), ADDRESS.STATE;

 

 

NOTE:  Our customer name field is populated like “LASTNAME, FIRST NAME MIDDLE INITIAL ETC” – so this query should work fine............