[Home] [Help]
PACKAGE BODY: APPS.ARP_CMERGE_ARCOL
Source
1 PACKAGE BODY ARP_CMERGE_ARCOL as
2 /* $Header: ARPLCOLB.pls 120.6 2005/10/30 04:24:24 appldev ship $ */
3
4 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
5 g_count NUMBER := 0;
6
7
8 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
9
10
11 PROCEDURE ar_cct (
12 req_id NUMBER,
13 set_num NUMBER,
14 process_mode VARCHAR2) IS
15
16 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
17 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
18 INDEX BY BINARY_INTEGER;
19 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
20
21 TYPE cust_call_topic_id_LIST_TYPE IS TABLE OF
22 AR_CUSTOMER_CALL_TOPICS.customer_call_topic_id%TYPE
23 INDEX BY BINARY_INTEGER;
24 PRIMARY_KEY_ID_LIST cust_call_topic_id_LIST_TYPE;
25
26 TYPE customer_id_LIST_TYPE IS TABLE OF
27 AR_CUSTOMER_CALL_TOPICS.customer_id%TYPE
28 INDEX BY BINARY_INTEGER;
29 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
30 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
31
32 TYPE site_use_id_LIST_TYPE IS TABLE OF
33 AR_CUSTOMER_CALL_TOPICS.site_use_id%TYPE
34 INDEX BY BINARY_INTEGER;
35 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
36 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
37
38 l_profile_val VARCHAR2(30);
39 CURSOR merged_records IS
40 SELECT distinct CUSTOMER_MERGE_HEADER_ID
41 ,customer_call_topic_id
42 ,yt.customer_id
43 ,site_use_id
44 FROM AR_CUSTOMER_CALL_TOPICS yt, ra_customer_merges m
45 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
46 nvl(yt.site_use_id, m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
47 AND m.process_flag = 'N'
48 AND m.request_id = req_id
49 AND m.set_number = set_num;
50 l_last_fetch BOOLEAN := FALSE;
51 l_count NUMBER;
52 BEGIN
53 IF process_mode='LOCK' THEN
54 NULL;
55 ELSE
56 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
57 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CUSTOMER_CALL_TOPICS',FALSE);
58 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
59 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
60
61 open merged_records;
62 LOOP
63 FETCH merged_records BULK COLLECT INTO
64 MERGE_HEADER_ID_LIST
65 , PRIMARY_KEY_ID_LIST
66 , NUM_COL1_ORIG_LIST
67 , NUM_COL2_ORIG_LIST
68 LIMIT ARP_CMERGE.max_array_size;/*Additional change for 2447449*/
69 IF merged_records%NOTFOUND THEN
70 l_last_fetch := TRUE;
71 END IF;
72 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
73 exit;
74 END IF;
75 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
76 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
77 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
78 END LOOP;
79 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
80 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
81 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
82 MERGE_LOG_ID,
83 TABLE_NAME,
84 MERGE_HEADER_ID,
85 PRIMARY_KEY_ID,
86 NUM_COL1_ORIG,
87 NUM_COL1_NEW,
88 NUM_COL2_ORIG,
89 NUM_COL2_NEW,
90 ACTION_FLAG,
91 REQUEST_ID,
92 CREATED_BY,
93 CREATION_DATE,
94 LAST_UPDATE_LOGIN,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY
97 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
98 'AR_CUSTOMER_CALL_TOPICS',
99 MERGE_HEADER_ID_LIST(I),
100 PRIMARY_KEY_ID_LIST(I),
101 NUM_COL1_ORIG_LIST(I),
102 NUM_COL1_NEW_LIST(I),
103 NUM_COL2_ORIG_LIST(I),
104 NUM_COL2_NEW_LIST(I),
105 'U',
106 req_id,
107 hz_utility_pub.CREATED_BY,
108 hz_utility_pub.CREATION_DATE,
109 hz_utility_pub.LAST_UPDATE_LOGIN,
110 hz_utility_pub.LAST_UPDATE_DATE,
111 hz_utility_pub.LAST_UPDATED_BY
112 );
113
114 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
115 UPDATE AR_CUSTOMER_CALL_TOPICS yt SET
116 customer_id=NUM_COL1_NEW_LIST(I)
117 ,site_use_id=NUM_COL2_NEW_LIST(I)
118 , LAST_UPDATE_DATE=SYSDATE
119 , last_updated_by=arp_standard.profile.user_id
120 , last_update_login=arp_standard.profile.last_update_login
121 WHERE customer_call_topic_id=PRIMARY_KEY_ID_LIST(I) ;
122
123 l_count := l_count + SQL%ROWCOUNT;
124 IF l_last_fetch THEN
125 EXIT;
126 END IF;
127 END LOOP;
128
129 arp_message.set_name('AR','AR_ROWS_UPDATED');
130 arp_message.set_token('NUM_ROWS',to_char(l_count));
131 END IF;
132 EXCEPTION
133 WHEN OTHERS THEN
134 arp_message.set_line( 'ar_cct');
135 RAISE;
136 END ar_cct;
140 records in table ar_customer_calls_all
137
138 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
139 /* bug3541921 : Added following procedure ar_cc to update
141 */
142 PROCEDURE ar_ccalls (
143 req_id NUMBER,
144 set_num NUMBER,
145 process_mode VARCHAR2) IS
146
147 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
148 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
149 INDEX BY BINARY_INTEGER;
150 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
151
152 TYPE customer_call_id_LIST_TYPE IS TABLE OF
153 AR_CUSTOMER_CALLS.customer_call_id%TYPE
154 INDEX BY BINARY_INTEGER;
155 PRIMARY_KEY_ID_LIST customer_call_id_LIST_TYPE;
156
157 TYPE customer_id_LIST_TYPE IS TABLE OF
158 AR_CUSTOMER_CALLS.customer_id%TYPE
159 INDEX BY BINARY_INTEGER;
160 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
161 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
162
163 TYPE site_use_id_LIST_TYPE IS TABLE OF
164 AR_CUSTOMER_CALLS.site_use_id%TYPE
165 INDEX BY BINARY_INTEGER;
166 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
167 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
168
169 l_profile_val VARCHAR2(30);
170 CURSOR merged_records IS
171 SELECT distinct CUSTOMER_MERGE_HEADER_ID
172 ,customer_call_id
173 ,yt.customer_id
174 ,site_use_id
175 FROM AR_CUSTOMER_CALLS yt, ra_customer_merges m
176 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
177 nvl(yt.site_use_id, m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
178 AND m.process_flag = 'N'
179 AND m.request_id = req_id
180 AND m.set_number = set_num;
181 l_last_fetch BOOLEAN := FALSE;
182 l_count NUMBER;
183 BEGIN
184 IF process_mode='LOCK' THEN
185 NULL;
186 ELSE
187 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
188 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_CUSTOMER_CALLS',FALSE);
189 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
190 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
191
192 open merged_records;
193 LOOP
194 FETCH merged_records BULK COLLECT INTO
195 MERGE_HEADER_ID_LIST
196 , PRIMARY_KEY_ID_LIST
197 , NUM_COL1_ORIG_LIST
198 , NUM_COL2_ORIG_LIST
199 LIMIT ARP_CMERGE.max_array_size;/*Additional change for 2447449*/
200 IF merged_records%NOTFOUND THEN
201 l_last_fetch := TRUE;
202 END IF;
203 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
204 exit;
205 END IF;
206 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
207 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
208 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
209 END LOOP;
210 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
211 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
212 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
213 MERGE_LOG_ID,
214 TABLE_NAME,
215 MERGE_HEADER_ID,
216 PRIMARY_KEY_ID,
217 NUM_COL1_ORIG,
218 NUM_COL1_NEW,
219 NUM_COL2_ORIG,
220 NUM_COL2_NEW,
221 ACTION_FLAG,
222 REQUEST_ID,
223 CREATED_BY,
224 CREATION_DATE,
225 LAST_UPDATE_LOGIN,
226 LAST_UPDATE_DATE,
227 LAST_UPDATED_BY
228 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
229 'AR_CUSTOMER_CALLS',
230 MERGE_HEADER_ID_LIST(I),
231 PRIMARY_KEY_ID_LIST(I),
232 NUM_COL1_ORIG_LIST(I),
233 NUM_COL1_NEW_LIST(I),
234 NUM_COL2_ORIG_LIST(I),
235 NUM_COL2_NEW_LIST(I),
236 'U',
237 req_id,
238 hz_utility_pub.CREATED_BY,
239 hz_utility_pub.CREATION_DATE,
240 hz_utility_pub.LAST_UPDATE_LOGIN,
241 hz_utility_pub.LAST_UPDATE_DATE,
242 hz_utility_pub.LAST_UPDATED_BY
243 );
244
245 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
246 UPDATE AR_CUSTOMER_CALLS yt SET
247 customer_id=NUM_COL1_NEW_LIST(I)
248 ,site_use_id=NUM_COL2_NEW_LIST(I)
249 , LAST_UPDATE_DATE=SYSDATE
250 , last_updated_by=arp_standard.profile.user_id
251 , last_update_login=arp_standard.profile.last_update_login
252 WHERE customer_call_id=PRIMARY_KEY_ID_LIST(I) ;
253
254 l_count := l_count + SQL%ROWCOUNT;
255 IF l_last_fetch THEN
256 EXIT;
257 END IF;
258 END LOOP;
259
260 arp_message.set_name('AR','AR_ROWS_UPDATED');
261 arp_message.set_token('NUM_ROWS',to_char(l_count));
262 END IF;
263 EXCEPTION
264 WHEN OTHERS THEN
265 arp_message.set_line( 'ar_ccalls');
266 RAISE;
267 END ar_ccalls;
268
269 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
270
271 /* bug3541921 : Added following procedure ar_sh to update
272 records in table ar_statements_history
273 */
274 PROCEDURE ar_sh (
275 req_id NUMBER,
276 set_num NUMBER,
277 process_mode VARCHAR2) IS
278
279 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
280 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
281 INDEX BY BINARY_INTEGER;
282 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
283
284 TYPE line_cluster_id_LIST_TYPE IS TABLE OF
285 AR_STATEMENTS_HISTORY.line_cluster_id%TYPE
286 INDEX BY BINARY_INTEGER;
287 PRIMARY_KEY_ID_LIST line_cluster_id_LIST_TYPE;
288
289 TYPE customer_id_LIST_TYPE IS TABLE OF
290 AR_STATEMENTS_HISTORY.customer_id%TYPE
291 INDEX BY BINARY_INTEGER;
292 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
293 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
294
295 TYPE site_use_id_LIST_TYPE IS TABLE OF
296 AR_STATEMENTS_HISTORY.site_use_id%TYPE
297 INDEX BY BINARY_INTEGER;
298 NUM_COL2_ORIG_LIST site_use_id_LIST_TYPE;
299 NUM_COL2_NEW_LIST site_use_id_LIST_TYPE;
300
301
302 l_profile_val VARCHAR2(30);
303 CURSOR merged_records IS
304 SELECT distinct CUSTOMER_MERGE_HEADER_ID
305 ,line_cluster_id
306 ,yt.customer_id
307 ,site_use_id
308 FROM AR_STATEMENTS_HISTORY yt, ra_customer_merges m
309 WHERE ( yt.customer_id = m.DUPLICATE_ID AND
310 yt.site_use_id = m.DUPLICATE_SITE_ID)
311 AND m.process_flag = 'N'
312 AND m.request_id = req_id
313 AND m.set_number = set_num;
314 l_last_fetch BOOLEAN := FALSE;
315 l_count NUMBER;
316 BEGIN
317 IF process_mode='LOCK' THEN
318 NULL;
319 ELSE
320 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
321 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','AR_STATEMENTS_HISTORY',FALSE);
322 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
323 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
324
325 open merged_records;
326 LOOP
327 FETCH merged_records BULK COLLECT INTO
328 MERGE_HEADER_ID_LIST
329 , PRIMARY_KEY_ID_LIST
330 , NUM_COL1_ORIG_LIST
331 , NUM_COL2_ORIG_LIST
332 LIMIT ARP_CMERGE.max_array_size;/*Additional change for 2447449*/
333 IF merged_records%NOTFOUND THEN
334 l_last_fetch := TRUE;
335 END IF;
336 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
337 exit;
338 END IF;
339 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
340 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
341 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
342 END LOOP;
343 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
344 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
345 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
346 MERGE_LOG_ID,
347 TABLE_NAME,
348 MERGE_HEADER_ID,
349 PRIMARY_KEY_ID,
350 NUM_COL1_ORIG,
351 NUM_COL1_NEW,
352 NUM_COL2_ORIG,
353 NUM_COL2_NEW,
354 ACTION_FLAG,
355 REQUEST_ID,
356 CREATED_BY,
357 CREATION_DATE,
358 LAST_UPDATE_LOGIN,
359 LAST_UPDATE_DATE,
360 LAST_UPDATED_BY
361 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
362 'AR_STATEMENTS_HISTORY',
363 MERGE_HEADER_ID_LIST(I),
364 PRIMARY_KEY_ID_LIST(I),
365 NUM_COL1_ORIG_LIST(I),
366 NUM_COL1_NEW_LIST(I),
367 NUM_COL2_ORIG_LIST(I),
368 NUM_COL2_NEW_LIST(I),
369 'U',
370 req_id,
371 hz_utility_pub.CREATED_BY,
372 hz_utility_pub.CREATION_DATE,
373 hz_utility_pub.LAST_UPDATE_LOGIN,
374 hz_utility_pub.LAST_UPDATE_DATE,
375 hz_utility_pub.LAST_UPDATED_BY
376 );
377
378 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
379 UPDATE AR_STATEMENTS_HISTORY yt SET
380 customer_id=NUM_COL1_NEW_LIST(I)
381 ,site_use_id=NUM_COL2_NEW_LIST(I)
382 ,bill_to_location = ( select hs.location
383 from hz_cust_site_uses hs
384 where hs.site_use_id = NUM_COL2_NEW_LIST(I))
385 , LAST_UPDATE_DATE=SYSDATE
386 , last_updated_by=arp_standard.profile.user_id
387 , last_update_login=arp_standard.profile.last_update_login
388 WHERE line_cluster_id=PRIMARY_KEY_ID_LIST(I)
389 AND customer_id = NUM_COL1_ORIG_LIST(I) ;
390
391 l_count := l_count + SQL%ROWCOUNT;
392 IF l_last_fetch THEN
393 EXIT;
394 END IF;
395 END LOOP;
396
397 arp_message.set_name('AR','AR_ROWS_UPDATED');
398 arp_message.set_token('NUM_ROWS',to_char(l_count));
399 END IF;
400 EXCEPTION
401 WHEN OTHERS THEN
402 arp_message.set_line( 'ar_sh');
403 RAISE;
404 END ar_sh;
405
406 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
407
408 PROCEDURE merge (req_id NUMBER, set_num NUMBER, process_mode VARCHAR2) is
409 BEGIN
410
411 arp_message.set_line( 'ARP_CMERGE_ARCOL.MERGE()+' );
412
413 ar_cct( req_id, set_num, process_mode );
414 /* bug354192 : added call to following procedures */
415
416 ar_ccalls( req_id, set_num, process_mode );
417 ar_sh( req_id, set_num, process_mode );
418
419 arp_message.set_line( 'ARP_CMERGE_ARCOL.MERGE()-' );
420
421 END merge;
422
423 end ARP_CMERGE_ARCOL;