[Home] [Help]
PACKAGE BODY: APPS.OKL_K_LINE_FIN_EXT_MASTER_PVT
Source
1 PACKAGE BODY OKL_K_LINE_FIN_EXT_MASTER_PVT AS
2 /* $Header: OKLRLEMB.pls 120.0.12010000.8 2009/01/13 23:56:09 sechawla noship $ */
3
4 PROCEDURE write_to_log(
5 p_message IN VARCHAR2
6 ) IS
7 BEGIN
8 fnd_file.put_line(fnd_file.log, p_message);
9 END write_to_log;
10
11
12 PROCEDURE write_to_output(
13 p_message IN VARCHAR2
14 ) IS
15 BEGIN
16 fnd_file.put_line(fnd_file.output, p_message);
17 END write_to_output;
18
19
20
21
22 /*========================================================================
23 | PUBLIC PROCEDURE Process_Spawner
24 |
25 | DESCRIPTION
26 | This procedure identifies contracts for Contract Line Financial Report, based
27 | upon a few primary input paramaters, inserts these contracts into a temp table and
28 | assigns a worker to each contract. It then spawns child request(s), based upon
29 | the parameter 'p_num_processes'. Once child requests complete, it launches
30 | request for Contract Line Financial Report
31 |
32 | CALLED FROM
33 | Concurrent Program "Master Program -- Contract Line Financial Report"
34 |
35 | CALLS PROCEDURES/FUNCTIONS
36 |
37 | KNOWN ISSUES
38 |
39 | NOTES
40 |
41 |
42 | MODIFICATION HISTORY
43 | Date Author Description of Changes
44 | 30-Oct-2008 Durga Janaswamy Created
45 | 15-Dec-2008 Seema Chawla Added params p_template_code,p_report_language,p_report_format
46 *=======================================================================*/
47
48 PROCEDURE Process_Spawner (
49 errbuf OUT NOCOPY VARCHAR2,
50 retcode OUT NOCOPY NUMBER,
51 P_OPERATING_UNIT IN NUMBER,
52 P_REPORT_DATE IN VARCHAR2,
53 P_DATA_SOURCE_CODE IN VARCHAR2,
54 P_REPORT_TEMPLATE_NAME IN VARCHAR2,
55 -- P_TEMPLATE_CODE IN VARCHAR2, --sechawla 7628379
56 P_REPORT_LANGUAGE IN VARCHAR2,
57 P_REPORT_FORMAT IN VARCHAR2,
58 P_START_DATE_FROM IN VARCHAR2,
59 P_START_DATE_TO IN VARCHAR2,
60 P_BOOK_CLASS IN VARCHAR2,
61 P_LEASE_PRODUCT IN VARCHAR2,
62 P_CONTRACT_NUMBER IN VARCHAR2,
63 P_CONTRACT_STATUS IN VARCHAR2,
64 P_CONTRACT_LINE_STATUS IN VARCHAR2,
65 P_CONTRACT_LINE_TYPE IN VARCHAR2,
66 P_CUSTOMER_NAME IN VARCHAR2,
67 P_CUSTOMER_NUMBER IN VARCHAR2,
68 P_VENDOR_NAME IN VARCHAR2,
69 P_VENDOR_NUMBER IN VARCHAR2,
70 P_FA_INFO_YN IN VARCHAR2,
71 P_TAX_BOOK IN VARCHAR2,
72 P_DELETE_DATA_YN IN VARCHAR2,
73 P_NUM_PROCESSES IN NUMBER
74
75 ) IS
76
77
78 CURSOR l_parallel_worker_csr(cp_assigned_process IN VARCHAR2) IS
79 SELECT object_value, khr_id, assigned_process
80 FROM OKL_PARALLEL_PROCESSES
81 WHERE object_type = 'CONTRACT_FIN_LINE_EXT-CONTRACT'
82 AND assigned_process = cp_assigned_process
83 AND process_status = 'PENDING_ASSIGNMENT';
84
85 CURSOR parent_sts_csr(p_request_id NUMBER) IS
86 SELECT count(*)
87 from fnd_concurrent_requests req,
88 fnd_concurrent_programs pgm
89 where req.PRIORITY_REQUEST_ID = p_request_id
90 and req.concurrent_program_id = pgm.concurrent_program_id
91 and req.PHASE_CODE = 'C'
92 and request_id <> p_request_id
93 and STATUS_CODE = 'E';
94
95 CURSOR parent_warn_sts_csr(p_request_id NUMBER) IS
96 SELECT count(*)
97 from fnd_concurrent_requests req,
98 fnd_concurrent_programs pgm
99 where req.priority_request_id = p_request_id
100 and req.concurrent_program_id = pgm.concurrent_program_id
101 and req.phase_code = 'C'
102 and request_id <> p_request_id
103 and status_code = 'G';
104
105 l_int_counter INTEGER;
106 l_init_loop BOOLEAN := TRUE;
107 l_seq_next NUMBER;
108 l_char_seq_num VARCHAR2(30);
109 l_data_found BOOLEAN := FALSE;
110 lp_k_start_date_from DATE;
111 lp_k_start_date_to DATE;
112 lp_k_end_date_from DATE;
113 lp_k_end_date_to DATE;
114 l_req_data VARCHAR2(10);
115 l_req_counter NUMBER;
116 request_id NUMBER := 0;
117 l_last_worker_used NUMBER;
118 TYPE parallel_worker_tbl_type IS TABLE OF l_parallel_worker_csr%ROWTYPE INDEX BY BINARY_INTEGER;
119
120 l_parallel_worker_tbl parallel_worker_tbl_type;
121 l_parallel_worker_temp_tbl parallel_worker_tbl_type;
122
123 l_fetch_size NUMBER := 10000;
124 l_total_rows NUMBER := 0;
125 l_this_row NUMBER;
126 l_max_worker_used NUMBER;
127
128 -- Org Id and standard who columns
129 l_last_updated_by okl_parallel_processes.last_updated_by%TYPE := Fnd_Global.USER_ID;
130 l_last_update_login okl_parallel_processes.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
131 l_request_id okl_parallel_processes.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
132 l_program_id okl_parallel_processes.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
133 l_org_id okl_parallel_processes.org_id%type;
134
135 l_child_in_error NUMBER;
136 l_child_in_warn NUMBER;
137
138 i NUMBER;
139 report_request_id NUMBER := 0;
140 l_row_count NUMBER;
141 p_ret_add_layout BOOLEAN := true;
142 lp_report_date DATE;
143
144 begin
145
146
147 -- The following block is added to control the sub-request program
148 -- submission. It ensures that this program is not executed recurrsively.
149 l_req_data := fnd_conc_global.request_data;
150 -- If l_req_data has a value within this session, the program is attempting to
151 -- run again, therefore break out of the loop.
152
153
154 -- Add couple of blank lines
155 fnd_file.new_line(fnd_file.log,2);
156 fnd_file.new_line(fnd_file.output,2);
157
158
159 write_to_log('l_req_data : '||l_req_data);
160
161 MO_GLOBAL.set_policy_context('S',p_operating_unit);
162
163 if l_req_data is not null and l_req_data = '1' then
164
165
166 l_child_in_error := 0;
167 OPEN parent_sts_csr( l_request_id );
168 FETCH parent_sts_csr INTO l_child_in_error;
169 CLOSE parent_sts_csr;
170
171 write_to_log('l_child_in_error : '||l_child_in_error);
172 l_child_in_warn := 0;
173 OPEN parent_warn_sts_csr( l_request_id );
174 FETCH parent_warn_sts_csr INTO l_child_in_warn;
175 CLOSE parent_warn_sts_csr;
176
177 write_to_log('l_child_in_warn : '||l_child_in_warn);
178
179 if l_child_in_error > 0 then
180 errbuf := 'Done, but with error!';
181 retcode := 2;
182 return;
183 end if;
184
185 if l_child_in_warn > 0 then
186 errbuf := 'Done, but with warning(s)!';
187 retcode := 1;
188 return;
189 end if;
190
191
192
193 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
194
195 --P_REPORT_TEMPLATE_NAME <-> value set 'OKL_XDO_REP_TEMPLATE' <-> stores the template code (in ID column)
196 --P_REPORT_LANGUAGE <-> value set 'OKL_XDO_REP_LANGUAGE' <-> stores language code e.g 'en' (in ID column)
197 --P_REPORT_FORMAT <-> value set 'OKL_XDO_REP_FORMAT' <-> stores format code e.g 'RTF' (in ID column)
198
199
200 p_ret_add_layout := FND_REQUEST.add_layout( template_appl_name=>'OKL',
201 template_code=> P_REPORT_TEMPLATE_NAME, --'OKLLNFINEXT',
202 template_language=>P_REPORT_LANGUAGE, --'en',
203 template_territory=>'00',
204 output_format=>P_REPORT_FORMAT); --'RTF');
205 IF p_ret_add_layout THEN
206 write_to_log('p_ret_add_layout = TRUE');
207 ELSE
208 write_to_log('p_ret_add_layout = FALSE');
209 END IF;
210
211 report_request_id := FND_REQUEST.SUBMIT_REQUEST(
212 application => 'OKL',
213 program => 'OKLLNFINEXT',
214 sub_request => TRUE,
215 argument1 => P_OPERATING_UNIT,
216 argument2 => P_REPORT_DATE,
217 argument3 => P_START_DATE_FROM,
218 argument4 => P_START_DATE_TO,
219 argument5 => P_BOOK_CLASS,
220 argument6 => P_LEASE_PRODUCT,
221 argument7 => P_CONTRACT_NUMBER,
222 argument8 => P_CONTRACT_STATUS,
223 argument9 => P_CONTRACT_LINE_STATUS,
224 argument10 => P_CONTRACT_LINE_TYPE,
225 argument11 => P_CUSTOMER_NAME,
226 argument12 => P_CUSTOMER_NUMBER,
227 argument13 => P_VENDOR_NAME,
228 argument14 => P_VENDOR_NUMBER,
229 argument15 => P_FA_INFO_YN,
230 argument16 => P_TAX_BOOK,
231 argument17 => P_DELETE_DATA_YN
232 );
233
234
235 write_to_log('Launching Report with Request ID '||report_request_id);
236
237 if (report_request_id = 0) then
238
239
240 write_to_log('Request submission failed. ');
241 write_to_log('Exiting with error... ');
242
243 DELETE OKL_PARALLEL_PROCESSES
244 WHERE assigned_process like l_char_seq_num||'%' ;
245
246 l_row_count := sql%rowcount;
247 IF l_row_count > 0 THEN
248 write_to_log('Deleted '||l_row_count||' rows from OKL_PARALLEL_PROCESSES.');
249 END IF;
250
251 COMMIT;
252
253 -- If request submission failed, exit with error
254 errbuf := fnd_message.get;
255 retcode := 2;
256 else
257 write_to_log('Report Sub-Request submitted, putting master into PAUSED mode');
258 --Set the globals to put the master into PAUSED mode
259 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
260 request_data => to_char(2));
261 errbuf := 'Sub-Request submitted!';
262 retcode := 0;
263 end if;
264
265 elsif l_req_data is not null and l_req_data = '2' then
266 l_child_in_error := 0;
267 OPEN parent_sts_csr( l_request_id );
268 FETCH parent_sts_csr INTO l_child_in_error;
269 CLOSE parent_sts_csr;
270
271 write_to_log('l_child_in_error : '||l_child_in_error);
272 l_child_in_warn := 0;
273 OPEN parent_warn_sts_csr( l_request_id );
274 FETCH parent_warn_sts_csr INTO l_child_in_warn;
275 CLOSE parent_warn_sts_csr;
276
277 write_to_log('l_child_in_warn : '||l_child_in_warn);
278
279 if l_child_in_error > 0 then
280 errbuf := 'Done, but with error!';
281 retcode := 2;
282 return;
283 end if;
284
285 if l_child_in_warn > 0 then
286 errbuf := 'Done, but with warning(s)!';
287 retcode := 1;
288 return;
289 end if;
290
291 write_to_log('Returning Successfully');
292 errbuf := 'Done!';
293 retcode := 0;
294 return;
295
296 end if;
297
298 IF l_req_data IS NULL THEN
299
300
301 write_to_log('P_OPERATING_UNIT = '||P_OPERATING_UNIT);
302 write_to_log('P_REPORT_DATE = '||P_REPORT_DATE);
303 write_to_log('P_DATA_SOURCE_CODE = '||P_DATA_SOURCE_CODE);
304 write_to_log('P_REPORT_TEMPLATE_NAME ='|| P_REPORT_TEMPLATE_NAME);
305 -- write_to_log('P_TEMPLATE_CODE ='|| P_TEMPLATE_CODE); --sechawla 7628379
306 write_to_log('P_REPORT_LANGUAGE ='|| P_REPORT_LANGUAGE);
307 write_to_log('P_REPORT_FORMAT ='|| P_REPORT_FORMAT);
308 write_to_log('P_START_DATE_FROM = '||P_START_DATE_FROM);
309 write_to_log('P_START_DATE_TO = '||P_START_DATE_TO);
310 write_to_log('P_BOOK_CLASS = '||P_BOOK_CLASS);
311 write_to_log('P_LEASE_PRODUCT = '||P_LEASE_PRODUCT);
312 write_to_log('P_CONTRACT_NUMBER= '||P_CONTRACT_NUMBER);
313 write_to_log('P_CONTRACT_STATUS = '||P_CONTRACT_STATUS);
314 write_to_log('P_CONTRACT_LINE_STATUS = '||P_CONTRACT_LINE_STATUS);
315 write_to_log('P_CONTRACT_LINE_TYPE = '||P_CONTRACT_LINE_TYPE);
316 write_to_log('P_CUSTOMER_NAME = '||P_CUSTOMER_NAME);
317 write_to_log('P_CUSTOMER_NUMBER = '||P_CUSTOMER_NUMBER);
318 write_to_log('P_VENDOR_NAME = '||P_VENDOR_NAME);
319 write_to_log('P_VENDOR_NUMBER = '||P_VENDOR_NUMBER);
320 write_to_log('P_FA_INFO_YN= '||P_FA_INFO_YN);
321 write_to_log('P_TAX_BOOK = '||P_TAX_BOOK);
322 write_to_log('P_DELETE_DATA_YN '||P_DELETE_DATA_YN);
323 write_to_log('P_NUM_PROCESSES= '||P_NUM_PROCESSES);
324
325
326
327
328
329 lp_report_date := FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE);
330
331 ---- Paramater validations -----
332
333 IF trunc(lp_report_date) <> trunc(sysdate) THEN
334 write_to_log('Report Date should be today''s date');
335 return;
336 END IF;
337
338 -- If p_num_processes is 0 then no need to go through the algorithm.
339 -- p_num_processes can be 1
340 if ( nvl(p_num_processes,0) = 0 OR nvl(p_num_processes,0) < 0 )then
341 write_to_log('No workers specified');
342 return;
343 end if;
344
345 lp_k_start_date_from := FND_DATE.CANONICAL_TO_DATE(P_START_DATE_FROM);
346 lp_k_start_date_to := FND_DATE.CANONICAL_TO_DATE(P_START_DATE_TO);
347
348
349 -- Select sequence for marking processes
350 select okl_opp_seq.nextval
351 into l_seq_next
352 from dual ;
353
354 l_char_seq_num := to_char(l_seq_next);
355
356 WRITE_TO_LOG('Sequence Number : '||l_seq_next);
357
358 -- mark records for processing
359
360 l_org_id := mo_global.get_current_org_id();
361 WRITE_TO_LOG('org id:: '||l_org_id);
362
363 INSERT INTO OKL_PARALLEL_PROCESSES
364 (
365 object_type,
366 object_value,
367 object_status,
368 assigned_process,
369 process_status,
370 start_date,
371 khr_id,
372 ORG_ID,
373 CREATED_BY,
374 CREATION_DATE,
375 LAST_UPDATE_DATE,
376 LAST_UPDATED_BY,
377 LAST_UPDATE_LOGIN,
378 REQUEST_ID,
379 PROGRAM_ID,
380 PROGRAM_UPDATE_DATE
381 )
382 SELECT
383 'CONTRACT_FIN_LINE_EXT-CONTRACT',
384 chr.contract_number,
385 OKL_K_LINE_FIN_EXT_PVT.Contract_Active_YN(khr.id, khr.DEAL_TYPE,chr.STS_CODE),
386 l_char_seq_num,
387 'PENDING_ASSIGNMENT',
388 sysdate,
389 chr.id ,
390 l_org_id,
391 l_last_updated_by,
392 sysdate,
393 sysdate,
394 l_last_updated_by,
395 l_last_update_login,
396 l_request_id,
397 l_program_id,
398 sysdate
399 FROM okc_k_headers_all_b chr,
400 OKL_K_HEADERS khr
401 WHERE chr.id = khr.id
402 AND chr.SCS_CODE = 'LEASE'
403 AND chr.sts_code IN ('BANKRUPTCY_HOLD','ENTERED','BOOKED', 'COMPLETE', 'EVERGREEN', 'EXPIRED', 'INCOMPLETE',
404 'LITIGATION_HOLD', 'NEW', 'PASSED', 'REVERSED', 'TERMINATED') --sechawla 13-jan-09 7693771
405 AND chr.AUTHORING_ORG_ID = P_OPERATING_UNIT
406 AND (chr.start_date IS NOT NULL AND chr.START_DATE >= lp_k_start_date_from)
407 AND (chr.start_date IS NOT NULL AND chr.START_DATE <= lp_k_start_date_to)
408 AND nvl(khr.DEAL_TYPE,'XXX') = nvl(P_BOOK_CLASS, nvl(khr.DEAL_TYPE,'XXX'))
409 AND nvl(khr.pdt_id,-9999) = nvl(P_LEASE_PRODUCT,nvl(khr.pdt_id,-9999))
410 AND chr.sts_code = nvl(P_CONTRACT_STATUS, chr.sts_code)
411 AND CHR.CONTRACT_NUMBER like NVL(P_CONTRACT_NUMBER, CHR.CONTRACT_NUMBER)
412 AND NOT EXISTS
413 (SELECT '1'
414 FROM OKL_PARALLEL_PROCESSES opp
415 WHERE chr.contract_number = opp.object_value
416 AND opp.object_type = 'CONTRACT_FIN_EXTRACT'
417 AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'));
418
419
420 l_row_count := SQL%ROWCOUNT;
421
422 write_to_log('Number of rows inserted in OKL_PARALLEL_PROCESSES :'||l_row_count);
423
424 IF l_row_count > 0 THEN
425 l_data_found := TRUE;
426
427 END IF;
428
429
430 COMMIT;
431
432
433 if l_data_found then
434
435 write_to_log('l_fetch_size : '||l_fetch_size);
436
437
438 i := 1;
439 l_parallel_worker_tbl.DELETE;
440 OPEN l_parallel_worker_csr(l_char_seq_num);
441 LOOP
442 l_parallel_worker_temp_tbl.DELETE;
443 FETCH l_parallel_worker_csr BULK COLLECT INTO l_parallel_worker_temp_tbl LIMIT l_fetch_size;
444
445
446 IF l_parallel_worker_temp_tbl.COUNT > 0 THEN
447 FOR k IN l_parallel_worker_temp_tbl.FIRST..l_parallel_worker_temp_tbl.LAST LOOP
448 l_parallel_worker_tbl(i).object_value := l_parallel_worker_temp_tbl(k).object_value;
449 l_parallel_worker_tbl(i).khr_id := l_parallel_worker_temp_tbl(k).khr_id;
450 l_parallel_worker_tbl(i).assigned_process := l_parallel_worker_temp_tbl(k).assigned_process;
451
452 i := i + 1;
453 END LOOP;
454
455 END IF;
456
457 EXIT WHEN l_parallel_worker_csr%NOTFOUND;
458 END LOOP;
459 CLOSE l_parallel_worker_csr;
460
461 l_total_rows := l_parallel_worker_tbl.count;
462
463 write_to_log('l_parallel_worker_tbl.count :'||l_total_rows);
464
465
466 IF l_total_rows > 0 THEN
467 -- p_num_processes is > 0
468 l_this_row := 1;
469
470 l_max_worker_used := 0;
471
472 WHILE l_this_row <= l_total_rows LOOP
473 FOR j in 1..p_num_processes LOOP
474 IF l_this_row <= l_total_rows THEN
475 l_parallel_worker_tbl(l_this_row).assigned_process := l_parallel_worker_tbl(l_this_row).assigned_process||'-'||j ;
476
477 l_last_worker_used := j;
478
479 l_this_row := l_this_row + 1;
480 ELSE
481 EXIT;
482 END IF;
483 END LOOP;
484
485 if l_last_worker_used > l_max_worker_used then
486 l_max_worker_used :=l_last_worker_used;
487 end if;
488
489 END LOOP;
490
491 write_to_log('l_max_worker_used :'||l_max_worker_used);
492
493 -- At this point, l_parallel_worker_tbl has all the contracts with an assigned process
494 FOR k IN l_parallel_worker_tbl.FIRST..l_parallel_worker_tbl.LAST LOOP
495
496
497 write_to_log('contract # : '||l_parallel_worker_tbl(k).object_value||': worker # '||l_parallel_worker_tbl(k).assigned_process);
498 UPDATE OKL_PARALLEL_PROCESSES
499 SET assigned_process = l_parallel_worker_tbl(k).assigned_process,
500 process_status = 'ASSIGNED'
501 WHERE object_Type = 'CONTRACT_FIN_LINE_EXT-CONTRACT'
502 AND object_value = l_parallel_worker_tbl(k).object_value
503 AND process_status = 'PENDING_ASSIGNMENT';
504
505 COMMIT;
506 END LOOP;
507
508 write_to_log('OKL_PARALLEL_PROCESSES Updated with worker assignment');
509
510 FOR j in 1..l_max_worker_used LOOP
511
512 l_req_data := fnd_conc_global.request_data;
513
514
515 if (l_req_data is not null) then
516 l_req_counter := l_req_counter + to_number(l_req_data);
517 else
518 l_req_counter := 1;
519 end if;
520
521 -- l_req_counter = number of workers used ?
522 write_to_log('Worker # :'||j||' l_req_data : '||l_req_data||' l_req_counter : '||l_req_counter);
523 if l_req_counter < (p_num_processes+1) then
524 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
525
526
527 request_id := FND_REQUEST.SUBMIT_REQUEST(
528 application => 'OKL',
529 program => 'OKL_K_LINE_FIN_REP_CHILD', --short name for child conc program
530 sub_request => TRUE,
531 argument1 => P_OPERATING_UNIT,
532 argument2 => P_REPORT_DATE,
533 argument3 => P_START_DATE_FROM,
534 argument4 => P_START_DATE_TO,
535 argument5 => P_BOOK_CLASS,
536 argument6 => P_LEASE_PRODUCT,
537 argument7 => P_CONTRACT_NUMBER,
538 argument8 => P_CONTRACT_STATUS,
539 argument9 => P_CONTRACT_LINE_STATUS,
540 argument10 => P_CONTRACT_LINE_TYPE,
541 argument11 => P_CUSTOMER_NAME,
542 argument12 => P_CUSTOMER_NUMBER,
543 argument13 => P_VENDOR_NAME,
544 argument14 => P_VENDOR_NUMBER,
545 argument15 => P_FA_INFO_YN,
546 argument16 => P_TAX_BOOK,
547 argument17 => P_DELETE_DATA_YN,
548 argument18 => P_NUM_PROCESSES,
549 argument19 => l_seq_next||'-'||j
550 );
551
552
553 write_to_log('Launching Process '||l_seq_next||'-'||j ||' with Request ID '||request_id);
554
555 if (request_id = 0) then
556
557 write_to_log('Request submission failed.');
558 write_to_log('Exiting with error... ');
559
560 DELETE OKL_PARALLEL_PROCESSES
561 WHERE assigned_process like l_char_seq_num||'%' ;
562
563 l_row_count := sql%rowcount;
564 IF l_row_count > 0 THEN
565 write_to_log('Deleted '||l_row_count||' rows from OKL_PARALLEL_PROCESSES.');
566 END IF;
567
568 COMMIT;
569 -- If request submission failed, exit with error
570 errbuf := fnd_message.get;
571 retcode := 2;
572 else
573 write_to_log('Sub-Request submitted, putting master into PAUSED mode');
574 -- Set the globals to put the master into PAUSED mode
575 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
576 request_data => to_char(1));
577 errbuf := 'Sub-Request submitted!';
578 retcode := 0;
579 end if;
580 end if;
581
582
583 END LOOP;
584
585 END IF; --IF l_total_rows > 0 THEN
586
587 else --if l_data_found then
588 write_to_log('No workers assigned due to no data found for prcocesing');
589
590 end if;
591 end if;
592 exception
593 when others then
594 write_to_log('Unhandled Exception '||sqlcode||':'||sqlerrm);
595
596 DELETE OKL_PARALLEL_PROCESSES
597 WHERE assigned_process like l_char_seq_num||'%' ;
598
599 l_row_count := sql%rowcount;
600
601 IF l_row_count > 0 THEN
602 write_to_log('Deleted '||sql%rowcount||' rows from OKL_PARALLEL_PROCESSES.');
603 END IF;
604
605 COMMIT;
606
607 END Process_Spawner;
608
609
610
611 END OKL_K_LINE_FIN_EXT_MASTER_PVT;