R12 - RACUST Errors With ORA-01400: cannot insert NULL into ("AR"."HZ_PARTY_USG_ASSIGNMENTS"."S (Doc ID 743463.1)

Last updated on JULY 12, 2017

Applies to:

Oracle Trading Community - Version 12.0.4 and later
Information in this document applies to any platform.
This problem can occur on any platform.
RACUST - Customer Interface

Symptoms

There is an issue when RACUST (Customer Interface) imports customer accounts.

While creating an Account for an Existing Party having a Merged party with same Original System Reference (OSR), Customer Interface errors.

If the party for the account has been merged, the same OSR reference can exist across two sources (a profile option currently set to 'Y'), then RACUST will always select the
party where rownum = 1 from the join to HZ_CUST_ACCOUNTS.

-----------------------------------------------------------------
> />racinc: inserting customers.
Current system time is 30-SEP-2008 22:04:39
cust_accounts: inserted 0 custrec
cust_accounts: inserted 0 custrec
> />Calling: hz_customer_int.insert_ci_party_usages
<<: hz_customer_int.insert_ci_party_usages
> />Calling: hz_customer_int.insert_nci_party_usages

racinc: ORA-01400: cannot insert NULL into ("AR"."HZ_PARTY_USG_ASSIGNMENTS"."S
racinc: Error inserting customers
Current system time is 30-SEP-2008 22:04:40
---------------------------------------------------------------------------


The problem is the use of HZ_CUSTOMER_INT.get_account_party_id.  
The last agrument to the procedure is currently 'A,' which means 'ANY.'
If you look at the function and the result for all customers being imported, the potential for a merged party being selected exists.

Responsibility:  Trading Community Manager
Navigation:  Control > Request > Run > Choose program: Import Batch to TCA Registry
1) Generate a bulk import batch.
2) Execute the insert statements in import_parties1.sql
3) Activate and define import. Do not dedupe against registry.
4) Once import completes, execute the insert statements in import_cust_accounts_1.sql
5) Submit the concurrent request "Customers Interface"
6) Once RACUST completes, repeat steps 1-3 with import_parties2.sql.
7) Log into Oracle Customer Data Librarian Superuser responsibility
8) Search for the party "Ralph Machismo". Mark these records as dupes and submit a merge request
9) Navigate to Data Quality Merge Request. Map the merge request. Be sure to select the party imported in step 6 as the master party. Apply suggested groupings for addresses
10) Once merge request completes, repeat steps 4-5 with the insert statements in import_cust_accounts_2.sql.
11) Finally, run the following query and notice how the party_id returned by the function used in hz_customer_int.insert_nci_party_usages .

SELECT party_id,
hz_customer_int.get_account_party_id('RACUSTPOPS', 'Y', 'P') correct_party_id,
hz_customer_int.get_account_party_id('RACUSTPOPS', 'Y', 'A') merged_party_id
FROM hz_cust_accounts
WHERE orig_system_reference = 'RACUSTPOPS';

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms