[Home] [Help]
PACKAGE BODY: APPS.GMS_SUMMARIZE_BUDGETS
Source
1 PACKAGE BODY GMS_SUMMARIZE_BUDGETS AS
2 -- $Header: gmsbusub.pls 120.6 2011/11/04 05:50:21 mokukuma ship $
3
4 -- To check on, whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6 x_mixed_budgeting varchar2(1);
7
8 -- Bug 2587078 : Modified parameter name from x_err_code to x_return_status
9 -- to bring the code in consistency with existing grants code
10 -- which uses x_err_code for tracking errors.
11
12 -- Added for GMS enhancement for R12: 5583170
13 -- ==============================================================================
14 Procedure validate_profile_bem (p_project_bem varchar2,
15 p_res_categorized VARCHAR2,
16 p_err_msg_code OUT NOCOPY VARCHAR2 ) is
17 x_err_code NUMBER(15);
18 x_err_stage VARCHAR2(2000);
19 l_start_date_active DATE ;
20 l_end_date_active DATE ;
21 l_entry_level_code pa_budget_entry_methods.entry_level_code%TYPE ;
22 l_categorization_code pa_budget_entry_methods.categorization_code%TYPE ;
23 l_time_phased_type_code pa_budget_entry_methods.time_phased_type_code%TYPE ;
24 l_burdened_cost_flag pa_budget_entry_methods.burdened_cost_flag%TYPE ;
25 l_bem_valid VARCHAR2(1);
26
27 begin
28 p_err_msg_code := 'S' ;
29 l_bem_valid := 'Y' ;
30
31 If p_project_bem IS NOT NULL THEN
32 select start_date_active ,
33 NVL(end_date_active, SYSDATE) ,
34 entry_level_code,categorization_code,
35 time_phased_type_code,
36 burdened_cost_flag
37 into l_start_date_active,
38 l_end_date_active,
39 l_entry_level_code,
40 l_categorization_code,
41 l_time_phased_type_code,
42 l_burdened_cost_flag
43 from pa_budget_entry_methods
44 where BUDGET_ENTRY_METHOD = p_project_bem ;
45 End if ;
46
47
48
49 IF p_project_bem IS NULL then
50 l_bem_valid := 'N' ;
51 END IF ;
52
53 If l_start_date_active > SYSDATE and l_end_date_active < SYSDATE then
54 l_bem_valid := 'N' ;
55 end if ;
56
57 If l_entry_level_code <> 'P' OR l_burdened_cost_flag <> 'Y' then
58 l_bem_valid := 'N' ;
59 end if ;
60
61 IF l_categorization_code <> 'N' and p_res_categorized = 'N' Then
62 l_bem_valid := 'N' ;
63 END IF ;
64
65 IF NVL(l_categorization_code,'N') = 'N' and p_res_categorized = 'Y' Then
66 l_bem_valid := 'N' ;
67 END IF ;
68
69 If p_res_categorized = 'Y' and l_bem_valid = 'N' then
70 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.validate_profile_bem - Caterozied and Invlid BEM ';
71 gms_error_pkg.gms_message(x_err_name => 'GMS_PROJ_BUDGET_SUM_CHANGE_CAT',
72 x_exec_type => 'C', -- for concurrent process
73 x_err_code => x_err_code,
74 x_err_buff => x_err_stage);
75
76 gms_error_pkg.gms_output(x_output => x_err_stage);
77
78 p_err_msg_code := 'F' ;
79 end if ;
80
81 If p_res_categorized = 'N' and l_bem_valid = 'N' then
82 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.validate_profile_bem - Uncaterozied and Invlid BEM ';
83 gms_error_pkg.gms_message(x_err_name => 'GMS_PROJ_BUDGET_SUM_CHAN_UNCAT',
84 x_exec_type => 'C', -- for concurrent process
85 x_err_code => x_err_code,
86 x_err_buff => x_err_stage);
87 gms_error_pkg.gms_output(x_output => x_err_stage);
88
89 p_err_msg_code := 'F' ;
90 end if ;
91
92
93 end ;
94
95 -- ------------------------------------------------------------
96 PROCEDURE GMS_SUMMARIZE_BUDGETS (x_project_id NUMBER
97 ,x_return_status OUT NOCOPY VARCHAR2
98 ,x_err_stage OUT NOCOPY VARCHAR2) IS
99
100 l_bem_count number ;
101 l_res_list_count number ;
102 l_award_count number ;
103 l_res_categorized VARCHAR2(1) ;
104 l_validate_profile VARCHAR2(1) := 'N' ;
105 l_time_phased_type_code VARCHAR2(1) ;
106 l_res_list_name pa_resource_lists.name%type ;
107 x_err_code varchar2(1);
108
109 BEGIN
110 x_return_status := 'S';
111 G_pa_res_list_id_none := NULL ;
112 G_pa_res_list_id := NULL ;
113
114 select count(distinct budget_entry_method_code) ,
115 count(distinct resource_list_id ), count(distinct award_id)
116 into l_bem_count, l_res_list_count, l_award_count
117 from GMS_BUDGET_VERSIONS
118 where budget_status_code = 'B'
119 and current_flag = 'Y'
120 and project_id = x_project_id ;
121 IF L_DEBUG = 'Y' THEN
122 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - l_bem_count : '||l_bem_count ,'C');
123 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - l_res_list_count : '||l_res_list_count ,'C');
124 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - l_award_count : '||l_award_count ,'C');
125 END IF;
126
127 G_multi_funding := 'N' ;
128 IF l_award_count > 1 THEN
129 G_multi_funding := 'Y';
130 END IF ;
131
132
133 -- ===================================================================================
134 -- When multiple award budgets are using the DIFFERENT BUDGET ENTRY METHODS and --
135 -- DIFFERENT RESOURCE LIST then the project budget would be summarized at the
136 -- Project entry level and none resource.
137 -- ===================================================================================
138 l_validate_profile := 'N' ;
139 L_res_categorized := 'N' ;
140
141 IF l_bem_count >= 1 and l_res_list_count > 1 then
142 G_project_bem := fnd_profile.value('GMS_PROJECT_BEM_UNCATEGORIZED');
143 if G_project_bem IS NOT NULL then
144 l_res_categorized := 'N' ;
145 l_validate_profile := 'Y' ;
146 else
147 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.GMS_SUMMARIZE_BUDGETS - UnCategorized Profile not Defined';
148 gms_error_pkg.gms_message(x_err_name => 'GMS_PROJ_BUDGET_SUM_CHAN_UNCAT',
149 x_exec_type => 'C', -- for concurrent process
150 x_err_code => x_err_code,
151 x_err_buff => x_err_stage);
152
153 gms_error_pkg.gms_output(x_output => x_err_stage);
154
155 /* gms_error_pkg.gms_message( x_err_name => 'GMS_PROJ_BUDGET_SUM_CHAN_UNCAT',
156 x_err_code => x_err_code,
157 x_err_buff => x_err_stage); */
158 x_return_status := 'F' ;
159 end if ;
160
161 End if ;
162
163 If l_bem_count >= 1 and l_res_list_count = 1 then
164
165 select distinct rl.name
166 into l_res_list_name
167 from GMS_BUDGET_VERSIONS gb , pa_resource_lists rl
168 where gb.resource_list_id = rl.resource_list_id
169 and budget_status_code = 'B'
170 and current_flag = 'Y'
171 and project_id = x_project_id ;
172
173 end if;
174
175 IF l_bem_count > 1 and l_res_list_count = 1 and l_res_list_name = 'None' then
176 G_project_bem := fnd_profile.value('GMS_PROJECT_BEM_UNCATEGORIZED');
177 l_res_categorized := 'N' ;
178 l_validate_profile := 'Y' ;
179 END IF ;
180
181 IF l_bem_count > 1 and l_res_list_count = 1 and l_res_list_name <> 'None' then
182 G_project_bem := fnd_profile.value('GMS_PROJECT_BEM_CATEGORIZED');
183 L_res_categorized := 'Y' ;
184 l_validate_profile := 'Y' ;
185 END IF ;
186
187
188 -- ===================================================================================
189 -- When multiple award budgets are using the SAME BUDGET ENTRY METHODS and --
190 -- SAME RESOURCE LIST then the project budget would be summarized using the
191 -- same budget entry method and same resource list provided that date range DO NOT overlap.
192 -- ===================================================================================
193
194 IF l_bem_count = 1 and l_res_list_count = 1 and l_award_count > 1 THEN
195 select distinct time_phased_type_code -- Added distinct for bug : 5750106
196 into l_time_phased_type_code
197 from GMS_BUDGET_VERSIONS bv, pa_budget_entry_methods bem
198 where bv.budget_entry_method_code = bem.budget_entry_method_code
199 and bv.project_id = x_project_id ;
200
201 IF l_time_phased_type_code = 'R' and l_res_list_name = 'None' then
202 G_project_bem := fnd_profile.value('GMS_PROJECT_BEM_UNCATEGORIZED');
203 l_res_categorized := 'N' ;
204 l_validate_profile := 'Y' ;
205 END IF ;
206
207 If l_time_phased_type_code = 'R' and l_res_list_name <> 'None' then
208
209 IF L_DEBUG = 'Y' THEN
210 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Setting CAT profile : ','C');
211 end if ;
212 G_project_bem := fnd_profile.value('GMS_PROJECT_BEM_CATEGORIZED');
213 l_res_categorized := 'Y' ;
214 l_validate_profile := 'Y' ;
215
216 END IF ;
217
218 If NVL(l_time_phased_type_code,'N') <> 'R' THEN
219 G_project_bem := NULL ;
220 end if ;
221 End if ; -- end if for IF l_bem_count = 1 and l_res_list_count = 1 and l_award_count > 1 THEN
222
223
224 IF l_res_categorized = 'N' and l_validate_profile = 'Y' and G_project_bem is NULL THEN
225
226 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.GMS_SUMMARIZE_BUDGETS - UnCategorized Profile not Defined';
227 gms_error_pkg.gms_message(x_err_name => 'GMS_PROJ_BUDGET_SUM_CHAN_UNCAT',
228 x_exec_type => 'C', -- for concurrent process
229 x_err_code => x_err_code,
230 x_err_buff => x_err_stage);
231
232 gms_error_pkg.gms_output(x_output => x_err_stage);
233
234 x_return_status := 'F' ;
235 End if ;
236
237 IF l_res_categorized = 'Y' and l_validate_profile = 'Y' and G_project_bem is NULL THEN
238
239 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.GMS_SUMMARIZE_BUDGETS - Categorized Profile not Defined';
240 gms_error_pkg.gms_message(x_err_name => 'GMS_PROJ_BUDGET_SUM_CHANGE_CAT',
241 x_exec_type => 'C', -- for concurrent process
242 x_err_code => x_err_code,
243 x_err_buff => x_err_stage);
244
245 gms_error_pkg.gms_output(x_output => x_err_stage);
246
247 x_return_status := 'F' ;
248 END IF ;
249
250 If l_validate_profile = 'Y' then
251 Validate_Profile_Bem (G_project_bem , l_res_categorized , x_err_code );
252 If x_err_code = 'F' then
253 x_return_status := 'F' ;
254 end if ;
255 End if ;
256
257 IF x_return_status = 'F' THEN
258 return ;
259 END IF ;
260
261 IF l_res_categorized = 'Y' THEN
262 select distinct resource_list_id
263 into G_pa_res_list_id
264 from GMS_BUDGET_VERSIONS
265 where budget_status_code = 'B'
266 and current_flag = 'Y'
267 and project_id = x_project_id ;
268 IF L_DEBUG = 'Y' THEN
269 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - G_pa_res_list_id : '|| G_pa_res_list_id,'C');
270 end if ;
271 END IF ;
272
273 -- Get the resource_list_id from pa_resource_list defined for a BG
274 -- if resource lists are different in BEMs.
275
276 If l_res_categorized = 'N' then
277 select resource_list_id
278 into G_pa_res_list_id_none
279 from pa_resource_lists prl , pa_implementations pai
280 where prl.business_group_id = pai.business_group_id
281 and prl.uncategorized_flag = 'Y'
282 and NVL(prl.migration_code ,'M') = 'M' ;
283 END if ;
284
285
286 end GMS_SUMMARIZE_BUDGETS;
287 -- ------------------------------------------------------------
288 Function budget_dates_overlap(x_project_id NUMBER) RETURN BOOLEAN is
289
290 cursor get_curr_date_range_csr (p_project_id in NUMBER, p_resource_list_member_id IN NUMBER )
291 is
292 select distinct gbl.start_date
293 , gbl.end_date
294 from gms_budget_versions gbv
295 , gms_resource_assignments gra
296 , gms_budget_lines gbl
297 where gbv.budget_version_id = gra.budget_version_id
298 and gra.resource_assignment_id = gbl.resource_assignment_id
299 and gbv.project_id = p_project_id
300 and gra.resource_list_member_id = p_resource_list_member_id;
301
302 cursor get_other_date_range_csr ( p_project_id in NUMBER)
303 is
304 select distinct gra.resource_list_member_id , gbl.start_date
305 , gbl.end_date
306 from gms_budget_versions gbv
307 , gms_resource_assignments gra
308 , gms_budget_lines gbl
309 where gbv.budget_version_id = gra.budget_version_id
310 and gra.resource_assignment_id = gbl.resource_assignment_id
311 and gbv.project_id = p_project_id
312 and gbv.current_flag = 'Y'
313 order by gra.resource_list_member_id ;-- , award_id ;
314
315 l_award_count number ;
316 l_resource_list_member_id NUMBER ;
317 Begin
318 IF L_DEBUG = 'Y' THEN
319 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....'|| 'Begin of get_other_date_range_csr','C');
320 END IF;
321
322 select count(award_id)
323 into l_award_count
324 from GMS_BUDGET_VERSIONS
325 where budget_status_code = 'B'
326 and current_flag = 'Y'
327 and project_id = x_project_id;
328
329 if l_award_count = 1 then
330
331 IF L_DEBUG = 'Y' THEN
332 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....'||'Project is NOT Multi Funded','C');
333 END IF;
334 return FALSE ;
335
336 end if;
337
338 IF L_DEBUG = 'Y' THEN
339 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....'||'Project is Multi Funded','C');
340 END IF;
341
342 FOR other_date_range_rec IN get_other_date_range_csr ( p_project_id => x_project_id )
343 LOOP
344 IF L_DEBUG = 'Y' THEN
345 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....11','C');
346 END IF;
347
348 l_resource_list_member_id := other_date_range_rec.resource_list_member_id ;
349
350 FOR curr_date_range_rec IN get_curr_date_range_csr (p_project_id => x_project_id ,
351 p_resource_list_member_id => l_resource_list_member_id)
352 LOOP
353 IF L_DEBUG = 'Y' THEN
354 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....22','C');
355 END IF;
356
357
358 if (curr_date_range_rec.start_date < other_date_range_rec.start_date
359 and curr_date_range_rec.end_date < other_date_range_rec.start_date) OR
360 (curr_date_range_rec.start_date > other_date_range_rec.end_date
361 and curr_date_range_rec.end_date > other_date_range_rec.end_date) OR
362 (curr_date_range_rec.start_date = other_date_range_rec.start_date
363 and curr_date_range_rec.end_date = other_date_range_rec.end_date) then
364
365 IF L_DEBUG = 'Y' THEN
366 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....'||'NO overlapping budget periods','C');
367 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..curr_date_range_rec.start_date '||curr_date_range_rec.start_date,'C');
368 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..curr_date_range_rec.end_date '||curr_date_range_rec.end_date,'C');
369 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..other_date_range_rec.start_date '||other_date_range_rec.start_date,'C');
370 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..other_date_range_rec.end_date '||other_date_range_rec.end_date,'C') ;
371 END IF;
372
373 NULL; --i.e Continue with next record.
374
375
376 ELSE -- Dates overlap
377 IF L_DEBUG = 'Y' THEN
378 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..curr_date_range_rec.start_date '||curr_date_range_rec.start_date,'C');
379 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..curr_date_range_rec.end_date '||curr_date_range_rec.end_date,'C');
380 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..other_date_range_rec.start_date '||other_date_range_rec.start_date,'C');
381 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap..other_date_range_rec.end_date '||other_date_range_rec.end_date,'C') ;
382 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....'||'Budget periods overlap...','C');
383 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....Overlap RLMI is'|| to_char(other_date_range_rec.resource_list_member_id),'C');
384 END IF;
385
386 RETURN TRUE;
387 end if ;
388
389 END LOOP ;
390 END LOOP ;
391
392 RETURN FALSE ; -- It means there is no overlapping period for any budgetlines for this project.
393
394 IF L_DEBUG = 'Y' THEN
395 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap.....'||' OUTSIDE THE LOOP','C');
396 end if;
397 exception
398
399 when others then
400
401 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.exception ' || SQLCODE, 'C');
402 raise;
403
404 End ;
405 -- ------------------------------------------------------------
406
407 PROCEDURE get_resource_list_id ( x_project_id NUMBER
408 ,x_budget_entry_method_code OUT NOCOPY VARCHAR2
409 ,x_resource_list_id OUT NOCOPY NUMBER
410 ,x_return_status OUT NOCOPY VARCHAR2 -- Bug 2587078
411 ,x_err_stage OUT NOCOPY VARCHAR2)
412 IS
413 x_err_code NUMBER(15); -- Bug 2587078
414 l_time_phased_type_code VARCHAR2(1);
415 BEGIN
416
417 IF L_DEBUG = 'Y' THEN
418 gms_error_pkg.gms_debug('*** Start of GMS_SUMMARIZE_BUDGETS.GET_RESOURCE_LIST_ID ***','C');
419 END IF;
420
421 select distinct gbv.resource_list_id -- Added distinct for Bug:2254944
422 , gbv.budget_entry_method_code , bem.time_phased_type_code -- Added time_phase_type_code for bug: 5750106
423 into x_resource_list_id
424 , x_budget_entry_method_code,l_time_phased_type_code
425 from gms_budget_versions gbv, pa_budget_entry_methods bem
426 where gbv.project_id = x_project_id
427 and gbv.budget_entry_method_code = bem.budget_entry_method_code -- Added for bug 5750106
428 and gbv.current_flag = 'Y';
429 -- and rownum < 2; -- Commented out NOCOPY for Bug:2254944
430
431 -- Added if condition for bug : 5750106
432 If l_time_phased_type_code = 'R' then
433 IF L_DEBUG = 'Y' THEN
434 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap... Time Phase code '|| l_time_phased_type_code,'C');
435 END IF;
436 if budget_dates_overlap(x_project_id) then
437 IF L_DEBUG = 'Y' THEN
438 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap... DAtes overlap TRUE','C');
439 END IF;
440 x_return_status := 'U'; -- Retrieve the BEM from profiles
441 else
442 IF L_DEBUG = 'Y' THEN
443 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap... DAtes overlap FALSE','C');
444 END IF;
445 x_return_status := 'S'; -- If dates don't overlap follow the old code line
446 end if;
447 else
448 IF L_DEBUG = 'Y' THEN
449 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.Budget_dates_overlap... DAtes overlap FALSE..FASLSE','C');
450 END IF;
451 x_return_status := 'S'; -- Bug 2587078
452
453 end if ;
454
455
456 IF L_DEBUG = 'Y' THEN
457 gms_error_pkg.gms_debug('*** End of GMS_SUMMARIZE_BUDGETS.GET_RESOURCE_LIST_ID ***','C');
458 END IF;
459
460 EXCEPTION
461 -- Modified below code for bug 2587078
462 WHEN NO_DATA_FOUND THEN
463 x_return_status := 'E';
464 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.GET_RESOURCE_LIST_ID- In NO_DATA_FOUND exception';
465 gms_error_pkg.gms_message( x_err_name => 'GMS_RESOURCE_LIST_ID_NOT_FOUND',
466 x_err_code => x_err_code,
467 x_err_buff => x_err_stage);
468 fnd_msg_pub.add;
469 RAISE FND_API.G_EXC_ERROR;
470
471 WHEN TOO_MANY_ROWS then
472 x_return_status := 'U';
473
474 WHEN others THEN
475 x_return_status := 'U';
476 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.GET_RESOURCE_LIST_ID- In others exception';
477 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
478 x_token_name1 => 'SQLCODE',
479 x_token_val1 => sqlcode,
480 x_token_name2 => 'SQLERRM',
481 x_token_val2 => sqlerrm,
482 x_err_code => x_err_code,
483 x_err_buff => x_err_stage);
484 fnd_msg_pub.add;
485 RAISE FND_API.G_EXC_ERROR;
486 -- End of changes for bug 2587078
487 END get_resource_list_id;
488 ----------------------------------------------------------------------------------------------
489 -- Bug 2587078 : Modified parameter name from x_err_code to x_return_status
490 -- to bring the code in consistency with existing grants code
491 -- which uses x_err_code for tracking errors.
492
493 FUNCTION draft_budget_exists( x_project_id NUMBER
494 ,x_return_status OUT NOCOPY VARCHAR2 -- Bug 2587078
495 ,x_err_stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN
496 IS
497 x_err_code NUMBER(15); -- Bug 2587078
498 draft_budget_check NUMBER(15) := 0;
499
500 BEGIN
501 IF L_DEBUG = 'Y' THEN
502 gms_error_pkg.gms_debug('*** Start of GMS_SUMMARIZE_BUDGETS.DRAFT_BUDGET_EXISTS ***','C');
503 END IF;
504
505 Select 1
506 into draft_budget_check
507 from pa_budget_versions
508 where project_id = x_project_id
509 and budget_type_code = 'AC'
510 and budget_status_code in ('W', 'S');
511
512 x_return_status := 'S'; -- Bug 2587078
513 RETURN TRUE;
514
515 IF L_DEBUG = 'Y' THEN
516 gms_error_pkg.gms_debug('*** End of GMS_SUMMARIZE_BUDGETS.DRAFT_BUDGET_EXISTS ***','C');
517 END IF;
518
519 EXCEPTION
520 -- Modified below code for bug 2587078
521 WHEN NO_DATA_FOUND THEN
522 x_return_status := 'S';
523 RETURN FALSE;
524 WHEN TOO_MANY_ROWS THEN
525 x_return_status := 'E';
526 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.DRAFT_BUDGET_EXISTS- In TOO_MANY_ROWS exception';
527 gms_error_pkg.gms_message( x_err_name => 'GMS_DUP_DRAFT_SUB_BUDGET',
528 x_err_code => x_err_code,
529 x_err_buff => x_err_stage);
530 fnd_msg_pub.add;
531 RETURN FALSE;
532 WHEN OTHERS THEN
533 x_return_status := 'U';
534 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.DRAFT_BUDGET_EXISTS- In others exception';
535 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
536 x_token_name1 => 'SQLCODE',
537 x_token_val1 => sqlcode,
538 x_token_name2 => 'SQLERRM',
539 x_token_val2 => sqlerrm,
540 x_err_code => x_err_code,
541 x_err_buff => x_err_stage);
542 fnd_msg_pub.add;
543 RETURN FALSE;
544 -- End of changes for bug 2587078
545 END draft_budget_exists;
546 -----------------------------------------------------------------------------------------------------------
547 -- Bug 2386041
548 PROCEDURE set_global_info
549 IS
550 x_msg_count NUMBER;
551 x_msg_data VARCHAR2(2000);
552 x_return_status VARCHAR2(2000);
553 message_temp VARCHAR2(2000);
554 BEGIN
555 --For Bug 4654211 :Operating unit Parameter added
556 pa_interface_utils_pub.set_global_info(p_api_version_number => 1.0,
557 p_responsibility_id => FND_GLOBAL.resp_id,
558 p_user_id => FND_GLOBAL.user_id,
559 p_resp_appl_id => FND_GLOBAL.resp_appl_id,
560 p_operating_unit_id => PA_MOAC_UTILS.get_current_org_id,
561 p_msg_count => x_msg_count,
562 p_msg_data => x_msg_data,
563 p_return_status => x_return_status);
564 IF x_return_status <>'S' THEN
565 FOR i IN 1.. x_msg_count LOOP
566 message_temp := gms_messages.get_message(X_Index => i, X_encoded=> 'T');
567 fnd_message.set_encoded(message_temp);
568 fnd_message.raise_error;
569 END LOOP;
570 fnd_message.set_name('GMS','GMS_PA_ENV_NOT_SET');
571 fnd_message.raise_error;
572 END IF;
573 END set_global_info;
574 -- Bug 2386041
575 ------------------------------------------------------------------------------------------------
576
577
578 PROCEDURE summarize_baselined_versions(x_project_id NUMBER
579 ,x_time_phased_type_code VARCHAR2
580 ,x_app_short_name OUT NOCOPY VARCHAR2
581 ,RETCODE OUT NOCOPY VARCHAR2
582 ,ERRBUF OUT NOCOPY VARCHAR2)
583 IS
584 -- cursor changed to receive task_id and flag to rollup to resource group.
585 -- Bug 3532920.
586 cursor C1(p_task_id number, p_res_grp varchar2) IS
587 select gbl.period_name
588 , decode(p_res_grp,
589 'Y', nvl(prl.parent_member_id, gra.resource_list_member_id),
590 gra.resource_list_member_id) resource_list_member_id
591 , gra.task_id
592 , sum(gbl.raw_cost) raw_cost
593 , sum(gbl.burdened_cost) burdened_cost
594 , sum(gbl.quantity) quantity
595 from gms_budget_versions gbv
596 , gms_resource_assignments gra
597 , gms_budget_lines gbl
598 , pa_resource_list_members prl --> Bug 2935048
599 where gbv.project_id = x_project_id
600 and gbv.budget_version_id = gra.budget_version_id
601 and gra.resource_assignment_id = gbl.resource_assignment_id
602 and gbv.current_flag = 'Y'
603 and gbv.resource_list_id = prl.resource_list_id
604 and gra.resource_list_member_id = prl.resource_list_member_id
605 and gra.task_id = p_task_id
606 group by gbl.period_name,
607 decode(p_res_grp,
608 'Y', nvl(prl.parent_member_id, gra.resource_list_member_id),
609 gra.resource_list_member_id),
610 gra.task_id;
611
612
613 -- cursor changed to receive task_id and flag to rollup to resource group.
614 -- Bug 3532920.
615 cursor C2(p_task_id number, p_res_grp varchar2) IS
616 select gbl.start_date
617 , gbl.end_date
618 , decode(p_res_grp,
619 'Y', nvl(prl.parent_member_id, gra.resource_list_member_id),
620 gra.resource_list_member_id) resource_list_member_id
621 , gra.task_id
622 , sum(gbl.raw_cost) raw_cost
623 , sum(gbl.burdened_cost) burdened_cost
624 , sum(gbl.quantity) quantity
625 from gms_budget_versions gbv
626 , gms_resource_assignments gra
627 , gms_budget_lines gbl
628 , pa_resource_list_members prl --> Bug 2935048
629 where gbv.project_id = x_project_id
630 and gbv.budget_version_id = gra.budget_version_id
631 and gra.resource_assignment_id = gbl.resource_assignment_id
632 and gbv.current_flag = 'Y'
633 and gbv.resource_list_id = prl.resource_list_id
634 and gra.resource_list_member_id = prl.resource_list_member_id
635 and gra.task_id = p_task_id
636 group by gbl.start_date, gbl.end_date,
637 decode(p_res_grp,
638 'Y', nvl(prl.parent_member_id, gra.resource_list_member_id),
639 gra.resource_list_member_id),
640 gra.task_id;
641
642
643 -- cursor changed to receive task_id and flag to rollup to resource group.
644 -- Bug 3532920.
645 cursor C3(p_task_id number, p_res_grp varchar2) IS
646 select decode(p_res_grp,
647 'Y', nvl(prl.parent_member_id, gra.resource_list_member_id),
648 gra.resource_list_member_id) resource_list_member_id
649 , gra.task_id
650 , sum(gbl.raw_cost) raw_cost
651 , sum(gbl.burdened_cost) burdened_cost
652 , sum(gbl.quantity) quantity
653 from gms_budget_versions gbv
654 , gms_resource_assignments gra
655 , gms_budget_lines gbl
656 , pa_resource_list_members prl --> Bug 2935048
657 where gbv.project_id = x_project_id
658 and gbv.budget_version_id = gra.budget_version_id
659 and gra.resource_assignment_id = gbl.resource_assignment_id
660 and gbv.current_flag = 'Y'
661 and gbv.resource_list_id = prl.resource_list_id
662 and gra.resource_list_member_id = prl.resource_list_member_id
663 and gra.task_id = p_task_id
664 group by decode(p_res_grp,
665 'Y', nvl(prl.parent_member_id, gra.resource_list_member_id),
666 gra.resource_list_member_id),
667 gra.task_id;
668
669 cursor task_cur is
670 select distinct gra.task_id
671 from gms_resource_assignments gra,
672 gms_budget_versions gbv
673 where gra.project_id = x_project_id
674 and gra.project_id = gbv.project_id
675 and gbv.current_flag = 'Y'
676 and gbv.budget_status_code = 'B';
677
678 -- x_err_code VARCHAR2(1) := NULL;
679 x_err_code NUMBER;
680 x_err_stage VARCHAR2(200) := NULL;
681 x_return_status VARCHAR2(1);
682
683 i BINARY_INTEGER:=0;
684
685 x_budget_lines_in_rec pa_budget_pub.budget_line_in_rec_type;
686 x_budget_lines_in_tbl pa_budget_pub.budget_line_in_tbl_type;
687 x_budget_lines_out_tbl pa_budget_pub.budget_line_out_tbl_type;
688
689 x_msg_data VARCHAR2(2000);
690 x_msg_count VARCHAR2(2000);
691 x_text VARCHAR2(2000);
692
693 x_resource_list_id NUMBER(15) := NULL;
694 x_entry_method_code VARCHAR2(30) := NULL;
695 x_entry_level_code VARCHAR2(30); -- Added for Bug:2592747
696 x_workflow_started VARCHAR2(1); -- required for baselining
697
698 x_project_start_date DATE;
699 x_project_end_date DATE;
700 x_task_start_date DATE; -- Added for bug 3372853
701 x_task_end_date DATE; -- Added for bug 3372853
702
703 v_task_id NUMBER;
704 v_res_grp varchar2(1);
705
706 l_set_profile_success1 BOOLEAN; -- bug 3770971
707 l_set_profile_success2 BOOLEAN; -- bug 3770971
708 l_user_profile_value1 VARCHAR2(30); -- bug 8214030
709 l_user_profile_value2 VARCHAR2(30); -- bug 8214030
710 l_budget_lines_in pa_budget_pub.budget_line_in_tbl_type; --Added for the Bug:10155464
711 l_budget_lines_in2 pa_budget_pub.budget_line_in_tbl_type; --Added for the Bug:10155464
712 l_variable NUMBER; --Added for the Bug:10155464
713 l_increment_variable NUMBER:=0; --Added for the Bug:10155464
714
715 -- This function returns 'Y' if the budget has both resource and resource group level budgeting.
716 -- Value returned is 'Y' if :
717 -- 1. Budgeting is at resource group level only OR
718 -- 2. Budgeting is at both resource and resource group level.
719 -- otherwise returns 'N', budgeting is at resource level only.
720 -- Bug 2935048.
721 --
722 -- Validation needs to happen at the task level if budget entry level
723 -- is task. Incase the budgeting level is Project, then task_id = 0.
724 -- Bug 3532920.
725
726 function check_resource_budget_levels(x_task_id in number) return varchar2 is
727 v_mixed_level_budget varchar2(1);
728 begin
729 select 'Y'
730 into v_mixed_level_budget
731 from dual
732 where exists (select '1'
733 from pa_resource_list_members prl,
734 gms_resource_assignments gra,
735 gms_budget_versions gbv
736 where prl.resource_list_id = x_resource_list_id
737 and prl.resource_list_member_id = gra.resource_list_member_id
738 and gbv.budget_version_id = gra.budget_version_id
739 and gbv.current_flag = 'Y'
740 and prl.parent_member_id is null
741 and gra.project_id = x_project_id
742 and gra.task_id = x_task_id);
743
744 return 'Y';
745 exception
746 when no_data_found then
747 return 'N';
748 end check_resource_budget_levels;
749
750 -- =================================================================================
751 -- New procedure introduced for GMS enhancements
752 -- =================================================================================
753 Procedure Project_sum_high_level ( x_project_id NUMBER ) IS
754 cursor C3_none_res IS
755 select prl.resource_list_member_id ,
756 gbv.project_id ,
757 sum(gbl.raw_cost) raw_cost
758 , sum(gbl.burdened_cost) burdened_cost
759 , sum(gbl.quantity) quantity
760 from gms_budget_versions gbv
761 , gms_resource_assignments gra
762 , gms_budget_lines gbl
763 , pa_resource_list_members prl
764 where gbv.project_id = x_project_id
765 and gbv.budget_version_id = gra.budget_version_id
766 and gra.resource_assignment_id = gbl.resource_assignment_id
767 and gbv.current_flag = 'Y'
768 and G_pa_res_list_id_none = prl.resource_list_id
769 and gra.resource_list_member_id = prl.resource_list_member_id
770 group by prl.resource_list_member_id , gbv.project_id;
771 -- group by gbv.project_id ;
772
773 cursor C3_res (p_pa_res_list_id VARCHAR2 ) IS
774 select
775 gra.resource_list_member_id ,
776 gbv.project_id
777 , sum(gbl.raw_cost) raw_cost
778 , sum(gbl.burdened_cost) burdened_cost
779 , sum(gbl.quantity) quantity
780 from gms_budget_versions gbv
781 , gms_resource_assignments gra
782 , gms_budget_lines gbl
783 , pa_resource_list_members prl
784 where gbv.project_id = x_project_id
785 and gbv.budget_version_id = gra.budget_version_id
786 and gra.resource_assignment_id = gbl.resource_assignment_id
787 and gbv.current_flag = 'Y'
788 and gbv.resource_list_id = prl.resource_list_id
789 and gra.resource_list_member_id = prl.resource_list_member_id
790 group by gra.resource_list_member_id, gbv.project_id ;
791
792 --Added cursor for the bug 11891595
793 cursor get_other_date_range_csr ( p_project_id in NUMBER)
794 is
795 select distinct gra.resource_list_member_id , gbl.start_date
796 , gbl.end_date
797 from gms_budget_versions gbv
798 , gms_resource_assignments gra
799 , gms_budget_lines gbl
800 where gbv.budget_version_id = gra.budget_version_id
801 and gra.resource_assignment_id = gbl.resource_assignment_id
802 and gbv.project_id = p_project_id
803 and gbv.current_flag = 'Y'
804 order by gra.resource_list_member_id ;
805
806 --Added cursor for the bug 11891595
807 cursor get_curr_date_range_csr (p_project_id in NUMBER, p_resource_list_member_id IN NUMBER )
808 is
809 select distinct gbl.start_date
810 , gbl.end_date
811 from gms_budget_versions gbv
812 , gms_resource_assignments gra
813 , gms_budget_lines gbl
814 where gbv.budget_version_id = gra.budget_version_id
815 and gra.resource_assignment_id = gbl.resource_assignment_id
816 and gbv.project_id = p_project_id
817 and gra.resource_list_member_id = p_resource_list_member_id;
818
819 x_project_end_date DATE ;
820 l_resource_list_member_id NUMBER ; --Added for the bug 11891595
821
822 Begin
823 SELECT start_date,
824 completion_date
825 INTO x_project_start_date,
826 x_project_end_date
827 FROM pa_projects_all
828 WHERE project_id = x_project_id;
829
830 -- Added the following below code for Bug:11891595 (start)
831
832 if x_project_end_date is null then
833 FOR other_date_range_rec IN get_other_date_range_csr ( p_project_id => x_project_id )
834 LOOP
835
836 l_resource_list_member_id := other_date_range_rec.resource_list_member_id ;
837
838 FOR curr_date_range_rec IN get_curr_date_range_csr (p_project_id => x_project_id ,
839 p_resource_list_member_id => l_resource_list_member_id)
840 LOOP
841
842 if NOT (curr_date_range_rec.start_date < other_date_range_rec.start_date
843 and curr_date_range_rec.end_date < other_date_range_rec.start_date) OR
844 (curr_date_range_rec.start_date > other_date_range_rec.end_date
845 and curr_date_range_rec.end_date > other_date_range_rec.end_date) OR
846 (curr_date_range_rec.start_date = other_date_range_rec.start_date
847 and curr_date_range_rec.end_date = other_date_range_rec.end_date) then
848
849
850 if curr_date_range_rec.end_date > other_date_range_rec.end_date then
851 PA_BUDGET_CHECK_PVT.G_PA_END_DATE:=curr_date_range_rec.end_date; -- Changed for the bug13344742
852 else
853 PA_BUDGET_CHECK_PVT.G_PA_END_DATE:=other_date_range_rec.end_date; -- Changed for the bug13344742
854 end if;
855
856 end if ;
857
858 END LOOP ;
859
860 END LOOP ;
861 end if;
862
863 --Added code for the bug 11891595 (end)
864
865 IF G_pa_res_list_id_none is not NULL then
866 FOR rec_c3_none_res in C3_none_res LOOP
867 EXIT when C3_none_res%NOTFOUND;
868 i := i + 1;
869 x_budget_lines_in_rec.raw_cost := rec_c3_none_res.raw_cost;
870 x_budget_lines_in_rec.quantity := rec_c3_none_res.quantity;
871 x_budget_lines_in_rec.burdened_cost := rec_c3_none_res.burdened_cost;
872 x_budget_lines_in_rec.budget_start_date := x_project_start_date;
873 x_budget_lines_in_rec.budget_end_date := NVL(x_project_end_date,PA_BUDGET_CHECK_PVT.G_PA_END_DATE); -- Changed for the bug 11891595 and 13344742
874 x_budget_lines_in_rec.resource_list_member_id := rec_c3_none_res.resource_list_member_id ; -- PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ;
875 x_budget_lines_in_rec.pm_product_code := 'GMS';
876 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
877 END LOOP;
878 END IF ;
879 -- Ajay Review comments
880 IF G_pa_res_list_id is not NULL THEN
881 FOR rec_c3_res in C3_res(G_pa_res_list_id) LOOP
882 EXIT when C3_res%NOTFOUND;
883 i := i + 1;
884 x_budget_lines_in_rec.raw_cost := rec_c3_res.raw_cost;
885 x_budget_lines_in_rec.quantity := rec_c3_res.quantity;
886 x_budget_lines_in_rec.burdened_cost := rec_c3_res.burdened_cost;
887 x_budget_lines_in_rec.budget_start_date := x_project_start_date;
888 x_budget_lines_in_rec.budget_end_date := NVL(x_project_end_date,PA_BUDGET_CHECK_PVT.G_PA_END_DATE); -- Changed for the bug 11891595 and 13344742
889 x_budget_lines_in_rec.resource_list_member_id := rec_c3_res.resource_list_member_id ; --PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ;
890 x_budget_lines_in_rec.pm_product_code := 'GMS';
891 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
892 END LOOP;
893 END IF ;
894
895 End ;
896 -- ---------------------------------------------------------------------------------
897 BEGIN
898
899 IF L_DEBUG = 'Y' THEN
900 gms_error_pkg.gms_debug('*** Start of GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS ***','C');
901 END IF;
902 x_return_status := 'S'; -- Initializing return status as Success.
903
904 -- Bug 2386041
905 -- Since we are calling the PA_BUDGET_PUB utilities need to initialize some global variables
906 set_global_info;
907 -- Bug 2386041
908
909 -- Bug 3770971..need to set the profile values for "PA: Cross Project User - Update" and
910 -- "PA: Cross Project User - View". These values are lost after calling set_global_info
911 -- which calls fnd_global.apps_initialize.
912
913 /* bug 8214030 changes start*/
914 l_user_profile_value1 := fnd_profile.value_specific(
915 NAME => 'PA_SUPER_PROJECT',
916 USER_ID => fnd_global.user_id,
917 RESPONSIBILITY_ID => fnd_global.resp_id,
918 APPLICATION_ID => fnd_global.resp_appl_id);
919
920 if ((l_user_profile_value1 = 'N') OR (l_user_profile_value1 is null)) then
921
922 BEGIN
923 SELECT profile_option_value
924 INTO l_user_profile_value1
925 FROM fnd_profile_options p,
926 fnd_profile_option_values v
927 WHERE p.profile_option_name = 'PA_SUPER_PROJECT'
928 AND v.profile_option_id = p.profile_option_id
929 AND v.level_id = 10004
930 AND v.level_value = fnd_global.user_id;
931 EXCEPTION
932 WHEN no_data_found THEN
933 l_user_profile_value1 := null;
934 WHEN others THEN
935 l_user_profile_value1 := null;
936 END;
937 l_set_profile_success1 := fnd_profile.save('PA_SUPER_PROJECT', 'Y', 'USER', fnd_global.user_id);
938 end if;
939
940 l_user_profile_value2 := fnd_profile.value_specific(
941 NAME => 'PA_SUPER_PROJECT_VIEW',
942 USER_ID => fnd_global.user_id,
943 RESPONSIBILITY_ID => fnd_global.resp_id,
944 APPLICATION_ID => fnd_global.resp_appl_id);
945
946 if ((l_user_profile_value2 = 'N') OR (l_user_profile_value2 is null)) then
947 BEGIN
948 SELECT profile_option_value
949 INTO l_user_profile_value2
950 FROM fnd_profile_options p,
951 fnd_profile_option_values v
952 WHERE p.profile_option_name = 'PA_SUPER_PROJECT_VIEW'
953 AND v.profile_option_id = p.profile_option_id
954 AND v.level_id = 10004
955 AND v.level_value = fnd_global.user_id;
956 EXCEPTION
957 WHEN no_data_found THEN
958 l_user_profile_value2 := null;
959 WHEN others THEN
960 l_user_profile_value2 := null;
961 END;
962 l_set_profile_success2 := fnd_profile.save('PA_SUPER_PROJECT_VIEW', 'Y', 'USER', fnd_global.user_id);
963 end if;
964
965 /* bug 8214030 changes end*/
966 -- Bug 3770971 end. Values will be set back in gms_budget_pub after summarize_budgets is done.
967
968 -- Added for GMS enhancement 5583170
969 SELECT start_date,
970 completion_date
971 INTO x_project_start_date,
972 x_project_end_date
973 FROM pa_projects_all
974 WHERE project_id = x_project_id;
975
976
977 IF draft_budget_exists (x_project_id
978 ,x_return_status
979 ,x_err_stage) THEN
980
981 IF L_DEBUG = 'Y' THEN
982 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling pa_budget_pub.delete_draft_budget','C');
983 END IF;
984
985 pa_budget_pub.delete_draft_budget(p_api_version_number => 1.0
986 ,p_init_msg_list => 'T'
987 ,p_msg_count => x_msg_count
988 ,p_msg_data => x_err_stage
989 ,p_return_status => x_return_status
990 ,p_pm_product_code => 'GMS' -- bug 3175909
991 ,p_pa_project_id => x_project_id
992 ,p_budget_type_code => 'AC');
993
994 IF x_return_status <> 'S' THEN
995
996 x_err_code := 2;
997
998 FND_MESSAGE.PARSE_ENCODED (encoded_message => x_err_stage
999 ,app_short_name => x_app_short_name
1000 ,message_name => x_text);
1001 x_err_stage := x_text;
1002
1003 IF L_DEBUG = 'Y' THEN
1004 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; **************************','C');
1005 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; x_return_status = '||x_return_status,'C');
1006 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; x_err_stage = '||x_err_stage,'C');
1007 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; x_msg_count = '||x_msg_count,'C');
1008 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; **************************','C');
1009 END IF;
1010
1011 RAISE FND_API.G_EXC_ERROR;
1012
1013 END IF;
1014
1015 ELSE
1016 IF x_return_status <> 'S' THEN
1017 RAISE FND_API.G_EXC_ERROR;
1018 END IF;
1019 END IF;
1020
1021 IF L_DEBUG = 'Y' THEN
1022 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling get_resource_list_id','C');
1023 END IF;
1024
1025 get_resource_list_id (x_project_id
1026 ,x_entry_method_code
1027 ,x_resource_list_id
1028 ,x_return_status
1029 ,x_err_stage);
1030
1031
1032 -- ------------------------------------
1033 -- Added for GMS enhancements : 5583170
1034 -- ------------------------------------
1035 If x_return_status = 'U' then
1036 IF L_DEBUG = 'Y' THEN
1037 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS-x_return_status is U', 'C');
1038 END IF;
1039
1040 GMS_SUMMARIZE_BUDGETS (x_project_id
1041 ,x_return_status
1042 ,x_err_stage ) ;
1043 x_resource_list_id := NVL(G_pa_res_list_id,G_pa_res_list_id_none) ;
1044
1045
1046 IF L_DEBUG = 'Y' THEN
1047 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - G_project_bem : '||G_project_bem ,'C');
1048 END IF;
1049
1050 IF L_DEBUG = 'Y' THEN
1051 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - x_return_status : '||x_return_status ,'C');
1052 END IF;
1053 end if ;
1054
1055 -- We should not proceed further as G_project_bem IS NULL ( when profiles are unset and different BEM are used )
1056
1057 If G_project_bem IS NULL and x_resource_list_id IS NULL then
1058
1059 x_return_status := 'X' ; -- We pass 'X' for the return status instead of 'S' as the award summarization has not happened. We will display
1060 -- different message saying that only Budget baseline has happened.
1061 x_err_code := 2;
1062 RAISE FND_API.G_EXC_ERROR;
1063 RETURN ;
1064 end if ;
1065
1066 If G_project_bem IS NULL and x_resource_list_id IS NOT NULL then -- Added for GMS enhancements
1067 IF L_DEBUG = 'Y' THEN
1068 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - x_time_phased_type_code : '||x_time_phased_type_code,'C');
1069 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Start of date calculation logic','C');
1070 END IF;
1071
1072 i := 0;
1073
1074 OPEN task_cur;
1075 LOOP
1076 FETCH task_cur INTO v_task_id;
1077 EXIT WHEN task_cur%NOTFOUND;
1078
1079 -- GMS enhanceemnt for R12 : 5583170
1080 x_task_start_date := NULL ;
1081 x_task_end_date := NULL ;
1082 IF v_task_id <> 0 THEN
1083 SELECT nvl(start_date,x_project_start_date),
1084 nvl(completion_date,x_project_end_date)
1085 INTO x_task_start_date,
1086 x_task_end_date /* Changed for bug 3372853 */
1087 FROM pa_tasks
1088 WHERE task_id = v_task_id;
1089 END IF ;
1090
1091 v_res_grp := check_resource_budget_levels(v_task_id);
1092
1093 if x_time_phased_type_code IN ('G', 'P') then
1094
1095 FOR rec_c1 in C1(v_task_id, v_res_grp)
1096 LOOP
1097 EXIT when C1%NOTFOUND;
1098 i := i + 1;
1099 x_budget_lines_in_rec.resource_list_member_id := rec_c1.resource_list_member_id;
1100 x_budget_lines_in_rec.raw_cost := rec_c1.raw_cost;
1101 x_budget_lines_in_rec.quantity := rec_c1.quantity;
1102 x_budget_lines_in_rec.burdened_cost := rec_c1.burdened_cost;
1103 x_budget_lines_in_rec.period_name := rec_c1.period_name;
1104 x_budget_lines_in_rec.pa_task_id := rec_c1.task_id;
1105 x_budget_lines_in_rec.resource_list_member_id := rec_c1.resource_list_member_id;
1106 x_budget_lines_in_rec.pm_product_code := 'GMS'; -- bug 3175909
1107 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
1108 END LOOP;
1109
1110 -- elsif x_time_phased_type_code IN ('R', 'N') then -- Bug 2466716
1111 elsif x_time_phased_type_code = 'R' then
1112
1113 FOR rec_c2 in C2(v_task_id, v_res_grp)
1114 LOOP
1115 EXIT when C2%NOTFOUND;
1116 i := i + 1;
1117 x_budget_lines_in_rec.resource_list_member_id := rec_c2.resource_list_member_id;
1118 x_budget_lines_in_rec.raw_cost := rec_c2.raw_cost;
1119 x_budget_lines_in_rec.quantity := rec_c2.quantity;
1120 x_budget_lines_in_rec.burdened_cost := rec_c2.burdened_cost;
1121 -- GMS enahancement : 5583170
1122 IF nvl(x_task_start_date, x_project_start_date) > rec_c2.start_date THEN
1123 x_budget_lines_in_rec.budget_start_date := nvl(x_task_start_date, x_project_start_date);
1124 ELSE
1125 x_budget_lines_in_rec.budget_start_date := rec_c2.start_date;
1126 end IF ;
1127
1128 IF nvl(x_task_end_date, x_project_end_date) < rec_c2.end_date THEN
1129 x_budget_lines_in_rec.budget_end_date := nvl(x_task_end_date, x_project_end_date);
1130 ELSE
1131 x_budget_lines_in_rec.budget_end_date := rec_c2.end_date;
1132 end IF ;
1133
1134 -- x_budget_lines_in_rec.budget_start_date := rec_c2.start_date;
1135 -- x_budget_lines_in_rec.budget_end_date := rec_c2.end_date;
1136
1137 x_budget_lines_in_rec.pa_task_id := rec_c2.task_id;
1138 x_budget_lines_in_rec.resource_list_member_id := rec_c2.resource_list_member_id;
1139 x_budget_lines_in_rec.pm_product_code := 'GMS'; -- bug 3175909
1140 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
1141 END LOOP;
1142
1143 elsif x_time_phased_type_code = 'N' then -- Bug 2466716
1144
1145 SELECT start_date,
1146 completion_date
1147 INTO x_project_start_date,
1148 x_project_end_date
1149 FROM pa_projects_all
1150 WHERE project_id = x_project_id;
1151
1152 FOR rec_c3 in C3(v_task_id, v_res_grp)
1153 LOOP
1154 EXIT when C3%NOTFOUND;
1155 i := i + 1;
1156
1157 -- Added the following SELECT and the IF block for Bug:2592747
1158
1159 SELECT distinct entry_level_code -- Added DISTINCT for Bug:2907692
1160 INTO x_entry_level_code
1161 FROM pa_budget_entry_methods pbem,
1162 gms_budget_versions gbv
1163 WHERE gbv.budget_entry_method_code = pbem.budget_entry_method_code
1164 AND gbv.project_id = x_project_id
1165 AND gbv.budget_status_code = 'B'
1166 AND gbv.current_flag = 'Y';
1167
1168 if x_entry_level_code in ('L','M','T') then
1169
1170 SELECT nvl(start_date,x_project_start_date),
1171 nvl(completion_date,x_project_end_date)
1172 INTO x_task_start_date,
1173 x_task_end_date /* Changed for bug 3372853 */
1174 --INTO x_project_start_date,
1175 -- x_project_end_date
1176 FROM pa_tasks
1177 WHERE task_id = rec_c3.task_id;
1178
1179 /* if x_project_start_date is NULL or x_project_end_date is NULL then Changed for bug 3372853 */
1180 if x_task_start_date is NULL or x_task_end_date is NULL then
1181 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Error occurred while fetching dates';
1182 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_TASK_PROJ_DATE',
1183 x_err_code => x_err_code,
1184 x_err_buff => x_err_stage);
1185 fnd_msg_pub.add; -- Bug 2587078
1186 RAISE FND_API.G_EXC_ERROR;
1187
1188 end if;
1189
1190 end if;
1191
1192 x_budget_lines_in_rec.resource_list_member_id := rec_c3.resource_list_member_id;
1193 x_budget_lines_in_rec.raw_cost := rec_c3.raw_cost;
1194 x_budget_lines_in_rec.quantity := rec_c3.quantity;
1195 x_budget_lines_in_rec.burdened_cost := rec_c3.burdened_cost;
1196 --Bug 5489263
1197 if x_entry_level_code in ('L','M','T') then
1198 x_budget_lines_in_rec.budget_start_date := x_task_start_date;
1199 x_budget_lines_in_rec.budget_end_date := x_task_end_date;
1200 else
1201 x_budget_lines_in_rec.budget_start_date := x_project_start_date;
1202 x_budget_lines_in_rec.budget_end_date := x_project_end_date;
1203 end if;
1204 --Bug 5489263
1205
1206 /*x_budget_lines_in_rec.budget_start_date := x_project_start_date;
1207 x_budget_lines_in_rec.budget_end_date := x_project_end_date; Changed for bug 3372853 */
1208 /* x_budget_lines_in_rec.budget_start_date := x_task_start_date;
1209 x_budget_lines_in_rec.budget_end_date := x_task_end_date; Commented for bug 5489263*/
1210
1211 x_budget_lines_in_rec.pa_task_id := rec_c3.task_id;
1212 x_budget_lines_in_rec.resource_list_member_id := rec_c3.resource_list_member_id;
1213 x_budget_lines_in_rec.pm_product_code := 'GMS'; -- bug 3175909
1214 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
1215 END LOOP;
1216
1217 end if; -- time_phased_type_code if
1218
1219 END LOOP; -- task_cur;
1220
1221 CLOSE task_cur;
1222
1223 ELSIF G_project_bem IS NOT NULL and x_return_status = 'S' THEN -- ELSE for If G_project_bem is N NULL THEN
1224
1225 Project_sum_high_level (x_project_id ) ;
1226
1227 End if ;
1228
1229 IF L_DEBUG = 'Y' THEN
1230 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - End of date calculation logic','C');
1231 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling pa_budget_pub.create_draft_budget','C');
1232 END IF;
1233
1234 --Call budget API to create draft budget
1235
1236 -- GMS enhancements for R12 : 5583170
1237 If G_project_bem IS NOT NULL then
1238
1239 select budget_entry_method_code
1240 into x_entry_method_code
1241 from pa_budget_entry_methods
1242 where budget_entry_method = G_project_bem ;
1243 end if ;
1244
1245
1246 -- Added the following below code for Bug:10155464 (start)
1247
1248 IF x_time_phased_type_code NOT IN ('G', 'P') THEN
1249 l_budget_lines_in :=x_budget_lines_in_tbl;
1250 FOR i IN l_budget_lines_in.FIRST..l_budget_lines_in.LAST
1251 LOOP
1252 if l_budget_lines_in.exists(i) then
1253
1254 l_increment_variable:=l_increment_variable+1;
1255 l_variable :=i+1;
1256
1257 FOR j IN l_variable..l_budget_lines_in.LAST
1258 LOOP
1259 IF l_budget_lines_in(i).resource_list_member_id=l_budget_lines_in(j).resource_list_member_id AND
1260 l_budget_lines_in(i).budget_start_date = l_budget_lines_in(j).budget_start_date AND
1261 l_budget_lines_in(i).pa_task_id = l_budget_lines_in(j).pa_task_id THEN
1262
1263 l_budget_lines_in(i).raw_cost :=l_budget_lines_in(i).raw_cost + l_budget_lines_in(j).raw_cost;
1264 l_budget_lines_in(i).quantity :=l_budget_lines_in(i).quantity + l_budget_lines_in(j).quantity;
1265 l_budget_lines_in(i).burdened_cost :=l_budget_lines_in(i).burdened_cost + l_budget_lines_in(j).burdened_cost;
1266
1267
1268 IF l_budget_lines_in(i).budget_end_date <= l_budget_lines_in(j).budget_end_date THEN
1269 l_budget_lines_in(i).budget_end_date:=l_budget_lines_in(j).budget_end_date;
1270 END IF;
1271
1272 l_budget_lines_in.delete(j);
1273
1274 END IF;
1275 END LOOP;
1276 l_budget_lines_in2(l_increment_variable):=l_budget_lines_in(i);
1277 END IF;
1278
1279 END LOOP;
1280 x_budget_lines_in_tbl:=l_budget_lines_in2;
1281 END IF;
1282
1283 -- Added the code for Bug:10155464 (end)
1284
1285 pa_budget_pub.create_draft_budget(p_api_version_number => 1.0
1286 ,p_init_msg_list => 'T'
1287 ,p_msg_count => x_msg_count
1288 ,p_msg_data => x_err_stage
1289 ,p_return_status => x_return_status
1290 ,p_pm_product_code => 'GMS'
1291 ,p_pa_project_id => x_project_id
1292 ,p_budget_type_code => 'AC'
1293 ,p_entry_method_code => x_entry_method_code
1294 ,p_resource_list_id => x_resource_list_id
1295 ,p_budget_lines_in => x_budget_lines_in_tbl
1296 ,p_budget_lines_out => x_budget_lines_out_tbl
1297 ,p_change_reason_code => null
1298 ,p_pm_budget_reference=> null);
1299
1300
1301 IF x_return_status <>'S' THEN
1302 x_err_code := 2;
1303
1304
1305 FND_MESSAGE.PARSE_ENCODED ( encoded_message => x_err_stage
1306 ,app_short_name => x_app_short_name
1307 ,message_name => x_text);
1308 x_err_stage := x_text;
1309
1310 IF L_DEBUG = 'Y' THEN
1311 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; ************************','C');
1312 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; x_return_status = '||x_return_status,'C');
1313 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; x_msg_count = '||x_msg_count,'C');
1314 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; x_err_stage = '||x_err_stage,'C');
1315 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; ************************','C');
1316 END IF;
1317
1318 RAISE FND_API.G_EXC_ERROR;
1319
1320 END IF;
1321 -- R11i Change
1322 -- Call PA's Budget API to Baseline this budget since the Project Budget should
1323 -- automatically be baselined when the Award Budget is baselined.
1324
1325 IF L_DEBUG = 'Y' THEN
1326 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling pa_budget_pub.baseline_budget','C');
1327 END IF;
1328
1329 pa_budget_pub.baseline_budget( p_api_version_number => 1.0,
1330 p_init_msg_list => 'T',
1331 p_msg_count => x_msg_count,
1332 p_msg_data => x_err_stage,
1333 p_return_status => x_return_status,
1334 p_workflow_started => x_workflow_started,
1335 p_pm_product_code => 'GMS',
1336 p_pa_project_id => x_project_id,
1337 p_pm_project_reference => NULL,
1338 p_budget_type_code => 'AC');
1339
1340 IF L_DEBUG = 'Y' THEN
1341 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1342 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_return_status = '||x_return_status,'C');
1343 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_msg_count = '||x_msg_count,'C');
1344 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_err_stage = '||x_err_stage,'C');
1345 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1346 END IF;
1347 RETCODE := x_return_status ; -- Added for GMS enhancement for R12 : 5583170
1348
1349 IF x_return_status <>'S' THEN
1350
1351 x_err_code := 2;
1352
1353 FND_MESSAGE.PARSE_ENCODED ( encoded_message => x_err_stage
1354 ,app_short_name => x_app_short_name
1355 ,message_name => x_text);
1356 x_err_stage := x_text;
1357
1358 IF L_DEBUG = 'Y' THEN
1359 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1360 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_return_status = '||x_return_status,'C');
1361 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_msg_count = '||x_msg_count,'C');
1362 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_err_stage = '||x_err_stage,'C');
1363 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1364 END IF;
1365
1366 RAISE FND_API.G_EXC_ERROR;
1367
1368 END IF;
1369
1370 IF L_DEBUG = 'Y' THEN
1371 gms_error_pkg.gms_debug('*** End of GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSION ***','C');
1372 END IF;
1373
1374 /* bug 8214030: resetting the profile values back */
1375 if (l_set_profile_success1 = TRUE) then
1376 l_set_profile_success1 := fnd_profile.save('PA_SUPER_PROJECT', l_user_profile_value1, 'USER', fnd_global.user_id);
1377 end if;
1378 if (l_set_profile_success2 = TRUE) then
1379 l_set_profile_success2 := fnd_profile.save('PA_SUPER_PROJECT_VIEW', l_user_profile_value2, 'USER', fnd_global.user_id);
1380 end if;
1381
1382 EXCEPTION
1383 WHEN FND_API.G_EXC_ERROR THEN
1384 IF L_DEBUG = 'Y' THEN
1385 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSION - In FND_API.G_EXC_ERROR exception','C');
1386 END IF;
1387 RETCODE := x_return_status;
1388 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - x_return_status ----- : '|| x_return_status,'C');
1389 ERRBUF := x_err_stage;
1390 /* bug 8214030: resetting the profile values back */
1391 if (l_set_profile_success1 = TRUE) then
1392 l_set_profile_success1 := fnd_profile.save('PA_SUPER_PROJECT', l_user_profile_value1, 'USER', fnd_global.user_id);
1393 end if;
1394 if (l_set_profile_success2 = TRUE) then
1395 l_set_profile_success2 := fnd_profile.save('PA_SUPER_PROJECT_VIEW', l_user_profile_value2, 'USER', fnd_global.user_id);
1396 end if;
1397 when OTHERS THEN
1398 IF L_DEBUG = 'Y' THEN
1399 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSION - In when Others exception','C');
1400 END IF;
1401 RETCODE := 'U';
1402 ERRBUF := (SQLERRM||' '||SQLCODE);
1403 /* bug 8214030: resetting the profile values back */
1404 if (l_set_profile_success1 = TRUE) then
1405 l_set_profile_success1 := fnd_profile.save('PA_SUPER_PROJECT', l_user_profile_value1, 'USER', fnd_global.user_id);
1406 end if;
1407 if (l_set_profile_success2 = TRUE) then
1408 l_set_profile_success2 := fnd_profile.save('PA_SUPER_PROJECT_VIEW', l_user_profile_value2, 'USER', fnd_global.user_id);
1409 end if;
1410 END summarize_baselined_versions;
1411
1412 END GMS_SUMMARIZE_BUDGETS;