[Home] [Help]
PACKAGE BODY: APPS.PA_WORKPLAN_ATTR_PVT
Source
1 PACKAGE BODY PA_WORKPLAN_ATTR_PVT AS
2 /* $Header: PAPRWPVB.pls 120.1.12010000.2 2009/07/22 12:27:38 gboomina ship $ */
3
4 -- Global constant
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_WORKPLAN_ATTR_PVT';
6
7
8 -- API name : Create_Proj_Workplan_Attrs
9 -- Type : Private
10 -- Pre-reqs : None.
11 -- Parameters :
12 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
13 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
14 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
15 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
16 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
17 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
18 -- p_project_id IN NUMBER Required
19 -- p_proj_element_id IN NUMBER Required
20 -- p_approval_reqd_flag IN VARCHAR2 Required
21 -- p_auto_publish_flag IN VARCHAR2 Required
22 -- p_approver_source_id IN NUMBER Required
23 -- p_approver_source_type IN NUMBER Required
24 -- p_default_display_lvl IN NUMBER Required
25 -- p_enable_wp_version_flag IN VARCHAR2 Required
26 -- p_auto_pub_upon_creation_flag IN VARCHAR2 Required
27 -- p_auto_sync_txn_date_flag IN VARCHAR2 Required
28 -- p_txn_date_sync_buf_days IN NUMBER Optional Default = FND_API.G_MISS_NUM
29 -- p_use_task_schedule_flag IN VARCHAR2 Optional
30 -- p_lifecycle_version_id IN NUMBER := FND_API.G_MISS_NUM
31 -- p_current_phase_version_id IN NUMBER := FND_API.G_MISS_NUM
32 -- x_return_status OUT VARCHAR2 Required
33 -- x_msg_count OUT NUMBER Required
34 -- x_msg_data OUT VARCHAR2 Optional
35
36 PROCEDURE CREATE_PROJ_WORKPLAN_ATTRS
37 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
38 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
39 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
40 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
41 ,p_debug_mode IN VARCHAR2 := 'N'
42 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
43 ,p_project_id IN NUMBER
44 ,p_proj_element_id IN NUMBER
45 ,p_approval_reqd_flag IN VARCHAR2
46 ,p_auto_publish_flag IN VARCHAR2
47 ,p_approver_source_id IN NUMBER
48 ,p_approver_source_type IN NUMBER
49 ,p_default_display_lvl IN NUMBER
50 ,p_enable_wp_version_flag IN VARCHAR2
51 ,p_auto_pub_upon_creation_flag IN VARCHAR2
52 ,p_auto_sync_txn_date_flag IN VARCHAR2
53 ,p_txn_date_sync_buf_days IN NUMBER := FND_API.G_MISS_NUM
54 ,p_lifecycle_version_id IN NUMBER := FND_API.G_MISS_NUM
55 ,p_current_phase_version_id IN NUMBER := FND_API.G_MISS_NUM
56 --bug 3325803: FP M
57 ,p_allow_lowest_tsk_dep_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
58 ,p_schedule_third_party_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
59 ,p_third_party_schedule_code IN VARCHAR2 := FND_API.G_MISS_CHAR
60 ,p_auto_rollup_subproj_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
61 --bug 3325803: FP M
62 -- gboomina added from MC-07 bug 8586393 - start
63 ,p_use_task_schedule_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
64 -- gboomina added from MC-07 bug 8586393 - end
65 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
66 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
67 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
68 )
69 IS
70 l_return_status VARCHAR2(1);
71 l_error_msg_code VARCHAR2(250);
72 l_msg_count NUMBER;
73 l_msg_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
74 l_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
75 l_msg_index_out NUMBER;
76 l_dummy VARCHAR2(1);
77
78 l_auto_publish_flag VARCHAR2(1);
79 l_approver_source_id NUMBER;
80 l_approver_source_type NUMBER;
81 l_txn_date_sync_buf_days NUMBER;
82 l_lifecycle_version_id NUMBER;
83 l_current_phase_version_id NUMBER;
84 --bug 3325803: FP M
85 l_allow_lowest_tsk_dep_flag VARCHAR2(1);
86 l_schedule_third_party_flag VARCHAR2(1);
87 l_third_party_schedule_code VARCHAR2(30);
88 l_auto_rollup_subproj_flag VARCHAR2(1);
89 --bug 3325803: FP M
90
91 -- gboomina added from MC-07 bug 8586393 - start
92 l_use_task_schedule_flag VARCHAR2(1);
93
94 CURSOR c2(p_project_id IN NUMBER) IS
95 SELECT use_task_schedule_flag
96 FROM PA_PROJ_WORKPLAN_ATTR
97 WHERE PROJECT_ID IN
98 (SELECT created_from_project_id
99 FROM pa_projects_all
100 WHERE project_id = p_project_id);
101 -- gboomina added from MC-07 bug 8586393 - end
102
103 BEGIN
104
105 if (p_debug_mode = 'Y') then
106 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Create_Proj_Workplan_Attrs BEGIN');
107 end if;
108
109 -- gboomina added from MC-07 bug 8586393 - start
110 IF (p_use_task_schedule_flag <> FND_API.G_MISS_CHAR) THEN
111 --if values is passed as parameter
112 l_use_task_schedule_flag := p_use_task_schedule_flag;
113 ELSE
114 --get value from parent project or template
115 OPEN c2(p_project_id);
116 FETCH c2 INTO l_use_task_schedule_flag;
117 CLOSE C2;
118 END IF;
119 -- gboomina added from MC-07 bug 8586393 - end
120
121 if p_commit = FND_API.G_TRUE then
122 savepoint create_proj_workplan_attrs_pvt;
123 end if;
124
125 if p_approval_reqd_flag <> 'Y' then
126 l_auto_publish_flag := 'N';
127 l_approver_source_id := NULL;
128 l_approver_source_type := NULL;
129 else
130 l_auto_publish_flag := p_auto_publish_flag;
131 l_approver_source_id := p_approver_source_id;
132 l_approver_source_type := p_approver_source_type;
133 end if;
134
135 if p_txn_date_sync_buf_days = FND_API.G_MISS_NUM THEN
136 l_txn_date_sync_buf_days := NULL;
137 else
138 l_txn_date_sync_buf_days := p_txn_date_sync_buf_days;
139 end if;
140
141 If p_lifecycle_version_id = FND_API.G_MISS_NUM THEN
142 l_lifecycle_version_id := NULL;
143 else
144 l_lifecycle_version_id := p_lifecycle_version_id;
145 end if;
146
147 IF p_current_phase_version_id = FND_API.G_MISS_NUM THEN
148 l_current_phase_version_id := NULL;
149 else
150 l_current_phase_version_id := p_current_phase_version_id;
151 end if;
152
153 --bug 3325803: FP M
154 IF (p_allow_lowest_tsk_dep_flag = FND_API.G_MISS_CHAR) THEN
155 l_allow_lowest_tsk_dep_flag := 'N';
156 ELSE
157 l_allow_lowest_tsk_dep_flag := p_allow_lowest_tsk_dep_flag;
158 END IF;
159
160 IF (p_schedule_third_party_flag = FND_API.G_MISS_CHAR) THEN
161 l_schedule_third_party_flag := 'N';
162 ELSE
163 l_schedule_third_party_flag := p_schedule_third_party_flag;
164 END IF;
165
166 IF (p_third_party_schedule_code = FND_API.G_MISS_CHAR) THEN
167 l_third_party_schedule_code := NULL;
168 ELSE
169 l_third_party_schedule_code := p_third_party_schedule_code;
170 END IF;
171
172 IF (p_auto_rollup_subproj_flag = FND_API.G_MISS_CHAR) THEN
173 --l_auto_rollup_subproj_flag := 'N';
174 l_auto_rollup_subproj_flag := 'Y';
175 ELSE
176 l_auto_rollup_subproj_flag := p_auto_rollup_subproj_flag;
177 END IF;
178 --end bug 3325803
179
180 if p_validate_only <> FND_API.G_TRUE then
181 INSERT INTO PA_PROJ_WORKPLAN_ATTR (
182 project_id
183 ,proj_element_id
184 ,wp_approval_reqd_flag
185 ,wp_auto_publish_flag
186 ,wp_approver_source_id
187 ,wp_approver_source_type
188 ,wp_default_display_lvl
189 ,wp_enable_version_flag
190 ,auto_pub_upon_creation_flag
191 ,auto_sync_txn_date_flag
192 ,txn_date_sync_buf_days
193 ,record_version_number
194 ,last_update_date
195 ,last_updated_by
196 ,creation_date
197 ,created_by
198 ,last_update_login
199 ,lifecycle_version_id
200 ,current_phase_version_id
201 ,schedule_third_party_flag
202 ,allow_lowest_tsk_dep_flag
203 ,auto_rollup_subproj_flag
204 ,third_party_schedule_code
205 ,source_object_id
206 ,source_object_type
207 ,use_task_schedule_flag) -- gboomina added for bug 8586393
208 VALUES (
209 p_project_id
210 ,p_proj_element_id
211 ,p_approval_reqd_flag
212 ,l_auto_publish_flag
213 ,l_approver_source_id
214 ,l_approver_source_type
215 ,p_default_display_lvl
216 ,p_enable_wp_version_flag
217 ,p_auto_pub_upon_creation_flag
218 ,p_auto_sync_txn_date_flag
219 ,l_txn_date_sync_buf_days
220 ,1
221 ,SYSDATE
222 ,FND_GLOBAL.USER_ID
223 ,SYSDATE
224 ,FND_GLOBAL.USER_ID
225 ,FND_GLOBAL.LOGIN_ID
226 ,l_lifecycle_version_id
227 ,l_current_phase_version_id
228 ,l_schedule_third_party_flag
229 ,l_allow_lowest_tsk_dep_flag
230 ,l_auto_rollup_subproj_flag
231 ,l_third_party_schedule_code
232 ,p_project_id
233 ,'PA_PROJECTS'
234 ,l_use_task_schedule_flag); -- gboomina added for bug 8586393
235 end if;
236
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 if p_commit = FND_API.G_TRUE then
240 commit work;
241 end if;
242
243 if (p_debug_mode = 'Y') then
244 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Create_Proj_Workplan_Attrs END');
245 end if;
246
247 EXCEPTION
248 when FND_API.G_EXC_ERROR then
249 if p_commit = FND_API.G_TRUE then
250 rollback to create_proj_workplan_attrs_pvt;
251 end if;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253 when FND_API.G_EXC_UNEXPECTED_ERROR then
254 if p_commit = FND_API.G_TRUE then
255 rollback to create_proj_workplan_attrs_pvt;
256 end if;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
259 p_procedure_name => 'Create_Proj_Workplan_Attrs',
260 p_error_text => SUBSTRB(SQLERRM,1,240));
261 when OTHERS then
262 if p_commit = FND_API.G_TRUE then
263 rollback to create_proj_workplan_attrs_pvt;
264 end if;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
267 p_procedure_name => 'Create_Proj_Workplan_Attrs',
268 p_error_text => SUBSTRB(SQLERRM,1,240));
269 raise;
270 END CREATE_PROJ_WORKPLAN_ATTRS;
271
272
273 -- API name : Update_Proj_Workplan_Attrs
274 -- Type : Private
275 -- Pre-reqs : None.
276 -- Parameters :
277 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
278 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
279 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
280 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
281 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
282 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
283 -- p_project_id IN NUMBER Required Default = FND_API.G_MISS_NUM
284 -- p_proj_element_id IN NUMBER Required Default = FND_API.G_MISS_NUM
285 -- p_approval_reqd_flag IN VARCHAR2 Required Default = FND_API.G_MISS_NUM
286 -- p_auto_publish_flag IN VARCHAR2 Required Default = FND_API.G_MISS_CHAR
287 -- p_approver_source_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
288 -- p_approver_source_type IN NUMBER Optional Default = FND_API.G_MISS_NUM
289 -- p_default_display_lvl IN NUMBER Required Default = FND_API.G_MISS_NUM
290 -- p_enable_wp_version_flag IN VARCHAR2 Required Default = FND_API.G_MISS_CHAR
291 -- p_auto_pub_upon_creation_flag IN VARCHAR2 Required Default = FND_API.G_MISS_CHAR
292 -- p_auto_sync_txn_date_flag IN VARCHAR2 Required Default = FND_API.G_MISS_CHAR
293 -- p_txn_date_sync_buf_days IN NUMBER Optional Default = FND_API.G_MISS_NUM
294 -- p_lifecycle_version_id IN NUMBER := FND_API.G_MISS_NUM
295 -- p_current_phase_version_id IN NUMBER := FND_API.G_MISS_NUM
296 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
297 -- p_use_task_schedule_flag IN NUMBER Optional
298 -- x_return_status OUT VARCHAR2 Required
299 -- x_msg_count OUT NUMBER Required
300 -- x_msg_data OUT VARCHAR2 Optional
301
302
303 PROCEDURE UPDATE_PROJ_WORKPLAN_ATTRS
304 (
305 p_commit IN VARCHAR2 := FND_API.G_FALSE
306 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
307 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
308 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
309 ,p_debug_mode IN VARCHAR2 := 'N'
310 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
311 ,p_project_id IN NUMBER := FND_API.G_MISS_NUM /* Added for Progress impact bug 3420093 */
312 ,p_proj_element_id IN NUMBER := FND_API.G_MISS_NUM /* Added for Progress impact bug 3420093 */
313 ,p_approval_reqd_flag IN VARCHAR2 := FND_API.G_MISS_CHAR /* Added for Progress impact bug 3420093 */
314 ,p_auto_publish_flag IN VARCHAR2 := FND_API.G_MISS_CHAR /* Added for Progress impact bug 3420093 */
315 ,p_approver_source_id IN NUMBER := FND_API.G_MISS_NUM /* Added for Progress impact bug 3420093 */
316 ,p_approver_source_type IN NUMBER := FND_API.G_MISS_NUM /* Added for Progress impact bug 3420093 */
317 ,p_default_display_lvl IN NUMBER := FND_API.G_MISS_NUM /* Added for Progress impact bug 3420093 */
318 ,p_enable_wp_version_flag IN VARCHAR2 := FND_API.G_MISS_CHAR /* Added for Progress impact bug 3420093 */
319 ,p_auto_pub_upon_creation_flag IN VARCHAR2 := FND_API.G_MISS_CHAR /* Added for Progress impact bug 3420093 */
320 ,p_auto_sync_txn_date_flag IN VARCHAR2 := FND_API.G_MISS_CHAR /* Added for Progress impact bug 3420093 */
321 ,p_txn_date_sync_buf_days IN NUMBER := FND_API.G_MISS_NUM
322 ,p_lifecycle_version_id IN NUMBER := FND_API.G_MISS_NUM
323 ,p_current_phase_version_id IN NUMBER := FND_API.G_MISS_NUM
324 --bug 3325803: FP M
325 ,p_allow_lowest_tsk_dep_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
326 ,p_schedule_third_party_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
327 ,p_third_party_schedule_code IN VARCHAR2 := FND_API.G_MISS_CHAR
328 ,p_auto_rollup_subproj_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
329 --bug 3325803: FP M
330 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
331 -- gboomina added for bug 8586393 - start
332 ,p_use_task_schedule_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
333 -- gboomina added for bug 8586393 - end
334 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
335 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
336 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
337 )
338 IS
339 l_return_status VARCHAR2(1);
340 l_error_msg_code VARCHAR2(250);
341 l_msg_count NUMBER;
342 l_msg_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
343 l_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
344 l_msg_index_out NUMBER;
345 l_dummy VARCHAR2(1);
346 l_clear_phase_flag VARCHAR2(1);
347
348 l_auto_publish_flag VARCHAR2(1);
349 l_approver_source_id NUMBER;
350 l_approver_source_type NUMBER;
351 l_txn_date_sync_buf_days NUMBER;
352 --LDENG
353 l_lifecycle_version_id NUMBER;
354 l_current_phase_version_id NUMBER;
355 --END LDENG
356
357 --mrajput
358 l_is_lifecycle_tracking VARCHAR2(1);
359 l_delete_ok VARCHAR2(1);
360 l_current_sequence NUMBER;
361 l_change_sequence NUMBER;
362 l_curr_phase_id NUMBER;
363 l_future_phase_id NUMBER;
364 l_lifecycle_id NUMBER;
365 l_policy_code VARCHAR2(30);
366 l_phase_change_code VARCHAR2(30);
367
368 --bug 3325803: FP M
369 l_allow_lowest_tsk_dep_flag VARCHAR2(1);
370 l_schedule_third_party_flag VARCHAR2(1);
371 l_third_party_schedule_code VARCHAR2(30);
372 l_auto_rollup_subproj_flag VARCHAR2(1);
373 --bug 3325803: FP M
374
375 -- gboomina added for bug 8586393 - start
376 l_use_task_schedule_flag VARCHAR2(1);
377 -- gboomina added for bug 8586393 - end
378
379 CURSOR c_current_display_sequence
380 IS
381 SELECT display_sequence,proj_element_id
382 FROM pa_proj_element_versions
383 WHERE element_version_id = l_current_phase_version_id;
384
385 CURSOR c_change_display_sequence
386 IS
387 SELECT display_sequence,proj_element_id
388 FROM pa_proj_element_versions
389 WHERE element_version_id = p_current_phase_version_id;
390
391 -- End mrajput
392 l_error_message VARCHAR(32); -- Bug 2760719
393
394 CURSOR c_get_struc_versions
395 IS
396 select element_version_id
397 from pa_proJ_elem_ver_structure
398 where project_id = p_project_id
399 AND proj_element_id = p_proj_element_id;
400 l_structure_version_id NUMBER;
401 l_dep_in_summary VARCHAR2(1);
402
403 BEGIN
404
405
406 if (p_debug_mode = 'Y') then
407 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Update_Proj_Workplan_Attrs BEGIN');
408 end if;
409
410 if (p_commit = FND_API.G_TRUE) then
411 savepoint update_proj_workplan_attrs_pvt;
412 end if;
413
414 --LDENG
415 l_clear_phase_flag := 'N';
416 l_lifecycle_version_id := NULL;
417 l_current_phase_version_id := NULL;
418 --END LDENG
419
420 -- mrajput added.
421 -- 18 Nov 2002. For Product Lifecycle Management through Bug2665633.
422 -- bug 3325803: Added new attributes
423 SELECT LIFECYCLE_VERSION_ID, CURRENT_PHASE_VERSION_ID,
424 schedule_third_party_flag, allow_lowest_tsk_dep_flag,
425 auto_rollup_subproj_flag, third_party_schedule_code
426 INTO l_lifecycle_version_id, l_current_phase_version_id,
427 l_schedule_third_party_flag, l_allow_lowest_tsk_dep_flag,
428 l_auto_rollup_subproj_flag, l_third_party_schedule_code
429 FROM pa_proj_workplan_attr
430 WHERE proj_element_id = p_proj_element_id
431 AND record_version_number = p_record_version_number;
432
433
434
435 PA_EGO_WRAPPER_PUB.check_lc_tracking_project(
436 p_api_version => 1.0 ,
437 p_project_id => p_project_id ,
438 x_is_lifecycle_tracking => l_is_lifecycle_tracking ,
439 x_return_status => l_return_status ,
440 x_errorcode => l_error_msg_code ,
441 x_msg_count => l_msg_count ,
442 x_msg_data => l_msg_data );
443
444
445 IF l_is_lifecycle_tracking = FND_API.G_TRUE THEN
446 IF( p_lifecycle_version_id IS NOT NULL ) and (p_lifecycle_version_id <> FND_API.G_MISS_NUM) AND (l_lifecycle_version_id IS NOT NULL) AND (l_lifecycle_version_id <> p_lifecycle_version_id) THEN
447
448 /* Bug2760719 -- Added code to populate error message */
449
450 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
451 p_msg_name => 'PA_LCYL_TRACKING_PROJ');
452
453
454 l_msg_count := FND_MSG_PUB.count_msg;
455
456 IF l_msg_count > 0 THEN
457 x_msg_count := l_msg_count;
458 IF x_msg_count = 1 THEN
459 x_msg_data := l_msg_data;
460 END IF;
461
462 raise FND_API.G_EXC_ERROR;
463 END IF;
464 END IF;
465 END IF;
466
467 --END mrajput
468
469 if p_validate_only <> FND_API.G_TRUE then
470 BEGIN
471 SELECT LIFECYCLE_VERSION_ID, CURRENT_PHASE_VERSION_ID
472 INTO l_lifecycle_version_id, l_current_phase_version_id
473 FROM pa_proj_workplan_attr
474 WHERE proj_element_id = p_proj_element_id
475 AND record_version_number = p_record_version_number
476 FOR UPDATE OF record_version_number NOWAIT;
477 EXCEPTION
478 when TIMEOUT_ON_RESOURCE then
479 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
480 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
481 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
482 when NO_DATA_FOUND then
483 if p_calling_module = 'FORM' then
484 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
485 p_msg_name => 'FORM_RECORD_CHANGED');
486 l_msg_data := 'FORM_RECORD_CHANGED';
487 else
488 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
489 p_msg_name => 'PA_XC_RECORD_CHANGED');
490 l_msg_data := 'PA_XC_RECORD_CHANGED';
491 end if;
492 when OTHERS then
493 if SQLCODE = -54 then
494 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
495 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
496 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
497 else
498 raise;
499 end if;
500 END;
501 else
502 BEGIN
503 SELECT LIFECYCLE_VERSION_ID, CURRENT_PHASE_VERSION_ID
504 INTO l_lifecycle_version_id, l_current_phase_version_id
505 FROM pa_proj_workplan_attr
506 WHERE proj_element_id = p_proj_element_id
507 AND record_version_number = p_record_version_number;
508 EXCEPTION
509 when NO_DATA_FOUND then
510 if p_calling_module = 'FORM' then
511 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
512 p_msg_name => 'FORM_RECORD_CHANGED');
513 l_msg_data := 'FORM_RECORD_CHANGED'; else
514 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
515 p_msg_name => 'PA_XC_RECORD_CHANGED');
516 l_msg_data := 'PA_XC_RECORD_CHANGED';
517 end if;
518 when OTHERS then
519 raise;
520 END;
521 end if;
522
523 l_msg_count := FND_MSG_PUB.count_msg;
524 if l_msg_count > 0 then
525 x_msg_count := l_msg_count;
526 if x_msg_count = 1 then
527 x_msg_data := l_msg_data;
528 end if;
529 raise FND_API.G_EXC_ERROR;
530 end if;
531
532 if p_approval_reqd_flag <> 'Y' then
533 l_auto_publish_flag := 'N';
534 l_approver_source_id := NULL;
535 l_approver_source_type := NULL;
536 else
537 l_auto_publish_flag := p_auto_publish_flag;
538 l_approver_source_id := p_approver_source_id;
539 l_approver_source_type := p_approver_source_type;
540 end if;
541
542 if p_txn_date_sync_buf_days = FND_API.G_MISS_NUM THEN
543 l_txn_date_sync_buf_days := NULL;
544 else
545 l_txn_date_sync_buf_days := p_txn_date_sync_buf_days;
546 end if;
547
548 --mrajput
549 -- 18 Nov 2002. For Product Lifecycle Management through Bug2665633.
550 IF (l_is_lifecycle_tracking = FND_API.G_TRUE ) THEN
551
552 -- changes for bug 2808582
553 OPEN c_current_display_sequence;
554 FETCH c_current_display_sequence into l_current_sequence,l_curr_phase_id;
555 CLOSE c_current_display_sequence;
556
557 OPEN c_change_display_sequence;
558 FETCH c_change_display_sequence into l_change_sequence,l_future_phase_id;
559 CLOSE c_change_display_sequence;
560
561 -- IF (l_current_phase_version_id is not NULL) and (p_current_phase_version_id <> FND_API.G_MISS_NUM) THEN
562 --changes for bug 2742365
563
564 -- Changes for Bug 2760719 , Added condition so that it considers the case of nulling out the phase
565
566 IF (p_current_phase_version_id <> FND_API.G_MISS_NUM OR p_current_phase_version_id is null ) THEN
567 IF ((l_current_phase_version_id IS NOT NULL ) AND (l_current_phase_version_id <> p_current_phase_version_id) AND (p_current_phase_version_id IS NOT NULL)
568 OR
569 ((l_current_phase_version_id IS NOT NULL ) AND (p_current_phase_version_id IS NULL)))THEN
570
571 /* commented for bug 2808582
572 OPEN c_current_display_sequence;
573 FETCH c_current_display_sequence into l_current_sequence,l_curr_phase_id;
574 CLOSE c_current_display_sequence;
575
576 OPEN c_change_display_sequence;
577 FETCH c_change_display_sequence into l_change_sequence,l_future_phase_id;
578 CLOSE c_change_display_sequence; */
579
580 IF(l_change_sequence > l_current_sequence) THEN
581 l_phase_change_code := 'PROMOTE';
582 ELSE
583 l_phase_change_code := 'DEMOTE';
584 END IF;
585
586 BEGIN
587 select proj_element_id into l_lifecycle_id
588 from pa_proj_element_versions
589 where element_version_id = l_lifecycle_version_id;
590 EXCEPTION
591 WHEN OTHERS THEN
592 raise FND_API.G_EXC_UNEXPECTED_ERROR;
593 END;
594
595 --hsiu
596 --bug 3254091
597 IF (p_validation_level > 50) THEN
598 PA_EGO_WRAPPER_PUB.get_policy_for_phase_change(
599 p_api_version => 1.0 ,
600 p_project_id => p_project_id , --Bug 2800909
601 p_current_phase_id => l_curr_phase_id ,
602 p_future_phase_id => l_future_phase_id ,
603 p_phase_change_code => l_phase_change_code ,
604 p_lifecycle_id => l_lifecycle_id ,
605 x_policy_code => l_policy_code ,
606 x_return_status => l_return_status ,
607 x_error_message => l_error_message , -- Bug 2760719
608 x_errorcode => l_error_msg_code ,
609 x_msg_count => l_msg_count ,
610 x_msg_data => l_msg_data );
611
612 /* Bug2760719 -- Added code to populate error message */
613
614 IF l_policy_code IN ('CHANGE_ORDER_REQUIRED','NOT_ALLOWED') THEN -- bug 3423005
615 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'EGO',
616 p_msg_name => l_error_message);
617 END IF;
618
619 IF l_policy_code IN ('CHANGE_ORDER_REQUIRED','NOT_ALLOWED') OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN -- bug 3423005
620 l_msg_count := FND_MSG_PUB.count_msg;
621 If l_msg_count > 0 THEN
622 x_msg_count := l_msg_count;
623 If l_msg_count = 1 THEN
624 pa_interface_utils_pub.get_messages
625 (p_encoded => FND_API.G_TRUE ,
626 p_msg_index => 1 ,
627 p_msg_count => l_msg_count ,
628 p_msg_data => l_msg_data ,
629 p_data => l_data ,
630 p_msg_index_out => l_msg_index_out
631 );
632 x_msg_data := l_data;
633 End if;
634 End if;
635 RAISE FND_API.G_EXC_ERROR;
636 END IF;
637 end if;
638 --end bug 3254091
639
640 END IF;
641
642 END IF;
643 END IF;
644 ---END mrajput
645
646 --LDENG
647 IF (l_lifecycle_version_id is not NULL) and (p_lifecycle_version_id <> FND_API.G_MISS_NUM or p_lifecycle_version_id is null) THEN
648 IF(p_lifecycle_version_id is null or l_lifecycle_version_id <> p_lifecycle_version_id) THEN
649 l_clear_phase_flag := 'Y';
650 END IF;
651 END IF;
652
653 If p_lifecycle_version_id <> FND_API.G_MISS_NUM or p_lifecycle_version_id is null THEN
654 l_lifecycle_version_id := p_lifecycle_version_id;
655 end if;
656
657 IF p_current_phase_version_id <> FND_API.G_MISS_NUM or p_current_phase_version_id is null THEN
658 l_current_phase_version_id := p_current_phase_version_id;
659 end if;
660 --END LDENG
661
662 IF (p_enable_wp_version_flag <>
663 PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(p_project_id)) THEN
664 PA_PROJECT_STRUCTURE_PUB1.update_workplan_versioning(
665 p_proj_element_id => p_proj_element_id
666 ,p_enable_wp_version_flag => p_enable_wp_version_flag
667 ,x_return_status => l_return_status
668 ,x_msg_count => l_msg_count
669 ,x_msg_data => l_msg_data
670 );
671
672 l_msg_count := FND_MSG_PUB.count_msg;
673 if l_msg_count > 0 then
674 x_msg_count := l_msg_count;
675 if x_msg_count = 1 then
676 x_msg_data := l_msg_data;
677 end if;
678 raise FND_API.G_EXC_ERROR;
679 end if;
680 END IF;
681
682 --bug 3305199: FP M
683 /*** bug 3305199: lowest task dep check ****/
684 IF (p_allow_lowest_tsk_dep_flag = 'Y') THEN
685 --check each structure version to see if dependency exists in summary level task
686 OPEN c_get_struc_versions;
687 LOOP
688 FETCH c_get_struc_versions INTO l_structure_version_id;
689 EXIT WHEN c_get_struc_versions%NOTFOUND;
690 l_dep_in_summary := PA_PROJECT_STRUCTURE_UTILS.Check_Struct_Has_Dep(l_structure_version_id);
691
692 IF (l_dep_in_summary = 'Y') THEN
693 CLOSE c_get_struc_versions;
694 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
695 p_msg_name => 'PA_DEP_ON_SUMM_TSK');
696 l_msg_count := FND_MSG_PUB.count_msg;
697 IF l_msg_count > 0 THEN
698 x_msg_count := l_msg_count;
699 IF x_msg_count = 1 THEN
700 x_msg_data := l_msg_data;
701 END IF;
702 raise FND_API.G_EXC_ERROR;
703 END IF;
704 END IF;
705
706 END LOOP;
707 CLOSE c_get_struc_versions;
708 END IF;
709
710 /*** bug 3305199 ****/
711
712 IF (p_allow_lowest_tsk_dep_flag <> FND_API.G_MISS_CHAR) THEN
713 l_allow_lowest_tsk_dep_flag := p_allow_lowest_tsk_dep_flag;
714 END IF;
715
716 --gboomina added for Bug 8586393 - start
717 IF (p_use_task_schedule_flag <> FND_API.G_MISS_CHAR) THEN
718 l_use_task_schedule_flag := p_use_task_schedule_flag;
719 END IF;
720 --gboomina added for Bug 8586393 - end
721
722 IF (p_schedule_third_party_flag <> FND_API.G_MISS_CHAR) THEN
723 l_schedule_third_party_flag := p_schedule_third_party_flag;
724 END IF;
725
726 IF (l_schedule_third_party_flag = 'N') THEN
727 --clear schedule dirty flag
728 update pa_proj_elem_ver_structure
729 set SCHEDULE_DIRTY_FLAG = 'N'
730 WHERE project_id = p_project_id and proj_element_id = p_proj_element_id;
731 END IF;
732
733 IF (p_third_party_schedule_code <> FND_API.G_MISS_CHAR) THEN
734 l_third_party_schedule_code := p_third_party_schedule_code;
735 END IF;
736
737 IF (p_auto_rollup_subproj_flag <> FND_API.G_MISS_CHAR) THEN
738 l_auto_rollup_subproj_flag := p_auto_rollup_subproj_flag;
739 END IF;
740 --end bug 3325803
741
742 if p_validate_only <> FND_API.G_TRUE then
743 --added condition to specify proj_element_id
744 /*
745 Modified this update for task progress bug 3420093. Added decodes with default values in following columns
746 wp_approval_reqd_flag,wp_auto_publish_flag,wp_default_display_lvl,wp_enable_version_flag,auto_pub_upon_creation_flag,
747 auto_sync_txn_date_flag.
748 */
749 --bug 3905167: added nvl for p_default_display_lvl.
750 UPDATE PA_PROJ_WORKPLAN_ATTR
751 SET wp_approval_reqd_flag = decode( p_approval_reqd_flag, FND_API.G_MISS_CHAR, wp_approval_reqd_flag, p_approval_reqd_flag ),
752 wp_auto_publish_flag = decode( l_auto_publish_flag, FND_API.G_MISS_CHAR, wp_auto_publish_flag, l_auto_publish_flag ),
753 wp_approver_source_id = l_approver_source_id,
754 wp_approver_source_type = l_approver_source_type,
755 wp_default_display_lvl = decode( p_default_display_lvl, FND_API.G_MISS_NUM, wp_default_display_lvl, nvl(p_default_display_lvl,0)),
756 wp_enable_version_flag = decode( p_enable_wp_version_flag, FND_API.G_MISS_CHAR, wp_enable_version_flag, p_enable_wp_version_flag),
757 auto_pub_upon_creation_flag = decode( p_auto_pub_upon_creation_flag, FND_API.G_MISS_CHAR, auto_pub_upon_creation_flag, p_auto_pub_upon_creation_flag ),
758 auto_sync_txn_date_flag = decode( p_auto_sync_txn_date_flag, FND_API.G_MISS_CHAR, auto_sync_txn_date_flag, p_auto_sync_txn_date_flag ),
759 txn_date_sync_buf_days = l_txn_date_sync_buf_days,
760 lifecycle_version_id = l_lifecycle_version_id,
761 current_phase_version_id = l_current_phase_version_id,
762 schedule_third_party_flag = l_schedule_third_party_flag,
763 allow_lowest_tsk_dep_flag = l_allow_lowest_tsk_dep_flag,
764 auto_rollup_subproj_flag = l_auto_rollup_subproj_flag,
765 third_party_schedule_code = l_third_party_schedule_code,
766 record_version_number = p_record_version_number + 1,
767 last_update_date = SYSDATE,
768 last_updated_by = FND_GLOBAL.USER_ID,
769 last_update_login = FND_GLOBAL.LOGIN_ID,
770 --gboomina added for Bug 8586393 - start
771 use_task_schedule_flag = l_use_task_schedule_flag
772 --gboomina added for Bug 8586393 - end
773 WHERE project_id = p_project_id and proj_element_id = p_proj_element_id;
774
775 --mrajput
776 -- 18 Nov 2002. For Product Lifecycle Management through Bug2665633.
777 ---changes for bug2742365
778 IF (l_is_lifecycle_tracking = FND_API.G_TRUE) THEN
779
780 PA_EGO_WRAPPER_PUB.sync_phase_change(
781 p_api_version => 1.0 ,
782 p_project_id => p_project_id ,
783 p_lifecycle_id => l_lifecycle_id ,
784 p_phase_id => l_future_phase_id ,
785 p_effective_date => sysdate ,
786 p_commit => p_commit ,
787 x_errorcode => l_error_msg_code ,
788 x_msg_count => l_msg_count ,
789 x_return_status => l_return_status ,
790 x_msg_data => l_msg_data );
791
792 /* Bug 2760719 -- Added code to show the error message */
793
794 l_msg_count := FND_MSG_PUB.count_msg;
795 if l_msg_count > 0 then
796 x_msg_count := l_msg_count;
797 if x_msg_count = 1 then
798 x_msg_data := l_msg_data;
799 end if;
800 raise FND_API.G_EXC_ERROR;
801 end if;
802 END IF;
803
804
805 -- END mrajput
806
807 --LDENG
808 IF (l_clear_phase_flag = 'Y') THEN
809 UPDATE PA_PROJ_ELEMENTS
810 SET phase_version_id = null
811 WHERE project_id = p_project_id
812 AND phase_version_id IS NOT NULL;
813 end if;
814 --END LDENG
815 end if;
816
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818
819 if p_commit = FND_API.G_TRUE then
820 commit work;
821 end if;
822
823 if (p_debug_mode = 'Y') then
824 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Update_Proj_Workplan_Attrs END');
825 end if;
826
827 EXCEPTION
828 when FND_API.G_EXC_ERROR then
829 if p_commit = FND_API.G_TRUE then
830 rollback to update_proj_workplan_attrs_pvt;
831 end if;
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 when FND_API.G_EXC_UNEXPECTED_ERROR then
834 if p_commit = FND_API.G_TRUE then
835 rollback to update_proj_workplan_attrs_pvt;
836 end if;
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
839 p_procedure_name => 'Update_Proj_Workplan_Attrs',
840 p_error_text => SUBSTRB(SQLERRM,1,240));
841 when OTHERS then
842 if p_commit = FND_API.G_TRUE then
843 rollback to update_proj_workplan_attrs_pvt;
844 end if;
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
846 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
847 p_procedure_name => 'Update_Proj_Workplan_Attrs',
848 p_error_text => SUBSTRB(SQLERRM,1,240));
849 raise;
850 END UPDATE_PROJ_WORKPLAN_ATTRS;
851
852
853 -- API name : Update_Structure_Name
854 -- Type : Private
855 -- Pre-reqs : None.
856 -- Parameters :
857 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
858 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
859 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
860 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
861 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
862 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
863 -- p_proj_element_id IN NUMBER Required
864 -- p_structure_name IN VARCHAR2 Required
865 -- p_record_version_number IN NUMBER Optional Default = FND_API.G_MISS_NUM
866 -- x_return_status OUT VARCHAR2 Required
867 -- x_msg_count OUT NUMBER Required
868 -- x_msg_data OUT VARCHAR2 Optional
869
870 PROCEDURE UPDATE_STRUCTURE_NAME
871 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
872 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
873 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
874 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
875 ,p_debug_mode IN VARCHAR2 := 'N'
876 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
877 ,p_proj_element_id IN NUMBER
878 ,p_structure_name IN VARCHAR2
879 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
880 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
881 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
882 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
883 )
884 IS
885 l_return_status VARCHAR2(1);
886 l_error_msg_code VARCHAR2(250);
887 l_msg_count NUMBER;
888 l_msg_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
889 l_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
890 l_msg_index_out NUMBER;
891 l_dummy VARCHAR2(1);
892 BEGIN
893
894 if (p_debug_mode = 'Y') then
895 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Update_Structure_Name BEGIN');
896 end if;
897
898 if p_commit = FND_API.G_TRUE then
899 savepoint update_structure_name_pvt;
900 end if;
901
902 if p_validate_only <> FND_API.G_TRUE then
903 BEGIN
904 SELECT 'x' INTO l_dummy
905 FROM pa_proj_elements
906 WHERE proj_element_id = p_proj_element_id
907 FOR UPDATE OF record_version_number NOWAIT;
908 EXCEPTION
909 when TIMEOUT_ON_RESOURCE then
910 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
911 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
912 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
913 when NO_DATA_FOUND then
914 if p_calling_module = 'FORM' then
915 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
916 p_msg_name => 'FORM_RECORD_CHANGED');
917 l_msg_data := 'FORM_RECORD_CHANGED';
918 else
919 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
920 p_msg_name => 'PA_XC_RECORD_CHANGED');
921 l_msg_data := 'PA_XC_RECORD_CHANGED';
922 end if;
923 when OTHERS then
924 if SQLCODE = -54 then
925 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
926 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
927 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
928 else
929 raise;
930 end if;
931 END;
932 else
933 BEGIN
934 SELECT 'x' INTO l_dummy
935 FROM pa_proj_elements
936 WHERE proj_element_id = p_proj_element_id;
937 EXCEPTION
938 when NO_DATA_FOUND then
939 if p_calling_module = 'FORM' then
940 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
941 p_msg_name => 'FORM_RECORD_CHANGED');
942 l_msg_data := 'FORM_RECORD_CHANGED';
943 else
944 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
945 p_msg_name => 'PA_XC_RECORD_CHANGED');
946 l_msg_data := 'PA_XC_RECORD_CHANGED';
947 end if;
948 when OTHERS then
949 raise;
950 END;
951 end if;
952
953 l_msg_count := FND_MSG_PUB.count_msg;
954 if l_msg_count > 0 then
955 x_msg_count := l_msg_count;
956 if x_msg_count = 1 then
957 x_msg_data := l_msg_data;
958 end if;
959 raise FND_API.G_EXC_ERROR;
960 end if;
961
962 if p_validate_only <> FND_API.G_TRUE then
963 UPDATE PA_PROJ_ELEMENTS
964 SET name = p_structure_name,
965 record_version_number = record_version_number + 1,
966 last_update_date = SYSDATE,
967 last_updated_by = FND_GLOBAL.USER_ID,
968 last_update_login = FND_GLOBAL.LOGIN_ID
969 WHERE proj_element_id = p_proj_element_id;
970 end if;
971
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 if p_commit = FND_API.G_TRUE then
975 commit work;
976 end if;
977
978 if (p_debug_mode = 'Y') then
979 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Update_Structure_Name END');
980 end if;
981
982 EXCEPTION
983 when FND_API.G_EXC_ERROR then
984 if p_commit = FND_API.G_TRUE then
985 rollback to update_structure_name_pvt;
986 end if;
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 when FND_API.G_EXC_UNEXPECTED_ERROR then
989 if p_commit = FND_API.G_TRUE then
990 rollback to update_structure_name_pvt;
991 end if;
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
993 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
994 p_procedure_name => 'Update_Structure_Name',
995 p_error_text => SUBSTRB(SQLERRM,1,240));
996 when OTHERS then
997 if p_commit = FND_API.G_TRUE then
998 rollback to update_structure_name_pvt;
999 end if;
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
1002 p_procedure_name => 'Update_Structure_Name',
1003 p_error_text => SUBSTRB(SQLERRM,1,240));
1004 raise;
1005 END UPDATE_STRUCTURE_NAME;
1006
1007
1008 -- API name : Delete_Proj_Workplan_Attrs
1009 -- Type : Private
1010 -- Pre-reqs : None.
1011 -- Parameters :
1012 -- p_commit IN VARCHAR2 Required Default = FND_API.G_FALSE
1013 -- p_validate_only IN VARCHAR2 Required Default = FND_API.G_TRUE
1014 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1015 -- p_calling_module IN VARCHAR2 Optional Default = 'SELF_SERVICE'
1016 -- p_debug_mode IN VARCHAR2 Optional Default = 'N'
1017 -- p_max_msg_count IN NUMBER Optional Default = FND_API.G_MISS_NUM
1018 -- p_project_id IN NUMBER Required
1019 -- p_proj_element_id IN NUMBER Required
1020 -- p_record_version_number IN NUMBER Required Default = FND_API.G_MISS_NUM
1021 -- x_return_status OUT VARCHAR2 Required
1022 -- x_msg_count OUT NUMBER Required
1023 -- x_msg_data OUT VARCHAR2 Optional
1024
1025 PROCEDURE DELETE_PROJ_WORKPLAN_ATTRS
1026 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1027 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1028 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1029 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1030 ,p_debug_mode IN VARCHAR2 := 'N'
1031 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1032 ,p_project_id IN NUMBER
1033 ,p_proj_element_id IN NUMBER
1034 ,p_record_version_number IN NUMBER := FND_API.G_MISS_NUM
1035 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1036 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1037 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1038 )
1039 IS
1040 l_return_status VARCHAR2(1);
1041 l_msg_count NUMBER;
1042 l_msg_data VARCHAR2(2000); --precision changed from 250 to 2000 for bug 4093600
1043 l_rowid VARCHAR2(250);
1044 l_dummy VARCHAR2(1);
1045
1046 BEGIN
1047 if (p_debug_mode = 'Y') then
1048 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Delete_Proj_Workplan_Attrs BEGIN');
1049 end if;
1050
1051 if p_commit = FND_API.G_TRUE then
1052 savepoint delete_proj_workplan_attrs_pvt;
1053 end if;
1054
1055 if (p_debug_mode = 'Y') then
1056 pa_debug.debug('Locking record...');
1057 end if;
1058
1059 if p_validate_only <> FND_API.G_TRUE then
1060 BEGIN
1061 SELECT 'x' INTO l_dummy
1062 FROM pa_proj_workplan_attr
1063 WHERE proj_element_id = p_proj_element_id
1064 AND record_version_number = p_record_version_number
1065 FOR UPDATE OF record_version_number NOWAIT;
1066 EXCEPTION
1067 when TIMEOUT_ON_RESOURCE then
1068 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1069 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1070 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1071 when NO_DATA_FOUND then
1072 if p_calling_module = 'FORM' then
1073 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1074 p_msg_name => 'FORM_RECORD_CHANGED');
1075 l_msg_data := 'FORM_RECORD_CHANGED';
1076 else
1077 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1078 p_msg_name => 'PA_XC_RECORD_CHANGED');
1079 l_msg_data := 'PA_XC_RECORD_CHANGED';
1080 end if;
1081 when OTHERS then
1082 if SQLCODE = -54 then
1083 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1084 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1085 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1086 else
1087 raise;
1088 end if;
1089 END;
1090 else
1091 BEGIN
1092 SELECT 'x' INTO l_dummy
1093 FROM pa_proj_workplan_attr
1094 WHERE proj_element_id = p_proj_element_id
1095 AND record_version_number = p_record_version_number;
1096 EXCEPTION
1097 when NO_DATA_FOUND then
1098 if p_calling_module = 'FORM' then
1099 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'FND',
1100 p_msg_name => 'FORM_RECORD_CHANGED');
1101 l_msg_data := 'FORM_RECORD_CHANGED';
1102 else
1103 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1104 p_msg_name => 'PA_XC_RECORD_CHANGED');
1105 l_msg_data := 'PA_XC_RECORD_CHANGED';
1106 end if;
1107 when OTHERS then
1108 raise;
1109 END;
1110 end if;
1111
1112 l_msg_count := FND_MSG_PUB.count_msg;
1113 if l_msg_count > 0 then
1114 x_msg_count := l_msg_count;
1115 if x_msg_count = 1 then
1116 x_msg_data := l_msg_data;
1117 end if;
1118 raise FND_API.G_EXC_ERROR;
1119 end if;
1120
1121 if p_validate_only <> FND_API.G_TRUE then
1122
1123 DELETE FROM PA_PROJ_WORKPLAN_ATTR
1124 WHERE proj_element_id = p_proj_element_id;
1125
1126 end if;
1127
1128 x_return_status := FND_API.G_RET_STS_SUCCESS;
1129
1130 if p_commit = FND_API.G_TRUE then
1131 commit work;
1132 end if;
1133
1134 if (p_debug_mode = 'Y') then
1135 pa_debug.debug('PA_WORKPLAN_ATTR_PVT.Delete_Proj_Workplan_Attrs END');
1136 end if;
1137
1138 EXCEPTION
1139 when FND_API.G_EXC_ERROR then
1140 if p_commit = FND_API.G_TRUE then
1141 rollback to delete_proj_workplan_attrs_pvt;
1142 end if;
1143 x_return_status := FND_API.G_RET_STS_ERROR;
1144 when others then
1145 if p_commit = FND_API.G_TRUE then
1146 rollback to delete_proj_workplan_attrs_pvt;
1147 end if;
1148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1149 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_WORKPLAN_ATTR_PVT',
1150 p_procedure_name => 'Delete_Proj_Workplan_Attrs',
1151 p_error_text => SUBSTRB(SQLERRM,1,240));
1152 raise;
1153 END DELETE_PROJ_WORKPLAN_ATTRS;
1154
1155 END PA_WORKPLAN_ATTR_PVT;