1 package body PA_PROJECT_UTILS as
2 -- $Header: PAXPUTLB.pls 120.8 2007/02/06 10:19:48 dthakker 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
1240 x_err_stack := old_stack;
1241
1242 exception
1243 when others then
1244 x_err_code := SQLCODE;
1245 rollback;
1246 return;
1247 end check_delete_project_ok;
1248
1249 --
1250 -- PROCEDURE
1251 -- change_pt_org_ok
1252 -- PURPOSE
1253 -- This procedure checks if a project has CDLs,Rev or
1254 -- Draft invoices.If project has any of
1255 -- these information, then it's not ok to change the project
1256 -- type or org and specific reason will be returned.
1257 -- If it's ok to change project type or org,
1258 -- the x_err_code will be 0.
1259 --
1260 -- HISTORY
1261 -- 13-JAN-96 R.Krishnamurthy Created
1262 --
1263 procedure change_pt_org_ok ( x_project_id IN number
1264 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1265 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1266 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1267 is
1268 old_stack varchar2(630);
1269 status_code number;
1270 begin
1271 x_err_code := 0;
1272 old_stack := x_err_stack;
1273
1274 x_err_stack := x_err_stack || '->change_pt_org_ok';
1275
1276 -- Check project id
1277 if (x_project_id is null) then
1278 x_err_code := 10;
1279 x_err_stage := 'PA_NO_PROJ_ID';
1280 return;
1281 end if ;
1282 -- Check for cdls for the project
1283 x_err_stage := 'check cdls for project '|| x_project_id;
1284 status_code := pa_proj_tsk_utils.check_cdl_exists
1285 (x_project_id,Null);
1286 if status_code <> 0 Then
1287 x_err_code := 20;
1288 x_err_stage := 'PA_PR_CANT_CHG_PROJ_TYPE';
1289 return;
1290 end if;
1291
1292 -- Check for draft revenue items for the project
1293 x_err_stage := 'check draft rev for project '|| x_project_id;
1294 status_code := pa_proj_tsk_utils.check_draft_rev_item_exists
1295 (x_project_id,Null);
1296 if status_code <> 0 Then
1297 x_err_code := 30;
1298 x_err_stage := 'PA_PR_CANT_CHG_PROJ_TYPE';
1299 return;
1300 end if;
1301
1302 -- Check for draft inv items for the project
1303 x_err_stage := 'check draft inv for project '|| x_project_id;
1304 status_code := pa_proj_tsk_utils.check_draft_inv_item_exists
1305 (x_project_id,Null);
1306 if status_code <> 0 Then
1307 x_err_code := 40;
1308 x_err_stage := 'PA_PR_CANT_CHG_PROJ_TYPE';
1309 return;
1310 end if;
1311
1312 x_err_stack := old_stack;
1313
1314 exception
1315 when others then
1316 x_err_code := SQLCODE;
1317 rollback;
1318 return;
1319 end change_pt_org_ok;
1320
1321 --
1322 -- PROCEDURE
1323 -- change_proj_num_ok
1324 -- PURPOSE
1325 -- This procedure checks if a project has exp items,po reqs,
1326 -- Draft invoices,po dists,ap invoices and ap inv dists .
1327 -- If project has any of
1328 -- these information, then it's not ok to change the project
1329 -- number If it's ok to change project number
1330 -- the x_err_code will be 0.
1331 --
1332 -- HISTORY
1333 -- 15-JAN-96 R.Krishnamurthy Created
1334 --
1335
1336 procedure change_proj_num_ok ( x_project_id IN number
1337 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1338 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1339 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1340 is
1341
1342 old_stack varchar2(630);
1343 status_code number;
1344 begin
1345 x_err_code := 0;
1346 old_stack := x_err_stack;
1347
1348 x_err_stack := x_err_stack || '->change_proj_num_ok';
1349
1350 -- Check project id
1351 if (x_project_id is null) then
1352 x_err_code := 10;
1353 x_err_stage := 'PA_NO_PROJ_ID';
1354 return;
1355 end if ;
1356
1357 -- Check for exp items for the project
1358 x_err_stage := 'check exp items for project '|| x_project_id;
1359 status_code := pa_proj_tsk_utils.check_exp_item_exists
1360 (x_project_id,Null);
1361 if status_code <> 0 Then
1362 x_err_code := 20;
1363 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_EXP';
1364 return;
1365 end if;
1366
1367 -- Check for invoices for the project
1368 x_err_stage := 'check invoices for project '|| x_project_id;
1369 status_code := pa_proj_tsk_utils.check_draft_inv_item_exists
1370 (x_project_id,Null);
1371 if status_code <> 0 Then
1372 x_err_code := 30;
1373 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_INV';
1374 return;
1375 end if;
1376
1377 -- Check for po reqs for the project
1378 x_err_stage := 'check po reqs for project '|| x_project_id;
1379 status_code := pa_proj_tsk_utils.check_po_req_dist_exists
1380 (x_project_id,Null);
1381 if status_code <> 0 Then
1382 x_err_code := 40;
1383 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_EXP';
1384 return;
1385 end if;
1386
1387 -- Check for po dist for the project
1388 x_err_stage := 'check po dist for project '|| x_project_id;
1389 status_code := pa_proj_tsk_utils.check_po_dist_exists
1390 (x_project_id,Null);
1391 if status_code <> 0 Then
1392 x_err_code := 50;
1393 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_EXP';
1394 return;
1395 end if;
1396
1397 -- Check for ap inv for the project
1398 x_err_stage := 'check ap inv for project '|| x_project_id;
1399 status_code := pa_proj_tsk_utils.check_ap_invoice_exists
1400 (x_project_id,Null);
1401 if status_code <> 0 Then
1402 x_err_code := 60;
1403 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_INV';
1404 return;
1405 end if;
1406
1407 -- Check for ap inv dist for the project
1408 x_err_stage := 'check ap inv dist for project '|| x_project_id;
1409 status_code := pa_proj_tsk_utils.check_ap_inv_dist_exists
1410 (x_project_id,Null);
1411 if status_code <> 0 Then
1412 x_err_code := 70;
1413 x_err_stage := 'PA_PR_NO_UPD_SEGMENT1_INV';
1414 return;
1415 end if;
1416
1417 x_err_stack := old_stack;
1418
1419 exception
1420 when others then
1421 x_err_code := SQLCODE;
1422 rollback;
1423 return;
1424
1425 end change_proj_num_ok;
1426
1427
1428 -- FUNCTION
1429 -- check_option_child_exists
1430 -- PURPOSE
1431 -- This function returns Y if child for the option exists
1432 -- and N otherwise
1433 --
1434 -- HISTORY
1435 -- 13-DEC-96 D.Roy Created
1436
1437 Function check_option_child_exists
1438 (p_option_code varchar2)
1439 return varchar2 is
1440 rv varchar2(1) ;
1441 temp number;
1442 begin
1443 begin
1444 select 1
1445 into temp
1446 from sys.dual where
1447 exists ( select 1 from pa_options where parent_option_code = p_option_code);
1448 rv := 'Y';
1449 exception
1450 when NO_DATA_FOUND then
1451 rv := 'N';
1452 end;
1453 return rv;
1454 end check_option_child_exists;
1455
1456 -- FUNCTION
1457 -- check_proj_funding
1458 -- PURPOSE
1459 -- This function returns 1 if funding exists for a project
1460 -- with allocated amount > 0.Returns 0 if allocated amount <- 0
1461 -- or there are no fundings for that project. If fundings
1462 -- exist and allocated amount > 0 then , function returns 1.
1463 -- If Oracle error occured, Oracle error code is returned.
1464 --
1465 -- HISTORY
1466 -- 16-JAN-96 R. Krishnamurthy Created
1467 --
1468 function check_proj_funding (x_project_id IN number ) return number
1469 is
1470
1471 cursor c1 is
1472 SELECT NVL(SUM(NVL(allocated_amount,0)),0) allocated_amount
1473 FROM pa_project_fundings
1474 WHERE project_id = x_project_id;
1475 c1_rec c1%rowtype;
1476 begin
1477 if (x_project_id is null ) then
1478 return(null);
1479 end if;
1480 open c1;
1481 fetch c1 into c1_rec;
1482 If c1%notfound or
1483 c1_rec.allocated_amount <= 0 then
1484 close c1;
1485 return(0);
1486 end if;
1487 if c1_rec.allocated_amount > 0 then
1488 close c1;
1489 return(1);
1490 end if;
1491 exception
1492 when others then
1493 return(SQLCODE);
1494
1495 end check_proj_funding;
1496
1497 -- PROCEDURE
1498 -- check_dist_rule_chg_ok
1499 -- PURPOSE
1500 -- This procedure checks whether it is ok
1501 -- to change the Distribution rule
1502 -- If it's ok to change Distribution rule
1503 -- the x_err_code will be 0.
1504 --
1505 -- HISTORY
1506 -- 17-APR-96 R.Krishnamurthy Created
1507 -- Right now the procedure does nothing. The rules shall be added later.
1508 -- 02-DEC-97 C.Hung Added validation
1509
1510
1511 procedure check_dist_rule_chg_ok ( x_project_id IN number
1512 , x_old_dist_rule IN varchar2
1513 , x_new_dist_rule IN varchar2
1514 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1515 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1516 , x_err_stack IN OUT NOCOPY varchar2) Is --File.Sql.39 bug 4440895
1517 old_stack varchar2(630);
1518 status_code number;
1519 dummy varchar2(1);
1520 Begin
1521 x_err_code := 0;
1522 old_stack := x_err_stack;
1523
1524 x_err_stack := x_err_stack || '->check_dist_rule_chg_ok';
1525
1526 -- Check project id
1527 if (x_project_id is null) then
1528 x_err_code := 10;
1529 x_err_stage := 'PA_NO_PROJ_ID';
1530 return;
1531 end if ;
1532
1533 -- Check if x_old_dist_rule and x_new_dist_rule are the same
1534 if x_old_dist_rule <> x_new_dist_rule then
1535
1536 -- Check if exp item exists
1537 x_err_stage := 'check expenditure items for project '|| x_project_id;
1538
1539 begin
1540 select null
1541 into dummy
1542 from sys.dual
1543 where not exists (
1544 select null
1545 from pa_expenditure_items_all pai
1546 /* Bug#3461661 : removed join with pa_tasks
1547 * ,pa_tasks t
1548 */
1549 ,pa_cost_distribution_lines_all pcd
1550 where
1551 /* Bug#3461661 : removed join condition
1552 * pai.task_id = t.task_id
1553 * and
1554 */
1555 pai.expenditure_item_id = pcd.expenditure_item_id
1556 and pai.project_id = x_project_id);
1557 exception
1558 when no_data_found then
1559 x_err_code := 20;
1560 /* Changed message name trailing under_score since that is the way
1561 it is stored in FND_NEW_MESSAGES - Bug# 1718782
1562 x_err_stage := 'PA_HAS_REV/INV';
1563 */
1564 x_err_stage := 'PA_HAS_REV/INV_';
1565 return;
1566 when others then
1567 x_err_code := SQLCODE;
1568 return;
1569 end;
1570
1571 -- Check if draft revenue exists
1572 x_err_stage := 'check draft revenue for project '|| x_project_id;
1573
1574 begin
1575 select null
1576 into dummy
1577 from sys.dual
1578 where not exists (
1579 select null
1580 from pa_draft_revenues_all
1581 where project_id = x_project_id);
1582 exception
1583 when no_data_found then
1584 x_err_code := 30;
1585 /* Changed message name trailing under_score since that is the way
1586 it is stored in FND_NEW_MESSAGES - Bug# 1718782
1587 x_err_stage := 'PA_HAS_REV/INV';
1588 */
1589 x_err_stage := 'PA_HAS_REV/INV_';
1590 return;
1591 when others then
1592 x_err_code := SQLCODE;
1593 return;
1594 end;
1595
1596 -- Check if draft invoice exists
1597 x_err_stage := 'check draft invoices for project '|| x_project_id;
1598
1599 begin
1600 select null
1601 into dummy
1602 from sys.dual
1603 where not exists (
1604 select null
1605 from pa_draft_invoices_all
1606 where project_id = x_project_id);
1607 exception
1608 when no_data_found then
1609 x_err_code := 40;
1610 /* Changed message name trailing under_score since that is the way
1611 it is stored in FND_NEW_MESSAGES - Bug# 1718782
1612 x_err_stage := 'PA_HAS_REV/INV';
1613 */
1614 x_err_stage := 'PA_HAS_REV/INV_';
1615 return;
1616 when others then
1617 x_err_code := SQLCODE;
1618 return;
1619 end;
1620 end if;
1621
1622 x_err_stack := old_stack;
1623
1624 -- Included Exception Block for 4537865
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 x_err_code := SQLCODE;
1628 return; -- Used Return instead of a RAISE because in this API ,everywhere it is so.
1629 End check_dist_rule_chg_ok;
1630
1631 FUNCTION GetProjNumMode RETURN VARCHAR2 IS
1632 -- This function returns the implementation-defined Project number
1633 -- generation mode . The mode could either be 'AUTOMATIC' which implies
1634 -- automatic numbering by PA or 'MANUAL' which implies that project
1635 -- number is to be assigned by users
1636
1637 -- If using server side PL/SQL or a client side PL/SQL through Oracle forms,
1638 -- the best way to make use of this function is to define a package variable
1639 -- in your package specification and assign the default value.
1640 -- For example in the package specification
1641 -- G_Proj_number_Gen_Mode Varchar2(30) := PA_PROJECT_UTILS.GetProjNumMode;
1642
1643 l_proj_number_mode VARCHAR2(30);
1644 CURSOR l_get_proj_number_csr IS
1645 SELECT user_defined_project_num_code
1646 FROM pa_implementations;
1647
1648 BEGIN
1649 OPEN l_get_proj_number_csr;
1650 FETCH l_get_proj_number_csr INTO l_proj_number_mode;
1651 CLOSE l_get_proj_number_csr;
1652 RETURN l_proj_number_mode;
1653 EXCEPTION
1654 WHEN OTHERS THEN
1655 RETURN NULL;
1656 END GetProjNumMode;
1657
1658 FUNCTION GetProjNumType RETURN VARCHAR2 IS
1659 -- This function returns the implementation-defined Project number
1660 -- type. The mode could either be 'NUMERIC' or 'ALPHANUMERIC'
1661
1662 -- If using server side PL/SQL or a client side PL/SQL through Oracle forms,
1663 -- the best way to make use of this function is to define a package variable
1664 -- in your package specification and assign the default value.
1665 -- For example in the package specification
1666 -- G_Proj_number_Gen_Type Varchar2(30) := PA_PROJECT_UTILS.GetProjNumType;
1667
1668 l_proj_number_type VARCHAR2(30);
1669 CURSOR l_get_proj_type_csr IS
1670 SELECT manual_project_num_type
1671 FROM pa_implementations;
1672
1673 BEGIN
1674 OPEN l_get_proj_type_csr;
1675 FETCH l_get_proj_type_csr INTO l_proj_number_type;
1676 CLOSE l_get_proj_type_csr;
1677 RETURN l_proj_number_type;
1678 EXCEPTION
1679 WHEN OTHERS THEN
1680 RETURN NULL;
1681 END GetProjNumType;
1682
1683 FUNCTION Check_project_action_allowed
1684 (x_project_id IN NUMBER,
1685 x_action_code IN VARCHAR2 ) return VARCHAR2
1686 IS
1687 --
1688 -- Bug 940541
1689 -- modified the cusror to select from pa_projects_all
1690 -- since project_id is unique across operating units, it should
1691 -- be fine to select from pa_projects_all table.
1692
1693 CURSOR l_project_csr IS
1694 SELECT project_status_code
1695 FROM pa_projects_all pap
1696 WHERE pap.project_id = x_project_id;
1697 l_project_status_code VARCHAR2(30);
1698 l_action_allowed VARCHAR2(1) := 'N';
1699
1700 BEGIN
1701 OPEN l_project_csr;
1702 FETCH l_project_csr INTO l_project_status_code;
1703 IF l_project_csr%NOTFOUND THEN
1704 CLOSE l_project_csr;
1705 RETURN 'N';
1706 END IF;
1707 l_action_allowed :=
1708 Check_prj_stus_action_allowed (x_project_status_code =>
1709 l_project_status_code,
1710 x_action_code => x_action_code );
1711 RETURN (NVL(l_action_allowed,'N'));
1712 EXCEPTION
1713 WHEN OTHERS THEN
1714 RETURN 'N';
1715
1716 END Check_project_action_allowed;
1717
1718 FUNCTION Check_prj_stus_action_allowed
1719 (x_project_status_code IN VARCHAR2,
1720 x_action_code IN VARCHAR2 ) return VARCHAR2
1721 IS
1722 /*Added project_system_status_code for bug 2125791*/
1723 CURSOR l_prjstus_csr IS
1724 SELECT enabled_flag ,project_system_status_code
1725 FROM pa_project_status_controls
1726 WHERE project_status_code = x_project_status_code
1727 AND action_code = x_action_code;
1728 l_action_allowed VARCHAR2(1) := 'N';
1729 x_proj_sys_status_code VARCHAR2(30);/*Added for bug 2125791*/
1730 BEGIN
1731
1732 /* Added for bug 2125791*/
1733 For curr_rec in 1..glob_total_rec
1734 LOOP
1735 IF glob_project_status_code(curr_rec) = x_project_status_code
1736 AND glob_action_code(curr_rec) = x_action_code THEN
1737 return(nvl(glob_enabled_flag(curr_rec),'N'));
1738 END IF;
1739 END LOOP;
1740 /* End for bug 2125791*/
1741
1742 OPEN l_prjstus_csr;
1743 FETCH l_prjstus_csr INTO l_action_allowed,x_proj_sys_status_code;
1744 IF l_prjstus_csr%NOTFOUND THEN
1745 CLOSE l_prjstus_csr;
1746 RETURN 'N';
1747 END IF;
1748 CLOSE l_prjstus_csr;
1749
1750 /*Added for bug 2125791*/
1751 if glob_total_rec >10 then
1752 glob_total_rec := 0;
1753 glob_project_status_code := null_pointer;
1754 glob_action_code := null_pointer;
1755 glob_proj_sys_status_code := null_pointer;
1756 glob_enabled_flag := null_pointer1;
1757 end if;
1758
1759 glob_total_rec := glob_total_rec +1;
1760 glob_project_status_code(glob_total_rec) := x_project_status_code;
1761 glob_action_code(glob_total_rec) := x_action_code;
1762 glob_proj_sys_status_code(glob_total_rec) := x_proj_sys_status_code;
1763 glob_enabled_flag(glob_total_rec) := l_action_allowed;
1764 /*End for bug 2125791*/
1765
1766 RETURN (NVL(l_action_allowed,'N'));
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 RETURN 'N';
1770 END Check_prj_stus_action_allowed;
1771 /* Bug 1512933 : Assignment Approval Changes */
1772
1773 FUNCTION Check_sys_action_allowed
1774 (x_project_system_status_code IN VARCHAR2,
1775 x_action_code IN VARCHAR2 ) return VARCHAR2
1776 IS
1777 /*Added project_status_code for bug 2125791*/
1778 CURSOR l_prjstus_csr IS
1779 SELECT enabled_flag,project_status_code
1780 FROM pa_project_status_controls
1781 WHERE project_system_status_code = x_project_system_status_code
1782 AND action_code = x_action_code;
1783 l_action_allowed VARCHAR2(1) := 'N';
1784 x_project_status_code VARCHAR2(30);/* Added for bug 2125791*/
1785 BEGIN
1786 /*Added for bug 2125791 */
1787 For curr_rec in 1..glob_total_rec
1788 LOOP
1789 IF glob_proj_sys_status_code(curr_rec) = x_project_system_status_code
1790 AND glob_action_code(curr_rec) = x_action_code THEN
1791 return(nvl(glob_enabled_flag(curr_rec),'N'));
1792 END IF;
1793 END LOOP;
1794 /*End for bug 2125791 */
1795
1796 OPEN l_prjstus_csr;
1797 FETCH l_prjstus_csr INTO l_action_allowed,x_project_status_code;
1798 IF l_prjstus_csr%NOTFOUND THEN
1799 RETURN 'Y';
1800 END IF;
1801 CLOSE l_prjstus_csr;
1802
1803 /*Added for bug 2125791*/
1804 if glob_total_rec >10 then
1805 glob_total_rec := 0;
1806 glob_project_status_code := null_pointer;
1807 glob_action_code := null_pointer;
1808 glob_proj_sys_status_code := null_pointer;
1809 glob_enabled_flag := null_pointer1;
1810 end if;
1811
1812 glob_total_rec := glob_total_rec +1;
1813 glob_project_status_code(glob_total_rec) := x_project_status_code;
1814 glob_action_code(glob_total_rec) := x_action_code;
1815 glob_proj_sys_status_code(glob_total_rec):= x_project_system_status_code;
1816 glob_enabled_flag(glob_total_rec) := l_action_allowed;
1817 /*End for bug 2125791*/
1818
1819 RETURN (NVL(l_action_allowed,'N'));
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 RETURN 'N';
1823 END Check_sys_action_allowed;
1824
1825 FUNCTION is_tp_schd_proj_task(p_tp_schedule_id in number)
1826 return varchar2
1827 IS
1828 CURSOR C_schedule_in_use IS
1829 SELECT '1'
1830 FROM dual
1831 WHERE EXISTS (SELECT 'Y'
1832 FROM PA_PROJECTS_ALL PP
1833 WHERE PP.labor_tp_schedule_id=p_tp_schedule_id
1834 OR PP.nl_tp_schedule_id=p_tp_schedule_id
1835 )
1836 OR EXISTS
1837 (SELECT 'Y'
1838 FROM PA_TASKS PT
1839 WHERE PT.labor_tp_schedule_id=p_tp_schedule_id
1840 OR PT.nl_tp_schedule_id=p_tp_schedule_id
1841 );
1842 v_ret_code varchar2(1) ;
1843 v_dummy varchar2(1);
1844 BEGIN
1845 v_ret_code := 'N';
1846 OPEN C_schedule_in_use ;
1847 FETCH C_schedule_in_use INTO v_dummy;
1848 IF C_schedule_in_use%FOUND THEN
1849 v_ret_code := 'Y' ;
1850 END IF;
1851 CLOSE C_schedule_in_use;
1852 RETURN v_ret_code;
1853 EXCEPTION
1854 WHEN NO_DATA_FOUND THEN
1855 v_ret_code := 'N' ;
1856 Return v_ret_code ;
1857 WHEN OTHERS THEN
1858 RAISE;
1859 END is_tp_schd_proj_task ;
1860
1861
1862 -- FUNCTION
1863 -- Is_Admin_Project
1864 -- PURPOSE
1865 -- This function checks if a given project_id is
1866 -- an Admin Project. If it is an Admin project
1867 -- then the function returns 'Y'. If not, then the
1868 -- function returns 'N'.
1869 --
1870 -- HISTORY
1871 -- 21-NOV-00 A.Layton Created
1872 --
1873 FUNCTION Is_Admin_Project (p_project_id IN pa_projects_all.project_id%TYPE)
1874 RETURN VARCHAR2
1875 IS
1876
1877 l_admin_flag VARCHAR2(1);
1878
1879 BEGIN
1880
1881 SELECT administrative_flag INTO l_admin_flag
1882 FROM pa_project_types_all pt,
1883 pa_projects_all proj
1884 WHERE pt.project_type = proj.project_type
1885 --AND nvl(pt.org_id, -99) = nvl(proj.org_id, -99)
1886 AND pt.org_id = proj.org_id --avajain
1887 AND proj.project_id = p_project_id;
1888
1889 RETURN l_admin_flag;
1890
1891 EXCEPTION
1892 WHEN OTHERS THEN
1893 RAISE;
1894
1895 END Is_Admin_Project;
1896
1897 -- FUNCTION
1898 -- Is_Unassigned_Time_Project
1899 -- PURPOSE
1900 -- This function checks if a given project_id is
1901 -- an Unassigned Time project. If it is an Unassigned Time project
1902 -- then the function returns 'Y'. If not, then the
1903 -- function returns 'N'.
1904 --
1905 -- HISTORY
1906 -- 25-SEP-00 A.Layton Created
1907 --
1908 FUNCTION Is_Unassigned_Time_Project (p_project_id IN pa_projects_all.project_id%TYPE)
1909 RETURN VARCHAR2
1910 IS
1911
1912 l_unassigned_time_flag VARCHAR2(1) := 'N';
1913
1914 BEGIN
1915
1916 SELECT pt.unassigned_time INTO l_unassigned_time_flag
1917 FROM pa_project_types_all pt,
1918 pa_projects_all proj
1919 WHERE pt.project_type = proj.project_type
1920 --AND nvl(pt.org_id, -99) = nvl(proj.org_id, -99)
1921 AND pt.org_id = proj.org_id
1922 AND proj.project_id = p_project_id;
1923
1924 RETURN l_unassigned_time_flag;
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 RAISE;
1929
1930 END Is_Unassigned_Time_Project;
1931
1932 FUNCTION IsUserProjectManager(p_project_id IN NUMBER,
1933 p_user_id IN NUMBER) RETURN VARCHAR2 IS
1934 x_val varchar2(1) := 'N';
1935 BEGIN
1936
1937 select 'Y'
1938 into x_val
1939 from pa_project_parties_v
1940 where project_id = p_project_id
1941 and project_role_id = 1
1942 and user_id = p_user_id
1943 and trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate)); ----- Project Manager
1944
1945 return x_val;
1946
1947 exception when others then
1948 return 'N';
1949
1950 END IsUserProjectManager;
1951
1952 -- PROCEDURE
1953 -- check_delete_project_type_ok
1954 -- PURPOSE
1955 -- This objective of this API is to check if it is OK to
1956 -- delete a proejct type
1957 --
1958 procedure check_delete_project_type_ok (
1959 p_project_type_id IN NUMBER
1960 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1961 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1962 )
1963 IS
1964 l_return_value VARCHAR2(1) := 'N';
1965 BEGIN
1966 x_return_status := FND_API.G_RET_STS_SUCCESS;
1967 IF NVL( PA_CONTROL_ITEMS_UTILS.check_project_type_in_use(
1968 p_project_type_id => p_project_type_id ), 0 ) <> 0
1969 THEN
1970 x_return_status := FND_API.G_RET_STS_ERROR;
1971 x_error_message_code :='PA_CI_PROJ_TYPE_IN_USE';
1972 END IF;
1973
1974 -- 4537865
1975 EXCEPTION
1976 WHEN OTHERS THEN
1977 x_error_message_code := SQLERRM ;
1978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1979 RAISE;
1980 end check_delete_project_type_ok;
1981
1982 /*Start: Addition of code for bug 2682806 */
1983
1984 Procedure check_delete_class_catg_ok (
1985 p_class_category IN VARCHAR2
1986 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1987 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1988 )
1989 IS
1990
1991 BEGIN
1992 x_return_status :='S';
1993 IF NVL(Pa_Control_items_utils.Check_Class_Category_In_Use
1994 (p_class_category=>p_class_category),0)=1
1995 THEN
1996 x_return_status := 'F' ;
1997 x_error_message_code := 'PA_CI_CLASS_CAT_IN_USE';
1998 END IF;
1999
2000 -- 4537865
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 x_error_message_code := SQLERRM ;
2004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2005 RAISE;
2006 end check_delete_class_catg_ok;
2007
2008 Procedure check_delete_class_code_ok (
2009 p_class_category IN VARCHAR2
2010 ,p_class_code IN VARCHAR2
2011 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2012 ,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2013 IS
2014
2015 BEGIN
2016 x_return_status :='S';
2017 IF NVL(Pa_Control_items_utils.Check_Class_Code_In_Use
2018 (p_class_category=>p_class_category,
2019 P_class_code => p_class_code ),0)=1
2020 THEN
2021 x_return_status := 'F' ;
2022 x_error_message_code := 'PA_CI_CLASS_CODE_IN_USE';
2023 END IF;
2024
2025 -- 4537865
2026 EXCEPTION
2027 WHEN OTHERS THEN
2028 x_error_message_code := SQLERRM ;
2029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2030 RAISE;
2031 end check_delete_class_code_ok;
2032
2033
2034 /*End: Addition of code for bug 2682806 */
2035
2036 --
2037 -- FUNCTION
2038 -- check_unique_project_reference
2039 -- PURPOSE
2040 -- This function returns 1 if a project reference is not already
2041 -- used in PA system and returns 0 if name is used.
2042 -- If Oracle error occurs, Oracle error number is returned.
2043 -- HISTORY
2044 -- 16-JUL-2003 zahid.khan Created
2045 -- 11-Dec-2005 sunkalya Bug Fix:4870305. Added parameter p_prod_code.
2046 --
2047 function check_unique_project_reference (p_proj_ref IN varchar2,
2048 p_prod_code IN varchar2, -- added for bug 4870305
2049 p_rowid IN varchar2 ) return number
2050 is
2051 cursor c1 is
2052 select project_id
2053 from pa_projects_all
2054 where pm_project_reference = p_proj_ref
2055 and pm_product_code = p_prod_code -- added for bug 4870305
2056 AND (p_ROWID IS NULL OR p_ROWID <> pa_projects_all.ROWID);
2057
2058 c1_rec c1%rowtype;
2059
2060 begin
2061 if (p_proj_ref is null ) then
2062 return(null);
2063 end if;
2064
2065 open c1;
2066 fetch c1 into c1_rec;
2067 if c1%notfound then
2068 close c1;
2069 return(1);
2070 else
2071 close c1;
2072 return(0);
2073 end if;
2074
2075 exception
2076 when others then
2077 return(SQLCODE);
2078
2079 end check_unique_project_reference;
2080
2081 --bug#2984611
2082 --This fucntion checks if the change in project type to
2083 --inter company project type is allowed or not.If the
2084 --primary ct/ship to ct. and bill to. customer are not
2085 --same then change to IC project type is not allowed.
2086 --This function takes the project type also as input
2087 --and performs the validation only when project type
2088 --is a IC project type i.e cc_prvdr_flag is 'Y'
2089
2090 function check_ic_proj_type_allowed(p_project_id IN NUMBER
2091 ,p_cc_prvdr_flag IN VARCHAR2 )
2092 RETURN NUMBER
2093 IS
2094 CURSOR c1 is
2095 SELECT 'Y'
2096 FROM DUAL
2097 WHERE EXISTS (SELECT 'Y'
2098 FROM PA_PROJECT_CUSTOMERS
2099 WHERE project_id = p_project_id
2100 AND (customer_id <> bill_to_customer_id
2101 OR customer_id <> ship_to_customer_id )) ;
2102 c1_rec c1%rowtype ;
2103
2104 BEGIN
2105 if p_project_id is NULL then
2106 return(null);
2107 end if ;
2108
2109 if nvl(p_cc_prvdr_flag,'N') = 'Y' then
2110 open c1 ;
2111 fetch c1 into c1_rec ;
2112
2113 if c1%notfound then
2114 close c1 ;
2115 return(0);
2116 else
2117 close c1 ;
2118 return (1);
2119 end if ;
2120 else
2121 return(0) ;
2122 end if ;
2123
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 if c1%ISOPEN THEN
2127 close c1 ;
2128 end if ;
2129 return(sqlcode) ;
2130 END check_ic_proj_type_allowed ;
2131
2132 /* Function added for bug 3738892 */
2133 FUNCTION is_flex_enabled ( appl_id IN NUMBER, flex_name IN varchar2)
2134 RETURN NUMBER
2135 IS
2136 flex_setup boolean;
2137 BEGIN
2138 flex_setup := Fnd_Flex_Apis.is_descr_setup(appl_id,flex_name);
2139 IF (flex_setup) THEN
2140 RETURN 1;
2141 ELSE
2142 RETURN 0;
2143 end if;
2144
2145 exception
2146 when others then
2147 return(SQLCODE);
2148 end is_flex_enabled;
2149
2150 /*
2151 Bug 5647964 Added the generic API which can be used for DFF Validations.
2152 The Procedure validates if the user has passed the required values for the various segments if they are required
2153 */
2154
2155
2156 PROCEDURE VALIDATE_DFF
2157 ( p_application_id IN NUMBER,
2158 p_flexfield_name IN VARCHAR2,
2159 p_attribute_category IN VARCHAR2,
2160 p_calling_module IN VARCHAR2,
2161 p_attribute1 IN VARCHAR2,
2162 p_attribute2 IN VARCHAR2,
2163 p_attribute3 IN VARCHAR2,
2164 p_attribute4 IN VARCHAR2,
2165 p_attribute5 IN VARCHAR2,
2166 p_attribute6 IN VARCHAR2,
2167 p_attribute7 IN VARCHAR2,
2168 p_attribute8 IN VARCHAR2,
2169 p_attribute9 IN VARCHAR2,
2170 p_attribute10 IN VARCHAR2,
2171 p_attribute11 IN VARCHAR2,
2172 p_attribute12 IN VARCHAR2,
2173 p_attribute13 IN VARCHAR2,
2174 p_attribute14 IN VARCHAR2,
2175 p_attribute15 IN VARCHAR2,
2176 x_return_status OUT NOCOPY VARCHAR2,
2177 x_msg_count OUT NOCOPY NUMBER,
2178 x_msg_data OUT NOCOPY VARCHAR2)
2179 IS
2180
2181
2182 l_flex_required BOOLEAN;
2183 l_is_context_segment_required BOOLEAN;
2184 l_global_req_segs_info1 Fnd_Flex_Apis.dff_required_segments_info;
2185 l_context_req_segs_info1 Fnd_Flex_Apis.dff_required_segments_info;
2186 l_segment_name VARCHAR2(30);
2187
2188 TYPE application_names IS VARRAY(15) of VARCHAR2(240);
2189
2190 v_application_names application_names;
2191 l_index NUMBER;
2192 i NUMBER;
2193 x_error_code NUMBER := 0;
2194 l_count NUMBER;
2195 BEGIN
2196 v_application_names := application_names();
2197
2198 l_flex_required := Fnd_Flex_Apis.is_descr_required(p_application_id,p_flexfield_name);
2199 IF (l_flex_required) THEN
2200 Fnd_Flex_Apis.get_dff_global_req_segs_info(p_application_id => p_application_id,
2201 p_flexfield_name => p_flexfield_name,
2202 x_is_context_segment_required => l_is_context_segment_required,
2203 x_global_req_segs_info => l_global_req_segs_info1) ;
2204
2205 IF (l_is_context_segment_required) THEN
2206 IF p_attribute_category IS NULL THEN
2207 x_error_code :=100 ;
2208 l_segment_name := NULL;
2209 ELSE
2210 Fnd_Flex_Apis.get_dff_context_req_segs_info(p_application_id => p_application_id,
2211 p_flexfield_name => p_flexfield_name,
2212 p_context_code => p_attribute_category,
2213 x_context_req_segs_info => l_context_req_segs_info1);
2214 END IF;
2215 END IF;
2216 END IF;
2217 /* GET APPL_COLUMN_NAMES FOR x_global_req_segs_info1 AND x_global_req_segs_info2
2218 None of these columns should be null */
2219 l_count := l_global_req_segs_info1.required_segment_names.count + l_context_req_segs_info1.required_segment_names.count ;
2220 v_application_names.EXTEND(l_count);
2221 l_index :=v_application_names.FIRST ;
2222
2223 if l_global_req_segs_info1.required_segment_names.first is not null then
2224 for x in l_global_req_segs_info1.required_segment_names.first..l_global_req_segs_info1.required_segment_names.last loop
2225 select APPLICATION_COLUMN_NAME
2226 into
2227 v_application_names(l_index)
2228 from fnd_descr_flex_column_usages
2229 where END_USER_COLUMN_NAME = l_global_req_segs_info1.required_segment_names(x)
2230 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2231 and DESCRIPTIVE_FLEX_CONTEXT_CODE = 'Global Data Elements' ;
2232 l_index := v_application_names.NEXT(l_index);
2233 End Loop;
2234 end if;
2235
2236 if l_context_req_segs_info1.required_segment_names.first is not null then
2237 for y in l_context_req_segs_info1.required_segment_names.first..l_context_req_segs_info1.required_segment_names.last loop
2238 select APPLICATION_COLUMN_NAME
2239 into
2240 v_application_names(l_index)
2241 from fnd_descr_flex_column_usages
2242 where END_USER_COLUMN_NAME = l_context_req_segs_info1.required_segment_names(y) --x_context_req_segs_info1(l_index)
2243 and DESCRIPTIVE_FLEXFIELD_NAME = p_flexfield_name
2244 and DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_category ;
2245 l_index := v_application_names.NEXT(l_index);
2246 End Loop;
2247 end if;
2248
2249 if x_error_code <> 100 then
2250 i := v_application_names.FIRST ;
2251 WHILE i IS NOT NULL LOOP
2252
2253 if v_application_names(i) = 'ATTRIBUTE1' then
2254 if p_attribute1 is null then
2255 x_error_code := 100;
2256 l_segment_name := 'ATTRIBUTE1';
2257 end if;
2258 end if;
2259
2260 if v_application_names(i) = 'ATTRIBUTE2' then
2261 if p_attribute2 is null then
2262 x_error_code := 100;
2263 l_segment_name := 'ATTRIBUTE2';
2264 end if;
2265 end if;
2266
2267 if v_application_names(i) = 'ATTRIBUTE3' then
2268 if p_attribute3 is null then
2269 x_error_code := 100;
2270 l_segment_name := 'ATTRIBUTE3';
2271 end if;
2272 end if;
2273
2274 if v_application_names(i) = 'ATTRIBUTE4' then
2275 if p_attribute4 is null then
2276 x_error_code := 100;
2277 l_segment_name := 'ATTRIBUTE4';
2278 end if;
2279 end if;
2280
2281 if v_application_names(i) = 'ATTRIBUTE5' then
2282 if p_attribute5 is null then
2283 x_error_code := 100;
2284 l_segment_name := 'ATTRIBUTE5';
2285 end if;
2286 end if;
2287
2288 if v_application_names(i) = 'ATTRIBUTE6' then
2289 if p_attribute6 is null then
2290 x_error_code := 100;
2291 l_segment_name := 'ATTRIBUTE6';
2292 end if;
2293 end if;
2294
2295 if v_application_names(i) = 'ATTRIBUTE7' then
2296 if p_attribute7 is null then
2297 x_error_code := 100;
2298 l_segment_name := 'ATTRIBUTE7';
2299 end if;
2300 end if;
2301
2302 if v_application_names(i) = 'ATTRIBUTE8' then
2303 if p_attribute8 is null then
2304 x_error_code := 100;
2305 l_segment_name := 'ATTRIBUTE8';
2306 end if;
2307 end if;
2308
2309 if v_application_names(i) = 'ATTRIBUTE9' then
2310 if p_attribute9 is null then
2311 x_error_code := 100;
2312 l_segment_name := 'ATTRIBUTE9';
2313 end if; /*error here */
2314 end if;
2315
2316 if v_application_names(i) = 'ATTRIBUTE10' then
2317 if p_attribute10 is null then
2318 x_error_code := 100;
2319 l_segment_name := 'ATTRIBUTE10';
2320 end if;
2321 end if;
2322
2323 if v_application_names(i) = 'ATTRIBUTE11' then
2324 if p_attribute11 is null then
2325 x_error_code := 100;
2326 l_segment_name := 'ATTRIBUTE11';
2327 end if;
2328 end if;
2329
2330 if v_application_names(i) = 'ATTRIBUTE12' then
2331 if p_attribute12 is null then
2332 x_error_code := 100;
2333 l_segment_name := 'ATTRIBUTE12';
2334 end if;
2335 end if;
2336
2337 if v_application_names(i) = 'ATTRIBUTE13' then
2338 if p_attribute13 is null then
2339 x_error_code := 100;
2340 l_segment_name := 'ATTRIBUTE13';
2341 end if;
2342 end if;
2343
2344 if v_application_names(i) = 'ATTRIBUTE14' then
2345 if p_attribute14 is null then
2346 x_error_code := 100;
2347 l_segment_name := 'ATTRIBUTE14';
2348 end if;
2349 end if;
2350
2351 if v_application_names(i) = 'ATTRIBUTE15' then
2352 if p_attribute15 is null then
2353 x_error_code := 100;
2354 l_segment_name := 'ATTRIBUTE15';
2355 end if;
2356 end if;
2357
2358 i := v_application_names.NEXT(i);
2359 END LOOP;
2360 end if;
2361
2362
2363 IF x_error_code = 100 THEN
2364 if p_calling_module = 'ADD_CLASS_CATEGORIES' then
2365 l_segment_name := null; /* Added this code to throw the error message for self-service without reference to Application column name.*/
2366 end if;
2367 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
2368 p_msg_name => 'FLEX-MISSING SEGMENT VALUE',
2369 p_token1 => 'SEGMENT',
2370 p_value1 => l_segment_name,
2371 p_token2 => 'FLEXFIELD',
2372 p_value2 => p_flexfield_name);
2373 -- x_msg_data := 'FLEX-MISSING SEGMENT VALUE';
2374 x_return_status := FND_API.G_RET_STS_ERROR ;
2375 END IF;
2376
2377 x_return_status := FND_API.G_RET_STS_SUCCESS;
2378
2379 EXCEPTION
2380 WHEN OTHERS THEN
2381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2382 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_UTILS',
2383 p_procedure_name => 'VALIDATE_DFF',
2384 p_error_text => SUBSTRB(SQLERRM,1,240));
2385 raise;
2386 END VALIDATE_DFF;
2387
2388 END PA_PROJECT_UTILS;
[Home] [Help]