[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_match_details (
p_search_ctx_id IN NUMBER,
p_dup_set_id IN NUMBER,
p_dup_party_id IN NUMBER
);
select count(*)
from hz_dup_set_parties dsp, hz_dup_sets ds
where dsp.dup_set_id = ds.dup_set_id
and ds.dup_batch_id = cp_dup_batch_id
and ds.winner_party_id <> dsp.dup_party_id
and merge_flag = 'Y'
and rownum =1;
select dup_party_id
from hz_dup_set_parties
where dup_set_id in (select dup_set_id from hz_dup_sets where dup_batch_id=cp_dup_batch_id)
group by dup_party_id
having count(*)>1;
select ds.dup_set_id
FROM hz_dup_set_parties dsp, hz_dup_sets ds
where dsp.dup_set_id=ds.dup_set_id
and ds.dup_batch_id=cp_dup_batch_id
and dsp.dup_party_id=cp_dup_party_id
order by ds.dup_set_id;
EXECUTE IMMEDIATE 'SELECT HZ_MATCH_RULE_'||p_rule_id||'.check_staged_var from dual' INTO l_staged_var;
select rule_purpose into match_rule_purpose
from hz_match_rules_vl
where match_rule_id = p_rule_id ;
select count(*) into batch_count from hz_dup_batch where dup_batch_id=l_batch_id
and request_id is not null;
select nvl(automerge_flag,'N') into l_automerge_flag
from hz_dup_batch
where dup_batch_id = l_batch_id;
select HZ_DUP_BATCH_S.NEXTVAL into l_new_batch_id from dual;
insert into hz_dl_selected_criteria
(select hz_dl_selected_criteria_s.NEXTVAL, 'SDIB', l_new_batch_id, attribute_name,operation,value,fnd_global.user_id,
sysdate,fnd_global.login_id,fnd_global.user_id,sysdate from hz_dl_selected_criteria
where batch_id=l_batch_id and batch_type='SDIB');
select dup_batch_name into l_batch_name from hz_dup_batch where dup_batch_id = l_batch_id;
HZ_DUP_BATCH_PKG.Insert_Row(l_batch_id,l_batch_name,p_rule_id,fnd_global.resp_appl_id,
'SYSTEM_GENERATED',fnd_global.user_id,sysdate,fnd_global.login_id,
sysdate,fnd_global.user_id);
UPDATE HZ_DUP_BATCH set automerge_flag = l_automerge_flag, last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID,last_updated_by = FND_GLOBAL.USER_ID
Where dup_batch_id = l_batch_id;
UPDATE HZ_DUP_BATCH
SET REQUEST_ID = hz_utility_v2pub.request_id
WHERE dup_batch_id = l_batch_id;
(select request_id
from fnd_concurrent_requests a
where concurrent_program_id = 44445
and phase_code = 'R'
and substr(argument_text, 1, instr(argument_text,',') -1 )
in ( select match_rule_id from hz_match_rules_vl where rule_purpose = 'Q')
order by actual_start_date
)
LOOP
log('Request Id of the concurrent program running already is ' || req_cur.request_id );
SELECT dup_batch_id INTO l_batch_id
FROM hz_dup_batch
where request_id = FND_GLOBAL.conc_request_id;
DELETE from hz_dup_set_parties
WHERE dup_set_id=l_dup_dup_set
AND dup_party_id=l_dup_dup_id;
SELECT count(1) INTO l_num_left
FROM hz_dup_set_parties
WHERE dup_set_id=l_dup_dup_set;
SELECT DUP_PARTY_ID INTO l_winner_id FROM HZ_DUP_SET_PARTIES
WHERE dup_set_id = l_dup_dup_set;
log('Winner party '||l_winner_id||' is the only party in dup set '||l_dup_dup_set||' and this party will be deleted frm the current batch ');
log('Delete dup sets with one party from hz_dup_set_parties, dup set id'||l_dup_dup_set);
DELETE from hz_dup_set_parties
WHERE dup_set_id=l_dup_dup_set;
log('Delete dup sets with no parties from hz_dup_sets, dup set id'||l_dup_dup_set);
DELETE from hz_dup_sets
WHERE dup_set_id=l_dup_dup_set;
DELETE FROM hz_dup_sets d1 WHERE dup_batch_id = l_batch_id
AND NOT EXISTS (SELECT 1 FROM hz_dup_set_parties
WHERE dup_set_id = d1.dup_set_id);
log('Delete dup sets with no parties from hz_dup_sets'||SQL%ROWCOUNT);
Select request_id BULK COLLECT into l_sub_requests
from Fnd_Concurrent_Requests R
Where Parent_Request_Id = FND_GLOBAL.conc_request_id
and (phase_code<>'C' or status_code<>'C');
select automerge_flag into l_automerge_flag
from hz_dup_batch
where dup_batch_id = l_batch_id;
update hz_dup_batch
set automerge_flag = 'N',
request_type = 'SYSTEM_AUTOMERGE'
where dup_batch_id = l_batch_id;
select dup_party_id
from hz_dup_set_parties
where dup_set_batch_id = cp_dup_batch_id --Bug No: 4244529
group by dup_party_id
having count(*)>1;
select dsp.dup_set_id
FROM hz_dup_set_parties dsp
where dsp.dup_set_batch_id = cp_dup_batch_id --Bug No: 4244529
and dsp.dup_party_id=cp_dup_party_id;*/
select rule_purpose into match_rule_purpose
from hz_match_rules_vl
where match_rule_id = l_rule_id ;
SELECT match_score, auto_merge_score
INTO l_match_score, l_auto_merge_score
FROM hz_match_rules_vl
WHERE match_rule_id = l_rule_id;
log('Start Time before insert to chunk ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
'insert /*+ APPEND */ into hz_dup_worker_chunk_gt
SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ parties.PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP''
AND NVL(parties.STATUS,''A'') = ''A'' AND mod(parties.PARTY_ID, :num_workers) = :worker_number '
USING l_num_workers, l_worker_number;
log('Number of parties inserted into HZ_DUP_WORKER_CHUNK_GT by worker '||l_worker_number||' is '||SQL%ROWCOUNT );
'insert /*+ APPEND */ into hz_dup_worker_chunk_gt
SELECT /*+ INDEX(parties HZ_PARTIES_U1) */ PARTY_ID FROM HZ_PARTIES parties WHERE parties.PARTY_TYPE <> ''PARTY_RELATIONSHIP'' AND NVL(parties.STATUS,''A'') = ''A''
AND mod(parties.PARTY_ID, :num_workers) = :worker_number AND '||
p_subset_defn
USING l_num_workers, l_worker_number;
log('Number of parties inserted into HZ_DUP_WORKER_CHUNK_GT by worker '||l_worker_number||' is '||SQL%ROWCOUNT );
log('End Time after insert to chunk ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
SELECT PARTY_ID
FROM HZ_DUP_WORKER_CHUNK_GT ORDER BY PARTY_ID)
LOOP
l_cur_party_id := EVALPARTY.PARTY_ID;
l_subset_defn := 'EXISTS (select 1 FROM HZ_PARTIES parties '||
'where parties.party_id = stage.party_id ' ||
'and '||p_subset_defn||')';
SELECT dup_party_id
FROM hz_dup_set_parties dp
WHERE dp.DUP_SET_BATCH_ID = p_batch_id --Bug No: 4244529
AND dp.DUP_PARTY_ID = p_party_id
AND dp.DUP_SET_ID <> p_dup_set_id;
DELETE FROM HZ_DUP_SET_PARTIES p
WHERE p.dup_set_id = p_dup_set_id
AND p.dup_party_id <> p_cur_party_id
AND EXISTS (
SELECT 1 FROM HZ_DUP_EXCLUSIONS d
WHERE (
(d.PARTY_ID=p_cur_party_id AND d.DUP_PARTY_ID=p.dup_party_id)
OR
(d.PARTY_ID=p.dup_party_id AND d.DUP_PARTY_ID=p_cur_party_id)
) AND (d.FROM_DATE IS NULL OR d.FROM_DATE <= SYSDATE)
AND (d.TO_DATE IS NULL OR d.TO_DATE >= SYSDATE)
);
SELECT COUNT(*) INTO dupset_count FROM HZ_DUP_SET_PARTIES
WHERE DUP_SET_ID = p_dup_set_id;
SELECT COUNT(*) INTO winner_count FROM HZ_DUP_SET_PARTIES
WHERE DUP_SET_ID = p_dup_set_id
and dup_party_id in
(select winner_party_id from hz_dup_sets where dup_set_id=p_dup_set_id);
DELETE FROM HZ_DUP_SET_PARTIES WHERE DUP_SET_ID=p_dup_set_id;
DELETE FROM HZ_DUP_SETS WHERE DUP_SET_ID=p_dup_set_id;
PROCEDURE insert_match_details (
p_search_ctx_id IN NUMBER,
p_dup_set_id IN NUMBER,
p_dup_party_id IN NUMBER
) IS
BEGIN
INSERT INTO HZ_DUP_MATCH_DETAILS (
DUP_SET_ID,
DUP_PARTY_ID,
WINNER_PARTY_VALUE,
MATCHED_PARTY_VALUE,
MATCHED_ATTRIBUTE,
ASSIGNED_SCORE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) SELECT p_dup_set_id,
p_dup_party_id,
gt.ENTERED_VALUE,
gt.MATCHED_VALUE,
vl.USER_DEFINED_ATTRIBUTE_NAME, -- Bug No: 3820598
gt.ASSIGNED_SCORE,
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
FROM hz_party_score_dtls_gt gt,hz_trans_attributes_vl vl -- Bug No: 3820598
WHERE gt.PARTY_ID = p_dup_party_id
AND gt.SEARCH_CONTEXT_ID = p_search_ctx_id
AND gt.ENTITY = vl.ENTITY_NAME
AND gt.ATTRIBUTE=vl.ATTRIBUTE_NAME;
END insert_match_details;
l_mr_last_updated_date HZ_MATCH_RULES_VL.last_update_date%type;
SELECT count(1)
INTO l_count
FROM hz_dup_match_details
WHERE dup_set_id = p_dup_set_id;
SELECT winner_party_id,creation_date INTO l_winner_party_id,l_dup_set_creation_date
FROM HZ_DUP_SETS
WHERE dup_set_id = p_dup_set_id;
select last_update_date,compilation_flag INTO l_mr_last_updated_date,l_mr_comp_flag
from HZ_MATCH_RULES_VL
where match_rule_id = p_rule_id;
IF l_mr_comp_flag <> 'C' OR l_mr_last_updated_date > l_dup_set_creation_date THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_CDL_NO_MATCH_DETAILS');
FOR DUP IN (SELECT DUP_PARTY_ID
FROM HZ_DUP_SET_PARTIES
WHERE DUP_SET_ID = p_dup_set_id and dup_party_id <> l_winner_party_id) LOOP
HZ_PARTY_SEARCH.get_score_details (
FND_API.G_FALSE,p_rule_id, DUP.DUP_PARTY_ID,
l_party_rec, l_party_site_list,l_contact_list, l_cpt_list,
l_search_ctx_id, l_return_status, l_msg_count, l_msg_data);
insert_match_details (l_search_ctx_id, p_dup_set_id, DUP.DUP_PARTY_ID);
FND_MSG_PUB.Delete_Msg;