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