1 PACKAGE Pji_Rep_Util AUTHID CURRENT_USER AS
2 /* $Header: PJIRX07S.pls 120.21.12020000.2 2013/04/04 21:50:03 sachandr ship $ */
3 G_RET_STS_WARNING VARCHAR2(1):='W';
4 G_RET_STS_ERROR VARCHAR2(1):='E';
5 /* Added new global variable which would be
6 set when context is of Project List page
7 Added for bug 9112216 */
8 G_PROJECT_LIST VARCHAR2(1) := 'N';
9
10 procedure olap_denorm_create (
11 p_element_version_id number ,
12 p_project_id number ) ;
13
14 PROCEDURE Add_Message (p_app_short_name VARCHAR2
15 , p_msg_name VARCHAR2
16 , p_msg_type VARCHAR2
17 , p_token1 VARCHAR2 DEFAULT NULL
18 , p_token1_value VARCHAR2 DEFAULT NULL
19 , p_token2 VARCHAR2 DEFAULT NULL
20 , p_token2_value VARCHAR2 DEFAULT NULL
21 , p_token3 VARCHAR2 DEFAULT NULL
22 , p_token3_value VARCHAR2 DEFAULT NULL
23 , p_token4 VARCHAR2 DEFAULT NULL
24 , p_token4_value VARCHAR2 DEFAULT NULL
25 , p_token5 VARCHAR2 DEFAULT NULL
26 , p_token5_value VARCHAR2 DEFAULT NULL
27 );
28
29 PROCEDURE Log_Struct_Change_Event(p_wbs_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE);
30
31 PROCEDURE Populate_WBS_Hierarchy_Cache(p_project_id NUMBER
32 , p_element_version_id NUMBER
33 , p_prg_flag VARCHAR2 DEFAULT 'N'
34 , p_page_type VARCHAR2 DEFAULT 'WORKPLAN'
35 , p_report_date_julian NUMBER DEFAULT NULL
36 , x_return_status IN OUT NOCOPY VARCHAR2
37 , x_msg_count IN OUT NOCOPY NUMBER
38 , x_msg_data IN OUT NOCOPY VARCHAR2);
39
40 PROCEDURE Populate_WP_Plan_Vers_Cache(p_project_id NUMBER
41 , p_prg_flag VARCHAR2 DEFAULT 'N'
42 , p_current_version_id NUMBER DEFAULT NULL
43 , p_latest_version_id NUMBER DEFAULT NULL
44 , p_baselined_version_id NUMBER DEFAULT NULL
45 , p_plan1_version_id NUMBER DEFAULT NULL
46 , p_plan2_version_id NUMBER DEFAULT NULL
47 , p_curr_wbs_vers_id NUMBER DEFAULT NULL
48 , x_return_status IN OUT NOCOPY VARCHAR2
49 , x_msg_count IN OUT NOCOPY NUMBER
50 , x_msg_data IN OUT NOCOPY VARCHAR2);
51
52 FUNCTION Derive_Prg_Rollup_Flag(
53 p_project_id NUMBER) RETURN VARCHAR2 ;
54
55 FUNCTION Derive_Perf_Prg_Rollup_Flag(
56 p_project_id NUMBER) RETURN VARCHAR2 ;
57
58 FUNCTION get_default_period_name (
59 p_project_id NUMBER) return VARCHAR2;
60
61 FUNCTION get_default_calendar_type
62 return VARCHAR2;
63
64 function Get_Task_Baseline_Cost (
65 p_project_id NUMBER, p_task_id NUMBER) return NUMBER;
66
67 function Get_Task_Latest_Published_Cost(
68 p_project_id NUMBER, p_task_id NUMBER) return NUMBER;
69
70 PROCEDURE Derive_Default_Calendar_Info(
71 p_project_id NUMBER
72 , x_calendar_type OUT NOCOPY VARCHAR2
73 , x_calendar_id OUT NOCOPY NUMBER
74 , x_period_name OUT NOCOPY VARCHAR2
75 , x_report_date_julian OUT NOCOPY NUMBER
76 , x_slice_name OUT NOCOPY VARCHAR2
77 , x_return_status IN OUT NOCOPY VARCHAR2
78 , x_msg_count IN OUT NOCOPY NUMBER
79 , x_msg_data IN OUT NOCOPY VARCHAR2);
80
81 FUNCTION Get_Version_Type(
82 p_project_id NUMBER
83 , p_fin_plan_type_id NUMBER
84 , p_version_type VARCHAR2
85 ) RETURN VARCHAR2 ;
86
87 PROCEDURE Derive_Default_Plan_Versions(
88 p_project_id NUMBER
89 , x_actual_version_id OUT NOCOPY NUMBER
90 , x_cstforecast_version_id OUT NOCOPY NUMBER
91 , x_cstbudget_version_id OUT NOCOPY NUMBER
92 , x_cstbudget2_version_id OUT NOCOPY NUMBER
93 , x_revforecast_version_id OUT NOCOPY NUMBER
94 , x_revbudget_version_id OUT NOCOPY NUMBER
95 , x_revbudget2_version_id OUT NOCOPY NUMBER
96 , x_orig_cstforecast_version_id OUT NOCOPY NUMBER
97 , x_orig_cstbudget_version_id OUT NOCOPY NUMBER
98 , x_orig_cstbudget2_version_id OUT NOCOPY NUMBER
99 , x_orig_revforecast_version_id OUT NOCOPY NUMBER
100 , x_orig_revbudget_version_id OUT NOCOPY NUMBER
101 , x_orig_revbudget2_version_id OUT NOCOPY NUMBER
102 , x_prior_cstfcst_version_id OUT NOCOPY NUMBER
103 , x_prior_revfcst_version_id OUT NOCOPY NUMBER
104 , x_return_status IN OUT NOCOPY VARCHAR2
105 , x_msg_count IN OUT NOCOPY NUMBER
106 , x_msg_data IN OUT NOCOPY VARCHAR2);
107
108 PROCEDURE Derive_Default_Currency_Info(
109 p_project_id NUMBER
110 , x_currency_record_type OUT NOCOPY NUMBER
111 , x_currency_code OUT NOCOPY VARCHAR2
112 , x_currency_type OUT NOCOPY VARCHAR2
113 , x_return_status IN OUT NOCOPY VARCHAR2
114 , x_msg_count IN OUT NOCOPY NUMBER
115 , x_msg_data IN OUT NOCOPY VARCHAR2);
116
117 PROCEDURE Derive_Perf_Currency_Info(
118 p_project_id NUMBER
119 , x_currency_record_type OUT NOCOPY NUMBER
120 , x_currency_code OUT NOCOPY VARCHAR2
121 , x_currency_type OUT NOCOPY VARCHAR2
122 , x_return_status IN OUT NOCOPY VARCHAR2
123 , x_msg_count IN OUT NOCOPY NUMBER
124 , x_msg_data IN OUT NOCOPY VARCHAR2);
125
126 FUNCTION Derive_FactorBy(
127 p_project_id NUMBER
128 , p_fin_plan_version_id NUMBER
129 , x_return_status IN OUT NOCOPY VARCHAR2
130 , x_msg_count IN OUT NOCOPY NUMBER
131 , x_msg_data IN OUT NOCOPY VARCHAR2) RETURN VARCHAR2 ;
132
133 PROCEDURE Derive_Project_Attributes(
134 p_project_id NUMBER
135 , x_return_status IN OUT NOCOPY VARCHAR2
136 , x_msg_count IN OUT NOCOPY NUMBER
137 , x_msg_data IN OUT NOCOPY VARCHAR2);
138
139 PROCEDURE Derive_Default_RBS_Parameters(
140 p_project_id NUMBER
141 ,p_plan_version_id NUMBER
142 , x_rbs_version_id OUT NOCOPY NUMBER
143 , x_rbs_element_id OUT NOCOPY NUMBER
144 , x_return_status IN OUT NOCOPY VARCHAR2
145 , x_msg_count IN OUT NOCOPY NUMBER
146 , x_msg_data IN OUT NOCOPY VARCHAR2);
147
148 PROCEDURE Derive_Perf_RBS_Parameters(
149 p_project_id NUMBER
150 ,p_plan_version_id NUMBER
151 ,p_prg_flag VARCHAR DEFAULT 'N'
152 , x_rbs_version_id OUT NOCOPY NUMBER
153 , x_rbs_element_id OUT NOCOPY NUMBER
154 , x_return_status IN OUT NOCOPY VARCHAR2
155 , x_msg_count IN OUT NOCOPY NUMBER
156 , x_msg_data IN OUT NOCOPY VARCHAR2);
157
158 PROCEDURE Derive_Default_WBS_Parameters(
159 p_project_id NUMBER
160 ,p_plan_version_id NUMBER
161 , x_wbs_version_id OUT NOCOPY NUMBER
162 , x_wbs_element_id OUT NOCOPY NUMBER
163 , x_return_status IN OUT NOCOPY VARCHAR2
164 , x_msg_count IN OUT NOCOPY NUMBER
165 , x_msg_data IN OUT NOCOPY VARCHAR2);
166
167 PROCEDURE Derive_WP_WBS_Parameters(
168 p_project_id NUMBER
169 , x_wbs_version_id OUT NOCOPY NUMBER
170 , x_wbs_element_id OUT NOCOPY NUMBER
171 , x_return_status IN OUT NOCOPY VARCHAR2
172 , x_msg_count IN OUT NOCOPY NUMBER
173 , x_msg_data IN OUT NOCOPY VARCHAR2);
174
175 PROCEDURE Derive_Slice_Name(
176 p_project_id NUMBER
177 , p_calendar_id NUMBER
178 , x_slice_name OUT NOCOPY VARCHAR2
179 , x_return_status IN OUT NOCOPY VARCHAR2
180 , x_msg_count IN OUT NOCOPY NUMBER
181 , x_msg_data IN OUT NOCOPY VARCHAR2);
182
183 FUNCTION Get_Slice_Name(
184 p_project_id NUMBER
185 ,p_calendar_id NUMBER) RETURN VARCHAR2;
186
187 PROCEDURE Derive_Plan_Type_Parameters(
188 p_project_id NUMBER
189 , p_fin_plan_type_id NUMBER
190 , x_plan_pref_code OUT NOCOPY VARCHAR2
191 , x_budget_forecast_flag OUT NOCOPY VARCHAR2
192 , x_plan_type_name OUT NOCOPY VARCHAR2
193 , x_plan_report_mask OUT NOCOPY VARCHAR2
194 , x_plan_margin_mask OUT NOCOPY VARCHAR2
195 , x_cost_app_flag IN OUT NOCOPY VARCHAR2
196 , x_rev_app_flag IN OUT NOCOPY VARCHAR2
197 , x_return_status IN OUT NOCOPY VARCHAR2
198 , x_msg_count IN OUT NOCOPY NUMBER
199 , x_msg_data IN OUT NOCOPY VARCHAR2);
200
201 PROCEDURE Derive_Version_Parameters(
202 p_version_id NUMBER
203 , x_version_name OUT NOCOPY VARCHAR2
204 , x_version_no OUT NOCOPY VARCHAR2
205 , x_version_record_no OUT NOCOPY VARCHAR2
206 , x_budget_status_code OUT NOCOPY VARCHAR2
207 , x_return_status IN OUT NOCOPY VARCHAR2
208 , x_msg_count IN OUT NOCOPY NUMBER
209 , x_msg_data IN OUT NOCOPY VARCHAR2);
210
211 PROCEDURE Derive_Fin_Plan_Versions(p_project_id NUMBER
212 ,p_version_id NUMBER
213 , x_curr_budget_version_id OUT NOCOPY NUMBER
214 , x_orig_budget_version_id OUT NOCOPY NUMBER
215 , x_prior_fcst_version_id OUT NOCOPY NUMBER
216 , x_return_status IN OUT NOCOPY VARCHAR2
217 , x_msg_count IN OUT NOCOPY NUMBER
218 , x_msg_data IN OUT NOCOPY VARCHAR2
219 );
220
221 PROCEDURE Derive_Work_Plan_Versions(p_project_id NUMBER
222 ,p_structure_version_id NUMBER
223 , x_current_version_id OUT NOCOPY NUMBER
224 , x_baselined_version_id OUT NOCOPY NUMBER
225 , x_published_version_id OUT NOCOPY NUMBER
226 , x_return_status IN OUT NOCOPY VARCHAR2
227 , x_msg_count IN OUT NOCOPY NUMBER
228 , x_msg_data IN OUT NOCOPY VARCHAR2
229 );
230
231 /*
232 FUNCTION get_report_date_julian(p_calendar_type VARCHAR2
233 , p_calendar_id NUMBER
234 , p_org_id NUMBER) RETURN NUMBER;
235
236 FUNCTION get_period_name(p_calendar_type VARCHAR2
237 , p_calendar_id NUMBER
238 , p_org_id NUMBER) RETURN VARCHAR2;
239 */
240
241 PROCEDURE Derive_Pa_Calendar_Info(p_project_id NUMBER
242 , p_calendar_type VARCHAR2
243 , x_calendar_id OUT NOCOPY NUMBER
244 , x_report_date_julian OUT NOCOPY NUMBER
245 , x_period_name OUT NOCOPY VARCHAR2
246 , x_slice_name OUT NOCOPY VARCHAR2
247 , x_return_status IN OUT NOCOPY VARCHAR2
248 , x_msg_count IN OUT NOCOPY NUMBER
249 , x_msg_data IN OUT NOCOPY VARCHAR2
250 );
251
252 PROCEDURE Derive_Project_Type(p_project_id NUMBER
253 , x_project_type OUT NOCOPY VARCHAR2
254 , x_return_status IN OUT NOCOPY VARCHAR2
255 , x_msg_count IN OUT NOCOPY NUMBER
256 , x_msg_data IN OUT NOCOPY VARCHAR2
257 );
258
259 --7602538
260 PROCEDURE drv_prf_prd(
261 p_from_date IN VARCHAR2,
262 p_to_date IN VARCHAR2,
263 x_from_period OUT NOCOPY NUMBER,
264 x_to_period OUT NOCOPY NUMBER);
265
266 FUNCTION get_work_plan_actual_version(p_project_id NUMBER
267 ) RETURN NUMBER;
268
269 FUNCTION get_fin_plan_actual_version(p_project_id NUMBER
270 ) RETURN NUMBER;
271
272 FUNCTION get_effort_uom(p_project_id NUMBER
273 ) RETURN NUMBER;
274
275
276 -- -----------------------------------------------------------------
277
278 -- -----------------------------------------------------------------
279 -- Setup Current Reporting Periods
280 -- -----------------------------------------------------------------
281
282 PROCEDURE update_curr_rep_periods(
283 p_pa_curr_rep_period VARCHAR2,
284 p_gl_curr_rep_period VARCHAR2,
285 p_ent_curr_rep_period VARCHAR2
286 );
287
288
289 -- -----------------------------------------------------------------
290
291 PROCEDURE get_project_home_default_param
292 ( p_project_id IN NUMBER,
293 p_page_Type IN VARCHAR2,
294 x_fin_plan_type_id IN OUT NOCOPY NUMBER,
295 x_cost_version_id IN OUT NOCOPY NUMBER,
296 x_rev_version_id IN OUT NOCOPY NUMBER,
297 x_struct_version_id IN OUT NOCOPY NUMBER,
298 x_return_status IN OUT NOCOPY VARCHAR2,
299 x_msg_count IN OUT NOCOPY NUMBER,
300 x_msg_data IN OUT NOCOPY VARCHAR2);
301
302 PROCEDURE Derive_Default_RBS_Element_Id(
303 p_rbs_version_id NUMBER
304 , x_rbs_element_id OUT NOCOPY NUMBER
305 , x_return_status IN OUT NOCOPY VARCHAR2
306 , x_msg_count IN OUT NOCOPY NUMBER
307 , x_msg_data IN OUT NOCOPY VARCHAR2);
308
309
310 PROCEDURE Derive_VP_Calendar_Info(
311 p_project_id NUMBER
312 , p_cst_version_id NUMBER
313 , p_rev_version_id NUMBER
314 , p_context_version_type VARCHAR2
315 , x_calendar_id OUT NOCOPY NUMBER
316 , x_calendar_type OUT NOCOPY VARCHAR2
317 , x_time_phase_valid_flag OUT NOCOPY VARCHAR2
318 , x_return_status IN OUT NOCOPY VARCHAR2
319 , x_msg_count IN OUT NOCOPY NUMBER
320 , x_msg_data IN OUT NOCOPY VARCHAR2);
321
322 PROCEDURE Derive_WP_Calendar_Info(
323 p_project_id NUMBER
324 , p_plan_version_id NUMBER
325 , x_calendar_id OUT NOCOPY NUMBER
326 , x_calendar_type OUT NOCOPY VARCHAR2
327 , x_return_status IN OUT NOCOPY VARCHAR2
328 , x_msg_count IN OUT NOCOPY NUMBER
329 , x_msg_data IN OUT NOCOPY VARCHAR2);
330
331 PROCEDURE Derive_WP_Period(
332 p_project_id NUMBER
333 , p_published_version_id NUMBER
334 , p_working_version_id NUMBER
335 , x_from_period OUT NOCOPY NUMBER
336 , x_to_period OUT NOCOPY NUMBER
337 , x_return_status IN OUT NOCOPY VARCHAR2
338 , x_msg_count IN OUT NOCOPY NUMBER
339 , x_msg_data IN OUT NOCOPY VARCHAR2);
340
341 PROCEDURE Derive_VP_Period(
342 p_project_id NUMBER
343 , p_plan_version_id_tbl SYSTEM.pa_num_tbl_type
344 , x_from_period OUT NOCOPY NUMBER
345 , x_to_period OUT NOCOPY NUMBER
346 , x_return_status IN OUT NOCOPY VARCHAR2
347 , x_msg_count IN OUT NOCOPY NUMBER
348 , x_msg_data IN OUT NOCOPY VARCHAR2);
349
350 PROCEDURE Derive_Perf_Period(
351 p_project_id NUMBER
352 , p_plan_version_id_tbl SYSTEM.pa_num_tbl_type
353 , x_from_period OUT NOCOPY NUMBER
354 , x_to_period OUT NOCOPY NUMBER
355 , x_return_status IN OUT NOCOPY VARCHAR2
356 , x_msg_count IN OUT NOCOPY NUMBER
357 , x_msg_data IN OUT NOCOPY VARCHAR2);
358
359 /*
360 * Added this procedure for bug 3842347. This procedure will give max and min date for a given plan version
361 * id and proejct_id. It is being used in get default api
362 */
363 PROCEDURE Get_Default_Period_Dates (
364 p_plan_version_ids IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type()
365 , p_project_id IN NUMBER
366 , x_min_julian_date OUT NOCOPY NUMBER
367 , x_max_julian_date OUT NOCOPY NUMBER);
368
369
370 PROCEDURE Derive_Version_Margin_Mask(
371 p_project_id NUMBER
372 , p_plan_version_id NUMBER
373 , x_plan_margin_mask OUT NOCOPY VARCHAR2
374 , x_return_status IN OUT NOCOPY VARCHAR2
375 , x_msg_count IN OUT NOCOPY NUMBER
376 , x_msg_data IN OUT NOCOPY VARCHAR2);
377
378
379 PROCEDURE Derive_Percent_Complete
380 ( p_project_id NUMBER
381 , p_wbs_version_id NUMBER
382 , p_wbs_element_id NUMBER
383 , p_rollup_flag VARCHAR2
384 , p_report_date_julian NUMBER
385 , p_structure_type VARCHAR2
386 , p_calendar_type VARCHAR2 DEFAULT 'E'
387 , p_calendar_id NUMBER DEFAULT -1
388 , p_prg_flag VARCHAR2
389 , x_percent_complete OUT NOCOPY NUMBER
390 , x_return_status IN OUT NOCOPY VARCHAR2
391 , x_msg_count IN OUT NOCOPY NUMBER
392 , x_msg_data IN OUT NOCOPY VARCHAR2
393 )
394 ;
395
396 PROCEDURE Check_Cross_Org
397 ( p_project_id NUMBER
398 , x_cross_org_flag OUT NOCOPY VARCHAR2
399 , x_return_status IN OUT NOCOPY VARCHAR2
400 , x_msg_count IN OUT NOCOPY NUMBER
401 , x_msg_data IN OUT NOCOPY VARCHAR2
402 );
403
404 /*
405 This is a wrapper API which does the consistency check
406 for program in workplan context.
407 */
408 PROCEDURE CHECK_WP_PARAM_CONSISTENCY
409 ( p_project_id IN pa_projects_all.project_id%TYPE
410 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
411 ,p_margin_code IN pa_proj_fp_options.margin_derived_from_code%TYPE
412 ,p_published_flag IN VARCHAR2
413 ,p_calendar_type IN pji_fp_xbs_accum_f.calendar_type%TYPE
414 ,p_calendar_id IN pa_projects_all.calendar_id%TYPE
415 ,p_rbs_version_id IN pa_proj_fp_options.rbs_version_id%TYPE
416 ,x_pc_flag OUT NOCOPY VARCHAR2
417 ,x_pfc_flag OUT NOCOPY VARCHAR2
418 ,x_margin_flag OUT NOCOPY VARCHAR2
419 ,x_workpub_flag OUT NOCOPY VARCHAR2
420 ,x_time_phase_flag OUT NOCOPY VARCHAR2
421 ,x_rbs_flag OUT NOCOPY VARCHAR2
422 ,x_return_status OUT NOCOPY VARCHAR2
423 ,x_msg_count OUT NOCOPY NUMBER
424 ,x_msg_data OUT NOCOPY VARCHAR2);
425
426 /*
427 check if all the projects in the program hierarchy contain
428 the same project and project functional currency.
429 */
430 PROCEDURE CHECK_WP_CURRENCY_CONSISTENCY
431 ( p_project_id IN pa_projects_all.project_id%TYPE
432 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
433 ,x_pc_flag OUT NOCOPY VARCHAR2
434 ,x_pfc_flag OUT NOCOPY VARCHAR2
435 ,x_return_status OUT NOCOPY VARCHAR2
436 ,x_msg_count OUT NOCOPY NUMBER
437 ,x_msg_data OUT NOCOPY VARCHAR2);
438
439 /*
440 check if all the linked structure versions in the
441 program hierarchy have the same margin mask.
442 */
443 PROCEDURE CHECK_WP_MARGIN_CONSISTENCY
444 ( p_project_id IN pa_projects_all.project_id%TYPE
445 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
446 ,p_margin_code IN pa_proj_fp_options.margin_derived_from_code%TYPE
447 ,x_margin_flag OUT NOCOPY VARCHAR2
448 ,x_return_status OUT NOCOPY VARCHAR2
449 ,x_msg_count OUT NOCOPY NUMBER
450 ,x_msg_data OUT NOCOPY VARCHAR2);
451
452 /*
453 check if all the structure versions in the program hierarchy
454 have the same status. ie published/not published.
455 */
456 PROCEDURE CHECK_WP_STATUS_CONSISTENCY
457 ( p_project_id IN pa_projects_all.project_id%TYPE
458 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
459 ,p_published_flag IN VARCHAR2
460 ,x_workpub_flag OUT NOCOPY VARCHAR2
461 ,x_return_status OUT NOCOPY VARCHAR2
462 ,x_msg_count OUT NOCOPY NUMBER
463 ,x_msg_data OUT NOCOPY VARCHAR2);
464
465 /*
466 check if all the structure versions in the program hierarchy have
467 same time phasing.
468 */
469 PROCEDURE CHECK_WP_TIME_CONSISTENCY
470 ( p_project_id IN pa_projects_all.project_id%TYPE
471 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
472 ,p_calendar_type IN pji_fp_xbs_accum_f.calendar_type%TYPE
473 ,p_calendar_id IN pa_projects_all.calendar_id%TYPE
474 ,x_time_phase_flag OUT NOCOPY VARCHAR2
475 ,x_return_status OUT NOCOPY VARCHAR2
476 ,x_msg_count OUT NOCOPY NUMBER
477 ,x_msg_data OUT NOCOPY VARCHAR2);
478
479 /*
480 check if all the structure versions in the program hierarchy have
481 the same RBS.
482 */
483 PROCEDURE CHECK_WP_RBS_CONSISTENCY
484 ( p_project_id IN pa_projects_all.project_id%TYPE
485 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
486 ,p_rbs_version_id IN pa_proj_fp_options.rbs_version_id%TYPE
487 ,x_rbs_flag OUT NOCOPY VARCHAR2
488 ,x_return_status OUT NOCOPY VARCHAR2
489 ,x_msg_count OUT NOCOPY NUMBER
490 ,x_msg_data OUT NOCOPY VARCHAR2);
491
492 FUNCTION GET_WP_BASELINED_PLAN_VERSION
493 ( p_project_id IN pa_projects_all.project_id%TYPE)
494 RETURN NUMBER;
495
496 FUNCTION GET_WP_LATEST_VERSION
497 ( p_project_id IN pa_projects_all.project_id%TYPE)
498 RETURN NUMBER;
499
500 FUNCTION GET_DEFAULT_EXPANSION_LEVEL
501 ( p_project_id IN pa_projects_all.project_id%TYPE
502 ,p_object_type IN VARCHAR2) -- Task/Resource indicated by 'T' or 'R'
503 RETURN NUMBER;
504
505 /* Gets all the default plan type ids for a give project */
506 PROCEDURE Derive_Default_Plan_Type_Ids(
507 p_project_id NUMBER
508 , x_cost_fcst_plan_type_id OUT NOCOPY NUMBER
509 , x_cost_bgt_plan_type_id OUT NOCOPY NUMBER
510 , x_cost_bgt2_plan_type_id OUT NOCOPY NUMBER
511 , x_rev_fcst_plan_type_id OUT NOCOPY NUMBER
512 , x_rev_bgt_plan_type_id OUT NOCOPY NUMBER
513 , x_rev_bgt2_plan_type_id OUT NOCOPY NUMBER
514 , x_return_status IN OUT NOCOPY VARCHAR2
515 , x_msg_count IN OUT NOCOPY NUMBER
516 , x_msg_data IN OUT NOCOPY VARCHAR2);
517
518
519 /*
520 ** Get all plan versions for a given project and plan type id
521 */
522 PROCEDURE Derive_Plan_Version_Ids(
523 p_project_id IN NUMBER
524 , p_cost_fcst_plan_type_id IN NUMBER
525 , p_cost_bgt_plan_type_id IN NUMBER
526 , p_cost_bgt2_plan_type_id IN NUMBER
527 , p_rev_fcst_plan_type_id IN NUMBER
528 , p_rev_bgt_plan_type_id IN NUMBER
529 , p_rev_bgt2_plan_type_id IN NUMBER
530 , x_cstforecast_version_id OUT NOCOPY NUMBER
531 , x_cstbudget_version_id OUT NOCOPY NUMBER
532 , x_cstbudget2_version_id OUT NOCOPY NUMBER
533 , x_revforecast_version_id OUT NOCOPY NUMBER
534 , x_revbudget_version_id OUT NOCOPY NUMBER
535 , x_revbudget2_version_id OUT NOCOPY NUMBER
536 , x_orig_cstbudget_version_id OUT NOCOPY NUMBER
537 , x_orig_cstbudget2_version_id OUT NOCOPY NUMBER
538 , x_orig_revbudget_version_id OUT NOCOPY NUMBER
539 , x_orig_revbudget2_version_id OUT NOCOPY NUMBER
540 , x_prior_cstfcst_version_id OUT NOCOPY NUMBER
541 , x_prior_revfcst_version_id OUT NOCOPY NUMBER
542 , x_return_status IN OUT NOCOPY VARCHAR2
543 , x_msg_count IN OUT NOCOPY NUMBER
544 , x_msg_data IN OUT NOCOPY VARCHAR2);
545
546
547 /*
548 * This api checks for each project in this passed program whether they
549 * have the same GL or same PA calendar or not. If GL calendar is same then x_gl_flag
550 * will return 'T' else 'F'. This logic is true for PA calendar also.
551 */
552 PROCEDURE Check_Perf_Cal_Consistency(
553 p_project_id IN pa_projects_all.project_id%TYPE
554 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
555 ,x_gl_flag OUT NOCOPY VARCHAR2
556 ,x_pa_flag OUT NOCOPY VARCHAR2
557 ,x_return_status OUT NOCOPY VARCHAR2
558 ,x_msg_count OUT NOCOPY NUMBER
559 ,x_msg_data OUT NOCOPY VARCHAR2);
560
561 /*
562 * This api checks for each project in this passed program whether they
563 * have the same Project Functional Currency (PFC) or not. If PFC is same then x_pfc_flag
564 * will return 'T' else 'F'.
565 */
566 PROCEDURE Check_Perf_Curr_Consistency(
567 p_project_id IN pa_projects_all.project_id%TYPE
568 ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
569 ,x_pfc_flag OUT NOCOPY VARCHAR2
570 ,x_return_status OUT NOCOPY VARCHAR2
571 ,x_msg_count OUT NOCOPY NUMBER
572 ,x_msg_data OUT NOCOPY VARCHAR2);
573
574 /* Adding this procedure to do addition calculation with Null rules
575 * for bug 4194804. Please do not add out parameters because this
576 * function is also used as select function in VO.xml also
577 */
578 FUNCTION Measures_Total(
579 p_measure1 IN NUMBER
580 , p_measure2 IN NUMBER DEFAULT NULL
581 , p_measure3 IN NUMBER DEFAULT NULL
582 , p_measure4 IN NUMBER DEFAULT NULL
583 , p_measure5 IN NUMBER DEFAULT NULL
584 , p_measure6 IN NUMBER DEFAULT NULL
585 , p_measure7 IN NUMBER DEFAULT NULL
586 ) RETURN NUMBER;
587
588 /* Checks if the smart slice api has been called or not.
589 If it is called then no need to call processing page,
590 but if it is not called then call the api and launch
591 the processing page. But if the processing is Deferred
592 then launch concurrent program */
593 PROCEDURE Is_Smart_Slice_Created(
594 p_rbs_version_id IN NUMBER,
595 p_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
596 p_wbs_element_id IN NUMBER,
597 p_rbs_element_id IN NUMBER,
598 p_prg_rollup_flag IN VARCHAR2,
599 p_curr_record_type_id IN NUMBER,
600 p_calendar_type IN VARCHAR2,
601 p_wbs_version_id IN NUMBER,
602 p_commit IN VARCHAR2 := 'Y',
603 p_project_id IN NUMBER, -- Added for bug 4419342
604 x_Smart_Slice_Flag OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2,
607 x_return_status OUT NOCOPY VARCHAR2);
608 /*
609 This procedure checks if the passed plan versions
610 are having same RBS or not. It returns two valid
611 values:
612 'Y':- The passed Plan versions having same RBS
613 'N':- The passed Plan version do not have same RBS
614 Assumptions:
615 + RBS is attached with context plan version
616 + Additional Plan versions are selected
617 */
618 PROCEDURE Chk_plan_vers_have_same_RBS(
619 p_fin_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
620 x_R_PlanVers_HavSame_RBS_Flag OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2,
623 x_return_status OUT NOCOPY VARCHAR2);
624
625
626 PROCEDURE GET_PROCESS_STATUS_MSG(
627 p_project_id IN pa_projects_all.project_id%TYPE
628 , p_structure_type IN pa_structure_types.structure_type%TYPE := NULL
629 , p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE := NULL
630 , p_prg_flag IN VARCHAR2 := NULL
631 , x_message_name OUT NOCOPY VARCHAR2
632 , x_message_type OUT NOCOPY VARCHAR2
633 , x_structure_version_id OUT NOCOPY NUMBER
634 , x_conc_request_id OUT NOCOPY NUMBER
635 , x_return_status IN OUT NOCOPY VARCHAR2
636 , x_msg_count IN OUT NOCOPY NUMBER
637 , x_msg_data IN OUT NOCOPY VARCHAR2);
638
639
640 PROCEDURE CHECK_PROJ_TYPE_CONSISTENCY
641 ( p_project_id IN NUMBER
642 ,p_wbs_version_id IN NUMBER
643 ,p_structure_type IN VARCHAR2 DEFAULT 'FINANCIAL'
644 ,x_ptc_flag OUT NOCOPY VARCHAR2 -- project type consistency flag
645 ,x_return_status OUT NOCOPY VARCHAR2
646 ,x_msg_count OUT NOCOPY NUMBER
647 ,x_msg_data OUT NOCOPY VARCHAR2);
648
649 PROCEDURE Derive_Pji_Calendar_Info(
650 p_project_id IN NUMBER
651 , p_period_type IN VARCHAR2
652 , p_as_of_date IN NUMBER
653 , x_calendar_type IN OUT NOCOPY VARCHAR2
654 , x_calendar_id OUT NOCOPY NUMBER
655 , x_period_name OUT NOCOPY VARCHAR2
656 , x_report_date_julian OUT NOCOPY NUMBER
657 , x_slice_name OUT NOCOPY VARCHAR2
658 , x_return_status IN OUT NOCOPY VARCHAR2
659 , x_msg_count IN OUT NOCOPY NUMBER
660 , x_msg_data IN OUT NOCOPY VARCHAR2);
661
662 PROCEDURE Derive_Pji_Currency_Info(
663 p_project_id NUMBER
664 , p_currency_record_type IN VARCHAR2
665 , x_currency_record_type OUT NOCOPY NUMBER
666 , x_currency_code OUT NOCOPY VARCHAR2
667 , x_currency_type OUT NOCOPY VARCHAR2
668 , x_return_status IN OUT NOCOPY VARCHAR2
669 , x_msg_count IN OUT NOCOPY NUMBER
670 , x_msg_data IN OUT NOCOPY VARCHAR2);
671
672 PROCEDURE Validate_Plan_Type(p_project_id NUMBER
673 , p_plan_type_id NUMBER
674 , x_plan_type_id IN OUT NOCOPY NUMBER
675 , x_return_status IN OUT NOCOPY VARCHAR2
676 , x_msg_count IN OUT NOCOPY NUMBER
677 , x_msg_data IN OUT NOCOPY VARCHAR2);
678
679 FUNCTION is_str_linked_to_working_ver
680 (p_project_id NUMBER
681 , p_structure_version_id NUMBER
682 , p_relationship_type VARCHAR2 := 'LW') return VARCHAR2;
683
684 FUNCTION Get_Page_Pers_Function_Name
685 (p_project_type VARCHAR2
686 ,p_page_type VARCHAR2) return VARCHAR2;
687
688 END Pji_Rep_Util;
[Home] [Help]