[Home] [Help]
PACKAGE BODY: APPS.IEX_GAR_PUB
Source
1 PACKAGE BODY IEX_GAR_PUB AS
2 /* $Header: iextptwb.pls 120.5 2006/05/17 06:15:44 lkkumar noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Start of Comments
6 ---------------------------------------------------------------------------
7 -- PACKAGE NAME: IEX_GAR_PUB
8 -- ---------------------------------------------------------------------
9 -- PURPOSE
10 --
11 -- Main Package for the concurrent program
12 -- "Generate Access Records".
13 --
14 -- Procedures:
15 -- (see below for specification)
16 --
17 -- NOTES
18 -- This package to be called from the concurrent program
19 -- "Generate Access Records"
20 --
21 -- HISTORY
22 ---------------------------------------------------------------------------
23
24
25 /*-------------------------------------------------------------------------+
26 | PRIVATE CONSTANTS
27 +-------------------------------------------------------------------------*/
28 G_PKG_NAME CONSTANT VARCHAR2(30):='IEX_GAR_PUB';
29 G_FILE_NAME CONSTANT VARCHAR2(12):='iextptwb.pls';
30 G_SORT_AREA_SIZE CONSTANT NUMBER := 100000000;
31 G_HASH_AREA_SIZE CONSTANT NUMBER := 100000000;
32 G_CURSOR_LIMIT CONSTANT NUMBER := 10000;
33 -- for BES enhancement
34 G_BUSINESS_EVENT CONSTANT VARCHAR2(60) := 'oracle.apps.as.tap.batch_mode';
35
36
37 /*-------------------------------------------------------------------------+
38 | PRIVATE DATATYPES
39 +-------------------------------------------------------------------------*/
40
41 /*-------------------------------------------------------------------------*
42 | PRIVATE VARIABLES
43 *-------------------------------------------------------------------------*/
44 g_run_mode VARCHAR2(7);
45
46 /*-------------------------------------------------------------------------*
47 | PRIVATE ROUTINES SPECIFICATION
48 *-------------------------------------------------------------------------*/
49 PROCEDURE Init(
50 p_run_mode IN VARCHAR2,
51 p_debug_mode IN VARCHAR2,
52 p_trace_mode IN VARCHAR2,
53 p_transaction_type IN VARCHAR2,
54 p_worker_id IN VARCHAR2,
55 p_actual_workers IN VARCHAR2,
56 p_prev_request_id IN NUMBER,
57 p_seq_num IN NUMBER,
58 px_terr_globals IN OUT NOCOPY IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
59 FUNCTION exist_subscription(p_event_name IN VARCHAR2) return VARCHAR2;
60 PROCEDURE RAISE_BES(p_terr_globals IN OUT NOCOPY IEX_TERR_WINNERS_PUB.TERR_GLOBALS);
61
62
63
64 /*------------------------------------------------------------------------*
65 | PUBLIC ROUTINES
66 *------------------------------------------------------------------------*/
67
68 /*-------------------------------------------------------------------------*
69 | PUBLIC ROUTINE
70 | Generate_Access_Records
71 |
72 | PURPOSE
73 | The main procedure of the concurrent program
74 |
75 | NOTES
76 |
77 |
78 | HISTORY
79 *-------------------------------------------------------------------------*/
80 PROCEDURE Generate_Access_Records(
81 errbuf OUT NOCOPY VARCHAR2,
82 retcode OUT NOCOPY VARCHAR2,
83 p_run_mode IN VARCHAR2,
84 p_debug_mode IN VARCHAR2,
85 p_trace_mode IN VARCHAR2,
86 p_transaction_type IN VARCHAR2,
87 p_worker_id IN VARCHAR2,
88 p_actual_workers IN VARCHAR2,
89 p_prev_request_id IN NUMBER,
90 p_seq_num IN NUMBER)
91 IS
92 l_terr_globals IEX_TERR_WINNERS_PUB.TERR_GLOBALS;
93 l_status BOOLEAN;
94 l_return_status VARCHAR2(1);
95 l_msg_count NUMBER;
96 l_msg_data VARCHAR2(2000);
97 l_errbuf VARCHAR2(4000);
98 l_retcode VARCHAR2(255);
99 l_dyn_str VARCHAR2(255);
100 l_temp NUMBER;
101 l_sub_exist VARCHAR2(1); -- to check where subscription exists
102
103 -- ffang 110703, enh2737659, New new mode TAP
104 l_target_type VARCHAR2(15) := '';
105 -- end ffang 110703, enh2737659
106
107 l_wincount NUMBER;
108
109 l_percent_analysed NUMBER;
110 l_acc_count NUMBER;
111 l_org_id NUMBER;
112
113
114 BEGIN
115
116 g_debug_flag := p_debug_mode;
117 IEX_TERR_WINNERS_PUB.g_debug_flag := p_debug_mode;
118 BEGIN
119 SELECT org_id INTO L_ORG_ID
120 FROM fnd_concurrent_requests
121 WHERE request_id=p_prev_request_id;
122 EXCEPTION WHEN OTHERS THEN
123 l_org_id := NULL;
124 END;
125
126 IEX_TERR_WINNERS_PUB.Print_Debug('*** Starting iextptwb.pls::Generate_Access_Records ***');
127 --Bug5043777. Fix By LKKUMAR. Start.
128 MO_GLOBAL.INIT('IEX');
129 IF (l_org_id IS NOT NULL) THEN
130 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
131 ELSE
132 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
133 END IF;
134
135 IF (MO_GLOBAL.GET_CURRENT_ORG_ID IS NULL) THEN
136 IEX_TERR_WINNERS_PUB.Print_Debug('Operating Unit Set : ' || 'All');
137 ELSE
138 IEX_TERR_WINNERS_PUB.Print_Debug('Operating Unit Set : ' || MO_GLOBAL.GET_OU_NAME(MO_GLOBAL.GET_CURRENT_ORG_ID));
139 END IF;
140 --Bug5043777. Fix By LKKUMAR. End.
141
142
143 l_percent_analysed :=nvl(TO_NUMBER(fnd_profile.value('IEX_TAP_PERCENT_ANALYSED')),20);
144 IF p_run_mode = IEX_ATA_PUB.G_TOTAL_MODE THEN
145 l_target_type := 'TOTAL';
146 ELSIF p_run_mode = IEX_ATA_PUB.G_NEW_MODE THEN
147 l_target_type := 'INCREMENTAL';
148 END If;
149
150
151 -- Set the Global variables
152 Init(
153 p_run_mode,
154 p_debug_mode,
155 p_trace_mode,
156 p_transaction_type,
157 p_worker_id,
158 p_actual_workers,
159 p_prev_request_id,
160 p_seq_num,
161 l_terr_globals);
162
163 COMMIT;
164
165 --
166
167 BEGIN
168 IEX_TERR_WINNERS_PUB.Print_Debug('Starting JTY_ASSIGN_BULK_PUB.get_winners for worker '|| p_worker_id);
169
170 JTY_ASSIGN_BULK_PUB.get_winners(
171 p_api_version_number => 1.0,
172 p_init_msg_list => FND_API.G_FALSE,
173 p_source_id => -1600,
174 p_trans_id => -1601,
175 p_program_name => 'COLLECTIONS/CUSTOMER PROGRAM',
176 p_mode => l_target_type,
177 p_percent_analyzed => l_percent_analysed,
178 p_worker_id => p_worker_id, -- the worker_id
179 x_return_status => l_return_status,
180 x_msg_count => l_msg_count,
181 x_msg_data => l_msg_data,
182 ERRBUF => l_errbuf,
183 RETCODE => l_retcode
184 );
185
186 IEX_TERR_WINNERS_PUB.Print_Debug('Completed JTY_ASSIGN_BULK_PUB.get_winners for worker '
187 || p_worker_id || ' with status ' || l_return_status);
188
189 IEX_TERR_WINNERS_PUB.Print_Debug('Message from JTY_ASSIGN_BULK_PUB.get_winners for worker ' ||
190 l_msg_data);
191
192 COMMIT;
193 EXCEPTION WHEN OTHERS THEN
194 IEX_TERR_WINNERS_PUB.Print_Debug('Error in JTY_ASSIGN_BULK_PUB.get_winners for worker '|| p_worker_id ||
195 ' '|| SQLERRM);
196 IEX_TERR_WINNERS_PUB.Print_Debug('Error buffer ' || l_errbuf);
197 END;
198
199 SELECT count(*) INTO l_acc_count FROM jtf_tae_1600_cust_winners;
200 IEX_TERR_WINNERS_PUB.Print_Debug('Number of records in jtf_tae_1600_cust_winners : ' || l_acc_count );
201
202
203 --
204
205 -- Set the Session parameters
206 IEX_ATA_PUB.Set_Area_Sizes;
207
208 /* Nothing is done for pre-cleaning. kasreeni 4/20/2005 */
209
210 -- Pre-cleaning of AS_ACCESSES_ALL and AS_TERRITORY_ACCESSES.
211 -- This is to be called if the Profile Option for Dup Res Deletion on address_id is enabled.
212 IF(l_terr_globals.enable_dups_rs_del = 'Y' ) THEN
213 IEX_TERR_ASSIGNMENT_CLEANUP.Cleanup_Duplicate_Resources(
214 x_errbuf => l_errbuf,
215 x_retcode => l_retcode,
216 p_terr_globals => l_terr_globals);
217 END IF;
218
219 IF l_terr_globals.transaction_type = 'ACCOUNT' THEN
220 IEX_PROCESS_ACCOUNT_WINNERS.Process_Account_Records(
221 x_errbuf => l_errbuf,
222 x_retcode => l_retcode,
223 p_terr_globals => l_terr_globals);
224 END IF;
225
226
227
228 /*
229 -- Cleanup AS_TERRITORY_ACCESSES Records. Overload this procedure if LEAD Processing Goes Away.
230 -- Cleanup Terr Accesses for Lead, if the Lead Processing is Enabled-AS_DISABLE_BATCH_LEAD_TERR_ASSIGNMENT
231 IEX_TERR_ASSIGNMENT_CLEANUP.Cleanup_Terrritory_Accesses(
232 x_errbuf => l_errbuf,
233 x_retcode => l_retcode,
234 p_terr_globals => l_terr_globals);
235
236 -- Delete Unqualified Access Records for Account,Oppot,Lead.
237 IF l_terr_globals.transaction_type = 'ACCOUNT' THEN
238 -- ffang 110703, enh2737659, New new mode TAP
239 IEX_TERR_WINNERS_PUB.Print_Debug('Clean up for Accounts');
240 IF p_run_mode = IEX_ATA_PUB.G_TOTAL_MODE THEN
241 IEX_TERR_ASSIGNMENT_CLEANUP.Perform_Account_Cleanup(
242 x_errbuf => l_errbuf,
243 x_retcode => l_retcode,
244 p_terr_globals => l_terr_globals);
245 END IF;
246 -- end ffang 110703, enh 2737659
247
248 -- BES enhancement
249
250 /** Start Bug 4419234 07/08/2005 No business events for H release **/
251 /**
252 l_sub_exist := iex_gar_pub.exist_subscription(G_BUSINESS_EVENT);
253
254 IF ((l_return_status = 'S') AND (l_sub_exist = 'Y')) THEN
255
256 IEX_TERR_WINNERS_PUB.Print_Debug('--- Event subscription exists... ');
257 IEX_TERR_WINNERS_PUB.Print_Debug('--- Event subscription raised from Account... ');
258
259 RAISE_BES(l_terr_globals);
260
261
262 END If;
263 **/
264
265 /** End Bug 4419234 07/08/2005 No business events for H release **/
266
267 EXCEPTION
268 WHEN others THEN
269 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_GAR_PUB::Generate_Access_Records');
270 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || SQLERRM );
271 errbuf := SQLERRM;
272 retcode := SQLCODE;
273 --retcode := FND_API.G_RET_STS_UNEXP_ERROR;
274 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
275 END Generate_Access_Records;
276
277 /*-------------------------------------------------------------------------*
278 | PRIVATE ROUTINE
279 | Init
280 |
281 | PURPOSE
282 | Initialize the global session variables
283 |
284 | NOTES
285 |
286 | HISTORY
287 |
288 *-------------------------------------------------------------------------*/
289
290
291 PROCEDURE Init(
292 p_run_mode IN VARCHAR2,
293 p_debug_mode IN VARCHAR2,
294 p_trace_mode IN VARCHAR2,
295 p_transaction_type IN VARCHAR2,
296 p_worker_id IN VARCHAR2,
297 p_actual_workers IN VARCHAR2,
298 p_prev_request_id IN NUMBER,
299 p_seq_num IN NUMBER,
300 px_terr_globals IN OUT NOCOPY IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
301 IS
302 l_ata_request_id_of_failed_gar number := -1;
303 l_temp varchar(300) ;
304
305 BEGIN
306
307
308 -- Set the Global variables
309
310 px_terr_globals.debug_flag := p_debug_mode;
311 px_terr_globals.run_mode := p_run_mode;
312 px_terr_globals.transaction_type := p_transaction_type;
313 px_terr_globals.worker_id:=p_worker_id;
314
315
316 -- For Restart Mode, the prev_request_id is not the CURRENT ATA Request_id,
317 -- Rather it the request_id of the Failed GAR's ATA Request_id and so determine
318 -- the MODE and assign the value for px_terr_globals.prev_request_id accordingly.
319 -- Note: the Restart is applicable for the Failed GAR ONLY.
320 --px_terr_globals.prev_request_id := p_prev_request_id;
321
322 px_terr_globals.sequence := p_seq_num;
323
324 px_terr_globals.bulk_size:= nvl(to_number(fnd_profile.value('AS_BULK_COMMIT_SIZE')),10000);
325
326 /*** Not needed now
327 if px_terr_globals.bulk_size < 10000 then
328 px_terr_globals.bulk_size:= 10000;
329 end if;
330 **/
331
332 IF p_trace_mode = 'Y'
333 THEN
334 l_temp := 'alter session set events = ''10046 trace name context forever, level 8'' ';
335 EXECUTE IMMEDIATE l_temp;
336 END IF;
337
338 begin
339 px_terr_globals.cursor_limit := nvl(to_number(fnd_profile.value('AS_TERR_RECORDS_TO_OPEN')) ,G_CURSOR_LIMIT);
340 if px_terr_globals.cursor_limit < 1 then
341 px_terr_globals.cursor_limit := G_CURSOR_LIMIT;
342 end if;
343 exception
344 when others then
345 px_terr_globals.cursor_limit := G_CURSOR_LIMIT;
346 end;
347
348
349 -- get profile option - USER_ID
350 px_terr_globals.user_id := to_number(fnd_profile.value('USER_ID'));
351
352 px_terr_globals.prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
353
354 -- get profile option -- CONC_PROGRAM_ID
355 px_terr_globals.prog_id := to_number(fnd_profile.value('CONC_PROGRAM_ID'));
356
357 -- get profile option -- CONC_LOGIN_ID
358 px_terr_globals.last_update_login := to_number(fnd_profile.value('CONC_LOGIN_ID'));
359
360 -- get profile option -- CONC_REQUEST_ID
361 px_terr_globals.request_id := to_number(fnd_profile.value('CONC_REQUEST_ID'));
362
363 -- If request_id = 0, select directly from sequence
364 IF px_terr_globals.request_id = 0 OR px_terr_globals.request_id IS NULL
365 THEN
366 -- Get concurrent sequence
367 IEX_TERR_WINNERS_PUB.Print_Debug('request_id is 0, get from sequence');
368 SELECT fnd_concurrent_requests_s.nextval into px_terr_globals.request_id from dual;
369 END IF;
370
371 -- Get the profile option for AS_ENABLE_DUPS_RS_DELETION
372 -- OS: Enable Duplicate Resource Deletion
373 -- For Now this is asssumed to be 'Y'
374 px_terr_globals.enable_dups_rs_del := 'Y';
375 px_terr_globals.disable_lead_processing := FND_PROFILE.Value('AS_DISABLE_BATCH_LEAD_TERR_ASSIGNMENT') ;
376
377 IF p_run_mode = 'RESTART' THEN
378 --Fetch the p_prev_request_id from JTF_TAE_1001_ACCOUNT_TRANS
379 begin
380 select REQUEST_ID into l_ata_request_id_of_failed_gar from jtf_tae_1001_account_trans
381 where request_id is not null and rownum < 2 ;
382 exception
383 when others then
384 IEX_TERR_WINNERS_PUB.Print_Debug('Cannot restart- JTF_TAE_1001_ACCOUNT_TRANS is Empty');
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 end;
387 px_terr_globals.prev_request_id := l_ata_request_id_of_failed_gar;
388 ELSE
389 px_terr_globals.prev_request_id := p_prev_request_id;
390 END IF;
391
392
393 -- Print the Global variables
394
395 COMMIT;
396
397 EXCEPTION
398 WHEN others THEN
399 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in AS_GAR_PUB::Init');
400 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE ' || to_char(SQLCODE) ||
401 ' SQLERRM ' || SQLERRM);
402 RAISE;
403 END Init;
404
405 /*-------------------------------------------------------------------------*
406 | PRIVATE ROUTINE
407 | exist_subscription
408 |
409 | PURPOSE
410 | The purpose of this function is to check if a subscription exists for a
411 | given event. Returns 'Y' if the subscription exist.
412 |
413 | NOTES
414 |
415 | HISTORY
416 *-------------------------------------------------------------------------*/
417
418 FUNCTION exist_subscription(p_event_name IN VARCHAR2) RETURN VARCHAR2
419
420 IS
421 --Bug4930397. Commented this as we don't have events now. Fix by LKKUMAR ON 10-JAN-2006. Start.
422 /*
423 CURSOR c1 IS
424 SELECT count(*)
425 FROM wf_events eve,
426 wf_event_subscriptions sub
427 WHERE eve.name = p_event_name
428 AND eve.status = 'ENABLED'
429 AND eve.guid = sub.event_filter_guid
430 AND sub.status = 'ENABLED'
431 AND sub.source_type = 'LOCAL';
432
433
434 l_count NUMBER;
435 l_yn VARCHAR2(1);
436
437 BEGIN
438
439 open c1;
440
441 fetch c1 into l_count;
442
443 if l_count > 0 then
444 l_yn := 'Y';
445 end if;
446
447 close c1;
448 RETURN l_yn;
449 */ --Bug4930397. Commented this as we don't have events now. Fix by LKKUMAR ON 10-JAN-2006. End.
450
451
452 BEGIN
453
454 RETURN ('N');
455 /*Bug4930397. Function Should return a value So, returning 'N'. We Shall remove this once we have
456 decided to have events for terrirory assignment*/
457
458
459 END exist_subscription;
460
461 /*-------------------------------------------------------------------------*
462 | PRIVATE ROUTINE
463 | RAISE_BES
464 |
465 | PURPOSE
466 | Raises the event
467 |
468 | NOTES
469 |
470 | HISTORY
471 *-------------------------------------------------------------------------*/
472
473 PROCEDURE RAISE_BES(p_terr_globals IN OUT NOCOPY IEX_TERR_WINNERS_PUB.TERR_GLOBALS) IS
474
475 l_mode VARCHAR2(7);
476 l_request_id NUMBER;
477 l_ata_request_id NUMBER;
478 l_worker_id NUMBER;
479 l_transaction_type VARCHAR2(30);
480 l_total_num_gar_workers NUMBER;
481 l_event_id NUMBER;
482 x_errbuf VARCHAR2(4000);
483 x_retcode VARCHAR2(4000);
484 l_param_list wf_parameter_list_t;
485 l_event_key VARCHAR2(100);
486 l_msg VARCHAR2(4000);
487
488 BEGIN
489 /** Start Bug 4419234 07/08/2005 No business events for H release **/
490 /** nullifying the code
491 l_mode := p_terr_globals.run_mode;
492 l_request_id := p_terr_globals.request_id;
493 l_ata_request_id := p_terr_globals.prev_request_id;
494 l_worker_id := p_terr_globals.worker_id;
495 l_transaction_type := p_terr_globals.transaction_type;
496
497 SELECT count(*) into l_total_num_gar_workers
498 FROM fnd_concurrent_requests
499 WHERE parent_request_id = l_ata_request_id;
500
501 WF_EVENT.AddParameterToList(
502 p_name => 'RUN_MODE',
503 p_value => l_mode,
504 p_parameterlist => l_param_list);
505
506 WF_EVENT.AddParameterToList(
507 p_name => 'REQUEST_ID',
508 p_value => l_request_id,
509 p_parameterlist => l_param_list);
510
511 WF_EVENT.AddParameterToList(
512 p_name => 'ATA_REQ_ID',
513 p_value => l_ata_request_id,
514 p_parameterlist => l_param_list);
515
516 WF_EVENT.AddParameterToList(
517 p_name => 'WORKER_ID',
518 p_value => l_worker_id,
519 p_parameterlist => l_param_list);
520
521 WF_EVENT.AddParameterToList(
522 p_name => 'TRANS_TYPE',
523 p_value => l_transaction_type,
524 p_parameterlist => l_param_list);
525
526 WF_EVENT.AddParameterToList(
527 p_name => 'TOTAL_WORKERS',
528 p_value => l_total_num_gar_workers,
529 p_parameterlist => l_param_list);
530
531 begin
532
533 SELECT AS_BUSINESS_EVENT_S.nextval INTO l_event_id FROM dual;
534
535 IEX_TERR_WINNERS_PUB.Print_Debug(' --- CALL WF_EVENT.RAISE.Start...');
536 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_event_id = '||l_event_id);
537 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_mode = '||l_mode);
538 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_request_id = '||l_request_id);
539 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_ata_request_id = '||l_ata_request_id);
540 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_worker_id = '||l_worker_id);
541 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_transaction_type = '||l_transaction_type);
542 IEX_TERR_WINNERS_PUB.Print_Debug(' --- l_total_num_gar_workers = '||l_total_num_gar_workers);
543
544
545 wf_event.raise (
546 p_event_name => G_BUSINESS_EVENT,
547 p_event_key => l_event_id,
548 p_parameters => l_param_list);
549
550 EXCEPTION
551
552 WHEN others THEN
553
554 x_errbuf := SQLERRM;
555 x_retcode := SQLCODE;
556
557 IEX_TERR_WINNERS_PUB.Print_Debug(' --- x_errbuf = '||x_errbuf||' , x_retcode = '||x_retcode);
558
559 end;
560
561 l_param_list.DELETE;
562 IEX_TERR_WINNERS_PUB.Print_Debug(' --- CALL WF_EVENT.RAISE.End...');
563
564 **/
565
566 null;
567
568 /** End Bug 4419234 07/08/2005 No business events for H release **/
569
570 END RAISE_BES;
571
572 END IEX_GAR_PUB;