[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select HEADER_ID
from oe_order_headers_all
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_order_headers_all
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_order_headers_all
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_order_headers_all
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE oe_order_headers_all a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_headers_all a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_headers_all a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE oe_order_headers_all a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select HEADER_ID
from oe_order_header_history
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_order_header_history
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_order_header_history
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_order_header_history
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
UPDATE oe_order_header_history a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_order_header_history a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_order_header_history a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_order_header_history a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
/* -- Interface tables need not be updated
Procedure OE_Merge_Headers_IFACE (Req_Id IN NUMBER,
Set_Num IN NUMBER,
Process_Mode IN VARCHAR2)
IS
CURSOR c1 is
select HEADER_ID
from oe_headers_iface_all
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_headers_iface_all
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_headers_iface_all
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select HEADER_ID
from oe_headers_iface_all
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
UPDATE oe_headers_iface_all a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_headers_iface_all a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_headers_iface_all a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_headers_iface_all a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
Interface tables need not be updated */
/*------------------------------------------------*/
/*--- PRIVATE PROCEDURE OE_Merge_Header_ACKS ---*/
/*------------------------------------------------*/
Procedure OE_Merge_Header_ACKS (Req_Id IN NUMBER,
Set_Num IN NUMBER,
Process_Mode IN VARCHAR2)
IS
CURSOR c1 is
select HEADER_ID
from OE_HEADER_ACKS
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select HEADER_ID
from OE_HEADER_ACKS
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select HEADER_ID
from OE_HEADER_ACKS
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select HEADER_ID
from OE_HEADER_ACKS
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_HEADER_ACKS a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE OE_HEADER_ACKS a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE OE_HEADER_ACKS a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_HEADER_ACKS a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select line_id
from oe_order_lines_all
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_all
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_all
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_all
where intmed_ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_all
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE oe_order_lines_all a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_lines_all a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_lines_all a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_lines_all a
set intmed_ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.intmed_ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where intmed_ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE oe_order_lines_all a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select line_id
from oe_order_lines_history
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_history
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_history
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_history
where intmed_ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_order_lines_history
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE oe_order_lines_history a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_lines_history a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_lines_history a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_order_lines_history a
set intmed_ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.intmed_ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where intmed_ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE oe_order_lines_history a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* Interface tables need not be updated
Procedure OE_Merge_Lines_IFACE (Req_Id IN NUMBER,
Set_Num IN NUMBER,
Process_Mode IN VARCHAR2)
IS
CURSOR c1 is
select line_id
from oe_lines_iface_all
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_lines_iface_all
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_lines_iface_all
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select line_id
from oe_lines_iface_all
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
UPDATE oe_lines_iface_all a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_lines_iface_all a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_lines_iface_all a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
UPDATE oe_lines_iface_all a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
Interface tables need not be updated */
/*-------------------------------------------------*/
/*--- PRIVATE PROCEDURE OE_Merge_Line_ACKS ---*/
/*-------------------------------------------------*/
Procedure OE_Merge_Line_ACKS (Req_Id IN NUMBER,
Set_Num IN NUMBER,
Process_Mode IN VARCHAR2)
IS
CURSOR c1 is
select line_id
from oe_line_acks
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select line_id
from oe_line_acks
where invoice_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select line_id
from oe_line_acks
where deliver_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select line_id
from oe_line_acks
where intmed_ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
select line_id
from oe_line_acks
where sold_to_org_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE oe_line_acks a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_line_acks a
set invoice_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.invoice_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where invoice_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_line_acks a
set deliver_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.deliver_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where deliver_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
UPDATE oe_line_acks a
set intmed_ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.intmed_ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where intmed_ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE oe_line_acks a
set sold_to_org_id = (select distinct m.customer_id
from ra_customer_merges m
where a.sold_to_org_id = m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
request_id = req_id,
program_application_id =fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = sysdate
where sold_to_org_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select RULE_ELEMENT_ID
from oe_attachment_rule_elements
where attribute_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SHIP_TO_ORG_ID'
for update nowait;
select RULE_ELEMENT_ID
from oe_attachment_rule_elements
where attribute_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INVOICE_TO_ORG_ID'
for update nowait;
select RULE_ELEMENT_ID
from oe_attachment_rule_elements
where attribute_value in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SOLD_TO_ORG_ID'
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
set (attribute_value) = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.attribute_value =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where attribute_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
set (attribute_value) = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.attribute_value =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where attribute_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INVOICE_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
set (attribute_value) = (select distinct to_char(m.customer_id)
from ra_customer_merges m
where a.attribute_value =
to_char(m.duplicate_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where attribute_value in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SOLD_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select CONDITION_ELEMENT_ID
from oe_def_condn_elems
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SHIP_TO_ORG_ID'
for update nowait;
select CONDITION_ELEMENT_ID
from oe_def_condn_elems
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INVOICE_TO_ORG_ID'
for update nowait;
select CONDITION_ELEMENT_ID
from oe_def_condn_elems
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INTMED_SHIP_TO_ORG_ID'
for update nowait;
select CONDITION_ELEMENT_ID
from oe_def_condn_elems
where value_string in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SOLD_TO_ORG_ID'
for update nowait;
select ATTR_DEF_RULE_ID
from oe_def_attr_def_rules
where src_constant_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SHIP_TO_ORG_ID'
for update nowait;
select ATTR_DEF_RULE_ID
from oe_def_attr_def_rules
where src_constant_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INVOICE_TO_ORG_ID'
for update nowait;
select ATTR_DEF_RULE_ID
from oe_def_attr_def_rules
where src_constant_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INTMED_SHIP_TO_ORG_ID'
for update nowait;
select ATTR_DEF_RULE_ID
from oe_def_attr_def_rules
where src_constant_value in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SOLD_TO_ORG_ID'
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DEF_CONDN_ELEMS a
set value_string = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DEF_CONDN_ELEMS a
set value_string = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INVOICE_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DEF_CONDN_ELEMS a
set value_string = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_DEF_CONDN_ELEMS a
set value_string = (select distinct to_char(m.customer_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SOLD_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DEF_ATTR_DEF_RULES a
set src_constant_value = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.src_constant_value =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where src_constant_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DEF_ATTR_DEF_RULES a
set src_constant_value = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.src_constant_value =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where src_constant_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INVOICE_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DEF_ATTR_DEF_RULES a
set src_constant_value = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.src_constant_value =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where src_constant_value in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_DEF_ATTR_DEF_RULES a
set src_constant_value = (select distinct to_char(m.customer_id)
from ra_customer_merges m
where a.src_constant_value =
to_char(m.duplicate_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where src_constant_value in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and attribute_code = 'SOLD_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select VALIDATION_TMPLT_ID
from oe_pc_vtmplt_cols
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'SHIP_TO_ORG_ID'
for update nowait;
select VALIDATION_TMPLT_ID
from oe_pc_vtmplt_cols
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'INVOICE_TO_ORG_ID'
for update nowait;
select VALIDATION_TMPLT_ID
from oe_pc_vtmplt_cols
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'INTMED_SHIP_TO_ORG_ID'
for update nowait;
select VALIDATION_TMPLT_ID
from oe_pc_vtmplt_cols
where value_string in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'SOLD_TO_ORG_ID'
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_PC_VTMPLT_COLS a
set value_string = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_PC_VTMPLT_COLS a
set value_string = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'INVOICE_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_PC_VTMPLT_COLS a
set value_string = (select distinct to_char(m.customer_site_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_site_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_site_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'INTMED_SHIP_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_PC_VTMPLT_COLS a
set value_string = (select distinct to_char(m.customer_id)
from ra_customer_merges m
where a.value_string =
to_char(m.duplicate_id)
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where value_string in (select to_char(m.duplicate_id)
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and column_name = 'SOLD_TO_ORG_ID';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select hold_source_id
from oe_hold_sources
where hold_entity_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and hold_entity_code = 'S'
for update nowait;
select hold_source_id
from oe_hold_sources
where hold_entity_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and hold_entity_code = 'B'
for update nowait;
select hold_source_id
from oe_hold_sources
where hold_entity_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and hold_entity_code = 'C'
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_HOLD_SOURCES a
set (hold_entity_id) = (select distinct m.customer_site_id
from ra_customer_merges m
where a.hold_entity_id =
m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where hold_entity_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and hold_entity_code = 'S';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_HOLD_SOURCES a
set (hold_entity_id) = (select distinct m.customer_site_id
from ra_customer_merges m
where a.hold_entity_id =
m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where hold_entity_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and hold_entity_code = 'B';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_HOLD_SOURCES a
set hold_entity_id = (select distinct m.customer_id
from ra_customer_merges m
where a.hold_entity_id =
m.duplicate_id
and m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where hold_entity_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
and hold_entity_code = 'C';
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select DROP_SHIP_SOURCE_ID
from oe_drop_ship_sources
where LINE_LOCATION_ID in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_DROP_SHIP_SOURCES a
set line_location_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.line_location_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where line_location_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select CUST_ITEM_SETTING_ID
from oe_cust_item_settings
where site_use_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
select CUST_ITEM_SETTING_ID
from oe_cust_item_settings
where customer_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE OE_CUST_ITEM_SETTINGS a
set site_use_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.site_use_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where site_use_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
/* customer level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
UPDATE OE_CUST_ITEM_SETTINGS a
set customer_id = (select distinct m.customer_id
from ra_customer_merges m
where a.customer_id = m.duplicate_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where customer_id in (select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
select Set_Id
from oe_sets
where ship_to_org_id in
(select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num)
for update nowait;
/* site level update */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
UPDATE oe_sets a
set ship_to_org_id = (select distinct m.customer_site_id
from ra_customer_merges m
where a.ship_to_org_id = m.duplicate_site_id
and m.request_id = req_id
and m.process_flag = 'Y'
and m.set_number = set_num),
update_date = sysdate,
updated_by = fnd_global.user_id,
update_login = fnd_global.login_id
where ship_to_org_id in (select m.duplicate_site_id
from ra_customer_merges m
where m.process_flag = 'Y'
and m.request_id = req_id
and m.set_number = set_num);
oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;