[Home] [Help]
PACKAGE BODY: APPS.IEX_ATA_PUB
Source
1 PACKAGE BODY IEX_ATA_PUB as
2 /* $Header: iextpinb.pls 120.22 2011/09/09 12:05:58 ehuh noship $ */
3 PROCEDURE Set_Up(
4 px_acct_qual_tbl IN OUT NOCOPY QUAL_LIST_TBL_TYPE);
5
6 PROCEDURE Concurrent_Profile_Options;
7
8 PROCEDURE Prepare_Parallel_Processing(
9 P_Request_Id NUMBER,
10 P_Prev_Request_Id NUMBER,
11 P_Run_Mode VARCHAR2,
12 P_AccountCount NUMBER,
13 P_MinNumParallelProc NUMBER,
14 P_NumChildAccountWorker NUMBER,
15 X_ActualAccountWorkersUsed OUT NOCOPY NUMBER);
16
17
18 /*-------------------------------------------------------------------------*
19 |
20 | PUBLIC ROUTINES
21 |
22 *-------------------------------------------------------------------------*/
23
24
25 /*-------------------------------------------------------------------------*
26 | PRIVATE ROUTINE
27 | Assign_Territory_Accesses
28 |
29 | PURPOSE
30 |
31 |
32 *-------------------------------------------------------------------------*/
33
34 PROCEDURE Assign_Territory_Accesses(
35 ERRBUF OUT NOCOPY VARCHAR2,
36 RETCODE OUT NOCOPY VARCHAR2,
37 P_ORG_ID IN NUMBER,
38 P_STR_LEVEL IN VARCHAR2 -- added for bug 8708291 multi level strategy
39 )
40 --Bug5043777 Removed the parameters which is no longer in use.
41 IS
42 p_debug_mode VARCHAR2(240);
43 p_trace_mode VARCHAR2(240);
44 p_prev_request_id NUMBER(24);
45 p_ext_param1 VARCHAR2(240);
46 p_ext_param2 VARCHAR2(240);
47 p_ext_param3 VARCHAR2(240);
48
49 p_trans_type_acc CONSTANT VARCHAR2(30) := 'ACCOUNT';
50
51 -- account qualifiers array of structure
52 l_acct_qual_tbl QUAL_LIST_TBL_TYPE;
53 l_req_id NUMBER;
54 l_msg VARCHAR2(2000);
55 l_number NUMBER := 1;
56 l_status BOOLEAN;
57 p_ActualAccountWorkersUsed NUMBER :=0;
58 l_call_pre_uhk BOOLEAN;
59 l_call_post_uhk BOOLEAN;
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2(2000);
62 l_acc_count NUMBER := 0;
63 l_return_status VARCHAR2(30);
64 lX_Msg_Count NUMBER;
65 lX_Msg_Data VARCHAR2(32767);
66 lx_retcode VARCHAR2(100);
67 lx_errbuf VARCHAR2(32767);
68 l_temp VARCHAR2(3000);
69
70 --l_terr_globals AS_TERR_WINNERS_PUB.TERR_GLOBALS;
71 l_errbuf VARCHAR2(4000);
72 l_retcode VARCHAR2(255);
73 l_target_type VARCHAR2(50);
74
75 l_percent_analysed NUMBER(15);
76
77 l_debug NUMBER(15);
78 l_AssignLevel VARCHAR2(20); -- Added by gnramasa on 29/08/2006 for bug # 5487449
79 l_date_str VARCHAR2(255);
80 --Begin Bug 7697167 27-Jan-2009 barathsr
81 l_req_id_lst IEX_UTILITIES.t_numbers;
82 cnt number:=0;--23/01
83 uphase VARCHAR2(255);
84 dphase VARCHAR2(255);
85 ustatus VARCHAR2(255);
86 dstatus VARCHAR2(255);
87 l_bool BOOLEAN;
88 message VARCHAR2(32000);
89 --End Bug 7697167 27-Jan-2009 barathsr
90 l_str_level_count number; -- Added for bug 8708291 pnaveenk multi level strategy
91 l_str_status varchar2(1); -- Added for bug 8708291 pnaveenk multi level strategy
92 BEGIN
93 --Bug5043777 Removed the parameters which is no longer in use. Fix By LKKUMAR. Start.
94 MO_GLOBAL.INIT('IEX');
95
96 IF (P_ORG_ID) IS NULL THEN
97 MO_GLOBAL.SET_POLICY_CONTEXT('M',NULL);
98 ELSE
99 MO_GLOBAL.SET_POLICY_CONTEXT('S',P_ORG_ID);
100 END IF;
101
102 IEX_DEBUG('Program iextpinb.pls : *** IEX_ATA_PUB.Assign_Territory_Access starts ***');
103 FND_FILE.PUT_LINE(FND_FILE.LOG,'Program iextpinb.pls : *** IEX_ATA_PUB.Asssign_Territory_Access starts ***');
104 FND_FILE.put_line(fnd_file.log, ' Strategy Level' || P_STR_LEVEL);
105 l_debug := NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'),20);
106 FND_FILE.put_line(fnd_file.log,'Value of Profile IEX: Debug Level(IEX_DEBUG_LEVEL) is : ' || l_debug);
107
108 IF (MO_GLOBAL.GET_CURRENT_ORG_ID IS NULL) THEN
109 FND_FILE.PUT_LINE(FND_FILE.LOG,'Operating Unit Set : ' || 'All');
110 ELSE
111 FND_FILE.PUT_LINE(FND_FILE.LOG,'Operating Unit Set : ' || MO_GLOBAL.GET_CURRENT_ORG_ID);
112 END IF;
113
114 --Start changes by gnramasa on 29/08/2006 for bug # 5487449
115 l_Assignlevel:= NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
116 FND_FILE.PUT_LINE(FND_FILE.LOG,'Territory Assignment Level, Profile IEX: Territory Access Level(IEX_ACCESS_LEVEL) := ' ||l_Assignlevel);
117
118 -- Start for bug 8708291 pnaveenk
119 /* If iex_utilities.validate_running_level(l_Assignlevel) <> 'Y' then
120 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Territory Assignment Level at Profile IEX: Territory Level not exists in the Strategy Level set up');
121 End if; */
122
123 Begin
124
125 SELECT count(*) into l_str_level_count
126 FROM IEX_LOOKUPS_V
127 WHERE LOOKUP_TYPE='IEX_RUNNING_LEVEL'
128 AND iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
129
130 select iex_utilities.validate_running_level(decode(P_STR_LEVEL,'PARTY','CUSTOMER','ACCOUNT','ACCOUNT','BILLTOSITE','BILL_TO','BILL_TO'))
131 into l_str_status
132 from dual;
133
134 If l_str_level_count > 1 then
135 If P_STR_LEVEL is null then
136 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Multiple Strategy Levels being used.');
137 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Please select the value for Parameter Territory Level');
138 return;
139 end if;
140
141 if l_str_status = 'Y' then
142 l_Assignlevel := P_STR_LEVEL;
143 else
144 FND_FILE.put_line(fnd_file.log, p_str_level || ' is not a valid level');
145 return;
146 end if;
147 end if;
148
149 Exception
150 When Others then
151 IEX_DEBUG(' Exception in finding strategy levels count');
152 End;
153
154 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Territory Running Level is ' || l_Assignlevel);
155 -- End for bug 8708291 pnaveenk
156
157
158 IF (l_debug <10) THEN
159 P_debug_mode := 'Y';
160 FND_FILE.PUT_LINE(FND_FILE.LOG,'Debug is Enabled , IEX: Debug Level(IEX_DEBUG_LEVEL) :' || l_debug);
161 ELSE
162 P_debug_mode := 'N';
163 FND_FILE.PUT_LINE(FND_FILE.LOG,'Debug is not Enabled , Profile IEX: Debug Level(IEX_DEBUG_LEVEL) : ' || l_debug || '. Please set this profile value < 10 to enable debug ');
164 END IF;
165
166 IF (l_debug =1) THEN
167 P_trace_mode := 'Y';
168 ELSE
169 P_trace_mode := 'N';
170 END IF;
171
172 G_debug_flag := P_debug_mode;
173 G_trace_mode := P_trace_mode;
174
175 IF p_trace_mode = 'Y' THEN
176 l_temp := 'alter session set events = ''10046 trace name context forever, level 8'' ';
177 EXECUTE IMMEDIATE l_temp;
178 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL Trace is Enabled , IEX: Debug Level(IEX_DEBUG_LEVEL) = 1');
179 ELSE
180 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQL Trace is not Enabled , IEX: Debug Level(IEX_DEBUG_LEVEL) <> 1');
181 END IF;
182 --End changes by gnramasa on 29/08/2006 for bug # 5487449
183 --Bug5043777 Removed the parameters which is no longer in use. Fix By LKKUMAR. End.
184
185 iex_ata_pub.g_debug_flag := p_debug_mode;
186
187
188 l_call_pre_uhk := JTF_USR_HKS.Ok_to_execute('IEX_ATA_PUB',
189 'Assign_Territory_Accesses',
190 'B','C');
191 IF l_call_pre_uhk THEN
192 IEX_DEBUG('Call pre user hook is true');
193 AS_ATA_UHK.ATA_Pre (
194 p_api_version_number => 2.0,
195 p_init_msg_list => FND_API.G_FALSE,
196 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
197 p_commit => FND_API.G_FALSE,
198 p_param1 => p_ext_param1,
199 p_param2 => p_ext_param2,
200 p_param3 => p_ext_param3,
201 x_return_status => l_return_status,
202 x_msg_count => l_msg_count,
203 x_msg_data => l_msg_data);
204 END IF;
205
206 -- call SetUp() to verify parameters and set global variables and arrays
207 Set_Up(l_acct_qual_tbl);
208
209 g_request_id := TO_NUMBER(fnd_profile.value('CONC_REQUEST_ID'));
210 g_debug_flag := p_debug_mode;
211 g_run_mode := 'TOTAL'; --Bug5043777
212 g_prev_request_id := p_prev_request_id;
213
214
215 Set_Area_Sizes;
216 COMMIT;
217 /*
218 IF g_run_mode = G_TOTAL_MODE THEN
219 IEX_DEBUG('Calling IEX_ATA_TOTAL.Load_All');
220 IEX_ATA_TOTAL.Load_All(
221 g_user_id, g_last_update_login, g_prog_appl_id, g_prog_id,
222 g_request_id, g_num_rollup_days, g_conversion_type);
223 END IF;
224 */
225 l_call_post_uhk := JTF_USR_HKS.Ok_to_execute('IEX_ATA_PUB',
226 'Assign_Territory_Accesses',
227 'A','C');
228
229 IF l_call_post_uhk THEN
230 IEX_DEBUG('Call post user hook is true');
231
232 AS_ATA_UHK.ATA_Post (
233 p_api_version_number => 2.0,
234 p_init_msg_list => FND_API.G_FALSE,
235 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
236 p_commit => FND_API.G_FALSE,
237 p_param1 => p_ext_param1,
238 p_param2 => p_ext_param2,
239 p_param3 => p_ext_param3,
240 p_request_id => g_request_id,
241 x_return_status => l_return_status,
242 x_msg_count => l_msg_count,
243 x_msg_data => l_msg_data);
244
245 IEX_DEBUG('user hook return: ' || l_return_status);
246 END IF;
247
248 g_min_num_parallel_proc:=nvl(TO_NUMBER(fnd_profile.value('IEX_TERR_MIN_NUM_PARALLEL_PROC')),1);
249 IEX_DEBUG('Min records for Parallel Processing (IEX_TERR_MIN_NUM_PARALLEL_PROC)=' || g_min_num_parallel_proc);
250 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Profile IEX: Territory Minimum Number of Records for Parallel Processing : ' || g_min_num_parallel_proc);
251
252 g_NumChildAccountWorker:=nvl(TO_NUMBER(fnd_profile.value('IEX_TAP_NUM_CHILD_ACCOUNT_WORKERS')),1);
253 FND_FILE.PUT_LINE(FND_FILE.LOG,'Profile IEX: Number of Child Account Workers for TAP : ' || g_NumChildAccountWorker);
254 if g_NumChildAccountWorker < 1 then
255 g_NumChildAccountWorker:=1;
256 elsif g_NumChildAccountWorker > 10 then
257 g_NumChildAccountWorker:=10;
258 IEX_DEBUG('Max no. of Parallel Account Workers allowed for TAP is:' || g_NumChildAccountWorker);
259 FND_FILE.PUT_LINE(FND_FILE.LOG,'Max no. of Parallel Account Workers allowed for TAP is : ' || g_NumChildAccountWorker);
260 end if;
261 IEX_DEBUG('Max Parallel Account Workers (IEX_TAP_NUM_CHILD_ACCOUNT_WORKERS)=' || g_NumChildAccountWorker);
262
263 l_percent_analysed :=nvl(TO_NUMBER(fnd_profile.value('IEX_TAP_PERCENT_ANALYSED')),20);
264 l_target_type := 'TOTAL';
265
266 BEGIN
267 IEX_DEBUG('Starting JTY_ASSIGN_BULK_PUB.collect_trans_data...');
268 select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
269 FND_FILE.PUT_LINE(FND_FILE.LOG,'---' || l_date_str || '--------------------------');
270 FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting JTY_ASSIGN_BULK_PUB.collect_trans_data...');
271
272 JTY_ASSIGN_BULK_PUB.collect_trans_data(
273 p_api_version_number => 1.0,
274 p_init_msg_list => FND_API.G_FALSE,
275 p_source_id => -1600,
276 p_trans_id => -1601,
277 p_program_name => 'COLLECTIONS/CUSTOMER PROGRAM',
278 p_mode => l_target_type,
279 p_where => null,
280 p_no_of_workers => g_NumChildAccountWorker,
281 p_percent_analyzed => l_percent_analysed,
282 p_request_id => g_request_id,
283 x_return_status => l_return_status,
284 x_msg_count => lx_msg_count,
285 x_msg_data => lx_msg_data,
286 ERRBUF => lx_errbuf,
287 RETCODE => lx_retcode);
288 IEX_DEBUG('Completed JTY_ASSIGN_BULK_PUB.collect_trans_data with status ' || l_return_status);
289 IEX_DEBUG('Message from JTY_ASSIGN_BULK_PUB.collect_trans_data ' ||lx_msg_data);
290 select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
291 FND_FILE.PUT_LINE(FND_FILE.LOG,'---' || l_date_str || '--------------------------');
292 FND_FILE.PUT_LINE(FND_FILE.LOG,'Ending JTY_ASSIGN_BULK_PUB.collect_trans_data...');
293 EXCEPTION WHEN OTHERS THEN
294 IEX_DEBUG('Error occured JTY_ASSIGN_BULK_PUB.collect_trans_data' ||SQLERRM);
295 IEX_DEBUG('Error buffer ' || lx_errbuf);
296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error occured JTY_ASSIGN_BULK_PUB.collect_trans_data' ||SQLERRM);
297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error buffer ' || lx_errbuf);
298 END;
299
300
301 SELECT count(*) INTO l_acc_count FROM jtf_tae_1600_cust_trans;
302 IEX_DEBUG('Number of records in jtf_tae_1600_cust_trans : ' || l_acc_count );
303 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of records in jtf_tae_1600_cust_trans : ' || l_acc_count );
304
305 IEX_DEBUG('Analyse values and Prepare Parallel Processing ...' );
306 -- populate worker_id in TRANS or NM_TRANS tables
307 Prepare_Parallel_Processing(
308 P_Request_Id => g_request_id,
309 P_Prev_Request_Id => g_prev_request_id,
310 P_Run_Mode => g_run_mode,
311 P_AccountCount => l_acc_count,
312 P_MinNumParallelProc => g_min_num_parallel_proc,
313 P_NumChildAccountWorker => g_NumChildAccountWorker,
314 X_ActualAccountWorkersUsed => p_ActualAccountWorkersUsed);
315 IEX_DEBUG('Parallel Processing values analysed successfully...');
316
317 IF (p_ActualAccountWorkersUsed = 0) THEN
318 IEX_DEBUG('No Records in JTF Trans Table, Do the Setup and then run this program');
319 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Do the setup and run this program ');
320 END IF;
321
322 SELECT count(*) INTO l_acc_count FROM jtf_tae_1600_cust_WINNERS;
323 IEX_DEBUG('Number of records in jtf_tae_1600_cust_winners : ' || l_acc_count );
324 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of records in jtf_tae_1600_cust_winners : ' || l_acc_count );
325
326 -- submit concurrent request IEXGAR for ACCOUNT
327 FOR i in 1..p_ActualAccountWorkersUsed LOOP
328 --Bug5043777. Fix By LKKUMAR. Set the Context for Spawn Program. Start.
329 fnd_request.set_org_id(mo_global.get_current_org_id);
330 FND_FILE.put_line(fnd_file.log,'Operating Unit Before Submitting IEXGAR is : ' || mo_global.get_current_org_id );
331 --Bug5043777. Fix By LKKUMAR. Set the Context for Spawn Program. Start.
332
333 IEX_DEBUG('Submiting IEXGAR(iextptwb.pls IEX_GAR_PUB.Generate_Access_Records)');
334 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submiting IEXGAR(iextptwb.pls IEX_GAR_PUB.Generate_Access_Records) ');
335 l_req_id := FND_REQUEST.SUBMIT_REQUEST('IEX',
336 'IEXGAR',
337 'Generate Access Records Collections',
338 '',
339 FALSE,
340 'TOTAL', --Bug5043777
341 p_debug_mode,
342 'N',
343 p_trans_type_acc,
344 i, -- worker id
345 p_ActualAccountWorkersUsed,
346 g_request_id,
347 l_number ,
348 -- CHR(0),
349 l_Assignlevel); -- Changed for bug 8708291 multi level strategy
350
351 commit;--Added for Bug 7697167 28-Jan-2009 barathsr
352
353 IF l_req_id = 0 THEN
354 l_msg:=FND_MESSAGE.GET;
355 IEX_DEBUG(l_msg);
356 END IF;
357 IEX_DEBUG('Submitted request for IEXGAR :' || l_req_id|| ' Worker Id:'||i);
358 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Submitted request for IEXGAR :' || l_req_id|| ' Worker Id:'||i);
359 l_req_id_lst(i):=l_req_id;--Added for Bug 7697167 28-Jan-2009 barathsr
360
361 END LOOP;
362
363
364 --Begin Bug 7697167 27-Jan-2009 barathsr
365 cnt:=l_req_id_lst.count;
366 IEX_DEBUG('Count of req_ids:' ||l_req_id_lst.count);
367 FND_FILE.PUT_LINE(FND_FILE.LOG,'Count of req_ids:' ||l_req_id_lst.count);
368 while cnt > 0 loop
369 for k in l_req_id_lst.first..l_req_id_lst.last
370 loop
371 if l_req_id_lst(k)<>-1 then
372 l_bool := FND_CONCURRENT.wait_for_request(
373 request_id =>l_req_id_lst(k),
374 interval =>30,
375 max_wait =>144000,
376 phase =>uphase,
377 status =>ustatus,
378 dev_phase =>dphase,
379 dev_status =>dstatus,
380 message =>message);
381 IF dphase = 'COMPLETE' then
382 l_req_id_lst(k):=-1;
383 cnt:=cnt-1;
384 END If; --dphase
385 end if;
386 end loop;
387 end loop;
388 --End Bug 7697167 27-Jan-2009 barathsr
389
390
391
392 IEX_DEBUG('Program iextpinb.pls : *** IEX_ATA_PUB.Assign_Territory_Access Ends ***');
393 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Program iextpinb.pls : *** IEX_ATA_PUB.Assign_Territory_Access Ends ***');
394 EXCEPTION
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396 IEX_DEBUG('Cannot Start');
397
398 WHEN others THEN
399 IEX_DEBUG('Exception: others in Assign_Territory_Accesses');
400 IEX_DEBUG('SQLCODE ' || to_char(SQLCODE) ||
401 ' SQLERRM ' || substr(SQLERRM, 1, 100));
402 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception: others in Assign_Territory_Accesses');
403 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SQLCODE ' || to_char(SQLCODE) ||
404 ' SQLERRM ' || substr(SQLERRM, 1, 100));
405
406 errbuf := SQLERRM;
407 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
408 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
409
410 END Assign_Territory_Accesses;
411
412
413 /*-------------------------------------------------------------------------*
414 | PRIVATE ROUTINE
415 | Set_Up
416 |
417 | PURPOSE
418 | Set up global variables and arrays
419 |
420 *-------------------------------------------------------------------------*/
421
422 PROCEDURE Set_Up(
423 px_acct_qual_tbl IN OUT NOCOPY QUAL_LIST_TBL_TYPE
424 )
425 IS
426 l_profile_value VARCHAR2(240);
427 BEGIN
428 IEX_DEBUG('Started doing the Set Up');
429
430 Concurrent_Profile_Options;
431
432 -- get profile option - AS_MC_MAX_ROLL_DAYS
433 /*
434 l_profile_value := fnd_profile.value('AS_MC_MAX_ROLL_DAYS');
435 IF RTRIM(l_profile_value) IS NULL THEN
436 g_num_rollup_days := 0;
437 ELSE
438 g_num_rollup_days := TO_NUMBER(l_profile_value);
439 END IF;
440
441 -- get profile option - AS_MC_DAILY_CONVERSION_TYPE
442 g_conversion_type := fnd_profile.value('AS_MC_DAILY_CONVERSION_TYPE');
443 */
444 -- get profile option - number of child process
445 g_num_child_processes :=
446 TO_NUMBER(fnd_profile.value('IEX_TERR_NUM_CHILD_PROCESSES'));
447 IEX_DEBUG('Mininum Child Process ' || g_num_child_processes);
448 -- get profile option - min number for parallel processing
449 g_min_num_parallel_proc :=
450 TO_NUMBER(NVL(fnd_profile.value('IEX_TERR_MIN_NUM_PARALLEL_PROC'), 3));
451 IEX_DEBUG('Minimum Number of Prallel Proc ' || g_min_num_parallel_proc);
452
453 -- fill in Account Qualifier Array
454 --Bug4654733. Fix by LKKUMAR on 06-Sep-2005. Remove AS dependency. Start
455 /*
456 AS_Tata_Process_Changed_Terr.Load_Acct_Qual_Array;
457 g_num_acct_qual := AS_Tata_Process_Changed_Terr.G_Account_Qualifier_Count; */
458 --Bug4654733. Fix by LKKUMAR on 06-Sep-2005. Remove AS dependency. End.
459
460 EXCEPTION
461 WHEN others THEN
462 IEX_DEBUG('Exception: others in set_up');
463 IEX_DEBUG('SQLCODE ' || to_char(SQLCODE) ||
464 ' SQLERRM ' || substr(SQLERRM, 1, 100));
465
466 RAISE;
467 END Set_Up;
468
469
470 /*-------------------------------------------------------------------------*
471 | PRIVATE ROUTINE
472 | Concurrent_Profile_Options
473 |
474 | PURPOSE
475 | Get concurrent profile options
476 |
477 *-------------------------------------------------------------------------*/
478
479 PROCEDURE Concurrent_Profile_Options
480 IS
481 l_temp_seq NUMBER;
482 l_retvalue VARCHAR2(20);
483
484 CURSOR c_get_conseq_cur IS
485 SELECT fnd_concurrent_requests_s.nextval
486 FROM dual;
487 BEGIN
488
489
490 -- get profile option - USER_ID
491 g_user_id := TO_NUMBER(fnd_profile.value('USER_ID'));
492
493 -- get profile option -- CONC_PROGRAM_APPLICATION_ID
494 g_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
495
496 -- get profile option -- CONC_PROGRAM_ID
497 g_prog_id := TO_NUMBER(fnd_profile.value('CONC_PROGRAM_ID'));
498
499 -- get profile option -- CONC_LOGIN_ID
500 g_last_update_login := TO_NUMBER(fnd_profile.value('CONC_LOGIN_ID'));
501
502 -- get profile option -- CONC_REQUEST_ID
503 g_request_id := TO_NUMBER(fnd_profile.value('CONC_REQUEST_ID'));
504
505
506 -- If g_request_id = 0, select directly from sequence
507 IF g_request_id = 0
508 THEN
509 -- Get concurrent sequence
510 OPEN c_get_conseq_cur;
511 FETCH c_get_conseq_cur INTO l_temp_seq;
512 CLOSE c_get_conseq_cur;
513
514 g_request_id := l_temp_seq;
515 END IF;
516 EXCEPTION
517 WHEN others THEN
518 IEX_DEBUG('Exception: others in concurrent_profile_options');
519 IEX_DEBUG('SQLCODE ' || to_char(SQLCODE) ||
520 ' SQLERRM ' || substr(SQLERRM, 1, 100));
521
522 RAISE;
523 END Concurrent_Profile_Options;
524
525 /*-------------------------------------------------------------------------*
526 | PUBLIC ROUTINE
527 | IEX_DEBUG
528 |
529 | PURPOSE
530 | write debug message
531 *-------------------------------------------------------------------------*/
532
533 PROCEDURE IEX_DEBUG( msg in VARCHAR2)
534 IS
535 l_length NUMBER;
536 l_start NUMBER := 1;
537 l_substring VARCHAR2(255);
538
539 l_base VARCHAR2(12);
540 l_date_str VARCHAR2(255);
541
542 BEGIN
543 IF g_debug_flag = 'Y'
544 THEN
545 select to_char( sysdate, 'DD-Mon-YYYY HH24:MI:SS') into l_date_str from dual;
546 FND_FILE.PUT_LINE(FND_FILE.LOG,'---' || l_date_str || '--------------------------');
547
548 -- chop the message to 255 long
549 l_length := length(msg);
550 WHILE l_length > 255 LOOP
551 l_substring := substr(msg, l_start, 255);
552 FND_FILE.PUT_LINE(FND_FILE.LOG, l_substring);
553 --Bug4221324. Fix by LKKUMAR on 06-Dec-2005. Start.
554 IEX_DEBUG_PUB.logmessage(l_substring);
555 --Bug4221324. Fix by LKKUMAR on 06-Dec-2005. End.
556 -- dbms_output.put_line(l_substring);
557
558 l_start := l_start + 255;
559 l_length := l_length - 255;
560 END LOOP;
561
562 l_substring := substr(msg, l_start);
563 FND_FILE.PUT_LINE(FND_FILE.LOG,l_substring);
564 --Bug4221324. Fix by LKKUMAR on 06-Dec-2005. Start.
565 IEX_DEBUG_PUB.logmessage(l_substring);
566 --Bug4221324. Fix by LKKUMAR on 06-Dec-2005. End.
567 -- dbms_output.put_line(l_substring);
568 END IF;
569 EXCEPTION
570 WHEN others THEN
571 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception: others in IEX_DEBUG');
572 FND_FILE.PUT_LINE(FND_FILE.LOG,
573 'SQLCODE ' || to_char(SQLCODE) ||
574 ' SQLERRM ' || substr(SQLERRM, 1, 100));
575 END IEX_DEBUG;
576
577
578 /*-------------------------------------------------------------------------*
579 | PUBLIC ROUTINE
580 | Set_Area_Sizes
581 |
582 | PURPOSE
583 | Set profile option values for sort area size and hash area size
584 *-------------------------------------------------------------------------*/
585
586 PROCEDURE Set_Area_Sizes
587 IS
588 st varchar2(500);
589 sort_size NUMBER := 100000000;
590 hash_size NUMBER := 100000000;
591 s number;
592
593 BEGIN
594 -- Alter session to set sort area size and hash area size
595 sort_size := fnd_profile.value('IEX_SORT_AREA_SIZE_FOR_TAP');
596 IF sort_size is not NULL and sort_size > 0 THEN
597 st := 'ALTER SESSION SET SORT_AREA_SIZE = ' || sort_size;
598 EXECUTE IMMEDIATE st;
599 select value into s from V$PARAMETER where name = 'sort_area_size';
600 END IF;
601 IEX_DEBUG('Sort Area Size ' || s );
602
603 hash_size := fnd_profile.value('IEX_HASH_AREA_SIZE_FOR_TAP');
604 IF hash_size is not NULL and hash_size > 0 THEN
605 st := 'ALTER SESSION SET HASH_AREA_SIZE = ' || hash_size;
606 EXECUTE IMMEDIATE st;
607 select value into s from V$PARAMETER where name = 'hash_area_size';
608 END IF;
609 IEX_DEBUG('Hash Area Size ' || s );
610 END Set_Area_Sizes;
611
612 /*-------------------------------------------------------------------------*
613 | PUBLIC ROUTINE
614 | Prepare_Parallel_Processing
615 |
616 | PURPOSE
617 | Prepare the TRANS tables for parallel processing
618 *-------------------------------------------------------------------------*/
619 PROCEDURE Prepare_Parallel_Processing(
620 P_Request_Id NUMBER,
621 P_Prev_Request_Id NUMBER,
622 P_Run_Mode VARCHAR2,
623 P_AccountCount NUMBER,
624 P_MinNumParallelProc NUMBER,
625 P_NumChildAccountWorker NUMBER,
626 X_ActualAccountWorkersUsed OUT NOCOPY NUMBER)
627 IS
628 l_ActualWorker NUMBER := 0;
629 l_WorkerLoad NUMBER := 0;
630
631 BEGIN
632
633 IEX_DEBUG('*** Prepare_Parallel_Processing() *** - Start - ');
634 X_ActualAccountWorkersUsed := 0;
635
636 l_ActualWorker := 0;
637 l_WorkerLoad := 0;
638
639 l_WorkerLoad := CEIL(P_AccountCount / P_NumChildAccountWorker);
640 If l_WorkerLoad < P_MinNumParallelProc then
641 l_WorkerLoad := P_MinNumParallelProc;
642 End If;
643
644 l_ActualWorker := CEIL(P_AccountCount/l_WorkerLoad);
645 IEX_DEBUG('Actual Worker Assigned : '||l_ActualWorker);
646
647 /*
648 IF p_run_mode = G_TOTAL_MODE THEN
649 UPDATE JTF_TAE_1001_ACCOUNT_TRANS
650 SET worker_id = mod (trans_object_id, l_ActualWorker) + 1;
651 IEX_DEBUG('UPDATE JTF_TAE_1001_ACCOUNT_TRANS.worker_id');
652 ELSIF p_run_mode = G_NEW_MODE THEN
653 UPDATE JTF_TAE_1001_ACCOUNT_NM_TRANS
654 SET worker_id = mod (trans_object_id, l_ActualWorker) + 1;
655 IEX_DEBUG('UPDATE JTF_TAE_1001_ACCOUNT_NM_TRANS.worker_id');
656 END IF;
657 */
658 X_ActualAccountWorkersUsed := l_ActualWorker;
659
660 EXCEPTION
661 WHEN others THEN
662 IEX_DEBUG('Exception: others in Prepare_Parallel_Processing');
663 IEX_DEBUG('SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || substr(SQLERRM, 1, 100));
664
665 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception: others in Prepare_Parallel_Processing');
666 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SQLCODE ' || to_char(SQLCODE) || ' SQLERRM ' || substr(SQLERRM, 1, 100));
667 END Prepare_Parallel_Processing;
668
669
670 END IEX_ATA_PUB;