[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid,
incident_id,
customer_id,
bill_to_contact_id,
ship_to_contact_id,
bill_to_party_id,
ship_to_party_id,
last_update_program_code
FROM cs_incidents_all_b
WHERE p_from_fk_id in (customer_id, bill_to_contact_id, ship_to_contact_id,
bill_to_party_id, ship_to_party_id )
FOR UPDATE NOWAIT;
l_last_update_program_code VARCHAR2_30_TBL;
SELECT merge_reason_code
INTO l_merge_reason_code
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
LOOP -- Loop for BULK selecting and processing the BULK selection in a batch of 1000
FETCH c1 BULK COLLECT INTO l_rowid_tbl,
l_incident_id,
l_customer_id,
l_bill_to_contact_id,
l_ship_to_contact_id ,
l_bill_to_party_id,
l_ship_to_party_id ,
l_last_update_program_code
LIMIT 1000 ;
-- if no records were found to be updated then stop and return to calling prg.
-- IF l_rowid_tbl.count = 0 THEN
-- RETURN;
UPDATE cs_incidents_all_b
SET customer_id = decode(customer_id, p_from_fk_id,
p_to_fk_id, customer_id),
bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id,
p_to_fk_id, bill_to_contact_id),
ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id,
p_to_fk_id, ship_to_contact_id),
bill_to_party_id = decode(bill_to_party_id, p_from_fk_id,
p_to_fk_id, bill_to_party_id),
ship_to_party_id = decode(ship_to_party_id, p_from_fk_id,
p_to_fk_id, ship_to_party_id),
object_version_number = object_version_number + 1,
incident_last_modified_date = sysdate ,
last_update_program_code = 'PARTY_MERGE',
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE rowid = l_rowid_tbl(i);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code(i) ;
l_audit_vals_rec.last_update_program_code := 'PARTY_MERGE' ;
l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
l_audit_vals_rec.updated_entity_id := l_incident_id(i);
p_last_update_date => SYSDATE,
p_creation_date => SYSDATE,
p_comments => NULL,
x_audit_id => l_audit_id);
END LOOP ; -- End Loop for BULK selecting and processing the BULK selection in a batch of 1000
SELECT rowid,incident_id ,
bill_to_site_use_id ,
ship_to_site_use_id,
last_update_program_code
FROM cs_incidents_all_b
WHERE p_from_fk_id in (bill_to_site_use_id, ship_to_site_use_id)
FOR UPDATE NOWAIT;
l_last_update_program_code VARCHAR2_30_TBL;
SELECT merge_reason_code
INTO l_merge_reason_code
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
l_last_update_program_code
LIMIT 1000;
UPDATE cs_incidents_all_b
SET bill_to_site_use_id = decode(bill_to_site_use_id, p_from_fk_id, p_to_fk_id,
bill_to_site_use_id ),
ship_to_site_use_id = decode(ship_to_site_use_id, p_from_fk_id, p_to_fk_id,
ship_to_site_use_id ),
object_version_number = object_version_number + 1,
incident_last_modified_date = sysdate,
last_update_program_code = 'PARTY_MERGE',
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE rowid = l_rowid_tbl(i);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code(i) ;
l_audit_vals_rec.last_update_program_code := 'PARTY_MERGE' ;
l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
l_audit_vals_rec.updated_entity_id := l_incident_id(i);
p_last_update_date => SYSDATE,
p_creation_date => SYSDATE,
p_comments => NULL,
x_audit_id => l_audit_id);
END LOOP ; -- End Loop for BULK selecting and processing the BULK selection in a batch of 1000
SELECT rowid,
incident_id ,
site_id,
customer_site_id,
install_site_use_id,
bill_to_site_id,
ship_to_site_id,
install_site_id,
incident_location_id ,
incident_location_type,
last_update_program_code
FROM cs_incidents_all_b
WHERE p_from_fk_id IN (site_id, customer_site_id, install_site_use_id,
bill_to_site_id, ship_to_site_id, install_site_id)
OR (incident_location_type = 'HZ_PARTY_SITE' AND incident_location_id = p_from_fk_id)
FOR UPDATE NOWAIT;
l_last_update_program_code VARCHAR2_30_TBL ;
SELECT merge_reason_code
INTO l_merge_reason_code
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
l_last_update_program_code
LIMIT 1000 ;
UPDATE cs_incidents_all_b
SET site_id = decode(site_id, p_from_fk_id, p_to_fk_id, site_id),
customer_site_id = decode(customer_site_id, p_from_fk_id, p_to_fk_id,
customer_site_id),
bill_to_site_id = decode(bill_to_site_id , p_from_fk_id, p_to_fk_id,
bill_to_site_id ),
ship_to_site_id = decode(ship_to_site_id , p_from_fk_id, p_to_fk_id,
ship_to_site_id ),
install_site_id = decode(install_site_id , p_from_fk_id, p_to_fk_id,
install_site_id ),
install_site_use_id = decode(install_site_use_id, p_from_fk_id, p_to_fk_id,
install_site_use_id ),
incident_location_id = Decode(NVL(incident_location_type,'XXX') ,
'HZ_LOCATION',incident_location_id ,
'HZ_PARTY_SITE',decode(incident_location_id,
p_from_fk_id,p_to_fk_id,incident_location_id),
'XXX',incident_location_id
),
incident_last_modified_date = sysdate ,
object_version_number = object_version_number + 1,
last_update_program_code = 'PARTY_MERGE',
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE rowid = l_rowid_tbl(i);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
l_audit_vals_rec. last_update_program_code := 'PARTY_MERGE' ;
l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code (i);
l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
l_audit_vals_rec.updated_entity_id := l_incident_id(i);
p_last_update_date => SYSDATE,
p_creation_date => SYSDATE,
p_comments => NULL,
x_audit_id => l_audit_id);
select rowid
from cs_incidents_audit_b
where p_from_fk_id in (bill_to_contact_id, ship_to_contact_id, old_bill_to_contact_id,
old_ship_to_contact_id)
for update nowait;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
update cs_incidents_audit_b
set bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id, p_to_fk_id,
bill_to_contact_id ),
old_bill_to_contact_id= decode(old_bill_to_contact_id, p_from_fk_id, p_to_fk_id,
old_bill_to_contact_id ),
ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id, p_to_fk_id,
ship_to_contact_id ),
old_ship_to_contact_id= decode(old_ship_to_contact_id, p_from_fk_id, p_to_fk_id,
old_ship_to_contact_id ),
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where rowid = l_rowid_tbl(i);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select rowid
from cs_incidents_audit_b
where p_from_fk_id in (site_id, old_site_id)
for update nowait;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
update cs_incidents_audit_b
set site_id = decode(site_id, p_from_fk_id, p_to_fk_id,
site_id),
old_site_id = decode(old_site_id, p_from_fk_id, p_to_fk_id,
old_site_id),
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
where rowid = l_rowid_tbl(i);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select 1
from cs_hz_sr_contact_points
where sr_contact_point_id = p_from_id
and party_id = p_from_fk_id
and contact_type <> 'EMPLOYEE'
for update nowait;
select merge_reason_code
into l_merge_reason_code
from hz_merge_batch
where batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
select sr_contact_point_id INTO v_merged_to_id
from cs_hz_sr_contact_points
where party_id = p_to_fk_id
and ( contact_point_id, incident_id ) = ( select contact_point_id, incident_id
from cs_hz_sr_contact_points
where sr_contact_point_id = p_from_id )
and sr_contact_point_id <> p_from_id
and contact_type <> 'EMPLOYEE'
and rownum = 1;
--Hence, just update the record with the new party id.
----dbms_output.put_line('hi 1' || v_merged_to_id);
UPDATE CS_HZ_SR_CONTACT_POINTS
SET party_id = p_to_fk_id,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE sr_contact_point_id = p_from_id
AND party_id = p_from_fk_id
RETURNING incident_id , sr_contact_point_id BULK COLLECT
INTO l_incident_id , l_sr_contact_point_id ;
p_updated_entity_code => 'SR_CONTACT_POINT',
p_updated_entity_id => l_sr_contact_point_id(i) ,
p_entity_update_date => sysdate ,
p_entity_activity_code => 'U',
p_update_program_code => 'PARTY_MERGE',
x_audit_id => l_audit_id ,
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ) ;
--HEnce, mark the the other found record as duplicate and update the current
--one which needs to updated with the new party id. WHile marking the record
--as DUPLICATE, check if it is the PRIMARY CONTACT. IF so, make the current one
--which is going to be updated with new party id, as PRIMARY CONTACT
--Found a record whcih results in duplicate, hence delete it
DELETE FROM
CS_HZ_SR_CONTACT_POINTS
WHERE sr_contact_point_id = v_merged_to_id
RETURNING primary_flag INTO l_primary_flag ;
UPDATE CS_HZ_SR_CONTACT_POINTS
SET party_id = p_to_fk_id,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE sr_contact_point_id = p_from_id
AND party_id = p_from_fk_id
RETURNING incident_id , sr_contact_point_id BULK COLLECT
INTO l_incident_id , l_sr_contact_point_id ;
p_updated_entity_code => 'SR_CONTACT_POINT',
p_updated_entity_id => l_sr_contact_point_id(i) ,
p_entity_update_date => sysdate ,
p_entity_activity_code => 'U',
p_update_program_code => 'PARTY_MERGE',
x_audit_id => l_audit_id ,
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ) ;
UPDATE CS_HZ_SR_CONTACT_POINTS
SET party_id = p_to_fk_id,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
primary_flag = 'Y'
WHERE sr_contact_point_id = p_from_id
AND party_id = p_from_fk_id
RETURNING incident_id , sr_contact_point_id BULK COLLECT
INTO l_incident_id , l_sr_contact_point_id ;
p_updated_entity_code => 'SR_CONTACT_POINT',
p_updated_entity_id => l_sr_contact_point_id(i) ,
p_entity_update_date => sysdate ,
p_entity_activity_code => 'U',
p_update_program_code => 'PARTY_MERGE',
x_audit_id => l_audit_id ,
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ) ;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
SELECT 1
FROM cs_hz_sr_contact_points
WHERE sr_contact_point_id = p_from_id
AND contact_point_id = p_from_fk_id
AND contact_type <> 'EMPLOYEE'
FOR UPDATE NOWAIT;
SELECT merge_reason_code
INTO l_merge_reason_code
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
select sr_contact_point_id INTO v_merged_to_id
from cs_hz_sr_contact_points
where contact_point_id = p_to_fk_id
and ( party_id, incident_id ) = ( select party_id, incident_id
from cs_hz_sr_contact_points
where sr_contact_point_id = p_from_id )
and sr_contact_point_id <> p_from_id
and contact_type <> 'EMPLOYEE'
and rownum = 1;
UPDATE CS_HZ_SR_CONTACT_POINTS
SET contact_point_id = p_to_fk_id,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE sr_contact_point_id = p_from_id
AND contact_point_id = p_from_fk_id
RETURNING incident_id , sr_contact_point_id BULK COLLECT
INTO l_incident_id , l_sr_contact_point_id ;
p_updated_entity_code => 'SR_CONTACT_POINT',
p_updated_entity_id => l_sr_contact_point_id(i) ,
p_entity_update_date => sysdate ,
p_entity_activity_code => 'U',
p_update_program_code => 'PARTY_MERGE',
x_audit_id => l_audit_id ,
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ) ;
--HEnce, mark the the other found record as duplicate and update the current
--one which needs to updated with the new party id. WHile marking the record
--as DUPLICATE, check if it is the PRIMARY CONTACT. IF so, make the current one
--which is going to be updated with new party id, as PRIMARY CONTACT
--Found a record which would result in duplicate. hence delete it
---dbms_output.put_line('merged id is not null');
DELETE FROM CS_HZ_SR_CONTACT_POINTS
WHERE SR_CONTACT_POINT_ID = v_merged_to_id
RETURNING primary_flag INTO l_primary_flag ;
UPDATE CS_HZ_SR_CONTACT_POINTS
SET contact_point_id = p_to_fk_id,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE sr_contact_point_id = p_from_id
AND contact_point_id = p_from_fk_id
RETURNING incident_id , sr_contact_point_id BULK COLLECT
INTO l_incident_id , l_sr_contact_point_id ;
p_updated_entity_code => 'SR_CONTACT_POINT',
p_updated_entity_id => l_sr_contact_point_id(i) ,
p_entity_update_date => sysdate ,
p_entity_activity_code => 'U',
p_update_program_code => 'PARTY_MERGE',
x_audit_id => l_audit_id ,
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ) ;
--PRIMARY_FLAG of the deleted record was Y, hence make this record as primary
---dbms_output.put_line('primary flag is Y');
UPDATE CS_HZ_SR_CONTACT_POINTS
SET contact_point_id = p_to_fk_id,
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
primary_flag = 'Y'
WHERE sr_contact_point_id = p_from_id
AND contact_point_id = p_from_fk_id
RETURNING incident_id , sr_contact_point_id BULK COLLECT
INTO l_incident_id , l_sr_contact_point_id ;
p_updated_entity_code => 'SR_CONTACT_POINT',
p_updated_entity_id => l_sr_contact_point_id(i) ,
p_entity_update_date => sysdate ,
p_entity_activity_code => 'U',
p_update_program_code => 'PARTY_MERGE',
x_audit_id => l_audit_id ,
x_return_status => l_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ) ;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
SELECT rowid,
incident_id ,
customer_phone_id ,
customer_email_id ,
last_update_program_code
FROM cs_incidents_all_b
WHERE p_from_fk_id IN ( customer_phone_id, customer_email_id )
FOR update nowait;
l_last_update_program_code VARCHAR2_30_TBL;
SELECT merge_reason_code
INTO l_merge_reason_code
FROM hz_merge_batch
WHERE batch_id = p_batch_id;
-- obtain lock on records to be updated.
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
l_last_update_program_code
LIMIT 1000 ;
UPDATE cs_incidents_all_b
SET customer_phone_id = decode(customer_phone_id, p_from_fk_id, p_to_fk_id,
customer_phone_id ),
customer_email_id = decode(customer_email_id, p_from_fk_id, p_to_fk_id,
customer_email_id ),
incident_last_modified_date = sysdate ,
last_update_program_code = 'PARTY_MERGE',
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID
WHERE rowid = l_rowid_tbl(i);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
l_audit_vals_rec. last_update_program_code := 'PARTY_MERGE' ;
l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code (i);
l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
l_audit_vals_rec.updated_entity_id := l_incident_id(i);
p_last_update_date => SYSDATE,
p_creation_date => SYSDATE,
p_comments => NULL,
x_audit_id => l_audit_id);