[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_interface_rec (
p_party_id IN NUMBER,
p_record_id IN NUMBER,
p_party_site_id IN NUMBER,
p_org_contact_id IN NUMBER,
p_entity IN VARCHAR2,
p_operation IN VARCHAR2,
p_staged_flag IN VARCHAR2 DEFAULT 'N'
);
PROCEDURE insert_into_interface(p_party_id IN NUMBER
) IS
l_char NUMBER;
select 'Y' into l_char
from hz_dqm_sync_interface
where party_id = p_party_id
and entity = 'PARTY'
and staged_flag in ('N', 'Y')
and rownum = 1;
insert_interface_rec (p_party_id, null, null, null, 'PARTY', 'U', 'Y');
END insert_into_interface;
SELECT 1
FROM hz_dqm_stage_log
WHERE operation = 'SHADOW_STAGING'
AND STEP = 'COMPLETE';
cursor l_party_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and a.entity = 'PARTY' AND REALTIME_SYNC_FLAG='Y';
cursor l_ps_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and a.entity = 'PARTY_SITES' AND REALTIME_SYNC_FLAG='Y';
cursor l_ct_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and entity = 'CONTACTS' AND REALTIME_SYNC_FLAG='Y';
cursor l_cp_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and entity = 'CONTACT_POINTS' AND REALTIME_SYNC_FLAG='Y';
update hz_staged_parties a set concat_col = concat_col
where a.party_id = L_PARTY_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
update hz_staged_party_sites a set concat_col = concat_col
where a.party_site_id = L_RECORD_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
update hz_staged_contacts a set concat_col = concat_col
where a.org_contact_id = L_RECORD_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
update hz_staged_contact_points a set concat_col = concat_col
where a.contact_point_id = L_RECORD_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
cursor l_party_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and a.entity = 'PARTY';
cursor l_ps_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and a.entity = 'PARTY_SITES';
cursor l_ct_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and entity = 'CONTACTS' ;
cursor l_cp_cur is select rowid, party_id, record_id
from hz_dqm_sync_interface a
where a.staged_flag = 'Y'
and entity = 'CONTACT_POINTS';
update hz_staged_parties a set concat_col = concat_col
where a.party_id = L_PARTY_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
update hz_staged_party_sites a set concat_col = concat_col
where a.party_site_id = L_RECORD_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
update hz_staged_contacts a set concat_col = concat_col
where a.org_contact_id = L_RECORD_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
update hz_staged_contact_points a set concat_col = concat_col
where a.contact_point_id = L_RECORD_ID(I);
delete from hz_dqm_sync_interface
where rowid = L_ROWID(I);
SELECT 1 from hz_dqm_sync_interface
WHERE entity = p_entity
AND record_id = p_record_id
AND staged_flag = 'E';
SELECT 1 from hz_dqm_sync_interface
WHERE entity = p_entity
AND party_id = p_party_id
AND staged_flag = 'E';
insert_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
SELECT oc.org_contact_id
FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc
WHERE pr.relationship_id = oc.party_relationship_id
AND pr.subject_id = p_party_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.directional_flag = 'F';
insert_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
insert_interface_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U');
insert_sh_interface_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U');
SELECT ps.party_id,p.party_type INTO l_party_id, l_party_type
FROM HZ_PARTY_SITES ps, HZ_PARTIES p
WHERE party_site_id = p_party_site_id
AND p.PARTY_ID = ps.PARTY_ID;
SELECT r.object_id, org_contact_id INTO l_party_id1,l_org_contact_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE r.party_id = l_party_id
AND r.relationship_id = oc.party_relationship_id
AND r.directional_flag='F'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
insert_interface_rec(l_party_id1,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_create_upd);
insert_sh_interface_rec(l_party_id1,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_create_upd);
SELECT r.object_id INTO l_party_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE oc.org_contact_id = p_org_contact_id
AND oc.party_relationship_id = r.relationship_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND subject_type ='PERSON'
AND DIRECTIONAL_FLAG= 'F'
AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
AND (r.status is null OR r.status = 'A' or r.status = 'I') ;
SELECT r.object_id INTO l_party_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE oc.org_contact_id = p_org_contact_id
AND oc.party_relationship_id = r.relationship_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND subject_type ='PERSON'
AND DIRECTIONAL_FLAG= 'F';
insert_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_create_upd);
insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_create_upd);
SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_id;
SELECT p.party_id, ps.party_site_id, party_type
INTO l_party_id1, l_party_site_id, l_party_type
FROM HZ_PARTY_SITES ps, HZ_PARTIES p
WHERE party_site_id = l_ot_id
AND p.party_id = ps.party_id;
SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE r.party_id = l_party_id1
AND r.relationship_id = oc.party_relationship_id
AND r.directional_flag='F'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
SELECT party_type INTO l_party_type
FROM hz_parties
WHERE party_id = l_ot_id;
SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE r.party_id = l_ot_id
AND r.relationship_id = oc.party_relationship_id
AND r.directional_flag='F'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
insert_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_create_upd);
insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_create_upd);
SELECT org_contact_id
FROM hz_org_contacts
WHERE party_relationship_id = p_relationship_id
AND status = 'A';
SELECT party_site_id
FROM hz_party_Sites
WHERE location_id = p_location_id
AND (status = 'A' or status = 'I') ;
SELECT c.PARTY_ID, p.PARTY_TYPE
FROM HZ_CUST_ACCOUNTS c, HZ_PARTIES p
WHERE c.cust_account_id = p_cust_acct_id
AND p.party_id = c.party_id
AND NOT EXISTS (
SELECT d.PARTY_ID
FROM HZ_DQM_SYNC_INTERFACE d
WHERE d.ENTITY = 'PARTY'
AND d.PARTY_ID = c.PARTY_ID
AND d.STAGED_FLAG = 'N');
PROCEDURE insert_error_rec (
p_party_id IN NUMBER,
p_record_id IN NUMBER,
p_party_site_id IN NUMBER,
p_org_contact_id IN NUMBER,
p_entity IN VARCHAR2,
p_operation IN VARCHAR2,
p_staged_flag IN VARCHAR2 DEFAULT 'E',
p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
p_error_data IN VARCHAR2
) IS
BEGIN
INSERT INTO hz_dqm_sync_interface (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
ERROR_DATA,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
p_party_id,
p_record_id,
p_party_site_id,
p_org_contact_id,
p_entity,
p_operation,
p_staged_flag,
p_realtime_sync_flag,
p_error_data,
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,
HZ_DQM_SYNC_INTERFACE_S.nextval
);
END insert_error_rec;
PROCEDURE insert_sh_interface_rec (
p_party_id IN NUMBER,
p_record_id IN NUMBER,
p_party_site_id IN NUMBER,
p_org_contact_id IN NUMBER,
p_entity IN VARCHAR2,
p_operation IN VARCHAR2,
p_staged_flag IN VARCHAR2 DEFAULT 'N'
) IS
is_real_time VARCHAR2(1);
SELECT
p_entity AS entity
,p_party_id AS party_id
,p_record_id AS record_id
,p_party_site_id AS party_site_id
,p_org_contact_id AS org_contact_id
FROM dual ) T
ON (S.entity = T.entity AND
S.party_id = T.party_id AND
NVL(S.record_id,-99) = NVL(T.record_id,-99) AND
NVL(S.party_site_id, -99) = NVL(T.party_site_id,-99) AND
NVL(S.org_contact_id,-99) = NVL(T.org_contact_id,-99) AND
S.staged_flag <> 'E')
WHEN NOT MATCHED THEN
INSERT (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
p_party_id,
p_record_id,
p_party_site_id,
p_org_contact_id,
p_entity,
p_operation,
p_staged_flag,
is_real_time,
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,
HZ_DQM_SH_SYNC_INTERFACE_S.nextval
);
END insert_sh_interface_rec;
PROCEDURE insert_interface_rec (
p_party_id IN NUMBER,
p_record_id IN NUMBER,
p_party_site_id IN NUMBER,
p_org_contact_id IN NUMBER,
p_entity IN VARCHAR2,
p_operation IN VARCHAR2,
p_staged_flag IN VARCHAR2 DEFAULT 'N'
) IS
is_real_time VARCHAR2(1);
INSERT INTO HZ_DQM_SYNC_INTERFACE(
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
p_party_id,
p_record_id,
p_party_site_id,
p_org_contact_id,
p_entity,
p_operation,
p_staged_flag,
is_real_time,
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,
HZ_DQM_SYNC_INTERFACE_S.nextval
);
SELECT
p_entity AS entity
,p_party_id AS party_id
,p_record_id AS record_id
,p_party_site_id AS party_site_id
,p_org_contact_id AS org_contact_id
FROM dual ) T
ON (S.entity = T.entity AND
S.party_id = T.party_id AND
NVL(S.record_id,-99) = NVL(T.record_id,-99) AND
NVL(S.party_site_id, -99) = NVL(T.party_site_id,-99) AND
NVL(S.org_contact_id,-99) = NVL(T.org_contact_id,-99) AND
S.staged_flag <> 'E' AND
S.operation <> 'D')
WHEN NOT MATCHED THEN
INSERT (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
p_party_id,
p_record_id,
p_party_site_id,
p_org_contact_id,
p_entity,
p_operation,
p_staged_flag,
is_real_time,
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,
HZ_DQM_SYNC_INTERFACE_S.nextval
);
END insert_interface_rec;
SELECT party_type INTO l_party_type
FROM HZ_PARTIES
WHERE party_id = p_from_id;
UPDATE HZ_STAGED_PARTIES
SET STATUS = 'M'
WHERE party_id = p_from_id;
ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN--Bug 13810656 Deferring the update of 'M' Status operation of Merge flow
IF l_party_type='ORGANIZATION' THEN
sync_org(p_from_id, 'D');
DELETE FROM HZ_STAGED_PARTY_SITES
WHERE party_site_id = p_from_id;
ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN--Bug 13810656 Deferring the delete of from party_site_id for Merge flow
sync_party_site (p_from_id,'D');
IF p_from_fk_id = p_to_fk_id THEN--Not Sure this will ever occur as Merge always deletes and creates a new PS for Merge/Transfer Case.
SELECT party_id INTO l_party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = p_from_id;
SELECT owner_table_id
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_id = cp_id;
SELECT oc.org_contact_id
FROM HZ_CONTACT_POINTS cp, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
WHERE owner_table_name = 'HZ_PARTIES'
AND contact_point_id = cp_id
AND rl.PARTY_ID = cp.owner_table_id
AND oc.party_relationship_id = rl.relationship_id
AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rl.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rl.DIRECTIONAL_FLAG = 'F';
SELECT oc.org_contact_id
FROM HZ_PARTY_SITES ps, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
WHERE ps.party_site_id = ps_id
AND rl.PARTY_ID = ps.party_id
AND oc.party_relationship_id = rl.relationship_id
AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rl.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rl.DIRECTIONAL_FLAG = 'F';
DELETE FROM HZ_STAGED_CONTACT_POINTS
WHERE contact_point_id = p_from_id;
ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN--Bug 13810656 Deferring the delete of from contact_point_id for Merge flow
BEGIN
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE STAGED_FLAG='N'
AND RECORD_ID=p_from_id AND ENTITY='CONTACT_POINTS';
SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
FROM hz_contact_points
WHERE contact_point_id = l_contact_point_id;
SELECT party_id INTO l_party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = l_ot_id;
SELECT party_type INTO l_party_type
FROM hz_parties
WHERE party_id = l_ot_id;
SELECT relationship_id, object_id INTO l_pr_id, l_party_id
FROM hz_relationships --bug 4500011 replaced hz_party_relationships with hz_relationships
WHERE party_id = l_ot_id
AND subject_table_name = 'HZ_PARTIES'
AND object_table_name = 'HZ_PARTIES'
AND directional_flag = 'F';
SELECT count(1) INTO l_num_ocs
FROM HZ_ORG_CONTACTS
WHERE party_relationship_id = l_pr_id;
DELETE FROM HZ_STAGED_CONTACTS
WHERE org_contact_id = p_from_id;
ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN----Bug 13810656 Deferring the delete of from contacts for Merge flow
sync_contact(p_from_id,'D');
SELECT pr.party_id, pr.object_id INTO l_rel_party_id, l_party_id
FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc --bug 4500011 replaced hz_party_relationships with hz_relationships
WHERE oc.org_contact_id = l_org_contact_id
AND pr.relationship_id = oc.party_relationship_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.directional_flag = 'F';
UPDATE HZ_STAGED_PARTY_SITES
SET party_id = l_party_id
WHERE party_site_id in (
SELECT party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = l_rel_party_id
AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'));
UPDATE HZ_STAGED_CONTACT_POINTS
SET party_id = l_party_id
WHERE contact_point_id in (
SELECT contact_point_id
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = l_rel_party_id
AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'));
ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN----Bug 13810656 Deferring the update of relationships for Merge flow
BEGIN
FOR I IN (SELECT party_site_id
FROM HZ_PARTY_SITES
WHERE party_id = l_rel_party_id
AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'))
LOOP
sync_party_site (I.party_site_id, 'U');
FOR I IN (SELECT contact_point_id
FROM HZ_CONTACT_POINTS
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = l_rel_party_id
AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'))
LOOP
sync_contact_point (I.contact_point_id, 'U');
select 'Y' into l_dqm_run
from HZ_TRANS_FUNCTIONS_VL
where STAGED_FLAG='Y'
and nvl(ACTIVE_FLAG,'Y')='Y'
and rownum = 1;
update HZ_DQM_SYNC_INTERFACE set STAGED_FLAG = 'P'
where STAGED_FLAG = 'N' and REALTIME_SYNC_FLAG = 'Y'
returning party_id, record_id, entity, operation, rowid BULK COLLECT into
l_party_id, l_record_id, l_entity, l_operation, l_rowid;
select party_type into l_party_type from hz_parties where party_id = l_party_id(i);
insert_into_interface(l_party_id(i));
DELETE FROM hz_dqm_sync_interface WHERE rowid = l_rowid(i) ;
UPDATE hz_dqm_sync_interface SET staged_flag = 'Y' WHERE rowid = l_rowid(i);
UPDATE hz_dqm_sync_interface SET error_data = l_sql_error_message, staged_flag = 'E' WHERE ROWID = l_rowid(i);
SELECT 'Y' INTO bool
FROM HZ_DQM_SYNC_GT
WHERE ROWNUM = 1;
log ('For Update Organization Party');
log ('For Update Person Party');
log ('For Update Party Sites');
log ('For Update Contacts');
log ('For Update Contact Points');
DELETE FROM hz_dqm_sync_interface
WHERE staged_flag = 'N'
AND error_data IS NULL
AND sync_interface_num BETWEEN p_from_rec AND p_to_rec;
CURSOR c_non_indexed IS select 1 from hz_dqm_stage_log where operation= 'ALTER_INDEX'
and start_flag = 'Y' and end_flag ='Y';
FOR I in (SELECT PARTY_ID,RECORD_ID,ENTITY FROM HZ_DQM_SYNC_INTERFACE WHERE OPERATION='D' AND STAGED_FLAG='N')
LOOP
IF (I.ENTITY='PARTY')
THEN
BEGIN
UPDATE HZ_STAGED_PARTIES
SET STATUS = 'M'
WHERE party_id = I.PARTY_ID;
DELETE FROM HZ_STAGED_PARTY_SITES
WHERE party_site_id = I.RECORD_ID;
DELETE FROM HZ_STAGED_CONTACTS
WHERE org_contact_id = I.RECORD_ID;
DELETE FROM HZ_STAGED_CONTACT_POINTS
WHERE contact_point_id = I.RECORD_ID;
/* Delete records from HZ_DQM_SYNC_INTERFACE table if that record already exists in staged table*/
log('Start Time for delete statements = ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='PARTY' and operation='C'
and party_id in (select /*+ parallel_index(s) index_ffs(s) */ party_id from hz_staged_parties s );
log (' After delete duplicate party entity records, time= '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='PARTY_SITES' and operation='C'
and record_id in (select /*+ parallel_index(s) index_ffs(s) */ party_site_id from hz_staged_party_sites s );
log (' After delete duplicate party site entity records '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='CONTACTS' and operation='C'
and record_id in (select /*+ parallel_index(s) index_ffs(s) */ org_contact_id from hz_staged_contacts s );
log (' After delete duplicate contact entity records = '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='CONTACT_POINTS' and operation='C'
and record_id in (select /*+ parallel_index(s) index_ffs(s) */ contact_point_id from hz_staged_contact_points s );
log (' After delete duplicate contact point entity records = '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
log('End Time for delete statements = ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
SELECT min(sync_interface_num), max(sync_interface_num) into l_min_id, l_max_id
FROM hz_dqm_sync_interface
WHERE staged_flag = 'N';
log ('Data Workers only create/update data');
select request_id BULK COLLECT into l_sub_requests
from fnd_concurrent_requests R
where parent_request_id = l_request_id
and concurrent_program_id = 46839
and (phase_code<>'C' or status_code<>'C');
log ('For Update Organization Party');
log ('For Update Person Party');
log ('For Update Party Sites');
log ('For Update Contacts');
log ('For Update Contact Points');
( SELECT 'Y' FROM
(
select
u.name pnd_index_owner,
idx_name pnd_index_name,
ixp_name pnd_partition_name,
pnd_rowid,
pnd_timestamp
from ctxsys.dr$pending, ctxsys.dr$index i, ctxsys.dr$index_partition p,
sys.user$ u
where idx_owner# = u.user#
and idx_id = ixp_idx_id
and pnd_pid = ixp_id
and pnd_pid <> 0
and pnd_cid = idx_id
UNION ALL
select
u.name pnd_index_owner,
idx_name pnd_index_name,
null pnd_partition_name,
pnd_rowid,
pnd_timestamp
from ctxsys.dr$pending, ctxsys.dr$index i, sys.user$ u
where idx_owner# = u.user#
and pnd_pid = 0
and pnd_cid = idx_id
)
WHERE PND_INDEX_NAME = p_index_name
and pnd_index_owner=p_owner_name
and rownum =1
)
LOOP
RETURN TRUE ;
SELECT COUNT(*) INTO index_cnt FROM HZ_DQM_STAGE_LOG WHERE OPERATION='ALTER_INDEX';
insert into hz_dqm_stage_log(OPERATION, NUMBER_OF_WORKERS,WORKER_NUMBER,STEP,START_FLAG,START_TIME,END_FLAG,
END_TIME,LAST_UPDATE_DATE, CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN )
values ('ALTER_INDEX',1,1,'STAGED_TABLES','Y',sysdate,'Y',null,sysdate,sysdate,
fnd_global.user_id,fnd_global.user_id,fnd_global.login_id);
update hz_dqm_stage_log set start_flag='Y',end_flag='Y',start_time=sysdate,
last_update_date=sysdate,last_update_login=fnd_global.login_id
where operation='ALTER_INDEX';
insert into hz_dqm_stage_log(OPERATION, NUMBER_OF_WORKERS,WORKER_NUMBER,STEP,START_FLAG,START_TIME,END_FLAG,
END_TIME,LAST_UPDATE_DATE, CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
values ('ALTER_INDEX',1,1,'STAGED_TABLES','N',sysdate,'N',null,sysdate,sysdate,
fnd_global.user_id,fnd_global.user_id,fnd_global.login_id);
update hz_dqm_stage_log set start_flag='N',end_flag='N',start_time=sysdate,
last_update_date=sysdate,last_update_login=fnd_global.login_id
where operation='ALTER_INDEX';
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');