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