[Home] [Help]
PACKAGE BODY: APPS.IEX_PROCESS_ACCOUNT_WINNERS
Source
1 PACKAGE BODY IEX_PROCESS_ACCOUNT_WINNERS AS
2 /* $Header: iextpawb.pls 120.7.12010000.2 2008/08/06 09:03:21 schekuri ship $ */
3
4 /*-------------------------------------------------------------------------*
5 | PRIVATE CONSTANTS
6 *-------------------------------------------------------------------------*/
7 G_PKG_NAME CONSTANT VARCHAR2(30):='IEX_PROCESS_ACCOUNT_WINNERS';
8 G_FILE_NAME CONSTANT VARCHAR2(12):='iextpawb.pls';
9 deadlock_detected EXCEPTION;
10 PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
11
12
13 /*-------------------------------------------------------------------------*
14 | PRIVATE DATATYPES
15 *-------------------------------------------------------------------------*/
16
17 /*-------------------------------------------------------------------------*
18 | PRIVATE VARIABLES
19 *-------------------------------------------------------------------------*/
20
21 /*-------------------------------------------------------------------------*
22 | PRIVATE ROUTINES SPECIFICATION
23 *-------------------------------------------------------------------------*/
24
25
26 /*-------------------------------------------------------------------------*
27 | PUBLIC ROUTINES
28 *-------------------------------------------------------------------------*/
29
30 TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
31 TYPE address_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
32 TYPE faf_list is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
33 TYPE org_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
34 TYPE res_type_list is TABLE of VARCHAR2(60) INDEX BY BINARY_INTEGER;
35
36 TYPE salesforce_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
37 TYPE sales_group_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
38 TYPE person_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
39 TYPE src_list is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER;
40 TYPE access_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
41 TYPE terr_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
42 TYPE collector_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
43
44 TYPE party_site_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
45 TYPE site_use_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
46 TYPE cust_account_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
47
48 PROCEDURE CheckCollectors(
49 x_errbuf OUT NOCOPY VARCHAR2,
50 x_retcode OUT NOCOPY VARCHAR2,
51 p_worker_id IN NUMBER,
52 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
53
54
55 PROCEDURE AssignSiteUseCollectors(
56 x_errbuf OUT NOCOPY VARCHAR2,
57 x_retcode OUT NOCOPY VARCHAR2,
58 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
59
60 PROCEDURE AssignPartyCollectors(
61 x_errbuf OUT NOCOPY VARCHAR2,
62 x_retcode OUT NOCOPY VARCHAR2,
63 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
64
65 PROCEDURE AssignAccountCollectors(
66 x_errbuf OUT NOCOPY VARCHAR2,
67 x_retcode OUT NOCOPY VARCHAR2,
68 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
69
70 PROCEDURE CreatePartyProfiles(
71 x_errbuf OUT NOCOPY VARCHAR2,
72 x_retcode OUT NOCOPY VARCHAR2,
73 p_worker_id IN NUMBER,
74 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
75
76 PROCEDURE CreateSiteUseProfiles(
77 x_errbuf OUT NOCOPY VARCHAR2,
78 x_retcode OUT NOCOPY VARCHAR2,
79 p_worker_id IN NUMBER,
80 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
81
82
83 PROCEDURE Process_Account_Records(
84 x_errbuf OUT NOCOPY VARCHAR2,
85 x_retcode OUT NOCOPY VARCHAR2,
86 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
87 IS
88
89 l_limit_flag BOOLEAN := FALSE;
90 l_max_fetches NUMBER := 10000;
91 l_loop_count NUMBER := 0;
92 l_src_exists VARCHAR2(1);
93 l_AssignLevel VARCHAR2(20);
94 l_var NUMBER;
95 l_worker_id NUMBER;
96 -------------------------------------------------------------------------------
97
98
99 BEGIN
100 IEX_TERR_WINNERS_PUB.Print_Debug('*** iextpawb.pls::IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records() ***');
101
102 l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
103 FND_FILE.PUT_LINE(FND_FILE.LOG,'Process Accounts Program started');
104 FND_FILE.PUT_LINE(FND_FILE.LOG,'Operating Unit Set : ' || MO_GLOBAL.GET_CURRENT_ORG_ID);
105 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level, IEX: Territory Access Level (IEX_ACCESS_LEVEL) := ' ||l_Assignlevel); -- changed by gnramasa on 29/08/2006 for bug # 5487449
106
107 l_worker_id:=p_terr_globals.worker_id;
108 l_var :=p_terr_globals.bulk_size;
109 IEX_TERR_WINNERS_PUB.Print_Debug('bulk size='||l_var);
110
111 IEX_TERR_WINNERS_PUB.Print_Debug('Calling CheckCollectors');
112 CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
113 if (x_retcode = 'E') then
114 IEX_TERR_WINNERS_PUB.Print_Debug('CheckCollectors Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
115 return;
116 end if;
117
118 if (l_AssignLevel = 'PARTY') then
119 IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreatePartyProfiles');
120 CreatePartyProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
121 if (x_retcode = 'E') then
122 IEX_TERR_WINNERS_PUB.Print_Debug('CreatePartyProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
123 return;
124 end if;
125 IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignPartyCollectors');
126 AssignPartyCollectors(x_errbuf, x_retcode, p_terr_globals);
127 elsif (l_AssignLevel = 'ACCOUNT') then
128 --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
129 IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignAccountCollectors');
130 AssignAccountCollectors(x_errbuf, x_retcode, p_terr_globals);
131 --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. End.
132 else
133 IEX_TERR_WINNERS_PUB.Print_Debug('Calling CreateSiteUseProfiles');
134 CreateSiteUseProfiles(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
135 if (x_retcode = 'E') then
136 IEX_TERR_WINNERS_PUB.Print_Debug('CreateSiteuseProfiles Exception: in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
137 return;
138 end if;
139 IEX_TERR_WINNERS_PUB.Print_Debug('Calling AssignSiteUseCollectors ');
140 AssignSiteUseCollectors(x_errbuf, x_retcode, p_terr_globals);
141 end if;
142 if (x_retcode = 'E') then
143 return;
144 end if;
145
146 EXCEPTION
147
148 WHEN others THEN
149 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::Process_Account_Records');
150 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
151 ' SQLERRM: ' || SQLERRM);
152 x_errbuf := SQLERRM;
153 x_retcode := SQLCODE;
154 RAISE;
155 END Process_Account_Records;
156
157
158 PROCEDURE AssignPartyCollectors(
159 x_errbuf OUT NOCOPY VARCHAR2,
160 x_retcode OUT NOCOPY VARCHAR2,
161 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
162 IS
163 -- Included 'Collections' Role Check
164 CURSOR c_UpdateProfile(c_worker_id number) IS
165 SELECT WIN.TRANS_OBJECT_id,
166 WIN.RESOURCE_id,
167 nvl(WIN.GROUP_ID,-777) GROUP_ID,
168 nvl(WIN.org_id,-777) org_id,
169 win.resource_type,
170 ACC.COLLECTOR_ID
171 FROM JTF_TAE_1600_CUST_WINNERS WIN,
172 AR_COLLECTORS ACC,
173 JTF_RS_ROLE_RELATIONS jtrr,
174 JTF_RS_ROLES_B jtr
175 WHERE WIN.SOURCE_ID = -1600
176 AND WIN.worker_id = c_worker_id
177 AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
178 -- AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag. Fix By LKKUMAR.
179 AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
180 AND DECODE(ACC.RESOURCE_TYPE,
181 'RS_RESOURCE', 'RS_EMPLOYEE',
182 'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
183 AND jtrr.role_resource_id = WIN.RESOURCE_ID
184 AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
185 GROUP BY WIN.TRANS_OBJECT_id,
186 WIN.RESOURCE_ID,
187 WIN.GROUP_ID,
188 WIN.ORG_ID, WIN.RESOURCE_TYPE, ACC.COLLECTOR_ID;
189
190 l_customer_id customer_id_list;
191 l_address_id address_id_list;
192 l_faf faf_list;
193 l_org_id org_id_list;
194 l_res_type res_type_list;
195
196 l_salesforce_id salesforce_id_list;
197 l_sales_group_id sales_group_id_list;
198 l_person_id person_id_list;
199 l_src src_list;
200 l_collector_id collector_list;
201
202 l_access_id access_id_list;
203 l_terr_id terr_id_list;
204
205 l_max_rows NUMBER := 10000;
206 l_attempts NUMBER := 0;
207 l_upd_attempts NUMBER := 0;
208 l_exceptions BOOLEAN := FALSE;
209
210 l_flag BOOLEAN;
211 l_first NUMBER;
212 l_last NUMBER;
213 l_var NUMBER;
214 l_worker_id NUMBER;
215
216
217 l_customer_id_empty customer_id_list;
218 l_address_id_empty address_id_list;
219 l_faf_empty faf_list;
220 l_org_id_empty org_id_list;
221 l_salesforce_id_empty salesforce_id_list;
222 l_sales_group_id_empty sales_group_id_list;
223 l_person_id_empty person_id_list;
224 l_src_empty src_list;
225 l_access_id_empty access_id_list;
226 l_terr_id_empty terr_id_list;
227 l_res_type_empty res_type_list;
228
229
230 l_limit_flag BOOLEAN := FALSE;
231 l_max_fetches NUMBER := 10000;
232 l_loop_count NUMBER := 0;
233 l_src_exists VARCHAR2(1);
234 l_AssignLevel VARCHAR2(20);
235
236 l_WORKER_OVERLIMIT EXCEPTION;
237 l_Status BOOLEAN;
238
239 -------------------------------------------------------------------------------
240
241
242 BEGIN
243 IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Party Level Collector Assignment ***');
244
245 l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
246 FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Program started');
247 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);
248
249 l_worker_id:=p_terr_globals.worker_id;
250 -- Bulk Read the Territory Assignments
251 l_var :=p_terr_globals.bulk_size;
252 l_max_fetches := p_terr_globals.cursor_limit;
253 IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
254 IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
255 IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
256 --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
257 OPEN c_UpdateProfile(l_worker_id);
258 LOOP
259 BEGIN
260 FETCH c_UpdateProfile BULK COLLECT INTO
261 l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
262 LIMIT l_max_fetches;
263 IF l_customer_id.count = 0 THEN
264 IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
265 CLOSE C_UPDATEPROFILE;
266 EXIT;
267 ELSE
268 IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
269 FORALL i in l_customer_id.first..l_customer_id.last
270 UPDATE HZ_CUSTOMER_PROFILES ACC
271 SET object_version_number = nvl(object_version_number,0) + 1,
272 ACC.LAST_UPDATE_DATE = SYSDATE,
273 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
274 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
275 ACC.REQUEST_ID = p_terr_globals.request_id,
276 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
277 ACC.PROGRAM_ID = p_terr_globals.prog_id,
278 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
279 ACC.COLLECTOR_ID = l_collector_id(i)
280 WHERE ACC.PARTY_ID = l_customer_id(i)
281 AND ACC.SITE_USE_ID IS NULL
282 AND ACC.CUST_ACCOUNT_ID = -1
283 AND ACC.COLLECTOR_ID <> l_collector_id(i);
284 --Commit When the Bulk commit size is reached.
285 IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
286 COMMIT;
287 END IF;
288 EXCEPTION WHEN deadlock_detected THEN
289 BEGIN
290 IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during party bulk update.. Performing row update..');
291 ROLLBACK;
292 FOR i in l_first .. l_last LOOP
293 BEGIN
294 UPDATE HZ_CUSTOMER_PROFILES ACC
295 SET object_version_number = nvl(object_version_number,0) + 1,
296 ACC.LAST_UPDATE_DATE = SYSDATE,
297 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
298 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
299 ACC.REQUEST_ID = p_terr_globals.request_id,
300 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
301 ACC.PROGRAM_ID = p_terr_globals.prog_id,
302 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
303 ACC.COLLECTOR_ID = l_collector_id(i)
304 WHERE ACC.PARTY_ID = l_customer_id(i)
305 AND ACC.SITE_USE_ID IS NULL
306 AND ACC.CUST_ACCOUNT_ID = -1
307 AND ACC.COLLECTOR_ID <> l_collector_id(i); EXCEPTION
308 WHEN OTHERS THEN
309 IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
310 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
311 ' SQLERRM: ' || SQLERRM);
312 END;
313 END LOOP;
314 END;
315 WHEN OTHERS THEN
316 IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
317 END;
318 END LOOP;
319 IF C_UPDATEPROFILE%ISOPEN THEN
320 CLOSE C_UPDATEPROFILE;
321 END IF;
322 --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
323 IEX_TERR_WINNERS_PUB.Print_Debug('*** Finished Party Level Collector Assignment ***');
324
325 l_customer_id.delete;
326 l_terr_id.delete;
327 l_customer_id := l_customer_id_empty;
328 l_address_id := l_address_id_empty;
329 l_org_id := l_org_id_empty;
330 l_salesforce_id := l_salesforce_id_empty;
331 l_sales_group_id := l_sales_group_id_empty;
332 l_person_id := l_person_id_empty;
333 l_attempts := 1;
334 l_exceptions := FALSE;
335
336 EXCEPTION
337 WHEN L_WORKER_OVERLIMIT THEN
338 x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
339 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
340 RAISE;
341
342 WHEN others THEN
343 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
344 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
345 ' SQLERRM: ' || SQLERRM);
346 x_errbuf := SQLERRM;
347 x_retcode := SQLCODE;
348 RAISE;
349 END AssignPartyCollectors;
350
351 --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. Start.
352 PROCEDURE AssignAccountCollectors(
353 x_errbuf OUT NOCOPY VARCHAR2,
354 x_retcode OUT NOCOPY VARCHAR2,
355 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
356 IS
357 -- Included 'Collections' Role Check
358 CURSOR c_UpdateProfile(c_worker_id number) IS
359 SELECT WIN.TRANS_OBJECT_id,
360 WIN.RESOURCE_id,
361 nvl(WIN.GROUP_ID,-777) GROUP_ID,
362 nvl(WIN.org_id,-777) org_id,
363 win.resource_type,
364 ACC.COLLECTOR_ID
365 FROM JTF_TAE_1600_CUST_WINNERS WIN,
366 AR_COLLECTORS ACC,
367 JTF_RS_ROLE_RELATIONS jtrr,
368 JTF_RS_ROLES_B jtr
369 WHERE WIN.SOURCE_ID = -1600
370 AND WIN.worker_id = c_worker_id
371 AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
372 --AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag
373 AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
374 AND DECODE(ACC.RESOURCE_TYPE,
375 'RS_RESOURCE', 'RS_EMPLOYEE',
376 'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
377 AND jtrr.role_resource_id = WIN.RESOURCE_ID
378 AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
379 GROUP BY WIN.TRANS_OBJECT_id,
380 WIN.RESOURCE_ID,
381 WIN.GROUP_ID,
382 WIN.ORG_ID, WIN.RESOURCE_TYPE, ACC.COLLECTOR_ID;
383
384 l_customer_id customer_id_list;
385 l_address_id address_id_list;
386 l_faf faf_list;
387 l_org_id org_id_list;
388 l_res_type res_type_list;
389
390 l_salesforce_id salesforce_id_list;
391 l_sales_group_id sales_group_id_list;
392 l_person_id person_id_list;
393 l_src src_list;
394 l_collector_id collector_list;
395
396 l_access_id access_id_list;
397 l_terr_id terr_id_list;
398
399 l_max_rows NUMBER := 10000;
400 l_attempts NUMBER := 0;
401 l_upd_attempts NUMBER := 0;
402 l_exceptions BOOLEAN := FALSE;
403
404 l_flag BOOLEAN;
405 l_first NUMBER;
406 l_last NUMBER;
407 l_var NUMBER;
408 l_worker_id NUMBER;
409
410
411 l_customer_id_empty customer_id_list;
412 l_address_id_empty address_id_list;
413 l_faf_empty faf_list;
414 l_org_id_empty org_id_list;
415 l_salesforce_id_empty salesforce_id_list;
416 l_sales_group_id_empty sales_group_id_list;
417 l_person_id_empty person_id_list;
418 l_src_empty src_list;
419 l_access_id_empty access_id_list;
420 l_terr_id_empty terr_id_list;
421 l_res_type_empty res_type_list;
422
423
424 l_limit_flag BOOLEAN := FALSE;
425 l_max_fetches NUMBER := 10000;
426 l_loop_count NUMBER := 0;
427 l_src_exists VARCHAR2(1);
428 l_AssignLevel VARCHAR2(20);
429
430 l_WORKER_OVERLIMIT EXCEPTION;
431 l_Status BOOLEAN;
432
433 -------------------------------------------------------------------------------
434
435
436 BEGIN
437 IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Account Level Collector Assignment ***');
438
439 l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
440 FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Program started');
441 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);
442
443 l_worker_id:=p_terr_globals.worker_id;
444 -- Bulk Read the Territory Assignments
445 l_var :=p_terr_globals.bulk_size;
446 l_max_fetches := p_terr_globals.cursor_limit;
447 IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size='||l_var);
448 IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size = ' || l_max_fetches);
449 IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
450
451 OPEN c_UpdateProfile(l_worker_id);
452 LOOP
453 BEGIN
454 FETCH c_UpdateProfile BULK COLLECT INTO
455 l_customer_id, l_salesforce_id, l_sales_group_id, l_org_id, l_Res_type, l_Collector_ID
456 LIMIT l_max_fetches;
457 IF l_customer_id.count = 0 THEN
458 IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
459 CLOSE C_UPDATEPROFILE;
460 EXIT;
461 ELSE
462 IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
463 FORALL i in l_customer_id.first..l_customer_id.last
464 UPDATE HZ_CUSTOMER_PROFILES ACC
465 SET object_version_number = nvl(object_version_number,0) + 1,
466 ACC.LAST_UPDATE_DATE = SYSDATE,
467 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
468 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
469 ACC.REQUEST_ID = p_terr_globals.request_id,
470 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
471 ACC.PROGRAM_ID = p_terr_globals.prog_id,
472 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
473 ACC.COLLECTOR_ID = l_collector_id(i)
474 WHERE ACC.PARTY_ID = l_customer_id(i)
475 AND ACC.SITE_USE_ID IS NULL
476 AND ACC.CUST_ACCOUNT_ID <> -1
477 AND ACC.COLLECTOR_ID <> l_collector_id(i);
478 --Commit When the Bulk commit size is reached.
479 IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
480 COMMIT;
481 END IF;
482 EXCEPTION WHEN deadlock_detected THEN
483 BEGIN
484 IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
485 ROLLBACK;
486 FOR i in l_first .. l_last LOOP
487 BEGIN
488 UPDATE HZ_CUSTOMER_PROFILES ACC
489 SET object_version_number = nvl(object_version_number,0) + 1,
490 ACC.LAST_UPDATE_DATE = SYSDATE,
491 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
492 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
493 ACC.REQUEST_ID = p_terr_globals.request_id,
494 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
495 ACC.PROGRAM_ID = p_terr_globals.prog_id,
496 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
497 ACC.COLLECTOR_ID = l_collector_id(i)
498 WHERE ACC.PARTY_ID = l_customer_id(i)
499 AND ACC.SITE_USE_ID IS NULL
500 AND ACC.CUST_ACCOUNT_ID <> -1
501 AND ACC.COLLECTOR_ID <> l_collector_id(i);
502 EXCEPTION WHEN OTHERS THEN
503 IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
504 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
505 ' SQLERRM: ' || SQLERRM);
506 END;
507 END LOOP;
508 END;
509 WHEN OTHERS THEN
510 IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
511 END;
512 END LOOP;
513 IF C_UPDATEPROFILE%ISOPEN THEN
514 CLOSE C_UPDATEPROFILE;
515 END IF;
516
517 IEX_TERR_WINNERS_PUB.Print_Debug('*** Completed Account Level Collector Assignment ***');
518 l_loop_count := 0;
519
520 l_customer_id.delete;
521 l_terr_id.delete;
522 l_customer_id := l_customer_id_empty;
523 l_address_id := l_address_id_empty;
524 l_org_id := l_org_id_empty;
525 l_salesforce_id := l_salesforce_id_empty;
526 l_sales_group_id := l_sales_group_id_empty;
527 l_person_id := l_person_id_empty;
528
529 l_attempts := 1;
530 l_exceptions := FALSE;
531
532 EXCEPTION
533 WHEN L_WORKER_OVERLIMIT THEN
534 x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
535 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
536 RAISE;
537
538 WHEN others THEN
539 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AssignPartyAccountCollectors::Process_Account_Records');
540 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
541 ' SQLERRM: ' || SQLERRM);
542 x_errbuf := SQLERRM;
543 x_retcode := SQLCODE;
544 RAISE;
545 END AssignAccountCollectors;
546 --Bug4650943. Fix By LKKUMAR on 04-Oct-2005. End.
547
548
549 PROCEDURE AssignSiteUseCollectors(
550 x_errbuf OUT NOCOPY VARCHAR2,
551 x_retcode OUT NOCOPY VARCHAR2,
552 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
553 IS
554 /*CURSOR c_UpdateProfile(c_worker_id number) IS
555 SELECT WIN.TRANS_OBJECT_id,
556 WIN.TRANS_DETAIL_OBJECT_ID,
557 WIN.RESOURCE_id,
558 nvl(WIN.GROUP_ID,-777) GROUP_ID,
559 nvl(WIN.org_id,-777) org_id,
560 win.resource_type,
561 ACC.COLLECTOR_ID,
562 cust_acct.cust_account_id,
563 hzp.SITE_USE_ID
564 FROM JTF_TAE_1600_CUST_WINNERS WIN,
565 AR_COLLECTORS ACC,
566 JTF_RS_ROLE_RELATIONS jtrr,
567 JTF_RS_ROLES_B jtr,
568 HZ_CUST_SITE_USES_ALL hzp,
569 HZ_CUST_ACCT_SITES_ALL acct_site,
570 HZ_CUST_ACCOUNTS cust_acct
571 WHERE WIN.SOURCE_ID = -1600
572 AND WIN.worker_id = c_worker_id
573 AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
574 --AND WIN.full_access_flag = 'Y'
575 AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
576 AND DECODE(ACC.RESOURCE_TYPE,
577 'RS_RESOURCE', 'RS_EMPLOYEE',
578 'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
579 AND jtrr.role_resource_id = WIN.RESOURCE_ID
580 AND jtr.ROLE_ID = jtrr.role_id
581 and jtr.role_type_code = 'COLLECTIONS'
582 --AND win.trans_detail_object_id is not null
583 --AND acct_site.party_site_id = win.trans_detail_object_id
584 AND WIN.TRANS_OBJECT_ID = CUST_ACCT.PARTY_ID
585 AND CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id
586 AND acct_site.cust_acct_site_id = hzp.cust_acct_site_id
587 AND hzp.SITE_USE_CODE = 'BILL_TO'
588 AND WIN.ORG_ID = acct_site.ORG_ID
589 AND acct_site.ORG_ID = hzp.ORG_ID
590 GROUP BY WIN.TRANS_OBJECT_id,
591 WIN.TRANS_DETAIL_OBJECT_ID,
592 WIN.RESOURCE_ID,
593 WIN.GROUP_ID,
594 WIN.ORG_ID,
595 WIN.RESOURCE_TYPE,
596 ACC.COLLECTOR_ID,
597 cust_acct.cust_account_id,
598 hzp.SITE_USE_ID;*/
599 CURSOR c_UpdateProfile(c_worker_id number) IS
600 SELECT WIN.TRANS_OBJECT_id,
601 WIN.TRANS_DETAIL_OBJECT_ID,
602 WIN.RESOURCE_id,
603 nvl(WIN.GROUP_ID,-777) GROUP_ID,
604 nvl(WIN.org_id,-777) org_id,
605 win.resource_type,
606 ACC.COLLECTOR_ID,
607 acct_site.cust_account_id,
608 hzp.SITE_USE_ID
609 FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
610 JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr,
611 HZ_CUST_SITE_USES hzp,
612 HZ_CUST_ACCT_SITES acct_site
613 WHERE WIN.SOURCE_ID = -1600
614 AND WIN.worker_id = c_worker_id
615 AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
616 --AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag.
617 AND win.org_id = hzp.org_id
618 AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
619 AND DECODE(ACC.RESOURCE_TYPE,
620 'RS_RESOURCE', 'RS_EMPLOYEE',
621 'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
622 AND jtrr.role_resource_id = WIN.RESOURCE_ID
623 AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
624 AND win.trans_detail_object_id is not null
625 AND acct_site.party_site_id = win.trans_detail_object_id
626 AND acct_site.cust_acct_site_id = hzp.cust_acct_site_id
627 AND hzp.SITE_USE_CODE = 'BILL_TO'
628 GROUP BY WIN.TRANS_OBJECT_id,
629 WIN.TRANS_DETAIL_OBJECT_ID,
630 WIN.RESOURCE_ID,
631 WIN.GROUP_ID,
632 WIN.ORG_ID, WIN.RESOURCE_TYPE,
633 ACC.COLLECTOR_ID,
634 acct_site.cust_account_id,
635 hzp.SITE_USE_ID;
636
637 l_customer_id customer_id_list;
638 l_address_id address_id_list;
639 l_faf faf_list;
640 l_org_id org_id_list;
641 l_res_type res_type_list;
642
643 l_salesforce_id salesforce_id_list;
644 l_sales_group_id sales_group_id_list;
645 l_person_id person_id_list;
646 l_src src_list;
647 l_collector_id collector_list;
648 l_siteuse_id site_use_id_list;
649 l_partysite_id party_site_id_list;
650 l_cust_account_id cust_account_id_list;
651
652 l_access_id access_id_list;
653 l_terr_id terr_id_list;
654
655 l_max_rows NUMBER := 10000;
656 l_attempts NUMBER := 0;
657 l_exceptions BOOLEAN := FALSE;
658
659 l_flag BOOLEAN;
660 l_first NUMBER;
661 l_last NUMBER;
662 l_var NUMBER;
663 l_worker_id NUMBER;
664
665 l_customer_id_empty customer_id_list;
666 l_address_id_empty address_id_list;
667 l_faf_empty faf_list;
668 l_org_id_empty org_id_list;
669 l_salesforce_id_empty salesforce_id_list;
670 l_sales_group_id_empty sales_group_id_list;
671 l_person_id_empty person_id_list;
672 l_src_empty src_list;
673 l_access_id_empty access_id_list;
674 l_terr_id_empty terr_id_list;
675 l_res_type_empty res_type_list;
676 l_cust_account_id_empty cust_account_id_list;
677
678 l_limit_flag BOOLEAN := FALSE;
679 l_max_fetches NUMBER := 10000;
680 l_loop_count NUMBER := 0;
681 l_src_exists VARCHAR2(1);
682 l_AssignLevel VARCHAR2(20);
683
684 l_WORKER_OVERLIMIT EXCEPTION;
685 l_Status BOOLEAN;
686 -------------------------------------------------------------------------------
687
688 BEGIN
689 IEX_TERR_WINNERS_PUB.Print_Debug('*** Started Site Level Collector Assignment ***');
690
691 l_Assignlevel := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
692 l_worker_id :=p_terr_globals.worker_id;
693 l_var :=p_terr_globals.bulk_size;
694 l_max_fetches := p_terr_globals.cursor_limit;
695
696
697 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Level (Picked from IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);
698 IEX_TERR_WINNERS_PUB.Print_Debug('Bulk Size =' ||l_var);
699 IEX_TERR_WINNERS_PUB.Print_Debug('Cursor Fetch Size =' ||l_max_fetches);
700
701 CheckCollectors(x_errbuf, x_retcode, l_worker_id, p_terr_globals);
702 if (x_retcode = 'E') then
703 IEX_TERR_WINNERS_PUB.Print_Debug('Error While creating Collectors, Not able to create collector');
704 IEX_TERR_WINNERS_PUB.Print_Debug('Not able to proceed with update, returning back');
705 return;
706 end if;
707 IEX_TERR_WINNERS_PUB.Print_Debug('Updating Profiles started ... ');
708 --Bug4613487. Fix by lkkumar on 29-Sep-2005. Start.
709 OPEN c_UpdateProfile(l_worker_id);
710 LOOP
711 BEGIN
712 FETCH c_UpdateProfile BULK COLLECT INTO
713 l_customer_id, l_partysite_id, l_salesforce_id, l_sales_group_id,
714 l_org_id, l_Res_type, l_Collector_ID,l_cust_account_id, l_siteuse_id
715 LIMIT l_max_fetches;
716 IF l_customer_id.count = 0 THEN
717 IEX_TERR_WINNERS_PUB.Print_Debug('Update Completed. Exiting the update loop');
718 CLOSE C_UPDATEPROFILE;
719 EXIT;
720 ELSE
721 IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Fetched ' || l_customer_id.count);
722 FORALL i in l_customer_id.first..l_customer_id.last
723 UPDATE HZ_CUSTOMER_PROFILES ACC
724 SET object_version_number = nvl(object_version_number,0) + 1,
725 ACC.LAST_UPDATE_DATE = SYSDATE,
726 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
727 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
728 ACC.REQUEST_ID = p_terr_globals.request_id,
729 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
730 ACC.PROGRAM_ID = p_terr_globals.prog_id,
731 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
732 ACC.COLLECTOR_ID = l_collector_id(i)
733 WHERE ACC.PARTY_ID = l_customer_id(i)
734 AND ACC.CUST_ACCOUNT_ID = l_cust_account_id(i)
735 AND ACC.SITE_USE_ID = l_siteuse_id(i)
736 AND ACC.COLLECTOR_ID <> l_collector_id(i);
737 --Commit When the Bulk commit size is reached.
738 IEX_TERR_WINNERS_PUB.Print_Debug('Total Rows Updated ' || l_customer_id.count);
739 COMMIT;
740 END IF;
741 EXCEPTION WHEN deadlock_detected THEN
742 BEGIN
743 IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update.. Performing row update..');
744 ROLLBACK;
745 FOR i in l_first .. l_last LOOP
746 BEGIN
747 UPDATE HZ_CUSTOMER_PROFILES ACC
748 SET object_version_number = nvl(object_version_number,0) + 1,
749 ACC.LAST_UPDATE_DATE = SYSDATE,
750 ACC.LAST_UPDATED_BY = p_terr_globals.user_id,
751 ACC.LAST_UPDATE_LOGIN = p_terr_globals.last_update_login,
752 ACC.REQUEST_ID = p_terr_globals.request_id,
753 ACC.PROGRAM_APPLICATION_ID = p_terr_globals.prog_appl_id,
754 ACC.PROGRAM_ID = p_terr_globals.prog_id,
755 ACC.PROGRAM_UPDATE_DATE = SYSDATE,
756 ACC.COLLECTOR_ID = l_collector_id(i)
757 WHERE ACC.PARTY_ID = l_customer_id(i)
758 AND ACC.CUST_ACCOUNT_ID = l_cust_account_id(i)
759 AND ACC.SITE_USE_ID = l_siteuse_id(i)
760 AND ACC.COLLECTOR_ID <> l_collector_id(i);
761 EXCEPTION
762 WHEN OTHERS THEN
763 IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
764 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
765 ' SQLERRM: ' || SQLERRM);
766 END;
767 END LOOP;
768 END;
769 WHEN OTHERS THEN
770 IEX_TERR_WINNERS_PUB.Print_Debug('Exception occured while updating site profile '||sqlerrm);
771 END;
772 END LOOP;
773 IF C_UPDATEPROFILE%ISOPEN THEN
774 CLOSE C_UPDATEPROFILE;
775 END IF;
776 --Bug4613487. Fix by lkkumar on 29-Sep-2005. End.
777 IEX_TERR_WINNERS_PUB.Print_Debug('*** Completed Site Level Collector Assignment ***');
778
779 l_limit_flag := FALSE;
780 l_loop_count := 0;
781
782 l_customer_id.delete;
783 l_terr_id.delete;
784 l_customer_id := l_customer_id_empty;
785 l_address_id := l_address_id_empty;
786 l_org_id := l_org_id_empty;
787 l_salesforce_id := l_salesforce_id_empty;
788 l_sales_group_id := l_sales_group_id_empty;
789 l_person_id := l_person_id_empty;
790 l_cust_account_id:= l_cust_account_id_empty;
791
792 l_attempts := 1;
793 l_exceptions := FALSE;
794
795 EXCEPTION
796 WHEN L_WORKER_OVERLIMIT THEN
797 x_retcode := FND_API.G_RET_STS_UNEXP_ERROR;
798 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
799 RAISE;
800 WHEN others THEN
801 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_WINNERS::AssignSiteUseCollectors');
802 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
803 ' SQLERRM: ' || SQLERRM);
804 x_errbuf := SQLERRM;
805 x_retcode := SQLCODE;
806 RAISE;
807 END AssignSiteUseCollectors;
808
809 PROCEDURE CheckCollectors(
810 x_errbuf OUT NOCOPY VARCHAR2,
811 x_retcode OUT NOCOPY VARCHAR2,
812 p_worker_id IN NUMBER,
813 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
814
815 l_missSF_id salesforce_id_list; -- Missed Sales Force ID
816 l_missSG_id sales_group_id_list; -- Missed Sales Group ID
817 l_missPer_ID person_id_list; -- Missed Person ID
818 l_missOrg_ID org_id_list;
819 l_missResType res_type_list; -- Missed Resource Type
820
821 l_max_fetches NUMBER;
822 l_limit_flag boolean;
823 l_loop_count NUMBER;
824 l_attempts number;
825 l_exceptions boolean;
826
827 l_flag BOOLEAN;
828 l_first NUMBER;
829 l_last NUMBER;
830 l_var NUMBER;
831
832 l_source_id NUMBER;
833 l_Resource_name VARCHAR2(300);
834
835 CURSOR c_MissedCollectors(c_worker_id number) IS
836 SELECT DISTINCT WIN.RESOURCE_id,
837 nvl(WIN.GROUP_ID,-777) GROUP_ID,
838 nvl(WIN.org_id,-777) org_id,
839 win.resource_type,
840 WIN.PERSON_ID
841 FROM JTF_TAE_1600_CUST_WINNERS WIN,
842 JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr
843 WHERE WIN.SOURCE_ID = -1600
844 AND WIN.worker_id = c_worker_id
845 AND WIN.RESOURCE_TYPE IN ('RS_EMPLOYEE', 'RS_GROUP')
846 AND jtrr.role_resource_id = WIN.RESOURCE_ID
847 AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
848 AND NOT EXISTS
849 (SELECT RESOURCE_ID, RESOURCE_TYPE FROM AR_COLLECTORS acc
850 WHERE ACC.RESOURCE_ID = WIN.RESOURCE_ID AND
851 ACC.RESOURCE_TYPE = DECODE(WIN.RESOURCE_TYPE, 'RS_EMPLOYEE', 'RS_RESOURCE', WIN.RESOURCE_TYPE));
852
853 Begin
854 -- Bulk Read the Non-existents in AR_COLLECTORS
855 l_loop_count := 0;
856 l_max_fetches := p_terr_globals.cursor_limit;
857 LOOP
858 if (l_limit_flag) then
859 EXIT;
860 End If;
861 l_loop_count := l_loop_count + 1;
862 IEX_TERR_WINNERS_PUB.Print_Debug('*** Check Resources not in AR Collectors. LOOPING Count -> :'||l_loop_count);
863
864 --------------------------------
865 l_attempts := 1;
866 l_exceptions := FALSE;
867 WHILE l_attempts < 3 LOOP -- Bulk read Collectors. attempts < 3
868 BEGIN
869 IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
870 OPEN c_MissedCollectors(p_worker_id);
871 FETCH c_MissedCollectors BULK COLLECT INTO
872 l_missSF_id, l_missSG_id, l_missOrg_ID, l_MissResType, l_missPer_ID
873 LIMIT l_max_fetches;
874 CLOSE c_MissedCollectors;
875 l_attempts := 3;
876 l_exceptions := FALSE;
877 EXCEPTION
878 WHEN Others THEN
879 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
880 l_attempts := l_attempts +1;
881 l_exceptions := TRUE;
882 if c_MissedCollectors%ISOPEN then
883 CLOSE c_MissedCollectors;
884 end if;
885 if l_attempts > 2 then
886 x_errbuf := SQLERRM;
887 x_retcode := SQLCODE;
888 RAISE;
889 end if;
890 END;
891 END LOOP; -- End Bulk read Sales Force ID. attempts < 3
892 IEX_TERR_WINNERS_PUB.Print_Debug('--- Read Missed Collectors End-Attempts: '||l_attempts);
893
894 -- Initialize variables
895 if l_missSF_id.count < l_max_fetches then
896 l_limit_flag := TRUE;
897 end if;
898
899 IEX_TERR_WINNERS_PUB.Print_Debug('--- Start INSERT OF AR_COLLECTORS = . ' || l_missSF_id.count);
900
901 IF l_missSF_id.count > 0 THEN -- if l_SalesForce_id.count > 0
902
903 l_attempts := 1;
904 IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
905
906 WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
907 BEGIN
908
909 IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
910
911 FOR i in 1 .. l_missSF_id.count LOOP
912 BEGIN
913 IF (l_missResType(i) = 'RS_GROUP') THEN
914 SELECT GROUP_NAME
915 INTO l_resource_name
916 FROM jtf_rs_groups_vl
917 WHERE group_id = l_missSF_id(i);
918 ELSE
919 SELECT resource_name, source_id
920 INTO l_resource_name, l_source_id
921 FROM jtf_rs_resource_extns_vl
922 WHERE resource_id = l_missSF_id(i);
923 END IF;
924 IEX_TERR_WINNERS_PUB.Print_Debug('After selecting Resource_name = '|| l_resource_name );
925
926 INSERT INTO AR_COLLECTORS
927 (COLLECTOR_ID ,
928 LAST_UPDATED_BY ,
929 LAST_UPDATE_DATE ,
930 LAST_UPDATE_LOGIN ,
931 CREATION_DATE ,
932 CREATED_BY ,
933 NAME ,
934 EMPLOYEE_ID ,
935 DESCRIPTION ,
936 STATUS ,
937 RESOURCE_ID ,
938 RESOURCE_TYPE )
939 VALUES
940 (AR_COLLECTORS_S.NEXTVAL ,
941 p_terr_globals.user_id ,
942 sysdate ,
943 p_terr_globals.last_update_login ,
944 sysdate ,
945 p_terr_globals.user_id ,
946 substr(l_resource_name,1, 30),
947 l_source_id ,
948 l_resource_name ,
949 'A',
950 l_missSF_id(i),
951 decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
952
953 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inserted to the AR_COLLECTORS. Collector_ID ');
954
955 EXCEPTION
956 WHEN OTHERS THEN
957 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
958 END;
959 COMMIT;
960 END LOOP;
961 l_attempts := 3;
962 IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
963 EXCEPTION
964 WHEN deadlock_detected THEN
965 begin
966 IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
967 rollback;
968 l_attempts := l_attempts +1;
969 if l_attempts = 3 then
970 FOR i in 1 .. l_missSF_id.count LOOP /*Inside deadlock detected loop */
971 BEGIN
972 IF (l_missResType(i) = 'RS_GROUP') THEN
973 SELECT GROUP_NAME
974 INTO l_resource_name
975 FROM jtf_rs_groups_vl
976 WHERE group_id = l_missSF_id(i);
977 ELSE
978 SELECT resource_name, source_id
979 INTO l_resource_name, l_source_id
980 FROM jtf_rs_resource_extns_vl
981 WHERE resource_id = l_missSF_id(i);
982 END IF;
983
984 INSERT INTO AR_COLLECTORS
985 (COLLECTOR_ID ,
986 LAST_UPDATED_BY ,
987 LAST_UPDATE_DATE ,
988 LAST_UPDATE_LOGIN ,
989 CREATION_DATE ,
990 CREATED_BY ,
991 NAME ,
992 EMPLOYEE_ID ,
993 DESCRIPTION ,
994 STATUS ,
995 RESOURCE_ID ,
996 RESOURCE_TYPE )
997 VALUES
998 (AR_COLLECTORS_S.NEXTVAL ,
999 p_terr_globals.user_id ,
1000 sysdate ,
1001 p_terr_globals.last_update_login ,
1002 sysdate ,
1003 p_terr_globals.user_id ,
1004 substr(l_resource_name,1,30) ,
1005 l_source_id ,
1006 l_resource_name ,
1007 'A',
1008 l_missSF_id(i),
1009 decode(l_missResType(i),'RS_GROUP','RS_GROUP','RS_RESOURCE' )) ;
1010
1011 FND_FILE.PUT_LINE(FND_FILE.LOG, ' After Inserting in to the AR_COLLECTORS');
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception during single row update');
1016 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1017 ' SQLERRM: ' || SQLERRM);
1018 END;
1019 END LOOP; /* End Inside deadlock detected loop */
1020 COMMIT;
1021 end if;
1022 end; -- end of deadlock exception
1023
1024 WHEN OTHERS THEN
1025 IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1026 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1027 ' SQLERRM: ' || SQLERRM);
1028 x_errbuf := SQLERRM;
1029 x_retcode := SQLCODE;
1030 RAISE;
1031 END;
1032 END LOOP; /* Update While loop; l_attempts < 3 */
1033
1034 END IF; --l_salesforce.count > 0
1035 IEX_TERR_WINNERS_PUB.Print_Debug('---Check Collectors Account.End-'|| l_missSF_id.count||' Rows Updated.');
1036 --------------------------------
1037 END LOOP; -- End Bulk read non-existent Collector ID. attempts < 3
1038
1039 EXCEPTION
1040 WHEN others THEN
1041 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CheckCollectors');
1042 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1043 ' SQLERRM: ' || SQLERRM);
1044 x_errbuf := SQLERRM;
1045 x_retcode := SQLCODE;
1046 RAISE;
1047 END CheckCollectors;
1048
1049
1050 PROCEDURE CreatePartyProfiles(
1051 x_errbuf OUT NOCOPY VARCHAR2,
1052 x_retcode OUT NOCOPY VARCHAR2,
1053 p_worker_id IN NUMBER,
1054 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
1055
1056 l_missCustomer customer_id_list; -- Missed Customer Profiles
1057
1058 l_max_fetches NUMBER;
1059 l_limit_flag boolean;
1060 l_loop_count NUMBER;
1061 l_attempts number;
1062 l_exceptions boolean;
1063
1064 l_customer_profile_id NUMBER;
1065 l_return_status VARChar2(10);
1066 l_msg_count NUMBER;
1067 l_msg_data VARCHAR2(2000);
1068
1069
1070 l_flag BOOLEAN;
1071 l_first NUMBER;
1072 l_last NUMBER;
1073 l_var NUMBER;
1074
1075 l_source_id NUMBER;
1076 l_Resource_name VARCHAR2(300);
1077 l_old_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1078
1079 CURSOR c_MissedProfiles(c_worker_id number) IS
1080 SELECT WIN.TRANS_OBJECT_id
1081 FROM JTF_TAE_1600_CUST_WINNERS WIN,
1082 AR_COLLECTORS ACC,
1083 JTF_RS_ROLE_RELATIONS jtrr,
1084 JTF_RS_ROLES_B jtr
1085 WHERE WIN.SOURCE_ID = -1600
1086 AND WIN.worker_id = c_worker_id
1087 AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
1088 -- AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag. Fix By LKKUMAR.
1089 AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
1090 AND DECODE(ACC.RESOURCE_TYPE,
1091 'RS_RESOURCE', 'RS_EMPLOYEE',
1092 'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
1093 AND jtrr.role_resource_id = WIN.RESOURCE_ID
1094 AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
1095 AND NOT EXISTS
1096 (SELECT 1 FROM HZ_CUSTOMER_PROFILES hcp
1097 WHERE hcp.CUST_ACCOUNT_ID = -1 AND
1098 hcp.PARTY_ID = WIN.TRANS_OBJECT_ID AND
1099 hcp.site_use_id is null )
1100 GROUP BY WIN.TRANS_OBJECT_id;
1101 l_profile_id NUMBER;
1102
1103 Begin
1104 -- Bulk Read the Non-existents in AR_COLLECTORS
1105 l_loop_count := 0;
1106 l_max_fetches := p_terr_globals.cursor_limit;
1107 LOOP
1108 if (l_limit_flag) then
1109 EXIT;
1110 End If;
1111 l_loop_count := l_loop_count + 1;
1112 IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);
1113
1114 --------------------------------
1115 l_attempts := 1;
1116 l_exceptions := FALSE;
1117 WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3
1118 BEGIN
1119 IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
1120 OPEN c_MissedProfiles(p_worker_id);
1121 FETCH c_MissedProfiles BULK COLLECT INTO l_missCustomer
1122 LIMIT l_max_fetches;
1123 CLOSE c_MissedProfiles;
1124 l_attempts := 3;
1125 l_exceptions := FALSE;
1126 EXCEPTION
1127 WHEN Others THEN
1128 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1129 l_attempts := l_attempts +1;
1130 l_exceptions := TRUE;
1131 if c_MissedProfiles%ISOPEN then
1132 CLOSE c_MissedProfiles;
1133 end if;
1134 if l_attempts > 2 then
1135 x_errbuf := SQLERRM;
1136 x_retcode := SQLCODE;
1137 RAISE;
1138 end if;
1139 END;
1140 END LOOP; -- End Bulk read Party list. attempts < 3
1141 IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles. End -Attempts: '||l_attempts);
1142
1143 -- Initialize variables
1144 if l_missCustomer.count < l_max_fetches then
1145 l_limit_flag := TRUE;
1146 end if;
1147
1148 IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Creating customer Profiles = . ' || l_missCustomer.count);
1149
1150 IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1151
1152 IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
1153 l_attempts := 1;
1154
1155 WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1156 BEGIN
1157
1158 IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1159
1160 FOR i in 1 .. l_missCustomer.count LOOP
1161 --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. Start.
1162 BEGIN
1163 SELECT CUST_ACCOUNT_PROFILE_ID
1164 INTO l_profile_id
1165 FROM HZ_CUSTOMER_PROFILES hcp
1166 WHERE hcp.CUST_ACCOUNT_ID = -1
1167 AND hcp.PARTY_ID = l_missCustomer(i)
1168 AND hcp.site_use_id is null;
1169 EXCEPTION WHEN NO_DATA_FOUND THEN
1170 BEGIN
1171 IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1172 l_old_customer_profile_rec.party_id := l_missCustomer(i);
1173 l_old_customer_profile_rec.created_by_module := 'IEX';
1174 l_old_customer_profile_rec.site_use_id := NULL;
1175 l_old_customer_profile_rec.cust_account_id := NULL;
1176
1177 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
1178 p_customer_profile_rec => l_old_customer_profile_rec,
1179 x_cust_account_profile_id => l_customer_profile_id,
1180 x_return_status => l_return_status,
1181 x_msg_count => l_msg_count,
1182 x_msg_data => l_msg_data);
1183
1184 IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || l_msg_count);
1185 IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1186
1187 EXCEPTION
1188 WHEN OTHERS THEN
1189 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
1190 END;
1191 END;
1192 --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. END.
1193 END LOOP;
1194 COMMIT;
1195 l_attempts := 3;
1196 IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1197 EXCEPTION
1198 WHEN deadlock_detected THEN
1199 begin
1200 IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1201 rollback;
1202 l_attempts := l_attempts +1;
1203 if l_attempts = 3 then
1204 FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */
1205 --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. Start.
1206 BEGIN
1207 SELECT CUST_ACCOUNT_PROFILE_ID
1208 INTO l_profile_id
1209 FROM HZ_CUSTOMER_PROFILES hcp
1210 WHERE hcp.CUST_ACCOUNT_ID = -1
1211 AND hcp.PARTY_ID = l_missCustomer(i)
1212 AND hcp.site_use_id is null;
1213 EXCEPTION WHEN NO_DATA_FOUND THEN
1214 BEGIN
1215 IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1216 l_old_customer_profile_rec.party_id := l_missCustomer(i);
1217 l_old_customer_profile_rec.created_by_module := 'IEX';
1218
1219 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
1220 p_customer_profile_rec => l_old_customer_profile_rec,
1221 x_cust_account_profile_id => l_customer_profile_id,
1222 x_return_status => l_return_status,
1223 x_msg_count => l_msg_count,
1224 x_msg_data => l_msg_data);
1225
1226 IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || l_msg_count);
1227 IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1232 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1233 ' SQLERRM: ' || SQLERRM);
1234 END;
1235 END;
1236 --Bug4574749. Fix By LKKUMAR on 12-Oct-2005. End.
1237
1238 END LOOP; /* End Inside deadlock detected loop */
1239 COMMIT;
1240 end if;
1241 end; -- end of deadlock exception
1242
1243 WHEN OTHERS THEN
1244 IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1245 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1246 ' SQLERRM: ' || SQLERRM);
1247 x_errbuf := SQLERRM;
1248 x_retcode := SQLCODE;
1249 RAISE;
1250 END;
1251 END LOOP; /* Update While loop; l_attempts < 3 */
1252
1253 END IF; --l_missCustomer .count > 0
1254 IEX_TERR_WINNERS_PUB.Print_Debug('---Create Customer Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1255 --------------------------------
1256 END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1257
1258 EXCEPTION
1259 WHEN others THEN
1260 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1261 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1262 ' SQLERRM: ' || SQLERRM);
1263 x_errbuf := SQLERRM;
1264 x_retcode := SQLCODE;
1265 RAISE;
1266 END CreatePartyProfiles;
1267
1268
1269 PROCEDURE CreateSiteUseProfiles(
1270 x_errbuf OUT NOCOPY VARCHAR2,
1271 x_retcode OUT NOCOPY VARCHAR2,
1272 p_worker_id IN NUMBER,
1273 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS) AS
1274
1275 l_missCustomer customer_id_list; -- Missed Customer Profiles
1276 l_missSiteUse site_use_id_list;
1277 l_missAccount cust_account_id_list;
1278
1279 l_max_fetches NUMBER;
1280 l_limit_flag boolean;
1281 l_loop_count NUMBER;
1282 l_attempts number;
1283 l_exceptions boolean;
1284
1285 l_customer_profile_id NUMBER;
1286 l_return_status VARChar2(10);
1287 l_msg_count NUMBER;
1288 l_msg_data VARCHAR2(2000);
1289
1290 l_flag BOOLEAN;
1291 l_first NUMBER;
1292 l_last NUMBER;
1293 l_var NUMBER;
1294
1295 l_source_id NUMBER;
1296 l_Resource_name VARCHAR2(300);
1297 l_old_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1298
1299 CURSOR c_MissedProfiles(c_worker_id number) IS
1300 SELECT win.trans_object_id, hzp.SITE_USE_ID,
1301 acct_site.cust_account_id
1302 FROM JTF_TAE_1600_CUST_WINNERS WIN, AR_COLLECTORS ACC,
1303 JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtr,
1304 HZ_CUST_SITE_USES hzp,
1305 HZ_CUST_ACCT_SITES acct_site
1306 WHERE WIN.SOURCE_ID = -1600
1307 AND WIN.worker_id = c_worker_id
1308 AND WIN.resource_type in ('RS_EMPLOYEE', 'RS_GROUP')
1309 -- AND WIN.full_access_flag = 'Y' Bug5043777. Remove the Full_Access_Flag. Fix By LKKUMAR.
1310 AND ACC.RESOURCE_ID = WIN.RESOURCE_ID
1311 AND DECODE(ACC.RESOURCE_TYPE,
1312 'RS_RESOURCE', 'RS_EMPLOYEE',
1313 'RS_GROUP', 'RS_GROUP', 'RS_EMPLOYEE') = WIN.RESOURCE_TYPE
1314 AND jtrr.role_resource_id = WIN.RESOURCE_ID
1315 AND jtr.ROLE_ID = jtrr.role_id and jtr.role_type_code = 'COLLECTIONS'
1316 AND win.trans_detail_object_id is not null
1317 AND acct_site.party_site_id = win.trans_detail_object_id
1318 AND hzp.cust_acct_site_id = acct_site.cust_acct_site_id
1319 AND win.org_id = hzp.org_id
1320 and hzp.SITE_USE_CODE = 'BILL_TO'
1321 AND NOT EXISTS
1322 (SELECT 1 FROM HZ_CUSTOMER_PROFILES hcp
1323 WHERE hcp.PARTY_ID = WIN.TRANS_OBJECT_ID AND
1324 hcp.site_use_id = HZP.site_use_id )
1325 GROUP BY WIN.TRANS_OBJECT_ID, acct_site.cust_account_id, hzp.SITE_USE_ID;
1326
1327 Begin
1328 -- Bulk Read the Non-existents in HZ_CUSTOMER_PROFILES Customer Sites
1329 l_loop_count := 0;
1330 l_max_fetches := p_terr_globals.cursor_limit;
1331 LOOP
1332 if (l_limit_flag) then
1333 EXIT;
1334 End If;
1335 l_loop_count := l_loop_count + 1;
1336 IEX_TERR_WINNERS_PUB.Print_Debug('*** Getting Parties with no profiles. LOOPING Count -> :'||l_loop_count);
1337
1338 --------------------------------
1339 l_attempts := 1;
1340 l_exceptions := FALSE;
1341 WHILE l_attempts < 3 LOOP -- Bulk read Party list. attempts < 3
1342 BEGIN
1343 IEX_TERR_WINNERS_PUB.Print_Debug('--- Attemp No: '||l_attempts);
1344 OPEN c_MissedProfiles(p_worker_id);
1345 FETCH c_MissedProfiles BULK COLLECT INTO l_missCustomer,
1346 l_missSiteUse, l_missAccount LIMIT l_max_fetches;
1347 CLOSE c_MissedProfiles;
1348 l_attempts := 3;
1349 l_exceptions := FALSE;
1350 EXCEPTION
1351 WHEN Others THEN
1352 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || SQLERRM);
1353 l_attempts := l_attempts +1;
1354 l_exceptions := TRUE;
1355 if c_MissedProfiles%ISOPEN then
1356 CLOSE c_MissedProfiles;
1357 end if;
1358 if l_attempts > 2 then
1359 x_errbuf := SQLERRM;
1360 x_retcode := SQLCODE;
1361 RAISE;
1362 end if;
1363 END;
1364 END LOOP; -- End Bulk read Party list. attempts < 3
1365 IEX_TERR_WINNERS_PUB.Print_Debug('--- Select Parties with no profiles.End-Attempts: '||l_attempts);
1366
1367 -- Initialize variables
1368 if l_missCustomer.count < l_max_fetches then
1369 l_limit_flag := TRUE;
1370 end if;
1371
1372 IEX_TERR_WINNERS_PUB.Print_Debug('--- Start. Create = . ' || l_missCustomer.count);
1373
1374 IF l_missCustomer.count > 0 THEN -- if Missed Customer Profiles .count > 0
1375
1376 l_attempts := 1;
1377 IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Flag Loop -----');
1378
1379 WHILE l_attempts < 3 LOOP /* Update While loop; l_attempts < 3 */
1380 BEGIN
1381
1382 IEX_TERR_WINNERS_PUB.Print_Debug('Inside IF, --- While Attempts Loop -----' || l_attempts);
1383
1384 FOR i in 1 .. l_missCustomer.count LOOP
1385 BEGIN
1386 IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1387
1388 l_old_customer_profile_rec.party_id := l_missCustomer(i);
1389 l_old_customer_profile_rec.cust_account_id := l_missAccount(i);
1390 l_old_customer_profile_rec.site_use_id := l_missSiteUse(i);
1391 l_old_customer_profile_rec.created_by_module := 'IEX';
1392
1393 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
1394 p_customer_profile_rec => l_old_customer_profile_rec,
1395 x_cust_account_profile_id => l_customer_profile_id,
1396 x_return_status => l_return_status,
1397 x_msg_count => l_msg_count,
1398 x_msg_data => l_msg_data);
1399
1400 IEX_TERR_WINNERS_PUB.Print_Debug(' Return data after create profile API ' || l_return_status || ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1401 IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1402
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error while selecting resource/groupname' );
1406 END;
1407 COMMIT;
1408 END LOOP;
1409 l_attempts := 3;
1410 IEX_TERR_WINNERS_PUB.Print_Debug('Records Updated: ' || l_first || '-'|| l_last);
1411 EXCEPTION
1412 WHEN deadlock_detected THEN
1413 begin
1414 IEX_TERR_WINNERS_PUB.Print_Debug('Deadlock encountered during bulk update-'||l_attempts);
1415 rollback;
1416 l_attempts := l_attempts +1;
1417 if l_attempts = 3 then
1418 FOR i in 1 .. l_missCustomer.count LOOP /*Inside deadlock detected loop */
1419 BEGIN
1420
1421 IEX_TERR_WINNERS_PUB.Print_Debug('Creating profile for Customer ID = '|| l_missCustomer(i) );
1422
1423 l_old_customer_profile_rec.party_id := l_missCustomer(i);
1424 l_old_customer_profile_rec.site_use_id := l_missSiteUse(i);
1425 l_old_customer_profile_rec.created_by_module := 'IEX';
1426 l_old_customer_profile_rec.cust_account_id := l_missAccount(i);
1427
1428 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
1429 p_customer_profile_rec => l_old_customer_profile_rec,
1430 x_cust_account_profile_id => l_customer_profile_id,
1431 x_return_status => l_return_status,
1432 x_msg_count => l_msg_count,
1433 x_msg_data => l_msg_data);
1434
1435 IEX_TERR_WINNERS_PUB.Print_Debug(' Return after create profile API ' || l_return_status ||
1436 ' msg_count ' || l_msg_count || ' msg_data ' || l_msg_data);
1437 IEX_TERR_WINNERS_PUB.Print_Debug(' Created Profile Id ' || l_customer_profile_id);
1438
1439 EXCEPTION
1440 WHEN OTHERS THEN
1441 IEX_TERR_WINNERS_PUB.Print_Debug('Others Exception Profile creation API update');
1442 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1443 ' SQLERRM: ' || SQLERRM);
1444 END;
1445 END LOOP; /* End Inside deadlock detected loop */
1446 COMMIT;
1447 end if;
1448 end; -- end of deadlock exception
1449
1450 WHEN OTHERS THEN
1451 IEX_TERR_WINNERS_PUB.Print_Debug('Exception : In others');
1452 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1453 ' SQLERRM: ' || SQLERRM);
1454 x_errbuf := SQLERRM;
1455 x_retcode := SQLCODE;
1456 RAISE;
1457 END;
1458 END LOOP; /* Update While loop; l_attempts < 3 */
1459
1460 END IF; --l_missCustomer .count > 0
1461 IEX_TERR_WINNERS_PUB.Print_Debug('---Create SiteUse Profile.End-'|| l_missCustomer.count||' Rows Updated.');
1462 --------------------------------
1463 END LOOP; -- End Bulk read non-existent Customer Profiles attempts < 3
1464
1465 EXCEPTION
1466 WHEN others THEN
1467 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_PROCESS_ACCOUNT_RECORDS::CreatePartyProfiles.');
1468 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
1469 ' SQLERRM: ' || SQLERRM);
1470 x_errbuf := SQLERRM;
1471 x_retcode := SQLCODE;
1472 RAISE;
1473 END CreateSiteUseProfiles;
1474
1475 END IEX_PROCESS_ACCOUNT_WINNERS;