[Home] [Help]
PACKAGE BODY: APPS.GMS_SUMMARIZE_BUDGETS
Source
1 PACKAGE BODY GMS_SUMMARIZE_BUDGETS AS
2 -- $Header: gmsbusub.pls 120.2 2007/02/06 09:49:00 rshaik 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
709 -- This function returns 'Y' if the budget has both resource and resource group level budgeting.
710 -- Value returned is 'Y' if :
711 -- 1. Budgeting is at resource group level only OR
712 -- 2. Budgeting is at both resource and resource group level.
713 -- otherwise returns 'N', budgeting is at resource level only.
714 -- Bug 2935048.
715 --
716 -- Validation needs to happen at the task level if budget entry level
717 -- is task. Incase the budgeting level is Project, then task_id = 0.
718 -- Bug 3532920.
719
720 function check_resource_budget_levels(x_task_id in number) return varchar2 is
721 v_mixed_level_budget varchar2(1);
722 begin
723 select 'Y'
724 into v_mixed_level_budget
725 from dual
726 where exists (select '1'
727 from pa_resource_list_members prl,
728 gms_resource_assignments gra,
729 gms_budget_versions gbv
730 where prl.resource_list_id = x_resource_list_id
731 and prl.resource_list_member_id = gra.resource_list_member_id
732 and gbv.budget_version_id = gra.budget_version_id
733 and gbv.current_flag = 'Y'
734 and prl.parent_member_id is null
735 and gra.project_id = x_project_id
736 and gra.task_id = x_task_id);
737
738 return 'Y';
739 exception
740 when no_data_found then
741 return 'N';
742 end check_resource_budget_levels;
743
744 -- =================================================================================
745 -- New procedure introduced for GMS enhancements
746 -- =================================================================================
747 Procedure Project_sum_high_level ( x_project_id NUMBER ) IS
748 cursor C3_none_res IS
749 select prl.resource_list_member_id ,
750 gbv.project_id ,
751 sum(gbl.raw_cost) raw_cost
752 , sum(gbl.burdened_cost) burdened_cost
753 , sum(gbl.quantity) quantity
754 from gms_budget_versions gbv
755 , gms_resource_assignments gra
756 , gms_budget_lines gbl
757 , pa_resource_list_members prl
758 where gbv.project_id = x_project_id
759 and gbv.budget_version_id = gra.budget_version_id
760 and gra.resource_assignment_id = gbl.resource_assignment_id
761 and gbv.current_flag = 'Y'
762 and G_pa_res_list_id_none = prl.resource_list_id
763 and gra.resource_list_member_id = prl.resource_list_member_id
764 group by prl.resource_list_member_id , gbv.project_id;
765 -- group by gbv.project_id ;
766
767 cursor C3_res (p_pa_res_list_id VARCHAR2 ) IS
768 select
769 gra.resource_list_member_id ,
770 gbv.project_id
771 , sum(gbl.raw_cost) raw_cost
772 , sum(gbl.burdened_cost) burdened_cost
773 , sum(gbl.quantity) quantity
774 from gms_budget_versions gbv
775 , gms_resource_assignments gra
776 , gms_budget_lines gbl
777 , pa_resource_list_members prl
778 where gbv.project_id = x_project_id
779 and gbv.budget_version_id = gra.budget_version_id
780 and gra.resource_assignment_id = gbl.resource_assignment_id
781 and gbv.current_flag = 'Y'
782 and gbv.resource_list_id = prl.resource_list_id
783 and gra.resource_list_member_id = prl.resource_list_member_id
784 group by gra.resource_list_member_id, gbv.project_id ;
785
786 x_project_end_date DATE ;
787
788 Begin
789 SELECT start_date,
790 completion_date
791 INTO x_project_start_date,
792 x_project_end_date
793 FROM pa_projects_all
794 WHERE project_id = x_project_id;
795
796 IF G_pa_res_list_id_none is not NULL then
797 FOR rec_c3_none_res in C3_none_res LOOP
798 EXIT when C3_none_res%NOTFOUND;
799 i := i + 1;
800 x_budget_lines_in_rec.raw_cost := rec_c3_none_res.raw_cost;
801 x_budget_lines_in_rec.quantity := rec_c3_none_res.quantity;
802 x_budget_lines_in_rec.burdened_cost := rec_c3_none_res.burdened_cost;
803 x_budget_lines_in_rec.budget_start_date := x_project_start_date;
804 x_budget_lines_in_rec.budget_end_date := x_project_end_date;
805 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 ;
806 x_budget_lines_in_rec.pm_product_code := 'GMS';
807 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
808 END LOOP;
809 END IF ;
810 -- Ajay Review comments
811 IF G_pa_res_list_id is not NULL THEN
812 FOR rec_c3_res in C3_res(G_pa_res_list_id) LOOP
813 EXIT when C3_res%NOTFOUND;
814 i := i + 1;
815 x_budget_lines_in_rec.raw_cost := rec_c3_res.raw_cost;
816 x_budget_lines_in_rec.quantity := rec_c3_res.quantity;
817 x_budget_lines_in_rec.burdened_cost := rec_c3_res.burdened_cost;
818 x_budget_lines_in_rec.budget_start_date := x_project_start_date;
819 x_budget_lines_in_rec.budget_end_date := x_project_end_date;
820 x_budget_lines_in_rec.resource_list_member_id := rec_c3_res.resource_list_member_id ; --PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM ;
821 x_budget_lines_in_rec.pm_product_code := 'GMS';
822 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
823 END LOOP;
824 END IF ;
825
826 End ;
827 -- ---------------------------------------------------------------------------------
828 BEGIN
829
830 IF L_DEBUG = 'Y' THEN
831 gms_error_pkg.gms_debug('*** Start of GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS ***','C');
832 END IF;
833 x_return_status := 'S'; -- Initializing return status as Success.
834
835 -- Bug 2386041
836 -- Since we are calling the PA_BUDGET_PUB utilities need to initialize some global variables
837 set_global_info;
838 -- Bug 2386041
839
840 -- Bug 3770971..need to set the profile values for "PA: Cross Project User - Update" and
841 -- "PA: Cross Project User - View". These values are lost after calling set_global_info
842 -- which calls fnd_global.apps_initialize.
843
844 l_set_profile_success1 := fnd_profile.save('PA_SUPER_PROJECT', 'Y', 'USER', fnd_global.user_id);
845 l_set_profile_success2 := fnd_profile.save('PA_SUPER_PROJECT_VIEW', 'Y', 'USER', fnd_global.user_id);
846
847 -- Bug 3770971 end. Values will be set back in gms_budget_pub after summarize_budgets is done.
848
849 -- Added for GMS enhancement 5583170
850 SELECT start_date,
851 completion_date
852 INTO x_project_start_date,
853 x_project_end_date
854 FROM pa_projects_all
855 WHERE project_id = x_project_id;
856
857
858 IF draft_budget_exists (x_project_id
859 ,x_return_status
860 ,x_err_stage) THEN
861
862 IF L_DEBUG = 'Y' THEN
863 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling pa_budget_pub.delete_draft_budget','C');
864 END IF;
865
866 pa_budget_pub.delete_draft_budget(p_api_version_number => 1.0
867 ,p_init_msg_list => 'T'
868 ,p_msg_count => x_msg_count
869 ,p_msg_data => x_err_stage
870 ,p_return_status => x_return_status
871 ,p_pm_product_code => 'GMS' -- bug 3175909
872 ,p_pa_project_id => x_project_id
873 ,p_budget_type_code => 'AC');
874
875 IF x_return_status <> 'S' THEN
876
877 x_err_code := 2;
878
879 FND_MESSAGE.PARSE_ENCODED (encoded_message => x_err_stage
880 ,app_short_name => x_app_short_name
881 ,message_name => x_text);
882 x_err_stage := x_text;
883
884 IF L_DEBUG = 'Y' THEN
885 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; **************************','C');
886 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; x_return_status = '||x_return_status,'C');
887 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; x_err_stage = '||x_err_stage,'C');
888 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; x_msg_count = '||x_msg_count,'C');
889 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after delete draft; **************************','C');
890 END IF;
891
892 RAISE FND_API.G_EXC_ERROR;
893
894 END IF;
895
896 ELSE
897 IF x_return_status <> 'S' THEN
898 RAISE FND_API.G_EXC_ERROR;
899 END IF;
900 END IF;
901
902 IF L_DEBUG = 'Y' THEN
903 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling get_resource_list_id','C');
904 END IF;
905
906 get_resource_list_id (x_project_id
907 ,x_entry_method_code
908 ,x_resource_list_id
909 ,x_return_status
910 ,x_err_stage);
911
912
913 -- ------------------------------------
914 -- Added for GMS enhancements : 5583170
915 -- ------------------------------------
916 If x_return_status = 'U' then
917 IF L_DEBUG = 'Y' THEN
918 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS-x_return_status is U', 'C');
919 END IF;
920
921 GMS_SUMMARIZE_BUDGETS (x_project_id
922 ,x_return_status
923 ,x_err_stage ) ;
924 x_resource_list_id := NVL(G_pa_res_list_id,G_pa_res_list_id_none) ;
925
926
927 IF L_DEBUG = 'Y' THEN
928 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - G_project_bem : '||G_project_bem ,'C');
929 END IF;
930
931 IF L_DEBUG = 'Y' THEN
932 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - x_return_status : '||x_return_status ,'C');
933 END IF;
934 end if ;
935
936 -- We should not proceed further as G_project_bem IS NULL ( when profiles are unset and different BEM are used )
937
938 If G_project_bem IS NULL and x_resource_list_id IS NULL then
939
940 x_return_status := 'X' ; -- We pass 'X' for the return status instead of 'S' as the award summarization has not happened. We will display
941 -- different message saying that only Budget baseline has happened.
942 x_err_code := 2;
943 RAISE FND_API.G_EXC_ERROR;
944 RETURN ;
945 end if ;
946
947 If G_project_bem IS NULL and x_resource_list_id IS NOT NULL then -- Added for GMS enhancements
948 IF L_DEBUG = 'Y' THEN
949 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - x_time_phased_type_code : '||x_time_phased_type_code,'C');
950 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Start of date calculation logic','C');
951 END IF;
952
953 i := 0;
954
955 OPEN task_cur;
956 LOOP
957 FETCH task_cur INTO v_task_id;
958 EXIT WHEN task_cur%NOTFOUND;
959
960 -- GMS enhanceemnt for R12 : 5583170
961 x_task_start_date := NULL ;
962 x_task_end_date := NULL ;
963 IF v_task_id <> 0 THEN
964 SELECT nvl(start_date,x_project_start_date),
965 nvl(completion_date,x_project_end_date)
966 INTO x_task_start_date,
967 x_task_end_date /* Changed for bug 3372853 */
968 FROM pa_tasks
969 WHERE task_id = v_task_id;
970 END IF ;
971
972 v_res_grp := check_resource_budget_levels(v_task_id);
973
974 if x_time_phased_type_code IN ('G', 'P') then
975
976 FOR rec_c1 in C1(v_task_id, v_res_grp)
977 LOOP
978 EXIT when C1%NOTFOUND;
979 i := i + 1;
980 x_budget_lines_in_rec.resource_list_member_id := rec_c1.resource_list_member_id;
981 x_budget_lines_in_rec.raw_cost := rec_c1.raw_cost;
982 x_budget_lines_in_rec.quantity := rec_c1.quantity;
983 x_budget_lines_in_rec.burdened_cost := rec_c1.burdened_cost;
984 x_budget_lines_in_rec.period_name := rec_c1.period_name;
985 x_budget_lines_in_rec.pa_task_id := rec_c1.task_id;
986 x_budget_lines_in_rec.resource_list_member_id := rec_c1.resource_list_member_id;
987 x_budget_lines_in_rec.pm_product_code := 'GMS'; -- bug 3175909
988 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
989 END LOOP;
990
991 -- elsif x_time_phased_type_code IN ('R', 'N') then -- Bug 2466716
992 elsif x_time_phased_type_code = 'R' then
993
994 FOR rec_c2 in C2(v_task_id, v_res_grp)
995 LOOP
996 EXIT when C2%NOTFOUND;
997 i := i + 1;
998 x_budget_lines_in_rec.resource_list_member_id := rec_c2.resource_list_member_id;
999 x_budget_lines_in_rec.raw_cost := rec_c2.raw_cost;
1000 x_budget_lines_in_rec.quantity := rec_c2.quantity;
1001 x_budget_lines_in_rec.burdened_cost := rec_c2.burdened_cost;
1002 -- GMS enahancement : 5583170
1003 IF nvl(x_task_start_date, x_project_start_date) > rec_c2.start_date THEN
1004 x_budget_lines_in_rec.budget_start_date := nvl(x_task_start_date, x_project_start_date);
1005 ELSE
1006 x_budget_lines_in_rec.budget_start_date := rec_c2.start_date;
1007 end IF ;
1008
1009 IF nvl(x_task_end_date, x_project_end_date) < rec_c2.end_date THEN
1010 x_budget_lines_in_rec.budget_end_date := nvl(x_task_end_date, x_project_end_date);
1011 ELSE
1012 x_budget_lines_in_rec.budget_end_date := rec_c2.end_date;
1013 end IF ;
1014
1015 -- x_budget_lines_in_rec.budget_start_date := rec_c2.start_date;
1016 -- x_budget_lines_in_rec.budget_end_date := rec_c2.end_date;
1017
1018 x_budget_lines_in_rec.pa_task_id := rec_c2.task_id;
1019 x_budget_lines_in_rec.resource_list_member_id := rec_c2.resource_list_member_id;
1020 x_budget_lines_in_rec.pm_product_code := 'GMS'; -- bug 3175909
1021 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
1022 END LOOP;
1023
1024 elsif x_time_phased_type_code = 'N' then -- Bug 2466716
1025
1026 SELECT start_date,
1027 completion_date
1028 INTO x_project_start_date,
1029 x_project_end_date
1030 FROM pa_projects_all
1031 WHERE project_id = x_project_id;
1032
1033 FOR rec_c3 in C3(v_task_id, v_res_grp)
1034 LOOP
1035 EXIT when C3%NOTFOUND;
1036 i := i + 1;
1037
1038 -- Added the following SELECT and the IF block for Bug:2592747
1039
1040 SELECT distinct entry_level_code -- Added DISTINCT for Bug:2907692
1041 INTO x_entry_level_code
1042 FROM pa_budget_entry_methods pbem,
1043 gms_budget_versions gbv
1044 WHERE gbv.budget_entry_method_code = pbem.budget_entry_method_code
1045 AND gbv.project_id = x_project_id
1046 AND gbv.budget_status_code = 'B'
1047 AND gbv.current_flag = 'Y';
1048
1049 if x_entry_level_code in ('L','M','T') then
1050
1051 SELECT nvl(start_date,x_project_start_date),
1052 nvl(completion_date,x_project_end_date)
1053 INTO x_task_start_date,
1054 x_task_end_date /* Changed for bug 3372853 */
1055 --INTO x_project_start_date,
1056 -- x_project_end_date
1057 FROM pa_tasks
1058 WHERE task_id = rec_c3.task_id;
1059
1060 /* if x_project_start_date is NULL or x_project_end_date is NULL then Changed for bug 3372853 */
1061 if x_task_start_date is NULL or x_task_end_date is NULL then
1062 x_err_stage := 'GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Error occurred while fetching dates';
1063 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_TASK_PROJ_DATE',
1064 x_err_code => x_err_code,
1065 x_err_buff => x_err_stage);
1066 fnd_msg_pub.add; -- Bug 2587078
1067 RAISE FND_API.G_EXC_ERROR;
1068
1069 end if;
1070
1071 end if;
1072
1073 x_budget_lines_in_rec.resource_list_member_id := rec_c3.resource_list_member_id;
1074 x_budget_lines_in_rec.raw_cost := rec_c3.raw_cost;
1075 x_budget_lines_in_rec.quantity := rec_c3.quantity;
1076 x_budget_lines_in_rec.burdened_cost := rec_c3.burdened_cost;
1077 --Bug 5489263
1078 if x_entry_level_code in ('L','M','T') then
1079 x_budget_lines_in_rec.budget_start_date := x_task_start_date;
1080 x_budget_lines_in_rec.budget_end_date := x_task_end_date;
1081 else
1082 x_budget_lines_in_rec.budget_start_date := x_project_start_date;
1083 x_budget_lines_in_rec.budget_end_date := x_project_end_date;
1084 end if;
1085 --Bug 5489263
1086
1087 /*x_budget_lines_in_rec.budget_start_date := x_project_start_date;
1088 x_budget_lines_in_rec.budget_end_date := x_project_end_date; Changed for bug 3372853 */
1089 /* x_budget_lines_in_rec.budget_start_date := x_task_start_date;
1090 x_budget_lines_in_rec.budget_end_date := x_task_end_date; Commented for bug 5489263*/
1091
1092 x_budget_lines_in_rec.pa_task_id := rec_c3.task_id;
1093 x_budget_lines_in_rec.resource_list_member_id := rec_c3.resource_list_member_id;
1094 x_budget_lines_in_rec.pm_product_code := 'GMS'; -- bug 3175909
1095 x_budget_lines_in_tbl(i) := x_budget_lines_in_rec;
1096 END LOOP;
1097
1098 end if; -- time_phased_type_code if
1099
1100 END LOOP; -- task_cur;
1101
1102 CLOSE task_cur;
1103
1104 ELSIF G_project_bem IS NOT NULL and x_return_status = 'S' THEN -- ELSE for If G_project_bem is N NULL THEN
1105
1106 Project_sum_high_level (x_project_id ) ;
1107
1108 End if ;
1109
1110 IF L_DEBUG = 'Y' THEN
1111 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - End of date calculation logic','C');
1112 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling pa_budget_pub.create_draft_budget','C');
1113 END IF;
1114
1115 --Call budget API to create draft budget
1116
1117 -- GMS enhancements for R12 : 5583170
1118 If G_project_bem IS NOT NULL then
1119
1120 select budget_entry_method_code
1121 into x_entry_method_code
1122 from pa_budget_entry_methods
1123 where budget_entry_method = G_project_bem ;
1124 end if ;
1125
1126 pa_budget_pub.create_draft_budget(p_api_version_number => 1.0
1127 ,p_init_msg_list => 'T'
1128 ,p_msg_count => x_msg_count
1129 ,p_msg_data => x_err_stage
1130 ,p_return_status => x_return_status
1131 ,p_pm_product_code => 'GMS'
1132 ,p_pa_project_id => x_project_id
1133 ,p_budget_type_code => 'AC'
1134 ,p_entry_method_code => x_entry_method_code
1135 ,p_resource_list_id => x_resource_list_id
1136 ,p_budget_lines_in => x_budget_lines_in_tbl
1137 ,p_budget_lines_out => x_budget_lines_out_tbl
1138 ,p_change_reason_code => null
1139 ,p_pm_budget_reference=> null);
1140
1141
1142 IF x_return_status <>'S' THEN
1143 x_err_code := 2;
1144
1145
1146 FND_MESSAGE.PARSE_ENCODED ( encoded_message => x_err_stage
1147 ,app_short_name => x_app_short_name
1148 ,message_name => x_text);
1149 x_err_stage := x_text;
1150
1151 IF L_DEBUG = 'Y' THEN
1152 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; ************************','C');
1153 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; x_return_status = '||x_return_status,'C');
1154 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; x_msg_count = '||x_msg_count,'C');
1155 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; x_err_stage = '||x_err_stage,'C');
1156 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after create draft; ************************','C');
1157 END IF;
1158
1159 RAISE FND_API.G_EXC_ERROR;
1160
1161 END IF;
1162 -- R11i Change
1163 -- Call PA's Budget API to Baseline this budget since the Project Budget should
1164 -- automatically be baselined when the Award Budget is baselined.
1165
1166 IF L_DEBUG = 'Y' THEN
1167 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - Calling pa_budget_pub.baseline_budget','C');
1168 END IF;
1169
1170 pa_budget_pub.baseline_budget( p_api_version_number => 1.0,
1171 p_init_msg_list => 'T',
1172 p_msg_count => x_msg_count,
1173 p_msg_data => x_err_stage,
1174 p_return_status => x_return_status,
1175 p_workflow_started => x_workflow_started,
1176 p_pm_product_code => 'GMS',
1177 p_pa_project_id => x_project_id,
1178 p_pm_project_reference => NULL,
1179 p_budget_type_code => 'AC');
1180
1181 IF L_DEBUG = 'Y' THEN
1182 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1183 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_return_status = '||x_return_status,'C');
1184 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_msg_count = '||x_msg_count,'C');
1185 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_err_stage = '||x_err_stage,'C');
1186 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1187 END IF;
1188 RETCODE := x_return_status ; -- Added for GMS enhancement for R12 : 5583170
1189
1190 IF x_return_status <>'S' THEN
1191
1192 x_err_code := 2;
1193
1194 FND_MESSAGE.PARSE_ENCODED ( encoded_message => x_err_stage
1195 ,app_short_name => x_app_short_name
1196 ,message_name => x_text);
1197 x_err_stage := x_text;
1198
1199 IF L_DEBUG = 'Y' THEN
1200 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1201 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_return_status = '||x_return_status,'C');
1202 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_msg_count = '||x_msg_count,'C');
1203 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; x_err_stage = '||x_err_stage,'C');
1204 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - after baseline budget; ************************','C');
1205 END IF;
1206
1207 RAISE FND_API.G_EXC_ERROR;
1208
1209 END IF;
1210
1211 IF L_DEBUG = 'Y' THEN
1212 gms_error_pkg.gms_debug('*** End of GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSION ***','C');
1213 END IF;
1214
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 IF L_DEBUG = 'Y' THEN
1218 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSION - In FND_API.G_EXC_ERROR exception','C');
1219 END IF;
1220 RETCODE := x_return_status;
1221 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSIONS - x_return_status ----- : '|| x_return_status,'C');
1222 ERRBUF := x_err_stage;
1223
1224 when OTHERS THEN
1225 IF L_DEBUG = 'Y' THEN
1226 gms_error_pkg.gms_debug('GMS_SUMMARIZE_BUDGETS.SUMMARIZE_BASELINED_VERSION - In when Others exception','C');
1227 END IF;
1228 RETCODE := 'U';
1229 ERRBUF := (SQLERRM||' '||SQLCODE);
1230 END summarize_baselined_versions;
1231
1232 END GMS_SUMMARIZE_BUDGETS;