1 package body PA_PROJ_STRUCTURE_UTILS as
2 /* $Header: PAXSTRUB.pls 120.7 2010/08/08 08:36:16 kmaddi ship $ */
3
4 -- Bug Fix 5611909. Creating global variables to cache the project id and budget version id.
5 -- These will be used in the program unit Get_All_Wbs_Rejns and these will be set by using
6 -- the set_budget_version_id_global procedure.
7 -- NOTE: PLEASE DO NOT MODIFY THESE ANYWHERE ELSE OR USING ANY OTHER MEANS.
8
9 --
10 procedure CHECK_LOOPED_PROJECT
11 (
12 p_api_version IN NUMBER := 1.0,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
16 p_debug_mode IN VARCHAR2 := 'N',
17 p_task_id IN NUMBER,
18 p_project_id IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
20 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
21 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
22 )
23 IS
24 l_looped VARCHAR2(1);
25 Begin
26 pa_debug.set_err_stack('CHECK_LOOPED_PROJECT');
27
28 --Block to check if this project is looped.
29 Begin
30 --check if it is looped
31 select 'Y'
32 into l_looped
33 from
34 -- bug 7573061 : skkoppul - changed table from pa_object_relationships to the below
35 (
36 SELECT * FROM pa_object_relationships
37 WHERE relationship_type IN ('LW','LF')
38 )
39 where
40 -- Bug 5589038 object_id_to1 =
41 object_id_to2 =
42 (
43 select
44 project_id
45 from
46 pa_tasks t
47 where
48 t.task_id = p_task_id
49 )
50 start with object_id_from2 = p_project_id
51 -- Bug 5589038 connect by object_id_from2 = PRIOR object_id_to1;
52 connect by object_id_from2 = PRIOR object_id_to2;
53 EXCEPTION
54 When NO_DATA_FOUND Then
55 l_looped := 'N';
56 End;
57
58
59 IF (l_looped = 'N') Then
60 --Block to check if the task is linking to its owning project
61 BEGIN
62 select 'Y'
63 into l_looped
64 from
65 pa_tasks t2
66 where
67 t2.task_id = p_task_id and
68 t2.project_id = p_project_id;
69 EXCEPTION
70 When NO_DATA_FOUND Then
71 l_looped := 'N';
72 END;
73 end if;
74
75 IF (l_looped = 'Y') THEN --There is a loop
76 x_return_status := FND_API.G_RET_STS_ERROR;
77 elsif (l_looped = 'N') THEN
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79 else
80 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
81 end if;
82
83 x_msg_count := 0;
84 x_msg_data := null;
85
86 pa_debug.reset_err_stack;
87 EXCEPTION
88 When OTHERS Then
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
90 RAISE;
91 END CHECK_LOOPED_PROJECT;
92
93
94
95 procedure CHECK_MERGED_PROJECT
96 (
97 p_api_version IN NUMBER := 1.0,
98 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
99 p_commit IN VARCHAR2 := FND_API.G_FALSE,
100 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
101 p_debug_mode IN VARCHAR2 := 'N',
102 p_task_id IN NUMBER,
103 p_project_id IN NUMBER,
104 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
105 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
106 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
107 )
108 IS
109 l_linked_id PA_OBJECT_RELATIONSHIPS.OBJECT_ID_FROM2%TYPE;
110 l_exist VARCHAR2(1);
111 l_linked VARCHAR2(1);
112
113 /* Commented for Bug5589038
114 CURSOR get_same_project(l_task_id NUMBER, l_project_id VARCHAR2) IS
115 select 'Y'
116 from
117 pa_object_relationships r,
118 pa_tasks t
119 where
120 r.object_id_to1 = l_project_id and
121 r.object_type_to = 'PA_PROJECTS' and
122 r.relationship_type = 'H' and
123 r.object_id_from2 = t.project_id and
124 t.task_id = l_task_id; */
125 -- object_id_from1 = l_task_id and
126 -- object_type_from = 'PA_TASKS' and
127 -- relationship_type = 'H';
128
129 -- Modified the above cursor for Bug Bug5589038
130 CURSOR get_same_project(l_task_id NUMBER, l_project_id VARCHAR2) IS
131 select 'Y'
132 from
133 pa_object_relationships r,
134 pa_tasks t
135 where
136 r.object_id_to2 = l_project_id and
137 r.object_type_to = 'PA_STRUCTURES' and
138 (r.relationship_type = 'LF' or r.relationship_type = 'LW') and
139 r.object_id_from2 = t.project_id and
140 t.task_id = l_task_id;
141
142 CURSOR get_merged_projects(l_task_id VARCHAR2, l_project_id VARCHAR2) IS
143 (
144 select
145 object_id_from2
146 from
147 -- bug 7573061 : skkoppul - changed table from pa_object_relationships to the below
148 (
149 select * from pa_object_relationships
150 where relationship_type in ('LW','LF')
151 )
152
153 start with
154 -- Bug5589038 object_id_to1 =
155 object_id_to2 =
156 (
157 select
158 project_id
159 from
160 pa_tasks t
161 where
162 t.task_id = l_task_id
163 )
164 --Bug5589038 connect by PRIOR object_id_from2 = object_id_to1
165 connect by PRIOR object_id_from2 = object_id_to2
166 union
167 select
168 project_id
169 from
170 pa_tasks
171 where
172 task_id = l_task_id
173 )
174 intersect
175 select
176 object_id_from2
177 from
178 -- bug 7573061 : skkoppul - changed table from pa_object_relationships to the below
179 (
180 select * from pa_object_relationships
181 where relationship_type in ('LW','LF')
182 )
183 -- Bug5589038 start with object_id_to1 = l_project_id
184 start with object_id_to2 = l_project_id
185 -- Bug5589038 connect by PRIOR object_id_from2 = object_id_to1;
186 connect by PRIOR object_id_from2 = object_id_to2;
187
188 Begin
189 pa_debug.set_err_stack('CHECK_MERGED_PROJECT');
190 --Block to check if this project is linked.
191 Begin
192 OPEN get_same_project(p_task_id, to_char(p_project_id));
193 FETCH get_same_project into l_exist;
194 CLOSE get_same_project;
195 IF (l_exist IS NULL) THEN
196 -- This row does not exist; no duplicates.
197 Begin
198 --check if it is merged
199 OPEN get_merged_projects(to_char(p_task_id), to_char(p_project_id));
200 LOOP
201 FETCH get_merged_projects INTO l_linked_id;
202 EXIT WHEN get_merged_projects%FOUND;
203 l_linked_id := null;
204 EXIT WHEN get_merged_projects%NOTFOUND;
205 END LOOP;
206 CLOSE get_merged_projects;
207
208 IF(l_linked_id IS NULL) THEN
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 ELSE
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 END IF;
213
214 EXCEPTION
215 When NO_DATA_FOUND Then
216 l_linked := 'N';
217 End;
218 ELSE
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 END IF;
221 END;
222
223 x_msg_count := 0;
224 x_msg_data := null;
225 pa_debug.reset_err_stack;
226 EXCEPTION
227 When OTHERS Then
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 RAISE;
230 END CHECK_MERGED_PROJECT;
231
232
233 function CHECK_PROJECT_CONTRACT_EXISTS
234 (
235 p_project_id IN NUMBER
236 )
237 return VARCHAR2
238 IS
239 output VARCHAR2(1);
240 retval VARCHAR2(1);
241 msg_count NUMBER;
242 msg_data VARCHAR2(2000);
243 ret_status VARCHAR2(10);
244 BEGIN
245 pa_debug.set_err_stack('CHECK_PROJECT_CONTRACT_EXISTS');
246 retval := FND_API.G_RET_STS_SUCCESS;
247
248 /*
249 -- Old code
250 output := OKE_UTILS.Project_Used(p_project_id);
251 */
252
253 OKE_PA_CHECKS_PUB.Project_Used(p_api_version => 1.0,
254 p_commit => FND_API.G_TRUE,
255 p_init_msg_list => FND_API.G_FALSE,
256 x_msg_count => msg_count,
257 x_msg_data => msg_data,
258 x_return_status => ret_status,
259 Project_ID => p_project_id,
260 X_Result => output
261 ); --Commented the call to OKE_PA_CHECKS_PUB.Project_Used
262 IF (output = 'Y') THEN
263 -- project is used, association exists, return error
264 retval := FND_API.G_RET_STS_ERROR;
265 ELSE
266 -- no association exists, return success
267 retval := FND_API.G_RET_STS_SUCCESS;
268 END IF;
269
270
271 pa_debug.reset_err_stack;
272 return retval;
273 EXCEPTION
274 When OTHERS Then
275 return FND_API.G_RET_STS_UNEXP_ERROR;
276 END CHECK_PROJECT_CONTRACT_EXISTS;
277
278
279 function CHECK_TASK_CONTRACT_EXISTS
280 (
281 p_task_id IN NUMBER
282 )
283 return VARCHAR2
284 IS
285 output VARCHAR2(1);
286 retval VARCHAR2(1);
287 msg_count NUMBER;
288 msg_data VARCHAR2(2000);
289 ret_status VARCHAR2(10);
290 BEGIN
291 pa_debug.set_err_stack('CHECK_TASK_CONTRACT_EXISTS');
292 retval := FND_API.G_RET_STS_SUCCESS;
293
294 /*
295 -- Old code
296 output := OKE_UTILS.Task_Used(p_task_id);
297 */
298 OKE_PA_CHECKS_PUB.Task_Used(p_api_version => 1.0,
299 p_commit => FND_API.G_TRUE,
300 p_init_msg_list => FND_API.G_FALSE,
301 x_msg_count => msg_count,
302 x_msg_data => msg_data,
303 x_return_status => ret_status,
304 Task_ID => p_task_id,
305 x_Result => output
306 ); --Commented the call to OKE_PA_CHECKS_PUB.Project_Used
307 IF (output = 'Y') THEN
308 -- project is used, association exists, return error
309 retval := FND_API.G_RET_STS_ERROR;
310 ELSE
311 -- no association exists, return success
312 retval := FND_API.G_RET_STS_SUCCESS;
313 END IF;
314
315 pa_debug.reset_err_stack;
316 return retval;
317 EXCEPTION
318 When OTHERS Then
319 return FND_API.G_RET_STS_UNEXP_ERROR;
320 END CHECK_TASK_CONTRACT_EXISTS;
321 -- FP M: Project Execution Workflow Changes
322 FUNCTION IS_WF_ENABLED_FOR_STRUCTURE(
323 p_project_id IN NUMBER
324 ,p_structure_type IN VARCHAR2
325 )
326 RETURN VARCHAR2
327 IS
328 -- This cursor selects the enable_wf_flag
329 -- for the given structure and for given project.
330 CURSOR C IS
331 SELECT ENABLE_WF_FLAG
332 FROM PA_PROJ_ELEMENTS ppe,
333 PA_PROJ_STRUCTURE_TYPES pps,
334 PA_STRUCTURE_TYPES pst
335 WHERE ppe.project_id = p_project_id
336 AND ppe.object_type = 'PA_STRUCTURES'
337 AND ppe.proj_element_id = pps.proj_element_id
338 AND pps.structure_type_id = pst.structure_type_id
339 AND pst.structure_type_class_code = p_structure_type ;
340
341 l_dummy VARCHAR2(1) := 'N' ;
342 BEGIN
343 OPEN C;
344 FETCH C INTO l_dummy ;
345 IF C%NOTFOUND THEN
346 l_dummy := 'N';
347 END IF ;
348 CLOSE C;
349 RETURN nvl(l_dummy,'N') ;
350 EXCEPTION
351 WHEN OTHERS THEN
352 return 'N';
353 END IS_WF_ENABLED_FOR_STRUCTURE ;
354
355 function Get_All_Wbs_Rejns(
356 p_project_id IN Number,
357 p_calling_mode IN Varchar2 Default 'PROJ_STR_VER',
358 p_proj_str_version_id IN Number,
359 p_Task_str_version_id IN Number Default Null,
360 p_start_date IN Date Default Null,
361 p_end_date IN Date Default Null
362 )
363 return VARCHAR2
364 IS
365 f1 varchar2(2000);
366 f2 varchar2(2000);
367 f3 varchar2(2000);
368 f4 varchar2(2000);
369 f5 varchar2(2000);
370 f6 varchar2(2000);
371 rs varchar2(2000);
372 returnflag varchar2(2000);
373
374
375 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
376
377 BEGIN
378
379 -- Bug Fix 5611909.
380 -- Caching the budget_version id and passing it to the PA_FIN_PLAN_UTILS2.Get_WbsBdgtLineRejns.
381 -- Get the budget_version_id from the db for the following conditions.
382 -- 1) This API is called for the first time. Both globals are NULL.
383 -- 2) This API is called for a different project id other than the global project id.
384
385 IF
386 (((PA_PROJ_STRUCTURE_UTILS.G_PROJECT_ID IS NULL) AND (PA_PROJ_STRUCTURE_UTILS.G_BUDGET_VERSION_ID IS NULL))
387 OR
388 (PA_PROJ_STRUCTURE_UTILS.G_PROJECT_ID <> p_project_ID)) THEN
389
390 l_budget_version_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id
391 (p_project_id => p_project_id
392 ,p_plan_type_id => null
393 ,p_proj_str_ver_id => p_proj_str_version_id);
394
395 set_budget_version_id_global(p_project_id,l_budget_version_id);
396
397 END IF;
398
399 PA_FIN_PLAN_UTILS2.Get_WbsBdgtLineRejns
400 (p_project_id => p_project_id,
401 p_calling_mode => p_calling_mode,
402 p_proj_str_version_id => p_proj_str_version_id,
403 p_Task_str_version_id => p_Task_str_version_id,
404 p_start_date => p_start_date,
405 p_end_date => p_end_date,
406 p_budget_version_id => PA_PROJ_STRUCTURE_UTILS.G_BUDGET_VERSION_ID,
407 x_cost_rejn_flag => f1,
408 x_burden_rejn_flag => f2,
409 x_revenue_rejn_flag => f3,
410 x_pc_conv_rejn_flag => f4,
411 x_pfc_conv_rejn_flag => f5,
412 x_projstrlvl_rejn_flag => f6,
413 x_return_status => rs);
414 returnflag := 'N';
418 IF (f1 = 'Y') THEN returnflag := 'Y'; END IF;
415 IF (p_calling_mode = 'PROJ_STR_VER') THEN
416 IF (f6 = 'Y') THEN returnflag := 'Y'; END IF;
417 ELSE
419 IF (f2 = 'Y') THEN returnflag := 'Y'; END IF;
420 IF (f3 = 'Y') THEN returnflag := 'Y'; END IF;
421 IF (f4 = 'Y') THEN returnflag := 'Y'; END IF;
422 IF (f5 = 'Y') THEN returnflag := 'Y'; END IF;
423 END IF;
424 RETURN (returnflag);
425 END Get_All_Wbs_Rejns;
426
427
428 --bug 4290593
429 function CHECK_STR_TEMP_TAB_POPULATED(p_project_id NUMBER) RETURN VARCHAR2 IS
430 CURSOR cur_str_tmp
431 IS
432 SELECT 'x' FROM pa_structures_tasks_tmp
433 WHERE parent_project_id=p_project_id
434 and rownum < 2;
435
436 l_dummy VARCHAR2(1);
437 BEGIN
438 OPEN cur_str_tmp;
439 FETCH cur_str_tmp INTO l_dummy;
440 IF cur_str_tmp%FOUND
441 THEN
442 CLOSE cur_str_tmp;
443 RETURN 'Y';
444 ELSE
445 CLOSE cur_str_tmp;
446 RETURN 'N';
447 END IF;
448 END CHECK_STR_TEMP_TAB_POPULATED;
449
450 function CHECK_PJI_TEMP_TAB_POPULATED(p_project_id NUMBER) RETURN VARCHAR2 IS
451 CURSOR cur_pji_tmp
452 IS
453 SELECT 'x' FROM pji_fm_xbs_accum_tmp1
454 WHERE project_id=p_project_id
455 and rownum < 2;
456
457 l_dummy VARCHAR2(1);
458 BEGIN
459 OPEN cur_pji_tmp;
460 FETCH cur_pji_tmp INTO l_dummy;
461 IF cur_pji_tmp%FOUND
462 THEN
463 CLOSE cur_pji_tmp;
464 RETURN 'Y';
465 ELSE
466 CLOSE cur_pji_tmp;
467 RETURN 'N';
468 END IF;
469 END CHECK_PJI_TEMP_TAB_POPULATED;
470 --end bug 4290593
471
472 -- Bug Fix 5611909. Creating global variables to cache the project id and budget version id.
473 -- These will be used in the program unit Get_All_Wbs_Rejns and these will be set by using
474 -- the set_budget_version_id_global procedure.
475 -- NOTE: PLEASE DO NOT MODIFY THESE ANYWHERE ELSE OR USING ANY OTHER MEANS.
476
477 PROCEDURE set_budget_version_id_global (p_project_id IN NUMBER,
478 p_budget_version_id IN NUMBER) IS
479
480 BEGIN
481
482 --Begin: 6046307: commented out the following IF condition that 'RAISE FND_API.G_EXC_ERROR' when 'p_project_id' or 'p_budget_version_id' are NULL
483 /*
484 IF p_project_id IS NULL OR p_budget_version_id IS NULL THEN
485 RAISE FND_API.G_EXC_ERROR;
486 END IF;
487 */
488 --End: 6046307
489
490 PA_PROJ_STRUCTURE_UTILS.G_PROJECT_ID := p_project_id;
491 PA_PROJ_STRUCTURE_UTILS.G_BUDGET_VERSION_ID := p_budget_version_id;
492
493 END;
494
495
496 END PA_PROJ_STRUCTURE_UTILS;
497
[Home] [Help]