1 package body PJI_COMPLETION_GRAPH as
2 /* $Header: PJIATCGB.pls 120.3 2011/03/25 05:52:53 arbandyo ship $ */
3 procedure get_bgt_ver_period(
4 p_budget_cost_plan_id IN Number default null,
5 p_budget_rev_plan_id IN Number default null,
6 p_forecast_cost_plan_id IN Number default null,
7 p_forecast_rev_plan_id IN Number default null,
8 p_project_id IN Number,
9 p_table_identifier Varchar ,
10 p_calendar_id IN Number default null) is
11
12 /* Modified for bug 9557889 */
13 tab_budget_cost_version_id PA_PLSQL_DATATYPES.IdTabTyp;
14 tab_budget_cost_str_date PA_PLSQL_DATATYPES.DateTabTyp;
15 tab_budget_cost_end_date PA_PLSQL_DATATYPES.DateTabTyp;
16 tab_budget_rev_version_id PA_PLSQL_DATATYPES.IdTabTyp;
17 tab_budget_rev_str_date PA_PLSQL_DATATYPES.DateTabTyp;
18 tab_budget_rev_end_date PA_PLSQL_DATATYPES.DateTabTyp;
19 tab_forecast_cost_version_id PA_PLSQL_DATATYPES.IdTabTyp;
20 tab_forecast_cost_str_date PA_PLSQL_DATATYPES.DateTabTyp;
21 tab_forecast_cost_end_date PA_PLSQL_DATATYPES.DateTabTyp;
22 tab_forecast_rev_version_id PA_PLSQL_DATATYPES.IdTabTyp;
23 tab_forecast_rev_str_date PA_PLSQL_DATATYPES.DateTabTyp;
24 tab_forecast_rev_end_date PA_PLSQL_DATATYPES.DateTabTyp;
25 l_plan_version_id number;
26 /* Modified for bug 9557889 */
27
28 ins_version_id PA_PLSQL_DATATYPES.IdTabTyp;
29 ins_name PA_PLSQL_DATATYPES.Char30TabTyp;
30 ins_period_id PA_PLSQL_DATATYPES.NumTabTyp;
31 ins_start_date PA_PLSQL_DATATYPES.DateTabTyp;
32 ins_end_date PA_PLSQL_DATATYPES.DateTabTyp;
33 ins_cst_rev_flag PA_PLSQL_DATATYPES.Char1TabTyp;
34 ins_budget_forecast_flag PA_PLSQL_DATATYPES.Char1TabTyp;
35
36 ins_pltab_counter number :=0;
37
38 /* Bug 4118904
39 /* This will denote the index that we can use for carry forward */
40 l_last_bud_cost_index Number;
41 l_last_bud_rev_index Number;
42 l_last_for_cost_index Number;
43 l_last_for_rev_index Number;
44
45 /* this denotes if we found a plan for the current period */
46 l_found Boolean;
47
48 /* Counter to track the number of versions looped thru */
49 l_bud_cst_ver_index Number;
50 l_bud_rev_ver_index Number;
51 l_for_cst_ver_index Number;
52 l_for_rev_ver_index Number;
53
54 /* This indicates if we have already processed the latest baselined version */
55 l_latest_bud_cst_ver Boolean;
56 l_latest_bud_rev_ver Boolean;
57 l_latest_for_cst_ver Boolean;
58 l_latest_for_rev_ver Boolean;
59 l_latest_all_ver Boolean;
60
61 l_version_type VARCHAR2(30); --Bug fix 8593605
62
63 CURSOR c_get_dates is
64 SELECT name,
65 ent_period_id period_id,
66 start_date ,
67 end_date
68 FROM pji_time_ent_period_v
69 where p_table_identifier = 'PJI_TIME_ENT_PERIOD_V'
70 UNION ALL
71 SELECT name,
72 ent_qtr_id period_id,
73 start_date,
74 end_date
75 FROM pji_time_ent_qtr_v
76 where p_table_identifier = 'PJI_TIME_ENT_QTR_V'
77 UNION ALL
78 SELECT name,
79 ent_year_id period_id,
80 start_date,
81 end_date
82 FROM pji_time_ent_year_v
83 where p_table_identifier = 'PJI_TIME_ENT_YEAR_V'
84 UNION ALL
85 SELECT name,
86 cal_period_id period_id,
87 start_date,
88 end_date
89 FROM pji_time_cal_period_v
90 where p_table_identifier = 'PJI_TIME_CAL_PERIOD_V'
91 and CALENDAR_ID = p_calendar_id
92 UNION ALL
93 SELECT name,
94 cal_qtr_id period_id,
95 start_date,
96 end_date
97 FROM pji_time_cal_qtr_v
98 where p_table_identifier = 'PJI_TIME_CAL_QTR_V'
99 and CALENDAR_ID = p_calendar_id
100 UNION ALL
101 SELECT name,
102 cal_year_id period_id,
103 start_date,
104 end_date
105 FROM pji_time_cal_year_v
106 where p_table_identifier = 'PJI_TIME_CAL_YEAR_V'
107 and CALENDAR_ID = p_calendar_id;
108
109
110 /* Here order by clause with baselined_date is important because
111 for a period there can be more than one baselined version, to
112 take the closest to the period end date this logic is used*/
113
114 /* BELOW CURSOR WILL BE REPLACED BY FIN PLAN QUERY OR PROCEDURE*/
115
116 /* Modified cursor for bug 9557889 */
117 Cursor all_budget_ver is
118 select bv.budget_version_id, Min(bl.start_date) start_date, Max(bl.end_date) end_date
119 from pa_budget_versions bv, pa_budget_lines bl
120 where project_id=p_project_id
121 and fin_plan_type_id=l_plan_version_id
122 AND bv.budget_version_id = bl.budget_version_id
123 and budget_status_code='B'
124 and (version_type = l_version_type OR version_type = 'ALL')
125 GROUP BY bv.budget_version_id;
126
127 begin
128
129 /* Fetching for cost budget in plsql table */
130 l_plan_version_id := p_budget_cost_plan_id;
131 l_version_type := 'COST'; --Bug fix 8593605
132 Open all_budget_ver;
133 Fetch all_budget_ver BULK COLLECT into tab_budget_cost_version_id,
134 tab_budget_cost_str_date, tab_budget_cost_end_date; /* Modified for bug 9557889 */
135 close all_budget_ver;
136
137 /* Fetching for rev budget in plsql table */
138 l_plan_version_id := p_budget_rev_plan_id;
139 l_version_type := 'REVENUE'; --Bug fix 8593605
140 Open all_budget_ver;
141 Fetch all_budget_ver BULK COLLECT into tab_budget_rev_version_id,
142 tab_budget_rev_str_date,tab_budget_rev_end_date; /* Modified for bug 9557889 */
143 close all_budget_ver;
144
145 /* Fetching for cost forecast in plsql table */
146 l_plan_version_id := p_forecast_cost_plan_id;
147 l_version_type := 'COST'; --Bug fix 8593605
148 Open all_budget_ver;
149 Fetch all_budget_ver BULK COLLECT into tab_forecast_cost_version_id,
150 tab_forecast_cost_str_date,tab_forecast_cost_end_date; /* Modified for bug 9557889 */
151 close all_budget_ver;
152
153 /* Fetching for rev forecast in plsql table */
154 l_plan_version_id := p_forecast_rev_plan_id;
155 l_version_type := 'REVENUE'; --Bug fix 8593605
156 Open all_budget_ver;
157 Fetch all_budget_ver BULK COLLECT into tab_forecast_rev_version_id,
158 tab_forecast_rev_str_date,tab_forecast_rev_end_date; /* Modified for bug 9557889 */
159 close all_budget_ver;
160
161 l_last_bud_cost_index := 0;
162 l_last_bud_rev_index := 0;
163 l_last_for_cost_index := 0;
164 l_last_for_rev_index := 0;
165
166 /*
167 These are flags that indicate if we have already processed the latest baselined plan version.
168 if all the latest baselined versions have been processed we will not carry forward any further.
169 */
170 l_latest_bud_cst_ver := false;
171 l_latest_bud_rev_ver := false;
172 l_latest_for_cst_ver := false;
173 l_latest_for_rev_ver := false;
174 l_latest_all_ver := false;
175
176 FOR s_period_fetch in c_get_dates
177 Loop
178 l_found := false;
179 l_bud_cst_ver_index := 0;
180 l_bud_rev_ver_index := 0;
181 l_for_cst_ver_index := 0;
182 l_for_rev_ver_index := 0;
183
184 for i in 1..tab_budget_cost_version_id.count
185 loop
186 /* Checks whether baselined date is between start and end date, if yes then
187 inserts a record and then exist out of this loop*/
188 l_bud_cst_ver_index := l_bud_cst_ver_index + 1;
189 if ((tab_budget_cost_end_date(i) >= s_period_fetch.end_date) AND
190 (tab_budget_cost_str_date(i) <= s_period_fetch.start_date)) then /* Modified for bug 9557889 */
191 ins_pltab_counter := ins_pltab_counter + 1;
192
193 ins_version_id(ins_pltab_counter) := tab_budget_cost_version_id(i);
194 ins_name(ins_pltab_counter) := s_period_fetch.name;
195 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
196 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
197 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
198 ins_cst_rev_flag(ins_pltab_counter) := 'C';
199 ins_budget_forecast_flag(ins_pltab_counter) := 'B';
200
201 l_last_bud_cost_index := ins_pltab_counter;
202 l_found := true;
203
204 if(l_bud_cst_ver_index = 1) then
205 l_latest_bud_cst_ver := true;
206 end if;
207 exit;
208 end if;
209 end loop;
210
211 /*
212 Bug 4185866. It is possible that no plan versions exist for this type. In this case,
213 ignore this for carry forward.
214 */
215 if(l_bud_cst_ver_index = 0) then
216 l_latest_bud_cst_ver := true;
217 end if;
218
219 /*
220 Bug 4118904. Copy the previous period amount to the current period if no
221 baselined version was found for the current period. Please note that this
222 version's baseline date doesnot actually lie between the period start and end
223 dates. This should be done only till we have hit the latest baselined version
224 of all four types.
225 */
226 if(l_found = false and l_last_bud_cost_index <> 0 and l_latest_all_ver = false) then
227 ins_pltab_counter := ins_pltab_counter + 1;
228
229 ins_version_id(ins_pltab_counter) := ins_version_id(l_last_bud_cost_index);
230 ins_name(ins_pltab_counter) := s_period_fetch.name;
231 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
232 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
233 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
234 ins_cst_rev_flag(ins_pltab_counter) := 'C';
235 ins_budget_forecast_flag(ins_pltab_counter) := 'B';
236 end if;
237
238 l_found := false;
239 for i in 1..tab_budget_rev_version_id.count
240 loop
241 l_bud_rev_ver_index := l_bud_rev_ver_index + 1;
242 if ((tab_budget_rev_end_date(i) >= s_period_fetch.end_date) AND
243 (tab_budget_rev_str_date(i) <= s_period_fetch.start_date))then /* Modified for bug 9557889 */
244 ins_pltab_counter := ins_pltab_counter + 1;
245
246 ins_version_id(ins_pltab_counter) := tab_budget_rev_version_id(i);
247 ins_name(ins_pltab_counter) := s_period_fetch.name;
248 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
249 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
250 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
251 ins_cst_rev_flag(ins_pltab_counter) := 'R';
252 ins_budget_forecast_flag(ins_pltab_counter) := 'B';
253
254 l_last_bud_rev_index := ins_pltab_counter;
255 l_found := true;
256
257 if(l_bud_rev_ver_index = 1) then
258 l_latest_bud_rev_ver := true;
259 end if;
260 exit;
261 end if;
262 end loop;
263
264 /*
265 Bug 4185866. It is possible that no plan versions exist for this type. In this case,
266 ignore this for carry forward.
267 */
268 if(l_bud_rev_ver_index = 0) then
269 l_latest_bud_rev_ver := true;
270 end if;
271
272 /*
273 Bug 4118904. Copy the previous period amount to the current period if no
274 baselined version was found for the current period. Please note that this
275 version's baseline date doesnot actually lie between the period start and end
276 dates.
277 */
278 if(l_found = false and l_last_bud_rev_index <> 0 and l_latest_all_ver = false) then
279 ins_pltab_counter := ins_pltab_counter + 1;
280
281 ins_version_id(ins_pltab_counter) := ins_version_id(l_last_bud_rev_index);
282 ins_name(ins_pltab_counter) := s_period_fetch.name;
283 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
284 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
285 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
286 ins_cst_rev_flag(ins_pltab_counter) := 'R';
287 ins_budget_forecast_flag(ins_pltab_counter) := 'B';
288 end if;
289
290 l_found := false;
291 for i in 1..tab_forecast_cost_version_id.count
292 loop
293 l_for_cst_ver_index := l_for_cst_ver_index + 1;
294 if ((tab_forecast_cost_end_date(i) >= s_period_fetch.end_date) AND
295 (tab_forecast_cost_str_date(i) <= s_period_fetch.start_date))then /* Modified for bug 9557889 */
296 ins_pltab_counter := ins_pltab_counter + 1;
297
298 ins_version_id(ins_pltab_counter) := tab_forecast_cost_version_id(i);
299 ins_name(ins_pltab_counter) := s_period_fetch.name;
300 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
301 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
302 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
303 ins_cst_rev_flag(ins_pltab_counter) := 'C';
304 ins_budget_forecast_flag(ins_pltab_counter) := 'F';
305
306 l_last_for_cost_index := ins_pltab_counter;
307 l_found := true;
308
309 if(l_for_cst_ver_index = 1) then
310 l_latest_for_cst_ver := true;
311 end if;
312 exit;
313 end if;
314 end loop;
315
316 /*
317 Bug 4185866. It is possible that no plan versions exist for this type. In this case,
318 ignore this for carry forward.
319 */
320 if(l_for_cst_ver_index = 0) then
321 l_latest_for_cst_ver := true;
322 end if;
323
324 if(l_found = false and l_last_for_cost_index <> 0 and l_latest_all_ver=false) then
325 ins_pltab_counter := ins_pltab_counter + 1;
326
327 ins_version_id(ins_pltab_counter) := ins_version_id(l_last_for_cost_index);
328 ins_name(ins_pltab_counter) := s_period_fetch.name;
329 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
330 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
331 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
332 ins_cst_rev_flag(ins_pltab_counter) := 'C';
333 ins_budget_forecast_flag(ins_pltab_counter) := 'F';
334 end if;
335
336 l_found := false;
337 for i in 1..tab_forecast_rev_version_id.count
338 loop
339 l_for_rev_ver_index := l_for_rev_ver_index + 1;
340 if ((tab_forecast_rev_end_date(i) >= s_period_fetch.end_date) AND
341 (tab_forecast_rev_str_date(i) <= s_period_fetch.start_date)) then /* Modified for bug 9557889 */
342 ins_pltab_counter := ins_pltab_counter + 1;
343
344 ins_version_id(ins_pltab_counter) := tab_forecast_rev_version_id(i);
345 ins_name(ins_pltab_counter) := s_period_fetch.name;
346 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
347 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
348 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
349 ins_cst_rev_flag(ins_pltab_counter) := 'R';
350 ins_budget_forecast_flag(ins_pltab_counter) := 'F';
351
352 l_last_for_rev_index := ins_pltab_counter;
353 l_found := true;
354
355 if(l_for_rev_ver_index = 1) then
356 l_latest_for_rev_ver := true;
357 end if;
358 exit;
359 end if;
360 end loop;
361
362 /*
363 Bug 4185866. It is possible that no plan versions exist for this type. In this case,
364 ignore this for carry forward.
365 */
366 if(l_for_rev_ver_index = 0) then
367 l_latest_for_rev_ver := true;
368 end if;
369
370 if(l_found = false and l_last_for_rev_index <> 0 and l_latest_all_ver=false) then
371 ins_pltab_counter := ins_pltab_counter + 1;
372
373 ins_version_id(ins_pltab_counter) := ins_version_id(l_last_for_rev_index);
374 ins_name(ins_pltab_counter) := s_period_fetch.name;
375 ins_period_id(ins_pltab_counter) := s_period_fetch.period_id;
376 ins_start_date(ins_pltab_counter) := s_period_fetch.start_date;
377 ins_end_date(ins_pltab_counter) := s_period_fetch.end_date;
378 ins_cst_rev_flag(ins_pltab_counter) := 'R';
379 ins_budget_forecast_flag(ins_pltab_counter) := 'F';
380 end if;
381
382 /* see if the latest baselined versions have been processed for all four cases.*/
383 if(l_latest_all_ver = false and
384 l_latest_bud_cst_ver = true and
385 l_latest_bud_rev_ver = true and
386 l_latest_for_cst_ver = true and
387 l_latest_for_rev_ver = true
388 )
389 then
390 l_latest_all_ver := true;
391 end if;
392
393 end loop;/* End of s_period_fetch in c_get_dates */
394
395 Bud_period_version_ins( p_ins_version_id => ins_version_id,
396 p_ins_name => ins_name,
397 p_ins_period_id => ins_period_id,
398 p_ins_start_date => ins_start_date,
399 p_ins_end_date => ins_end_date,
400 p_ins_cst_rev_flag => ins_cst_rev_flag,
401 p_ins_budget_forecast_flag => ins_budget_forecast_flag);
402
403 end get_bgt_ver_period;
404
405 procedure Bud_period_version_ins (p_ins_version_id PA_PLSQL_DATATYPES.IdTabTyp,
406 p_ins_name PA_PLSQL_DATATYPES.Char30TabTyp,
407 p_ins_period_id PA_PLSQL_DATATYPES.NumTabTyp,
408 p_ins_start_date PA_PLSQL_DATATYPES.DateTabTyp,
409 p_ins_end_date PA_PLSQL_DATATYPES.DateTabTyp,
410 p_ins_cst_rev_flag PA_PLSQL_DATATYPES.Char1TabTyp,
411 p_ins_budget_forecast_flag PA_PLSQL_DATATYPES.Char1TabTyp) is
412 begin
413
414 delete from pji_period_budget_ver_tmp;
415 FORALL j IN 1..p_ins_version_id.count
416 insert into pji_period_budget_ver_tmp(
417 name,
418 period_id,
419 budget_version_id,
420 start_date,
421 end_date,
422 cst_rev_flag,
423 bud_for_act_flag)
424 values (p_ins_name(j),
425 p_ins_period_id(j),
426 p_ins_version_id(j),
427 p_ins_start_date(j),
428 p_ins_end_date(j),
429 p_ins_cst_rev_flag(j),
430 p_ins_budget_forecast_flag(j));
431 End Bud_period_version_ins;
432
433 End PJI_COMPLETION_GRAPH;
[Home] [Help]