4 --The following global varables are added for as_of_date APIs
1 package PA_PROGRESS_UTILS AUTHID CURRENT_USER as
2 /* $Header: PAPCUTLS.pls 120.17.12020000.5 2013/06/06 09:28:47 djambhek ship $ */
3
5 previous_record_count NUMBER(15) := 0;
6 next_record_count NUMBER(15) := 0;
7 record_count NUMBER(15) := 0;
8 x_bill_thru_date DATE;
9 project_id NUMBER := 0;
10 previous_record_index NUMBER := 0;
11 current_index NUMBER := 0;
12 previous_return_date DATE := sysdate - 10;
13 l_last_progress_date DATE;
14 l_Last_Bill_Thru_Date DATE;
15 l_return_date PA_VC_1000_10 := PA_VC_1000_10(1000);
16 i NUMBER := 0;
17
18 X_project_start_date DATE;
19 X_project_finish_date DATE;
20 G_prog_as_of_date DATE := sysdate;
21 G_bac_value_project_id NUMBER := 0; -- FPM Dev CR 3
22
23 -- 4535784 Begin
24 j_task NUMBER := 0;
25 g_task_id NUMBER := 0;
26 g_max_rollup_dt DATE;
27 -- 4535784 End
28 --
29
30 -- Bug 7633088
31 g_override_as_of_date DATE := NULL;
32
33 FUNCTION GET_LATEST_TASK_VER_ID (p_project_id IN NUMBER,
37 p_object_type IN VARCHAR2
34 p_task_id IN NUMBER) return NUMBER;
35
36 FUNCTION PROGRESS_RECORD_EXISTS (p_element_version_id IN NUMBER,
38 ,p_project_id IN NUMBER -- Fixed bug # 3688901.
39 ) return VARCHAR2;
40
41 FUNCTION GET_LATEST_STRUCTURE_VER_ID (p_project_id IN NUMBER) return NUMBER;
42
43 FUNCTION isUserProjectManager(p_user_id IN NUMBER,
44 p_project_id IN NUMBER) return VARCHAR2;
45
46 FUNCTION Get_Working_Progress_Id(p_project_id IN NUMBER,
47 p_task_id IN NUMBER) return NUMBER;
48
49 PROCEDURE UPDATE_TASK_PROG_REQ_DATE(p_commit in varchar2 := FND_API.G_TRUE,
50 p_object_id in number,
51 p_object_type in varchar2,
52 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
53 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
54 x_msg_data out NOCOPY varchar2); --File.Sql.39 bug 4440895
55 PROCEDURE adjust_reminder_date(
56 p_commit IN VARCHAR2 := FND_API.G_TRUE
57 ,p_project_id IN NUMBER
58 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
59 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
60 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
61
62 FUNCTION PROJ_TASK_PROG_EXISTS(p_project_id IN NUMBER,
63 p_task_id IN NUMBER) return VARCHAR2;
64
65 FUNCTION GET_PRIOR_PERCENT_COMPLETE(p_project_id IN NUMBER,
66 p_task_id IN NUMBER,
67 p_as_of_date IN DATE) return NUMBER;
68
69
70 FUNCTION GET_LATEST_AS_OF_DATE(
71 p_task_id NUMBER
72 ,p_project_id NUMBER := null -- FPM Development Bug 3420093
73 ,p_object_id NUMBER := null -- FPM Development Bug 3420093
74 ,p_object_type VARCHAR2 := 'PA_TASKS'-- FPM Development Bug 3420093
75 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
76 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2
77 ) RETURN DATE;
78
79
80
81
82 function Get_AS_OF_DATE (
83 X_Project_ID IN Number,
84 X_project_start_date IN Date default NULL,
85 X_Billing_Cycle_ID IN Number default NULL,
86 X_Billing_Offset_Days IN Number default NULL,
87 X_Bill_Thru_Date IN Date default NULL,
88 X_Last_Bill_Thru_Date IN Date default NULL
89 ) RETURN DATE;
90
91
92 FUNCTION as_of_date(
93 X_Project_ID IN NUMBER ,
94 x_object_id IN NUMBER ,
95 X_Billing_Cycle_ID IN NUMBER DEFAULT NULL ,
96 X_Object_type IN VARCHAR2 DEFAULT 'PA_TASKS' , -- FPM Development Bug 3420093
97 X_structure_type IN VARCHAR2 DEFAULT 'WORKPLAN' ,-- FPM Development Bug 3420093
98 X_proj_element_id IN NUMBER := null /* Amit : Modified for IB4 Progress CR. */
99 ) RETURN DATE;
100
101 FUNCTION get_next_ppc_id RETURN NUMBER;
102
103 -- FPM Development Bug 3420093 : Added p_object_type
104 FUNCTION CHECK_VALID_AS_OF_DATE(p_as_of_date IN DATE, p_project_id IN NUMBER, p_object_id NUMBER, p_object_type VARCHAR2 := 'PA_TASKS' ,p_cbs_element_id NUMBER := null ,
105 p_proj_element_id IN NUMBER := null /* Amit : Modified for IB4 Progress CR. */ )
106 RETURN VARCHAR2;
107
108 FUNCTION Calc_base_percent(
109 p_task_id NUMBER,
110 p_incr_work_qty NUMBER,
111 p_cuml_work_qty NUMBER,
112 p_est_remaining_effort NUMBER
113 ) RETURN NUMBER;
114
115 -- 4392189 Phase 2: This method is not used anywhere
116 /*
117 PROCEDURE get_rollup_attrs(
118 p_task_id NUMBER,
119 p_as_of_date DATE,
120 x_EFF_ROLLUP_PROG_STAT_CODE OUT VARCHAR2,
121 x_EFF_ROLLUP_PROG_STAT_NAME OUT VARCHAR2,
122 x_ESTIMATED_REMAINING_EFFORT OUT NUMBER,
123 x_BASE_PERCENT_COMPLETE OUT NUMBER,
124 x_EFF_ROLLUP_PERCENT_COMP OUT NUMBER,
125 x_ESTIMATED_START_DATE OUT DATE,
126 x_ESTIMATED_FINISH_DATE OUT DATE,
127 x_ACTUAL_START_DATE OUT DATE,
128 x_ACTUAL_FINISH_DATE OUT DATE,
129 x_status_icon_ind OUT VARCHAR2,
130 x_status_icon_active_ind OUT VARCHAR2
131 );
132 */
133
134 FUNCTION get_next_progress_cycle(
135 p_project_id NUMBER,
136 p_task_id NUMBER,
137 p_object_id NUMBER := null, -- FPM Development Bug 3420093
138 p_object_type VARCHAR2 := 'PA_TASKS', -- FPM Development Bug 3420093
139 p_structure_type VARCHAR2 := 'WORKPLAN', -- FPM Development Bug 3420093
140 p_start_date DATE := to_date(null) -- FPM Development Bug 3420093
141 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
142 ) RETURN DATE;
143
144 FUNCTION get_prog_dt_closest_to_sys_dt(
145 p_project_id NUMBER,
146 p_task_id NUMBER,
150 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
147 p_object_id NUMBER := null, -- FPM Development Bug 3420093
148 p_object_type VARCHAR2 := 'PA_TASKS', -- FPM Development Bug 3420093
149 p_structure_type VARCHAR2 := 'WORKPLAN'
151 ) RETURN DATE ;
152
153 FUNCTION check_prog_exists_on_aod(
154 p_project_id NUMBER,
155 p_object_type VARCHAR2,
156 p_object_version_id NUMBER,
157 p_task_id NUMBER := null/* Amit : Modified for IB4 Progress CR. */ ,
158 p_as_of_date DATE,
159 p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
160 ,p_object_id NUMBER := null /* Modified for IB4 Progress CR. */
161 ,p_cbs_element_id NUMBER := NULL --Added for 16200605
162 ) RETURN VARCHAR2;
163
164 FUNCTION get_ppc_id(
165 p_project_id NUMBER
166 ,p_object_id NUMBER
167 ,p_object_type VARCHAR2
168 ,p_object_version_id NUMBER
169 ,p_as_of_date DATE
170 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
171 ,p_task_id NUMBER := null /* Modified for IB4 Progress CR. */
172 ,p_cbs_element_id NUMBER := NULL --Added for 16200605
173
174 ) RETURN NUMBER;
175
176 FUNCTION get_prog_rollup_id(
177 p_project_id NUMBER
178 ,p_object_id NUMBER
179 ,p_object_type VARCHAR2
180 ,p_object_version_id NUMBER
181 ,p_as_of_date DATE
182 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
183 ,p_structure_version_id NUMBER := null -- FPM Development Bug 3420093
184 ,x_record_version_number OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
185 ,p_proj_element_id NUMBER := null /* Modified for IB4 Progress CR. */
186 ,p_action VARCHAR2 := 'PUBLISH' -- Bug 3879461
187 ,p_cbs_element_id NUMBER := NULL --Added for 16200605
188 ) RETURN NUMBER;
189
190 FUNCTION check_task_has_progress(
191 p_task_id NUMBER ) RETURN VARCHAR2;
192
193 FUNCTION get_last_cumulative(
194 p_project_id NUMBER
195 ,p_object_id NUMBER
196 ,p_object_type VARCHAR2
197 ,p_as_of_date DATE ) RETURN NUMBER;
198
199
200 FUNCTION get_planned_wq(
201 p_project_id NUMBER
202 ,p_object_id NUMBER
203 ,p_object_version_id NUMBER ) RETURN NUMBER;
204
205 PROCEDURE clear_prog_outdated_flag(
206 p_project_id NUMBER
207 ,p_object_id NUMBER
208 ,p_object_type VARCHAR2
209 ,p_structure_version_id NUMBER default null --bug 3851528
210 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
211 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
212 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
213 );
214
215 -- FPM Development Bug 3420093
216 PROCEDURE get_project_progress_defaults(
217 p_project_id NUMBER
218 ,p_structure_type IN VARCHAR2
219 ,x_WQ_ENABLED_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
220 ,x_EFFORT_ENABLED_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
221 ,x_PERCENT_COMP_ENABLED_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
222 ,x_task_weight_basis_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
223 ,X_ALLOW_COLLAB_PROG_ENTRY OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
224 ,X_ALLW_PHY_PRCNT_CMP_OVERRIDES OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
225 );
226
227 PROCEDURE get_progress_defaults(
228 p_project_id NUMBER
229 ,p_object_version_id NUMBER
230 ,p_object_type VARCHAR2
231 ,p_object_id NUMBER
232 ,p_as_of_date DATE
233 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
234 ,x_WQ_ACTUAL_ENTRY_CODE OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
235 ,x_WQ_ENABLED_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
236 ,x_EFFORT_ENABLED_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
237 ,x_BASE_PERCENT_COMP_DERIV_CODE OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
238 ,x_PERCENT_COMP_ENABLED_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
239 ,X_PROGRESS_ENTRY_ENABLE_FLAG OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
240 ,X_ALLOW_COLLAB_PROG_ENTRY OUT NOCOPY VARCHAR2 -- FPM Development Bug 3420093 --File.Sql.39 bug 4440895
241 ,X_ALLW_PHY_PRCNT_CMP_OVERRIDES OUT NOCOPY VARCHAR2 -- FPM Development Bug 3420093 --File.Sql.39 bug 4440895
242 ,x_task_weight_basis_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
243 );
244
245 FUNCTION chk_prg_since_last_prg(
246 p_project_id NUMBER
247 ,p_percent_complete_id NUMBER
248 ,p_object_type VARCHAR2
249 ,p_object_id NUMBER
250 ,p_task_id NUMBER := null /* Modified for IB4 Progress CR. */
251 ) RETURN VARCHAR2;
252
253 FUNCTION check_project_has_progress(
254 p_project_id NUMBER,
255 p_object_id NUMBER,
256 p_structure_type VARCHAR2 := null ) RETURN VARCHAR2; -- added by kmaddi for bug 6914708
257
258 FUNCTION get_task_prog_profile(
259 p_profile_name VARCHAR2 ) RETURN VARCHAR2;
260
261 FUNCTION Working_version_exist(
262 p_task_id NUMBER := null /* Amit : Modified for IB4 Progress CR. */
263 ,p_project_id NUMBER
264 ,p_object_type VARCHAR2
265 ,p_object_id NUMBER := null /* Modified for IB4 Progress CR. */
266 ,p_as_of_date DATE := null -- bug 4185364
267 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
268 ) RETURN DATE;
269
270 FUNCTION check_status_referenced(
271 p_status_code VARCHAR2 ) RETURN BOOLEAN;
275 ,p_as_of_date DATE ) RETURN DATE;
272
273 FUNCTION GET_LATEST_AS_OF_DATE2(
274 p_task_id NUMBER
276
277 FUNCTION is_parent_on_hold(
278 p_object_version_id NUMBER
279 ) RETURN VARCHAR2;
280
281 FUNCTION get_task_status(
282 p_project_id NUMBER
283 ,p_object_id NUMBER
284 , p_object_type VARCHAR2 := 'PA_TASKS' -- FPM Development Bug 3420093
285 ) RETURN VARCHAR2;
286
287 FUNCTION get_system_task_status(
288 p_status_code VARCHAR2
289 ,p_object_type VARCHAR2 := 'PA_TASKS'
290 ) RETURN VARCHAR2;
291
292 Function is_cycle_ok_to_delete(p_progress_cycle_id IN NUMBER) return VARCHAR2;
293
294 function get_max_ppc_id(p_project_id IN NUMBER,
295 p_object_id IN NUMBER,
296 p_object_type IN VARCHAR2,
297 p_as_of_date IN DATE) return number;
298
299 function get_max_rollup_asofdate(p_project_id IN NUMBER,
300 p_object_id IN NUMBER,
301 p_object_type IN VARCHAR2,
302 p_as_of_date IN DATE,
303 p_object_version_id IN NUMBER,
304 p_structure_type IN VARCHAR2 := 'WORKPLAN', -- FPM Dev CR 3
305 p_structure_version_id NUMBER := NULL -- FPM Dev CR 4
306 ,p_proj_element_id NUMBER := null /* Modified for IB4 Progress CR. */
307 ) return date;
308
309 function get_project_wq_flag(p_project_id IN NUMBER) return varchar2;
310
311 PROCEDURE copy_attachments (
312 p_project_id IN NUMBER,
313 p_object_id IN NUMBER,
314 p_object_type IN VARCHAR2,
315 p_from_pc_id IN NUMBER,
316 p_to_pc_id IN NUMBER,
317 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
318 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
319 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
320 );
321
322 function is_task_manager (p_task_id IN NUMBER,
323 p_project_id IN NUMBER,
324 p_user_id IN NUMBER) return varchar2;
325
326 --Bug 3010538 : New API for the Task Weighting Enhancement.
327 FUNCTION GET_TASK_WEIGHTING_BASIS(
328 p_project_id IN pa_projects_all.project_id%TYPE
329 , p_structure_type IN VARCHAR2 := 'WORKPLAN' -- FPM Dev CR 3
330 )
331 return VARCHAR2;
332
333 -- Progress Management Changes. Bug # 3420093.
334
335 FUNCTION is_object_progressable(p_project_id IN NUMBER
336 ,p_proj_element_id IN NUMBER
337 ,p_object_id IN NUMBER
338 ,p_object_type IN VARCHAR2) return VARCHAR2;
339
340 FUNCTION check_wp_working_prog_exists(p_project_id IN NUMBER
341 ,p_structure_version_id IN NUMBER
342 ) return VARCHAR2;
343
344 FUNCTION is_pc_override_allowed(p_project_id IN NUMBER
345 ,p_structure_type IN VARCHAR2 := 'WORKPLAN'
346 ) return VARCHAR2;
347
348
349 FUNCTION calculate_percentage( p_actual_value NUMBER
350 ,p_planned_value NUMBER ) return NUMBER;
351
352
353 FUNCTION GET_EARLIEST_AS_OF_DATE(
354 p_project_id NUMBER
355 ,p_object_id NUMBER
356 ,p_object_type VARCHAR2
357 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
358 ,p_task_id NUMBER := null /* Modified for IB4 Progress CR. */
359 ) RETURN DATE;
360
361 FUNCTION check_assignment_exists(
362 p_project_id NUMBER
363 ,p_object_version_id NUMBER
364 ,p_object_type VARCHAR2
365 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
366 ) RETURN VARCHAR2;
367
368 FUNCTION get_last_effort(
369 p_project_id NUMBER
370 ,p_object_id NUMBER
371 ,p_object_type VARCHAR2
372 ,p_as_of_date DATE
373 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
374 ,p_proj_element_id NUMBER := null /* Modified for IB4 Progress CR. */
375 ) RETURN NUMBER;
376
377 FUNCTION get_last_cost(
378 p_project_id NUMBER
379 ,p_object_id NUMBER
380 ,p_object_type VARCHAR2
381 ,p_as_of_date DATE
382 ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
383 ,p_proj_element_id NUMBER := null /* Modified for IB4 Progress CR. */
384 ) RETURN NUMBER;
385
386
387 PROCEDURE convert_currency_amounts(
388 p_api_version IN NUMBER :=1.0
389 ,p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
390 ,p_commit IN VARCHAR2 :=FND_API.G_FALSE
391 ,p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
392 ,p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
393 ,p_calling_module IN VARCHAR2 :='SELF_SERVICE'
394 ,p_debug_mode IN VARCHAR2 :='N'
395 ,p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
396 ,p_project_id IN NUMBER
397 ,p_task_id IN NUMBER /* to pass to conversion api */
398 ,p_as_of_date IN DATE /* to pass to conversion api */
402 ,p_calling_mode IN VARCHAR2 := 'ACTUAL_RATES' -- Bug 4372462
399 ,P_txn_cost IN NUMBER
400 ,P_txn_curr_code IN VARCHAR2
401 ,p_structure_version_id IN NUMBER -- Bug 3627787
403 ,p_budget_version_id IN NUMBER := null -- Bug 4372462
404 ,p_res_assignment_id IN NUMBER := null -- Bug 4372462
405 ,p_init_inout_vars IN VARCHAR2 := 'Y' -- Bug 4372462
406 ,P_project_curr_code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
407 ,P_project_rate_type IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
408 ,P_project_rate_date IN OUT NOCOPY DATE --File.Sql.39 bug 4440895
409 ,P_project_exch_rate IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
410 ,P_project_raw_cost IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
411 ,P_projfunc_curr_code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
412 ,P_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
413 ,P_projfunc_cost_rate_date IN OUT NOCOPY DATE --File.Sql.39 bug 4440895
414 ,P_projfunc_cost_exch_rate IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
415 ,P_projfunc_raw_cost IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
416 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
417 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
418 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
419 );
420
421 FUNCTION get_time_phase_period(p_structure_version_id IN NUMBER
422 ,p_project_id IN NUMBER := NULL) return VARCHAR2;
423
424 FUNCTION get_incremental_actual_cost(p_as_of_date IN DATE
425 ,p_period_name IN VARCHAR2
426 ,pgn_flag IN VARCHAR2
427 ,p_project_id IN NUMBER
428 ,p_object_id IN NUMBER
429 ,p_object_version_id IN NUMBER
430 ,currency_flag IN VARCHAR2 := 'T'
431 ,p_structure_version_id IN NUMBER := null --3694031
432 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
433 ) return NUMBER;
434
435 FUNCTION get_incremental_actual_rawcost(p_as_of_date IN DATE
436 ,p_period_name IN VARCHAR2
437 ,pgn_flag IN VARCHAR2
438 ,p_project_id IN NUMBER
439 ,p_object_id IN NUMBER
440 ,p_object_version_id IN NUMBER
441 ,currency_flag IN VARCHAR2 := 'T'
442 ,p_structure_version_id IN NUMBER := null --3694031
443 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
444 ) return NUMBER;
445
446 FUNCTION get_incremental_actual_effort(p_as_of_date IN DATE
447 ,p_period_name IN VARCHAR2
448 ,pgn_flag IN VARCHAR2
449 ,p_project_id IN NUMBER
450 ,p_object_id IN NUMBER
451 ,p_object_version_id IN NUMBER
452 ,p_structure_version_id IN NUMBER := null --3694031
453 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
454 ) return NUMBER;
455
456
457 FUNCTION get_act_txn_cost_this_period (p_as_of_date IN DATE
458 ,p_project_id IN NUMBER
459 ,p_object_id IN NUMBER
460 ,p_object_version_id IN NUMBER
461 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
462 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
463 ) return NUMBER;
464
465 FUNCTION get_act_pfn_cost_this_period (p_as_of_date IN DATE
466 ,p_project_id IN NUMBER
467 ,p_object_id IN NUMBER
468 ,p_object_version_id IN NUMBER
469 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */) return NUMBER;
470
471 FUNCTION get_act_cost_this_period (p_as_of_date IN DATE
472 ,p_project_id IN NUMBER
473 ,p_object_id IN NUMBER
474 ,p_object_version_id IN NUMBER
475 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
476 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
477 ) return NUMBER;
478
479
480 FUNCTION get_act_effort_this_period (p_as_of_date IN DATE
481 ,p_project_id IN NUMBER
482 ,p_object_id IN NUMBER
483 ,p_object_version_id IN NUMBER
484 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */) return NUMBER;
485
486
490 ) RETURN VARCHAR2;
487 FUNCTION check_wwp_prog_publishing_ok(
488 p_project_id IN NUMBER
489 ,p_structure_version_id IN NUMBER
491
492 FUNCTION Get_BAC_Value(
493 p_project_id IN NUMBER
494 ,p_task_weight_method IN VARCHAR2
495 ,p_proj_element_id IN NUMBER
496 ,p_structure_version_id IN NUMBER
497 ,p_structure_type IN VARCHAR2
498 ,p_working_wp_prog_flag IN VARCHAR2 default 'N' --maansari7/18. To get the planned in case of apply lp flow
499 ,p_program_flag IN VARCHAR2 default 'Y' -- Bug 4493105
500 ) RETURN NUMBER;
501
502 FUNCTION Get_LATEST_PROGRESS_ENTRY_DATE(
503 p_project_id NUMBER
504 ,p_object_id NUMBER
505 ,p_object_type VARCHAR2 := 'PA_TASKS'
506 ,p_structure_type VARCHAR2 := 'WORKPLAN'
507 ,p_task_id NUMBER := null /* Amit : Modified for IB4 Progress CR. */
508 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
509 ) RETURN DATE ;
510
511 FUNCTION Get_EARLY_PROGRESS_ENTRY_DATE(
512 p_project_id NUMBER
513 ,p_object_id NUMBER
514 ,p_object_type VARCHAR2 := 'PA_TASKS'
515 ,p_structure_type VARCHAR2 := 'WORKPLAN'
516 ,p_task_id NUMBER := null /* Modified for IB4 Progress CR. */
517 ) RETURN DATE ;
518
519 FUNCTION latest_published_progress_date(p_project_id IN NUMBER
520 ,p_structure_type IN VARCHAR2 ) RETURN DATE;
521
522 FUNCTION check_object_has_prog(
523 p_project_id IN NUMBER -- FPM Dev CR 7 : Removed defaulting
524 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
525 ,p_object_id IN NUMBER -- FPM Dev CR 7 : Removed defaulting
526 ,p_object_type IN VARCHAR2:='PA_TASKS'
527 ,p_structure_type IN VARCHAR2:='WORKPLAN'
528 ,p_progress_status IN VARCHAR2:='ANY'
529 ) RETURN VARCHAR2;
530
531 --- Following APIs added by Bhumesh
532
533 Function Prog_Get_Pa_Period_Name (p_Date IN Date
534 , p_org_id IN NUMBER :=null -- 4746476
535 ) RETURN VARCHAR2 ;
536
537 Function Prog_Get_GL_Period_Name (P_Date IN Date
538 , p_org_id IN NUMBER :=null -- 4746476
539 ) RETURN VARCHAR2 ;
540
541 -- History
542 -- 02-aug-04
543 -- Added two params p_structure_version_id and p_structure_status to return base percent complete
544 -- from a working version also.
545 -- This change is done for B and F.
546
547 Procedure REDEFAULT_BASE_PC (
548 p_Project_ID IN NUMBER
549 ,p_Proj_element_id IN NUMBER
550 ,p_Structure_type IN VARCHAR2 DEFAULT 'WORKPLAN'
551 ,p_object_type IN VARCHAR2 DEFAULT 'PA_TASKS'
552 ,p_As_Of_Date IN DATE
553 ,p_structure_version_id IN NUMBER DEFAULT null
554 ,p_structure_status IN VARCHAR2 DEFAULT null
555 ,p_calling_context IN VARCHAR2 DEFAULT 'PROGRESS'
556 ,X_base_percent_complete OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
557 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
558 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
559 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
560 );
561
562 Procedure RECALCULATE_PROG_STATS (
563 p_project_id IN NUMBER
564 ,p_proj_element_id IN NUMBER
565 ,p_task_version_id IN NUMBER
566 ,p_structure_type IN VARCHAR2 DEFAULT 'WORKPLAN'
567 ,p_As_Of_Date IN DATE
568 ,P_Overide_Percent_Complete IN NUMBER
569 ,p_Actual_Effort IN NUMBER
570 ,p_Actual_Cost IN NUMBER
571 ,p_Planned_Effort IN NUMBER
572 ,p_Planned_Cost IN NUMBER
573 ,p_baselined_Effort IN NUMBER
574 ,p_baselined_Cost IN NUMBER
575 ,x_BCWS OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
576 ,X_BCWP OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
577 ,X_SCH_Performance_Index OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
578 ,X_COST_Performance_Index OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
579 ,x_Sch_At_Completion OUT NOCOPY DATE --File.Sql.39 bug 4440895
580 ,x_Complete_Performance_Index OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
581 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
582 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
583 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
584 );
585
586 Procedure DEF_DATES_FROM_RESOURCES (
587 p_project_id IN NUMBER
588 ,p_proj_element_id IN NUMBER
589 ,p_structure_type IN VARCHAR2 DEFAULT 'WORKPLAN'
590 ,p_As_Of_Date IN DATE
591 ,x_Actual_Start_Date OUT NOCOPY DATE --File.Sql.39 bug 4440895
592 ,x_Actual_Finish_Date OUT NOCOPY DATE --File.Sql.39 bug 4440895
593 ,x_Estimated_Start_Date OUT NOCOPY DATE --File.Sql.39 bug 4440895
594 ,x_Estimated_Finish_Date OUT NOCOPY DATE --File.Sql.39 bug 4440895
595 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
596 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
597 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
598 );
599
600 FUNCTION check_actuals_allowed (p_project_id IN NUMBER
601 ,p_structure_type IN VARCHAR2 := 'WORKPLAN') RETURN VARCHAR2;
602
603 -- Progress Management Changes Bug # 3420093.
604
605 FUNCTION get_bcws (p_project_id IN NUMBER
606 ,p_object_id IN NUMBER
607 ,p_proj_element_id IN NUMBER
611 ,p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
608 ,p_as_of_date IN DATE
609 ,p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
610 ,p_rollup_method IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
612 ,p_scheduled_end_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
613 ,p_prj_currency_code IN VARCHAR2 := null --bug 3824042
614 ,p_structure_type IN VARCHAR2 := 'WORKPLAN' --maansari4/10
615 ) RETURN NUMBER;
616
617 FUNCTION get_latest_ass_prog_date(p_project_id IN NUMBER
618 ,p_structure_type IN VARCHAR2
619 ,p_object_id IN NUMBER
620 ,p_object_type IN VARCHAR2
621 ,p_task_id IN NUMBER := null /* Modified for IB4 Progress CR. */) RETURN DATE;
622
623 --- End of addding new APIs
624
625 FUNCTION get_resource_list_id ( p_resource_list_member_id NUMBER) RETURN NUMBER;
626
627
628 function get_max_rollup_asofdate2(p_project_id IN NUMBER,
629 p_object_id IN NUMBER,
630 p_object_type IN VARCHAR2,
631 p_structure_type IN VARCHAR2 := 'WORKPLAN', -- FPM Dev CR 3
632 p_structure_version_id IN NUMBER := NULL -- FPM Dev CR 4
633 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
634 ) return date;
635
636 procedure set_prog_as_of_Date(p_project_id IN NUMBER,
637 p_task_id IN NUMBER,
638 p_as_of_date IN DATE default to_date(null),
639 p_object_id IN NUMBER := null, -- Bug 3974627
640 p_object_type IN VARCHAR2 := 'PA_TASKS' -- Bug 3974627
641 );
642
643 function get_prog_asofdate return date;
644 PRAGMA RESTRICT_REFERENCES(get_prog_asofdate, WNDS, WNPS);
645
646 --The following api is used to render cost region on Task progress Details -summary page
647
648 --bug 4085786, changed the signature
649 --function check_workplan_cost ( p_project_id NUMBER) RETURN VARCHAR2;
650 function check_workplan_cost ( p_project_id IN NUMBER,
651 p_task_id IN NUMBER := NULL,
652 p_object_id IN NUMBER := NULL,
653 p_object_type IN VARCHAR2 := 'PA_TASKS',
654 p_structure_version_id IN NUMBER := NULL
655 ) RETURN VARCHAR2;
656
657 -- Progress Management Changes. Bug # 3420093.
658
659 procedure get_actuals_for_task(p_project_id IN NUMBER
660 ,p_wp_task_id IN NUMBER
661 ,p_res_list_mem_id IN NUMBER
662 ,p_as_of_date IN DATE
663 ,x_planned_work_qty OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
664 ,x_actual_work_qty OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
665 ,x_ppl_act_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
666 ,x_eqpmt_act_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
667 ,x_oth_act_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
668 ,x_ppl_act_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
669 ,x_eqpmt_act_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
670 ,x_oth_act_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
671 ,x_act_labor_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
672 ,x_act_eqpmt_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
673 ,x_unit_of_measure OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
674 ,x_txn_currency_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
675 ,x_ppl_act_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
676 ,x_eqpmt_act_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
677 ,x_oth_act_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
678 ,x_ppl_act_rawcost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
679 ,x_eqpmt_act_rawcost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
680 ,x_oth_act_rawcost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
681 ,x_ppl_act_rawcost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
682 ,x_eqpmt_act_rawcost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
683 ,x_oth_act_rawcost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
684 ,x_ppl_act_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
685 ,x_eqpmt_act_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
686 ,x_oth_act_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
687 ,x_oth_quantity OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
688 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
689 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
690 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
691
692 -- Progress Management Changes. Bug # 3420093.
693
697 p_project_id NUMBER
694 FUNCTION wp_task_ver_id_for_fin_task_id(p_project_id NUMBER, p_fin_task_id NUMBER) return NUMBER;
695 /* Bug 3595585 : Added the following procedure */
696 FUNCTION get_last_etc_effort(
698 ,p_object_id NUMBER
699 ,p_object_type VARCHAR2
700 ,p_as_of_date DATE
701 ,p_structure_type VARCHAR2 := 'WORKPLAN'
702 ,p_proj_element_id NUMBER := null /* Modified for IB4 Progress CR. */
703 ) RETURN NUMBER;
704
705 /* Bug 3595585 : Added the following procedure */
706 FUNCTION get_last_etc_cost(
707 p_project_id NUMBER
708 ,p_object_id NUMBER
709 ,p_object_type VARCHAR2
710 ,p_as_of_date DATE
711 ,p_structure_type VARCHAR2 := 'WORKPLAN'
712 ,p_proj_element_id NUMBER := null /* Modified for IB4 Progress CR. */
713 ) RETURN NUMBER;
714
715 /* Bug 3595585 : Added the following procedure */
716 -- Bug 3621404 : Added burden parameters
717 PROCEDURE get_last_etc_all(p_project_id IN NUMBER
718 ,p_object_id IN NUMBER
719 ,p_object_type IN VARCHAR2
720 ,p_as_of_date IN DATE
721 ,p_structure_type IN VARCHAR2 := 'WORKPLAN'
722 ,x_etc_txn_raw_cost_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
723 ,x_etc_prj_raw_cost_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
724 ,x_etc_pfc_raw_cost_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
725 ,x_etc_txn_bur_cost_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
726 ,x_etc_prj_bur_cost_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
727 ,x_etc_pfc_bur_cost_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
728 ,x_etc_effort_last_subm OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
729 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
730 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
731 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
732 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
733 ,p_resource_class_code IN VARCHAR2 := 'PEOPLE' -- Bug 3836485
734 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
735 );
736
737 function sum_etc_values(
738 p_planned_value NUMBER := null
739 ,p_ppl_etc_value NUMBER := null
740 ,p_eqpmt_etc_value NUMBER := null
741 ,p_oth_etc_value NUMBER := null
742 ,p_subprj_ppl_etc_value NUMBER := null
743 ,p_subprj_eqpmt_etc_value NUMBER := null
744 ,p_subprj_oth_etc_value NUMBER := null
745 ,p_oth_etc_quantity NUMBER := null
746 ,p_actual_value NUMBER := null
747 ,p_mode VARCHAR2 := 'PUBLISH'
748 )return number;
749
750
751 -- Progress Management Changes. Bug # 3621404.
752
753 FUNCTION get_act_rawcost_this_period (p_as_of_date IN DATE
754 ,p_project_id IN NUMBER
755 ,p_object_id IN NUMBER
756 ,p_object_version_id IN NUMBER
757 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */) return NUMBER;
758
759
760 -- Progress Management Changes. Bug # 3621404.
761
762 FUNCTION get_act_txn_rawcost_thisperiod (p_as_of_date IN DATE
763 ,p_project_id IN NUMBER
764 ,p_object_id IN NUMBER
765 ,p_object_version_id IN NUMBER
766 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
767 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
768 ) return NUMBER;
769
770
771 -- Progress Management Changes. Bug # 3621404.
772
773 FUNCTION get_act_pfn_rawcost_thisperiod (p_as_of_date IN DATE
774 ,p_project_id IN NUMBER
775 ,p_object_id IN NUMBER
776 ,p_object_version_id IN NUMBER
777 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */) return NUMBER;
778
779
780 -- Bug 3621404 : Added Get_Res_Rate_Burden_Multiplier
781 Procedure Get_Res_Rate_Burden_Multiplier(P_res_list_mem_id IN NUMBER
782 ,P_project_id IN NUMBER
783 ,P_task_id IN NUMBER := null --bug 3860575
784 ,p_as_of_date IN DATE := null --bug 3901289
785 --maansari6/14 bug 3686920
786 ,p_structure_version_id IN NUMBER default null
787 ,p_currency_code IN VARCHAR2 default null
788 --maansari6/14 bug 3686920
789 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
790 ,p_calling_mode IN VARCHAR2 := 'ACTUAL_RATES' -- Bug 3627315
791 -- ,P_dummy_override_raw_cost IN NUMBER Bug 3632946
792 -- ,P_override_txn_currency_code IN VARCHAR2 Bug 3632946
793 ,x_resource_curr_code OUT NOCOPY VARCHAR2
797 ,X_burden_multiplier OUT NOCOPY NUMBER
794 ,x_resource_raw_rate OUT NOCOPY NUMBER
795 ,x_resource_burden_rate OUT NOCOPY NUMBER
796 -- ,X_dummy_burden_cost OUT NOCOPY NUMBER Bug 3632946
798 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
799 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
800 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
801 );
802
803 -- Bug 3621404 : Raw Cost Changes, Added this procedure
804 PROCEDURE get_all_amounts_cumulative
805 (p_project_id IN NUMBER
806 ,p_object_id IN NUMBER
807 ,p_object_type IN VARCHAR2
808 ,p_structure_version_id IN NUMBER := NULL -- Do not pass if published structure version
809 ,p_as_of_date IN DATE := NULL -- Must pass if published structure version
810 ,x_act_bur_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
811 ,x_act_bur_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
812 ,x_act_bur_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
813 ,x_act_raw_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
814 ,x_act_raw_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
815 ,x_act_raw_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
816 ,x_etc_bur_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
817 ,x_etc_bur_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
818 ,x_etc_bur_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
819 ,x_etc_raw_cost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
820 ,x_etc_raw_cost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
821 ,x_etc_raw_cost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
822 ,x_act_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
823 ,x_etc_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
824 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
825 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
826 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
827 ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
828 );
829
830 -- Progress Management Changes. Bug # 3621404.
831
832 function derive_etc_values(
833 p_planned_value NUMBER := null
834 ,p_ppl_act_value NUMBER := null
835 ,p_eqpmt_act_value NUMBER := null
836 ,p_oth_act_value NUMBER := null
837 ,p_subprj_ppl_act_value NUMBER := null
838 ,p_subprj_eqpmt_act_value NUMBER := null
839 ,p_subprj_oth_act_value NUMBER := null
840 ,p_oth_quantity_to_date NUMBER := null
841 )return number;
842
843 -- Bug 3633293 : Added check_deliverable_exists
844 FUNCTION check_deliverable_exists(
845 p_project_id NUMBER
846 ,p_object_id NUMBER
847 ) RETURN VARCHAR2 ;
848
849 -- Bug 3651781 : Added published_dlv_prog_exists
850 function published_dlv_prog_exists
851 (
852 p_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE
853 ,p_dlv_proj_elt_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE
854 ) RETURN VARCHAR2 ;
855
856
857 --Added for performance improvements.
858 g_structure_version_id NUMBER;
859 procedure set_global_str_ver_id(p_structure_version_id NUMBER);
860 function get_global_str_ver_id RETURN NUMBER;
861
862 g_time_phase_period_name VARCHAR2(150);
863 procedure set_global_time_phase_period(p_period_name VARCHAR2);
864 function get_global_time_phase_period RETURN VARCHAR2;
865
866 -- Added following two functions for bug 3709439
867 FUNCTION Percent_Spent_Value
868 (
869 p_actual_value NUMBER
870 ,p_planned_value NUMBER
871 ) RETURN NUMBER ;
872
873 FUNCTION Percent_Complete_Value
874 (
875 p_actual_value NUMBER
876 ,p_etc_value NUMBER
877 ) RETURN NUMBER ;
878
879 -- Bug 3784324 : Added procedure convert_effort_to_cost
880 PROCEDURE convert_effort_to_cost
881 ( p_resource_list_mem_id IN NUMBER
882 ,p_project_id IN NUMBER
883 ,p_structure_version_id IN NUMBER
884 ,p_txn_currency_code IN VARCHAR
885 ,p_planned_effort IN NUMBER
886 ,p_planned_rawcost_tc IN NUMBER
887 ,p_act_effort_this_period IN NUMBER
888 ,p_act_effort IN NUMBER
889 ,p_etc_effort IN NUMBER
890 ,p_rate_based_flag IN VARCHAR := 'Y'
891 ,p_act_rawcost_tc IN NUMBER
892 ,x_act_rawcost_tc_this_period IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
893 ,x_etc_rawcost_tc IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
894 ,x_prcnt_comp_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
895 ,x_prcnt_spent_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
896 ,x_eac_effort OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
897 ,x_prcnt_comp_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
898 ,x_prcnt_spent_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
899 ,x_eac_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
900 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
901 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
902 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
903 );
904 --BUG 3815202, rtarway
905 FUNCTION get_last_published_perc_comp(
906 p_project_id NUMBER
910 ) RETURN NUMBER ;
907 ,p_object_id NUMBER
908 ,p_as_of_date Date
909 ,p_object_type VARCHAR2
911
912 /* Bug # 3861344: Created API: return_start_end_date(). */
913
914 Function return_start_end_date(
915 p_scheduled_date DATE := NULL
916 ,p_baselined_date DATE := NULL
917 ,p_project_id NUMBER
918 ,p_proj_element_id NUMBER
919 ,p_object_type VARCHAR2 := 'PA_TASKS'
920 ,p_start_end_flag VARCHAR2 := 'S'
921 ) return date;
922
923 -- Procedure to be called when applying latest progress to / publishing the working workplan version.
924 PROCEDURE check_txn_currency_diff
925 (
926 p_structure_version_id IN NUMBER,
927 p_context IN VARCHAR2 DEFAULT 'PUBLISH_STRUCTURE',
928 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
929 );
930
931 -- Procedure to be called when updating task assignments for progress related business rules check.
932 PROCEDURE check_prog_for_update_asgmts
933 (
934 p_task_assignment_tbl IN PA_TASK_ASSIGNMENT_UTILS.l_resource_rec_tbl_type,
935 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
936 );
937
938 -- Bug # 3910193: Created API: convert_effort_to_cost_brdn_pc().
939
940 PROCEDURE convert_effort_to_cost_brdn_pc
941 ( p_resource_list_mem_id IN NUMBER
942 ,p_project_id IN NUMBER
943 ,p_task_id IN NUMBER
944 ,p_as_of_date IN DATE
945 ,p_structure_version_id IN NUMBER
946 ,p_txn_currency_code IN VARCHAR
947 ,p_planned_quantity IN NUMBER
948 ,p_act_quantity_this_period IN NUMBER
949 ,p_act_quantity IN NUMBER
950 ,p_act_brdncost_pc IN NUMBER
951 ,p_etc_quantity IN NUMBER
952 ,p_rate_based_flag IN VARCHAR := 'Y'
953 ,x_act_brdncost_pc_this_period OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
954 ,x_etc_brdncost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
955 ,x_prcnt_comp_quantity OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
956 ,x_prcnt_spent_quantity OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
957 ,x_eac_quantity OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
958 ,x_prcnt_comp_brdncost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
959 ,x_prcnt_spent_brdncost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
960 ,x_eac_brdncost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
961 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
962 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
963 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
964 );
965
966 function get_actual_summ_date(p_project_id IN NUMBER) return date;
967
968 /* Bug # 3956235: Created API: get_cost_variance(). */
969
970 Function get_cost_variance(
971 p_project_id NUMBER
972 , p_proj_element_id NUMBER
973 , p_structure_version_id NUMBER
974 , p_task_weight_method VARCHAR2
975 , p_structure_type VARCHAR2 := 'WORKPLAN'
976 , p_base_percent_complete NUMBER
977 , p_eff_rollup_percent_comp NUMBER
978 , p_earned_value NUMBER
979 , p_oth_act_cost_to_date_pc NUMBER
980 , p_ppl_act_cost_to_date_pc NUMBER
981 , p_eqpmt_act_cost_to_date_pc NUMBER
982 , p_spj_oth_act_cost_to_date_pc NUMBER
983 , p_spj_ppl_act_cost_pc NUMBER
984 , p_spj_eqpmt_act_cost_pc NUMBER
985 ) return number;
986
987 -- Begin fix for Bug # 4073659.
988
989 FUNCTION check_prog_exists_and_delete(
990 p_project_id NUMBER
991 ,p_task_id NUMBER
992 ,p_object_type VARCHAR2 := 'PA_TASKS'
993 ,p_object_id NUMBER := null
994 ,p_structure_type VARCHAR2 := 'WORKPLAN'
995 ,p_delete_progress_flag VARCHAR2 := 'Y' -- Fix for Bug # 4140984.
996 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16598322
997 ) RETURN VARCHAR2;
998
999 -- End fix for Bug # 4073659.
1000
1001 /* Begin fix for bug # 4115607. */
1002
1003 FUNCTION get_app_cost_budget_cb_wor_ver(p_project_id NUMBER)
1004 return NUMBER;
1005
1006 /* End fix for bug # 4115607. */
1007 --Bug 5027965. introduced parameter p_etc_cost_calc_mode which can be either COPY or DERIVE.
1008 --If its COPY then
1009 ----the etc cost in the current working workplan version will returned. In this case
1010 ----the parameter p_budget_version_id will contain the budget version id corresponding to the
1011 ----current working workplan version
1012 --If its DERIVE then
1013 ----the etc cost will be derived based on the rate setup on p_as_of_date
1014 ----p_budget_version_id will contain the budget version id corresponding to the latest published
1015 ---- workplan version
1016 procedure get_plan_costs_for_qty
1017 ( p_etc_cost_calc_mode IN VARCHAR2 DEFAULT 'DERIVE' --Bug 5027965
1018 ,p_resource_list_mem_id IN NUMBER
1019 ,p_project_id IN NUMBER
1023 ,p_txn_currency_code IN VARCHAR
1020 ,p_task_id IN NUMBER
1021 ,p_as_of_date IN DATE
1022 ,p_structure_version_id IN NUMBER
1024 ,p_rate_based_flag IN VARCHAR := 'Y'
1025 ,p_quantity IN NUMBER
1026 ,p_budget_version_id IN NUMBER ---4372462
1027 ,p_res_assignment_id IN NUMBER
1028 ,x_rawcost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1029 ,x_brdncost_tc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1030 ,x_rawcost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1031 ,x_brdncost_pc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1032 ,x_rawcost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1033 ,x_brdncost_fc OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1034 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1035 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1036 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1037 ,p_cbs_element_id IN NUMBER DEFAULT NULL
1038 );
1039
1040 /* Begin Fix for Bug # 4108270.*/
1041
1042 FUNCTION get_pc_from_sub_tasks_assgn
1043 (p_project_id NUMBER
1044 ,p_proj_element_id NUMBER
1045 ,p_structure_version_id NUMBER
1046 ,p_include_sub_tasks_flag VARCHAR2 := 'Y'
1047 ,p_structure_type VARCHAR2 := 'WORKPLAN'
1048 ,p_object_type VARCHAR2 := 'PA_TASKS'
1049 ,p_as_of_date DATE := null
1050 ,p_program_flag VARCHAR2 := 'Y' -- 4392189 : Program Reporting Changes - Phase 2
1051 )
1052 RETURN NUMBER;
1053
1054 /* End Fix for Bug # 4108270.*/
1055
1056 /* Begin fix for Bug # 4185974. */
1057
1058 FUNCTION get_act_for_prev_asofdate (p_as_of_date IN DATE
1059 ,p_project_id IN NUMBER
1060 ,p_object_id IN NUMBER
1061 ,p_object_version_id IN NUMBER
1062 ,p_proj_element_id IN NUMBER := null
1063 ,p_effort_cost_flag IN VARCHAR2 := 'C'
1064 ,p_cost_type_flag IN VARCHAR2 := 'B'
1065 ,p_currency_flag IN VARCHAR2 := 'T'
1066 ,p_cbs_element_id IN NUMBER := null) return NUMBER;
1067
1068 /* End fix for Bug # 4185974. */
1069
1070 -- Begin fix for Bug # 4319171.
1071
1072 function calc_act(p_ppl_cost_eff IN NUMBER := null
1073 , p_eqpmt_cost_eff IN NUMBER := null
1074 , p_oth_cost_eff IN NUMBER := null
1075 , p_subprj_ppl_cost_eff IN NUMBER := null
1076 , p_subprj_eqpmt_cost_eff IN NUMBER := null
1077 , p_subprj_oth_cost_eff IN NUMBER := null) return NUMBER;
1078
1079 function calc_etc(p_planned_cost_eff IN NUMBER := null
1080 , p_ppl_cost_eff IN NUMBER := null
1081 , p_eqpmt_cost_eff IN NUMBER := null
1082 , p_oth_cost_eff IN NUMBER := null
1083 , p_subprj_ppl_cost_eff IN NUMBER := null
1084 , p_subprj_eqpmt_cost_eff IN NUMBER := null
1085 , p_subprj_oth_cost_eff IN NUMBER := null
1086 , p_oth_quantity IN NUMBER := null
1087 , p_act_cost_eff IN NUMBER := null) return NUMBER;
1088
1089 function calc_wetc(p_planned_cost_eff IN NUMBER := null
1090 , p_ppl_cost_eff IN NUMBER := null
1091 , p_eqpmt_cost_eff IN NUMBER := null
1092 , p_oth_cost_eff IN NUMBER := null
1093 , p_subprj_ppl_cost_eff IN NUMBER := null
1094 , p_subprj_eqpmt_cost_eff IN NUMBER := null
1095 , p_subprj_oth_cost_eff IN NUMBER := null
1096 , p_oth_quantity IN NUMBER := null) return NUMBER;
1097
1098 function calc_plan(p_ppl_cost_eff IN NUMBER := null
1099 , p_eqpmt_cost_eff IN NUMBER := null
1100 , p_oth_cost_eff IN NUMBER := null) return NUMBER;
1101
1102 -- End fix for Bug # 4319171.
1103
1104 -- Bug 4490532 Begin
1105 g_self_project_id NUMBER;
1106 g_self_object_version_id NUMBER;
1107 g_self_as_of_date DATE;
1108 g_self_current_flag VARCHAR2(1);
1109 g_self_rec_version_number NUMBER;
1110 g_self_act_effort NUMBER;
1111 g_self_act_cost NUMBER;
1112 g_self_etc_effort NUMBER;
1113 g_self_etc_cost NUMBER;
1114
1115 function get_self_amounts(p_amount_type IN VARCHAR2
1116 , p_structure_sharing_code IN VARCHAR2
1117 , p_prg_group IN NUMBER
1118 , p_project_id IN NUMBER
1119 , p_object_version_id IN NUMBER
1120 , p_proj_element_id IN NUMBER
1121 , p_as_of_date IN DATE
1122 , p_current_flag IN VARCHAR2
1123 , p_record_version_number IN NUMBER
1124 ) return NUMBER;
1125 -- Bug 4490532 End
1126
1127 -- Bug 4871809, added this function for perf reasons, used in pa_progress_pvt.
1128 function get_w_pub_prupid_asofdate(p_project_id IN number,
1129 p_object_id IN number,
1130 p_object_type IN varchar2,
1131 p_task_id IN number,
1132 p_as_of_date IN date,
1133 p_chk_task IN varchar2 default 'Y',
1134 p_cbs_element_id in number :=null) return number;
1135
1136 function get_w_pub_currflag(p_project_id IN number,
1137 p_object_id IN number,
1138 p_object_type IN varchar2,
1139 p_task_id IN number,
1140 p_chk_task IN varchar2 default 'N') return varchar2;
1141
1142 function check_etc_overridden(p_plan_qty IN NUMBER,
1143 p_actual_qty IN NUMBER,
1144 p_etc_qty IN NUMBER) return varchar2;
1145
1146 function check_ta_has_prog(
1147 p_project_id IN NUMBER,
1148 p_proj_element_id IN NUMBER,
1149 p_element_ver_id IN NUMBER ) return varchar2;
1150
1151
1152 --Start changes for Bug 6664716
1153 procedure get_plan_value
1154 ( p_project_id IN NUMBER
1155 ,p_structure_version_id IN NUMBER
1156 ,p_element_id IN NUMBER
1157 ,p_as_of_date IN DATE
1158 );
1159
1160 procedure clear_tmp_tables (p_task_id IN Number Default Null);
1161 --End changes for Bug 6664716
1162
1163
1164 -- Bug 7259306
1165 FUNCTION get_def_as_of_date_prog_report(
1166 p_project_id pa_progress_rollup.project_id%TYPE,
1167 p_proj_element_id pa_progress_rollup.proj_element_id%TYPE,
1168 p_object_type pa_progress_rollup.object_type%TYPE := 'PA_TASKS'
1169 ) RETURN DATE;
1170
1171 -- Bug 7633088
1172 procedure set_override_as_of_date(p_as_of_date IN DATE DEFAULT to_date(NULL));
1173
1174 /** bpottipa added this method for bug 9877852.
1175 * This will be used in Team Member Home page > Update Progress >
1176 * Assignments tab view to get the actual effort this period
1177 * value. The logic in this method is similar to the one used in
1178 * pa_tasks_assigns_progress_v to derive actual effort to this period.
1179 */
1180 FUNCTION get_assgn_act_effort_period (p_as_of_date IN DATE
1181 ,p_project_id IN NUMBER
1182 ,p_object_id IN NUMBER
1183 ,p_object_version_id IN NUMBER
1184 ,p_proj_element_id IN NUMBER := null
1185 ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
1186 ) return NUMBER;
1187
1188 end PA_PROGRESS_UTILS;
1189
[Home] [Help]