The following lines contain the word 'select', 'insert', 'update' or 'delete':
1) 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_MASS_SERVICE_TXNS_TEMP table update the customer_id */
message_text := '***-- Procedure CS_MERGE_CUSTOMER_ID --**';
/* This process updates the customer_id of the CS_MASS_SERVICE_TXNS_TEMP 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_MASS_SERVICE_TXNS_TEMP 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 ( 1/1 )';
UPDATE CS_MASS_SERVICE_TXNS_TEMP 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';