[Home] [Help]
PACKAGE BODY: APPS.GMS_SWEEPER
Source
1 PACKAGE BODY GMS_SWEEPER AS
2 -- $Header: gmsfcuab.pls 120.9.12010000.7 2009/12/02 06:11:59 abjacob 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
7 Type Bal_rec is RECORD(Exp_item_id pa_expenditure_items_all.expenditure_item_id%type,
8 adl gms_award_distributions.adl_line_num%type,
9 award_id gms_awards_all.award_id%type,
10 Project_id gms_event_attribute.project_id%type,
11 Task_Id gms_event_attribute.actual_task_id%type,
12 Amount gms_event_attribute.bill_amount%type,
13 Reason Varchar2(200));
14
15 Type Fail_Rec is RECORD(resource_list_id gms_bc_packets.resource_list_member_id%type,
16 person_id pa_expenditures_all.incurred_by_person_id%type,
17 job_id pa_expenditure_items_all.job_id%type,
18 org_id pa_expenditures_all.incurred_by_organization_id%type,
19 expenditure_type pa_expenditure_items_all.expenditure_type%type,
20 nlr pa_expenditure_items_all.non_labor_resource%type,
21 exp_category pa_expenditure_types.expenditure_category%type,
22 rev_category pa_expenditure_types.revenue_category_code%type,
23 nlr_org_id pa_expenditure_items_all.organization_id%type,
24 sys_link pa_expenditure_items_all.system_linkage_function%type,
25 exp_date pa_expenditure_items_all.expenditure_item_date%type,
26 bvid gms_bc_packets.budget_version_id%type,
27 bud_task gms_bc_packets.bud_task_id%type,
28 cat_code pa_budget_entry_methods.categorization_code%type,
29 tp_code pa_budget_entry_methods.time_phased_type_code%type);
30
31 Type Err_Bal_Tab is table of Bal_Rec
32 INDEX BY BINARY_INTEGER;
33
34 Type Fail_Tab is table of Fail_Rec
35 INDEX BY BINARY_INTEGER;
36
37 Upd_Error_Table Err_Bal_Tab;
38
39 Upd_Reason_Table Fail_Tab;
40
41 -- Variables used for recording burdenable raw cost and raw cost -- Bug 4053891
42 TYPE Rec_Award_Exp is RECORD(award_id gms_award_exp_type_act_cost.award_id%type,
43 expenditure_type gms_award_exp_type_act_cost.expenditure_type%type,
44 exp_raw_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
45 exp_burdenable_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
46 ap_raw_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
47 ap_burdenable_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
48 po_raw_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
49 po_burdenable_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
50 req_raw_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
51 req_burdenable_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
52 enc_raw_cost gms_award_exp_type_act_cost.exp_raw_cost%type,
53 enc_burdenable_cost gms_award_exp_type_act_cost.exp_raw_cost%type);
54
55 --TYPE Tab_Award_exp is TABLE OF gms_award_exp_type_act_cost%ROWTYPE;
56 TYPE Tab_Award_exp is TABLE OF Rec_Award_Exp INDEX by BINARY_INTEGER;
57 Tab_Award_exp_burden Tab_Award_exp;
58
59 /* -----------------------------------------------------------------------------------------------
60 Procedure : lock_budget_versions (Bug 4053891)
61 Purpose : - This procedure will lock the budget version records for the budget versions
62 being posted.
63 - This was reqd. to enforce incompatibility between sweeper and FC
64 for REQ/PO/AP/FAB/Interface.
65 -------------------------------------------------------------------------------------------------- */
66 Procedure Lock_budget_versions(p_budget_version_id number) is
67 l_dummy number;
68 Begin
69 Select 1 into l_dummy from gms_budget_versions
70 where budget_version_id = p_budget_version_id
71 for update;
72 End Lock_budget_versions;
73
74 /* -----------------------------------------------------------------------------------------------
75 Procedure : Record_burden_amounts (Bug 4053891)
76 Purpose : This procedure will record the burdenable raw cost and the raw cost
77 amounts that needs to be posted to gms_award_exp_type_act_cost
78 -------------------------------------------------------------------------------------------------- */
79 PROCEDURE Record_burden_amounts(p_award_id IN gms_award_exp_type_act_cost.award_id%type,
80 p_exp_type IN gms_award_exp_type_act_cost.expenditure_type%type,
81 p_raw_cost IN gms_award_exp_type_act_cost.exp_raw_cost%type,
82 p_burdenable_cost IN gms_award_exp_type_act_cost.exp_raw_cost%type,
83 p_doc_type IN VARCHAR2,
84 p_Tab_Award_exp_burden IN OUT NOCOPY Tab_Award_exp,
85 p_error_code OUT NOCOPY Varchar2
86 ) IS
87 l_plsql_counter number;
88 l_posted_flag varchar2(1);
89 -- Variable to hold procedure name
90 l_procedure_name varchar2(25);
91
92 Begin
93 If p_Tab_Award_exp_burden.exists(1) then
94 l_plsql_counter := p_Tab_Award_exp_burden.COUNT;
95 End If;
96
97 l_posted_flag := 'N';
98 p_error_code := null;
99
100 l_procedure_name := 'RECORD_BURDEN_AMOUNTS:';
101 IF L_DEBUG = 'Y' THEN
102 gms_error_pkg.gms_debug(l_procedure_name||'Start','C');
103 END IF;
104
105 If l_plsql_counter > 0 then -- I
106 For x in 1..l_plsql_counter
107 Loop
108 If (p_Tab_Award_exp_burden(x).award_id = p_award_id and
109 p_Tab_Award_exp_burden(x).expenditure_type = p_exp_type) then -- II
110
111 If p_doc_type = 'EXP' then
112 p_Tab_Award_exp_burden(x).exp_raw_cost := nvl(p_Tab_Award_exp_burden(x).exp_raw_cost,0) + p_raw_cost;
113 p_Tab_Award_exp_burden(x).exp_burdenable_cost := nvl(p_Tab_Award_exp_burden(x).exp_burdenable_cost,0) + p_burdenable_cost;
114 l_posted_flag := 'Y';
115 EXIT;
116 End If;
117
118 If p_doc_type = 'REQ' then
119 p_Tab_Award_exp_burden(x).req_raw_cost := nvl(p_Tab_Award_exp_burden(x).req_raw_cost,0) + p_raw_cost;
120 p_Tab_Award_exp_burden(x).req_burdenable_cost := nvl(p_Tab_Award_exp_burden(x).req_burdenable_cost,0) + p_burdenable_cost;
121 l_posted_flag := 'Y';
122 EXIT;
123 End If;
124
125 If p_doc_type = 'PO' then
126 p_Tab_Award_exp_burden(x).po_raw_cost := nvl(p_Tab_Award_exp_burden(x).po_raw_cost,0) + p_raw_cost;
127 p_Tab_Award_exp_burden(x).po_burdenable_cost := nvl(p_Tab_Award_exp_burden(x).po_burdenable_cost,0) + p_burdenable_cost;
128 l_posted_flag := 'Y';
129 EXIT;
130 End If;
131
132 If p_doc_type = 'AP' then
133 p_Tab_Award_exp_burden(x).ap_raw_cost := nvl(p_Tab_Award_exp_burden(x).ap_raw_cost,0) + p_raw_cost;
134 p_Tab_Award_exp_burden(x).ap_burdenable_cost := nvl(p_Tab_Award_exp_burden(x).ap_burdenable_cost,0) + p_burdenable_cost;
135 l_posted_flag := 'Y';
136 EXIT;
137 End If;
138
139 If p_doc_type = 'ENC' then
140 p_Tab_Award_exp_burden(x).enc_raw_cost := nvl(p_Tab_Award_exp_burden(x).enc_raw_cost,0) + p_raw_cost;
141 p_Tab_Award_exp_burden(x).enc_burdenable_cost := nvl(p_Tab_Award_exp_burden(x).enc_burdenable_cost,0) + p_burdenable_cost;
142 l_posted_flag := 'Y';
143 EXIT;
144 End If;
145
146 End If; -- Award/Exp type comparison II
147 End loop;
148 End If; -- If l_plsql_counter > 0 then -- I
149
150 If l_posted_flag = 'N' then
151
152 l_plsql_counter := nvl(l_plsql_counter,0) + 1;
153
154 p_Tab_Award_exp_burden(l_plsql_counter).award_id := p_award_id;
155 p_Tab_Award_exp_burden(l_plsql_counter).expenditure_type := p_exp_type;
156
157 If p_doc_type = 'EXP' then
158 p_Tab_Award_exp_burden(l_plsql_counter).exp_raw_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).exp_raw_cost,0) + p_raw_cost;
159 p_Tab_Award_exp_burden(l_plsql_counter).exp_burdenable_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).exp_burdenable_cost,0) + p_burdenable_cost;
160 GOTO AMT_ACCOUNTED;
161 End If;
162
163 If p_doc_type = 'REQ' then
164 p_Tab_Award_exp_burden(l_plsql_counter).req_raw_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).req_raw_cost,0) + p_raw_cost;
165 p_Tab_Award_exp_burden(l_plsql_counter).req_burdenable_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).req_burdenable_cost,0) + p_burdenable_cost;
166 GOTO AMT_ACCOUNTED;
167 End If;
168
169 If p_doc_type = 'PO' then
170 p_Tab_Award_exp_burden(l_plsql_counter).po_raw_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).po_raw_cost,0) + p_raw_cost;
171 p_Tab_Award_exp_burden(l_plsql_counter).po_burdenable_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).po_burdenable_cost,0) + p_burdenable_cost;
172 GOTO AMT_ACCOUNTED;
173 End If;
174
175 If p_doc_type = 'AP' then
176 p_Tab_Award_exp_burden(l_plsql_counter).ap_raw_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).ap_raw_cost,0) + p_raw_cost;
177 p_Tab_Award_exp_burden(l_plsql_counter).ap_burdenable_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).ap_burdenable_cost,0) + p_burdenable_cost;
178 GOTO AMT_ACCOUNTED;
179 End If;
180
181 If p_doc_type = 'ENC' then
182 p_Tab_Award_exp_burden(l_plsql_counter).enc_raw_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).enc_raw_cost,0) + p_raw_cost;
183 p_Tab_Award_exp_burden(l_plsql_counter).enc_burdenable_cost := nvl(p_Tab_Award_exp_burden(l_plsql_counter).enc_burdenable_cost,0) + p_burdenable_cost;
184 GOTO AMT_ACCOUNTED;
185 End If;
186
187 <<AMT_ACCOUNTED>>
188 null;
189 End If; -- I
190 -- l_posted_flag := 'Y';
191 IF L_DEBUG = 'Y' THEN
192 gms_error_pkg.gms_debug(l_procedure_name||'End','C');
193 END IF;
194
195 Exception
196 When others then
197 IF L_DEBUG = 'Y' THEN
198 gms_error_pkg.gms_debug(l_procedure_name||SQLERRM,'C');
199 END IF;
200 p_error_code := 'E';
201 End Record_burden_amounts;
202
203 /* -----------------------------------------------------------------------------------------------
204 Procedure : Post_burden_amounts (Bug 4053891)
205 Purpose : This procedure will post the burdenable raw cost and the raw cost
206 amounts to gms_award_exp_type_act_cost
207 -------------------------------------------------------------------------------------------------- */
208 PROCEDURE Post_burden_amounts (p_plsql_count IN Number,
209 p_Tab_Award_exp_burden IN Tab_Award_exp,
210 p_error_code OUT NOCOPY Varchar2)
211 IS
212 -- Variable to hold procedure name
213 l_procedure_name varchar2(25);
214 Begin
215 p_error_code := null;
216 l_procedure_name := 'POST_BURDEN_AMOUNTS:';
217 IF L_DEBUG = 'Y' THEN
218 gms_error_pkg.gms_debug(l_procedure_name||'Start','C');
219 END IF;
220
221 -- 1. Get gms_bc_packet records for burdne posting
222 For x in 1..p_plsql_count
223 loop
224 -- 2.Update burden summary table: gms_award_exp_type_act_cost
225 Update gms_award_exp_type_act_cost
226 Set exp_raw_cost = nvl(exp_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).exp_raw_cost,0),
227 exp_burdenable_cost = nvl(exp_burdenable_cost,0)+ nvl(p_Tab_Award_exp_burden(x).exp_burdenable_cost,0),
228 ap_raw_cost = nvl(ap_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).ap_raw_cost,0),
229 ap_burdenable_cost = nvl(ap_burdenable_cost,0) + nvl(p_Tab_Award_exp_burden(x).ap_burdenable_cost,0),
230 po_raw_cost = nvl(po_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).po_raw_cost,0),
231 po_burdenable_cost = nvl(po_burdenable_cost,0) + nvl(p_Tab_Award_exp_burden(x).po_burdenable_cost,0),
232 req_raw_cost = nvl(req_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).req_raw_cost,0),
233 req_burdenable_cost = nvl(req_burdenable_cost,0)+ nvl(p_Tab_Award_exp_burden(x).req_burdenable_cost,0),
234 enc_raw_cost = nvl(enc_raw_cost,0) + nvl(p_Tab_Award_exp_burden(x).enc_raw_cost,0),
235 enc_burdenable_cost = nvl(enc_burdenable_cost,0)+ nvl(p_Tab_Award_exp_burden(x).enc_burdenable_cost,0)
236 Where award_id = p_Tab_Award_exp_burden(x).award_id
237 And expenditure_type = p_Tab_Award_exp_burden(x).expenditure_type;
238
239 -- 3. If burden summary record does not exist, create it
240 IF SQL%NOTFOUND then
241 INSERT INTO GMS_award_exp_type_act_cost (award_id,
242 expenditure_type,
243 exp_raw_cost,
244 exp_burdenable_cost,
245 ap_raw_cost,
246 ap_burdenable_cost,
247 po_raw_cost,
248 po_burdenable_cost,
249 req_raw_cost,
250 req_burdenable_cost,
251 enc_raw_cost,
252 enc_burdenable_cost,
253 created_by,
254 created_date,
255 last_updated_by,
256 last_update_date )
257 Values (p_Tab_Award_exp_burden(x).award_id,
258 p_Tab_Award_exp_burden(x).expenditure_type,
259 nvl(p_Tab_Award_exp_burden(x).exp_raw_cost,0),
260 nvl(p_Tab_Award_exp_burden(x).exp_burdenable_cost,0),
261 nvl(p_Tab_Award_exp_burden(x).ap_raw_cost,0),
262 nvl(p_Tab_Award_exp_burden(x).ap_burdenable_cost,0),
263 nvl(p_Tab_Award_exp_burden(x).po_raw_cost,0),
264 nvl(p_Tab_Award_exp_burden(x).po_burdenable_cost,0),
265 nvl(p_Tab_Award_exp_burden(x).req_raw_cost,0),
266 nvl(p_Tab_Award_exp_burden(x).req_burdenable_cost,0),
267 nvl(p_Tab_Award_exp_burden(x).enc_raw_cost,0),
268 nvl(p_Tab_Award_exp_burden(x).enc_burdenable_cost,0),
269 nvl(fnd_global.user_id,0),
270 sysdate,
271 nvl(fnd_global.user_id,0),
272 sysdate);
273 IF SQL%ROWCOUNT = 0 THEN
274 ROLLBACK TO SAVEPOINT A;
275 RAISE_APPLICATION_ERROR(-20002,SQLERRM);
276 END IF;
277
278
279
280 END IF; -- IF SQL%NOTFOUND then
281
282 End Loop;
283
284 IF L_DEBUG = 'Y' THEN
285 gms_error_pkg.gms_debug(l_procedure_name||'End','C');
286 END IF;
287 Exception
288 When others then
289 IF L_DEBUG = 'Y' THEN
290 gms_error_pkg.gms_debug(l_procedure_name||SQLERRM,'C');
291 END IF;
292 p_error_code := 'E';
293 End Post_burden_amounts;
294 ----------------------------------------------------------------------------------------
295
296 -- Procedure to intitalize revenue records as a pre-process when sweeper called in
297 -- baseline mode
298
299 PROCEDURE intialize_revenue_records(p_award_id in number,
300 p_project_id in number,
301 p_err_code out NOCOPY number,
302 p_err_buff out NOCOPY varchar2) IS
303 l_award_project_id number(15);
304 l_stage varchar2(60);
305 l_bvid number(15);
306 Begin
307
308 p_err_code := 0;
309
310 l_stage := 'intialize_revenue_records: Get Award Project Id';
311
312 Select award_project_id
313 into l_award_project_id
314 from gms_awards_all -- Bug 4732065
315 where award_id = p_award_id;
316
317 l_stage := 'intialize_revenue_records: Delete gms_billing_cancellations';
318
319 delete
320 from gms_billing_cancellations
321 where award_project_id = l_award_project_id
322 and actual_project_id = p_project_id;
323
324 l_stage := 'intialize_revenue_records: Update Burden Records';
325
326 Update gms_burden_components
327 set revenue_accumulated='N'
328 where award_project_id = l_award_project_id
329 and actual_project_id = p_project_id
330 and event_type = 'REVENUE';
331
332 l_stage := 'intialize_revenue_records: Update raw records';
333
334 Update gms_event_intersect
335 set revenue_accumulated='N'
336 where expenditure_item_id in
337 (Select expenditure_item_id
338 from gms_award_distributions
339 where award_id = p_award_id
340 and project_id = p_project_id
341 and adl_status = 'A' -- added for bug 4108031
342 and document_type = 'EXP') -- added for bug 4108031
343 and event_type = 'REVENUE';
344
345
346 l_stage := 'intialize_revenue_records: Get Bvid';
347
348 Select budget_version_id
349 into l_bvid
350 from gms_budget_versions
351 where project_id = p_project_id
352 and award_id = p_award_id
353 and budget_type_code ='AC'
354 and budget_status_code = 'B'
355 and current_flag='Y';
356
357 l_stage := 'intialize_revenue_records: Clean balances';
358
359 --## This deletion is reqd. as there can be a case when Sweeper is running in 'U' mode
360 --## and another user baslines Award/Project. In this case, there may be a partial updation
361 --## for the new budget version. As we're running baseline in 'B' mode for that award/projcet again
362 --## we need to clean up and rebuild data.
363
364 Delete
365 from gms_balances
366 where project_id = p_project_id
367 and award_id = p_award_id
368 and budget_version_id = l_bvid
369 and balance_type='REV';
370
371 Exception
372
373 When Others then
374
375 gms_error_pkg.gms_message ( x_err_name=> 'GMS_UNEXPECTED_ERROR',
376 x_token_name1 => 'PROGRAM_NAME',
377 x_token_val1 => l_stage,
378 x_token_name2 => 'SQLCODE',
379 x_token_val2 => SQLCODE,
380 x_token_name3 => 'SQLERRM',
381 x_token_val3 => SQLERRM,
382 x_exec_type => 'C',
383 x_err_code => p_err_code,
384 x_err_buff => p_err_buff );
385
386 End intialize_revenue_records;
387 ----------------------------------------------------------------------------------------
388 -- Procedure to calculate start and end date for all amount type and boundary code combinations
389
390 PROCEDURE setup_start_end_date (
391 x_project_id IN gms_bc_packets.project_id%TYPE,
392 x_award_id IN gms_bc_packets.award_id%TYPE,
393 x_bud_task_id IN gms_bc_packets.bud_task_id%TYPE, -- Bug 2673200
394 x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
395 x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
396 x_entry_level_code IN pa_budget_entry_methods.entry_level_code%TYPE, --Bug 2673200
397 x_expenditure_item_date IN DATE,
398 x_amount_type IN gms_awards.amount_type%TYPE,
399 x_boundary_code IN gms_awards.boundary_code%TYPE,
400 x_set_of_books_id IN gms_bc_packets.set_of_books_id%TYPE,
401 x_start_date OUT NOCOPY DATE,
402 x_end_date OUT NOCOPY DATE,
403 x_err_code OUT NOCOPY NUMBER,
404 x_err_buff OUT NOCOPY VARCHAR2 ) IS
405
406 project_start_date DATE;
407 project_end_date DATE;
408 x_award_start_date DATE;
409 x_award_end_date DATE;
410 x_task_start_date DATE;
411 x_task_end_date DATE;
412 x_check_task VARCHAR2(1) := 'N';
413
414 x_error_stage VARCHAR2(40);
415 x_error_procedure_name VARCHAR2(40) := 'gms_sweeper.setup_start_end_date_cal';
416
417 -- For date range budget start and end dates will never overlap for an award and project combination
418 -- For None all budget lines will have same start and end Dates for project,task and award combination
419
420 CURSOR get_budget_dates(p_project_id NUMBER,
421 p_award_id NUMBER,
422 p_budget_version_id NUMBER,
423 p_bud_task_id NUMBER,
424 p_check_task VARCHAR2,
425 p_expenditure_item_date DATE) IS
426 SELECT MAX(TRUNC(gb.start_date)),MIN(TRUNC(gb.end_date))
427 FROM gms_balances gb
428 WHERE gb.project_id = p_project_id
429 AND gb.award_id = p_award_id
430 AND gb.budget_version_id = p_budget_version_id
431 AND gb.balance_type = 'BGT'
432 AND ( (p_check_task = 'Y' AND gb.task_id = p_bud_task_id) OR
433 (p_check_task = 'N'))
434 AND TRUNC(p_expenditure_item_date) between gb.start_date and gb.end_date;
435
436 BEGIN
437 x_error_procedure_name := 'setup_start_end_date_cal';
438 x_err_code := 0; -- initialize error code
439
440 IF x_entry_level_code <> 'P' AND x_time_phased_type_code = 'N' THEN
441 x_check_task :='Y' ;
442 ELSE
443 x_check_task :='N' ;
444 END IF;
445
446 IF x_time_phased_type_code = 'N' THEN
447 x_error_stage := 'Time Phase = N' ;
448
449 OPEN get_budget_dates(x_project_id,x_award_id,x_budget_version_id,x_bud_task_id,
450 x_check_task,x_expenditure_item_date);
451 FETCH get_budget_dates INTO x_start_date,x_end_date;
452 CLOSE get_budget_dates;
453
454 IF (x_start_date IS NULL OR x_end_date IS NULL) THEN
455
456 SELECT start_date,completion_date
457 INTO project_start_date,project_end_date
458 FROM pa_projects_all -- Bug 4732065 : modified to use _all
459 WHERE project_id = x_project_id;
460
461 SELECT nvl(preaward_date,start_date_active), end_date_active
462 INTO x_award_start_date,x_award_end_date
463 FROM gms_awards_all -- Bug 4732065 : modified to use _all
464 WHERE award_id = x_award_id;
465
466 IF (x_entry_level_code = 'P') THEN
467
468 x_start_date := GREATEST(NVL(project_start_date,x_award_start_date),x_award_start_date);
469 x_end_date := LEAST(NVL(project_end_date,x_award_end_date),x_award_end_date);
470
471 ELSE
472
473 SELECT start_date,completion_date
474 INTO x_task_start_date,x_task_end_date
475 FROM pa_tasks
476 WHERE task_id = x_bud_task_id;
477
478 x_start_date := GREATEST(NVL(project_start_date,x_award_start_date),x_award_start_date,NVL(x_task_start_date,x_award_start_date));
479 x_end_date := LEAST(NVL(project_end_date,x_award_end_date),x_award_end_date,NVL(x_task_end_date,x_award_end_date));
480
481 END IF;
482 END IF;
483
484 END IF;
485
486 IF x_time_phased_type_code = 'R' THEN
487
488 x_error_stage := 'Time Phase = R' ;
489
490 OPEN get_budget_dates(x_project_id,x_award_id,x_budget_version_id,x_bud_task_id,
491 x_check_task,x_expenditure_item_date);
492 FETCH get_budget_dates INTO x_start_date,x_end_date;
493 CLOSE get_budget_dates;
494
495 If x_start_date is NULL then
496
497 select gps.start_date, gps.end_date
498 into x_start_date, x_end_date
499 from gl_period_statuses gps
500 where gps.application_id = 101
501 and gps.set_of_books_id = x_set_of_books_id
502 and trunc(x_expenditure_item_date) between gps.start_date and gps.end_date
503 and gps.adjustment_period_flag = 'N';
504
505 End if ;
506
507 End if; --IF x_time_phased_type_code = 'R' THEN
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 gms_error_pkg.gms_message ( x_err_name=> 'GMS_UNEXPECTED_ERROR',
512 x_token_name1 => 'PROGRAM_NAME',
513 x_token_val1 => x_error_procedure_name||'.'||x_error_stage,
514 x_token_name2 => 'SQLCODE',
515 x_token_val2 => SQLCODE,
516 x_token_name3 => 'SQLERRM',
517 x_token_val3 => SQLERRM,
518 x_exec_type => 'C',
519 x_err_code => x_err_code,
520 x_err_buff => x_err_buff );
521
522 x_start_date := null;
523 x_end_date := null;
524
525 END setup_start_end_date;
526
527 ----------------------------------------------------------------------------------------
528
529 PROCEDURE upd_act_enc_bal (errbuf OUT NOCOPY VARCHAR2
530 ,retcode OUT NOCOPY NUMBER -- Changed datatype to NUMBER for Bug:2464800
531 ,x_packet_id in number default null
532 ,x_mode in varchar2 DEFAULT 'U',
533 x_project_id in number default null,
534 x_award_id in number default null ) IS
535 cursor c_gms_packets (x_status_code varchar2) is --Bug 2138376 : Added x_status_code parameter
536 select gbc.budget_version_id
537 , gbc.project_id
538 , gbc.award_id
539 , gbc.task_id
540 , gbc.bud_task_id
541 , gbc.top_task_id
542 , gbc.document_type
543 , gbc.period_name
544 , gbc.resource_list_member_id
545 , gbc.parent_resource_id
546 , gbc.bud_resource_list_member_id
547 , gbc.set_of_books_id
548 , trunc(gbc.expenditure_item_date) expenditure_item_date
549 , gbc.entered_dr
550 , gbc.entered_cr
551 , gbc.actual_flag
552 , gbv.resource_list_id
553 , pbm.time_phased_type_code
554 , pbm.entry_level_code --2673200
555 , gbc.document_header_id
556 , gbc.document_distribution_id
557 , gbc.bc_packet_id
558 , ga.amount_type
559 , ga.boundary_code
560 , nvl(gbc.burdenable_raw_cost,0) burdenable_raw_cost -- this and next 4 added for bug 4053891
561 , gbc.parent_bc_packet_id
562 , gbc.expenditure_type
563 , nvl(gbc.burden_adjustment_flag,'N') burden_adjustment_flag
564 , gbc.rowid
565 from gms_budget_versions gbv
566 , gms_bc_packets gbc
567 , pa_budget_entry_methods pbm
568 , gms_awards_all ga
569 where gbc.status_code = x_status_code --Bug 2138376 : Replaced 'A' with x_status_code
570 and gbc.packet_id = nvl(x_packet_id, packet_id)
571 and gbv.budget_version_id = gbc.budget_version_id
572 and gbv.budget_entry_method_code = pbm.budget_entry_method_code
573 and ga.award_id = gbc.award_id
574 and gbc.project_id = nvl(x_project_id,gbc.project_id) /* Bug 3813928 */
575 and gbc.award_id = nvl(x_award_id,gbc.award_id) /* Bug 3813928 */
576 --for update of gbc.project_id; Bug4053891
577 order by gbc.budget_version_id,gbc.expenditure_type;
578
579 -- Bug 4053891 Start
580
581 x_plsql_count number;
582 x_loop_counter number;
583 x_old_budget_version_id number;
584
585 -- Bug 4053891 End
586
587
588 x_err_code varchar2(2):= null;
589 x_err_stage varchar2(255):= null;
590 St_e_code number;
591 x_parent_member_id number;
592 x_entry_level_code varchar2(30);
593 x_st_date date;
594 x_ed_date date;
595 x_budget_task_id number;
596 x_bud_res_list_member_id number;
597 x_program_name varchar2(30) := 'GMS_SWEEPER.UPD_ACT_ENC_BAL';
598 x_stage varchar2(10);
599 temp_flag number;
600 l_counter number :=0;
601 x_status_code varchar2(1) ; --Bug 2138376
602 l_offset_days NUMBER; -- Bug 2155790
603 -------------------------------------------------------------------
604 /*
605 || This Cursor is used in 'U' or non-baselining mode.
606 || update_revenue_balance procedure is called in a loop
607 || with in this cursor
608 */
609
610 Cursor Cur_records is
611 select distinct ga.award_id award_id, gspf.project_id project_id,imp.set_of_books_id
612 from gms_summary_project_fundings gspf,
613 gms_installments gi,
614 gms_awards_all ga, -- Bug 4732065 : To run the process across org
615 pa_implementations_all imp -- Bug 4732065 :
616 where gspf.installment_id = gi.installment_id
617 and gi.award_id = ga.award_id
618 and ga.revenue_distribution_rule='COST'
619 and ga.award_template_flag='DEFERRED'
620 and ga.org_id = imp.org_id
621 and (exists (select award_project_id
622 from gms_event_intersect
623 where award_project_id = ga.award_project_id
624 and event_type = 'REVENUE')
625 OR exists
626 (select award_project_id
627 from gms_billing_cancellations
628 where award_project_id = ga.award_project_id
629 and calling_process='Revenue')) ;
630
631 --Bug 4732065 : To fetch sob when called from baseline
632
633 Cursor c_get_sob (p_award_id IN NUMBER) IS
634 select set_of_books_id
635 from gms_awards_all ga,
636 pa_implementations_all imp
637 where ga.award_id = p_award_id
638 and imp.org_id = ga.org_id;
639
640 l_sob_id NUMBER;
641 -------------------------------------------------------------------
642
643 Procedure error_output(Err_table IN Err_Bal_Tab,
644 Res_table IN Fail_Tab)
645 IS
646 l_rec number :=0;
647 Begin
648
649 l_rec := err_table.COUNT;
650
651 --fnd_file.put_line(FND_FILE.OUTPUT,'---------- Records that Errored Out NOCOPY -----------');
652 gms_error_pkg.gms_output('---------- Records that Errored Out NOCOPY -----------');
653
654 for l_records in 1..l_rec
655 loop
656
657 gms_error_pkg.gms_output('----- Record Number: '||l_records||' ----');
658 gms_error_pkg.gms_output('---- Record -----');
659
660 gms_error_pkg.gms_output('Expenditure_item_id :'||Err_table(l_records).exp_item_id);
661 gms_error_pkg.gms_output('Adl Line num :'||Err_table(l_records).adl);
662 gms_error_pkg.gms_output('Award_id :'||Err_table(l_records).award_id);
663 gms_error_pkg.gms_output('Project_Id :'||Err_table(l_records).project_id);
664 gms_error_pkg.gms_output('Task_id :'||Err_table(l_records).task_id);
665 gms_error_pkg.gms_output('Amount :'||Err_table(l_records).amount);
666 gms_error_pkg.gms_output('Reason :'||Err_table(l_records).reason);
667
668 gms_error_pkg.gms_output('---- Record Details ----');
669 gms_error_pkg.gms_output('Person Id :'||Res_table(l_records).Person_Id);
670 gms_error_pkg.gms_output('Job Id :'||Res_table(l_records).Job_id);
671 gms_error_pkg.gms_output('Org_Id :'||Res_table(l_records).org_id);
672 gms_error_pkg.gms_output('Expenditure_type :'||Res_table(l_records).expenditure_type);
673 gms_error_pkg.gms_output('Non Labor resource :'||Res_table(l_records).nlr);
674 gms_error_pkg.gms_output('Expenditure Category :'||Res_table(l_records).exp_category);
675 gms_error_pkg.gms_output('Revenue Category :'||Res_table(l_records).rev_category);
676 gms_error_pkg.gms_output('Non Labor resource Org Id :'||Res_table(l_records).nlr_org_id);
677 gms_error_pkg.gms_output('System Linkage :'||Res_table(l_records).sys_link);
678 gms_error_pkg.gms_output('Expenditure Date :'||Res_table(l_records).exp_date);
679 gms_error_pkg.gms_output('Budget Version :'||Res_table(l_records).bvid);
680 gms_error_pkg.gms_output('Budgeted Task :'||Res_table(l_records).bud_task);
681 gms_error_pkg.gms_output('Categorization Code :'||Res_table(l_records).cat_code);
682 gms_error_pkg.gms_output('Time Phase code :'||Res_table(l_records).tp_code);
683 gms_error_pkg.gms_output('--------------------------------------------------');
684 end loop;
685
686 gms_error_pkg.gms_output('---- End Report ----');
687
688 End;
689
690 --------------------------------------------------------------------
691 /* --------------------------------------------------------------
692 ************* REVENUE UPDATION PROCEDURE STARTS *************
693 -------------------------------------------------------------- */
694 -- Procedure to update revenue amount in GMS_BALANCE from Revenue
695 -- related tables
696
697 Procedure update_revenue_balance(p_mode IN varchar2 default 'U',
698 p_award_id IN number,
699 p_project_id IN number,
700 p_sob_id IN number,
701 error_table IN OUT NOCOPY Err_Bal_Tab,
702 reason_table IN OUT NOCOPY Fail_Tab) IS
703
704 l_count number := 0;
705 --Start of the bug 5481465
706 --5379433: Start
707 TYPE IdTabTyp IS TABLE OF NUMBER
708 INDEX BY BINARY_INTEGER;
709 TYPE DateTabTyp IS TABLE OF DATE
710 INDEX BY BINARY_INTEGER;
711 TYPE NumTabTyp IS TABLE OF NUMBER
712 INDEX BY BINARY_INTEGER;
713 TYPE Char3TabTyp IS TABLE OF VARCHAR2(3)
714 INDEX BY BINARY_INTEGER;
715 TYPE Char30TabTyp IS TABLE OF VARCHAR2(30)
716 INDEX BY BINARY_INTEGER;
717 TYPE RowidTabTyp IS TABLE OF ROWID
718 INDEX BY BINARY_INTEGER;
719 --5379433: End
720
721 Cursor gms_rev1 is
722 -- In this select RLMI has been derived, GEI.
723 select gad.project_id project_id,
724 gad.award_id award_id,
725 gad.adl_line_num adl_line_num,
726 gad.cdl_line_num,
727 gad.task_id task_id,
728 gad.bud_task_id,
729 trunc(item.expenditure_item_date) expenditure_item_date,
730 gad.resource_list_member_id rlmi,
731 prm.parent_member_id parent,
732 gei.expenditure_item_id,
733 gei.amount amount,
734 'GEI' from_table,
735 item.expenditure_type,
736 to_number(NULL) org_id,
737 gei.rowid
738 from gms_event_intersect gei,
739 pa_expenditure_items_all item,
740 gms_award_distributions gad ,
741 pa_resource_list_members prm
742 where gei.expenditure_item_id = gad.expenditure_item_id
743 and gei.adl_line_num = gad.adl_line_num
744 and gei.event_type = 'REVENUE'
745 -- and gei.revenue_accumulated = 'N'
746 and item.expenditure_item_id = gad.expenditure_item_id
747 and gad.resource_list_member_id = prm.resource_list_member_id
748 and gad.document_type='EXP'
749 and gad.adl_status='A'
750 and gad.project_id = p_project_id
751 and gad.award_id = p_award_id
752 and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
753 UNION ALL
754 -- In this select RLMI has not been derived, GBC.
755 select gad.project_id project_id,
756 gad.award_id award_id,
757 gad.adl_line_num adl_line_num,
758 gad.cdl_line_num,
759 gad.task_id task_id,
760 gad.bud_task_id,
761 trunc(item.expenditure_item_date) expenditure_item_date,
762 to_number(NULL) rlmi,
763 prm.parent_member_id parent,
764 gbc.expenditure_item_id,
765 gbc.amount amount,
766 'GBC' from_table,
767 gbc.burden_exp_type,
768 nvl(pea.incurred_by_organization_id ,item.override_to_organization_id) org_id,
769 gbc.rowid
770 from gms_burden_components gbc,
771 pa_expenditure_items_all item ,
772 pa_expenditures_all pea,
773 gms_award_distributions gad ,
774 pa_resource_list_members prm
775 where gbc.expenditure_item_id = gad.expenditure_item_id
776 and gbc.adl_line_num = gad.adl_line_num
777 and gbc.event_type = 'REVENUE'
778 -- and gbc.revenue_accumulated = 'N'
779 and item.expenditure_item_id = gad.expenditure_item_id
780 and item.expenditure_id = pea.expenditure_id
781 and gad.resource_list_member_id = prm.resource_list_member_id
782 and gad.document_type='EXP'
783 and gad.adl_status='A'
784 and gad.project_id = p_project_id
785 and gad.award_id = p_award_id
786 and NVL(prm.migration_code,'M') ='M'; -- Bug 3626671
787 -- UNION ALL
788 -- select gad.project_id project_id,
789 -- gad.award_id award_id,
790 -- gad.adl_line_num adl_line_num,
791 -- gad.cdl_line_num,
792 -- gad.task_id task_id,
793 -- gad.bud_task_id,
794 -- trunc(item.expenditure_item_date) expenditure_item_date,
795 -- gad.resource_list_member_id rlmi,
796 -- prm.parent_member_id parent,
797 -- gbi.expenditure_item_id,
798 -- gbi.bill_amount amount,
799 -- 'GBI' from_table,
800 -- nvl(gbi.burden_exp_type,item.expenditure_type),
801 -- nvl(pea.incurred_by_organization_id,item.override_to_organization_id) org_id, gbi.rowid
802 -- from gms_billing_cancellations gbi,
803 -- pa_expenditure_items_all item ,
804 -- pa_expenditures_all pea,
805 -- gms_award_distributions gad ,
806 -- pa_resource_list_members prm
807 -- where gbi.expenditure_item_id = gad.expenditure_item_id
808 -- and item.expenditure_item_id = gad.expenditure_item_id
809 -- and gad.adl_line_num = gbi.adl_line_num
810 -- and item.expenditure_id = pea.expenditure_id
811 -- and gad.resource_list_member_id = prm.resource_list_member_id
812 -- and gad.document_type='EXP'
813 -- and gad.adl_status='A'
814 -- and gad.project_id = p_project_id
815 -- and gad.award_id = p_award_id
816 -- and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
817 -- ORDER BY 2, -- award_id
818 -- 1; -- project_i d
819 --End of bug 5481465
820 -- Bug 4732065: Modified below cursor to fetch org_id
821 Cursor gms_rev2 is --renamed gms_rev to gms_rev2 as pasrt of the bug 5481465
822 -- In this select RLMI has been derived, GEI.
823 select gad.project_id project_id,
824 gad.award_id award_id,
825 gad.adl_line_num adl_line_num,
826 gad.cdl_line_num,
827 gad.task_id task_id,
828 gad.bud_task_id,
829 trunc(item.expenditure_item_date) expenditure_item_date,
830 gad.resource_list_member_id rlmi,
831 prm.parent_member_id parent,
832 gei.expenditure_item_id,
833 gei.amount amount,
834 'GEI' from_table,
835 item.expenditure_type,
836 to_number(NULL) exp_org_id,
837 gei.rowid
838 --item.org_id -- Bug 4732065 commented for the bug 5481465
839 from gms_event_intersect gei,
840 pa_expenditure_items_all item,
841 gms_award_distributions gad,
842 pa_resource_list_members prm
843 where gei.expenditure_item_id = gad.expenditure_item_id
844 and gei.adl_line_num = gad.adl_line_num
845 and gei.event_type = 'REVENUE'
846 and gei.revenue_accumulated = 'N'
847 and item.expenditure_item_id = gad.expenditure_item_id
848 and gad.resource_list_member_id = prm.resource_list_member_id
849 and gad.document_type='EXP'
850 and gad.adl_status='A'
851 --and gad.project_id = p_project_id /* commented for the bug 5481465 */
852 --and gad.award_id = p_award_id /* Commented for the bug 5481465 */
853 and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
854 UNION ALL
855 -- In this select RLMI has not been derived, GBC.
856 select gad.project_id project_id,
857 gad.award_id award_id,
858 gad.adl_line_num adl_line_num,
859 gad.cdl_line_num,
860 gad.task_id task_id,
861 gad.bud_task_id,
862 trunc(item.expenditure_item_date) expenditure_item_date,
863 to_number(NULL) rlmi,
864 prm.parent_member_id parent,
865 gbc.expenditure_item_id,
866 gbc.amount amount,
867 'GBC' from_table,
868 gbc.burden_exp_type,
869 nvl(pea.incurred_by_organization_id,item.override_to_organization_id) exp_org_id,
870 gbc.rowid
871 --item.org_id -- Bug 4732065 commented for the bug 5481465
872 from gms_burden_components gbc,
873 pa_expenditure_items_all item,
874 pa_expenditures_all pea,
875 gms_award_distributions gad,
876 pa_resource_list_members prm
877 where gbc.expenditure_item_id = gad.expenditure_item_id
878 and gbc.adl_line_num = gad.adl_line_num
879 and gbc.event_type = 'REVENUE'
880 and gbc.revenue_accumulated = 'N'
881 and item.expenditure_item_id = gad.expenditure_item_id
882 and item.expenditure_id = pea.expenditure_id
883 and gad.resource_list_member_id = prm.resource_list_member_id
884 and gad.document_type='EXP'
885 and gad.adl_status='A'
886 -- and gad.project_id = p_project_id /* Commented for the bug 5481465 */
887 -- and gad.award_id = p_award_id /* Commented for the bug 5481465 */
888 and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
889 UNION ALL
890 select gad.project_id project_id,
891 gad.award_id award_id,
892 gad.adl_line_num adl_line_num,
893 gad.cdl_line_num,
894 gad.task_id task_id,
895 gad.bud_task_id,
896 trunc(item.expenditure_item_date) expenditure_item_date,
897 gad.resource_list_member_id rlmi,
898 prm.parent_member_id parent,
899 gbi.expenditure_item_id,
900 gbi.bill_amount amount,
901 'GBI' from_table,
902 nvl(gbi.burden_exp_type,item.expenditure_type),
903 nvl(pea.incurred_by_organization_id,item.override_to_organization_id) exp_org_id,
904 gbi.rowid
905 --item.org_id -- Bug 4732065 commented for the bug 5481465
906 from gms_billing_cancellations gbi,
907 pa_expenditure_items_all item,
908 pa_expenditures_all pea,
909 gms_award_distributions gad,
910 pa_resource_list_members prm
911 where gbi.expenditure_item_id = gad.expenditure_item_id
912 and item.expenditure_item_id = gad.expenditure_item_id
913 and gad.adl_line_num = gbi.adl_line_num
914 and item.expenditure_id = pea.expenditure_id
915 and gad.resource_list_member_id = prm.resource_list_member_id
916 and gad.document_type='EXP'
917 and gad.adl_status='A'
918 -- and gad.project_id = p_project_id /* Commented for the bug 5481465 */
919 -- and gad.award_id = p_award_id /* Commented for the bug 5481465 */
920 and NVL(prm.migration_code,'M') ='M' -- Bug 3626671
921 ORDER BY 2, -- award_id
922 1; -- project_id
923
924 x_expenditure_type varchar2(60);
925
926 x_start_date date;
927 x_end_date date;
928
929 x_set_of_books_id number;
930 x_amount_type gms_awards_all.amount_type%type := null;
931 x_boundary_code gms_awards_all.boundary_code%type := null;
932 x_resource_list_member_id number(30);
933 St_Err_Buff varchar2(2000) := null;
934 x_e_code number;
935 x_stage varchar2(100);
936 St_Err_Code varchar2(1);
937 x_budget_version_id gms_budget_versions.budget_version_id%type;
938
939 x_budget_entry_method pa_budget_entry_methods.budget_entry_method%type;
940 x_categorization_code pa_budget_entry_methods.categorization_code%type;
941 x_resource_list_id gms_budget_versions.resource_list_id%type;
942 x_time_phased_type_code pa_budget_entry_methods.time_phased_type_code%type;
943 x_uncategorized_rlmi pa_resource_list_members.resource_list_member_id%type;
944 x_prev_list_processed number(30);
945 x_group_resource_type_id number(15);
946 x_group_resource_type_name varchar2(60);
947 x_resource_type_tab gms_res_map.resource_type_table;
948 x_entry_level_code pa_budget_entry_methods.entry_level_code%type; --2673200
949
950 --5481465: Start
951 l_project_id_tbl IdTabTyp;
952 l_award_id_tbl IdTabTyp;
953 l_adl_ln_num_id_tbl IdTabTyp;
954 l_cdl_ln_num_id_tbl IdTabTyp;
955 l_task_id_tbl IdTabTyp;
956 l_bud_task_id_tbl IdTabTyp;
957 l_ei_date_tbl DateTabTyp;
958 l_rlmi_tbl IdTabTyp;
959 l_prnt_member_id_tbl IdTabTyp;
960 l_ei_id_tbl IdTabTyp;
961 l_amount_tbl NumTabTyp;
962 l_fr_tab_tbl Char3TabTyp;
963 l_exp_type_tbl Char30TabTyp;
964 l_org_id_tbl IdTabTyp;
965 l_rowid_tbl RowidTabTyp;
966
967 v_max_size NUMBER := 500;
968 v_all_done NUMBER := 0;
969 x_old_award_id NUMBER;
970 x_old_project_id NUMBER;
971 --5481465: End
972
973 Begin
974
975 -- 1. Update revenue amout in GMS_BALANCES for each record in gms_rev cursor.
976
977 -- Bug 4732065: Shifted logic for deriving SOB inside LOOP
978 -- Start of the bug 5481465
979
980 x_set_of_books_id := p_sob_id;
981
982 If p_mode ='B' then
983 OPEN gms_rev1;
984 Else
985 OPEN gms_rev2;
986 end if;
987
988 while(v_all_done = 0) loop
989 If p_mode ='B' then
990 FETCH gms_rev1 BULK COLLECT INTO
991 l_project_id_tbl,
992 l_award_id_tbl,
993 l_adl_ln_num_id_tbl,
994 l_cdl_ln_num_id_tbl,
995 l_task_id_tbl,
996 l_bud_task_id_tbl,
997 l_ei_date_tbl,
998 l_rlmi_tbl,
999 l_prnt_member_id_tbl,
1000 l_ei_id_tbl,
1001 l_amount_tbl,
1002 l_fr_tab_tbl,
1003 l_exp_type_tbl,
1004 l_org_id_tbl,
1005 l_rowid_tbl
1006 LIMIT v_max_size;
1007 Else
1008 FETCH gms_rev2 BULK COLLECT INTO
1009 l_project_id_tbl,
1010 l_award_id_tbl,
1011 l_adl_ln_num_id_tbl,
1012 l_cdl_ln_num_id_tbl,
1013 l_task_id_tbl,
1014 l_bud_task_id_tbl,
1015 l_ei_date_tbl,
1016 l_rlmi_tbl,
1017 l_prnt_member_id_tbl,
1018 l_ei_id_tbl,
1019 l_amount_tbl,
1020 l_fr_tab_tbl,
1021 l_exp_type_tbl,
1022 l_org_id_tbl,
1023 l_rowid_tbl
1024 LIMIT v_max_size;
1025 END IF;
1026
1027 If l_rowid_tbl.COUNT = 0 then
1028 exit;
1029 End If;
1030
1031 FOR i IN l_rowid_tbl.FIRST..l_rowid_tbl.LAST LOOP
1032 /* Commented and added new condtion with OR bug#7582155
1033 if (nvl(x_old_award_id,-1) <> l_award_id_tbl(i)
1034 and nvl(x_old_project_id,-1) <> l_project_id_tbl(i)) then */
1035 if (nvl(x_old_award_id,-1) <> l_award_id_tbl(i)
1036 OR nvl(x_old_project_id,-1) <> l_project_id_tbl(i)) then
1037
1038 --End of the bug 5481465
1039
1040 -- 2. Get budget version id
1041
1042 x_stage:='Get Budget Version Id';
1043
1044 Begin
1045
1046 select budget_version_id
1047 into x_budget_version_id
1048 from gms_budget_versions
1049 where project_id = l_project_id_tbl(i) -- p_project_id as part of the bug 5481465
1050 and award_id = l_award_id_tbl(i) -- p_award_id as part of the bug 5481465
1051 and budget_type_code ='AC'
1052 and budget_status_code = 'B'
1053 and current_flag='Y';
1054 Exception
1055 when others then
1056
1057 x_budget_version_id := NULL;
1058
1059 End;
1060
1061 If x_budget_version_id is null then
1062
1063 GOTO NO_PROCESS1;
1064 IF L_DEBUG = 'Y' THEN
1065 gms_error_pkg.gms_debug('No processing','C');
1066 END IF;
1067
1068 End if;
1069
1070 IF L_DEBUG = 'Y' THEN
1071 gms_error_pkg.gms_debug('Project_id, Award_id, Budget_version_id:'||l_project_id_tbl(i)||','||l_award_id_tbl(i)||','||x_budget_version_id,'C');
1072 END IF;
1073
1074 -- 3. Initialize revenue records
1075
1076 If p_mode ='B' then
1077
1078 x_stage:='Initialize records in baseline mode';
1079
1080 /*intialize_revenue_records(p_award_id,p_project_id,x_e_code,St_Err_Buff); commented and added below line as part of the bug 5481465 */
1081 intialize_revenue_records(l_award_id_tbl(i),l_project_id_tbl(i),x_e_code,St_Err_Buff);
1082
1083 End if;
1084
1085 -- 4. Get Amount type, Boundary Code
1086
1087 x_stage:='Get Amount Type, Boundary Code';
1088
1089 Begin
1090 if (nvl(x_old_award_id,-1) <> l_award_id_tbl(i)) then --added for the bug 5481465
1091 Select amount_type, boundary_code
1092 Into x_amount_type, x_boundary_code
1093 From gms_awards_all
1094 Where award_id = l_award_id_tbl(i); --p_award_id; for bug 5481465
1095
1096 End If; /* for bug 5481465 */
1097 Exception
1098
1099 When others then
1100 RAISE;
1101 -- Will call main exception
1102 End;
1103
1104 IF L_DEBUG = 'Y' THEN
1105 gms_error_pkg.gms_debug('Amount_type,Boundary_code:'|| x_amount_type||','||x_boundary_code,'C');
1106 END IF;
1107
1108 -- 5. Get Resource List Id and Categorization Code
1109
1110 x_stage:='Get Resource_List_Id, Categorization,TPC';
1111
1112 Begin
1113
1114 Select a.resource_list_id,
1115 b.categorization_code,
1116 b.time_phased_type_code,
1117 b.entry_level_code --2673200
1118 into x_resource_list_id,
1119 x_categorization_code,
1120 x_time_phased_type_code ,
1121 x_entry_level_code --2673200
1122 from gms_budget_versions a,
1123 pa_budget_entry_methods b
1124 where a.budget_version_id = x_budget_version_id
1125 and b.budget_entry_method_code = a.budget_entry_method_code;
1126
1127 Exception
1128
1129 When Others then
1130 RAISE;
1131
1132 End;
1133
1134 -- 6. Get RLMI for Uncategorized resources.
1135 If x_categorization_code <> 'R' then
1136
1137 x_stage:='Get RLMI for Uncategorized';
1138
1139 Begin
1140
1141 select resource_list_member_id
1142 into x_uncategorized_rlmi
1143 from pa_resource_list_members
1144 where resource_list_id = x_resource_list_id
1145 and NVL(migration_code,'M') ='M'; -- Bug 3626671
1146
1147 Exception
1148
1149 When Others then
1150 RAISE;
1151 End;
1152
1153 End if; --If x_categorization_code <> 'R' then
1154 End If; --if (nvl(x_old_award_id,-1) <> l_award_id_tbl(i) and nvl(x_old_project_id,-1) <> l_project_id_tbl(i)) for bug 5481465
1155
1156 -- 4. Revenue Txns. processing
1157
1158 /*for rev_gen in gms_rev
1159 loop */ /* commented for the bug 5481465 */
1160
1161 IF L_DEBUG = 'Y' THEN
1162 gms_error_pkg.gms_debug('Expenditure_item_id,Adl_line_Num:'||l_ei_id_tbl(i)||','||l_adl_ln_num_id_tbl(i),'C');
1163 gms_error_pkg.gms_debug('Amount,Table:'||l_amount_tbl(i)||','||l_fr_tab_tbl(i),'C');
1164 END IF;
1165
1166 x_stage :='Get Set of Books Id';
1167 -- x_set_of_books_id := p_sob_id; /* Commented for bug 9134876*/
1168
1169 /* Added for bug 9134876*/
1170 if p_mode <> 'B' and (nvl(x_old_award_id,-1) <> l_award_id_tbl(i)) then
1171 OPEN c_get_sob (l_award_id_tbl(i));
1172 FETCH c_get_sob INTO x_set_of_books_id;
1173 CLOSE c_get_sob ;
1174 end if;
1175
1176
1177 -- 4a. Calculate RLMI
1178
1179 x_stage:='Get RLMI';
1180
1181 If x_categorization_code <> 'R' then -- Uncategorized value 'N'
1182
1183 x_resource_list_member_id := x_uncategorized_rlmi;
1184
1185 Elsif x_categorization_code = 'R' then
1186
1187 /* x_resource_list_member_id:=rev_gen.rlmi; as part of bug and added below line */
1188 x_resource_list_member_id:=l_rlmi_tbl(i);
1189
1190 If x_resource_list_member_id is null then
1191
1192 x_stage:='Get RLMI for Categorized';
1193
1194 gms_res_map.map_resources('EXP', --x_document_type,
1195 l_ei_id_tbl(i),--rev_gen.expenditure_item_id, --x_document_header_id /* changed parameter for the bug 5481465 */
1196 l_cdl_ln_num_id_tbl(i),--rev_gen.cdl_line_num, --x_document_distribution_id /* changed parameter for the bug 5481465 */
1197 l_exp_type_tbl(i),--rev_gen.expenditure_type, --x_expenditure_type /* changed parameter for the bug 5481465 */
1198 l_org_id_tbl(i),--rev_gen.exp_org_id, --x_expenditure_org_id /* changed parameter for the bug 5481465 */
1199 'R', --x_categorization_code
1200 x_resource_list_id, --x_resource_list_id
1201 NULL, --x_event_type
1202 x_prev_list_processed,
1203 x_group_resource_type_id,
1204 x_group_resource_type_name,
1205 x_resource_type_tab,
1206 x_resource_list_member_id,
1207 x_e_code,
1208 St_Err_Buff);
1209
1210
1211 End If;
1212
1213 End if; --if x_categorization_code = .....
1214
1215 IF L_DEBUG = 'Y' THEN
1216 gms_error_pkg.gms_debug('RLMI :'||x_resource_list_member_id,'C');
1217 END IF;
1218
1219 if x_resource_list_member_id is null then
1220
1221 l_count:=error_table.COUNT;
1222 l_count:=l_count+1;
1223
1224 error_table(l_count).exp_item_id :=l_ei_id_tbl(i);--rev_gen.expenditure_item_id; /* As part of the bug 5481465 */
1225 error_table(l_count).adl :=l_adl_ln_num_id_tbl(i);--rev_gen.adl_line_num; /* As part of the bug 5481465 */
1226 error_table(l_count).award_id := l_award_id_tbl(i);--rev_gen.award_id; /* As part of the bug 5481465 */
1227 error_table(l_count).project_id :=l_project_id_tbl(i);--rev_gen.project_id; /* As part of the bug 5481465 */
1228 error_table(l_count).task_id := l_task_id_tbl(i);--rev_gen.task_id; /* As part of the bug 5481465 */
1229 error_table(l_count).amount:=l_amount_tbl(i);--rev_gen.amount; /* As part of the bug 5481465 */
1230 error_table(l_count).reason:= 'Revenue Item: Resource mapping Failure';
1231
1232 reason_table(l_count).resource_list_id:=x_resource_list_id;
1233 reason_table(l_count).org_id:=l_org_id_tbl(i);--rev_gen.exp_org_id; /* As part of the bug 5481465 */
1234 reason_table(l_count).expenditure_type:=l_exp_type_tbl(i);--rev_gen.expenditure_type; /* As part of the bug 5481465 */
1235 reason_table(l_count).exp_date:=l_ei_date_tbl(i);--rev_gen.expenditure_item_date; /* As part of the bug 5481465 */
1236 reason_table(l_count).bvid:=x_budget_version_id;
1237 reason_table(l_count).bud_task:=l_bud_task_id_tbl(i);--rev_gen.bud_task_id; /* As part of the bug 5481465 */
1238 reason_table(l_count).cat_code:=x_categorization_code;
1239 reason_table(l_count).tp_code:=x_time_phased_type_code;
1240
1241 GOTO NO_PROCESS;
1242
1243 end if;
1244
1245 -- 4c. Update balance
1246
1247 x_stage:='Update balance';
1248
1249 UPDATE gms_balances gb
1250 set revenue_period_to_date = nvl(revenue_period_to_date,0) + nvl(l_amount_tbl(i),0) --nvl(rev_gen.amount,0) as part of the bug 5481465
1251 WHERE gb.project_id = l_project_id_tbl(i) --rev_gen.project_id as part of the bug 5481465
1252 AND gb.award_id = l_award_id_tbl(i)--rev_gen.award_id as part of the bug 5481465
1253 AND (gb.task_id = l_task_id_tbl(i) --rev_gen.task_id as part of the bug 5481465
1254 or gb.task_id is null)
1255 AND (gb.resource_list_member_id = x_resource_list_member_id
1256 or gb.resource_list_member_id is null)
1257 AND gb.set_of_books_id = x_set_of_books_id
1258 AND gb.budget_version_id = x_budget_version_id
1259 AND gb.balance_type = 'REV'
1260 AND /*rev_gen.expenditure_item_date for bug 5481465*/l_ei_date_tbl(i) between gb.start_date and gb.end_date
1261 AND rownum = 1;
1262
1263
1264
1265 IF (SQL%NOTFOUND) THEN
1266
1267 IF L_DEBUG = 'Y' THEN
1268 gms_error_pkg.gms_debug('No Balance Line Updated','C');
1269 END IF;
1270
1271 -- 4d. Calculate Start and End date.
1272
1273 x_stage:='Calculate Dates';
1274
1275 if x_time_phased_type_code = 'G' then
1276 select gps.start_date, gps.end_date
1277 into x_start_date, x_end_date
1278 from gl_period_statuses gps
1279 where gps.application_id = 101
1280 and gps.set_of_books_id = x_set_of_books_id
1281 and /*rev_gen.expenditure_item_date for bug 5481465*/ l_ei_date_tbl(i) between gps.start_date and gps.end_date
1282 and gps.adjustment_period_flag = 'N';
1283
1284 elsif x_time_phased_type_code = 'P' then
1285 select start_date , end_date
1286 into x_start_date , x_end_date
1287 from pa_periods gpa /* Bug 6721990: Replaced pa_periods_all with pa_periods */
1288 where /* rev_gen.expenditure_item_date for bug 5481465 */ l_ei_date_tbl(i) between gpa.start_date and gpa.end_date;
1289
1290 elsif x_time_phased_type_code in ('R','N') then
1291
1292 -- get the dates for the period from balances. there should always be
1293 -- a record in balances as actuals are already posted. Bug 3487431
1294
1295 select gb.start_date, gb.end_date
1296 into x_start_date, x_end_date
1297 from gms_balances gb
1298 where gb.budget_version_id = x_budget_version_id
1299 and /* rev_gen.expenditure_item_date for bug 5481465 */ l_ei_date_tbl(i) between
1300 gb.start_date and gb.end_date
1301 and rownum = 1;
1302
1303 end if;
1304
1305 If x_start_date is null then
1306
1307 l_count:=error_table.COUNT;
1308 l_count:=l_count+1;
1309
1310 error_table(l_count).exp_item_id :=l_ei_id_tbl(i);--rev_gen.expenditure_item_id; /* For bug 5481465 */
1311 error_table(l_count).adl :=l_adl_ln_num_id_tbl(i);--rev_gen.adl_line_num; /* For bug 5481465 */
1312 error_table(l_count).award_id := l_award_id_tbl(i);--rev_gen.award_id; /* For bug 5481465 */
1313 error_table(l_count).project_id :=l_project_id_tbl(i);--rev_gen.project_id; /* For bug 5481465 */
1314 error_table(l_count).task_id := l_task_id_tbl(i);--rev_gen.task_id; /* For bug 5481465 */
1315 error_table(l_count).amount:=l_amount_tbl(i);--rev_gen.amount; /* For bug 5481465 */
1316 error_table(l_count).reason:= 'Revenue Item: Start Date Null';
1317
1318 reason_table(l_count).resource_list_id:=x_resource_list_id;
1319 /*reason_table(l_count).org_id:=rev_gen.exp_org_id;
1320 reason_table(l_count).expenditure_type:=rev_gen.expenditure_type;
1321 reason_table(l_count).exp_date:=rev_gen.expenditure_item_date; commented and added below lines for bug 5481465*/
1322 reason_table(l_count).org_id:=l_org_id_tbl(i);
1323 reason_table(l_count).expenditure_type:=l_exp_type_tbl(i);
1324 reason_table(l_count).exp_date:=l_ei_date_tbl(i);
1325
1326 reason_table(l_count).bvid:=x_budget_version_id;
1327 reason_table(l_count).bud_task:=l_bud_task_id_tbl(i);--rev_gen.bud_task_id; for bug 5481465
1328 reason_table(l_count).cat_code:=x_categorization_code;
1329 reason_table(l_count).tp_code:=x_time_phased_type_code;
1330 GOTO NO_PROCESS;
1331
1332 END if;
1333
1334 If x_end_date is null then
1335
1336 l_count:=error_table.COUNT;
1337 l_count:=l_count+1;
1338
1339 error_table(l_count).exp_item_id :=l_ei_id_tbl(i);--rev_gen.expenditure_item_id; /* For bug 5481465 */
1340 error_table(l_count).adl :=l_adl_ln_num_id_tbl(i);--rev_gen.adl_line_num; /* For bug 5481465 */
1341 error_table(l_count).award_id := l_award_id_tbl(i);--rev_gen.award_id; /* For bug 5481465 */
1342 error_table(l_count).project_id :=l_project_id_tbl(i);--rev_gen.project_id; /* For bug 5481465 */
1343 error_table(l_count).task_id :=l_task_id_tbl(i);-- rev_gen.task_id; /* For bug 5481465 */
1344 error_table(l_count).amount:=l_amount_tbl(i);--rev_gen.amount; /* For bug 5481465 */
1345 error_table(l_count).reason:= 'Revenue Item: End Date Null';
1346
1347 reason_table(l_count).resource_list_id:=x_resource_list_id;
1348 /*reason_table(l_count).org_id:=rev_gen.exp_org_id;
1349 reason_table(l_count).expenditure_type:=rev_gen.expenditure_type;
1350 reason_table(l_count).exp_date:=rev_gen.expenditure_item_date; commented and added below lines for bug 5481465*/
1351 reason_table(l_count).org_id:=l_org_id_tbl(i);
1352 reason_table(l_count).expenditure_type:=l_exp_type_tbl(i);
1353 reason_table(l_count).exp_date:=l_ei_date_tbl(i);
1354
1355
1356 reason_table(l_count).bvid:=x_budget_version_id;
1357 reason_table(l_count).bud_task:=l_bud_task_id_tbl(i);--rev_gen.bud_task_id; /* For bug 5481465 */
1358 reason_table(l_count).cat_code:=x_categorization_code;
1359 reason_table(l_count).tp_code:=x_time_phased_type_code;
1360
1361 GOTO NO_PROCESS;
1362
1363 END if;
1364
1365 x_stage:='Insert New Balance';
1366
1367 insert into gms_balances (project_id
1368 ,award_id
1369 ,task_id
1370 ,resource_list_member_id
1371 ,set_of_books_id
1372 ,budget_Version_id
1373 ,balance_type
1374 ,last_update_date
1375 ,last_updated_by
1376 ,created_by
1377 ,creation_date
1378 ,last_update_login
1379 ,start_date
1380 ,end_date
1381 ,parent_member_id
1382 ,revenue_period_to_date
1383 )
1384 values
1385 (l_project_id_tbl(i)--rev_gen.project_id /* For bug 5481465 */
1386 ,l_award_id_tbl(i)--rev_gen.award_id /* For bug 5481465 */
1387 ,l_task_id_tbl(i)--rev_gen.task_id /* For bug 5481465 */
1388 ,x_resource_list_member_id
1389 ,x_set_of_books_id
1390 ,x_budget_version_id
1391 ,'REV'
1392 ,sysdate
1393 ,FND_GLOBAL.USER_ID
1394 ,FND_GLOBAL.USER_ID
1395 ,sysdate
1396 ,FND_GLOBAL.LOGIN_ID
1397 ,x_start_date
1398 ,x_end_date
1399 ,l_prnt_member_id_tbl(i) --rev_gen.parent
1400 ,l_amount_tbl(i) --rev_gen.amount
1401 );
1402
1403 IF L_DEBUG = 'Y' THEN
1404 gms_error_pkg.gms_debug('After Balance Line Insert','C');
1405 END IF;
1406 end if;
1407
1408 x_stage:='Updating revenue records';
1409
1410 --2. Update the resource_accumulated = 'Y'
1411 /*if rev_gen.from_table = 'GEI' then commented and added below condition for bug 5481465*/
1412 if l_fr_tab_tbl(i) = 'GEI' then
1413 update gms_event_intersect
1414 set revenue_accumulated = 'Y'
1415 where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
1416 /*elsif rev_gen.from_table = 'GBC' then commented and added below condition for bug 5481465*/
1417 elsif l_fr_tab_tbl(i) = 'GBC' then
1418 update gms_burden_components
1419 set revenue_accumulated = 'Y'
1420 where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
1421 /*elsif rev_gen.from_table = 'GBI' then commented and added below condition for bug 5481465*/
1422 elsif l_fr_tab_tbl(i) = 'GBI' then
1423 delete from gms_billing_cancellations
1424 where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
1425 end if;
1426 x_old_award_id := l_award_id_tbl(i) ;
1427 x_old_project_id := l_project_id_tbl(i);
1428
1429 <<NO_PROCESS>>
1430 NULL;
1431 end loop;
1432 -- start of the bug 5481465
1433 l_project_id_tbl.delete;
1434 l_award_id_tbl.delete;
1435 l_adl_ln_num_id_tbl.delete;
1436 l_cdl_ln_num_id_tbl.delete;
1437 l_task_id_tbl.delete;
1438 l_bud_task_id_tbl.delete;
1439 l_ei_date_tbl.delete;
1440 l_rlmi_tbl.delete;
1441 l_prnt_member_id_tbl.delete;
1442 l_ei_id_tbl.delete;
1443 l_amount_tbl.delete;
1444 l_fr_tab_tbl.delete;
1445 l_exp_type_tbl.delete;
1446 l_org_id_tbl.delete;
1447 If (l_rowid_tbl.COUNT < v_max_size) then
1448 l_rowid_tbl.delete;
1449 exit;
1450 Else
1451 l_rowid_tbl.delete;
1452 End If;
1453
1454 -- End Loop; -- Cursor loop
1455
1456 If p_mode ='B' then
1457 EXIT WHEN gms_rev1%NOTFOUND;
1458 Else
1459 EXIT WHEN gms_rev2%NOTFOUND;
1460 END IF;
1461 end loop;
1462
1463 If p_mode ='B' then
1464 CLOSE gms_rev1;
1465 Else
1466 CLOSE gms_rev2;
1467 End If;
1468
1469 --End of bug 5481465
1470
1471 <<NO_PROCESS1>>
1472 NULL;
1473 exception
1474 when others then
1475 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1476 'SQLCODE',
1477 SQLCODE,
1478 'SQLERRM',
1479 SQLERRM,
1480 X_token_name5 => 'PROGRAM_NAME',
1481 X_token_val5 => 'GMS_SWEEPER.update_revenue_balance, Stage: '|| X_Stage,
1482 X_Exec_Type => 'C',
1483 X_Err_Code => St_Err_Code,
1484 X_Err_Buff => St_Err_Buff);
1485 end update_revenue_balance;
1486 /* -------------------------------------------------------------
1487 ************* REVENUE UPDATION PROCEDURE ENDS *************
1488 ------------------------------------------------------------- */
1489 -------------------------------------------------------------------
1490 -- MAIN BEGIN STARTS HERE ---
1491
1492 BEGIN
1493
1494 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
1495
1496 -- Initialize error tables
1497 upd_error_table.delete;
1498 upd_reason_table.delete;
1499
1500 if x_mode = 'B' then --> baseline calls with mode = 'B'
1501 x_status_code := 'B';
1502 else --> mode = 'U'
1503 x_status_code := 'A';
1504 end if;
1505
1506 IF L_DEBUG = 'Y' THEN
1507 gms_error_pkg.gms_debug('---------- Budget Balance Process Starts ----','C');
1508
1509 gms_error_pkg.gms_debug('Baselining for Project_ID :'||x_project_id,'C');
1510 gms_error_pkg.gms_debug('Baselining for Award_ID :'||x_award_id,'C');
1511
1512 END IF;
1513
1514 --Bug 4732065 : Shifted the code to fetch set of books id as part of Main cursors
1515
1516 -- Bug 2155790 : Added the following code to fetch the value for l_offset_days from
1517 -- from the profile GMS_PURGE_FUNDS_CHECK_RESULTS .
1518
1519 BEGIN
1520
1521 FND_PROFILE.GET('GMS_PURGE_FUNDS_CHECK_RESULTS', l_offset_days);
1522
1523 IF l_offset_days IS NULL THEN
1524 IF L_DEBUG = 'Y' THEN
1525 gms_error_pkg.gms_debug('Profile GMS_PURGE_FUNDS_CHECK_RESULTS cannot have NULL value','C');
1526 gms_error_pkg.gms_debug('Defaulting the value of GMS_PURGE_FUNDS_CHECK_RESULTS profile to 3','C');
1527 END IF;
1528 l_offset_days :=3 ;
1529 ELSIF (l_offset_days < 1 ) THEN
1530 IF L_DEBUG = 'Y' THEN
1531 gms_error_pkg.gms_debug('Profile GMS_PURGE_FUNDS_CHECK_RESULTS cannot have value '||l_offset_days||' which is less than 1','C');
1532 gms_error_pkg.gms_debug('Defaulting the value of GMS_PURGE_FUNDS_CHECK_RESULTS profile to 3','C');
1533 END IF;
1534 l_offset_days :=3 ;
1535
1536 END IF;
1537
1538 Exception
1539 When value_error then
1540 IF L_DEBUG = 'Y' THEN
1541 gms_error_pkg.gms_debug('Invalid character type value is assigned to Profile GMS_PURGE_FUNDS_CHECK_RESULTS',
1542 'C');
1543 gms_error_pkg.gms_debug('Defaulting the value of GMS_PURGE_FUNDS_CHECK_RESULTS profile to 3','C');
1544 END IF;
1545 l_offset_days :=3;
1546 END;
1547
1548 -- End of code modifications done for bug 2155790
1549
1550
1551 -- Bug 3487431... moved revenue posting from here to after actuals posting.
1552
1553 --## Records would be deleted from gms_bc_packets only in normal 'U' mode
1554 If x_mode <> 'B' then
1555
1556
1557 ---------------------------------------------------------------------------------- +
1558 x_stage := '100'; -- Delete old records from gms_bc_packets
1559 IF L_DEBUG = 'Y' THEN
1560 gms_error_pkg.gms_debug('Deleting old records from GMS','C');
1561 END IF;
1562
1563 -- Bug 2155790 : Removed the trunc function and replaced 3 with l_offset_days
1564 delete from gms_bc_packets
1565 where status_code IN ('R', 'T', 'S', 'F','C','I','P','E','X')
1566 and (sysdate - creation_date) >= l_offset_days;
1567
1568 --R12 Fundscheck Management uptake: Logic added to delete records in P status associated with inactive session.
1569 delete from gms_bc_packets gms
1570 where status_code IN ('P')
1571 and NOT EXISTS (SELECT 'x'
1572 FROM v$session
1573 WHERE audsid = gms.session_id
1574 and Serial# = gms.serial_id);
1575
1576 ---------------------------------------------------------------------------------- +
1577 x_stage := '200'; -- Delete from gms_bc_packet_arrival_order
1578 IF L_DEBUG = 'Y' THEN
1579 gms_error_pkg.gms_debug('Deleting records from arrival order table','C');
1580 END IF;
1581
1582 DELETE gms_bc_packet_arrival_order ao
1583 WHERE NOT EXISTS (
1584 SELECT 1
1585 FROM gms_bc_packets
1586 WHERE packet_id = ao.packet_id
1587 );
1588
1589 COMMIT;
1590
1591 ---------------------------------------------------------------------------------- +
1592 x_stage := '250'; -- delete transactions left in pending state
1593 IF L_DEBUG = 'Y' THEN
1594 gms_error_pkg.gms_debug('Deleting transactions left in pending state','C');
1595 END IF;
1596
1597 gms_funds_control_pkg.delete_pending_txns(retcode,errbuf);
1598 -- Note: Above mentioned procedure will not handle exception , so if there is any error
1599 -- it will fall to when others ...
1600
1601 COMMIT;
1602
1603 End if; -- If x_mode <> 'B' then
1604
1605 -- Bug 4053891 Starts
1606
1607 x_plsql_count :=0;
1608 x_old_budget_version_id :=-999;
1609 x_loop_counter :=0;
1610 -- Do not add any more commits in the program then that is already present --K.Biju
1611
1612 -- Bug 4053891 Ends
1613
1614 x_stage := '300'; -- Before loop of gms_bc_packet
1615 IF L_DEBUG = 'Y' THEN
1616 gms_error_pkg.gms_debug('Start Loop for Updation','C');
1617 gms_error_pkg.gms_debug('----------------------------------------------','C');
1618 END IF;
1619
1620 FOR rec_gms_packets IN c_gms_packets(x_status_code) --Added parameter to fix bug 2138376
1621 LOOP
1622 BEGIN
1623
1624 -- Bug 4053891 Starts
1625 ---------------------------------------------------------------------------------- +
1626 -- Code flow in the loop
1627 -- * Post burden and commit if 100 records have already been processed or
1628 -- budget version id changed. COMMIT after posting.
1629 -- Else
1630 -- * Record burden
1631 -- * Update balance (Not changed)
1632 -- * Update ADL (Not changed)
1633 -- * Update gms_bc_packets
1634 ---------------------------------------------------------------------------------- +
1635 x_stage := '325';
1636 IF L_DEBUG = 'Y' THEN
1637 gms_error_pkg.gms_debug('Check if burden to be posted','C');
1638 END IF;
1639
1640 If x_mode <> 'B' then
1641 If (x_loop_counter = 100 or
1642 (x_old_budget_version_id <> -999 and
1643 x_old_budget_version_id <> rec_gms_packets.budget_version_id)
1644 ) then
1645 -- Batch limit 100 reached or budget version changed:
1646 -- * Post Burden
1647 -- * Initalize variables
1648 -- * Commit
1649 If Tab_Award_exp_burden.exists(1) then
1650 x_plsql_count := Tab_Award_exp_burden.count;
1651 End If;
1652
1653 -- * Post Burden
1654 If x_plsql_count > 0 then
1655
1656 x_stage := '350';
1657 IF L_DEBUG = 'Y' THEN
1658 gms_error_pkg.gms_debug('Posting burden for bvid:'||x_old_budget_version_id,'C');
1659 END IF;
1660
1661 POST_BURDEN_AMOUNTS(x_plsql_count,Tab_Award_exp_burden,x_err_code);
1662
1663 If x_err_code = 'E' then
1664
1665 IF L_DEBUG = 'Y' THEN
1666 gms_error_pkg.gms_debug('Error Code after posting burden(E->Failure):'||x_err_code,'C');
1667 END IF;
1668
1669 ROLLBACK to SAVEPOINT A;
1670 If Tab_Award_exp_burden.exists(1) then
1671 Tab_Award_exp_burden.delete;
1672 End If;
1673 GOTO NO_PROCESS;
1674
1675 End If;
1676
1677 IF L_DEBUG = 'Y' THEN
1678 gms_error_pkg.gms_debug('Burden posted for bvid:'||x_old_budget_version_id,'C');
1679 END IF;
1680
1681 End If; --If x_plsql_count > 0 then
1682
1683 -- * Initalize variables
1684 x_loop_counter := 0;
1685 x_plsql_count := 0;
1686 x_old_budget_version_id := rec_gms_packets.budget_version_id;
1687
1688 If Tab_Award_exp_burden.exists(1) then
1689 Tab_Award_exp_burden.delete;
1690 End If;
1691
1692 -- * Commit
1693 Commit;
1694
1695 End If; -- If burden posting reqd.
1696
1697 SAVEPOINT A;
1698
1699 -- Normal processing ..
1700 x_loop_counter := x_loop_counter + 1;
1701
1702 x_stage := '360';
1703 IF L_DEBUG = 'Y' THEN
1704 gms_error_pkg.gms_debug('Recording burden','C');
1705 END IF;
1706
1707 -- Record Burden amounts
1708 If ((rec_gms_packets.burden_adjustment_flag = 'N' and
1709 rec_gms_packets.parent_bc_packet_id is null)
1710 -- original raw line
1711 OR
1712 (rec_gms_packets.burden_adjustment_flag = 'Y' and
1713 rec_gms_packets.burdenable_raw_cost <> 0)
1714 -- Burden adjustment line (Raw portion)
1715 ) then
1716
1717 -- Lock records ...
1718 If x_loop_counter = 1 then
1719 Lock_budget_versions(rec_gms_packets.budget_version_id);
1720 --Tab_Award_exp_burden := Tab_Award_exp();
1721 End If;
1722
1723 -- Record Burden amounts
1724 Record_burden_amounts(rec_gms_packets.award_id,
1725 rec_gms_packets.expenditure_type,
1726 nvl(rec_gms_packets.entered_dr,0) - nvl(rec_gms_packets.entered_cr,0),
1727 rec_gms_packets.burdenable_raw_cost,
1728 rec_gms_packets.document_type,
1729 Tab_Award_exp_burden,
1730 x_err_code);
1731 If x_err_code = 'E' then
1732 IF L_DEBUG = 'Y' THEN
1733 gms_error_pkg.gms_debug('Burden recording failed for bvid,award_id,expenditure type,doc_type:'||
1734 rec_gms_packets.budget_version_id||','||rec_gms_packets.award_id||
1735 ','||rec_gms_packets.expenditure_type||','||
1736 rec_gms_packets.document_type,'C');
1737 END IF;
1738 ROLLBACK to SAVEPOINT A;
1739 GOTO NO_PROCESS;
1740 End If;
1741 End If;
1742 End If; -- If x_mode <> 'B' then
1743
1744 ---------------------------------------------------------------------------------------------------+
1745 -- Bug 4053891 Ends
1746
1747
1748 x_stage := '400'; -- Update GMS_BALANCES record
1749 IF L_DEBUG = 'Y' THEN
1750 gms_error_pkg.gms_debug('----------------------------------------------','C');
1751 gms_error_pkg.gms_debug('Expenditure --> '||to_char(rec_gms_packets.document_header_id),'C');
1752 gms_error_pkg.gms_debug('Adl --> '||to_char(rec_gms_packets.document_distribution_id),'C');
1753 gms_error_pkg.gms_debug('Award --> '||to_char(rec_gms_packets.award_id),'C');
1754 gms_error_pkg.gms_debug('Project --> '||to_char(rec_gms_packets.project_id),'C');
1755 gms_error_pkg.gms_debug('Task --> '||to_char(rec_gms_packets.task_id),'C');
1756 gms_error_pkg.gms_debug('Budget Version --> '||to_char(rec_gms_packets.budget_version_id),'C');
1757 gms_error_pkg.gms_debug('Document --> '||rec_gms_packets.document_type,'C');
1758 END IF;
1759
1760
1761 /* Bug 5956576: Base Bug 5956576 - Changes start */
1762 /* UPDATE sql commented and rewritten into two separate queries
1763 inside IF..ELSE blocks based on rec_gms_packets.document_type */
1764
1765 /*
1766 UPDATE gms_balances gb
1767 SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
1768 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1769 decode(rec_gms_packets.document_type,'EXP',1,0),
1770 gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
1771 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1772 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1773 WHERE gb.project_id = rec_gms_packets.project_id
1774 AND gb.award_id = rec_gms_packets.award_id
1775 AND ((rec_gms_packets.document_type = 'BGT'
1776 and gb.task_id = rec_gms_packets.bud_task_id)
1777 OR (rec_gms_packets.document_type <>'BGT' -- Bug 2138376 : changed to <> as per GSCC standards
1778 and gb.task_id=rec_gms_packets.task_id))
1779 AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
1780 AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
1781 AND gb.budget_version_id = rec_gms_packets.budget_version_id
1782 AND gb.balance_type = rec_gms_packets.document_type
1783 AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
1784 AND rownum = 1;
1785 */
1786 IF (rec_gms_packets.document_type = 'BGT') THEN
1787
1788 UPDATE gms_balances gb
1789 SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
1790 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1791 decode(rec_gms_packets.document_type,'EXP',1,0),
1792 gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
1793 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1794 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1795 WHERE gb.project_id = rec_gms_packets.project_id
1796 AND gb.award_id = rec_gms_packets.award_id
1797 AND gb.task_id = rec_gms_packets.bud_task_id --Bug5875538 for Perf. Fix
1798 AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
1799 AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
1800 AND gb.budget_version_id = rec_gms_packets.budget_version_id
1801 AND gb.balance_type = rec_gms_packets.document_type
1802 AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
1803 AND rownum = 1;
1804
1805 ELSIF (rec_gms_packets.document_type <>'BGT') THEN
1806
1807 UPDATE gms_balances gb
1808 SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
1809 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1810 decode(rec_gms_packets.document_type,'EXP',1,0),
1811 gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
1812 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1813 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1814 WHERE gb.project_id = rec_gms_packets.project_id
1815 AND gb.award_id = rec_gms_packets.award_id
1816 AND gb.task_id=rec_gms_packets.task_id --Bug5875538 for Perf. Fix
1817 AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
1818 AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
1819 AND gb.budget_version_id = rec_gms_packets.budget_version_id
1820 AND gb.balance_type = rec_gms_packets.document_type
1821 AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
1822 AND rownum = 1;
1823
1824 END IF;
1825
1826 /* Bug 5956576: Base Bug 5955990 - Changes end */
1827
1828 IF (SQL%NOTFOUND) THEN
1829
1830 if rec_gms_packets.time_phased_type_code = 'G' then
1831 select gps.start_date, gps.end_date
1832 into x_st_date, x_ed_date
1833 from gl_period_statuses gps
1834 where gps.application_id = 101
1835 and gps.set_of_books_id = rec_gms_packets.set_of_books_id
1836 and rec_gms_packets.expenditure_item_date between gps.start_date and gps.end_date
1837 and gps.adjustment_period_flag = 'N';
1838
1839 elsif rec_gms_packets.time_phased_type_code = 'P' then
1840 select start_date , end_date
1841 into x_st_date , x_ed_date
1842 from pa_periods gpa --Bug 4732065 /*bug 6660289*/
1843 where rec_gms_packets.expenditure_item_date between gpa.start_date and gpa.end_date;
1844
1845 elsif rec_gms_packets.time_phased_type_code in ('R','N') then
1846
1847 setup_start_end_date(rec_gms_packets.project_id,
1848 rec_gms_packets.award_id,
1849 rec_gms_packets.bud_task_id, -- 2673200
1850 rec_gms_packets.budget_version_id,
1851 rec_gms_packets.time_phased_type_code,
1852 rec_gms_packets.entry_level_code, -- 2673200
1853 rec_gms_packets.expenditure_item_date,
1854 rec_gms_packets.amount_type,
1855 rec_gms_packets.boundary_code,
1856 rec_gms_packets.set_of_books_id,
1857 x_st_date,
1858 x_ed_date,
1859 St_e_code,
1860 x_err_stage);
1861
1862 end if;
1863
1864 If x_st_date is null or x_ed_date is null then
1865
1866 l_counter:=Upd_error_table.COUNT;
1867 l_counter:=l_counter+1;
1868
1869 upd_error_table(l_counter).exp_item_id :=rec_gms_packets.document_header_id;
1870 upd_error_table(l_counter).adl :=rec_gms_packets.document_distribution_id;
1871 upd_error_table(l_counter).award_id := rec_gms_packets.award_id;
1872 upd_error_table(l_counter).project_id :=rec_gms_packets.project_id;
1873 upd_error_table(l_counter).task_id := rec_gms_packets.task_id;
1874 upd_error_table(l_counter).amount:=rec_gms_packets.entered_dr - rec_gms_packets.entered_cr;
1875
1876 upd_reason_table(l_counter).resource_list_id:=rec_gms_packets.resource_list_id;
1877 upd_reason_table(l_counter).person_id:=null;
1878 upd_reason_table(l_counter).job_id:=null;
1879 upd_reason_table(l_counter).org_id:=null;
1880 upd_reason_table(l_counter).expenditure_type:=null;
1881 upd_reason_table(l_counter).nlr:=null;
1882 upd_reason_table(l_counter).exp_category:=null;
1883 upd_reason_table(l_counter).rev_category:=null;
1884 upd_reason_table(l_counter).org_id:=null;
1885 upd_reason_table(l_counter).sys_link:=null;
1886 upd_reason_table(l_counter).exp_date:=rec_gms_packets.expenditure_item_date;
1887 upd_reason_table(l_counter).bvid:=rec_gms_packets.budget_version_id;
1888 upd_reason_table(l_counter).bud_task:=rec_gms_packets.bud_task_id;
1889 upd_reason_table(l_counter).cat_code:=null;
1890 upd_reason_table(l_counter).tp_code:=rec_gms_packets.time_phased_type_code;
1891
1892 upd_error_table(l_counter).reason:= 'Transaction item:';
1893
1894 If x_st_date is null then
1895 upd_error_table(l_counter).reason:= upd_error_table(l_counter).reason||'Start Date Null';
1896 End If;
1897 If x_ed_date is null then
1898 upd_error_table(l_counter).reason:= upd_error_table(l_counter).reason||':End Date Null';
1899 End If;
1900
1901 If x_mode <> 'B' then
1902 ROLLBACK TO SAVEPOINT A;
1903 GOTO NO_PROCESS;
1904 Else
1905 RAISE_APPLICATION_ERROR(-20001,'Could not derive budget period date');
1906 End If;
1907
1908 END if;
1909
1910 x_stage := '800';
1911
1912 -- Insert GMS_BALANCES record where balance record not exist
1913
1914 insert into gms_balances (project_id
1915 ,award_id
1916 ,task_id
1917 ,top_task_id
1918 ,resource_list_member_id
1919 ,set_of_books_id
1920 ,budget_Version_id
1921 ,balance_type
1922 ,last_update_date
1923 ,last_updated_by
1924 ,created_by
1925 ,creation_date
1926 ,last_update_login
1927 ,start_date
1928 ,end_date
1929 ,parent_member_id
1930 ,budget_period_to_date
1931 ,actual_period_to_date
1932 ,encumb_period_to_date
1933 )
1934 values
1935 (rec_gms_packets.project_id
1936 ,rec_gms_packets.award_id
1937 ,decode(rec_gms_packets.document_type,'BGT',rec_gms_packets.bud_task_id,
1938 rec_gms_packets.task_id)
1939 ,rec_gms_packets.top_task_id
1940 ,rec_gms_packets.resource_list_member_id
1941 ,rec_gms_packets.set_of_books_id
1942 ,rec_gms_packets.budget_Version_id
1943 ,rec_gms_packets.document_type
1944 ,sysdate
1945 ,FND_GLOBAL.USER_ID
1946 ,FND_GLOBAL.USER_ID
1947 ,sysdate
1948 ,FND_GLOBAL.LOGIN_ID
1949 ,x_st_date
1950 ,x_ed_date
1951 ,rec_gms_packets.parent_resource_id
1952 ,0
1953 ,(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1954 decode(rec_gms_packets.document_type,'EXP',1,0)
1955 ,(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1956 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1957 );
1958
1959
1960 END IF; -- sql%notfound
1961
1962 x_stage := '900'; -- Update gms_bc_packet record status to 'X'
1963 update gms_bc_packets set status_code = 'X'
1964 WHERE rowid=rec_gms_packets.rowid;
1965
1966 -- Changed to rowid criteria, bug 4053891
1967
1968 -------------------------------------------------------------------
1969 --Update adl for accumulated_flag for mode <> B
1970 -------------------------------------------------------------------
1971
1972 if x_mode <> 'B' then
1973 if rec_gms_packets.document_type in ('EXP','ENC') then
1974
1975 update gms_award_distributions
1976 set accumulated_flag='Y'
1977 where expenditure_item_id = rec_gms_packets.document_header_id
1978 and rec_gms_packets.document_distribution_id = decode(rec_gms_packets.document_type, --Bug 5726575
1979 'EXP', cdl_line_num,
1980 'ENC', adl_line_num)
1981 and nvl(reversed_flag, 'N') = 'N' --Bug 5726575
1982 and line_num_reversed is null
1983 and document_type=rec_gms_packets.document_type
1984 and award_id=rec_gms_packets.award_id
1985 and project_id = rec_gms_packets.project_id
1986 and task_id = rec_gms_packets.task_id
1987 and adl_status='A'
1988 and cost_distributed_flag='Y'
1989 and fc_status='A';
1990
1991 elsif rec_gms_packets.document_type ='PO' then
1992
1993 update gms_award_distributions
1994 set accumulated_flag='Y'
1995 where po_distribution_id = rec_gms_packets.document_distribution_id
1996 and document_type=rec_gms_packets.document_type
1997 and award_id=rec_gms_packets.award_id
1998 and project_id = rec_gms_packets.project_id
1999 and task_id = rec_gms_packets.task_id
2000 and adl_status='A'
2001 and fc_status='A';
2002
2003 elsif rec_gms_packets.document_type ='REQ' then
2004
2005 update gms_award_distributions
2006 set accumulated_flag='Y'
2007 where distribution_id = rec_gms_packets.document_distribution_id
2008 and document_type=rec_gms_packets.document_type
2009 and award_id=rec_gms_packets.award_id
2010 and project_id = rec_gms_packets.project_id
2011 and task_id = rec_gms_packets.task_id
2012 and adl_status='A'
2013 and fc_status='A';
2014
2015 elsif rec_gms_packets.document_type ='AP' then
2016
2017 update gms_award_distributions
2018 set accumulated_flag='Y'
2019 where invoice_id = rec_gms_packets.document_header_id
2020 -- AP Lines uptake: changed join from with distribution num to distribution id
2021 and invoice_distribution_id = rec_gms_packets.document_distribution_id
2022 and document_type=rec_gms_packets.document_type
2023 and award_id=rec_gms_packets.award_id
2024 and project_id = rec_gms_packets.project_id
2025 and task_id = rec_gms_packets.task_id
2026 and adl_status='A'
2027 and fc_status='A';
2028
2029 end if;
2030
2031 end if;
2032
2033
2034 EXCEPTION
2035 When others then
2036 IF L_DEBUG = 'Y' THEN
2037 gms_error_pkg.gms_debug('Stage:'||x_stage||';'||substr(sqlerrm,1,255),'C');
2038 END IF;
2039 If x_mode <> 'B' then
2040 ROLLBACK TO SAVEPOINT A;
2041 -- If Sweeper then rollback and continue
2042 Else
2043 RAISE;
2044 -- If baseline then fail process ..
2045 End if;
2046 END;
2047
2048 <<NO_PROCESS>>
2049 NULL;
2050
2051
2052 END LOOP;
2053
2054 -- Bug 4053891 .. for last set of data .. Start
2055 If x_mode <> 'B' then
2056 If x_loop_counter > 1 then
2057 -- * Post Burden
2058 -- * Initalize variables
2059 -- * Commit
2060
2061 If Tab_Award_exp_burden.exists(1) then
2062 x_plsql_count := Tab_Award_exp_burden.count;
2063 End If;
2064
2065 -- * Post Burden
2066 If x_plsql_count > 0 then
2067
2068 post_burden_amounts(x_plsql_count,Tab_Award_exp_burden,x_err_code);
2069
2070 If x_err_code = 'E' then
2071 ROLLBACK; -- not to savepoint
2072 End If;
2073 End If;
2074
2075 -- * Initalize variables
2076 x_loop_counter := 0;
2077 x_plsql_count := 0;
2078 -- * Commit
2079 Commit;
2080 End If;
2081 End if;
2082
2083 -- Bug 4053891 .. for last set of data .. End
2084
2085 -- Moved Revenue posting to after actuals ...bug 3487431
2086
2087 -- Call update_revenue_balance.
2088 IF L_DEBUG = 'Y' THEN
2089 gms_error_pkg.gms_debug('Calling Revenue Updation','C');
2090 gms_error_pkg.gms_debug('Mode passed for revenue updation '||x_mode,'C');
2091 END IF;
2092
2093 -- Bug 2138376 : Modify the following code
2094 -- If called from Baselining process consider records with status 'B' else
2095 --consider records with status 'A'
2096
2097 if x_mode ='B' then
2098 x_status_code :='B'; --Bug 2138376
2099
2100 -- Bug 4732065 : if called from baselining derive value of SOB from x_award_id which will be NOT NULL
2101
2102 OPEN c_get_sob (x_award_id);
2103 FETCH c_get_sob INTO l_sob_id;
2104 CLOSE c_get_sob ;
2105
2106 update_revenue_balance(x_mode,x_award_id,x_project_id,l_sob_id,upd_error_table,upd_reason_table);
2107
2108 --Update adl accumulated_flag for mode = B
2109 update gms_award_distributions
2110 set accumulated_flag='Y'
2111 where award_id = x_award_id
2112 and project_id = x_project_id
2113 and adl_status='A'
2114 and fc_status='A';
2115
2116 else -- x_mode ='U'
2117 x_status_code :='A'; --Bug 2138376
2118
2119 /* for records in cur_records
2120 loop
2121 Commented for the bug 5481465*/
2122 /* update_revenue_balance(x_mode,records.award_id,records.project_id,records.set_of_books_id, commented and added below line for the bug 5481465*/
2123 update_revenue_balance(x_mode,-1,-1,l_sob_id,
2124 upd_error_table,upd_reason_table);
2125
2126 commit;
2127 -- Added to improve performance ..
2128
2129 -- end loop; commented for the bug 5481465
2130
2131 end if;
2132
2133 IF L_DEBUG = 'Y' THEN
2134 gms_error_pkg.gms_debug('Revenue Updation Completed','C');
2135 END IF;
2136
2137 -- ...bug 3487431 end.
2138
2139
2140
2141 error_output(upd_error_table,upd_reason_table);
2142
2143 retcode := 0; -- Changed from 'S' to 0 (zero) for Bug:2464800
2144 commit;
2145 EXCEPTION
2146 when OTHERS then
2147 retcode := 2; -- Changed from 'E' to 2 for Bug:2464800
2148 errbuf := (x_stage||' '||SQLCODE||' '||SQLERRM);
2149 --dbms_output.put_line('failed at when others'||SQLCODE||SQLERRM);
2150 END upd_act_enc_bal;
2151 END GMS_SWEEPER;