[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_person_restrict_sql := 'exists ( SELECT 1 from HZ_ORG_CONTACTS oc, hz_relationships r'
|| ' where oc.org_contact_id = stage.org_contact_id and'
|| ' r.relationship_id = oc.party_relationship_id'
|| ' and r.subject_type = ''PERSON'' AND r.object_type = ''ORGANIZATION'' '
|| ' and exists ( SELECT 1 FROM HZ_PARTIES stage1 where stage1.party_id = r.subject_id'
|| ' and ' || p_restrict1_sql || ' ) )' ;
SELECT party_type INTO l_party_type
FROM HZ_PARTIES
WHERE party_id = p_record_id;
SELECT PARTY_NAME INTO l_matched_value
FROM HZ_PARTIES
WHERE party_id = p_record_id
and rownum = 1;
SELECT PARTY_NAME INTO l_matched_value
FROM HZ_PARTIES
WHERE party_id = p_record_id
and rownum = 1;
SELECT party_type INTO l_party_type
FROM HZ_PARTIES
WHERE party_id = p_record_id;
SELECT TAX_REFERENCE INTO l_matched_value
FROM HZ_ORGANIZATION_PROFILES
WHERE party_id = p_record_id
and effective_end_date is null
and rownum = 1;
SELECT TAX_REFERENCE INTO l_matched_value
FROM HZ_PERSON_PROFILES
WHERE party_id = p_record_id
and effective_end_date is null
and rownum = 1;
SELECT HZ_LOCATIONS.POSTAL_CODE
INTO l_matched_value
FROM HZ_PARTY_SITES, HZ_LOCATIONS
WHERE HZ_PARTY_SITES.party_site_id = p_record_id
AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
SELECT EMAIL_ADDRESS INTO l_matched_value
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = p_record_id and rownum=1;
PROCEDURE INSERT_PARTY_SCORE (
p_party_id IN NUMBER
,p_record_id IN NUMBER
,p_search_ctx_id IN NUMBER
,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
,p_table_TX2 VARCHAR2
,p_table_TX8 VARCHAR2
,p_table_TX44 VARCHAR2
,p_idx IN NUMBER) IS
l_current_score NUMBER:=0;
hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_PARTY_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
VALUES (
p_party_id,p_record_id,p_search_ctx_id,'PARTY_NAME',
'PARTY', p_search_rec.PARTY_NAME,
l_attrib_value,l_score);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
VALUES (
p_party_id,p_record_id,p_search_ctx_id,'TAX_REFERENCE',
'PARTY', p_search_rec.TAX_REFERENCE,
l_attrib_value,l_score);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
PROCEDURE INSERT_PARTY_SITES_SCORE (
p_party_id IN NUMBER
,p_record_id IN NUMBER
,p_search_ctx_id IN NUMBER
,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
,p_table_TX3 VARCHAR2
,p_table_TX4 VARCHAR2
,p_table_TX11 VARCHAR2
,p_idx IN NUMBER) IS
l_current_score NUMBER:=0;
hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_PARTY_SITES_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
VALUES (
p_party_id,p_record_id,p_search_ctx_id,'ADDRESS',
'PARTY_SITES', p_search_rec.ADDRESS,
l_attrib_value,l_score);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
VALUES (
p_party_id,p_record_id,p_search_ctx_id,'POSTAL_CODE',
'PARTY_SITES', p_search_rec.POSTAL_CODE,
l_attrib_value,l_score);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
PROCEDURE INSERT_CONTACTS_SCORE (
p_party_id IN NUMBER
,p_record_id IN NUMBER
,p_search_ctx_id IN NUMBER
,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
,p_idx IN NUMBER) IS
l_current_score NUMBER:=0;
hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_CONTACTS_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
PROCEDURE INSERT_CONTACT_POINTS_SCORE (
p_party_id IN NUMBER
,p_record_id IN NUMBER
,p_search_ctx_id IN NUMBER
,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
,p_table_TX1 VARCHAR2
,p_table_TX6 VARCHAR2
,p_table_TX5 VARCHAR2
,p_idx IN NUMBER) IS
l_current_score NUMBER:=0;
hz_utility_v2pub.debug(p_message=>'Inside Calling Procedure - INSERT_CONTACT_POINTS_SCORE',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
VALUES (
p_party_id,p_record_id,p_search_ctx_id,'FLEX_FORMAT_PHONE_NUMBER',
'CONTACT_POINTS', p_search_rec.FLEX_FORMAT_PHONE_NUMBER,
l_attrib_value,l_score);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_PARTY_SCORE_DTLS_GT (PARTY_ID, RECORD_ID, SEARCH_CONTEXT_ID,
ATTRIBUTE,ENTITY,ENTERED_VALUE, MATCHED_VALUE, ASSIGNED_SCORE)
VALUES (
p_party_id,p_record_id,p_search_ctx_id,'EMAIL_ADDRESS',
'CONTACT_POINTS', p_search_rec.EMAIL_ADDRESS,
l_attrib_value,l_score);
hz_utility_v2pub.debug(p_message=>'Inserting into HZ_PARTY_SCORE_DTLS_GT ... Done',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
g_mappings.DELETE;
g_party_site_stage_list.DELETE;
g_contact_stage_list.DELETE;
g_contact_pt_stage_list.DELETE;
call_order.DELETE;
call_max_score.DELETE;
SELECT PARTY_ID , TX2, TX8, TX44
FROM hz_staged_parties
WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id);
l_sqlstr := 'SELECT PARTY_ID , TX2, TX8, TX44 FROM hz_staged_parties stage ';
SELECT PARTY_ID , TX2, TX8, TX44
FROM HZ_STAGED_PARTIES stage
WHERE PARTY_ID = p_dup_party_id;
SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX2, TX8, TX44
FROM HZ_STAGED_PARTIES stage
WHERE contains( concat_col, p_contains_str)>0
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX44
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
WHERE contains( concat_col, p_contains_str)>0
AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
AND d.party_id = stage.party_id
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) );
l_check := instrb(p_restrict_sql, 'SELECTIVE');
hz_utility_v2pub.debug(p_message=>'Restrict sql has a Selective Hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX44'||
' FROM HZ_STAGED_PARTIES stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
' AND ('||p_restrict_sql||')' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_STAGED_PARTY_SITES stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND stage.party_id = p_party_id;
SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_STAGED_PARTY_SITES stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND stage.party_id = p_party_id
UNION
SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND r.object_id = p_party_id
AND r.subject_id = stage.party_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.relationship_id = oc.party_relationship_id
AND oc.org_contact_id = stage.org_contact_id;
SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_STAGED_PARTY_SITES stage
WHERE contains( concat_col, p_contains_str)>0
AND EXISTS (
SELECT 1 FROM HZ_STAGED_PARTIES p
WHERE p.PARTY_ID = stage.PARTY_ID
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND d.search_context_id = p_search_ctx_id
AND d.party_id = stage.party_id
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
UNION
SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND d.search_context_id = p_search_ctx_id
AND d.party_id = r.subject_id
AND r.relationship_id = oc.party_relationship_id
AND oc.org_contact_id = stage.org_contact_id
AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND d.search_context_id = p_search_ctx_id
AND d.party_id = stage.party_id
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
l_check := instrb(p_restrict_sql, 'SELECTIVE');
l_check_dt := instrb(p_restrict_sql, 'SELECTIVE_PS');
hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective_ps hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
' FROM HZ_STAGED_PARTY_SITES stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND (ORG_CONTACT_ID IS NULL ' ||
' AND ('||p_restrict_sql||'))' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
' UNION ' ||
'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
' FROM HZ_STAGED_PARTY_SITES stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND ORG_CONTACT_ID IN ' ||
' ( SELECT org_contact_id from HZ_ORG_CONTACTS oc, (select object_id, relationship_id, subject_id party_id from hz_relationships ' ||
' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
' where stage1.relationship_id = oc.party_relationship_id ' ||
' and ('||p_restrict_sql1|| ') )' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
' FROM HZ_STAGED_PARTY_SITES stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND EXISTS ('||
' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
' WHERE p.party_id = stage.party_id ' ||
' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
' FROM HZ_STAGED_PARTY_SITES stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND EXISTS ('||
' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
' WHERE p.party_id = stage.party_id ' ||
' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
' AND ('||p_restrict_sql||')' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
FROM HZ_STAGED_CONTACTS stage
WHERE contains( concat_col, p_contains_str)>0
AND EXISTS (
SELECT 1 FROM HZ_STAGED_PARTIES p
WHERE p.PARTY_ID = stage.PARTY_ID
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND stage.party_id = p_party_id;
SELECT ORG_CONTACT_ID, PARTY_ID
FROM HZ_STAGED_CONTACTS stage
WHERE contains( concat_col, p_contains_str)>0
AND EXISTS (
SELECT 1 FROM HZ_STAGED_PARTIES p
WHERE p.PARTY_ID = stage.PARTY_ID
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
WHERE contains( concat_col, p_contains_str)>0
AND d.search_context_id = p_search_ctx_id
AND d.party_id = stage.party_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
l_check := instrb(p_restrict_sql, 'SELECTIVE');
l_check_dt := instrb(p_restrict_sql, 'SELECTIVE_CT');
hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective_ct hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Restrict sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
' FROM HZ_STAGED_CONTACTS stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND EXISTS ('||
' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
' WHERE p.party_id = stage.party_id ' ||
' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND ('||p_restrict_sql||')' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
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 , TX1, TX6, TX5
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND d.search_context_id = p_search_ctx_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND d.party_id = stage.org_contact_id ;
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 , TX1, TX6, TX5
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND d.search_context_id = p_search_ctx_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND d.party_id = stage.party_site_id ;
SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
FROM HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND stage.party_id = p_party_id;
SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
FROM HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND stage.party_id = p_party_id
UNION
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 , TX1, TX6, TX5
FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
WHERE contains( concat_col, p_contains_str)>0
AND r.object_id = p_party_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND r.subject_id = stage.party_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.relationship_id = oc.party_relationship_id
AND oc.org_contact_id = stage.org_contact_id;
SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
FROM HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND EXISTS (
SELECT 1 FROM HZ_STAGED_PARTIES p
WHERE p.PARTY_ID = stage.PARTY_ID
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(p.status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(p.status, 'A') in ('A')) )
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = p.TX36)))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND d.search_context_id = p_search_ctx_id
AND d.party_id = stage.party_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
UNION
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 , TX1, TX6, TX5
FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND d.search_context_id = p_search_ctx_id
AND d.party_id = r.subject_id
AND r.relationship_id = oc.party_relationship_id
AND oc.org_contact_id = stage.org_contact_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR r.subject_id <> p_dup_party_id);
SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
WHERE contains( concat_col, p_contains_str)>0
AND d.search_context_id = p_search_ctx_id
AND d.party_id = stage.party_id
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id);
l_check := instrb(p_restrict_sql, 'SELECTIVE');
l_check_dt := instrb(p_restrict_sql, 'SELECTIVE_CPT');
hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective_cpt hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Restrict Sql has the selective hint',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
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 , TX1, TX6, TX5'||
' FROM HZ_STAGED_CONTACT_POINTS stage'||
' WHERE contains( concat_col, :cont)>0 '||
' AND (stage.org_contact_id is null '||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND ('||p_restrict_sql||'))' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
' UNION ' ||
' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
' FROM HZ_STAGED_CONTACT_POINTS stage'||
' WHERE contains( concat_col, :cont)>0 '||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND (stage.org_contact_id in ' ||
' ( SELECT org_contact_id from HZ_ORG_CONTACTS oc, (select object_id, relationship_id, subject_id party_id from hz_relationships r ' ||
' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
' where stage1.relationship_id = oc.party_relationship_id ' ||
' and ('||p_restrict_sql1 || ') ) )' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
' FROM HZ_STAGED_CONTACT_POINTS stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND EXISTS ('||
' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
' WHERE p.party_id = stage.party_id ' ||
' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
' FROM HZ_STAGED_CONTACT_POINTS stage'||
' WHERE contains( concat_col, :cont)>0'||
' AND EXISTS ('||
' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
' WHERE p.party_id = stage.party_id ' ||
' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
' AND( ('''||l_search_merged||''' =''Y'' ) '||
' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
' AND ('||p_restrict_sql||')' ||
' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
SELECT p_search_ctx_id, PARTY_ID, decode(TX8,g_party_stage_rec.TX8||' ',100,90)
FROM hz_staged_parties
WHERE TX8 LIKE g_party_stage_rec.TX8||' %'
AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = TX36))
AND( (l_search_merged ='Y' )
OR (l_search_merged = 'I' AND nvl(status, 'A') in ('A', 'I'))
OR (l_search_merged = 'N' AND nvl(status, 'A') in ('A')))
AND (p_dup_party_id IS NULL OR party_id <> p_dup_party_id)
AND rownum <= p_thresh;
l_sqlstr := 'INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE) SELECT :ctx_id, PARTY_ID, decode(TX8,:TX8||'' '',100,90) FROM hz_staged_parties stage ';
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
H_SCORES.DELETE;
g_mappings.DELETE;
hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'In eval party site level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'In eval_party_site_level inserting into HZ_MATCHED_PARTY_SITES_GT from the H_PS_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id, h_ps_id(I), h_ps_party_id(I), h_ps_score(I));
hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'In eval contact level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'In eval_contact_level inserting into HZ_MATCHED_CONTACTS_GT from the H_CT_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id, h_ct_id(I), h_ct_party_id(I), h_ct_score(I));
hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'In eval contact point level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'In eval_contact_point_level inserting into HZ_MATCHED_CPTS_GT from the H_CPT_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));
SELECT use_contact_addr_flag, use_contact_cpt_flag
INTO l_use_contact_addr_flag, l_use_contact_cpt_flag
FROM hz_match_rules_b
WHERE match_rule_id = 41;
SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
hz_utility_v2pub.debug(p_message=>'In find_parties inserting Search Rule results into HZ_MATCHED_PARTIES_GT',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
VALUES (l_search_ctx_id,H_SCORES(l_party_id).PARTY_ID,round((H_SCORES(l_party_id).TOTAL_SCORE/l_entered_max_score)*100));
SELECT party_type INTO l_party_type from hz_parties where party_id = p_party_id;
SELECT subject_id INTO l_person_id FROM HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, hz_parties p
WHERE oc.org_contact_id = p_contact_id
AND r.relationship_id = oc.party_relationship_id
AND r.object_id = p_party_id
AND p.party_id = r.subject_id
AND p.party_type = 'PERSON'
AND ROWNUM=1;
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
H_SCORES.DELETE;
g_mappings.DELETE;
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
H_PARTY_ID.DELETE(l_party_id_idx-1);
H_PARTY_ID_LIST.DELETE(l_index);
H_SCORES.DELETE(l_index);
SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
FROM HZ_STAGED_CONTACTS stage
WHERE contains( concat_col, p_contains_str)>0
AND ORG_CONTACT_ID in (
SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
org_contact_id
from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc
where d.party_id = r.subject_id
and oc.party_relationship_id = r.relationship_id
and d.search_context_id = p_search_ctx_id);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID,PARTY_SITE_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id, h_ps_id(I), h_ps_party_id(I), h_ps_score(I));
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID,ORG_CONTACT_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id, h_ct_id(I), h_ct_party_id(I), h_ct_score(I));
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
H_PARTY_ID.DELETE;
H_PARTY_ID_LIST.DELETE;
hz_utility_v2pub.debug(p_message=>'Match rule is AND and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
l_search_ctx_id,H_PARTY_ID(I));
hz_utility_v2pub.debug(p_message=>'Match rule is OR and call type is AND. Inserting into HZ_DQM_PARTIES_GT, from the H_PARTY_ID list',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
hz_utility_v2pub.debug(p_message=>'Inserting into the final array, the person_id - '||l_person_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
INSERT INTO HZ_MATCHED_CPTS_GT (SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id, h_cpt_id(I), h_cpt_party_id(I), h_cpt_score(I));
SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
INSERT INTO HZ_MATCHED_PARTIES_GT (SEARCH_CONTEXT_ID, PARTY_ID, SCORE)
VALUES (l_search_ctx_id,H_SCORES(l_party_id).PARTY_ID,round((H_SCORES(l_party_id).TOTAL_SCORE/l_entered_max_score)*100));
DELETE FROM HZ_MATCHED_PARTY_SITES_GT ps WHERE SEARCH_CONTEXT_ID = x_search_ctx_id
AND NOT EXISTS
(SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ps.PARTY_ID);
DELETE FROM HZ_MATCHED_CONTACTS_GT ct WHERE SEARCH_CONTEXT_ID = x_search_ctx_id
AND NOT EXISTS
(SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = ct.PARTY_ID);
DELETE FROM HZ_MATCHED_CPTS_GT cpt WHERE SEARCH_CONTEXT_ID = x_search_ctx_id
AND NOT EXISTS
(SELECT 1 FROM HZ_MATCHED_PARTIES_GT p WHERE SEARCH_CONTEXT_ID = x_search_ctx_id AND p.PARTY_ID = cpt.PARTY_ID);
SELECT use_contact_addr_flag, use_contact_cpt_flag
INTO l_use_contact_addr_flag, l_use_contact_cpt_flag
FROM hz_match_rules_b
WHERE match_rule_id = 41;
SELECT HZ_MERGE_BATCH_S.nextval INTO x_dup_set_id FROM DUAL;
INSERT INTO HZ_DUP_SETS ( DUP_SET_ID, DUP_BATCH_ID, WINNER_PARTY_ID,
STATUS, MERGE_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE, LAST_UPDATED_BY)
VALUES (x_dup_set_id, p_dup_batch_id, p_party_id, 'SYSBATCH',
'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
hz_utility_pub.user_id);
INSERT INTO HZ_DUP_SET_PARTIES (DUP_PARTY_ID,DUP_SET_ID,MERGE_SEQ_ID,
MERGE_BATCH_ID,merge_flag,SCORE,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,LAST_UPDATED_BY,DUP_SET_BATCH_ID)
VALUES (p_party_id,x_dup_set_id,0,0,
'Y',100,hz_utility_pub.created_by,hz_utility_pub.creation_date,
hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
hz_utility_pub.user_id,p_dup_batch_id);
cursor get_cpts_for_party_sites is select contact_point_id
from hz_contact_points
where owner_table_name = 'HZ_PARTY_SITES'
and primary_flag='Y'
and owner_table_id = p_party_site_id;
cursor get_cpt_for_contact_id is select contact_point_id
from hz_org_contacts a, hz_relationships b, hz_contact_points c
where a.party_relationship_id = b.relationship_id
and c.owner_table_name = 'HZ_PARTIES'
and c.primary_flag='Y'
and c.owner_table_id = b.party_id
and b.directional_flag = 'F'
and a.org_contact_id = p_org_contact_id;
SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
values (l_search_ctx_id,l_match_ps_list(I));
INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE)
VALUES (l_search_ctx_id,H_SCORES(l_party_site_id).PARTY_ID, H_SCORES(l_party_site_id).PARTY_SITE_ID, (H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100);
INSERT INTO HZ_MATCHED_PARTY_SITES_GT (SEARCH_CONTEXT_ID, PARTY_ID, PARTY_SITE_ID, SCORE)
VALUES (l_search_ctx_id,H_SCORES(l_party_site_id).PARTY_ID, H_SCORES(l_party_site_id).PARTY_SITE_ID, round((H_SCORES(l_party_site_id).TOTAL_SCORE/l_entered_max_score)*100));
SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
values (l_search_ctx_id,l_match_contact_list(I));
INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE)
VALUES (l_search_ctx_id,H_SCORES(l_org_contact_id).PARTY_ID, H_SCORES(l_org_contact_id).ORG_CONTACT_ID, (H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100);
INSERT INTO HZ_MATCHED_CONTACTS_GT (SEARCH_CONTEXT_ID, PARTY_ID, ORG_CONTACT_ID, SCORE)
VALUES (l_search_ctx_id,H_SCORES(l_org_contact_id).PARTY_ID, H_SCORES(l_org_contact_id).ORG_CONTACT_ID, round((H_SCORES(l_org_contact_id).TOTAL_SCORE/l_entered_max_score)*100));
SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
INSERT INTO HZ_MATCHED_CPTS_GT(SEARCH_CONTEXT_ID,CONTACT_POINT_ID,PARTY_ID,SCORE) VALUES (
l_search_ctx_id,H_CONTACT_POINT_ID(I),H_PARTY_ID(I),round(H_SCORE(I)/l_entered_max_score)*100);
SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX44,1);
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) ,l_max_TX3,l_max_TX4,l_max_TX11,l_max_idx);
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) ,l_max_idx);
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) ,l_max_TX1,l_max_TX6,l_max_TX5,l_max_idx);
SELECT party_site_id FROM (
SELECT party_site_id,identifying_address_flag
FROM HZ_PARTY_SITES
WHERE party_id = p_party_id
AND (status is null OR status = 'A')
AND identifying_address_flag='Y'
UNION
SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
FROM HZ_PARTY_SITES
WHERE party_id = p_party_id
AND (status is null OR status = 'A')
AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
AND ROWNUM<6
UNION
SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
FROM HZ_PARTY_SITES
WHERE (status is null OR status = 'A')
AND party_id in (
SELECT party_id
FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
WHERE HZ_RELATIONSHIPS.object_id = p_party_id
AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
and l_use_contact_info = 'Y'
)
AND ROWNUM<6
) order by identifying_address_flag desc
) LOOP
l_party_site_ids(ps) := PARTY_SITES.party_site_id;
SELECT CONTACT_POINT_ID
FROM HZ_CONTACT_POINTS
WHERE PRIMARY_FLAG = 'Y'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_party_id
UNION
SELECT CONTACT_POINT_ID
FROM HZ_CONTACT_POINTS,HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
WHERE PRIMARY_FLAG = 'Y'
AND owner_table_name = 'HZ_PARTIES'
AND OWNER_TABLE_ID = HZ_RELATIONSHIPS.party_id
AND HZ_RELATIONSHIPS.object_id = p_party_id
AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
and l_use_contact_info = 'Y'
AND ROWNUM<6
UNION
SELECT CONTACT_POINT_ID
FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
WHERE PRIMARY_FLAG = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND owner_table_id = party_site_id
AND PARTY_ID = p_party_id
AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;
SELECT PARTY_TYPE INTO l_party_type
FROM HZ_PARTIES
WHERE PARTY_ID = p_party_id;
SELECT
translate(HZ_PARTIES.PARTY_NAME, '%',' ')
,translate(HZ_ORGANIZATION_PROFILES.TAX_REFERENCE, '%',' ')
INTO
x_party_search_rec.PARTY_NAME
,x_party_search_rec.TAX_REFERENCE
FROM HZ_PARTIES, HZ_ORGANIZATION_PROFILES
WHERE HZ_PARTIES.party_id = HZ_ORGANIZATION_PROFILES.party_id
AND HZ_ORGANIZATION_PROFILES.effective_end_date is NULL
AND HZ_PARTIES.party_id = p_party_id;
SELECT
translate(HZ_PARTIES.PARTY_NAME, '%',' ')
,translate(HZ_PERSON_PROFILES.TAX_REFERENCE, '%',' ')
INTO
x_party_search_rec.PARTY_NAME
,x_party_search_rec.TAX_REFERENCE
FROM HZ_PARTIES, HZ_PERSON_PROFILES
WHERE HZ_PARTIES.party_id = HZ_PERSON_PROFILES.party_id
AND HZ_PERSON_PROFILES.effective_end_date is NULL
AND HZ_PARTIES.party_id = p_party_id;
SELECT party_site_id
,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
FROM HZ_PARTY_SITES, HZ_LOCATIONS
WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
SELECT org_contact_id
FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
WHERE HZ_ORG_CONTACTS.org_contact_id = cp_org_contact_id
AND HZ_RELATIONSHIPS.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZ_RELATIONSHIPS.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZ_RELATIONSHIPS.DIRECTIONAL_FLAG = 'F'
AND HZ_ORG_CONTACTS.party_relationship_id = HZ_RELATIONSHIPS.relationship_id
AND HZ_RELATIONSHIPS.subject_id = HZ_PERSON_PROFILES.party_id
AND HZ_PERSON_PROFILES.effective_end_date IS NULL;
SELECT contact_point_id, contact_point_type
,translate(HZ_CONTACT_POINTS.EMAIL_ADDRESS, '%',' ')
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = cp_contact_point_id;
SELECT 1 FROM HZ_TRANS_FUNCTIONS_VL
WHERE nvl(STAGED_FLAG,'N') = 'N'
AND FUNCTION_ID in (
6
,12
,49
,66
,67
,74
,110
,114
,115
);