[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_STRUCTURE_PUB1
Source
1 package body PA_PROJECT_STRUCTURE_PUB1 as
2 /*$Header: PAXSTCPB.pls 120.10.12020000.11 2013/05/21 12:54:08 djambhek 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 l_conc_req_id NUMBER; -- Bug 8347243
1903
1904 cursor c2(c_project_id NUMBER, c_structure_version_id NUMBER) IS
1905 select record_version_number
1906 from pa_proj_elem_ver_structure
1907 where project_id = c_project_id
1908 and element_version_id = c_structure_version_id;
1909
1910 CURSOR c1 IS
1911 select project_id
1912 from pa_proj_element_versions
1913 where element_version_id = p_structure_version_id;
1914
1915 BEGIN
1916 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.PUBLISH_STRUCTURE');
1917
1918 IF (p_debug_mode = 'Y') THEN
1919 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.PUBLISH_STRUCTURE begin');
1920 END IF;
1921
1922 IF (p_commit = FND_API.G_TRUE) THEN
1923 savepoint publish_structure;
1924 END IF;
1925
1926 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1928 END IF;
1929
1930 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1931 FND_MSG_PUB.initialize;
1932 END IF;
1933
1934 --Check if this structure can be published (ie, if linked structures are published)
1935 IF ('Y' <> PA_PROJECT_STRUCTURE_UTILS.Check_Publish_Struc_Ver_Ok(p_structure_version_id)) THEN
1936 PA_UTILS.ADD_MESSAGE('PA','PA_PS_OTHER_WORKING_LOCKED');
1937 x_msg_data := 'PA_PS_OTHER_WORKING_LOCKED';
1938 RAISE FND_API.G_EXC_ERROR;
1939 END IF;
1940
1941
1942 --Call private API
1943 PA_PROJECT_STRUCTURE_PVT1.Publish_Structure(
1944 p_api_version => p_api_version
1945 ,p_init_msg_list => p_init_msg_list
1946 ,p_commit => p_commit
1947 ,p_validate_only => p_validate_only
1948 ,p_validation_level => p_validation_level
1949 ,p_calling_module => p_calling_module
1950 ,p_debug_mode => p_debug_mode
1951 ,p_max_msg_count => p_max_msg_count
1952 ,p_responsibility_id => p_responsibility_id
1953 ,p_user_id => p_user_id
1954 ,p_structure_version_id => p_structure_version_id
1955 ,p_publish_structure_ver_name => p_publish_structure_ver_name
1956 ,p_structure_ver_desc => p_structure_ver_desc
1957 ,p_effective_date => p_effective_date
1958 ,p_original_baseline_flag => p_original_baseline_flag
1959 ,p_current_baseline_flag => p_current_baseline_flag
1960 ,p_pub_prog_flag => p_pub_prog_flag
1961 ,x_published_struct_ver_id => x_published_struct_ver_id
1962 ,x_return_status => l_return_status
1963 ,x_msg_count => l_msg_count
1964 ,x_msg_data => l_msg_data
1965 );
1966
1967 -- error_msg('public --> '||l_return_status||l_msg_count||l_msg_data);
1968
1969 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1970 x_msg_count := FND_MSG_PUB.count_msg;
1971 IF x_msg_count = 1 then
1972 pa_interface_utils_pub.get_messages
1973 (p_encoded => FND_API.G_TRUE,
1974 p_msg_index => 1,
1975 -- p_msg_count => l_msg_count,
1976 -- p_msg_data => l_msg_data,
1977 p_data => l_data,
1978 p_msg_index_out => l_msg_index_out);
1979 x_msg_data := l_data;
1980 -- error_msg('public --> '||x_msg_data||', '||l_msg_data);
1981 END IF;
1982 raise FND_API.G_EXC_ERROR;
1983 END IF;
1984
1985 -- Bug 8347243 : Workflow notification here should happen only in ONLINE mode. For CONCURRENT mode
1986 -- workflow notification will be initiated from PA_PROJ_TASK_STRUC_PUB.PROCESS_WBS_UPDATES_CONC
1987 -- We will get concurrent id here to determine if this is ONLINE or CONCURRENT mode
1988
1989 OPEN c1;
1990 FETCH c1 into l_project_id;
1991 CLOSE c1;
1992
1993 -- Bug 8347243
1994 select conc_request_id INTO l_conc_req_id
1995 from pa_proj_elem_ver_structure
1996 where element_version_id = p_structure_version_id
1997 and project_id = l_project_id;
1998
1999 IF l_conc_req_id is null THEN -- Bug 8347243 --Bug#9628640
2000
2001 --start workflow
2002
2003 OPEN c2(l_project_id, x_published_struct_ver_id);
2004 FETCH c2 into l_record_version_number;
2005 CLOSE c2;
2006
2007 -- FP M : 3491609 : Project Execution Workflow
2008 PA_PROJECT_STRUCTURE_PVT1.change_workplan_status
2009 (
2010 p_project_id => l_project_id
2011 ,p_structure_version_id => x_published_struct_ver_id
2012 ,p_status_code => 'STRUCTURE_PUBLISHED'
2013 ,p_record_version_number => l_record_version_number
2014 ,x_return_status => l_return_status
2015 ,x_msg_count => l_msg_count
2016 ,x_msg_data => l_msg_data
2017 );
2018
2019 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2020 x_msg_count := FND_MSG_PUB.count_msg;
2021 IF x_msg_count = 1 then
2022 pa_interface_utils_pub.get_messages
2023 (p_encoded => FND_API.G_TRUE,
2024 p_msg_index => 1,
2025 -- p_msg_count => l_msg_count,
2026 -- p_msg_data => l_msg_data,
2027 p_data => l_data,
2028 p_msg_index_out => l_msg_index_out);
2029 x_msg_data := l_data;
2030 -- error_msg('public --> '||x_msg_data||', '||l_msg_data);
2031 END IF;
2032 raise FND_API.G_EXC_ERROR;
2033 END IF;
2034
2035 -- FP M : 3491609 : Project Execution Workflow
2036 PA_WORKPLAN_WORKFLOW.START_PROJECT_EXECUTION_WF
2037 (
2038 p_project_id => l_project_id
2039 ,x_msg_count => l_msg_count
2040 ,x_msg_data => l_msg_data
2041 ,x_return_status => l_return_status
2042 ) ;
2043
2044 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2045 x_msg_count := FND_MSG_PUB.count_msg;
2046 IF x_msg_count = 1 then
2047 pa_interface_utils_pub.get_messages
2048 (p_encoded => FND_API.G_TRUE,
2049 p_msg_index => 1,
2050 p_data => l_data,
2051 p_msg_index_out => l_msg_index_out);
2052 x_msg_data := l_data;
2053 END IF;
2054 raise FND_API.G_EXC_ERROR;
2055 END IF;
2056
2057 -- FP M : 3491609 : Project Execution Workflow
2058 x_return_status := FND_API.G_RET_STS_SUCCESS;
2059
2060 END IF; -- Bug 8347243
2061
2062 IF (p_commit = FND_API.G_TRUE) THEN
2063 COMMIT;
2064 END IF;
2065
2066 IF (p_debug_mode = 'Y') THEN
2067 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.PUBLISH_STRUCTURE end');
2068 END IF;
2069
2070 EXCEPTION
2071 when FND_API.G_EXC_ERROR then
2072 if p_commit = FND_API.G_TRUE then
2073 rollback to publish_structure;
2074 end if;
2075 x_return_status := FND_API.G_RET_STS_ERROR;
2076
2077 x_published_struct_ver_id := NULL ; -- 4537865
2078 when FND_API.G_EXC_UNEXPECTED_ERROR then
2079 if p_commit = FND_API.G_TRUE then
2080 rollback to publish_structure;
2081 end if;
2082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2083 -- 4537865
2084 x_msg_count := 1 ;
2085 x_msg_data := SUBSTRB(SQLERRM,1,240);
2086 x_published_struct_ver_id := NULL ;
2087 -- 4537865
2088
2089 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2090 p_procedure_name => 'PUBLISH_STRUCTURE',
2091 p_error_text => x_msg_data); -- 4537865
2092 when OTHERS then
2093 if p_commit = FND_API.G_TRUE then
2094 rollback to publish_structure;
2095 end if;
2096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2097 -- 4537865
2098 x_msg_count := 1 ;
2099 x_msg_data := SUBSTRB(SQLERRM,1,240);
2100 x_published_struct_ver_id := NULL ;
2101 -- 4537865
2102
2103 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2104 p_procedure_name => 'PUBLISH_STRUCTURE',
2105 p_error_text => x_msg_data); -- 4537865
2106 raise;
2107 END Publish_Structure;
2108
2109
2110 -- API name : Copy_Structure
2111 -- Type : Public Procedure
2112 -- Pre-reqs : None
2113 -- Return Value : N/A
2114 -- Parameters
2115 -- p_api_version IN NUMBER := 1.0
2116 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2117 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2118 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2119 -- p_validation_level IN VARCHAR2 := 100
2120 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2121 -- p_debug_mode IN VARCHAR2 := 'N'
2122 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2123 -- p_src_project_id IN NUMBER
2124 -- p_dest_project_id IN NUMBER
2125 -- p_delta IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2126 -- x_return_status OUT VARCHAR2
2127 -- x_msg_count OUT NUMBER
2128 -- x_msg_data OUT VARCHAR2
2129 --
2130 -- History
2131 --
2132 -- 25-JUN-01 HSIU -Created
2133 --
2134 --
2135
2136
2137 procedure Copy_Structure
2138 (
2139 p_api_version IN NUMBER := 1.0
2140 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2141 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2142 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2143 ,p_validation_level IN VARCHAR2 := 100
2144 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2145 ,p_debug_mode IN VARCHAR2 := 'N'
2146 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2147 ,p_src_project_id IN NUMBER
2148 ,p_dest_project_id IN NUMBER
2149 -- anlee
2150 -- Dates changes
2151 ,p_delta IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2152 -- End of changes
2153 ,p_copy_task_flag IN VARCHAR2 := 'Y'
2154 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2155 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2156 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2157 )
2158 IS
2159 l_api_name CONSTANT VARCHAR(30) := 'COPY_STRUCTURE';
2160 l_api_version CONSTANT NUMBER := 1.0;
2161
2162 l_return_status VARCHAR2(1);
2163 l_msg_count NUMBER;
2164 l_msg_data VARCHAR2(250);
2165 l_data VARCHAR2(250);
2166 l_msg_index_out NUMBER;
2167 BEGIN
2168
2169 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE');
2170
2171 IF (p_debug_mode = 'Y') THEN
2172 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE begin');
2173 END IF;
2174
2175 IF (p_commit = FND_API.G_TRUE) THEN
2176 savepoint copy_structure;
2177 END IF;
2178
2179 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2181 END IF;
2182
2183 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2184 FND_MSG_PUB.initialize;
2185 END IF;
2186
2187 PA_PROJECT_STRUCTURE_PVT1.Copy_Structure
2188 ( p_commit => FND_API.G_FALSE
2189 ,p_validate_only => p_validate_only
2190 ,p_validation_level => p_validation_level
2191 ,p_calling_module => p_calling_module
2192 ,p_debug_mode => p_debug_mode
2193 ,p_max_msg_count => p_max_msg_count
2194 ,p_src_project_id => p_src_project_id
2195 ,p_dest_project_id => p_dest_project_id
2196 -- anlee
2197 -- Dates changes
2198 ,p_delta => p_delta
2199 -- End of changes
2200 ,p_copy_task_flag => p_copy_task_flag
2201 ,x_return_status => l_return_status
2202 ,x_msg_count => l_msg_count
2203 ,x_msg_data => l_msg_data);
2204
2205 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2206 x_msg_count := FND_MSG_PUB.count_msg;
2207 IF x_msg_count = 1 then
2208 pa_interface_utils_pub.get_messages
2209 (p_encoded => FND_API.G_TRUE,
2210 p_msg_index => 1,
2211 -- p_msg_count => l_msg_count,
2212 -- p_msg_data => l_msg_data,
2213 p_data => l_data,
2214 p_msg_index_out => l_msg_index_out);
2215 x_msg_data := l_data;
2216 END IF;
2217 raise FND_API.G_EXC_ERROR;
2218 END IF;
2219
2220 x_return_status := FND_API.G_RET_STS_SUCCESS;
2221
2222 IF (p_commit = FND_API.G_TRUE) THEN
2223 COMMIT;
2224 END IF;
2225
2226 IF (p_debug_mode = 'Y') THEN
2227 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE END');
2228 END IF;
2229
2230 EXCEPTION
2231 when FND_API.G_EXC_ERROR then
2232 if p_commit = FND_API.G_TRUE then
2233 rollback to copy_structure;
2234 end if;
2235 x_return_status := FND_API.G_RET_STS_ERROR;
2236 when FND_API.G_EXC_UNEXPECTED_ERROR then
2237 if p_commit = FND_API.G_TRUE then
2238 rollback to copy_structure;
2239 end if;
2240 -- 4537865
2241 x_msg_count := 1 ;
2242 x_msg_data := SUBSTRB(SQLERRM,1,240);
2243 -- 4537865
2244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2245 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2246 p_procedure_name => 'COPY_STRUCTURE',
2247 p_error_text => x_msg_data); -- 4537865
2248 when OTHERS then
2249 if p_commit = FND_API.G_TRUE then
2250 rollback to copy_structure;
2251 end if;
2252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2253 -- 4537865
2254 x_msg_count := 1 ;
2255 x_msg_data := SUBSTRB(SQLERRM,1,240);
2256 -- 4537865
2257 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2258 p_procedure_name => 'COPY_STRUCTURE',
2259 p_error_text => x_msg_data); -- 4537865
2260 raise;
2261 END Copy_Structure;
2262
2263
2264 -- API name : Copy_Structure_Version
2265 -- Type : Public Procedure
2266 -- Pre-reqs : None
2267 -- Return Value : N/A
2268 -- Parameters
2269 -- p_api_version IN NUMBER := 1.0
2270 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2271 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2272 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2273 -- p_validation_level IN VARCHAR2 := 100
2274 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2275 -- p_debug_mode IN VARCHAR2 := 'N'
2276 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2277 -- p_structure_version_id IN NUMBER
2278 -- p_new_struct_ver_name IN VARCHAR2
2279 -- p_new_struct_ver_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2280 -- x_new_struct_ver_id OUT NUMBER
2281 -- x_return_status OUT VARCHAR2
2282 -- x_msg_count OUT NUMBER
2283 -- x_msg_data OUT VARCHAR2
2284 --
2285 -- History
2286 --
2287 -- 25-JUN-01 HSIU -Created
2288 --
2289 --
2290
2291
2292 procedure Copy_Structure_Version
2293 (
2294 p_api_version IN NUMBER := 1.0
2295 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2296 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2297 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2298 ,p_validation_level IN VARCHAR2 := 100
2299 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2300 ,p_debug_mode IN VARCHAR2 := 'N'
2301 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2302 ,p_structure_version_id IN NUMBER
2303 ,p_new_struct_ver_name IN VARCHAR2
2304 ,p_new_struct_ver_desc IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2305 ,p_change_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2306 ,x_new_struct_ver_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2307 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2308 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2309 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2310 )
2311 IS
2312 l_api_name CONSTANT VARCHAR(30) := 'COPY_STRUCTURE_VERSION';
2313 l_api_version CONSTANT NUMBER := 1.0;
2314
2315 l_new_struct_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
2316 l_return_status VARCHAR2(1);
2317 l_msg_count NUMBER;
2318 l_msg_data VARCHAR2(250);
2319 l_data VARCHAR2(250);
2320 l_msg_index_out NUMBER;
2321 BEGIN
2322
2323 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE_VERSION');
2324
2325 IF (p_debug_mode = 'Y') THEN
2326 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE_VERSION begin');
2327 END IF;
2328
2329 IF (p_commit = FND_API.G_TRUE) THEN
2330 savepoint copy_structure_version;
2331 END IF;
2332
2333 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2335 END IF;
2336
2337 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2338 FND_MSG_PUB.initialize;
2339 END IF;
2340
2341 PA_PROJECT_STRUCTURE_PVT1.Copy_Structure_Version_bulk
2342 ( p_commit => FND_API.G_FALSE
2343 ,p_validate_only => p_validate_only
2344 ,p_validation_level => p_validation_level
2345 ,p_calling_module => p_calling_module
2346 ,p_debug_mode => p_debug_mode
2347 ,p_max_msg_count => p_max_msg_count
2348 ,p_structure_version_id => p_structure_version_id
2349 ,p_new_struct_ver_name => p_new_struct_ver_name
2350 ,p_new_struct_ver_desc => p_new_struct_ver_desc
2351 ,p_change_reason_code => p_change_reason_code
2352 ,x_new_struct_ver_id => l_new_struct_ver_id
2353 ,x_return_status => l_return_status
2354 ,x_msg_count => l_msg_count
2355 ,x_msg_data => l_msg_data);
2356
2357 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2358 x_msg_count := FND_MSG_PUB.count_msg;
2359 IF x_msg_count = 1 then
2360 pa_interface_utils_pub.get_messages
2361 (p_encoded => FND_API.G_TRUE,
2362 p_msg_index => 1,
2363 -- p_msg_count => l_msg_count,
2364 -- p_msg_data => l_msg_data,
2365 p_data => l_data,
2366 p_msg_index_out => l_msg_index_out);
2367 x_msg_data := l_data;
2368 END IF;
2369 raise FND_API.G_EXC_ERROR;
2370 END IF;
2371
2372 x_new_struct_ver_id := l_new_struct_ver_id;
2373 x_return_status := FND_API.G_RET_STS_SUCCESS;
2374
2375 IF (p_commit = FND_API.G_TRUE) THEN
2376 COMMIT;
2377 END IF;
2378
2379 IF (p_debug_mode = 'Y') THEN
2380 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.COPY_STRUCTURE_VERSION END');
2381 END IF;
2382
2383 EXCEPTION
2384 when FND_API.G_EXC_ERROR then
2385 if p_commit = FND_API.G_TRUE then
2386 rollback to copy_structure_version;
2387 end if;
2388 x_return_status := FND_API.G_RET_STS_ERROR;
2389 x_new_struct_ver_id := NULL ; -- 4537865
2390 when FND_API.G_EXC_UNEXPECTED_ERROR then
2391 if p_commit = FND_API.G_TRUE then
2392 rollback to copy_structure_version;
2393 end if;
2394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2395 -- 4537865
2396 x_msg_count := 1 ;
2397 x_msg_data := SUBSTRB(SQLERRM,1,240);
2398 x_new_struct_ver_id := NULL ;
2399 -- 4537865
2400 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2401 p_procedure_name => 'COPY_STRUCTURE_VERSION',
2402 p_error_text => x_msg_data); -- 4537865
2403 when OTHERS then
2404 if p_commit = FND_API.G_TRUE then
2405 rollback to copy_structure_version;
2406 end if;
2407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2408 -- 4537865
2409 x_msg_count := 1 ;
2410 x_msg_data := SUBSTRB(SQLERRM,1,240);
2411 x_new_struct_ver_id := NULL ;
2412 -- 4537865
2413 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2414 p_procedure_name => 'COPY_STRUCTURE_VERSION',
2415 p_error_text => x_msg_data); -- 4537865
2416 raise;
2417 END Copy_Structure_Version;
2418
2419
2420 procedure SUBMIT_WORKPLAN
2421 (
2422 p_api_version IN NUMBER := 1.0
2423 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2424 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2425 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2426 ,p_validation_level IN VARCHAR2 := 100
2427 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2428 ,p_debug_mode IN VARCHAR2 := 'N'
2429 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2430 ,p_project_id IN NUMBER
2431 ,p_structure_id IN NUMBER
2432 ,p_structure_version_id IN NUMBER
2433 ,p_responsibility_id IN NUMBER
2434 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2435 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2436 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2437 )
2438 IS
2439 l_api_name CONSTANT VARCHAR(30) := 'SUBMIT_WORKPLAN';
2440 l_api_version CONSTANT NUMBER := 1.0;
2441
2442 l_return_status VARCHAR2(1);
2443 l_msg_count NUMBER;
2444 l_msg_data VARCHAR2(250);
2445 l_data VARCHAR2(250);
2446 l_msg_index_out NUMBER;
2447 l_project_id NUMBER;
2448 BEGIN
2449 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.SUBMIT_WORKPLAN');
2450
2451 IF (p_debug_mode = 'Y') THEN
2452 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.SUBMIT_WORKPLAN begin');
2453 END IF;
2454
2455 IF (p_commit = FND_API.G_TRUE) THEN
2456 savepoint submit_workplan;
2457 END IF;
2458
2459 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461 END IF;
2462
2463 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2464 FND_MSG_PUB.initialize;
2465 END IF;
2466
2467 --Check if this structure can be published (ie, if linked structures are published)
2468 IF ('Y' <> PA_PROJECT_STRUCTURE_UTILS.Check_Publish_Struc_Ver_Ok(p_structure_version_id)) THEN
2469 PA_UTILS.ADD_MESSAGE('PA','PA_PS_OTHER_WORKING_LOCKED');
2470 x_msg_data := 'PA_PS_OTHER_WORKING_LOCKED';
2471 RAISE FND_API.G_EXC_ERROR;
2472 END IF;
2473
2474 PA_PROJECT_STRUCTURE_PVT1.SUBMIT_WORKPLAN(
2475 p_api_version => p_api_version
2476 ,p_commit => p_commit
2477 ,p_validate_only => p_validate_only
2478 ,p_validation_level => p_validation_level
2479 ,p_calling_module => p_calling_module
2480 ,p_debug_mode => p_debug_mode
2481 ,p_max_msg_count => p_max_msg_count
2482 ,p_project_id => p_project_id
2483 ,p_structure_id => p_structure_id
2484 ,p_structure_version_id => p_structure_version_id
2485 ,p_responsibility_id => p_responsibility_id
2486 ,x_return_status => x_return_status
2487 ,x_msg_count => x_msg_count
2488 ,x_msg_data => x_msg_data
2489 );
2490
2491 x_msg_count := FND_MSG_PUB.count_msg;
2492 IF (x_msg_count > 0) THEN
2493 IF x_msg_count = 1 then
2494 pa_interface_utils_pub.get_messages
2495 (p_encoded => FND_API.G_TRUE,
2496 p_msg_index => 1,
2497 p_data => l_data,
2498 p_msg_index_out => l_msg_index_out);
2499 x_msg_data := l_data;
2500 END IF;
2501 raise FND_API.G_EXC_ERROR;
2502 END IF;
2503
2504 --call private API
2505 x_return_status := FND_API.G_RET_STS_SUCCESS;
2506
2507 IF (p_commit = FND_API.G_TRUE) THEN
2508 COMMIT;
2509 END IF;
2510
2511 IF (p_debug_mode = 'Y') THEN
2512 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.SUBMIT_WORKPLAN end');
2513 END IF;
2514
2515 EXCEPTION
2516 when FND_API.G_EXC_ERROR then
2517 if p_commit = FND_API.G_TRUE then
2518 rollback to submit_workplan;
2519 end if;
2520 x_return_status := FND_API.G_RET_STS_ERROR;
2521 when FND_API.G_EXC_UNEXPECTED_ERROR then
2522 if p_commit = FND_API.G_TRUE then
2523 rollback to submit_workplan;
2524 end if;
2525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2526 -- 4537865
2527 x_msg_count := 1 ;
2528 x_msg_data := SUBSTRB(SQLERRM,1,240);
2529 -- 4537865
2530 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2531 p_procedure_name => 'SUBMIT_WORKPLAN',
2532 p_error_text => x_msg_data); -- 4537865
2533 when OTHERS then
2534 if p_commit = FND_API.G_TRUE then
2535 rollback to submit_workplan;
2536 end if;
2537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2538 -- 4537865
2539 x_msg_count := 1 ;
2540 x_msg_data := SUBSTRB(SQLERRM,1,240);
2541 -- 4537865
2542 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2543 p_procedure_name => 'SUBMIT_WORKPLAN',
2544 p_error_text => x_msg_data); -- 4537865
2545 raise;
2546 END SUBMIT_WORKPLAN;
2547
2548
2549 PROCEDURE rework_workplan
2550 (
2551 p_api_version IN NUMBER := 1.0
2552 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2553 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2554 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2555 ,p_validation_level IN VARCHAR2 := 100
2556 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2557 ,p_debug_mode IN VARCHAR2 := 'N'
2558 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2559 ,p_project_id IN NUMBER
2560 ,p_structure_version_id IN NUMBER
2561 ,p_record_version_number IN NUMBER
2562 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2563 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2564 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2565 )
2566 IS
2567 l_api_name CONSTANT VARCHAR(30) := 'REWORK_WORKPLAN';
2568 l_api_version CONSTANT NUMBER := 1.0;
2569
2570 l_return_status VARCHAR2(1);
2571 l_msg_count NUMBER;
2572 l_msg_data VARCHAR2(250);
2573 l_data VARCHAR2(250);
2574 l_msg_index_out NUMBER;
2575 l_project_id NUMBER;
2576 BEGIN
2577 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.REWORK_WORKPLAN');
2578
2579 IF (p_debug_mode = 'Y') THEN
2580 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.REWORK_WORKPLAN begin');
2581 END IF;
2582
2583 IF (p_commit = FND_API.G_TRUE) THEN
2584 savepoint rework_workplan;
2585 END IF;
2586
2587 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2589 END IF;
2590
2591 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2592 FND_MSG_PUB.initialize;
2593 END IF;
2594
2595 PA_PROJECT_STRUCTURE_PVT1.REWORK_WORKPLAN(
2596 p_api_version => 1.0
2597 ,p_commit => p_commit
2598 ,p_validate_only => p_validate_only
2599 ,p_validation_level => p_validation_level
2600 ,p_calling_module => p_calling_module
2601 ,p_debug_mode => p_debug_mode
2602 ,p_max_msg_count => p_max_msg_count
2603 ,p_project_id => p_project_id
2604 ,p_structure_version_id => p_structure_version_id
2605 ,p_record_version_number => p_record_version_number
2606 ,x_return_status => x_return_status
2607 ,x_msg_count => x_msg_count
2608 ,x_msg_data => x_msg_data
2609 );
2610
2611 x_msg_count := FND_MSG_PUB.count_msg;
2612 IF (x_msg_count > 0) THEN
2613 IF x_msg_count = 1 then
2614 pa_interface_utils_pub.get_messages
2615 (p_encoded => FND_API.G_TRUE,
2616 p_msg_index => 1,
2617 p_data => l_data,
2618 p_msg_index_out => l_msg_index_out);
2619 x_msg_data := l_data;
2620 END IF;
2621 raise FND_API.G_EXC_ERROR;
2622 END IF;
2623
2624 x_return_status := FND_API.G_RET_STS_SUCCESS;
2625
2626 IF (p_commit = FND_API.G_TRUE) THEN
2627 COMMIT;
2628 END IF;
2629
2630 IF (p_debug_mode = 'Y') THEN
2631 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.REWORK_WORKPLAN end');
2632 END IF;
2633
2634 EXCEPTION
2635 when FND_API.G_EXC_ERROR then
2636 if p_commit = FND_API.G_TRUE then
2637 rollback to rework_workplan;
2638 end if;
2639 x_return_status := FND_API.G_RET_STS_ERROR;
2640 when FND_API.G_EXC_UNEXPECTED_ERROR then
2641 if p_commit = FND_API.G_TRUE then
2642 rollback to rework_workplan;
2643 end if;
2644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2645 -- 4537865
2646 x_msg_count := 1 ;
2647 x_msg_data := SUBSTRB(SQLERRM,1,240);
2648 -- 4537865
2649 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2650 p_procedure_name => 'REWORK_WORKPLAN',
2651 p_error_text => x_msg_data); -- 4537865
2652 when OTHERS then
2653 if p_commit = FND_API.G_TRUE then
2654 rollback to rework_workplan;
2655 end if;
2656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2657 -- 4537865
2658 x_msg_count := 1 ;
2659 x_msg_data := SUBSTRB(SQLERRM,1,240);
2660 -- 4537865
2661 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2662 p_procedure_name => 'REWORK_WORKPLAN',
2663 p_error_text => x_msg_data); -- 4537865
2664 raise;
2665 END rework_workplan;
2666
2667
2668 -- API name : update_structures_setup_attr
2669 -- Type : Update API
2670 -- Pre-reqs : None
2671 -- Return Value : Update_structures_setup_attr
2672 --
2673 -- Parameters
2674 -- p_project_id IN NUMBER
2675 -- p_workplan_enabled_flag IN VARCHAR2
2676 -- p_financial_enabled_flag IN VARCHAR2
2677 -- p_sharing_enabled_flag IN VARCHAR2
2678 --FP M changes bug 3301192
2679 -- p_deliverables_enabled_flag IN VARCHAR2
2680 -- p_sharing_option_code IN VARCHAR2
2681 --End FP M changes bug 3301192
2682 -- p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2683 -- p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2684 -- x_return_status OUT VARCHAR2
2685 -- x_msg_count OUT NUMBER
2686 -- x_msg_data OUT VARCHAR2
2687 --
2688 -- History
2689 --
2690 -- 26-JUL-02 HSIU -Created
2691 -- 30-Mar-04 JYAN added p_sys_program_flag and p_allow_multi_prog_rollup
2692 --
2693 PROCEDURE update_structures_setup_attr
2694 ( p_api_version IN NUMBER := 1.0
2695 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2696 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2697 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2698 ,p_validation_level IN VARCHAR2 := 100
2699 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2700 ,p_debug_mode IN VARCHAR2 := 'N'
2701 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2702 ,p_project_id IN NUMBER
2703 ,p_workplan_enabled_flag IN VARCHAR2
2704 ,p_financial_enabled_flag IN VARCHAR2
2705 ,p_sharing_enabled_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2706 --FP M changes bug 3301192
2707 ,p_deliverables_enabled_flag IN VARCHAR2
2708 ,p_sharing_option_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2709 --End FP M changes bug 3301192
2710 ,p_sys_program_flag IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2711 ,p_allow_multi_prog_rollup IN varchar2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
2712 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2713 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2714 ,x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2715 p_cbs_version_id IN Number default null --Added for CBS phase 2
2716 ,p_cbs_enable_flag IN VARCHAR2 default 'N' --Bug16083858
2717 )
2718 IS
2719 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_STRUCTURES_SETUP_ATTR';
2720 l_api_version CONSTANT NUMBER := 1.0;
2721 l_data VARCHAR2(250);
2722 l_msg_index_out NUMBER;
2723
2724 BEGIN
2725 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr');
2726
2727 IF (p_debug_mode = 'Y') THEN
2728 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr begin');
2729 END IF;
2730
2731 IF (p_commit = FND_API.G_TRUE) THEN
2732 savepoint update_struc_setup_attr_pub;
2733 END IF;
2734
2735 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2737 END IF;
2738
2739 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2740 FND_MSG_PUB.initialize;
2741 END IF;
2742
2743 PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURES_SETUP_ATTR(
2744 p_api_version => p_api_version
2745 ,p_init_msg_list => p_init_msg_list
2746 ,p_commit => p_commit
2747 ,p_validate_only => p_validate_only
2748 ,p_validation_level => p_validation_level
2749 ,p_calling_module => p_calling_module
2750 ,p_debug_mode => p_debug_mode
2751 ,p_max_msg_count => p_max_msg_count
2752 ,p_project_id => p_project_id
2753 ,p_workplan_enabled_flag => p_workplan_enabled_flag
2754 ,p_financial_enabled_flag => p_financial_enabled_flag
2755 ,p_sharing_enabled_flag => 'N' --p_sharing_enabled_flag
2756 ,p_deliverables_enabled_flag => p_deliverables_enabled_flag
2757 ,p_sharing_option_code => p_sharing_option_code
2758 ,p_sys_program_flag => p_sys_program_flag
2759 ,p_allow_multi_prog_rollup => p_allow_multi_prog_rollup
2760 ,x_return_status => x_return_status
2761 ,x_msg_count => x_msg_count
2762 ,x_msg_data => x_msg_data
2763 ,p_cbs_version_id => p_cbs_version_id
2764 ,p_cbs_enable_flag => p_cbs_enable_flag -- Bug16083858
2765 );
2766
2767 x_msg_count := FND_MSG_PUB.count_msg;
2768 IF (x_msg_count > 0) THEN
2769 IF x_msg_count = 1 then
2770 pa_interface_utils_pub.get_messages
2771 (p_encoded => FND_API.G_TRUE,
2772 p_msg_index => 1,
2773 p_data => l_data,
2774 p_msg_index_out => l_msg_index_out);
2775 x_msg_data := l_data;
2776 END IF;
2777 raise FND_API.G_EXC_ERROR;
2778 END IF;
2779
2780 x_return_status := FND_API.G_RET_STS_SUCCESS;
2781
2782 IF (p_commit = FND_API.G_TRUE) THEN
2783 COMMIT;
2784 END IF;
2785
2786 IF (p_debug_mode = 'Y') THEN
2787 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURES_SETUP_ATTR end');
2788 END IF;
2789
2790 EXCEPTION
2791 when FND_API.G_EXC_ERROR then
2792 if p_commit = FND_API.G_TRUE then
2793 rollback to update_struc_setup_attr_pub;
2794 end if;
2795 x_return_status := FND_API.G_RET_STS_ERROR;
2796 when FND_API.G_EXC_UNEXPECTED_ERROR then
2797 if p_commit = FND_API.G_TRUE then
2798 rollback to update_struc_setup_attr_pub;
2799 end if;
2800 -- 4537865
2801 x_msg_count := 1 ;
2802 x_msg_data := SUBSTRB(SQLERRM,1,240);
2803 -- 4537865
2804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2805 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2806 p_procedure_name => 'update_structures_setup_attr',
2807 p_error_text => x_msg_data); -- 4537865
2808 when OTHERS then
2809 if p_commit = FND_API.G_TRUE then
2810 rollback to update_struc_setup_attr_pub;
2811 end if;
2812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2813 -- 4537865
2814 x_msg_count := 1 ;
2815 x_msg_data := SUBSTRB(SQLERRM,1,240);
2816 -- 4537865
2817 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2818 p_procedure_name => 'update_structures_setup_attr',
2819 p_error_text => x_msg_data); -- 4537865
2820 raise;
2821 END update_structures_setup_attr;
2822
2823
2824 PROCEDURE update_workplan_versioning
2825 ( p_api_version IN NUMBER := 1.0
2826 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2827 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2828 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2829 ,p_validation_level IN VARCHAR2 := 100
2830 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2831 ,p_debug_mode IN VARCHAR2 := 'N'
2832 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2833 ,p_proj_element_id IN NUMBER
2834 ,p_enable_wp_version_flag IN VARCHAR2
2835 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2836 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2837 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2838 )
2839 IS
2840 l_api_name CONSTANT VARCHAR(30) := 'update_workplan_versioning';
2841 l_api_version CONSTANT NUMBER := 1.0;
2842 l_data VARCHAR2(250);
2843 l_msg_index_out NUMBER;
2844
2845 BEGIN
2846 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr');
2847
2848 IF (p_debug_mode = 'Y') THEN
2849 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_structures_setup_attr begin');
2850 END IF;
2851
2852 IF (p_commit = FND_API.G_TRUE) THEN
2853 savepoint update_wp_versioning_pub;
2854 END IF;
2855
2856 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2858 END IF;
2859
2860 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2861 FND_MSG_PUB.initialize;
2862 END IF;
2863
2864 PA_PROJECT_STRUCTURE_PVT1.update_workplan_versioning(
2865 p_api_version => p_api_version
2866 ,p_init_msg_list => p_init_msg_list
2867 ,p_commit => p_commit
2868 ,p_validate_only => p_validate_only
2869 ,p_validation_level => p_validation_level
2870 ,p_calling_module => p_calling_module
2871 ,p_debug_mode => p_debug_mode
2872 ,p_max_msg_count => p_max_msg_count
2873 ,p_proj_element_id => p_proj_element_id
2874 ,p_enable_wp_version_flag => p_enable_wp_version_flag
2875 ,x_return_status => x_return_status
2876 ,x_msg_count => x_msg_count
2877 ,x_msg_data => x_msg_data
2878 );
2879
2880 x_msg_count := FND_MSG_PUB.count_msg;
2881 IF (x_msg_count > 0) THEN
2882 IF x_msg_count = 1 then
2883 pa_interface_utils_pub.get_messages
2884 (p_encoded => FND_API.G_TRUE,
2885 p_msg_index => 1,
2886 p_data => l_data,
2887 p_msg_index_out => l_msg_index_out);
2888 x_msg_data := l_data;
2889 END IF;
2890 raise FND_API.G_EXC_ERROR;
2891 END IF;
2892
2893 x_return_status := FND_API.G_RET_STS_SUCCESS;
2894
2895 IF (p_commit = FND_API.G_TRUE) THEN
2896 COMMIT;
2897 END IF;
2898
2899 IF (p_debug_mode = 'Y') THEN
2900 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_WORKPLAN_VERSIONING end');
2901 END IF;
2902
2903 EXCEPTION
2904 when FND_API.G_EXC_ERROR then
2905 if p_commit = FND_API.G_TRUE then
2906 rollback to update_wp_versioning_pub;
2907 end if;
2908 x_return_status := FND_API.G_RET_STS_ERROR;
2909 when FND_API.G_EXC_UNEXPECTED_ERROR then
2910 if p_commit = FND_API.G_TRUE then
2911 rollback to update_wp_versioning_pub;
2912 end if;
2913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2914 -- 4537865
2915 x_msg_count := 1 ;
2916 x_msg_data := SUBSTRB(SQLERRM,1,240);
2917 -- 4537865
2918 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2919 p_procedure_name => 'update_workplan_versioning',
2920 p_error_text => x_msg_data); -- 4537865
2921 when OTHERS then
2922 if p_commit = FND_API.G_TRUE then
2923 rollback to update_wp_versioning_pub;
2924 end if;
2925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2926 -- 4537865
2927 x_msg_count := 1 ;
2928 x_msg_data := SUBSTRB(SQLERRM,1,240);
2929 -- 4537865
2930 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
2931 p_procedure_name => 'update_workplan_versioning',
2932 p_error_text => x_msg_data); -- 4537865
2933 raise;
2934 END update_workplan_versioning;
2935
2936 -- API name : Delete_Working_Struc_Ver
2937 -- Type : Public Procedure
2938 -- Pre-reqs : None
2939 -- Return Value : N/A
2940 -- Parameters
2941 -- p_api_version IN NUMBER := 1.0
2942 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2943 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
2944 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2945 -- p_validation_level IN VARCHAR2 := 100
2946 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2947 -- p_debug_mode IN VARCHAR2 := 'N'
2948 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2949 -- p_structure_version_id IN NUMBER
2950 -- p_record_version_number IN NUMBER
2951 -- x_return_status OUT VARCHAR2
2952 -- x_msg_count OUT NUMBER
2953 -- x_msg_data OUT VARCHAR2
2954 --
2955 -- History
2956 --
2957 -- 17-DEC-02 HSIU -Created
2958 --
2959 --
2960
2961
2962 procedure Delete_Working_Struc_Ver
2963 (
2964 p_api_version IN NUMBER := 1.0
2965 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2966 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2967 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2968 ,p_validation_level IN VARCHAR2 := 100
2969 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2970 ,p_debug_mode IN VARCHAR2 := 'N'
2971 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2972 ,p_structure_version_id IN NUMBER
2973 ,p_record_version_number IN NUMBER
2974 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2975 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2976 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2977 )
2978 IS
2979 l_api_name CONSTANT VARCHAR(30) := 'Delete_Working_Struc_Ver';
2980 l_api_version CONSTANT NUMBER := 1.0;
2981
2982 l_return_status VARCHAR2(1);
2983 l_msg_count NUMBER;
2984 l_msg_data VARCHAR2(250);
2985 l_data VARCHAR2(250);
2986 l_msg_index_out NUMBER;
2987 l_error_message_code VARCHAR2(30);
2988
2989 CURSOR c1 IS
2990 select project_id
2991 from pa_proj_element_versions
2992 where element_version_id = p_structure_version_id;
2993 l_project_id NUMBER;
2994
2995 BEGIN
2996 pa_debug.init_err_stack('PA_PROJECT_STRUCTURE_PUB1.Delete_Working_Struc_Ver');
2997
2998 IF (p_debug_mode = 'Y') THEN
2999 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_WORKINGSTRUC_VER begin');
3000 END IF;
3001
3002 IF (p_commit = FND_API.G_TRUE) THEN
3003 savepoint delete_working_struc_ver;
3004 END IF;
3005
3006 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3008 END IF;
3009
3010 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3011 FND_MSG_PUB.initialize;
3012 END IF;
3013
3014 OPEN c1;
3015 FETCH c1 into l_project_id;
3016 CLOSE c1;
3017
3018 --Check if locked
3019 PA_PROJECT_STRUCTURE_UTILS.Check_Delete_Structure_Ver_Ok(
3020 l_project_id
3021 ,p_structure_version_id
3022 ,l_return_status
3023 ,l_error_message_code);
3024
3025 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3026 PA_UTILS.ADD_MESSAGE('PA', l_error_message_code);
3027 x_msg_data := l_error_message_code;
3028 RAISE FND_API.G_EXC_ERROR;
3029 END IF;
3030
3031 --Check if it is ok to delete working version
3032 IF ('N' = PA_PROJECT_STRUCTURE_UTILS.check_del_work_struc_ver_ok(p_structure_version_id)) THEN
3033 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_DEL_WK_STRUC_VER_ERR');
3034 x_msg_data := 'PA_PS_DEL_WK_STRUC_VER_ERR';
3035 RAISE FND_API.G_EXC_ERROR;
3036 END IF;
3037
3038 PA_PROJECT_STRUCTURE_PVT1.Delete_Struc_Ver_Wo_Val(p_commit => 'N',
3039 p_debug_mode => p_debug_mode,
3040 p_structure_version_id => p_structure_version_id,
3041 p_record_version_number => p_record_version_number,
3042 x_return_status => l_return_status,
3043 x_msg_count => l_msg_count,
3044 x_msg_data => l_msg_data);
3045
3046 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3047 x_msg_count := FND_MSG_PUB.count_msg;
3048 IF x_msg_count = 1 then
3049 pa_interface_utils_pub.get_messages
3050 (p_encoded => FND_API.G_TRUE,
3051 p_msg_index => 1,
3052 p_data => l_data,
3053 p_msg_index_out => l_msg_index_out);
3054 x_msg_data := l_data;
3055 END IF;
3056 raise FND_API.G_EXC_ERROR;
3057 END IF;
3058
3059 x_return_status := FND_API.G_RET_STS_SUCCESS;
3060
3061 IF (p_debug_mode = 'Y') THEN
3062 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION end');
3063 END IF;
3064
3065 EXCEPTION
3066 when FND_API.G_EXC_ERROR then
3067 if p_commit = FND_API.G_TRUE then
3068 rollback to delete_working_struc_ver;
3069 end if;
3070 x_msg_count := FND_MSG_PUB.count_msg;
3071 x_return_status := FND_API.G_RET_STS_ERROR;
3072 when FND_API.G_EXC_UNEXPECTED_ERROR then
3073 if p_commit = FND_API.G_TRUE then
3074 rollback to delete_working_struc_ver;
3075 end if;
3076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3077 -- 4537865
3078 x_msg_count := 1 ;
3079 x_msg_data := SUBSTRB(SQLERRM,1,240);
3080 -- 4537865
3081 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3082 p_procedure_name => 'Delete_Working_Struc_Ver',
3083 p_error_text => x_msg_data); -- 4537865
3084 when OTHERS then
3085 if p_commit = FND_API.G_TRUE then
3086 rollback to delete_working_struc_ver;
3087 end if;
3088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3089 -- 4537865
3090 x_msg_count := 1 ;
3091 x_msg_data := SUBSTRB(SQLERRM,1,240);
3092 -- 4537865
3093 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3094 p_procedure_name => 'Delete_Working_Struc_Ver',
3095 p_error_text => x_msg_data); -- 4537865
3096 raise;
3097 END DELETE_WORKING_STRUC_VER;
3098
3099 procedure ENABLE_FINANCIAL_STRUCTURE
3100 (
3101 p_api_version IN NUMBER := 1.0
3102 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3103 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3104 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3105 ,p_validation_level IN NUMBER := 100
3106 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3107 ,p_debug_mode IN VARCHAR2 := 'N'
3108 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3109 ,p_project_id IN NUMBER
3110 ,p_proj_element_id IN NUMBER
3111 ,p_approval_reqd_flag IN VARCHAR2 DEFAULT 'N'
3112 ,p_auto_publish_flag IN VARCHAR2 DEFAULT 'N'
3113 ,p_approver_source_id IN NUMBER DEFAULT NULL
3114 ,p_approver_source_type IN NUMBER DEFAULT NULL
3115 ,p_default_display_lvl IN NUMBER DEFAULT 0
3116 ,p_enable_wp_version_flag IN VARCHAR2 DEFAULT 'N'
3117 ,p_auto_pub_upon_creation_flag IN VARCHAR2 DEFAULT 'N'
3118 ,p_auto_sync_txn_date_flag IN VARCHAR2 DEFAULT 'N'
3119 ,p_txn_date_sync_buf_days IN NUMBER DEFAULT NULL
3120 ,p_lifecycle_version_id IN NUMBER DEFAULT NULL
3121 ,p_current_phase_version_id IN NUMBER DEFAULT NULL
3122 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3123 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3124 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3125 )
3126
3127 IS
3128
3129 l_api_name CONSTANT VARCHAR(30) := 'ENABLE_FINANCIAL_STRUCTURE';
3130 l_api_version CONSTANT NUMBER := 1.0;
3131 l_return_status VARCHAR2(1);
3132 l_msg_count NUMBER;
3133 l_msg_data VARCHAR2(250);
3134 l_data VARCHAR2(250);
3135 l_msg_index_out NUMBER;
3136
3137 l_proj_progress_attr_id NUMBER;
3138
3139 BEGIN
3140
3141 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.ENABLE_FINANCIAL_STRUCTURE');
3142
3143 IF (p_debug_mode = 'Y') THEN
3144 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.ENABLE_FINANCIAL_STRUCTURE begin');
3145 END IF;
3146
3147 IF (p_commit = FND_API.G_TRUE) THEN
3148 savepoint ENABLE_FINANCIAL_STRUCTURE;
3149 END IF;
3150
3151 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3153 END IF;
3154
3155 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3156 FND_MSG_PUB.initialize;
3157 END IF;
3158
3159 PA_WORKPLAN_ATTR_PVT.CREATE_PROJ_WORKPLAN_ATTRS(
3160 p_commit => p_commit
3161 ,p_validate_only => p_validate_only
3162 ,p_validation_level => p_validation_level
3163 ,p_calling_module => p_calling_module
3164 ,p_debug_mode => p_debug_mode
3165 ,p_max_msg_count => p_max_msg_count
3166 ,p_project_id => p_project_id
3167 ,p_proj_element_id => p_proj_element_id
3168 ,p_approval_reqd_flag => p_approval_reqd_flag
3169 ,p_auto_publish_flag => p_auto_publish_flag
3170 ,p_approver_source_id => p_approver_source_id
3171 ,p_approver_source_type => p_approver_source_type
3172 ,p_default_display_lvl => p_default_display_lvl
3173 ,p_enable_wp_version_flag => p_enable_wp_version_flag
3174 ,p_auto_pub_upon_creation_flag => p_auto_pub_upon_creation_flag
3175 ,p_auto_sync_txn_date_flag => p_auto_sync_txn_date_flag
3176 ,p_txn_date_sync_buf_days => p_txn_date_sync_buf_days
3177 ,p_lifecycle_version_id => p_lifecycle_version_id
3178 ,p_current_phase_version_id => p_current_phase_version_id
3179 ,x_return_status => l_return_status
3180 ,x_msg_count => x_msg_count
3181 ,x_msg_data => x_msg_data
3182 );
3183
3184 -- 4537865 : This was missing earlier
3185 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3186 x_msg_count := FND_MSG_PUB.count_msg;
3187 IF x_msg_count = 1 then
3188 pa_interface_utils_pub.get_messages
3189 (p_encoded => FND_API.G_TRUE,
3190 p_msg_index => 1,
3191 p_data => l_data,
3192 p_msg_index_out => l_msg_index_out);
3193 x_msg_data := l_data;
3194 END IF;
3195 raise FND_API.G_EXC_ERROR;
3196 END IF;
3197 -- End : 4537865
3198 PA_PROGRESS_PUB.CREATE_PROJ_PROG_ATTR(
3199 p_api_version => l_api_version
3200 ,p_init_msg_list => p_init_msg_list
3201 ,p_commit => p_commit
3202 ,p_validate_only => p_validate_only
3203 ,p_validation_level => p_validation_level
3204 ,p_calling_module => p_calling_module
3205 ,p_debug_mode => p_debug_mode
3206 ,p_max_msg_count => p_max_msg_count
3207 ,p_project_id => p_project_id
3208 ,P_OBJECT_TYPE => 'PA_STRUCTURES'
3209 ,P_OBJECT_ID => p_proj_element_id
3210 ,P_PROGRESS_CYCLE_ID => to_number(null)
3211 ,P_WQ_ENABLE_FLAG => 'N'
3212 ,P_REMAIN_EFFORT_ENABLE_FLAG => 'N'
3213 ,P_PERCENT_COMP_ENABLE_FLAG => 'Y'
3214 ,P_NEXT_PROGRESS_UPDATE_DATE => to_date(null)
3215 ,p_TASK_WEIGHT_BASIS_CODE => 'COST'
3216 ,X_PROJ_PROGRESS_ATTR_ID => l_proj_progress_attr_id
3217 ,P_ALLOW_COLLAB_PROG_ENTRY => 'N'
3218 ,P_ALLW_PHY_PRCNT_CMP_OVERRIDES => 'Y'
3219 ,p_structure_type => 'FINANCIAL' --Amit
3220 ,x_return_status => l_return_status
3221 ,x_msg_count => l_msg_count
3222 ,x_msg_data => l_msg_data
3223 );
3224
3225 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3226 x_msg_count := FND_MSG_PUB.count_msg;
3227 IF x_msg_count = 1 then
3228 pa_interface_utils_pub.get_messages
3229 (p_encoded => FND_API.G_TRUE,
3230 p_msg_index => 1,
3231 -- p_msg_count => l_msg_count,
3232 -- p_msg_data => l_msg_data,
3233 p_data => l_data,
3234 p_msg_index_out => l_msg_index_out);
3235 x_msg_data := l_data;
3236 END IF;
3237 raise FND_API.G_EXC_ERROR;
3238 END IF;
3239
3240 x_return_status := FND_API.G_RET_STS_SUCCESS;
3241
3242 IF (p_commit = FND_API.G_TRUE) THEN
3243 COMMIT;
3244 END IF;
3245
3246 IF (p_debug_mode = 'Y') THEN
3247 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.ENABLE_FINANCIAL_STRUCTURE END');
3248 END IF;
3249
3250 EXCEPTION
3251 when FND_API.G_EXC_ERROR then
3252 if p_commit = FND_API.G_TRUE then
3253 rollback to ENABLE_FINANCIAL_STRUCTURE;
3254 end if;
3255 x_return_status := FND_API.G_RET_STS_ERROR;
3256 when FND_API.G_EXC_UNEXPECTED_ERROR then
3257 if p_commit = FND_API.G_TRUE then
3258 rollback to ENABLE_FINANCIAL_STRUCTURE;
3259 end if;
3260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3261 -- 4537865
3262 x_msg_count := 1 ;
3263 x_msg_data := SUBSTRB(SQLERRM,1,240);
3264 -- 4537865
3265 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3266 p_procedure_name => 'ENABLE_FINANCIAL_STRUCTURE',
3267 p_error_text => x_msg_data); -- 4537865
3268 when OTHERS then
3269 if p_commit = FND_API.G_TRUE then
3270 rollback to ENABLE_FINANCIAL_STRUCTURE;
3271 end if;
3272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3273 -- 4537865
3274 x_msg_count := 1 ;
3275 x_msg_data := SUBSTRB(SQLERRM,1,240);
3276 -- 4537865
3277 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3278 p_procedure_name => 'ENABLE_FINANCIAL_STRUCTURE',
3279 p_error_text => x_msg_data); -- 4537865
3280 raise;
3281
3282 END ENABLE_FINANCIAL_STRUCTURE;
3283
3284
3285
3286 procedure DISABLE_FINANCIAL_STRUCTURE
3287 (
3288 p_api_version IN NUMBER := 1.0
3289 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3290 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3291 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3292 ,p_validation_level IN NUMBER := 100
3293 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3294 ,p_debug_mode IN VARCHAR2 := 'N'
3295 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3296 ,p_project_id IN NUMBER
3297 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3298 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3299 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3300 )
3301
3302 IS
3303
3304 CURSOR C1 IS
3305 SELECT task_id
3306 FROM pa_tasks
3307 WHERE project_id = p_project_id
3308 AND top_task_id = task_id
3309 AND parent_task_id is NULL;
3310
3311 l_task_id NUMBER;
3312 l_parent_structure_id NUMBER;
3313 l_parent_structure_version_id NUMBER;
3314 l_record_version_number PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
3315 l_rowid VARCHAR2(255);
3316 l_check_sharing_enabled VARCHAR(1);
3317 l_proj_element_id NUMBER;
3318 l_api_name CONSTANT VARCHAR(30) := 'DISABLE_FINANCIAL_STRUCTURE';
3319 l_api_version CONSTANT NUMBER := 1.0;
3320 l_return_status VARCHAR2(1);
3321 l_msg_count NUMBER;
3322 l_msg_data VARCHAR2(250);
3323 l_data VARCHAR2(250);
3324 l_msg_index_out NUMBER;
3325
3326 l_err_code NUMBER := 0;
3327 l_err_stack VARCHAR2(630);
3328 l_err_stage VARCHAR2(80);
3329
3330 CURSOR get_struc_ver_info(c_structure_id NUMBER) IS
3331 SELECT element_version_id, record_version_number
3332 FROM pa_proj_element_versions
3333 where proj_element_id = c_structure_id
3334 and project_id = p_project_id;
3335
3336 BEGIN
3337 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE');
3338
3339 IF (p_debug_mode = 'Y') THEN
3340 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE begin');
3341 END IF;
3342
3343 IF (p_commit = FND_API.G_TRUE) THEN
3344 savepoint DISABLE_FINANCIAL_STRUCTURE;
3345 END IF;
3346
3347 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3349 END IF;
3350
3351 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3352 FND_MSG_PUB.initialize;
3353 END IF;
3354
3355 l_check_sharing_enabled := PA_PROJECT_STRUCTURE_UTILS.CHECK_SHARING_ENABLED(p_project_id);
3356
3357 /* hsiu: added, 3305199 */
3358 select a.proj_element_id into l_parent_structure_id
3359 from pa_proj_elements a, pa_proj_structure_types b, pa_structure_types c
3360 where a.proj_element_id = b.proj_element_id
3361 and a.project_id = p_project_id
3362 and b.structure_type_id = c.structure_type_id
3363 and c.structure_type = 'FINANCIAL';
3364 /* hsiu: changes end, 3305199 */
3365
3366 OPEN C1;
3367 LOOP
3368 FETCH C1 into l_task_id;
3369 EXIT when C1%NOTFOUND;
3370
3371 PA_TASK_UTILS.CHECK_DELETE_TASK_OK(x_task_id => l_task_id,
3372 x_err_code => l_err_code,
3373 x_err_stage => l_err_stage,
3374 x_err_stack => l_err_stack);
3375 IF (l_err_code <> 0) THEN
3376 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_CANT_DIS_FN_STR');
3377 x_msg_data := 'PA_PS_CANT_DIS_FN_STR';
3378 raise FND_API.G_EXC_ERROR; --Bug No 3517852 SMukka Stop processing and come out of the loop
3379 ELSE
3380
3381 /* Amit : Moved the below code outside loop
3382 select rowid into l_rowid from pa_proj_progress_attr
3383 where project_id = p_project_id
3384 and object_id = l_task_id ;
3385
3386
3387 PA_PROJ_PROGRESS_ATTR_PKG.delete_row(l_rowid);
3388 */
3389
3390 IF (l_check_sharing_enabled = 'Y') THEN
3391 PA_PROJECT_CORE.DELETE_TASK(x_task_id => l_task_id,
3392 x_validate_flag => 'N',
3393 x_err_code => l_err_code,
3394 x_err_stage => l_err_stage,
3395 x_err_stack => l_err_stack);
3396
3397 UPDATE PA_PROJ_ELEMENT_VERSIONS
3398 SET FINANCIAL_TASK_FLAG = 'N'
3399 WHERE PROJECT_ID = p_project_id
3400 AND PROJ_ELEMENT_ID = l_task_id;
3401 ELSE
3402 PA_PROJ_TASK_STRUC_PUB.DELETE_TASK_STRUCTURE(p_calling_module => 'PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE',
3403 p_project_id => p_project_id,
3404 p_task_id => l_task_id,
3405 x_msg_count => l_msg_count,
3406 x_msg_data => l_msg_data,
3407 x_return_status => l_return_status);
3408
3409 /* hsiu: 3305199
3410 SELECT B.PROJ_ELEMENT_ID INTO l_proj_element_id FROM PA_PROJECTS_ALL A,PA_PROJ_STRUCTURE_TYPES B,PA_PROJ_ELEMENTS C
3411 WHERE A.PROJECT_ID = C.PROJECT_ID
3412 AND B.PROJ_ELEMENT_ID = C.PROJ_ELEMENT_ID
3413 AND B.PROJ_STRUCTURE_TYPE_ID = 6
3414 AND A.PROJECT_ID = P_PROJECT_ID;
3415
3416 PA_WORKPLAN_ATTR_PVT.DELETE_PROJ_WORKPLAN_ATTRS(p_project_id => p_project_id,
3417 p_proj_element_id => l_proj_element_id,
3418 x_msg_count => l_msg_count,
3419 x_msg_data => l_msg_data,
3420 x_return_status => l_return_status);
3421 end changes hsiu 3305199 */
3422 END IF;
3423 END IF;
3424 END LOOP;
3425 CLOSE C1;
3426
3427 /* hsiu commented 3305199
3428 SELECT parent_structure_id, parent_structure_version_id into l_parent_structure_id, l_parent_structure_version_id
3429 FROM PA_STRUCT_TASK_WBS_V
3430 WHERE task_id = l_task_id;
3431 */
3432
3433 select rowid into l_rowid from pa_proj_progress_attr
3434 where project_id = p_project_id
3435 -- and object_id = l_task_id Amit
3436 and object_id = l_parent_structure_id --Amit
3437 AND structure_type = 'FINANCIAL'; --Amit
3438
3439 PA_PROJ_PROGRESS_ATTR_PKG.delete_row(l_rowid);
3440
3441 SELECT rowid into l_rowid
3442 FROM pa_proj_structure_types
3443 WHERE proj_element_id = l_parent_structure_id
3444 and STRUCTURE_TYPE_ID = 6;
3445
3446 PA_PROJ_STRUCTURE_TYPES_PKG.delete_row(l_rowid);
3447
3448 IF (l_check_sharing_enabled <> 'Y') THEN
3449
3450 select record_version_number
3451 INTO l_record_version_number
3452 from pa_proj_workplan_attr
3453 where proj_element_id = l_parent_structure_id;
3454
3455 PA_WORKPLAN_ATTR_PVT.DELETE_PROJ_WORKPLAN_ATTRS(p_validate_only => FND_API.G_FALSE,
3456 p_project_id => p_project_id,
3457 p_proj_element_id => l_parent_structure_id,
3458 p_record_version_number => l_record_version_number,
3459 x_msg_count => l_msg_count,
3460 x_msg_data => l_msg_data,
3461 x_return_status => l_return_status);
3462 END IF;
3463
3464 /* hsiu commented 3305199
3465 SELECT record_version_number into l_record_version_number
3466 FROM pa_proj_element_versions
3467 WHERE element_version_id = l_parent_structure_version_id;
3468 */
3469
3470 /*Introducing the following IF CLAUSE For Bug 3938654
3471 In case of SHARED Case ,What is happening is :
3472 The 'Only' Structure Version ID that is avaiable(which is shared by both WP and Financial structures)
3473 is retrieved by the Cursor get_struc_ver_info , and the Structure Version corresponding to the ID
3474 is getting deleted .
3475 It means indirectly the WP structure is also getting deleted .This should not happen .
3476 */
3477
3478 IF (l_check_sharing_enabled <> 'Y') THEN -- For Bug 3938654
3479 --hsiu: 3305199
3480 open get_struc_ver_info(l_parent_structure_id);
3481 LOOP
3482 FETCH get_struc_ver_info INTO l_parent_structure_version_id, l_record_version_number;
3483 EXIT WHEN get_Struc_ver_info%NOTFOUND;
3484
3485 PA_PROJECT_STRUCTURE_PUB1.DELETE_STRUCTURE_VERSION(
3486 p_structure_version_id => l_parent_structure_version_id
3487 ,p_record_version_number => l_record_version_number
3488 ,x_return_status => l_return_status
3489 ,x_msg_count => l_msg_count
3490 ,x_msg_data => l_msg_data
3491 );
3492 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3493 x_msg_count := FND_MSG_PUB.count_msg;
3494 IF x_msg_count = 1 then
3495 pa_interface_utils_pub.get_messages
3496 (p_encoded => FND_API.G_TRUE,
3497 p_msg_index => 1,
3498 p_data => l_data,
3499 p_msg_index_out => l_msg_index_out);
3500 x_msg_data := l_data;
3501 END IF;
3502 raise FND_API.G_EXC_ERROR;
3503 END IF;
3504 END LOOP;
3505 CLOSE get_struc_ver_info;
3506 --hsiu chagnes end: 3305199
3507 END IF ; --IF CLAUSE introduced for Bug 3938654
3508
3509 /* hsiu 3305199
3510 SELECT record_version_number into l_record_version_number
3511 FROM pa_proj_elements
3512 WHERE proj_element_id = l_parent_structure_id;
3513
3514 PA_PROJECT_STRUCTURE_PVT1.DELETE_STRUCTURE(
3515 p_structure_id => l_parent_structure_id
3516 ,p_record_version_number => l_record_version_number
3517 ,x_return_status => l_return_status
3518 ,x_msg_count => l_msg_count
3519 ,x_msg_data => l_msg_data
3520 );
3521
3522 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3523 x_msg_count := FND_MSG_PUB.count_msg;
3524 IF x_msg_count = 1 then
3525 pa_interface_utils_pub.get_messages
3526 (p_encoded => FND_API.G_TRUE,
3527 p_msg_index => 1,
3528 -- p_msg_count => l_msg_count,
3529 -- p_msg_data => l_msg_data,
3530 p_data => l_data,
3531 p_msg_index_out => l_msg_index_out);
3532 x_msg_data := l_data;
3533 END IF;
3534 raise FND_API.G_EXC_ERROR;
3535 END IF;
3536 */
3537
3538 x_return_status := FND_API.G_RET_STS_SUCCESS;
3539
3540 IF (p_commit = FND_API.G_TRUE) THEN
3541 COMMIT;
3542 END IF;
3543
3544 IF (p_debug_mode = 'Y') THEN
3545 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.DISABLE_FINANCIAL_STRUCTURE END');
3546 END IF;
3547
3548 EXCEPTION
3549 when FND_API.G_EXC_ERROR then
3550 if p_commit = FND_API.G_TRUE then
3551 rollback to DISABLE_FINANCIAL_STRUCTURE;
3552 end if;
3553 x_return_status := FND_API.G_RET_STS_ERROR;
3554 when FND_API.G_EXC_UNEXPECTED_ERROR then
3555 if p_commit = FND_API.G_TRUE then
3556 rollback to DISABLE_FINANCIAL_STRUCTURE;
3557 end if;
3558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3559 -- 4537865
3560 x_msg_count := 1 ;
3561 x_msg_data := SUBSTRB(SQLERRM,1,240);
3562 -- 4537865
3563 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3564 p_procedure_name => 'DISABLE_FINANCIAL_STRUCTURE',
3565 p_error_text => x_msg_data); -- 4537865
3566 when OTHERS then
3567 if p_commit = FND_API.G_TRUE then
3568 rollback to DISABLE_FINANCIAL_STRUCTURE;
3569 end if;
3570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3571 -- 4537865
3572 x_msg_count := 1 ;
3573 x_msg_data := SUBSTRB(SQLERRM,1,240);
3574 -- 4537865
3575 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3576 p_procedure_name => 'DISABLE_FINANCIAL_STRUCTURE',
3577 p_error_text => x_msg_data); -- 4537865
3578 raise;
3579
3580 END DISABLE_FINANCIAL_STRUCTURE;
3581
3582
3583
3584 -- API name : Clear_Financial_Flag
3585 -- Type : Public Procedure
3586 -- Pre-reqs : None
3587 -- Return Value : N/A
3588 -- Parameters
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 VARCHAR2 := 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 VARCHAR2
3601 -- x_msg_count OUT NUMBER
3602 -- x_msg_data OUT VARCHAR2
3603 --
3604 -- History
3605 -- 02-JAN-04 Rakesh Raghavan - Created
3606
3607 procedure CLEAR_FINANCIAL_FLAG
3608 (
3609 p_api_version IN NUMBER := 1.0
3610 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3611 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3612 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3613 ,p_validation_level IN NUMBER := 100
3614 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3615 ,p_debug_mode IN VARCHAR2 := 'N'
3616 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3617 ,p_project_id IN NUMBER
3618 ,p_task_version_id IN NUMBER
3619 ,p_structure_version_id IN NUMBER
3620 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3621 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3622 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3623 )
3624 IS
3625 l_return_status VARCHAR2(1);
3626 l_api_name CONSTANT VARCHAR(30) := 'CLEAR_FINANCIAL_FLAG';
3627 l_api_version CONSTANT NUMBER := 1.0;
3628 l_msg_count NUMBER;
3629 l_msg_data VARCHAR2(250);
3630 l_data VARCHAR2(250);
3631 l_msg_index_out NUMBER;
3632 BEGIN
3633
3634 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.CLEAR_FINANCIAL_FLAG');
3635
3636 IF (p_debug_mode = 'Y') THEN
3637 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CLEAR_FINANCIAL_FLAG begin');
3638 END IF;
3639
3640 IF (p_commit = FND_API.G_TRUE) THEN
3641 savepoint CLEAR_FINANCIAL_FLAG;
3642 END IF;
3643
3644 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3645 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3646 END IF;
3647
3648 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3649 FND_MSG_PUB.initialize;
3650 END IF;
3651
3652 IF (p_structure_version_id IS NOT NULL) THEN
3653 UPDATE PA_PROJ_ELEMENT_VERSIONS
3654 SET FINANCIAL_TASK_FLAG = 'N'
3655 WHERE PROJECT_ID = p_project_id
3656 AND PARENT_STRUCTURE_VERSION_ID = p_structure_version_id;
3657 END IF;
3658
3659 IF (p_task_version_id is NOT NULL) THEN
3660 UPDATE PA_PROJ_ELEMENT_VERSIONS
3661 SET FINANCIAL_TASK_FLAG='N'
3662 WHERE PROJECT_ID = p_project_id
3663 AND ELEMENT_VERSION_ID = p_task_version_id;
3664 END IF;
3665
3666 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3667 x_msg_count := FND_MSG_PUB.count_msg;
3668 IF x_msg_count = 1 then
3669 pa_interface_utils_pub.get_messages
3670 (p_encoded => FND_API.G_TRUE,
3671 p_msg_index => 1,
3672 -- p_msg_count => l_msg_count,
3673 -- p_msg_data => l_msg_data,
3674 p_data => l_data,
3675 p_msg_index_out => l_msg_index_out);
3676 x_msg_data := l_data;
3677 END IF;
3678 raise FND_API.G_EXC_ERROR;
3679 END IF;
3680
3681 x_return_status := FND_API.G_RET_STS_SUCCESS;
3682
3683 IF (p_commit = FND_API.G_TRUE) THEN
3684 COMMIT;
3685 END IF;
3686
3687 IF (p_debug_mode = 'Y') THEN
3688 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.CLEAR_FINANCIAL_FLAG END');
3689 END IF;
3690
3691 EXCEPTION
3692 when FND_API.G_EXC_ERROR then
3693 if p_commit = FND_API.G_TRUE then
3694 rollback to CLEAR_FINANCIAL_FLAG;
3695 end if;
3696 x_return_status := FND_API.G_RET_STS_ERROR;
3697 when FND_API.G_EXC_UNEXPECTED_ERROR then
3698 if p_commit = FND_API.G_TRUE then
3699 rollback to CLEAR_FINANCIAL_FLAG;
3700 end if;
3701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3702 -- 4537865
3703 x_msg_count := 1 ;
3704 x_msg_data := SUBSTRB(SQLERRM,1,240);
3705 -- 4537865
3706 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3707 p_procedure_name => 'CLEAR_FINANCIAL_FLAG',
3708 p_error_text => x_msg_data); -- 4537865
3709 when OTHERS then
3710 if p_commit = FND_API.G_TRUE then
3711 rollback to CLEAR_FINANCIAL_FLAG;
3712 end if;
3713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3714 -- 4537865
3715 x_msg_count := 1 ;
3716 x_msg_data := SUBSTRB(SQLERRM,1,240);
3717 -- 4537865
3718 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3719 p_procedure_name => 'CLEAR_FINANCIAL_FLAG',
3720 p_error_text => x_msg_data); -- 4537865
3721 raise;
3722
3723 END CLEAR_FINANCIAL_FLAG;
3724 --
3725 -- API name : Update_Sch_Dirty_Flag
3726 -- Type : Public Procedure
3727 -- Pre-reqs : None
3728 -- Return Value : N/A
3729 -- Parameters
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 := FND_API.G_VALID_LEVEL_FULL
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
3741 -- x_return_status OUT VARCHAR2
3742 -- x_msg_count OUT NUMBER
3743 -- x_msg_data OUT VARCHAR2
3744 --
3745 -- History
3746 -- 23-MAR-04 Srikanth Mukka - Created
3747 --
3748 PROCEDURE Update_Sch_Dirty_Flag
3749 (
3750 p_api_version IN NUMBER := 1.0
3751 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3752 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3753 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3754 ,p_validation_level IN NUMBER := 100
3755 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3756 ,p_debug_mode IN VARCHAR2 := 'N'
3757 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3758 ,p_project_id IN NUMBER
3759 ,p_structure_version_id IN NUMBER
3760 ,p_dirty_flag IN VARCHAR2 := 'N'
3761 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3762 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3763 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3764 )
3765 IS
3766 --
3767 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_SCH_DIRTY_FLAG';
3768 l_api_version CONSTANT NUMBER := 1.0;
3769 l_data VARCHAR2(250);
3770 l_msg_index_out NUMBER;
3771 --
3772 BEGIN
3773 pa_debug.init_err_stack ('PA_PROJECT_STRUCTURE_PUB1.update_sch_dirty_flag');
3774 --
3775 IF (p_debug_mode = 'Y') THEN
3776 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.update_sch_dirty_flag begin');
3777 END IF;
3778 --
3779 IF (p_commit = FND_API.G_TRUE) THEN
3780 savepoint update_sch_dirty_flag_pub;
3781 END IF;
3782 --
3783 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3785 END IF;
3786 --
3787 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3788 FND_MSG_PUB.initialize;
3789 END IF;
3790 --
3791 PA_PROJECT_STRUCTURE_PVT1.Update_Sch_Dirty_Flag(
3792 p_project_id =>p_project_id
3793 ,p_structure_version_id =>p_structure_version_id
3794 ,p_dirty_flag =>p_dirty_flag
3795 ,x_return_status =>x_return_status
3796 ,x_msg_count =>x_msg_count
3797 ,x_msg_data =>x_msg_data
3798 );
3799 --
3800 x_msg_count := FND_MSG_PUB.count_msg;
3801 IF (x_msg_count > 0) THEN
3802 IF x_msg_count = 1 THEN
3803 pa_interface_utils_pub.get_messages
3804 (p_encoded => FND_API.G_TRUE,
3805 p_msg_index => 1,
3806 p_data => l_data,
3807 p_msg_index_out => l_msg_index_out);
3808 x_msg_data := l_data;
3809 END IF;
3810 RAISE FND_API.G_EXC_ERROR;
3811 END IF;
3812 --
3813 x_return_status := FND_API.G_RET_STS_SUCCESS;
3814 --
3815 IF (p_commit = FND_API.G_TRUE) THEN
3816 COMMIT;
3817 END IF;
3818 --
3819 IF (p_debug_mode = 'Y') THEN
3820 pa_debug.debug('PA_PROJECT_STRUCTURE_PUB1.UPDATE_SCH_DIRTY_FLAG end');
3821 END IF;
3822 --
3823 EXCEPTION
3824 WHEN FND_API.G_EXC_ERROR THEN
3825 IF p_commit = FND_API.G_TRUE THEN
3826 ROLLBACK TO update_sch_dirty_flag_pub;
3827 END IF;
3828 x_return_status := FND_API.G_RET_STS_ERROR;
3829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3830 IF p_commit = FND_API.G_TRUE THEN
3831 ROLLBACK TO update_sch_dirty_flag_pub;
3832 END IF;
3833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3834 -- 4537865
3835 x_msg_count := 1 ;
3836 x_msg_data := SUBSTRB(SQLERRM,1,240);
3837 -- 4537865
3838 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3839 p_procedure_name => 'update_sch_dirty_flag',
3840 p_error_text => x_msg_data); -- 4537865
3841 WHEN OTHERS THEN
3842 IF p_commit = FND_API.G_TRUE THEN
3843 ROLLBACK TO update_sch_dirty_flag_pub;
3844 END IF;
3845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3846 -- 4537865
3847 x_msg_count := 1 ;
3848 x_msg_data := SUBSTRB(SQLERRM,1,240);
3849 -- 4537865
3850 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3851 p_procedure_name => 'update_sch_dirty_flag',
3852 p_error_text => x_msg_data); -- 4537865
3853 RAISE;
3854 END update_sch_dirty_flag;
3855
3856 --
3857 --
3858 -- API name : DELETE_PUBLISHED_STRUCTURE_VERSION
3859 -- Tracking Bug : 4925192
3860 -- Type : Public Procedure
3861 -- Pre-reqs : None
3862 -- Return Value : N/A
3863 -- Parameters
3864 -- p_api_version IN NUMBER := 1.0
3865 -- ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3866 -- ,p_structure_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
3867 -- ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
3868 -- ,x_return_status OUT NOCOPY VARCHAR2
3869 -- ,x_msg_count OUT NOCOPY NUMBER
3870 -- ,x_msg_data OUT NOCOPY VARCHAR2
3871 --
3872 -- History
3873 --
3874 -- 17-NOV-06 Ram Namburi -Created
3875 --
3876 -- Purpose:
3877 --
3878 -- This API will delete a published structure version
3879 -- 1. It calls the delete validation API to see if deletion is okay.
3880 -- 2. Then it calls the Progress API to roll up the progress to the next higher
3881 -- later versions
3882 -- 3. Then it calls the actual delete API.
3883 --
3884 /*
3885 -- ######################################################################################
3886 SAMPLE CODE FOR DELETE MULTIPLE VERSIONS:
3887
3888
3889 FOR i in p_structure_version_id_tbl.FIRST..p_structure_version_id_tbl.LAST LOOP -- Call the validation API
3890 -- If Y is returned then proceed with the
3891 -- progress rollup and
3892 -- actual deletion.
3893 -- l_structure_version_rec := p_structure_version_in_tbl(i);
3894 l_val_return_status := PA_PROJECT_STRUCTURE_UTILS.check_del_pub_struc_ver_ok(p_structure_version_id_tbl(i));
3895
3896 IF (l_val_return_status = 'Y') then
3897
3898 if (l_validation_failed <> 'Y'))THEN
3899
3900 -- Call Progress rollup API
3901
3902 BEGIN
3903
3904 pa_progress_pvt.UPD_PROG_RECS_STR_DELETE(
3905 p_project_id => p_project_id,
3906 p_str_ver_id_to_del => p_structure_version_id_tbl(i),
3907 x_return_status => l_return_status,
3908 x_msg_count => l_msg_count,
3909 x_msg_data => l_msg_data);
3910 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3911 NULL;
3912 END IF;
3913 EXCEPTION
3914 WHEN OTHERS THEN
3915 NULL;
3916 END;
3917
3918
3919 BEGIN
3920 -- Call the actual delete API
3921 PA_PROJECT_STRUCTURE_PUB1.delete_working_struc_ver(
3922 p_api_version => 1.0,
3923 p_structure_version_id => p_structure_version_id_tbl(i),
3924 p_record_version_number => p_record_version_number_tbl(i),
3925 x_return_status => l_return_status,
3926 x_msg_count => l_msg_count,
3927 x_msg_data => l_msg_data);
3928 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3929 NULL;
3930 END IF;
3931 EXCEPTION
3932 WHEN OTHERS THEN
3933 l_validation_failed := 'Y'
3934
3935 -- < store: message data in local tbl variable.
3936 -- store: ver name of p_structure_version_id_tbl(i) in local tbl.
3937 -- store: ver number of p_structure_version_id_tbl(i) in local tbl > --
3938 NULL;
3939 END;
3940
3941 end if; -- if (l_validation_failed <> 'Y'))THEN
3942
3943 ELSE
3944 l_validation_failed := 'Y';
3945
3946 -- < read message from stack into local tbl variable.
3947 -- store: ver name of p_structure_version_id_tbl(i) in local tbl.
3948 -- store: ver number of p_structure_version_id_tbl(i) in local tbl > --
3949 END IF;
3950 END LOOP;
3951 IF l_validation_failed = 'Y' THEN
3952 -- Return Status will be error so the caller
3953 -- can read from the error stack
3954 x_return_status := FND_API.G_RET_STS_ERROR;
3955
3956 -- clear FND stack of all previously pop messages.
3957
3958 -- populate generic error message
3959 -- < loop thorugh the local tbl variable with messages.
3960 -- pop message with ver name and ver number tokens using the new 'TOKEN : MESG'
3961 -- end loop>
3962
3963 END IF;
3964
3965 EXCEPTION
3966 WHEN FND_API.G_EXC_ERROR THEN
3967
3968 IF p_commit = FND_API.G_TRUE THEN
3969
3970 ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
3971
3972 END IF;
3973 x_return_status := FND_API.G_RET_STS_ERROR;
3974
3975 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3976 IF p_commit = FND_API.G_TRUE THEN
3977 ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
3978 END IF;
3979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3980 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3981 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
3982 p_error_text => SUBSTRB(SQLERRM,1,240));
3983 WHEN OTHERS THEN
3984 IF p_commit = FND_API.G_TRUE THEN
3985
3986 ROLLBACK TO DELETE_PUBLISHED_STRUCTURE_VER;
3987
3988 END IF;
3989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3990 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
3991 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
3992 p_error_text => SUBSTRB(SQLERRM,1,240));
3993 RAISE;
3994
3995 END DELETE_PUBLISHED_STRUCTURE_VER;
3996
3997 -- ######################################################################################
3998
3999 */
4000
4001
4002 procedure DELETE_PUBLISHED_STRUCTURE_VER
4003 (
4004 p_api_version IN NUMBER := 1.0
4005 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4006 ,p_project_id IN NUMBER
4007 ,p_structure_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE
4008 ,p_record_version_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE
4009 ,x_return_status OUT NOCOPY VARCHAR2
4010 ,x_msg_count OUT NOCOPY NUMBER
4011 ,x_msg_data OUT NOCOPY VARCHAR2
4012 ) IS
4013
4014 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PUBLISHED_STRUCTURE_VER';
4015 l_validation_failed VARCHAR2(1) := 'N';
4016 l_val_return_status VARCHAR2(1);
4017
4018 l_return_status VARCHAR2(1);
4019 l_msg_data VARCHAR2(2000);
4020 l_msg_count NUMBER;
4021 -- i NUMBER;
4022 -- l_structure_version_rec structure_version_in_rec_type;
4023
4024 BEGIN
4025
4026 -- SAVEPOINT DELETE_PUBLISHED_STRUCTURE_VER;
4027
4028 --l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4029
4030 -- Standard call to check for call compatibility.
4031
4032 IF NOT FND_API.Compatible_API_Call ( 1.0 ,
4033 p_api_version ,
4034 l_api_name ,
4035 G_PKG_NAME ) THEN
4036
4037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4038 END IF;
4039
4040 -- Initialize the message table Unconditionally.
4041
4042 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
4043
4044 FND_MSG_PUB.initialize;
4045 END IF;
4046
4047 -- Set API return status to success
4048 x_return_status := FND_API.G_RET_STS_SUCCESS;
4049
4050 -- Actual Processing starts here.
4051 -- Start looping through the passed in values.
4052
4053 FOR i in p_structure_version_id_tbl.FIRST..p_structure_version_id_tbl.LAST LOOP -- Call the validation API
4054
4055 -- If Y is returned then proceed with the
4056 -- progress rollup and
4057 -- actual deletion.
4058 l_val_return_status := PA_PROJECT_STRUCTURE_UTILS.check_del_pub_struc_ver_ok(p_structure_version_id_tbl(i),p_project_id);
4059 IF l_val_return_status = 'Y' THEN
4060 -- Call Progress rollup API
4061
4062 pa_progress_pvt.UPD_PROG_RECS_STR_DELETE(
4063 p_project_id => p_project_id,
4064 p_str_ver_id_to_del => p_structure_version_id_tbl(i),
4065 x_return_status => l_return_status,
4066 x_msg_count => l_msg_count,
4067 x_msg_data => l_msg_data);
4068 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4069 l_msg_count := FND_MSG_PUB.count_msg;
4070 x_msg_data := l_msg_data;
4071 RAISE FND_API.G_EXC_ERROR;
4072 END IF;
4073
4074 -- Call the actual delete API
4075 PA_PROJECT_STRUCTURE_PUB1.delete_working_struc_ver(
4076 p_api_version => 1.0,
4077 p_structure_version_id => p_structure_version_id_tbl(i),
4078 p_record_version_number => p_record_version_number_tbl(i),
4079 x_return_status => l_return_status,
4080 x_msg_count => l_msg_count,
4081 x_msg_data => l_msg_data);
4082 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4083 l_msg_count := FND_MSG_PUB.count_msg;
4084 x_msg_data := l_msg_data;
4085 RAISE FND_API.G_EXC_ERROR;
4086 END IF;
4087
4088 ELSE
4089 x_return_status := FND_API.G_RET_STS_ERROR;
4090 RAISE FND_API.G_EXC_ERROR;
4091 END IF;
4092 END LOOP;
4093
4094 EXCEPTION
4095 WHEN FND_API.G_EXC_ERROR THEN
4096
4097 x_return_status := FND_API.G_RET_STS_ERROR;
4098
4099 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4100
4101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4102 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4103 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
4104 p_error_text => SUBSTRB(SQLERRM,1,240));
4105
4106 WHEN OTHERS THEN
4107
4108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4109 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4110 p_procedure_name => 'DELETE_PUBLISHED_STRUCTURE_VER',
4111 p_error_text => SUBSTRB(SQLERRM,1,240));
4112
4113 RAISE;
4114
4115 END DELETE_PUBLISHED_STRUCTURE_VER;
4116
4117
4118 -- History
4119 -- 03-May-06 Ram Namburi - Created
4120 -- Purpose:
4121 -- This is used to enable the program on a project. In forms we are not allowing the user to create a link
4122 -- all the times, and if the program is not enabled then we enable that on the fly so the link creation is
4123 -- possible. This is needed otherwise users need to go to SS page to enable program on the project and come
4124 -- back to forms to create links. In order to remove this dependency we are now calling this API from forms
4125 -- as we couldnt directly call the update_structures_setup_attr.
4126 -- This wrapper will call the update_structures_setup_attr with other parameters.
4127 --
4128 PROCEDURE enable_program_flag(
4129 p_project_id IN NUMBER
4130 ,x_return_status OUT NOCOPY VARCHAR2
4131 ,x_msg_count OUT NOCOPY NUMBER
4132 ,x_msg_data OUT NOCOPY VARCHAR2
4133 ) IS
4134
4135 l_data VARCHAR2(250);
4136 l_msg_index_out NUMBER;
4137
4138 l_wp_enabled VARCHAR2(1) := NULL;
4139 l_fin_enabled VARCHAR2(1) := NULL;
4140 l_delv_enabled VARCHAR2(1) := NULL;
4141 l_share_code VARCHAR2(30) := NULL;
4142
4143 BEGIN
4144
4145 l_wp_enabled := PA_PROJECT_STRUCTURE_UTILS.Check_workplan_enabled(p_project_id);
4146 l_fin_enabled := PA_PROJECT_STRUCTURE_UTILS.Check_financial_enabled(p_project_id);
4147 l_delv_enabled := PA_PROJECT_STRUCTURE_UTILS.Check_deliverable_enabled(p_project_id);
4148 l_share_code := PA_PROJECT_STRUCTURE_UTILS.Get_Structure_sharing_code(p_project_id);
4149
4150 PA_PROJECT_STRUCTURE_PUB1.UPDATE_STRUCTURES_SETUP_ATTR(
4151 p_api_version => 1.0
4152 ,p_init_msg_list => 'T'
4153 ,p_commit => 'F'
4154 --,p_validate_only => 'T'
4155 --,p_validation_level => p_validation_level
4156 --,p_calling_module => p_calling_module
4157 ,p_debug_mode => 'N'
4158 --,p_max_msg_count => p_max_msg_count
4159 ,p_project_id => p_project_id
4160 ,p_workplan_enabled_flag => l_wp_enabled
4161 ,p_financial_enabled_flag => l_fin_enabled
4162 ,p_deliverables_enabled_flag => l_delv_enabled
4163 ,p_sharing_option_code => l_share_code
4164 ,p_sys_program_flag => 'Y'
4165 ,p_allow_multi_prog_rollup => 'N'
4166 ,x_return_status => x_return_status
4167 ,x_msg_count => x_msg_count
4168 ,x_msg_data => x_msg_data
4169 );
4170
4171 x_msg_count := FND_MSG_PUB.count_msg;
4172
4173 IF (x_msg_count > 0) THEN
4174
4175 IF x_msg_count = 1 THEN
4176
4177 pa_interface_utils_pub.get_messages
4178 (p_encoded => FND_API.G_TRUE,
4179 p_msg_index => 1,
4180 p_data => l_data,
4181 p_msg_index_out => l_msg_index_out);
4182 x_msg_data := l_data;
4183 END IF;
4184
4185 RAISE FND_API.G_EXC_ERROR;
4186 END IF;
4187
4188 x_return_status := FND_API.G_RET_STS_SUCCESS;
4189
4190
4191 EXCEPTION
4192
4193 WHEN FND_API.G_EXC_ERROR THEN
4194
4195 x_return_status := FND_API.G_RET_STS_ERROR;
4196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4197
4198 x_msg_count := 1 ;
4199 x_msg_data := SUBSTRB(SQLERRM,1,240);
4200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4201 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4202 p_procedure_name => 'enable_program_flag',
4203 p_error_text => x_msg_data);
4204 WHEN OTHERS THEN
4205
4206 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4207 x_msg_count := 1 ;
4208 x_msg_data := SUBSTRB(SQLERRM,1,240);
4209 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_PUB1',
4210 p_procedure_name => 'enable_program_flag',
4211 p_error_text => x_msg_data);
4212 RAISE;
4213
4214 END enable_program_flag;
4215
4216 /*
4217 * This API will validate project to check whether we can enable CBS or not.
4218 * Project_Id : Takes Project Id as input
4219 * x_valid_flag : Return Y when valid and resturns N when invalid
4220 * x_error_msg_code : Error code when x_valid_flag is N ,invalid.
4221 */
4222
4223
4224 PROCEDURE VALIDATE_PROJECT_FOR_CBS(
4225 p_project_id IN NUMBER,
4226 p_cbs_flag IN VARCHAR2,
4227 x_valid_flag OUT NOCOPY VARCHAR2,
4228 x_error_msg_code OUT NOCOPY VARCHAR2 )
4229 IS
4230
4231 /* Cursor to check baseline funding with out budget option bug#16772073*/
4232 CURSOR C_BL_FUND_BUD_EXISTS IS
4233 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4234 (SELECT NULL
4235 FROM PA_PROJECTS_ALL
4236 WHERE PROJECT_ID=p_project_id
4237 AND baseline_funding_flag = 'Y');
4238
4239 /* Cursor to check resource assignments */
4240 CURSOR C_RES_ASSIGNMENT_EXISTS IS
4241 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4242 (SELECT NULL
4243 FROM PA_RESOURCE_ASSIGNMENTS
4244 WHERE PROJECT_ID=p_project_id);
4245
4246 /* Cursor to check Expenditure items */
4247 CURSOR C_CHECK_EXPENDITURE_ITEM IS
4248 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4249 (SELECT NULL
4250 FROM PA_EXPENDITURE_ITEMS_ALL
4251 WHERE PROJECT_ID=p_project_id);
4252
4253 /* This Cursor checks if the project is enabled for program link */
4254 CURSOR C_GET_LINK_PROGRAM_FLAG IS
4255 SELECT SYS_PROGRAM_FLAG
4256 FROM PA_PROJECTS_ALL
4257 WHERE PROJECT_ID=p_project_id;
4258
4259 /* This Cursor checks if the project is form based budgets */
4260 CURSOR C_CHECK_FORM_BASED_BUDGET IS
4261 SELECT BUDGET_TYPE_CODE
4262 FROM PA_BUDGET_VERSIONS
4263 WHERE PROJECT_ID=p_project_id;
4264
4265 /* This Cursor checks if the project tax task id */
4266 CURSOR C_CHECK_TAX_TASK_ID_EXISTS IS
4267 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4268 (SELECT NULL
4269 FROM PA_PROJECTS_ALL
4270 WHERE PROJECT_ID=p_project_id
4271 and CC_TAX_TASK_ID is not null);
4272
4273 /* This Cursor checks if the project has purchase order distribution */
4274 CURSOR C_CHECK_PO_DIST_EXISTS IS
4275 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4276 (SELECT NULL
4277 FROM PO_DISTRIBUTIONS_ALL
4278 WHERE PROJECT_ID=p_project_id);
4279
4280 /* This Cursor checks if the project has purchase order requisition */
4281 CURSOR C_CHECK_PO_REQ_EXISTS IS
4282 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4283 (SELECT NULL
4284 FROM PO_REQ_DISTRIBUTIONS_ALL
4285 WHERE PROJECT_ID=p_project_id);
4286
4287 /* This Cursor checks if the project has supplier invoices */
4288 CURSOR C_CHECK_AP_INVOICE_EXISTS IS
4289 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4290 (SELECT NULL
4291 FROM AP_INVOICES_ALL
4292 WHERE PROJECT_ID=p_project_id);
4293
4294 /* This Cursor checks if the project has supplier invoices distribution */
4295 CURSOR C_CHECK_AP_INVOICE_DIST_EXISTS IS
4296 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4297 (SELECT NULL
4298 FROM AP_INVOICE_DISTRIBUTIONS_ALL
4299 WHERE PROJECT_ID=p_project_id);
4300
4301 /* This Cursor checks if the project has commitment transaction */
4302 CURSOR C_CHECK_COMMIT_TXN_EXISTS IS
4303 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4304 (SELECT NULL
4305 FROM PA_COMMITMENT_TXNS
4306 WHERE PROJECT_ID=p_project_id);
4307
4308 /* This Cursor checks if the project has iExpense records */
4309 CURSOR C_CHECK_iExpense_Rec_EXISTS IS
4310 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4311 (SELECT NULL
4312 FROM AP_EXP_REPORT_DISTS_ALL
4313 WHERE PROJECT_ID=p_project_id);
4314
4315 /* This Cursor checks if the project has Inventory Transaction records */
4316 CURSOR C_CHECK_INV_TXN_EXISTS IS
4317 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4318 (SELECT NULL
4319 FROM MTL_MATERIAL_TRANSACTIONS
4320 WHERE PROJECT_ID=p_project_id);
4321
4322 /* This Cursor checks if the project has inter project billing */
4323 CURSOR C_CHECK_INTER_PROJ_BILL_EXISTS IS
4324 SELECT BILL_ANOTHER_PROJECT_FLAG
4325 FROM PA_PROJECT_CUSTOMERS
4326 WHERE PROJECT_ID=p_project_id;
4327
4328 /* This Cursor checks if the project is used as Reciever Project for Inter project billing */
4329 CURSOR C_CHECK_PROJ_IS_RECIEVER IS
4330 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4331 (Select Null from PA_PROJECT_CUSTOMERS
4332 where RECEIVER_TASK_ID in
4333 (Select Task_id From PA_TASKS Where project_id=p_project_id));
4334
4335 /* This Cursor checks if any tasks has Reciever Project for Inter project billing */
4336 CURSOR C_CHECK_TASK_IS_RECIEVER IS
4337 SELECT 1 FROM SYS.DUAL WHERE EXISTS
4338 (Select Null from PA_TASKS where PROJECT_ID=p_project_id
4339 and RECEIVE_PROJECT_INVOICE_FLAG='Y');
4340
4341 /* This Cursor checks if project has been defined from a project type which has the Burden Accounted on a different Project-Task */
4342 CURSOR C_CHECK_PROJ_BURDEN_ACC IS
4343 select NVL(BURDEN_ACCOUNT_FLAG,'N') from PA_PROJECT_TYPES_ALL where
4344 Project_Type = (Select PROJECT_TYPE from PA_PROJECTS_ALL where project_id=p_project_id);
4345
4346
4347
4348 L_DUMMY NUMBER(1);
4349 l_status_code NUMBER(1);
4350 l_return_value VARCHAR2(25);
4351 err_msg_post varchar2(2);
4352
4353 x_used_in_OTL BOOLEAN;
4354
4355 BEGIN
4356
4357 x_valid_flag :='N'; -- Initially make the flag as N (invalid) , once all validations are passed then set it to Y(valid)
4358 pa_debug.write('Inside Procedure VALIDATE_CBS',3);
4359
4360 If p_cbs_flag = 'Y' THEN
4361 err_msg_post :='_E';
4362 else
4363 err_msg_post :='_D';
4364 end if;
4365
4366 --------------------------------------------------------------------------------------------------------------------------
4367 /* baseline funding with out budget option should not be enabled for checking if the
4368 project is CBS enabled bug#16772073*/
4369 IF p_cbs_flag = 'Y' THEN
4370 pa_debug.write('Check if there exists baseline funding with out budget:'||p_project_id,3);
4371 OPEN C_BL_FUND_BUD_EXISTS;
4372 FETCH C_BL_FUND_BUD_EXISTS into L_DUMMY;
4373 CLOSE C_BL_FUND_BUD_EXISTS;
4374 IF L_DUMMY = 1 THEN
4375 --Throw error message here,resource assignment you cannot enable CBS
4376 x_error_msg_code :='PA_CBS_BL_BUD_ENABLED';
4377 return;
4378 END IF;
4379 END IF;
4380 ----------------------------------------------------------------------------------------------------------------------------
4381
4382 --------------------------------------------------------------------------------------------------------------------------
4383 /* Project should not have any resource assignments to enable for CBS ,
4384 since Cost Code is mandatory for resource assignments for CBS */
4385 pa_debug.write('Check if there exists Resource Assignments for project:'||p_project_id,3);
4386 OPEN C_RES_ASSIGNMENT_EXISTS;
4387 FETCH C_RES_ASSIGNMENT_EXISTS into L_DUMMY;
4388 CLOSE C_RES_ASSIGNMENT_EXISTS;
4389 IF L_DUMMY = 1 THEN
4390 --Throw error message here,resource assignment you cannot enable CBS
4391 x_error_msg_code :='PA_CBS_RES_ASSGN_EXISTS_E';
4392 return;
4393 END IF;
4394 ----------------------------------------------------------------------------------------------------------------------------
4395 /* Project should not have any program links for CBS , as it is not supported */
4396
4397 OPEN C_GET_LINK_PROGRAM_FLAG;
4398 FETCH C_GET_LINK_PROGRAM_FLAG into l_return_value;
4399 CLOSE C_GET_LINK_PROGRAM_FLAG;
4400 IF l_return_value = 'Y' THEN
4401 x_error_msg_code :='PA_CBS_PRGM_LINK_EXISTS_E';
4402 return;
4403 END IF;
4404
4405 ----------------------------------------------------------------------------------------------------------------------------
4406 /* Project should not have any form based budgets created */
4407 l_return_value := NULL ; --16743723
4408 OPEN C_CHECK_FORM_BASED_BUDGET;
4409 FETCH C_CHECK_FORM_BASED_BUDGET into l_return_value;
4410 CLOSE C_CHECK_FORM_BASED_BUDGET;
4411 IF l_return_value is not NULL THEN
4412 x_error_msg_code :='PA_CBS_FORM_BUDGET_EXISTS_E';
4413 return;
4414 END IF;
4415 ----------------------------------------------------------------------------------------------------------------------------
4416 /* Validate Project for any events
4417 l_status_code :=pa_proj_tsk_utils.check_event_exists(P_PROJECT_ID, null);
4418 IF ( l_status_code = 1 ) THEN
4419 x_error_msg_code :='PA_CBS_EVENT_EXISTS'||err_msg_post;
4420 return;
4421 END IF;
4422
4423 /* Validate Project for any events
4424 l_status_code :=PA_PROJ_TSK_UTILS.check_funding_exists(P_PROJECT_ID, null);
4425 IF ( l_status_code = 1 ) THEN
4426 x_error_msg_code :='PA_CBS_FUND_EXISTS'||err_msg_post;
4427 return;
4428 END IF;
4429 */
4430 ----------------------------------------------------------------------------------------------------------------------------
4431 /* Validate Project for external system
4432 as we should use external systems will use alternate task id instead of task id for CBS*/
4433 l_status_code :=pjm_projtask_deletion.CheckUse_ProjectTask(P_PROJECT_ID, null);
4434 IF ( l_status_code = 1 ) THEN -- The Project is used in MFG
4435 x_error_msg_code :='PA_CBS_USED_IN_EXTRN_SYS_E';
4436 return;
4437 END IF;
4438 ---------------------------------------------------------------------------------------------------- ----------------------
4439 /*Validate project allocations */
4440 l_return_value:=pa_alloc_utils.Is_project_in_allocations(p_project_id);
4441 If l_return_value = 'Y' THEN
4442 x_error_msg_code :='PA_CBS_ALLOC_EXISTS'||err_msg_post;
4443 return;
4444 End If;
4445
4446 -----------------------------------------------------------------------------------------------------------------------------
4447
4448 pa_debug.write('Check if there exists any actuals for the project',3);
4449 OPEN C_CHECK_EXPENDITURE_ITEM;
4450 FETCH C_CHECK_EXPENDITURE_ITEM into L_DUMMY;
4451 CLOSE C_CHECK_EXPENDITURE_ITEM;
4452
4453 IF L_DUMMY = 1 THEN
4454 x_error_msg_code :='PA_CBS_EXPD_ITEM_EXISTS';
4455 return;
4456 END IF;
4457
4458
4459 -----------------------------------------------------------------------------------------------------------------------------
4460 pa_debug.write('Check if there exists any purchase order distribution',3);
4461 OPEN C_CHECK_PO_DIST_EXISTS;
4462 FETCH C_CHECK_PO_DIST_EXISTS into L_DUMMY;
4463 CLOSE C_CHECK_PO_DIST_EXISTS;
4464
4465 IF L_DUMMY = 1 THEN
4466 x_error_msg_code :='PA_CBS_PO_DIST_E';
4467 return;
4468 END IF;
4469 -----------------------------------------------------------------------------------------------------------------------------
4470 pa_debug.write('Check if there exists any purchase order requisitions',3);
4471 OPEN C_CHECK_PO_REQ_EXISTS;
4472 FETCH C_CHECK_PO_REQ_EXISTS into L_DUMMY;
4473 CLOSE C_CHECK_PO_REQ_EXISTS;
4474
4475 IF L_DUMMY = 1 THEN
4476 x_error_msg_code :='PA_CBS_PO_REQ_E';
4477 return;
4478 END IF;
4479 -----------------------------------------------------------------------------------------------------------------------------
4480 pa_debug.write('Check if there exists any supplier invoices',3);
4481 OPEN C_CHECK_AP_INVOICE_EXISTS;
4482 FETCH C_CHECK_AP_INVOICE_EXISTS into L_DUMMY;
4483 CLOSE C_CHECK_AP_INVOICE_EXISTS;
4484
4485 IF L_DUMMY = 1 THEN
4486 x_error_msg_code :='PA_CBS_SUP_INV_E';
4487 return;
4488 END IF;
4489
4490 -----------------------------------------------------------------------------------------------------------------------------
4491 pa_debug.write('Check if there exists supplier invoices distribution',3);
4492 OPEN C_CHECK_AP_INVOICE_DIST_EXISTS;
4493 FETCH C_CHECK_AP_INVOICE_DIST_EXISTS into L_DUMMY;
4494 CLOSE C_CHECK_AP_INVOICE_DIST_EXISTS;
4495
4496 IF L_DUMMY = 1 THEN
4497 x_error_msg_code :='PA_CBS_SUP_INV_DIST_E';
4498 return;
4499 END IF;
4500
4501 -----------------------------------------------------------------------------------------------------------------------------
4502 pa_debug.write('Check if there exists commitment transaction',3);
4503 OPEN C_CHECK_COMMIT_TXN_EXISTS;
4504 FETCH C_CHECK_COMMIT_TXN_EXISTS into L_DUMMY;
4505 CLOSE C_CHECK_COMMIT_TXN_EXISTS;
4506
4507 IF L_DUMMY = 1 THEN
4508 x_error_msg_code :='PA_CBS_COMMT_TXN_E';
4509 return;
4510 END IF;
4511 -----------------------------------------------------------------------------------------------------------------------------
4512 pa_debug.write('Check if there exists iExpense records',3);
4513 OPEN C_CHECK_iExpense_Rec_EXISTS;
4514 FETCH C_CHECK_iExpense_Rec_EXISTS into L_DUMMY;
4515 CLOSE C_CHECK_iExpense_Rec_EXISTS;
4516
4517 IF L_DUMMY = 1 THEN
4518 x_error_msg_code :='PA_CBS_IEXP_REC_E';
4519 return;
4520 END IF;
4521
4522 -----------------------------------------------------------------------------------------------------------------------------
4523 pa_debug.write('Check if there exists Inventory Transaction records',3);
4524 OPEN C_CHECK_INV_TXN_EXISTS;
4525 FETCH C_CHECK_INV_TXN_EXISTS into L_DUMMY;
4526 CLOSE C_CHECK_INV_TXN_EXISTS;
4527
4528 IF L_DUMMY = 1 THEN
4529 x_error_msg_code :='PA_CBS_INV_REC_E';
4530 return;
4531 END IF;
4532 -----------------------------------------------------------------------------------------------------------------------------
4533 pa_debug.write('Check if there exists Intercompany Tax Receiving Task ',3);
4534 OPEN C_CHECK_TAX_TASK_ID_EXISTS;
4535 FETCH C_CHECK_TAX_TASK_ID_EXISTS into L_DUMMY;
4536 CLOSE C_CHECK_TAX_TASK_ID_EXISTS;
4537
4538 IF L_DUMMY = 1 THEN
4539 x_error_msg_code :='PA_CBS_TAXT_TSK_ID_EXST_E';
4540 return;
4541 END IF;
4542 ----------------------------------------------------------------------------------------------------------------------------
4543 /* Project should not have any inter project billing */
4544
4545 OPEN C_CHECK_INTER_PROJ_BILL_EXISTS;
4546 FETCH C_CHECK_INTER_PROJ_BILL_EXISTS into l_return_value;
4547 CLOSE C_CHECK_INTER_PROJ_BILL_EXISTS;
4548 IF l_return_value='Y' THEN
4549 x_error_msg_code :='PA_CBS_INTR_PROJ_BILL_EXST_E';
4550 return;
4551 END IF;
4552
4553 OPEN C_CHECK_PROJ_IS_RECIEVER;
4554 FETCH C_CHECK_PROJ_IS_RECIEVER into L_DUMMY;
4555 CLOSE C_CHECK_PROJ_IS_RECIEVER;
4556 IF L_DUMMY = 1 THEN
4557 x_error_msg_code :='PA_CBS_PROJ_RECIEVER_E';
4558 return;
4559 END IF;
4560
4561 OPEN C_CHECK_TASK_IS_RECIEVER;
4562 FETCH C_CHECK_TASK_IS_RECIEVER into L_DUMMY;
4563 CLOSE C_CHECK_TASK_IS_RECIEVER;
4564 IF L_DUMMY = 1 THEN
4565 x_error_msg_code :='PA_CBS_PROJ_RCV_FLAG_E';
4566 return;
4567 END IF;
4568
4569
4570 ------------------------------------------------------------------------------------------------------------------------------
4571 /* Check if Project is used in OTL Expenditure Items */
4572
4573 x_used_in_OTL :=Hxc_Integration_Layer_V1_Grp.Chk_Mapping_Exists (
4574 P_Bld_Blk_Info_Type => 'PROJECTS'
4575 ,P_Field_Name => 'Project_Id'
4576 ,P_Field_Value => p_project_id
4577 ,P_status => 'WORKING'
4578 ,P_Scope => 'DETAIL');
4579 IF x_used_in_OTL THEN
4580 x_error_msg_code :='PA_CBS_OTL_EXP_ITEM_E';
4581 return;
4582 END IF;
4583
4584 ------------------------------------------------------------------------------------------------------------------------------
4585 /* GMS Project Validation */
4586
4587 l_return_value :=gms_pa_costing_pkg.grants_implemented;
4588
4589 IF l_return_value='Y' THEN
4590 x_error_msg_code :='PA_CBS_GMS_PROJ_E';
4591 return;
4592 END IF;
4593
4594 ------------------------------------------------------------------------------------------------------------------------------
4595 /* Check if project has Funds check */
4596
4597 l_return_value :=PA_FUNDS_CONTROL_UTILS.is_funds_check_enabled(p_project_id);
4598 IF l_return_value='Y' THEN
4599 x_error_msg_code :='PA_CBS_PRJ_FUNDS_E';
4600 return;
4601 END IF;
4602 ------------------------------------------------------------------------------------------------------------------------------
4603 /*Check if project has been defined from a project type which has the Burden Accounted on a different Project-Task */
4604 OPEN C_CHECK_PROJ_BURDEN_ACC;
4605 FETCH C_CHECK_PROJ_BURDEN_ACC INTO l_return_value;
4606 CLOSE C_CHECK_PROJ_BURDEN_ACC;
4607
4608 IF l_return_value='Y' THEN
4609 x_error_msg_code :='PA_CBS_BURDEN_E';
4610 return;
4611 END IF;
4612
4613
4614 pa_debug.write('All validations are done',3);
4615 x_valid_flag :='Y';
4616
4617 END VALIDATE_PROJECT_FOR_CBS;
4618 --
4619 --
4620 end PA_PROJECT_STRUCTURE_PUB1;