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