My Oracle Support Banner

Unexpected Error: Aborting Duplicate Batch When Using Custom Attribute for Subset in SDIB (Doc ID 2022146.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Trading Community - Version 12.0 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Data Quality Management

When attempting to run ARHDQBD module: DQM Duplicate Identification Program
the following error occurs.

ERROR
-----------------------
Worker with request id 11657728
did not complete normally
p_rule_id 10060
match_rule_purpose Q
dup_workers_completed FALSE
Concurrent Program Execution completed

ARHDQBDW module: DQM Duplicate Identification Worker
+---------------------------------------------------------------------------+

Current system time is 17-APR-2015 15:16:00

+---------------------------------------------------------------------------+

**Starts**17-APR-2015 15:16:00
**Ends**17-APR-2015 15:16:00
UnExpected Error
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Starting Concurrent Program 'Batch Quick Duplicate Identification Worker: 1'
Start Time 04-17-15 15:16:00
subset defn is parties.CUSTOM_ATTRIBUTE1 = 'Y' AND parties.CUSTOM_ATTRIBUTE6 = 'Y'
------------------------------------------------------
Start Time before insert to hz_dqm_stage_gt 04-17-15 15:16:00
--------------------------------
Unexpected Error
Error: Aborting duplicate batch
THIS WORKER ERRORED OUT
Unexpected SQL error encountered when performing search.
Procedure: TCA_DUP_ID_WORKER
Error: ORA-00904: "PARTIES"."CUSTOM_ATTRIBUTE6": invalid identifier
Please contact the system administrator.

 

Customer_attribute6 is custom attribute and the data is stored in HZ_STAGED_PARTIES.TX160 with value Yes and Null.

Here is the custom transformation used for the attribute:
FUNCTION EXCLUDE_CCRL(p_party_id IN NUMBER,
p_entity IN VARCHAR2,
p_attribute IN VARCHAR2,
p_context IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
IS

o_attribute1 VARCHAR2(240) := NULL;
v_attribute1 VARCHAR2(240) := NULL;

l_party_id NUMBER;
l_party_name VARCHAR2(360);
l_party_info VARCHAR2(1000);

BEGIN

l_party_info := NULL;

BEGIN

SELECT nvl(COL.NAME,'AA') INTO v_attribute1
FROM
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_PARTIES PARTY,
HZ_CUST_ACCT_SITES A,
HZ_CUST_SITE_USES SU,
HZ_CUSTOMER_PROFILES CP_CUST,
AR_COLLECTORS COL
WHERE CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_CODE='BILL_TO'
AND SU.ORG_ID=82
AND CUST_ACCT.CUST_ACCOUNT_ID= CP_CUST.CUST_ACCOUNT_ID
AND COL.COLLECTOR_ID =CP_CUST.COLLECTOR_ID
AND COL.NAME='CCRL'
AND PARTY.PARTY_ID = p_party_id;


IF v_attribute1 = 'CCRL' THEN
o_attribute1 := 'Yes';

ELSE
o_attribute1 := 'No';

END IF; --v_attribute1 = 'PTP'

EXCEPTION
WHEN NO_DATA_FOUND THEN
o_attribute1:= NULL;

WHEN TOO_MANY_ROWS THEN
o_attribute1:= NULL;
END;

RETURN(o_attribute1);

EXCEPTION
WHEN OTHERS THEN

APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT,'ERROR in (F) ASTM_EXCLUDE_CCRL ');
v_error_code := SQLCODE;
v_error := SQLERRM;
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT,'ERROR_CODE '||v_error_code);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.OUTPUT,'ERROR '||v_error);
FND_MESSAGE.SET_NAME('APPS','ASTM_DQM_ATTR_PROCESS_ERROR');
FND_MESSAGE.SET_TOKEN('FUNC','ASTM_DQM_PROCESS.ASTM_EXCLUDE_CCRL');
FND_MESSAGE.SET_TOKEN('ERROR',v_error);
FND_MSG_PUB.ADD;
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
--RETURN(l_party_info);
END; -- FUNCTION EXCLUDE_CCRL

The DQM Staging Program with STAGE_ALL_DATA was completed with no error

Select b.STAGED_ATTRIBUTE_TABLE, a.ATTRIBUTE_NAME,b.STAGED_ATTRIBUTE_COLUMN
from HZ_TRANS_ATTRIBUTES_B a, hz_trans_functions_b b
where a.attribute_id=b.attribute_id and TAGED_ATTRIBUTE_TABLE ='HZ_STAGED_PARTIES'
order by b.STAGED_ATTRIBUTE_TABLE,b.STAGED_ATTRIBUTE_COLUMN;

returns that the CustomAttribte6 is stored in TX160 and TX160 contains Yes and Null for all the
records in hz_staged_parties.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot DQM Duplicate Identification Program

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.