[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_PROC_NAME CONSTANT VARCHAR2(30) := 'UPDATE_CS_DATA';
PROCEDURE UPDATE_CS_DATA
( p_batch_id IN NUMBER,
p_request_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
-- Declare all the nested tables those will be used in the SR and SR charges transactions merge.
l_rowid_tbl ROWID_TBL;
l_last_update_program_code_tbl VARCHAR2_30_TBL;
SELECT /*+ PARALLEL(i) */
i.rowid,
i.incident_id ,
i.customer_id ,
pc.from_entity_id from_party_id ,
pc.to_entity_id to_party_id ,
i.bill_to_party_id,
pbp.from_entity_id from_bill_to_party_id ,
pbp.to_entity_id to_bill_to_party_id,
i.ship_to_party_id,
psp.from_entity_id from_ship_to_party_id ,
psp.to_entity_id to_ship_to_party_id,
i.bill_to_contact_id ,
pbc.from_entity_id from_bill_to_contact_id ,
pbc.to_entity_id to_bill_to_contact_id ,
i.ship_to_contact_id ,
psc.from_entity_id from_ship_to_contact_id ,
psc.to_entity_id to_ship_to_contact_id ,
i.bill_to_site_id,
pbs.from_entity_id from_bill_to_site_id,
pbs.to_entity_id to_bill_to_site_id,
i.ship_to_site_id,
pss.from_entity_id from_ship_to_site_id,
pss.to_entity_id to_ship_to_site_id,
i.site_id ,
ps.from_entity_id from_site_id,
ps.to_entity_id to_site_id,
i.customer_site_id,
pcs.from_entity_id from_customer_site_id,
pcs.to_entity_id to_customer_site_id,
i.install_site_id,
pis.from_entity_id from_install_site_id,
pis.to_entity_id to_install_site_id,
i.install_site_use_id,
pisu.from_entity_id from_install_site_use_id,
pisu.to_entity_id to_install_site_use_id,
i.incident_location_id,
i.incident_location_type,
pils.from_entity_id from_incident_location_id,
pils.to_entity_id to_incident_location_id,
i.ship_to_site_use_id,
psu.from_entity_id from_ship_to_site_use_id,
psu.to_entity_id to_ship_to_site_use_id,
i.bill_to_site_use_id,
pbu.from_entity_id from_bill_to_site_use_id,
pbu.to_entity_id to_bill_to_site_use_id,
i.customer_phone_id,
pch.from_entity_id from_phone_id,
pch.to_entity_id to_phone_id ,
i.customer_email_id,
pce.from_entity_id from_email_id,
pce.to_entity_id to_email_id ,
i.last_update_program_code
FROM cs_incidents_all_B i,
hz_merge_party_log pc,
hz_merge_party_log pbp,
hz_merge_party_log psp,
hz_merge_party_log pbc,
hz_merge_party_log psc,
hz_merge_party_log pbs,
hz_merge_party_log pss,
hz_merge_party_log ps,
hz_merge_party_log pcs,
hz_merge_party_log pis,
hz_merge_party_log pisu,
hz_merge_party_log pils,
hz_merge_party_log psu,
hz_merge_party_log pbu,
hz_merge_party_log pch,
hz_merge_party_log pce
WHERE pc.request_id(+) = p_request_id
AND pc.merge_dict_id(+) = 1 -- Entity = HZ Party
AND pc.from_entity_id(+) = i.customer_id
AND pc.operation_type(+) = 'Merge'
AND pbp.merge_dict_id(+) = 1 -- Entity = HZ Party
AND pbp.request_id(+) = p_request_id
AND pbp.from_entity_id(+) = i.bill_to_party_id
AND pbp.operation_type(+) = 'Merge'
AND psp.merge_dict_id(+) = 1 -- Entity = HZ Party
AND psp.request_id(+) = p_request_id
AND psp.from_entity_id(+) = i.ship_to_party_id
AND psp.operation_type(+) = 'Merge'
AND pbc.merge_dict_id(+) = 1 -- Entity = HZ Party
AND pbc.request_id(+) = p_request_id
AND pbc.from_entity_id(+) = i.bill_to_contact_id
AND pbc.operation_type(+) = 'Merge'
AND psc.merge_dict_id(+) = 1 -- Entity = HZ Party
AND psc.request_id(+) = p_request_id
AND psc.from_entity_id(+) = i.ship_to_contact_id
AND psc.operation_type(+) = 'Merge'
AND pbs.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND pbs.request_id(+) = p_request_id
AND pbs.from_entity_id(+) = i.bill_to_site_id
AND pbs.operation_type(+) = 'Merge'
AND pss.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND pss.request_id(+) = p_request_id
AND pss.from_entity_id(+) = i.ship_to_site_id
AND pss.operation_type(+) = 'Merge'
AND ps.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND ps.request_id(+) = p_request_id
AND ps.from_entity_id(+) = i.site_id
AND ps.operation_type(+) = 'Merge'
AND pcs.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND pcs.request_id(+) = p_request_id
AND pcs.from_entity_id(+) = i.customer_site_id
AND pcs.operation_type(+) = 'Merge'
AND pis.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND pis.request_id(+) = p_request_id
AND pis.from_entity_id(+) = i.install_site_id
AND pis.operation_type(+) = 'Merge'
AND pisu.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND pisu.request_id(+) = p_request_id
AND pisu.from_entity_id(+) = i.install_site_use_id
AND pisu.operation_type(+) = 'Merge'
AND pils.merge_dict_id(+) = 3 -- Entity = HZ Party site
AND pils.request_id(+) = p_request_id
AND pils.from_entity_id(+) = i.incident_location_id
AND pils.operation_type(+) = 'Merge'
AND psu.merge_dict_id(+) = 19 -- Entity = HZ Party Site Use
AND psu.request_id(+) = p_request_id
AND psu.from_entity_id(+) = i.ship_to_site_use_id
AND psu.operation_type(+) = 'Merge'
AND pbu.merge_dict_id(+) = 19 -- Entity = HZ Party Site Use
AND pbu.request_id(+) = p_request_id
AND pbu.from_entity_id(+) = i.bill_to_site_use_id
AND pbu.operation_type(+) = 'Merge'
AND pch.merge_dict_id(+) = 4 -- Entity = HZ Contact Point
AND pch.request_id(+) = p_request_id
AND pch.from_entity_id(+) = i.customer_phone_id
AND pch.operation_type(+) = 'Merge'
AND pce.merge_dict_id(+) = 4 -- Entity = HZ Contact Point
AND pce.request_id(+) = p_request_id
AND pce.from_entity_id(+) = i.customer_email_id
AND pce.operation_type(+) = 'Merge'
AND (pc.from_entity_id is not null OR
pbp.from_entity_id is not null OR
psp.from_entity_id is not null OR
pbc.from_entity_id is not null OR
psc.from_entity_id is not null OR
pbs.from_entity_id is not null OR
pss.from_entity_id is not null OR
pcs.from_entity_id is not null OR
ps.from_entity_id is not null OR
pis.from_entity_id is not null OR
pisu.from_entity_id is not null OR
pils.from_entity_id is not null OR
psu.from_entity_id is not null OR
pbu.from_entity_id is not null OR
pch.from_entity_id is not null OR
pce.from_entity_id is not null
) ;
SELECT /*+ PARALLEL(c) */
c.rowid,
c.estimate_detail_id,
c.bill_to_party_id,
pbp.from_entity_id from_bill_to_party_id ,
pbp.to_entity_id to_bill_to_party_id,
c.ship_to_party_id,
psp.from_entity_id from_ship_to_party_id ,
psp.to_entity_id to_ship_to_party_id,
c.bill_to_contact_id ,
pbc.from_entity_id from_bill_to_contact_id ,
pbc.to_entity_id to_bill_to_contact_id ,
c.ship_to_contact_id ,
psc.from_entity_id from_ship_to_contact_id ,
psc.to_entity_id to_ship_to_contact_id ,
c.invoice_to_org_id,
pbs.from_entity_id from_bill_to_site_id,
pbs.to_entity_id to_bill_to_site_id,
c.ship_to_org_id,
pss.from_entity_id from_ship_to_site_id,
pss.to_entity_id to_ship_to_site_id
FROM cs_estimate_details c,
hz_merge_party_log pbp,
hz_merge_party_log psp,
hz_merge_party_log pbc,
hz_merge_party_log psc,
hz_merge_party_log pbs,
hz_merge_party_log pss
WHERE pbp.merge_dict_id(+) = 1 -- Entity = HZ Party
AND pbp.request_id(+) = p_request_id
AND pbp.from_entity_id(+) = c.bill_to_party_id
AND pbp.operation_type(+) = 'Merge'
AND psp.merge_dict_id(+) = 1 -- Entity = HZ Party
AND psp.request_id(+) = p_request_id
AND psp.from_entity_id(+) = c.ship_to_party_id
AND psp.operation_type(+) = 'Merge'
AND pbc.merge_dict_id(+) = 1 -- Entity = HZ Party
AND pbc.request_id(+) = p_request_id
AND pbc.from_entity_id(+) = c.bill_to_contact_id
AND pbc.operation_type(+) = 'Merge'
AND psc.merge_dict_id(+) = 1 -- Entity = HZ Party
AND psc.request_id(+) = p_request_id
AND psc.from_entity_id(+) = c.ship_to_contact_id
AND psc.operation_type(+) = 'Merge'
AND pbs.merge_dict_id(+) = 3 -- Entity = HZ Party Site
AND pbs.request_id(+) = p_request_id
AND pbs.from_entity_id(+) = c.invoice_to_org_id
--AND pbs.operation_type(+) = 'Merge' --bug 7310180
AND pss.merge_dict_id(+) = 3 -- Entity = HZ Party Site
AND pss.request_id(+) = p_request_id
AND pss.from_entity_id(+) = c.ship_to_org_id
--AND pss.operation_type(+) = 'Merge' --bug 7310180
AND (pbp.from_entity_id is not null OR
psp.from_entity_id is not null OR
pbc.from_entity_id is not null OR
psc.from_entity_id is not null OR
pbs.from_entity_id is not null OR
pss.from_entity_id is not null );
SELECT batch_party_id ,
from_party_id ,
to_party_id
FROM hz_merge_parties
WHERE merge_reason_code <> 'SAME_PARTY_MERGE'
AND batch_id = p_batch_id;
SELECT p.from_entity_id ,
p.to_entity_id ,
p.batch_party_id,
cc.sr_contact_point_id
FROM hz_merge_party_log p,
cs_hz_sr_contact_points cc
WHERE p.merge_dict_id = 4
AND p.operation_type = 'Merge'
AND p.request_id = p_request_id
AND p.from_entity_id = cc.contact_point_id
AND cc.contact_type <> 'EMPLOYEE';
SELECT p.batch_party_id,
p.from_party_id ,
p.to_party_id ,
cc.sr_contact_point_id
FROM hz_merge_parties p,
cs_hz_sr_contact_points cc
WHERE p.merge_type <> 'SAME_PARTY_MERGE'
AND p.batch_id = p_batch_id
AND p.from_party_id = cc.party_id
AND cc.contact_type <> 'EMPLOYEE';
SELECT ex.rowid,
ex.extension_id,
ex.party_id ,
p.from_party_id ,
p.to_party_id
FROM hz_merge_parties p,
cs_sr_contacts_ext ex
WHERE ex.party_id = p.from_party_id
AND p.batch_id = p_batch_id
AND p.merge_type <> 'SAME_PARTY_MERGE';
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('In CS_PartyMerge_PKG.Update_CS_Data');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
arp_message.set_line('CS_PARTYMERGE_PKG.UPDATE_CS_DATA()+');
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Merging Service Request transactions');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_last_update_program_code_tbl
LIMIT 1000 ;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('No of Service requests to be updated : '||C_Get_ServiceRequests%ROWCOUNT);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
UPDATE cs_incidents_all_b i
SET i.customer_id = DECODE(i.customer_id,l_from_party_id_tbl(i),
l_to_party_id_tbl(i) , i.customer_id),
i.bill_to_party_id = DECODE(i.bill_to_party_id,l_from_bill_to_party_id_tbl(i),
l_to_bill_to_party_id_tbl(i),i.bill_to_party_id),
i.ship_to_party_id = DECODE(i.ship_to_party_id , l_from_ship_to_party_id_tbl(i) ,
l_to_ship_to_party_id_tbl(i) , i.ship_to_party_id),
i.bill_to_contact_id = DECODE(i.bill_to_contact_id , l_from_bill_to_contact_id_tbl(i) ,
l_to_bill_to_contact_id_tbl(i) , i.bill_to_contact_id),
i.ship_to_contact_id = DECODE(i.ship_to_contact_id , l_from_ship_to_contact_id_tbl(i) ,
l_to_ship_to_contact_id_tbl(i) , i.ship_to_contact_id),
i.bill_to_site_id = DECODE(i.bill_to_site_id , l_from_bill_to_site_id_tbl(i),
l_to_bill_to_site_id_tbl(i) , i.bill_to_site_id),
i.ship_to_site_id = DECODE(i.ship_to_site_id , l_from_ship_to_site_id_tbl(i),
l_to_ship_to_site_id_tbl(i) , i.ship_to_site_id),
i.site_id = DECODE(i.site_id , l_from_site_id_tbl(i) ,
l_to_site_id_tbl(i) , i.site_id),
i.customer_site_id = DECODE(i.customer_site_id , l_from_customer_site_id_tbl(i) ,
l_to_customer_site_id_tbl(i) , i.install_site_id),
i.install_site_id = DECODE(i.install_site_id , l_from_install_site_id_tbl(i) ,
l_to_install_site_id_tbl(i) , i.install_site_use_id ),
i.install_site_use_id = DECODE(i.install_site_use_id , l_from_install_site_use_id_tbl(i) ,
l_to_install_site_use_id_tbl(i) , i.install_site_use_id),
i.incident_location_id = DECODE(i.incident_location_type , 'HZ_PARTY_SITE',
DECODE (i.incident_location_id , l_from_inc_loc_id_tbl(i) ,
l_to_incident_location_id_tbl(i) , i.incident_location_id),
i.incident_location_id),
i.ship_to_site_use_id = DECODE(i.ship_to_site_use_id , l_from_ship_to_site_use_id_tbl(i) ,
l_to_ship_to_site_use_id_tbl(i) , i.ship_to_site_use_id),
i.bill_to_site_use_id = DECODE(i.bill_to_site_use_id , l_from_bill_to_site_use_id_tbl(i) ,
l_to_bill_to_site_use_id_tbl(i) , i.bill_to_site_use_id),
i.customer_phone_id = DECODE(i.customer_phone_id , l_from_phone_id_tbl(i) ,
l_to_phone_id_tbl(i) , i.customer_phone_id),
i.customer_email_id = DECODE(i.customer_email_id , l_from_email_id_tbl(i) ,
l_to_email_id_tbl(i) , i.customer_email_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);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('No. of service requests updated : '||l_count);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
dbg_msg := ('Creating SR Audit record for each updated service request');
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Calling CS_PartyMerge_PKG.Update_CS_Data API ');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code_tbl(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_tbl(i);
p_last_update_date => SYSDATE,
p_creation_date => SYSDATE,
p_comments => NULL,
x_audit_id => l_audit_id);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Created SR audit records for the SRs updated in a batch');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Updating Service Request Transactions completed');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_rowid_tbl.DELETE;
l_incident_id_tbl.DELETE;
l_estimate_detail_id_tbl.DELETE;
l_customer_id_tbl.DELETE;
l_from_party_id_tbl.DELETE;
l_to_party_id_tbl.DELETE;
l_bill_to_party_id_tbl.DELETE;
l_from_bill_to_party_id_tbl.DELETE;
l_to_bill_to_party_id_tbl.DELETE;
l_ship_to_party_id_tbl.DELETE;
l_from_ship_to_party_id_tbl.DELETE;
l_to_ship_to_party_id_tbl.DELETE;
l_bill_to_contact_id_tbl.DELETE;
l_from_bill_to_contact_id_tbl.DELETE;
l_to_bill_to_contact_id_tbl.DELETE;
l_ship_to_contact_id_tbl.DELETE;
l_from_ship_to_contact_id_tbl.DELETE;
l_to_ship_to_contact_id_tbl.DELETE;
l_bill_to_site_id_tbl.DELETE;
l_from_bill_to_site_id_tbl.DELETE;
l_to_bill_to_site_id_tbl.DELETE;
l_ship_to_site_id_tbl.DELETE;
l_from_ship_to_site_id_tbl.DELETE;
l_to_ship_to_site_id_tbl.DELETE;
l_site_id_tbl.DELETE;
l_from_site_id_tbl.DELETE;
l_to_site_id_tbl.DELETE;
l_customer_site_id_tbl.DELETE;
l_from_customer_site_id_tbl.DELETE;
l_to_customer_site_id_tbl.DELETE;
l_install_site_id_tbl.DELETE;
l_from_install_site_id_tbl.DELETE;
l_to_install_site_id_tbl.DELETE;
l_install_site_use_id_tbl.DELETE;
l_from_install_site_use_id_tbl.DELETE;
l_to_install_site_use_id_tbl.DELETE;
l_incident_location_id_tbl.DELETE;
l_incident_location_type_tbl.DELETE;
l_from_inc_loc_id_tbl.DELETE;
l_to_incident_location_id_tbl.DELETE;
l_ship_to_site_use_id_tbl.DELETE;
l_from_ship_to_site_use_id_tbl.DELETE;
l_to_ship_to_site_use_id_tbl.DELETE;
l_bill_to_site_use_id_tbl.DELETE;
l_from_bill_to_site_use_id_tbl.DELETE;
l_to_bill_to_site_use_id_tbl.DELETE;
l_customer_phone_id_tbl.DELETE;
l_from_phone_id_tbl.DELETE;
l_to_phone_id_tbl.DELETE;
l_customer_email_id_tbl.DELETE;
l_from_email_id_tbl.DELETE;
l_to_email_id_tbl.DELETE;
l_last_update_program_code_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory completed');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Updating SR Charges transactions');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('No of SR Charge transactions to be updated in a batch : '||C_Get_Estimate_details%ROWCOUNT);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
UPDATE cs_estimate_details c
SET c.bill_to_party_id = DECODE(c.bill_to_party_id , l_from_bill_to_party_id_tbl(i) ,
l_to_bill_to_party_id_tbl(i) , c.bill_to_party_id),
c.ship_to_party_id = DECODE(c.ship_to_party_id , l_from_ship_to_party_id_tbl(i) ,
l_to_ship_to_party_id_tbl(i) , c.ship_to_party_id),
c.bill_to_contact_id = DECODE(c.bill_to_contact_id , l_from_bill_to_contact_id_tbl(i) ,
l_to_bill_to_contact_id_tbl(i) , c.bill_to_contact_id),
c.ship_to_contact_id = DECODE(c.ship_to_contact_id , l_from_ship_to_contact_id_tbl(i) ,
l_to_ship_to_contact_id_tbl(i) , c.ship_to_contact_id),
c.invoice_to_org_id = DECODE(c.invoice_to_org_id , l_from_bill_to_site_id_tbl(i),
l_to_bill_to_site_id_tbl(i) , c.invoice_to_org_id),
c.ship_to_org_id = DECODE(c.ship_to_org_id , l_from_ship_to_site_id_tbl(i),
l_to_ship_to_site_id_tbl(i) , c.ship_to_org_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);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('No of SR Charge transactions updated : '||l_count);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('SR Charge transactions updated, Releasing memory');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_rowid_tbl.DELETE;
l_estimate_detail_id_tbl.DELETE;
l_bill_to_party_id_tbl.DELETE;
l_from_bill_to_party_id_tbl.DELETE;
l_to_bill_to_party_id_tbl.DELETE;
l_ship_to_party_id_tbl.DELETE;
l_from_ship_to_party_id_tbl.DELETE;
l_to_ship_to_party_id_tbl.DELETE;
l_bill_to_contact_id_tbl.DELETE;
l_from_bill_to_contact_id_tbl.DELETE;
l_to_bill_to_contact_id_tbl.DELETE;
l_ship_to_contact_id_tbl.DELETE;
l_from_ship_to_contact_id_tbl.DELETE;
l_to_ship_to_contact_id_tbl.DELETE;
l_from_bill_to_site_id_tbl.DELETE;
l_to_bill_to_site_id_tbl.DELETE;
l_from_ship_to_site_id_tbl.DELETE;
l_to_ship_to_site_id_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory done');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Updating SR Contact point transactions ');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('No. of SR Contact point transactions to be updated in a batch and impacted due to party merge : '||c_Get_contact_point_txns2%ROWCOUNT);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('SR contact point ,impacted due to party merge, transactions updated, Releasing memory');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_from_party_id_tbl.DELETE;
l_to_party_id_tbl.DELETE;
l_batch_party_id_tbl.DELETE;
l_sr_contact_point_id_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory done');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Updating SR contact point transactions.');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('SR contact point transactions to be updated : '||c_Get_contact_point_txns1%ROWCOUNT);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('SR contact point ,impacted due to contact point merge, transactions updated, Releasing memory');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_from_email_id_tbl.DELETE;
l_to_email_id_tbl.DELETE;
l_batch_party_id_tbl.DELETE;
l_sr_contact_point_id_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory done');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Calling SR Charges routine to update sub restriction setup. Parties merged : '||C_Get_merged_parties%ROWCOUNT);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('SR charged sub restrictions data impacted due to party merge, transactions updated, Releasing memory');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_from_party_id_tbl.DELETE;
l_to_party_id_tbl.DELETE;
l_batch_party_id_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory done');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_rowid_tbl.DELETE;
l_incident_id_tbl.DELETE;
l_estimate_detail_id_tbl.DELETE;
l_customer_id_tbl.DELETE;
l_from_party_id_tbl.DELETE;
l_to_party_id_tbl.DELETE;
l_bill_to_party_id_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Updating extensible attributes for the party role. Parties merged : '||c_get_party_ext_attr_rec%ROWCOUNT);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
UPDATE cs_sr_contacts_ext
SET party_id = DECODE(party_id,l_from_party_id_tbl(i),l_to_party_id_tbl(i),party_id)
WHERE rowid = l_rowid_tbl(i);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('No. of Party role ext attribute records updated : '||l_count);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Creating SR Audit record for each updated party role ext. attr. record');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Created SR audit records for the SRs updated in a batch');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('SR extensible attributes,associated with party role, data impacted due to party merge, transactions updated');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
l_rowid_tbl.DELETE;
l_extension_id_tbl.DELETE;
l_customer_id_tbl.DELETE;
l_from_party_id_tbl.DELETE;
l_to_party_id_tbl.DELETE;
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
dbg_msg := ('Releasing memory done');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
END UPDATE_CS_DATA;