1 package body PA_PROJECT_UTILS as
2 -- $Header: PAXPUTLB.pls 120.9.12020000.3 2013/04/02 07:05:10 speddi ship $
3
4
5 --
6 -- PROCEDURE
7 -- get_project_status_code
8 -- PURPOSE
9 -- This procedure retrieves project status code for a specified
10 -- project status.
11 -- HISTORY
12 -- 16-OCT-95 R. Chiu Created
13 --
14 l_pkg_name VARCHAR2(30) := 'PA_PROJECT_UTILS';
15 procedure get_project_status_code (x_project_status IN varchar2
16 , x_project_status_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
17 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
18 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
19 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
20 is
21 cursor c1 is
22 select project_status_code
23 from pa_project_statuses
24 where project_status_name = x_project_status;
25
26 c1_rec c1%rowtype;
27 old_stack varchar2(630);
28
29 begin
30 x_err_code := 0;
31 old_stack := x_err_stack;
32 x_err_stack := x_err_stack || '->get_project_status_code';
33
34 x_err_stage := 'get status code of project status '|| x_project_status;
35
36 open c1;
37 fetch c1 into c1_rec;
38 if c1%notfound then
39 raise NO_DATA_FOUND;
40 else
41 x_project_status_code := c1_rec.project_status_code ;
42 end if;
43 close c1;
44
45 x_err_stack := old_stack;
46
47 exception
48
49 when NO_DATA_FOUND then
50 x_err_code := 10;
51 x_err_stage := 'PA_PROJ_NO_STATUS_CODE';
52 when others then
53 x_err_code := SQLCODE;
54
55 end get_project_status_code;
56
57
58 -- PROCEDURE
59 -- get_distribution_rule_code
60 -- PURPOSE
61 -- This function retrieves distribution rule name given the
62 -- user-friendly name that describes the distribution rule.
63 --
64 -- HISTORY
65 -- 20-OCT-95 R. Chiu Created
66 --
67 procedure get_distribution_rule_code ( x_dist_name IN varchar2
68 , x_dist_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
69 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
70 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
71 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
72 is
73 cursor c1 is
74 select distribution_rule
75 from pa_distribution_rules
76 where meaning = x_dist_name;
77
78 c1_rec c1%rowtype;
79 old_stack varchar2(630);
80
81 begin
82 x_err_code := 0;
83 old_stack := x_err_stack;
84 x_err_stack := x_err_stack || '->get_distribution_rule_code';
85
86 x_err_stage := 'get distribution rule code of '|| x_dist_name;
87
88 open c1;
89 fetch c1 into c1_rec;
90 if c1%notfound then
91 raise NO_DATA_FOUND;
92 else
93 x_dist_code := c1_rec.distribution_rule ;
94 end if;
95 close c1;
96
97 x_err_stack := old_stack;
98
99 exception
100
101 when NO_DATA_FOUND then
102 x_err_code := 10;
103 x_err_stage := 'PA_PROJ_NO_DIST_RULE';
104 when others then
105 x_err_code := SQLCODE;
106
107 end get_distribution_rule_code;
108
109 --
110 -- PROCEDURE
111 -- get_proj_type_class_code
112 -- PURPOSE
113 -- This procedure retrieves project type class code for
114 -- a given project type or project id. If both project type
115 -- and project id are passed, then procedure treated it as if
116 -- only project id were passed.
117 --
118 -- HISTORY
119 -- 20-OCT-95 R. Chiu Created
120 --
121 procedure get_proj_type_class_code ( x_project_type IN varchar2
122 , x_project_id IN number
123 , x_proj_type_class_code OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
124 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
125 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
126 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
127 is
128 cursor c1 is
129 select project_type_class_code
130 from pa_project_types
131 where project_type = x_project_type;
132
133 cursor c2 is
134 select project_type_class_code
135 from pa_project_types_all t, pa_projects_all p -- Modified pa_projects and pa_project_types to pa_projects_all and pa_project_types_all for bug#3512486
136 where p.project_id = x_project_id
137 and p.project_type = t.project_type
138 -- and nvl(p.org_id, -99) = nvl(t.org_id, -99); -- Added the and condition for bug#3512486
139 and p.org_id = t.org_id ;
140
141 c1_rec c1%rowtype;
142 c2_rec c2%rowtype;
143 old_stack varchar2(630);
144
145 begin
146 x_err_code := 0;
147 old_stack := x_err_stack;
148 x_err_stack := x_err_stack || '->get_proj_type_class_code';
149
150 if (x_project_type is null and x_project_id is null) then
151 x_err_code := 10;
152 x_err_stage := 'PA_NO_TYPE_AND_ID';
153 return;
154 end if;
155
156 x_err_stage := 'get proj type class code of '|| x_project_type;
157
158 if (x_project_id is null) then
159 open c1;
160 fetch c1 into c1_rec;
161 if c1%notfound then
162 close c1;
163 raise NO_DATA_FOUND;
164 else
165 x_proj_type_class_code := c1_rec.project_type_class_code ;
166 end if;
167 close c1;
168 else
169 open c2;
170 fetch c2 into c2_rec;
171 if c2%notfound then
172 close c2;
173 raise NO_DATA_FOUND;
174 else
175 x_proj_type_class_code := c2_rec.project_type_class_code ;
176 end if;
177 close c2;
178 end if;
179
180 x_err_stack := old_stack;
181
182 exception
183
184 when NO_DATA_FOUND then
185 x_err_code := 10;
186 x_err_stage := 'PA_NO_PROJ_TYPE_CLASS';
187 when others then
188 x_err_code := SQLCODE;
189
190 end get_proj_type_class_code;
191
192
193 --
194 -- FUNCTION
195 -- check_unique_project_name
196 -- PURPOSE
197 -- This function returns 1 if a project name is not already
198 -- used in PA system and returns 0 if name is used.
199 -- If Oracle error occurs, Oracle error number is returned.
200 -- HISTORY
201 -- 20-OCT-95 R. Chiu Created
202 --
203 function check_unique_project_name (x_project_name IN varchar2,
204 x_rowid IN varchar2 ) return number
205 is
206 cursor c1 is
207 select project_id
208 from pa_projects_all
209 where name = x_project_name
210 AND (x_ROWID IS NULL OR x_ROWID <> pa_projects_all.ROWID);
211
212 c1_rec c1%rowtype;
213
214 begin
215 if (x_project_name is null ) then
216 return(null);
217 end if;
218
219 open c1;
220 fetch c1 into c1_rec;
221 if c1%notfound then
222 close c1;
223 return(1);
224 else
225 close c1;
226 return(0);
227 end if;
228
229 exception
230 when others then
231 return(SQLCODE);
232
233 end check_unique_project_name;
234
235
236 --
237 -- FUNCTION
238 -- check_unique_long_name
239 -- PURPOSE
240 -- This function returns 1 if a long name is not already
241 -- used in PA system and returns 0 if name is used.
242 -- If Oracle error occurs, Oracle error number is returned.
243 -- HISTORY
244 -- 26-OCT-02 MUMOHAN Created
245 --
246 function check_unique_long_name (x_long_name IN varchar2,
247 x_rowid IN varchar2 ) return number
248 is
249 cursor c1 is
250 select project_id
251 from pa_projects_all
252 where long_name = x_long_name
253 AND (x_ROWID IS NULL OR x_ROWID <> pa_projects_all.ROWID);
254
255 c1_rec c1%rowtype;
256
257 begin
258 if (x_long_name is null ) then
259 return(null);
260 end if;
261
262 open c1;
263 fetch c1 into c1_rec;
264 if c1%notfound then
265 close c1;
266 return(1);
267 else
268 close c1;
269 return(0);
270 end if;
271
272 exception
273 when others then
274 return(SQLCODE);
275
276 end check_unique_long_name;
277
278 --
279 -- FUNCTION
280 -- check_unique_project_number
281 -- PURPOSE
282 -- This function returns 1 if a project number is not already
283 -- used in PA system and returns 0 if number is used.
284 -- If Oracle error occurs, Oracle error number is returned.
285 -- HISTORY
286 -- 20-OCT-95 R. Chiu Created
287 --
288 function check_unique_project_number (x_project_number IN varchar2,
289 x_rowid IN varchar2 ) return number
290 is
291 cursor c1 is
292 select project_id
293 from pa_projects_all
294 where segment1 = x_project_number
295 AND (x_ROWID IS NULL OR x_ROWID <> pa_projects_all.ROWID);
296
297 c1_rec c1%rowtype;
298
299 begin
300 if (x_project_number is null ) then
301 return(null);
302 end if;
303
304 open c1;
305 fetch c1 into c1_rec;
306 if c1%notfound then
307 close c1;
308 return(1);
309 else
310 close c1;
311 return(0);
312 end if;
313
314 exception
315 when others then
316 return(SQLCODE);
317
318 end check_unique_project_number;
319
320
321 --
322 -- FUNCTION
323 -- check_unique_proj_class
324 -- PURPOSE
325 -- This function returns 1 if a project class code is
326 -- not already used for a specified project and class
327 -- category in PA system and returns 0 otherwise.
328 -- If a user does not supply all the values for project id,
329 -- x_class_category, and x_class_code, then null will
330 -- be returned.
331 -- If Oracle error occurs, Oracle error number is returned.
332 --
333 --
334 -- HISTORY
335 -- 20-OCT-95 R. Chiu Created
336 --
337 function check_unique_proj_class (x_project_id IN number
338 , x_class_category IN varchar2
339 , x_class_code IN varchar2
340 , x_rowid IN varchar2 ) return number
341 is
342 cursor c1 is
343 select 1
344 from pa_project_classes
345 where project_id = x_project_id
346 AND class_category = x_class_category
347 AND class_code = x_class_code
348 AND (x_rowid is null
349 or x_rowid <> pa_project_classes.rowid);
350
351 c1_rec c1%rowtype;
352
353 begin
354 if (x_project_id is null or x_class_category is null or
355 x_class_code is null ) then
356 return (null);
357 end if;
358
359 open c1;
360 fetch c1 into c1_rec;
361 if c1%notfound then
362 close c1;
363 return(1);
364 else
365 close c1;
366 return(0);
367 end if;
368
369 exception
370 when others then
371 return(SQLCODE);
372 end check_unique_proj_class;
373
374
375 --
376 -- FUNCTION
377 -- check_unique_customer
378 -- PURPOSE
379 -- This function returns 1 if a customer is unique for
380 -- the specified project and returns 0 if that customer
381 -- already exists for that project. If a user does not
382 -- supply all the values, then null is returned. If Oracle
383 -- error occurs, Oracle error number is returned.
384 --
385 -- HISTORY
386 -- 20-OCT-95 R. Chiu Created
387 --
388 function check_unique_customer (x_project_id IN number
389 , x_customer_id IN varchar2
390 , x_rowid IN varchar2 ) return number
391 is
392 cursor c1 is
393 select 1
394 from pa_project_customers
395 where project_id = x_project_id
396 AND customer_id = x_customer_id
397 AND (x_rowid is null
398 or x_rowid <> pa_project_customers.rowid);
399
400 c1_rec c1%rowtype;
401
402 begin
403 if (x_project_id is null or x_customer_id is null ) then
404 return (null);
405 end if;
406
407 open c1;
408 fetch c1 into c1_rec;
409 if c1%notfound then
410 close c1;
411 return(1);
412 else
413 close c1;
414 return(0);
415 end if;
416
417 exception
418 when others then
419 return(SQLCODE);
420 end check_unique_customer;
421
422 --
423 -- FUNCTION
424 -- check_project_type_valid
425 -- PURPOSE
426 -- This function returns 1 if a project type is valid in
427 -- PA system and returns 0 if it's not valid.
428 -- If Oracle error occurs, Oracle error number is returned.
429 --
430 -- HISTORY
431 -- 20-OCT-95 R. Chiu Created
432 --
433 function check_project_type_valid (x_project_type IN varchar2 ) return number
434 is
435 cursor c1 is
436 select project_type from pa_project_types
437 where project_type = x_project_type ;
438
439 c1_rec c1%rowtype;
440
441 begin
442 if (x_project_type is null ) then
443 return(null);
444 end if;
445
446 open c1;
447 fetch c1 into c1_rec;
448 if c1%notfound then
449 close c1;
450 return(0);
451 else
452 close c1;
453 return(1);
454 end if;
455
456 exception
457 when others then
458 return(SQLCODE);
459 end check_project_type_valid;
460
461
462 --
463 -- FUNCTION
464 -- check_manager_exists
465 -- PURPOSE
466 -- This function returns 1 if a project has an acting
467 -- manager and returns 0 if no manage is found.
468 -- If Oracle error occurs, Oracle error number is returned.
469 --
470 -- HISTORY
471 -- 20-OCT-95 R. Chiu Created
472 --
473 function check_manager_exists (x_project_id IN number ) return number
474 is
475 cursor c1 is
476 select 1
477 from sys.dual
478 where exists (SELECT person_id
479 FROM pa_project_players
480 WHERE project_id = x_project_id
481 and project_role_type = 'PROJECT MANAGER'
482 AND TRUNC(sysdate) BETWEEN start_date_active
483 AND NVL(end_date_active, TRUNC(sysdate)));
484
485 c1_rec c1%rowtype;
486
487 begin
488 if (x_project_id is null) then
489 return(null);
490 end if;
491
492 open c1;
493 fetch c1 into c1_rec;
494 if c1%notfound then
495 close c1;
496 return (0);
497 else
498 close c1;
499 return(1);
500 end if;
501
502
503 exception
504 when others then
505 return (SQLCODE);
506 end check_manager_exists;
507
508
509 -- FUNCTION
510 -- check_bill_split
511 -- PURPOSE
512 -- This function returns 1 if a project has total customer
513 -- contribution of 100% and returns 0 if total contribution
514 -- is less than 100%.
515 -- If Oracle error occurs, Oracle error number is returned.
516 --
517 -- HISTORY
518 -- 20-OCT-95 R. Chiu Created
519 --
520 function check_bill_split (x_project_id IN number ) return number
521 is
522 cursor c1 is
523 SELECT NULL
524 FROM PA_PROJECT_CUSTOMERS
525 WHERE PROJECT_ID = x_PROJECT_ID
526 GROUP BY PROJECT_ID
527 HAVING SUM(CUSTOMER_BILL_SPLIT) = 100;
528
529 c1_rec c1%rowtype;
530
531 begin
532 if (x_project_id is null) then
533 return(null);
534 end if;
535
536 open c1;
537 fetch c1 into c1_rec;
538 if c1%notfound then
539 close c1;
540 return (0);
541 else
542 close c1;
543 return(1);
544 end if;
545
546 exception
547 when others then
548 return (SQLCODE);
549 end check_bill_split;
550
551
552 -- FUNCTION
553 -- check_bill_contact_exists
554 -- PURPOSE
555 -- This function returns 1 if a project has a billing contact
556 -- for a customer whose contribution is greater than 0 and
557 -- returns 0 if this condition is not met for that project.
558 -- If Oracle error occurs, Oracle error number is returned.
559 --
560 -- HISTORY
561 -- 20-OCT-95 R. Chiu Created
562 --
563 function check_bill_contact_exists (x_project_id IN number ) return number
564 is
565 cursor c1 is
566 select 1
567 from sys.dual
568 where exists (SELECT NULL
569 FROM PA_PROJECT_CUSTOMERS CUST
570 WHERE CUST.PROJECT_ID = x_project_id
571 AND CUST.CUSTOMER_BILL_SPLIT > 0
572 AND EXISTS (SELECT NULL
573 FROM PA_PROJECT_CONTACTS CONT
574 WHERE CONT.PROJECT_ID = x_project_id
575 AND CONT.CUSTOMER_ID= CUST.CUSTOMER_ID
576 AND CONT.PROJECT_CONTACT_TYPE_CODE = 'BILLING'));
577
578 c1_rec c1%rowtype;
579
580 begin
581 if (x_project_id is null) then
582 return(null);
583 end if;
584
585 open c1;
586 fetch c1 into c1_rec;
587 if c1%notfound then
588 close c1;
589 return (0);
590 else
591 close c1;
592 return(1);
593 end if;
594
595 exception
596 when others then
597 return (SQLCODE);
598 end check_bill_contact_exists;
599
600
601 -- FUNCTION
602 -- check_class_category
603 -- PURPOSE
604 -- This function returns 1 if a project has all the mandatory
605 -- class categories and returns 0 if mandatory class category
606 -- is missing.
607 -- If Oracle error occurs, Oracle error number is returned.
608 --
609 -- HISTORY
610 -- 20-OCT-95 R. Chiu Created
611 --
612 function check_class_category (x_project_id IN number ) return number
613 is
614 cursor c1 is
615 select 1
616 from sys.dual
617 where exists (SELECT NULL
618 -- anlee - modified for Classifications enhancements
619 /*
620 FROM PA_CLASS_CATEGORIES CC
621 WHERE MANDATORY_FLAG = 'Y'
622 AND TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE_ACTIVE)
623 AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
624 */
625 FROM PA_VALID_CATEGORIES_V VC,
626 PA_PROJECTS_ALL PPA,
627 PA_PROJECT_TYPES_ALL PPTA
628 WHERE VC.MANDATORY_FLAG = 'Y'
629 AND PPA.PROJECT_ID = x_project_id
630 AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
631 --AND nvl(PPA.ORG_ID, -99) = nvl(PPTA.ORG_ID, -99)
632 AND PPA.org_id = PPTA.org_id
633 AND VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
634 AND NOT EXISTS (SELECT NULL
635 FROM PA_PROJECT_CLASSES PC
636 WHERE PC.PROJECT_ID = x_PROJECT_ID
637 -- AND PC.CLASS_CATEGORY = CC.CLASS_CATEGORY));
638 AND PC.CLASS_CATEGORY = VC.CLASS_CATEGORY));
639
640 c1_rec c1%rowtype;
641
642 begin
643 if (x_project_id is null) then
644 return(null);
645 end if;
646
647 open c1;
648 fetch c1 into c1_rec;
649 if c1%notfound then
650 close c1;
651 return (1);
652 else
653 close c1;
654 return(0);
655 end if;
656
657 exception
658 when others then
659 return (SQLCODE);
660 end check_class_category;
661
662
663 -- FUNCTION
664 -- check_draft_inv_exists
665 -- PURPOSE
666 -- This function returns 1 if draft invoice exists for a project
667 -- and returns 0 if no draft invoice is found.
668 --
669 -- If Oracle error occured, Oracle error code is returned.
670 --
671 -- HISTORY
672 -- 20-OCT-95 R. Chiu Created
673 --
674 function check_draft_inv_exists (x_project_id IN number ) return number
675 is
676 x_proj_id number;
677
678 cursor c1 is
679 SELECT 1
680 FROM sys.dual
681 WHERE EXISTS (SELECT NULL
682 FROM pa_draft_invoices
683 WHERE project_id = x_project_id);
684
685 c1_rec c1%rowtype;
686
687 begin
688 if (x_project_id is null) then
689 return(null);
690 end if;
691
692 open c1;
693 fetch c1 into c1_rec;
694 if c1%notfound then
695 close c1;
696 return(0);
697 else
698 close c1;
699 return(1);
700 end if;
701
702
703 exception
704 when others then
705 return(SQLCODE);
706 end check_draft_inv_exists;
707
708
709 -- FUNCTION
710 -- check_draft_rev_exists
711 -- PURPOSE
712 -- This function returns 1 if draft revenue exists for a project
713 -- and returns 0 if no draft revenue is found.
714 --
715 -- If Oracle error occured, Oracle error code is returned.
716 --
717 -- HISTORY
718 -- 20-OCT-95 R. Chiu Created
719 --
720 function check_draft_rev_exists (x_project_id IN number ) return number
721 is
722 x_proj_id number;
723
724 cursor c1 is
725 SELECT 1
726 FROM sys.dual
727 WHERE EXISTS (SELECT NULL
728 FROM pa_draft_revenues
729 WHERE project_id = x_project_id);
730
731 c1_rec c1%rowtype;
732
733 begin
734 if (x_project_id is null) then
735 return(null);
736 end if;
737
738 open c1;
739 fetch c1 into c1_rec;
740 if c1%notfound then
741 close c1;
742 return(0);
743 else
744 close c1;
745 return(1);
746 end if;
747
748 exception
749 when others then
750 return(SQLCODE);
751 end check_draft_rev_exists;
752
753
754 -- FUNCTION
755 -- check_created_proj_reference
756 -- PURPOSE
757 -- This function returns 1 if a project is referenced
758 -- by another project in pa_projects.created_from_project_id
759 -- and returns 0 if a project is not referenced.
760 --
761 -- If Oracle error occured, Oracle error code is returned.
762 --
763 -- HISTORY
764 -- 20-OCT-95 R. Chiu Created
765 --
766 function check_created_proj_reference (x_project_id IN number ) return number
767 is
768 cursor c1 is
769 select 1
770 from sys.dual
771 where exists (SELECT null
772 FROM pa_projects
773 where created_from_project_id = x_project_id);
774
775 c1_rec c1%rowtype;
776
777 begin
778 open c1;
779 fetch c1 into c1_rec;
780 if c1%notfound then
781 close c1;
782 return(0);
783 else
784 close c1;
785 return(1);
786 end if;
787
788 exception
789 when others then
790 return(SQLCODE);
791
792 end check_created_proj_reference;
793
794
795 --
796 -- PROCEDURE
797 -- check_delete_project_ok
798 -- PURPOSE
799 -- This objective of this API is to check if it is OK to
800 -- delete a project
801 --
802 -- In order to delete a project, a project must NOT
803 -- have any of the following:
804 --
805 -- * Event
806 -- * Expenditure item
807 -- * Puchase order line
808 -- * Requisition line
809 -- * Supplier invoice (ap invoice)
810 -- * Funding
811 -- * Budget
812 -- * Committed transactions
813 -- * Compensation rule sets
814 -- * Project is referenced by others
815 -- * Project is used in allocations
816 -- * Contract
817 -- * Sourcing
818
819 procedure check_delete_project_ok ( x_project_id IN number
820 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --Bug 2947492
821 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
822 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
823 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
824 is
825
826 old_stack varchar2(630);
827 status_code number;
828 l_return_val varchar2(1);
829 dummy_null varchar2(30) default null;
830 cursor p1 is select 1 from pa_project_types
831 where burden_sum_dest_project_id = x_project_id;
832 temp number;
833 --Ansari
834 x_used_in_OTL BOOLEAN; --To pass to OTL API.
835 --For org fc
836 l_return_status VARCHAR2(30);
837 l_err_code VARCHAR2(2500);
838 --For org fc
839 --Ansari
840
841 -- Ram Namburi
842 -- Bug Fix 4759187
843 l_msg_count NUMBER;
844 l_msg_data VARCHAR2(2000);
845
846 -- Bug 5750591: Cursor to check whether the project_id passed is PJR Unassigned time project
847 l_temp_char VARCHAR2(1);
848 CURSOR c_is_unassigned_time_proj(l_project_id IN NUMBER) IS
849 SELECT 'Y'
850 FROM pa_projects_all ppa, pa_forecasting_options_all pfoa
851 WHERE ppa.project_id = l_project_id
852 AND ppa.org_id = pfoa.org_id
853 AND (pfoa.bill_unassign_proj_id = ppa.project_id OR pfoa.nonbill_unassign_proj_id = ppa.project_id);
854
855 -- Bug 5750624: Cursor to check the project is allowed to deleted from PJR assignments
856 -- We should not allow to delete project with Confirmed Assignment or if it had any Confirmed assignments in the past which is not Confirmed now.
857 CURSOR c_is_pjr_delete_allowed(l_project_id IN NUMBER) IS
858 SELECT 'N'
859 FROM pa_project_assignments ppa, pa_project_statuses pps
860 WHERE ppa.project_id = l_project_id
861 AND ppa.assignment_type = 'STAFFED_ASSIGNMENT'
862 AND pps.status_type = 'STAFFED_ASGMT'
863 AND ppa.status_code = pps.project_status_code
864 AND pps.project_system_status_code = 'STAFFED_ASGMT_CONF'
865 UNION
866 SELECT 'N'
867 FROM pa_project_assignments ppa, pa_assignments_history pph, pa_project_statuses pps, pa_project_statuses pps1
868 WHERE ppa.project_id = l_project_id
869 AND ppa.assignment_id = pph.assignment_id
870 AND ppa.assignment_type = 'STAFFED_ASSIGNMENT'
871 -- AND pph.assignment_type = 'STAFFED_ASSIGNMENT' -- Not required since a staffed assignment in present is checked with staffed assignment in the past
872 AND pps.status_type = 'STAFFED_ASGMT'
873 AND pph.status_code = pps.project_status_code
874 AND pps.project_system_status_code = 'STAFFED_ASGMT_CONF'
875 AND pps1.status_type = 'STAFFED_ASGMT'
876 AND ppa.status_code = pps1.project_status_code
877 AND pps1.project_system_status_code <> 'STAFFED_ASGMT_CANCEL';
878
879 begin
880 x_err_code := 0;
881 old_stack := x_err_stack;
882
883 x_err_stack := x_err_stack || '->check_delete_project_ok';
884
885 -- Check project id
886 if (x_project_id is null) then
887 x_err_code := 10;
888 x_err_stage := 'PA_NO_PROJ_ID';
889 return;
890 end if ;
891
892 open p1;
893 fetch p1 into temp;
894 if p1%notfound then
895 close p1; -- 5374313 removed null statement and added code to close cursor p1
896 else
897 close p1; -- 5374313 added code to close cursor p1
898 x_err_code := 250;
899 x_err_stage := 'PA_PROJ_BURDEN_SUM_DEST';
900 return;
901 end if;
902
903 -- Check if project has event
904 x_err_stage := 'check event for '|| x_project_id;
905 status_code :=
906 pa_proj_tsk_utils.check_event_exists(x_project_id, null);
907 if ( status_code = 1 ) then
908 x_err_code := 20;
909 x_err_stage := 'PA_PROJ_EVENT_EXIST';
910 return;
911 elsif ( status_code < 0 ) then
912 x_err_code := status_code;
913 return;
914 end if;
915
916 -- Check if project has expenditure item
917 x_err_stage := 'check expenditure item for '|| x_project_id;
918 status_code :=
919 pa_proj_tsk_utils.check_exp_item_exists(x_project_id, null);
920 if ( status_code = 1 ) then
921 x_err_code := 30;
922 x_err_stage := 'PA_PROJ_EXP_ITEM_EXIST';
923 return;
924 elsif ( status_code < 0 ) then
925 x_err_code := status_code;
926 return;
927 end if;
928
929 -- Check if project has purchase order distribution
930 x_err_stage := 'check purchase order for '|| x_project_id;
931 status_code :=
932 pa_proj_tsk_utils.check_po_dist_exists(x_project_id, null);
933 if ( status_code = 1 ) then
934 x_err_code := 40;
935 x_err_stage := 'PA_PROJ_PO_DIST_EXIST';
936 return;
937 elsif ( status_code < 0 ) then
938 x_err_code := status_code;
939 return;
940 end if;
941
942 -- Check if project has purchase order requisition
943 x_err_stage := 'check purchase order requisition for '|| x_project_id;
944 status_code :=
945 pa_proj_tsk_utils.check_po_req_dist_exists(x_project_id, null);
946 if ( status_code = 1 ) then
947 x_err_code := 50;
948 x_err_stage := 'PA_PROJ_PO_REQ_DIST_EXIST';
949 return;
950 elsif ( status_code < 0 ) then
951 x_err_code := status_code;
952 return;
953 end if;
954
955 -- Check if project has supplier invoices
956 x_err_stage := 'check supplier invoice for '|| x_project_id;
957 status_code :=
958 pa_proj_tsk_utils.check_ap_invoice_exists(x_project_id, null);
959 if ( status_code = 1 ) then
960 x_err_code := 60;
961 x_err_stage := 'PA_PROJ_AP_INV_EXIST';
962 return;
963 elsif ( status_code < 0 ) then
964 x_err_code := status_code;
965 return;
966 end if;
967
968 -- Check if project has supplier invoice distribution
969 x_err_stage := 'check supplier inv distribution for '|| x_project_id;
970 status_code :=
971 pa_proj_tsk_utils.check_ap_inv_dist_exists(x_project_id, null);
972 if ( status_code = 1 ) then
973 x_err_code := 70;
974 x_err_stage := 'PA_PROJ_AP_INV_DIST_EXIST';
975 return;
976 elsif ( status_code < 0 ) then
977 x_err_code := status_code;
978 return;
979 end if;
980
981 -- Check if project has funding
982 x_err_stage := 'check funding for '|| x_project_id;
983 status_code :=
984 pa_proj_tsk_utils.check_funding_exists(x_project_id, null);
985 if ( status_code = 1 ) then
986 x_err_code := 80;
987 x_err_stage := 'PA_PROJ_FUND_EXIST';
988 return;
989 elsif ( status_code < 0 ) then
990 x_err_code := status_code;
991 return;
992 end if;
993
994 -- Check if project has any budget
995 x_err_stage := 'check budget for '|| x_project_id;
996 status_code :=
997 pa_budget_utils.check_proj_budget_exists(x_project_id, 'A',
998 dummy_null);
999 if ( status_code = 1 ) then
1000 x_err_code := 90;
1001 x_err_stage := 'PA_PROJ_BUDGET_EXIST';
1002 return;
1003 elsif ( status_code < 0 ) then
1004 x_err_code := status_code;
1005 return;
1006 end if;
1007
1008 -- Check if project has any FP options
1009 x_err_stage := 'check FP for '|| x_project_id;
1010 status_code :=
1011 PA_FIN_PLAN_UTILS.check_proj_fp_options_exists(p_project_id => x_project_id);
1012 if ( status_code = 1 ) then
1013 x_err_code := 95;
1014 x_err_stage := 'PA_PROJ_FP_OPTIONS_EXIST';
1015 return;
1016 elsif ( status_code < 0 ) then
1017 x_err_code := status_code;
1018 return;
1019 end if;
1020
1021
1022 -- Check if project has commitment transaction
1023 x_err_stage := 'check commitment transaction for '|| x_project_id;
1024 status_code :=
1025 pa_proj_tsk_utils.check_commitment_txn_exists(x_project_id, null);
1026 if ( status_code = 1 ) then
1027 x_err_code := 100;
1028 x_err_stage := 'PA_PROJ_CMT_TXN_EXIST';
1029 return;
1030 elsif ( status_code < 0 ) then
1031 x_err_code := status_code;
1032 return;
1033 end if;
1034
1035 -- Check if project has compensation rule set
1036 x_err_stage := 'check compensation rule set for '|| x_project_id;
1037 status_code :=
1038 pa_proj_tsk_utils.check_comp_rule_set_exists(x_project_id, null);
1039 if ( status_code = 1 ) then
1040 x_err_code := 110;
1041 x_err_stage := 'PA_PROJ_COMP_RULE_SET_EXIST';
1042 return;
1043 elsif ( status_code < 0 ) then
1044 x_err_code := status_code;
1045 return;
1046 end if;
1047
1048 -- Check if project id is referenced by other projects
1049 x_err_stage := 'check project reference for '|| x_project_id;
1050 status_code :=
1051 pa_project_utils.check_created_proj_reference(x_project_id);
1052 if ( status_code = 1 ) then
1053 x_err_code := 120;
1054 x_err_stage := 'PA_PROJ_CREATED_REF_EXIST';
1055 return;
1056 elsif ( status_code < 0 ) then
1057 x_err_code := status_code;
1058 return;
1059 end if;
1060
1061 -- Check if project is in use in an external system
1062 x_err_stage := 'check for project used in external system for'|| x_project_id;
1063 status_code :=
1064 pjm_projtask_deletion.CheckUse_ProjectTask(x_project_id, null);
1065 if ( status_code = 1 ) then
1066 x_err_code := 130;
1067 /* Commented the existing error message and modified it to 'PA_PROJ_TASK_IN_USE_MFG' as below for bug 3600806
1068 x_err_stage := 'PA_PROJ_IN_USE_EXTERNAL'; */
1069 x_err_stage := 'PA_PROJ_TASK_IN_USE_MFG';
1070 return;
1071 elsif ( status_code = 2 ) THEN -- Added elseif condition for bug 3600806.
1072 x_err_code := 130;
1073 x_err_stage := 'PA_PROJ_TASK_IN_USE_AUTO';
1074 return;
1075 elsif ( status_code < 0 ) then
1076 x_err_code := status_code;
1077 return;
1078 elsif ( status_code <> 0) then -- Added else condition for bug 3600806 to display a generic error message.
1079 x_err_code := 130;
1080 x_err_stage := 'PA_PROJ_TASK_IN_USE_EXTERNAL';
1081 return;
1082 end if;
1083
1084 -- Check task
1085 for task_rec in (select task_id
1086 from pa_tasks
1087 where project_id = x_project_id
1088 and task_id = top_task_id) loop
1089
1090 pa_task_utils.check_delete_task_ok(
1091 x_task_id => task_rec.task_id,
1092 x_validation_mode => x_validation_mode, --Bug 2947492
1093 x_err_code => x_err_code,
1094 x_err_stage => x_err_stage,
1095 x_err_stack => x_err_stack);
1096 if (x_err_code <> 0) then
1097 return;
1098 end if;
1099
1100 end loop;
1101
1102 -- Check if project is used in allocation rules
1103 x_err_stage := 'check if allocations use project '|| x_project_id;
1104 l_return_val :=
1105 pa_alloc_utils.Is_Project_In_Allocations(x_project_id);
1106 if ( l_return_val = 'Y' ) then
1107 x_err_code := 150;
1108 x_err_stage := 'PA_PROJ_IN_ALLOC';
1109 return;
1110 end if;
1111
1112 -- Check if project has cc organization relations
1113 x_err_stage := 'Check cc organization relations for '|| x_project_id;
1114 status_code :=
1115 pa_cc_utils.check_pvdr_rcvr_control_exist(x_project_id);
1116 if ( status_code = 1 ) then
1117 x_err_code := 160;
1118 x_err_stage := 'PA_PRJ_CC_ORG_REL_EXIST';
1119 return;
1120 elsif ( status_code < 0 ) then
1121 x_err_code := status_code;
1122 return;
1123 end if;
1124
1125 -- Check if project contract is installed.
1126 -- IF CONTRACT_IS_INSTALLED THEN
1127
1128 -- HSIU added.
1129 -- Check if contract is associated to the project
1130 IF ( pa_install.is_product_installed('OKE')) THEN
1131 x_err_stage := 'Check contract association for project '||x_project_id;
1132 IF (PA_PROJ_STRUCTURE_PUB.CHECK_SUBPROJ_CONTRACT_ASSO(x_project_id) <>
1133 FND_API.G_RET_STS_SUCCESS) THEN
1134 x_err_code := 170;
1135 x_err_stage := 'PA_STRUCT_PJ_HAS_CONTRACT';
1136 return;
1137 END IF;
1138 END IF;
1139 -- Finished checking if project contract is installed.
1140 --Ansari
1141 --Check to see if the project has been used in OTL--Added by Ansari
1142 PA_OTC_API.ProjectTaskUsed( p_search_attribute => 'PROJECT',
1143 p_search_value => x_project_id,
1144 x_used => x_used_in_OTL );
1145 --If exists in OTL
1146 IF x_used_in_OTL
1147 THEN
1148 x_err_code := 180;
1149 x_err_stage := 'PA_PROJ_EXP_ITEM_EXIST';
1150 return;
1151 END IF;
1152
1153 --end of OTL check.
1154 --Ansari
1155 --Check to see if the project is in use Org Forecasting
1156 IF PA_FP_ORG_FCST_UTILS.check_org_proj_template
1157 ( p_project_id => x_project_id
1158 ,x_return_status => l_return_status
1159 ,x_err_code => l_err_code ) = 'Y'
1160 THEN
1161 x_err_code := 190;
1162 x_err_stage := 'PA_FP_PROJ_REF_FCST_OPTIONS';
1163 return;
1164 END IF;
1165 --end of ORG-FORECASTING check.
1166
1167 --PA K Build 3
1168 x_err_stage := 'Check Control items exist for project '|| x_project_id;
1169 IF PA_CONTROL_ITEMS_UTILS.check_control_item_exists( p_project_id => x_project_id ) <> 0
1170 THEN
1171 x_err_code := 200;
1172 x_err_stage := 'PA_CI_PROJ_TASK_IN_USE';
1173 return;
1174 end if;
1175
1176 x_err_stage := 'Check Non Draft Control items exist for project '|| x_project_id;
1177 IF PA_CONTROL_ITEMS_UTILS.CheckNonDraftCI( p_project_id => x_project_id ) = 'Y'
1178 THEN
1179 x_err_code := 210;
1180 x_err_stage := 'PA_CI_ITEMS_EXIST';
1181 return;
1182 end if;
1183
1184 --PA K Build 3
1185
1186 -- Ram Namburi
1187 -- Bug Fix 4759187
1188
1189 -- Check to see if the project has been used in Sourcing i.e PON
1190
1191 PON_PROJECTS_INTEGRATION_GRP.CHECK_DELETE_PROJECT_OK(
1192 p_api_version => 1.0,
1193 p_init_msg_list => FND_API.G_TRUE,
1194 p_project_id => x_project_id,
1195 x_return_status => l_return_status,
1196 x_msg_count => l_msg_count,
1197 x_msg_data => l_msg_data );
1198
1199 --If the project is used in sourcing then the return status is an error.
1200 -- so check the return status and if it is not success then the message data string contains appropriate
1201 -- message as well.
1202 -- The message is also set in the message stack. All we need to do is return from here as we are doing
1203 -- for other earlier checks.
1204
1205 IF NVL(l_return_status , FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_SUCCESS THEN
1206 x_err_code := 300;
1207 x_err_stage := l_msg_data; -- 'PON_PROJECT_USED_NO_DELETE';
1208 return;
1209 END IF;
1210
1211 -- End of Bug Fix 4759187
1212
1213 -- Start Bug 5750591: Cursor to check whether the project_id passed is PJR Unassigned time project
1214 OPEN c_is_unassigned_time_proj(x_project_id);
1215 FETCH c_is_unassigned_time_proj INTO l_temp_char;
1216 IF c_is_unassigned_time_proj%NOTFOUND THEN
1217 CLOSE c_is_unassigned_time_proj;
1218 ELSE
1219 CLOSE c_is_unassigned_time_proj;
1220 x_err_code := 310;
1221 x_err_stage := 'PA_PROJ_IS_UNASSIGNED_TIME';
1222 RETURN;
1223 END IF;
1224 -- End Bug 5750591: Cursor to check whether the project_id passed is PJR Unassigned time project
1225
1226 -- Start Bug 5750624: Cursor to check the project is allowed to deleted from PJR assignments
1227 -- We should not allow to delete project with Confirmed Assignment or any assignment which had been in confirmed state previously
1228 OPEN c_is_pjr_delete_allowed(x_project_id);
1229 FETCH c_is_pjr_delete_allowed INTO l_temp_char;
1230 IF c_is_pjr_delete_allowed%NOTFOUND THEN
1231 CLOSE c_is_pjr_delete_allowed;
1232 ELSE
1233 CLOSE c_is_pjr_delete_allowed;
1234 x_err_code := 320;
1235 x_err_stage := 'PA_PJR_CONFIRMED_ASSIGNMENT';
1236 RETURN;
1237 END IF;
1238 -- End Bug 5750624: Cursor to check the project is allowed to deleted from PJR assignments
1239 /* Start changes for Service Intgration bug#16535441*/
1240 x_err_stage := 'check expenditure item for '|| x_project_id;
1241 status_code :=
1242 pa_proj_tsk_utils.check_service_order_exists(x_project_id, null);
1243 if ( status_code = 1 ) then
1244 x_err_code := 330;
1245 x_err_stage := 'PA_PROJ_SER_ORD_EXIST';
1246 return;
1247 elsif ( status_code < 0 ) then
1248 x_err_code := status_code;
1249 return;
1250 end if;
1251 /* End changes for Service Intgration bug#16535441*/
1252 x_err_stack := old_stack;
1253
1254 exception
1255 when others then
1256 x_err_code := SQLCODE;
1257 rollback;
1258 return;
1259 end check_delete_project_ok;
1260
1261 --
1262 -- PROCEDURE
1263 -- change_pt_org_ok
1264 -- PURPOSE
1265 -- This procedure checks if a project has CDLs,Rev or
1266 -- Draft invoices.If project has any of
1267 -- these information, then it's not ok to change the project
1268 -- type or org and specific reason will be returned.
1269 -- If it's ok to change project type or org,
1270 -- the x_err_code will be 0.
1271 --
1272 -- HISTORY
1273 -- 13-JAN-96 R.Krishnamurthy Created
1274 --
1275 procedure change_pt_org_ok ( x_project_id IN number
1276 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1277 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1278 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1279 is
1280 old_stack varchar2(630);
1281 status_code number;
1282 begin
1283 x_err_code := 0;
1284 old_stack := x_err_stack;
1285
1286 x_err_stack := x_err_stack || '->change_pt_org_ok';
1287
1288 -- Check project id
1289 if (x_project_id is null) then
1290 x_err_code := 10;
1291 x_err_stage := 'PA_NO_PROJ_ID';
1292 return;
1293 end if ;
1294 -- Check for cdls for the project
1295 x_err_stage := 'check cdls for project '|| x_project_id;
1296 status_code := pa_proj_tsk_utils.check_cdl_exists
1297 (x_project_id,Null);
1298 if status_code <> 0 Then
1299 x_err_code := 20;
1300 x_err_stage := 'PA_PR_CANT_CHG_PROJ_TYPE';
1301 return;
1302 end if;
1303
1304 -- Check for draft revenue items for the project
1305 x_err_stage := 'check draft rev for project '|| x_project_id;
1306 status_code := pa_proj_tsk_utils.check_draft_rev_item_exists
1307 (x_project_id,Null);
1308 if status_code <> 0 Then
1309 x_err_code := 30;
1310 x_err_stage := 'PA_PR_CANT_CHG_PROJ_TYPE';
1311 return;
1312 end if;
1313
1314 -- Check for draft inv items for the project
1315 x_err_stage := 'check draft inv for project '|| x_project_id;
1316 status_code := pa_proj_tsk_utils.check_draft_inv_item_exists
1317 (x_project_id,Null);
1318 if status_code <> 0 Then
1319 x_err_code := 40;
1320 x_err_stage := 'PA_PR_CANT_CHG_PROJ_TYPE';
1321 return;
1322 end if;
1323
1324 x_err_stack := old_stack;
1325
1326 exception
1327 when others then
1328 x_err_code := SQLCODE;
1329 rollback;
1330 return;
1331 end change_pt_org_ok;
1332
1333 --
1334 -- PROCEDURE
1335 -- change_proj_num_ok
1336 -- PURPOSE
1337 -- This procedure checks if a project has exp items,po reqs,
1338 -- Draft invoices,po dists,ap invoices and ap inv dists .
1339 -- If project has any of
1340 -- these information, then it's not ok to change the project
1341 -- number If it's ok to change project number
1342 -- the x_err_code will be 0.
1343 --
1344 -- HISTORY
1345 -- 15-JAN-96 R.Krishnamurthy Created
1346 --
1347
1348 procedure change_proj_num_ok ( x_project_id IN number
1349 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1350 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1351 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1352 is
1353
1354 old_stack varchar2(630);
1355 status_code number;
1356 begin
1357 x_err_code := 0;
1358 old_stack := x_err_stack;
1359
1360 x_err_stack := x_err_stack || '->change_proj_num_ok';
1361
1362 -- Check project id
1363 if (x_project_id is null) then
1364 x_err_code := 10;
1365 x_err_stage := 'PA_NO_PROJ_ID';
1366 return;
1367 end if ;
1368
1369 -- Check for exp items for the project
1370 x_err_stage := 'check exp items for project '|| x_project_id;
1371 status_code := pa_proj_tsk_utils.check_exp_item_exists
1372 (x_project_id,Null);
1373 if status_code <> 0 Then
1374 x_err_code := 20;
1375 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_EXP';
1376 return;
1377 end if;
1378
1379 -- Check for invoices for the project
1380 x_err_stage := 'check invoices for project '|| x_project_id;
1381 status_code := pa_proj_tsk_utils.check_draft_inv_item_exists
1382 (x_project_id,Null);
1383 if status_code <> 0 Then
1384 x_err_code := 30;
1385 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_INV';
1386 return;
1387 end if;
1388
1389 -- Check for po reqs for the project
1390 x_err_stage := 'check po reqs for project '|| x_project_id;
1391 status_code := pa_proj_tsk_utils.check_po_req_dist_exists
1392 (x_project_id,Null);
1393 if status_code <> 0 Then
1394 x_err_code := 40;
1395 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_EXP';
1396 return;
1397 end if;
1398
1399 -- Check for po dist for the project
1400 x_err_stage := 'check po dist for project '|| x_project_id;
1401 status_code := pa_proj_tsk_utils.check_po_dist_exists
1402 (x_project_id,Null);
1403 if status_code <> 0 Then
1404 x_err_code := 50;
1405 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_EXP';
1406 return;
1407 end if;
1408
1409 -- Check for ap inv for the project
1410 x_err_stage := 'check ap inv for project '|| x_project_id;
1411 status_code := pa_proj_tsk_utils.check_ap_invoice_exists
1412 (x_project_id,Null);
1413 if status_code <> 0 Then
1414 x_err_code := 60;
1415 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_INV';
1416 return;
1417 end if;
1418
1419 -- Check for ap inv dist for the project
1420 x_err_stage := 'check ap inv dist for project '|| x_project_id;
1421 status_code := pa_proj_tsk_utils.check_ap_inv_dist_exists
1422 (x_project_id,Null);
1423 if status_code <> 0 Then
1424 x_err_code := 70;
1425 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_INV';
1426 return;
1427 end if;
1428
1429 x_err_stack := old_stack;
1430
1431 exception
1432 when others then
1433 x_err_code := SQLCODE;
1434 rollback;
1435 return;
1436
1437 end change_proj_num_ok;
1438
1439
1440 -- FUNCTION
1441 -- check_option_child_exists
1442 -- PURPOSE
1443 -- This function returns Y if child for the option exists
1444 -- and N otherwise
1445 --
1446 -- HISTORY
1447 -- 13-DEC-96 D.Roy Created
1448
1449 Function check_option_child_exists
1450 (p_option_code varchar2)
1451 return varchar2 is
1452 rv varchar2(1) ;
1453 temp number;
1454 begin
1455 begin
1456 select 1
1457 into temp
1458 from sys.dual where
1459 exists ( select 1 from pa_options where parent_option_code = p_option_code);
1460 rv := 'Y';
1461 exception
1462 when NO_DATA_FOUND then
1463 rv := 'N';
1464 end;
1465 return rv;
1466 end check_option_child_exists;
1467
1468 -- FUNCTION
1469 -- check_proj_funding
1470 -- PURPOSE
1471 -- This function returns 1 if funding exists for a project
1472 -- with allocated amount > 0.Returns 0 if allocated amount <- 0
1473 -- or there are no fundings for that project. If fundings
1474 -- exist and allocated amount > 0 then , function returns 1.
1475 -- If Oracle error occured, Oracle error code is returned.
1476 --
1477 -- HISTORY
1478 -- 16-JAN-96 R. Krishnamurthy Created
1479 --
1480 function check_proj_funding (x_project_id IN number ) return number
1481 is
1482
1483 cursor c1 is
1484 SELECT NVL(SUM(NVL(allocated_amount,0)),0) allocated_amount
1485 FROM pa_project_fundings
1486 WHERE project_id = x_project_id;
1487 c1_rec c1%rowtype;
1488 begin
1489 if (x_project_id is null ) then
1490 return(null);
1491 end if;
1492 open c1;
1493 fetch c1 into c1_rec;
1494 If c1%notfound or
1495 c1_rec.allocated_amount <= 0 then
1496 close c1;
1497 return(0);
1498 end if;
1499 if c1_rec.allocated_amount > 0 then
1500 close c1;
1501 return(1);
1502 end if;
1503 exception
1504 when others then
1505 return(SQLCODE);
1506
1507 end check_proj_funding;
1508
1509 -- PROCEDURE
1510 -- check_dist_rule_chg_ok
1511 -- PURPOSE
1512 -- This procedure checks whether it is ok
1513 -- to change the Distribution rule
1514 -- If it's ok to change Distribution rule
1515 -- the x_err_code will be 0.
1516 --
1517 -- HISTORY
1518 -- 17-APR-96 R.Krishnamurthy Created
1519 -- Right now the procedure does nothing. The rules shall be added later.
1520 -- 02-DEC-97 C.Hung Added validation
1521
1522
1523 procedure check_dist_rule_chg_ok ( x_project_id IN number
1524 , x_old_dist_rule IN varchar2
1525 , x_new_dist_rule IN varchar2
1526 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1527 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1528 , x_err_stack IN OUT NOCOPY varchar2) Is --File.Sql.39 bug 4440895
1529 old_stack varchar2(630);
1530 status_code number;
1531 dummy varchar2(1);
1532 Begin
1533 x_err_code := 0;
1534 old_stack := x_err_stack;
1535
1536 x_err_stack := x_err_stack || '->check_dist_rule_chg_ok';
1537
1538 -- Check project id
1539 if (x_project_id is null) then
1540 x_err_code := 10;
1541 x_err_stage := 'PA_NO_PROJ_ID';
1542 return;
1543 end if ;
1544
1545 -- Check if x_old_dist_rule and x_new_dist_rule are the same
1546 if x_old_dist_rule <> x_new_dist_rule then
1547
1548 -- Check if exp item exists
1549 x_err_stage := 'check expenditure items for project '|| x_project_id;
1550
1551 begin
1552 select null
1553 into dummy
1554 from sys.dual
1555 where not exists (
1556 select null
1557 from pa_expenditure_items_all pai
1558 /* Bug#3461661 : removed join with pa_tasks
1559 * ,pa_tasks t
1560 */
1561 ,pa_cost_distribution_lines_all pcd
1562 where
1563 /* Bug#3461661 : removed join condition
1564 * pai.task_id = t.task_id
1565 * and
1566 */
1567 pai.expenditure_item_id = pcd.expenditure_item_id
1568 and pai.project_id = x_project_id);
1569 exception
1570 when no_data_found then
1571 x_err_code := 20;
1572 /* Changed message name trailing under_score since that is the way
1573 it is stored in FND_NEW_MESSAGES - Bug# 1718782
1574 x_err_stage := 'PA_HAS_REV/INV';
1575 */
1576 x_err_stage := 'PA_HAS_REV/INV_';
1577 return;
1578 when others then
1579 x_err_code := SQLCODE;
1580 return;
1581 end;
1582
1583 -- Check if draft revenue exists
1584 x_err_stage := 'check draft revenue for project '|| x_project_id;
1585
1586 begin
1587 select null
1588 into dummy
1589 from sys.dual
1590 where not exists (
1591 select null
1592 from pa_draft_revenues_all
1593 where project_id = x_project_id);
1594 exception
1595 when no_data_found then
1596 x_err_code := 30;
1597 /* Changed message name trailing under_score since that is the way
1598 it is stored in FND_NEW_MESSAGES - Bug# 1718782
1599 x_err_stage := 'PA_HAS_REV/INV';
1600 */
1601 x_err_stage := 'PA_HAS_REV/INV_';
1602 return;
1603 when others then
1604 x_err_code := SQLCODE;
1605 return;
1606 end;
1607
1608 -- Check if draft invoice exists
1609 x_err_stage := 'check draft invoices for project '|| x_project_id;
1610
1611 begin
1612 select null
1613 into dummy
1614 from sys.dual
1615 where not exists (
1616 select null
1617 from pa_draft_invoices_all
1618 where project_id = x_project_id);
1619 exception
1620 when no_data_found then
1621 x_err_code := 40;
1622 /* Changed message name trailing under_score since that is the way
1623 it is stored in FND_NEW_MESSAGES - Bug# 1718782
1624 x_err_stage := 'PA_HAS_REV/INV';
1625 */
1626 x_err_stage := 'PA_HAS_REV/INV_';
1627 return;
1628 when others then
1629 x_err_code := SQLCODE;
1630 return;
1631 end;
1632 end if;
1633
1634 x_err_stack := old_stack;
1635
1636 -- Included Exception Block for 4537865
1637 EXCEPTION
1638 WHEN OTHERS THEN
1639 x_err_code := SQLCODE;
1640 return; -- Used Return instead of a RAISE because in this API ,everywhere it is so.
1641 End check_dist_rule_chg_ok;
1642
1643 FUNCTION GetProjNumMode RETURN VARCHAR2 IS
1644 -- This function returns the implementation-defined Project number
1645 -- generation mode . The mode could either be 'AUTOMATIC' which implies
1646 -- automatic numbering by PA or 'MANUAL' which implies that project
1647 -- number is to be assigned by users
1648
1649 -- If using server side PL/SQL or a client side PL/SQL through Oracle forms,
1650 -- the best way to make use of this function is to define a package variable
1651 -- in your package specification and assign the default value.
1652 -- For example in the package specification
1653 -- G_Proj_number_Gen_Mode Varchar2(30) := PA_PROJECT_UTILS.GetProjNumMode;
1654
1655 l_proj_number_mode VARCHAR2(30);
1656 CURSOR l_get_proj_number_csr IS
1657 SELECT user_defined_project_num_code
1658 FROM pa_implementations;
1659
1660 BEGIN
1661 OPEN l_get_proj_number_csr;
1662 FETCH l_get_proj_number_csr INTO l_proj_number_mode;
1663 CLOSE l_get_proj_number_csr;
1664 RETURN l_proj_number_mode;
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 RETURN NULL;
1668 END GetProjNumMode;
1669
1670 FUNCTION GetProjNumType RETURN VARCHAR2 IS
1671 -- This function returns the implementation-defined Project number
1672 -- type. The mode could either be 'NUMERIC' or 'ALPHANUMERIC'
1673
1674 -- If using server side PL/SQL or a client side PL/SQL through Oracle forms,
1675 -- the best way to make use of this function is to define a package variable
1676 -- in your package specification and assign the default value.
1677 -- For example in the package specification
1678 -- G_Proj_number_Gen_Type Varchar2(30) := PA_PROJECT_UTILS.GetProjNumType;
1679
1680 l_proj_number_type VARCHAR2(30);
1681 CURSOR l_get_proj_type_csr IS
1682 SELECT manual_project_num_type
1683 FROM pa_implementations;
1684
1685 BEGIN
1686 OPEN l_get_proj_type_csr;
1687 FETCH l_get_proj_type_csr INTO l_proj_number_type;
1688 CLOSE l_get_proj_type_csr;
1689 RETURN l_proj_number_type;
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 RETURN NULL;
1693 END GetProjNumType;
1694
1695 FUNCTION Check_project_action_allowed
1696 (x_project_id IN NUMBER,
1697 x_action_code IN VARCHAR2 ) return VARCHAR2
1698 IS
1699 --
1700 -- Bug 940541
1701 -- modified the cusror to select from pa_projects_all
1702 -- since project_id is unique across operating units, it should
1703 -- be fine to select from pa_projects_all table.
1704
1705 CURSOR l_project_csr IS
1706 SELECT project_status_code
1707 FROM pa_projects_all pap
1708 WHERE pap.project_id = x_project_id;
1709 l_project_status_code VARCHAR2(30);
1710 l_action_allowed VARCHAR2(1) := 'N';
1711
1712 BEGIN
1713 OPEN l_project_csr;
1714 FETCH l_project_csr INTO l_project_status_code;
1715 IF l_project_csr%NOTFOUND THEN
1716 CLOSE l_project_csr;
1717 RETURN 'N';
1718 END IF;
1719 l_action_allowed :=
1720 Check_prj_stus_action_allowed (x_project_status_code =>
1721 l_project_status_code,
1722 x_action_code => x_action_code );
1723 RETURN (NVL(l_action_allowed,'N'));
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 RETURN 'N';
1727
1728 END Check_project_action_allowed;
1729
1730 FUNCTION Check_prj_stus_action_allowed
1731 (x_project_status_code IN VARCHAR2,
1732 x_action_code IN VARCHAR2 ) return VARCHAR2
1733 IS
1734 /*Added project_system_status_code for bug 2125791*/
1735 CURSOR l_prjstus_csr IS
1736 SELECT enabled_flag ,project_system_status_code
1737 FROM pa_project_status_controls
1738 WHERE project_status_code = x_project_status_code
1739 AND action_code = x_action_code;
1740 l_action_allowed VARCHAR2(1) := 'N';
1741 x_proj_sys_status_code VARCHAR2(30);/*Added for bug 2125791*/
1742 BEGIN
1743
1744 /* Added for bug 2125791*/
1745 For curr_rec in 1..glob_total_rec
1746 LOOP
1747 IF glob_project_status_code(curr_rec) = x_project_status_code
1748 AND glob_action_code(curr_rec) = x_action_code THEN
1749 return(nvl(glob_enabled_flag(curr_rec),'N'));
1750 END IF;
1751 END LOOP;
1752 /* End for bug 2125791*/
1753
1754 OPEN l_prjstus_csr;
1755 FETCH l_prjstus_csr INTO l_action_allowed,x_proj_sys_status_code;
1756 IF l_prjstus_csr%NOTFOUND THEN
1757 CLOSE l_prjstus_csr;
1758 RETURN 'N';
1759 END IF;
1760 CLOSE l_prjstus_csr;
1761
1762 /*Added for bug 2125791*/
1763 if glob_total_rec >10 then
1764 glob_total_rec := 0;
1765 glob_project_status_code := null_pointer;
1766 glob_action_code := null_pointer;
1767 glob_proj_sys_status_code := null_pointer;
1768 glob_enabled_flag := null_pointer1;
1769 end if;
1770
1771 glob_total_rec := glob_total_rec +1;
1772 glob_project_status_code(glob_total_rec) := x_project_status_code;
1773 glob_action_code(glob_total_rec) := x_action_code;
1774 glob_proj_sys_status_code(glob_total_rec) := x_proj_sys_status_code;
1775 glob_enabled_flag(glob_total_rec) := l_action_allowed;
1776 /*End for bug 2125791*/
1777
1778 RETURN (NVL(l_action_allowed,'N'));
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781 RETURN 'N';
1782 END Check_prj_stus_action_allowed;
1783 /* Bug 1512933 : Assignment Approval Changes */
1784
1785 FUNCTION Check_sys_action_allowed
1786 (x_project_system_status_code IN VARCHAR2,
1787 x_action_code IN VARCHAR2 ) return VARCHAR2
1788 IS
1789 /*Added project_status_code for bug 2125791*/
1790 CURSOR l_prjstus_csr IS
1791 SELECT enabled_flag,project_status_code
1792 FROM pa_project_status_controls
1793 WHERE project_system_status_code = x_project_system_status_code
1794 AND action_code = x_action_code;
1795 l_action_allowed VARCHAR2(1) := 'N';
1796 x_project_status_code VARCHAR2(30);/* Added for bug 2125791*/
1797 BEGIN
1798 /*Added for bug 2125791 */
1799 For curr_rec in 1..glob_total_rec
1800 LOOP
1801 IF glob_proj_sys_status_code(curr_rec) = x_project_system_status_code
1802 AND glob_action_code(curr_rec) = x_action_code THEN
1803 return(nvl(glob_enabled_flag(curr_rec),'N'));
1804 END IF;
1805 END LOOP;
1806 /*End for bug 2125791 */
1807
1808 OPEN l_prjstus_csr;
1809 FETCH l_prjstus_csr INTO l_action_allowed,x_project_status_code;
1810 IF l_prjstus_csr%NOTFOUND THEN
1811 RETURN 'Y';
1812 END IF;
1813 CLOSE l_prjstus_csr;
1814
1815 /*Added for bug 2125791*/
1816 if glob_total_rec >10 then
1817 glob_total_rec := 0;
1818 glob_project_status_code := null_pointer;
1819 glob_action_code := null_pointer;
1820 glob_proj_sys_status_code := null_pointer;
1821 glob_enabled_flag := null_pointer1;
1822 end if;
1823
1824 glob_total_rec := glob_total_rec +1;
1825 glob_project_status_code(glob_total_rec) := x_project_status_code;
1826 glob_action_code(glob_total_rec) := x_action_code;
1827 glob_proj_sys_status_code(glob_total_rec):= x_project_system_status_code;
1828 glob_enabled_flag(glob_total_rec) := l_action_allowed;
1829 /*End for bug 2125791*/
1830
1831 RETURN (NVL(l_action_allowed,'N'));
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834 RETURN 'N';
1835 END Check_sys_action_allowed;
1836
1837 FUNCTION is_tp_schd_proj_task(p_tp_schedule_id in number)
1838 return varchar2
1839 IS
1840 CURSOR C_schedule_in_use IS
1841 SELECT '1'
1842 FROM dual
1843 WHERE EXISTS (SELECT 'Y'
1844 FROM PA_PROJECTS_ALL PP
1845 WHERE PP.labor_tp_schedule_id=p_tp_schedule_id
1846 OR PP.nl_tp_schedule_id=p_tp_schedule_id
1847 )
1848 OR EXISTS
1849 (SELECT 'Y'
1850 FROM PA_TASKS PT
1851 WHERE PT.labor_tp_schedule_id=p_tp_schedule_id
1852 OR PT.nl_tp_schedule_id=p_tp_schedule_id
1853 );
1854 v_ret_code varchar2(1) ;
1855 v_dummy varchar2(1);
1856 BEGIN
1857 v_ret_code := 'N';
1858 OPEN C_schedule_in_use ;
1859 FETCH C_schedule_in_use INTO v_dummy;
1860 IF C_schedule_in_use%FOUND THEN
1861 v_ret_code := 'Y' ;
1862 END IF;
1863 CLOSE C_schedule_in_use;
1864 RETURN v_ret_code;
1865 EXCEPTION
1866 WHEN NO_DATA_FOUND THEN
1867 v_ret_code := 'N' ;
1868 Return v_ret_code ;
1869 WHEN OTHERS THEN
1870 RAISE;
1871 END is_tp_schd_proj_task ;
1872
1873
1874 -- FUNCTION
1875 -- Is_Admin_Project
1876 -- PURPOSE
1877 -- This function checks if a given project_id is
1878 -- an Admin Project. If it is an Admin project
1879 -- then the function returns 'Y'. If not, then the
1880 -- function returns 'N'.
1881 --
1882 -- HISTORY
1883 -- 21-NOV-00 A.Layton Created
1884 --
1885 FUNCTION Is_Admin_Project (p_project_id IN pa_projects_all.project_id%TYPE)
1886 RETURN VARCHAR2
1887 IS
1888
1889 l_admin_flag VARCHAR2(1);
1890
1891 BEGIN
1892
1893 SELECT administrative_flag INTO l_admin_flag
1894 FROM pa_project_types_all pt,
1895 pa_projects_all proj
1896 WHERE pt.project_type = proj.project_type
1897 --AND nvl(pt.org_id, -99) = nvl(proj.org_id, -99)
1898 AND pt.org_id = proj.org_id --avajain
1899 AND proj.project_id = p_project_id;
1900
1901 RETURN l_admin_flag;
1902
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 RAISE;
1906
1907 END Is_Admin_Project;
1908
1909 -- FUNCTION
1910 -- Is_Unassigned_Time_Project
1911 -- PURPOSE
1912 -- This function checks if a given project_id is
1913 -- an Unassigned Time project. If it is an Unassigned Time project
1914 -- then the function returns 'Y'. If not, then the
1915 -- function returns 'N'.
1916 --
1917 -- HISTORY
1918 -- 25-SEP-00 A.Layton Created
1919 --
1920 FUNCTION Is_Unassigned_Time_Project (p_project_id IN pa_projects_all.project_id%TYPE)
1921 RETURN VARCHAR2
1922 IS
1923
1924 l_unassigned_time_flag VARCHAR2(1) := 'N';
1925
1926 BEGIN
1927
1928 SELECT pt.unassigned_time INTO l_unassigned_time_flag
1929 FROM pa_project_types_all pt,
1930 pa_projects_all proj
1931 WHERE pt.project_type = proj.project_type
1932 --AND nvl(pt.org_id, -99) = nvl(proj.org_id, -99)
1933 AND pt.org_id = proj.org_id
1934 AND proj.project_id = p_project_id;
1935
1936 RETURN l_unassigned_time_flag;
1937
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 RAISE;
1941
1942 END Is_Unassigned_Time_Project;
1943
1944 FUNCTION IsUserProjectManager(p_project_id IN NUMBER,
1945 p_user_id IN NUMBER) RETURN VARCHAR2 IS
1946 x_val varchar2(1) := 'N';
1947 BEGIN
1948
1949 select 'Y'
1950 into x_val
1951 from pa_project_parties_v
1952 where project_id = p_project_id
1953 and project_role_id = 1
1954 and user_id = p_user_id
1955 and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate)); ----- Project Manager
1956
1957 return x_val;
1958
1959 exception when others then
1960 return 'N';
1961
1962 END IsUserProjectManager;
1963
1964 -- PROCEDURE
1965 -- check_delete_project_type_ok
1966 -- PURPOSE
1967 -- This objective of this API is to check if it is OK to
1968 -- delete a proejct type
1969 --
1970 procedure check_delete_project_type_ok (
1971 p_project_type_id IN NUMBER
1972 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1973 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1974 )
1975 IS
1976 l_return_value VARCHAR2(1) := 'N';
1977 BEGIN
1978 x_return_status := FND_API.G_RET_STS_SUCCESS;
1979 IF NVL( PA_CONTROL_ITEMS_UTILS.check_project_type_in_use(
1980 p_project_type_id => p_project_type_id ), 0 ) <> 0
1981 THEN
1982 x_return_status := FND_API.G_RET_STS_ERROR;
1983 x_error_message_code :='PA_CI_PROJ_TYPE_IN_USE';
1984 END IF;
1985
1986 -- 4537865
1987 EXCEPTION
1988 WHEN OTHERS THEN
1989 x_error_message_code := SQLERRM ;
1990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1991 RAISE;
1992 end check_delete_project_type_ok;
1993
1994 /*Start: Addition of code for bug 2682806 */
1995
1996 Procedure check_delete_class_catg_ok (
1997 p_class_category IN VARCHAR2
1998 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1999 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2000 )
2001 IS
2002
2003 BEGIN
2004 x_return_status :='S';
2005 IF NVL(Pa_Control_items_utils.Check_Class_Category_In_Use
2006 (p_class_category=>p_class_category),0)=1
2007 THEN
2008 x_return_status := 'F' ;
2009 x_error_message_code := 'PA_CI_CLASS_CAT_IN_USE';
2010 END IF;
2011
2012 -- 4537865
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 x_error_message_code := SQLERRM ;
2016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2017 RAISE;
2018 end check_delete_class_catg_ok;
2019
2020 Procedure check_delete_class_code_ok (
2021 p_class_category IN VARCHAR2
2022 ,p_class_code IN VARCHAR2
2023 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2024 ,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2025 IS
2026
2027 BEGIN
2028 x_return_status :='S';
2029 IF NVL(Pa_Control_items_utils.Check_Class_Code_In_Use
2030 (p_class_category=>p_class_category,
2031 P_class_code => p_class_code ),0)=1
2032 THEN
2033 x_return_status := 'F' ;
2034 x_error_message_code := 'PA_CI_CLASS_CODE_IN_USE';
2035 END IF;
2036
2037 -- 4537865
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040 x_error_message_code := SQLERRM ;
2041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2042 RAISE;
2043 end check_delete_class_code_ok;
2044
2045
2046 /*End: Addition of code for bug 2682806 */
2047
2048 --
2049 -- FUNCTION
2050 -- check_unique_project_reference
2051 -- PURPOSE
2052 -- This function returns 1 if a project reference is not already
2053 -- used in PA system and returns 0 if name is used.
2054 -- If Oracle error occurs, Oracle error number is returned.
2055 -- HISTORY
2056 -- 16-JUL-2003 zahid.khan Created
2057 -- 11-Dec-2005 sunkalya Bug Fix:4870305. Added parameter p_prod_code.
2058 --
2059 function check_unique_project_reference (p_proj_ref IN varchar2,
2060 p_prod_code IN varchar2, -- added for bug 4870305
2061 p_rowid IN varchar2 ) return number
2062 is
2063 cursor c1 is
2064 select project_id
2065 from pa_projects_all
2066 where pm_project_reference = p_proj_ref
2067 and pm_product_code = p_prod_code -- added for bug 4870305
2068 AND (p_ROWID IS NULL OR p_ROWID <> pa_projects_all.ROWID);
2069
2070 c1_rec c1%rowtype;
2071
2072 begin
2073 if (p_proj_ref is null ) then
2074 return(null);
2075 end if;
2076
2077 open c1;
2078 fetch c1 into c1_rec;
2079 if c1%notfound then
2080 close c1;
2081 return(1);
2082 else
2083 close c1;
2084 return(0);
2085 end if;
2086
2087 exception
2088 when others then
2089 return(SQLCODE);
2090
2091 end check_unique_project_reference;
2092
2093 --bug#2984611
2094 --This fucntion checks if the change in project type to
2095 --inter company project type is allowed or not.If the
2096 --primary ct/ship to ct. and bill to. customer are not
2097 --same then change to IC project type is not allowed.
2098 --This function takes the project type also as input
2099 --and performs the validation only when project type
2100 --is a IC project type i.e cc_prvdr_flag is 'Y'
2101
2102 function check_ic_proj_type_allowed(p_project_id IN NUMBER
2103 ,p_cc_prvdr_flag IN VARCHAR2 )
2104 RETURN NUMBER
2105 IS
2106 CURSOR c1 is
2107 SELECT 'Y'
2108 FROM DUAL
2109 WHERE EXISTS (SELECT 'Y'
2110 FROM PA_PROJECT_CUSTOMERS
2111 WHERE project_id = p_project_id
2112 AND (customer_id <> bill_to_customer_id
2113 OR customer_id <> ship_to_customer_id )) ;
2114 c1_rec c1%rowtype ;
2115
2116 BEGIN
2117 if p_project_id is NULL then
2118 return(null);
2119 end if ;
2120
2121 if nvl(p_cc_prvdr_flag,'N') = 'Y' then
2122 open c1 ;
2123 fetch c1 into c1_rec ;
2124
2125 if c1%notfound then
2126 close c1 ;
2127 return(0);
2128 else
2129 close c1 ;
2130 return (1);
2131 end if ;
2132 else
2133 return(0) ;
2134 end if ;
2135
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 if c1%ISOPEN THEN
2139 close c1 ;
2140 end if ;
2141 return(sqlcode) ;
2142 END check_ic_proj_type_allowed ;
2143
2144 /* Function added for bug 3738892 */
2145 FUNCTION is_flex_enabled ( appl_id IN NUMBER, flex_name IN varchar2)
2146 RETURN NUMBER
2147 IS
2148 flex_setup boolean;
2149 BEGIN
2150 flex_setup := Fnd_Flex_Apis.is_descr_setup(appl_id,flex_name);
2151 IF (flex_setup) THEN
2152 RETURN 1;
2153 ELSE
2154 RETURN 0;
2155 end if;
2156
2157 exception
2158 when others then
2159 return(SQLCODE);
2160 end is_flex_enabled;
2161
2162 /*
2163 Bug 5647964 Added the generic API which can be used for DFF Validations.
2164 The Procedure validates if the user has passed the required values for the various segments if they are required
2165 */
2166
2167
2168 PROCEDURE VALIDATE_DFF
2169 ( p_application_id IN NUMBER,
2170 p_flexfield_name IN VARCHAR2,
2171 p_attribute_category IN VARCHAR2,
2172 p_calling_module IN VARCHAR2,
2173 p_attribute1 IN VARCHAR2,
2174 p_attribute2 IN VARCHAR2,
2175 p_attribute3 IN VARCHAR2,
2176 p_attribute4 IN VARCHAR2,
2177 p_attribute5 IN VARCHAR2,
2178 p_attribute6 IN VARCHAR2,
2179 p_attribute7 IN VARCHAR2,
2180 p_attribute8 IN VARCHAR2,
2181 p_attribute9 IN VARCHAR2,
2182 p_attribute10 IN VARCHAR2,
2183 p_attribute11 IN VARCHAR2,
2184 p_attribute12 IN VARCHAR2,
2185 p_attribute13 IN VARCHAR2,
2186 p_attribute14 IN VARCHAR2,
2187 p_attribute15 IN VARCHAR2,
2188 x_return_status OUT NOCOPY VARCHAR2,
2189 x_msg_count OUT NOCOPY NUMBER,
2190 x_msg_data OUT NOCOPY VARCHAR2)
2191 IS
2192
2193
2194 l_flex_required BOOLEAN;
2195 l_is_context_segment_required BOOLEAN;
2196 l_global_req_segs_info1 Fnd_Flex_Apis.dff_required_segments_info;
2197 l_context_req_segs_info1 Fnd_Flex_Apis.dff_required_segments_info;
2198 l_segment_name VARCHAR2(30);
2199
2200 TYPE application_names IS VARRAY(15) of VARCHAR2(240);
2201
2202 v_application_names application_names;
2203 l_index NUMBER;
2204 i NUMBER;
2205 x_error_code NUMBER := 0;
2206 l_count NUMBER;
2207 BEGIN
2208 v_application_names := application_names();
2209
2210 l_flex_required := Fnd_Flex_Apis.is_descr_required(p_application_id,p_flexfield_name);
2211 IF (l_flex_required) THEN
2212 Fnd_Flex_Apis.get_dff_global_req_segs_info(p_application_id => p_application_id,
2213 p_flexfield_name => p_flexfield_name,
2214 x_is_context_segment_required => l_is_context_segment_required,
2215 x_global_req_segs_info => l_global_req_segs_info1) ;
2216
2217 IF (l_is_context_segment_required) THEN
2218 IF p_attribute_category IS NULL THEN
2219 x_error_code :=100 ;
2220 l_segment_name := NULL;
2221 ELSE
2222 Fnd_Flex_Apis.get_dff_context_req_segs_info(p_application_id => p_application_id,
2223 p_flexfield_name => p_flexfield_name,
2224 p_context_code => p_attribute_category,
2225 x_context_req_segs_info => l_context_req_segs_info1);
2226 END IF;
2227 END IF;
2228 END IF;
2229 /* GET APPL_COLUMN_NAMES FOR x_global_req_segs_info1 AND x_global_req_segs_info2
2230 None of these columns should be null */
2231 l_count := l_global_req_segs_info1.required_segment_names.count + l_context_req_segs_info1.required_segment_names.count ;
2232 v_application_names.EXTEND(l_count);
2233 l_index :=v_application_names.FIRST ;
2234
2235 if l_global_req_segs_info1.required_segment_names.first is not null then
2236 for x in l_global_req_segs_info1.required_segment_names.first..l_global_req_segs_info1.required_segment_names.last loop
2237 select APPLICATION_COLUMN_NAME
2238 into
2239 v_application_names(l_index)
2240 from fnd_descr_flex_column_usages
2241 where END_USER_COLUMN_NAME = l_global_req_segs_info1.required_segment_names(x)
2242 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2243 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements' ;
2244 l_index := v_application_names.NEXT(l_index);
2245 End Loop;
2246 end if;
2247
2248 if l_context_req_segs_info1.required_segment_names.first is not null then
2249 for y in l_context_req_segs_info1.required_segment_names.first..l_context_req_segs_info1.required_segment_names.last loop
2250 select APPLICATION_COLUMN_NAME
2251 into
2252 v_application_names(l_index)
2253 from fnd_descr_flex_column_usages
2254 where END_USER_COLUMN_NAME = l_context_req_segs_info1.required_segment_names(y) --x_context_req_segs_info1(l_index)
2255 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2256 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_category ;
2257 l_index := v_application_names.NEXT(l_index);
2258 End Loop;
2259 end if;
2260
2261 if x_error_code <> 100 then
2262 i := v_application_names.FIRST ;
2263 WHILE i IS NOT NULL LOOP
2264
2265 if v_application_names(i) = 'ATTRIBUTE1' then
2266 if p_attribute1 is null then
2267 x_error_code := 100;
2268 l_segment_name := 'ATTRIBUTE1';
2269 end if;
2270 end if;
2271
2272 if v_application_names(i) = 'ATTRIBUTE2' then
2273 if p_attribute2 is null then
2274 x_error_code := 100;
2275 l_segment_name := 'ATTRIBUTE2';
2276 end if;
2277 end if;
2278
2279 if v_application_names(i) = 'ATTRIBUTE3' then
2280 if p_attribute3 is null then
2281 x_error_code := 100;
2282 l_segment_name := 'ATTRIBUTE3';
2283 end if;
2284 end if;
2285
2286 if v_application_names(i) = 'ATTRIBUTE4' then
2287 if p_attribute4 is null then
2288 x_error_code := 100;
2289 l_segment_name := 'ATTRIBUTE4';
2290 end if;
2291 end if;
2292
2293 if v_application_names(i) = 'ATTRIBUTE5' then
2294 if p_attribute5 is null then
2295 x_error_code := 100;
2296 l_segment_name := 'ATTRIBUTE5';
2297 end if;
2298 end if;
2299
2300 if v_application_names(i) = 'ATTRIBUTE6' then
2301 if p_attribute6 is null then
2302 x_error_code := 100;
2303 l_segment_name := 'ATTRIBUTE6';
2304 end if;
2305 end if;
2306
2307 if v_application_names(i) = 'ATTRIBUTE7' then
2308 if p_attribute7 is null then
2309 x_error_code := 100;
2310 l_segment_name := 'ATTRIBUTE7';
2311 end if;
2312 end if;
2313
2314 if v_application_names(i) = 'ATTRIBUTE8' then
2315 if p_attribute8 is null then
2316 x_error_code := 100;
2317 l_segment_name := 'ATTRIBUTE8';
2318 end if;
2319 end if;
2320
2321 if v_application_names(i) = 'ATTRIBUTE9' then
2322 if p_attribute9 is null then
2323 x_error_code := 100;
2324 l_segment_name := 'ATTRIBUTE9';
2325 end if; /*error here */
2326 end if;
2327
2328 if v_application_names(i) = 'ATTRIBUTE10' then
2329 if p_attribute10 is null then
2330 x_error_code := 100;
2331 l_segment_name := 'ATTRIBUTE10';
2332 end if;
2333 end if;
2334
2335 if v_application_names(i) = 'ATTRIBUTE11' then
2336 if p_attribute11 is null then
2337 x_error_code := 100;
2338 l_segment_name := 'ATTRIBUTE11';
2339 end if;
2340 end if;
2341
2342 if v_application_names(i) = 'ATTRIBUTE12' then
2343 if p_attribute12 is null then
2344 x_error_code := 100;
2345 l_segment_name := 'ATTRIBUTE12';
2346 end if;
2347 end if;
2348
2349 if v_application_names(i) = 'ATTRIBUTE13' then
2350 if p_attribute13 is null then
2351 x_error_code := 100;
2352 l_segment_name := 'ATTRIBUTE13';
2353 end if;
2354 end if;
2355
2356 if v_application_names(i) = 'ATTRIBUTE14' then
2357 if p_attribute14 is null then
2358 x_error_code := 100;
2359 l_segment_name := 'ATTRIBUTE14';
2360 end if;
2361 end if;
2362
2363 if v_application_names(i) = 'ATTRIBUTE15' then
2364 if p_attribute15 is null then
2365 x_error_code := 100;
2366 l_segment_name := 'ATTRIBUTE15';
2367 end if;
2368 end if;
2369
2370 i := v_application_names.NEXT(i);
2371 END LOOP;
2372 end if;
2373
2374
2375 IF x_error_code = 100 THEN
2376 if p_calling_module = 'ADD_CLASS_CATEGORIES' then
2377 l_segment_name := null; /* Added this code to throw the error message for self-service without reference to Application column name.*/
2378 end if;
2379 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2380 p_msg_name => 'FLEX-MISSING SEGMENT VALUE',
2381 p_token1 => 'SEGMENT',
2382 p_value1 => l_segment_name,
2383 p_token2 => 'FLEXFIELD',
2384 p_value2 => p_flexfield_name);
2385 -- x_msg_data := 'FLEX-MISSING SEGMENT VALUE';
2386 x_return_status := FND_API.G_RET_STS_ERROR ;
2387 END IF;
2388
2389 x_return_status := FND_API.G_RET_STS_SUCCESS;
2390
2391 EXCEPTION
2392 WHEN OTHERS THEN
2393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2394 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_UTILS',
2395 p_procedure_name => 'VALIDATE_DFF',
2396 p_error_text => SUBSTRB(SQLERRM,1,240));
2397 raise;
2398 END VALIDATE_DFF;
2399 --Added for bug 12345249
2400 FUNCTION get_person_id(p_user_id IN NUMBER)
2401 RETURN NUMBER
2402 IS
2403 l_person_id NUMBER;
2404 BEGIN
2405
2406 SELECT papf.person_id into l_person_id
2407 FROM per_all_people_f papf, fnd_user usr
2408 WHERE papf.person_id = usr.employee_id
2409 AND trunc(sysdate) between papf.effective_start_date
2410 AND nvl(papf.effective_end_date, sysdate+1)
2411 AND trunc(sysdate) between usr.start_date
2412 AND nvl(usr.end_date, sysdate+1)
2413 AND usr.user_id = p_user_id;
2414
2415 RETURN l_person_id;
2416 EXCEPTION
2417 WHEN OTHERS THEN
2418 RETURN -999;
2419 END get_person_id;
2420
2421 END PA_PROJECT_UTILS;
[Home] [Help]