1 PACKAGE BODY PA_DELIVERABLE_UTILS AS
2 /* $Header: PADLUTLB.pls 120.8 2007/11/23 06:02:29 rthumma ship $ */
3 g_module_name VARCHAR2(100) := 'pa.plsql.PA_DELIVERABLE_UTILS';
4 l_debug_level2 CONSTANT NUMBER := 2;
5 l_debug_level3 CONSTANT NUMBER := 3;
6 l_debug_level4 CONSTANT NUMBER := 4;
7 l_debug_level5 CONSTANT NUMBER := 5;
8
9
10 -- SubProgram : IS_DLV_TYPE_NAME_UNIQUE
11 -- Type : UTIL FUNCTION
12 -- Purpose : This function will check whether deliverable type name is unique
13 -- Note : If the passed deliverable type name already exists in the database then it is not unique
14 -- Assumptions : None
15 -- Parameter IN/OUT Type Required Description and Purpose
16 -- -------------------------- ------------ ---------- ---------- --------------------------
17
18 -- P_deliverable_type_name IN VARCHAR2 N Deliverable Type Name
19
20 FUNCTION IS_DLV_TYPE_NAME_UNIQUE
21 (
22 p_deliverable_type_name IN VARCHAR2
23 ) RETURN VARCHAR2
24 IS
25
26 l_return_status varchar2(1);
27 l_dummy varchar2(1);
28
29
30 CURSOR c_deliverable_type_name_exists IS
31 SELECT 'X'
32 FROM PA_TASK_TYPES
33 WHERE TASK_TYPE = p_deliverable_type_name -- 3946664 removed upper from both the sides
34 AND OBJECT_TYPE='PA_DLVR_TYPES';
35
36 --The cursor c_deliverable_type_name_exists returns 'X'
37 --if there is atleast one row with the same deliverable type name
38
39 BEGIN
40 OPEN c_deliverable_type_name_exists;
41 FETCH c_deliverable_type_name_exists into l_dummy ;
42 IF c_deliverable_type_name_exists%found THEN
43 l_return_status:='N';
44 ELSE
45 l_return_status:='Y';
46 END IF;
47 CLOSE c_deliverable_type_name_exists;
48
49 return l_return_status;
50
51 END IS_DLV_TYPE_NAME_UNIQUE;
52
53 -- SubProgram : IS_DLV_TYPE_IN_USE
54 -- Type : UTIL FUNCTION
55 -- Purpose : This function will check whether deliverable type is in use
56 -- Note : If there is an entry for the deliverable type id in the pa_proj_elements table
57 -- it means that deliverable type is in use.
58 -- Assumptions : None
59 -- Parameter IN/OUT Type Required Description and Purpose
60 -- --------------------------- ------------ ---------- --------- ---------------------------
61
62 -- p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE N Deliverable Type Id
63
64
65 FUNCTION IS_DLV_TYPE_IN_USE
66 (
67 p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE
68 ) RETURN VARCHAR2
69 IS
70
71 l_return_status varchar2(1);
72 l_dummy varchar2(1);
73
74 --The cursor c_dlv_type_in_use will return 'X'
75 --if the deliverable type id is present in the
76 --PA_PROJ_ELEMENTS table .If it is present then
77 --it means that it is in use.
78
79 CURSOR c_dlv_type_in_use IS
80 SELECT 'X'
81 FROM DUAL
82 WHERE EXISTS (SELECT 'X'
83 FROM PA_PROJ_ELEMENTS
84 WHERE TYPE_ID = p_deliverable_type_id
85 AND OBJECT_TYPE='PA_DELIVERABLES');
86
87 BEGIN
88 OPEN c_dlv_type_in_use;
89 FETCH c_dlv_type_in_use into l_dummy ;
90 IF c_dlv_type_in_use%found THEN
91 l_return_status:='Y';
92 ELSE
93 l_return_status:='N';
94 END IF;
95 CLOSE c_dlv_type_in_use;
96
97 return l_return_status;
98
99 END IS_DLV_TYPE_IN_USE;
100
101 -- SubProgram : IS_DLV_TYPE_ACTIONS_EXISTS
102 -- Type : UTIL FUNCTION
103 -- Purpose : This function will check whether actions exist for the Deliverable Type
104 -- Note : If there are any actions by defaultly defined for a deliverable type,
105 -- then that relationship will be present in the PA_OBJECT_RELATIONSHIPS table
106 -- The relationship is defined by the relationship_type 'A'
107 -- and the subtype is 'DLVR_TYPE_TO_ACTION'
108 -- Assumptions : None
109 -- Parameter IN/OUT Type Required Description and Purpose
110 -- --------------------------- ------------ ----------- --------- ---------------------------
111
112 -- p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE N Deliverable Type Id
113
114 FUNCTION IS_DLV_TYPE_ACTIONS_EXISTS
115 (
116 p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE
117 ) RETURN VARCHAR2
118 IS
119
120 l_return_status varchar2(1);
121 l_dummy varchar2(1);
122
123 --The cursor c_dlv_type_action_exists returns 'X' at the first hit
124 --If there exists a relationship 'DLVR_TYPE_TO_ACTION'
125 --FROM the passed p_deliverable_type_id of object_type 'PA_DLVR_TYPES'
126 --TO any of the deliverable actions of object_type 'PA_ACTIONS' in the PA_OBJECT_RELATIONSHIPS table
127 --Note : The relationship is defined by the relationship_type 'A'
128
129 CURSOR c_dlv_type_action_exists IS
130 SELECT 'X'
131 FROM DUAL
132 WHERE EXISTS (SELECT 'X'
133 FROM PA_OBJECT_RELATIONSHIPS obj
134 WHERE obj.object_id_from2 = p_deliverable_type_id
135 AND obj.object_type_from = 'PA_DLVR_TYPES'
136 AND obj.relationship_subtype = 'DLVR_TYPE_TO_ACTION'
137 AND obj.relationship_type = 'A'
138 AND obj.object_type_to = 'PA_ACTIONS');
139
140 BEGIN
141 OPEN c_dlv_type_action_exists;
142 FETCH c_dlv_type_action_exists into l_dummy ;
143 IF c_dlv_type_action_exists%found THEN
144 l_return_status:='Y';
145 ELSE
146 l_return_status:='N';
147 END IF;
148 CLOSE c_dlv_type_action_exists;
149
150 return l_return_status;
151
152 END IS_DLV_TYPE_ACTIONS_EXISTS;
153
154 -- SubProgram : IS_DLV_ACTIONS_EXISTS
155 -- Type : UTIL FUNCTION
156 -- Purpose : This function will check whether
157 -- there exists a deliverable of type p_deliverable_type_id
158 -- which is associated with actions
159 -- Note : If there are any actions are defined for a deliverable,
160 -- then that relationship will be present in the PA_OBJECT_RELATIONSHIPS table
161 -- The relationship is defined by the relationship_type 'A'
162 -- and the subtype is 'DELIVERABLE_TO_ACTION'
163 -- Assumptions : None
164 -- Parameter IN/OUT Type Required Description and Purpose
165 -- --------------------------- ------------ ---------- --------- ---------------------------
166
167 -- p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE N Deliverable Type Id
168
169 FUNCTION IS_DLV_ACTIONS_EXISTS
170 (
171 p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE
172 ) RETURN VARCHAR2
173 IS
174
175 l_return_status varchar2(1);
176 l_dummy varchar2(1);
177
178 --The cursor c_dlv_action_exists returns 'X' at the first hit
179 --If there exists a deliverable of object_type "PA_DELIVERABLES'
180 --of type p_deliverable_type_id
181 --and the deliverable has a relationship 'DELIVERABLE_TO_ACTION'
182 --WITH any of the Deliverable Actions of object_type 'PA_ACTIONS'
183
184 --Note : The relationship is defined by the relationship_type 'A'
185
186 CURSOR c_dlv_action_exists IS
187 SELECT 'X'
188 FROM DUAL
189 WHERE EXISTS (SELECT 'X'
190 FROM PA_OBJECT_RELATIONSHIPS obj,
191 PA_PROJ_ELEMENTS ppe
192 where ppe.type_id = p_deliverable_type_id
193 and ppe.object_type='PA_DELIVERABLES'
194 and ppe.proj_element_id = obj.object_id_from2
195 and obj.object_type_from = 'PA_DELIVERABLES'
196 and obj.object_type_to = 'PA_ACTIONS'
197 and obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
198 and obj.relationship_type = 'A');
199
200
201 BEGIN
202 OPEN c_dlv_action_exists;
203 FETCH c_dlv_action_exists into l_dummy ;
204 IF c_dlv_action_exists%found THEN
205 l_return_status:='Y';
206 ELSE
207 l_return_status:='N';
208 END IF;
209 CLOSE c_dlv_action_exists;
210
211 return l_return_status;
212
213 END IS_DLV_ACTIONS_EXISTS;
214
215 -- SubProgram : IS_DLV_BASED_ASSCN_EXISTS
216 -- Type : UTIL FUNCTION
217 -- Purpose : This function will check whether
218 -- there exists a deliverable of type p_deliverable_type_id
219 -- has been associated with a Deliverable-based task
220 -- Note : 1) If a deliverable has been associated with a task
221 -- then an entry corresponding to its association with the task can be found
222 -- in the PA_OBJECT_RELATIONSHIPS table with the relationship_type defined by 'A'
223 -- and relationship_sub_type 'TASK_TO_DELIVERABLE'
224 -- 2)If a task is deliverable then
225 -- the progress rollup method of the task is "Deliverable-based" .This can be found
226 -- from the PA_PROJ_ELEMENTS table.
227 -- Assumptions : None
228 -- Parameter IN/OUT Type Required Description and Purpose
229 -- --------------------------- ------------ ---------- --------- ---------------------------
230
231 -- p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE N Deliverable Type Id
232
233
234 FUNCTION IS_DLV_BASED_ASSCN_EXISTS
235 (
236 p_deliverable_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE
237 ) RETURN VARCHAR2
238 IS
239 l_return_status varchar2(1);
240 l_dummy varchar2(1);
241
242 --The cursor c_dlv_based_task_exists returns 'X' at the first hit
243 --If there exists a deliverable of object_type 'PA_DELIVERABLES'
244 --of type p_deliverable_id
245 --and which has a relationship 'TASK_TO_DELIVERABLE' with a
246 --'DELIVERABLE' task of object_type 'PA_TASKS'
247
248 --Note : The relationship is defined by the relationship_type 'A'
249
250 CURSOR c_dlv_based_task_exists IS
251 SELECT 'X'
252 FROM DUAL
253 WHERE EXISTS (SELECT 'X'
254 FROM PA_OBJECT_RELATIONSHIPS obj,
255 PA_PROJ_ELEMENTS ppe1,
256 PA_PROJ_ELEMENTS ppe2
257 where ppe1.type_id = p_deliverable_type_id
258 and ppe1.object_type= 'PA_DELIVERABLES'
259 and ppe1.proj_element_id = obj.object_id_to2
260 and ppe2. proj_element_id = obj.object_id_from2
261 and ppe2.object_type = 'PA_TASKS'
262 and ppe2.project_id = ppe1.project_id
263 and ppe2.base_percent_comp_deriv_code = 'DELIVERABLE'
264 and obj.object_type_from = 'PA_TASKS'
265 and obj.object_type_to = 'PA_DELIVERABLES' -- 3570283 removed extra spaces
266 and obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
267 and obj.relationship_type = 'A');
268
269
270 BEGIN
271 OPEN c_dlv_based_task_exists;
272 FETCH c_dlv_based_task_exists into l_dummy ;
273 IF c_dlv_based_task_exists%found THEN
274 l_return_status:='Y';
275 ELSE
276 l_return_status:='N';
277 END IF;
278 CLOSE c_dlv_based_task_exists;
279
280 return l_return_status;
281
282 END IS_DLV_BASED_ASSCN_EXISTS;
283
284 -- SubProgram : IS_EFF_FROM_TO_DATE_VALID
285 -- Type : UTIL FUNCTION
286 -- Purpose : This function will check whether
287 -- the entered start date and end date are valid
288 -- If and Only if,the startdate is greater than the end date then it is Invalid
289 -- Note : None
290 -- Assumption : If the startdate and enddate are the same,then it is valid
291 -- Parameter IN/OUT Type Required Description and Purpose
292 -- --------------------------- ------------ ---------- --------- ---------------------------
293
294 -- p_start_date_Active IN DATE N Effetive Start date
295 -- p_end_date_Active IN DATE N Effective End date
296
297
298 FUNCTION IS_EFF_FROM_TO_DATE_VALID
299 (
300 p_start_date_active IN DATE,
301 p_end_date_active IN DATE
302 ) RETURN VARCHAR2
303 IS
304
305 l_return_status varchar2(1);
306
307 BEGIN
308 IF p_end_date_active IS NOT NULL AND TRUNC(p_start_date_active) > TRUNC(p_end_date_active) THEN
309 l_return_status:='N';
310 ELSE
311 l_return_status:='Y';
312 END IF;
313 return l_return_status;
314 END IS_EFF_FROM_TO_DATE_VALID;
315
316 -- SubProgram : GET_ASSOCIATED_TASKS
317 -- Type : UTIL FUNCTION
318 -- Purpose : This function will get the list of tasks associated to the deliverable
319 -- in the format task1,task2,task3,More..
320 -- Note : None
321 -- Assumption : None
322 -- Parameter IN/OUT Type Required Description and Purpose
323 -- --------------------------- ------------ ---------- --------- ---------------------------
324 -- p_deliverable_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE N Deliverable Type Id
325
326 FUNCTION GET_ASSOCIATED_TASKS
327 (
328 p_deliverable_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
329 ) RETURN VARCHAR2
330 IS
331
332 TYPE l_tbl IS TABLE OF VARCHAR2(350) INDEX BY BINARY_INTEGER;
333 l_name_number_tbl l_tbl ;
334 l_count NUMBER ;
335 l_string varchar2(500);
336 l_meaning varchar2(80);
337
338 CURSOR c_associated_tasks IS
339 SELECT ppe. name||'('|| ppe.element_number||')' name_number
340 FROM PA_PROJ_ELEMENTS ppe ,
341 PA_OBJECT_RELATIONSHIPS obj
342 WHERE ppe.object_type='PA_TASKS'
343 AND ppe.proj_element_id = OBJ.object_id_from2
344 AND OBJ.object_id_to2 =p_deliverable_id
345 AND OBJ.object_type_to = 'PA_DELIVERABLES'
346 AND OBJ.object_type_from = 'PA_TASKS'
347 AND OBJ.relationship_type = 'A'
348 AND OBJ.relationship_subtype = 'TASK_TO_DELIVERABLE'
349 ORDER BY ppe.base_percent_comp_deriv_code;
350
351 CURSOR c_lookup_meaning IS
352 SELECT meaning
353 FROM pa_lookups
354 WHERE lookup_type = 'PA_DLV_MORE'
355 AND lookup_code = 'MORE';
356 BEGIN
357 OPEN c_associated_tasks;
358 OPEN c_lookup_meaning;
359 FETCH c_associated_tasks BULK COLLECT INTO l_name_number_tbl;
360 CLOSE c_associated_tasks;
361 IF nvl(l_name_number_tbl.LAST,0)>0
362 THEN
363 FETCH c_lookup_meaning INTO l_meaning;
364 CLOSE c_lookup_meaning;
365 FOR l_count in l_name_number_tbl.FIRST..l_name_number_tbl.LAST LOOP
366 IF l_count = 1
367 THEN
368 l_string :=l_name_number_tbl(l_count);
369 ELSE
370 l_string := l_string||','||l_name_number_tbl(l_count);
371 END IF;
372 EXIT WHEN l_count >= 3 ;
373 END LOOP ;
374 IF nvl(l_name_number_tbl.LAST,0)>3
375 THEN
376 l_string := l_string||','|| l_meaning||'..';
377 END IF ;
378 RETURN l_string;
379 ELSE
380 RETURN NULL;
381 END IF ;
382 END GET_ASSOCIATED_TASKS;
383
384 -- SubProgram : GET_OKE_FLAGS
385 -- Type : UTIL PROCEDURE
386 -- Purpose : This procedure will return all the required flags for OKE
387 -- validation . This API will call util APIs provided by OKE
388 -- team.
389 -- Note : None
390 -- Assumption : None
391 -- Parameter IN/OUT Type Required Description and Purpose
392 -- --------------------------- ------------ ---------- --------- ---------------------------
393 -- p_project_id IN NUMBER Y Project Id
394 -- p_dlvr_item_id IN NUMBER Y element id of the deliverable
395 -- p_dlvr_version_id IN NUMBER Y version id of the deliverable
396 -- p_action_item_id IN NUMBER Y element id of the action
397 -- p_action_version_id IN VARCHAR2 Y version id of the action
398 -- p_calling_module IN VARCHAR2 Y Calling module
399 -- x_ready_to_ship OUT VARCHAR2 Y Ready to ship flag
400 -- x_ready_to_procure OUT VARCHAR2 Y Ready to procure flag
401 -- x_planning_initiated OUT VARCHAR2 Y Planning initiated flag
402 -- x_proc_initiated OUT VARCHAR2 Y Procurement initiated flag
403 -- x_shipping_initiated OUT VARCHAR2 Y Shipping initiated flag
404 -- x_item_exists OUT VARCHAR2 Y Item Exists Flag
405 -- x_item_shippable OUT VARCHAR2 Y Item Shippable Flag
406 -- x_item_billable OUT VARCHAR2 Y Item billable Flag
407 -- x_item_purchasable OUT VARCHAR2 Y Item purchasable Flag
408 -- x_ship_procure_flag_dlv OUT VARCHAR2 Y Shipping/Procurement Flag for deliverable
409 -- x_return_status OUT VARCHAR2 Y
410 -- x_msg_count OUT VARCHAR2 Y
411 -- x_msg_data OUT
412
413 PROCEDURE GET_OKE_FLAGS
414 ( p_project_id IN pa_projects_all.project_id%TYPE
415 ,p_dlvr_item_id IN pa_proj_elements.proj_element_id%TYPE
416 ,p_dlvr_version_id IN pa_proj_element_versions.element_version_id%TYPE
417 ,p_action_item_id IN pa_proj_elements.proj_element_id%TYPE
418 ,p_action_version_id IN pa_proj_element_versions.element_version_id%TYPE
419 ,p_calling_module IN VARCHAR2
420 ,x_ready_to_ship OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
421 ,x_ready_to_procure OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
422 ,x_planning_initiated OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
423 ,x_proc_initiated OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
424 ,x_shipping_initiated OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
425 ,x_item_exists OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
426 ,x_item_shippable OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
427 ,x_item_billable OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
428 ,x_item_purchasable OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
429 ,x_ship_procure_flag_dlv OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
430 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
431 ,x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
432 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
433 )
434 IS
435
436 CURSOR ship_procure_flag_dlv IS
437 SELECT 'Y'
438 FROM dual
439 WHERE EXISTS ( SELECT 'Y'
440 FROM pa_object_relationships obj
441 ,pa_proj_element_versions ver
442 WHERE obj.object_id_from2 = p_dlvr_item_id
443 AND obj.object_type_to = 'PA_ACTIONS'
444 AND obj.object_type_from = 'PA_DELIVERABLES'
445 AND obj.object_id_to2 = ver.proj_element_id
446 AND obj.RELATIONSHIP_TYPE = 'A'
447 AND obj.RELATIONSHIP_SUBTYPE = 'DELIVERABLE_TO_ACTION'
448 AND (nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Ship_Yn(ver.element_version_id),'N') = 'Y'
449 OR nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Procure_Yn(ver.element_version_id),'N') = 'Y' )
450 ) ;
451
452 l_debug_mode VARCHAR2(10);
453 l_msg_count NUMBER ;
454 l_data VARCHAR2(2000);
455 l_msg_data VARCHAR2(2000);
456 l_msg_index_out NUMBER;
457
458 BEGIN
459
460 x_msg_count := 0;
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
463
464 IF l_debug_mode = 'Y' THEN
465 PA_DEBUG.set_curr_function( p_function => 'GET_OKE_FLAGS'
466 ,p_debug_mode => l_debug_mode );
467 pa_debug.g_err_stage:= 'Inside CREATE_DLV_ACTIONS_IN_BULK ';
468 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
469 END IF;
470
471 x_ready_to_ship := 'N' ;
472 x_ready_to_procure := 'N' ;
473 x_planning_initiated := 'N' ;
474 x_proc_initiated := 'N' ;
475 x_shipping_initiated := 'N' ;
476 x_item_exists := 'N' ;
477 x_item_shippable := 'N' ;
478 x_item_billable := 'N' ;
479 x_item_purchasable := 'N' ;
480 x_ship_procure_flag_dlv := 'N' ;
481
482 -- IF p_calling_module 'DELETE_DELIVERABLE' THEN
483 IF p_calling_module IN ( 'DELETE_DELIVERABLE','DELETE_ASSOCIATION','UPDATE_DUE_DATE' ) THEN
484 OPEN ship_procure_flag_dlv ;
485 FETCH ship_procure_flag_dlv INTO x_ship_procure_flag_dlv ;
486 CLOSE ship_procure_flag_dlv ;
487 ELSE
488 -- Initialize all the out parameters
489
490 x_ready_to_ship := nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Ship_Yn (P_Action_ID => p_action_version_id),'N') ;
491 x_ready_to_procure := nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Procure_Yn(P_Action_ID => p_action_version_id ),'N') ;
492 x_planning_initiated := nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(P_Action_ID => p_action_version_id ),'N') ;
493 x_proc_initiated := nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(P_Action_ID => p_action_version_id ),'N') ;
494 x_shipping_initiated := nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(P_Action_ID => p_action_version_id ),'N') ;
495 -- x_item_exists := nvl(OKE_DELIVERABLE_UTILS_PUB.Item_Defined_Yn(P_Action_ID => p_action_version_id ),'N') ;
496 x_item_exists := nvl(OKE_DELIVERABLE_UTILS_PUB.Item_Defined_Yn( p_dlvr_version_id ),'N') ;
497 x_item_shippable := nvl(OKE_DELIVERABLE_UTILS_PUB.Item_Shippable_Yn(P_Deliverable_ID=>p_dlvr_version_id),'N') ;
498 x_item_billable := nvl(OKE_DELIVERABLE_UTILS_PUB.Item_Billable_Yn(P_Deliverable_ID => p_dlvr_version_id ),'N') ;
499 x_item_purchasable := nvl(OKE_DELIVERABLE_UTILS_PUB.Item_Purchasable_Yn(P_Deliverable_ID => p_dlvr_version_id ),'N') ;
500
501 pa_debug.g_err_stage:= 'Inside CREATE_DLV_ACTIONS_IN_BULK ';
502 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
503 END IF ;
504
505 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
506 pa_debug.reset_curr_function;
507 END IF ;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 x_msg_count := 1;
513 x_msg_data := SQLERRM;
514
515 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLE_UTILS'
516 ,p_procedure_name => 'GET_OKE_FLAGS');
517
518 IF l_debug_mode = 'Y' THEN
519 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
520 pa_debug.write('GET_OKE_FLAGS:'|| g_module_name,pa_debug.g_err_stage,5);
521 pa_debug.reset_curr_function;
522 END IF;
523 RAISE;
524 END GET_OKE_FLAGS ;
525
526 -- SubProgram : IS_TASK_ASSGMNT_EXISTS
527 -- Type : UTIL FUNCTION
528 -- Purpose : This procedure will return 'Y' or 'N' based on whether
529 -- deliverable is associated with task Assignment or not .
530 -- Note : None
531 -- Assumption : None
532 --
533 -- Parameter IN/OUT Type Required Description and Purpose
534 -- --------------------------- ------------ ---------- --------- ---------------------------
535 -- p_project_id IN NUMBER Y Project Id
536 -- p_dlvr_item_id IN NUMBER Y element id of the deliverable
537 -- p_dlvr_version_id IN NUMBER Y version id of the deliverable
538
539 FUNCTION IS_TASK_ASSGMNT_EXISTS
540 ( p_project_id IN pa_projects_all.project_id%TYPE
541 ,p_dlvr_item_id IN pa_proj_elements.proj_element_id%TYPE
542 ,p_dlvr_version_id IN pa_proj_element_versions.element_version_id%TYPE
543 )
544 RETURN VARCHAR2
545 IS
546 l_task_assignment_exists VARCHAR2(1) := 'N' ;
547 CURSOR C IS
548 SELECT 'Y'
549 FROM dual
550 WHERE EXISTS (SELECT 'X'
551 from pa_object_relationships
552 where object_id_to2 = p_dlvr_item_id
553 and object_type_from = 'PA_ASSIGNMENTS'
554 and object_type_to = 'PA_DELIVERABELS'
555 and relationship_type = 'A'
556 and relationship_subtype = 'ASSIGNMENT_TO_DELIVERABLE') ;
557 BEGIN
558 OPEN C ;
559 FETCH C INTO l_task_assignment_exists ;
560 CLOSE C ;
561 RETURN l_task_assignment_exists ;
562 END IS_TASK_ASSGMNT_EXISTS ;
563
564
565 -- SubProgram : IS_DLV_STATUS_CHANGE_ALLOWED
566 -- Type : UTIL PROCEDURE
567 -- Purpose : This procedure will return 'Y' or 'N' based on whether
568 -- deliverable status change is allowed or not.
569 -- Note : None
570 -- Assumption : None
571 --
572 -- Parameter IN/OUT Type Required Description and Purpose
573 -- --------------------------- ------------ ---------- --------- ---------------------------
574 -- p_project_id IN NUMBER Y Project Id
575 -- p_dlvr_item_id IN NUMBER Y element id of the deliverable
576 -- p_dlv_type_id IN NUMBER Y Deliverable type id
577 -- p_dlvr_version_id IN NUMBER Y version id of the deliverable
578 -- x_return_status OUT VARCHAR2
579 -- x_msg_count OUT NUMBER
580 -- x_msg_data OUT VARCHAR2
581
582
583 PROCEDURE IS_DLV_STATUS_CHANGE_ALLOWED
584 ( p_project_id IN pa_projects_all.project_id%TYPE
585 ,p_dlvr_item_id IN pa_proj_elements.proj_element_id%TYPE
586 ,p_dlvr_version_id IN pa_proj_element_versions.element_version_id%TYPE
587 ,p_dlv_type_id IN pa_task_types.task_type_id%TYPE
588 ,p_dlvr_status_code IN PA_PROJ_ELEMENTS.STATUS_CODE%TYPE
589 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
590 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
591 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
592 )
593 IS
594
595 -- Cursor to check whether there existis any
596 -- action for the deliverable for which either
597 -- procurement is initiated or shipping has
598 -- been initiated or billing action exists
599
600 CURSOR c_ship_procure_flag_dlv IS
601 SELECT 'Y'
602 FROM dual
603 WHERE EXISTS ( SELECT 'Y'
604 FROM pa_object_relationships obj
605 ,pa_proj_element_versions ver
606 WHERE obj.object_id_from2 = p_dlvr_item_id
607 AND obj.object_type_to = 'PA_ACTIONS'
608 AND obj.object_type_from = 'PA_DELIVERABLES'
609 AND obj.object_id_to2 = ver.proj_element_id
610 AND obj.relationship_type = 'A'
611 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
612 AND (nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ver.element_version_id),'N') = 'Y'
613 OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ver.element_version_id),'N') = 'Y'
614 OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(object_id_to2) = 'BILLING')
615 ) ;
616
617 -- This cursor will fetch the system_status_code
618 -- for the given status_code .
619
620 CURSOR c_get_system_status IS
621 SELECT project_system_status_code
622 FROM pa_project_statuses
623 WHERE project_status_code = p_dlvr_status_code
624 AND status_type = 'DELIVERABLE' ;
625 -- Bug 3503296 In the following cursor ,we have to check
626 --"Whether Shipping Or Procurement has NOT been initiated for any of the deliverable's actions"
627 --If for atleast one hit that "Either Shipping Or Procurement has not been initiated for some action" then We cannot change
628 --the deliverable's status to completed .So,if this cursor returns 'Y' then status change to COMPLETED should not be allowed
629
630 CURSOR c_complete_dlv_check IS
631 SELECT 'Y'
632 FROM dual
633 WHERE EXISTS ( SELECT 'Y'
634 FROM pa_object_relationships obj
635 ,pa_proj_element_versions ver
636 WHERE
637 obj.object_id_from2 = p_dlvr_item_id
638 AND obj.object_type_to = 'PA_ACTIONS'
639 AND obj.object_type_from = 'PA_DELIVERABLES'
640 AND obj.object_id_to2 = ver.proj_element_id
641 AND
642 (
643 (
644 PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(object_id_to2) = 'SHIPPING'
645 -- Commented for Bug 3503296 AND nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ver.element_version_id),'N') = 'Y'
646 AND nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ver.element_version_id),'N') = 'N' --Included for Bug 3503296
647 )
648 OR
649 (
650 PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(object_id_to2) = 'PROCUREMENT'
651 -- Commented for Bug 3503296 AND nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ver.element_version_id),'N') = 'Y'
652 AND nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ver.element_version_id),'N') = 'N' --Included for Bug 3503296
653 )
654 )
655 );
656
657
658 -- Bug 3512346 CHECK_DELV_EVENT_PROCESSED API was uncommented by avaithia on 01-Apr-2004 (Also,Included Project Id,ElementVerId as params)
659 CURSOR c_complete_dlv_bill_check IS
660 SELECT 'Y'
661 FROM dual
662 WHERE EXISTS ( SELECT 'Y'
663 FROM pa_object_relationships obj
664 ,pa_proj_element_versions ver
665 WHERE
666 obj.object_id_from2 = p_dlvr_item_id
667 AND obj.object_type_to = 'PA_ACTIONS'
668 AND obj.object_type_from = 'PA_DELIVERABLES'
669 AND obj.object_id_to2 = ver.proj_element_id
670 AND
671 (
672 PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(object_id_to2) = 'BILLING'
673 AND nvl(PA_BILLING_WRKBNCH_EVENTS.CHECK_DELV_EVENT_PROCESSED(ver.project_id,p_dlvr_version_id,ver.element_version_id) ,'N') = 'N'
674 )
675 );
676
677 l_system_status_code pa_lookups.lookup_code%TYPE ;
678 l_status_change_allowed VARCHAR2(1) ;
679 BEGIN
680
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682
683 OPEN c_get_system_status ;
684 FETCH c_get_system_status INTO l_system_status_code ;
685 IF c_get_system_status%NOTFOUND THEN
686 x_return_status := FND_API.G_RET_STS_SUCCESS;
687 END IF ;
688 CLOSE c_get_system_status ;
689
690 -- Status mapped to system defined DLVR_ON_HOLD/DLVR_CANCELLED
691 -- is not allowed if :
692 -- 1. Billing function extsts
693 -- 2. Shipping has been initiated for any of the action
694 -- 3. Procurement has been initiated for any of the action
695
696 IF l_system_status_code IN ('DLVR_ON_HOLD','DLVR_CANCELLED') THEN
697
698 OPEN c_ship_procure_flag_dlv ;
699 FETCH c_ship_procure_flag_dlv INTO l_status_change_allowed ;
700
701 IF c_ship_procure_flag_dlv%NOTFOUND THEN
702 l_status_change_allowed := 'Y' ;
703 ELSE
704 l_status_change_allowed := 'N' ;
705 -- 4229934 Added code to populate error message if dlvr status change to cancel is
706 -- not allowed
707 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
708 p_msg_name => 'PA_DLV_STATUS_CHG_NOT_ALLOWED');
709 -- 4229934 end
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 END IF ;
712
713 CLOSE c_ship_procure_flag_dlv ;
714
715 ELSE
716 l_status_change_allowed := 'Y' ;
717 END IF ;
718
719 -- Status mapped to system defined DLVR_COMPLETED is not allowed if
720 -- 1. Shipping has not been initiated for shipping action.
721 -- 2. Procurement has not been initiated for procurement action .
722 -- 3. Event has not been processed by billing action .
723 -- 4. Document based deliverable has no deliverbale docs. defined.
724 -- 5. Item based document has no item defined .
725
726 IF l_system_status_code = 'DLVR_COMPLETED' THEN
727
728 IF PA_DELIVERABLE_UTILS.GET_DLV_TYPE_CLASS_CODE(p_dlv_type_id) = 'DOCUMENT' THEN
729
730 IF PA_DELIVERABLE_UTILS.IS_DLV_DOC_DEFINED(p_dlvr_item_id,p_dlvr_version_id) = 'N' THEN
731 l_status_change_allowed := 'N' ;
732 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
733 p_msg_name => 'PA_DLV_DOC_NOT_DEFINED');
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 ELSE
736 l_status_change_allowed := 'Y' ;
737 END IF ;
738
739 ELSIF PA_DELIVERABLE_UTILS.GET_DLV_TYPE_CLASS_CODE(p_dlv_type_id) = 'ITEM' THEN
740
741 IF OKE_DELIVERABLE_UTILS_PUB.Item_Defined_Yn(p_dlvr_version_id) = 'N' THEN
742 l_status_change_allowed := 'N' ;
743 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
744 p_msg_name => 'PA_DLV_ITEM_NOT_DEFINED');
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 ELSE
747 l_status_change_allowed := 'Y' ;
748 END IF ;
749
750 END IF ;
751
752 OPEN c_complete_dlv_check ;
753 FETCH c_complete_dlv_check INTO l_status_change_allowed ;
754
755 IF c_complete_dlv_check%NOTFOUND THEN
756 l_status_change_allowed := 'Y' ;
757 ELSE
758 l_status_change_allowed := 'N' ;
759 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
760 p_msg_name => 'PA_DLV_WSH_REQ_NOT_INITIATED');
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 END IF ;
763 CLOSE c_complete_dlv_check ;
764
765
766 OPEN c_complete_dlv_bill_check ;
767 FETCH c_complete_dlv_bill_check INTO l_status_change_allowed ;
768
769 IF c_complete_dlv_bill_check%NOTFOUND THEN
770 l_status_change_allowed := 'Y' ;
771 ELSE
772 l_status_change_allowed := 'N' ;
773 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
774 p_msg_name => 'PA_DLV_BILL_EVT_NOT_INITIATED');
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 END IF ;
777 CLOSE c_complete_dlv_bill_check ;
778
779 END IF ;
780 EXCEPTION
781 WHEN OTHERS THEN
782
783 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
784 x_msg_count := 1;
785 x_msg_data := SQLERRM;
786
787 IF c_ship_procure_flag_dlv%ISOPEN THEN
788 CLOSE c_ship_procure_flag_dlv;
789 END IF;
790
791 IF c_complete_dlv_check%ISOPEN THEN
792 CLOSE c_complete_dlv_check;
793 END IF;
794
795 IF c_complete_dlv_bill_check%ISOPEN THEN
796 CLOSE c_complete_dlv_bill_check;
797 END IF;
798
799 Fnd_Msg_Pub.add_exc_msg
800 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
801 , p_procedure_name => 'IS_DLV_STATUS_CHANGE_ALLOWED'
802 , p_error_text => x_msg_data);
803
804 END IS_DLV_STATUS_CHANGE_ALLOWED ;
805
806 -- SubProgram : GET_DLV_TYPE_CLASS_CODE
807 -- Type : UTIL FUNCTION
808 -- Purpose : This procedure will return Deliverable Type Class Code
809 -- Note : None
810 -- Assumption : None
811 --
812 -- Parameter IN/OUT Type Required Description and Purpose
813 -- --------------------------- ------------ ---------- --------- ---------------------------
814 -- p_dlv_type_id IN NUMBER Y Deliverable type id
815
816 FUNCTION GET_DLV_TYPE_CLASS_CODE
817 (
818 p_dlvr_type_id IN pa_task_types.task_type_id%TYPE
819 )
820 RETURN VARCHAR2
821 IS
822 CURSOR c_dlv_type_class IS
823 SELECT task_type_class_code
824 FROM pa_task_types
825 WHERE task_type_id = p_dlvr_type_id ;
826 l_task_type_class_code pa_task_types.task_type_class_code%TYPE ;
827 BEGIN
828 OPEN c_dlv_type_class ;
829 FETCH c_dlv_type_class INTO l_task_type_class_code ;
830 IF c_dlv_type_class%NOTFOUND THEN
831 l_task_type_class_code := null ;
832 END IF ;
833 CLOSE c_dlv_type_class ;
834 RETURN l_task_type_class_code ;
835 END GET_DLV_TYPE_CLASS_CODE ;
836
837 -- SubProgram : GET_FUNCTION_CODE
838 -- Type : UTIL FUNCTION
839 -- Purpose : This procedure will return function code of action
840 -- Note : None
841 -- Assumption : None
842 --
843 -- Parameter IN/OUT Type Required Description and Purpose
844 -- --------------------------- ------------ ---------- --------- ---------------------------
845 -- p_action_element_id IN NUMBER Y Element Id Of Action
846
847 FUNCTION GET_FUNCTION_CODE
848 (
849 p_action_element_id IN pa_proj_elements.proj_element_id%TYPE
850 )
851 RETURN VARCHAR2
852 IS
853 CURSOR c_function_code IS
854 SELECT function_code
855 FROM pa_proj_elements
856 WHERE proj_element_id = p_action_element_id ;
857 l_function_code pa_proj_elements.function_code%TYPE ;
858 BEGIN
859 OPEN c_function_code ;
860 FETCH c_function_code INTO l_function_code ;
861 IF c_function_code%NOTFOUND THEN
862 l_function_code := null ;
863 END IF ;
864 CLOSE c_function_code ;
865 RETURN l_function_code ;
866 END GET_FUNCTION_CODE ;
867
868 -- SubProgram : IS_DLV_DOC_DEFINED
869 -- Type : UTIL FUNCTION
870 -- Purpose : This procedure will return 'Y' or 'N' based on whether
871 -- Deliverable Documents is defined or not
872 -- Note : None
873 -- Assumption : None
874 --
875 -- Parameter IN/OUT Type Required Description and Purpose
876 -- --------------------------- ------------ ---------- --------- ---------------------------
877 -- p_action_element_id IN NUMBER Y Element Id Of Action
878 -- p_dlvr_item_id IN NUMBER Y Element Version Id of Action
879
880
881 FUNCTION IS_DLV_DOC_DEFINED
882 (
883 p_dlvr_item_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
884 ,p_dlvr_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
885 ) RETURN VARCHAR2
886 IS
887
888 l_doc_defined VARCHAR2(1) := 'N' ;
889
890 CURSOR l_doc_exists_csr
891 IS
892 SELECT
893 'Y'
894 FROM
895 DUAL
896 WHERE EXISTS
897 (
898 SELECT
899 'Y'
900 FROM
901 FND_ATTACHED_DOCUMENTS ATT
902 WHERE
903 ATT.ENTITY_NAME = 'PA_DLVR_DOC_ATTACH'
904 AND ATT.PK1_VALUE = p_dlvr_version_id
905 );
906
907 BEGIN
908
909 OPEN l_doc_exists_csr ;
910 FETCH l_doc_exists_csr INTO l_doc_defined ;
911 CLOSE l_doc_exists_csr ;
912
913 RETURN l_doc_defined ;
914
915 EXCEPTION
916
917 WHEN NO_DATA_FOUND THEN
918 l_doc_defined := null;
919 return l_doc_defined;
920
921 WHEN OTHERS THEN
922
923 IF l_doc_exists_csr%ISOPEN THEN
924 CLOSE l_doc_exists_csr;
925 END IF;
926
927 Fnd_Msg_Pub.add_exc_msg
928 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
929 , p_procedure_name => 'IS_DLV_DOC_DEFINED'
930 , p_error_text => SUBSTRB(SQLERRM,1,240));
931
932
933 RAISE;
934
935 END IS_DLV_DOC_DEFINED;
936
937
938 -- dthakker :: added the following procedures and functions
939
940 -- Procedure : GET_STRUCTURE_INFO
941 -- Type : UTILITY
942 -- Purpose : To retrieve Structure Information
943 -- Note : Fetch structure element_id and element_version_id from
944 -- : the cursor
945 -- Assumptions : Use this API to get structure info of only 'DELIVERABLE' structure type
946 -- For other structure types it mau not work
947 -- Parameters Type Required Description and Purpose
948 -- --------------------------- ------ -------- --------------------------------------------------------
949 -- p_api_version NUMBER N 1.0
950 -- p_calling_module VARCHAR2 N := 'SELF_SERVICE'
951 -- p_project_id NUMBER Y Project Id
952 -- p_structure_type VARCHAR2 Y Structure Type
953 -- x_proj_element_id NUMBER
954 -- x_element_version_id NUMBER
955 -- x_return_status VARCHAR2 N Return Status
956 -- x_msg_count NUMBER N Message Count
957 -- x_msg_data VARCHAR2 N Message Data
958
959
960 PROCEDURE GET_STRUCTURE_INFO
961 (
962 p_api_version IN NUMBER := 1.0
963 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
964 ,p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
965 ,p_structure_type IN VARCHAR2 := 'DELIVERABLE'
966 ,x_proj_element_id OUT NOCOPY PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE --File.Sql.39 bug 4440895
967 ,x_element_version_id OUT NOCOPY PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE --File.Sql.39 bug 4440895
968 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
969 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
970 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
971 )
972 IS
973 l_msg_count NUMBER := 0;
974 l_data VARCHAR2(2000);
975 l_msg_data VARCHAR2(2000);
976 l_msg_index_out NUMBER;
977 l_debug_mode VARCHAR2(1);
978
979 l_debug_level2 CONSTANT NUMBER := 2;
980 l_debug_level3 CONSTANT NUMBER := 3;
981 l_debug_level4 CONSTANT NUMBER := 4;
982 l_debug_level5 CONSTANT NUMBER := 5;
983
984 l_object_type VARCHAR2(150) := 'PA_STRUCTURES';
985 l_proj_element_id NUMBER;
986 l_element_version_id NUMBER;
987
988 CURSOR l_struct_info_csr
989 IS
990 SELECT ppe.proj_element_id
991 ,ppe.element_version_id
992 FROM pa_proj_elem_ver_structure ppe
993 ,pa_proj_structure_types pst
994 ,pa_structure_types sty
995 WHERE ppe.project_id = p_project_id
996 AND ppe.proj_element_id = pst.proj_element_id
997 AND pst.structure_type_id = sty.structure_type_id
998 AND sty.structure_type = p_structure_type
999 AND sty.structure_type_class_code = p_structure_type ;
1000
1001 /* Commented following existing code for Performance Fix : Bug 3614361
1002 Instead of deriving structure information in the way mentioned in commented code,a better
1003 approach will be to retrieve the Structure Information from pa_proj_elem_ver_structure table
1004 as above */
1005
1006 /*Select
1007 ppe.proj_element_id
1008 ,pev.element_version_id
1009 From
1010 pa_proj_elements ppe,
1011 pa_proj_element_versions pev,
1012 pa_proj_structure_types pst,
1013 pa_structure_types st
1014 Where
1015 ppe.project_id = p_project_id
1016 and pev.project_id = p_project_id
1017 and ppe.object_type = l_object_type
1018 and ppe.proj_element_id = pev.proj_element_id
1019 and pev.object_type = l_object_type
1020 and ppe.proj_element_id = pst.proj_element_id
1021 and pst.STRUCTURE_TYPE_ID = st.STRUCTURE_TYPE_ID
1022 and st.structure_type = p_structure_type
1023 and st.structure_type_class_code = p_structure_type;
1024 */
1025
1026 BEGIN
1027 x_msg_count := 0;
1028 x_return_status := FND_API.G_RET_STS_SUCCESS;
1029 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1030
1031 IF l_debug_mode = 'Y' THEN
1032 PA_DEBUG.set_curr_function( p_function => 'GET_STRICTURE_INFO',
1033 p_debug_mode => l_debug_mode );
1034 END IF;
1035
1036 IF l_debug_mode = 'Y' THEN
1037 Pa_Debug.g_err_stage:= 'GET_STRICTURE_INFO : Printing Input parameters';
1038 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1039 l_debug_level3);
1040 Pa_Debug.WRITE(g_module_name,' P_PROJECT_ID '||':'|| p_project_id,
1041 l_debug_level3);
1042 Pa_Debug.WRITE(g_module_name,' P_STRUCTURE_TYPE '||':'|| p_structure_type,
1043 l_debug_level3);
1044 END IF;
1045
1046 IF l_debug_mode = 'Y' THEN
1047 Pa_Debug.g_err_stage:= 'Fetch Structure Info From Cursor';
1048 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1049 l_debug_level3);
1050 END IF;
1051
1052 OPEN l_struct_info_csr;
1053 FETCH l_struct_info_csr INTO l_proj_element_id, l_element_version_id;
1054 CLOSE l_struct_info_csr;
1055
1056 x_proj_element_id := l_proj_element_id;
1057 x_element_version_id := l_element_version_id;
1058
1059 x_return_status := FND_API.G_RET_STS_SUCCESS;
1060
1061 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
1062 pa_debug.reset_curr_function;
1063 END IF ;
1064
1065 EXCEPTION
1066
1067 WHEN OTHERS THEN
1068
1069 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1070 x_msg_count := 1;
1071 x_msg_data := SQLERRM;
1072
1073 IF l_struct_info_csr%ISOPEN THEN
1074 CLOSE l_struct_info_csr;
1075 END IF;
1076
1077 Fnd_Msg_Pub.add_exc_msg
1078 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1079 , p_procedure_name => 'GET_STRUCTURE_INFO'
1080 , p_error_text => x_msg_data);
1081
1082 IF l_debug_mode = 'Y' THEN
1083 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
1084 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
1085 l_debug_level5);
1086 Pa_Debug.reset_curr_function;
1087 END IF;
1088
1089 END GET_STRUCTURE_INFO;
1090
1091
1092 -- Procedure : GET_CARRYING_OUT_ORG
1093 -- Type : UTILITY
1094 -- Purpose : To retrieve Carrying Out Organization Id
1095 -- Note : Retrieve Carrying Out Organization Id from pa_projects if task_id is null,
1096 -- : Retrieve Carrying Out Organization Id from pa_proj_elements if task_id is not null
1097 -- Assumptions : None
1098
1099 -- Parameters Type Required Description and Purpose
1100 -- --------------------------- ------ -------- --------------------------------------------------------
1101 -- p_project_id NUMBER Y Project Id
1102 -- p_task_id NUMBER Y Task Id
1103
1104 FUNCTION GET_CARRYING_OUT_ORG
1105 (
1106 p_project_id PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
1107 ,p_task_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1108 )
1109 RETURN NUMBER
1110 IS
1111 x_organization_id PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE;
1112 BEGIN
1113 -- if task_id is null then retrive carrying_out_organization_id from pa_projects
1114 IF (p_task_id IS NULL) THEN
1115 SELECT
1116 p.carrying_out_organization_id INTO x_organization_id
1117 FROM
1118 PA_PROJECTS_ALL p
1119 WHERE p.project_id = p_project_id;
1120 -- else retrive carrying_out_organization_id from pa_proj_elements
1121 ELSE
1122 SELECT
1123 ppe.carrying_out_organization_id INTO x_organization_id
1124 FROM
1125 PA_PROJ_ELEMENTS ppe
1126 WHERE
1127 ppe.proj_element_id = p_task_id
1128 AND ppe.object_type = 'PA_TASKS'
1129 AND ppe.project_id = p_project_id;
1130 END IF;
1131
1132 return x_organization_id;
1133 EXCEPTION
1134
1135 WHEN NO_DATA_FOUND THEN
1136 x_organization_id := null;
1137 return x_organization_id;
1138
1139 WHEN OTHERS THEN
1140
1141 Fnd_Msg_Pub.add_exc_msg
1142 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1143 , p_procedure_name => 'GET_CARRYING_OUT_ORG'
1144 , p_error_text => SUBSTRB(SQLERRM,1,240));
1145
1146 RAISE;
1147
1148 END GET_CARRYING_OUT_ORG;
1149
1150 -- Procedure : GET_PROGRESS_ROLLUP_METHOD
1151 -- Type : UTILITY
1152 -- Purpose : To retrieve Progress Rollup Method
1153 -- Note : Retrieve Progress Rollup Method from pa_proj_elements for task id
1154 -- :
1155 -- Assumptions : None
1156
1157 -- Parameters Type Required Description and Purpose
1158 -- --------------------------- ------ -------- --------------------------------------------------------
1159 -- p_task_id NUMBER Y Task Id
1160
1161
1162 FUNCTION GET_PROGRESS_ROLLUP_METHOD
1163 (
1164 p_task_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1165 )
1166 RETURN VARCHAR2
1167 IS
1168 x_base_percent_comp_deriv_code VARCHAR2(30);
1169 BEGIN
1170 SELECT ppe.base_percent_comp_deriv_code
1171 INTO x_base_percent_comp_deriv_code
1172 FROM PA_PROJ_ELEMENTS ppe
1173 WHERE ppe.proj_element_id = p_task_id
1174 AND ppe.object_type = 'PA_TASKS';
1175
1176 -- 3625019 when task type is changed from update task detail page
1177 -- base_percent_comp_deriv_code attribute is set to null and task type id
1178 -- is set to null value
1179
1180 -- to handle above case, if base_percent_comp_deriv_code is null , retrieve
1181 -- progress rollup method from task type
1182
1183 IF x_base_percent_comp_deriv_code IS NULL THEN
1184 select ptt.base_percent_comp_deriv_code
1185 INTO x_base_percent_comp_deriv_code
1186 from pa_task_types ptt,
1187 pa_proj_elements ppe
1188 where ppe.proj_element_id = p_task_id
1189 and ptt.task_type_id = ppe.type_id ;
1190 END IF;
1191
1192 return x_base_percent_comp_deriv_code;
1193 EXCEPTION
1194
1195 WHEN NO_DATA_FOUND THEN
1196
1197 x_base_percent_comp_deriv_code := null;
1198 return x_base_percent_comp_deriv_code;
1199
1200 WHEN OTHERS THEN
1201
1202 Fnd_Msg_Pub.add_exc_msg
1203 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1204 , p_procedure_name => 'GET_PROGRESS_ROLLUP_METHOD'
1205 , p_error_text => SUBSTRB(SQLERRM,1,240));
1206
1207 RAISE;
1208
1209 END GET_PROGRESS_ROLLUP_METHOD;
1210
1211
1212 -- Procedure : IS_ACTIONS_EXISTS
1213 -- Type : UTILITY
1214 -- Purpose : To check Deliverable Actions exists for Deliverable
1215 -- Note : Used in Update_Deliverable API
1216 -- :
1217 -- Assumptions : None
1218
1219 -- Parameters Type Required Description and Purpose
1220 -- --------------------------- ------ -------- --------------------------------------------------------
1221 -- p_proj_element_id NUMBER Y Deliverable Id ( Proj Element Id )
1222 -- p_project_id NUMBER Y Project Id
1223
1224 FUNCTION IS_ACTIONS_EXISTS
1225 (
1226 p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
1227 ,p_proj_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1228 )
1229 RETURN VARCHAR2
1230 IS
1231 x_actions_exists VARCHAR2(1) := 'N';
1232 l_relationship_type VARCHAR2(30) := 'DELIVERABLE_TO_ACTION';
1233 BEGIN
1234
1235 SELECT
1236 'Y' into x_actions_exists
1237 FROM
1238 DUAL
1239 WHERE
1240 EXISTS
1241 (
1242 SELECT
1243 OBJECT_RELATIONSHIP_ID
1244 FROM
1245 PA_OBJECT_RELATIONSHIPS
1246 WHERE
1247 OBJECT_ID_FROM2 = p_proj_element_id
1248 AND RELATIONSHIP_SUBTYPE = l_relationship_type
1249 AND RELATIONSHIP_TYPE = 'A'
1250 );
1251
1252 return nvl(x_actions_exists,'N');
1253 EXCEPTION
1254
1255 WHEN NO_DATA_FOUND THEN
1256
1257 x_actions_exists := 'N';
1258 return x_actions_exists;
1259
1260 WHEN OTHERS THEN
1261
1262 Fnd_Msg_Pub.add_exc_msg
1263 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1264 , p_procedure_name => 'IS_ACTIONS_EXISTS'
1265 , p_error_text => SUBSTRB(SQLERRM,1,240));
1266
1267 RAISE;
1268
1269 END IS_ACTIONS_EXISTS;
1270
1271
1272 -- Procedure : IS_BILLING_FUNCTION_EXISTS
1273 -- Type : UTILITY
1274 -- Purpose : To check BILLING function exists for Deliverable Actions
1275 -- Note : Used in Update_Deliverable API
1276 -- :
1277 -- Assumptions : None
1278
1279 -- Parameters Type Required Description and Purpose
1280 -- --------------------------- ------ -------- --------------------------------------------------------
1281 -- p_proj_element_id NUMBER Y Deliverable Id ( Proj Element Id )
1282 -- p_project_id NUMBER Y Project Id
1283
1284 FUNCTION IS_BILLING_FUNCTION_EXISTS
1285 (
1286 p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
1287 ,p_proj_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1288 )
1289 RETURN VARCHAR2
1290 IS
1291 x_biling_function_exists VARCHAR2(1) := 'N';
1292 l_function_code VARCHAR2(30) := 'BILLING';
1293 BEGIN
1294
1295 SELECT
1296 'Y' into x_biling_function_exists
1297 FROM
1298 DUAL
1299 WHERE
1300 EXISTS
1301 (
1302 SELECT
1303 PPE.PROJ_ELEMENT_ID
1304 FROM
1305 PA_OBJECT_RELATIONSHIPS POR
1306 ,PA_PROJ_ELEMENTS PPE
1307 WHERE
1308 POR.OBJECT_ID_FROM2 = p_proj_element_id
1309 AND PPE.PROJ_ELEMENT_ID = POR.OBJECT_ID_TO2
1310 AND POR.OBJECT_TYPE_FROM = 'PA_DELIVERABLES'
1311 AND POR.OBJECT_TYPE_TO = 'PA_ACTIONS'
1312 AND PPE.FUNCTION_CODE = l_function_code
1313 );
1314
1315 return nvl(x_biling_function_exists,'N');
1316 EXCEPTION
1317
1318 WHEN NO_DATA_FOUND THEN
1319
1320 x_biling_function_exists := 'N';
1321 return x_biling_function_exists;
1322
1323 WHEN OTHERS THEN
1324
1325 Fnd_Msg_Pub.add_exc_msg
1326 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1327 , p_procedure_name => 'IS_BILLING_FUNCTION_EXISTS'
1328 , p_error_text => SUBSTRB(SQLERRM,1,240));
1329
1330 RAISE;
1331
1332 END IS_BILLING_FUNCTION_EXISTS;
1333
1334 -- Procedure : IS_DELIVERABLE_HAS_PROGRESS
1335 -- Type : UTILITY
1336 -- Purpose : To check Progress Record Exists for Deliverable
1337 -- Note : Used in Update_Deliverable API
1338 -- :
1339 -- Assumptions : None
1340
1341 -- Parameters Type Required Description and Purpose
1342 -- --------------------------- ------ -------- --------------------------------------------------------
1343 -- p_proj_element_id NUMBER Y Deliverable Id ( Proj Element Id )
1344 -- p_project_id NUMBER Y Project Id
1345
1346 FUNCTION IS_DELIVERABLE_HAS_PROGRESS
1347 (
1348 p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
1349 ,p_proj_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1350 )
1351 RETURN VARCHAR2
1352 IS
1353 x_deliverable_has_progress VARCHAR2(1) := 'N';
1354 BEGIN
1355
1356 -- 3279978 added function call to check progress records for deliverable
1357
1358 x_deliverable_has_progress := PA_PROGRESS_UTILS.check_object_has_prog(
1359 p_project_id => p_project_id
1360 ,p_object_id => p_proj_element_id
1361 ,p_object_type => 'PA_DELIVERABLES'
1362 );
1363
1364 return nvl(x_deliverable_has_progress,'N');
1365 EXCEPTION
1366
1367 WHEN NO_DATA_FOUND THEN
1368
1369 x_deliverable_has_progress := 'N';
1370 return x_deliverable_has_progress;
1371
1372 WHEN OTHERS THEN
1373 Fnd_Msg_Pub.add_exc_msg
1374 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1375 , p_procedure_name => 'IS_DELIVERABLE_HAS_PROGRESS'
1376 , p_error_text => SUBSTRB(SQLERRM,1,240));
1377
1378 RAISE;
1379 END IS_DELIVERABLE_HAS_PROGRESS;
1380
1381
1382 -- Procedure : GET_DLVR_TYPE_INFO
1383 -- Type : UTILITY
1384 -- Purpose : To retrieve Deliverable Type Info
1385 -- Note :
1386 -- :
1387 -- Assumptions : None
1388
1389 -- Parameters Type Required Description and Purpose
1390 -- --------------------------- ------ -------- --------------------------------------------------------
1391 -- p_dlvr_type_id NUMBER Y Task Id
1392 -- x_dlvr_prg_enabled VARCHAR2 dlvr prg flag
1393 -- x_dlvr_action_enabled VARCHAR2 enable_action_flag
1394 -- x_dlvr_default_status_code VARCHAR2 default_status_code
1395
1396
1397 PROCEDURE GET_DLVR_TYPE_INFO
1398 (
1399 p_dlvr_type_id IN PA_TASK_TYPES.TASK_TYPE_ID%TYPE
1400 ,x_dlvr_prg_enabled OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1401 ,x_dlvr_action_enabled OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1402 ,x_dlvr_default_status_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1403 )
1404 IS
1405 CURSOR l_dlvr_type_info_csr
1406 IS
1407 SELECT
1408 ptt.prog_entry_enable_flag
1409 ,ptt.enable_dlvr_actions_flag
1410 ,ptt.initial_status_code
1411 FROM
1412 PA_TASK_TYPES ptt
1413 WHERE
1414 ptt.task_type_id = p_dlvr_type_id
1415 AND ptt.object_type = 'PA_DLVR_TYPES';
1416
1417 BEGIN
1418
1419 OPEN l_dlvr_type_info_csr;
1420 FETCH l_dlvr_type_info_csr INTO x_dlvr_prg_enabled, x_dlvr_action_enabled, x_dlvr_default_status_code;
1421 CLOSE l_dlvr_type_info_csr;
1422
1423 EXCEPTION
1424
1425 WHEN OTHERS THEN
1426
1427 Fnd_Msg_Pub.add_exc_msg
1428 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1429 , p_procedure_name => 'IS_DLVR_PRG_ENABLED'
1430 , p_error_text => SUBSTRB(SQLERRM,1,240));
1431
1432 IF l_dlvr_type_info_csr%ISOPEN THEN
1433 CLOSE l_dlvr_type_info_csr;
1434 END IF;
1435
1436 RAISE;
1437
1438 END GET_DLVR_TYPE_INFO;
1439
1440 -- Procedure : GET_DLVR_DETAIL
1441 -- Type : UTILITY
1442 -- Purpose : To retrieve Deliverable name and number
1443 -- Note : Retrieve Name and Number from pa_proj_elements
1444 -- :
1445 -- Assumptions : None
1446
1447 -- Parameters Type Required Description and Purpose
1448 -- --------------------------- ------ -------- --------------------------------------------------------
1449 -- p_dlvr_version_id NUMBER Y Deliverable Version Id
1450 -- x_name VARCHAR2 Deliverable Name
1451 -- x_number VARCHAR2 Deliverable Number
1452
1453
1454 PROCEDURE GET_DLVR_DETAIL
1455 (
1456 p_dlvr_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1457 ,x_name OUT NOCOPY PA_PROJ_ELEMENTS.NAME%TYPE --File.Sql.39 bug 4440895
1458 ,x_number OUT NOCOPY PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE --File.Sql.39 bug 4440895
1459 )
1460 IS
1461 CURSOR l_dlvr_info_csr
1462 IS
1463 SELECT
1464 PPE.NAME,
1465 PPE.ELEMENT_NUMBER
1466 FROM
1467 PA_PROJ_ELEMENTS PPE,
1468 PA_PROJ_ELEMENT_VERSIONS PEV
1469 WHERE
1470 PEV.ELEMENT_VERSION_ID = p_dlvr_ver_id
1471 AND PPE.PROJECT_ID = PEV.PROJECT_ID
1472 AND PPE.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID
1473 AND PPE.OBJECT_TYPE = 'PA_DELIVERABLES'
1474 AND PEV.OBJECT_TYPE = 'PA_DELIVERABLES';
1475
1476 BEGIN
1477
1478 OPEN l_dlvr_info_csr;
1479 FETCH l_dlvr_info_csr INTO x_name, x_number;
1480 CLOSE l_dlvr_info_csr;
1481
1482 EXCEPTION
1483
1484 WHEN OTHERS THEN
1485
1486 Fnd_Msg_Pub.add_exc_msg
1487 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1488 , p_procedure_name => 'GET_DLVR_DETAIL'
1489 , p_error_text => SUBSTRB(SQLERRM,1,240));
1490
1491 IF l_dlvr_info_csr%ISOPEN THEN
1492 CLOSE l_dlvr_info_csr;
1493 END IF;
1494
1495 RAISE;
1496
1497 END GET_DLVR_DETAIL;
1498
1499
1500 -- Procedure : GET_ACTION_DETAIL
1501 -- Type : UTILITY
1502 -- Purpose : To retrieve Deliverable Action name and number
1503 -- Note : Retrieve Action Name and Number from pa_proj_elements
1504 -- :
1505 -- Assumptions : None
1506
1507 -- Parameters Type Required Description and Purpose
1508 -- --------------------------- ------ -------- --------------------------------------------------------
1509 -- p_dlvr_action_ver_id NUMBER Y Delivearble Action Version Id
1510 -- x_name VARCHAR2 Deliverable Action Name
1511 -- x_number VARCHAR2 Deliverable Action Number
1512
1513
1514 PROCEDURE GET_ACTION_DETAIL
1515 (
1516 p_dlvr_action_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1517 ,x_name OUT NOCOPY PA_PROJ_ELEMENTS.NAME%TYPE --File.Sql.39 bug 4440895
1518 ,x_number OUT NOCOPY PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE --File.Sql.39 bug 4440895
1519 )
1520 IS
1521
1522 CURSOR l_dlvr_action_info_csr
1523 IS
1524 SELECT
1525 PPE.NAME,
1526 PPE.ELEMENT_NUMBER
1527 FROM
1528 PA_PROJ_ELEMENTS PPE,
1529 PA_PROJ_ELEMENT_VERSIONS PEV
1530 WHERE
1531 PEV.ELEMENT_VERSION_ID = p_dlvr_action_ver_id
1532 AND PPE.PROJECT_ID = PEV.PROJECT_ID
1533 AND PPE.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID
1534 AND PPE.OBJECT_TYPE = 'PA_ACTIONS'
1535 AND PEV.OBJECT_TYPE = 'PA_ACTIONS';
1536
1537
1538 BEGIN
1539
1540 OPEN l_dlvr_action_info_csr;
1541 FETCH l_dlvr_action_info_csr INTO x_name, x_number;
1542 CLOSE l_dlvr_action_info_csr;
1543
1544 EXCEPTION
1545
1546 WHEN OTHERS THEN
1547
1548 Fnd_Msg_Pub.add_exc_msg
1549 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1550 , p_procedure_name => 'GET_ACTION_DETAIL'
1551 , p_error_text => SUBSTRB(SQLERRM,1,240));
1552
1553 IF l_dlvr_action_info_csr%ISOPEN THEN
1554 CLOSE l_dlvr_action_info_csr;
1555 END IF;
1556
1557 RAISE;
1558
1559 END GET_ACTION_DETAIL;
1560
1561
1562 -- Procedure : GET_PROJ_CURRENCY_CODE
1563 -- Type : UTILITY
1564 -- Purpose : To retrieve Project Currency Code
1565 -- Note : Retrieve projfunc_currency_code from pa_projects_all
1566 -- :
1567 -- Assumptions : None
1568
1569 -- Parameters Type Required Description and Purpose
1570 -- --------------------------- ------ -------- --------------------------------------------------------
1571 -- p_dlvr_ver_id NUMBER Y Deliverable Version Id
1572
1573
1574 FUNCTION GET_PROJ_CURRENCY_CODE
1575 (
1576 p_dlvr_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1577 )
1578 RETURN VARCHAR2
1579 IS
1580 x_proj_currency_code PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE := null;
1581
1582 CURSOR l_proj_currency_code_csr
1583 IS
1584 SELECT
1585 PPA.PROJFUNC_CURRENCY_CODE
1586 FROM
1587 PA_PROJECTS_ALL PPA,
1588 PA_PROJ_ELEMENT_VERSIONS PEV
1589 WHERE
1590 PEV.ELEMENT_VERSION_ID = p_dlvr_ver_id
1591 AND PEV.PROJECT_ID = PPA.PROJECT_ID
1592 AND PEV.OBJECT_TYPE = 'PA_DELIVERABLES';
1593
1594 BEGIN
1595
1596 OPEN l_proj_currency_code_csr;
1597 FETCH l_proj_currency_code_csr INTO x_proj_currency_code;
1598 CLOSE l_proj_currency_code_csr;
1599
1600 return x_proj_currency_code;
1601
1602 EXCEPTION
1603
1604 WHEN OTHERS THEN
1605
1606 Fnd_Msg_Pub.add_exc_msg
1607 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1608 , p_procedure_name => 'GET_PROJ_CURRENCY_CODE'
1609 , p_error_text => SUBSTRB(SQLERRM,1,240));
1610
1611 RAISE;
1612
1613 END GET_PROJ_CURRENCY_CODE;
1614
1615 -- Procedure : GET_DLVR_PROJECT_DETAIL
1616 -- Type : UTILITY
1617 -- Purpose : To retrieve Project Id and name
1618 -- Note : Retrieve Project Id and Name from pa_projects_all
1619 -- :
1620 -- Assumptions : None
1621
1622 -- Parameters Type Required Description and Purpose
1623 -- --------------------------- ------ -------- --------------------------------------------------------
1624 -- p_dlvr_ver_id NUMBER Y Delivearble Version Id
1625 -- x_project_id NUMBER Project Id
1626 -- x_project_name VARCHAR2 Project Name
1627
1628
1629 PROCEDURE GET_DLVR_PROJECT_DETAIL
1630 (
1631 p_dlvr_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1632 ,x_project_id OUT NOCOPY PA_PROJ_ELEMENTS.PROJECT_ID%TYPE --File.Sql.39 bug 4440895
1633 ,x_project_name OUT NOCOPY PA_PROJECTS_ALL.NAME%TYPE --File.Sql.39 bug 4440895
1634 )
1635 IS
1636
1637 CURSOR l_project_info_csr
1638 IS
1639 SELECT
1640 PPA.PROJECT_ID,
1641 PPA.NAME
1642 FROM
1643 PA_PROJECTS_ALL PPA,
1644 PA_PROJ_ELEMENT_VERSIONS PEV
1645 WHERE
1646 PEV.ELEMENT_VERSION_ID = p_dlvr_ver_id
1647 AND PEV.PROJECT_ID = PPA.PROJECT_ID
1648 AND PEV.OBJECT_TYPE = 'PA_DELIVERABLES';
1649
1650 BEGIN
1651
1652 OPEN l_project_info_csr;
1653 FETCH l_project_info_csr INTO x_project_id, x_project_name;
1654 CLOSE l_project_info_csr;
1655
1656 EXCEPTION
1657
1658 WHEN OTHERS THEN
1659
1660 Fnd_Msg_Pub.add_exc_msg
1661 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1662 , p_procedure_name => 'GET_DLVR_PROJECT_DETAIL'
1663 , p_error_text => SUBSTRB(SQLERRM,1,240));
1664
1665 IF l_project_info_csr%ISOPEN THEN
1666 CLOSE l_project_info_csr;
1667 END IF;
1668
1669 RAISE;
1670
1671 END GET_DLVR_PROJECT_DETAIL;
1672
1673
1674 -- Procedure : GET_ACTION_PROJECT_DETAIL
1675 -- Type : UTILITY
1676 -- Purpose : To retrieve Project Id and name
1677 -- Note : Retrieve Project Id and Name from pa_projects_all
1678 -- :
1679 -- Assumptions : None
1680
1681 -- Parameters Type Required Description and Purpose
1682 -- --------------------------- ------ -------- --------------------------------------------------------
1683 -- p_dlvr_action_ver_id NUMBER Y Delivearble Action Version Id
1684 -- x_project_id NUMBER Project Id
1685 -- x_project_name VARCHAR2 Project Name
1686
1687
1688 PROCEDURE GET_ACTION_PROJECT_DETAIL
1689 (
1690 p_dlvr_action_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1691 ,x_project_id OUT NOCOPY PA_PROJ_ELEMENTS.PROJECT_ID%TYPE --File.Sql.39 bug 4440895
1692 ,x_project_name OUT NOCOPY PA_PROJECTS_ALL.NAME%TYPE --File.Sql.39 bug 4440895
1693 )
1694 IS
1695
1696 CURSOR l_project_info_csr
1697 IS
1698 SELECT
1699 PPA.PROJECT_ID,
1700 PPA.NAME
1701 FROM
1702 PA_PROJECTS_ALL PPA,
1703 PA_PROJ_ELEMENT_VERSIONS PEV
1704 WHERE
1705 PEV.ELEMENT_VERSION_ID = p_dlvr_action_ver_id
1706 AND PEV.PROJECT_ID = PPA.PROJECT_ID
1707 AND PEV.OBJECT_TYPE = 'PA_ACTIONS';
1708
1709 BEGIN
1710
1711 OPEN l_project_info_csr;
1712 FETCH l_project_info_csr INTO x_project_id, x_project_name;
1713 CLOSE l_project_info_csr;
1714
1715 EXCEPTION
1716
1717 WHEN OTHERS THEN
1718
1719 Fnd_Msg_Pub.add_exc_msg
1720 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1721 , p_procedure_name => 'GET_ACTION_PROJECT_DETAIL'
1722 , p_error_text => SUBSTRB(SQLERRM,1,240));
1723
1724 IF l_project_info_csr%ISOPEN THEN
1725 CLOSE l_project_info_csr;
1726 END IF;
1727
1728 RAISE;
1729
1730 END GET_ACTION_PROJECT_DETAIL;
1731
1732
1733 -- Procedure : GET_ACTION_TASK_DETAIL
1734 -- Type : UTILITY
1735 -- Purpose : To retrieve Task number
1736 -- Note : Retrieve Task Number
1737 -- :
1738 -- Assumptions : None
1739
1740 -- Parameters Type Required Description and Purpose
1741 -- --------------------------- ------ -------- --------------------------------------------------------
1742 -- p_task_id NUMBER Task Versioin Id
1743
1744
1745 FUNCTION GET_ACTION_TASK_DETAIL
1746 (
1747 p_task_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1748 ) RETURN VARCHAR2
1749 IS
1750
1751 l_task_number VARCHAR2(340) ;
1752
1753 CURSOR l_task_info_csr
1754 IS
1755 SELECT
1756 PPE. NAME||'('|| PPE.ELEMENT_NUMBER||')' NAME_NUMBER
1757 FROM
1758 PA_PROJ_ELEMENTS PPE
1759 WHERE PPE.PROJ_ELEMENT_ID = p_task_id
1760 AND PPE.OBJECT_TYPE = 'PA_TASKS';
1761
1762 BEGIN
1763
1764 OPEN l_task_info_csr;
1765 FETCH l_task_info_csr INTO l_task_number;
1766 CLOSE l_task_info_csr;
1767
1768 return l_task_number;
1769 EXCEPTION
1770
1771 WHEN OTHERS THEN
1772
1773 IF l_task_info_csr%ISOPEN THEN
1774 CLOSE l_task_info_csr;
1775 END IF;
1776
1777 Fnd_Msg_Pub.add_exc_msg
1778 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1779 , p_procedure_name => 'GET_ACTION_TASK_DETAIL'
1780 , p_error_text => SUBSTRB(SQLERRM,1,240));
1781
1782 RAISE;
1783
1784 END GET_ACTION_TASK_DETAIL;
1785
1786
1787 -- Procedure : GET_DEFAULT_DLVR_OWNER
1788 -- Type : UTILITY
1789 -- Purpose : To retrieve Default Deliverable Owner Id And Name
1790 -- Note :
1791 -- :
1792 -- Assumptions : None
1793
1794 -- Parameters Type Required Description and Purpose
1795 -- --------------------------- ------ -------- --------------------------------------------------------
1796 -- p_project_id NUMBER Y Project Id
1797 -- p_task_ver_id NUMBER Y Task Version Id
1798 -- x_owner_id NUMBER Owner Id
1799 -- x_owner_name VARCHAR Owner Name
1800
1801 PROCEDURE GET_DEFAULT_DLVR_OWNER
1802 (
1803 p_project_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1804 ,p_task_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1805 ,x_owner_id OUT NOCOPY PER_ALL_PEOPLE_F.PERSON_ID%TYPE --File.Sql.39 bug 4440895
1806 ,x_owner_name OUT NOCOPY PER_ALL_PEOPLE_F.FULL_NAME%TYPE --File.Sql.39 bug 4440895
1807 )
1808 IS
1809
1810 CURSOR l_owner_info_csr
1811 IS
1812 SELECT
1813 PPF.PERSON_ID
1814 ,PPF.FULL_NAME
1815 FROM
1816 PA_PROJ_ELEMENTS PPE,
1817 PER_ALL_PEOPLE_F PPF,
1818 PA_PROJ_ELEMENT_VERSIONS PEV
1819 WHERE
1820 PEV.ELEMENT_VERSION_ID = p_task_ver_id
1821 AND PEV.OBJECT_TYPE = 'PA_TASKS'
1822 AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
1823 AND PPE.OBJECT_TYPE = 'PA_TASKS'
1824 AND PPE.MANAGER_PERSON_ID = PPF.PERSON_ID
1825 AND PPE.PROJECT_ID = p_project_id
1826 AND PEV.PROJECT_ID = p_project_id
1827 AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE;
1828
1829 BEGIN
1830
1831 IF p_task_ver_id IS NOT NULL THEN
1832 OPEN l_owner_info_csr;
1833 FETCH l_owner_info_csr INTO x_owner_id, x_owner_name;
1834 CLOSE l_owner_info_csr;
1835 ELSE
1836 x_owner_id := PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER
1837 (
1838 p_project_id => p_project_id
1839 );
1840 x_owner_name := PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME
1841 (
1842 p_project_id => p_project_id
1843 );
1844 END IF;
1845 EXCEPTION
1846
1847 WHEN OTHERS THEN
1848
1849 Fnd_Msg_Pub.add_exc_msg
1850 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1851 , p_procedure_name => 'GET_DEFAULT_DLVR_OWNER'
1852 , p_error_text => SUBSTRB(SQLERRM,1,240));
1853
1854 IF l_owner_info_csr%ISOPEN THEN
1855 CLOSE l_owner_info_csr;
1856 END IF;
1857
1858 RAISE;
1859
1860 END GET_DEFAULT_DLVR_OWNER;
1861
1862
1863 -- Procedure : GET_DEFAULT_DLVR_DATE
1864 -- Type : UTILITY
1865 -- Purpose : To retrieve Default Deliverable Due Date
1866 -- Note :
1867 -- :
1868 -- Assumptions : None
1869
1870 -- Parameters Type Required Description and Purpose
1871 -- --------------------------- ------ -------- --------------------------------------------------------
1872 -- p_project_id NUMBER Y Project Id
1873 -- p_task_ver_ NUMBER Y Task Version Id
1874 -- x_due_date DATE Dlvr Due Date
1875
1876 PROCEDURE GET_DEFAULT_DLVR_DATE
1877 (
1878 p_project_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
1879 ,p_task_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1880 ,x_due_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
1881 )
1882 IS
1883
1884 l_scheduled_finish_date DATE := NULL;
1885 l_target_finish_date DATE := NULL;
1886 l_completion_date DATE := NULL;
1887 l_sysdate DATE := NULL;
1888
1889 CURSOR l_proj_date_info_csr
1890 IS
1891 SELECT
1892 SCHEDULED_FINISH_DATE,
1893 TARGET_FINISH_DATE,
1894 COMPLETION_DATE,
1895 SYSDATE
1896 FROM
1897 PA_PROJECTS_ALL
1898 WHERE
1899 PROJECT_ID = p_project_id;
1900
1901
1902 CURSOR l_task_date_info_csr
1903 IS
1904 SELECT
1905 PES.SCHEDULED_FINISH_DATE,
1906 SYSDATE
1907 FROM
1908 PA_PROJ_ELEMENT_VERSIONS PEV
1909 ,PA_PROJ_ELEM_VER_SCHEDULE PES
1910 WHERE
1911 PEV.ELEMENT_VERSION_ID = p_task_ver_id
1912 AND PEV.ELEMENT_VERSION_ID = PES.ELEMENT_VERSION_ID ;
1913
1914 /* AND PEV.OBJECT_TYPE = 'PA_TASKS'
1915 AND PES.PROJECT_ID = p_project_id;Commented Unnecessary joins -
1916 This Query was flagged by xpl utility because of missing index PA_PROJ_ELEM_VER_SCHEDULE_U2 in ch2m 3614361 */
1917
1918 BEGIN
1919
1920 IF p_task_ver_id IS NOT NULL THEN
1921
1922 OPEN l_task_date_info_csr;
1923 FETCH l_task_date_info_csr INTO l_scheduled_finish_date ,l_sysdate;
1924 CLOSE l_task_date_info_csr;
1925
1926 IF l_scheduled_finish_date IS NOT NULL THEN
1927 x_due_date := l_scheduled_finish_date;
1928 ELSE
1929 x_due_date := l_sysdate;
1930 END IF;
1931
1932 ELSE
1933
1934 OPEN l_proj_date_info_csr;
1935 FETCH l_proj_date_info_csr INTO l_scheduled_finish_date, l_target_finish_date, l_completion_date, l_sysdate ;
1936 CLOSE l_proj_date_info_csr;
1937
1938
1939 IF l_scheduled_finish_date IS NOT NULL THEN
1940 x_due_date := l_scheduled_finish_date;
1941 ELSIF l_target_finish_date IS NOT NULL THEN
1942 x_due_date := l_target_finish_date;
1943 ELSIF l_completion_date IS NOT NULL THEN
1944 x_due_date := l_completion_date;
1945 ELSE
1946 x_due_date := l_sysdate;
1947 END IF;
1948
1949 END IF;
1950 EXCEPTION
1951
1952 WHEN OTHERS THEN
1953
1954 Fnd_Msg_Pub.add_exc_msg
1955 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
1956 , p_procedure_name => 'GET_DEFAULT_DLVR_DATE'
1957 , p_error_text => SUBSTRB(SQLERRM,1,240));
1958
1959 IF l_proj_date_info_csr%ISOPEN THEN
1960 CLOSE l_proj_date_info_csr;
1961 END IF;
1962
1963 IF l_task_date_info_csr%ISOPEN THEN
1964 CLOSE l_task_date_info_csr;
1965 END IF;
1966
1967 RAISE;
1968
1969 END GET_DEFAULT_DLVR_DATE;
1970
1971
1972 -- Procedure : GET_DEFAULT_ACTION_OWNER
1973 -- Type : UTILITY
1974 -- Purpose : To retrieve Default Deliverable Owner Id And Name
1975 -- Note :
1976 -- :
1977 -- Assumptions : None
1978
1979 -- Parameters Type Required Description and Purpose
1980 -- --------------------------- ------ -------- --------------------------------------------------------
1981 -- p_dlvr_verid NUMBER Y Deliverable Version Id
1982 -- x_owner_id NUMBER Owner Id
1983 -- x_owner_name VARCHAR Owner Name
1984
1985 PROCEDURE GET_DEFAULT_ACTION_OWNER
1986 (
1987 p_dlvr_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
1988 ,x_owner_id OUT NOCOPY PER_ALL_PEOPLE_F.PERSON_ID%TYPE --File.Sql.39 bug 4440895
1989 ,x_owner_name OUT NOCOPY PER_ALL_PEOPLE_F.FULL_NAME%TYPE --File.Sql.39 bug 4440895
1990 )
1991 IS
1992
1993 CURSOR l_owner_info_csr
1994 IS
1995 SELECT
1996 PPF.PERSON_ID
1997 ,PPF.FULL_NAME
1998 FROM
1999 PA_PROJ_ELEMENT_VERSIONS PEV
2000 ,PA_PROJ_ELEMENTS PPE
2001 ,PER_ALL_PEOPLE_F PPF
2002 WHERE
2003 PEV.ELEMENT_VERSION_ID = p_dlvr_ver_id
2004 AND PPE.OBJECT_TYPE = 'PA_DELIVERABLES'
2005 AND PPE.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID
2006 AND PEV.OBJECT_TYPE = 'PA_DELIVERABLES'
2007 AND PPE.MANAGER_PERSON_ID = PPF.PERSON_ID
2008 AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE;
2009
2010 BEGIN
2011
2012 OPEN l_owner_info_csr;
2013 FETCH l_owner_info_csr INTO x_owner_id, x_owner_name;
2014 CLOSE l_owner_info_csr;
2015
2016 EXCEPTION
2017
2018 WHEN OTHERS THEN
2019
2020 Fnd_Msg_Pub.add_exc_msg
2021 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
2022 , p_procedure_name => 'GET_DEFAULT_ACTION_OWNER'
2023 , p_error_text => SUBSTRB(SQLERRM,1,240));
2024
2025 IF l_owner_info_csr%ISOPEN THEN
2026 CLOSE l_owner_info_csr;
2027 END IF;
2028
2029 RAISE;
2030
2031 END GET_DEFAULT_ACTION_OWNER;
2032
2033
2034 -- Procedure : GET_DEFAULT_ACTION_DATE
2035 -- Type : UTILITY
2036 -- Purpose : To retrieve Default Deliverable Action Due Date
2037 -- Note :
2038 -- :
2039 -- Assumptions : None
2040
2041 -- Parameters Type Required Description and Purpose
2042 -- --------------------------- ------ -------- --------------------------------------------------------
2043 -- p_dlvr_ver_id NUMBER Y Dlvr Ver Id
2044 -- p_task_ver_id NUMBER Y Task Ver Id
2045 -- p_calling_mode NUMBER Y Possible Values are 'TEMPLATE', 'PROJECT'
2046 -- x_due_date DATE Dlvr Due Date
2047 -- x_earliest_start_date DATE Earliest Start Date
2048 -- x_earliest_finish_date DATE Earliest Finish Date
2049
2050 PROCEDURE GET_DEFAULT_ACTION_DATE
2051 (
2052 p_dlvr_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2053 ,p_task_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2054 ,p_project_mode IN VARCHAR2
2055 ,p_function_code IN PA_PROJ_ELEMENTS.FUNCTION_CODE%TYPE
2056 ,x_due_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
2057 )
2058 IS
2059
2060 l_scheduled_finish_date DATE := NULL;
2061 l_task_scheduled_finish_date DATE := NULL;
2062 l_sysdate DATE := NULL;
2063 l_earliest_start_date DATE := NULL;
2064 l_earliest_finish_date DATE := NULL;
2065
2066 CURSOR l_proj_date_info_csr
2067 IS
2068 SELECT
2069 PES.SCHEDULED_FINISH_DATE,
2070 SYSDATE
2071 FROM
2072 PA_PROJ_ELEMENT_VERSIONS PEV,
2073 PA_PROJ_ELEM_VER_SCHEDULE PES
2074 WHERE
2075 PEV.ELEMENT_VERSION_ID = p_dlvr_ver_id
2076 AND PEV.ELEMENT_VERSION_ID = PES.ELEMENT_VERSION_ID ;
2077
2078 /* AND PEV.OBJECT_TYPE = 'PA_DELIVERABLES'
2079 AND PEV.PROJECT_ID = PES.PROJECT_ID;
2080 Commented Unwanted Joins - This query was flagged by xpl utility because of missing index
2081 PA_PROJ_ELEM_VER_SCHEDULE_U2 in ch2m database Bug 3614361 */
2082
2083 CURSOR l_task_date_info_csr
2084 IS
2085 SELECT
2086 PES.EARLY_START_DATE
2087 ,PES.EARLY_FINISH_DATE
2088 ,PES.SCHEDULED_FINISH_DATE
2089 FROM
2090 PA_PROJ_ELEMENT_VERSIONS PEV
2091 ,PA_PROJ_ELEM_VER_SCHEDULE PES
2092 WHERE
2093 PEV.ELEMENT_VERSION_ID = p_task_ver_id
2094 AND PEV.ELEMENT_VERSION_ID = PES.ELEMENT_VERSION_ID ;
2095
2096 /* AND PEV.OBJECT_TYPE = 'PA_TASKS'
2097 AND PEV.PROJECT_ID = PES.PROJECT_ID;
2098 Commented Unwanted Joins - This query was flagged by xpl utility because of missing index
2099 PA_PROJ_ELEM_VER_SCHEDULE_U2 in ch2m database Bug 3614361 */
2100 BEGIN
2101
2102 OPEN l_proj_date_info_csr;
2103 FETCH l_proj_date_info_csr INTO l_scheduled_finish_date, l_sysdate;
2104 CLOSE l_proj_date_info_csr;
2105
2106 IF p_task_ver_id IS NULL THEN
2107 IF l_scheduled_finish_date IS NOT NULL THEN
2108 x_due_date := l_scheduled_finish_date;
2109 ELSE
2110 x_due_date := l_sysdate;
2111 END IF;
2112 ELSE
2113 IF p_project_mode = 'TEMPLATE' THEN
2114 IF l_scheduled_finish_date IS NOT NULL THEN
2115 x_due_date := l_scheduled_finish_date;
2116 ELSE
2117 x_due_date := l_sysdate;
2118 END IF;
2119 ELSE
2120 OPEN l_task_date_info_csr;
2121 FETCH l_task_date_info_csr INTO l_earliest_start_date, l_earliest_finish_date, l_task_scheduled_finish_date;
2122 CLOSE l_task_date_info_csr;
2123
2124 IF p_function_code = 'PROCUREMENT' THEN
2125 x_due_date := l_earliest_start_date;
2126 ELSIF p_function_code = 'SHIPPING' THEN
2127 x_due_date := l_earliest_finish_date;
2128 ELSE
2129 IF l_scheduled_finish_date IS NOT NULL THEN
2130 x_due_date := l_scheduled_finish_date;
2131 ELSIF l_task_scheduled_finish_date IS NOT NULL THEN
2132 x_due_date := l_task_scheduled_finish_date;
2133 ELSE
2134 x_due_date := l_sysdate;
2135 END IF;
2136 END IF;
2137
2138 IF p_function_code = 'PROCUREMENT' or p_function_code = 'SHIPPING' THEN
2139 IF x_due_date IS NULL THEN
2140 IF l_scheduled_finish_date IS NOT NULL THEN
2141 x_due_date := l_scheduled_finish_date;
2142 ELSIF l_task_scheduled_finish_date IS NOT NULL THEN
2143 x_due_date := l_task_scheduled_finish_date;
2144 ELSE
2145 x_due_date := l_sysdate;
2146 END IF;
2147 END IF;
2148 END IF;
2149 END IF;
2150 END IF;
2151 EXCEPTION
2152
2153 WHEN OTHERS THEN
2154
2155 Fnd_Msg_Pub.add_exc_msg
2156 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
2157 , p_procedure_name => 'GET_DEFAULT_ACTION_DATE'
2158 , p_error_text => SUBSTRB(SQLERRM,1,240));
2159
2160 IF l_proj_date_info_csr%ISOPEN THEN
2161 CLOSE l_proj_date_info_csr;
2162 END IF;
2163
2164 IF l_task_date_info_csr%ISOPEN THEN
2165 CLOSE l_task_date_info_csr;
2166 END IF;
2167
2168 RAISE;
2169
2170 END GET_DEFAULT_ACTION_DATE;
2171
2172 -- API to check whether deliverable based association
2173 -- exists for Deliverable
2174
2175 FUNCTION IS_DLV_BASED_ASSCN_EXISTS
2176 (
2177 p_dlv_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
2178 ,p_dlv_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL
2179 )
2180 RETURN VARCHAR2
2181 IS
2182 l_dummy VARCHAR2(1) := 'N' ;
2183 l_return_status VARCHAR2(1) := 'N' ;
2184 -- Bug: 3902158 - Changing Cursor query to avoid call to function PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD
2185 /*CURSOR c_dlv_based_task_exists IS
2186 SELECT 'X'
2187 FROM DUAL
2188 WHERE EXISTS (SELECT 'X'
2189 FROM PA_OBJECT_RELATIONSHIPS obj,
2190 PA_PROJ_ELEMENTS ppe
2191 where ppe.proj_element_id = p_dlv_element_id
2192 and ppe.object_type= 'PA_DELIVERABLES'
2193 and obj.object_id_to2 = ppe.proj_element_id
2194 and obj.object_type_from = 'PA_TASKS'
2195 and obj.object_type_to = 'PA_DELIVERABLES' -- 3570283 removed extra spaces
2196 and obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
2197 and obj.relationship_type = 'A'
2198 and nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(obj.object_id_from2),'X') = 'DELIVERABLE'
2199 ); */
2200
2201 CURSOR c_dlv_based_task_exists IS
2202 SELECT 'X'
2203 FROM DUAL
2204 WHERE EXISTS (SELECT 'X'
2205 FROM PA_OBJECT_RELATIONSHIPS obj,
2206 pa_proj_elements ppe,
2207 pa_task_types ptt
2208 where obj.object_id_to2 = p_dlv_element_id
2209 and obj.object_type_from = 'PA_TASKS'
2210 and obj.object_type_to = 'PA_DELIVERABLES'
2211 and obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
2212 and obj.relationship_type = 'A'
2213 and ppe.proj_element_id = obj.object_id_from2
2214 and ppe.type_id=ptt.task_type_id
2215 and nvl(ppe.base_percent_comp_deriv_code,ptt.base_percent_comp_deriv_code) = 'DELIVERABLE');
2216
2217
2218 BEGIN
2219 OPEN c_dlv_based_task_exists;
2220 FETCH c_dlv_based_task_exists into l_dummy ;
2221 IF c_dlv_based_task_exists%found THEN
2222 l_return_status:='Y';
2223 ELSE
2224 l_return_status:='N';
2225 END IF;
2226 CLOSE c_dlv_based_task_exists;
2227 return l_return_status;
2228 END IS_DLV_BASED_ASSCN_EXISTS ;
2229
2230 -- This function will return 'Y' if there exists any action
2231 -- with ready to ship flag as 'Y'
2232
2233 FUNCTION GET_READY_TO_SHIP_FLAG
2234 (
2235 p_dlv_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
2236 ,p_dlv_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2237 ) RETURN VARCHAR2
2238 IS
2239 CURSOR ship_flag_dlv IS
2240 SELECT 'Y'
2241 FROM dual
2242 WHERE EXISTS ( SELECT 'Y'
2243 FROM pa_object_relationships obj
2244 ,pa_proj_element_versions ver
2245 WHERE obj.object_id_from2 = p_dlv_element_id
2246 AND obj.object_type_to = 'PA_ACTIONS'
2247 AND obj.object_type_from = 'PA_DELIVERABLES'
2248 AND obj.object_id_to2 = ver.proj_element_id
2249 AND obj.relationship_type = 'A'
2250 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
2251 AND nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Ship_Yn(ver.element_version_id),'N') = 'Y'
2252 ) ;
2253 l_dummy VARCHAR2(1) := 'N' ;
2254 l_return_status VARCHAR2(1) := 'N' ;
2255 BEGIN
2256 OPEN ship_flag_dlv;
2257 FETCH ship_flag_dlv into l_dummy ;
2258 IF ship_flag_dlv%found THEN
2259 l_return_status:='Y';
2260 ELSE
2261 l_return_status:='N';
2262 END IF;
2263 CLOSE ship_flag_dlv;
2264 return l_return_status;
2265 END GET_READY_TO_SHIP_FLAG ;
2266
2267 FUNCTION GET_READY_TO_PROC_FLAG
2268 (
2269 p_dlv_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
2270 ,p_dlv_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2271 ) RETURN VARCHAR2
2272 IS
2273 CURSOR proc_flag_dlv IS
2274 SELECT 'Y'
2275 FROM dual
2276 WHERE EXISTS ( SELECT 'Y'
2277 FROM pa_object_relationships obj
2278 ,pa_proj_element_versions ver
2279 WHERE obj.object_id_from2 = p_dlv_element_id
2280 AND obj.object_type_to = 'PA_ACTIONS'
2281 AND obj.object_type_from = 'PA_DELIVERABLES'
2282 AND obj.object_id_to2 = ver.proj_element_id
2283 AND obj.relationship_type = 'A'
2284 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
2285 AND nvl(OKE_DELIVERABLE_UTILS_PUB.Ready_To_Procure_Yn(ver.element_version_id),'N') = 'Y'
2286 ) ;
2287 l_dummy VARCHAR2(1) := 'N' ;
2288 l_return_status VARCHAR2(1) := 'N' ;
2289 BEGIN
2290 OPEN proc_flag_dlv;
2291 FETCH proc_flag_dlv into l_dummy ;
2292 IF proc_flag_dlv%found THEN
2293 l_return_status:='Y';
2294 ELSE
2295 l_return_status:='N';
2296 END IF;
2297 CLOSE proc_flag_dlv;
2298 return l_return_status;
2299 END GET_READY_TO_PROC_FLAG ;
2300
2301 -- This API will return 'Y' if for a task if there exists any
2302 -- progress enabled deliverable .
2303
2304 FUNCTION IS_PROG_ENABLED_DLV_EXISTS
2305 (
2306 p_proj_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
2307 ) RETURN VARCHAR2
2308 IS
2309 CURSOR c_prog_enabled_dlv_exists IS
2310 SELECT 'Y'
2311 FROM dual
2312 WHERE EXISTS ( SELECT 'Y'
2313 FROM pa_object_relationships obj
2314 ,pa_proj_elements ppe
2315 ,pa_task_types ptt
2316 WHERE obj.object_id_from2 = p_proj_element_id
2317 AND obj.object_type_to = 'PA_DELIVERABLES'
2318 AND obj.object_type_from = 'PA_TASKS'
2319 AND obj.relationship_type = 'A'
2320 AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
2321 AND obj.object_id_to2 = ppe.proj_element_id
2322 AND ppe.object_type = 'PA_DELIVERABLES'
2323 AND ptt.task_type_id = ppe.type_id
2324 AND nvl(ptt.prog_entry_enable_flag ,'N') = 'Y'
2325 ) ;
2326 l_dummy VARCHAR2(1) := 'N' ;
2327 l_return_status VARCHAR2(1) := 'N' ;
2328 BEGIN
2329 OPEN c_prog_enabled_dlv_exists;
2330 FETCH c_prog_enabled_dlv_exists into l_dummy ;
2331 IF c_prog_enabled_dlv_exists%found THEN
2332 l_return_status:='Y';
2333 ELSE
2334 l_return_status:='N';
2335 END IF;
2336 CLOSE c_prog_enabled_dlv_exists;
2337 return l_return_status;
2338 END IS_PROG_ENABLED_DLV_EXISTS;
2339
2340 -- This API will return 'Y' is progress is enabled for deliverable
2341
2342 FUNCTION IS_PROGRESS_ENABLED
2343 (
2344 p_proj_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
2345 ) RETURN VARCHAR2
2346 IS
2347 CURSOR c_is_prog_enabled_dlv IS
2348 SELECT 'Y'
2349 FROM pa_proj_elements ppe
2350 ,pa_task_types ptt
2351 WHERE ppe.proj_element_id = p_proj_element_id
2352 AND ppe.object_type = 'PA_DELIVERABLES'
2353 AND ptt.task_type_id = ppe.type_id
2354 AND nvl(ptt.prog_entry_enable_flag,'N') = 'Y'
2355 AND ptt.object_type = 'PA_DLVR_TYPES' ;
2356
2357 l_dummy VARCHAR2(1) := 'N' ;
2358 l_return_status VARCHAR2(1) := 'N' ;
2359
2360 BEGIN
2361 OPEN c_is_prog_enabled_dlv;
2362 FETCH c_is_prog_enabled_dlv into l_dummy ;
2363 IF c_is_prog_enabled_dlv%found THEN
2364 l_return_status:='Y';
2365 ELSE
2366 l_return_status:='N';
2367 END IF;
2368 CLOSE c_is_prog_enabled_dlv;
2369 return l_return_status;
2370 END IS_PROGRESS_ENABLED;
2371
2372 -- Procedure : GET_PROJECT_DETAILS
2373 -- Type : UTILITY
2374 -- Purpose : To retrieve Project Currency Code and Organization Id
2375 -- Note :
2376 -- :
2377 -- Assumptions : None
2378
2379 -- Parameters Type Required Description and Purpose
2380 -- --------------------------- ------ -------- --------------------------------------------------------
2381 -- p_project_id NUMBER Y Project Id
2382 -- x_projfunc_currency_code VARCHAR Project Currency Code
2383 -- x_org_id VARCHAR Organization Id
2384
2385 PROCEDURE GET_PROJECT_DETAILS
2386 (
2387 p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE
2388 ,x_projfunc_currency_code OUT NOCOPY PA_PROJECTS_ALL.PROJFUNC_CURRENCY_CODE%TYPE --File.Sql.39 bug 4440895
2389 ,x_org_id OUT NOCOPY PA_PLAN_RES_DEFAULTS.item_master_id%TYPE -- 3462360 changed type --File.Sql.39 bug 4440895
2390 )
2391 IS
2392
2393 l_return_status VARCHAR2(1) := 'S';
2394 l_msg_data VARCHAR2(2000);
2395 l_msg_count NUMBER;
2396
2397 CURSOR l_project_detail_csr
2398 IS
2399 SELECT
2400 P.PROJFUNC_CURRENCY_CODE
2401 -- ,P.ORG_ID -- 3462360 removed org_id column
2402 FROM
2403 PA_PROJECTS_ALL P
2404 WHERE
2405 P.PROJECT_ID = p_project_id;
2406
2407 BEGIN
2408
2409 OPEN l_project_detail_csr;
2410 FETCH l_project_detail_csr INTO x_projfunc_currency_code;
2411 CLOSE l_project_detail_csr;
2412
2413 -- 3462360 added procedure call to retrieve material_Class_id
2414
2415 PA_RESOURCE_UTILS1.Return_Material_Class_Id
2416 (
2417 x_material_class_id => x_org_id
2418 ,x_return_status => l_return_status
2419 ,x_msg_data => l_msg_data
2420 ,x_msg_count => l_msg_count
2421 );
2422
2423 EXCEPTION
2424
2425 WHEN OTHERS THEN
2426
2427 Fnd_Msg_Pub.add_exc_msg
2428 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
2429 , p_procedure_name => 'GET_PROJECT_DETAILS'
2430 , p_error_text => SUBSTRB(SQLERRM,1,240));
2431
2432 IF l_project_detail_csr%ISOPEN THEN
2433 CLOSE l_project_detail_csr;
2434 END IF;
2435
2436 RAISE;
2437
2438 END GET_PROJECT_DETAILS;
2439
2440 FUNCTION GET_DLV_DESCRIPTION
2441 (
2442 p_action_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2443 ) RETURN VARCHAR2
2444 IS
2445 CURSOR c_dlv_desc IS
2446 SELECT ppe.description
2447 FROM pa_proj_elements ppe
2448 ,pa_object_relationships obj
2449 ,pa_proj_element_versions pev
2450 WHERE pev.element_version_id = p_action_ver_id
2451 AND pev.object_type = 'PA_ACTIONS' /*Included this clause for Performance fix Bug # 3614361 */
2452 AND pev.proj_element_id = obj.object_id_to2
2453 AND obj.relationship_type = 'A'
2454 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
2455 AND obj.object_type_from = 'PA_DELIVERABLES'
2456 AND obj.object_type_to = 'PA_ACTIONS'
2457 AND obj.object_id_from2 = ppe.proj_element_id
2458 AND ppe.object_type = 'PA_DELIVERABLES' /*Included this clause for Performance fix Bug # 3614361 */
2459 ;
2460
2461 l_dummy pa_proj_elements.description%TYPE;
2462 BEGIN
2463 OPEN c_dlv_desc;
2464 FETCH c_dlv_desc into l_dummy ;
2465 CLOSE c_dlv_desc;
2466 RETURN l_dummy ;
2467 END GET_DLV_DESCRIPTION;
2468
2469 -- 3470061 oke needed this api which will take deliverable version id as in parameter
2470 -- and return deliverable description
2471
2472 FUNCTION GET_DELIVERABLE_DESCRIPTION
2473 (
2474 p_deliverable_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2475 ) RETURN VARCHAR2
2476 IS
2477 CURSOR c_dlv_desc IS
2478 SELECT
2479 ppe.description
2480 FROM
2481 pa_proj_elements ppe
2482 ,pa_proj_element_versions pev
2483 WHERE
2484 pev.element_version_id = p_deliverable_id
2485 AND ppe.object_type = 'PA_DELIVERABLES'
2486 AND pev.object_type = 'PA_DELIVERABLES'
2487 AND ppe.proj_element_id = pev.proj_element_id
2488 AND ppe.project_id = pev.project_id;
2489
2490 l_dummy pa_proj_elements.description%TYPE;
2491
2492 BEGIN
2493 OPEN c_dlv_desc;
2494 FETCH c_dlv_desc into l_dummy ;
2495 CLOSE c_dlv_desc;
2496 RETURN l_dummy ;
2497
2498 END GET_DELIVERABLE_DESCRIPTION;
2499
2500 -- 3470061
2501
2502 FUNCTION IS_DLV_ITEM_BASED
2503 (
2504 p_action_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2505 ) RETURN VARCHAR2
2506 IS
2507 CURSOR c_is_dlv_item_based IS
2508 SELECT 'Y'
2509 FROM dual
2510 WHERE EXISTS (SELECT 'Y'
2511 FROM pa_proj_elements ppe
2512 ,pa_object_relationships obj
2513 ,pa_proj_element_versions pev
2514 ,pa_task_types ptt
2515 WHERE pev.element_version_id = p_action_ver_id
2516 AND pev.proj_element_id = obj.object_id_to2
2517 AND obj.relationship_type = 'A'
2518 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
2519 AND obj.object_type_from = 'PA_DELIVERABLES'
2520 AND obj.object_type_to = 'PA_ACTIONS'
2521 AND obj.object_id_from2 = ppe.proj_element_id
2522 AND ptt.task_type_id = ppe.type_id
2523 AND ptt.task_type_class_code = 'ITEM'
2524 ) ;
2525 l_dummy VARCHAR2(1) := 'N' ;
2526 BEGIN
2527 OPEN c_is_dlv_item_based;
2528 FETCH c_is_dlv_item_based into l_dummy ;
2529 CLOSE c_is_dlv_item_based;
2530 RETURN l_dummy ;
2531 END IS_DLV_ITEM_BASED ;
2532
2533 -- Procedure : GET_DEFAULT_ACTN_DATE
2534 -- Type : UTILITY
2535 -- Purpose : To retrieve Default Deliverable Due Date
2536 -- Note :
2537 -- :
2538 -- Assumptions : None
2539
2540 -- Parameters Type Required Description and Purpose
2541 -- --------------------------- ------ -------- --------------------------------------------------------
2542 -- p_dlvr_ver_id NUMBER Y Dlvr Ver Id
2543 -- p_task_ver_id NUMBER Y Task Ver Id
2544 -- p_project_mode NUMBER Y Possible Values are 'TEMPLATE', 'PROJECT'
2545 -- x_due_date DATE Dlvr Due Date
2546 -- x_earliest_start_date DATE Earliest Start Date
2547 -- x_earliest_finish_date DATE Earliest Finish Date
2548
2549 PROCEDURE GET_DEFAULT_ACTN_DATE
2550 (
2551 p_dlvr_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2552 ,p_task_ver_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
2553 ,p_project_mode IN VARCHAR2
2554 ,x_due_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
2555 ,x_earliest_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
2556 ,x_earliest_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
2557 )
2558 IS
2559
2560 l_scheduled_finish_date DATE := NULL;
2561 l_task_scheduled_finish_date DATE := NULL;
2562 l_sysdate DATE := NULL;
2563
2564 CURSOR l_proj_date_info_csr
2565 IS
2566 SELECT
2567 PES.SCHEDULED_FINISH_DATE,
2568 SYSDATE
2569 FROM
2570 PA_PROJ_ELEMENT_VERSIONS PEV,
2571 PA_PROJ_ELEM_VER_SCHEDULE PES
2572 WHERE
2573 PEV.ELEMENT_VERSION_ID = p_dlvr_ver_id
2574 AND PEV.ELEMENT_VERSION_ID = PES.ELEMENT_VERSION_ID
2575 AND PEV.OBJECT_TYPE = 'PA_DELIVERABLES'
2576 AND PEV.PROJECT_ID = PES.PROJECT_ID;/* Including this additional clause for Performance Fix : Bug 3614361 */
2577
2578 CURSOR l_task_date_info_csr
2579 IS
2580 SELECT
2581 PES.EARLY_START_DATE
2582 ,PES.EARLY_FINISH_DATE
2583 ,PES.SCHEDULED_FINISH_DATE
2584 FROM
2585 PA_PROJ_ELEMENT_VERSIONS PEV
2586 ,PA_PROJ_ELEM_VER_SCHEDULE PES
2587 WHERE
2588 PEV.ELEMENT_VERSION_ID = p_task_ver_id
2589 AND PEV.ELEMENT_VERSION_ID = PES.ELEMENT_VERSION_ID
2590 AND PEV.OBJECT_TYPE = 'PA_TASKS'
2591 AND PEV.PROJECT_ID = PES.PROJECT_ID;/* Including this additional clause for Performance Fix : Bug 3614361 */
2592
2593 BEGIN
2594
2595 OPEN l_proj_date_info_csr;
2596 FETCH l_proj_date_info_csr INTO l_scheduled_finish_date, l_sysdate;
2597 CLOSE l_proj_date_info_csr;
2598
2599 IF p_task_ver_id IS NULL THEN
2600
2601 IF l_scheduled_finish_date IS NOT NULL THEN
2602 x_due_date := l_scheduled_finish_date;
2603 ELSE
2604 x_due_date := l_sysdate;
2605 END IF;
2606
2607 x_earliest_start_date := NULL;
2608 x_earliest_finish_date := NULL;
2609
2610 ELSE
2611
2612 IF p_project_mode = 'TEMPLATE' THEN
2613
2614 IF l_scheduled_finish_date IS NOT NULL THEN
2615 x_due_date := l_scheduled_finish_date;
2616 ELSE
2617 x_due_date := l_sysdate;
2618 END IF;
2619
2620 x_earliest_start_date := NULL;
2621 x_earliest_finish_date := NULL;
2622
2623 ELSE
2624
2625 OPEN l_task_date_info_csr;
2626 FETCH l_task_date_info_csr INTO x_earliest_start_date, x_earliest_finish_date, l_task_scheduled_finish_date;
2627 CLOSE l_task_date_info_csr;
2628
2629 IF l_scheduled_finish_date IS NOT NULL THEN
2630 x_due_date := l_scheduled_finish_date;
2631 ELSIF l_task_scheduled_finish_date IS NOT NULL THEN
2632 x_due_date := l_task_scheduled_finish_date;
2633 ELSE
2634 x_due_date := l_sysdate;
2635 END IF;
2636
2637 END IF;
2638
2639 END IF;
2640 EXCEPTION
2641
2642 WHEN OTHERS THEN
2643
2644 Fnd_Msg_Pub.add_exc_msg
2645 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
2646 , p_procedure_name => 'GET_DEFAULT_ACTN_DATE'
2647 , p_error_text => SUBSTRB(SQLERRM,1,240));
2648
2649 IF l_proj_date_info_csr%ISOPEN THEN
2650 CLOSE l_proj_date_info_csr;
2651 END IF;
2652
2653 IF l_task_date_info_csr%ISOPEN THEN
2654 CLOSE l_task_date_info_csr;
2655 END IF;
2656
2657 RAISE;
2658
2659 END GET_DEFAULT_ACTN_DATE;
2660
2661 PROCEDURE CHECK_DLVR_DISABLE_ALLOWED( p_api_version IN NUMBER := 1.0
2662 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2663 ,p_debug_mode IN VARCHAR2 := 'N'
2664 ,p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
2665 ,x_return_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2666 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2667 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2668 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2669 IS
2670
2671 /* Bug 3597178 This cursor is no more needed
2672 As we dont need to check for existence of association b/w deliverable and deliverable based tasks for that project
2673 CURSOR cur_tsk_assoc IS
2674 SELECT 1 FROM dual
2675 WHERE EXISTS(
2676 SELECT ppe.proj_element_id DlvrElemId, ppev.element_version_id DlvrElemVerId
2677 FROM pa_proj_elements ppe,
2678 pa_proj_element_versions ppev
2679 WHERE ppe.proj_element_id = ppev.proj_element_id
2680 AND ppe.project_id = p_project_id
2681 AND ppev.project_id = p_project_id
2682 AND ppe.object_type = ppev.object_type
2683 AND ppe.object_type = 'PA_DELIVERABLES'
2684 AND 'Y' = PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(ppe.proj_element_id,ppev.element_version_id)
2685 );
2686 */
2687
2688 CURSOR cur_dlvr_progress IS
2689 SELECT 1 FROM dual
2690 WHERE EXISTS(
2691 SELECT 1
2692 FROM pa_proj_elements ppe
2693 WHERE ppe.project_id = p_project_id
2694 AND ppe.object_type = 'PA_DELIVERABLES'
2695 AND 'Y' = PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(p_project_id,ppe.proj_element_id)
2696 );
2697 /* Commented the following SELECT statement for Performance Bug Fix 3614361 */
2698 /*
2699 SELECT 1 FROM dual
2700 WHERE EXISTS(
2701 SELECT ppe.proj_element_id DlvrElemId, ppev.element_version_id DlvrElemVerId
2702 FROM pa_proj_elements ppe,
2703 pa_proj_element_versions ppev
2704 WHERE ppe.project_id = p_project_id
2705 AND ppe.object_type = 'PA_DELIVERABLES'
2706 AND 'Y' = PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(p_project_id,ppe.proj_element_id)
2707 );
2708 */
2709
2710 CURSOR cur_ship_or_proc IS
2711 SELECT 1 FROM dual
2712 WHERE EXISTS(
2713 SELECT ppe.proj_element_id DlvrElemId, ppev.element_version_id DlvrElemVerId
2714 FROM pa_proj_elements ppe,
2715 pa_proj_element_versions ppev
2716 WHERE ppe.proj_element_id = ppev.proj_element_id
2717 AND ppe.project_id = p_project_id
2718 AND ppev.project_id = p_project_id
2719 AND ppe.object_type = ppev.object_type
2720 AND ppe.object_type = 'PA_ACTIONS'
2721 AND ( 'Y' = PA_DELIVERABLE_UTILS.GET_READY_TO_SHIP_FLAG(ppe.proj_element_id,ppev.element_version_id)
2722 OR 'Y' = PA_DELIVERABLE_UTILS.GET_READY_TO_PROC_FLAG(ppe.proj_element_id,ppev.element_version_id) )
2723 );
2724
2725 CURSOR cur_billing_fn IS
2726 SELECT 1 FROM dual
2727 WHERE EXISTS(
2728 SELECT 1
2729 FROM pa_proj_elements ppe
2730 WHERE ppe.project_id = p_project_id
2731 AND ppe.object_type = 'PA_ACTIONS'
2732 AND PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(ppe.proj_element_id) = 'BILLING'
2733 AND 'Y' = PA_Billing_Wrkbnch_Events.CHECK_BILLING_EVENT_EXISTS(p_project_id,ppe.proj_element_id)
2734 );
2735
2736 l_debug_mode VARCHAR2(1);
2737 l_debug_level2 CONSTANT NUMBER := 2;
2738 l_debug_level3 CONSTANT NUMBER := 3;
2739 l_debug_level4 CONSTANT NUMBER := 4;
2740 l_debug_level5 CONSTANT NUMBER := 5;
2741
2742 l_dummy NUMBER;
2743
2744 BEGIN
2745
2746 x_return_flag := null;
2747 x_return_status := FND_API.G_RET_STS_SUCCESS;
2748 x_msg_count := 0;
2749 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2750
2751 IF l_debug_mode = 'Y' THEN
2752 PA_DEBUG.set_curr_function( p_function => 'PA_DELIVERABLE_UTILS : CHECK_DLVR_DISABLE_ALLOWED',
2753 p_debug_mode => l_debug_mode );
2754 END IF;
2755
2756 IF l_debug_mode = 'Y' THEN
2757 Pa_Debug.g_err_stage:= 'PA_DELIVERABLE_UTILS : CHECK_DLVR_DISABLE_ALLOWED : Printing Input parameters';
2758 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
2759 l_debug_level3);
2760 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
2761 l_debug_level3);
2762 END IF;
2763
2764 IF 'N' = PA_PROJECT_STRUCTURE_UTILS.CHECK_DELIVERABLE_ENABLED(p_project_id) THEN
2765 x_return_flag := 'Y';
2766 ELSE
2767
2768 /* Bug 3597178 This check is not needed
2769 OPEN cur_tsk_assoc;
2770 FETCH cur_tsk_assoc INTO l_dummy;
2771 IF cur_tsk_assoc%FOUND THEN
2772 x_return_flag := 'N';
2773 x_return_status := FND_API.G_RET_STS_ERROR;
2774 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2775 p_msg_name => 'PA_DLV_TASK_ASSN_EXISTS' );
2776 CLOSE cur_tsk_assoc;
2777 return;
2778 ELSE
2779 CLOSE cur_tsk_assoc;
2780 END IF;
2781 End of Changes for Bug 3597178 */
2782
2783 OPEN cur_dlvr_progress;
2784 FETCH cur_dlvr_progress INTO l_dummy;
2785 IF cur_dlvr_progress%FOUND THEN
2786 x_return_flag := 'N';
2787 x_return_status := FND_API.G_RET_STS_ERROR;
2788 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2789 p_msg_name => 'PA_DLV_HAS_PROGRESS' );
2790 CLOSE cur_dlvr_progress;
2791 return;
2792 ELSE
2793 CLOSE cur_dlvr_progress;
2794 END IF;
2795
2796 OPEN cur_ship_or_proc;
2797 FETCH cur_ship_or_proc INTO l_dummy;
2798 IF cur_ship_or_proc%FOUND THEN
2799 x_return_flag := 'N';
2800 x_return_status := FND_API.G_RET_STS_ERROR;
2801 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2802 p_msg_name => 'PA_DLV_ACTION_TXN_EXISTS' );
2803 CLOSE cur_ship_or_proc;
2804 return;
2805 ELSE
2806 CLOSE cur_ship_or_proc;
2807 END IF;
2808
2809 OPEN cur_billing_fn;
2810 FETCH cur_billing_fn INTO l_dummy;
2811 IF cur_billing_fn%FOUND THEN
2812 x_return_flag := 'N';
2813 x_return_status := FND_API.G_RET_STS_ERROR;
2814 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2815 p_msg_name => 'PA_DLV_ACTION_TXN_EXISTS' );
2816 CLOSE cur_billing_fn;
2817 return;
2818 ELSE
2819 CLOSE cur_billing_fn;
2820 END IF;
2821
2822 -- x_return_flag := 'W';
2823 -- x_return_status := FND_API.G_RET_STS_ERROR;
2824 -- PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2825 -- p_msg_name => 'PA_DLV_DEFINED' );
2826 return;
2827 END IF;
2828
2829 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2830 pa_debug.reset_curr_function;
2831 END IF ;
2832
2833 EXCEPTION
2834 WHEN OTHERS THEN
2835 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2836 x_msg_count := 1;
2837 x_msg_data := SQLERRM;
2838
2839 IF cur_dlvr_progress%ISOPEN THEN
2840 CLOSE cur_dlvr_progress;
2841 END IF;
2842 IF cur_ship_or_proc%ISOPEN THEN
2843 CLOSE cur_ship_or_proc;
2844 END IF;
2845
2846 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2847 pa_debug.reset_curr_function;
2848 END IF ;
2849
2850 Fnd_Msg_Pub.add_exc_msg
2851 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
2852 , p_procedure_name => 'CHECK_DLVR_DISABLE_ALLOWED'
2853 , p_error_text => x_msg_data );
2854 RAISE;
2855 END CHECK_DLVR_DISABLE_ALLOWED;
2856
2857
2858
2859
2860
2861 PROCEDURE UPDATE_TSK_STATUS_CANCELLED( p_api_version IN NUMBER := 1.0
2862 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2863 ,p_debug_mode IN VARCHAR2 := 'N'
2864 ,p_task_id IN NUMBER
2865 ,p_status_code IN PA_PROJECT_STATUSES.PROJECT_STATUS_CODE%TYPE
2866 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2867 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2868 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2869 )
2870 IS
2871
2872 CURSOR cur_check_cancel_possible IS
2873 SELECT 1 FROM dual
2874 WHERE EXISTS(
2875 SELECT 1
2876 FROM pa_proj_elements ppe,
2877 pa_proj_element_versions ppv,
2878 pa_object_relationships obj1,
2879 pa_object_relationships obj2
2880 WHERE ppe.proj_element_id=p_task_id
2881 AND ppe.object_type='PA_TASKS'
2882 AND obj1.relationship_type='A'
2883 AND obj1.relationship_subtype='TASK_TO_DELIVERABLE'
2884 AND obj1.object_id_from2=p_task_id
2885 AND obj1.object_type_from='PA_TASKS'
2886 AND obj1.object_type_to='PA_DELIVERABLES'
2887 AND obj2.relationship_type='A'
2888 AND obj2.relationship_subtype='DELIVERABLE_TO_ACTION'
2889 AND obj2.object_id_from2=obj1.object_id_to2
2890 AND obj2.object_type_from='PA_DELIVERABLES'
2891 AND obj2.object_type_to='PA_ACTIONS'
2892 AND ppv.proj_element_id=obj2.object_id_to2
2893 AND ppv.object_type='PA_ACTIONS'
2894 AND ( nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ppv.element_version_id),'N') = 'Y'
2895 OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ppv.element_version_id),'N') = 'Y'
2896 OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(obj2.object_id_to2) = 'BILLING' )
2897 );
2898 CURSOR cur_get_assoc_dlvr IS
2899 SELECT proj_element_id
2900 FROM pa_proj_elements ppe,
2901 pa_object_relationships obj
2902 WHERE obj.relationship_type='A'
2903 AND obj.relationship_subtype='TASK_TO_DELIVERABLE'
2904 AND obj.object_id_from2=p_task_id
2905 AND obj.object_type_from='PA_TASKS'
2906 AND obj.object_type_to='PA_DELIVERABLES'
2907 AND ppe.proj_element_id = obj.object_id_to2
2908 AND ppe.object_type = 'PA_DELIVERABLES';
2909
2910 l_debug_mode VARCHAR2(1);
2911 l_debug_level2 CONSTANT NUMBER := 2;
2912 l_debug_level3 CONSTANT NUMBER := 3;
2913 l_debug_level4 CONSTANT NUMBER := 4;
2914 l_debug_level5 CONSTANT NUMBER := 5;
2915
2916 l_system_code PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
2917 l_dummy NUMBER;
2918 BEGIN
2919 x_return_status := FND_API.G_RET_STS_SUCCESS;
2920 x_msg_count := 0;
2921 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2922
2923 IF l_debug_mode = 'Y' THEN
2924 PA_DEBUG.set_curr_function( p_function => 'PA_DELIVERABLE_UTILS : UPDATE_TSK_STATUS_CANCELLED',
2925 p_debug_mode => l_debug_mode );
2926 END IF;
2927 IF l_debug_mode = 'Y' THEN
2928 Pa_Debug.g_err_stage:= 'PA_DELIVERABLE_UTILS : UPDATE_TSK_STATUS_CANCELLED : Printing Input parameters';
2929 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
2930 l_debug_level3);
2931 Pa_Debug.WRITE(g_module_name,'p_task_id'||':'||p_task_id,
2932 l_debug_level3);
2933 Pa_Debug.WRITE(g_module_name,'p_status_code'||':'||p_status_code,
2934 l_debug_level3);
2935 END IF;
2936
2937 SELECT distinct PROJECT_SYSTEM_STATUS_CODE INTO l_system_code
2938 FROM pa_project_statuses
2939 WHERE STATUS_TYPE='TASK'
2940 AND PROJECT_STATUS_CODE=p_status_code;
2941
2942 IF 'DELIVERABLE' <> PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_task_id)
2943 OR (l_system_code <> 'CANCELLED' AND l_system_code <> 'ON_HOLD') THEN
2944 return;
2945 ELSIF l_system_code = 'CANCELLED' THEN
2946 OPEN cur_check_cancel_possible;
2947 FETCH cur_check_cancel_possible INTO l_dummy;
2948 IF cur_check_cancel_possible%FOUND THEN
2949 x_return_status := FND_API.G_RET_STS_ERROR;
2950 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2951 p_msg_name => 'PA_TSK_DEL_TXN_EXISTS' );
2952 CLOSE cur_check_cancel_possible;
2953 return;
2954 ELSE
2955 CLOSE cur_check_cancel_possible;
2956 END IF;
2957 END IF;
2958 FOR assoc_dlvr_rec IN cur_get_assoc_dlvr LOOP
2959 UPDATE pa_proj_elements
2960 SET status_code = p_status_code
2961 WHERE proj_element_id = assoc_dlvr_rec.proj_element_id;
2962 END LOOP;
2963
2964 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2965 pa_debug.reset_curr_function;
2966 END IF ;
2967
2968 EXCEPTION
2969 WHEN OTHERS THEN
2970 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2971 x_msg_count := 1;
2972 x_msg_data := SQLERRM;
2973
2974 IF cur_check_cancel_possible%ISOPEN THEN
2975 CLOSE cur_check_cancel_possible;
2976 END IF;
2977 IF cur_get_assoc_dlvr%ISOPEN THEN
2978 CLOSE cur_get_assoc_dlvr;
2979 END IF;
2980
2981 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
2982 pa_debug.reset_curr_function;
2983 END IF ;
2984
2985 Fnd_Msg_Pub.add_exc_msg
2986 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
2987 , p_procedure_name => 'UPDATE_TSK_STATUS_CANCELLED'
2988 , p_error_text => x_msg_data );
2989 RAISE;
2990 END UPDATE_TSK_STATUS_CANCELLED;
2991
2992
2993
2994
2995
2996 PROCEDURE CHECK_CHANGE_MAPPING_OK( p_api_version IN NUMBER := 1.0
2997 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2998 ,p_debug_mode IN VARCHAR2 := 'N'
2999 ,p_wp_task_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
3000 ,p_fp_task_verison_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
3001 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3002 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3003 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3004 )
3005 IS
3006 CURSOR cur_check_transaction_init IS
3007 SELECT 1 FROM dual
3008 WHERE EXISTS(
3009 SELECT 1
3010 FROM pa_proj_elements ppe,
3011 pa_proj_element_versions ppv,
3012 pa_object_relationships obj1,
3013 pa_object_relationships obj2
3014 WHERE ppe.proj_element_id=p_wp_task_version_id
3015 AND ppe.object_type='PA_TASKS'
3016 AND obj1.relationship_type='A'
3017 AND obj1.relationship_subtype='TASK_TO_DELIVERABLE'
3018 AND obj1.object_id_from2=ppe.proj_element_id
3019 AND obj1.object_type_from='PA_TASKS'
3020 AND obj1.object_type_to='PA_DELIVERABLES'
3021 AND obj2.relationship_type='A'
3022 AND obj2.relationship_subtype='DELIVERABLE_TO_ACTION'
3023 AND obj2.object_id_from2=obj1.object_id_to2
3024 AND obj2.object_type_from='PA_DELIVERABLES'
3025 AND obj2.object_type_to='PA_ACTIONS'
3026 AND ppv.proj_element_id=obj2.object_id_to2
3027 AND ppv.object_type='PA_ACTIONS'
3028 AND ( nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ppv.element_version_id),'N') = 'Y'
3029 OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ppv.element_version_id),'N') = 'Y'
3030 OR nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(ppv.element_version_id),'N') = 'Y'
3031 OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(obj2.object_id_to2) = 'BILLING' )
3032 );
3033
3034 l_debug_mode VARCHAR2(1);
3035 l_debug_level2 CONSTANT NUMBER := 2;
3036 l_debug_level3 CONSTANT NUMBER := 3;
3037 l_debug_level4 CONSTANT NUMBER := 4;
3038 l_debug_level5 CONSTANT NUMBER := 5;
3039
3040 l_dummy NUMBER;
3041 BEGIN
3042 x_return_status := FND_API.G_RET_STS_SUCCESS;
3043 x_msg_count := 0;
3044 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3045
3046 IF l_debug_mode = 'Y' THEN
3047 PA_DEBUG.set_curr_function( p_function => 'PA_DELIVERABLE_UTILS : CHECK_CHANGE_MAPPING_OK',
3048 p_debug_mode => l_debug_mode );
3049 END IF;
3050
3051 IF l_debug_mode = 'Y' THEN
3052 Pa_Debug.g_err_stage := 'PA_DELIVERABLE_UTILS : CHECK_CHANGE_MAPPING_OK : Printing Input parameters';
3053 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3054 l_debug_level3);
3055 Pa_Debug.WRITE(g_module_name,'p_wp_task_version_id'||':'||p_wp_task_version_id,
3056 l_debug_level3);
3057 Pa_Debug.WRITE(g_module_name,'p_fp_task_verison_id'||':'||p_fp_task_verison_id,
3058 l_debug_level3);
3059 END IF;
3060
3061 OPEN cur_check_transaction_init;
3062 FETCH cur_check_transaction_init INTO l_dummy;
3063 IF cur_check_transaction_init%FOUND THEN
3064 x_return_status := FND_API.G_RET_STS_ERROR;
3065 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
3066 p_msg_name => 'PA_PS_WP_MAPPING_TXN_EXISTS' );
3067 END IF;
3068 CLOSE cur_check_transaction_init;
3069
3070 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
3071 pa_debug.reset_curr_function;
3072 END IF ;
3073
3074 EXCEPTION
3075 WHEN OTHERS THEN
3076 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3077 x_msg_count := 1;
3078 x_msg_data := SQLERRM;
3079
3080 IF cur_check_transaction_init%ISOPEN THEN
3081 CLOSE cur_check_transaction_init;
3082 END IF;
3083 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
3084 pa_debug.reset_curr_function;
3085 END IF ;
3086 Fnd_Msg_Pub.add_exc_msg
3087 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
3088 , p_procedure_name => 'CHECK_DLVR_DISABLE_ALLOWED'
3089 , p_error_text => x_msg_data );
3090 RAISE;
3091 END CHECK_CHANGE_MAPPING_OK;
3092
3093
3094 -- Bug 3957706 < Start >
3095 -- This API is called from the following places :-
3096 -- 1) PA_TASK_PVT1.Update_Task API
3097 -- In this Context,
3098 -- p_task_id - The Task's Proj Element ID
3099 -- p_prog_method_code - New Progress Method Code for the Task
3100
3101 PROCEDURE CHECK_PROGRESS_MTH_CODE_VALID( p_api_version IN NUMBER := 1.0
3102 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3103 ,p_debug_mode IN VARCHAR2 := 'N'
3104 ,p_task_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
3105 ,p_prog_method_code IN PA_PROJ_ELEMENTS.BASE_PERCENT_COMP_DERIV_CODE%TYPE
3106 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3107 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3108 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3109 )
3110 IS
3111 /* Commented this Cursor as It is not used anywhere
3112 CURSOR cur_assoc_dlvr_has_prog IS
3113 SELECT 1 FROM dual
3114 WHERE EXISTS(
3115 SELECT ppe.proj_element_id
3116 FROM pa_proj_elements ppe,
3117 pa_object_relationships obj
3118 WHERE obj.relationship_type='A'
3119 AND obj.relationship_subtype='TASK_TO_DELIVERABLE'
3120 AND obj.object_id_from2=p_task_id
3121 AND obj.object_type_from='PA_TASKS'
3122 AND obj.object_type_to='PA_DELIVERABLES'
3123 AND ppe.proj_element_id = obj.object_id_to2
3124 AND 'Y' = PA_DELIVERABLE_UTILS.IS_DELIVERABLE_HAS_PROGRESS(ppe.project_id,ppe.proj_element_id)
3125 );
3126 */
3127
3128 CURSOR cur_dlvr_assoc_exists IS
3129 SELECT 1 FROM dual
3130 WHERE EXISTS(
3131 SELECT 1
3132 FROM pa_proj_elements ppe,
3133 pa_object_relationships obj,
3134 pa_proj_element_versions ppev
3135 WHERE ppe.proj_element_id=p_task_id
3136 AND ppe.object_type='PA_TASKS'
3137 AND obj.relationship_type='A'
3138 AND obj.relationship_subtype='TASK_TO_DELIVERABLE'
3139 AND obj.object_id_from2=ppe.proj_element_id
3140 AND obj.object_type_from='PA_TASKS'
3141 AND obj.object_type_to='PA_DELIVERABLES'
3142 AND ppev.proj_element_id = obj.object_id_to2
3143 AND ppev.project_id = ppe.project_id
3144 /*This AND Clause is Wrong as the 1st param passed is the Task ID whereas IS_DLV_BASED_ASSCN_EXISTS API
3145 expects the 1st param as the Deliverable's Proj Element ID
3146 AND 'Y' = PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(ppe.proj_element_id,
3147 ppev.element_version_id)
3148 So,Included the new AND CLause as below */
3149 AND 'Y' = PA_DELIVERABLE_UTILS.IS_DLV_BASED_ASSCN_EXISTS(ppev.proj_element_id ,
3150 ppev.element_version_id)
3151 );
3152
3153 l_dummy NUMBER;
3154
3155 BEGIN
3156 x_return_status := FND_API.G_RET_STS_SUCCESS;
3157 x_msg_count := 0;
3158
3159 /* Commented this code because we dont need this code
3160 In the Context ,this API is used .
3161 IF 'DELIVERABLE' = PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_task_id)
3162 AND 'DELIVERABLE' <> p_prog_method_code THEN
3163 OPEN cur_assoc_dlvr_has_prog;
3164 FETCH cur_assoc_dlvr_has_prog INTO l_dummy;
3165 IF cur_assoc_dlvr_has_prog%FOUND THEN
3166 x_return_status := FND_API.G_RET_STS_ERROR;
3167 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
3168 p_msg_name => 'PA_TSK_PROGRESS_LOSE_WARN' );
3169 CLOSE cur_assoc_dlvr_has_prog;
3170 return;
3171 ELSIF */
3172
3173 --nvl check needed in following if Clause as NULL Value also can be returned
3174 IF 'DELIVERABLE' <> nvl(PA_DELIVERABLE_UTILS.GET_PROGRESS_ROLLUP_METHOD(p_task_id),'X')
3175 AND 'DELIVERABLE' = p_prog_method_code THEN
3176
3177 OPEN cur_dlvr_assoc_exists;
3178 FETCH cur_dlvr_assoc_exists INTO l_dummy;
3179 CLOSE cur_dlvr_assoc_exists;
3180
3181 IF nvl(l_dummy,0)=1 THEN
3182 x_return_status := FND_API.G_RET_STS_ERROR;
3183 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
3184 p_msg_name => 'PA_TSK_PROGRESS_MTH_ERR' );
3185 /* CLOSE cur_assoc_dlvr_has_prog; Commented as Cursor not used*/
3186 END IF;
3187
3188 END IF;
3189
3190 /* CLOSE cur_assoc_dlvr_has_prog;
3191
3192 END IF; Commented as this cursor not used anymore */
3193
3194 EXCEPTION
3195 WHEN OTHERS THEN
3196 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3197 x_msg_count := 1;
3198 x_msg_data := SQLERRM;
3199
3200 /* IF cur_assoc_dlvr_has_prog%ISOPEN THEN
3201 CLOSE cur_assoc_dlvr_has_prog;
3202 END IF;
3203 Commented as cursor not used */
3204 --Bug 3957706 < End >
3205
3206 IF cur_dlvr_assoc_exists%ISOPEN THEN
3207 CLOSE cur_dlvr_assoc_exists;
3208 END IF;
3209
3210 Fnd_Msg_Pub.add_exc_msg
3211 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
3212 , p_procedure_name => 'CHECK_PROGRESS_MTH_CODE_VALID'
3213 , p_error_text => x_msg_data );
3214 RAISE;
3215 END CHECK_PROGRESS_MTH_CODE_VALID;
3216
3217
3218
3219
3220
3221 FUNCTION CHECK_PROJ_DLV_TXN_EXISTS( p_api_version IN NUMBER := 1.0
3222 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
3223 ,p_debug_mode IN VARCHAR2 := 'N'
3224 ,p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
3225 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3226 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3227 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3228 )
3229 RETURN VARCHAR2 IS
3230
3231 CURSOR cur_proj_txn_exists IS
3232 SELECT 'Y' FROM dual
3233 WHERE EXISTS(
3234 SELECT 1
3235 FROM pa_proj_elements ppe,
3236 pa_proj_elements ppe2,
3237 pa_proj_element_versions ppev,
3238 pa_object_relationships obj1,
3239 pa_object_relationships obj2
3240 WHERE ppe.project_id = p_project_id
3241 AND ppe.object_type = 'PA_ACTIONS'
3242 AND ppev.project_id = p_project_id
3243 AND ppe.proj_element_id = ppev.proj_element_id
3244 AND obj1.object_id_to2 = ppe.proj_element_id
3245 AND obj1.relationship_type ='A'
3246 AND obj1.relationship_subtype = 'DELIVERABLE_TO_ACTION'
3247 AND obj1.object_type_from ='PA_DELIVERABLES'
3248 AND obj1.object_type_to = 'PA_ACTIONS'
3249 AND obj2.object_id_to2 = obj1.object_id_from2
3250 AND obj2.relationship_type ='A'
3251 AND obj2.relationship_subtype='TASK_TO_DELIVERABLE'
3252 AND obj2.object_type_from = 'PA_TASKS'
3253 AND obj2.object_type_to = 'PA_DELIVERABLES'
3254 AND ppe2.proj_element_id=obj2.object_id_from1
3255 AND ppe2.object_type='PA_TASKS'
3256 AND ( nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ppev.element_version_id),'N') = 'Y'
3257 OR nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ppev.element_version_id),'N') = 'Y'
3258 OR nvl(OKE_DELIVERABLE_UTILS_PUB.MDS_Initiated_Yn(ppev.element_version_id),'N') = 'Y'
3259 OR PA_DELIVERABLE_UTILS.GET_FUNCTION_CODE(ppe.proj_element_id) = 'BILLING' )
3260 );
3261
3262 l_debug_mode VARCHAR2(1);
3263 l_debug_level2 CONSTANT NUMBER := 2;
3264 l_debug_level3 CONSTANT NUMBER := 3;
3265 l_debug_level4 CONSTANT NUMBER := 4;
3266 l_debug_level5 CONSTANT NUMBER := 5;
3267
3268 l_return_flag VARCHAR2(1) := 'Y';
3269 BEGIN
3270 x_return_status := FND_API.G_RET_STS_SUCCESS;
3271 x_msg_count := 0;
3272 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3273
3274 IF l_debug_mode = 'Y' THEN
3275 PA_DEBUG.set_curr_function( p_function => 'PA_DELIVERABLE_UTILS : CHECK_PROJ_DLV_TXN_EXISTS',
3276 p_debug_mode => l_debug_mode );
3277 END IF;
3278 IF l_debug_mode = 'Y' THEN
3279 Pa_Debug.g_err_stage:= 'PA_DELIVERABLE_UTILS : CHECK_PROJ_DLV_TXN_EXISTS : Printing Input parameters';
3280 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3281 l_debug_level3);
3282 Pa_Debug.WRITE(g_module_name,'p_project_id'||':'||p_project_id,
3283 l_debug_level3);
3284 END IF;
3285
3286 OPEN cur_proj_txn_exists;
3287 FETCH cur_proj_txn_exists INTO l_return_flag;
3288 IF cur_proj_txn_exists%FOUND THEN
3289 x_return_status := FND_API.G_RET_STS_ERROR;
3290 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
3291 p_msg_name => 'PA_STR_SETUP_CHANGE_ERR' );
3292 ELSE
3293 l_return_flag := 'N';
3294 END IF;
3295 CLOSE cur_proj_txn_exists;
3296
3297 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
3298 pa_debug.reset_curr_function;
3299 END IF ;
3300
3301 return l_return_flag;
3302 EXCEPTION
3303 WHEN OTHERS THEN
3304 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3305 x_msg_count := 1;
3306 x_msg_data := SQLERRM;
3307
3308 IF cur_proj_txn_exists%ISOPEN THEN
3309 CLOSE cur_proj_txn_exists;
3310 END IF;
3311
3312 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
3313 pa_debug.reset_curr_function;
3314 END IF ;
3315
3316 Fnd_Msg_Pub.add_exc_msg
3317 ( p_pkg_name => 'PA_DELIVERABLE_UTILS'
3318 , p_procedure_name => 'CHECK_PROJ_DLV_TXN_EXISTS'
3319 , p_error_text => x_msg_data );
3320 RAISE;
3321 END CHECK_PROJ_DLV_TXN_EXISTS;
3322
3323 FUNCTION GET_ASSOCIATED_DELIVERABLES
3324 (
3325 p_task_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
3326 ) RETURN VARCHAR2
3327 IS
3328
3329 TYPE l_tbl IS TABLE OF VARCHAR2(350) INDEX BY BINARY_INTEGER;
3330 l_name_number_tbl l_tbl ;
3331 l_count NUMBER ;
3332 l_string varchar2(5000); -- Modified size from 500 to 5000 for Bug#6153741
3333 l_meaning varchar2(80);
3334
3335 CURSOR c_associated_deliverables IS
3336 SELECT ppe. name||'('|| ppe.element_number||')' name_number
3337 FROM PA_PROJ_ELEMENTS ppe ,
3338 PA_OBJECT_RELATIONSHIPS obj
3339 WHERE ppe.object_type='PA_DELIVERABLES'
3340 AND ppe.proj_element_id = OBJ.object_id_to2
3341 AND OBJ.object_id_from2 =p_task_id
3342 AND OBJ.object_type_to = 'PA_DELIVERABLES'
3343 AND OBJ.object_type_from = 'PA_TASKS'
3344 AND OBJ.relationship_type = 'A'
3345 AND OBJ.relationship_subtype = 'TASK_TO_DELIVERABLE';
3346
3347 CURSOR c_lookup_meaning IS
3348 SELECT meaning
3349 FROM pa_lookups
3350 WHERE lookup_type = 'PA_DLV_MORE'
3351 AND lookup_code = 'MORE';
3352
3353 BEGIN
3354
3355 OPEN c_associated_deliverables;
3356 -- Bug Fix 5609470.
3357 -- Moved the following code to inside the code to avoid unnecessary executions as the cursor
3358 -- gets opened even when the meaning is not used.
3359 -- Hence moving the open fetch and close as well to inside just before the usage.
3360
3361 -- OPEN c_lookup_meaning;
3362 FETCH c_associated_deliverables BULK COLLECT INTO l_name_number_tbl;
3363 CLOSE c_associated_deliverables;
3364 IF nvl(l_name_number_tbl.LAST,0)>0 THEN
3365
3366 -- Bug Fix 5609470.
3367 -- Moved the following code to inside the code to avoid unnecessary executions as the cursor
3368 -- gets opened even when the meaning is not used.
3369 -- Hence moving the open fetch and close as well to inside just before the usage.
3370 -- FETCH c_lookup_meaning INTO l_meaning;
3371 -- CLOSE c_lookup_meaning;
3372
3373 FOR l_count in l_name_number_tbl.FIRST..l_name_number_tbl.LAST LOOP
3374 IF l_count = 1
3375 THEN
3376 l_string :=l_name_number_tbl(l_count);
3377 ELSE
3378 l_string := l_string||','||l_name_number_tbl(l_count);
3379 END IF;
3380 EXIT WHEN l_count >= 3 ;
3381 END LOOP ;
3382
3383 IF nvl(l_name_number_tbl.LAST,0)>3 THEN
3384
3385 -- Bug Fix 5609470.
3386 -- Moved the following code to here to avoid unnecessary executions as the cursor
3387 -- gets opened even when the meaning is not used.
3388 -- Hence moving the open fetch and close as well to inside just before the usage.
3389
3390 OPEN c_lookup_meaning;
3391 FETCH c_lookup_meaning INTO l_meaning;
3392 CLOSE c_lookup_meaning;
3393
3394 -- End of Bug Fix 5609470.
3395
3396 l_string := l_string||','|| l_meaning||'..';
3397 END IF ;
3398 RETURN l_string;
3399 ELSE
3400 RETURN NULL;
3401 END IF ;
3402 END GET_ASSOCIATED_DELIVERABLES;
3403
3404 /*===============================================================================================
3405 Deliverable Defaulting Logic for Copy From Project/Template Flow
3406 Case 1:
3407 Source Project/Template Dates
3408 |------------------|-----------------|----------------------|
3409 |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3410 |------------------|-----------------|----------------------|
3411 | NULL | NULL | NULL |
3412 |------------------|-----------------|----------------------|
3413
3414
3415 |------------------|-----------------|------------------|-----------------|----------------------|
3416 | QE Start Date | QE End Date |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3417 |------------------|-----------------|------------------|-----------------|----------------------|
3418 | NULL | NULL | NULL | NULL | NULL |
3419 |------------------|-----------------|------------------|-----------------|----------------------|
3420 | 01-APR-2002 | NULL | 01-APR-2002 | NULL | 01-APR-2002 |
3421 |------------------|-----------------|------------------|-----------------|----------------------|
3422 | 01-APR-2002 | 01-APR-2003 | 01-APR-2002 | 01-APR-2003 | 01-APR-2003 |
3423 |------------------|-----------------|------------------|-----------------|----------------------|
3424
3425 Case 2:
3426 |------------------|-----------------|----------------------|
3427 |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3428 |------------------|-----------------|----------------------|
3429 | 01-APR-2002 | NULL | NULL |
3430 |------------------|-----------------|----------------------|
3431
3432 |------------------|-----------------|------------------|-----------------|----------------------|
3433 | QE Start Date | QE End Date |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3434 |------------------|-----------------|------------------|-----------------|----------------------|
3435 | NULL | NULL | 01-APR-2002 | NULL | 01-APR-2002 |
3436 |------------------|-----------------|------------------|-----------------|----------------------|
3437 | 01-APR-2003 | NULL | 01-APR-2003 | NULL | 01-APR-2003 |
3438 |------------------|-----------------|------------------|-----------------|----------------------|
3439 | 01-APR-2003 | 01-APR-2004 | 01-APR-2003 | 01-APR-2004 | 01-APR-2004 |
3440 |------------------|-----------------|------------------|-----------------|----------------------|
3441
3442 Case 3:
3443 |------------------|-----------------|----------------------|
3444 |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3445 |------------------|-----------------|----------------------|
3446 | 01-APR-2002 | NULL | 15-APR-2002 |
3447 |------------------|-----------------|----------------------|
3448
3449 |------------------|-----------------|------------------|-----------------|----------------------|
3450 | QE Start Date | QE End Date |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3451 |------------------|-----------------|------------------|-----------------|----------------------|
3452 | NULL | NULL | 01-APR-2002 | NULL | 15-APR-2002 |
3453 |------------------|-----------------|------------------|-----------------|----------------------|
3454 | 01-APR-2003 | NULL | 01-APR-2003 | NULL | 15-APR-2003 |
3455 |------------------|-----------------|------------------|-----------------|----------------------|
3456 | 01-APR-2003 | 01-APR-2004 | 01-APR-2003 | 01-APR-2004 | 15-APR-2004 |
3457 |------------------|-----------------|------------------|-----------------|----------------------|
3458 | 01-APR-2003 | 12-APR-2003 | 01-APR-2003 | 12-APR-2003 | 12-APR-2003 |
3459 |------------------|-----------------|------------------|-----------------|----------------------|
3460
3461 Case 4:
3462 |------------------|-----------------|----------------------|
3463 |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3464 |------------------|-----------------|----------------------|
3465 | NULL | NULL | 15-APR-2002 |
3466 |------------------|-----------------|----------------------|
3467
3468 |------------------|-----------------|------------------|-----------------|----------------------|
3469 | QE Start Date | QE End Date |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3470 |------------------|-----------------|------------------|-----------------|----------------------|
3471 | NULL | NULL | NULL | NULL | 15-APR-2002 |
3472 |------------------|-----------------|------------------|-----------------|----------------------|
3473 | 01-APR-2003 | NULL | 01-APR-2003 | NULL | 01-APR-2003 |
3474 |------------------|-----------------|------------------|-----------------|----------------------|
3475 | 01-APR-2003 | 01-APR-2004 | 01-APR-2003 | 01-APR-2004 | 01-APR-2004 |
3476 |------------------|-----------------|------------------|-----------------|----------------------|
3477
3478 Case 5:
3479 |------------------|-----------------|----------------------|
3480 |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3481 |------------------|-----------------|----------------------|
3482 | 01-APR-2002 | 15-APR-2003 | 15-APR-2002 |
3483 |------------------|-----------------|----------------------|
3484
3485 |------------------|-----------------|------------------|-----------------|----------------------|
3486 | QE Start Date | QE End Date |ProjectStart Date |ProjectEnd Date |Deliverable Due Date |
3487 |------------------|-----------------|------------------|-----------------|----------------------|
3488 | NULL | NULL | 01-APR-2002 | 15-APR-2003 | 15-APR-2002 |
3489 |------------------|-----------------|------------------|-----------------|----------------------|
3490 | 01-APR-2003 | NULL | 01-APR-2003 | 15-APR-2004 | 15-APR-2003 |
3491 |------------------|-----------------|------------------|-----------------|----------------------|
3492 | 01-APR-2003 | 01-APR-2004 | 01-APR-2003 | 01-APR-2004 | 15-APR-2003 |
3493 |------------------|-----------------|------------------|-----------------|----------------------|
3494 | 01-APR-2003 | 12-APR-2003 | 01-APR-2003 | 12-APR-2003 | 12-APR-2003 |
3495 |------------------|-----------------|------------------|-----------------|----------------------|
3496
3497 =================================================================================================*/
3498
3499 FUNCTION GET_ADJUSTED_DATES
3500 (
3501 p_project_id IN pa_projects_all.project_id%TYPE
3502 ,p_dlv_due_date IN DATE
3503 ,p_delta IN NUMBER
3504 ) RETURN DATE
3505 IS
3506 CURSOR c_proj_dates IS
3507 SELECT start_date
3508 ,completion_date
3509 FROM pa_projects_all pa
3510 WHERE pa.project_id = p_project_id ;
3511 l_start_date DATE ;
3512 l_end_date DATE ;
3513 l_dlv_due_date DATE ;
3514 l_delta NUMBER ;
3515
3516 BEGIN
3517 OPEN c_proj_dates ;
3518 FETCH c_proj_dates into l_start_date,l_end_date ;
3519 CLOSE c_proj_dates ;
3520
3521 IF p_dlv_due_date IS NULL THEN
3522 IF l_end_date IS NOT NULL THEN
3523 l_dlv_due_date := l_end_date ;
3524 ELSE
3525 l_dlv_due_date := l_start_date ;
3526 END IF ;
3527
3528 ELSE
3529 l_delta := nvl(p_delta,0) ;
3530
3531 IF l_end_date IS NOT NULL THEN
3532 l_dlv_due_date := LEAST(l_end_date,p_dlv_due_date + l_delta);
3533 ELSE
3534 -- Bug#3601622
3535 -- GREATEST function returns null if any of the
3536 -- parameter is null. To avaid this added nvl on both side.
3537 l_dlv_due_date := GREATEST(nvl(l_start_date,p_dlv_due_date + l_delta),nvl(p_dlv_due_date + l_delta,l_start_date));
3538 END IF ;
3539
3540 -- 3493612 , new defaulted due date will be always between adjusted project start date and end date
3541 -- or it will be project end date
3542
3543 -- added below code to check new defaulted due date is between adjusted project start date and end date
3544 -- if it is not , defaulted due date will be set to adjusted project end date
3545
3546 IF l_end_date IS NOT NULL AND l_start_date IS NOT NULL THEN
3547 IF NOT (l_dlv_due_date > l_start_date AND l_dlv_due_date < l_end_date) THEN
3548 l_dlv_due_date := l_end_date;
3549 END IF;
3550 END IF;
3551
3552 -- 3493612
3553
3554 END IF ;
3555
3556 RETURN l_dlv_due_date ;
3557
3558 END GET_ADJUSTED_DATES;
3559
3560 FUNCTION IS_ITEM_BASED_DLV_EXISTS RETURN VARCHAR2
3561 IS
3562 CURSOR C IS
3563 SELECT 'Y' FROM DUAL
3564 WHERE EXISTS(SELECT 'Y'
3565 FROM pa_proj_elements ppe,
3566 pa_task_types ptt
3567 WHERE ppe.object_type = 'PA_DELIVERABLES'
3568 AND ppe.type_id = ptt.task_type_id
3569 AND ptt.task_type_class_code = 'ITEM'
3570 AND ptt.object_type = 'PA_DLVR_TYPES'
3571 ) ;
3572 l_dummy VARCHAR2(1) := 'N' ;
3573 BEGIN
3574 OPEN C ;
3575 FETCH C INTO l_dummy ;
3576 CLOSE C ;
3577 RETURN l_dummy ;
3578 END IS_ITEM_BASED_DLV_EXISTS ;
3579
3580 FUNCTION IS_BILLING_FUNCTION
3581 (
3582 p_action_version_id IN pa_proj_element_versions.element_version_id%TYPE
3583 )
3584 RETURN VARCHAR2
3585 IS
3586 CURSOR C IS
3587 SELECT 'Y'
3588 FROM DUAL
3589 WHERE EXISTS (Select 'Y'
3590 from pa_proj_elements ppe,
3591 pa_proj_element_versions pev
3592 where pev.element_version_id = p_action_version_id
3593 and ppe.proj_element_id = pev.proj_element_id
3594 and ppe.function_code = 'BILLING'
3595 ) ;
3596
3597 l_dummy VARCHAR2(1) := 'N' ;
3598 BEGIN
3599 OPEN C ;
3600 FETCH C INTO l_dummy ;
3601 CLOSE C ;
3602 RETURN l_dummy ;
3603 END IS_BILLING_FUNCTION ;
3604
3605 -- API name : Get_Project_Type_Class
3606 -- Type : Public procedure
3607 -- Pre-reqs : None
3608 -- Return Value : VARCHAR2
3609 -- Prameters
3610 -- p_project_id IN NUMBER REQUIRED
3611 -- x_return_status OUT VARCHAR2 REQUIRED
3612
3613 FUNCTION Get_Project_Type_Class(
3614 p_project_id NUMBER ,
3615 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3616 ) RETURN VARCHAR2 AS
3617
3618 CURSOR cur_projects_all
3619 IS
3620 SELECT ppt.project_type_class_code
3621 FROM pa_projects_all ppa, pa_project_types ppt
3622 WHERE ppa.project_id = p_project_id
3623 AND ppa.project_type = ppt.project_type
3624 AND ppa.org_id = ppt.org_id; -- 4363092 MOAC Changes
3625
3626 l_project_type_class_code VARCHAR2(30);
3627 BEGIN
3628 OPEN cur_projects_all;
3629 FETCH cur_projects_all INTO l_project_type_class_code;
3630 CLOSE cur_projects_all;
3631 RETURN l_project_type_class_code;
3632 x_return_status:= FND_API.G_RET_STS_SUCCESS;
3633 EXCEPTION
3634 WHEN OTHERS THEN
3635 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3636 END Get_Project_Type_Class;
3637
3638 -- SubProgram : IS_DLVR_ITEM_BASED
3639 -- Type : UTIL FUNCTION
3640 -- Purpose : This function will check whether the deliverable is item based,
3641 -- It takes the deliverable version id as input parameter
3642 -- Assumptions : None
3643 -- Parameter IN/OUT Type Required Description and Purpose
3644 -- --------------------------- ------------ ----------- --------- ---------------------------
3645
3646 -- p_deliverable_id IN VARCHAR2 N Deliverable Version Id
3647 FUNCTION IS_DLVR_ITEM_BASED
3648 (
3649 p_deliverable_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
3650 ) RETURN VARCHAR2
3651 IS
3652 CURSOR c_is_dlvr_item_based IS
3653 SELECT 'Y'
3654 FROM PA_TASK_TYPES
3655 WHERE TASK_TYPE_ID=(SELECT TYPE_ID
3656 FROM PA_PROJ_ELEMENTS PPE,
3657 PA_PROJ_ELEMENT_VERSIONS PEV
3658 WHERE PPE.PROJ_ELEMENT_ID= PEV.PROJ_ELEMENT_ID
3659 AND PEV.ELEMENT_VERSION_ID = p_deliverable_id
3660 AND PEV.OBJECT_TYPE='PA_DELIVERABLES'
3661 AND PPE.OBJECT_TYPE='PA_DELIVERABLES')
3662 AND TASK_TYPE_CLASS_CODE='ITEM'
3663 AND OBJECT_TYPE='PA_DLVR_TYPES';
3664 l_dummy VARCHAR2(1) := 'N' ;
3665 BEGIN
3666 OPEN c_is_dlvr_item_based;
3667 FETCH c_is_dlvr_item_based into l_dummy ;
3668 CLOSE c_is_dlvr_item_based;
3669 RETURN nvl(l_dummy,'N') ;
3670 END IS_DLVR_ITEM_BASED ;
3671
3672 -- 3454572 added function for TM Home Page
3673
3674 -- SubProgram : GET_DLVR_NAME_NUMBER
3675 -- Type : UTIL FUNCTION
3676 -- Purpose : This function will return deliverale name and number
3677 -- Assumptions : None
3678 -- Parameter IN/OUT Type Required Description and Purpose
3679 -- --------------------------- ------------ ----------- --------- ---------------------------
3680
3681 -- p_deliverable_id IN VARCHAR2 N Deliverable Version Id
3682
3683 FUNCTION GET_DLVR_NAME_NUMBER
3684 (
3685 p_deliverable_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
3686 ) RETURN VARCHAR2
3687 IS
3688 CURSOR c_dlv_name_number IS
3689 SELECT
3690 ppe.name || '(' || ppe.element_number || ')'
3691 FROM
3692 pa_proj_elements ppe
3693 ,pa_proj_element_versions pev
3694 WHERE
3695 pev.element_version_id = p_deliverable_id
3696 AND ppe.object_type = 'PA_DELIVERABLES'
3697 AND pev.object_type = 'PA_DELIVERABLES'
3698 AND ppe.proj_element_id = pev.proj_element_id
3699 AND ppe.project_id = pev.project_id;
3700
3701 l_dummy VARCHAR2(350) := NULL;
3702
3703 BEGIN
3704 OPEN c_dlv_name_number;
3705 FETCH c_dlv_name_number into l_dummy ;
3706 CLOSE c_dlv_name_number;
3707 RETURN l_dummy ;
3708
3709 END GET_DLVR_NAME_NUMBER;
3710
3711 -- SubProgram : GET_DLVR_NUMBER
3712 -- Type : UTIL FUNCTION
3713 -- Purpose : This function will return deliverale number
3714 -- Assumptions : None
3715 -- Parameter IN/OUT Type Required Description and Purpose
3716 -- --------------------------- ------------ ----------- --------- ---------------------------
3717
3718 -- p_deliverable_id IN VARCHAR2 N Deliverable Version Id
3719
3720 FUNCTION GET_DLVR_NUMBER
3721 (
3722 p_deliverable_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
3723 ) RETURN VARCHAR2
3724 IS
3725 CURSOR c_dlv_name_number IS
3726 SELECT
3727 ppe.ELEMENT_NUMBER
3728 FROM
3729 pa_proj_elements ppe
3730 ,pa_proj_element_versions pev
3731 WHERE
3732 pev.element_version_id = p_deliverable_id
3733 AND ppe.object_type = 'PA_DELIVERABLES'
3734 AND pev.object_type = 'PA_DELIVERABLES'
3735 AND ppe.proj_element_id = pev.proj_element_id
3736 AND ppe.project_id = pev.project_id;
3737
3738 l_dummy VARCHAR2(350) := NULL;
3739
3740 BEGIN
3741 OPEN c_dlv_name_number;
3742 FETCH c_dlv_name_number into l_dummy ;
3743 CLOSE c_dlv_name_number;
3744 RETURN l_dummy ;
3745
3746 END GET_DLVR_NUMBER;
3747
3748 -- 3454572 end
3749 -- 3442451 added for deliverable security implementation
3750
3751 -- SubProgram : IS_DLVR_OWNER
3752 -- Type : UTIL FUNCTION
3753 -- Purpose : This function will return Y, if user is deliverable owner
3754 -- Assumptions : None
3755 -- Parameter IN/OUT Type Required Description and Purpose
3756 -- --------------------------- ------------ ----------- --------- ---------------------------
3757 -- p_deliverable_id IN VARCHAR2 Y Deliverable Version Id
3758 -- p_user_id IN NUMBER Y User Id
3759
3760
3761 FUNCTION IS_DLVR_OWNER
3762 (
3763 p_deliverable_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
3764 ,p_user_id IN NUMBER
3765 ) RETURN VARCHAR2
3766 IS
3767 CURSOR c_dlvr_owner IS
3768 select
3769 ppe.manager_person_id
3770 from
3771 pa_proj_elements ppe,
3772 pa_proj_element_versions pev
3773 where
3774 pev.element_version_id = p_deliverable_id
3775 and pev.object_type = 'PA_DELIVERABLES'
3776 and ppe.object_type = 'PA_DELIVERABLES'
3777 and pev.proj_element_id = ppe.proj_element_id
3778 and pev.project_id = ppe.project_id
3779 and pev.object_type = pev.object_type;
3780
3781 l_is_owner VARCHAR2(1) := 'N';
3782 l_dlvr_owner_id NUMBER := NULL;
3783 l_person_id NUMBER := NULL;
3784
3785 BEGIN
3786
3787 l_person_id := PA_UTILS.GetEmpIdFromUser(p_user_id);
3788
3789 OPEN c_dlvr_owner;
3790 FETCH c_dlvr_owner into l_dlvr_owner_id ;
3791 CLOSE c_dlvr_owner;
3792
3793 IF l_dlvr_owner_id = l_person_id THEN
3794 l_is_owner := 'Y';
3795 END IF;
3796 RETURN l_is_owner;
3797
3798 END IS_DLVR_OWNER;
3799
3800 -- 3442451 end
3801 /* ==============3435905 : FP M : Deliverables Changes For AMG - Start * =========================*/
3802
3803
3804 -- SubProgram : VALIDATE_DELIVERABLE
3805 -- Type : UTIL FUNCTION
3806 -- Purpose : This function will validate the IDs passed from AMG apis. It will be called
3807 -- only when context is AMG, for both Create and Update
3808 -- Assumptions : None
3809
3810 Procedure Validate_Deliverable
3811 (
3812 p_deliverable_id IN NUMBER
3813 , p_deliverable_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
3814 , p_dlvr_number IN PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
3815 , p_dlvr_name IN PA_PROJ_ELEMENTS.NAME%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
3816 , px_dlvr_owner_id IN OUT NOCOPY PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE --File.Sql.39 bug 4440895
3817 , p_dlvr_owner_name IN VARCHAR2 := NULL
3818 , p_dlvr_type_id IN PA_PROJ_ELEMENTS.TYPE_ID%TYPE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3819 , px_actual_finish_date IN OUT NOCOPY DATE --File.Sql.39 bug 4440895
3820 , px_progress_weight IN OUT NOCOPY PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE --File.Sql.39 bug 4440895
3821 , px_status_code IN OUT NOCOPY Pa_task_types.initial_status_code%TYPE --File.Sql.39 bug 4440895
3822 , p_carrying_out_org_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3823 , p_project_id IN PA_PROJ_ELEMENTS.PROJECT_ID%TYPE
3824 , p_task_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
3825 , p_calling_mode IN VARCHAR2 := 'INSERT'
3826 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3827 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3828 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3829 )
3830 IS
3831 l_api_name CONSTANT VARCHAR2(100) := 'VALIDATE_DELIVERABLE';
3832 l_debug_mode VARCHAR2(1);
3833 l_msg_data VARCHAR2(2000);
3834
3835 l_dlvr_owner_id PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE;
3836 --added for Bug: 4537865
3837 l_new_dlvr_owner_id PA_PROJ_ELEMENTS.MANAGER_PERSON_ID%TYPE;
3838 l_new_carrying_out_org_id PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE;
3839 --added for Bug: 4537865
3840 l_carrying_out_org_id PA_PROJ_ELEMENTS.CARRYING_OUT_ORGANIZATION_ID%TYPE;
3841 l_dlvr_prg_enabled VARCHAR2(1) := NULL;
3842 l_dlvr_action_enabled VARCHAR2(1) := NULL;
3843 l_status_code PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL;
3844 l_status_code_valid VARCHAR2(1) := NULL;
3845
3846 l_project_number Pa_Projects_All.Segment1%TYPE;
3847 l_task_number Pa_Proj_Elements.Name%TYPE;
3848
3849 BEGIN
3850
3851 x_msg_count := 0;
3852 x_return_status := FND_API.G_RET_STS_SUCCESS;
3853 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3854
3855 IF l_debug_mode = 'Y' THEN
3856 PA_DEBUG.set_curr_function( p_function => 'VALIDATE_DELIVERABLE', p_debug_mode => l_debug_mode );
3857 END IF;
3858
3859 IF px_dlvr_owner_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
3860 l_dlvr_owner_id := NULL;
3861 ELSE
3862 l_dlvr_owner_id := px_dlvr_owner_id;
3863 END IF;
3864
3865 IF p_carrying_out_org_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
3866 l_carrying_out_org_id := NULL;
3867 ELSE
3868 l_carrying_out_org_id := p_carrying_out_org_id;
3869 END IF;
3870
3871 -- Fetching Task Name , Project Name to use as token in Error Messages.
3872 IF (p_task_id IS NOT NULL AND p_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) THEN
3873 SELECT name INTO l_task_number
3874 FROM Pa_Proj_Elements
3875 WHERE proj_element_id = p_task_id;
3876 ELSE
3877 l_task_number := null;
3878 END IF;
3879
3880 SELECT segment1 INTO l_project_number
3881 FROM Pa_Projects_All
3882 WHERE project_id = p_project_id;
3883
3884 IF l_debug_mode = 'Y' THEN
3885 Pa_Debug.WRITE(g_module_name, 'token values proj ['||l_Project_Number||'] task ['||l_task_Number||']',l_debug_level3);
3886 END IF;
3887
3888 -- Validating deliverable_name - not null
3889 IF p_dlvr_name IS NULL
3890 OR p_dlvr_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
3891
3892 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3893 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_NAME_MISSING') ;
3894 PA_UTILS.ADD_MESSAGE
3895 (p_app_short_name => 'PA',
3896 p_msg_name => 'PA_DLVR_VALID_ERR',
3897 p_token1 => 'PROJECT',
3898 p_value1 => l_project_number,
3899 p_token2 => 'TASK',
3900 p_value2 => l_task_number,
3901 p_token3 => 'DLVR_REFERENCE',
3902 p_value3 => p_deliverable_reference,
3903 p_token4 => 'MESSAGE',
3904 p_value4 => l_err_message
3905 );
3906 END IF;
3907 x_return_status := FND_API.G_RET_STS_ERROR;
3908 RAISE FND_API.G_EXC_ERROR;
3909 END IF;
3910
3911 IF l_debug_mode = 'Y' THEN
3912 Pa_Debug.WRITE(g_module_name,' validating deliverable name '||p_dlvr_name, l_debug_level3);
3913 END IF;
3914
3915 -- Validating deliverable_short_name - not null
3916 IF p_dlvr_number IS NULL
3917 OR p_dlvr_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
3918
3919 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3920 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_NUMBER_MISSING') ;
3921 PA_UTILS.ADD_MESSAGE
3922 (p_app_short_name => 'PA',
3923 p_msg_name => 'PA_DLVR_VALID_ERR',
3924 p_token1 => 'PROJECT',
3925 p_value1 => l_project_number,
3926 p_token2 => 'TASK',
3927 p_value2 => l_task_number,
3928 p_token3 => 'DLVR_REFERENCE',
3929 p_value3 => p_deliverable_reference,
3930 p_token4 => 'MESSAGE',
3931 p_value4 => l_err_message
3932 );
3933 END IF;
3934 x_return_status := FND_API.G_RET_STS_ERROR;
3935 RAISE FND_API.G_EXC_ERROR;
3936 END IF;
3937
3938 IF l_debug_mode = 'Y' THEN
3939 Pa_Debug.WRITE(g_module_name,' validating deliverable short name '||p_dlvr_number, l_debug_level3);
3940 END IF;
3941
3942 -- Validating Deliverable Type Id - not null, valid value
3943 IF p_dlvr_type_id IS NULL
3944 OR p_dlvr_type_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
3945
3946 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3947 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_TYPE_MISSING') ;
3948 PA_UTILS.ADD_MESSAGE
3949 (p_app_short_name => 'PA',
3950 p_msg_name => 'PA_DLVR_VALID_ERR',
3951 p_token1 => 'PROJECT',
3952 p_value1 => l_project_number,
3953 p_token2 => 'TASK',
3954 p_value2 => l_task_number,
3955 p_token3 => 'DLVR_REFERENCE',
3956 p_value3 => p_deliverable_reference,
3957 p_token4 => 'MESSAGE',
3958 p_value4 => l_err_message
3959 );
3960 END IF;
3961 x_return_status := FND_API.G_RET_STS_ERROR;
3962 RAISE FND_API.G_EXC_ERROR;
3963
3964 ELSE -- Deliverable type is not null, checking for valid value
3965 IF (Pa_Deliverable_Utils.IS_DLV_TYPE_ID_VALID(p_dlvr_type_id) = 'N') THEN
3966 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3967 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_TYPE_INVALID') ;
3968 PA_UTILS.ADD_MESSAGE
3969 (p_app_short_name => 'PA',
3970 p_msg_name => 'PA_DLVR_VALID_ERR',
3971 p_token1 => 'PROJECT',
3972 p_value1 => l_project_number,
3973 p_token2 => 'TASK',
3974 p_value2 => l_task_number,
3975 p_token3 => 'DLVR_REFERENCE',
3976 p_value3 => p_deliverable_reference,
3977 p_token4 => 'MESSAGE',
3978 p_value4 => l_err_message
3979 );
3980 END IF;
3981 x_return_status := FND_API.G_RET_STS_ERROR;
3982 RAISE FND_API.G_EXC_ERROR;
3983 END IF;
3984 END IF;
3985
3986 IF l_debug_mode = 'Y' THEN
3987 Pa_Debug.WRITE(g_module_name,' validating deliverable type'||p_dlvr_type_id, l_debug_level3);
3988 END IF;
3989
3990 -- Validating Deliverable Owner Id - valid value
3991 IF (l_dlvr_owner_id IS NOT NULL ) THEN
3992 Pa_Tasks_Maint_Utils.CHECK_TASK_MGR_NAME_OR_ID (
3993 p_task_mgr_name => p_dlvr_owner_name
3994 ,p_task_mgr_id => l_dlvr_owner_id
3995 ,p_project_id => p_project_id
3996 ,p_check_id_flag => 'Y'
3997 ,p_calling_module => 'AMG'
3998 -- ,x_task_mgr_id => l_dlvr_owner_id * commenented for bug: 4537865
3999 ,x_task_mgr_id => l_new_dlvr_owner_id -- added for bug: 4537865
4000 ,x_return_status => x_return_status
4001 ,x_error_msg_code => l_msg_data );
4002 IF l_debug_mode = 'Y' THEN
4003 Pa_Debug.WRITE(g_module_name,' validated owner id'||l_dlvr_owner_id||x_return_status , l_debug_level3);
4004 END IF;
4005
4006 -- added for bug: 4537865
4007 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4008 l_dlvr_owner_id := l_new_dlvr_owner_id;
4009 END IF;
4010 -- added for bug: 4537865
4011
4012 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4014 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
4015 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_OWNER_INVALID') ;
4016 PA_UTILS.ADD_MESSAGE
4017 (p_app_short_name => 'PA',
4018 p_msg_name => 'PA_DLVR_VALID_ERR',
4019 p_token1 => 'PROJECT',
4020 p_value1 => l_project_number,
4021 p_token2 => 'TASK',
4022 p_value2 => l_task_number,
4023 p_token3 => 'DLVR_REFERENCE',
4024 p_value3 => p_deliverable_reference,
4025 p_token4 => 'MESSAGE',
4026 p_value4 => l_err_message
4027 );
4028 x_return_status := FND_API.G_RET_STS_ERROR;
4029 RAISE FND_API.G_EXC_ERROR;
4030 END IF;
4031 END IF;
4032
4033 -- Validating Status Code - valid value
4034 l_status_code_valid := Pa_Deliverable_Utils.IS_STATUS_CODE_VALID(px_status_code, p_calling_mode);
4035
4036 IF l_debug_mode = 'Y' THEN
4037 Pa_Debug.WRITE(g_module_name,' validated status code ['||px_status_code||'] outcome ['||l_status_code_valid||']' , l_debug_level3);
4038 END IF;
4039
4040 IF (px_status_code IS NOT NULL and l_status_code_valid = 'N') THEN
4041 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4042 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_STATUS_INVALID') ;
4043 PA_UTILS.ADD_MESSAGE
4044 (p_app_short_name => 'PA',
4045 p_msg_name => 'PA_DLVR_VALID_ERR',
4046 p_token1 => 'PROJECT',
4047 p_value1 => l_project_number,
4048 p_token2 => 'TASK',
4049 p_value2 => l_task_number,
4050 p_token3 => 'DLVR_REFERENCE',
4051 p_value3 => p_deliverable_reference,
4052 p_token4 => 'MESSAGE',
4053 p_value4 => l_err_message
4054 );
4055
4056 END IF;
4057 x_return_status := FND_API.G_RET_STS_ERROR;
4058 RAISE FND_API.G_EXC_ERROR;
4059 END IF;
4060
4061 -- Validating Carrying Out Org Id - valid value
4062 IF (l_carrying_out_org_id IS NOT NULL ) THEN
4063 Pa_Hr_Org_Utils.CHECK_ORGNAME_OR_ID
4064 ( p_organization_id => l_carrying_out_org_id
4065 ,p_organization_name => NULL
4066 ,p_check_id_flag => 'Y'
4067 -- ,x_organization_id => l_carrying_out_org_id * commented for Bug: 4537685
4068 ,x_organization_id => l_new_carrying_out_org_id -- added for Bug: 4537685
4069 ,x_return_status => x_return_status
4070 ,x_error_msg_code => l_msg_data);
4071 IF l_debug_mode = 'Y' THEN
4072 Pa_Debug.WRITE(g_module_name,' validating carrying out org'||l_carrying_out_org_id||x_return_status, l_debug_level3);
4073 END IF;
4074
4075 -- added for Bug: 4537685
4076 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
4077 l_carrying_out_org_id := l_new_carrying_out_org_id;
4078 END IF;
4079 -- added for Bug: 4537685
4080
4081 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4082 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4083 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
4084 l_err_message := FND_MESSAGE.GET_STRING('PA','DLVR_CARRYING_ORG_INVALID') ;
4085 PA_UTILS.ADD_MESSAGE
4086 (p_app_short_name => 'PA',
4087 p_msg_name => 'PA_DLVR_VALID_ERR',
4088 p_token1 => 'PROJECT',
4089 p_value1 => l_project_number,
4090 p_token2 => 'TASK',
4091 p_value2 => l_task_number,
4092 p_token3 => 'DLVR_REFERENCE',
4093 p_value3 => p_deliverable_reference,
4094 p_token4 => 'MESSAGE',
4095 p_value4 => l_err_message
4096 );
4097 x_return_status := FND_API.G_RET_STS_ERROR;
4098 RAISE FND_API.G_EXC_ERROR;
4099 END IF;
4100 END IF;
4101
4102 Pa_Deliverable_Utils.Progress_Enabled_Validation
4103 (
4104 p_deliverable_id => p_deliverable_id
4105 , p_project_id => p_project_id
4106 , p_dlvr_type_id => p_dlvr_type_id
4107 , px_actual_finish_date => px_actual_finish_date
4108 , px_progress_weight => px_progress_weight
4109 , px_status_code => px_status_code
4110 , p_calling_Mode => p_calling_Mode
4111 ) ;
4112
4113 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
4114 pa_debug.reset_curr_function;
4115 END IF ;
4116
4117 EXCEPTION
4118 WHEN FND_API.G_EXC_ERROR THEN
4119 x_return_status := FND_API.G_RET_STS_ERROR;
4120 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
4121 pa_debug.reset_curr_function;
4122 END IF ;
4123
4124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4126 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
4127 pa_debug.reset_curr_function;
4128 END IF ;
4129
4130 WHEN OTHERS THEN
4131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4132
4133 IF l_debug_mode = 'Y' THEN --Added for bug 4945876
4134 pa_debug.reset_curr_function;
4135 END IF ;
4136
4137 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4138 FND_MSG_PUB.add_exc_msg
4139 ( p_pkg_name => g_module_name
4140 , p_procedure_name => l_api_name );
4141 END IF;
4142
4143 END Validate_Deliverable ;
4144
4145 -- SubProgram : IS_DLVR_TYPE_ID_VALID
4146 -- Type : UTIL FUNCTION
4147 -- Purpose : This function will check whether the deliverable type id is valid
4148 -- Assumptions : None
4149
4150 FUNCTION IS_DLV_TYPE_ID_VALID
4151 (
4152 p_deliverable_type_id IN NUMBER
4153 ) RETURN VARCHAR2
4154 IS
4155
4156 l_return_status varchar2(1);
4157 l_dummy varchar2(1);
4158
4159 CURSOR c_dlvr_type_id_exists IS
4160 SELECT 'X'
4161 FROM PA_TASK_TYPES
4162 WHERE TASK_TYPE_ID = p_deliverable_type_id
4163 AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active, sysdate)
4164 AND OBJECT_TYPE='PA_DLVR_TYPES';
4165
4166 BEGIN
4167 OPEN c_dlvr_type_id_exists;
4168 FETCH c_dlvr_type_id_exists into l_dummy ;
4169
4170 IF c_dlvr_type_id_exists%found THEN
4171 l_return_status:='Y';
4172 ELSE
4173 l_return_status:='N';
4174 END IF;
4175
4176 CLOSE c_dlvr_type_id_exists;
4177
4178 return l_return_status;
4179
4180 END IS_DLV_TYPE_ID_VALID;
4181
4182 -- SubProgram : get_deliverable_version_id
4183 -- Type : UTIL FUNCTION
4184 -- Purpose : This function returns the deliverable version id of a deliverable
4185 -- Assumptions : None
4186
4187 FUNCTION get_deliverable_version_id
4188 (
4189 p_deliverable_id IN NUMBER ,
4190 p_structure_version_id IN NUMBER ,
4191 p_project_id IN NUMBER
4192 ) RETURN NUMBER
4193 IS
4194
4195 l_dummy pa_proj_element_versions.element_version_id%TYPE;
4196
4197 CURSOR c_dlvr_version IS
4198 SELECT
4199 pev.element_version_id
4200 FROM
4201 pa_proj_elements ppe
4202 ,pa_proj_element_versions pev
4203 WHERE
4204 pev.proj_element_id = p_deliverable_id
4205 AND ppe.object_type = 'PA_DELIVERABLES'
4206 AND pev.object_type = 'PA_DELIVERABLES'
4207 AND ppe.proj_element_id = pev.proj_element_id
4208 AND ppe.project_id = pev.project_id
4209 AND ppe.project_id = p_project_id
4210 AND pev.parent_structure_version_id = nvl(p_structure_version_id, pev.parent_structure_version_id);
4211
4212 BEGIN
4213 OPEN c_dlvr_version;
4214 FETCH c_dlvr_version into l_dummy ;
4215
4216 IF c_dlvr_version%found THEN
4217 CLOSE c_dlvr_version;
4218 RETURN l_dummy;
4219 ELSE
4220 CLOSE c_dlvr_version;
4221 RETURN NULL;
4222 END IF;
4223
4224 END get_deliverable_version_id;
4225
4226
4227 -- SubProgram : GET_DLVR_TASK_ASSCN_ID
4228 -- Type : UTIL FUNCTION
4229 -- Purpose : This function returns the object_relationship_id of task and deliverable association
4230 -- Assumptions : None
4231
4232 FUNCTION GET_DLVR_TASK_ASSCN_ID
4233 (
4234 p_deliverable_id IN NUMBER ,
4235 p_task_id IN NUMBER
4236 ) RETURN NUMBER
4237 IS
4238
4239 l_dummy pa_object_relationships.object_relationship_Id%TYPE;
4240
4241 CURSOR c_dlvr_task_asscn IS
4242 SELECT obj.object_relationship_id
4243 FROM PA_OBJECT_RELATIONSHIPS obj
4244 WHERE OBJ.object_id_from2 = p_task_id
4245 AND OBJ.object_id_to2 =p_deliverable_id
4246 AND OBJ.object_type_to = 'PA_DELIVERABLES'
4247 AND OBJ.object_type_from = 'PA_TASKS'
4248 AND OBJ.relationship_type = 'A'
4249 AND OBJ.relationship_subtype = 'TASK_TO_DELIVERABLE';
4250
4251 BEGIN
4252 OPEN c_dlvr_task_asscn;
4253 FETCH c_dlvr_task_asscn into l_dummy ;
4254
4255 IF c_dlvr_task_asscn%found THEN
4256 CLOSE c_dlvr_task_asscn;
4257 RETURN l_dummy;
4258 ELSE
4259 CLOSE c_dlvr_task_asscn;
4260 RETURN NULL;
4261 END IF;
4262
4263 END GET_DLVR_TASK_ASSCN_ID;
4264
4265 -- SubProgram : IS_STATUS_CODE_VALID
4266 -- Type : UTIL FUNCTION
4267 -- Purpose : This function will check whether the status code is valid
4268 -- Assumptions : None
4269
4270 FUNCTION IS_STATUS_CODE_VALID
4271 (
4272 p_status_code IN VARCHAR2
4273 , p_calling_mode IN VARCHAR2 := 'INSERT'
4274 ) RETURN VARCHAR2
4275 IS
4276
4277 l_return_status varchar2(1);
4278 l_dummy varchar2(1);
4279
4280 CURSOR c_status_code_exists IS
4281 SELECT 'X'
4282 FROM PA_PROJECT_STATUSES
4283 WHERE UPPER(project_status_code) = UPPER(p_status_code)
4284 AND UPPER(project_system_status_code) = DECODE(p_calling_mode, 'INSERT', 'DLVR_NOT_STARTED',project_system_status_code)
4285 AND status_type = 'DELIVERABLE'
4286 AND sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active,sysdate);
4287
4288 BEGIN
4289 OPEN c_status_code_exists;
4290 FETCH c_status_code_exists into l_dummy ;
4291 IF c_status_code_exists%found THEN
4292 l_return_status:='Y';
4293 ELSE
4294 l_return_status:='N';
4295 END IF;
4296 CLOSE c_status_code_exists;
4297
4298 return l_return_status;
4299
4300 END IS_STATUS_CODE_VALID;
4301
4302 --====================================================================================
4303 --Name: is_dlvr_reference_unique
4304 --Type: procedure
4305 --Description: Checking if deliverable_reference is passed or not. If passed is
4306 -- unique or not
4307 --
4308 --Called subprograms: none
4309 --
4310 --
4311 --
4312 --History:
4313 -- 19-AUG-1996 Puneet Created
4314 --
4315 PROCEDURE is_dlvr_reference_unique
4316 (p_deliverable_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4317 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4318 ,x_unique_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4319 ,x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
4320
4321 IS
4322
4323 CURSOR l_dlvr_ref_csr
4324 IS
4325 SELECT elem.proj_element_id, proj.segment1
4326 FROM pa_proj_elements elem, pa_projects_all proj
4327 where pm_source_reference = p_deliverable_reference
4328 and elem.project_id = p_project_id
4329 and elem.project_id = proj.project_id
4330 and object_type = 'PA_DELIVERABLES';
4331
4332 l_api_name CONSTANT VARCHAR2(30) := 'is_dlvr_reference_unique';
4333 l_deliverable_id NUMBER ;
4334 l_dummy VARCHAR2(1);
4335
4336 l_err_message Fnd_New_Messages.Message_text%TYPE; -- for AMG message
4337 l_project_number Pa_Projects_All.Segment1%TYPE;
4338
4339
4340 BEGIN
4341
4342 x_return_status := FND_API.G_RET_STS_SUCCESS;
4343
4344 IF p_deliverable_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4345 AND p_deliverable_reference IS NOT NULL
4346 THEN
4347 OPEN l_dlvr_ref_csr;
4348 FETCH l_dlvr_ref_csr INTO l_deliverable_id, l_project_number;
4349
4350 IF l_dlvr_ref_csr%FOUND
4351 THEN
4352 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4353 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_DUP_DLVR_REFERENCE') ;
4354 PA_UTILS.ADD_MESSAGE
4355 (p_app_short_name => 'PA',
4356 p_msg_name => 'PA_DLVR_VALID_ERR',
4357 p_token1 => 'PROJECT',
4358 p_value1 => l_project_number,
4359 p_token2 => 'TASK',
4360 p_value2 => null,
4361 p_token3 => 'DLVR_REFERENCE',
4362 p_value3 => p_deliverable_reference,
4363 p_token4 => 'MESSAGE',
4364 p_value4 => l_err_message
4365 );
4366 END IF;
4367 x_unique_flag := 'N';
4368 --RAISE FND_API.G_EXC_ERROR; Commented bug 3651538 as Exception should not be raised as
4369 -- it indicates that dlvr is to be updated.
4370 ELSE --l_dlvr_ref_csr%FOUND
4371 x_unique_flag := 'Y';
4372 END IF; --l_dlvr_ref_csr%FOUND
4373 CLOSE l_dlvr_ref_csr;
4374
4375 ELSE
4376
4377 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4378 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_DLVR_REF_AND_ID_MISSING') ;
4379 PA_UTILS.ADD_MESSAGE
4380 (p_app_short_name => 'PA',
4381 p_msg_name => 'PA_DLVR_VALID_ERR',
4382 p_token1 => 'PROJECT',
4383 p_value1 => l_project_number,
4384 p_token2 => 'TASK',
4385 p_value2 => null,
4386 p_token3 => 'DLVR_REFERENCE',
4387 p_value3 => p_deliverable_reference,
4388 p_token4 => 'MESSAGE',
4389 p_value4 => l_err_message
4390 );
4391 END IF;
4392 x_unique_flag := Null;
4393 RAISE FND_API.G_EXC_ERROR;
4394
4395 END IF; -- If p_deliverable_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4396
4397
4398 EXCEPTION
4399 WHEN FND_API.G_EXC_ERROR THEN
4400 x_return_status := FND_API.G_RET_STS_ERROR;
4401
4402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4404
4405 WHEN OTHERS THEN
4406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4407 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4408 FND_MSG_PUB.add_exc_msg
4409 ( p_pkg_name => G_MODULE_NAME
4410 , p_procedure_name => l_api_name );
4411 END IF;
4412
4413 END is_dlvr_reference_unique;
4414
4415 --====================================================================================
4416 --Name: convert_pm_dlvrref_to_id
4417 --Type: Procedure
4418 --Description: This procedure can be used to converse
4419 -- an incoming deliverable reference to
4420 -- a deliverable ID.
4421 --
4422 --Called subprograms: none
4423 --
4424 --
4425 --
4426 --History:
4427 -- 19-AUG-1996 Puneet Created
4428 --
4429 PROCEDURE Convert_pm_dlvrref_to_id
4430 (p_deliverable_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4431 ,p_deliverable_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4432 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4433 ,p_out_deliverable_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4434 ,p_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
4435
4436 IS
4437
4438 CURSOR l_dlvr_id_csr
4439 IS
4440 SELECT 'X'
4441 FROM pa_proj_elements
4442 where proj_element_id = p_deliverable_id
4443 and project_id = p_project_id
4444 and object_type = 'PA_DELIVERABLES';
4445
4446 CURSOR l_dlvr_ref_csr
4447 IS
4448 SELECT proj_element_id
4449 FROM pa_proj_elements
4450 where pm_source_reference = p_deliverable_reference
4451 and project_id = p_project_id
4452 and object_type = 'PA_DELIVERABLES';
4453
4454 CURSOR proj_num
4455 IS
4456 SELECT segment1
4457 FROM pa_projects_all
4458 WHERE project_id = p_project_id;
4459
4460 l_api_name CONSTANT VARCHAR2(30) := 'Convert_pm_dlvrref_to_id';
4461 l_deliverable_id NUMBER ;
4462 l_dummy VARCHAR2(1);
4463 l_project_number Pa_Projects_All.Segment1%TYPE;
4464
4465 BEGIN
4466
4467 p_return_status := FND_API.G_RET_STS_SUCCESS;
4468
4469 OPEN proj_num;
4470 FETCH proj_num INTO l_project_number;
4471 CLOSE proj_num;
4472
4473 IF p_deliverable_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4474 AND p_deliverable_id IS NOT NULL
4475 THEN
4476
4477 --check validity of this ID
4478 OPEN l_dlvr_id_csr;
4479 FETCH l_dlvr_id_csr INTO l_dummy;
4480
4481 IF l_dlvr_id_csr%NOTFOUND
4482 THEN
4483 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4484 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_DLVR_ID_INVALID') ;
4485 PA_UTILS.ADD_MESSAGE
4486 (p_app_short_name => 'PA',
4487 p_msg_name => 'PA_DLVR_VALID_ERR',
4488 p_token1 => 'PROJECT',
4489 p_value1 => l_project_number,
4490 p_token2 => 'TASK',
4491 p_value2 => null,
4492 p_token3 => 'DLVR_REFERENCE',
4493 p_value3 => p_deliverable_reference,
4494 p_token4 => 'MESSAGE',
4495 p_value4 => l_err_message
4496 );
4497 END IF;
4498 CLOSE l_dlvr_id_csr;
4499 RAISE FND_API.G_EXC_ERROR;
4500 END IF;
4501
4502 CLOSE l_dlvr_id_csr;
4503 p_out_deliverable_id := p_deliverable_id;
4504
4505 ELSIF p_deliverable_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4506 AND p_deliverable_reference IS NOT NULL
4507 THEN
4508
4509 --check validity of this reference
4510 OPEN l_dlvr_ref_csr;
4511 FETCH l_dlvr_ref_csr INTO l_deliverable_id;
4512
4513 IF l_dlvr_ref_csr%NOTFOUND
4514 THEN
4515 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4516 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_DLVR_REF_INVALID') ;
4517 PA_UTILS.ADD_MESSAGE
4518 (p_app_short_name => 'PA',
4519 p_msg_name => 'PA_DLVR_VALID_ERR',
4520 p_token1 => 'PROJECT',
4521 p_value1 => l_project_number,
4522 p_token2 => 'TASK',
4523 p_value2 => null,
4524 p_token3 => 'DLVR_REFERENCE',
4525 p_value3 => p_deliverable_reference,
4526 p_token4 => 'MESSAGE',
4527 p_value4 => l_err_message
4528 );
4529 END IF;
4530 CLOSE l_dlvr_ref_csr;
4531 RAISE FND_API.G_EXC_ERROR;
4532 END IF;
4533
4534 CLOSE l_dlvr_ref_csr;
4535 p_out_deliverable_id := l_deliverable_id;
4536 ELSE
4537 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4538 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_DLVR_REF_AND_ID_MISSING') ;
4539 PA_UTILS.ADD_MESSAGE
4540 (p_app_short_name => 'PA',
4541 p_msg_name => 'PA_DLVR_VALID_ERR',
4542 p_token1 => 'PROJECT',
4543 p_value1 => l_project_number,
4544 p_token2 => 'DLVR_REFERENCE',
4545 p_value2 => p_deliverable_reference,
4546 p_token3 => 'MESSAGE',
4547 p_value3 => l_err_message
4548 );
4549 END IF;
4550 RAISE FND_API.G_EXC_ERROR;
4551
4552 END IF; -- If p_deliverable_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4553
4554 EXCEPTION
4555 WHEN FND_API.G_EXC_ERROR THEN
4556 p_return_status := FND_API.G_RET_STS_ERROR;
4557
4558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4559 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4560
4561 WHEN OTHERS THEN
4562 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4563 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4564 FND_MSG_PUB.add_exc_msg
4565 ( p_pkg_name => G_MODULE_NAME
4566 , p_procedure_name => l_api_name );
4567 END IF;
4568
4569 END Convert_pm_dlvrref_to_id;
4570
4571 -- SubProgram : get_action_version_id
4572 -- Type : UTIL FUNCTION
4573 -- Purpose : This function returns the action version id of an action
4574 -- Assumptions : None
4575
4576 FUNCTION get_action_version_id
4577 (
4578 p_action_id IN NUMBER ,
4579 p_structure_version_id IN NUMBER ,
4580 p_project_id IN NUMBER
4581 ) RETURN NUMBER
4582 IS
4583
4584 l_dummy pa_proj_element_versions.element_version_id%TYPE;
4585
4586 CURSOR c_action_version IS
4587 SELECT
4588 pev.element_version_id
4589 FROM
4590 pa_proj_elements ppe
4591 ,pa_proj_element_versions pev
4592 WHERE
4593 pev.proj_element_id = p_action_id
4594 AND ppe.object_type = 'PA_ACTIONS'
4595 AND pev.object_type = 'PA_ACTIONS'
4596 AND ppe.proj_element_id = pev.proj_element_id
4597 AND ppe.project_id = pev.project_id
4598 AND ppe.project_id = p_project_id
4599 AND nvl(pev.parent_structure_version_id,-99) = nvl(nvl(p_structure_version_id, pev.parent_structure_version_id),-99);
4600
4601 BEGIN
4602 OPEN c_action_version;
4603 FETCH c_action_version into l_dummy ;
4604
4605 IF c_action_version%found THEN
4606 CLOSE c_action_version;
4607 RETURN l_dummy;
4608 ELSE
4609 CLOSE c_action_version;
4610 RETURN NULL;
4611 END IF;
4612
4613 END get_action_version_id;
4614
4615 -- SubProgram : is_action_reference_unique
4616 -- Type : UTIL FUNCTION
4617 -- Purpose : Check if action_reference is passed or not. Is it unique or not
4618 -- Assumptions : None
4619
4620 PROCEDURE is_action_reference_unique
4621 (
4622 p_action_reference IN VARCHAR2 :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4623 ,p_deliverable_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4624 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4625 ,x_unique_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4626 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4627 )
4628 IS
4629
4630 CURSOR l_action_ref_csr
4631 IS
4632 SELECT 'X'
4633 FROM pa_proj_elements ppe,
4634 pa_object_relationships por
4635 WHERE por.object_id_from2 = p_deliverable_id
4636 AND object_id_to2 =ppe.proj_element_id
4637 AND ppe.pm_source_reference = p_action_reference
4638 and project_id = p_project_id
4639 and object_type = 'PA_ACTIONS'
4640 and object_type_from = 'PA_DELIVERABLES';
4641
4642 CURSOR proj_num IS
4643 SELECT proj.segment1
4644 , elem.name
4645 FROM pa_projects_all proj
4646 , pa_proj_elements elem
4647 WHERE proj.project_id = p_project_id
4648 AND elem.project_id = proj.project_id
4649 AND elem.object_type = 'PA_DELIVERABLES'
4650 AND elem.proj_element_id = p_deliverable_id;
4651
4652 l_api_name CONSTANT VARCHAR2(30) := 'is_action_reference_unique';
4653 l_action_id NUMBER ;
4654 l_dummy VARCHAR2(1);
4655 l_project_number Pa_Projects_All.Segment1%TYPE;
4656 l_deliverable_name Pa_proj_elements.name%TYPE;
4657
4658 BEGIN
4659
4660 x_return_status := FND_API.G_RET_STS_SUCCESS;
4661
4662 OPEN proj_num;
4663 FETCH proj_num INTO l_project_number, l_deliverable_name;
4664 CLOSE proj_num;
4665
4666 IF p_action_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4667 AND p_action_reference IS NOT NULL
4668 THEN
4669 OPEN l_action_ref_csr;
4670 FETCH l_action_ref_csr INTO l_dummy; -- 3749462 changed from l_action_id to l_dummy
4671
4672 IF l_action_ref_csr%FOUND THEN
4673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4674 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_ACTION_REF_INVALID') ;
4675 PA_UTILS.ADD_MESSAGE
4676 (p_app_short_name => 'PA',
4677 p_msg_name => 'PA_ACTION_VALID_ERR',
4678 p_token1 => 'PROJECT',
4679 p_value1 => l_project_number,
4680 p_token2 => 'DLVR_REFERENCE',
4681 p_value2 => l_deliverable_name,
4682 p_token3 => 'ACTION_REFERENCE',
4683 p_value3 => p_action_reference,
4684 p_token4 => 'MESSAGE',
4685 p_value4 => l_err_message
4686 );
4687 END IF; -- fnd_msg_pub.g_msg_lvl_error
4688
4689 CLOSE l_action_ref_csr;
4690 x_unique_flag := 'N';
4691 RAISE FND_API.G_EXC_ERROR;
4692
4693 END IF; -- l_action_dlvr_relation%FOUND
4694 CLOSE l_action_ref_csr;
4695
4696 ELSE -- p_action_reference IS NULL
4697 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4698 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_ACTION_REF_AND_ID_MISSING') ;
4699 PA_UTILS.ADD_MESSAGE
4700 (p_app_short_name => 'PA',
4701 p_msg_name => 'PA_ACTION_VALID_ERR',
4702 p_token1 => 'PROJECT',
4703 p_value1 => l_project_number,
4704 p_token2 => 'DLVR_REFERENCE',
4705 p_value2 => l_deliverable_name,
4706 p_token3 => 'ACTION_REFERENCE',
4707 p_value3 => p_action_reference,
4708 p_token4 => 'MESSAGE',
4709 p_value4 => l_err_message
4710 );
4711 END IF;-- fnd_msg_pub.g_msg_lvl_error
4712
4713 x_unique_flag := Null;
4714
4715 RAISE FND_API.G_EXC_ERROR;
4716
4717 END IF; -- p_action_reference IS NULL
4718
4719 x_unique_flag := 'Y';
4720
4721 EXCEPTION
4722 WHEN FND_API.G_EXC_ERROR THEN
4723 x_return_status := FND_API.G_RET_STS_ERROR;
4724
4725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4727
4728 WHEN OTHERS THEN
4729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4730 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4731 FND_MSG_PUB.add_exc_msg
4732 ( p_pkg_name => G_MODULE_NAME
4733 , p_procedure_name => l_api_name );
4734 END IF;
4735
4736 END is_action_reference_unique;
4737
4738 --Name: IS_FUNCTION_CODE_VALID
4739 --Type: FUNCTION
4740 --Description: This functions validate the function code based on lookup values
4741 -- an action ID.
4742
4743 FUNCTION IS_FUNCTION_CODE_VALID
4744 (
4745 p_function_code IN VARCHAR2
4746 ) RETURN VARCHAR2
4747 IS
4748
4749 CURSOR c_func_code IS
4750 SELECT 'X'
4751 FROM pa_lookups
4752 WHERE lookup_type = 'PA_DLVR_ACTION_FUNCTION'
4753 AND lookup_code = p_function_code;
4754
4755 l_dummy VARCHAR2(1) := 'Y';
4756
4757 BEGIN
4758
4759 OPEN c_func_code;
4760 FETCH c_func_code into l_dummy;
4761
4762 IF c_func_code%NOTFOUND THEN
4763 CLOSE c_Func_code;
4764 return 'N';
4765 ELSE
4766 CLOSE c_Func_code;
4767 return 'Y';
4768 END IF;
4769
4770 END IS_FUNCTION_CODE_VALID;
4771
4772 --Name: convert_pm_actionref_to_id
4773 --Type: Procedure
4774 --Description: This procedure can be used to converse an incoming action reference to
4775 -- an action ID.
4776
4777 PROCEDURE Convert_pm_actionref_to_id
4778 (
4779 p_action_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4780 ,p_action_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4781 ,p_deliverable_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4782 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4783 ,p_out_action_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4784 ,p_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4785 )
4786 IS
4787
4788 CURSOR l_action_id_csr
4789 IS
4790 SELECT ppe.proj_element_id
4791 FROM pa_proj_elements ppe,
4792 pa_object_relationships por
4793 WHERE por.object_id_from2 = p_deliverable_id
4794 AND object_id_to2 = ppe.proj_element_id
4795 AND ppe.proj_element_id = p_action_id
4796 and ppe.project_id = p_project_id
4797 and object_type = 'PA_ACTIONS'
4798 and object_type_from = 'PA_DELIVERABLES';
4799
4800 CURSOR l_action_ref_csr
4801 IS
4802 SELECT ppe.proj_element_id
4803 FROM pa_proj_elements ppe,
4804 pa_object_relationships por
4805 WHERE por.object_id_from2 = p_deliverable_id
4806 AND object_id_to2 = ppe.proj_element_id
4807 AND ppe.pm_source_reference = p_action_reference
4808 and ppe.project_id = p_project_id
4809 and object_type = 'PA_ACTIONS'
4810 and object_type_from = 'PA_DELIVERABLES';
4811
4812 CURSOR proj_num IS
4813 SELECT proj.segment1
4814 , elem.name
4815 FROM pa_projects_all proj
4816 , pa_proj_elements elem
4817 WHERE proj.project_id = p_project_id
4818 AND elem.project_id = proj.project_id
4819 AND elem.object_type = 'PA_DELIVERABLES'
4820 AND elem.proj_element_id = p_deliverable_id;
4821
4822
4823 l_api_name CONSTANT VARCHAR2(30) := 'Convert_pm_dlvrref_to_id';
4824 l_action_id NUMBER ;
4825 l_dummy VARCHAR2(1);
4826 l_project_number Pa_Projects_All.Segment1%TYPE;
4827 l_deliverable_name Pa_proj_elements.name%TYPE;
4828
4829 BEGIN
4830
4831 p_return_status := FND_API.G_RET_STS_SUCCESS;
4832
4833 OPEN proj_num;
4834 FETCH proj_num INTO l_project_number, l_deliverable_name;
4835 CLOSE proj_num;
4836
4837 IF p_action_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4838 AND p_action_id IS NOT NULL
4839 THEN
4840
4841 --check validity of this ID
4842 OPEN l_action_id_csr;
4843 FETCH l_action_id_csr INTO l_action_id;
4844
4845 IF l_action_id_csr%NOTFOUND
4846 THEN
4847 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4848 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_ACTION_ID_INVALID') ;
4849 PA_UTILS.ADD_MESSAGE
4850 (p_app_short_name => 'PA',
4851 p_msg_name => 'PA_ACTION_VALID_ERR',
4852 p_token1 => 'PROJECT',
4853 p_value1 => l_project_number,
4854 p_token2 => 'DLVR_REFERENCE',
4855 p_value2 => l_deliverable_name,
4856 p_token3 => 'ACTION_REFERENCE',
4857 p_value3 => p_action_reference,
4858 p_token4 => 'MESSAGE',
4859 p_value4 => l_err_message
4860 );
4861
4862 END IF;
4863 CLOSE l_action_id_csr;
4864 RAISE FND_API.G_EXC_ERROR;
4865 END IF;
4866
4867 CLOSE l_action_id_csr;
4868 p_out_action_id := p_action_id;
4869
4870 ELSIF p_action_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
4871 AND p_action_reference IS NOT NULL
4872 THEN
4873
4874 --check validity of this reference
4875 OPEN l_action_ref_csr;
4876 FETCH l_action_ref_csr INTO l_action_id;
4877
4878 IF l_action_ref_csr%NOTFOUND
4879 THEN
4880 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4881 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_ACTION_REF_INVALID') ;
4882 PA_UTILS.ADD_MESSAGE
4883 (p_app_short_name => 'PA',
4884 p_msg_name => 'PA_DLVR_VALID_ERR',
4885 p_token1 => 'PROJECT',
4886 p_value1 => l_project_number,
4887 p_token2 => 'DLVR_REFERENCE',
4888 p_value2 => l_deliverable_name,
4889 p_token3 => 'ACTION_REFERENCE',
4890 p_value3 => p_action_reference,
4891 p_token4 => 'MESSAGE',
4892 p_value4 => l_err_message
4893 );
4894 END IF;
4895
4896 CLOSE l_action_ref_csr;
4897 RAISE FND_API.G_EXC_ERROR;
4898 END IF;
4899
4900 CLOSE l_action_ref_csr;
4901 p_out_action_id := l_action_id;
4902 ELSE
4903 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4904 l_err_message := FND_MESSAGE.GET_STRING('PA','PA_ACTION_REF_AND_ID_MISSING') ;
4905 PA_UTILS.ADD_MESSAGE
4906 (p_app_short_name => 'PA',
4907 p_msg_name => 'PA_DLVR_VALID_ERR',
4908 p_token1 => 'PROJECT',
4909 p_value1 => l_project_number,
4910 p_token2 => 'DLVR_REFERENCE',
4911 p_value2 => l_deliverable_name,
4912 p_token3 => 'ACTION_REFERENCE',
4913 p_value3 => p_action_reference,
4914 p_token4 => 'MESSAGE',
4915 p_value4 => l_err_message
4916 );
4917 END IF;
4918 RAISE FND_API.G_EXC_ERROR;
4919
4920 END IF; -- If p_action_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4921
4922 EXCEPTION
4923 WHEN FND_API.G_EXC_ERROR THEN
4924 p_return_status := FND_API.G_RET_STS_ERROR;
4925
4926 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4927 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4928
4929 WHEN OTHERS THEN
4930 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4931 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4932 FND_MSG_PUB.add_exc_msg
4933 ( p_pkg_name => G_MODULE_NAME
4934 , p_procedure_name => l_api_name );
4935 END IF;
4936
4937 END Convert_pm_actionref_to_id;
4938
4939 --Name: Progress_Enabled_Validation
4940 --Type: Procedure
4941 --Description: This procedure is used to modify/validate
4942 -- completion date, status code, progress weight
4943 -- based on progress enabled or not for a dlvr.
4944 --
4945 -- Logic :
4946 -- FOR CREATE
4947 -- Progress Completion Status Progress
4948 -- Enabled Date Code Weight
4949 --
4950 -- Y Null mapped to DLV_NOT_STARTED User value
4951 -- N Null mapped to DLV_NOT_STARTED Null
4952 -- FOR UPDATE
4953 -- Y No updation No updation User Value
4954 -- N User Value User Value Null
4955
4956 PROCEDURE Progress_Enabled_Validation
4957 (
4958 p_deliverable_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4959 , p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4960 , p_dlvr_type_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4961 , px_actual_finish_date IN OUT NOCOPY DATE --File.Sql.39 bug 4440895
4962 , px_progress_weight IN OUT NOCOPY PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE --File.Sql.39 bug 4440895
4963 , px_status_code IN OUT NOCOPY Pa_task_types.initial_status_code%TYPE --File.Sql.39 bug 4440895
4964 , p_calling_Mode IN VARCHAR2 := 'INSERT'
4965 ) IS
4966
4967 Cursor C1 IS
4968 SELECT completion_date, status_code
4969 FROM pa_deliverables_v
4970 WHERE project_id = p_project_id
4971 AND proj_element_id = p_deliverable_id
4972 AND dlvr_type_id = p_dlvr_type_id;
4973
4974
4975 l_dlvr_prg_enabled VARCHAR2(1) := NULL;
4976 l_dlvr_action_enabled VARCHAR2(1) := NULL;
4977 l_status_code Pa_task_types.initial_status_code%TYPE := NULL;
4978
4979 BEGIN
4980 -- Fetching information based on deliverable type
4981 PA_DELIVERABLE_UTILS.get_dlvr_type_info
4982 (
4983 p_dlvr_type_id => p_dlvr_type_id,
4984 x_dlvr_prg_enabled => l_dlvr_prg_enabled,
4985 x_dlvr_action_enabled => l_dlvr_action_enabled,
4986 x_dlvr_default_status_code => l_status_code
4987 );
4988
4989 Pa_Debug.WRITE(g_module_name,'Progress_Enabled dlvr_type['||p_dlvr_type_id||']Prg Enabled[' ||l_dlvr_prg_enabled||
4990 ']Action Enabled['||l_dlvr_action_enabled||']status['||l_status_code||']',l_debug_level3);
4991
4992 -- Override the default status code in case passed by customer.
4993 IF px_status_code IS NOT NULL THEN
4994 l_status_code := px_status_code;
4995 END IF;
4996
4997 IF (l_dlvr_prg_enabled = 'Y') THEN
4998
4999 IF ( p_calling_mode = 'INSERT') THEN
5000 px_actual_finish_date := NULL;
5001 px_status_code := l_status_code;
5002 px_progress_weight := px_progress_weight;
5003 ELSE --p_calling_mode = 'UPDATE'
5004 OPEN C1;
5005 FETCH C1 INTO px_actual_finish_date , px_status_code;
5006 px_progress_weight := px_progress_weight;
5007 CLOSE C1;
5008 END IF; --p_calling_mode = 'INSERT'
5009
5010 ELSE -- l_dlvr_prg_enabled = 'N'
5011
5012 IF ( p_calling_mode = 'INSERT') THEN
5013 px_actual_finish_date := NULL;
5014 px_status_code := l_status_code;
5015 px_progress_weight := NULL;
5016 ELSE --p_calling_mode = 'UPDATE'
5017 px_actual_finish_date := px_actual_finish_date;
5018 px_status_code := l_status_code;
5019 px_progress_weight := NULL;
5020 END IF; --p_calling_mode = 'INSERT'
5021
5022 END IF; -- l_dlvr_prg_enabled = 'Y'
5023 END Progress_Enabled_Validation;
5024
5025 --Name: enable_deliverable
5026 --Type: Procedure
5027 --Description: This api calls Pa_Project_Structure_Pub1 apis for
5028 -- creating Structure, Structure Versions, Structure Version Attributes
5029
5030 Procedure enable_deliverable(
5031 p_api_version IN NUMBER := 1.0
5032 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5033 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5034 , p_debug_mode IN VARCHAR2 := 'N'
5035 , p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
5036 , p_project_id IN Pa_Projects_All.project_id%TYPE
5037 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5038 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5039 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5040 )IS
5041 Cursor C_name IS
5042 SELECT segment1||':Deliverable'
5043 FROM pa_Projects_All
5044 WHERE Project_id = p_Project_Id;
5045
5046 l_name Pa_Proj_Elements.Name%TYPE;
5047 l_structure_id Pa_Proj_Elements.Proj_Element_Id%TYPE;
5048 l_structure_version_id Pa_Proj_Element_Versions.Element_Version_Id%TYPE;
5049 l_pev_structure_id Pa_Proj_Element_Versions.Element_Version_Id%TYPE;
5050
5051 l_api_name CONSTANT VARCHAR2(30) := 'ENABLE_DELIVERABLES';
5052 l_msg_index_out NUMBER;
5053 -- added for Bug:4537865
5054 l_new_msg_data VARCHAR2(2000);
5055 -- added for Bug:4537865
5056 BEGIN
5057
5058 -- Initialize the message table if requested.
5059 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
5060 FND_MSG_PUB.initialize;
5061 END IF;
5062
5063 IF (p_commit = FND_API.G_TRUE) THEN
5064 savepoint CREATE_DELIVERABLE_PUB;
5065 END IF;
5066
5067 IF p_debug_mode = 'Y' THEN
5068 PA_DEBUG.set_curr_function( p_function => l_api_name,
5069 p_debug_mode => p_debug_mode );
5070 pa_debug.g_err_stage:= 'Inside '||l_api_name;
5071 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
5072 END IF;
5073
5074 -- Set API return status to success
5075 x_return_status := FND_API.G_RET_STS_SUCCESS;
5076
5077 OPEN C_name;
5078 FETCH C_name INTO l_name;
5079 CLOSE C_name;
5080
5081 PA_PROJECT_STRUCTURE_PUB1.create_structure (
5082 p_api_version => p_api_version
5083 , p_init_msg_list => p_init_msg_list
5084 , p_commit => p_commit
5085 , p_debug_mode => p_debug_mode
5086 , p_validate_only => p_validate_only
5087 , p_project_id => p_project_id
5088 , p_calling_flag => 'DELIVERABLE'
5089 , p_calling_module => 'AMG'
5090 , p_structure_name => l_name
5091 , p_structure_description => l_name
5092 , x_return_status => x_return_status
5093 , x_msg_count => x_msg_count
5094 , x_msg_data => x_msg_data
5095 , x_structure_id => l_structure_Id
5096 );
5097
5098 IF p_debug_mode = 'Y' THEN
5099 pa_debug.write(g_module_name,'PA_PROJECT_STRUCTURE_PUB1.create_structure Return Status ['||x_return_status||']Struc ID['||l_structure_id||']',3) ;
5100 END IF;
5101
5102 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5104 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5105 RAISE FND_API.G_EXC_ERROR;
5106 END IF;
5107
5108
5109 PA_PROJECT_STRUCTURE_PUB1.Create_Structure_Version (
5110 p_api_version => p_api_version
5111 , p_init_msg_list => p_init_msg_list
5112 , p_commit => p_commit
5113 , p_debug_mode => p_debug_mode
5114 , p_validate_only => p_validate_only
5115 , p_calling_module => 'AMG'
5116 , p_structure_id => l_structure_id
5117 , x_return_status => x_return_status
5118 , x_msg_count => x_msg_count
5119 , x_msg_data => x_msg_data
5120 , x_structure_version_id => l_structure_version_id
5121 );
5122
5123 IF p_debug_mode = 'Y' THEN
5124 pa_debug.write(g_module_name,'PA_PROJECT_STRUCTURE_PUB1.Create_Structure_Version Return Status ['||x_return_status||']Struc Vers ID['||l_structure_version_id||']',3) ;
5125 END IF;
5126
5127 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5129 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5130 RAISE FND_API.G_EXC_ERROR;
5131 END IF;
5132
5133
5134 PA_PROJECT_STRUCTURE_PUB1.Create_Structure_Version_Attr (
5135 p_api_version => p_api_version
5136 , p_init_msg_list => p_init_msg_list
5137 , p_commit => p_commit
5138 , p_debug_mode => p_debug_mode
5139 , p_validate_only => p_validate_only
5140 , p_calling_module => 'AMG'
5141 , p_structure_version_id => l_structure_version_id
5142 , p_structure_version_name => l_name
5143 , p_structure_version_desc => l_name
5144 , p_change_reason_code => null
5145 , x_pev_structure_id => l_pev_structure_id
5146 , x_return_status => x_return_status
5147 , x_msg_count => x_msg_count
5148 , x_msg_data => x_msg_data
5149 );
5150
5151 IF p_debug_mode = 'Y' THEN
5152 pa_debug.write(g_module_name,'PA_PROJECT_STRUCTURE_PUB1.Create_Structure_Version_Attr Return Status ['||x_return_status||']Struc ID['||l_pev_structure_id||']',3) ;
5153 END IF;
5154
5155 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5157 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5158 RAISE FND_API.G_EXC_ERROR;
5159 END IF;
5160
5161 IF p_debug_mode = 'Y' THEN --Added for bug 4945876
5162 pa_debug.reset_curr_function;
5163 END IF ;
5164
5165 EXCEPTION
5166 WHEN FND_API.G_EXC_ERROR THEN
5167 IF (p_commit = FND_API.G_TRUE) THEN
5168 ROLLBACK TO CREATE_DELIVERABLE_PUB;
5169 END IF;
5170 IF p_debug_mode = 'Y' THEN
5171 pa_debug.reset_curr_function;
5172 pa_debug.write(g_module_name,l_api_name||': Inside G_EXC_ERROR exception',5);
5173 END IF;
5174 x_return_status := FND_API.G_RET_STS_ERROR;
5175 x_msg_count := FND_MSG_PUB.count_msg;
5176
5177 IF x_msg_count = 1 THEN
5178 PA_INTERFACE_UTILS_PUB.get_messages
5179 (p_encoded => FND_API.G_FALSE,
5180 p_msg_index => 1,
5181 p_msg_count => x_msg_count,
5182 p_msg_data => x_msg_data,
5183 -- p_data => x_msg_data, * commented for Bug: 4537865
5184 p_data => l_new_msg_data, -- added for Bug: 4537865
5185 p_msg_index_out => l_msg_index_out);
5186
5187 -- added for Bug: 4537865
5188 x_msg_data := l_new_msg_data;
5189 -- added for Bug: 4537865
5190 END IF;
5191
5192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5193 IF (p_commit = FND_API.G_TRUE) THEN
5194 ROLLBACK TO CREATE_DELIVERABLE_PUB;
5195 END IF;
5196 IF p_debug_mode = 'Y' THEN
5197 pa_debug.reset_curr_function;
5198
5199 pa_debug.write(g_module_name,l_api_name||': Inside G_EXC_UNEXPECTED_ERROR exception',5);
5200 END IF;
5201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5202 x_msg_count := FND_MSG_PUB.count_msg;
5203
5204 IF x_msg_count = 1 THEN
5205 PA_INTERFACE_UTILS_PUB.get_messages
5206 (p_encoded => FND_API.G_FALSE,
5207 p_msg_index => 1,
5208 p_msg_count => x_msg_count,
5209 p_msg_data => x_msg_data,
5210 -- p_data => x_msg_data, * commented for Bug: 4537865
5211 p_data => l_new_msg_data, -- added for Bug: 4537865
5212 p_msg_index_out => l_msg_index_out);
5213
5214 -- added for Bug: 4537865
5215 x_msg_data := l_new_msg_data;
5216 -- added for Bug: 4537865
5217
5218 END IF;
5219
5220 WHEN OTHERS THEN
5221 IF (p_commit = FND_API.G_TRUE) THEN
5222 ROLLBACK TO CREATE_DELIVERABLE_PUB;
5223 END IF;
5224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5225 x_msg_count := 1;
5226 x_msg_data := SQLERRM;
5227
5228 IF p_debug_mode = 'Y' THEN
5229 pa_debug.g_err_stage:=l_api_name||': Unexpected Error'||SQLERRM;
5230 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
5231 pa_debug.reset_curr_function;
5232 END IF;
5233
5234
5235 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5236 FND_MSG_PUB.add_exc_msg
5237 ( p_pkg_name => g_module_name
5238 , p_procedure_name => l_api_name );
5239 END IF;
5240 END enable_deliverable;
5241
5242 /* ==============3435905 : FP M : Deliverables Changes For AMG - END * =========================*/
5243
5244 -- SubProgram : IS_DLV_PROGRESSABLE
5245 -- Type : UTIL FUNCTION
5246 -- Purpose : This function will check whether the given delievrable has progress
5247 -- entry allowed
5248 -- Note :
5249 -- Assumptions : None
5250 -- Parameter IN/OUT Type Required Description and Purpose
5251 -- --------------------------- ------------ ----------- --------- ---------------------------
5252 -- p_project_id IN NUMBER Project Id
5253 -- p_deliverable_id IN NUMBER Deliverable Proj Element Id
5254
5255 FUNCTION IS_DLV_PROGRESSABLE
5256 (
5257 p_project_id IN NUMBER
5258 ,p_deliverable_id IN NUMBER
5259 ) RETURN VARCHAR2
5260 IS
5261
5262 l_dummy varchar2(1):='N';
5263
5264 CURSOR c_dlv_type IS
5265 SELECT nvl(prog_entry_enable_flag,'N')
5266 from pa_proj_elements elem
5267 , pa_task_types dlvtype
5268 where elem.type_id = dlvtype.task_type_id
5269 and elem.proj_element_id = p_deliverable_id
5270 and elem.object_type = 'PA_DELIVERABLES'
5271 and elem.project_id = p_project_id;
5272
5273 BEGIN
5274 OPEN c_dlv_type;
5275 FETCH c_dlv_type into l_dummy ;
5276 IF c_dlv_type%notfound THEN
5277 l_dummy:='N';
5278 END IF;
5279 CLOSE c_dlv_type;
5280
5281 return l_dummy;
5282
5283 END IS_DLV_PROGRESSABLE;
5284
5285 -- SubProgram : IS_STR_TASK_HAS_DELIVERABLES
5286 -- Type : UTIL FUNCTION
5287 -- Purpose : This function will check whether the given task has deliverables
5288 -- Note :
5289 -- Assumptions : None
5290 -- Parameter IN/OUT Type Required Description and Purpose
5291 -- --------------------------- ------------ ----------- --------- ---------------------------
5292 -- p_str_task_id IN NUMBER Proj Element Id of structure or task
5293
5294 FUNCTION IS_STR_TASK_HAS_DELIVERABLES
5295 (
5296 p_str_task_id IN NUMBER
5297 ) RETURN VARCHAR2
5298 IS
5299
5300 l_dummy varchar2(1):='N';
5301
5302 CURSOR c_task_dlvs IS
5303 SELECT 'Y'
5304 FROM dual
5305 WHERE exists
5306 (Select 'xyz'
5307 from pa_object_relationships
5308 where object_id_from2 = p_str_task_id
5309 and relationship_type = 'A'
5310 and relationship_subtype IN ('STRUCTURE_TO_DELIVERABLE', 'TASK_TO_DELIVERABLE')
5311 );
5312
5313 BEGIN
5314 OPEN c_task_dlvs;
5315 FETCH c_task_dlvs into l_dummy ;
5316 IF c_task_dlvs%found THEN
5317 l_dummy:='Y';
5318 ELSE
5319 l_dummy:='N';
5320 END IF;
5321
5322 CLOSE c_task_dlvs;
5323
5324 return l_dummy;
5325
5326 END IS_STR_TASK_HAS_DELIVERABLES;
5327
5328 -- SubProgram : GET_TASK_DATES ( 3442451 )
5329 -- Type : UTIL FUNCTION
5330 -- Purpose : This function will retrun date ( i.e. schedule_start_date / schedule_finish_date /
5331 -- actual_start_date / actual_finish_date / earliest_start_date / earliest_finish_date )
5332 -- based on p_date_type parameter
5333 -- Note :
5334 -- Assumptions : None
5335 -- Parameter IN/OUT Type Required Description and Purpose
5336 -- --------------------------- ------------ ----------- --------- ---------------------------
5337 -- p_project_id IN NUMBER Yes Project Id
5338 -- p_date_type IN VARCHAR2 Yes Date Type
5339 -- p_task_id IN NUMBER Yes proj element id of task
5340
5341 FUNCTION GET_TASK_DATES
5342 (
5343 p_project_id IN NUMBER
5344 ,p_date_type IN VARCHAR2
5345 ,p_task_id IN NUMBER
5346 ) RETURN DATE
5347 IS
5348
5349
5350 -- 3578694 removed the cursor
5351 -- as PA_PROJ_ELEMENT_VERSIONS had join with object relationship table
5352 -- and one of the where condition was checking the structure_to_task relationship subtype
5353 -- so for child tasks, above condition is failing
5354
5355 --cursor c_task_exists(l_struct_ver_id IN NUMBER) IS
5356 -- SELECT
5357 -- PEV.ELEMENT_VERSION_ID
5358 -- FROM
5359 -- PA_OBJECT_RELATIONSHIPS POR
5360 -- ,PA_PROJ_ELEMENT_VERSIONS PEV
5361 -- WHERE
5362 -- POR.OBJECT_ID_FROM1 = l_struct_ver_id
5363 -- AND PEV.PROJ_ELEMENT_ID = p_task_id
5364 -- AND POR.OBJECT_ID_TO1 = PEV.ELEMENT_VERSION_ID
5365 -- AND POR.OBJECT_TYPE_FROM = 'PA_STRUCTURES'
5366 -- AND POR.OBJECT_TYPE_TO = 'PA_TASKS'
5367 -- AND POR.RELATIONSHIP_SUBTYPE = 'STRUCTURE_TO_TASK';
5368
5369 -- 3578694 cursor will return task element version id for the
5370 -- passed structure version id
5371 cursor c_task_exists(l_struct_ver_id IN NUMBER) IS
5372 SELECT
5373 PEV.ELEMENT_VERSION_ID
5374 FROM
5375 PA_PROJ_ELEMENT_VERSIONS PEV
5376 WHERE
5377 PEV.PARENT_STRUCTURE_VERSION_ID = l_struct_ver_id
5378 AND PEV.PROJ_ELEMENT_ID = p_task_id
5379 AND PEV.PROJECT_ID = p_project_id;
5380
5381 CURSOR c_task_info(l_task_ver_id IN NUMBER) IS
5382 SELECT
5383 SCHEDULED_START_DATE
5384 ,SCHEDULED_FINISH_DATE
5385 ,ACTUAL_START_DATE
5386 ,ACTUAL_FINISH_DATE
5387 ,EARLY_START_DATE
5388 ,EARLY_FINISH_DATE
5389 FROM
5390 PA_PROJ_ELEM_VER_SCHEDULE
5391 WHERE
5392 ELEMENT_VERSION_ID = l_task_ver_id ;
5393
5394 /*AND PROJECT_ID = p_project_id Commented Unwanted Join 3614361 */
5395
5396 l_date DATE := NULL;
5397
5398 l_latest_pub_wp_struct_id NUMBER := NULL;
5399 l_current_working_wp_struct_id NUMBER := NULL;
5400 l_wp_struct_id NUMBER := NULL;
5401
5402 l_task_ver_id NUMBER := NULL;
5403
5404 is_task_in_published_ver VARCHAR(1) := 'N';
5405 is_task_in_curnt_wrkng_ver VARCHAR(1) := 'N';
5406
5407 c_task_rec c_task_exists%rowtype;
5408 c_task_date_rec c_task_info%rowtype;
5409
5410 BEGIN
5411
5412 -- retrieve published wp structure version id
5413
5414 l_latest_pub_wp_struct_id := PA_PROJ_ELEMENTS_UTILS.latest_published_ver_id(p_project_id);
5415
5416 -- check whether task exists for published wp structure
5417 -- if yes
5418 -- retrieve task version id, set is_task_in_published_ver to 'Y' and task version id in local variable
5419 -- else
5420 -- retrieve current working wp structure version id
5421 -- if yes retrieve task version id,
5422 -- set is_task_in_published_ver to 'Y' and task version id in local variible
5423 -- end if
5424 -- end if
5425 -- if task_vers_id retrieved is not null , i.e. task is either in published or current working
5426 -- retrieve the required dates for the task version id based on date type variable value
5427 -- else
5428 -- return null value
5429 -- end if
5430
5431 -- 3578694 added if condition for checking l_latest_pub_wp_struct_id for NULL
5432 -- if l_latest_pub_wp_struct_id is null, set is_task_in_published_ver to 'N'
5433 -- and do not check for task existance for that particular structure version
5434
5435 IF l_latest_pub_wp_struct_id IS NULL THEN
5436 is_task_in_published_ver := 'N';
5437 ELSE
5438
5439 OPEN c_task_exists(l_latest_pub_wp_struct_id);
5440 FETCH c_task_exists into c_task_rec;
5441
5442 IF c_task_exists%NOTFOUND THEN
5443 is_task_in_published_ver := 'N';
5444 ELSE
5445 is_task_in_published_ver := 'Y';
5446 l_task_ver_id := c_task_rec.ELEMENT_VERSION_ID;
5447 END IF;
5448
5449 CLOSE c_task_exists;
5450 END IF;
5451
5452 IF is_task_in_published_ver = 'N' THEN
5453 l_current_working_wp_struct_id := PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(p_project_id);
5454
5455 OPEN c_task_exists(l_current_working_wp_struct_id);
5456 FETCH c_task_exists into c_task_rec ;
5457
5458 IF c_task_exists%notfound THEN
5459 is_task_in_curnt_wrkng_ver := 'N';
5460 ELSE
5461 is_task_in_curnt_wrkng_ver := 'Y';
5462 l_task_ver_id := c_task_rec.ELEMENT_VERSION_ID;
5463 END IF;
5464
5465 CLOSE c_task_exists;
5466 END IF;
5467
5468 IF l_task_ver_id IS NOT NULL THEN
5469 OPEN c_task_info(l_task_ver_id);
5470 FETCH c_task_info into c_task_date_rec ;
5471 CLOSE c_task_info;
5472
5473 IF p_date_type = 'SCH_START_DATE' THEN
5474 l_date := c_task_date_rec.SCHEDULED_START_DATE;
5475 ELSIF p_date_type = 'SCH_FINISH_DATE' THEN
5476 l_date := c_task_date_rec.SCHEDULED_FINISH_DATE;
5477 ELSIF p_date_type = 'ACT_START_DATE' THEN
5478 l_date := c_task_date_rec.ACTUAL_START_DATE;
5479 ELSIF p_date_type = 'ACT_FINISH_DATE' THEN
5480 l_date := c_task_date_rec.ACTUAL_FINISH_DATE;
5481 ELSIF p_date_type = 'EARLY_START_DATE' THEN
5482 l_date := c_task_date_rec.EARLY_START_DATE;
5483 ELSIF p_date_type = 'EARLY_FINISH_DATE' THEN
5484 l_date := c_task_date_rec.EARLY_FINISH_DATE;
5485 ELSE
5486 l_date := NULL;
5487 END IF;
5488 END IF;
5489
5490 return l_date;
5491
5492 END GET_TASK_DATES;
5493
5494 FUNCTION IS_DLV_BASED_TASK_EXISTS
5495 (
5496 p_project_id IN NUMBER
5497 ) RETURN VARCHAR2
5498 IS
5499 CURSOR dlv_based_task IS
5500 SELECT 'Y'
5501 FROM dual
5502 WHERE EXISTS ( SELECT 'Y'
5503 FROM pa_proj_elements
5504 WHERE base_percent_comp_deriv_code = 'DELIVERABLE'
5505 AND object_type = 'PA_TASKS'
5506 AND project_id = p_project_id);
5507 l_dummy VARCHAR2(1) := 'N' ;
5508 BEGIN
5509 OPEN dlv_based_task ;
5510 FETCH dlv_based_task INTO l_dummy ;
5511 CLOSE dlv_based_task ;
5512
5513 RETURN l_dummy ;
5514 END IS_DLV_BASED_TASK_EXISTS ;
5515
5516 FUNCTION IS_DELIVERABLES_DEFINED
5517 (
5518 p_project_id IN NUMBER
5519 ) RETURN VARCHAR2
5520 IS
5521 CURSOR is_dlv_exists IS
5522 SELECT 'Y'
5523 FROM dual
5524 WHERE EXISTS ( SELECT 'Y'
5525 FROM pa_proj_elements
5526 WHERE object_type = 'PA_DELIVERABLES'
5527 AND project_id = p_project_id);
5528 l_dummy VARCHAR2(1) := 'N' ;
5529 BEGIN
5530
5531 OPEN is_dlv_exists ;
5532 FETCH is_dlv_exists INTO l_dummy ;
5533 CLOSE is_dlv_exists ;
5534
5535 RETURN l_dummy ;
5536 END IS_DELIVERABLES_DEFINED ;
5537
5538 FUNCTION CHECK_USER_VIEW_DLV_PRIVILEGE
5539 (
5540 p_project_id IN NUMBER
5541 ) RETURN VARCHAR2
5542 IS
5543 l_ret_code VARCHAR2(1) ;
5544 BEGIN
5545 l_ret_code := PA_SECURITY_PVT.check_user_privilege
5546 ( p_privilege => 'PA_DELIVERABLE_VIEW'
5547 ,p_object_name => 'PA_PROJECTS'
5548 ,p_object_key => p_project_id
5549 ) ;
5550 RETURN l_ret_code ;
5551 END CHECK_USER_VIEW_DLV_PRIVILEGE;
5552
5553
5554 PROCEDURE GET_DEFAULT_TASK
5555 (
5556 p_dlv_element_id IN NUMBER
5557 ,p_dlv_version_id IN NUMBER
5558 ,p_project_id IN NUMBER
5559 ,x_oke_task_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5560 ,x_oke_task_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5561 ,x_oke_task_number OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5562 ,x_bill_task_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5563 ,x_bill_task_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5564 ,x_bill_task_number OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5565 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5566 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5567 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5568 )
5569 IS
5570 -- This cursor will return the fin. task id which are mapped to WP
5571 -- task which are associated to give deliverable for SPLIT
5572 -- MAPPING setup.
5573
5574 -- 3586196 changed cursor for where conditions and
5575 -- added task name number column
5576 CURSOR split_mapping(c_parent_struct_ver_id IN NUMBER) IS
5577 SELECT distinct pt.task_id
5578 ,pt.task_name
5579 ,pt.task_number
5580 FROM pa_tasks pt
5581 ,pa_proj_element_versions pev1
5582 ,pa_proj_element_versions pev2
5583 ,pa_object_relationships obj1
5584 ,pa_object_relationships obj2
5585 WHERE obj1.object_id_to2 = p_dlv_element_id
5586 AND obj1.relationship_type = 'A'
5587 AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
5588 AND obj1.object_type_from = 'PA_TASKS'
5589 AND obj1.object_type_to = 'PA_DELIVERABLES'
5590 AND pev1.proj_element_id = obj1.object_id_from2
5591 AND pev1.parent_structure_version_id = c_parent_struct_ver_id
5592 AND pev1.project_id = p_project_id
5593 AND obj2.object_id_from1 = pev1.element_version_id
5594 AND obj2.relationship_type = 'M'
5595 AND pev2.element_version_id = obj2.object_id_to1
5596 AND pt.project_id = p_project_id
5597 AND pt.task_id = pev2.proj_element_id
5598 AND pt.chargeable_flag = 'Y' ;
5599
5600 -- This cursor will return the fin. task id in PARTLY /FULLY
5601 -- SHARED structure
5602 CURSOR share_partial_or_full(c_parent_struct_ver_id IN NUMBER) IS
5603 SELECT distinct pt.task_id
5604 ,pt.task_name
5605 ,pt.task_number
5606 FROM pa_tasks pt
5607 ,pa_proj_element_versions pev
5608 ,pa_object_relationships obj
5609 WHERE obj.object_id_to2 = p_dlv_element_id
5610 AND obj.relationship_type = 'A'
5611 AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
5612 AND obj.object_type_from = 'PA_TASKS'
5613 AND obj.object_type_to = 'PA_DELIVERABLES'
5614 AND pev.proj_element_id = obj.object_id_from2
5615 AND pev.parent_structure_version_id = c_parent_struct_ver_id
5616 AND pev.project_id = p_project_id
5617 AND pev.proj_element_id = pt.task_id
5618 AND pt.project_id = p_project_id
5619 AND pt.chargeable_flag = 'Y' ;
5620
5621
5622 -- This cursor will return the fin. top task id which are
5623 -- mapped to WP task which are associated to give deliver
5624 -- able for SPLIT MAPPING setup.
5625 CURSOR split_mapping_bill(c_parent_struct_ver_id IN NUMBER) IS
5626 SELECT distinct pt.top_task_id
5627 ,pt1.task_name
5628 ,pt1.task_number
5629 FROM pa_tasks pt
5630 ,pa_proj_element_versions pev1
5631 ,pa_proj_element_versions pev2
5632 ,pa_object_relationships obj1
5633 ,pa_object_relationships obj2
5634 ,pa_tasks pt1
5635 WHERE obj1.object_id_to2 = p_dlv_element_id
5636 AND obj1.relationship_type = 'A'
5637 AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
5638 AND obj1.object_type_from = 'PA_TASKS'
5639 AND obj1.object_type_to = 'PA_DELIVERABLES'
5640 AND pev1.proj_element_id = obj1.object_id_from2
5641 AND pev1.parent_structure_version_id = c_parent_struct_ver_id
5642 AND pev1.project_id = p_project_id
5643 AND obj2.object_id_from1 = pev1.element_version_id
5644 AND obj2.relationship_type = 'M'
5645 AND pev2.element_version_id = obj2.object_id_to1
5646 AND pt.project_id = p_project_id
5647 AND pt.task_id = pev2.proj_element_id
5648 AND pt1.task_id = pt.top_task_id
5649 AND pt1.project_id = p_project_id;
5650
5651 -- This cursor will return the fin. top task id which in PARTLY/FULLY
5652 -- SHARED STRUCTURE
5653 CURSOR share_partial_or_full_bill(c_parent_struct_ver_id IN NUMBER) IS
5654 SELECT distinct pt.top_task_id
5655 ,pt1.task_name
5656 ,pt1.task_number
5657 FROM pa_tasks pt
5658 ,pa_proj_element_versions pev
5659 ,pa_object_relationships obj
5660 ,pa_tasks pt1
5661 WHERE obj.object_id_to2 = p_dlv_element_id
5662 AND obj.relationship_type = 'A'
5663 AND obj.relationship_subtype = 'TASK_TO_DELIVERABLE'
5664 AND obj.object_type_from = 'PA_TASKS'
5665 AND obj.object_type_to = 'PA_DELIVERABLES'
5666 AND pev.proj_element_id = obj.object_id_from2
5667 AND pev.parent_structure_version_id = c_parent_struct_ver_id
5668 AND pev.project_id = p_project_id
5669 AND pev.proj_element_id = pt.task_id
5670 AND pt.project_id = p_project_id
5671 AND pt1.task_id = pt.top_task_id
5672 AND pt1.project_id = p_project_id;
5673
5674
5675 i NUMBER ;
5676 l_share_type VARCHAR2(30) := null ;
5677 l_struct_version_id NUMBER := null ;
5678 l_debug_mode VARCHAR2(1) ;
5679
5680 BEGIN
5681
5682 x_msg_count := 0;
5683 x_return_status := FND_API.G_RET_STS_SUCCESS;
5684 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
5685
5686 IF l_debug_mode = 'Y' THEN
5687 PA_DEBUG.set_curr_function( p_function => 'GET_DEFAULT_TASK'
5688 ,p_debug_mode => l_debug_mode );
5689 pa_debug.g_err_stage:= 'Inside GET_DEFAULT_TASK ';
5690 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
5691 END IF;
5692
5693 l_share_type := PA_PROJ_TASK_STRUC_PUB.GET_SHARE_TYPE(p_project_id => p_project_id );
5694
5695 -- 3586196 added debug statements
5696
5697 IF l_debug_mode = 'Y' THEN
5698 pa_debug.write(g_module_name,'l_share_type: ' || l_share_type,3);
5699 END IF;
5700
5701 -- First get the current working version. If its not available get the latest published version.
5702 l_struct_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_CURRENT_WORKING_VER_ID(p_project_id => p_project_id) ;
5703
5704 IF l_debug_mode = 'Y' THEN
5705 pa_debug.write(g_module_name,'l_struct_version_id: ' || l_struct_version_id,3);
5706 END IF;
5707
5708 If nvl(l_struct_version_id,-99)=-99 THEN
5709 l_struct_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(p_project_id => p_project_id) ;
5710 END IF ;
5711
5712 IF l_debug_mode = 'Y' THEN
5713 pa_debug.write(g_module_name,'l_struct_version_id: ' || l_struct_version_id,3);
5714 END IF;
5715
5716 IF l_share_type = 'SPLIT_MAPPING' THEN
5717
5718 -- Get the default task id for OKE
5719 OPEN split_mapping(l_struct_version_id) ;
5720 i:=0 ;
5721 LOOP
5722 i:=i+1 ;
5723 FETCH split_mapping INTO x_oke_task_id, x_oke_task_name, x_oke_task_number ;
5724 EXIT WHEN (split_mapping%NOTFOUND OR i>2) ;
5725 END LOOP;
5726 CLOSE split_mapping ;
5727
5728 IF l_debug_mode = 'Y' THEN
5729 pa_debug.write(g_module_name,'i: ' || i,3);
5730 END IF;
5731
5732 IF i>2 THEN
5733 x_oke_task_id := null ;
5734 x_oke_task_name := null;
5735 x_oke_task_number := null;
5736 END IF ;
5737
5738 -- Get the default task id for BILLING
5739 OPEN split_mapping_bill(l_struct_version_id) ;
5740 i:=0 ;
5741 LOOP
5742 i:=i+1 ;
5743 FETCH split_mapping_bill INTO x_bill_task_id, x_bill_task_name, x_bill_task_number ;
5744 EXIT WHEN (split_mapping_bill%NOTFOUND OR i>2) ;
5745 END LOOP;
5746 CLOSE split_mapping_bill ;
5747
5748 IF l_debug_mode = 'Y' THEN
5749 pa_debug.write(g_module_name,'i: ' || i,3);
5750 END IF;
5751
5752 IF i>2 THEN
5753 x_bill_task_id := null ;
5754 x_bill_task_name := null;
5755 END IF ;
5756
5757 ELSIF l_share_type IN ('SHARE_PARTIAL','SHARE_FULL') THEN
5758
5759 -- Get the default task id for OKE
5760 OPEN share_partial_or_full(l_struct_version_id) ;
5761 i:=0 ;
5762 LOOP
5763 i:=i+1 ;
5764 FETCH share_partial_or_full INTO x_oke_task_id, x_oke_task_name, x_oke_task_number ;
5765 EXIT WHEN (share_partial_or_full%NOTFOUND OR i>2) ;
5766 END LOOP;
5767 CLOSE share_partial_or_full ;
5768
5769 IF i>2 THEN
5770 x_oke_task_id := null ;
5771 x_oke_task_name := null;
5772 x_oke_task_number := null;
5773 END IF ;
5774
5775 IF l_debug_mode = 'Y' THEN
5776 pa_debug.write(g_module_name,'i: ' || i,3);
5777 END IF;
5778
5779 -- Get the default task id for BILLING
5780 OPEN share_partial_or_full_bill(l_struct_version_id) ;
5781 i:=0 ;
5782 LOOP
5783 i:=i+1 ;
5784 FETCH share_partial_or_full_bill INTO x_bill_task_id, x_bill_task_name, x_bill_task_number ;
5785 EXIT WHEN (share_partial_or_full_bill%NOTFOUND OR i>2) ;
5786 END LOOP;
5787 CLOSE share_partial_or_full_bill ;
5788
5789 IF l_debug_mode = 'Y' THEN
5790 pa_debug.write(g_module_name,'i: ' || i,3);
5791 END IF;
5792
5793 IF i>2 THEN
5794 x_bill_task_id := null ;
5795 x_bill_task_name := null;
5796 x_bill_task_number := null;
5797 END IF ;
5798
5799 END IF ;
5800
5801 IF l_debug_mode = 'Y' THEN
5802 pa_debug.write(g_module_name,'x_oke_task_id: ' || x_oke_task_id,3);
5803 pa_debug.write(g_module_name,'x_oke_task_name: ' || x_oke_task_name,3);
5804 pa_debug.write(g_module_name,'x_oke_task_number: ' || x_oke_task_number,3);
5805 pa_debug.write(x_bill_task_id,'x_bill_task_id: ' || x_bill_task_id,3);
5806 pa_debug.write(g_module_name,'x_bill_task_name: ' || x_bill_task_name,3);
5807 pa_debug.write(g_module_name,'x_bill_task_number: ' || x_bill_task_number,3);
5808 END IF;
5809
5810
5811 IF l_debug_mode = 'Y' THEN
5812 pa_debug.g_err_stage:= 'Exiting GET_DEFAULT_TASK' ;
5813 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
5814 pa_debug.reset_curr_function;
5815 END IF;
5816 EXCEPTION
5817 WHEN OTHERS THEN
5818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5819 x_msg_count := 1;
5820 x_msg_data := SQLERRM;
5821
5822 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLE_UTILS'
5823 ,p_procedure_name => 'GET_DEFAULT_TASK');
5824
5825 IF l_debug_mode = 'Y' THEN
5826 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
5827 pa_debug.write('GET_DEFAULT_TASK: ' || g_module_name,pa_debug.g_err_stage,5);
5828 pa_debug.reset_curr_function;
5829 END IF;
5830 RAISE;
5831 END GET_DEFAULT_TASK;
5832
5833
5834
5835 -- SubProgram : IS_SHIPPING_INITIATED ( 3555460 )
5836 -- Type : UTIL FUNCTION
5837 -- Purpose : This function will 'Y' if shipping is initiated for deliverable
5838 -- 'N' if shipping is not initiated
5839 -- Note :
5840 -- Assumptions : None
5841 -- Parameter IN/OUT Type Required Description and Purpose
5842 -- --------------------------- ------------ ----------- --------- ---------------------------
5843 -- p_dlv_element_id IN NUMBER Yes Deliverale Element ID
5844 -- p_dlv_version_id IN NUMBER Yes Deliverable Version Id
5845
5846 FUNCTION IS_SHIPPING_INITIATED
5847 (
5848 p_dlv_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
5849 ,p_dlv_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
5850 ) RETURN VARCHAR2
5851 IS
5852 CURSOR ship_flag_dlv IS
5853 SELECT 'Y'
5854 FROM dual
5855 WHERE EXISTS ( SELECT 'Y'
5856 FROM pa_object_relationships obj
5857 ,pa_proj_element_versions ver
5858 WHERE obj.object_id_from2 = p_dlv_element_id
5859 AND obj.object_type_to = 'PA_ACTIONS'
5860 AND obj.object_type_from = 'PA_DELIVERABLES'
5861 AND obj.object_id_to2 = ver.proj_element_id
5862 AND obj.relationship_type = 'A'
5863 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
5864 AND nvl(OKE_DELIVERABLE_UTILS_PUB.WSH_Initiated_Yn(ver.element_version_id),'N') = 'Y'
5865 ) ;
5866 l_dummy VARCHAR2(1) := 'N' ;
5867 l_return_status VARCHAR2(1) := 'N' ;
5868 BEGIN
5869 OPEN ship_flag_dlv;
5870 FETCH ship_flag_dlv into l_dummy ;
5871 IF ship_flag_dlv%found THEN
5872 l_return_status:='Y';
5873 ELSE
5874 l_return_status:='N';
5875 END IF;
5876 CLOSE ship_flag_dlv;
5877 return l_return_status;
5878 END IS_SHIPPING_INITIATED ;
5879
5880 -- SubProgram : IS_PROCUREMENT_INITIATED ( 3555460 )
5881 -- Type : UTIL FUNCTION
5882 -- Purpose : This function will 'Y' if procurement is initiated for deliverable
5883 -- 'N' if procurement is not initiated
5884 -- Note :
5885 -- Assumptions : None
5886 -- Parameter IN/OUT Type Required Description and Purpose
5887 -- --------------------------- ------------ ----------- --------- ---------------------------
5888 -- p_dlv_element_id IN NUMBER Yes Deliverale Element ID
5889 -- p_dlv_version_id IN NUMBER Yes Deliverable Version Id
5890
5891
5892 FUNCTION IS_PROCUREMENT_INITIATED
5893 (
5894 p_dlv_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
5895 ,p_dlv_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
5896 ) RETURN VARCHAR2
5897 IS
5898 CURSOR proc_flag_dlv IS
5899 SELECT 'Y'
5900 FROM dual
5901 WHERE EXISTS ( SELECT 'Y'
5902 FROM pa_object_relationships obj
5903 ,pa_proj_element_versions ver
5904 WHERE obj.object_id_from2 = p_dlv_element_id
5905 AND obj.object_type_to = 'PA_ACTIONS'
5906 AND obj.object_type_from = 'PA_DELIVERABLES'
5907 AND obj.object_id_to2 = ver.proj_element_id
5908 AND obj.relationship_type = 'A'
5909 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
5910 AND nvl(OKE_DELIVERABLE_UTILS_PUB.REQ_Initiated_Yn(ver.element_version_id),'N') = 'Y'
5911 ) ;
5912 l_dummy VARCHAR2(1) := 'N' ;
5913 l_return_status VARCHAR2(1) := 'N' ;
5914 BEGIN
5915 OPEN proc_flag_dlv;
5916 FETCH proc_flag_dlv into l_dummy ;
5917 IF proc_flag_dlv%found THEN
5918 l_return_status:='Y';
5919 ELSE
5920 l_return_status:='N';
5921 END IF;
5922 CLOSE proc_flag_dlv;
5923 return l_return_status;
5924 END IS_PROCUREMENT_INITIATED ;
5925
5926 -- SubProgram : IS_BILLING_EVENT_PROCESSED ( 3555460 )
5927 -- Type : UTIL FUNCTION
5928 -- Purpose : This function will 'Y' if billing event is processed for deliverable
5929 -- 'N' if billing event is not processed
5930 -- Note :
5931 -- Assumptions : None
5932 -- Parameter IN/OUT Type Required Description and Purpose
5933 -- --------------------------- ------------ ----------- --------- ---------------------------
5934 -- p_dlv_element_id IN NUMBER Yes Deliverale Element ID
5935 -- p_dlv_version_id IN NUMBER Yes Deliverable Version Id
5936
5937
5938 FUNCTION IS_BILLING_EVENT_PROCESSED
5939 (
5940 p_dlv_element_id IN PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
5941 ,p_dlv_version_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
5942 ) RETURN VARCHAR2
5943 IS
5944 CURSOR bill_flag_dlv IS
5945 SELECT 'Y'
5946 FROM dual
5947 WHERE EXISTS ( SELECT 'Y'
5948 FROM pa_object_relationships obj
5949 ,pa_proj_element_versions ver
5950 WHERE obj.object_id_from2 = p_dlv_element_id
5951 AND obj.object_type_to = 'PA_ACTIONS'
5952 AND obj.object_type_from = 'PA_DELIVERABLES'
5953 AND obj.object_id_to2 = ver.proj_element_id
5954 AND obj.relationship_type = 'A'
5955 AND obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
5956 AND nvl(PA_BILLING_WRKBNCH_EVENTS.CHECK_DELV_EVENT_PROCESSED(ver.project_id,p_dlv_version_id,ver.element_version_id),'N') = 'Y'
5957 ) ;
5958 l_dummy VARCHAR2(1) := 'N' ;
5959 l_return_status VARCHAR2(1) := 'N' ;
5960 BEGIN
5961 OPEN bill_flag_dlv;
5962 FETCH bill_flag_dlv into l_dummy ;
5963 IF bill_flag_dlv%found THEN
5964 l_return_status:='Y';
5965 ELSE
5966 l_return_status:='N';
5967 END IF;
5968 CLOSE bill_flag_dlv;
5969 return l_return_status;
5970 END IS_BILLING_EVENT_PROCESSED ;
5971
5972
5973 -- Procedure : GET_BILLING_DETAILS ( 3622126 )
5974 -- Type : UTILITY
5975 -- Purpose : To return billing action description and completion date
5976 -- Note : Retrieve action description and completion date
5977 -- :
5978 -- Assumptions : None
5979
5980 -- Parameters Type Required Description and Purpose
5981 -- --------------------------- ------ -------- --------------------------------------------------------
5982 -- p_action_version_id NUMBER Y Delivearble Action Version Id
5983 -- x_bill_completion_date DATE Billing Event Date
5984 -- x_bill_description VARCHAR2 Billing Action Description
5985
5986 PROCEDURE GET_BILLING_DETAILS
5987 (
5988 p_action_version_id IN PA_PROJ_ELEM_VER_SCHEDULE.ELEMENT_VERSION_ID%TYPE
5989 ,x_bill_completion_date OUT NOCOPY PA_PROJ_ELEM_VER_SCHEDULE.ACTUAL_FINISH_DATE%TYPE --File.Sql.39 bug 4440895
5990 ,x_bill_description OUT NOCOPY PA_PROJ_ELEMENTS.DESCRIPTION%TYPE --File.Sql.39 bug 4440895
5991 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5992 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5993 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5994 )
5995 IS
5996
5997 CURSOR bill_details IS
5998 SELECT
5999 PPE.DESCRIPTION
6000 ,PES.ACTUAL_FINISH_DATE
6001 FROM
6002 PA_PROJ_ELEMENTS PPE,
6003 PA_PROJ_ELEM_VER_SCHEDULE PES
6004 WHERE
6005 PES.ELEMENT_VERSION_ID = p_action_version_id
6006 AND PES.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
6007 AND PPE.OBJECT_TYPE = 'PA_ACTIONS'
6008 AND PPE.FUNCTION_CODE = 'BILLING'
6009 AND PPE.PROJECT_ID = PES.PROJECT_ID ;
6010
6011
6012 l_debug_mode VARCHAR2(1) ;
6013
6014 BEGIN
6015
6016 x_msg_count := 0;
6017 x_return_status := FND_API.G_RET_STS_SUCCESS;
6018 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
6019
6020 IF l_debug_mode = 'Y' THEN
6021 PA_DEBUG.set_curr_function( p_function => 'GET_BILLING_DETAILS'
6022 ,p_debug_mode => l_debug_mode );
6023 pa_debug.g_err_stage:= 'Inside GET_BILLING_DETAILS ';
6024 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
6025 END IF;
6026
6027 OPEN bill_details;
6028 FETCH bill_details into x_bill_description, x_bill_completion_date ;
6029 CLOSE bill_details;
6030
6031 IF l_debug_mode = 'Y' THEN
6032 pa_debug.g_err_stage:= 'Exiting GET_BILLING_DETAILS' ;
6033 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
6034 pa_debug.reset_curr_function;
6035 END IF;
6036
6037 EXCEPTION
6038 WHEN OTHERS THEN
6039 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6040 x_msg_count := 1;
6041 x_msg_data := SQLERRM;
6042
6043 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DELIVERABLE_UTILS'
6044 ,p_procedure_name => 'GET_BILLING_DETAILS');
6045
6046 IF l_debug_mode = 'Y' THEN
6047 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
6048 pa_debug.write('GET_BILLING_DETAILS: ' || g_module_name,pa_debug.g_err_stage,5);
6049 pa_debug.reset_curr_function;
6050 END IF;
6051 RAISE;
6052 END GET_BILLING_DETAILS;
6053
6054
6055 -- SubProgram : GET_TASK_INFO ( 3651340 )
6056 -- Type : UTIL FUNCTION
6057 -- Purpose : This function returns task version id if p_task_or_struct is 'T'
6058 -- parent structure version id if p_task_or_struct is 'S'
6059 -- Note :
6060 -- Assumptions : None
6061 -- Parameter IN/OUT Type Required Description and Purpose
6062 -- --------------------------- ------------ ----------- --------- ---------------------------
6063 -- p_project_id IN NUMBER Yes Project Id
6064 -- p_task_id IN NUMBER Yes proj element id of task
6065 -- p_task_or_struct IN VARCHAR2 Yes Valid Values are ( 'T' - for task version id, 'S' -
6066 -- for parent structure version id
6067
6068 FUNCTION GET_TASK_INFO
6069 (
6070 p_project_id IN NUMBER
6071 ,p_task_id IN NUMBER
6072 ,p_task_or_struct IN VARCHAR2
6073 ) RETURN NUMBER
6074 IS
6075
6076 cursor c_task_exists(l_struct_ver_id IN NUMBER) IS
6077 SELECT
6078 PEV.ELEMENT_VERSION_ID
6079 FROM
6080 PA_PROJ_ELEMENT_VERSIONS PEV
6081 WHERE
6082 PEV.PARENT_STRUCTURE_VERSION_ID = l_struct_ver_id
6083 AND PEV.PROJ_ELEMENT_ID = p_task_id
6084 AND PEV.PROJECT_ID = p_project_id;
6085
6086 -- 3651340 added desc to order by clause in cursor
6087
6088 CURSOR c_task_in_wp_version IS
6089 SELECT PPEVS.ELEMENT_VERSION_ID STRUCT_VER_ID
6090 ,PEV.ELEMENT_VERSION_ID TASK_VER_ID
6091 FROM
6092 PA_PROJ_ELEM_VER_STRUCTURE PPEVS
6093 ,PA_PROJ_ELEMENT_VERSIONS PEV
6094 WHERE PEV.PROJECT_ID = p_project_id
6095 AND PEV.PARENT_STRUCTURE_VERSION_ID = PPEVS.ELEMENT_VERSION_ID
6096 AND PEV.PROJ_ELEMENT_ID = p_task_id
6097 AND PEV.PROJECT_ID = PPEVS.PROJECT_ID
6098 AND ROWNUM < 2
6099 ORDER BY PPEVS.LAST_UPDATE_DATE DESC;
6100
6101 l_latest_pub_wp_struct_id NUMBER := NULL;
6102 l_current_working_wp_struct_id NUMBER := NULL;
6103 l_task_ver_id NUMBER := NULL;
6104 l_wp_structure_ver_id NUMBER := NULL;
6105
6106 BEGIN
6107
6108 -- retrieve published wp structure version id
6109 -- if task is in in latest published wp verion, return task version id and parent structure version id
6110
6111 l_latest_pub_wp_struct_id := PA_PROJ_ELEMENTS_UTILS.latest_published_ver_id(p_project_id);
6112
6113 IF l_latest_pub_wp_struct_id IS NOT NULL THEN
6114
6115 OPEN c_task_exists(l_latest_pub_wp_struct_id);
6116 FETCH c_task_exists into l_task_ver_id;
6117 CLOSE c_task_exists;
6118
6119 l_wp_structure_ver_id := l_latest_pub_wp_struct_id;
6120
6121 END IF;
6122
6123 -- if task is not in the latest published version, retrieve current working structure version id
6124 -- if task is in the current working version, return task version id and parent structure version id
6125
6126 IF l_task_ver_id IS NULL THEN
6127
6128 l_current_working_wp_struct_id := PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(p_project_id);
6129
6130 IF l_current_working_wp_struct_id IS NOT NULL THEN
6131
6132 OPEN c_task_exists(l_current_working_wp_struct_id);
6133 FETCH c_task_exists into l_task_ver_id ;
6134 CLOSE c_task_exists;
6135 l_wp_structure_ver_id := l_current_working_wp_struct_id;
6136 END IF;
6137
6138 END IF;
6139
6140 -- if task is not in current working versioin, retrieve the last updated structure version
6141 -- for which task is associated to structure
6142
6143 IF l_task_ver_id IS NULL THEN
6144
6145 OPEN c_task_in_wp_version;
6146 FETCH c_task_in_wp_version into l_wp_structure_ver_id, l_task_ver_id;
6147 CLOSE c_task_in_wp_version;
6148
6149 END IF;
6150
6151 -- if p_task_or_struct is 'T' then return task version id
6152 -- if p_task_or_struct is 'S' then return parent structure version id
6153
6154 IF p_task_or_struct = 'T' THEN
6155 return l_task_ver_id;
6156 ELSIF p_task_or_struct = 'S' THEN
6157 return l_wp_structure_ver_id;
6158 END IF;
6159
6160 END GET_TASK_INFO;
6161
6162 -- added for bug# 3911050
6163 -- SubProgram : GET_SHIP_PROC_ACTN_DETAIL ( 3911050 )
6164 -- Type : UTIL FUNCTION
6165 -- Purpose : This function returns shipping and procurement action details ( name, id, due date )
6166 -- Note : OKE is calling this procedure while create dlvr to default dlvr type actions to dlvr
6167 -- Assumptions : None
6168 -- Parameter IN/OUT Type Required Description and Purpose
6169 -- --------------------------- ------------ ----------- --------- ---------------------------
6170 -- p_dlvr_id IN NUMBER Yes Dlvr Ver Id
6171 -- x_ship_id OUT NUMBER Shipping Action Ver Id
6172 -- x_ship_name OUT VARCHAR2 Shipping Action Name
6173 -- x_ship_due_date OUT Date Shipping Due Date
6174 -- x_proc_id OUT NUMBER Procurement Action Ver Id
6175 -- x_proc_name OUT VARCHAR2 Procurement Action Name
6176 -- x_proc_due_date OUT Date Procurement Due Date
6177 --
6178
6179 PROCEDURE GET_SHIP_PROC_ACTN_DETAIL
6180 (
6181 p_dlvr_id IN PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE
6182 ,x_ship_id OUT NOCOPY PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE --File.Sql.39 bug 4440895
6183 ,x_ship_name OUT NOCOPY PA_PROJ_ELEMENTS.NAME%TYPE --File.Sql.39 bug 4440895
6184 ,x_ship_due_date OUT NOCOPY PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE --File.Sql.39 bug 4440895
6185 ,x_proc_id OUT NOCOPY PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE --File.Sql.39 bug 4440895
6186 ,x_proc_name OUT NOCOPY PA_PROJ_ELEMENTS.NAME%TYPE --File.Sql.39 bug 4440895
6187 ,x_proc_due_date OUT NOCOPY PA_PROJ_ELEM_VER_SCHEDULE.SCHEDULED_FINISH_DATE%TYPE --File.Sql.39 bug 4440895
6188 )
6189 IS
6190
6191 CURSOR l_ship_info_csr
6192 IS
6193 select
6194 obj.object_id_to1
6195 ,ppe.name
6196 ,ppevs.scheduled_finish_date
6197 from
6198 pa_object_relationships obj
6199 ,pa_proj_elements ppe
6200 ,pa_proj_elem_ver_schedule ppevs
6201 ,pa_proj_element_versions pev
6202 where
6203 pev.element_version_id = p_dlvr_id
6204 and obj.object_id_from2 = pev.proj_element_id
6205 and pev.object_type = 'PA_DELIVERABLES'
6206 and obj.object_type_from = 'PA_DELIVERABLES'
6207 and obj.object_type_to = 'PA_ACTIONS'
6208 and obj.relationship_type = 'A'
6209 and obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
6210 and ppe.proj_element_id = obj.object_id_to2
6211 and ppe.proj_element_id = ppevs.proj_element_id
6212 and ppe.project_id = ppevs.project_id
6213 and ppe.function_code = 'SHIPPING';
6214
6215 CURSOR l_proc_info_csr
6216 IS
6217 select
6218 obj.object_id_to1
6219 ,ppe.name
6220 ,ppevs.scheduled_finish_date
6221 from
6222 pa_object_relationships obj
6223 ,pa_proj_elements ppe
6224 ,pa_proj_elem_ver_schedule ppevs
6225 ,pa_proj_element_versions pev
6226 where
6227 pev.element_version_id = p_dlvr_id
6228 and obj.object_id_from2 = pev.proj_element_id
6229 and pev.object_type = 'PA_DELIVERABLES'
6230 and obj.object_type_from = 'PA_DELIVERABLES'
6231 and obj.object_type_to = 'PA_ACTIONS'
6232 and obj.relationship_type = 'A'
6233 and obj.relationship_subtype = 'DELIVERABLE_TO_ACTION'
6234 and ppe.proj_element_id = obj.object_id_to2
6235 and ppe.proj_element_id = ppevs.proj_element_id
6236 and ppe.project_id = ppevs.project_id
6237 and ppe.function_code = 'PROCUREMENT';
6238
6239 BEGIN
6240
6241 OPEN l_ship_info_csr;
6242 FETCH l_ship_info_csr into x_ship_id, x_ship_name, x_ship_due_date ;
6243 CLOSE l_ship_info_csr;
6244
6245 OPEN l_proc_info_csr;
6246 FETCH l_proc_info_csr into x_proc_id, x_proc_name, x_proc_due_date ;
6247 CLOSE l_proc_info_csr;
6248
6249 END GET_SHIP_PROC_ACTN_DETAIL;
6250
6251 END PA_DELIVERABLE_UTILS;
[Home] [Help]