[Home] [Help]
PACKAGE BODY: APPS.PA_FP_VIEW_PLANS_UTIL
Source
1 PACKAGE BODY pa_fp_view_plans_util as
2 /* $Header: PAFPVPUB.pls 120.1 2005/08/19 16:31:38 mwasowic noship $
3 Start of Comments
4 Package name : pa_fin_plan_maint_ver_global
5 Purpose : API's for Financial Planning: View Plans Page
6 History :
7 NOTE :
8 End of Comments
9 */
10
11 FUNCTION calculate_gl_total
12 (p_amount_code IN pa_amount_types_b.amount_type_code%TYPE,
13 p_project_id IN pa_resource_assignments.project_id%TYPE,
14 p_task_id IN pa_resource_assignments.task_id%TYPE,
15 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE)
16 return NUMBER
17 is
18 l_burdened_cost NUMBER := 0;
19 l_revenue NUMBER := 0;
20 l_margin NUMBER := 0;
21 l_margin_percent NUMBER := 0;
22 begin
23 select SUM(DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
24 'R', DECODE(amount_subtype,
25 'RAW_COST', nvl(period_amount1, 0) + nvl(period_amount2, 0) +
26 nvl(period_amount3, 0) + nvl(period_amount4, 0) +
27 nvl(period_amount5, 0) + nvl(period_amount6, 0) +
28 DECODE(pa_fp_view_plans_pub.Get_Prec_Pds_Flag,
29 'Y', nvl(preceding_periods_amount,0),
30 0) +
31 DECODE(pa_fp_view_plans_pub.Get_Succ_Pds_Flag,
32 'Y', nvl(succeeding_periods_amount,0),
33 0),
34 0),
35 DECODE(amount_subtype,
36 'BURDENED_COST', nvl(period_amount1, 0) + nvl(period_amount2, 0) +
37 nvl(period_amount3, 0) + nvl(period_amount4, 0) +
38 nvl(period_amount5, 0) + nvl(period_amount6, 0) +
39 DECODE(pa_fp_view_plans_pub.Get_Prec_Pds_Flag,
40 'Y', nvl(preceding_periods_amount,0),
41 0) +
42 DECODE(pa_fp_view_plans_pub.Get_Succ_Pds_Flag,
43 'Y', nvl(succeeding_periods_amount,0),
44 0),
45 0))),
46 SUM(DECODE(amount_subtype,
47 'REVENUE', nvl(period_amount1, 0) + nvl(period_amount2, 0) +
48 nvl(period_amount3, 0) + nvl(period_amount4, 0) +
49 nvl(period_amount5, 0) + nvl(period_amount6, 0) +
50 DECODE(pa_fp_view_plans_pub.Get_Prec_Pds_Flag,
51 'Y', nvl(preceding_periods_amount,0),
52 0) +
53 DECODE(pa_fp_view_plans_pub.Get_Succ_Pds_Flag,
54 'Y', nvl(succeeding_periods_amount,0),
55 0),
56 0))
57 into l_burdened_cost,
58 l_revenue
59 from PA_FIN_VP_PDS_VIEW_TMP
60 where project_id = p_project_id and
61 task_id = p_task_id and
62 resource_list_member_id = p_resource_list_member_id and
63 amount_type in ('COST', 'REVENUE');
64 l_margin := l_revenue - l_burdened_cost;
65 if ((l_revenue = 0) or (l_burdened_cost = 0)) then -- Added for bug 3651389
66 l_margin_percent := 0;
67 else
68 l_margin_percent := (l_revenue - l_burdened_cost) / l_revenue;
69 end if;
70
71 if p_amount_code = 'MARGIN_PERCENT' then
72 return l_margin_percent;
73 else
74 return 0;
75 end if;
76 end calculate_gl_total;
77 /* ------------------------------------------------------------------ */
78
79 FUNCTION calculate_pa_total
80 (p_amount_code IN pa_amount_types_b.amount_type_code%TYPE,
81 p_project_id IN pa_resource_assignments.project_id%TYPE,
82 p_task_id IN pa_resource_assignments.task_id%TYPE,
83 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE)
84 return NUMBER
85 is
86 l_burdened_cost NUMBER := 0;
87 l_revenue NUMBER := 0;
88 l_margin NUMBER := 0;
89 l_margin_percent NUMBER := 0;
90 begin
91 -- bug fix 2746025: calculate margin from RAW_COST or BURDENED_COST based
92 -- on margin_derived_from_code
93 select SUM(DECODE(pa_fp_view_plans_pub.G_FP_CALC_MARGIN_FROM,
94 'R', DECODE(amount_subtype,
95 'RAW_COST', nvl(period_amount1, 0) + nvl(period_amount2, 0) +
96 nvl(period_amount3, 0) + nvl(period_amount4, 0) +
97 nvl(period_amount5, 0) + nvl(period_amount6, 0) +
98 nvl(period_amount7, 0) + nvl(period_amount8, 0) +
99 nvl(period_amount9, 0) + nvl(period_amount10, 0) +
100 nvl(period_amount11, 0) + nvl(period_amount12, 0) +
101 nvl(period_amount13, 0) +
102 DECODE(pa_fp_view_plans_pub.Get_Prec_Pds_Flag,
103 'Y', nvl(preceding_periods_amount,0),
104 0) +
105 DECODE(pa_fp_view_plans_pub.Get_Succ_Pds_Flag,
106 'Y', nvl(succeeding_periods_amount,0),
107 0),
108 0),
109 DECODE(amount_subtype,
110 'BURDENED_COST', nvl(period_amount1, 0) + nvl(period_amount2, 0) +
111 nvl(period_amount3, 0) + nvl(period_amount4, 0) +
112 nvl(period_amount5, 0) + nvl(period_amount6, 0) +
113 nvl(period_amount7, 0) + nvl(period_amount8, 0) +
114 nvl(period_amount9, 0) + nvl(period_amount10, 0) +
115 nvl(period_amount11, 0) + nvl(period_amount12, 0) +
116 nvl(period_amount13, 0) +
117 DECODE(pa_fp_view_plans_pub.Get_Prec_Pds_Flag,
118 'Y', nvl(preceding_periods_amount,0),
119 0) +
120 DECODE(pa_fp_view_plans_pub.Get_Succ_Pds_Flag,
121 'Y', nvl(succeeding_periods_amount,0),
122 0),
123 0))),
124 SUM(DECODE(amount_subtype,
125 'REVENUE', nvl(period_amount1, 0) + nvl(period_amount2, 0) +
126 nvl(period_amount3, 0) + nvl(period_amount4, 0) +
127 nvl(period_amount5, 0) + nvl(period_amount6, 0) +
128 nvl(period_amount7, 0) + nvl(period_amount8, 0) +
129 nvl(period_amount9, 0) + nvl(period_amount10, 0) +
130 nvl(period_amount11, 0) + nvl(period_amount12, 0) +
131 nvl(period_amount13, 0) +
132 DECODE(pa_fp_view_plans_pub.Get_Prec_Pds_Flag,
133 'Y', nvl(preceding_periods_amount,0),
134 0) +
135 DECODE(pa_fp_view_plans_pub.Get_Succ_Pds_Flag,
136 'Y', nvl(succeeding_periods_amount,0),
137 0),
138 0))
139 into l_burdened_cost,
140 l_revenue
141 from PA_FIN_VP_PDS_VIEW_TMP
142 where project_id = p_project_id and
143 task_id = p_task_id and
144 resource_list_member_id = p_resource_list_member_id and
145 amount_type in ('COST', 'REVENUE');
146 l_margin := l_revenue - l_burdened_cost;
147 if ((l_revenue = 0) or (l_burdened_cost = 0)) then -- Added for bug 3651389
148 l_margin_percent := 0;
149 else
150 l_margin_percent := (l_revenue - l_burdened_cost) / l_revenue;
151 end if;
152
153 if p_amount_code = 'MARGIN_PERCENT' then
154 return l_margin_percent;
155 else
156 return 0;
157 end if;
158 end calculate_pa_total;
159
160 /* ------------------------------------------------------------------ */
161 -- This procedure checks to see if two period profiles are compatible
162 -- Period profiles are compatible if at least one of the following is true:
163 -- 1. They have the same period_profile_id
164 -- 2. They have different period_profile_id's, but have the same
165 -- period type (PA or GL), span the same number of periods, and begin on the
166 -- same date.
167 FUNCTION check_compatible_pd_profiles
168 (p_period_profile_id1 IN pa_proj_period_profiles.period_profile_id%TYPE,
169 p_period_profile_id2 IN pa_proj_period_profiles.period_profile_id%TYPE)
170 return VARCHAR2
171 is
172 l_return_value VARCHAR2(1);
173 l_plan_period_type1 pa_proj_period_profiles.plan_period_type%TYPE;
174 l_plan_period_type2 pa_proj_period_profiles.plan_period_type%TYPE;
175 l_number_of_periods1 pa_proj_period_profiles.number_of_periods%TYPE;
176 l_number_of_periods2 pa_proj_period_profiles.number_of_periods%TYPE;
177 l_plan_start_date1 pa_proj_period_profiles.period1_start_date%TYPE;
178 l_plan_start_date2 pa_proj_period_profiles.period1_start_date%TYPE;
179 begin
180 if (p_period_profile_id1 is null) or (p_period_profile_id2 is null) then return 'N';
181 elsif p_period_profile_id1 = p_period_profile_id2 then return 'Y';
182 else
183 l_return_value := 'N';
184 select plan_period_type,
185 number_of_periods,
186 period1_start_date
187 into l_plan_period_type1,
188 l_number_of_periods1,
189 l_plan_start_date1
190 from pa_proj_period_profiles
191 where period_profile_id = p_period_profile_id1;
192 select plan_period_type,
193 number_of_periods,
194 period1_start_date
195 into l_plan_period_type2,
196 l_number_of_periods2,
197 l_plan_start_date2
198 from pa_proj_period_profiles
199 where period_profile_id = p_period_profile_id2;
200 if (l_plan_period_type1 = l_plan_period_type2) and
201 (l_number_of_periods1 = l_number_of_periods2) and
202 (l_plan_start_date1 = l_plan_start_date2) then
203 l_return_value := 'Y';
204 end if;
205 return l_return_value;
206 end if;
207 end check_compatible_pd_profiles;
208 /* -------------------------------------------------------------------- */
209
210 FUNCTION assign_row_level
211 (p_project_id IN pa_resource_assignments.project_id%TYPE,
212 p_task_id IN pa_resource_assignments.task_id%TYPE,
213 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE)
214 return NUMBER
215 is
216 /* local variables */
217 l_return_value NUMBER;
218 l_parent_task_id pa_tasks.parent_task_id%TYPE;
219 l_parent_wbs_level pa_tasks.wbs_level%TYPE;
220 l_res_parent_member_id pa_resource_list_members.parent_member_id%TYPE;
221 BEGIN
222 l_return_value := -1;
223 -- NEED TO ACCOUNT FOR PROJECT AND TASK LEVEL ROWS THAT ARE USER-ENTERED
224 if p_resource_list_member_id = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id then
225 if p_task_id = 0 then
226 -- this is a PROJECT row
227 l_return_value := 1;
228 else
229 -- this is a TASK row
230 select nvl(parent_task_id, -99)
231 into l_parent_task_id
232 from pa_tasks
233 where task_id = p_task_id and project_id = p_project_id;
234 if l_parent_task_id = -99 then
235 -- this task has no parent; so it must be a top-level task
236 l_return_value := 2;
237 else
238 -- this task has a parent; so the level is one more than the parent
239 select wbs_level
240 into l_parent_wbs_level
241 from pa_tasks
242 where task_id = l_parent_task_id;
243 l_return_value := 1 + l_parent_wbs_level + 1; -- 1 for project, wbs_level, and 1 for child
244 end if; -- parent_task_id is null
245 end if;
246
247 else -- proceed as normal
248 if p_resource_list_member_id = 0 then
249 if p_task_id = 0 then
250 -- if both task and rlm id are 0, then this row is a PROJECT row
251 l_return_value := 1;
252 else
253 -- p_resource_list_member_id is 0, but p_task_id is not 0 --> TASK row
254 select nvl(parent_task_id, -99)
255 into l_parent_task_id
256 from pa_tasks
257 where task_id = p_task_id and project_id = p_project_id;
258 if l_parent_task_id = -99 then
259 -- this task has no parent; so it must be a top-level task
260 l_return_value := 2;
261 else
262 -- this task has a parent; so the level is one more than the parent
263 select wbs_level
264 into l_parent_wbs_level
265 from pa_tasks
266 where task_id = l_parent_task_id;
267 l_return_value := 1 + l_parent_wbs_level + 1; -- 1 for project, wbs_level, and 1 for child
268 end if; -- parent_task_id is null
269 end if; -- p_task_id=0
270 else
271 -- p_resource_list_member_id is not 0 here
272 select nvl(parent_member_id, -99)
273 into l_res_parent_member_id
274 from pa_resource_list_members
275 where resource_list_member_id = p_resource_list_member_id;
276 if p_task_id = 0 then
277 -- resource is direct descendant of the project
278 if l_res_parent_member_id = -99 then
279 -- resource is RESOURCE PARENT: level = project_level (1) + 1
280 l_return_value := 2;
281 else
282 -- resource is RESOURCE CHILD: level = project_level (1) + 2
283 l_return_value := 3;
284 end if; -- res_parent_member_id is null
285 else
286 -- task_id is not 0, so see if it has a parent
287 select nvl(parent_task_id, -99)
288 into l_parent_task_id
289 from pa_tasks
290 where task_id = p_task_id and project_id = p_project_id;
291 if l_parent_task_id = -99 then
292 if l_res_parent_member_id = -99 then
293 -- this resource is RESOURCE PARENT: assign level to top_task_level (2) + 1
294 l_return_value := 1 + 1 + 1;
295 else
296 -- this resource is a RESOURCE CHILD: assign level to top_task_level (2) + 2
297 l_return_value := 1 + 1 + 2;
298 end if; -- res_parent_member_id is null
299 else
300 select wbs_level
301 into l_parent_wbs_level
302 from pa_tasks
303 where task_id = l_parent_task_id;
304 if l_res_parent_member_id = -99 then
305 -- this resource is RESOURCE PARENT: assign level to parent_task_level + 1 + 1 + 1
306 l_return_value := 1 + l_parent_wbs_level + 1 + 1;
307 else
308 -- this resource is a RESOURCE CHILD: assign level to parent_task_level + 1 + 2 + 1
309 l_return_value := 1 + l_parent_wbs_level + 2 + 1;
310 end if; -- res_parent_member_id is null
311 end if; -- parent_task_id is null
312 end if; -- p_task_id=0
313 end if; -- p_resource_list_member_id=0
314 end if;
315 return l_return_value;
316 END assign_row_level;
317 /* -------------------------------------------------------------------- */
318
319 FUNCTION assign_parent_element
320 (p_project_id IN pa_resource_assignments.project_id%TYPE,
321 p_task_id IN pa_resource_assignments.task_id%TYPE,
322 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE)
323 return VARCHAR2
324 as
325 /* local variables */
326 l_return_value VARCHAR2(2000);
327 l_parent_task_id pa_tasks.parent_task_id%TYPE;
328 l_parent_wbs_level pa_tasks.wbs_level%TYPE;
329 l_res_parent_member_id pa_resource_list_members.parent_member_id%TYPE;
330 l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
331
332 BEGIN
333 l_return_value := -1;
334 -- NEED TO ACCOUNT FOR PROJECT AND TASK LEVEL ROWS THAT ARE USER-ENTERED
335 if p_resource_list_member_id = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id then
336 if p_task_id = 0 then
337 -- this is a PROJECT ROW
338 l_return_value := 'NONE';
339 else
340 -- this is a TASK ROW
341 select nvl(parent_task_id, -99)
342 into l_parent_task_id
343 from pa_tasks
344 where task_id = p_task_id and project_id = p_project_id;
345 if l_parent_task_id = -99 then
346 -- this task has no parent; so it must be a top-level task, so PARENT = PROJECT
347 select name || ' (' || segment1 || ')'
348 into l_return_value
349 from pa_projects_all
350 where project_id = p_project_id;
351 else
352 -- this task has a parent; so PARENT = TASK NAME
353 select task_name || ' (' || task_number || ')'
354 into l_return_value
355 from pa_tasks
356 where task_id = l_parent_task_id;
357 end if; -- parent_task_id is null
358 end if;
359
360 else -- proceed as normal
361 if p_resource_list_member_id = 0 then
362 if p_task_id = 0 then
363 -- if both task and rlm id are 0, then this row is a PROJECT row, so has no parent
364 l_return_value := 'NONE';
365 else
366 -- p_resource_list_member_id is 0, but p_task_id is not 0 --> TASK row
367 select nvl(parent_task_id, -99)
368 into l_parent_task_id
369 from pa_tasks
370 where task_id = p_task_id and project_id = p_project_id;
371 if l_parent_task_id = -99 then
372 -- this task has no parent; so it must be a top-level task, so PARENT = PROJECT
373 select name || ' (' || segment1 || ')'
374 into l_return_value
375 from pa_projects_all
376 where project_id = p_project_id;
377 else
378 -- this task has a parent; so PARENT = TASK NAME
379 select task_name || ' (' || task_number || ')'
380 into l_return_value
381 from pa_tasks
382 where task_id = l_parent_task_id;
383 end if; -- parent_task_id is null
384 end if; -- p_task_id=0
385 else
386 -- p_resource_list_member_id is not 0 here
387 select nvl(parent_member_id, -99),
388 resource_list_id
389 into l_res_parent_member_id,
390 l_resource_list_id
391 from pa_resource_list_members
392 where resource_list_member_id = p_resource_list_member_id;
393 if p_task_id = 0 then
394 -- resource is direct descendant of the project
395 if l_res_parent_member_id = -99 then
396 -- resource is RESOURCE PARENT: PARENT = PROJECT
397 select name || ' (' || segment1 || ')'
398 into l_return_value
399 from pa_projects_all
400 where project_id = p_project_id;
401 else
402 -- resource is RESOURCE CHILD: PARENT = RESOURCE GROUP NAME
403 /*
404 select name
405 into l_return_value
406 from pa_resource_lists
407 where resource_list_id = l_resource_list_id;
408 */
409 select alias
410 into l_return_value
411 from pa_resource_list_members
412 where resource_list_member_id = l_res_parent_member_id;
413 end if; -- res_parent_member_id is null
414 else
415 -- task_id is not 0, so see if it has a parent
416 select nvl(parent_task_id, -99)
417 into l_parent_task_id
418 from pa_tasks
419 where task_id = p_task_id and project_id = p_project_id;
420 if l_parent_task_id = -99 then
421 if l_res_parent_member_id = -99 then
422 -- this resource is RESOURCE PARENT of a top-level task: so PARENT = PROJECT
423 -- updated 11/5/02: PARENT should be TOP-LEVEL-TASK
424 /*
425 select name || ' (' || segment1 || ')'
426 into l_return_value
427 from pa_projects_all
428 where project_id = p_project_id;
429 */
430 select task_name || ' (' || task_number || ')'
431 into l_return_value
432 from pa_tasks
433 where task_id=p_task_id;
434 else
435 -- this resource is a RESOURCE CHILD: so PARENT = RESOURCE GROUP NAME
436 /*
437 select name
438 into l_return_value
439 from pa_resource_lists
440 where resource_list_id = l_resource_list_id;
441 */
442 select alias
443 into l_return_value
444 from pa_resource_list_members
445 where resource_list_member_id = l_res_parent_member_id;
446 end if; -- res_parent_member_id is null
447 else
448 if l_res_parent_member_id = -99 then
449 -- this resource is RESOURCE PARENT: PARENT = PARENT TASK
450 select task_name || ' (' || task_number || ')'
451 into l_return_value
452 from pa_tasks
453 where task_id = p_task_id;
454 -- where task_id = l_parent_task_id;
455 else
456 -- this resource is a RESOURCE CHILD: PARENT = RESOURCE GROUP NAME
457 /*
458 select name
459 into l_return_value
460 from pa_resource_lists
461 where resource_list_id = l_resource_list_id;
462 */
463 select alias
464 into l_return_value
465 from pa_resource_list_members
466 where resource_list_member_id = l_res_parent_member_id;
467 end if; -- res_parent_member_id is null
468 end if; -- parent_task_id is null
469 end if; -- p_task_id=0
470 end if; -- p_resource_list_member_id=0
471 end if;
472 return l_return_value;
473 END assign_parent_element;
474 /* --------------------------------------------------------------------- */
475
476 FUNCTION assign_element_name
477 (p_project_id IN pa_resource_assignments.project_id%TYPE,
478 p_task_id IN pa_resource_assignments.task_id%TYPE,
479 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE)
480 return VARCHAR2
481 is
482 /* local variables */
483 l_return_value VARCHAR2(2000);
484 l_parent_task_id pa_tasks.parent_task_id%TYPE;
485 l_parent_wbs_level pa_tasks.wbs_level%TYPE;
486 l_res_parent_member_id pa_resource_list_members.parent_member_id%TYPE;
487 l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
488
489 BEGIN
490 l_return_value := 'dummy element name';
491 -- NEED TO ACCOUNT FOR PROJECT AND TASK LEVEL ROWS THAT ARE USER-ENTERED
492 if p_resource_list_member_id = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id then
493 if p_task_id = 0 then
494 -- retrieve PROJECT NAME
495 select name || ' (' || segment1 || ')'
496 into l_return_value
497 from pa_projects_all
498 where project_id = p_project_id;
499 else
500 -- get TASK NAME
501 select task_name || ' (' || task_number || ')'
502 into l_return_value
503 from pa_tasks
504 where project_id = p_project_id and
505 task_id = p_task_id;
506 end if;
507
508 else -- proceed as normal
509 if p_resource_list_member_id = 0 then
510 if p_task_id = 0 then
511 -- if both task and rlm id are 0, then this row is a PROJECT row
512 -- retrieve PROJECT NAME
513 select name || ' (' || segment1 || ')'
514 into l_return_value
515 from pa_projects_all
516 where project_id = p_project_id;
517 else
518 -- p_resource_list_member_id is 0, but p_task_id is not 0 --> TASK row
519 -- get TASK NAME
520 select task_name || ' (' || task_number || ')'
521 into l_return_value
522 from pa_tasks
523 where project_id = p_project_id and
524 task_id = p_task_id;
525 end if;
526 else
527 -- check to see if this is a resource list or a resource
528 select nvl(parent_member_id, -99),
529 resource_list_id
530 into l_res_parent_member_id,
531 l_resource_list_id
532 from pa_resource_list_members
533 where resource_list_member_id = p_resource_list_member_id;
534 if l_res_parent_member_id = -99 then
535 -- resource is RESOURCE list
536 -- get RESOURCE GROUP NAME
537 /*
538 select name
539 into l_return_value
540 from pa_resource_lists
541 where resource_list_id = l_resource_list_id;
542 */
543 select alias
544 into l_return_value
545 from pa_resource_list_members
546 where resource_list_member_id = p_resource_list_member_id;
547 else
548 -- resource is RESOURCE child
549 -- get RESOURCE CHILD NAME
550 select alias
551 into l_return_value
552 from pa_resource_list_members
553 where resource_list_member_id = p_resource_list_member_id;
554 end if; -- res_parent_member_id is null
555 end if; -- p_resource_list_member_id=0
556 end if;
557 return l_return_value;
558 END assign_element_name;
559 /* --------------------------------------------------------------------- */
560
561 FUNCTION assign_element_level
562 (p_project_id IN pa_resource_assignments.project_id%TYPE,
563 p_budget_version_id IN pa_resource_assignments.budget_version_id%TYPE,
564 p_task_id IN pa_resource_assignments.task_id%TYPE,
565 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE)
566 return VARCHAR2
567 is
568 /* local variables */
569 l_return_value VARCHAR2(2000);
570 l_parent_task_id pa_tasks.parent_task_id%TYPE;
571 l_parent_wbs_level pa_tasks.wbs_level%TYPE;
572 l_res_parent_member_id pa_resource_list_members.parent_member_id%TYPE;
573 l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
574 l_labor_res_flag pa_resource_assignments.track_as_labor_flag%TYPE;
575
576 BEGIN
577 l_return_value := 'dummy element level';
578
579 -- NEED TO ACCOUNT FOR PROJECT AND TASK LEVEL ROWS THAT ARE USER-ENTERED
580 if p_resource_list_member_id = pa_fp_view_plans_pub.Get_Uncat_Res_List_Member_Id then
581 if p_task_id = 0 then
582 l_return_value := 'PROJECT';
583 else
584 l_return_value := 'TASK';
585 end if;
586 else -- proceed as normal
587 if p_resource_list_member_id = 0 then
588 if p_task_id = 0 then
589 -- if both task and rlm id are 0, then this row is a PROJECT row
590 l_return_value := 'PROJECT';
591 else
592 -- p_resource_list_member_id is 0, but p_task_id is not 0 --> TASK row
593 l_return_value := 'TASK';
594 end if;
595 else
596 -- check to see if this is a resource list or a resource
597 select nvl(parent_member_id, -99),
598 resource_list_id
599 into l_res_parent_member_id,
600 l_resource_list_id
601 from pa_resource_list_members
602 where resource_list_member_id = p_resource_list_member_id;
603 if l_res_parent_member_id = -99 then
604 -- resource is RESOURCE list
605 l_return_value := 'RESOURCE_LIST';
606 else
607 -- resource is RESOURCE child; need to determine if LABOR or NON_LABOR
608 select nvl(track_as_labor_flag, 'N')
609 into l_labor_res_flag
610 from pa_resource_assignments
611 where project_id = p_project_id and
612 budget_version_id = p_budget_version_id and
613 task_id = p_task_id and
614 resource_list_member_id = p_resource_list_member_id;
615 if l_labor_res_flag = 'Y' then
616 l_return_value := 'LABOR_RESOURCE';
617 else
618 l_return_value := 'NON_LABOR_RESOURCE';
619 end if;
620 end if; -- res_parent_member_id is null
621 end if; -- p_resource_list_member_id=0
622 end if;
623 return l_return_value;
624 END assign_element_level;
625 /* --------------------------------------------------------------------- */
626 -- this procedure populates task, resource_group, or resource field for the
627 -- user_entered view (flat hierarchy; no HGrid)
628
629 FUNCTION assign_flat_element_names
630 (p_project_id IN pa_resource_assignments.project_id%TYPE,
631 p_task_id IN pa_resource_assignments.task_id%TYPE,
632 p_resource_list_member_id IN pa_resource_assignments.resource_list_member_id%TYPE,
633 p_element_type IN VARCHAR2)
634 return VARCHAR2
635 is
636 l_return_value VARCHAR2(80);
637 l_resource_name pa_resource_list_members.alias%TYPE;
638 l_res_parent_member_id pa_resource_list_members.parent_member_id%TYPE;
639 l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
640 BEGIN
641 l_return_value := '';
642 if p_element_type = 'TASK' then
643 if p_task_id > 0 then
644 select task_name || ' (' || task_number || ')'
645 into l_return_value
646 from pa_tasks
647 where task_id = p_task_id;
648 end if;
649 else
650 if p_resource_list_member_id > 0 then
651 select alias,
652 nvl(parent_member_id, -99),
653 resource_list_id
654 into l_resource_name,
655 l_res_parent_member_id,
656 l_resource_list_id
657 from pa_resource_list_members
658 where resource_list_member_id = p_resource_list_member_id;
659 if p_element_type = 'RESOURCE' then
660 -- make sure that the element is a resource (ie. has a parent member)
661 if l_res_parent_member_id > 0 then
662 l_return_value := l_resource_name;
663 end if;
664 else
665 -- make sure that the element is a resource group (ie. does not have parent member)
666 if l_res_parent_member_id = -99 then
667 select name
668 into l_return_value
669 from pa_resource_lists
670 where resource_list_id = l_resource_list_id;
671 end if;
672 end if; -- p_element_type = 'RESOURCE'
673 end if;
674 end if; -- p_element_type
675 return l_return_value;
676 END assign_flat_element_names;
677 /* --------------------------------------------------------------------- */
678
679 procedure assign_default_amount
680 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
681 x_default_amount_type_code OUT NOCOPY pa_proj_periods_denorm.amount_type_code%TYPE, --File.Sql.39 bug 4440895
682 x_default_amount_subtype_code OUT NOCOPY pa_proj_periods_denorm.amount_subtype_code%TYPE, --File.Sql.39 bug 4440895
683 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
684 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
685 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
686 is
687 l_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
688 l_budget_version_type VARCHAR2(30); -- used for determining how to rank the amount types
689 l_raw_cost_flag pa_fin_plan_amount_sets.raw_cost_flag%TYPE;
690 l_burdened_cost_flag pa_fin_plan_amount_sets.burdened_cost_flag%TYPE;
691 l_cost_qty_flag pa_fin_plan_amount_sets.cost_qty_flag%TYPE;
692 l_revenue_flag pa_fin_plan_amount_sets.revenue_flag%TYPE;
693 l_revenue_qty_flag pa_fin_plan_amount_sets.revenue_qty_flag%TYPE;
694
695 l_default_amount_type_code pa_proj_periods_denorm.amount_type_code%TYPE;
696 l_default_amount_subtype_code pa_proj_periods_denorm.amount_subtype_code%TYPE;
697 l_msg_count NUMBER := 0;
698 BEGIN
699 select DECODE(fin_plan_preference_code,
700 'COST_ONLY', cost_amount_set_id,
701 'REVENUE_ONLY', revenue_amount_set_id,
702 'COST_AND_REV_SAME', all_amount_set_id,
703 DECODE(nvl(cost_amount_set_id,-1),
704 -1, DECODE(nvl(revenue_amount_set_id, -1),
705 -1, nvl(all_amount_set_id, -1),
706 revenue_amount_set_id),
707 cost_amount_set_id)),
708 DECODE(fin_plan_preference_code,
709 'COST_ONLY', 'COST',
710 'REVENUE_ONLY', 'REVENUE',
711 'COST_AND_REV_SAME', 'BOTH',
712 DECODE(nvl(cost_amount_set_id,-1),
713 -1, DECODE(nvl(revenue_amount_set_id, -1),
714 -1, 'NEITHER',
715 'REVENUE'),
716 'COST'))
717 into l_amount_set_id,
718 l_budget_version_type
719 from pa_proj_fp_options
720 where fin_plan_version_id = p_budget_version_id and
721 fin_plan_option_level_code = 'PLAN_VERSION';
722 -- IF WE FIND NO AMOUNT SET, then we cannot query amount sets table, so throw error
723 if l_amount_set_id = -1 then
724 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
725 p_msg_name => 'PA_FP_NO_AMOUNT_SET_ID');
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 l_msg_count := l_msg_count + 1;
728 x_msg_count := l_msg_count;
729 return;
730 else
731 -- retrieve ALL the relevent flags
732 select raw_cost_flag,
733 burdened_cost_flag,
734 cost_qty_flag,
735 revenue_flag,
736 revenue_qty_flag
737 into l_raw_cost_flag,
738 l_burdened_cost_flag,
739 l_cost_qty_flag,
740 l_revenue_flag,
741 l_revenue_qty_flag
742 from pa_fin_plan_amount_sets
743 where fin_plan_amount_set_id = l_amount_set_id;
744 -- determine the top_ranking amount type, based on the budget version type
745 if l_budget_version_type = 'COST' then
746 if l_raw_cost_flag = 'Y' then
747 l_default_amount_type_code := 'COST';
748 l_default_amount_subtype_code := 'RAW_COST';
749 elsif l_burdened_cost_flag = 'Y' then
750 l_default_amount_type_code := 'COST';
751 l_default_amount_subtype_code := 'BURDENED_COST';
752 elsif l_cost_qty_flag = 'Y' then
753 l_default_amount_type_code := 'COST';
754 l_default_amount_subtype_code := 'QUANTITY';
755 else
756 l_default_amount_type_code := 'NONE';
757 l_default_amount_subtype_code := 'NONE';
758 end if; -- l_budget_version_type = 'COST'
759 elsif l_budget_version_type = 'REVENUE' then
760 if l_revenue_flag = 'Y' then
761 l_default_amount_type_code := 'REVENUE';
762 l_default_amount_subtype_code := 'REVENUE';
763 elsif l_revenue_qty_flag = 'Y' then
764 l_default_amount_type_code := 'QUANTITY';
765 l_default_amount_subtype_code := 'QUANTITY';
766 else
767 l_default_amount_type_code := 'NONE';
768 l_default_amount_subtype_code := 'NONE';
769 end if; -- l_budget_version_type = 'REVENUE'
770 else
771 if l_revenue_flag = 'Y' then
772 l_default_amount_type_code := 'REVENUE';
773 l_default_amount_subtype_code := 'REVENUE';
774 elsif l_raw_cost_flag = 'Y' then
775 l_default_amount_type_code := 'COST';
776 l_default_amount_subtype_code := 'RAW_COST';
777 elsif l_burdened_cost_flag = 'Y' then
778 l_default_amount_type_code := 'COST';
779 l_default_amount_subtype_code := 'BURDENED_COST';
780 elsif (l_cost_qty_flag = 'Y') or (l_revenue_qty_flag = 'Y') then
781 l_default_amount_type_code := 'QUANTITY';
782 l_default_amount_subtype_code := 'QUANTITY';
783 else
784 l_default_amount_type_code := 'NONE';
785 l_default_amount_subtype_code := 'NONE';
786 end if; -- l_budget_version_type = 'BOTH' or 'NEITHER'
787 end if; -- l_budget_version_type
788 end if;
789 x_default_amount_type_code := l_default_amount_type_code;
790 x_default_amount_subtype_code := l_default_amount_subtype_code;
791 EXCEPTION
792 when others then
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794 x_msg_count := 1;
795 x_msg_data := SQLERRM;
796 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_VIEW_PLANS_PUB',
797 p_procedure_name => 'Assign_Default_Amount');
798 pa_debug.reset_err_stack;
799 return;
800 END assign_default_amount;
801 /* --------------------------------------------------------------------- */
802 -- CHANGE HISTORY:
803 -- 12/26/02 dlai added two new conditions:
804 -- p_period_number=0 --> preceding_periods_amount
805 -- p_period_number=14 --> succeeding_periods_amount
806 function get_period_n_value
807 (p_period_profile_id IN pa_proj_period_profiles.period_profile_id%TYPE,
808 p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
809 p_resource_assignment_id IN pa_proj_periods_denorm.resource_assignment_id%TYPE,
810 p_project_currency_type IN VARCHAR2,
811 p_amount_type_id IN pa_proj_periods_denorm.amount_type_id%TYPE,
812 p_period_number IN NUMBER) return NUMBER
813 is
814 l_return_value NUMBER;
815
816 begin
817 --hr_utility.trace('entering');
818 --hr_utility.trace('date= ' || pa_fp_view_plans_pub.Get_Period_Start_Date1());
819 -- *** PERFORMANCE ISSUE 2773408: get budget_version_id to use index
820 -- PA_PROJECT_PERIODS_DENORM_N1
821
822 if p_period_number = 1 then
823 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date1(),
824 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
825 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
826 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
827 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
828 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
829 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
830 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
831 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
832 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
833 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
834 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
835 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
836 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
837 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
838 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
839 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
840 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
841 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
842 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
843 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
844 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
845 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
846 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
847 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
848 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
849 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
850 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
851 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
852 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
853 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
854 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
855 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
856 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
857 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
858 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
859 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
860 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
861 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
862 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
863 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
864 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
865 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
866 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
867 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
868 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
869 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
870 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
871 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
872 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
873 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
874 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
875 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
876 null))
877 into l_return_value
878 from pa_proj_period_profiles pppp,
879 pa_proj_periods_denorm pppd
880 where pppp.period_profile_id = p_period_profile_id and
881 pppp.period_profile_id=pppd.period_profile_id and
882 pppd.budget_version_id = p_budget_version_id and
883 pppd.resource_assignment_id = p_resource_assignment_id and
884 pppd.amount_subtype_id = p_amount_type_id and
885 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
886 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
887 --group by pppd.amount_subtype_id;
888
889 elsif p_period_number = 2 then
890 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date2(),
891 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
892 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
893 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
894 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
895 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
896 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
897 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
898 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
899 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
900 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
901 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
902 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
903 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
904 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
905 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
906 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
907 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
908 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
909 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
910 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
911 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
912 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
913 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
914 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
915 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
916 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
917 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
918 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
919 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
920 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
921 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
922 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
923 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
924 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
925 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
926 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
927 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
928 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
929 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
930 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
931 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
932 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
933 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
934 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
935 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
936 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
937 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
938 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
939 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
940 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
941 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
942 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
943 null))
944 into l_return_value
945 from pa_proj_period_profiles pppp,
946 pa_proj_periods_denorm pppd
947 where pppp.period_profile_id = p_period_profile_id and
948 pppp.period_profile_id=pppd.period_profile_id and
949 pppd.budget_version_id = p_budget_version_id and
950 pppd.resource_assignment_id = p_resource_assignment_id and
951 pppd.amount_subtype_id = p_amount_type_id and
952 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
953 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
954 --group by pppd.amount_subtype_id;
955
956 elsif p_period_number = 3 then
957 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date3(),
958 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
959 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
960 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
961 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
962 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
963 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
964 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
965 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
966 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
967 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
968 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
969 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
970 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
971 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
972 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
973 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
974 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
975 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
976 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
977 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
978 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
979 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
980 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
981 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
982 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
983 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
984 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
985 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
986 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
987 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
988 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
989 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
990 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
991 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
992 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
993 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
994 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
995 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
996 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
997 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
998 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
999 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1000 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1001 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1002 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1003 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1004 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1005 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1006 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1007 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1008 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1009 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1010 null))
1011 into l_return_value
1012 from pa_proj_period_profiles pppp,
1013 pa_proj_periods_denorm pppd
1014 where pppp.period_profile_id = p_period_profile_id and
1015 pppp.period_profile_id=pppd.period_profile_id and
1016 pppd.budget_version_id = p_budget_version_id and
1017 pppd.resource_assignment_id = p_resource_assignment_id and
1018 pppd.amount_subtype_id = p_amount_type_id and
1019 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1020 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1021 --group by pppd.amount_subtype_id;
1022
1023 elsif p_period_number = 4 then
1024 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date4(),
1025 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1026 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1027 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1028 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1029 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1030 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1031 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1032 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1033 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1034 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1035 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1036 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1037 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1038 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1039 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1040 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1041 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1042 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1043 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1044 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1045 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1046 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1047 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1048 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1049 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1050 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1051 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1052 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1053 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1054 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1055 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1056 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1057 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1058 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1059 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1060 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1061 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1062 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1063 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1064 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1065 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1066 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1067 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1068 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1069 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1070 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1071 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1072 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1073 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1074 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1075 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1076 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1077 null))
1078 into l_return_value
1079 from pa_proj_period_profiles pppp,
1080 pa_proj_periods_denorm pppd
1081 where pppp.period_profile_id = p_period_profile_id and
1082 pppp.period_profile_id=pppd.period_profile_id and
1083 pppd.budget_version_id = p_budget_version_id and
1084 pppd.resource_assignment_id = p_resource_assignment_id and
1085 pppd.amount_subtype_id = p_amount_type_id and
1086 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1087 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1088 --group by pppd.amount_subtype_id;
1089
1090 elsif p_period_number = 5 then
1091 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date5(),
1092 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1093 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1094 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1095 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1096 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1097 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1098 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1099 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1100 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1101 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1102 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1103 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1104 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1105 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1106 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1107 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1108 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1109 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1110 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1111 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1112 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1113 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1114 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1115 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1116 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1117 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1118 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1119 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1120 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1121 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1122 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1123 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1124 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1125 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1126 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1127 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1128 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1129 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1130 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1131 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1132 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1133 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1134 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1135 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1136 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1137 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1138 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1139 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1140 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1141 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1142 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1143 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1144 null))
1145 into l_return_value
1146 from pa_proj_period_profiles pppp,
1147 pa_proj_periods_denorm pppd
1148 where pppp.period_profile_id = p_period_profile_id and
1149 pppp.period_profile_id=pppd.period_profile_id and
1150 pppd.budget_version_id = p_budget_version_id and
1151 pppd.resource_assignment_id = p_resource_assignment_id and
1152 pppd.amount_subtype_id = p_amount_type_id and
1153 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1154 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1155 --group by pppd.amount_subtype_id;
1156
1157 elsif p_period_number = 6 then
1158 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date6(),
1159 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1160 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1161 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1162 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1163 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1164 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1165 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1166 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1167 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1168 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1169 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1170 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1171 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1172 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1173 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1174 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1175 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1176 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1177 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1178 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1179 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1180 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1181 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1182 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1183 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1184 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1185 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1186 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1187 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1188 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1189 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1190 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1191 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1192 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1193 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1194 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1195 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1196 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1197 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1198 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1199 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1200 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1201 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1202 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1203 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1204 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1205 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1206 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1207 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1208 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1209 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1210 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1211 null))
1212 into l_return_value
1213 from pa_proj_period_profiles pppp,
1214 pa_proj_periods_denorm pppd
1215 where pppp.period_profile_id = p_period_profile_id and
1216 pppp.period_profile_id=pppd.period_profile_id and
1217 pppd.budget_version_id = p_budget_version_id and
1218 pppd.resource_assignment_id = p_resource_assignment_id and
1219 pppd.amount_subtype_id = p_amount_type_id and
1220 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1221 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1222 --group by pppd.amount_subtype_id;
1223
1224 elsif p_period_number = 7 then
1225 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date7(),
1226 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1227 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1228 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1229 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1230 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1231 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1232 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1233 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1234 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1235 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1236 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1237 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1238 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1239 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1240 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1241 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1242 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1243 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1244 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1245 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1246 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1247 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1248 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1249 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1250 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1251 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1252 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1253 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1254 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1255 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1256 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1257 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1258 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1259 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1260 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1261 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1262 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1263 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1264 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1265 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1266 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1267 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1268 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1269 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1270 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1271 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1272 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1273 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1274 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1275 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1276 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1277 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1278 null))
1279 into l_return_value
1280 from pa_proj_period_profiles pppp,
1281 pa_proj_periods_denorm pppd
1282 where pppp.period_profile_id = p_period_profile_id and
1283 pppp.period_profile_id=pppd.period_profile_id and
1284 pppd.budget_version_id = p_budget_version_id and
1285 pppd.resource_assignment_id = p_resource_assignment_id and
1286 pppd.amount_subtype_id = p_amount_type_id and
1287 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1288 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1289 --group by pppd.amount_subtype_id;
1290
1291 elsif p_period_number = 8 then
1292 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date8(),
1293 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1294 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1295 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1296 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1297 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1298 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1299 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1300 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1301 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1302 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1303 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1304 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1305 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1306 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1307 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1308 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1309 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1310 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1311 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1312 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1313 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1314 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1315 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1316 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1317 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1318 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1319 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1320 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1321 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1322 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1323 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1324 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1325 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1326 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1327 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1328 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1329 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1330 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1331 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1332 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1333 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1334 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1335 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1336 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1337 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1338 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1339 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1340 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1341 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1342 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1343 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1344 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1345 null))
1346 into l_return_value
1347 from pa_proj_period_profiles pppp,
1348 pa_proj_periods_denorm pppd
1349 where pppp.period_profile_id = p_period_profile_id and
1350 pppp.period_profile_id=pppd.period_profile_id and
1351 pppd.budget_version_id = p_budget_version_id and
1352 pppd.resource_assignment_id = p_resource_assignment_id and
1353 pppd.amount_subtype_id = p_amount_type_id and
1354 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1355 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1356 --group by pppd.amount_subtype_id;
1357
1358 elsif p_period_number = 9 then
1359 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date9(),
1360 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1361 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1362 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1363 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1364 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1365 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1366 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1367 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1368 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1369 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1370 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1371 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1372 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1373 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1374 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1375 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1376 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1377 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1378 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1379 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1380 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1381 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1382 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1383 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1384 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1385 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1386 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1387 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1388 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1389 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1390 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1391 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1392 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1393 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1394 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1395 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1396 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1397 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1398 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1399 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1400 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1401 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1402 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1403 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1404 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1405 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1406 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1407 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1408 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1409 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1410 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1411 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1412 null))
1413 into l_return_value
1414 from pa_proj_period_profiles pppp,
1415 pa_proj_periods_denorm pppd
1416 where pppp.period_profile_id = p_period_profile_id and
1417 pppp.period_profile_id=pppd.period_profile_id and
1418 pppd.budget_version_id = p_budget_version_id and
1419 pppd.resource_assignment_id = p_resource_assignment_id and
1420 pppd.amount_subtype_id = p_amount_type_id and
1421 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1422 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1423 --group by pppd.amount_subtype_id;
1424
1425 elsif p_period_number = 10 then
1426 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date10(),
1427 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1428 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1429 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1430 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1431 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1432 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1433 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1434 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1435 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1436 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1437 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1438 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1439 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1440 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1441 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1442 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1443 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1444 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1445 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1446 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1447 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1448 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1449 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1450 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1451 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1452 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1453 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1454 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1455 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1456 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1457 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1458 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1459 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1460 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1461 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1462 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1463 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1464 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1465 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1466 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1467 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1468 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1469 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1470 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1471 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1472 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1473 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1474 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1475 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1476 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1477 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1478 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1479 null))
1480 into l_return_value
1481 from pa_proj_period_profiles pppp,
1482 pa_proj_periods_denorm pppd
1483 where pppp.period_profile_id = p_period_profile_id and
1484 pppp.period_profile_id=pppd.period_profile_id and
1485 pppd.budget_version_id = p_budget_version_id and
1486 pppd.resource_assignment_id = p_resource_assignment_id and
1487 pppd.amount_subtype_id = p_amount_type_id and
1488 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1489 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1490 --group by pppd.amount_subtype_id;
1491
1492 elsif p_period_number = 11 then
1493 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date11(),
1494 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1495 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1496 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1497 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1498 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1499 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1500 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1501 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1502 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1503 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1504 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1505 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1506 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1507 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1508 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1509 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1510 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1511 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1512 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1513 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1514 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1515 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1516 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1517 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1518 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1519 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1520 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1521 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1522 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1523 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1524 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1525 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1526 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1527 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1528 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1529 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1530 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1531 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1532 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1533 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1534 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1535 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1536 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1537 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1538 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1539 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1540 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1541 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1542 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1543 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1544 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1545 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1546 null))
1547 into l_return_value
1548 from pa_proj_period_profiles pppp,
1549 pa_proj_periods_denorm pppd
1550 where pppp.period_profile_id = p_period_profile_id and
1551 pppp.period_profile_id=pppd.period_profile_id and
1552 pppd.budget_version_id = p_budget_version_id and
1553 pppd.resource_assignment_id = p_resource_assignment_id and
1554 pppd.amount_subtype_id = p_amount_type_id and
1555 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1556 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1557 --group by pppd.amount_subtype_id;
1558
1559 elsif p_period_number = 12 then
1560 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date12(),
1561 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1562 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1563 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1564 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1565 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1566 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1567 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1568 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1569 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1570 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1571 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1572 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1573 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1574 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1575 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1576 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1577 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1578 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1579 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1580 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1581 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1582 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1583 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1584 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1585 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1586 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1587 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1588 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1589 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1590 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1591 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1592 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1593 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1594 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1595 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1596 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1597 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1598 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1599 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1600 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1601 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1602 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1603 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1604 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1605 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1606 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1607 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1608 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1609 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1610 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1611 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1612 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1613 null))
1614 into l_return_value
1615 from pa_proj_period_profiles pppp,
1616 pa_proj_periods_denorm pppd
1617 where pppp.period_profile_id = p_period_profile_id and
1618 pppp.period_profile_id=pppd.period_profile_id and
1619 pppd.budget_version_id = p_budget_version_id and
1620 pppd.resource_assignment_id = p_resource_assignment_id and
1621 pppd.amount_subtype_id = p_amount_type_id and
1622 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1623 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1624 --group by pppd.amount_subtype_id;
1625
1626 elsif p_period_number = 13 then
1627 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date13(),
1628 pppp.period1_start_Date, nvl(pppd.period_amount1,0),
1629 pppp.period2_start_Date, nvl(pppd.period_amount2,0),
1630 pppp.period3_start_Date, nvl(pppd.period_amount3,0),
1631 pppp.period4_start_Date, nvl(pppd.period_amount4,0),
1632 pppp.period5_start_Date, nvl(pppd.period_amount5,0),
1633 pppp.period6_start_Date, nvl(pppd.period_amount6,0),
1634 pppp.period7_start_Date, nvl(pppd.period_amount7,0),
1635 pppp.period8_start_Date, nvl(pppd.period_amount8,0),
1636 pppp.period9_start_Date, nvl(pppd.period_amount9,0),
1637 pppp.period10_start_Date, nvl(pppd.period_amount10,0),
1638 pppp.period11_start_Date, nvl(pppd.period_amount11,0),
1639 pppp.period12_start_Date, nvl(pppd.period_amount12,0),
1640 pppp.period13_start_Date, nvl(pppd.period_amount13,0),
1641 pppp.period14_start_Date, nvl(pppd.period_amount14,0),
1642 pppp.period15_start_Date, nvl(pppd.period_amount15,0),
1643 pppp.period16_start_Date, nvl(pppd.period_amount16,0),
1644 pppp.period17_start_Date, nvl(pppd.period_amount17,0),
1645 pppp.period18_start_Date, nvl(pppd.period_amount18,0),
1646 pppp.period19_start_Date, nvl(pppd.period_amount19,0),
1647 pppp.period20_start_Date, nvl(pppd.period_amount20,0),
1648 pppp.period21_start_Date, nvl(pppd.period_amount21,0),
1649 pppp.period22_start_Date, nvl(pppd.period_amount22,0),
1650 pppp.period23_start_Date, nvl(pppd.period_amount23,0),
1651 pppp.period24_start_Date, nvl(pppd.period_amount24,0),
1652 pppp.period25_start_Date, nvl(pppd.period_amount25,0),
1653 pppp.period26_start_Date, nvl(pppd.period_amount26,0),
1654 pppp.period27_start_Date, nvl(pppd.period_amount27,0),
1655 pppp.period28_start_Date, nvl(pppd.period_amount28,0),
1656 pppp.period29_start_Date, nvl(pppd.period_amount29,0),
1657 pppp.period30_start_Date, nvl(pppd.period_amount30,0),
1658 pppp.period31_start_Date, nvl(pppd.period_amount31,0),
1659 pppp.period32_start_Date, nvl(pppd.period_amount32,0),
1660 pppp.period33_start_Date, nvl(pppd.period_amount33,0),
1661 pppp.period34_start_Date, nvl(pppd.period_amount34,0),
1662 pppp.period35_start_Date, nvl(pppd.period_amount35,0),
1663 pppp.period36_start_Date, nvl(pppd.period_amount36,0),
1664 pppp.period37_start_Date, nvl(pppd.period_amount37,0),
1665 pppp.period38_start_Date, nvl(pppd.period_amount38,0),
1666 pppp.period39_start_Date, nvl(pppd.period_amount39,0),
1667 pppp.period40_start_Date, nvl(pppd.period_amount40,0),
1668 pppp.period41_start_Date, nvl(pppd.period_amount41,0),
1669 pppp.period42_start_Date, nvl(pppd.period_amount42,0),
1670 pppp.period43_start_Date, nvl(pppd.period_amount43,0),
1671 pppp.period44_start_Date, nvl(pppd.period_amount44,0),
1672 pppp.period45_start_Date, nvl(pppd.period_amount45,0),
1673 pppp.period46_start_Date, nvl(pppd.period_amount46,0),
1674 pppp.period47_start_Date, nvl(pppd.period_amount47,0),
1675 pppp.period48_start_Date, nvl(pppd.period_amount48,0),
1676 pppp.period49_start_Date, nvl(pppd.period_amount49,0),
1677 pppp.period50_start_Date, nvl(pppd.period_amount50,0),
1678 pppp.period51_start_Date, nvl(pppd.period_amount51,0),
1679 pppp.period52_start_Date, nvl(pppd.period_amount52,0),
1680 null))
1681 into l_return_value
1682 from pa_proj_period_profiles pppp,
1683 pa_proj_periods_denorm pppd
1684 where pppp.period_profile_id = p_period_profile_id and
1685 pppp.period_profile_id=pppd.period_profile_id and
1686 pppd.budget_version_id = p_budget_version_id and
1687 pppd.resource_assignment_id = p_resource_assignment_id and
1688 pppd.amount_subtype_id = p_amount_type_id and
1689 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1690 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1691 --group by pppd.amount_subtype_id;
1692 elsif p_period_number = 0 then
1693 select SUM(nvl(pppd.preceding_periods_amount,0))
1694 into l_return_value
1695 from pa_proj_period_profiles pppp,
1696 pa_proj_periods_denorm pppd
1697 where pppp.period_profile_id = p_period_profile_id and
1698 pppp.period_profile_id=pppd.period_profile_id and
1699 pppd.budget_version_id = p_budget_version_id and
1700 pppd.resource_assignment_id = p_resource_assignment_id and
1701 pppd.amount_subtype_id = p_amount_type_id and
1702 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1703 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1704 --group by pppd.amount_subtype_id;
1705 elsif p_period_number = 14 then
1706 select SUM(nvl(pppd.succeeding_periods_amount,0))
1707 into l_return_value
1708 from pa_proj_period_profiles pppp,
1709 pa_proj_periods_denorm pppd
1710 where pppp.period_profile_id = p_period_profile_id and
1711 pppp.period_profile_id=pppd.period_profile_id and
1712 pppd.budget_version_id = p_budget_version_id and
1713 pppd.resource_assignment_id = p_resource_assignment_id and
1714 pppd.amount_subtype_id = p_amount_type_id and
1715 ((p_amount_type_id=215 and pppd.currency_type='TRANSACTION') or
1716 (p_amount_type_id <> 215 and pppd.currency_type = p_project_currency_type));
1717 --group by pppd.amount_subtype_id;
1718 end if;
1719 return l_return_value;
1720
1721 EXCEPTION
1722 WHEN NO_DATA_FOUND THEN
1723 return null;
1724 WHEN TOO_MANY_ROWS THEN
1725 pa_debug.write_file('get_period_n_value: p_period_profile_id= ' ||
1726 to_char(p_period_profile_id));
1727 pa_debug.write_file('get_period_n_value: p_amount_type_id= ' ||
1728 to_char(p_amount_type_id));
1729 pa_debug.write_file('get_period_n_value: p_resource_assignment_id= ' ||
1730 to_char(p_resource_assignment_id));
1731 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fp_view_plans_util',
1732 p_procedure_name => 'get_period_n_value: TOO_MANY_ROWS');
1733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1734 WHEN OTHERS THEN
1735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1736 end get_period_n_value;
1737
1738
1739 /* THIS IMPLEMENTATION IS INCORRECT BECAUSE WE'RE TRYING TO INCORPORATE
1740 * ORG FORECASTING VALUES
1741
1742 function get_period_n_value
1743 (p_period_profile_id IN pa_proj_period_profiles.period_profile_id%TYPE,
1744 p_resource_assignment_id IN pa_proj_periods_denorm.resource_assignment_id%TYPE,
1745 p_project_currency_type IN VARCHAR2,
1746 p_amount_type_id IN pa_proj_periods_denorm.amount_type_id%TYPE,
1747 p_period_number IN NUMBER) return NUMBER
1748 is
1749 l_return_value NUMBER;
1750
1751 begin
1752 -- we use SUM(DECODE) because for COST or REVENUE amount type, there's
1753 -- one AMOUNT_SUBTYPE_ID which will require us to subtract instead of add
1754 -- COST: TP_COST_OUT (185)
1755 -- REVENUE: TP_REVENUE_OUT (120)
1756 --hr_utility.trace('pa_fp_view_plans_pub.Get_Period_Start_Date1()= ' ||
1757 to_char(pa_fp_view_plans_pub.Get_Period_Start_Date1()));
1758 if p_period_number = 1 then
1759 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date1(),
1760 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
1761 185, -pppd.period_amount1,
1762 120, -pppd.period_amount1,
1763 pppd.period_amount1),
1764 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
1765 185, -pppd.period_amount2,
1766 120, -pppd.period_amount2,
1767 pppd.period_amount2),
1768 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
1769 185, -pppd.period_amount3,
1770 120, -pppd.period_amount3,
1771 pppd.period_amount3),
1772 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
1773 185, -pppd.period_amount4,
1774 120, -pppd.period_amount4,
1775 pppd.period_amount4),
1776 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
1777 185, -pppd.period_amount5,
1778 120, -pppd.period_amount5,
1779 pppd.period_amount5),
1780 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
1781 185, -pppd.period_amount6,
1782 120, -pppd.period_amount6,
1783 pppd.period_amount6),
1784 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
1785 185, -pppd.period_amount7,
1786 120, -pppd.period_amount7,
1787 pppd.period_amount7),
1788 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
1789 185, -pppd.period_amount8,
1790 120, -pppd.period_amount8,
1791 pppd.period_amount8),
1792 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
1793 185, -pppd.period_amount9,
1794 120, -pppd.period_amount9,
1795 pppd.period_amount9),
1796 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
1797 185, -pppd.period_amount10,
1798 120, -pppd.period_amount10,
1799 pppd.period_amount10),
1800 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
1801 185, -pppd.period_amount11,
1802 120, -pppd.period_amount11,
1803 pppd.period_amount11),
1804 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
1805 185, -pppd.period_amount12,
1806 120, -pppd.period_amount12,
1807 pppd.period_amount12),
1808 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
1809 185, -pppd.period_amount13,
1810 120, -pppd.period_amount13,
1811 pppd.period_amount13),
1812 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
1813 185, -pppd.period_amount14,
1814 120, -pppd.period_amount14,
1815 pppd.period_amount14),
1816 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
1817 185, -pppd.period_amount15,
1818 120, -pppd.period_amount15,
1819 pppd.period_amount15),
1820 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
1821 185, -pppd.period_amount16,
1822 120, -pppd.period_amount16,
1823 pppd.period_amount16),
1824 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
1825 185, -pppd.period_amount17,
1826 120, -pppd.period_amount17,
1827 pppd.period_amount17),
1828 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
1829 185, -pppd.period_amount18,
1830 120, -pppd.period_amount18,
1831 pppd.period_amount18),
1832 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
1833 185, -pppd.period_amount19,
1834 120, -pppd.period_amount19,
1835 pppd.period_amount19),
1836 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
1837 185, -pppd.period_amount20,
1838 120, -pppd.period_amount20,
1839 pppd.period_amount20),
1840 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
1841 185, -pppd.period_amount21,
1842 120, -pppd.period_amount21,
1843 pppd.period_amount21),
1844 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
1845 185, -pppd.period_amount22,
1846 120, -pppd.period_amount22,
1847 pppd.period_amount22),
1848 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
1849 185, -pppd.period_amount23,
1850 120, -pppd.period_amount23,
1851 pppd.period_amount23),
1852 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
1853 185, -pppd.period_amount24,
1854 120, -pppd.period_amount24,
1855 pppd.period_amount24),
1856 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
1857 185, -pppd.period_amount25,
1858 120, -pppd.period_amount25,
1859 pppd.period_amount25),
1860 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
1861 185, -pppd.period_amount26,
1862 120, -pppd.period_amount26,
1863 pppd.period_amount26),
1864 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
1865 185, -pppd.period_amount27,
1866 120, -pppd.period_amount27,
1867 pppd.period_amount27),
1868 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
1869 185, -pppd.period_amount28,
1870 120, -pppd.period_amount28,
1871 pppd.period_amount28),
1872 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
1873 185, -pppd.period_amount29,
1874 120, -pppd.period_amount29,
1875 pppd.period_amount29),
1876 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
1877 185, -pppd.period_amount30,
1878 120, -pppd.period_amount30,
1879 pppd.period_amount30),
1880 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
1881 185, -pppd.period_amount31,
1882 120, -pppd.period_amount31,
1883 pppd.period_amount31),
1884 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
1885 185, -pppd.period_amount32,
1886 120, -pppd.period_amount32,
1887 pppd.period_amount32),
1888 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
1889 185, -pppd.period_amount33,
1890 120, -pppd.period_amount33,
1891 pppd.period_amount33),
1892 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
1893 185, -pppd.period_amount34,
1894 120, -pppd.period_amount34,
1895 pppd.period_amount34),
1896 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
1897 185, -pppd.period_amount35,
1898 120, -pppd.period_amount35,
1899 pppd.period_amount35),
1900 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
1901 185, -pppd.period_amount36,
1902 120, -pppd.period_amount36,
1903 pppd.period_amount36),
1904 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
1905 185, -pppd.period_amount37,
1906 120, -pppd.period_amount37,
1907 pppd.period_amount37),
1908 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
1909 185, -pppd.period_amount38,
1910 120, -pppd.period_amount38,
1911 pppd.period_amount38),
1912 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
1913 185, -pppd.period_amount39,
1914 120, -pppd.period_amount39,
1915 pppd.period_amount39),
1916 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
1917 185, -pppd.period_amount40,
1918 120, -pppd.period_amount40,
1919 pppd.period_amount40),
1920 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
1921 185, -pppd.period_amount41,
1922 120, -pppd.period_amount41,
1923 pppd.period_amount41),
1924 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
1925 185, -pppd.period_amount42,
1926 120, -pppd.period_amount42,
1927 pppd.period_amount42),
1928 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
1929 185, -pppd.period_amount43,
1930 120, -pppd.period_amount43,
1931 pppd.period_amount43),
1932 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
1933 185, -pppd.period_amount44,
1934 120, -pppd.period_amount44,
1935 pppd.period_amount44),
1936 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
1937 185, -pppd.period_amount45,
1938 120, -pppd.period_amount45,
1939 pppd.period_amount45),
1940 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
1941 185, -pppd.period_amount46,
1942 120, -pppd.period_amount46,
1943 pppd.period_amount46),
1944 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
1945 185, -pppd.period_amount47,
1946 120, -pppd.period_amount47,
1947 pppd.period_amount47),
1948 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
1949 185, -pppd.period_amount48,
1950 120, -pppd.period_amount48,
1951 pppd.period_amount48),
1952 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
1953 185, -pppd.period_amount49,
1954 120, -pppd.period_amount49,
1955 pppd.period_amount49),
1956 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
1957 185, -pppd.period_amount50,
1958 120, -pppd.period_amount50,
1959 pppd.period_amount50),
1960 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
1961 185, -pppd.period_amount51,
1962 120, -pppd.period_amount51,
1963 pppd.period_amount51),
1964 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
1965 185, -pppd.period_amount52,
1966 120, -pppd.period_amount52,
1967 pppd.period_amount52),0))
1968 into l_return_value
1969 from pa_proj_period_profiles pppp,
1970 pa_proj_periods_denorm pppd
1971 where pppp.period_profile_id = p_period_profile_id and
1972 pppp.period_profile_id=pppd.period_profile_id and
1973 pppd.amount_type_id = p_amount_type_id and
1974 pppd.resource_assignment_id = p_resource_assignment_id and
1975 pppd.currency_type = p_project_currency_type
1976 group by pppd.amount_type_id;
1977
1978 elsif p_period_number = 2 then
1979 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date2(),
1980 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
1981 185, -pppd.period_amount1,
1982 120, -pppd.period_amount1,
1983 pppd.period_amount1),
1984 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
1985 185, -pppd.period_amount2,
1986 120, -pppd.period_amount2,
1987 pppd.period_amount2),
1988 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
1989 185, -pppd.period_amount3,
1990 120, -pppd.period_amount3,
1991 pppd.period_amount3),
1992 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
1993 185, -pppd.period_amount4,
1994 120, -pppd.period_amount4,
1995 pppd.period_amount4),
1996 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
1997 185, -pppd.period_amount5,
1998 120, -pppd.period_amount5,
1999 pppd.period_amount5),
2000 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
2001 185, -pppd.period_amount6,
2002 120, -pppd.period_amount6,
2003 pppd.period_amount6),
2004 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
2005 185, -pppd.period_amount7,
2006 120, -pppd.period_amount7,
2007 pppd.period_amount7),
2008 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
2009 185, -pppd.period_amount8,
2010 120, -pppd.period_amount8,
2011 pppd.period_amount8),
2012 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
2013 185, -pppd.period_amount9,
2014 120, -pppd.period_amount9,
2015 pppd.period_amount9),
2016 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
2017 185, -pppd.period_amount10,
2018 120, -pppd.period_amount10,
2019 pppd.period_amount10),
2020 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
2021 185, -pppd.period_amount11,
2022 120, -pppd.period_amount11,
2023 pppd.period_amount11),
2024 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
2025 185, -pppd.period_amount12,
2026 120, -pppd.period_amount12,
2027 pppd.period_amount12),
2028 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
2029 185, -pppd.period_amount13,
2030 120, -pppd.period_amount13,
2031 pppd.period_amount13),
2032 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
2033 185, -pppd.period_amount14,
2034 120, -pppd.period_amount14,
2035 pppd.period_amount14),
2036 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
2037 185, -pppd.period_amount15,
2038 120, -pppd.period_amount15,
2039 pppd.period_amount15),
2040 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
2041 185, -pppd.period_amount16,
2042 120, -pppd.period_amount16,
2043 pppd.period_amount16),
2044 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
2045 185, -pppd.period_amount17,
2046 120, -pppd.period_amount17,
2047 pppd.period_amount17),
2048 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
2049 185, -pppd.period_amount18,
2050 120, -pppd.period_amount18,
2051 pppd.period_amount18),
2052 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
2053 185, -pppd.period_amount19,
2054 120, -pppd.period_amount19,
2055 pppd.period_amount19),
2056 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
2057 185, -pppd.period_amount20,
2058 120, -pppd.period_amount20,
2059 pppd.period_amount20),
2060 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
2061 185, -pppd.period_amount21,
2062 120, -pppd.period_amount21,
2063 pppd.period_amount21),
2064 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
2065 185, -pppd.period_amount22,
2066 120, -pppd.period_amount22,
2067 pppd.period_amount22),
2068 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
2069 185, -pppd.period_amount23,
2070 120, -pppd.period_amount23,
2071 pppd.period_amount23),
2072 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
2073 185, -pppd.period_amount24,
2074 120, -pppd.period_amount24,
2075 pppd.period_amount24),
2076 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
2077 185, -pppd.period_amount25,
2078 120, -pppd.period_amount25,
2079 pppd.period_amount25),
2080 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
2081 185, -pppd.period_amount26,
2082 120, -pppd.period_amount26,
2083 pppd.period_amount26),
2084 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
2085 185, -pppd.period_amount27,
2086 120, -pppd.period_amount27,
2087 pppd.period_amount27),
2088 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
2089 185, -pppd.period_amount28,
2090 120, -pppd.period_amount28,
2091 pppd.period_amount28),
2092 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
2093 185, -pppd.period_amount29,
2094 120, -pppd.period_amount29,
2095 pppd.period_amount29),
2096 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
2097 185, -pppd.period_amount30,
2098 120, -pppd.period_amount30,
2099 pppd.period_amount30),
2100 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
2101 185, -pppd.period_amount31,
2102 120, -pppd.period_amount31,
2103 pppd.period_amount31),
2104 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
2105 185, -pppd.period_amount32,
2106 120, -pppd.period_amount32,
2107 pppd.period_amount32),
2108 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
2109 185, -pppd.period_amount33,
2110 120, -pppd.period_amount33,
2111 pppd.period_amount33),
2112 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
2113 185, -pppd.period_amount34,
2114 120, -pppd.period_amount34,
2115 pppd.period_amount34),
2116 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
2117 185, -pppd.period_amount35,
2118 120, -pppd.period_amount35,
2119 pppd.period_amount35),
2120 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
2121 185, -pppd.period_amount36,
2122 120, -pppd.period_amount36,
2123 pppd.period_amount36),
2124 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
2125 185, -pppd.period_amount37,
2126 120, -pppd.period_amount37,
2127 pppd.period_amount37),
2128 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
2129 185, -pppd.period_amount38,
2130 120, -pppd.period_amount38,
2131 pppd.period_amount38),
2132 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
2133 185, -pppd.period_amount39,
2134 120, -pppd.period_amount39,
2135 pppd.period_amount39),
2136 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
2137 185, -pppd.period_amount40,
2138 120, -pppd.period_amount40,
2139 pppd.period_amount40),
2140 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
2141 185, -pppd.period_amount41,
2142 120, -pppd.period_amount41,
2143 pppd.period_amount41),
2144 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
2145 185, -pppd.period_amount42,
2146 120, -pppd.period_amount42,
2147 pppd.period_amount42),
2148 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
2149 185, -pppd.period_amount43,
2150 120, -pppd.period_amount43,
2151 pppd.period_amount43),
2152 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
2153 185, -pppd.period_amount44,
2154 120, -pppd.period_amount44,
2155 pppd.period_amount44),
2156 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
2157 185, -pppd.period_amount45,
2158 120, -pppd.period_amount45,
2159 pppd.period_amount45),
2160 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
2161 185, -pppd.period_amount46,
2162 120, -pppd.period_amount46,
2163 pppd.period_amount46),
2164 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
2165 185, -pppd.period_amount47,
2166 120, -pppd.period_amount47,
2167 pppd.period_amount47),
2168 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
2169 185, -pppd.period_amount48,
2170 120, -pppd.period_amount48,
2171 pppd.period_amount48),
2172 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
2173 185, -pppd.period_amount49,
2174 120, -pppd.period_amount49,
2175 pppd.period_amount49),
2176 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
2177 185, -pppd.period_amount50,
2178 120, -pppd.period_amount50,
2179 pppd.period_amount50),
2180 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
2181 185, -pppd.period_amount51,
2182 120, -pppd.period_amount51,
2183 pppd.period_amount51),
2184 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
2185 185, -pppd.period_amount52,
2186 120, -pppd.period_amount52,
2187 pppd.period_amount52),0))
2188 into l_return_value
2189 from pa_proj_period_profiles pppp,
2190 pa_proj_periods_denorm pppd
2191 where pppp.period_profile_id = p_period_profile_id and
2192 pppp.period_profile_id=pppd.period_profile_id and
2193 pppd.amount_type_id = p_amount_type_id and
2194 pppd.resource_assignment_id = p_resource_assignment_id and
2195 pppd.currency_type = p_project_currency_type
2196 group by pppd.amount_type_id;
2197
2198 elsif p_period_number = 3 then
2199 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date3(),
2200 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
2201 185, -pppd.period_amount1,
2202 120, -pppd.period_amount1,
2203 pppd.period_amount1),
2204 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
2205 185, -pppd.period_amount2,
2206 120, -pppd.period_amount2,
2207 pppd.period_amount2),
2208 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
2209 185, -pppd.period_amount3,
2210 120, -pppd.period_amount3,
2211 pppd.period_amount3),
2212 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
2213 185, -pppd.period_amount4,
2214 120, -pppd.period_amount4,
2215 pppd.period_amount4),
2216 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
2217 185, -pppd.period_amount5,
2218 120, -pppd.period_amount5,
2219 pppd.period_amount5),
2220 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
2221 185, -pppd.period_amount6,
2222 120, -pppd.period_amount6,
2223 pppd.period_amount6),
2224 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
2225 185, -pppd.period_amount7,
2226 120, -pppd.period_amount7,
2227 pppd.period_amount7),
2228 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
2229 185, -pppd.period_amount8,
2230 120, -pppd.period_amount8,
2231 pppd.period_amount8),
2232 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
2233 185, -pppd.period_amount9,
2234 120, -pppd.period_amount9,
2235 pppd.period_amount9),
2236 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
2237 185, -pppd.period_amount10,
2238 120, -pppd.period_amount10,
2239 pppd.period_amount10),
2240 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
2241 185, -pppd.period_amount11,
2242 120, -pppd.period_amount11,
2243 pppd.period_amount11),
2244 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
2245 185, -pppd.period_amount12,
2246 120, -pppd.period_amount12,
2247 pppd.period_amount12),
2248 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
2249 185, -pppd.period_amount13,
2250 120, -pppd.period_amount13,
2251 pppd.period_amount13),
2252 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
2253 185, -pppd.period_amount14,
2254 120, -pppd.period_amount14,
2255 pppd.period_amount14),
2256 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
2257 185, -pppd.period_amount15,
2258 120, -pppd.period_amount15,
2259 pppd.period_amount15),
2260 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
2261 185, -pppd.period_amount16,
2262 120, -pppd.period_amount16,
2263 pppd.period_amount16),
2264 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
2265 185, -pppd.period_amount17,
2266 120, -pppd.period_amount17,
2267 pppd.period_amount17),
2268 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
2269 185, -pppd.period_amount18,
2270 120, -pppd.period_amount18,
2271 pppd.period_amount18),
2272 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
2273 185, -pppd.period_amount19,
2274 120, -pppd.period_amount19,
2275 pppd.period_amount19),
2276 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
2277 185, -pppd.period_amount20,
2278 120, -pppd.period_amount20,
2279 pppd.period_amount20),
2280 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
2281 185, -pppd.period_amount21,
2282 120, -pppd.period_amount21,
2283 pppd.period_amount21),
2284 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
2285 185, -pppd.period_amount22,
2286 120, -pppd.period_amount22,
2287 pppd.period_amount22),
2288 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
2289 185, -pppd.period_amount23,
2290 120, -pppd.period_amount23,
2291 pppd.period_amount23),
2292 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
2293 185, -pppd.period_amount24,
2294 120, -pppd.period_amount24,
2295 pppd.period_amount24),
2296 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
2297 185, -pppd.period_amount25,
2298 120, -pppd.period_amount25,
2299 pppd.period_amount25),
2300 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
2301 185, -pppd.period_amount26,
2302 120, -pppd.period_amount26,
2303 pppd.period_amount26),
2304 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
2305 185, -pppd.period_amount27,
2306 120, -pppd.period_amount27,
2307 pppd.period_amount27),
2308 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
2309 185, -pppd.period_amount28,
2310 120, -pppd.period_amount28,
2311 pppd.period_amount28),
2312 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
2313 185, -pppd.period_amount29,
2314 120, -pppd.period_amount29,
2315 pppd.period_amount29),
2316 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
2317 185, -pppd.period_amount30,
2318 120, -pppd.period_amount30,
2319 pppd.period_amount30),
2320 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
2321 185, -pppd.period_amount31,
2322 120, -pppd.period_amount31,
2323 pppd.period_amount31),
2324 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
2325 185, -pppd.period_amount32,
2326 120, -pppd.period_amount32,
2327 pppd.period_amount32),
2328 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
2329 185, -pppd.period_amount33,
2330 120, -pppd.period_amount33,
2331 pppd.period_amount33),
2332 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
2333 185, -pppd.period_amount34,
2334 120, -pppd.period_amount34,
2335 pppd.period_amount34),
2336 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
2337 185, -pppd.period_amount35,
2338 120, -pppd.period_amount35,
2339 pppd.period_amount35),
2340 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
2341 185, -pppd.period_amount36,
2342 120, -pppd.period_amount36,
2343 pppd.period_amount36),
2344 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
2345 185, -pppd.period_amount37,
2346 120, -pppd.period_amount37,
2347 pppd.period_amount37),
2348 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
2349 185, -pppd.period_amount38,
2350 120, -pppd.period_amount38,
2351 pppd.period_amount38),
2352 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
2353 185, -pppd.period_amount39,
2354 120, -pppd.period_amount39,
2355 pppd.period_amount39),
2356 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
2357 185, -pppd.period_amount40,
2358 120, -pppd.period_amount40,
2359 pppd.period_amount40),
2360 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
2361 185, -pppd.period_amount41,
2362 120, -pppd.period_amount41,
2363 pppd.period_amount41),
2364 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
2365 185, -pppd.period_amount42,
2366 120, -pppd.period_amount42,
2367 pppd.period_amount42),
2368 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
2369 185, -pppd.period_amount43,
2370 120, -pppd.period_amount43,
2371 pppd.period_amount43),
2372 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
2373 185, -pppd.period_amount44,
2374 120, -pppd.period_amount44,
2375 pppd.period_amount44),
2376 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
2377 185, -pppd.period_amount45,
2378 120, -pppd.period_amount45,
2379 pppd.period_amount45),
2380 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
2381 185, -pppd.period_amount46,
2382 120, -pppd.period_amount46,
2383 pppd.period_amount46),
2384 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
2385 185, -pppd.period_amount47,
2386 120, -pppd.period_amount47,
2387 pppd.period_amount47),
2388 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
2389 185, -pppd.period_amount48,
2390 120, -pppd.period_amount48,
2391 pppd.period_amount48),
2392 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
2393 185, -pppd.period_amount49,
2394 120, -pppd.period_amount49,
2395 pppd.period_amount49),
2396 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
2397 185, -pppd.period_amount50,
2398 120, -pppd.period_amount50,
2399 pppd.period_amount50),
2400 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
2401 185, -pppd.period_amount51,
2402 120, -pppd.period_amount51,
2403 pppd.period_amount51),
2404 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
2405 185, -pppd.period_amount52,
2406 120, -pppd.period_amount52,
2407 pppd.period_amount52),0))
2408 into l_return_value
2409 from pa_proj_period_profiles pppp,
2410 pa_proj_periods_denorm pppd
2411 where pppp.period_profile_id = p_period_profile_id and
2412 pppp.period_profile_id=pppd.period_profile_id and
2413 pppd.amount_type_id = p_amount_type_id and
2414 pppd.resource_assignment_id = p_resource_assignment_id and
2415 pppd.currency_type = p_project_currency_type
2416 group by pppd.amount_type_id;
2417
2418 elsif p_period_number = 4 then
2419 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date4(),
2420 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
2421 185, -pppd.period_amount1,
2422 120, -pppd.period_amount1,
2423 pppd.period_amount1),
2424 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
2425 185, -pppd.period_amount2,
2426 120, -pppd.period_amount2,
2427 pppd.period_amount2),
2428 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
2429 185, -pppd.period_amount3,
2430 120, -pppd.period_amount3,
2431 pppd.period_amount3),
2432 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
2433 185, -pppd.period_amount4,
2434 120, -pppd.period_amount4,
2435 pppd.period_amount4),
2436 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
2437 185, -pppd.period_amount5,
2438 120, -pppd.period_amount5,
2439 pppd.period_amount5),
2440 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
2441 185, -pppd.period_amount6,
2442 120, -pppd.period_amount6,
2443 pppd.period_amount6),
2444 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
2445 185, -pppd.period_amount7,
2446 120, -pppd.period_amount7,
2447 pppd.period_amount7),
2448 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
2449 185, -pppd.period_amount8,
2450 120, -pppd.period_amount8,
2451 pppd.period_amount8),
2452 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
2453 185, -pppd.period_amount9,
2454 120, -pppd.period_amount9,
2455 pppd.period_amount9),
2456 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
2457 185, -pppd.period_amount10,
2458 120, -pppd.period_amount10,
2459 pppd.period_amount10),
2460 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
2461 185, -pppd.period_amount11,
2462 120, -pppd.period_amount11,
2463 pppd.period_amount11),
2464 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
2465 185, -pppd.period_amount12,
2466 120, -pppd.period_amount12,
2467 pppd.period_amount12),
2468 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
2469 185, -pppd.period_amount13,
2470 120, -pppd.period_amount13,
2471 pppd.period_amount13),
2472 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
2473 185, -pppd.period_amount14,
2474 120, -pppd.period_amount14,
2475 pppd.period_amount14),
2476 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
2477 185, -pppd.period_amount15,
2478 120, -pppd.period_amount15,
2479 pppd.period_amount15),
2480 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
2481 185, -pppd.period_amount16,
2482 120, -pppd.period_amount16,
2483 pppd.period_amount16),
2484 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
2485 185, -pppd.period_amount17,
2486 120, -pppd.period_amount17,
2487 pppd.period_amount17),
2488 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
2489 185, -pppd.period_amount18,
2490 120, -pppd.period_amount18,
2491 pppd.period_amount18),
2492 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
2493 185, -pppd.period_amount19,
2494 120, -pppd.period_amount19,
2495 pppd.period_amount19),
2496 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
2497 185, -pppd.period_amount20,
2498 120, -pppd.period_amount20,
2499 pppd.period_amount20),
2500 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
2501 185, -pppd.period_amount21,
2502 120, -pppd.period_amount21,
2503 pppd.period_amount21),
2504 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
2505 185, -pppd.period_amount22,
2506 120, -pppd.period_amount22,
2507 pppd.period_amount22),
2508 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
2509 185, -pppd.period_amount23,
2510 120, -pppd.period_amount23,
2511 pppd.period_amount23),
2512 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
2513 185, -pppd.period_amount24,
2514 120, -pppd.period_amount24,
2515 pppd.period_amount24),
2516 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
2517 185, -pppd.period_amount25,
2518 120, -pppd.period_amount25,
2519 pppd.period_amount25),
2520 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
2521 185, -pppd.period_amount26,
2522 120, -pppd.period_amount26,
2523 pppd.period_amount26),
2524 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
2525 185, -pppd.period_amount27,
2526 120, -pppd.period_amount27,
2527 pppd.period_amount27),
2528 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
2529 185, -pppd.period_amount28,
2530 120, -pppd.period_amount28,
2531 pppd.period_amount28),
2532 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
2533 185, -pppd.period_amount29,
2534 120, -pppd.period_amount29,
2535 pppd.period_amount29),
2536 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
2537 185, -pppd.period_amount30,
2538 120, -pppd.period_amount30,
2539 pppd.period_amount30),
2540 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
2541 185, -pppd.period_amount31,
2542 120, -pppd.period_amount31,
2543 pppd.period_amount31),
2544 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
2545 185, -pppd.period_amount32,
2546 120, -pppd.period_amount32,
2547 pppd.period_amount32),
2548 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
2549 185, -pppd.period_amount33,
2550 120, -pppd.period_amount33,
2551 pppd.period_amount33),
2552 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
2553 185, -pppd.period_amount34,
2554 120, -pppd.period_amount34,
2555 pppd.period_amount34),
2556 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
2557 185, -pppd.period_amount35,
2558 120, -pppd.period_amount35,
2559 pppd.period_amount35),
2560 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
2561 185, -pppd.period_amount36,
2562 120, -pppd.period_amount36,
2563 pppd.period_amount36),
2564 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
2565 185, -pppd.period_amount37,
2566 120, -pppd.period_amount37,
2567 pppd.period_amount37),
2568 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
2569 185, -pppd.period_amount38,
2570 120, -pppd.period_amount38,
2571 pppd.period_amount38),
2572 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
2573 185, -pppd.period_amount39,
2574 120, -pppd.period_amount39,
2575 pppd.period_amount39),
2576 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
2577 185, -pppd.period_amount40,
2578 120, -pppd.period_amount40,
2579 pppd.period_amount40),
2580 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
2581 185, -pppd.period_amount41,
2582 120, -pppd.period_amount41,
2583 pppd.period_amount41),
2584 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
2585 185, -pppd.period_amount42,
2586 120, -pppd.period_amount42,
2587 pppd.period_amount42),
2588 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
2589 185, -pppd.period_amount43,
2590 120, -pppd.period_amount43,
2591 pppd.period_amount43),
2592 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
2593 185, -pppd.period_amount44,
2594 120, -pppd.period_amount44,
2595 pppd.period_amount44),
2596 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
2597 185, -pppd.period_amount45,
2598 120, -pppd.period_amount45,
2599 pppd.period_amount45),
2600 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
2601 185, -pppd.period_amount46,
2602 120, -pppd.period_amount46,
2603 pppd.period_amount46),
2604 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
2605 185, -pppd.period_amount47,
2606 120, -pppd.period_amount47,
2607 pppd.period_amount47),
2608 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
2609 185, -pppd.period_amount48,
2610 120, -pppd.period_amount48,
2611 pppd.period_amount48),
2612 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
2613 185, -pppd.period_amount49,
2614 120, -pppd.period_amount49,
2615 pppd.period_amount49),
2616 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
2617 185, -pppd.period_amount50,
2618 120, -pppd.period_amount50,
2619 pppd.period_amount50),
2620 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
2621 185, -pppd.period_amount51,
2622 120, -pppd.period_amount51,
2623 pppd.period_amount51),
2624 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
2625 185, -pppd.period_amount52,
2626 120, -pppd.period_amount52,
2627 pppd.period_amount52),0))
2628 into l_return_value
2629 from pa_proj_period_profiles pppp,
2630 pa_proj_periods_denorm pppd
2631 where pppp.period_profile_id = p_period_profile_id and
2632 pppp.period_profile_id=pppd.period_profile_id and
2633 pppd.amount_type_id = p_amount_type_id and
2634 pppd.resource_assignment_id = p_resource_assignment_id and
2635 pppd.currency_type = p_project_currency_type
2636 group by pppd.amount_type_id;
2637
2638 elsif p_period_number = 5 then
2639 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date5(),
2640 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
2641 185, -pppd.period_amount1,
2642 120, -pppd.period_amount1,
2643 pppd.period_amount1),
2644 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
2645 185, -pppd.period_amount2,
2646 120, -pppd.period_amount2,
2647 pppd.period_amount2),
2648 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
2649 185, -pppd.period_amount3,
2650 120, -pppd.period_amount3,
2651 pppd.period_amount3),
2652 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
2653 185, -pppd.period_amount4,
2654 120, -pppd.period_amount4,
2655 pppd.period_amount4),
2656 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
2657 185, -pppd.period_amount5,
2658 120, -pppd.period_amount5,
2659 pppd.period_amount5),
2660 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
2661 185, -pppd.period_amount6,
2662 120, -pppd.period_amount6,
2663 pppd.period_amount6),
2664 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
2665 185, -pppd.period_amount7,
2666 120, -pppd.period_amount7,
2667 pppd.period_amount7),
2668 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
2669 185, -pppd.period_amount8,
2670 120, -pppd.period_amount8,
2671 pppd.period_amount8),
2672 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
2673 185, -pppd.period_amount9,
2674 120, -pppd.period_amount9,
2675 pppd.period_amount9),
2676 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
2677 185, -pppd.period_amount10,
2678 120, -pppd.period_amount10,
2679 pppd.period_amount10),
2680 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
2681 185, -pppd.period_amount11,
2682 120, -pppd.period_amount11,
2683 pppd.period_amount11),
2684 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
2685 185, -pppd.period_amount12,
2686 120, -pppd.period_amount12,
2687 pppd.period_amount12),
2688 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
2689 185, -pppd.period_amount13,
2690 120, -pppd.period_amount13,
2691 pppd.period_amount13),
2692 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
2693 185, -pppd.period_amount14,
2694 120, -pppd.period_amount14,
2695 pppd.period_amount14),
2696 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
2697 185, -pppd.period_amount15,
2698 120, -pppd.period_amount15,
2699 pppd.period_amount15),
2700 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
2701 185, -pppd.period_amount16,
2702 120, -pppd.period_amount16,
2703 pppd.period_amount16),
2704 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
2705 185, -pppd.period_amount17,
2706 120, -pppd.period_amount17,
2707 pppd.period_amount17),
2708 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
2709 185, -pppd.period_amount18,
2710 120, -pppd.period_amount18,
2711 pppd.period_amount18),
2712 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
2713 185, -pppd.period_amount19,
2714 120, -pppd.period_amount19,
2715 pppd.period_amount19),
2716 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
2717 185, -pppd.period_amount20,
2718 120, -pppd.period_amount20,
2719 pppd.period_amount20),
2720 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
2721 185, -pppd.period_amount21,
2722 120, -pppd.period_amount21,
2723 pppd.period_amount21),
2724 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
2725 185, -pppd.period_amount22,
2726 120, -pppd.period_amount22,
2727 pppd.period_amount22),
2728 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
2729 185, -pppd.period_amount23,
2730 120, -pppd.period_amount23,
2731 pppd.period_amount23),
2732 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
2733 185, -pppd.period_amount24,
2734 120, -pppd.period_amount24,
2735 pppd.period_amount24),
2736 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
2737 185, -pppd.period_amount25,
2738 120, -pppd.period_amount25,
2739 pppd.period_amount25),
2740 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
2741 185, -pppd.period_amount26,
2742 120, -pppd.period_amount26,
2743 pppd.period_amount26),
2744 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
2745 185, -pppd.period_amount27,
2746 120, -pppd.period_amount27,
2747 pppd.period_amount27),
2748 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
2749 185, -pppd.period_amount28,
2750 120, -pppd.period_amount28,
2751 pppd.period_amount28),
2752 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
2753 185, -pppd.period_amount29,
2754 120, -pppd.period_amount29,
2755 pppd.period_amount29),
2756 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
2757 185, -pppd.period_amount30,
2758 120, -pppd.period_amount30,
2759 pppd.period_amount30),
2760 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
2761 185, -pppd.period_amount31,
2762 120, -pppd.period_amount31,
2763 pppd.period_amount31),
2764 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
2765 185, -pppd.period_amount32,
2766 120, -pppd.period_amount32,
2767 pppd.period_amount32),
2768 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
2769 185, -pppd.period_amount33,
2770 120, -pppd.period_amount33,
2771 pppd.period_amount33),
2772 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
2773 185, -pppd.period_amount34,
2774 120, -pppd.period_amount34,
2775 pppd.period_amount34),
2776 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
2777 185, -pppd.period_amount35,
2778 120, -pppd.period_amount35,
2779 pppd.period_amount35),
2780 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
2781 185, -pppd.period_amount36,
2782 120, -pppd.period_amount36,
2783 pppd.period_amount36),
2784 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
2785 185, -pppd.period_amount37,
2786 120, -pppd.period_amount37,
2787 pppd.period_amount37),
2788 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
2789 185, -pppd.period_amount38,
2790 120, -pppd.period_amount38,
2791 pppd.period_amount38),
2792 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
2793 185, -pppd.period_amount39,
2794 120, -pppd.period_amount39,
2795 pppd.period_amount39),
2796 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
2797 185, -pppd.period_amount40,
2798 120, -pppd.period_amount40,
2799 pppd.period_amount40),
2800 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
2801 185, -pppd.period_amount41,
2802 120, -pppd.period_amount41,
2803 pppd.period_amount41),
2804 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
2805 185, -pppd.period_amount42,
2806 120, -pppd.period_amount42,
2807 pppd.period_amount42),
2808 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
2809 185, -pppd.period_amount43,
2810 120, -pppd.period_amount43,
2811 pppd.period_amount43),
2812 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
2813 185, -pppd.period_amount44,
2814 120, -pppd.period_amount44,
2815 pppd.period_amount44),
2816 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
2817 185, -pppd.period_amount45,
2818 120, -pppd.period_amount45,
2819 pppd.period_amount45),
2820 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
2821 185, -pppd.period_amount46,
2822 120, -pppd.period_amount46,
2823 pppd.period_amount46),
2824 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
2825 185, -pppd.period_amount47,
2826 120, -pppd.period_amount47,
2827 pppd.period_amount47),
2828 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
2829 185, -pppd.period_amount48,
2830 120, -pppd.period_amount48,
2831 pppd.period_amount48),
2832 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
2833 185, -pppd.period_amount49,
2834 120, -pppd.period_amount49,
2835 pppd.period_amount49),
2836 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
2837 185, -pppd.period_amount50,
2838 120, -pppd.period_amount50,
2839 pppd.period_amount50),
2840 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
2841 185, -pppd.period_amount51,
2842 120, -pppd.period_amount51,
2843 pppd.period_amount51),
2844 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
2845 185, -pppd.period_amount52,
2846 120, -pppd.period_amount52,
2847 pppd.period_amount52),0))
2848 into l_return_value
2849 from pa_proj_period_profiles pppp,
2850 pa_proj_periods_denorm pppd
2851 where pppp.period_profile_id = p_period_profile_id and
2852 pppp.period_profile_id=pppd.period_profile_id and
2853 pppd.amount_type_id = p_amount_type_id and
2854 pppd.resource_assignment_id = p_resource_assignment_id and
2855 pppd.currency_type = p_project_currency_type
2856 group by pppd.amount_type_id;
2857
2858 elsif p_period_number = 6 then
2859 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date6(),
2860 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
2861 185, -pppd.period_amount1,
2862 120, -pppd.period_amount1,
2863 pppd.period_amount1),
2864 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
2865 185, -pppd.period_amount2,
2866 120, -pppd.period_amount2,
2867 pppd.period_amount2),
2868 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
2869 185, -pppd.period_amount3,
2870 120, -pppd.period_amount3,
2871 pppd.period_amount3),
2872 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
2873 185, -pppd.period_amount4,
2874 120, -pppd.period_amount4,
2875 pppd.period_amount4),
2876 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
2877 185, -pppd.period_amount5,
2878 120, -pppd.period_amount5,
2879 pppd.period_amount5),
2880 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
2881 185, -pppd.period_amount6,
2882 120, -pppd.period_amount6,
2883 pppd.period_amount6),
2884 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
2885 185, -pppd.period_amount7,
2886 120, -pppd.period_amount7,
2887 pppd.period_amount7),
2888 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
2889 185, -pppd.period_amount8,
2890 120, -pppd.period_amount8,
2891 pppd.period_amount8),
2892 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
2893 185, -pppd.period_amount9,
2894 120, -pppd.period_amount9,
2895 pppd.period_amount9),
2896 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
2897 185, -pppd.period_amount10,
2898 120, -pppd.period_amount10,
2899 pppd.period_amount10),
2900 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
2901 185, -pppd.period_amount11,
2902 120, -pppd.period_amount11,
2903 pppd.period_amount11),
2904 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
2905 185, -pppd.period_amount12,
2906 120, -pppd.period_amount12,
2907 pppd.period_amount12),
2908 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
2909 185, -pppd.period_amount13,
2910 120, -pppd.period_amount13,
2911 pppd.period_amount13),
2912 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
2913 185, -pppd.period_amount14,
2914 120, -pppd.period_amount14,
2915 pppd.period_amount14),
2916 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
2917 185, -pppd.period_amount15,
2918 120, -pppd.period_amount15,
2919 pppd.period_amount15),
2920 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
2921 185, -pppd.period_amount16,
2922 120, -pppd.period_amount16,
2923 pppd.period_amount16),
2924 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
2925 185, -pppd.period_amount17,
2926 120, -pppd.period_amount17,
2927 pppd.period_amount17),
2928 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
2929 185, -pppd.period_amount18,
2930 120, -pppd.period_amount18,
2931 pppd.period_amount18),
2932 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
2933 185, -pppd.period_amount19,
2934 120, -pppd.period_amount19,
2935 pppd.period_amount19),
2936 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
2937 185, -pppd.period_amount20,
2938 120, -pppd.period_amount20,
2939 pppd.period_amount20),
2940 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
2941 185, -pppd.period_amount21,
2942 120, -pppd.period_amount21,
2943 pppd.period_amount21),
2944 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
2945 185, -pppd.period_amount22,
2946 120, -pppd.period_amount22,
2947 pppd.period_amount22),
2948 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
2949 185, -pppd.period_amount23,
2950 120, -pppd.period_amount23,
2951 pppd.period_amount23),
2952 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
2953 185, -pppd.period_amount24,
2954 120, -pppd.period_amount24,
2955 pppd.period_amount24),
2956 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
2957 185, -pppd.period_amount25,
2958 120, -pppd.period_amount25,
2959 pppd.period_amount25),
2960 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
2961 185, -pppd.period_amount26,
2962 120, -pppd.period_amount26,
2963 pppd.period_amount26),
2964 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
2965 185, -pppd.period_amount27,
2966 120, -pppd.period_amount27,
2967 pppd.period_amount27),
2968 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
2969 185, -pppd.period_amount28,
2970 120, -pppd.period_amount28,
2971 pppd.period_amount28),
2972 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
2973 185, -pppd.period_amount29,
2974 120, -pppd.period_amount29,
2975 pppd.period_amount29),
2976 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
2977 185, -pppd.period_amount30,
2978 120, -pppd.period_amount30,
2979 pppd.period_amount30),
2980 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
2981 185, -pppd.period_amount31,
2982 120, -pppd.period_amount31,
2983 pppd.period_amount31),
2984 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
2985 185, -pppd.period_amount32,
2986 120, -pppd.period_amount32,
2987 pppd.period_amount32),
2988 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
2989 185, -pppd.period_amount33,
2990 120, -pppd.period_amount33,
2991 pppd.period_amount33),
2992 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
2993 185, -pppd.period_amount34,
2994 120, -pppd.period_amount34,
2995 pppd.period_amount34),
2996 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
2997 185, -pppd.period_amount35,
2998 120, -pppd.period_amount35,
2999 pppd.period_amount35),
3000 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
3001 185, -pppd.period_amount36,
3002 120, -pppd.period_amount36,
3003 pppd.period_amount36),
3004 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
3005 185, -pppd.period_amount37,
3006 120, -pppd.period_amount37,
3007 pppd.period_amount37),
3008 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
3009 185, -pppd.period_amount38,
3010 120, -pppd.period_amount38,
3011 pppd.period_amount38),
3012 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
3013 185, -pppd.period_amount39,
3014 120, -pppd.period_amount39,
3015 pppd.period_amount39),
3016 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
3017 185, -pppd.period_amount40,
3018 120, -pppd.period_amount40,
3019 pppd.period_amount40),
3020 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
3021 185, -pppd.period_amount41,
3022 120, -pppd.period_amount41,
3023 pppd.period_amount41),
3024 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
3025 185, -pppd.period_amount42,
3026 120, -pppd.period_amount42,
3027 pppd.period_amount42),
3028 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
3029 185, -pppd.period_amount43,
3030 120, -pppd.period_amount43,
3031 pppd.period_amount43),
3032 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
3033 185, -pppd.period_amount44,
3034 120, -pppd.period_amount44,
3035 pppd.period_amount44),
3036 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
3037 185, -pppd.period_amount45,
3038 120, -pppd.period_amount45,
3039 pppd.period_amount45),
3040 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
3041 185, -pppd.period_amount46,
3042 120, -pppd.period_amount46,
3043 pppd.period_amount46),
3044 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
3045 185, -pppd.period_amount47,
3046 120, -pppd.period_amount47,
3047 pppd.period_amount47),
3048 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
3049 185, -pppd.period_amount48,
3050 120, -pppd.period_amount48,
3051 pppd.period_amount48),
3052 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
3053 185, -pppd.period_amount49,
3054 120, -pppd.period_amount49,
3055 pppd.period_amount49),
3056 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
3057 185, -pppd.period_amount50,
3058 120, -pppd.period_amount50,
3059 pppd.period_amount50),
3060 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
3061 185, -pppd.period_amount51,
3062 120, -pppd.period_amount51,
3063 pppd.period_amount51),
3064 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
3065 185, -pppd.period_amount52,
3066 120, -pppd.period_amount52,
3067 pppd.period_amount52),0))
3068 into l_return_value
3069 from pa_proj_period_profiles pppp,
3070 pa_proj_periods_denorm pppd
3071 where pppp.period_profile_id = p_period_profile_id and
3072 pppp.period_profile_id=pppd.period_profile_id and
3073 pppd.amount_type_id = p_amount_type_id and
3074 pppd.resource_assignment_id = p_resource_assignment_id and
3075 pppd.currency_type = p_project_currency_type
3076 group by pppd.amount_type_id;
3077
3078 elsif p_period_number = 7 then
3079 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date7(),
3080 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
3081 185, -pppd.period_amount1,
3082 120, -pppd.period_amount1,
3083 pppd.period_amount1),
3084 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
3085 185, -pppd.period_amount2,
3086 120, -pppd.period_amount2,
3087 pppd.period_amount2),
3088 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
3089 185, -pppd.period_amount3,
3090 120, -pppd.period_amount3,
3091 pppd.period_amount3),
3092 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
3093 185, -pppd.period_amount4,
3094 120, -pppd.period_amount4,
3095 pppd.period_amount4),
3096 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
3097 185, -pppd.period_amount5,
3098 120, -pppd.period_amount5,
3099 pppd.period_amount5),
3100 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
3101 185, -pppd.period_amount6,
3102 120, -pppd.period_amount6,
3103 pppd.period_amount6),
3104 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
3105 185, -pppd.period_amount7,
3106 120, -pppd.period_amount7,
3107 pppd.period_amount7),
3108 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
3109 185, -pppd.period_amount8,
3110 120, -pppd.period_amount8,
3111 pppd.period_amount8),
3112 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
3113 185, -pppd.period_amount9,
3114 120, -pppd.period_amount9,
3115 pppd.period_amount9),
3116 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
3117 185, -pppd.period_amount10,
3118 120, -pppd.period_amount10,
3119 pppd.period_amount10),
3120 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
3121 185, -pppd.period_amount11,
3122 120, -pppd.period_amount11,
3123 pppd.period_amount11),
3124 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
3125 185, -pppd.period_amount12,
3126 120, -pppd.period_amount12,
3127 pppd.period_amount12),
3128 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
3129 185, -pppd.period_amount13,
3130 120, -pppd.period_amount13,
3131 pppd.period_amount13),
3132 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
3133 185, -pppd.period_amount14,
3134 120, -pppd.period_amount14,
3135 pppd.period_amount14),
3136 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
3137 185, -pppd.period_amount15,
3138 120, -pppd.period_amount15,
3139 pppd.period_amount15),
3140 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
3141 185, -pppd.period_amount16,
3142 120, -pppd.period_amount16,
3143 pppd.period_amount16),
3144 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
3145 185, -pppd.period_amount17,
3146 120, -pppd.period_amount17,
3147 pppd.period_amount17),
3148 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
3149 185, -pppd.period_amount18,
3150 120, -pppd.period_amount18,
3151 pppd.period_amount18),
3152 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
3153 185, -pppd.period_amount19,
3154 120, -pppd.period_amount19,
3155 pppd.period_amount19),
3156 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
3157 185, -pppd.period_amount20,
3158 120, -pppd.period_amount20,
3159 pppd.period_amount20),
3160 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
3161 185, -pppd.period_amount21,
3162 120, -pppd.period_amount21,
3163 pppd.period_amount21),
3164 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
3165 185, -pppd.period_amount22,
3166 120, -pppd.period_amount22,
3167 pppd.period_amount22),
3168 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
3169 185, -pppd.period_amount23,
3170 120, -pppd.period_amount23,
3171 pppd.period_amount23),
3172 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
3173 185, -pppd.period_amount24,
3174 120, -pppd.period_amount24,
3175 pppd.period_amount24),
3176 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
3177 185, -pppd.period_amount25,
3178 120, -pppd.period_amount25,
3179 pppd.period_amount25),
3180 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
3181 185, -pppd.period_amount26,
3182 120, -pppd.period_amount26,
3183 pppd.period_amount26),
3184 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
3185 185, -pppd.period_amount27,
3186 120, -pppd.period_amount27,
3187 pppd.period_amount27),
3188 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
3189 185, -pppd.period_amount28,
3190 120, -pppd.period_amount28,
3191 pppd.period_amount28),
3192 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
3193 185, -pppd.period_amount29,
3194 120, -pppd.period_amount29,
3195 pppd.period_amount29),
3196 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
3197 185, -pppd.period_amount30,
3198 120, -pppd.period_amount30,
3199 pppd.period_amount30),
3200 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
3201 185, -pppd.period_amount31,
3202 120, -pppd.period_amount31,
3203 pppd.period_amount31),
3204 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
3205 185, -pppd.period_amount32,
3206 120, -pppd.period_amount32,
3207 pppd.period_amount32),
3208 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
3209 185, -pppd.period_amount33,
3210 120, -pppd.period_amount33,
3211 pppd.period_amount33),
3212 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
3213 185, -pppd.period_amount34,
3214 120, -pppd.period_amount34,
3215 pppd.period_amount34),
3216 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
3217 185, -pppd.period_amount35,
3218 120, -pppd.period_amount35,
3219 pppd.period_amount35),
3220 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
3221 185, -pppd.period_amount36,
3222 120, -pppd.period_amount36,
3223 pppd.period_amount36),
3224 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
3225 185, -pppd.period_amount37,
3226 120, -pppd.period_amount37,
3227 pppd.period_amount37),
3228 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
3229 185, -pppd.period_amount38,
3230 120, -pppd.period_amount38,
3231 pppd.period_amount38),
3232 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
3233 185, -pppd.period_amount39,
3234 120, -pppd.period_amount39,
3235 pppd.period_amount39),
3236 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
3237 185, -pppd.period_amount40,
3238 120, -pppd.period_amount40,
3239 pppd.period_amount40),
3240 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
3241 185, -pppd.period_amount41,
3242 120, -pppd.period_amount41,
3243 pppd.period_amount41),
3244 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
3245 185, -pppd.period_amount42,
3246 120, -pppd.period_amount42,
3247 pppd.period_amount42),
3248 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
3249 185, -pppd.period_amount43,
3250 120, -pppd.period_amount43,
3251 pppd.period_amount43),
3252 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
3253 185, -pppd.period_amount44,
3254 120, -pppd.period_amount44,
3255 pppd.period_amount44),
3256 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
3257 185, -pppd.period_amount45,
3258 120, -pppd.period_amount45,
3259 pppd.period_amount45),
3260 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
3261 185, -pppd.period_amount46,
3262 120, -pppd.period_amount46,
3263 pppd.period_amount46),
3264 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
3265 185, -pppd.period_amount47,
3266 120, -pppd.period_amount47,
3267 pppd.period_amount47),
3268 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
3269 185, -pppd.period_amount48,
3270 120, -pppd.period_amount48,
3271 pppd.period_amount48),
3272 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
3273 185, -pppd.period_amount49,
3274 120, -pppd.period_amount49,
3275 pppd.period_amount49),
3276 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
3277 185, -pppd.period_amount50,
3278 120, -pppd.period_amount50,
3279 pppd.period_amount50),
3280 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
3281 185, -pppd.period_amount51,
3282 120, -pppd.period_amount51,
3283 pppd.period_amount51),
3284 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
3285 185, -pppd.period_amount52,
3286 120, -pppd.period_amount52,
3287 pppd.period_amount52),0))
3288 into l_return_value
3289 from pa_proj_period_profiles pppp,
3290 pa_proj_periods_denorm pppd
3291 where pppp.period_profile_id = p_period_profile_id and
3292 pppp.period_profile_id=pppd.period_profile_id and
3293 pppd.amount_type_id = p_amount_type_id and
3294 pppd.resource_assignment_id = p_resource_assignment_id and
3295 pppd.currency_type = p_project_currency_type
3296 group by pppd.amount_type_id;
3297
3298 elsif p_period_number = 8 then
3299 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date8(),
3300 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
3301 185, -pppd.period_amount1,
3302 120, -pppd.period_amount1,
3303 pppd.period_amount1),
3304 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
3305 185, -pppd.period_amount2,
3306 120, -pppd.period_amount2,
3307 pppd.period_amount2),
3308 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
3309 185, -pppd.period_amount3,
3310 120, -pppd.period_amount3,
3311 pppd.period_amount3),
3312 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
3313 185, -pppd.period_amount4,
3314 120, -pppd.period_amount4,
3315 pppd.period_amount4),
3316 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
3317 185, -pppd.period_amount5,
3318 120, -pppd.period_amount5,
3319 pppd.period_amount5),
3320 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
3321 185, -pppd.period_amount6,
3322 120, -pppd.period_amount6,
3323 pppd.period_amount6),
3324 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
3325 185, -pppd.period_amount7,
3326 120, -pppd.period_amount7,
3327 pppd.period_amount7),
3328 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
3329 185, -pppd.period_amount8,
3330 120, -pppd.period_amount8,
3331 pppd.period_amount8),
3332 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
3333 185, -pppd.period_amount9,
3334 120, -pppd.period_amount9,
3335 pppd.period_amount9),
3336 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
3337 185, -pppd.period_amount10,
3338 120, -pppd.period_amount10,
3339 pppd.period_amount10),
3340 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
3341 185, -pppd.period_amount11,
3342 120, -pppd.period_amount11,
3343 pppd.period_amount11),
3344 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
3345 185, -pppd.period_amount12,
3346 120, -pppd.period_amount12,
3347 pppd.period_amount12),
3348 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
3349 185, -pppd.period_amount13,
3350 120, -pppd.period_amount13,
3351 pppd.period_amount13),
3352 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
3353 185, -pppd.period_amount14,
3354 120, -pppd.period_amount14,
3355 pppd.period_amount14),
3356 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
3357 185, -pppd.period_amount15,
3358 120, -pppd.period_amount15,
3359 pppd.period_amount15),
3360 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
3361 185, -pppd.period_amount16,
3362 120, -pppd.period_amount16,
3363 pppd.period_amount16),
3364 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
3365 185, -pppd.period_amount17,
3366 120, -pppd.period_amount17,
3367 pppd.period_amount17),
3368 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
3369 185, -pppd.period_amount18,
3370 120, -pppd.period_amount18,
3371 pppd.period_amount18),
3372 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
3373 185, -pppd.period_amount19,
3374 120, -pppd.period_amount19,
3375 pppd.period_amount19),
3376 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
3377 185, -pppd.period_amount20,
3378 120, -pppd.period_amount20,
3379 pppd.period_amount20),
3380 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
3381 185, -pppd.period_amount21,
3382 120, -pppd.period_amount21,
3383 pppd.period_amount21),
3384 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
3385 185, -pppd.period_amount22,
3386 120, -pppd.period_amount22,
3387 pppd.period_amount22),
3388 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
3389 185, -pppd.period_amount23,
3390 120, -pppd.period_amount23,
3391 pppd.period_amount23),
3392 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
3393 185, -pppd.period_amount24,
3394 120, -pppd.period_amount24,
3395 pppd.period_amount24),
3396 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
3397 185, -pppd.period_amount25,
3398 120, -pppd.period_amount25,
3399 pppd.period_amount25),
3400 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
3401 185, -pppd.period_amount26,
3402 120, -pppd.period_amount26,
3403 pppd.period_amount26),
3404 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
3405 185, -pppd.period_amount27,
3406 120, -pppd.period_amount27,
3407 pppd.period_amount27),
3408 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
3409 185, -pppd.period_amount28,
3410 120, -pppd.period_amount28,
3411 pppd.period_amount28),
3412 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
3413 185, -pppd.period_amount29,
3414 120, -pppd.period_amount29,
3415 pppd.period_amount29),
3416 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
3417 185, -pppd.period_amount30,
3418 120, -pppd.period_amount30,
3419 pppd.period_amount30),
3420 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
3421 185, -pppd.period_amount31,
3422 120, -pppd.period_amount31,
3423 pppd.period_amount31),
3424 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
3425 185, -pppd.period_amount32,
3426 120, -pppd.period_amount32,
3427 pppd.period_amount32),
3428 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
3429 185, -pppd.period_amount33,
3430 120, -pppd.period_amount33,
3431 pppd.period_amount33),
3432 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
3433 185, -pppd.period_amount34,
3434 120, -pppd.period_amount34,
3435 pppd.period_amount34),
3436 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
3437 185, -pppd.period_amount35,
3438 120, -pppd.period_amount35,
3439 pppd.period_amount35),
3440 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
3441 185, -pppd.period_amount36,
3442 120, -pppd.period_amount36,
3443 pppd.period_amount36),
3444 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
3445 185, -pppd.period_amount37,
3446 120, -pppd.period_amount37,
3447 pppd.period_amount37),
3448 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
3449 185, -pppd.period_amount38,
3450 120, -pppd.period_amount38,
3451 pppd.period_amount38),
3452 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
3453 185, -pppd.period_amount39,
3454 120, -pppd.period_amount39,
3455 pppd.period_amount39),
3456 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
3457 185, -pppd.period_amount40,
3458 120, -pppd.period_amount40,
3459 pppd.period_amount40),
3460 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
3461 185, -pppd.period_amount41,
3462 120, -pppd.period_amount41,
3463 pppd.period_amount41),
3464 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
3465 185, -pppd.period_amount42,
3466 120, -pppd.period_amount42,
3467 pppd.period_amount42),
3468 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
3469 185, -pppd.period_amount43,
3470 120, -pppd.period_amount43,
3471 pppd.period_amount43),
3472 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
3473 185, -pppd.period_amount44,
3474 120, -pppd.period_amount44,
3475 pppd.period_amount44),
3476 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
3477 185, -pppd.period_amount45,
3478 120, -pppd.period_amount45,
3479 pppd.period_amount45),
3480 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
3481 185, -pppd.period_amount46,
3482 120, -pppd.period_amount46,
3483 pppd.period_amount46),
3484 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
3485 185, -pppd.period_amount47,
3486 120, -pppd.period_amount47,
3487 pppd.period_amount47),
3488 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
3489 185, -pppd.period_amount48,
3490 120, -pppd.period_amount48,
3491 pppd.period_amount48),
3492 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
3493 185, -pppd.period_amount49,
3494 120, -pppd.period_amount49,
3495 pppd.period_amount49),
3496 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
3497 185, -pppd.period_amount50,
3498 120, -pppd.period_amount50,
3499 pppd.period_amount50),
3500 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
3501 185, -pppd.period_amount51,
3502 120, -pppd.period_amount51,
3503 pppd.period_amount51),
3504 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
3505 185, -pppd.period_amount52,
3506 120, -pppd.period_amount52,
3507 pppd.period_amount52),0))
3508 into l_return_value
3509 from pa_proj_period_profiles pppp,
3510 pa_proj_periods_denorm pppd
3511 where pppp.period_profile_id = p_period_profile_id and
3512 pppp.period_profile_id=pppd.period_profile_id and
3513 pppd.amount_type_id = p_amount_type_id and
3514 pppd.resource_assignment_id = p_resource_assignment_id and
3515 pppd.currency_type = p_project_currency_type
3516 group by pppd.amount_type_id;
3517
3518 elsif p_period_number = 9 then
3519 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date9(),
3520 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
3521 185, -pppd.period_amount1,
3522 120, -pppd.period_amount1,
3523 pppd.period_amount1),
3524 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
3525 185, -pppd.period_amount2,
3526 120, -pppd.period_amount2,
3527 pppd.period_amount2),
3528 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
3529 185, -pppd.period_amount3,
3530 120, -pppd.period_amount3,
3531 pppd.period_amount3),
3532 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
3533 185, -pppd.period_amount4,
3534 120, -pppd.period_amount4,
3535 pppd.period_amount4),
3536 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
3537 185, -pppd.period_amount5,
3538 120, -pppd.period_amount5,
3539 pppd.period_amount5),
3540 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
3541 185, -pppd.period_amount6,
3542 120, -pppd.period_amount6,
3543 pppd.period_amount6),
3544 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
3545 185, -pppd.period_amount7,
3546 120, -pppd.period_amount7,
3547 pppd.period_amount7),
3548 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
3549 185, -pppd.period_amount8,
3550 120, -pppd.period_amount8,
3551 pppd.period_amount8),
3552 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
3553 185, -pppd.period_amount9,
3554 120, -pppd.period_amount9,
3555 pppd.period_amount9),
3556 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
3557 185, -pppd.period_amount10,
3558 120, -pppd.period_amount10,
3559 pppd.period_amount10),
3560 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
3561 185, -pppd.period_amount11,
3562 120, -pppd.period_amount11,
3563 pppd.period_amount11),
3564 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
3565 185, -pppd.period_amount12,
3566 120, -pppd.period_amount12,
3567 pppd.period_amount12),
3568 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
3569 185, -pppd.period_amount13,
3570 120, -pppd.period_amount13,
3571 pppd.period_amount13),
3572 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
3573 185, -pppd.period_amount14,
3574 120, -pppd.period_amount14,
3575 pppd.period_amount14),
3576 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
3577 185, -pppd.period_amount15,
3578 120, -pppd.period_amount15,
3579 pppd.period_amount15),
3580 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
3581 185, -pppd.period_amount16,
3582 120, -pppd.period_amount16,
3583 pppd.period_amount16),
3584 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
3585 185, -pppd.period_amount17,
3586 120, -pppd.period_amount17,
3587 pppd.period_amount17),
3588 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
3589 185, -pppd.period_amount18,
3590 120, -pppd.period_amount18,
3591 pppd.period_amount18),
3592 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
3593 185, -pppd.period_amount19,
3594 120, -pppd.period_amount19,
3595 pppd.period_amount19),
3596 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
3597 185, -pppd.period_amount20,
3598 120, -pppd.period_amount20,
3599 pppd.period_amount20),
3600 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
3601 185, -pppd.period_amount21,
3602 120, -pppd.period_amount21,
3603 pppd.period_amount21),
3604 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
3605 185, -pppd.period_amount22,
3606 120, -pppd.period_amount22,
3607 pppd.period_amount22),
3608 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
3609 185, -pppd.period_amount23,
3610 120, -pppd.period_amount23,
3611 pppd.period_amount23),
3612 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
3613 185, -pppd.period_amount24,
3614 120, -pppd.period_amount24,
3615 pppd.period_amount24),
3616 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
3617 185, -pppd.period_amount25,
3618 120, -pppd.period_amount25,
3619 pppd.period_amount25),
3620 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
3621 185, -pppd.period_amount26,
3622 120, -pppd.period_amount26,
3623 pppd.period_amount26),
3624 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
3625 185, -pppd.period_amount27,
3626 120, -pppd.period_amount27,
3627 pppd.period_amount27),
3628 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
3629 185, -pppd.period_amount28,
3630 120, -pppd.period_amount28,
3631 pppd.period_amount28),
3632 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
3633 185, -pppd.period_amount29,
3634 120, -pppd.period_amount29,
3635 pppd.period_amount29),
3636 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
3637 185, -pppd.period_amount30,
3638 120, -pppd.period_amount30,
3639 pppd.period_amount30),
3640 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
3641 185, -pppd.period_amount31,
3642 120, -pppd.period_amount31,
3643 pppd.period_amount31),
3644 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
3645 185, -pppd.period_amount32,
3646 120, -pppd.period_amount32,
3647 pppd.period_amount32),
3648 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
3649 185, -pppd.period_amount33,
3650 120, -pppd.period_amount33,
3651 pppd.period_amount33),
3652 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
3653 185, -pppd.period_amount34,
3654 120, -pppd.period_amount34,
3655 pppd.period_amount34),
3656 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
3657 185, -pppd.period_amount35,
3658 120, -pppd.period_amount35,
3659 pppd.period_amount35),
3660 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
3661 185, -pppd.period_amount36,
3662 120, -pppd.period_amount36,
3663 pppd.period_amount36),
3664 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
3665 185, -pppd.period_amount37,
3666 120, -pppd.period_amount37,
3667 pppd.period_amount37),
3668 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
3669 185, -pppd.period_amount38,
3670 120, -pppd.period_amount38,
3671 pppd.period_amount38),
3672 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
3673 185, -pppd.period_amount39,
3674 120, -pppd.period_amount39,
3675 pppd.period_amount39),
3676 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
3677 185, -pppd.period_amount40,
3678 120, -pppd.period_amount40,
3679 pppd.period_amount40),
3680 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
3681 185, -pppd.period_amount41,
3682 120, -pppd.period_amount41,
3683 pppd.period_amount41),
3684 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
3685 185, -pppd.period_amount42,
3686 120, -pppd.period_amount42,
3687 pppd.period_amount42),
3688 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
3689 185, -pppd.period_amount43,
3690 120, -pppd.period_amount43,
3691 pppd.period_amount43),
3692 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
3693 185, -pppd.period_amount44,
3694 120, -pppd.period_amount44,
3695 pppd.period_amount44),
3696 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
3697 185, -pppd.period_amount45,
3698 120, -pppd.period_amount45,
3699 pppd.period_amount45),
3700 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
3701 185, -pppd.period_amount46,
3702 120, -pppd.period_amount46,
3703 pppd.period_amount46),
3704 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
3705 185, -pppd.period_amount47,
3706 120, -pppd.period_amount47,
3707 pppd.period_amount47),
3708 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
3709 185, -pppd.period_amount48,
3710 120, -pppd.period_amount48,
3711 pppd.period_amount48),
3712 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
3713 185, -pppd.period_amount49,
3714 120, -pppd.period_amount49,
3715 pppd.period_amount49),
3716 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
3717 185, -pppd.period_amount50,
3718 120, -pppd.period_amount50,
3719 pppd.period_amount50),
3720 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
3721 185, -pppd.period_amount51,
3722 120, -pppd.period_amount51,
3723 pppd.period_amount51),
3724 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
3725 185, -pppd.period_amount52,
3726 120, -pppd.period_amount52,
3727 pppd.period_amount52),0))
3728 into l_return_value
3729 from pa_proj_period_profiles pppp,
3730 pa_proj_periods_denorm pppd
3731 where pppp.period_profile_id = p_period_profile_id and
3732 pppp.period_profile_id=pppd.period_profile_id and
3733 pppd.amount_type_id = p_amount_type_id and
3734 pppd.resource_assignment_id = p_resource_assignment_id and
3735 pppd.currency_type = p_project_currency_type
3736 group by pppd.amount_type_id;
3737
3738 elsif p_period_number = 10 then
3739 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date10(),
3740 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
3741 185, -pppd.period_amount1,
3742 120, -pppd.period_amount1,
3743 pppd.period_amount1),
3744 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
3745 185, -pppd.period_amount2,
3746 120, -pppd.period_amount2,
3747 pppd.period_amount2),
3748 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
3749 185, -pppd.period_amount3,
3750 120, -pppd.period_amount3,
3751 pppd.period_amount3),
3752 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
3753 185, -pppd.period_amount4,
3754 120, -pppd.period_amount4,
3755 pppd.period_amount4),
3756 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
3757 185, -pppd.period_amount5,
3758 120, -pppd.period_amount5,
3759 pppd.period_amount5),
3760 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
3761 185, -pppd.period_amount6,
3762 120, -pppd.period_amount6,
3763 pppd.period_amount6),
3764 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
3765 185, -pppd.period_amount7,
3766 120, -pppd.period_amount7,
3767 pppd.period_amount7),
3768 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
3769 185, -pppd.period_amount8,
3770 120, -pppd.period_amount8,
3771 pppd.period_amount8),
3772 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
3773 185, -pppd.period_amount9,
3774 120, -pppd.period_amount9,
3775 pppd.period_amount9),
3776 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
3777 185, -pppd.period_amount10,
3778 120, -pppd.period_amount10,
3779 pppd.period_amount10),
3780 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
3781 185, -pppd.period_amount11,
3782 120, -pppd.period_amount11,
3783 pppd.period_amount11),
3784 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
3785 185, -pppd.period_amount12,
3786 120, -pppd.period_amount12,
3787 pppd.period_amount12),
3788 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
3789 185, -pppd.period_amount13,
3790 120, -pppd.period_amount13,
3791 pppd.period_amount13),
3792 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
3793 185, -pppd.period_amount14,
3794 120, -pppd.period_amount14,
3795 pppd.period_amount14),
3796 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
3797 185, -pppd.period_amount15,
3798 120, -pppd.period_amount15,
3799 pppd.period_amount15),
3800 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
3801 185, -pppd.period_amount16,
3802 120, -pppd.period_amount16,
3803 pppd.period_amount16),
3804 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
3805 185, -pppd.period_amount17,
3806 120, -pppd.period_amount17,
3807 pppd.period_amount17),
3808 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
3809 185, -pppd.period_amount18,
3810 120, -pppd.period_amount18,
3811 pppd.period_amount18),
3812 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
3813 185, -pppd.period_amount19,
3814 120, -pppd.period_amount19,
3815 pppd.period_amount19),
3816 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
3817 185, -pppd.period_amount20,
3818 120, -pppd.period_amount20,
3819 pppd.period_amount20),
3820 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
3821 185, -pppd.period_amount21,
3822 120, -pppd.period_amount21,
3823 pppd.period_amount21),
3824 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
3825 185, -pppd.period_amount22,
3826 120, -pppd.period_amount22,
3827 pppd.period_amount22),
3828 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
3829 185, -pppd.period_amount23,
3830 120, -pppd.period_amount23,
3831 pppd.period_amount23),
3832 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
3833 185, -pppd.period_amount24,
3834 120, -pppd.period_amount24,
3835 pppd.period_amount24),
3836 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
3837 185, -pppd.period_amount25,
3838 120, -pppd.period_amount25,
3839 pppd.period_amount25),
3840 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
3841 185, -pppd.period_amount26,
3842 120, -pppd.period_amount26,
3843 pppd.period_amount26),
3844 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
3845 185, -pppd.period_amount27,
3846 120, -pppd.period_amount27,
3847 pppd.period_amount27),
3848 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
3849 185, -pppd.period_amount28,
3850 120, -pppd.period_amount28,
3851 pppd.period_amount28),
3852 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
3853 185, -pppd.period_amount29,
3854 120, -pppd.period_amount29,
3855 pppd.period_amount29),
3856 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
3857 185, -pppd.period_amount30,
3858 120, -pppd.period_amount30,
3859 pppd.period_amount30),
3860 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
3861 185, -pppd.period_amount31,
3862 120, -pppd.period_amount31,
3863 pppd.period_amount31),
3864 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
3865 185, -pppd.period_amount32,
3866 120, -pppd.period_amount32,
3867 pppd.period_amount32),
3868 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
3869 185, -pppd.period_amount33,
3870 120, -pppd.period_amount33,
3871 pppd.period_amount33),
3872 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
3873 185, -pppd.period_amount34,
3874 120, -pppd.period_amount34,
3875 pppd.period_amount34),
3876 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
3877 185, -pppd.period_amount35,
3878 120, -pppd.period_amount35,
3879 pppd.period_amount35),
3880 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
3881 185, -pppd.period_amount36,
3882 120, -pppd.period_amount36,
3883 pppd.period_amount36),
3884 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
3885 185, -pppd.period_amount37,
3886 120, -pppd.period_amount37,
3887 pppd.period_amount37),
3888 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
3889 185, -pppd.period_amount38,
3890 120, -pppd.period_amount38,
3891 pppd.period_amount38),
3892 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
3893 185, -pppd.period_amount39,
3894 120, -pppd.period_amount39,
3895 pppd.period_amount39),
3896 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
3897 185, -pppd.period_amount40,
3898 120, -pppd.period_amount40,
3899 pppd.period_amount40),
3900 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
3901 185, -pppd.period_amount41,
3902 120, -pppd.period_amount41,
3903 pppd.period_amount41),
3904 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
3905 185, -pppd.period_amount42,
3906 120, -pppd.period_amount42,
3907 pppd.period_amount42),
3908 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
3909 185, -pppd.period_amount43,
3910 120, -pppd.period_amount43,
3911 pppd.period_amount43),
3912 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
3913 185, -pppd.period_amount44,
3914 120, -pppd.period_amount44,
3915 pppd.period_amount44),
3916 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
3917 185, -pppd.period_amount45,
3918 120, -pppd.period_amount45,
3919 pppd.period_amount45),
3920 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
3921 185, -pppd.period_amount46,
3922 120, -pppd.period_amount46,
3923 pppd.period_amount46),
3924 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
3925 185, -pppd.period_amount47,
3926 120, -pppd.period_amount47,
3927 pppd.period_amount47),
3928 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
3929 185, -pppd.period_amount48,
3930 120, -pppd.period_amount48,
3931 pppd.period_amount48),
3932 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
3933 185, -pppd.period_amount49,
3934 120, -pppd.period_amount49,
3935 pppd.period_amount49),
3936 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
3937 185, -pppd.period_amount50,
3938 120, -pppd.period_amount50,
3939 pppd.period_amount50),
3940 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
3941 185, -pppd.period_amount51,
3942 120, -pppd.period_amount51,
3943 pppd.period_amount51),
3944 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
3945 185, -pppd.period_amount52,
3946 120, -pppd.period_amount52,
3947 pppd.period_amount52),0))
3948 into l_return_value
3949 from pa_proj_period_profiles pppp,
3950 pa_proj_periods_denorm pppd
3951 where pppp.period_profile_id = p_period_profile_id and
3952 pppp.period_profile_id=pppd.period_profile_id and
3953 pppd.amount_type_id = p_amount_type_id and
3954 pppd.resource_assignment_id = p_resource_assignment_id and
3955 pppd.currency_type = p_project_currency_type
3956 group by pppd.amount_type_id;
3957
3958 elsif p_period_number = 11 then
3959 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date11(),
3960 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
3961 185, -pppd.period_amount1,
3962 120, -pppd.period_amount1,
3963 pppd.period_amount1),
3964 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
3965 185, -pppd.period_amount2,
3966 120, -pppd.period_amount2,
3967 pppd.period_amount2),
3968 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
3969 185, -pppd.period_amount3,
3970 120, -pppd.period_amount3,
3971 pppd.period_amount3),
3972 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
3973 185, -pppd.period_amount4,
3974 120, -pppd.period_amount4,
3975 pppd.period_amount4),
3976 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
3977 185, -pppd.period_amount5,
3978 120, -pppd.period_amount5,
3979 pppd.period_amount5),
3980 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
3981 185, -pppd.period_amount6,
3982 120, -pppd.period_amount6,
3983 pppd.period_amount6),
3984 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
3985 185, -pppd.period_amount7,
3986 120, -pppd.period_amount7,
3987 pppd.period_amount7),
3988 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
3989 185, -pppd.period_amount8,
3990 120, -pppd.period_amount8,
3991 pppd.period_amount8),
3992 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
3993 185, -pppd.period_amount9,
3994 120, -pppd.period_amount9,
3995 pppd.period_amount9),
3996 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
3997 185, -pppd.period_amount10,
3998 120, -pppd.period_amount10,
3999 pppd.period_amount10),
4000 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
4001 185, -pppd.period_amount11,
4002 120, -pppd.period_amount11,
4003 pppd.period_amount11),
4004 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
4005 185, -pppd.period_amount12,
4006 120, -pppd.period_amount12,
4007 pppd.period_amount12),
4008 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
4009 185, -pppd.period_amount13,
4010 120, -pppd.period_amount13,
4011 pppd.period_amount13),
4012 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
4013 185, -pppd.period_amount14,
4014 120, -pppd.period_amount14,
4015 pppd.period_amount14),
4016 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
4017 185, -pppd.period_amount15,
4018 120, -pppd.period_amount15,
4019 pppd.period_amount15),
4020 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
4021 185, -pppd.period_amount16,
4022 120, -pppd.period_amount16,
4023 pppd.period_amount16),
4024 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
4025 185, -pppd.period_amount17,
4026 120, -pppd.period_amount17,
4027 pppd.period_amount17),
4028 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
4029 185, -pppd.period_amount18,
4030 120, -pppd.period_amount18,
4031 pppd.period_amount18),
4032 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
4033 185, -pppd.period_amount19,
4034 120, -pppd.period_amount19,
4035 pppd.period_amount19),
4036 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
4037 185, -pppd.period_amount20,
4038 120, -pppd.period_amount20,
4039 pppd.period_amount20),
4040 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
4041 185, -pppd.period_amount21,
4042 120, -pppd.period_amount21,
4043 pppd.period_amount21),
4044 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
4045 185, -pppd.period_amount22,
4046 120, -pppd.period_amount22,
4047 pppd.period_amount22),
4048 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
4049 185, -pppd.period_amount23,
4050 120, -pppd.period_amount23,
4051 pppd.period_amount23),
4052 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
4053 185, -pppd.period_amount24,
4054 120, -pppd.period_amount24,
4055 pppd.period_amount24),
4056 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
4057 185, -pppd.period_amount25,
4058 120, -pppd.period_amount25,
4059 pppd.period_amount25),
4060 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
4061 185, -pppd.period_amount26,
4062 120, -pppd.period_amount26,
4063 pppd.period_amount26),
4064 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
4065 185, -pppd.period_amount27,
4066 120, -pppd.period_amount27,
4067 pppd.period_amount27),
4068 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
4069 185, -pppd.period_amount28,
4070 120, -pppd.period_amount28,
4071 pppd.period_amount28),
4072 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
4073 185, -pppd.period_amount29,
4074 120, -pppd.period_amount29,
4075 pppd.period_amount29),
4076 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
4077 185, -pppd.period_amount30,
4078 120, -pppd.period_amount30,
4079 pppd.period_amount30),
4080 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
4081 185, -pppd.period_amount31,
4082 120, -pppd.period_amount31,
4083 pppd.period_amount31),
4084 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
4085 185, -pppd.period_amount32,
4086 120, -pppd.period_amount32,
4087 pppd.period_amount32),
4088 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
4089 185, -pppd.period_amount33,
4090 120, -pppd.period_amount33,
4091 pppd.period_amount33),
4092 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
4093 185, -pppd.period_amount34,
4094 120, -pppd.period_amount34,
4095 pppd.period_amount34),
4096 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
4097 185, -pppd.period_amount35,
4098 120, -pppd.period_amount35,
4099 pppd.period_amount35),
4100 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
4101 185, -pppd.period_amount36,
4102 120, -pppd.period_amount36,
4103 pppd.period_amount36),
4104 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
4105 185, -pppd.period_amount37,
4106 120, -pppd.period_amount37,
4107 pppd.period_amount37),
4108 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
4109 185, -pppd.period_amount38,
4110 120, -pppd.period_amount38,
4111 pppd.period_amount38),
4112 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
4113 185, -pppd.period_amount39,
4114 120, -pppd.period_amount39,
4115 pppd.period_amount39),
4116 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
4117 185, -pppd.period_amount40,
4118 120, -pppd.period_amount40,
4119 pppd.period_amount40),
4120 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
4121 185, -pppd.period_amount41,
4122 120, -pppd.period_amount41,
4123 pppd.period_amount41),
4124 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
4125 185, -pppd.period_amount42,
4126 120, -pppd.period_amount42,
4127 pppd.period_amount42),
4128 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
4129 185, -pppd.period_amount43,
4130 120, -pppd.period_amount43,
4131 pppd.period_amount43),
4132 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
4133 185, -pppd.period_amount44,
4134 120, -pppd.period_amount44,
4135 pppd.period_amount44),
4136 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
4137 185, -pppd.period_amount45,
4138 120, -pppd.period_amount45,
4139 pppd.period_amount45),
4140 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
4141 185, -pppd.period_amount46,
4142 120, -pppd.period_amount46,
4143 pppd.period_amount46),
4144 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
4145 185, -pppd.period_amount47,
4146 120, -pppd.period_amount47,
4147 pppd.period_amount47),
4148 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
4149 185, -pppd.period_amount48,
4150 120, -pppd.period_amount48,
4151 pppd.period_amount48),
4152 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
4153 185, -pppd.period_amount49,
4154 120, -pppd.period_amount49,
4155 pppd.period_amount49),
4156 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
4157 185, -pppd.period_amount50,
4158 120, -pppd.period_amount50,
4159 pppd.period_amount50),
4160 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
4161 185, -pppd.period_amount51,
4162 120, -pppd.period_amount51,
4163 pppd.period_amount51),
4164 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
4165 185, -pppd.period_amount52,
4166 120, -pppd.period_amount52,
4167 pppd.period_amount52),0))
4168 into l_return_value
4169 from pa_proj_period_profiles pppp,
4170 pa_proj_periods_denorm pppd
4171 where pppp.period_profile_id = p_period_profile_id and
4172 pppp.period_profile_id=pppd.period_profile_id and
4173 pppd.amount_type_id = p_amount_type_id and
4174 pppd.resource_assignment_id = p_resource_assignment_id and
4175 pppd.currency_type = p_project_currency_type
4176 group by pppd.amount_type_id;
4177
4178 elsif p_period_number = 12 then
4179 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date12(),
4180 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
4181 185, -pppd.period_amount1,
4182 120, -pppd.period_amount1,
4183 pppd.period_amount1),
4184 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
4185 185, -pppd.period_amount2,
4186 120, -pppd.period_amount2,
4187 pppd.period_amount2),
4188 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
4189 185, -pppd.period_amount3,
4190 120, -pppd.period_amount3,
4191 pppd.period_amount3),
4192 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
4193 185, -pppd.period_amount4,
4194 120, -pppd.period_amount4,
4195 pppd.period_amount4),
4196 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
4197 185, -pppd.period_amount5,
4198 120, -pppd.period_amount5,
4199 pppd.period_amount5),
4200 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
4201 185, -pppd.period_amount6,
4202 120, -pppd.period_amount6,
4203 pppd.period_amount6),
4204 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
4205 185, -pppd.period_amount7,
4206 120, -pppd.period_amount7,
4207 pppd.period_amount7),
4208 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
4209 185, -pppd.period_amount8,
4210 120, -pppd.period_amount8,
4211 pppd.period_amount8),
4212 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
4213 185, -pppd.period_amount9,
4214 120, -pppd.period_amount9,
4215 pppd.period_amount9),
4216 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
4217 185, -pppd.period_amount10,
4218 120, -pppd.period_amount10,
4219 pppd.period_amount10),
4220 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
4221 185, -pppd.period_amount11,
4222 120, -pppd.period_amount11,
4223 pppd.period_amount11),
4224 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
4225 185, -pppd.period_amount12,
4226 120, -pppd.period_amount12,
4227 pppd.period_amount12),
4228 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
4229 185, -pppd.period_amount13,
4230 120, -pppd.period_amount13,
4231 pppd.period_amount13),
4232 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
4233 185, -pppd.period_amount14,
4234 120, -pppd.period_amount14,
4235 pppd.period_amount14),
4236 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
4237 185, -pppd.period_amount15,
4238 120, -pppd.period_amount15,
4239 pppd.period_amount15),
4240 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
4241 185, -pppd.period_amount16,
4242 120, -pppd.period_amount16,
4243 pppd.period_amount16),
4244 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
4245 185, -pppd.period_amount17,
4246 120, -pppd.period_amount17,
4247 pppd.period_amount17),
4248 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
4249 185, -pppd.period_amount18,
4250 120, -pppd.period_amount18,
4251 pppd.period_amount18),
4252 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
4253 185, -pppd.period_amount19,
4254 120, -pppd.period_amount19,
4255 pppd.period_amount19),
4256 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
4257 185, -pppd.period_amount20,
4258 120, -pppd.period_amount20,
4259 pppd.period_amount20),
4260 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
4261 185, -pppd.period_amount21,
4262 120, -pppd.period_amount21,
4263 pppd.period_amount21),
4264 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
4265 185, -pppd.period_amount22,
4266 120, -pppd.period_amount22,
4267 pppd.period_amount22),
4268 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
4269 185, -pppd.period_amount23,
4270 120, -pppd.period_amount23,
4271 pppd.period_amount23),
4272 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
4273 185, -pppd.period_amount24,
4274 120, -pppd.period_amount24,
4275 pppd.period_amount24),
4276 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
4277 185, -pppd.period_amount25,
4278 120, -pppd.period_amount25,
4279 pppd.period_amount25),
4280 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
4281 185, -pppd.period_amount26,
4282 120, -pppd.period_amount26,
4283 pppd.period_amount26),
4284 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
4285 185, -pppd.period_amount27,
4286 120, -pppd.period_amount27,
4287 pppd.period_amount27),
4288 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
4289 185, -pppd.period_amount28,
4290 120, -pppd.period_amount28,
4291 pppd.period_amount28),
4292 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
4293 185, -pppd.period_amount29,
4294 120, -pppd.period_amount29,
4295 pppd.period_amount29),
4296 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
4297 185, -pppd.period_amount30,
4298 120, -pppd.period_amount30,
4299 pppd.period_amount30),
4300 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
4301 185, -pppd.period_amount31,
4302 120, -pppd.period_amount31,
4303 pppd.period_amount31),
4304 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
4305 185, -pppd.period_amount32,
4306 120, -pppd.period_amount32,
4307 pppd.period_amount32),
4308 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
4309 185, -pppd.period_amount33,
4310 120, -pppd.period_amount33,
4311 pppd.period_amount33),
4312 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
4313 185, -pppd.period_amount34,
4314 120, -pppd.period_amount34,
4315 pppd.period_amount34),
4316 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
4317 185, -pppd.period_amount35,
4318 120, -pppd.period_amount35,
4319 pppd.period_amount35),
4320 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
4321 185, -pppd.period_amount36,
4322 120, -pppd.period_amount36,
4323 pppd.period_amount36),
4324 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
4325 185, -pppd.period_amount37,
4326 120, -pppd.period_amount37,
4327 pppd.period_amount37),
4328 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
4329 185, -pppd.period_amount38,
4330 120, -pppd.period_amount38,
4331 pppd.period_amount38),
4332 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
4333 185, -pppd.period_amount39,
4334 120, -pppd.period_amount39,
4335 pppd.period_amount39),
4336 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
4337 185, -pppd.period_amount40,
4338 120, -pppd.period_amount40,
4339 pppd.period_amount40),
4340 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
4341 185, -pppd.period_amount41,
4342 120, -pppd.period_amount41,
4343 pppd.period_amount41),
4344 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
4345 185, -pppd.period_amount42,
4346 120, -pppd.period_amount42,
4347 pppd.period_amount42),
4348 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
4349 185, -pppd.period_amount43,
4350 120, -pppd.period_amount43,
4351 pppd.period_amount43),
4352 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
4353 185, -pppd.period_amount44,
4354 120, -pppd.period_amount44,
4355 pppd.period_amount44),
4356 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
4357 185, -pppd.period_amount45,
4358 120, -pppd.period_amount45,
4359 pppd.period_amount45),
4360 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
4361 185, -pppd.period_amount46,
4362 120, -pppd.period_amount46,
4363 pppd.period_amount46),
4364 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
4365 185, -pppd.period_amount47,
4366 120, -pppd.period_amount47,
4367 pppd.period_amount47),
4368 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
4369 185, -pppd.period_amount48,
4370 120, -pppd.period_amount48,
4371 pppd.period_amount48),
4372 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
4373 185, -pppd.period_amount49,
4374 120, -pppd.period_amount49,
4375 pppd.period_amount49),
4376 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
4377 185, -pppd.period_amount50,
4378 120, -pppd.period_amount50,
4379 pppd.period_amount50),
4380 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
4381 185, -pppd.period_amount51,
4382 120, -pppd.period_amount51,
4383 pppd.period_amount51),
4384 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
4385 185, -pppd.period_amount52,
4386 120, -pppd.period_amount52,
4387 pppd.period_amount52),0))
4388 into l_return_value
4389 from pa_proj_period_profiles pppp,
4390 pa_proj_periods_denorm pppd
4391 where pppp.period_profile_id = p_period_profile_id and
4392 pppp.period_profile_id=pppd.period_profile_id and
4393 pppd.amount_type_id = p_amount_type_id and
4394 pppd.resource_assignment_id = p_resource_assignment_id and
4395 pppd.currency_type = p_project_currency_type
4396 group by pppd.amount_type_id;
4397
4398 elsif p_period_number = 13 then
4399 select SUM(DECODE(pa_fp_view_plans_pub.Get_Period_Start_Date13(),
4400 pppp.period1_start_Date, DECODE(pppd.amount_subtype_id,
4401 185, -pppd.period_amount1,
4402 120, -pppd.period_amount1,
4403 pppd.period_amount1),
4404 pppp.period2_start_Date, DECODE(pppd.amount_subtype_id,
4405 185, -pppd.period_amount2,
4406 120, -pppd.period_amount2,
4407 pppd.period_amount2),
4408 pppp.period3_start_Date, DECODE(pppd.amount_subtype_id,
4409 185, -pppd.period_amount3,
4410 120, -pppd.period_amount3,
4411 pppd.period_amount3),
4412 pppp.period4_start_Date, DECODE(pppd.amount_subtype_id,
4413 185, -pppd.period_amount4,
4414 120, -pppd.period_amount4,
4415 pppd.period_amount4),
4416 pppp.period5_start_Date, DECODE(pppd.amount_subtype_id,
4417 185, -pppd.period_amount5,
4418 120, -pppd.period_amount5,
4419 pppd.period_amount5),
4420 pppp.period6_start_Date, DECODE(pppd.amount_subtype_id,
4421 185, -pppd.period_amount6,
4422 120, -pppd.period_amount6,
4423 pppd.period_amount6),
4424 pppp.period7_start_Date, DECODE(pppd.amount_subtype_id,
4425 185, -pppd.period_amount7,
4426 120, -pppd.period_amount7,
4427 pppd.period_amount7),
4428 pppp.period8_start_Date, DECODE(pppd.amount_subtype_id,
4429 185, -pppd.period_amount8,
4430 120, -pppd.period_amount8,
4431 pppd.period_amount8),
4432 pppp.period9_start_Date, DECODE(pppd.amount_subtype_id,
4433 185, -pppd.period_amount9,
4434 120, -pppd.period_amount9,
4435 pppd.period_amount9),
4436 pppp.period10_start_Date, DECODE(pppd.amount_subtype_id,
4437 185, -pppd.period_amount10,
4438 120, -pppd.period_amount10,
4439 pppd.period_amount10),
4440 pppp.period11_start_Date, DECODE(pppd.amount_subtype_id,
4441 185, -pppd.period_amount11,
4442 120, -pppd.period_amount11,
4443 pppd.period_amount11),
4444 pppp.period12_start_Date, DECODE(pppd.amount_subtype_id,
4445 185, -pppd.period_amount12,
4446 120, -pppd.period_amount12,
4447 pppd.period_amount12),
4448 pppp.period13_start_Date, DECODE(pppd.amount_subtype_id,
4449 185, -pppd.period_amount13,
4450 120, -pppd.period_amount13,
4451 pppd.period_amount13),
4452 pppp.period14_start_Date, DECODE(pppd.amount_subtype_id,
4453 185, -pppd.period_amount14,
4454 120, -pppd.period_amount14,
4455 pppd.period_amount14),
4456 pppp.period15_start_Date, DECODE(pppd.amount_subtype_id,
4457 185, -pppd.period_amount15,
4458 120, -pppd.period_amount15,
4459 pppd.period_amount15),
4460 pppp.period16_start_Date, DECODE(pppd.amount_subtype_id,
4461 185, -pppd.period_amount16,
4462 120, -pppd.period_amount16,
4463 pppd.period_amount16),
4464 pppp.period17_start_Date, DECODE(pppd.amount_subtype_id,
4465 185, -pppd.period_amount17,
4466 120, -pppd.period_amount17,
4467 pppd.period_amount17),
4468 pppp.period18_start_Date, DECODE(pppd.amount_subtype_id,
4469 185, -pppd.period_amount18,
4470 120, -pppd.period_amount18,
4471 pppd.period_amount18),
4472 pppp.period19_start_Date, DECODE(pppd.amount_subtype_id,
4473 185, -pppd.period_amount19,
4474 120, -pppd.period_amount19,
4475 pppd.period_amount19),
4476 pppp.period20_start_Date, DECODE(pppd.amount_subtype_id,
4477 185, -pppd.period_amount20,
4478 120, -pppd.period_amount20,
4479 pppd.period_amount20),
4480 pppp.period21_start_Date, DECODE(pppd.amount_subtype_id,
4481 185, -pppd.period_amount21,
4482 120, -pppd.period_amount21,
4483 pppd.period_amount21),
4484 pppp.period22_start_Date, DECODE(pppd.amount_subtype_id,
4485 185, -pppd.period_amount22,
4486 120, -pppd.period_amount22,
4487 pppd.period_amount22),
4488 pppp.period23_start_Date, DECODE(pppd.amount_subtype_id,
4489 185, -pppd.period_amount23,
4490 120, -pppd.period_amount23,
4491 pppd.period_amount23),
4492 pppp.period24_start_Date, DECODE(pppd.amount_subtype_id,
4493 185, -pppd.period_amount24,
4494 120, -pppd.period_amount24,
4495 pppd.period_amount24),
4496 pppp.period25_start_Date, DECODE(pppd.amount_subtype_id,
4497 185, -pppd.period_amount25,
4498 120, -pppd.period_amount25,
4499 pppd.period_amount25),
4500 pppp.period26_start_Date, DECODE(pppd.amount_subtype_id,
4501 185, -pppd.period_amount26,
4502 120, -pppd.period_amount26,
4503 pppd.period_amount26),
4504 pppp.period27_start_Date, DECODE(pppd.amount_subtype_id,
4505 185, -pppd.period_amount27,
4506 120, -pppd.period_amount27,
4507 pppd.period_amount27),
4508 pppp.period28_start_Date, DECODE(pppd.amount_subtype_id,
4509 185, -pppd.period_amount28,
4510 120, -pppd.period_amount28,
4511 pppd.period_amount28),
4512 pppp.period29_start_Date, DECODE(pppd.amount_subtype_id,
4513 185, -pppd.period_amount29,
4514 120, -pppd.period_amount29,
4515 pppd.period_amount29),
4516 pppp.period30_start_Date, DECODE(pppd.amount_subtype_id,
4517 185, -pppd.period_amount30,
4518 120, -pppd.period_amount30,
4519 pppd.period_amount30),
4520 pppp.period31_start_Date, DECODE(pppd.amount_subtype_id,
4521 185, -pppd.period_amount31,
4522 120, -pppd.period_amount31,
4523 pppd.period_amount31),
4524 pppp.period32_start_Date, DECODE(pppd.amount_subtype_id,
4525 185, -pppd.period_amount32,
4526 120, -pppd.period_amount32,
4527 pppd.period_amount32),
4528 pppp.period33_start_Date, DECODE(pppd.amount_subtype_id,
4529 185, -pppd.period_amount33,
4530 120, -pppd.period_amount33,
4531 pppd.period_amount33),
4532 pppp.period34_start_Date, DECODE(pppd.amount_subtype_id,
4533 185, -pppd.period_amount34,
4534 120, -pppd.period_amount34,
4535 pppd.period_amount34),
4536 pppp.period35_start_Date, DECODE(pppd.amount_subtype_id,
4537 185, -pppd.period_amount35,
4538 120, -pppd.period_amount35,
4539 pppd.period_amount35),
4540 pppp.period36_start_Date, DECODE(pppd.amount_subtype_id,
4541 185, -pppd.period_amount36,
4542 120, -pppd.period_amount36,
4543 pppd.period_amount36),
4544 pppp.period37_start_Date, DECODE(pppd.amount_subtype_id,
4545 185, -pppd.period_amount37,
4546 120, -pppd.period_amount37,
4547 pppd.period_amount37),
4548 pppp.period38_start_Date, DECODE(pppd.amount_subtype_id,
4549 185, -pppd.period_amount38,
4550 120, -pppd.period_amount38,
4551 pppd.period_amount38),
4552 pppp.period39_start_Date, DECODE(pppd.amount_subtype_id,
4553 185, -pppd.period_amount39,
4554 120, -pppd.period_amount39,
4555 pppd.period_amount39),
4556 pppp.period40_start_Date, DECODE(pppd.amount_subtype_id,
4557 185, -pppd.period_amount40,
4558 120, -pppd.period_amount40,
4559 pppd.period_amount40),
4560 pppp.period41_start_Date, DECODE(pppd.amount_subtype_id,
4561 185, -pppd.period_amount41,
4562 120, -pppd.period_amount41,
4563 pppd.period_amount41),
4564 pppp.period42_start_Date, DECODE(pppd.amount_subtype_id,
4565 185, -pppd.period_amount42,
4566 120, -pppd.period_amount42,
4567 pppd.period_amount42),
4568 pppp.period43_start_Date, DECODE(pppd.amount_subtype_id,
4569 185, -pppd.period_amount43,
4570 120, -pppd.period_amount43,
4571 pppd.period_amount43),
4572 pppp.period44_start_Date, DECODE(pppd.amount_subtype_id,
4573 185, -pppd.period_amount44,
4574 120, -pppd.period_amount44,
4575 pppd.period_amount44),
4576 pppp.period45_start_Date, DECODE(pppd.amount_subtype_id,
4577 185, -pppd.period_amount45,
4578 120, -pppd.period_amount45,
4579 pppd.period_amount45),
4580 pppp.period46_start_Date, DECODE(pppd.amount_subtype_id,
4581 185, -pppd.period_amount46,
4582 120, -pppd.period_amount46,
4583 pppd.period_amount46),
4584 pppp.period47_start_Date, DECODE(pppd.amount_subtype_id,
4585 185, -pppd.period_amount47,
4586 120, -pppd.period_amount47,
4587 pppd.period_amount47),
4588 pppp.period48_start_Date, DECODE(pppd.amount_subtype_id,
4589 185, -pppd.period_amount48,
4590 120, -pppd.period_amount48,
4591 pppd.period_amount48),
4592 pppp.period49_start_Date, DECODE(pppd.amount_subtype_id,
4593 185, -pppd.period_amount49,
4594 120, -pppd.period_amount49,
4595 pppd.period_amount49),
4596 pppp.period50_start_Date, DECODE(pppd.amount_subtype_id,
4597 185, -pppd.period_amount50,
4598 120, -pppd.period_amount50,
4599 pppd.period_amount50),
4600 pppp.period51_start_Date, DECODE(pppd.amount_subtype_id,
4601 185, -pppd.period_amount51,
4602 120, -pppd.period_amount51,
4603 pppd.period_amount51),
4604 pppp.period52_start_Date, DECODE(pppd.amount_subtype_id,
4605 185, -pppd.period_amount52,
4606 120, -pppd.period_amount52,
4607 pppd.period_amount52),0))
4608 into l_return_value
4609 from pa_proj_period_profiles pppp,
4610 pa_proj_periods_denorm pppd
4611 where pppp.period_profile_id = p_period_profile_id and
4612 pppp.period_profile_id=pppd.period_profile_id and
4613 pppd.amount_type_id = p_amount_type_id and
4614 pppd.resource_assignment_id = p_resource_assignment_id and
4615 pppd.currency_type = p_project_currency_type
4616 group by pppd.amount_type_id;
4617 end if;
4618 return l_return_value;
4619
4620 EXCEPTION
4621 WHEN NO_DATA_FOUND THEN
4622 return null;
4623 end get_period_n_value;
4624 */
4625
4626 /* --------------------------------------------------------------------- */
4627
4628 FUNCTION calc_margin_percent
4629 (p_cost_value IN NUMBER,
4630 p_rev_value IN NUMBER) return NUMBER
4631 is
4632 BEGIN
4633 if (p_rev_value is null) or (p_cost_value is null) then
4634 return null;
4635 elsif (p_rev_value = 0) or ( p_cost_value = 0) then -- Added the OR condition for bug 3651389
4636 return 0;
4637 else
4638 return (p_rev_value-p_cost_value)/p_rev_value;
4639 end if;
4640 END calc_margin_percent;
4641 /* --------------------------------------------------------------------- */
4642
4643 PROCEDURE refresh_period_profile
4644 (p_project_id IN pa_projects_all.project_id%TYPE,
4645 p_budget_version_id1 IN pa_budget_versions.budget_version_id%TYPE,
4646 p_budget_version_id2 IN pa_budget_versions.budget_version_id%TYPE,
4647 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4648 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
4649 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4650 is
4651 l_gl_pd_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
4652 l_pa_pd_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
4653 l_rpt_request_id NUMBER;
4654 x_conc_req_id NUMBER;
4655
4656 -- error-handling variables
4657 l_debug_mode VARCHAR2(1) := 'Y';
4658
4659 cursor GL_ppId is
4660 select period_profile_id
4661 from pa_proj_period_profiles
4662 where project_id = p_project_id and
4663 plan_period_type = 'GL' and
4664 current_flag ='Y';
4665 GL_ppId_rec GL_ppId%ROWTYPE;
4666
4667 cursor PA_ppId is
4668 select period_profile_id
4669 from pa_proj_period_profiles
4670 where project_id = p_project_id and
4671 plan_period_type = 'PA' and
4672 current_flag ='Y';
4673 PA_ppId_rec GL_ppId%ROWTYPE;
4674
4675 BEGIN
4676 x_return_status := FND_API.G_RET_STS_SUCCESS;
4677 -- RETRIEVE the current period_profile_id's for GL
4678 open GL_ppId;
4679 fetch GL_ppId into GL_ppId_rec;
4680 if GL_ppId%NOTFOUND then
4681 pa_debug.write('pa_fp_view_plans_util.refresh_period_profile', 'no ppid for GL', 2);
4682 else
4683 l_gl_pd_profile_id := GL_ppId_rec.period_profile_id;
4684 end if;
4685 close GL_ppId;
4686 -- RETRIEVE the current period_profile_id's for PA
4687 open PA_ppId;
4688 fetch PA_ppId into PA_ppId_rec;
4689 if PA_ppId%NOTFOUND then
4690 pa_debug.write('pa_fp_view_plans_util.refresh_period_profile', 'no ppid for PA', 2);
4691 else
4692 l_pa_pd_profile_id := PA_ppId_rec.period_profile_id;
4693 end if;
4694 close PA_ppId;
4695
4696 l_rpt_request_id := FND_REQUEST.submit_request
4697 (application => 'PA',
4698 program => 'PAPDPROF',
4699 description => 'PRC: Refresh Plan Versions Period Profile',
4700 start_time => NULL,
4701 sub_request => false,
4702 argument1 => p_budget_version_id1,
4703 argument2 => p_budget_version_id2,
4704 argument3 => p_project_id,
4705 argument4 => NULL,
4706 argument5 => l_gl_pd_profile_id,
4707 argument6 => l_pa_pd_profile_id,
4708 argument7 => l_debug_mode);
4709 IF l_rpt_request_id = 0 then
4710 PA_DEBUG.g_err_stage := 'Error while submitting Report [PAFPEXRP]';
4711 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4712 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
4713 p_msg_name => 'PA_FP_PP_CONC_PGM_ERR');
4714 x_return_status := FND_API.G_RET_STS_ERROR;
4715 ROLLBACK;
4716 RETURN;
4717 ELSE
4718 PA_DEBUG.g_err_stage := 'Exception Report Request Id : ' ||
4719 LTRIM(TO_CHAR(l_rpt_request_id )) ;
4720 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
4721 p_write_file => 'OUT',
4722 p_write_mode => 1);
4723 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
4724 END IF;
4725 x_conc_req_id := LTRIM(RTRIM(TO_CHAR(l_rpt_request_id)));
4726
4727 -- bug 2740907: stamp request_id into pa_budget_versions
4728 -- set locked_by_person_id=-99: locked by processing
4729 update pa_budget_versions
4730 set plan_processing_code = 'PPP',
4731 record_version_number = record_version_number + 1,
4732 request_id = x_conc_req_id,
4733 locked_by_person_id = -98
4734 where budget_version_id = p_budget_version_id1;
4735 update pa_budget_versions
4736 set plan_processing_code = 'PPP',
4737 record_version_number = record_version_number + 1,
4738 request_id = x_conc_req_id,
4739 locked_by_person_id = -98
4740 where budget_version_id = p_budget_version_id2;
4741 IF x_return_Status = FND_API.G_RET_STS_SUCCESS THEN
4742 COMMIT;
4743 ELSE
4744 ROLLBACK;
4745 END IF;
4746
4747 END refresh_period_profile;
4748 /* --------------------------------------------------------------------- */
4749
4750 FUNCTION has_period_profile_id
4751 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE)
4752 return VARCHAR2 is
4753 l_return_value VARCHAR2(1);
4754 BEGIN
4755 l_return_value := 'N';
4756 select 'Y'
4757 into l_return_value
4758 from pa_budget_versions
4759 where budget_version_id = p_budget_version_id and
4760 period_profile_id is not null;
4761 return l_return_value;
4762 EXCEPTION
4763 WHEN NO_DATA_FOUND THEN
4764 return l_return_value;
4765 END has_period_profile_id;
4766
4767 /*************************************************************/
4768 procedure roll_up_budget_lines
4769 (p_budget_version_id in pa_budget_versions.budget_version_id%TYPE,
4770 p_cost_or_rev in VARCHAR2)
4771 is
4772
4773 cursor l_ra_csr is
4774 select resource_assignment_id
4775 from pa_resource_assignments
4776 where budget_version_id = p_budget_version_id;
4777 l_ra_rec l_ra_csr%ROWTYPE;
4778
4779 /* local variables */
4780 l_resource_assignment_id pa_resource_assignments.resource_assignment_id%TYPE;
4781 l_line_costrev_total pa_resource_assignments.total_plan_revenue%TYPE;
4782 l_line_quantity_total pa_resource_assignments.total_plan_quantity%TYPE;
4783 begin
4784 open l_ra_csr;
4785 if p_cost_or_rev = 'REVENUE' then
4786 -- roll up the revenue budget version
4787 loop
4788 fetch l_ra_csr into l_ra_rec;
4789 exit when l_ra_csr%NOTFOUND;
4790 select SUM(nvl(quantity,0)),
4791 SUM(nvl(revenue,0))
4792 into l_line_quantity_total,
4793 l_line_costrev_total
4794 from pa_budget_lines
4795 where resource_assignment_id = l_ra_rec.resource_assignment_id
4796 group by resource_assignment_id;
4797 update pa_resource_assignments
4798 set total_plan_revenue = l_line_costrev_total,
4799 total_utilization_hours = l_line_quantity_total
4800 where
4801 resource_assignment_id = l_ra_rec.resource_assignment_id;
4802 end loop;
4803 else
4804 -- roll up the cost budget version
4805 loop
4806 fetch l_ra_csr into l_ra_rec;
4807 exit when l_ra_csr%NOTFOUND;
4808 select SUM(nvl(quantity,0)),
4809 SUM(nvl(burdened_cost,0))
4810 into l_line_quantity_total,
4811 l_line_costrev_total
4812 from pa_budget_lines
4813 where resource_assignment_id = l_ra_rec.resource_assignment_id
4814 group by resource_assignment_id;
4815 update pa_resource_assignments
4816 set total_plan_burdened_cost = l_line_costrev_total,
4817 total_utilization_hours = l_line_quantity_total
4818 where
4819 resource_assignment_id = l_ra_rec.resource_assignment_id;
4820 end loop;
4821 end if;
4822 commit;
4823 close l_ra_csr;
4824 end roll_up_budget_lines;
4825
4826
4827 FUNCTION get_amttype_id
4828 ( p_amt_typ_code IN pa_amount_types_b.amount_type_code%TYPE) RETURN NUMBER
4829 is
4830 l_amount_type_id pa_amount_types_b.amount_type_id%TYPE;
4831 l_amt_code pa_fp_view_plans_util.char240_data_type_table;
4832 l_amt_id pa_fp_view_plans_util.number_data_type_table;
4833
4834 l_debug_mode VARCHAR2(30);
4835
4836 CURSOR get_amt_det IS
4837 SELECT atb.amount_type_id
4838 ,atb.amount_type_code
4839 FROM pa_amount_types_b atb
4840 WHERE atb.amount_type_class = 'R';
4841
4842 l_stage number := 0;
4843
4844 BEGIN
4845 pa_debug.init_err_stack('PA_FP_ORG_FCST_GEN_PUB.get_amttype_id');
4846
4847 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4848 l_debug_mode := NVL(l_debug_mode, 'Y');
4849
4850 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4851
4852 l_amount_type_id := -99;
4853
4854 IF l_amt_code.last IS NULL THEN
4855 OPEN get_amt_det;
4856 LOOP
4857 FETCH get_amt_det into l_amt_id(nvl(l_amt_id.last+1,1))
4858 ,l_amt_code(nvl(l_amt_code.last+1,1));
4859 EXIT WHEN get_amt_det%NOTFOUND;
4860 END LOOP;
4861 END IF;
4862
4863 IF l_amt_code.last IS NOT NULL THEN
4864 FOR i in l_amt_id.first..l_amt_id.last LOOP
4865 IF l_amt_code(i) = p_amt_typ_code THEN
4866 l_amount_type_id := l_amt_id(i);
4867 END IF;
4868 END LOOP;
4869 END IF;
4870 IF l_amount_type_id = -99 THEN
4871 pa_debug.g_err_stage := 'p_amt_typ_code ['||p_amt_typ_code ||']';
4872 pa_debug.write_file(pa_debug.g_err_stage);
4873 END IF;
4874 pa_debug.reset_err_stack;
4875 RETURN(l_amount_type_id);
4876
4877 EXCEPTION
4878 WHEN OTHERS THEN
4879 FND_MSG_PUB.add_exc_msg(
4880 p_pkg_name => 'PA_FP_ORG_FCST_GEN_PUB.get_amttype_id'
4881 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
4882
4883 pa_debug.write_file(SQLERRM);
4884 pa_debug.reset_err_stack;
4885 RAISE;
4886 END get_amttype_id;
4887
4888
4889 -- FP L: used in View/Edit Plan page whenever navigation option to View/Edit
4890 -- Plan Line page is chosen. If the resource assignment has been deleted by
4891 -- WBS, an error needs to be displayed
4892 procedure check_res_assignment_exists
4893 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
4894 x_res_assignment_exists OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4895 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4896 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
4897 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
4898 l_res_assignment_exists VARCHAR2(1);
4899 l_msg_index_out NUMBER(30);
4900 BEGIN
4901 x_msg_count := 0;
4902 x_return_status := FND_API.G_RET_STS_SUCCESS;
4903 select 'Y'
4904 into l_res_assignment_exists
4905 from dual
4906 where exists
4907 (select resource_assignment_id
4908 from pa_resource_assignments
4909 where resource_assignment_id = p_resource_assignment_id);
4910 if l_res_assignment_exists = 'Y' then
4911 x_res_assignment_exists := 'Y';
4912 else
4913 x_res_assignment_exists := 'N';
4914 end if;
4915 return;
4916 EXCEPTION
4917 WHEN NO_DATA_FOUND THEN
4918 x_res_assignment_exists := 'N';
4919 /*
4920 PA_UTILS.Add_Message(p_app_short_name => 'PA',
4921 p_msg_name => 'PA_FP_EPL_TASK_UPDATED',
4922 p_token1 => 'TASK_NAME',
4923 p_value1 => p_task_name,
4924 p_token2 => 'TASK_NUMBER',
4925 p_value2 => p_task_number);
4926 x_return_status := FND_API.G_RET_STS_ERROR;
4927 x_msg_count := FND_MSG_PUB.Count_Msg;
4928 if x_msg_count = 1 then
4929 PA_INTERFACE_UTILS_PUB.get_messages
4930 (p_encoded => FND_API.G_TRUE,
4931 p_msg_index => 1,
4932 p_data => x_msg_data,
4933 p_msg_index_out => l_msg_index_out);
4934 end if;
4935 return;
4936 */
4937 WHEN OTHERS THEN
4938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4939 x_msg_count := 1;
4940 x_msg_data := SQLERRM;
4941 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fp_view_plans_util',
4942 p_procedure_name => 'check_res_assignment_exists');
4943 pa_debug.reset_err_stack;
4944 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4945 END check_res_assignment_exists;
4946
4947
4948 -- FP L: used in View/Edit Plan page to determine if a plan version is planned
4949 -- at a resource or resource group level (bug 2813661)
4950 -- NOTE: THIS PROCEDURE IS USED ONLY FOR COLUMN DISPLAY PURPOSES: IT CONTAINS LOGIC
4951 -- THAT IS USED TO HIDE/SHOW THE RESOURCE AND/OR RESOURCE GROUP COLUMNS. IT
4952 -- CONTAINS DISPLAY LOGIC THAT MAY NOT BE DIRECTLY RELEVANT TO THE ACTUAL
4953 -- PLANNING LEVEL OF THE VERSION.
4954 -- ** p_entered_amts_only_flag = 'Y' if this is used by the View Plan page
4955 -- (query only rows with entered amts), and 'N' if used by Edit Plan page
4956
4957 -- Bug 3081511 After the bug fix, the logic to display 'Resource' and
4958 -- 'Resource Group' columns in edit/view plan pages stands as follows:
4959 -- Case 1: If the resource list is a uncategorized, value 'N' is returned.
4960 -- So, neither 'Resource Group' column nor 'Resource' column would
4961 -- be rendered.
4962 -- Case 2: If the resource list is 'Ungrouped' value 'R' is returned. Only
4963 -- 'Resource' column is shown.
4964 -- Case 3: If the resource list is 'Grouped' and if the entire version is
4965 -- planned at 'Resource Group' level, value 'G' is returned.
4966 -- Only 'Resource Group' columns would be shown
4967 -- Case 4: If the resource list is 'Grouped' and if any of the resources
4968 -- have been planned for value 'M' is returned.
4969 -- Both resource group and resource columns are rendered.
4970
4971 procedure get_plan_version_res_level
4972 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
4973 p_entered_amts_only_flag IN VARCHAR2,
4974 x_resource_level OUT NOCOPY VARCHAR2, -- 'R' = resource, 'G' = resource group, --File.Sql.39 bug 4440895
4975 -- 'M' = mixed, -- 'N' = not applicable (NONE resource list)
4976 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4977 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
4978 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
4979
4980 l_group_resource_type_id pa_resource_lists.group_resource_type_id%TYPE;
4981 l_uncategorized_flag pa_resource_lists.uncategorized_flag%TYPE; -- bug 3081511
4982 l_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
4983 l_prj_rlm_id pa_resource_list_members.resource_list_member_id%TYPE;
4984 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4985 l_version_type pa_budget_versions.version_type%TYPE;
4986 l_resource_level VARCHAR2(1); -- used to store temp value of return variable
4987 l_resource_list_id pa_resource_lists_all_bg.resource_list_id%TYPE;
4988
4989 cursor c1(c_fp_opt_id number,
4990 c_ver_type varchar2) is
4991 select fpe.task_id,
4992 fpe.resource_planning_level
4993 from
4994 pa_fp_elements fpe,
4995 pa_tasks t
4996 where
4997 fpe.proj_fp_options_id = c_fp_opt_id and
4998 fpe.element_type = c_ver_type and
4999 fpe.task_id = t.task_id and
5000 fpe.resource_list_member_id = 0 and
5001 fpe.plannable_flag = 'Y';
5002 c1_rec c1%ROWTYPE;
5003
5004 BEGIN
5005 x_return_status := FND_API.G_RET_STS_SUCCESS;
5006 x_msg_count := 0;
5007
5008 /* If group_resource_type_id is 0, then version could be planned by Resources,
5009 or the resource list could be None. Therefore, we need to check the
5010 uncategorized_flag as well (uncategorized_flag='Y' means resource list is None)
5011 */
5012 select nvl(rl.group_resource_type_id,0),
5013 nvl(rl.uncategorized_flag, 'N'),
5014 rl.resource_list_id
5015 into l_group_resource_type_id,
5016 l_uncategorized_flag,
5017 l_resource_list_id
5018 from pa_budget_versions bv,
5019 pa_resource_lists_all_bg rl
5020 where bv.budget_version_id = p_budget_version_id
5021 and bv.resource_list_id = rl.resource_list_id;
5022
5023 if l_group_resource_type_id = 0 then
5024 if l_uncategorized_flag = 'N' then
5025 x_resource_level := 'R';
5026 else
5027 x_resource_level := 'N';
5028 end if;
5029 -- for an ungrouped resource list only these are the possible values.
5030 -- no further processing is required
5031 pa_debug.reset_err_stack;
5032 return;
5033 end if; -- l_group_resource_type_id = 0
5034
5035 -- fetch proj_fp_options_id and planning level of the version
5036
5037 select po.proj_fp_options_id,
5038 DECODE(po.fin_plan_preference_code,
5039 'COST_ONLY', po.cost_fin_plan_level_code,
5040 'REVENUE_ONLY', po.revenue_fin_plan_level_code,
5041 po.all_fin_plan_level_code),
5042 bv.version_type
5043 into l_proj_fp_options_id,
5044 l_fin_plan_level_code,
5045 l_version_type
5046 from pa_proj_fp_options po,
5047 pa_budget_versions bv
5048 where bv.budget_version_id = p_budget_version_id and
5049 bv.budget_version_id = po.fin_plan_version_id and
5050 po.fin_plan_option_level_code = 'PLAN_VERSION';
5051
5052 -- Processing is different if the verison is planned at 'project' level or task level
5053 if l_fin_plan_level_code = 'P' then
5054 /*** PROJECT-LEVEL PLANNING ***/
5055 begin
5056 select ra.resource_list_member_id
5057 into l_prj_rlm_id
5058 from pa_resource_assignments ra
5059 where ra.budget_version_id = p_budget_version_id and
5060 nvl(ra.resource_assignment_type,'USER_ENTERED') = 'USER_ENTERED' and
5061 rownum < 2;
5062 exception
5063 when no_data_found then
5064 -- no planning elements returned: return 'N'
5065 x_resource_level := 'N';
5066 l_prj_rlm_id := null;
5067 pa_debug.reset_err_stack;
5068 return;
5069 end;
5070 if nvl(l_prj_rlm_id,0) > 0 then
5071 select decode(parent_member_id,null,'G','R')
5072 into x_resource_level
5073 from pa_resource_list_members
5074 where resource_list_member_id = l_prj_rlm_id;
5075 end if;
5076 else
5077 /*** NOT PROJECT-LEVEL PLANNING ***/
5078 open c1(l_proj_fp_options_id, l_version_type);
5079 fetch c1 into c1_rec;
5080 if c1%NOTFOUND then
5081 -- no planning elements returned: return 'N'
5082 x_resource_level := 'N';
5083 pa_debug.reset_err_stack;
5084 return;
5085 else
5086 /* loop through the records, checking the 'resource_planning_level' attribute
5087 * if they are ALL 'R', then x_resource_level := 'R'
5088 * if they are ALL 'G', then x_resource_level := 'G'
5089 * otherwise, x_resource_level = 'M'
5090 */
5091 l_resource_level := nvl(c1_rec.resource_planning_level, 'M');
5092 loop
5093 fetch c1 into c1_rec;
5094 exit when c1%NOTFOUND;
5095 if nvl(c1_rec.resource_planning_level, 'M') <> l_resource_level then
5096 l_resource_level := 'M';
5097 exit;
5098 end if;
5099 end loop;
5100 x_resource_level := l_resource_level;
5101 end if;
5102 close c1;
5103 end if;
5104
5105 -- If the resource list is grouped and all the resources have been planned
5106 -- at 'R'level, we need to show the 'resource group' column also in edit
5107 -- and view plan pages for clarity purposes.
5108
5109 if x_resource_level = 'R' and l_group_resource_type_id <> 0 then
5110 x_resource_level := 'M';
5111 end if;
5112 pa_debug.reset_err_stack;
5113 EXCEPTION
5114 WHEN OTHERS THEN
5115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5116 x_msg_count := 1;
5117 x_msg_data := SQLERRM;
5118 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'pa_fp_view_plans_util',
5119 p_procedure_name => 'get_plan_version_res_level');
5120 pa_debug.reset_err_stack;
5121 raise FND_API.G_EXC_UNEXPECTED_ERROR;
5122 END get_plan_version_res_level;
5123
5124 end pa_fp_view_plans_util;