[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id= p_match_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity_name
)
LOOP
temp := TRUE ;
/*** Private procedure for inserting lines into generated packages **/
PROCEDURE l(str VARCHAR2) IS
BEGIN
HZ_GEN_PLSQL.add_line(str);
/*** Private procedure for inserting statement , procedure level debug lines into generated packages **/
PROCEDURE d(p_msg_level NUMBER,str VARCHAR2, val VARCHAR2 DEFAULT NULL, pad VARCHAR2 DEFAULT ' ') IS
l_msg_level VARCHAR2(30);
/*** Private procedure for inserting statement , procedure level debug start lines into generated packages **/
PROCEDURE ds(p_msg_level NUMBER,pad VARCHAR2 DEFAULT ' ') IS
BEGIN
IF nvl(FND_PROFILE.VALUE('HZ_DQM_DEV_DEBUG'), 'N') = 'N'
THEN
IF p_msg_level=FND_LOG.LEVEL_STATEMENT THEN
l(pad||'IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN');
/*** Private procedure for inserting statement , procedure level debug content lines into generated packages **/
PROCEDURE dc(p_msg_level NUMBER,str VARCHAR2, val VARCHAR2 DEFAULT NULL, pad VARCHAR2 DEFAULT ' ') IS
l_msg_level VARCHAR2(30);
/*** Private procedure for inserting statement , procedure level debug end lines into generated packages **/
PROCEDURE de(pad VARCHAR2 DEFAULT ' ') IS
BEGIN
IF nvl(FND_PROFILE.VALUE('HZ_DQM_DEV_DEBUG'), 'N') = 'N'
THEN
l(pad||'END IF;');
/*** VJN Introduced Private procedures for inserting
upto four consecutive procedure level debug lines with
statement level header and footer into generated
packages
Note that str1 and val1 are not defaulted. We need atleast
one procedure level line.
This the string - value pair version
**/
PROCEDURE ldbg_sv(str1 VARCHAR2, val1 VARCHAR2,
str2 VARCHAR2 DEFAULT NULL, val2 VARCHAR2 DEFAULT NULL,
str3 VARCHAR2 DEFAULT NULL, val3 VARCHAR2 DEFAULT NULL,
str4 VARCHAR2 DEFAULT NULL, val4 VARCHAR2 DEFAULT NULL
) IS
BEGIN
IF str1 IS NOT NULL
THEN
ds(fnd_log.level_statement, ' ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT primary_attribute_id
FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
where p.match_rule_id=p_rule_id
and p.attribute_id=a.attribute_id
and a.entity_name = p_entity
and nvl(p.filter_flag,'N') = 'N' )
LOOP
-- between attributes
IF FIRST1
THEN
FIRST1 := FALSE;
FOR trans in ( SELECT staged_attribute_column
FROM hz_primary_trans pt, hz_trans_functions_vl f
where f.function_id = pt.function_id
and pt.primary_attribute_id = attrs.primary_attribute_id
)
LOOP
IF FIRST
THEN
l('-- do an or between all the transformations of an attribute -- ');
SELECT DISTINCT a.entity_name
FROM hz_match_rule_secondary s, hz_trans_attributes_vl a
WHERE a.attribute_id = s.attribute_id
AND s.match_rule_id = p_rule_id
MINUS
SELECT DISTINCT a.entity_name
FROM hz_match_rule_primary p, hz_trans_attributes_vl a
WHERE a.attribute_id = p.attribute_id
AND p.match_rule_id = p_rule_id;
SELECT 1
FROM hz_match_rule_primary p, hz_primary_trans pt, hz_trans_functions_vl f
WHERE p.match_rule_id = p_rule_id
AND pt.PRIMARY_ATTRIBUTE_ID = p.PRIMARY_ATTRIBUTE_ID
AND f.function_id = pt.function_id
AND f.ACTIVE_FLAG = 'N'
UNION
SELECT 1
FROM hz_match_rule_secondary s, hz_secondary_trans pt, hz_trans_functions_vl f
WHERE s.match_rule_id = p_rule_id
AND pt.SECONDARY_ATTRIBUTE_ID = s.SECONDARY_ATTRIBUTE_ID
AND f.function_id = pt.function_id
AND f.ACTIVE_FLAG = 'N';
SELECT 1 INTO l_batch_flag
FROM HZ_MATCH_RULES_VL
WHERE match_rule_id = l_rule_id;
SELECT RULE_PURPOSE,NVL(MATCH_RULE_TYPE,'SINGLE') into l_purpose,l_match_rule_type FROM HZ_MATCH_RULES_VL --Code Change for Match Rule Set
WHERE match_rule_id = l_rule_id;
entity defined in scoring. Added update statements since compile_match_rule
is public api and commented unnecessary updates in compile_all_rules and
compile_all_rules_nolog. */
OPEN check_null_set;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'C' WHERE MATCH_RULE_ID = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
l(' SELECT COMPILATION_FLAG ');
FOR RULE IN (SELECT MATCH_RULE_ID,RULE_NAME
FROM HZ_MATCH_RULES_VL
WHERE nvl(ACTIVE_FLAG,'Y')='Y'
AND (nvl(COMPILATION_FLAG,'N') = 'C'
OR MATCH_RULE_ID = p_rule_id)) LOOP
l(' -- Code for Match rule '||RULE.RULE_NAME);
SELECT f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id
AND a.entity_name= p_entity
ORDER BY sa.attribute_id) LOOP
l(' ,p_table_'||TX.STAGED_ATTRIBUTE_COLUMN||' VARCHAR2');
SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.entity_name = p_entity ) LOOP
FIRST := TRUE;
SELECT TRANSFORMATION_NAME, STAGED_ATTRIBUTE_COLUMN, f.FUNCTION_ID,
TRANSFORMATION_WEIGHT, SIMILARITY_CUTOFF
FROM HZ_SECONDARY_TRANS s,
HZ_TRANS_FUNCTIONS_VL f
WHERE s.SECONDARY_ATTRIBUTE_ID = SECATTRS.SECONDARY_ATTRIBUTE_ID
AND s.FUNCTION_ID = f.FUNCTION_ID
ORDER BY TRANSFORMATION_WEIGHT desc) LOOP
IF FIRST THEN
FIRST := FALSE;
PROCEDURE add_insert_function(p_entity VARCHAR2, p_rule_id NUMBER) IS
FIRST boolean := TRUE;
l(' PROCEDURE INSERT_'||p_entity||'_SCORE (');
SELECT f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id
AND a.entity_name= p_entity
ORDER BY sa.attribute_id) LOOP
l(' ,p_table_'||TX.STAGED_ATTRIBUTE_COLUMN||' VARCHAR2');
ldbg_s('Inside Calling Procedure - INSERT_'||p_entity||'_SCORE');
SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id,
USER_DEFINED_ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.entity_name = p_entity ) LOOP
FIRST := TRUE;
SELECT TRANSFORMATION_NAME, STAGED_ATTRIBUTE_COLUMN, f.FUNCTION_ID,
TRANSFORMATION_WEIGHT, SIMILARITY_CUTOFF
FROM HZ_SECONDARY_TRANS s,
HZ_TRANS_FUNCTIONS_VL f
WHERE s.SECONDARY_ATTRIBUTE_ID = SECATTRS.SECONDARY_ATTRIBUTE_ID
AND s.FUNCTION_ID = f.FUNCTION_ID
ORDER BY TRANSFORMATION_WEIGHT desc) LOOP
IF FIRST THEN
FIRST := FALSE;
ldbg_s('Inserting into HZ_PARTY_SCORE_DTLS_GT ...');
l(' INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,');
ldbg_s('Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done');
SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id,
USER_DEFINED_ATTRIBUTE_NAME, SOURCE_TABLE, CUSTOM_ATTRIBUTE_PROCEDURE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.entity_name = entity) LOOP
l(' IF p_attribute = '''||SECATTRS.ATTRIBUTE_NAME||''' THEN');
l(' SELECT party_type INTO l_party_type ');
l(' SELECT '||SECATTRS.ATTRIBUTE_NAME ||
' INTO l_matched_value ');
l(' SELECT '||SECATTRS.ATTRIBUTE_NAME ||
' INTO l_matched_value ');
l(' SELECT '|| SECATTRS.SOURCE_TABLE||'.'||
SECATTRS.ATTRIBUTE_NAME);
l(' SELECT '|| SECATTRS.SOURCE_TABLE||'.'||
SECATTRS.ATTRIBUTE_NAME);
l(' SELECT ' || SECATTRS.ATTRIBUTE_NAME || ' INTO l_matched_value ');
SELECT count(1) INTO l_num_primary
FROM HZ_MATCH_RULE_PRIMARY p,
HZ_TRANS_ATTRIBUTES_VL a
WHERE p.match_rule_id = p_rule_id
AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND ENTITY_NAME = p_entity;
SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME,nvl(FILTER_FLAG,'N') FILTER_FLAG,
nvl(DENORM_FLAG,'N') DENORM_FLAG
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = p_entity) LOOP
l('');
SELECT f.STAGED_ATTRIBUTE_COLUMN, f.TRANSFORMATION_NAME, nvl(f.PRIMARY_FLAG,'N') PRIMARY_FLAG
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_PRIMARY_TRANS pt
WHERE pt.PRIMARY_ATTRIBUTE_ID = PRIMATTRS.PRIMARY_ATTRIBUTE_ID
AND pt.FUNCTION_ID = f.FUNCTION_ID) LOOP
IF PRIMATTRS.FILTER_FLAG <> 'Y' THEN
IF PRIMTRANS.PRIMARY_FLAG = 'Y' THEN
tmp := '''A'||PRIMATTRS.ATTRIBUTE_ID||'''';
x_select_list OUT NOCOPY VARCHAR2,
x_param_list OUT NOCOPY VARCHAR2,
x_into_list OUT NOCOPY VARCHAR2) IS
FIRST BOOLEAN;
x_select_list := '';
SELECT f.staged_attribute_column, a.attribute_name, f.procedure_name
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id
AND a.entity_name= p_entity
ORDER BY sa.attribute_id) LOOP
--- Modified for Bug 4016594
IF TX.ATTRIBUTE_NAME = 'DUNS_NUMBER_C' AND upper(TX.PROCEDURE_NAME) = 'HZ_TRANS_PKG.EXACT' THEN
x_select_list := x_select_list || ', lpad(rtrim('||TX.STAGED_ATTRIBUTE_COLUMN||'),9,chr('||ascii('0')||'))';
x_select_list := x_select_list || ', '||TX.STAGED_ATTRIBUTE_COLUMN;
l_p_select_list VARCHAR2(1000);
l_ps_select_list VARCHAR2(1000);
l_c_select_list VARCHAR2(1000);
l_cpt_select_list VARCHAR2(1000);
SELECT RULE_PURPOSE, MATCH_SCORE, nvl(AUTO_MERGE_SCORE,99999), decode(MATCH_ALL_FLAG,'Y',' AND ',' OR '),
NVL(match_rule_type,'SINGLE') ---Code Change for Match Rule Set
INTO l_purpose, l_match_threshold, l_auto_merge_score, l_match_str,l_rule_type---Code Change for Match Rule Set
FROM HZ_MATCH_RULES_VL
WHERE match_rule_id = p_rule_id;
SELECT nvl(SUM(SCORE),1) INTO l_max_score
FROM HZ_MATCH_RULE_SECONDARY
WHERE match_rule_id = p_rule_id;
l(' p_person_restrict_sql := ''exists ( SELECT 1 from HZ_ORG_CONTACTS oc, hz_relationships r'' ');
l(' ||' || ' '' and exists ( SELECT 1 FROM HZ_PARTIES stage1 where stage1.party_id = r.subject_id'' ');
SELECT f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id
AND a.entity_name= 'PARTY'
ORDER BY sa.attribute_id) LOOP
IF FIRST THEN
l(' (');
SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.entity_name = 'PARTY') LOOP
FIRST := TRUE;
SELECT TRANSFORMATION_NAME, STAGED_ATTRIBUTE_COLUMN, f.FUNCTION_ID,
TRANSFORMATION_WEIGHT, SIMILARITY_CUTOFF
FROM HZ_SECONDARY_TRANS s,
HZ_TRANS_FUNCTIONS_VL f
WHERE s.SECONDARY_ATTRIBUTE_ID = SECATTRS.SECONDARY_ATTRIBUTE_ID
AND s.FUNCTION_ID = f.FUNCTION_ID
ORDER BY TRANSFORMATION_WEIGHT desc) LOOP
IF FIRST THEN
FIRST := FALSE;
add_insert_function('PARTY',p_rule_id);
add_insert_function('PARTY_SITES',p_rule_id);
add_insert_function('CONTACTS',p_rule_id);
add_insert_function('CONTACT_POINTS',p_rule_id);
l(' g_mappings.DELETE;');
l(' g_party_site_stage_list.DELETE;');
l(' g_contact_stage_list.DELETE;');
l(' g_contact_pt_stage_list.DELETE;');
l(' call_order.DELETE;');
l(' call_max_score.DELETE;');
SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME, nvl(SCORE,0) SCORE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p,
HZ_MATCH_RULE_SECONDARY s
WHERE p.match_rule_id = p_rule_id
AND s.match_rule_id (+) = p_rule_id
AND s.attribute_id (+) = a.attribute_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND nvl(FILTER_FLAG,'N') <> 'Y'
ORDER BY SCORE) LOOP
FIRST1 := TRUE;
SELECT f.STAGED_ATTRIBUTE_COLUMN, f.TRANSFORMATION_NAME, nvl(f.PRIMARY_FLAG,'N') PRIMARY_FLAG, f.PROCEDURE_NAME
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_PRIMARY_TRANS pt
WHERE pt.PRIMARY_ATTRIBUTE_ID = PRIMATTRS.PRIMARY_ATTRIBUTE_ID
AND pt.FUNCTION_ID = f.FUNCTION_ID)
LOOP
IF FIRST1 THEN
l_trans := '(g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||' IS NULL OR '' ''||'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||'||'' '' like ''% ''||g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||'||'' %'')';
get_column_list(p_rule_id, 'PARTY',l_p_select_list,l_p_param_list, l_p_into_list);
get_column_list(p_rule_id, 'PARTY_SITES',l_ps_select_list,l_ps_param_list, l_ps_into_list);
get_column_list(p_rule_id, 'CONTACTS',l_c_select_list,l_c_param_list, l_c_into_list);
get_column_list(p_rule_id, 'CONTACT_POINTS',l_cpt_select_list,l_cpt_param_list, l_cpt_into_list);
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
AND FILTER_FLAG = 'Y'
UNION
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = 'PARTY'
AND a.attribute_name='PARTY_TYPE'
AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
AND nvl(f.active_flag,'Y')='Y'
AND ROWNUM=1
) LOOP
IF FIRST THEN
l_party_filter_str := '(g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN ||
' IS NULL OR g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN || '||'' '' = p.' ||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||')';
SELECT to_char(score) INTO l_party_name_score from HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
WHERE a.attribute_id = s.attribute_id
AND s.match_rule_id = p_rule_id
AND attribute_name = 'PARTY_NAME';
l(' SELECT PARTY_ID '|| l_p_select_list);
l(' l_sqlstr := ''SELECT PARTY_ID '|| l_p_select_list||' FROM hz_staged_parties stage '';');
SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND a.attribute_name <> 'PARTY_NAME'
AND nvl(FILTER_FLAG,'N') = 'N') LOOP
IF FIRST THEN
l(' l_sqlstr := l_sqlstr || '' AND ((:attr IS NULL OR '';');
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_PRIMARY_TRANS pt
WHERE pt.PRIMARY_ATTRIBUTE_ID = PATTRS.PRIMARY_ATTRIBUTE_ID
AND pt.FUNCTION_ID = f.FUNCTION_ID) LOOP
IF FIRST1 THEN
l(' l_sqlstr := l_sqlstr || '' ('||PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||' like :'||PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||'||'''' %'''' '';');
SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND a.attribute_name <> 'PARTY_NAME'
AND nvl(FILTER_FLAG,'N') = 'Y') LOOP
l(' l_sqlstr := l_sqlstr || '' AND (:attr IS NULL OR '';');
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_PRIMARY_TRANS pt
WHERE pt.PRIMARY_ATTRIBUTE_ID = PATTRS.PRIMARY_ATTRIBUTE_ID
AND pt.FUNCTION_ID = f.FUNCTION_ID) LOOP
IF FIRST1 THEN
l(' l_sqlstr := l_sqlstr || '' ('||PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||' like :'||PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||'||'''' %'''' '';');
SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND nvl(a.denorm_flag,'N') = 'Y') LOOP
l(' l_sqlstr := l_sqlstr || '' AND (:attr IS NULL OR '';');
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_PRIMARY_TRANS pt
WHERE pt.PRIMARY_ATTRIBUTE_ID = DENATTR.PRIMARY_ATTRIBUTE_ID
AND pt.FUNCTION_ID = f.FUNCTION_ID) LOOP
IF FIRST1 THEN
l(' l_sqlstr := l_sqlstr || '' (D_PS like ''''% ''''||:'||DENTRANS.STAGED_ATTRIBUTE_COLUMN||'||'''' %'''' '';');
l(' SELECT PARTY_ID '|| l_p_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID '|| l_p_select_list);
l(' SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID '|| l_p_select_list);
l(' l_check := instrb(p_restrict_sql, ''SELECTIVE''); ');
ldbg_s('Restrict sql has a Selective Hint');
l(' l_sqlstr := ''SELECT '' || l_hint || '' PARTY_ID '|| l_p_select_list||'''||');
l(' SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID'|| l_ps_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID'|| l_ps_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID'|| l_ps_select_list);
l(' SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID'|| l_ps_select_list);
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID'|| l_ps_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID'|| l_ps_select_list);
l(' SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID'|| l_ps_select_list);
l(' l_check := instrb(p_restrict_sql, ''SELECTIVE''); ');
l(' l_check_dt := instrb(p_restrict_sql, ''SELECTIVE_PS''); ');
ldbg_s('Restrict sql has the selective_ps hint');
ldbg_s('Restrict sql has the selective hint');
l(' l_sqlstr := ''SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '|| l_ps_select_list||'''||');
l(' ''SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '|| l_ps_select_list||'''||');
l(' '' ( SELECT org_contact_id from HZ_ORG_CONTACTS oc, (select object_id, relationship_id, subject_id party_id from hz_relationships '' ||');
l(' l_sqlstr := ''SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '|| l_ps_select_list||'''||');
l(' '' SELECT 1 FROM HZ_STAGED_PARTIES p '' || ');
l(' l_sqlstr := ''SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID '|| l_ps_select_list||'''||');
l(' '' SELECT 1 FROM HZ_STAGED_PARTIES p '' || ');
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID'|| l_c_select_list);
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT ORG_CONTACT_ID, PARTY_ID'|| l_c_select_list);
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID'|| l_c_select_list);
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' l_check := instrb(p_restrict_sql, ''SELECTIVE''); ');
l(' l_check_dt := instrb(p_restrict_sql, ''SELECTIVE_CT''); ');
ldbg_s('Restrict sql has the selective_ct hint');
ldbg_s('Restrict sql has the selective hint');
l(' l_sqlstr := ''SELECT '' || l_hint || '' ORG_CONTACT_ID, PARTY_ID '|| l_c_select_list||'''||');
l(' '' SELECT 1 FROM HZ_STAGED_PARTIES p '' || ');
l(' SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID '|| l_cpt_select_list);
l(' SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list);
l(' l_check := instrb(p_restrict_sql, ''SELECTIVE''); ');
l(' l_check_dt := instrb(p_restrict_sql, ''SELECTIVE_CPT''); ');
ldbg_s('Restrict Sql has the selective_cpt hint');
ldbg_s('Restrict Sql has the selective hint');
l(' l_sqlstr := '' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list||'''||');
l(' '' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list||'''||');
l(' '' ( SELECT org_contact_id from HZ_ORG_CONTACTS oc, (select object_id, relationship_id, subject_id party_id from hz_relationships r '' ||');
l(' l_sqlstr := ''SELECT '' || l_hint ||'' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list||'''||');
l(' '' SELECT 1 FROM HZ_STAGED_PARTIES p '' || ');
l(' l_sqlstr := ''SELECT '' || l_hint ||'' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '|| l_cpt_select_list||'''||');
l(' '' SELECT 1 FROM HZ_STAGED_PARTIES p '' || ');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' SELECT p_search_ctx_id, PARTY_ID, '||l_party_name_score);
l(' l_sqlstr := ''INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, '||l_party_name_score||' FROM hz_staged_parties stage '';');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' H_SCORES.DELETE; ');
l(' g_mappings.DELETE;');
ldbg_s('Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
ldbg_s('Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, ''SELECTIVE'')=0)');
ldbg_s('Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
ldbg_s('Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, ''SELECTIVE'')=0)');
ldbg_s('In eval_party_site_level inserting into HZ_MATCHED_PARTY_SITES_GT from the H_PS_ID list');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (');
ldbg_s('Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
ldbg_s('Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, ''SELECTIVE'')=0)');
ldbg_s('In eval_contact_level inserting into HZ_MATCHED_CONTACTS_GT from the H_CT_ID list');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (');
ldbg_s('Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
ldbg_s('Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, ''SELECTIVE'')=0)');
ldbg_s('In eval_contact_point_level inserting into HZ_MATCHED_CPTS_GT from the H_CPT_ID list');
l(' INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
l(' SELECT use_contact_addr_flag, use_contact_cpt_flag ');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
ldbg_s('In find_parties inserting Search Rule results into HZ_MATCHED_PARTIES_GT');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
ldbg_s('In find_parties inserting Webservice Rule results into HZ_MATCHED_PARTIES_GT');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
ldbg_s('In find_parties inserting Duplicate Identification results into HZ_MATCHED_PARTIES_GT');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
ldbg_s('Before Inserting Duplicate Identification results into HZ_DUP_SET_PARTIES, if dup party already exists');
l(' SELECT 1 INTO l_tmp FROM HZ_DUP_SET_PARTIES'); --Bug No: 4244529
ldbg_s('In find_parties inserting Duplicate Identification results into HZ_DUP_SET_PARTIES');
l(' INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,');
l(' MERGE_BATCH_ID,SCORE,MERGE_FLAG, CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID) '); --Bug No: 4244529
l(' hz_utility_pub.last_update_login,');
l(' hz_utility_pub.last_update_date,');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
SELECT a.attribute_name,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = 'PARTY'
AND a.attribute_name='PARTY_TYPE'
AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
AND nvl(f.active_flag,'Y')='Y'
AND ROWNUM=1
) LOOP
l(' PROCEDURE set_person_party_type IS');
l(' SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;');
l(' SELECT subject_id INTO l_person_id FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, hz_parties p');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' H_SCORES.DELETE; ');
l(' g_mappings.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, ''SELECTIVE'')=0)');
l(' H_PARTY_ID.DELETE(l_party_id_idx-1);');
l(' H_PARTY_ID_LIST.DELETE(l_index);');
l(' H_SCORES.DELETE(l_index);');
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID'|| l_c_select_list);
l(' SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/ ');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
ldbg_s('Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
ldbg_s('Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list');
ldbg_sv('Inserting into the final array, the person_id - ','l_person_id');
l(' INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' SELECT 1 INTO l_tmp FROM HZ_DUP_SET_PARTIES'); --Bug No: 4244529
l(' INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,');
l(' MERGE_BATCH_ID,SCORE,MERGE_FLAG, CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID) '); --Bug No: 4244529
l(' hz_utility_pub.last_update_login,');
l(' hz_utility_pub.last_update_date,');
l(' DELETE FROM HZ_MATCHED_PARTY_SITES_GT ps WHERE SEARCH_CONTEXT_ID = x_search_ctx_id ');
l(' (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ps.PARTY_ID);');
l(' DELETE FROM HZ_MATCHED_CONTACTS_GT ct WHERE SEARCH_CONTEXT_ID = x_search_ctx_id ');
l(' (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ct.PARTY_ID);');
l(' DELETE FROM HZ_MATCHED_CPTS_GT cpt WHERE SEARCH_CONTEXT_ID = x_search_ctx_id ');
l(' (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = cpt.PARTY_ID);');
l(' SELECT use_contact_addr_flag, use_contact_cpt_flag ');
l(' SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;');
l(' INSERT INTO HZ_DUP_SETS ( DUP_SET_ID, DUP_BATCH_ID, WINNER_PARTY_ID,');
l(' STATUS, MERGE_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE, LAST_UPDATED_BY) ');
l(' hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,');
l(' INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,');
l(' MERGE_BATCH_ID,merge_flag,SCORE,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID) '); --Bug No: 4244529
l(' hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,');
l(' cursor get_cpts_for_party_sites is select contact_point_id ');
l(' cursor get_cpt_for_contact_id is select contact_point_id ');
SELECT a.attribute_id, a.attribute_name, a.entity_name
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
UNION
SELECT a.attribute_id, a.attribute_name, a.entity_name
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id) LOOP
l(' IF AttrVals.EXISTS('||ATTRS.attribute_id||') THEN');
SELECT tag INTO l_cpt_type FROM fnd_lookup_values
WHERE lookup_type = 'HZ_DQM_CPT_ATTR_TYPE'
AND lookup_code = ATTRS.attribute_name
AND ROWNUM=1;
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND (a.ENTITY_NAME='PARTY_SITES' OR a.ENTITY_NAME='CONTACT_POINTS')
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE) ');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND (a.ENTITY_NAME='CONTACTS' OR a.ENTITY_NAME='CONTACT_POINTS')
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE) ');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.ENTITY_NAME='CONTACT_POINTS'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_CPTS_GT(SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, '||l_p_param_list||',1);');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.entity_name = 'PARTY_SITES'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_max_'||TX.staged_attribute_column ||' := l_'||TX.staged_attribute_column ||';');
l(' INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) '||replace(l_ps_param_list,'l_TX','l_max_TX')||',l_max_idx);');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.entity_name = 'CONTACTS'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_max_'||TX.staged_attribute_column ||' := l_'||TX.staged_attribute_column ||';');
l(' INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) '||replace(l_c_param_list,'l_TX','l_max_TX')||',l_max_idx);');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.entity_name = 'CONTACT_POINTS'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_max_'||TX.staged_attribute_column ||' := l_'||TX.staged_attribute_column ||';');
l(' INSERT_CONTACT_POINTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_point_list(l_max_idx), g_contact_pt_stage_list(l_max_idx) '||replace(l_cpt_param_list,'l_TX','l_max_TX')||',l_max_idx);');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND nvl(p.FILTER_FLAG,'N') = 'N') LOOP
l(' IF p_party_search_rec.'||PRIMATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_party_site_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACTS'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
l_p_select_list VARCHAR2(1000);
l_ps_select_list VARCHAR2(1000);
l_c_select_list VARCHAR2(1000);
l_cpt_select_list VARCHAR2(1000);
SELECT RULE_PURPOSE, MATCH_SCORE, nvl(AUTO_MERGE_SCORE,99999), decode(MATCH_ALL_FLAG,'Y',' AND ',' OR ')
INTO l_purpose, l_match_threshold, l_auto_merge_score, l_match_str
FROM HZ_MATCH_RULES_VL
WHERE match_rule_id = p_rule_id;
SELECT nvl(SUM(SCORE),1) INTO l_max_score
FROM HZ_MATCH_RULE_SECONDARY
WHERE match_rule_id = p_rule_id;
SELECT f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id
AND a.entity_name= 'PARTY'
ORDER BY sa.attribute_id) LOOP
IF FIRST THEN
l(' (');
SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.entity_name = 'PARTY') LOOP
FIRST := TRUE;
SELECT TRANSFORMATION_NAME, STAGED_ATTRIBUTE_COLUMN, f.FUNCTION_ID,
TRANSFORMATION_WEIGHT, SIMILARITY_CUTOFF
FROM HZ_SECONDARY_TRANS s,
HZ_TRANS_FUNCTIONS_VL f
WHERE s.SECONDARY_ATTRIBUTE_ID = SECATTRS.SECONDARY_ATTRIBUTE_ID
AND s.FUNCTION_ID = f.FUNCTION_ID
ORDER BY TRANSFORMATION_WEIGHT desc) LOOP
IF FIRST THEN
FIRST := FALSE;
add_insert_function('PARTY',p_rule_id);
add_insert_function('PARTY_SITES',p_rule_id);
add_insert_function('CONTACTS',p_rule_id);
add_insert_function('CONTACT_POINTS',p_rule_id);
l(' g_mappings.DELETE;');
l(' g_party_site_stage_list.DELETE;');
l(' g_contact_stage_list.DELETE;');
l(' g_contact_pt_stage_list.DELETE;');
l(' call_order.DELETE;');
l(' call_max_score.DELETE;');
SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME, nvl(SCORE,0) SCORE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p,
HZ_MATCH_RULE_SECONDARY s
WHERE p.match_rule_id = p_rule_id
AND s.match_rule_id (+) = p_rule_id
AND s.attribute_id (+) = a.attribute_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND nvl(FILTER_FLAG,'N') <> 'Y'
ORDER BY SCORE) LOOP
FIRST1 := TRUE;
SELECT f.STAGED_ATTRIBUTE_COLUMN, f.TRANSFORMATION_NAME, nvl(f.PRIMARY_FLAG,'N') PRIMARY_FLAG
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_PRIMARY_TRANS pt
WHERE pt.PRIMARY_ATTRIBUTE_ID = PRIMATTRS.PRIMARY_ATTRIBUTE_ID
AND pt.FUNCTION_ID = f.FUNCTION_ID)
LOOP
IF FIRST1 THEN
l_trans := '(g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||' IS NULL OR '' ''||'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||'||'' '' like ''% ''||g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||'||'' %'')';
get_column_list(p_rule_id, 'PARTY',l_p_select_list,l_p_param_list, l_p_into_list);
get_column_list(p_rule_id, 'PARTY_SITES',l_ps_select_list,l_ps_param_list, l_ps_into_list);
get_column_list(p_rule_id, 'CONTACTS',l_c_select_list,l_c_param_list, l_c_into_list);
get_column_list(p_rule_id, 'CONTACT_POINTS',l_cpt_select_list,l_cpt_param_list, l_cpt_into_list);
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
AND nvl(FILTER_FLAG,'N') = 'Y'
UNION
SELECT f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = 'PARTY'
AND a.attribute_name='PARTY_TYPE'
AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
AND nvl(f.active_flag,'Y')='Y'
AND ROWNUM=1
) LOOP
IF FIRST THEN
l_party_filter_str := '(g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN ||
' IS NULL OR g_party_stage_rec.'||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN || '||'' '' = p.' ||
PRIMTRANS.STAGED_ATTRIBUTE_COLUMN||')';
l(' SELECT PARTY_ID '|| l_p_select_list);
l(' SELECT /*+ ORDERED */ stage.PARTY_ID '|| replace(l_p_select_list,'T','stage.T'));
l(' SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID '|| replace(l_p_select_list,'T','stage.T'));
l(' l_sqlstr := ''SELECT /*+ ORDERED */ stage.PARTY_ID '|| replace(l_p_select_list,'T','stage.T')||'''||');
l(' SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID'||
replace(l_ps_select_list,'T','stage.T') );
l(' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID'||
replace(l_ps_select_list,'T','stage.T'));
l(' SELECT /*+ USE_NL(srch stage) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID'||
replace(l_ps_select_list,'T','stage.T'));
l(' SELECT stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID'
|| replace(l_ps_select_list,'T','stage.T'));
l(' l_sqlstr := ''SELECT /*+ ORDERED USE_NL(srch stage p) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID ' ||
replace(l_ps_select_list,'T','stage.T')||'''||');
l(' l_sqlstr := ''SELECT /*+ USE_NL(srch stage) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID ' ||
replace(l_ps_select_list,'T','stage.T')||'''||');
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ stage.ORG_CONTACT_ID, stage.PARTY_ID'||
replace(l_c_select_list,'T' , 'stage.T') );
l(' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT /*+ USE_NL(srch stage) */ stage.ORG_CONTACT_ID, stage.PARTY_ID'|| replace(l_c_select_list,'T','stage.T') );
l(' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ stage.ORG_CONTACT_ID, stage.PARTY_ID'
|| replace(l_c_select_list,'T' , 'stage.T') );
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' l_sqlstr := ''SELECT /*+ USE_NL(srch stage) */ stage.ORG_CONTACT_ID, stage.PARTY_ID '||
replace(l_c_select_list,'T','stage.T')||'''||');
l(' '' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p '' || ');
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID,'
|| 'stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID '
|| replace(l_cpt_select_list,'T','stage.T') );
l(' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT /*+ USE_NL(srch stage) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID '
|| replace(l_cpt_select_list,'T','stage.T') );
l(' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p');
l(' SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID '
|| replace(l_cpt_select_list,'T','stage.T') );
l(' SELECT 1 FROM HZ_STAGED_PARTIES p');
l(' l_sqlstr := ''SELECT /*+ USE_NL(srch stage) */ stage.CONTACT_POINT_ID, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID '
|| replace(l_cpt_select_list,'T','stage.T')||'''||');
l(' '' SELECT /*+ INDEX(p HZ_STAGED_PARTIES_U1) */ 1 FROM HZ_STAGED_PARTIES p '' || ');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' H_SCORES.DELETE; ');
l(' g_mappings.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' SELECT use_contact_addr_flag, use_contact_cpt_flag ');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l(' SELECT 1 INTO l_tmp FROM HZ_DUP_SET_PARTIES'); --Bug No: 4244529
l(' INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,');
l(' MERGE_BATCH_ID,SCORE,MERGE_FLAG, CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID) '); --Bug No: 4244529
l(' hz_utility_pub.last_update_login,');
l(' hz_utility_pub.last_update_date,');
l('----------INSERT INTO HZ_MATCHED_PARTY_SITES -----');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (');
l('----------INSERT INTO HZ_MATCHED_CONTACTS-----');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (');
l('----------INSERT INTO HZ_MATCHED_CPTS-----');
l(' INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
SELECT a.attribute_name,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = 'PARTY'
AND a.attribute_name='PARTY_TYPE'
AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
AND nvl(f.active_flag,'Y')='Y'
AND ROWNUM=1
) LOOP
l(' PROCEDURE set_person_party_type IS');
l(' SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;');
l(' SELECT subject_id INTO l_person_id FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, hz_parties p');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' H_SCORES.DELETE; ');
l(' g_mappings.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID'|| l_c_select_list);
l(' SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/ ');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)');
l(' SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, ');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)');
l(' SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, ');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)');
l(' SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, ');
l(' H_PARTY_ID.DELETE;');
l(' H_PARTY_ID_LIST.DELETE;');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (');
l(' INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID)');
l(' SELECT distinct l_search_ctx_id,r.subject_id from HZ_DQM_PARTIES_GT d, HZ_ORG_CONTACTS oc, ');
l(' INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) ');
l('----------INSERT INTO HZ_MATCHED_PARTY_SITES -----');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (');
l('----------INSERT INTO HZ_MATCHED_CONTACTS-----');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (');
l('----------INSERT INTO HZ_MATCHED_CPTS-----');
l(' INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
l(' DELETE FROM HZ_MATCHED_PARTY_SITES_GT ps WHERE SEARCH_CONTEXT_ID = x_search_ctx_id ');
l(' (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ps.PARTY_ID);');
l(' DELETE FROM HZ_MATCHED_CONTACTS_GT ct WHERE SEARCH_CONTEXT_ID = x_search_ctx_id ');
l(' (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ct.PARTY_ID);');
l(' DELETE FROM HZ_MATCHED_CPTS_GT cpt WHERE SEARCH_CONTEXT_ID = x_search_ctx_id ');
l(' (SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = cpt.PARTY_ID);');
l(' SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;');
l(' INSERT INTO HZ_DUP_SETS ( DUP_SET_ID, DUP_BATCH_ID, WINNER_PARTY_ID,');
l(' STATUS, MERGE_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE, LAST_UPDATED_BY) ');
l(' hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,');
l(' INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,');
l(' MERGE_BATCH_ID,merge_flag,SCORE,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,');
l(' LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID) '); --Bug No: 4244529
l(' hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,');
l(' cursor get_cpts_for_party_sites is select contact_point_id ');
l(' cursor get_cpt_for_contact_id is select contact_point_id ');
SELECT a.attribute_id, a.attribute_name, a.entity_name
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
UNION
SELECT a.attribute_id, a.attribute_name, a.entity_name
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id) LOOP
l(' IF AttrVals.EXISTS('||ATTRS.attribute_id||') THEN');
SELECT tag INTO l_cpt_type FROM fnd_lookup_values
WHERE lookup_type = 'HZ_DQM_CPT_ATTR_TYPE'
AND lookup_code = ATTRS.attribute_name
AND ROWNUM=1;
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND (a.ENTITY_NAME='PARTY_SITES' OR a.ENTITY_NAME='CONTACT_POINTS')
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE) ');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND (a.ENTITY_NAME='CONTACTS' OR a.ENTITY_NAME='CONTACT_POINTS')
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE) ');
l(' INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE) ');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.ENTITY_NAME='CONTACT_POINTS'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT INTO HZ_MATCHED_CPTS_GT(SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_'||TX.staged_attribute_column ||' VARCHAR2(2000);');
l(' SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;');
l(' INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, '||l_p_param_list||',1);');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.entity_name = 'PARTY_SITES'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_max_'||TX.staged_attribute_column ||' := l_'||TX.staged_attribute_column ||';');
l(' INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) '||replace(l_ps_param_list,'l_TX','l_max_TX')||',l_max_idx);');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.entity_name = 'CONTACTS'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_max_'||TX.staged_attribute_column ||' := l_'||TX.staged_attribute_column ||';');
l(' INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) '||replace(l_c_param_list,'l_TX','l_max_TX')||',l_max_idx);');
SELECT distinct f.staged_attribute_column
FROM hz_trans_functions_vl f, hz_secondary_trans st,
hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
WHERE sa.match_rule_id = p_rule_id
AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
AND st.function_id = f.function_id
AND a.entity_name = 'CONTACT_POINTS'
AND a.attribute_id = sa.attribute_id) LOOP
l(' l_max_'||TX.staged_attribute_column ||' := l_'||TX.staged_attribute_column ||';');
l(' INSERT_CONTACT_POINTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_point_list(l_max_idx), g_contact_pt_stage_list(l_max_idx) '||replace(l_cpt_param_list,'l_TX','l_max_TX')||',l_max_idx);');
l(' --------------- DELETE FROM ALL SRCH TABLES ---------------------');
l(' DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;');
l(' --------------- DELETE FROM ALL SRCH TABLES ---------------------');
l(' DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;');
l(' --------------- DELETE FROM ALL SRCH TABLES ---------------------');
l(' DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;');
l(' --------------- DELETE FROM ALL SRCH TABLES ---------------------');
l(' DELETE FROM HZ_SRCH_PARTIES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_PSITES WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CONTACTS WHERE batch_id = -1 ;');
l(' DELETE FROM HZ_SRCH_CPTS WHERE batch_id = -1 ;');
l_sql := 'select ' || proc ||
'(:attrval,:lang,:attr,:entity,:ctx) from dual';
INSERT_TRFNS varchar2(32000);
INSERT_TRFN_VALUES varchar2(32000);
SELECT a.ATTRIBUTE_NAME, SCORE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY') LOOP
l(' IF p_search_rec.'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr,
HZ_SECONDARY_TRANS sfunc
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
AND sattr.SECONDARY_ATTRIBUTE_ID = sfunc.SECONDARY_ATTRIBUTE_ID
AND sfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.attribute_name,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = 'PARTY'
AND a.attribute_name='PARTY_TYPE'
AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
AND nvl(f.active_flag,'Y')='Y'
AND ROWNUM=1
)
LOOP
NONE := FALSE;
INSERT_TRFNS := INSERT_TRFNS ||','||FUNCS.STAGED_ATTRIBUTE_COLUMN;
INSERT_TRFN_VALUES := INSERT_TRFN_VALUES ||','||'x_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN;
l(' insert into HZ_SRCH_PARTIES(batch_id,party_id, party_osr,party_os' || INSERT_TRFNS
|| ')'||' values(-1,-1,-1,-1'|| INSERT_TRFN_VALUES ||');');
SELECT a.ATTRIBUTE_NAME, SCORE, nvl(a.denorm_flag,'N') DENORM_FLAG
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity) LOOP
l(' IF p_search_list(I).'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr,
HZ_SECONDARY_TRANS sfunc
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND sattr.SECONDARY_ATTRIBUTE_ID = sfunc.SECONDARY_ATTRIBUTE_ID
AND sfunc.FUNCTION_ID = f.FUNCTION_ID
)
LOOP
NONE := FALSE;
SELECT a.ATTRIBUTE_ID, a.ATTRIBUTE_NAME
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.ATTRIBUTE_ID, a.ATTRIBUTE_NAME
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr,
HZ_SECONDARY_TRANS sfunc
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND sattr.SECONDARY_ATTRIBUTE_ID = sfunc.SECONDARY_ATTRIBUTE_ID
AND sfunc.FUNCTION_ID = f.FUNCTION_ID
)
LOOP
IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( FUNCS.attribute_id)
THEN
NONE := FALSE ;
INSERT_TRFNS varchar2(32000);
INSERT_TRFN_VALUES varchar2(32000);
SELECT a.ATTRIBUTE_NAME, SCORE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity) LOOP
l(' IF p_search_list(I).'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr,
HZ_SECONDARY_TRANS sfunc
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND sattr.SECONDARY_ATTRIBUTE_ID = sfunc.SECONDARY_ATTRIBUTE_ID
AND sfunc.FUNCTION_ID = f.FUNCTION_ID
)
LOOP
NONE := FALSE;
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.ATTRIBUTE_NAME,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr,
HZ_SECONDARY_TRANS sfunc
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = p_entity
AND sattr.SECONDARY_ATTRIBUTE_ID = sfunc.SECONDARY_ATTRIBUTE_ID
AND sfunc.FUNCTION_ID = f.FUNCTION_ID
)
LOOP
NONE := FALSE;
INSERT_TRFNS := INSERT_TRFNS ||','||FUNCS.STAGED_ATTRIBUTE_COLUMN;
INSERT_TRFN_VALUES := INSERT_TRFN_VALUES ||','||'x_stage_list(I).'||FUNCS.STAGED_ATTRIBUTE_COLUMN;
l(' insert into HZ_SRCH_PSITES(batch_id,party_id, party_osr,party_os, party_site_id,party_site_osr, party_site_os,new_party_flag ' || INSERT_TRFNS
|| ')'||' values(-1,-1,-1,-1,-1,-1,-1,''Y'''|| INSERT_TRFN_VALUES ||');');
l(' insert into HZ_SRCH_CONTACTS(batch_id,party_id, party_osr,party_os, org_contact_id,contact_osr, contact_os,new_party_flag ' || INSERT_TRFNS
|| ')'||' values(-1,-1,-1,-1,-1,-1,-1,''Y'''|| INSERT_TRFN_VALUES ||');');
l(' insert into HZ_SRCH_CPTS(batch_id,party_id, party_osr,party_os, contact_point_id,contact_pt_osr, contact_pt_os,contact_point_type,new_party_flag ' || INSERT_TRFNS
|| ')'||' values(-1,-1,-1,-1,-1,-1,-1,-1,''Y'''|| INSERT_TRFN_VALUES ||');');
SELECT a.ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY sattr
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
) LOOP
IF PRIMATTRS.ATTRIBUTE_NAME not in (
'PARTY_NAME','PARTY_TYPE','STATUS','PARTY_ALL_NAMES') THEN
IF NONE THEN
l(' IF p_search_rec.'||PRIMATTRS.ATTRIBUTE_NAME || ' IS NOT NULL ');
SELECT a.ATTRIBUTE_NAME, SCORE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY') LOOP
l(' IF p_search_rec.'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
SELECT a.ATTRIBUTE_NAME,a.attribute_id,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY pattr,
HZ_PRIMARY_TRANS pfunc
WHERE pattr.MATCH_RULE_ID = p_rule_id
AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
AND pattr.PRIMARY_ATTRIBUTE_ID = pfunc.PRIMARY_ATTRIBUTE_ID
AND pfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.ATTRIBUTE_NAME,a.attribute_id,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY sattr,
HZ_SECONDARY_TRANS sfunc
WHERE sattr.MATCH_RULE_ID = p_rule_id
AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = 'PARTY'
AND sattr.SECONDARY_ATTRIBUTE_ID = sfunc.SECONDARY_ATTRIBUTE_ID
AND sfunc.FUNCTION_ID = f.FUNCTION_ID
UNION
SELECT a.attribute_name,a.attribute_id,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f,
HZ_TRANS_ATTRIBUTES_VL a
WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = 'PARTY'
AND a.attribute_name='PARTY_TYPE'
AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
AND nvl(f.active_flag,'Y')='Y'
AND ROWNUM=1
)
LOOP
NONE := FALSE;
select nvl(filter_flag, 'N') INTO l_filt
FROM HZ_MATCH_RULE_PRIMARY p
where p.MATCH_RULE_ID = p_rule_id
AND p.attribute_id = FUNCS.attribute_id;
FOR CPTS IN (SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND p.attribute_id = a.attribute_id
AND ATTRIBUTE_NAME <> 'CONTACT_POINT_TYPE'
AND nvl(p.FILTER_FLAG,'N') = 'N') LOOP
IF FIRST THEN
l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND nvl(p.FILTER_FLAG,'N') = 'N') LOOP
l(' IF p_party_search_rec.'||PRIMATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
SELECT ENTITY_NAME, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME <> 'PARTY'
AND p.attribute_id = a.attribute_id
AND ATTRIBUTE_NAME <> 'CONTACT_POINT_TYPE'
AND nvl(p.FILTER_FLAG,'N') = 'N')
LOOP
IF PRIMATTRS.ENTITY_NAME = 'PARTY_SITES' THEN
l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_party_site_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND s.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_party_site_list(I).'|| SECATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND s.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| SECATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACTS'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACTS'
AND s.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_list(I).'|| SECATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND s.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| SECATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND p.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| PRIMATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND s.attribute_id = a.attribute_id)
LOOP
IF FIRST THEN
l(' IF p_contact_point_list(I).'|| SECATTRS.ATTRIBUTE_NAME||' IS NOT NULL ');
SELECT distinct ATTRIBUTE_NAME, CUSTOM_ATTRIBUTE_PROCEDURE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = p_entity
AND (a.SOURCE_TABLE = 'CUSTOM'
OR a.CUSTOM_ATTRIBUTE_PROCEDURE IS NOT NULL)
UNION
SELECT distinct ATTRIBUTE_NAME, CUSTOM_ATTRIBUTE_PROCEDURE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = p_entity
AND (a.SOURCE_TABLE = 'CUSTOM'
OR a.CUSTOM_ATTRIBUTE_PROCEDURE IS NOT NULL)) LOOP
l(p_record||'.'||CUSTATTRS.ATTRIBUTE_NAME||':=');
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
);
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
);
l(' SELECT party_site_id FROM ('); ---Code Change for Match Rule Set
l(' SELECT party_site_id,identifying_address_flag'); ---Code Change for Match Rule Set
l(' SELECT party_site_id,NVL(identifying_address_flag,''N'') identifying_address_flag'); ---Code Change for Match Rule Set
l(' SELECT party_site_id,NVL(identifying_address_flag,''N'') identifying_address_flag');
l(' SELECT party_id');
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
);
l(' SELECT org_contact_id');
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
);
l(' SELECT CONTACT_POINT_ID');
l(' SELECT CONTACT_POINT_ID');
l(' SELECT CONTACT_POINT_ID');
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
);
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
);
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
);
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
);
l(' SELECT PARTY_TYPE INTO l_party_type');
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
);
l(' SELECT ');
SELECT distinct a.ATTRIBUTE_NAME, decode(a.SOURCE_TABLE, 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES', 'HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES', 'HZ_ORGANIZATION_PROFILES', a.SOURCE_TABLE) SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
UNION
SELECT distinct a.ATTRIBUTE_NAME, decode(a.SOURCE_TABLE, 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES', 'HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES', 'HZ_ORGANIZATION_PROFILES', a.SOURCE_TABLE) SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL) LOOP
IF FIRST THEN
l(' translate(' || ATTRS.SOURCE_TABLE||'.'||ATTRS.ATTRIBUTE_NAME || ', ''%'','' '')');--bug 5621864
SELECT distinct a.ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
UNION
SELECT distinct a.ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL) LOOP
IF FIRST THEN
l(' x_party_search_rec.'||ATTRS.ATTRIBUTE_NAME);
SELECT COUNT(*) INTO l_num_attrs
FROM (
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
UNION
SELECT a.attribute_id
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
);
l(' SELECT ');
SELECT distinct a.ATTRIBUTE_NAME, decode(a.SOURCE_TABLE, 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES', 'HZ_PERSON_PROFILES','HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES', a.SOURCE_TABLE) SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
UNION
SELECT distinct a.ATTRIBUTE_NAME, decode(a.SOURCE_TABLE, 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES', 'HZ_PERSON_PROFILES', 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES', a.SOURCE_TABLE) SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL) LOOP
IF FIRST THEN
l(' translate(' || ATTRS.SOURCE_TABLE||'.'||ATTRS.ATTRIBUTE_NAME || ', ''%'','' '')');--bug 5621864
SELECT distinct a.ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
UNION
SELECT distinct a.ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY'
AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES' OR
a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES' OR
a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES')
AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL) LOOP
IF FIRST THEN
l(' x_party_search_rec.'||ATTRS.ATTRIBUTE_NAME);
l(' SELECT party_site_id');
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND a.SOURCE_TABLE <> 'CUSTOM'
UNION
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
l(' ,translate(' || ATTRS.SOURCE_TABLE||'.'||ATTRS.ATTRIBUTE_NAME || ', ''%'','' '')');--bug 5621864
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND a.SOURCE_TABLE <> 'CUSTOM'
UNION
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'PARTY_SITES'
AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
l(' ,x_party_site_list(J).'||ATTRS.ATTRIBUTE_NAME);
l(' SELECT org_contact_id');
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
AND a.SOURCE_TABLE <> 'CUSTOM'
UNION
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
l(' ,translate(' || ATTRS.SOURCE_TABLE||'.'||ATTRS.ATTRIBUTE_NAME || ', ''%'','' '')');--bug 5621864
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
AND a.SOURCE_TABLE <> 'CUSTOM'
UNION
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACTS'
AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
l(' ,x_contact_list(J).'||ATTRS.ATTRIBUTE_NAME);
l(' SELECT contact_point_id, contact_point_type');
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND a.SOURCE_TABLE <> 'CUSTOM'
UNION
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
l(' ,translate(' || ATTRS.SOURCE_TABLE||'.'||ATTRS.ATTRIBUTE_NAME || ', ''%'','' '')');--bug 5565522
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_PRIMARY p
WHERE p.match_rule_id = p_rule_id
AND p.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND a.SOURCE_TABLE <> 'CUSTOM'
UNION
SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
FROM HZ_TRANS_ATTRIBUTES_VL a,
HZ_MATCH_RULE_SECONDARY s
WHERE s.match_rule_id = p_rule_id
AND s.attribute_id = a.attribute_id
AND a.ENTITY_NAME = 'CONTACT_POINTS'
AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
l(' ,x_contact_point_list(J).'||ATTRS.ATTRIBUTE_NAME);
SELECT f.FUNCTION_ID
FROM hz_primary_trans f, hz_match_rule_primary a
WHERE a.MATCH_RULE_ID = p_rule_id
AND a.PRIMARY_ATTRIBUTE_ID = f.PRIMARY_ATTRIBUTE_ID
UNION
SELECT f.FUNCTION_ID
FROM hz_secondary_trans f, hz_match_rule_secondary a
WHERE a.MATCH_RULE_ID = p_rule_id
AND a.SECONDARY_ATTRIBUTE_ID = f.SECONDARY_ATTRIBUTE_ID;
SELECT f.FUNCTION_ID
FROM hz_primary_trans f, hz_match_rule_primary a
WHERE a.MATCH_RULE_ID IN (SELECT UNIQUE CONDITION_MATCH_RULE_ID
FROM HZ_MATCH_RULE_CONDITIONS
WHERE MATCH_RULE_SET_ID = p_rule_id
)
AND a.PRIMARY_ATTRIBUTE_ID = f.PRIMARY_ATTRIBUTE_ID
UNION
SELECT f.FUNCTION_ID
FROM hz_secondary_trans f, hz_match_rule_secondary a
WHERE a.MATCH_RULE_ID IN (SELECT UNIQUE CONDITION_MATCH_RULE_ID
FROM HZ_MATCH_RULE_CONDITIONS
WHERE MATCH_RULE_SET_ID = p_rule_id
)
AND a.SECONDARY_ATTRIBUTE_ID = f.SECONDARY_ATTRIBUTE_ID;
SELECT nvl(match_rule_type,'SINGLE') into l_rule_type FROM HZ_MATCH_RULES_VL
WHERE match_rule_id = p_rule_id;
l(' SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL ');
SELECT count(1) INTO l_num_primary
FROM HZ_MATCH_RULE_PRIMARY p,
HZ_TRANS_ATTRIBUTES_VL a
WHERE p.match_rule_id = p_rule_id
AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND ENTITY_NAME = p_entity;
SELECT count(1) INTO l_num_secondary
FROM HZ_MATCH_RULE_SECONDARY p,
HZ_TRANS_ATTRIBUTES_VL a
WHERE p.match_rule_id = p_rule_id
AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND ENTITY_NAME = p_entity;
SELECT MATCH_RULE_ID,RULE_NAME FROM HZ_MATCH_RULES_VL
where rule_purpose = p_rule_purpose ;
UPDATE HZ_MATCH_RULES_B SET compilation_flag = 'U'
where rule_purpose = p_rule_purpose ;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_match_rule_id;
SELECT MATCH_RULE_ID,RULE_NAME FROM HZ_MATCH_RULES_VL ORDER BY match_rule_type DESC nulls first;--bug 5263694
UPDATE HZ_MATCH_RULES_B SET compilation_flag = 'U';
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_match_rule_id;
l(' select attribute_id into l_id ');
SELECT count(unique compilation_flag) FROM HZ_MATCH_RULES_B
WHERE match_rule_id IN( SELECT unique condition_match_rule_id
FROM HZ_MATCH_RULE_CONDITIONS
WHERE match_rule_set_id = p_rule_set_id)
AND compilation_flag <> 'C';
SELECT UNIQUE MATCH_RULE_ID,RULE_NAME FROM HZ_MATCH_RULES_VL
WHERE COMPILATION_FLAG <> 'C'
AND MATCH_RULE_ID IN (SELECT UNIQUE MATCH_RULE_SET_ID FROM HZ_MATCH_RULE_CONDITIONS
WHERE CONDITION_MATCH_RULE_ID = p_cond_rule_id)
;
UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_match_rule_set_id;
l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
' attr.attribute_name,operation,value,rank '||
' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
' AND cond.attribute_id= attr.attribute_id (+) '||
' ORDER BY rank ASC';
l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
' attr.attribute_name,operation,value,rank '||
' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''CONTACTS'') '||
' AND cond.attribute_id= attr.attribute_id (+) '||
' ORDER BY rank ASC';
l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
' attr.attribute_name,operation,value,rank '||
' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''PARTY_SITES'') '||
' AND cond.attribute_id= attr.attribute_id (+) '||
' ORDER BY rank ASC';
l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
' attr.attribute_name,operation,value,rank '||
' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''PARTY_SITES'',''CONTACTS'') '||
' AND cond.attribute_id= attr.attribute_id (+) '||
' ORDER BY rank ASC';
l(' CURSOR c_rule_name(p_rule_id NUMBER) IS SELECT rule_name FROM hz_match_rules_vl ');
SELECT nvl(sum(sec.score),0) INTO l_score
FROM HZ_MATCH_RULE_SECONDARY sec,HZ_TRANS_ATTRIBUTES_VL attr
WHERE sec.match_rule_id = p_match_rule_id
AND sec.attribute_id = attr.attribute_id
AND attr.entity_name = p_entity_name;