1 package body PA_IND_RATE_SCH_REVISIONS_PKG as
2 -- $Header: PAXCIRRB.pls 120.2.12000000.12 2007/08/31 07:37:01 prabsing ship $
3 -----------------------------------------------------------------------------
4 procedure start_to_gl(x_return_status IN OUT NOCOPY number,
5 x_stage IN OUT NOCOPY number,
6 x_start_date_active IN date)
7 is
8 x_dummy number;
9
10 begin
11 x_return_status := 0;
12 x_stage := 0;
13
14 -- check that start date is equal to a GL period start date.
15 begin
16 select 1
17 into x_dummy
18 from gl_period_statuses gp,
19 pa_implementations imp
20 where gp.start_date = x_start_date_active
21 and gp.application_id = Pa_Period_Process_Pkg.Application_Id
22 and gp.set_of_books_id = imp.set_of_books_id
23 and gp.adjustment_period_flag = 'N';
24
25 x_return_status := 0; -- ie. value exists is referenced table
26
27 EXCEPTION
28 when NO_DATA_FOUND then -- ie. value does not exist in ref table
29 x_return_status := 1;
30 x_stage := 1;
31
32 when OTHERS then
33 x_return_status := SQLCODE;
34 end;
35
36 end start_to_gl;
37 -----------------------------------------------------------------------------
38 procedure end_to_gl(x_return_status IN OUT NOCOPY number,
39 x_stage IN OUT NOCOPY number,
40 x_end_date_active IN date)
41 is
42 x_dummy number;
43
44 begin
45 x_return_status := 0;
46 x_stage := 0;
47
48 -- check that end date is equal to a GL period end date.
49 begin
50 select 1
51 into x_dummy
52 from gl_period_statuses gp,
53 pa_implementations imp
54 where gp.end_date = x_end_date_active
55 and gp.application_id = Pa_Period_Process_Pkg.Application_Id
56 and gp.set_of_books_id = imp.set_of_books_id
57 and gp.adjustment_period_flag = 'N';
58
59 x_return_status := 0; -- ie. value exists is referenced table
60
61 EXCEPTION
62 when NO_DATA_FOUND then -- ie. value does not exist in ref table
63 x_return_status := 1;
64 x_stage := 1;
65
66 when OTHERS then
67 x_return_status := SQLCODE;
68 end;
69
70 end end_to_gl;
71 ----------------------------------------------------------------------------
72 procedure check_dates(x_return_status IN OUT NOCOPY number,
73 x_stage IN OUT NOCOPY number,
74 x_ind_rate_sch_id IN number,
75 x_start_date_active IN date,
76 x_end_date_active IN date,
77 x_max_revision_id IN OUT NOCOPY number,
78 x_max_end_date_active IN OUT NOCOPY date)
79 is
80
81 x_max_start_date_active pa_ind_rate_sch_revisions.start_date_active%TYPE;
82 x_dummy number;
83
84 begin
85 x_return_status := 0;
86 x_stage := 0;
87
88 -- get the newest revision details from the database.
89 begin
90 select ind_rate_sch_revision_id,
91 start_date_active,
92 end_date_active
93 into x_max_revision_id,
94 x_max_start_date_active,
95 x_max_end_date_active
96 from pa_ind_rate_sch_revisions
97 where ind_rate_sch_id = x_ind_rate_sch_id
98 and start_date_active in
99 (select max(start_date_active)
100 from pa_ind_rate_sch_revisions
101 where ind_rate_sch_id = x_ind_rate_sch_id);
102
103 x_return_status := 0;
104
105 EXCEPTION
106 when TOO_MANY_ROWS then
107 x_return_status := 1;
108 x_stage := 1;
109 return;
110 end;
111
112 -- validation of the current revision against the last one in the
113 -- database.
114 begin
115 if (x_max_end_date_active is NULL) then
116 begin
117 if (x_start_date_active > x_max_start_date_active) then
118 begin
119 x_max_end_date_active := x_start_date_active - 1;
120 x_return_status := 0;
121 end;
122 else
123 begin
124 x_return_status := 1;
125 x_stage := 2;
126 return;
127 end;
128 end if;
129 end;
130 else
131 begin
132 if (x_start_date_active = x_max_end_date_active + 1) then
133 x_return_status := 0;
134 else
135 x_return_status := 1;
136 x_stage := 3;
137 return;
138 end if;
139 end;
140 end if;
141 end;
142
143 EXCEPTION
144 when OTHERS then
145 x_return_status := SQLCODE;
146
147 end check_dates;
148 -----------------------------------------------------------------------------
149 procedure check_references(x_return_status IN OUT NOCOPY number,
150 x_stage IN OUT NOCOPY number,
151 x_ind_rate_sch_revision_id IN number)
152 is
153 -- Begin bug 1718170
154 l_dummy0 number := 0; /* Bug : 5877935 */
155 l_dummy1 number := 0;
156 l_dummy2 number := 0;
157 l_dummy3 number := 0;
158 l_dummy4 number := 0;
159 -- l_gms_installed varchar2(1) := 'N'; /*Start Bug : 5877935 */ /* commented for 6334295 */
160 l_ind_compile_set_id number := 0; /* Added for Bug 6312921 */
161
162 /* Commenting code below for Bug 6312921 */
163 -- cursor c0 is
164 -- select 1
165 -- from pa_ind_compiled_sets ics
166 -- where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
167 -- and exists (select null
168 -- from pa_expenditure_items ei
169 -- where tp_ind_compiled_set_id = ics.ind_compiled_set_id);
170 -- cursor c1 is
171 -- select 1
172 -- from pa_ind_compiled_sets ics
173 -- where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
174 -- /*and not exists (select null -- Changed the not exists to exists */
175 -- and exists (select /*+ index(ei PA_EXPENDITURE_ITEMS_N11) */ null --added hint for bug 5845101
176 -- from pa_expenditure_items ei
177 -- where cost_ind_compiled_set_id = ics.ind_compiled_set_id);
178 --
179 -- cursor c2 is
180 -- select 1
181 -- from pa_ind_compiled_sets ics
182 -- where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
183 -- /*and not exists (select null -- Changed the not exists to exists */
184 -- and exists (select null
185 -- from pa_expenditure_items
186 -- where rev_ind_compiled_set_id = ics.ind_compiled_set_id);
187 --
188 -- cursor c3 is
189 -- select 1
190 -- from pa_ind_compiled_sets ics
191 --where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
192 -- /*and not exists (select null -- Changed the not exists to exists */
193 -- and exists (select null
194 -- from pa_expenditure_items
195 -- where inv_ind_compiled_set_id = ics.ind_compiled_set_id);
196 --
197 /* Commenting code for Bug 6312921 ends here */
198 /* Code added for Bug 6312921 :- starts */
199 Cursor cind is
200 select ics.ind_compiled_set_id
201 from pa_ind_compiled_sets ics
202 where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id ;
203
204 cursor c0 (l_ind_compile_set_id number) is
205 select 1
206 from dual
207 where exists (select null
208 /* from pa_expenditure_items ei */ /* for bug 6334295 */
209 from pa_expenditure_items_all ei
210 where tp_ind_compiled_set_id = l_ind_compile_set_id );
211
212 cursor c1 (l_ind_compile_set_id number) is
213 select 1
214 from dual
215 where exists (select /*+ index(ei PA_EXPENDITURE_ITEMS_N11) */ null --added hint for bug 5845101
216 /* from pa_expenditure_items ei */ /* for bug 6334295 */
217 from pa_expenditure_items_all ei
218 where cost_ind_compiled_set_id = l_ind_compile_set_id);
219
220 cursor c2 (l_ind_compile_set_id number ) is
221 select 1
222 from dual
223 where exists (select null
224 /* from pa_expenditure_items */ /* for bug 6334295 */
225 from pa_expenditure_items_all
226 where rev_ind_compiled_set_id = l_ind_compile_set_id);
227
228 cursor c3 (l_ind_compile_set_id number ) is
229 select 1
230 from dual
231 where exists (select null
232 /* from pa_expenditure_items */ /* for bug 6334295 */
233 from pa_expenditure_items_all
234 where inv_ind_compiled_set_id = l_ind_compile_set_id);
235
236 /* Code added for Bug 6312921 :- ends */
237
238 -- End bug 1718170
239 /*c_cint cursor is added for Bug 3041364 added for Capital Interest */
240 cursor c_cint is
241 select 1
242 from sys.dual
243 where exists (select 1
244 from pa_alloc_txn_details
245 where ind_rate_sch_revision_id = x_ind_rate_sch_revision_id);
246
247 /*Start ---- Added this cursor for 5877935 */
248 /* Code commented for Bug 6312921 - */
249 /*cursor c4 is
250 select 1 from dual where exists (
251 select 1 from pa_ind_compiled_sets ICS
252 WHERE ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id and
253 (exists
254 (SELECT NULL FROM pa_cost_distribution_lines_all CDL
255 WHERE ICS.ind_compiled_set_id = CDL.ind_compiled_set_id
256 )
257 OR (l_gms_installed = 'Y' and EXISTS
258 (SELECT NULL FROM gms_award_distributions adl
259 WHERE ICS.ind_compiled_set_id = adL.ind_compiled_set_id
260 )
261 )
262 OR (l_gms_installed = 'Y' and EXISTS
263 (SELECT NULL FROM gms_encumbrance_items gei
264 WHERE ICS.ind_compiled_set_id = gei.ind_compiled_set_id
265 )
266 ))
267 ); */
268 /* Code added for Bug 6312921 :- starts */
269
270 cursor c4 (l_ind_compile_set_id number ) is
271 select 1 from dual where exists
272
273 (SELECT NULL FROM pa_cost_distribution_lines_all CDL
274 WHERE CDL.ind_compiled_set_id = l_ind_compile_set_id
275 )
276 /* OR (l_gms_installed = 'Y' and EXISTS*/ /* for bug 6334295 */
277 OR EXISTS
278 (SELECT NULL FROM gms_award_distributions adl
279 WHERE adL.ind_compiled_set_id = l_ind_compile_set_id)
280
281 OR EXISTS
282 /* OR (l_gms_installed = 'Y' and EXISTS*/ /* for bug 6334295 */
283 /* (SELECT NULL FROM gms_encumbrance_items gei */ /* for bug 6334295 */
284 (SELECT NULL FROM gms_encumbrance_items_all gei
285 WHERE gei.ind_compiled_set_id = l_ind_compile_set_id
286 );
287
288 /* Code added for Bug 6312921 :- ends */
289 begin
290
291 /* Bug :5877935 ...condt */
292 /* IF gms_install.enabled THEN
293 l_gms_installed := 'Y' ;
294 END IF ; /* Bug :5877935 */ /* /* commented for bug 6334295 */
295
296 x_return_status := 0;
297 x_stage := 0;
298
299 -- check against expenditure items
300
301 /* This select does a full table scan on pa_expenditure_item
302 select 1
303 into x_dummy
304 from sys.dual
305 where not exists
306 (select 1
307 from pa_expenditure_items ei,
308 pa_ind_compiled_sets ics
309 where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
310 and ( (ei.cost_ind_compiled_set_id = ics.ind_compiled_set_id)
311 or (ei.rev_ind_compiled_set_id = ics.ind_compiled_set_id)
312 or (ei.inv_ind_compiled_set_id = ics.ind_compiled_set_id)));
313 */
314
315 /* Code commented for Bug 6312921 */
316 -- open c0;
317 -- fetch c0 into l_dummy0;
318 -- close c0;
319
320 -- IF l_dummy0 = 1 Then
321 -- x_return_status := 1;
322 -- x_stage := 1;
323 -- Else
324 -- Begin bug 1718170
325 -- open c1;
326 -- fetch c1 into l_dummy1;
327 -- close c1;
328
329 -- If l_dummy1 = 1 Then
330
331 -- x_return_status := 1;
332 -- x_stage := 1;
333
334 -- Else
335
336 -- open c2;
337 -- fetch c2 into l_dummy2;
338 -- close c2;
339
340 -- If l_dummy2 = 1 Then
341 -- x_return_status := 1;
342 -- x_stage := 1;
343 -- Else
344
345 -- open c3;
346 -- fetch c3 into l_dummy3;
347 -- close c3;
348
349 -- If l_dummy3 = 1 then
350 -- x_return_status := 1;
351 -- x_stage := 1;
352 -- else /* Added this for bug 5870999 */
353 -- open c4;
354 -- fetch c4 into l_dummy4;
355 -- close c4;
356
357 -- If l_dummy4 = 1 then
358 -- x_return_status := 1;
359 -- x_stage := 2;
360 -- end if;
361 -- End If;
362
363 -- End If;
364
365 -- End If;
366 -- End If;
367 /* Code commented for Bug 6312921 ends here*/
368
369 /* Code added for Bug 6312921 - starts Code commented above re-written here inside loop */
370 for cindrec in cind loop
371 open c0(cindrec.ind_compiled_set_id);
372 fetch c0 into l_dummy0;
373 close c0;
374
375 IF l_dummy0 = 1 Then
376 x_return_status := 1;
377 x_stage := 1;
378 exit;
379 Else /* Bug :5877935 */
380
381
382 -- Begin bug 1718170
383 open c1(cindrec.ind_compiled_set_id);
384 fetch c1 into l_dummy1;
385 close c1;
386
387 If l_dummy1 = 1 Then
388
389 x_return_status := 1;
390 x_stage := 1;
391 exit;
392
393 Else
394
395 open c2(cindrec.ind_compiled_set_id);
396 fetch c2 into l_dummy2;
397 close c2;
398
399 If l_dummy2 = 1 Then
400 x_return_status := 1;
401 x_stage := 1;
402 exit;
403 Else
404
405 open c3(cindrec.ind_compiled_set_id);
406 fetch c3 into l_dummy3;
407 close c3;
408
409 If l_dummy3 = 1 then
410
411 x_return_status := 1;
412 x_stage := 1;
413 exit;
414 else /* Added this for bug 5877935 */
415 open c4(cindrec.ind_compiled_set_id);
416 fetch c4 into l_dummy4;
417 close c4;
418
419 If l_dummy4 = 1 then
420 x_return_status := 1;
421 -- x_stage := 2; Bug 6377913
422 x_stage := 1;
423 exit;
424 end if;
425 End If;
426
427 End If;
428
429 End If;
430 End If;
431 end loop;
432 l_dummy4 :=0; /* since the same dummy is used in the above, initializing it back to 0 bug:5877935*/
433
434 -- End bug 1718170
435 /*Bug 3041364 added for Capital Interest */
436 open c_cint;
437 fetch c_cint into l_dummy4;
438 close c_cint;
439 If l_dummy4 = 1 then
440 x_return_status := 1;
441 x_stage := 1;
442 End If;
443 /*End of changes for Bug 3041364 added for Capital Interest */
444 EXCEPTION
445 when NO_DATA_FOUND then -- ie. value exists in ref table
446 x_return_status := 1;
447 x_stage := 1;
448
449 when OTHERS then
450 x_return_status := SQLCODE;
451
452 end check_references;
453 -----------------------------------------------------------------------------
454
455 procedure check_end_date_limit(x_return_status IN OUT NOCOPY number,
456 x_end_date_active IN date,
457 x_ind_rate_sch_revision_id IN number)
458 is
459
460
461 l_dummy0 number := 0;
462 l_dummy1 number := 0;
463 l_dummy2 number := 0;
464 l_dummy3 number := 0;
465 l_dummy4 number := 0;
466
467 -- l_gms_installed varchar2(1) := 'N';/* Bug6081362 */ /* commented for bug 6334295 */
468
469
470 cursor c0 is
471 select 1
472 from pa_ind_compiled_sets ics
473 where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
474 and exists (select null
475 from pa_expenditure_items ei
476 where tp_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);
477
478
479 cursor c1 is
480 select 1
481 from pa_ind_compiled_sets ics
482 where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
483 /*and not exists (select null -- Changed the not exists to exists */
484 and exists (select null
485 from pa_expenditure_items_all ei
486 where cost_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);
487
488 cursor c2 is
489 select 1
490 from pa_ind_compiled_sets ics
491 where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
492 /*and not exists (select null -- Changed the not exists to exists */
493 and exists (select null
494 from pa_expenditure_items_all
495 where rev_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);
496
497 cursor c3 is
498 select 1
499 from pa_ind_compiled_sets ics
500 where ics.ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
501 /*and not exists (select null -- Changed the not exists to exists */
502 and exists (select null
503 from pa_expenditure_items_all
504 where inv_ind_compiled_set_id = ics.ind_compiled_set_id and expenditure_item_date > x_end_date_active);
505
506 /* Bug6081362 */
507 cursor c4 is
508 select 1 from dual where exists (select 1 from pa_ind_compiled_sets ICS
509 WHERE ics.ind_rate_sch_revision_id =x_ind_rate_sch_revision_id and
510 exists (SELECT NULL FROM gms_encumbrance_items_all gei
511 WHERE ICS.ind_compiled_set_id = gei.ind_compiled_set_id
512 and ENCUMBRANCE_ITEM_DATE > x_end_date_active
513 /* and l_gms_installed = 'Y'*/ /* commented for bug 6334295 */
514 ));
515
516
517 begin
518
519 /* IF gms_install.enabled THEN
520 l_gms_installed := 'Y' ;
521 END IF ; */ /* commented for bug 6334295 */
522
523
524 x_return_status := 0;
525
526 open c0;
527 fetch c0 into l_dummy0;
528 close c0;
529
530 IF l_dummy0 = 1 Then
531 x_return_status := 1;
532
533 ELSE
534
535 open c1;
536 fetch c1 into l_dummy1;
537 close c1;
538
539 IF l_dummy1 = 1 Then
540 x_return_status := 1;
541
542 ELSE
543
544 open c2;
545 fetch c2 into l_dummy2;
546 close c2;
547
548 IF l_dummy2 = 1 Then
549 x_return_status := 1;
550
551 ELSE
552
553 open c3;
554 fetch c3 into l_dummy3;
555 close c3;
556
557 IF l_dummy3 = 1 Then
558 x_return_status := 1;
559
560 ELSE
561
562 /*Bug6081362 */
563 open c4;
564 fetch c4 into l_dummy4;
565 close c4;
566
567 IF l_dummy4 = 1 Then
568 x_return_status := 1;
569
570 END IF;
571 END IF;
572 END IF;
573 END IF;
574 END IF;
575
576 EXCEPTION
577 when NO_DATA_FOUND then
578 x_return_status := 1;
579
580 when OTHERS then
581 x_return_status := SQLCODE;
582
583 end check_end_date_limit;
584 -----------------------------------------------------------------------------
585
586 procedure check_start_date(x_return_status IN OUT NOCOPY number,
587 x_stage IN OUT NOCOPY number,
588 x_prev_end_date_active IN OUT NOCOPY date,
589 x_prev_revision_id IN OUT NOCOPY number,
590 x_ind_rate_sch_revision_id IN number,
591 x_ind_rate_sch_id IN number,
592 x_start_date_active IN date)
593 is
594 x_end_date_active date;
595 x_prev_start_date_active date;
596
597 begin
598 x_return_status := 0;
599 x_stage := 0;
600
601 begin
602 select end_date_active
603 into x_end_date_active
604 from pa_ind_rate_sch_revisions
605 where ind_rate_sch_revision_id = x_ind_rate_sch_revision_id;
606
607 if (x_end_date_active is null) then
608 begin
609 select end_date_active,
610 start_date_active,
611 ind_rate_sch_revision_id
612 into x_prev_end_date_active,
613 x_prev_start_date_active,
614 x_prev_revision_id
615 from pa_ind_rate_sch_revisions
616 where ind_rate_sch_id = x_ind_rate_sch_id
617 and end_date_active in
618 (select max(end_date_active)
619 from pa_ind_rate_sch_revisions
620 where ind_rate_sch_id = x_ind_rate_sch_id);
621 end;
622 else
623 begin
624 select end_date_active,
625 start_date_active,
626 ind_rate_sch_revision_id
627 into x_prev_end_date_active,
628 x_prev_start_date_active,
629 x_prev_revision_id
630 from pa_ind_rate_sch_revisions
631 where ind_rate_sch_id = x_ind_rate_sch_id
632 and end_date_active < x_end_date_active
633 and end_date_active in
634 (select max(end_date_active)
635 from pa_ind_rate_sch_revisions
636 where ind_rate_sch_id = x_ind_rate_sch_id
637 and end_date_active < x_end_date_active);
638 end;
639 end if;
640
641 x_return_status := 0;
642
643 EXCEPTION
644 when NO_DATA_FOUND then
645 x_return_status := 0; -- Since this is the first and only record for
646 -- this revision.
647
648 when TOO_MANY_ROWS then
649 x_return_status := 1;
650 x_stage := 1;
651 return;
652
653 when OTHERS then
654 x_return_status := SQLCODE;
655 end;
656
657 begin
658 -- Check that the previous revision that is to be changed is
659 -- not referenced in expenditure items. If it is , then the
660 -- change is not allowed.
661 pa_ind_rate_sch_revisions_pkg.check_references(x_return_status,
662 x_stage,
663 x_prev_revision_id);
664 if (x_return_status > 0) then
665 begin
666 x_stage := 5;
667 return;
668 end;
669 elsif (x_return_status < 0) then
670 return;
671 end if;
672 end;
673
674 if ((x_start_date_active - 1) < x_prev_start_date_active) then
675 x_return_status := 1;
676 x_stage := 2;
677 return;
678 else
679 x_prev_end_date_active := x_start_date_active - 1;
680 end if;
681
682 end check_start_date;
683 -----------------------------------------------------------------------------
684 procedure check_end_date(x_return_status IN OUT NOCOPY number,
685 x_stage IN OUT NOCOPY number,
686 x_next_start_date_active IN OUT NOCOPY date,
687 x_next_revision_id IN OUT NOCOPY number,
688 x_ind_rate_sch_revision_id IN number,
689 x_ind_rate_sch_id IN number,
690 x_end_date_active IN date)
691 is
692 x_start_date_active date;
693 x_next_end_date_active date;
694
695 begin
696 x_return_status := 0;
697 x_stage := 0;
698
699 begin
700 select start_date_active
701 into x_start_date_active
702 from pa_ind_rate_sch_revisions
703 where ind_rate_sch_revision_id = x_ind_rate_sch_revision_id;
704
705 select start_date_active,
706 end_date_active,
707 ind_rate_sch_revision_id
708 into x_next_start_date_active,
709 x_next_end_date_active,
710 x_next_revision_id
711 from pa_ind_rate_sch_revisions
712 where ind_rate_sch_id = x_ind_rate_sch_id
713 and start_date_active > x_start_date_active
714 and start_date_active in
715 (select min(start_date_active)
716 from pa_ind_rate_sch_revisions
717 where ind_rate_sch_id = x_ind_rate_sch_id
718 and start_date_active > x_start_date_active);
719
720 x_return_status := 0;
721
722 EXCEPTION
723 when NO_DATA_FOUND then
724 x_return_status := 0; -- Since this is the first and only record for
725 -- this revision.
726
727 when TOO_MANY_ROWS then
728 x_return_status := 1;
729 x_stage := 1;
730 return;
731
732 when OTHERS then
733 x_return_status := SQLCODE;
734 end;
735
736 begin
737 -- Check that the next revision that is to be changed is
738 -- not referenced in expenditure items. If it is , then the
739 -- change is not allowed.
740 pa_ind_rate_sch_revisions_pkg.check_references(x_return_status,
741 x_stage,
742 x_next_revision_id);
743 if (x_return_status > 0) then
744 begin
745 x_stage := 5;
746 return;
747 end;
748 elsif (x_return_status < 0) then
749 return;
750 end if;
751 end;
752
753 if (x_next_end_date_active is NULL) then
754 begin
755 x_next_start_date_active := x_end_date_active + 1;
756 end;
757 else
758 begin
759 if ((x_end_date_active + 1) > x_next_end_date_active) then
760 x_return_status := 1;
761 x_stage := 2;
762 return;
763 else
764 x_next_start_date_active := x_end_date_active + 1;
765 end if;
766 end;
767 end if;
768
769 end check_end_date;
770
771 -----------------------------------------------------------------------------
772 procedure check_multipliers(x_ind_rate_sch_revision_id IN number,
773 x_return_status IN OUT NOCOPY number,
774 x_stage IN OUT NOCOPY number)
775 is
776
777 dummy integer;
778 begin
779
780 x_stage := 100;
781 x_return_status := 0;
782
783 SELECT 1 INTO dummy
784 FROM sys.dual
785 WHERE EXISTS
786 (SELECT 1
787 FROM pa_ind_cost_multipliers
788 WHERE ind_rate_sch_revision_id = x_ind_rate_sch_revision_id);
789
790 exception
791 when OTHERS then
792 x_return_status := SQLCODE;
793
794 end check_multipliers;
795
796 -----------------------------------------------------------------------------
797 /***2933915:Added parameter x_ready_to_compile and another select in check_ready_compile()
798 to check if the multiplier has changed in pa_ind_cost_multipliers.This will form the
799 additional criteria for deciding if a revision can be compiled or not ***/
800
801 procedure check_ready_compile(x_ind_rate_sch_revision_id IN number,
802 x_ready_compile_flag IN OUT NOCOPY varchar2,
803 x_ready_for_compile IN OUT NOCOPY varchar2, /*2933915*/
804 x_compiled_flag IN OUT NOCOPY varchar2,
805 x_return_status IN OUT NOCOPY number,
806 x_stage IN OUT NOCOPY number)
807 is
808 begin
809
810 x_stage := 100;
811 x_return_status := 0;
812
813 SELECT ready_to_compile_flag, compiled_flag
814 INTO x_ready_compile_flag, x_compiled_flag
815 FROM pa_ind_rate_sch_revisions
816 WHERE ind_rate_sch_revision_id = x_ind_rate_sch_revision_id;
817
818 /***2933915 :This is to check if ready to compile multipliers are existing i.e if multipliers have changed*/
819 Begin
820 SELECT 'Y'
821 INTO x_ready_for_compile
822 FROM dual
823 WHERE exists (select 1
824 from pa_ind_cost_multipliers
825 WHERE ind_rate_sch_revision_id = x_ind_rate_sch_revision_id
826 AND nvl(ready_to_compile_flag,'N') in ('Y','X'));
827 exception
828 When NO_DATA_FOUND then /*2933915*/
829 x_ready_for_compile :='N';
830 End;
831 /*End of changes for bug# 2933915*/
832 exception
833 when OTHERS then
834 x_return_status := SQLCODE;
835
836 end check_ready_compile;
837 -----------------------------------------------------------------------------
838
839 end PA_IND_RATE_SCH_REVISIONS_PKG;
[Home] [Help]