[Home] [Help]
PACKAGE BODY: APPS.PA_SWEEPER
Source
1 PACKAGE BODY PA_SWEEPER AS
2 -- $Header: PAFCUAEB.pls 120.7.12020000.5 2013/03/06 09:29:04 admarath ship $
3
4 -- Function : GetBCBalStartDate
5 -- Purpose : Based on TPC, returns the start date
6 -- Changed 5/29 to check for GL date, PA date when TPC = G, P resp.
7 -- If TPC = GL then selects GL period start date where the given GL date falls.
8 -- If TPC = PA then selects PA period start date where the given PA date falls.
9 -- If TPC = 'N' then get the start_date from pa_bc_balances where EI date falls
10 -- for the given task, budget_version and RLMI.
11 FUNCTION GetBCBalStartDate(
12 p_time_phase_code in varchar2,
13 p_project_id in number,
14 p_ei_date in date,
15 p_bdgt_version in number,
16 p_sob_id in number,
17 p_org_id in number,
18 p_task_id in number,
19 p_top_task_id in number,
20 p_rlmi in number,
21 p_gl_date in date,
22 p_pa_date in date) return date
23 IS
24 l_st_date date;
25 BEGIN
26 --pa_funds_control_utils.print_message('Entering GetBCBalStartDate, Time Phase = ' || p_time_phase_code || ' Sob = '|| p_sob_id || ' Org Id = ' || p_org_id);
27 --pa_fck_util.debug_msg('Entering GetBCBalStartDate' );
28
29 IF p_time_phase_code = 'G' then
30 select gps.start_date
31 into l_st_date
32 from gl_period_statuses gps
33 where gps.application_id = 101
34 and gps.set_of_books_id = p_sob_id
35 and trunc(p_gl_date) between trunc(gps.start_date) and trunc(gps.end_date)
36 and gps.adjustment_period_flag = 'N';
37 ELSIF p_time_phase_code = 'P' then
38 select pda.start_date
39 into l_st_date
40 from pa_periods_all pda
41 where trunc(pda.start_date) <= trunc(p_pa_date)
42 and trunc(pda.end_date) >= trunc(p_pa_date)
43 --trunc(p_ei_date) between trunc(pda.start_date) and trunc(pda.end_date)
44 -- R12 and nvl(pda.org_id,-99) = nvl(p_org_id,-99);
45 and pda.org_id = p_org_id;
46 ELSIF p_time_phase_code = 'N' then
47
48 -- Bug 3487403 and 3400389 Modified the code for time_phase_type_code 'N'
49
50 DECLARE
51
52 CURSOR c_prj_start IS
53 SELECT start_date
54 FROM pa_projects_all
55 WHERE project_id = p_project_id;
56
57 CURSOR c_prj_budg IS
58 SELECT MIN(start_date)
59 FROM pa_bc_balances
60 WHERE project_id = p_project_id
61 AND budget_version_id = p_bdgt_version;
62
63 BEGIN
64
65 OPEN c_prj_start;
66 FETCH c_prj_start INTO l_st_date;
67 CLOSE c_prj_start;
68
69 IF l_st_date IS NULL THEN
70
71 OPEN c_prj_budg;
72 FETCH c_prj_budg INTO l_st_date;
73 CLOSE c_prj_budg;
74
75 END IF;
76
77 IF l_st_date IS NULL THEN
78 l_st_date := p_ei_date;
79 ELSIF l_st_date > p_ei_date THEN
80 l_st_date := p_ei_date;
81 END IF;
82
83 END;
84
85 END IF;
86
87 --pa_funds_control_utils.print_message('Leaving Start date');
88 --pa_fck_util.debug_msg('Exiting GetBCBalStartDate' );
89 return l_st_date;
90 EXCEPTION
91 WHEN OTHERS THEN
92 raise;
93 END GetBCBalStartDate;
94
95
96 -- Function : GetBCBalEndDate
97 -- Purpose : Based on TPC, returns the end date
98 -- Changed 5/29 to check for GL date, PA date when TPC = G, P resp.
99 -- If TPC = GL then selects GL period end date where the given GL date falls.
100 -- If TPC = PA then selects PA period end date where the given PA date falls.
101 -- If TPC = 'N' then get the end_date from pa_bc_balances where EI date falls
102 -- for the given task, budget_version and RLMI.
103 FUNCTION GetBCBalEndDate(
104 p_time_phase_code in varchar2,
105 p_project_id in number,
106 p_ei_date in date,
107 p_bdgt_version in number,
108 p_sob_id in number,
109 p_org_id in number,
110 p_task_id in number,
111 p_top_task_id in number,
112 p_rlmi in number,
113 p_gl_date in date,
114 p_pa_date in date) return date
115 IS
116 l_ed_date date;
117 BEGIN
118 --pa_funds_control_utils.print_message('Entering GetBCBalEndDate');
119 --pa_fck_util.debug_msg('Entering GetBCBalEndDate' );
120
121 IF p_time_phase_code = 'G' then
122 select gps.end_date
123 into l_ed_date
124 from gl_period_statuses gps
125 where gps.application_id = 101
126 and gps.set_of_books_id = p_sob_id
127 and trunc(p_gl_date) between trunc(gps.start_date) and trunc(gps.end_date)
128 and gps.adjustment_period_flag = 'N';
129 ELSIF p_time_phase_code = 'P' then
130 select ppd.end_date
131 into l_ed_date
132 from pa_periods_all ppd
133 where trunc(ppd.start_date) <= trunc(p_pa_date)
134 and trunc(ppd.end_date) >= trunc(p_pa_date)
135 --trunc(p_ei_date) between trunc(ppd.start_date) and trunc(ppd.end_date)
136 -- R12 and nvl(ppd.org_id,-99) = nvl(p_org_id,-99);
137 and ppd.org_id = p_org_id;
138 ELSIF p_time_phase_code = 'N' then
139
140 -- Bug 3487403 and 3400389 Modified the code for time_phase_type_code 'N'
141
142 DECLARE
143
144 CURSOR c_prj_end IS
145 SELECT completion_date
146 FROM pa_projects_all
147 WHERE project_id = p_project_id;
148
149 CURSOR c_prj_budg IS
150 SELECT MAX(end_date)
151 FROM pa_bc_balances
152 WHERE project_id = p_project_id
153 AND budget_version_id = p_bdgt_version;
154
155 BEGIN
156
157 OPEN c_prj_end;
158 FETCH c_prj_end INTO l_ed_date;
159 CLOSE c_prj_end;
160
161 IF l_ed_date IS NULL THEN
162 OPEN c_prj_budg;
163 FETCH c_prj_budg INTO l_ed_date;
164 CLOSE c_prj_budg;
165 END IF;
166
167 IF l_ed_date IS NULL THEN
168 l_ed_date := p_ei_date;
169 ELSIF l_ed_date < p_ei_date THEN
170 l_ed_date := p_ei_date;
171 END IF;
172
173 END;
174
175 END IF;
176
177 --pa_funds_control_utils.print_message('Leaving End date');
178 --pa_fck_util.debug_msg('Exiting GetBCBalEndDate' );
179 return l_ed_date;
180 EXCEPTION
181 WHEN OTHERS THEN
182 raise;
183 END GetBCBalEndDate;
184
185 PROCEDURE update_act_enc_balance(
186 x_return_status OUT NOCOPY VARCHAR2
187 ,x_error_message_code OUT NOCOPY VARCHAR2
188 --PA.M
189 ,p_project_id IN NUMBER DEFAULT NULL) IS
190
191 cursor c_pkt_proj is
192 select distinct project_id project_id
193 from pa_bc_packets
194 where status_code = 'A'
195 and substr(result_code ,1,1) = 'P'
196 --PA.M
197 and (Pa_Bc_Packets.Project_Id = P_Project_Id
198 Or P_ProjecT_Id is NULL) ;
199
200 cursor c_bc_packets(l_project_id in number) is
201 select pbc.budget_version_id
202 , pbc.project_id
203 , pbc.task_id
204 , pbc.bud_task_id
205 , pbc.top_task_id
206 , pbc.document_type
207 , pbc.period_name
208 , pbc.resource_list_member_id
209 , pbc.parent_resource_id
210 , pbc.set_of_books_id
211 , trunc(pbc.expenditure_item_date) expenditure_item_date
212 , pbc.accounted_dr
213 , pbc.accounted_cr
214 , pbc.actual_flag
215 , pbv.resource_list_id
216 , pbm.time_phased_type_code
217 , pbc.document_header_id
218 , pbc.document_distribution_id
219 , pbc.bc_commitment_id
220 , pbc.packet_id
221 , pbc.expenditure_type
222 , pbc.pa_date
223 , pbc.gl_date
224 , pbc.period_year
225 , pbc.period_num
226 , pbc.je_category_name
227 , pbc.je_source_name
228 , pbc.expenditure_organization_id
229 , pbc.entered_dr
230 , pbc.entered_cr
231 , pbc.budget_ccid
232 , pbc.txn_ccid
233 , pbc.bc_packet_id
234 , pbc.parent_bc_packet_id
235 , pbc.bud_resource_list_member_id
236 , pbc.balance_posted_flag
237 , pbc.encumbrance_type_id
238 , pbc.proj_encumbrance_type_id
239 , pbc.status_code
240 , pbc.org_id
241 , pbc.burden_cost_flag
242 --PA.M
243 , pbc.Document_Line_Id
244 , pbc.Compiled_Multiplier
245 , pbc.Fc_Start_Date
246 , pbc.Fc_End_Date
247 , pbc.Comm_Tot_Raw_Amt
248 , pbc.Comm_Tot_Bd_Amt
249 , pbc.Comm_Raw_Amt_Relieved
250 , pbc.Comm_Bd_Amt_Relieved
251 , pbc.Summary_Record_Flag
252 , pbc.Exp_Item_Id
253 , pbc.reference1
254 , pbc.reference2
255 , pbc.reference3
256 --R12
257 , pbc.bc_event_id
258 , pbc.vendor_id
259 , pbc.budget_line_id
260 , pbc.burden_method_code
261 , pbc.document_header_id_2
262 , pbc.document_distribution_type
263 from pa_budget_versions pbv
264 , pa_bc_packets pbc
265 , pa_budget_entry_methods pbm
266 where pbc.status_code = 'A'
267 and substr(pbc.result_code ,1,1) = 'P'
268 and pbc.balance_posted_flag = 'N'
269 and pbv.budget_version_id = pbc.budget_version_id
270 and pbc.project_id = pbv.project_id
271 and pbv.budget_entry_method_code = pbm.budget_entry_method_code
272 and pbc.project_id = l_project_id
273 order by pbc.packet_id;
274
275 cursor c_ins_packets(l_project_id in number) is
276 select pbc.budget_version_id
277 , pbc.project_id
278 , pbc.task_id
279 , pbc.top_task_id
280 , pbc.document_type
281 , pbc.resource_list_member_id
282 , pbc.parent_resource_id
283 , pbc.set_of_books_id
284 , sum((nvl(pbc.accounted_dr,0)- nvl(pbc.accounted_cr,0)))*decode(pbc.document_type,'EXP',1,0)
285 actual_ptd
286 , sum((nvl(pbc.accounted_dr,0)- nvl(accounted_cr,0)))*decode(pbc.document_type,'REQ',1,'PO',1,'AP',1,'CC_P_CO',1,'CC_C_CO',1,'CC_P_PAY',1,'CC_C_PAY',1,0)
287 encumb_ptd
288 , pbc.balance_posted_flag
289 , pbc.status_code
290 , pbm.time_phased_type_code
291 --, trunc(pbc.expenditure_item_date)
292 , PA_SWEEPER.GetBCBalStartDate(pbm.time_phased_type_code,pbc.project_id,pbc.expenditure_item_date,pbc.budget_version_id, pbc.set_of_books_id, pbc.org_id, pbc.task_id, pbc.top_task_id, pbc.resource_list_member_id,pbc.gl_date,pbc.pa_date)
293 , PA_SWEEPER.GetBCBalEndDate(pbm.time_phased_type_code,pbc.project_id,pbc.expenditure_item_date, pbc.budget_version_id, pbc.set_of_books_id, pbc.org_id, pbc.task_id, pbc.top_task_id, pbc.resource_list_member_id,pbc.gl_date,pbc.pa_date)
294 /*Bug 3007393*/
295 /*, pbc.org_id*/
296 from pa_budget_versions pbv
297 , pa_bc_packets pbc
298 , pa_budget_entry_methods pbm
299 where pbc.status_code = 'A'
300 and substr(pbc.result_code ,1,1) = 'P'
301 and pbc.balance_posted_flag = 'N'
302 and pbv.budget_version_id = pbc.budget_version_id
303 and pbv.budget_entry_method_code = pbm.budget_entry_method_code
304 and pbc.project_id = pbv.project_id
305 and pbc.project_id = l_project_id
306 and not exists (
307 select 'X'
308 from pa_bc_balances pb
309 where pb.project_id = l_project_id
310 AND pb.task_id = pbc.task_id
311 AND pb.resource_list_member_id = pbc.resource_list_member_id
312 AND pb.set_of_books_id = pbc.set_of_books_id
313 AND pb.budget_version_id = pbc.budget_version_id
314 AND pb.balance_type = pbc.document_type
315 AND ((pbm.time_phased_type_code = 'N' and
316 trunc(pbc.expenditure_item_date) between trunc(pb.start_date) and trunc(pb.end_date))
317 OR (pbm.time_phased_type_code = 'P' and
318 trunc(pbc.pa_date) between trunc(pb.start_date) and trunc(pb.end_date))
319 OR (pbm.time_phased_type_code = 'G' and
320 trunc(pbc.gl_date) between trunc(pb.start_date) and trunc(pb.end_date))))
321 group by pbc.budget_version_id
322 , pbc.project_id
323 , pbc.task_id
324 , pbc.top_task_id
325 , pbc.document_type
326 --, trunc(pbc.expenditure_item_date)
327 , PA_SWEEPER.GetBCBalStartDate(pbm.time_phased_type_code,pbc.project_id,pbc.expenditure_item_date,pbc.budget_version_id, pbc.set_of_books_id, pbc.org_id, pbc.task_id, pbc.top_task_id, pbc.resource_list_member_id,pbc.gl_date,pbc.pa_date)
328 , PA_SWEEPER.GetBCBalEndDate(pbm.time_phased_type_code,pbc.project_id,pbc.expenditure_item_date, pbc.budget_version_id, pbc.set_of_books_id, pbc.org_id, pbc.task_id, pbc.top_task_id, pbc.resource_list_member_id,pbc.gl_date,pbc.pa_date)
329 , pbc.resource_list_member_id
330 , pbc.parent_resource_id
331 , pbc.set_of_books_id
332 , pbm.time_phased_type_code
333 , pbc.balance_posted_flag
334 , pbc.status_code;
335 /*Bug 3007393*/
336 /*, pbc.org_id;*/
337
338 l_profile_value varchar2(255) := FND_PROFILE.VALUE('PA_MAINTAIN_FC_PACKETS'); -- Added for Bug 4588095
339
340 cursor c_delete_pkts is
341 select rowid
342 from pa_bc_packets
343 where status_code in ('X', 'V', 'L')
344 -- and (trunc(sysdate) - trunc(creation_date)) >= FND_PROFILE.VALUE_SPECIFIC('PA_MAINTAIN_FC_PACKETS'); Modified for Bug 4588095
345 and (trunc(sysdate) - trunc(creation_date)) >= l_profile_value
346 union all
347 select rowid
348 from pa_bc_packets pbc
349 where pbc.status_code in ('P')
350 and ((pbc.session_id is not null and
351 pbc.serial_id is not null and
352 NOT EXISTS (SELECT 'x'
353 FROM v$session
354 WHERE audsid = pbc.session_id
355 AND Serial# = pbc.serial_id)
356 ) OR
357 (pbc.session_id is null and
358 pbc.serial_id is null and
359 (trunc(sysdate) - trunc(creation_date)) >= 10
360 -- modified from 3 to 10 days .. we're not expecting any process to run for 10 days
361 -- interface runs by batch size and we're not expecting batches to run for 10 days
362 )
363 )
364 union all
365 select rowid
366 from pa_bc_packets
367 where status_code in ('I')
368 and (trunc(sysdate) - trunc(creation_date)) >= 3
369 union all
370 select rowid
371 from pa_bc_packets pbc
372 where pbc.status_code in ('S','F','T','R')
373 and (trunc(sysdate) - trunc(pbc.creation_date)) >= l_profile_value
374 and ((pbc.bc_event_id is null) OR
375 (pbc.bc_event_id is not null AND
376 NOT EXISTS (select 1 from xla_events xl
377 where xl.event_id = pbc.bc_event_id)
378 )
379 );
380
381
382 l_st_date date;
383 l_ed_date date;
384 l_debug_mode varchar2(1) := 'N';
385 l_count number := 0;
386 l_project_id number;
387
388 num number := 0;
389
390 l_PktBdgtVerTab PA_PLSQL_DATATYPES.IdTabTyp;
391 l_PktProjectTab PA_PLSQL_DATATYPES.IdTabTyp;
392 l_PktTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
393 l_PktDocTypTab PA_PLSQL_DATATYPES.Char10TabTyp;
394 l_PktSobTab PA_PLSQL_DATATYPES.IdTabTyp;
395 l_PktDocHeadTab PA_PLSQL_DATATYPES.IdTabTyp;
396 l_PktDocDistTab PA_PLSQL_DATATYPES.IdTabTyp;
397 l_PktEiDateTab PA_PLSQL_DATATYPES.DateTabTyp;
398 l_PktExpTypTab PA_PLSQL_DATATYPES.Char30TabTyp;
399 l_PktExpOrgTab PA_PLSQL_DATATYPES.IdTabTyp;
400 l_PktActFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
401 l_PktPeriodTab PA_PLSQL_DATATYPES.Char15TabTyp;
402 l_PktTPCTab PA_PLSQL_DATATYPES.Char30TabTyp;
403 l_PktRlmiTab PA_PLSQL_DATATYPES.IdTabTyp;
404 l_PktParResTab PA_PLSQL_DATATYPES.IdTabTyp;
405 l_PktBdgtTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
406 l_PktBdgtRlmiTab PA_PLSQL_DATATYPES.IdTabTyp;
407 l_PktTTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
408 l_PktEntDrTab PA_PLSQL_DATATYPES.NumTabTyp;
409 l_PktEntCrTab PA_PLSQL_DATATYPES.NumTabTyp;
410 l_PktAcctDrTab PA_PLSQL_DATATYPES.NumTabTyp;
411 l_PktAcctCrTab PA_PLSQL_DATATYPES.NumTabTyp;
412 l_PktStatusTab PA_PLSQL_DATATYPES.Char1TabTyp;
413 l_PktBcCommTab PA_PLSQL_DATATYPES.IdTabTyp;
414 l_PktPADateTab PA_PLSQL_DATATYPES.DateTabTyp;
415 l_PktGLDateTab PA_PLSQL_DATATYPES.DateTabTyp;
416 l_PktBdgtCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
417 l_PktTxnCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
418 l_PktParBcPktTab PA_PLSQL_DATATYPES.IdTabTyp;
419 l_PktBcPktTab PA_PLSQL_DATATYPES.IdTabTyp;
420 l_PktIdTab PA_PLSQL_DATATYPES.IdTabTyp;
421 l_PktSrcNameTab PA_PLSQL_DATATYPES.Char30TabTyp;
422 l_PktCatNameTab PA_PLSQL_DATATYPES.Char30TabTyp;
423 l_PktPdYearTab PA_PLSQL_DATATYPES.NumTabTyp;
424 l_PktPdNumTab PA_PLSQL_DATATYPES.NumTabTyp;
425 l_PktRlistTab PA_PLSQL_DATATYPES.NumTabTyp;
426 l_PktBalPostFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
427 l_PktEncTypIdTab PA_PLSQL_DATATYPES.IdTabTyp;
428 l_PktPrjEncTypIdTab PA_PLSQL_DATATYPES.IdTabTyp;
429 l_PktOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
430 l_PktCstBurdFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
431
432 l_InsBdgtVerTab PA_PLSQL_DATATYPES.IdTabTyp;
433 l_InsProjectTab PA_PLSQL_DATATYPES.IdTabTyp;
434 l_InsTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
435 l_InsTTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
436 l_InsDocTypTab PA_PLSQL_DATATYPES.Char10TabTyp;
437 l_InsRlmiTab PA_PLSQL_DATATYPES.IdTabTyp;
438 l_InsParResTab PA_PLSQL_DATATYPES.IdTabTyp;
439 l_InsSobTab PA_PLSQL_DATATYPES.IdTabTyp;
440 l_InsActPTDTab PA_PLSQL_DATATYPES.NumTabTyp;
441 l_InsEncPTDTab PA_PLSQL_DATATYPES.NumTabTyp;
442 l_InsBalPostFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
443 l_InsStatusTab PA_PLSQL_DATATYPES.Char1TabTyp;
444 l_InsTPCTab PA_PLSQL_DATATYPES.Char30TabTyp;
445 --l_InsEiDateTab PA_PLSQL_DATATYPES.DateTabTyp;
446 l_InsStDateTab PA_PLSQL_DATATYPES.DateTabTyp;
447 l_InsEdDateTab PA_PLSQL_DATATYPES.DateTabTyp;
448 l_InsOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
449
450 l_StsUpdPktIdTab PA_PLSQL_DATATYPES.IdTabTyp;
451 l_StsUpdBcPktIdTab PA_PLSQL_DATATYPES.IdTabTyp;
452
453 l_RowIdTab PA_PLSQL_DATATYPES.RowidTabTyp;
454
455 --PA.M
456 l_PktDocLineIdTab PA_PLSQL_DATATYPES.IdTabTyp;
457 l_PktCompMultiplierTab PA_PLSQL_DATATYPES.NumTabTyp;
458 l_PktFcStartDateTab PA_PLSQL_DATATYPES.DateTabTyp;
459 l_PktFcEndDateTab PA_PLSQL_DATATYPES.DateTabTyp;
460 l_PktCommTotRawAmtTab PA_PLSQL_DATATYPES.NumTabTyp;
461 l_PktCommTotBdAmtTab PA_PLSQL_DATATYPES.NumTabTyp;
462 l_PktCommRawAmtRelievedTab PA_PLSQL_DATATYPES.NumTabTyp;
463 l_PktCommBdAmtRelievedTab PA_PLSQL_DATATYPES.NumTabTyp;
464 l_PktSummaryRecordFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
465 l_PktExpItemIdTab PA_PLSQL_DATATYPES.IdTabTyp;
466 l_PktReference1Tab PA_PLSQL_DATATYPES.Char80TabTyp;
467 l_PktReference2Tab PA_PLSQL_DATATYPES.Char80TabTyp;
468 l_PktReference3Tab PA_PLSQL_DATATYPES.Char80TabTyp;
469
470 --R12
471 l_PktBcEventIDTab PA_PLSQL_DATATYPES.IdTabTyp;
472 l_PktBudgetLineIDTab PA_PLSQL_DATATYPES.IdTabTyp;
473 l_PktBurdenMethodCodeTab PA_PLSQL_DATATYPES.Char30TabTyp;
474 l_PktVendorIdTab PA_PLSQL_DATATYPES.IdTabTyp;
475 l_PktDocHdrId2Tab PA_PLSQL_DATATYPES.IdTabTyp;
476 l_PktDocDistTypeTab PA_PLSQL_DATATYPES.Char30TabTyp;
477
478 rows NATURAL := 200;
479
480 --Code Changes for Bug No.2984871 start
481 l_rowcount number :=0;
482 --Code Changes for Bug No.2984871 end
483
484 --Procedure to initialize the pl/sql tables
485 PROCEDURE InitPlSqlTabs is
486 BEGIN
487 l_PktBdgtVerTab.Delete;
488 l_PktProjectTab.Delete;
489 l_PktTaskTab.Delete;
490 l_PktDocTypTab.Delete;
491 l_PktSobTab.Delete;
492 l_PktDocHeadTab.Delete;
493 l_PktDocDistTab.Delete;
494 l_PktEiDateTab.Delete;
495 l_PktExpTypTab.Delete;
496 l_PktExpOrgTab.Delete;
497 l_PktActFlagTab.Delete;
498 l_PktPeriodTab.Delete;
499 l_PktTPCTab.Delete;
500 l_PktRlmiTab.Delete;
501 l_PktParResTab.Delete;
502 l_PktBdgtTaskTab.Delete;
503 l_PktBdgtRlmiTab.Delete;
504 l_PktTTaskTab.Delete;
505 l_PktEntDrTab.Delete;
506 l_PktEntCrTab.Delete;
507 l_PktAcctDrTab.Delete;
508 l_PktAcctCrTab.Delete;
509 l_PktStatusTab.Delete;
510 l_PktBcCommTab.Delete;
511 l_PktPADateTab.Delete;
512 l_PktGLDateTab.Delete;
513 l_PktBdgtCCIDTab.Delete;
514 l_PktTxnCCIDTab.Delete;
515 l_PktParBcPktTab.Delete;
516 l_PktBcPktTab.Delete;
517 l_PktIdTab.Delete;
518 l_PktSrcNameTab.Delete;
519 l_PktCatNameTab.Delete;
520 l_PktPdYearTab.Delete;
521 l_PktPdNumTab.Delete;
522 l_PktRlistTab.Delete;
523 l_PktBalPostFlagTab.Delete;
524 l_PktEncTypIdTab.Delete;
525 l_PktPrjEncTypIdTab.Delete;
526 l_PktOrgIdTab.Delete;
527 l_PktCstBurdFlagTab.Delete;
528 l_InsBdgtVerTab.Delete;
529 l_InsProjectTab.Delete;
530 l_InsTaskTab.Delete;
531 l_InsTTaskTab.Delete;
532 l_InsDocTypTab.Delete;
533 l_InsRlmiTab.Delete;
534 l_InsParResTab.Delete;
535 l_InsSobTab.Delete;
536 l_InsActPTDTab.Delete;
537 l_InsEncPTDTab.Delete;
538 l_InsBalPostFlagTab.Delete;
539 l_InsStatusTab.Delete;
540 l_InsTPCTab.Delete;
541 --l_InsEiDateTab.Delete;
542 l_InsStDateTab.Delete;
543 l_InsEdDateTab.Delete;
544 l_InsOrgIdTab.Delete;
545 l_StsUpdPktIdTab.Delete;
546 l_StsUpdBcPktIdTab.Delete;
547 --PA.M
548 l_PktDocLineIdTab.Delete;
549 l_PktCompMultiplierTab.Delete;
550 l_PktFcStartDateTab.Delete;
551 l_PktFcEndDateTab.Delete;
552 l_PktCommTotRawAmtTab.Delete;
553 l_PktCommTotBdAmtTab.Delete;
554 l_PktCommRawAmtRelievedTab.Delete;
555 l_PktCommBdAmtRelievedTab.Delete;
556 l_PktSummaryRecordFlagTab.Delete;
557 l_PktExpItemIdTab.Delete;
558 l_PktReference1Tab.delete;
559 l_PktReference2Tab.delete;
560 l_PktReference3Tab.delete;
561 --R12
562 l_PktBcEventIDTab.delete;
563 l_PktBudgetLineIDTab.delete;
564 l_PktBurdenMethodCodeTab.delete;
565 l_PktVendorIdTab.delete;
566 l_PktDocHdrId2Tab.delete;
567 l_PktDocDistTypeTab.delete;
568 EXCEPTION
569 WHEN OTHERS THEN
570 RAISE;
571 END InitPlSqlTabs;
572
573 --Procedure to initialize the pl/sql tables
574 PROCEDURE InitPlSqlTabs2 is
575 BEGIN
576 l_InsBdgtVerTab.Delete;
577 l_InsProjectTab.Delete;
578 l_InsTaskTab.Delete;
579 l_InsTTaskTab.Delete;
580 l_InsDocTypTab.Delete;
581 l_InsRlmiTab.Delete;
582 l_InsParResTab.Delete;
583 l_InsSobTab.Delete;
584 l_InsActPTDTab.Delete;
585 l_InsEncPTDTab.Delete;
586 l_InsBalPostFlagTab.Delete;
587 l_InsStatusTab.Delete;
588 l_InsTPCTab.Delete;
589 l_InsStDateTab.Delete;
590 l_InsEdDateTab.Delete;
591 l_InsOrgIdTab.Delete;
592 EXCEPTION
593 WHEN OTHERS THEN
594 RAISE;
595 END InitPlSqlTabs2;
596
597 -- Bug 14364299 Added populate_hist_table procedure
598
599 PROCEDURE populate_hist_table
600 IS
601
602 TYPE t_pa_pkt_hist IS TABLE OF PA_BC_PACKETS_HIST%ROWTYPE;
603
604 l_pa_pkt_hist t_pa_pkt_hist := t_pa_pkt_hist();
605 l_profile_value varchar2(255) := FND_PROFILE.VALUE('PA_MAINTAIN_FC_PACKETS');
606 CURSOR c_bc_pkts_x IS
607 SELECT *
608 FROM pa_bc_packets
609 WHERE status_code = 'X'
610 AND funds_process_mode <> 'B'
611 AND (trunc(sysdate) - trunc(creation_date)) >= l_profile_value;
612
613 rows NATURAL := 200;
614 i NUMBER :=0;
615
616 BEGIN
617
618 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
619 pa_debug.g_err_stage := 'Log: Inside populate_hist_table';
620 pa_debug.write_file('LOG',pa_debug.g_err_stage);
621 END IF;
622
623 OPEN c_bc_pkts_x;
624 LOOP
625 l_pa_pkt_hist.delete;
626 FETCH c_bc_pkts_x
627 BULK COLLECT INTO l_pa_pkt_hist LIMIT rows;
628 EXIT WHEN l_pa_pkt_hist.COUNT =0;
629
630
631 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
632 pa_debug.g_err_stage := 'Log: No. of records to insert in pa_bc_packets_hist ' || l_pa_pkt_hist.count;
633 pa_debug.write_file('LOG',pa_debug.g_err_stage);
634 END IF;
635
636
637 FORALL i IN l_pa_pkt_hist.FIRST .. l_pa_pkt_hist.LAST
638 INSERT INTO PA_BC_PACKETS_HIST VALUES l_pa_pkt_hist(i);
639
640 END LOOP;
641 CLOSE c_bc_pkts_x;
642
643 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
644 pa_debug.g_err_stage := 'Log: Exiting populate_hist_table';
645 pa_debug.write_file('LOG',pa_debug.g_err_stage);
646 END IF;
647
648 END populate_hist_table;
649
650
651 BEGIN
652
653 --Initialize the error stack
654 PA_DEBUG.init_err_stack('PA_SWEEPER.UPDATE_ACT_ENC_BALANCE');
655
656 --Initialize the message table for FND_MSG_PUB
657 fnd_msg_pub.initialize;
658
659 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
660 l_debug_mode := NVL(l_debug_mode, 'N');
661
662 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
663
664 --Initialize the return status to success
665 x_return_status := FND_API.G_RET_STS_SUCCESS;
666
667 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
668 pa_debug.g_err_stage := 'Log: Calling populate_hist_table';
669 pa_debug.write_file('LOG',pa_debug.g_err_stage);
670 END IF;
671
672 -- Bug 14364299 Added populate_hist_table procedure call
673
674 populate_hist_table();
675
676 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
677 pa_debug.g_err_stage := 'Log: Start of Update_Act_Enc_Balance';
678 pa_debug.write_file('LOG',pa_debug.g_err_stage);
679 END IF;
680
681 pa_funds_control_utils.print_message('Entering Sweeper');
682 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
683 pa_fck_util.debug_msg('PB:Entering Sweeper');
684 END IF;
685
686 --deleting rows older than 'X' days which is set in the profile
687 --Pkts with status C should not exist ideally
688 --Pkts with status B will be taken care of during rebaselining
689 --Pkts with status P are yet to be funds checked, do not delete here.
690 pa_funds_control_utils.print_message('Open c_delete_pkts');
691 open c_delete_pkts;
692 loop
693 l_RowIdTab.Delete;
694 fetch c_delete_pkts bulk collect into
695 l_RowIdTab
696 limit rows;
697
698 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
699 pa_debug.g_err_stage := 'Log: No. of records to delete ' || l_RowIdTab.count;
700 pa_debug.write_file('LOG',pa_debug.g_err_stage);
701 END IF;
702
703 pa_funds_control_utils.print_message('No. of records to delete = ' || l_RowIdTab.count);
704
705 if l_RowIdTab.count = 0 then
706 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
707 pa_debug.g_err_stage := 'Log: No records in c_delete_pkts, exit';
708 pa_debug.write_file('LOG',pa_debug.g_err_stage);
709 END IF;
710 pa_funds_control_utils.print_message('No records from c_delete_pkts, exit');
711 exit;
712 end if;
713
714 FORALL i in l_RowIdTab.first..l_RowIdTab.last
715 delete from pa_bc_packets
716 where rowid = l_RowIdTab(i);
717
718 /*Code Changes for Bug No.2984871 start */
719 l_rowcount:=sql%rowcount;
720 /*Code Changes for Bug No.2984871 end */
721 commit;
722 exit when c_delete_pkts%notfound;
723 end loop;
724 close c_delete_pkts;
725 pa_funds_control_utils.print_message('Close c_delete_pkts');
726
727 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
728 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
729 pa_debug.g_err_stage := 'Log: Last Deleted ' || to_char(l_rowcount) || ' records from PA_BC_PACKETS older than ' || l_profile_value || ' days';
730 pa_debug.write_file('LOG',pa_debug.g_err_stage);
731 END IF;
732 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
733 pa_funds_control_utils.print_message('No of Deleted old packets in pa_bc_packets = ' || to_char(l_rowcount));
734
735 --deleting rows older than 'X' days which is set in the profile
736 --(batch_id of -999 are those belonging to actuals)
737 /*
738 The below delete is removed for R12
739 delete from gl_bc_packets
740 where je_batch_id = -999
741 and (trunc(sysdate) - trunc(last_update_date)) >= FND_PROFILE.VALUE_SPECIFIC('PA_MAINTAIN_FC_PACKETS');
742 */
743
744 /*Code Changes for Bug No.2984871 start */
745 l_rowcount:=sql%rowcount;
746 /*Code Changes for Bug No.2984871 end */
747 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
748 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
749 pa_debug.g_err_stage := 'Log: Deleted ' || to_char(l_rowcount) || ' records from GL_BC_PACKETS older than ' || l_profile_value || ' days';
750 pa_debug.write_file('LOG',pa_debug.g_err_stage);
751 END IF;
752 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
753 pa_funds_control_utils.print_message('No of Deleted old packets in gl_bc_packets = '|| to_char(l_rowcount));
754
755 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
756 pa_debug.g_err_stage := 'Log: Entering Loop';
757 pa_debug.write_file('LOG',pa_debug.g_err_stage);
758 END IF;
759
760 --Loop for distinct project in pa_bc_packets
761 FOR eRec in c_pkt_proj LOOP
762
763 l_project_id := eRec.project_id;
764 pa_funds_control_utils.print_message('*******************************************************');
765 pa_funds_control_utils.print_message('Inside Project loop ' || to_char(l_project_id));
766
767 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
768 pa_debug.g_err_stage := '*******************************************************';
769 pa_debug.write_file('LOG',pa_debug.g_err_stage);
770
771 pa_debug.g_err_stage := 'Log: Inside Loop for Project = ' || to_char(l_project_id);
772 pa_debug.write_file('LOG',pa_debug.g_err_stage);
773 END IF;
774
775 --Acquire lock on project
776 IF (pa_debug.acquire_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
777
778 pa_funds_control_utils.print_message('Acquired Lock on project '||to_char(l_project_id));
779
780 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
781 pa_debug.g_err_stage := 'Log: Lock Acquired for Project = ' || to_char(l_project_id) ;
782 pa_debug.write_file('LOG',pa_debug.g_err_stage);
783 END IF;
784
785 pa_funds_control_utils.print_message('Open cursor c_bc_packets');
786 --Open c_bc_packets cursor
787 open c_bc_packets(l_project_id);
788
789 l_count := 0;
790
791 --Start loop
792 LOOP
793 l_count := l_count+1;
794
795 --Initialize the counter for maintaining updated balance records
796 num := 0;
797
798 --Call procedure to initialize the tables
799 InitPlSqlTabs;
800
801 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
802 pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
803 pa_debug.write_file('LOG',pa_debug.g_err_stage);
804 END IF;
805
806 pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
807
808 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
809 pa_debug.g_err_stage := 'Log: Fetch cursor c_bc_packets';
810 pa_debug.write_file('LOG',pa_debug.g_err_stage);
811 END IF;
812
813
814 pa_funds_control_utils.print_message('Fetch cursor c_bc_packets');
815
816 --Fetch cursor c_bc_packets limiting rows
817 FETCH c_bc_packets BULK COLLECT INTO
818 l_PktBdgtVerTab,
819 l_PktProjectTab,
820 l_PktTaskTab ,
821 l_PktBdgtTaskTab,
822 l_PktTTaskTab ,
823 l_PktDocTypTab ,
824 l_PktPeriodTab ,
825 l_PktRlmiTab ,
826 l_PktParResTab ,
827 l_PktSobTab ,
828 l_PktEiDateTab ,
829 l_PktAcctDrTab ,
830 l_PktAcctCrTab ,
831 l_PktActFlagTab ,
832 l_PktRlistTab ,
833 l_PktTPCTab ,
834 l_PktDocHeadTab ,
835 l_PktDocDistTab ,
836 l_PktBcCommTab ,
837 l_PktIdTab ,
838 l_PktExpTypTab ,
839 l_PktPADateTab ,
840 l_PktGLDateTab ,
841 l_PktPdYearTab ,
842 l_PktPdNumTab ,
843 l_PktCatNameTab ,
844 l_PktSrcNameTab ,
845 l_PktExpOrgTab ,
846 l_PktEntDrTab ,
847 l_PktEntCrTab ,
848 l_PktBdgtCCIDTab,
849 l_PktTxnCCIDTab ,
850 l_PktBcPktTab ,
851 l_PktParBcPktTab,
852 l_PktBdgtRlmiTab,
853 l_PktBalPostFlagTab,
854 l_PktEncTypIdTab,
855 l_PktPrjEncTypIdTab,
856 l_PktStatusTab,
857 l_PktOrgIdTab,
858 l_PktCstBurdFlagTab
859 --PA.M
860 ,l_PktDocLineIdTab
861 ,l_PktCompMultiplierTab
862 ,l_PktFcStartDateTab
863 ,l_PktFcEndDateTab
864 ,l_PktCommTotRawAmtTab
865 ,l_PktCommTotBdAmtTab
866 ,l_PktCommRawAmtRelievedTab
867 ,l_PktCommBdAmtRelievedTab
868 ,l_PktSummaryRecordFlagTab
869 ,l_PktExpItemIdTab
870 ,l_PktReference1Tab
871 ,l_PktReference2Tab
872 ,l_PktReference3Tab
873 --R12
874 ,l_PktBcEventIDTab
875 ,l_PktVendorIdTab
876 ,l_PktBudgetLineIDTab
877 ,l_PktBurdenMethodCodeTab
878 ,l_PktDocHdrId2Tab
879 ,l_PktDocDistTypeTab
880 LIMIT rows;
881
882 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
883 pa_debug.g_err_stage := 'Log: No. of records fetched ' || l_PktIdTab.count;
884 pa_debug.write_file('LOG',pa_debug.g_err_stage);
885 END IF;
886
887 pa_funds_control_utils.print_message('No. of records fetched = ' || l_PktIdTab.count);
888
889 --If no rows fetched, exit
890 IF l_PktIdTab.count = 0 THEN
891 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
892 pa_debug.g_err_stage := 'Log: No records in c_bc_packets, exit';
893 pa_debug.write_file('LOG',pa_debug.g_err_stage);
894 END IF;
895 pa_funds_control_utils.print_message('No records from c_bc_packets, exit');
896 EXIT;
897 END IF;
898
899 pa_funds_control_utils.print_message('In c_bc_packets loop');
900
901 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
902 pa_debug.g_err_stage := 'Log: Before update of balances';
903 pa_debug.write_file('LOG',pa_debug.g_err_stage);
904 END IF;
905
906 pa_funds_control_utils.print_message('Before update of balances');
907
908 --Update the balances if the record exists in pa_bc_balances
909 FORALL i in l_PktIdTab.FIRST..l_PktIdTab.LAST
910 UPDATE pa_bc_balances pb
911 SET pb.last_update_date = sysdate,
912 pb.last_update_login = fnd_global.login_id,
913 pb.last_updated_by = fnd_global.user_id,
914 pb.request_id = fnd_global.conc_request_id,
915 pb.program_id = fnd_global.conc_program_id,
916 pb.program_application_id = fnd_global.prog_appl_id,
917 pb.program_update_date = sysdate,
918 pb.actual_period_to_date = nvl(pb.actual_period_to_date,0) +
919 (nvl(l_PktAcctDrTab(i),0)- nvl(l_PktAcctCrTab(i),0)) *
920 decode(l_PktDocTypTab(i),'EXP',1,0),
921 pb.encumb_period_to_date = nvl(pb.encumb_period_to_date,0) +
922 ((nvl(l_PktAcctDrTab(i),0)- nvl(l_PktAcctCrTab(i),0)) *
923 decode(l_PktDocTypTab(i),'REQ',1,'PO',1,'AP',1,'CC_P_CO',1,'CC_C_CO',1,'CC_P_PAY',1,'CC_C_PAY',1,0))
924 WHERE pb.project_id = l_PktProjectTab(i)
925 AND pb.task_id = l_PktTaskTab(i)
926 AND pb.resource_list_member_id = l_PktRlmiTab(i)
927 AND pb.set_of_books_id = l_PktSobTab(i)
928 AND pb.budget_version_id = l_PktBdgtVerTab(i)
929 AND pb.balance_type = l_PktDocTypTab(i)
930 AND ((l_PktTPCTab(i) = 'N' and
931 trunc(l_PktEiDateTab(i)) between trunc(pb.start_date) and trunc(pb.end_date))
932 OR (l_PktTPCTab(i) = 'P' and
933 trunc(l_PktPaDateTab(i)) between trunc(pb.start_date) and trunc(pb.end_date))
934 OR (l_PktTPCTab(i) = 'G' and
935 trunc(l_PktGlDateTab(i)) between trunc(pb.start_date) and trunc(pb.end_date)));
936
937 --Collect no of records updated and store the packet id in another table
938 for i in l_PktIdTab.FIRST..l_PktIdTab.LAST loop
939 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
940 pa_debug.g_err_stage := 'Log: Updating Balances , Index = '|| i || ' RowCount = ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i);
941 pa_debug.write_file('LOG',pa_debug.g_err_stage);
942 END IF;
943 pa_funds_control_utils.print_message('Updating Balances ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i));
944
945 --If no. of rec updated not = 0 then place packet id in another table.
946 --This is to ensure that we update the status of these packets only
947 if (SQL%BULK_ROWCOUNT(i) <> 0) then
948 num := num + 1;
949 l_StsUpdPktIdTab(num) := l_PktIdTab(i);
950 l_StsUpdBcPktIdTab(num) := l_PktBcPktTab(i);
951 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
952 pa_debug.g_err_stage := 'Log: Num = ' || num || ' Status Pkt Id = ' || l_StsUpdPktIdTab(num) || ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num);
953 pa_debug.write_file('LOG',pa_debug.g_err_stage);
954 END IF;
955 pa_funds_control_utils.print_message('Status Packet Id = ' || l_StsUpdPktIdTab(num)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num));
956 end if;
957 end loop;
958
959 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
960 pa_debug.g_err_stage := 'Log: Updated Records = ' || l_StsUpdPktIdTab.count;
961 pa_debug.write_file('LOG',pa_debug.g_err_stage);
962 END IF;
963
964 --The below loop is only for debugging. Can be removed, if log file is huge.
965 if l_StsUpdPktIdTab.count<>0 then
966 for i in l_StsUpdPktIdTab.FIRST..l_StsUpdPktIdTab.LAST loop
967 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
968 pa_debug.g_err_stage := 'Log: No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i);
969 pa_debug.write_file('LOG',pa_debug.g_err_stage);
970 END IF;
971 pa_funds_control_utils.print_message('No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i));
972 end loop;
973 end if;
974
975 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
976 pa_debug.g_err_stage := 'Log: After Update of balances';
977 pa_debug.write_file('LOG',pa_debug.g_err_stage);
978
979 pa_debug.g_err_stage := 'Log: Before Insert into commitments';
980 pa_debug.write_file('LOG',pa_debug.g_err_stage);
981 END IF;
982
983 pa_funds_control_utils.print_message('Inserting Commitments');
984
985 --Insert into pa_bc_commitments_all if bc_commitment_id is null in pa_bc_packets
986 FORALL k in l_PktIdTab.FIRST..l_PktIdTab.LAST
987 insert into pa_bc_commitments_all(
988 bc_commitment_id,
989 packet_id,
990 project_id,
991 task_id,
992 expenditure_type,
993 expenditure_item_date,
994 pa_date,
995 gl_date,
996 period_name,
997 period_year,
998 period_num,
999 je_category_name,
1000 je_source_name,
1001 document_type,
1002 expenditure_organization_id,
1003 document_header_id,
1004 document_distribution_id,
1005 top_task_id,
1006 parent_resource_id,
1007 budget_version_id,
1008 resource_list_member_id,
1009 accounted_dr,
1010 accounted_cr,
1011 entered_dr,
1012 entered_cr,
1013 budget_ccid,
1014 txn_ccid,
1015 bc_packet_id,
1016 parent_bc_packet_id,
1017 set_of_books_id,
1018 bud_resource_list_member_id,
1019 bud_task_id,
1020 actual_flag,
1021 encumbrance_type_id,
1022 proj_encumbrance_type_id,
1023 org_id,
1024 burden_cost_flag,
1025 last_update_date,
1026 last_updated_by,
1027 created_by,
1028 creation_date,
1029 last_update_login,
1030 transfer_status_code,
1031 request_id,
1032 program_id,
1033 program_application_id,
1034 program_update_date
1035 --PA.M
1036 ,Document_Line_Id
1037 ,Compiled_Multiplier
1038 ,Fc_Start_Date
1039 ,Fc_End_Date
1040 ,Comm_Tot_Raw_Amt
1041 ,Comm_Tot_Bd_Amt
1042 ,Comm_Raw_Amt_Relieved
1043 ,Comm_Bd_Amt_Relieved
1044 ,Summary_Record_Flag
1045 ,Exp_Item_Id
1046 ,reference1
1047 ,reference2
1048 ,reference3
1049 --R12
1050 ,bc_event_id
1051 ,budget_line_id
1052 ,burden_method_code
1053 ,vendor_id
1054 ,document_header_id_2
1055 ,document_distribution_type)
1056 select
1057 pa_bc_commitments_s.nextval,
1058 l_PktIdTab(k),
1059 l_PktProjectTab(k),
1060 l_PktTaskTab(k),
1061 l_PktExpTypTab(k),
1062 l_PktEiDateTab(k),
1063 l_PktPaDateTab(k),
1064 l_PktGlDateTab(k),
1065 l_PktPeriodTab(k),
1066 l_PktPdYearTab(k),
1067 l_PktPdNumTab(k),
1068 l_PktCatNameTab(k),
1069 l_PktSrcNameTab(k),
1070 l_PktDocTypTab(k),
1071 l_PktExpOrgTab(k),
1072 l_PktDocHeadTab(k),
1073 l_PktDocDistTab(k),
1074 l_PktTTaskTab(k),
1075 l_PktParResTab(k),
1076 l_PktBdgtVerTab(k),
1077 l_PktRlmiTab(k),
1078 l_PktAcctDrTab(k),
1079 l_PktAcctCrTab(k),
1080 l_PktEntDrTab(k),
1081 l_PktEntCrTab(k),
1082 l_PktBdgtCCIDTab(k),
1083 l_PktTxnCCIDTab(k),
1084 l_PktBcPktTab(k),
1085 l_PktParBCPktTab(k),
1086 l_PktSobTab(k),
1087 l_PktBdgtRlmiTab(k),
1088 l_PktBdgtTaskTab(k),
1089 l_PktActFlagTab(k),
1090 l_PktEncTypIdTab(k),
1091 l_PktPrjEncTypIdTab(k),
1092 l_PktOrgIdTab(k),
1093 l_PktCstBurdFlagTab(k),
1094 sysdate,
1095 FND_GLOBAL.USER_ID,
1096 FND_GLOBAL.USER_ID,
1097 sysdate,
1098 FND_GLOBAL.LOGIN_ID,
1099 'P',
1100 fnd_global.conc_request_id,
1101 fnd_global.conc_program_id,
1102 fnd_global.prog_appl_id,
1103 sysdate
1104 --PA.M
1105 ,l_PktDocLineIdTab(k)
1106 ,l_PktCompMultiplierTab(k)
1107 ,l_PktFcStartDateTab(k)
1108 ,l_PktFcEndDateTab(k)
1109 ,l_PktCommTotRawAmtTab(k)
1110 ,l_PktCommTotBdAmtTab(k)
1111 ,l_PktCommRawAmtRelievedTab(k)
1112 ,l_PktCommBdAmtRelievedTab(k)
1113 ,l_PktSummaryRecordFlagTab(k)
1114 ,l_PktExpItemIdTab(k)
1115 ,l_PktReference1Tab(k)
1116 ,l_PktReference2Tab(k)
1117 ,l_PktReference3Tab(k)
1118 --R12
1119 ,l_PktBcEventIDTab(k)
1120 ,l_PktBudgetLineIdTab(k)
1121 ,l_PktBurdenMethodCodeTab(k)
1122 ,l_PktVendorIdTab(k)
1123 ,l_PktDocHdrId2Tab(k)
1124 ,l_PktDocDistTypeTab(k)
1125 from dual
1126 where l_PktBcCommTab(k) is null
1127 and l_PktRlmiTab(k) is not null
1128 and l_PktBdgtVerTab(k) is not null
1129 and l_PktDocTypTab(k) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY')
1130 and not exists (select 'X'
1131 from pa_bc_commitments_all
1132 where document_type = l_PktDocTypTab(k)
1133 and l_PktDocTypTab(k) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY')
1134 and document_header_id = l_PktDocHeadTab(k)
1135 and (document_distribution_id = l_PktDocDistTab(k)
1136 or (document_distribution_id = -9999
1137 and
1138 document_line_id = l_PktDocLineIdTab(k))
1139 )
1140 and bc_packet_id = l_PktBcPktTab(k));
1141
1142 --The below loop is only for debugging. Can be removed, if log file is huge.
1143 for i in l_PktIdTab.first..l_PktIdTab.last loop
1144 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1145 pa_debug.g_err_stage := 'Log: No of commitment inserts = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1146 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1147 END IF;
1148 pa_funds_control_utils.print_message('No. of commitment insert = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1149 end loop;
1150
1151 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1152 pa_debug.g_err_stage := 'Log: After Insert into commitments';
1153 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1154
1155 pa_debug.g_err_stage := 'Log: Before Update of commitments';
1156 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1157 END IF;
1158
1159 pa_funds_control_utils.print_message('Before update of pa_bc_commitments_all');
1160
1161 --Update pa_bc_commitments_all if bc_commitment_id is not null
1162 FORALL j in l_PktIdTab.FIRST..l_PktIdTab.LAST
1163 UPDATE pa_bc_commitments_all pbc
1164 SET pbc.packet_id = l_PktIdTab(j),
1165 pbc.top_task_id = l_PktTTaskTab(j),
1166 pbc.parent_resource_id = l_PktParResTab(j),
1167 pbc.budget_version_id = l_PktBdgtVerTab(j),
1168 pbc.resource_list_member_id = l_PktRlmiTab(j),
1169 pbc.entered_dr = l_PktEntDrTab(j),
1170 pbc.entered_cr = l_PktEntCrTab(j),
1171 pbc.accounted_dr = l_PktAcctDrTab(j),
1172 pbc.accounted_cr = l_PktAcctCrTab(j),
1173 pbc.budget_ccid = l_PktBdgtCCIDTab(j),
1174 pbc.txn_ccid = l_PktTxnCCIDTab(j),
1175 --pbc.bc_packet_id = l_PktBCPktTab(j),
1176 --pbc.parent_bc_packet_id = l_PktParBCPktTab(j),
1177 pbc.set_of_books_id = l_PktSobTab(j),
1178 pbc.bud_resource_list_member_id = l_PktBdgtRlmiTab(j),
1179 pbc.bud_task_id = l_PktBdgtTaskTab(j),
1180 pbc.actual_flag = l_PktActFlagTab(j),
1181 pbc.encumbrance_type_id = l_PktEncTypIdTab(j),
1182 pbc.proj_encumbrance_type_id = l_PktPrjEncTypIdTab(j),
1183 pbc.last_updated_by = fnd_global.user_id,
1184 pbc.last_update_date = sysdate,
1185 pbc.last_update_login = fnd_global.login_id,
1186 pbc.request_id = fnd_global.conc_request_id,
1187 pbc.program_id = fnd_global.conc_program_id,
1188 pbc.program_application_id = fnd_global.prog_appl_id,
1189 pbc.program_update_date = sysdate,
1190 pbc.budget_line_id = l_PktBudgetLineIdTab(j)
1191 where ((pbc.bc_commitment_id = l_PktBcCommTab(j))
1192 or (l_PktBcCommTab(j) is null
1193 --Bug 2779986: This exist clause will be true for reversing commitment txns
1194 --that came when baseline is in progress (what we call delta txns) for the project.
1195 --We have to update only that record which satisfies the document id combination
1196 --and bc_packet_id and not all the records. Hence added the extra conditions.
1197 and pbc.document_header_id = l_PktDocHeadTab(j)
1198 and (pbc.document_distribution_id = l_PktDocDistTab(j)
1199 or (pbc.document_distribution_id = -9999
1200 and
1201 pbc.document_line_id = l_PktDocLineIdTab(j))
1202 )
1203 and pbc.bc_packet_id = l_PktBcPktTab(j)
1204 and exists (select 'X'
1205 from pa_bc_commitments_all pbc1
1206 where pbc1.project_id = l_PktProjectTab(j)
1207 and pbc1.task_id = l_PktTaskTab(j)
1208 and pbc1.document_type = l_PktDocTypTab(j)
1209 and l_PktDocTypTab(j) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY')
1210 and pbc1.document_header_id = l_PktDocHeadTab(j)
1211 and (pbc1.document_distribution_id = l_PktDocDistTab(j)
1212 or (pbc1.document_distribution_id = -9999
1213 and
1214 pbc1.document_line_id = l_PktDocLineIdTab(j))
1215 )
1216 and pbc1.bc_packet_id = l_PktBcPktTab(j)
1217 and pbc1.budget_version_id < l_PktBdgtVerTab(j))))
1218 and pbc.project_id = l_PktProjectTab(j)
1219 and l_PktRlmiTab(j) is not null
1220 and l_PktBdgtVerTab(j) is not null
1221 and l_PktDocTypTab(j) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY');
1222
1223 --The below loop is only for debugging. Can be removed, if log file is huge.
1224 for i in l_PktIdTab.first..l_PktIdTab.last loop
1225 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1226 pa_debug.g_err_stage := 'Log: No of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1227 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1228 END IF;
1229 pa_funds_control_utils.print_message('No. of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1230 end loop;
1231
1232 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1233 pa_debug.g_err_stage := 'Log: After Update of commitments';
1234 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1235
1236 pa_debug.g_err_stage := 'Log: Before update status_code, balance_posted_flag in c_bc_packets loop '||l_StsUpdPktIdTab.count;
1237 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1238 END IF;
1239
1240 pa_funds_control_utils.print_message('Before update of status_code');
1241 pa_funds_control_utils.print_message('No. of status updates in c_bc_packets= '||l_StsUpdPktIdTab.count);
1242
1243 --Update status_code and balance_posted_flag
1244 IF (l_StsUpdPktIdTab.count <> 0) THEN
1245 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1246 pa_debug.write_file('LOG', 'Pkt FIRST = ' || l_StsUpdPktIdTab.FIRST || ' LAST = '|| l_StsUpdPktIdTab.LAST );
1247 pa_debug.write_file('LOG', 'Bc Pkt FIRST = ' || l_StsUpdBcPktIdTab.FIRST || ' LAST = '|| l_StsUpdBcPktIdTab.LAST );
1248 END IF;
1249 FORALL m in l_StsUpdPktIdTab.FIRST..l_StsUpdPktIdTab.LAST
1250 UPDATE pa_bc_packets pbc
1251 SET pbc.status_code = 'X',
1252 pbc.balance_posted_flag = 'Y',
1253 pbc.last_update_date = sysdate,
1254 pbc.last_update_login = fnd_global.login_id,
1255 pbc.last_updated_by = fnd_global.user_id
1256 WHERE pbc.status_code = 'A'
1257 AND pbc.packet_id = l_StsUpdPktIdTab(m)
1258 AND pbc.bc_packet_id = l_StsUpdBcPktIdTab(m)
1259 AND pbc.balance_posted_flag = 'N'
1260 AND l_PktStatusTab(m) = 'A'
1261 AND l_PktBalPostFlagTab(m) = 'N';
1262 END IF;
1263
1264 --The below loop is for debugging, can be removed if log file is huge
1265 if (l_StsUpdPktIdTab.count <> 0) then
1266 for i in l_StsUpdPktIdTab.FIRST..l_StsUpdPktIdTab.LAST loop
1267 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1268 pa_debug.g_err_stage := 'Log: No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1269 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1270 END IF;
1271 pa_funds_control_utils.print_message('No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1272 end loop;
1273 end if;
1274
1275 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1276 pa_debug.g_err_stage := 'Log: After Update of status_code and balance_posted_flag in c_bc_packets loop';
1277 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1278 END IF;
1279
1280 --Commit in a batch i.e. 200 rows
1281 commit;
1282 pa_funds_control_utils.print_message('End loop');
1283
1284 EXIT WHEN c_bc_packets%NOTFOUND;
1285 END LOOP;
1286
1287 pa_funds_control_utils.print_message('Close c_bc_packets');
1288
1289 CLOSE c_bc_packets;
1290 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1291 pa_debug.g_err_stage := 'Log: Open cursor c_ins_packets';
1292 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1293 END IF;
1294
1295 pa_funds_control_utils.print_message('Open cursor c_ins_packets');
1296
1297 l_count := 0;
1298
1299 --Open cursor c_ins_packets
1300 OPEN c_ins_packets(l_project_id);
1301 LOOP
1302
1303 --Initialize PL/SQL tables
1304 InitPlSqlTabs2;
1305
1306 l_count := l_count+1;
1307
1308 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1309 pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
1310 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1311 END IF;
1312
1313 pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
1314
1315 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1316 pa_debug.g_err_stage := 'Log: Fetch cursor c_ins_packets';
1317 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1318 END IF;
1319
1320 pa_funds_control_utils.print_message('Fetch cursor c_ins_packets');
1321
1322 --Fetch records from cursor in batch
1323 FETCH c_ins_packets bulk collect into
1324 l_InsBdgtVerTab,
1325 l_InsProjectTab,
1326 l_InsTaskTab,
1327 l_InsTTaskTab,
1328 l_InsDocTypTab,
1329 l_InsRlmiTab,
1330 l_InsParResTab,
1331 l_InsSobTab,
1332 l_InsActPTDTab,
1333 l_InsEncPTDTab,
1334 l_InsBalPostFlagTab,
1335 l_InsStatusTab,
1336 l_InsTPCTab,
1337 --l_InsEiDateTab,
1338 l_InsStDateTab,
1339 l_InsEdDateTab
1340 /*Bug 3007393*/
1341 /*,l_InsOrgIdTab*/
1342 LIMIT rows;
1343
1344 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1345 pa_debug.g_err_stage := 'Log: No. of records fetched = '||l_InsProjectTab.count;
1346 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1347 END IF;
1348
1349 pa_funds_control_utils.print_message('No. of records fetched = ' || l_InsProjectTab.count);
1350
1351 --If no. of records fetched = 0 then exit
1352 IF l_InsProjectTab.count = 0 THEN
1353 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1354 pa_debug.g_err_stage := 'Log: No records from cursor c_ins_packets, exit';
1355 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1356 END IF;
1357 pa_funds_control_utils.print_message('No records from cursor c_ins_packets, exit');
1358 EXIT;
1359 END IF;
1360
1361 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1362 pa_debug.g_err_stage := 'Log: Before inserting balances';
1363 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1364 END IF;
1365
1366 pa_funds_control_utils.print_message('Before inserting into pa_bc_balances');
1367
1368 --Insert into pa_bc_balances
1369 FORALL p in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1370 insert into pa_bc_balances(project_id
1371 ,task_id
1372 ,top_task_id
1373 ,resource_list_member_id
1374 ,set_of_books_id
1375 ,budget_version_id
1376 ,balance_type
1377 ,last_update_date
1378 ,last_updated_by
1379 ,created_by
1380 ,creation_date
1381 ,last_update_login
1382 ,start_date
1383 ,end_date
1384 ,parent_member_id
1385 ,budget_period_to_date
1386 ,actual_period_to_date
1387 ,encumb_period_to_date
1388 ,request_id
1389 ,program_id
1390 ,program_application_id
1391 ,program_update_date)
1392 select
1393 l_InsProjectTab(p),
1394 l_InsTaskTab(p),
1395 l_InsTTaskTab(p),
1396 l_InsRlmiTab(p),
1397 l_InsSobTab(p),
1398 l_InsBdgtVerTab(p),
1399 l_InsDocTypTab(p),
1400 sysdate,
1401 FND_GLOBAL.USER_ID,
1402 FND_GLOBAL.USER_ID,
1403 sysdate,
1404 FND_GLOBAL.LOGIN_ID,
1405 --GetBCBalStartDate(l_InsTPCTab(p),l_InsProjectTab(p),l_InsEiDateTab(p),
1406 -- l_InsBdgtVerTab(p),l_InsSobTab(p),l_InsOrgIdTab(p)),
1407 --GetBCBalEndDate(l_InsTPCTab(p),l_InsProjectTab(p),l_InsEiDateTab(p),
1408 -- l_InsBdgtVerTab(p),l_InsSobTab(p),l_InsOrgIdTab(p)),
1409 l_InsStDateTab(p),
1410 l_InsEdDateTab(p),
1411 l_InsParResTab(p),
1412 0,
1413 l_InsActPTDTab(p),
1414 l_InsEncPTDTab(p),
1415 fnd_global.conc_request_id,
1416 fnd_global.conc_program_id,
1417 fnd_global.prog_appl_id,
1418 sysdate
1419 from dual where l_InsDocTypTab(p) in ('EXP', 'AP','PO','REQ','CC_C_CO','CC_P_CO','CC_C_PAY','CC_P_PAY');
1420
1421 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1422 pa_debug.g_err_stage := 'Log: After inserting balances';
1423 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1424
1425 pa_debug.g_err_stage := 'Log: Before update status_code and balance_posted_flag in c_ins_packets';
1426 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1427 END IF;
1428
1429 pa_funds_control_utils.print_message('Update status_code and balance_posted_flag');
1430
1431 --Update status_code and balance_posted_flag in pa_bc_packets
1432 FORALL m in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1433 UPDATE pa_bc_packets pbc
1434 SET pbc.status_code = 'X',
1435 pbc.balance_posted_flag = 'Y',
1436 pbc.last_update_date = sysdate,
1437 pbc.last_update_login = fnd_global.login_id,
1438 pbc.last_updated_by = fnd_global.user_id
1439 WHERE pbc.status_code = 'A'
1440 AND pbc.project_id = l_InsProjectTab(m)
1441 AND pbc.task_id = l_InsTaskTab(m)
1442 AND pbc.balance_posted_flag = 'N'
1443 AND l_InsStatusTab(m) = 'A'
1444 AND l_InsBalPostFlagTab(m) = 'N';
1445
1446 --Below loop for debugging, can be removed if log file is huge
1447 for i in l_InsProjectTab.first..l_InsProjectTab.last loop
1448 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1449 pa_debug.g_err_stage := 'Log: No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i);
1450 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1451 END IF;
1452 pa_funds_control_utils.print_message('No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i));
1453 end loop;
1454
1455 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1456 pa_debug.g_err_stage := 'Log: After Update of status_code and balance_posted_flag in c_ins_packets loop';
1457 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1458 END IF;
1459
1460 --Commit in a batch, i.e. 200 rows
1461 commit;
1462 EXIT WHEN c_ins_packets%NOTFOUND;
1463
1464 END LOOP;
1465
1466 --Close c_ins_packets cursor
1467 CLOSE c_ins_packets;
1468
1469 --Release lock on project
1470 IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1471 pa_funds_control_utils.print_message('Releasing lock for '|| l_project_id);
1472 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1473 pa_debug.g_err_stage := 'Log: Finished processing, release lock on Project = ' || to_char(l_project_id);
1474 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1475 END IF;
1476 null;
1477 END IF;
1478
1479 ELSE
1480 --Unable to acquire user lock
1481 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1482 pa_debug.g_err_stage := 'Log: Could not acquire lock for Project = '||to_char(l_project_id);
1483 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1484 END IF;
1485 END IF;
1486
1487 END LOOP;
1488
1489 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1490 pa_debug.g_err_stage := '*******************************************************';
1491 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1492
1493 pa_debug.g_err_stage := 'Log: End of Update_Act_Enc_Balance';
1494 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1495 END IF;
1496
1497 pa_fck_util.debug_msg('PB:Exiting Sweeper');
1498
1499 --Reset the error stack when returning to the calling program
1500 PA_DEBUG.Reset_Err_Stack;
1501
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 --Since release_user_lock always issues a commit, do a
1505 --rollback before calling release_user_lock.
1506 rollback;
1507
1508 x_return_status := fnd_api.g_ret_sts_unexp_error;
1509
1510 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_SWEEPER'
1511 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1512
1513 x_error_message_code := (SQLCODE||' '||SQLERRM);
1514
1515 IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1516 pa_funds_control_utils.print_message('In others Releasing lock for proj '|| l_project_id);
1517 null;
1518 END IF;
1519
1520 IF c_bc_packets%isopen THEN
1521 close c_bc_packets;
1522 END IF;
1523 IF c_ins_packets%isopen THEN
1524 close c_ins_packets;
1525 END IF;
1526 IF c_delete_pkts%isopen THEN
1527 close c_delete_pkts;
1528 END IF;
1529
1530 raise;
1531 END update_act_enc_balance;
1532
1533 END PA_SWEEPER;