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