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