[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 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;
/* merge the CS_SYSTEMS table update bill_to_site_use_id */
message_text := '***-- Procedure CS_MERGE_BILL_TO_SITE_ID --**';
/* merge the CS_SYSTEMS table update install_site_use_id */
message_text := '***-- Procedure CS_MERGE_INSTALL_SITE_ID --**';
/* merge the CS_SYSTEMS table update ship_to_site_use_id */
message_text := '***-- Procedure CS_MERGE_SHIP_TO_SITE_ID --**';
/* merge the CS_SYSTEMS table update the customer_id */
message_text := '***-- Procedure CS_MERGE_CUSTOMER_ID --**';
check to make sure all data has been updated. If not report it to the
log file. */
CS_CHECK_MERGE_DATA ( req_id, set_number, process_mode );
/* Update the ship use site id of CS_SYSTEMS */
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);
/* number of rows updated */
number_of_rows NUMBER;
SELECT bill_to_site_use_id
FROM CS_SYSTEMS yt, RA_CUSTOMER_MERGES RACM
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 )
AND yt.customer_id <> RACM.DUPLICATE_ID
FOR UPDATE NOWAIT;
UPDATE CS_SYSTEMS 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';
/* Update the ship use site id of CS_SYSTEMS */
PROCEDURE CS_MERGE_INSTALL_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);
/* number of rows updated */
number_of_rows NUMBER;
SELECT install_site_use_id
FROM CS_SYSTEMS yt, RA_CUSTOMER_MERGES RACM
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 )
AND yt.customer_id <> RACM.DUPLICATE_ID
FOR UPDATE NOWAIT;
'Starting to update the install_site_use_id ( 2/4 )';
UPDATE CS_SYSTEMS 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';
/* Update the ship to site id of CS_SYSTEMS */
PROCEDURE CS_MERGE_SHIP_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);
/* number of rows updated */
number_of_rows NUMBER;
SELECT ship_to_site_use_id
FROM CS_SYSTEMS yt, RA_CUSTOMER_MERGES RACM
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 )
AND yt.customer_id <> RACM.DUPLICATE_ID
FOR UPDATE NOWAIT;
message_text := 'Starting to update the Ship_use_site_id ( 3/4 )';
UPDATE CS_SYSTEMS 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';
/* This process updates the customer_id of the CS_SYSTEMS table */
PROCEDURE CS_MERGE_CUSTOMER_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);
/* number of rows updated */
number_of_rows NUMBER;
SELECT yt.customer_id
FROM CS_SYSTEMS yt, RA_CUSTOMER_MERGES RACM
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 ( 4/4 )';
UPDATE CS_SYSTEMS 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';
/* number of rows updated */
number_of_rows NUMBER;
SELECT
DISTINCT
cs.serial_number
FROM CS_SYSTEMS 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_SYSTEMS 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_SYSTEMS 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_SYSTEMS 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 ))) );