[Home] [Help]
PACKAGE BODY: APPS.PA_WORKPLAN_ATTR_UTILS
Source
1 PACKAGE BODY PA_WORKPLAN_ATTR_UTILS AS
2 /* $Header: PAPRWPUB.pls 120.2 2005/08/23 04:30:25 sunkalya noship $ */
3
4 -- Global constant
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_WORKPLAN_ATTR_UTILS';
6
7 -- API name : CHECK_LIFECYCLE_NAME_OR_ID
8 -- Type : Utility
9 -- Pre-reqs : None.
10 -- Parameters :
11 -- p_lifecycle_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
12 -- p_lifecycle_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
13 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
14 -- x_lifecycle_id OUT NUMBER Required
15 -- x_return_status OUT VARCHAR2 Required
16 -- x_error_msg_code OUT VARCHAR2 Required
17
18 PROCEDURE CHECK_LIFECYCLE_NAME_OR_ID
19 ( p_lifecycle_id IN NUMBER := FND_API.G_MISS_NUM
20 ,p_lifecycle_name IN VARCHAR2 := FND_API.G_MISS_CHAR
21 ,p_check_id_flag IN VARCHAR2 := 'A'
22 ,x_lifecycle_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
23 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
24 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
25 )
26 IS
27 BEGIN
28 if (p_lifecycle_id = FND_API.G_MISS_NUM) OR (p_lifecycle_id is NULL) then
29 if (p_lifecycle_name is not NULL) then
30 SELECT pev.element_version_id
31 INTO x_lifecycle_id
32 FROM pa_proj_elements pe, pa_proj_element_versions pev,
33 pa_lifecycle_usages plu
34 WHERE pe.project_id=0
35 AND pe.proj_element_id = pev.proj_element_id
36 AND pe.element_number = p_lifecycle_name
37 AND pe.proj_element_id = plu.LIFECYCLE_ID
38 AND plu.USAGE_TYPE = 'PROJECTS';
39 else
40 x_lifecycle_id := NULL;
41 end if;
42 else
43 if p_check_id_flag = 'Y' then
44 x_lifecycle_id := p_lifecycle_id;
45 ELSIF (p_check_id_flag='N') THEN
46 x_lifecycle_id := p_lifecycle_id;
47 ELSIF (p_check_id_flag = 'A') THEN
48 IF (p_lifecycle_name IS NULL) THEN
49 x_lifecycle_id := NULL;
50 ELSE
51 SELECT pev.element_version_id
52 INTO x_lifecycle_id
53 FROM pa_proj_elements pe, pa_proj_element_versions pev,
54 pa_lifecycle_usages plu
55 WHERE pe.project_id=0
56 AND pe.proj_element_id = pev.proj_element_id
57 AND pe.element_number = p_lifecycle_name
58 AND pe.proj_element_id = plu.LIFECYCLE_ID
59 AND plu.USAGE_TYPE = 'PROJECTS';
60 END IF;
61 end if;
62 end if;
63
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65 EXCEPTION
66 when NO_DATA_FOUND then
67 x_lifecycle_id := NULL;
68 x_return_status := FND_API.G_RET_STS_ERROR;
69 x_error_msg_code := 'PA_LCYL_NOT_VALID';
70 when OTHERS then
71 x_lifecycle_id := NULL;
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
73 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS', p_procedure_name => 'CHECK_LIFECYCLE_NAME_OR_ID');
74 raise;
75 END CHECK_LIFECYCLE_NAME_OR_ID;
76
77 -- API name : CHECK_LIFECYCLE_PHASE_NAME_ID
78 -- Type : Utility
79 -- Pre-reqs : None.
80 -- Parameters :
81 -- p_lifecycle_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
82 -- p_current_lifecycle_phase_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
83 -- p_current_lifecycle_phase IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
84 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
85 -- x_current_lifecycle_phase_id OUT NUMBER Required
86 -- x_return_status OUT VARCHAR2 Required
87 -- x_error_msg_code OUT VARCHAR2 Required
88
89 PROCEDURE CHECK_LIFECYCLE_PHASE_NAME_ID
90 ( p_lifecycle_id IN NUMBER := FND_API.G_MISS_NUM
91 ,p_current_lifecycle_phase_id IN NUMBER := FND_API.G_MISS_NUM
92 ,p_current_lifecycle_phase IN VARCHAR2 := FND_API.G_MISS_CHAR
93 ,p_check_id_flag IN VARCHAR2 := 'A'
94 ,x_current_lifecycle_phase_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
95 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
96 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
97 )
98 IS
99 NO_LIFECYCLE EXCEPTION;
100 BEGIN
101 if (p_lifecycle_id = FND_API.G_MISS_NUM) OR (p_lifecycle_id is NULL) then
102 RAISE NO_LIFECYCLE;
103 end if;
104
105 if (p_current_lifecycle_phase_id = FND_API.G_MISS_NUM) OR (p_current_lifecycle_phase_id is NULL) then
106 if (p_current_lifecycle_phase is not NULL) then
107 SELECT pev.element_version_id
108 INTO x_current_lifecycle_phase_id
109 FROM pa_proj_elements pe, pa_proj_element_versions pev
110 WHERE pe.project_id=0
111 AND pe.element_number = p_current_lifecycle_phase
112 AND pe.proj_element_id = pev.proj_element_id
113 AND pev.parent_structure_version_id = p_lifecycle_id;
114 else
115 x_current_lifecycle_phase_id := NULL;
116 end if;
117 else
118 if p_check_id_flag = 'Y' then
119 x_current_lifecycle_phase_id := p_current_lifecycle_phase_id;
120 ELSIF (p_check_id_flag='N') THEN
121 x_current_lifecycle_phase_id := p_current_lifecycle_phase_id;
122 ELSIF (p_check_id_flag = 'A') THEN
123 IF (p_current_lifecycle_phase IS NULL) THEN
124 x_current_lifecycle_phase_id := NULL;
125 ELSE
126 SELECT pev.element_version_id
127 INTO x_current_lifecycle_phase_id
128 FROM pa_proj_elements pe, pa_proj_element_versions pev
129 WHERE pe.project_id=0
130 AND pe.element_number = p_current_lifecycle_phase
131 AND pe.proj_element_id = pev.proj_element_id
132 AND pev.parent_structure_version_id = p_lifecycle_id;
133 END IF;
134 end if;
135 end if;
136
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138 EXCEPTION
139 when NO_LIFECYCLE then
140 x_current_lifecycle_phase_id := NULL;
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 x_error_msg_code := 'PA_LCYL_LIFECYCLE_REQUIRED';
143 when NO_DATA_FOUND then
144 x_current_lifecycle_phase_id := NULL;
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 x_error_msg_code := 'PA_LCYL_PHASE_NOT_VALID';
147 when OTHERS then
148 x_current_lifecycle_phase_id := NULL;
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS', p_procedure_name => 'CHECK_LIFECYCLE_PHASE_NAME_ID');
151 raise;
152 END CHECK_LIFECYCLE_PHASE_NAME_ID;
153
154 -- API name : Check_Approver_Name_Or_Id
155 -- Type : Utility
156 -- Pre-reqs : None.
157 -- Parameters :
158 -- p_approver_source_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
159 -- p_approver_source_type IN NUMBER Optional Default = FND_API.G_MISS_NUM
160 -- p_approver_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
161 -- p_check_id_flag IN VARCHAR2 Optional Default = 'A'
162 -- x_approver_source_id OUT NUMBER Required
163 -- x_approver_source_type OUT NUMBER Required
164 -- x_return_status OUT VARCHAR2 Required
165 -- x_error_msg_code OUT VARCHAR2 Required
166
167 PROCEDURE CHECK_APPROVER_NAME_OR_ID
168 ( p_approver_source_id IN NUMBER := FND_API.G_MISS_NUM
169 ,p_approver_source_type IN NUMBER := FND_API.G_MISS_NUM
170 ,p_approver_name IN VARCHAR2 := FND_API.G_MISS_CHAR
171 ,p_check_id_flag IN VARCHAR2 := 'A'
172 ,x_approver_source_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
173 ,x_approver_source_type OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
174 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
175 ,x_error_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
176 )
177 IS
178 l_current_source_id NUMBER := NULL;
179 l_current_source_type NUMBER := NULL;
180 l_num_ids NUMBER := 0;
181 l_id_found_flag VARCHAR(1) := 'N';
182
183 CURSOR c_ids IS
184 SELECT resource_source_id, resource_type_id
185 FROM pa_people_lov_v
186 WHERE name = p_approver_name;
187
188 BEGIN
189 if (p_approver_source_id = FND_API.G_MISS_NUM) OR (p_approver_source_id is NULL) then
190 if (p_approver_name is not NULL) then
191 SELECT resource_source_id, resource_type_id
192 INTO x_approver_source_id, x_approver_source_type
193 FROM pa_people_lov_v
194 WHERE name = p_approver_name;
195 else
196 x_approver_source_id := NULL;
197 x_approver_source_type := NULL;
198 end if;
199 else
200 if p_check_id_flag = 'Y' then
201 SELECT resource_source_id, resource_type_id
202 INTO x_approver_source_id, x_approver_source_type
203 FROM pa_people_lov_v
204 WHERE resource_source_id = p_approver_source_id
205 AND resource_type_id = p_approver_source_type;
206 ELSIF (p_check_id_flag='N') THEN
207 x_approver_source_id := p_approver_source_id;
208 x_approver_source_type := p_approver_source_type;
209
210 ELSIF (p_check_id_flag = 'A') THEN
211 IF (p_approver_name IS NULL) THEN
212 -- Return a null ID since the name is null.
213 x_approver_source_id := NULL;
214 x_approver_source_type := NULL;
215 ELSE
216
217 -- Find the ID which matches the Name passed
218 OPEN c_ids;
219 LOOP
220 FETCH c_ids INTO l_current_source_id, l_current_source_type;
221 EXIT WHEN c_ids%NOTFOUND;
222 IF (l_current_source_id = p_approver_source_id) AND
223 (l_current_source_type = p_approver_source_type) THEN
224 l_id_found_flag := 'Y';
225 x_approver_source_id := p_approver_source_id;
226 x_approver_source_type := p_approver_source_type;
227 END IF;
228 END LOOP;
229 l_num_ids := c_ids%ROWCOUNT;
230 CLOSE c_ids;
231
232 IF (l_num_ids = 0) THEN
233 -- No IDs for name
234 RAISE NO_DATA_FOUND;
235 ELSIF (l_num_ids = 1) THEN
236 -- Since there is only one ID for the name use it.
237 x_approver_source_id := l_current_source_id;
238 x_approver_source_type := l_current_source_type;
239 ELSIF (l_id_found_flag = 'N') THEN
240 -- More than one ID for the name and none of the IDs matched
241 -- the ID passed in.
242 RAISE TOO_MANY_ROWS;
243 END IF;
244 END IF;
245 end if;
246 end if;
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248 EXCEPTION
249 when NO_DATA_FOUND then
250 x_approver_source_id := NULL;
251 x_approver_source_type := NULL;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 x_error_msg_code := 'PA_APPR_SOURCE_NAME_INV';
254 when TOO_MANY_ROWS then
255 x_approver_source_id := NULL;
256 x_approver_source_type := NULL;
257 x_return_status := FND_API.G_RET_STS_ERROR;
258 x_error_msg_code := 'PA_APPR_SOURCE_NAME_MULTIPLE';
259 when OTHERS then
260 x_approver_source_id := NULL;
261 x_approver_source_type := NULL;
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS', p_procedure_name => 'CHECK_APPROVER_NAME_OR_ID');
264 raise;
265 END CHECK_APPROVER_NAME_OR_ID;
266
267
268 -- API name : Check_Wp_Versioning_Enabled
269 -- Type : Utility
270 -- Pre-reqs : None.
271 -- Parameters :
272 -- p_project_id IN NUMBER Required
273
274
275 FUNCTION CHECK_WP_VERSIONING_ENABLED
276 ( p_project_id IN NUMBER
277 ) RETURN VARCHAR2
278 IS
279 l_wp_versioning_enabled VARCHAR2(1);
280
281 /*Bug No 3489940 */
282 /* Modified get_flag cursor to check for workplan structure */
283 /*CURSOR get_flag IS
284 SELECT wp_enable_version_flag
285 FROM pa_proj_workplan_attr
286 WHERE project_id = p_project_id;*/
287 CURSOR get_flag IS
288 SELECT ppwa.wp_enable_version_flag
289 FROM pa_proj_workplan_attr ppwa,
290 pa_proj_elements ppe,
291 pa_proj_structure_types ppst,
292 pa_structure_types pst
293 WHERE ppwa.project_id = p_project_id
294 AND ppe.project_id = ppwa.project_id
295 AND ppe.proj_element_id = ppwa.proj_element_id
296 AND ppe.proj_element_id = ppst.proj_element_id
297 AND ppst.structure_type_id = pst.structure_type_id
298 AND pst.structure_type_class_code = 'WORKPLAN';
299
300 BEGIN
301
302 OPEN get_flag;
303 FETCH get_flag INTO l_wp_versioning_enabled;
304 CLOSE get_flag;
305
306 return l_wp_versioning_enabled;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 return NULL;
311 END CHECK_WP_VERSIONING_ENABLED;
312
313
314
315 -- API name : Check_DATE_SYNC_ENABLED
316 -- Type : Utility
317 -- Pre-reqs : None.
318 -- Parameters :
319 -- p_proj_element_id IN NUMBER Required
320
321 FUNCTION CHECK_AUTO_DATE_SYNC_ENABLED
322 ( p_proj_element_id IN NUMBER
323 ) RETURN VARCHAR2
324 IS
325 l_sync_enabled VARCHAR2(1);
326 --Jul 23rd Only Workplan
327 CURSOR get_sync_flag IS
328 select ppwa.AUTO_SYNC_TXN_DATE_FLAG
329 from pa_proj_workplan_attr ppwa,
330 pa_proj_elements ppe,
331 pa_proj_structure_types ppst,
332 pa_structure_types pst
333 WHERE ppwa.proj_element_id = p_proj_element_id
334 AND ppe.project_id = ppwa.project_id
335 AND ppe.proj_element_id = ppwa.proj_element_id
336 AND ppe.proj_element_id = ppst.proj_element_id
337 AND ppst.structure_type_id = pst.structure_type_id
338 AND pst.structure_type_class_code = 'WORKPLAN';
339 /* select AUTO_SYNC_TXN_DATE_FLAG
340 from pa_proj_workplan_attr
341 Where proj_element_id = p_proj_element_id;*/
342 BEGIN
343 open get_sync_flag;
344 FETCH get_sync_flag into l_sync_enabled;
345 CLOSE get_sync_flag;
346
347 return l_sync_enabled;
348
349 END CHECK_AUTO_DATE_SYNC_ENABLED;
350
351
352 -- API name : GET_SYNC_BUF_DAYS
353 -- Type : Utility
354 -- Pre-reqs : None.
355 -- Parameters :
356 -- p_proj_element_id IN NUMBER Required
357
358 FUNCTION GET_SYNC_BUF_DAYS
359 ( p_proj_element_id IN NUMBER
360 ) RETURN NUMBER
361 IS
362 l_sync_days NUMBER;
363 --Jul 23rd Only Workplan
364 CURSOR get_sync_days IS
365 select ppwa.TXN_DATE_SYNC_BUF_DAYS
366 from pa_proj_workplan_attr ppwa,
367 pa_proj_elements ppe,
368 pa_proj_structure_types ppst,
369 pa_structure_types pst
370 WHERE ppwa.proj_element_id = p_proj_element_id
371 AND ppe.project_id = ppwa.project_id
372 AND ppe.proj_element_id = ppwa.proj_element_id
373 AND ppe.proj_element_id = ppst.proj_element_id
374 AND ppst.structure_type_id = pst.structure_type_id
375 AND pst.structure_type_class_code = 'WORKPLAN';
376 /* select TXN_DATE_SYNC_BUF_DAYS
377 from pa_proj_workplan_attr
378 Where proj_element_id = p_proj_element_id; */
379 BEGIN
380 OPEN get_sync_days;
381 FETCH get_sync_days into l_sync_days;
382 CLOSE get_sync_days;
383
384 return l_sync_days;
385 END GET_SYNC_BUF_DAYS;
386
387
388 -- API name : CHECK_WP_PROJECT_EXISTS
389 -- Type : Utility
390 -- Pre-reqs : None.
391 -- Parameters :
392 -- p_lifecycle_id IN NUMBER Required
393
394 PROCEDURE CHECK_WP_PROJECT_EXISTS
395 ( p_lifecycle_id IN NUMBER
396 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
397 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
398 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
399 IS
400
401 CURSOR get_lifecycle_version(c_proj_element_id IN NUMBER) IS
402 SELECT element_version_id
403 FROM pa_proj_element_versions
404 WHERE proj_element_id = c_proj_element_id;
405 cur_lifecycle_version get_lifecycle_version%ROWTYPE;
406
407 CURSOR c1(c_lifecycle_version_id IN NUMBER) IS
408 SELECT project_id
409 FROM pa_proj_workplan_attr
410 WHERE lifecycle_version_id = c_lifecycle_version_id;
411
412 l_delete_lifecycle_error EXCEPTION;
413 l_msg_index_out NUMBER;
414 -- added for Bug fix: 4537865
415 l_new_msg_data VARCHAR2(2000);
416 -- added for Bug fix: 4537865
417
418 BEGIN
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421 OPEN get_lifecycle_version(p_lifecycle_id);
422 FETCH get_lifecycle_version INTO cur_lifecycle_version;
423 CLOSE get_lifecycle_version;
424
425 OPEN c1(cur_lifecycle_version.element_version_id);
426 IF c1%FOUND THEN
427 RAISE l_delete_lifecycle_error;
428 END IF;
429 CLOSE c1;
430
431 EXCEPTION
432 WHEN l_delete_lifecycle_error THEN
433 PA_UTILS.add_message('PA','PA_DEL_LIFECYCLE_ERROR');
434 x_return_status := FND_API.G_RET_STS_ERROR;
435 x_msg_data := 'PA_DEL_LIFECYCLE_ERROR';
436 x_msg_count := FND_MSG_PUB.Count_Msg;
437 If x_msg_count = 1 THEN
438 pa_interface_utils_pub.get_messages
439 (p_encoded => FND_API.G_TRUE,
440 p_msg_index => 1,
441 p_msg_count => x_msg_count,
442 p_msg_data => x_msg_data,
443 --p_data => x_msg_data, * Commented for Bug fix: 4537865
444 p_data => l_new_msg_data, -- added for Bug fix: 4537865
445 p_msg_index_out => l_msg_index_out );
446 -- added for Bug fix: 4537865
447 x_msg_data := l_new_msg_data;
448 -- added for Bug fix: 4537865
449 End if;
450
451 WHEN OTHERS THEN
452 FND_MSG_PUB.add_exc_msg
453 (p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS.CHECK_WP_PROJECT_EXISTS',
454 p_procedure_name => PA_DEBUG.G_Err_Stack );
455 RAISE;
456
457 END CHECK_WP_PROJECT_EXISTS;
458
459
460 -- API name : CHECK_WP_TASK_EXISTS
461 -- Type : Utility
462 -- Pre-reqs : None.
463 -- Parameters :
464 -- p_lifecycle_phase_id IN NUMBER Required
465
466 PROCEDURE CHECK_WP_TASK_EXISTS
467 ( p_lifecycle_phase_id IN NUMBER
468 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
469 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
470 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
471 IS
472 CURSOR get_phase_version(c_proj_element_id IN NUMBER) IS
473 SELECT element_version_id
474 FROM pa_proj_element_versions
475 WHERE proj_element_id = c_proj_element_id;
476 cur_phase_version get_phase_version%ROWTYPE;
477
478 CURSOR c1(c_phase_version_id IN NUMBER) IS
479 SELECT proj_element_id
480 FROM pa_proj_elements
481 WHERE phase_version_id = c_phase_version_id;
482
483 l_del_lifecycle_phase_error EXCEPTION;
484 l_msg_index_out NUMBER;
485 -- added for Bug fix: 4537865
486 l_new_msg_data VARCHAR2(2000);
487 -- added for Bug fix: 4537865
488 BEGIN
489 x_return_status := FND_API.G_RET_STS_SUCCESS;
490
491 OPEN get_phase_version(p_lifecycle_phase_id);
492 FETCH get_phase_version INTO cur_phase_version;
493 CLOSE get_phase_version;
494
495 OPEN c1(cur_phase_version.element_version_id);
496 IF c1%FOUND THEN
497 RAISE l_del_lifecycle_phase_error;
498 END IF;
499 CLOSE c1;
500
501 EXCEPTION
502 WHEN l_del_lifecycle_phase_error THEN
503 PA_UTILS.add_message('PA','PA_DEL_LIFECYCLE_PHASE_ERROR');
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 x_msg_data := 'PA_DEL_LIFECYCLE_PHASE_ERROR';
506 x_msg_count := FND_MSG_PUB.Count_Msg;
507 If x_msg_count = 1 THEN
508 pa_interface_utils_pub.get_messages
509 (p_encoded => FND_API.G_TRUE,
510 p_msg_index => 1,
511 p_msg_count => x_msg_count,
512 p_msg_data => x_msg_data,
513 -- p_data => x_msg_data, * Commented for Bug fix: 4537865
514 p_data => l_new_msg_data, -- added for Bug fix: 4537865
515 p_msg_index_out => l_msg_index_out );
516 -- added for Bug fix: 4537865
517 x_msg_data := l_new_msg_data;
518 -- added for Bug fix: 4537865
519 End if;
520
521 WHEN OTHERS THEN
522 FND_MSG_PUB.add_exc_msg
523 (p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS.CHECK_WP_TASK_EXISTS',
524 p_procedure_name => PA_DEBUG.G_Err_Stack );
525 RAISE;
526
527 END CHECK_WP_TASK_EXISTS;
528
529 -- API name : UPDATE_CURRENT_PHASE
530 -- Type : Utility
531 -- Pre-reqs : None.
532 -- Parameters :
533 -- p_lifecycle_phase_id IN NUMBER Required
534 -- p_proj_element_id IN NUMBER Required
535
536 PROCEDURE UPDATE_CURRENT_PHASE
537 (
538 p_lifecycle_phase_id IN NUMBER
539 ,p_proj_element_id IN NUMBER
540 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
541 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
542 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
543 IS
544 --Jul 23rd Only Workplan
545 CURSOR cur_wrkpln_attrs
546 IS
547 select ppwa.*
548 from pa_proj_workplan_attr ppwa,
549 pa_proj_elements ppe,
550 pa_proj_structure_types ppst,
551 pa_structure_types pst
552 WHERE ppwa.proj_element_id = p_proj_element_id
553 AND ppe.project_id = ppwa.project_id
554 AND ppe.proj_element_id = ppwa.proj_element_id
555 AND ppe.proj_element_id = ppst.proj_element_id
556 AND ppst.structure_type_id = pst.structure_type_id
557 AND pst.structure_type_class_code = 'WORKPLAN';
558 /*SELECT *
559 FROM PA_PROJ_WORKPLAN_ATTR
560 WHERE proj_element_id = p_proj_element_id; */
561
562 l_return_status VARCHAR2(1);
563 l_error_msg_code VARCHAR2(250);
564 l_msg_count NUMBER;
565 l_msg_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
566 l_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
567
568 l_del_lifecycle_phase_error EXCEPTION;
569 l_msg_index_out NUMBER;
570 l_cur_wrkpln_attrs_rec cur_wrkpln_attrs%ROWTYPE;
571
572 BEGIN
573
574 OPEN cur_wrkpln_attrs;
575 FETCH cur_wrkpln_attrs INTO l_cur_wrkpln_attrs_rec;
576 IF cur_wrkpln_attrs%FOUND
577 THEN
578 PA_WORKPLAN_ATTR_PUB.update_proj_workplan_attrs(
579 p_validate_only => FND_API.G_FALSE
580 ,p_project_id => l_cur_wrkpln_attrs_rec.project_id
581 ,p_proj_element_id => p_proj_element_id
582 ,p_approval_reqd_flag => l_cur_wrkpln_attrs_rec.WP_APPROVAL_REQD_FLAG
583 ,p_auto_publish_flag => l_cur_wrkpln_attrs_rec.WP_AUTO_PUBLISH_FLAG
584 ,p_approver_source_id => l_cur_wrkpln_attrs_rec.WP_APPROVER_SOURCE_ID
585 ,p_approver_source_type => l_cur_wrkpln_attrs_rec.WP_APPROVER_SOURCE_TYPE
586 ,p_default_display_lvl => l_cur_wrkpln_attrs_rec.WP_DEFAULT_DISPLAY_LVL
587 ,p_enable_wp_version_flag => l_cur_wrkpln_attrs_rec.WP_ENABLE_VERSION_FLAG
588 ,p_lifecycle_id => l_cur_wrkpln_attrs_rec.LIFECYCLE_VERSION_ID
589 ,p_current_lifecycle_phase_id => p_lifecycle_phase_id
590 ,p_auto_pub_upon_creation_flag => l_cur_wrkpln_attrs_rec.AUTO_PUB_UPON_CREATION_FLAG
591 ,p_auto_sync_txn_date_flag => l_cur_wrkpln_attrs_rec.AUTO_SYNC_TXN_DATE_FLAG
592 ,p_txn_date_sync_buf_days => l_cur_wrkpln_attrs_rec.TXN_DATE_SYNC_BUF_DAYS
593 ,p_record_version_number => l_cur_wrkpln_attrs_rec.RECORD_VERSION_NUMBER
594 ,x_return_status => l_return_status
595 ,x_msg_count => l_msg_count
596 ,x_msg_data => l_msg_data
597 );
598 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
599 x_msg_count := FND_MSG_PUB.count_msg;
600 if x_msg_count = 1 then
601 pa_interface_utils_pub.get_messages
602 (p_encoded => FND_API.G_TRUE,
603 p_msg_index => 1,
604 p_msg_count => l_msg_count,
605 p_msg_data => l_msg_data,
606 p_data => l_data,
607 p_msg_index_out => l_msg_index_out);
608 x_msg_data := l_data;
609 end if;
610 raise FND_API.G_EXC_ERROR;
611 end if;
612
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614
615 END IF;
616 CLOSE cur_wrkpln_attrs;
617
618 /* UPDATE PA_PROJ_WORKPLAN_ATTR
619 SET current_phase_version_id = p_lifecycle_phase_id
620 WHERE proj_element_id = p_proj_element_id;
621 */
622
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624 x_msg_count := 0;
625
626 EXCEPTION
627 when FND_API.G_EXC_ERROR then
628 FND_MSG_PUB.add_exc_msg
629 (p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS.UPDATE_CURRENT_PHASE',
630 p_procedure_name => PA_DEBUG.G_Err_Stack );
631 x_return_status := FND_API.G_RET_STS_ERROR;
632
633 WHEN OTHERS THEN
634 FND_MSG_PUB.add_exc_msg
635 (p_pkg_name => 'PA_WORKPLAN_ATTR_UTILS.UPDATE_CURRENT_PHASE',
636 p_procedure_name => PA_DEBUG.G_Err_Stack );
637 x_return_status := FND_API.G_RET_STS_ERROR;
638 RAISE;
639
640 END UPDATE_CURRENT_PHASE;
641
642
643 FUNCTION CHECK_APPROVAL_REQUIRED
644 ( p_project_id IN NUMBER
645 ) RETURN VARCHAR2
646 IS
647 --Jul 23rd Only Workplan
648 CURSOR get_flag IS
649 select ppwa.WP_APPROVAL_REQD_FLAG
650 from pa_proj_workplan_attr ppwa,
651 pa_proj_elements ppe,
652 pa_proj_structure_types ppst,
653 pa_structure_types pst
654 WHERE ppwa.project_id = p_project_id
655 AND ppe.project_id = ppwa.project_id
656 AND ppe.proj_element_id = ppwa.proj_element_id
657 AND ppe.proj_element_id = ppst.proj_element_id
658 AND ppst.structure_type_id = pst.structure_type_id
659 AND pst.structure_type_class_code = 'WORKPLAN';
660 /* SELECT WP_APPROVAL_REQD_FLAG
661 FROM pa_proj_workplan_attr
662 WHERE project_id = p_project_id;*/
663
664 l_flag VARCHAR2(1);
665 BEGIN
666 OPEN get_flag;
667 FETCH get_flag INTO l_flag;
668 CLOSE get_flag;
669
670 return l_flag;
671 END CHECK_APPROVAL_REQUIRED;
672
673
674 FUNCTION CHECK_AUTO_PUB_ENABLED
675 ( p_project_id IN NUMBER
676 ) RETURN VARCHAR2
677 IS
678 --Jul 23rd Only Workplan
679 CURSOR get_flag IS
680 select ppwa.WP_AUTO_PUBLISH_FLAG
681 from pa_proj_workplan_attr ppwa,
682 pa_proj_elements ppe,
683 pa_proj_structure_types ppst,
684 pa_structure_types pst
685 WHERE ppwa.project_id = p_project_id
686 AND ppe.project_id = ppwa.project_id
687 AND ppe.proj_element_id = ppwa.proj_element_id
688 AND ppe.proj_element_id = ppst.proj_element_id
689 AND ppst.structure_type_id = pst.structure_type_id
690 AND pst.structure_type_class_code = 'WORKPLAN';
691 /* SELECT WP_AUTO_PUBLISH_FLAG
692 FROM pa_proj_workplan_attr
693 WHERE project_id = p_project_id;*/
694
695 l_flag VARCHAR2(1);
696 BEGIN
697 OPEN get_flag;
698 FETCH get_flag INTO l_flag;
699 CLOSE get_flag;
700
701 return l_flag;
702 END CHECK_AUTO_PUB_ENABLED;
703
704
705 FUNCTION CHECK_AUTO_PUB_AT_CREATION
706 ( p_template_id IN NUMBER
707 ) RETURN VARCHAR2
708 IS
709 --Jul 23rd Only Workplan
710 CURSOR get_flag IS
711 select ppwa.AUTO_PUB_UPON_CREATION_FLAG
712 from pa_proj_workplan_attr ppwa,
713 pa_proj_elements ppe,
714 pa_proj_structure_types ppst,
715 pa_structure_types pst
716 WHERE ppwa.project_id = p_template_id
717 AND ppe.project_id = ppwa.project_id
718 AND ppe.proj_element_id = ppwa.proj_element_id
719 AND ppe.proj_element_id = ppst.proj_element_id
720 AND ppst.structure_type_id = pst.structure_type_id
721 AND pst.structure_type_class_code = 'WORKPLAN';
722 /* SELECT AUTO_PUB_UPON_CREATION_FLAG
723 FROM pa_proj_workplan_attr
724 WHERE project_id = p_template_id;*/
725
726 l_flag VARCHAR2(1);
727 BEGIN
728 OPEN get_flag;
729 FETCH get_flag INTO l_flag;
730 CLOSE get_flag;
731
732 return l_flag;
733 END CHECK_AUTO_PUB_AT_CREATION;
734
735
736 END PA_WORKPLAN_ATTR_UTILS;