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