[Home] [Help]
PACKAGE BODY: APPS.AR_UPGHARNESS_PKG
Source
1 PACKAGE BODY AR_UPGHARNESS_PKG AS
2 /*$Header: ARXLAHNB.pls 120.10 2006/10/05 19:21:57 hyu noship $*/
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 => to_char(sysdate,'DD-MON-YY HH:MI:SS'),
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
395
392 log('EXCEPTION OTHERS in update_process_status :'||SQLERRM);
393 RAISE;
394 END;
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 );
454 log( message =>' workers_num :'||p_num_workers);
455 log( message =>' worker_id :'||p_worker_id );
456 log( message =>' status :'||p_status );
457 log( message =>' p_order_num :'||p_order_num);
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);
547 request_control_id
544 log('end_date :'||p_ins_rec.end_date);
545
546 INSERT INTO xla_upgrade_requests(
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)
612 WHERE request_control_id = p_request_control_id
613 AND application_id = 222;
614 END;
615
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
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647 IF l_table_name IN ('JL_BR_AR_OCCURRENCE_DOCS_ALL','JL_BR_AR_MC_OCC_DOCS') THEN
648
649 IF fnd_installation.get_app_info('JL',l_status,l_industry,l_schema) THEN
650 l_table_owner := l_schema;
651 ELSE
652 l_table_owner := 'JL';
653 END IF;
654 l_phase := 4;
655 l_program_code := 'JL_UPG';
656
657 --{GIR
658 ELSIF l_table_name IN ('GL_IMPORT_REFERENCES') THEN
659
660 IF fnd_installation.get_app_info('SQLGL',l_status,l_industry,l_schema) THEN
661 l_table_owner := l_schema;
662 ELSE
663 l_table_owner := 'GL';
664 END IF;
665 l_phase := 2;
666 l_program_code := 'GIR_UPG';
667
668 ELSIF l_table_name = 'PSATRX' THEN
669
670 IF fnd_installation.get_app_info('PSA',l_status,l_industry,l_schema) THEN
671 l_table_owner := l_schema;
672 ELSE
673 l_table_owner := 'PSA';
674 END IF;
675 l_phase := 5;
676 l_program_code := 'PSA_UPG';
677 --}
678 ELSE
679
680 IF fnd_installation.get_app_info('AR',l_status,l_industry,l_schema) THEN
681 l_table_owner := l_schema;
682 ELSE
683 l_table_owner := 'AR';
684 END IF;
685
686 IF l_table_name IN ('RA_CUSTOMER_TRX_ALL','AR_CASH_RECEIPTS_ALL','AR_ADJUSTMENTS_ALL') THEN
687 l_phase := 1;
688 l_program_code := 'AR_UPG';
689 ELSE
690 l_phase := 3;
691 l_program_code := 'MRC_UPG';
692 END IF;
693
694 END IF;
695
696 -- AR records the run as Active
697 record_ar_run
698 (p_table_name => l_table_name,
699 p_script_name => l_script_name,
700 p_num_workers => l_num_workers,
701 p_worker_id => l_worker_id,
702 p_batch_size => l_batch_size,
703 p_status => 'A',
704 p_program_code => l_program_code,
705 p_phase => l_phase,
706 p_order_num => p_order_num,
707 x_return_status => x_return_status,
708 x_return_msg => x_return_msg,
712 FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_API_ERROR' );
709 x_request_control_id => x_request_control_id);
710
711 IF x_return_status <> 'S' THEN
713 FND_MESSAGE.SET_TOKEN( 'TEXT', x_return_msg );
714 FND_MSG_PUB.ADD;
715 RAISE maintenance_record;
716 END IF;
717
718
719 SAVEPOINT upgrade_by_request;
720 --
721 -- For AR upgrade package does not support the x_return_status
722 --
723 IF l_table_name = 'RA_CUSTOMER_TRX_ALL' THEN
724 outandlog( message =>' l_table_name :'||l_table_name );
725 outandlog( message =>' l_schema :'||l_schema);
726 ARP_XLA_UPGRADE.UPGRADE_TRANSACTIONS(l_table_owner => l_table_owner,
727 l_table_name => l_table_name,
728 l_script_name => l_script_name,
729 l_worker_id => l_worker_id,
730 l_num_workers => l_num_workers,
731 l_batch_size => l_batch_size,
732 l_batch_id => l_batch_id,
733 l_action_flag => 'R');
734
735 ELSIF l_table_name = 'AR_CASH_RECEIPTS_ALL' THEN
736 outandlog( message =>' l_table_name :'||l_table_name );
737 outandlog( message =>' l_schema :'||l_schema);
738
739 ARP_XLA_UPGRADE.UPGRADE_RECEIPTS(l_table_owner => l_table_owner,
740 l_table_name => l_table_name,
741 l_script_name => l_script_name,
742 l_worker_id => l_worker_id,
743 l_num_workers => l_num_workers,
744 l_batch_size => l_batch_size,
745 l_batch_id => l_batch_id,
746 l_action_flag => 'R');
747
748 ELSIF l_table_name = 'AR_ADJUSTMENTS_ALL' THEN
749 outandlog( message =>' l_table_name :'||l_table_name );
750 outandlog( message =>' l_schema :'||l_schema);
751
752 ARP_XLA_UPGRADE.UPGRADE_ADJUSTMENTS(l_table_owner => l_table_owner,
753 l_table_name => l_table_name,
754 l_script_name => l_script_name,
755 l_worker_id => l_worker_id,
756 l_num_workers => l_num_workers,
757 l_batch_size => l_batch_size,
758 l_batch_id => l_batch_id,
759 l_action_flag => 'R');
760
761 --{GL Links
762 ELSIF l_table_name = 'GL_IMPORT_REFERENCES' THEN
763 outandlog( message =>' l_table_name :'||l_table_name );
764 outandlog( message =>' l_schema :'||l_schema);
765
766 ARP_XLA_UPGRADE.update_gl_sla_link(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 => 'R');
774 --}
775
776 ELSIF l_table_name = 'MC_TRANSACTIONS' THEN
777 outandlog( message =>' l_table_name :RA_MC_CUSTOMER_TRX' );
778 outandlog( message =>' l_schema :'||l_schema);
779
780 ARP_MRC_XLA_UPGRADE.UPGRADE_MC_TRANSACTIONS
781 (l_table_owner => l_table_owner,
782 l_table_name => 'RA_MC_CUSTOMER_TRX',
783 l_script_name => l_script_name,
784 l_worker_id => l_worker_id,
785 l_num_workers => l_num_workers,
786 l_batch_size => l_batch_size,
787 l_batch_id => l_batch_id,
788 l_action_flag => 'R');
789
790 ELSIF l_table_name = 'MC_RECEIPTS' THEN
791 outandlog( message =>' l_table_name :AR_MC_CASH_RECEIPTS' );
792 outandlog( message =>' l_schema :'||l_schema);
793
794 ARP_MRC_XLA_UPGRADE.UPGRADE_MC_RECEIPTS
795 (l_table_owner => l_table_owner,
796 l_table_name => 'AR_MC_CASH_RECEIPTS',
797 l_script_name => l_script_name,
798 l_worker_id => l_worker_id,
799 l_num_workers => l_num_workers,
800 l_batch_size => l_batch_size,
801 l_batch_id => l_batch_id,
802 l_action_flag => 'R');
803
804 ELSIF l_table_name = 'MC_ADJUSTMENTS' THEN
805 outandlog( message =>' l_table_name :AR_MC_ADJUSTMENTS' );
806 outandlog( message =>' l_schema :'||l_schema);
807
808 ARP_MRC_XLA_UPGRADE.UPGRADE_MC_ADJUSTMENTS
809 (l_table_owner => l_table_owner,
810 l_table_name => 'AR_MC_ADJUSTMENTS',
811 l_script_name => l_script_name,
812 l_worker_id => l_worker_id,
813 l_num_workers => l_num_workers,
814 l_batch_size => l_batch_size,
815 l_batch_id => l_batch_id,
816 l_action_flag => 'R');
817
818 --
819 -- For JL the upgrade code does support the x_return_status
820 --
821 ELSIF l_table_name = 'JL_BR_AR_OCCURRENCE_DOCS_ALL' THEN
822
823 outandlog( message =>' l_table_name :JL_BR_AR_OCCURRENCE_DOCS_ALL' );
824 outandlog( message =>' l_schema :'||l_schema);
825 JL_BR_AR_BANK_ACCT_PKG.UPGRADE_OCCURRENCES(
826 l_table_owner => l_table_owner,
827 l_table_name => l_table_name,
828 l_script_name => l_script_name,
829 l_worker_id => l_worker_id,
830 l_num_workers => l_num_workers,
834 x_return_status=> x_return_status);
831 l_batch_size => l_batch_size,
832 l_batch_id => l_batch_id,
833 l_action_flag => 'R',
835
836
837 --{ BUG#4645903
838 ELSIF l_table_name = 'JL_BR_AR_MC_OCC_DOCS' THEN
839
840 outandlog( message =>' l_table_name :JL_BR_AR_OCCURRENCE_DOCS_ALL' );
841 outandlog( message =>' l_schema :'||l_schema);
842 JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES(
843 l_table_owner => l_table_owner,
844 l_table_name => 'JL_BR_AR_OCCURENCE_DOCS_ALL',
845 l_script_name => l_script_name,
846 l_worker_id => l_worker_id,
847 l_num_workers => l_num_workers,
848 l_batch_size => l_batch_size,
849 l_batch_id => l_batch_id,
850 l_action_flag => 'R',
851 x_return_status=> x_return_status);
852 --}
853 ELSIF l_table_name = 'PSATRX' THEN
854
855 --{PSATRX
856 ar_upg_psa_dist_pkg.UPGRADE_TRANSACTIONS(
857 l_table_owner => l_table_owner,
858 l_table_name => 'PSA_MF_TRX_DIST_ALL',
859 l_script_name => l_script_name,
860 l_worker_id => l_worker_id,
861 l_num_workers => l_num_workers,
862 l_batch_size => l_batch_size,
863 l_batch_id => l_batch_id,
864 l_action_flag => 'R');
865
866 END IF;
867
868 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870 END IF;
871
872
873 -- Updating record as successfull
874 update_req_control
875 (p_request_control_id => x_request_control_id,
876 p_status => 'S');
877
878 outandlog( message =>' Ending at ' || to_char(SYSDATE, 'HH24:MI:SS') );
879 outandlog( message =>'Worker has completed successfully');
880
881 EXCEPTION
882 WHEN maintenance_record THEN
883 outandlog('Error: ' || FND_MESSAGE.GET);
884 retcode := 1;
885 errbuf := errbuf || logerror;
886 outandlog('Aborting concurrent program execution');
887 -- FND_FILE.close;
888
889 WHEN FND_API.G_EXC_ERROR THEN
890 ROLLBACK TO upgrade_by_request;
891 outandlog('Error: ' || FND_MESSAGE.GET);
892 retcode := 1;
893 errbuf := errbuf || logerror;
894 outandlog('Aborting concurrent program execution');
895 -- FND_FILE.close;
896
897
898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
899 --JL raises FND api error
900 ROLLBACK TO upgrade_by_request;
901 outandlog('Error: ' || FND_MESSAGE.GET);
902 retcode := 1;
903 errbuf := errbuf || logerror;
904 outandlog('Aborting concurrent program execution');
905 -- FND_FILE.close;
906
907 WHEN OTHERS THEN
908 --AR and MRC and PSA raises when others
909 ROLLBACK TO upgrade_by_request;
910 outandlog('Error: ' || FND_MESSAGE.GET);
911 log('SQL Error ' || SQLERRM);
912 retcode := 1;
913 errbuf := errbuf || logerror || SQLERRM;
914 outandlog('Aborting concurrent program execution');
915 -- FND_FILE.close;
916
917 END;
918
919
920
921
922
923 PROCEDURE ins_exist_process_again
924 (p_product_name IN VARCHAR2,
925 p_batch_id IN NUMBER,
926 p_parent_req_control_id IN NUMBER,
927 x_nb_inserted OUT NOCOPY NUMBER)
928 IS
929 l_program_code VARCHAR2(30);
930 l_phase NUMBER;
931 BEGIN
932 IF p_product_name = 'AR' THEN
933 l_program_code := 'AR_UPG';
934 l_phase := 1;
935 --{GIR
936 ELSIF p_product_name = 'GL' THEN
937 l_program_code := 'GIR_UPG';
938 l_phase := 2;
939 --}
940 ELSIF p_product_name = 'MRC' THEN
941 l_program_code := 'MRC_UPG';
942 l_phase := 3;
943 ELSIF p_product_name = 'JL' THEN
944 l_program_code := 'JL_UPG';
945 l_phase := 4;
946 ELSIF p_product_name = 'PSA' THEN
947 l_program_code := 'PSA_UPG';
948 l_phase := 5;
949 END IF;
950
951 INSERT INTO ar_submission_ctrl_gt
952 (worker_id , --worker_number
953 batch_id , --batch_id
954 script_name , --script_name
955 status , --INSERTED, SUBMITTED, (NORMAL, ERROR, WARNING, CANCELLED, TERMINATED)
956 order_num , --order helper number
957 request_id , --request_id
958 table_name ) --table_name
959 SELECT worker_id,
960 batch_id,
961 script_name,
962 'INSERTED',
963 order_num,
964 request_control_id,
965 table_name
966 FROM xla_upgrade_requests
967 WHERE batch_id = p_batch_id
968 AND parent_request_control_id = p_parent_req_control_id
969 AND phase_num = l_phase
970 AND program_code = l_program_code
971 AND status_code <> 'S'
972 AND application_id = 222;
973
974 x_nb_inserted := SQL%ROWCOUNT;
975 END;
976
977
978
979 PROCEDURE ins_subs_for_one_script
980 (p_script_name IN VARCHAR2,
981 p_batch_id IN NUMBER,
982 p_order_num IN VARCHAR2,
983 p_table_name IN VARCHAR2,
984 p_workers_num IN NUMBER)
985 IS
986 i NUMBER;
990 tab_status DBMS_SQL.VARCHAR2_TABLE;
987 tab_worker_number DBMS_SQL.NUMBER_TABLE;
988 tab_batch_id DBMS_SQL.NUMBER_TABLE;
989 tab_script_name DBMS_SQL.VARCHAR2_TABLE;
991 tab_order DBMS_SQL.NUMBER_TABLE;
992 tab_request_id DBMS_SQL.NUMBER_TABLE;
993 tab_table_name DBMS_SQL.VARCHAR2_TABLE;
994 BEGIN
995 ins_120_ctl(p_script_name => p_script_name,
996 p_batch_id => p_batch_id);
997
998 FOR i IN 1 .. p_workers_num LOOP
999 tab_worker_number(i) := i;
1000 tab_batch_id(i) := p_batch_id;
1001 tab_script_name(i) := p_script_name;
1002 tab_status(i) := 'INSERTED';
1003 tab_order(i) := p_order_num;
1004 tab_request_id(i) := NULL;
1005 tab_table_name(i) := p_table_name;
1006 END LOOP;
1007
1008 FORALL i IN tab_worker_number.FIRST .. tab_worker_number.LAST
1009 INSERT INTO ar_submission_ctrl_gt
1010 (worker_id , --worker_number
1011 batch_id , --batch_id
1012 script_name , --script_name
1013 status , --INSERTED, SUBMITTED, (NORMAL, ERROR, WARNING, CANCELLED, TERMINATED)
1014 order_num , --order helper number
1015 request_id , --request_id
1016 table_name ) --table_name
1017 VALUES
1018 (tab_worker_number(i),
1019 tab_batch_id(i),
1020 tab_script_name(i),
1021 tab_status(i),
1022 tab_order(i),
1023 tab_request_id(i),
1024 tab_table_name(i));
1025 END;
1026
1027
1028
1029 PROCEDURE ins_new_process
1030 (p_product_name IN VARCHAR2,
1031 p_batch_id IN NUMBER,
1032 p_parent_req_control_id IN NUMBER,
1033 p_workers_num IN NUMBER,
1034 x_nb_inserted OUT NOCOPY NUMBER)
1035 IS
1036 l_script_name VARCHAR2(30);
1037 BEGIN
1038 x_nb_inserted := 0;
1039
1040 IF p_product_name = 'AR' THEN
1041
1042 l_script_name := 'ar120trx_'||p_batch_id;
1043
1044 ins_subs_for_one_script
1045 (p_script_name => l_script_name,
1046 p_batch_id => p_batch_id,
1047 p_order_num => 1,
1048 p_table_name => 'RA_CUSTOMER_TRX_ALL',
1049 p_workers_num => p_workers_num);
1050
1051 l_script_name := 'ar120rcp_'||p_batch_id;
1052
1053 ins_subs_for_one_script
1054 (p_script_name => l_script_name,
1055 p_batch_id => p_batch_id,
1056 p_order_num => 2,
1057 p_table_name => 'AR_CASH_RECEIPTS_ALL',
1058 p_workers_num => p_workers_num);
1059
1060 l_script_name := 'ar120adj_'||p_batch_id;
1061
1062 ins_subs_for_one_script
1063 (p_script_name => l_script_name,
1064 p_batch_id => p_batch_id,
1065 p_order_num => 3,
1066 p_table_name => 'AR_ADJUSTMENTS_ALL',
1067 p_workers_num => p_workers_num);
1068
1069 x_nb_inserted := 3 * p_workers_num;
1070
1071 --{GIR
1072 ELSIF p_product_name = 'GL' THEN
1073 l_script_name := 'ar120gir_'||p_batch_id;
1074
1075 ins_subs_for_one_script
1076 (p_script_name => l_script_name,
1077 p_batch_id => p_batch_id,
1078 p_order_num => 1,
1079 p_table_name => 'GL_IMPORT_REFERENCES',
1080 p_workers_num => p_workers_num);
1081
1082 x_nb_inserted := p_workers_num;
1083 --}
1084
1085 ELSIF p_product_name = 'MRC' THEN
1086
1087 IF mrc_run_required = 'Y' THEN
1088
1089 l_script_name := 'armc120trx_'||p_batch_id;
1090
1091 ins_subs_for_one_script
1092 (p_script_name => l_script_name,
1093 p_batch_id => p_batch_id,
1094 p_order_num => 1,
1095 p_table_name => 'MC_TRANSACTIONS',
1096 p_workers_num => p_workers_num);
1097
1098 l_script_name := 'armc120rcp_'||p_batch_id;
1099
1100 ins_subs_for_one_script
1101 (p_script_name => l_script_name,
1102 p_batch_id => p_batch_id,
1103 p_order_num => 2,
1104 p_table_name => 'MC_RECEIPTS',
1105 p_workers_num => p_workers_num);
1106
1107 l_script_name := 'armc120adj_'||p_batch_id;
1108
1109 ins_subs_for_one_script
1110 (p_script_name => l_script_name,
1111 p_batch_id => p_batch_id,
1112 p_order_num => 3,
1113 p_table_name => 'MC_ADJUSTMENTS',
1114 p_workers_num => p_workers_num);
1115
1116 x_nb_inserted := 3 * p_workers_num;
1117
1118 END IF;
1119
1120 ELSIF p_product_name = 'JL' THEN
1121
1122 IF JL_BR_AR_BANK_ACCT_PKG.check_if_upgrade_occs THEN
1123
1124 l_script_name := 'jl120occ_'||p_batch_id;
1125
1126 ins_subs_for_one_script
1127 (p_script_name => l_script_name,
1128 p_batch_id => p_batch_id,
1129 p_order_num => 1,
1130 p_table_name => 'JL_BR_AR_OCCURRENCE_DOCS_ALL',
1131 p_workers_num => p_workers_num);
1132
1133 x_nb_inserted := p_workers_num;
1134
1135
1136 END IF;
1137
1138 IF JL_BR_AR_BANK_ACCT_PKG.check_if_upgrade_occs AND
1139 mrc_run_required = 'Y'
1140 THEN
1141
1142 l_script_name := 'jl120mcocc_'||p_batch_id;
1143
1144 ins_subs_for_one_script
1145 (p_script_name => l_script_name,
1146 p_batch_id => p_batch_id,
1147 p_order_num => 2,
1148 p_table_name => 'JL_BR_AR_MC_OCC_DOCS',
1152 END IF;
1149 p_workers_num => p_workers_num);
1150
1151 x_nb_inserted := x_nb_inserted + p_workers_num;
1153
1154
1155 ELSIF p_product_name = 'PSA' THEN
1156
1157 l_script_name := 'psatrx_'||p_batch_id;
1158
1159 ins_subs_for_one_script
1160 (p_script_name => l_script_name,
1161 p_batch_id => p_batch_id,
1162 p_order_num => 1,
1163 p_table_name => 'PSATRX',
1164 p_workers_num => p_workers_num);
1165
1166 x_nb_inserted := p_workers_num;
1167
1168 END IF;
1169
1170 END;
1171
1172
1173
1174
1175
1176 PROCEDURE check_run_data
1177 (p_batch_id IN NUMBER,
1178 x_return_status OUT NOCOPY VARCHAR2,
1179 x_return_msg OUT NOCOPY VARCHAR2)
1180 IS
1181 CURSOR c_req(p_batch_id IN NUMBER) IS
1182 SELECT status,
1183 request_id
1184 FROM ar_submission_ctrl_gt
1185 WHERE batch_id = p_batch_id
1186 AND status <> 'NORMAL';
1187 l_status VARCHAR2(30);
1188 l_request_id NUMBER;
1189 BEGIN
1190 x_return_status := 'S';
1191 OPEN c_req(p_batch_id);
1192 FETCH c_req INTO l_status, l_request_id;
1193 IF c_req%NOTFOUND THEN
1194 x_return_status := 'S';
1195 ELSE
1196 fnd_message.set_name('AR','AR_XLA_EXIST_REQ_ERROR');
1197 fnd_message.set_token('REQUEST_ID',l_request_id);
1198 x_return_msg := fnd_message.get;
1199 outandlog(x_return_msg);
1200 x_return_status := 'E';
1201 END IF;
1202 CLOSE c_req;
1203 END;
1204
1205
1206
1207
1208 PROCEDURE ar_master_upg_product
1209 (p_parent_req_control_id IN NUMBER,
1210 p_batch_id IN NUMBER,
1211 p_reexecution IN VARCHAR2 DEFAULT 'N',
1212 p_product_name IN VARCHAR2,
1213 p_workers_num IN NUMBER,
1214 p_batch_size IN NUMBER,
1215 x_return_msg OUT NOCOPY VARCHAR2,
1216 x_return_status OUT NOCOPY VARCHAR2 )
1217 IS
1218 l_result VARCHAR2(1) := 'N';
1219 incremental_cnt NUMBER := 0;
1220 x_nb_inserted NUMBER := 0;
1221 execution_req VARCHAR2(1);
1222 nothing_to_run EXCEPTION;
1223 finish EXCEPTION;
1224
1225 CURSOR c(p_batch_id IN NUMBER)
1226 IS
1227 SELECT 'Y'
1228 FROM ar_submission_ctrl_gt
1229 WHERE status IN ('INSERTED','SUBMITTED')
1230 AND batch_id = p_batch_id;
1231
1232
1233
1234 BEGIN
1235 log( message =>'ar_master_upg_product + ' );
1236 log( message =>' Running for the product:'||p_product_name);
1237 log( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1238 log( message =>' p_workers_num :'||p_workers_num);
1239 log( message =>' Is this reexecution :'||p_reexecution);
1240 log( message =>' p_batch_size :'||p_batch_size);
1241 log( message =>' p_workers_num :'||p_workers_num);
1242
1243 x_return_status := 'S';
1244
1245 IF p_workers_num < 1 THEN
1246 RAISE nothing_to_run;
1247 END IF;
1248
1249 IF p_reexecution = 'Y' THEN
1250
1251 IF p_product_name = 'AR' THEN
1252
1253 ins_exist_process_again
1254 (p_product_name => 'AR',
1255 p_batch_id => p_batch_id,
1256 p_parent_req_control_id => p_parent_req_control_id,
1257 x_nb_inserted => x_nb_inserted);
1258
1259 --{GIR
1260 ELSIF p_product_name = 'GL' THEN
1261
1262 ins_exist_process_again
1263 (p_product_name => 'GL',
1264 p_batch_id => p_batch_id,
1265 p_parent_req_control_id => p_parent_req_control_id,
1266 x_nb_inserted => x_nb_inserted);
1267 --}
1268
1269 ELSIF p_product_name = 'MRC' THEN
1270
1271 ins_exist_process_again
1272 (p_product_name => 'MRC',
1273 p_batch_id => p_batch_id,
1274 p_parent_req_control_id => p_parent_req_control_id,
1275 x_nb_inserted => x_nb_inserted);
1276
1277 ELSIF p_product_name = 'JL' THEN
1278
1279 ins_exist_process_again
1280 (p_product_name => 'JL',
1281 p_batch_id => p_batch_id,
1282 p_parent_req_control_id => p_parent_req_control_id,
1283 x_nb_inserted => x_nb_inserted);
1284
1285 ELSIF p_product_name = 'PSA' THEN
1286
1287 ins_exist_process_again
1288 (p_product_name => 'PSA',
1289 p_batch_id => p_batch_id,
1290 p_parent_req_control_id => p_parent_req_control_id,
1291 x_nb_inserted => x_nb_inserted);
1292
1293 END IF;
1294
1295
1296 IF x_nb_inserted > 0 THEN
1297 execution_req := 'Y';
1298 ELSE
1299 execution_req := 'N';
1300 END IF;
1301
1302 ELSE
1303
1304 IF p_product_name = 'AR' THEN
1305
1306 ins_new_process
1307 (p_product_name => 'AR',
1308 p_batch_id => p_batch_id,
1309 p_parent_req_control_id => p_parent_req_control_id,
1310 p_workers_num => p_workers_num,
1311 x_nb_inserted => x_nb_inserted);
1312
1313
1314 --{GIR
1315 ELSIF p_product_name = 'GL' THEN
1316
1317 ins_new_process
1318 (p_product_name => 'GL',
1319 p_batch_id => p_batch_id,
1320 p_parent_req_control_id => p_parent_req_control_id,
1321 p_workers_num => p_workers_num,
1325
1322 x_nb_inserted => x_nb_inserted);
1323
1324 --}
1326
1327 ELSIF p_product_name = 'MRC' THEN
1328
1329 ins_new_process
1330 (p_product_name => 'MRC',
1331 p_batch_id => p_batch_id,
1332 p_parent_req_control_id => p_parent_req_control_id,
1333 p_workers_num => p_workers_num,
1334 x_nb_inserted => x_nb_inserted);
1335
1336 ELSIF p_product_name = 'JL' THEN
1337
1338 ins_new_process
1339 (p_product_name => 'JL',
1340 p_batch_id => p_batch_id,
1341 p_parent_req_control_id => p_parent_req_control_id,
1342 p_workers_num => p_workers_num,
1343 x_nb_inserted => x_nb_inserted);
1344
1345 ELSIF p_product_name = 'PSA' THEN
1346
1347 ins_new_process
1348 (p_product_name => 'PSA',
1349 p_batch_id => p_batch_id,
1350 p_parent_req_control_id => p_parent_req_control_id,
1351 p_workers_num => p_workers_num,
1352 x_nb_inserted => x_nb_inserted);
1353
1354
1355 END IF;
1356
1357 IF x_nb_inserted > 0 THEN
1358 execution_req := 'Y';
1359 ELSE
1360 execution_req := 'N';
1361 END IF;
1362
1363 END IF;
1364
1365
1366 IF execution_req = 'Y' THEN
1367
1368 log( message =>' In the child requests submission loop');
1369
1370 LOOP
1371
1372 OPEN c(p_batch_id);
1373 FETCH c INTO l_result;
1374 IF c%NOTFOUND THEN
1375 RAISE finish;
1376 END IF;
1377 CLOSE c;
1378
1379 submission_main_routine
1380 (p_workers => p_workers_num,
1381 p_batch_id => p_batch_id,
1382 p_batch_size => p_batch_size);
1383
1384 verif_status
1385 (p_batch_id => p_batch_id);
1386
1387 END LOOP;
1388
1389 END IF;
1390 log( message =>'ar_master_upg_product -' );
1391
1392 EXCEPTION
1393 WHEN nothing_to_run THEN
1394 log('number of workers ='||p_workers_num);
1395 log('nothing to run');
1396 log( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1397 log( message =>'ar_master_upg_product - ' );
1398 x_return_msg := 'number of workers ='||p_workers_num||' nothing to run';
1399 WHEN finish THEN
1400 log('Raise Finish');
1401 IF c%ISOPEN THEN
1402 close c;
1403 END IF;
1404 -- Add XLA update status call
1405 check_run_data
1406 (p_batch_id => p_batch_id,
1407 x_return_status => x_return_status,
1408 x_return_msg => x_return_msg);
1409 log( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1410 log( message =>'ar_master_upg_product -' );
1411
1412
1413 END;
1414
1415
1416
1417
1418 PROCEDURE validate_xla_upg_data
1419 (p_ledger_id IN NUMBER,
1420 x_start_date OUT NOCOPY DATE,
1421 x_end_date OUT NOCOPY DATE,
1422 x_return_status OUT NOCOPY VARCHAR2,
1423 x_return_msg OUT NOCOPY VARCHAR2)
1424 IS
1425 CURSOR c_xla_dates IS
1426 SELECT start_date,
1427 end_date
1428 FROM xla_upgrade_dates
1429 WHERE ledger_id = p_ledger_id;
1430
1431
1432 CURSOR c_ar_xla_dates IS
1433 SELECT b.start_date,
1434 b.end_date
1435 FROM xla_upgrade_requests b
1436 WHERE b.ledger_id = p_ledger_id
1437 AND b.program_code = 'XLA_UPG'
1438 AND b.status_code = 'S'
1439 AND b.phase_num = 0
1440 AND b.application_id = 222
1441 AND EXISTS (SELECT NULL
1442 FROM xla_upgrade_dates a
1443 WHERE (a.start_date BETWEEN b.start_date AND b.end_date OR
1444 a.end_date BETWEEN b.start_date AND b.end_date)
1445 AND a.ledger_id = b.ledger_id);
1446
1447
1448 l_start_date DATE;
1449 l_end_date DATE;
1450 nb_of_xla_dates_row NUMBER := 0;
1451 l_overlapp VARCHAR2(1);
1452 nothing_to_run EXCEPTION;
1453 more_than_one_xla_dates EXCEPTION;
1454 start_greater_than_end EXCEPTION;
1455 overlapp EXCEPTION;
1456 BEGIN
1457 x_return_status := 'S';
1458
1459 OPEN c_xla_dates;
1460 LOOP
1461 FETCH c_xla_dates INTO l_start_date, l_end_date;
1462 EXIT WHEN c_xla_dates%NOTFOUND;
1463 nb_of_xla_dates_row := nb_of_xla_dates_row + 1;
1464 END LOOP;
1465 CLOSE c_xla_dates;
1466
1467 IF nb_of_xla_dates_row = 0 THEN
1468 RAISE nothing_to_run;
1469 ELSIF nb_of_xla_dates_row > 1 THEN
1470 RAISE more_than_one_xla_dates;
1471 END IF;
1472
1473 IF l_start_date > l_end_date THEN
1474 RAISE start_greater_than_end;
1475 END IF;
1476
1477 OPEN c_ar_xla_dates;
1478 FETCH c_ar_xla_dates INTO
1479 l_start_date,
1480 l_end_date ;
1481 IF c_ar_xla_dates%FOUND THEN
1482 l_overlapp := 'Y';
1483 END IF;
1484 CLOSE c_ar_xla_dates;
1485
1486 IF l_overlapp = 'Y' THEN
1487 RAISE overlapp;
1488 END IF;
1489
1490 x_start_date := l_start_date;
1491 x_end_date := l_end_date;
1492
1493 EXCEPTION
1494 WHEN nothing_to_run THEN
1495 x_return_status := 'N';
1496 x_return_msg := 'AR_XLA_UPGRADE_DATE_MISSING';
1497 WHEN more_than_one_xla_dates THEN
1498 x_return_status := 'E';
1499 x_return_msg := 'MORE_THAN_ONE_XLA_UPG_DATE';
1500 WHEN start_greater_than_end THEN
1501 x_return_status := 'E';
1505 x_return_msg := 'AR_XLA_UPGRADE_DATE_OVERLAP';
1502 x_return_msg := 'XLA_END_GREATER_THAN_START';
1503 WHEN overlapp THEN
1504 x_return_status := 'E';
1506 END;
1507
1508
1509
1510
1511
1512
1513
1514 PROCEDURE lauch_and_relaunch
1515 (p_start_product IN VARCHAR2,
1516 p_reexec IN VARCHAR2,
1517 p_parent_req_control_id IN NUMBER,
1518 p_batch_id IN NUMBER,
1519 p_workers_num IN NUMBER,
1520 p_batch_size IN NUMBER,
1521 x_return_status OUT NOCOPY VARCHAR2,
1522 x_return_msg OUT NOCOPY VARCHAR2)
1523 IS
1524 l_current_product VARCHAR2(30);
1525 x_request_control_id NUMBER;
1526 stop_at_product EXCEPTION;
1527
1528 BEGIN
1529 l_current_product := p_start_product;
1530
1531 ar_master_upg_product
1532 (p_parent_req_control_id => p_parent_req_control_id,
1533 p_batch_id => p_batch_id,
1534 p_reexecution => p_reexec,
1535 p_product_name => p_start_product,
1536 p_workers_num => p_workers_num,
1537 p_batch_size => p_batch_size,
1538 x_return_msg => x_return_msg,
1539 x_return_status => x_return_status);
1540
1541
1542 IF x_return_status <> 'S' THEN
1543 RAISE stop_at_product;
1544 END IF;
1545
1546 IF p_start_product = 'AR' THEN
1547
1548 DELETE FROM ar_submission_ctrl_gt;
1549
1550 l_current_product := 'GL';
1551
1552 ar_master_upg_product
1553 (p_parent_req_control_id => x_request_control_id,
1554 p_batch_id => p_batch_id,
1555 p_reexecution => 'N',
1556 p_product_name => l_current_product,
1557 p_workers_num => p_workers_num,
1558 p_batch_size => p_batch_size,
1559 x_return_msg => x_return_msg,
1560 x_return_status => x_return_status);
1561
1562 IF x_return_status <> 'S' THEN
1563 RAISE stop_at_product;
1564 END IF;
1565
1566 DELETE FROM ar_submission_ctrl_gt;
1567
1568 l_current_product := 'MRC';
1569
1570 ar_master_upg_product
1571 (p_parent_req_control_id => x_request_control_id,
1572 p_batch_id => p_batch_id,
1573 p_reexecution => 'N',
1574 p_product_name => l_current_product,
1575 p_workers_num => p_workers_num,
1576 p_batch_size => p_batch_size,
1577 x_return_msg => x_return_msg,
1578 x_return_status => x_return_status);
1579
1580
1581 IF x_return_status <> 'S' THEN
1582 RAISE stop_at_product;
1583 END IF;
1584
1585
1586 DELETE FROM ar_submission_ctrl_gt;
1587
1588 l_current_product := 'JL';
1589
1590 ar_master_upg_product
1591 (p_parent_req_control_id => x_request_control_id,
1592 p_batch_id => p_batch_id,
1593 p_reexecution => 'N',
1594 p_product_name => l_current_product,
1595 p_workers_num => p_workers_num,
1596 p_batch_size => p_batch_size,
1597 x_return_msg => x_return_msg,
1598 x_return_status => x_return_status);
1599
1600
1601 IF x_return_status <> 'S' THEN
1602 RAISE stop_at_product;
1603 END IF;
1604
1605 DELETE FROM ar_submission_ctrl_gt;
1606
1607 l_current_product := 'PSA';
1608
1609 ar_master_upg_product
1610 (p_parent_req_control_id => x_request_control_id,
1611 p_batch_id => p_batch_id,
1612 p_reexecution => 'N',
1613 p_product_name => l_current_product,
1614 p_workers_num => p_workers_num,
1615 p_batch_size => p_batch_size,
1616 x_return_msg => x_return_msg,
1617 x_return_status => x_return_status);
1618
1619
1620 IF x_return_status <> 'S' THEN
1621 RAISE stop_at_product;
1622 END IF;
1623
1624
1625
1626 ELSIF p_start_product = 'GL' THEN
1627
1628 DELETE FROM ar_submission_ctrl_gt;
1629
1630 l_current_product := 'MRC';
1631
1632 ar_master_upg_product
1633 (p_parent_req_control_id => x_request_control_id,
1634 p_batch_id => p_batch_id,
1635 p_reexecution => 'N',
1636 p_product_name => l_current_product,
1637 p_workers_num => p_workers_num,
1638 p_batch_size => p_batch_size,
1639 x_return_msg => x_return_msg,
1640 x_return_status => x_return_status);
1641
1642
1643 IF x_return_status <> 'S' THEN
1644 RAISE stop_at_product;
1645 END IF;
1646
1647
1648 DELETE FROM ar_submission_ctrl_gt;
1649
1650 l_current_product := 'JL';
1651
1652 ar_master_upg_product
1653 (p_parent_req_control_id => x_request_control_id,
1654 p_batch_id => p_batch_id,
1655 p_reexecution => 'N',
1656 p_product_name => l_current_product,
1657 p_workers_num => p_workers_num,
1658 p_batch_size => p_batch_size,
1659 x_return_msg => x_return_msg,
1660 x_return_status => x_return_status);
1661
1662
1663 IF x_return_status <> 'S' THEN
1664 RAISE stop_at_product;
1665 END IF;
1666
1670
1667 DELETE FROM ar_submission_ctrl_gt;
1668
1669 l_current_product := 'PSA';
1671 ar_master_upg_product
1672 (p_parent_req_control_id => x_request_control_id,
1673 p_batch_id => p_batch_id,
1674 p_reexecution => 'N',
1675 p_product_name => l_current_product,
1676 p_workers_num => p_workers_num,
1677 p_batch_size => p_batch_size,
1678 x_return_msg => x_return_msg,
1679 x_return_status => x_return_status);
1680
1681
1682 IF x_return_status <> 'S' THEN
1683 RAISE stop_at_product;
1684 END IF;
1685
1686
1687 ELSIF p_start_product = 'MRC' THEN
1688
1689
1690 DELETE FROM ar_submission_ctrl_gt;
1691
1692 l_current_product := 'JL';
1693
1694 ar_master_upg_product
1695 (p_parent_req_control_id => x_request_control_id,
1696 p_batch_id => p_batch_id,
1697 p_reexecution => 'N',
1698 p_product_name => l_current_product,
1699 p_workers_num => p_workers_num,
1700 p_batch_size => p_batch_size,
1701 x_return_msg => x_return_msg,
1702 x_return_status => x_return_status);
1703
1704
1705 IF x_return_status <> 'S' THEN
1706 RAISE stop_at_product;
1707 END IF;
1708
1709 DELETE FROM ar_submission_ctrl_gt;
1710
1711 l_current_product := 'PSA';
1712
1713 ar_master_upg_product
1714 (p_parent_req_control_id => x_request_control_id,
1715 p_batch_id => p_batch_id,
1716 p_reexecution => 'N',
1717 p_product_name => l_current_product,
1718 p_workers_num => p_workers_num,
1719 p_batch_size => p_batch_size,
1720 x_return_msg => x_return_msg,
1721 x_return_status => x_return_status);
1722
1723
1724 IF x_return_status <> 'S' THEN
1725 RAISE stop_at_product;
1726 END IF;
1727
1728 ELSIF p_start_product = 'JL' THEN
1729
1730 DELETE FROM ar_submission_ctrl_gt;
1731
1732 l_current_product := 'PSA';
1733
1734 ar_master_upg_product
1735 (p_parent_req_control_id => x_request_control_id,
1736 p_batch_id => p_batch_id,
1737 p_reexecution => 'N',
1738 p_product_name => l_current_product,
1739 p_workers_num => p_workers_num,
1740 p_batch_size => p_batch_size,
1741 x_return_msg => x_return_msg,
1742 x_return_status => x_return_status);
1743
1744 END IF;
1745
1746 EXCEPTION
1747 WHEN stop_at_product THEN
1748 x_return_status := 'E';
1749 x_return_msg := x_return_msg||'
1750 Child processes incomplete for '||l_current_product;
1751 log(x_return_msg);
1752
1753 END;
1754
1755
1756 PROCEDURE ar_master_upg
1757 (errbuf OUT NOCOPY VARCHAR2,
1758 retcode OUT NOCOPY VARCHAR2,
1759 p_ledger_id IN NUMBER,
1760 p_period_name IN VARCHAR2,
1761 p_workers_num IN NUMBER,
1762 p_batch_size IN NUMBER)
1763 IS
1764 CURSOR c_batch_id IS
1765 SELECT xla_upg_batches_s.NEXTVAL
1766 FROM dual;
1767
1768
1769 CURSOR exist_xla_active(p_ledger_id IN NUMBER)
1770 IS
1771 SELECT *
1772 FROM xla_upgrade_requests
1773 WHERE program_code = 'XLA_UPG'
1774 AND phase_num = 0
1775 AND status_code = 'A'
1776 AND ledger_id = p_ledger_id
1777 AND application_id = 222;
1778
1779 l_xla_upg_rec xla_upgrade_requests%ROWTYPE;
1780
1781 CURSOR child_reexc_request(p_ledger_id IN NUMBER,
1782 p_batch_id IN NUMBER)
1783 IS
1784 SELECT DECODE(program_code,'AR_UPG' ,'AR' ,
1785 'GIR_UPG' ,'GL' , --GIR
1786 'MRC_UPG','MRC',
1787 'JL_UPG' ,'JL',
1788 'PSA_UPG','PSA')
1789 FROM xla_upgrade_requests
1790 WHERE program_code <> 'XLA_UPG'
1791 AND phase_num <> 0
1792 AND status_code = 'A'
1793 AND ledger_id = p_ledger_id
1794 AND application_id = 222;
1795
1796
1797 CURSOR c_xla_upg_dates(p_ledger_id IN NUMBER)
1798 IS
1799 SELECT start_date,
1800 end_date
1801 FROM xla_upgrade_dates
1802 WHERE ledger_id = p_ledger_id;
1803
1804
1805 CURSOR c_ledger(p_ledger_id IN NUMBER) IS
1806 SELECT name
1807 FROM gl_ledgers
1808 WHERE ledger_id = p_ledger_id;
1809
1810 l_ledger_name VARCHAR2(30);
1811 l_start_date DATE;
1812 l_end_date DATE;
1813 l_reexec VARCHAR2(1);
1814 l_start_product VARCHAR2(30);
1815 l_batch_id NUMBER;
1816 l_result VARCHAR2(1) := 'N';
1817 l_run_required VARCHAR2(1);
1818 l_xla_dates_mismatch VARCHAR2(1);
1819 l_dates_missing VARCHAR2(1);
1820 x_start_date DATE;
1821 x_end_date DATE;
1822 x_return_status VARCHAR2(10);
1823 x_return_msg VARCHAR2(2000);
1824 x_request_control_id NUMBER;
1825
1826
1827 nothing_to_run EXCEPTION;
1828 should_relaunch_existing_upg EXCEPTION;
1829 no_date_in_xla_tab EXCEPTION;
1830 previous_run_dates_mismatch EXCEPTION;
1831
1832 BEGIN
1836
1833 log( message =>'ar_master_upg + ' );
1834 log( message =>' Starting at ' || to_char(SYSDATE, 'HH24:MI:SS') );
1835 log( message =>' p_workers_num :'||p_workers_num);
1837 IF p_workers_num < 1 THEN
1838 RAISE nothing_to_run;
1839 END IF;
1840
1841 retcode := '0';
1842
1843 OPEN exist_xla_active(p_ledger_id => p_ledger_id);
1844 FETCH exist_xla_active INTO l_xla_upg_rec;
1845 IF exist_xla_active%FOUND THEN
1846 l_reexec := 'Y';
1847 ELSE
1848 l_reexec := 'N';
1849 END IF;
1850 CLOSE exist_xla_active;
1851
1852
1853 IF l_reexec = 'Y' THEN
1854
1855 log('p_period_name:'||p_period_name);
1856 log('p_ledger_id:'||p_ledger_id);
1857 log('p_workers_num:'||p_workers_num);
1858 log('p_batch_size:'||p_batch_size);
1859 ------------------------------------------------------------
1860 -- Check the user parameters are the same as the current run
1861 ------------------------------------------------------------
1862 IF l_xla_upg_rec.period_name <> p_period_name OR
1863 l_xla_upg_rec.ledger_id <> p_ledger_id OR
1864 l_xla_upg_rec.workers_num <> p_workers_num OR
1865 l_xla_upg_rec.batch_size <> p_batch_size
1866 THEN
1867 RAISE should_relaunch_existing_upg;
1868 ELSE
1869
1870 OPEN c_xla_upg_dates(p_ledger_id => p_ledger_id);
1871 FETCH c_xla_upg_dates INTO
1872 l_start_date,
1873 l_end_date ;
1874 IF c_xla_upg_dates%NOTFOUND THEN
1875 l_dates_missing := 'Y';
1876 ELSE
1877 IF l_xla_upg_rec.start_date <> l_start_date OR
1878 l_xla_upg_rec.end_date <> l_end_date
1879 THEN
1880 l_xla_dates_mismatch := 'Y';
1881 END IF;
1882 END IF;
1883 CLOSE c_xla_upg_dates;
1884
1885 IF l_dates_missing = 'Y' THEN
1886 RAISE no_date_in_xla_tab;
1887 END IF;
1888
1889 IF l_xla_dates_mismatch = 'Y' THEN
1890 RAISE previous_run_dates_mismatch;
1891 END IF;
1892
1893 END IF;
1894 END IF;
1895
1896
1897
1898
1899 IF l_reexec = 'N' THEN
1900
1901 -----------------------
1902 -- 1 Verif XLA_UPG data
1903 -----------------------
1904 validate_xla_upg_data
1905 (p_ledger_id => p_ledger_id,
1906 x_start_date => x_start_date,
1907 x_end_date => x_end_date,
1908 x_return_status => x_return_status,
1909 x_return_msg => x_return_msg);
1910
1911 IF x_return_status = 'N' THEN
1912 RAISE nothing_to_run;
1913 ELSIF x_return_status = 'E' THEN
1914 RAISE fnd_api.G_EXC_ERROR;
1915 END IF;
1916
1917
1918 --------------------------
1919 --2 record the upgrade run
1920 --------------------------
1921 OPEN c_batch_id;
1922 FETCH c_batch_id INTO l_batch_id;
1923 CLOSE c_batch_id;
1924
1925 l_xla_upg_rec.phase_num := 0;
1926 l_xla_upg_rec.PROGRAM_CODE := 'XLA_UPG';
1927 l_xla_upg_rec.DESCRIPTION := 'ON DEMAND UPGRADE LEDGER:'||p_ledger_id||
1928 ' FROM '||x_start_date||' TO '|| x_end_date;
1929 l_xla_upg_rec.STATUS_CODE := 'A';
1930 l_xla_upg_rec.LEDGER_ID := p_ledger_id;
1931 l_xla_upg_rec.PERIOD_NAME := p_period_name;
1932 l_xla_upg_rec.WORKER_ID := NULL;
1933 l_xla_upg_rec.WORKERS_NUM := p_workers_num;
1934 l_xla_upg_rec.TABLE_NAME := NULL;
1935 l_xla_upg_rec.SCRIPT_NAME := NULL;
1936 l_xla_upg_rec.BATCH_SIZE := p_batch_size;
1937 l_xla_upg_rec.BATCH_ID := l_batch_id;
1938 l_xla_upg_rec.ORDER_NUM := 0;
1939 l_xla_upg_rec.START_DATE := x_start_date;
1940 l_xla_upg_rec.END_DATE := x_end_date;
1941
1942 insert_req_control
1943 (p_ins_rec => l_xla_upg_rec,
1944 x_request_control_id => x_request_control_id);
1945
1946 --------------------------------
1947 -- Ensure the record is recorded
1948 --------------------------------
1949 COMMIT;
1950
1951 l_xla_upg_rec.request_control_id := x_request_control_id;
1952 l_start_product := 'AR';
1953 l_run_required := 'Y';
1954 l_reexec := 'N';
1955
1956 ELSE
1957 OPEN child_reexc_request(p_ledger_id => p_ledger_id,
1958 p_batch_id => l_xla_upg_rec.batch_id);
1959 FETCH child_reexc_request INTO l_start_product;
1960 IF child_reexc_request%NOTFOUND THEN
1961 l_run_required := 'N';
1962 ELSE
1963 l_run_required := 'Y';
1964 l_reexec := 'Y';
1965 END IF;
1966 CLOSE child_reexc_request;
1967 END IF;
1968
1969
1970 ---------------------------------
1971 --Submitting subrequest per product
1972 ---------------------------------
1973 IF l_run_required = 'Y' THEN
1974
1975 lauch_and_relaunch
1976 (p_start_product => l_start_product,
1977 p_reexec => l_reexec,
1978 p_parent_req_control_id => l_xla_upg_rec.request_control_id,
1979 p_batch_id => l_xla_upg_rec.batch_id,
1980 p_workers_num => l_xla_upg_rec.workers_num,
1981 p_batch_size => l_xla_upg_rec.batch_size,
1982 x_return_status => x_return_status,
1983 x_return_msg => x_return_msg);
1984
1985
1986 IF x_return_status = 'S' THEN
1987
1988 update_req_control
1989 (p_request_control_id => l_xla_upg_rec.request_control_id,
1990 p_status => 'S');
1991
1992 --Update GL PERIOD STATUSES with XLA api
1993 update_gl_period;
1994 ELSE
1995 retcode := '-2';
1996
1997 END IF;
1998
1999 END IF;
2000
2001 log( message =>'ar_master_upg - ' );
2002
2003 EXCEPTION
2004 WHEN nothing_to_run THEN
2005 log('number of workers ='||p_workers_num);
2006 log('nothing to run');
2007 log( message =>' End at ' || to_char(SYSDATE, 'HH24:MI:SS') );
2008 log( message =>'ar_master_upg - ' );
2009 retcode := 0;
2010 errbuf := 'number of workers ='||p_workers_num||' nothing to run';
2011
2012 WHEN should_relaunch_existing_upg THEN
2013 retcode := -2;
2014 OPEN c_ledger(l_xla_upg_rec.ledger_id);
2015 FETCH c_ledger INTO l_ledger_name;
2016 CLOSE c_ledger;
2017 fnd_message.set_name('AR','AR_UNSUCCESSFUL_RUN_EXISTS');
2018 fnd_message.set_token('PERIOD' ,l_xla_upg_rec.period_name);
2019 fnd_message.set_token('LEDGER' ,l_ledger_name);
2020 fnd_message.set_token('WORKERS',l_xla_upg_rec.workers_num);
2021 fnd_message.set_token('SIZE' ,l_xla_upg_rec.batch_size);
2022 errbuf := fnd_message.get;
2023 outandlog(errbuf);
2024
2025 WHEN no_date_in_xla_tab THEN
2026 retcode := -2;
2027 fnd_message.set_name('AR','AR_XLA_UPGRADE_DATE_MISSING');
2028 errbuf := fnd_message.get;
2029 outandlog(errbuf);
2030
2031 WHEN previous_run_dates_mismatch THEN
2032 retcode := -2;
2033 fnd_message.set_name('AR','AR_XLA_UPGRADE_DATE_OVERLAP');
2034 fnd_message.set_token('START_DATE' ,l_start_date);
2035 fnd_message.set_token('END_DATE' ,l_end_date);
2036 fnd_message.set_token('EXIST_START_DATE',l_xla_upg_rec.start_date);
2037 fnd_message.set_token('EXIST_END_DATE' ,l_xla_upg_rec.end_date);
2038 errbuf := fnd_message.get;
2039 outandlog(errbuf);
2040
2041 END;
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058 END;