R12.1 OKEKAUWB OKE_CUSTOMER_CONTACTS_V RETURNS DOUBLE RECORDS FOR EACH ID

Last updated on MARCH 08, 2017

Applies to:

Symptoms

When entering the name for a contact role, the Contracts Organizer - Authoring Workbench gives an error - "Invalid value for the column Expenditure Item Date CONTACT.ID1, .ID2". User is unable to save the contact.

The navigation is: Contracts Organizer --> Authoring Workbench --> Parties and Contacts tab.

The data is NOT duplicated but the query that is used to build the OKE_CUSTOMER_CONTACTS_V returns double records for each ID. This behavior occurs for all the contact records that the view is expected to display.

The LOV also shows records with a ", " along with the actual contact name record.

The customer believes the OKE_CUSTOMER_CONTACTS_V is not fully qualifying the use of the HZ_RELATIONSHIPS table, which in R12 stores both "sides" of the relationship in separate records.
The trace file is attached. Based on the trace, the form is returning a message after the following query which does not return data:

SELECT 'x'
 FROM OKC_CONTACTS
WHERE CPL_ID = :B8
  AND CRO_CODE = :B7
  AND NVL (JTOT_OBJECT1_CODE, 'X') = NVL ( :B6, 'X')
  AND NVL (OBJECT1_ID1, 'X') = NVL ( :B5, 'X')
  AND NVL (OBJECT1_ID2, 'X') = NVL ( :B4, 'X')
  AND ID <> NVL ( :B3, -9999)
  AND ( ( ( :B2 BETWEEN TRUNC (START_DATE) AND TRUNC (END_DATE))
  OR ( (TRUNC (START_DATE) BETWEEN :B2 AND :B1)
  OR (TRUNC (END_DATE) BETWEEN :B2 AND :B1))
  OR ( :B1 BETWEEN TRUNC (START_DATE) AND TRUNC (END_DATE)))
  OR ( (END_DATE IS NULL AND ( :B1 = START_DATE))
  OR ( :B1 IS NULL AND ( :B2   OR ( :B1 IS NULL AND END_DATE IS NULL)))

Cause