[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECTS_MAINT_UTILS
Source
1 PACKAGE BODY PA_PROJECTS_MAINT_UTILS AS
2 /* $Header: PARMPRUB.pls 120.5 2009/06/15 07:09:38 admarath ship $ */
3 -- API name : check_org_name_or_id
4 -- Type : Public
5 -- Pre-reqs : None.
6 -- Parameters :
7 -- p_organization_id IN hr_organization_units.organization_id%TYPE Required
8 -- p_name IN hr_organization_units.name%TYPE Required
9 -- p_check_id_flag IN VARCHAR2 Required
10 -- x_organization_id OUT hr_organization_units.organization_id%TYPE Required
11 -- x_return_status OUT VARCHAR2 Required
12 -- x_error_msg_code OUT VARCHAR2 Required
13 --
14 -- History
15 --
16 -- 18-AUG-2000 -- Sakthi/William - Created.
17 --
18 --
19 procedure Check_org_name_Or_Id
20 (p_organization_id IN hr_organization_units.organization_id%TYPE
21 ,p_name IN hr_organization_units.name%TYPE
22 ,p_check_id_flag IN VARCHAR2
23 ,x_organization_id OUT NOCOPY hr_organization_units.organization_id%TYPE --File.Sql.39 bug 4440895
24 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
25 ,x_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
26 BEGIN
27 IF (p_organization_id IS NOT NULL) THEN
28 IF (p_check_id_flag = 'Y') THEN
29 SELECT organization_id
30 INTO x_organization_id
31 FROM hr_organization_units
32 WHERE organization_id = p_organization_id;
33 ELSE
34 x_organization_id := p_organization_id;
35 END IF;
36 ELSE
37 SELECT organization_id
38 INTO x_organization_id
39 FROM hr_organization_units
40 WHERE name = p_name;
41 END IF;
42 x_return_status:= FND_API.G_RET_STS_SUCCESS;
43 EXCEPTION
44 WHEN no_data_found THEN
45 x_organization_id := NULL ; -- 4537865
46 x_return_status:= FND_API.G_RET_STS_ERROR;
47 x_error_msg_code:= 'PA_INVALID_ORG';
48 WHEN too_many_rows THEN
49 x_organization_id := NULL ; -- 4537865
50 x_return_status:= FND_API.G_RET_STS_ERROR;
51 x_error_msg_code:= 'PA_ORG_NOT_UNIQUE';
52 WHEN OTHERS THEN
53 x_organization_id := NULL ; -- 4537865
54 x_error_msg_code := SQLCODE ; -- 4537865
55 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
56 RAISE;
57 END Check_org_name_Or_Id;
58
59 -- API name : check_check_project_status_or_id
60 -- Type : Public
61 -- Pre-reqs : None.
62 -- Parameters :
63 -- p_project_status_code IN pa_project_statuses.project_status_code%TYPE Required
64 -- p_project_status_name IN pa_project_statuses.project_status_name%TYPE Required
65 -- p_check_id_flag IN VARCHAR2 Required
66 -- x_project_status_code OUT pa_project_statuses.project_status_code%TYPE Required
67 -- x_return_status OUT VARCHAR2 Required
68 -- x_error_msg_code OUT VARCHAR2 Required
69 --
70 -- History
71 --
72 -- 18-AUG-2000 -- Sakthi/William - Created.
73 --
74 --
75 procedure Check_project_status_or_id
76 (p_project_status_code IN pa_project_statuses.project_status_code%TYPE
77 ,p_project_status_name IN pa_project_statuses.project_status_name%TYPE
78 ,p_check_id_flag IN VARCHAR2
79 ,x_project_status_code OUT NOCOPY pa_project_statuses.project_status_code%TYPE --File.Sql.39 bug 4440895
80 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
81 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
82 IS
83 BEGIN
84 IF (p_project_status_code IS NOT NULL) THEN
85 IF (p_check_id_flag = 'Y') THEN
86 SELECT project_status_code
87 INTO x_project_status_code
88 FROM pa_project_statuses
89 WHERE project_status_code = p_project_status_code;
90 ELSE
91 x_project_status_code := p_project_status_code;
92 END IF;
93 ELSE
94 SELECT project_status_code
95 INTO x_project_status_code
96 FROM pa_project_statuses
97 WHERE project_status_name = p_project_status_name;
98 END IF;
99 x_return_status:= FND_API.G_RET_STS_SUCCESS;
100 EXCEPTION
101 WHEN no_data_found THEN
102 -- 4537865 : RESET x_project_status_code value also
103 x_project_status_code := NULL ;
104
105 x_return_status:= FND_API.G_RET_STS_ERROR;
106 x_error_msg_code:= 'PA_PROJECT_STATUS_INVALID';
107 WHEN too_many_rows THEN
108 -- 4537865 : RESET x_project_status_code value also
109 x_project_status_code := NULL ;
110 x_return_status:= FND_API.G_RET_STS_ERROR;
111 x_error_msg_code:= 'PA_PROJECT_STATUS_NOT_UNIQUE';
112 WHEN OTHERS THEN
113 -- 4537865 : RESET x_project_status_code and x_error_msg_code value also
114 x_project_status_code := NULL ;
115 x_error_msg_code := SQLCODE ;
116
117 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
118 RAISE;
119 END Check_project_status_Or_Id;
120
121 -- API name : check_customer_name_or_id
122 -- Type : Public
123 -- Pre-reqs : None.
124 -- Parameters :
125 -- p_customer_id IN ra_customers.customer_id%TYPE Required
126 -- p_customer_name IN ra_customers.customer_name%TYPE Required
127 -- p_check_id_flag IN VARCHAR2 Required
128 -- x_return_status OUT VARCHAR2 Required
129 -- x_error_msg_code OUT VARCHAR2 Required
130 --
131 --
132 -- History
133 --
134 -- 18-AUG-2000 -- Sakthi/William - Created.
135 --
136 --
137 procedure Check_customer_name_or_id
138 ( p_customer_id IN hz_cust_accounts.cust_account_id%TYPE -- ra_customers.customer_id%TYPE -- for 4363092 TCA changes
139 ,p_customer_name IN hz_parties.party_name%TYPE -- ra_customers.customer_name%TYPE -- for 4363092 TCA changes
140 ,p_check_id_flag IN VARCHAR2
141 ,x_customer_id OUT NOCOPY hz_cust_accounts.cust_account_id%TYPE -- ra_customers.customer_id%TYPE -- for 4363092 TCA changes --File.Sql.39 bug 4440895
142 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
143 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
144 IS
145 BEGIN
146 IF (p_customer_id IS NOT NULL) THEN
147 IF (p_check_id_flag = 'Y') THEN
148
149 -- 4363092 TCA changes, replaced RA views with HZ tables
150 /*
151 SELECT customer_id
152 INTO x_customer_id
153 FROM ra_customers
154 WHERE customer_id = p_customer_id;
155 */
156
157 SELECT cust_acct.cust_account_id
158 INTO x_customer_id
159 FROM
160 hz_parties party,
161 hz_cust_accounts cust_acct
162 WHERE
163 cust_acct.party_id = party.party_id
164 and cust_acct.cust_account_id = p_customer_id;
165
166 -- 4363092 end
167
168 ELSE
169 x_customer_id := p_customer_id;
170 END IF;
171 ELSE
172 -- 4363092 TCA changes, replaced RA views with HZ tables
173 /*
174 SELECT customer_id
175 INTO x_customer_id
176 FROM ra_customers
177 WHERE customer_name = p_customer_name;
178 */
179
180 SELECT cust_acct.cust_account_id
181 INTO x_customer_id
182 FROM
183 hz_parties party,
184 hz_cust_accounts cust_acct
185 WHERE
186 cust_acct.party_id = party.party_id
187 and substrb(party.party_name,1,50) = p_customer_name;
188
189 -- 4363092 end
190
191 END IF;
192 x_return_status:= FND_API.G_RET_STS_SUCCESS;
193 EXCEPTION
194 WHEN no_data_found THEN
195
196 -- 4537865 : RESET x_customer_id also
197 x_customer_id := NULL ;
198
199 x_return_status:= FND_API.G_RET_STS_ERROR;
200 x_error_msg_code:= 'PA_CUSTOMER_ID_INVALID';
201 WHEN too_many_rows THEN
202
203 -- 4537865 : RESET x_customer_id also
204 x_customer_id := NULL ;
205
206 x_return_status:= FND_API.G_RET_STS_ERROR;
207 x_error_msg_code:= 'PA_CUS_NAME_NOT UNIQUE';
208 WHEN OTHERS THEN
209
210 -- 4537865 : RESET x_customer_id and x_error_msg_code also
211 x_customer_id := NULL ;
212 x_error_msg_code := SQLCODE;
213
214 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
215 RAISE;
216 END Check_customer_name_Or_Id;
217
218 -- API name : check_probability_code_or_id
219 -- Type : Public
220 -- Pre-reqs : None.
221 -- Parameters :
222 -- p_probability_member_id IN pa_probability_members.probability_member_id%TYPE Required
223 -- p_probability_percentage IN pa_probability_members.probability_percentage%TYPE Required
224 -- p_project_type IN pa_projects_all.project_type%TYPE
225 -- p_probability_list_id IN pa_probability_lists.probability_list_id%TYPE
226 -- p_check_id_flag IN VARCHAR2 Required
227 -- x_probability_member_id OUT pa_probability_members.probability_member_id%TYPE Required
228 -- x_return_status OUT VARCHAR2 Required
229 -- x_error_msg_code OUT VARCHAR2 Required
230 --
231 -- History
232 --
233 -- 18-AUG-2000 -- Sakthi/William - Created.
234 -- 18-MAR-2002 -- xxlu Added IN parameter p_probability_list_id.
235 --
236 PROCEDURE CHECK_PROBABILITY_CODE_OR_ID
237 (p_probability_member_id IN pa_probability_members.probability_member_id%TYPE
238 ,p_probability_percentage IN pa_probability_members.probability_percentage%TYPE
239 , p_project_type IN pa_projects_all.project_type%TYPE
240 ,p_probability_list_id IN pa_probability_lists.probability_list_id%TYPE:=NULL
241 ,p_check_id_flag IN VARCHAR2
242 ,x_probability_member_id OUT NOCOPY pa_probability_members.probability_member_id%TYPE --File.Sql.39 bug 4440895
243 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
244 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
245 IS
246 l_current_id NUMBER := NULL;
247 l_num_ids NUMBER := 0;
248 l_id_found_flag VARCHAR(1) := 'N';
249
250
251 CURSOR c_ids IS
252 select probability_member_id
253 from pa_probability_members
254 where probability_list_id =
255 (select probability_list_id from pa_project_types where
256 project_type = p_project_type)
257 and probability_percentage = p_probability_percentage;
258
259 --MOAC Changes: Bug 4363092: removed nvl usage with org_id
260 CURSOR c_ids1 IS -- Added the cursor for Bug#3807805
261 select probability_member_id
262 from pa_probability_members
263 where probability_list_id =
264 (select probability_list_id from pa_project_types_all
265 where project_type = p_project_type
266 and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID )
267 and probability_percentage = p_probability_percentage;
268
269 BEGIN
270 IF (p_probability_member_id IS NOT NULL) THEN
271 IF (p_check_id_flag = 'Y') THEN
272 SELECT probability_member_id
273 INTO x_probability_member_id
274 FROM pa_probability_members
275 WHERE probability_member_id = p_probability_member_id;
276
277 ELSIF (p_check_id_flag='N') THEN
278 x_probability_member_id := p_probability_member_id;
279
280
281 ELSIF (p_check_id_flag = 'A') THEN
282 IF (p_probability_percentage IS NULL) THEN
283 -- Return a null ID since the probability is null.
284 x_probability_member_id := NULL;
285 ELSE
286
287 -- Find the ID which matches the Name passed
288 If PA_PROJECT_REQUEST_PVT.G_ORG_ID is null then -- Added the if condition for Bug#3807805
289 OPEN c_ids;
290 LOOP
291 FETCH c_ids INTO l_current_id;
292 EXIT WHEN c_ids%NOTFOUND;
293 IF (l_current_id = p_probability_member_id) THEN
294 l_id_found_flag := 'Y';
295 x_probability_member_id := p_probability_member_id;
296 END IF;
297 END LOOP;
298 l_num_ids := c_ids%ROWCOUNT;
299 CLOSE c_ids;
300 else -- Added the else block for Bug#3807805
301 OPEN c_ids1;
302 LOOP
303 FETCH c_ids1 INTO l_current_id;
304 EXIT WHEN c_ids1%NOTFOUND;
305 IF (l_current_id = p_probability_member_id) THEN
306 l_id_found_flag := 'Y';
307 x_probability_member_id := p_probability_member_id;
308 END IF;
309 END LOOP;
310 l_num_ids := c_ids1%ROWCOUNT;
311 CLOSE c_ids1;
312 end if;
313
314 IF (l_num_ids = 0) THEN
315 -- No IDs for name
316 RAISE NO_DATA_FOUND;
317 ELSIF (l_num_ids = 1) THEN
318 -- Since there is only one ID for the name use it.
319 x_probability_member_id := l_current_id;
320 ELSIF (l_id_found_flag = 'N') THEN
321 -- More than one ID for the name and none of the IDs matched
322 -- the ID passed in.
323 RAISE TOO_MANY_ROWS;
324 END IF;
325 END IF;
326
327 END IF;
328
329 ELSE
330 IF (p_probability_percentage IS NOT NULL) THEN
331
332 IF (p_probability_list_id IS NULL) THEN
333 If PA_PROJECT_REQUEST_PVT.G_ORG_ID is null then -- Added the if condition for Bug#3807805
334 select probability_member_id
335 into x_probability_member_id
336 from pa_probability_members
337 where probability_list_id =
338 (select probability_list_id from pa_project_types where
339 project_type = p_project_type)
340 and probability_percentage = p_probability_percentage;
341 else -- Added the else block for Bug#3807805
342 select probability_member_id
343 into x_probability_member_id
344 from pa_probability_members
345 where probability_list_id =
346 (select probability_list_id from pa_project_types_all where
347 project_type = p_project_type
348 and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID ) --MOAC Changes: Bug 4363092: removed nvl usage with org_id
349 and probability_percentage = p_probability_percentage;
350 end if;
351 ELSE
352 SELECT probability_member_id
353 INTO x_probability_member_id
354 FROM pa_probability_members
355 WHERE probability_list_id = p_probability_list_id
356 AND probability_percentage = p_probability_percentage;
357 END IF;
358
359 ELSE
360 x_probability_member_id := NULL;
361 END IF;
362
363 END IF;
364 x_return_status:= FND_API.G_RET_STS_SUCCESS;
365
366 EXCEPTION
367 WHEN no_data_found THEN
368 x_probability_member_id := NULL;
369 x_return_status:= FND_API.G_RET_STS_ERROR;
370 x_error_msg_code:= 'PA_PROBABILITY_ID_INVALID';
371 WHEN too_many_rows THEN
372 x_probability_member_id := NULL;
373 x_return_status:= FND_API.G_RET_STS_ERROR;
374 x_error_msg_code:= 'PA_PROBABILITY_NOT_UNIQUE';
375 WHEN OTHERS THEN
376 -- 4537865 : RESET x_error_msg_code also
377 x_error_msg_code := SQLCODE ;
378
379 x_probability_member_id := NULL;
380 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
381 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS', p_procedure_name => 'CHECK_PROBABILITY_CODE_OR_ID');
382 RAISE;
383 END CHECK_PROBABILITY_CODE_OR_ID;
384
385 -- API name : check_calendar_name_or_id
386 -- Type : Public
387 -- Pre-reqs : None.
388 -- Parameters :
389 -- p_calendar_id IN jtf_calendars_tl.calendar_id%TYPE Required
390 -- p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE Required
391 -- p_check_id_flag IN VARCHAR2 Required
392 -- x_calendar_id OUT jtf_calendars_tl.calendar_id%TYPE Required
393 -- x_return_status OUT VARCHAR2 Required
394 -- x_error_msg_code OUT VARCHAR2 Required
395 --
396 -- History
397 --
398 -- 18-AUG-2000 -- Sakthi/William - Created.
399 --
400 --
401 PROCEDURE CHECK_CALENDAR_NAME_OR_ID
402 (p_calendar_id IN jtf_calendars_vl.calendar_id%TYPE
403 ,p_calendar_name IN jtf_calendars_vl.calendar_name%TYPE
404 ,p_check_id_flag IN VARCHAR2
405 ,x_calendar_id OUT NOCOPY jtf_calendars_vl.calendar_id%TYPE --File.Sql.39 bug 4440895
406 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
407 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
408 IS
409 BEGIN
410 IF (p_calendar_id IS NOT NULL) THEN
411 IF (p_check_id_flag = 'Y') THEN
412 SELECT calendar_id
413 INTO x_calendar_id
414 FROM jtf_calendars_b --Used _b table:Bug 4352162
415 WHERE calendar_id = p_calendar_id;
416 ELSE
417 x_calendar_id := p_calendar_id;
418 END IF;
419 ELSE
420 SELECT calendar_id
421 INTO x_calendar_id
422 FROM jtf_calendars_vl
423 WHERE calendar_name = p_calendar_name;
424 END IF;
425 x_return_status:= FND_API.G_RET_STS_SUCCESS;
426 EXCEPTION
427 WHEN no_data_found THEN
428 -- 4537865 : RESET x_calendar_id also
429 x_calendar_id := NULL ;
430
431 x_return_status:= FND_API.G_RET_STS_ERROR;
432 x_error_msg_code:= 'PA_CALENDAR_ID_INVALID';
433 WHEN too_many_rows THEN
434
435 -- 4537865 : RESET x_calendar_id also
436 x_calendar_id := NULL ;
437
438 x_return_status:= FND_API.G_RET_STS_ERROR;
439 x_error_msg_code:= 'PA_CALENDAR_NOT_UNIQUE';
440 WHEN OTHERS THEN
441
442 -- 4537865 : RESET x_calendar_id and x_error_msg_code: also
443 x_calendar_id := NULL ;
444 x_error_msg_code := SQLCODE ;
445
446 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
447 RAISE;
448 END CHECK_CALENDAR_NAME_OR_ID;
449
450 -- API name : get_project_manager
451 -- Type : Public
452 -- Pre-reqs : None.
453 -- Parameters :
454 -- p_project_id IN NUMBER
455 -- Return : NUMBER
456 --
457 -- History
458 --
459 -- 18-AUG-2000 -- Sakthi/William - Created.
460 --
461 --
462 FUNCTION GET_PROJECT_MANAGER( p_project_id IN NUMBER)
463 RETURN NUMBER
464 IS
465 CURSOR C1 (c_project_id NUMBER)
466 IS
467 Select PPP.RESOURCE_SOURCE_ID
468 FROM PA_PROJECT_PARTIES PPP ,
469 PA_PROJECT_ROLE_TYPES PPRT
470 WHERE
471 PPP.PROJECT_ID = c_project_id
472 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
473 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
474 AND trunc(sysdate) between trunc(PPP.start_date_active)
475 AND NVL(trunc(PPP.end_date_active),sysdate)
476 AND ppp.object_type = 'PA_PROJECTS'
477 AND ppp.object_id = c_project_id
478 AND resource_type_id = 101;
479 /* Added for bug 6802729 the following conditions
480 * and ppp.object_type = 'PA_PROJECTS'
481 * and ppp.object_id = c_project_id
482 * and resource_type_id = 101
483 * .
484 * This allows us to better leverage the indexes since object is a key
485 * part of this table, and the resource_type_id = 101 means that the person is
486 * an employee (and not an external party). */
487
488 l_return_value NUMBER(10);
489 BEGIN
490 OPEN C1 (p_project_id);
491 FETCH C1 INTO l_return_value;
492 CLOSE C1;
493 RETURN l_return_value;
494 END;
495
496 -- API name : get_project_manager_name
497 -- Type : Public
498 -- Pre-reqs : None.
499 -- Parameters :
500 -- p_person_id IN NUMBER
501 -- Return : VARCHAR2
502 --
503 -- History
504 --
505 -- 18-AUG-2000 -- Sakthi/William - Created.
506 --
507 --
508 FUNCTION GET_PROJECT_MANAGER_NAME (p_person_id IN NUMBER)
509 RETURN VARCHAR2
510 IS
511
512 /*
513 CURSOR C1( p_person_id NUMBER)
514 IS
515 Select PE.full_name
516 FROM PA_EMPLOYEES PE
517 WHERE PE.PERSON_ID = p_person_id;
518 */
519
520 CURSOR C1( p_person_id NUMBER)
521 IS
522 Select PE.full_name
523 FROM per_all_people_f PE
524 WHERE PE.PERSON_ID = p_person_id
525 AND trunc(sysdate) between PE.EFFECTIVE_START_DATE AND PE.EFFECTIVE_END_DATE; --Included by avaithia for Bug 3448680
526 /* 6802729 removed nvl() from PE.EFFECTIVE_END_DATE as this is a not null column
527 * */
528 l_manager_id NUMBER(10);
529 l_return_value VARCHAR2(250);
530 BEGIN
531 l_manager_id := get_project_manager(p_person_id);
532 IF l_manager_id IS NOT NULL
533 THEN
534 OPEN C1 (l_manager_id);
535 FETCH C1 INTO l_return_value;
536 CLOSE C1;
537 END IF;
538 RETURN l_return_value;
539 END;
540
541 -- API name : get_primary_customer
542 -- Type : Public
543 -- Pre-reqs : None.
544 -- Parameters :
545 -- p_project_id IN NUMBER
546 -- Return : NUMBER
547 --
548 -- History
549 --
550 -- 18-AUG-2000 -- Sakthi/William - Created.
551 -- 21-MAR-2001 anlee
552 -- Modified to remove join
553 -- with PA_LOOKUPS
554 -- The designation "Primary"
555 -- is not always applicable
556 --
557 FUNCTION GET_PRIMARY_CUSTOMER( p_project_id IN NUMBER)
558 RETURN NUMBER
559 IS
560
561 -- 3703272 Included substrb in the Cursor select statement while retrieving Customer Name
562 CURSOR C1 (c_project_id NUMBER)
563 IS
564 Select PPC.customer_id, NVL(PPC.customer_bill_split,0) bill_split, substrb(PCV.customer_name,1,50)
565 FROM PA_PROJECT_CUSTOMERS PPC,
566 PA_CUSTOMERS_V PCV
567 WHERE PPC.project_id = c_project_id
568 AND PPC.customer_id = PCV.customer_id
569 ORDER BY bill_split DESC, customer_name;
570
571 l_return_value NUMBER(10);
572 l_bill_split NUMBER(10);
573 l_customer_name VARCHAR2(250);
574
575 BEGIN
576
577 OPEN C1 (p_project_id);
578 FETCH C1 INTO l_return_value, l_bill_split, l_customer_name;
579 CLOSE C1;
580
581 RETURN l_return_value;
582
583 END;
584
585 -- API name : get_primary_customer_name
586 -- Type : Public
587 -- Pre-reqs : None.
588 -- Parameters :
589 -- p_project_id IN NUMBER
590 -- Return : VARCHAR2
591 --
592 -- History
593 --
594 -- 18-AUG-2000 -- Sakthi/William - Created.
595 --
596 --
597 FUNCTION GET_PRIMARY_CUSTOMER_NAME( p_project_id IN NUMBER)
598 RETURN VARCHAR2
599 IS
600
601 --3645993 : Included substrb in the Cursor select statement while retrieving Customer Name
602
603 CURSOR C1 (c_project_id NUMBER)
604 IS
605 Select PPC.customer_id, NVL(PPC.customer_bill_split,0) bill_split, substrb(PCV.customer_name,1,50)
606 FROM PA_PROJECT_CUSTOMERS PPC,
607 PA_CUSTOMERS_V PCV
608 WHERE PPC.project_id = c_project_id
609 AND PPC.customer_id = PCV.customer_id
610 ORDER BY bill_split DESC, customer_name;
611
612 l_customer_id NUMBER(10);
613 l_bill_split NUMBER(10);
614 l_return_value VARCHAR2(250);
615 BEGIN
616
617 OPEN C1 (p_project_id);
618 FETCH C1 INTO l_customer_id, l_bill_split, l_return_value;
619 CLOSE C1;
620
621 RETURN l_return_value;
622
623 END;
624
625 -- API name : class_check_trans
626 -- Type : Public
627 -- Pre-reqs : None.
628 -- Parameters :
629 -- p_project_id IN NUMBER
630 -- Return : VARCHAR2
631 --
632 -- History
633 --
634 -- 18-AUG-2000 -- Sakthi/William - Created.
635 --
636 --
637 FUNCTION CLASS_CHECK_TRANS (p_project_id NUMBER)
638 RETURN VARCHAR2 IS
639 CURSOR class_cdl (c_project_id NUMBER)
640 IS
641 SELECT '1'
642 FROM pa_expenditure_items pai, --Bug#3088387pa_tasks t,
643 pa_cost_distribution_lines pcd
644 WHERE pai.project_id=c_project_id AND --Bug#3088387t.task_id AND
645 pai.expenditure_item_id=pcd.expenditure_item_id ;
646 -- Bug#3088387AND t.project_id=c_project_id;
647
648 CURSOR class_draft_revenue (p_project_id NUMBER)
649 IS
650 SELECT '1'
651 FROM pa_draft_revenues
652 WHERE project_id=p_project_id;
653
654 CURSOR class_draft_invoice (p_project_id NUMBER)
655 IS
656 SELECT '1'
657 FROM pa_draft_invoices
658 WHERE project_id=p_project_id;
659
660 rec_class_cdl VARCHAR2(1);
661 rec_class_draft_rev VARCHAR2(1);
662 rec_class_draft_inv VARCHAR2(1);
663 l_return VARCHAR2(1);
664 Begin
665 OPEN class_cdl(p_project_id);
666 FETCH class_cdl INTO rec_class_cdl;
667
668 OPEN class_draft_revenue(p_project_id);
669 FETCH class_draft_revenue INTO rec_class_draft_rev;
670
671 OPEN class_draft_invoice(p_project_id);
672 FETCH class_draft_invoice INTO rec_class_draft_inv;
673
674 IF class_cdl%notfound AND class_draft_invoice%NOTFOUND
675 AND class_draft_revenue%notfound THEN
676 l_return:='N';
677 ELSE
678 l_return:='Y';
679 END IF;
680 CLOSE class_cdl;
681 CLOSE class_draft_invoice;
682 CLOSE class_draft_revenue;
683 RETURN l_return;
684 END CLASS_CHECK_TRANS;
685
686 -- API name : check_class_catg_can_delete
687 -- Type : Public
688 -- Pre-reqs : None.
689 -- Parameters :
690 -- p_object_id IN NUMBER
691 -- p_object_type IN VARCHAR2
692 -- p_class_category IN VARCHAR2
693 -- x_return_status OUT VARCHAR2
694 -- x_error_msg_code OUT VARCHAR2
695 --
696 -- History
697 --
698 -- 18-AUG-2000 -- Sakthi/William - Created.
699 --
700 --
701 PROCEDURE CHECK_CLASS_CATG_CAN_DELETE (p_object_id NUMBER,
702 p_object_type VARCHAR2,
703 p_class_category VARCHAR2,
704 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
705 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
706 IS
707 CURSOR class_delrec (c_class_category VARCHAR2)
708 IS
709 SELECT autoaccounting_flag
710 FROM pa_class_categories
711 WHERE class_category = c_class_category;
712
713 l_dummy VARCHAR2(1);
714 l_rec_Status_flag VARCHAR2(1);
715 l_project_id NUMBER;
716 BEGIN
717 x_return_status := 'S';
718 if p_object_type = 'PA_TASKS' then
719 SELECT project_id
720 INTO l_project_id
721 FROM pa_tasks
722 WHERE task_id = p_object_id;
723 else
724 l_project_id := p_object_id;
725 end if;
726
727 /* changes for bug 2681127 */
728 IF pa_projects_maint_utils.class_check_mandatory (p_class_category, l_project_id) = 'Y' THEN
729 x_error_msg_code := 'PA_CLASS_CATEGORY_MANDATORY';
730 x_return_status := 'E' ;
731 RETURN;
732 END IF;
733 /* changes for bug 2681127 end */
734
735 l_dummy := PA_PROJECTS_MAINT_UTILS.CLASS_CHECK_TRANS(l_project_id);
736 IF l_dummy ='Y'
737 THEN
738 OPEN class_delrec(p_class_category);
739 FETCH class_delrec INTO l_rec_status_flag;
740 IF (l_rec_status_flag = 'Y')
741 THEN
742 CLOSE class_delrec;
743 x_error_msg_code := 'PA_PRJ_TRAN_ERR';
744 x_return_Status := 'E' ;
745 ELSE
746 CLOSE class_delrec;
747 END IF;
748 END IF;
749 -- 4537865 : Based on this API usage I have Included ths exception block
750 EXCEPTION
751 WHEN OTHERS THEN
752 x_return_status := 'U';
753 x_error_msg_code := SQLCODE;
754 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
755 p_procedure_name => 'CHECK_CLASS_CATG_CAN_DELETE',
756 p_error_text => SUBSTRB(SQLERRM,1,240));
757 raise;
758 END CHECK_CLASS_CATG_CAN_DELETE;
759
760
761 -- API name : check_duplicate_class_catg
762 -- Type : Public
763 -- Pre-reqs : None.
764 -- Parameters :
765 -- p_object_id IN NUMBER
766 -- p_object_type IN VARCHAR2
767 -- p_class_category IN VARCHAR2
768 -- p_class_code IN VARCHAR2
769 -- x_return_status OUT VARCHAR2
770 -- x_error_msg_code OUT VARCHAR2
771 --
772 -- History
773 --
774 -- 18-AUG-2000 -- Sakthi/William - Created.
775 --
776 --
777 PROCEDURE CHECK_DUPLICATE_CLASS_CATG (p_object_id NUMBER,
778 p_object_type VARCHAR2,
779 p_class_category VARCHAR2,
780 p_class_code VARCHAR2,
781 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
782 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
783 IS
784 CURSOR l_duplicate_cur (c_object_id NUMBER,
785 c_object_type VARCHAR2,
786 c_class_category VARCHAR2,
787 c_class_code VARCHAR2)
788 IS
789 SELECT 'x'
790 FROM PA_PROJECT_CLASSES
791 WHERE object_id = c_object_id
792 AND object_type = c_object_type
793 AND class_category = c_class_category
794 AND class_code = c_class_code;
795 l_dummy VARCHAR2(1);
796 BEGIN
797 x_return_status := 'S';
798 OPEN l_duplicate_cur(p_object_id, p_object_type, p_class_category,p_class_code);
799 FETCH l_duplicate_cur INTO l_dummy;
800 IF l_duplicate_cur%FOUND
801 THEN
802 x_error_msg_code := 'PA_DUPLICATE_CLASS_CATG';
803 x_return_status := 'E';
804 END IF;
805 CLOSE l_duplicate_cur;
806 -- 4537865 : Based on this API usage I have Included ths exception block
807 EXCEPTION
808 WHEN OTHERS THEN
809 x_return_status := 'U';
810 x_error_msg_code := SQLCODE;
811 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
812 p_procedure_name => 'CHECK_DUPLICATE_CLASS_CATG',
813 p_error_text => SUBSTRB(SQLERRM,1,240));
814 raise;
815 END CHECK_DUPLICATE_CLASS_CATG;
816
817
818 -- API name : check_class_catg_one_only_code
819 -- Type : Public
820 -- Pre-reqs : None.
821 -- Parameters :
822 -- p_object_id IN NUMBER
823 -- p_object_type IN VARCHAR2
824 -- p_class_category IN VARCHAR2
825 -- x_return_status OUT VARCHAR2
826 -- x_error_msg_code OUT VARCHAR2
827 --
828 -- History
829 --
830 -- 18-AUG-2000 -- Sakthi/William - Created.
831 --
832 --
833 PROCEDURE CHECK_CLASS_CATG_ONE_ONLY_CODE (
834 p_object_id NUMBER,
835 p_object_type VARCHAR2,
836 p_class_category VARCHAR2,
837 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
838 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
839 IS
840 CURSOR Check_One_code_only_cur (c_object_id NUMBER,
841 c_object_type VARCHAR2,
842 c_class_category VARCHAR2)
843 IS
844 SELECT 'x'
845 FROM PA_PROJECT_CLASSES_V PAC,
846 PA_CLASS_CATEGORIES PCC
847 WHERE PAC.object_id = c_object_id AND
848 PAC.object_type = c_object_type AND
849 PAC.class_category = c_class_category AND
850 PAC.class_category = PCC.class_category AND
851 PCC.pick_one_code_only_flag = 'Y';
852 l_dummy VARCHAR2(1);
853 BEGIN
854 x_return_status := 'S';
855 OPEN Check_One_code_only_cur(p_object_id, p_object_type, p_class_category);
856 Fetch Check_One_code_only_cur into l_dummy;
857 IF Check_One_code_only_cur%FOUND THEN
858 x_error_msg_code:= 'PA_ONE_CODE_ONLY_CLASS';
859 x_return_status := 'E';
860 ElSE
861 CLOSE Check_One_code_only_cur;
862 END IF;
863 -- 4537865 : Based on this API usage I have Included ths exception block
864 EXCEPTION
865 WHEN OTHERS THEN
866 x_return_status := 'U';
867 x_error_msg_code := SQLCODE;
868 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
869 p_procedure_name => 'CHECK_CLASS_CATG_ONE_ONLY_CODE',
870 p_error_text => SUBSTRB(SQLERRM,1,240));
871 raise;
872 END CHECK_CLASS_CATG_ONE_ONLY_CODE;
873
874
875 -- API name : check_class_catg_can_override
876 -- Type : Public
877 -- Pre-reqs : None.
878 -- Parameters :
879 -- p_project_id IN NUMBER
880 -- p_class_category IN VARCHAR2
881 -- p_class_code IN VARCHAR2
882 -- x_return_status OUT VARCHAR2
883 -- x_error_msg_code OUT VARCHAR2
884 --
885 -- History
886 --
887 -- 18-AUG-2000 -- Sakthi/William - Created.
888 --
889 --
890 PROCEDURE CHECK_CLASS_CATG_CAN_OVERRIDE (
891 p_project_id NUMBER,
892 p_class_category VARCHAR2,
893 p_class_code VARCHAR2,
894 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
895 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
896 IS
897 CURSOR l_project_template (c_project_id NUMBER)
898 IS
899 SELECT created_from_project_id
900 FROM PA_PROJECTS
901 WHERE project_id = c_project_id;
902
903 CURSOR l_override (c_created_from_project_id NUMBER, c_class_category VARCHAR2)
904 IS
905 SELECT 'x'
906 FROM PA_OVERRIDE_FIELDS_V
907 WHERE pa_source_template_id = c_created_from_project_id
908 AND pa_field_name = 'CLASSIFICATION'
909 AND UPPER(type) = c_class_category;
910 l_dummy VARCHAR2(1);
911 l_created_from_project_id pa_projects.created_from_project_id%TYPE;
912 BEGIN
913 x_return_status := 'S';
914 OPEN l_project_template(p_project_id);
915 FETCH l_project_template INTO l_created_from_project_id;
916 CLOSE l_project_template;
917
918 OPEN l_override (l_created_from_project_id,p_class_category);
919 FETCH l_override INTO l_dummy;
920 IF l_override%NOTFOUND
921 THEN
922 x_error_msg_code:= 'PA_CLASS_CAT_NOT_OVERRIDABLE';
923 x_return_status := 'E';
924 ELSE
925 IF PA_PROJECT_PVT.CHECK_CLASS_CODE_VALID(p_class_category,
926 p_class_code) = 'N'
927 THEN
928 x_error_msg_code:= 'PA_INVALID_CLASS_CATEGORY';
929 x_return_status := 'E';
930 END IF;
931 END IF;
932 CLOSE l_override;
933 -- 4537865 : Based on this API usage I have Included ths exception block
934 EXCEPTION
935 WHEN OTHERS THEN
936 x_return_status := 'U';
937 x_error_msg_code := SQLCODE;
938 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
939 p_procedure_name => 'CHECK_CLASS_CATG_CAN_OVERRIDE',
940 p_error_text => SUBSTRB(SQLERRM,1,240));
941 raise;
942 END CHECK_CLASS_CATG_CAN_OVERRIDE;
943
944 -- API name : check_probability_can_change
945 -- Type : Public
946 -- Pre-reqs : None.
947 -- Parameters :
948 -- p_project_status_code IN VARCHAR2
949 -- x_return_status OUT VARCHAR2
950 -- x_error_msg_code OUT VARCHAR2
951 --
952 -- History
953 --
954 -- 18-AUG-2000 -- Sakthi/William - Created.
955 --
956 --
957 PROCEDURE CHECK_PROBABILITY_CAN_CHANGE (
958 p_project_status_code VARCHAR2,
959 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
960 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
961 IS
962 CURSOR l_project_system_status_csr(c_project_status_code VARCHAR2)
963 IS
964 SELECT project_system_status_code
965 FROM PA_PROJECT_STATUSES
966 WHERE project_status_code = c_project_status_code;
967 l_project_system_Status VARCHAR(250);
968 BEGIN
969 x_return_status := 'S';
970 OPEN l_project_system_status_csr(p_project_status_code);
971 FETCH l_project_system_status_csr into l_project_system_status;
972 CLOSE l_project_system_status_csr;
973 IF PA_PROJECT_UTILS.check_prj_stus_action_allowed
974 (l_project_system_status,'CHANGE_PROJECT_PROBABILITY') <> 'Y'
975 THEN
976 x_error_msg_code:= 'PA_PRJ_PROB_CANNOT_CHNG';
977 --new message
978 x_return_status := 'E' ;
979 END IF;
980 -- 4537865 : Based on this API usage I have Included ths exception block
981 EXCEPTION
982 WHEN OTHERS THEN
983 x_return_status := 'U';
984 x_error_msg_code := SQLCODE;
985 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
986 p_procedure_name => 'CHECK_PROBABILITY_CAN_CHANGE',
987 p_error_text => SUBSTRB(SQLERRM,1,240));
988 raise;
989 END CHECK_PROBABILITY_CAN_CHANGE;
990
991 -- API name : check_bill_job_grp_req
992 -- Type : Public
993 -- Pre-reqs : None.
994 -- Parameters :
995 -- p_project_type IN VARCHAR2
996 -- p_bill_job_group IN NUMBER
997 -- x_return_status OUT VARCHAR2
998 -- x_error_msg_code OUT VARCHAR2
999 --
1000 -- History
1001 --
1002 -- 18-AUG-2000 -- Sakthi/William - Created.
1003 --
1004 --
1005 PROCEDURE CHECK_BILL_JOB_GRP_REQ( p_project_type IN VARCHAR2,
1006 p_bill_job_group_id IN NUMBER,
1007 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1008 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1009 IS
1010 CURSOR l_project_type_csr
1011 (l_project_type VARCHAR2)
1012 IS
1013 SELECT project_type_class_code
1014 FROM pa_project_types
1015 WHERE project_type = l_project_type;
1016
1017 l_project_type_class_code pa_project_types.project_type_class_code%TYPE;
1018 BEGIN
1019 x_return_status := 'S';
1020 OPEN l_project_type_csr(p_project_type);
1021 FETCH l_project_type_csr INTO l_project_type_class_code;
1022 CLOSE l_project_type_csr;
1023 IF l_project_type_class_code = 'CONTRACT' AND
1024 (p_bill_job_group_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM OR
1025 p_bill_job_group_id = NULL )
1026 THEN
1027 x_error_msg_code := 'PA_BILL_JOB_GROUP_NOT_NULL';
1028 --new message
1029 x_return_status := 'E';
1030 END IF;
1031 -- 4537865 : Based on this API usage I have Included ths exception block
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 x_return_status := 'U';
1035 x_error_msg_code := SQLCODE;
1036 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
1037 p_procedure_name => 'CHECK_BILL_JOB_GRP_REQ',
1038 p_error_text => SUBSTRB(SQLERRM,1,240));
1039 raise;
1040 END CHECK_BILL_JOB_GRP_REQ;
1041
1042 -- API name : get_cost_job_group_id
1043 -- Type : Public
1044 -- Pre-reqs : None.
1045 -- Parameters : None.
1046 -- Return : NUMBER
1047 --
1048 -- History
1049 --
1050 -- 18-AUG-2000 -- Sakthi/William - Created.
1051 --
1052 --
1053 FUNCTION GET_COST_JOB_GROUP_ID RETURN NUMBER IS
1054
1055 Cursor implementation_csr is
1056 select business_group_id,
1057 org_id
1058 from pa_implementations;
1059
1060 Cursor job_group_csr (p_business_group_id number) is
1061 select jobs.job_group_id
1062 from per_job_groups jobs
1063 where jobs.business_group_id = p_business_group_id
1064 and jobs.internal_name = 'HR_'||to_char(jobs.business_group_id);
1065
1066 t_business_group_id NUMBER(15);
1067 t_org_id NUMBER(15);
1068 t_job_group_id NUMBER(25);
1069
1070 BEGIN
1071
1072 open implementation_csr;
1073 fetch implementation_csr into t_business_group_id, t_org_id;
1074 close implementation_csr;
1075
1076 open job_group_csr (t_business_group_id);
1077 fetch job_group_csr into t_job_group_id;
1078 close job_group_csr;
1079 Return t_job_group_id;
1080 END GET_COST_JOB_GROUP_ID;
1081
1082 -- API name : check_bill_rate_rate_schl_exists
1083 -- Type : Public
1084 -- Pre-reqs : None.
1085 -- Parameters :
1086 -- p_project_id IN NUMBER
1087 --
1088 -- History
1089 --
1090 -- 08-SEP-2000 -- Sakthi/William - Created.
1091 --
1092 FUNCTION CHECK_BILL_RATE_SCHL_EXISTS (p_project_id IN NUMBER)
1093 RETURN VARCHAR2 IS
1094
1095 Cursor c1 (c_project_id NUMBER)
1096 Is
1097 Select bill_job_group_id,
1098 labor_std_bill_rate_schdl,
1099 non_labor_std_bill_rate_schdl
1100 from pa_projects
1101 where project_id = c_project_id;
1102
1103 Cursor c2 (c_bill_job_group_id NUMBER,
1104 c_lab_std_bill_rate_schdl VARCHAR2,
1105 c_non_lab_std_bill_rate_schdl VARCHAR2)
1106 IS
1107 SELECT 'Y' FROM pa_std_bill_rate_schedules
1108 WHERE SCHEDULE_TYPE = 'JOB'
1109 AND JOB_GROUP_ID = c_bill_job_group_id
1110 AND (STD_BILL_RATE_SCHEDULE = c_lab_std_bill_rate_schdl
1111 or STD_BILL_RATE_SCHEDULE = c_non_lab_std_bill_rate_schdl);
1112
1113 l_bill_job_group_id pa_projects.bill_job_group_id%TYPE;
1114 l_lab_std_bill_rate_schdl pa_projects.labor_std_bill_rate_schdl%TYPE;
1115 l_non_lab_std_bill_rate_schdl pa_projects.non_labor_std_bill_rate_schdl%TYPE;
1116 l_return VARCHAR2(1) :='N';
1117 BEGIN
1118 OPEN c1 (p_project_id);
1119 FETCH c1 INTO l_bill_job_group_id,l_lab_std_bill_rate_schdl,
1120 l_non_lab_std_bill_rate_schdl;
1121 CLOSE c1;
1122 IF l_bill_job_group_id is not null and
1123 ( l_lab_std_bill_rate_schdl is not null or
1124 l_non_lab_std_bill_rate_schdl is not null)
1125 Then
1126 OPEN c2(l_bill_job_group_id,l_lab_std_bill_rate_schdl,
1127 l_non_lab_std_bill_rate_schdl);
1128 FETCH c2 INTO l_return;
1129 IF c2%NOTFOUND then
1130 l_return := 'N';
1131 ELSE
1132 l_return := 'Y';
1133 END IF;
1134 CLOSE c2;
1135 End If;
1136 Return l_return;
1137 END CHECK_BILL_RATE_SCHL_EXISTS;
1138
1139 -- API name : check_project_option_exists
1140 -- Type : Public
1141 -- Pre-reqs : None.
1142 -- Parameters :
1143 -- p_project_id IN NUMBER
1144 -- p_option_code IN VARCHAR2
1145 -- Return : VARCHAR2
1146 --
1147 -- History
1148 --
1149 -- 01-JUN-2001 -- Sakthi - Created.
1150 --
1151 --
1152 FUNCTION CHECK_PROJECT_OPTION_EXISTS( p_project_id NUMBER,
1153 p_option_code VARCHAR2)
1154 RETURN VARCHAR2 IS
1155 CURSOR l_project_template (c_project_id NUMBER)
1156 IS
1157 SELECT created_from_project_id
1158 FROM PA_PROJECTS_ALL
1159 WHERE project_id = p_project_id;
1160
1161 CURSOR l_project_option (p_template_id NUMBER, p_option_code VARCHAR2)
1162 IS
1163 SELECT 'x'
1164 FROM PA_OPTIONS OPT1, PA_PROJECT_OPTIONS OPT2
1165 /* Commented for Bug 2499051
1166 , PA_PROJECTS_ALL PROJ
1167 */
1168 WHERE opt1.option_code = opt2.option_code
1169 AND opt1.OPTION_FUNCTION_NAME = p_option_code
1170 AND opt2.project_id = p_template_id;
1171
1172 l_dummy VARCHAR2(1);
1173 x_return_status VARCHAR2(1);
1174 l_created_from_project_id pa_projects.created_from_project_id%TYPE;
1175
1176 BEGIN
1177
1178 x_return_status := 'S';
1179
1180 OPEN l_project_template (p_project_id);
1181 FETCH l_project_template INTO l_created_from_project_id;
1182 CLOSE l_project_template;
1183
1184 OPEN l_project_option (l_created_from_project_id, p_option_code);
1185 FETCH l_project_option INTO l_dummy;
1186 CLOSE l_project_option;
1187
1188 IF l_dummy = 'x' then
1189 x_return_status := 'S';
1190 ELSE
1191 x_return_status := 'E';
1192 END IF;
1193
1194 RETURN (x_return_status);
1195
1196 END CHECK_PROJECT_OPTION_EXISTS;
1197
1198
1199 -- API name : check_category_total_valid
1200 -- Type : Public
1201 -- Pre-reqs : None.
1202 -- Parameters :
1203 -- p_object_id IN NUMBER
1204 -- p_object_type IN VARCHAR2
1205 -- p_class_category IN VARCHAR2
1206 -- p_rowid IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
1207 -- p_code_percentage IN NUMBER
1208 -- x_return_status OUT VARCHAR2
1209 -- x_error_msg_code OUT VARCHAR2
1210 --
1211 -- History
1212 --
1213 -- 11-OCT-2001 -- anlee created
1214 --
1215 --
1216 PROCEDURE CHECK_CATEGORY_TOTAL_VALID (p_object_id NUMBER,
1217 p_object_type VARCHAR2,
1218 p_class_category VARCHAR2,
1219 p_rowid VARCHAR2 := FND_API.G_MISS_CHAR,
1220 p_code_percentage NUMBER,
1221 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1222 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1223 IS
1224
1225 l_sum NUMBER;
1226 l_old_percentage NUMBER;
1227 l_old_category VARCHAR2(30);
1228
1229 CURSOR get_sum
1230 IS
1231 SELECT sum(pc.code_percentage)
1232 FROM PA_PROJECT_CLASSES pc
1233 WHERE pc.object_id = p_object_id
1234 AND pc.object_type = p_object_type
1235 AND pc.class_category = p_class_category;
1236
1237 CURSOR get_old_percentage
1238 IS
1239 SELECT pc.class_category, pc.code_percentage
1240 FROM PA_PROJECT_CLASSES pc
1241 WHERE rowid = p_rowid;
1242
1243 BEGIN
1244 x_return_status := 'S';
1245 l_sum := 0;
1246
1247 OPEN get_sum;
1248 FETCH get_sum INTO l_sum;
1249 CLOSE get_sum;
1250
1251 if((p_rowid is not null) AND (p_rowid <> FND_API.G_MISS_CHAR)) then
1252 OPEN get_old_percentage;
1253 FETCH get_old_percentage INTO l_old_category, l_old_percentage;
1254 CLOSE get_old_percentage;
1255
1256 if l_old_category = p_class_category then
1257 if l_old_percentage is not null then
1258 l_sum := l_sum - l_old_percentage;
1259 end if;
1260 end if;
1261 end if;
1262
1263 if p_code_percentage is not null then
1264 l_sum := l_sum + p_code_percentage;
1265 end if;
1266
1267 if((l_sum < 0) OR (l_sum > 100)) then
1268 x_return_status := 'E';
1269 x_error_msg_code := 'PA_CLASS_CATG_TOTAL_INVALID';
1270 end if;
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 x_return_status := 'U';
1274 x_error_msg_code := SQLCODE ; -- 4537865
1275 raise;
1276 END CHECK_CATEGORY_TOTAL_VALID;
1277
1278
1279 -- API name : check_category_valid
1280 -- Type : Public
1281 -- Pre-reqs : None.
1282 -- Parameters :
1283 -- p_object_type_id IN NUMBER
1284 -- p_class_category IN VARCHAR2
1285 -- x_return_status OUT VARCHAR2
1286 -- x_error_msg_code OUT VARCHAR2
1287 --
1288 -- History
1289 --
1290 -- 11-OCT-2001 -- anlee created
1291 --
1292 --
1293 PROCEDURE CHECK_CATEGORY_VALID (p_object_type_id NUMBER,
1294 p_class_category VARCHAR2,
1295 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1296 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1297 IS
1298
1299 CURSOR check_valid_catg_csr(c_class_category VARCHAR2, c_object_type_id NUMBER)
1300 IS
1301 SELECT 'Y'
1302 FROM PA_VALID_CATEGORIES_V
1303 WHERE class_category = c_class_category
1304 AND object_type_id = c_object_type_id;
1305
1306 l_dummy VARCHAR2(1);
1307 BEGIN
1308 x_return_status := 'S';
1309
1310 OPEN check_valid_catg_csr(p_class_category, p_object_type_id);
1311 FETCH check_valid_catg_csr INTO l_dummy;
1312 if check_valid_catg_csr%NOTFOUND then
1313 x_return_status := 'E';
1314 x_error_msg_code := 'PA_CLASS_CATG_INVALID';
1315 end if;
1316 CLOSE check_valid_catg_csr; -- Added for Bug#3876212
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 x_return_status := 'U';
1320 x_error_msg_code := SQLCODE; -- 4537865
1321 raise;
1322 END CHECK_CATEGORY_VALID;
1323
1324
1325 -- API name : check_percentage_allowed
1326 -- Type : Public
1327 -- Pre-reqs : None.
1328 -- Parameters :
1329 -- p_class_category IN VARCHAR2
1330 -- x_return_status OUT VARCHAR2
1331 -- x_error_msg_code OUT VARCHAR2
1332 --
1333 -- History
1334 --
1335 -- 11-OCT-2001 -- anlee created
1336 --
1337 --
1338 PROCEDURE CHECK_PERCENTAGE_ALLOWED (p_class_category VARCHAR2,
1339 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1340 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1341 IS
1342
1343 l_allow_percent_flag VARCHAR2(1);
1344
1345 BEGIN
1346 x_return_status := 'S';
1347
1348 SELECT allow_percent_flag
1349 INTO l_allow_percent_flag
1350 FROM pa_class_categories
1351 WHERE class_category = p_class_category;
1352
1353 if l_allow_percent_flag = 'N' then
1354 x_return_status := 'E';
1355 x_error_msg_code := 'PA_CODE_PERCENT_NOT_ALLOWED';
1356 end if;
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 x_return_status := 'U';
1360 x_error_msg_code := SQLCODE ; -- 4537865
1361 raise;
1362 END CHECK_PERCENTAGE_ALLOWED;
1363
1364
1365 -- API name : check_mandatory_classes
1366 -- Type : Public
1367 -- Pre-reqs : None.
1368 -- Parameters :
1369 -- p_class_category IN VARCHAR2
1370 -- x_return_status OUT VARCHAR2
1371 -- x_error_msg_code OUT VARCHAR2
1372 --
1373 -- History
1374 --
1375 -- 11-OCT-2001 -- anlee created
1376 --
1377 --
1378 PROCEDURE CHECK_MANDATORY_CLASSES (p_object_id VARCHAR2,
1379 p_object_type VARCHAR2,
1380 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1381 x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1382 IS
1383
1384 CURSOR C1(c_object_id NUMBER, c_object_type VARCHAR2)
1385 IS
1386 SELECT pcc.class_category, mandatory_flag
1387 FROM pa_class_categories pcc,
1388 pa_project_classes ppc
1389 WHERE pcc.class_category <> ppc.class_category
1390 AND ppc.object_id = c_object_id
1391 AND ppc.object_type = c_object_type
1392 AND trunc(sysdate) between trunc(pcc.start_date_active)
1393 and trunc(nvl(pcc.end_date_active, sysdate));
1394
1395 CURSOR C2(c_object_id NUMBER, c_class_category VARCHAR2)
1396 IS
1397 SELECT 1
1398 FROM PA_VALID_CATEGORIES vc,
1399 PA_PROJECTS_ALL ppa,
1400 PA_PROJECT_TYPES_ALL ppta
1401 WHERE vc.mandatory_flag = 'Y'
1402 AND vc.class_category = c_class_category
1403 AND ppa.project_id = p_object_id
1404 AND ppa.project_type = ppta.project_type
1405 AND ppa.org_id = ppta.org_id --MOAC Changes: Bug 4363092: removed nvl usage with org_id
1406 AND vc.object_type_id = ppta.project_type_id
1407 AND trunc(sysdate) between trunc(ppta.start_date_active)
1408 and trunc(nvl(ppta.end_date_active, sysdate));
1409
1410 l_class_category pa_class_categories.class_category%TYPE;
1411 l_dummy NUMBER;
1412 l_mandatory_flag VARCHAR2(1);
1413 BEGIN
1414 x_return_status := 'S';
1415
1416 OPEN C1(p_object_id, p_object_type);
1417 LOOP
1418 FETCH C1 INTO l_class_category, l_mandatory_flag;
1419 EXIT WHEN C1%NOTFOUND;
1420
1421 if(p_object_type = 'PA_PROJECTS') then
1422 OPEN C2(p_object_id, l_class_category);
1423 FETCH C2 INTO l_dummy;
1424 if C2%FOUND then
1425 x_return_status := 'E';
1426 x_error_msg_code := 'PA_MANDATORY_CATG_REQD';
1427 CLOSE C1; -- Added for Bug#3876212
1428 CLOSE C2; -- Added for Bug#3876212
1429 return;
1430 elsif l_mandatory_flag = 'Y' then
1431 x_return_status := 'E';
1432 x_error_msg_code := 'PA_MANDATORY_CATG_REQD';
1433 CLOSE C1; -- Added for Bug#3876212
1434 CLOSE C2; -- Added for Bug#3876212
1435 return;
1436 end if;
1437 CLOSE C2; -- Added for Bug#3876212
1438 end if;
1439 end LOOP;
1440 CLOSE C1; -- Added for Bug#3876212
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 x_return_status := 'U';
1444 -- 4537865
1445 x_error_msg_code := SQLCODE ;
1446 raise;
1447 END CHECK_MANDATORY_CLASSES;
1448
1449
1450 -- API name : check_currency_name_or_code
1451 -- Type : Public
1452 -- Pre-reqs : None.
1453 -- Parameters :
1454 -- p_agreement_currency IN FND_CURRENCIES_VL.currency_code%TYPE Required
1455 -- p_agreement_currency_name IN FND_CURRENCIES_VL.name%TYPE Required
1456 -- p_check_id_flag IN VARCHAR2 Required
1457 -- x_agreement_currency OUT VARCHAR2 Required
1458 -- x_return_status OUT VARCHAR2 Required
1459 -- x_error_msg_code OUT VARCHAR2 Required
1460 --
1461 --
1462 -- History
1463 --
1464 -- 12-OCT-2001 -- anlee created
1465 -- 01-MAR-2002 -- MAansari Modified SQL to include start_date_active, end_date_active
1466 -- and enabled_flag in the where clause.
1467 --
1468 --
1469 procedure Check_currency_name_or_code
1470 ( p_agreement_currency IN FND_CURRENCIES_VL.currency_code%TYPE
1471 ,p_agreement_currency_name IN FND_CURRENCIES_VL.name%TYPE
1472 ,p_check_id_flag IN VARCHAR2
1473 ,x_agreement_currency OUT NOCOPY FND_CURRENCIES_VL.currency_code%TYPE --File.Sql.39 bug 4440895
1474 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1475 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1476 IS
1477 BEGIN
1478 IF (p_agreement_currency IS NOT NULL) THEN
1479 IF (p_check_id_flag = 'Y') THEN
1480 SELECT currency_code
1481 INTO x_agreement_currency
1482 FROM fnd_currencies --Used base table:Bug 4352162
1483 WHERE currency_code = p_agreement_currency
1484 AND nvl(enabled_flag, 'Y') = 'Y'
1485 AND sysdate between decode(start_date_active, null, sysdate, start_date_active)
1486 AND decode (end_date_active, null, sysdate, end_date_active);
1487 ELSE
1488 x_agreement_currency := p_agreement_currency;
1489 END IF;
1490 ELSE
1491 SELECT currency_code
1492 INTO x_agreement_currency
1493 FROM fnd_currencies_vl
1494 WHERE name = p_agreement_currency_name
1495 AND nvl(enabled_flag, 'Y') = 'Y'
1496 AND sysdate between decode(start_date_active, null, sysdate, start_date_active)
1497 AND decode (end_date_active, null, sysdate, end_date_active);
1498 END IF;
1499 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1500 EXCEPTION
1501 WHEN no_data_found THEN
1502 x_return_status:= FND_API.G_RET_STS_ERROR;
1503 x_error_msg_code:= 'PA_AGREEMENT_CURR_INVALID';
1504 -- 4537865
1505 x_agreement_currency := NULL ;
1506
1507 WHEN too_many_rows THEN
1508 x_return_status:= FND_API.G_RET_STS_ERROR;
1509 x_error_msg_code:= 'PA_CURR_NAME_NOT UNIQUE';
1510 -- 4537865
1511 x_agreement_currency := NULL ;
1512
1513 WHEN OTHERS THEN
1514 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1515 -- 4537865
1516 x_agreement_currency := NULL ;
1517 x_error_msg_code:= SQLCODE ;
1518
1519 RAISE;
1520 END Check_currency_name_or_code;
1521
1522
1523 -- API name : check_agreement_org_name_or_id
1524 -- Type : Public
1525 -- Pre-reqs : None.
1526 -- Parameters :
1527 -- p_agreement_org_id IN pa_organizations_project_v.organization_id%TYPE Required
1528 -- p_agreement_org_name IN pa_organizations_project_v.name%TYPE Required
1529 -- p_check_id_flag IN VARCHAR2 Required
1530 -- x_agreement_org_id OUT NUMBER Required
1531 -- x_return_status OUT VARCHAR2 Required
1532 -- x_error_msg_code OUT VARCHAR2 Required
1533 --
1534 --
1535 -- History
1536 --
1537 -- 12-OCT-2001 -- anlee created
1538 --
1539 --
1540 procedure Check_agreement_org_name_or_id
1541 ( p_agreement_org_id IN pa_organizations_project_v.organization_id%TYPE
1542 ,p_agreement_org_name IN pa_organizations_project_v.name%TYPE
1543 ,p_check_id_flag IN VARCHAR2
1544 ,x_agreement_org_id OUT NOCOPY pa_organizations_project_v.organization_id%TYPE --File.Sql.39 bug 4440895
1545 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1546 ,x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1547 IS
1548 BEGIN
1549 IF (p_agreement_org_id IS NOT NULL) THEN
1550 IF (p_check_id_flag = 'Y') THEN
1551 SELECT organization_id
1552 INTO x_agreement_org_id
1553 FROM pa_organizations_project_v
1554 WHERE organization_id = p_agreement_org_id;
1555 ELSE
1556 x_agreement_org_id := p_agreement_org_id;
1557 END IF;
1558 ELSE
1559 SELECT organization_id
1560 INTO x_agreement_org_id
1561 FROM pa_organizations_project_v
1562 WHERE name = p_agreement_org_name;
1563 END IF;
1564 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1565 EXCEPTION
1566 WHEN no_data_found THEN
1567 x_return_status:= FND_API.G_RET_STS_ERROR;
1568 x_error_msg_code:= 'PA_INVALID_ORG';
1569 -- 4537865
1570 x_agreement_org_id := NULL ;
1571
1572 WHEN too_many_rows THEN
1573 x_return_status:= FND_API.G_RET_STS_ERROR;
1574 x_error_msg_code:= 'PA_ORG_NOT UNIQUE';
1575 -- 4537865
1576 x_agreement_org_id := NULL ;
1577 WHEN OTHERS THEN
1578 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1579 -- 4537865
1580 x_agreement_org_id := NULL ;
1581 x_error_msg_code:= SQLCODE ;
1582
1583 RAISE;
1584 END Check_agreement_org_name_or_id;
1585
1586
1587 -- API name : get_class_codes
1588 -- Type : Public
1589 -- Pre-reqs : None.
1590 -- Parameters :
1591 -- p_object_id IN NUMBER
1592 -- p_object_type IN VARCHAR2
1593 -- p_class_category IN VARCHAR2
1594 -- Return : VARCHAR2
1595 --
1596 -- History
1597 --
1598 -- 18-OCT-2001 -- anlee - Created
1599 --
1600 --
1601 FUNCTION GET_CLASS_CODES(p_object_id IN NUMBER, p_object_type IN VARCHAR2, p_class_category IN VARCHAR2)
1602 RETURN VARCHAR2
1603 IS
1604 CURSOR C1(c_object_id NUMBER, c_object_type VARCHAR2, c_class_category VARCHAR2)
1605 IS
1606 SELECT class_code, code_percentage
1607 FROM PA_PROJECT_CLASSES
1608 WHERE object_id = c_object_id
1609 AND object_type = c_object_type
1610 AND class_category = c_class_category
1611 ORDER BY class_code;
1612
1613 l_return_value VARCHAR2(4000);
1614 l_class_code VARCHAR2(30);
1615 l_code_percentage NUMBER;
1616 BEGIN
1617 l_return_value := null;
1618 OPEN C1(p_object_id, p_object_type, p_class_category);
1619 LOOP
1620 FETCH C1 INTO l_class_code, l_code_percentage;
1621 EXIT WHEN C1%NOTFOUND;
1622
1623 if l_return_value is not null then
1624 if l_code_percentage is null then
1625 l_return_value := l_return_value || ' <BR>' || l_class_code;
1626 else
1627 l_return_value := l_return_value || ' <BR>' || l_class_code || ' (' || to_char(l_code_percentage) || '%)';
1628 end if;
1629 else
1630 if l_code_percentage is null then
1631 l_return_value := l_class_code;
1632 else
1633 l_return_value := l_class_code || ' (' || to_char(l_code_percentage) || '%)';
1634 end if;
1635 end if;
1636 END LOOP;
1637 CLOSE C1;
1638
1639 RETURN l_return_value;
1640
1641 END GET_CLASS_CODES;
1642
1643
1644 -- API name : get_class_exceptions
1645 -- Type : Public
1646 -- Pre-reqs : None.
1647 -- Parameters :
1648 -- p_object_id IN NUMBER
1649 -- p_object_type IN VARCHAR2
1650 -- p_class_category IN VARCHAR2
1651 -- p_mandatory IN VARCHAR2
1652 -- Return : VARCHAR2
1653 --
1654 -- History
1655 --
1656 -- 18-OCT-2001 -- anlee - Created
1657 --
1658 --
1659 FUNCTION GET_CLASS_EXCEPTIONS(p_object_id IN NUMBER, p_object_type IN VARCHAR2, p_class_category IN VARCHAR2, p_mandatory IN VARCHAR2)
1660 RETURN VARCHAR2
1661 IS
1662 CURSOR C1
1663 IS
1664 SELECT sum(code_percentage)
1665 FROM PA_PROJECT_CLASSES
1666 WHERE object_id = p_object_id
1667 AND object_type = p_object_type
1668 AND class_category = p_class_category;
1669
1670 -- 3690967 For performance bug changed below cursor select query
1671 -- removed below cursor definition
1672
1673 /*
1674 CURSOR C2
1675 IS
1676 SELECT total_100_percent_flag
1677 FROM PA_VALID_CATEGORIES_V vc,
1678 PA_PROJECTS_ALL ppa,
1679 PA_PROJECT_TYPES_ALL ppta
1680 WHERE ppa.project_id = p_object_id
1681 AND ppa.project_type = ppta.project_type
1682 AND nvl(ppa.org_id, -99) = nvl(ppta.org_id, -99)
1683 AND vc.object_type_id = ppta.project_type_id
1684 AND vc.class_category = p_class_category;
1685 */
1686
1687 -- 3690967 added below select for above cursor
1688
1689 CURSOR C2
1690 IS
1691 SELECT total_100_percent_flag
1692 FROM PA_CLASS_CATEGORIES
1693 WHERE object_type = p_object_type
1694 AND class_category = p_class_category;
1695
1696 -- 3690967 end
1697
1698 l_total_percentage NUMBER;
1699 l_return_value VARCHAR2(4000);
1700 l_total_100_percent_flag VARCHAR2(1);
1701 BEGIN
1702
1703 if p_mandatory = 'Y' then
1704 FND_MESSAGE.set_name('PA', 'PA_MANDATORY_CATG_REQD');
1705 l_return_value := FND_MESSAGE.GET;
1706 return l_return_value;
1707 end if;
1708
1709 OPEN C1;
1710 FETCH C1 INTO l_total_percentage;
1711 CLOSE C1;
1712
1713 OPEN C2;
1714 FETCH C2 INTO l_total_100_percent_flag;
1715 CLOSE C2;
1716
1717 l_return_value := NULL;
1718 if l_total_100_percent_flag = 'Y' then
1719 if l_total_percentage <> 100 then
1720 FND_MESSAGE.set_name('PA', 'PA_CLASS_CATG_NOT_100');
1721 l_return_value := FND_MESSAGE.GET;
1722 return l_return_value;
1723 end if;
1724 end if;
1725
1726 return l_return_value;
1727 END GET_CLASS_EXCEPTIONS;
1728
1729
1730 -- API name : get_object_type_id
1731 -- Type : Public
1732 -- Pre-reqs : None.
1733 -- Parameters :
1734 -- p_object_id IN NUMBER
1735 -- p_object_type IN VARCHAR2
1736 -- Return : NUMBER
1737 --
1738 -- History
1739 --
1740 -- 18-OCT-2001 -- anlee - Created
1741 --
1742 --
1743 FUNCTION GET_OBJECT_TYPE_ID(p_object_id IN NUMBER, p_object_type IN VARCHAR2)
1744 RETURN NUMBER
1745 IS
1746 CURSOR C1
1747 IS
1748 SELECT ppta.project_type_id
1749 FROM PA_PROJECT_TYPES_ALL ppta,
1750 PA_PROJECTS_ALL ppa
1751 WHERE ppa.project_id = p_object_id
1752 AND ppa.project_type = ppta.project_type
1753 AND ppa.org_id = ppta.org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
1754
1755 l_object_type_id NUMBER;
1756 BEGIN
1757 if p_object_type = 'PA_PROJECTS' then
1758 OPEN C1;
1759 FETCH C1 INTO l_object_type_id;
1760 CLOSE C1;
1761 end if;
1762 return l_object_type_id;
1763 END GET_OBJECT_TYPE_ID;
1764
1765
1766 -- API name : populate_class_exception
1767 -- Type : Public
1768 -- Pre-reqs : None.
1769 -- Parameters :
1770 -- p_project : IN NUMBER
1771 -- Return : NUMBER
1772 --
1773 -- History
1774 --
1775 -- 16-NOV-2001 -- Sakthi/Ansari - Created
1776 --
1777 --
1778
1779 PROCEDURE POPULATE_CLASS_EXCEPTION (p_project_id NUMBER) IS
1780
1781 l_exception VARCHAR2(4000); /* 3102753-Modified length for variable from 2500 to 4000 */
1782
1783 CURSOR l_message_name IS
1784 SELECT message_name
1785 FROM FND_NEW_MESSAGES
1786 WHERE message_text = l_exception
1787 /* added for Bug 2634995 */
1788 AND application_id = fnd_global.resp_appl_id
1789 AND language_code = userenv('LANG');
1790 /* Bug 2634995 ends */
1791 CURSOR l_class_exception IS
1792 SELECT CLASS_CATEGORY, CLASS_CODES, TOTAL_PERCENTAGE, EXCEPTIONS, SORT_ORDER
1793 FROM pa_project_class_totals_v
1794 WHERE project_id = p_project_id
1795 AND SORT_ORDER IN ('A','B')
1796 ORDER BY sort_order;
1797
1798 /* 3102753 - Modified the length of the variables. Made l_class_category 30 from 50, l_class_codes 4000 from 50
1799 and l_total_percentage Number from Number(10,2) */
1800
1801 l_class_category VARCHAR2(30);
1802 l_class_codes VARCHAR2(4000);
1803 l_total_percentage NUMBER; /* Modified to NUMBER and commented NUMBER(10,2) for bug 3102753 */
1804 l_message_code VARCHAR2(30);
1805 l_sort_order VARCHAR2(1);
1806
1807 BEGIN
1808 OPEN l_class_exception;
1809 LOOP
1810 FETCH l_class_exception INTO l_class_category,
1811 l_class_codes,
1812 l_total_percentage,
1813 l_exception,
1814 l_sort_order;
1815 EXIT WHEN l_class_exception%NOTFOUND;
1816
1817 if l_sort_order = 'A' then
1818 FND_MESSAGE.SET_NAME('PA', 'PA_TOT_PERCENT_MISSING');
1819 FND_MESSAGE.SET_TOKEN('CLASS_CATEGORY', l_class_category);
1820 FND_MESSAGE.SET_TOKEN('TOTAL_PERCENTAGE', to_char(l_total_percentage));
1821 FND_MSG_PUB.ADD;
1822 elsif l_sort_order = 'B' then
1823 FND_MESSAGE.SET_NAME('PA', 'PA_MANDATORY_CLASS_CATEGORY');
1824 FND_MESSAGE.SET_TOKEN('CLASS_CATEGORY', l_class_category);
1825 FND_MSG_PUB.ADD;
1826 end if;
1827
1828 END LOOP;
1829 CLOSE l_class_exception; -- Added for Bug#3876212
1830 END POPULATE_CLASS_EXCEPTION;
1831
1832
1833 -- API name : check_proj_recalc
1834 -- Type : Public
1835 -- Pre-reqs : None.
1836 -- Parameters :
1837 -- p_project_id IN NUMBER
1838 -- p_organization_id IN NUMBER
1839 -- p_organization_name IN VARCHAR2
1840 -- Return : VARCHAR2
1841 --
1842 -- History
1843 --
1844 -- 22-MAY-2002 -- anlee - Created
1845 --
1846 --
1847 FUNCTION CHECK_PROJ_RECALC (p_project_id IN NUMBER,
1848 p_organization_id IN NUMBER,
1849 p_organization_name IN VARCHAR2)
1850 RETURN VARCHAR2
1851 IS
1852
1853 cursor cdl_exist_csr
1854 IS
1855 SELECT count(*)
1856 FROM sys.dual
1857 WHERE EXISTS
1858 (SELECT NULL
1859 FROM pa_expenditure_items_all pai,
1860 pa_tasks t,
1861 pa_cost_distribution_lines_all pcd
1862 WHERE pai.task_id = t.task_id
1863 AND pai.expenditure_item_id = pcd.expenditure_item_id
1864 AND t.project_id = p_project_id);
1865
1866 cursor template_csr
1867 IS
1868 SELECT template_flag
1869 FROM pa_projects_all
1870 WHERE project_id = p_project_id;
1871
1872 cursor org_csr
1873 IS
1874 SELECT carrying_out_organization_id
1875 FROM PA_PROJECTS_ALL
1876 WHERE project_id = p_project_id;
1877
1878 l_cdl_exist NUMBER;
1879 l_template_flag VARCHAR2(1);
1880 l_organization_id NUMBER;
1881 l_old_organization_id NUMBER;
1882 l_return_status VARCHAR2(1);
1883 l_error_msg_code VARCHAR2(250);
1884 BEGIN
1885
1886 -- First check if the organization has changed
1887 IF (p_organization_id is not null AND p_organization_id <> FND_API.G_MISS_NUM) OR
1888 (p_organization_name is not null AND p_organization_name <> FND_API.G_MISS_CHAR) THEN
1889
1890 pa_hr_org_utils.Check_OrgName_Or_Id
1891 (p_organization_id => p_organization_id
1892 ,p_organization_name => p_organization_name
1893 ,p_check_id_flag => 'A'
1894 ,x_organization_id => l_organization_id
1895 ,x_return_status => l_return_status
1896 ,x_error_msg_code => l_error_msg_code);
1897
1898 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1899 return 'N';
1900 END IF;
1901 END IF;
1902
1903 OPEN org_csr;
1904 FETCH org_csr INTO l_old_organization_id;
1905 CLOSE org_csr;
1906
1907 if l_old_organization_id <> l_organization_id then
1908 OPEN cdl_exist_csr;
1909 FETCH cdl_exist_csr INTO l_cdl_exist;
1910 CLOSE cdl_exist_csr;
1911
1912 if l_cdl_exist > 0 then
1913 OPEN template_csr;
1914 FETCH template_csr INTO l_template_flag;
1915 CLOSE template_csr;
1916
1917 if fnd_function.test('PA_PAXTRAPE_ADJ_RECALC_CST_REV') AND (l_template_flag = 'N') then
1918 -- recalc
1919 return 'Y';
1920 end if;
1921 end if;
1922 end if;
1923
1924 return 'N';
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 return 'N';
1929 END CHECK_PROJ_RECALC;
1930
1931
1932 -- API name : validate_pipeline_info
1933 -- Type : Public
1934 -- Pre-reqs : None.
1935 -- Parameters :
1936 -- p_project_id IN NUMBER
1937 -- Return : VARCHAR2
1938 --
1939 -- History
1940 --
1941 -- 26-JUN-2002 -- anlee - Created
1942 --
1943 --
1944 FUNCTION VALIDATE_PIPELINE_INFO (p_project_id IN NUMBER)
1945 RETURN VARCHAR2
1946 IS
1947 CURSOR C1 IS
1948 SELECT probability_member_id, expected_approval_date
1949 FROM PA_PROJECTS_ALL
1950 WHERE project_id = p_project_id;
1951
1952 l_probability_member_id NUMBER;
1953 l_expected_approval_date DATE;
1954 BEGIN
1955
1956 OPEN C1;
1957 FETCH C1 INTO l_probability_member_id, l_expected_approval_date;
1958 CLOSE C1;
1959
1960 IF l_probability_member_id is not null and l_expected_approval_date is null THEN
1961 return 'PA_EXP_APP_DATE_REQUIRED';
1962 END IF;
1963
1964 IF l_probability_member_id is null and l_expected_approval_date is not null THEN
1965 return 'PA_PROBA_PERCENT_REQUIRED';
1966 END IF;
1967
1968 return NULL;
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 return NULL;
1972 END VALIDATE_PIPELINE_INFO;
1973
1974
1975 -- API name : check_classcode_name_or_id
1976 -- Type : Public
1977 -- Pre-reqs : None.
1978 -- Description : It validates and returns the class code id
1979 -- from the class code and class category combination
1980 -- History
1981 --
1982 -- 20-Nov-2002 -- adabdull - Created
1983
1984 PROCEDURE Check_ClassCode_Name_Or_Id(
1985 p_classcode_id IN pa_class_codes.class_code_id%TYPE
1986 ,p_classcode_name IN pa_class_codes.class_code%TYPE
1987 ,p_classcategory IN pa_class_codes.class_category%TYPE
1988 ,p_check_id_flag IN VARCHAR2
1989 ,x_classcode_id OUT NOCOPY pa_class_codes.class_code_id%TYPE --File.Sql.39 bug 4440895
1990 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1991 ,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1992 IS
1993
1994 CURSOR c_ids IS
1995 SELECT class_code_id
1996 FROM pa_class_codes
1997 WHERE class_category = p_classcategory
1998 AND class_code = p_classcode_name;
1999
2000 l_current_id NUMBER := NULL;
2001 l_num_ids NUMBER := 0;
2002 l_id_found_flag VARCHAR(1) := 'N';
2003
2004 BEGIN
2005
2006 pa_debug.init_err_stack ('pa_projects_maint_utils.Check_ClassCode_Name_Or_Id');
2007
2008 IF p_classcode_id IS NOT NULL AND p_classcode_id <> FND_API.G_MISS_NUM THEN
2009
2010 IF p_check_id_flag = 'Y' THEN
2011 SELECT class_code_id
2012 INTO x_classcode_id
2013 FROM pa_class_codes
2014 WHERE class_category = p_classcategory
2015 AND class_code_id = p_classcode_id;
2016
2017 ELSIF (p_check_id_flag = 'N') then
2018 x_classcode_id := p_classcode_id;
2019
2020 ELSIF (p_check_id_flag = 'A') THEN
2021
2022 IF p_classcode_name IS NULL THEN
2023 -- return a null since since the name is null
2024 x_classcode_id := NULL;
2025 ELSE
2026 -- fine the ID which matches the name
2027 OPEN c_ids;
2028 LOOP
2029 FETCH c_ids INTO l_current_id;
2030 EXIT WHEN c_ids%notfound;
2031 IF (l_current_id = p_classcode_id) THEN
2032 l_id_found_flag := 'Y';
2033 x_classcode_id := p_classcode_id;
2034 END IF;
2035 END LOOP;
2036 l_num_ids := c_ids%rowcount;
2037 CLOSE c_ids;
2038
2039 IF l_num_ids = 0 THEN
2040 -- No IDS for the name
2041 RAISE no_data_found;
2042 ELSIF(l_num_ids = 1) THEN
2043 -- there is only one
2044 x_classcode_id := l_current_id;
2045 ELSIF (l_id_found_flag = 'N') THEN
2046 -- more than one ID found for the name
2047 RAISE too_many_rows;
2048 END IF;
2049 END IF;
2050
2051 END IF;
2052
2053 ELSE
2054
2055 IF (p_classcode_name IS NOT NULL) then
2056 SELECT class_code_id
2057 INTO x_classcode_id
2058 FROM pa_class_codes
2059 WHERE class_category = p_classcategory
2060 AND class_code = p_classcode_name;
2061
2062 ELSE
2063 x_classcode_id := NULL;
2064 END IF;
2065 END IF;
2066
2067 x_return_status := FND_API.G_RET_STS_SUCCESS;
2068 pa_debug.reset_err_stack;
2069
2070 EXCEPTION
2071
2072 WHEN NO_DATA_FOUND THEN
2073 x_classcode_id := null;
2074 x_return_status := FND_API.G_RET_STS_ERROR;
2075 x_error_message_code := 'PA_CLASS_CATG_CODE_INVALID';
2076
2077 WHEN TOO_MANY_ROWS THEN
2078 x_classcode_id := null;
2079 x_return_status := FND_API.G_RET_STS_ERROR;
2080 x_error_message_code := 'PA_CLASS_CATG_CODE_INVALID';
2081
2082 WHEN OTHERS THEN
2083 x_classcode_id := null;
2084 fnd_msg_pub.add_exc_msg
2085 (p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
2086 p_procedure_name => pa_debug.g_err_stack );
2087 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2088 -- 4537865
2089 x_error_message_code := SQLCODE ;
2090 RAISE;
2091
2092 END Check_Classcode_Name_Or_Id;
2093
2094
2095 -- API name : check_classcategory_name_or_id
2096 -- Type : Public
2097 -- Pre-reqs : None.
2098 -- Description : It validates and returns the class category id
2099 -- from the class category name.
2100 -- History
2101 --
2102 -- 20-Nov-2002 -- adabdull - Created
2103
2104 PROCEDURE Check_ClassCategory_Name_Or_Id(
2105 p_class_category_id IN pa_class_categories.class_category_id%TYPE
2106 ,p_class_category_name IN pa_class_categories.class_category%TYPE
2107 ,p_check_id_flag IN VARCHAR2
2108 ,x_class_category_id OUT NOCOPY pa_class_categories.class_category_id%TYPE --File.Sql.39 bug 4440895
2109 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2110 ,x_error_message_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2111 IS
2112
2113 CURSOR c_ids IS
2114 SELECT class_category_id
2115 FROM pa_class_categories
2116 WHERE class_category = p_class_category_name;
2117
2118 l_current_id NUMBER := NULL;
2119 l_num_ids NUMBER := 0;
2120 l_id_found_flag VARCHAR(1) := 'N';
2121
2122 BEGIN
2123
2124 pa_debug.init_err_stack ('pa_projects_maint_utils.Check_ClassCategory_Name_Or_Id');
2125
2126 IF p_class_category_id IS NOT NULL AND p_class_category_id <> FND_API.G_MISS_NUM THEN
2127
2128 IF p_check_id_flag = 'Y' THEN
2129 SELECT class_category_id
2130 INTO x_class_category_id
2131 FROM pa_class_categories
2132 WHERE class_category_id = p_class_category_id;
2133
2134 ELSIF (p_check_id_flag = 'N') then
2135 x_class_category_id := p_class_category_id;
2136
2137 ELSIF (p_check_id_flag = 'A') THEN
2138
2139 IF p_class_category_name IS NULL THEN
2140 -- return a null since since the name is null
2141 x_class_category_id := NULL;
2142 ELSE
2143 -- fine the ID which matches the name
2144 OPEN c_ids;
2145 LOOP
2146 FETCH c_ids INTO l_current_id;
2147 EXIT WHEN c_ids%notfound;
2148 IF (l_current_id = p_class_category_id) THEN
2149 l_id_found_flag := 'Y';
2150 x_class_category_id := p_class_category_id;
2151 END IF;
2152 END LOOP;
2153 l_num_ids := c_ids%rowcount;
2154 CLOSE c_ids;
2155
2156 IF l_num_ids = 0 THEN
2157 -- No IDS for the name
2158 RAISE no_data_found;
2159 ELSIF(l_num_ids = 1) THEN
2160 -- there is only one
2161 x_class_category_id := l_current_id;
2162 ELSIF (l_id_found_flag = 'N') THEN
2163 -- more than one ID found for the name
2164 RAISE too_many_rows;
2165 END IF;
2166 END IF;
2167
2168 END IF;
2169
2170 ELSE
2171
2172 IF (p_class_category_name IS NOT NULL) then
2173 SELECT class_category_id
2174 INTO x_class_category_id
2175 FROM pa_class_categories
2176 WHERE class_category = p_class_category_name;
2177
2178 ELSE
2179 x_class_category_id := NULL;
2180 END IF;
2181 END IF;
2182
2183 x_return_status := FND_API.G_RET_STS_SUCCESS;
2184 pa_debug.reset_err_stack;
2185
2186 EXCEPTION
2187
2188 WHEN NO_DATA_FOUND THEN
2189 x_class_category_id := null;
2190 x_return_status := FND_API.G_RET_STS_ERROR;
2191 x_error_message_code := 'PA_CLASS_CATG_INVALID';
2192
2193 WHEN TOO_MANY_ROWS THEN
2194 x_class_category_id := null;
2195 x_return_status := FND_API.G_RET_STS_ERROR;
2196 x_error_message_code := 'PA_CLASS_CATG_INVALID';
2197
2198 WHEN OTHERS THEN
2199 x_class_category_id := null;
2200 fnd_msg_pub.add_exc_msg
2201 (p_pkg_name => 'PA_PROJECTS_MAINT_UTILS',
2202 p_procedure_name => pa_debug.g_err_stack );
2203 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
2204 -- 4537865
2205 x_error_message_code := SQLCODE ;
2206 RAISE;
2207
2208 END Check_ClassCategory_Name_Or_Id;
2209
2210 -- API name : class_check_mandatory
2211 -- Type : Public
2212 -- Pre-reqs : None.
2213 -- Description : Checks whether the class category is mandatory and returns 'Y' or 'N'
2214 -- History
2215 --
2216 -- 19-Jan-2003 -- vshastry - Created
2217 --
2218
2219 FUNCTION CLASS_CHECK_MANDATORY
2220 (
2221 p_class_category VARCHAR2,
2222 p_project_id NUMBER) RETURN VARCHAR2
2223 IS
2224 CURSOR C1 IS
2225 SELECT mandatory_flag
2226 FROM pa_class_categories
2227 WHERE class_category = p_class_category;
2228
2229 CURSOR C2 IS
2230 SELECT ps.project_system_status_code
2231 FROM pa_project_statuses ps, pa_projects_all pp
2232 WHERE pp.project_id = p_project_id
2233 AND pp.project_status_code = ps.project_status_code;
2234
2235 /* added cursor c3 for bug 2784433 */
2236 CURSOR C3 IS
2237 SELECT mandatory_flag
2238 FROM pa_valid_categories pvc
2239 WHERE class_category = p_class_category
2240 AND EXISTS (SELECT 'X'
2241 FROM pa_project_types_all pta
2242 WHERE pta.project_type_id = pvc.object_type_id
2243 AND EXISTS (SELECT 'X'
2244 FROM pa_projects_all ppa
2245 WHERE pta.project_type = ppa.project_type
2246 AND project_id = p_project_id));
2247
2248 x_mandatory_flag VARCHAR2(1);
2249 l_project_status pa_project_statuses.project_system_status_code%TYPE;
2250
2251 BEGIN
2252 OPEN C2;
2253 FETCH C2 INTO l_project_status;
2254 CLOSE C2;
2255
2256 IF l_project_status = 'APPROVED' THEN
2257 OPEN C1;
2258 FETCH C1 INTO x_mandatory_flag;
2259 CLOSE C1;
2260
2261 /* added for bug 2784433 */
2262 IF x_mandatory_flag <> 'Y' THEN
2263 OPEN C3;
2264 FETCH C3 INTO x_mandatory_flag;
2265 CLOSE C3;
2266 END IF;
2267 /* added till here for bug 2784433 */
2268 END IF;
2269 RETURN x_mandatory_flag;
2270 END CLASS_CHECK_MANDATORY;
2271
2272
2273 END PA_PROJECTS_MAINT_UTILS;