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