[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select request_id , merge_reason_code
into G_REQUEST_ID , G_MERGE_REASON_CODE
from hz_merge_batch
where batch_id = p_batch_id;
select 1
from csc_customers
where party_id = p_from_fk_id
for update nowait;
select override_flag from_override_flag,
overridden_critical_flag from_overridden_critical_flag,
rowid from_rowid,
cust_account_id from_cust_account_id,
overridden_critical_flag from_overridden_critical_flag,
override_reason_code from_override_reason_code
from csc_customers
where party_id = p_from_fk_id;
select override_flag to_override_flag,
overridden_critical_flag to_overridden_critical_flag
from csc_customers
where party_id = p_to_fk_id;
update csc_customers
set party_id = p_to_fk_id,
last_update_date = trunc(SYSDATE),
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
request_id = G_REQUEST_ID,
program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
program_update_date = trunc(SYSDATE)
where party_id = p_from_fk_id;
insert into csc_customers_audit_hist (
cust_hist_id, party_id, last_update_date,
last_updated_by, last_update_login, creation_date,
created_by, changed_date, changed_by,
sys_det_critical_flag, override_flag, overridden_critical_flag,
override_reason_code, request_id,
program_application_id,
program_id, program_update_date)
values (
csc_customers_audit_hist_s.nextval, p_to_fk_id, sysdate,
g_user_id, g_login_id, sysdate,
g_user_id, sysdate, g_user_id,
'N', l_from_override_flag, l_from_overridden_crit_flag,
l_from_override_reason_code , G_REQUEST_ID,
ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE );
-- Hence, we cannot update the party_id in this table. Instead, set the
-- party_status of the merge from party to 'M' (Merged); this record will
csc_customers_pkg.update_row (
x_rowid => l_from_rowid,
x_party_id => p_from_fk_id,
x_cust_account_id => l_from_cust_account_id,
x_last_update_date => SYSDATE,
x_last_updated_by => G_USER_ID,
x_last_update_login => G_LOGIN_ID,
x_creation_date => SYSDATE, -- value used for audit table purposes
x_created_by => G_USER_ID, -- value used for audit table purposes
x_sys_det_critical_flag => 'N', -- value not changed in update stmt. in pkg
x_override_flag => l_from_override_flag,
x_overridden_critical_flag => l_from_overridden_crit_flag,
x_override_reason_code => l_from_override_reason_code,
p_party_status => 'M',
p_request_id => G_REQUEST_ID,
p_program_application_id => ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
p_program_id => ARP_STANDARD.PROFILE.PROGRAM_ID,
p_program_update_date => SYSDATE );
select 1
from csc_cust_plans
where party_id = p_from_fk_id
for update nowait;
-- NOTE : If update performance is bad...then consider acheiving the same
-- logic thru the use of cursors..updating records individually.
-- Perform transfer if duplicate plans do not exist between the TO and FROM
-- parties
-- Bug# 2919377, if plan is transfered then one record will be inserted for
-- p_to_fk_id with status transferred, one record will be inserted for
-- p_from_fk_id with status merged.Update the Audit table first in case of plan transfer.
insert into csc_cust_plans_audit (
plan_audit_id, plan_id, party_id, cust_account_id,
plan_status_code, request_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, program_application_id,
program_id, program_update_date, object_version_number )
select
csc_cust_plans_audit_s.nextval, plan_id, p_to_fk_id, cust_account_id,
G_TRANSFER_PLAN, G_REQUEST_ID, SYSDATE,
G_USER_ID, SYSDATE, G_USER_ID,
G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
from csc_cust_plans
where party_id = p_from_fk_id
and cust_account_id is not null;
insert into csc_cust_plans_audit (
plan_audit_id, plan_id, party_id, cust_account_id,
plan_status_code, request_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, program_application_id,
program_id, program_update_date, object_version_number )
select
csc_cust_plans_audit_s.nextval, plan_id, p_to_fk_id, cust_account_id,
G_TRANSFER_PLAN, G_REQUEST_ID, SYSDATE,
G_USER_ID, SYSDATE, G_USER_ID,
G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
from csc_cust_plans
where party_id = p_from_fk_id and cust_account_id is null
and plan_id not in ( select plan_id
from csc_cust_plans
where party_id = p_to_fk_id );
insert into csc_cust_plans_audit (
plan_audit_id, plan_id, party_id, cust_account_id,
plan_status_code, request_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, program_application_id,
program_id, program_update_date, object_version_number )
select
csc_cust_plans_audit_s.nextval, plan_id, p_from_fk_id, cust_account_id,
G_MERGE_PLAN, G_REQUEST_ID, SYSDATE,
G_USER_ID, SYSDATE, G_USER_ID,
G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
from csc_cust_plans
where party_id = p_from_fk_id
and cust_account_id is not null;
insert into csc_cust_plans_audit (
plan_audit_id, plan_id, party_id, cust_account_id,
plan_status_code, request_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, program_application_id,
program_id, program_update_date, object_version_number )
select
csc_cust_plans_audit_s.nextval, plan_id, p_from_fk_id, cust_account_id,
G_MERGE_PLAN, G_REQUEST_ID, SYSDATE,
G_USER_ID, SYSDATE, G_USER_ID,
G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
from csc_cust_plans
where party_id = p_from_fk_id and cust_account_id is null
and plan_id not in ( select plan_id
from csc_cust_plans
where party_id = p_to_fk_id );
update csc_cust_plans
set party_id = p_to_fk_id,
plan_status_code = G_TRANSFER_PLAN,
request_id = G_REQUEST_ID,
-- end_date_active = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
program_update_date = SYSDATE,
object_version_number = object_version_number + 1
where party_id = p_from_fk_id
and cust_account_id is not null;
update csc_cust_plans
set party_id = p_to_fk_id,
plan_status_code = G_TRANSFER_PLAN,
request_id = G_REQUEST_ID,
-- end_date_active = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
program_update_date = SYSDATE,
object_version_number = object_version_number + 1
where party_id = p_from_fk_id and cust_account_id is null
and plan_id not in ( select plan_id
from csc_cust_plans
where party_id = p_to_fk_id );
g_mesg := 'Number of CSC_CUST_PLANS_AUDIT records inserted coresponding to the '
|| 'CSC_CUST_PLANS records transferred = ' || to_char(audit_count) ;
--arp_message.set_line('Number of CSC_CUST_PLANS_AUDIT records inserted ' ||
--'coresponding to the CSC_CUST_PLANS records ' ||
--'transferred = ' || to_char(sql%rowcount) );
update csc_cust_plans
set plan_status_code = G_MERGE_PLAN,
end_date_active = SYSDATE,
request_id = G_REQUEST_ID,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
program_update_date = SYSDATE,
object_version_number = object_version_number + 1
where party_id = p_from_fk_id
and plan_id in ( select plan_id
from csc_cust_plans
where party_id = p_to_fk_id );
insert into csc_cust_plans_audit (
plan_audit_id, plan_id, party_id,
plan_status_code, request_id, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login, program_application_id,
program_id, program_update_date, object_version_number )
select
csc_cust_plans_audit_s.nextval, plan_id, p_from_fk_id,
G_MERGE_PLAN, G_REQUEST_ID, SYSDATE,
G_USER_ID, SYSDATE, G_USER_ID,
G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
from csc_cust_plans
where party_id = p_from_fk_id
and plan_id in ( select plan_id
from csc_cust_plans
where party_id = p_to_fk_id );
g_mesg := 'Number of CSC_CUST_PLANS_AUDIT records inserted coresponding to the '
|| 'CSC_CUST_PLANS records merged = ' || to_char(sql%rowcount) ;
--arp_message.set_line('Number of CSC_CUST_PLANS_AUDIT records inserted ' ||
--'coresponding to the CSC_CUST_PLANS records ' ||
--'merged = ' || to_char(sql%rowcount) );
select 1
from csc_customized_plans
where party_id = p_from_fk_id
for update nowait;
-- NOTE : If update performance is bad...then consider acheiving the same
-- logic thru the use of cursors..updating records individually.
-- Perform transfer if duplicate plans do not exist between the TO and FROM
-- parties
update csc_customized_plans
set party_id = p_to_fk_id,
request_id = G_REQUEST_ID,
program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
program_update_date = SYSDATE,
plan_status_code = G_TRANSFER_PLAN
where party_id = p_from_fk_id
and plan_id not in ( select plan_id
from csc_customized_plans
where party_id = p_to_fk_id );
-- Delete records if duplicate customized plans exist between the TO
-- and FROM parties.
-- The delete operation is being performed temperorily until some additional
-- columns are added to the CSC_CUSTOMIZED_PLANS table to denote the merge
-- or transfer operation.
delete from csc_customized_plans
where party_id = p_from_fk_id
and plan_id in ( select plan_id
from csc_customized_plans
where party_id = p_to_fk_id );
g_mesg := 'Number of CSC_CUSTOMIZED_PLANS records deleted = ' || to_char(l_count);