[Home] [Help]
PACKAGE BODY: APPS.PA_XLA_SWEEP_TXN_PKG
Source
1 PACKAGE BODY PA_XLA_SWEEP_TXN_PKG AS
2 -- $Header: PACCGLEB.pls 120.6 2005/10/26 03:04:36 rshaik noship $
3 G_Debug_Mode Varchar2(1);
4
5 --Forward declaration
6 PROCEDURE UPD_BTC_TBC_RELATED_CMT_GLDATE;
7
8 Procedure InitPLSQLTab Is
9 Begin
10 g_expenditure_item_id.delete;
11 g_adjusted_expenditure_item_id.delete;
12 g_system_linkage_function.delete;
13 g_PERIOD_ACCRUAL_FLAG.delete;
14 g_cdl_rowid.delete;
15 g_gl_date_new_tab.delete;
16 g_gl_period_new_tab.delete;
17 g_line_num.delete;
18 -- R12 Funds Management Uptake
19 g_cdl_line_type.delete;
20 g_liquidate_encum_flag.delete;
21 g_buren_Sum_Dest_Run_Id.delete;
22 g_document_header_Id.delete;
23 g_document_distribution_Id.delete;
24 g_expenditure_type.delete;
25 g_cdl_acct_event_id.delete;
26 End;
27
28
29
30 PROCEDURE Log_Message(p_message in VARCHAR2,
31 p_mode in NUMBER DEFAULT 0) IS
32 BEGIN
33
34 If (G_Debug_Mode = 'Y') Then
35 pa_cc_utils.log_message(p_message,1);
36 End If;
37
38 END Log_Message;
39
40
41 Procedure SWEEP_TXNS (P_ORG_ID PA_IMPLEMENTATIONS_ALL.ORG_ID%TYPE,
42 P_GL_PERIOD GL_PERIOD_STATUSES.PERIOD_NAME%TYPE,
43 P_TRAN_TYPE VARCHAR2)
44 Is
45
46 Cursor ACC_EVENT_ERROR(p_start_date date, p_end_date date)
47 Is Select EV.Event_Id From xla_entity_events_v EV
48 where EV.EVENT_DATE between p_start_date and p_end_Date
49 and EV.process_status_code <> 'P'
50 and EV.security_id_int_1 = g_org_id
51 and EV.Application_Id = 275
52 and EV.EVENT_TYPE_CODE IN ( SELECT EVENT_TYPE_CODE
53 from XLA_EVENT_TYPES_VL
54 Where
55 ( ( ENTITY_CODE = 'EXPENDITURES'
56 AND P_TRAN_TYPE = 'EXPENDITURES'
57 AND EVENT_CLASS_CODE NOT IN ('BORROWED_AND_LENT',
58 'PRVDR_RECVR_RECLASS')
59 )
60 OR
61 (
62 ENTITY_CODE = 'EXPENDITURES'
63 AND P_TRAN_TYPE = 'CROSSCHARGE'
64 AND EVENT_CLASS_CODE IN ('BORROWED_AND_LENT',
65 'PRVDR_RECVR_RECLASS')
66 )
67 OR
68 (
69 ENTITY_CODE = 'REVENUE'
70 AND P_TRAN_TYPE = 'REVENUE'
71 )
72 OR
73 (
74 P_TRAN_TYPE Is Null
75 AND ENTITY_CODE IN ('EXPENDITURES', 'REVENUE')
76 )
77 )
78 AND Application_ID = 275
79
80 );
81
82 l_prof_new_gldate_derivation Varchar2(1) := 'N';
83
84
85 p_first_date Date;
86 p_last_date Date;
87
88 l_err_msg fnd_new_messages.MESSAGE_TEXT%TYPE;
89 Begin
90 pa_debug.set_curr_function('SWEEP_TXNS');
91
92 g_app_id := 101;
93 g_org_id := P_ORG_ID;
94 g_request_id := fnd_global.conc_request_id;
95
96 g_tran_type := P_TRAN_TYPE;
97
98 If G_debug_mode is NULL Then
99 fnd_profile.get('PA_DEBUG_MODE',G_debug_mode);
100 G_debug_mode := NVL(G_debug_mode, 'N');
101 pa_debug.set_process(x_process => 'PLSQL' ,
102 x_debug_mode => G_debug_mode);
103 End If;
104
105 Select set_of_books_id
106 into g_sob_id
107 from pa_implementations_all where org_id = g_org_id;
108
109 Select START_DATE, End_Date into p_first_date , p_last_date
110 From gl_period_statuses
111 where period_name = P_GL_PERIOD
112 and set_of_books_id = g_sob_id
113 and application_id = g_app_id ;
114
115 g_new_period_date := pa_utils2.get_prvdr_gl_date(p_first_date
116 ,g_app_id
117 ,g_sob_id);
118
119 If g_new_period_date Is null Then
120 FND_MESSAGE.SET_NAME('PA','PA_SWEEP_NO_GL_PERIOD');
121 l_err_msg := FND_MESSAGE.GET;
122 app_exception.RAISE_EXCEPTION( exception_text =>l_err_msg);
123 End If;
124
125 g_new_period_name := pa_utils2.get_gl_period_name ( g_new_period_date , g_org_id);
126
127 Log_Message ( 'New Period ' || g_new_period_name || ', New Date ' || g_new_period_date );
128
129 Open ACC_EVENT_ERROR (p_first_date, p_last_date);
130 Loop
131 g_event_tab.delete;
132
133 Log_Message ( 'Before fetching from ACC_EVENT_ERROR bulk size of ' || g_bulk_size);
134
135 Fetch ACC_EVENT_ERROR bulk collect into g_event_tab limit g_bulk_size;
136
137 Log_Message ( 'After fetching from ACC_EVENT_ERROR, total cnt ' || g_event_tab.count );
138
139 If g_event_tab.count = 0 Then
140 Exit;
141 End If;
142
143 If NVL(P_TRAN_TYPE,'EXPENDITURES') = 'EXPENDITURES' Then
144 FORALL i IN 1..g_event_tab.count
145 Update PA_COST_DISTRIBUTION_LINES_ALL
146 SET TRANSFER_STATUS_CODE = 'X' ,
147 Request_Id = g_request_id
148 Where Acct_Event_ID = g_event_tab(i);
149
150 Log_Message ( SQL%ROWCOUNT || ' Updated CDLs with X');
151 End If;
152
153 If NVL(P_TRAN_TYPE,'CROSSCHARGE') = 'CROSSCHARGE' Then
154 FORALL i IN 1..g_event_tab.count
155 Update pa_cc_dist_lines
156 SET TRANSFER_STATUS_CODE = 'X' ,
157 Request_Id = g_request_id
158 Where Acct_Event_ID = g_event_tab(i);
159 Log_Message ( SQL%ROWCOUNT || ' Updated CCDLs with X');
160 End If;
161
162 If NVL(P_TRAN_TYPE,'REVENUE') = 'REVENUE' Then
163 FORALL i IN 1..g_event_tab.count
164 Update PA_DRAFT_REVENUES_ALL
165 SET TRANSFER_STATUS_CODE = 'X' ,
166 Request_Id = g_request_id
167 Where Event_ID = g_event_tab(i);
168 Log_Message ( SQL%ROWCOUNT || ' Updated RDLs with X');
169 End If;
170
171 Log_Message ( 'Calling Populate_GL_Dates for GL-DAte Rederivation');
172
173 POPULATE_GL_DATE;
174
175 Log_Message ( 'Returning Populate_GL_Dates');
176
177
178 FORALL i in 1..g_event_tab.count
179 Update XLA_EVENTS
180 Set EVENT_DATE = g_new_period_date ,
181 TRANSACTION_DATE = g_new_period_date ,
182 LAST_UPDATE_DATE = Sysdate ,
183 LAST_UPDATED_BY = fnd_global.user_id ,
184 LAST_UPDATE_LOGIN = fnd_global.user_id ,
185 request_id = g_request_id
186 Where event_id = g_event_tab(i);
187
188 Log_Message ( SQL%ROWCOUNT || ' Event(s) updated.');
189
190 FORALL i in 1..g_event_tab.count
191 Update XLA_AE_HEADERS
192 SET ACCOUNTING_DATE = g_new_period_date ,
193 PERIOD_NAME = g_new_period_name ,
194 LAST_UPDATE_DATE = Sysdate ,
195 LAST_UPDATED_BY = fnd_global.user_id ,
196 LAST_UPDATE_LOGIN = fnd_global.user_id ,
197 request_id = g_request_id
198 Where event_id = g_event_tab(i);
199
200 Log_Message ( SQL%ROWCOUNT || ' Header(s) updated.');
201
202 Commit;
203
204 Log_Message ( 'Commit Sucessful..');
205
206 If g_event_tab.count < g_bulk_size Then
207 Exit;
208 End If;
209
210 End Loop;
211
212 Close ACC_EVENT_ERROR;
213
214 pa_debug.reset_curr_function;
215
216 Log_Message ( 'Exiting...');
217 Exception
218 When Others Then
219 Raise;
220 End SWEEP_TXNS;
221
222
223
224 Procedure Populate_Gl_Date
225 Is
226 Cursor c_sel_cdl Is
227 SELECT
228 ei.expenditure_item_id,
229 cdl.billable_flag,
230 cdl.line_type,
231 cdl.line_num,
232 ei.transaction_source,
233 tr.gl_accounted_flag,
234 ei.denom_currency_code,
235 ei.acct_currency_code,
236 ei.acct_rate_date,
237 ei.acct_rate_type,
238 ei.acct_exchange_rate,
239 ei.project_currency_code,
240 ei.project_rate_date,
241 ei.project_rate_type,
242 ei.project_exchange_rate,
243 tr.system_linkage_function,
244 ei.projfunc_currency_code,
245 ei.projfunc_cost_rate_date,
246 ei.projfunc_cost_rate_type,
247 ei.projfunc_cost_exchange_rate,
248 ei.work_type_id
249 FROM pa_expenditure_items_all ei,
250 pa_cost_distribution_lines_all cdl,
251 pa_transaction_sources tr
252 WHERE tr.transaction_source(+) = ei.transaction_source
253 AND ei.expenditure_item_id = cdl.expenditure_item_id
254 AND CDL.Transfer_Status_Code = 'Y'
255 AND CDL.request_id = g_request_id;
256
257
258 Cursor CDL_CUR Is
259 Select
260 ei.expenditure_item_id ,
261 ei.adjusted_expenditure_item_id ,
262 ei.system_linkage_function ,
263 exp_grp.PERIOD_ACCRUAL_FLAG ,
264 rowidtochar(CDL.rowid) ROW_ID ,
265 cdl.line_num ,
266 g_new_period_date GL_DATE,
267 g_new_period_name GL_PERIOD_NAME,
268 cdl.recvr_gl_date ,
269 IMP.set_of_books_id recvr_sob_id,
270 nvl(EI.recvr_org_id,CDL.org_id) recvr_org_id,
271 -- R12 Funds Management uptake
272 cdl.line_type ,
273 cdl.liquidate_encum_flag ,
274 ei.Burden_Sum_Dest_Run_Id ,
275 ei.document_header_id ,
276 ei.document_distribution_id ,
277 ei.expenditure_type ,
278 cdl.Acct_Event_ID
279 From PA_Cost_Distribution_lines_ALL CDL,
280 PA_Expenditure_items_all EI,
281 PA_IMPLEMENTATIONS_ALL IMP ,
282 PA_EXPENDITURES_ALL EXP ,
283 PA_EXPENDITURE_GROUPS_ALL EXP_GRP
284 Where CDL.Transfer_Status_Code = 'X'
285 AND CDL.expenditure_item_id = EI.expenditure_item_id
286 AND nvl(EI.recvr_org_id,CDL.org_id) = IMP.ORG_ID
287 AND EXP.EXPENDITURE_ID = EI.EXPENDITURE_ID
288 AND EXP_GRP.EXPENDITURE_GROUP = EXP.EXPENDITURE_GROUP
289 AND EXP_GRP.ORG_ID = EXP.ORG_ID
290 AND CDL.REQUEST_ID = g_request_id
291 AND CDL.ORG_ID = g_org_id
292 AND EI.ORG_ID = g_org_id ;
293
294
295
296
297
298 l_err_code NUMBER ;
299 l_err_stage VARCHAR2(2000);
300 l_err_stack VARCHAR2(255) ;
301
302 Begin
303
304 pa_debug.set_curr_function('Populate_Gl_Date');
305
306 If NVL(g_tran_type,'EXPENDITURES') = 'EXPENDITURES' Then
307
308 Open CDL_CUR;
309 Loop
310 InitPLSQLTab;
311
312 log_message ('Before Fetching from CDL_CUR, Bulk Size ' || g_cdl_bulk_size);
313
314 Fetch CDL_CUR Bulk Collect INTO
315 g_expenditure_item_id ,
316 g_adjusted_expenditure_item_id ,
317 g_system_linkage_function ,
318 g_PERIOD_ACCRUAL_FLAG ,
319 g_cdl_rowid ,
320 g_line_num ,
321 g_gl_date_new_tab ,
322 g_gl_period_new_tab ,
323 g_recvr_gl_date_new_tab ,
324 g_recvr_sob_id ,
325 g_recvr_org_id ,
326 -- R12 Funds Management uptake
327 g_cdl_line_type ,
328 g_liquidate_encum_flag ,
329 g_buren_Sum_Dest_Run_Id ,
330 g_document_header_Id ,
331 g_document_distribution_id ,
332 g_expenditure_type ,
333 g_cdl_acct_event_id
334 limit g_cdl_bulk_size;
335
336 log_message ('After Fetching from CDL_CUR, CDL(s) Fetched = ' || g_expenditure_item_id.count );
337
338 If g_expenditure_item_id.count = 0 then
339 Exit;
340 End If;
341
342 FOR i in 1..g_expenditure_item_id.count
343 Loop
344 If g_recvr_sob_id(i) <> g_sob_id Then
345
346 log_message ('Reciever SOB , Provider SOB different. Deriving GL-Date for Reciever SOB ');
347
348 g_recvr_gl_date_new_tab(i) := pa_utils2.get_recvr_gl_date
349 (
350 g_recvr_gl_date_new_tab(i),
351 g_app_id,
352 g_recvr_sob_id(i)
353 ) ;
354
355 g_recvr_gl_period_new_tab(i) := pa_utils2.get_gl_period_name(
356 g_recvr_gl_date_new_tab(i),
357 g_recvr_org_id(i)
358 ) ;
359
360 log_message ('Reciever GL_Date Derived = ' || g_recvr_gl_date_new_tab(i) || ' : ' || g_recvr_gl_period_new_tab(i));
361 Else
362 g_recvr_gl_date_new_tab(i) := g_new_period_date;
363 g_recvr_gl_period_new_tab(i) := g_new_period_name;
364 End If;
365
366 End Loop;
367
368
369 log_message ('Updating CDLs with TSC = A if Not Summarised, Y if Summarised');
370
371 FORALL i IN 1..g_expenditure_item_id.count
372 UPDATE PA_Cost_Distribution_lines_ALL CDL
373 SET CDL.request_id = g_request_id,
374 CDL.transfer_status_code = DECODE(g_gl_date_new_tab(i),
375 NULL,'R', DECODE(g_recvr_gl_date_new_tab(i)
376 ,NULL,'R',
377 DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
378 'N', 'A',
379 DECODE (CDL.gl_date,g_gl_date_new_tab(i), 'A','Y')
380 )
381 )
382 )
383 ,CDL.gl_date = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
384 'N', nvl(g_gl_date_new_tab(i),CDL.gl_date) ,
385 CDL.GL_DATE)
386 ,CDL.gl_period_name = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
387 'N', nvl(g_gl_period_new_tab(i),CDL.gl_period_name),
388 CDL.gl_period_name)
389 ,CDL.recvr_gl_date = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
390 'N', nvl(g_recvr_gl_date_new_tab(i),CDL.recvr_gl_date),
391 CDL.recvr_gl_date)
392 ,CDL.recvr_gl_period_name = DECODE ( DECODE ( CDL.LINE_TYPE ,'R', CDL.PJI_SUMMARIZED_FLAG, 'N'),
393 'N', nvl(g_recvr_gl_period_new_tab(i),CDL.recvr_gl_period_name),
394 CDL.recvr_gl_period_name)
395 WHERE CDL.Expenditure_Item_Id = g_expenditure_item_id(i)
396 AND CDL.Line_nUm = g_line_num(i)
397 AND CDL.Transfer_Status_Code = 'X';
398
399 log_message ( 'Total CDLs updated ' || SQL%ROWCOUNT);
400
401 log_message ( 'Call for Pa_Costing.ReverseCdl for CDLs with TSC = Y ');
402
403 For cdlsel in c_sel_cdl
404 Loop
405
406 log_message ( '....CDL with TSC = Y , Exp Item ID = ' || cdlsel.expenditure_item_id);
407
408 Pa_Costing.ReverseCdl
409 ( X_expenditure_item_id => cdlsel.expenditure_item_id
410 , X_billable_flag => cdlsel.billable_flag
411 , X_amount => NULL
412 , X_quantity => NULL
413 , X_burdened_cost => NULL
414 , X_dr_ccid => NULL
415 , X_cr_ccid => NULL
416 , X_tr_source_accounted => 'Y'
417 , X_line_type => cdlsel.line_type
418 , X_user => fnd_global.user_id
419 , X_denom_currency_code => cdlsel.denom_currency_code
420 , X_denom_raw_cost => NULL
421 , X_denom_burden_cost => NULL
422 , X_acct_currency_code => cdlsel.acct_currency_code
423 , X_acct_rate_date => cdlsel.acct_rate_date
424 , X_acct_rate_type => cdlsel.acct_rate_type
425 , X_acct_exchange_rate => cdlsel.acct_exchange_rate
426 , X_acct_raw_cost => NULL
427 , X_acct_burdened_cost => NULL
428 , X_project_currency_code => cdlsel.project_currency_code
429 , X_project_rate_date => cdlsel.project_rate_date
430 , X_project_rate_type => cdlsel.project_rate_type
431 , X_project_exchange_rate => cdlsel.project_exchange_rate
432 , X_err_code => l_err_code
433 , X_err_stage => l_err_stage
434 , X_err_stack => l_err_stack
435 , P_Projfunc_currency_code => cdlsel.projfunc_currency_code
436 , P_Projfunc_cost_rate_date => cdlsel.projfunc_cost_rate_date
437 , P_Projfunc_cost_rate_type => cdlsel.projfunc_cost_rate_type
438 , P_Projfunc_cost_exchange_rate => cdlsel.projfunc_cost_exchange_rate
439 , P_project_raw_cost => null
440 , P_project_burdened_cost => null
441 , P_Work_Type_Id => cdlsel.work_type_id
442 , P_mode => 'INTERFACE'
443 , X_line_num => cdlsel.line_num
444 );
445
446 End Loop;
447
448
449 FORALL i IN 1..g_expenditure_item_id.count
450 UPDATE PA_Cost_Distribution_lines_ALL CDL
451 SET CDL.request_id = g_request_id
452 ,CDL.transfer_status_code = 'A'
453 ,CDL.gl_date = nvl(g_gl_date_new_tab(i),CDL.gl_date)
454 ,CDL.gl_period_name = nvl(g_gl_period_new_tab(i),CDL.gl_period_name)
455 ,CDL.recvr_gl_date = nvl(g_recvr_gl_date_new_tab(i),CDL.recvr_gl_date)
456 ,CDL.recvr_gl_period_name = nvl(g_recvr_gl_period_new_tab(i),CDL.recvr_gl_period_name)
457 WHERE CDL.Transfer_Status_Code = 'Y'
458 AND CDL.reversed_flag is NULL
459 AND CDL.Expenditure_Item_Id = g_expenditure_item_id(i)
460 AND CDL.Line_nUm = g_line_num(i);
461
462 log_message ( SQL%ROWCOUNT || ' rows updated from TSC = Y to A for PJI Summarized Lines');
463
464 log_message ( 'Calling UPD_BTC_TBC_RELATED_CMT_GLDATE to stamp GL date on related commitments for BTC and TBC CDLs ');
465 UPD_BTC_TBC_RELATED_CMT_GLDATE;
466
467 FOR i IN 1..g_expenditure_item_id.count
468 LOOP
469 g_currec := i;
470 If g_system_linkage_function(i) = 'PJ' and g_PERIOD_ACCRUAL_FLAG(i) = 'Y' Then
471 log_message ( 'The EI is from Exp Group with Period Accrual Flag Y ');
472 CHECK_MISC_TXNS;
473 End If;
474 END LOOP;
475
476
477 If g_expenditure_item_id.count < g_cdl_bulk_size Then
478 Exit;
479 End If;
480
481 End Loop;
482
483 Close CDL_CUR;
484
485 END IF;
486
487 IF NVL(g_tran_type, 'REVENUE') = 'REVENUE' Then
488 Update PA_Draft_Revenues_All
489 set gl_date = g_new_period_date ,
490 gl_period_name = g_new_period_name ,
491 transfer_status_code = 'A'
492 Where request_id = g_request_id
493 and transfer_status_code = 'X';
494
495 log_message ( SQL%ROWCOUNT || ' RDLs updated for GL-Date Rederivation.');
496 End If;
497
498 If NVL(g_tran_type,'CROSSCHARGE') = 'CROSSCHARGE' Then
499 Update pa_cc_dist_lines
500 set gl_date = g_new_period_date ,
501 gl_period_name = g_new_period_name ,
502 transfer_status_code = 'A'
503 Where request_id = g_request_id
504 and transfer_status_code = 'X';
505
506 log_message ( SQL%ROWCOUNT || ' CCDLs updated for GL-Date Rederivation.');
507 End If;
508
509 pa_debug.reset_curr_function;
510 Exception
511 When Others Then
512 Raise;
513 End Populate_Gl_Date;
514
515 Procedure CHECK_MISC_TXNS
516 Is
517 v_gl_per_end_dt DATE;
518 l_adj_exp_item_id pa_expenditure_items_all.adjusted_expenditure_item_id%type;
519 l_exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
520 l_gl_date DATE;
521 l_pji_summarized_flag VARCHAR2(1);
522 l_prvdr_accr_date DATE;
523 l_billable_flag pa_cost_distribution_lines_all.billable_flag%type;
524 l_line_type VARCHAR2(1);
525 l_line_num NUMBER ;
526 l_denom_currency_code pa_expenditure_items_all.denom_currency_code%type;
527 l_acct_currency_code pa_expenditure_items_all.acct_currency_code%type;
528 l_acct_rate_date pa_expenditure_items_all.acct_rate_date%type;
529 l_acct_rate_type pa_expenditure_items_all.acct_rate_type%type;
530 l_acct_exchange_rate pa_expenditure_items_all.acct_exchange_rate%type;
531 l_project_currency_code pa_expenditure_items_all.project_currency_code%type;
532 l_project_rate_date pa_expenditure_items_all.project_rate_date%type;
533 l_project_rate_type pa_expenditure_items_all.project_rate_type%type;
534 l_project_exchange_rate pa_expenditure_items_all.project_exchange_rate%type;
535 l_projfunc_currency_code pa_expenditure_items_all.projfunc_currency_code%type;
536 l_projfunc_cost_rate_date pa_expenditure_items_all.projfunc_cost_rate_date%type;
537 l_projfunc_cost_rate_type pa_expenditure_items_all.projfunc_cost_rate_type%type;
538 l_projfunc_cost_exchange_rate pa_expenditure_items_all.projfunc_cost_exchange_rate%type;
539 l_work_type_id pa_expenditure_items_all.work_type_id%type;
540
541
542 l_acct_event_id pa_cost_distribution_lines_all.acct_event_id%type;
543 l_transfer_status_Code pa_cost_distribution_lines_all.transfer_status_code%type;
544 l_accr_period_name pa_cost_distribution_lines_all.gl_period_name%type;
545
546 l_err_code NUMBER ;
547 l_err_stage VARCHAR2(2000);
548 l_err_stack VARCHAR2(255) ;
549 l_org_id Number;
550
551 Begin
552
553 pa_debug.set_curr_function('CHECK_MISC_TXNS');
554
555
556 SELECT
557 ei.expenditure_item_id,
558 ei.adjusted_expenditure_item_id,
559 cdl.gl_date,
560 cdl.pji_summarized_flag,
561 cdl.billable_flag,
562 cdl.line_type,
563 cdl.line_num,
564 ei.denom_currency_code,
565 ei.acct_currency_code,
566 ei.acct_rate_date,
567 ei.acct_rate_type,
568 ei.acct_exchange_rate,
569 ei.project_currency_code,
570 ei.project_rate_date,
571 ei.project_rate_type,
572 ei.project_exchange_rate,
573 ei.projfunc_currency_code,
574 ei.projfunc_cost_rate_date,
575 ei.projfunc_cost_rate_type,
576 ei.projfunc_cost_exchange_rate,
577 ei.work_type_id ,
578 cdl.acct_event_id ,
579 cdl.transfer_status_Code ,
580 cdl.org_id
581 INTO l_exp_item_id,
582 l_adj_exp_item_id,
583 l_gl_date,
584 l_pji_summarized_flag,
585 l_billable_flag,
586 l_line_type,
587 l_line_num,
588 l_denom_currency_code,
589 l_acct_currency_code,
590 l_acct_rate_date,
591 l_acct_rate_type,
592 l_acct_exchange_rate,
593 l_project_currency_code,
594 l_project_rate_date,
595 l_project_rate_type,
596 l_project_exchange_rate,
597 l_projfunc_currency_code,
598 l_projfunc_cost_rate_date,
599 l_projfunc_cost_rate_type,
600 l_projfunc_cost_exchange_rate,
601 l_work_type_id,
602 l_acct_event_id,
603 l_transfer_status_code,
604 l_org_id
605 FROM PA_COST_DISTRIBUTION_LINES_ALL CDL,
606 PA_EXPENDITURE_ITEMS_ALL EI
607 WHERE CDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
608 AND EI.ADJUSTED_EXPENDITURE_ITEM_ID = g_expenditure_item_id(g_currec);
609
610 log_message ('Deriving Next Period for Reversing EI');
611
612 SELECT GPS.start_date
613 INTO l_prvdr_accr_date
614 FROM gl_period_statuses GPS
615 WHERE GPS.application_id = 101
616 AND GPS.set_of_books_id = g_sob_id
617 AND GPS.adjustment_period_flag = 'N'
618 AND GPS.start_date = (SELECT min(GPS1.start_date)
619 FROM gl_period_statuses GPS1
620 WHERE GPS1.application_id = 101
621 AND GPS1.set_of_books_id = g_sob_id
622 AND GPS1.adjustment_period_flag = 'N'
623 AND GPS1.start_date > g_gl_date_new_tab(g_currec)
624 );
625
626 l_prvdr_accr_date := pa_utils2.get_prvdr_gl_date(l_prvdr_accr_date
627 ,g_app_id
628 ,g_sob_id);
629
630
631 log_message ('Reversing EI ' || l_exp_item_id || ' of ' || g_expenditure_item_id(g_currec) ||
632 ' re-derived with ' || l_prvdr_accr_date );
633
634 IF (l_pji_summarized_flag = 'N') THEN
635
636 UPDATE PA_Cost_Distribution_lines_ALL CDL
637 SET CDL.gl_date = l_prvdr_accr_date
638 WHERE CDL.EXPENDITURE_ITEM_ID = l_exp_item_id;
639
640
641 log_message ('Reversing EI is not PJI Summarised. Updated with new GL-Date');
642
643 ELSE
644
645 log_message ('Reversing EI is PJI Summarised. Creating I Lines....');
646
647 Pa_Costing.ReverseCdl
648 ( X_expenditure_item_id => l_exp_item_id
649 , X_billable_flag => l_billable_flag
650 , X_amount => NULL
651 , X_quantity => NULL
652 , X_burdened_cost => NULL
653 , X_dr_ccid => NULL
654 , X_cr_ccid => NULL
655 , X_tr_source_accounted => 'Y'
656 , X_line_type => l_line_type
657 , X_user => fnd_global.user_id
658 , X_denom_currency_code => l_denom_currency_code
659 , X_denom_raw_cost => NULL
660 , X_denom_burden_cost => NULL
661 , X_acct_currency_code => l_acct_currency_code
662 , X_acct_rate_date => l_acct_rate_date
663 , X_acct_rate_type => l_acct_rate_type
664 , X_acct_exchange_rate => l_acct_exchange_rate
665 , X_acct_raw_cost => NULL
666 , X_acct_burdened_cost => NULL
667 , X_project_currency_code => l_project_currency_code
668 , X_project_rate_date => l_project_rate_date
669 , X_project_rate_type => l_project_rate_type
670 , X_project_exchange_rate => l_project_exchange_rate
671 , X_err_code => l_err_code
672 , X_err_stage => l_err_stage
673 , X_err_stack => l_err_stack
674 , P_Projfunc_currency_code => l_projfunc_currency_code
675 , P_Projfunc_cost_rate_date => l_projfunc_cost_rate_date
676 , P_Projfunc_cost_rate_type => l_projfunc_cost_rate_type
677 , P_Projfunc_cost_exchange_rate => l_projfunc_cost_exchange_rate
678 , P_project_raw_cost => null
679 , P_project_burdened_cost => null
680 , P_Work_Type_Id => l_work_type_id
681 , P_mode => 'INTERFACE'
682 , X_line_num => l_line_num
683 );
684
685 log_message ('I lines created for Reversing EI.');
686
687 l_accr_period_name := pa_utils2.get_gl_period_name (l_prvdr_accr_date,l_org_id);
688
689
690
691 UPDATE PA_Cost_Distribution_lines_ALL CDL
692 SET CDL.GL_DATE = l_prvdr_accr_date,
693 CDL.GL_PERIOD_NAME = l_accr_period_name
694 WHERE CDL.EXPENDITURE_ITEM_ID = l_exp_item_id
695 AND CDL.LINE_NUM_REVERSED IS NULL
696 AND CDL.TRANSFER_STATUS_CODE in ('P','R','G','A');
697
698 log_message ('CDLs of Reversing EI updated.');
699
700
701 If l_transfer_status_code = 'A' and l_acct_event_id is not null then
702
703
704 log_message ('Events are created for Reversing EI.');
705
706 Update XLA_EVENTS
707 Set EVENT_DATE = l_prvdr_accr_date ,
708 TRANSACTION_DATE = l_prvdr_accr_date,
709 Request_ID = g_request_id ,
710 LAST_UPDATE_DATE = Sysdate ,
711 LAST_UPDATED_BY = fnd_global.user_id ,
712 LAST_UPDATE_LOGIN = fnd_global.user_id
713 Where event_id = l_acct_event_id;
714
715 log_message ('Event updated for reversing EI.');
716
717 Update XLA_AE_HEADERS
718 SET ACCOUNTING_DATE = l_prvdr_accr_date ,
719 PERIOD_NAME = l_accr_period_name ,
720 Request_ID = g_request_id,
721 LAST_UPDATE_DATE = Sysdate ,
722 LAST_UPDATED_BY = fnd_global.user_id ,
723 LAST_UPDATE_LOGIN = fnd_global.user_id
724 Where event_id = l_acct_event_id;
725
726 log_message ('Header updated for reversing EI.');
727
728 End If;
729
730 End If;
731
732 pa_debug.reset_curr_function;
733
734 Exception
735 When Others Then
736 Raise;
737 End CHECK_MISC_TXNS;
738
739 PROCEDURE UPD_BTC_TBC_RELATED_CMT_GLDATE IS
740 BEGIN
741
742 log_message (' Start of upd_btc_tbc_related_cmt_gldate');
743
744 log_message (' Updating AP commitment records with newly derived open gl_date for corresponding BTC/TBC CDL');
745
746 FORALL i IN 1..g_expenditure_item_id.count
747 UPDATE pa_bc_commitments bc_cm
748 SET bc_cm.request_id = g_request_id,
749 bc_cm.transferred_date = SYSDATE,
750 bc_cm.liquidate_gl_date = g_gl_date_new_tab(i)
751 WHERE ( bc_cm.document_header_id,bc_cm.document_distribution_id,expenditure_type)
752 IN ( SELECT exp.document_header_id,exp.document_distribution_id,exp.expenditure_type
753 FROM PA_Cost_Distribution_lines cdl_raw,
754 pa_expenditure_items_all exp
755 WHERE cdl_raw.burden_sum_source_run_id = g_buren_Sum_Dest_Run_Id(i)
756 AND exp.expenditure_item_id = cdl_raw.expenditure_item_id
757 AND cdl_raw.line_num = 1
758 AND g_cdl_line_type(i) ='R'
759 UNION ALL
760 SELECT g_document_header_id(i),g_document_distribution_id(i),g_expenditure_type(i)
761 FROM dual
762 WHERE g_cdl_line_type(i) ='D' )
763 AND bc_cm.transfer_status_code = 'A'
764 AND bc_cm.bc_event_id = g_cdl_acct_event_id(i)
765 AND bc_cm.document_type = 'AP'
766 AND bc_cm.burden_cost_flag = 'R'
767 AND ((bc_cm.parent_bc_packet_id IS NOT NULL AND g_cdl_line_type(i) ='R') OR g_cdl_line_type(i) ='D')
768 AND NVL(g_liquidate_encum_flag(i),'N') = 'Y'
769 AND ((g_system_linkage_function(i) = 'BTC' AND g_cdl_line_type(i) = 'R' ) OR
770 (g_system_linkage_function(i) IN ('VI','ST','OT') AND g_cdl_line_type(i) = 'D'));
771
772 log_message ( 'Total AP commitment records updated ' || SQL%ROWCOUNT);
773
774 log_message (' Updating PO commitment records with newly derived open gl_date for corresponding BTC/TBC CDL');
775
776 FORALL i IN 1..g_expenditure_item_id.count
777 UPDATE pa_bc_commitments bc_cm
778 SET bc_cm.request_id = g_request_id,
779 bc_cm.transferred_date = SYSDATE,
780 bc_cm.liquidate_gl_date = g_gl_date_new_tab(i)
781 WHERE (bc_cm.exp_item_id,bc_cm.expenditure_type)
782 IN ( SELECT cdl_raw.expenditure_item_id,ei_raw.expenditure_type
783 FROM PA_Cost_Distribution_lines cdl_raw,
784 Pa_Expenditure_Items ei_raw
785 WHERE cdl_raw.burden_sum_source_run_id = g_buren_Sum_Dest_Run_Id(i)
786 AND cdl_raw.line_num = 1
787 AND cdl_raw.expenditure_item_id = ei_raw.expenditure_item_id
788 AND ei_raw.system_linkage_function in ('ST','OT','VI')
789 AND g_cdl_line_type(i) ='R'
790 UNION ALL
791 SELECT g_expenditure_item_id(i),g_expenditure_type(i)
792 FROM dual
793 WHERE g_system_linkage_function(i) IN ('ST','OT','VI')
794 AND g_cdl_line_type(i) ='D')
795 AND bc_cm.transfer_status_code in ('P','R','X')
796 AND bc_cm.document_type = 'PO'
797 AND bc_cm.burden_cost_flag = 'R'
798 AND ((bc_cm.parent_bc_packet_id is not null AND g_cdl_line_type(i) ='R') OR g_cdl_line_type(i) ='D')
799 AND NVL(g_liquidate_encum_flag(i),'N') = 'Y'
800 AND ((g_system_linkage_function(i) = 'BTC' AND g_cdl_line_type(i) = 'R' ) OR
801 (g_system_linkage_function(i) IN ('VI','ST','OT') AND g_cdl_line_type(i) = 'D'));
802
803 log_message ( 'Total AP commitment records updated ' || SQL%ROWCOUNT);
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 log_message ( 'In when others exception region' || SQL%ROWCOUNT);
808 RAISE;
809 END UPD_BTC_TBC_RELATED_CMT_GLDATE;
810
811 END PA_XLA_SWEEP_TXN_PKG;
812