[Home] [Help]
PACKAGE BODY: APPS.PA_DELIVERABLE_PUB
Source
1 PACKAGE BODY PA_DELIVERABLE_PUB AS
2 /* $Header: PADLVPUB.pls 120.2.12010000.2 2008/11/12 15:33:17 rthumma ship $ */
3
4 g_module_name VARCHAR2(100) := 'PA_DELIVERABLE_PUB';
5 Invalid_Arg_Exc_Dlv EXCEPTION ;
6
7 Invalid_Arg_Exc_WP Exception;
8
9 -- Procedure : Create_Deliverable
10 -- Type : PUBLIC
11 -- Purpose : Create Deliveable Page calls this procedure to create deliverables
12 -- Note : Check for input parameter validations and short name uniqueness.
13 -- : Retrieve carrying_out_organization_id and structure_info.
14 -- : Call Create_Deliveable procedure of the pa_deliverable_pvt package.
15 -- Assumptions : None
16
17 -- Parameters Type Required Description and Purpose
18 -- --------------------------- ------ -------- --------------------------------------------------------
19 -- p_api_version NUMBER N 1.0
20 -- p_init_msg_list VARCHAR2 N := FND_API.G_TRUE
21 -- p_commit VARCHAR2 N := FND_API.G_FALSE
22 -- p_validate_only VARCHAR2 N := FND_API.G_TRUE
23 -- p_validation_level NUMBER N := FND_API.G_VALID_LEVEL_FULL
24 -- p_calling_module VARCHAR2 N := 'SELF_SERVICE'
25 -- p_debug_mode VARCHAR2 N := 'N'
26 -- p_max_msg_count NUMBER N := NULL
27 -- p_record_version_number NUMBER N := 1
28 -- p_object_type VARCHAR2 N Object Type Default 'PA_DELIVERABLES'
29 -- p_project_id NUMBER Y Project Id
30 -- p_dlvr_number VARCHAR2 Y Deliverable Number
31 -- p_dlvr_name VARCHAR2 Y Deliverable Name
32 -- p_dlvr_description VARCHAR2 N Description
33 -- p_dlvr_owner_id NUMBER N Deliverable Owner Id
34 -- p_dlvr_owner_name VARCHAR2 N Delivearble Owner Name
35 -- p_carrying_out_org_id NUMBER N Project Carrying Out Organization Id
36 -- p_carrying_out_org_name VARCHAR2 N Project Carrying Out Organization Name
37 -- p_dlvr_version_id NUMBER N Deliverable Version Id
38 -- p_status_code VARCHAR2 N Delivearble Status
39 -- p_parent_structure_id NUMBER N Deliverable Parent Structure Id
40 -- p_dlvr_type_id NUMBER N Deliverable Type Id
41 -- p_dlvr_type_name VARCHAR2 N Deliverable Type Name
42 -- p_progress_weight NUMBER N Progress Weight
43 -- p_scheduled_finish_date DATE N Scheduled Finish Date
44 -- p_actual_finish_date DATE N Actual Finish Date
45 -- p_task_id NUMBER N
46 -- p_task_version_id NUMBER N
47 -- p_task_name VARCHAR2 N
48 -- p_attribute_category VARCHAR2 N
49 -- p_attribute1 VARCHAR2 N
50 -- p_attribute2 VARCHAR2 N
51 -- p_attribute3 VARCHAR2 N
52 -- p_attribute4 VARCHAR2 N
53 -- p_attribute5 VARCHAR2 N
54 -- p_attribute6 VARCHAR2 N
55 -- p_attribute7 VARCHAR2 N
56 -- p_attribute8 VARCHAR2 N
57 -- p_attribute9 VARCHAR2 N
58 -- p_attribute10 VARCHAR2 N
59 -- p_attribute11 VARCHAR2 N
60 -- p_attribute12 VARCHAR2 N
61 -- p_attribute13 VARCHAR2 N
62 -- p_attribute14 VARCHAR2 N
63 -- p_attribute15 VARCHAR2 N
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 := NULL
91 , p_dlvr_type_id IN PA_PROJ_ELEMENTS.TYPE_ID%TYPE := NULL
92 , p_dlvr_type_name IN VARCHAR2 := NULL
93 , p_progress_weight IN PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL
94 , p_scheduled_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE := NULL
95 , p_actual_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.ACTUAL_FINISH_DATE%TYPE := NULL
96 , p_task_id IN NUMBER := NULL
97 , p_task_version_id IN NUMBER := NULL
98 , p_task_name IN VARCHAR2 := NULL
99 , p_deliverable_reference IN VARCHAR2 := NULL
100 , p_attribute_category IN PA_PROJ_ELEMENTS.ATTRIBUTE_CATEGORY%TYPE := NULL
101 , p_attribute1 IN PA_PROJ_ELEMENTS.ATTRIBUTE1%TYPE := NULL
102 , p_attribute2 IN PA_PROJ_ELEMENTS.ATTRIBUTE2%TYPE := NULL
103 , p_attribute3 IN PA_PROJ_ELEMENTS.ATTRIBUTE3%TYPE := NULL
104 , p_attribute4 IN PA_PROJ_ELEMENTS.ATTRIBUTE4%TYPE := NULL
105 , p_attribute5 IN PA_PROJ_ELEMENTS.ATTRIBUTE5%TYPE := NULL
106 , p_attribute6 IN PA_PROJ_ELEMENTS.ATTRIBUTE6%TYPE := NULL
107 , p_attribute7 IN PA_PROJ_ELEMENTS.ATTRIBUTE7%TYPE := NULL
108 , p_attribute8 IN PA_PROJ_ELEMENTS.ATTRIBUTE8%TYPE := NULL
109 , p_attribute9 IN PA_PROJ_ELEMENTS.ATTRIBUTE9%TYPE := NULL
110 , p_attribute10 IN PA_PROJ_ELEMENTS.ATTRIBUTE10%TYPE := NULL
111 , p_attribute11 IN PA_PROJ_ELEMENTS.ATTRIBUTE11%TYPE := NULL
112 , p_attribute12 IN PA_PROJ_ELEMENTS.ATTRIBUTE12%TYPE := NULL
113 , p_attribute13 IN PA_PROJ_ELEMENTS.ATTRIBUTE13%TYPE := NULL
114 , p_attribute14 IN PA_PROJ_ELEMENTS.ATTRIBUTE14%TYPE := NULL
115 , p_attribute15 IN PA_PROJ_ELEMENTS.ATTRIBUTE15%TYPE := NULL
116 , p_item_id IN NUMBER := NULL
117 , p_inventory_org_id IN NUMBER := NULL
118 , p_quantity IN NUMBER := NULL
119 , p_uom_code IN VARCHAR2 := NULL
120 , p_item_description IN VARCHAR2 := NULL
121 , p_unit_price IN NUMBER := NULL
122 , p_unit_number IN VARCHAR2 := NULL
123 , p_currency_code IN VARCHAR2 := NULL
124 , p_pm_source_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR /* Bug no. 3651113 */
125 , p_dlvr_item_id OUT NOCOPY PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE --File.Sql.39 bug 4440895
126 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
127 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
128 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
129 )
130 IS
131
132 l_msg_count NUMBER := 0;
133 l_element_id NUMBER := NULL;
134 l_data VARCHAR2(2000);
135 l_return_status VARCHAR2(1);
136 l_msg_data VARCHAR2(2000);
137 l_msg_index_out NUMBER;
138 l_debug_mode VARCHAR2(1);
139
140 l_debug_level2 CONSTANT NUMBER := 2;
141 l_debug_level3 CONSTANT NUMBER := 3;
142 l_debug_level4 CONSTANT NUMBER := 4;
143 l_debug_level5 CONSTANT NUMBER := 5;
144
145 l_structure_type VARCHAR2(150) := 'DELIVERABLE';
146 l_structure_id NUMBER;
147 l_element_structure_id NUMBER;
148 is_dlvr_number_unique VARCHAR2(1) := 'N';
149 l_carrying_out_org NUMBER := NULL;
150
151 l_dlvr_description PA_PROJ_ELEMENTS.DESCRIPTION%TYPE;
152 l_dlvr_owner_id PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE;
153 l_carrying_out_org_id PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE;
154 l_progress_weight PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE;
155 l_scheduled_finish_date DATE;
156 l_actual_finish_date DATE;
157 l_dlvr_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
158
159 l_dlvr_prg_enabled VARCHAR2(1) := NULL;
160 l_dlvr_action_enabled VARCHAR2(1) := NULL;
161 l_status_code Pa_task_types.initial_status_code%TYPE := NULL;
162
163
164 l_item_dlv VARCHAR2(1) := NULL;
165 l_dlv_rec oke_amg_grp.dlv_rec_type;
166
167 l_project_number Pa_Projects_All.Segment1%TYPE;
168 l_task_number Pa_Proj_Elements.Name%TYPE;
169
170 l_item_id OKE_DELIVERABLES_B.ITEM_ID%TYPE;
171 l_inventory_org_id OKE_DELIVERABLES_B.INVENTORY_ORG_ID%TYPE;
172 l_quantity OKE_DELIVERABLES_B.QUANTITY%TYPE;
173 l_uom_code OKE_DELIVERABLES_B.UOM_CODE%TYPE;
174 l_item_description VARCHAR2(2000);
175 l_unit_price OKE_DELIVERABLES_B.UNIT_PRICE%TYPE;
176 l_unit_number OKE_DELIVERABLES_B.UNIT_NUMBER%TYPE;
177 l_currency_code OKE_DELIVERABLES_B.CURRENCY_CODE%TYPE;
178 l_dlv_elem_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
179 l_master_inv_org_id PA_PLAN_RES_DEFAULTS.item_master_id%TYPE;
180
181 BEGIN
182
183 x_msg_count := 0;
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
186
187 IF l_debug_mode = 'Y' THEN
188 PA_DEBUG.set_curr_function( p_function => 'CREATE_DELIVERABLE',
189 p_debug_mode => l_debug_mode );
190 END IF;
191
192 /*==============3435905 : FP M : Deliverables Changes For AMG - START ==============================*/
193
194 IF p_dlvr_description = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
195 l_dlvr_description := NULL;
196 ELSE
197 l_dlvr_description := p_dlvr_description;
198 END IF;
199
200 IF p_dlvr_owner_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
201 l_dlvr_owner_id := NULL;
202 ELSE
203 l_dlvr_owner_id := p_dlvr_owner_id;
204 END IF;
205
206 IF p_carrying_out_org_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
207 l_carrying_out_org_id := NULL;
208 ELSE
209 l_carrying_out_org_id := p_carrying_out_org_id;
210 END IF;
211
212 IF p_status_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
213 l_status_code := NULL;
214 ELSE
215 l_status_code := p_status_code;
216 END IF;
217
218 IF p_progress_weight = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
219 l_progress_weight := NULL;
220 ELSE
221 l_progress_weight := p_progress_weight;
222 END IF;
223
224 IF p_scheduled_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
225 l_scheduled_finish_date := NULL;
226 ELSE
227 l_scheduled_finish_date := p_scheduled_finish_date;
228 END IF;
229
230 IF p_actual_finish_date = PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE THEN
231 l_actual_finish_date := NULL;
232 ELSE
233 l_actual_finish_date := p_actual_finish_date;
234 END IF;
235
236 -- Fetching Task Name , Project Name to use as token in Error Messages.
237 IF (p_task_id IS NOT NULL AND p_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
238 SELECT name INTO l_task_number
239 FROM Pa_Proj_Elements
240 WHERE proj_element_id = p_task_id;
241 ELSE
242 l_task_number := null;
243 END IF;
244
245 SELECT segment1 INTO l_project_number
246 FROM Pa_Projects_All
247 WHERE project_id = p_project_id;
248
249 IF l_debug_mode = 'Y' THEN
250 Pa_Debug.WRITE(g_module_name, 'token values proj ['||l_Project_Number||'] task ['||l_task_Number||']',l_debug_level3);
251 END IF;
252
253 /*==============3435905 : FP M : Deliverables Changes For AMG - END ============================== */
254 IF l_debug_mode = 'Y' THEN
255 Pa_Debug.g_err_stage:= 'CREATE_DELIVERABLE : Printing Input parameters';
256 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
257 l_debug_level3);
258 Pa_Debug.WRITE(g_module_name,' P_PROJECT_ID '||':'|| p_project_id,
259 l_debug_level3);
260 Pa_Debug.WRITE(g_module_name,' P_DLVR_NUMBER '||':'|| p_dlvr_number,
261 l_debug_level3);
262 Pa_Debug.WRITE(g_module_name,' P_DLVR_NAME '||':'|| p_dlvr_name,
263 l_debug_level3);
264 Pa_Debug.WRITE(g_module_name,' P_DLVR_TYPE_ID '||':'|| p_dlvr_type_id,
265 l_debug_level3);
266 Pa_Debug.WRITE(g_module_name,' P_DLVR_TYPE_NAME '||':'|| p_dlvr_type_name,
267 l_debug_level3);
268 Pa_Debug.WRITE(g_module_name,' P_TASK_VERSION_ID '||':'|| p_task_version_id,
269 l_debug_level3);
270 Pa_Debug.WRITE(g_module_name,' P_TASK_NAME '||':'|| p_task_name,
271 l_debug_level3);
272 END IF;
273
274 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
275 FND_MSG_PUB.initialize;
276 END IF;
277
278 IF (p_commit = FND_API.G_TRUE) THEN
279 savepoint CREATE_DLVR_PUB;
280 END IF;
281
282 IF l_debug_mode = 'Y' THEN
283 Pa_Debug.g_err_stage:= 'Validating Input parameters';
284 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
285 l_debug_level3);
286 END IF;
287
288 -- Input parameter Validation Logic
289
290 --Bug Number 3861930
291 --Negative Progress Weight Entry Should not be allowed
292
293 IF p_progress_weight IS NOT NULL AND p_progress_weight < 0 THEN
294 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
295 p_msg_name => 'PA_NEG_PRG_NOT_ALLOWED');
296 x_return_status := FND_API.G_RET_STS_ERROR;
297 RAISE FND_API.G_EXC_ERROR;
298 END IF;
299 --End Bug Number 3861930
300
301 /*==============3435905 : FP M : Deliverables Changes For AMG - START ==============================*/
302 IF (p_calling_module = 'AMG') THEN
303 Pa_Deliverable_Utils.Validate_Deliverable
304 (
305 p_deliverable_id => null
306 , p_deliverable_reference => p_deliverable_reference
307 , p_dlvr_number => p_dlvr_number
308 , p_dlvr_name => p_dlvr_name
309 , px_dlvr_owner_id => l_dlvr_owner_id
310 , p_dlvr_owner_name => p_dlvr_owner_name
311 , p_dlvr_type_id => p_dlvr_type_id
312 , px_actual_finish_date => l_actual_finish_date
313 , px_progress_weight => l_progress_weight
314 , p_carrying_out_org_id => p_carrying_out_org_id
315 , px_status_code => l_status_code
316 , p_project_id => p_project_id
317 , p_task_id => p_task_id
318 , p_calling_mode => 'INSERT'
319 , x_return_status => x_return_status
320 , x_msg_count => x_msg_count
321 , x_msg_data => x_msg_data
322 );
323
324 IF p_debug_mode = 'Y' THEN
325 pa_debug.g_err_stage := 'Validated deliverable returns ['||x_return_status||']';
326 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
327 END IF;
328
329 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
332 RAISE FND_API.G_EXC_ERROR;
333 END IF;
334
335 -- Fetch dlvr_version_id from sequence
336 SELECT PA_PROJ_ELEMENT_VERSIONS_S.nextval
337 INTO l_dlvr_version_id
338 FROM DUAL;
339
340 /*==============3435905 : FP M : Deliverables Changes For AMG - END ============================== */
341
342 ELSE /* context <> 'AMG' */
343 IF (p_project_id IS NULL ) THEN
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 END IF;
346
347 IF (p_dlvr_number IS NULL ) THEN
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 END IF;
350
351 IF (p_dlvr_name IS NULL ) THEN
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 END IF;
354
355 IF (p_dlvr_type_id IS NULL ) THEN
356 IF (p_dlvr_type_name IS NULL ) THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 END IF;
359 END IF;
360
361
362 IF (p_dlvr_version_id IS NULL ) THEN
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 ELSE
365 l_dlvr_version_id := p_dlvr_version_id;
366 END IF;
367
368 END IF;
369 -- Business Logic
370
371 IF l_debug_mode = 'Y' THEN
372 Pa_Debug.WRITE(g_module_name,' Retrieving Carrying Out Organization Information ',
373 l_debug_level3);
374 END IF;
375
376 IF l_debug_mode = 'Y' THEN
377 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_UTILS.get_carrying_out_org called ',
378 l_debug_level3);
379 END IF;
380
381 -- retrieve carrying_out_organization_id
382
383 l_carrying_out_org := PA_DELIVERABLE_UTILS.get_carrying_out_org
384 (
385 p_project_id => p_project_id
386 ,p_task_id => p_task_id
387 );
388
389 IF (l_carrying_out_org IS NULL ) THEN
390 x_return_status := FND_API.G_RET_STS_ERROR;
391 END IF;
392
393 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
394 RAISE Invalid_Arg_Exc_WP;
395 END IF;
396
397 IF l_debug_mode = 'Y' THEN
398 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_UTILS.get_carrying_out_org ',
399 l_debug_level3);
400 END IF;
401
402 IF l_debug_mode = 'Y' THEN
403 Pa_Debug.WRITE(g_module_name,' Retrieving Structure Information ',
404 l_debug_level3);
405 END IF;
406
407 IF l_debug_mode = 'Y' THEN
408 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_UTILS.get_structure_info called ',
409 l_debug_level3);
410 END IF;
411
412 -- retrieve structure information
413
414 PA_DELIVERABLE_UTILS.get_structure_info
415 (
416 p_project_id => p_project_id
417 ,P_structure_type => l_structure_type
418 ,X_proj_element_id => l_structure_id
419 ,X_element_version_id => l_element_structure_id
420 ,x_return_status => x_return_status
421 ,x_msg_count => x_msg_count
422 ,x_msg_data => x_msg_data
423 );
424
425 IF l_debug_mode = 'Y' THEN
426 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_UTILS.get_structure_info ',
427 l_debug_level3);
428 END IF;
429
430
431 -- l_status_code := p_status_code;
432
433 -- retrieve default status code, if not passed
434
435 IF l_status_code IS NULL THEN
436
437 -- call PA_DELIVERABLE_UTILS.get_dlvr_type_info to retrieve default status code
438
439 IF l_debug_mode = 'Y' THEN
440 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_UTILS.get_dlvr_type_info called ',
441 l_debug_level3);
442 END IF;
443
444 PA_DELIVERABLE_UTILS.get_dlvr_type_info
445 (
446 p_dlvr_type_id => p_dlvr_type_id,
447 x_dlvr_prg_enabled => l_dlvr_prg_enabled,
448 x_dlvr_action_enabled => l_dlvr_action_enabled,
449 x_dlvr_default_status_code => l_status_code
450 );
451
452 IF l_debug_mode = 'Y' THEN
453 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_UTILS.get_dlvr_type_info ',
454 l_debug_level3);
455 END IF;
456
457 END IF;
458
459
460 IF l_debug_mode = 'Y' THEN
461 Pa_Debug.WRITE(g_module_name,' Checking Uniqueness for Deliverable Number ',
462 l_debug_level3);
463 END IF;
464
465 -- check for dlvr_number uniqueness
466 -- check for dlvr_number uniqueness
467
468 is_dlvr_number_unique := PA_PROJ_ELEMENTS_UTILS.Check_element_NUmber_Unique
469 (
470 p_element_number => p_dlvr_number
471 ,p_element_id => l_element_id
472 ,p_project_id => p_project_id
473 ,p_structure_id => l_structure_id
474 ,p_object_type => p_object_type
475 );
476 IF l_debug_mode = 'Y' THEN
477 Pa_Debug.WRITE(g_module_name,' Dlvr Num Unique['||p_dlvr_number||']['||is_dlvr_number_unique||']', l_debug_level3);
478 END IF;
479 -- if is_dlvr_number_unique is 'N' return with error
480
481 IF (is_dlvr_number_unique = 'N' ) THEN
482 IF (p_calling_module = 'AMG') THEN
483 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
484
485 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_NUMBER_DUPLICATE') ;
486 PA_UTILS.ADD_MESSAGE
487 (p_app_short_name => 'PA',
488 p_msg_name => 'PA_DLVR_VALID_ERR',
489 p_token1 => 'PROJECT',
490 p_value1 => l_project_number,
491 p_token2 => 'TASK',
492 p_value2 => l_task_number,
493 p_token3 => 'DLVR_REFERENCE',
494 p_value3 => p_deliverable_reference,
495 p_token4 => 'MESSAGE',
496 p_value4 => l_err_message
497 );
498 END IF;
499 x_return_status := FND_API.G_RET_STS_ERROR;
500 RAISE FND_API.G_EXC_ERROR;
501 END IF; /* context=AMG */
502 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
503 p_msg_name => 'PA_DLVR_NUMBER_EXISTS');
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 END IF;
506
507 -- else call create_deliveable procedure of pa_deliveable_pvt package
508
509 IF l_debug_mode = 'Y' THEN
510 Pa_Debug.WRITE(g_module_name,' Calling PA_DELIVERABLE_PVT.Create_Deliverable',
511 l_debug_level3);
512 END IF;
513
514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518 PA_DELIVERABLE_PVT.Create_Deliverable
519 (
520 p_api_version => p_api_version
521 ,p_init_msg_list => FND_API.G_FALSE
522 ,p_commit => p_commit
523 ,p_validate_only => p_validate_only
524 ,p_validation_level => p_validation_level
525 ,p_calling_module => p_calling_module
526 ,p_debug_mode => l_debug_mode
527 ,p_max_msg_count => p_max_msg_count
528 ,p_record_version_number => p_record_version_number
529 ,p_object_type => p_object_type
530 ,p_project_id => p_project_id
531 ,p_dlvr_number => p_dlvr_number
532 ,p_dlvr_name => p_dlvr_name
533 ,p_dlvr_description => l_dlvr_description
534 ,p_dlvr_owner_id => l_dlvr_owner_id
535 ,p_dlvr_owner_name => p_dlvr_owner_name
536 ,p_carrying_out_org_id => l_carrying_out_org
537 ,p_carrying_out_org_name => p_carrying_out_org_name
538 ,p_dlvr_version_id => l_dlvr_version_id
539 ,p_status_code => l_status_code
540 ,p_parent_structure_id => l_structure_id
541 ,p_parent_struct_ver_id => l_element_structure_id
542 ,p_dlvr_type_id => p_dlvr_type_id
543 ,p_dlvr_type_name => p_dlvr_type_name
544 ,p_dlvr_reference => p_deliverable_reference
545 ,p_progress_weight => l_progress_weight
546 ,p_scheduled_finish_date => l_scheduled_finish_date
547 ,p_actual_finish_date => l_actual_finish_date
548 ,p_task_id => p_task_id
549 ,p_task_version_id => p_task_version_id
550 ,p_task_name => p_task_name
551 ,p_attribute_category => p_attribute_category
552 ,p_attribute1 => p_attribute1
553 ,p_attribute2 => p_attribute2
554 ,p_attribute3 => p_attribute3
555 ,p_attribute4 => p_attribute4
556 ,p_attribute5 => p_attribute5
557 ,p_attribute6 => p_attribute6
558 ,p_attribute7 => p_attribute7
559 ,p_attribute8 => p_attribute8
560 ,p_attribute9 => p_attribute9
561 ,p_attribute10 => p_attribute10
562 ,p_attribute11 => p_attribute11
563 ,p_attribute12 => p_attribute12
564 ,p_attribute13 => p_attribute13
565 ,p_attribute14 => p_attribute14
566 ,p_attribute15 => p_attribute15
567 ,p_dlvr_item_id => p_dlvr_item_id
568 ,p_pm_source_code => p_pm_source_code /* Bug no. 3651113 */
569 ,x_return_status => x_return_status
570 ,x_msg_count => l_msg_count
571 ,x_msg_data => l_msg_data
572 );
573
574 IF l_debug_mode = 'Y' THEN
575 Pa_Debug.WRITE(g_module_name,' Returned from PA_DELIVERABLE_PVT.Create_Deliverable['||x_return_status||']',
576 l_debug_level3);
577 END IF;
578
579 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586 /*==============3435905 : FP M : Deliverables Changes For AMG - START ==============================*/
587 IF (p_calling_module = 'AMG') THEN
588
589 -- 3630378 changed below cursor to retrieve deliverable element version id
590
591 SELECT Pa_Deliverable_Utils.IS_Dlvr_Item_Based(element_version_id), element_version_id
592 INTO l_item_dlv, l_dlv_elem_ver_id
593 FROM Pa_Proj_Element_Versions
594 WHERE proj_element_id = p_dlvr_item_id
595 AND project_id = p_project_id;
596
597 -- 3630378 Added below code for item information
598
599 IF p_item_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
600 l_item_id := NULL;
601 ELSE
602 l_item_id := p_item_id;
603 END IF;
604
605 IF p_inventory_org_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
606 l_inventory_org_id := NULL;
607 ELSE
608 l_inventory_org_id := p_inventory_org_id;
609 END IF;
610
611 -- 3630378 added code to retrieve master inventory organization id
612
613 PA_RESOURCE_UTILS1.Return_Material_Class_Id
614 (
615 x_material_class_id => l_master_inv_org_id
616 ,x_return_status => l_return_status
617 ,x_msg_data => l_msg_data
618 ,x_msg_count => l_msg_count
619 );
620
621 IF p_quantity = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
622 l_quantity := NULL;
623 ELSE
624 l_quantity := p_quantity;
625 END IF;
626
627 IF p_uom_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
628 l_uom_code := NULL;
629 ELSE
630 l_uom_code := p_uom_code;
631 END IF;
632
633 IF p_unit_price = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
634 l_unit_price := NULL;
635 ELSE
636 l_unit_price := p_unit_price;
637 END IF;
638
639 IF p_unit_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
640 l_unit_number := NULL;
641 ELSE
642 l_unit_number := p_unit_number;
643 END IF;
644
645 IF p_currency_code = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
646 l_currency_code := NULL;
647 ELSE
648 l_currency_code := p_currency_code;
649 END IF;
650
651 -- 3630378 changed from input parameter passing to local variable passing
652 -- this local variables are initialized to user passed values or
653 -- null in above code
654
655 l_dlv_rec.dlv_short_name := p_dlvr_number ;
656 l_dlv_rec.dlv_description := l_dlvr_description ;
657 l_dlv_rec.item_id := l_item_id ;
658 l_dlv_rec.inventory_org_id := l_inventory_org_id ;
659 l_dlv_rec.quantity := l_quantity ;
660 l_dlv_rec.uom_code := l_uom_code ;
661 l_dlv_rec.unit_price := l_unit_price ;
662 l_dlv_rec.unit_number := l_unit_number ;
663 l_dlv_rec.pa_deliverable_id := l_dlv_elem_ver_id ;
664 l_dlv_rec.project_id := p_project_id ;
665 l_dlv_rec.currency_code := l_currency_code ;
666
667 -- 3630378 end
668
669 -- 3630378 changed parameter passing
670 -- passing l_master_inv_org_id instead of 0
671
672 oke_amg_grp.manage_dlv
673 ( p_api_version => p_api_version
674 , p_init_msg_list => p_init_msg_list
675 , p_commit => FND_API.G_FALSE
676 , p_action => 'CREATE'
677 , p_item_dlv => l_item_dlv
678 , p_master_inv_org_id => l_master_inv_org_id
679 , p_dlv_rec => l_dlv_rec
680 , x_return_status => x_return_status
681 , x_msg_data => x_msg_data
682 , x_msg_count => x_msg_count
683 );
684
685 END IF;
686
687 IF l_debug_mode = 'Y' THEN
688 Pa_Debug.WRITE(g_module_name,' Returned from oke_amg_grp.manage_dlv['||x_return_status||']',
689 l_debug_level3);
690 END IF;
691
692 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700 /*==============3435905 : FP M : Deliverables Changes For AMG - END ==============================*/
701
702 IF (p_commit = FND_API.G_TRUE) THEN
703 COMMIT;
704 END IF;
705
706 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
707 pa_debug.reset_curr_function;
708 END IF ;
709
710 EXCEPTION
711
712 WHEN FND_API.G_EXC_ERROR THEN
713
714 x_return_status := Fnd_Api.G_RET_STS_ERROR;
715 l_msg_count := Fnd_Msg_Pub.count_msg;
716
717 IF p_commit = FND_API.G_TRUE THEN
718 ROLLBACK TO CREATE_DLVR_PUB;
719 END IF;
720
721 IF l_msg_count = 1 AND x_msg_data IS NULL
722 THEN
723 Pa_Interface_Utils_Pub.get_messages
724 ( p_encoded => Fnd_Api.G_TRUE
725 , p_msg_index => 1
726 , p_msg_count => l_msg_count
727 , p_msg_data => l_msg_data
728 , p_data => l_data
729 , p_msg_index_out => l_msg_index_out);
730 x_msg_data := l_data;
731 x_msg_count := l_msg_count;
732 ELSE
733 x_msg_count := l_msg_count;
734 END IF;
735 IF l_debug_mode = 'Y' THEN
736 Pa_Debug.reset_curr_function;
737 END IF;
738
739 WHEN Invalid_Arg_Exc_WP THEN
740
741 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
742 x_msg_count := 1;
743 x_msg_data := 'PA_DELIVERABLE_PUB : Create_Deliverable : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
744
745 IF p_commit = FND_API.G_TRUE THEN
746 ROLLBACK TO CREATE_DLVR_PUB;
747 END IF;
748
749 Fnd_Msg_Pub.add_exc_msg
750 ( p_pkg_name => 'PA_DELIVERABLE_PUB'
751 , p_procedure_name => 'Create_Deliverable'
752 , p_error_text => x_msg_data);
753
754 IF l_debug_mode = 'Y' THEN
755 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
756 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
757 l_debug_level5);
758 Pa_Debug.reset_curr_function;
759 END IF;
760 RAISE;
761
762 WHEN OTHERS THEN
763
764 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
765 x_msg_count := 1;
766 x_msg_data := SQLERRM;
767
768 IF p_commit = FND_API.G_TRUE THEN
769 ROLLBACK TO CREATE_DLVR_PUB;
770 END IF;
771
772 Fnd_Msg_Pub.add_exc_msg
773 ( p_pkg_name => 'PA_DELIVERABLE_PUB'
774 , p_procedure_name => 'Create_Deliverable'
775 , p_error_text => x_msg_data);
776
777 IF l_debug_mode = 'Y' THEN
778 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
779 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
780 l_debug_level5);
781 Pa_Debug.reset_curr_function;
782 END IF;
783 RAISE;
784
785 END Create_Deliverable;
786
787
788 -- Procedure : Update_Deliverable
789 -- Type : PUBLIC
790 -- Purpose : Create Deliveable Page calls this procedure to create deliverables
791 -- Note : Check for input parameter validations and short name uniqueness.
792 -- : Retrieve carrying_out_organization_id and structure_info.
793 -- : Call Update_Deliverable procedure of the pa_deliverable_pvt package.
794 -- Assumptions : None
795
796 -- Parameters Type Required Description and Purpose
797 -- --------------------------- ------ -------- --------------------------------------------------------
798 -- p_api_version NUMBER N 1.0
799 -- p_init_msg_list VARCHAR2 N := FND_API.G_TRUE
800 -- p_commit VARCHAR2 N := FND_API.G_FALSE
801 -- p_validate_only VARCHAR2 N := FND_API.G_TRUE
802 -- p_validation_level NUMBER N := FND_API.G_VALID_LEVEL_FULL
803 -- p_calling_module VARCHAR2 N := 'SELF_SERVICE'
804 -- p_debug_mode VARCHAR2 N := 'N'
805 -- p_max_msg_count NUMBER N := NULL
806 -- p_record_version_number NUMBER N := 1
807 -- p_object_type VARCHAR2 N Object Type Default 'PA_DELIVERABLES'
808 -- p_project_id NUMBER Y Project Id
809 -- p_dlvr_number VARCHAR2 Y Deliverable Number
810 -- p_dlvr_name VARCHAR2 Y Deliverable Name
811 -- p_dlvr_description VARCHAR2 N Description
812 -- p_dlvr_owner_id NUMBER N Deliverable Owner Id
813 -- p_dlvr_owner_name VARCHAR2 N Delivearble Owner Name
814 -- p_carrying_out_org_id NUMBER N Project Carrying Out Organization Id
815 -- p_carrying_out_org_name VARCHAR2 N Project Carrying Out Organization Name
816 -- p_dlvr_version_id NUMBER N Deliverable Version Id
817 -- p_status_code VARCHAR2 N Delivearble Status
818 -- p_parent_structure_id NUMBER N Deliverable Parent Structure Id
819 -- p_dlvr_type_id NUMBER N Deliverable Type Id
820 -- p_dlvr_type_name VARCHAR2 N Deliverable Type Name
821 -- p_progress_weight NUMBER N Progress Weight
822 -- p_scheduled_finish_date DATE N Scheduled Finish Date
823 -- p_actual_finish_date DATE N Actual Finish Date
824 -- p_task_id NUMBER N
825 -- p_task_version_id NUMBER N
826 -- p_task_name VARCHAR2 N
827 -- p_attribute_category VARCHAR2 N
828 -- p_attribute1 VARCHAR2 N
829 -- p_attribute2 VARCHAR2 N
830 -- p_attribute3 VARCHAR2 N
831 -- p_attribute4 VARCHAR2 N
832 -- p_attribute5 VARCHAR2 N
833 -- p_attribute6 VARCHAR2 N
834 -- p_attribute7 VARCHAR2 N
835 -- p_attribute8 VARCHAR2 N
836 -- p_attribute9 VARCHAR2 N
837 -- p_attribute10 VARCHAR2 N
838 -- p_attribute11 VARCHAR2 N
839 -- p_attribute12 VARCHAR2 N
840 -- p_attribute13 VARCHAR2 N
841 -- p_attribute14 VARCHAR2 N
842 -- p_attribute15 VARCHAR2 N
843 -- x_return_status VARCHAR2 N Return Status
844 -- x_msg_count NUMBER N Message Count
845 -- x_msg_data VARCHAR2 N Message Data
846
847 PROCEDURE Update_Deliverable
848 (
849 p_api_version IN NUMBER := 1.0
850 , p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
851 , p_commit IN VARCHAR2 := FND_API.G_FALSE
852 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
853 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
854 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
855 , p_debug_mode IN VARCHAR2 := 'N'
856 , p_max_msg_count IN NUMBER := NULL
857 , p_record_version_number IN NUMBER := 1
858 , p_object_type IN PA_PROJ_ELEMENTS.OBJECT_TYPE%TYPE := 'PA_DELIVERABLES'
859 , p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
860 , p_dlvr_number IN PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE
861 , p_dlvr_name IN PA_PROJ_ELEMENTS.NAME%TYPE
862 , p_dlvr_description IN PA_PROJ_ELEMENTS.DESCRIPTION%TYPE := NULL
863 , p_dlvr_owner_id IN PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE := NULL
864 , p_dlvr_owner_name IN VARCHAR2 := NULL
865 , p_carrying_out_org_id IN PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE := NULL
866 , p_carrying_out_org_name IN VARCHAR2 := NULL
867 , p_dlvr_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL
868 , p_status_code IN PA_PROJ_ELEMENTS.STATUS_CODE%TYPE := NULL
869 , p_parent_structure_id IN PA_PROJ_ELEMENTS.PARENT_STRUCTURE_ID%TYPE := NULL
870 , p_dlvr_type_id IN PA_PROJ_ELEMENTS.TYPE_ID%TYPE := NULL
871 , p_dlvr_type_name IN VARCHAR2 := NULL
872 , p_progress_weight IN PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL
873 , p_scheduled_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE := NULL
874 , p_actual_finish_date IN PA_PROJ_ELEM_VER_SCHEDULE.ACTUAL_FINISH_DATE%TYPE := NULL
875 , p_task_id IN NUMBER := NULL
876 , p_task_version_id IN NUMBER := NULL
877 , p_task_name IN VARCHAR2 := NULL
878 , p_deliverable_reference IN VARCHAR2 := NULL
879 , p_attribute_category IN PA_PROJ_ELEMENTS.ATTRIBUTE_CATEGORY%TYPE := NULL
880 , p_attribute1 IN PA_PROJ_ELEMENTS.ATTRIBUTE1%TYPE := NULL
881 , p_attribute2 IN PA_PROJ_ELEMENTS.ATTRIBUTE2%TYPE := NULL
882 , p_attribute3 IN PA_PROJ_ELEMENTS.ATTRIBUTE3%TYPE := NULL
883 , p_attribute4 IN PA_PROJ_ELEMENTS.ATTRIBUTE4%TYPE := NULL
884 , p_attribute5 IN PA_PROJ_ELEMENTS.ATTRIBUTE5%TYPE := NULL
885 , p_attribute6 IN PA_PROJ_ELEMENTS.ATTRIBUTE6%TYPE := NULL
886 , p_attribute7 IN PA_PROJ_ELEMENTS.ATTRIBUTE7%TYPE := NULL
887 , p_attribute8 IN PA_PROJ_ELEMENTS.ATTRIBUTE8%TYPE := NULL
888 , p_attribute9 IN PA_PROJ_ELEMENTS.ATTRIBUTE9%TYPE := NULL
889 , p_attribute10 IN PA_PROJ_ELEMENTS.ATTRIBUTE10%TYPE := NULL
890 , p_attribute11 IN PA_PROJ_ELEMENTS.ATTRIBUTE11%TYPE := NULL
891 , p_attribute12 IN PA_PROJ_ELEMENTS.ATTRIBUTE12%TYPE := NULL
892 , p_attribute13 IN PA_PROJ_ELEMENTS.ATTRIBUTE13%TYPE := NULL
893 , p_attribute14 IN PA_PROJ_ELEMENTS.ATTRIBUTE14%TYPE := NULL
894 , p_attribute15 IN PA_PROJ_ELEMENTS.ATTRIBUTE15%TYPE := NULL
895 , p_item_id IN NUMBER := NULL
896 , p_inventory_org_id IN NUMBER := NULL
897 , p_quantity IN NUMBER := NULL
898 , p_uom_code IN VARCHAR2 := NULL
899 , p_item_description IN VARCHAR2 := NULL
900 , p_unit_price IN NUMBER := NULL
901 , p_unit_number IN VARCHAR2 := NULL
902 , p_currency_code IN VARCHAR2 := NULL
903 , p_dlvr_item_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
904 , p_pm_source_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR /* Bug no. 3651113 */
905 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
906 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
907 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
908 )
909 IS
910 l_msg_count NUMBER := 0;
911 l_data VARCHAR2(2000);
912 l_msg_data VARCHAR2(2000);
913 l_msg_index_out NUMBER;
914 l_debug_mode VARCHAR2(1);
915
916 l_debug_level2 CONSTANT NUMBER := 2;
917 l_debug_level3 CONSTANT NUMBER := 3;
918 l_debug_level4 CONSTANT NUMBER := 4;
919 l_debug_level5 CONSTANT NUMBER := 5;
920
921 l_dlvr_name PA_PROJ_ELEMENTS.NAME%TYPE;
922 l_dlvr_description PA_PROJ_ELEMENTS.DESCRIPTION%TYPE;
923 l_dlvr_owner_id PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE;
924 l_carrying_out_org_id PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE;
925 l_progress_weight PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE;
926 l_scheduled_finish_date DATE;
927 l_actual_finish_date DATE;
928 l_dlvr_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
929 l_status_code PA_PROJ_ELEMENTS.STATUS_CODE%TYPE;
930 l_dlvr_type_id PA_PROJ_ELEMENTS.TYPE_ID%TYPE;
931 l_task_id PA_TASKS.TASK_ID%TYPE; --added bug 3651538
932 l_deliverable_reference PA_PROJ_ELEMENTS.PM_SOURCE_CODE%TYPE; -- added for bug# 3749447
933
934 l_structure_type VARCHAR2(150) := 'DELIVERABLE';
935 l_structure_id NUMBER;
936 l_element_structure_id NUMBER;
937 is_dlvr_number_unique VARCHAR2(1) := 'N';
938 l_carrying_out_org NUMBER;
939
940 l_item_id OKE_DELIVERABLES_B.ITEM_ID%TYPE ;
941 l_inventory_org_id OKE_DELIVERABLES_B.INVENTORY_ORG_ID%TYPE ;
942 l_quantity OKE_DELIVERABLES_B.QUANTITY%TYPE ;
943 l_uom_code OKE_DELIVERABLES_B.UOM_CODE%TYPE ;
944 l_item_description OKE_DELIVERABLES_TL.DESCRIPTION%TYPE ;
945 l_unit_price OKE_DELIVERABLES_B.UNIT_PRICE%TYPE ;
946 l_unit_number OKE_DELIVERABLES_B.UNIT_NUMBER%TYPE ;
947 l_currency_code OKE_DELIVERABLES_B.CURRENCY_CODE%TYPE ;
948 l_item_dlv VARCHAR2(1) ;
949
950 Cursor C_dlvr IS SELECT
951 decode( p_dlvr_name , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, element_name, p_dlvr_name) element_name
952 , decode( p_dlvr_description , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, description , p_dlvr_description) description
953 , decode( p_dlvr_owner_id , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, manager_person_id, p_dlvr_owner_id) manager_person_id
954 , decode( p_carrying_out_org_id , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, null , p_carrying_out_org_id) carrying_out_org_id -- to be derived later
955 , decode( p_status_code , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,status_code , p_status_code) status_code
956 , decode( p_dlvr_type_id , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, dlvr_type_id, p_dlvr_type_id) dlvr_type_id
957 , decode( p_progress_weight , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, progress_weight, p_progress_weight) progress_weight
958 , decode( p_scheduled_finish_date, PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,due_date , p_scheduled_finish_date) due_date
959 , decode( p_actual_finish_date , PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,completion_date, p_actual_finish_date) completion_date
960 , decode( p_task_id , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM, null , p_task_id) task_id -- added bug 3651538
961 , decode( p_deliverable_reference, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR, pm_source_reference, p_deliverable_reference) pm_source_reference -- added for bug# 3749447
962 FROM pa_deliverables_v
963 WHERE element_version_id = p_dlvr_version_id;
964
965 Cursor C_oke IS SELECT
966 decode( p_item_id , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , item_id, p_item_id) item_id
967 , decode( p_inventory_org_id , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , inventory_org_id, p_inventory_org_id) inventory_org_id
968 , decode( p_quantity , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , quantity, p_quantity) quantity
969 , decode( p_uom_code , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR , uom_code, p_uom_code) uom_code
970 , decode( p_item_description , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR , description, p_item_description) description
971 , decode( p_unit_price , PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM , unit_price, p_unit_price) unit_price
972 , decode( p_unit_number , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR , unit_number, p_unit_number) unit_number -- 3749447 changed from G_PA_MISS_NUM to G_PA_MISS_CHAR
973 , decode( p_currency_code , PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR , currency_code, p_currency_code) currency_code
974 FROM oke_deliverables_vl
975 WHERE source_deliverable_id = p_dlvr_version_id; -- 3749447 changed where clause condition from deliverable_number = p_dlvr_number to use deliverable ver id
976
977 l_dlv_rec oke_amg_grp.dlv_rec_type;
978
979 -- added for bug# 3651542
980 l_dlv_elem_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
981 l_master_inv_org_id PA_PLAN_RES_DEFAULTS.item_master_id%TYPE;
982 l_return_status VARCHAR2(1);
983 -- end bug# 3651542
984
985 l_manage_dlv_flag VARCHAR2(1); -- Bug 7562076
986
987 BEGIN
988
989 x_msg_count := 0;
990 x_return_status := FND_API.G_RET_STS_SUCCESS;
991 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
992
993 IF l_debug_mode = 'Y' THEN
994 PA_DEBUG.set_curr_function( p_function => 'UPDATE_DELIVERABLE',
995 p_debug_mode => l_debug_mode );
996 END IF;
997
998 IF l_debug_mode = 'Y' THEN
999 Pa_Debug.g_err_stage:= 'UPDATE_DELIVERABLE : Printing Input parameters';
1000 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1001 l_debug_level3);
1002 Pa_Debug.WRITE(g_module_name,' P_PROJECT_ID '||':'|| p_project_id,
1003 l_debug_level3);
1004 Pa_Debug.WRITE(g_module_name,' P_DLVR_NUMBER '||':'|| p_dlvr_number,
1005 l_debug_level3);
1006 Pa_Debug.WRITE(g_module_name,' P_DLVR_NAME '||':'|| p_dlvr_name,
1007 l_debug_level3);
1008 Pa_Debug.WRITE(g_module_name,' P_DLVR_TYPE_ID '||':'|| p_dlvr_type_id,
1009 l_debug_level3);
1010 Pa_Debug.WRITE(g_module_name,' P_DLVR_TYPE_NAME '||':'|| p_dlvr_type_name,
1011 l_debug_level3);
1012 Pa_Debug.WRITE(g_module_name,' P_TASK_VERSION_ID '||':'|| p_task_version_id,
1013 l_debug_level3);
1014 Pa_Debug.WRITE(g_module_name,' P_TASK_NAME '||':'|| p_task_name,
1015 l_debug_level3);
1016 END IF;
1017
1018 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1019 FND_MSG_PUB.initialize;
1020 END IF;
1021
1022 IF (p_commit = FND_API.G_TRUE) THEN
1023 savepoint UPDATE_DLVR_PUB;
1024 END IF;
1025
1026 IF l_debug_mode = 'Y' THEN
1027 Pa_Debug.g_err_stage:= 'Validating Input parameters';
1028 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1029 l_debug_level3);
1030 END IF;
1031
1032 -- Input parameter Validation Logic
1033
1034 --Bug 3861930 negative progress weight should not be allowed
1035
1036 IF p_progress_weight IS NOT NULL AND p_progress_weight < 0 THEN
1037 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1038 p_msg_name => 'PA_NEG_PRG_NOT_ALLOWED');
1039 x_return_status := FND_API.G_RET_STS_ERROR;
1040 RAISE FND_API.G_EXC_ERROR;
1041 END IF;
1042
1043 --End 3861930
1044 /*==============3435905 : FP M : Deliverables Changes For AMG - START ==============================*/
1045 IF (p_calling_module = 'AMG') THEN
1046
1047 -- Fetching the values from database.
1048 -- If passed value is MISS_CHAR or MISS_NUM or MISS_DATE then passed the fetched values.
1049
1050 For C_dlvr_rec IN C_dlvr LOOP
1051
1052 l_dlvr_name := C_dlvr_rec.element_name ;
1053 l_dlvr_description := C_dlvr_rec.description ;
1054 l_dlvr_owner_id := C_dlvr_rec.manager_person_id ;
1055 l_carrying_out_org_id := C_dlvr_rec.carrying_out_org_id ;
1056 l_status_code := C_dlvr_rec.status_code ;
1057 l_dlvr_type_id := C_dlvr_rec.dlvr_type_id ;
1058 l_progress_weight := C_dlvr_rec.progress_weight ;
1059 l_scheduled_finish_date := C_dlvr_rec.due_date ;
1060 l_actual_finish_date := C_dlvr_rec.completion_date ;
1061 l_task_id := C_dlvr_rec.task_id ; -- added bug 3651538
1062 l_deliverable_reference := C_dlvr_rec.pm_source_reference ; -- added for bug# 3749447
1063 END LOOP;
1064
1065 For C_oke_rec IN C_oke LOOP
1066
1067 l_item_id := C_oke_rec.item_id ;
1068 l_inventory_org_id := C_oke_rec.inventory_org_id ;
1069 l_quantity := C_oke_rec.quantity ;
1070 l_uom_code := C_oke_rec.uom_code ;
1071 l_item_description := C_oke_rec.description ;
1072 l_unit_price := C_oke_rec.unit_price ;
1073 l_unit_number := C_oke_rec.unit_number ;
1074 l_currency_code := C_oke_rec.currency_code ;
1075 END LOOP;
1076
1077 -- Validating the input parameters passed through AMG
1078 Pa_Deliverable_Utils.Validate_Deliverable
1079 (
1080 p_deliverable_id => p_dlvr_item_id
1081 , p_deliverable_reference => l_deliverable_reference -- changed for bug# 3749447
1082 , p_dlvr_number => p_dlvr_number
1083 , p_dlvr_name => l_dlvr_name
1084 , px_dlvr_owner_id => l_dlvr_owner_id
1085 , p_dlvr_owner_name => p_dlvr_owner_name
1086 , p_dlvr_type_id => l_dlvr_type_id
1087 , p_carrying_out_org_id => l_carrying_out_org_id
1088 , px_actual_finish_date => l_actual_finish_date
1089 , px_progress_weight => l_progress_weight
1090 , px_status_code => l_status_code
1091 , p_project_id => p_project_id
1092 , p_task_id => l_task_id -- changed p_task_id to l_task_id 3651538
1093 , p_calling_mode => 'UPDATE'
1094 , x_return_status => x_return_status
1095 , x_msg_count => x_msg_count
1096 , x_msg_data => x_msg_data );
1097
1098 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1101 RAISE FND_API.G_EXC_ERROR;
1102 END IF;
1103
1104 /*==============3435905 : FP M : Deliverables Changes For AMG - END ============================== */
1105
1106 ELSE /* context <> 'AMG' */
1107
1108 IF (p_project_id IS NULL ) THEN
1109 x_return_status := FND_API.G_RET_STS_ERROR;
1110 END IF;
1111
1112 IF (p_dlvr_number IS NULL ) THEN
1113 x_return_status := FND_API.G_RET_STS_ERROR;
1114 END IF;
1115
1116 IF (p_dlvr_name IS NULL ) THEN
1117 x_return_status := FND_API.G_RET_STS_ERROR;
1118 END IF;
1119
1120 IF (p_dlvr_type_id IS NULL ) THEN
1121 IF (p_dlvr_type_name IS NULL ) THEN
1122 x_return_status := FND_API.G_RET_STS_ERROR;
1123 END IF;
1124 END IF;
1125
1126 IF (p_status_code IS NULL ) THEN
1127 x_return_status := FND_API.G_RET_STS_ERROR;
1128 END IF;
1129
1130
1131 IF (p_dlvr_version_id IS NULL ) THEN
1132 x_return_status := FND_API.G_RET_STS_ERROR;
1133 END IF;
1134
1135 l_dlvr_name := p_dlvr_name ;
1136 l_dlvr_description := p_dlvr_description ;
1137 l_dlvr_owner_id := p_dlvr_owner_id ;
1138 l_carrying_out_org_id := p_carrying_out_org_id ;
1139 /* l_progress_weight := p_dlvr_version_id ; Commented by avaithia Bug 3518386*/
1140 l_progress_weight := p_progress_weight ; /*Included by avaithia Bug 3518386*/
1141 l_scheduled_finish_date := p_scheduled_finish_date ;
1142 l_actual_finish_date := p_actual_finish_date ;
1143 l_status_code := p_status_code ;
1144 l_dlvr_type_id := p_dlvr_type_id ;
1145 l_task_id := p_task_id ; -- added bug 3651538
1146
1147 l_item_id := p_item_id ;
1148 l_inventory_org_id := p_inventory_org_id ;
1149 l_quantity := p_quantity ;
1150 l_uom_code := p_uom_code ;
1151 l_item_description := p_item_description ;
1152 l_unit_price := p_unit_price ;
1153 l_unit_number := p_unit_number ;
1154 l_currency_code := p_currency_code ;
1155
1156
1157 END IF; /* context =AMG */
1158 -- Business Logic
1159
1160 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1161 RAISE Invalid_Arg_Exc_WP;
1162 END IF;
1163
1164 IF l_debug_mode = 'Y' THEN
1165 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_UTILS.get_carrying_out_org ',
1166 l_debug_level3);
1167 END IF;
1168
1169 IF l_debug_mode = 'Y' THEN
1170 Pa_Debug.WRITE(g_module_name,' Retrieving Structure Information ',
1171 l_debug_level3);
1172 END IF;
1173
1174 IF l_debug_mode = 'Y' THEN
1175 Pa_Debug.WRITE(g_module_name,' PA_DELIVERABLE_UTILS.get_structure_info called ',
1176 l_debug_level3);
1177 END IF;
1178
1179 -- retrieve structure information
1180
1181 PA_DELIVERABLE_UTILS.get_structure_info
1182 (
1183 p_project_id => p_project_id
1184 ,P_structure_type => l_structure_type
1185 ,X_proj_element_id => l_structure_id
1186 ,X_element_version_id => l_element_structure_id
1187 ,x_return_status => x_return_status
1188 ,x_msg_count => x_msg_count
1189 ,x_msg_data => x_msg_data
1190 );
1191
1192 IF l_debug_mode = 'Y' THEN
1193 Pa_Debug.WRITE(g_module_name,' Out of PA_DELIVERABLE_UTILS.get_structure_info ',
1194 l_debug_level3);
1195 END IF;
1196
1197 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1198 RAISE FND_API.G_EXC_ERROR;
1199 END IF;
1200
1201 IF l_debug_mode = 'Y' THEN
1202 Pa_Debug.WRITE(g_module_name,' Checking Uniqueness for Deliverable Number ',
1203 l_debug_level3);
1204 END IF;
1205
1206 -- check for dlvr_number uniqueness
1207
1208 is_dlvr_number_unique := PA_PROJ_ELEMENTS_UTILS.Check_element_NUmber_Unique
1209 (
1210 p_element_number => p_dlvr_number
1211 ,p_element_id => p_dlvr_item_id
1212 ,p_project_id => p_project_id
1213 ,p_structure_id => l_structure_id
1214 ,p_object_type => p_object_type
1215 );
1216
1217 -- if is_dlvr_number_unique is 'N' return with error
1218
1219 IF (is_dlvr_number_unique = 'N' ) THEN
1220 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1221 p_msg_name => 'PA_DLVR_NUMBER_EXISTS');
1222 x_return_status := FND_API.G_RET_STS_ERROR;
1223 END IF;
1224
1225 -- else call update_deliveable procedure of pa_deliveable_pvt package
1226
1227 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1228 RAISE FND_API.G_EXC_ERROR;
1229 END IF;
1230
1231 IF l_debug_mode = 'Y' THEN
1232 Pa_Debug.WRITE(g_module_name,' Deliverable Number is unique',
1233 l_debug_level3);
1234 END IF;
1235
1236 IF l_debug_mode = 'Y' THEN
1237 Pa_Debug.WRITE(g_module_name,' Calling PA_DELIVERABLE_PVT.Update_Deliverable',
1238 l_debug_level3);
1239 END IF;
1240
1241 PA_DELIVERABLE_PVT.Update_Deliverable
1242 (
1243 p_api_version => p_api_version
1244 ,p_init_msg_list => FND_API.G_FALSE
1245 ,p_commit => p_commit
1246 ,p_validate_only => p_validate_only
1247 ,p_validation_level => p_validation_level
1248 ,p_calling_module => p_calling_module
1249 ,p_debug_mode => l_debug_mode
1250 ,p_max_msg_count => p_max_msg_count
1251 ,p_record_version_number => p_record_version_number
1252 ,p_object_type => p_object_type
1253 ,p_project_id => p_project_id
1254 ,p_dlvr_number => p_dlvr_number
1255 ,p_dlvr_name => l_dlvr_name
1256 ,p_dlvr_description => l_dlvr_description
1257 ,p_dlvr_owner_id => l_dlvr_owner_id
1258 ,p_dlvr_owner_name => p_dlvr_owner_name
1259 ,p_carrying_out_org_id => l_carrying_out_org
1260 ,p_carrying_out_org_name => p_carrying_out_org_name
1261 ,p_dlvr_version_id => p_dlvr_version_id
1262 ,p_status_code => l_status_code
1263 ,p_parent_structure_id => l_structure_id
1264 ,p_parent_struct_ver_id => l_element_structure_id
1265 ,p_dlvr_type_id => l_dlvr_type_id
1266 ,p_dlvr_type_name => p_dlvr_type_name
1267 ,p_progress_weight => l_progress_weight
1268 ,p_scheduled_finish_date => l_scheduled_finish_date
1269 ,p_actual_finish_date => l_actual_finish_date
1270 ,p_task_id => l_task_id -- changed p_task_id to l_task_id 3651538
1271 ,p_task_version_id => p_task_version_id
1272 ,p_task_name => p_task_name
1273 ,p_attribute_category => p_attribute_category
1274 ,p_attribute1 => p_attribute1
1275 ,p_attribute2 => p_attribute2
1276 ,p_attribute3 => p_attribute3
1277 ,p_attribute4 => p_attribute4
1278 ,p_attribute5 => p_attribute5
1279 ,p_attribute6 => p_attribute6
1280 ,p_attribute7 => p_attribute7
1281 ,p_attribute8 => p_attribute8
1282 ,p_attribute9 => p_attribute9
1283 ,p_attribute10 => p_attribute10
1284 ,p_attribute11 => p_attribute11
1285 ,p_attribute12 => p_attribute12
1286 ,p_attribute13 => p_attribute13
1287 ,p_attribute14 => p_attribute14
1288 ,p_attribute15 => p_attribute15
1289 ,p_dlvr_item_id => p_dlvr_item_id
1290 ,p_pm_source_code => p_pm_source_code /* Bug no. 3651113 */
1291 ,p_deliverable_reference => l_deliverable_reference -- added for bug# 3749447
1292 ,x_return_status => x_return_status
1293 ,x_msg_count => l_msg_count
1294 ,x_msg_data => l_msg_data
1295 );
1296
1297 IF l_debug_mode = 'Y' THEN
1298 Pa_Debug.WRITE(g_module_name,' Returned from PA_DELIVERABLE_PVT.Update_Deliverable',
1299 l_debug_level3);
1300 END IF;
1301
1302
1303 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1305 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1306 RAISE FND_API.G_EXC_ERROR;
1307 END IF;
1308
1309 /*==============3435905 : FP M : Deliverables Changes For AMG - START ==============================*/
1310 IF (p_calling_module = 'AMG') THEN
1311
1312 -- 3651542 added element_version_id column in select statement
1313 -- this retrieved element_version_id will be passed to oke
1314
1315 SELECT Pa_Deliverable_Utils.IS_Dlvr_Item_Based(element_version_id), element_version_id
1316 INTO l_item_dlv, l_dlv_elem_ver_id
1317 FROM Pa_Proj_Element_Versions
1318 WHERE proj_element_id = p_dlvr_item_id
1319 AND project_id = p_project_id;
1320
1321 -- Bug 7562076
1322 -- Added code to check if we are passing any input parameters to override the existing data present
1323 -- in oke tables.If we are not passing or if we are passing the values which are present in system
1324 -- then oke_amg_grp.manage_dlv is not required to be called.
1325
1326 l_manage_dlv_flag := 'Y';
1327
1328 IF (l_item_id IS NULL AND l_inventory_org_id IS NULL AND l_quantity IS NULL AND l_uom_code IS NULL AND
1329 l_unit_price IS NULL AND l_unit_number IS NULL AND l_currency_code IS NULL) THEN
1330
1331 l_manage_dlv_flag := 'N';
1332
1333 ELSE
1334 BEGIN
1335 SELECT 'N' INTO l_manage_dlv_flag
1336 FROM OKE_DELIVERABLES_B
1337 WHERE project_id = p_project_id
1338 AND source_deliverable_id = l_dlv_elem_ver_id
1339 AND CURRENCY_CODE = l_currency_code
1340 AND nvl(unit_number,-99) = nvl(l_unit_number,-99)
1341 AND nvl(unit_price,-99) = nvl(l_unit_price,-99)
1342 AND uom_code = l_uom_code
1343 AND nvl(quantity,-99) = nvl(l_quantity,-99)
1344 AND inventory_org_id = l_inventory_org_id
1345 AND item_id = l_item_id;
1346 EXCEPTION
1347 WHEN NO_DATA_FOUND THEN
1348 l_manage_dlv_flag := 'Y';
1349 END;
1350
1351 END IF;
1352
1353 IF l_manage_dlv_flag = 'Y' THEN
1354
1355 l_dlv_rec.dlv_short_name := p_dlvr_number ;
1356 l_dlv_rec.dlv_description := l_dlvr_description ;
1357 l_dlv_rec.item_id := l_item_id ;
1358 l_dlv_rec.inventory_org_id := l_inventory_org_id ;
1359 l_dlv_rec.quantity := l_quantity ;
1360 l_dlv_rec.uom_code := l_uom_code ;
1361 l_dlv_rec.unit_price := l_unit_price ;
1362 l_dlv_rec.unit_number := l_unit_number ;
1363 -- 3651542 oke is expecting element_version_id of deliverable, earlier proj_element_id was passed
1364 -- passing deliverable element_version_id
1365 l_dlv_rec.pa_deliverable_id := l_dlv_elem_ver_id ;
1366 l_dlv_rec.project_id := p_project_id ;
1367 l_dlv_rec.currency_code := l_currency_code ;
1368
1369 -- added below code to retrieve master inventory org id
1370
1371 PA_RESOURCE_UTILS1.Return_Material_Class_Id
1372 (
1373 x_material_class_id => l_master_inv_org_id
1374 ,x_return_status => l_return_status
1375 ,x_msg_data => l_msg_data
1376 ,x_msg_count => l_msg_count
1377 );
1378
1379 oke_amg_grp.manage_dlv
1380 ( p_api_version => p_api_version
1381 , p_init_msg_list => p_init_msg_list
1382 , p_commit => FND_API.G_FALSE
1383 , p_action => 'UPDATE'
1384 , p_item_dlv => l_item_dlv
1385 , p_master_inv_org_id => l_master_inv_org_id -- 3651542 passing retrieved master inventory org id
1386 , p_dlv_rec => l_dlv_rec
1387 , x_return_status => x_return_status
1388 , x_msg_data => x_msg_data
1389 , x_msg_count => x_msg_count
1390 );
1391
1392 END IF; -- Bug 7562076
1393 END IF;
1394
1395 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1397 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1398 RAISE FND_API.G_EXC_ERROR;
1399 END IF;
1400
1401 /*==============3435905 : FP M : Deliverables Changes For AMG - END ==============================*/
1402
1403 x_return_status := FND_API.G_RET_STS_SUCCESS;
1404
1405 IF (p_commit = FND_API.G_TRUE) THEN
1406 COMMIT;
1407 END IF;
1408
1409 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
1410 pa_debug.reset_curr_function;
1411 END IF ;
1412
1413 EXCEPTION
1414
1415 WHEN FND_API.G_EXC_ERROR THEN
1416
1417 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1418 l_msg_count := Fnd_Msg_Pub.count_msg;
1419
1420 IF p_commit = FND_API.G_TRUE THEN
1421 ROLLBACK TO UPDATE_DLVR_PUB;
1422 END IF;
1423
1424 IF l_msg_count = 1 AND x_msg_data IS NULL
1425 THEN
1426 Pa_Interface_Utils_Pub.get_messages
1427 ( p_encoded => Fnd_Api.G_TRUE
1428 , p_msg_index => 1
1429 , p_msg_count => l_msg_count
1430 , p_msg_data => l_msg_data
1431 , p_data => l_data
1432 , p_msg_index_out => l_msg_index_out);
1433 x_msg_data := l_data;
1434 x_msg_count := l_msg_count;
1435 ELSE
1436 x_msg_count := l_msg_count;
1437 END IF;
1438 IF l_debug_mode = 'Y' THEN
1439 Pa_Debug.reset_curr_function;
1440 END IF;
1441
1442 WHEN Invalid_Arg_Exc_WP THEN
1443
1444 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1445 x_msg_count := 1;
1446 x_msg_data := 'PA_DELIVERABLE_PUB : Update_Deliverable : NULL PARAMETERS ARE PASSED OR CURSOR DIDNT RETURN ANY ROWS';
1447
1448 IF p_commit = FND_API.G_TRUE THEN
1449 ROLLBACK TO UPDATE_DLVR_PUB;
1450 END IF;
1451
1452 Fnd_Msg_Pub.add_exc_msg
1453 ( p_pkg_name => 'PA_DELIVERABLE_PUB'
1454 , p_procedure_name => 'Update_Deliverable'
1455 , p_error_text => x_msg_data);
1456
1457 IF l_debug_mode = 'Y' THEN
1458 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1459 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1460 l_debug_level5);
1461 Pa_Debug.reset_curr_function;
1462 END IF;
1463 RAISE;
1464
1465 WHEN OTHERS THEN
1466
1467 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1468 x_msg_count := 1;
1469 x_msg_data := SQLERRM;
1470
1471 IF p_commit = FND_API.G_TRUE THEN
1472 ROLLBACK TO UPDATE_DLVR_PUB;
1473 END IF;
1474
1475 Fnd_Msg_Pub.add_exc_msg
1476 ( p_pkg_name => 'PA_DELIVERABLE_PUB'
1477 , p_procedure_name => 'Update_Deliverable'
1478 , p_error_text => x_msg_data);
1479
1480 IF l_debug_mode = 'Y' THEN
1481 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1482 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1483 l_debug_level5);
1484 Pa_Debug.reset_curr_function;
1485 END IF;
1486 RAISE;
1487
1488 END Update_Deliverable;
1489
1490 -- SubProgram : DELETE_DLV_TASK_ASSOCIATION
1491 -- Type : PROCEDURE
1492 -- Purpose : Public API to Delete Task - Deliverable Association
1493 -- Note : Public API called from Task Detail and Deliverable Details Page
1494 -- Assumptions : None
1495 -- Parameter IN/OUT Type Required Description and Purpose
1496 -- --------------------------- --------- ---------- --------- ---------------------------
1497 -- p_api_version IN NUMBER N Standard Parameter
1498 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
1499 -- p_commit IN VARCHAR2 N Standard Parameter
1500 -- p_validate_only IN VARCHAR2 N Standard Parameter
1501 -- p_validation_level IN NUMBER N Standard Parameter
1502 -- p_calling_module IN VARCHAR2 N Standard Parameter
1503 -- p_debug_mode IN VARCHAR2 N Standard Parameter
1504 -- p_max_msg_count IN NUMBER N Standard Parameter
1505 -- p_task_element_id IN NUMBER N Task Element Id
1506 -- p_task_version_id IN NUMBER N Task Version Id
1507 -- p_dlv_element_id IN NUMBER N Deliverable Element Id
1508 -- p_dlv_version_id IN NUMBER N Deliverable Version Id
1509 -- p_object_relationship_id IN NUMBER N Object Relationship Id
1510 -- p_obj_rec_ver_number IN NUMBER N Record Version NUmber
1511 -- p_project_id IN NUMBER N Project Id
1512 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
1513 -- x_msg_count OUT NUMBER N Standard Out Parameter
1514 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
1515
1516
1517
1518
1519 PROCEDURE DELETE_DLV_TASK_ASSOCIATION
1520 (p_api_version IN NUMBER :=1.0
1521 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
1522 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
1523 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
1524 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
1525 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
1526 ,p_debug_mode IN VARCHAR2 :='N'
1527 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1528 ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
1529 ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
1530 ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
1531 ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
1532 ,p_object_relationship_id IN pa_object_relationships.object_relationship_id%TYPE
1533 ,p_obj_rec_ver_number IN pa_object_relationships.record_version_number%TYPE
1534 ,p_project_id IN pa_projects_all.project_id%TYPE
1535 ,p_calling_context IN VARCHAR2 := 'TASKS'
1536 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1537 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1538 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1539 )
1540 IS
1541 l_debug_mode VARCHAR2(10);
1542 l_msg_count NUMBER ;
1543 l_data VARCHAR2(2000);
1544 l_msg_data VARCHAR2(2000);
1545 l_msg_index_out NUMBER;
1546 l_disassociation_allowed VARCHAR2(1);
1547
1548 BEGIN
1549
1550 x_msg_count := 0;
1551 x_return_status := FND_API.G_RET_STS_SUCCESS;
1552 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1553
1554 IF l_debug_mode = 'Y' THEN
1555 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_TASK_ASSOCIATION',
1556 p_debug_mode => l_debug_mode );
1557 pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
1558 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1559 END IF;
1560
1561 IF l_debug_mode = 'Y' THEN
1562 pa_debug.g_err_stage:= 'Printing Input parameters';
1563 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1564 pa_debug.write(g_module_name,'p_task_element_id '||':'||p_task_element_id,3) ;
1565 pa_debug.write(g_module_name,'p_task_version_id'||':'||p_task_version_id,3) ;
1566 pa_debug.write(g_module_name,'p_dlv_element_id '||':'||p_dlv_element_id,3) ;
1567 pa_debug.write(g_module_name,'p_dlv_version_id'||':'||p_dlv_version_id,3) ;
1568 pa_debug.write(g_module_name,'p_object_relationship_id'||':'||p_object_relationship_id,3) ;
1569 END IF;
1570
1571 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1572 FND_MSG_PUB.initialize;
1573 END IF;
1574
1575 IF (p_commit = FND_API.G_TRUE) THEN
1576 savepoint DEL_DLV_TASK_ASSCN_PUB_SP ;
1577 END IF;
1578
1579 IF l_debug_mode = 'Y' THEN
1580 pa_debug.g_err_stage:= 'Validating Input parameters';
1581 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1582 END IF;
1583
1584 IF (p_dlv_element_id IS NULL OR p_dlv_element_id IS NULL ) OR
1585 (p_task_element_id IS NULL OR p_task_version_id IS NULL)
1586 THEN
1587 IF l_debug_mode = 'Y' THEN
1588 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
1589 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1590 END IF;
1591
1592 x_return_status := FND_API.G_RET_STS_ERROR;
1593 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1594 ,p_msg_name => 'PA_INV_PARAM_PASSED');
1595 RAISE Invalid_Arg_Exc_Dlv;
1596 END IF;
1597
1598 IF l_debug_mode = 'Y' THEN
1599 pa_debug.g_err_stage:= 'Calling PA_DELIVERABLE_PVT.DELETE_DLV_TASK_ASSOCIATION ';
1600 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1601 END IF;
1602
1603 -- Call PA_DELIVERAB_PVT.DELETE_DLV_TASK_ASSOCIATION
1604 PA_DELIVERABLE_PVT.DELETE_DLV_TASK_ASSOCIATION
1605 (p_api_version => p_api_version
1606 ,p_init_msg_list => p_init_msg_list
1607 ,p_commit => p_commit
1608 ,p_validate_only => p_validate_only
1609 ,p_validation_level => p_validation_level
1610 ,p_calling_module => p_calling_module
1611 ,p_debug_mode => l_debug_mode
1612 ,p_max_msg_count => p_max_msg_count
1613 ,p_task_element_id => p_task_element_id
1614 ,p_task_version_id => p_task_version_id
1615 ,p_dlv_element_id => p_dlv_element_id
1616 ,p_dlv_version_id => p_dlv_version_id
1617 ,p_object_relationship_id => p_object_relationship_id
1618 ,p_obj_rec_ver_number => p_obj_rec_ver_number
1619 ,p_project_id => p_project_id
1620 ,p_calling_context => p_calling_context
1621 ,x_return_status => x_return_status
1622 ,x_msg_count => x_msg_count
1623 ,x_msg_data => x_msg_data
1624 ) ;
1625
1626 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1627 RAISE Invalid_Arg_Exc_Dlv ;
1628 END IF ;
1629
1630 IF l_debug_mode = 'Y' THEN
1631 pa_debug.g_err_stage:= 'Exiting DELETE_DLV_TASK_ASSOCIATION' ;
1632 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1633 pa_debug.reset_curr_function;
1634 END IF;
1635
1636 EXCEPTION
1637 WHEN Invalid_Arg_Exc_Dlv THEN
1638 x_return_status := FND_API.G_RET_STS_ERROR;
1639 l_msg_count := FND_MSG_PUB.count_msg;
1640
1641 IF (p_commit = FND_API.G_TRUE) THEN
1642 ROLLBACK TO DEL_DLV_TASK_ASSCN_PUB_SP;
1643 END IF ;
1644
1645 IF l_debug_mode = 'Y' THEN
1646 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_TASK_ASSOCIATION';
1647 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1648 END IF;
1649
1650 IF l_msg_count = 1 THEN
1651 PA_INTERFACE_UTILS_PUB.get_messages
1652 (p_encoded => FND_API.G_TRUE,
1653 p_msg_index => 1,
1654 p_msg_count => l_msg_count,
1655 p_msg_data => l_msg_data,
1656 p_data => l_data,
1657 p_msg_index_out => l_msg_index_out);
1658 x_msg_data := l_data;
1659 x_msg_count := l_msg_count;
1660 ELSE
1661 x_msg_count := l_msg_count;
1662 END IF;
1663 IF l_debug_mode = 'Y' THEN
1664 pa_debug.reset_curr_function;
1665 END IF ;
1666 RETURN;
1667 WHEN OTHERS THEN
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1669 x_msg_count := 1;
1670 x_msg_data := SQLERRM;
1671
1672 IF (p_commit = FND_API.G_TRUE) THEN
1673 ROLLBACK TO DEL_DLV_TASK_ASSCN_PUB_SP;
1674 END IF ;
1675
1676 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PUB'
1677 ,p_procedure_name => 'DELETE_DLV_TASK_ASSOCIATION');
1678
1679 IF p_debug_mode = 'Y' THEN
1680 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1681 pa_debug.write('DELETE_DLV_TASK_ASSOCIATION: ' || g_module_name,pa_debug.g_err_stage,5);
1682 pa_debug.reset_curr_function;
1683 END IF;
1684 RAISE;
1685 END DELETE_DLV_TASK_ASSOCIATION ;
1686
1687 -- SubProgram : DELETE_DELIVERABLES_IN_BULK
1688 -- Type : PROCEDURE
1689 -- Purpose : Public API to Delete Multiple Deliverables from Deliverable List Page
1690 -- Note : This API is called from Deliverable List Page
1691 -- Assumptions : None
1692 -- Parameter IN/OUT Type Required Description and Purpose
1693 -- --------------------------- --------- ---------- --------- ---------------------------
1694 -- p_api_version IN NUMBER N Standard Parameter
1695 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
1696 -- p_commit IN VARCHAR2 N Standard Parameter
1697 -- p_validate_only IN VARCHAR2 N Standard Parameter
1698 -- p_validation_level IN NUMBER N Standard Parameter
1699 -- p_calling_module IN VARCHAR2 N Standard Parameter
1700 -- p_debug_mode IN VARCHAR2 N Standard Parameter
1701 -- p_max_msg_count IN NUMBER N Standard Parameter
1702 -- p_dlv_element_id_tbl IN PLSQL Table N PLSQL table of Dlv Element Id
1703 -- p_dlv_version_id_tbl IN PLSQL Table N PLSQL table of Dlv Version Id
1704 -- p_rec_ver_number_tbl IN PLSQL Table N PLSQL Table of Rec. Version Number
1705 -- p_dlv_name_tbl IN PLSQL Table N PLSQL Table of Dlv. Name
1706 -- p_dlv_number_tbl IN PLSQL Table N PLSQL Table of Dlv. Number
1707 -- p_project_id IN NUMBER N Project Id
1708 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
1709 -- x_msg_count OUT NUMBER N Standard Out Parameter
1710 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
1711
1712 PROCEDURE DELETE_DELIVERABLES_IN_BULK
1713 (p_api_version IN NUMBER :=1.0
1714 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
1715 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
1716 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
1717 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
1718 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
1719 ,p_debug_mode IN VARCHAR2 :='N'
1720 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1721 ,p_dlv_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
1722 ,p_dlv_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
1723 ,p_rec_ver_number_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
1724 ,p_dlv_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
1725 ,p_dlv_number_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
1726 ,p_project_id IN pa_projects_all.project_id%TYPE
1727 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1728 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1729 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1730 )
1731 IS
1732 l_msg_count NUMBER := 0;
1733 l_msg_data VARCHAR2(2000);
1734 l_return_status VARCHAR2(1);
1735 l_dummy_app_name VARCHAR2(30);
1736 l_enc_msg_data VARCHAR2(2000);
1737 l_msg_name VARCHAR2(30);
1738 l_msg_index_out NUMBER ;
1739 l_debug_mode VARCHAR2(1);
1740
1741 TYPE l_error_msg_name_tbl_type IS TABLE OF
1742 fnd_new_messages.message_text%TYPE INDEX BY BINARY_INTEGER ;
1743 TYPE l_element_name_tbl_type IS TABLE OF
1744 pa_proj_elements.name%TYPE INDEX BY BINARY_INTEGER ;
1745 TYPE l_element_number_tbl_type IS TABLE OF
1746 pa_proj_elements.element_number%TYPE INDEX BY BINARY_INTEGER ;
1747
1748 l_error_msg_name_tbl l_error_msg_name_tbl_type ;
1749 l_element_name_tbl l_element_name_tbl_type ;
1750 l_element_number_tbl l_element_number_tbl_type ;
1751 j NUMBER ;
1752
1753 BEGIN
1754 l_msg_count := 0;
1755 l_return_status := FND_API.G_RET_STS_SUCCESS;
1756 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1757 j := 0 ;
1758
1759 IF l_debug_mode = 'Y' THEN
1760 PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLES_IN_BULK',
1761 p_debug_mode => l_debug_mode );
1762 pa_debug.g_err_stage:= 'Inside DELETE_DELIVERABLES_IN_BULK ';
1763 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1764 END IF;
1765
1766 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1767 FND_MSG_PUB.initialize;
1768 END IF;
1769
1770 SAVEPOINT DELETE_DELIVERABLES ;
1771
1772 IF nvl(p_dlv_element_id_tbl.LAST,0)>0 THEN
1773 IF l_debug_mode = 'Y' THEN
1774 pa_debug.debug('Some row is fetched for deletion');
1775 END IF ;
1776 FOR i in p_dlv_element_id_tbl.FIRST..p_dlv_element_id_tbl.LAST LOOP
1777
1778 IF l_debug_mode = 'Y' THEN
1779 pa_debug.debug('Deliverable Element Id is :'||p_dlv_element_id_tbl(i));
1780 pa_debug.debug('record version id is :'||p_rec_ver_number_tbl(i));
1781 END IF ;
1782
1783 -- initialization is required for every loop
1784 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1785 l_msg_count := 0 ;
1786 l_msg_data := null ;
1787
1788 PA_DELIVERABLE_PVT.DELETE_DELIVERABLE
1789 (p_api_version => p_api_version
1790 ,p_init_msg_list => p_init_msg_list
1791 ,p_commit => p_commit
1792 ,p_validate_only => p_validate_only
1793 ,p_validation_level => p_validation_level
1794 ,p_calling_module => p_calling_module
1795 ,p_debug_mode => l_debug_mode
1796 ,p_max_msg_count => p_max_msg_count
1797 ,p_dlv_element_id => p_dlv_element_id_tbl(i)
1798 ,p_dlv_version_id => p_dlv_version_id_tbl(i)
1799 ,p_rec_ver_number => p_rec_ver_number_tbl(i)
1800 ,p_project_id => p_project_id
1801 ,x_return_status => l_return_status
1802 ,x_msg_count => l_msg_count
1803 ,x_msg_data => l_msg_data
1804 );
1805
1806 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1807
1808 j := j+1 ;
1809
1810
1811 PA_INTERFACE_UTILS_PUB.get_messages
1812 (p_encoded => FND_API.G_FALSE, -- Get the encoded message.
1813 p_msg_index => 1, -- Get the message at index 1.
1814 p_data => l_enc_msg_data,
1815 p_msg_index_out => l_msg_index_out);
1816
1817
1818 l_error_msg_name_tbl(j) := l_enc_msg_data ;
1819 l_element_name_tbl(j) := p_dlv_name_tbl(i) ;
1820 l_element_number_tbl(j) := p_dlv_number_tbl(i) ;
1821
1822
1823 END IF ;
1824
1825 END LOOP ;
1826
1827 IF j > 0 THEN
1828
1829 ROLLBACK TO DELETE_DELIVERABLES;
1830
1831 FND_MSG_PUB.initialize;
1832
1833 FOR k IN l_element_name_tbl.FIRST..l_element_name_tbl.LAST LOOP
1834
1835 PA_UTILS.ADD_MESSAGE
1836 (p_app_short_name => 'PA',
1837 p_msg_name => 'PA_PS_TASK_NAME_NUM_ERR',
1838 p_token1 => 'TASK_NAME',
1839 p_value1 => l_element_name_tbl(k),
1840 p_token2 => 'TASK_NUMBER',
1841 p_value2 => l_element_number_tbl(k),
1842 p_token3 => 'MESSAGE',
1843 p_value3 => l_error_msg_name_tbl(k)
1844 );
1845
1846
1847 END LOOP ;
1848 END IF ;
1849 END IF ;
1850
1851 x_msg_count := FND_MSG_PUB.count_msg ;
1852
1853 IF x_msg_count > 0 THEN
1854 x_return_status := 'E' ;
1855 END IF ;
1856
1857 IF l_debug_mode = 'Y' THEN
1858 pa_debug.reset_curr_function;
1859 END IF ;
1860
1861 EXCEPTION
1862 WHEN OTHERS THEN
1863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1864 x_msg_count := 1;
1865 x_msg_data := SQLERRM;
1866 ROLLBACK TO DELETE_DELIVERABLES;
1867
1868 FND_MSG_PUB.add_exc_msg
1869 ( p_pkg_name => 'PA_DELIVERABLE_PUB'
1870 ,p_procedure_name => 'DELETE_DELIVERABLES_IN_BULK' );
1871 IF l_debug_mode = 'Y' THEN
1872 pa_debug.write('DELETE_DELIVERABLES_IN_BULK' || g_module_name,SQLERRM,4);
1873 pa_debug.write('DELETE_DELIVERABLES_IN_BULK' || g_module_name,pa_debug.G_Err_Stack,4);
1874 pa_debug.reset_curr_function;
1875 END IF;
1876 RAISE ;
1877 END DELETE_DELIVERABLES_IN_BULK ;
1878
1879 -- SubProgram : CREATE_ASSOCIATIONS_IN_BULK
1880 -- Type : PROCEDURE
1881 -- Purpose : Public API to Create associations from Associate Deliverable
1882 -- and Associate Tasks page .
1883 -- Note : This API is called from Associate Deliverable and Associate Tasks page
1884 -- Assumptions : None
1885 -- Parameter IN/OUT Type Required Description and Purpose
1886 -- --------------------------- --------- ---------- --------- ---------------------------
1887 -- p_api_version IN NUMBER N Standard Parameter
1888 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
1889 -- p_commit IN VARCHAR2 N Standard Parameter
1890 -- p_validate_only IN VARCHAR2 N Standard Parameter
1891 -- p_validation_level IN NUMBER N Standard Parameter
1892 -- p_calling_module IN VARCHAR2 N Standard Parameter
1893 -- p_debug_mode IN VARCHAR2 N Standard Parameter
1894 -- p_max_msg_count IN NUMBER N Standard Parameter
1895 -- p_element_id_tbl IN PLSQL Table N PLSQL table of Dlv Element Id
1896 -- p_version_id_tbl IN PLSQL Table N PLSQL table of Dlv Version Id
1897 -- p_element_name_tbl IN PLSQL Table N PLSQL Table of Dlv. Name
1898 -- p_element_number_tbl IN PLSQL Table N PLSQL Table of Dlv. Number
1899 -- p_task_or_dlv_elt_id IN NUMBER Y Task or deliverable element id
1900 -- p_task_or_dlv_elt_id IN NUMBER Y Task or deliverable version id
1901 -- p_project_id IN NUMBER N Project Id
1902 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
1903 -- x_msg_count OUT NUMBER N Standard Out Parameter
1904 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
1905
1906 -- How the data should be passed to this API ?
1907 -- When called from ASSOCIATE DELIVERABLE page
1908 -- p_element_id_tbl will have all the element id of selected deliverables
1909 -- p_version_id_tbl will have all the version id of selected deliverables
1910 -- p_element_name_tbl will have selected deliverables name
1911 -- p_element_number_tbl will have selected deliverables name
1912 -- p_task_or_dlv_elt_id will have Task Id
1913 -- p_task_or_dlv_ver_id will have task version id
1914 -- p_project_id will have project id
1915 -- p_task_or_dlv will be 'PA_TASKS'
1916 -- When called from ASSOCIATE TASKS page
1917 -- p_element_id_tbl will have all the element id of selected tasks
1918 -- p_version_id_tbl will have all the version id of selected tasks
1919 -- p_element_name_tbl will have selected tasks name
1920 -- p_element_number_tbl will have selected tasks name
1921 -- p_task_or_dlv_elt_id will have Deliverable element Id
1922 -- p_task_or_dlv_ver_id will have Deliverable version id
1923 -- p_project_id will have project id
1924 -- p_task_or_dlv will be 'PA_DELIVERABLES'
1925
1926 PROCEDURE CREATE_ASSOCIATIONS_IN_BULK
1927 (p_api_version IN NUMBER :=1.0
1928 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
1929 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
1930 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
1931 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
1932 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
1933 ,p_debug_mode IN VARCHAR2 :='N'
1934 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1935 ,p_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
1936 ,p_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
1937 ,p_element_name_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
1938 ,p_element_number_tbl IN SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE()
1939 ,p_task_or_dlv_elt_id IN NUMBER
1940 ,p_task_or_dlv_ver_id IN NUMBER
1941 ,p_project_id IN pa_projects_all.project_id%TYPE
1942 ,p_task_or_dlv IN VARCHAR2 := 'PA_TASKS'
1943 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1944 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1945 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1946 )
1947 IS
1948 l_debug_mode VARCHAR2(10);
1949 l_msg_count NUMBER ;
1950 l_data VARCHAR2(2000);
1951 l_msg_data VARCHAR2(2000);
1952 l_msg_index_out NUMBER;
1953
1954 BEGIN
1955 l_msg_count := 0;
1956 x_return_status := FND_API.G_RET_STS_SUCCESS;
1957 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1958
1959 IF l_debug_mode = 'Y' THEN
1960 PA_DEBUG.set_curr_function( p_function => 'CREATE_ASSOCIATIONS_IN_BULK',
1961 p_debug_mode => l_debug_mode );
1962 pa_debug.g_err_stage:= 'Inside CREATE_ASSOCIATIONS_IN_BULK ';
1963 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1964 END IF;
1965
1966 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
1967 FND_MSG_PUB.initialize;
1968 END IF;
1969
1970 IF (p_commit = FND_API.G_TRUE) THEN
1971 SAVEPOINT CREATE_ASSOCIATIONS_SP ;
1972 END IF ;
1973
1974 IF (p_task_or_dlv IS NULL OR p_task_or_dlv_elt_id IS NULL OR p_task_or_dlv_ver_id IS NULL )
1975 THEN
1976 IF l_debug_mode = 'Y' THEN
1977 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
1978 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1979 END IF;
1980
1981 x_return_status := FND_API.G_RET_STS_ERROR;
1982 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1983 ,p_msg_name => 'PA_INV_PARAM_PASSED');
1984 RAISE Invalid_Arg_Exc_Dlv;
1985 END IF;
1986
1987 IF nvl(p_element_id_tbl.LAST,0)>0 THEN
1988 PA_DELIVERABLE_PVT.CREATE_ASSOCIATIONS_IN_BULK
1989 (p_api_version => p_api_version
1990 ,p_init_msg_list => FND_API.G_FALSE
1991 ,p_commit => p_commit
1992 ,p_validate_only => p_validate_only
1993 ,p_validation_level => p_validation_level
1994 ,p_calling_module => p_calling_module
1995 ,p_debug_mode => l_debug_mode
1996 ,p_max_msg_count => p_max_msg_count
1997 ,p_element_id_tbl => p_element_id_tbl
1998 ,p_version_id_tbl => p_version_id_tbl
1999 ,p_element_name_tbl => p_element_name_tbl
2000 ,p_element_number_tbl => p_element_number_tbl
2001 ,p_task_or_dlv_elt_id => p_task_or_dlv_elt_id
2002 ,p_task_or_dlv_ver_id => p_task_or_dlv_ver_id
2003 ,p_project_id => p_project_id
2004 ,p_task_or_dlv => p_task_or_dlv
2005 ,x_return_status => x_return_status
2006 ,x_msg_count => x_msg_count
2007 ,x_msg_data => x_msg_data
2008 ) ;
2009 END IF ;
2010
2011 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2012 RAISE Invalid_Arg_Exc_Dlv ;
2013 END IF ;
2014
2015 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2016 pa_debug.reset_curr_function;
2017 END IF ;
2018
2019 EXCEPTION
2020 WHEN Invalid_Arg_Exc_Dlv THEN
2021 x_return_status := FND_API.G_RET_STS_ERROR;
2022 l_msg_count := FND_MSG_PUB.count_msg;
2023
2024 IF (p_commit = FND_API.G_TRUE) THEN
2025 ROLLBACK TO CREATE_ASSOCIATIONS_SP;
2026 END IF ;
2027
2028 IF l_debug_mode = 'Y' THEN
2029 pa_debug.g_err_stage := 'inside invalid arg exception of CREATE_ASSOCIATIONS_IN_BULK';
2030 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2031 END IF;
2032
2033 IF l_msg_count = 1 THEN
2034 PA_INTERFACE_UTILS_PUB.get_messages
2035 (p_encoded => FND_API.G_TRUE,
2036 p_msg_index => 1,
2037 p_msg_count => l_msg_count,
2038 p_msg_data => l_msg_data,
2039 p_data => l_data,
2040 p_msg_index_out => l_msg_index_out);
2041 x_msg_data := l_data;
2042 x_msg_count := l_msg_count;
2043 ELSE
2044 x_msg_count := l_msg_count;
2045 END IF;
2046 IF l_debug_mode = 'Y' THEN
2047 pa_debug.reset_curr_function;
2048 END IF ;
2049 RETURN;
2050 WHEN OTHERS THEN
2051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052 x_msg_count := 1;
2053 x_msg_data := SQLERRM;
2054
2055 IF (p_commit = FND_API.G_TRUE) THEN
2056 ROLLBACK TO CREATE_ASSOCIATIONS_SP;
2057 END IF ;
2058
2059 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PUB'
2060 ,p_procedure_name => 'CREATE_ASSOCIATIONS_IN_BULK');
2061
2062 IF p_debug_mode = 'Y' THEN
2063 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2064 pa_debug.write('CREATE_ASSOCIATIONS_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
2065 pa_debug.reset_curr_function;
2066 END IF;
2067 RAISE;
2068 END CREATE_ASSOCIATIONS_IN_BULK ;
2069
2070 -- SubProgram : DELETE_DELIVERABLE_STRUCTURE
2071 -- Type : PROCEDURE
2072 -- Purpose : Public API to Delete Multiple Deliverables from Deliverable List Page
2073 -- Note : This API is called from Deliverable List Page
2074 -- Assumptions : None
2075 -- Parameter IN/OUT Type Required Description and Purpose
2076 -- --------------------------- --------- ---------- --------- ---------------------------
2077 -- p_api_version IN NUMBER N Standard Parameter
2078 -- p_init_msg_list IN VARCHAR2 N Standard Parameter
2079 -- p_commit IN VARCHAR2 N Standard Parameter
2080 -- p_validate_only IN VARCHAR2 N Standard Parameter
2081 -- p_validation_level IN NUMBER N Standard Parameter
2082 -- p_calling_module IN VARCHAR2 N Standard Parameter
2083 -- p_debug_mode IN VARCHAR2 N Standard Parameter
2084 -- p_max_msg_count IN NUMBER N Standard Parameter
2085 -- p_project_id IN NUMBER N Project Id
2086 -- x_return_status OUT VARCHAR2 N Standard Out Parameter
2087 -- x_msg_count OUT NUMBER N Standard Out Parameter
2088 -- x_msg_data OUT VARCHAR2 N Standard Out Parameter
2089
2090 PROCEDURE DELETE_DELIVERABLE_STRUCTURE
2091 (p_api_version IN NUMBER :=1.0
2092 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2093 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2094 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2095 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2096 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2097 ,p_debug_mode IN VARCHAR2 :='N'
2098 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2099 ,p_project_id IN pa_projects_all.project_id%TYPE
2100 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2101 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2102 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2103 )
2104 IS
2105 l_msg_count NUMBER := 0;
2106 l_msg_data VARCHAR2(2000);
2107 l_data VARCHAR2(2000);
2108 l_return_status VARCHAR2(1);
2109 l_msg_index_out NUMBER ;
2110 l_debug_mode VARCHAR2(1);
2111 l_proj_element_id NUMBER ;
2112 l_element_version_id NUMBER ;
2113 l_return_flag VARCHAR2(1);
2114
2115
2116 BEGIN
2117 l_msg_count := 0;
2118 l_return_status := FND_API.G_RET_STS_SUCCESS;
2119 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2120
2121
2122 IF l_debug_mode = 'Y' THEN
2123 PA_DEBUG.set_curr_function( p_function => 'DELETE_DELIVERABLE_STRUCTURE',
2124 p_debug_mode => l_debug_mode );
2125 pa_debug.g_err_stage:= 'Inside DELETE_DELIVERABLE_STRUCTURE ';
2126 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2127 END IF;
2128
2129 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2130 FND_MSG_PUB.initialize;
2131 END IF;
2132
2133 IF (p_commit = FND_API.G_TRUE) THEN
2134 SAVEPOINT DELETE_DELIVERABLE_STRUCTURE;
2135 END IF ;
2136
2137 IF (p_project_id IS NULL )
2138 THEN
2139 IF l_debug_mode = 'Y' THEN
2140 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
2141 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2142 END IF;
2143
2144 x_return_status := FND_API.G_RET_STS_ERROR;
2145 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
2146 ,p_msg_name => 'PA_INV_PARAM_PASSED');
2147 RAISE Invalid_Arg_Exc_Dlv;
2148 END IF;
2149
2150 IF l_debug_mode = 'Y' THEN
2151 pa_debug.g_err_stage:= 'Calling PA_DELIVERABLE_PVT.DELETE_DELIVERABLE_STRUCTURE';
2152 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2153 END IF;
2154
2155 -- Call the pvt API
2156 PA_DELIVERABLE_PVT.DELETE_DELIVERABLE_STRUCTURE
2157 (p_debug_mode => l_debug_mode
2158 ,p_project_id => p_project_id
2159 ,x_return_status => x_return_status
2160 ,x_msg_count => x_msg_count
2161 ,x_msg_data => x_msg_data
2162 ) ;
2163
2164 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2165 RAISE Invalid_Arg_Exc_Dlv ;
2166 END IF ;
2167
2168
2169 IF l_debug_mode = 'Y' THEN
2170 pa_debug.reset_curr_function;
2171 END IF ;
2172 EXCEPTION
2173 WHEN Invalid_Arg_Exc_Dlv THEN
2174 x_return_status := FND_API.G_RET_STS_ERROR;
2175 l_msg_count := FND_MSG_PUB.count_msg;
2176
2177 IF (p_commit = FND_API.G_TRUE) THEN
2178 ROLLBACK TO DELETE_DELIVERABLE_STRUCTURE;
2179 END IF ;
2180
2181 IF l_debug_mode = 'Y' THEN
2182 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DELIVERABLE_STRUCTURE';
2183 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2184 END IF;
2185
2186 IF l_msg_count = 1 THEN
2187 PA_INTERFACE_UTILS_PUB.get_messages
2188 (p_encoded => FND_API.G_TRUE,
2189 p_msg_index => 1,
2190 p_msg_count => l_msg_count,
2191 p_msg_data => l_msg_data,
2192 p_data => l_data,
2193 p_msg_index_out => l_msg_index_out);
2194 x_msg_data := l_data;
2195 x_msg_count := l_msg_count;
2196 ELSE
2197 x_msg_count := l_msg_count;
2198 END IF;
2199 IF l_debug_mode = 'Y' THEN
2200 pa_debug.reset_curr_function;
2201 END IF ;
2202 RETURN;
2203 WHEN OTHERS THEN
2204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205 x_msg_count := 1;
2206 x_msg_data := SQLERRM;
2207
2208 IF (p_commit = FND_API.G_TRUE) THEN
2209 ROLLBACK TO DELETE_DELIVERABLE_STRUCTURE;
2210 END IF ;
2211
2212 FND_MSG_PUB.add_exc_msg
2213 ( p_pkg_name => 'PA_DELIVERABLE_PUB'
2214 ,p_procedure_name => 'DELETE_DELIVERABLE_STRUCTURE' );
2215 IF l_debug_mode = 'Y' THEN
2216 pa_debug.write('DELETE_DELIVERABLE_STRUCTURE' || g_module_name,SQLERRM,4);
2217 pa_debug.write('DELETE_DELIVERABLE_STRUCTURE' || g_module_name,pa_debug.G_Err_Stack,4);
2218 pa_debug.reset_curr_function;
2219 END IF;
2220 RAISE ;
2221 END DELETE_DELIVERABLE_STRUCTURE ;
2222
2223 PROCEDURE DELETE_DLV_TASK_ASSCN_IN_BULK
2224 (p_api_version IN NUMBER :=1.0
2225 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2226 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2227 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2228 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2229 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2230 ,p_debug_mode IN VARCHAR2 :='N'
2231 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2232 ,p_calling_context IN VARCHAR2 := 'PA_TASKS'
2233 ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
2234 ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
2235 ,p_project_id IN pa_projects_all.project_id%TYPE
2236 ,p_delete_or_validate IN VARCHAR2 := 'B' -- 3955848 V- Validate , D - Delete, B - Validate and Delete
2237 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2238 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2239 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2240 )
2241 IS
2242 l_debug_mode VARCHAR2(10);
2243 l_msg_count NUMBER ;
2244 l_data VARCHAR2(2000);
2245 l_msg_data VARCHAR2(2000);
2246 l_msg_index_out NUMBER;
2247 l_dummy VARCHAR2(1) ;
2248
2249 BEGIN
2250
2251 x_msg_count := 0;
2252 x_return_status := FND_API.G_RET_STS_SUCCESS;
2253 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2254
2255 IF l_debug_mode = 'Y' THEN
2256 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_TASK_ASSCN_IN_BULK',
2257 p_debug_mode => l_debug_mode );
2258 pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSCN_IN_BULK ';
2259 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2260 END IF;
2261
2262 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2263 FND_MSG_PUB.initialize;
2264 END IF;
2265
2266 IF (p_commit = FND_API.G_TRUE) THEN
2267 ROLLBACK TO DELETE_DLV_TASK_ASSCN_IN_BULK ;
2268 END IF ;
2269
2270 -- 3651542 Removed "p_task_version_id IS NULL" check from below IF condition
2271 -- IF (p_task_element_id IS NULL OR p_task_version_id IS NULL OR p_project_id IS NULL )
2272 IF (p_task_element_id IS NULL OR p_project_id IS NULL )
2273 THEN
2274 IF l_debug_mode = 'Y' THEN
2275 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
2276 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2277 END IF;
2278
2279 x_return_status := FND_API.G_RET_STS_ERROR;
2280 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
2281 ,p_msg_name => 'PA_INV_PARAM_PASSED');
2282 RAISE Invalid_Arg_Exc_Dlv;
2283 END IF;
2284
2285 PA_DELIVERABLE_PVT.DELETE_DLV_TASK_ASSCN_IN_BULK
2286 (p_api_version => p_api_version
2287 ,p_init_msg_list => p_init_msg_list
2288 ,p_commit => p_commit
2289 ,p_validate_only => p_validate_only
2290 ,p_validation_level => p_validation_level
2291 ,p_calling_module => p_calling_module
2292 ,p_debug_mode => l_debug_mode
2293 ,p_max_msg_count => p_max_msg_count
2294 ,p_calling_context => p_calling_context
2295 ,p_task_element_id => p_task_element_id
2296 ,p_task_version_id => p_task_version_id
2297 ,p_project_id => p_project_id
2298 ,p_delete_or_validate => p_delete_or_validate -- 3955848 passing it to pvt api
2299 ,x_return_status => x_return_status
2300 ,x_msg_count => x_msg_count
2301 ,x_msg_data => x_msg_data
2302 ) ;
2303
2304 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2305 RAISE Invalid_Arg_Exc_Dlv ;
2306 END IF ;
2307
2308 IF l_debug_mode = 'Y' THEN
2309 pa_debug.g_err_stage:= 'Exiting DELETE_DLV_TASK_ASSCN_IN_BULK' ;
2310 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2311 pa_debug.reset_curr_function;
2312 END IF;
2313
2314 EXCEPTION
2315 WHEN Invalid_Arg_Exc_Dlv THEN
2316 x_return_status := FND_API.G_RET_STS_ERROR;
2317 l_msg_count := FND_MSG_PUB.count_msg;
2318
2319 IF l_debug_mode = 'Y' THEN
2320 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_TASK_ASSCN_IN_BULK';
2321 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2322 END IF;
2323
2324 IF (p_commit = FND_API.G_TRUE) THEN
2325 ROLLBACK TO DELETE_DLV_TASK_ASSCN_IN_BULK ;
2326 END IF ;
2327
2328 IF l_msg_count = 1 THEN
2329 PA_INTERFACE_UTILS_PUB.get_messages
2330 (p_encoded => FND_API.G_TRUE,
2331 p_msg_index => 1,
2332 p_msg_count => l_msg_count,
2333 p_msg_data => l_msg_data,
2334 p_data => l_data,
2335 p_msg_index_out => l_msg_index_out);
2336 x_msg_data := l_data;
2337 x_msg_count := l_msg_count;
2338 ELSE
2339 x_msg_count := l_msg_count;
2340 END IF;
2341 IF l_debug_mode = 'Y' THEN
2342 pa_debug.reset_curr_function;
2343 END IF ;
2344 RETURN;
2345 WHEN OTHERS THEN
2346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2347 x_msg_count := 1;
2348 x_msg_data := SQLERRM;
2349
2350 IF (p_commit = FND_API.G_TRUE) THEN
2351 ROLLBACK TO DELETE_DLV_TASK_ASSCN_IN_BULK ;
2352 END IF ;
2353
2354 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
2355 ,p_procedure_name => 'DELETE_DLV_TASK_ASSCN_IN_BULK');
2356
2357 IF p_debug_mode = 'Y' THEN
2358 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2359 pa_debug.write('DELETE_DLV_TASK_ASSCN_IN_BULK: ' || g_module_name,pa_debug.g_err_stage,5);
2360 pa_debug.reset_curr_function;
2361 END IF;
2362 RAISE;
2363 END DELETE_DLV_TASK_ASSCN_IN_BULK ;
2364
2365 PROCEDURE DELETE_DLV_ASSOCIATIONS
2366 (p_api_version IN NUMBER :=1.0
2367 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2368 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2369 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2370 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2371 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2372 ,p_debug_mode IN VARCHAR2 :='N'
2373 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2374 ,p_project_id IN NUMBER
2375 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2376 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2377 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2378 )
2379 IS
2380 l_debug_mode VARCHAR2(10);
2381 l_msg_count NUMBER ;
2382 l_data VARCHAR2(2000);
2383 l_msg_data VARCHAR2(2000);
2384 l_msg_index_out NUMBER;
2385 l_dummy VARCHAR2(1) ;
2386 l_return_flag VARCHAR2(1) ;
2387
2388 BEGIN
2389
2390 x_msg_count := 0;
2391 x_return_status := FND_API.G_RET_STS_SUCCESS;
2392 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2393
2394 IF l_debug_mode = 'Y' THEN
2395 PA_DEBUG.set_curr_function( p_function => 'DELETE_DLV_ASSOCIATIONS',
2396 p_debug_mode => l_debug_mode );
2397 pa_debug.g_err_stage:= 'Inside DELETE_DLV_TASK_ASSOCIATION ';
2398 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2399 END IF;
2400
2401 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2402 FND_MSG_PUB.initialize;
2403 END IF;
2404
2405 IF (p_project_id IS NULL )
2406 THEN
2407 IF l_debug_mode = 'Y' THEN
2408 pa_debug.g_err_stage:= 'INVALID INPUT PARAMETER';
2409 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2410 END IF;
2411
2412 x_return_status := FND_API.G_RET_STS_ERROR;
2413 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
2414 ,p_msg_name => 'PA_INV_PARAM_PASSED');
2415 RAISE Invalid_Arg_Exc_Dlv;
2416 END IF;
2417
2418 IF l_debug_mode = 'Y' THEN
2419 pa_debug.g_err_stage:= 'Calling PA_DELIVERABLE_PVT.DELETE_DLV_ASSOCIATIONS';
2420 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2421 END IF ;
2422
2423 PA_DELIVERABLE_PVT.DELETE_DLV_ASSOCIATIONS
2424 (p_api_version => p_api_version
2425 ,p_init_msg_list => p_init_msg_list
2426 ,p_commit => p_commit
2427 ,p_validate_only => p_validate_only
2428 ,p_validation_level => p_validation_level
2429 ,p_calling_module => p_calling_module
2430 ,p_debug_mode => l_debug_mode
2431 ,p_max_msg_count => p_max_msg_count
2432 ,p_project_id => p_project_id
2433 ,x_return_status => x_return_status
2434 ,x_msg_count => x_msg_count
2435 ,x_msg_data => x_msg_data
2436 ) ;
2437
2438 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2439 RAISE Invalid_Arg_Exc_Dlv ;
2440 END IF ;
2441
2442 IF l_debug_mode = 'Y' THEN
2443 pa_debug.g_err_stage:= 'Exiting DELETE_DLV_ASSOCIATIONS' ;
2444 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2445 pa_debug.reset_curr_function;
2446 END IF;
2447
2448 EXCEPTION
2449 WHEN Invalid_Arg_Exc_Dlv THEN
2450 x_return_status := FND_API.G_RET_STS_ERROR;
2451 l_msg_count := FND_MSG_PUB.count_msg;
2452
2453 IF l_debug_mode = 'Y' THEN
2454 pa_debug.g_err_stage := 'inside invalid arg exception of DELETE_DLV_ASSOCIATIONS';
2455 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2456 END IF;
2457
2458 IF l_msg_count = 1 THEN
2459 PA_INTERFACE_UTILS_PUB.get_messages
2460 (p_encoded => FND_API.G_TRUE,
2461 p_msg_index => 1,
2462 p_msg_count => l_msg_count,
2463 p_msg_data => l_msg_data,
2464 p_data => l_data,
2465 p_msg_index_out => l_msg_index_out);
2466 x_msg_data := l_data;
2467 x_msg_count := l_msg_count;
2468 ELSE
2469 x_msg_count := l_msg_count;
2470 END IF;
2471 IF l_debug_mode = 'Y' THEN
2472 pa_debug.reset_curr_function;
2473 END IF ;
2474 RETURN;
2475 WHEN OTHERS THEN
2476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2477 x_msg_count := 1;
2478 x_msg_data := SQLERRM;
2479
2480 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLES_PVT'
2481 ,p_procedure_name => 'DELETE_DLV_ASSOCIATIONS');
2482
2483 IF p_debug_mode = 'Y' THEN
2484 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2485 pa_debug.write('DELETE_DLV_ASSOCIATIONS: ' || g_module_name,pa_debug.g_err_stage,5);
2486 pa_debug.reset_curr_function;
2487 END IF;
2488 RAISE;
2489 END DELETE_DLV_ASSOCIATIONS ;
2490
2491 PROCEDURE COPY_DELIVERABLES
2492 (p_api_version IN NUMBER :=1.0
2493 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2494 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2495 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2496 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2497 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2498 ,p_debug_mode IN VARCHAR2 :='N'
2499 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2500 ,p_source_project_id IN NUMBER
2501 ,p_target_project_id IN NUMBER
2502 ,p_dlv_element_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
2503 ,p_dlv_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
2504 ,p_item_details_flag IN VARCHAR2 := 'N'
2505 ,p_dlv_actions_flag IN VARCHAR2 := 'N'
2506 ,p_dlv_attachments_flag IN VARCHAR2 := 'N'
2507 ,p_association_flag IN VARCHAR2 := 'N'
2508 ,p_prefix IN VARCHAR2 := null
2509 ,p_delta IN NUMBER := null
2510 ,p_calling_context IN VARCHAR2
2511 ,p_task_id IN NUMBER :=null --Bug 3429393
2512 ,p_task_version_id IN NUMBER :=null --Bug 3429393
2513 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2514 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2515 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2516 )
2517 IS
2518 l_debug_mode VARCHAR2(10);
2519 l_msg_count NUMBER ;
2520 l_data VARCHAR2(2000);
2521 l_msg_data VARCHAR2(2000);
2522 l_msg_index_out NUMBER;
2523 l_dummy VARCHAR2(1) ;
2524 l_return_flag VARCHAR2(1) ;
2525 BEGIN
2526 x_msg_count := 0;
2527 x_return_status := FND_API.G_RET_STS_SUCCESS;
2528 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2529
2530 IF l_debug_mode = 'Y' THEN
2531 PA_DEBUG.set_curr_function( p_function => 'COPY_DELIVERABLES',
2532 p_debug_mode => l_debug_mode );
2533 pa_debug.g_err_stage:= 'Inside COPY_DELIVERABLES ';
2534 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2535 END IF;
2536
2537 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2538 FND_MSG_PUB.initialize;
2539 END IF;
2540 IF (p_commit = FND_API.G_TRUE) THEN
2541 SAVEPOINT COPY_DELIVERABLES_SP ;
2542 END IF ;
2543 --Bug 3429393
2544 IF l_debug_mode = 'Y' THEN
2545 pa_debug.g_err_stage:= 'p_source_project_id is '||p_source_project_id;
2546 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2547 pa_debug.g_err_stage:= 'p_target_project_id is '||p_target_project_id;
2548 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2549 pa_debug.g_err_stage:= 'p_calling_context is '||p_calling_context;
2550 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2551 pa_debug.g_err_stage:= 'p_task_id is '||p_task_id;
2552 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2553 pa_debug.g_err_stage:= 'p_task_version_id is '||p_task_version_id;
2554 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2555 END IF;
2556
2557 PA_DELIVERABLE_PVT.COPY_DELIVERABLES
2558 (p_api_version => p_api_version
2559 ,p_init_msg_list => FND_API.G_FALSE
2560 ,p_debug_mode => l_debug_mode
2561 ,p_source_project_id => p_source_project_id
2562 ,p_target_project_id => p_target_project_id
2563 ,p_dlv_element_id_tbl => p_dlv_element_id_tbl
2564 ,p_dlv_version_id_tbl => p_dlv_version_id_tbl
2565 ,p_item_details_flag => p_item_details_flag
2566 ,p_dlv_actions_flag => p_dlv_actions_flag
2567 ,p_dlv_attachments_flag => p_dlv_attachments_flag
2568 ,p_association_flag => p_association_flag
2569 ,p_prefix => p_prefix
2570 ,p_delta => p_delta
2571 ,p_calling_context => p_calling_context
2572 ,p_task_id => p_task_id --Bug 3429393
2573 ,p_task_version_id => p_task_version_id --Bug 3429393
2574 ,x_return_status => x_return_status
2575 ,x_msg_count => x_msg_count
2576 ,x_msg_data => x_msg_data
2577 ) ;
2578
2579 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2580 RAISE Invalid_Arg_Exc_Dlv ;
2581 END IF ;
2582 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2583 pa_debug.reset_curr_function;
2584 END IF ;
2585 EXCEPTION
2586 WHEN Invalid_Arg_Exc_Dlv THEN
2587 x_return_status := FND_API.G_RET_STS_ERROR;
2588 l_msg_count := FND_MSG_PUB.count_msg;
2589
2590 IF (p_commit = FND_API.G_TRUE) THEN
2591 ROLLBACK TO COPY_DELIVERABLES_SP ;
2592 END IF ;
2593
2594 IF l_debug_mode = 'Y' THEN
2595 pa_debug.g_err_stage := 'inside invalid arg exception of COPY_DELIVERABLES';
2596 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2597 END IF;
2598
2599 IF l_msg_count = 1 THEN
2600 PA_INTERFACE_UTILS_PUB.get_messages
2601 (p_encoded => FND_API.G_TRUE,
2602 p_msg_index => 1,
2603 p_msg_count => l_msg_count,
2604 p_msg_data => l_msg_data,
2605 p_data => l_data,
2606 p_msg_index_out => l_msg_index_out);
2607 x_msg_data := l_data;
2608 x_msg_count := l_msg_count;
2609 ELSE
2610 x_msg_count := l_msg_count;
2611 END IF;
2612 IF l_debug_mode = 'Y' THEN
2613 pa_debug.reset_curr_function;
2614 END IF ;
2615 RETURN;
2616 WHEN OTHERS THEN
2617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2618 x_msg_count := 1;
2619 x_msg_data := SQLERRM;
2620
2621 IF (p_commit = FND_API.G_TRUE) THEN
2622 ROLLBACK TO COPY_DELIVERABLES_SP ;
2623 END IF ;
2624
2625 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLE_PUB'
2626 ,p_procedure_name => 'COPY_DELIVERABLES');
2627
2628 IF p_debug_mode = 'Y' THEN
2629 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2630 pa_debug.write('DELETE_DLV_ASSOCIATIONS: ' || g_module_name,pa_debug.g_err_stage,5);
2631 pa_debug.reset_curr_function;
2632 END IF;
2633 RAISE;
2634 END COPY_DELIVERABLES ;
2635
2636 PROCEDURE COPY_ASSOCIATIONS
2637 (p_api_version IN NUMBER :=1.0
2638 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
2639 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
2640 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
2641 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
2642 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
2643 ,p_debug_mode IN VARCHAR2 :='N'
2644 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
2645 ,p_src_task_versions_tab IN SYSTEM.pa_num_tbl_type
2646 ,p_dest_task_versions_tab IN SYSTEM.pa_num_tbl_type
2647 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2648 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2649 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2650 )
2651 IS
2652 l_debug_mode VARCHAR2(10);
2653 l_msg_count NUMBER ;
2654 l_data VARCHAR2(2000);
2655 l_msg_data VARCHAR2(2000);
2656 l_msg_index_out NUMBER;
2657 l_dummy VARCHAR2(1) ;
2658 l_proj_element_id NUMBER ;
2659 l_project_id NUMBER ;
2660 l_task_element_id NUMBER ;
2661
2662
2663 l_element_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
2664 l_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ;
2665 l_element_name_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
2666 l_element_number_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE() ;
2667
2668 -- 3461959 : Task_To_Deliverable association not getting copied
2669 -- changed the c_source_deliverable query
2670 -- changed the c_get_element_id query
2671
2672 CURSOR c_source_deliverable (c_task_version_id IN NUMBER )
2673 IS
2674 SELECT ppe.proj_element_id
2675 ,pev1.element_version_id
2676 ,ppe.element_number
2677 ,ppe.name
2678 FROM pa_proj_elements ppe
2679 ,pa_proj_element_versions pev1
2680 ,pa_proj_element_versions pev2
2681 ,pa_object_relationships obj
2682 WHERE pev2.element_version_id = c_task_version_id
2683 AND obj.object_id_from2 = pev2.proj_element_id
2684 AND obj.object_type_from = 'PA_TASKS'
2685 AND obj.object_type_to = 'PA_DELIVERABLES'
2686 AND obj.relationship_type = 'A'
2687 AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
2688 AND obj.object_id_to2 = ppe.proj_element_id
2689 AND pev1.proj_element_id = ppe.proj_element_id ; -- 3461959 changed from element_version_id to proj_element_id
2690
2691 CURSOR c_get_element_id(c_task_version_id IN NUMBER )
2692 IS
2693 SELECT ppe.proj_element_id
2694 ,ppe.project_id
2695 FROM pa_proj_elements ppe ,
2696 pa_proj_element_versions pev
2697 WHERE pev.element_version_id = c_task_version_id
2698 AND ppe.proj_element_id = pev.proj_element_id
2699 AND nvl(ppe.base_percent_comp_deriv_code,'X') <> 'DELIVERABLE' ; -- 3461959 added nvl function
2700
2701 BEGIN
2702 x_msg_count := 0;
2703 x_return_status := FND_API.G_RET_STS_SUCCESS;
2704 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2705
2706 IF l_debug_mode = 'Y' THEN
2707 PA_DEBUG.set_curr_function( p_function => 'COPY_ASSOCIATIONS',
2708 p_debug_mode => l_debug_mode );
2709 pa_debug.g_err_stage:= 'Inside COPY_ASSOCIATIONS ';
2710 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2711 END IF;
2712
2713 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
2714 FND_MSG_PUB.initialize;
2715 END IF;
2716
2717 IF (p_commit = FND_API.G_TRUE) THEN
2718 SAVEPOINT COPY_ASSOCIATIONS_SP ;
2719 END IF ;
2720
2721 FOR i IN p_src_task_versions_tab.FIRST..p_src_task_versions_tab.LAST LOOP
2722
2723 OPEN c_source_deliverable (p_src_task_versions_tab(i));
2724 FETCH c_source_deliverable BULK COLLECT INTO
2725 l_element_id_tbl
2726 ,l_version_id_tbl
2727 ,l_element_name_tbl
2728 ,l_element_number_tbl ;
2729 CLOSE c_source_deliverable ;
2730
2731 IF nvl(l_element_id_tbl.LAST,0)>0 THEN
2732
2733 OPEN c_get_element_id(p_src_task_versions_tab(i));
2734 FETCH c_get_element_id INTO l_proj_element_id,l_project_id ;
2735 IF c_get_element_id%NOTFOUND THEN
2736 PA_UTILS.ADD_MESSAGE('PA','PA_DLV_COPY_TASK_ERR');
2737 RAISE Invalid_Arg_Exc_Dlv ;
2738 END IF ;
2739 CLOSE c_get_element_id ;
2740
2741 -- 3461959 added the below code to fetch proj_element_id for destination
2742 -- task
2743
2744 OPEN c_get_element_id(p_dest_task_versions_tab(i));
2745 FETCH c_get_element_id INTO l_task_element_id,l_project_id ;
2746 CLOSE c_get_element_id ;
2747
2748 PA_DELIVERABLE_PUB.CREATE_ASSOCIATIONS_IN_BULK
2749 (p_element_id_tbl => l_element_id_tbl
2750 ,p_version_id_tbl => l_version_id_tbl
2751 ,p_element_name_tbl => l_element_name_tbl
2752 ,p_element_number_tbl => l_element_number_tbl
2753 ,p_task_or_dlv_elt_id => l_task_element_id -- 3461959 changed from l_proj_element_id to l_task_element_id
2754 ,p_task_or_dlv_ver_id => p_dest_task_versions_tab(i)
2755 ,p_project_id => l_project_id
2756 ,p_task_or_dlv => 'PA_TASKS'
2757 ,x_return_status => x_return_status
2758 ,x_msg_count => x_msg_count
2759 ,x_msg_data => x_msg_data
2760 ) ;
2761 END IF ;
2762
2763 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2764 RAISE Invalid_Arg_Exc_Dlv ;
2765 END IF ;
2766 END LOOP ;
2767
2768 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2769 pa_debug.reset_curr_function;
2770 END IF ;
2771
2772 EXCEPTION
2773 WHEN Invalid_Arg_Exc_Dlv THEN
2774 x_return_status := FND_API.G_RET_STS_ERROR;
2775 l_msg_count := FND_MSG_PUB.count_msg;
2776
2777 IF (p_commit = FND_API.G_TRUE) THEN
2778 ROLLBACK TO COPY_ASSOCIATIONS_SP ;
2779 END IF ;
2780
2781 IF l_debug_mode = 'Y' THEN
2782 pa_debug.g_err_stage := 'inside invalid arg exception of COPY_ASSOCIATIONS';
2783 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2784 END IF;
2785
2786 IF l_msg_count = 1 THEN
2787 PA_INTERFACE_UTILS_PUB.get_messages
2788 (p_encoded => FND_API.G_TRUE,
2789 p_msg_index => 1,
2790 p_msg_count => l_msg_count,
2791 p_msg_data => l_msg_data,
2792 p_data => l_data,
2793 p_msg_index_out => l_msg_index_out);
2794 x_msg_data := l_data;
2795 x_msg_count := l_msg_count;
2796 ELSE
2797 x_msg_count := l_msg_count;
2798 END IF;
2799 IF l_debug_mode = 'Y' THEN
2800 pa_debug.reset_curr_function;
2801 END IF ;
2802 RETURN;
2803 WHEN OTHERS THEN
2804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2805 x_msg_count := 1;
2806 x_msg_data := SQLERRM;
2807
2808 IF (p_commit = FND_API.G_TRUE) THEN
2809 ROLLBACK TO COPY_ASSOCIATIONS_SP ;
2810 END IF ;
2811
2812 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLE_PUB'
2813 ,p_procedure_name => 'COPY_ASSOCIATIONS');
2814
2815 IF p_debug_mode = 'Y' THEN
2816 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2817 pa_debug.write('COPY_ASSOCIATIONS: ' || g_module_name,pa_debug.g_err_stage,5);
2818 pa_debug.reset_curr_function;
2819 END IF;
2820 RAISE;
2821 END COPY_ASSOCIATIONS ;
2822
2823 END PA_DELIVERABLE_PUB;