[Home] [Help]
PACKAGE BODY: APPS.GMS_SWEEPER
Source
1 PACKAGE BODY GMS_SWEEPER AS
2 -- $Header: gmsfcuab.pls 120.9.12010000.2 2008/08/25 09:22:04 mumohan 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 if (nvl(x_old_award_id,-1) <> l_award_id_tbl(i)
1033 and nvl(x_old_project_id,-1) <> l_project_id_tbl(i)) then
1034
1035 --End of the bug 5481465
1036
1037 -- 2. Get budget version id
1038
1039 x_stage:='Get Budget Version Id';
1040
1041 Begin
1042
1043 select budget_version_id
1044 into x_budget_version_id
1045 from gms_budget_versions
1046 where project_id = l_project_id_tbl(i) -- p_project_id as part of the bug 5481465
1047 and award_id = l_award_id_tbl(i) -- p_award_id as part of the bug 5481465
1048 and budget_type_code ='AC'
1049 and budget_status_code = 'B'
1050 and current_flag='Y';
1051 Exception
1052 when others then
1053
1054 x_budget_version_id := NULL;
1055
1056 End;
1057
1058 If x_budget_version_id is null then
1059
1060 GOTO NO_PROCESS1;
1061 IF L_DEBUG = 'Y' THEN
1062 gms_error_pkg.gms_debug('No processing','C');
1063 END IF;
1064
1065 End if;
1066
1067 IF L_DEBUG = 'Y' THEN
1068 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');
1069 END IF;
1070
1071 -- 3. Initialize revenue records
1072
1073 If p_mode ='B' then
1074
1075 x_stage:='Initialize records in baseline mode';
1076
1077 /*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 */
1078 intialize_revenue_records(l_award_id_tbl(i),l_project_id_tbl(i),x_e_code,St_Err_Buff);
1079
1080 End if;
1081
1082 -- 4. Get Amount type, Boundary Code
1083
1084 x_stage:='Get Amount Type, Boundary Code';
1085
1086 Begin
1087 if (nvl(x_old_award_id,-1) <> l_award_id_tbl(i)) then --added for the bug 5481465
1088 Select amount_type, boundary_code
1089 Into x_amount_type, x_boundary_code
1090 From gms_awards_all
1091 Where award_id = l_award_id_tbl(i); --p_award_id; for bug 5481465
1092
1093 End If; /* for bug 5481465 */
1094 Exception
1095
1096 When others then
1097 RAISE;
1098 -- Will call main exception
1099 End;
1100
1101 IF L_DEBUG = 'Y' THEN
1102 gms_error_pkg.gms_debug('Amount_type,Boundary_code:'|| x_amount_type||','||x_boundary_code,'C');
1103 END IF;
1104
1105 -- 5. Get Resource List Id and Categorization Code
1106
1107 x_stage:='Get Resource_List_Id, Categorization,TPC';
1108
1109 Begin
1110
1111 Select a.resource_list_id,
1112 b.categorization_code,
1113 b.time_phased_type_code,
1114 b.entry_level_code --2673200
1115 into x_resource_list_id,
1116 x_categorization_code,
1117 x_time_phased_type_code ,
1118 x_entry_level_code --2673200
1119 from gms_budget_versions a,
1120 pa_budget_entry_methods b
1121 where a.budget_version_id = x_budget_version_id
1122 and b.budget_entry_method_code = a.budget_entry_method_code;
1123
1124 Exception
1125
1126 When Others then
1127 RAISE;
1128
1129 End;
1130
1131 -- 6. Get RLMI for Uncategorized resources.
1132 If x_categorization_code <> 'R' then
1133
1134 x_stage:='Get RLMI for Uncategorized';
1135
1136 Begin
1137
1138 select resource_list_member_id
1139 into x_uncategorized_rlmi
1140 from pa_resource_list_members
1141 where resource_list_id = x_resource_list_id
1142 and NVL(migration_code,'M') ='M'; -- Bug 3626671
1143
1144 Exception
1145
1146 When Others then
1147 RAISE;
1148 End;
1149
1150 End if; --If x_categorization_code <> 'R' then
1151 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
1152
1153 -- 4. Revenue Txns. processing
1154
1155 /*for rev_gen in gms_rev
1156 loop */ /* commented for the bug 5481465 */
1157
1158 IF L_DEBUG = 'Y' THEN
1159 gms_error_pkg.gms_debug('Expenditure_item_id,Adl_line_Num:'||l_ei_id_tbl(i)||','||l_adl_ln_num_id_tbl(i),'C');
1160 gms_error_pkg.gms_debug('Amount,Table:'||l_amount_tbl(i)||','||l_fr_tab_tbl(i),'C');
1161 END IF;
1162
1163 x_stage :='Get Set of Books Id';
1164 x_set_of_books_id := p_sob_id;
1165
1166 -- 4a. Calculate RLMI
1167
1168 x_stage:='Get RLMI';
1169
1170 If x_categorization_code <> 'R' then -- Uncategorized value 'N'
1171
1172 x_resource_list_member_id := x_uncategorized_rlmi;
1173
1174 Elsif x_categorization_code = 'R' then
1175
1176 /* x_resource_list_member_id:=rev_gen.rlmi; as part of bug and added below line */
1177 x_resource_list_member_id:=l_rlmi_tbl(i);
1178
1179 If x_resource_list_member_id is null then
1180
1181 x_stage:='Get RLMI for Categorized';
1182
1183 gms_res_map.map_resources('EXP', --x_document_type,
1184 l_ei_id_tbl(i),--rev_gen.expenditure_item_id, --x_document_header_id /* changed parameter for the bug 5481465 */
1185 l_cdl_ln_num_id_tbl(i),--rev_gen.cdl_line_num, --x_document_distribution_id /* changed parameter for the bug 5481465 */
1186 l_exp_type_tbl(i),--rev_gen.expenditure_type, --x_expenditure_type /* changed parameter for the bug 5481465 */
1187 l_org_id_tbl(i),--rev_gen.exp_org_id, --x_expenditure_org_id /* changed parameter for the bug 5481465 */
1188 'R', --x_categorization_code
1189 x_resource_list_id, --x_resource_list_id
1190 NULL, --x_event_type
1191 x_prev_list_processed,
1192 x_group_resource_type_id,
1193 x_group_resource_type_name,
1194 x_resource_type_tab,
1195 x_resource_list_member_id,
1196 x_e_code,
1197 St_Err_Buff);
1198
1199
1200 End If;
1201
1202 End if; --if x_categorization_code = .....
1203
1204 IF L_DEBUG = 'Y' THEN
1205 gms_error_pkg.gms_debug('RLMI :'||x_resource_list_member_id,'C');
1206 END IF;
1207
1208 if x_resource_list_member_id is null then
1209
1210 l_count:=error_table.COUNT;
1211 l_count:=l_count+1;
1212
1213 error_table(l_count).exp_item_id :=l_ei_id_tbl(i);--rev_gen.expenditure_item_id; /* As part of the bug 5481465 */
1214 error_table(l_count).adl :=l_adl_ln_num_id_tbl(i);--rev_gen.adl_line_num; /* As part of the bug 5481465 */
1215 error_table(l_count).award_id := l_award_id_tbl(i);--rev_gen.award_id; /* As part of the bug 5481465 */
1216 error_table(l_count).project_id :=l_project_id_tbl(i);--rev_gen.project_id; /* As part of the bug 5481465 */
1217 error_table(l_count).task_id := l_task_id_tbl(i);--rev_gen.task_id; /* As part of the bug 5481465 */
1218 error_table(l_count).amount:=l_amount_tbl(i);--rev_gen.amount; /* As part of the bug 5481465 */
1219 error_table(l_count).reason:= 'Revenue Item: Resource mapping Failure';
1220
1221 reason_table(l_count).resource_list_id:=x_resource_list_id;
1222 reason_table(l_count).org_id:=l_org_id_tbl(i);--rev_gen.exp_org_id; /* As part of the bug 5481465 */
1223 reason_table(l_count).expenditure_type:=l_exp_type_tbl(i);--rev_gen.expenditure_type; /* As part of the bug 5481465 */
1224 reason_table(l_count).exp_date:=l_ei_date_tbl(i);--rev_gen.expenditure_item_date; /* As part of the bug 5481465 */
1225 reason_table(l_count).bvid:=x_budget_version_id;
1226 reason_table(l_count).bud_task:=l_bud_task_id_tbl(i);--rev_gen.bud_task_id; /* As part of the bug 5481465 */
1227 reason_table(l_count).cat_code:=x_categorization_code;
1228 reason_table(l_count).tp_code:=x_time_phased_type_code;
1229
1230 GOTO NO_PROCESS;
1231
1232 end if;
1233
1234 -- 4c. Update balance
1235
1236 x_stage:='Update balance';
1237
1238 UPDATE gms_balances gb
1239 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
1240 WHERE gb.project_id = l_project_id_tbl(i) --rev_gen.project_id as part of the bug 5481465
1241 AND gb.award_id = l_award_id_tbl(i)--rev_gen.award_id as part of the bug 5481465
1242 AND (gb.task_id = l_task_id_tbl(i) --rev_gen.task_id as part of the bug 5481465
1243 or gb.task_id is null)
1244 AND (gb.resource_list_member_id = x_resource_list_member_id
1245 or gb.resource_list_member_id is null)
1246 AND gb.set_of_books_id = x_set_of_books_id
1247 AND gb.budget_version_id = x_budget_version_id
1248 AND gb.balance_type = 'REV'
1249 AND /*rev_gen.expenditure_item_date for bug 5481465*/l_ei_date_tbl(i) between gb.start_date and gb.end_date
1250 AND rownum = 1;
1251
1252
1253
1254 IF (SQL%NOTFOUND) THEN
1255
1256 IF L_DEBUG = 'Y' THEN
1257 gms_error_pkg.gms_debug('No Balance Line Updated','C');
1258 END IF;
1259
1260 -- 4d. Calculate Start and End date.
1261
1262 x_stage:='Calculate Dates';
1263
1264 if x_time_phased_type_code = 'G' then
1265 select gps.start_date, gps.end_date
1266 into x_start_date, x_end_date
1267 from gl_period_statuses gps
1268 where gps.application_id = 101
1269 and gps.set_of_books_id = x_set_of_books_id
1270 and /*rev_gen.expenditure_item_date for bug 5481465*/ l_ei_date_tbl(i) between gps.start_date and gps.end_date
1271 and gps.adjustment_period_flag = 'N';
1272
1273 elsif x_time_phased_type_code = 'P' then
1274 select start_date , end_date
1275 into x_start_date , x_end_date
1276 from pa_periods gpa /* Bug 6721990: Replaced pa_periods_all with pa_periods */
1277 where /* rev_gen.expenditure_item_date for bug 5481465 */ l_ei_date_tbl(i) between gpa.start_date and gpa.end_date;
1278
1279 elsif x_time_phased_type_code in ('R','N') then
1280
1281 -- get the dates for the period from balances. there should always be
1282 -- a record in balances as actuals are already posted. Bug 3487431
1283
1284 select gb.start_date, gb.end_date
1285 into x_start_date, x_end_date
1286 from gms_balances gb
1287 where gb.budget_version_id = x_budget_version_id
1288 and /* rev_gen.expenditure_item_date for bug 5481465 */ l_ei_date_tbl(i) between
1289 gb.start_date and gb.end_date
1290 and rownum = 1;
1291
1292 end if;
1293
1294 If x_start_date is null then
1295
1296 l_count:=error_table.COUNT;
1297 l_count:=l_count+1;
1298
1299 error_table(l_count).exp_item_id :=l_ei_id_tbl(i);--rev_gen.expenditure_item_id; /* For bug 5481465 */
1300 error_table(l_count).adl :=l_adl_ln_num_id_tbl(i);--rev_gen.adl_line_num; /* For bug 5481465 */
1301 error_table(l_count).award_id := l_award_id_tbl(i);--rev_gen.award_id; /* For bug 5481465 */
1302 error_table(l_count).project_id :=l_project_id_tbl(i);--rev_gen.project_id; /* For bug 5481465 */
1303 error_table(l_count).task_id := l_task_id_tbl(i);--rev_gen.task_id; /* For bug 5481465 */
1304 error_table(l_count).amount:=l_amount_tbl(i);--rev_gen.amount; /* For bug 5481465 */
1305 error_table(l_count).reason:= 'Revenue Item: Start Date Null';
1306
1307 reason_table(l_count).resource_list_id:=x_resource_list_id;
1308 /*reason_table(l_count).org_id:=rev_gen.exp_org_id;
1309 reason_table(l_count).expenditure_type:=rev_gen.expenditure_type;
1310 reason_table(l_count).exp_date:=rev_gen.expenditure_item_date; commented and added below lines for bug 5481465*/
1311 reason_table(l_count).org_id:=l_org_id_tbl(i);
1312 reason_table(l_count).expenditure_type:=l_exp_type_tbl(i);
1313 reason_table(l_count).exp_date:=l_ei_date_tbl(i);
1314
1315 reason_table(l_count).bvid:=x_budget_version_id;
1316 reason_table(l_count).bud_task:=l_bud_task_id_tbl(i);--rev_gen.bud_task_id; for bug 5481465
1317 reason_table(l_count).cat_code:=x_categorization_code;
1318 reason_table(l_count).tp_code:=x_time_phased_type_code;
1319 GOTO NO_PROCESS;
1320
1321 END if;
1322
1323 If x_end_date is null then
1324
1325 l_count:=error_table.COUNT;
1326 l_count:=l_count+1;
1327
1328 error_table(l_count).exp_item_id :=l_ei_id_tbl(i);--rev_gen.expenditure_item_id; /* For bug 5481465 */
1329 error_table(l_count).adl :=l_adl_ln_num_id_tbl(i);--rev_gen.adl_line_num; /* For bug 5481465 */
1330 error_table(l_count).award_id := l_award_id_tbl(i);--rev_gen.award_id; /* For bug 5481465 */
1331 error_table(l_count).project_id :=l_project_id_tbl(i);--rev_gen.project_id; /* For bug 5481465 */
1332 error_table(l_count).task_id :=l_task_id_tbl(i);-- rev_gen.task_id; /* For bug 5481465 */
1333 error_table(l_count).amount:=l_amount_tbl(i);--rev_gen.amount; /* For bug 5481465 */
1334 error_table(l_count).reason:= 'Revenue Item: End Date Null';
1335
1336 reason_table(l_count).resource_list_id:=x_resource_list_id;
1337 /*reason_table(l_count).org_id:=rev_gen.exp_org_id;
1338 reason_table(l_count).expenditure_type:=rev_gen.expenditure_type;
1339 reason_table(l_count).exp_date:=rev_gen.expenditure_item_date; commented and added below lines for bug 5481465*/
1340 reason_table(l_count).org_id:=l_org_id_tbl(i);
1341 reason_table(l_count).expenditure_type:=l_exp_type_tbl(i);
1342 reason_table(l_count).exp_date:=l_ei_date_tbl(i);
1343
1344
1345 reason_table(l_count).bvid:=x_budget_version_id;
1346 reason_table(l_count).bud_task:=l_bud_task_id_tbl(i);--rev_gen.bud_task_id; /* For bug 5481465 */
1347 reason_table(l_count).cat_code:=x_categorization_code;
1348 reason_table(l_count).tp_code:=x_time_phased_type_code;
1349
1350 GOTO NO_PROCESS;
1351
1352 END if;
1353
1354 x_stage:='Insert New Balance';
1355
1356 insert into gms_balances (project_id
1357 ,award_id
1358 ,task_id
1359 ,resource_list_member_id
1360 ,set_of_books_id
1361 ,budget_Version_id
1362 ,balance_type
1363 ,last_update_date
1364 ,last_updated_by
1365 ,created_by
1366 ,creation_date
1367 ,last_update_login
1368 ,start_date
1369 ,end_date
1370 ,parent_member_id
1371 ,revenue_period_to_date
1372 )
1373 values
1374 (l_project_id_tbl(i)--rev_gen.project_id /* For bug 5481465 */
1375 ,l_award_id_tbl(i)--rev_gen.award_id /* For bug 5481465 */
1376 ,l_task_id_tbl(i)--rev_gen.task_id /* For bug 5481465 */
1377 ,x_resource_list_member_id
1378 ,x_set_of_books_id
1379 ,x_budget_version_id
1380 ,'REV'
1381 ,sysdate
1382 ,FND_GLOBAL.USER_ID
1383 ,FND_GLOBAL.USER_ID
1384 ,sysdate
1385 ,FND_GLOBAL.LOGIN_ID
1386 ,x_start_date
1387 ,x_end_date
1388 ,l_prnt_member_id_tbl(i) --rev_gen.parent
1389 ,l_amount_tbl(i) --rev_gen.amount
1390 );
1391
1392 IF L_DEBUG = 'Y' THEN
1393 gms_error_pkg.gms_debug('After Balance Line Insert','C');
1394 END IF;
1395 end if;
1396
1397 x_stage:='Updating revenue records';
1398
1399 --2. Update the resource_accumulated = 'Y'
1400 /*if rev_gen.from_table = 'GEI' then commented and added below condition for bug 5481465*/
1401 if l_fr_tab_tbl(i) = 'GEI' then
1402 update gms_event_intersect
1403 set revenue_accumulated = 'Y'
1404 where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
1405 /*elsif rev_gen.from_table = 'GBC' then commented and added below condition for bug 5481465*/
1406 elsif l_fr_tab_tbl(i) = 'GBC' then
1407 update gms_burden_components
1408 set revenue_accumulated = 'Y'
1409 where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
1410 /*elsif rev_gen.from_table = 'GBI' then commented and added below condition for bug 5481465*/
1411 elsif l_fr_tab_tbl(i) = 'GBI' then
1412 delete from gms_billing_cancellations
1413 where rowid = l_rowid_tbl(i);--rev_gen.rowid; for bug 5481465
1414 end if;
1415 x_old_award_id := l_award_id_tbl(i) ;
1416 x_old_project_id := l_project_id_tbl(i);
1417
1418 <<NO_PROCESS>>
1419 NULL;
1420 end loop;
1421 -- start of the bug 5481465
1422 l_project_id_tbl.delete;
1423 l_award_id_tbl.delete;
1424 l_adl_ln_num_id_tbl.delete;
1425 l_cdl_ln_num_id_tbl.delete;
1426 l_task_id_tbl.delete;
1427 l_bud_task_id_tbl.delete;
1428 l_ei_date_tbl.delete;
1429 l_rlmi_tbl.delete;
1430 l_prnt_member_id_tbl.delete;
1431 l_ei_id_tbl.delete;
1432 l_amount_tbl.delete;
1433 l_fr_tab_tbl.delete;
1434 l_exp_type_tbl.delete;
1435 l_org_id_tbl.delete;
1436 If (l_rowid_tbl.COUNT < v_max_size) then
1437 l_rowid_tbl.delete;
1438 exit;
1439 Else
1440 l_rowid_tbl.delete;
1441 End If;
1442
1443 -- End Loop; -- Cursor loop
1444
1445 If p_mode ='B' then
1446 EXIT WHEN gms_rev1%NOTFOUND;
1447 Else
1448 EXIT WHEN gms_rev2%NOTFOUND;
1449 END IF;
1450 end loop;
1451
1452 If p_mode ='B' then
1453 CLOSE gms_rev1;
1454 Else
1455 CLOSE gms_rev2;
1456 End If;
1457
1458 --End of bug 5481465
1459
1460 <<NO_PROCESS1>>
1461 NULL;
1462 exception
1463 when others then
1464 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1465 'SQLCODE',
1466 SQLCODE,
1467 'SQLERRM',
1468 SQLERRM,
1469 X_token_name5 => 'PROGRAM_NAME',
1470 X_token_val5 => 'GMS_SWEEPER.update_revenue_balance, Stage: '|| X_Stage,
1471 X_Exec_Type => 'C',
1472 X_Err_Code => St_Err_Code,
1473 X_Err_Buff => St_Err_Buff);
1474 end update_revenue_balance;
1475 /* -------------------------------------------------------------
1476 ************* REVENUE UPDATION PROCEDURE ENDS *************
1477 ------------------------------------------------------------- */
1478 -------------------------------------------------------------------
1479 -- MAIN BEGIN STARTS HERE ---
1480
1481 BEGIN
1482
1483 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
1484
1485 -- Initialize error tables
1486 upd_error_table.delete;
1487 upd_reason_table.delete;
1488
1489 if x_mode = 'B' then --> baseline calls with mode = 'B'
1490 x_status_code := 'B';
1491 else --> mode = 'U'
1492 x_status_code := 'A';
1493 end if;
1494
1495 IF L_DEBUG = 'Y' THEN
1496 gms_error_pkg.gms_debug('---------- Budget Balance Process Starts ----','C');
1497
1498 gms_error_pkg.gms_debug('Baselining for Project_ID :'||x_project_id,'C');
1499 gms_error_pkg.gms_debug('Baselining for Award_ID :'||x_award_id,'C');
1500
1501 END IF;
1502
1503 --Bug 4732065 : Shifted the code to fetch set of books id as part of Main cursors
1504
1505 -- Bug 2155790 : Added the following code to fetch the value for l_offset_days from
1506 -- from the profile GMS_PURGE_FUNDS_CHECK_RESULTS .
1507
1508 BEGIN
1509
1510 FND_PROFILE.GET('GMS_PURGE_FUNDS_CHECK_RESULTS', l_offset_days);
1511
1512 IF l_offset_days IS NULL THEN
1513 IF L_DEBUG = 'Y' THEN
1514 gms_error_pkg.gms_debug('Profile GMS_PURGE_FUNDS_CHECK_RESULTS cannot have NULL value','C');
1515 gms_error_pkg.gms_debug('Defaulting the value of GMS_PURGE_FUNDS_CHECK_RESULTS profile to 3','C');
1516 END IF;
1517 l_offset_days :=3 ;
1518 ELSIF (l_offset_days < 1 ) THEN
1519 IF L_DEBUG = 'Y' THEN
1520 gms_error_pkg.gms_debug('Profile GMS_PURGE_FUNDS_CHECK_RESULTS cannot have value '||l_offset_days||' which is less than 1','C');
1521 gms_error_pkg.gms_debug('Defaulting the value of GMS_PURGE_FUNDS_CHECK_RESULTS profile to 3','C');
1522 END IF;
1523 l_offset_days :=3 ;
1524
1525 END IF;
1526
1527 Exception
1528 When value_error then
1529 IF L_DEBUG = 'Y' THEN
1530 gms_error_pkg.gms_debug('Invalid character type value is assigned to Profile GMS_PURGE_FUNDS_CHECK_RESULTS',
1531 '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 END;
1536
1537 -- End of code modifications done for bug 2155790
1538
1539
1540 -- Bug 3487431... moved revenue posting from here to after actuals posting.
1541
1542 --## Records would be deleted from gms_bc_packets only in normal 'U' mode
1543 If x_mode <> 'B' then
1544
1545
1546 ---------------------------------------------------------------------------------- +
1547 x_stage := '100'; -- Delete old records from gms_bc_packets
1548 IF L_DEBUG = 'Y' THEN
1549 gms_error_pkg.gms_debug('Deleting old records from GMS','C');
1550 END IF;
1551
1552 -- Bug 2155790 : Removed the trunc function and replaced 3 with l_offset_days
1553 delete from gms_bc_packets
1554 where status_code IN ('R', 'T', 'S', 'F','C','I','P','E','X')
1555 and (sysdate - creation_date) >= l_offset_days;
1556
1557 --R12 Fundscheck Management uptake: Logic added to delete records in P status associated with inactive session.
1558 delete from gms_bc_packets gms
1559 where status_code IN ('P')
1560 and NOT EXISTS (SELECT 'x'
1561 FROM v$session
1562 WHERE audsid = gms.session_id
1563 and Serial# = gms.serial_id);
1564
1565 ---------------------------------------------------------------------------------- +
1566 x_stage := '200'; -- Delete from gms_bc_packet_arrival_order
1567 IF L_DEBUG = 'Y' THEN
1568 gms_error_pkg.gms_debug('Deleting records from arrival order table','C');
1569 END IF;
1570
1571 DELETE gms_bc_packet_arrival_order ao
1572 WHERE NOT EXISTS (
1573 SELECT 1
1574 FROM gms_bc_packets
1575 WHERE packet_id = ao.packet_id
1576 );
1577
1578 COMMIT;
1579
1580 ---------------------------------------------------------------------------------- +
1581 x_stage := '250'; -- delete transactions left in pending state
1582 IF L_DEBUG = 'Y' THEN
1583 gms_error_pkg.gms_debug('Deleting transactions left in pending state','C');
1584 END IF;
1585
1586 gms_funds_control_pkg.delete_pending_txns(retcode,errbuf);
1587 -- Note: Above mentioned procedure will not handle exception , so if there is any error
1588 -- it will fall to when others ...
1589
1590 COMMIT;
1591
1592 End if; -- If x_mode <> 'B' then
1593
1594 -- Bug 4053891 Starts
1595
1596 x_plsql_count :=0;
1597 x_old_budget_version_id :=-999;
1598 x_loop_counter :=0;
1599 -- Do not add any more commits in the program then that is already present --K.Biju
1600
1601 -- Bug 4053891 Ends
1602
1603 x_stage := '300'; -- Before loop of gms_bc_packet
1604 IF L_DEBUG = 'Y' THEN
1605 gms_error_pkg.gms_debug('Start Loop for Updation','C');
1606 gms_error_pkg.gms_debug('----------------------------------------------','C');
1607 END IF;
1608
1609 FOR rec_gms_packets IN c_gms_packets(x_status_code) --Added parameter to fix bug 2138376
1610 LOOP
1611 BEGIN
1612
1613 -- Bug 4053891 Starts
1614 ---------------------------------------------------------------------------------- +
1615 -- Code flow in the loop
1616 -- * Post burden and commit if 100 records have already been processed or
1617 -- budget version id changed. COMMIT after posting.
1618 -- Else
1619 -- * Record burden
1620 -- * Update balance (Not changed)
1621 -- * Update ADL (Not changed)
1622 -- * Update gms_bc_packets
1623 ---------------------------------------------------------------------------------- +
1624 x_stage := '325';
1625 IF L_DEBUG = 'Y' THEN
1626 gms_error_pkg.gms_debug('Check if burden to be posted','C');
1627 END IF;
1628
1629 If x_mode <> 'B' then
1630 If (x_loop_counter = 100 or
1631 (x_old_budget_version_id <> -999 and
1632 x_old_budget_version_id <> rec_gms_packets.budget_version_id)
1633 ) then
1634 -- Batch limit 100 reached or budget version changed:
1635 -- * Post Burden
1636 -- * Initalize variables
1637 -- * Commit
1638 If Tab_Award_exp_burden.exists(1) then
1639 x_plsql_count := Tab_Award_exp_burden.count;
1640 End If;
1641
1642 -- * Post Burden
1643 If x_plsql_count > 0 then
1644
1645 x_stage := '350';
1646 IF L_DEBUG = 'Y' THEN
1647 gms_error_pkg.gms_debug('Posting burden for bvid:'||x_old_budget_version_id,'C');
1648 END IF;
1649
1650 POST_BURDEN_AMOUNTS(x_plsql_count,Tab_Award_exp_burden,x_err_code);
1651
1652 If x_err_code = 'E' then
1653
1654 IF L_DEBUG = 'Y' THEN
1655 gms_error_pkg.gms_debug('Error Code after posting burden(E->Failure):'||x_err_code,'C');
1656 END IF;
1657
1658 ROLLBACK to SAVEPOINT A;
1659 If Tab_Award_exp_burden.exists(1) then
1660 Tab_Award_exp_burden.delete;
1661 End If;
1662 GOTO NO_PROCESS;
1663
1664 End If;
1665
1666 IF L_DEBUG = 'Y' THEN
1667 gms_error_pkg.gms_debug('Burden posted for bvid:'||x_old_budget_version_id,'C');
1668 END IF;
1669
1670 End If; --If x_plsql_count > 0 then
1671
1672 -- * Initalize variables
1673 x_loop_counter := 0;
1674 x_plsql_count := 0;
1675 x_old_budget_version_id := rec_gms_packets.budget_version_id;
1676
1677 If Tab_Award_exp_burden.exists(1) then
1678 Tab_Award_exp_burden.delete;
1679 End If;
1680
1681 -- * Commit
1682 Commit;
1683
1684 End If; -- If burden posting reqd.
1685
1686 SAVEPOINT A;
1687
1688 -- Normal processing ..
1689 x_loop_counter := x_loop_counter + 1;
1690
1691 x_stage := '360';
1692 IF L_DEBUG = 'Y' THEN
1693 gms_error_pkg.gms_debug('Recording burden','C');
1694 END IF;
1695
1696 -- Record Burden amounts
1697 If ((rec_gms_packets.burden_adjustment_flag = 'N' and
1698 rec_gms_packets.parent_bc_packet_id is null)
1699 -- original raw line
1700 OR
1701 (rec_gms_packets.burden_adjustment_flag = 'Y' and
1702 rec_gms_packets.burdenable_raw_cost <> 0)
1703 -- Burden adjustment line (Raw portion)
1704 ) then
1705
1706 -- Lock records ...
1707 If x_loop_counter = 1 then
1708 Lock_budget_versions(rec_gms_packets.budget_version_id);
1709 --Tab_Award_exp_burden := Tab_Award_exp();
1710 End If;
1711
1712 -- Record Burden amounts
1713 Record_burden_amounts(rec_gms_packets.award_id,
1714 rec_gms_packets.expenditure_type,
1715 nvl(rec_gms_packets.entered_dr,0) - nvl(rec_gms_packets.entered_cr,0),
1716 rec_gms_packets.burdenable_raw_cost,
1717 rec_gms_packets.document_type,
1718 Tab_Award_exp_burden,
1719 x_err_code);
1720 If x_err_code = 'E' then
1721 IF L_DEBUG = 'Y' THEN
1722 gms_error_pkg.gms_debug('Burden recording failed for bvid,award_id,expenditure type,doc_type:'||
1723 rec_gms_packets.budget_version_id||','||rec_gms_packets.award_id||
1724 ','||rec_gms_packets.expenditure_type||','||
1725 rec_gms_packets.document_type,'C');
1726 END IF;
1727 ROLLBACK to SAVEPOINT A;
1728 GOTO NO_PROCESS;
1729 End If;
1730 End If;
1731 End If; -- If x_mode <> 'B' then
1732
1733 ---------------------------------------------------------------------------------------------------+
1734 -- Bug 4053891 Ends
1735
1736
1737 x_stage := '400'; -- Update GMS_BALANCES record
1738 IF L_DEBUG = 'Y' THEN
1739 gms_error_pkg.gms_debug('----------------------------------------------','C');
1740 gms_error_pkg.gms_debug('Expenditure --> '||to_char(rec_gms_packets.document_header_id),'C');
1741 gms_error_pkg.gms_debug('Adl --> '||to_char(rec_gms_packets.document_distribution_id),'C');
1742 gms_error_pkg.gms_debug('Award --> '||to_char(rec_gms_packets.award_id),'C');
1743 gms_error_pkg.gms_debug('Project --> '||to_char(rec_gms_packets.project_id),'C');
1744 gms_error_pkg.gms_debug('Task --> '||to_char(rec_gms_packets.task_id),'C');
1745 gms_error_pkg.gms_debug('Budget Version --> '||to_char(rec_gms_packets.budget_version_id),'C');
1746 gms_error_pkg.gms_debug('Document --> '||rec_gms_packets.document_type,'C');
1747 END IF;
1748
1749
1750 /* Bug 5956576: Base Bug 5956576 - Changes start */
1751 /* UPDATE sql commented and rewritten into two separate queries
1752 inside IF..ELSE blocks based on rec_gms_packets.document_type */
1753
1754 /*
1755 UPDATE gms_balances gb
1756 SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
1757 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1758 decode(rec_gms_packets.document_type,'EXP',1,0),
1759 gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
1760 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1761 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1762 WHERE gb.project_id = rec_gms_packets.project_id
1763 AND gb.award_id = rec_gms_packets.award_id
1764 AND ((rec_gms_packets.document_type = 'BGT'
1765 and gb.task_id = rec_gms_packets.bud_task_id)
1766 OR (rec_gms_packets.document_type <>'BGT' -- Bug 2138376 : changed to <> as per GSCC standards
1767 and gb.task_id=rec_gms_packets.task_id))
1768 AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
1769 AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
1770 AND gb.budget_version_id = rec_gms_packets.budget_version_id
1771 AND gb.balance_type = rec_gms_packets.document_type
1772 AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
1773 AND rownum = 1;
1774 */
1775 IF (rec_gms_packets.document_type = 'BGT') THEN
1776
1777 UPDATE gms_balances gb
1778 SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
1779 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1780 decode(rec_gms_packets.document_type,'EXP',1,0),
1781 gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
1782 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1783 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1784 WHERE gb.project_id = rec_gms_packets.project_id
1785 AND gb.award_id = rec_gms_packets.award_id
1786 AND gb.task_id = rec_gms_packets.bud_task_id --Bug5875538 for Perf. Fix
1787 AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
1788 AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
1789 AND gb.budget_version_id = rec_gms_packets.budget_version_id
1790 AND gb.balance_type = rec_gms_packets.document_type
1791 AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
1792 AND rownum = 1;
1793
1794 ELSIF (rec_gms_packets.document_type <>'BGT') THEN
1795
1796 UPDATE gms_balances gb
1797 SET gb.actual_period_to_date = nvl(gb.actual_period_to_date,0) +
1798 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1799 decode(rec_gms_packets.document_type,'EXP',1,0),
1800 gb.encumb_period_to_date = nvl(gb.encumb_period_to_date,0) +
1801 (nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1802 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1803 WHERE gb.project_id = rec_gms_packets.project_id
1804 AND gb.award_id = rec_gms_packets.award_id
1805 AND gb.task_id=rec_gms_packets.task_id --Bug5875538 for Perf. Fix
1806 AND gb.resource_list_member_id = rec_gms_packets.resource_list_member_id
1807 AND gb.set_of_books_id = rec_gms_packets.set_of_books_id
1808 AND gb.budget_version_id = rec_gms_packets.budget_version_id
1809 AND gb.balance_type = rec_gms_packets.document_type
1810 AND rec_gms_packets.expenditure_item_date between gb.start_date and gb.end_date
1811 AND rownum = 1;
1812
1813 END IF;
1814
1815 /* Bug 5956576: Base Bug 5955990 - Changes end */
1816
1817 IF (SQL%NOTFOUND) THEN
1818
1819 if rec_gms_packets.time_phased_type_code = 'G' then
1820 select gps.start_date, gps.end_date
1821 into x_st_date, x_ed_date
1822 from gl_period_statuses gps
1823 where gps.application_id = 101
1824 and gps.set_of_books_id = rec_gms_packets.set_of_books_id
1825 and rec_gms_packets.expenditure_item_date between gps.start_date and gps.end_date
1826 and gps.adjustment_period_flag = 'N';
1827
1828 elsif rec_gms_packets.time_phased_type_code = 'P' then
1829 select start_date , end_date
1830 into x_st_date , x_ed_date
1831 from pa_periods gpa --Bug 4732065 /*bug 6660289*/
1832 where rec_gms_packets.expenditure_item_date between gpa.start_date and gpa.end_date;
1833
1834 elsif rec_gms_packets.time_phased_type_code in ('R','N') then
1835
1836 setup_start_end_date(rec_gms_packets.project_id,
1837 rec_gms_packets.award_id,
1838 rec_gms_packets.bud_task_id, -- 2673200
1839 rec_gms_packets.budget_version_id,
1840 rec_gms_packets.time_phased_type_code,
1841 rec_gms_packets.entry_level_code, -- 2673200
1842 rec_gms_packets.expenditure_item_date,
1843 rec_gms_packets.amount_type,
1844 rec_gms_packets.boundary_code,
1845 rec_gms_packets.set_of_books_id,
1846 x_st_date,
1847 x_ed_date,
1848 St_e_code,
1849 x_err_stage);
1850
1851 end if;
1852
1853 If x_st_date is null or x_ed_date is null then
1854
1855 l_counter:=Upd_error_table.COUNT;
1856 l_counter:=l_counter+1;
1857
1858 upd_error_table(l_counter).exp_item_id :=rec_gms_packets.document_header_id;
1859 upd_error_table(l_counter).adl :=rec_gms_packets.document_distribution_id;
1860 upd_error_table(l_counter).award_id := rec_gms_packets.award_id;
1861 upd_error_table(l_counter).project_id :=rec_gms_packets.project_id;
1862 upd_error_table(l_counter).task_id := rec_gms_packets.task_id;
1863 upd_error_table(l_counter).amount:=rec_gms_packets.entered_dr - rec_gms_packets.entered_cr;
1864
1865 upd_reason_table(l_counter).resource_list_id:=rec_gms_packets.resource_list_id;
1866 upd_reason_table(l_counter).person_id:=null;
1867 upd_reason_table(l_counter).job_id:=null;
1868 upd_reason_table(l_counter).org_id:=null;
1869 upd_reason_table(l_counter).expenditure_type:=null;
1870 upd_reason_table(l_counter).nlr:=null;
1871 upd_reason_table(l_counter).exp_category:=null;
1872 upd_reason_table(l_counter).rev_category:=null;
1873 upd_reason_table(l_counter).org_id:=null;
1874 upd_reason_table(l_counter).sys_link:=null;
1875 upd_reason_table(l_counter).exp_date:=rec_gms_packets.expenditure_item_date;
1876 upd_reason_table(l_counter).bvid:=rec_gms_packets.budget_version_id;
1877 upd_reason_table(l_counter).bud_task:=rec_gms_packets.bud_task_id;
1878 upd_reason_table(l_counter).cat_code:=null;
1879 upd_reason_table(l_counter).tp_code:=rec_gms_packets.time_phased_type_code;
1880
1881 upd_error_table(l_counter).reason:= 'Transaction item:';
1882
1883 If x_st_date is null then
1884 upd_error_table(l_counter).reason:= upd_error_table(l_counter).reason||'Start Date Null';
1885 End If;
1886 If x_ed_date is null then
1887 upd_error_table(l_counter).reason:= upd_error_table(l_counter).reason||':End Date Null';
1888 End If;
1889
1890 If x_mode <> 'B' then
1891 ROLLBACK TO SAVEPOINT A;
1892 GOTO NO_PROCESS;
1893 Else
1894 RAISE_APPLICATION_ERROR(-20001,'Could not derive budget period date');
1895 End If;
1896
1897 END if;
1898
1899 x_stage := '800';
1900
1901 -- Insert GMS_BALANCES record where balance record not exist
1902
1903 insert into gms_balances (project_id
1904 ,award_id
1905 ,task_id
1906 ,top_task_id
1907 ,resource_list_member_id
1908 ,set_of_books_id
1909 ,budget_Version_id
1910 ,balance_type
1911 ,last_update_date
1912 ,last_updated_by
1913 ,created_by
1914 ,creation_date
1915 ,last_update_login
1916 ,start_date
1917 ,end_date
1918 ,parent_member_id
1919 ,budget_period_to_date
1920 ,actual_period_to_date
1921 ,encumb_period_to_date
1922 )
1923 values
1924 (rec_gms_packets.project_id
1925 ,rec_gms_packets.award_id
1926 ,decode(rec_gms_packets.document_type,'BGT',rec_gms_packets.bud_task_id,
1927 rec_gms_packets.task_id)
1928 ,rec_gms_packets.top_task_id
1929 ,rec_gms_packets.resource_list_member_id
1930 ,rec_gms_packets.set_of_books_id
1931 ,rec_gms_packets.budget_Version_id
1932 ,rec_gms_packets.document_type
1933 ,sysdate
1934 ,FND_GLOBAL.USER_ID
1935 ,FND_GLOBAL.USER_ID
1936 ,sysdate
1937 ,FND_GLOBAL.LOGIN_ID
1938 ,x_st_date
1939 ,x_ed_date
1940 ,rec_gms_packets.parent_resource_id
1941 ,0
1942 ,(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1943 decode(rec_gms_packets.document_type,'EXP',1,0)
1944 ,(nvl(rec_gms_packets.entered_dr,0)- nvl(rec_gms_packets.entered_cr,0)) *
1945 decode(rec_gms_packets.document_type,'REQ',1,'PO',1,'AP',1,'ENC',1,0)
1946 );
1947
1948
1949 END IF; -- sql%notfound
1950
1951 x_stage := '900'; -- Update gms_bc_packet record status to 'X'
1952 update gms_bc_packets set status_code = 'X'
1953 WHERE rowid=rec_gms_packets.rowid;
1954
1955 -- Changed to rowid criteria, bug 4053891
1956
1957 -------------------------------------------------------------------
1958 --Update adl for accumulated_flag for mode <> B
1959 -------------------------------------------------------------------
1960
1961 if x_mode <> 'B' then
1962 if rec_gms_packets.document_type in ('EXP','ENC') then
1963
1964 update gms_award_distributions
1965 set accumulated_flag='Y'
1966 where expenditure_item_id = rec_gms_packets.document_header_id
1967 and rec_gms_packets.document_distribution_id = decode(rec_gms_packets.document_type, --Bug 5726575
1968 'EXP', cdl_line_num,
1969 'ENC', adl_line_num)
1970 and nvl(reversed_flag, 'N') = 'N' --Bug 5726575
1971 and line_num_reversed is null
1972 and document_type=rec_gms_packets.document_type
1973 and award_id=rec_gms_packets.award_id
1974 and project_id = rec_gms_packets.project_id
1975 and task_id = rec_gms_packets.task_id
1976 and adl_status='A'
1977 and cost_distributed_flag='Y'
1978 and fc_status='A';
1979
1980 elsif rec_gms_packets.document_type ='PO' then
1981
1982 update gms_award_distributions
1983 set accumulated_flag='Y'
1984 where po_distribution_id = rec_gms_packets.document_distribution_id
1985 and document_type=rec_gms_packets.document_type
1986 and award_id=rec_gms_packets.award_id
1987 and project_id = rec_gms_packets.project_id
1988 and task_id = rec_gms_packets.task_id
1989 and adl_status='A'
1990 and fc_status='A';
1991
1992 elsif rec_gms_packets.document_type ='REQ' then
1993
1994 update gms_award_distributions
1995 set accumulated_flag='Y'
1996 where distribution_id = rec_gms_packets.document_distribution_id
1997 and document_type=rec_gms_packets.document_type
1998 and award_id=rec_gms_packets.award_id
1999 and project_id = rec_gms_packets.project_id
2000 and task_id = rec_gms_packets.task_id
2001 and adl_status='A'
2002 and fc_status='A';
2003
2004 elsif rec_gms_packets.document_type ='AP' then
2005
2006 update gms_award_distributions
2007 set accumulated_flag='Y'
2008 where invoice_id = rec_gms_packets.document_header_id
2009 -- AP Lines uptake: changed join from with distribution num to distribution id
2010 and invoice_distribution_id = rec_gms_packets.document_distribution_id
2011 and document_type=rec_gms_packets.document_type
2012 and award_id=rec_gms_packets.award_id
2013 and project_id = rec_gms_packets.project_id
2014 and task_id = rec_gms_packets.task_id
2015 and adl_status='A'
2016 and fc_status='A';
2017
2018 end if;
2019
2020 end if;
2021
2022
2023 EXCEPTION
2024 When others then
2025 IF L_DEBUG = 'Y' THEN
2026 gms_error_pkg.gms_debug('Stage:'||x_stage||';'||substr(sqlerrm,1,255),'C');
2027 END IF;
2028 If x_mode <> 'B' then
2029 ROLLBACK TO SAVEPOINT A;
2030 -- If Sweeper then rollback and continue
2031 Else
2032 RAISE;
2033 -- If baseline then fail process ..
2034 End if;
2035 END;
2036
2037 <<NO_PROCESS>>
2038 NULL;
2039
2040
2041 END LOOP;
2042
2043 -- Bug 4053891 .. for last set of data .. Start
2044 If x_mode <> 'B' then
2045 If x_loop_counter > 1 then
2046 -- * Post Burden
2047 -- * Initalize variables
2048 -- * Commit
2049
2050 If Tab_Award_exp_burden.exists(1) then
2051 x_plsql_count := Tab_Award_exp_burden.count;
2052 End If;
2053
2054 -- * Post Burden
2055 If x_plsql_count > 0 then
2056
2057 post_burden_amounts(x_plsql_count,Tab_Award_exp_burden,x_err_code);
2058
2059 If x_err_code = 'E' then
2060 ROLLBACK; -- not to savepoint
2061 End If;
2062 End If;
2063
2064 -- * Initalize variables
2065 x_loop_counter := 0;
2066 x_plsql_count := 0;
2067 -- * Commit
2068 Commit;
2069 End If;
2070 End if;
2071
2072 -- Bug 4053891 .. for last set of data .. End
2073
2074 -- Moved Revenue posting to after actuals ...bug 3487431
2075
2076 -- Call update_revenue_balance.
2077 IF L_DEBUG = 'Y' THEN
2078 gms_error_pkg.gms_debug('Calling Revenue Updation','C');
2079 gms_error_pkg.gms_debug('Mode passed for revenue updation '||x_mode,'C');
2080 END IF;
2081
2082 -- Bug 2138376 : Modify the following code
2083 -- If called from Baselining process consider records with status 'B' else
2084 --consider records with status 'A'
2085
2086 if x_mode ='B' then
2087 x_status_code :='B'; --Bug 2138376
2088
2089 -- Bug 4732065 : if called from baselining derive value of SOB from x_award_id which will be NOT NULL
2090
2091 OPEN c_get_sob (x_award_id);
2092 FETCH c_get_sob INTO l_sob_id;
2093 CLOSE c_get_sob ;
2094
2095 update_revenue_balance(x_mode,x_award_id,x_project_id,l_sob_id,upd_error_table,upd_reason_table);
2096
2097 --Update adl accumulated_flag for mode = B
2098 update gms_award_distributions
2099 set accumulated_flag='Y'
2100 where award_id = x_award_id
2101 and project_id = x_project_id
2102 and adl_status='A'
2103 and fc_status='A';
2104
2105 else -- x_mode ='U'
2106 x_status_code :='A'; --Bug 2138376
2107
2108 /* for records in cur_records
2109 loop
2110 Commented for the bug 5481465*/
2111 /* 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*/
2112 update_revenue_balance(x_mode,-1,-1,l_sob_id,
2113 upd_error_table,upd_reason_table);
2114
2115 commit;
2116 -- Added to improve performance ..
2117
2118 -- end loop; commented for the bug 5481465
2119
2120 end if;
2121
2122 IF L_DEBUG = 'Y' THEN
2123 gms_error_pkg.gms_debug('Revenue Updation Completed','C');
2124 END IF;
2125
2126 -- ...bug 3487431 end.
2127
2128
2129
2130 error_output(upd_error_table,upd_reason_table);
2131
2132 retcode := 0; -- Changed from 'S' to 0 (zero) for Bug:2464800
2133 commit;
2134 EXCEPTION
2135 when OTHERS then
2136 retcode := 2; -- Changed from 'E' to 2 for Bug:2464800
2137 errbuf := (x_stage||' '||SQLCODE||' '||SQLERRM);
2138 --dbms_output.put_line('failed at when others'||SQLCODE||SQLERRM);
2139 END upd_act_enc_bal;
2140 END GMS_SWEEPER;