[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1) Update the bill_to_site_use_id
2) Update the install_site_use_id
3) Update the ship_to_site_use_id
4) Update the order_bill_to_site_use_id
5) Update the order_ship_to_site_use_id
5) Update the customer_id
---------------------------------------------------------------------------- */
PROCEDURE MERGE ( req_id IN NUMBER,
set_number IN NUMBER,
process_mode IN VARCHAR2 ) IS
/* used to store a free form text to be written to the log file */
message_text char(80);
/* number of rows updated */
number_of_rows NUMBER;
/* Update the ship use site id of CS_CUSTOMER_PRODUCTS
PROCEDURE CS_MERGE_BILL_TO_SITE_ID ( req_id IN NUMBER,
set_number IN NUMBER,
process_mode IN VARCHAR2 ) IS
used to store a free form text to be written to the log file
message_text char(80);
SELECT bill_to_site_use_id
FROM CS_CUSTOMER_PRODUCTS yt
WHERE
yt.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_number )
FOR UPDATE NOWAIT;
UPDATE CS_CUSTOMER_PRODUCTS yt
SET
yt.bill_to_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE 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_number ),
yt.LAST_UPDATE_DATE = SYSDATE,
yt.LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
yt.LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
yt.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_number );
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of bill_to_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of bill_to_site_use_id';
SELECT install_site_use_id
FROM CS_CUSTOMER_PRODUCTS yt
WHERE
yt.install_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_number )
FOR UPDATE NOWAIT;
'Starting to update the install_site_use_id ( 2/6 )';
UPDATE CS_CUSTOMER_PRODUCTS yt
SET
yt.install_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.install_site_use_id
= DUPLICATE_SITE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number ),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
yt.install_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_number );
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of Install_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of Install_site_use_id';
SELECT ship_to_site_use_id
FROM CS_CUSTOMER_PRODUCTS yt
WHERE
yt.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_number )
FOR UPDATE NOWAIT;
message_text := 'Starting to update the ship_to_site_use_id ( 3/6 )';
UPDATE CS_CUSTOMER_PRODUCTS yt
SET
yt.ship_to_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.ship_to_site_use_id
= DUPLICATE_SITE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number ),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
yt.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_number );
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of ship_to_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of ship_to_site_use_id';
SELECT Order_bill_to_site_use_id
FROM CS_CUSTOMER_PRODUCTS yt
WHERE
yt.Order_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_number )
FOR UPDATE NOWAIT;
'Starting to update the Order_bill_to_site_use_id ( 4/6 )';
UPDATE CS_CUSTOMER_PRODUCTS yt
SET
yt.Order_bill_to_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.Order_bill_to_site_use_id
= DUPLICATE_SITE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number ),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
yt.Order_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_number );
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of Order_bill_to_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of Order_bill_to_site_use_id';
SELECT Order_ship_to_site_use_id
FROM CS_CUSTOMER_PRODUCTS yt
WHERE
yt.Order_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_number )
FOR UPDATE NOWAIT;
'Starting to update the Order_ship_to_site_use_id ( 5/6 )';
UPDATE CS_CUSTOMER_PRODUCTS yt
SET
yt.Order_ship_to_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.Order_ship_to_site_use_id
= DUPLICATE_SITE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number ),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
yt.Order_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_number );
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of Order_ship_to_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of Order_ship_to_site_use_id';
SELECT yt.customer_id
FROM CS_CUSTOMER_PRODUCTS yt
WHERE
yt.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_number )
FOR UPDATE NOWAIT;
message_text := 'Starting to update the customer_id ( 6/6 )';
UPDATE CS_CUSTOMER_PRODUCTS yt
SET
yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.customer_id = DUPLICATE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number ),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
yt.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_number );
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of customer_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of customer_id';
SELECT
DISTINCT
cs.current_serial_number
FROM CS_CUSTOMER_PRODUCTS CS,
RA_CUSTOMER_MERGES RACM
WHERE
RACM.PROCESS_FLAG = 'N' AND
RACM.REQUEST_ID = req_id AND
RACM.SET_NUMBER = set_number AND
((( cs.customer_id = RACM.CUSTOMER_ID AND
cs.bill_to_site_use_id != racm.customer_site_id AND
cs.bill_to_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_CUSTOMER_PRODUCTS CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.bill_to_site_use_id = racm.customer_site_id or
cs.bill_to_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.install_site_use_id != racm.customer_site_id AND
cs.install_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_CUSTOMER_PRODUCTS CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.install_site_use_id = racm.customer_site_id or
cs.install_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.ship_to_site_use_id != racm.customer_site_id AND
cs.ship_to_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_CUSTOMER_PRODUCTS CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.ship_to_site_use_id = racm.customer_site_id or
cs.ship_to_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.order_bill_to_site_use_id != racm.customer_site_id AND
cs.order_bill_to_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_CUSTOMER_PRODUCTS CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.order_bill_to_site_use_id = racm.customer_site_id or
cs.order_bill_to_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.order_ship_to_site_use_id != racm.customer_site_id AND
cs.order_ship_to_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_CUSTOMER_PRODUCTS CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.order_ship_to_site_use_id = racm.customer_site_id or
cs.order_ship_to_site_use_id IS NULL ))));