[Home] [Help]
PACKAGE BODY: APPS.PJI_REP_UTIL
Source
1 PACKAGE BODY Pji_Rep_Util AS
2 /* $Header: PJIRX07B.pls 120.52.12020000.11 2013/04/16 15:27:13 krkondur ship $ */
3
4 g_project_id NUMBER:=-1;
5 g_pa_calendar_type VARCHAR2(1):='P';
6 g_global_calendar_type VARCHAR2(1):='E';
7 g_period_name VARCHAR2(30);
8 g_report_date_julian NUMBER;
9 g_actual_version_id NUMBER :=-1;
10 g_cstforecast_version_id NUMBER;
11 g_cstbudget_version_id NUMBER;
12 g_cstbudget2_version_id NUMBER;
13 g_revforecast_version_id NUMBER;
14 g_revbudget_version_id NUMBER;
15 g_revbudget2_version_id NUMBER;
16 g_orig_cstforecast_version_id NUMBER;
17 g_orig_cstbudget_version_id NUMBER;
18 g_orig_cstbudget2_version_id NUMBER;
19 g_orig_revforecast_version_id NUMBER;
20 g_orig_revbudget_version_id NUMBER;
21 g_orig_revbudget2_version_id NUMBER;
22 g_cost_bgt_plan_type_id NUMBER;
23 g_rev_bgt_plan_type_id NUMBER;
24 g_cost_fcst_plan_type_id NUMBER;
25 g_rev_fcst_plan_type_id NUMBER;
26 g_prg_flag VARCHAR2(1);
27 g_project_org_id NUMBER;
28 g_proj_currency_code VARCHAR2(30);
29 g_projfunc_currency_code VARCHAR2(30);
30 g_gl_calendar_id NUMBER :=-99;
31 g_pa_calendar_id NUMBER :=-99;
32 g_global_calendar_id NUMBER :=-99;
33 g_input_calendar_type VARCHAR2(1) :=' ';
34 g_input_calendar_id NUMBER := -99;
35
36 g_debug_mode VARCHAR2(1) := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
37 g_proc NUMBER :=5;
38
39 procedure olap_denorm_create (
40 p_element_version_id number ,
41 p_project_id number ) IS
42
43 t_element_version_id number ;
44 p_elem_id number ;
45
46 begin
47
48 PA_olap_PVT.G_PJT_ROLLUP_FLAG0 := 'Y';
49
50 delete from pa_olap_xbs_denorm_TMP ;
51
52 delete from pa_olap_wbs_header;
53
54 delete from pji_rep_xbs_denorm where project_id = p_project_id ;
55
56 insert into pa_olap_xbs_denorm_TMP (
57 STRUCT_TYPE,PRG_GROUP,STRUCT_VERSION_ID,SUP_PROJECT_ID,SUP_ID,SUP_EMT_ID,SUBRO_ID,SUB_ID,
58 SUB_EMT_ID,SUP_LEVEL,SUB_LEVEL,SUB_ROLLUP_ID,SUB_LEAF_FLAG,
59 LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
60 STRUCT_EMT_ID,RELATIONSHIP_TYPE )
61 select 'PRG',to_number(null), to_number(null), e.project_id ,e.element_version_id , e.proj_element_id ,to_number(null), e.element_version_id ,
62 e.proj_element_id , 1,1,e.proj_element_id, 'Y',
63 sysdate , -1 , sysdate , -1 , - 1,
64 to_number(null), to_number(null)
65 from pa_proj_element_versions e where project_id = p_project_id and object_type = 'PA_STRUCTURES';
66
67
68 insert into pa_olap_wbs_header (
69 PROJECT_ID,PLAN_VERSION_ID,WBS_VERSION_ID,
70 WP_FLAG,CB_FLAG,CO_FLAG,LOCK_FLAG,
71 PLAN_TYPE_ID,MIN_TXN_DATE,MAX_TXN_DATE,
72 LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
73 PLAN_TYPE_CODE)
74 select bv.project_id , bv.budget_version_id
75 , DECODE ( NVL(bv.wp_version_flag, 'N')
76 , 'Y', bv.project_structure_version_id
77 , PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id) -- -9999 --
78 ) wbs_struct_version_id
79 , NVL(bv.wp_version_flag, 'N')
80 , decode(bv.current_flag|| DECODE(bv.baselined_date, NULL, 'N', 'Y'),'YY','Y','N' )
81 , decode(bv.current_original_flag|| DECODE(bv.baselined_date, NULL, 'N', 'Y'),'YY','Y','N' )
82 ,'N'
83 , fpo.fin_plan_type_id plan_type_id
84 , trunc(sysdate) , trunc(sysdate)
85 , sysdate , -1 , sysdate , -1 , -1
86 , DECODE (bv.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code /* 4471527 */
87 FROM
88 ( select p_project_id project_id from dual ) map,
89 -- pji_pjp_rbs_header rhd,
90 pa_budget_versions bv,
91 pa_proj_fp_options fpo,
92 -- pji_pa_proj_events_log pel,
93 pa_projects_all ppa,
94 pa_rbs_versions_b rvb --Added for bug#5728852
95 WHERE 1=1
96 AND ppa.project_id = map.project_id
97 AND bv.version_type is not NULL -- COST, REVENUE, etc. Should not be null.
98 AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
99 AND fpo.project_id = map.project_id
100 AND bv.fin_plan_type_id = fpo.fin_plan_type_id
101 AND bv.budget_version_id = fpo.fin_plan_version_id
102 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
103 AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
104 AND fpo.rbs_version_id = rvb.rbs_version_id
105 AND map.project_id = bv.project_id ;
106
107
108 t_element_version_id := PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(1008);
109
110 select proj_element_id into p_elem_id from pa_proj_element_versions
111 where element_version_id = t_element_version_id;
112
113
114 INSERT INTO pji_rep_xbs_denorm
115 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
116 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
117 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
118 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
119 SELECT p_project_id ,'N',p_project_id,-1,struct_ver.proj_element_id,
120 'N',struct_ver.NAME,'Y','Y' ,
121 SYSDATE, SYSDATE, 1, 1,
122 0 ,t_element_version_id, -1,t_element_version_id, 'WF'
123 FROM pa_proj_elem_ver_structure struct_ver
124 WHERE 1=1
125 AND struct_ver.project_id = p_project_id
126 AND struct_ver.element_version_id = t_element_version_id;
127
128 INSERT INTO pji_rep_xbs_denorm
129 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
130 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
131 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
132 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
133 SELECT p_project_id,'N',p_project_id,struct_ver.proj_element_id,
134 struct_ver.proj_element_id,'N',struct_ver.NAME,'N','N' ,
135 SYSDATE, SYSDATE, 1, 1,
136 0 ,t_element_version_id, 0, t_element_version_id, 'WF'
137 FROM pa_proj_elem_ver_structure struct_ver
138 WHERE 1=1
139 AND struct_ver.project_id = p_project_id
140 AND struct_ver.element_version_id = t_element_version_id;
141
142 INSERT INTO pji_rep_xbs_denorm
143 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
144 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
145 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
146 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
147 SELECT t1.project_id , 'N', t1.project_id , nvl(t1.parent_task_id , p_elem_id ) parent_task_id ,
148 t1.task_id ,'N' ,emt.name,'Y' ,decode(nvl(t2.parent_task_id ,-1) , -1 , 'N', 'Y' ),
149 SYSDATE, SYSDATE, 1, 1,
150 0 ,t_element_version_id, ver.display_sequence,t_element_version_id, 'WF'
151 FROM pa_tasks t1 , pa_proj_elements emt,pa_proj_element_versions ver,
152 ( select distinct parent_task_id from pa_tasks t3 where t3.project_id = p_project_id ) t2
153 where t1.project_id = p_project_id
154 and t1.task_id = t2.parent_task_id (+)
155 AND t1.task_id = emt.PROJ_ELEMENT_ID
156 AND ver.project_id =t1.project_id
157 AND ver.parent_structure_version_id = t_element_version_id
158 AND ver.object_type = 'PA_TASKS'
159 AND ver.proj_element_id = t1.task_id;
160
161
162 null;
163
164
165 end;
166
167 PROCEDURE Add_Message (p_app_short_name VARCHAR2
168 , p_msg_name VARCHAR2
169 , p_msg_type VARCHAR2
170 , p_token1 VARCHAR2 DEFAULT NULL
171 , p_token1_value VARCHAR2 DEFAULT NULL
172 , p_token2 VARCHAR2 DEFAULT NULL
173 , p_token2_value VARCHAR2 DEFAULT NULL
174 , p_token3 VARCHAR2 DEFAULT NULL
175 , p_token3_value VARCHAR2 DEFAULT NULL
176 , p_token4 VARCHAR2 DEFAULT NULL
177 , p_token4_value VARCHAR2 DEFAULT NULL
178 , p_token5 VARCHAR2 DEFAULT NULL
179 , p_token5_value VARCHAR2 DEFAULT NULL
180 ) IS
181 BEGIN
182 Fnd_Message.set_name(p_app_short_name, p_msg_name);
183 -- Fnd_Msg_Pub.ADD;
184 IF p_token1 IS NOT NULL THEN
185 Fnd_Message.set_token(p_token1, p_token1_value);
186 END IF;
187
188 IF p_token2 IS NOT NULL THEN
189 Fnd_Message.set_token(p_token2, p_token2_value);
190 END IF;
191
192 IF p_token3 IS NOT NULL THEN
193 Fnd_Message.set_token(p_token3, p_token3_value);
194 END IF;
195
196 IF p_token4 IS NOT NULL THEN
197 Fnd_Message.set_token(p_token4, p_token4_value);
198 END IF;
199
200 IF p_token5 IS NOT NULL THEN
201 Fnd_Message.set_token(p_token5, p_token5_value);
202 END IF;
203 Fnd_Msg_Pub.add_detail(p_message_type=>p_msg_type);
204 EXCEPTION
205 WHEN OTHERS THEN
206 Fnd_Message.set_name('PJI','PJI_REP_GENERIC_MSG');
207 Fnd_Message.set_token('PROC_NAME','Pji_Rep_Util.Add_Message');
208 END Add_Message;
209
210 PROCEDURE Log_Struct_Change_Event(p_wbs_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE) IS
211 l_i NUMBER;
212 BEGIN
213
214 IF g_debug_mode = 'Y' THEN
215 Pji_Utils.WRITE2LOG( 'populate_wbs_hierachy_cache: beginning', TRUE , g_proc);
216 END IF;
217 --Bug 5929145 Log events only for projects and not templates
218 -- Insert into event log for the project structure change of current project
219 FORALL l_i IN p_wbs_version_id_tbl.FIRST..p_wbs_version_id_tbl.LAST
220 INSERT INTO pa_pji_proj_events_log
221 (event_type,event_id, event_object, operation_type, status, last_update_date
222 , last_updated_by, creation_date, created_by, last_update_login, attribute1, attribute2)
223 SELECT 'STRUCT_CHANGE',pa_pji_proj_events_log_s.NEXTVAL,'-99','X','X',SYSDATE,
224 Fnd_Global.USER_ID,SYSDATE,Fnd_Global.USER_ID,Fnd_Global.LOGIN_ID,p_wbs_version_id_tbl(l_i),'N'
225 FROM dual WHERE exists (
226 SELECT ppa.project_id from PA_PROJECTS_ALL ppa, PA_PROJ_ELEM_VER_STRUCTURE ppevs WHERE
227 ppa.project_id = ppevs.project_id AND
228 ppevs.ELEMENT_VERSION_ID = p_wbs_version_id_tbl(l_i) AND
229 ppa.template_flag <> 'Y'
230 )
231 and not exists --changes start for 8738137
232 (select 'Y'
233 from pa_pji_proj_events_log
234 where event_type = 'STRUCT_CHANGE'
235 and event_object = '-99'
236 and attribute1 = to_char(p_wbs_version_id_tbl(l_i)) -- bug 13109678 : added to_char
237 and attribute2 ='N'); --changes end for 8738137
238
239 -- Insert into event log for the program structure change for all parent project and current project
240 FORALL l_i IN p_wbs_version_id_tbl.FIRST..p_wbs_version_id_tbl.LAST
241 INSERT INTO pa_pji_proj_events_log
242 (event_type,event_id, event_object, operation_type, status, last_update_date
243 , last_updated_by, creation_date, created_by, last_update_login, attribute1, attribute2)
244 SELECT
245 'STRUCT_CHANGE',pa_pji_proj_events_log_s.NEXTVAL,'-99','X','X',SYSDATE,
246 Fnd_Global.USER_ID,SYSDATE,Fnd_Global.USER_ID,Fnd_Global.LOGIN_ID,sup_id,'Y'
247 FROM
248 (
249 select /*+ ordered */ -- bug 13109678 : removed index hint
250 distinct(prg.SUP_ID) SUP_ID
251 from
252 PJI_XBS_DENORM prg,
253 PA_PROJECTS_ALL prj
254 where
255 prg.STRUCT_VERSION_ID is null and
256 prg.SUB_ID = p_wbs_version_id_tbl(l_i) and
257 prg.SUP_PROJECT_ID = prj.PROJECT_ID and
258 prj.SYS_PROGRAM_FLAG = 'Y' and
259 prj.template_flag <> 'Y' and not exists
260 (select 'Y' --changes start for 8738137
261 from pa_pji_proj_events_log
262 where event_type = 'STRUCT_CHANGE'
263 and event_object = '-99'
264 and attribute1 = to_char(prg.sup_id) -- bug 13109678 : added to_char
265 and attribute2 = 'Y') --changes end for 8738137
266 );
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Log_Struct_Change_Event');
271 RAISE;
272 END;
273
274
275
276
277 /* Need to add program logic */
278 PROCEDURE Populate_WBS_Hierarchy_Cache(p_project_id NUMBER
279 , p_element_version_id NUMBER
280 , p_prg_flag VARCHAR2 DEFAULT 'N'
281 , p_page_type VARCHAR2 DEFAULT 'WORKPLAN'
282 , p_report_date_julian NUMBER DEFAULT NULL
283 , x_return_status IN OUT NOCOPY VARCHAR2
284 , x_msg_count IN OUT NOCOPY NUMBER
285 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
286 l_count NUMBER;
287 l_i NUMBER;
288 l_j NUMBER;
289 l_rollup_percent NUMBER;
290 l_pre_proj_element_id NUMBER := -1;
291 l_proj_element_id NUMBER;
292 l_prg_flag VARCHAR2(1);
293 l_project_id NUMBER;
294 l_relationship_type VARCHAR2(2);
295 l_element_version_id_str VARCHAR2(30);
296 l_proj_elem_ids_tbl SYSTEM.PA_NUM_TBL_TYPE;
297 l_complete_percents_tbl SYSTEM.PA_NUM_TBL_TYPE;
298
299 l_proj_elem_ids_tmp_tbl SYSTEM.PA_NUM_TBL_TYPE;
300 l_complete_percents_tmp_tbl SYSTEM.PA_NUM_TBL_TYPE;
301 l_rowid_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE;
302 l_update_flag BOOLEAN;
303
304 -- OLAP START
305
306 l_pjt_rollup_flag varchar2(1);
307 l_new_count number ;
308 l_new_elem_id number ;
309 l_struct_ver_id number;
310 l_cbs_enabled_flag varchar2(1);
311
312 cursor c1 is
313 SELECT object_id , LEVEL +1,
314 SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
315 connect_by_isleaf leaf_node
316 FROM pa_ppr_obj_tmp_view r
317 -- OLAP_BUG
318 -- START WITH r.parent_object_id = p_element_version_id CONNECT BY
319 START WITH r.parent_object_id = -1 CONNECT BY
320 PRIOR r.object_id = r.parent_object_id ;
321
322 l_task_arr PA_PLSQL_DATATYPES.NumTabTyp;
323 l_task_level_arr PA_PLSQL_DATATYPES.NumTabTyp;
324 l_connect_path_arr PA_PLSQL_DATATYPES.Char240TabTyp;
325 l_leaf_node_arr PA_PLSQL_DATATYPES.NumTabTyp;
326
327 -- OLAP END
328 BEGIN
329
330 IF g_debug_mode = 'Y' THEN
331 Pji_Utils.WRITE2LOG( 'populate_wbs_hierachy_cache: beginning', TRUE , g_proc);
332 END IF;
333
334 IF x_return_status IS NULL THEN
335 x_msg_count := 0;
336 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
337 END IF;
338
339 -- OLAP START
340 if ( p_element_version_id is not null ) then -- Bug 16507592
341
342 select nvl(p.pjt_rollup_enabled_flag,'N'), p.cbs_enable_flag, ver.proj_element_id -- SCOPE_00 Get cbs_enabled_flag
343 into l_pjt_rollup_flag, l_cbs_enabled_flag, l_new_elem_id
344 from pa_projects_all p , PA_PROJ_ELEMENT_VERSIONS ver
345 where p.project_id = p_project_id and p.project_id = ver.project_id
346 and ver.element_version_id = p_element_version_id ;
347
348 l_struct_ver_id := p_element_version_id; -- Bug 16507592
349
350 else -- Bug 16507592 START
351
352 /*select nvl(p.pjt_rollup_enabled_flag,'N'), s.proj_element_id , s.element_version_id
353 into l_pjt_rollup_flag, l_new_elem_id , l_struct_ver_id
354 from pa_projects_all p , pa_proj_elem_ver_structure s, pa_proj_structure_types ppst, pa_structure_types pst
355 where p.project_id = p_project_id
356 and s.project_id = p.project_id
357 and s.proj_element_id = ppst.proj_element_id
358 and ppst.structure_type_id = pst.structure_type_id
359 and s.current_flag = 'Y'
360 and pst.structure_type_class_code = 'FINANCIAL';*/
361
362 x_return_status := Fnd_Api.G_RET_STS_ERROR;
363 IF g_debug_mode = 'Y' THEN
364 Pji_Utils.WRITE2LOG( 'populate_wbs_hierachy_cache: p_element_version_id cannot be null', TRUE , g_proc);
365
366 END IF;
367 dbms_standard.raise_application_error(-20020, 'populate_wbs_hierachy_cache: p_element_version_id cannot be null');
368
369
370 end if; -- Bug 16507592 END
371
372
373 -- OLAP END
374
375 SELECT COUNT(*)
376 INTO l_count
377 FROM pji_rep_xbs_denorm
378 WHERE sup_project_id = p_project_id
379 AND prg_flag = p_prg_flag
380 AND wbs_version_id = p_element_version_id
381 AND ROWNUM=1;
382
383 l_element_version_id_str := TO_CHAR(p_element_version_id);
384
385 DELETE FROM pa_pji_proj_events_log
386 WHERE attribute1 = l_element_version_id_str
387 AND event_object = '-99'
388 AND attribute2 = p_prg_flag
389 AND event_type = 'STRUCT_CHANGE';
390 IF SQL%ROWCOUNT > 0 THEN
391 l_update_flag := TRUE;
392 ELSE
393 l_update_flag := FALSE;
394 END IF;
395
396 IF p_page_type = 'WORKPLAN' THEN
397 l_relationship_type := 'LW';
398 ELSE
399 l_relationship_type := 'LF';
400 END IF;
401
402 -- OLAP START
403
404 PA_olap_PVT.G_PJT_ROLLUP_FLAG0 := 'N';
405
406
407 IF l_cbs_enabled_flag = 'Y' then -- SCOPE_00
408
409 -- Comment out the below if
410
411 -- if ( l_pjt_rollup_flag = 'Y' ) then -- SCOPE_00 comment this.
412
413
414 IF ( l_pjt_rollup_flag = 'Y' ) then -- SCOPE_00
415 olap_denorm_create (
416 p_element_version_id => p_element_version_id,
417 p_project_id => p_project_id ) ;
418
419 end If; -- SCOPE_00
420
421 l_new_count := 0 ;
422
423 SELECT COUNT(*)
424 INTO l_new_count
425 FROM pa_ppr_obj_tmp_view
426 WHERE object_id = l_new_elem_id; -- Bug 16507592 ;
427
428
429 if ( l_new_count <> 1 ) then
430
431
432
433 delete from pa_ppr_obj_tmp_view;
434
435 insert into pa_ppr_obj_tmp_view ( object_id , parent_object_id )
436 select /*+ ordered */
437 -- OLAP_BUG
438 -- a.proj_element_id , nvl(c.proj_element_id,p_element_version_id)
439 a.proj_element_id , nvl(c.proj_element_id,l_new_elem_id )
440 from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
441 where a.project_id = p_project_id
442 and a.object_type = 'PA_TASKS'
443 and a.parent_structure_version_id = l_struct_ver_id -- Bug 16507592
444 and a.element_version_id = b.object_id_to1 (+)
445 and b.object_type_From (+) = 'PA_TASKS'
446 and b.relationship_type (+) = 'S'
447 and b.object_id_from1 = c.element_version_id (+) ;
448
449 -- OLAP_BUG
450
451 insert into pa_ppr_obj_tmp_view ( object_id , parent_object_id ) values ( l_new_elem_id , -1 );
452
453 l_task_arr.delete;
454 l_task_level_arr.delete;
455 l_connect_path_arr.delete;
456 l_leaf_node_arr.delete;
457
458 OPEN c1;
459
460 FETCH c1
461 BULK COLLECT INTO l_task_arr, l_task_level_arr,l_connect_path_arr , l_leaf_node_arr ;
462
463 CLOSE c1;
464
465
466
467
468 FORALL k IN 1..l_task_arr.COUNT
469 UPDATE pa_ppr_obj_tmp_view
470 SET connect_path = l_connect_path_arr(k),
471 leaf_node = l_leaf_node_arr(k),
472 wbs_rbs_level = l_task_level_arr(k)
473 WHERE object_id = l_task_arr(k);
474
475
476 end if;
477
478
479 --elsIF (l_count=0) OR l_update_flag THEN -- SCOPE_00 comment this IF Statement and add below IF statement
480
481 END IF; -- SCOPE_00 Add this end if
482
483 IF (l_count=0) OR l_update_flag THEN -- SCOPE_00 Add this IF statement
484
485 -- OLAP ENDS
486
487 IF (l_update_flag) THEN
488
489 DELETE FROM pji_rep_xbs_denorm
490 WHERE wbs_version_id = p_element_version_id
491 AND prg_flag = p_prg_flag;
492
493 END IF;
494
495 IF p_prg_flag = 'N' THEN
496
497
498 /*
499 ** for initial structure information, get the structure record and change its
500 ** parent to -1, so that -1 is a unique parent which has only one child
501 */
502 INSERT INTO pji_rep_xbs_denorm
503 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
504 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
505 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
506 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
507 SELECT p_project_id,'N',p_project_id,-1,struct_ver.proj_element_id,
508 'N',struct_ver.NAME,'Y','Y' ,
509 SYSDATE, SYSDATE, 1, 1,
510 0 ,p_element_version_id, -1, p_element_version_id, 'WF'
511 FROM pa_proj_elem_ver_structure struct_ver
512 WHERE 1=1
513 AND struct_ver.project_id = p_project_id
514 AND struct_ver.element_version_id = p_element_version_id;
515
516 /*
517 ** Insert the self node for this project
518 */
519 INSERT INTO pji_rep_xbs_denorm
520 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
521 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
522 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
523 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
524 SELECT p_project_id,'N',p_project_id,struct_ver.proj_element_id,
525 struct_ver.proj_element_id,'N',struct_ver.NAME,'N','N' ,
526 SYSDATE, SYSDATE, 1, 1,
527 0 ,p_element_version_id, 0, p_element_version_id, 'WF'
528 FROM pa_proj_elem_ver_structure struct_ver
529 WHERE 1=1
530 AND struct_ver.project_id = p_project_id
531 AND struct_ver.element_version_id = p_element_version_id;
532
533
534
535 /*
536 ** for wbs structure information
537 */
538 INSERT INTO pji_rep_xbs_denorm
539 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
540 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
541 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
542 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
543 SELECT denorm.sup_project_id,'N',denorm.sup_project_id,denorm.sup_emt_id,
544 denorm.sub_emt_id,'N',emt.name,DECODE(denorm.sub_level-sup_level,0,'N','Y'),DECODE(denorm.sub_leaf_flag,'Y','N','Y'),
545 SYSDATE, SYSDATE, 1, 1,
546 0 ,p_element_version_id, ver.display_sequence,p_element_version_id, denorm.relationship_type
547 FROM pji_xbs_denorm denorm, pa_proj_elements emt,pa_proj_element_versions ver
548 WHERE 1=1
549 AND denorm.sup_project_id = p_project_id
550 AND denorm.struct_version_id = p_element_version_id
551 AND denorm.sub_level - denorm.sup_level<=1
552 AND denorm.struct_type = 'WBS'
553 AND denorm.SUB_EMT_ID = emt.PROJ_ELEMENT_ID
554 AND ver.project_id = p_project_id
555 AND ver.parent_structure_version_id = p_element_version_id
556 AND ver.object_type = 'PA_TASKS'
557 AND ver.proj_element_id = denorm.sub_emt_id;
558
559 INSERT INTO pji_rep_xbs_denorm
560 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
561 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
562 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
563 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
564 SELECT
565 denorm.sup_project_id
566 , 'N'
567 , denorm.sup_project_id
568 , denorm.sup_emt_id
569 , denorm.sub_emt_id
570 , 'N'
571 , emt.name
572 , 'Y'
573 , DECODE(denorm.sub_leaf_flag,'Y','N','Y')
574 , SYSDATE
575 , SYSDATE
576 , 1
577 , 1
578 , 0
579 , p_element_version_id
580 , ver.display_sequence
581 , p_element_version_id
582 , denorm.relationship_type
583 FROM pji_xbs_denorm denorm
584 , pa_proj_elements emt
585 ,pa_proj_element_versions ver
586 WHERE 1=1
587 AND denorm.sup_project_id = p_project_id
588 AND denorm.sub_emt_id = emt.proj_element_id
589 AND denorm.struct_version_id = p_element_version_id
590 AND denorm.struct_type = 'XBS'
591 AND ver.project_id = p_project_id
592 AND ver.parent_structure_version_id = p_element_version_id
593 AND ver.object_type = 'PA_TASKS'
594 AND ver.proj_element_id = denorm.sub_emt_id;
595
596
597
598
599
600 ELSE
601
602
603 SELECT COUNT(*)
604 INTO l_count
605 FROM pji_xbs_denorm
606 WHERE sup_project_id = p_project_id
607 AND sup_id = p_element_version_id
608 AND struct_type = 'PRG'
609 AND sub_level>sup_level
610 AND ROWNUM=1;
611
612 IF l_count >0 THEN
613 l_prg_flag := 'Y';
614 ELSE
615 l_prg_flag := 'N';
616 END IF;
617
618 /*
619 ** Insert the virtual Header
620 */
621
622 INSERT INTO pji_rep_xbs_denorm
623 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
624 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
625 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
626 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
627 SELECT p_project_id
628 , l_prg_flag
629 ,struct_ver.project_id
630 ,-1
631 ,struct_ver.proj_element_id
632 ,'Y'
633 ,struct_ver.NAME
634 ,'Y'
635 ,'Y'
636 , SYSDATE
637 , SYSDATE
638 , 1
639 , 1
640 , 0
641 ,p_element_version_id
642 , -1
643 ,p_element_version_id
644 , 'WF'
645 FROM pa_proj_elem_ver_structure struct_ver
646 WHERE 1=1
647 AND struct_ver.project_id = p_project_id
648 AND struct_ver.element_version_id = p_element_version_id;
649
650
651 /*
652 ** Insert the project level self amount
653 */
654 INSERT INTO pji_rep_xbs_denorm
655 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
656 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
657 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
658 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
659 SELECT p_project_id
660 , DECODE(struct_ver.project_id, p_project_id, l_prg_flag,DECODE(SUB_LEAF_FLAG,'Y','N','Y'))
661 ,struct_ver.project_id
662 ,denorm.sub_emt_id
663 ,denorm.sub_emt_id
664 ,'Y'
665 ,struct_ver.NAME
666 ,'N'
667 ,'N'
668 , SYSDATE
669 , SYSDATE
670 , 1
671 , 1
672 , 0
673 ,p_element_version_id
674 , 0
675 , denorm.sup_id
676 , 'WF'
677 FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver, pa_proj_elements emt
678 WHERE 1=1
679 AND denorm.sup_project_id = p_project_id
680 AND denorm.sup_id = p_element_version_id
681 AND denorm.struct_version_id IS NULL
682 AND denorm.struct_type = 'PRG'
683 AND denorm.sub_id = struct_ver.element_version_id
684 AND denorm.sub_emt_id = emt.proj_element_id
685 AND emt.project_id = struct_ver.project_id
686 AND NVL(denorm.relationship_type,'WF') IN ('WF',l_relationship_type);
687
688 /*
689 ** Insert wbs information inside each structure
690 */
691 INSERT INTO pji_rep_xbs_denorm
692 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
693 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
694 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
695 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
696 SELECT p_project_id
697 , DECODE(emt.project_id, p_project_id, l_prg_flag,DECODE(structs.SUB_LEAF_FLAG,'Y','N','Y'))
698 ,emt.project_id
699 ,denorm.sup_emt_id
700 ,denorm.sub_emt_id
701 ,'Y'
702 ,emt.name
703 ,DECODE(denorm.sub_level-denorm.sup_level,0,'N','Y')
704 ,DECODE(denorm.sub_leaf_flag,'Y','N','Y')
705 , SYSDATE
706 , SYSDATE
707 , 1
708 , 1
709 , 0
710 ,p_element_version_id
711 , ver.display_sequence
712 ,denorm.struct_version_id
713 , denorm.relationship_type
714 FROM pa_proj_elements emt, pji_xbs_denorm denorm,
715 (SELECT sub_id wbs_version_id, sub_leaf_flag
716 FROM pji_xbs_denorm
717 WHERE 1=1
718 AND sup_project_id = p_project_id
719 AND sup_id = p_element_version_id
720 AND struct_version_id IS NULL
721 AND struct_type = 'PRG'
722 AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
723 ) structs
724 ,pa_proj_element_versions ver
725 WHERE 1=1
726 AND denorm.sub_level-denorm.sup_level<=1
727 AND denorm.struct_type = 'WBS'
728 AND denorm.sub_emt_id = emt.proj_element_id
729 AND denorm.struct_version_id = structs.wbs_version_id
730 AND ver.project_id = emt.project_id
731 AND ver.parent_structure_version_id = denorm.struct_version_id
732 AND ver.object_type = 'PA_TASKS'
733 AND ver.proj_element_id = denorm.sub_emt_id;
734
735 /*
736 ** Insert the link between structure and the top level elements
737 */
738
739 INSERT INTO pji_rep_xbs_denorm
740 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
741 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
742 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
743 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
744 SELECT
745 p_project_id
746 , DECODE(emt.project_id, p_project_id, l_prg_flag,DECODE(structs.SUB_LEAF_FLAG,'Y','N','Y'))
747 ,emt.project_id
748 , denorm.sup_emt_id
749 , denorm.sub_emt_id
750 , 'Y'
751 , emt.name
752 , 'Y'
753 , DECODE(denorm.sub_leaf_flag,'Y','N','Y')
754 , SYSDATE
755 , SYSDATE
756 , 1
757 , 1
758 , 0
759 , p_element_version_id
760 , ver.display_sequence
761 , denorm.struct_version_id
762 , denorm.relationship_type
763 FROM pji_xbs_denorm denorm
764 , pa_proj_elements emt
765 , (SELECT sub_id wbs_version_id, sub_leaf_flag
766 FROM pji_xbs_denorm
767 WHERE 1=1
768 AND sup_project_id = p_project_id
769 AND sup_id = p_element_version_id
770 AND struct_version_id IS NULL
771 AND struct_type = 'PRG'
772 AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
773 ) structs
774 ,pa_proj_element_versions ver
775 WHERE 1=1
776 AND denorm.sub_emt_id = emt.proj_element_id
777 AND denorm.struct_version_id = structs.wbs_version_id
778 AND denorm.struct_type = 'XBS'
779 AND ver.project_id = emt.project_id
780 AND ver.parent_structure_version_id = denorm.struct_version_id
781 AND ver.object_type = 'PA_TASKS'
782 AND ver.proj_element_id = denorm.sub_emt_id;
783
784 /*
785 * Insert link from project to project
786 */
787 INSERT INTO pji_rep_xbs_denorm
788 (SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
789 CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
790 CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
791 LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
792 SELECT p_project_id
793 ,DECODE(struct_ver.project_id, p_project_id, l_prg_flag,DECODE(structs.SUB_LEAF_FLAG,'Y','N','Y'))
794 ,struct_ver.project_id
795 ,denorm.sub_rollup_id
796 ,denorm.sub_emt_id
797 ,'Y'
798 ,struct_ver.NAME
799 ,'Y'
800 ,'Y'
801 , SYSDATE
802 , SYSDATE
803 , 1
804 , 1
805 , 0
806 ,p_element_version_id
807 , -1
808 , sub_id
809 , denorm.relationship_type
810 FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver,pa_proj_elements emt
811 , (SELECT sub_id wbs_version_id, sub_leaf_flag
812 FROM pji_xbs_denorm
813 WHERE 1=1
814 AND sup_project_id = p_project_id
815 AND sup_id = p_element_version_id
816 AND struct_version_id IS NULL
817 AND struct_type = 'PRG'
818 AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
819 ) structs
820 WHERE 1=1
821 AND denorm.sup_id = structs.wbs_version_id
822 AND denorm.struct_type = 'PRG'
823 AND denorm.struct_version_id IS NULL
824 AND denorm.sub_rollup_id <> denorm.sup_emt_id
825 AND denorm.sub_id = struct_ver.element_version_id
826 AND denorm.sub_emt_id = emt.proj_element_id
827 AND emt.project_id = struct_ver.project_id;
828
829 /*
830 * set rollup flag for elements have links to other projects
831 */
832
833 UPDATE pji_rep_xbs_denorm
834 SET display_child_flag = 'Y'
835 WHERE rollup_flag = 'Y'
836 AND sup_project_id = p_project_id
837 AND prg_flag = 'Y'
838 AND child_element_id IN
839 (SELECT sub_rollup_id
840 FROM pji_xbs_denorm,
841 (SELECT sub_id wbs_version_id, sub_leaf_flag
842 FROM pji_xbs_denorm
843 WHERE 1=1
844 AND sup_project_id = p_project_id
845 AND sup_id = p_element_version_id
846 AND struct_version_id IS NULL
847 AND struct_type = 'PRG'
848 AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
849 ) structs
850 WHERE 1=1
851 AND struct_type ='PRG'
852 AND struct_version_id IS NULL
853 AND sup_id = structs.wbs_version_id
854 AND NVL(sub_rollup_id, sup_emt_id) <> sup_emt_id);
855
856 END IF;
857 END IF;
858
859 IF g_debug_mode = 'Y' THEN
860 Pji_Utils.WRITE2LOG( 'populate_wbs_hierachy_cache: before getting percent', TRUE , g_proc);
861 END IF;
862
863 COMMIT;
864
865 IF g_debug_mode = 'Y' THEN
866 Pji_Utils.WRITE2LOG( 'populate_wbs_hierachy_cache: finishing', TRUE , g_proc);
867 END IF;
868
869 EXCEPTION
870 WHEN OTHERS THEN
871 x_msg_count := x_msg_count + 1;
872 x_return_status := Fnd_Api.G_RET_STS_ERROR;
873 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Populate_WBS_Hierarchy_Cache');
874 RAISE;
875 END;
876
877 PROCEDURE Populate_WP_Plan_Vers_Cache(p_project_id NUMBER
878 , p_prg_flag VARCHAR2 DEFAULT 'N'
879 , p_current_version_id NUMBER DEFAULT NULL
880 , p_latest_version_id NUMBER DEFAULT NULL
881 , p_baselined_version_id NUMBER DEFAULT NULL
882 , p_plan1_version_id NUMBER DEFAULT NULL
883 , p_plan2_version_id NUMBER DEFAULT NULL
884 , p_curr_wbs_vers_id NUMBER DEFAULT NULL
885 , x_return_status IN OUT NOCOPY VARCHAR2
886 , x_msg_count IN OUT NOCOPY NUMBER
887 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
888
889 -- OLAP START V2
890 l_pjt_rollup_enabled_flag varchar2(1);
891 -- OLAP END V2
892
893 BEGIN
894 IF g_debug_mode = 'Y' THEN
895 Pji_Utils.WRITE2LOG( 'Populate_WP_Plan_Vers_Cache: beginning', TRUE , g_proc);
896 END IF;
897
898 IF x_return_status IS NULL THEN
899 x_msg_count := 0;
900 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
901 END IF;
902
903 BEGIN
904 -- fnd_stats.set_table_stats('PJI','PJI_PLAN_EXTR_TMP',10,10,10);
905 pji_pjp_fp_curr_wrap.set_table_stats('PJI','PJI_PLAN_EXTR_TMP',10,10,10);
906 END;
907
908 DELETE FROM PJI_PLAN_EXTR_TMP;
909
910
911 -- OLAP START V2
912
913 select p.pjt_rollup_enabled_flag
914 into l_pjt_rollup_enabled_flag
915 from pa_projects_all p
916 where p_project_id = p.project_id ;
917
918
919
920 if l_pjt_rollup_enabled_flag = 'Y' then
921
922
923 INSERT INTO PJI_PLAN_EXTR_TMP
924 (PROJECT_ID, PLAN_VER_ID, LPB_PLAN_VER_ID, BASE_PLAN_VER_ID, PLAN1_VER_ID, PLAN2_VER_ID, STRUCT_VER_ID)
925 VALUES
926 (p_project_id, p_current_version_id, p_latest_version_id, p_baselined_version_id, p_plan1_version_id,
927 p_plan2_version_id, p_curr_wbs_vers_id);
928
929
930 elsIF p_prg_flag = 'Y' then
931 -- OLAP END V2
932 -- populate the plan versions ids into the temp table for the program hierarchy
933 INSERT INTO PJI_PLAN_EXTR_TMP
934 (PROJECT_ID, PLAN_VER_ID, LPB_PLAN_VER_ID, BASE_PLAN_VER_ID, PLAN1_VER_ID, PLAN2_VER_ID, STRUCT_VER_ID)
935 SELECT
936 header.project_id,
937 MAX(DECODE(header_p.plan_version_id,p_current_version_id,header.plan_version_id,NULL)),
938 MAX(DECODE(header_p.plan_version_id,p_latest_version_id,header.plan_version_id,NULL)),
939 MAX(DECODE(header_p.plan_version_id,p_baselined_version_id,header.plan_version_id,NULL)),
940 MAX(DECODE(header_p.plan_version_id,p_plan1_version_id,header.plan_version_id,NULL)),
941 MAX(DECODE(header_p.plan_version_id,p_plan2_version_id,header.plan_version_id,NULL)),
942 MAX(DECODE(header_p.plan_version_id,p_current_version_id,header.wbs_version_id,NULL))
943 FROM
944 pji_xbs_denorm denorm
945 , pa_proj_elements elem
946 , pji_pjp_wbs_header header
947 , pji_pjp_wbs_header header_p
948 WHERE 1=1
949 AND header_p.project_id = p_project_id
950 AND header_p.plan_version_id IN
951 (
952 p_current_version_id,
953 p_latest_version_id,
954 p_baselined_version_id,
955 p_plan1_version_id,
956 p_plan2_version_id
957 )
958 AND denorm.sup_project_id = header_p.project_id
959 AND denorm.sup_id = header_p.wbs_version_id
960 AND denorm.struct_type = 'PRG'
961 AND NVL(denorm.relationship_type,'WF') IN ('LW','WF')
962 AND denorm.struct_version_id IS NULL
963 AND denorm.sub_emt_id = elem.proj_element_id
964 AND header.project_id = elem.project_id
965 AND header.wbs_version_id = denorm.sub_id
966 AND header.wp_flag = 'Y'
967 GROUP BY header.project_id;
968 ELSE
969 INSERT INTO PJI_PLAN_EXTR_TMP
970 (PROJECT_ID, PLAN_VER_ID, LPB_PLAN_VER_ID, BASE_PLAN_VER_ID, PLAN1_VER_ID, PLAN2_VER_ID, STRUCT_VER_ID)
971 VALUES
972 (p_project_id, p_current_version_id, p_latest_version_id, p_baselined_version_id, p_plan1_version_id, p_plan2_version_id, p_curr_wbs_vers_id);
973 END IF;
974
975 IF g_debug_mode = 'Y' THEN
976 Pji_Utils.WRITE2LOG( 'Populate_WP_Plan_Vers_Cache: finishing', TRUE , g_proc);
977 END IF;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 x_msg_count := x_msg_count + 1;
982 x_return_status := Fnd_Api.G_RET_STS_ERROR;
983 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_Calendar_Info');
984 RAISE;
985 END Populate_WP_Plan_Vers_Cache;
986
987 PROCEDURE Derive_Default_Calendar_Info(
988 p_project_id NUMBER
989 , x_calendar_type OUT NOCOPY VARCHAR2
990 , x_calendar_id OUT NOCOPY NUMBER
991 , x_period_name OUT NOCOPY VARCHAR2
992 , x_report_date_julian OUT NOCOPY NUMBER
993 , x_slice_name OUT NOCOPY VARCHAR2
994 , x_return_status IN OUT NOCOPY VARCHAR2
995 , x_msg_count IN OUT NOCOPY NUMBER
996 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
997
998 l_tp_flag varchar2(10); --Bug 9048624
999 BEGIN
1000
1001 IF g_debug_mode = 'Y' THEN
1002 Pji_Utils.WRITE2LOG( 'derive_default_calendar_info: beginning', TRUE , g_proc);
1003 END IF;
1004
1005 IF x_return_status IS NULL THEN
1006 x_msg_count := 0;
1007 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1008 END IF;
1009
1010
1011
1012 IF g_project_id <> p_project_id THEN
1013 /*
1014 ** Following are the defaulting rules:
1015 ** calendar_type: always pa calendar.
1016 ** calendar_id: pa calendar id for the project org.
1017 ** period_name: based on the project performance or implementations setup.
1018 ** report_date: julian start date of the above determined period.
1019 */
1020 Derive_Project_Attributes(p_project_id, x_return_status, x_msg_count, x_msg_data);
1021 END IF;
1022
1023 /* Since we override our implemenation of enterprise calendar, we
1024 * always use -1 as our enterprise calendar id .
1025 */
1026
1027 g_global_calendar_id := -1;
1028 g_global_calendar_type := 'E';
1029 /*
1030 SELECT calendar_id
1031 INTO g_global_calendar_id
1032 FROM fii_time_cal_name
1033 WHERE period_set_name = Fnd_Profile.VALUE( 'BIS_ENTERPRISE_CALENDAR' )
1034 AND period_type = Fnd_Profile.VALUE( 'BIS_PERIOD_TYPE' );
1035 */
1036 --Bug 5593229
1037 -- x_calendar_type := g_global_calendar_type;
1038
1039 --Bug 9048624
1040 l_tp_flag := PJI_UTILS.get_setup_parameter('TIME_PHASE_FLAG');
1041
1042 if(l_tp_flag = 'N') then
1043 x_calendar_type := NVL(Fnd_Profile.value('PJI_DEF_RPT_CAL_TYPE'), 'E');
1044 else
1045 SELECT Min(calendar_type) into x_calendar_type
1046 FROM pji_fp_xbs_accum_f WHERE project_id = p_project_id
1047 and plan_version_id <> -1 and calendar_type <> 'A';
1048 -- Bug 12708882 - if x_calendar_type is null, derive x_calendar_type as per original logic
1049 -- which was there before data processing control was implemented.
1050 -- x_calendar_type will be null for the case when the budgets are defined with time_phased_code
1051 -- as N.
1052 If x_calendar_type is null then
1053 x_calendar_type := NVL(Fnd_Profile.value('PJI_DEF_RPT_CAL_TYPE'), 'E');
1054 end if;
1055 -- End Bug 12708882
1056 end if;
1057 --Bug 9048624
1058
1059 Derive_Pa_Calendar_Info(p_project_id
1060 ,x_calendar_type
1061 ,x_calendar_id
1062 ,x_report_date_julian
1063 ,x_period_name
1064 ,x_slice_name
1065 , x_return_status
1066 , x_msg_count
1067 , x_msg_data);
1068
1069
1070 IF g_debug_mode = 'Y' THEN
1071 Pji_Utils.WRITE2LOG( 'derive_default_calendar_info: finishing', TRUE , g_proc);
1072 END IF;
1073
1074 EXCEPTION
1075 WHEN OTHERS THEN
1076 x_msg_count := x_msg_count + 1;
1077 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1078 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_Calendar_Info');
1079 RAISE;
1080 END Derive_Default_Calendar_Info;
1081
1082 PROCEDURE Derive_WP_Calendar_Info(
1083 p_project_id NUMBER
1084 , p_plan_version_id NUMBER
1085 , x_calendar_id OUT NOCOPY NUMBER
1086 , x_calendar_type OUT NOCOPY VARCHAR2
1087 , x_return_status IN OUT NOCOPY VARCHAR2
1088 , x_msg_count IN OUT NOCOPY NUMBER
1089 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
1090 l_fin_plan_type_id NUMBER(15);
1091 l_time_phase_same_flag VARCHAR2(80);
1092 BEGIN
1093
1094 IF g_debug_mode = 'Y' THEN
1095 Pji_Utils.WRITE2LOG( 'derive_wp_calendar_info: beginning', TRUE , g_proc);
1096 END IF;
1097
1098
1099 /* SELECT fin_plan_type_id
1100 INTO l_fin_plan_type_id
1101 FROM pa_budget_versions
1102 WHERE budget_version_id = p_plan_version_id;
1103 */
1104 Derive_VP_Calendar_Info(
1105 p_project_id
1106 , p_plan_version_id
1107 , NULL
1108 , 'COST'
1109 , x_calendar_id
1110 , x_calendar_type
1111 , l_time_phase_same_flag
1112 , x_return_status
1113 , x_msg_count
1114 , x_msg_data);
1115
1116 IF g_debug_mode = 'Y' THEN
1117 Pji_Utils.WRITE2LOG( 'derive_wp_calendar_info: finishing', TRUE , g_proc);
1118 END IF;
1119
1120 EXCEPTION
1121 WHEN NO_DATA_FOUND THEN
1122 x_msg_count := x_msg_count + 1;
1123 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1124 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'WP CALENDAR');
1125 WHEN OTHERS THEN
1126 x_msg_count := x_msg_count + 1;
1127 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1128 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_WP_Calendar_Info');
1129 RAISE;
1130 END Derive_WP_Calendar_Info;
1131
1132 PROCEDURE Derive_VP_Calendar_Info(
1133 p_project_id NUMBER
1134 , p_cst_version_id NUMBER
1135 , p_rev_version_id NUMBER
1136 , p_context_version_type VARCHAR2
1137 , x_calendar_id OUT NOCOPY NUMBER
1138 , x_calendar_type OUT NOCOPY VARCHAR2
1139 , x_time_phase_valid_flag OUT NOCOPY VARCHAR2
1140 , x_return_status IN OUT NOCOPY VARCHAR2
1141 , x_msg_count IN OUT NOCOPY NUMBER
1142 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
1143 l_gl_calendar_id NUMBER;
1144 l_pa_calendar_id NUMBER;
1145 l_all_cal_type VARCHAR2(30);
1146 l_cost_cal_type VARCHAR2(30);
1147 l_revenue_cal_type VARCHAR2(30);
1148 l_preference_code VARCHAR2(30);
1149 l_working_version_id NUMBER;
1150 BEGIN
1151
1152 IF g_debug_mode = 'Y' THEN
1153 Pji_Utils.WRITE2LOG( 'derive_vp_calendar_info: beginning', TRUE , g_proc);
1154 END IF;
1155
1156 IF x_return_status IS NULL THEN
1157 x_msg_count := 0;
1158 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1159 END IF;
1160
1161 IF p_cst_version_id IS NOT NULL AND p_cst_version_id <> -99 THEN
1162 IF p_cst_version_id = p_rev_version_id THEN
1163 l_preference_code := 'SAME';
1164 ELSIF p_rev_version_id IS NOT NULL AND p_rev_version_id <> -99 THEN
1165 l_preference_code := 'SEP';
1166 ELSE
1167 l_preference_code := 'COST';
1168 END IF;
1169 l_working_version_id := p_cst_version_id;
1170 ELSIF p_rev_version_id IS NOT NULL AND p_rev_version_id <> -99 THEN
1171 l_working_version_id := p_rev_version_id;
1172 l_preference_code := 'REVENUE';
1173 ELSE
1174 RETURN;
1175 END IF;
1176
1177
1178 SELECT org.gl_calendar_id,org.pa_calendar_id
1179 INTO l_gl_calendar_id, l_pa_calendar_id
1180 FROM
1181 pa_projects_all projects,
1182 pji_org_extr_info org
1183 /* WHERE NVL(projects.org_id,-99) = NVL(org.org_id,-99) -- Added NVL for bug 3989132 */
1184 WHERE projects.org_id = org.org_id -- Removed NVL for Bug5376591
1185 AND projects.project_id = p_project_id;
1186
1187 SELECT all_time_phased_code, cost_time_phased_code, revenue_time_phased_code
1188 INTO l_all_cal_type, l_cost_cal_type, l_revenue_cal_type
1189 FROM pa_proj_fp_options
1190 WHERE project_id = p_project_id
1191 AND fin_plan_version_id = l_working_version_id
1192 AND fin_plan_option_level_code = Pa_Fp_Constants_Pkg.G_OPTION_LEVEL_PLAN_VERSION;
1193
1194 IF l_preference_code = 'SEP' THEN
1195 SELECT revenue_time_phased_code
1196 INTO l_revenue_cal_type
1197 FROM pa_proj_fp_options
1198 WHERE project_id = p_project_id
1199 AND fin_plan_version_id = p_rev_version_id
1200 AND fin_plan_option_level_code = Pa_Fp_Constants_Pkg.G_OPTION_LEVEL_PLAN_VERSION;
1201 END IF ;
1202
1203 IF l_preference_code = 'REVENUE' THEN
1204 x_calendar_type := l_revenue_cal_type;
1205 ELSIF l_preference_code = 'SAME' THEN
1206 x_calendar_type := l_all_cal_type;
1207 ELSIF l_preference_code = 'COST' THEN
1208 x_calendar_type := l_cost_cal_type;
1209 ELSE
1210 /* if it is SEP, if cost is not valid, we will take revenue
1211 * if revenue is not valid, we will take cost
1212 * if both valid, we decide it using the context version type */
1213 IF (l_cost_cal_type IS NULL) OR (l_cost_cal_type = 'N') THEN
1214 x_calendar_type := l_revenue_cal_type;
1215 ELSIF (l_revenue_cal_type IS NULL) OR (l_revenue_cal_type = 'N') THEN
1216 x_calendar_type := l_cost_cal_type;
1217 ELSE
1218 IF p_context_version_type = 'COST' THEN
1219 x_calendar_type := l_cost_cal_type;
1220 ELSE
1221 x_calendar_type := l_revenue_cal_type;
1222 END IF ;
1223 END IF ;
1224 END IF;
1225
1226 -- We have make sure if cost is selected, it is valid, so if the selected one is not valid, that means
1227 -- revenue is not valid, so it will be an invalid case
1228 IF (x_calendar_type IS NULL) OR (x_calendar_type = 'N') THEN
1229 x_time_phase_valid_flag := 'PJI_REP_PLAN_NOT_TF';
1230 /*
1231 * the left cases are: 1. cost revenue both valid and same (Valid)
1232 * 2. one is null, the other is valid (Valid)
1233 * 3. cost revenue both valid and not same (Not Valid)
1234 * 4, one is N, the other is valid (Not Valid)
1235 * NVL(l_revenue_cal_type,l_cost_cal_type) <> NVL(l_cost_cal_type,l_rev_cal_type) will handle
1236 * all 2,3,4 cases
1237 */
1238 ELSIF (l_preference_code = 'SEP') AND (NVL(l_revenue_cal_type,l_cost_cal_type) <> NVL(l_cost_cal_type,l_revenue_cal_type)) THEN
1239 IF (l_revenue_cal_type = 'N') AND (l_cost_cal_type <>'N') THEN
1240 x_time_phase_valid_flag := 'PJI_REP_REV_NOT_TF';
1241 ELSIF (l_revenue_cal_type <> 'N') AND (l_cost_cal_type = 'N') THEN
1242 x_time_phase_valid_flag := 'PJI_REP_COST_NOT_TF ';
1243 ELSE
1244 IF p_context_version_type = 'COST' THEN
1245 x_time_phase_valid_flag := 'PJI_REP_TF_NOT_SAME';
1246 ELSE
1247 x_time_phase_valid_flag := 'PJI_REP_TF_NOT_SAME_REV';
1248 END IF ;
1249 END IF;
1250 ELSE
1251 x_time_phase_valid_flag := 'Y';
1252 END IF;
1253
1254
1255 IF x_calendar_type = Pa_Fp_Constants_Pkg.G_TIME_PHASED_CODE_P THEN
1256 x_calendar_id := l_pa_calendar_id;
1257 ELSIF x_calendar_type = Pa_Fp_Constants_Pkg.G_TIME_PHASED_CODE_G THEN
1258 x_calendar_id := l_gl_calendar_id;
1259 END IF;
1260
1261 IF g_debug_mode = 'Y' THEN
1262 Pji_Utils.WRITE2LOG( 'derive_vp_calendar_info: finishing', TRUE , g_proc);
1263 END IF;
1264
1265 EXCEPTION
1266 WHEN NO_DATA_FOUND THEN
1267 x_msg_count := x_msg_count + 1;
1268 x_return_status := Pji_Rep_Util.G_RET_STS_ERROR;
1269 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'VP CALENDAR');
1270 WHEN OTHERS THEN
1271 x_msg_count := x_msg_count + 1;
1272 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1273 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Vp_Calendar_Info');
1274 RAISE;
1275 END Derive_Vp_Calendar_Info;
1276
1277
1278 FUNCTION Get_Version_Type(
1279 p_project_id NUMBER
1280 , p_fin_plan_type_id NUMBER
1281 , p_version_type VARCHAR2
1282 ) RETURN VARCHAR2 IS
1283 l_version_type pa_budget_versions.version_type%TYPE;
1284 BEGIN
1285
1286 IF g_debug_mode = 'Y' THEN
1287 Pji_Utils.WRITE2LOG( 'get_version_type: beginning', TRUE , g_proc);
1288 END IF;
1289
1290 SELECT fin_plan_preference_code
1291 INTO l_version_type
1292 FROM pa_proj_fp_options
1293 WHERE project_id = p_project_id
1294 AND fin_plan_type_id = p_fin_plan_type_id
1295 AND fin_plan_option_level_code = Pa_Fp_Constants_Pkg.G_OPTION_LEVEL_PLAN_TYPE;
1296
1297 IF l_version_type = Pa_Fp_Constants_Pkg.G_PREF_COST_AND_REV_SAME THEN
1298 l_version_type := Pa_Fp_Constants_Pkg.G_VERSION_TYPE_ALL;
1299 ELSE
1300 l_version_type := p_version_type;
1301 END IF;
1302
1303 IF g_debug_mode = 'Y' THEN
1304 Pji_Utils.WRITE2LOG( 'get_version_type: returning', TRUE , g_proc);
1305 END IF;
1306
1307 RETURN l_version_type;
1308
1309 EXCEPTION
1310 WHEN OTHERS THEN
1311 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'VERSION TYPE');
1312 RETURN NULL;
1313 END Get_Version_Type;
1314
1315 /*
1316 ** derive all plan versions-related default information
1317 */
1318 PROCEDURE Derive_Default_Plan_Versions(
1319 p_project_id NUMBER
1320 , x_actual_version_id OUT NOCOPY NUMBER
1321 , x_cstforecast_version_id OUT NOCOPY NUMBER
1322 , x_cstbudget_version_id OUT NOCOPY NUMBER
1323 , x_cstbudget2_version_id OUT NOCOPY NUMBER
1324 , x_revforecast_version_id OUT NOCOPY NUMBER
1325 , x_revbudget_version_id OUT NOCOPY NUMBER
1326 , x_revbudget2_version_id OUT NOCOPY NUMBER
1327 , x_orig_cstforecast_version_id OUT NOCOPY NUMBER
1328 , x_orig_cstbudget_version_id OUT NOCOPY NUMBER
1329 , x_orig_cstbudget2_version_id OUT NOCOPY NUMBER
1330 , x_orig_revforecast_version_id OUT NOCOPY NUMBER
1331 , x_orig_revbudget_version_id OUT NOCOPY NUMBER
1332 , x_orig_revbudget2_version_id OUT NOCOPY NUMBER
1333 , x_prior_cstfcst_version_id OUT NOCOPY NUMBER
1334 , x_prior_revfcst_version_id OUT NOCOPY NUMBER
1335 , x_return_status IN OUT NOCOPY VARCHAR2
1336 , x_msg_count IN OUT NOCOPY NUMBER
1337 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
1338 l_cst_budget_version_type pa_budget_versions.version_type%TYPE;
1339 l_rev_budget_version_type pa_budget_versions.version_type%TYPE;
1340 l_cst_forecast_version_type pa_budget_versions.version_type%TYPE;
1341 l_rev_forecast_version_type pa_budget_versions.version_type%TYPE;
1342 l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1343 l_temp_holder1 NUMBER;
1344 l_temp_holder2 NUMBER;
1345 BEGIN
1346
1347 IF g_debug_mode = 'Y' THEN
1348 Pji_Utils.WRITE2LOG( 'derive_default_plan_versions: beginning', TRUE , g_proc);
1349 END IF;
1350
1351
1352 IF x_return_status IS NULL THEN
1353 x_msg_count := 0;
1354 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1355 END IF;
1356
1357 -- IF g_project_id <> p_project_id THEN
1358 /*
1359 ** Following are the defaulting rules:
1360 ** cost budget plan version: current baselined version of approved cost plan type.
1361 ** revenue budget plan version: current baselined version of approved revenue plan type.
1362 ** cost forecast plan version: current baselined version of primary cost plan type.
1363 ** revenue forecast plan version: current baselined version of primary revenue plan type.
1364 ** cost budget2 plan version: null.
1365 ** revenue budget2 plan version: null.
1366 ** misc: return original baselined for all above plan versions.
1367 */
1368 BEGIN
1369 /*
1370 ** Get approved cost and revenue plan types for the
1371 ** project.
1372 */
1373 Pa_Fin_Plan_Utils.Get_Appr_Cost_Plan_Type_Info(
1374 p_project_id=>p_project_id
1375 ,x_plan_type_id =>g_cost_bgt_plan_type_id
1376 ,x_return_status=>x_return_status
1377 ,x_msg_count=>x_msg_count
1378 ,x_msg_data=>x_msg_data);
1379 Pa_Fin_Plan_Utils.Get_Appr_Rev_Plan_Type_Info(
1380 p_project_id=>p_project_id
1381 ,x_plan_type_id=>g_rev_bgt_plan_type_id
1382 ,x_return_status=>x_return_status
1383 ,x_msg_count=>x_msg_count
1384 ,x_msg_data=>x_msg_data);
1385 /*
1386 ** Get primary cost and revenue plan types for the
1387 ** project.
1388 */
1389 Pa_Fin_Plan_Utils.Is_Pri_Fcst_Cost_PT_Attached(
1390 p_project_id=>p_project_id
1391 ,x_plan_type_id=>g_cost_fcst_plan_type_id
1392 ,x_return_status=>x_return_status
1393 ,x_msg_count=>x_msg_count
1394 ,x_msg_data=>x_msg_data);
1395 Pa_Fin_Plan_Utils.Is_Pri_Fcst_Rev_PT_Attached(
1396 p_project_id=>p_project_id
1397 ,x_plan_type_id=>g_rev_fcst_plan_type_id
1398 ,x_return_status=>x_return_status
1399 ,x_msg_count=>x_msg_count
1400 ,x_msg_data=>x_msg_data);
1401 /*
1402 ** Get current and original baselined plan versions
1403 ** for approved/primary cost and revenue plan types.
1404 */
1405 IF g_cost_bgt_plan_type_id IS NOT NULL THEN
1406 Pa_Fin_Plan_Utils.Get_Cost_Base_Version_Info(
1407 p_project_id=>p_project_id
1408 ,p_fin_plan_Type_id=>g_cost_bgt_plan_type_id
1409 ,p_budget_type_code=>NULL
1410 ,x_budget_version_id=>g_cstbudget_version_id
1411 ,x_return_status =>x_return_status
1412 ,x_msg_count=>x_msg_count
1413 ,x_msg_data=>x_msg_data);
1414
1415 l_cst_budget_version_type:=Get_Version_Type(p_project_id
1416 ,g_cost_bgt_plan_type_id
1417 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
1418
1419 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
1420 p_project_id=>p_project_id
1421 ,p_fin_plan_Type_id=>g_cost_bgt_plan_type_id
1422 ,p_version_type=>l_cst_budget_version_type
1423 ,x_fp_options_id=>l_fp_options_id
1424 ,x_fin_plan_version_id=>g_orig_cstbudget_version_id
1425 ,x_return_status =>x_return_status
1426 ,x_msg_count=>x_msg_count
1427 ,x_msg_data=>x_msg_data);
1428 END IF;
1429
1430 IF g_rev_bgt_plan_type_id IS NOT NULL THEN
1431 Pa_Fin_Plan_Utils.Get_Rev_Base_Version_Info(
1432 p_project_id=>p_project_id
1433 ,p_fin_plan_Type_id=>g_rev_bgt_plan_type_id
1434 ,p_budget_type_code=>NULL
1435 ,x_budget_version_id=>g_revbudget_version_id
1436 ,x_return_status =>x_return_status
1437 ,x_msg_count=>x_msg_count
1438 ,x_msg_data=>x_msg_data);
1439
1440 l_rev_budget_version_type:=Get_Version_Type(p_project_id
1441 ,g_rev_bgt_plan_type_id
1442 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
1443
1444 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
1445 p_project_id=>p_project_id
1446 ,p_fin_plan_Type_id=>g_rev_bgt_plan_type_id
1447 ,p_version_type=>l_rev_budget_version_type
1448 ,x_fp_options_id=>l_fp_options_id
1449 ,x_fin_plan_version_id=>g_orig_revbudget_version_id
1450 ,x_return_status =>x_return_status
1451 ,x_msg_count=>x_msg_count
1452 ,x_msg_data=>x_msg_data);
1453 END IF;
1454
1455 IF g_cost_fcst_plan_type_id IS NOT NULL THEN
1456 Pa_Fin_Plan_Utils.Get_Cost_Base_Version_Info(
1457 p_project_id=>p_project_id
1458 ,p_fin_plan_Type_id=>g_cost_fcst_plan_type_id
1459 ,p_budget_type_code=>NULL
1460 ,x_budget_version_id=>g_cstforecast_version_id
1461 ,x_return_status =>x_return_status
1462 ,x_msg_count=>x_msg_count
1463 ,x_msg_data=>x_msg_data);
1464
1465 l_cst_forecast_version_type:=Get_Version_Type(p_project_id
1466 ,g_cost_fcst_plan_type_id
1467 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
1468
1469 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
1470 p_project_id=>p_project_id
1471 ,p_fin_plan_Type_id=>g_cost_fcst_plan_type_id
1472 ,p_version_type=>l_cst_forecast_version_type
1473 ,x_fp_options_id=>l_fp_options_id
1474 ,x_fin_plan_version_id=>g_orig_cstforecast_version_id
1475 ,x_return_status =>x_return_status
1476 ,x_msg_count=>x_msg_count
1477 ,x_msg_data=>x_msg_data);
1478
1479 IF (g_cstforecast_version_id IS NOT NULL) AND (g_cstforecast_version_id <>-99) THEN
1480 Pa_Planning_Element_Utils.get_finplan_bvids(p_project_id=>p_project_id
1481 ,p_budget_version_id => g_cstforecast_version_id
1482 , x_current_version_id => l_temp_holder1
1483 , x_original_version_id => l_temp_holder2
1484 , x_prior_fcst_version_id => x_prior_cstfcst_version_id
1485 ,x_return_status =>x_return_status
1486 ,x_msg_count=>x_msg_count
1487 ,x_msg_data=>x_msg_data);
1488 --Bug5510794 deriving the correct prior forecast version id
1489 x_prior_cstfcst_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id
1490 (g_cstforecast_version_id, p_project_id);
1491 END IF;
1492
1493 END IF;
1494
1495 IF g_rev_fcst_plan_type_id IS NOT NULL THEN
1496 Pa_Fin_Plan_Utils.Get_Rev_Base_Version_Info(
1497 p_project_id=>p_project_id
1498 ,p_fin_plan_Type_id=>g_rev_fcst_plan_type_id
1499 ,p_budget_type_code=>NULL
1500 ,x_budget_version_id=>g_revforecast_version_id
1501 ,x_return_status =>x_return_status
1502 ,x_msg_count=>x_msg_count
1503 ,x_msg_data=>x_msg_data);
1504
1505 l_rev_forecast_version_type:=Get_Version_Type(p_project_id
1506 ,g_rev_fcst_plan_type_id
1507 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
1508
1509 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
1510 p_project_id=>p_project_id
1511 ,p_fin_plan_Type_id=>g_rev_fcst_plan_type_id
1512 ,p_version_type=>l_rev_forecast_version_type
1513 ,x_fp_options_id=>l_fp_options_id
1514 ,x_fin_plan_version_id=>g_orig_revforecast_version_id
1515 ,x_return_status =>x_return_status
1516 ,x_msg_count=>x_msg_count
1517 ,x_msg_data=>x_msg_data);
1518
1519 IF (g_revforecast_version_id IS NOT NULL) AND (g_revforecast_version_id <>-99) THEN
1520 Pa_Planning_Element_Utils.get_finplan_bvids(p_project_id=>p_project_id
1521 ,p_budget_version_id => g_revforecast_version_id
1522 , x_current_version_id => l_temp_holder1
1523 , x_original_version_id => l_temp_holder2
1524 , x_prior_fcst_version_id => x_prior_revfcst_version_id
1525 ,x_return_status =>x_return_status
1526 ,x_msg_count=>x_msg_count
1527 ,x_msg_data=>x_msg_data);
1528 --Bug5510794 deriving the correct prior forecast version id
1529 x_prior_revfcst_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id
1530 (g_revforecast_version_id, p_project_id);
1531 END IF;
1532 END IF;
1533 g_actual_version_id := get_fin_plan_actual_version(p_project_id);
1534 g_project_id := p_project_id;
1535 END;
1536 -- END IF;
1537 x_cstforecast_version_id := g_cstforecast_version_id;
1538 x_cstbudget_version_id := g_cstbudget_version_id;
1539 x_revforecast_version_id := g_revforecast_version_id;
1540 x_revbudget_version_id := g_revbudget_version_id;
1541 x_orig_cstforecast_version_id := g_orig_cstforecast_version_id;
1542 x_orig_cstbudget_version_id := g_orig_cstbudget_version_id;
1543 x_orig_revforecast_version_id := g_orig_revforecast_version_id;
1544 x_orig_revbudget_version_id := g_orig_revbudget_version_id;
1545 x_actual_version_id := g_actual_version_id;
1546
1547 /*
1548 ** Budget2 information is always defaulted to null. This is provided
1549 ** as a placeholder for any future changes.
1550 */
1551 x_cstbudget2_version_id := g_cstbudget2_version_id;
1552 x_revbudget2_version_id := g_revbudget2_version_id;
1553 x_orig_cstbudget2_version_id := g_orig_cstbudget2_version_id;
1554 x_orig_revbudget2_version_id := g_orig_revbudget2_version_id;
1555
1556 IF g_debug_mode = 'Y' THEN
1557 Pji_Utils.WRITE2LOG( 'derive_default_plan_versions: finishing', TRUE , g_proc);
1558 END IF;
1559
1560 EXCEPTION
1561 WHEN OTHERS THEN
1562 x_msg_count := x_msg_count + 1;
1563 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1564 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_Plan_Versions');
1565 RAISE;
1566 END Derive_Default_Plan_Versions;
1567
1568 /*
1569 ** derive all plan versions-related default information
1570 */
1571 PROCEDURE Derive_Default_Currency_Info(
1572 p_project_id NUMBER
1573 , x_currency_record_type OUT NOCOPY NUMBER
1574 , x_currency_code OUT NOCOPY VARCHAR2
1575 , x_currency_type OUT NOCOPY VARCHAR2
1576 , x_return_status IN OUT NOCOPY VARCHAR2
1577 , x_msg_count IN OUT NOCOPY NUMBER
1578 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
1579
1580
1581 BEGIN
1582
1583 IF g_debug_mode = 'Y' THEN
1584 Pji_Utils.WRITE2LOG( 'derive_default_currency_info: beginning', TRUE , g_proc);
1585 END IF;
1586
1587 IF x_return_status IS NULL THEN
1588 x_msg_count := 0;
1589 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1590 END IF;
1591
1592 -- IF g_project_id <> p_project_id THEN
1593 /*
1594 ** Following are the defaulting rules:
1595 ** currency rec type: always project currency (8).
1596 ** currency code: derive project currency code from pa_projects_all.
1597 ** currency_type: always project currency 'P'.
1598 */
1599 Derive_Project_Attributes(p_project_id, x_return_status, x_msg_count, x_msg_data);
1600 -- END IF;
1601
1602 /* Code added for 9112216 starts
1603 This code has been added to honor the value of profile option
1604 PJI:Default Reporting Currency Type in Project List page. The IF-THEN
1605 condition ensures that this change does not impact current behaviour of
1606 View Budget and View Workplan Cost pages */
1607 if Pji_Rep_Util.G_PROJECT_LIST = 'Y' then
1608 Derive_Perf_Currency_Info(p_project_id, x_currency_record_type,
1609 x_currency_code, x_currency_type,
1610 x_return_status, x_msg_count, x_msg_data);
1611 else
1612 x_currency_record_type:=8;
1613 x_currency_type:='P';
1614 x_currency_code:=g_proj_currency_code;
1615 end if;
1616 /* Code added for 9112216 ends */
1617
1618 IF g_debug_mode = 'Y' THEN
1619 Pji_Utils.WRITE2LOG( 'derive_default_currency_info: finishing', TRUE , g_proc);
1620 END IF;
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 x_msg_count := x_msg_count + 1;
1625 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1626 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_Currency_Info');
1627 RAISE;
1628 END Derive_Default_Currency_Info;
1629
1630
1631 PROCEDURE Derive_Perf_Currency_Info(
1632 p_project_id NUMBER
1633 , x_currency_record_type OUT NOCOPY NUMBER
1634 , x_currency_code OUT NOCOPY VARCHAR2
1635 , x_currency_type OUT NOCOPY VARCHAR2
1636 , x_return_status IN OUT NOCOPY VARCHAR2
1637 , x_msg_count IN OUT NOCOPY NUMBER
1638 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
1639
1640
1641 BEGIN
1642
1643 IF g_debug_mode = 'Y' THEN
1644 Pji_Utils.WRITE2LOG( 'derive_perf_currency_info: beginning', TRUE , g_proc);
1645 END IF;
1646
1647 IF x_return_status IS NULL THEN
1648 x_msg_count := 0;
1649 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1650 END IF;
1651
1652 /*--bug 5593229
1653 x_currency_record_type:=1;
1654 x_currency_type:='G';
1655 x_currency_code:=Pji_Utils.get_global_primary_currency;
1656 */
1657
1658 IF (Fnd_Profile.value('PJI_DEF_RPT_CUR_TYPE') = 'GLOBAL_CURRENCY') THEN
1659 IF pji_utils.get_setup_parameter('GLOBAL_CURR1_FLAG') = 'Y' THEN --Bug 9067029
1660 x_currency_record_type:= 1;
1661 x_currency_type:= 'G';
1662 x_currency_code:= Pji_Utils.get_global_primary_currency;
1663 ELSE
1664 x_currency_record_type:= 4;
1665 x_currency_type:= 'F';
1666 x_currency_code := g_projfunc_currency_code;
1667 END IF;
1668 ELSIF (Fnd_Profile.value('PJI_DEF_RPT_CUR_TYPE') = 'SEC_GLOBAL_CURRENCY') THEN
1669 IF pji_utils.get_setup_parameter('GLOBAL_CURR2_FLAG') = 'Y' THEN --Bug 9067029
1670 x_currency_record_type:= 2;
1671 x_currency_type:= 'G';
1672 x_currency_code:= Pji_Utils.get_global_secondary_currency;
1673 ELSE
1674 x_currency_record_type:= 4;
1675 x_currency_type:= 'F';
1676 x_currency_code := g_projfunc_currency_code;
1677 END IF;
1678 ELSIF (Fnd_Profile.value('PJI_DEF_RPT_CUR_TYPE') = 'PROJ_CURRENCY') THEN
1679 x_currency_record_type:= 8;
1680 x_currency_type:= 'P';
1681 x_currency_code:= g_proj_currency_code;
1682 ELSE
1683 x_currency_record_type:= 4;
1684 x_currency_type:= 'F';
1685 x_currency_code := g_projfunc_currency_code;
1686 END IF;
1687
1688 IF g_debug_mode = 'Y' THEN
1689 Pji_Utils.WRITE2LOG( 'derive_default_currency_info: finishing', TRUE , g_proc);
1690 END IF;
1691
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 x_msg_count := x_msg_count + 1;
1695 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1696 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_Currency_Info');
1697 RAISE;
1698 END Derive_Perf_Currency_Info;
1699
1700 FUNCTION Derive_FactorBy(
1701 p_project_id NUMBER
1702 , p_fin_plan_version_id NUMBER
1703 , x_return_status IN OUT NOCOPY VARCHAR2
1704 , x_msg_count IN OUT NOCOPY NUMBER
1705 , x_msg_data IN OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
1706 l_factor_by_code VARCHAR2(30);
1707 BEGIN
1708
1709 IF g_debug_mode = 'Y' THEN
1710 Pji_Utils.WRITE2LOG( 'derive_factorby: beginning', TRUE , g_proc);
1711 END IF;
1712
1713 IF x_return_status IS NULL THEN
1714 x_msg_count := 0;
1715 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1716 END IF;
1717
1718 IF p_fin_plan_version_id IS NOT NULL THEN
1719 /*
1720 ** Pick the default factor by defined for the selected
1721 ** plan_type.
1722 */
1723 SELECT factor_by_code
1724 INTO l_factor_by_code
1725 FROM pa_proj_fp_options
1726 WHERE 1=1
1727 AND fin_plan_option_level_code = 'PLAN_VERSION'
1728 AND project_id = p_project_id
1729 AND fin_plan_version_id = p_fin_plan_version_id;
1730 ELSE
1731 /*
1732 ** In project performance we display data for more
1733 ** than one plan type hence we donot know what plantype
1734 ** to derive the default value from. That is the reason
1735 ** why we have hard coded it to be 1.
1736 */
1737 l_factor_by_code:= 1;
1738 END IF;
1739
1740 IF g_debug_mode = 'Y' THEN
1741 Pji_Utils.WRITE2LOG( 'derive_factorby: returning', TRUE , g_proc);
1742 END IF;
1743
1744 RETURN l_factor_by_code;
1745 EXCEPTION
1746 WHEN NO_DATA_FOUND THEN
1747 RETURN '1';
1748 WHEN OTHERS THEN
1749 x_msg_count := x_msg_count + 1;
1750 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1751 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_FactorBy');
1752 RETURN '1';
1753 END Derive_FactorBy;
1754
1755 FUNCTION Derive_Prg_Rollup_Flag(
1756 p_project_id NUMBER) RETURN VARCHAR2 IS
1757 l_return_status VARCHAR2(1000);
1758 l_msg_count NUMBER;
1759 l_msg_data VARCHAR2(2000);
1760 BEGIN
1761
1762 IF g_debug_mode = 'Y' THEN
1763 Pji_Utils.WRITE2LOG( 'derive_prg_rollup_flag: beginning', TRUE , g_proc);
1764 END IF;
1765
1766 -- IF p_project_id <> g_project_id THEN
1767 /*
1768 ** Refresh all cached values for the project.
1769 */
1770 Derive_Project_Attributes(p_project_id, l_return_status, l_msg_count, l_msg_data);
1771 -- END IF;
1772
1773 RETURN g_prg_flag;
1774
1775 -- RETURN 'N'; --bug 4127656 temporarily turn off program reporting
1776
1777 IF g_debug_mode = 'Y' THEN
1778 Pji_Utils.WRITE2LOG( 'derive_prg_rollup_flag: finishing', TRUE , g_proc);
1779 END IF;
1780
1781 EXCEPTION
1782 WHEN OTHERS THEN
1783 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_prg_rollup_flag');
1784 RETURN NULL;
1785 END Derive_Prg_Rollup_Flag;
1786
1787 FUNCTION Derive_Perf_Prg_Rollup_Flag(
1788 p_project_id NUMBER) RETURN VARCHAR2 IS
1789 BEGIN
1790 RETURN 'N';
1791 END;
1792
1793 PROCEDURE Derive_Project_Attributes(
1794 p_project_id NUMBER
1795 , x_return_status IN OUT NOCOPY VARCHAR2
1796 , x_msg_count IN OUT NOCOPY NUMBER
1797 , x_msg_data IN OUT NOCOPY VARCHAR2) IS
1798 BEGIN
1799
1800 IF g_debug_mode = 'Y' THEN
1801 Pji_Utils.WRITE2LOG( 'derive_project_attributes: beginning', TRUE , g_proc);
1802 END IF;
1803
1804 IF x_return_status IS NULL THEN
1805 x_msg_count := 0;
1806 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1807 END IF;
1808
1809 -- IF p_project_id <> g_project_id THEN
1810 /*
1811 ** Reset the values of the project cache to null to ensure
1812 ** that cache never reflects out of sync values.
1813 */
1814 g_project_org_id:=NULL;
1815 g_proj_currency_code:=NULL;
1816 g_prg_flag:=NULL;
1817 g_gl_calendar_id:=NULL;
1818 g_pa_calendar_id:=NULL;
1819 g_project_id := p_project_id;
1820 /*
1821 ** Derive all the required values for a given project. The
1822 ** join to org_extr table will ensure that we always return
1823 ** data for summarized projects.
1824 */
1825 SELECT
1826 prj.org_id
1827 , prj.project_currency_code
1828 , prj.projfunc_currency_code
1829 , NVL(prj.sys_program_flag,'N')
1830 , info.gl_calendar_id
1831 , info.pa_calendar_id
1832 INTO
1833 g_project_org_id
1834 , g_proj_currency_code
1835 , g_projfunc_currency_code
1836 , g_prg_flag
1837 , g_gl_calendar_id
1838 , g_pa_calendar_id
1839 FROM pa_projects_all prj
1840 , pji_org_extr_info info
1841 WHERE project_id = p_project_id
1842 /* AND NVL(info.org_id,-99) = NVL(prj.org_id,-99); -- Added NVL for bug 3989132 */
1843 AND info.org_id = prj.org_id; -- Removed NVL for Bug5376591
1844
1845 IF g_debug_mode = 'Y' THEN
1846 Pji_Utils.WRITE2LOG( 'derive_project_attributes: finishing', TRUE , g_proc);
1847 END IF;
1848
1849 -- END IF;
1850 EXCEPTION
1851 WHEN NO_DATA_FOUND THEN
1852 x_msg_count := x_msg_count + 1;
1853 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1854 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'PROJ ATTRIBUTES');
1855 WHEN OTHERS THEN
1856 x_msg_count := x_msg_count + 1;
1857 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1858 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Project_Attributes');
1859 RAISE;
1860 END Derive_Project_Attributes;
1861
1862 /*
1863 * Not for project performance, only for workplan and fin plan
1864 */
1865 PROCEDURE Derive_Default_RBS_Parameters(
1866 p_project_id NUMBER
1867 ,p_plan_version_id NUMBER
1868 , x_rbs_version_id OUT NOCOPY NUMBER
1869 , x_rbs_element_id OUT NOCOPY NUMBER
1870 , x_return_status IN OUT NOCOPY VARCHAR2
1871 , x_msg_count IN OUT NOCOPY NUMBER
1872 , x_msg_data IN OUT NOCOPY VARCHAR2)
1873 IS
1874 BEGIN
1875
1876 IF g_debug_mode = 'Y' THEN
1877 Pji_Utils.WRITE2LOG( 'derive_default_rbs_parameters: beginning', TRUE , g_proc);
1878 END IF;
1879
1880
1881 IF x_return_status IS NULL THEN
1882 x_msg_count := 0;
1883 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1884 END IF;
1885
1886 SELECT rbs_version_id
1887 INTO x_rbs_version_id
1888 FROM
1889 pa_proj_fp_options
1890 WHERE fin_plan_version_id = p_plan_version_id;
1891
1892 IF x_rbs_version_id IS NULL THEN
1893 RAISE NO_DATA_FOUND;
1894 END IF;
1895
1896 Derive_Default_Rbs_Element_Id(
1897 x_rbs_version_id
1898 , x_rbs_element_id
1899 , x_return_status
1900 , x_msg_count
1901 , x_msg_data);
1902
1903 IF g_debug_mode = 'Y' THEN
1904 Pji_Utils.WRITE2LOG( 'derive_default_rbs_parameters: finishing', TRUE , g_proc);
1905 END IF;
1906
1907 EXCEPTION
1908 WHEN NO_DATA_FOUND THEN
1909 x_rbs_version_id := -99;
1910 x_rbs_element_id := -99;
1911 /* x_msg_count := x_msg_count + 1;
1912 x_return_status := Pji_Rep_Util.G_RET_STS_WARNING;
1913 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_VP_NO_RBS_VERSION', p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING);
1914 */ WHEN OTHERS THEN
1915 x_msg_count := x_msg_count + 1;
1916 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1917 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_RBS_Parameters');
1918 RAISE;
1919 END Derive_Default_RBS_Parameters;
1920
1921
1922 /*
1923 * Not for fin plan and work plan, only for project performance
1924 */
1925 PROCEDURE Derive_Perf_RBS_Parameters(
1926 p_project_id NUMBER
1927 ,p_plan_version_id NUMBER
1928 ,p_prg_flag VARCHAR DEFAULT 'N'
1929 , x_rbs_version_id OUT NOCOPY NUMBER
1930 , x_rbs_element_id OUT NOCOPY NUMBER
1931 , x_return_status IN OUT NOCOPY VARCHAR2
1932 , x_msg_count IN OUT NOCOPY NUMBER
1933 , x_msg_data IN OUT NOCOPY VARCHAR2)
1934 IS
1935 CURSOR c_rbs_versions IS
1936 SELECT rbs_version_id
1937 FROM
1938 pa_rbs_prj_assignments
1939 WHERE
1940 project_id = p_project_id
1941 AND assignment_status ='ACTIVE'
1942 AND prog_rep_usage_flag IN ('Y',p_prg_flag)
1943 ORDER BY primary_reporting_rbs_flag DESC;
1944 BEGIN
1945
1946 IF g_debug_mode = 'Y' THEN
1947 Pji_Utils.WRITE2LOG( 'derive_perf_rbs_parameters: beginning', TRUE , g_proc);
1948 END IF;
1949
1950 IF x_return_status IS NULL THEN
1951 x_msg_count := 0;
1952 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1953 END IF;
1954
1955
1956 OPEN c_rbs_versions;
1957 FETCH c_rbs_versions INTO x_rbs_version_id;
1958
1959 IF c_rbs_versions%NOTFOUND THEN
1960 CLOSE c_rbs_versions; /* Added for bug 7270236 */
1961 RAISE NO_DATA_FOUND;
1962 END IF;
1963
1964 CLOSE c_rbs_versions; --bug#3877822
1965
1966 IF x_rbs_version_id IS NULL THEN
1967 RAISE NO_DATA_FOUND;
1968 END IF;
1969
1970 Derive_Default_Rbs_Element_Id(
1971 x_rbs_version_id
1972 , x_rbs_element_id
1973 , x_return_status
1974 , x_msg_count
1975 , x_msg_data);
1976
1977 IF g_debug_mode = 'Y' THEN
1978 Pji_Utils.WRITE2LOG( 'derive_perf_rbs_parameters: finishing', TRUE , g_proc);
1979 END IF;
1980
1981 EXCEPTION
1982 WHEN NO_DATA_FOUND THEN
1983 x_rbs_version_id := -99;
1984 x_rbs_element_id := -99;
1985 /* x_msg_count := x_msg_count + 1;
1986 x_return_status := Pji_Rep_Util.G_RET_STS_WARNING;
1987 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_PERF_NO_RBS_VERSION', p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING);
1988 */ WHEN OTHERS THEN
1989 x_msg_count := x_msg_count + 1;
1990 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1991 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Perf_RBS_Parameters');
1992 RAISE;
1993 END Derive_Perf_RBS_Parameters;
1994
1995
1996 PROCEDURE Derive_Default_RBS_Element_Id(
1997 p_rbs_version_id NUMBER
1998 , x_rbs_element_id OUT NOCOPY NUMBER
1999 , x_return_status IN OUT NOCOPY VARCHAR2
2000 , x_msg_count IN OUT NOCOPY NUMBER
2001 , x_msg_data IN OUT NOCOPY VARCHAR2)
2002 IS
2003 CURSOR c_rbs_elements IS
2004 SELECT rbs_element_id
2005 FROM
2006 pa_rbs_elements rbs
2007 WHERE
2008 rbs.rbs_version_id = p_rbs_version_id
2009 AND rbs.rbs_level = 1
2010 ORDER BY rbs.user_created_flag;
2011
2012 BEGIN
2013
2014 IF g_debug_mode = 'Y' THEN
2015 Pji_Utils.WRITE2LOG( 'derive_default_rbs_element_id: beginning', TRUE , g_proc);
2016 END IF;
2017
2018 IF x_return_status IS NULL THEN
2019 x_msg_count := 0;
2020 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2021 END IF;
2022
2023 OPEN c_rbs_elements;
2024 FETCH c_rbs_elements INTO x_rbs_element_id;
2025
2026 IF c_rbs_elements%NOTFOUND THEN
2027 RAISE NO_DATA_FOUND;
2028 END IF;
2029
2030 CLOSE c_rbs_elements; -- Bug#3877822
2031
2032 IF g_debug_mode = 'Y' THEN
2033 Pji_Utils.WRITE2LOG( 'derive_default_rbs_element_id: finishing', TRUE , g_proc);
2034 END IF;
2035
2036 EXCEPTION
2037 WHEN NO_DATA_FOUND THEN
2038 x_msg_count := x_msg_count + 1;
2039 x_return_status := Pji_Rep_Util.G_RET_STS_WARNING;
2040 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'RBS ELEMENT');
2041 WHEN OTHERS THEN
2042 x_msg_count := x_msg_count + 1;
2043 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2044 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_RBS_Element_Id');
2045 RAISE;
2046 END Derive_Default_RBS_Element_Id;
2047
2048 PROCEDURE Derive_Default_WBS_Parameters(
2049 p_project_id NUMBER
2050 ,p_plan_version_id NUMBER
2051 , x_wbs_version_id OUT NOCOPY NUMBER
2052 , x_wbs_element_id OUT NOCOPY NUMBER
2053 , x_return_status IN OUT NOCOPY VARCHAR2
2054 , x_msg_count IN OUT NOCOPY NUMBER
2055 , x_msg_data IN OUT NOCOPY VARCHAR2)
2056 IS
2057 BEGIN
2058
2059 IF g_debug_mode = 'Y' THEN
2060 Pji_Utils.WRITE2LOG( 'derive_default_wbs_parameter: beginning', TRUE , g_proc);
2061 END IF;
2062
2063 IF x_return_status IS NULL THEN
2064 x_msg_count := 0;
2065 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2066 END IF;
2067
2068 BEGIN
2069 /*
2070 SELECT hdr.wbs_version_id
2071 INTO x_wbs_version_id
2072 FROM
2073 pji_pjp_wbs_header hdr
2074 WHERE
2075 hdr.project_id = p_project_id
2076 AND hdr.plan_version_id = p_plan_version_id;*/ -- commented this as part of 16507592
2077 /*added below as part of 16507592*/
2078 SELECT wbs_version_id into x_wbs_version_id
2079 FROM
2080 (SELECT hdr.wbs_version_id
2081 FROM pa_projects_all p ,
2082 pji_pjp_wbs_header hdr
2083 WHERE p.project_id = p_project_id and
2084 hdr.project_id = p.project_id
2085 AND hdr.plan_version_id = p_plan_version_id
2086 AND NVL(p.pjt_rollup_enabled_flag,0) = 'N'
2087 UNION ALL
2088 SELECT pa_project_structure_utils.GET_FIN_STRUC_VER_ID(p_project_id)
2089 FROM pa_projects_all p ,
2090 pa_budget_versions b
2091 WHERE p.project_id = p_project_id
2092 AND b.project_id = p.project_id
2093 AND b.budget_version_id = p_plan_version_id
2094 AND fin_plan_type_id <> 10
2095 AND NVL(p.pjt_rollup_enabled_flag,0) = 'Y'
2096 UNION ALL
2097 SELECT b.project_structure_version_id
2098 FROM pa_projects_all p ,
2099 pa_budget_versions b
2100 WHERE p.project_id = p_project_id
2101 AND b.project_id = p.project_id
2102 AND b.budget_version_id = p_plan_version_id
2103 AND fin_plan_type_id = 10
2104 AND NVL(p.pjt_rollup_enabled_flag,0) = 'Y'
2105 ) ;
2106 EXCEPTION
2107 WHEN NO_DATA_FOUND THEN
2108 RETURN;
2109 END;
2110
2111 BEGIN
2112 SELECT elm.proj_element_id
2113 INTO x_wbs_element_id
2114 FROM pa_proj_element_versions elm
2115 WHERE elm.element_version_id = x_wbs_version_id;
2116 EXCEPTION
2117 WHEN NO_DATA_FOUND THEN
2118 x_msg_count := x_msg_count + 1;
2119 x_return_status := Pji_Rep_Util.G_RET_STS_WARNING;
2120 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'WBS ELEMENT');
2121 END;
2122
2123 /* SELECT hdr.wbs_version_id,elm.proj_element_id
2124 INTO x_wbs_version_id, x_wbs_element_id
2125 FROM
2126 pji_pjp_wbs_header hdr
2127 , pa_proj_element_versions elm
2128 WHERE
2129 hdr.wbs_version_id = elm.element_version_id
2130 AND hdr.project_id = p_project_id
2131 AND hdr.plan_version_id = p_plan_version_id;
2132 */
2133 IF g_debug_mode = 'Y' THEN
2134 Pji_Utils.WRITE2LOG( 'derive_default_wbs_parameter: finishing', TRUE , g_proc);
2135 END IF;
2136
2137 EXCEPTION
2138 WHEN OTHERS THEN
2139 x_msg_count := x_msg_count + 1;
2140 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2141 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_WBS_Parameters');
2142 RAISE;
2143 END Derive_Default_WBS_Parameters;
2144
2145 PROCEDURE Derive_WP_WBS_Parameters(
2146 p_project_id NUMBER
2147 , x_wbs_version_id OUT NOCOPY NUMBER
2148 , x_wbs_element_id OUT NOCOPY NUMBER
2149 , x_return_status IN OUT NOCOPY VARCHAR2
2150 , x_msg_count IN OUT NOCOPY NUMBER
2151 , x_msg_data IN OUT NOCOPY VARCHAR2)
2152 IS
2153 CURSOR c_wbs_params IS
2154 SELECT element_version_id, proj_element_id
2155 FROM
2156 pa_proj_elem_ver_structure
2157 WHERE
2158 project_id = p_project_id
2159 ORDER BY NVL(Latest_eff_published_flag,'N') DESC, NVL(current_working_flag,'N') DESC;
2160 BEGIN
2161
2162 IF g_debug_mode = 'Y' THEN
2163 Pji_Utils.WRITE2LOG( 'derive_wp_wbs_parameter: beginning', TRUE , g_proc);
2164 END IF;
2165
2166 IF x_return_status IS NULL THEN
2167 x_msg_count := 0;
2168 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2169 END IF;
2170
2171 OPEN c_wbs_params;
2172 FETCH c_wbs_params INTO x_wbs_version_id,x_wbs_element_id;
2173
2174 IF c_wbs_params%NOTFOUND THEN
2175 RAISE NO_DATA_FOUND;
2176 END IF;
2177
2178 CLOSE c_wbs_params; --bug#3877822
2179
2180 IF g_debug_mode = 'Y' THEN
2181 Pji_Utils.WRITE2LOG( 'derive_wp_wbs_parameter: finishing', TRUE , g_proc);
2182 END IF;
2183
2184 EXCEPTION
2185 WHEN OTHERS THEN
2186 x_msg_count := x_msg_count + 1;
2187 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2188 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_WP_WBS_Parameters');
2189 RAISE;
2190 END Derive_WP_WBS_Parameters;
2191
2192
2193
2194 /*
2195 * Slice name is decide by the calendar type and the number
2196 * of records in the time tables.
2197 * if the calendar type is "E" (same as calendar_id =-1), operation goes to the _ENT_ table
2198 * otherwise operation goes to the _CAL_ table
2199 * in the sequence of Period, Quarter and Year, if the qualified records in the table is
2200 * less or equals to 12, then use that table name as the slice name otherwise go to the next table
2201 */
2202 PROCEDURE Derive_Slice_Name(
2203 p_project_id NUMBER
2204 , p_calendar_id NUMBER
2205 , x_slice_name OUT NOCOPY VARCHAR2
2206 , x_return_status IN OUT NOCOPY VARCHAR2
2207 , x_msg_count IN OUT NOCOPY NUMBER
2208 , x_msg_data IN OUT NOCOPY VARCHAR2)
2209 IS
2210 l_start_date DATE;
2211 l_end_date DATE;
2212 l_rec_count NUMBER;
2213 BEGIN
2214
2215 Pa_Debug.init_err_stack('PJI_REP_UTIL.Derive_Slice_Name');
2216 IF g_debug_mode = 'Y' THEN
2217 Pa_Debug.write_file('Derive_Slice_Name: beginning',5);
2218 -- Pji_Utils.WRITE2LOG( 'derive_slice_name: beginning', TRUE , g_proc);
2219 END IF;
2220
2221 IF x_return_status IS NULL THEN
2222 x_msg_count := 0;
2223 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2224 END IF;
2225
2226 SELECT start_date,NVL(completion_date,TRUNC(SYSDATE))
2227 INTO l_start_date, l_end_date
2228 FROM pa_projects_all
2229 WHERE project_id = p_project_id;
2230
2231 IF p_calendar_id = -1 THEN
2232
2233 SELECT COUNT(*)
2234 INTO l_rec_count
2235 FROM pji_time_ent_period_v
2236 WHERE start_date >= l_start_date
2237 AND end_date <= l_end_date;
2238
2239 IF l_rec_count > 12 THEN
2240
2241 SELECT COUNT(*)
2242 INTO l_rec_count
2243 FROM pji_time_ent_qtr_v
2244 WHERE start_date >= l_start_date
2245 AND end_date <= l_end_date;
2246
2247 IF l_rec_count <= 1 THEN
2248 x_slice_name := 'PJI_TIME_ENT_PERIOD_V';
2249 ELSIF l_rec_count > 12 THEN
2250
2251 SELECT COUNT(*)
2252 INTO l_rec_count
2253 FROM pji_time_ent_year_v
2254 WHERE start_date >= l_start_date
2255 AND end_date <= l_end_date;
2256
2257 IF l_rec_count <=1 THEN
2258 x_slice_name := 'PJI_TIME_ENT_QTR_V';
2259 ELSE
2260 x_slice_name := 'PJI_TIME_ENT_YEAR_V';
2261 END IF;
2262 ELSE
2263 x_slice_name := 'PJI_TIME_ENT_QTR_V';
2264 END IF;
2265 ELSE
2266 x_slice_name := 'PJI_TIME_ENT_PERIOD_V';
2267 END IF;
2268 ELSE
2269
2270 SELECT COUNT(*)
2271 INTO l_rec_count
2272 FROM pji_time_cal_period_v
2273 WHERE start_date >= l_start_date
2274 AND end_date <= l_end_date
2275 AND calendar_id = p_calendar_id;
2276
2277 IF l_rec_count > 12 THEN
2278
2279 SELECT COUNT(*)
2280 INTO l_rec_count
2281 FROM pji_time_cal_qtr_v
2282 WHERE start_date >= l_start_date
2283 AND end_date <= l_end_date
2284 AND calendar_id = p_calendar_id;
2285
2286 IF l_rec_count <= 1 THEN
2287 x_slice_name := 'PJI_TIME_CAL_PERIOD_V';
2288 ELSIF l_rec_count > 12 THEN
2289
2290 SELECT COUNT(*)
2291 INTO l_rec_count
2292 FROM pji_time_cal_year_v
2293 WHERE start_date >= l_start_date
2294 AND end_date <= l_end_date
2295 AND calendar_id = p_calendar_id;
2296
2297 IF l_rec_count <=1 THEN
2298 x_slice_name := 'PJI_TIME_CAL_QTR_V';
2299 ELSE
2300 x_slice_name := 'PJI_TIME_CAL_YEAR_V';
2301 END IF;
2302 ELSE
2303 x_slice_name := 'PJI_TIME_CAL_QTR_V';
2304 END IF;
2305 ELSE
2306 x_slice_name := 'PJI_TIME_CAL_PERIOD_V';
2307 END IF;
2308
2309 END IF;
2310
2311 IF g_debug_mode = 'Y' THEN
2312 -- Pji_Utils.WRITE2LOG( 'derive_slice_name: finishing', TRUE , g_proc);
2313 Pa_Debug.write_file('Derive_Slice_Name: returning',5);
2314 END IF;
2315 Pa_Debug.Reset_Err_Stack;
2316
2317 EXCEPTION
2318 WHEN NO_DATA_FOUND THEN
2319 x_msg_count := x_msg_count + 1;
2320 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2321 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'SLICE NAME');
2322 WHEN OTHERS THEN
2323 x_msg_count := x_msg_count + 1;
2324 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2325 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Slice_Name');
2326 RAISE;
2327 END Derive_Slice_Name;
2328
2329 PROCEDURE Derive_Plan_Type_Parameters(
2330 p_project_id NUMBER
2331 , p_fin_plan_type_id NUMBER
2332 , x_plan_pref_code OUT NOCOPY VARCHAR2
2333 , x_budget_forecast_flag OUT NOCOPY VARCHAR2
2334 , x_plan_type_name OUT NOCOPY VARCHAR2
2335 , x_plan_report_mask OUT NOCOPY VARCHAR2
2336 , x_plan_margin_mask OUT NOCOPY VARCHAR2
2337 , x_cost_app_flag IN OUT NOCOPY VARCHAR2
2338 , x_rev_app_flag IN OUT NOCOPY VARCHAR2
2339 , x_return_status IN OUT NOCOPY VARCHAR2
2340 , x_msg_count IN OUT NOCOPY NUMBER
2341 , x_msg_data IN OUT NOCOPY VARCHAR2)
2342 IS
2343 l_class_code VARCHAR2(30);
2344 BEGIN
2345
2346 IF x_return_status IS NULL THEN
2347 x_msg_count := 0;
2348 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2349 END IF;
2350
2351 IF g_debug_mode = 'Y' THEN
2352 Pji_Utils.WRITE2LOG( 'derive_plan_type_parameters: begining', TRUE , g_proc);
2353 END IF;
2354
2355 SELECT pt.plan_class_code
2356 , pt.NAME
2357 , op.FIN_PLAN_PREFERENCE_CODE
2358 , op.MARGIN_DERIVED_FROM_CODE
2359 , DECODE(op.fin_plan_preference_code,
2360 'COST_AND_REV_SEP',op.report_labor_hrs_from_code,
2361 'REVENUE_ONLY','REVENUE',
2362 'COST')
2363 , NVL(op.approved_cost_plan_type_flag,'N')
2364 , NVL(op.approved_rev_plan_type_flag, 'N')
2365 INTO l_class_code
2366 , x_plan_type_name
2367 , x_plan_pref_code
2368 , x_plan_margin_mask
2369 , x_plan_report_mask
2370 , x_cost_app_flag
2371 , x_rev_app_flag
2372 FROM pa_fin_plan_types_vl pt
2373 , pa_proj_fp_options op
2374 WHERE 1=1
2375 AND pt.fin_plan_type_id = p_fin_plan_type_id
2376 AND op.fin_plan_type_id = pt.fin_plan_type_id
2377 AND op.fin_plan_option_level_code = 'PLAN_TYPE'
2378 AND op.project_id = p_project_id;
2379
2380 IF l_class_code = 'BUDGET' THEN
2381 x_budget_forecast_flag := 'B';
2382 ELSE
2383 x_budget_forecast_flag := 'F';
2384 END IF;
2385
2386 IF g_debug_mode = 'Y' THEN
2387 Pji_Utils.WRITE2LOG( 'derive_plan_type_parameters: finishing', TRUE , g_proc);
2388 END IF;
2389
2390 EXCEPTION
2391 WHEN NO_DATA_FOUND THEN
2392 x_msg_count := x_msg_count + 1;
2393 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2394 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'PLAN TYPE PARAMETERS');
2395 WHEN OTHERS THEN
2396 x_msg_count := x_msg_count + 1;
2397 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2398 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Plan_Type_Parameters');
2399 RAISE;
2400 END Derive_Plan_Type_Parameters;
2401
2402
2403 PROCEDURE Derive_Version_Margin_Mask(
2404 p_project_id NUMBER
2405 , p_plan_version_id NUMBER
2406 , x_plan_margin_mask OUT NOCOPY VARCHAR2
2407 , x_return_status IN OUT NOCOPY VARCHAR2
2408 , x_msg_count IN OUT NOCOPY NUMBER
2409 , x_msg_data IN OUT NOCOPY VARCHAR2)
2410 IS
2411 BEGIN
2412
2413 IF x_return_status IS NULL THEN
2414 x_msg_count := 0;
2415 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2416 END IF;
2417
2418 IF g_debug_mode = 'Y' THEN
2419 Pji_Utils.WRITE2LOG( 'derive_version_margin_mask: begining', TRUE , g_proc);
2420 END IF;
2421
2422 SELECT op.MARGIN_DERIVED_FROM_CODE
2423 INTO x_plan_margin_mask
2424 FROM pa_proj_fp_options op
2425 WHERE op.fin_plan_version_id = p_plan_version_id
2426 AND op.fin_plan_option_level_code = 'PLAN_VERSION'
2427 AND op.project_id = p_project_id;
2428
2429 IF g_debug_mode = 'Y' THEN
2430 Pji_Utils.WRITE2LOG( 'derive_version_margin_mask: finishing', TRUE , g_proc);
2431 END IF;
2432 EXCEPTION
2433 WHEN NO_DATA_FOUND THEN
2434 x_msg_count := x_msg_count + 1;
2435 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2436 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'PLAN VERSION MARGIN MASK');
2437 x_plan_margin_mask := 'B';
2438 WHEN OTHERS THEN
2439 x_msg_count := x_msg_count + 1;
2440 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2441 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Version_Margin_Mask');
2442 RAISE;
2443 END Derive_Version_Margin_Mask;
2444
2445 PROCEDURE Derive_Version_Parameters(
2446 p_version_id NUMBER
2447 , x_version_name OUT NOCOPY VARCHAR2
2448 , x_version_no OUT NOCOPY VARCHAR2
2449 , x_version_record_no OUT NOCOPY VARCHAR2
2450 , x_budget_status_code OUT NOCOPY VARCHAR2
2451 , x_return_status IN OUT NOCOPY VARCHAR2
2452 , x_msg_count IN OUT NOCOPY NUMBER
2453 , x_msg_data IN OUT NOCOPY VARCHAR2)
2454 IS
2455 BEGIN
2456
2457 IF g_debug_mode = 'Y' THEN
2458 Pji_Utils.WRITE2LOG( 'derive_version_parameters: begining', TRUE , g_proc);
2459 END IF;
2460
2461 IF x_return_status IS NULL THEN
2462 x_msg_count := 0;
2463 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2464 END IF;
2465
2466 SELECT version_number, version_name, record_version_number,budget_status_code
2467 INTO x_version_no, x_version_name, x_version_record_no , x_budget_status_code
2468 FROM pa_budget_versions
2469 WHERE budget_version_id = p_version_id;
2470
2471 IF g_debug_mode = 'Y' THEN
2472 Pji_Utils.WRITE2LOG( 'derive_version_parameters: finishing', TRUE , g_proc);
2473 END IF;
2474
2475 EXCEPTION
2476 WHEN NO_DATA_FOUND THEN
2477 x_msg_count := x_msg_count + 1;
2478 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2479 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_NO_PLAN_VERSION', p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING);
2480 WHEN OTHERS THEN
2481 x_msg_count := x_msg_count + 1;
2482 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2483 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Version_Parameters');
2484 RAISE;
2485 END Derive_Version_Parameters;
2486
2487 PROCEDURE Derive_Fin_Plan_Versions(p_project_id NUMBER
2488 ,p_version_id NUMBER
2489 , x_curr_budget_version_id OUT NOCOPY NUMBER
2490 , x_orig_budget_version_id OUT NOCOPY NUMBER
2491 , x_prior_fcst_version_id OUT NOCOPY NUMBER
2492 , x_return_status IN OUT NOCOPY VARCHAR2
2493 , x_msg_count IN OUT NOCOPY NUMBER
2494 , x_msg_data IN OUT NOCOPY VARCHAR2
2495 )
2496 IS
2497 l_return_status VARCHAR2(1000);
2498 l_msg_count NUMBER;
2499 l_msg_data VARCHAR2(1000);
2500 BEGIN
2501
2502
2503 IF g_debug_mode = 'Y' THEN
2504 Pji_Utils.WRITE2LOG( 'derive_fin_plan_versions: begining', TRUE , g_proc);
2505 END IF;
2506
2507 IF x_return_status IS NULL THEN
2508 x_msg_count := 0;
2509 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2510 END IF;
2511
2512 Pa_Planning_Element_Utils.get_finplan_bvids(
2513 p_project_id => p_project_id
2514 ,p_budget_version_id => p_version_id
2515 ,p_view_plan_flag => 'Y' /* Bug#4221225 */
2516 , x_current_version_id => x_curr_budget_version_id
2517 , x_original_version_id => x_orig_budget_version_id
2518 , x_prior_fcst_version_id => x_prior_fcst_version_id
2519 , x_return_status => l_return_status
2520 , x_msg_count => l_msg_count
2521 , x_msg_data => l_msg_data);
2522
2523 --Bug5510794 deriving the correct prior forecast version id
2524 x_prior_fcst_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id(p_version_id, p_project_id);
2525
2526 IF (l_msg_count > 0) THEN
2527 x_msg_count := l_msg_count;
2528 x_return_status := l_return_status;
2529 x_msg_data := l_msg_data;
2530 END IF;
2531
2532
2533 IF x_curr_budget_version_id = -1 THEN
2534 x_curr_budget_version_id := -99;
2535 END IF;
2536
2537 IF x_orig_budget_version_id = -1 THEN
2538 x_orig_budget_version_id := -99;
2539 END IF;
2540
2541 IF x_prior_fcst_version_id = -1 THEN
2542 x_prior_fcst_version_id := -99;
2543 END IF;
2544
2545 IF g_debug_mode = 'Y' THEN
2546 Pji_Utils.WRITE2LOG( 'derive_fin_plan_versions: finishing', TRUE , g_proc);
2547 END IF;
2548
2549 EXCEPTION
2550 WHEN OTHERS THEN
2551 x_msg_count := x_msg_count + 1;
2552 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2553 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Fin_Plan_Versions');
2554 RAISE;
2555 END Derive_Fin_Plan_Versions;
2556
2557
2558 PROCEDURE Derive_Work_Plan_Versions(p_project_id NUMBER
2559 ,p_structure_version_id NUMBER
2560 , x_current_version_id OUT NOCOPY NUMBER
2561 , x_baselined_version_id OUT NOCOPY NUMBER
2562 , x_published_version_id OUT NOCOPY NUMBER
2563 , x_return_status IN OUT NOCOPY VARCHAR2
2564 , x_msg_count IN OUT NOCOPY NUMBER
2565 , x_msg_data IN OUT NOCOPY VARCHAR2
2566 )
2567 IS
2568 l_return_status VARCHAR2(1000);
2569 l_msg_count NUMBER;
2570 l_msg_data VARCHAR2(1000);
2571 BEGIN
2572
2573 IF g_debug_mode = 'Y' THEN
2574 Pji_Utils.WRITE2LOG( 'derive_work_plan_versions: begining', TRUE , g_proc);
2575 END IF;
2576
2577 IF x_return_status IS NULL THEN
2578 x_msg_count := 0;
2579 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2580 END IF;
2581
2582 Pa_Planning_Element_Utils.get_workplan_bvids
2583 (p_project_id
2584 , p_structure_version_id
2585 , x_current_version_id
2586 , x_baselined_version_id
2587 , x_published_version_id
2588 , x_return_status
2589 , x_msg_count
2590 , x_msg_data);
2591
2592 IF (l_msg_count > 0) THEN
2593 x_msg_count := l_msg_count;
2594 x_return_status := l_return_status;
2595 x_msg_data := l_msg_data;
2596 END IF;
2597
2598
2599 IF x_current_version_id = -1 THEN
2600 x_current_version_id := -99;
2601 END IF;
2602
2603 IF x_baselined_version_id = -1 THEN
2604 x_baselined_version_id := -99;
2605 END IF;
2606
2607 IF x_published_version_id = -1 THEN
2608 x_published_version_id := -99;
2609 END IF;
2610
2611 IF g_debug_mode = 'Y' THEN
2612 Pji_Utils.WRITE2LOG( 'derive_work_plan_versions: finishing', TRUE , g_proc);
2613 END IF;
2614
2615 EXCEPTION
2616 WHEN OTHERS THEN
2617 x_msg_count := x_msg_count + 1;
2618 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2619 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Work_Plan_Versions');
2620 RAISE;
2621 END Derive_Work_Plan_Versions;
2622
2623 /*
2624 FUNCTION get_report_date_julian(p_calendar_type VARCHAR2
2625 , p_calendar_id NUMBER
2626 , p_org_id NUMBER) RETURN NUMBER
2627 IS
2628 l_return_status VARCHAR2(1000);
2629 l_msg_count NUMBER;
2630 l_msg_data VARCHAR2(2000);
2631 BEGIN
2632
2633 Pa_Debug.init_err_stack('PJI_REP_UTIL.Get_Report_Date_Julian');
2634 IF g_debug_mode = 'Y' THEN
2635 Pa_Debug.write_file('Get_Report_Date_Julian: begining',5);
2636 -- Pji_Utils.WRITE2LOG( 'derive_report_date_julian: begining', TRUE , g_proc);
2637 END IF;
2638
2639 -- IF (p_calendar_type <> g_input_calendar_type) OR (p_calendar_id <> g_input_calendar_id) THEN
2640 Derive_Period_Julian(p_calendar_type
2641 , p_calendar_id
2642 , p_org_id
2643 , l_return_status
2644 , l_msg_count
2645 , l_msg_data);
2646 g_input_calendar_type := p_calendar_type;
2647 g_input_calendar_id := p_calendar_id;
2648 -- END IF;
2649 RETURN g_report_date_julian;
2650
2651 IF g_debug_mode = 'Y' THEN
2652 -- Pji_Utils.WRITE2LOG( 'derive_report_date_julian: finishing', TRUE , g_proc);
2653 Pa_Debug.write_file('Get_Report_Date_Julian: returning',5);
2654 END IF;
2655 Pa_Debug.Reset_Err_Stack;
2656
2657 EXCEPTION
2658 WHEN OTHERS THEN
2659 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Get_Report_Date_Julian');
2660 RETURN 1;
2661 END Get_Report_Date_Julian;
2662
2663
2664 FUNCTION get_period_name(p_calendar_type VARCHAR2
2665 , p_calendar_id NUMBER
2666 , p_org_id NUMBER) RETURN VARCHAR2
2667 IS
2668 l_return_status VARCHAR2(1000);
2669 l_msg_count NUMBER;
2670 l_msg_data VARCHAR2(2000);
2671 BEGIN
2672
2673 Pa_Debug.init_err_stack('PJI_REP_UTIL.Get_Period_Name');
2674 IF g_debug_mode = 'Y' THEN
2675 Pa_Debug.write_file('Get_Period_Name: begining',5);
2676 -- Pji_Utils.WRITE2LOG( 'get_period_name: begining', TRUE , g_proc);
2677 END IF;
2678
2679 -- IF (p_calendar_type <> g_input_calendar_type) OR (p_calendar_id <> g_input_calendar_id) THEN
2680 Derive_Period_Julian(p_calendar_type
2681 , p_calendar_id
2682 , p_org_id
2683 , l_return_status
2684 , l_msg_count
2685 , l_msg_data);
2686 g_input_calendar_type := p_calendar_type;
2687 g_input_calendar_id := p_calendar_id;
2688 -- END IF;
2689
2690 IF g_debug_mode = 'Y' THEN
2691 -- Pji_Utils.WRITE2LOG( 'get_period_name: returning', TRUE , g_proc);
2692 Pa_Debug.write_file('Get_Period_Name: returning',5);
2693 END IF;
2694 Pa_Debug.Reset_Err_Stack;
2695
2696 RETURN g_period_name;
2697
2698 EXCEPTION
2699 WHEN OTHERS THEN
2700 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Period_Name');
2701 RETURN NULL;
2702 END Get_Period_Name;
2703
2704 */
2705 PROCEDURE Derive_Pa_Calendar_Info(p_project_id NUMBER
2706 , p_calendar_type VARCHAR2
2707 , x_calendar_id OUT NOCOPY NUMBER
2708 , x_report_date_julian OUT NOCOPY NUMBER
2709 , x_period_name OUT NOCOPY VARCHAR2
2710 , x_slice_name OUT NOCOPY VARCHAR2
2711 , x_return_status IN OUT NOCOPY VARCHAR2
2712 , x_msg_count IN OUT NOCOPY NUMBER
2713 , x_msg_data IN OUT NOCOPY VARCHAR2
2714 )
2715 IS
2716 l_curr_rep_gl_period VARCHAR2(30);
2717 l_curr_rep_pa_period VARCHAR2(30);
2718 l_curr_rep_ent_period VARCHAR2(30);
2719 l_specific_pa_period VARCHAR2(30);
2720 l_specific_gl_period VARCHAR2(30);
2721 l_specific_ent_period VARCHAR2(30);
2722 l_specific_period VARCHAR2(30);
2723 l_active_rep VARCHAR2(30);
2724 l_report_date DATE;
2725 l_application_id NUMBER;
2726 l_period_name VARCHAR2(100);
2727 l_gl_calendar_id NUMBER;
2728 l_pa_calendar_id NUMBER;
2729 l_same_pa_gl_period varchar2(1); /* Added for bug 9859878 */
2730 BEGIN
2731 Pa_Debug.init_err_stack('PJI_REP_UTIL.Derive_Period_Julian');
2732 IF g_debug_mode = 'Y' THEN
2733 Pa_Debug.write_file('Derive_Period_Julian: begining',5);
2734 -- Pji_Utils.WRITE2LOG( 'derive_period_julian: begining', TRUE , g_proc);
2735 END IF;
2736
2737 IF p_calendar_type = 'E' THEN
2738 x_calendar_id := -1;
2739 ELSE
2740 SELECT info.gl_calendar_id, info.pa_calendar_id
2741 INTO l_gl_calendar_id, l_pa_calendar_id
2742 FROM pji_org_extr_info info, pa_projects_all proj
2743 WHERE info.org_id = proj.org_id
2744 AND proj.project_id = p_project_id;
2745
2746 IF p_calendar_type = 'G' THEN
2747 x_calendar_id := l_gl_calendar_id;
2748 ELSE
2749 x_calendar_id := l_pa_calendar_id;
2750 END IF;
2751 END IF;
2752
2753 /* Added for bug 9859878
2754 select same_pa_gl_period into l_same_pa_gl_period
2755 from pa_implementations;
2756 */
2757 /* Added for bug 12420222 */
2758 select same_pa_gl_period into l_same_pa_gl_period
2759 from pa_implementations_all where org_id =
2760 (select org_id from pa_projects_all where project_id = p_project_id);
2761 /* Added for bug 12420222 */
2762
2763 Derive_Slice_Name(p_project_id,
2764 x_calendar_id,
2765 x_slice_name,
2766 x_return_status,
2767 x_msg_count,
2768 x_msg_data);
2769
2770
2771 SELECT curr_rep_gl_period, curr_rep_pa_period, curr_rep_ent_period
2772 INTO l_curr_rep_gl_period, l_curr_rep_pa_period, l_curr_rep_ent_period
2773 FROM pji_system_settings;
2774
2775 IF p_calendar_type = 'G' THEN
2776 l_active_rep := l_curr_rep_gl_period;
2777 l_application_id := 101;
2778 ELSIF p_calendar_type = 'P' THEN
2779 l_active_rep := l_curr_rep_pa_period;
2780 l_application_id := 275;
2781 ELSE
2782 l_active_rep := l_curr_rep_ent_period;
2783 END IF;
2784
2785 IF l_active_rep IS NULL THEN
2786 x_msg_count := x_msg_count + 1;
2787 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2788 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_CUR_PERIOD_MISSING', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR);
2789 x_report_date_julian :=2;
2790 RETURN;
2791 END IF;
2792
2793 IF l_active_rep = 'SPECIFIC' THEN
2794 BEGIN
2795
2796 SELECT
2797 info.pa_curr_rep_period,
2798 info.gl_curr_rep_period,
2799 params.value
2800 INTO l_specific_pa_period, l_specific_gl_period, l_specific_ent_period
2801 FROM pji_org_extr_info info,
2802 pji_system_parameters params,
2803 pa_projects_all proj
2804 WHERE proj.project_id = p_project_id
2805 AND info.org_id = proj.org_id
2806 AND params.name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
2807
2808 EXCEPTION
2809 WHEN NO_DATA_FOUND THEN
2810 x_msg_count := x_msg_count + 1;
2811 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2812 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_CUR_PERIOD_MISSING', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR);
2813 x_report_date_julian :=2;
2814 RETURN;
2815 END;
2816
2817 IF p_calendar_type = 'G' THEN
2818 l_specific_period := l_specific_gl_period;
2819 ELSIF p_calendar_type = 'P' THEN
2820 l_specific_period := l_specific_pa_period;
2821 ELSE
2822 l_specific_period := l_specific_ent_period;
2823 END IF;
2824
2825 IF l_specific_period IS NULL THEN
2826 x_msg_count := x_msg_count + 1;
2827 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2828 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_CUR_PERIOD_MISSING', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR);
2829 x_report_date_julian :=2;
2830 RETURN;
2831 END IF;
2832 END IF;
2833
2834
2835 IF p_calendar_type = 'E' THEN
2836 IF l_active_rep IN ('CURRENT','PRIOR') THEN
2837 SELECT start_date
2838 INTO l_report_date
2839 FROM pji_time_ent_period_v
2840 WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date;
2841 END IF;
2842
2843 IF l_active_rep = 'PRIOR' THEN
2844 SELECT MAX(start_date)
2845 INTO l_report_date
2846 FROM pji_time_ent_period_v
2847 WHERE end_date <l_report_date;
2848 END IF;
2849
2850 IF l_active_rep = 'SPECIFIC' THEN
2851 l_period_name := l_specific_period;
2852
2853 SELECT start_date
2854 INTO l_report_date
2855 FROM pji_time_ent_period_v
2856 WHERE name = l_period_name;
2857 ELSE
2858 SELECT name
2859 INTO l_period_name
2860 FROM pji_time_ent_period_v
2861 WHERE l_report_date BETWEEN start_date AND end_date;
2862 END IF;
2863 ELSE
2864 IF l_active_rep ='FIRST_OPEN' THEN
2865 if l_same_pa_gl_period = 'Y' then /* Added for bug 9859878 */
2866 SELECT MIN(TIM.start_date) first_open
2867 INTO l_report_date
2868 FROM
2869 pji_time_cal_period_v TIM
2870 , gl_period_statuses glps
2871 , pa_implementations paimp
2872 WHERE 1=1
2873 AND TIM.calendar_id = x_calendar_id
2874 AND paimp.set_of_books_id = glps.set_of_books_id
2875 AND glps.application_id = l_application_id
2876 AND glps.period_name = TIM.NAME
2877 AND closing_status = 'O';
2878 else /* Added for bug 9859878 */
2879 SELECT MIN(TIM.start_date) first_open
2880 INTO l_report_date
2881 FROM
2882 pji_time_cal_period_v TIM
2883 , pa_periods_all pa
2884 , pa_implementations paimp
2885 WHERE 1=1
2886 AND TIM.calendar_id = x_calendar_id
2887 AND paimp.org_id = pa.org_id
2888 AND pa.period_name = TIM.NAME
2889 AND status = 'O';
2890 end if; /* Added for bug 9859878 */
2891 ELSIF l_active_rep = 'LAST_OPEN' THEN
2892 if l_same_pa_gl_period = 'Y' then /* Added for bug 9859878 */
2893 SELECT MAX(TIM.start_date) last_open
2894 INTO l_report_date
2895 FROM
2896 pji_time_cal_period_v TIM
2897 , gl_period_statuses glps
2898 , pa_implementations paimp
2899 WHERE 1=1
2900 AND TIM.calendar_id = x_calendar_id
2901 AND paimp.set_of_books_id = glps.set_of_books_id
2902 AND glps.application_id = 275
2903 AND glps.period_name = TIM.NAME
2904 AND closing_status = 'O';
2905 else /* Added for bug 9859878 */
2906 SELECT MAX(TIM.start_date) last_open
2907 INTO l_report_date
2908 FROM
2909 pji_time_cal_period_v TIM
2910 , pa_periods_all pa
2911 , pa_implementations paimp
2912 WHERE 1=1
2913 AND TIM.calendar_id = x_calendar_id
2914 AND paimp.org_id = pa.org_id
2915 AND pa.period_name = TIM.NAME
2916 AND status = 'O';
2917 end if; /* Added for bug 9859878 */
2918 ELSIF l_active_rep = 'LAST_CLOSED' THEN
2919 if l_same_pa_gl_period = 'Y' then /* Added for bug 9859878 */
2920 SELECT MAX(TIM.start_date) last_closed
2921 INTO l_report_date
2922 FROM
2923 pji_time_cal_period_v TIM
2924 , gl_period_statuses glps
2925 , pa_implementations paimp
2926 WHERE 1=1
2927 AND TIM.calendar_id = x_calendar_id
2928 AND paimp.set_of_books_id = glps.set_of_books_id
2929 AND glps.application_id = l_application_id
2930 AND glps.period_name = TIM.NAME
2931 AND closing_status = 'C';
2932 else /* Added for bug 9859878 */
2933 SELECT MAX(TIM.start_date) last_open
2934 INTO l_report_date
2935 FROM
2936 pji_time_cal_period_v TIM
2937 , pa_periods_all pa
2938 , pa_implementations paimp
2939 WHERE 1=1
2940 AND TIM.calendar_id = x_calendar_id
2941 AND paimp.org_id = pa.org_id
2942 AND pa.period_name = TIM.NAME
2943 AND status = 'C';
2944 end if; /* Added for bug 9859878 */
2945
2946 ELSIF l_active_rep IN ('CURRENT','PRIOR') THEN
2947 SELECT start_date
2948 INTO l_report_date
2949 FROM pji_time_cal_period_v
2950 WHERE TRUNC(SYSDATE) BETWEEN start_date
2951 AND end_date
2952 AND calendar_id = x_calendar_id;
2953 END IF;
2954
2955 IF l_active_rep = 'PRIOR' THEN
2956 SELECT MAX(start_date)
2957 INTO l_report_date
2958 FROM pji_time_cal_period_v
2959 WHERE end_date < l_report_date
2960 AND calendar_id = x_calendar_id;
2961 END IF;
2962
2963 IF l_active_rep = 'SPECIFIC' THEN
2964 l_period_name := l_specific_period;
2965
2966 SELECT start_date
2967 INTO l_report_date
2968 FROM pji_time_cal_period_v
2969 WHERE name = l_period_name
2970 AND calendar_id = x_calendar_id;
2971 ELSE
2972 SELECT name
2973 INTO l_period_name
2974 FROM pji_time_cal_period_v
2975 WHERE l_report_date BETWEEN start_date AND end_date
2976 AND calendar_id = x_calendar_id;
2977 END IF;
2978 END IF;
2979
2980 x_report_date_julian := TO_CHAR(l_report_date,'j');
2981 IF x_report_date_julian IS NULL THEN
2982 x_report_date_julian :=2;
2983 END IF;
2984 x_period_name := l_period_name;
2985
2986
2987
2988 IF g_debug_mode = 'Y' THEN
2989 -- Pji_Utils.WRITE2LOG( 'derive_period_julian: finishing', TRUE , g_proc);
2990 Pa_Debug.write_file('Derive_Period_Julian: returning',5);
2991 END IF;
2992 Pa_Debug.Reset_Err_Stack;
2993
2994
2995 EXCEPTION
2996 WHEN NO_DATA_FOUND THEN
2997 x_msg_count := x_msg_count + 1;
2998 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2999 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_SYSTEM_ERROR', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'TOKEN_NAME',p_token1_value=>'CURRENT PERIOD');
3000 x_report_date_julian :=2;
3001 WHEN OTHERS THEN
3002 x_msg_count := x_msg_count + 1;
3003 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3004 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Period_Julian');
3005 RAISE;
3006 END Derive_Pa_Calendar_Info;
3007
3008
3009 FUNCTION get_work_plan_actual_version(p_project_id NUMBER
3010 ) RETURN NUMBER
3011 IS
3012 l_struct_sharing_code pa_projects_all.STRUCTURE_SHARING_CODE%TYPE;
3013 BEGIN
3014
3015 IF g_debug_mode = 'Y' THEN
3016 Pji_Utils.WRITE2LOG( 'get_work_plan_actual_version: begining', TRUE , g_proc);
3017 END IF;
3018
3019 l_struct_sharing_code := Pa_Project_Structure_Utils.get_Structure_sharing_code(
3020 p_project_id=> p_project_id );
3021 -- SHARE_FULL
3022 -- SHARE_PARTIAL
3023 -- SPLIT_NO_MAPPING
3024 -- SPLILT_MAPPING
3025
3026 IF g_debug_mode = 'Y' THEN
3027 Pji_Utils.WRITE2LOG( 'get_work_plan_actual_version: returning', TRUE , g_proc);
3028 END IF;
3029
3030 IF (l_struct_sharing_code = 'SPLIT_NO_MAPPING' OR l_struct_sharing_code = 'SPLILT_MAPPING') THEN
3031 RETURN -2;
3032 ELSE
3033 RETURN -1;
3034 END IF;
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037 RETURN -1;
3038 END get_work_plan_actual_version;
3039
3040 FUNCTION get_fin_plan_actual_version(p_project_id NUMBER
3041 ) RETURN NUMBER
3042 IS
3043 BEGIN
3044 RETURN -1;
3045 END get_fin_plan_actual_version;
3046
3047 FUNCTION get_effort_uom(p_project_id NUMBER
3048 ) RETURN NUMBER
3049 IS
3050 BEGIN
3051 RETURN 1;
3052 END get_effort_uom;
3053
3054 -- -----------------------------------------------------------------
3055
3056 -- -------------- --
3057 -- User: aartola --
3058 -- -------------- --
3059
3060 -- -----------------------------------------------------------------
3061 -- Setup Current Reporting Periods
3062 -- -----------------------------------------------------------------
3063
3064 PROCEDURE update_curr_rep_periods(
3065 p_pa_curr_rep_period VARCHAR2,
3066 p_gl_curr_rep_period VARCHAR2,
3067 p_ent_curr_rep_period VARCHAR2
3068 ) AS
3069
3070 -- ----------------------------------------------
3071 -- declare statements --
3072
3073 l_org_id_count NUMBER := 0;
3074 l_ent_period_count NUMBER := 0;
3075
3076 -- ----------------------------------------------
3077
3078 BEGIN
3079 -- ----------------------------------------------
3080 IF g_debug_mode = 'Y' THEN
3081 Pji_Utils.WRITE2LOG( 'update_curr_rep_periods: begining', TRUE , g_proc);
3082 END IF;
3083
3084 -- check if pji_org_extr_info table has a record for p_org_id
3085 -- Count funtion is introduced
3086 SELECT COUNT(info.org_id)
3087 INTO l_org_id_count
3088 FROM pji_org_extr_info info
3089 WHERE 1=1
3090 AND info.org_id = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
3091
3092
3093 IF l_org_id_count = 0
3094 THEN
3095
3096 -- insert p_org_id, pa_curr_rep_period and gl_curr_rep_period (everything else is null)
3097
3098 INSERT
3099 INTO pji_org_extr_info
3100 (
3101 org_id,
3102 pa_curr_rep_period,
3103 gl_curr_rep_period
3104 )
3105 VALUES
3106 (
3107 NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99),
3108 p_pa_curr_rep_period,
3109 p_gl_curr_rep_period
3110 );
3111 ELSE
3112
3113 -- update pa_curr_rep_period and gl_curr_rep_period
3114
3115 UPDATE pji_org_extr_info
3116 SET pa_curr_rep_period = p_pa_curr_rep_period,
3117 gl_curr_rep_period = p_gl_curr_rep_period
3118 WHERE org_id = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
3119 END IF;
3120
3121 -- ----------------------------------------------
3122 --Count funtion is introduced
3123 SELECT COUNT(params.name)
3124 INTO l_ent_period_count
3125 FROM pji_system_parameters params
3126 WHERE 1=1
3127 AND params.name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
3128
3129 IF l_ent_period_count = 0
3130 THEN
3131
3132 INSERT
3133 INTO pji_system_parameters
3134 (
3135 name,
3136 value
3137 )
3138 VALUES
3139 (
3140 'PJI_PJP_ENT_CURR_REP_PERIOD',
3141 p_ent_curr_rep_period
3142 );
3143 ELSE
3144
3145 UPDATE pji_system_parameters
3146 SET value = p_ent_curr_rep_period
3147 WHERE name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
3148
3149
3150
3151 END IF;
3152
3153
3154 -- ----------------------------------------------
3155
3156 COMMIT;
3157
3158 IF g_debug_mode = 'Y' THEN
3159 Pji_Utils.WRITE2LOG( 'update_curr_rep_periods: finishing', TRUE , g_proc);
3160 END IF;
3161
3162
3163 END update_curr_rep_periods;
3164
3165 -- -----------------------------------------------------------------
3166
3167
3168 PROCEDURE get_project_home_default_param
3169 ( p_project_id IN NUMBER,
3170 p_page_Type IN VARCHAR2,
3171 x_fin_plan_type_id IN OUT NOCOPY NUMBER,
3172 x_cost_version_id IN OUT NOCOPY NUMBER,
3173 x_rev_version_id IN OUT NOCOPY NUMBER,
3174 x_struct_version_id IN OUT NOCOPY NUMBER,
3175 x_return_status IN OUT NOCOPY VARCHAR2,
3176 x_msg_count IN OUT NOCOPY NUMBER,
3177 x_msg_data IN OUT NOCOPY VARCHAR2)
3178 IS
3179
3180
3181 l_cost_plan_type_id NUMBER;
3182 l_rev_plan_type_id NUMBER;
3183 l_cost_version_type VARCHAR2(30);
3184 l_rev_version_type VARCHAR2(30);
3185 l_fp_options_id NUMBER;
3186 l_cost_version_id NUMBER;
3187 l_rev_version_id NUMBER;
3188 l_plan_type_id NUMBER;
3189 l_struct_version_id NUMBER;
3190
3191
3192 BEGIN
3193
3194
3195 IF g_debug_mode = 'Y' THEN
3196 Pji_Utils.WRITE2LOG( 'get_project_home_default_parameters: begining', TRUE , g_proc);
3197 END IF;
3198
3199 x_msg_count := 0;
3200 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3201
3202
3203 /* Get the Approved Budget - Cost and Revenue Plan Type */
3204
3205 IF (p_page_type = 'VB') THEN
3206
3207
3208 Pa_Fin_Plan_Utils.Get_Appr_Cost_Plan_Type_Info(
3209 p_project_id => p_project_id
3210 ,x_plan_type_id => l_cost_plan_type_id
3211 ,x_return_status => x_return_status
3212 ,x_msg_count => x_msg_count
3213 ,x_msg_data => x_msg_data);
3214
3215
3216 Pa_Fin_Plan_Utils.Get_Appr_Rev_Plan_Type_Info(
3217 p_project_id => p_project_id
3218 ,x_plan_type_id => l_rev_plan_type_id
3219 ,x_return_status => x_return_status
3220 ,x_msg_count => x_msg_count
3221 ,x_msg_data => x_msg_data);
3222
3223
3224 IF (l_cost_plan_type_id IS NULL) AND (l_rev_plan_type_id IS NULL) THEN
3225
3226 x_msg_data := 'PJI_REP_VP_BDGT_PRJ_SH_NO_PT' ;
3227 RETURN;
3228
3229 END IF;
3230
3231
3232 IF (l_cost_plan_type_id IS NOT NULL) THEN
3233
3234
3235 l_plan_type_id := l_cost_plan_type_id;
3236 l_cost_version_type :=Get_Version_Type(p_project_id,
3237 l_cost_plan_type_id,
3238 Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
3239
3240 Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info(
3241 p_project_id => p_project_id,
3242 p_fin_plan_type_id => l_cost_plan_type_id,
3243 p_version_type => l_cost_version_type,
3244 x_fp_options_id => l_fp_options_id,
3245 x_fin_plan_version_id => l_cost_version_id,
3246 x_return_status => x_return_status,
3247 x_msg_count => x_msg_count,
3248 x_msg_data => x_msg_data);
3249
3250
3251 IF (l_cost_version_id IS NULL) THEN
3252
3253 x_msg_data := 'PJI_REP_VP_BDGT_PRJ_SH_NO_PV' ;
3254 RETURN;
3255
3256 END IF;
3257
3258
3259 END IF;
3260
3261
3262 IF (l_rev_plan_type_id IS NOT NULL) THEN
3263
3264 l_plan_type_id := l_rev_plan_type_id;
3265
3266 l_rev_version_type :=Get_Version_Type( p_project_id,
3267 l_rev_plan_type_id,
3268 Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
3269
3270
3271 Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info(
3272 p_project_id => p_project_id,
3273 p_fin_plan_type_id => l_rev_plan_type_id,
3274 p_version_type => l_rev_version_type,
3275 x_fp_options_id => l_fp_options_id,
3276 x_fin_plan_version_id => l_rev_version_id,
3277 x_return_status => x_return_status,
3278 x_msg_count => x_msg_count,
3279 x_msg_data => x_msg_data);
3280
3281
3282 IF (l_rev_version_id IS NULL) THEN
3283
3284 x_msg_data := 'PJI_REP_VP_BDGT_PRJ_SH_NO_PV' ;
3285 RETURN;
3286
3287 END IF;
3288
3289
3290 END IF;
3291
3292
3293
3294 IF (l_cost_plan_type_id IS NOT NULL) AND (l_rev_plan_type_id IS NOT NULL) THEN
3295
3296 l_plan_type_id := l_cost_plan_type_id;
3297
3298 END IF;
3299 x_fin_plan_type_id := l_plan_type_id;
3300 x_cost_version_id := l_cost_version_id;
3301 x_rev_version_id := l_rev_version_id;
3302
3303
3304 END IF;
3305
3306
3307
3308
3309 /* Get the Primary Forecast - Cost and Revenue Plan Type */
3310
3311 IF (p_page_type = 'VF') THEN
3312
3313
3314
3315 Pa_Fin_Plan_Utils.Is_Pri_Fcst_Cost_PT_Attached(
3316 p_project_id => p_project_id
3317 ,x_plan_type_id => l_cost_plan_type_id
3318 ,x_return_status => x_return_status
3319 ,x_msg_count => x_msg_count
3320 ,x_msg_data => x_msg_data);
3321
3322 Pa_Fin_Plan_Utils.Is_Pri_Fcst_Rev_PT_Attached(
3323 p_project_id => p_project_id
3324 ,x_plan_type_id => l_rev_plan_type_id
3325 ,x_return_status => x_return_status
3326 ,x_msg_count => x_msg_count
3327 ,x_msg_data => x_msg_data);
3328
3329
3330 IF (l_cost_plan_type_id IS NULL) AND (l_rev_plan_type_id IS NULL) THEN
3331 x_msg_data := 'PJI_REP_VP_FCST_PRJ_SH_NO_PT' ;
3332 RETURN;
3333
3334 END IF;
3335
3336 IF (l_cost_plan_type_id IS NOT NULL) THEN
3337
3338
3339 l_plan_type_id := l_cost_plan_type_id;
3340
3341 l_cost_version_type :=Get_Version_Type(p_project_id,
3342 l_cost_plan_type_id,
3343 Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
3344
3345
3346 Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info(
3347 p_project_id => p_project_id,
3348 p_fin_plan_type_id => l_cost_plan_type_id,
3349 p_version_type => l_cost_version_type,
3350 x_fp_options_id => l_fp_options_id,
3351 x_fin_plan_version_id => l_cost_version_id,
3352 x_return_status => x_return_status,
3353 x_msg_count => x_msg_count,
3354 x_msg_data => x_msg_data);
3355
3356
3357 IF (l_cost_version_id IS NULL) THEN
3358
3359 x_msg_data := 'PJI_REP_VP_FCST_PRJ_SH_NO_PV' ;
3360 RETURN;
3361
3362 END IF;
3363
3364 END IF;
3365
3366
3367 IF (l_rev_plan_type_id IS NOT NULL) THEN
3368
3369 l_plan_type_id := l_rev_plan_type_id;
3370
3371 l_rev_version_type :=Get_Version_Type( p_project_id,
3372 l_rev_plan_type_id,
3373 Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
3374
3375
3376 Pa_Fin_Plan_Utils.Get_Curr_Working_Version_Info(
3377 p_project_id => p_project_id,
3378 p_fin_plan_type_id => l_rev_plan_type_id,
3379 p_version_type => l_rev_version_type,
3380 x_fp_options_id => l_fp_options_id,
3381 x_fin_plan_version_id => l_rev_version_id,
3382 x_return_status => x_return_status,
3383 x_msg_count => x_msg_count,
3384 x_msg_data => x_msg_data);
3385
3386
3387 IF (l_rev_version_id IS NULL) THEN
3388
3389 x_msg_data := 'PJI_REP_VP_FCST_PRJ_SH_NO_PV' ;
3390 RETURN;
3391
3392 END IF;
3393
3394
3395 END IF;
3396
3397 IF (l_cost_plan_type_id IS NOT NULL) AND (l_rev_plan_type_id IS NOT NULL) THEN
3398
3399 l_plan_type_id := l_cost_plan_type_id;
3400
3401 END IF;
3402
3403 x_fin_plan_type_id := l_plan_type_id;
3404 x_cost_version_id := l_cost_version_id;
3405 x_rev_version_id := l_rev_version_id;
3406
3407
3408 END IF;
3409
3410
3411
3412 /* Get the Structure version for the work plan */
3413
3414 IF (p_page_type = 'WP') THEN
3415
3416
3417 /* Get the latest wp published version */
3418
3419 l_struct_version_id := Pa_Project_Structure_Utils.GET_LATEST_WP_VERSION(p_project_id);
3420
3421
3422 /* If latest published version is null then get it from currenct working version */
3423
3424
3425 IF l_struct_version_id IS NULL THEN
3426
3427 l_struct_version_id := Pa_Project_Structure_Utils.get_current_working_ver_id(p_project_id);
3428
3429 END IF;
3430
3431 x_struct_version_id := l_struct_version_id;
3432
3433 END IF;
3434
3435 IF g_debug_mode = 'Y' THEN
3436 Pji_Utils.WRITE2LOG( 'get_project_home_default_parameters: finishing', TRUE , g_proc);
3437 END IF;
3438
3439
3440 EXCEPTION
3441 WHEN OTHERS THEN
3442 x_msg_count := 1;
3443 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3444 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.get_project_home_default_param');
3445 RAISE;
3446
3447 END get_project_home_default_param;
3448
3449 PROCEDURE Derive_WP_Period(
3450 p_project_id NUMBER
3451 , p_published_version_id NUMBER
3452 , p_working_version_id NUMBER
3453 , x_from_period OUT NOCOPY NUMBER
3454 , x_to_period OUT NOCOPY NUMBER
3455 , x_return_status IN OUT NOCOPY VARCHAR2
3456 , x_msg_count IN OUT NOCOPY NUMBER
3457 , x_msg_data IN OUT NOCOPY VARCHAR2)
3458 IS
3459 CURSOR c_dates IS
3460 SELECT TO_CHAR(sch.scheduled_start_date,'j'),TO_CHAR(sch.scheduled_finish_date,'j')
3461 FROM
3462 pji_pjp_wbs_header hdr
3463 , pa_proj_elem_ver_schedule sch
3464 WHERE
3465 hdr.project_id = p_project_id
3466 AND hdr.plan_version_id IN ( p_published_version_id,p_working_version_id)
3467 AND sch.element_version_id = hdr.wbs_version_id
3468 ORDER BY DECODE(hdr.plan_version_id, p_published_version_id, 0,1);
3469
3470 BEGIN
3471
3472 IF g_debug_mode = 'Y' THEN
3473 Pji_Utils.WRITE2LOG( 'Derive_WP_Period: begining', TRUE , g_proc);
3474 END IF;
3475
3476 IF x_return_status IS NULL THEN
3477 x_msg_count := 0;
3478 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3479 END IF;
3480
3481 OPEN c_dates;
3482 FETCH c_dates INTO x_from_period,x_to_period;
3483
3484
3485 IF c_dates%NOTFOUND THEN
3486 RAISE NO_DATA_FOUND;
3487 END IF;
3488
3489 CLOSE c_dates;--bug#3877822
3490
3491 IF g_debug_mode = 'Y' THEN
3492 Pji_Utils.WRITE2LOG( 'Derive_WP_Period: finishing', TRUE , g_proc);
3493 END IF;
3494
3495 EXCEPTION
3496 WHEN NO_DATA_FOUND THEN
3497 x_from_period := NULL;
3498 x_to_period := NULL;
3499 WHEN OTHERS THEN
3500 x_msg_count := x_msg_count + 1;
3501 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3502 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_WP_Period');
3503 RAISE;
3504 END Derive_WP_Period;
3505
3506 PROCEDURE Derive_VP_Period(
3507 p_project_id NUMBER
3508 , p_plan_version_id_tbl SYSTEM.pa_num_tbl_type
3509 , x_from_period OUT NOCOPY NUMBER
3510 , x_to_period OUT NOCOPY NUMBER
3511 , x_return_status IN OUT NOCOPY VARCHAR2
3512 , x_msg_count IN OUT NOCOPY NUMBER
3513 , x_msg_data IN OUT NOCOPY VARCHAR2)
3514 IS
3515 BEGIN
3516
3517 IF g_debug_mode = 'Y' THEN
3518 Pji_Utils.WRITE2LOG( 'Derive_VP_Period: begining', TRUE , g_proc);
3519 END IF;
3520
3521 IF x_return_status IS NULL THEN
3522 x_msg_count := 0;
3523 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3524 END IF;
3525
3526 Get_Default_Period_Dates (
3527 p_plan_version_id_tbl
3528 , p_project_id
3529 , x_from_period
3530 , x_to_period
3531 );
3532
3533
3534 IF g_debug_mode = 'Y' THEN
3535 Pji_Utils.WRITE2LOG( 'Derive_VP_Period: finishing', TRUE , g_proc);
3536 END IF;
3537
3538 EXCEPTION
3539 WHEN NO_DATA_FOUND THEN
3540 x_from_period := NULL;
3541 x_to_period := NULL;
3542 WHEN OTHERS THEN
3543 x_msg_count := x_msg_count + 1;
3544 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3545 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_VP_Period');
3546 RAISE;
3547 END Derive_VP_Period;
3548
3549 PROCEDURE Derive_Perf_Period(
3550 p_project_id NUMBER
3551 , p_plan_version_id_tbl SYSTEM.pa_num_tbl_type
3552 , x_from_period OUT NOCOPY NUMBER
3553 , x_to_period OUT NOCOPY NUMBER
3554 , x_return_status IN OUT NOCOPY VARCHAR2
3555 , x_msg_count IN OUT NOCOPY NUMBER
3556 , x_msg_data IN OUT NOCOPY VARCHAR2)
3557 IS
3558 BEGIN
3559
3560 IF g_debug_mode = 'Y' THEN
3561 Pji_Utils.WRITE2LOG( 'Derive_Perf_Period: begining', TRUE , g_proc);
3562 END IF;
3563
3564 IF x_return_status IS NULL THEN
3565 x_msg_count := 0;
3566 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3567 END IF;
3568
3569 Get_Default_Period_Dates (
3570 p_plan_version_id_tbl
3571 , p_project_id
3572 , x_from_period
3573 , x_to_period
3574 );
3575
3576 IF g_debug_mode = 'Y' THEN
3577 Pji_Utils.WRITE2LOG( 'Derive_Perf_Period: finishing', TRUE , g_proc);
3578 END IF;
3579
3580 EXCEPTION
3581 WHEN NO_DATA_FOUND THEN
3582 x_from_period := NULL;
3583 x_to_period := NULL;
3584 WHEN OTHERS THEN
3585 x_msg_count := x_msg_count + 1;
3586 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3587 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Perf_Period');
3588 RAISE;
3589 END Derive_Perf_Period;
3590
3591 FUNCTION Get_Slice_Name(
3592 p_project_id NUMBER
3593 ,p_calendar_id NUMBER) RETURN VARCHAR2
3594 IS
3595 l_slice_name VARCHAR2(50);
3596 l_return_status VARCHAR2(1000);
3597 l_msg_count NUMBER;
3598 l_msg_data VARCHAR2(2000);
3599 BEGIN
3600
3601 Pa_Debug.init_err_stack('PJI_REP_UTIL.Get_Slice_Name');
3602 IF g_debug_mode = 'Y' THEN
3603 Pa_Debug.write_file('Get_Slice_Name: begining',5);
3604 -- Pji_Utils.WRITE2LOG( 'Get_Slice_Name: begining', TRUE , g_proc);
3605 END IF;
3606
3607 Derive_Slice_Name(p_project_id,
3608 p_calendar_id,
3609 l_slice_name,
3610 l_return_status,
3611 l_msg_count,
3612 l_msg_data);
3613
3614
3615 IF g_debug_mode = 'Y' THEN
3616 -- Pji_Utils.WRITE2LOG( 'Get_Slice_Name: returning', TRUE , g_proc);
3617 Pa_Debug.write_file('Get_Slice_Name: returning',5);
3618 END IF;
3619 Pa_Debug.Reset_Err_Stack;
3620
3621 RETURN l_slice_name;
3622
3623 EXCEPTION
3624 WHEN OTHERS THEN
3625 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Period_Name');
3626 RETURN NULL;
3627 END Get_Slice_Name;
3628 /*
3629 * Aded procedure for bug 3842347
3630 */
3631
3632 /* Modified code for bug 4175400*/
3633 PROCEDURE Get_Default_Period_Dates (
3634 p_plan_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
3635 , p_project_id IN NUMBER
3636 , x_min_julian_date OUT NOCOPY NUMBER
3637 , x_max_julian_date OUT NOCOPY NUMBER
3638 )
3639 IS
3640 l_min_date DATE := NULL;
3641 l_max_date DATE := NULL;
3642 l_cur_min_date DATE := NULL;
3643 l_cur_max_date DATE := NULL;
3644 l_plan_version_id NUMBER := NULL;
3645 l_plan_version_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
3646 j NUMBER := 0;
3647 BEGIN
3648 IF g_debug_mode = 'Y' THEN
3649 Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: begining', TRUE , g_proc);
3650 END IF;
3651
3652 -- Checking if project id is null or p_plan_version_ids's table is null then exiting smoothly
3653 IF ( p_project_id IS NULL OR p_plan_version_ids.COUNT <= 0 ) THEN
3654 IF g_debug_mode = 'Y' THEN
3655 Pji_Utils.WRITE2LOG( 'Exiting from Get_Default_Period_Dates: no Project or Plan version id found', TRUE , g_proc);
3656 END IF;
3657
3658 RETURN;
3659 END IF;
3660
3661 l_plan_version_ids.extend(17);
3662
3663 FOR i IN p_plan_version_ids.FIRST..p_plan_version_ids.LAST LOOP
3664 l_plan_version_id := NULL;
3665
3666 IF ( p_plan_version_ids.EXISTS(i)) THEN
3667 j := j + 1;
3668 l_plan_version_ids(j) := p_plan_version_ids(i);
3669
3670 IF g_debug_mode = 'Y' THEN
3671 Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: inside pa_resource_assignments table', TRUE , g_proc);
3672 END IF;
3673 END IF;
3674 END LOOP;
3675
3676 FOR i IN 1..17 LOOP
3677 IF ( l_plan_version_ids.EXISTS(i)) THEN
3678 NULL;
3679 ELSE
3680 l_plan_version_ids(i) := 0;
3681 END IF;
3682 END LOOP;
3683
3684
3685 IF ( l_plan_version_ids.COUNT > 0 ) THEN
3686 BEGIN
3687 SELECT MAX(max_txn_date), MIN(min_txn_date)
3688 INTO l_max_date, l_min_date
3689 FROM pji_pjp_wbs_header
3690 WHERE project_id = p_project_id
3691 AND plan_version_id IN (l_plan_version_ids(1),l_plan_version_ids(2),l_plan_version_ids(3),l_plan_version_ids(4),
3692 l_plan_version_ids(5),l_plan_version_ids(6),l_plan_version_ids(7),l_plan_version_ids(8),
3693 l_plan_version_ids(9),l_plan_version_ids(10),l_plan_version_ids(11),l_plan_version_ids(12),
3694 l_plan_version_ids(13),l_plan_version_ids(14),l_plan_version_ids(15),l_plan_version_ids(16),l_plan_version_ids(17) );
3695
3696 IF g_debug_mode = 'Y' THEN
3697 Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: Done selecting pa_resource_assignments table', TRUE , g_proc);
3698 END IF;
3699 EXCEPTION
3700 WHEN NO_DATA_FOUND THEN
3701 l_min_date := NULL;
3702 l_max_date := NULL;
3703 IF g_debug_mode = 'Y' THEN
3704 Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: No data found in pa_resource_assignments table', TRUE , g_proc);
3705 END IF;
3706 END;
3707 END IF;
3708
3709 x_min_julian_date := TO_CHAR(l_min_date,'j');
3710 x_max_julian_date := TO_CHAR(l_max_date,'j');
3711
3712 IF g_debug_mode = 'Y' THEN
3713 Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: Leaving ', TRUE , g_proc);
3714 END IF;
3715
3716 EXCEPTION
3717 WHEN OTHERS THEN
3718 x_min_julian_date := NULL;
3719 x_max_julian_date := NULL;
3720 IF g_debug_mode = 'Y' THEN
3721 Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: When others ', TRUE , g_proc);
3722 END IF;
3723 NULL;
3724
3725 END Get_Default_Period_Dates;
3726
3727
3728 PROCEDURE Derive_Project_Type(p_project_id NUMBER
3729 , x_project_type OUT NOCOPY VARCHAR2
3730 , x_return_status IN OUT NOCOPY VARCHAR2
3731 , x_msg_count IN OUT NOCOPY NUMBER
3732 , x_msg_data IN OUT NOCOPY VARCHAR2
3733 )
3734 IS
3735 BEGIN
3736
3737 IF g_debug_mode = 'Y' THEN
3738 Pji_Utils.WRITE2LOG( 'Derive_Project_Type: begining', TRUE , g_proc);
3739 END IF;
3740
3741 IF x_return_status IS NULL THEN
3742 x_msg_count := 0;
3743 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3744 END IF;
3745
3746 SELECT DISTINCT UPPER(t.project_type_class_code)
3747 INTO x_project_type
3748 FROM pa_projects_all p
3749 , pa_project_types_all t
3750 WHERE 1=1
3751 AND p.project_id = p_project_Id
3752 AND p.project_type = t.project_type
3753 /*AND NVL(p.org_id,-99) = NVL(t.org_id,-99); -- Added NVL for bug 3989132*/
3754 AND p.org_id = t.org_id ; -- Removed NVL for Bug 5376591
3755
3756
3757 IF g_debug_mode = 'Y' THEN
3758 Pji_Utils.WRITE2LOG( 'Derive_Project_Type: finishing', TRUE , g_proc);
3759 END IF;
3760
3761 EXCEPTION
3762 WHEN NO_DATA_FOUND THEN
3763 x_project_type := 'CONTRACT';
3764 WHEN OTHERS THEN
3765 x_msg_count := x_msg_count + 1;
3766 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3767 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Project_Type');
3768 RAISE;
3769 END Derive_Project_Type;
3770
3771 PROCEDURE Derive_Percent_Complete
3772 ( p_project_id NUMBER
3773 , p_wbs_version_id NUMBER
3774 , p_wbs_element_id NUMBER
3775 , p_rollup_flag VARCHAR2
3776 , p_report_date_julian NUMBER
3777 , p_structure_type VARCHAR2
3778 , p_calendar_type VARCHAR2 DEFAULT 'E'
3779 , p_calendar_id NUMBER DEFAULT -1
3780 , p_prg_flag VARCHAR2
3781 , x_percent_complete OUT NOCOPY NUMBER
3782 , x_return_status IN OUT NOCOPY VARCHAR2
3783 , x_msg_count IN OUT NOCOPY NUMBER
3784 , x_msg_data IN OUT NOCOPY VARCHAR2
3785 )
3786 IS
3787 l_object_type VARCHAR2(30);
3788 l_report_date DATE := NULL;
3789 BEGIN
3790
3791 IF g_debug_mode = 'Y' THEN
3792 Pji_Utils.WRITE2LOG( 'Derive_Percent_Complete: begining', TRUE , g_proc);
3793 END IF;
3794
3795 IF x_return_status IS NULL THEN
3796 x_msg_count := 0;
3797 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3798 END IF;
3799
3800 BEGIN
3801 SELECT object_type
3802 INTO l_object_type
3803 FROM pa_proj_elements
3804 WHERE proj_element_id = p_wbs_element_id;
3805 EXCEPTION
3806 WHEN NO_DATA_FOUND THEN
3807 l_object_type := 'PA_STRUCTURES';
3808 END;
3809
3810 IF p_calendar_type = 'E' THEN
3811 SELECT end_date
3812 INTO l_report_date
3813 FROM pji_time_ent_period
3814 WHERE TO_DATE(p_report_date_julian,'j') BETWEEN start_date AND end_date;
3815 ELSIF p_calendar_type = 'G' OR p_calendar_type = 'P' THEN
3816 SELECT end_date
3817 INTO l_report_date
3818 FROM pji_time_cal_period
3819 WHERE TO_DATE(p_report_date_julian,'j') BETWEEN start_date AND end_date
3820 AND calendar_id=p_calendar_id;
3821 END IF;
3822
3823 IF p_structure_type = 'FINANCIAL' AND p_prg_flag = 'Y' THEN
3824 x_percent_complete := NULL;
3825 ELSE
3826 x_percent_complete := Pa_Progress_Utils.get_pc_from_sub_tasks_assgn(p_project_id =>p_project_id
3827 ,p_proj_element_id => p_wbs_element_id
3828 ,p_structure_version_id => p_wbs_version_id
3829 ,p_include_sub_tasks_flag => p_rollup_flag
3830 ,p_structure_type => p_structure_type
3831 ,p_object_type => l_object_type
3832 ,p_as_of_date =>l_report_date
3833 ,p_program_flag => p_prg_flag);
3834 END IF;
3835 IF g_debug_mode = 'Y' THEN
3836 Pji_Utils.WRITE2LOG( 'Derive_Complete_Percent: finishing', TRUE , g_proc);
3837 END IF;
3838
3839 EXCEPTION
3840 WHEN NO_DATA_FOUND THEN
3841 x_percent_complete := NULL;
3842 WHEN OTHERS THEN
3843 x_msg_count := x_msg_count + 1;
3844 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3845 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Complete_Percentage');
3846 RAISE;
3847 END Derive_Percent_Complete;
3848
3849
3850 PROCEDURE Check_Cross_Org
3851 ( p_project_id NUMBER
3852 , x_cross_org_flag OUT NOCOPY VARCHAR2
3853 , x_return_status IN OUT NOCOPY VARCHAR2
3854 , x_msg_count IN OUT NOCOPY NUMBER
3855 , x_msg_data IN OUT NOCOPY VARCHAR2
3856 )
3857 IS
3858 l_env_org NUMBER(15);
3859 l_project_org NUMBER(15);
3860 BEGIN
3861
3862 IF g_debug_mode = 'Y' THEN
3863 Pji_Utils.WRITE2LOG( 'Check_Cross_Org: begining', TRUE , g_proc);
3864 END IF;
3865
3866 IF x_return_status IS NULL THEN
3867 x_msg_count := 0;
3868 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3869 END IF;
3870
3871 SELECT
3872 NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)
3873 INTO l_env_org
3874 FROM dual;
3875
3876 SELECT org_id
3877 INTO l_project_org
3878 FROM pa_projects_all
3879 WHERE project_id = p_project_id;
3880
3881 IF l_env_org = l_project_org THEN
3882 x_cross_org_flag := 'F';
3883 ELSE
3884 x_cross_org_flag := 'T';
3885 END IF;
3886
3887 IF g_debug_mode = 'Y' THEN
3888 Pji_Utils.WRITE2LOG( 'Check_Cross_Org: finishing', TRUE , g_proc);
3889 END IF;
3890
3891 EXCEPTION
3892 WHEN NO_DATA_FOUND THEN
3893 x_cross_org_flag := 'F';
3894 WHEN OTHERS THEN
3895 x_msg_count := x_msg_count + 1;
3896 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3897 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Check_Cross_Org');
3898 RAISE;
3899 END Check_Cross_Org;
3900
3901 /*
3902 This is a wrapper API which does the consistency check
3903 for program in workplan context.
3904 */
3905 PROCEDURE CHECK_WP_PARAM_CONSISTENCY
3906 ( p_project_id IN pa_projects_all.project_id%TYPE
3907 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
3908 ,p_margin_code IN pa_proj_fp_options.margin_derived_from_code%TYPE
3909 ,p_published_flag IN VARCHAR2
3910 ,p_calendar_type IN pji_fp_xbs_accum_f.calendar_type%TYPE
3911 ,p_calendar_id IN pa_projects_all.calendar_id%TYPE
3912 ,p_rbs_version_id IN pa_proj_fp_options.rbs_version_id%TYPE
3913 ,x_pc_flag OUT NOCOPY VARCHAR2
3914 ,x_pfc_flag OUT NOCOPY VARCHAR2
3915 ,x_margin_flag OUT NOCOPY VARCHAR2
3916 ,x_workpub_flag OUT NOCOPY VARCHAR2
3917 ,x_time_phase_flag OUT NOCOPY VARCHAR2
3918 ,x_rbs_flag OUT NOCOPY VARCHAR2
3919 ,x_return_status OUT NOCOPY VARCHAR2
3920 ,x_msg_count OUT NOCOPY NUMBER
3921 ,x_msg_data OUT NOCOPY VARCHAR2)
3922 AS
3923
3924 l_msg_count NUMBER := 0;
3925 l_data VARCHAR2(2000);
3926 l_msg_data VARCHAR2(2000);
3927 l_debug_mode VARCHAR2(1);
3928
3929 l_debug_level2 CONSTANT NUMBER := 2;
3930 l_debug_level3 CONSTANT NUMBER := 3;
3931 l_debug_level4 CONSTANT NUMBER := 4;
3932 l_debug_level5 CONSTANT NUMBER := 5;
3933
3934 l_module_name VARCHAR2(100) := 'pa.plsql.CHECK_WP_PARAM_CONSISTENCY';
3935
3936 BEGIN
3937 x_msg_count := 0;
3938 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3939 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
3940
3941 IF l_debug_mode = 'Y' THEN
3942 Pa_Debug.set_curr_function( p_function => 'CHECK_WP_PARAM_CONSISTENCY',
3943 p_debug_mode => l_debug_mode );
3944 END IF;
3945
3946 --Call the currency check API
3947 Pji_Rep_Util.CHECK_WP_CURRENCY_CONSISTENCY
3948 ( p_project_id => p_project_id
3949 ,p_wbs_version_id => p_wbs_version_id
3950 ,x_pc_flag => x_pc_flag
3951 ,x_pfc_flag => x_pfc_flag
3952 ,x_return_status => x_return_status
3953 ,x_msg_count => x_msg_count
3954 ,x_msg_data => x_msg_data );
3955
3956 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
3957 RETURN;
3958 END IF;
3959
3960 --Call the margin check API
3961 Pji_Rep_Util.CHECK_WP_MARGIN_CONSISTENCY
3962 ( p_project_id => p_project_id
3963 ,p_wbs_version_id => p_wbs_version_id
3964 ,p_margin_code => p_margin_code
3965 ,x_margin_flag => x_margin_flag
3966 ,x_return_status => x_return_status
3967 ,x_msg_count => x_msg_count
3968 ,x_msg_data => x_msg_data );
3969
3970 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
3971 RETURN;
3972 END IF;
3973
3974 --Call the status check API
3975 Pji_Rep_Util.CHECK_WP_STATUS_CONSISTENCY
3976 ( p_project_id => p_project_id
3977 ,p_wbs_version_id => p_wbs_version_id
3978 ,p_published_flag => p_published_flag
3979 ,x_workpub_flag => x_workpub_flag
3980 ,x_return_status => x_return_status
3981 ,x_msg_count => x_msg_count
3982 ,x_msg_data => x_msg_data );
3983
3984 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
3985 RETURN;
3986 END IF;
3987
3988 --Call the time phase check API only if the published flag is Y
3989 IF p_published_flag = 'Y' THEN
3990 Pji_Rep_Util.CHECK_WP_TIME_CONSISTENCY
3991 ( p_project_id => p_project_id
3992 ,p_wbs_version_id => p_wbs_version_id
3993 ,p_calendar_type => p_calendar_type
3994 ,p_calendar_id => p_calendar_id
3995 ,x_time_phase_flag => x_time_phase_flag
3996 ,x_return_status => x_return_status
3997 ,x_msg_count => x_msg_count
3998 ,x_msg_data => x_msg_data );
3999
4000 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4001 RETURN;
4002 END IF;
4003 END IF;
4004
4005 --Call the RBS check API
4006 Pji_Rep_Util.CHECK_WP_RBS_CONSISTENCY
4007 ( p_project_id => p_project_id
4008 ,p_wbs_version_id => p_wbs_version_id
4009 ,p_rbs_version_id => p_rbs_version_id
4010 ,x_rbs_flag => x_rbs_flag
4011 ,x_return_status => x_return_status
4012 ,x_msg_count => x_msg_count
4013 ,x_msg_data => x_msg_data );
4014
4015 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4016 RETURN;
4017 END IF;
4018
4019 IF l_debug_mode = 'Y' THEN
4020 Pa_Debug.g_err_stage:= 'Exiting CHECK_WP_PARAM_CONSISTENCY';
4021 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
4022 Pa_Debug.reset_curr_function;
4023 END IF;
4024 EXCEPTION
4025 WHEN OTHERS THEN
4026
4027 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4028 x_msg_count := 1;
4029 x_msg_data := SQLERRM;
4030
4031 Fnd_Msg_Pub.add_exc_msg
4032 ( p_pkg_name => 'PJI_REP_UTIL'
4033 ,p_procedure_name => 'CHECK_WP_PARAM_CONSISTENCY'
4034 ,p_error_text => x_msg_data);
4035
4036 IF l_debug_mode = 'Y' THEN
4037 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4038 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
4039 Pa_Debug.reset_curr_function;
4040 END IF;
4041 RAISE;
4042 END CHECK_WP_PARAM_CONSISTENCY;
4043
4044 /*
4045 check if all the projects in the program hierarchy contain
4046 the same project and project functional currency.
4047 */
4048 PROCEDURE CHECK_WP_CURRENCY_CONSISTENCY
4049 ( p_project_id IN pa_projects_all.project_id%TYPE
4050 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4051 ,x_pc_flag OUT NOCOPY VARCHAR2
4052 ,x_pfc_flag OUT NOCOPY VARCHAR2
4053 ,x_return_status OUT NOCOPY VARCHAR2
4054 ,x_msg_count OUT NOCOPY NUMBER
4055 ,x_msg_data OUT NOCOPY VARCHAR2)
4056 AS
4057
4058 CURSOR get_proj_currency_details(c_project_id pa_projects_all.project_id%TYPE)
4059 IS
4060 SELECT project_currency_code,projfunc_currency_code
4061 FROM pa_projects_all
4062 WHERE project_id = c_project_id;
4063
4064 CURSOR check_project_currency(c_project_id pa_projects_all.project_id%TYPE,
4065 c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4066 c_currency_code pa_projects_all.project_currency_code%TYPE)
4067 IS
4068 SELECT 1
4069 FROM pji_xbs_denorm denorm,
4070 pa_proj_elements ele,
4071 pa_projects_all proj
4072 WHERE denorm.sup_project_id = c_project_id
4073 AND denorm.sup_id = c_wbs_version_id
4074 AND denorm.struct_type = 'PRG'
4075 AND denorm.struct_version_id IS NULL
4076 AND denorm.sub_emt_id = ele.proj_element_id
4077 AND ele.project_id = proj.project_id
4078 AND proj.project_currency_code <> c_currency_code;
4079
4080 CURSOR check_projfunc_currency(c_project_id pa_projects_all.project_id%TYPE,
4081 c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4082 c_currency_code pa_projects_all.project_currency_code%TYPE)
4083 IS
4084 SELECT 1
4085 FROM pji_xbs_denorm denorm,
4086 pa_proj_elements ele,
4087 pa_projects_all proj
4088 WHERE denorm.sup_project_id = c_project_id
4089 AND denorm.sup_id = c_wbs_version_id
4090 AND denorm.struct_type = 'PRG'
4091 AND denorm.struct_version_id IS NULL
4092 AND denorm.sub_emt_id = ele.proj_element_id
4093 AND ele.project_id = proj.project_id
4094 AND proj.projfunc_currency_code <> c_currency_code;
4095
4096 l_msg_count NUMBER := 0;
4097 l_data VARCHAR2(2000);
4098 l_msg_data VARCHAR2(2000);
4099 l_debug_mode VARCHAR2(1);
4100
4101
4102 l_debug_level2 CONSTANT NUMBER := 2;
4103 l_debug_level3 CONSTANT NUMBER := 3;
4104 l_debug_level4 CONSTANT NUMBER := 4;
4105 l_debug_level5 CONSTANT NUMBER := 5;
4106
4107 l_module_name VARCHAR2(100) := 'pa.plsql.CHECK_WP_CURRENCY_CONSISTENCY';
4108
4109 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
4110 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
4111 l_dummy NUMBER;
4112
4113 BEGIN
4114 x_msg_count := 0;
4115 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4116 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
4117
4118 IF l_debug_mode = 'Y' THEN
4119 Pa_Debug.set_curr_function( p_function => 'CHECK_WP_CURRENCY_CONSISTENCY',
4120 p_debug_mode => l_debug_mode );
4121 END IF;
4122
4123 --These flags will denote the respective consistency. A value of Y for x_pc_flag
4124 --indicates that all the projects in the program have the same PC/PFC. A value of N indicates
4125 --inconsistency.
4126 x_pc_flag := 'Y';
4127 x_pfc_flag := 'Y';
4128
4129 --Obtain the project and the projfunc currency codes
4130 OPEN get_proj_currency_details(p_project_id);
4131 FETCH get_proj_currency_details INTO l_project_currency_code,l_projfunc_currency_code;
4132 CLOSE get_proj_currency_details;
4133
4134 OPEN check_project_currency(p_project_id,p_wbs_version_id,l_project_currency_code);
4135 FETCH check_project_currency INTO l_dummy;
4136 IF check_project_currency%FOUND THEN
4137 x_pc_flag := 'N';
4138 END IF;
4139 CLOSE check_project_currency;
4140
4141 OPEN check_projfunc_currency(p_project_id,p_wbs_version_id,l_projfunc_currency_code);
4142 FETCH check_projfunc_currency INTO l_dummy;
4143 IF check_projfunc_currency%FOUND THEN
4144 x_pfc_flag := 'N';
4145 END IF;
4146 CLOSE check_projfunc_currency;
4147
4148 IF l_debug_mode = 'Y' THEN
4149 Pa_Debug.g_err_stage:= 'Exiting CHECK_WP_CURRENCY_CONSISTENCY';
4150 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
4151 Pa_Debug.reset_curr_function;
4152 END IF;
4153 EXCEPTION
4154
4155 WHEN OTHERS THEN
4156
4157 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4158 x_msg_count := 1;
4159 x_msg_data := SQLERRM;
4160
4161 Fnd_Msg_Pub.add_exc_msg
4162 ( p_pkg_name => 'PJI_REP_UTIL'
4163 ,p_procedure_name => 'CHECK_WP_CURRENCY_CONSISTENCY'
4164 ,p_error_text => x_msg_data);
4165
4166 IF l_debug_mode = 'Y' THEN
4167 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4168 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
4169 Pa_Debug.reset_curr_function;
4170 END IF;
4171 RAISE;
4172 END CHECK_WP_CURRENCY_CONSISTENCY;
4173
4174 /*
4175 check if all the linked structure versions in the
4176 program hierarchy have the same margin mask.
4177 */
4178 PROCEDURE CHECK_WP_MARGIN_CONSISTENCY
4179 ( p_project_id IN pa_projects_all.project_id%TYPE
4180 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4181 ,p_margin_code IN pa_proj_fp_options.margin_derived_from_code%TYPE
4182 ,x_margin_flag OUT NOCOPY VARCHAR2
4183 ,x_return_status OUT NOCOPY VARCHAR2
4184 ,x_msg_count OUT NOCOPY NUMBER
4185 ,x_msg_data OUT NOCOPY VARCHAR2)
4186 AS
4187
4188 CURSOR check_margin_mask(c_project_id pa_projects_all.project_id%TYPE,
4189 c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4190 c_margin_code pa_proj_fp_options.margin_derived_from_code%TYPE)
4191 IS
4192 SELECT 1
4193 FROM pji_xbs_denorm denorm,
4194 pji_pjp_wbs_header header,
4195 pa_proj_fp_options opt
4196 WHERE denorm.sup_project_id = c_project_id
4197 AND denorm.sup_id = c_wbs_version_id
4198 AND denorm.struct_type = 'PRG'
4199 AND denorm.struct_version_id IS NULL
4200 AND denorm.sub_id = header.wbs_version_id
4201 AND header.wp_flag = 'Y'
4202 AND header.plan_version_id = opt.fin_plan_version_id
4203 AND opt.fin_plan_option_level_code = 'PLAN_VERSION'
4204 AND opt.margin_derived_from_code <> c_margin_code;
4205
4206 l_msg_count NUMBER := 0;
4207 l_data VARCHAR2(2000);
4208 l_msg_data VARCHAR2(2000);
4209 l_debug_mode VARCHAR2(1);
4210
4211
4212 l_debug_level2 CONSTANT NUMBER := 2;
4213 l_debug_level3 CONSTANT NUMBER := 3;
4214 l_debug_level4 CONSTANT NUMBER := 4;
4215 l_debug_level5 CONSTANT NUMBER := 5;
4216
4217 l_module_name VARCHAR2(100) := 'pa.plsql.CHECK_WP_MARGIN_CONSISTENCY';
4218
4219 l_dummy NUMBER;
4220
4221 BEGIN
4222 x_msg_count := 0;
4223 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4224 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
4225
4226 IF l_debug_mode = 'Y' THEN
4227 Pa_Debug.set_curr_function( p_function => 'CHECK_WP_MARGIN_CONSISTENCY',
4228 p_debug_mode => l_debug_mode );
4229 END IF;
4230
4231 --These flags will denote the respective consistency. A value of Y for x_currency_flag
4232 --indicates that all the projects in the program have the same PC/PFC. A value of N indicates
4233 --inconsistency.
4234 x_margin_flag := 'Y';
4235
4236 OPEN check_margin_mask(p_project_id,p_wbs_version_id,p_margin_code);
4237 FETCH check_margin_mask INTO l_dummy;
4238 IF check_margin_mask%FOUND THEN
4239 x_margin_flag := 'N';
4240 END IF;
4241 CLOSE check_margin_mask;
4242
4243 IF l_debug_mode = 'Y' THEN
4244 Pa_Debug.g_err_stage:= 'Exiting CHECK_WP_MARGIN_CONSISTENCY';
4245 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
4246 Pa_Debug.reset_curr_function;
4247 END IF;
4248 EXCEPTION
4249
4250 WHEN OTHERS THEN
4251
4252 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4253 x_msg_count := 1;
4254 x_msg_data := SQLERRM;
4255
4256 Fnd_Msg_Pub.add_exc_msg
4257 ( p_pkg_name => 'PJI_REP_UTIL'
4258 ,p_procedure_name => 'CHECK_WP_MARGIN_CONSISTENCY'
4259 ,p_error_text => x_msg_data);
4260
4261 IF l_debug_mode = 'Y' THEN
4262 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4263 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
4264 Pa_Debug.reset_curr_function;
4265 END IF;
4266 RAISE;
4267 END CHECK_WP_MARGIN_CONSISTENCY;
4268
4269
4270 /*
4271 check if all the structure versions in the program hierarchy
4272 have the same status. ie published/not published.
4273 */
4274
4275 PROCEDURE CHECK_WP_STATUS_CONSISTENCY
4276 ( p_project_id IN pa_projects_all.project_id%TYPE
4277 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4278 ,p_published_flag IN VARCHAR2
4279 ,x_workpub_flag OUT NOCOPY VARCHAR2
4280 ,x_return_status OUT NOCOPY VARCHAR2
4281 ,x_msg_count OUT NOCOPY NUMBER
4282 ,x_msg_data OUT NOCOPY VARCHAR2)
4283 AS
4284
4285 CURSOR check_published_flag(c_project_id pa_projects_all.project_id%TYPE,
4286 c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4287 c_published_flag VARCHAR2)
4288 IS
4289 SELECT 1
4290 FROM pji_xbs_denorm denorm,
4291 pa_proj_elements ele
4292 WHERE denorm.sup_project_id = c_project_id
4293 AND denorm.sup_id = c_wbs_version_id
4294 AND denorm.struct_type = 'PRG'
4295 AND denorm.struct_version_id IS NULL
4296 AND denorm.sub_emt_id = ele.proj_element_id
4297 AND Pa_Project_Structure_Utils.Check_Struc_Ver_Published(ele.project_id,denorm.sub_id) <> c_published_flag;
4298
4299 l_msg_count NUMBER := 0;
4300 l_data VARCHAR2(2000);
4301 l_msg_data VARCHAR2(2000);
4302 l_debug_mode VARCHAR2(1);
4303
4304
4305 l_debug_level2 CONSTANT NUMBER := 2;
4306 l_debug_level3 CONSTANT NUMBER := 3;
4307 l_debug_level4 CONSTANT NUMBER := 4;
4308 l_debug_level5 CONSTANT NUMBER := 5;
4309
4310 l_module_name VARCHAR2(100) := 'pa.plsql.CHECK_WP_STATUS_CONSISTENCY';
4311
4312 l_dummy NUMBER;
4313
4314 BEGIN
4315 x_msg_count := 0;
4316 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4317 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
4318
4319 IF l_debug_mode = 'Y' THEN
4320 Pa_Debug.set_curr_function( p_function => 'CHECK_WP_STATUS_CONSISTENCY',
4321 p_debug_mode => l_debug_mode );
4322 END IF;
4323
4324 --These flags will denote the respective consistency. A value of Y for x_currency_flag
4325 --indicates that all the projects in the program have the same PC/PFC. A value of N indicates
4326 --inconsistency.
4327 x_workpub_flag := 'Y';
4328
4329 --The flag x_workpub_flag just denotes an inconsistency in the pgm structure.
4330 OPEN check_published_flag(p_project_id,p_wbs_version_id,p_published_flag);
4331 FETCH check_published_flag INTO l_dummy;
4332 IF check_published_flag%FOUND THEN
4333 x_workpub_flag := 'N';
4334 END IF;
4335 CLOSE check_published_flag;
4336
4337 IF l_debug_mode = 'Y' THEN
4338 Pa_Debug.g_err_stage:= 'Exiting CHECK_WP_PARAM_CONSISTENCY';
4339 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
4340 Pa_Debug.reset_curr_function;
4341 END IF;
4342 EXCEPTION
4343
4344 WHEN OTHERS THEN
4345
4346 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4347 x_msg_count := 1;
4348 x_msg_data := SQLERRM;
4349
4350 Fnd_Msg_Pub.add_exc_msg
4351 ( p_pkg_name => 'PJI_REP_UTIL'
4352 ,p_procedure_name => 'CHECK_WP_STATUS_CONSISTENCY'
4353 ,p_error_text => x_msg_data);
4354
4355 IF l_debug_mode = 'Y' THEN
4356 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4357 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
4358 Pa_Debug.reset_curr_function;
4359 END IF;
4360 RAISE;
4361 END CHECK_WP_STATUS_CONSISTENCY;
4362
4363
4364 /*
4365 check if all the structure versions in the program hierarchy have
4366 same time phasing.
4367 */
4368 PROCEDURE CHECK_WP_TIME_CONSISTENCY
4369 ( p_project_id IN pa_projects_all.project_id%TYPE
4370 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4371 ,p_calendar_type IN pji_fp_xbs_accum_f.calendar_type%TYPE
4372 ,p_calendar_id IN pa_projects_all.calendar_id%TYPE
4373 ,x_time_phase_flag OUT NOCOPY VARCHAR2
4374 ,x_return_status OUT NOCOPY VARCHAR2
4375 ,x_msg_count OUT NOCOPY NUMBER
4376 ,x_msg_data OUT NOCOPY VARCHAR2)
4377 AS
4378
4379
4380 CURSOR get_pgm_ver_details(c_project_id pa_projects_all.project_id%TYPE,
4381 c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE)
4382 IS
4383 SELECT header.project_id, header.plan_version_id
4384 FROM pji_xbs_denorm denorm,
4385 pji_pjp_wbs_header header
4386 WHERE denorm.sup_project_id = c_project_id
4387 AND denorm.sup_id = c_wbs_version_id
4388 AND denorm.struct_type = 'PRG'
4389 AND denorm.struct_version_id IS NULL
4390 AND header.wbs_version_id = denorm.sub_id
4391 AND header.wp_flag = 'Y';
4392
4393
4394 l_msg_count NUMBER := 0;
4395 l_data VARCHAR2(2000);
4396 l_msg_data VARCHAR2(2000);
4397 l_debug_mode VARCHAR2(1);
4398
4399
4400 l_debug_level2 CONSTANT NUMBER := 2;
4401 l_debug_level3 CONSTANT NUMBER := 3;
4402 l_debug_level4 CONSTANT NUMBER := 4;
4403 l_debug_level5 CONSTANT NUMBER := 5;
4404
4405 l_module_name VARCHAR2(100) := 'pa.plsql.CHECK_WP_TIME_CONSISTENCY';
4406
4407 l_calendar_id pa_projects_all.calendar_id%TYPE;
4408 l_calendar_type pji_fp_xbs_accum_f.calendar_type%TYPE;
4409
4410 BEGIN
4411 x_msg_count := 0;
4412 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4413 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
4414
4415 IF l_debug_mode = 'Y' THEN
4416 Pa_Debug.set_curr_function( p_function => 'CHECK_WP_TIME_CONSISTENCY',
4417 p_debug_mode => l_debug_mode );
4418 END IF;
4419
4420 --These flags will denote the respective consistency. A value of Y for x_currency_flag
4421 --indicates that all the projects in the program have the same PC/PFC. A value of N indicates
4422 --inconsistency.
4423 x_time_phase_flag := 'Y';
4424
4425 FOR rec IN get_pgm_ver_details(p_project_id,p_wbs_version_id) LOOP
4426 Pji_Rep_Util.Derive_WP_Calendar_Info(
4427 p_project_id => rec.project_id
4428 ,p_plan_version_id => rec.plan_version_id
4429 ,x_calendar_id => l_calendar_id
4430 ,x_calendar_type => l_calendar_type
4431 ,x_return_status => x_return_status
4432 ,x_msg_count => x_msg_count
4433 ,x_msg_data => x_msg_data);
4434
4435 IF x_return_status = Fnd_Api.G_RET_STS_SUCCESS THEN
4436 IF NVL(l_calendar_id,-99) <> NVL(p_calendar_id,-99) AND l_calendar_type <> p_calendar_type THEN
4437 x_time_phase_flag := 'N';
4438 EXIT;
4439 END IF;
4440 ELSE
4441 EXIT; -- you have returned with error.
4442 END IF;
4443 END LOOP;
4444
4445 IF l_debug_mode = 'Y' THEN
4446 Pa_Debug.g_err_stage:= 'Exiting CHECK_WP_TIME_CONSISTENCY';
4447 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
4448 Pa_Debug.reset_curr_function;
4449 END IF;
4450 EXCEPTION
4451
4452 WHEN OTHERS THEN
4453
4454 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4455 x_msg_count := 1;
4456 x_msg_data := SQLERRM;
4457
4458 Fnd_Msg_Pub.add_exc_msg
4459 ( p_pkg_name => 'PJI_REP_UTIL'
4460 ,p_procedure_name => 'CHECK_WP_PARAM_CONSISTENCY'
4461 ,p_error_text => x_msg_data);
4462
4463 IF l_debug_mode = 'Y' THEN
4464 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4465 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
4466 Pa_Debug.reset_curr_function;
4467 END IF;
4468 RAISE;
4469 END CHECK_WP_TIME_CONSISTENCY;
4470
4471 /*
4472 check if all the structure versions in the program hierarchy have
4473 the same RBS.
4474 */
4475 PROCEDURE CHECK_WP_RBS_CONSISTENCY
4476 ( p_project_id IN pa_projects_all.project_id%TYPE
4477 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4478 ,p_rbs_version_id IN pa_proj_fp_options.rbs_version_id%TYPE
4479 ,x_rbs_flag OUT NOCOPY VARCHAR2
4480 ,x_return_status OUT NOCOPY VARCHAR2
4481 ,x_msg_count OUT NOCOPY NUMBER
4482 ,x_msg_data OUT NOCOPY VARCHAR2)
4483 AS
4484
4485 CURSOR check_rbs_flag(c_project_id pa_projects_all.project_id%TYPE,
4486 c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4487 c_rbs_version_id pa_proj_fp_options.rbs_version_id%TYPE)
4488 IS
4489 SELECT 1
4490 FROM pji_xbs_denorm denorm,
4491 pji_pjp_wbs_header header,
4492 pa_proj_fp_options opt
4493 WHERE denorm.sup_project_id = c_project_id
4494 AND denorm.sup_id = c_wbs_version_id
4495 AND denorm.struct_type = 'PRG'
4496 AND denorm.struct_version_id IS NULL
4497 AND denorm.sub_id = header.wbs_version_id
4498 AND header.wp_flag = 'Y'
4499 AND header.plan_version_id = opt.fin_plan_version_id
4500 AND opt.fin_plan_option_level_code = 'PLAN_VERSION'
4501 AND NVL(opt.rbs_version_id, -50) <> c_rbs_version_id; --Bug 4506849
4502
4503
4504 l_msg_count NUMBER := 0;
4505 l_data VARCHAR2(2000);
4506 l_msg_data VARCHAR2(2000);
4507 l_debug_mode VARCHAR2(1);
4508
4509
4510 l_debug_level2 CONSTANT NUMBER := 2;
4511 l_debug_level3 CONSTANT NUMBER := 3;
4512 l_debug_level4 CONSTANT NUMBER := 4;
4513 l_debug_level5 CONSTANT NUMBER := 5;
4514
4515 l_module_name VARCHAR2(100) := 'pa.plsql.CHECK_WP_RBS_CONSISTENCY';
4516
4517 l_dummy NUMBER;
4518
4519 BEGIN
4520 x_msg_count := 0;
4521 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4522 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
4523
4524 IF l_debug_mode = 'Y' THEN
4525 Pa_Debug.set_curr_function( p_function => 'CHECK_WP_RBS_CONSISTENCY',
4526 p_debug_mode => l_debug_mode );
4527 END IF;
4528
4529 --These flags will denote the respective consistency. A value of Y for x_currency_flag
4530 --indicates that all the projects in the program have the same PC/PFC. A value of N indicates
4531 --inconsistency.
4532 x_rbs_flag := 'Y';
4533
4534 OPEN check_rbs_flag(p_project_id,p_wbs_version_id,p_rbs_version_id);
4535 FETCH check_rbs_flag INTO l_dummy;
4536 IF check_rbs_flag%FOUND THEN
4537 x_rbs_flag := 'N';
4538 END IF;
4539 CLOSE check_rbs_flag;
4540
4541 IF l_debug_mode = 'Y' THEN
4542 Pa_Debug.g_err_stage:= 'Exiting CHECK_WP_RBS_CONSISTENCY';
4543 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
4544 Pa_Debug.reset_curr_function;
4545 END IF;
4546 EXCEPTION
4547
4548 WHEN OTHERS THEN
4549
4550 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4551 x_msg_count := 1;
4552 x_msg_data := SQLERRM;
4553
4554 Fnd_Msg_Pub.add_exc_msg
4555 ( p_pkg_name => 'PJI_REP_UTIL'
4556 ,p_procedure_name => 'CHECK_WP_RBS_CONSISTENCY'
4557 ,p_error_text => x_msg_data);
4558
4559 IF l_debug_mode = 'Y' THEN
4560 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
4561 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
4562 Pa_Debug.reset_curr_function;
4563 END IF;
4564 RAISE;
4565 END CHECK_WP_RBS_CONSISTENCY;
4566
4567 FUNCTION GET_WP_BASELINED_PLAN_VERSION
4568 ( p_project_id IN pa_projects_all.project_id%TYPE)
4569 RETURN NUMBER
4570 IS
4571 CURSOR get_baselined_version(c_project_id pa_projects_all.project_id%TYPE)
4572 IS
4573 SELECT header.plan_version_id
4574 FROM pa_proj_elem_ver_structure str,
4575 pji_pjp_wbs_header header
4576 WHERE str.project_id = c_project_id
4577 AND str.current_flag = 'Y'
4578 AND header.project_id = str.project_id
4579 AND header.wbs_version_id = str.element_version_id
4580 AND header.wp_flag = 'Y';
4581
4582 l_baseline_version_id pji_pjp_wbs_header.plan_version_id%TYPE;
4583
4584 BEGIN
4585 OPEN get_baselined_version(p_project_id);
4586 FETCH get_baselined_version INTO l_baseline_version_id;
4587 CLOSE get_baselined_version;
4588 RETURN l_baseline_version_id;
4589 EXCEPTION
4590 WHEN OTHERS THEN
4591 RETURN NULL;
4592 END;
4593
4594 FUNCTION GET_WP_LATEST_VERSION
4595 ( p_project_id IN pa_projects_all.project_id%TYPE)
4596 RETURN NUMBER
4597 IS
4598 CURSOR get_latest_published_version(c_project_id pa_projects_all.project_id%TYPE)
4599 IS
4600 SELECT header.plan_version_id
4601 FROM pa_proj_elem_ver_structure str,
4602 pji_pjp_wbs_header header
4603 WHERE str.project_id = c_project_id
4604 AND str.latest_eff_published_flag = 'Y'
4605 AND header.project_id = str.project_id
4606 AND header.wbs_version_id = str.element_version_id
4607 AND header.wp_flag = 'Y';
4608
4609 l_latest_published_version_id pji_pjp_wbs_header.plan_version_id%TYPE;
4610
4611 BEGIN
4612 OPEN get_latest_published_version(p_project_id);
4613 FETCH get_latest_published_version INTO l_latest_published_version_id;
4614 CLOSE get_latest_published_version;
4615 RETURN l_latest_published_version_id;
4616 EXCEPTION
4617 WHEN OTHERS THEN
4618 RETURN NULL;
4619 END;
4620
4621 FUNCTION GET_DEFAULT_EXPANSION_LEVEL
4622 ( p_project_id IN pa_projects_all.project_id%TYPE
4623 ,p_object_type IN VARCHAR2)
4624 RETURN NUMBER
4625 IS
4626 CURSOR get_wp_default_disp_lvl(c_project_id pa_projects_all.project_id%TYPE)
4627 IS
4628 SELECT wp_default_display_lvl
4629 FROM pa_workplan_options_v
4630 WHERE PROJECT_ID = c_project_id;
4631
4632 CURSOR get_fp_default_disp_lvl(c_project_id pa_projects_all.project_id%TYPE)
4633 IS
4634 SELECT default_display_lvl
4635 FROM pa_financial_options_v
4636 WHERE PROJECT_ID = c_project_id;
4637
4638 l_default_exp_level NUMBER := 0;
4639 BEGIN
4640 IF p_object_type = 'T' THEN
4641 OPEN get_wp_default_disp_lvl(p_project_id);
4642 FETCH get_wp_default_disp_lvl INTO l_default_exp_level;
4643 CLOSE get_wp_default_disp_lvl;
4644
4645 l_default_exp_level := NVL(l_default_exp_level,0);
4646 --Bug 5469672 Add logic to derive default FBS expansion level
4647 ELSIF p_object_type = 'FT' THEN
4648 OPEN get_fp_default_disp_lvl(p_project_id);
4649 FETCH get_fp_default_disp_lvl INTO l_default_exp_level;
4650 CLOSE get_fp_default_disp_lvl;
4651
4652 l_default_exp_level := NVL(l_default_exp_level,0);
4653 ELSIF p_object_type = 'R' THEN
4654 l_default_exp_level := 2; --Once we have a proper setup for RBS, this hardcoding should be replaced.
4655 ELSE
4656 l_default_exp_level := 0;
4657 END IF;
4658
4659 RETURN l_default_exp_level;
4660 END GET_DEFAULT_EXPANSION_LEVEL;
4661
4662 PROCEDURE Derive_Default_Plan_Type_Ids(
4663 p_project_id IN NUMBER
4664 , x_cost_fcst_plan_type_id OUT NOCOPY NUMBER
4665 , x_cost_bgt_plan_type_id OUT NOCOPY NUMBER
4666 , x_cost_bgt2_plan_type_id OUT NOCOPY NUMBER
4667 , x_rev_fcst_plan_type_id OUT NOCOPY NUMBER
4668 , x_rev_bgt_plan_type_id OUT NOCOPY NUMBER
4669 , x_rev_bgt2_plan_type_id OUT NOCOPY NUMBER
4670 , x_return_status IN OUT NOCOPY VARCHAR2
4671 , x_msg_count IN OUT NOCOPY NUMBER
4672 , x_msg_data IN OUT NOCOPY VARCHAR2)
4673
4674 IS
4675 l_cost_bgt_plan_type_id NUMBER := NULL;
4676 l_rev_bgt_plan_type_id NUMBER := NULL;
4677
4678 BEGIN
4679 IF g_debug_mode = 'Y' THEN
4680 Pji_Utils.WRITE2LOG( 'Get_Plan_Types_Id: beginning', TRUE , g_proc);
4681 END IF;
4682
4683
4684 IF x_return_status IS NULL THEN
4685 x_msg_count := 0;
4686 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4687 END IF;
4688
4689 IF p_project_id IS NOT NULL THEN
4690
4691 /*
4692 ** Get approved cost and revenue plan types for the
4693 ** project.
4694 */
4695 Pa_Fin_Plan_Utils.Get_Appr_Cost_Plan_Type_Info(
4696 p_project_id=>p_project_id
4697 ,x_plan_type_id =>l_cost_bgt_plan_type_id
4698 ,x_return_status=>x_return_status
4699 ,x_msg_count=>x_msg_count
4700 ,x_msg_data=>x_msg_data);
4701 Pa_Fin_Plan_Utils.Get_Appr_Rev_Plan_Type_Info(
4702 p_project_id=>p_project_id
4703 ,x_plan_type_id=>l_rev_bgt_plan_type_id
4704 ,x_return_status=>x_return_status
4705 ,x_msg_count=>x_msg_count
4706 ,x_msg_data=>x_msg_data);
4707
4708 /* Assigning cost budget type id in cost budget 2 type id
4709 and revenue budget type id in revenue budget 2 type id */
4710
4711 x_cost_bgt_plan_type_id := l_cost_bgt_plan_type_id ;
4712 x_rev_bgt_plan_type_id := l_rev_bgt_plan_type_id;
4713 x_cost_bgt2_plan_type_id := NULL;--l_cost_bgt_plan_type_id ;
4714 x_rev_bgt2_plan_type_id := NULL; --l_rev_bgt_plan_type_id;
4715
4716 /*
4717 ** Get primary cost and revenue plan types for the
4718 ** project.
4719 */
4720 Pa_Fin_Plan_Utils.Is_Pri_Fcst_Cost_PT_Attached(
4721 p_project_id=>p_project_id
4722 ,x_plan_type_id=>x_cost_fcst_plan_type_id
4723 ,x_return_status=>x_return_status
4724 ,x_msg_count=>x_msg_count
4725 ,x_msg_data=>x_msg_data);
4726 Pa_Fin_Plan_Utils.Is_Pri_Fcst_Rev_PT_Attached(
4727 p_project_id=>p_project_id
4728 ,x_plan_type_id=>x_rev_fcst_plan_type_id
4729 ,x_return_status=>x_return_status
4730 ,x_msg_count=>x_msg_count
4731 ,x_msg_data=>x_msg_data);
4732 ELSE
4733 x_cost_bgt_plan_type_id := NULL;
4734 x_rev_bgt_plan_type_id := NULL;
4735 x_cost_bgt2_plan_type_id := NULL;
4736 x_rev_bgt2_plan_type_id := NULL;
4737 x_cost_fcst_plan_type_id := NULL;
4738 x_rev_fcst_plan_type_id := NULL;
4739 END IF;
4740
4741 EXCEPTION
4742 WHEN OTHERS THEN
4743 NULL;
4744 END Derive_Default_Plan_Type_Ids;
4745
4746 /*
4747 ** Get all plan versions for a given project and plan type id
4748 */
4749 PROCEDURE Derive_Plan_Version_Ids(
4750 p_project_id IN NUMBER
4751 , p_cost_fcst_plan_type_id IN NUMBER
4752 , p_cost_bgt_plan_type_id IN NUMBER
4753 , p_cost_bgt2_plan_type_id IN NUMBER
4754 , p_rev_fcst_plan_type_id IN NUMBER
4755 , p_rev_bgt_plan_type_id IN NUMBER
4756 , p_rev_bgt2_plan_type_id IN NUMBER
4757 , x_cstforecast_version_id OUT NOCOPY NUMBER
4758 , x_cstbudget_version_id OUT NOCOPY NUMBER
4759 , x_cstbudget2_version_id OUT NOCOPY NUMBER
4760 , x_revforecast_version_id OUT NOCOPY NUMBER
4761 , x_revbudget_version_id OUT NOCOPY NUMBER
4762 , x_revbudget2_version_id OUT NOCOPY NUMBER
4763 , x_orig_cstbudget_version_id OUT NOCOPY NUMBER
4764 , x_orig_cstbudget2_version_id OUT NOCOPY NUMBER
4765 , x_orig_revbudget_version_id OUT NOCOPY NUMBER
4766 , x_orig_revbudget2_version_id OUT NOCOPY NUMBER
4767 , x_prior_cstfcst_version_id OUT NOCOPY NUMBER
4768 , x_prior_revfcst_version_id OUT NOCOPY NUMBER
4769 , x_return_status IN OUT NOCOPY VARCHAR2
4770 , x_msg_count IN OUT NOCOPY NUMBER
4771 , x_msg_data IN OUT NOCOPY VARCHAR2)
4772 IS
4773 l_cst_budget_version_type pa_budget_versions.version_type%TYPE;
4774 l_rev_budget_version_type pa_budget_versions.version_type%TYPE;
4775 l_cst_budget2_version_type pa_budget_versions.version_type%TYPE;
4776 l_rev_budget2_version_type pa_budget_versions.version_type%TYPE;
4777 l_cst_forecast_version_type pa_budget_versions.version_type%TYPE;
4778 l_rev_forecast_version_type pa_budget_versions.version_type%TYPE;
4779 l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4780 l_temp_holder1 NUMBER;
4781 l_temp_holder2 NUMBER;
4782 l_temp_cstforecast_version_id NUMBER;
4783 l_tmp_orig_cstforecast_ver_id NUMBER;
4784 l_temp_revforecast_version_id NUMBER;
4785 l_tmp_orig_revforecast_ver_id NUMBER;
4786 BEGIN
4787
4788 IF g_debug_mode = 'Y' THEN
4789 Pji_Utils.WRITE2LOG( 'derive_default_plan_versions: beginning', TRUE , g_proc);
4790 END IF;
4791
4792
4793 IF x_return_status IS NULL THEN
4794 x_msg_count := 0;
4795 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4796 END IF;
4797
4798 IF p_project_id IS NOT NULL THEN
4799 /*
4800 ** Following are the defaulting rules:
4801 ** cost budget plan version: current baselined version of approved cost plan type.
4802 ** revenue budget plan version: current baselined version of approved revenue plan type.
4803 ** cost forecast plan version: current baselined version of primary cost plan type.
4804 ** revenue forecast plan version: current baselined version of primary revenue plan type.
4805 ** cost budget2 plan version: current baselined version of approved cost plan type 2.
4806 ** revenue budget2 plan version: current baselined version of approved revenue plan type 2.
4807 ** misc: return original baselined for all above plan versions.
4808 */
4809 BEGIN
4810
4811 /*
4812 ** Get current and original baselined plan versions
4813 ** for approved/primary cost and revenue plan types.
4814 */
4815 IF p_cost_bgt_plan_type_id IS NOT NULL THEN
4816 BEGIN
4817 Pa_Fin_Plan_Utils.Get_Cost_Base_Version_Info(
4818 p_project_id =>p_project_id
4819 ,p_fin_plan_Type_id =>p_cost_bgt_plan_type_id
4820 ,p_budget_type_code =>NULL
4821 ,x_budget_version_id =>x_cstbudget_version_id
4822 ,x_return_status =>x_return_status
4823 ,x_msg_count =>x_msg_count
4824 ,x_msg_data =>x_msg_data);
4825 EXCEPTION
4826 WHEN NO_DATA_FOUND THEN
4827 x_cstbudget_version_id := NULL;
4828 END;
4829
4830 BEGIN
4831 l_cst_budget_version_type:=Get_Version_Type(p_project_id
4832 ,p_cost_bgt_plan_type_id
4833 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
4834
4835 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
4836 p_project_id =>p_project_id
4837 ,p_fin_plan_Type_id =>p_cost_bgt_plan_type_id
4838 ,p_version_type =>l_cst_budget_version_type
4839 ,x_fp_options_id =>l_fp_options_id
4840 ,x_fin_plan_version_id =>x_orig_cstbudget_version_id
4841 ,x_return_status =>x_return_status
4842 ,x_msg_count =>x_msg_count
4843 ,x_msg_data =>x_msg_data);
4844 EXCEPTION
4845 WHEN NO_DATA_FOUND THEN
4846 x_orig_cstbudget_version_id := NULL;
4847 END;
4848
4849 ELSE
4850 x_cstbudget_version_id := NULL;
4851 x_orig_cstbudget_version_id := NULL;
4852 END IF;
4853
4854 IF p_rev_bgt_plan_type_id IS NOT NULL THEN
4855
4856 BEGIN
4857 Pa_Fin_Plan_Utils.Get_Rev_Base_Version_Info(
4858 p_project_id =>p_project_id
4859 ,p_fin_plan_Type_id =>p_rev_bgt_plan_type_id
4860 ,p_budget_type_code =>NULL
4861 ,x_budget_version_id =>x_revbudget_version_id
4862 ,x_return_status =>x_return_status
4863 ,x_msg_count =>x_msg_count
4864 ,x_msg_data =>x_msg_data);
4865 EXCEPTION
4866 WHEN NO_DATA_FOUND THEN
4867 x_revbudget_version_id := NULL;
4868 END;
4869
4870 BEGIN
4871 l_rev_budget_version_type:=Get_Version_Type(p_project_id
4872 ,p_rev_bgt_plan_type_id
4873 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
4874
4875 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
4876 p_project_id =>p_project_id
4877 ,p_fin_plan_Type_id =>p_rev_bgt_plan_type_id
4878 ,p_version_type =>l_rev_budget_version_type
4879 ,x_fp_options_id =>l_fp_options_id
4880 ,x_fin_plan_version_id =>x_orig_revbudget_version_id
4881 ,x_return_status =>x_return_status
4882 ,x_msg_count =>x_msg_count
4883 ,x_msg_data =>x_msg_data);
4884 EXCEPTION
4885 WHEN NO_DATA_FOUND THEN
4886 x_orig_revbudget_version_id := NULL;
4887 END;
4888
4889 ELSE
4890 x_revbudget_version_id := NULL;
4891 x_orig_revbudget_version_id := NULL;
4892 END IF;
4893
4894 /*
4895 ** Get current and original baselined plan versions
4896 ** for approved/primary cost and revenue plan types 2.
4897 */
4898 IF p_cost_bgt2_plan_type_id IS NOT NULL THEN
4899
4900 BEGIN
4901 Pa_Fin_Plan_Utils.Get_Cost_Base_Version_Info(
4902 p_project_id =>p_project_id
4903 ,p_fin_plan_Type_id =>p_cost_bgt2_plan_type_id
4904 ,p_budget_type_code =>NULL
4905 ,x_budget_version_id =>x_cstbudget2_version_id
4906 ,x_return_status =>x_return_status
4907 ,x_msg_count =>x_msg_count
4908 ,x_msg_data =>x_msg_data);
4909 EXCEPTION
4910 WHEN NO_DATA_FOUND THEN
4911 x_cstbudget2_version_id := NULL;
4912 END;
4913
4914 BEGIN
4915 l_cst_budget2_version_type:=Get_Version_Type(p_project_id
4916 ,p_cost_bgt2_plan_type_id
4917 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
4918
4919 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
4920 p_project_id =>p_project_id
4921 ,p_fin_plan_Type_id =>p_cost_bgt2_plan_type_id
4922 ,p_version_type =>l_cst_budget2_version_type
4923 ,x_fp_options_id =>l_fp_options_id
4924 ,x_fin_plan_version_id =>x_orig_cstbudget2_version_id
4925 ,x_return_status =>x_return_status
4926 ,x_msg_count =>x_msg_count
4927 ,x_msg_data =>x_msg_data);
4928 EXCEPTION
4929 WHEN NO_DATA_FOUND THEN
4930 x_orig_cstbudget2_version_id := NULL;
4931 END;
4932
4933 ELSE
4934 x_cstbudget2_version_id := NULL;
4935 x_orig_cstbudget2_version_id := NULL;
4936 END IF;
4937
4938 IF p_rev_bgt2_plan_type_id IS NOT NULL THEN
4939
4940 BEGIN
4941 Pa_Fin_Plan_Utils.Get_Rev_Base_Version_Info(
4942 p_project_id =>p_project_id
4943 ,p_fin_plan_Type_id =>p_rev_bgt2_plan_type_id
4944 ,p_budget_type_code =>NULL
4945 ,x_budget_version_id =>x_revbudget2_version_id
4946 ,x_return_status =>x_return_status
4947 ,x_msg_count =>x_msg_count
4948 ,x_msg_data =>x_msg_data);
4949 EXCEPTION
4950 WHEN NO_DATA_FOUND THEN
4951 x_revbudget2_version_id := NULL;
4952 END;
4953
4954 BEGIN
4955 l_rev_budget2_version_type:=Get_Version_Type(p_project_id
4956 ,p_rev_bgt2_plan_type_id
4957 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
4958
4959 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
4960 p_project_id =>p_project_id
4961 ,p_fin_plan_Type_id =>p_rev_bgt2_plan_type_id
4962 ,p_version_type =>l_rev_budget2_version_type
4963 ,x_fp_options_id =>l_fp_options_id
4964 ,x_fin_plan_version_id =>x_orig_revbudget2_version_id
4965 ,x_return_status =>x_return_status
4966 ,x_msg_count =>x_msg_count
4967 ,x_msg_data =>x_msg_data);
4968 EXCEPTION
4969 WHEN NO_DATA_FOUND THEN
4970 x_orig_revbudget2_version_id := NULL;
4971 END;
4972 ELSE
4973 x_revbudget2_version_id := NULL;
4974 x_orig_revbudget2_version_id := NULL;
4975 END IF;
4976
4977 /*
4978 ** Get current and prior baselined plan versions
4979 ** for forecast cost and revenue plan types.
4980 */
4981 IF p_cost_fcst_plan_type_id IS NOT NULL THEN
4982
4983 BEGIN
4984 Pa_Fin_Plan_Utils.Get_Cost_Base_Version_Info(
4985 p_project_id =>p_project_id
4986 ,p_fin_plan_Type_id =>p_cost_fcst_plan_type_id
4987 ,p_budget_type_code =>NULL
4988 ,x_budget_version_id =>l_temp_cstforecast_version_id
4989 ,x_return_status =>x_return_status
4990 ,x_msg_count =>x_msg_count
4991 ,x_msg_data =>x_msg_data);
4992 EXCEPTION
4993 WHEN NO_DATA_FOUND THEN
4994 l_temp_cstforecast_version_id := NULL;
4995 END;
4996
4997 BEGIN
4998 l_cst_forecast_version_type:=Get_Version_Type(p_project_id
4999 ,p_cost_fcst_plan_type_id
5000 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_COST);
5001
5002 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
5003 p_project_id =>p_project_id
5004 ,p_fin_plan_Type_id =>p_cost_fcst_plan_type_id
5005 ,p_version_type =>l_cst_forecast_version_type
5006 ,x_fp_options_id =>l_fp_options_id
5007 ,x_fin_plan_version_id =>l_tmp_orig_cstforecast_ver_id
5008 ,x_return_status =>x_return_status
5009 ,x_msg_count =>x_msg_count
5010 ,x_msg_data =>x_msg_data);
5011 EXCEPTION
5012 WHEN NO_DATA_FOUND THEN
5013 l_tmp_orig_cstforecast_ver_id := NULL;
5014 END;
5015
5016 IF (l_temp_cstforecast_version_id IS NOT NULL) AND (l_temp_cstforecast_version_id <>-99) THEN
5017 x_cstforecast_version_id := l_temp_cstforecast_version_id;
5018
5019 BEGIN
5020 Pa_Planning_Element_Utils.get_finplan_bvids(
5021 p_project_id =>p_project_id
5022 ,p_budget_version_id => l_temp_cstforecast_version_id
5023 , x_current_version_id => l_temp_holder1
5024 , x_original_version_id => l_temp_holder2
5025 , x_prior_fcst_version_id => x_prior_cstfcst_version_id
5026 , x_return_status => x_return_status
5027 , x_msg_count => x_msg_count
5028 , x_msg_data => x_msg_data);
5029 --Bug5510794 deriving the correct prior forecast version id
5030 x_prior_cstfcst_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id(l_temp_cstforecast_version_id, p_project_id);
5031 EXCEPTION
5032 WHEN NO_DATA_FOUND THEN
5033 x_prior_cstfcst_version_id := NULL;
5034 END;
5035
5036 ELSE
5037 x_cstforecast_version_id := NULL;
5038 x_prior_cstfcst_version_id := NULL;
5039 END IF;
5040 ELSE
5041 x_cstforecast_version_id := NULL;
5042 x_prior_cstfcst_version_id := NULL;
5043 END IF;
5044
5045 IF p_rev_fcst_plan_type_id IS NOT NULL THEN
5046
5047 BEGIN
5048 Pa_Fin_Plan_Utils.Get_Rev_Base_Version_Info(
5049 p_project_id =>p_project_id
5050 ,p_fin_plan_Type_id =>p_rev_fcst_plan_type_id
5051 ,p_budget_type_code =>NULL
5052 ,x_budget_version_id =>l_temp_revforecast_version_id
5053 ,x_return_status =>x_return_status
5054 ,x_msg_count =>x_msg_count
5055 ,x_msg_data =>x_msg_data);
5056 EXCEPTION
5057 WHEN NO_DATA_FOUND THEN
5058 l_temp_revforecast_version_id := NULL;
5059 END;
5060
5061 BEGIN
5062 l_rev_forecast_version_type:=Get_Version_Type(p_project_id
5063 ,p_rev_fcst_plan_type_id
5064 ,Pa_Fp_Constants_Pkg.G_VERSION_TYPE_REVENUE);
5065
5066 Pa_Fin_Plan_Utils.Get_Curr_Original_Version_Info(
5067 p_project_id =>p_project_id
5068 ,p_fin_plan_Type_id =>p_rev_fcst_plan_type_id
5069 ,p_version_type =>l_rev_forecast_version_type
5070 ,x_fp_options_id =>l_fp_options_id
5071 ,x_fin_plan_version_id =>l_tmp_orig_revforecast_ver_id
5072 ,x_return_status =>x_return_status
5073 ,x_msg_count =>x_msg_count
5074 ,x_msg_data =>x_msg_data);
5075 EXCEPTION
5076 WHEN NO_DATA_FOUND THEN
5077 l_tmp_orig_revforecast_ver_id := NULL;
5078 END;
5079
5080 IF (l_temp_revforecast_version_id IS NOT NULL) AND (l_temp_revforecast_version_id <>-99) THEN
5081 x_revforecast_version_id := l_temp_revforecast_version_id;
5082
5083 BEGIN
5084 Pa_Planning_Element_Utils.get_finplan_bvids(
5085 p_project_id => p_project_id
5086 ,p_budget_version_id => l_temp_revforecast_version_id
5087 , x_current_version_id => l_temp_holder1
5088 , x_original_version_id => l_temp_holder2
5089 , x_prior_fcst_version_id => x_prior_revfcst_version_id
5090 , x_return_status => x_return_status
5091 , x_msg_count => x_msg_count
5092 , x_msg_data => x_msg_data);
5093 --Bug5510794 deriving the correct prior forecast version id
5094 x_prior_revfcst_version_id := Pa_Planning_Element_Utils.get_prior_forecast_version_id(l_temp_revforecast_version_id, p_project_id);
5095 EXCEPTION
5096 WHEN NO_DATA_FOUND THEN
5097 x_prior_revfcst_version_id := NULL;
5098 END;
5099
5100 ELSE
5101 x_revforecast_version_id := NULL;
5102 x_prior_revfcst_version_id := NULL;
5103 END IF;
5104 ELSE
5105 x_revforecast_version_id := NULL;
5106 x_prior_revfcst_version_id := NULL;
5107 END IF;
5108 END;
5109 ELSE
5110 x_cstbudget_version_id := NULL;
5111 x_orig_cstbudget_version_id := NULL;
5112 x_revbudget_version_id := NULL;
5113 x_orig_revbudget_version_id := NULL;
5114 x_revbudget2_version_id := NULL;
5115 x_orig_revbudget2_version_id := NULL;
5116 x_cstbudget2_version_id := NULL;
5117 x_orig_cstbudget2_version_id := NULL;
5118 x_cstforecast_version_id := NULL;
5119 x_prior_cstfcst_version_id := NULL;
5120 x_revforecast_version_id := NULL;
5121 x_prior_revfcst_version_id := NULL;
5122
5123 END IF;
5124
5125 /* The pa api of retrieving the plan versions need to check whether the given
5126 * plan type belongs to the given project id. If not, it will put an error
5127 * message into the stack. Since we don't think this is a exception, we have
5128 * catched the exception in the API, but the error message is still there,
5129 * so we need to clean the stack too.*/
5130
5131 Fnd_Msg_Pub.Initialize;
5132
5133 IF g_debug_mode = 'Y' THEN
5134 Pji_Utils.WRITE2LOG( 'derive_default_plan_versions: finishing', TRUE , g_proc);
5135 END IF;
5136
5137 EXCEPTION
5138 WHEN OTHERS THEN
5139 x_msg_count := x_msg_count + 1;
5140 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5141 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Get_Plan_Versions_Id');
5142 RAISE;
5143 END Derive_Plan_Version_Ids;
5144
5145 /*
5146 * This api checks for each project in this passed program whether they
5147 * have the same GL or same PA calendar or not. If GL calendar is same then x_gl_flag
5148 * will return 'T' else 'F'. This logic is true for PA calendar also.
5149 */
5150 PROCEDURE Check_Perf_Cal_Consistency(
5151 p_project_id IN pa_projects_all.project_id%TYPE
5152 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
5153 ,x_gl_flag OUT NOCOPY VARCHAR2
5154 ,x_pa_flag OUT NOCOPY VARCHAR2
5155 ,x_return_status OUT NOCOPY VARCHAR2
5156 ,x_msg_count OUT NOCOPY NUMBER
5157 ,x_msg_data OUT NOCOPY VARCHAR2)
5158 IS
5159 l_msg_count NUMBER := 0;
5160 l_data VARCHAR2(2000);
5161 l_msg_data VARCHAR2(2000);
5162 l_debug_mode VARCHAR2(1);
5163 l_num_of_gl_cal NUMBER := 0;
5164 l_num_of_pa_cal NUMBER := 0;
5165 l_debug_level3 CONSTANT NUMBER := 3;
5166 l_debug_level5 CONSTANT NUMBER := 5;
5167 l_module_name VARCHAR2(100) := 'pa.plsql.Check_Perf_Cal_Consistency';
5168
5169 BEGIN
5170 x_msg_count := 0;
5171 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5172 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
5173
5174 IF l_debug_mode = 'Y' THEN
5175 Pa_Debug.set_curr_function( p_function => 'Check_Perf_Cal_Consistency',
5176 p_debug_mode => l_debug_mode );
5177 END IF;
5178
5179 --These flags will denote the respective consistency. A value of T for x_gl_flag, and x_pa_flag
5180 --indicates that all the projects in the program have the same GL/PA calendar. A value of F indicates
5181 --inconsistency.
5182 x_gl_flag := 'T';
5183 x_pa_flag := 'T';
5184
5185 --Obtain the GL and PA calendars count
5186 SELECT COUNT(DISTINCT info.gl_calendar_id),
5187 COUNT(DISTINCT info.pa_calendar_id)
5188 INTO l_num_of_gl_cal,
5189 l_num_of_pa_cal
5190 FROM
5191 pji_xbs_denorm denorm
5192 , pa_proj_elements elem
5193 , pa_projects_all proj
5194 , pji_org_extr_info info
5195 WHERE 1=1
5196 AND denorm.sup_project_id = p_project_id -- project_id
5197 AND denorm.sup_id = p_wbs_version_id -- wbs_version_id
5198 AND denorm.struct_type = 'PRG'
5199 AND denorm.struct_version_id IS NULL
5200 AND NVL(denorm.relationship_type,'WF') IN ('LF','WF')
5201 AND denorm.sub_emt_id = elem.proj_element_id
5202 AND proj.project_id = elem.project_id
5203 AND NVL(info.org_id,-99) = NVL(proj.org_id,-99);
5204
5205 IF (NVL(l_num_of_gl_cal,0) > 1 ) THEN
5206 x_gl_flag := 'F';
5207 ELSE
5208 x_gl_flag := 'T';
5209 END IF;
5210
5211 IF (NVL(l_num_of_pa_cal,0) > 1 ) THEN
5212 x_pa_flag := 'F';
5213 ELSE
5214 x_pa_flag := 'T';
5215 END IF;
5216
5217 IF l_debug_mode = 'Y' THEN
5218 Pa_Debug.g_err_stage:= 'Exiting Check_Perf_Cal_Consistency';
5219 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
5220 Pa_Debug.reset_curr_function;
5221 END IF;
5222 EXCEPTION
5223 WHEN OTHERS THEN
5224 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5225 x_msg_count := 1;
5226 x_msg_data := SQLERRM;
5227
5228 Fnd_Msg_Pub.add_exc_msg
5229 ( p_pkg_name => 'PJI_REP_UTIL'
5230 ,p_procedure_name => 'Check_Perf_Cal_Consistency'
5231 ,p_error_text => x_msg_data);
5232
5233 IF l_debug_mode = 'Y' THEN
5234 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
5235 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
5236 Pa_Debug.reset_curr_function;
5237 END IF;
5238 RAISE;
5239 END Check_Perf_Cal_Consistency;
5240
5241 /*
5242 * This api checks for each project in this passed program whether they
5243 * have the same Project Functional Currency (PFC) or not. If PFC is same then x_pfc_flag
5244 * will return 'T' else 'F'.
5245 */
5246 PROCEDURE Check_Perf_Curr_Consistency(
5247 p_project_id IN pa_projects_all.project_id%TYPE
5248 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
5249 ,x_pfc_flag OUT NOCOPY VARCHAR2
5250 ,x_return_status OUT NOCOPY VARCHAR2
5251 ,x_msg_count OUT NOCOPY NUMBER
5252 ,x_msg_data OUT NOCOPY VARCHAR2)
5253 IS
5254 l_msg_count NUMBER := 0;
5255 l_data VARCHAR2(2000);
5256 l_msg_data VARCHAR2(2000);
5257 l_debug_mode VARCHAR2(1);
5258 l_debug_level3 CONSTANT NUMBER := 3;
5259 l_debug_level5 CONSTANT NUMBER := 5;
5260 l_module_name VARCHAR2(100) := 'pa.plsql.Check_Perf_Cal_Consistency';
5261 l_num_of_projfunc_curr NUMBER;
5262
5263 BEGIN
5264 x_msg_count := 0;
5265 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5266 l_debug_mode := NVL(Fnd_Profile.value('PA_DEBUG_MODE'),'N');
5267
5268 IF l_debug_mode = 'Y' THEN
5269 Pa_Debug.set_curr_function( p_function => 'Check_Perf_Curr_Consistency',
5270 p_debug_mode => l_debug_mode );
5271 END IF;
5272
5273 --These flags will denote the respective consistency. A value of T for x_pfc_flag
5274 --indicates that all the projects in the program have the same PFC. A value of F indicates
5275 --inconsistency.
5276 x_pfc_flag := 'T';
5277
5278 --Obtain the PFC count
5279 SELECT COUNT(DISTINCT proj.projfunc_currency_code)
5280 INTO l_num_of_projfunc_curr
5281 FROM
5282 pji_xbs_denorm denorm
5283 , pa_proj_elements elem
5284 , pa_projects_all proj
5285 WHERE 1=1
5286 AND denorm.sup_project_id = p_project_id -- project_id
5287 AND denorm.sup_id = p_wbs_version_id -- wbs_version_id
5288 AND denorm.struct_type = 'PRG'
5289 AND denorm.struct_version_id IS NULL
5290 AND NVL(denorm.relationship_type,'WF') IN ('LF','WF')
5291 AND denorm.sub_emt_id = elem.proj_element_id
5292 AND proj.project_id = elem.project_id;
5293
5294 IF (NVL(l_num_of_projfunc_curr,0) > 1 ) THEN
5295 x_pfc_flag := 'F';
5296 ELSE
5297 x_pfc_flag := 'T';
5298 END IF;
5299
5300 IF l_debug_mode = 'Y' THEN
5301 Pa_Debug.g_err_stage:= 'Exiting Check_Perf_Curr_Consistency';
5302 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level3);
5303 Pa_Debug.reset_curr_function;
5304 END IF;
5305 EXCEPTION
5306 WHEN OTHERS THEN
5307 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5308 x_msg_count := 1;
5309 x_msg_data := SQLERRM;
5310
5311 Fnd_Msg_Pub.add_exc_msg
5312 ( p_pkg_name => 'PJI_REP_UTIL'
5313 ,p_procedure_name => 'Check_Perf_Curr_Consistency'
5314 ,p_error_text => x_msg_data);
5315
5316 IF l_debug_mode = 'Y' THEN
5317 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
5318 Pa_Debug.WRITE(l_module_name,Pa_Debug.g_err_stage,l_debug_level5);
5319 Pa_Debug.reset_curr_function;
5320 END IF;
5321 RAISE;
5322 END Check_Perf_Curr_Consistency;
5323
5324 /* Adding this procedure to do addition calculation with Null rules
5325 * for bug 4194804. Please do not add out parameters because this
5326 * function is also used as select function in VO.xml also
5327 */
5328 FUNCTION Measures_Total(
5329 p_measure1 IN NUMBER
5330 , p_measure2 IN NUMBER DEFAULT NULL
5331 , p_measure3 IN NUMBER DEFAULT NULL
5332 , p_measure4 IN NUMBER DEFAULT NULL
5333 , p_measure5 IN NUMBER DEFAULT NULL
5334 , p_measure6 IN NUMBER DEFAULT NULL
5335 , p_measure7 IN NUMBER DEFAULT NULL
5336 ) RETURN NUMBER
5337 IS
5338 l_measures_total NUMBER;
5339 l_debug_mode VARCHAR2(1);
5340 l_debug_level3 CONSTANT NUMBER := 3;
5341 l_debug_level5 CONSTANT NUMBER := 5;
5342 l_module_name VARCHAR2(100) := 'pa.plsql.Measures_Total';
5343 BEGIN
5344 IF ( p_measure1 IS NULL AND p_measure2 IS NULL AND p_measure3 IS NULL AND
5345 p_measure4 IS NULL AND p_measure5 IS NULL AND p_measure6 IS NULL AND
5346 p_measure7 IS NULL ) THEN
5347
5348 RETURN TO_NUMBER(NULL);
5349 ELSE
5350 l_measures_total := ( NVL(p_measure1,0) + NVL(p_measure2,0) + NVL(p_measure3,0) +
5351 NVL(p_measure4,0) + NVL(p_measure5,0) + NVL(p_measure6,0) +
5352 NVL(p_measure7,0) );
5353 RETURN l_measures_total;
5354 END IF;
5355
5356 EXCEPTION
5357 WHEN OTHERS THEN
5358 NULL;
5359 END Measures_Total;
5360
5361 /* Checks if the smart slice api has been called or not.
5362 If it is called then no need to call processing page,
5363 but if it is not called then call the api and launch
5364 the processing page. But if the processing is Deferred
5365 then launch concurrent program */
5366
5367 /* Changed the whole logic for Program and Project case for bug 4411930 */
5368
5369 PROCEDURE Is_Smart_Slice_Created(
5370 p_rbs_version_id IN NUMBER,
5371 p_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
5372 p_wbs_element_id IN NUMBER,
5373 p_rbs_element_id IN NUMBER,
5374 p_prg_rollup_flag IN VARCHAR2,
5375 p_curr_record_type_id IN NUMBER,
5376 p_calendar_type IN VARCHAR2,
5377 p_wbs_version_id IN NUMBER,
5378 p_commit IN VARCHAR2 := 'Y',
5379 p_project_id IN NUMBER, -- Aded for bug 4419342
5380 x_Smart_Slice_Flag OUT NOCOPY VARCHAR2,
5381 x_msg_count OUT NOCOPY NUMBER,
5382 x_msg_data OUT NOCOPY VARCHAR2,
5383 x_return_status OUT NOCOPY VARCHAR2)
5384 IS
5385 l_plan_version_id NUMBER := NULL;
5386 l_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5387 l_plan_type_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5388 l_project_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5389 l_get_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5390 l_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
5391 l_exists VARCHAR2(1) := 'N';
5392 j NUMBER := 0;
5393 l_count NUMBER := 1;
5394 l_IsSmartSliceCreated_Flag VARCHAR2(1) := 'Y';
5395 l_found BOOLEAN;
5396 l_project_id NUMBER := 1;
5397
5398 -- OLAP START
5399 l_pjt_rollup_enabled_flag varchar2(1);
5400 -- OLAP END
5401 BEGIN
5402 l_IsSmartSliceCreated_Flag := 'Y';
5403
5404 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5405 IF g_debug_mode = 'Y' THEN
5406 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: begining', TRUE , g_proc);
5407 END IF;
5408
5409
5410 -- OLAP START
5411
5412 select p.pjt_rollup_enabled_flag
5413 into l_pjt_rollup_enabled_flag
5414 from pa_projects_all p
5415 where p.project_id = p_project_id ;
5416 --16672616
5417
5418
5419
5420 if l_pjt_rollup_enabled_flag = 'Y' then
5421
5422
5423 l_IsSmartSliceCreated_Flag := 'N';
5424 x_Smart_Slice_Flag := 'N';
5425
5426
5427
5428
5429 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5430 IF g_debug_mode = 'Y' THEN
5431 Pji_Utils.WRITE2LOG( 'NEW PPR END OF API ', TRUE , g_proc);
5432 END IF;
5433
5434 return ; -- NO NEED TO EXECUTE THE BELOW CODE.
5435
5436 end if ;
5437
5438
5439 -- OLAP END
5440
5441
5442
5443
5444
5445 -- Checking if RBS version id is null or p_plan_version_ids's table is null then exiting smoothly
5446 IF ( p_rbs_version_id IS NULL OR p_plan_version_id_tbl.COUNT <= 0 ) THEN
5447 IF g_debug_mode = 'Y' THEN
5448 Pji_Utils.WRITE2LOG( 'Exiting from Is_Smart_Slice_Created: no RBS or Plan version id found', TRUE , g_proc);
5449 END IF;
5450
5451 RETURN;
5452 END IF;
5453
5454 l_plan_version_id_tbl.EXTEND;
5455 FOR i IN p_plan_version_id_tbl.FIRST..p_plan_version_id_tbl.LAST LOOP
5456 IF ( p_plan_version_id_tbl.EXISTS(i)) THEN
5457 l_found := FALSE;
5458 FOR j IN l_plan_version_id_tbl.FIRST..l_plan_version_id_tbl.LAST LOOP
5459 IF ( l_plan_version_id_tbl(j) = p_plan_version_id_tbl(i) ) THEN
5460 l_found := TRUE;
5461 EXIT;
5462 END IF;
5463 END LOOP;
5464 IF ( NOT l_found ) THEN
5465 l_plan_version_id_tbl(l_plan_version_id_tbl.COUNT) := p_plan_version_id_tbl(i);
5466 l_plan_version_id_tbl.EXTEND;
5467 IF g_debug_mode = 'Y' THEN
5468 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: inside assignment', TRUE , g_proc);
5469 END IF;
5470 END IF;
5471 END IF;
5472 END LOOP;
5473
5474 l_count := l_plan_version_id_tbl.COUNT - 1;
5475
5476
5477 FOR i IN 1..17 LOOP
5478 IF ( l_plan_version_id_tbl.EXISTS(i)) THEN
5479 NULL;
5480 ELSE
5481 l_plan_version_id_tbl.EXTEND;
5482 l_plan_version_id_tbl(i) := 0;
5483 END IF;
5484 l_project_id_tbl.EXTEND;
5485 l_project_id_tbl(i) := p_project_id;
5486 END LOOP;
5487
5488 IF ( g_debug_mode = 'Y') THEN
5489 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with logic', TRUE , g_proc);
5490 END IF;
5491
5492 /* Program case */
5493 IF (NVL(p_prg_rollup_flag,'N') = 'Y') THEN
5494
5495 IF g_debug_mode = 'Y' THEN
5496 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Entering into Program logic', TRUE , g_proc);
5497 END IF;
5498
5499 BEGIN
5500 SELECT project_id
5501 INTO l_project_id
5502 FROM
5503 pa_proj_element_versions
5504 WHERE
5505 ELEMENT_VERSION_ID = p_wbs_version_id;
5506 EXCEPTION
5507 WHEN NO_DATA_FOUND THEN
5508 NULL;
5509 END;
5510
5511 IF (g_debug_mode = 'Y') THEN
5512 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with Project Select', TRUE , g_proc);
5513 END IF;
5514
5515 BEGIN
5516 SELECT DISTINCT head.wbs_version_id BULK COLLECT
5517 INTO l_get_wbs_version_id_tbl
5518 FROM pji_pjp_wbs_header head
5519 WHERE head.plan_version_id IN ( l_plan_version_id_tbl(1),l_plan_version_id_tbl(2),l_plan_version_id_tbl(3),
5520 l_plan_version_id_tbl(4), l_plan_version_id_tbl(5),l_plan_version_id_tbl(6),
5521 l_plan_version_id_tbl(7),l_plan_version_id_tbl(8), l_plan_version_id_tbl(9),
5522 l_plan_version_id_tbl(10),l_plan_version_id_tbl(11),l_plan_version_id_tbl(12),
5523 l_plan_version_id_tbl(13),l_plan_version_id_tbl(14),l_plan_version_id_tbl(15),
5524 l_plan_version_id_tbl(16),l_plan_version_id_tbl(17) )
5525 AND head.project_id = l_project_id;
5526 EXCEPTION
5527 WHEN NO_DATA_FOUND THEN
5528 NULL;
5529 IF g_debug_mode = 'Y' THEN
5530 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found in wbs_version_id Select', TRUE , g_proc);
5531 END IF;
5532 END;
5533
5534 IF g_debug_mode = 'Y' THEN
5535 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with wbs_version_id Select', TRUE , g_proc);
5536 END IF;
5537
5538 BEGIN
5539 SELECT DISTINCT NVL(head.plan_type_id,-1) BULK COLLECT
5540 INTO l_plan_type_id_tbl
5541 FROM pji_pjp_wbs_header head
5542 WHERE head.plan_version_id IN ( l_plan_version_id_tbl(1),l_plan_version_id_tbl(2),l_plan_version_id_tbl(3),
5543 l_plan_version_id_tbl(4), l_plan_version_id_tbl(5),l_plan_version_id_tbl(6),
5544 l_plan_version_id_tbl(7),l_plan_version_id_tbl(8), l_plan_version_id_tbl(9),
5545 l_plan_version_id_tbl(10),l_plan_version_id_tbl(11),l_plan_version_id_tbl(12),
5546 l_plan_version_id_tbl(13),l_plan_version_id_tbl(14),l_plan_version_id_tbl(15),
5547 l_plan_version_id_tbl(16),l_plan_version_id_tbl(17) )
5548 AND head.project_id = l_project_id;
5549 EXCEPTION
5550 WHEN NO_DATA_FOUND THEN
5551 NULL;
5552 IF g_debug_mode = 'Y' THEN
5553 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found in plan_type_id Select', TRUE , g_proc);
5554 END IF;
5555 END;
5556
5557 IF g_debug_mode = 'Y' THEN
5558 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with plan_type_id Select', TRUE , g_proc);
5559 END IF;
5560
5561 FOR i IN 1..17 LOOP
5562 IF ( l_get_wbs_version_id_tbl.EXISTS(i)) THEN
5563 NULL;
5564 ELSE
5565 l_get_wbs_version_id_tbl.EXTEND;
5566 l_get_wbs_version_id_tbl(i) := 0;
5567 END IF;
5568 END LOOP;
5569
5570 FOR i IN 1..17 LOOP
5571 IF ( l_plan_type_id_tbl.EXISTS(i)) THEN
5572 NULL;
5573 ELSE
5574 l_plan_type_id_tbl.EXTEND;
5575 l_plan_type_id_tbl(i) := 0;
5576 END IF;
5577 END LOOP;
5578
5579 IF g_debug_mode = 'Y' THEN
5580 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Before deleting project tem table and plan version temp table', TRUE , g_proc);
5581 END IF;
5582
5583 l_plan_version_id_tbl.DELETE;
5584 l_project_id_tbl.DELETE;
5585
5586 IF g_debug_mode = 'Y' THEN
5587 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Before selecting plan version,project,wbs', TRUE , g_proc);
5588 END IF;
5589
5590 BEGIN
5591 SELECT DISTINCT head.plan_version_id,head.project_id ,pji.sub_id wbs_version_id BULK COLLECT
5592 INTO l_plan_version_id_tbl, l_project_id_tbl,l_wbs_version_id_tbl
5593 FROM pji_xbs_Denorm pji
5594 ,pji_pjp_wbs_header head
5595 WHERE pji.struct_type='PRG'
5596 --AND pji.sup_level<>pji.sub_level --Bug 4624479
5597 AND pji.sup_id IN(l_get_wbs_version_id_tbl(1),l_get_wbs_version_id_tbl(2),l_get_wbs_version_id_tbl(3)
5598 ,l_get_wbs_version_id_tbl(4),l_get_wbs_version_id_tbl(5),l_get_wbs_version_id_tbl(6)
5599 ,l_get_wbs_version_id_tbl(7),l_get_wbs_version_id_tbl(8),l_get_wbs_version_id_tbl(9)
5600 ,l_get_wbs_version_id_tbl(10),l_get_wbs_version_id_tbl(11),l_get_wbs_version_id_tbl(12)
5601 ,l_get_wbs_version_id_tbl(13),l_get_wbs_version_id_tbl(14),l_get_wbs_version_id_tbl(15)
5602 ,l_get_wbs_version_id_tbl(16),l_get_wbs_version_id_tbl(17))
5603 AND pji.sub_id=head.wbs_version_id
5604 AND p_prg_rollup_flag='Y'
5605 AND NVL(head.plan_type_id,-1) IN (l_plan_type_id_tbl(1),l_plan_type_id_tbl(2),l_plan_type_id_tbl(3)
5606 ,l_plan_type_id_tbl(4),l_plan_type_id_tbl(5),l_plan_type_id_tbl(6)
5607 ,l_plan_type_id_tbl(7),l_plan_type_id_tbl(8),l_plan_type_id_tbl(9)
5608 ,l_plan_type_id_tbl(10),l_plan_type_id_tbl(11),l_plan_type_id_tbl(12)
5609 ,l_plan_type_id_tbl(13),l_plan_type_id_tbl(14),l_plan_type_id_tbl(15)
5610 ,l_plan_type_id_tbl(16),l_plan_type_id_tbl(17))
5611 AND ((head.cb_flag='Y' ) OR (head.co_flag='Y' ) OR (head.wp_flag='Y') OR (head.wp_flag='N'
5612 AND head.plan_version_id = -1) );
5613
5614 EXCEPTION
5615 WHEN OTHERS THEN
5616 NULL;
5617 IF g_debug_mode = 'Y' THEN
5618 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found in selecting plan version,project,wbs', TRUE , g_proc);
5619 END IF;
5620 END;
5621
5622 IF g_debug_mode = 'Y' THEN
5623 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with selecting plan version,project,wbs', TRUE , g_proc);
5624 END IF;
5625
5626 END IF; -- Program Case
5627
5628 IF g_debug_mode = 'Y' THEN
5629 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with program logic and checking status table', TRUE , g_proc);
5630 END IF;
5631
5632
5633 FOR i IN 1..l_plan_version_id_tbl.COUNT LOOP
5634
5635 IF (l_plan_version_id_tbl(i) <> 0) THEN
5636 -- Reset flag values.
5637 l_exists := 'N';
5638 BEGIN
5639 SELECT 'Y'
5640 INTO l_exists FROM dual
5641 WHERE EXISTS ( SELECT 1
5642 FROM pji_rollup_level_status rst
5643 WHERE rst.rbs_version_id = p_rbs_version_id
5644 AND rst.plan_version_id = l_plan_version_id_tbl(i)
5645 AND rst.project_id = l_project_id_tbl(i));
5646 EXCEPTION
5647 WHEN NO_DATA_FOUND THEN
5648 x_Smart_Slice_Flag := 'Y';
5649 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5650 IF ( g_debug_mode = 'Y' ) THEN
5651 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found ', TRUE , g_proc);
5652 END IF;
5653 END;
5654
5655 IF ( g_debug_mode = 'Y') THEN
5656 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Coming out as one of the version does not hace smart slice', TRUE , g_proc);
5657 END IF;
5658
5659 -- If smart slice does not exist mark to create the same
5660 IF ( l_exists = 'N' ) THEN
5661 l_IsSmartSliceCreated_Flag := 'N';
5662 EXIT;
5663 END IF;
5664
5665 END IF;
5666
5667 END LOOP;
5668
5669 /* Important: If the process is Deferred for getting the data, then a new status will be passed i.e. 'D' */
5670
5671 x_Smart_Slice_Flag := l_IsSmartSliceCreated_Flag;
5672 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5673 EXCEPTION
5674 WHEN OTHERS THEN
5675 x_msg_count := 1;
5676 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5677 IF g_debug_mode = 'Y' THEN
5678 Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: When others ', TRUE , g_proc);
5679 END IF;
5680 NULL;
5681 END Is_Smart_Slice_Created;
5682
5683
5684
5685 /*
5686 This procedure checks if the passed plan versions
5687 are having same RBS or not. It returns two valid
5688 values:
5689 'Y':- The passed Plan versions having same RBS
5690 'N':- The passed Plan version do not have same RBS
5691 Assumptions:
5692 + RBS is attached with context plan version
5693 + Additional Plan versions are selected
5694 */
5695 PROCEDURE Chk_plan_vers_have_same_RBS(
5696 p_fin_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
5697 x_R_PlanVers_HavSame_RBS_Flag OUT NOCOPY VARCHAR2,
5698 x_msg_count OUT NOCOPY NUMBER,
5699 x_msg_data OUT NOCOPY VARCHAR2,
5700 x_return_status OUT NOCOPY VARCHAR2)
5701 IS
5702 l_fin_plan_version_id NUMBER := NULL;
5703 l_fin_plan_version_ids SYSTEM.pa_num_tbl_type;
5704 j NUMBER := 0;
5705 l_count NUMBER := 1;
5706 l_R_PlanVers_HavSame_RBS_Flag VARCHAR2(1) := 'Y';
5707 BEGIN
5708 l_R_PlanVers_HavSame_RBS_Flag := 'Y';
5709
5710 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5711 IF g_debug_mode = 'Y' THEN
5712 Pji_Utils.WRITE2LOG( 'Chk_plan_vers_have_same_RBS: begining', TRUE , g_proc);
5713 END IF;
5714
5715 -- Checking if p_plan_version_ids's table is null then exiting smoothly
5716 IF ( p_fin_plan_version_id_tbl.COUNT <= 0 ) THEN
5717 IF g_debug_mode = 'Y' THEN
5718 Pji_Utils.WRITE2LOG( 'Exiting from Chk_plan_vers_have_same_RBS: No Plan version id found', TRUE , g_proc);
5719 END IF;
5720
5721 RETURN;
5722 END IF;
5723 l_count := 1;
5724
5725 FOR i IN p_fin_plan_version_id_tbl.FIRST..p_fin_plan_version_id_tbl.LAST LOOP
5726 l_fin_plan_version_id := NULL;
5727
5728 IF ( p_fin_plan_version_id_tbl.EXISTS(i)) THEN
5729 j := j + 1;
5730 l_fin_plan_version_ids(j) := p_fin_plan_version_id_tbl(i);
5731
5732 IF g_debug_mode = 'Y' THEN
5733 Pji_Utils.WRITE2LOG( 'Chk_plan_vers_have_same_RBS: inside assignment', TRUE , g_proc);
5734 END IF;
5735 END IF;
5736 END LOOP;
5737
5738 FOR i IN 1..8 LOOP
5739 IF ( l_fin_plan_version_ids.EXISTS(i)) THEN
5740 NULL;
5741 ELSE
5742 l_fin_plan_version_ids(i) := 0;
5743 END IF;
5744 END LOOP;
5745
5746 IF ( l_fin_plan_version_ids.COUNT > 0 ) THEN
5747 BEGIN
5748 SELECT DECODE(COUNT(DISTINCT rbs_version_id),l_count,'Y','N')
5749 INTO l_R_PlanVers_HavSame_RBS_Flag
5750 FROM pa_proj_fp_options
5751 WHERE 1=1
5752 AND fin_plan_option_level_code='PLAN_VERSION'
5753 AND fin_plan_version_id IN (l_fin_plan_version_ids(1),l_fin_plan_version_ids(2),l_fin_plan_version_ids(3),l_fin_plan_version_ids(4),
5754 l_fin_plan_version_ids(5),l_fin_plan_version_ids(6),l_fin_plan_version_ids(7),l_fin_plan_version_ids(8));
5755
5756 IF g_debug_mode = 'Y' THEN
5757 Pji_Utils.WRITE2LOG( 'Chk_plan_vers_have_same_RBS: Done selecting from pa_proj_fp_options table ', TRUE , g_proc);
5758 END IF;
5759
5760 IF ( l_R_PlanVers_HavSame_RBS_Flag = 'Y') THEN
5761 x_R_PlanVers_HavSame_RBS_Flag := l_R_PlanVers_HavSame_RBS_Flag;
5762 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5763 ELSE
5764 x_R_PlanVers_HavSame_RBS_Flag := l_R_PlanVers_HavSame_RBS_Flag;
5765 x_msg_count := NVL(x_msg_count,0) + 1;
5766 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5767 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_NOT_SAME_RBS', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR);
5768 END IF;
5769
5770
5771 EXCEPTION
5772 WHEN NO_DATA_FOUND THEN
5773 x_R_PlanVers_HavSame_RBS_Flag := 'N';
5774 x_msg_count := NVL(x_msg_count,0) + 1;
5775 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5776 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_NOT_SAME_RBS', p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR);
5777 IF g_debug_mode = 'Y' THEN
5778 Pji_Utils.WRITE2LOG( 'Chk_plan_vers_have_same_RBS: No data found ', TRUE , g_proc);
5779 END IF;
5780 END;
5781 END IF;
5782 EXCEPTION
5783 WHEN OTHERS THEN
5784 x_msg_count := 1;
5785 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5786 IF g_debug_mode = 'Y' THEN
5787 Pji_Utils.WRITE2LOG( 'Chk_plan_vers_have_same_RBS: When others ', TRUE , g_proc);
5788 END IF;
5789 NULL;
5790 END Chk_plan_vers_have_same_RBS;
5791
5792 PROCEDURE GET_PROCESS_STATUS_MSG(
5793 p_project_id IN pa_projects_all.project_id%TYPE
5794 , p_structure_type IN pa_structure_types.structure_type%TYPE := NULL
5795 , p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE := NULL
5796 , p_prg_flag IN VARCHAR2 := NULL
5797 , x_message_name OUT NOCOPY VARCHAR2
5798 , x_message_type OUT NOCOPY VARCHAR2
5799 , x_structure_version_id OUT NOCOPY NUMBER
5800 , x_conc_request_id OUT NOCOPY NUMBER
5801 , x_return_status IN OUT NOCOPY VARCHAR2
5802 , x_msg_count IN OUT NOCOPY NUMBER
5803 , x_msg_data IN OUT NOCOPY VARCHAR2)
5804 IS
5805
5806 CURSOR struct_list(c_structure_version_id pa_proj_element_versions.element_version_id%TYPE
5807 , c_structure_type VARCHAR2) IS
5808 SELECT vs.element_version_id, vs.project_id
5809 FROM pa_proj_elem_ver_structure vs
5810 , pji_xbs_denorm denorm
5811 WHERE denorm.struct_version_id IS NULL
5812 AND denorm.struct_type = 'PRG'
5813 AND NVL(denorm.relationship_type,'WF') IN ('WF',c_structure_type)
5814 AND denorm.sup_id = c_structure_version_id
5815 AND denorm.sub_id = vs.element_version_id;
5816
5817 l_relationship_type VARCHAR2(2);
5818 l_project_id pa_projects_all.project_id%TYPE;
5819 l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
5820
5821 BEGIN
5822
5823 IF g_debug_mode = 'Y' THEN
5824 Pji_Utils.WRITE2LOG( 'Get_Process_Status_Msg: beginning', TRUE , g_proc);
5825 END IF;
5826
5827 IF x_return_status IS NULL THEN
5828 x_msg_count := 0;
5829 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5830 END IF;
5831
5832 IF p_prg_flag = 'N' THEN
5833 Pa_Project_Structure_Utils.GET_STRUCTURE_MSG(
5834 p_project_id => p_project_id
5835 , p_structure_type => p_structure_type
5836 , p_structure_version_id => p_structure_version_id
5837 , x_message_name => x_message_name
5838 , x_message_type => x_message_type
5839 , x_structure_version_id => x_structure_version_id
5840 , x_conc_request_id => x_conc_request_id);
5841
5842 RETURN;
5843 ELSE
5844 IF p_structure_type = 'WORKPLAN' THEN
5845 l_relationship_type := 'LW';
5846 ELSE
5847 l_relationship_type := 'LF';
5848 END IF;
5849
5850 OPEN struct_list(p_structure_version_id,l_relationship_type);
5851
5852 LOOP
5853 FETCH struct_list INTO l_structure_version_id,l_project_id;
5854 EXIT WHEN struct_list%NOTFOUND;
5855 Pa_Project_Structure_Utils.GET_STRUCTURE_MSG(
5856 p_project_id => l_project_id
5857 , p_structure_type => p_structure_type
5858 , p_structure_version_id => l_structure_version_id
5859 , x_message_name => x_message_name
5860 , x_message_type => x_message_type
5861 , x_structure_version_id => x_structure_version_id
5862 , x_conc_request_id => x_conc_request_id);
5863 IF x_message_name IS NOT NULL THEN
5864 CLOSE struct_list;
5865 RETURN;
5866 END IF;
5867 END LOOP;
5868 CLOSE struct_list;
5869 END IF;
5870
5871 IF g_debug_mode = 'Y' THEN
5872 Pji_Utils.WRITE2LOG( 'Get_Process_Status_Msg: finishing', TRUE , g_proc);
5873 END IF;
5874
5875 EXCEPTION
5876 WHEN OTHERS THEN
5877 x_msg_count := x_msg_count + 1;
5878 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5879 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Complete_Percentage');
5880 RAISE;
5881 END Get_Process_Status_Msg;
5882
5883 PROCEDURE CHECK_PROJ_TYPE_CONSISTENCY
5884 ( p_project_id IN NUMBER
5885 ,p_wbs_version_id IN NUMBER
5886 ,p_structure_type IN VARCHAR2 DEFAULT 'FINANCIAL'
5887 ,x_ptc_flag OUT NOCOPY VARCHAR2 -- project type consistency flag
5888 ,x_return_status OUT NOCOPY VARCHAR2
5889 ,x_msg_count OUT NOCOPY NUMBER
5890 ,x_msg_data OUT NOCOPY VARCHAR2)
5891 IS
5892 l_relationship_type VARCHAR2(2);
5893 l_proj_type_count NUMBER;
5894 BEGIN
5895
5896 IF g_debug_mode = 'Y' THEN
5897 Pji_Utils.WRITE2LOG( 'Check_Proj_Type_Consistency: beginning', TRUE , g_proc);
5898 END IF;
5899
5900 IF x_return_status IS NULL THEN
5901 x_msg_count := 0;
5902 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5903 END IF;
5904
5905 IF p_structure_type = 'WORKPLAN' THEN
5906 l_relationship_type := 'LW';
5907 ELSE
5908 l_relationship_type := 'LF';
5909 END IF;
5910
5911 SELECT COUNT(DISTINCT UPPER(pt.project_type_class_code))
5912 INTO l_proj_type_count
5913 FROM pji_xbs_denorm denorm
5914 ,pa_proj_elem_ver_structure vs
5915 ,pa_projects_all proj
5916 ,pa_project_types_all pt
5917 WHERE struct_type = 'PRG'
5918 AND sup_project_id = p_project_id
5919 AND struct_version_id IS NULL
5920 AND NVL(denorm.relationship_type,'WF') IN ('WF',l_relationship_type)
5921 AND denorm.sup_id = p_wbs_version_id
5922 AND denorm.sub_id = vs.element_version_id
5923 AND vs.project_id = proj.project_id
5924 AND proj.project_type = pt.project_type
5925 AND proj.org_id = pt.org_id ; --Added clause for performnace imp. for bug 5376591
5926
5927 IF l_proj_type_count>1 THEN
5928 x_ptc_flag := 'F';
5929 ELSE
5930 x_ptc_flag := 'T';
5931 END IF;
5932
5933 IF g_debug_mode = 'Y' THEN
5934 Pji_Utils.WRITE2LOG( 'Check_Proj_Type_Consistency: finishing', TRUE , g_proc);
5935 END IF;
5936
5937 EXCEPTION
5938 WHEN OTHERS THEN
5939 x_msg_count := x_msg_count + 1;
5940 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5941 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Check_Proj_Type_Consistency');
5942 RAISE;
5943 END Check_Proj_Type_Consistency;
5944
5945
5946
5947
5948 PROCEDURE Derive_Pji_Calendar_Info(
5949 p_project_id IN NUMBER
5950 , p_period_type IN VARCHAR2
5951 , p_as_of_date IN NUMBER
5952 , x_calendar_type IN OUT NOCOPY VARCHAR2
5953 , x_calendar_id OUT NOCOPY NUMBER
5954 , x_period_name OUT NOCOPY VARCHAR2
5955 , x_report_date_julian OUT NOCOPY NUMBER
5956 , x_slice_name OUT NOCOPY VARCHAR2
5957 , x_return_status IN OUT NOCOPY VARCHAR2
5958 , x_msg_count IN OUT NOCOPY NUMBER
5959 , x_msg_data IN OUT NOCOPY VARCHAR2)
5960 IS
5961 l_gl_calendar_id NUMBER;
5962 l_pa_calendar_id NUMBER;
5963
5964 BEGIN
5965
5966 IF g_debug_mode = 'Y' THEN
5967 Pji_Utils.WRITE2LOG( 'derive_pji_calendar_info: beginning', TRUE , g_proc);
5968 END IF;
5969
5970 IF x_return_status IS NULL THEN
5971 x_msg_count := 0;
5972 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5973 END IF;
5974
5975 SELECT info.gl_calendar_id, info.pa_calendar_id
5976 INTO l_gl_calendar_id, l_pa_calendar_id
5977 FROM pji_org_extr_info info, pa_projects_all proj
5978 WHERE info.org_id = proj.org_id
5979 AND proj.project_id = p_project_id;
5980
5981 IF x_calendar_type IS NULL THEN
5982 IF p_period_type = 'FII_TIME_CAL_PERIOD' OR p_period_type = 'FII_TIME_CAL_QTR'
5983 OR p_period_type = 'FII_TIME_CAL_YEAR' THEN
5984 x_calendar_type := 'G';
5985 x_calendar_id := l_gl_calendar_id;
5986 ELSIF p_period_type = 'PJI_TIME_PA_PERIOD' THEN
5987 x_calendar_type := 'P';
5988 x_calendar_id := l_pa_calendar_id;
5989 ELSE
5990 x_calendar_type := 'E';
5991 x_calendar_id := -1;
5992 END IF;
5993 ELSE
5994 IF x_calendar_type = 'E' THEN
5995 x_calendar_id := -1;
5996 ELSE
5997 IF x_calendar_type = 'G' THEN
5998 x_calendar_id := l_gl_calendar_id;
5999 ELSE
6000 x_calendar_id := l_pa_calendar_id;
6001 END IF;
6002 END IF;
6003 END IF;
6004
6005 IF x_calendar_type = 'E' THEN
6006 SELECT name,TO_CHAR(start_date,'j')
6007 INTO x_period_name, x_report_date_julian
6008 FROM pji_time_ent_period_v
6009 WHERE TO_DATE(p_as_of_date, 'j') BETWEEN start_date AND end_date;
6010 ELSE
6011 SELECT name,TO_CHAR(start_date,'j')
6012 INTO x_period_name, x_report_date_julian
6013 FROM pji_time_cal_period_v
6014 WHERE calendar_id = x_calendar_id
6015 AND TO_DATE(p_as_of_date,'j') BETWEEN start_date AND end_date;
6016 END IF;
6017
6018 IF x_report_date_julian IS NULL THEN
6019 x_report_date_julian :=2;
6020 END IF;
6021
6022 Derive_Slice_Name(p_project_id,
6023 x_calendar_id,
6024 x_slice_name,
6025 x_return_status,
6026 x_msg_count,
6027 x_msg_data);
6028
6029 IF g_debug_mode = 'Y' THEN
6030 Pji_Utils.WRITE2LOG( 'derive_pji_calendar_info: finishing', TRUE , g_proc);
6031 END IF;
6032
6033 EXCEPTION
6034 WHEN NO_DATA_FOUND THEN
6035 x_msg_count := x_msg_count + 1;
6036 x_return_status := Fnd_Api.G_RET_STS_ERROR;
6037 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Pji_Calendar_Info');
6038 x_report_date_julian :=2;
6039 WHEN OTHERS THEN
6040 x_msg_count := x_msg_count + 1;
6041 x_return_status := Fnd_Api.G_RET_STS_ERROR;
6042 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Pji_Calendar_Info');
6043 x_report_date_julian :=2;
6044 RAISE;
6045 END Derive_Pji_Calendar_Info;
6046
6047 PROCEDURE Derive_Pji_Currency_Info(
6048 p_project_id NUMBER
6049 , p_currency_record_type IN VARCHAR2
6050 , x_currency_record_type OUT NOCOPY NUMBER
6051 , x_currency_code OUT NOCOPY VARCHAR2
6052 , x_currency_type OUT NOCOPY VARCHAR2
6053 , x_return_status IN OUT NOCOPY VARCHAR2
6054 , x_msg_count IN OUT NOCOPY NUMBER
6055 , x_msg_data IN OUT NOCOPY VARCHAR2)
6056 IS
6057 l_projfunc_currency_code VARCHAR2(15);
6058 l_project_currency_code VARCHAR2(15);
6059 BEGIN
6060
6061 IF g_debug_mode = 'Y' THEN
6062 Pji_Utils.WRITE2LOG( 'derive_pji_currency_info: beginning', TRUE , g_proc);
6063 END IF;
6064
6065 IF x_return_status IS NULL THEN
6066 x_msg_count := 0;
6067 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6068 END IF;
6069
6070 x_currency_record_type := p_currency_record_type;
6071
6072 IF p_currency_record_type = 1 THEN
6073 x_currency_code := Pji_Utils.get_global_primary_currency;
6074 x_currency_type := 'G';
6075 ELSIF p_currency_record_type = 2 THEN
6076 x_currency_type := 'G';
6077 x_currency_code := Pji_Utils.get_global_secondary_currency;
6078 ELSE
6079 SELECT projfunc_currency_code, project_currency_code
6080 INTO l_projfunc_currency_code,l_project_currency_code
6081 FROM pa_projects_all
6082 WHERE project_id = p_project_id;
6083
6084 IF p_currency_record_type = 4 THEN
6085 x_currency_code := l_projfunc_currency_code;
6086 x_currency_type := 'F';
6087 ELSE
6088 x_currency_code := l_project_currency_code;
6089 x_currency_type := 'P';
6090 END IF;
6091 END IF;
6092
6093 IF g_debug_mode = 'Y' THEN
6094 Pji_Utils.WRITE2LOG( 'derive_pji_currency_info: finishing', TRUE , g_proc);
6095 END IF;
6096
6097 EXCEPTION
6098 WHEN OTHERS THEN
6099 x_msg_count := x_msg_count + 1;
6100 x_return_status := Fnd_Api.G_RET_STS_ERROR;
6101 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Pji_Currency_Info');
6102 RAISE;
6103 END Derive_Pji_Currency_Info;
6104
6105
6106 PROCEDURE Validate_Plan_Type(p_project_id NUMBER
6107 , p_plan_type_id NUMBER
6108 , x_plan_type_id IN OUT NOCOPY NUMBER
6109 , x_return_status IN OUT NOCOPY VARCHAR2
6110 , x_msg_count IN OUT NOCOPY NUMBER
6111 , x_msg_data IN OUT NOCOPY VARCHAR2)
6112 IS
6113 l_plan_type_count NUMBER;
6114 BEGIN
6115
6116 IF g_debug_mode = 'Y' THEN
6117 Pji_Utils.WRITE2LOG( 'validate_plan_type: beginning', TRUE , g_proc);
6118 END IF;
6119
6120 IF x_return_status IS NULL THEN
6121 x_msg_count := 0;
6122 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6123 END IF;
6124
6125 SELECT COUNT(*)
6126 INTO l_plan_type_count
6127 FROM pa_proj_fp_options
6128 WHERE project_id = p_project_id
6129 AND fin_plan_type_id = p_plan_type_id
6130 AND fin_plan_option_level_code = 'PLAN_TYPE'
6131 AND ROWNUM =1;
6132
6133 IF l_plan_type_count > 0 THEN
6134 x_plan_type_id := p_plan_type_id;
6135 END IF;
6136
6137 IF g_debug_mode = 'Y' THEN
6138 Pji_Utils.WRITE2LOG( 'validate_plan_type: finishing', TRUE , g_proc);
6139 END IF;
6140
6141 EXCEPTION
6142 WHEN OTHERS THEN
6143 x_msg_count := x_msg_count + 1;
6144 x_return_status := Fnd_Api.G_RET_STS_ERROR;
6145 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Validate_Plan_Type');
6146 RAISE;
6147 END Validate_Plan_Type;
6148
6149 FUNCTION is_str_linked_to_working_ver
6150 (p_project_id NUMBER
6151 , p_structure_version_id NUMBER
6152 , p_relationship_type VARCHAR2 := 'LW') return VARCHAR2
6153 is
6154 l_return_value VARCHAR2(1) := null;
6155 l_count NUMBER;
6156 BEGIN
6157 IF g_debug_mode = 'Y' THEN
6158 Pji_Utils.WRITE2LOG( 'is_str_linked_to_working_ver: beginning', TRUE , g_proc);
6159 END IF;
6160
6161 l_return_value := 'Y';
6162
6163 Select count(*)
6164 INTO l_count
6165 FROM
6166 pji_xbs_denorm denorm
6167 , pa_proj_elements elem
6168 , pa_proj_elem_ver_structure ppevs
6169 WHERE 1=1
6170 AND denorm.sup_project_id = p_project_id
6171 AND denorm.sup_id = p_structure_version_id
6172 AND denorm.struct_type = 'PRG'
6173 AND NVL(denorm.relationship_type,'WF') IN (p_relationship_type,'WF')
6174 AND denorm.struct_version_id IS NULL
6175 AND denorm.sub_emt_id = elem.proj_element_id
6176 AND ppevs.project_id = elem.project_id
6177 AND ppevs.element_version_id = denorm.sub_id
6178 AND ppevs.status_code = 'STRUCTURE_WORKING';
6179
6180 IF l_count = 0 THEN
6181 l_return_value := 'N';
6182 END IF;
6183
6184 IF g_debug_mode = 'Y' THEN
6185 Pji_Utils.WRITE2LOG( 'is_str_linked_to_working_ver: finishing', TRUE , g_proc);
6186 END IF;
6187
6188 return(l_return_value);
6189 EXCEPTION
6190 WHEN OTHERS THEN
6191 l_return_value := 'N';
6192 return(l_return_value);
6193
6194 END IS_STR_LINKED_TO_WORKING_VER;
6195
6196 FUNCTION Get_Page_Pers_Function_Name
6197 (p_project_type VARCHAR2
6198 ,p_page_type VARCHAR2) return VARCHAR2
6199 IS
6200 l_return_value VARCHAR2(255) := null;
6201 BEGIN
6202 IF g_debug_mode = 'Y' THEN
6203 Pji_Utils.WRITE2LOG( 'Get_Page_Pers_Function_Name : Start', TRUE , g_proc);
6204 END IF;
6205
6206 IF p_project_type = 'CONTRACT' THEN
6207 SELECT ATTRIBUTE12 into l_return_value
6208 FROM pa_lookups
6209 WHERE lookup_code = p_page_type and lookup_type='PA_PAGE_TYPES';
6210 ELSIF p_project_type = 'INDIRECT' THEN
6211 SELECT ATTRIBUTE13 into l_return_value
6212 FROM pa_lookups
6213 WHERE lookup_code = p_page_type and lookup_type='PA_PAGE_TYPES';
6214 ELSIF p_project_type = 'CAPITAL' THEN
6215 SELECT ATTRIBUTE14 into l_return_value
6216 FROM pa_lookups
6217 WHERE lookup_code = p_page_type and lookup_type='PA_PAGE_TYPES';
6218 END IF;
6219
6220 IF g_debug_mode = 'Y' THEN
6221 Pji_Utils.WRITE2LOG( 'Get_Page_Pers_Function_Name : Finish', TRUE , g_proc);
6222 END IF;
6223
6224 return(l_return_value);
6225
6226 EXCEPTION
6227 WHEN OTHERS THEN
6228 return(l_return_value);
6229
6230 END Get_Page_Pers_Function_Name;
6231
6232 --bug 7602538
6233 PROCEDURE drv_prf_prd(
6234 p_from_date IN VARCHAR2,
6235 p_to_date IN VARCHAR2,
6236 x_from_period OUT NOCOPY NUMBER,
6237 x_to_period OUT NOCOPY NUMBER)
6238 IS
6239 l_from_date DATE := NULL;
6240 l_to_date DATE := NULL;
6241 BEGIN
6242 l_from_date := to_date(p_from_date, 'YYYY-MM-DD');
6243 l_to_date := to_date(p_to_date, 'YYYY-MM-DD');
6244
6245 x_from_period := TO_CHAR(l_from_date,'j');
6246 x_to_period := TO_CHAR(l_to_date,'j');
6247
6248 END drv_prf_prd;
6249
6250 --bug 5612955
6251 FUNCTION get_default_calendar_type return VARCHAR2 IS
6252
6253 l_calendar_type VARCHAR2(10);
6254
6255 cursor c1 is
6256 SELECT NVL(Fnd_Profile.value('PJI_DEF_RPT_CAL_TYPE'), 'E') FROM dual; -- Based on profile "PJI: Default Reporting Calendar Type"
6257
6258 begin
6259
6260 open c1;
6261 fetch c1 into l_calendar_type;
6262 close c1;
6263
6264 return l_calendar_type;
6265 END get_default_calendar_type;
6266
6267 FUNCTION get_default_period_name
6268 ( p_project_id IN NUMBER) return VARCHAR2
6269 IS
6270
6271 l_calendar_type VARCHAR2(1);
6272
6273 l_calendar_id NUMBER;
6274 l_report_date_julian NUMBER;
6275
6276
6277 l_period_name VARCHAR2(255);
6278
6279 l_slice_name VARCHAR2(80);
6280 l_return_status VARCHAR2(1);
6281 l_msg_count NUMBER;
6282 l_msg_data VARCHAR2(50);
6283
6284
6285 cursor c1 is
6286 SELECT NVL(Fnd_Profile.value('PJI_DEF_RPT_CAL_TYPE'), 'E') FROM dual; -- Based on Profile : PJI: Default Reporting Calendar Type
6287
6288 begin
6289
6290 open c1;
6291 fetch c1 into l_calendar_type;
6292 close c1;
6293
6294 Derive_Pa_Calendar_Info(p_project_id,l_calendar_type,l_calendar_id,l_report_date_julian,l_period_name,l_slice_name,l_return_status,l_msg_count,l_msg_data);
6295
6296 return Upper(l_period_name);
6297 END get_default_period_name;
6298
6299 -- API name : GET_TASK_LATEST_PUBLISHED_COST
6300 -- Type : Utils API
6301 -- Pre-reqs : None
6302 -- Return Value : Get the task latest published cost value
6303 --
6304 --
6305 --
6306 -- Parameters
6307 -- p_project_id IN NUMBER
6308 -- p_task_id IN NUMBER
6309 --
6310 -- History
6311 --
6312 -- 26-FEB-09 rbruno -Created
6313
6314
6315 FUNCTION GET_TASK_LATEST_PUBLISHED_COST
6316 ( p_project_id IN NUMBER, p_task_id in NUMBER
6317 ) return NUMBER
6318 IS
6319
6320 CURSOR c2 IS
6321
6322
6323
6324 SELECT Sum(brdn_cost) LATEST_PUBLISHED_COST
6325
6326 FROM pji_fp_xbs_accum_f f,
6327 pa_projects_all p,
6328 pa_rbs_prj_assignments r,
6329 pa_proj_elem_ver_structure s,
6330 pa_proj_element_versions ppev,
6331 pji_pjp_wbs_header h
6332 WHERE p.project_id=f.project_id
6333 AND p.project_id=r.project_id
6334 AND p.project_id=s.project_id
6335 AND p.project_id=ppev.project_id
6336 AND p.project_id=h.project_id
6337 AND f.rbs_version_id=-1
6338 AND f.project_id= p_project_id
6339 AND f.project_element_id = p_task_id
6340 AND f.currency_code=p.projfunc_currency_code
6341 AND f.rbs_aggr_level='T'
6342 AND f.prg_rollup_flag='N'
6343 AND f.period_type_id=32
6344 AND f.plan_type_id=10
6345 AND h.wp_flag='Y'
6346 AND s.latest_eff_published_flag='Y'
6347 AND s.element_version_id=h.wbs_version_id
6348 AND s.element_version_id=ppev.element_version_id
6349 AND ppev.object_type='PA_STRUCTURES'
6350 AND f.plan_version_id=h.plan_version_id
6351 AND r.WP_USAGE_FLAG = 'Y'
6352 AND f.rbs_version_id = -1
6353 AND f.rbs_element_id = -1
6354
6355
6356 GROUP BY
6357 p.project_id,
6358 f.project_element_id,
6359 f.calendar_type,
6360 f.period_type_id,
6361 f.curr_record_type_id,
6362 f.plan_type_id,
6363 f.plan_version_id,
6364 f.rbs_version_id;
6365
6366 l_latest_published_cost NUMBER;
6367 BEGIN
6368 OPEN c2;
6369 FETCH c2 into l_latest_published_cost;
6370 CLOSE c2;
6371
6372 return Nvl(l_latest_published_cost,0);
6373 END GET_TASK_LATEST_PUBLISHED_COST;
6374
6375 FUNCTION GET_TASK_BASELINE_COST
6376 ( p_project_id IN NUMBER, p_task_id in NUMBER) return NUMBER
6377 IS
6378 CURSOR c1 IS
6379
6380 SELECT Sum(f.brdn_cost) BASELINE_COST
6381 FROM
6382 pji_fp_xbs_accum_f f,
6383 pa_projects_all p,
6384 PA_PROJ_ELEM_VER_STRUCTURE ppev,
6385 pji_pjp_wbs_header h
6386 WHERE
6387 p.project_id = p_project_id AND
6388 p.project_id=f.project_id AND
6389 f.project_element_id = p_task_id AND
6390 p.project_id = ppev.project_id AND
6391 p.project_id = h.project_id AND
6392 f.currency_code=p.projfunc_currency_code AND
6393 f.rbs_aggr_level='T' AND
6394 f.period_type_id=32 AND
6395 f.plan_type_id=10 AND
6396 f.prg_rollup_flag='N' AND
6397 ppev.ELEMENT_VERSION_ID = h.wbs_version_id AND
6398 ppev.CURRENT_flag = 'Y' AND --current baseline
6399 h.wp_flag='Y' AND
6400 f.plan_version_id=h.plan_version_id AND
6401 f.rbs_version_id = -1 AND
6402 f.rbs_element_id = -1
6403 GROUP BY f.project_id,f.project_element_id;
6404
6405 l_baseline_cost NUMBER;
6406
6407
6408 BEGIN
6409 OPEN c1;
6410 FETCH c1 into l_baseline_cost;
6411 CLOSE c1;
6412 return l_baseline_cost;
6413 END GET_TASK_BASELINE_COST;
6414
6415 PROCEDURE Derive_Default_CBS_Parameters(
6416 P_PROJECT_ID NUMBER
6417 --,p_plan_version_id NUMBER
6418 , X_CBS_VERSION_ID OUT NOCOPY NUMBER
6419 , x_cbs_element_id OUT NOCOPY NUMBER
6420 , x_return_status IN OUT NOCOPY VARCHAR2
6421 , x_msg_count IN OUT NOCOPY NUMBER
6422 , x_msg_data IN OUT NOCOPY VARCHAR2)
6423 IS
6424 BEGIN
6425
6426 IF G_DEBUG_MODE = 'Y' THEN
6427 Pji_Utils.WRITE2LOG( 'derive_default_cbs_parameters: beginning', TRUE , g_proc);
6428 END IF;
6429
6430
6431 IF x_return_status IS NULL THEN
6432 x_msg_count := 0;
6433 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6434 END IF;
6435
6436 SELECT CBS_VERSION_ID
6437 INTO x_cbs_version_id
6438 FROM
6439 PA_PROJECTS_ALL
6440 WHERE project_id=p_project_id;
6441
6442 IF x_cbs_version_id IS NULL THEN
6443 RAISE NO_DATA_FOUND;
6444 END IF;
6445
6446 SELECT rbs_element_id into x_cbs_element_id
6447 FROM
6448 pa_rbs_elements rbs
6449 WHERE
6450 RBS.RBS_VERSION_ID = X_CBS_VERSION_ID
6451 AND rbs.rbs_level = 1;
6452
6453 IF G_DEBUG_MODE = 'Y' THEN
6454 Pji_Utils.WRITE2LOG( 'derive_default_cbs_parameters: finishing', TRUE , g_proc);
6455 END IF;
6456
6457 EXCEPTION
6458 WHEN NO_DATA_FOUND THEN
6459 x_cbs_version_id := -99;
6460 x_cbs_element_id := -99;
6461 /* x_msg_count := x_msg_count + 1;
6462 x_return_status := Pji_Rep_Util.G_RET_STS_WARNING;
6463 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_VP_NO_RBS_VERSION', p_msg_type=>Pji_Rep_Util.G_RET_STS_WARNING);
6464 */ WHEN OTHERS THEN
6465 x_msg_count := x_msg_count + 1;
6466 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
6467 Add_Message(p_app_short_name=> 'PJI',p_msg_name=> 'PJI_REP_GENERIC_MSG',p_msg_type=>Pji_Rep_Util.G_RET_STS_ERROR,p_token1=>'PROC_NAME',p_token1_value=>'Pji_Rep_Util.Derive_Default_CBS_Parameters');
6468 RAISE;
6469 END DERIVE_DEFAULT_CBS_PARAMETERS;
6470
6471 END Pji_Rep_Util;