1 PACKAGE BODY csp_cmerge_bb5 AS
2 /* $Header: cscm105b.pls 115.1 99/07/16 08:47:53 porting s $ */
3
4 /* ----------------- Local Procedures ----------------------------------------*/
5
6 PROCEDURE CS_MERGE_CUSTOMER_ID ( req_id IN NUMBER,
7 set_number IN NUMBER,
8 process_mode IN VARCHAR2 );
9
10 /* ------------------- End Local Procedures ------------------------------------ */
11
12 /* This procedure handles the merge process for the CS_MASS_NOTIFICATION_TXNS_TEMP.
13 It calls 1 seperate procedures to accomplish the task. The tasts are listed
14 below:
15 1) Update the customer_id
16
17 ---------------------------------------------------------------------------- */
18
19 PROCEDURE MERGE ( req_id IN NUMBER,
20 set_number IN NUMBER,
21 process_mode IN VARCHAR2 ) IS
22
23 /* used to store a free form text to be written to the log file */
24
25 message_text char(80);
26
27 /* number of rows updated */
28
29 number_of_rows NUMBER;
30
31 BEGIN
32
33 /* Put the header in the report to identify the block to be run */
34
35 arp_message.set_line('CP_CMERGE_BB5.MERGE()+');
36
37 IF ( process_mode = 'LOCK' ) Then
38 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
39 arp_message.set_token('TABLE_NAME', 'CS_MASS_NOTIFICATION_TXNS_TEMP',FALSE );
40 message_text := 'The locking is done in block CSP_CMERGE_BB5';
41 arp_message.set_line(message_text);
42 ELSE
43 arp_message.set_name('AR', 'AR_UPDATING_TABLE');
44 arp_message.set_token('TABLE_NAME', 'CS_MASS_NOTIFICATION_TXNS_TEMP',FALSE );
45 message_text := 'The merge is done in block CSP_CMERGE_BB5';
46 arp_message.set_line(message_text);
47 END IF;
48
49 /* merge the CS_MASS_NOTIFICATION_TXNS_TEMP table update the customer_id */
50
51 message_text := '***-- Procedure CS_MERGE_CUSTOMER_ID --**';
52 arp_message.set_line(message_text);
53
54 CS_MERGE_CUSTOMER_ID( req_id, set_number, process_mode );
55
56 message_text := '***-- End CS_MERGE_CUSTOMER_ID --**';
57 arp_message.set_line(message_text);
58
59 /* Report that the process for CS_MASS_NOTIFICATION_TXNS_TEMP is complete */
60
61 IF ( process_mode = 'LOCK' ) Then
62 message_text := '** LOCKING completed for table CS_MASS_NOTIFICATION_TXNS_TEMP **';
63 arp_message.set_line(message_text);
64 ELSE
65 message_text := '** MERGE completed for table CS_MASS_NOTIFICATION_TXNS_TEMP **';
66 arp_message.set_line(message_text);
67 END IF;
68
69 arp_message.set_line('CP_CMERGE_BB5.MERGE()-');
70
71 END MERGE;
72
73 /* -----------------------------------------------------------------------------*/
74
75 /* This process updates the customer_id of the CS_MASS_NOTIFICATION_TXNS_TEMP table */
76
77 PROCEDURE CS_MERGE_CUSTOMER_ID (req_id IN NUMBER,
78 set_number IN NUMBER,
79 process_mode IN VARCHAR2 ) IS
80
81 /* used to store a free form text to be written to the log file */
82
83 message_text char(80);
84
85 /* number of rows updated */
86
87 number_of_rows NUMBER;
88
89 Cursor LOCK_CUSTOMER_ID ( req_id NUMBER, set_number NUMBER ) IS
90 SELECT yt.customer_id
91 FROM CS_MASS_NOTIFICATION_TXNS_TEMP yt, RA_CUSTOMER_MERGES RACM
92 WHERE
93 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
94 FROM RA_CUSTOMER_MERGES RACM
95 WHERE RACM.PROCESS_FLAG = 'N'
96 AND RACM.REQUEST_ID = req_id
97 AND RACM.SET_NUMBER = set_number )
98 FOR UPDATE NOWAIT;
99
100 BEGIN
101 IF ( process_mode = 'LOCK' ) Then
102
103 message_text := 'LOCKING the customer_id ( 1/1 )';
104 arp_message.set_line(message_text);
105
106 OPEN LOCK_CUSTOMER_ID ( req_id, set_number );
107 CLOSE LOCK_CUSTOMER_ID;
108
109 message_text := 'Done locking customer_id';
110 arp_message.set_line(message_text);
111
112 ELSE
113
114 message_text := 'Starting to update the customer_id ( 1/1 )';
115 arp_message.set_line(message_text);
116
117 UPDATE CS_MASS_NOTIFICATION_TXNS_TEMP yt
118 SET
119 yt.customer_id = ( SELECT DISTINCT RACM.CUSTOMER_ID
120 FROM RA_CUSTOMER_MERGES RACM
121 WHERE yt.customer_id = DUPLICATE_ID
122 AND RACM.PROCESS_FLAG = 'N'
123 AND RACM.REQUEST_ID = req_id
124 AND RACM.SET_NUMBER = set_number )
125 WHERE
126 yt.customer_id IN ( SELECT RACM.DUPLICATE_ID
127 FROM RA_CUSTOMER_MERGES RACM
128 WHERE RACM.PROCESS_FLAG = 'N'
129 AND RACM.REQUEST_ID = req_id
130 AND RACM.SET_NUMBER = set_number );
131
132 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
133 number_of_rows := sql%rowcount;
134 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
135 message_text := 'Done with the update of customer_id';
136 arp_message.set_line(message_text);
137
138 END IF;
139
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142
143 message_text := 'Customer_id NOT found -- proceeding *** ';
144 arp_message.set_line(message_text);
145 arp_message.set_name( 'CS', 'CS_ROWS_UPDATED');
146 number_of_rows := sql%rowcount;
147 arp_message.set_token( 'NUM_ROWS',to_char( number_of_rows) );
148 message_text := 'Done with the update of customer_id';
149 arp_message.set_line(message_text);
150
151 WHEN OTHERS THEN
152
153 message_text := SUBSTR(SQLERRM,1,70);
154 arp_message.set_error('CS_MERGE_CUSTOMER_ID',
155 message_text);
156 raise;
157
158 END CS_MERGE_CUSTOMER_ID;
159
160 END CSP_CMERGE_BB5;
[Home] [Help]