[Home] [Help]
PACKAGE BODY: APPS.AR_UPGHARNESS_PKG
Source
1 PACKAGE BODY AR_UPGHARNESS_PKG AS
2 /*$Header: ARXLAHNB.pls 120.10.12010000.4 2009/08/19 05:12:18 aghoraka ship $*/
3
4 PROCEDURE ins_120_ctl
5 (p_script_name IN VARCHAR2,
6 p_batch_id IN NUMBER);
7
8 PROCEDURE verif_status
9 (p_batch_id IN NUMBER);
10
11 PROCEDURE submission_main_routine
12 (p_workers IN NUMBER,
13 p_batch_id IN NUMBER,
14 p_batch_size IN NUMBER);
15
16 PROCEDURE update_process_status
17 (p_request_id IN NUMBER
18 ,p_status IN VARCHAR2);
19
20 PROCEDURE insert_req_control
21 (p_ins_rec IN xla_upgrade_requests%ROWTYPE,
22 x_request_control_id OUT NOCOPY NUMBER);
23
24 PROCEDURE update_req_control
25 (p_request_control_id IN NUMBER,
26 p_status IN VARCHAR2);
27
28 --{FND loging
29 PROCEDURE log(
30 message IN VARCHAR2,
31 newline IN BOOLEAN DEFAULT TRUE) IS
32 BEGIN
33 IF message = 'NEWLINE' THEN
34 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
35 ELSIF (newline) THEN
36 FND_FILE.put_line(fnd_file.log,message);
37 ELSE
38 FND_FILE.put(fnd_file.log,message);
39 END IF;
40 END log;
41
42 PROCEDURE out(
43 message IN VARCHAR2,
44 newline IN BOOLEAN DEFAULT TRUE) IS
45 BEGIN
46 IF message = 'NEWLINE' THEN
47 FND_FILE.NEW_LINE(FND_FILE.output, 1);
48 ELSIF (newline) THEN
49 FND_FILE.put_line(fnd_file.output,message);
50 ELSE
51 FND_FILE.put(fnd_file.output,message);
52 END IF;
53 END out;
54
55 PROCEDURE outandlog(
56 message IN VARCHAR2,
57 newline IN BOOLEAN DEFAULT TRUE) IS
58 BEGIN
59 out(message, newline);
60 log(message, newline);
61 END outandlog;
62
63
64 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
65 RETURN VARCHAR2 IS
66 l_msg_data VARCHAR2(2000);
67 BEGIN
68 FND_MSG_PUB.Reset;
69
70 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
71 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
72 END LOOP;
73 IF (SQLERRM IS NOT NULL) THEN
74 l_msg_data := l_msg_data || SQLERRM;
75 END IF;
76 log(l_msg_data);
77 RETURN l_msg_data;
78 END logerror;
79 --}
80
81 PROCEDURE update_gl_period
82 IS
83 CURSOR c IS
84 SELECT 'Y'
85 FROM ar_submission_ctrl_gt
86 WHERE STATUS <> 'NORMAL';
87
88 CURSOR update_gl_periods IS
89 SELECT application_id,
90 ledger_id,
91 period_name,
92 period_year
93 FROM gl_period_statuses gps
94 WHERE gps.migration_status_code = 'P'
95 AND gps.application_id (+) = 222;
96 l_status VARCHAR2(1);
97 l_not_complete VARCHAR2(1);
98 update_gps_error EXCEPTION;
99 BEGIN
100 OPEN c;
101 FETCH c INTO l_not_complete;
102 IF c%NOTFOUND THEN
103 -- All requests end NORMAL - Update GL period statuses
104 FOR l_update_gl_periods IN update_gl_periods LOOP
105 l_status := xla_upgrade_pub.set_migration_status_code
106 ( 222,
107 l_update_gl_periods.ledger_id,
108 l_update_gl_periods.period_name,
109 l_update_gl_periods.period_year);
110 IF l_status = 'F' THEN
111 RAISE update_gps_error;
112 END IF;
113 COMMIT;
114 END LOOP;
115 ELSE
116 --Some requests ended with WARNING or ERROR - Do not update the GL period Statuses
117 -- So that the next run processes them
118 NULL;
119 END IF;
120 CLOSE c;
121 EXCEPTION
122 WHEN UPDATE_GPS_ERROR THEN
123 RAISE;
124 WHEN NO_DATA_FOUND THEN
125 NULL;
126 WHEN OTHERS THEN
127 RAISE;
128 END;
129
130
131 FUNCTION mrc_run_required RETURN VARCHAR2
132 IS
133 CURSOR c IS
134 SELECT 'Y'
135 FROM gl_mc_reporting_options_11i
136 WHERE application_id = 222;
137 l_result VARCHAR2(1);
138 BEGIN
139 OPEN c;
140 FETCH c INTO l_result;
141 IF c%NOTFOUND THEN
142 l_result := 'N';
143 END IF;
144 CLOSE c;
145 RETURN l_result;
146 EXCEPTION
147 WHEN OTHERS THEN
148 RETURN 'N';
149 END;
150
151
152 PROCEDURE ins_120_ctl
153 (p_script_name IN VARCHAR2,
154 p_batch_id IN NUMBER)
155 IS
156 BEGIN
157 outandlog('ins_120_ctl +');
158 outandlog(' p_script_name:'||p_script_name);
159 INSERT INTO ar_upg_120_control
160 (script_name,
161 processed_flag,
162 action_flag,
163 batch_id,
164 creation_date,
165 created_by,
166 last_update_date,
167 last_updated_by) VALUES
168 (p_script_name,
169 'A',
170 'R', --downtime
171 p_batch_id,
172 sysdate,
173 -2005,
174 sysdate,
175 -2005);
176 outandlog('ins_120_ctl -');
177 EXCEPTION
178 WHEN OTHERS THEN
179 log(' EXCPETION OTHERS in ins_120_ctl :'||SQLERRM);
180 RAISE;
181 END;
182
183 PROCEDURE verif_status
184 (p_batch_id IN NUMBER)
185 IS
186 CURSOR c_req IS
187 SELECT request_id
188 FROM ar_submission_ctrl_gt
189 WHERE batch_id = p_batch_id
190 AND status = 'SUBMITTED';
191 l_res BOOLEAN;
192 l_phase VARCHAR2(30);
193 l_status VARCHAR2(30);
194 l_dev_phase VARCHAR2(30);
195 l_dev_status VARCHAR2(30);
196 l_message VARCHAR2(2000);
197 l_request_id NUMBER;
198 BEGIN
199 OPEN c_req;
200 LOOP
201 FETCH c_req INTO l_request_id;
202 EXIT WHEN c_req%NOTFOUND;
203
204 l_res := FND_CONCURRENT.GET_REQUEST_STATUS
205 (request_id => l_request_id
206 ,phase => l_phase
207 ,status => l_status
208 ,dev_phase => l_dev_phase
209 ,dev_status => l_dev_status
210 ,message => l_message);
211
212 IF l_dev_phase = 'COMPLETE' THEN
213
214 IF l_dev_status = 'NORMAL' THEN
215 outandlog('The process '||l_request_id||' completed successfully');
216 outandlog(l_message);
217 ELSIF l_dev_status = 'ERROR' THEN
218 log('The process '||l_request_id||' completed with error');
219 log(l_message);
220 ELSIF l_dev_status = 'WARNING' THEN
221 outandlog('The process '||l_request_id||' completed with warning');
222 outandlog(l_message);
223 ELSIF l_dev_status = 'CANCELLED' THEN
224 log('User has aborted the process '||l_request_id);
225 log(l_message);
226 ELSIF l_dev_status = 'TERMINATED' THEN
227 log('User has aborted the process '||l_request_id);
228 log(l_message);
229 END IF;
230
231 update_process_status
232 (p_request_id => l_request_id
233 ,p_status => l_dev_status);
234
235 COMMIT;
236 END IF;
237 END LOOP;
238 CLOSE c_req;
239 EXCEPTION
240 WHEN OTHERS THEN
241 log('EXCEPTION OTHERS in verif_status :'||SQLERRM);
242 END;
243
244
245
246 PROCEDURE submission_main_routine
247 (p_workers IN NUMBER,
248 p_batch_id IN NUMBER,
249 p_batch_size IN NUMBER)
250 IS
251 -- Get the job inserted not executed
252 CURSOR c IS
253 SELECT script_name script_name,
254 table_name table_name,
255 worker_id worker_num,
256 order_num order_num,
257 rowid
258 FROM ar_submission_ctrl_gt
259 WHERE status = 'INSERTED'
260 AND batch_id = p_batch_id
261 ORDER BY order_num ASC;
262
263 -- Number of running workers
264 CURSOR c_run_workers IS
265 SELECT COUNT(*)
266 FROM ar_submission_ctrl_gt
267 WHERE status = 'SUBMITTED'
268 AND batch_id = p_batch_id;
269
270 l_num_running_workers NUMBER;
271 l_num_avail_workers NUMBER;
272 l_prv_script_name VARCHAR2(30);
273 l_prv_table_name VARCHAR2(30);
274 l_cpt NUMBER;
275 l_run_worker NUMBER;
276 l_script_name VARCHAR2(30);
277 l_table_name VARCHAR2(30);
278 l_rowid VARCHAR2(2000);
279 l_req_id NUMBER;
280 l_table_owner VARCHAR2(30);
281 l_worker_num NUMBER;
282 l_order_num NUMBER;
283 exception_in_submission EXCEPTION;
284 FUNCTION max_running_worknum
285 (p_table_name IN VARCHAR2,
286 p_batch_id IN NUMBER) RETURN NUMBER
287 IS
288 -- Get the max worker number submitted for all script
289 CURSOR c_max IS
290 SELECT MAX(worker_id) max_worker
291 FROM ar_submission_ctrl_gt
292 WHERE batch_id = p_batch_id
293 AND table_name = p_table_name
294 AND status <> 'INSERTED';
295 l_return NUMBER;
296 BEGIN
297 OPEN c_max;
298 FETCH c_max INTO l_return;
299 IF c_max%NOTFOUND THEN
300 l_return := 0;
301 END IF;
302 CLOSE c_max;
303 RETURN l_return;
304 END;
305 BEGIN
306
307 OPEN c_run_workers;
308 FETCH c_run_workers INTO l_num_running_workers;
309 CLOSE c_run_workers;
310
311 l_num_avail_workers := p_workers - l_num_running_workers;
312
313 IF l_num_avail_workers > 0 THEN
314 l_prv_script_name := 'INIT';
315 l_prv_table_name := 'INIT';
316 l_cpt := 0;
317 OPEN c;
318 LOOP
319 FETCH c INTO l_script_name,
320 l_table_name,
321 l_worker_num,
322 l_order_num,
323 l_rowid;
324 EXIT WHEN c%NOTFOUND;
325 l_cpt := l_cpt + 1;
326 IF l_table_name <> l_prv_table_name THEN
327 l_run_worker := max_running_worknum
328 (p_table_name => l_table_name,
329 p_batch_id => p_batch_id);
330 END IF;
331 l_run_worker := l_run_worker + 1;
332 l_req_id := FND_REQUEST.SUBMIT_REQUEST
333 (application => 'AR',
334 program => 'ARXLAUPGCP',
335 -- description => 'AR XLA Upgrade on demande',
336 start_time => SYSDATE,
337 sub_request => FALSE,
338 argument1 => l_table_name,
339 argument2 => l_script_name,
340 argument3 => p_workers,
341 argument4 => l_worker_num,
342 argument5 => p_batch_size,
343 argument6 => l_order_num);
344 IF l_req_id = 0 THEN
345 log('Error submitting request');
346 log(fnd_message.get);
347 l_prv_script_name := l_script_name;
348 l_prv_table_name := l_table_name;
349 UPDATE ar_submission_ctrl_gt
350 SET request_id = -9,
351 status = 'ABORTED'
352 WHERE rowid = l_rowid;
353 COMMIT;
354 ELSE
355 outandlog('Submitted request ID : ' || l_req_id );
356 log('Request ID : ' || l_req_id);
357 l_prv_script_name := l_script_name;
358 l_prv_table_name := l_table_name;
359 UPDATE ar_submission_ctrl_gt
360 SET request_id = l_req_id,
361 status = 'SUBMITTED'
362 WHERE rowid = l_rowid;
363 COMMIT;
364 END IF;
365 IF l_cpt = l_num_avail_workers THEN
366 COMMIT;
367 EXIT;
368 END IF;
369 END LOOP;
370 CLOSE c;
371 END IF;
372 EXCEPTION
373 WHEN OTHERS THEN
374 log('EXCEPTION OTHERS in submission_main_routine :'||SQLERRM);
375
376 END submission_main_routine;
377
378
379
380
381 PROCEDURE update_process_status
382 (p_request_id IN NUMBER
383 ,p_status IN VARCHAR2)
384 IS
385 BEGIN
386 log('updating ar_submission_ctrl_gt the process status to '||p_status||' for request_id '||p_request_id);
387 UPDATE ar_submission_ctrl_gt
388 SET status = p_status
389 WHERE request_id = p_request_id;
390 EXCEPTION
391 WHEN OTHERS THEN
392 log('EXCEPTION OTHERS in update_process_status :'||SQLERRM);
393 RAISE;
394 END;
395
396 PROCEDURE record_ar_run
397 (p_table_name IN VARCHAR2,
398 p_script_name IN VARCHAR2,
399 p_num_workers IN NUMBER,
400 p_worker_id IN NUMBER,
401 p_batch_size IN VARCHAR2,
402 p_status IN VARCHAR2,
403 p_program_code IN VARCHAR2,
404 p_phase IN NUMBER,
405 p_order_num IN NUMBER,
406 x_return_status IN OUT NOCOPY VARCHAR2,
407 x_return_msg OUT NOCOPY VARCHAR2,
408 x_request_control_id OUT NOCOPY NUMBER)
409 IS
410 CURSOR c_child_req IS
411 SELECT request_control_id
412 FROM xla_upgrade_requests
413 WHERE table_name = p_table_name
414 AND script_name = p_script_name
415 AND workers_num = p_num_workers
416 AND worker_id = p_worker_id
417 AND batch_size = p_batch_size
418 AND application_id = 222;
419 l_request_control_id NUMBER;
420
421 CURSOR c_xla_request IS
422 SELECT request_control_id
423 ,ledger_id
424 ,period_name
425 ,worker_id
426 ,workers_num
427 ,batch_size
428 ,batch_id
429 ,start_date
430 ,end_date
431 FROM xla_upgrade_requests
432 WHERE phase_num = 0
433 AND status_code <> 'S'
434 AND program_code = 'XLA_UPG'
435 AND application_id = 222;
436
437 l_rec xla_upgrade_requests%ROWTYPE;
438 l_ins_rec xla_upgrade_requests%ROWTYPE;
439
440 CURSOR c_ord_num IS
441 SELECT order_num
442 FROM ar_submission_ctrl_gt
443 WHERE table_name = p_table_name
444 AND script_name = p_script_name
445 AND worker_id = p_worker_id;
446 l_ord_num NUMBER;
447
448 no_xla_request EXCEPTION;
449 BEGIN
450 log( message =>'record_ar_run +' );
451 log( message =>' table_name :'||p_table_name );
452 log( message =>' script_name :'||p_script_name);
453 log( message =>' batch_size :'||p_batch_size );
457 log( message =>' p_order_num :'||p_order_num);
454 log( message =>' workers_num :'||p_num_workers);
455 log( message =>' worker_id :'||p_worker_id );
456 log( message =>' status :'||p_status );
458
459 OPEN c_child_req;
460 FETCH c_child_req INTO l_request_control_id;
461 IF c_child_req%NOTFOUND THEN
462 OPEN c_xla_request;
463 FETCH c_xla_request INTO l_rec.parent_request_control_id
464 ,l_rec.ledger_id
465 ,l_rec.period_name
466 ,l_rec.worker_id
467 ,l_rec.workers_num
468 ,l_rec.batch_size
469 ,l_rec.batch_id
470 ,l_rec.start_date
471 ,l_rec.end_date;
472
473 IF c_xla_request%NOTFOUND THEN
474 RAISE no_xla_request;
475 END IF;
476
477 OPEN c_ord_num;
478 FETCH c_ord_num INTO l_ord_num;
479 CLOSE c_ord_num;
480
481 l_ins_rec := l_rec;
482 l_ins_rec.table_name := p_table_name;
483 l_ins_rec.script_name := p_script_name;
484 l_ins_rec.workers_num := p_num_workers;
485 l_ins_rec.worker_id := p_worker_id;
486 l_ins_rec.batch_size := p_batch_size;
487 l_ins_rec.order_num := p_order_num;
488 l_ins_rec.status_code := p_status;
489 l_ins_rec.program_code := p_program_code;
490 l_ins_rec.phase_num := p_phase;
491
492 insert_req_control(l_ins_rec,x_request_control_id);
493 ELSE
494 x_return_msg := 'The xla_upgrade_requests record exist-request_control_id:'||l_request_control_id;
495
496 update_req_control(l_request_control_id,p_status);
497
498 x_request_control_id := l_request_control_id;
499 log( message => x_return_msg);
500 END IF;
501 CLOSE c_child_req;
502 log( message => 'record_ar_run -');
503 EXCEPTION
504 WHEN no_xla_request THEN
505 x_return_status := 'E';
506 x_return_msg := 'Parent process from XLA has to exist';
507 log( message => 'EXCEPTION no_xla_request in record_ar_run');
508 log( message => 'Parent process from XLA has to exist');
509
510 WHEN OTHERS THEN
511 x_return_status := 'U';
512 x_return_msg := SQLERRM;
513 log( message => 'EXCEPTION OTHERS in record_ar_run');
514 log( message => x_return_msg);
515 END;
516
517
518 PROCEDURE insert_req_control
519 (p_ins_rec IN xla_upgrade_requests%ROWTYPE,
520 x_request_control_id OUT NOCOPY NUMBER)
521 IS
522 l_request_control_id NUMBER;
523 BEGIN
524 log( message => 'Inserting a record in ar_reqrest_control');
525 SELECT xla_upgrade_requests_s.NEXTVAL
526 INTO l_request_control_id
527 FROM DUAL;
528 log('request_control_id:'|| l_request_control_id);
529 log('parent_request_control_id:'||p_ins_rec.parent_request_control_id);
530 log('phase :'||p_ins_rec.phase_num);
531 log('program_code :'||p_ins_rec.program_code);
532 log('description :'||p_ins_rec.description);
533 log('status :'||p_ins_rec.status_code);
534 log('ledger_id :'||p_ins_rec.ledger_id);
535 log('period_name :'||p_ins_rec.period_name);
536 log('worker_id :'||p_ins_rec.worker_id);
537 log('workers_num :'||p_ins_rec.workers_num);
538 log('table_name :'||p_ins_rec.table_name);
539 log('script_name :'||p_ins_rec.script_name);
540 log('batch_size :'||p_ins_rec.batch_size);
541 log('batch_id :'||p_ins_rec.batch_id);
542 log('order_num :'||p_ins_rec.order_num);
543 log('start_date :'||p_ins_rec.start_date);
544 log('end_date :'||p_ins_rec.end_date);
545
546 INSERT INTO xla_upgrade_requests(
547 request_control_id
548 ,parent_request_control_id
549 ,phase_num
550 ,program_code
551 ,description
552 ,status_code
553 ,ledger_id
554 ,period_name
555 ,worker_id
556 ,workers_num
557 ,table_name
558 ,script_name
559 ,batch_size
560 ,batch_id
561 ,order_num
562 ,start_date
563 ,end_date
564 ,CREATION_DATE
565 ,CREATED_BY
566 ,LAST_UPDATE_DATE
567 ,LAST_UPDATED_BY
568 ,LAST_UPDATE_LOGIN
569 ,application_id )
570 VALUES (
571 l_request_control_id
572 ,p_ins_rec.parent_request_control_id
573 ,p_ins_rec.phase_num
574 ,p_ins_rec.program_code
575 ,p_ins_rec.description
576 ,p_ins_rec.status_code
577 ,p_ins_rec.ledger_id
578 ,p_ins_rec.period_name
579 ,p_ins_rec.worker_id
580 ,p_ins_rec.workers_num
581 ,p_ins_rec.table_name
582 ,p_ins_rec.script_name
583 ,p_ins_rec.batch_size
584 ,p_ins_rec.batch_id
585 ,p_ins_rec.order_num
586 ,p_ins_rec.start_date
587 ,p_ins_rec.end_date
588 ,SYSDATE
589 ,nvl(FND_GLOBAL.user_id,-1)
590 ,SYSDATE
591 ,nvl(FND_GLOBAL.user_id,-1)
592 ,nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id)
593 ,222 );
594
595 x_request_control_id := l_request_control_id;
596 log( message => 'new record request_control_id :'||l_request_control_id);
597 END;
598
599
600 PROCEDURE update_req_control
601 (p_request_control_id IN NUMBER,
602 p_status IN VARCHAR2)
603 IS
604 BEGIN
605 log( message => 'Updating control record with request_control_id :'||p_request_control_id||'
606 by setting the status to:'||p_status);
607 UPDATE xla_upgrade_requests
608 SET status_code = p_status
609 ,LAST_UPDATE_DATE = SYSDATE
610 ,LAST_UPDATED_BY = nvl(FND_GLOBAL.user_id,-1)
611 ,LAST_UPDATE_LOGIN = nvl(FND_GLOBAL.conc_login_id,FND_GLOBAL.login_id)
615
612 WHERE request_control_id = p_request_control_id
613 AND application_id = 222;
614 END;
616 PROCEDURE upgrade_by_request (
617 errbuf OUT NOCOPY VARCHAR2,
618 retcode OUT NOCOPY VARCHAR2,
619 l_table_name IN VARCHAR2,
620 l_script_name IN VARCHAR2,
621 l_num_workers IN NUMBER,
622 l_worker_id IN NUMBER,
623 l_batch_size IN VARCHAR2,
624 p_order_num IN NUMBER)
625 IS
626 l_batch_id NUMBER;
627 x_return_status VARCHAR2(10);
628 x_return_msg VARCHAR2(2000);
629 x_request_control_id NUMBER;
630 l_bool BOOLEAN;
631 l_status VARCHAR2(30);
632 l_industry VARCHAR2(30);
633 l_schema VARCHAR2(30);
634 l_table_owner VARCHAR2(30);
635 l_program_code VARCHAR2(30);
636 l_phase NUMBER;
637 maintenance_record EXCEPTION;
638 BEGIN
639 retcode := 0;
640 outandlog( message =>'upgrade_by_request for the table :'||l_table_name );
641 outandlog( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
642 outandlog( message =>' l_script_name :'||l_script_name);
643 outandlog( message =>' l_batch_size :'||l_batch_size );
644 outandlog( message =>' l_worker_id :'||l_worker_id);
645 outandlog( message =>' l_table_name :'||l_table_name);
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648
649 IF l_table_name IN ('JL_BR_AR_OCCURRENCE_DOCS_ALL','JL_BR_AR_MC_OCC_DOCS') THEN
650
651 IF fnd_installation.get_app_info('JL',l_status,l_industry,l_schema) THEN
652 l_table_owner := l_schema;
653 ELSE
654 l_table_owner := 'JL';
655 END IF;
656 l_phase := 3;
657 l_program_code := 'JL_UPG';
658
659 --{GIR
660 ELSIF l_table_name IN ('GL_IMPORT_REFERENCES') THEN
661
662 IF fnd_installation.get_app_info('SQLGL',l_status,l_industry,l_schema) THEN
663 l_table_owner := l_schema;
664 ELSE
665 l_table_owner := 'GL';
666 END IF;
667 l_phase := 5;
668 l_program_code := 'GIR_UPG';
669
670 ELSIF l_table_name = 'PSATRX' THEN
671
672 IF fnd_installation.get_app_info('PSA',l_status,l_industry,l_schema) THEN
673 l_table_owner := l_schema;
674 ELSE
675 l_table_owner := 'PSA';
676 END IF;
677 l_phase := 4;
678 l_program_code := 'PSA_UPG';
679 --}
680 ELSE
681
682 IF fnd_installation.get_app_info('AR',l_status,l_industry,l_schema) THEN
683 l_table_owner := l_schema;
684 ELSE
685 l_table_owner := 'AR';
686 END IF;
687
688 IF l_table_name IN ('RA_CUSTOMER_TRX_ALL','AR_CASH_RECEIPTS_ALL'
689 ,'AR_ADJUSTMENTS_ALL','AR_RECEIVABLE_APPLICATIONS_ALL') THEN
690 l_phase := 1;
691 l_program_code := 'AR_UPG';
692 ELSE
693 l_phase := 2;
694 l_program_code := 'MRC_UPG';
695 END IF;
696
697 END IF;
698
699 -- AR records the run as Active
700 record_ar_run
701 (p_table_name => l_table_name,
702 p_script_name => l_script_name,
703 p_num_workers => l_num_workers,
704 p_worker_id => l_worker_id,
705 p_batch_size => l_batch_size,
706 p_status => 'A',
707 p_program_code => l_program_code,
708 p_phase => l_phase,
709 p_order_num => p_order_num,
710 x_return_status => x_return_status,
711 x_return_msg => x_return_msg,
712 x_request_control_id => x_request_control_id);
713
714 IF x_return_status <> 'S' THEN
715 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
716 FND_MESSAGE.SET_TOKEN( 'TEXT', x_return_msg );
717 FND_MSG_PUB.ADD;
718 RAISE maintenance_record;
719 END IF;
720
721 SELECT batch_id
722 INTO l_batch_id
723 FROM xla_upgrade_requests
724 WHERE worker_id = l_worker_id
725 AND script_name = l_script_name
726 AND table_name = l_table_name ;
727
728 SAVEPOINT upgrade_by_request;
729 --
730 -- For AR upgrade package does not support the x_return_status
731 --
732 IF l_table_name = 'RA_CUSTOMER_TRX_ALL' THEN
733
734 IF l_script_name = 'ar120trx_'||l_batch_id THEN
735
736 outandlog( message =>' l_table_name :'||l_table_name );
737 outandlog( message =>' l_schema :'||l_schema);
738 ARP_XLA_UPGRADE.UPGRADE_TRANSACTIONS(l_table_owner => l_table_owner,
739 l_table_name => l_table_name,
740 l_script_name => l_script_name,
741 l_worker_id => l_worker_id,
742 l_num_workers => l_num_workers,
743 l_batch_size => l_batch_size,
744 l_batch_id => l_batch_id,
745 l_action_flag => 'P');
746
747 ELSIF l_script_name = 'ar120br_'||l_batch_id THEN
748
749 outandlog( message =>' l_table_name :'||l_table_name );
750 outandlog( message =>' l_schema :'||l_schema);
751 ARP_XLA_UPGRADE.UPGRADE_BILLS_RECEIVABLE(l_table_owner => l_table_owner,
752 l_table_name => l_table_name,
753 l_script_name => l_script_name,
754 l_worker_id => l_worker_id,
755 l_num_workers => l_num_workers,
756 l_batch_size => l_batch_size,
757 l_batch_id => l_batch_id,
761
758 l_action_flag => 'P');
759
760 END IF;
762 ELSIF l_table_name = 'AR_CASH_RECEIPTS_ALL' THEN
763 outandlog( message =>' l_table_name :'||l_table_name );
764 outandlog( message =>' l_schema :'||l_schema);
765
766 ARP_XLA_UPGRADE.UPGRADE_RECEIPTS(l_table_owner => l_table_owner,
767 l_table_name => l_table_name,
768 l_script_name => l_script_name,
769 l_worker_id => l_worker_id,
770 l_num_workers => l_num_workers,
771 l_batch_size => l_batch_size,
772 l_batch_id => l_batch_id,
773 l_action_flag => 'P');
774
775 ELSIF l_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL' THEN
776 outandlog( message =>' l_table_name :'||l_table_name );
777 outandlog( message =>' l_schema :'||l_schema);
778
779 ARP_XLA_UPGRADE.UPGRADE_CASH_DIST(l_table_owner => l_table_owner,
780 l_table_name => l_table_name,
781 l_script_name => l_script_name,
782 l_worker_id => l_worker_id,
783 l_num_workers => l_num_workers,
784 l_batch_size => l_batch_size,
785 l_batch_id => l_batch_id,
786 l_action_flag => 'P');
787
788
789 ELSIF l_table_name = 'AR_ADJUSTMENTS_ALL' THEN
790 outandlog( message =>' l_table_name :'||l_table_name );
791 outandlog( message =>' l_schema :'||l_schema);
792
793 ARP_XLA_UPGRADE.UPGRADE_ADJUSTMENTS(l_table_owner => l_table_owner,
794 l_table_name => l_table_name,
795 l_script_name => l_script_name,
796 l_worker_id => l_worker_id,
797 l_num_workers => l_num_workers,
798 l_batch_size => l_batch_size,
799 l_batch_id => l_batch_id,
800 l_action_flag => 'P');
801
802
803 ELSIF l_table_name = 'MC_TRANSACTIONS' THEN
804 outandlog( message =>' l_table_name :RA_MC_CUSTOMER_TRX' );
805 outandlog( message =>' l_schema :'||l_schema);
806
807 ARP_MRC_XLA_UPGRADE.UPGRADE_MC_TRANSACTIONS
808 (l_table_owner => l_table_owner,
809 l_table_name => 'RA_MC_CUSTOMER_TRX',
810 l_script_name => l_script_name,
811 l_worker_id => l_worker_id,
812 l_num_workers => l_num_workers,
813 l_batch_size => l_batch_size,
814 l_batch_id => l_batch_id,
815 l_action_flag => 'P');
816
817 ELSIF l_table_name = 'MC_RECEIPTS' THEN
818 outandlog( message =>' l_table_name :AR_MC_CASH_RECEIPTS' );
819 outandlog( message =>' l_schema :'||l_schema);
820
821 ARP_MRC_XLA_UPGRADE.UPGRADE_MC_RECEIPTS
822 (l_table_owner => l_table_owner,
823 l_table_name => 'AR_MC_CASH_RECEIPTS',
824 l_script_name => l_script_name,
825 l_worker_id => l_worker_id,
826 l_num_workers => l_num_workers,
827 l_batch_size => l_batch_size,
828 l_batch_id => l_batch_id,
829 l_action_flag => 'P');
830
831 ELSIF l_table_name = 'MC_ADJUSTMENTS' THEN
832 outandlog( message =>' l_table_name :AR_MC_ADJUSTMENTS' );
833 outandlog( message =>' l_schema :'||l_schema);
834
835 ARP_MRC_XLA_UPGRADE.UPGRADE_MC_ADJUSTMENTS
836 (l_table_owner => l_table_owner,
837 l_table_name => 'AR_MC_ADJUSTMENTS',
838 l_script_name => l_script_name,
839 l_worker_id => l_worker_id,
840 l_num_workers => l_num_workers,
841 l_batch_size => l_batch_size,
842 l_batch_id => l_batch_id,
843 l_action_flag => 'P');
844
845 --
846 -- For JL the upgrade code does support the x_return_status
847 --
848 ELSIF l_table_name = 'JL_BR_AR_OCCURRENCE_DOCS_ALL' THEN
849
850 outandlog( message =>' l_table_name :JL_BR_AR_OCCURRENCE_DOCS_ALL' );
851 outandlog( message =>' l_schema :'||l_schema);
852 JL_BR_AR_BANK_ACCT_PKG.UPGRADE_OCCURRENCES(
853 l_table_owner => l_table_owner,
854 l_table_name => l_table_name,
855 l_script_name => l_script_name,
856 l_worker_id => l_worker_id,
857 l_num_workers => l_num_workers,
858 l_batch_size => l_batch_size,
859 l_batch_id => l_batch_id,
860 l_action_flag => 'R',
861 x_return_status=> x_return_status);
862
863
864 --{ BUG#4645903
865 ELSIF l_table_name = 'JL_BR_AR_MC_OCC_DOCS' THEN
866
867 outandlog( message =>' l_table_name :JL_BR_AR_OCCURRENCE_DOCS_ALL' );
868 outandlog( message =>' l_schema :'||l_schema);
869 JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES(
870 l_table_owner => l_table_owner,
871 l_table_name => 'JL_BR_AR_OCCURENCE_DOCS_ALL',
872 l_script_name => l_script_name,
873 l_worker_id => l_worker_id,
874 l_num_workers => l_num_workers,
875 l_batch_size => l_batch_size,
876 l_batch_id => l_batch_id,
877 l_action_flag => 'R',
881
878 x_return_status=> x_return_status);
879 --}
880 ELSIF l_table_name = 'PSATRX' THEN
882 --{PSATRX
883 ar_upg_psa_dist_pkg.UPGRADE_TRANSACTIONS(
884 l_table_owner => l_table_owner,
885 l_table_name => 'PSA_MF_TRX_DIST_ALL',
886 l_script_name => l_script_name,
887 l_worker_id => l_worker_id,
888 l_num_workers => l_num_workers,
889 l_batch_size => l_batch_size,
890 l_batch_id => l_batch_id,
891 l_action_flag => 'P');
892
893 --{GL Links
894 ELSIF l_table_name = 'GL_IMPORT_REFERENCES' THEN
895 outandlog( message =>' l_table_name :'||l_table_name );
896 outandlog( message =>' l_schema :'||l_schema);
897
898 ARP_XLA_UPGRADE.update_gl_sla_link(l_table_owner => l_table_owner,
899 l_table_name => l_table_name,
900 l_script_name => l_script_name,
901 l_worker_id => l_worker_id,
902 l_num_workers => l_num_workers,
903 l_batch_size => l_batch_size,
904 l_batch_id => l_batch_id,
905 l_action_flag => 'P');
906 --}
907
908 END IF;
909
910 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
912 END IF;
913
914
915 -- Updating record as successfull
916 update_req_control
917 (p_request_control_id => x_request_control_id,
918 p_status => 'S');
919
920 outandlog( message =>' Ending at ' || to_char(SYSDATE, 'HH24:MI:SS') );
921 outandlog( message =>'Worker has completed successfully');
922
923 EXCEPTION
924 WHEN maintenance_record THEN
925 outandlog('Error: ' || FND_MESSAGE.GET);
926 retcode := 1;
927 errbuf := errbuf || logerror;
928 outandlog('Aborting concurrent program execution');
929 -- FND_FILE.close;
930
931 WHEN FND_API.G_EXC_ERROR THEN
932 ROLLBACK TO upgrade_by_request;
933 outandlog('Error: ' || FND_MESSAGE.GET);
934 retcode := 1;
935 errbuf := errbuf || logerror;
936 outandlog('Aborting concurrent program execution');
937 -- FND_FILE.close;
938
939
940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
941 --JL raises FND api error
942 ROLLBACK TO upgrade_by_request;
943 outandlog('Error: ' || FND_MESSAGE.GET);
944 retcode := 1;
945 errbuf := errbuf || logerror;
946 outandlog('Aborting concurrent program execution');
947 -- FND_FILE.close;
948
949 WHEN OTHERS THEN
950 --AR and MRC and PSA raises when others
951 outandlog('Error: ' || FND_MESSAGE.GET);
952 log('SQL Error ' || SQLERRM);
953 retcode := 1;
954 errbuf := errbuf || logerror || SQLERRM;
955 outandlog('Aborting concurrent program execution');
956 ROLLBACK TO upgrade_by_request;
957 -- FND_FILE.close;
958
959 END;
960
961
962
963
964
965 PROCEDURE ins_exist_process_again
966 (p_product_name IN VARCHAR2,
967 p_batch_id IN NUMBER,
968 p_parent_req_control_id IN NUMBER,
969 x_nb_inserted OUT NOCOPY NUMBER)
970 IS
971 l_program_code VARCHAR2(30);
972 l_phase NUMBER;
973 BEGIN
974 IF p_product_name = 'AR' THEN
975 l_program_code := 'AR_UPG';
976 l_phase := 1;
977 ELSIF p_product_name = 'MRC' THEN
978 l_program_code := 'MRC_UPG';
979 l_phase := 2;
980 ELSIF p_product_name = 'JL' THEN
981 l_program_code := 'JL_UPG';
982 l_phase := 3;
983 ELSIF p_product_name = 'PSA' THEN
984 l_program_code := 'PSA_UPG';
985 l_phase := 4;
986 --{GIR
987 ELSIF p_product_name = 'GL' THEN
988 l_program_code := 'GIR_UPG';
989 l_phase := 5;
990 --}
991 END IF;
992
993 INSERT INTO ar_submission_ctrl_gt
994 (worker_id , --worker_number
995 batch_id , --batch_id
996 script_name , --script_name
997 status , --INSERTED, SUBMITTED, (NORMAL, ERROR, WARNING, CANCELLED, TERMINATED)
998 order_num , --order helper number
999 request_id , --request_id
1000 table_name ) --table_name
1001 SELECT worker_id,
1002 batch_id,
1003 script_name,
1004 'INSERTED',
1005 order_num,
1006 request_control_id,
1007 table_name
1008 FROM xla_upgrade_requests
1009 WHERE batch_id = p_batch_id
1010 AND parent_request_control_id = p_parent_req_control_id
1011 AND phase_num = l_phase
1012 AND program_code = l_program_code
1013 AND status_code <> 'S'
1014 AND application_id = 222;
1015
1016 x_nb_inserted := SQL%ROWCOUNT;
1017 END;
1018
1019
1020
1021 PROCEDURE ins_subs_for_one_script
1022 (p_script_name IN VARCHAR2,
1023 p_batch_id IN NUMBER,
1024 p_order_num IN VARCHAR2,
1025 p_table_name IN VARCHAR2,
1026 p_workers_num IN NUMBER)
1027 IS
1028 i NUMBER;
1029 tab_worker_number DBMS_SQL.NUMBER_TABLE;
1030 tab_batch_id DBMS_SQL.NUMBER_TABLE;
1031 tab_script_name DBMS_SQL.VARCHAR2_TABLE;
1032 tab_status DBMS_SQL.VARCHAR2_TABLE;
1033 tab_order DBMS_SQL.NUMBER_TABLE;
1034 tab_request_id DBMS_SQL.NUMBER_TABLE;
1038 p_batch_id => p_batch_id);
1035 tab_table_name DBMS_SQL.VARCHAR2_TABLE;
1036 BEGIN
1037 ins_120_ctl(p_script_name => p_script_name,
1039
1040 FOR i IN 1 .. p_workers_num LOOP
1041 tab_worker_number(i) := i;
1042 tab_batch_id(i) := p_batch_id;
1043 tab_script_name(i) := p_script_name;
1044 tab_status(i) := 'INSERTED';
1045 tab_order(i) := p_order_num;
1046 tab_request_id(i) := NULL;
1047 tab_table_name(i) := p_table_name;
1048 END LOOP;
1049
1050 FORALL i IN tab_worker_number.FIRST .. tab_worker_number.LAST
1051 INSERT INTO ar_submission_ctrl_gt
1052 (worker_id , --worker_number
1053 batch_id , --batch_id
1054 script_name , --script_name
1055 status , --INSERTED, SUBMITTED, (NORMAL, ERROR, WARNING, CANCELLED, TERMINATED)
1056 order_num , --order helper number
1057 request_id , --request_id
1058 table_name ) --table_name
1059 VALUES
1060 (tab_worker_number(i),
1061 tab_batch_id(i),
1062 tab_script_name(i),
1063 tab_status(i),
1064 tab_order(i),
1065 tab_request_id(i),
1066 tab_table_name(i));
1067 END;
1068
1069
1070
1071 PROCEDURE ins_new_process
1072 (p_product_name IN VARCHAR2,
1073 p_batch_id IN NUMBER,
1074 p_parent_req_control_id IN NUMBER,
1075 p_workers_num IN NUMBER,
1076 x_nb_inserted OUT NOCOPY NUMBER)
1077 IS
1078 l_script_name VARCHAR2(30);
1079 BEGIN
1080 x_nb_inserted := 0;
1081
1082 IF p_product_name = 'AR' THEN
1083
1084 l_script_name := 'ar120trx_'||p_batch_id;
1085
1086 ins_subs_for_one_script
1087 (p_script_name => l_script_name,
1088 p_batch_id => p_batch_id,
1089 p_order_num => 1,
1090 p_table_name => 'RA_CUSTOMER_TRX_ALL',
1091 p_workers_num => p_workers_num);
1092
1093 l_script_name := 'ar120br_'||p_batch_id;
1094
1095 ins_subs_for_one_script
1096 (p_script_name => l_script_name,
1097 p_batch_id => p_batch_id,
1098 p_order_num => 2,
1099 p_table_name => 'RA_CUSTOMER_TRX_ALL',
1100 p_workers_num => p_workers_num);
1101
1102 l_script_name := 'ar120rcp_'||p_batch_id;
1103
1104 ins_subs_for_one_script
1105 (p_script_name => l_script_name,
1106 p_batch_id => p_batch_id,
1107 p_order_num => 3,
1108 p_table_name => 'AR_CASH_RECEIPTS_ALL',
1109 p_workers_num => p_workers_num);
1110
1111 l_script_name := 'ar120cash_'||p_batch_id;
1112
1113 ins_subs_for_one_script
1114 (p_script_name => l_script_name,
1115 p_batch_id => p_batch_id,
1116 p_order_num => 4,
1117 p_table_name => 'AR_RECEIVABLE_APPLICATIONS_ALL',
1118 p_workers_num => p_workers_num);
1119
1120 l_script_name := 'ar120adj_'||p_batch_id;
1121
1122 ins_subs_for_one_script
1123 (p_script_name => l_script_name,
1124 p_batch_id => p_batch_id,
1125 p_order_num => 5,
1126 p_table_name => 'AR_ADJUSTMENTS_ALL',
1127 p_workers_num => p_workers_num);
1128
1129 x_nb_inserted := 5 * p_workers_num;
1130
1131 ELSIF p_product_name = 'MRC' THEN
1132
1133 IF mrc_run_required = 'Y' THEN
1134
1135 l_script_name := 'armc120trx_'||p_batch_id;
1136
1137 ins_subs_for_one_script
1138 (p_script_name => l_script_name,
1139 p_batch_id => p_batch_id,
1140 p_order_num => 1,
1141 p_table_name => 'MC_TRANSACTIONS',
1142 p_workers_num => p_workers_num);
1143
1144 l_script_name := 'armc120rcp_'||p_batch_id;
1145
1146 ins_subs_for_one_script
1147 (p_script_name => l_script_name,
1148 p_batch_id => p_batch_id,
1149 p_order_num => 2,
1150 p_table_name => 'MC_RECEIPTS',
1151 p_workers_num => p_workers_num);
1152
1153 l_script_name := 'armc120adj_'||p_batch_id;
1154
1155 ins_subs_for_one_script
1156 (p_script_name => l_script_name,
1157 p_batch_id => p_batch_id,
1158 p_order_num => 3,
1159 p_table_name => 'MC_ADJUSTMENTS',
1160 p_workers_num => p_workers_num);
1161
1162 x_nb_inserted := 3 * p_workers_num;
1163
1164 END IF;
1165
1166 ELSIF p_product_name = 'JL' THEN
1167
1168 IF JL_BR_AR_BANK_ACCT_PKG.check_if_upgrade_occs THEN
1169
1170 l_script_name := 'jl120occ_'||p_batch_id;
1171
1172 ins_subs_for_one_script
1173 (p_script_name => l_script_name,
1174 p_batch_id => p_batch_id,
1175 p_order_num => 1,
1176 p_table_name => 'JL_BR_AR_OCCURRENCE_DOCS_ALL',
1177 p_workers_num => p_workers_num);
1178
1179 x_nb_inserted := p_workers_num;
1180
1181
1182 END IF;
1183
1184 IF JL_BR_AR_BANK_ACCT_PKG.check_if_upgrade_occs AND
1185 mrc_run_required = 'Y'
1186 THEN
1187
1188 l_script_name := 'jl120mcocc_'||p_batch_id;
1189
1190 ins_subs_for_one_script
1191 (p_script_name => l_script_name,
1192 p_batch_id => p_batch_id,
1193 p_order_num => 2,
1194 p_table_name => 'JL_BR_AR_MC_OCC_DOCS',
1195 p_workers_num => p_workers_num);
1196
1200
1197 x_nb_inserted := x_nb_inserted + p_workers_num;
1198 END IF;
1199
1201 ELSIF p_product_name = 'PSA' THEN
1202
1203 l_script_name := 'psatrx_'||p_batch_id;
1204
1205 ins_subs_for_one_script
1206 (p_script_name => l_script_name,
1207 p_batch_id => p_batch_id,
1208 p_order_num => 1,
1209 p_table_name => 'PSATRX',
1210 p_workers_num => p_workers_num);
1211
1212 x_nb_inserted := p_workers_num;
1213
1214 --{GIR
1215 ELSIF p_product_name = 'GL' THEN
1216 l_script_name := 'ar120gir_'||p_batch_id;
1217
1218 ins_subs_for_one_script
1219 (p_script_name => l_script_name,
1220 p_batch_id => p_batch_id,
1221 p_order_num => 1,
1222 p_table_name => 'GL_IMPORT_REFERENCES',
1223 p_workers_num => p_workers_num);
1224
1225 x_nb_inserted := p_workers_num;
1226 --}
1227
1228 END IF;
1229
1230 END;
1231
1232
1233
1234
1235
1236 PROCEDURE check_run_data
1237 (p_batch_id IN NUMBER,
1238 x_return_status OUT NOCOPY VARCHAR2,
1239 x_return_msg OUT NOCOPY VARCHAR2)
1240 IS
1241 CURSOR c_req(p_batch_id IN NUMBER) IS
1242 SELECT status,
1243 request_id
1244 FROM ar_submission_ctrl_gt
1245 WHERE batch_id = p_batch_id
1246 AND status <> 'NORMAL';
1247 l_status VARCHAR2(30);
1248 l_request_id NUMBER;
1249 BEGIN
1250 x_return_status := 'S';
1251 OPEN c_req(p_batch_id);
1252 FETCH c_req INTO l_status, l_request_id;
1253 IF c_req%NOTFOUND THEN
1254 x_return_status := 'S';
1255 ELSE
1256 fnd_message.set_name('AR','AR_XLA_EXIST_REQ_ERROR');
1257 fnd_message.set_token('REQUEST_ID',l_request_id);
1258 x_return_msg := fnd_message.get;
1259 outandlog(x_return_msg);
1260 x_return_status := 'E';
1261 END IF;
1262 CLOSE c_req;
1263 END;
1264
1265
1266
1267
1268 PROCEDURE ar_master_upg_product
1269 (p_parent_req_control_id IN NUMBER,
1270 p_batch_id IN NUMBER,
1271 p_reexecution IN VARCHAR2 DEFAULT 'N',
1272 p_product_name IN VARCHAR2,
1273 p_workers_num IN NUMBER,
1274 p_batch_size IN NUMBER,
1275 x_return_msg OUT NOCOPY VARCHAR2,
1276 x_return_status OUT NOCOPY VARCHAR2 )
1277 IS
1278 l_result VARCHAR2(1) := 'N';
1279 incremental_cnt NUMBER := 0;
1280 x_nb_inserted NUMBER := 0;
1281 execution_req VARCHAR2(1);
1282 nothing_to_run EXCEPTION;
1283 finish EXCEPTION;
1284
1285 CURSOR c(p_batch_id IN NUMBER)
1286 IS
1287 SELECT 'Y'
1288 FROM ar_submission_ctrl_gt
1289 WHERE status IN ('INSERTED','SUBMITTED')
1290 AND batch_id = p_batch_id;
1291
1292
1293
1294 BEGIN
1295 log( message =>'ar_master_upg_product + ' );
1296 log( message =>' Running for the product:'||p_product_name);
1297 log( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1298 log( message =>' p_workers_num :'||p_workers_num);
1299 log( message =>' Is this reexecution :'||p_reexecution);
1300 log( message =>' p_batch_size :'||p_batch_size);
1301 log( message =>' p_workers_num :'||p_workers_num);
1302
1303 x_return_status := 'S';
1304
1305 IF p_workers_num < 1 THEN
1306 RAISE nothing_to_run;
1307 END IF;
1308
1309 IF p_reexecution = 'Y' THEN
1310
1311 IF p_product_name = 'AR' THEN
1312
1313 ins_exist_process_again
1314 (p_product_name => 'AR',
1315 p_batch_id => p_batch_id,
1316 p_parent_req_control_id => p_parent_req_control_id,
1317 x_nb_inserted => x_nb_inserted);
1318
1319 ELSIF p_product_name = 'MRC' THEN
1320
1321 ins_exist_process_again
1322 (p_product_name => 'MRC',
1323 p_batch_id => p_batch_id,
1324 p_parent_req_control_id => p_parent_req_control_id,
1325 x_nb_inserted => x_nb_inserted);
1326
1327 ELSIF p_product_name = 'JL' THEN
1328
1329 ins_exist_process_again
1330 (p_product_name => 'JL',
1331 p_batch_id => p_batch_id,
1332 p_parent_req_control_id => p_parent_req_control_id,
1333 x_nb_inserted => x_nb_inserted);
1334
1335 ELSIF p_product_name = 'PSA' THEN
1336
1337 ins_exist_process_again
1338 (p_product_name => 'PSA',
1339 p_batch_id => p_batch_id,
1340 p_parent_req_control_id => p_parent_req_control_id,
1341 x_nb_inserted => x_nb_inserted);
1342
1343 --{GIR
1344 ELSIF p_product_name = 'GL' THEN
1345
1346 ins_exist_process_again
1347 (p_product_name => 'GL',
1348 p_batch_id => p_batch_id,
1349 p_parent_req_control_id => p_parent_req_control_id,
1350 x_nb_inserted => x_nb_inserted);
1351 --}
1352
1353 END IF;
1354
1355
1356 IF x_nb_inserted > 0 THEN
1357 execution_req := 'Y';
1358 ELSE
1359 execution_req := 'N';
1360 END IF;
1361
1362 ELSE
1363
1364 IF p_product_name = 'AR' THEN
1365
1366 ins_new_process
1367 (p_product_name => 'AR',
1368 p_batch_id => p_batch_id,
1369 p_parent_req_control_id => p_parent_req_control_id,
1370 p_workers_num => p_workers_num,
1374 ELSIF p_product_name = 'MRC' THEN
1371 x_nb_inserted => x_nb_inserted);
1372
1373
1375
1376 ins_new_process
1377 (p_product_name => 'MRC',
1378 p_batch_id => p_batch_id,
1379 p_parent_req_control_id => p_parent_req_control_id,
1380 p_workers_num => p_workers_num,
1381 x_nb_inserted => x_nb_inserted);
1382
1383 ELSIF p_product_name = 'JL' THEN
1384
1385 ins_new_process
1386 (p_product_name => 'JL',
1387 p_batch_id => p_batch_id,
1388 p_parent_req_control_id => p_parent_req_control_id,
1389 p_workers_num => p_workers_num,
1390 x_nb_inserted => x_nb_inserted);
1391
1392 ELSIF p_product_name = 'PSA' THEN
1393
1394 ins_new_process
1395 (p_product_name => 'PSA',
1396 p_batch_id => p_batch_id,
1397 p_parent_req_control_id => p_parent_req_control_id,
1398 p_workers_num => p_workers_num,
1399 x_nb_inserted => x_nb_inserted);
1400
1401 --{GIR
1402 ELSIF p_product_name = 'GL' THEN
1403
1404 ins_new_process
1405 (p_product_name => 'GL',
1406 p_batch_id => p_batch_id,
1407 p_parent_req_control_id => p_parent_req_control_id,
1408 p_workers_num => p_workers_num,
1409 x_nb_inserted => x_nb_inserted);
1410
1411 --}
1412
1413 END IF;
1414
1415 IF x_nb_inserted > 0 THEN
1416 execution_req := 'Y';
1417 ELSE
1418 execution_req := 'N';
1419 END IF;
1420
1421 END IF;
1422
1423
1424 IF execution_req = 'Y' THEN
1425
1426 log( message =>' In the child requests submission loop');
1427
1428 LOOP
1429
1430 OPEN c(p_batch_id);
1431 FETCH c INTO l_result;
1432 IF c%NOTFOUND THEN
1433 RAISE finish;
1434 END IF;
1435 CLOSE c;
1436
1437 submission_main_routine
1438 (p_workers => p_workers_num,
1439 p_batch_id => p_batch_id,
1440 p_batch_size => p_batch_size);
1441
1442 verif_status
1443 (p_batch_id => p_batch_id);
1444
1445 END LOOP;
1446
1447 END IF;
1448 log( message =>'ar_master_upg_product -' );
1449
1450 EXCEPTION
1451 WHEN nothing_to_run THEN
1452 log('number of workers ='||p_workers_num);
1453 log('nothing to run');
1454 log( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1455 log( message =>'ar_master_upg_product - ' );
1456 x_return_msg := 'number of workers ='||p_workers_num||' nothing to run';
1457 WHEN finish THEN
1458 log('Raise Finish');
1459 IF c%ISOPEN THEN
1460 close c;
1461 END IF;
1462 -- Add XLA update status call
1463 check_run_data
1464 (p_batch_id => p_batch_id,
1465 x_return_status => x_return_status,
1466 x_return_msg => x_return_msg);
1467 log( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1468 log( message =>'ar_master_upg_product -' );
1469
1470
1471 END;
1472
1473
1474
1475
1476 PROCEDURE validate_xla_upg_data
1477 (p_ledger_id IN NUMBER,
1478 x_start_date OUT NOCOPY DATE,
1479 x_end_date OUT NOCOPY DATE,
1480 x_return_status OUT NOCOPY VARCHAR2,
1481 x_return_msg OUT NOCOPY VARCHAR2)
1482 IS
1483 CURSOR c_xla_dates IS
1484 SELECT start_date,
1485 end_date
1486 FROM xla_upgrade_dates
1487 WHERE ledger_id = p_ledger_id;
1488
1489
1490 CURSOR c_ar_xla_dates IS
1491 SELECT b.start_date,
1492 b.end_date
1493 FROM xla_upgrade_requests b
1494 WHERE b.ledger_id = p_ledger_id
1495 AND b.program_code = 'XLA_UPG'
1496 AND b.status_code = 'S'
1497 AND b.phase_num = 0
1498 AND b.application_id = 222
1499 AND EXISTS (SELECT NULL
1500 FROM xla_upgrade_dates a
1501 WHERE (a.start_date BETWEEN b.start_date AND b.end_date OR
1502 a.end_date BETWEEN b.start_date AND b.end_date)
1503 AND a.ledger_id = b.ledger_id);
1504
1505
1506 l_start_date DATE;
1507 l_end_date DATE;
1508 nb_of_xla_dates_row NUMBER := 0;
1509 l_overlapp VARCHAR2(1);
1510 nothing_to_run EXCEPTION;
1511 more_than_one_xla_dates EXCEPTION;
1512 start_greater_than_end EXCEPTION;
1513 overlapp EXCEPTION;
1514 BEGIN
1515 x_return_status := 'S';
1516
1517 OPEN c_xla_dates;
1518 LOOP
1519 FETCH c_xla_dates INTO l_start_date, l_end_date;
1520 EXIT WHEN c_xla_dates%NOTFOUND;
1521 nb_of_xla_dates_row := nb_of_xla_dates_row + 1;
1522 END LOOP;
1523 CLOSE c_xla_dates;
1524
1525 IF nb_of_xla_dates_row = 0 THEN
1526 RAISE nothing_to_run;
1527 ELSIF nb_of_xla_dates_row > 1 THEN
1528 RAISE more_than_one_xla_dates;
1529 END IF;
1530
1531 IF l_start_date > l_end_date THEN
1532 RAISE start_greater_than_end;
1533 END IF;
1534
1535 OPEN c_ar_xla_dates;
1536 FETCH c_ar_xla_dates INTO
1537 l_start_date,
1538 l_end_date ;
1539 IF c_ar_xla_dates%FOUND THEN
1540 l_overlapp := 'Y';
1541 END IF;
1542 CLOSE c_ar_xla_dates;
1543
1544 IF l_overlapp = 'Y' THEN
1545 RAISE overlapp;
1546 END IF;
1547
1548 x_start_date := l_start_date;
1549 x_end_date := l_end_date;
1550
1551 EXCEPTION
1555 WHEN more_than_one_xla_dates THEN
1552 WHEN nothing_to_run THEN
1553 x_return_status := 'N';
1554 x_return_msg := 'AR_XLA_UPGRADE_DATE_MISSING';
1556 x_return_status := 'E';
1557 x_return_msg := 'MORE_THAN_ONE_XLA_UPG_DATE';
1558 WHEN start_greater_than_end THEN
1559 x_return_status := 'E';
1560 x_return_msg := 'XLA_END_GREATER_THAN_START';
1561 WHEN overlapp THEN
1562 x_return_status := 'E';
1563 x_return_msg := 'AR_XLA_UPGRADE_DATE_OVERLAP';
1564 END;
1565
1566
1567
1568
1569
1570
1571
1572 PROCEDURE lauch_and_relaunch
1573 (p_start_product IN VARCHAR2,
1574 p_reexec IN VARCHAR2,
1575 p_parent_req_control_id IN NUMBER,
1576 p_batch_id IN NUMBER,
1577 p_workers_num IN NUMBER,
1578 p_batch_size IN NUMBER,
1579 x_return_status OUT NOCOPY VARCHAR2,
1580 x_return_msg OUT NOCOPY VARCHAR2)
1581 IS
1582 l_current_product VARCHAR2(30);
1583 x_request_control_id NUMBER;
1584 stop_at_product EXCEPTION;
1585
1586 BEGIN
1587 l_current_product := p_start_product;
1588
1589 ar_master_upg_product
1590 (p_parent_req_control_id => p_parent_req_control_id,
1591 p_batch_id => p_batch_id,
1592 p_reexecution => p_reexec,
1593 p_product_name => p_start_product,
1594 p_workers_num => p_workers_num,
1595 p_batch_size => p_batch_size,
1596 x_return_msg => x_return_msg,
1597 x_return_status => x_return_status);
1598
1599
1600 IF x_return_status <> 'S' THEN
1601 RAISE stop_at_product;
1602 END IF;
1603
1604 IF p_start_product = 'AR' THEN
1605
1606 DELETE FROM ar_submission_ctrl_gt;
1607
1608 l_current_product := 'MRC';
1609
1610 ar_master_upg_product
1611 (p_parent_req_control_id => x_request_control_id,
1612 p_batch_id => p_batch_id,
1613 p_reexecution => 'N',
1614 p_product_name => l_current_product,
1615 p_workers_num => p_workers_num,
1616 p_batch_size => p_batch_size,
1617 x_return_msg => x_return_msg,
1618 x_return_status => x_return_status);
1619
1620
1621 IF x_return_status <> 'S' THEN
1622 RAISE stop_at_product;
1623 END IF;
1624
1625
1626 DELETE FROM ar_submission_ctrl_gt;
1627
1628 l_current_product := 'JL';
1629
1630 ar_master_upg_product
1631 (p_parent_req_control_id => x_request_control_id,
1632 p_batch_id => p_batch_id,
1633 p_reexecution => 'N',
1634 p_product_name => l_current_product,
1635 p_workers_num => p_workers_num,
1636 p_batch_size => p_batch_size,
1637 x_return_msg => x_return_msg,
1638 x_return_status => x_return_status);
1639
1640
1641 IF x_return_status <> 'S' THEN
1642 RAISE stop_at_product;
1643 END IF;
1644
1645 DELETE FROM ar_submission_ctrl_gt;
1646
1647 l_current_product := 'PSA';
1648
1649 ar_master_upg_product
1650 (p_parent_req_control_id => x_request_control_id,
1651 p_batch_id => p_batch_id,
1652 p_reexecution => 'N',
1653 p_product_name => l_current_product,
1654 p_workers_num => p_workers_num,
1655 p_batch_size => p_batch_size,
1656 x_return_msg => x_return_msg,
1657 x_return_status => x_return_status);
1658
1659
1660 IF x_return_status <> 'S' THEN
1661 RAISE stop_at_product;
1662 END IF;
1663
1664 DELETE FROM ar_submission_ctrl_gt;
1665
1666 l_current_product := 'GL';
1667
1668 ar_master_upg_product
1669 (p_parent_req_control_id => x_request_control_id,
1670 p_batch_id => p_batch_id,
1671 p_reexecution => 'N',
1672 p_product_name => l_current_product,
1673 p_workers_num => p_workers_num,
1674 p_batch_size => p_batch_size,
1675 x_return_msg => x_return_msg,
1676 x_return_status => x_return_status);
1677
1678 IF x_return_status <> 'S' THEN
1679 RAISE stop_at_product;
1680 END IF;
1681
1682 /* ELSIF p_start_product = 'GL' THEN
1683
1684 DELETE FROM ar_submission_ctrl_gt;
1685
1686 l_current_product := 'MRC';
1687
1688 ar_master_upg_product
1689 (p_parent_req_control_id => x_request_control_id,
1690 p_batch_id => p_batch_id,
1691 p_reexecution => 'N',
1692 p_product_name => l_current_product,
1693 p_workers_num => p_workers_num,
1694 p_batch_size => p_batch_size,
1695 x_return_msg => x_return_msg,
1696 x_return_status => x_return_status);
1697
1698
1699 IF x_return_status <> 'S' THEN
1700 RAISE stop_at_product;
1701 END IF;
1702
1703
1704 DELETE FROM ar_submission_ctrl_gt;
1705
1706 l_current_product := 'JL';
1707
1708 ar_master_upg_product
1709 (p_parent_req_control_id => x_request_control_id,
1710 p_batch_id => p_batch_id,
1711 p_reexecution => 'N',
1712 p_product_name => l_current_product,
1713 p_workers_num => p_workers_num,
1717
1714 p_batch_size => p_batch_size,
1715 x_return_msg => x_return_msg,
1716 x_return_status => x_return_status);
1718
1719 IF x_return_status <> 'S' THEN
1720 RAISE stop_at_product;
1721 END IF;
1722
1723 DELETE FROM ar_submission_ctrl_gt;
1724
1725 l_current_product := 'PSA';
1726
1727 ar_master_upg_product
1728 (p_parent_req_control_id => x_request_control_id,
1729 p_batch_id => p_batch_id,
1730 p_reexecution => 'N',
1731 p_product_name => l_current_product,
1732 p_workers_num => p_workers_num,
1733 p_batch_size => p_batch_size,
1734 x_return_msg => x_return_msg,
1735 x_return_status => x_return_status);
1736
1737
1738 IF x_return_status <> 'S' THEN
1739 RAISE stop_at_product;
1740 END IF; */
1741
1742
1743 ELSIF p_start_product = 'MRC' THEN
1744
1745
1746 DELETE FROM ar_submission_ctrl_gt;
1747
1748 l_current_product := 'JL';
1749
1750 ar_master_upg_product
1751 (p_parent_req_control_id => x_request_control_id,
1752 p_batch_id => p_batch_id,
1753 p_reexecution => 'N',
1754 p_product_name => l_current_product,
1755 p_workers_num => p_workers_num,
1756 p_batch_size => p_batch_size,
1757 x_return_msg => x_return_msg,
1758 x_return_status => x_return_status);
1759
1760
1761 IF x_return_status <> 'S' THEN
1762 RAISE stop_at_product;
1763 END IF;
1764
1765 DELETE FROM ar_submission_ctrl_gt;
1766
1767 l_current_product := 'PSA';
1768
1769 ar_master_upg_product
1770 (p_parent_req_control_id => x_request_control_id,
1771 p_batch_id => p_batch_id,
1772 p_reexecution => 'N',
1773 p_product_name => l_current_product,
1774 p_workers_num => p_workers_num,
1775 p_batch_size => p_batch_size,
1776 x_return_msg => x_return_msg,
1777 x_return_status => x_return_status);
1778
1779
1780 IF x_return_status <> 'S' THEN
1781 RAISE stop_at_product;
1782 END IF;
1783
1784 DELETE FROM ar_submission_ctrl_gt;
1785
1786 l_current_product := 'GL';
1787
1788 ar_master_upg_product
1789 (p_parent_req_control_id => x_request_control_id,
1790 p_batch_id => p_batch_id,
1791 p_reexecution => 'N',
1792 p_product_name => l_current_product,
1793 p_workers_num => p_workers_num,
1794 p_batch_size => p_batch_size,
1795 x_return_msg => x_return_msg,
1796 x_return_status => x_return_status);
1797
1798 IF x_return_status <> 'S' THEN
1799 RAISE stop_at_product;
1800 END IF;
1801
1802 ELSIF p_start_product = 'JL' THEN
1803
1804 DELETE FROM ar_submission_ctrl_gt;
1805
1806 l_current_product := 'PSA';
1807
1808 ar_master_upg_product
1809 (p_parent_req_control_id => x_request_control_id,
1810 p_batch_id => p_batch_id,
1811 p_reexecution => 'N',
1812 p_product_name => l_current_product,
1813 p_workers_num => p_workers_num,
1814 p_batch_size => p_batch_size,
1815 x_return_msg => x_return_msg,
1816 x_return_status => x_return_status);
1817
1818 IF x_return_status <> 'S' THEN
1819 RAISE stop_at_product;
1820 END IF;
1821
1822 DELETE FROM ar_submission_ctrl_gt;
1823
1824 l_current_product := 'GL';
1825
1826 ar_master_upg_product
1827 (p_parent_req_control_id => x_request_control_id,
1828 p_batch_id => p_batch_id,
1829 p_reexecution => 'N',
1830 p_product_name => l_current_product,
1831 p_workers_num => p_workers_num,
1832 p_batch_size => p_batch_size,
1833 x_return_msg => x_return_msg,
1834 x_return_status => x_return_status);
1835
1836 IF x_return_status <> 'S' THEN
1837 RAISE stop_at_product;
1838 END IF;
1839
1840 ELSIF p_start_product = 'PSA' THEN
1841
1842 DELETE FROM ar_submission_ctrl_gt;
1843
1844 l_current_product := 'GL';
1845
1846 ar_master_upg_product
1847 (p_parent_req_control_id => x_request_control_id,
1848 p_batch_id => p_batch_id,
1849 p_reexecution => 'N',
1850 p_product_name => l_current_product,
1851 p_workers_num => p_workers_num,
1852 p_batch_size => p_batch_size,
1853 x_return_msg => x_return_msg,
1854 x_return_status => x_return_status);
1855
1856 IF x_return_status <> 'S' THEN
1857 RAISE stop_at_product;
1858 END IF;
1859
1860 END IF;
1861
1862 EXCEPTION
1863 WHEN stop_at_product THEN
1864 x_return_status := 'E';
1865 x_return_msg := x_return_msg||'
1866 Child processes incomplete for '||l_current_product;
1867 log(x_return_msg);
1868
1869 END;
1870
1871
1872 PROCEDURE ar_master_upg
1873 (errbuf OUT NOCOPY VARCHAR2,
1874 retcode OUT NOCOPY VARCHAR2,
1875 p_ledger_id IN NUMBER,
1876 p_period_name IN VARCHAR2,
1880 CURSOR c_batch_id IS
1877 p_workers_num IN NUMBER,
1878 p_batch_size IN NUMBER)
1879 IS
1881 SELECT xla_upg_batches_s.NEXTVAL
1882 FROM dual;
1883
1884
1885 CURSOR exist_xla_active(p_ledger_id IN NUMBER)
1886 IS
1887 SELECT *
1888 FROM xla_upgrade_requests
1889 WHERE program_code = 'XLA_UPG'
1890 AND phase_num = 0
1891 AND status_code = 'A'
1892 AND ledger_id = p_ledger_id
1893 AND application_id = 222;
1894
1895 l_xla_upg_rec xla_upgrade_requests%ROWTYPE;
1896
1897 CURSOR child_reexc_request(p_ledger_id IN NUMBER,
1898 p_batch_id IN NUMBER)
1899 IS
1900 SELECT DECODE(program_code,'AR_UPG' ,'AR' ,
1901 'GIR_UPG' ,'GL' , --GIR
1902 'MRC_UPG','MRC',
1903 'JL_UPG' ,'JL',
1904 'PSA_UPG','PSA')
1905 FROM xla_upgrade_requests
1906 WHERE program_code <> 'XLA_UPG'
1907 AND phase_num <> 0
1908 AND status_code = 'A'
1909 AND ledger_id = p_ledger_id
1910 AND application_id = 222;
1911
1912
1913 CURSOR c_xla_upg_dates(p_ledger_id IN NUMBER)
1914 IS
1915 SELECT start_date,
1916 end_date
1917 FROM xla_upgrade_dates
1918 WHERE ledger_id = p_ledger_id;
1919
1920
1921 CURSOR c_ledger(p_ledger_id IN NUMBER) IS
1922 SELECT name
1923 FROM gl_ledgers
1924 WHERE ledger_id = p_ledger_id;
1925
1926 l_ledger_name VARCHAR2(30);
1927 l_start_date DATE;
1928 l_end_date DATE;
1929 l_reexec VARCHAR2(1);
1930 l_start_product VARCHAR2(30);
1931 l_batch_id NUMBER;
1932 l_result VARCHAR2(1) := 'N';
1933 l_run_required VARCHAR2(1);
1934 l_xla_dates_mismatch VARCHAR2(1);
1935 l_dates_missing VARCHAR2(1);
1936 x_start_date DATE;
1937 x_end_date DATE;
1938 x_return_status VARCHAR2(10);
1939 x_return_msg VARCHAR2(2000);
1940 x_request_control_id NUMBER;
1941
1942
1943 nothing_to_run EXCEPTION;
1944 should_relaunch_existing_upg EXCEPTION;
1945 no_date_in_xla_tab EXCEPTION;
1946 previous_run_dates_mismatch EXCEPTION;
1947
1948 BEGIN
1949 log( message =>'ar_master_upg + ' );
1950 log( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1951 log( message =>' p_workers_num :'||p_workers_num);
1952
1953 IF p_workers_num < 1 THEN
1954 RAISE nothing_to_run;
1955 END IF;
1956
1957 retcode := '0';
1958
1959 OPEN exist_xla_active(p_ledger_id => p_ledger_id);
1960 FETCH exist_xla_active INTO l_xla_upg_rec;
1961 IF exist_xla_active%FOUND THEN
1962 l_reexec := 'Y';
1963 ELSE
1964 l_reexec := 'N';
1965 END IF;
1966 CLOSE exist_xla_active;
1967
1968
1969 IF l_reexec = 'Y' THEN
1970
1971 log('p_period_name:'||p_period_name);
1972 log('p_ledger_id:'||p_ledger_id);
1973 log('p_workers_num:'||p_workers_num);
1974 log('p_batch_size:'||p_batch_size);
1975 ------------------------------------------------------------
1976 -- Check the user parameters are the same as the current run
1977 ------------------------------------------------------------
1978 IF l_xla_upg_rec.period_name <> p_period_name OR
1979 l_xla_upg_rec.ledger_id <> p_ledger_id OR
1980 l_xla_upg_rec.workers_num <> p_workers_num OR
1981 l_xla_upg_rec.batch_size <> p_batch_size
1982 THEN
1983 RAISE should_relaunch_existing_upg;
1984 ELSE
1985
1986 OPEN c_xla_upg_dates(p_ledger_id => p_ledger_id);
1987 FETCH c_xla_upg_dates INTO
1988 l_start_date,
1989 l_end_date ;
1990 IF c_xla_upg_dates%NOTFOUND THEN
1991 l_dates_missing := 'Y';
1992 ELSE
1993 IF l_xla_upg_rec.start_date <> l_start_date OR
1994 l_xla_upg_rec.end_date <> l_end_date
1995 THEN
1996 l_xla_dates_mismatch := 'Y';
1997 END IF;
1998 END IF;
1999 CLOSE c_xla_upg_dates;
2000
2001 IF l_dates_missing = 'Y' THEN
2002 RAISE no_date_in_xla_tab;
2003 END IF;
2004
2005 IF l_xla_dates_mismatch = 'Y' THEN
2006 RAISE previous_run_dates_mismatch;
2007 END IF;
2008
2009 END IF;
2010 END IF;
2011
2012
2013
2014
2015 IF l_reexec = 'N' THEN
2016
2017 -----------------------
2018 -- 1 Verif XLA_UPG data
2019 -----------------------
2020 validate_xla_upg_data
2021 (p_ledger_id => p_ledger_id,
2022 x_start_date => x_start_date,
2023 x_end_date => x_end_date,
2024 x_return_status => x_return_status,
2025 x_return_msg => x_return_msg);
2026
2027 IF x_return_status = 'N' THEN
2028 RAISE nothing_to_run;
2029 ELSIF x_return_status = 'E' THEN
2030 RAISE fnd_api.G_EXC_ERROR;
2031 END IF;
2032
2033
2034 --------------------------
2035 --2 record the upgrade run
2036 --------------------------
2037 OPEN c_batch_id;
2038 FETCH c_batch_id INTO l_batch_id;
2039 CLOSE c_batch_id;
2040
2041 l_xla_upg_rec.phase_num := 0;
2042 l_xla_upg_rec.PROGRAM_CODE := 'XLA_UPG';
2043 l_xla_upg_rec.DESCRIPTION := 'ON DEMAND UPGRADE LEDGER:'||p_ledger_id||
2044 ' FROM '||x_start_date||' TO '|| x_end_date;
2048 l_xla_upg_rec.WORKER_ID := NULL;
2045 l_xla_upg_rec.STATUS_CODE := 'A';
2046 l_xla_upg_rec.LEDGER_ID := p_ledger_id;
2047 l_xla_upg_rec.PERIOD_NAME := p_period_name;
2049 l_xla_upg_rec.WORKERS_NUM := p_workers_num;
2050 l_xla_upg_rec.TABLE_NAME := NULL;
2051 l_xla_upg_rec.SCRIPT_NAME := NULL;
2052 l_xla_upg_rec.BATCH_SIZE := p_batch_size;
2053 l_xla_upg_rec.BATCH_ID := l_batch_id;
2054 l_xla_upg_rec.ORDER_NUM := 0;
2055 l_xla_upg_rec.START_DATE := x_start_date;
2056 l_xla_upg_rec.END_DATE := x_end_date;
2057
2058 insert_req_control
2059 (p_ins_rec => l_xla_upg_rec,
2060 x_request_control_id => x_request_control_id);
2061
2062 --------------------------------
2063 -- Ensure the record is recorded
2064 --------------------------------
2065 COMMIT;
2066
2067 l_xla_upg_rec.request_control_id := x_request_control_id;
2068 l_start_product := 'AR';
2069 l_run_required := 'Y';
2070 l_reexec := 'N';
2071
2072 ELSE
2073 OPEN child_reexc_request(p_ledger_id => p_ledger_id,
2074 p_batch_id => l_xla_upg_rec.batch_id);
2075 FETCH child_reexc_request INTO l_start_product;
2076 IF child_reexc_request%NOTFOUND THEN
2077 l_run_required := 'N';
2078 ELSE
2079 l_run_required := 'Y';
2080 l_reexec := 'Y';
2081 END IF;
2082 CLOSE child_reexc_request;
2083 END IF;
2084
2085
2086 ---------------------------------
2087 --Submitting subrequest per product
2088 ---------------------------------
2089 IF l_run_required = 'Y' THEN
2090
2091 lauch_and_relaunch
2092 (p_start_product => l_start_product,
2093 p_reexec => l_reexec,
2094 p_parent_req_control_id => l_xla_upg_rec.request_control_id,
2095 p_batch_id => l_xla_upg_rec.batch_id,
2096 p_workers_num => l_xla_upg_rec.workers_num,
2097 p_batch_size => l_xla_upg_rec.batch_size,
2098 x_return_status => x_return_status,
2099 x_return_msg => x_return_msg);
2100
2101
2102 IF x_return_status = 'S' THEN
2103
2104 update_req_control
2105 (p_request_control_id => l_xla_upg_rec.request_control_id,
2106 p_status => 'S');
2107
2108 --Update GL PERIOD STATUSES with XLA api
2109 update_gl_period;
2110 ELSE
2111 retcode := '-2';
2112
2113 END IF;
2114
2115 END IF;
2116
2117 log( message =>'ar_master_upg - ' );
2118
2119 EXCEPTION
2120 WHEN nothing_to_run THEN
2121 log('number of workers ='||p_workers_num);
2122 log('nothing to run');
2123 log( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
2124 log( message =>'ar_master_upg - ' );
2125 retcode := 0;
2126 errbuf := 'number of workers ='||p_workers_num||' nothing to run';
2127
2128 WHEN should_relaunch_existing_upg THEN
2129 retcode := -2;
2130 OPEN c_ledger(l_xla_upg_rec.ledger_id);
2131 FETCH c_ledger INTO l_ledger_name;
2132 CLOSE c_ledger;
2133 fnd_message.set_name('AR','AR_UNSUCCESSFUL_RUN_EXISTS');
2134 fnd_message.set_token('PERIOD' ,l_xla_upg_rec.period_name);
2135 fnd_message.set_token('LEDGER' ,l_ledger_name);
2136 fnd_message.set_token('WORKERS',l_xla_upg_rec.workers_num);
2137 fnd_message.set_token('SIZE' ,l_xla_upg_rec.batch_size);
2138 errbuf := fnd_message.get;
2139 outandlog(errbuf);
2140
2141 WHEN no_date_in_xla_tab THEN
2142 retcode := -2;
2143 fnd_message.set_name('AR','AR_XLA_UPGRADE_DATE_MISSING');
2144 errbuf := fnd_message.get;
2145 outandlog(errbuf);
2146
2147 WHEN previous_run_dates_mismatch THEN
2148 retcode := -2;
2149 fnd_message.set_name('AR','AR_XLA_UPGRADE_DATE_OVERLAP');
2150 fnd_message.set_token('START_DATE' ,l_start_date);
2151 fnd_message.set_token('END_DATE' ,l_end_date);
2152 fnd_message.set_token('EXIST_START_DATE',l_xla_upg_rec.start_date);
2153 fnd_message.set_token('EXIST_END_DATE' ,l_xla_upg_rec.end_date);
2154 errbuf := fnd_message.get;
2155 outandlog(errbuf);
2156
2157 END;
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174 END;