[Home] [Help]
PACKAGE BODY: APPS.PAY_SLA_UPDATE_PKG
Source
1 PACKAGE BODY PAY_SLA_UPDATE_PKG AS
2 /* $Header: payxlaupg.pkb 120.4 2011/09/26 09:45:27 kskoduri ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'PAY_SLA_UPDATE_PKG';
4 gv_process_name CONSTANT varchar2(10) := 'PAYSLAUPG';
5
6 g_xla_tran_s xla_transaction_entities.entity_id%type;
7 g_xla_event_s xla_events.event_id%type;
8 g_xla_headers_s xla_ae_headers.ae_header_id%type;
9
10 -------------------------------------------------------------------------------------
11 -- Name : Trans_Pay_Patch_Status
12 -- Function : To transfer qualified periods to PAY_PATCH_STATUS.
13 -- Period should be Closed/Permanently Closed , Non Adjustment
14 -- and between Start Period and End Period both inclusive .
15 --
16 -- Column Values
17 -- ==============
18 -- ID = Pay_Patch_Status_s.Nextval
19 -- Patch Number = Ledger Id
20 -- Patch Name = Period Name
21 -- Process Type = 'PAYSLAUPG'
22 -- Status = Null if period needs to be upgraded by this process.
23 -- = U if period is already upgraded before.
24 -- Phase = 'PAYSLAUPG'||Concurrent Request Id .
25 -- To identify what periods are inserted by a particular
26 -- Conc Program
27 -------------------------------------------------------------------------------------
28
29 PROCEDURE Trans_Pay_Patch_Status
30 (p_Ledger_Id IN VARCHAR2,
31 p_Start_Period IN VARCHAR2,
32 p_End_Period IN VARCHAR2)
33 IS
34 CURSOR csr_Get_Periods IS
35 SELECT gps.Ledger_Id Ledger_Id,
36 gps.Period_Name Period_Name
37 FROM gl_Period_Statuses gps
38 WHERE gps.Ledger_Id = p_Ledger_Id
39 AND gps.cLosing_Status IN ('C','P')
40 AND gps.Adjustment_Period_Flag = 'N'
41 AND gps.Application_Id = '101'
42 AND gps.Start_Date >= (SELECT Start_Date
43 FROM gl_Period_Statuses
44 WHERE Ledger_Id = p_Ledger_Id
45 AND Application_Id = '101'
46 AND Period_Name = p_Start_Period)
47 AND (gps.End_Date <= (SELECT End_Date
48 FROM gl_Period_Statuses
49 WHERE Ledger_Id = p_Ledger_Id
50 AND Application_Id = '101'
51 AND Period_Name = p_End_Period)
52 OR p_End_Period is null)
53 AND EXISTS (SELECT 1
54 FROM Pay_All_Payrolls_f pp
55 WHERE pp.gl_Set_Of_Books_Id IS NOT NULL
56 AND gps.Ledger_Id = pp.gl_Set_Of_Books_Id)
57 ORDER BY gps.Start_Date;
58
59 lv_temp NUMBER(5);
60
61 BEGIN
62 /* Transfer rows from GL_PERIOD_STATUS to PAY_PATCH_STATUS */
63
64 FOR a IN csr_Get_Periods LOOP
65 lv_temp := NULL;
66
67 BEGIN
68 SELECT 1
69 INTO lv_temp
70 FROM Dual
71 WHERE EXISTS (SELECT 1
72 FROM Pay_Patch_Status pps
73 WHERE Process_Type = gv_process_name
74 AND pps.Patch_Number = a.Ledger_Id
75 AND pps.Patch_Name = a.Period_Name
76 AND Status = 'U');
77 EXCEPTION
78 WHEN No_Data_Found THEN
79 lv_temp := 0;
80 END;
81 /* If already upgraded thenset status as U else Null */
82
83 IF lv_temp = 0 THEN
84 INSERT INTO Pay_Patch_Status
85 (Id,
86 Patch_Number,
87 Patch_Name,
88 Process_Type,
89 Status,
90 Phase,
91 update_date)
92 VALUES (Pay_Patch_Status_s.Nextval,
93 a.Ledger_Id,
94 a.Period_Name,
95 gv_process_name,
96 NULL,
97 gv_process_name||fnd_Global.Conc_Request_Id,
98 sysdate );
99 ELSIF lv_temp = 1 THEN
100 INSERT INTO Pay_Patch_Status
101 (Id,
102 Patch_Number,
103 Patch_Name,
104 Process_Type,
105 Status,
106 Phase,
107 Description,
108 update_date)
109 VALUES (Pay_Patch_Status_s.Nextval,
110 a.Ledger_Id,
111 a.Period_Name,
112 gv_process_name,
113 'U',
114 gv_process_name||fnd_Global.Conc_Request_Id,
115 'Period already Upgraded',
116 sysdate);
117 END IF;
118 END LOOP;
119
120 COMMIT;
121 END;
122 -------------------------------------------------------------------------------------
123 -- Name : Update_Proc_MGR
124 -- Function : This is the Manager Process called by Conc Program
125 -- Upgrade Historical Payroll Data to SLA.
126 -------------------------------------------------------------------------------------
127
128 PROCEDURE Update_Proc_PAY_MGR (
129 X_errbuf out NOCOPY varchar2,
130 X_retcode out NOCOPY varchar2,
131 p_ledger_id in varchar2,
132 p_start_period in varchar2,
133 p_end_period in varchar2,
134 p_debug_flag in varchar2,
135 X_batch_size in number default 1,
136 X_Num_Workers in number default 5,
137 X_Argument4 in varchar2 default null,
138 X_Argument5 in varchar2 default null,
139 X_Argument6 in varchar2 default null,
140 X_Argument7 in varchar2 default null,
141 X_Argument8 in varchar2 default null,
142 X_Argument9 in varchar2 default null,
143 X_Argument10 in varchar2 default null)
144 IS
145
146 l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Proc_MGR';
147
148 l_stmt_num number;
149 l_api_name CONSTANT VARCHAR2(30) := 'Update_Proc_PAY_MGR';
150 l_api_version CONSTANT NUMBER := 1.0;
151
152 l_conc_status BOOLEAN;
153
154 submit_conc_failed EXCEPTION;
155
156 l_phase varchar2(80);
157 l_status_code varchar2(80);
158 l_dev_phase varchar2(15);
159 l_message varchar2(255);
160
161 L_SUB_REQTAB fnd_concurrent.requests_tab_type;
162 req_data varchar2(10);
163 submit_req boolean;
164
165 PAY_UPGRADE_RUNNING exception;
166 l_prg_appid number;
167 l_program_name varchar2(15);
168 l_reqid_count number;
169
170 l_ret_code varchar2(10);
171
172 l_argument4 number;
173 l_argument5 number;
174 l_product varchar2(30);
175 l_status varchar2(30);
176 l_industry varchar2(30);
177 l_retstatus boolean;
178 l_table_owner varchar2(30);
179 lv_param_found boolean default FALSE;
180 lv_Num_Workers number default 1;
181 lv_debug_enabled boolean default FALSE;
182 lv_no_periods boolean default TRUE;
183
184 Cursor csr_pay_patch_status(p_req_id in number) is
185 select row_number() over(partition by phase order by id) Nu,patch_number Ledger_id,patch_name Period_name,decode(Status,'U','Upgraded','E','Errored',null) Status,Description description
186 from pay_patch_status
187 where PROCESS_TYPE='PAYSLAUPG'
188 and phase='PAYSLAUPG'||to_char(p_req_id)
189 and patch_number=p_ledger_id
190 order by id;
191
192 BEGIN
193
194 if p_debug_flag = 'Y' then
195 lv_debug_enabled := TRUE;
196 end if;
197
198 If lv_debug_enabled then
199 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
200 fnd_file.put_line(fnd_file.log,'In P_LEDGER_ID :'|| P_LEDGER_ID);
201 fnd_file.put_line(fnd_file.log,'In P_START_PERIOD :'|| P_START_PERIOD);
202 fnd_file.put_line(fnd_file.log,'In P_END_PERIOD :'|| P_END_PERIOD);
203 End if;
204
205 l_stmt_num :=0;
206 l_stmt_num :=5;
207 l_prg_appid := 801;
208 l_program_name := 'PAYSLAUPG';
209 l_reqid_count := 0;
210 lv_no_periods := TRUE;
211
212 req_data := fnd_conc_global.request_data;
213
214 if (req_data is null) then
215 submit_req := TRUE;
216 else
217 submit_req := FALSE;
218 end if;
219
220 if (submit_req = TRUE) then
221
222 if (nvl(fnd_global.conc_request_id, -1) < 0) then
223 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
224 end if;
225
226 -- Standard call to check for call compatibility
227 IF NOT FND_API.Compatible_API_Call (
228 l_api_version,
229 1.0,
230 l_api_name,
231 G_PKG_NAME ) THEN
232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
233 END IF;
234
235 x_retcode := FND_API.G_RET_STS_SUCCESS;
236
237 l_product :='PAY';
238
239 l_stmt_num :=10;
240 l_retstatus := fnd_installation.get_app_info(
241 l_product, l_status, l_industry, l_table_owner);
242
243 if ((l_retstatus = TRUE) AND (l_table_owner is not null)) then
244
245 Begin
246
247 /* Call Trans_pay_patch_status to Transfer rows from GL_PERIOD_STATUS to PAY_PATCH_STATUS */
248
249 If lv_debug_enabled then
250 fnd_file.put_line(fnd_file.log,'Before Trans_pay_patch_status ');
251 End if;
252
253 Trans_pay_patch_status (
254 P_LEDGER_ID => P_LEDGER_ID,
255 P_START_PERIOD => P_START_PERIOD,
256 P_END_PERIOD => P_END_PERIOD);
257
258 If lv_debug_enabled then
259 fnd_file.put_line(fnd_file.log,'After Trans_pay_patch_status ');
260 End if;
261
262 lv_Num_Workers := X_Num_Workers;
263 /* Get the Pay Action Parameter Value of THREADS */
264
265 If lv_debug_enabled then
266 fnd_file.put_line(fnd_file.log,'Before pay_core_utils.get_action_parameter ');
267 End if;
268
269 pay_core_utils.get_action_parameter('THREADS',lv_Num_Workers,lv_param_found);
270
271 if (not lv_param_found) then
272 lv_Num_Workers := x_Num_Workers;
273 end if;
274
275 If lv_debug_enabled then
276 fnd_file.put_line(fnd_file.log,'No of Workers :'||to_char(lv_Num_Workers));
277 End if;
278
279 If lv_debug_enabled then
280 fnd_file.put_line(fnd_file.log,'After pay_core_utils.get_action_parameter ');
281 End if;
282
283 begin
284
285 If lv_debug_enabled then
286 fnd_file.put_line(fnd_file.log,'Before AD_CONC_UTILS_PKG.submit_subrequests ');
287 End if;
288
289 AD_CONC_UTILS_PKG.submit_subrequests(
290 X_errbuf=>X_errbuf,
291 X_retcode=>X_retcode,
292 X_WorkerConc_app_shortname=>'PAY',
293 X_WorkerConc_progname=>'PAYSLAUPGW',
294 X_Batch_size=>X_batch_size,
295 X_Num_Workers=>lv_Num_Workers,
296 X_Argument4 => P_LEDGER_ID,
297 X_Argument5 => P_START_PERIOD,
298 X_Argument6 => nvl(P_END_PERIOD,'null'),
299 X_Argument7 => fnd_global.conc_request_id,
300 X_Argument8 => P_DEBUG_FLAG,
301 X_Argument9 => null,
302 X_Argument10 => null);
303
304 exception when others then
305 fnd_file.put_line(FND_FILE.LOG,'Error '||sqlcode ||' '||sqlerrm);
306
307 end;
308
309 If lv_debug_enabled then
310 fnd_file.put_line(fnd_file.log,'After AD_CONC_UTILS_PKG.submit_subrequests ');
311 End if;
312 exception
313 when no_data_found then
314 fnd_file.put_line(FND_FILE.LOG, 'No Payroll Sub Ledger data needs to be upgraded.');
315
316 end;
317 end if;
318 else
319
320 l_sub_reqtab := fnd_concurrent.get_sub_requests(fnd_global.conc_request_id);
321
322 x_retcode := FND_API.G_RET_STS_SUCCESS;
323
324 for i IN 1..l_sub_reqtab.COUNT()
325 loop
326
327 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
328 X_retcode := FND_API.g_ret_sts_unexp_error;
329 end if;
330
331 end loop;
332
333 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
334 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------------------------------');
335 fnd_file.put_line(FND_FILE.LOG,'-------------------Upgrade of Historical Payroll data to Subledger Accounting---------');
336 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------------------------------');
337 fnd_file.put_line(FND_FILE.LOG,'Parameters:');
338 fnd_file.put_line(FND_FILE.LOG,'Ledger Id : '||p_ledger_id);
339 fnd_file.put_line(FND_FILE.LOG,'Start Period : '||p_start_period);
340 fnd_file.put_line(FND_FILE.LOG,'End Period : '||p_end_period);
341
342 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------------------------------');
343 fnd_file.put_line(FND_FILE.LOG,' No Period Name Status Message ');
344 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------------------------------');
345
346
347 For b in csr_pay_patch_status(fnd_global.conc_request_id)
348 loop
349 fnd_file.put_line(FND_FILE.LOG,' '||substr(rpad(to_char(b.nu),3,' '),1,4)||' '||b.period_name||' '||b.status||' '||b.Description);
350 lv_no_periods := FALSE;
351 end loop;
352
353 If lv_no_periods then
354 fnd_file.put_line(FND_FILE.LOG,' There are no Closed/Permanently Closed Periods between this Start Date and End Date');
355 end if;
356
357 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------------------------------');
358 fnd_file.put_line(FND_FILE.LOG,'--------------------------------------------------------------------------------------');
359 end if;
360 end if;
361
362 If lv_debug_enabled then
363 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
364 End if;
365
366 EXCEPTION
367 WHEN submit_conc_failed THEN
368 X_retcode := FND_API.g_ret_sts_unexp_error;
369 X_errbuf:=l_module||'.'||l_stmt_num||': Submit concurrent request failed.';
370 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
371
372 WHEN fnd_api.g_exc_unexpected_error THEN
373 X_retcode := FND_API.g_ret_sts_unexp_error;
374
375 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
376 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
377
378 WHEN fnd_api.g_exc_error THEN
379 X_retcode := FND_API.g_ret_sts_error;
380
381 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
382 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
383
384 WHEN OTHERS THEN
385 X_retcode := FND_API.g_ret_sts_unexp_error;
386 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
387 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
388
389 END Update_Proc_PAY_MGR;
390
391 ----------------------------------------------------------------------------------------
392 -- Name : Update_Proc_PAY_WKR
393 -- Function : Worker process to update Payroll Sub Ledger to SLA data model.
394 -- This is called by Manager Procewss Update_Proc_PAY_MGR
395
396 ----------------------------------------------------------------------------------------
397 PROCEDURE Update_Proc_PAY_WKR (
398 X_errbuf out NOCOPY varchar2,
399 X_retcode out NOCOPY varchar2,
400 X_batch_size in number,
401 X_Worker_Id in number,
402 X_Num_Workers in number,
403 X_Argument4 in varchar2 default null,
404 X_Argument5 in varchar2 default null,
405 X_Argument6 in varchar2 default null,
406 X_Argument7 in varchar2 default null,
407 X_Argument8 in varchar2 default null,
408 X_Argument9 in varchar2 default null,
409 X_Argument10 in varchar2 default null)
410 IS
411
412 l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Proc_WKR';
413 l_worker_id number;
414 l_product varchar2(30);
415 l_table_name varchar2(30) := 'PAY_PATCH_STATUS';
416 l_id_column varchar2(30) := 'ID';
417 l_update_name varchar2(30);
418
419 l_table_owner varchar2(30);
420 l_status VARCHAR2(30);
421 l_industry VARCHAR2(30);
422 l_retstatus BOOLEAN;
423 l_any_rows_to_process boolean;
424
425 l_start_id number;
426 l_end_id number;
427 l_rows_processed number;
428
429 l_stmt_num number;
430 update_subledger_exception exception;
431 l_conc_status BOOLEAN;
432 lv_debug_enabled boolean default FALSE;
433
434 BEGIN
435 l_stmt_num :=0;
436 if X_Argument8 = 'Y' then /* X_Argument8 = P_DEBUG_FLAG */
437 lv_debug_enabled := TRUE;
438 end if;
439
440 If lv_debug_enabled then
441 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
442 fnd_file.put_line(fnd_file.log,'In P_LEDGER_ID(X_Argument4) :'|| X_Argument4);
443 End if;
444 --
445 BEGIN
446 l_stmt_num :=10;
447
448 l_update_name := 'PAYSLA' ||X_Argument7;
449 l_retstatus := FND_INSTALLATION.GET_APP_INFO('PAY', l_status, l_industry, l_table_owner);
450
451 /*Modified the application short name as PAY instead of GL for bug 13017809
452 Also note that the table pay_patch_status is a PAY table */
453 /*l_table_owner:='HR'; Commented out for bug 12343825*/
454
455
456 ad_parallel_updates_pkg.initialize_id_range(
457 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
458 X_owner=>l_table_owner,
459 X_table=>l_table_name,
460 X_script=>l_update_name,
461 X_ID_column=>l_id_column,
462 X_worker_id=>X_Worker_Id,
463 X_num_workers=>X_num_workers,
464 X_batch_size=>X_batch_size,
465 X_debug_level=>0);
466
467
468 ad_parallel_updates_pkg.get_id_range(
469 l_start_id,
470 l_end_id,
471 l_any_rows_to_process,
472 X_batch_size,
473 TRUE);
474
475
476 while (l_any_rows_to_process = TRUE)
477 loop
478 l_stmt_num :=30;
479 If lv_debug_enabled then
480 fnd_file.put_line(fnd_file.log,'Before calling Update_Payroll_Subledger');
481 fnd_file.put_line(fnd_file.log,'l_start_id :'||l_start_id);
482 fnd_file.put_line(fnd_file.log,'l_end_id :'||l_end_id);
483 End if;
484
485 PAY_SLA_UPDATE_PKG.Update_Payroll_Subledger(
486 X_errbuf=>X_errbuf,
487 X_retcode=>X_retcode,
488 X_start_id=>l_start_id,
489 X_end_id=>l_end_id,
490 P_LEDGER_ID=>X_Argument4,
491 P_MGR_REQ_ID=>X_Argument7,
492 P_DEBUG_FLAG=>X_Argument8);
493
494 If lv_debug_enabled then
495 fnd_file.put_line(fnd_file.log,'After calling Update_Payroll_Subledger');
496 End if;
497
498 if (X_retcode <>FND_API.G_RET_STS_SUCCESS) then
499 raise update_subledger_exception;
500 end if;
501
502 l_rows_processed := X_batch_size;
503
504 l_stmt_num :=40;
505
506 ad_parallel_updates_pkg.processed_id_range(
507 l_rows_processed,
508 l_end_id);
509
510 commit;
511
512 l_stmt_num :=50;
513 ad_parallel_updates_pkg.get_id_range(
514 l_start_id,
515 l_end_id,
516 l_any_rows_to_process,
517 X_batch_size,
518 FALSE);
519
520 end loop;
521
522 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
527 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
528 raise;
529 END;
530
531 If lv_debug_enabled then
532 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
533 End if;
534
535 EXCEPTION
536 WHEN update_subledger_exception THEN
537 ROLLBACK;
538 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
539 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
540
541 WHEN fnd_api.g_exc_unexpected_error THEN
542 ROLLBACK;
543 X_retcode := FND_API.g_ret_sts_unexp_error;
544 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
545 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
546
547 WHEN fnd_api.g_exc_error THEN
548 ROLLBACK;
549 X_retcode := FND_API.g_ret_sts_error;
550 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
551 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
552
553 WHEN OTHERS THEN
554 ROLLBACK;
555 X_retcode := FND_API.g_ret_sts_unexp_error;
556 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
557 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
558
559 END Update_Proc_PAY_WKR;
560
561 ----------------------------------------------------------------------------------------
562 -- Name : GET_SEQUENCE_VALUE
563 -- Function : TO get the sequence values for XLA Sequences .
564 ----------------------------------------------------------------------------------------
565
566 FUNCTION get_sequence_value(p_row_number in number,p_tab_name varchar2)
567 return number is
568 BEGIN
569
570 if p_tab_name='xla_transaction_entities' then
571 If p_row_number = 1 then
572 select xla_transaction_entities_s.nextval,
573 xla_events_s.nextval,
574 xla_ae_headers_s.nextval
575 into g_xla_tran_s,g_xla_event_s,g_xla_headers_s
576 from dual;
577 return g_xla_tran_s;
578 else
579 return g_xla_tran_s;
580 end if;
581 end if;
582
583 if p_tab_name='xla_events' then
584 return g_xla_event_s;
585 end if;
586
587 if p_tab_name='xla_ae_headers' then
588 return g_xla_headers_s;
589 end if;
590
591 END get_sequence_value;
592
593 ----------------------------------------------------------------------------------------
594 -- Name : GET_FULL_NAME
595 -- Function : TO get the Full Name from Assignment_Action_Id
596 ----------------------------------------------------------------------------------------
597
598 FUNCTION get_full_name(p_assignment_act_id in pay_assignment_actions.assignment_action_id%type,
599 p_eff_date in date)
600 return varchar2 is
601
602 Cursor csr_full_name(p_assnmnt_act_id in pay_assignment_actions.assignment_action_id%type,
603 p_eff_date in date)
604 is
605 select full_name
606 from per_people_f ppf,
607 per_assignments_f paf,
608 pay_assignment_actions paa
609 where paa.assignment_id=paf.assignment_id
610 and paf.person_id=ppf.person_id
611 and paa.assignment_action_id=p_assnmnt_act_id
612 and p_eff_date between ppf.effective_start_date and ppf.effective_end_date;
613
614 lv_full_name varchar2(240);
615 BEGIN
616
617 open csr_full_name(p_assignment_act_id,p_eff_date);
618 fetch csr_full_name into lv_full_name;
619 close csr_full_name;
620
621 return lv_full_name;
622 END get_full_name;
623
624
625 -------------------------------------------------------------------------------------
626 -- Name : Update_Payroll_Subledger
627 -- Type : Private
628 -- Function : To update Payroll Sub Ledger to SLA data model from Start ID
629 -- to End ID for Ledger (P_LEDGER_ID).
630 -- Pre-reqs :
631 -- Parameters :
632 -- IN : X_start_id in number
633 -- X_end_id in number
634 -- P_LEDGER_ID in varchar2
635 -- P_MGR_REQ_ID in varchar2
636 --
637 -- OUT : X_errbuf out NOCOPY varchar2,
638 -- X_retcode out NOCOPY varchar2
639 --
640 -- Notes : The Procedure is called from Update_Proc_PAY_WKR.
641 --
642 -- End of comments
643 -------------------------------------------------------------------------------------
644
645 PROCEDURE Update_Payroll_Subledger (
646 x_errbuf out nocopy varchar2,
647 x_retcode out nocopy varchar2,
648 x_start_id in number,
649 x_end_id in number,
650 p_ledger_id in varchar2,
651 p_mgr_req_id in varchar2,
652 p_debug_flag in varchar2
653 )
654 IS
655
656 l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Payroll_Subledger';
657 lv_application_id CONSTANT number := 801;
658 l_upg_batch_id number;
659 lv_Error_msg varchar2(1000);
660 lv_application_name CONSTANT varchar2(10) := 'Payroll';
661 lv_debug_enabled boolean default FALSE;
662
663 Cursor csr_get_periods(p_start_id in pay_patch_status.id%type,
664 p_end_id in pay_patch_status.id%type,
665 p_process_name in varchar2,
666 p_mgr_req_id in varchar2)
667 is
668 select patch_number ledger_id,patch_name period_name
669 from pay_patch_status
670 where id between p_start_id and p_end_id
671 and process_type=p_process_name
672 and phase = p_process_name||p_mgr_req_id
673 and patch_number=p_ledger_id
674 and status is null;
675
676 Cursor csr_get_headers(p_ledger_id in gl_je_headers.ledger_id%type,
677 p_period_name in gl_je_headers.period_name%type,
678 p_app_name in varchar2)
679 is
680 select je_header_id
681 from gl_je_headers gjh
682 where gjh.period_name=p_period_name
683 and gjh.je_category=p_app_name
684 and gjh.ledger_id=p_ledger_id
685 and gjh.je_source=p_app_name
686 order by je_header_id;
687
688 BEGIN
689
690 if p_debug_flag = 'Y' then
691 lv_debug_enabled := TRUE;
692 end if;
693
694 If lv_debug_enabled then
695 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
696 fnd_file.put_line(fnd_file.log,'In P_LEDGER_ID :'|| P_LEDGER_ID);
697 fnd_file.put_line(fnd_file.log,'In X_START_ID :'|| to_char(X_start_id));
698 fnd_file.put_line(fnd_file.log,'In X_END_ID :'|| to_char(X_end_id));
699 fnd_file.put_line(fnd_file.log,'In P_MGR_REQ_ID :'|| to_char(p_mgr_req_id));
700 End if;
701
702 l_upg_batch_id := to_number(p_mgr_req_id);
703
704 g_xla_tran_s :=0;
705 g_xla_event_s :=0;
706 g_xla_headers_s :=0;
707
708 For j in csr_get_periods(X_start_id ,X_end_id,gv_process_name,to_char(p_mgr_req_id))
709 Loop
710
711 If lv_debug_enabled then
712 fnd_file.put_line(fnd_file.log,'Before upgrading ledger_id :'|| j.ledger_id);
713 fnd_file.put_line(fnd_file.log,'Before upgrading period_name :'|| j.period_name);
714 fnd_file.put_line(fnd_file.log,'Before upgrading gv_process_name :'|| gv_process_name);
715 End if;
716
717 Begin
718
719 If lv_debug_enabled then
720 fnd_file.put_line(fnd_file.log,'Before updating GL Tables ');
721 End if;
722
723 For l in csr_get_headers(j.ledger_id,j.period_name,lv_application_name)
724 Loop
725
726 update gl_je_headers
727 set je_from_sla_flag='Y'
728 where je_header_id=l.je_header_id;
729
730 update gl_je_lines
731 set gl_sl_link_id=XLA_GL_SL_LINK_ID_S.nextval ,
732 gl_sl_link_table='XLAJEL'
733 where je_header_id =l.je_header_id;
734
735 update gl_import_references gir
736 set gir.gl_sl_link_id = (select gl_sl_link_id from gl_je_lines gjl1
737 where gir.je_header_id = gjl1.je_header_id
738 and gir.je_line_num=gjl1.je_line_num),
739 gir.gl_sl_link_table ='XLAJEL'
740 where gir.je_header_id = l.je_header_id
741 and gir.je_line_num = (select je_line_num from gl_je_lines gjl
742 where gir.je_header_id = gjl.je_header_id
743 and gir.je_line_num=gjl.je_line_num);
744
745 End loop;
746
747 If lv_debug_enabled then
748 fnd_file.put_line(fnd_file.log,'After updating GL Tables ');
749 End if;
750
751 If lv_debug_enabled then
752 fnd_file.put_line(fnd_file.log,'Before Inserting into XLA Tables ');
753 End if;
754
755 INSERT ALL
756 WHEN (rank_id=1) then
757 INTO xla_transaction_entities (
758 upg_batch_id,
759 entity_id,
760 application_id,
761 ledger_id,
762 entity_code,
763 source_id_int_1,
764 source_id_char_1,
765 transaction_number,
766 creation_date,
767 created_by,
768 last_update_date,
769 last_updated_by,
770 last_update_login,
771 source_application_id,
772 upg_source_application_id)
773 VALUES (l_upg_batch_id,
774 xla_transaction_seq,
775 lv_application_id,
776 ledger_id,
777 'ASSIGNMENTS',
778 TGL_ASSIGNMENT_ACTION_ID,
779 To_char(EFFECTIVE_DATE,'YYYY/MM/DD'),
780 TGL_ASSIGNMENT_ACTION_ID,
781 creation_date,
782 created_by,
783 last_update_date,
784 last_updated_by,
785 last_update_login,
786 lv_application_id,
787 lv_application_id)
788 INTO xla_events (
789 upg_batch_id,
790 application_id,
791 entity_id,
792 event_id,
793 event_number,
794 event_type_code,
795 event_date,
796 event_status_code,
797 process_status_code,
798 on_hold_flag,
799 creation_date,
800 created_by,
801 last_update_date,
802 last_updated_by,
803 last_update_login,
804 transaction_date,
805 upg_source_application_id)
806 VALUES (l_upg_batch_id,
807 lv_application_id,
808 xla_transaction_seq,
809 xla_events_seq,
810 1,
811 EVENT_TYPE_CODE,
812 EFFECTIVE_DATE,
813 'P',
814 'P',
815 'N',
816 creation_date,
817 created_by,
818 last_update_date,
819 last_updated_by,
820 last_update_login,
821 EFFECTIVE_DATE,
822 lv_application_id)
823 INTO xla_ae_headers (
824 upg_batch_id,
825 application_id,
826 amb_context_code,
827 entity_id,
828 event_id,
829 event_type_code,
830 ae_header_id,
831 ledger_id,
832 je_category_name,
833 accounting_date,
834 period_name,
835 balance_type_code,
836 gl_transfer_status_code,
837 gl_transfer_date,
838 accounting_entry_status_code,
839 accounting_entry_type_code,
840 description,
841 creation_date,
842 created_by,
843 last_update_date,
844 last_updated_by,
845 last_update_login,
846 zero_amount_flag,
847 accrual_reversal_flag,
848 upg_source_application_id)
849 VALUES (l_upg_batch_id,
850 lv_application_id,
851 'DEFAULT',
852 xla_transaction_seq,
853 xla_events_seq,
854 EVENT_TYPE_CODE,
855 xla_ae_headers_seq,
856 ledger_id,
857 lv_application_name,
858 EFFECTIVE_DATE,
859 period_name,
860 'A',
861 'Y',
862 effective_date,
863 'F',
864 'STANDARD',
865 header_desc,
866 creation_date,
867 created_by,
868 last_update_date,
869 last_updated_by,
870 last_update_login,
871 'N',
872 'N',
873 lv_application_id)
874 INTO pay_xla_events(
875 event_id,
876 assignment_action_id,
877 accounting_date,
878 event_status)
879 VALUES(xla_events_seq,
880 TGL_ASSIGNMENT_ACTION_ID,
881 EFFECTIVE_DATE,
882 'P')
883 WHEN (1=1) then
884 INTO xla_ae_lines (
885 upg_batch_id,
886 application_id,
887 ae_header_id,
888 ae_line_num,
889 code_combination_id,
890 gl_transfer_mode_code,
891 description,
892 accounted_dr,
893 accounted_cr,
894 currency_code,
895 currency_conversion_date,
896 currency_conversion_rate,
897 currency_conversion_type,
898 entered_dr,
899 entered_cr,
900 accounting_class_code,
901 gl_sl_link_id,
902 gl_sl_link_table,
903 gain_or_loss_flag,
904 creation_date,
905 created_by,
906 last_update_date,
907 last_updated_by,
908 last_update_login,
909 accounting_date,
910 ledger_id,
911 mpa_accrual_entry_flag)
912 VALUES (l_upg_batch_id,
913 lv_application_id,
914 xla_ae_headers_seq,
915 rank_id,
916 code_combination_id,
917 'S',
918 line_desc,
919 accounted_dr,
920 accounted_cr,
921 currency_code,
922 currency_conversion_date,
923 currency_conversion_rate,
924 currency_conversion_type,
925 entered_dr,
926 entered_cr,
927 'COST',
928 link_id,
929 'XLAJEL',
930 'N',
931 creation_date,
932 created_by,
933 last_update_date,
934 last_updated_by,
935 last_update_login,
936 EFFECTIVE_DATE,
937 ledger_id,
938 'N')
939 INTO xla_distribution_links (
940 upg_batch_id,
941 application_id,
942 event_id,
943 ae_header_id,
944 ae_line_num,
945 source_distribution_type,
946 source_distribution_id_num_1,
947 merge_duplicate_code,
948 ref_ae_header_id,
949 temp_line_num,
950 event_class_code,
951 event_type_code)
952 VALUES (l_upg_batch_id,
953 lv_application_id,
954 xla_events_seq,
955 xla_ae_headers_seq,
956 rank_id,
957 action_type,
958 TGL_ASSIGNMENT_ACTION_ID,
959 'N',
960 xla_ae_headers_seq,
961 rank_id,
962 EVENT_CLASS_CODE,
963 DIS_EVENT_TYPE_CODE)
964 SELECT row_number() over(partition by tgl_assignment_action_id order by debit_or_credit) RANK_ID,
965 get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_transaction_entities') xla_transaction_seq,
966 get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_events') xla_events_seq,
967 get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_ae_headers') xla_ae_headers_seq,
968 ledger_id,
969 period_name,
970 je_header_id,
971 currency_code,
972 currency_conversion_date,
973 currency_conversion_rate,
974 currency_conversion_type,
975 effective_date,
976 tgl_assignment_action_id,
977 code_combination_id,
978 costing_assignment_action_id,
979 link_id,
980 cost_allocation_keyflex_id,
981 element_name,
982 debit_or_credit,
983 entered_dr,
984 entered_cr,
985 (entered_dr*currency_conversion_rate) accounted_dr,
986 (entered_cr*currency_conversion_rate) accounted_cr,
987 decode(action_type,'C','COST','CP','PAYMENT_COST','S','RETRO_COST') event_type_code,
988 decode(action_type,'C','COSTS_ALL','CP','PAYMENT_COSTS_ALL','COSTS_ALL') dis_event_type_code,
989 decode(action_type,'C','COSTS','CP','PAYMENT_COSTS','COSTS') event_class_code,
990 decode(action_type,'CP','Payment Cost for '||get_full_name(tgl_assignment_action_id,effective_date)||' on '||effective_date) header_desc,
991 decode(action_type,'C',debit_or_credit ||' Cost for '||Element_name,'CP',debit_or_credit||' payment cost') line_desc,
992 action_type,
993 creation_date,
994 created_by,
995 last_update_date,
996 last_updated_by,
997 last_update_login
998 From
999 (SELECT distinct gjh.ledger_id LEDGER_ID,
1000 gjh.period_name PERIOD_NAME,
1001 gjh.je_header_id JE_HEADER_ID,
1002 gjh.currency_code,
1003 gjh.currency_conversion_date,
1004 gjh.currency_conversion_rate,
1005 gjh.currency_conversion_type,
1006 gjl.effective_date EFFECTIVE_DATE,
1007 pa.assignment_action_id TGL_ASSIGNMENT_ACTION_ID,
1008 gjl.code_combination_id CODE_COMBINATION_ID,
1009 gjl.gl_sl_link_id LINK_ID,
1010 gjl.creation_date,
1011 gjl.created_by,
1012 gjl.last_update_date,
1013 gjl.last_updated_by,
1014 gjl.last_update_login,
1015 pcv.assignment_action_id COSTING_ASSIGNMENT_ACTION_ID,
1016 pcv.cost_allocation_keyflex_id,
1017 pcv.element_name,
1018 pcv.debit_or_credit,
1019 decode(pcv.debit_or_credit,'Debit',pcv.costed_value,null) entered_dr,
1020 decode(pcv.debit_or_credit,'Credit',pcv.costed_value,null) entered_cr ,
1021 ppa1.action_type
1022 FROM pay_payroll_actions ppa1, -- Cost pay actions
1023 pay_assignment_actions pa1, -- Cost asg actions.
1024 pay_action_interlocks pi3, -- Cost - Run
1025 pay_action_interlocks pi1, -- Cost - Trans GL
1026 pay_all_payrolls_f pp,
1027 pay_action_classifications pac,
1028 pay_payroll_actions ppa2, -- Payroll run actions.
1029 pay_assignment_actions pa2, -- Payroll run asg actions.
1030 pay_action_interlocks pi2, -- Run - Trans GL
1031 pay_assignment_actions pa, -- Trans GL asg actions
1032 pay_payroll_actions ppa, -- Trans GL pay actions
1033 pay_costs_v pcv,
1034 gl_je_headers gjh,
1035 gl_je_lines gjl
1036 WHERE ppa.payroll_action_id = to_number(gjl.reference_1)
1037 AND pa.payroll_action_id = ppa.payroll_action_id
1038 AND pa.action_status = 'C'
1039 AND ppa2.payroll_action_id = to_number(gjl.reference_5)
1040 AND pcv.cost_allocation_keyflex_id=to_number(gjl.reference_2)
1041 AND pi2.locking_action_id = pa.assignment_action_id
1042 AND pa2.assignment_action_id = pi2.locked_action_id
1043 AND ppa2.payroll_action_id = pa2.payroll_action_id
1044 AND ppa2.consolidation_set_id +0 = ppa.consolidation_set_id
1045 AND pac.action_type = ppa2.action_type
1046 AND pac.classification_name = 'COSTED'
1047 AND pp.payroll_id = ppa2.payroll_id
1048 AND pi1.locking_action_id = pa.assignment_action_id
1049 AND pa1.assignment_action_id = pi1.locked_action_id
1050 AND pa1.assignment_action_id <> pa2.assignment_action_id
1051 AND pi3.locking_action_id = pa1.assignment_action_id
1052 AND pa2.assignment_action_id = pi3.locked_action_id
1053 AND ppa1.payroll_action_id = pa1.payroll_action_id
1054 AND ppa1.action_type in ('C','S')
1055 AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
1056 AND pcv.assignment_action_id= pa1.assignment_action_id
1057 AND gjl.je_header_id=gjh.je_header_id
1058 AND decode(gjl.entered_cr,0,'Debit','Credit')=pcv.debit_or_credit
1059 AND gjh.period_name=j.period_name
1060 AND gjh.je_category=lv_application_name
1061 AND gjh.ledger_id= j.ledger_id
1062 AND gjh.je_source=lv_application_name
1063 UNION
1064 SELECT distinct gjh.ledger_id LEDGER_ID,
1065 gjh.period_name PERIOD_NAME,
1066 gjh.je_header_id JE_HEADER_ID,
1067 gjh.currency_code,
1068 gjh.currency_conversion_date,
1069 gjh.currency_conversion_rate,
1070 gjh.currency_conversion_type,
1071 gjl.effective_date EFFECTIVE_DATE,
1072 pa.assignment_action_id TGL_ASSIGNMENT_ACTION_ID,
1073 gjl.code_combination_id CODE_COMBINATION_ID,
1074 gjl.gl_sl_link_id LINK_ID,
1075 gjl.creation_date,
1076 gjl.created_by,
1077 gjl.last_update_date,
1078 gjl.last_updated_by,
1079 gjl.last_update_login,
1080 ppc.assignment_action_id COSTING_ASSIGNMENT_ACTION_ID,
1081 ppc.gl_account_ccid,
1082 ppc.payment_method_name,
1083 ppc.debit_or_credit,
1084 decode(ppc.debit_or_credit,'Debit',to_number(ppc.costed_value),null) entered_dr,
1085 decode(ppc.debit_or_credit,'Credit',to_number(ppc.costed_value),null) entered_cr,
1086 ppa1.action_type
1087 FROM pay_payroll_actions ppa, -- Trans GL pay actions
1088 pay_assignment_actions pa, -- Trans GL asg actions
1089 pay_action_interlocks pi1, -- Cost - Trans GL
1090 pay_assignment_actions pa1, -- Cost asg actions
1091 pay_payroll_actions ppa1, -- Cost pay actions
1092 per_all_assignments_f pera,
1093 pay_all_payrolls_f pp,
1094 pay_payment_costs_v ppc,
1095 gl_je_headers gjh,
1096 gl_je_lines gjl
1097 WHERE ppa.payroll_action_id = to_number(gjl.reference_1)
1098 AND pa.payroll_action_id = ppa.payroll_action_id
1099 AND pi1.locking_action_id = pa.assignment_action_id
1100 AND pa1.assignment_action_id = pi1.locked_action_id
1101 AND ppa1.payroll_action_id = pa1.payroll_action_id
1102 AND ppa1.action_type = 'CP'
1103 AND pera.assignment_id = pa.assignment_id
1104 AND ppa1.effective_date BETWEEN pera.effective_start_date AND pera.effective_end_date
1105 AND pp.payroll_id = pera.payroll_id
1106 AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
1107 AND ppc.gl_account_ccid =to_number(gjl.reference_2)
1108 AND ppc.assignment_action_id= pa1.assignment_action_id
1109 AND gjl.je_header_id=gjh.je_header_id
1110 AND decode(gjl.entered_cr,0,'Debit','Credit')=ppc.debit_or_credit
1111 AND gjh.period_name=j.period_name
1112 AND gjh.je_category=lv_application_name
1113 AND gjh.ledger_id= j.ledger_id
1114 AND gjh.je_source=lv_application_name) A;
1115
1116
1117 If lv_debug_enabled then
1118 fnd_file.put_line(fnd_file.log,'After Inserting into XLA Tables ');
1119 End if;
1120
1121 update pay_patch_status
1122 set status='U',
1123 description='Period Successfully Upgraded'
1124 where process_type=gv_process_name
1125 and phase =gv_process_name||P_MGR_REQ_ID
1126 and patch_number=j.ledger_id
1127 and patch_name =j.period_name;
1128
1129 commit;
1130
1131 If lv_debug_enabled then
1132 fnd_file.put_line(fnd_file.log,'Period '||j.period_name||' Successfully upgraded ');
1133 End if;
1134
1135
1136 Exception when others then
1137 Rollback;
1138
1139 lv_Error_msg :=SQLCODE||' '||SQLERRM;
1140
1141 update pay_patch_status
1142 set status='E',
1143 description='Error '||lv_Error_msg
1144 where process_type=gv_process_name
1145 and phase =gv_process_name||P_MGR_REQ_ID
1146 and patch_number=j.ledger_id
1147 and patch_name =j.period_name;
1148
1149 fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||j.period_name||' '||lv_Error_msg);
1150
1151 End;
1152
1153 If lv_debug_enabled then
1154 fnd_file.put_line(fnd_file.log,'After upgrading ledger_id :'|| j.ledger_id);
1155 fnd_file.put_line(fnd_file.log,'After upgrading period_name :'|| j.period_name);
1156 End if;
1157
1158 End Loop;
1159
1160 If lv_debug_enabled then
1161 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
1162 End if;
1163
1164 EXCEPTION when others then
1165 Rollback;
1166 X_errbuf:=l_module||': '|| SQLERRM;
1167 fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||SQLCODE||' '||SQLERRM);
1168 end Update_Payroll_Subledger;
1169
1170 END PAY_SLA_UPDATE_PKG;