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