[Home] [Help]
PACKAGE BODY: APPS.PA_DELIVERABLE_PVT
Source
1 PACKAGE BODY PA_DELIVERABLE_PVT AS
2 /* $Header: PADLVPVB.pls 120.3 2006/01/27 04:39:32 vkadimes noship $ */
3
4 Invalid_Arg_Exc_Dlv EXCEPTION ;
5 g_module_name VARCHAR2(100) := 'PA_DELIVERABLE_PVT';
6 g_deliverable_based CONSTANT pa_lookups.lookup_code%TYPE := 'DELIVERABLE' ;
7
8 -- Procedure : Create_Deliverable
9 -- Type : PRIVATE
10 -- Purpose : Create_Deliverable Procedure will be called for creation of deliverable
11 -- Note : Call insert_row method of pa_proj_elements, pa_proj_element_versions,
12 -- : pa_proj_element_sch and pa_object_relationships packages
13 -- Assumptions : None
14
15 -- Parameters Type Required Description and Purpose
16 -- --------------------------- ------ -------- --------------------------------------------------------
17 -- p_api_version NUMBER N 1.0
18 -- p_init_msg_list VARCHAR2 N := FND_API.G_TRUE
19 -- p_commit VARCHAR2 N := FND_API.G_FALSE
20 -- p_validate_only VARCHAR2 N := FND_API.G_TRUE
21 -- p_validation_level NUMBER N := FND_API.G_VALID_LEVEL_FULL
22 -- p_calling_module VARCHAR2 N := 'SELF_SERVICE'
23 -- p_debug_mode VARCHAR2 N := 'N'
24 -- p_max_msg_count NUMBER N := NULL
25 -- p_record_version_number NUMBER N := 1
26 -- p_object_type VARCHAR2 N Object Type Default 'PA_DELIVERABLES'
27 -- p_project_id NUMBER Y Project Id
28 -- p_dlvr_number VARCHAR2 Y Deliverable Number
29 -- p_dlvr_name VARCHAR2 Y Deliverable Name
30 -- p_dlvr_description VARCHAR2 N Description
31 -- p_dlvr_owner_id NUMBER N Deliverable Owner Id
32 -- p_dlvr_owner_name VARCHAR2 N Delivearble Owner Name
33 -- p_carrying_out_org_id NUMBER N Project Carrying Out Organization Id
34 -- p_carrying_out_org_name VARCHAR2 N Project Carrying Out Organization Name
35 -- p_dlvr_version_id NUMBER N Deliverable Version Id
36 -- p_status_code VARCHAR2 N Delivearble Status
37 -- p_parent_structure_id NUMBER N Deliverable Parent Structure Id
38 -- p_parent_struct_ver_id NUMBER N Deliverable Parent Structure Version Id
39 -- p_dlvr_type_id NUMBER N Deliverable Type Id
40 -- p_dlvr_type_name VARCHAR2 N Deliverable Type Name
41 -- p_progress_weight NUMBER N Progress Weight
42 -- p_scheduled_finish_date DATE N Scheduled Finish Date
43 -- p_actual_finish_date DATE N Actual Finish Date
44 -- p_task_id NUMBER N task_id
45 -- p_task_version_id NUMBER N task_version_id
46 -- p_task_name VARCHAR2 N task_name
47 -- p_attribute_category VARCHAR2 N attribute_category
48 -- p_attribute1 VARCHAR2 N attribute1
49 -- p_attribute2 VARCHAR2 N attribute2
50 -- p_attribute3 VARCHAR2 N attribute3
51 -- p_attribute4 VARCHAR2 N attribute4
52 -- p_attribute5 VARCHAR2 N attribute5
53 -- p_attribute6 VARCHAR2 N attribute6
54 -- p_attribute7 VARCHAR2 N attribute7
55 -- p_attribute8 VARCHAR2 N attribute8
56 -- p_attribute9 VARCHAR2 N attribute9
57 -- p_attribute10 VARCHAR2 N attribute10
58 -- p_attribute11 VARCHAR2 N attribute11
59 -- p_attribute12 VARCHAR2 N attribute12
60 -- p_attribute13 VARCHAR2 N attribute13
61 -- p_attribute14 VARCHAR2 N attribute14
62 -- p_attribute15 VARCHAR2 N attribute15
63 -- p_dlvr_item_id NUMBER N proj_element_id
64 -- x_return_status VARCHAR2 N Return Status
65 -- x_msg_count NUMBER N Message Count
66 -- x_msg_data VARCHAR2 N Message Data
67
68 PROCEDURE Create_Deliverable
69 (
70 p_api_version IN NUMBER := 1.0
71 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
72 , p_commit IN VARCHAR2 := FND_API.G_FALSE
73 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
74 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
75 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
76 , p_debug_mode IN VARCHAR2 := 'N'
77 , p_max_msg_count IN NUMBER := NULL
78 , p_record_version_number IN NUMBER := 1
79 , p_object_type IN PA_PROJ_ELEMENTS.OBJECT_TYPE%TYPE := 'PA_DELIVERABLES'
80 , p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
81 , p_dlvr_number IN PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE
82 , p_dlvr_name IN PA_PROJ_ELEMENTS.NAME%TYPE
83 , p_dlvr_description IN PA_PROJ_ELEMENTS.DESCRIPTION%TYPE := NULL
84 , p_dlvr_owner_id IN PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE := NULL
85 , p_dlvr_owner_name IN VARCHAR2 := NULL
86 , p_carrying_out_org_id IN PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE := NULL
87 , p_carrying_out_org_name IN VARCHAR2 := NULL
88 , p_dlvr_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL
89 , p_status_code IN PA_PROJ_ELEMENTS.STATUS_CODE%TYPE := NULL
90 , p_parent_structure_id IN PA_PROJ_ELEMENTS.PARENT_STRUCTURE_ID%TYPE
91 , p_parent_struct_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
92 , p_dlvr_type_id IN PA_PROJ_ELEMENTS.TYPE_ID%TYPE := NULL
93 , p_dlvr_type_name IN VARCHAR2 := NULL
94 , p_dlvr_reference IN VARCHAR2 := NULL -- 3435905
95 , p_progress_weight IN PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL
96 , p_scheduled_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE := NULL
97 , p_actual_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.ACTUAL_FINISH_DATE%TYPE := NULL
98 , p_task_id IN NUMBER := NULL
99 , p_task_version_id IN NUMBER := NULL
100 , p_task_name IN VARCHAR2 := NULL
101 , p_attribute_category IN PA_PROJ_ELEMENTS.ATTRIBUTE_CATEGORY%TYPE := NULL
102 , p_attribute1 IN PA_PROJ_ELEMENTS.ATTRIBUTE1%TYPE := NULL
103 , p_attribute2 IN PA_PROJ_ELEMENTS.ATTRIBUTE2%TYPE := NULL
104 , p_attribute3 IN PA_PROJ_ELEMENTS.ATTRIBUTE3%TYPE := NULL
105 , p_attribute4 IN PA_PROJ_ELEMENTS.ATTRIBUTE4%TYPE := NULL
106 , p_attribute5 IN PA_PROJ_ELEMENTS.ATTRIBUTE5%TYPE := NULL
107 , p_attribute6 IN PA_PROJ_ELEMENTS.ATTRIBUTE6%TYPE := NULL
108 , p_attribute7 IN PA_PROJ_ELEMENTS.ATTRIBUTE7%TYPE := NULL
109 , p_attribute8 IN PA_PROJ_ELEMENTS.ATTRIBUTE8%TYPE := NULL
110 , p_attribute9 IN PA_PROJ_ELEMENTS.ATTRIBUTE9%TYPE := NULL
111 , p_attribute10 IN PA_PROJ_ELEMENTS.ATTRIBUTE10%TYPE := NULL
112 , p_attribute11 IN PA_PROJ_ELEMENTS.ATTRIBUTE11%TYPE := NULL
113 , p_attribute12 IN PA_PROJ_ELEMENTS.ATTRIBUTE12%TYPE := NULL
114 , p_attribute13 IN PA_PROJ_ELEMENTS.ATTRIBUTE13%TYPE := NULL
115 , p_attribute14 IN PA_PROJ_ELEMENTS.ATTRIBUTE14%TYPE := NULL
116 , p_attribute15 IN PA_PROJ_ELEMENTS.ATTRIBUTE15%TYPE := NULL
117 , p_dlvr_item_id OUT NOCOPY PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE --File.Sql.39 bug 4440895
118 , p_pm_source_code IN VARCHAR2 := NULL /* Bug no. 3651113 */
119 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
120 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
121 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
122 )
123 IS
124
125 l_msg_count NUMBER := 0;
126 l_element_id NUMBER := NULL;
127 l_data VARCHAR2(2000);
128 l_msg_data VARCHAR2(2000);
129 l_msg_index_out NUMBER;
130 l_debug_mode VARCHAR2(1);
131
132 l_debug_level2 CONSTANT NUMBER := 2;
133 l_debug_level3 CONSTANT NUMBER := 3;
134 l_debug_level4 CONSTANT NUMBER := 4;
135 l_debug_level5 CONSTANT NUMBER := 5;
136
137 X_ROW_ID VARCHAR2(18);
138 l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE := NULL;
139 l_dlvr_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL;
140 l_new_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE := NULL;
141 l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE := NULL;
142 l_progress_weight PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL; -- 3570283 added
143 l_prog_rollup_method VARCHAR2(30) := NULL;
144
145 l_dlvr_prg_flag VARCHAR2(1) := 'N'; -- 3570283 added
146 l_dlvr_act_flag VARCHAR2(1) := 'N'; -- 3570283 added
147 l_dlvr_dflt_status VARCHAR2(30) := NULL; -- 3570283 added
148
149 BEGIN
150
151 x_msg_count := 0;
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
154
155 IF l_debug_mode = 'Y' THEN
156 PA_DEBUG.set_curr_function( p_function => 'CREATE_DELIVERABLE',
157 p_debug_mode => l_debug_mode );
158 END IF;
159
160 IF (p_commit = FND_API.G_TRUE) THEN
161 savepoint CREATE_DLVR_PVT;
162 END IF;
163
164 -- Business Logic
165
166 -- if create deliverable is done from task detail page,
167 -- retrieve progress rollup method for task
168 -- if it is deliveable_based and progress is enabled, check progress_weight is not null
169
170 -- 3570283 added , to retrieve deliverable type information
171
172 PA_DELIVERABLE_UTILS.get_dlvr_type_info
173 (
174 p_dlvr_type_id => p_dlvr_type_id
175 ,x_dlvr_prg_enabled => l_dlvr_prg_flag
176 ,x_dlvr_action_enabled => l_dlvr_act_flag
177 ,x_dlvr_default_status_code => l_dlvr_dflt_status
178 );
179
180 IF l_debug_mode = 'Y' THEN
181 Pa_Debug.WRITE(g_module_name,' l_dlvr_prg_flag ' || l_dlvr_prg_flag, l_debug_level3);
182 END IF;
183
184 -- 3570283
185 -- added one if contion l_dlvr_prg_flag = 'Y'
186 -- if progress is enabled for deliverable type and task_id is not null, then check for
187 -- manadatory progress weight parameter
188
189 IF p_task_id IS NOT NULL AND l_dlvr_prg_flag = 'Y' THEN -- 3570283 added l_dlvr_prg_flag condition
190
191 l_prog_rollup_method := PA_DELIVERABLE_UTILS.get_progress_rollup_method
192 (
193 p_task_id => p_task_id
194 );
195
196 IF l_debug_mode = 'Y' THEN
197 Pa_Debug.WRITE(g_module_name,' l_prog_rollup_method ' || l_prog_rollup_method, l_debug_level3);
198 END IF;
199
200 IF l_prog_rollup_method = g_deliverable_based THEN
201 IF p_progress_weight IS NULL THEN
202 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
203 p_msg_name => 'PA_DLVR_PROG_WEIGHT_MISSING');
204 x_return_status := FND_API.G_RET_STS_ERROR;
205 END IF;
206 END IF;
207
208 END IF;
209
210 IF l_debug_mode = 'Y' THEN
211 Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENTS_PKG.Insert_Row Called ',
212 l_debug_level3);
213 END IF;
214
215 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218
219 -- 3570283 added
220 -- if deliverable type is changed from progress enabled type to disabled type
221 -- progress weight shuold be passed accordingly as user entered value or null
222
223 IF l_dlvr_prg_flag = 'Y' THEN
224 l_progress_weight := p_progress_weight;
225 ELSE
226 l_progress_weight := NULL;
227 END IF;
228
229 -- 3570283 added
230
231 -- call insert_row of pa_proj_elements package
232
233 PA_PROJ_ELEMENTS_PKG.Insert_Row
234 (
235 X_ROW_ID => X_ROW_ID
236 ,X_PROJ_ELEMENT_ID => l_proj_element_id
237 ,X_PROJECT_ID => p_project_id
238 ,X_OBJECT_TYPE => p_object_type
239 ,X_ELEMENT_NUMBER => p_dlvr_number
240 ,X_NAME => p_dlvr_name
241 ,X_DESCRIPTION => p_dlvr_description
242 ,X_STATUS_CODE => p_status_code
243 ,X_WF_STATUS_CODE => null
244 ,X_PM_PRODUCT_CODE => p_pm_source_code /* Bug no. 3651113 -- Passed p_pm_source_code instead of null*/
245 ,X_PM_TASK_REFERENCE => p_dlvr_reference
246 ,X_CLOSED_DATE => NULL
247 ,X_LOCATION_ID => NULL
248 ,X_MANAGER_PERSON_ID => p_dlvr_owner_id
249 ,X_CARRYING_OUT_ORGANIZATION_ID => p_carrying_out_org_id
250 ,X_TYPE_ID => p_dlvr_type_id
251 ,X_PRIORITY_CODE => NULL
252 ,X_INC_PROJ_PROGRESS_FLAG => NULL
253 ,X_REQUEST_ID => NULL
254 ,X_PROGRAM_APPLICATION_ID => NULL
255 ,X_PROGRAM_ID => NULL
256 ,X_PROGRAM_UPDATE_DATE => NULL
257 ,X_LINK_TASK_FLAG => NULL
258 ,X_ATTRIBUTE_CATEGORY => p_attribute_category
259 ,X_ATTRIBUTE1 => p_attribute1
260 ,X_ATTRIBUTE2 => p_attribute2
261 ,X_ATTRIBUTE3 => p_attribute3
262 ,X_ATTRIBUTE4 => p_attribute4
263 ,X_ATTRIBUTE5 => p_attribute5
264 ,X_ATTRIBUTE6 => p_attribute6
265 ,X_ATTRIBUTE7 => p_attribute7
266 ,X_ATTRIBUTE8 => p_attribute8
267 ,X_ATTRIBUTE9 => p_attribute9
268 ,X_ATTRIBUTE10 => p_attribute10
269 ,X_ATTRIBUTE11 => p_attribute11
270 ,X_ATTRIBUTE12 => p_attribute12
271 ,X_ATTRIBUTE13 => p_attribute13
272 ,X_ATTRIBUTE14 => p_attribute14
273 ,X_ATTRIBUTE15 => p_attribute15
274 ,X_TASK_WEIGHTING_DERIV_CODE => NULL
275 ,X_WORK_ITEM_CODE => NULL
276 ,X_UOM_CODE => NULL
277 ,X_WQ_ACTUAL_ENTRY_CODE => NULL
278 ,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
279 ,X_PARENT_STRUCTURE_ID => p_parent_structure_id
280 ,X_PHASE_CODE => NULL
281 ,X_PHASE_VERSION_ID => NULL
282 ,X_PROGRESS_WEIGHT => l_progress_weight -- 3570283 changed from p_progress_weight
283 -- ,X_PROG_ROLLUP_METHOD => NULL
284 ,X_FUNCTION_CODE => NULL
285 ,X_SOURCE_OBJECT_ID => p_project_id
286 ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
287 );
288
289
290 p_dlvr_item_id := l_proj_element_id;
291
292 IF l_debug_mode = 'Y' THEN
293 Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENTS_PKG.Insert_Row Element ['||p_dlvr_number||']['||p_dlvr_number||']',
294 l_debug_level3);
295 END IF;
296
297 IF l_debug_mode = 'Y' THEN
298 Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row Called ',
299 l_debug_level3);
300 END IF;
301
302 -- call insert_row of pa_proj_element_versions package
303
304 l_dlvr_version_id := p_dlvr_version_id;
305
306 PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row
307 (
308 X_ROW_ID => X_ROW_ID
309 ,X_ELEMENT_VERSION_ID => l_dlvr_version_id
310 ,X_PROJ_ELEMENT_ID => l_proj_element_id
311 ,X_OBJECT_TYPE => p_object_type
312 ,X_PROJECT_ID => p_project_id
313 ,X_PARENT_STRUCTURE_VERSION_ID => p_parent_struct_ver_id
314 ,X_DISPLAY_SEQUENCE => NULL
315 ,X_WBS_LEVEL => NULL
316 ,X_WBS_NUMBER => NULL
317 ,X_ATTRIBUTE_CATEGORY => p_attribute_category
318 ,X_ATTRIBUTE1 => p_attribute1
319 ,X_ATTRIBUTE2 => p_attribute2
320 ,X_ATTRIBUTE3 => p_attribute3
321 ,X_ATTRIBUTE4 => p_attribute4
322 ,X_ATTRIBUTE5 => p_attribute5
323 ,X_ATTRIBUTE6 => p_attribute6
324 ,X_ATTRIBUTE7 => p_attribute7
325 ,X_ATTRIBUTE8 => p_attribute8
326 ,X_ATTRIBUTE9 => p_attribute9
327 ,X_ATTRIBUTE10 => p_attribute10
328 ,X_ATTRIBUTE11 => p_attribute11
329 ,X_ATTRIBUTE12 => p_attribute12
330 ,X_ATTRIBUTE13 => p_attribute13
331 ,X_ATTRIBUTE14 => p_attribute14
332 ,X_ATTRIBUTE15 => p_attribute15
333 ,X_TASK_UNPUB_VER_STATUS_CODE => NULL
334 ,X_SOURCE_OBJECT_ID => p_project_id
335 ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
336 );
337
338 IF l_debug_mode = 'Y' THEN
339 Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row Element['||l_dlvr_version_id||']',
340 l_debug_level3);
341 END IF;
342
343 IF l_debug_mode = 'Y' THEN
344 Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_SCH_PKG.Insert_Row Called ',
345 l_debug_level3);
346 END IF;
347
348 -- call insert_row of pa_proj_element_sch package
349
350 PA_PROJ_ELEMENT_SCH_PKG.Insert_Row
351 (
352 X_ROW_ID => X_Row_Id
353 ,X_PEV_SCHEDULE_ID => l_new_pev_schedule_id
354 ,X_ELEMENT_VERSION_ID => l_dlvr_version_id
355 ,X_PROJECT_ID => p_project_id
356 ,X_PROJ_ELEMENT_ID => l_proj_element_id
357 ,X_SCHEDULED_START_DATE => NULL
358 ,X_SCHEDULED_FINISH_DATE => p_scheduled_finish_date
359 ,X_OBLIGATION_START_DATE => NULL
360 ,X_OBLIGATION_FINISH_DATE => NULL
361 ,X_ACTUAL_START_DATE => NULL
362 ,X_ACTUAL_FINISH_DATE => p_actual_finish_date
363 ,X_ESTIMATED_START_DATE => NULL
364 ,X_ESTIMATED_FINISH_DATE => NULL
365 ,X_DURATION => NULL
366 ,X_EARLY_START_DATE => NULL
367 ,X_EARLY_FINISH_DATE => NULL
368 ,X_LATE_START_DATE => NULL
369 ,X_LATE_FINISH_DATE => NULL
370 ,X_CALENDAR_ID => NULL
371 ,X_MILESTONE_FLAG => NULL
372 ,X_CRITICAL_FLAG => NULL
373 ,X_WQ_PLANNED_QUANTITY => NULL
374 ,X_PLANNED_EFFORT => NULL
375 ,X_ACTUAL_DURATION => NULL
376 ,X_ESTIMATED_DURATION => NULL
377 ,X_ATTRIBUTE_CATEGORY => p_attribute_category
378 ,X_ATTRIBUTE1 => p_attribute1
379 ,X_ATTRIBUTE2 => p_attribute2
380 ,X_ATTRIBUTE3 => p_attribute3
381 ,X_ATTRIBUTE4 => p_attribute4
382 ,X_ATTRIBUTE5 => p_attribute5
383 ,X_ATTRIBUTE6 => p_attribute6
384 ,X_ATTRIBUTE7 => p_attribute7
385 ,X_ATTRIBUTE8 => p_attribute8
386 ,X_ATTRIBUTE9 => p_attribute9
387 ,X_ATTRIBUTE10 => p_attribute10
388 ,X_ATTRIBUTE11 => p_attribute11
389 ,X_ATTRIBUTE12 => p_attribute12
390 ,X_ATTRIBUTE13 => p_attribute13
391 ,X_ATTRIBUTE14 => p_attribute14
392 ,X_ATTRIBUTE15 => p_attribute15
393 ,X_SOURCE_OBJECT_ID => p_project_id
394 ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
395 );
396
397 IF l_debug_mode = 'Y' THEN
398 Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_SCH_PKG.Insert_Row schedule ['||l_new_pev_schedule_id||']',
399 l_debug_level3);
400 END IF;
401
402 IF l_debug_mode = 'Y' THEN
403 Pa_Debug.WRITE(g_module_name,' PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Called Structure-To-Deliverable',
404 l_debug_level3);
405 END IF;
406
407 -- call insert_row of pa_object_relationships package
408 -- it creates relationship from structure_to_deliverable
409
410 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
411 (
412 p_user_id => FND_GLOBAL.USER_ID
413 ,p_object_type_from => 'PA_STRUCTURES'
414 ,p_object_id_from1 => p_parent_struct_ver_id
415 ,p_object_id_from2 => p_parent_structure_id
416 ,p_object_id_from3 => NULL
417 ,p_object_id_from4 => NULL
418 ,p_object_id_from5 => NULL
419 ,p_object_type_to => 'PA_DELIVERABLES'
420 ,p_object_id_to1 => p_dlvr_version_id
421 ,p_object_id_to2 => l_proj_element_id
422 ,p_object_id_to3 => NULL
423 ,p_object_id_to4 => NULL
424 ,p_object_id_to5 => NULL
425 ,p_relationship_type => 'S'
426 ,p_relationship_subtype => 'STRUCTURE_TO_DELIVERABLE'
427 ,p_lag_day => NULL
428 ,p_imported_lag => NULL
429 ,p_priority => NULL
430 ,p_pm_product_code => p_pm_source_code /* Bug no. 3651113 -- Passed p_pm_source_code instead of null*/
431 ,x_object_relationship_id => l_new_obj_rel_id
432 ,x_return_status => x_return_status
433 );
434
435 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
436 RAISE FND_API.G_EXC_ERROR;
437 END IF;
438
439 IF l_debug_mode = 'Y' THEN
440 Pa_Debug.WRITE(g_module_name,' Out of PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Structure-To-Deliverable str vers ['
441 ||p_parent_struct_ver_id||']str ['||p_parent_struct_ver_id||']' , l_debug_level3);
442 END IF;
443
444 IF (P_TASK_ID IS NOT NULL ) THEN
445
446 IF l_debug_mode = 'Y' THEN
447 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_PVT.CREATE_DLV_TASK_ASSOCIATION Called Task-To-Deliverable',
448 l_debug_level3);
449 END IF;
450
451 -- if task_id is not null , i.e. create_deliverable is called from task page
452 -- call insert_row of pa_object_relationships package
453 -- it creates relationship from task_to_deliverable
454 /*
455 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
456 (
457 p_user_id => FND_GLOBAL.USER_ID
458 ,p_object_type_from => 'PA_TASKS'
459 ,p_object_id_from1 => p_task_version_id
460 ,p_object_id_from2 => p_task_id
461 ,p_object_id_from3 => NULL
462 ,p_object_id_from4 => NULL
463 ,p_object_id_from5 => NULL
464 ,p_object_type_to => 'PA_DELIVERABLES'
465 ,p_object_id_to1 => p_dlvr_version_id
466 ,p_object_id_to2 => l_proj_element_id
467 ,p_object_id_to3 => NULL
468 ,p_object_id_to4 => NULL
469 ,p_object_id_to5 => NULL
470 ,p_relationship_type => 'A'
471 ,p_relationship_subtype => 'TASK_TO_DELIVERABLE'
472 ,p_lag_day => NULL
473 ,p_imported_lag => NULL
474 ,p_priority => NULL
475 ,p_pm_product_code => NULL
476 ,x_object_relationship_id => l_new_obj_rel_id
477 ,x_return_status => x_return_status
478 );
479
480 */
481 PA_DELIVERABLE_PVT.CREATE_DLV_TASK_ASSOCIATION
482 ( p_debug_mode => l_debug_mode
483 ,p_task_element_id => p_task_id
484 ,p_task_version_id => p_task_version_id
485 ,p_dlv_element_id => l_proj_element_id
486 ,p_dlv_version_id => p_dlvr_version_id
487 ,p_project_id => p_project_id
488 ,x_return_status => x_return_status
489 ,x_msg_count => x_msg_count
490 ,x_msg_data => x_msg_data
491 );
492
493
494 IF l_debug_mode = 'Y' THEN
495 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_PVT.CREATE_DLV_TASK_ASSOCIATION Task-To-Dlvr['||x_return_status||']',
496 l_debug_level3);
497 END IF;
498
499 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502
503 END IF;
504
505 IF l_debug_mode = 'Y' THEN
506 Pa_Debug.WRITE(g_module_name,' PA_ACTIONS_PUB.COPY_ACTIONS Called ',
507 l_debug_level3);
508 END IF;
509
510 IF (p_calling_module <> 'AMG') THEN
511 PA_ACTIONS_PUB.COPY_ACTIONS
512 (
513 p_init_msg_list => p_init_msg_list
514 ,p_commit => p_commit
515 ,p_debug_mode => l_debug_mode
516 ,p_source_object_id => p_dlvr_type_id
517 ,p_source_object_type => 'PA_DLVR_TYPES'
518 ,p_target_object_id => l_proj_element_id
519 ,p_target_object_type => 'PA_DELIVERABLES'
520 ,p_source_project_id => null
521 ,p_target_project_id => p_project_id
522 ,p_task_id => p_task_id
523 ,p_task_ver_id => p_task_version_id
524 ,p_carrying_out_organization_id => p_carrying_out_org_id
525 ,p_pm_source_reference => null
526 ,p_pm_source_code => null
527 ,p_calling_mode => 'CREATE' -- Added for bug 3911050
528 ,x_return_status => x_return_status
529 ,x_msg_count => x_msg_count
530 ,x_msg_data => x_msg_data
531 ) ;
532
533
534 IF l_debug_mode = 'Y' THEN
535 Pa_Debug.WRITE(g_module_name,' Out of PA_ACTIONS_PUB.COPY_ACTIONS ['||x_return_status||']', l_debug_level3);
536 END IF;
537
538 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541 END IF; --p_calling_module <> 'AMG'
542
543 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
544 pa_debug.reset_curr_function;
545 END IF ;
546
547 x_return_status := FND_API.G_RET_STS_SUCCESS;
548
549 EXCEPTION
550
551 WHEN FND_API.G_EXC_ERROR THEN
552 x_return_status := Fnd_Api.G_RET_STS_ERROR;
553 l_msg_count := Fnd_Msg_Pub.count_msg;
554
555 IF p_commit = FND_API.G_TRUE THEN
556 ROLLBACK TO CREATE_DLVR_PVT;
557 END IF;
558
559 IF l_msg_count = 1 AND x_msg_data IS NULL
560 THEN
561 Pa_Interface_Utils_Pub.get_messages
562 ( p_encoded => Fnd_Api.G_TRUE
563 , p_msg_index => 1
564 , p_msg_count => l_msg_count
565 , p_msg_data => l_msg_data
566 , p_data => l_data
567 , p_msg_index_out => l_msg_index_out);
568 x_msg_data := l_data;
569 x_msg_count := l_msg_count;
570 ELSE
571 x_msg_count := l_msg_count;
572 END IF;
573 IF l_debug_mode = 'Y' THEN
574 Pa_Debug.reset_curr_function;
575 END IF;
576
577 WHEN OTHERS THEN
578
579 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
580 x_msg_count := 1;
581 x_msg_data := SQLERRM;
582
583 IF p_commit = FND_API.G_TRUE THEN
584 ROLLBACK TO CREATE_DLVR_PVT;
585 END IF;
586
587 Fnd_Msg_Pub.add_exc_msg
588 ( p_pkg_name => 'PA_DELIVERABLE_PVT'
589 , p_procedure_name => 'Create_Deliverable'
590 , p_error_text => x_msg_data);
591
592 IF l_debug_mode = 'Y' THEN
593 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
594 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
595 l_debug_level5);
596 Pa_Debug.reset_curr_function;
597 END IF;
598 RAISE;
599
600 END Create_Deliverable;
601
602
603 -- Procedure : Update_Deliverable
604 -- Type : PRIVATE
605 -- Purpose : Update_Deliverable Procedure will be called for creation of deliverable
606 -- Note : Call update_row method of pa_proj_elements, pa_proj_element_versions,
607 -- : pa_proj_element_sch and pa_object_relationships packages
608 -- Assumptions : None
609
610 -- Parameters Type Required Description and Purpose
611 -- --------------------------- ------ -------- --------------------------------------------------------
612 -- p_api_version NUMBER N 1.0
613 -- p_init_msg_list VARCHAR2 N := FND_API.G_TRUE
614 -- p_commit VARCHAR2 N := FND_API.G_FALSE
615 -- p_validate_only VARCHAR2 N := FND_API.G_TRUE
616 -- p_validation_level NUMBER N := FND_API.G_VALID_LEVEL_FULL
617 -- p_calling_module VARCHAR2 N := 'SELF_SERVICE'
618 -- p_debug_mode VARCHAR2 N := 'N'
619 -- p_max_msg_count NUMBER N := NULL
620 -- p_record_version_number NUMBER N := 1
621 -- p_object_type VARCHAR2 N Object Type Default 'PA_DELIVERABLES'
622 -- p_project_id NUMBER Y Project Id
623 -- p_dlvr_number VARCHAR2 Y Deliverable Number
624 -- p_dlvr_name VARCHAR2 Y Deliverable Name
625 -- p_dlvr_description VARCHAR2 N Description
626 -- p_dlvr_owner_id NUMBER N Deliverable Owner Id
627 -- p_dlvr_owner_name VARCHAR2 N Delivearble Owner Name
628 -- p_carrying_out_org_id NUMBER N Project Carrying Out Organization Id
629 -- p_carrying_out_org_name VARCHAR2 N Project Carrying Out Organization Name
630 -- p_dlvr_version_id NUMBER N Deliverable Version Id
631 -- p_status_code VARCHAR2 N Delivearble Status
632 -- p_parent_structure_id NUMBER N
633 -- p_parent_struct_ver_id NUMBER N
634 -- p_dlvr_type_id NUMBER N Deliverable Type Id
635 -- p_dlvr_type_name VARCHAR2 N Deliverable Type Name
636 -- p_progress_weight NUMBER N Progress Weight
637 -- p_scheduled_finish_date DATE N Scheduled Finish Date
638 -- p_actual_finish_date DATE N Actual Finish Date
639 -- p_task_id NUMBER N
640 -- p_task_version_id NUMBER N
641 -- p_task_name VARCHAR2 N
642 -- p_attribute_category VARCHAR2 N
643 -- p_attribute1 VARCHAR2 N
644 -- p_attribute2 VARCHAR2 N
645 -- p_attribute3 VARCHAR2 N
646 -- p_attribute4 VARCHAR2 N
647 -- p_attribute5 VARCHAR2 N
648 -- p_attribute6 VARCHAR2 N
649 -- p_attribute7 VARCHAR2 N
650 -- p_attribute8 VARCHAR2 N
651 -- p_attribute9 VARCHAR2 N
652 -- p_attribute10 VARCHAR2 N
653 -- p_attribute11 VARCHAR2 N
654 -- p_attribute12 VARCHAR2 N
655 -- p_attribute13 VARCHAR2 N
656 -- p_attribute14 VARCHAR2 N
657 -- p_attribute15 VARCHAR2 N
658 -- p_dlvr_item_id NUMBER N proj_element_id
659 -- x_return_status VARCHAR2 N Return Status
660 -- x_msg_count NUMBER N Message Count
661 -- x_msg_data VARCHAR2 N Message Data
662
663 PROCEDURE Update_Deliverable
664 (
665 p_api_version IN NUMBER := 1.0
666 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
667 , p_commit IN VARCHAR2 := FND_API.G_FALSE
668 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
669 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
670 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
671 , p_debug_mode IN VARCHAR2 := 'N'
672 , p_max_msg_count IN NUMBER := NULL
673 , p_record_version_number IN NUMBER := 1
674 , p_object_type IN PA_PROJ_ELEMENTS.OBJECT_TYPE%TYPE := 'PA_DELIVERABLES'
675 , p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
676 , p_dlvr_number IN PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE
677 , p_dlvr_name IN PA_PROJ_ELEMENTS.NAME%TYPE
678 , p_dlvr_description IN PA_PROJ_ELEMENTS.DESCRIPTION%TYPE := NULL
679 , p_dlvr_owner_id IN PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE := NULL
680 , p_dlvr_owner_name IN VARCHAR2 := NULL
681 , p_carrying_out_org_id IN PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE := NULL
682 , p_carrying_out_org_name IN VARCHAR2 := NULL
683 , p_dlvr_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL
684 , p_status_code IN PA_PROJ_ELEMENTS.STATUS_CODE%TYPE := NULL
685 , p_parent_structure_id IN PA_PROJ_ELEMENTS.PARENT_STRUCTURE_ID%TYPE
686 , p_parent_struct_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
687 , p_dlvr_type_id IN PA_PROJ_ELEMENTS.TYPE_ID%TYPE := NULL
688 , p_dlvr_type_name IN VARCHAR2 := NULL
689 , p_progress_weight IN PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL
690 , p_scheduled_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE := NULL
691 , p_actual_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.ACTUAL_FINISH_DATE%TYPE := NULL
692 , p_task_id IN NUMBER := NULL
693 , p_task_version_id IN NUMBER := NULL
694 , p_task_name IN VARCHAR2 := NULL
695 , p_attribute_category IN PA_PROJ_ELEMENTS.ATTRIBUTE_CATEGORY%TYPE := NULL
696 , p_attribute1 IN PA_PROJ_ELEMENTS.ATTRIBUTE1%TYPE := NULL
697 , p_attribute2 IN PA_PROJ_ELEMENTS.ATTRIBUTE2%TYPE := NULL
698 , p_attribute3 IN PA_PROJ_ELEMENTS.ATTRIBUTE3%TYPE := NULL
699 , p_attribute4 IN PA_PROJ_ELEMENTS.ATTRIBUTE4%TYPE := NULL
700 , p_attribute5 IN PA_PROJ_ELEMENTS.ATTRIBUTE5%TYPE := NULL
701 , p_attribute6 IN PA_PROJ_ELEMENTS.ATTRIBUTE6%TYPE := NULL
702 , p_attribute7 IN PA_PROJ_ELEMENTS.ATTRIBUTE7%TYPE := NULL
703 , p_attribute8 IN PA_PROJ_ELEMENTS.ATTRIBUTE8%TYPE := NULL
704 , p_attribute9 IN PA_PROJ_ELEMENTS.ATTRIBUTE9%TYPE := NULL
705 , p_attribute10 IN PA_PROJ_ELEMENTS.ATTRIBUTE10%TYPE := NULL
706 , p_attribute11 IN PA_PROJ_ELEMENTS.ATTRIBUTE11%TYPE := NULL
707 , p_attribute12 IN PA_PROJ_ELEMENTS.ATTRIBUTE12%TYPE := NULL
708 , p_attribute13 IN PA_PROJ_ELEMENTS.ATTRIBUTE13%TYPE := NULL
709 , p_attribute14 IN PA_PROJ_ELEMENTS.ATTRIBUTE14%TYPE := NULL
710 , p_attribute15 IN PA_PROJ_ELEMENTS.ATTRIBUTE15%TYPE := NULL
711 , p_dlvr_item_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
712 , p_pm_source_code IN VARCHAR2 :=NULL /* Bug no. 3651113 */
713 , p_deliverable_reference IN VARCHAR2 := NULL -- added for bug# 3749447
714 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
715 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
716 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
717 )
718 IS
719
720 l_msg_count NUMBER := 0;
721 l_element_id NUMBER := NULL;
722 l_dlvr_version_id NUMBER := NULL;
723 l_data VARCHAR2(2000);
724 l_msg_data VARCHAR2(2000);
725 l_msg_index_out NUMBER;
726 l_debug_mode VARCHAR2(1);
727
728
729 l_utl_return_status VARCHAR2(1);
730 l_utl_msg_count NUMBER := 0;
731 l_utl_msg_data VARCHAR2(2000);
732
733 l_debug_level2 CONSTANT NUMBER := 2;
734 l_debug_level3 CONSTANT NUMBER := 3;
735 l_debug_level4 CONSTANT NUMBER := 4;
736 l_debug_level5 CONSTANT NUMBER := 5;
737
738 l_prog_rollup_method VARCHAR2(30) := NULL;
739 l_progress_weight PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL; -- 3570283 added
740 l_calling_mode VARCHAR2(30) := 'UPDATE_DUE_DATE';
741
742
743 l_ready_to_ship VARCHAR2(1) := 'N';
744 l_ready_to_procure VARCHAR2(1) := 'N';
745 l_read_to_create_demand VARCHAR2(1) := 'N';
746 l_planning_initiated VARCHAR2(1) := 'N';
747 l_proc_initiated VARCHAR2(1) := 'N';
748 l_item_info_exists VARCHAR2(1) := 'N';
749 l_item_shippable VARCHAR2(1) := 'N';
750 l_item_billable VARCHAR2(1) := 'N';
751 l_item_purchasable VARCHAR2(1) := 'N';
752 l_shipping_initiated VARCHAR2(1) := 'N';
753
754 l_object_type VARCHAR2(30) := 'PA_DELIVERABLES';
755 l_function_call_done VARCHAR2(1) := 'N';
756 l_system_status_code VARCHAR2(30) := NULL;
757 l_complete_sys_status_code VARCHAR2(30) := 'DLVR_COMPLETED';
758 l_not_started_sys_status_code VARCHAR2(30) := 'DLVR_NOT_STARTED';
759 is_dlvr_actions_exists VARCHAR2(1) := 'N';
760
761 l_update_allowed VARCHAR2(1) := NULL;
762 l_change_allowed VARCHAR2(1) := NULL;
763 l_dlvr_prg_enabled VARCHAR2(1) := NULL;
764 l_dlvr_action_enabled VARCHAR2(1) := NULL;
765 l_dlvr_has_progress VARCHAR2(1) := NULL;
766 l_dlvr_based_assc_exists VARCHAR2(1) := NULL;
767 l_new_pev_schedule_id NUMBER := NULL;
768 l_cancel_status VARCHAR2(30) := 'DLVR_ON_HOLD';
769 l_hold_status VARCHAR2(30) := 'DLVR_CANCELLED';
770 l_dlvr_default_status_code VARCHAR2(30) := NULL;
771 l_ship_procure_flag_dlv VARCHAR2(1) := NULL;
772
773 CURSOR l_row_id_ppe_csr
774 IS
775 SELECT
776 ROWID
777 ,PM_SOURCE_REFERENCE
778 FROM
779 PA_PROJ_ELEMENTS PPE
780 WHERE
781 PPE.PROJ_ELEMENT_ID = p_dlvr_item_id AND
782 PPE.PROJECT_ID = p_project_id AND
783 PPE.OBJECT_TYPE = l_object_type;
784
785 ppe_rec l_row_id_ppe_csr%ROWTYPE;
786
787 CURSOR l_row_id_pev_csr
788 IS
789 SELECT
790 ROWID
791 FROM
792 PA_PROJ_ELEMENT_VERSIONS PEV
793 WHERE
794 PEV.PROJ_ELEMENT_ID = p_dlvr_item_id AND
795 PEV.ELEMENT_VERSION_ID = p_dlvr_version_id AND
796 PEV.PROJECT_ID = p_project_id AND
797 PEV.OBJECT_TYPE = l_object_type;
798
799 pev_rec l_row_id_pev_csr%ROWTYPE;
800
801 CURSOR l_proj_system_status_csr
802 IS
803 SELECT
804 PPS.PROJECT_SYSTEM_STATUS_CODE
805 FROM
806 PA_PROJECT_STATUSES PPS
807 WHERE
808 PPS.PROJECT_STATUS_CODE = p_status_code;
809
810 CURSOR l_proj_sch_ver_info_csr
811 IS
812 SELECT
813 pev.pev_schedule_id,
814 ROWID
815 FROM
816 pa_proj_elem_ver_schedule pev
817 WHERE
818 pev.proj_element_id = p_dlvr_item_id AND
819 pev.ELEMENT_VERSION_ID = p_dlvr_version_id AND
820 pev.PROJECT_ID = p_project_id;
821
822 pes_rec l_proj_sch_ver_info_csr%ROWTYPE;
823
824 CURSOR l_dlvr_info_csr
825 IS
826 Select
827 ppe.element_number,
828 ppe.name,
829 ppe.description,
830 ppe.status_code,
831 ppe.manager_person_id,
832 ppe.carrying_out_organization_id,
833 ppe.record_version_number,
834 ppe.parent_structure_id,
835 ppe.type_id,
836 ppe.progress_weight,
837 ppe.base_percent_comp_deriv_code, --ppe.prog_rollup_method,
838 pvs.scheduled_finish_date,
839 pvs.actual_finish_date
840 From
841 pa_proj_elements ppe,
842 pa_proj_elem_ver_schedule pvs
843 Where
844 ppe.project_id = p_project_id and
845 ppe.proj_element_id = p_dlvr_item_id and
846 ppe.object_type = l_object_type and
847 pvs.project_id = p_project_id and
848 ppe.project_id = pvs.project_id and
849 ppe.proj_element_id = pvs.proj_element_id;
850
851 l_dlvr_info_rec l_dlvr_info_csr%ROWTYPE;
852
853
854 BEGIN
855 x_msg_count := 0;
856 x_return_status := FND_API.G_RET_STS_SUCCESS;
857 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
858
859 IF l_debug_mode = 'Y' THEN
860 PA_DEBUG.set_curr_function( p_function => 'UPDATE_DELIVERABLE',
861 p_debug_mode => l_debug_mode );
862 END IF;
863
864 IF (p_commit = FND_API.G_TRUE) THEN
865 savepoint UPDATE_DLVR_PVT;
866 END IF;
867
868 IF l_debug_mode = 'Y' THEN
869 Pa_Debug.WRITE(g_module_name,' Printing Input Params in UPDATE_DELIVERABLE : PADLVPVB.pls ####()',
870 l_debug_level3);
871 Pa_Debug.WRITE(g_module_name,' ####() p_project_id is '||p_project_id,
872 l_debug_level3);
873 Pa_Debug.WRITE(g_module_name,' ####()p_dlvr_item_id is '||p_dlvr_item_id,
874 l_debug_level3);
875 Pa_Debug.WRITE(g_module_name,' ###() p_dlvr_version_id is ' || p_dlvr_version_id,
876 l_debug_level3);
877 Pa_Debug.WRITE(g_module_name,' ####() p_status_code' || p_status_code,
878 l_debug_level3);
879 Pa_Debug.WRITE(g_module_name,' ####() p_dlvr_type_id ' || p_dlvr_type_id,
880 l_debug_level3);
881 END IF;
882
883 -- Business Logic
884
885 -- retrieve progress rollup method for task
886 -- if it is deliveable_based, check progress_weight is not null
887
888 -- 3570283 Progress Weight mandatory if deliverale is associated with deliverable based task
889
890 -- removed the below code
891 -- in case of deliverable list page, task id will be null, so the above check will fail
892 -- though it should not fail
893
894 /*
895 l_prog_rollup_method := PA_DELIVERABLE_UTILS.get_progress_rollup_method
896 (
897 p_task_id => p_task_id
898 );
899
900 IF l_prog_rollup_method = g_deliverable_based THEN
901 IF p_progress_weight IS NULL THEN
902 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
903 p_msg_name => 'PA_DLVR_PROG_WEIGHT_MISSING');
904 x_return_status := FND_API.G_RET_STS_ERROR;
905 END IF;
906 END IF;
907 */
908
909 -- retrieve dlvr_progress_flag and enale_dlvr_action_flag for deliveable type
910
911 IF l_debug_mode = 'Y' THEN
912 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_UTILS.GET_DLVR_TYPE_INFO Called ',
913 l_debug_level3);
914 END IF;
915
916 PA_DELIVERABLE_UTILS.GET_DLVR_TYPE_INFO
917 (
918 p_dlvr_type_id => p_dlvr_type_id,
919 x_dlvr_prg_enabled => l_dlvr_prg_enabled,
920 x_dlvr_action_enabled => l_dlvr_action_enabled,
921 x_dlvr_default_status_code => l_dlvr_default_status_code
922 );
923
924 IF l_debug_mode = 'Y' THEN
925 Pa_Debug.WRITE(g_module_name,' Out of A_DELIVERABLE_UTILS.GET_DLVR_TYPE_INFO ',
926 l_debug_level3);
927 END IF;
928
929 IF l_debug_mode = 'Y' THEN
930 Pa_Debug.WRITE(g_module_name,' l_dlvr_prg_enabled ' || l_dlvr_prg_enabled, l_debug_level3);
931 END IF;
932
933 -- added the following code
934
935 -- if progress is enabled, check for the deliverable, whether deliverable based task association is there or not
936 -- if yes, check progress weight is entered or not
937
938 IF l_dlvr_prg_enabled = 'Y' THEN
939
940 l_dlvr_based_assc_exists := PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS
941 (
942 p_dlv_element_id => p_dlvr_item_id
943 );
944
945 IF l_dlvr_based_assc_exists = 'Y' THEN
946 IF p_progress_weight IS NULL THEN
947 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
948 p_msg_name => 'PA_DLVR_PROG_WEIGHT_MISSING');
949 x_return_status := FND_API.G_RET_STS_ERROR;
950 END IF;
951 END IF;
952
953 END IF;
954
955 -- 3570283
956
957 -- retrieving the old deliverable information
958
959 OPEN l_dlvr_info_csr;
960 FETCH l_dlvr_info_csr INTO l_dlvr_info_rec;
961 CLOSE l_dlvr_info_csr;
962
963 -- 3956982 Commented out below validation, deliverable due date change is allowed if "Ready to Ship" or
964 -- "Ready to Procure" is checked
965
966 /*
967
968 -- if "Ready to Ship" or "Ready to Procure" is checked ( i.e. 'Y' THEN )
969 -- then deliverable due date ( i.e. scheduled_finish_date ) update is not allowed
970 -- add the error message and set return_status to errror
971 -- To retrieve "Ready to Ship" or "Ready to Procure" statuses call
972 -- PA_DELIVERABLE_UTILS.PA_DLVR_OKE_INTEGRATION and retrieve the required statuses
973
974
975 IF trim(l_dlvr_info_rec.scheduled_finish_date) <> trim(p_scheduled_finish_date) THEN
976
977 IF l_debug_mode = 'Y' THEN
978 Pa_Debug.WRITE(g_module_name,' Scheduled_finish_date is changed in update dlvr',
979 l_debug_level3);
980 END IF;
981
982 IF l_debug_mode = 'Y' THEN
983 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_UTILS.GET_OKE_FLAGS Called ',
984 l_debug_level3);
985 END IF;
986
987 PA_DELIVERABLE_UTILS.GET_OKE_FLAGS
988 (
989 P_PROJECT_ID => p_project_id
990 ,P_DLVR_ITEM_ID => p_dlvr_item_id
991 ,P_DLVR_VERSION_ID => p_dlvr_version_id
992 ,P_ACTION_ITEM_ID => null
993 ,P_ACTION_VERSION_ID => null
994 ,P_CALLING_MODULE => l_calling_mode
995 ,X_READY_TO_SHIP => l_ready_to_ship
996 ,X_READY_TO_PROCURE => l_ready_to_procure
997 ,X_PLANNING_INITIATED => l_planning_initiated
998 ,X_PROC_INITIATED => l_proc_initiated
999 ,X_ITEM_EXISTS => l_item_info_exists
1000 ,X_ITEM_SHIPPABLE => l_item_shippable
1001 ,X_ITEM_BILLABLE => l_item_billable
1002 ,X_ITEM_PURCHASABLE => l_item_purchasable
1003 ,X_SHIPPING_INITIATED => l_shipping_initiated
1004 ,X_SHIP_PROCURE_FLAG_DLV => l_ship_procure_flag_dlv
1005 ,X_RETURN_STATUS => l_utl_return_status
1006 ,X_MSG_COUNT => l_utl_msg_count
1007 ,X_MSG_DATA => l_utl_msg_data
1008 );
1009
1010 IF l_debug_mode = 'Y' THEN
1011 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_UTILS.GET_OKE_FLAGS ',
1012 l_debug_level3);
1013 END IF;
1014
1015 -- set l_function_call_done to 'Y' if PA_DELIVERABLE_UTILS.PA_DLVR_OKE_INTEGRATION
1016 -- if l_function_call_done is set to 'Y' , the above function will not be called again
1017 l_function_call_done := 'Y';
1018
1019 IF l_utl_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1020 x_return_status := FND_API.G_RET_STS_ERROR;
1021 END IF;
1022
1023
1024 IF l_debug_mode = 'Y' THEN
1025 Pa_Debug.WRITE(g_module_name,' l_ship_procure_flag_dlv ' || l_ship_procure_flag_dlv,
1026 l_debug_level3);
1027 END IF;
1028
1029 IF l_ship_procure_flag_dlv = 'Y' THEN
1030 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1031 p_msg_name => 'PA_DLV_DUEDATECHG_NOT_ALLOWED');
1032 x_return_status := FND_API.G_RET_STS_ERROR;
1033 END IF;
1034
1035 END IF;
1036
1037 IF l_debug_mode = 'Y' THEN
1038 Pa_Debug.WRITE(g_module_name,' 1st validation status ' || x_return_status,
1039 l_debug_level3);
1040 END IF;
1041
1042 */
1043
1044 -- 3956982 end
1045
1046 -- retrieve project_system_status for the user entered deliverable status
1047
1048 OPEN l_proj_system_status_csr;
1049 FETCH l_proj_system_status_csr INTO l_system_status_code;
1050 CLOSE l_proj_system_status_csr;
1051
1052
1053 -- 3661686 incorporated review comments
1054 -- moved the below two validation here
1055
1056 -- 3661686 added following code to validate the following scenario
1057 -- if user has entered deliverable completion date and not changed status to completed
1058 -- error message should shown
1059
1060 IF p_actual_finish_date IS NOT NULL AND l_system_status_code <> l_complete_sys_status_code THEN
1061 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1062 p_msg_name => 'PA_DLVR_STATUS_NOT_COMPLETED');
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 END IF;
1065
1066 -- if user changes deliverable status to dlvr_completed , user must enter actual_finish_date ( i.e. completion date )
1067 -- if user doesn't enter completion date, set return status to error
1068
1069 IF l_system_status_code = l_complete_sys_status_code AND p_actual_finish_date IS NULL THEN
1070 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1071 p_msg_name => 'PA_DLVR_COMPLT_DATE_MISSING');
1072 x_return_status := FND_API.G_RET_STS_ERROR;
1073 END IF;
1074
1075 -- if status is completed, check for the deliverable completion validations
1076 -- 1. document is defined or not ( if document based deliverable )
1077 -- 2. item information is defined or not ( if item based deliverable )
1078 -- 3. shipping/procurement is initiated or not
1079 -- 4. billing event is processed or not
1080
1081 IF l_complete_sys_status_code = l_system_status_code THEN
1082
1083 PA_DELIVERABLE_UTILS.IS_DLV_STATUS_CHANGE_ALLOWED
1084 (
1085 P_PROJECT_ID => p_project_id
1086 ,P_DLVR_ITEM_ID => p_dlvr_item_id
1087 ,P_DLVR_VERSION_ID => p_dlvr_version_id
1088 ,P_DLV_TYPE_ID => p_dlvr_type_id
1089 ,P_DLVR_STATUS_CODE => p_status_code
1090 ,x_return_status => l_utl_return_status
1091 ,x_msg_count => l_utl_msg_count
1092 ,x_msg_data => l_utl_msg_data
1093 );
1094
1095 IF l_utl_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1096 x_return_status := FND_API.G_RET_STS_ERROR;
1097 END IF;
1098 END IF;
1099
1100 IF l_debug_mode = 'Y' THEN
1101 Pa_Debug.WRITE(g_module_name,' 3rd validation status ' || x_return_status,
1102 l_debug_level3);
1103 END IF;
1104
1105
1106 -- if dlvr status has changed, dlvr_progress_flag and dlvr_action_enabled_flag are set to 'Y'
1107 -- and deliveable has progress records does not exists
1108 -- deliverable status change should be the one, which is mapping to "Not Started" status
1109 -- if it is not mapping to "Not Started" status, set return status to error
1110
1111 IF l_dlvr_info_rec.status_code <> p_status_code THEN
1112
1113 IF l_debug_mode = 'Y' THEN
1114 Pa_Debug.WRITE(g_module_name,' dlvr status is changed ',
1115 l_debug_level3);
1116 END IF;
1117
1118 IF l_dlvr_prg_enabled = 'Y' THEN
1119 -- 3661686 commented l_dlvr_action_enabled condition ,
1120 -- according to latest FD, for l_dlvr_action_enabled condition should not be checked
1121 -- while checking for the progress record existance
1122 -- IF l_dlvr_action_enabled = 'Y' THEN
1123 l_dlvr_has_progress := PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS
1124 (
1125 p_project_id => p_project_id,
1126 p_proj_element_id => p_dlvr_item_id
1127 );
1128 IF l_debug_mode = 'Y' THEN
1129 Pa_Debug.WRITE(g_module_name,' For Deliverable progress and action is enabled ',
1130 l_debug_level3);
1131 END IF;
1132
1133 IF l_dlvr_has_progress = 'N' THEN
1134 IF l_debug_mode = 'Y' THEN
1135 Pa_Debug.WRITE(g_module_name,' Deliverable does not have progress records ',
1136 l_debug_level3);
1137 END IF;
1138
1139 IF l_system_status_code <> l_not_started_sys_status_code THEN
1140 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1141 p_msg_name => 'PA_DLV_NOT_START_ONLY_ALLOWED');
1142 x_return_status := FND_API.G_RET_STS_ERROR;
1143 END IF;
1144 END IF;
1145 -- END IF;
1146 END IF;
1147 END IF;
1148
1149 IF l_debug_mode = 'Y' THEN
1150 Pa_Debug.WRITE(g_module_name,' 2nd validation status ' || x_return_status,
1151 l_debug_level3);
1152 END IF;
1153
1154 -- if user changes status to 'cancelled' or 'on hold ', delivearble action functions needs to be validated
1155 -- call PA_DELIVERABLE_UTILS.IS_DLV_STATUS_CHANGE_ALLOWED for this validation
1156 -- if x_change_allowed is set to 'N', set return status to error
1157
1158 IF l_dlvr_info_rec.status_code <> p_status_code THEN
1159
1160 -- Bug 3499825 The following line is commented by avaithia on 02-Apr-2004
1161 -- The l_cancel_status / l_hold_status correspond to system statuses whereas p_system_code is the userdefined status
1162 --So,The following check is wrong .The l_cancel_status / l_hold_status should be compared with l_system_status_code.
1163
1164 -- IF p_status_code = l_cancel_status or p_status_code = l_hold_status THEN
1165
1166 IF l_system_status_code = l_cancel_status or l_system_status_code = l_hold_status THEN
1167 PA_DELIVERABLE_UTILS.IS_DLV_STATUS_CHANGE_ALLOWED
1168 (
1169 P_PROJECT_ID => p_project_id
1170 ,P_DLVR_ITEM_ID => p_dlvr_item_id
1171 ,P_DLVR_VERSION_ID => p_dlvr_version_id
1172 ,P_DLV_TYPE_ID => p_dlvr_type_id
1173 ,P_DLVR_STATUS_CODE => p_status_code
1174 ,x_return_status => l_utl_return_status
1175 ,x_msg_count => l_utl_msg_count
1176 ,x_msg_data => l_utl_msg_data
1177 );
1178
1179 -- 4229934 based on the return status , setting x_return_status
1180 IF l_utl_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 END IF;
1183
1184 -- commented below code which populates error message
1185 -- because above api call takes care of populating it
1186 /*
1187 IF l_utl_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1188 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1189 p_msg_name => 'PA_DLV_STATUS_CHG_NOT_ALLOWED');
1190 x_return_status := FND_API.G_RET_STS_ERROR;
1191 END IF;
1192 */
1193 -- 4229934 end
1194 END IF;
1195 END IF;
1196
1197 IF l_debug_mode = 'Y' THEN
1198 Pa_Debug.WRITE(g_module_name,' 4th validation status ' || x_return_status,
1199 l_debug_level3);
1200 END IF;
1201
1202 -- if user changes dlvr type, check wether deliverable actions for that delivearble exists or not
1203 -- if dlvr actions are existing, set return status to error
1204
1205 IF l_dlvr_info_rec.type_id <> p_dlvr_type_id THEN
1206 is_dlvr_actions_exists := PA_DELIVERABLE_UTILS.IS_ACTIONS_EXISTS
1207 (
1208 p_project_id => p_project_id,
1209 p_proj_element_id => p_dlvr_item_id
1210 );
1211 IF is_dlvr_actions_exists = 'Y' THEN
1212 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1213 p_msg_name => 'PA_DLVR_ACTION_EXISTS');
1214 x_return_status := FND_API.G_RET_STS_ERROR;
1215 END IF;
1216
1217 l_dlvr_has_progress := PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS
1218 (
1219 p_project_id => p_project_id,
1220 p_proj_element_id => p_dlvr_item_id
1221 );
1222
1223 -- if dlvr has progress and dlvr type is changes, set return status to error
1224
1225 IF l_dlvr_has_progress = 'Y' THEN
1226 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1227 p_msg_name => 'PA_DLVR_PROGRESS_EXISTS');
1228 x_return_status := FND_API.G_RET_STS_ERROR;
1229 END IF;
1230
1231 END IF;
1232
1233 IF l_debug_mode = 'Y' THEN
1234 Pa_Debug.WRITE(g_module_name,'5th validation status ' || x_return_status,
1235 l_debug_level3);
1236 END IF;
1237
1238
1239 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1240 RAISE FND_API.G_EXC_ERROR;
1241 END IF;
1242
1243 IF l_debug_mode = 'Y' THEN
1244 Pa_Debug.WRITE(g_module_name,' x_return_status ' || x_return_status,
1245 l_debug_level3);
1246 END IF;
1247
1248 IF l_debug_mode = 'Y' THEN
1249 Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENTS_PKG.Update_Row Called ',
1250 l_debug_level3);
1251 END IF;
1252
1253 -- call update_row of pa_proj_elements package
1254
1255 l_element_id := p_dlvr_item_id;
1256
1257 -- 3570283 added
1258 -- if deliverable type is changed from progress enabled one to disabled one
1259 -- progress weight should be set to null value
1260
1261 IF l_dlvr_prg_enabled = 'Y' THEN
1262 l_progress_weight := p_progress_weight;
1263 ELSE
1264 l_progress_weight := NULL;
1265 END IF;
1266
1267 -- 3570283 end
1268
1269 OPEN l_row_id_ppe_csr;
1270 FETCH l_row_id_ppe_csr INTO ppe_rec;
1271 CLOSE l_row_id_ppe_csr;
1272
1273 PA_PROJ_ELEMENTS_PKG.Update_Row
1274 (
1275 X_ROW_ID => ppe_rec.rowid
1276 ,X_PROJ_ELEMENT_ID => l_element_id
1277 ,X_PROJECT_ID => p_project_id
1278 ,X_OBJECT_TYPE => p_object_type
1279 ,X_ELEMENT_NUMBER => p_dlvr_number
1280 ,X_NAME => p_dlvr_name
1281 ,X_DESCRIPTION => p_dlvr_description
1282 ,X_STATUS_CODE => p_status_code
1283 ,X_WF_STATUS_CODE => NULL
1284 ,X_PM_PRODUCT_CODE => p_pm_source_code /* Bug no. 3651113 -- Passed p_pm_source_code instead of null*/
1285 ,X_PM_TASK_REFERENCE => p_deliverable_reference -- 3749447 changed from NULL to retrieved value
1286 ,X_CLOSED_DATE => NULL
1287 ,X_LOCATION_ID => NULL
1288 ,X_MANAGER_PERSON_ID => p_dlvr_owner_id
1289 ,X_CARRYING_OUT_ORGANIZATION_ID => p_carrying_out_org_id
1290 ,X_TYPE_ID => p_dlvr_type_id
1291 ,X_PRIORITY_CODE => NULL
1292 ,X_INC_PROJ_PROGRESS_FLAG => NULL
1293 ,X_RECORD_VERSION_NUMBER => p_record_version_number
1294 ,X_REQUEST_ID => NULL
1295 ,X_PROGRAM_APPLICATION_ID => NULL
1296 ,X_PROGRAM_ID => NULL
1297 ,X_PROGRAM_UPDATE_DATE => NULL
1298 ,X_ATTRIBUTE_CATEGORY => p_attribute_category
1299 ,X_ATTRIBUTE1 => p_attribute1
1300 ,X_ATTRIBUTE2 => p_attribute2
1301 ,X_ATTRIBUTE3 => p_attribute3
1302 ,X_ATTRIBUTE4 => p_attribute4
1303 ,X_ATTRIBUTE5 => p_attribute5
1304 ,X_ATTRIBUTE6 => p_attribute6
1305 ,X_ATTRIBUTE7 => p_attribute7
1306 ,X_ATTRIBUTE8 => p_attribute8
1307 ,X_ATTRIBUTE9 => p_attribute9
1308 ,X_ATTRIBUTE10 => p_attribute10
1309 ,X_ATTRIBUTE11 => p_attribute11
1310 ,X_ATTRIBUTE12 => p_attribute12
1311 ,X_ATTRIBUTE13 => p_attribute13
1312 ,X_ATTRIBUTE14 => p_attribute14
1313 ,X_ATTRIBUTE15 => p_attribute15
1314 ,X_TASK_WEIGHTING_DERIV_CODE => NULL
1315 ,X_WORK_ITEM_CODE => NULL
1316 ,X_UOM_CODE => NULL
1317 ,X_WQ_ACTUAL_ENTRY_CODE => NULL
1318 ,X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL
1319 ,X_PARENT_STRUCTURE_ID => p_parent_structure_id
1320 ,X_PHASE_CODE => NULL
1321 ,X_PHASE_VERSION_ID => NULL
1322 ,X_PROGRESS_WEIGHT => l_progress_weight -- 3570283 changed from p_progress_weight
1323 -- ,X_PROG_ROLLUP_METHOD => NULL
1324 ,X_FUNCTION_CODE => NULL
1325 );
1326
1327
1328 IF l_debug_mode = 'Y' THEN
1329 Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENTS_PKG.Update_Row ',
1330 l_debug_level3);
1331 END IF;
1332
1333 IF l_debug_mode = 'Y' THEN
1334 Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row Called ',
1335 l_debug_level3);
1336 END IF;
1337
1338 -- call update_row of pa_proj_element_versions package
1339
1340 OPEN l_row_id_pev_csr;
1341 FETCH l_row_id_pev_csr INTO pev_rec;
1342 CLOSE l_row_id_pev_csr;
1343
1344 PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row
1345 (
1346 X_ROW_ID => pev_rec.rowid
1347 ,X_ELEMENT_VERSION_ID => p_dlvr_version_id
1348 ,X_PROJ_ELEMENT_ID => p_dlvr_item_id
1349 ,X_OBJECT_TYPE => p_object_type
1350 ,X_PROJECT_ID => p_project_id
1351 ,X_PARENT_STRUCTURE_VERSION_ID => p_parent_struct_ver_id
1352 ,X_DISPLAY_SEQUENCE => NULL
1353 ,X_WBS_LEVEL => NULL
1354 ,X_WBS_NUMBER => NULL
1355 ,X_RECORD_VERSION_NUMBER => p_record_version_number
1356 ,X_ATTRIBUTE_CATEGORY => p_attribute_category
1357 ,X_ATTRIBUTE1 => p_attribute1
1358 ,X_ATTRIBUTE2 => p_attribute2
1359 ,X_ATTRIBUTE3 => p_attribute3
1360 ,X_ATTRIBUTE4 => p_attribute4
1361 ,X_ATTRIBUTE5 => p_attribute5
1362 ,X_ATTRIBUTE6 => p_attribute6
1363 ,X_ATTRIBUTE7 => p_attribute7
1364 ,X_ATTRIBUTE8 => p_attribute8
1365 ,X_ATTRIBUTE9 => p_attribute9
1366 ,X_ATTRIBUTE10 => p_attribute10
1367 ,X_ATTRIBUTE11 => p_attribute11
1368 ,X_ATTRIBUTE12 => p_attribute12
1369 ,X_ATTRIBUTE13 => p_attribute13
1370 ,X_ATTRIBUTE14 => p_attribute14
1371 ,X_ATTRIBUTE15 => p_attribute15
1372 ,X_TASK_UNPUB_VER_STATUS_CODE => NULL
1373 );
1374
1375 IF l_debug_mode = 'Y' THEN
1376 Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row ',
1377 l_debug_level3);
1378 END IF;
1379
1380 IF l_debug_mode = 'Y' THEN
1381 Pa_Debug.WRITE(g_module_name,' PA_PROJ_ELEMENT_SCH_PKG.Update_Row Called ',
1382 l_debug_level3);
1383 END IF;
1384
1385 -- call update_row of pa_proj_element_sch package
1386
1387
1388 OPEN l_proj_sch_ver_info_csr;
1389 FETCH l_proj_sch_ver_info_csr INTO pes_rec ;
1390 CLOSE l_proj_sch_ver_info_csr;
1391
1392
1393
1394 PA_PROJ_ELEMENT_SCH_PKG.Update_Row
1395 (
1396 X_ROW_ID => pes_rec.rowid
1397 ,X_PEV_SCHEDULE_ID => pes_rec.pev_schedule_id
1398 ,X_ELEMENT_VERSION_ID => p_dlvr_version_id
1399 ,X_PROJECT_ID => p_project_id
1400 ,X_PROJ_ELEMENT_ID => p_dlvr_item_id
1401 ,X_SCHEDULED_START_DATE => NULL
1402 ,X_SCHEDULED_FINISH_DATE => p_scheduled_finish_date
1403 ,X_OBLIGATION_START_DATE => NULL
1404 ,X_OBLIGATION_FINISH_DATE => NULL
1405 ,X_ACTUAL_START_DATE => NULL
1406 ,X_ACTUAL_FINISH_DATE => p_actual_finish_date
1407 ,X_ESTIMATED_START_DATE => NULL
1408 ,X_ESTIMATED_FINISH_DATE => NULL
1409 ,X_DURATION => NULL
1410 ,X_EARLY_START_DATE => NULL
1411 ,X_EARLY_FINISH_DATE => NULL
1412 ,X_LATE_START_DATE => NULL
1413 ,X_LATE_FINISH_DATE => NULL
1414 ,X_CALENDAR_ID => NULL
1415 ,X_MILESTONE_FLAG => NULL
1416 ,X_CRITICAL_FLAG => NULL
1417 ,X_WQ_PLANNED_QUANTITY => NULL
1418 ,X_PLANNED_EFFORT => NULL
1419 ,X_ACTUAL_DURATION => NULL
1420 ,X_ESTIMATED_DURATION => NULL
1421 ,X_RECORD_VERSION_NUMBER => p_record_version_number
1422 ,X_ATTRIBUTE_CATEGORY => p_attribute_category
1423 ,X_ATTRIBUTE1 => p_attribute1
1424 ,X_ATTRIBUTE2 => p_attribute2
1425 ,X_ATTRIBUTE3 => p_attribute3
1426 ,X_ATTRIBUTE4 => p_attribute4
1427 ,X_ATTRIBUTE5 => p_attribute5
1428 ,X_ATTRIBUTE6 => p_attribute6
1429 ,X_ATTRIBUTE7 => p_attribute7
1430 ,X_ATTRIBUTE8 => p_attribute8
1431 ,X_ATTRIBUTE9 => p_attribute9
1432 ,X_ATTRIBUTE10 => p_attribute10
1433 ,X_ATTRIBUTE11 => p_attribute11
1434 ,X_ATTRIBUTE12 => p_attribute12
1435 ,X_ATTRIBUTE13 => p_attribute13
1436 ,X_ATTRIBUTE14 => p_attribute14
1437 ,X_ATTRIBUTE15 => p_attribute15
1438 );
1439
1440 IF l_debug_mode = 'Y' THEN
1441 Pa_Debug.WRITE(g_module_name,' Out of PA_PROJ_ELEMENT_SCH_PKG.Update_Row ',
1442 l_debug_level3);
1443 END IF;
1444
1445 -- if deliverable type is changed , copy the actions of that deliverable type
1446 -- to that deliverable
1447
1448 -- 3749447 in amg flow, if deliverable type is changed, defaulting of actions should not
1449 -- be done from deliverabel type
1450
1451 IF p_calling_module <> 'AMG' AND l_dlvr_info_rec.type_id <> p_dlvr_type_id THEN
1452
1453 IF l_debug_mode = 'Y' THEN
1454 Pa_Debug.WRITE(g_module_name,' PA_ACTIONS_PUB.COPY_ACTIONS Called ',
1455 l_debug_level3);
1456 END IF;
1457
1458 PA_ACTIONS_PUB.COPY_ACTIONS
1459 (
1460 p_init_msg_list => p_init_msg_list
1461 ,p_commit => p_commit
1462 ,p_debug_mode => l_debug_mode
1463 ,p_source_object_id => p_dlvr_type_id
1464 ,p_source_object_type => 'PA_DLVR_TYPES'
1465 ,p_target_object_id => p_dlvr_item_id
1466 ,p_target_object_type => 'PA_DELIVERABLES'
1467 ,p_source_project_id => null
1468 ,p_target_project_id => p_project_id
1469 ,p_task_id => p_task_id
1470 ,p_task_ver_id => p_task_version_id
1471 ,p_carrying_out_organization_id => p_carrying_out_org_id
1472 ,p_pm_source_reference => null
1473 ,p_pm_source_code => null
1474 ,p_calling_mode => 'UPDATE' -- Added for bug 3911050
1475 ,x_return_status => x_return_status
1476 ,x_msg_count => x_msg_count
1477 ,x_msg_data => x_msg_data
1478 ) ;
1479
1480 IF l_debug_mode = 'Y' THEN
1481 Pa_Debug.WRITE(g_module_name,' Out of PA_ACTIONS_PUB.COPY_ACTIONS ',
1482 l_debug_level3);
1483 END IF;
1484
1485 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1486 RAISE FND_API.G_EXC_ERROR;
1487 END IF;
1488
1489 END IF;
1490
1491 -- 3749447 added call to create deliverable to task association if task id is passed, in amg flow
1492 -- if task to deliverable association is already there, do nothing
1493 -- else create task to deliverable association
1494
1495 IF p_calling_module = 'AMG' AND p_task_id IS NOT NULL THEN
1496
1497 PA_DELIVERABLE_PVT.CREATE_DLV_TASK_ASSOCIATION
1498 ( p_debug_mode => l_debug_mode
1499 ,p_task_element_id => p_task_id
1500 ,p_task_version_id => p_task_version_id
1501 ,p_dlv_element_id => p_dlvr_item_id
1502 ,p_dlv_version_id => p_dlvr_version_id
1503 ,p_project_id => p_project_id
1504 ,x_return_status => x_return_status
1505 ,x_msg_count => x_msg_count
1506 ,x_msg_data => x_msg_data
1507 );
1508
1509 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1510 RAISE FND_API.G_EXC_ERROR;
1511 END IF;
1512
1513 END IF;
1514
1515 x_return_status := FND_API.G_RET_STS_SUCCESS;
1516 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
1517 pa_debug.reset_curr_function;
1518 END IF ;
1519
1520 EXCEPTION
1521
1522 WHEN FND_API.G_EXC_ERROR THEN
1523 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1524 l_msg_count := Fnd_Msg_Pub.count_msg;
1525
1526 IF l_row_id_ppe_csr%ISOPEN THEN
1527 CLOSE l_row_id_ppe_csr;
1528 END IF;
1529
1530 IF l_row_id_pev_csr%ISOPEN THEN
1531 CLOSE l_row_id_pev_csr;
1532 END IF;
1533
1534 IF l_proj_system_status_csr%ISOPEN THEN
1535 CLOSE l_proj_system_status_csr;
1536 END IF;
1537
1538 IF l_proj_sch_ver_info_csr%ISOPEN THEN
1539 CLOSE l_proj_sch_ver_info_csr;
1540 END IF;
1541
1542 IF l_dlvr_info_csr%ISOPEN THEN
1543 CLOSE l_dlvr_info_csr;
1544 END IF;
1545
1546 IF p_commit = FND_API.G_TRUE THEN
1547 ROLLBACK TO UPDATE_DLVR_PVT;
1548 END IF;
1549
1550 IF l_msg_count = 1 AND x_msg_data IS NULL
1551 THEN
1552 Pa_Interface_Utils_Pub.get_messages
1553 ( p_encoded => Fnd_Api.G_TRUE
1554 , p_msg_index => 1
1555 , p_msg_count => l_msg_count
1556 , p_msg_data => l_msg_data
1557 , p_data => l_data
1558 , p_msg_index_out => l_msg_index_out);
1559 x_msg_data := l_data;
1560 x_msg_count := l_msg_count;
1561 ELSE
1562 x_msg_count := l_msg_count;
1563 END IF;
1564 IF l_debug_mode = 'Y' THEN
1565 Pa_Debug.reset_curr_function;
1566 END IF;
1567
1568 WHEN OTHERS THEN
1569
1570 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1571 x_msg_count := 1;
1572 x_msg_data := SQLERRM;
1573
1574 IF l_row_id_ppe_csr%ISOPEN THEN
1575 CLOSE l_row_id_ppe_csr;
1576 END IF;
1577
1578 IF l_row_id_pev_csr%ISOPEN THEN
1579 CLOSE l_row_id_pev_csr;
1580 END IF;
1581
1582 IF l_proj_system_status_csr%ISOPEN THEN
1583 CLOSE l_proj_system_status_csr;
1584 END IF;
1585
1586 IF l_proj_sch_ver_info_csr%ISOPEN THEN
1587 CLOSE l_proj_sch_ver_info_csr;
1588 END IF;
1589
1590 IF l_dlvr_info_csr%ISOPEN THEN
1591 CLOSE l_dlvr_info_csr;
1592 END IF;
1593
1594 IF p_commit = FND_API.G_TRUE THEN
1595 ROLLBACK TO UPDATE_DLVR_PVT;
1596 END IF;
1597
1598 Fnd_Msg_Pub.add_exc_msg
1599 ( p_pkg_name => 'PA_DELIVERABLE_PVT'
1600 , p_procedure_name => 'Update_Deliverable'
1601 , p_error_text => x_msg_data);
1602
1603 IF l_debug_mode = 'Y' THEN
1604 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1605 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1606 l_debug_level5);
1607 Pa_Debug.reset_curr_function;
1608 END IF;
1609 RAISE;
1610
1611 END Update_Deliverable;
1612
1613 -- SubProgram : delete_deliverable
1614 -- Type : PROCEDURE
1615 -- Purpose : Public API to Delete Task - Deliverable Association
1616 -- Note : Public API called from Task Detail and Deliverable Details Page
1617 -- Assumptions : None
1618 -- Parameter IN/OUT Type Required Description and Purpose
1619 -- --------------------------- --------- ---------- --------- ---------------------------
1620 -- p_api_version IN NUMBER N Standard Parameter
1621 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
1622 -- p_commit IN VARCHAR2 N Standard Parameter
1623 -- p_validate_only IN VARCHAR2 N Standard Parameter
1624 -- p_validation_level IN NUMBER N Standard Parameter
1625 -- p_calling_module IN VARCHAR2 N Standard Parameter
1626 -- p_debug_mode IN VARCHAR2 N Standard Parameter
1627 -- p_max_msg_count IN NUMBER N Standard Parameter
1628 -- p_dlv_element_id IN NUMBER N Deliverable Element Id
1629 -- p_dlv_version_id IN NUMBER N Deliverbale Version Id
1630 -- p_rec_ver_number IN NUMBER N Record Version Number
1631 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
1632 -- x_msg_count OUT NUMBER N Standard Out Parameter
1633 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
1634
1635
1636 PROCEDURE delete_deliverable
1637 (p_api_version IN NUMBER :=1.0
1638 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
1639 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
1640 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
1641 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
1642 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
1643 ,p_debug_mode IN VARCHAR2 :='N'
1644 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1645 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
1646 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
1647 ,p_rec_ver_number IN pa_proj_elements.record_version_number%TYPE
1648 ,p_project_id IN pa_projects_all.project_id%TYPE
1649 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1650 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1651 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1652 )
1653 IS
1654 l_debug_mode VARCHAR2(10);
1655 l_msg_count NUMBER ;
1656 l_data VARCHAR2(2000);
1657 l_msg_data VARCHAR2(2000);
1658 l_msg_index_out NUMBER;
1659 l_disassociation_allowed VARCHAR2(1);
1660 l_dummy VARCHAR2(1);
1661 l_action_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1662 l_action_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1663 l_dlv_rec oke_amg_grp.dlv_rec_type;
1664 l_item_dlv VARCHAR2(1) := NULL;
1665 i NUMBER;
1666
1667 -- 3733321 added local variables
1668
1669 l_master_inv_org_id PA_PLAN_RES_DEFAULTS.item_master_id%TYPE;
1670 l_return_status VARCHAR2(1);
1671
1672 BEGIN
1673
1674 x_msg_count := 0;
1675 x_return_status := FND_API.G_RET_STS_SUCCESS;
1676 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1677
1678 IF l_debug_mode = 'Y' THEN
1679 PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLE',
1680 p_debug_mode => l_debug_mode );
1681 pa_debug.g_err_stage:= 'Inside DELETE_DELIVERABLE ';
1682 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1683 END IF;
1684
1685 IF l_debug_mode = 'Y' THEN
1686 pa_debug.g_err_stage:= 'Printing Input parameters';
1687 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1688 pa_debug.write(g_module_name,'p_dlv_element_id '||':'||p_dlv_element_id,3) ;
1689 pa_debug.write(g_module_name,'p_dlv_version_id'||':'||p_dlv_version_id,3) ;
1690 pa_debug.write(g_module_name,'p_project_id'||':'||p_project_id,3) ;
1691 END IF;
1692
1693 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1694 FND_MSG_PUB.initialize;
1695 END IF;
1696
1697 IF (p_commit = FND_API.G_TRUE) THEN
1698 savepoint DELETE_DELIVERABLE_SP ;
1699 END IF;
1700
1701 IF l_debug_mode = 'Y' THEN
1702 pa_debug.g_err_stage:= 'Validating Input parameters';
1703 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1704 END IF;
1705
1706 IF ( p_dlv_element_id IS NULL OR p_dlv_version_id IS NULL )
1707 THEN
1708 IF l_debug_mode = 'Y' THEN
1709 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
1710 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1711 END IF;
1712
1713 x_return_status := FND_API.G_RET_STS_ERROR;
1714 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1715 ,p_msg_name => 'PA_INV_PARAM_PASSED');
1716 RAISE Invalid_Arg_Exc_Dlv;
1717 END IF;
1718
1719 -- Lock the record before performing deletion
1720 BEGIN
1721 select 'x' into l_dummy
1722 from PA_PROJ_ELEMENTS
1723 where proj_element_id = p_dlv_element_id
1724 and record_version_number = decode(p_calling_module, 'AMG', record_version_number,p_rec_ver_number)
1725 for update of record_version_number NOWAIT;
1726 EXCEPTION
1727 WHEN TIMEOUT_ON_RESOURCE THEN
1728 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1729 WHEN NO_DATA_FOUND THEN
1730 l_msg_data := 'PA_XC_RECORD_CHANGED';
1731 WHEN OTHERS THEN
1732 IF SQLCODE = -54 then
1733 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1734 ELSE
1735 raise;
1736 END IF ;
1737 END ;
1738
1739 -- If locking is not successfull then
1740 -- its not worth going for further validation .
1741 IF l_msg_data IS NOT NULL THEN
1742 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1743 ,p_msg_name => l_msg_data) ;
1744 RAISE Invalid_Arg_Exc_Dlv ;
1745 END IF ;
1746
1747
1748 IF nvl(PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(p_project_id,p_dlv_element_id),'N') = 'Y' THEN
1749 x_return_status := FND_API.G_RET_STS_ERROR;
1750 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_PROGRESS_NO_DLTN') ;
1751 END IF ;
1752
1753 -- 3555460 added validation for checking shipping action is initiated or not
1754 -- if shipping is initiated, show error message for shipping initiation
1755 -- else check for ready to ship flags for shipping action
1756 -- if ready to ship flag is set for shipping action, throw error message for ready to ship
1757
1758 IF nvl(PA_DELIVERABLE_UTILS.IS_SHIPPING_INITIATED(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
1759 x_return_status := FND_API.G_RET_STS_ERROR;
1760 PA_UTILS.ADD_MESSAGE('PA','PA_DEL_DLVR_SHIP_INIT_ERR') ;
1761 ELSE
1762 IF nvl(PA_DELIVERABLE_UTILS.GET_READY_TO_SHIP_FLAG(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
1763 x_return_status := FND_API.G_RET_STS_ERROR;
1764 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_SHIP_ACTION_NO_DLTN') ;
1765 END IF ;
1766 END IF;
1767
1768 -- 3555460 added validation for checking procurement action is initiated or not
1769 -- if procurement is initiated, show error message for procurement initiation
1770 -- else check for ready to procure flags for procurement action
1771 -- if ready to ship flag is set for procurement action, throw error message for ready to procure
1772
1773
1774 IF nvl(PA_DELIVERABLE_UTILS.IS_PROCUREMENT_INITIATED(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
1775 x_return_status := FND_API.G_RET_STS_ERROR;
1776 PA_UTILS.ADD_MESSAGE('PA','PA_DEL_DLVR_PROC_INIT_ERR') ;
1777 ELSE
1778 IF nvl(PA_DELIVERABLE_UTILS.GET_READY_TO_PROC_FLAG(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
1779 x_return_status := FND_API.G_RET_STS_ERROR;
1780 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_PROC_ACTION_NO_DLTN') ;
1781 END IF ;
1782 END IF;
1783
1784 -- 3555460 added validation for checking billing event processed
1785 -- if billing function exists,
1786 -- if billing event is processed, show error message for billing event process
1787 -- else show error message for billing function exists
1788 -- end if
1789
1790 IF nvl(PA_DELIVERABLE_UTILS.IS_BILLING_FUNCTION_EXISTS(p_project_id,p_dlv_element_id),'N') = 'Y' THEN
1791 IF nvl(PA_DELIVERABLE_UTILS.IS_BILLING_EVENT_PROCESSED(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
1792 x_return_status := FND_API.G_RET_STS_ERROR;
1793 PA_UTILS.ADD_MESSAGE('PA','PA_DEL_DLVR_BILL_PROC_ERR') ;
1794 ELSE
1795 x_return_status := FND_API.G_RET_STS_ERROR;
1796 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_BILLING_FUNC_NO_DLTN') ;
1797 END IF;
1798 END IF ;
1799
1800 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1801 RAISE FND_API.G_EXC_ERROR;
1802 END IF;
1803
1804 -- 3733321 Added code to retrieve deliverable deliverable type
1805
1806 IF (p_calling_module = 'AMG') THEN
1807
1808 SELECT Pa_Deliverable_Utils.IS_Dlvr_Item_Based(element_version_id)
1809 INTO l_item_dlv
1810 FROM Pa_Proj_Element_Versions
1811 WHERE proj_element_id = p_dlv_element_id
1812 AND project_id = p_project_id;
1813
1814 END IF;
1815
1816 --Here we are deleting the TASK TO DELIVERABLE Relationship
1817 DELETE FROM PA_OBJECT_RELATIONSHIPS
1818 WHERE OBJECT_ID_TO2 = p_dlv_element_id
1819 AND object_type_from = 'PA_TASKS' /* Included these 4 additional Clauses for Performance Bug # 3614361*/
1820 AND object_type_to = 'PA_DELIVERABLES'
1821 AND relationship_type = 'A'
1822 AND RELATIONSHIP_SUBTYPE ='TASK_TO_DELIVERABLE';
1823
1824 -- 3946997 Added code to delete task assingment to deliverable association
1825
1826 DELETE FROM PA_OBJECT_RELATIONSHIPS
1827 WHERE OBJECT_ID_TO2 = p_dlv_element_id
1828 AND object_type_from = 'PA_ASSIGNMENTS'
1829 AND object_type_to = 'PA_DELIVERABLES'
1830 AND relationship_type = 'A'
1831 AND RELATIONSHIP_SUBTYPE ='ASSIGNMENT_TO_DELIVERABLE';
1832
1833 -- 3946997 end
1834
1835 -- 3749451 added below code to delete structure to deliverable association , when deliverable is deleted
1836 DELETE FROM PA_OBJECT_RELATIONSHIPS
1837 WHERE OBJECT_ID_TO2 = p_dlv_element_id
1838 AND object_type_from = 'PA_STRUCTURES'
1839 AND object_type_to = 'PA_DELIVERABLES'
1840 AND relationship_type = 'S'
1841 AND RELATIONSHIP_SUBTYPE ='STRUCTURE_TO_DELIVERABLE';
1842
1843 --The following delete Statements remove the deliverable details
1844 DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
1845 WHERE ELEMENT_VERSION_ID = p_dlv_version_id ;
1846
1847 DELETE FROM PA_PROJ_ELEMENT_VERSIONS
1848 WHERE ELEMENT_VERSION_ID = p_dlv_version_id ;
1849
1850 DELETE FROM PA_PROJ_ELEMENTS
1851 WHERE PROJ_ELEMENT_ID = p_dlv_element_id ;
1852
1853 IF l_debug_mode = 'Y' THEN
1854 Pa_Debug.WRITE(g_module_name,' After deleting records from Project table, deleting OKE records['||x_return_status||']', 3);
1855 END IF;
1856
1857 --Start Bug 3538320 <<Included by avaithia on 29-Mar-2004>>
1858 --Delete the Attachment records of the deliverable
1859
1860 IF nvl(PA_DELIVERABLE_UTILS.IS_DLV_DOC_DEFINED(p_dlv_element_id,p_dlv_version_id),'N')='Y' THEN
1861 fnd_attached_documents2_pkg.delete_attachments
1862 (X_entity_name => 'PA_DLVR_DOC_ATTACH',
1863 X_pk1_value => to_char(p_dlv_version_id),
1864 X_delete_document_flag => 'Y');
1865
1866 END IF ;
1867
1868 fnd_attached_documents2_pkg.delete_attachments
1869 (X_entity_name => 'PA_DVLR_ATTACH',
1870 X_pk1_value => to_char(p_dlv_version_id),
1871 X_delete_document_flag => 'Y');
1872
1873 --End Bug 3538320
1874
1875 --Start Bug 3431156
1876 -- delete the deliverable from OKE table
1877 IF (p_calling_module = 'AMG') THEN
1878
1879 -- 3733321 added code to retrieve master inventory org id
1880
1881 PA_RESOURCE_UTILS1.Return_Material_Class_Id
1882 (
1883 x_material_class_id => l_master_inv_org_id
1884 ,x_return_status => l_return_status
1885 ,x_msg_data => l_msg_data
1886 ,x_msg_count => l_msg_count
1887 );
1888
1889 -- 3733321 changed from p_dlv_element_id to p_dlv_version_id
1890 -- because oke expects deliverable version id
1891
1892 l_dlv_rec.pa_deliverable_id := p_dlv_version_id ;
1893 l_dlv_rec.project_id := p_project_id ;
1894
1895 -- 3733321 passing l_item_dlv as p_item_dlv , earlier it was passed as null
1896 -- passing l_master_inv_org_id as p_master_inv_org_id , earlier it was passed as 0
1897
1898 oke_amg_grp.manage_dlv
1899 ( p_api_version => p_api_version
1900 , p_init_msg_list => p_init_msg_list
1901 , p_commit => FND_API.G_FALSE
1902 , p_action => 'DELETE'
1903 , p_item_dlv => l_item_dlv
1904 , p_master_inv_org_id => l_master_inv_org_id
1905 , p_dlv_rec => l_dlv_rec
1906 , x_return_status => x_return_status
1907 , x_msg_data => x_msg_data
1908 , x_msg_count => x_msg_count
1909 );
1910
1911 ELSE
1912 x_return_status := FND_API.G_RET_STS_SUCCESS;
1913 OKE_DELIVERABLE_UTILS_PUB.DELETE_DELIVERABLE
1914 ( P_DELIVERABLE_ID => p_dlv_version_id
1915 , X_Return_Status => x_return_status
1916 , X_Msg_Count => x_msg_count
1917 , X_Msg_Data => x_msg_data
1918 ) ;
1919 END IF;
1920
1921 IF l_debug_mode = 'Y' THEN
1922 Pa_Debug.WRITE(g_module_name,' Returned from oke_amg_grp.manage_dlv['||x_return_status||']', 3);
1923 END IF;
1924
1925 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1928 RAISE FND_API.G_EXC_ERROR;
1929 END IF;
1930
1931 -- delete the deliverable actions which are associated with this deliverable
1932 -- and delete the action's association with the deliverable
1933
1934 --Included on 12-Feb-2004 by avaithia <<Start>>
1935
1936 DELETE FROM PA_OBJECT_RELATIONSHIPS
1937 WHERE OBJECT_ID_FROM2 = p_dlv_element_id
1938 RETURNING object_id_to2,object_id_to1
1939 BULK COLLECT INTO l_action_element_id_tbl,l_action_version_id_tbl;
1940
1941 --The nvl check has been included by avaithia on 29-Mar-2004
1942 IF nvl(l_action_element_id_tbl.LAST,0) > 0 THEN
1943 FORALL i IN l_action_element_id_tbl.FIRST..l_action_element_id_tbl.LAST
1944 DELETE FROM PA_PROJ_ELEMENTS
1945 WHERE PROJ_ELEMENT_ID = l_action_element_id_tbl(i);
1946
1947 FORALL i IN l_action_version_id_tbl.FIRST..l_action_version_id_tbl.LAST
1948 DELETE FROM PA_PROJ_ELEMENT_VERSIONS
1949 WHERE ELEMENT_VERSION_ID = l_action_version_id_tbl(i) ;
1950
1951
1952 FORALL i IN l_action_version_id_tbl.FIRST..l_action_version_id_tbl.LAST
1953 DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
1954 WHERE ELEMENT_VERSION_ID = l_action_version_id_tbl(i) ;
1955 END IF ;
1956
1957 --<<End>>Included on 12-Feb-2004 by avaithia
1958
1959 --End Bug 3431156
1960
1961 IF l_debug_mode = 'Y' THEN
1962 pa_debug.g_err_stage:= 'Exiting DELETE_DELIVERABLE' ;
1963 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1964 pa_debug.reset_curr_function;
1965 END IF;
1966
1967 EXCEPTION
1968 WHEN Invalid_Arg_Exc_Dlv THEN
1969 x_return_status := FND_API.G_RET_STS_ERROR;
1970 l_msg_count := FND_MSG_PUB.count_msg;
1971
1972 IF p_commit = FND_API.G_TRUE THEN
1973 ROLLBACK TO DELETE_DELIVERABLE_SP;
1974 END IF;
1975
1976 IF l_debug_mode = 'Y' THEN
1977 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DELIVERABLE';
1978 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1979 END IF;
1980
1981 IF l_msg_count = 1 THEN
1982 PA_INTERFACE_UTILS_PUB.get_messages
1983 (p_encoded => FND_API.G_FALSE,
1984 p_msg_index => 1,
1985 p_msg_count => l_msg_count,
1986 p_msg_data => l_msg_data,
1987 p_data => l_data,
1988 p_msg_index_out => l_msg_index_out);
1989 x_msg_data := l_data;
1990 x_msg_count := l_msg_count;
1991 ELSE
1992 x_msg_count := l_msg_count;
1993 END IF;
1994 IF l_debug_mode = 'Y' THEN
1995 pa_debug.reset_curr_function;
1996 END IF ;
1997 RETURN;
1998
1999 WHEN FND_API.G_EXC_ERROR THEN
2000 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2001 l_msg_count := Fnd_Msg_Pub.count_msg;
2002
2003 IF p_commit = FND_API.G_TRUE THEN
2004 ROLLBACK TO DELETE_DELIVERABLE_SP;
2005 END IF;
2006
2007 IF l_msg_count = 1 AND x_msg_data IS NULL
2008 THEN
2009 Pa_Interface_Utils_Pub.get_messages
2010 ( p_encoded => Fnd_Api.G_FALSE
2011 , p_msg_index => 1
2012 , p_msg_count => l_msg_count
2013 , p_msg_data => l_msg_data
2014 , p_data => l_data
2015 , p_msg_index_out => l_msg_index_out);
2016 x_msg_data := l_data;
2017 x_msg_count := l_msg_count;
2018 ELSE
2019 x_msg_count := l_msg_count;
2020 END IF;
2021
2022 IF l_debug_mode = 'Y' THEN
2023 pa_debug.write( g_module_name,'DELETE_DELIVERABLE: G_EXC_ERROR msg_count' ||l_msg_count ,5);
2024 Pa_Debug.reset_curr_function;
2025 END IF;
2026
2027 WHEN OTHERS THEN
2028 IF p_commit = FND_API.G_TRUE THEN
2029 ROLLBACK TO DELETE_DELIVERABLE_SP;
2030 END IF;
2031
2032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2033 x_msg_count := 1;
2034 x_msg_data := SQLERRM;
2035
2036 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
2037 ,p_procedure_name => 'DELETE_DELIVERABLE');
2038
2039 IF p_debug_mode = 'Y' THEN
2040 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2041 pa_debug.write('DELETE_DELIVERABLE: ' || g_module_name,pa_debug.g_err_stage,5);
2042 pa_debug.reset_curr_function;
2043 END IF;
2044 RAISE;
2045 END delete_deliverable ;
2046
2047 -- SubProgram : DELETE_DLV_TASK_ASSOCIATION
2048 -- Type : PROCEDURE
2049 -- Purpose : Private API to Delete Task - Deliverable Association
2050 -- Note :
2051 -- Assumptions : None
2052 -- Parameter IN/OUT Type Required Description and Purpose
2053 -- --------------------------- --------- ---------- --------- ---------------------------
2054 -- p_api_version IN NUMBER N Standard Parameter
2055 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
2056 -- p_commit IN VARCHAR2 N Standard Parameter
2057 -- p_validate_only IN VARCHAR2 N Standard Parameter
2058 -- p_validation_level IN NUMBER N Standard Parameter
2059 -- p_calling_module IN VARCHAR2 N Standard Parameter
2060 -- p_debug_mode IN VARCHAR2 N Standard Parameter
2061 -- p_max_msg_count IN NUMBER N Standard Parameter
2062 -- p_task_element_id IN NUMBER N Task Element Id
2063 -- p_task_version_id IN NUMBER N Task Version Id
2064 -- p_dlv_element_id IN NUMBER N Deliverable Element Id
2065 -- p_dlv_version_id IN NUMBER N Deliverable Version Id
2066 -- p_object_relationship_id IN NUMBER N Object Relationship Id
2067 -- p_obj_rec_ver_number IN NUMBER N Record Version NUmber
2068 -- p_calling_context IN VARCHAR2 Y Calling Context - TASKS Or DELIVERABLES
2069 -- p_project_id IN NUMBER N Project Id
2070 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
2071 -- x_msg_count OUT NUMBER N Standard Out Parameter
2072 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
2073
2074 PROCEDURE DELETE_DLV_TASK_ASSOCIATION
2075 (p_api_version IN NUMBER :=1.0
2076 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2077 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2078 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2079 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2080 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2081 ,p_debug_mode IN VARCHAR2 :='N'
2082 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2083 ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
2084 ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
2085 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
2086 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
2087 ,p_object_relationship_id IN pa_object_relationships.object_relationship_id%TYPE
2088 ,p_obj_rec_ver_number IN pa_object_relationships.record_version_number%TYPE
2089 ,p_project_id IN pa_projects_all.project_id%TYPE
2090 ,p_calling_context IN VARCHAR2 -- Bug 3555460
2091 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2092 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2093 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2094 )
2095 IS
2096 l_debug_mode VARCHAR2(10);
2097 l_msg_count NUMBER ;
2098 l_data VARCHAR2(2000);
2099 l_msg_data VARCHAR2(2000);
2100 l_msg_index_out NUMBER;
2101 l_disassociation_allowed VARCHAR2(1) := 'Y' ;
2102
2103 BEGIN
2104
2105 x_msg_count := 0;
2106 x_return_status := FND_API.G_RET_STS_SUCCESS;
2107 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2108
2109 IF l_debug_mode = 'Y' THEN
2110 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_TASK_ASSOCIATION',
2111 p_debug_mode => l_debug_mode );
2112 pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
2113 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2114 END IF;
2115
2116 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2117 FND_MSG_PUB.initialize;
2118 END IF;
2119
2120 IF (p_commit = FND_API.G_TRUE) THEN
2121 savepoint DEL_DLV_TASK_ASSOCIATION_SP ;
2122 END IF;
2123
2124 -- 3888280 start
2125 -- added existing code in new if condition , which checks for calling context
2126 -- IS_DISASSOCIATION_ALLOWED should not be called for deleting TA to DLVR association
2127 -- Calling above api if calling context is not TASK_ASSIGNMENT
2128
2129 IF p_calling_context <> 'TASK_ASSIGNMENT' THEN
2130
2131 -- Not doing any explicit locking as it is taken care
2132 -- by PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW
2133
2134 -- Call VALIDATE_DISASSOCIATION_ALLOWED to check whether
2135 -- disassociation is allowed or not. This API will also
2136 -- populate the error stack with proper error message if
2137 -- disassociation is not allowed .
2138
2139 IF l_debug_mode = 'Y' THEN
2140 pa_debug.g_err_stage:= 'Call IS_DISASSOCIATION_ALLOWED ';
2141 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2142 END IF ;
2143
2144 PA_DELIVERABLE_PVT.IS_DISASSOCIATION_ALLOWED
2145 (p_api_version => p_api_version
2146 ,p_init_msg_list => FND_API.G_FALSE
2147 ,p_commit => p_commit
2148 ,p_validate_only => p_validate_only
2149 ,p_validation_level => p_validation_level
2150 ,p_calling_module => p_calling_module
2151 ,p_debug_mode => p_debug_mode
2152 ,p_max_msg_count => p_max_msg_count
2153 ,p_task_element_id => p_task_element_id
2154 ,p_task_version_id => p_task_version_id
2155 ,p_dlv_element_id => p_dlv_element_id
2156 ,p_dlv_version_id => p_dlv_version_id
2157 ,p_project_id => p_project_id
2158 ,p_calling_context => p_calling_context -- Bug 3555460
2159 ,x_disassociation_allowed => l_disassociation_allowed
2160 ,x_return_status => x_return_status
2161 ,x_msg_count => x_msg_count
2162 ,x_msg_data => x_msg_data
2163 ) ;
2164
2165 IF l_debug_mode = 'Y' THEN
2166 pa_debug.g_err_stage:= 'l_disassociation_allowed is'||l_disassociation_allowed;
2167 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2168 END IF ;
2169
2170 IF l_debug_mode = 'Y' THEN
2171 pa_debug.g_err_stage:= 'x_return_status is'|| x_return_status;
2172 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2173 END IF ;
2174
2175
2176 IF (nvl(l_disassociation_allowed,'Y') = 'N' OR x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2177 RAISE Invalid_Arg_Exc_Dlv ;
2178 END IF ;
2179 ELSE
2180
2181 IF l_debug_mode = 'Y' THEN
2182 pa_debug.g_err_stage:= ' No Validation Required For TA To Dlvr Deletion ' ;
2183 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2184 END IF ;
2185
2186 END IF;
2187
2188 -- 3888280 end
2189
2190 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW
2191 ( p_object_relationship_id => p_object_relationship_id
2192 ,p_object_type_from => NULL
2193 ,p_object_id_from1 => NULL
2194 ,p_object_id_from2 => NULL
2195 ,p_object_id_from3 => NULL
2196 ,p_object_id_from4 => NULL
2197 ,p_object_id_from5 => NULL
2198 ,p_object_type_to => NULL
2199 ,p_object_id_to1 => NULL
2200 ,p_object_id_to2 => NULL
2201 ,p_object_id_to3 => NULL
2202 ,p_object_id_to4 => NULL
2203 ,p_object_id_to5 => NULL
2204 ,p_record_version_number => p_obj_rec_ver_number
2205 ,p_pm_product_code => NULL
2206 ,x_return_status => x_return_status
2207 );
2208
2209 IF l_debug_mode = 'Y' THEN
2210 pa_debug.g_err_stage:= 'x_return_status is'|| x_return_status;
2211 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2212 END IF ;
2213
2214 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2215 RAISE Invalid_Arg_Exc_Dlv ;
2216 END IF ;
2217
2218 IF l_debug_mode = 'Y' THEN
2219 pa_debug.g_err_stage:= 'Exiting DELETE_DLV_TASK_ASSOCIATION' ;
2220 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2221 pa_debug.reset_curr_function;
2222 END IF;
2223
2224 EXCEPTION
2225 WHEN Invalid_Arg_Exc_Dlv THEN
2226 x_return_status := FND_API.G_RET_STS_ERROR;
2227 l_msg_count := FND_MSG_PUB.count_msg;
2228
2229 IF (p_commit = FND_API.G_TRUE) THEN
2230 ROLLBACK TO DEL_DLV_TASK_ASSOCIATION_SP;
2231 END IF ;
2232
2233 IF l_debug_mode = 'Y' THEN
2234 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_TASK_ASSOCIATION';
2235 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2236 END IF;
2237
2238 IF l_msg_count = 1 THEN
2239 PA_INTERFACE_UTILS_PUB.get_messages
2240 (p_encoded => FND_API.G_TRUE,
2241 p_msg_index => 1,
2242 p_msg_count => l_msg_count,
2243 p_msg_data => l_msg_data,
2244 p_data => l_data,
2245 p_msg_index_out => l_msg_index_out);
2246 x_msg_data := l_data;
2247 x_msg_count := l_msg_count;
2248 ELSE
2249 x_msg_count := l_msg_count;
2250 END IF;
2251 IF l_debug_mode = 'Y' THEN
2252 pa_debug.reset_curr_function;
2253 END IF ;
2254 RETURN;
2255 WHEN OTHERS THEN
2256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2257 x_msg_count := 1;
2258 x_msg_data := SQLERRM;
2259
2260 IF (p_commit = FND_API.G_TRUE) THEN
2261 ROLLBACK TO DEL_DLV_TASK_ASSOCIATION_SP;
2262 END IF ;
2263
2264 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
2265 ,p_procedure_name => 'DELETE_DLV_TASK_ASSOCIATION');
2266
2267 IF p_debug_mode = 'Y' THEN
2268 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2269 pa_debug.write('DELETE_DLV_TASK_ASSOCIATION: ' || g_module_name,pa_debug.g_err_stage,5);
2270 pa_debug.reset_curr_function;
2271 END IF;
2272 RAISE;
2273 END DELETE_DLV_TASK_ASSOCIATION ;
2274
2275 -- SubProgram : IS_DISASSOCIATION_ALLOWED
2276 -- Type : PROCEDURE
2277 -- Purpose : This API will check whether disassociation is allowed
2278 -- Note :
2279 -- Assumptions : None
2280 -- Parameter IN/OUT Type Required Description and Purpose
2281 -- -------------------------- --------- ---------- --------- ------------------------
2282 -- p_api_version IN NUMBER N Standard Parameter
2283 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
2284 -- p_commit IN VARCHAR2 N Standard Parameter
2285 -- p_validate_only IN VARCHAR2 N Standard Parameter
2286 -- p_validation_level IN NUMBER N Standard Parameter
2287 -- p_calling_module IN VARCHAR2 N Standard Parameter
2288 -- p_debug_mode IN VARCHAR2 N Standard Parameter
2289 -- p_max_msg_count IN NUMBER N Standard Parameter
2290 -- p_task_element_id IN NUMBER N Task Element Id
2291 -- p_task_version_id IN NUMBER N Task Version Id
2292 -- p_dlv_element_id IN NUMBER N Deliverable Element Id
2293 -- p_dlv_version_id IN NUMBER N Deliverable Version Id
2294 -- p_project_id IN NUMBER N Project Id
2295 -- p_calling_context IN VARCHAR2 Y Calling Context -TASKS Or DELIVERABLES
2296 -- x_disassociation_allowed OUT VARCHAR2
2297 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
2298 -- x_msg_count OUT NUMBER N Standard Out Parameter
2299 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
2300
2301 PROCEDURE IS_DISASSOCIATION_ALLOWED
2302 (p_api_version IN NUMBER :=1.0
2303 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2304 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2305 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2306 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2307 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2308 ,p_debug_mode IN VARCHAR2 :='N'
2309 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2310 ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
2311 ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
2312 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
2313 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
2314 ,p_project_id IN pa_projects_all.project_id%TYPE
2315 ,p_calling_context IN VARCHAR2 -- Bug 3555460
2316 ,x_disassociation_allowed OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2317 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2318 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2319 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2320 )
2321 IS
2322 BEGIN
2323 x_msg_count := 0;
2324 x_return_status := FND_API.G_RET_STS_SUCCESS;
2325
2326 IF p_debug_mode = 'Y' THEN
2327 pa_debug.set_curr_function( p_function => 'IS_DISASSOCIATION_ALLOWED',
2328 p_debug_mode => p_debug_mode );
2329 pa_debug.g_err_stage:= 'Inside IS_DISASSOCIATION_ALLOWED ,Calling Context is ' || p_calling_context;
2330 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2331 END IF;
2332
2333 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2334 FND_MSG_PUB.initialize;
2335 END IF;
2336
2337
2338 IF p_task_element_id IS NOT NULL THEN
2339 IF nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_task_element_id),'X') = g_deliverable_based THEN
2340
2341 -- Bug 3651781 While deleting association between a deliverable based task and a deliverable ,we have to
2342 -- Look for 'PUBLISHED' progress records ,NOT mere existence of progress records
2343
2344 -- IF nvl(PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(p_project_id,p_dlv_element_id),'N') = 'Y' THEN
2345 IF nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(p_project_id,p_dlv_element_id),'N') = 'Y' THEN
2346 IF p_calling_context = 'TASKS' THEN -- Bug 3555460 Calling context newly introduced
2347 PA_UTILS.ADD_MESSAGE('PA','PA_TASK_PROGRESS_NO_DISASCN') ;
2348 ELSIF p_calling_context = 'DELIVERABLES' THEN
2349 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_PROGRESS_NO_DISASCN') ;
2350 END IF ;
2351 x_return_status := FND_API.G_RET_STS_ERROR;
2352 x_disassociation_allowed := 'N';
2353 END IF ;
2354 END IF ;
2355 END IF ;
2356
2357 --Bug 3555460 While deleting a task to deliverable association,Only check that has to be made is
2358 --If the workplan task is deliverable based, and deliverable associated to the task has progress records
2359 --So,Rest of the validations need to be commented out .
2360 /*IF nvl(PA_DELIVERABLE_UTILS.GET_READY_TO_SHIP_FLAG(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
2361 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_SHIP_ACTION_NO_DISASCN') ;
2362 x_return_status := FND_API.G_RET_STS_ERROR;
2363 x_disassociation_allowed := 'N';
2364 END IF ;
2365
2366 IF nvl(PA_DELIVERABLE_UTILS.GET_READY_TO_PROC_FLAG(p_dlv_element_id,p_dlv_version_id),'N') = 'Y' THEN
2367 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_PROC_ACTION_NO_DISASCN') ;
2368 x_return_status := FND_API.G_RET_STS_ERROR;
2369 x_disassociation_allowed := 'N';
2370 END IF ;
2371
2372 IF nvl(PA_DELIVERABLE_UTILS.IS_BILLING_FUNCTION_EXISTS(p_project_id,p_dlv_element_id),'N') = 'Y' THEN
2373 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_BILLING_FUNC_NO_DISASCN') ;
2374 x_return_status := FND_API.G_RET_STS_ERROR;
2375 x_disassociation_allowed := 'N';
2376 END IF ;*/
2377
2378 IF p_debug_mode = 'Y' THEN --Added for bug 4945876
2379 pa_debug.reset_curr_function;
2380 END IF ;
2381
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2385 x_msg_count := 1;
2386 x_msg_data := SQLERRM;
2387
2388 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PUB'
2389 ,p_procedure_name => 'IS_DISASSOCIATION_ALLOWED');
2390
2391 IF p_debug_mode = 'Y' THEN
2392 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2393 pa_debug.write('IS_DISASSOCIATION_ALLOWED: ' || g_module_name,pa_debug.g_err_stage,5);
2394 pa_debug.reset_curr_function;
2395 END IF;
2396 RAISE;
2397 END IS_DISASSOCIATION_ALLOWED;
2398
2399 -- SubProgram : CREATE_ASSOCIATIONS_IN_BULK
2400 -- Type : PROCEDURE
2401 -- Purpose : Private API to Create associations
2402 -- Note :
2403 -- Assumptions : None
2404 -- Parameter IN/OUT Type Required Description and Purpose
2405 -- --------------------------- --------- ---------- --------- ---------------------------
2406 -- p_api_version IN NUMBER N Standard Parameter
2407 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
2408 -- p_commit IN VARCHAR2 N Standard Parameter
2409 -- p_validate_only IN VARCHAR2 N Standard Parameter
2410 -- p_validation_level IN NUMBER N Standard Parameter
2411 -- p_calling_module IN VARCHAR2 N Standard Parameter
2412 -- p_debug_mode IN VARCHAR2 N Standard Parameter
2413 -- p_max_msg_count IN NUMBER N Standard Parameter
2414 -- p_element_id_tbl IN PLSQL Table N PLSQL table of Dlv Element Id
2415 -- p_version_id_tbl IN PLSQL Table N PLSQL table of Dlv Version Id
2416 -- p_element_name_tbl IN PLSQL Table N PLSQL Table of Dlv. Name
2417 -- p_element_number_tbl IN PLSQL Table N PLSQL Table of Dlv. Number
2418 -- p_task_or_dlv_elt_id IN NUMBER Y Task or deliverable element id
2419 -- p_task_or_dlv_elt_id IN NUMBER Y Task or deliverable version id
2420 -- p_project_id IN NUMBER N Project Id
2421 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
2422 -- x_msg_count OUT NUMBER N Standard Out Parameter
2423 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
2424
2425 PROCEDURE CREATE_ASSOCIATIONS_IN_BULK
2426 (p_api_version IN NUMBER :=1.0
2427 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2428 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2429 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2430 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2431 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2432 ,p_debug_mode IN VARCHAR2 :='N'
2433 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2434 ,p_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
2435 ,p_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
2436 ,p_element_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
2437 ,p_element_number_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
2438 ,p_task_or_dlv_elt_id IN NUMBER
2439 ,p_task_or_dlv_ver_id IN NUMBER
2440 ,p_project_id IN pa_projects_all.project_id%TYPE
2441 ,p_task_or_dlv IN VARCHAR2 := 'PA_TASKS'
2442 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2443 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2444 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2445 )
2446 IS
2447 l_msg_count NUMBER ;
2448 l_data VARCHAR2(2000);
2449 l_msg_data VARCHAR2(2000);
2450 l_msg_index_out NUMBER;
2451 l_err_message fnd_new_messages.message_text%TYPE ;
2452 l_prog_enabled_dlv_count NUMBER ;
2453 l_dlv_based_task_count NUMBER ;
2454
2455 BEGIN
2456
2457 x_msg_count := 0;
2458 x_return_status := FND_API.G_RET_STS_SUCCESS;
2459
2460 IF p_debug_mode = 'Y' THEN
2461 PA_DEBUG.set_curr_function( p_function => 'CREATE_ASSOCIATIONS_IN_BULK'
2462 ,p_debug_mode => p_debug_mode );
2463 pa_debug.g_err_stage:= 'Inside CREATE_ASSOCIATIONS_IN_BULK ';
2464 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2465 END IF;
2466
2467 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2468 FND_MSG_PUB.initialize;
2469 END IF;
2470
2471 -- Perform following validation
2472
2473 -- This is required when coming from Associate Deliverables Page
2474 -- 1. If task is is deliverable based then
2475 -- 1.1 If deliverable is associated to Deliverable Based Task Then
2476 -- ASSOCIATION IS NOT POSSIBLE
2477
2478 -- This is required when coming from Associate Tasks Page
2479 -- 2. If deliverable is attached to deliverable based task then
2480 -- 1.1.1 If tas kis deliverable based then
2481 -- ASSOCIATION IS NOT POSSIBLE
2482 -- 3. else
2483 -- 1.2.1 If multiple Deliverables task is selected then
2484 -- ASSOCIATION IS NOT POSSIBLE
2485 -- Following validation has to be performed from ASSOCIATE TASK
2486 -- page as well as ASSOCIATE DELIVERABLE page
2487
2488 IF p_task_or_dlv = 'PA_TASKS' THEN
2489
2490 IF nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_task_or_dlv_elt_id),'X') = g_deliverable_based THEN
2491 -- Initialize the error message beforehand so as to avoid unnecessary
2492 -- call of FND_MESSAGE.GET_STRING API again and again in LOOP
2493 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_DLV_ASSCN_ERR') ;
2494 IF nvl(p_element_id_tbl.LAST,0)>0 THEN
2495 --3614361 Explicitly mentioned param name
2496 FOR i in p_element_id_tbl.FIRST..p_element_id_tbl.LAST LOOP
2497 IF nvl(PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(p_dlv_element_id => p_element_id_tbl(i)),'X') = 'Y' THEN
2498 PA_UTILS.ADD_MESSAGE
2499 (p_app_short_name => 'PA',
2500 p_msg_name => 'PA_PS_TASK_NAME_NUM_ERR',
2501 p_token1 => 'TASK_NAME',
2502 p_value1 => p_element_name_tbl(i),
2503 p_token2 => 'TASK_NUMBER',
2504 p_value2 => p_element_number_tbl(i),
2505 p_token3 => 'MESSAGE',
2506 p_value3 => l_err_message
2507 );
2508
2509 END IF ;
2510 END LOOP ;
2511 END IF ;
2512 END IF ;
2513 ELSE -- p_task_or_dlv = 'TASKS'
2514 --3614361 Explicitly mentioned param name
2515 IF nvl(PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(p_dlv_element_id=>p_task_or_dlv_elt_id),'X') = 'Y' THEN
2516
2517 -- Initialize the error message beforehand so as to avoid unnecessary
2518 -- call of FND_MESSAGE.GET_STRING API again and again in LOOP
2519 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_TASK_ASSCN_ERR') ;
2520
2521 IF nvl(p_element_id_tbl.LAST,0) > 0 THEN
2522 FOR i in p_element_id_tbl.FIRST..p_element_id_tbl.LAST LOOP
2523 IF nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_element_id_tbl(i)),'X') = g_deliverable_based THEN
2524 PA_UTILS.ADD_MESSAGE
2525 (p_app_short_name => 'PA',
2526 p_msg_name => 'PA_PS_TASK_NAME_NUM_ERR',
2527 p_token1 => 'TASK_NAME',
2528 p_value1 => p_element_name_tbl(i),
2529 p_token2 => 'TASK_NUMBER',
2530 p_value2 => p_element_number_tbl(i),
2531 p_token3 => 'MESSAGE',
2532 p_value3 => l_err_message
2533 );
2534
2535 END IF ;
2536 END LOOP ;
2537 END IF ;
2538 ELSE
2539 IF nvl(p_element_id_tbl.LAST,0)>0 THEN
2540 l_prog_enabled_dlv_count := 0 ;
2541 FOR i in p_element_id_tbl.FIRST..p_element_id_tbl.LAST LOOP
2542 IF nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_element_id_tbl(i)),'X') = g_deliverable_based THEN
2543 -- 3625019 changed variable , initialization was done one l_prog_enabled_dlv_count and
2544 -- variable value increment is done on l_dlv_based_task_count and if condition checking is done on l_prog_enabled_dlv_count
2545 -- IF l_prog_enabled_dlv_count > 1 THEN condition will always fail
2546 l_prog_enabled_dlv_count := l_prog_enabled_dlv_count+1 ;
2547 END IF ;
2548 IF l_prog_enabled_dlv_count > 1 THEN
2549 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
2550 ,p_msg_name => 'PA_MULTI_TASK_ASSCN_ERR');
2551 EXIT ;
2552 END IF ;
2553 END LOOP ;
2554 END IF ;
2555 END IF ;
2556 END IF ;
2557
2558 x_msg_count := FND_MSG_PUB.count_msg ;
2559
2560 IF x_msg_count > 0 THEN
2561 RAISE Invalid_Arg_Exc_Dlv ;
2562 END IF ;
2563
2564 FORALL i in p_element_id_tbl.FIRST..p_element_id_tbl.LAST
2565 INSERT INTO PA_OBJECT_RELATIONSHIPS (
2566 object_relationship_id
2567 ,object_type_from
2568 ,object_id_from1
2569 ,object_type_to
2570 ,object_id_to1
2571 ,relationship_type
2572 ,created_by
2573 ,creation_date
2574 ,last_updated_by
2575 ,last_update_date
2576 ,object_id_from2
2577 ,object_id_to2
2578 ,relationship_subtype
2579 ,record_version_number
2580 ,last_update_login
2581 )
2582 VALUES
2583 (
2584 pa_object_relationships_s.nextval
2585 ,'PA_TASKS'
2586 ,decode(p_task_or_dlv,'PA_TASKS',p_task_or_dlv_ver_id,p_version_id_tbl(i))
2587 ,'PA_DELIVERABLES'
2588 ,decode(p_task_or_dlv,'PA_TASKS',p_version_id_tbl(i),p_task_or_dlv_ver_id)
2589 ,'A'
2590 ,fnd_global.user_id
2591 ,SYSDATE
2592 ,fnd_global.user_id
2593 ,SYSDATE
2594 ,decode(p_task_or_dlv,'PA_TASKS',p_task_or_dlv_elt_id,p_element_id_tbl(i))
2595 ,decode(p_task_or_dlv,'PA_TASKS',p_element_id_tbl(i),p_task_or_dlv_elt_id)
2596 ,'TASK_TO_DELIVERABLE'
2597 ,1
2598 ,fnd_global.login_id
2599 ) ;
2600
2601
2602 IF p_debug_mode = 'Y' THEN
2603 pa_debug.reset_curr_function;
2604 END IF ;
2605
2606 EXCEPTION
2607 WHEN Invalid_Arg_Exc_Dlv THEN
2608 x_return_status := FND_API.G_RET_STS_ERROR;
2609 l_msg_count := FND_MSG_PUB.count_msg;
2610 IF p_debug_mode = 'Y' THEN
2611 pa_debug.g_err_stage := 'inside invalid arg exception of CREATE_ASSOCIATIONS_IN_BULK';
2612 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2613 END IF;
2614
2615 IF x_msg_count = 1 THEN
2616 PA_INTERFACE_UTILS_PUB.get_messages
2617 (p_encoded => FND_API.G_TRUE,
2618 p_msg_index => 1,
2619 p_msg_count => l_msg_count,
2620 p_msg_data => l_msg_data,
2621 p_data => l_data,
2622 p_msg_index_out => l_msg_index_out);
2623 x_msg_data := l_data;
2624 x_msg_count := l_msg_count;
2625 ELSE
2626 x_msg_count := l_msg_count;
2627 END IF;
2628 IF p_debug_mode = 'Y' THEN
2629 pa_debug.reset_curr_function;
2630 END IF ;
2631 RETURN;
2632 WHEN OTHERS THEN
2633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2634 x_msg_count := 1;
2635 x_msg_data := SQLERRM;
2636 FND_MSG_PUB.add_exc_msg
2637 ( p_pkg_name => 'PA_DELIVERABLE_PVT'
2638 ,p_procedure_name => 'CREATE_ASSOCIATIONS_IN_BULK' );
2639 IF p_debug_mode = 'Y' THEN
2640 pa_debug.write('CREATE_ASSOCIATIONS_IN_BULK' || g_module_name,SQLERRM,4);
2641 pa_debug.write('CREATE_ASSOCIATIONS_IN_BULK' || g_module_name,pa_debug.G_Err_Stack,4);
2642 pa_debug.reset_curr_function;
2643 END IF;
2644 RAISE ;
2645 END CREATE_ASSOCIATIONS_IN_BULK ;
2646
2647
2648 -- SubProgram : CREATE_DLV_TASK_ASSOCIATION
2649 -- Type : PROCEDURE
2650 -- Purpose : Private API to Create association between task and a deliverable
2651 -- Note :
2652 -- Assumptions : None
2653 -- Parameter IN/OUT Type Required Description and Purpose
2654 -- --------------------------- --------- ---------- --------- ---------------------------
2655 -- p_api_version IN NUMBER N Standard Parameter
2656 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
2657 -- p_commit IN VARCHAR2 N Standard Parameter
2658 -- p_validate_only IN VARCHAR2 N Standard Parameter
2659 -- p_validation_level IN NUMBER N Standard Parameter
2660 -- p_calling_module IN VARCHAR2 N Standard Parameter
2661 -- p_debug_mode IN VARCHAR2 N Standard Parameter
2662 -- p_max_msg_count IN NUMBER N Standard Parameter
2663 -- p_task_element_id IN NUMBER Y Task Element Id
2664 -- p_task_version_id IN NUMBER Y Task Version Id
2665 -- p_dlv_element_id IN NUMBER Y Deliverable Element Id
2666 -- p_dlv_version_id IN NUMBER Y Deliverable Version Id
2667 -- p_project_id IN NUMBER Y Project Id
2668 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
2669 -- x_msg_count OUT NUMBER N Standard Out Parameter
2670 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
2671
2672 PROCEDURE CREATE_DLV_TASK_ASSOCIATION
2673 (p_api_version IN NUMBER :=1.0
2674 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2675 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2676 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2677 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2678 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2679 ,p_debug_mode IN VARCHAR2 :='N'
2680 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2681 ,p_calling_context IN VARCHAR2 := 'PA_TASKS'
2682 ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
2683 ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
2684 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
2685 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
2686 ,p_project_id IN pa_projects_all.project_id%TYPE
2687 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2688 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2689 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2690 )
2691 IS
2692 l_debug_mode VARCHAR2(10);
2693 l_msg_count NUMBER ;
2694 l_data VARCHAR2(2000);
2695 l_msg_data VARCHAR2(2000);
2696 l_msg_index_out NUMBER;
2697
2698 -- 3651542 added below cursor and local variables
2699
2700 CURSOR c_dlvr_task_assgnt_asscn IS
2701 SELECT obj.object_relationship_id
2702 FROM PA_OBJECT_RELATIONSHIPS obj
2703 WHERE OBJ.object_id_from2 = p_task_element_id
2704 AND OBJ.object_id_to2 = p_dlv_element_id
2705 AND OBJ.object_type_to = 'PA_DELIVERABLES'
2706 AND OBJ.object_type_from = 'PA_ASSIGNMENTS'
2707 AND OBJ.relationship_type = 'A'
2708 AND OBJ.relationship_subtype = 'ASSIGNMENT_TO_DELIVERABLE';
2709
2710 l_dummy pa_object_relationships.object_relationship_Id%TYPE;
2711
2712 is_asscn_exists VARCHAR2(1) := 'N';
2713
2714 BEGIN
2715 x_msg_count := 0;
2716 x_return_status := FND_API.G_RET_STS_SUCCESS;
2717
2718 IF p_debug_mode = 'Y' THEN
2719 PA_DEBUG.set_curr_function( p_function => 'CREATE_DLV_TASK_ASSOCIATION'
2720 ,p_debug_mode => p_debug_mode );
2721 pa_debug.g_err_stage:= 'Inside CREATE_DLV_TASK_ASSOCIATION ';
2722 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2723 END IF;
2724
2725 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2726 FND_MSG_PUB.initialize;
2727 END IF;
2728
2729 IF (p_commit = FND_API.G_TRUE) THEN
2730 SAVEPOINT CREATE_DLV_TASK_ASSOCIATION_SP ;
2731 END IF ;
2732
2733 IF ((p_task_element_id IS NULL )OR --p_task_version_id IS NULL) OR
2734 (p_dlv_element_id IS NULL OR p_dlv_version_id IS NULL ))
2735 THEN
2736 IF p_debug_mode = 'Y' THEN
2737 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
2738 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2739 END IF;
2740
2741 x_return_status := FND_API.G_RET_STS_ERROR;
2742 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
2743 ,p_msg_name => 'PA_INV_PARAM_PASSED');
2744 RAISE Invalid_Arg_Exc_Dlv;
2745 END IF ;
2746
2747 -- Perform following validation before creating association
2748 -- 1. If Deliverable is associated to deliverable based task then
2749 -- 1.1 If Task is Deliverable based then
2750 -- ASSOCIATION IS NOT ALLOWED
2751 -- 3614361 Explicitly mentioned param names
2752
2753 IF nvl(PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(p_dlv_element_id => p_dlv_element_id,p_dlv_version_id => p_dlv_version_id),'X') = 'Y'
2754 THEN
2755 IF nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_task_element_id),'X') = g_deliverable_based THEN
2756 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
2757 ,p_msg_name => 'PA_DLV_ASSCN_ERR');
2758 x_return_status := FND_API.G_RET_STS_ERROR;
2759 END IF ;
2760 END IF ;
2761
2762 IF x_return_status <>FND_API.G_RET_STS_SUCCESS
2763 THEN
2764 RAISE Invalid_Arg_Exc_Dlv ;
2765 END IF ;
2766
2767 IF p_debug_mode = 'Y' THEN
2768 pa_debug.g_err_stage:= 'Populating PA_OBJECT_RELATIONSHIPS';
2769 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2770 END IF;
2771
2772 -- 3651542 added below call to check whether task assignment to deliverable relation ship is
2773 -- existing or not
2774 -- check for calling module and calling context, if it is AMG and PA_ASSIGNMENTS resp,
2775 -- if c_dlvr_task_assgnt_asscn returns record, set is_asscn_exists lag to 'Y'
2776
2777 IF p_calling_module = 'AMG' AND p_calling_context = 'PA_ASSIGNMENTS' THEN
2778 OPEN c_dlvr_task_assgnt_asscn;
2779 FETCH c_dlvr_task_assgnt_asscn into l_dummy ;
2780 IF c_dlvr_task_assgnt_asscn%FOUND THEN
2781 is_asscn_exists := 'Y';
2782 END IF;
2783 CLOSE c_dlvr_task_assgnt_asscn;
2784 END IF;
2785
2786 -- 3651542 below if condition was used for checking task to deliverable asscn
2787 -- if it is there , do not create new association
2788 -- Moved that condition as separate code and setting is_asscn_exists flag to 'Y' if asscn is existing
2789
2790 -- here GET_DLVR_TASK_ASSCN_ID returns object relationship id of the relationship
2791 -- if it is null, i.e. relationship is not existing, is_asscn_exists will be 'N'
2792 -- if it is not null, relationship is existing, and set is_asscn_exists to 'Y'
2793
2794 -- 3651542 check association existance both in AMG and SS, removed p_calling_module = 'AMG' condition
2795 -- from below if
2796
2797 -- IF ( p_calling_module = 'AMG' AND p_calling_context = 'PA_TASKS' AND
2798 IF ( p_calling_context = 'PA_TASKS' AND
2799 (PA_DELIVERABLE_UTILS.GET_DLVR_TASK_ASSCN_ID(p_dlv_element_id,p_task_element_id) IS NOT NULL)) THEN
2800 is_asscn_exists := 'Y';
2801 END IF;
2802
2803
2804 -- 3744841 If it is AMG Context then if association doesnt exist already,then only do insert
2805 -- The Other context where this API is used is :- SELF_SERVICE
2806 -- On creating a deliverable in task details flow it should get associated
2807 -- automatically to that task (after validations)
2808
2809
2810 -- 3651542 only one of the IF condition will be satisfied and either task to dvlr association or
2811 -- task assgn to deliverable asscn existance will be checked and is_asscn_exists falg will be set to 'Y'
2812
2813 -- moved below commented code from if condition, as separate code segment above
2814 -- insert will be called if calling module is SS or if calling module is AMG and asscn does not exists
2815
2816 -- 3651542 incorporated review comments , the association existance will be checked for both AMG and SS
2817 -- removing p_calling_module = 'SELF_SERVICE' and p_calling_module = 'AMG' conditions from below if
2818 IF ( is_asscn_exists = 'N' )
2819 -- IF (p_calling_module = 'SELF_SERVICE') OR ( p_calling_module = 'AMG' AND is_asscn_exists = 'N' ) --OR
2820 -- ( p_calling_module = 'AMG' AND nvl(PA_DELIVERABLE_UTILS.GET_DLVR_TASK_ASSCN_ID(p_dlv_element_id,p_task_element_id),-99) = -99) -- 3749487 changed from 'X' to -99
2821 THEN
2822 -- Populate object relationship table
2823 INSERT INTO PA_OBJECT_RELATIONSHIPS (
2824 object_relationship_id
2825 ,object_type_from
2826 ,object_id_from1
2827 ,object_type_to
2828 ,object_id_to1
2829 ,relationship_type
2830 ,created_by
2831 ,creation_date
2832 ,last_updated_by
2833 ,last_update_date
2834 ,object_id_from2
2835 ,object_id_to2
2836 ,relationship_subtype
2837 ,record_version_number
2838 ,last_update_login
2839 )
2840 VALUES
2841 (
2842 pa_object_relationships_s.nextval
2843 ,decode(p_calling_context,'PA_TASKS','PA_TASKS','PA_ASSIGNMENTS')
2844 ,p_task_version_id
2845 ,'PA_DELIVERABLES'
2846 ,p_dlv_version_id
2847 ,'A'
2848 ,fnd_global.user_id
2849 ,SYSDATE
2850 ,fnd_global.user_id
2851 ,SYSDATE
2852 ,p_task_element_id
2853 ,p_dlv_element_id
2854 ,decode(p_calling_context,'PA_TASKS','TASK_TO_DELIVERABLE','ASSIGNMENT_TO_DELIVERABLE')
2855 ,1
2856 ,fnd_global.login_id
2857 ) ;
2858 END IF;
2859
2860 IF p_debug_mode = 'Y' THEN
2861 pa_debug.reset_curr_function;
2862 END IF ;
2863 EXCEPTION
2864 WHEN Invalid_Arg_Exc_Dlv THEN
2865 x_return_status := FND_API.G_RET_STS_ERROR;
2866 l_msg_count := FND_MSG_PUB.count_msg;
2867
2868 IF (p_commit = FND_API.G_TRUE) THEN
2869 ROLLBACK TO CREATE_DLV_TASK_ASSOCIATION_SP;
2870 END IF ;
2871
2872 IF p_debug_mode = 'Y' THEN
2873 pa_debug.g_err_stage := 'inside invalid arg exception of CREATE_DLV_TASK_ASSOCIATION';
2874 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2875 END IF;
2876
2877 IF l_msg_count = 1 THEN
2878 PA_INTERFACE_UTILS_PUB.get_messages
2879 (p_encoded => FND_API.G_TRUE,
2880 p_msg_index => 1,
2881 p_msg_count => l_msg_count,
2882 p_msg_data => l_msg_data,
2883 p_data => l_data,
2884 p_msg_index_out => l_msg_index_out);
2885 x_msg_data := l_data;
2886 x_msg_count := l_msg_count;
2887 ELSE
2888 x_msg_count := l_msg_count;
2889 END IF;
2890 IF l_debug_mode = 'Y' THEN
2891 pa_debug.reset_curr_function;
2892 END IF ;
2893 RETURN;
2894 WHEN OTHERS THEN
2895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2896 x_msg_count := 1;
2897 x_msg_data := SQLERRM;
2898
2899 IF (p_commit = FND_API.G_TRUE) THEN
2900 ROLLBACK TO CREATE_DLV_TASK_ASSOCIATION_SP;
2901 END IF ;
2902
2903 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_VT'
2904 ,p_procedure_name => 'CREATE_DLV_TASK_ASSOCIATION');
2905
2906 IF p_debug_mode = 'Y' THEN
2907 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2908 pa_debug.write('CREATE_DLV_TASK_ASSOCIATION: ' || g_module_name,pa_debug.g_err_stage,5);
2909 pa_debug.reset_curr_function;
2910 END IF;
2911 RAISE;
2912 END CREATE_DLV_TASK_ASSOCIATION ;
2913
2914 PROCEDURE DELETE_DELIVERABLE_STRUCTURE
2915 (p_api_version IN NUMBER :=1.0
2916 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2917 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2918 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2919 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2920 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2921 ,p_debug_mode IN VARCHAR2 :='N'
2922 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2923 ,p_project_id IN pa_projects_all.project_id%TYPE
2924 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2925 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2926 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2927 )
2928 IS
2929 l_msg_count NUMBER := 0;
2930 l_msg_data VARCHAR2(2000);
2931 l_data VARCHAR2(2000);
2932 l_return_status VARCHAR2(1);
2933 l_msg_index_out NUMBER ;
2934 l_proj_element_id NUMBER ;
2935 l_element_version_id NUMBER ;
2936 l_return_flag VARCHAR2(1);
2937 l_dlv_based_task_exists VARCHAR2(1);
2938
2939 --Bug # 3431156 Included by avaithia
2940 l_dlv_version_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2941 l_object_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
2942
2943 --Bug #3538320 Included by avaithia on 29-Mar-2004
2944 l_dlv_proj_elt_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
2945
2946 BEGIN
2947 l_msg_count := 0;
2948 l_return_status := FND_API.G_RET_STS_SUCCESS;
2949
2950 IF p_debug_mode = 'Y' THEN
2951 PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLE_STRUCTURE',
2952 p_debug_mode => p_debug_mode );
2953 pa_debug.g_err_stage:= 'Inside DELETE_DELIVERABLE_STRUCTURE ';
2954 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2955 END IF;
2956
2957 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2958 FND_MSG_PUB.initialize;
2959 END IF;
2960
2961 -- Validate mandatory input parameter
2962 PA_DELIVERABLE_UTILS.CHECK_DLVR_DISABLE_ALLOWED
2963 ( p_debug_mode => p_debug_mode
2964 ,p_project_id => p_project_id
2965 ,x_return_flag => l_return_flag
2966 ,x_return_status => x_return_status
2967 ,x_msg_count => x_msg_count
2968 ,x_msg_data => x_msg_data
2969 ) ;
2970
2971 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2972 RAISE Invalid_Arg_Exc_Dlv ;
2973 END IF ;
2974
2975 /* Bug 3597178 We can still go about Disabling Deliverable Structure ,Even If Deliverable Based Tasks Exist for that project
2976 l_dlv_based_task_exists := PA_DELIVERABLE_UTILS.IS_DLV_BASED_TASK_EXISTS (p_project_id);
2977 IF (l_dlv_based_task_exists = 'Y') THEN
2978 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_DLV_BASED_TASK_EXISTS');
2979 RAISE Invalid_Arg_Exc_Dlv ;
2980 END IF;
2981 */
2982 -- Delete the structure level record
2983 DELETE FROM PA_PROJ_ELEMENTS
2984 WHERE project_id = p_project_id
2985 AND object_type = 'PA_STRUCTURES'
2986 AND proj_element_id in (SELECT proj_element_id
2987 FROM pa_proj_structure_types
2988 WHERE structure_type_id = 8
2989 )
2990 RETURNING proj_element_id INTO l_proj_element_id ;
2991
2992 DELETE FROM PA_PROJ_ELEMENT_VERSIONS
2993 WHERE proj_element_id = l_proj_element_id
2994 AND object_type = 'PA_STRUCTURES'
2995 RETURNING element_version_id INTO l_element_version_id ;
2996
2997 DELETE FROM PA_PROJ_STRUCTURE_TYPES
2998 WHERE proj_element_id = l_proj_element_id ;
2999
3000 DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
3001 WHERE proj_element_id = l_proj_element_id
3002 AND project_id = p_project_id ;/* Included project_id clause for Performance Bug Fix 3614361 */
3003
3004 /*3614361 Included Delete from PA_PROJ_ELEM_VER_STRUCTURE */
3005 DELETE FROM PA_PROJ_ELEM_VER_STRUCTURE
3006 WHERE proj_element_id = l_proj_element_id
3007 AND project_id = p_project_id ;
3008
3009 /* Moved the following code to top for Performance Bug Fix 3614361
3010 BULK DELETE Approach is a better approach in this case */
3011
3012 -- Delete the deliverables,actions and the relationships
3013 DELETE FROM PA_PROJ_ELEMENT_VERSIONS
3014 WHERE project_id = p_project_id
3015 AND object_type in ('PA_DELIVERABLES','PA_ACTIONS')
3016 RETURNING ELEMENT_VERSION_ID ,OBJECT_TYPE,PROJ_ELEMENT_ID --Included Proj_element_id for Bug 3538320
3017 BULK COLLECT INTO l_dlv_version_tbl,l_object_type_tbl,l_dlv_proj_elt_tbl;
3018
3019 -- 3837025 , if there is no deliverable and action records , below code is failing and giving numeric or null value error
3020 -- Checking for the table's last value, if it is greater than zero i.e. there is deliverable or actions record in the db
3021 -- go ahead and delete the records
3022
3023 IF nvl(l_dlv_version_tbl.LAST,0)>0 THEN
3024
3025 FORALL j IN l_dlv_proj_elt_tbl.FIRST..l_dlv_proj_elt_tbl.LAST
3026 DELETE FROM PA_OBJECT_RELATIONSHIPS
3027 WHERE object_id_to2 = l_dlv_proj_elt_tbl(j)
3028 and object_type_to = 'PA_DELIVERABLES'; -- Added for perf bug# 3964586;
3029
3030 -- 3986132 Added below code to delete DELIVERABLE to ACTION association from
3031 -- pa_object_relationships table
3032
3033 FORALL j IN l_dlv_version_tbl.FIRST..l_dlv_version_tbl.LAST
3034 DELETE FROM PA_OBJECT_RELATIONSHIPS
3035 WHERE object_id_from1 = l_dlv_version_tbl(j)
3036 and object_type_from = 'PA_DELIVERABLES'
3037 and object_type_to = 'PA_ACTIONS';
3038
3039 -- 3986132 end
3040
3041 FORALL j IN l_dlv_version_tbl.FIRST..l_dlv_version_tbl.LAST
3042 DELETE FROM PA_PROJ_ELEM_VER_SCHEDULE
3043 WHERE project_id = p_project_id
3044 AND element_version_id = l_dlv_version_tbl(j);
3045
3046 FORALL j IN l_dlv_proj_elt_tbl.FIRST..l_dlv_proj_elt_tbl.LAST
3047 DELETE FROM PA_PROJ_ELEMENTS
3048 WHERE proj_element_id = l_dlv_proj_elt_tbl(j);
3049
3050 --The nvl check is needed because :Say suppose only deliverable structure has been
3051 --enabled and there are no deliverables created so far,
3052 --then nothing will be there in l_dlv_version_id_tbl
3053
3054 -- 3837025 moved below IF clause above , i.e. before the for loop of deleting records from object relationship tables
3055 --IF nvl(l_dlv_version_tbl.LAST,0)>0 THEN
3056 FOR i IN l_dlv_version_tbl.FIRST..l_dlv_version_tbl.LAST LOOP
3057
3058 --Included by avaithia on 12-Feb-2004 <<Start>> Bug # 3431156
3059 --Whenever deliverable structure is disabled we need to delete the deliverable/action
3060 --related data from OKE tables.
3061
3062 -- OKE API internally takes care of deleting the actions associated with the deliverable
3063
3064 IF l_object_type_tbl(i)= 'PA_DELIVERABLES'
3065 THEN
3066
3067 OKE_DELIVERABLE_UTILS_PUB.DELETE_DELIVERABLE
3068 ( P_DELIVERABLE_ID => l_dlv_version_tbl(i)
3069 , X_Return_Status => x_return_status
3070 , X_Msg_Count => x_msg_count
3071 , X_Msg_Data => x_msg_data
3072 ) ;
3073 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3074 RAISE Invalid_Arg_Exc_Dlv ;
3075 END IF ;
3076
3077 --Start Bug 3538320 <<Included by avaithia on 29-Mar-2004>>
3078 --Delete the Attachment records of the deliverable
3079
3080 IF nvl(PA_DELIVERABLE_UTILS.IS_DLV_DOC_DEFINED(l_dlv_proj_elt_tbl(i),l_dlv_version_tbl(i)),'N')='Y' THEN
3081 fnd_attached_documents2_pkg.delete_attachments
3082 (X_entity_name => 'PA_DLVR_DOC_ATTACH',
3083 X_pk1_value => to_char(l_dlv_version_tbl(i)),
3084 X_delete_document_flag => 'Y');
3085 END IF ;
3086
3087 fnd_attached_documents2_pkg.delete_attachments
3088 (X_entity_name => 'PA_DVLR_ATTACH',
3089 X_pk1_value => to_char(l_dlv_version_tbl(i)),
3090 X_delete_document_flag => 'Y');
3091
3092 --End Bug 3538320
3093
3094 END IF;
3095
3096 END LOOP;
3097 END IF;
3098
3099 -- <<End>> Included by avaithia on 12-Feb-2004 Bug # 3431156
3100
3101 IF p_debug_mode = 'Y' THEN
3102 pa_debug.reset_curr_function;
3103 END IF ;
3104
3105 EXCEPTION
3106 WHEN Invalid_Arg_Exc_Dlv THEN
3107 x_return_status := FND_API.G_RET_STS_ERROR;
3108 l_msg_count := FND_MSG_PUB.count_msg;
3109
3110 IF p_debug_mode = 'Y' THEN
3111 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DELIVERABLE_STRUCTURE';
3112 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
3113 END IF;
3114
3115 IF l_msg_count = 1 THEN
3116 PA_INTERFACE_UTILS_PUB.get_messages
3117 (p_encoded => FND_API.G_TRUE,
3118 p_msg_index => 1,
3119 p_msg_count => l_msg_count,
3120 p_msg_data => l_msg_data,
3121 p_data => l_data,
3122 p_msg_index_out => l_msg_index_out);
3123 x_msg_data := l_data;
3124 x_msg_count := l_msg_count;
3125 ELSE
3126 x_msg_count := l_msg_count;
3127 END IF;
3128 IF p_debug_mode = 'Y' THEN
3129 pa_debug.reset_curr_function;
3130 END IF ;
3131 RETURN;
3132 WHEN OTHERS THEN
3133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3134 x_msg_count := 1;
3135 x_msg_data := SQLERRM;
3136
3137 FND_MSG_PUB.add_exc_msg
3138 ( p_pkg_name => 'PA_DELIVERABLE_PVT'
3139 ,p_procedure_name => 'DELETE_DELIVERABLE_STRUCTURE' );
3140 IF p_debug_mode = 'Y' THEN
3141 pa_debug.write('DELETE_DELIVERABLE_STRUCTURE' || g_module_name,SQLERRM,4);
3142 pa_debug.write('DELETE_DELIVERABLE_STRUCTURE' || g_module_name,pa_debug.G_Err_Stack,4);
3143 pa_debug.reset_curr_function;
3144 END IF;
3145 RAISE ;
3146 END DELETE_DELIVERABLE_STRUCTURE ;
3147
3148 /* Proper Comments put for Bug 3906015 */
3149 -- IMPORTANT :-
3150 -- p_calling_context = 'PA_TASKS' case of this API is EXACTLY same as the contents of DELETE_DLV_ASSOCIATIONS API
3151
3152 -- What is the need for DELETE_DLV_ASSOCIATIONS API then ?
3153 -- During task->deliverable association deletion (i.e) p_calling_context = 'PA_TASKS' case
3154 -- This API DELETE_DLV_TASK_ASSCN_IN_BULK populates an error message :
3155 -- "You cannot delete this task as task has association with deliverable which has transactions"
3156 --
3157 -- which is irrelevant error message when the operation is "Disabling Workplan" (Or) "SHARE -> SPLIT SETUP CHANGES"
3158
3159 -- Note that :- Disabling Workplan (Or) "SHARE -> SPLIT SETUP CHANGES" also ,deletes WP tasks and
3160 -- which also means that the tasks' associations with the deliverables have to be deleted
3161
3162 -- So,In this case ,to throw an appropriate error message
3163 -- (and) to avoid any impact (for DELETE_DLV_TASK_ASSCN_IN_BULK API being called from other places)
3164 -- We are putting the same code in DELETE_DLV_ASSOCIATIONS API
3165
3166 -- *******************************************************************************************************************
3167 -- FOR ANY FIX DONE FOR p_calling_context ='PA_TASKS' case ,SAME FIX HAS TO DONE FOR DELETE_DLV_ASSOCIATIONS API ALSO
3168 -- *******************************************************************************************************************
3169 PROCEDURE DELETE_DLV_TASK_ASSCN_IN_BULK
3170 (p_api_version IN NUMBER :=1.0
3171 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3172 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
3173 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3174 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3175 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3176 ,p_debug_mode IN VARCHAR2 :='N'
3177 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3178 ,p_calling_context IN VARCHAR2 := 'PA_TASKS'
3179 ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
3180 ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
3181 ,p_project_id IN pa_projects_all.project_id%TYPE
3182 ,p_delete_or_validate IN VARCHAR2 := 'B' -- 3955848 V- Validate , D - Delete, B - Validate and Delete ( default )
3183 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3184 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3185 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3186 )
3187 IS
3188
3189 l_msg_count NUMBER ;
3190 l_data VARCHAR2(2000);
3191 l_msg_data VARCHAR2(2000);
3192 l_msg_index_out NUMBER;
3193 l_dummy VARCHAR2(1) ;
3194
3195 -- 3899363 Commented below cursor select because shipping/procurement/mds and billing action related validations
3196 -- are not required for workplan task deletion
3197 -- Also, the below sql is passing deliverable action element id to check for deliverable publish progress record e
3198 -- existance
3199 -- The below validations should be done only for deliverale based task
3200
3201 /*
3202 -- Bug 3651781 While deleting association between a deliverable based task and a deliverable ,we have to
3203 -- Look for 'PUBLISHED' progress records ,NOT mere existence of progress records
3204 CURSOR C(c_project_id IN NUMBER) IS
3205 SELECT 'Y'
3206 FROM DUAL
3207 WHERE EXISTS (SELECT 'Y'
3208 FROM pa_proj_element_versions pev
3209 ,pa_object_relationships obj1
3210 ,pa_object_relationships obj2
3211 WHERE obj1.object_id_from2 = p_task_element_id
3212 AND obj1.relationship_type = 'A'
3213 AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3214 AND obj1.object_type_from = 'PA_TASKS'
3215 AND obj1.object_type_to = 'PA_DELIVERABLES'
3216 AND obj2.object_id_from2 = obj1.object_id_to2
3217 AND obj2.relationship_type = 'A'
3218 AND obj2.relationship_subtype = 'DELIVERABLE_TO_ACTION'
3219 AND obj2.object_type_from = 'PA_DELIVERABLES'
3220 AND obj2.object_type_to = 'PA_ACTIONS'
3221 AND obj2.object_id_to2 = pev.proj_element_id
3222 AND (nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(pev.element_version_id),'N') = 'Y'
3223 OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(pev.element_version_id),'N') = 'Y'
3224 OR nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(pev.element_version_id),'N') = 'Y'
3225 OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(pev.proj_element_id) = 'BILLING'
3226 -- OR nvl(PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(c_project_id,pev.proj_element_id),'N') = 'Y' Commented for 3651781
3227 OR nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , pev.proj_element_id),'N') = 'Y'
3228 )
3229 );
3230 */
3231
3232 -- Changed the above cursor select, it returns Y if deliverable based task is associated with deliverable, having
3233 -- published progress record
3234 -- if below cursor returns Y, workplan task to deliverable association deletion is not allowed, error message is populated
3235
3236 CURSOR C(c_project_id IN NUMBER) IS
3237 SELECT 'Y'
3238 FROM DUAL
3239 WHERE EXISTS (SELECT 'Y'
3240 FROM pa_proj_elements ppe
3241 ,pa_object_relationships obj1
3242 ,pa_task_types ptt
3243 WHERE obj1.object_id_from2 = p_task_element_id
3244 AND obj1.relationship_type = 'A'
3245 AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3246 AND obj1.object_type_from = 'PA_TASKS'
3247 AND obj1.object_type_to = 'PA_DELIVERABLES'
3248 AND obj1.object_id_from2 = ppe.proj_element_id
3249 and ppe.type_id = ptt.task_type_id
3250 and ppe.object_type = 'PA_TASKS'
3251 and nvl(ppe.base_percent_comp_deriv_code,ptt.base_percent_comp_deriv_code) = 'DELIVERABLE'
3252 and nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , obj1.object_id_to2),'N') = 'Y');
3253
3254 -- 3899363 end
3255
3256 BEGIN
3257
3258 x_msg_count := 0;
3259 x_return_status := FND_API.G_RET_STS_SUCCESS;
3260
3261 IF p_debug_mode = 'Y' THEN
3262 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_TASK_ASSOCIATION',
3263 p_debug_mode => p_debug_mode );
3264 pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
3265 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3266 END IF;
3267
3268 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
3269 FND_MSG_PUB.initialize;
3270 END IF;
3271
3272 IF p_debug_mode = 'Y' THEN
3273 pa_debug.g_err_stage:= 'Opening cursor ';
3274 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3275 END IF ;
3276
3277 /* Following Comment introduced for Bug 3906015*/
3278 -- *******************************************************************************************************************
3279 -- FOR ANY FIX DONE FOR p_calling_context ='PA_TASKS' case ,SAME FIX HAS TO DONE FOR DELETE_DLV_ASSOCIATIONS API ALSO
3280 -- *******************************************************************************************************************
3281
3282 IF p_calling_context = 'PA_TASKS' THEN
3283
3284 -- 3955848 Added following if condition to check for p_delete_or_validate
3285 -- For this fix, there is no change required in DELETE_DLV_ASSOCIATIONS because in this
3286 -- fix there is no extra validation is done, just separated the validation and actual deletion
3287 -- for version enabled case, task deletion
3288
3289 -- if p_delete_or_validate is B , do both validation and deletion ( default behaviour )
3290 -- if p_delete_or_validate is V , do only validation ( ver enabled case , task deletion flow )
3291
3292 IF p_delete_or_validate IN ('B','V') THEN
3293
3294 OPEN C(p_project_id) ;
3295 FETCH C into l_dummy ;
3296 IF C%FOUND THEN
3297 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_TASK_ASSCN_EXISTS') ;
3298 RAISE Invalid_Arg_Exc_Dlv ;
3299 END IF ;
3300 CLOSE C;
3301
3302 END IF;
3303
3304 IF p_debug_mode = 'Y' THEN
3305 pa_debug.g_err_stage:= 'Delete the association';
3306 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3307 END IF ;
3308
3309 -- 3955848 Added following if condition to check for p_delete_or_validate
3310 -- if p_delete_or_validate is B , do both validation and deletion ( default behaviour )
3311 -- if p_delete_or_validate is D , do only deletion ( ver enabled case, publishing flow )
3312
3313 IF p_delete_or_validate IN ('B','D') THEN
3314
3315 DELETE FROM pa_object_relationships
3316 WHERE object_id_from2 = p_task_element_id
3317 AND object_type_from = 'PA_TASKS'
3318 AND object_type_to = 'PA_DELIVERABLES'
3319 AND relationship_type = 'A'
3320 AND relationship_subtype = 'TASK_TO_DELIVERABLE' ;
3321
3322 END IF;
3323 ELSE
3324 DELETE FROM pa_object_relationships
3325 WHERE object_id_from2 = p_task_element_id
3326 AND object_type_from = 'PA_ASSIGNMENTS'
3327 AND object_type_to = 'PA_DELIVERABLES'
3328 AND relationship_type = 'A'
3329 AND relationship_subtype = 'ASSIGNMENT_TO_DELIVERABLE' ;
3330 END IF ;
3331
3332 IF p_debug_mode = 'Y' THEN
3333 pa_debug.g_err_stage:= 'Exiting DELETE_DLV_TASK_ASSCN_IN_BULK' ;
3334 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
3335 pa_debug.reset_curr_function;
3336 END IF;
3337
3338 EXCEPTION
3339 WHEN Invalid_Arg_Exc_Dlv THEN
3340 x_return_status := FND_API.G_RET_STS_ERROR;
3341 l_msg_count := FND_MSG_PUB.count_msg;
3342
3343 IF p_debug_mode = 'Y' THEN
3344 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_TASK_ASSCN_IN_BULK';
3345 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
3346 END IF;
3347
3348 IF l_msg_count = 1 THEN
3349 PA_INTERFACE_UTILS_PUB.get_messages
3350 (p_encoded => FND_API.G_TRUE,
3351 p_msg_index => 1,
3352 p_msg_count => l_msg_count,
3353 p_msg_data => l_msg_data,
3354 p_data => l_data,
3355 p_msg_index_out => l_msg_index_out);
3356 x_msg_data := l_data;
3357 x_msg_count := l_msg_count;
3358 ELSE
3359 x_msg_count := l_msg_count;
3360 END IF;
3361 IF p_debug_mode = 'Y' THEN
3362 pa_debug.reset_curr_function;
3363 END IF ;
3364 RETURN;
3365 WHEN OTHERS THEN
3366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3367 x_msg_count := 1;
3368 x_msg_data := SQLERRM;
3369
3370 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
3371 ,p_procedure_name => 'DELETE_DLV_TASK_ASSCN_IN_BULK');
3372
3373 IF p_debug_mode = 'Y' THEN
3374 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3375 pa_debug.write('DELETE_DLV_TASK_ASSCN_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
3376 pa_debug.reset_curr_function;
3377 END IF;
3378 RAISE;
3379 END DELETE_DLV_TASK_ASSCN_IN_BULK ;
3380
3381
3382 /* Elaborative Comment explaining purpose of API included for Bug 3906015*/
3383
3384 -- This procedure is called ONLY under 2 cases
3385 -- 1) While Structure Setup Change happens
3386 -- from SHARED -> SPLIT
3387 -- 2) While Workplan is Disabled
3388
3389 -- In this case ,the Workplan Tasks have to be deleted and hence their associations
3390 -- with deliverables too .
3391
3392 -- So,While a WP Task is deleted ,the validations that have to happen from Deliverables Side are :-
3393 -- 1) There should not be 'published' progress record for Deliverable associated to the WP Task
3394 -- 2) There should not be any transaction for the deliverable associated to the WP task by means of
3395 -- Initiating Shipping ,Procurement (Or) Billing
3396 --******************************************************************************************************************
3397 -- In Future whatever fix is done to DELETE_DLV_TASK_ASSCN_IN_BULK API (for p_calling_context = 'PA_TASKS' case)
3398 -- care should be taken to see that whether same fix needs to be done here in DELETE_DLV_ASSOCIATIONS too .
3399
3400 -- The Vice versa also applies .
3401 --******************************************************************************************************************
3402 PROCEDURE DELETE_DLV_ASSOCIATIONS
3403 (p_api_version IN NUMBER :=1.0
3404 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3405 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
3406 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3407 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3408 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3409 ,p_debug_mode IN VARCHAR2 :='N'
3410 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3411 ,p_project_id IN NUMBER
3412 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3413 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3414 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3415 )
3416 IS
3417 l_debug_mode VARCHAR2(10);
3418 l_msg_count NUMBER ;
3419 l_data VARCHAR2(2000);
3420 l_msg_data VARCHAR2(2000);
3421 l_msg_index_out NUMBER;
3422 l_dummy VARCHAR2(1) ;
3423 l_return_flag VARCHAR2(1) ;
3424
3425 -- 3899363 Commented below cursor select because shipping/procurement/mds and billing action related validations
3426 -- are not required for workplan task deletion
3427 -- Also, the below sql is passing deliverable action element id to check for deliverable publish progress record e
3428 -- existance
3429 -- The below validations should be done only for deliverale based task
3430
3431 /* This cursor is very much similar to the one (cursor c) in DELETE_DLV_TASK_ASSCN_IN_BULK API
3432 But with little modifications - bug 3906015*/
3433 /*
3434 CURSOR C (c_project_id IN NUMBER) IS
3435 SELECT 'Y'
3436 from dual
3437 WHERE EXISTS(
3438 SELECT 'Y'
3439 FROM PA_PROJ_ELEMENT_VERSIONS pev1,
3440 PA_OBJECT_RELATIONSHIPS obj1,
3441 PA_PROJ_ELEMENT_VERSIONS pev,
3442 PA_OBJECT_RELATIONSHIPS obj2
3443 WHERE obj1.object_id_from2 = pev1.proj_element_id
3444 AND pev1.project_id = c_project_id
3445 AND pev1.object_type = 'PA_TASKS'
3446 AND obj1.relationship_type = 'A'
3447 AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3448 AND obj1.object_type_from = 'PA_TASKS'
3449 AND obj1.object_type_to = 'PA_DELIVERABLES'
3450 AND obj2.object_id_from2 = obj1.object_id_to2
3451 AND obj2.relationship_type = 'A'
3452 AND obj2.relationship_subtype = 'DELIVERABLE_TO_ACTION'
3453 AND obj1.object_type_from = 'PA_DELIVERABLES'
3454 AND obj1.object_type_to = 'PA_ACTIONS'
3455 AND obj2.object_id_to2 = pev.proj_element_id
3456 AND (nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(pev.element_version_id),'N') = 'Y'
3457 OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(pev.element_version_id),'N') = 'Y'
3458 OR nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(pev.element_version_id),'N') = 'Y'
3459 OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(pev.proj_element_id) = 'BILLING'
3460 OR nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , pev.proj_element_id),'N') = 'Y'
3461 )
3462 );
3463 */
3464
3465 -- Changed the above cursor select, it returns Y if deliverable based task is associated with deliverable, having
3466 -- published progress record
3467 -- if below cursor returns Y, workplan task to deliverable association deletion is not allowed, error message is populated
3468
3469 CURSOR C (c_project_id IN NUMBER) IS
3470 SELECT 'Y'
3471 from dual
3472 WHERE EXISTS(
3473 SELECT 'Y'
3474 FROM pa_proj_elements ppe
3475 ,pa_object_relationships obj1
3476 ,pa_task_types ptt
3477 WHERE
3478 ppe.project_id = c_project_id
3479 and ppe.object_type = 'PA_TASKS'
3480 and ppe.type_id = ptt.task_type_id
3481 and nvl(ppe.base_percent_comp_deriv_code,ptt.base_percent_comp_deriv_code) = 'DELIVERABLE'
3482 AND obj1.object_id_from2 = ppe.proj_element_id
3483 AND obj1.relationship_type = 'A'
3484 AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3485 AND obj1.object_type_from = 'PA_TASKS'
3486 AND obj1.object_type_to = 'PA_DELIVERABLES'
3487 and nvl(PA_PROGRESS_UTILS.published_dlv_prog_exists(c_project_id , obj1.object_id_to2),'N') = 'Y');
3488
3489 -- 3899363 end
3490
3491 BEGIN
3492
3493 x_msg_count := 0;
3494 x_return_status := FND_API.G_RET_STS_SUCCESS;
3495
3496 IF p_debug_mode = 'Y' THEN
3497 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_ASSOCIATIONS',
3498 p_debug_mode => p_debug_mode );
3499 pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
3500 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3501 END IF;
3502
3503 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
3504 FND_MSG_PUB.initialize;
3505 END IF;
3506
3507 /* Commented for Bug 3906015 : Incorrect Check was being performed
3508 -- Validate mandatory input parameter
3509 PA_DELIVERABLE_UTILS.CHECK_DLVR_DISABLE_ALLOWED
3510 ( p_debug_mode => p_debug_mode
3511 ,p_project_id => p_project_id
3512 ,x_return_flag => l_return_flag
3513 ,x_return_status => x_return_status
3514 ,x_msg_count => x_msg_count
3515 ,x_msg_data => x_msg_data
3516 ) ;
3517
3518 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3519 RAISE Invalid_Arg_Exc_Dlv ;
3520 END IF ;
3521 */
3522 -- Bug 3906015 : Start
3523 OPEN C(p_project_id) ;
3524 FETCH C into l_dummy ;
3525 IF C%FOUND THEN
3526 PA_UTILS.ADD_MESSAGE('PA','PA_TASK_DLV_ASSCN_EXISTS') ;
3527 RAISE Invalid_Arg_Exc_Dlv ;
3528 END IF ;
3529 CLOSE C;
3530 -- Bug 3906015 : End
3531
3532 IF p_debug_mode = 'Y' THEN
3533 pa_debug.g_err_stage:= 'Delete the associations';
3534 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3535 END IF ;
3536
3537 DELETE FROM pa_object_relationships
3538 WHERE object_type_from = 'PA_TASKS'
3539 AND object_type_to = 'PA_DELIVERABLES'
3540 AND relationship_type = 'A'
3541 AND relationship_subtype = 'TASK_TO_DELIVERABLE'
3542 AND object_id_from2 in (SELECT proj_element_id
3543 FROM pa_proj_elements
3544 WHERE project_id = p_project_id ) ;
3545
3546 IF p_debug_mode = 'Y' THEN
3547 pa_debug.g_err_stage:= 'Exiting DELETE_DLV_ASSOCIATIONS' ;
3548 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
3549 pa_debug.reset_curr_function;
3550 END IF;
3551
3552 EXCEPTION
3553 WHEN Invalid_Arg_Exc_Dlv THEN
3554 x_return_status := FND_API.G_RET_STS_ERROR;
3555 l_msg_count := FND_MSG_PUB.count_msg;
3556
3557 IF p_debug_mode = 'Y' THEN
3558 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_ASSOCIATIONS';
3559 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
3560 END IF;
3561
3562 IF l_msg_count = 1 THEN
3563 PA_INTERFACE_UTILS_PUB.get_messages
3564 (p_encoded => FND_API.G_TRUE,
3565 p_msg_index => 1,
3566 p_msg_count => l_msg_count,
3567 p_msg_data => l_msg_data,
3568 p_data => l_data,
3569 p_msg_index_out => l_msg_index_out);
3570 x_msg_data := l_data;
3571 x_msg_count := l_msg_count;
3572 ELSE
3573 x_msg_count := l_msg_count;
3574 END IF;
3575 IF p_debug_mode = 'Y' THEN
3576 pa_debug.reset_curr_function;
3577 END IF ;
3578 RETURN;
3579 WHEN OTHERS THEN
3580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3581 x_msg_count := 1;
3582 x_msg_data := SQLERRM;
3583
3584 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
3585 ,p_procedure_name => 'DELETE_DLV_ASSOCIATIONS');
3586
3587 IF p_debug_mode = 'Y' THEN
3588 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
3589 pa_debug.write('DELETE_DLV_ASSOCIATIONS: ' || g_module_name,pa_debug.g_err_stage,5);
3590 pa_debug.reset_curr_function;
3591 END IF;
3592 RAISE;
3593 END DELETE_DLV_ASSOCIATIONS ;
3594
3595 -- SubProgram : COPY_DELIVERABLES
3596 -- Type : PROCEDURE
3597 -- Purpose : Public API to Delete Multiple Deliverables from Deliverable List Page
3598 -- Note : This API is called from Deliverable List Page
3599 -- Assumptions : None
3600 -- Parameter IN/OUT Type Required Description and Purpose
3601 -- --------------------------- --------- ---------- --------- ---------------------------
3602 -- p_api_version IN NUMBER N Standard Parameter
3603 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
3604 -- p_commit IN VARCHAR2 N Standard Parameter
3605 -- p_validate_only IN VARCHAR2 N Standard Parameter
3606 -- p_validation_level IN NUMBER N Standard Parameter
3607 -- p_calling_module IN VARCHAR2 N Standard Parameter
3608 -- p_debug_mode IN VARCHAR2 N Standard Parameter
3609 -- p_max_msg_count IN NUMBER N Standard Parameter
3610 -- p_source_project_id IN NUMBER Y Source Project Id
3611 -- p_target_project_id IN NUMBER Y Target Project Id
3612 -- p_dlv_element_id_tbl IN PLSQL table N Source Dlv. Element Id
3613 -- p_dlv_version_id_tbl IN PLSQL table N Target Dlv. Element Id
3614 -- p_item_details_flag IN VARCHAR2 N Copy Item Flag
3615 -- p_dlv_actions_flag IN VARCHAR2 N Copy Actions Flag
3616 -- p_dlv_attachments_flag IN VARCHAR2 N Copy Attachment Flag
3617 -- p_association_flag IN VARCHAR2 N Copy Associations Flag
3618 -- p_prefix IN VARCHAR2 N Prefix
3619 -- p_delta IN VARCHAR2 N Passed during copy project
3620 -- p_calling_context IN VARCHAR2 Y Calling Context.
3621 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
3622 -- x_msg_count OUT NUMBER N Standard Out Parameter
3623 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
3624
3625
3626 -- This API is called from different flows :
3627 -- 1. CREATE PROJECT FROM TEMPLATE/PROJECT
3628 -- 2. COPY DELIVERABLES
3629 -- 3. COPY EXTERNAL
3630 -- 4. COPY TASKS
3631 -- Any changes in this API might impact the above flows so all the impact analysis
3632 -- is must .
3633
3634 -- Following bugs are fixed :
3635 -- Note : Please update this sction with the bug no. and description
3636 -- for any bug fixes .
3637 -- Bug No. Date Technichal Description(In Brief)
3638 -- ======== ======== ================================
3639 -- 3515845 22-MAR-04 Progress weight not getting copied as
3640 -- l_progress_weigh_tbl was overwritten with
3641 -- function_code for deliverables which null.
3642 -- Hence always null is getting copied.
3643 -- Removed the function_code from the cursor
3644 -- l_proj_element_data and l_source_deliverables
3645 -- and from subsequent fetch statement removed the
3646 -- l_progress_weight_tbl
3647
3648 -- 3515852 13-MAR-04 While copying deliverables , status should be
3649 -- defaulted from deliverable type .
3650
3651 -- 3493612 13-MAR-04 During create project, dates are not adjusted
3652 -- based on quick entry dates.
3653
3654 PROCEDURE COPY_DELIVERABLES
3655 (p_api_version IN NUMBER :=1.0
3656 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3657 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
3658 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3659 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3660 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3661 ,p_debug_mode IN VARCHAR2 :='N'
3662 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3663 ,p_source_project_id IN NUMBER
3664 ,p_target_project_id IN NUMBER
3665 ,p_dlv_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
3666 ,p_dlv_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
3667 ,p_item_details_flag IN VARCHAR2 := 'N'
3668 ,p_dlv_actions_flag IN VARCHAR2 := 'N'
3669 ,p_dlv_attachments_flag IN VARCHAR2 := 'N'
3670 ,p_association_flag IN VARCHAR2 := 'N'
3671 ,p_prefix IN VARCHAR2 := null
3672 ,p_delta IN NUMBER := null
3673 ,p_calling_context IN VARCHAR2
3674 ,p_task_id IN NUMBER :=null
3675 ,p_task_version_id IN NUMBER :=null
3676 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3677 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3678 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3679 )
3680 IS
3681 l_debug_mode VARCHAR2(10) ;
3682 l_msg_count NUMBER ;
3683 l_data VARCHAR2(2000) ;
3684 l_msg_data VARCHAR2(2000) ;
3685 l_msg_index_out NUMBER ;
3686
3687 -- This is the cursor which will be called during copy deliverables,copy external and copy tasks.
3688 -- This cursor fetch the source deliverable info.
3689
3690 CURSOR l_proj_element_data(c_proj_element_id IN pa_proj_elements.proj_element_id%TYPE)
3691 IS
3692 SELECT SUBSTR(p_prefix||ppe.element_number,1,100)
3693 ,SUBSTR(p_prefix||ppe.name,1,240)
3694 ,ppe.manager_person_id
3695 ,ppe.carrying_out_organization_id
3696 ,ppe.progress_weight
3697 ,ppe.pm_source_reference
3698 ,ppe.pm_source_code
3699 ,ppe.description
3700 ,ppe.attribute_category
3701 ,ppe.attribute1
3702 ,ppe.attribute2
3703 ,ppe.attribute3
3704 ,ppe.attribute4
3705 ,ppe.attribute5
3706 ,ppe.attribute6
3707 ,ppe.attribute7
3708 ,ppe.attribute8
3709 ,ppe.attribute9
3710 ,ppe.attribute10
3711 ,ppe.attribute11
3712 ,ppe.attribute12
3713 ,ppe.attribute13
3714 ,ppe.attribute14
3715 ,ppe.proj_element_id --source is maintianed in attribute15 column.
3716 ,ppe.proj_element_id --to populate the source
3717 ,pev.element_version_id -- Source element version id
3718 ,ppe.type_id
3719 ,pvs.scheduled_finish_date
3720 ,ptt.initial_status_code -- Bug#3515852
3721 FROM pa_proj_elements ppe
3722 ,pa_proj_elem_ver_schedule pvs
3723 ,pa_proj_element_versions pev
3724 ,pa_task_types ptt
3725 WHERE ppe.proj_element_id = c_proj_element_id
3726 AND ppe.object_type = 'PA_DELIVERABLES'
3727 AND pev.proj_element_id = c_proj_element_id
3728 AND pev.object_type = 'PA_DELIVERABLES'
3729 AND pvs.proj_element_id = c_proj_element_id
3730 AND pvs.project_id = ppe.project_id
3731 AND ptt.task_type_id = ppe.type_id -- Bug#3515852
3732 AND ptt.object_type = 'PA_DLVR_TYPES'; -- Bug#3515852
3733
3734 -- This is the cursor which will be called during create project flow.
3735 -- This cursor will fetch the source deliverable info.
3736
3737 CURSOR l_source_deliverables
3738 IS
3739 SELECT ppe.element_number
3740 ,ppe.name
3741 ,ppe.manager_person_id
3742 ,ppe.carrying_out_organization_id
3743 ,ppe.progress_weight
3744 ,ppe.pm_source_reference
3745 ,ppe.pm_source_code
3746 ,ppe.description
3747 ,ppe.attribute_category
3748 ,ppe.attribute1
3749 ,ppe.attribute2
3750 ,ppe.attribute3
3751 ,ppe.attribute4
3752 ,ppe.attribute5
3753 ,ppe.attribute6
3754 ,ppe.attribute7
3755 ,ppe.attribute8
3756 ,ppe.attribute9
3757 ,ppe.attribute10
3758 ,ppe.attribute11
3759 ,ppe.attribute12
3760 ,ppe.attribute13
3761 ,ppe.attribute14
3762 ,ppe.proj_element_id --source is maintianed in attribute15 column.
3763 ,ppe.proj_element_id --to populate the source
3764 ,pev.element_version_id -- Source element version id
3765 ,ppe.type_id
3766 ,pvs.scheduled_finish_date
3767 ,ptt.initial_status_code -- Bug#3515852
3768 FROM pa_proj_elements ppe
3769 ,pa_proj_elem_ver_schedule pvs
3770 ,pa_proj_element_versions pev
3771 ,pa_task_types ptt
3772 WHERE ppe.project_id = p_source_project_id
3773 AND ppe.object_type = 'PA_DELIVERABLES'
3774 AND pev.proj_element_id = ppe.proj_element_id
3775 AND pev.project_id = p_source_project_id
3776 AND ppe.project_id = pev.project_id -- Added for perf bug# 3964586
3777 AND pev.object_type = 'PA_DELIVERABLES'
3778 AND ppe.project_id=pvs.project_id -- Added for perf bug# 3964586
3779 AND ppe.proj_element_id = pvs.proj_element_id
3780 AND pev.element_version_id=pvs.element_version_id -- Added for perf bug# 3964586
3781 AND pvs.project_id = p_source_project_id
3782 AND ptt.task_type_id = ppe.type_id -- Bug#3515852
3783 AND ptt.object_type = 'PA_DLVR_TYPES'; -- Bug#3515852
3784
3785 CURSOR c_structure_id IS
3786 SELECT ppe.proj_element_id
3787 ,ppe.element_version_id
3788 FROM pa_proj_elem_ver_structure ppe
3789 ,pa_proj_structure_types pst
3790 ,pa_structure_types sty
3791 WHERE ppe.project_id = p_target_project_id
3792 AND ppe.proj_element_id = pst.proj_element_id
3793 AND pst.structure_type_id = sty.structure_type_id
3794 AND sty.structure_type = 'DELIVERABLE'
3795 AND sty.structure_type_class_code = 'DELIVERABLE'
3796 ;
3797
3798 /* Commented for Performance Bug 3614361
3799 SELECT ppe.proj_element_id
3800 ,pev.element_version_id
3801 FROM pa_proj_structure_types pst
3802 ,pa_structure_types sty
3803 ,pa_proj_elements ppe
3804 ,pa_proj_element_versions pev
3805 WHERE ppe.project_id = p_target_project_id
3806 AND ppe.object_type = 'PA_STRUCTURES'
3807 AND pev.proj_element_id = ppe.proj_element_id
3808 AND pev.project_id = ppe.project_id
3809 AND ppe.object_type = pev.object_type
3810 AND ppe.proj_element_id = pst.proj_element_id
3811 AND pst.structure_type_id = sty.structure_type_id
3812 AND sty.structure_type = 'DELIVERABLE'
3813 AND sty.structure_type_class_code = 'DELIVERABLE' ;
3814 */
3815 --Bug 3611598 Following Cursor and local variables have been included
3816 CURSOR c_project_details IS
3817 SELECT pa.project_type
3818 ,pa.project_status_code
3819 ,project_system_status_code
3820 ,segment1 -- 3671408 added column to retrieve project number
3821 FROM PA_PROJECT_STATUSES pps,
3822 PA_PROJECTS_ALL pa
3823 WHERE pa.PROJECT_ID = p_target_project_id
3824 AND pps.PROJECT_STATUS_CODE = pa.PROJECT_STATUS_CODE ;
3825
3826 l_project_sys_status_code PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
3827 l_status_code PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
3828 l_project_type PA_PROJECTS_ALL.PROJECT_TYPE%TYPE;
3829
3830 l_item_type VARCHAR2(30);
3831 l_wf_process VARCHAR2(30);
3832 l_wf_item_type VARCHAR2(30);
3833 l_err_code NUMBER := 0;
3834 l_wf_enabled_flag VARCHAR2(1);
3835 --Bug 3611598 <<End of Changes>>
3836
3837 l_structure_id NUMBER ;
3838 l_structure_version_id NUMBER ;
3839 l_owner_id NUMBER ;
3840 l_dummy PER_ALL_PEOPLE_F.FULL_NAME%TYPE ;
3841 l_due_date DATE ;
3842 l_parent_structure_id NUMBER ;
3843 l_parent_structure_version_id NUMBER ;
3844 l_y_or_n VARCHAR2(1) ;
3845 l_project_name pa_projects_all.name%TYPE ;
3846 l_suffix pa_lookups.meaning%TYPE ;
3847 l_name VARCHAR2(250);
3848
3849 l_element_number_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3850 l_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3851 l_manager_person_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3852 l_carrying_out_org_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3853 l_progress_weight_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3854 l_pm_source_reference_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
3855 l_pm_source_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
3856
3857 l_due_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE() ;
3858 l_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3859 l_rec_ver_num_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3860 l_description_tbl SYSTEM.PA_VARCHAR2_2000_TBL_TYPE := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
3861 l_attribute_category_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
3862 l_attribute1_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3863 l_attribute2_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3864 l_attribute3_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3865 l_attribute4_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3866 l_attribute5_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3867 l_attribute6_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3868 l_attribute7_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3869 l_attribute8_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3870 l_attribute9_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3871 l_attribute10_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3872 l_attribute11_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3873 l_attribute12_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3874 l_attribute13_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3875 l_attribute14_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3876 l_attribute15_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
3877 l_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3878 -- Bug#3515852
3879 l_status_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE() ;
3880
3881 l_proj_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3882 l_source_proj_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3883 l_source_element_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3884 l_target_element_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
3885 l_date DATE ;
3886 -- Bug#3515852 : This flag is no more required. Hence commenting.
3887 -- l_item_details_flag VARCHAR2(1) := 'N' ; -- 3469876 added the variable
3888
3889 l_project_number pa_projects_all.segment1%TYPE ; -- added for bug# 3671408
3890
3891 BEGIN
3892
3893 l_msg_count := 0;
3894 x_return_status := FND_API.G_RET_STS_SUCCESS;
3895 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3896
3897 IF l_debug_mode = 'Y' THEN
3898 PA_DEBUG.set_curr_function( p_function => 'COPY_DELIVERABLES',
3899 p_debug_mode => l_debug_mode );
3900 pa_debug.g_err_stage:= 'Inside COPY_DELIVERABLES ';
3901 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3902 END IF;
3903
3904 IF l_debug_mode = 'Y' THEN
3905 pa_debug.g_err_stage:= 'p_source_project_id is '||p_source_project_id;
3906 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3907 pa_debug.g_err_stage:= 'p_target_project_id is '||p_target_project_id;
3908 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3909 END IF;
3910
3911 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
3912 FND_MSG_PUB.initialize;
3913 END IF;
3914
3915 IF p_calling_context = 'COPY_PROJECT' THEN
3916
3917 -- Below two selects are used for creating structure level
3918 -- element name .
3919 SELECT meaning
3920 INTO l_suffix
3921 FROM pa_lookups
3922 WHERE lookup_type = 'PA_STRUCTURE_TYPE_CLASS'
3923 AND lookup_code = 'DELIVERABLE';
3924
3925 SELECT name
3926 INTO l_project_name
3927 FROM pa_projects_all
3928 WHERE project_id = p_target_project_id ;
3929
3930
3931
3932
3933 l_name := substr(l_project_name||':'||l_suffix, 1, 240) ;
3934
3935 -- Get the structure element id and version id before inserting .
3936 SELECT pa_tasks_s.nextval
3937 ,pa_proj_element_versions_s.nextval
3938 INTO l_parent_structure_id
3939 ,l_parent_structure_version_id
3940 FROM dual ;
3941
3942
3943 -- If p_calling_context is COPY_PROJECT then first
3944 -- populate the structure level record .
3945
3946
3947 INSERT INTO PA_PROJ_ELEMENTS
3948 ( proj_element_id
3949 ,project_id
3950 ,object_type
3951 ,element_number
3952 ,name
3953 ,status_code
3954 ,creation_date
3955 ,created_by
3956 ,last_update_date
3957 ,last_updated_by
3958 ,description
3959 ,pm_source_reference
3960 ,pm_source_code
3961 ,manager_person_id
3962 ,carrying_out_organization_id
3963 ,record_version_number
3964 ,last_update_login
3965 ,parent_structure_id
3966 ,source_object_id
3967 ,source_object_type
3968 )
3969 SELECT l_parent_structure_id
3970 ,p_target_project_id
3971 ,ppe.object_type
3972 ,ppe.element_number
3973 ,l_name
3974 ,ppe.status_code
3975 ,sysdate
3976 ,fnd_global.user_id
3977 ,sysdate
3978 ,fnd_global.user_id
3979 ,ppe.description
3980 ,ppe.pm_source_reference
3981 ,ppe.pm_source_code
3982 ,ppe.manager_person_id
3983 ,ppe.carrying_out_organization_id
3984 ,1
3985 ,fnd_global.login_id
3986 ,l_parent_structure_id
3987 ,p_target_project_id
3988 ,'PA_PROJECTS'
3989 FROM pa_proj_elements ppe,
3990 pa_proj_structure_types pst
3991 WHERE ppe.object_type = 'PA_STRUCTURES'
3992 AND ppe.project_id = p_source_project_id
3993 AND pst.proj_element_id = ppe.proj_element_id
3994 AND pst.structure_type_id = 8 ;--For Deliverable
3995
3996
3997 INSERT INTO PA_PROJ_ELEMENT_VERSIONS
3998 ( element_version_id
3999 ,proj_element_id
4000 ,object_type
4001 ,project_id
4002 ,parent_structure_version_id
4003 ,creation_date
4004 ,created_by
4005 ,last_update_date
4006 ,last_updated_by
4007 ,record_version_number
4008 ,last_update_login
4009 ,source_object_id
4010 ,source_object_type
4011 )
4012 VALUES
4013 (l_parent_structure_version_id
4014 ,l_parent_structure_id
4015 ,'PA_STRUCTURES'
4016 ,p_target_project_id
4017 ,l_parent_structure_version_id
4018 ,SYSDATE
4019 ,fnd_global.user_id
4020 ,SYSDATE
4021 ,fnd_global.user_id
4022 ,1
4023 ,fnd_global.login_id
4024 ,p_target_project_id,
4025 'PA_PROJECTS'
4026 ) ;
4027
4028 INSERT INTO PA_PROJ_STRUCTURE_TYPES
4029 ( proj_structure_type_id
4030 ,proj_element_id
4031 ,structure_type_id
4032 ,creation_date
4033 ,created_by
4034 ,last_update_date
4035 ,last_updated_by
4036 ,last_update_login
4037 ,record_version_number
4038 )
4039 VALUES
4040 (pa_proj_structure_types_s.nextval
4041 ,l_parent_structure_id
4042 ,8
4043 ,SYSDATE
4044 ,fnd_global.user_id
4045 ,SYSDATE
4046 ,fnd_global.user_id
4047 ,fnd_global.login_id
4048 ,1
4049 ) ;
4050
4051 INSERT INTO PA_PROJ_ELEM_VER_STRUCTURE
4052 ( pev_structure_id
4053 ,element_version_id
4054 ,version_number
4055 ,name
4056 ,project_id
4057 ,proj_element_id
4058 ,current_flag
4059 ,original_flag
4060 ,latest_eff_published_flag
4061 ,creation_date
4062 ,created_by
4063 ,last_update_date
4064 ,last_updated_by
4065 ,record_version_number
4066 ,pm_source_code
4067 ,pm_source_reference
4068 ,source_object_id
4069 ,source_object_type)
4070 SELECT
4071 pa_proj_elem_ver_structure_s.nextval
4072 ,l_parent_structure_version_id
4073 ,ppe.element_number
4074 ,l_name
4075 ,p_target_project_id
4076 ,l_parent_structure_id
4077 ,ver.current_flag
4078 ,ver.original_flag
4079 ,ver.latest_eff_published_flag
4080 ,sysdate
4081 ,fnd_global.user_id
4082 ,sysdate
4083 ,fnd_global.user_id
4084 ,1
4085 ,ppe.pm_source_code
4086 ,ppe.pm_source_reference
4087 ,p_target_project_id
4088 ,'PA_PROJECTS'
4089 FROM pa_proj_elem_ver_structure ver
4090 ,pa_proj_elements ppe
4091 ,pa_proj_structure_types pst
4092 WHERE ver.project_id = p_source_project_id
4093 AND ppe.proj_element_id = ver.proj_element_id
4094 AND ppe.object_type = 'PA_STRUCTURES'
4095 AND ppe.proj_element_id = pst.proj_element_id
4096 AND pst.structure_type_id = 8 ;
4097
4098 ELSE
4099
4100 OPEN c_structure_id;
4101 FETCH c_structure_id INTO l_parent_structure_id ,l_parent_structure_version_id ;
4102 CLOSE c_structure_id ;
4103
4104 END IF ;
4105
4106 IF p_calling_context = 'COPY_PROJECT' THEN
4107
4108 OPEN l_source_deliverables ;
4109 FETCH l_source_deliverables BULK COLLECT INTO
4110 l_element_number_tbl
4111 ,l_name_tbl
4112 ,l_manager_person_id_tbl
4113 ,l_carrying_out_org_id_tbl
4114 ,l_progress_weight_tbl
4115 ,l_pm_source_reference_tbl
4116 ,l_pm_source_code_tbl
4117 ,l_description_tbl
4118 ,l_attribute_category_tbl
4119 ,l_attribute1_tbl
4120 ,l_attribute2_tbl
4121 ,l_attribute3_tbl
4122 ,l_attribute4_tbl
4123 ,l_attribute5_tbl
4124 ,l_attribute6_tbl
4125 ,l_attribute7_tbl
4126 ,l_attribute8_tbl
4127 ,l_attribute9_tbl
4128 ,l_attribute10_tbl
4129 ,l_attribute11_tbl
4130 ,l_attribute12_tbl
4131 ,l_attribute13_tbl
4132 ,l_attribute14_tbl
4133 ,l_attribute15_tbl
4134 ,l_source_proj_element_id_tbl
4135 ,l_source_element_ver_id_tbl
4136 ,l_type_id_tbl
4137 ,l_due_date_tbl
4138 ,l_status_code_tbl ;
4139 CLOSE l_source_deliverables ;
4140
4141 ELSE
4142
4143 IF nvl(p_dlv_element_id_tbl.LAST,0) > 0 THEN
4144 FOR i IN p_dlv_element_id_tbl.FIRST..p_dlv_element_id_tbl.LAST LOOP
4145 l_element_number_tbl.extend ;
4146 l_name_tbl.extend ;
4147 l_manager_person_id_tbl.extend ;
4148 l_carrying_out_org_id_tbl.extend ;
4149 l_progress_weight_tbl.extend ;
4150 l_pm_source_reference_tbl.extend ;
4151 l_pm_source_code_tbl.extend ;
4152 l_description_tbl.extend ;
4153 l_attribute_category_tbl.extend ;
4154 l_attribute1_tbl.extend ;
4155 l_attribute2_tbl.extend ;
4156 l_attribute3_tbl.extend ;
4157 l_attribute4_tbl.extend ;
4158 l_attribute5_tbl.extend ;
4159 l_attribute6_tbl.extend ;
4160 l_attribute7_tbl.extend ;
4161 l_attribute8_tbl.extend ;
4162 l_attribute9_tbl.extend ;
4163 l_attribute10_tbl.extend ;
4164 l_attribute11_tbl.extend ;
4165 l_attribute12_tbl.extend ;
4166 l_attribute13_tbl.extend ;
4167 l_attribute14_tbl.extend ;
4168 l_attribute15_tbl.extend ;
4169 l_source_proj_element_id_tbl.extend ;
4170 l_source_element_ver_id_tbl.extend ;
4171 l_type_id_tbl.extend ;
4172 l_due_date_tbl.extend ;
4173 l_status_code_tbl.extend ;
4174
4175 OPEN l_proj_element_data(p_dlv_element_id_tbl(i)) ;
4176 FETCH l_proj_element_data INTO
4177 l_element_number_tbl(i)
4178 ,l_name_tbl(i)
4179 ,l_manager_person_id_tbl(i)
4180 ,l_carrying_out_org_id_tbl(i)
4181 ,l_progress_weight_tbl(i)
4182 ,l_pm_source_reference_tbl(i)
4183 ,l_pm_source_code_tbl(i)
4184 ,l_description_tbl(i)
4185 ,l_attribute_category_tbl(i)
4186 ,l_attribute1_tbl(i)
4187 ,l_attribute2_tbl(i)
4188 ,l_attribute3_tbl(i)
4189 ,l_attribute4_tbl(i)
4190 ,l_attribute5_tbl(i)
4191 ,l_attribute6_tbl(i)
4192 ,l_attribute7_tbl(i)
4193 ,l_attribute8_tbl(i)
4194 ,l_attribute9_tbl(i)
4195 ,l_attribute10_tbl(i)
4196 ,l_attribute11_tbl(i)
4197 ,l_attribute12_tbl(i)
4198 ,l_attribute13_tbl(i)
4199 ,l_attribute14_tbl(i)
4200 ,l_attribute15_tbl(i)
4201 ,l_source_proj_element_id_tbl(i)
4202 ,l_source_element_ver_id_tbl(i)
4203 ,l_type_id_tbl(i)
4204 ,l_due_date_tbl(i)
4205 ,l_status_code_tbl(i);
4206 CLOSE l_proj_element_data ;
4207
4208 END LOOP ;
4209
4210 END IF ;
4211
4212 END IF ;
4213
4214 IF l_debug_mode = 'Y' THEN
4215 pa_debug.g_err_stage:= 'Populating PA_PROJ_ELEMENTS ';
4216 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4217 END IF;
4218
4219 -- Validate unique deliverable number
4220 IF l_debug_mode = 'Y' THEN
4221 pa_debug.g_err_stage := 'Check for Duplicate Deliverable Number ';
4222 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4223 END IF ;
4224
4225 IF p_calling_context <> 'COPY_PROJECT' THEN
4226
4227 IF nvl(l_element_number_tbl.LAST,0)> 0 THEN -- Included for 4468344
4228 FOR i IN l_element_number_tbl.FIRST..l_element_number_tbl.LAST LOOP
4229 l_y_or_n := PA_PROJ_ELEMENTS_UTILS.Check_element_Number_Unique
4230 (p_element_number => l_element_number_tbl(i)
4231 ,p_element_id => null
4232 ,p_project_id => p_target_project_id
4233 ,p_structure_id => l_parent_structure_id
4234 ,p_object_type => 'PA_DELIVERABLES'
4235 );
4236
4237 IF nvl(l_y_or_n,'Y') = 'N' THEN
4238 PA_UTILS.ADD_MESSAGE('PA','PA_ENTER_OTHER_PREFIX') ;
4239 -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4240 RAISE Invalid_Arg_Exc_Dlv ;
4241 -- END IF ;
4242 pa_debug.g_err_stage := 'Duplicate Deliverable Number ';
4243 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4244 END IF ;
4245
4246 END LOOP ;
4247 END IF ; -- Included for 4468344
4248 END IF ;
4249
4250 -- Proceed if only something is fetched.
4251 IF nvl(l_element_number_tbl.LAST,0)> 0 THEN
4252 -- If clause added for bug# 3429221
4253
4254 -- Populate pa_proj_element table
4255 FORALL i IN l_element_number_tbl.FIRST..l_element_number_tbl.LAST
4256 INSERT INTO PA_PROJ_ELEMENTS
4257 ( proj_element_id
4258 ,project_id
4259 ,object_type
4260 ,element_number
4261 ,name
4262 ,status_code
4263 ,creation_date
4264 ,created_by
4265 ,last_update_date
4266 ,last_updated_by
4267 ,description
4268 ,pm_source_reference
4269 ,pm_source_code
4270 ,manager_person_id
4271 ,carrying_out_organization_id
4272 ,record_version_number
4273 ,last_update_login
4274 ,attribute_category
4275 ,attribute1
4276 ,attribute2
4277 ,attribute3
4278 ,attribute4
4279 ,attribute5
4280 ,attribute6
4281 ,attribute7
4282 ,attribute8
4283 ,attribute9
4284 ,attribute10
4285 ,attribute11
4286 ,attribute12
4287 ,attribute13
4288 ,attribute14
4289 ,attribute15
4290 ,parent_structure_id
4291 ,type_id
4292 ,progress_weight
4293 ,source_object_id
4294 ,source_object_type
4295 )
4296 VALUES
4297 ( PA_TASKS_S.NEXTVAL
4298 ,p_target_project_id
4299 ,'PA_DELIVERABLES'
4300 ,l_element_number_tbl(i)
4301 ,l_name_tbl(i)
4302 ,l_status_code_tbl(i) -- Bug#3515852 'DLVR_NOT_STARTED'
4303 ,sysdate
4304 ,fnd_global.user_id
4305 ,sysdate
4306 ,fnd_global.user_id
4307 ,l_description_tbl(i)
4308 ,l_pm_source_reference_tbl(i)
4309 ,l_pm_source_code_tbl(i)
4310 ,l_manager_person_id_tbl(i)
4311 ,l_carrying_out_org_id_tbl(i)
4312 ,1
4313 ,fnd_global.login_id
4314 ,l_attribute_category_tbl(i)
4315 ,l_attribute1_tbl(i)
4316 ,l_attribute2_tbl(i)
4317 ,l_attribute3_tbl(i)
4318 ,l_attribute4_tbl(i)
4319 ,l_attribute5_tbl(i)
4320 ,l_attribute6_tbl(i)
4321 ,l_attribute7_tbl(i)
4322 ,l_attribute8_tbl(i)
4323 ,l_attribute9_tbl(i)
4324 ,l_attribute10_tbl(i)
4325 ,l_attribute11_tbl(i)
4326 ,l_attribute12_tbl(i)
4327 ,l_attribute13_tbl(i)
4328 ,l_attribute14_tbl(i)
4329 ,l_attribute15_tbl(i)
4330 ,l_parent_structure_id
4331 ,l_type_id_tbl(i)
4332 ,l_progress_weight_tbl(i)
4333 ,p_target_project_id
4334 ,'PA_PROJECTS'
4335 )
4336 RETURNING proj_element_id
4337 BULK COLLECT INTO l_proj_element_id_tbl ;
4338
4339 IF l_debug_mode = 'Y' THEN
4340 pa_debug.g_err_stage:= 'Populating PA_PROJ_ELEMENT_VERSIONS ';
4341 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4342 END IF;
4343
4344 -- Populate pa_proj_element_version table
4345 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
4346 INSERT INTO PA_PROJ_ELEMENT_VERSIONS
4347 ( element_version_id
4348 ,proj_element_id
4349 ,object_type
4350 ,project_id
4351 ,parent_structure_version_id
4352 ,creation_date
4353 ,created_by
4354 ,last_update_date
4355 ,last_updated_by
4356 ,record_version_number
4357 ,last_update_login
4358 ,source_object_id
4359 ,source_object_type
4360 )
4361 VALUES
4362 (pa_proj_element_versions_s.nextval
4363 ,l_proj_element_id_tbl(i)
4364 ,'PA_DELIVERABLES'
4365 ,p_target_project_id
4366 ,l_parent_structure_version_id
4367 ,SYSDATE
4368 ,fnd_global.user_id
4369 ,SYSDATE
4370 ,fnd_global.user_id
4371 ,1
4372 ,fnd_global.login_id
4373 ,p_target_project_id
4374 ,'PA_PROJECTS'
4375 )
4376 RETURNING element_version_id
4377 BULK COLLECT INTO l_target_element_ver_id_tbl ;
4378
4379 IF l_debug_mode = 'Y' THEN
4380 pa_debug.g_err_stage:= 'Populating PA_PROJ_ELEM_VER_SCHEDULE ';
4381 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4382 END IF;
4383
4384
4385 -- Populate pa_proj_elem_ver_schedule table
4386 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
4387 INSERT INTO PA_PROJ_ELEM_VER_SCHEDULE(
4388 pev_schedule_id
4389 ,element_version_id
4390 ,project_id
4391 ,proj_element_id
4392 ,creation_date
4393 ,created_by
4394 ,last_update_date
4395 ,last_updated_by
4396 ,last_update_login
4397 ,scheduled_finish_date
4398 ,actual_finish_date
4399 ,record_version_number
4400 ,source_object_id
4401 ,source_object_type
4402 )
4403 VALUES
4404 (
4405 pa_proj_elem_ver_schedule_s.nextval
4406 ,l_target_element_ver_id_tbl(i)
4407 ,p_target_project_id
4408 ,l_proj_element_id_tbl(i)
4409 ,SYSDATE
4410 ,fnd_global.user_id
4411 ,SYSDATE
4412 ,fnd_global.user_id
4413 ,fnd_global.login_id
4414 ,decode(p_calling_context,'COPY_PROJECT', -- 3493612
4415 PA_DELIVERABLE_UTILS.GET_ADJUSTED_DATES
4416 (p_target_project_id
4417 ,l_due_date_tbl(i)
4418 ,p_delta
4419 ), l_due_date_tbl(i))
4420 ,null
4421 ,1
4422 ,p_target_project_id
4423 ,'PA_PROJECTS'
4424 ) ;
4425
4426 IF l_debug_mode = 'Y' THEN
4427 pa_debug.g_err_stage:= 'Populating PA_OBJECT_RELATIONSHIPS ';
4428 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4429 END IF;
4430
4431
4432 -- populate the object relationships table for STRUCTURE_TO_DELIVERABLE
4433 FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
4434 INSERT INTO PA_OBJECT_RELATIONSHIPS(
4435 object_relationship_id
4436 ,object_type_from
4437 ,object_id_from1
4438 ,object_type_to
4439 ,object_id_to1
4440 ,relationship_type
4441 ,created_by
4442 ,creation_date
4443 ,last_updated_by
4444 ,last_update_date
4445 ,object_id_from2
4446 ,object_id_to2
4447 ,relationship_subtype
4448 ,record_version_number
4449 ,last_update_login
4450 )
4451 VALUES
4452 (
4453 pa_object_relationships_s.nextval
4454 ,'PA_STRUCTURES'
4455 ,l_parent_structure_version_id
4456 ,'PA_DELIVERABLES'
4457 ,l_target_element_ver_id_tbl(i)
4458 ,'S'
4459 ,fnd_global.user_id
4460 ,SYSDATE
4461 ,fnd_global.user_id
4462 ,SYSDATE
4463 ,l_parent_structure_id
4464 ,l_proj_element_id_tbl(i)
4465 ,'STRUCTURE_TO_DELIVERABLE'
4466 ,1
4467 ,fnd_global.login_id
4468 ) ;
4469
4470
4471 IF nvl(l_proj_element_id_tbl.LAST,0)>0 THEN
4472 IF (nvl(p_association_flag,'N') = 'Y' AND p_calling_context = 'COPY_PROJECT' ) THEN
4473 INSERT INTO PA_OBJECT_RELATIONSHIPS(
4474 object_relationship_id
4475 ,object_type_from
4476 ,object_id_from1
4477 ,object_type_to
4478 ,object_id_to1
4479 ,relationship_type
4480 ,created_by
4481 ,creation_date
4482 ,last_updated_by
4483 ,last_update_date
4484 ,object_id_from2
4485 ,object_id_to2
4486 ,relationship_subtype
4487 ,record_version_number
4488 ,last_update_login
4489 )
4490 SELECT
4491 pa_object_relationships_s.nextval
4492 ,'PA_TASKS'
4493 ,null
4494 ,'PA_DELIVERABLES'
4495 ,dlv2.element_version_id
4496 ,'A'
4497 ,fnd_global.user_id
4498 ,SYSDATE
4499 ,fnd_global.user_id
4500 ,SYSDATE
4501 ,tsk1.proj_element_id
4502 ,dlv1.proj_element_id
4503 ,'TASK_TO_DELIVERABLE'
4504 ,1
4505 ,fnd_global.login_id
4506 FROM pa_proj_elements tsk1
4507 ,pa_proj_elements dlv1
4508 ,pa_proj_element_versions dlv2
4509 ,pa_object_relationships obj
4510 WHERE dlv1.project_id = p_target_project_id
4511 AND dlv1.object_type = 'PA_DELIVERABLES'
4512 AND dlv1.attribute15 = obj.object_id_to2
4513 AND obj.relationship_type = 'A'
4514 AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
4515 AND obj.object_type_from = 'PA_TASKS'
4516 AND obj.object_type_to = 'PA_DELIVERABLES'
4517 AND tsk1.project_id = p_target_project_id
4518 AND tsk1.attribute15 = obj.object_id_from2
4519 AND tsk1.object_type = 'PA_TASKS'
4520 AND dlv1.proj_element_id = dlv2.proj_element_id ;
4521 END IF ;
4522 END IF ;
4523
4524 FOR i IN l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST LOOP
4525
4526 IF (nvl(p_association_flag,'N') = 'Y' AND p_calling_context = 'COPY_DELIVERABLES' ) THEN
4527
4528 INSERT INTO PA_OBJECT_RELATIONSHIPS(
4529 object_relationship_id
4530 ,object_type_from
4531 ,object_id_from1
4532 ,object_type_to
4533 ,object_id_to1
4534 ,relationship_type
4535 ,created_by
4536 ,creation_date
4537 ,last_updated_by
4538 ,last_update_date
4539 ,object_id_from2
4540 ,object_id_to2
4541 ,relationship_subtype
4542 ,record_version_number
4543 ,last_update_login
4544 )
4545 SELECT
4546 pa_object_relationships_s.nextval
4547 ,'PA_TASKS'
4548 ,obj.object_id_from1
4549 ,'PA_DELIVERABLES'
4550 ,l_target_element_ver_id_tbl(i)
4551 ,'A'
4552 ,fnd_global.user_id
4553 ,SYSDATE
4554 ,fnd_global.user_id
4555 ,SYSDATE
4556 ,obj.object_id_from2
4557 ,l_proj_element_id_tbl(i)
4558 ,'TASK_TO_DELIVERABLE'
4559 ,1
4560 ,fnd_global.login_id
4561 FROM pa_object_relationships obj
4562 WHERE obj.object_id_to2 = p_dlv_element_id_tbl(i)
4563 AND obj.object_type_to = 'PA_DELIVERABLES'
4564 AND obj.object_type_from = 'PA_TASKS'
4565 AND obj.relationship_type = 'A'
4566 AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE' ;
4567
4568 END IF ;
4569
4570 IF nvl(p_dlv_attachments_flag,'N') = 'Y' THEN
4571
4572 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS
4573 (
4574 X_from_entity_name => 'PA_DLVR_DOC_ATTACH'
4575 ,X_from_pk1_value => l_source_element_ver_id_tbl(i)
4576 ,X_to_entity_name => 'PA_DLVR_DOC_ATTACH'
4577 ,X_to_pk1_value => l_target_element_ver_id_tbl(i)
4578 ,X_created_by => fnd_global.user_id
4579 ,X_last_update_login => fnd_global.login_id
4580 );
4581
4582 FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS
4583 (
4584 X_from_entity_name => 'PA_DVLR_ATTACH'
4585 ,X_from_pk1_value => l_source_element_ver_id_tbl(i)
4586 ,X_to_entity_name => 'PA_DVLR_ATTACH'
4587 ,X_to_pk1_value => l_target_element_ver_id_tbl(i)
4588 ,X_created_by => fnd_global.user_id
4589 ,X_last_update_login => fnd_global.login_id
4590 );
4591
4592 END IF ;
4593
4594 -- Commented the IF clause as OKE maintains entry for both Item Based
4595 -- Deliverable and Non Item Based Deliverable in there table . On the basis
4596 -- of p_copy_item_details_flag the OKE api will either populate 'NULL' for
4597 -- item attributes OR it will copy from source deliverable.
4598
4599 -- IF ( nvl(p_item_details_flag,'N') = 'Y' OR p_calling_context = 'COPY_PROJECT') THEN
4600
4601 -- 3469876 added the following check
4602
4603 -- After selective copy project , item info. should not be copied
4604 -- in default for deliverable. Hence commenting the below mentioned code.
4605
4606 --IF p_calling_context = 'COPY_PROJECT' then
4607 -- l_item_details_flag := 'Y' ;
4608 -- ELSE
4609 -- l_item_details_flag := nvl(p_item_details_flag,'N');
4610 --END IF ;
4611 -- 3469876
4612
4613 OKE_DELIVERABLE_UTILS_PUB.COPY_ITEM
4614 (p_source_project_id => p_source_project_id
4615 ,p_target_project_id => p_target_project_id
4616 ,p_source_deliverable_id => l_source_element_ver_id_tbl(i)
4617 ,p_target_deliverable_id => l_target_element_ver_id_tbl(i)
4618 ,p_target_deliverable_number => l_element_number_tbl(i)
4619 ,p_copy_item_details_flag => nvl(p_item_details_flag,'N') -- 3469876 changed the parameter
4620 ,x_return_status => x_return_status
4621 ,x_msg_count => x_msg_count
4622 ,x_msg_data => x_msg_data
4623 );
4624
4625 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4626 RAISE Invalid_Arg_Exc_Dlv ;
4627 END IF ;
4628
4629 -- END IF ;
4630
4631 IF (nvl(p_dlv_actions_flag,'N') = 'Y' OR p_calling_context = 'COPY_PROJECT') THEN
4632
4633 IF l_debug_mode = 'Y' THEN
4634 pa_debug.g_err_stage:= 'Calling PA_ACTIONS_PUB.COPY_ACTIONS';
4635 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4636 END IF;
4637 PA_ACTIONS_PUB.COPY_ACTIONS
4638 (p_api_version => p_api_version
4639 ,p_init_msg_list => p_init_msg_list
4640 ,p_commit => p_commit
4641 ,p_validate_only => p_validate_only
4642 ,p_validation_level => p_validation_level
4643 ,p_calling_module => p_calling_module
4644 ,p_debug_mode => p_debug_mode
4645 ,p_max_msg_count => p_max_msg_count
4646 ,p_source_object_id => l_source_proj_element_id_tbl(i)
4647 ,p_source_object_type => 'PA_DELIVERABLES'
4648 ,p_target_object_id => l_proj_element_id_tbl(i)
4649 ,p_target_object_type => 'PA_DELIVERABLES'
4650 ,p_source_project_id => p_source_project_id
4651 ,p_target_project_id => p_target_project_id
4652 ,p_task_id => null
4653 ,p_task_ver_id => null
4654 ,p_carrying_out_organization_id => l_carrying_out_org_id_tbl(i)
4655 ,p_pm_source_reference => l_pm_source_reference_tbl(i)
4656 ,p_pm_source_code => l_pm_source_code_tbl(i)
4657 ,x_return_status => x_return_status
4658 ,x_msg_count => x_msg_count
4659 ,x_msg_data => x_msg_data
4660 ) ;
4661
4662 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4663 RAISE Invalid_Arg_Exc_Dlv ;
4664 END IF ;
4665 END IF ;
4666
4667 END LOOP ;
4668
4669 END IF ;
4670
4671 --Bug # 3429393
4672 --If the Calling context is Copy External then
4673 --We have to call create associations in bulk API
4674 --which will associate the selected deliverables
4675 --automatically to the task in which context the Copy External Page was called.
4676 IF p_task_id IS NOT NULL AND p_task_version_id IS NOT NULL AND p_calling_context='COPY_EXTERNAL'
4677 THEN
4678 PA_DELIVERABLE_PVT.CREATE_ASSOCIATIONS_IN_BULK
4679 (p_api_version => p_api_version
4680 ,p_init_msg_list => p_init_msg_list
4681 ,p_commit => p_commit
4682 ,p_validate_only => p_validate_only
4683 ,p_validation_level => p_validation_level
4684 ,p_calling_module => p_calling_module
4685 ,p_debug_mode => p_debug_mode
4686 ,p_max_msg_count => p_max_msg_count
4687 ,p_element_id_tbl => l_proj_element_id_tbl
4688 ,p_version_id_tbl => l_target_element_ver_id_tbl
4689 ,p_element_name_tbl => l_name_tbl
4690 ,p_element_number_tbl => l_element_number_tbl
4691 ,p_task_or_dlv_elt_id => p_task_id
4692 ,p_task_or_dlv_ver_id => p_task_version_id
4693 ,p_project_id => p_target_project_id
4694 ,p_task_or_dlv => 'PA_TASKS'
4695 ,x_return_status => x_return_status
4696 ,x_msg_count => x_msg_count
4697 ,x_msg_data => x_msg_data
4698 );
4699 END IF;
4700
4701
4702 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4703 RAISE Invalid_Arg_Exc_Dlv ;
4704 END IF ;
4705
4706 /*Stubbed Out Auto Initiate Demand on Project Approval Functionality
4707 Bug 3819086
4708
4709 --Bug 3611598 <<Start>>
4710
4711 IF p_calling_context = 'COPY_PROJECT' THEN
4712
4713 OPEN c_project_details ;
4714 -- 3671408 added l_project_number
4715 FETCH c_project_details INTO l_project_type,l_status_code,l_project_sys_status_code, l_project_number ;
4716 CLOSE c_project_details;
4717
4718 IF p_debug_mode = 'Y' THEN
4719 pa_debug.g_err_stage:= 'Project Type is :'||l_project_type ;
4720 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
4721 pa_debug.g_err_stage:= 'Project Status Code is :'||l_status_code ;
4722 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
4723 -- added for bug# 3671408
4724 pa_debug.g_err_stage:= 'Project System Status Code is :'||l_project_sys_status_code ;
4725 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
4726 END IF;
4727
4728 --Check whether the project's system status code is Approved
4729 IF nvl(l_project_sys_status_code,'-99') = 'APPROVED' THEN
4730 --If Yes,Find Whether Workflow is enabled for the project
4731
4732 IF (l_project_type IS NOT NULL AND l_status_code IS NOT NULL) THEN
4733 pa_project_stus_utils.check_wf_enabled
4734 (x_project_status_code => l_status_code,
4735 x_project_type => l_project_type,
4736 x_project_id => p_target_project_id,
4737 x_wf_item_type => l_item_type,
4738 x_wf_process => l_wf_process,
4739 x_wf_enabled_flag => l_wf_enabled_flag,
4740 x_err_code => l_err_code
4741 );
4742 --Workflow is NOT coupled to changing statues.
4743 -- So, the x_err_code for the aforementioned Check_Wf_Enabled
4744 --- is IGNORED if x_err_code > 0.
4745 IF p_debug_mode = 'Y' THEN
4746 pa_debug.g_err_stage:= 'The Error Code is ' ||l_err_code ;
4747 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
4748 pa_debug.g_err_stage:= 'The Workflow Enabled Flag is ' ||l_wf_enabled_flag ;
4749 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
4750 END IF;
4751
4752 IF (l_err_code > 0) THEN
4753 l_err_code := 0;
4754 END IF;
4755
4756 IF l_err_code = 0 THEN
4757 IF nvl(l_wf_enabled_flag,'N') <> 'Y' THEN
4758 --If Workflow is not enabled then Place call to wrapper API
4759
4760 -- 3671408 retrieving segment1 value for project and passing the retrieved
4761 -- value to api
4762
4763 PA_ACTIONS_PUB.RUN_ACTION_CONC_PROCESS_WRP
4764 (
4765 p_project_id => p_target_project_id
4766 ,p_project_number => l_project_number -- added for bug# 3671408
4767 ,x_msg_count => x_msg_count
4768 ,x_msg_data => x_msg_data
4769 ,x_return_status => x_return_status
4770 );
4771 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4772 RAISE FND_API.G_EXC_ERROR;
4773 END IF;
4774 END IF;
4775 END IF;
4776 END IF;
4777 END IF;
4778 END IF;
4779 --End of Changes Bug 3611598
4780
4781 End of Commenting for Bug 3819086 */
4782
4783 IF p_debug_mode = 'Y' THEN
4784 pa_debug.g_err_stage:= 'Exiting COPY_DELIVERABLES' ;
4785 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
4786 pa_debug.reset_curr_function;
4787 END IF;
4788
4789 EXCEPTION
4790 WHEN Invalid_Arg_Exc_Dlv THEN
4791 x_return_status := FND_API.G_RET_STS_ERROR;
4792 l_msg_count := FND_MSG_PUB.count_msg;
4793
4794 IF l_debug_mode = 'Y' THEN
4795 pa_debug.g_err_stage := 'inside invalid arg exception of COPY_DELIVERABLES';
4796 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
4797 END IF;
4798
4799 IF l_msg_count = 1 THEN
4800 PA_INTERFACE_UTILS_PUB.get_messages
4801 (p_encoded => FND_API.G_TRUE,
4802 p_msg_index => 1,
4803 p_msg_count => l_msg_count,
4804 p_msg_data => l_msg_data,
4805 p_data => l_data,
4806 p_msg_index_out => l_msg_index_out);
4807 x_msg_data := l_data;
4808 x_msg_count := l_msg_count;
4809 ELSE
4810 x_msg_count := l_msg_count;
4811 END IF;
4812 IF l_debug_mode = 'Y' THEN
4813 pa_debug.reset_curr_function;
4814 END IF ;
4815 RETURN;
4816 WHEN OTHERS THEN
4817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4818 x_msg_count := 1;
4819 x_msg_data := SQLERRM;
4820
4821 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
4822 ,p_procedure_name => 'COPY_DELIVERABLES');
4823
4824 IF p_debug_mode = 'Y' THEN
4825 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4826 pa_debug.write('COPY_DELIVERABLES: ' || g_module_name,pa_debug.g_err_stage,5);
4827 pa_debug.reset_curr_function;
4828 END IF;
4829 RAISE;
4830 END COPY_DELIVERABLES ;
4831
4832 END PA_DELIVERABLE_PVT;