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