[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL
FROM MTL_DEMAND
WHERE bill_to_site_use_id in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
FOR UPDATE NOWAIT;
SELECT NULL
FROM MTL_DEMAND
WHERE ship_to_site_use_id in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
FOR UPDATE NOWAIT;
SELECT NULL
FROM MTL_DEMAND
WHERE customer_id in (select racm.duplicate_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
and ship_to_site_use_id is NULL
and bill_to_site_use_id is NULL
FOR UPDATE NOWAIT;
arp_message.set_line('site level update : ship to and bill to site use id');
UPDATE MTL_DEMAND yt
set customer_id = (select distinct racm.customer_id
from ra_customer_merges racm
where yt.customer_id = racm.duplicate_id
and (yt.ship_to_site_use_id = racm.duplicate_site_id
or yt.bill_to_site_use_id = racm.duplicate_site_id)
and racm.process_flag= 'N'
and racm.request_id = req_id
and racm.set_number = set_num),
ship_to_site_use_id = (select distinct racm.customer_site_id
from ra_customer_merges racm
where yt.customer_id = racm.duplicate_id
and yt.ship_to_site_use_id = racm.duplicate_site_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num),
bill_to_site_use_id = (select distinct racm.customer_site_id
from ra_customer_merges racm
where yt.customer_id = racm.duplicate_id
and yt.bill_to_site_use_id = racm.duplicate_site_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num),
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id = arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = sysdate
where ship_to_site_use_id in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
and bill_to_site_use_id in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num);
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
/* site level update */
/* for bill to site use id */
arp_message.set_name('AR', 'AR_UPDATING_TABLE');
arp_message.set_line('site level update : bill to site use id');
UPDATE MTL_DEMAND yt
set (customer_id,
bill_to_site_use_id) = (select distinct racm.customer_id,
racm.customer_site_id
from ra_customer_merges racm
where yt.customer_id = racm.duplicate_id
and yt.bill_to_site_use_id = racm.duplicate_site_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num),
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = sysdate
where bill_to_site_use_id in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
and (ship_to_site_use_id is NULL
or ship_to_site_use_id not in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num));
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
/* site level update */
/* for ship to site use id */
arp_message.set_name('AR', 'AR_UPDATING_TABLE');
arp_message.set_line('site level update : ship to site use id');
UPDATE MTL_DEMAND yt
set (customer_id,
ship_to_site_use_id) = (select distinct racm.customer_id,
racm.customer_site_id
from ra_customer_merges racm
where yt.customer_id = racm.duplicate_id
and yt.ship_to_site_use_id = racm.duplicate_site_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num),
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = sysdate
where ship_to_site_use_id in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
and (bill_to_site_use_id is NULL
or bill_to_site_use_id not in (select racm.duplicate_site_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num));
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
/* customer level update */
arp_message.set_name('AR', 'AR_UPDATING_TABLE');
UPDATE MTL_DEMAND yt
set customer_id = (select distinct racm.customer_id
from ra_customer_merges racm
where yt.customer_id =
racm.duplicate_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num),
last_update_date = sysdate,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id =arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = sysdate
where customer_id in (select racm.duplicate_id
from ra_customer_merges racm
where racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num)
and ship_to_site_use_id is NULL
and bill_to_site_use_id is NULL;
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');