[Home] [Help]
PACKAGE BODY: APPS.PA_SWEEPER
Source
1 PACKAGE BODY PA_SWEEPER AS
2 -- $Header: PAFCUAEB.pls 120.7 2006/03/31 03:52:46 rshaik noship $
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 BEGIN
597
598 --Initialize the error stack
599 PA_DEBUG.init_err_stack('PA_SWEEPER.UPDATE_ACT_ENC_BALANCE');
600
601 --Initialize the message table for FND_MSG_PUB
602 fnd_msg_pub.initialize;
603
604 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
605 l_debug_mode := NVL(l_debug_mode, 'N');
606
607 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
608
609 --Initialize the return status to success
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611
612 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
613 pa_debug.g_err_stage := 'Log: Start of Update_Act_Enc_Balance';
614 pa_debug.write_file('LOG',pa_debug.g_err_stage);
615 END IF;
616
617 pa_funds_control_utils.print_message('Entering Sweeper');
618 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
619 pa_fck_util.debug_msg('PB:Entering Sweeper');
620 END IF;
621
622 --deleting rows older than 'X' days which is set in the profile
623 --Pkts with status C should not exist ideally
624 --Pkts with status B will be taken care of during rebaselining
625 --Pkts with status P are yet to be funds checked, do not delete here.
626 pa_funds_control_utils.print_message('Open c_delete_pkts');
627 open c_delete_pkts;
628 loop
629 l_RowIdTab.Delete;
630 fetch c_delete_pkts bulk collect into
631 l_RowIdTab
632 limit rows;
633
634 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
635 pa_debug.g_err_stage := 'Log: No. of records to delete ' || l_RowIdTab.count;
636 pa_debug.write_file('LOG',pa_debug.g_err_stage);
637 END IF;
638
639 pa_funds_control_utils.print_message('No. of records to delete = ' || l_RowIdTab.count);
640
641 if l_RowIdTab.count = 0 then
642 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
643 pa_debug.g_err_stage := 'Log: No records in c_delete_pkts, exit';
644 pa_debug.write_file('LOG',pa_debug.g_err_stage);
645 END IF;
646 pa_funds_control_utils.print_message('No records from c_delete_pkts, exit');
647 exit;
648 end if;
649
650 FORALL i in l_RowIdTab.first..l_RowIdTab.last
651 delete from pa_bc_packets
652 where rowid = l_RowIdTab(i);
653
654 /*Code Changes for Bug No.2984871 start */
655 l_rowcount:=sql%rowcount;
656 /*Code Changes for Bug No.2984871 end */
657 commit;
658 exit when c_delete_pkts%notfound;
659 end loop;
660 close c_delete_pkts;
661 pa_funds_control_utils.print_message('Close c_delete_pkts');
662
663 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
664 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
665 pa_debug.g_err_stage := 'Log: Last Deleted ' || to_char(l_rowcount) || ' records from PA_BC_PACKETS older than ' || l_profile_value || ' days';
666 pa_debug.write_file('LOG',pa_debug.g_err_stage);
667 END IF;
668 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
669 pa_funds_control_utils.print_message('No of Deleted old packets in pa_bc_packets = ' || to_char(l_rowcount));
670
671 --deleting rows older than 'X' days which is set in the profile
672 --(batch_id of -999 are those belonging to actuals)
673 /*
674 The below delete is removed for R12
675 delete from gl_bc_packets
676 where je_batch_id = -999
677 and (trunc(sysdate) - trunc(last_update_date)) >= FND_PROFILE.VALUE_SPECIFIC('PA_MAINTAIN_FC_PACKETS');
678 */
679
680 /*Code Changes for Bug No.2984871 start */
681 l_rowcount:=sql%rowcount;
682 /*Code Changes for Bug No.2984871 end */
683 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
684 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
685 pa_debug.g_err_stage := 'Log: Deleted ' || to_char(l_rowcount) || ' records from GL_BC_PACKETS older than ' || l_profile_value || ' days';
686 pa_debug.write_file('LOG',pa_debug.g_err_stage);
687 END IF;
688 /* Bug 2984871: Replaced sql%rwocount with l_rowcount in the below statement */
689 pa_funds_control_utils.print_message('No of Deleted old packets in gl_bc_packets = '|| to_char(l_rowcount));
690
691 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
692 pa_debug.g_err_stage := 'Log: Entering Loop';
693 pa_debug.write_file('LOG',pa_debug.g_err_stage);
694 END IF;
695
696 --Loop for distinct project in pa_bc_packets
697 FOR eRec in c_pkt_proj LOOP
698
699 l_project_id := eRec.project_id;
700 pa_funds_control_utils.print_message('*******************************************************');
701 pa_funds_control_utils.print_message('Inside Project loop ' || to_char(l_project_id));
702
703 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
704 pa_debug.g_err_stage := '*******************************************************';
705 pa_debug.write_file('LOG',pa_debug.g_err_stage);
706
707 pa_debug.g_err_stage := 'Log: Inside Loop for Project = ' || to_char(l_project_id);
708 pa_debug.write_file('LOG',pa_debug.g_err_stage);
709 END IF;
710
711 --Acquire lock on project
712 IF (pa_debug.acquire_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
713
714 pa_funds_control_utils.print_message('Acquired Lock on project '||to_char(l_project_id));
715
716 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
717 pa_debug.g_err_stage := 'Log: Lock Acquired for Project = ' || to_char(l_project_id) ;
718 pa_debug.write_file('LOG',pa_debug.g_err_stage);
719 END IF;
720
721 pa_funds_control_utils.print_message('Open cursor c_bc_packets');
722 --Open c_bc_packets cursor
723 open c_bc_packets(l_project_id);
724
725 l_count := 0;
726
727 --Start loop
728 LOOP
729 l_count := l_count+1;
730
731 --Initialize the counter for maintaining updated balance records
732 num := 0;
733
734 --Call procedure to initialize the tables
735 InitPlSqlTabs;
736
737 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
738 pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
739 pa_debug.write_file('LOG',pa_debug.g_err_stage);
740 END IF;
741
742 pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
743
744 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
745 pa_debug.g_err_stage := 'Log: Fetch cursor c_bc_packets';
746 pa_debug.write_file('LOG',pa_debug.g_err_stage);
747 END IF;
748
749
750 pa_funds_control_utils.print_message('Fetch cursor c_bc_packets');
751
752 --Fetch cursor c_bc_packets limiting rows
753 FETCH c_bc_packets BULK COLLECT INTO
754 l_PktBdgtVerTab,
755 l_PktProjectTab,
756 l_PktTaskTab ,
757 l_PktBdgtTaskTab,
758 l_PktTTaskTab ,
759 l_PktDocTypTab ,
760 l_PktPeriodTab ,
761 l_PktRlmiTab ,
762 l_PktParResTab ,
763 l_PktSobTab ,
764 l_PktEiDateTab ,
765 l_PktAcctDrTab ,
766 l_PktAcctCrTab ,
767 l_PktActFlagTab ,
768 l_PktRlistTab ,
769 l_PktTPCTab ,
770 l_PktDocHeadTab ,
771 l_PktDocDistTab ,
772 l_PktBcCommTab ,
773 l_PktIdTab ,
774 l_PktExpTypTab ,
775 l_PktPADateTab ,
776 l_PktGLDateTab ,
777 l_PktPdYearTab ,
778 l_PktPdNumTab ,
779 l_PktCatNameTab ,
780 l_PktSrcNameTab ,
781 l_PktExpOrgTab ,
782 l_PktEntDrTab ,
783 l_PktEntCrTab ,
784 l_PktBdgtCCIDTab,
785 l_PktTxnCCIDTab ,
786 l_PktBcPktTab ,
787 l_PktParBcPktTab,
788 l_PktBdgtRlmiTab,
789 l_PktBalPostFlagTab,
790 l_PktEncTypIdTab,
791 l_PktPrjEncTypIdTab,
792 l_PktStatusTab,
793 l_PktOrgIdTab,
794 l_PktCstBurdFlagTab
795 --PA.M
796 ,l_PktDocLineIdTab
797 ,l_PktCompMultiplierTab
798 ,l_PktFcStartDateTab
799 ,l_PktFcEndDateTab
800 ,l_PktCommTotRawAmtTab
801 ,l_PktCommTotBdAmtTab
802 ,l_PktCommRawAmtRelievedTab
803 ,l_PktCommBdAmtRelievedTab
804 ,l_PktSummaryRecordFlagTab
805 ,l_PktExpItemIdTab
806 ,l_PktReference1Tab
807 ,l_PktReference2Tab
808 ,l_PktReference3Tab
809 --R12
810 ,l_PktBcEventIDTab
811 ,l_PktVendorIdTab
812 ,l_PktBudgetLineIDTab
813 ,l_PktBurdenMethodCodeTab
814 ,l_PktDocHdrId2Tab
815 ,l_PktDocDistTypeTab
816 LIMIT rows;
817
818 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
819 pa_debug.g_err_stage := 'Log: No. of records fetched ' || l_PktIdTab.count;
820 pa_debug.write_file('LOG',pa_debug.g_err_stage);
821 END IF;
822
823 pa_funds_control_utils.print_message('No. of records fetched = ' || l_PktIdTab.count);
824
825 --If no rows fetched, exit
826 IF l_PktIdTab.count = 0 THEN
827 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
828 pa_debug.g_err_stage := 'Log: No records in c_bc_packets, exit';
829 pa_debug.write_file('LOG',pa_debug.g_err_stage);
830 END IF;
831 pa_funds_control_utils.print_message('No records from c_bc_packets, exit');
832 EXIT;
833 END IF;
834
835 pa_funds_control_utils.print_message('In c_bc_packets loop');
836
837 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
838 pa_debug.g_err_stage := 'Log: Before update of balances';
839 pa_debug.write_file('LOG',pa_debug.g_err_stage);
840 END IF;
841
842 pa_funds_control_utils.print_message('Before update of balances');
843
844 --Update the balances if the record exists in pa_bc_balances
845 FORALL i in l_PktIdTab.FIRST..l_PktIdTab.LAST
846 UPDATE pa_bc_balances pb
847 SET pb.last_update_date = sysdate,
848 pb.last_update_login = fnd_global.login_id,
849 pb.last_updated_by = fnd_global.user_id,
850 pb.request_id = fnd_global.conc_request_id,
851 pb.program_id = fnd_global.conc_program_id,
852 pb.program_application_id = fnd_global.prog_appl_id,
853 pb.program_update_date = sysdate,
854 pb.actual_period_to_date = nvl(pb.actual_period_to_date,0) +
855 (nvl(l_PktAcctDrTab(i),0)- nvl(l_PktAcctCrTab(i),0)) *
856 decode(l_PktDocTypTab(i),'EXP',1,0),
857 pb.encumb_period_to_date = nvl(pb.encumb_period_to_date,0) +
858 ((nvl(l_PktAcctDrTab(i),0)- nvl(l_PktAcctCrTab(i),0)) *
859 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))
860 WHERE pb.project_id = l_PktProjectTab(i)
861 AND pb.task_id = l_PktTaskTab(i)
862 AND pb.resource_list_member_id = l_PktRlmiTab(i)
863 AND pb.set_of_books_id = l_PktSobTab(i)
864 AND pb.budget_version_id = l_PktBdgtVerTab(i)
865 AND pb.balance_type = l_PktDocTypTab(i)
866 AND ((l_PktTPCTab(i) = 'N' and
867 trunc(l_PktEiDateTab(i)) between trunc(pb.start_date) and trunc(pb.end_date))
868 OR (l_PktTPCTab(i) = 'P' and
869 trunc(l_PktPaDateTab(i)) between trunc(pb.start_date) and trunc(pb.end_date))
870 OR (l_PktTPCTab(i) = 'G' and
871 trunc(l_PktGlDateTab(i)) between trunc(pb.start_date) and trunc(pb.end_date)));
872
873 --Collect no of records updated and store the packet id in another table
874 for i in l_PktIdTab.FIRST..l_PktIdTab.LAST loop
875 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
876 pa_debug.g_err_stage := 'Log: Updating Balances , Index = '|| i || ' RowCount = ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i);
877 pa_debug.write_file('LOG',pa_debug.g_err_stage);
878 END IF;
879 pa_funds_control_utils.print_message('Updating Balances ' || SQL%BULK_ROWCOUNT(i) ||' Pkt Id ' || l_PktBcPktTab(i));
880
881 --If no. of rec updated not = 0 then place packet id in another table.
882 --This is to ensure that we update the status of these packets only
883 if (SQL%BULK_ROWCOUNT(i) <> 0) then
884 num := num + 1;
885 l_StsUpdPktIdTab(num) := l_PktIdTab(i);
886 l_StsUpdBcPktIdTab(num) := l_PktBcPktTab(i);
887 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
888 pa_debug.g_err_stage := 'Log: Num = ' || num || ' Status Pkt Id = ' || l_StsUpdPktIdTab(num) || ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num);
889 pa_debug.write_file('LOG',pa_debug.g_err_stage);
890 END IF;
891 pa_funds_control_utils.print_message('Status Packet Id = ' || l_StsUpdPktIdTab(num)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(num));
892 end if;
893 end loop;
894
895 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
896 pa_debug.g_err_stage := 'Log: Updated Records = ' || l_StsUpdPktIdTab.count;
897 pa_debug.write_file('LOG',pa_debug.g_err_stage);
898 END IF;
899
900 --The below loop is only for debugging. Can be removed, if log file is huge.
901 if l_StsUpdPktIdTab.count<>0 then
902 for i in l_StsUpdPktIdTab.FIRST..l_StsUpdPktIdTab.LAST loop
903 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
904 pa_debug.g_err_stage := 'Log: No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i);
905 pa_debug.write_file('LOG',pa_debug.g_err_stage);
906 END IF;
907 pa_funds_control_utils.print_message('No of Status updates = ' || l_StsUpdPktIdTab(i)|| ' Bc Pkt = ' || l_StsUpdBcPktIdTab(i));
908 end loop;
909 end if;
910
911 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
912 pa_debug.g_err_stage := 'Log: After Update of balances';
913 pa_debug.write_file('LOG',pa_debug.g_err_stage);
914
915 pa_debug.g_err_stage := 'Log: Before Insert into commitments';
916 pa_debug.write_file('LOG',pa_debug.g_err_stage);
917 END IF;
918
919 pa_funds_control_utils.print_message('Inserting Commitments');
920
921 --Insert into pa_bc_commitments_all if bc_commitment_id is null in pa_bc_packets
922 FORALL k in l_PktIdTab.FIRST..l_PktIdTab.LAST
923 insert into pa_bc_commitments_all(
924 bc_commitment_id,
925 packet_id,
926 project_id,
927 task_id,
928 expenditure_type,
929 expenditure_item_date,
930 pa_date,
931 gl_date,
932 period_name,
933 period_year,
934 period_num,
935 je_category_name,
936 je_source_name,
937 document_type,
938 expenditure_organization_id,
939 document_header_id,
940 document_distribution_id,
941 top_task_id,
942 parent_resource_id,
943 budget_version_id,
944 resource_list_member_id,
945 accounted_dr,
946 accounted_cr,
947 entered_dr,
948 entered_cr,
949 budget_ccid,
950 txn_ccid,
951 bc_packet_id,
952 parent_bc_packet_id,
953 set_of_books_id,
954 bud_resource_list_member_id,
955 bud_task_id,
956 actual_flag,
957 encumbrance_type_id,
958 proj_encumbrance_type_id,
959 org_id,
960 burden_cost_flag,
961 last_update_date,
962 last_updated_by,
963 created_by,
964 creation_date,
965 last_update_login,
966 transfer_status_code,
967 request_id,
968 program_id,
969 program_application_id,
970 program_update_date
971 --PA.M
972 ,Document_Line_Id
973 ,Compiled_Multiplier
974 ,Fc_Start_Date
975 ,Fc_End_Date
976 ,Comm_Tot_Raw_Amt
977 ,Comm_Tot_Bd_Amt
978 ,Comm_Raw_Amt_Relieved
979 ,Comm_Bd_Amt_Relieved
980 ,Summary_Record_Flag
981 ,Exp_Item_Id
982 ,reference1
983 ,reference2
984 ,reference3
985 --R12
986 ,bc_event_id
987 ,budget_line_id
988 ,burden_method_code
989 ,vendor_id
990 ,document_header_id_2
991 ,document_distribution_type)
992 select
993 pa_bc_commitments_s.nextval,
994 l_PktIdTab(k),
995 l_PktProjectTab(k),
996 l_PktTaskTab(k),
997 l_PktExpTypTab(k),
998 l_PktEiDateTab(k),
999 l_PktPaDateTab(k),
1000 l_PktGlDateTab(k),
1001 l_PktPeriodTab(k),
1002 l_PktPdYearTab(k),
1003 l_PktPdNumTab(k),
1004 l_PktCatNameTab(k),
1005 l_PktSrcNameTab(k),
1006 l_PktDocTypTab(k),
1007 l_PktExpOrgTab(k),
1008 l_PktDocHeadTab(k),
1009 l_PktDocDistTab(k),
1010 l_PktTTaskTab(k),
1011 l_PktParResTab(k),
1012 l_PktBdgtVerTab(k),
1013 l_PktRlmiTab(k),
1014 l_PktAcctDrTab(k),
1015 l_PktAcctCrTab(k),
1016 l_PktEntDrTab(k),
1017 l_PktEntCrTab(k),
1018 l_PktBdgtCCIDTab(k),
1019 l_PktTxnCCIDTab(k),
1020 l_PktBcPktTab(k),
1021 l_PktParBCPktTab(k),
1022 l_PktSobTab(k),
1023 l_PktBdgtRlmiTab(k),
1024 l_PktBdgtTaskTab(k),
1025 l_PktActFlagTab(k),
1026 l_PktEncTypIdTab(k),
1027 l_PktPrjEncTypIdTab(k),
1028 l_PktOrgIdTab(k),
1029 l_PktCstBurdFlagTab(k),
1030 sysdate,
1031 FND_GLOBAL.USER_ID,
1032 FND_GLOBAL.USER_ID,
1033 sysdate,
1034 FND_GLOBAL.LOGIN_ID,
1035 'P',
1036 fnd_global.conc_request_id,
1037 fnd_global.conc_program_id,
1038 fnd_global.prog_appl_id,
1039 sysdate
1040 --PA.M
1041 ,l_PktDocLineIdTab(k)
1042 ,l_PktCompMultiplierTab(k)
1043 ,l_PktFcStartDateTab(k)
1044 ,l_PktFcEndDateTab(k)
1045 ,l_PktCommTotRawAmtTab(k)
1046 ,l_PktCommTotBdAmtTab(k)
1047 ,l_PktCommRawAmtRelievedTab(k)
1048 ,l_PktCommBdAmtRelievedTab(k)
1049 ,l_PktSummaryRecordFlagTab(k)
1050 ,l_PktExpItemIdTab(k)
1051 ,l_PktReference1Tab(k)
1052 ,l_PktReference2Tab(k)
1053 ,l_PktReference3Tab(k)
1054 --R12
1055 ,l_PktBcEventIDTab(k)
1056 ,l_PktBudgetLineIdTab(k)
1057 ,l_PktBurdenMethodCodeTab(k)
1058 ,l_PktVendorIdTab(k)
1059 ,l_PktDocHdrId2Tab(k)
1060 ,l_PktDocDistTypeTab(k)
1061 from dual
1062 where l_PktBcCommTab(k) is null
1063 and l_PktRlmiTab(k) is not null
1064 and l_PktBdgtVerTab(k) is not null
1065 and l_PktDocTypTab(k) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY')
1066 and not exists (select 'X'
1067 from pa_bc_commitments_all
1068 where document_type = l_PktDocTypTab(k)
1069 and l_PktDocTypTab(k) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY')
1070 and document_header_id = l_PktDocHeadTab(k)
1071 and (document_distribution_id = l_PktDocDistTab(k)
1072 or (document_distribution_id = -9999
1073 and
1074 document_line_id = l_PktDocLineIdTab(k))
1075 )
1076 and bc_packet_id = l_PktBcPktTab(k));
1077
1078 --The below loop is only for debugging. Can be removed, if log file is huge.
1079 for i in l_PktIdTab.first..l_PktIdTab.last loop
1080 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1081 pa_debug.g_err_stage := 'Log: No of commitment inserts = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1082 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1083 END IF;
1084 pa_funds_control_utils.print_message('No. of commitment insert = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1085 end loop;
1086
1087 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1088 pa_debug.g_err_stage := 'Log: After Insert into commitments';
1089 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1090
1091 pa_debug.g_err_stage := 'Log: Before Update of commitments';
1092 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1093 END IF;
1094
1095 pa_funds_control_utils.print_message('Before update of pa_bc_commitments_all');
1096
1097 --Update pa_bc_commitments_all if bc_commitment_id is not null
1098 FORALL j in l_PktIdTab.FIRST..l_PktIdTab.LAST
1099 UPDATE pa_bc_commitments_all pbc
1100 SET pbc.packet_id = l_PktIdTab(j),
1101 pbc.top_task_id = l_PktTTaskTab(j),
1102 pbc.parent_resource_id = l_PktParResTab(j),
1103 pbc.budget_version_id = l_PktBdgtVerTab(j),
1104 pbc.resource_list_member_id = l_PktRlmiTab(j),
1105 pbc.entered_dr = l_PktEntDrTab(j),
1106 pbc.entered_cr = l_PktEntCrTab(j),
1107 pbc.accounted_dr = l_PktAcctDrTab(j),
1108 pbc.accounted_cr = l_PktAcctCrTab(j),
1109 pbc.budget_ccid = l_PktBdgtCCIDTab(j),
1110 pbc.txn_ccid = l_PktTxnCCIDTab(j),
1111 --pbc.bc_packet_id = l_PktBCPktTab(j),
1112 --pbc.parent_bc_packet_id = l_PktParBCPktTab(j),
1113 pbc.set_of_books_id = l_PktSobTab(j),
1114 pbc.bud_resource_list_member_id = l_PktBdgtRlmiTab(j),
1115 pbc.bud_task_id = l_PktBdgtTaskTab(j),
1116 pbc.actual_flag = l_PktActFlagTab(j),
1117 pbc.encumbrance_type_id = l_PktEncTypIdTab(j),
1118 pbc.proj_encumbrance_type_id = l_PktPrjEncTypIdTab(j),
1119 pbc.last_updated_by = fnd_global.user_id,
1120 pbc.last_update_date = sysdate,
1121 pbc.last_update_login = fnd_global.login_id,
1122 pbc.request_id = fnd_global.conc_request_id,
1123 pbc.program_id = fnd_global.conc_program_id,
1124 pbc.program_application_id = fnd_global.prog_appl_id,
1125 pbc.program_update_date = sysdate,
1126 pbc.budget_line_id = l_PktBudgetLineIdTab(j)
1127 where ((pbc.bc_commitment_id = l_PktBcCommTab(j))
1128 or (l_PktBcCommTab(j) is null
1129 --Bug 2779986: This exist clause will be true for reversing commitment txns
1130 --that came when baseline is in progress (what we call delta txns) for the project.
1131 --We have to update only that record which satisfies the document id combination
1132 --and bc_packet_id and not all the records. Hence added the extra conditions.
1133 and pbc.document_header_id = l_PktDocHeadTab(j)
1134 and (pbc.document_distribution_id = l_PktDocDistTab(j)
1135 or (pbc.document_distribution_id = -9999
1136 and
1137 pbc.document_line_id = l_PktDocLineIdTab(j))
1138 )
1139 and pbc.bc_packet_id = l_PktBcPktTab(j)
1140 and exists (select 'X'
1141 from pa_bc_commitments_all pbc1
1142 where pbc1.project_id = l_PktProjectTab(j)
1143 and pbc1.task_id = l_PktTaskTab(j)
1144 and pbc1.document_type = l_PktDocTypTab(j)
1145 and l_PktDocTypTab(j) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY')
1146 and pbc1.document_header_id = l_PktDocHeadTab(j)
1147 and (pbc1.document_distribution_id = l_PktDocDistTab(j)
1148 or (pbc1.document_distribution_id = -9999
1149 and
1150 pbc1.document_line_id = l_PktDocLineIdTab(j))
1151 )
1152 and pbc1.bc_packet_id = l_PktBcPktTab(j)
1153 and pbc1.budget_version_id < l_PktBdgtVerTab(j))))
1154 and pbc.project_id = l_PktProjectTab(j)
1155 and l_PktRlmiTab(j) is not null
1156 and l_PktBdgtVerTab(j) is not null
1157 and l_PktDocTypTab(j) in ('AP', 'PO', 'REQ', 'CC_P_CO', 'CC_C_CO', 'CC_P_PAY', 'CC_C_PAY');
1158
1159 --The below loop is only for debugging. Can be removed, if log file is huge.
1160 for i in l_PktIdTab.first..l_PktIdTab.last loop
1161 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1162 pa_debug.g_err_stage := 'Log: No of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1163 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1164 END IF;
1165 pa_funds_control_utils.print_message('No. of commitment updates = ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1166 end loop;
1167
1168 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1169 pa_debug.g_err_stage := 'Log: After Update of commitments';
1170 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1171
1172 pa_debug.g_err_stage := 'Log: Before update status_code, balance_posted_flag in c_bc_packets loop '||l_StsUpdPktIdTab.count;
1173 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1174 END IF;
1175
1176 pa_funds_control_utils.print_message('Before update of status_code');
1177 pa_funds_control_utils.print_message('No. of status updates in c_bc_packets= '||l_StsUpdPktIdTab.count);
1178
1179 --Update status_code and balance_posted_flag
1180 IF (l_StsUpdPktIdTab.count <> 0) THEN
1181 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1182 pa_debug.write_file('LOG', 'Pkt FIRST = ' || l_StsUpdPktIdTab.FIRST || ' LAST = '|| l_StsUpdPktIdTab.LAST );
1183 pa_debug.write_file('LOG', 'Bc Pkt FIRST = ' || l_StsUpdBcPktIdTab.FIRST || ' LAST = '|| l_StsUpdBcPktIdTab.LAST );
1184 END IF;
1185 FORALL m in l_StsUpdPktIdTab.FIRST..l_StsUpdPktIdTab.LAST
1186 UPDATE pa_bc_packets pbc
1187 SET pbc.status_code = 'X',
1188 pbc.balance_posted_flag = 'Y',
1189 pbc.last_update_date = sysdate,
1190 pbc.last_update_login = fnd_global.login_id,
1191 pbc.last_updated_by = fnd_global.user_id
1192 WHERE pbc.status_code = 'A'
1193 AND pbc.packet_id = l_StsUpdPktIdTab(m)
1194 AND pbc.bc_packet_id = l_StsUpdBcPktIdTab(m)
1195 AND pbc.balance_posted_flag = 'N'
1196 AND l_PktStatusTab(m) = 'A'
1197 AND l_PktBalPostFlagTab(m) = 'N';
1198 END IF;
1199
1200 --The below loop is for debugging, can be removed if log file is huge
1201 if (l_StsUpdPktIdTab.count <> 0) then
1202 for i in l_StsUpdPktIdTab.FIRST..l_StsUpdPktIdTab.LAST loop
1203 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1204 pa_debug.g_err_stage := 'Log: No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i);
1205 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1206 END IF;
1207 pa_funds_control_utils.print_message('No of status update ' || SQL%BULK_ROWCOUNT(i)||' Pkt Id ' || l_PktBcPktTab(i));
1208 end loop;
1209 end if;
1210
1211 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1212 pa_debug.g_err_stage := 'Log: After Update of status_code and balance_posted_flag in c_bc_packets loop';
1213 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1214 END IF;
1215
1216 --Commit in a batch i.e. 200 rows
1217 commit;
1218 pa_funds_control_utils.print_message('End loop');
1219
1220 EXIT WHEN c_bc_packets%NOTFOUND;
1221 END LOOP;
1222
1223 pa_funds_control_utils.print_message('Close c_bc_packets');
1224
1225 CLOSE c_bc_packets;
1226 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1227 pa_debug.g_err_stage := 'Log: Open cursor c_ins_packets';
1228 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1229 END IF;
1230
1231 pa_funds_control_utils.print_message('Open cursor c_ins_packets');
1232
1233 l_count := 0;
1234
1235 --Open cursor c_ins_packets
1236 OPEN c_ins_packets(l_project_id);
1237 LOOP
1238
1239 --Initialize PL/SQL tables
1240 InitPlSqlTabs2;
1241
1242 l_count := l_count+1;
1243
1244 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1245 pa_debug.g_err_stage := 'Log: Fetched ' || l_count || ' batch';
1246 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1247 END IF;
1248
1249 pa_funds_control_utils.print_message('No of cursor batch fetched = ' || l_count);
1250
1251 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1252 pa_debug.g_err_stage := 'Log: Fetch cursor c_ins_packets';
1253 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1254 END IF;
1255
1256 pa_funds_control_utils.print_message('Fetch cursor c_ins_packets');
1257
1258 --Fetch records from cursor in batch
1259 FETCH c_ins_packets bulk collect into
1260 l_InsBdgtVerTab,
1261 l_InsProjectTab,
1262 l_InsTaskTab,
1263 l_InsTTaskTab,
1264 l_InsDocTypTab,
1265 l_InsRlmiTab,
1266 l_InsParResTab,
1267 l_InsSobTab,
1268 l_InsActPTDTab,
1269 l_InsEncPTDTab,
1270 l_InsBalPostFlagTab,
1271 l_InsStatusTab,
1272 l_InsTPCTab,
1273 --l_InsEiDateTab,
1274 l_InsStDateTab,
1275 l_InsEdDateTab
1276 /*Bug 3007393*/
1277 /*,l_InsOrgIdTab*/
1278 LIMIT rows;
1279
1280 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1281 pa_debug.g_err_stage := 'Log: No. of records fetched = '||l_InsProjectTab.count;
1282 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1283 END IF;
1284
1285 pa_funds_control_utils.print_message('No. of records fetched = ' || l_InsProjectTab.count);
1286
1287 --If no. of records fetched = 0 then exit
1288 IF l_InsProjectTab.count = 0 THEN
1289 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1290 pa_debug.g_err_stage := 'Log: No records from cursor c_ins_packets, exit';
1291 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1292 END IF;
1293 pa_funds_control_utils.print_message('No records from cursor c_ins_packets, exit');
1294 EXIT;
1295 END IF;
1296
1297 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1298 pa_debug.g_err_stage := 'Log: Before inserting balances';
1299 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1300 END IF;
1301
1302 pa_funds_control_utils.print_message('Before inserting into pa_bc_balances');
1303
1304 --Insert into pa_bc_balances
1305 FORALL p in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1306 insert into pa_bc_balances(project_id
1307 ,task_id
1308 ,top_task_id
1309 ,resource_list_member_id
1310 ,set_of_books_id
1311 ,budget_version_id
1312 ,balance_type
1313 ,last_update_date
1314 ,last_updated_by
1315 ,created_by
1316 ,creation_date
1317 ,last_update_login
1318 ,start_date
1319 ,end_date
1320 ,parent_member_id
1321 ,budget_period_to_date
1322 ,actual_period_to_date
1323 ,encumb_period_to_date
1324 ,request_id
1325 ,program_id
1326 ,program_application_id
1327 ,program_update_date)
1328 select
1329 l_InsProjectTab(p),
1330 l_InsTaskTab(p),
1331 l_InsTTaskTab(p),
1332 l_InsRlmiTab(p),
1333 l_InsSobTab(p),
1334 l_InsBdgtVerTab(p),
1335 l_InsDocTypTab(p),
1336 sysdate,
1337 FND_GLOBAL.USER_ID,
1338 FND_GLOBAL.USER_ID,
1339 sysdate,
1340 FND_GLOBAL.LOGIN_ID,
1341 --GetBCBalStartDate(l_InsTPCTab(p),l_InsProjectTab(p),l_InsEiDateTab(p),
1342 -- l_InsBdgtVerTab(p),l_InsSobTab(p),l_InsOrgIdTab(p)),
1343 --GetBCBalEndDate(l_InsTPCTab(p),l_InsProjectTab(p),l_InsEiDateTab(p),
1344 -- l_InsBdgtVerTab(p),l_InsSobTab(p),l_InsOrgIdTab(p)),
1345 l_InsStDateTab(p),
1346 l_InsEdDateTab(p),
1347 l_InsParResTab(p),
1348 0,
1349 l_InsActPTDTab(p),
1350 l_InsEncPTDTab(p),
1351 fnd_global.conc_request_id,
1352 fnd_global.conc_program_id,
1353 fnd_global.prog_appl_id,
1354 sysdate
1355 from dual where l_InsDocTypTab(p) in ('EXP', 'AP','PO','REQ','CC_C_CO','CC_P_CO','CC_C_PAY','CC_P_PAY');
1356
1357 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1358 pa_debug.g_err_stage := 'Log: After inserting balances';
1359 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1360
1361 pa_debug.g_err_stage := 'Log: Before update status_code and balance_posted_flag in c_ins_packets';
1362 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1363 END IF;
1364
1365 pa_funds_control_utils.print_message('Update status_code and balance_posted_flag');
1366
1367 --Update status_code and balance_posted_flag in pa_bc_packets
1368 FORALL m in l_InsProjectTab.FIRST..l_InsProjectTab.LAST
1369 UPDATE pa_bc_packets pbc
1370 SET pbc.status_code = 'X',
1371 pbc.balance_posted_flag = 'Y',
1372 pbc.last_update_date = sysdate,
1373 pbc.last_update_login = fnd_global.login_id,
1374 pbc.last_updated_by = fnd_global.user_id
1375 WHERE pbc.status_code = 'A'
1376 AND pbc.project_id = l_InsProjectTab(m)
1377 AND pbc.task_id = l_InsTaskTab(m)
1378 AND pbc.balance_posted_flag = 'N'
1379 AND l_InsStatusTab(m) = 'A'
1380 AND l_InsBalPostFlagTab(m) = 'N';
1381
1382 --Below loop for debugging, can be removed if log file is huge
1383 for i in l_InsProjectTab.first..l_InsProjectTab.last loop
1384 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1385 pa_debug.g_err_stage := 'Log: No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i);
1386 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1387 END IF;
1388 pa_funds_control_utils.print_message('No. of status update in c_ins_packets = ' || SQL%BULK_ROWCOUNT(i));
1389 end loop;
1390
1391 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1392 pa_debug.g_err_stage := 'Log: After Update of status_code and balance_posted_flag in c_ins_packets loop';
1393 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1394 END IF;
1395
1396 --Commit in a batch, i.e. 200 rows
1397 commit;
1398 EXIT WHEN c_ins_packets%NOTFOUND;
1399
1400 END LOOP;
1401
1402 --Close c_ins_packets cursor
1403 CLOSE c_ins_packets;
1404
1405 --Release lock on project
1406 IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1407 pa_funds_control_utils.print_message('Releasing lock for '|| l_project_id);
1408 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1409 pa_debug.g_err_stage := 'Log: Finished processing, release lock on Project = ' || to_char(l_project_id);
1410 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1411 END IF;
1412 null;
1413 END IF;
1414
1415 ELSE
1416 --Unable to acquire user lock
1417 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1418 pa_debug.g_err_stage := 'Log: Could not acquire lock for Project = '||to_char(l_project_id);
1419 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1420 END IF;
1421 END IF;
1422
1423 END LOOP;
1424
1425 IF l_debug_mode = 'Y' THEN /* added for bug#2672653 */
1426 pa_debug.g_err_stage := '*******************************************************';
1427 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1428
1429 pa_debug.g_err_stage := 'Log: End of Update_Act_Enc_Balance';
1430 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1431 END IF;
1432
1433 pa_fck_util.debug_msg('PB:Exiting Sweeper');
1434
1435 --Reset the error stack when returning to the calling program
1436 PA_DEBUG.Reset_Err_Stack;
1437
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440 --Since release_user_lock always issues a commit, do a
1441 --rollback before calling release_user_lock.
1442 rollback;
1443
1444 x_return_status := fnd_api.g_ret_sts_unexp_error;
1445
1446 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_SWEEPER'
1447 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1448
1449 x_error_message_code := (SQLCODE||' '||SQLERRM);
1450
1451 IF (pa_debug.release_user_lock('SWEEPLOCK:'||to_char(l_project_id)) = 0) THEN
1452 pa_funds_control_utils.print_message('In others Releasing lock for proj '|| l_project_id);
1453 null;
1454 END IF;
1455
1456 IF c_bc_packets%isopen THEN
1457 close c_bc_packets;
1458 END IF;
1459 IF c_ins_packets%isopen THEN
1460 close c_ins_packets;
1461 END IF;
1462 IF c_delete_pkts%isopen THEN
1463 close c_delete_pkts;
1464 END IF;
1465
1466 raise;
1467 END update_act_enc_balance;
1468
1469 END PA_SWEEPER;