[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_TEMPLATE_SETUP_UTILS
Source
4 -- API name : GET_OPTION_DETAILS
1 PACKAGE BODY PA_PROJ_TEMPLATE_SETUP_UTILS AS
2 /* $Header: PATMSTUB.pls 120.2.12010000.5 2009/07/20 06:55:32 rmandali ship $ */
3
5 -- Type : Utils API
6 -- Pre-reqs : None
7 -- Return Value :
8 --
9 -- Parameters
10 -- p_option_code IN VARCHAR2
11 -- x_option_name OUT VARCHAR2
12 -- x_function_name OUT VARCHAR2
13 -- x_sort_order OUT NUMBER
14
15 PROCEDURE GET_OPTION_DETAILS(
16 p_option_code IN VARCHAR2
17 ,x_option_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18 ,x_function_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
19 ,x_sort_order OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
20 ,x_web_html_call OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21 ) AS
22
23 CURSOR cur_pa_options
24 IS
25 SELECT option_name, option_function_name,sort_order, FFF.web_html_call
26 FROM PA_OPTIONS PO, FND_FORM_FUNCTIONS FFF
27 WHERE option_code = p_option_code
28 AND PO.OPTION_FUNCTION_NAME = FFF.FUNCTION_NAME(+);
29 BEGIN
30 OPEN cur_pa_options;
31 FETCH cur_pa_options INTO x_option_name, x_function_name, x_sort_order,
32 x_web_html_call;
33 CLOSE cur_pa_options;
34 END GET_OPTION_DETAILS;
35
36 -- API name : GET_PROJ_NUM_OPTION
37 -- Type : Utils API
38 -- Pre-reqs : None
42 -- none
39 -- Return Value : 'AUTOMATIC', 'MANUAL'
40 --
41 -- Parameters
43
44 FUNCTION GET_PROJ_NUM_OPTION RETURN VARCHAR2 IS
45 CURSOR cur_pa_imp
46 IS
47 SELECT user_defined_project_num_code
48 FROM pa_implementations;
49
50 l_return_value VARCHAR2(25);
51 BEGIN
52
53 OPEN cur_pa_imp;
54 FETCH cur_pa_imp INTO l_return_value;
55 CLOSE cur_pa_imp;
56 RETURN l_return_value;
57
58 END GET_PROJ_NUM_OPTION;
59
60 -- API name : Header_Option
61 -- Type : Utils API
62 -- Pre-reqs : None
63 -- Return Value : 'Y', 'N'
64 --
65 -- Parameters
66 -- p_option_code VARCHAR2;
67
68 FUNCTION Header_Option( p_option_code VARCHAR2 ) RETURN VARCHAR2 IS
69
70 CURSOR cur_pa_options
71 IS
72 SELECT 'X'
73 FROM pa_options
74 WHERE parent_option_code = p_option_code;
75 l_dummy_char VARCHAR2(1);
76 BEGIN
77 OPEN cur_pa_options;
78 FETCH cur_pa_options INTO l_dummy_char;
79 IF cur_pa_options%FOUND
80 THEN
81 CLOSE cur_pa_options;
82 RETURN 'Y';
83 ELSE
84 CLOSE cur_pa_options;
85 RETURN 'N';
86 END IF;
87 END Header_Option;
88
89 -- API name : get_limiting_value_meaning
90 -- Type : Utils API
91 -- Pre-reqs : None
92 -- Return Value : the meaning in case of customer and key member and category code
93 -- in case of classification'Y', 'N'
94 --
95 -- Parameters
96 -- p_field_name VARCHAR2;
97 -- p_limiting_value VARCHAR2;
98
99 FUNCTION get_limiting_value_meaning( p_field_name VARCHAR2, p_limiting_value VARCHAR2 ) RETURN VARCHAR2
100 IS
101 CURSOR cur_key_member
102 IS
103 SELECT roles.meaning
104 FROM pa_project_role_types_vl roles
105 WHERE trunc(sysdate) between start_date_active and nvl(end_date_active, sysdate)
106 AND roles.project_role_type = p_limiting_value;
107
108 CURSOR cur_customer_name
109 IS
110 SELECT meaning
111 FROM pa_lookups
112 WHERE lookup_type = 'CUSTOMER PROJECT RELATIONSHIP'
113 AND enabled_flag = 'Y'
114 AND trunc(sysdate) between start_date_active and nvl(end_date_active, sysdate)
115 AND lookup_code = p_limiting_value;
116
117 l_return_value VARCHAR2(240);
118
119 BEGIN
120 IF p_field_name in( 'KEY_MEMBER', 'ORG_ROLE' )
121 THEN
122 OPEN cur_key_member;
123 FETCH cur_key_member INTO l_return_value;
124 CLOSE cur_key_member;
125 ELSIF p_field_name = 'CUSTOMER_NAME'
126 THEN
127 OPEN cur_customer_name;
128 FETCH cur_customer_name INTO l_return_value;
129 CLOSE cur_customer_name;
130 ELSIF p_field_name = 'CLASSIFICATION'
131 THEN
132 --In case of classification the category code is limiting value and specification as well.
133 l_return_value := p_limiting_value;
134 END IF;
135
136 RETURN l_return_value;
137 END get_limiting_value_meaning;
138
139 -- API name : get_limiting_value_meaning
140 -- Type : Utils API
141 -- Pre-reqs : None
142 -- Return Value : None
143 --
144 -- Parameters
145 -- p_option_code VARCHAR2;
146
147 PROCEDURE Check_Template_attr_req(
148 p_project_number VARCHAR2,
149 p_project_name VARCHAR2,
150 p_project_type VARCHAR2,
151 p_organization_id NUMBER,
152 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
153 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
154 ) IS
155 BEGIN
156 x_return_status:= FND_API.G_RET_STS_SUCCESS;
157
158 IF p_project_number IS NULL
159 THEN
160 x_error_msg_code := 'PA_SETUP_PROJ_NUM_REQ';
161 x_return_status:= FND_API.G_RET_STS_ERROR;
162 RAISE FND_API.G_EXC_ERROR;
163 END IF;
164
165 IF p_project_name IS NULL
166 THEN
167 x_error_msg_code := 'PA_SETUP_PROJ_NAME_REQ';
168 x_return_status:= FND_API.G_RET_STS_ERROR;
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171
172 IF p_project_type IS NULL
173 THEN
174 x_error_msg_code := 'PA_SETUP_PROJ_TYPE_REQ';
175 x_return_status:= FND_API.G_RET_STS_ERROR;
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178
179 IF p_organization_id IS NULL
180 THEN
181 x_error_msg_code := 'PA_SETUP_ORG_ID_REQ';
182 x_return_status:= FND_API.G_RET_STS_ERROR;
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 EXCEPTION
187 WHEN FND_API.G_EXC_ERROR THEN
188 x_return_status:= FND_API.G_RET_STS_ERROR;
189 END Check_Template_attr_req;
190 -- API name : Get_Project_Type_Defaults
191 -- Type : Utils API
192 -- Pre-reqs : None
193 -- Return Value : None
194 --
195 -- Parameters
196 -- p_project_type VARCHAR2
197 -- x_Status_code OUT VARCHAR2
198 -- x_service_type_code OUT VARCHAR2
202 -- x_labor_std_bill_rate_schdl OUT VARCHAR2
199 -- x_cost_ind_rate_sch_id OUT NUMBER
200 -- x_labor_sch_type OUT VARCHAR2
201 -- x_labor_bill_rate_org_id OUT NUMBER
203 -- x_non_labor_sch_type OUT VARCHAR2
204 -- x_non_labor_bill_rate_org_id OUT NUMBER
205 -- x_non_labor_std_bill_rate_schdl OUT VARCHAR2
206 -- x_rev_ind_rate_sch_id OUT NUMBER
207 -- x_inv_ind_rate_sch_id OUT NUMBER
208 -- x_labor_invoice_format_id OUT NUMBER
209 -- x_non_labor_invoice_format_id OUT NUMBER
210 -- x_Burden_cost_flag OUT VARCHAR2
211 -- x_interface_asset_cost_code OUT VARCHAR2
212 -- x_cost_sch_override_flag OUT VARCHAR2
213 -- x_billing_offset OUT NUMBER
214 -- x_billing_cycle_id OUT NUMBER
215 -- x_cc_prvdr_flag OUT VARCHAR2
216 -- x_bill_job_group_id OUT NUMBER
217 -- x_cost_job_group_id OUT NUMBER
218 -- x_work_type_id OUT NUMBER
219 -- x_role_list_id OUT NUMBER
220 -- x_unassigned_time OUT NUMBER
221 -- x_emp_bill_rate_schedule_id OUT NUMBER
222 -- x_job_bill_rate_schedule_id OUT NUMBER
223 -- x_budgetary_override_flag OUT VARCHAR2
224 -- x_baseline_funding_flag OUT VARCHAR2
225 -- x_non_lab_std_bill_rt_sch_id OUT NUMBER
226 -- x_revaluate_funding_flag OUT VARCHAR2
227 -- x_include_gains_losses_flag OUT VARCHAR2
228 -- x_return_status OUT VARCHAR2
229 -- x_error_msg_code OUT VARCHAR2
230 -- x_date_eff_funds_flag OUT VARCHAR2
231
232 PROCEDURE Get_Project_Type_Defaults(
233 p_project_type VARCHAR2
234 ,x_Status_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
235 ,x_service_type_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
236 ,x_cost_ind_rate_sch_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
237 ,x_labor_sch_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
238 ,x_labor_bill_rate_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
239 ,x_labor_std_bill_rate_schdl OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
240 ,x_non_labor_sch_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
241 ,x_non_labor_bill_rate_org_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
242 ,x_nl_std_bill_rate_schdl OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
243 ,x_rev_ind_rate_sch_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
244 ,x_inv_ind_rate_sch_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
245 ,x_labor_invoice_format_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
246 ,x_non_labor_invoice_format_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
247 ,x_Burden_cost_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
248 ,x_interface_asset_cost_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
249 ,x_cost_sch_override_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
250 ,x_billing_offset OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
251 ,x_billing_cycle_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
252 ,x_cc_prvdr_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
253 ,x_bill_job_group_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
254 ,x_cost_job_group_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
255 ,x_work_type_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
256 ,x_role_list_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
257 ,x_unassigned_time OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
258 ,x_emp_bill_rate_schedule_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
259 ,x_job_bill_rate_schedule_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
260 ,x_budgetary_override_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
261 ,x_baseline_funding_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
262 ,x_non_lab_std_bill_rt_sch_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
263 ,x_project_type_class_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
264 -- anlee
265 -- patchset K changes
266 ,x_revaluate_funding_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
267 ,x_include_gains_losses_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
268 -- End of changes
269 --PA L Changes 2872708
270 ,x_asset_allocation_method OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
271 ,x_CAPITAL_EVENT_PROCESSING OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
272 ,x_CINT_RATE_SCH_ID OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
273 --End PA L Changes 2872708
274 --Federal.Bug#5511353
275 ,x_date_eff_funds_flag OUT NOCOPY VARCHAR2
276 --Federal.Bug#5511353
277 ,x_ar_rec_notify_flag OUT NOCOPY VARCHAR2 -- 7508661 : EnC
278 ,x_auto_release_pwp_inv OUT NOCOPY VARCHAR2 -- 7508661 : EnC
279 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
280 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
281 ) IS
282 CURSOR cur_project_types
283 IS
284 SELECT def_start_proj_Status_code
285 ,service_type_code
286 ,cost_ind_rate_sch_id
287 ,labor_sch_type
288 ,labor_bill_rate_org_id
289 ,labor_std_bill_rate_schdl
290 ,non_labor_sch_type
291 ,non_labor_bill_rate_org_id
292 ,non_labor_std_bill_rate_schdl
293 ,rev_ind_rate_sch_id
294 ,inv_ind_rate_sch_id
298 ,interface_asset_cost_code
295 ,labor_invoice_format_id
296 ,non_labor_invoice_format_id
297 ,Burden_cost_flag
299 ,cost_sch_override_flag
300 ,billing_offset
301 ,billing_cycle_id
302 ,cc_prvdr_flag
303 ,bill_job_group_id
304 ,cost_job_group_id
305 ,work_type_id
306 ,role_list_id
307 ,unassigned_time
308 ,emp_bill_rate_schedule_id
309 ,job_bill_rate_schedule_id
310 ,budgetary_override_flag
311 ,baseline_funding_flag
312 ,non_lab_std_bill_rt_sch_id
313 ,project_type_class_code
314 -- anlee
315 -- patchset K changes
316 ,revaluate_funding_flag
317 ,include_gains_losses_flag
318 -- End of changes
319 --PA L Changes 2872708
320 ,asset_allocation_method
321 ,CAPITAL_EVENT_PROCESSING
322 ,CINT_RATE_SCH_ID
323 --End PA L Changes 2872708
324 ,nvl(date_eff_funds_consumption,'N') --bug#5511353
325 ,ar_rec_notify_flag -- 7508661 : EnC
326 ,auto_release_pwp_inv -- 7508661 : EnC
327 FROM pa_project_types
328 WHERE project_type = p_project_type;
329 BEGIN
330 x_return_status:= FND_API.G_RET_STS_SUCCESS;
331
332 OPEN cur_project_types;
333 FETCH cur_project_types INTO x_Status_code
334 ,x_service_type_code
335 ,x_cost_ind_rate_sch_id
336 ,x_labor_sch_type
337 ,x_labor_bill_rate_org_id
338 ,x_labor_std_bill_rate_schdl
339 ,x_non_labor_sch_type
340 ,x_non_labor_bill_rate_org_id
341 ,x_nl_std_bill_rate_schdl
342 ,x_rev_ind_rate_sch_id
343 ,x_inv_ind_rate_sch_id
344 ,x_labor_invoice_format_id
345 ,x_non_labor_invoice_format_id
346 ,x_Burden_cost_flag
347 ,x_interface_asset_cost_code
351 ,x_cc_prvdr_flag
348 ,x_cost_sch_override_flag
349 ,x_billing_offset
350 ,x_billing_cycle_id
352 ,x_bill_job_group_id
353 ,x_cost_job_group_id
354 ,x_work_type_id
355 ,x_role_list_id
356 ,x_unassigned_time
357 ,x_emp_bill_rate_schedule_id
358 ,x_job_bill_rate_schedule_id
359 ,x_budgetary_override_flag
360 ,x_baseline_funding_flag
361 ,x_non_lab_std_bill_rt_sch_id
362 ,x_project_type_class_code
363 -- anlee
364 -- patchset K changes
365 ,x_revaluate_funding_flag
366 ,x_include_gains_losses_flag
367 -- End of changes
368 --PA L Changes 2872708
369 ,x_asset_allocation_method
370 ,x_CAPITAL_EVENT_PROCESSING
371 ,x_CINT_RATE_SCH_ID
372 --End PA L Changes 2872708
373 ,x_date_eff_funds_flag
374 ,x_ar_rec_notify_flag -- 7508661 : EnC
375 ,x_auto_release_pwp_inv -- 7508661 : EnC
376 ;
377
378 IF cur_project_types%NOTFOUND
379 THEN
380 x_error_msg_code := 'PA_SETUP_INV_PROJ_TYPE';
381 x_return_status:= FND_API.G_RET_STS_ERROR;
382 CLOSE cur_project_types;
383 RAISE FND_API.G_EXC_ERROR;
384 ELSE
385 CLOSE cur_project_types;
386 END IF;
387 EXCEPTION
388 WHEN FND_API.G_EXC_ERROR THEN
389 x_return_status:= FND_API.G_RET_STS_ERROR;
390
391 END Get_Project_Type_Defaults;
392
393 -- API name : Get_Field_name
394 -- Type : Utils API
395 -- Pre-reqs : None
396 -- Return Value : None
397 --
398 -- Parameters
399 -- p_field_name_meaning VARCHAR2
400 -- x_field_name OUT VARCHAR2
401 -- x_return_status OUT VARCHAR2
402 -- x_error_msg_code OUT VARCHAR2
403
404
405 PROCEDURE Get_Field_name(
406 p_field_name_meaning VARCHAR2,
407 x_field_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
408 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
409 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
410 ) AS
411 CURSOR cur_field_name
412 IS
413 SELECT lookup_code
414 FROM pa_lookups
415 WHERE meaning = p_field_name_meaning
416 AND lookup_type = 'OVERRIDE FIELD';
417
418 BEGIN
419 x_return_status:= FND_API.G_RET_STS_SUCCESS;
420
421 OPEN cur_field_name;
422 FETCH cur_field_name INTO x_field_name;
423 IF cur_field_name%NOTFOUND
424 THEN
425 x_return_status:= FND_API.G_RET_STS_ERROR;
426 CLOSE cur_field_name;
427 x_error_msg_code := 'PA_SETUP_INV_FIELD_MEANG';
428 RAISE FND_API.G_EXC_ERROR;
429 END IF;
430 CLOSE cur_field_name;
431
432 EXCEPTION
433 WHEN FND_API.G_EXC_ERROR THEN
434 x_return_status:= FND_API.G_RET_STS_ERROR;
435
436 END Get_Field_name;
437
438 -- API name : Get_limiting_value
439 -- Type : Utils API
440 -- Pre-reqs : None
441 -- Return Value : None
442 --
443 -- Parameters
444 -- p_field_name VARCHAR2
445 -- p_specification VARCHAR2
446 -- x_limiting_value OUT VARCHAR2
447 -- x_return_status OUT VARCHAR2
448 -- x_error_msg_code OUT VARCHAR2
449
450 PROCEDURE Get_limiting_value(
451 p_field_name VARCHAR2,
452 p_specification VARCHAR2,
453 x_limiting_value OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
454 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
455 x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
456 ) AS
457
458 CURSOR cur_key_member
459 IS
460 SELECT roles.project_role_type
461 FROM pa_project_role_types_vl roles
462 WHERE trunc(sysdate) between start_date_active and nvl(end_date_active, sysdate)
463 AND roles.meaning = p_specification;
464
465 CURSOR cur_customer_name
466 IS
467 SELECT lookup_code
468 FROM pa_lookups
469 WHERE lookup_type = 'CUSTOMER PROJECT RELATIONSHIP'
470 AND enabled_flag = 'Y'
471 AND trunc(sysdate) between start_date_active and nvl(end_date_active, sysdate)
472 AND meaning = p_specification;
473
474
475 BEGIN
476 x_return_status:= FND_API.G_RET_STS_SUCCESS;
477 IF p_field_name = 'CLASSIFICATION'
478 THEN
479 x_limiting_value := p_specification;
480 ELSIF p_field_name = 'KEY_MEMBER'
481 THEN
482 OPEN cur_key_member;
483 FETCH cur_key_member INTO x_limiting_value;
484 IF cur_key_member%NOTFOUND
485 THEN
486 x_return_status:= FND_API.G_RET_STS_ERROR;
487 CLOSE cur_key_member;
488 x_error_msg_code := 'PA_SETUP_INV_KM_MEANG';
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491 CLOSE cur_key_member;
492 ELSIF p_field_name = 'ORG_ROLE'
493 THEN
494 OPEN cur_key_member;
495 FETCH cur_key_member INTO x_limiting_value;
496 IF cur_key_member%NOTFOUND
497 THEN
498 x_return_status:= FND_API.G_RET_STS_ERROR;
499 CLOSE cur_key_member;
503 CLOSE cur_key_member;
500 x_error_msg_code := 'PA_SETUP_INV_ORG_ROL_MEANG';
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
504 ELSIF p_field_name = 'CUSTOMER_NAME'
505 THEN
506 OPEN cur_customer_name;
507 FETCH cur_customer_name INTO x_limiting_value;
508 IF cur_customer_name%NOTFOUND
509 THEN
510 x_return_status:= FND_API.G_RET_STS_ERROR;
511 CLOSE cur_customer_name;
512 x_error_msg_code := 'PA_SETUP_INV_CUST_MEANG';
513 RAISE FND_API.G_EXC_ERROR;
514 END IF;
515 CLOSE cur_customer_name;
516 END IF;
517
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 x_return_status:= FND_API.G_RET_STS_ERROR;
521
522 END Get_limiting_value;
523
524
525 -- API name : CHECK_TEMPLATE_NAME_OR_ID
526 -- Type : Utils API
527 -- Pre-reqs : None
528 -- Return Value : None
529 --
530 -- Parameters
531 -- p_template_name IN VARCHAR2 := 'JUNK_CHARS'
532 -- p_template_id IN NUMBER := -9999
533 -- p_check_id_flag IN VARCHAR2 := 'A'
534 -- x_template_id OUT NUMBER
535 -- x_return_status OUT VARCHAR2
536 -- x_error_msg_code OUT VARCHAR2
537
538 procedure CHECK_TEMPLATE_NAME_OR_ID
539 (
540 p_template_name IN VARCHAR2 := 'JUNK_CHARS'
541 ,p_template_id IN NUMBER := -9999
542 ,p_check_id_flag IN VARCHAR2 := 'A'
543 ,x_template_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
544 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
545 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
546 ) AS
547 l_current_id NUMBER := NULL;
548 l_rows NUMBER := 0;
549 l_id_found_flag VARCHAR2(1) := 'N';
550
551 cursor c IS
552 select project_id
553 from pa_projects
554 where UPPER(name) = UPPER(p_template_name);
555
556 BEGIN
557 IF (p_template_id IS NULL) OR (p_template_id = -9999 ) THEN
558 -- ID is empty
559 IF (p_template_name IS NOT NULL ) THEN
560 OPEN c;
561 LOOP
562 FETCH c INTO l_current_id;
563 EXIT WHEN c%NOTFOUND;
564 IF (l_current_id = p_template_id) THEN
565 l_id_found_flag := 'Y';
566 x_template_id := l_current_id;
567 END IF;
568 END LOOP;
569 l_rows := c%ROWCOUNT;
570 CLOSE c;
571 If (l_rows = 0) THEN
572 RAISE NO_DATA_FOUND;
573 ELSIF (l_rows = 1) THEN
574 x_template_id := l_current_id;
575 ELSIF (l_id_found_flag = 'N') THEN
576 RAISE TOO_MANY_ROWS;
577 END IF;
578 END IF;
579 ELSE
580 --dbms_output.put_line( 'In else part ' );
581
582 -- ID is not empty;
583 IF (p_check_id_flag = 'Y') THEN
584 SELECT project_id
585 INTO x_template_id
586 FROM pa_projects
587 WHERE project_id = p_template_id;
588 ELSIF (p_check_id_flag = 'N') THEN
589 x_template_id := p_template_id;
590 ELSIF (p_check_id_flag = 'A') THEN
591 OPEN c;
592 LOOP
593 --dbms_output.put_line( 'Before fetch ' );
594 FETCH c INTO l_current_id;
595 EXIT WHEN c%NOTFOUND;
596 IF (l_current_id = p_template_id) THEN
597 l_id_found_flag := 'Y';
598 x_template_id := l_current_id;
599 END IF;
600 END LOOP;
601 l_rows := c%ROWCOUNT;
602 CLOSE c;
603 If (l_rows = 0) THEN
604 --dbms_output.put_line( 'Before no data found cond ' );
605 RAISE NO_DATA_FOUND;
606 ELSIF (l_rows = 1) THEN
607 x_template_id := l_current_id;
608 ELSIF (l_id_found_flag = 'N') THEN
609 RAISE TOO_MANY_ROWS;
610 END IF;
611 END IF;
612 END IF;
613
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 --dbms_output.put_line( 'In no data found exception ' );
618 x_template_id := NULL;
619 x_return_status := FND_API.G_RET_STS_ERROR;
620 x_error_msg_code := 'PA_SETUP_INV_TMPL_ID';
621 WHEN TOO_MANY_ROWS THEN
622 x_template_id := NULL;
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 x_error_msg_code := 'PA_SETUP_TMPL_ID_NOT_UNIQ';
625 WHEN OTHERS THEN
626 x_template_id := NULL;
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_UTILS',
629 p_procedure_name => 'CHECK_TEMPLATE_NAME_OR_ID');
630 RAISE;
631 END CHECK_TEMPLATE_NAME_OR_ID;
632
633
634 -- API name : CHECK_PROJECT_NAME_OR_ID
635 -- Type : Utils API
636 -- Pre-reqs : None
637 -- Return Value : None
638 --
639 -- Parameters
640 -- Parameters
641 -- p_project_name IN VARCHAR2 := 'JUNK_CHARS'
642 -- p_project_id IN NUMBER := -9999
643 -- p_check_id_flag IN VARCHAR2 := 'A'
647
644 -- x_project_id OUT NUMBER
645 -- x_return_status OUT VARCHAR2
646 -- x_error_msg_code OUT VARCHAR2
648
649 procedure CHECK_PROJECT_NAME_OR_ID
650 (
651 p_project_name IN VARCHAR2 := 'JUNK_CHARS'
652 ,p_project_id IN NUMBER := -9999
653 ,p_check_id_flag IN VARCHAR2 := 'A'
654 ,x_project_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
655 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
656 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
657 ) AS
658 l_current_id NUMBER := NULL;
659 l_rows NUMBER := 0;
660 l_id_found_flag VARCHAR2(1) := 'N';
661 l_ndf_exception NUMBER; --to indicate which part of the code raised no_data_found exception
662
663 cursor c IS
664 select project_id
665 from pa_projects_v --cannot replace with project_all bcoz project name user enters must be validated with secuured view.
666 where name = p_project_name; -- removed the UPPER function for perf. bug 2786121
667
668 BEGIN
669
670 --Initialize error stack
671 FND_MSG_PUB.initialize;
672
673 IF (p_project_id IS NULL) OR (p_project_id = -9999) THEN
674 -- ID is empty
675 IF (p_project_name IS NOT NULL ) THEN
676 OPEN c;
677 LOOP
678 FETCH c INTO l_current_id;
679 EXIT WHEN c%NOTFOUND;
680 IF (l_current_id = p_project_id) THEN
681 l_id_found_flag := 'Y';
682 x_project_id := l_current_id;
683 END IF;
684 END LOOP;
685 l_rows := c%ROWCOUNT;
686 CLOSE c;
687 If (l_rows = 0) THEN
688 l_ndf_exception := 1;
689 RAISE NO_DATA_FOUND;
690 ELSIF (l_rows = 1) THEN
691 x_project_id := l_current_id;
692 ELSIF (l_id_found_flag = 'N') THEN
693 RAISE TOO_MANY_ROWS;
694 END IF;
695 END IF;
696 ELSE
697 -- ID is not empty;
698 IF (p_check_id_flag = 'Y') THEN
699 SELECT project_id
700 INTO x_project_id
701 FROM pa_projects_all --replaced pa_projects_v with pa_projects_all for perf. bug 2786121
702 WHERE project_id = p_project_id;
703 ELSIF (p_check_id_flag = 'N') THEN
704 x_project_id := p_project_id;
705 ELSIF (p_check_id_flag = 'A') THEN
706 OPEN c;
707 LOOP
708 FETCH c INTO l_current_id;
709 EXIT WHEN c%NOTFOUND;
710 IF (l_current_id = p_project_id) THEN
711 l_id_found_flag := 'Y';
712 x_project_id := l_current_id;
713 END IF;
714 END LOOP;
715 l_rows := c%ROWCOUNT;
716 CLOSE c;
717 If (l_rows = 0) THEN
718 l_ndf_exception := 2;
719 RAISE NO_DATA_FOUND;
720 ELSIF (l_rows = 1) THEN
721 x_project_id := l_current_id;
722 ELSIF (l_id_found_flag = 'N') THEN
723 RAISE TOO_MANY_ROWS;
724 END IF;
725 END IF;
726 END IF;
727
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729 EXCEPTION
730 WHEN NO_DATA_FOUND THEN
731 x_project_id := NULL;
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 IF l_ndf_exception = 2 THEN
734 x_error_msg_code := 'PA_TASK_INV_PRJ_ID';
735 ELSE
736 x_error_msg_code := 'PA_SETUP_INV_PROJ_NAME';
737 END IF;
738 WHEN TOO_MANY_ROWS THEN
739 x_project_id := NULL;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 x_error_msg_code := 'PA_TASK_PRJ_ID_NOT_UNIQ';
742 WHEN OTHERS THEN
743 x_project_id := NULL;
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
745 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_UTILS',
746 p_procedure_name => 'CHECK_PROJECT_NAME_OR_ID');
747 RAISE;
748 END CHECK_PROJECT_NAME_OR_ID;
749
750 -- API name : CHECK_PROJ_TMPL_NAME_OR_ID
751 -- Type : Utils API
752 -- Pre-reqs : None
753 -- Return Value : None
754 --
755 -- Parameters
756 -- p_proj_tmpl_name IN VARCHAR2 := 'JUNK_CHARS'
757 -- p_proj_tmpl_id IN NUMBER := -9999
758 -- p_check_id_flag IN VARCHAR2 := 'A'
759 -- p_template_flag IN VARCHAR2 := 'Y'
760 -- x_proj_tmpl_id OUT NUMBER
761 -- x_return_status OUT VARCHAR2
762 -- x_error_msg_code OUT VARCHAR2
763
764 procedure CHECK_PROJ_TMPL_NAME_OR_ID
765 (
766 p_proj_tmpl_name IN VARCHAR2 := 'JUNK_CHARS'
767 ,p_proj_tmpl_id IN NUMBER := -9999
768 ,p_check_id_flag IN VARCHAR2 := 'A'
769 ,p_template_flag IN VARCHAR2 := 'Y'
770 ,x_proj_tmpl_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
771 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
772 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
773 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
774 ) AS
775 l_return_status VARCHAR2(1);
776 l_msg_count NUMBER;
777 l_msg_data VARCHAR2(250);
778 l_data VARCHAR2(250);
779 l_msg_index_out NUMBER;
780 l_error_msg_code VARCHAR2(250);
784 THEN
781 begin
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783 IF p_template_flag = 'Y'
785 PA_PROJ_TEMPLATE_SETUP_UTILS.CHECK_TEMPLATE_NAME_OR_ID(
786 p_template_name => p_proj_tmpl_name
787 ,p_template_id => p_proj_tmpl_id
788 ,p_check_id_flag => p_check_id_flag
789 ,x_template_id => x_proj_tmpl_id
790 ,x_return_status => l_return_status
791 ,x_error_msg_code => l_error_msg_code
792 );
793 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
794 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
795 p_msg_name => l_error_msg_code);
796 END IF;
797 ELSE
798 PA_PROJ_TEMPLATE_SETUP_UTILS.CHECK_PROJECT_NAME_OR_ID(
799 p_project_name => p_proj_tmpl_name
800 ,p_project_id => p_proj_tmpl_id
801 ,p_check_id_flag => p_check_id_flag
802 ,x_project_id => x_proj_tmpl_id
803 ,x_return_status => l_return_status
804 ,x_error_msg_code => l_error_msg_code
805 );
806 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
807 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
808 p_msg_name => l_error_msg_code);
809 END IF;
810 END IF;
811 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
812 x_msg_count := FND_MSG_PUB.count_msg;
813 IF x_msg_count = 1 then
814 pa_interface_utils_pub.get_messages
815 (p_encoded => FND_API.G_TRUE,
816 p_msg_index => 1,
817 p_msg_count => l_msg_count,
818 p_msg_data => l_msg_data,
819 p_data => l_data,
820 p_msg_index_out => l_msg_index_out);
821 x_msg_data := l_data;
822 END IF;
823 raise FND_API.G_EXC_ERROR;
824 END IF;
825 exception
826 when FND_API.G_EXC_ERROR then
827 x_return_status := FND_API.G_RET_STS_ERROR;
828
829 WHEN OTHERS THEN
830 x_proj_tmpl_id := NULL;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_UTILS',
833 p_procedure_name => 'CHECK_PROJ_TEMPL_NAME_OR_ID');
834 RAISE;
835
836 end CHECK_PROJ_TMPL_NAME_OR_ID;
837
838
839 -- API name : GET_OPTION_ENABLED
840 -- Type : Utils API
841 -- Pre-reqs : None
842 -- Return Value :
843 --
844 -- Parameters
845 -- p_option_code IN VARCHAR2
846 -- p_project_id IN NUMBER
847 -- x_option_enabled OUT VARCHAR2
848
849 PROCEDURE GET_OPTION_ENABLED(
850 p_option_code IN VARCHAR2
851 ,p_project_id IN NUMBER
852 ,x_option_enabled OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
853 ) AS
854
855 l_template_id NUMBER;
856 l_option_enabled VARCHAR2(1);
857 l_check_template VARCHAR2(1);
858
859 CURSOR get_template_id
860 IS
861 SELECT created_from_project_id
862 FROM PA_PROJECTS_ALL
863 WHERE project_id = p_project_id;
864
865 CURSOR cur_pa_proj_options(c_project_id NUMBER)
866 IS
867 SELECT 'Y'
868 FROM PA_PROJECT_OPTIONS PPO
869 WHERE option_code = p_option_code
870 AND project_id = c_project_id;
871
872 CURSOR check_template
873 IS
874 SELECT 'Y'
875 FROM PA_PROJECTS_ALL
876 WHERE project_id = p_project_id
877 AND template_flag = 'Y';
878
879 BEGIN
880
881 OPEN check_template;
882 FETCH check_template INTO l_check_template;
883
884 if check_template%FOUND then
885 OPEN cur_pa_proj_options(p_project_id);
886 FETCH cur_pa_proj_options INTO l_option_enabled;
887 if cur_pa_proj_options%NOTFOUND then
888 x_option_enabled := 'N';
889 else
890 x_option_enabled := l_option_enabled;
891 end if;
892
893 CLOSE cur_pa_proj_options;
894
895 else
896 OPEN get_template_id;
897 FETCH get_template_id INTO l_template_id;
898 CLOSE get_template_id;
899
900 OPEN cur_pa_proj_options(l_template_id);
901 FETCH cur_pa_proj_options INTO l_option_enabled;
902 if cur_pa_proj_options%NOTFOUND then
903 x_option_enabled := 'N';
904 else
905 x_option_enabled := l_option_enabled;
906 end if;
907
908 CLOSE cur_pa_proj_options;
909 end if;
910
911 --bug 3905802, close the cursor
912 CLOSE check_template;
913
914 END GET_OPTION_ENABLED;
915
916 -- API name : GET_PROJ_NUM_TYPE
917 -- Type : Utils API
918 -- Pre-reqs : None
919 -- Return Value : 'NUMERIC' or 'ALPHANUMERIC'
920 --
921 -- Parameters
922 -- none
923
924 FUNCTION GET_PROJ_NUM_TYPE RETURN VARCHAR2 IS
925 CURSOR cur_pa_imp
926 IS
927 SELECT Manual_Project_Num_Type
928 FROM pa_implementations;
929
930 l_return_value VARCHAR2(25);
931 BEGIN
932
933 OPEN cur_pa_imp;
934 FETCH cur_pa_imp INTO l_return_value;
935 CLOSE cur_pa_imp;
936 RETURN l_return_value;
937
938 END GET_PROJ_NUM_TYPE;
939
940 /* Added for Bug 8492552 Start */
941 -- API name : GET_TOTAL_PERCENT
942 -- Type : Utils API
943 -- Pre-reqs : None
944 -- Return Value :
945 --
946 -- Parameters
947 -- P_CATEGORY IN VARCHAR2
948 -- X_TOTAL_PERCENT_FLAG OUT VARCHAR2
949
950 PROCEDURE GET_TOTAL_PERCENT(
951 P_CATEGORY IN VARCHAR2
952 ,X_TOTAL_PERCENT_FLAG OUT NOCOPY VARCHAR2
953 ) AS
954
955 l_temp_flag VARCHAR2(1);
956
957 CURSOR get_total_percent_flag
958 IS
959 select total_100_percent_flag
960 from pa_class_categories
961 where class_category = P_CATEGORY ;
962
963 BEGIN
964
965 OPEN get_total_percent_flag;
966 FETCH get_total_percent_flag INTO l_temp_flag;
967
968
969 if get_total_percent_flag%NOTFOUND then
970 X_TOTAL_PERCENT_FLAG := 'N';
971 else
972 X_TOTAL_PERCENT_FLAG := l_temp_flag;
973 end if;
974
975 CLOSE get_total_percent_flag;
976
977 END GET_TOTAL_PERCENT;
978 /* Added for Bug 8492552 End */
979
980 END PA_PROJ_TEMPLATE_SETUP_UTILS;