[Home] [Help]
PACKAGE BODY: APPS.PA_DATE_RANGE_PKG
Source
1 package body PA_DATE_RANGE_PKG AS
2 /* $Header: PADTRNGB.pls 120.2 2006/01/19 03:27:23 nkumbi noship $ */
3
4 procedure DATE_RANGE_UPGRD(
5 P_BUDGET_VERSIONS IN SYSTEM.PA_NUM_TBL_TYPE,
6 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
7 X_MSG_COUNT OUT NOCOPY NUMBER,
8 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
9
10 --Bug 4185180.Given a budget version id as parameter the cursor should bring the PLAN_VERSION, PLAN_TYPE level records for that
11 -- project and budget version.
12 cursor get_elig_bud_ver_csr(c_project_id pa_budget_versions.project_id%TYPE,
13 c_budget_ver_id pa_budget_versions.budget_version_id%type) is
14 select fp.fin_plan_version_id budget_version_id
15 , fp.project_id
16 , fp.proj_fp_options_id
17 , nvl(pa.org_id,-99) org_id
18 , fp.fin_plan_type_id /* bug 3804286: added fin_plan_type_id */
19 , fp.fin_plan_preference_code /* bug 3804286: added fin_plan_preference_code */
20 , pa.start_date /* bug 3804286: added start_date */
21 , fp.fin_plan_option_level_code
22 --Bug 4046524
23 , pa.project_currency_code
24 , pa.projfunc_currency_code
25 from pa_proj_fp_options fp, pa_projects_all pa,
26 pa_budget_versions pbv
27 where fp.project_id = pa.project_id
28 and fp.project_id = c_project_id
29 and pbv.budget_version_id=c_budget_ver_id
30 and (fp.fin_plan_version_id = c_budget_ver_id OR
31 (fin_plan_option_level_code <> 'PLAN_VERSION' AND nvl(fp.fin_plan_type_id,-99)=nvl(pbv.fin_plan_type_id,-99)) )
32 and decode(fp.fin_plan_preference_code,
33 'COST_ONLY',cost_time_phased_code,
34 'REVENUE_ONLY',revenue_time_phased_code,
35 'COST_AND_REV_SAME',all_time_phased_code,
36 'COST_AND_REV_SEP',decode(cost_time_phased_code,
37 'R',cost_time_phased_code,
38 revenue_time_phased_code)) = 'R';
39
40 cursor chk_plan_ver_csr(c_budget_ver_id pa_budget_versions.budget_version_id%type) is
41 select project_id,'Y' from pa_proj_fp_options fp
42 where fin_plan_version_id = c_budget_ver_id and
43 fin_plan_option_level_code like 'PLAN_VERSION';
44
45 --Bug 4919018. SQL Repository Performance Activity
46 cursor ftch_period_details_csr(c_org_id pa_projects_all.org_id%type) is
47 select pi.pa_period_type,sob.accounted_period_type, sob.period_set_name from
48 pa_implementations_all pi,
49 gl_sets_of_books sob
50 where nvl(pi.org_id,-99) = c_org_id
51 and sob.set_of_books_id = pi.set_of_books_id;
52
53 --Bug 4176129. Removed the UNION clause as at any point only one of the 2 SQLs would get executed
54 cursor chk_ra_exists_csr(c_budget_version_id pa_budget_versions.budget_version_id%type,
55 c_period_type gl_date_period_map.period_type%type,
56 c_period_set_name gl_sets_of_books.period_set_name%type) is
57 select 'Y' from dual
58 where exists (select 'Y' from pa_resource_assignments ra
59 where budget_version_id = c_budget_version_id
60 and ra.planning_start_date is NOT NULL /* bug 3673111 */
61 and ra.planning_end_date is NOT NULL /* bug 3673111 */
62 and not exists
63 ((
64 select 'Y' from
65 gl_date_period_map g
66 where trunc(g.accounting_date) between ra.planning_start_date and ra.planning_end_date
67 and g.period_set_name = c_period_set_name
68 and g.period_type = c_period_type
69 )));
70
71
72
73 cursor chk_ra_for_bl_exists_csr(c_budget_version_id pa_budget_versions.budget_version_id%type) is
74 select 'Y' from dual
75 where exists (select 'Y' from pa_budget_lines bl
76 where bl.budget_version_id = c_budget_version_id
77 group by resource_assignment_id
78 having count(*) > 1);
79
80 --Bug 4176129
81 cursor get_per_type_csr(c_org_id pa_projects_all.org_id%type,
82 c_period_set_name gl_sets_of_books.period_set_name%type) is
83 select pi.pa_period_type,sob.accounted_period_type
84 from pa_implementations_all pi,
85 gl_sets_of_books sob
86 where nvl(pi.org_id,-99) = c_org_id
87 and sob.set_of_books_id = pi.set_of_books_id
88 and exists
89 (select 1
90 from gl_date_period_map g
91 where g.period_set_name=c_period_set_name);
92
93
94
95 -- Begin Bug 3890562, 17-SEP-2004, jwhite ---------------------------------
96 -- Make query similar to get_budget_lines_csr, which explodes date-range into PA/GL periodic data.
97
98
99 cursor chk_pa_gl_per_exists_csr(c_period_type pa_implementations_all.pa_period_type%type
100 ,c_budget_version_id pa_budget_versions.budget_version_id%type
101 ,c_period_set_name gl_sets_of_books.period_set_name%type) is
102 select 'Y' from dual
103 where exists ( select 'Y'
104 from pa_budget_lines bl
105 ,gl_periods gl
106 where bl.budget_version_id = c_budget_version_id
107 and gl.period_type = c_period_type
108 and gl.period_set_name = c_period_set_name
109 and gl.ADJUSTMENT_PERIOD_FLAG = 'N'
110 and (bl.start_date between gl.start_date and gl.end_date
111 or bl.end_date between gl.start_date and gl.end_date
112 or (gl.start_date > bl.start_date and gl.end_date < bl.end_date)));
113
114 --Bug 3988010. Removed the NVL so as not to upgrade NULL to 0
115
116 cursor get_non_time_multi_csr(c_budget_version_id pa_budget_versions.budget_version_id%type) is
117 select min(start_date) min_date ,max(end_date) max_date,
118 sum(quantity) sum_quantity,
119 sum(raw_cost) sum_raw_cost,
120 sum(burdened_cost) sum_burdened_cost,
121 sum(revenue) sum_revenue,
122 sum(project_raw_cost) sum_project_raw_cost,
123 sum(project_burdened_cost) sum_project_burdened_cost,
124 sum(project_revenue) sum_project_revenue,
125 sum(txn_raw_cost) sum_txn_raw_cost,
126 sum(txn_burdened_cost) sum_txn_burdened_cost,
127 sum(txn_revenue) sum_txn_revenue,
128 resource_assignment_id,txn_currency_code
129 from pa_budget_lines
130 where budget_version_id = c_budget_version_id
131 group by resource_assignment_id, txn_currency_code ;
132
133 cursor get_res_assign_id_csr(c_budget_version_id pa_budget_lines.budget_version_id%type) is
134 select resource_assignment_id,planning_start_date from pa_resource_assignments
135 where budget_version_id = c_budget_version_id;
136
137 -- bug 3673111, 14-JUL-04, jwhite --------------------------------------------------------
138 -- Added the following to select and group-by: gl.start_date,gl.end_date, gl.PERIOD_NAME
139 -- Rearragned group-by to group primary by period and then by txn currency code
140 -- Removed the following from select and group-by: bl.start_date,bl.end_date
141 -- Reversed the IN-parameter dates for spread_amount function call.
142
143 -- Bug 3807889, 04-AUG-04, jwhite
144 -- Added filter to EXCLUDE adjustment periods.
145 --
146
147 -- Bug 3988010. Removed the Calls to pa_misc.spread_amount function as that function
148 -- rounds the amounts. The amounts will be upgraded without applying the round function.
149
150 -- Bug 4215637. Used ratio_to_report Function to address the fractions of the date range falling into
151 -- individual periods not summing upto 1
152
153 -- Bug 4299635. The amounts are rounded to atmost 5 digits. This is done to make sure that the amounts
154 -- for date range budgets are correctly upgraded to periodic budgets. PC/PFC amounts need not be rounded
155 -- since they will re-derived by MC api which is called in PAFPUPGB.pls
156 cursor get_budget_lines_csr(l_budget_version_id pa_budget_lines.budget_version_id%type,l_res_assign_id pa_resource_assignments.resource_assignment_id%type
157 ,l_org_id pa_projects_all.org_id%type,l_per_type pa_implementations_all.pa_period_type%type,l_period_set_name gl_sets_of_books.period_set_name%type) is
158 select
159 rs.resource_assignment_id resource_assignment_id,
160 rs.txn_currency_code txn_currency_code,
161 rs.gl_start_date gl_start_date,
162 rs.gl_end_date gl_end_date,
163 rs.PERIOD_NAME period_name,
164 rs.rate_based_flag rate_based_flag,
165 round(sum(rs.spr_quantity * factor),5) spr_quantity,
166 sum(rs.spr_raw_cost * factor) spr_raw_cost,
167 sum(rs.spr_burdened_cost * factor) spr_burdened_cost,
168 sum(rs.spr_revenue * factor) spr_revenue,
169 sum(rs.spr_project_raw_cost * factor) spr_project_raw_cost,
170 sum(rs.spr_project_burdened_cost * factor) spr_project_burdened_cost,
171 sum(rs.spr_project_revenue * factor) spr_project_revenue,
172 round(sum(rs.spr_txn_raw_cost * factor),5) spr_txn_raw_cost,
173 round(sum(rs.spr_txn_burdened_cost * factor),5) spr_txn_burdened_cost,
174 round(sum(rs.spr_txn_revenue * factor),5) spr_txn_revenue,
175 --Bug 4299635. The below columns will have the total amounts for the budget line accumulated into the first
176 --PA/GL period into which the budget line falls. These amounts will be used later in comparing the actual
177 --amounts that should get upgraded and the amounts that got upgraded
178 sum(rs.spr_quantity * tot_amt_factor) total_qty,
179 sum(rs.spr_txn_raw_cost * tot_amt_factor) total_txn_raw_cost,
180 sum(rs.spr_txn_burdened_cost * tot_amt_factor) total_txn_burd_cost,
181 sum(rs.spr_txn_revenue * tot_amt_factor) total_txn_revenue
182 from(
183 select
184 bl.resource_assignment_id resource_assignment_id,
185 ra.rate_based_flag rate_based_flag,
186 bl.txn_currency_code txn_currency_code,
187 gl.start_date gl_start_date,
188 gl.end_date gl_end_date,
189 gl.PERIOD_NAME period_name,
190 bl.quantity spr_quantity,
191 bl.raw_cost spr_raw_cost,
192 bl.burdened_cost spr_burdened_cost,
193 bl.revenue spr_revenue,
194 bl.project_raw_cost spr_project_raw_cost,
195 bl.project_burdened_cost spr_project_burdened_cost,
196 bl.project_revenue spr_project_revenue,
197 bl.txn_raw_cost spr_txn_raw_cost,
198 bl.txn_burdened_cost spr_txn_burdened_cost,
199 bl.txn_revenue spr_txn_revenue,
200 ratio_to_report((decode(least(bl.start_date, gl.start_date),
201 bl.start_date,decode(least(bl.end_date,gl.end_date),
202 gl.end_date,gl.end_date-gl.start_date+1,
203 bl.end_date,bl.end_date-gl.start_date+1),
204 gl.start_date,decode(least(bl.end_date,gl.end_date),
205 gl.end_date,gl.end_date-bl.start_date+1,
206 bl.end_date,bl.end_date-bl.start_date+1))
207 )) OVER (PARTITION BY bl.budget_line_id) factor,
208 --Bug 4299635. This factor will be used to derive the total amount that should get upgraded
209 --for a planning txn
210 DECODE(least(bl.start_date, gl.start_date),
211 gl.start_date,1,
212 0) tot_amt_factor
213 from pa_budget_lines bl,gl_periods gl, pa_resource_assignments ra
214 where bl.budget_version_id = l_budget_version_id
215 and ra.resource_assignment_id = l_res_assign_id
216 and bl.resource_assignment_id = l_res_assign_id
217 and gl.period_type = l_per_type
218 and gl.period_set_name = l_period_set_name
219 and gl.ADJUSTMENT_PERIOD_FLAG = 'N' /* Bug 3807889: Added this filter */
220 and (bl.start_date between gl.start_date and gl.end_date
221 or bl.end_date between gl.start_date and gl.end_date
222 or (gl.start_date > bl.start_date and gl.end_date < bl.end_date)))rs
223 group by resource_assignment_id, gl_start_date, PERIOD_NAME, txn_currency_code, gl_end_date,rate_based_flag;
224
225
226 -- End bug 3673111, 14-JUL-04, jwhite --------------------------------------------------------
227
228
229 -- Bug 3804286, 12-AUG-03, jwhite ---------------------------------------------------------
230
231 cursor get_period_mask_id_csr(c_time_phased_code pa_period_masks_b.time_phase_code%TYPE)
232 is
233 select period_mask_id
234 from pa_period_masks_b
235 where pre_defined_flag='Y'
236 and time_phase_code = c_time_phased_code;
237
238 -- End Bug 3804286, 12-AUG-03, jwhite ------------------------------------------------------
239
240
241 TYPE budget_lines_tbl is table of get_budget_lines_csr%ROWTYPE
242 index by binary_integer;
243 l_get_budget_lines_tbl budget_lines_tbl;
244
245 l_project_id pa_budget_versions.project_id%TYPE;
246 l_one_ra_exists varchar2(1) := 'N';
247 l_one_ra_for_bl_exists varchar2(1) := 'N';
248 l_pa_per_exists varchar2(1) := 'N';
249 l_gl_per_exists varchar2(1) := 'N';
250 l_budget_version_id pa_budget_versions.budget_version_id%type;
251 l_time_phased_mode varchar2(1) := null;
252 l_pa_period_type pa_implementations_all.pa_period_type%type;
253 l_per_type pa_implementations_all.pa_period_type%type;
254 l_accounted_per_type pa_implementations_all.pa_period_type%type;
255 l_min_date date;
256 g_upgrade_mode varchar2(100);
257 l_counter number := 0;
258 l_attribute_category pa_budget_lines.attribute_category%type;
259 l_attribute1 pa_budget_lines.attribute1%type;
260 l_attribute2 pa_budget_lines.attribute2 %type;
261 l_attribute3 pa_budget_lines.attribute3%type;
262 l_attribute4 pa_budget_lines.attribute4%type;
263 l_attribute5 pa_budget_lines.attribute5%type;
264 l_attribute6 pa_budget_lines.attribute6%type;
265 l_attribute7 pa_budget_lines.attribute7%type;
266 l_attribute8 pa_budget_lines.attribute8%type;
267 l_attribute9 pa_budget_lines.attribute9%type;
268 l_attribute10 pa_budget_lines.attribute10%type;
269 l_attribute11 pa_budget_lines.attribute11%type;
270 l_attribute12 pa_budget_lines.attribute12 %type;
271 l_attribute13 pa_budget_lines.attribute13%type;
272 l_attribute14 pa_budget_lines.attribute14%type;
273 l_attribute15 pa_budget_lines.attribute15%type ;
274 l_plan_ver_exists varchar2(1) := 'Y';
275 l_debug_mode varchar2(30);
276 l_module_name VARCHAR2(100):= 'pa.plsql.PA_DATE_RANGE_PKG';
277 l_msg_index_out NUMBER;
278 l_data VARCHAR2(2000);
279 l_msg_data VARCHAR2(2000);
280 l_msg_count number;
281
282
283
284 -- Bug 3804286, 12-AUG-04, jwhite -----------------------------------------------
285
286 l_project_start_date pa_projects_all.start_date%TYPE := NULL;
287 l_org_id pa_projects_all.org_id%TYPE := NULL;
288
289 l_period_mask_id pa_period_masks_b.period_mask_id%type;
290 l_curr_plan_period pa_budget_versions.current_planning_period%type;
291 l_cost_current_planning_period pa_proj_fp_options.cost_current_planning_period%type;
292 l_cost_period_mask_id pa_proj_fp_options.cost_period_mask_id%type;
293 l_rev_current_planning_period pa_proj_fp_options.rev_current_planning_period%type;
294 l_rev_period_mask_id pa_proj_fp_options.rev_period_mask_id%type;
295 l_all_current_planning_period pa_proj_fp_options.all_current_planning_period%type;
296 l_all_period_mask_id pa_proj_fp_options.all_period_mask_id%type;
297
298 -- End Bug 3804286, 12-AUG-04, jwhite --------------------------------------------
299
300 --Bug 4299635. These tbls given below will use txn currency code as index.
301 TYPE varchar2_indexed_num_tbl_type IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
302 TYPE varchar2_indexed_date_tbl_type IS TABLE OF DATE INDEX BY VARCHAR2(15);
303
304 l_plan_txn_post_upg_qty_tbl varchar2_indexed_num_tbl_type;
305 l_plan_txn_act_qty_tbl varchar2_indexed_num_tbl_type;
306 l_plan_txn_post_upg_rc_tbl varchar2_indexed_num_tbl_type;
307 l_plan_txn_act_rc_tbl varchar2_indexed_num_tbl_type;
308 l_plan_txn_post_upg_bc_tbl varchar2_indexed_num_tbl_type;
309 l_plan_txn_act_bc_tbl varchar2_indexed_num_tbl_type;
310 l_plan_txn_post_upg_rev_tbl varchar2_indexed_num_tbl_type;
311 l_plan_txn_act_rev_tbl varchar2_indexed_num_tbl_type;
312 l_last_bl_indx_in_plan_txn_tbl varchar2_indexed_num_tbl_type;
313 l_max_st_dt_in_plan_txn_tbl varchar2_indexed_date_tbl_type;
314 l_txn_curr_index pa_fp_txn_currencies.txn_currency_code%TYPE;
315 l_last_bl_index NUMBER;
316 --Bug 4919018
317 l_pi_pa_period_type pa_implementations_all.pa_period_type%type;
318 l_sob_accounted_period_type gl_sets_of_books.accounted_period_type%type;
319 l_sob_period_set_name gl_sets_of_books.period_set_name%type;
320
321
322
323
324 BEGIN
325
326
327
328 x_msg_count := 0;
329 x_msg_data := NULL;
330 x_return_status := FND_API.G_RET_STS_SUCCESS;
331
332 pa_debug.init_err_stack('PA_DATE_RANGE_PKG.DATE_RANGE_UPGRD');
333 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
334 l_debug_mode := NVL(l_debug_mode, 'Y');
335 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
336 IF l_debug_mode = 'Y' THEN
337 pa_debug.g_err_stage := 'Entered Date Range Upgrade';
338 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
339
340 pa_debug.g_err_stage := 'Checking for valid parameters';
341 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
342 END IF;
343
344
345
346 if (p_budget_versions.count <= 0 ) then
347 IF l_debug_mode = 'Y' THEN
348 pa_debug.g_err_stage := 'Budget Versions Not Passed as Input';
349 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
350 END IF;
351 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
352 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
353 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
354 end if;
355
356
357
358
359 for j in p_budget_versions.first .. p_budget_versions.last
360
361 loop
362
363 l_plan_ver_exists := 'N'; -- No rows found by chk_plan_ver_csr
364 l_budget_version_id := p_budget_versions(j);
365
366
367 open chk_plan_ver_csr(p_budget_versions(j));
368 fetch chk_plan_ver_csr into l_project_id, l_plan_ver_exists;
369 close chk_plan_ver_csr;
370
371
372
373 -- Bug 3673111, 07-JUN-04, jwhite ------------------------------------------
374
375
376
377 if ( nvl(l_plan_ver_exists, 'N') = 'N' ) then
378
379
380 --NOT Plan-Version: SKIP to the Next Budget Version
381 IF l_debug_mode = 'Y' THEN
382 pa_debug.g_err_stage := 'SKIPPED NON-Plan-Version Budget Version='||to_char(l_budget_version_id);
383 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
384 END IF;
385
386 GOTO skip_version; -- goto end of budget version loop
387
388 end if;
389
390 -- End Bug 3673111, 07-JUN-04, jwhite ----------------------------------------
391
392 -- Loop for each eligible Budget Version
393 for l_get_elig_bud_ver_csr in get_elig_bud_ver_csr(l_project_id,p_budget_versions(j))
394 loop
395
396 l_one_ra_exists := 'N';
397 l_one_ra_for_bl_exists := 'N';
398 l_pa_per_exists := 'N';
399 l_gl_per_exists := 'N';
400 l_pa_period_type := NULL;
401 l_accounted_per_type := NULL;
402 l_time_phased_mode := 'N';
403 l_per_type := NULL;
404 g_upgrade_mode := 'Non_Time_Phase';
405 -- Bug 3804286, 12-AUG-04, jwhite -----------------------------------------------
406
407 l_period_mask_id := NULL;
408 l_curr_plan_period := NULL;
409 l_cost_current_planning_period := NULL;
410 l_cost_period_mask_id := NULL;
411 l_rev_current_planning_period := NULL;
412 l_rev_period_mask_id := NULL;
413 l_all_current_planning_period := NULL;
414 l_all_period_mask_id := NULL;
415
416 l_project_start_date := NULL;
417 l_org_id := NULL;
418
419 select trunc(sysdate) into l_min_date from dual;
420
421 --Bug 4919018
422 open ftch_period_details_csr(l_get_elig_bud_ver_csr.org_id);
423 fetch ftch_period_details_csr into l_pi_pa_period_type, l_sob_accounted_period_type, l_sob_period_set_name;
424 close ftch_period_details_csr;
425
426 -- End Bug 3804286, 12-AUG-04, jwhite -----------------------------------------------
427
428
429 /* project/plan type level records would have version id as null and hence
430 l_one_ra_for_bl_exists would be N */
431
432 -- Check ifbudget line exists for RAId then upgrade_mode = non_time_phase
433 open chk_ra_for_bl_exists_csr(l_get_elig_bud_ver_csr.budget_version_id);
434 fetch chk_ra_for_bl_exists_csr into l_one_ra_for_bl_exists;
435 close chk_ra_for_bl_exists_csr;
436
437
438 -- Bug 3673111, 07-JUN-04, jwhite ------------------------------------------
439 -- if (l_one_ra_for_bl_exists = 'Y'), then multiple budget lines exist for a given resource assignment.
440 -- Multiple budget lines must have different start dates and, therefore, different date ranges.
441 -- Effectively, multiple budget lines per resource assignment implies periodic budget line defintion.
442 --
443 -- Therefore, this if/end if must test for "l_one_ra_for_bl_exists = 'Y'"
444 -- for PERIODIC processing.
445 --
446
447 /* From this point in code, the local variables would be N for project/plan type level records,
448 and this takes care of no extra processing happening wrto reading budget version related data.
449 Also, for this case (project/plan type level record), the time phased code local variable
450 initailization ensures that the time phased code is set to N. */
451
452 if (l_one_ra_for_bl_exists = 'Y') /* bug 3673111: Changed 'N' to 'Y' */
453 then
454
455
456 -- Check if PA Period exists
457 open get_per_type_csr(l_get_elig_bud_ver_csr.org_id, l_sob_period_set_name);
458 fetch get_per_type_csr into l_pa_period_type,l_accounted_per_type;
459 close get_per_type_csr;
460
461 --Bug 4046492.Call the function to derive the Time Phase to which the budget version should be upgraded
462 l_time_phased_mode := PA_DATE_RANGE_PKG.get_time_phase_mode(p_budget_version_id =>l_get_elig_bud_ver_csr.budget_version_id
463 ,p_pa_period_type =>l_pa_period_type
464 ,p_gl_period_type =>l_accounted_per_type
465 ,p_org_id =>l_get_elig_bud_ver_csr.org_id);
466 IF l_time_phased_mode='P' THEN
467
468 l_pa_per_exists:='Y';
469
470 ELSIF l_time_phased_mode='G' THEN
471
472 l_gl_per_exists:='Y';
473
474 END IF;
475
476 end if; -- l_one_ra_for_bl_exists = 'Y'
477
478 -- End Bug 3673111, 07-JUN-04, jwhite ------------------------------------------
479
480
481
482 -- Check if atleast one RA exists where there are no PA / GL Period defined between start , end date of the pa_res_assignment then upgrade_mode = non_time_phase
483 if (l_pa_per_exists = 'Y') or (l_gl_per_exists = 'Y') then
484
485 --Bug 4176129. The fact that either l_pa_per_exists or l_gl_per_exists is Y indicates that l_pa_period_type and
486 --l_accounted_per_type used below are not null and initialized in the above loop.
487 if (l_pa_per_exists ='Y') then
488 open chk_ra_exists_csr(l_get_elig_bud_ver_csr.budget_version_id,l_pa_period_type,l_sob_period_set_name);
489 fetch chk_ra_exists_csr into l_one_ra_exists;
490 close chk_ra_exists_csr;
491 else
492 open chk_ra_exists_csr(l_get_elig_bud_ver_csr.budget_version_id,l_accounted_per_type,l_sob_period_set_name);
493 fetch chk_ra_exists_csr into l_one_ra_exists;
494 close chk_ra_exists_csr;
495 end if;
496
497 end if;
498
499
500
501 -- Bug 3673111, 07-JUN-04, jwhite ------------------------------------------
502
503 -- if (l_one_ra_for_bl_exists = 'Y'), the multiple budget lines exist for a given resource assignment.
504 -- Multiple budget lines must have different start dates and, therefore, different date ranges.
505 -- Effectively, multiple budget lines per resource assignment implies periodic budget line defintion.
506 --
507 -- Therefore, this if/end if must test for "l_one_ra_for_bl_exists = 'N'" to identifY
508 -- NON-TIME-PHASE processing.
509 --
510
511
512
513
514 if ((l_one_ra_for_bl_exists = 'N') or (l_one_ra_exists = 'Y')) then /* Bug 3673111 */
515 g_upgrade_mode := 'Non_Time_Phase';
516 l_time_phased_mode := 'N';
517 elsif (l_pa_per_exists = 'Y') then
518 g_upgrade_mode := 'PA_Period_Upgrade';
519 l_time_phased_mode := 'P';
520 elsif (l_gl_per_exists = 'Y') then
521 g_upgrade_mode := 'GL_Period_Upgrade';
522 l_time_phased_mode := 'G';
523 else
524 g_upgrade_mode := 'Non_Time_Phase_Multi_Line';
525 l_time_phased_mode := 'N';
526 end if;
527
528 -- End Bug 3673111, 07-JUN-04, jwhite ------------------------------------------------------
529
530 IF l_debug_mode = 'Y' THEN
531 pa_debug.g_err_stage := 'Mode of the upgrade';
532 pa_debug.write(l_module_name,'Upgrade Mode is' || g_upgrade_mode,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
533 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
534 end if;
535
536
537 IF l_debug_mode = 'Y' THEN
538 pa_debug.g_err_stage := 'Updating pa_proj_fp_options table';
539 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
540 end if;
541
542 -- Bug 3804286, 12-AUG-03, jwhite -------------------------------------------------------------
543
544 -- Update the PLAN_VERSION Options Record with the Various Derived Values
545
546 -- For Periodic Budget Versions, Get the Start Date for
547 -- Subseqeunt Derivation of the Current Planning Period Name.
548
549
550 IF ( l_time_phased_mode IN ('G','P' ) )
551 THEN
552
553 l_project_start_date := l_get_elig_bud_ver_csr.start_date;
554 l_org_id := l_get_elig_bud_ver_csr.org_id;
555
556
557 -- If Project Start is Still NUll,
558 -- then Find Minimum Budget Line Start Date, If Any.
559 IF ( l_project_start_date IS NULL)
560 THEN
561
562 begin
563
564 SELECT min(start_date)
565 INTO l_project_start_date
566 FROM pa_budget_lines
567 WHERE budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
568
569 /* Following "if" takes care of case when there no budget lines for the budget verison
570 or
571 the record that is processed is a project/plan type level record. */
572
573 IF l_project_start_date IS NULL THEN
574 select trunc(sysdate) into l_project_start_date from dual;
575 END IF;
576
577 end;
578
579 END IF; -- l_project_start_date IS NULL)
580
581
582 -- IF Start Date FOUND,
583 -- THEN Derive GL/PA Period Name to Populate the Current Planning Period
584
585 IF ( l_project_start_date IS NULL)
586 THEN
587
588 l_curr_plan_period := NULL;
589
590 ELSE
591
592 IF (l_time_phased_mode = 'G')
593 THEN
594 -- Get GL Period Name
595
596 begin
597
598 SELECT gl.PERIOD_NAME
599 INTO l_curr_plan_period
600 FROM gl_periods gl
601 WHERE gl.period_type = l_accounted_per_type
602 and l_project_start_date between gl.START_DATE and gl.END_DATE
603 AND gl.period_set_name = l_sob_period_set_name
604 AND gl.ADJUSTMENT_PERIOD_FLAG = 'N';
605
606 exception
607 WHEN NO_DATA_FOUND THEN
608 l_curr_plan_period := NULL;
609
610 end;
611
612 -- Get GL Period Mask Id. This Must Exist.
613 Open get_period_mask_id_csr('G');
614 Fetch get_period_mask_id_csr INTO l_period_mask_id;
615 Close get_period_mask_id_csr;
616
617
618 End IF; -- GL Period Type
619
620 IF (l_time_phased_mode = 'P')
621 THEN
622 -- Get PA Period Name
623
624 begin
625
626 SELECT gl.PERIOD_NAME
627 INTO l_curr_plan_period
628 FROM gl_periods gl
629 WHERE gl.period_type = l_PA_period_type
630 and l_project_start_date between gl.START_DATE and gl.END_DATE
631 AND gl.period_set_name = l_sob_period_set_name
632 AND gl.ADJUSTMENT_PERIOD_FLAG = 'N';
633
634 exception
635 WHEN NO_DATA_FOUND THEN
636 l_curr_plan_period := NULL;
637
638 end;
639
640 -- Get PA Period Mask Id. This Must Exist.
641 Open get_period_mask_id_csr('P');
642 Fetch get_period_mask_id_csr INTO l_period_mask_id;
643 Close get_period_mask_id_csr;
644
645
646 End IF; -- PA Period Type
647
648 End if; --l_project_start_date IS NULL
649
650 Else
651 -- Catch All Conditon (l_time_phased_mode s/b 'N')
652
653 l_curr_plan_period := NULL;
654 l_period_mask_id := NULL;
655 End IF; -- l_time_phased_mode
656
657
658 IF (l_get_elig_bud_ver_csr.fin_plan_preference_code = 'COST_ONLY')
659 then
660
661 l_cost_current_planning_period := l_curr_plan_period;
662 l_cost_period_mask_id := l_period_mask_id;
663 elsif (l_get_elig_bud_ver_csr.fin_plan_preference_code = 'REVENUE_ONLY')
664 then
665
666 l_rev_current_planning_period := l_curr_plan_period;
667 l_rev_period_mask_id := l_period_mask_id;
668 elsif (l_get_elig_bud_ver_csr.fin_plan_preference_code = 'COST_AND_REV_SEP')
669 then
670
671 l_cost_current_planning_period := l_curr_plan_period;
672 l_cost_period_mask_id := l_period_mask_id;
673 l_rev_current_planning_period := l_curr_plan_period;
674 l_rev_period_mask_id := l_period_mask_id;
675 else
676
677 l_all_current_planning_period := l_curr_plan_period;
678 l_all_period_mask_id := l_period_mask_id;
679 End IF;
680
681 -- Update Values that Have Changed Because of this Date-Range Conversion
682 -- Time phased code is updated based on whether it is 'R' or not instead of using
683 -- fin plan pref code since the options selected would surely have either cost/rev/all or
684 -- both cost and rev (in case of cost-and-rev-sep pref code) as 'R'. 'COST AND REV SEP'
685 -- is not budget version level but for project/plan type level record of fp opt.
686
687 UPDATE pa_proj_fp_options
688 SET cost_time_phased_code = decode(cost_time_phased_code,'R',l_time_phased_mode,cost_time_phased_code), /* Bug 3792821 */
689 revenue_time_phased_code = decode(revenue_time_phased_code,'R',l_time_phased_mode,revenue_time_phased_code), /* Bug 3792821 */
690 all_time_phased_code = decode(all_time_phased_code,'R',l_time_phased_mode,all_time_phased_code), /* Bug 3792821 */
691 cost_current_planning_period = l_cost_current_planning_period,
692 cost_period_mask_id = l_cost_period_mask_id,
693 rev_current_planning_period = l_rev_current_planning_period,
694 rev_period_mask_id = l_rev_period_mask_id,
695 all_current_planning_period = l_all_current_planning_period ,
696 all_period_mask_id = l_all_period_mask_id
697 WHERE proj_fp_options_id = l_get_elig_bud_ver_csr.proj_fp_options_id;
698
699 /* Including this IF so that we can avoid an update (though it would do nothing) in case
700 of project/plan type level record */
701 IF l_get_elig_bud_ver_csr.fin_plan_option_level_code = 'PLAN_VERSION' THEN
702
703 update pa_budget_versions
704 SET current_planning_period = l_curr_plan_period,
705 period_mask_id = l_period_mask_id
706 where budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
707
708 END IF;
709
710 -- End Bug 3804286, 12-AUG-03, jwhite ---------------------------------------------------------
711 /* g_upgrade_mode would be 'Non_Time_Phase' for project/plan type level record and hence
712 none of the below processing would be done for them */
713
714 if g_upgrade_mode = 'Non_Time_Phase_Multi_Line' then
715 IF l_debug_mode = 'Y' THEN
716 pa_debug.g_err_stage := 'Entered Non Time Phase Multi Line';
717 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
718 end if;
719 -- Loop for each budget line for the given version
720 for l_get_non_time_multi_csr in get_non_time_multi_csr(l_get_elig_bud_ver_csr.budget_version_id)
721 loop
722 insert into pa_budget_lines_m_upg_dtrange
723 (
724 resource_assignment_id,
725 start_date,
726 last_update_date,
727 last_updated_by,
728 creation_date,
729 created_by ,
730 last_update_login,
731 end_date,
732 period_name,
733 quantity,
734 raw_cost,
735 burdened_cost ,
736 revenue ,
737 change_reason_code,
738 description ,
739 attribute_category ,
740 attribute1,
741 attribute2 ,
742 attribute3 ,
743 attribute4,
744 attribute5,
745 attribute6,
746 attribute7 ,
747 attribute8 ,
748 attribute9,
749 attribute10,
750 attribute11,
751 attribute12,
752 attribute13 ,
753 attribute14 ,
754 attribute15,
755 raw_cost_source,
756 burdened_cost_source,
757 quantity_source ,
758 revenue_source ,
759 pm_product_code,
760 pm_budget_line_reference ,
761 cost_rejection_code ,
762 revenue_rejection_code ,
763 burden_rejection_code ,
764 other_rejection_code ,
765 code_combination_id ,
766 ccid_gen_status_code,
767 ccid_gen_rej_message ,
768 request_id ,
769 borrowed_revenue ,
770 tp_revenue_in,
771 tp_revenue_out ,
772 revenue_adj,
773 lent_resource_cost,
774 tp_cost_in ,
775 tp_cost_out,
776 cost_adj ,
777 unassigned_time_cost,
778 utilization_percent,
779 utilization_hours ,
780 utilization_adj ,
781 capacity,
782 head_count ,
783 head_count_adj,
784 projfunc_currency_code,
785 projfunc_cost_rate_type ,
786 projfunc_cost_exchange_rate,
787 projfunc_cost_rate_date_type ,
788 projfunc_cost_rate_date ,
789 projfunc_rev_rate_type ,
790 projfunc_rev_exchange_rate ,
791 projfunc_rev_rate_date_type ,
792 projfunc_rev_rate_date ,
793 project_currency_code ,
794 project_cost_rate_type ,
795 project_cost_exchange_rate ,
796 project_cost_rate_date_type ,
797 project_cost_rate_date,
798 project_raw_cost ,
799 project_burdened_cost ,
800 project_rev_rate_type ,
801 project_rev_exchange_rate,
802 project_rev_rate_date_type ,
803 project_rev_rate_date ,
804 project_revenue,
805 txn_currency_code,
806 txn_raw_cost,
807 txn_burdened_cost ,
808 txn_revenue,
809 bucketing_period_code,
810 budget_line_id ,
811 budget_version_id)
812 ( select
813 resource_assignment_id,
814 start_date,
815 last_update_date,
816 last_updated_by,
817 creation_date,
818 created_by ,
819 last_update_login,
820 end_date,
821 period_name,
822 quantity,
823 raw_cost,
824 burdened_cost ,
825 revenue ,
826 change_reason_code,
827 description ,
828 attribute_category ,
829 attribute1,
830 attribute2 ,
831 attribute3 ,
832 attribute4,
833 attribute5,
834 attribute6,
835 attribute7 ,
836 attribute8 ,
837 attribute9,
838 attribute10,
839 attribute11,
840 attribute12,
841 attribute13 ,
842 attribute14 ,
843 attribute15,
844 raw_cost_source,
845 burdened_cost_source,
846 quantity_source ,
847 revenue_source ,
848 pm_product_code,
849 pm_budget_line_reference ,
850 cost_rejection_code ,
851 revenue_rejection_code ,
852 burden_rejection_code ,
853 other_rejection_code ,
854 code_combination_id ,
855 ccid_gen_status_code,
856 ccid_gen_rej_message ,
857 request_id ,
858 borrowed_revenue ,
859 tp_revenue_in,
860 tp_revenue_out ,
861 revenue_adj,
862 lent_resource_cost,
863 tp_cost_in ,
864 tp_cost_out,
865 cost_adj ,
866 unassigned_time_cost,
867 utilization_percent,
868 utilization_hours ,
869 utilization_adj ,
870 capacity,
871 head_count ,
872 head_count_adj,
873 projfunc_currency_code,
874 projfunc_cost_rate_type ,
875 projfunc_cost_exchange_rate,
876 projfunc_cost_rate_date_type ,
877 projfunc_cost_rate_date ,
878 projfunc_rev_rate_type ,
879 projfunc_rev_exchange_rate ,
880 projfunc_rev_rate_date_type ,
881 projfunc_rev_rate_date ,
882 project_currency_code ,
883 project_cost_rate_type ,
884 project_cost_exchange_rate ,
885 project_cost_rate_date_type ,
886 project_cost_rate_date,
887 project_raw_cost ,
888 project_burdened_cost ,
889 project_rev_rate_type ,
890 project_rev_exchange_rate,
891 project_rev_rate_date_type ,
892 project_rev_rate_date ,
893 project_revenue,
894 txn_currency_code,
895 txn_raw_cost,
896 txn_burdened_cost ,
897 txn_revenue,
898 bucketing_period_code,
899 budget_line_id ,
900 budget_version_id from pa_budget_lines where
901 resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
902 and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
903 and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id);
904
905 delete from pa_budget_lines
906 where resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
907 and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
908 and start_date <> l_get_non_time_multi_csr.min_date
909 and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
910
911 update pa_budget_lines
912 set start_date = l_get_non_time_multi_csr.min_date,
913 end_date = l_get_non_time_multi_csr.max_date,
914 quantity = l_get_non_time_multi_csr.sum_quantity,
915 raw_cost = l_get_non_time_multi_csr.sum_raw_cost,
916 burdened_cost = l_get_non_time_multi_csr.sum_burdened_cost,
917 revenue = l_get_non_time_multi_csr.sum_revenue,
918 project_raw_cost = l_get_non_time_multi_csr.sum_project_raw_cost,
919 project_burdened_cost = l_get_non_time_multi_csr.sum_project_burdened_cost,
920 project_revenue = l_get_non_time_multi_csr.sum_project_revenue,
921 txn_raw_cost = l_get_non_time_multi_csr.sum_txn_raw_cost,
922 txn_burdened_cost = l_get_non_time_multi_csr.sum_txn_burdened_cost,
923 txn_revenue = l_get_non_time_multi_csr.sum_txn_revenue
924 where resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
925 and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
926 and start_date = l_get_non_time_multi_csr.min_date
927 and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
928
929 end loop;
930 -- Loop for each budget line for the given version ends here
931 end if;
932 if (g_upgrade_mode = 'PA_Period_Upgrade') or (g_upgrade_mode = 'GL_Period_Upgrade') then
933
934 IF l_debug_mode = 'Y' THEN
935 pa_debug.g_err_stage := 'Entered PA/GL Period Upgrade';
936 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
937 end if;
938
939 -- Loop for Resource Assignment Id for given Budget Version
940 select decode(g_upgrade_mode,'PA_Period_Upgrade',l_pa_period_type,l_accounted_per_type) into l_per_type from dual;
941 for l_get_res_assign_id_csr in get_res_assign_id_csr(l_get_elig_bud_ver_csr.budget_version_id)
942 loop
943
944
945 -- Bug 3673111, 07-JUN-04, jwhite ------------------------------------------
946 -- Added begin/end block to catch no_data_found ORA errors.
947
948 begin
949 select
950 attribute_category ,
951 attribute1 ,
952 attribute2 ,
953 attribute3,
954 attribute4,
955 attribute5 ,
956 attribute6,
957 attribute7 ,
958 attribute8 ,
959 attribute9,
960 attribute10,
961 attribute11,
962 attribute12 ,
963 attribute13,
964 attribute14 ,
965 attribute15
966 into
967 l_attribute_category ,
968 l_attribute1 ,
969 l_attribute2 ,
970 l_attribute3,
971 l_attribute4,
972 l_attribute5 ,
973 l_attribute6,
974 l_attribute7 ,
975 l_attribute8 ,
976 l_attribute9,
977 l_attribute10,
978 l_attribute11,
979 l_attribute12 ,
980 l_attribute13,
981 l_attribute14 ,
982 l_attribute15
983 from pa_budget_lines where
984 start_date = l_get_res_assign_id_csr.planning_start_date
985 and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
986
987 exception
988 when no_data_found then
989 l_attribute_category := Null;
990 l_attribute1 := Null;
991 l_attribute2 := Null;
992 l_attribute3 := Null;
993 l_attribute4 := Null;
994 l_attribute5 := Null;
995 l_attribute6 := Null;
996 l_attribute7 := Null;
997 l_attribute8 := Null;
998 l_attribute9 := Null;
999 l_attribute10 := Null;
1000 l_attribute11 := Null;
1001 l_attribute12 := Null;
1002 l_attribute13 := Null;
1003 l_attribute14 := Null;
1004 l_attribute15 := Null;
1005 end;
1006
1007
1008 -- End Bug 3673111, 07-JUN-04, jwhite ------------------------------------------
1009
1010
1011
1012
1013 IF l_debug_mode = 'Y' THEN
1014 pa_debug.g_err_stage := 'Inserting into Backup Table';
1015 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1016 end if;
1017
1018 insert into pa_budget_lines_m_upg_dtrange
1019 (
1020 resource_assignment_id,
1021 start_date,
1022 last_update_date,
1023 last_updated_by,
1024 creation_date,
1025 created_by ,
1026 last_update_login,
1027 end_date,
1028 period_name,
1029 quantity,
1030 raw_cost,
1031 burdened_cost ,
1032 revenue ,
1033 change_reason_code,
1034 description ,
1035 attribute_category ,
1036 attribute1,
1037 attribute2 ,
1038 attribute3 ,
1039 attribute4,
1040 attribute5,
1041 attribute6,
1042 attribute7 ,
1043 attribute8 ,
1044 attribute9,
1045 attribute10,
1046 attribute11,
1047 attribute12,
1048 attribute13 ,
1049 attribute14 ,
1050 attribute15,
1051 raw_cost_source,
1052 burdened_cost_source,
1053 quantity_source ,
1054 revenue_source ,
1055 pm_product_code,
1056 pm_budget_line_reference ,
1057 cost_rejection_code ,
1058 revenue_rejection_code ,
1059 burden_rejection_code ,
1060 other_rejection_code ,
1061 code_combination_id ,
1062 ccid_gen_status_code,
1063 ccid_gen_rej_message ,
1064 request_id ,
1065 borrowed_revenue ,
1066 tp_revenue_in,
1067 tp_revenue_out ,
1068 revenue_adj,
1069 lent_resource_cost,
1070 tp_cost_in ,
1071 tp_cost_out,
1072 cost_adj ,
1073 unassigned_time_cost,
1074 utilization_percent,
1075 utilization_hours ,
1076 utilization_adj ,
1077 capacity,
1078 head_count ,
1079 head_count_adj,
1080 projfunc_currency_code,
1081 projfunc_cost_rate_type ,
1082 projfunc_cost_exchange_rate,
1083 projfunc_cost_rate_date_type ,
1084 projfunc_cost_rate_date ,
1085 projfunc_rev_rate_type ,
1086 projfunc_rev_exchange_rate ,
1087 projfunc_rev_rate_date_type ,
1088 projfunc_rev_rate_date ,
1089 project_currency_code ,
1090 project_cost_rate_type ,
1091 project_cost_exchange_rate ,
1092 project_cost_rate_date_type ,
1093 project_cost_rate_date,
1094 project_raw_cost ,
1095 project_burdened_cost ,
1096 project_rev_rate_type ,
1097 project_rev_exchange_rate,
1098 project_rev_rate_date_type ,
1099 project_rev_rate_date ,
1100 project_revenue,
1101 txn_currency_code,
1102 txn_raw_cost,
1103 txn_burdened_cost ,
1104 txn_revenue,
1105 bucketing_period_code,
1106 budget_line_id ,
1107 budget_version_id)
1108 select
1109 resource_assignment_id,
1110 start_date,
1111 last_update_date,
1112 last_updated_by,
1113 creation_date,
1114 created_by ,
1115 last_update_login,
1116 end_date,
1117 period_name,
1118 quantity,
1119 raw_cost,
1120 burdened_cost ,
1121 revenue ,
1122 change_reason_code,
1123 description ,
1124 attribute_category ,
1125 attribute1,
1126 attribute2 ,
1127 attribute3 ,
1128 attribute4,
1129 attribute5,
1130 attribute6,
1131 attribute7 ,
1132 attribute8 ,
1133 attribute9,
1134 attribute10,
1135 attribute11,
1136 attribute12,
1137 attribute13 ,
1138 attribute14 ,
1139 attribute15,
1140 raw_cost_source,
1141 burdened_cost_source,
1142 quantity_source ,
1143 revenue_source ,
1144 pm_product_code,
1145 pm_budget_line_reference ,
1146 cost_rejection_code ,
1147 revenue_rejection_code ,
1148 burden_rejection_code ,
1149 other_rejection_code ,
1150 code_combination_id ,
1151 ccid_gen_status_code,
1152 ccid_gen_rej_message ,
1153 request_id ,
1154 borrowed_revenue ,
1155 tp_revenue_in,
1156 tp_revenue_out ,
1157 revenue_adj,
1158 lent_resource_cost,
1159 tp_cost_in ,
1160 tp_cost_out,
1161 cost_adj ,
1162 unassigned_time_cost,
1163 utilization_percent,
1164 utilization_hours ,
1165 utilization_adj ,
1166 capacity,
1167 head_count ,
1168 head_count_adj,
1169 projfunc_currency_code,
1170 projfunc_cost_rate_type ,
1171 projfunc_cost_exchange_rate,
1172 projfunc_cost_rate_date_type ,
1173 projfunc_cost_rate_date ,
1174 projfunc_rev_rate_type ,
1175 projfunc_rev_exchange_rate ,
1176 projfunc_rev_rate_date_type ,
1177 projfunc_rev_rate_date ,
1178 project_currency_code ,
1179 project_cost_rate_type ,
1180 project_cost_exchange_rate ,
1181 project_cost_rate_date_type ,
1182 project_cost_rate_date,
1183 project_raw_cost ,
1184 project_burdened_cost ,
1185 project_rev_rate_type ,
1186 project_rev_exchange_rate,
1187 project_rev_rate_date_type ,
1188 project_rev_rate_date ,
1189 project_revenue,
1190 txn_currency_code,
1191 txn_raw_cost,
1192 txn_burdened_cost ,
1193 txn_revenue,
1194 bucketing_period_code,
1195 budget_line_id ,
1196 budget_version_id from pa_budget_lines where
1197 budget_version_id = l_get_elig_bud_ver_csr.budget_version_id
1198 and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
1199
1200 l_counter := 0;
1201 l_get_budget_lines_tbl.delete; /* bug 3673111: moved here from loop below */
1202
1203 --Bug 4299635
1204 l_plan_txn_post_upg_qty_tbl.delete;
1205 l_plan_txn_act_qty_tbl.delete;
1206 l_plan_txn_post_upg_rc_tbl.delete;
1207 l_plan_txn_act_rc_tbl.delete;
1208 l_plan_txn_post_upg_bc_tbl.delete;
1209 l_plan_txn_act_bc_tbl.delete;
1210 l_plan_txn_post_upg_rev_tbl.delete;
1211 l_plan_txn_act_rev_tbl.delete;
1212 l_last_bl_indx_in_plan_txn_tbl.delete;
1213 l_max_st_dt_in_plan_txn_tbl.delete;
1214
1215 -- Loop for Budget Lines for each Resource Assignment Id
1216 for l_get_budget_lines_csr in get_budget_lines_csr(l_get_elig_bud_ver_csr.budget_version_id,l_get_res_assign_id_csr.resource_assignment_id,l_get_elig_bud_ver_csr.org_id,l_per_type, l_sob_period_set_name)
1217 loop
1218
1219
1220 -- bug 3673111, 14-JUL-04, jwhite --------------------------------------------------------
1221 -- Purpose of the l_min_date is to find the earliest date for the budgets lines being processed.
1222 --
1223 -- For the original logic, With the l_min_date initialized to sysdate, the logic would not
1224 -- work when all budget lines are created with dates beyond the current sysdate.
1225
1226 -- Therefore, added the following conditional initialization:
1227
1228 IF ( l_counter = 0 )
1229 then
1230
1231 l_min_date := l_get_budget_lines_csr.gl_start_date;
1232
1233 End IF;
1234
1235 -- End Bug 3673111, 14-JUL-04, jwhite ---------------------------------------------------
1236
1237
1238 l_counter := l_counter + 1;
1239 if (l_min_date > l_get_budget_lines_csr.gl_start_date) then
1240 l_min_date := l_get_budget_lines_csr.gl_start_date;
1241 end if;
1242
1243 -- bug 3673111, 14-JUL-04, jwhite --------------------------------------------------------
1244 --
1245
1246 -- 1) Moved statement "l_get_budget_lines_tbl.delete;" out of loop so ALL records could be stored
1247 -- for subsequent processing. If inside loop, then only last record stored.
1248 --
1249 -- 2) For the start/end dates, changed the source to the GL start/end dates. This is necessary
1250 -- to store the period start and end dates corresponding to the Period_Name on the pa_budget_lines.
1251 --
1252 -- 3) Added PERIOD_NAME to table array for the subsequent insert statement
1253 --
1254
1255 -- 4) Added conditional testing for table array record count to prevent ORA errors.
1256 --
1257
1258
1259 l_get_budget_lines_tbl(l_counter).gl_start_date := l_get_budget_lines_csr.gl_start_date ;
1260 l_get_budget_lines_tbl(l_counter).gl_end_date := l_get_budget_lines_csr.gl_end_date;
1261 l_get_budget_lines_tbl(l_counter).resource_assignment_id := l_get_budget_lines_csr.resource_assignment_id;
1262 l_get_budget_lines_tbl(l_counter).txn_currency_code := l_get_budget_lines_csr.txn_currency_code;
1263 l_get_budget_lines_tbl(l_counter).spr_quantity := l_get_budget_lines_csr.spr_quantity;
1264 l_get_budget_lines_tbl(l_counter).spr_raw_cost := l_get_budget_lines_csr.spr_raw_cost;
1265 l_get_budget_lines_tbl(l_counter).spr_burdened_cost := l_get_budget_lines_csr.spr_burdened_cost;
1266 l_get_budget_lines_tbl(l_counter).spr_revenue := l_get_budget_lines_csr.spr_revenue;
1267 l_get_budget_lines_tbl(l_counter).spr_project_raw_cost := l_get_budget_lines_csr.spr_project_raw_cost;
1268 l_get_budget_lines_tbl(l_counter).spr_project_burdened_cost := l_get_budget_lines_csr.spr_project_burdened_cost;
1269 l_get_budget_lines_tbl(l_counter).spr_project_revenue := l_get_budget_lines_csr.spr_project_revenue;
1270 l_get_budget_lines_tbl(l_counter).spr_txn_raw_cost := l_get_budget_lines_csr.spr_txn_raw_cost;
1271 l_get_budget_lines_tbl(l_counter).spr_txn_burdened_cost := l_get_budget_lines_csr.spr_txn_burdened_cost;
1272 l_get_budget_lines_tbl(l_counter).spr_txn_revenue := l_get_budget_lines_csr.spr_txn_revenue;
1273 l_get_budget_lines_tbl(l_counter).PERIOD_NAME := l_get_budget_lines_csr.PERIOD_NAME;
1274
1275 --Bug 4299635
1276 l_txn_curr_index:=l_get_budget_lines_csr.txn_currency_code;
1277
1278 --Derive the Quantity that should get upgraded (l_plan_txn_act_qty_tbl) and the quantity
1279 --derived for upgrade (l_plan_txn_post_upg_qty_tbl).Bug 4299635
1280 IF l_plan_txn_act_qty_tbl.EXISTS(l_txn_curr_index) THEN
1281 l_plan_txn_act_qty_tbl(l_txn_curr_index) := nvl(l_plan_txn_act_qty_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.total_qty,0);
1282 ELSE
1283 l_plan_txn_act_qty_tbl(l_txn_curr_index) := nvl(l_get_budget_lines_csr.total_qty,0);
1284 END IF;
1285
1286 IF l_plan_txn_post_upg_qty_tbl.EXISTS(l_txn_curr_index) THEN
1287 l_plan_txn_post_upg_qty_tbl(l_txn_curr_index) := NVL(l_plan_txn_post_upg_qty_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.spr_quantity,0);
1288 ELSE
1289 l_plan_txn_post_upg_qty_tbl(l_txn_curr_index):=nvl(l_get_budget_lines_csr.spr_quantity,0);
1290 END IF;
1291
1292 --Derive the txn raw cost that should get upgraded (l_plan_txn_act_rc_tbl) and the txn raw cost
1293 --derived for upgrade (l_plan_txn_post_upg_rc_tbl).Bug 4299635
1294 IF l_plan_txn_act_rc_tbl.EXISTS(l_txn_curr_index) THEN
1295 l_plan_txn_act_rc_tbl(l_txn_curr_index) := nvl(l_plan_txn_act_rc_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.total_txn_raw_cost,0);
1296 ELSE
1297 l_plan_txn_act_rc_tbl(l_txn_curr_index) := nvl(l_get_budget_lines_csr.total_txn_raw_cost,0);
1298 END IF;
1299
1300 IF l_plan_txn_post_upg_rc_tbl.EXISTS(l_txn_curr_index) THEN
1301 l_plan_txn_post_upg_rc_tbl(l_txn_curr_index) := NVL(l_plan_txn_post_upg_rc_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.spr_txn_raw_cost,0);
1302 ELSE
1303 l_plan_txn_post_upg_rc_tbl(l_txn_curr_index):=nvl(l_get_budget_lines_csr.spr_txn_raw_cost,0);
1304 END IF;
1305
1306 --Derive the txn burdened cost that should get upgraded (l_plan_txn_act_bc_tbl) and the txn burdened cost
1307 --derived for upgrade (l_plan_txn_post_upg_bc_tbl).Bug 4299635
1308 IF l_plan_txn_act_bc_tbl.EXISTS(l_txn_curr_index) THEN
1309 l_plan_txn_act_bc_tbl(l_txn_curr_index) := nvl(l_plan_txn_act_bc_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.total_txn_burd_cost,0);
1310 ELSE
1311 l_plan_txn_act_bc_tbl(l_txn_curr_index) := nvl(l_get_budget_lines_csr.total_txn_burd_cost,0);
1312 END IF;
1313
1314 IF l_plan_txn_post_upg_bc_tbl.EXISTS(l_txn_curr_index) THEN
1315 l_plan_txn_post_upg_bc_tbl(l_txn_curr_index) := NVL(l_plan_txn_post_upg_bc_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.spr_txn_burdened_cost,0);
1316 ELSE
1317 l_plan_txn_post_upg_bc_tbl(l_txn_curr_index):=nvl(l_get_budget_lines_csr.spr_txn_burdened_cost,0);
1318 END IF;
1319
1320 --Derive the txn revenue that should get upgraded (l_plan_txn_act_rev_tbl) and the txn revenue
1321 --derived for upgrade (l_plan_txn_post_upg_rev_tbl).Bug 4299635
1322 IF l_plan_txn_act_rev_tbl.EXISTS(l_txn_curr_index) THEN
1323 l_plan_txn_act_rev_tbl(l_txn_curr_index) := nvl(l_plan_txn_act_rev_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.total_txn_revenue,0);
1324 ELSE
1325 l_plan_txn_act_rev_tbl(l_txn_curr_index) := nvl(l_get_budget_lines_csr.total_txn_revenue,0);
1326 END IF;
1327
1328 IF l_plan_txn_post_upg_rev_tbl.EXISTS(l_txn_curr_index) THEN
1329 l_plan_txn_post_upg_rev_tbl(l_txn_curr_index) := NVL(l_plan_txn_post_upg_rev_tbl(l_txn_curr_index),0) + nvl(l_get_budget_lines_csr.spr_txn_revenue,0);
1330 ELSE
1331 l_plan_txn_post_upg_rev_tbl(l_txn_curr_index):=nvl(l_get_budget_lines_csr.spr_txn_revenue,0);
1332 END IF;
1333
1334 IF l_max_st_dt_in_plan_txn_tbl.EXISTS(l_txn_curr_index) THEN
1335 IF l_max_st_dt_in_plan_txn_tbl(l_txn_curr_index) < l_get_budget_lines_csr.gl_start_date THEN
1336 l_max_st_dt_in_plan_txn_tbl(l_txn_curr_index):=l_get_budget_lines_csr.gl_start_date;
1337 l_last_bl_indx_in_plan_txn_tbl(l_txn_curr_index):=l_counter;
1338 END IF;
1339 ELSE
1340 l_max_st_dt_in_plan_txn_tbl(l_txn_curr_index):=l_get_budget_lines_csr.gl_start_date;
1341 l_last_bl_indx_in_plan_txn_tbl(l_txn_curr_index):=l_counter;
1342 END IF;
1343
1344
1345 end loop;
1346 -- Loop for Budget Lines for each Resource Assignment Id
1347
1348
1349
1350
1351 IF (l_get_budget_lines_tbl.count > 0) /* bug 3673111 */
1352 THEN
1353
1354 --Bug 4299635. If the amounts before and after upgrade do not tally then adjust the difference
1355 -- in the last budget line of each palnning transaction
1356 l_txn_curr_index := NULL;
1357 FOR i IN 1..l_plan_txn_act_qty_tbl.COUNT LOOP
1358
1359 IF l_txn_curr_index IS NULL THEN
1360
1361 l_txn_curr_index := l_plan_txn_act_qty_tbl.FIRST;
1362
1363 ELSE
1364
1365 l_txn_curr_index := l_plan_txn_act_qty_tbl.NEXT(l_txn_curr_index);
1366
1367 END IF;
1368 l_last_bl_index := l_last_bl_indx_in_plan_txn_tbl(l_txn_curr_index);
1369
1370 IF NVL(l_plan_txn_act_qty_tbl(l_txn_curr_index),0) <> NVL(l_plan_txn_post_upg_qty_tbl(l_txn_curr_index),0) THEN
1371
1372 l_get_budget_lines_tbl(l_last_bl_index).spr_quantity := nvl(l_get_budget_lines_tbl(l_last_bl_index).spr_quantity,0) +
1373 NVL(l_plan_txn_act_qty_tbl(l_txn_curr_index),0) -
1374 NVL(l_plan_txn_post_upg_qty_tbl(l_txn_curr_index),0);
1375
1376 END IF;
1377
1378 IF NVL(l_plan_txn_act_rc_tbl(l_txn_curr_index),0) <> NVL(l_plan_txn_post_upg_rc_tbl(l_txn_curr_index),0) THEN
1379
1380 l_get_budget_lines_tbl(l_last_bl_index).spr_txn_raw_cost := nvl(l_get_budget_lines_tbl(l_last_bl_index).spr_txn_raw_cost,0) +
1381 NVL(l_plan_txn_act_rc_tbl(l_txn_curr_index),0) -
1382 NVL(l_plan_txn_post_upg_rc_tbl(l_txn_curr_index),0);
1383
1384 END IF;
1385
1386 IF NVL(l_plan_txn_act_bc_tbl(l_txn_curr_index),0) <> NVL(l_plan_txn_post_upg_bc_tbl(l_txn_curr_index),0) THEN
1387
1388 l_get_budget_lines_tbl(l_last_bl_index).spr_txn_burdened_cost := nvl(l_get_budget_lines_tbl(l_last_bl_index).spr_txn_burdened_cost,0) +
1389 NVL(l_plan_txn_act_bc_tbl(l_txn_curr_index),0) -
1390 NVL(l_plan_txn_post_upg_bc_tbl(l_txn_curr_index),0);
1391
1392 END IF;
1393
1394 IF NVL(l_plan_txn_act_rev_tbl(l_txn_curr_index),0) <> NVL(l_plan_txn_post_upg_rev_tbl(l_txn_curr_index),0) THEN
1395
1396 l_get_budget_lines_tbl(l_last_bl_index).spr_txn_revenue := nvl(l_get_budget_lines_tbl(l_last_bl_index).spr_txn_revenue,0) +
1397 NVL(l_plan_txn_act_rev_tbl(l_txn_curr_index),0) -
1398 NVL(l_plan_txn_post_upg_rev_tbl(l_txn_curr_index),0);
1399
1400 END IF;
1401
1402 END LOOP;
1403
1404
1405 -- Loop to traverse through the array or records.
1406 IF l_debug_mode = 'Y' THEN
1407 pa_debug.g_err_stage := 'Deleting from pa_budget_lines table';
1408 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1409 end if;
1410 for i in l_get_budget_lines_tbl.first .. l_get_budget_lines_tbl.last
1411 loop
1412 delete pa_budget_lines
1413 where resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id and
1414 budget_version_id = l_get_elig_bud_ver_csr.budget_version_id and
1415 txn_currency_code = l_get_budget_lines_tbl(i).txn_currency_code;
1416 end loop;
1417 -- Loop to traverse through the array or records ends here.
1418
1419 -- Loop to traverse through the array or records for insertion.
1420 for i in l_get_budget_lines_tbl.first .. l_get_budget_lines_tbl.last
1421 loop
1422
1423 insert into pa_budget_lines(
1424 last_update_date,
1425 last_updated_by,
1426 creation_date,
1427 created_by,
1428 last_update_login,
1429 start_date,
1430 end_date,
1431 resource_assignment_id,
1432 txn_currency_code,
1433 quantity,
1434 raw_cost,
1435 burdened_cost,
1436 revenue,
1437 project_raw_cost,
1438 project_burdened_cost,
1439 project_revenue,
1440 txn_raw_cost,
1441 txn_burdened_cost,
1442 txn_revenue,
1443 budget_line_id,
1444 budget_version_id,
1445 PERIOD_NAME, /* bug 3673111 */
1446 --Bug 4046524.Columns included for this bug start here
1447 project_currency_code,
1448 projfunc_currency_code,
1449 projfunc_cost_rate_type ,
1450 projfunc_cost_exchange_rate,
1451 projfunc_cost_rate_date_type,
1452 projfunc_cost_rate_date,
1453 projfunc_rev_rate_type,
1454 projfunc_rev_exchange_rate,
1455 projfunc_rev_rate_date_type,
1456 projfunc_rev_rate_date,
1457 project_cost_rate_type ,
1458 project_cost_exchange_rate ,
1459 project_cost_rate_date_type ,
1460 project_cost_rate_date,
1461 project_rev_rate_type,
1462 project_rev_exchange_rate,
1463 project_rev_rate_date_type,
1464 project_rev_rate_date
1465 --Bug 4046524.Columns included for this bug end here
1466 )
1467 select
1468 sysdate,
1469 -1,
1470 sysdate,
1471 -1,
1472 -1,
1473 l_get_budget_lines_tbl(i).gl_start_date,
1474 l_get_budget_lines_tbl(i).gl_end_date,
1475 l_get_budget_lines_tbl(i).resource_assignment_id, /* bug 3673111 */
1476 l_get_budget_lines_tbl(i).txn_currency_code,
1477 l_get_budget_lines_tbl(i).spr_quantity,
1478 l_get_budget_lines_tbl(i).spr_raw_cost,
1479 l_get_budget_lines_tbl(i).spr_burdened_cost,
1480 l_get_budget_lines_tbl(i).spr_revenue,
1481 l_get_budget_lines_tbl(i).spr_project_raw_cost,
1482 l_get_budget_lines_tbl(i).spr_project_burdened_cost,
1483 l_get_budget_lines_tbl(i).spr_project_revenue,
1484 l_get_budget_lines_tbl(i).spr_txn_raw_cost,
1485 l_get_budget_lines_tbl(i).spr_txn_burdened_cost,
1486 l_get_budget_lines_tbl(i).spr_txn_revenue,
1487 pa_budget_lines_s.nextval,
1488 l_get_elig_bud_ver_csr.budget_version_id,
1489 l_get_budget_lines_tbl(i).PERIOD_NAME, /* bug 3673111 */
1490 --Bug 4046524.Columns included for this bug start here
1491 l_get_elig_bud_ver_csr.project_currency_code,
1492 l_get_elig_bud_ver_csr.projfunc_currency_code,
1493 'User', --projfunc_cost_rate_type
1494 DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_raw_cost,0), --projfunc_cost_exchange_rate
1495 0,0,
1496 l_get_budget_lines_tbl(i).spr_raw_cost/l_get_budget_lines_tbl(i).spr_txn_raw_cost),
1497 NULL, --projfunc_cost_rate_date_type
1498 NULL, --projfunc_cost_rate_date
1499 'User', --projfunc_rev_rate_type
1500 DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_revenue,0), --projfunc_rev_exchange_rate
1501 0,0,
1502 l_get_budget_lines_tbl(i).spr_revenue/l_get_budget_lines_tbl(i).spr_txn_revenue),
1503 NULL, --projfunc_rev_rate_date_type
1504 NULL, --projfunc_rev_rate_date
1505 'User', --project_cost_rate_type
1506 DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_raw_cost,0), --project_cost_exchange_rate
1507 0,0,
1508 l_get_budget_lines_tbl(i).spr_project_raw_cost/l_get_budget_lines_tbl(i).spr_txn_raw_cost),
1509 NULL, --project_cost_rate_date_type
1510 NULL, --project_cost_rate_date
1511 'User', --project_rev_rate_type
1512 DECODE(NVL(l_get_budget_lines_tbl(i).spr_txn_revenue,0), --project_rev_exchange_rate
1513 0,0,
1514 l_get_budget_lines_tbl(i).spr_project_revenue/l_get_budget_lines_tbl(i).spr_txn_revenue),
1515 NULL, --project_rev_rate_date_type
1516 NULL --project_rev_rate_date
1517 --Bug 4046524.Columns included for this bug end here
1518 from dual;
1519 null;
1520
1521 end loop;
1522
1523 -- Loop to traverse through the array or records for insertion ends.
1524 IF l_debug_mode = 'Y' THEN
1525 pa_debug.g_err_stage := 'Updating pa_budget_lines table';
1526 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1527 end if;
1528 update pa_budget_lines
1529 set
1530 attribute_category = l_attribute_category ,
1531 attribute1 = l_attribute1 ,
1532 attribute2 = l_attribute2 ,
1533 attribute3 = l_attribute3,
1534 attribute4 = l_attribute4,
1535 attribute5 = l_attribute5 ,
1536 attribute6 = l_attribute6,
1537 attribute7 = l_attribute7 ,
1538 attribute8 = l_attribute8 ,
1539 attribute9 = l_attribute9,
1540 attribute10 = l_attribute10,
1541 attribute11 = l_attribute11,
1542 attribute12 = l_attribute12 ,
1543 attribute13 = l_attribute13,
1544 attribute14 = l_attribute14 ,
1545 attribute15 = l_attribute15
1546 where start_date = l_min_date
1547 and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
1548
1549 End If; -- IF (l_get_budget_lines_tbl.count > 0)
1550
1551 end loop;
1552 -- Loop for Resource Assignment Id for given Budget Version Ends here.
1553 end if;
1554
1555
1556 END LOOP; -- Budget Version cursor
1557
1558 -- Bug 3673111, 07-JUN-04, jwhite ---------------------------------
1559 -- Place GOTO LABEL here SKIP Invalid Budget Version
1560
1561
1562 <<skip_version>>
1563 NULL ;
1564
1565
1566 end loop;
1567
1568 EXCEPTION
1569 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc then
1570 l_msg_count := FND_MSG_PUB.count_msg;
1571 IF l_msg_count = 1 THEN
1572 PA_INTERFACE_UTILS_PUB.get_messages
1573 (p_encoded => FND_API.G_TRUE
1574 ,p_msg_index => 1
1575 ,p_msg_count => l_msg_count
1576 ,p_msg_data => l_msg_data
1577 ,p_data => l_data
1578 ,p_msg_index_out => l_msg_index_out);
1579 x_msg_data := l_data;
1580 x_msg_count := l_msg_count;
1581 ELSE
1582 x_msg_count := l_msg_count;
1583 x_msg_data := l_msg_data;
1584 END IF;
1585
1586 IF l_debug_mode = 'Y' THEN
1587 pa_debug.g_err_stage:='Invalid Arguments Passed';
1588 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1589 END IF;
1590 x_return_status:= FND_API.G_RET_STS_ERROR;
1591 pa_debug.reset_err_stack;
1592 -- ROLLBACK; /* Commented-out to maintain the savepoint for the concurrent program. */
1593 RAISE;
1594
1595 WHEN OTHERS THEN
1596 if (get_elig_bud_ver_csr%ISOPEN) then
1597 close get_elig_bud_ver_csr;
1598 end if;
1599
1600 if (chk_plan_ver_csr%ISOPEN) then
1601 close chk_plan_ver_csr;
1602 end if;
1603
1604 if (chk_ra_exists_csr%ISOPEN) then
1605 close chk_ra_exists_csr;
1606 end if;
1607
1608 if (chk_ra_for_bl_exists_csr%ISOPEN) then
1609 close chk_ra_for_bl_exists_csr;
1610 end if;
1611
1612 if (get_per_type_csr%ISOPEN) then
1613 close get_per_type_csr;
1614 end if;
1615
1616 if (chk_pa_gl_per_exists_csr%ISOPEN) then
1617 close chk_pa_gl_per_exists_csr;
1618 end if;
1619
1620 if (get_non_time_multi_csr%ISOPEN) then
1621 close get_non_time_multi_csr;
1622 end if;
1623
1624 if (get_res_assign_id_csr%ISOPEN) then
1625 close get_res_assign_id_csr;
1626 end if;
1627
1628 if (ftch_period_details_csr%ISOPEN) then
1629 close ftch_period_details_csr;
1630 end if;
1631
1632 if (get_budget_lines_csr%ISOPEN) then
1633 close get_budget_lines_csr;
1634 end if;
1635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1636 x_msg_count := 1;
1637 x_msg_data := SQLERRM;
1638
1639 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_DATE_RANGE_PKG',p_procedure_name => 'DATE_RANGE_UPGRD');
1640 IF l_debug_mode = 'Y' THEN
1641 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1642 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1643 END IF;
1644
1645 pa_debug.write_file('DATE_RANGE_UPGRD: Upgrade has failed for the budget version '||l_budget_version_id,5);
1646 pa_debug.write_file('DATE_RANGE_UPGRD: Failure Reason:'||pa_debug.G_Err_Stack,5);
1647 pa_debug.reset_err_stack;
1648 --ROLLBACK; /* Commented-out to maintain the savepoint for the concurrent program. */
1649 RAISE;
1650 end DATE_RANGE_UPGRD;
1651
1652 --Bug 4046492. This API returns the Time Phase into which a budget version should be upgraded. The values
1653 --that can be returned are
1654 --'P' if the budget version has to upgraded to PA Time Phase
1655 --'G' if the budget version has to upgraded to GL Time Phase
1656 --'N' if the budget version has to upgraded to None Time Phase
1657 --This function will be called from the upgrade script paupg102.sql and PADTRNGB.DATE_RANGE_UPGRD.No validations are done
1658 --in this API and the calling APIs should take care of passing correct values.
1659 FUNCTION get_time_phase_mode
1660 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1661 ,p_pa_period_type IN pa_implementations_all.pa_period_type%TYPE
1662 ,p_gl_period_type IN gl_sets_of_books.accounted_period_type%TYPE
1663 ,p_org_id IN pa_projects_all.org_id%TYPE) RETURN VARCHAR2
1664 IS
1665 --Bug 4174789. In the expression to derive factor, replaced the division with substraction to nullify
1666 --errors because of infinite digits after decimal point
1667 CURSOR c_derive_time_phase_csr(c_period_type gl_periods.period_type%TYPE)
1668 IS
1669 SELECT 1
1670 FROM dual
1671 WHERE EXISTS
1672 (SELECT 1
1673 FROM (SELECT to_number(NVL(SUM(
1674 (decode(least(bl.start_date, gl.start_date),
1675 bl.start_date,decode(least(bl.end_date,gl.end_date),
1676 gl.end_date,gl.end_date-gl.start_date+1,
1677 bl.end_date,bl.end_date-gl.start_date+1),
1678 gl.start_date,decode(least(bl.end_date,gl.end_date),
1679 gl.end_date,gl.end_date-bl.start_date+1,
1680 bl.end_date,bl.end_date-bl.start_date+1))
1681 )),0)-(bl.end_date-bl.start_date+1)) factor
1682 FROM pa_budget_lines bl,
1683 (SELECT gl.start_date start_date,
1684 gl.end_date end_date,
1685 gl.period_name period_name
1686 FROM gl_periods gl, pa_implementations_all pi, gl_sets_of_books sob
1687 WHERE gl.period_type=c_period_type
1688 AND sob.set_of_books_id=pi.set_of_books_id
1689 AND nvl(pi.org_id,-99)=nvl(p_org_id,-99)
1690 AND gl.adjustment_period_flag='N'
1691 AND gl.period_set_name=sob.period_set_name
1692 UNION ALL
1693 SELECT to_date(NULL) start_date,
1694 to_date(NULL) end_date,
1695 to_char(NULL) period_name
1696 FROM dual) gl
1697 WHERE bl.budget_version_id = p_budget_version_id
1698 AND( (bl.start_date BETWEEN gl.start_date AND gl.end_date
1699 OR bl.end_date BETWEEN gl.start_date AND gl.end_date
1700 OR (gl.start_date > bl.start_date AND gl.end_date < bl.end_date))
1701 OR gl.start_date IS NULL)
1702 GROUP BY bl.budget_line_id,bl.start_date,bl.end_date) pbl
1703 WHERE pbl.factor<>0);
1704
1705 l_exists NUMBER;
1706
1707 BEGIN
1708
1709 OPEN c_derive_time_phase_csr(p_pa_period_type);
1710 FETCH c_derive_time_phase_csr INTO l_exists;
1711 CLOSE c_derive_time_phase_csr;
1712
1713 IF l_exists IS NULL THEN
1714
1715 --PA periods are defined in the system. The budget version can be upgraded succesfully to PA
1716 RETURN 'P';
1717
1718 END IF;
1719
1720 IF l_exists =1 THEN
1721
1722 l_exists:=NULL;
1723 OPEN c_derive_time_phase_csr(p_gl_period_type);
1724 FETCH c_derive_time_phase_csr INTO l_exists;
1725 CLOSE c_derive_time_phase_csr;
1726
1727 END IF;
1728
1729 IF l_exists IS NULL THEN
1730
1731 --GL periods are defined in the system. The budget version can be upgraded succesfully to GL
1732 RETURN 'G';
1733
1734 ELSE
1735
1736 --GL/PA period setup would not help in the correct upgrade of the budget version. Hence it has
1737 --to be upgraded to None Time Phase.
1738 RETURN 'N';
1739
1740 END IF;
1741
1742 END get_time_phase_mode;
1743
1744 end PA_DATE_RANGE_PKG;