1 package body PJM_PROJECT as
2 /* $Header: PJMPROJB.pls 120.7.12010000.2 2008/09/17 06:43:10 ybabulal ship $ */
3
4 FUNCTION Proj_Or_Seiban
5 ( X_project_id IN NUMBER
6 ) return varchar2 IS
7
8 L_proj_id NUMBER;
9 L_seiban NUMBER;
10 L_flag VARCHAR2(10) := 'N';
11
12 CURSOR p IS
13 select project_id from pa_projects_all
14 where project_id = X_project_id;
15
16 CURSOR s IS
17 select project_id from pjm_seiban_numbers
18 where project_id = X_project_id;
19
20 BEGIN
21
22 open p;
23 fetch p into L_proj_id;
24 if (p%notfound) then
25 open s;
26 fetch s into L_seiban;
27 if (s%notfound) then L_flag := 'N';
28 else L_flag := 'S';
29 end if;
30 close s;
31 else L_flag := 'P';
32 end if;
33
34 close p;
35 return (L_flag);
36
37 END Proj_Or_Seiban;
38
39
40 FUNCTION Is_Exp_Proj
41 ( X_project_id IN NUMBER ,
42 X_org_id IN NUMBER
43 ) return varchar2 IS
44
45 L_proj_id NUMBER;
46
47 BEGIN
48
49 select project_id into L_proj_id
50 from pa_projects_all_expend_v
51 where project_id = X_project_id
52 and expenditure_org_id = X_org_id;
53
54 return ('Y');
55
56 exception
57 when others then
58 return ('N');
59
60 END Is_Exp_Proj;
61
62
63 -- Private Function
64 --
65
66 FUNCTION Org_ID
67 ( X_inv_org IN NUMBER
68 ) RETURN NUMBER IS
69
70 L_Org_ID NUMBER;
71
72 CURSOR c IS
73 select TO_NUMBER(org_information3)
74 from hr_organization_information
75 where ( ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
76 and organization_id = X_inv_org;
77 --FND_PROFILE.VALUE('MFG_ORGANIZATION_ID');
78
79 BEGIN
80
81 -- Get the ou from inventory org default
82 OPEN c;
83 FETCH c INTO L_Org_ID;
84 CLOSE c;
85
86 -- If no mfg_organiation_id setup, then go for MOAC routine and client_info logic
87 -- BUG fix 5479390, should not use CLIENT_INFO as it is going away in R12.
88 -- IF L_Org_ID IS NULL THEN
89 -- L_Org_ID := to_number(rtrim(substr( userenv('CLIENT_INFO') , 1 , 10 ))) ;
90 -- END IF;
91
92 RETURN L_Org_ID;
93
94 END Org_ID;
95
96 --
97 -- Public Functions and Procedures
98 --
99
100 function all_proj_idtonum
101 ( X_project_id in number
102 ) return varchar2 IS
103 L_project_num varchar2(30);
104 cursor C1 is
105 select segment1
106 from pa_projects_all
107 where project_id = X_project_id
108 union
109 select project_number
110 from pjm_seiban_numbers
111 where project_id = X_project_id;
112
113 begin
114
115 if X_project_id is null then
116 return null;
117 end if;
118
119 open c1;
120 fetch c1 into L_project_num;
121 close c1;
122
123 return L_project_num;
124
125 end all_proj_idtonum;
126
127
128 function all_proj_idtoname
129 ( X_project_id in number
130 ) return varchar2 IS
131 L_project_name varchar2(30);
132 cursor C1 is
133 select name
134 from pa_projects_all
135 where project_id = X_project_id
136 union
137 select project_name
138 from pjm_seiban_numbers
139 where project_id = X_project_id;
140
141 begin
142
143 if X_project_id is null then
144 return null;
145 end if;
146
147 open c1;
148 fetch c1 into L_project_name;
149 close c1;
150
151 return L_project_name;
152
153 end all_proj_idtoname;
154
155
156 function val_proj_idtonum
157 ( X_project_id in number
158 , X_organization_id in number
159 ) return varchar2 IS
160 L_project_num varchar2(30);
161 L_dummy number;
162 cursor C1 is
163 select project_number
164 from PJM_PROJECTS_ORG_OU_SECURE_V /*Bug 6684081: Changed the view that should be used for MOAC. This API is called by OM team.*/
165 where project_id = X_project_id;
166
167 cursor C2 is
168 select 1
169 from pjm_project_parameters
170 where project_id = X_project_id
171 and organization_id = X_organization_id;
172
173 -- Bug Fix 7337239
174 cursor C3 is
175 select project_number
176 from PJM_PROJECTS_V
177 where project_id = X_project_id;
178
179 begin
180
181 if X_project_id is null then
182 return null;
183 end if;
184
185 IF X_organization_id IS NULL THEN
186 open c1;
187 fetch c1 into L_project_num;
188 close c1;
189 else
190 open c3;
191 fetch c3 into L_project_num;
192 close c3;
193 end if;
194
195 if X_organization_id is not null then
196 open c2;
197 fetch c2 into L_dummy;
198 close c2;
199 if L_dummy is null then
200 return null;
201 end if;
202 end if;
203
204 return L_project_num;
205
206 end val_proj_idtonum;
207
208
209 function val_proj_idtoname
210 ( X_project_id in number
211 , X_organization_id in number
212 ) return varchar2 IS
213 L_project_name varchar2(30);
214 L_dummy number;
215 cursor C1 is
216 select project_name
217 from pjm_projects_v
218 where project_id = X_project_id;
219
220 cursor C2 is
221 select 1
222 from pjm_project_parameters
223 where project_id = X_project_id
224 and organization_id = X_organization_id;
225
226 begin
227
228 if X_project_id is null then
229 return null;
230 end if;
231
232 open c1;
233 fetch c1 into L_project_name;
234 close c1;
235
236 if X_organization_id is not null then
237 open c2;
238 fetch c2 into L_dummy;
239 close c2;
240 if L_dummy is null then
241 return null;
242 end if;
243 end if;
244
245 return L_project_name;
246
247 end val_proj_idtoname;
248
249
250 function val_proj_numtoid
251 ( X_project_num in varchar2
252 , X_organization_id in number
253 ) return number IS
254 L_project_id number;
255 L_dummy number;
256 cursor C1 is
257 select project_id
258 from pjm_projects_v
259 where project_number = X_project_num;
260
261 cursor C2 is
262 select 1
263 from pjm_project_parameters
264 where project_id = L_project_id
265 and organization_id = X_organization_id;
266
267 begin
268
269 if X_project_num is null then
270 return null;
271 end if;
272
273 open c1;
274 fetch c1 into L_project_id;
275 close c1;
276
277 if X_organization_id is not null then
278 open c2;
279 fetch c2 into L_dummy;
280 close c2;
281 if L_dummy is null then
282 return null;
283 end if;
284 end if;
285
286 return L_project_id;
287
288 end val_proj_numtoid;
289
290
291 function val_proj_nametoid
292 ( X_project_name in varchar2
293 , X_organization_id in number
294 ) return number IS
295 L_project_id number;
296 L_dummy number;
297 cursor C1 is
298 select project_id
299 from pjm_projects_v
300 where project_name = X_project_name;
301
302 cursor C2 is
303 select 1
304 from pjm_project_parameters
305 where project_id = L_project_id
306 and organization_id = X_organization_id;
307
308 begin
309
310 if X_project_name is null then
311 return null;
312 end if;
313
314 open c1;
315 fetch c1 into L_project_id;
316 close c1;
317
318 if X_organization_id is not null then
319 open c2;
320 fetch c2 into L_dummy;
321 close c2;
322 if L_dummy is null then
323 return null;
324 end if;
325 end if;
326
327 return L_project_id;
328
329 end val_proj_nametoid;
330
331
332 function all_task_idtonum
333 ( X_task_id in number
334 ) return varchar2 IS
335 L_task_num varchar2(25);
336 cursor C1 is
337 select task_number
338 from pa_tasks
339 where task_id = X_task_id;
340
341 begin
342
343 if X_task_id is null then
344 return null;
345 end if;
346
347 open c1;
348 fetch c1 into L_task_num;
349 close c1;
350
351 return L_task_num;
352
353 end all_task_idtonum;
354
355
356 function all_task_idtoname
357 ( X_task_id in number
358 ) return varchar2 IS
359 L_task_name varchar2(20);
360 cursor C1 is
361 select task_name
362 from pa_tasks
363 where task_id = X_task_id;
364
365 begin
366
367 if X_task_id is null then
368 return null;
369 end if;
370
371 open c1;
372 fetch c1 into L_task_name;
373 close c1;
374
375 return L_task_name;
376
377 end all_task_idtoname;
378
379
380 function val_task_idtonum
381 ( X_project_id in number
382 , X_task_id in number
383 ) return varchar2 IS
384 L_task_num varchar2(25);
385 cursor C1 is
386 select task_number
387 from pjm_tasks_v
388 where project_id = X_project_id
389 and task_id = X_task_id;
390
391 begin
392
393 if X_task_id is null then
394 return null;
395 end if;
396
397 if ( val_proj_idtonum(X_project_id) is null ) then
398 return null;
399 end if;
400
401 open c1;
402 fetch c1 into L_task_num;
403 close c1;
404
405 return L_task_num;
406
407 end val_task_idtonum;
408
409
410 function val_task_idtoname
411 ( X_project_id in number
412 , X_task_id in number
413 ) return varchar2 IS
414 L_task_name varchar2(20);
415 cursor C1 is
416 select task_name
417 from pjm_tasks_v
418 where project_id = X_project_id
419 and task_id = X_task_id;
420
421 begin
422
423 if X_task_id is null then
424 return null;
425 end if;
426
427 if ( val_proj_idtonum(X_project_id) is null ) then
428 return null;
429 end if;
430
431 open c1;
432 fetch c1 into L_task_name;
433 close c1;
434
435 return L_task_name;
436
437 end val_task_idtoname;
438
439
440 function val_task_numtoid
441 ( X_project_num in varchar2
442 , X_task_num in varchar2
443 ) return number IS
444 L_project_id number;
445 L_task_id number;
446 cursor C1 is
447 select task_id
448 from pjm_tasks_v
449 where project_id = L_project_id
450 and task_number = X_task_num;
451
452 begin
453
454 if X_task_num is null then
455 return null;
456 end if;
457
458 L_project_id := val_proj_numtoid(X_project_num);
459
460 open c1;
461 fetch c1 into L_task_id;
462 close c1;
463
464 return L_task_id;
465
466 end val_task_numtoid;
467
468
469 function val_task_nametoid
470 ( X_project_name in varchar2
471 , X_task_name in varchar2
472 ) return number IS
473 L_project_id number;
474 L_task_id number;
475 cursor C1 is
476 select task_id
477 from pjm_tasks_v
478 where project_id = L_project_id
479 and task_name = X_task_name;
480
481 begin
482
483 if X_task_name is null then
484 return null;
485 end if;
486
487 L_project_id := val_proj_nametoid(X_project_name);
488
489 open c1;
490 fetch c1 into L_task_id;
491 close c1;
492
493 return L_task_id;
494
495 end val_task_nametoid;
496
497
498 --
499 -- Validate project references for a particular calling function
500 --
501
502 --
503 -- This variant provides backward compatibility where an early version
504 -- did not include date parameters
505 --
506 function validate_proj_references
507 ( X_inventory_org_id in number
508 , X_project_id in number
509 , X_task_id in number
510 , X_calling_function in varchar2
511 , X_error_code out nocopy varchar2
512 ) return boolean IS
513
514 retcode varchar2(1);
515
516 begin
517
518 retcode := validate_proj_references
519 ( X_inventory_org_id => X_inventory_org_id
520 , X_operating_unit => Org_ID(X_inventory_org_id)
521 , X_project_id => X_project_id
522 , X_task_id => X_task_id
523 , X_date1 => NULL
524 , X_date2 => NULL
525 , X_calling_function => X_calling_function
526 , X_error_code => X_error_code
527 );
528
529 if ( retcode = G_VALIDATE_SUCCESS ) then
530 return ( TRUE );
531 else
532 return ( FALSE );
533 end if;
534
535 end validate_proj_references;
536
537
538 --
539 -- This variant does not include the error code out parameter
540 -- and can be used in SQL
541 --
542 function validate_proj_references
543 ( X_inventory_org_id in number
544 , X_project_id in number
545 , X_task_id in number
546 , X_date1 in date
547 , X_date2 in date
548 , X_calling_function in varchar2
549 ) return varchar2 IS
550
551 retcode varchar2(1);
552 errcode varchar2(240);
553
554 begin
555
556 retcode := validate_proj_references
557 ( X_inventory_org_id => X_inventory_org_id
558 , X_operating_unit => Org_ID(X_inventory_org_id)
559 , X_project_id => X_project_id
560 , X_task_id => X_task_id
561 , X_date1 => X_date1
562 , X_date2 => X_date2
563 , X_calling_function => X_calling_function
564 , X_error_code => errcode
565 );
566
567 return ( retcode );
568
569 end validate_proj_references;
570
571
572 --
573 -- This variant uses the current operating unit as the default
574 --
575 function validate_proj_references
576 ( X_inventory_org_id in number
577 , X_project_id in number
578 , X_task_id in number
579 , X_date1 in date default null
580 , X_date2 in date default null
581 , X_calling_function in varchar2
582 , X_error_code out nocopy varchar2
583 ) return varchar2 IS
584
585 retcode varchar2(1);
586
587 begin
588
589 retcode := validate_proj_references
590 ( X_inventory_org_id => X_inventory_org_id
591 , X_operating_unit => Org_ID(X_inventory_org_id)
592 , X_project_id => X_project_id
593 , X_task_id => X_task_id
594 , X_date1 => X_date1
595 , X_date2 => X_date2
596 , X_calling_function => X_calling_function
597 , X_error_code => X_error_code
598 );
599
600 return ( retcode );
601
602 end validate_proj_references;
603
604
605 --
606 -- This is the main definition of the function
607 --
608 function validate_proj_references
609 ( X_inventory_org_id in number
610 , X_operating_unit in number
611 , X_project_id in number
612 , X_task_id in number
613 , X_date1 in date
614 , X_date2 in date
615 , X_calling_function in varchar2
616 , X_error_code out nocopy varchar2
617 ) return varchar2 IS
618
619 L_proj_ctrl_level varchar2(10);
620 L_project_number varchar2(30);
621 L_org_code varchar2(30);
622 L_operating_unit number;
623 retcode varchar2(80);
624 retsts varchar2(1);
625 start_date date;
626 end_date date;
627 param_start_date date;
628 param_end_date date;
632 failures number;
629 project_num varchar2(30);
630 task_num varchar2(30);
631 warnings number;
633 msgcount number;
634 msgdata varchar2(2000);
635 i number;
636 projflag varchar2(10);
637
638 VALIDATE_FAILURE exception;
639 VALIDATE_WARNING exception;
640
641 --
642 -- Cursor to check implementation of PJM and project control level
643 --
644 cursor pop is
645 select mp.organization_code
646 , decode(pop.organization_id ,
647 null , 'GEN-ORG NOT PJM ENABLED' ,
648 decode(pop.project_reference_enabled ,
649 'N' , 'GEN-ORG NOT PRJ REF ENABLED' ,
650 null))
651 , decode(pop.project_control_level, 1, 'PROJECT', 2, 'TASK')
652 from pjm_org_parameters pop
653 , mtl_parameters mp
654 where mp.organization_id = X_inventory_org_id
655 and pop.organization_id (+) = mp.organization_id;
656
657 --
658 -- Cursor to check
659 -- 1) Project is chargeable or not
660 -- 2) Project setup for current inventory org or not
661 -- 3) Start / End date for further processing
662 --
663 cursor proj is
664 select p.segment1
665 , decode(pp.project_id ,
666 null , 'GEN-INVALID PROJ FOR ORG',
667 decode(PJM_PROJECT.Is_Exp_Proj(p.project_id,L_operating_unit),
668 'Y', null,
669 decode(p.org_id,
670 L_operating_unit, 'GEN-INVALID PROJ' ,
671 'GEN-INVALID PROJ FOR OU')))
672 , trunc(p.start_date)
673 , trunc(p.completion_date)
674 , trunc(pp.start_date_active)
675 , trunc(pp.end_date_active)
676 from pa_projects_all p
677 , pjm_project_parameters pp
678 where p.project_id = X_project_id
679 and pp.project_id (+) = p.project_id
680 and pp.organization_id (+) = X_inventory_org_id;
681
682 cursor seiban is
683 select sn.project_number
684 , decode(pp.project_id ,
685 null , 'GEN-INVALID PROJ FOR ORG' , null)
686 , to_date(null)
687 , to_date(null)
688 , trunc(pp.start_date_active)
689 , trunc(pp.end_date_active)
690 from pjm_seiban_numbers sn
691 , pjm_project_parameters pp
692 where sn.project_id = X_project_id
693 and pp.organization_id (+) = X_inventory_org_id
694 and pp.project_id (+) = sn.project_id;
695
696 --
697 -- Cursor to check
698 -- 1) Task is valid or not
699 -- 2) Task is chargeable or not
700 -- 3) Start / End date for further processing
701 --
702 cursor task is
703 select te.task_number
704 , decode(te.chargeable_flag ,
705 'N' , 'GEN-TASK NOT CHARGEABLE' , null)
706 , trunc(te.start_date)
707 , trunc(te.completion_date)
708 from pa_tasks_all_expend_v te
709 where te.task_id = X_task_id
710 and te.project_id = X_project_id
711 and te.expenditure_org_id = L_operating_unit;
712
713 cursor base_task is
714 select t.task_number
715 , 'GEN-INVALID TASK'
716 , trunc(t.start_date)
717 , trunc(t.completion_date)
718 from pa_tasks t
719 where t.task_id = X_task_id
720 and t.project_id = X_project_id;
721
722 begin
723 --
724 -- if project is not given, no need for further processing
725 --
726 if ( X_project_id is null ) then
727 return( G_VALIDATE_SUCCESS );
728 end if;
729
730 --
731 -- Initialize the message table
732 --
733 fnd_msg_pub.initialize;
734
735 --
736 -- Initialize the failure and warning counts
737 --
738 failures := 0;
739 warnings := 0;
740
741 --
742 -- First make sure org is PJM enabled and retrieve
743 -- project control level
744 --
745 open pop;
746 fetch pop into L_org_code , retcode , L_proj_ctrl_level;
747 if ( pop%notfound ) then
748 X_error_code := 'GEN-ORG ID INVALID';
749 FND_MESSAGE.set_name('PJM', X_error_code);
750 FND_MESSAGE.set_token('ID', X_inventory_org_id);
751 FND_MSG_PUB.add;
752 failures := failures + 1;
753 end if;
754
755 close pop;
756
757 if ( retcode is not null ) then
758 X_error_code := retcode;
759 FND_MESSAGE.set_name('PJM', X_error_code);
760 FND_MESSAGE.set_token('ORG', L_org_code);
761 FND_MSG_PUB.add;
762 failures := failures + 1;
763 end if;
764
765 L_operating_unit := nvl( X_operating_unit , Org_ID(X_inventory_org_id) );
766 projflag := pjm_project.proj_or_seiban(X_project_id);
767
768 --
769 -- Now validating project reference; invalid if cursor returns
770 -- retcode value
771 --
772 if (projflag = 'P') then
773 open proj;
774 fetch proj into project_num , retcode , start_date , end_date
775 , param_start_date , param_end_date;
776 close proj;
777 elsif (projflag = 'S') then
778 open seiban;
779 fetch seiban into project_num , retcode , start_date , end_date
780 , param_start_date , param_end_date;
781 close seiban;
782 else
783 --
784 -- Not Project or Seiban: project ID is not valid
785 --
786 X_error_code := 'GEN-PROJ ID INVALID';
787 FND_MESSAGE.set_name('PJM' , X_error_code);
788 FND_MESSAGE.set_token('ID' , X_project_id);
792
789 FND_MSG_PUB.add;
790 failures := failures + 1;
791 end if;
793 if (projflag = 'P' or projflag = 'S') then
794 if ( retcode is not null ) then
795 X_error_code := retcode;
796 FND_MESSAGE.set_name('PJM', X_error_code);
797 FND_MESSAGE.set_token('PROJECT', project_num);
798 FND_MSG_PUB.add;
799 failures := failures + 1;
800 else
801 --
802 -- Project valid; now check against date
803 --
804 if ( X_date1 is not null ) then
805 if not ( trunc(X_date1) >= nvl(start_date , trunc(X_date1) - 1) and
806 trunc(X_date1) <= nvl(end_date , trunc(X_date1) + 1) ) then
807 --
808 -- X_date1 falls out of start/end date window
809 --
810 X_error_code := 'GEN-INVALID DATE FOR PROJ';
811 FND_MESSAGE.set_name('PJM', X_error_code);
812 FND_MESSAGE.set_token('DATE', X_date1);
813 FND_MSG_PUB.add;
814 warnings := warnings + 1;
815 end if;
816
817 if not ( trunc(X_date1) >= nvl(param_start_date , trunc(X_date1) - 1) and
818 trunc(X_date1) <= nvl(param_end_date , trunc(X_date1) + 1) ) then
819 --
820 -- X_date1 falls out of parameter start/end date window
821 --
822 X_error_code := 'GEN-INVALID DATE FOR ORG';
823 FND_MESSAGE.set_name('PJM', X_error_code);
824 FND_MESSAGE.set_token('DATE', X_date1);
825 FND_MSG_PUB.add;
826 warnings := warnings + 1;
827 end if;
828 end if;
829
830 if ( X_date2 is not null ) then
831 if not ( trunc(X_date2) >= nvl(start_date , trunc(X_date2) - 1) and
832 trunc(X_date2) <= nvl(end_date , trunc(X_date2) + 1) ) then
833 --
834 -- X_date2 falls out of start/end date window
835 --
836 X_error_code := 'GEN-INVALID DATE FOR PROJ';
837 FND_MESSAGE.set_name('PJM', X_error_code);
838 FND_MESSAGE.set_token('DATE', X_date2);
839 FND_MSG_PUB.add;
840 warnings := warnings + 1;
841 end if;
842
843 if not ( trunc(X_date2) >= nvl(param_start_date , trunc(X_date2) - 1) and
844 trunc(X_date2) <= nvl(param_end_date , trunc(X_date2) + 1) ) then
845 --
846 -- X_date2 falls out of start/end date window
847 --
848 X_error_code := 'GEN-INVALID DATE FOR ORG';
849 FND_MESSAGE.set_name('PJM', X_error_code);
850 FND_MESSAGE.set_token('DATE', X_date2);
851 FND_MSG_PUB.add;
852 warnings := warnings + 1;
853 end if;
854 end if;
855
856 -- end if; -- check task only if project is valid
857
858 if ( X_task_id is null ) then
859 --
860 -- No task reference present; make sure complies with project
861 -- control level
862 --
863 if ( L_proj_ctrl_level = 'TASK' ) then
864 X_error_code := 'GEN-INVALID PROJ REF';
865 FND_MESSAGE.set_name('PJM', X_error_code);
866 FND_MESSAGE.set_token('ORG', L_org_code);
867 FND_MSG_PUB.add;
868 failures := failures + 1;
869 end if;
870
871 else
872
873 open task;
874 fetch task into task_num , retcode , start_date , end_date;
875
876 --
877 -- If the cursor returns nothing, then the task is
878 -- not not in pa_tasks_all_expend_v
879 --
880 if ( task%notfound ) then
881
882 open base_task; -- Check if the task is in base table
883 fetch base_task into task_num , retcode , start_date , end_date;
884
885 -- If the base_task cursor returns nothing, then
886 -- either the task does not exist or not for this project
887 if ( base_task%notfound ) then
888 X_error_code := 'GEN-TASK ID INVALID';
889 FND_MESSAGE.set_name('PJM' , X_error_code);
890 FND_MESSAGE.set_token('ID' , X_task_id);
891 FND_MSG_PUB.add;
892 failures := failures + 1;
893 else
894 X_error_code := retcode;
895 FND_MESSAGE.set_name('PJM' , X_error_code);
896 FND_MESSAGE.set_token('TASK' , task_num);
897 FND_MSG_PUB.add;
898 failures := failures + 1;
899 end if;
900
901 --
902 -- Task reference invalid if cursor returns retcode value
903 --
904 elsif ( retcode is not null ) then
905
906 X_error_code := retcode;
907 FND_MESSAGE.set_name('PJM', X_error_code);
908 FND_MESSAGE.set_token('TASK', task_num);
909 FND_MSG_PUB.add;
910 failures := failures + 1;
911
912 else
913
914 if ( X_date1 is not null ) then
915 if not ( trunc(X_date1) >= nvl(start_date , trunc(X_date1) - 1) and
916 trunc(X_date1) <= nvl(end_date , trunc(X_date1) + 1) ) then
917 --
918 -- X_date1 falls out of start/end date window
919 --
920 X_error_code := 'GEN-INVALID DATE FOR TASK';
921 FND_MESSAGE.set_name('PJM', X_error_code);
922 FND_MESSAGE.set_token('DATE', X_date1);
923 FND_MSG_PUB.add;
924 warnings := warnings + 1;
925 end if;
926 end if;
927
928 if ( X_date2 is not null ) then
929 if not ( trunc(X_date2) >= nvl(start_date , trunc(X_date2) - 1) and
930 trunc(X_date2) <= nvl(end_date , trunc(X_date2) + 1) ) then
931 --
932 -- X_date2 falls out of start/end date window
933 --
934 X_error_code := 'GEN-INVALID DATE FOR TASK';
935 FND_MESSAGE.set_name('PJM', X_error_code);
936 FND_MESSAGE.set_token('DATE', X_date2);
937 FND_MSG_PUB.add;
938 warnings := warnings + 1;
939 end if;
940 end if;
941
942 end if;
943
944 if ( task%isopen ) then
945 close task;
946 end if;
947
948 if ( base_task%isopen ) then
949 close base_task;
950 end if;
951
952 end if; -- end of validate task
953 end if; -- end of validate project
954 end if; -- end of Project or Seiban
955
956 --
957 -- Calling extension routine for any custom validations
958 --
959 retsts := PJM_PROJECT_EXT.validate_proj_references
960 ( X_inventory_org_id => X_inventory_org_id
961 , X_operating_unit => L_operating_unit
962 , X_project_id => X_project_id
963 , X_task_id => X_task_id
964 , X_date1 => X_date1
965 , X_date2 => X_date2
966 , X_calling_function => X_calling_function
967 , X_error_code => X_error_code
968 );
969
970 if ( retsts = G_VALIDATE_FAILURE ) then
971 failures := failures + 1;
972 elsif ( retsts = G_VALIDATE_WARNING ) then
973 warnings := warnings + 1;
974 end if;
975
976 if ( failures > 0 ) then
977 raise VALIDATE_FAILURE;
978 elsif ( warnings > 0 ) then
979 raise VALIDATE_WARNING;
980 else
981 return ( G_VALIDATE_SUCCESS );
982 end if;
983
984 exception
985 when VALIDATE_FAILURE then
986 msgdata := '';
987 msgcount := FND_MSG_PUB.count_msg;
988 for i in 1..msgcount loop
989 msgdata := msgdata ||
990 i || '. ' ||
991 fnd_msg_pub.get( p_msg_index => i
992 , p_encoded => FND_API.G_FALSE ) ||
993 fnd_global.newline;
994 end loop;
995 fnd_message.set_name('PJM' , 'GEN-PROJ REF FAILURE');
996 fnd_message.set_token('ERRORTEXT' , msgdata);
997 return ( G_VALIDATE_FAILURE );
998
999 when VALIDATE_WARNING then
1000 msgdata := '';
1001 msgcount := fnd_msg_pub.count_msg;
1002 for i in 1..msgcount loop
1003 msgdata := msgdata ||
1004 i || '. ' ||
1005 fnd_msg_pub.get( p_msg_index => i
1006 , p_encoded => FND_API.G_FALSE ) ||
1007 fnd_global.newline;
1008 end loop;
1009 fnd_message.set_name('PJM' , 'GEN-PROJ REF WARNING');
1010 fnd_message.set_token('ERRORTEXT' , msgdata);
1011 return ( G_VALIDATE_WARNING );
1012
1013 when others then
1014 if fnd_msg_pub.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1015 fnd_msg_pub.add_exc_msg( p_pkg_name => 'PJM_PROJECT'
1016 , p_procedure_name => 'VALIDATE_PROJ_REFERENCES' );
1017 end if;
1018 return ( G_VALIDATE_FAILURE );
1019 end validate_proj_references;
1020
1021
1022 end PJM_PROJECT;
[Home] [Help]