[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_STRUCTURE_PUB1
Source
1 package body PA_PROJECT_STRUCTURE_PUB1 as
2 /*$Header: PAXSTCPB.pls 120.8 2007/07/10 05:57:14 kkorada ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJECT_STRUCTURE_PUB1';
5
6 FUNCTION CHECK_ACTION_ALLOWED
7 ( p_action IN VARCHAR2
8 ,p_version_id IN NUMBER
9 ,p_status_code IN VARCHAR2
10 ) RETURN VARCHAR2
11 IS
12 /* Bug 2680486 -- Performance changes, Added c_project_id in the following cursor parameter and added condition accordingly */
13 CURSOR get_user_locked_status_csr(c_version_id NUMBER, c_user_id NUMBER, c_project_id NUMBER)
14 IS
15 SELECT 'Y'
16 FROM PA_PROJ_ELEM_VER_STRUCTURE pevs,
17 FND_USER fu
18 WHERE pevs.element_version_id = c_version_id
19 AND pevs.lock_status_code = 'LOCKED'
20 AND fu.user_id = c_user_id
21 AND pevs.locked_by_person_id = fu.employee_id
22 AND pevs.project_id = c_project_id;
23
24 /* Bug 2680486 -- Performance changes, Added c_project_id in the following cursor parameter and added condition accordingly */
25
26 CURSOR get_locked_status_csr(c_version_id NUMBER, c_project_id NUMBER)
27 IS
28 SELECT 'Y'
29 FROM PA_PROJ_ELEM_VER_STRUCTURE
30 WHERE element_version_id = c_version_id
31 AND lock_status_code = 'LOCKED'
32 AND project_id = c_project_id;
33
34 CURSOR get_published_status_csr(c_version_id NUMBER, c_project_id NUMBER)
35 IS
36 SELECT 'Y'
37 FROM PA_PROJ_ELEM_VER_STRUCTURE
38 WHERE element_version_id = c_version_id
39 AND published_date is not null
40 AND project_id = c_project_id;
41
42 CURSOR get_project_id_csr(c_version_id NUMBER)
43 IS
44 SELECT project_id
45 FROM PA_PROJ_ELEMENT_VERSIONS
46 WHERE element_version_id = c_version_id;
47
48 x_ret_code VARCHAR2(1) := fnd_api.g_false;
49 l_dummy VARCHAR2(1) := null;
50 l_project_id NUMBER;
51 l_ret_code VARCHAR2(250);
52 l_return_status VARCHAR2(1);
53 l_msg_count NUMBER;
54 l_msg_data VARCHAR2(2000);
55 l_user_id NUMBER;
56 l_approval_opt VARCHAR2(1);
57 BEGIN
58 l_user_id := pa_project_structure_pub1.GetGlobalUserId();
59 if l_user_id is null then
60 return fnd_api.g_false;
61 end if;
62
63 /* Bug 2680486 -- Performance changes -- Added the following cursor call to get the project_id,
64 which will be used in further queries to improve performance */
65 OPEN get_project_id_csr(p_version_id);
66 FETCH get_project_id_csr INTO l_project_id;
67 CLOSE get_project_id_csr;
68
69
70 if p_action = 'EDIT_TASK' then
71 if p_status_code = 'STRUCTURE_WORKING' then
72 OPEN get_user_locked_status_csr(p_version_id, l_user_id, l_project_id);
73 FETCH get_user_locked_status_csr INTO l_dummy;
74 if get_user_locked_status_csr%FOUND then
75 x_ret_code := fnd_api.g_true;
76 else
77 x_ret_code := fnd_api.g_false;
78 end if;
79 CLOSE get_user_locked_status_csr;
80 else
81 x_ret_code := fnd_api.g_false;
82 end if;
83 elsif p_action = 'EDIT_TASK_STRUCT' then
84 if p_status_code = 'STRUCTURE_WORKING' then
85 OPEN get_user_locked_status_csr(p_version_id, l_user_id, l_project_id);
86 FETCH get_user_locked_status_csr INTO l_dummy;
87 if get_user_locked_status_csr%FOUND then
88 x_ret_code := fnd_api.g_true;
89 else
90 x_ret_code := fnd_api.g_false;
91 end if;
92 CLOSE get_user_locked_status_csr;
93 else
94 x_ret_code := fnd_api.g_false;
95 end if;
96 elsif p_action = 'PUBLISH' then
97 /* Bug 2680486 -- Performance changes -- Commented the following cursor call. Now it is getting call at the top */
98 /* OPEN get_project_id_csr(p_version_id);
99 FETCH get_project_id_csr INTO l_project_id;
100 CLOSE get_project_id_csr;
101 */
102
103 l_approval_opt := PA_PROJECT_STRUCTURE_UTILS.get_approval_option(l_project_id);
104
105 if l_approval_opt = 'N' then
106 if p_status_code = 'STRUCTURE_WORKING' then
107 OPEN get_locked_status_csr(p_version_id,l_project_id);
108 FETCH get_locked_status_csr INTO l_dummy;
109 if get_locked_status_csr%FOUND then
110 OPEN get_user_locked_status_csr(p_version_id, l_user_id,l_project_id);
111 FETCH get_user_locked_status_csr INTO l_dummy;
112 if get_user_locked_status_csr%NOTFOUND then
113 x_ret_code := fnd_api.g_false;
114 else
115 x_ret_code := fnd_api.g_true;
116 end if;
117 CLOSE get_user_locked_status_csr;
118 else
119 x_ret_code := fnd_api.g_true;
120 end if;
121 CLOSE get_locked_status_csr;
122 else
123 x_ret_code := fnd_api.g_false;
124 end if;
125 else
126 if p_status_code = 'STRUCTURE_APPROVED' then
127 OPEN get_locked_status_csr(p_version_id,l_project_id);
128 FETCH get_locked_status_csr INTO l_dummy;
129 if get_locked_status_csr%FOUND then
130 OPEN get_user_locked_status_csr(p_version_id, l_user_id,l_project_id);
131 FETCH get_user_locked_status_csr INTO l_dummy;
132 if get_user_locked_status_csr%NOTFOUND then
133 x_ret_code := fnd_api.g_false;
134 else
135 x_ret_code := fnd_api.g_true;
136 end if;
137 CLOSE get_user_locked_status_csr;
138 else
139 x_ret_code := fnd_api.g_true;
140 end if;
141 CLOSE get_locked_status_csr;
142 else
143 x_ret_code := fnd_api.g_false;
144 end if;
145 end if;
146 elsif p_action = 'REWORK' then
147 if p_status_code in ('STRUCTURE_SUBMITTED', 'STRUCTURE_APPROVED', 'STRUCTURE_REJECTED') then
148 x_ret_code := fnd_api.g_true;
149 else
150 x_ret_code := fnd_api.g_false;
151 end if;
152 elsif p_action = 'LOCK' then
153 if p_status_code = 'STRUCTURE_WORKING' then
154 OPEN get_locked_status_csr(p_version_id,l_project_id);
155 FETCH get_locked_status_csr INTO l_dummy;
156 if get_locked_status_csr%FOUND then
157 x_ret_code := fnd_api.g_false;
158 else
159 x_ret_code := fnd_api.g_true;
160 end if;
161 else
162 x_ret_code := fnd_api.g_false;
163 end if;
164 elsif p_action = 'UNLOCK' then
165 if p_status_code = 'STRUCTURE_WORKING' then
166 OPEN get_user_locked_status_csr(p_version_id, l_user_id,l_project_id);
167 FETCH get_user_locked_status_csr INTO l_dummy;
168 if get_user_locked_status_csr%FOUND then
169 CLOSE get_user_locked_status_csr;
170 x_ret_code := fnd_api.g_true;
171 else
172 CLOSE get_user_locked_status_csr;
173
174 OPEN get_locked_status_csr(p_version_id,l_project_id);
175 FETCH get_locked_status_csr INTO l_dummy;
176 if get_locked_status_csr%FOUND then
177
178 /* Bug 2680486 -- Performance changes -- Commented the following query. We have obtained project_id at the top itself.*/
179 /* SELECT project_id
180 INTO l_project_id
181 FROM PA_PROJ_ELEM_VER_STRUCTURE
182 WHERE element_version_id = p_version_id;
183 */
184
185 PA_SECURITY_PVT.check_user_privilege
186 ( 'PA_UNLOCK_ANY_STRUCTURE'
187 ,'PA_PROJECTS'
188 ,l_project_id
189 ,l_ret_code
190 ,l_return_status
191 ,l_msg_count
192 ,l_msg_data);
193
194 if l_ret_code = fnd_api.g_true then
195 x_ret_code := fnd_api.g_true;
196 else
197 x_ret_code := fnd_api.g_false;
198 end if;
199 else
200 x_ret_code := fnd_api.g_false;
201 end if;
202 end if;
203 else
204 x_ret_code := fnd_api.g_false;
205 end if;
206 elsif p_action = 'OBSOLETE' then
207 OPEN get_published_status_csr(p_version_id,l_project_id);
208 FETCH get_published_status_csr INTO l_dummy;
209 if get_published_status_csr%FOUND then
210 x_ret_code := fnd_api.g_true;
211 else
212 x_ret_code := fnd_api.g_false;
213 end if;
214 CLOSE get_published_status_csr;
215 end if;
216
217 return x_ret_code;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 x_ret_code := fnd_api.g_false;
222 return x_ret_code;
223
224 END CHECK_ACTION_ALLOWED;
225
226
227 PROCEDURE SetGlobalUserId ( p_user_id NUMBER )
228 IS
229 BEGIN
230 pa_project_structure_pub1.global_user_id := p_user_id;
231 END SetGlobalUserId;
232
233
234 FUNCTION GetGlobalUserId RETURN NUMBER
235 IS
236 BEGIN
237 RETURN ( pa_project_structure_pub1.global_user_id );
238 END GetGlobalUserId;
239
240
241 -- API name : Create_Structure
242 -- Type : Public Procedure
243 -- Pre-reqs : None
244 -- Return Value : N/A
245 -- Parameters
246 -- p_api_version IN NUMBER := 1.0
247 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
248 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
249 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
250 -- p_validation_level IN VARCHAR2 := 100
251 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
252 -- p_debug_mode IN VARCHAR2 := 'N'
253 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
254 -- p_project_id IN NUMBER
255 -- p_structure_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
256 -- p_structure_name IN VARCHAR2
257 -- p_calling_flag IN VARCHAR2 := 'WORKPLAN'
258 -- p_structure_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
259 -- p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
260 -- p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
261 -- p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
262 -- p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
263 -- p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
264 -- p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
265 -- p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
266 -- p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
267 -- p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
268 -- p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
269 -- p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
270 -- p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
271 -- p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
272 -- p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
273 -- p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
274 -- p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
275 -- x_structure_id OUT NUMBER
276 -- x_return_status OUT VARCHAR2
277 -- x_msg_count OUT NUMBER
278 -- x_msg_data OUT VARCHAR2
279 --
280 -- History
281 --
282 -- 25-JUN-01 HSIU -Created
283 --
284 --
285
286
287 procedure Create_Structure
288 (
289 p_api_version IN NUMBER := 1.0
290 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
291 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
292 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
293 ,p_validation_level IN VARCHAR2 := 100
294 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
295 ,p_debug_mode IN VARCHAR2 := 'N'
296 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
297 ,p_project_id IN NUMBER
298 ,p_structure_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
299 ,p_structure_name IN VARCHAR2
300 ,p_calling_flag IN VARCHAR2 := 'WORKPLAN'
301 ,p_structure_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
302 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
303 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
304 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
305 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
306 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
307 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
308 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
309 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
310 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
311 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
312 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
313 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
314 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
315 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
316 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
317 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
318 ,p_approval_reqd_flag IN VARCHAR2 := 'N'
319 ,p_auto_publish_flag IN VARCHAR2 := 'N'
320 ,p_approver_source_id IN NUMBER := FND_API.G_MISS_NUM
321 ,p_approver_source_type IN NUMBER := FND_API.G_MISS_NUM
322 ,p_default_display_lvl IN NUMBER := 0
323 ,p_enable_wp_version_flag IN VARCHAR2 := 'N'
324 ,p_auto_pub_upon_creation_flag IN VARCHAR2 := 'N'
325 ,p_auto_sync_txn_date_flag IN VARCHAR2 := 'N'
326 ,p_txn_date_sync_buf_days IN NUMBER := FND_API.G_MISS_NUM
327 --LDENG
328 ,p_lifecycle_version_id IN NUMBER := FND_API.G_MISS_NUM
329 ,p_current_phase_version_id IN NUMBER := FND_API.G_MISS_NUM
330 --END LDENG
331 ,p_progress_cycle_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
332 ,p_wq_enable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
333 ,p_remain_effort_enable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
334 ,p_percent_comp_enable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
335 ,p_next_progress_update_date IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
336 ,p_action_set_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
337 ,x_structure_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
338 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
339 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
340 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
341 )
342 IS
343 l_api_name CONSTANT VARCHAR(30) := 'CREATE_STRUCTURE';
344 l_api_version CONSTANT NUMBER := 1.0;
345
346 l_structure_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
347 l_return_status VARCHAR2(1);
348 l_msg_count NUMBER;
349 l_msg_data VARCHAR2(250);
350 l_data VARCHAR2(250);
351 l_msg_index_out NUMBER;
352
353
354 BEGIN
355 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE');
356
357 IF (p_debug_mode = 'Y') THEN
358 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE begin');
359 END IF;
360
361 IF (p_commit = FND_API.G_TRUE) THEN
362 savepoint create_structure;
363 END IF;
364
365 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367 END IF;
368
369
370 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
371 FND_MSG_PUB.initialize;
372 END IF;
373
374
375 PA_PROJECT_STRUCTURE_PVT1.Create_Structure(
376 p_api_version => p_api_version
377 ,p_init_msg_list => p_init_msg_list
378 ,p_commit => p_commit
379 ,p_validate_only => p_validate_only
380 ,p_validation_level => p_validation_level
381 ,p_calling_module => p_calling_module
382 ,p_debug_mode => p_debug_mode
383 ,p_max_msg_count => p_max_msg_count
384 ,p_project_id => p_project_id
385 ,p_structure_number => p_structure_number
386 ,p_structure_name => p_structure_name
387 ,p_calling_flag => p_calling_flag
388 ,p_structure_description => p_structure_description
389 ,p_attribute_category => p_attribute_category
390 ,p_attribute1 => p_attribute1
391 ,p_attribute2 => p_attribute2
392 ,p_attribute3 => p_attribute3
393 ,p_attribute4 => p_attribute4
394 ,p_attribute5 => p_attribute5
395 ,p_attribute6 => p_attribute6
396 ,p_attribute7 => p_attribute7
397 ,p_attribute8 => p_attribute8
398 ,p_attribute9 => p_attribute9
399 ,p_attribute10 => p_attribute10
400 ,p_attribute11 => p_attribute11
401 ,p_attribute12 => p_attribute12
402 ,p_attribute13 => p_attribute13
403 ,p_attribute14 => p_attribute14
404 ,p_attribute15 => p_attribute15
405 ,p_approval_reqd_flag => p_approval_reqd_flag
406 ,p_auto_publish_flag => p_auto_publish_flag
407 ,p_approver_source_id => p_approver_source_id
408 ,p_approver_source_type => p_approver_source_type
409 ,p_default_display_lvl => p_default_display_lvl
410 ,p_enable_wp_version_flag => p_enable_wp_version_flag
411 ,p_auto_pub_upon_creation_flag => p_auto_pub_upon_creation_flag
412 ,p_auto_sync_txn_date_flag => p_auto_sync_txn_date_flag
413 ,p_txn_date_sync_buf_days => p_txn_date_sync_buf_days
414 --LDENG
415 ,p_lifecycle_version_id => p_lifecycle_version_id
416 ,p_current_phase_version_id => p_current_phase_version_id
417 --END LDENG
418 ,p_progress_cycle_id => p_progress_cycle_id
419 ,p_wq_enable_flag => p_wq_enable_flag
420 ,p_remain_effort_enable_flag => p_remain_effort_enable_flag
421 ,p_percent_comp_enable_flag => p_percent_comp_enable_flag
422 ,p_next_progress_update_date => p_next_progress_update_date
423 ,p_action_set_id => p_action_set_id
424 ,x_structure_id => l_structure_id
425 ,x_return_status => l_return_status
426 ,x_msg_count => l_msg_count
427 ,x_msg_data => l_msg_data
428 );
429
430 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
431 x_msg_count := FND_MSG_PUB.count_msg;
432 IF x_msg_count = 1 then
433 pa_interface_utils_pub.get_messages
434 (p_encoded => FND_API.G_TRUE,
435 p_msg_index => 1,
436 -- p_msg_count => l_msg_count,
437 -- p_msg_data => l_msg_data,
438 p_data => l_data,
439 p_msg_index_out => l_msg_index_out);
440 x_msg_data := l_data;
441 END IF;
442 raise FND_API.G_EXC_ERROR;
443 END IF;
444
445 x_structure_id := l_structure_id;
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447
448 IF (p_commit = FND_API.G_TRUE) THEN
449 COMMIT;
450 END IF;
451
452 IF (p_debug_mode = 'Y') THEN
453 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE END');
454 END IF;
455
456 EXCEPTION
457 when FND_API.G_EXC_ERROR then
458 if p_commit = FND_API.G_TRUE then
459 rollback to create_structure;
460 end if;
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 when FND_API.G_EXC_UNEXPECTED_ERROR then
463 if p_commit = FND_API.G_TRUE then
464 rollback to create_structure;
465 end if;
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
468 p_procedure_name => 'CREATE_STRUCTURE',
469 p_error_text => SUBSTRB(SQLERRM,1,240));
470 when OTHERS then
471 if p_commit = FND_API.G_TRUE then
472 rollback to create_structure;
473 end if;
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
476 p_procedure_name => 'CREATE_STRUCTURE',
477 p_error_text => SUBSTRB(SQLERRM,1,240));
478 raise;
479 END CREATE_STRUCTURE;
480
481
482 -- API name : Create_Structure_Version
483 -- Type : Public Procedure
484 -- Pre-reqs : None
485 -- Return Value : N/A
486 -- Parameters
487 -- p_api_version IN NUMBER := 1.0
488 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
489 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
490 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
491 -- p_validation_level IN VARCHAR2 := 100
492 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
493 -- p_debug_mode IN VARCHAR2 := 'N'
494 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
495 -- p_structure_id IN NUMBER
496 -- p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
497 -- p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
498 -- p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
499 -- p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
500 -- p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
501 -- p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
502 -- p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
503 -- p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
504 -- p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
505 -- p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
506 -- p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
507 -- p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
508 -- p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
509 -- p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
510 -- p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
511 -- p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
512 -- x_structure_version_id OUT NUMBER
513 -- x_return_status OUT VARCHAR2
514 -- x_msg_count OUT NUMBER
515 -- x_msg_data OUT VARCHAR2
516 --
517 -- History
518 --
519 -- 25-JUN-01 HSIU -Created
520 --
521 --
522
523
524 procedure Create_Structure_Version
525 (
526 p_api_version IN NUMBER := 1.0
527 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
528 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
529 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
530 ,p_validation_level IN VARCHAR2 := 100
531 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
532 ,p_debug_mode IN VARCHAR2 := 'N'
533 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
534 ,p_structure_id IN NUMBER
535 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
536 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
537 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
538 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
539 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
540 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
541 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
542 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
543 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
544 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
545 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
546 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
547 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
548 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
549 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
550 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
551 ,x_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
552 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
553 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
554 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
555 )
556 IS
557 l_api_name CONSTANT VARCHAR(30) := 'CREATE_STRUCTURE_VERSION';
558 l_api_version CONSTANT NUMBER := 1.0;
559
560 l_structure_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
561 l_return_status VARCHAR2(1);
562 l_msg_count NUMBER;
563 l_msg_data VARCHAR2(250);
564 l_data VARCHAR2(250);
565 l_msg_index_out NUMBER;
566
567 BEGIN
568 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE_VERSION');
569
570 IF (p_debug_mode = 'Y') THEN
571 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE_VERSION begin');
572 END IF;
573
574 IF (p_commit = FND_API.G_TRUE) THEN
575 savepoint create_structure_version;
576 END IF;
577
578 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END IF;
581
582 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
583 FND_MSG_PUB.initialize;
584 END IF;
585
586 PA_PROJECT_STRUCTURE_PVT1.Create_Structure_Version(
587 p_api_version => p_api_version
588 ,p_init_msg_list => p_init_msg_list
589 ,p_commit => p_commit
590 ,p_validate_only => p_validate_only
591 ,p_validation_level => p_validation_level
592 ,p_calling_module => p_calling_module
593 ,p_debug_mode => p_debug_mode
594 ,p_max_msg_count => p_max_msg_count
595 ,p_structure_id => p_structure_id
596 ,p_attribute_category => p_attribute_category
597 ,p_attribute1 => p_attribute1
598 ,p_attribute2 => p_attribute2
599 ,p_attribute3 => p_attribute3
600 ,p_attribute4 => p_attribute4
601 ,p_attribute5 => p_attribute5
602 ,p_attribute6 => p_attribute6
603 ,p_attribute7 => p_attribute7
604 ,p_attribute8 => p_attribute8
605 ,p_attribute9 => p_attribute9
606 ,p_attribute10 => p_attribute10
607 ,p_attribute11 => p_attribute11
608 ,p_attribute12 => p_attribute12
609 ,p_attribute13 => p_attribute13
610 ,p_attribute14 => p_attribute14
611 ,p_attribute15 => p_attribute15
612 ,x_structure_version_id => l_structure_version_id
613 ,x_return_status => l_return_status
614 ,x_msg_count => l_msg_count
615 ,x_msg_data => l_msg_data
616 );
617
618 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
619 x_msg_count := FND_MSG_PUB.count_msg;
620 IF x_msg_count = 1 then
621 pa_interface_utils_pub.get_messages
622 (p_encoded => FND_API.G_TRUE,
623 p_msg_index => 1,
624 -- p_msg_count => l_msg_count,
625 -- p_msg_data => l_msg_data,
626 p_data => l_data,
627 p_msg_index_out => l_msg_index_out);
628 x_msg_data := l_data;
629 END IF;
630 raise FND_API.G_EXC_ERROR;
631 END IF;
632
633 x_structure_version_id := l_structure_version_id;
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 IF (p_commit = FND_API.G_TRUE) THEN
637 COMMIT;
638 END IF;
639
640 IF (p_debug_mode = 'Y') THEN
641 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE_VERSION END');
642 END IF;
643
644 EXCEPTION
645 when FND_API.G_EXC_ERROR then
646 if p_commit = FND_API.G_TRUE then
647 rollback to create_structure_version;
648 end if;
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 when FND_API.G_EXC_UNEXPECTED_ERROR then
651 if p_commit = FND_API.G_TRUE then
652 rollback to create_structure_version;
653 end if;
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
656 p_procedure_name => 'CREATE_STRUCTURE_VERSION',
657 p_error_text => SUBSTRB(SQLERRM,1,240));
658 when OTHERS then
659 if p_commit = FND_API.G_TRUE then
660 rollback to create_structure_version;
661 end if;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
664 p_procedure_name => 'CREATE_STRUCTURE_VERSION',
665 p_error_text => SUBSTRB(SQLERRM,1,240));
666 raise;
667 END CREATE_STRUCTURE_VERSION;
668
669
670 -- API name : Create_Structure_Version_Attr
671 -- Type : Public Procedure
672 -- Pre-reqs : None
673 -- Return Value : N/A
674 -- Parameters
675 -- p_api_version IN NUMBER := 1.0
676 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
677 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
678 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
679 -- p_validation_level IN VARCHAR2 := 100
680 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
681 -- p_debug_mode IN VARCHAR2 := 'N'
682 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
683 -- p_structure_version_id IN NUMBER
684 -- p_structure_version_name IN VARCHAR2
685 -- p_structure_version_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
686 -- p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
687 -- p_latest_eff_published_flag IN VARCHAR2 := 'N'
688 -- p_published_flag IN VARCHAR2 := 'N'
689 -- p_locked_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
690 -- p_struct_version_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
691 -- p_baseline_current_flag IN VARCHAR2 := 'N'
692 -- p_baseline_original_flag IN VARCHAR2 := 'N'
693 -- x_pev_structure_id OUT NUMBER
694 -- x_return_status OUT VARCHAR2
695 -- x_msg_count OUT NUMBER
696 -- x_msg_data OUT VARCHAR2
697 --
698 -- History
699 --
700 -- 25-JUN-01 HSIU -Created
701 --
702 --
703
704
705 procedure Create_Structure_Version_Attr
706 (
707 p_api_version IN NUMBER := 1.0
708 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
709 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
710 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
711 ,p_validation_level IN VARCHAR2 := 100
712 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
713 ,p_debug_mode IN VARCHAR2 := 'N'
714 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
715 ,p_structure_version_id IN NUMBER
716 ,p_structure_version_name IN VARCHAR2
717 ,p_structure_version_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
718 ,p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
719 ,p_latest_eff_published_flag IN VARCHAR2 := 'N'
720 ,p_published_flag IN VARCHAR2 := 'N'
721 ,p_locked_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
722 ,p_struct_version_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
723 ,p_baseline_current_flag IN VARCHAR2 := 'N'
724 ,p_baseline_original_flag IN VARCHAR2 := 'N'
725 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
726 ,x_pev_structure_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
727 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
728 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
729 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
730 )
731 IS
732 l_api_name CONSTANT VARCHAR(30) := 'CREATE_STRUCTURE_VERSION_ATTR';
733 l_api_version CONSTANT NUMBER := 1.0;
734
735 l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
736 l_return_status VARCHAR2(1);
737 l_msg_count NUMBER;
738 l_msg_data VARCHAR2(250);
739 l_data VARCHAR2(250);
740 l_msg_index_out NUMBER;
741
742 BEGIN
743 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE_VERSION_ATTR');
744
745 IF (p_debug_mode = 'Y') THEN
746 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE_VERSION_ATTR begin');
747 END IF;
748
749 IF (p_commit = FND_API.G_TRUE) THEN
750 savepoint create_structure_version_attr;
751 END IF;
752
753 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
755 END IF;
756
757 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
758 FND_MSG_PUB.initialize;
759 END IF;
760
761 PA_PROJECT_STRUCTURE_PVT1.Create_Structure_Version_Attr(
762 p_api_version => p_api_version
763 ,p_init_msg_list => p_init_msg_list
764 ,p_commit => p_commit
765 ,p_validate_only => p_validate_only
766 ,p_validation_level => p_validation_level
767 ,p_calling_module => p_calling_module
768 ,p_debug_mode => p_debug_mode
769 ,p_max_msg_count => p_max_msg_count
770 ,p_structure_version_id => p_structure_version_id
771 ,p_structure_version_name => p_structure_version_name
772 ,p_structure_version_desc => p_structure_version_desc
773 ,p_effective_date => p_effective_date
774 ,p_latest_eff_published_flag => p_latest_eff_published_flag
775 ,p_published_flag => p_published_flag
776 ,p_locked_status_code => p_locked_status_code
777 ,p_struct_version_status_code => p_struct_version_status_code
778 ,p_baseline_current_flag => p_baseline_current_flag
779 ,p_baseline_original_flag => p_baseline_original_flag
780 ,p_change_reason_code => p_change_reason_code
781 ,x_pev_structure_id => l_pev_structure_id
782 ,x_return_status => l_return_status
783 ,x_msg_count => l_msg_count
784 ,x_msg_data => l_msg_data
785 );
786
787 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
788 x_msg_count := FND_MSG_PUB.count_msg;
789 IF x_msg_count = 1 then
790 pa_interface_utils_pub.get_messages
791 (p_encoded => FND_API.G_TRUE,
792 p_msg_index => 1,
793 -- p_msg_count => l_msg_count,
794 -- p_msg_data => l_msg_data,
795 p_data => l_data,
796 p_msg_index_out => l_msg_index_out);
797 x_msg_data := l_data;
798 END IF;
799 raise FND_API.G_EXC_ERROR;
800 END IF;
801
802 x_pev_structure_id := l_pev_structure_id;
803 x_return_status := FND_API.G_RET_STS_SUCCESS;
804
805 IF (p_commit = FND_API.G_TRUE) THEN
806 COMMIT;
807 END IF;
808
809 IF (p_debug_mode = 'Y') THEN
810 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CREATE_STRUCTURE_VERSION_ATTR END');
811 END IF;
812
813 EXCEPTION
814 when FND_API.G_EXC_ERROR then
815 if p_commit = FND_API.G_TRUE then
816 rollback to create_structure_version_attr;
817 end if;
818 x_return_status := FND_API.G_RET_STS_ERROR;
819 when FND_API.G_EXC_UNEXPECTED_ERROR then
820 if p_commit = FND_API.G_TRUE then
821 rollback to create_structure_version_attr;
822 end if;
823
824 -- 4537865
825 x_msg_count := 1 ;
826 x_msg_data := SUBSTRB(SQLERRM,1,240);
827 -- 4537865
828
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
831 p_procedure_name => 'CREATE_STRUCTURE_VERSION_ATTR',
832 p_error_text => x_msg_data); -- 4537865
833 when OTHERS then
834 if p_commit = FND_API.G_TRUE then
835 rollback to create_structure_version_attr;
836 end if;
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838
839 -- 4537865
840 x_msg_count := 1 ;
841 x_msg_data := SUBSTRB(SQLERRM,1,240);
842 -- 4537865
843
844 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
845 p_procedure_name => 'CREATE_STRUCTURE_VERSION_ATTR',
846 p_error_text => x_msg_data); -- 4537865
847 raise;
848 END CREATE_STRUCTURE_VERSION_ATTR;
849
850
851 -- API name : Update_Structure
852 -- Type : Public Procedure
853 -- Pre-reqs : None
854 -- Return Value : N/A
855 -- Parameters
856 -- p_api_version IN NUMBER := 1.0
857 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
858 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
859 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
860 -- p_validation_level IN VARCHAR2 := 100
861 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
862 -- p_debug_mode IN VARCHAR2 := 'N'
863 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
864 -- p_structure_id IN NUMBER
865 -- p_structure_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
866 -- p_structure_name IN VARCHAR2
867 -- p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
868 -- p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
869 -- p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
870 -- p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
871 -- p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
872 -- p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
873 -- p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
874 -- p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
875 -- p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
876 -- p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
877 -- p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
878 -- p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
879 -- p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
880 -- p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
881 -- p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
882 -- p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
883 -- p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
884 -- p_record_version_number IN NUMBER
885 -- x_return_status OUT VARCHAR2
886 -- x_msg_count OUT NUMBER
887 -- x_msg_data OUT VARCHAR2
888 --
889 -- History
890 --
891 -- 25-JUN-01 HSIU -Created
892 --
893 --
894
895
896 procedure Update_Structure
897 (
898 p_api_version IN NUMBER := 1.0
899 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
900 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
901 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
902 ,p_validation_level IN VARCHAR2 := 100
903 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
904 ,p_debug_mode IN VARCHAR2 := 'N'
905 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
906 ,p_structure_id IN NUMBER
907 ,p_structure_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
908 ,p_structure_name IN VARCHAR2
909 ,p_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
910 ,p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
911 ,p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
912 ,p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
913 ,p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
914 ,p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
915 ,p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
916 ,p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
917 ,p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
918 ,p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
919 ,p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
920 ,p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
921 ,p_attribute11 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
922 ,p_attribute12 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
923 ,p_attribute13 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
924 ,p_attribute14 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
925 ,p_attribute15 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
926 ,p_record_version_number IN NUMBER
927 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
928 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
929 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
930 )
931 IS
932 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_STRUCTURE';
933 l_api_version CONSTANT NUMBER := 1.0;
934
935 l_return_status VARCHAR2(1);
936 l_msg_count NUMBER;
937 l_msg_data VARCHAR2(250);
938 l_data VARCHAR2(250);
939 l_msg_index_out NUMBER;
940
941 l_dummy VARCHAR2(1);
942
943
944 BEGIN
945 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE');
946
947 IF (p_debug_mode = 'Y') THEN
948 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE begin');
949 END IF;
950
951 IF (p_commit = FND_API.G_TRUE) THEN
952 savepoint update_structure;
953 END IF;
954
955 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957 END IF;
958
959 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
960 FND_MSG_PUB.initialize;
961 END IF;
962
963 --Lock row
964 IF( p_validate_only <> FND_API.G_TRUE) THEN
965 BEGIN
966 select 'x' into l_dummy
967 from PA_PROJ_ELEMENTS
968 where proj_element_id = p_structure_id
969 and record_version_number = p_record_version_number
970 for update of record_version_number NOWAIT;
971 EXCEPTION
972 WHEN TIMEOUT_ON_RESOURCE THEN
973 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
974 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
975 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
976 WHEN NO_DATA_FOUND THEN
977 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
978 p_msg_name => 'PA_XC_RECORD_CHANGED');
979 l_msg_data := 'PA_XC_RECORD_CHANGED';
980 WHEN OTHERS THEN
981 IF SQLCODE = -54 then
982 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
983 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
984 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
985 ELSE
986 raise;
987 END IF;
988 END;
989 ELSE
990 --check record_version_number
991 BEGIN
992 select 'x' into l_dummy
993 from PA_PROJ_ELEMENTS
994 where proj_element_id = p_structure_id
995 and record_version_number = p_record_version_number;
996 EXCEPTION
997 WHEN NO_DATA_FOUND THEN
998 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
999 p_msg_name => 'PA_XC_RECORD_CHANGED');
1000 l_msg_data := 'PA_XC_RECORD_CHANGED';
1001 WHEN OTHERS THEN
1002 RAISE;
1003 END;
1004 END IF;
1005
1006 --check if there is error
1007 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1008 x_msg_count := FND_MSG_PUB.count_msg;
1009 IF x_msg_count = 1 then
1010 pa_interface_utils_pub.get_messages
1011 (p_encoded => FND_API.G_TRUE,
1012 p_msg_index => 1,
1013 -- p_msg_count => l_msg_count,
1014 -- p_msg_data => l_msg_data,
1015 p_data => l_data,
1016 p_msg_index_out => l_msg_index_out);
1017 x_msg_data := l_data;
1018 END IF;
1019 raise FND_API.G_EXC_ERROR;
1020 END IF;
1021 PA_PROJECT_STRUCTURE_PVT1.Update_Structure(
1022 p_api_version => p_api_version
1023 ,p_init_msg_list => p_init_msg_list
1024 ,p_commit => p_commit
1025 ,p_validate_only => p_validate_only
1026 ,p_validation_level => p_validation_level
1027 ,p_calling_module => p_calling_module
1028 ,p_debug_mode => p_debug_mode
1029 ,p_max_msg_count => p_max_msg_count
1030 ,p_structure_id => p_structure_id
1031 ,p_structure_number => p_structure_number
1032 ,p_structure_name => p_structure_name
1033 ,p_description => p_description
1034 ,p_attribute_category => p_attribute_category
1035 ,p_attribute1 => p_attribute1
1036 ,p_attribute2 => p_attribute2
1037 ,p_attribute3 => p_attribute3
1038 ,p_attribute4 => p_attribute4
1039 ,p_attribute5 => p_attribute5
1040 ,p_attribute6 => p_attribute6
1041 ,p_attribute7 => p_attribute7
1042 ,p_attribute8 => p_attribute8
1043 ,p_attribute9 => p_attribute9
1044 ,p_attribute10 => p_attribute10
1045 ,p_attribute11 => p_attribute11
1046 ,p_attribute12 => p_attribute12
1047 ,p_attribute13 => p_attribute13
1048 ,p_attribute14 => p_attribute14
1049 ,p_attribute15 => p_attribute15
1050 ,p_record_version_number => p_record_version_number
1051 ,x_return_status => x_return_status
1052 ,x_msg_count => x_msg_count
1053 ,x_msg_data => x_msg_data
1054 );
1055
1056 -- 4537865 : Wrong check was made against l_return_status. Corrected it to x_return_status
1057 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1058 x_msg_count := FND_MSG_PUB.count_msg;
1059 IF x_msg_count = 1 then
1060 pa_interface_utils_pub.get_messages
1061 (p_encoded => FND_API.G_TRUE,
1062 p_msg_index => 1,
1063 -- p_msg_count => l_msg_count,
1064 -- p_msg_data => l_msg_data,
1065 p_data => l_data,
1066 p_msg_index_out => l_msg_index_out);
1067 x_msg_data := l_data;
1068 END IF;
1069 raise FND_API.G_EXC_ERROR;
1070 END IF;
1071
1072 x_return_status := FND_API.G_RET_STS_SUCCESS;
1073
1074 IF (p_commit = FND_API.G_TRUE) THEN
1075 COMMIT;
1076 END IF;
1077
1078 IF (p_debug_mode = 'Y') THEN
1079 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE end');
1080 END IF;
1081
1082 EXCEPTION
1083 when FND_API.G_EXC_ERROR then
1084 if p_commit = FND_API.G_TRUE then
1085 rollback to update_structure;
1086 end if;
1087 x_return_status := FND_API.G_RET_STS_ERROR;
1088 when FND_API.G_EXC_UNEXPECTED_ERROR then
1089 if p_commit = FND_API.G_TRUE then
1090 rollback to update_structure;
1091 end if;
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093
1094 -- 4537865
1095 x_msg_count := 1 ;
1096 x_msg_data := SUBSTRB(SQLERRM,1,240);
1097 -- 4537865
1098
1099 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
1100 p_procedure_name => 'UPDATE_STRUCTURE',
1101 p_error_text => x_msg_data); -- 4537865
1102 when OTHERS then
1103 if p_commit = FND_API.G_TRUE then
1104 rollback to update_structure;
1105 end if;
1106
1107 -- 4537865
1108 x_msg_count := 1 ;
1109 x_msg_data := SUBSTRB(SQLERRM,1,240);
1110 -- 4537865
1111
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
1114 p_procedure_name => 'UPDATE_STRUCTURE',
1115 p_error_text => x_msg_data); -- 4537865
1116 raise;
1117 END UPDATE_STRUCTURE;
1118
1119
1120 -- API name : Update_Structure_Version_Attr
1121 -- Type : Public Procedure
1122 -- Pre-reqs : None
1123 -- Return Value : N/A
1124 -- Parameters
1125 -- p_api_version IN NUMBER := 1.0
1126 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1127 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1128 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1129 -- p_validation_level IN VARCHAR2 := 100
1130 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1131 -- p_debug_mode IN VARCHAR2 := 'N'
1132 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1133 -- p_pev_structure_id IN NUMBER
1134 -- p_structure_version_name IN VARCHAR2
1135 -- p_structure_version_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1136 -- p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1137 -- p_latest_eff_published_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1138 -- p_locked_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1139 -- p_struct_version_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1140 -- p_baseline_current_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1141 -- p_baseline_original_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1142 -- p_record_version_number IN NUMBER
1143 -- p_current_working_ver_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1144 -- x_return_status OUT VARCHAR2
1145 -- x_msg_count OUT NUMBER
1146 -- x_msg_data OUT VARCHAR2
1147 --
1148 -- History
1149 --
1150 -- 25-JUN-01 HSIU -Created
1151 --
1152 --
1153
1154
1155 procedure Update_Structure_Version_Attr
1156 (
1157 p_api_version IN NUMBER := 1.0
1158 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1159 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1160 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1161 ,p_validation_level IN VARCHAR2 := 100
1162 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1163 ,p_debug_mode IN VARCHAR2 := 'N'
1164 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1165 ,p_pev_structure_id IN NUMBER
1166 ,p_structure_version_name IN VARCHAR2
1167 ,p_structure_version_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1168 ,p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1169 ,p_latest_eff_published_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1170 ,p_locked_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1171 ,p_struct_version_status_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1172 ,p_baseline_current_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1173 ,p_baseline_original_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1174 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1175 ,p_record_version_number IN NUMBER
1176 --FP M changes bug 3301192
1177 ,p_current_working_ver_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1178 --end FP M changes bug 3301192
1179 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1180 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1181 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1182 )
1183 IS
1184 l_api_name CONSTANT VARCHAR(30) := 'Update_Structure_Version_Attr';
1185 l_api_version CONSTANT NUMBER := 1.0;
1186
1187 l_return_status VARCHAR2(1);
1188 l_msg_count NUMBER;
1189 l_msg_data VARCHAR2(250);
1190 l_data VARCHAR2(250);
1191 l_msg_index_out NUMBER;
1192
1193 BEGIN
1194 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.Update_Structure_Version_Attr');
1195
1196 IF (p_debug_mode = 'Y') THEN
1197 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE_VERSION_ATTR begin');
1198 END IF;
1199
1200 IF (p_commit = FND_API.G_TRUE) THEN
1201 savepoint update_structure_version_attr;
1202 END IF;
1203
1204 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1206 END IF;
1207
1208 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1209 FND_MSG_PUB.initialize;
1210 END IF;
1211
1212 --Lock row
1213
1214 --check if there is error
1215 -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1216 -- x_msg_count := FND_MSG_PUB.count_msg;
1217 -- IF x_msg_count = 1 then
1218 -- pa_interface_utils_pub.get_messages
1219 -- (p_encoded => FND_API.G_TRUE,
1220 -- p_msg_index => 1,
1221 -- p_msg_count => l_msg_count,
1222 -- p_msg_data => l_msg_data,
1223 -- p_data => l_data,
1224 -- p_msg_index_out => l_msg_index_out);
1225 -- x_msg_data := l_data;
1226 -- END IF;
1227 -- raise FND_API.G_EXC_ERROR;
1228 -- END IF;
1229
1230 PA_PROJECT_STRUCTURE_PVT1.Update_Structure_Version_Attr(
1231 p_api_version => p_api_version
1232 ,p_init_msg_list => p_init_msg_list
1233 ,p_commit => p_commit
1234 ,p_validate_only => p_validate_only
1235 ,p_validation_level => p_validation_level
1236 ,p_calling_module => p_calling_module
1237 ,p_debug_mode => p_debug_mode
1238 ,p_max_msg_count => p_max_msg_count
1239 ,p_pev_structure_id => p_pev_structure_id
1240 ,p_structure_version_name => p_structure_version_name
1241 ,p_structure_version_desc => p_structure_version_desc
1242 ,p_effective_date => p_effective_date
1243 ,p_latest_eff_published_flag => p_latest_eff_published_flag
1244 ,p_locked_status_code => p_locked_status_code
1245 ,p_struct_version_status_code => p_struct_version_status_code
1246 ,p_baseline_current_flag => p_baseline_current_flag
1247 ,p_baseline_original_flag => p_baseline_original_flag
1248 ,p_change_reason_code => p_change_reason_code
1249 ,p_record_version_number => p_record_version_number
1250 ,p_current_working_ver_flag => p_current_working_ver_flag
1251 ,x_return_status => x_return_status
1252 ,x_msg_count => x_msg_count
1253 ,x_msg_data => x_msg_data
1254 );
1255
1256 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1257 x_msg_count := FND_MSG_PUB.count_msg;
1258 IF x_msg_count = 1 then
1259 pa_interface_utils_pub.get_messages
1260 (p_encoded => FND_API.G_TRUE,
1261 p_msg_index => 1,
1262 -- p_msg_count => l_msg_count,
1263 -- p_msg_data => l_msg_data,
1264 p_data => l_data,
1265 p_msg_index_out => l_msg_index_out);
1266 x_msg_data := l_data;
1267 END IF;
1268 raise FND_API.G_EXC_ERROR;
1269 END IF;
1270
1271 x_return_status := FND_API.G_RET_STS_SUCCESS;
1272
1273 IF (p_commit = FND_API.G_TRUE) THEN
1274 COMMIT;
1275 END IF;
1276
1277 IF (p_debug_mode = 'Y') THEN
1278 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURE_VERSION_ATTR END');
1279 END IF;
1280
1281 EXCEPTION
1282 when FND_API.G_EXC_ERROR then
1283 if p_commit = FND_API.G_TRUE then
1284 rollback to update_structure_version_attr;
1285 end if;
1286 x_return_status := FND_API.G_RET_STS_ERROR;
1287 when FND_API.G_EXC_UNEXPECTED_ERROR then
1288 if p_commit = FND_API.G_TRUE then
1289 rollback to update_structure_version_attr;
1290 end if;
1291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292 -- 4537865
1293 x_msg_count := 1 ;
1294 x_msg_data := SUBSTRB(SQLERRM,1,240);
1295 -- 4537865
1296
1297 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
1298 p_procedure_name => 'Update_Structure_Version_Attr',
1299 p_error_text => x_msg_data) ; -- 4537865
1300 when OTHERS then
1301 if p_commit = FND_API.G_TRUE then
1302 rollback to update_structure_version_attr;
1303 end if;
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1305 -- 4537865
1306 x_msg_count := 1 ;
1307 x_msg_data := SUBSTRB(SQLERRM,1,240);
1308 -- 4537865
1309
1310 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
1311 p_procedure_name => 'Update_Structure_Version_Attr',
1312 p_error_text => x_msg_data); -- 4537865
1313 raise;
1314 END Update_Structure_Version_Attr;
1315
1316
1317 -- API name : Delete_Structure_Version
1318 -- Type : Public Procedure
1319 -- Pre-reqs : None
1320 -- Return Value : N/A
1321 -- Parameters
1322 -- p_api_version IN NUMBER := 1.0
1323 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1324 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1325 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1326 -- p_validation_level IN VARCHAR2 := 100
1327 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1328 -- p_debug_mode IN VARCHAR2 := 'N'
1329 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1330 -- p_structure_version_id IN NUMBER
1331 -- p_record_version_number IN NUMBER
1332 -- x_return_status OUT VARCHAR2
1333 -- x_msg_count OUT NUMBER
1334 -- x_msg_data OUT VARCHAR2
1335 --
1336 -- History
1337 --
1338 -- 25-JUN-01 HSIU -Created
1339 --
1340 --
1341 -- 25-May-07 Ram Namburi 6046307 Enhanced this to delete published structure versions
1342 -- using this AMG API.
1343 -- 10-Jul-07 kkorada Bug # 6023347: Added a new parameter p_calling_from in Delete_Structure_Version procedure
1344
1345
1346 procedure Delete_Structure_Version
1347 (
1348 p_api_version IN NUMBER := 1.0
1349 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1350 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1351 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1352 ,p_validation_level IN VARCHAR2 := 100
1353 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1354 ,p_debug_mode IN VARCHAR2 := 'N'
1355 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1356 ,p_calling_from IN VARCHAR2 := 'XYZ' ---Added for bug 6023347
1357 ,p_structure_version_id IN NUMBER
1358 ,p_record_version_number IN NUMBER
1359 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1360 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1361 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1362 )
1363 IS
1364 l_api_name CONSTANT VARCHAR(30) := 'Delete_Structure_Version';
1365 l_api_version CONSTANT NUMBER := 1.0;
1366
1367 l_return_status VARCHAR2(1);
1368 l_msg_count NUMBER;
1369 l_msg_data VARCHAR2(250);
1370 l_data VARCHAR2(250);
1371 l_msg_index_out NUMBER;
1372
1373 l_dummy VARCHAR2(1);
1374 l_error_message_code VARCHAR2(250);
1375
1376 CURSOR get_struc_ver IS
1377 select pev.project_id, pe.proj_element_id, pe.record_version_number,
1378 pev.element_version_id, pev.record_version_number,
1379 pevs.pev_structure_id, pevs.record_version_number,
1380 pevsh.pev_schedule_id, pevsh.record_version_number,
1381 pevsh.rowid
1382 from pa_proj_elements pe,
1383 pa_proj_element_versions pev,
1384 pa_proj_elem_ver_structure pevs,
1385 pa_proj_elem_ver_schedule pevsh
1386 where pev.element_version_id = p_structure_version_id and
1387 pev.proj_element_id = pe.proj_element_id and
1388 pev.project_id = pevs.project_id and
1389 pev.element_version_id = pevs.element_version_id and
1390 pev.project_id = pevsh.project_id (+) and
1391 pev.element_version_id = pevsh.element_version_id (+);
1392
1393 CURSOR is_last_version(p_structure_id NUMBER) IS
1394 select 'N'
1395 from pa_proj_element_versions
1396 where proj_element_id = p_structure_id;
1397
1398 CURSOR get_top_tasks IS
1399 select v.element_version_id
1400 from pa_proj_element_versions v,
1401 pa_object_relationships r
1402 where v.element_version_id = r.object_id_to1
1403 and r.object_id_from1 = p_structure_version_id
1404 and r.object_type_from = 'PA_STRUCTURES';
1405
1406 cursor sel_wp_attr(c_proj_element_id NUMBER) IS
1407 select record_version_number
1408 from pa_proj_workplan_attr
1409 where proj_element_id = c_proj_element_id;
1410
1411 l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
1412 l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
1413 l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
1414 l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
1415 l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
1416 l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
1417 l_pevs_rvn PA_PROJ_ELEM_VER_STRUCTURE.RECORD_VERSION_NUMBER%TYPE;
1418 l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
1419 l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
1420 l_pevsh_rowid VARCHAR2(255);
1421
1422 l_task_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
1423 l_task_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
1424 l_wp_attr_rvn PA_PROJ_WORKPLAN_ATTR.RECORD_VERSION_NUMBER%TYPE;
1425
1426 l_parent_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.PARENT_STRUCTURE_VERSION_ID%TYPE;
1427 l_structure_type pa_structure_types.STRUCTURE_TYPE_CLASS_CODE%TYPE; --Amit
1428
1429 -- SWM/IPM enhancement merger into R12.
1430 -- Bug 6046307
1431 l_strucutre_status PA_PROJ_ELEM_VER_STRUCTURE.STATUS_CODE%TYPE;
1432 l_str SYSTEM.PA_NUM_TBL_TYPE;
1433 l_rvn SYSTEM.PA_NUM_TBL_TYPE;
1434 i BINARY_INTEGER := 0;
1435
1436
1437 BEGIN
1438 pa_debug.init_err_stack('PA_PROJECT_STRUCTURE_PUB1.Delete_Structure_Version');
1439
1440 IF (p_debug_mode = 'Y') THEN
1441 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION begin');
1442 END IF;
1443
1444 IF (p_commit = FND_API.G_TRUE) THEN
1445 savepoint delete_structure_version;
1446 END IF;
1447
1448 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451
1452 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1453 FND_MSG_PUB.initialize;
1454 END IF;
1455
1456 IF (p_debug_mode = 'Y') THEN
1457 pa_debug.debug('get structure version_info');
1458 END IF;
1459
1460 --Delete logic
1461 --Get structure version information
1462 OPEN get_struc_ver;
1463 FETCH get_struc_ver INTO l_project_id,
1464 l_proj_element_id,
1465 l_pe_rvn,
1466 l_element_version_id,
1467 l_pev_rvn,
1468 l_pev_structure_id,
1469 l_pevs_rvn,
1470 l_pev_schedule_id,
1471 l_pevsh_rvn,
1472 l_pevsh_rowid;
1473 IF (get_struc_ver%NOTFOUND) THEN
1474 CLOSE get_struc_ver;
1475 RAISE NO_DATA_FOUND;
1476 END IF;
1477 CLOSE get_struc_ver;
1478
1479 -- SWM/IPM enhancement merger into R12.
1480 -- Bug 6046307
1481 -- Exisint code deletes working structure versions only. In order to delete published versions
1482 -- we need to branch the code and call the newly created API DELETE_PUBLISHED_STRUCTURE_VER
1483 -- If the version is working then go with the existing code and if the version is published
1484 -- use the new API.
1485
1486 -- Though we are deleting a single version here the delete published version was designed for deletion
1487 -- of multiple versions. So the AMG API also can be enhanced in the future to delete multiple
1488 -- structure versions in one shot.
1489
1490 -- The only issue here is the p_commit parameter. Even if the end user wants to commit the changes
1491 -- by passing the p_commit as true I do not see any changes which commits the data.
1492 -- The following piece of code doesnt issue a commit, so I am leaving the commit responsiblity
1493 -- to the Delete_Structure_Version API.
1494 -- Ideally there should be a commit statement at the end basing on the p_commit parameter value.
1495 --
1496 -- This should be treated as a bug and should be taken care of.
1497
1498 IF l_strucutre_status = 'STRUCTURE_PUBLISHED' THEN
1499 i := i+1;
1500 l_str := SYSTEM.PA_NUM_TBL_TYPE(1);
1501 l_rvn := SYSTEM.PA_NUM_TBL_TYPE(1);
1502
1503 l_str(1) := p_structure_version_id;
1504 l_rvn(1) := p_record_version_number;
1505
1506 PA_PROJECT_STRUCTURE_PUB1.DELETE_PUBLISHED_STRUCTURE_VER(
1507 p_api_version => p_api_version
1508 ,p_init_msg_list => p_init_msg_list
1509 ,p_project_id => l_project_id
1510 ,p_structure_version_id_tbl => l_str
1511 ,p_record_version_number_tbl => l_rvn
1512 ,x_return_status => l_return_status
1513 ,x_msg_count => l_msg_count
1514 ,x_msg_data => l_msg_data);
1515
1516 -- Checking the return status.
1517 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1518 x_msg_count := FND_MSG_PUB.count_msg;
1519 IF x_msg_count = 1 then
1520 pa_interface_utils_pub.get_messages
1521 (p_encoded => FND_API.G_TRUE,
1522 p_msg_index => 1,
1523 p_data => l_data,
1524 p_msg_index_out => l_msg_index_out);
1525 x_msg_data := l_data;
1526 END IF;
1527 raise FND_API.G_EXC_ERROR;
1528 END IF;
1529
1530
1531 ELSE -- Not a published structure version. Going with the existing regular flow.
1532
1533 --Check delete structure version ok
1534 PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(l_project_id,
1535 p_structure_version_id,
1536 l_return_status,
1537 l_error_message_code);
1538 IF (l_return_status <> 'S') THEN
1539 PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
1540 x_msg_data := l_error_message_code;
1541 RAISE FND_API.G_EXC_ERROR;
1542 END IF;
1543
1544 -- 4369486 : Added this check : If this is the last working structure version,we wont be allowed to delete it.
1545
1546 IF ('N' = PA_PROJECT_STRUCTURE_UTILS.check_del_work_struc_ver_ok(p_structure_version_id)) THEN
1547 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_DEL_WK_STRUC_VER_ERR');
1548 x_msg_data := 'PA_PS_DEL_WK_STRUC_VER_ERR';
1549 RAISE FND_API.G_EXC_ERROR;
1550 END IF;
1551 -- End of Code Change for 4369486
1552
1553 --NO ERROR, call delete_task
1554 --select all top level tasks
1555 OPEN get_top_tasks;
1556 LOOP
1557 FETCH get_top_tasks into l_task_version_id;
1558 EXIT WHEN get_top_tasks%NOTFOUND;
1559 IF (p_debug_mode = 'Y') THEN
1560 pa_debug.debug('deleting task version '||to_char(l_task_version_id));
1561 END IF;
1562
1563 --Get record version number for task, as it will change everytime
1564 --a task is deleted.
1565 select record_version_number, parent_structure_version_id
1566 into l_task_rvn, l_parent_struc_ver_id
1567 from pa_proj_element_versions
1568 where element_version_id = l_task_version_id;
1569
1570 PA_TASK_PUB1.DELETE_TASK_VERSION(p_commit => 'N',
1571 p_debug_mode => p_debug_mode,
1572 p_structure_version_id => l_parent_struc_ver_id,
1573 p_task_version_id => l_task_version_id,
1574 p_record_version_number => l_task_rvn,
1575 p_called_from_api => 'DELETE_STRUCTURE_VERSION', -- Bug 3056077
1576 p_calling_from => p_calling_from, -- Bug 6023347
1577 x_return_status => l_return_status,
1578 x_msg_count => l_msg_count,
1579 x_msg_data => l_msg_data);
1580 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1581 x_msg_count := FND_MSG_PUB.count_msg;
1582 IF x_msg_count = 1 then
1583 pa_interface_utils_pub.get_messages
1584 (p_encoded => FND_API.G_TRUE,
1585 p_msg_index => 1,
1586 p_data => l_data,
1587 p_msg_index_out => l_msg_index_out);
1588 x_msg_data := l_data;
1589 END IF;
1590 CLOSE get_top_tasks;
1591 raise FND_API.G_EXC_ERROR;
1592 END IF;
1593
1594 END LOOP;
1595 CLOSE get_top_tasks;
1596
1597 IF (p_debug_mode = 'Y') THEN
1598 pa_debug.debug('done deleting tasks');
1599 END IF;
1600 --If all tasks are deleted, delete schedule if workplan
1601 --Check if this is workplan
1602
1603 -- Amit : Added IF condition below
1604 IF (PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(p_structure_version_id, 'WORKPLAN') = 'Y') THEN
1605 l_structure_type := 'WORKPLAN';
1606 ELSE
1607 l_structure_type := 'FINANCIAL';
1608 END IF;
1609
1610 If (l_structure_type = 'WORKPLAN') THEN
1611 --Structure type exists. Delete from schedule table
1612 IF (p_debug_mode = 'Y') THEN
1613 pa_debug.debug('WORKPLAN type');
1614 END IF;
1615 PA_PROJ_ELEMENT_SCH_PKG.Delete_Row(l_pevsh_rowid);
1616
1617 END IF;
1618
1619 --check for errors.
1620 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1621 x_msg_count := FND_MSG_PUB.count_msg;
1622 IF x_msg_count = 1 then
1623 pa_interface_utils_pub.get_messages
1624 (p_encoded => FND_API.G_TRUE,
1625 p_msg_index => 1,
1626 -- p_msg_count => l_msg_count,
1627 -- p_msg_data => l_msg_data,
1628 p_data => l_data,
1629 p_msg_index_out => l_msg_index_out);
1630 x_msg_data := l_data;
1631 END IF;
1632 raise FND_API.G_EXC_ERROR;
1633 END IF;
1634
1635 IF (p_debug_mode = 'Y') THEN
1636 pa_debug.debug('del structure version attr');
1637 END IF;
1638 --Delete structure version attribute
1639 PA_PROJECT_STRUCTURE_PVT1.Delete_Structure_Version_Attr(
1640 p_commit => p_commit,
1641 p_debug_mode => p_debug_mode,
1642 p_pev_structure_id => l_pev_structure_id,
1643 p_record_version_number => l_pevs_rvn,
1644 x_return_status => l_return_status,
1645 x_msg_count => l_msg_count,
1646 x_msg_data => l_msg_data);
1647 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1648 x_msg_count := FND_MSG_PUB.count_msg;
1649 IF x_msg_count = 1 then
1650 pa_interface_utils_pub.get_messages
1651 (p_encoded => FND_API.G_TRUE,
1652 p_msg_index => 1,
1653 -- p_msg_count => l_msg_count,
1654 -- p_msg_data => l_msg_data,
1655 p_data => l_data,
1656 p_msg_index_out => l_msg_index_out);
1657 x_msg_data := l_data;
1658 END IF;
1659 raise FND_API.G_EXC_ERROR;
1660 END IF;
1661
1662
1663 IF (p_debug_mode = 'Y') THEN
1664 pa_debug.debug('del structure version');
1665 END IF;
1666
1667 --Delete structure version
1668 PA_PROJECT_STRUCTURE_PVT1.Delete_Structure_Version(
1669 p_commit => p_commit,
1670 p_debug_mode => p_debug_mode,
1671 p_structure_version_id => l_element_version_id,
1672 p_record_version_number => l_pev_rvn,
1673 x_return_status => l_return_status,
1674 x_msg_count => l_msg_count,
1675 x_msg_data => l_msg_data);
1676
1677 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1678 x_msg_count := FND_MSG_PUB.count_msg;
1679 IF x_msg_count = 1 then
1680 pa_interface_utils_pub.get_messages
1681 (p_encoded => FND_API.G_TRUE,
1682 p_msg_index => 1,
1683 -- p_msg_count => l_msg_count,
1684 -- p_msg_data => l_msg_data,
1685 p_data => l_data,
1686 p_msg_index_out => l_msg_index_out);
1687 x_msg_data := l_data;
1688 END IF;
1689 raise FND_API.G_EXC_ERROR;
1690 END IF;
1691
1692
1693 --Delete structure if this is the last version
1694 OPEN is_last_version(l_proj_element_id);
1695 FETCH is_last_version into l_dummy;
1696 IF is_last_version%NOTFOUND THEN
1697 --We are deleting the last version. Delete structure
1698 IF (p_debug_mode = 'Y') THEN
1699 pa_debug.debug('delete non-versioned structure');
1700 END IF;
1701
1702 IF (PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure(l_proj_element_id, 'WORKPLAN') = 'Y') THEN
1703 --delete workplan attribute
1704 OPEN sel_wp_attr(l_proj_element_id);
1705 FETCH sel_wp_attr into l_wp_attr_rvn;
1706 CLOSE sel_wp_attr;
1707
1708 PA_WORKPLAN_ATTR_PUB.DELETE_PROJ_WORKPLAN_ATTRS(
1709 p_validate_only => FND_API.G_FALSE
1710 ,p_project_id => l_project_id
1711 ,p_proj_element_id => l_proj_element_id
1712 ,p_record_version_number => l_wp_attr_rvn
1713 ,x_return_status => l_return_status
1714 ,x_msg_count => l_msg_count
1715 ,x_msg_data => l_msg_data
1716 );
1717
1718 --Check error
1719 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1720 x_msg_count := FND_MSG_PUB.count_msg;
1721 IF x_msg_count = 1 then
1722 pa_interface_utils_pub.get_messages
1723 (p_encoded => FND_API.G_TRUE,
1724 p_msg_index => 1,
1725 p_data => l_data,
1726 p_msg_index_out => l_msg_index_out);
1727 x_msg_data := l_data;
1728 END IF;
1729 raise FND_API.G_EXC_ERROR;
1730 END IF;
1731
1732 PA_PROGRESS_PUB.DELETE_PROJ_PROG_ATTR(
1733 p_validate_only => FND_API.G_FALSE
1734 ,p_project_id => l_project_id
1735 ,P_OBJECT_TYPE => 'PA_STRUCTURES'
1736 ,p_object_id => l_proj_element_id
1737 ,p_structure_type => l_structure_type --Amit
1738 ,x_return_status => l_return_status
1739 ,x_msg_count => l_msg_count
1740 ,x_msg_data => l_msg_data
1741 );
1742
1743 --Check error
1744 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1745 x_msg_count := FND_MSG_PUB.count_msg;
1746 IF x_msg_count = 1 then
1747 pa_interface_utils_pub.get_messages
1748 (p_encoded => FND_API.G_TRUE,
1749 p_msg_index => 1,
1750 p_data => l_data,
1751 p_msg_index_out => l_msg_index_out);
1752 x_msg_data := l_data;
1753 END IF;
1754 raise FND_API.G_EXC_ERROR;
1755 END IF;
1756
1757 END IF;
1758
1759 select record_version_number into l_pe_rvn
1760 from pa_proj_elements where proj_element_id = l_proj_element_id;
1761 PA_PROJECT_STRUCTURE_PVT1.Delete_Structure(
1762 p_commit => p_commit,
1763 p_debug_mode => p_debug_mode,
1764 p_structure_id => l_proj_element_id,
1765 p_record_version_number => l_pe_rvn,
1766 x_return_status => l_return_status,
1767 x_msg_count => l_msg_count,
1768 x_msg_data => l_msg_data);
1769
1770 END IF;
1771 CLOSE is_last_version;
1772
1773 --Check error
1774 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1775 x_msg_count := FND_MSG_PUB.count_msg;
1776 IF x_msg_count = 1 then
1777 pa_interface_utils_pub.get_messages
1778 (p_encoded => FND_API.G_TRUE,
1779 p_msg_index => 1,
1780 -- p_msg_count => l_msg_count,
1781 -- p_msg_data => l_msg_data,
1782 p_data => l_data,
1783 p_msg_index_out => l_msg_index_out);
1784 x_msg_data := l_data;
1785 END IF;
1786 raise FND_API.G_EXC_ERROR;
1787 END IF;
1788
1789
1790 x_return_status := FND_API.G_RET_STS_SUCCESS;
1791
1792 IF (p_debug_mode = 'Y') THEN
1793 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION end');
1794 END IF;
1795
1796 END IF; -- End of IF l_strucutre_status = 'STRUCTURE_PUBLISHED' THEN
1797
1798 EXCEPTION
1799 when FND_API.G_EXC_ERROR then
1800 if p_commit = FND_API.G_TRUE then
1801 rollback to delete_structure_version;
1802 end if;
1803 x_msg_count := FND_MSG_PUB.count_msg;
1804 x_return_status := FND_API.G_RET_STS_ERROR;
1805 when FND_API.G_EXC_UNEXPECTED_ERROR then
1806 if p_commit = FND_API.G_TRUE then
1807 rollback to delete_structure_version;
1808 end if;
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1810 -- 4537865
1811 x_msg_count := 1 ;
1812 x_msg_data := SUBSTRB(SQLERRM,1,240);
1813 -- 4537865
1814
1815 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
1816 p_procedure_name => 'Delete_Structure_Version',
1817 p_error_text => x_msg_data); -- 4537865
1818 when OTHERS then
1819 if p_commit = FND_API.G_TRUE then
1820 rollback to delete_structure_version;
1821 end if;
1822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1823 -- 4537865
1824 x_msg_count := 1 ;
1825 x_msg_data := SUBSTRB(SQLERRM,1,240);
1826 -- 4537865
1827
1828 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
1829 p_procedure_name => 'Delete_Structure_Version',
1830 p_error_text => x_msg_data); -- 4537865
1831 raise;
1832 END DELETE_STRUCTURE_VERSION;
1833
1834
1835 -- API name : Publish_Structure
1836 -- Type : Public Procedure
1837 -- Pre-reqs : None
1838 -- Return Value : N/A
1839 -- Parameters
1840 -- p_api_version IN NUMBER := 1.0
1841 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1842 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1843 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1844 -- p_validation_level IN VARCHAR2 := 100
1845 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1846 -- p_debug_mode IN VARCHAR2 := 'N'
1847 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1848 -- p_responsibility_id IN NUMBER := 0
1849 -- p_structure_version_id IN NUMBER
1850 -- p_publish_structure_ver_name IN VARCHAR2
1851 -- p_structure_ver_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1852 -- p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1853 -- p_original_baseline_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1854 -- p_current_baseline_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1855 -- x_published_struct_ver_id OUT NUMBER
1856 -- x_return_status OUT VARCHAR2
1857 -- x_msg_count OUT NUMBER
1858 -- x_msg_data OUT VARCHAR2
1859 --
1860 -- History
1861 --
1862 -- 25-JUN-01 HSIU -Created
1863 --
1864 --
1865
1866
1867 procedure Publish_Structure
1868 (
1869 p_api_version IN NUMBER := 1.0
1870 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1871 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1872 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1873 ,p_validation_level IN VARCHAR2 := 100
1874 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1875 ,p_debug_mode IN VARCHAR2 := 'N'
1876 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1877 ,p_responsibility_id IN NUMBER := 0
1878 ,p_user_id IN NUMBER := NULL
1879 ,p_structure_version_id IN NUMBER
1880 ,p_publish_structure_ver_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1881 ,p_structure_ver_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1882 ,p_effective_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
1883 ,p_original_baseline_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1884 ,p_current_baseline_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
1885 ,p_pub_prog_flag IN VARCHAR2 DEFAULT 'Y' -- Added for FP_M changes 3420093
1886 ,x_published_struct_ver_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1887 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1888 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1889 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1890 )
1891 IS
1892 l_api_name CONSTANT VARCHAR(30) := 'Publish_Structure';
1893 l_api_version CONSTANT NUMBER := 1.0;
1894
1895 l_return_status VARCHAR2(1);
1896 l_msg_count NUMBER;
1897 l_msg_data VARCHAR2(2000);--Bug 5059828. Changed the precision to 2000
1898 l_data VARCHAR2(2000);--Bug 5059828. Changed the precision to 2000
1899 l_msg_index_out NUMBER;
1900 l_project_id NUMBER;
1901 l_record_version_number NUMBER;
1902
1903 cursor c2(c_project_id NUMBER, c_structure_version_id NUMBER) IS
1904 select record_version_number
1905 from pa_proj_elem_ver_structure
1906 where project_id = c_project_id
1907 and element_version_id = c_structure_version_id;
1908
1909 CURSOR c1 IS
1910 select project_id
1911 from pa_proj_element_versions
1912 where element_version_id = p_structure_version_id;
1913
1914 BEGIN
1915 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.PUBLISH_STRUCTURE');
1916
1917 IF (p_debug_mode = 'Y') THEN
1918 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.PUBLISH_STRUCTURE begin');
1919 END IF;
1920
1921 IF (p_commit = FND_API.G_TRUE) THEN
1922 savepoint publish_structure;
1923 END IF;
1924
1925 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 END IF;
1928
1929 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1930 FND_MSG_PUB.initialize;
1931 END IF;
1932
1933 --Check if this structure can be published (ie, if linked structures are published)
1934 IF ('Y' <> PA_PROJECT_STRUCTURE_UTILS.Check_Publish_Struc_Ver_Ok(p_structure_version_id)) THEN
1935 PA_UTILS.ADD_MESSAGE('PA','PA_PS_OTHER_WORKING_LOCKED');
1936 x_msg_data := 'PA_PS_OTHER_WORKING_LOCKED';
1937 RAISE FND_API.G_EXC_ERROR;
1938 END IF;
1939
1940
1941 --Call private API
1942 PA_PROJECT_STRUCTURE_PVT1.Publish_Structure(
1943 p_api_version => p_api_version
1944 ,p_init_msg_list => p_init_msg_list
1945 ,p_commit => p_commit
1946 ,p_validate_only => p_validate_only
1947 ,p_validation_level => p_validation_level
1948 ,p_calling_module => p_calling_module
1949 ,p_debug_mode => p_debug_mode
1950 ,p_max_msg_count => p_max_msg_count
1951 ,p_responsibility_id => p_responsibility_id
1952 ,p_user_id => p_user_id
1953 ,p_structure_version_id => p_structure_version_id
1954 ,p_publish_structure_ver_name => p_publish_structure_ver_name
1955 ,p_structure_ver_desc => p_structure_ver_desc
1956 ,p_effective_date => p_effective_date
1957 ,p_original_baseline_flag => p_original_baseline_flag
1958 ,p_current_baseline_flag => p_current_baseline_flag
1959 ,p_pub_prog_flag => p_pub_prog_flag
1960 ,x_published_struct_ver_id => x_published_struct_ver_id
1961 ,x_return_status => l_return_status
1962 ,x_msg_count => l_msg_count
1963 ,x_msg_data => l_msg_data
1964 );
1965
1966 -- error_msg('public --> '||l_return_status||l_msg_count||l_msg_data);
1967
1968 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1969 x_msg_count := FND_MSG_PUB.count_msg;
1970 IF x_msg_count = 1 then
1971 pa_interface_utils_pub.get_messages
1972 (p_encoded => FND_API.G_TRUE,
1973 p_msg_index => 1,
1974 -- p_msg_count => l_msg_count,
1975 -- p_msg_data => l_msg_data,
1976 p_data => l_data,
1977 p_msg_index_out => l_msg_index_out);
1978 x_msg_data := l_data;
1979 -- error_msg('public --> '||x_msg_data||', '||l_msg_data);
1980 END IF;
1981 raise FND_API.G_EXC_ERROR;
1982 END IF;
1983
1984 --start workflow
1985 OPEN c1;
1986 FETCH c1 into l_project_id;
1987 CLOSE c1;
1988
1989 OPEN c2(l_project_id, x_published_struct_ver_id);
1990 FETCH c2 into l_record_version_number;
1991 CLOSE c2;
1992
1993 -- FP M : 3491609 : Project Execution Workflow
1994 PA_PROJECT_STRUCTURE_PVT1.change_workplan_status
1995 (
1996 p_project_id => l_project_id
1997 ,p_structure_version_id => x_published_struct_ver_id
1998 ,p_status_code => 'STRUCTURE_PUBLISHED'
1999 ,p_record_version_number => l_record_version_number
2000 ,x_return_status => l_return_status
2001 ,x_msg_count => l_msg_count
2002 ,x_msg_data => l_msg_data
2003 );
2004
2005 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2006 x_msg_count := FND_MSG_PUB.count_msg;
2007 IF x_msg_count = 1 then
2008 pa_interface_utils_pub.get_messages
2009 (p_encoded => FND_API.G_TRUE,
2010 p_msg_index => 1,
2011 -- p_msg_count => l_msg_count,
2012 -- p_msg_data => l_msg_data,
2013 p_data => l_data,
2014 p_msg_index_out => l_msg_index_out);
2015 x_msg_data := l_data;
2016 -- error_msg('public --> '||x_msg_data||', '||l_msg_data);
2017 END IF;
2018 raise FND_API.G_EXC_ERROR;
2019 END IF;
2020
2021 -- FP M : 3491609 : Project Execution Workflow
2022 PA_WORKPLAN_WORKFLOW.START_PROJECT_EXECUTION_WF
2023 (
2024 p_project_id => l_project_id
2025 ,x_msg_count => l_msg_count
2026 ,x_msg_data => l_msg_data
2027 ,x_return_status => l_return_status
2028 ) ;
2029
2030 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2031 x_msg_count := FND_MSG_PUB.count_msg;
2032 IF x_msg_count = 1 then
2033 pa_interface_utils_pub.get_messages
2034 (p_encoded => FND_API.G_TRUE,
2035 p_msg_index => 1,
2036 p_data => l_data,
2037 p_msg_index_out => l_msg_index_out);
2038 x_msg_data := l_data;
2039 END IF;
2040 raise FND_API.G_EXC_ERROR;
2041 END IF;
2042
2043 -- FP M : 3491609 : Project Execution Workflow
2044 x_return_status := FND_API.G_RET_STS_SUCCESS;
2045
2046 IF (p_commit = FND_API.G_TRUE) THEN
2047 COMMIT;
2048 END IF;
2049
2050 IF (p_debug_mode = 'Y') THEN
2051 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.PUBLISH_STRUCTURE end');
2052 END IF;
2053
2054 EXCEPTION
2055 when FND_API.G_EXC_ERROR then
2056 if p_commit = FND_API.G_TRUE then
2057 rollback to publish_structure;
2058 end if;
2059 x_return_status := FND_API.G_RET_STS_ERROR;
2060
2061 x_published_struct_ver_id := NULL ; -- 4537865
2062 when FND_API.G_EXC_UNEXPECTED_ERROR then
2063 if p_commit = FND_API.G_TRUE then
2064 rollback to publish_structure;
2065 end if;
2066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067 -- 4537865
2068 x_msg_count := 1 ;
2069 x_msg_data := SUBSTRB(SQLERRM,1,240);
2070 x_published_struct_ver_id := NULL ;
2071 -- 4537865
2072
2073 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2074 p_procedure_name => 'PUBLISH_STRUCTURE',
2075 p_error_text => x_msg_data); -- 4537865
2076 when OTHERS then
2077 if p_commit = FND_API.G_TRUE then
2078 rollback to publish_structure;
2079 end if;
2080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2081 -- 4537865
2082 x_msg_count := 1 ;
2083 x_msg_data := SUBSTRB(SQLERRM,1,240);
2084 x_published_struct_ver_id := NULL ;
2085 -- 4537865
2086
2087 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2088 p_procedure_name => 'PUBLISH_STRUCTURE',
2089 p_error_text => x_msg_data); -- 4537865
2090 raise;
2091 END Publish_Structure;
2092
2093
2094 -- API name : Copy_Structure
2095 -- Type : Public Procedure
2096 -- Pre-reqs : None
2097 -- Return Value : N/A
2098 -- Parameters
2099 -- p_api_version IN NUMBER := 1.0
2100 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2101 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2102 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2103 -- p_validation_level IN VARCHAR2 := 100
2104 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2105 -- p_debug_mode IN VARCHAR2 := 'N'
2106 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2107 -- p_src_project_id IN NUMBER
2108 -- p_dest_project_id IN NUMBER
2109 -- p_delta IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2110 -- x_return_status OUT VARCHAR2
2111 -- x_msg_count OUT NUMBER
2112 -- x_msg_data OUT VARCHAR2
2113 --
2114 -- History
2115 --
2116 -- 25-JUN-01 HSIU -Created
2117 --
2118 --
2119
2120
2121 procedure Copy_Structure
2122 (
2123 p_api_version IN NUMBER := 1.0
2124 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2125 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2126 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2127 ,p_validation_level IN VARCHAR2 := 100
2128 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2129 ,p_debug_mode IN VARCHAR2 := 'N'
2130 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2131 ,p_src_project_id IN NUMBER
2132 ,p_dest_project_id IN NUMBER
2133 -- anlee
2134 -- Dates changes
2135 ,p_delta IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2136 -- End of changes
2137 ,p_copy_task_flag IN VARCHAR2 := 'Y'
2138 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2139 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2140 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2141 )
2142 IS
2143 l_api_name CONSTANT VARCHAR(30) := 'COPY_STRUCTURE';
2144 l_api_version CONSTANT NUMBER := 1.0;
2145
2146 l_return_status VARCHAR2(1);
2147 l_msg_count NUMBER;
2148 l_msg_data VARCHAR2(250);
2149 l_data VARCHAR2(250);
2150 l_msg_index_out NUMBER;
2151 BEGIN
2152
2153 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE');
2154
2155 IF (p_debug_mode = 'Y') THEN
2156 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE begin');
2157 END IF;
2158
2159 IF (p_commit = FND_API.G_TRUE) THEN
2160 savepoint copy_structure;
2161 END IF;
2162
2163 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2165 END IF;
2166
2167 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2168 FND_MSG_PUB.initialize;
2169 END IF;
2170
2171 PA_PROJECT_STRUCTURE_PVT1.Copy_Structure
2172 ( p_commit => FND_API.G_FALSE
2173 ,p_validate_only => p_validate_only
2174 ,p_validation_level => p_validation_level
2175 ,p_calling_module => p_calling_module
2176 ,p_debug_mode => p_debug_mode
2177 ,p_max_msg_count => p_max_msg_count
2178 ,p_src_project_id => p_src_project_id
2179 ,p_dest_project_id => p_dest_project_id
2180 -- anlee
2181 -- Dates changes
2182 ,p_delta => p_delta
2183 -- End of changes
2184 ,p_copy_task_flag => p_copy_task_flag
2185 ,x_return_status => l_return_status
2186 ,x_msg_count => l_msg_count
2187 ,x_msg_data => l_msg_data);
2188
2189 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2190 x_msg_count := FND_MSG_PUB.count_msg;
2191 IF x_msg_count = 1 then
2192 pa_interface_utils_pub.get_messages
2193 (p_encoded => FND_API.G_TRUE,
2194 p_msg_index => 1,
2195 -- p_msg_count => l_msg_count,
2196 -- p_msg_data => l_msg_data,
2197 p_data => l_data,
2198 p_msg_index_out => l_msg_index_out);
2199 x_msg_data := l_data;
2200 END IF;
2201 raise FND_API.G_EXC_ERROR;
2202 END IF;
2203
2204 x_return_status := FND_API.G_RET_STS_SUCCESS;
2205
2206 IF (p_commit = FND_API.G_TRUE) THEN
2207 COMMIT;
2208 END IF;
2209
2210 IF (p_debug_mode = 'Y') THEN
2211 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE END');
2212 END IF;
2213
2214 EXCEPTION
2215 when FND_API.G_EXC_ERROR then
2216 if p_commit = FND_API.G_TRUE then
2217 rollback to copy_structure;
2218 end if;
2219 x_return_status := FND_API.G_RET_STS_ERROR;
2220 when FND_API.G_EXC_UNEXPECTED_ERROR then
2221 if p_commit = FND_API.G_TRUE then
2222 rollback to copy_structure;
2223 end if;
2224 -- 4537865
2225 x_msg_count := 1 ;
2226 x_msg_data := SUBSTRB(SQLERRM,1,240);
2227 -- 4537865
2228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2229 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2230 p_procedure_name => 'COPY_STRUCTURE',
2231 p_error_text => x_msg_data); -- 4537865
2232 when OTHERS then
2233 if p_commit = FND_API.G_TRUE then
2234 rollback to copy_structure;
2235 end if;
2236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2237 -- 4537865
2238 x_msg_count := 1 ;
2239 x_msg_data := SUBSTRB(SQLERRM,1,240);
2240 -- 4537865
2241 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2242 p_procedure_name => 'COPY_STRUCTURE',
2243 p_error_text => x_msg_data); -- 4537865
2244 raise;
2245 END Copy_Structure;
2246
2247
2248 -- API name : Copy_Structure_Version
2249 -- Type : Public Procedure
2250 -- Pre-reqs : None
2251 -- Return Value : N/A
2252 -- Parameters
2253 -- p_api_version IN NUMBER := 1.0
2254 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2255 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2256 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2257 -- p_validation_level IN VARCHAR2 := 100
2258 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2259 -- p_debug_mode IN VARCHAR2 := 'N'
2260 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2261 -- p_structure_version_id IN NUMBER
2262 -- p_new_struct_ver_name IN VARCHAR2
2263 -- p_new_struct_ver_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2264 -- x_new_struct_ver_id OUT NUMBER
2265 -- x_return_status OUT VARCHAR2
2266 -- x_msg_count OUT NUMBER
2267 -- x_msg_data OUT VARCHAR2
2268 --
2269 -- History
2270 --
2271 -- 25-JUN-01 HSIU -Created
2272 --
2273 --
2274
2275
2276 procedure Copy_Structure_Version
2277 (
2278 p_api_version IN NUMBER := 1.0
2279 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2280 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2281 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2282 ,p_validation_level IN VARCHAR2 := 100
2283 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2284 ,p_debug_mode IN VARCHAR2 := 'N'
2285 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2286 ,p_structure_version_id IN NUMBER
2287 ,p_new_struct_ver_name IN VARCHAR2
2288 ,p_new_struct_ver_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2289 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2290 ,x_new_struct_ver_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2291 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2292 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2293 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2294 )
2295 IS
2296 l_api_name CONSTANT VARCHAR(30) := 'COPY_STRUCTURE_VERSION';
2297 l_api_version CONSTANT NUMBER := 1.0;
2298
2299 l_new_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
2300 l_return_status VARCHAR2(1);
2301 l_msg_count NUMBER;
2302 l_msg_data VARCHAR2(250);
2303 l_data VARCHAR2(250);
2304 l_msg_index_out NUMBER;
2305 BEGIN
2306
2307 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE_VERSION');
2308
2309 IF (p_debug_mode = 'Y') THEN
2310 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE_VERSION begin');
2311 END IF;
2312
2313 IF (p_commit = FND_API.G_TRUE) THEN
2314 savepoint copy_structure_version;
2315 END IF;
2316
2317 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2319 END IF;
2320
2321 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2322 FND_MSG_PUB.initialize;
2323 END IF;
2324
2325 PA_PROJECT_STRUCTURE_PVT1.Copy_Structure_Version_bulk
2326 ( p_commit => FND_API.G_FALSE
2327 ,p_validate_only => p_validate_only
2328 ,p_validation_level => p_validation_level
2329 ,p_calling_module => p_calling_module
2330 ,p_debug_mode => p_debug_mode
2331 ,p_max_msg_count => p_max_msg_count
2332 ,p_structure_version_id => p_structure_version_id
2333 ,p_new_struct_ver_name => p_new_struct_ver_name
2334 ,p_new_struct_ver_desc => p_new_struct_ver_desc
2335 ,p_change_reason_code => p_change_reason_code
2336 ,x_new_struct_ver_id => l_new_struct_ver_id
2337 ,x_return_status => l_return_status
2338 ,x_msg_count => l_msg_count
2339 ,x_msg_data => l_msg_data);
2340
2341 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2342 x_msg_count := FND_MSG_PUB.count_msg;
2343 IF x_msg_count = 1 then
2344 pa_interface_utils_pub.get_messages
2345 (p_encoded => FND_API.G_TRUE,
2346 p_msg_index => 1,
2347 -- p_msg_count => l_msg_count,
2348 -- p_msg_data => l_msg_data,
2349 p_data => l_data,
2350 p_msg_index_out => l_msg_index_out);
2351 x_msg_data := l_data;
2352 END IF;
2353 raise FND_API.G_EXC_ERROR;
2354 END IF;
2355
2356 x_new_struct_ver_id := l_new_struct_ver_id;
2357 x_return_status := FND_API.G_RET_STS_SUCCESS;
2358
2359 IF (p_commit = FND_API.G_TRUE) THEN
2360 COMMIT;
2361 END IF;
2362
2363 IF (p_debug_mode = 'Y') THEN
2364 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE_VERSION END');
2365 END IF;
2366
2367 EXCEPTION
2368 when FND_API.G_EXC_ERROR then
2369 if p_commit = FND_API.G_TRUE then
2370 rollback to copy_structure_version;
2371 end if;
2372 x_return_status := FND_API.G_RET_STS_ERROR;
2373 x_new_struct_ver_id := NULL ; -- 4537865
2374 when FND_API.G_EXC_UNEXPECTED_ERROR then
2375 if p_commit = FND_API.G_TRUE then
2376 rollback to copy_structure_version;
2377 end if;
2378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2379 -- 4537865
2380 x_msg_count := 1 ;
2381 x_msg_data := SUBSTRB(SQLERRM,1,240);
2382 x_new_struct_ver_id := NULL ;
2383 -- 4537865
2384 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2385 p_procedure_name => 'COPY_STRUCTURE_VERSION',
2386 p_error_text => x_msg_data); -- 4537865
2387 when OTHERS then
2388 if p_commit = FND_API.G_TRUE then
2389 rollback to copy_structure_version;
2390 end if;
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2392 -- 4537865
2393 x_msg_count := 1 ;
2394 x_msg_data := SUBSTRB(SQLERRM,1,240);
2395 x_new_struct_ver_id := NULL ;
2396 -- 4537865
2397 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2398 p_procedure_name => 'COPY_STRUCTURE_VERSION',
2399 p_error_text => x_msg_data); -- 4537865
2400 raise;
2401 END Copy_Structure_Version;
2402
2403
2404 procedure SUBMIT_WORKPLAN
2405 (
2406 p_api_version IN NUMBER := 1.0
2407 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2408 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2409 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2410 ,p_validation_level IN VARCHAR2 := 100
2411 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2412 ,p_debug_mode IN VARCHAR2 := 'N'
2413 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2414 ,p_project_id IN NUMBER
2415 ,p_structure_id IN NUMBER
2416 ,p_structure_version_id IN NUMBER
2417 ,p_responsibility_id IN NUMBER
2418 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2419 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2420 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2421 )
2422 IS
2423 l_api_name CONSTANT VARCHAR(30) := 'SUBMIT_WORKPLAN';
2424 l_api_version CONSTANT NUMBER := 1.0;
2425
2426 l_return_status VARCHAR2(1);
2427 l_msg_count NUMBER;
2428 l_msg_data VARCHAR2(250);
2429 l_data VARCHAR2(250);
2430 l_msg_index_out NUMBER;
2431 l_project_id NUMBER;
2432 BEGIN
2433 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.SUBMIT_WORKPLAN');
2434
2435 IF (p_debug_mode = 'Y') THEN
2436 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.SUBMIT_WORKPLAN begin');
2437 END IF;
2438
2439 IF (p_commit = FND_API.G_TRUE) THEN
2440 savepoint submit_workplan;
2441 END IF;
2442
2443 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2445 END IF;
2446
2447 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2448 FND_MSG_PUB.initialize;
2449 END IF;
2450
2451 --Check if this structure can be published (ie, if linked structures are published)
2452 IF ('Y' <> PA_PROJECT_STRUCTURE_UTILS.Check_Publish_Struc_Ver_Ok(p_structure_version_id)) THEN
2453 PA_UTILS.ADD_MESSAGE('PA','PA_PS_OTHER_WORKING_LOCKED');
2454 x_msg_data := 'PA_PS_OTHER_WORKING_LOCKED';
2455 RAISE FND_API.G_EXC_ERROR;
2456 END IF;
2457
2458 PA_PROJECT_STRUCTURE_PVT1.SUBMIT_WORKPLAN(
2459 p_api_version => p_api_version
2460 ,p_commit => p_commit
2461 ,p_validate_only => p_validate_only
2462 ,p_validation_level => p_validation_level
2463 ,p_calling_module => p_calling_module
2464 ,p_debug_mode => p_debug_mode
2465 ,p_max_msg_count => p_max_msg_count
2466 ,p_project_id => p_project_id
2467 ,p_structure_id => p_structure_id
2468 ,p_structure_version_id => p_structure_version_id
2469 ,p_responsibility_id => p_responsibility_id
2470 ,x_return_status => x_return_status
2471 ,x_msg_count => x_msg_count
2472 ,x_msg_data => x_msg_data
2473 );
2474
2475 x_msg_count := FND_MSG_PUB.count_msg;
2476 IF (x_msg_count > 0) THEN
2477 IF x_msg_count = 1 then
2478 pa_interface_utils_pub.get_messages
2479 (p_encoded => FND_API.G_TRUE,
2480 p_msg_index => 1,
2481 p_data => l_data,
2482 p_msg_index_out => l_msg_index_out);
2483 x_msg_data := l_data;
2484 END IF;
2485 raise FND_API.G_EXC_ERROR;
2486 END IF;
2487
2488 --call private API
2489 x_return_status := FND_API.G_RET_STS_SUCCESS;
2490
2491 IF (p_commit = FND_API.G_TRUE) THEN
2492 COMMIT;
2493 END IF;
2494
2495 IF (p_debug_mode = 'Y') THEN
2496 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.SUBMIT_WORKPLAN end');
2497 END IF;
2498
2499 EXCEPTION
2500 when FND_API.G_EXC_ERROR then
2501 if p_commit = FND_API.G_TRUE then
2502 rollback to submit_workplan;
2503 end if;
2504 x_return_status := FND_API.G_RET_STS_ERROR;
2505 when FND_API.G_EXC_UNEXPECTED_ERROR then
2506 if p_commit = FND_API.G_TRUE then
2507 rollback to submit_workplan;
2508 end if;
2509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2510 -- 4537865
2511 x_msg_count := 1 ;
2512 x_msg_data := SUBSTRB(SQLERRM,1,240);
2513 -- 4537865
2514 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2515 p_procedure_name => 'SUBMIT_WORKPLAN',
2516 p_error_text => x_msg_data); -- 4537865
2517 when OTHERS then
2518 if p_commit = FND_API.G_TRUE then
2519 rollback to submit_workplan;
2520 end if;
2521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2522 -- 4537865
2523 x_msg_count := 1 ;
2524 x_msg_data := SUBSTRB(SQLERRM,1,240);
2525 -- 4537865
2526 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2527 p_procedure_name => 'SUBMIT_WORKPLAN',
2528 p_error_text => x_msg_data); -- 4537865
2529 raise;
2530 END SUBMIT_WORKPLAN;
2531
2532
2533 PROCEDURE rework_workplan
2534 (
2535 p_api_version IN NUMBER := 1.0
2536 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2537 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2538 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2539 ,p_validation_level IN VARCHAR2 := 100
2540 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2541 ,p_debug_mode IN VARCHAR2 := 'N'
2542 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2543 ,p_project_id IN NUMBER
2544 ,p_structure_version_id IN NUMBER
2545 ,p_record_version_number IN NUMBER
2546 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2547 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2548 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2549 )
2550 IS
2551 l_api_name CONSTANT VARCHAR(30) := 'REWORK_WORKPLAN';
2552 l_api_version CONSTANT NUMBER := 1.0;
2553
2554 l_return_status VARCHAR2(1);
2555 l_msg_count NUMBER;
2556 l_msg_data VARCHAR2(250);
2557 l_data VARCHAR2(250);
2558 l_msg_index_out NUMBER;
2559 l_project_id NUMBER;
2560 BEGIN
2561 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.REWORK_WORKPLAN');
2562
2563 IF (p_debug_mode = 'Y') THEN
2564 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.REWORK_WORKPLAN begin');
2565 END IF;
2566
2567 IF (p_commit = FND_API.G_TRUE) THEN
2568 savepoint rework_workplan;
2569 END IF;
2570
2571 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2573 END IF;
2574
2575 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2576 FND_MSG_PUB.initialize;
2577 END IF;
2578
2579 PA_PROJECT_STRUCTURE_PVT1.REWORK_WORKPLAN(
2580 p_api_version => 1.0
2581 ,p_commit => p_commit
2582 ,p_validate_only => p_validate_only
2583 ,p_validation_level => p_validation_level
2584 ,p_calling_module => p_calling_module
2585 ,p_debug_mode => p_debug_mode
2586 ,p_max_msg_count => p_max_msg_count
2587 ,p_project_id => p_project_id
2588 ,p_structure_version_id => p_structure_version_id
2589 ,p_record_version_number => p_record_version_number
2590 ,x_return_status => x_return_status
2591 ,x_msg_count => x_msg_count
2592 ,x_msg_data => x_msg_data
2593 );
2594
2595 x_msg_count := FND_MSG_PUB.count_msg;
2596 IF (x_msg_count > 0) THEN
2597 IF x_msg_count = 1 then
2598 pa_interface_utils_pub.get_messages
2599 (p_encoded => FND_API.G_TRUE,
2600 p_msg_index => 1,
2601 p_data => l_data,
2602 p_msg_index_out => l_msg_index_out);
2603 x_msg_data := l_data;
2604 END IF;
2605 raise FND_API.G_EXC_ERROR;
2606 END IF;
2607
2608 x_return_status := FND_API.G_RET_STS_SUCCESS;
2609
2610 IF (p_commit = FND_API.G_TRUE) THEN
2611 COMMIT;
2612 END IF;
2613
2614 IF (p_debug_mode = 'Y') THEN
2615 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.REWORK_WORKPLAN end');
2616 END IF;
2617
2618 EXCEPTION
2619 when FND_API.G_EXC_ERROR then
2620 if p_commit = FND_API.G_TRUE then
2621 rollback to rework_workplan;
2622 end if;
2623 x_return_status := FND_API.G_RET_STS_ERROR;
2624 when FND_API.G_EXC_UNEXPECTED_ERROR then
2625 if p_commit = FND_API.G_TRUE then
2626 rollback to rework_workplan;
2627 end if;
2628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2629 -- 4537865
2630 x_msg_count := 1 ;
2631 x_msg_data := SUBSTRB(SQLERRM,1,240);
2632 -- 4537865
2633 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2634 p_procedure_name => 'REWORK_WORKPLAN',
2635 p_error_text => x_msg_data); -- 4537865
2636 when OTHERS then
2637 if p_commit = FND_API.G_TRUE then
2638 rollback to rework_workplan;
2639 end if;
2640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2641 -- 4537865
2642 x_msg_count := 1 ;
2643 x_msg_data := SUBSTRB(SQLERRM,1,240);
2644 -- 4537865
2645 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2646 p_procedure_name => 'REWORK_WORKPLAN',
2647 p_error_text => x_msg_data); -- 4537865
2648 raise;
2649 END rework_workplan;
2650
2651
2652 -- API name : update_structures_setup_attr
2653 -- Type : Update API
2654 -- Pre-reqs : None
2655 -- Return Value : Update_structures_setup_attr
2656 --
2657 -- Parameters
2658 -- p_project_id IN NUMBER
2659 -- p_workplan_enabled_flag IN VARCHAR2
2660 -- p_financial_enabled_flag IN VARCHAR2
2661 -- p_sharing_enabled_flag IN VARCHAR2
2662 --FP M changes bug 3301192
2663 -- p_deliverables_enabled_flag IN VARCHAR2
2664 -- p_sharing_option_code IN VARCHAR2
2665 --End FP M changes bug 3301192
2666 -- p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2667 -- p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2668 -- x_return_status OUT VARCHAR2
2669 -- x_msg_count OUT NUMBER
2670 -- x_msg_data OUT VARCHAR2
2671 --
2672 -- History
2673 --
2674 -- 26-JUL-02 HSIU -Created
2675 -- 30-Mar-04 JYAN added p_sys_program_flag and p_allow_multi_prog_rollup
2676 --
2677 PROCEDURE update_structures_setup_attr
2678 ( p_api_version IN NUMBER := 1.0
2679 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2680 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2681 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2682 ,p_validation_level IN VARCHAR2 := 100
2683 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2684 ,p_debug_mode IN VARCHAR2 := 'N'
2685 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2686 ,p_project_id IN NUMBER
2687 ,p_workplan_enabled_flag IN VARCHAR2
2688 ,p_financial_enabled_flag IN VARCHAR2
2689 ,p_sharing_enabled_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2690 --FP M changes bug 3301192
2691 ,p_deliverables_enabled_flag IN VARCHAR2
2692 ,p_sharing_option_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2693 --End FP M changes bug 3301192
2694 ,p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2695 ,p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2696 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2697 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2698 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2699 )
2700 IS
2701 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_STRUCTURES_SETUP_ATTR';
2702 l_api_version CONSTANT NUMBER := 1.0;
2703 l_data VARCHAR2(250);
2704 l_msg_index_out NUMBER;
2705
2706 BEGIN
2707 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr');
2708
2709 IF (p_debug_mode = 'Y') THEN
2710 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr begin');
2711 END IF;
2712
2713 IF (p_commit = FND_API.G_TRUE) THEN
2714 savepoint update_struc_setup_attr_pub;
2715 END IF;
2716
2717 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2719 END IF;
2720
2721 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2722 FND_MSG_PUB.initialize;
2723 END IF;
2724
2725 PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURES_SETUP_ATTR(
2726 p_api_version => p_api_version
2727 ,p_init_msg_list => p_init_msg_list
2728 ,p_commit => p_commit
2729 ,p_validate_only => p_validate_only
2730 ,p_validation_level => p_validation_level
2731 ,p_calling_module => p_calling_module
2732 ,p_debug_mode => p_debug_mode
2733 ,p_max_msg_count => p_max_msg_count
2734 ,p_project_id => p_project_id
2735 ,p_workplan_enabled_flag => p_workplan_enabled_flag
2736 ,p_financial_enabled_flag => p_financial_enabled_flag
2737 ,p_sharing_enabled_flag => 'N' --p_sharing_enabled_flag
2738 ,p_deliverables_enabled_flag => p_deliverables_enabled_flag
2739 ,p_sharing_option_code => p_sharing_option_code
2740 ,p_sys_program_flag => p_sys_program_flag
2741 ,p_allow_multi_prog_rollup => p_allow_multi_prog_rollup
2742 ,x_return_status => x_return_status
2743 ,x_msg_count => x_msg_count
2744 ,x_msg_data => x_msg_data
2745 );
2746
2747 x_msg_count := FND_MSG_PUB.count_msg;
2748 IF (x_msg_count > 0) THEN
2749 IF x_msg_count = 1 then
2750 pa_interface_utils_pub.get_messages
2751 (p_encoded => FND_API.G_TRUE,
2752 p_msg_index => 1,
2753 p_data => l_data,
2754 p_msg_index_out => l_msg_index_out);
2755 x_msg_data := l_data;
2756 END IF;
2757 raise FND_API.G_EXC_ERROR;
2758 END IF;
2759
2760 x_return_status := FND_API.G_RET_STS_SUCCESS;
2761
2762 IF (p_commit = FND_API.G_TRUE) THEN
2763 COMMIT;
2764 END IF;
2765
2766 IF (p_debug_mode = 'Y') THEN
2767 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURES_SETUP_ATTR end');
2768 END IF;
2769
2770 EXCEPTION
2771 when FND_API.G_EXC_ERROR then
2772 if p_commit = FND_API.G_TRUE then
2773 rollback to update_struc_setup_attr_pub;
2774 end if;
2775 x_return_status := FND_API.G_RET_STS_ERROR;
2776 when FND_API.G_EXC_UNEXPECTED_ERROR then
2777 if p_commit = FND_API.G_TRUE then
2778 rollback to update_struc_setup_attr_pub;
2779 end if;
2780 -- 4537865
2781 x_msg_count := 1 ;
2782 x_msg_data := SUBSTRB(SQLERRM,1,240);
2783 -- 4537865
2784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2785 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2786 p_procedure_name => 'update_structures_setup_attr',
2787 p_error_text => x_msg_data); -- 4537865
2788 when OTHERS then
2789 if p_commit = FND_API.G_TRUE then
2790 rollback to update_struc_setup_attr_pub;
2791 end if;
2792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2793 -- 4537865
2794 x_msg_count := 1 ;
2795 x_msg_data := SUBSTRB(SQLERRM,1,240);
2796 -- 4537865
2797 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2798 p_procedure_name => 'update_structures_setup_attr',
2799 p_error_text => x_msg_data); -- 4537865
2800 raise;
2801 END update_structures_setup_attr;
2802
2803
2804 PROCEDURE update_workplan_versioning
2805 ( p_api_version IN NUMBER := 1.0
2806 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2807 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2808 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2809 ,p_validation_level IN VARCHAR2 := 100
2810 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2811 ,p_debug_mode IN VARCHAR2 := 'N'
2812 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2813 ,p_proj_element_id IN NUMBER
2814 ,p_enable_wp_version_flag IN VARCHAR2
2815 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2816 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2817 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2818 )
2819 IS
2820 l_api_name CONSTANT VARCHAR(30) := 'update_workplan_versioning';
2821 l_api_version CONSTANT NUMBER := 1.0;
2822 l_data VARCHAR2(250);
2823 l_msg_index_out NUMBER;
2824
2825 BEGIN
2826 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr');
2827
2828 IF (p_debug_mode = 'Y') THEN
2829 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr begin');
2830 END IF;
2831
2832 IF (p_commit = FND_API.G_TRUE) THEN
2833 savepoint update_wp_versioning_pub;
2834 END IF;
2835
2836 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2838 END IF;
2839
2840 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2841 FND_MSG_PUB.initialize;
2842 END IF;
2843
2844 PA_PROJECT_STRUCTURE_PVT1.update_workplan_versioning(
2845 p_api_version => p_api_version
2846 ,p_init_msg_list => p_init_msg_list
2847 ,p_commit => p_commit
2848 ,p_validate_only => p_validate_only
2849 ,p_validation_level => p_validation_level
2850 ,p_calling_module => p_calling_module
2851 ,p_debug_mode => p_debug_mode
2852 ,p_max_msg_count => p_max_msg_count
2853 ,p_proj_element_id => p_proj_element_id
2854 ,p_enable_wp_version_flag => p_enable_wp_version_flag
2855 ,x_return_status => x_return_status
2856 ,x_msg_count => x_msg_count
2857 ,x_msg_data => x_msg_data
2858 );
2859
2860 x_msg_count := FND_MSG_PUB.count_msg;
2861 IF (x_msg_count > 0) THEN
2862 IF x_msg_count = 1 then
2863 pa_interface_utils_pub.get_messages
2864 (p_encoded => FND_API.G_TRUE,
2865 p_msg_index => 1,
2866 p_data => l_data,
2867 p_msg_index_out => l_msg_index_out);
2868 x_msg_data := l_data;
2869 END IF;
2870 raise FND_API.G_EXC_ERROR;
2871 END IF;
2872
2873 x_return_status := FND_API.G_RET_STS_SUCCESS;
2874
2875 IF (p_commit = FND_API.G_TRUE) THEN
2876 COMMIT;
2877 END IF;
2878
2879 IF (p_debug_mode = 'Y') THEN
2880 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_WORKPLAN_VERSIONING end');
2881 END IF;
2882
2883 EXCEPTION
2884 when FND_API.G_EXC_ERROR then
2885 if p_commit = FND_API.G_TRUE then
2886 rollback to update_wp_versioning_pub;
2887 end if;
2888 x_return_status := FND_API.G_RET_STS_ERROR;
2889 when FND_API.G_EXC_UNEXPECTED_ERROR then
2890 if p_commit = FND_API.G_TRUE then
2891 rollback to update_wp_versioning_pub;
2892 end if;
2893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2894 -- 4537865
2895 x_msg_count := 1 ;
2896 x_msg_data := SUBSTRB(SQLERRM,1,240);
2897 -- 4537865
2898 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2899 p_procedure_name => 'update_workplan_versioning',
2900 p_error_text => x_msg_data); -- 4537865
2901 when OTHERS then
2902 if p_commit = FND_API.G_TRUE then
2903 rollback to update_wp_versioning_pub;
2904 end if;
2905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2906 -- 4537865
2907 x_msg_count := 1 ;
2908 x_msg_data := SUBSTRB(SQLERRM,1,240);
2909 -- 4537865
2910 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2911 p_procedure_name => 'update_workplan_versioning',
2912 p_error_text => x_msg_data); -- 4537865
2913 raise;
2914 END update_workplan_versioning;
2915
2916 -- API name : Delete_Working_Struc_Ver
2917 -- Type : Public Procedure
2918 -- Pre-reqs : None
2919 -- Return Value : N/A
2920 -- Parameters
2921 -- p_api_version IN NUMBER := 1.0
2922 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2923 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2924 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2925 -- p_validation_level IN VARCHAR2 := 100
2926 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2927 -- p_debug_mode IN VARCHAR2 := 'N'
2928 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2929 -- p_structure_version_id IN NUMBER
2930 -- p_record_version_number IN NUMBER
2931 -- x_return_status OUT VARCHAR2
2932 -- x_msg_count OUT NUMBER
2933 -- x_msg_data OUT VARCHAR2
2934 --
2935 -- History
2936 --
2937 -- 17-DEC-02 HSIU -Created
2938 --
2939 --
2940
2941
2942 procedure Delete_Working_Struc_Ver
2943 (
2944 p_api_version IN NUMBER := 1.0
2945 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2946 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2947 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2948 ,p_validation_level IN VARCHAR2 := 100
2949 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2950 ,p_debug_mode IN VARCHAR2 := 'N'
2951 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2952 ,p_structure_version_id IN NUMBER
2953 ,p_record_version_number IN NUMBER
2954 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2955 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2956 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2957 )
2958 IS
2959 l_api_name CONSTANT VARCHAR(30) := 'Delete_Working_Struc_Ver';
2960 l_api_version CONSTANT NUMBER := 1.0;
2961
2962 l_return_status VARCHAR2(1);
2963 l_msg_count NUMBER;
2964 l_msg_data VARCHAR2(250);
2965 l_data VARCHAR2(250);
2966 l_msg_index_out NUMBER;
2967 l_error_message_code VARCHAR2(30);
2968
2969 CURSOR c1 IS
2970 select project_id
2971 from pa_proj_element_versions
2972 where element_version_id = p_structure_version_id;
2973 l_project_id NUMBER;
2974
2975 BEGIN
2976 pa_debug.init_err_stack('PA_PROJECT_STRUCTURE_PUB1.Delete_Working_Struc_Ver');
2977
2978 IF (p_debug_mode = 'Y') THEN
2979 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_WORKINGSTRUC_VER begin');
2980 END IF;
2981
2982 IF (p_commit = FND_API.G_TRUE) THEN
2983 savepoint delete_working_struc_ver;
2984 END IF;
2985
2986 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2987 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2988 END IF;
2989
2990 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2991 FND_MSG_PUB.initialize;
2992 END IF;
2993
2994 OPEN c1;
2995 FETCH c1 into l_project_id;
2996 CLOSE c1;
2997
2998 --Check if locked
2999 PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
3000 l_project_id
3001 ,p_structure_version_id
3002 ,l_return_status
3003 ,l_error_message_code);
3004
3005 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3006 PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
3007 x_msg_data := l_error_message_code;
3008 RAISE FND_API.G_EXC_ERROR;
3009 END IF;
3010
3011 --Check if it is ok to delete working version
3012 IF ('N' = PA_PROJECT_STRUCTURE_UTILS.check_del_work_struc_ver_ok(p_structure_version_id)) THEN
3013 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_DEL_WK_STRUC_VER_ERR');
3014 x_msg_data := 'PA_PS_DEL_WK_STRUC_VER_ERR';
3015 RAISE FND_API.G_EXC_ERROR;
3016 END IF;
3017
3018 PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_Wo_Val(p_commit => 'N',
3019 p_debug_mode => p_debug_mode,
3020 p_structure_version_id => p_structure_version_id,
3021 p_record_version_number => p_record_version_number,
3022 x_return_status => l_return_status,
3023 x_msg_count => l_msg_count,
3024 x_msg_data => l_msg_data);
3025
3026 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3027 x_msg_count := FND_MSG_PUB.count_msg;
3028 IF x_msg_count = 1 then
3029 pa_interface_utils_pub.get_messages
3030 (p_encoded => FND_API.G_TRUE,
3031 p_msg_index => 1,
3032 p_data => l_data,
3033 p_msg_index_out => l_msg_index_out);
3034 x_msg_data := l_data;
3035 END IF;
3036 raise FND_API.G_EXC_ERROR;
3037 END IF;
3038
3039 x_return_status := FND_API.G_RET_STS_SUCCESS;
3040
3041 IF (p_debug_mode = 'Y') THEN
3042 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION end');
3043 END IF;
3044
3045 EXCEPTION
3046 when FND_API.G_EXC_ERROR then
3047 if p_commit = FND_API.G_TRUE then
3048 rollback to delete_working_struc_ver;
3049 end if;
3050 x_msg_count := FND_MSG_PUB.count_msg;
3051 x_return_status := FND_API.G_RET_STS_ERROR;
3052 when FND_API.G_EXC_UNEXPECTED_ERROR then
3053 if p_commit = FND_API.G_TRUE then
3054 rollback to delete_working_struc_ver;
3055 end if;
3056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3057 -- 4537865
3058 x_msg_count := 1 ;
3059 x_msg_data := SUBSTRB(SQLERRM,1,240);
3060 -- 4537865
3061 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3062 p_procedure_name => 'Delete_Working_Struc_Ver',
3063 p_error_text => x_msg_data); -- 4537865
3064 when OTHERS then
3065 if p_commit = FND_API.G_TRUE then
3066 rollback to delete_working_struc_ver;
3067 end if;
3068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3069 -- 4537865
3070 x_msg_count := 1 ;
3071 x_msg_data := SUBSTRB(SQLERRM,1,240);
3072 -- 4537865
3073 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3074 p_procedure_name => 'Delete_Working_Struc_Ver',
3075 p_error_text => x_msg_data); -- 4537865
3076 raise;
3077 END DELETE_WORKING_STRUC_VER;
3078
3079 procedure ENABLE_FINANCIAL_STRUCTURE
3080 (
3081 p_api_version IN NUMBER := 1.0
3082 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3083 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3084 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3085 ,p_validation_level IN NUMBER := 100
3086 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3087 ,p_debug_mode IN VARCHAR2 := 'N'
3088 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3089 ,p_project_id IN NUMBER
3090 ,p_proj_element_id IN NUMBER
3091 ,p_approval_reqd_flag IN VARCHAR2 DEFAULT 'N'
3092 ,p_auto_publish_flag IN VARCHAR2 DEFAULT 'N'
3093 ,p_approver_source_id IN NUMBER DEFAULT NULL
3094 ,p_approver_source_type IN NUMBER DEFAULT NULL
3095 ,p_default_display_lvl IN NUMBER DEFAULT 0
3096 ,p_enable_wp_version_flag IN VARCHAR2 DEFAULT 'N'
3097 ,p_auto_pub_upon_creation_flag IN VARCHAR2 DEFAULT 'N'
3098 ,p_auto_sync_txn_date_flag IN VARCHAR2 DEFAULT 'N'
3099 ,p_txn_date_sync_buf_days IN NUMBER DEFAULT NULL
3100 ,p_lifecycle_version_id IN NUMBER DEFAULT NULL
3101 ,p_current_phase_version_id IN NUMBER DEFAULT NULL
3102 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3103 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3104 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3105 )
3106
3107 IS
3108
3109 l_api_name CONSTANT VARCHAR(30) := 'ENABLE_FINANCIAL_STRUCTURE';
3110 l_api_version CONSTANT NUMBER := 1.0;
3111 l_return_status VARCHAR2(1);
3112 l_msg_count NUMBER;
3113 l_msg_data VARCHAR2(250);
3114 l_data VARCHAR2(250);
3115 l_msg_index_out NUMBER;
3116
3117 l_proj_progress_attr_id NUMBER;
3118
3119 BEGIN
3120
3121 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.ENABLE_FINANCIAL_STRUCTURE');
3122
3123 IF (p_debug_mode = 'Y') THEN
3124 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.ENABLE_FINANCIAL_STRUCTURE begin');
3125 END IF;
3126
3127 IF (p_commit = FND_API.G_TRUE) THEN
3128 savepoint ENABLE_FINANCIAL_STRUCTURE;
3129 END IF;
3130
3131 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3133 END IF;
3134
3135 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3136 FND_MSG_PUB.initialize;
3137 END IF;
3138
3139 PA_WORKPLAN_ATTR_PVT.CREATE_PROJ_WORKPLAN_ATTRS(
3140 p_commit => p_commit
3141 ,p_validate_only => p_validate_only
3142 ,p_validation_level => p_validation_level
3143 ,p_calling_module => p_calling_module
3144 ,p_debug_mode => p_debug_mode
3145 ,p_max_msg_count => p_max_msg_count
3146 ,p_project_id => p_project_id
3147 ,p_proj_element_id => p_proj_element_id
3148 ,p_approval_reqd_flag => p_approval_reqd_flag
3149 ,p_auto_publish_flag => p_auto_publish_flag
3150 ,p_approver_source_id => p_approver_source_id
3151 ,p_approver_source_type => p_approver_source_type
3152 ,p_default_display_lvl => p_default_display_lvl
3153 ,p_enable_wp_version_flag => p_enable_wp_version_flag
3154 ,p_auto_pub_upon_creation_flag => p_auto_pub_upon_creation_flag
3155 ,p_auto_sync_txn_date_flag => p_auto_sync_txn_date_flag
3156 ,p_txn_date_sync_buf_days => p_txn_date_sync_buf_days
3157 ,p_lifecycle_version_id => p_lifecycle_version_id
3158 ,p_current_phase_version_id => p_current_phase_version_id
3159 ,x_return_status => l_return_status
3160 ,x_msg_count => x_msg_count
3161 ,x_msg_data => x_msg_data
3162 );
3163
3164 -- 4537865 : This was missing earlier
3165 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3166 x_msg_count := FND_MSG_PUB.count_msg;
3167 IF x_msg_count = 1 then
3168 pa_interface_utils_pub.get_messages
3169 (p_encoded => FND_API.G_TRUE,
3170 p_msg_index => 1,
3171 p_data => l_data,
3172 p_msg_index_out => l_msg_index_out);
3173 x_msg_data := l_data;
3174 END IF;
3175 raise FND_API.G_EXC_ERROR;
3176 END IF;
3177 -- End : 4537865
3178 PA_PROGRESS_PUB.CREATE_PROJ_PROG_ATTR(
3179 p_api_version => l_api_version
3180 ,p_init_msg_list => p_init_msg_list
3181 ,p_commit => p_commit
3182 ,p_validate_only => p_validate_only
3183 ,p_validation_level => p_validation_level
3184 ,p_calling_module => p_calling_module
3185 ,p_debug_mode => p_debug_mode
3186 ,p_max_msg_count => p_max_msg_count
3187 ,p_project_id => p_project_id
3188 ,P_OBJECT_TYPE => 'PA_STRUCTURES'
3189 ,P_OBJECT_ID => p_proj_element_id
3190 ,P_PROGRESS_CYCLE_ID => to_number(null)
3191 ,P_WQ_ENABLE_FLAG => 'N'
3192 ,P_REMAIN_EFFORT_ENABLE_FLAG => 'N'
3193 ,P_PERCENT_COMP_ENABLE_FLAG => 'Y'
3194 ,P_NEXT_PROGRESS_UPDATE_DATE => to_date(null)
3195 ,p_TASK_WEIGHT_BASIS_CODE => 'COST'
3196 ,X_PROJ_PROGRESS_ATTR_ID => l_proj_progress_attr_id
3197 ,P_ALLOW_COLLAB_PROG_ENTRY => 'N'
3198 ,P_ALLW_PHY_PRCNT_CMP_OVERRIDES => 'Y'
3199 ,p_structure_type => 'FINANCIAL' --Amit
3200 ,x_return_status => l_return_status
3201 ,x_msg_count => l_msg_count
3202 ,x_msg_data => l_msg_data
3203 );
3204
3205 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3206 x_msg_count := FND_MSG_PUB.count_msg;
3207 IF x_msg_count = 1 then
3208 pa_interface_utils_pub.get_messages
3209 (p_encoded => FND_API.G_TRUE,
3210 p_msg_index => 1,
3211 -- p_msg_count => l_msg_count,
3212 -- p_msg_data => l_msg_data,
3213 p_data => l_data,
3214 p_msg_index_out => l_msg_index_out);
3215 x_msg_data := l_data;
3216 END IF;
3217 raise FND_API.G_EXC_ERROR;
3218 END IF;
3219
3220 x_return_status := FND_API.G_RET_STS_SUCCESS;
3221
3222 IF (p_commit = FND_API.G_TRUE) THEN
3223 COMMIT;
3224 END IF;
3225
3226 IF (p_debug_mode = 'Y') THEN
3227 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.ENABLE_FINANCIAL_STRUCTURE END');
3228 END IF;
3229
3230 EXCEPTION
3231 when FND_API.G_EXC_ERROR then
3232 if p_commit = FND_API.G_TRUE then
3233 rollback to ENABLE_FINANCIAL_STRUCTURE;
3234 end if;
3235 x_return_status := FND_API.G_RET_STS_ERROR;
3236 when FND_API.G_EXC_UNEXPECTED_ERROR then
3237 if p_commit = FND_API.G_TRUE then
3238 rollback to ENABLE_FINANCIAL_STRUCTURE;
3239 end if;
3240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3241 -- 4537865
3242 x_msg_count := 1 ;
3243 x_msg_data := SUBSTRB(SQLERRM,1,240);
3244 -- 4537865
3245 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3246 p_procedure_name => 'ENABLE_FINANCIAL_STRUCTURE',
3247 p_error_text => x_msg_data); -- 4537865
3248 when OTHERS then
3249 if p_commit = FND_API.G_TRUE then
3250 rollback to ENABLE_FINANCIAL_STRUCTURE;
3251 end if;
3252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3253 -- 4537865
3254 x_msg_count := 1 ;
3255 x_msg_data := SUBSTRB(SQLERRM,1,240);
3256 -- 4537865
3257 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3258 p_procedure_name => 'ENABLE_FINANCIAL_STRUCTURE',
3259 p_error_text => x_msg_data); -- 4537865
3260 raise;
3261
3262 END ENABLE_FINANCIAL_STRUCTURE;
3263
3264
3265
3266 procedure DISABLE_FINANCIAL_STRUCTURE
3267 (
3268 p_api_version IN NUMBER := 1.0
3269 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3270 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3271 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3272 ,p_validation_level IN NUMBER := 100
3273 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3274 ,p_debug_mode IN VARCHAR2 := 'N'
3275 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3276 ,p_project_id IN NUMBER
3277 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3278 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3279 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3280 )
3281
3282 IS
3283
3284 CURSOR C1 IS
3285 SELECT task_id
3286 FROM pa_tasks
3287 WHERE project_id = p_project_id
3288 AND top_task_id = task_id
3289 AND parent_task_id is NULL;
3290
3291 l_task_id NUMBER;
3292 l_parent_structure_id NUMBER;
3293 l_parent_structure_version_id NUMBER;
3294 l_record_version_number PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
3295 l_rowid VARCHAR2(255);
3296 l_check_sharing_enabled VARCHAR(1);
3297 l_proj_element_id NUMBER;
3298 l_api_name CONSTANT VARCHAR(30) := 'DISABLE_FINANCIAL_STRUCTURE';
3299 l_api_version CONSTANT NUMBER := 1.0;
3300 l_return_status VARCHAR2(1);
3301 l_msg_count NUMBER;
3302 l_msg_data VARCHAR2(250);
3303 l_data VARCHAR2(250);
3304 l_msg_index_out NUMBER;
3305
3306 l_err_code NUMBER := 0;
3307 l_err_stack VARCHAR2(630);
3308 l_err_stage VARCHAR2(80);
3309
3310 CURSOR get_struc_ver_info(c_structure_id NUMBER) IS
3311 SELECT element_version_id, record_version_number
3312 FROM pa_proj_element_versions
3313 where proj_element_id = c_structure_id
3314 and project_id = p_project_id;
3315
3316 BEGIN
3317 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE');
3318
3319 IF (p_debug_mode = 'Y') THEN
3320 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE begin');
3321 END IF;
3322
3323 IF (p_commit = FND_API.G_TRUE) THEN
3324 savepoint DISABLE_FINANCIAL_STRUCTURE;
3325 END IF;
3326
3327 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3329 END IF;
3330
3331 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3332 FND_MSG_PUB.initialize;
3333 END IF;
3334
3335 l_check_sharing_enabled := PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(p_project_id);
3336
3337 /* hsiu: added, 3305199 */
3338 select a.proj_element_id into l_parent_structure_id
3339 from pa_proj_elements a, pa_proj_structure_types b, pa_structure_types c
3340 where a.proj_element_id = b.proj_element_id
3341 and a.project_id = p_project_id
3342 and b.structure_type_id = c.structure_type_id
3343 and c.structure_type = 'FINANCIAL';
3344 /* hsiu: changes end, 3305199 */
3345
3346 OPEN C1;
3347 LOOP
3348 FETCH C1 into l_task_id;
3349 EXIT when C1%NOTFOUND;
3350
3351 PA_TASK_UTILS.CHECK_DELETE_TASK_OK(x_task_id => l_task_id,
3352 x_err_code => l_err_code,
3353 x_err_stage => l_err_stage,
3354 x_err_stack => l_err_stack);
3355 IF (l_err_code <> 0) THEN
3356 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_CANT_DIS_FN_STR');
3357 x_msg_data := 'PA_PS_CANT_DIS_FN_STR';
3358 raise FND_API.G_EXC_ERROR; --Bug No 3517852 SMukka Stop processing and come out of the loop
3359 ELSE
3360
3361 /* Amit : Moved the below code outside loop
3362 select rowid into l_rowid from pa_proj_progress_attr
3363 where project_id = p_project_id
3364 and object_id = l_task_id ;
3365
3366
3367 PA_PROJ_PROGRESS_ATTR_PKG.delete_row(l_rowid);
3368 */
3369
3370 IF (l_check_sharing_enabled = 'Y') THEN
3371 PA_PROJECT_CORE.DELETE_TASK(x_task_id => l_task_id,
3372 x_validate_flag => 'N',
3373 x_err_code => l_err_code,
3374 x_err_stage => l_err_stage,
3375 x_err_stack => l_err_stack);
3376
3377 UPDATE PA_PROJ_ELEMENT_VERSIONS
3378 SET FINANCIAL_TASK_FLAG = 'N'
3379 WHERE PROJECT_ID = p_project_id
3380 AND PROJ_ELEMENT_ID = l_task_id;
3381 ELSE
3382 PA_PROJ_TASK_STRUC_PUB.DELETE_TASK_STRUCTURE(p_calling_module => 'PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE',
3383 p_project_id => p_project_id,
3384 p_task_id => l_task_id,
3385 x_msg_count => l_msg_count,
3386 x_msg_data => l_msg_data,
3387 x_return_status => l_return_status);
3388
3389 /* hsiu: 3305199
3390 SELECT B.PROJ_ELEMENT_ID INTO l_proj_element_id FROM PA_PROJECTS_ALL A,PA_PROJ_STRUCTURE_TYPES B,PA_PROJ_ELEMENTS C
3391 WHERE A.PROJECT_ID = C.PROJECT_ID
3392 AND B.PROJ_ELEMENT_ID = C.PROJ_ELEMENT_ID
3393 AND B.PROJ_STRUCTURE_TYPE_ID = 6
3394 AND A.PROJECT_ID = P_PROJECT_ID;
3395
3396 PA_WORKPLAN_ATTR_PVT.DELETE_PROJ_WORKPLAN_ATTRS(p_project_id => p_project_id,
3397 p_proj_element_id => l_proj_element_id,
3398 x_msg_count => l_msg_count,
3399 x_msg_data => l_msg_data,
3400 x_return_status => l_return_status);
3401 end changes hsiu 3305199 */
3402 END IF;
3403 END IF;
3404 END LOOP;
3405 CLOSE C1;
3406
3407 /* hsiu commented 3305199
3408 SELECT parent_structure_id, parent_structure_version_id into l_parent_structure_id, l_parent_structure_version_id
3409 FROM PA_STRUCT_TASK_WBS_V
3410 WHERE task_id = l_task_id;
3411 */
3412
3413 select rowid into l_rowid from pa_proj_progress_attr
3414 where project_id = p_project_id
3415 -- and object_id = l_task_id Amit
3416 and object_id = l_parent_structure_id --Amit
3417 AND structure_type = 'FINANCIAL'; --Amit
3418
3419 PA_PROJ_PROGRESS_ATTR_PKG.delete_row(l_rowid);
3420
3421 SELECT rowid into l_rowid
3422 FROM pa_proj_structure_types
3423 WHERE proj_element_id = l_parent_structure_id
3424 and STRUCTURE_TYPE_ID = 6;
3425
3426 PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
3427
3428 IF (l_check_sharing_enabled <> 'Y') THEN
3429
3430 select record_version_number
3431 INTO l_record_version_number
3432 from pa_proj_workplan_attr
3433 where proj_element_id = l_parent_structure_id;
3434
3435 PA_WORKPLAN_ATTR_PVT.DELETE_PROJ_WORKPLAN_ATTRS(p_validate_only => FND_API.G_FALSE,
3436 p_project_id => p_project_id,
3437 p_proj_element_id => l_parent_structure_id,
3438 p_record_version_number => l_record_version_number,
3439 x_msg_count => l_msg_count,
3440 x_msg_data => l_msg_data,
3441 x_return_status => l_return_status);
3442 END IF;
3443
3444 /* hsiu commented 3305199
3445 SELECT record_version_number into l_record_version_number
3446 FROM pa_proj_element_versions
3447 WHERE element_version_id = l_parent_structure_version_id;
3448 */
3449
3450 /*Introducing the following IF CLAUSE For Bug 3938654
3451 In case of SHARED Case ,What is happening is :
3452 The 'Only' Structure Version ID that is avaiable(which is shared by both WP and Financial structures)
3453 is retrieved by the Cursor get_struc_ver_info , and the Structure Version corresponding to the ID
3454 is getting deleted .
3455 It means indirectly the WP structure is also getting deleted .This should not happen .
3456 */
3457
3458 IF (l_check_sharing_enabled <> 'Y') THEN -- For Bug 3938654
3459 --hsiu: 3305199
3460 open get_struc_ver_info(l_parent_structure_id);
3461 LOOP
3462 FETCH get_struc_ver_info INTO l_parent_structure_version_id, l_record_version_number;
3463 EXIT WHEN get_Struc_ver_info%NOTFOUND;
3464
3465 PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION(
3466 p_structure_version_id => l_parent_structure_version_id
3467 ,p_record_version_number => l_record_version_number
3468 ,x_return_status => l_return_status
3469 ,x_msg_count => l_msg_count
3470 ,x_msg_data => l_msg_data
3471 );
3472 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3473 x_msg_count := FND_MSG_PUB.count_msg;
3474 IF x_msg_count = 1 then
3475 pa_interface_utils_pub.get_messages
3476 (p_encoded => FND_API.G_TRUE,
3477 p_msg_index => 1,
3478 p_data => l_data,
3479 p_msg_index_out => l_msg_index_out);
3480 x_msg_data := l_data;
3481 END IF;
3482 raise FND_API.G_EXC_ERROR;
3483 END IF;
3484 END LOOP;
3485 CLOSE get_struc_ver_info;
3486 --hsiu chagnes end: 3305199
3487 END IF ; --IF CLAUSE introduced for Bug 3938654
3488
3489 /* hsiu 3305199
3490 SELECT record_version_number into l_record_version_number
3491 FROM pa_proj_elements
3492 WHERE proj_element_id = l_parent_structure_id;
3493
3494 PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE(
3495 p_structure_id => l_parent_structure_id
3496 ,p_record_version_number => l_record_version_number
3497 ,x_return_status => l_return_status
3498 ,x_msg_count => l_msg_count
3499 ,x_msg_data => l_msg_data
3500 );
3501
3502 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3503 x_msg_count := FND_MSG_PUB.count_msg;
3504 IF x_msg_count = 1 then
3505 pa_interface_utils_pub.get_messages
3506 (p_encoded => FND_API.G_TRUE,
3507 p_msg_index => 1,
3508 -- p_msg_count => l_msg_count,
3509 -- p_msg_data => l_msg_data,
3510 p_data => l_data,
3511 p_msg_index_out => l_msg_index_out);
3512 x_msg_data := l_data;
3513 END IF;
3514 raise FND_API.G_EXC_ERROR;
3515 END IF;
3516 */
3517
3518 x_return_status := FND_API.G_RET_STS_SUCCESS;
3519
3520 IF (p_commit = FND_API.G_TRUE) THEN
3521 COMMIT;
3522 END IF;
3523
3524 IF (p_debug_mode = 'Y') THEN
3525 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE END');
3526 END IF;
3527
3528 EXCEPTION
3529 when FND_API.G_EXC_ERROR then
3530 if p_commit = FND_API.G_TRUE then
3531 rollback to DISABLE_FINANCIAL_STRUCTURE;
3532 end if;
3533 x_return_status := FND_API.G_RET_STS_ERROR;
3534 when FND_API.G_EXC_UNEXPECTED_ERROR then
3535 if p_commit = FND_API.G_TRUE then
3536 rollback to DISABLE_FINANCIAL_STRUCTURE;
3537 end if;
3538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3539 -- 4537865
3540 x_msg_count := 1 ;
3541 x_msg_data := SUBSTRB(SQLERRM,1,240);
3542 -- 4537865
3543 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3544 p_procedure_name => 'DISABLE_FINANCIAL_STRUCTURE',
3545 p_error_text => x_msg_data); -- 4537865
3546 when OTHERS then
3547 if p_commit = FND_API.G_TRUE then
3548 rollback to DISABLE_FINANCIAL_STRUCTURE;
3549 end if;
3550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3551 -- 4537865
3552 x_msg_count := 1 ;
3553 x_msg_data := SUBSTRB(SQLERRM,1,240);
3554 -- 4537865
3555 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3556 p_procedure_name => 'DISABLE_FINANCIAL_STRUCTURE',
3557 p_error_text => x_msg_data); -- 4537865
3558 raise;
3559
3560 END DISABLE_FINANCIAL_STRUCTURE;
3561
3562
3563
3564 -- API name : Clear_Financial_Flag
3565 -- Type : Public Procedure
3566 -- Pre-reqs : None
3567 -- Return Value : N/A
3568 -- Parameters
3569 -- p_api_version IN NUMBER := 1.0
3570 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3571 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
3572 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3573 -- p_validation_level IN VARCHAR2 := 100
3574 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3575 -- p_debug_mode IN VARCHAR2 := 'N'
3576 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3577 -- p_project_id IN NUMBER
3578 -- p_task_version_id IN NUMBER
3579 -- p_structure_version_id IN NUMBER
3580 -- x_return_status OUT VARCHAR2
3581 -- x_msg_count OUT NUMBER
3582 -- x_msg_data OUT VARCHAR2
3583 --
3584 -- History
3585 -- 02-JAN-04 Rakesh Raghavan - Created
3586
3587 procedure CLEAR_FINANCIAL_FLAG
3588 (
3589 p_api_version IN NUMBER := 1.0
3590 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3591 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3592 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3593 ,p_validation_level IN NUMBER := 100
3594 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3595 ,p_debug_mode IN VARCHAR2 := 'N'
3596 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3597 ,p_project_id IN NUMBER
3598 ,p_task_version_id IN NUMBER
3599 ,p_structure_version_id IN NUMBER
3600 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3601 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3602 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3603 )
3604 IS
3605 l_return_status VARCHAR2(1);
3606 l_api_name CONSTANT VARCHAR(30) := 'CLEAR_FINANCIAL_FLAG';
3607 l_api_version CONSTANT NUMBER := 1.0;
3608 l_msg_count NUMBER;
3609 l_msg_data VARCHAR2(250);
3610 l_data VARCHAR2(250);
3611 l_msg_index_out NUMBER;
3612 BEGIN
3613
3614 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.CLEAR_FINANCIAL_FLAG');
3615
3616 IF (p_debug_mode = 'Y') THEN
3617 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CLEAR_FINANCIAL_FLAG begin');
3618 END IF;
3619
3620 IF (p_commit = FND_API.G_TRUE) THEN
3621 savepoint CLEAR_FINANCIAL_FLAG;
3622 END IF;
3623
3624 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3626 END IF;
3627
3628 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3629 FND_MSG_PUB.initialize;
3630 END IF;
3631
3632 IF (p_structure_version_id IS NOT NULL) THEN
3633 UPDATE PA_PROJ_ELEMENT_VERSIONS
3634 SET FINANCIAL_TASK_FLAG = 'N'
3635 WHERE PROJECT_ID = p_project_id
3636 AND PARENT_STRUCTURE_VERSION_ID = p_structure_version_id;
3637 END IF;
3638
3639 IF (p_task_version_id is NOT NULL) THEN
3640 UPDATE PA_PROJ_ELEMENT_VERSIONS
3641 SET FINANCIAL_TASK_FLAG='N'
3642 WHERE PROJECT_ID = p_project_id
3643 AND ELEMENT_VERSION_ID = p_task_version_id;
3644 END IF;
3645
3646 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3647 x_msg_count := FND_MSG_PUB.count_msg;
3648 IF x_msg_count = 1 then
3649 pa_interface_utils_pub.get_messages
3650 (p_encoded => FND_API.G_TRUE,
3651 p_msg_index => 1,
3652 -- p_msg_count => l_msg_count,
3653 -- p_msg_data => l_msg_data,
3654 p_data => l_data,
3655 p_msg_index_out => l_msg_index_out);
3656 x_msg_data := l_data;
3657 END IF;
3658 raise FND_API.G_EXC_ERROR;
3659 END IF;
3660
3661 x_return_status := FND_API.G_RET_STS_SUCCESS;
3662
3663 IF (p_commit = FND_API.G_TRUE) THEN
3664 COMMIT;
3665 END IF;
3666
3667 IF (p_debug_mode = 'Y') THEN
3668 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CLEAR_FINANCIAL_FLAG END');
3669 END IF;
3670
3671 EXCEPTION
3672 when FND_API.G_EXC_ERROR then
3673 if p_commit = FND_API.G_TRUE then
3674 rollback to CLEAR_FINANCIAL_FLAG;
3675 end if;
3676 x_return_status := FND_API.G_RET_STS_ERROR;
3677 when FND_API.G_EXC_UNEXPECTED_ERROR then
3678 if p_commit = FND_API.G_TRUE then
3679 rollback to CLEAR_FINANCIAL_FLAG;
3680 end if;
3681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3682 -- 4537865
3683 x_msg_count := 1 ;
3684 x_msg_data := SUBSTRB(SQLERRM,1,240);
3685 -- 4537865
3686 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3687 p_procedure_name => 'CLEAR_FINANCIAL_FLAG',
3688 p_error_text => x_msg_data); -- 4537865
3689 when OTHERS then
3690 if p_commit = FND_API.G_TRUE then
3691 rollback to CLEAR_FINANCIAL_FLAG;
3692 end if;
3693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3694 -- 4537865
3695 x_msg_count := 1 ;
3696 x_msg_data := SUBSTRB(SQLERRM,1,240);
3697 -- 4537865
3698 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3699 p_procedure_name => 'CLEAR_FINANCIAL_FLAG',
3700 p_error_text => x_msg_data); -- 4537865
3701 raise;
3702
3703 END CLEAR_FINANCIAL_FLAG;
3704 --
3705 -- API name : Update_Sch_Dirty_Flag
3706 -- Type : Public Procedure
3707 -- Pre-reqs : None
3708 -- Return Value : N/A
3709 -- Parameters
3710 -- p_api_version IN NUMBER := 1.0
3711 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3712 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
3713 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3714 -- p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3715 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3716 -- p_debug_mode IN VARCHAR2 := 'N'
3717 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3718 -- p_project_id IN NUMBER
3719 -- p_structure_version_id IN NUMBER
3720 -- p_dirty_flag IN VARCHAR2
3721 -- x_return_status OUT VARCHAR2
3722 -- x_msg_count OUT NUMBER
3723 -- x_msg_data OUT VARCHAR2
3724 --
3725 -- History
3726 -- 23-MAR-04 Srikanth Mukka - Created
3727 --
3728 PROCEDURE Update_Sch_Dirty_Flag
3729 (
3730 p_api_version IN NUMBER := 1.0
3731 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3732 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3733 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3734 ,p_validation_level IN NUMBER := 100
3735 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3736 ,p_debug_mode IN VARCHAR2 := 'N'
3737 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3738 ,p_project_id IN NUMBER
3739 ,p_structure_version_id IN NUMBER
3740 ,p_dirty_flag IN VARCHAR2 := 'N'
3741 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3742 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3743 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3744 )
3745 IS
3746 --
3747 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_SCH_DIRTY_FLAG';
3748 l_api_version CONSTANT NUMBER := 1.0;
3749 l_data VARCHAR2(250);
3750 l_msg_index_out NUMBER;
3751 --
3752 BEGIN
3753 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_sch_dirty_flag');
3754 --
3755 IF (p_debug_mode = 'Y') THEN
3756 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_sch_dirty_flag begin');
3757 END IF;
3758 --
3759 IF (p_commit = FND_API.G_TRUE) THEN
3760 savepoint update_sch_dirty_flag_pub;
3761 END IF;
3762 --
3763 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3765 END IF;
3766 --
3767 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3768 FND_MSG_PUB.initialize;
3769 END IF;
3770 --
3771 PA_PROJECT_STRUCTURE_PVT1.Update_Sch_Dirty_Flag(
3772 p_project_id =>p_project_id
3773 ,p_structure_version_id =>p_structure_version_id
3774 ,p_dirty_flag =>p_dirty_flag
3775 ,x_return_status =>x_return_status
3776 ,x_msg_count =>x_msg_count
3777 ,x_msg_data =>x_msg_data
3778 );
3779 --
3780 x_msg_count := FND_MSG_PUB.count_msg;
3781 IF (x_msg_count > 0) THEN
3782 IF x_msg_count = 1 THEN
3783 pa_interface_utils_pub.get_messages
3784 (p_encoded => FND_API.G_TRUE,
3785 p_msg_index => 1,
3786 p_data => l_data,
3787 p_msg_index_out => l_msg_index_out);
3788 x_msg_data := l_data;
3789 END IF;
3790 RAISE FND_API.G_EXC_ERROR;
3791 END IF;
3792 --
3793 x_return_status := FND_API.G_RET_STS_SUCCESS;
3794 --
3795 IF (p_commit = FND_API.G_TRUE) THEN
3796 COMMIT;
3797 END IF;
3798 --
3799 IF (p_debug_mode = 'Y') THEN
3800 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_SCH_DIRTY_FLAG end');
3801 END IF;
3802 --
3803 EXCEPTION
3804 WHEN FND_API.G_EXC_ERROR THEN
3805 IF p_commit = FND_API.G_TRUE THEN
3806 ROLLBACK TO update_sch_dirty_flag_pub;
3807 END IF;
3808 x_return_status := FND_API.G_RET_STS_ERROR;
3809 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3810 IF p_commit = FND_API.G_TRUE THEN
3811 ROLLBACK TO update_sch_dirty_flag_pub;
3812 END IF;
3813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814 -- 4537865
3815 x_msg_count := 1 ;
3816 x_msg_data := SUBSTRB(SQLERRM,1,240);
3817 -- 4537865
3818 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3819 p_procedure_name => 'update_sch_dirty_flag',
3820 p_error_text => x_msg_data); -- 4537865
3821 WHEN OTHERS THEN
3822 IF p_commit = FND_API.G_TRUE THEN
3823 ROLLBACK TO update_sch_dirty_flag_pub;
3824 END IF;
3825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3826 -- 4537865
3827 x_msg_count := 1 ;
3828 x_msg_data := SUBSTRB(SQLERRM,1,240);
3829 -- 4537865
3830 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3831 p_procedure_name => 'update_sch_dirty_flag',
3832 p_error_text => x_msg_data); -- 4537865
3833 RAISE;
3834 END update_sch_dirty_flag;
3835
3836 --
3837 --
3838 -- API name : DELETE_PUBLISHED_STRUCTURE_VERSION
3839 -- Tracking Bug : 4925192
3840 -- Type : Public Procedure
3841 -- Pre-reqs : None
3842 -- Return Value : N/A
3843 -- Parameters
3844 -- p_api_version IN NUMBER := 1.0
3845 -- ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3846 -- ,p_structure_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
3847 -- ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
3848 -- ,x_return_status OUT NOCOPY VARCHAR2
3849 -- ,x_msg_count OUT NOCOPY NUMBER
3850 -- ,x_msg_data OUT NOCOPY VARCHAR2
3851 --
3852 -- History
3853 --
3854 -- 17-NOV-06 Ram Namburi -Created
3855 --
3856 -- Purpose:
3857 --
3858 -- This API will delete a published structure version
3859 -- 1. It calls the delete validation API to see if deletion is okay.
3860 -- 2. Then it calls the Progress API to roll up the progress to the next higher
3861 -- later versions
3862 -- 3. Then it calls the actual delete API.
3863 --
3864 /*
3865 -- ######################################################################################
3866 SAMPLE CODE FOR DELETE MULTIPLE VERSIONS:
3867
3868
3869 FOR i in p_structure_version_id_tbl.FIRST..p_structure_version_id_tbl.LAST LOOP -- Call the validation API
3870 -- If Y is returned then proceed with the
3871 -- progress rollup and
3872 -- actual deletion.
3873 -- l_structure_version_rec := p_structure_version_in_tbl(i);
3874 l_val_return_status := PA_PROJECT_STRUCTURE_UTILS.check_del_pub_struc_ver_ok(p_structure_version_id_tbl(i));
3875
3876 IF (l_val_return_status = 'Y') then
3877
3878 if (l_validation_failed <> 'Y'))THEN
3879
3880 -- Call Progress rollup API
3881
3882 BEGIN
3883
3884 pa_progress_pvt.UPD_PROG_RECS_STR_DELETE(
3885 p_project_id => p_project_id,
3886 p_str_ver_id_to_del => p_structure_version_id_tbl(i),
3887 x_return_status => l_return_status,
3888 x_msg_count => l_msg_count,
3889 x_msg_data => l_msg_data);
3890 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3891 NULL;
3892 END IF;
3893 EXCEPTION
3894 WHEN OTHERS THEN
3895 NULL;
3896 END;
3897
3898
3899 BEGIN
3900 -- Call the actual delete API
3901 PA_PROJECT_STRUCTURE_PUB1.delete_working_struc_ver(
3902 p_api_version => 1.0,
3903 p_structure_version_id => p_structure_version_id_tbl(i),
3904 p_record_version_number => p_record_version_number_tbl(i),
3905 x_return_status => l_return_status,
3906 x_msg_count => l_msg_count,
3907 x_msg_data => l_msg_data);
3908 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3909 NULL;
3910 END IF;
3911 EXCEPTION
3912 WHEN OTHERS THEN
3913 l_validation_failed := 'Y'
3914
3915 -- < store: message data in local tbl variable.
3916 -- store: ver name of p_structure_version_id_tbl(i) in local tbl.
3917 -- store: ver number of p_structure_version_id_tbl(i) in local tbl > --
3918 NULL;
3919 END;
3920
3921 end if; -- if (l_validation_failed <> 'Y'))THEN
3922
3923 ELSE
3924 l_validation_failed := 'Y';
3925
3926 -- < read message from stack into local tbl variable.
3927 -- store: ver name of p_structure_version_id_tbl(i) in local tbl.
3928 -- store: ver number of p_structure_version_id_tbl(i) in local tbl > --
3929 END IF;
3930 END LOOP;
3931 IF l_validation_failed = 'Y' THEN
3932 -- Return Status will be error so the caller
3933 -- can read from the error stack
3934 x_return_status := FND_API.G_RET_STS_ERROR;
3935
3936 -- clear FND stack of all previously pop messages.
3937
3938 -- populate generic error message
3939 -- < loop thorugh the local tbl variable with messages.
3940 -- pop message with ver name and ver number tokens using the new 'TOKEN : MESG'
3941 -- end loop>
3942
3943 END IF;
3944
3945 EXCEPTION
3946 WHEN FND_API.G_EXC_ERROR THEN
3947
3948 IF p_commit = FND_API.G_TRUE THEN
3949
3950 ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
3951
3952 END IF;
3953 x_return_status := FND_API.G_RET_STS_ERROR;
3954
3955 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3956 IF p_commit = FND_API.G_TRUE THEN
3957 ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
3958 END IF;
3959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3960 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3961 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
3962 p_error_text => SUBSTRB(SQLERRM,1,240));
3963 WHEN OTHERS THEN
3964 IF p_commit = FND_API.G_TRUE THEN
3965
3966 ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
3967
3968 END IF;
3969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3970 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3971 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
3972 p_error_text => SUBSTRB(SQLERRM,1,240));
3973 RAISE;
3974
3975 END DELETE_PUBLISHED_STRUCTURE_VER;
3976
3977 -- ######################################################################################
3978
3979 */
3980
3981
3982 procedure DELETE_PUBLISHED_STRUCTURE_VER
3983 (
3984 p_api_version IN NUMBER := 1.0
3985 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3986 ,p_project_id IN NUMBER
3987 ,p_structure_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
3988 ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
3989 ,x_return_status OUT NOCOPY VARCHAR2
3990 ,x_msg_count OUT NOCOPY NUMBER
3991 ,x_msg_data OUT NOCOPY VARCHAR2
3992 ) IS
3993
3994 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PUBLISHED_STRUCTURE_VER';
3995 l_validation_failed VARCHAR2(1) := 'N';
3996 l_val_return_status VARCHAR2(1);
3997
3998 l_return_status VARCHAR2(1);
3999 l_msg_data VARCHAR2(2000);
4000 l_msg_count NUMBER;
4001 -- i NUMBER;
4002 -- l_structure_version_rec structure_version_in_rec_type;
4003
4004 BEGIN
4005
4006 -- SAVEPOINT DELETE_PUBLISHED_STRUCTURE_VER;
4007
4008 --l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4009
4010 -- Standard call to check for call compatibility.
4011
4012 IF NOT FND_API.Compatible_API_Call ( 1.0 ,
4013 p_api_version ,
4014 l_api_name ,
4015 G_PKG_NAME ) THEN
4016
4017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4018 END IF;
4019
4020 -- Initialize the message table Unconditionally.
4021
4022 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
4023
4024 FND_MSG_PUB.initialize;
4025 END IF;
4026
4027 -- Set API return status to success
4028 x_return_status := FND_API.G_RET_STS_SUCCESS;
4029
4030 -- Actual Processing starts here.
4031 -- Start looping through the passed in values.
4032
4033 FOR i in p_structure_version_id_tbl.FIRST..p_structure_version_id_tbl.LAST LOOP -- Call the validation API
4034
4035 -- If Y is returned then proceed with the
4036 -- progress rollup and
4037 -- actual deletion.
4038 l_val_return_status := PA_PROJECT_STRUCTURE_UTILS.check_del_pub_struc_ver_ok(p_structure_version_id_tbl(i),p_project_id);
4039 IF l_val_return_status = 'Y' THEN
4040 -- Call Progress rollup API
4041
4042 pa_progress_pvt.UPD_PROG_RECS_STR_DELETE(
4043 p_project_id => p_project_id,
4044 p_str_ver_id_to_del => p_structure_version_id_tbl(i),
4045 x_return_status => l_return_status,
4046 x_msg_count => l_msg_count,
4047 x_msg_data => l_msg_data);
4048 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4049 l_msg_count := FND_MSG_PUB.count_msg;
4050 x_msg_data := l_msg_data;
4051 RAISE FND_API.G_EXC_ERROR;
4052 END IF;
4053
4054 -- Call the actual delete API
4055 PA_PROJECT_STRUCTURE_PUB1.delete_working_struc_ver(
4056 p_api_version => 1.0,
4057 p_structure_version_id => p_structure_version_id_tbl(i),
4058 p_record_version_number => p_record_version_number_tbl(i),
4059 x_return_status => l_return_status,
4060 x_msg_count => l_msg_count,
4061 x_msg_data => l_msg_data);
4062 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4063 l_msg_count := FND_MSG_PUB.count_msg;
4064 x_msg_data := l_msg_data;
4065 RAISE FND_API.G_EXC_ERROR;
4066 END IF;
4067
4068 ELSE
4069 x_return_status := FND_API.G_RET_STS_ERROR;
4070 RAISE FND_API.G_EXC_ERROR;
4071 END IF;
4072 END LOOP;
4073
4074 EXCEPTION
4075 WHEN FND_API.G_EXC_ERROR THEN
4076
4077 x_return_status := FND_API.G_RET_STS_ERROR;
4078
4079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4080
4081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4082 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4083 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
4084 p_error_text => SUBSTRB(SQLERRM,1,240));
4085
4086 WHEN OTHERS THEN
4087
4088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4089 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4090 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
4091 p_error_text => SUBSTRB(SQLERRM,1,240));
4092
4093 RAISE;
4094
4095 END DELETE_PUBLISHED_STRUCTURE_VER;
4096
4097
4098 -- History
4099 -- 03-May-06 Ram Namburi - Created
4100 -- Purpose:
4101 -- This is used to enable the program on a project. In forms we are not allowing the user to create a link
4102 -- all the times, and if the program is not enabled then we enable that on the fly so the link creation is
4103 -- possible. This is needed otherwise users need to go to SS page to enable program on the project and come
4104 -- back to forms to create links. In order to remove this dependency we are now calling this API from forms
4105 -- as we couldnt directly call the update_structures_setup_attr.
4106 -- This wrapper will call the update_structures_setup_attr with other parameters.
4107 --
4108 PROCEDURE enable_program_flag(
4109 p_project_id IN NUMBER
4110 ,x_return_status OUT NOCOPY VARCHAR2
4111 ,x_msg_count OUT NOCOPY NUMBER
4112 ,x_msg_data OUT NOCOPY VARCHAR2
4113 ) IS
4114
4115 l_data VARCHAR2(250);
4116 l_msg_index_out NUMBER;
4117
4118 l_wp_enabled VARCHAR2(1) := NULL;
4119 l_fin_enabled VARCHAR2(1) := NULL;
4120 l_delv_enabled VARCHAR2(1) := NULL;
4121 l_share_code VARCHAR2(30) := NULL;
4122
4123 BEGIN
4124
4125 l_wp_enabled := PA_PROJECT_STRUCTURE_UTILS.Check_workplan_enabled(p_project_id);
4126 l_fin_enabled := PA_PROJECT_STRUCTURE_UTILS.Check_financial_enabled(p_project_id);
4127 l_delv_enabled := PA_PROJECT_STRUCTURE_UTILS.Check_deliverable_enabled(p_project_id);
4128 l_share_code := PA_PROJECT_STRUCTURE_UTILS.Get_Structure_sharing_code(p_project_id);
4129
4130 PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURES_SETUP_ATTR(
4131 p_api_version => 1.0
4132 ,p_init_msg_list => 'T'
4133 ,p_commit => 'F'
4134 --,p_validate_only => 'T'
4135 --,p_validation_level => p_validation_level
4136 --,p_calling_module => p_calling_module
4137 ,p_debug_mode => 'N'
4138 --,p_max_msg_count => p_max_msg_count
4139 ,p_project_id => p_project_id
4140 ,p_workplan_enabled_flag => l_wp_enabled
4141 ,p_financial_enabled_flag => l_fin_enabled
4142 ,p_deliverables_enabled_flag => l_delv_enabled
4143 ,p_sharing_option_code => l_share_code
4144 ,p_sys_program_flag => 'Y'
4145 ,p_allow_multi_prog_rollup => 'N'
4146 ,x_return_status => x_return_status
4147 ,x_msg_count => x_msg_count
4148 ,x_msg_data => x_msg_data
4149 );
4150
4151 x_msg_count := FND_MSG_PUB.count_msg;
4152
4153 IF (x_msg_count > 0) THEN
4154
4155 IF x_msg_count = 1 THEN
4156
4157 pa_interface_utils_pub.get_messages
4158 (p_encoded => FND_API.G_TRUE,
4159 p_msg_index => 1,
4160 p_data => l_data,
4161 p_msg_index_out => l_msg_index_out);
4162 x_msg_data := l_data;
4163 END IF;
4164
4165 RAISE FND_API.G_EXC_ERROR;
4166 END IF;
4167
4168 x_return_status := FND_API.G_RET_STS_SUCCESS;
4169
4170
4171 EXCEPTION
4172
4173 WHEN FND_API.G_EXC_ERROR THEN
4174
4175 x_return_status := FND_API.G_RET_STS_ERROR;
4176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4177
4178 x_msg_count := 1 ;
4179 x_msg_data := SUBSTRB(SQLERRM,1,240);
4180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4181 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4182 p_procedure_name => 'enable_program_flag',
4183 p_error_text => x_msg_data);
4184 WHEN OTHERS THEN
4185
4186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4187 x_msg_count := 1 ;
4188 x_msg_data := SUBSTRB(SQLERRM,1,240);
4189 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4190 p_procedure_name => 'enable_program_flag',
4191 p_error_text => x_msg_data);
4192 RAISE;
4193
4194 END enable_program_flag;
4195 --
4196 --
4197 end PA_PROJECT_STRUCTURE_PUB1;