[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
1) Update the system_ship_to_site_use_id
2) Update the system_install_site_use_id
3) Update the cp_ship_to_site_use_id
4) Update the cp_install_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;
/* merge the CS_TEMPLATES_INTERFACE table update system ship to site use id */
message_text := '***-- Procedure CS_MERGE_SYS_SHIP_SITE_ID --**';
/* merge the CS_TEMPLATES_INTERFACE table update system install site use id */
message_text := '***-- Procedure CS_MERGE_SYS_INSTALL_SITE_ID --**';
/* merge the CS_TEMPLATES_INTERFACE table update cp ship to site use id */
message_text := '***-- Procedure CS_MERGE_CP_SHIP_SITE_ID --**';
/* merge the CS_TEMPLATES_INTERFACE table update cp install site use id */
message_text := '***-- Procedure CS_MERGE_CP_INSTALL_SITE_ID --**';
/* merge the CS_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE */
PROCEDURE CS_MERGE_SYS_SHIP_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 system_ship_to_site_use_id
FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
WHERE
yt.system_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;
UPDATE CS_TEMPLATES_INTERFACE yt
SET
yt.system_ship_to_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.system_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_number )
WHERE
yt.system_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 system_ship_to_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of system_ship_to_site_use_id';
/* Update the ship use site id of CS_TEMPLATES_INTERFACE */
PROCEDURE CS_MERGE_SYS_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 system_install_site_use_id
FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
WHERE
yt.system_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 system_install_site_use_id ( 2/5 )';
UPDATE CS_TEMPLATES_INTERFACE yt
SET
yt.system_install_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.system_install_site_use_id
= DUPLICATE_SITE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number )
WHERE
yt.system_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 system_install_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of system_install_site_use_id';
/* Update the ship use site id of CS_TEMPLATES_INTERFACE */
PROCEDURE CS_MERGE_CP_SHIP_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 cp_ship_to_site_use_id
FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
WHERE
yt.cp_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 cp_ship_to_site_use_id ( 3/5 )';
UPDATE CS_TEMPLATES_INTERFACE yt
SET
yt.cp_ship_to_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.cp_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 )
WHERE
yt.cp_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 cp_ship_to_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of cp_ship_to_site_use_id';
/* Update the ship use site id of CS_TEMPLATES_INTERFACE */
PROCEDURE CS_MERGE_CP_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 cp_install_site_use_id
FROM CS_TEMPLATES_INTERFACE yt, RA_CUSTOMER_MERGES RACM
WHERE
yt.cp_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 cp_install_site_use_id ( 4/5 )';
UPDATE CS_TEMPLATES_INTERFACE yt
SET
yt.cp_install_site_use_id =
( SELECT DISTINCT RACM.CUSTOMER_SITE_ID
FROM RA_CUSTOMER_MERGES RACM
WHERE yt.cp_install_site_use_id
= DUPLICATE_SITE_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number )
WHERE
yt.cp_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 cp_install_site_use_id';
arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
message_text := 'Done with the update of cp_install_site_use_id';
/* This process updates the customer_id of the CS_TEMPLATES_INTERFACE 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_TEMPLATES_INTERFACE 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 ( 5/5 )';
UPDATE CS_TEMPLATES_INTERFACE 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 )
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.templates_interface_id
FROM CS_TEMPLATES_INTERFACE 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.system_ship_to_site_use_id <> racm.customer_site_id AND
cs.system_ship_to_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_TEMPLATES_INTERFACE CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.system_ship_to_site_use_id = racm.customer_site_id or
cs.system_ship_to_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.system_install_site_use_id <> racm.customer_site_id AND
cs.system_install_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_TEMPLATES_INTERFACE CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.system_install_site_use_id = racm.customer_site_id or
cs.system_install_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.cp_install_site_use_id <> racm.customer_site_id AND
cs.cp_install_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_TEMPLATES_INTERFACE CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.cp_install_site_use_id = racm.customer_site_id or
cs.cp_install_site_use_id IS NULL ))) AND
(( cs.customer_id = RACM.CUSTOMER_ID AND
cs.cp_ship_to_site_use_id <> racm.customer_site_id AND
cs.cp_ship_to_site_use_id IS NOT NULL ) AND
( cs.customer_id NOT IN ( select racm.customer_id
from CS_TEMPLATES_INTERFACE CS,
RA_CUSTOMER_MERGES RACM
where cs.customer_id = RACM.CUSTOMER_ID AND
cs.cp_ship_to_site_use_id = racm.customer_site_id or
cs.cp_ship_to_site_use_id IS NULL ))) );