[Home] [Help]
PACKAGE BODY: APPS.PAY_SLA_UPDATE_PKG
Source
1 PACKAGE BODY PAY_SLA_UPDATE_PKG AS
2 /* $Header: payxlaupg.pkb 120.0.12010000.4 2009/01/28 10:00:51 priupadh noship $*/
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('GL', l_status, l_industry, l_table_owner);
450 l_table_owner:='HR';
451
452
453 ad_parallel_updates_pkg.initialize_id_range(
454 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
455 X_owner=>l_table_owner,
456 X_table=>l_table_name,
457 X_script=>l_update_name,
458 X_ID_column=>l_id_column,
459 X_worker_id=>X_Worker_Id,
460 X_num_workers=>X_num_workers,
461 X_batch_size=>X_batch_size,
462 X_debug_level=>0);
463
464
465 ad_parallel_updates_pkg.get_id_range(
466 l_start_id,
467 l_end_id,
468 l_any_rows_to_process,
469 X_batch_size,
470 TRUE);
471
472
473 while (l_any_rows_to_process = TRUE)
474 loop
475 l_stmt_num :=30;
476 If lv_debug_enabled then
477 fnd_file.put_line(fnd_file.log,'Before calling Update_Payroll_Subledger');
478 fnd_file.put_line(fnd_file.log,'l_start_id :'||l_start_id);
479 fnd_file.put_line(fnd_file.log,'l_end_id :'||l_end_id);
480 End if;
481
482 PAY_SLA_UPDATE_PKG.Update_Payroll_Subledger(
483 X_errbuf=>X_errbuf,
484 X_retcode=>X_retcode,
485 X_start_id=>l_start_id,
486 X_end_id=>l_end_id,
487 P_LEDGER_ID=>X_Argument4,
488 P_MGR_REQ_ID=>X_Argument7,
489 P_DEBUG_FLAG=>X_Argument8);
490
491 If lv_debug_enabled then
492 fnd_file.put_line(fnd_file.log,'After calling Update_Payroll_Subledger');
493 End if;
494
495 if (X_retcode <>FND_API.G_RET_STS_SUCCESS) then
496 raise update_subledger_exception;
497 end if;
498
499 l_rows_processed := X_batch_size;
500
501 l_stmt_num :=40;
502
503 ad_parallel_updates_pkg.processed_id_range(
504 l_rows_processed,
505 l_end_id);
506
507 commit;
508
509 l_stmt_num :=50;
510 ad_parallel_updates_pkg.get_id_range(
511 l_start_id,
512 l_end_id,
513 l_any_rows_to_process,
514 X_batch_size,
515 FALSE);
516
517 end loop;
518
519 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
524 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
525 raise;
526 END;
527
528 If lv_debug_enabled then
529 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
530 End if;
531
532 EXCEPTION
533 WHEN update_subledger_exception THEN
534 ROLLBACK;
535 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
536 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
537
538 WHEN fnd_api.g_exc_unexpected_error THEN
539 ROLLBACK;
540 X_retcode := FND_API.g_ret_sts_unexp_error;
541 X_errbuf:=l_module||'.'||l_stmt_num||': An exception has occurred.';
542 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
543
544 WHEN fnd_api.g_exc_error THEN
545 ROLLBACK;
546 X_retcode := FND_API.g_ret_sts_error;
547 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
548 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
549
550 WHEN OTHERS THEN
551 ROLLBACK;
552 X_retcode := FND_API.g_ret_sts_unexp_error;
553 X_errbuf:=l_module||'.'||l_stmt_num||': '|| SQLERRM;
554 l_conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',X_errbuf);
555
556 END Update_Proc_PAY_WKR;
557
558 ----------------------------------------------------------------------------------------
559 -- Name : GET_SEQUENCE_VALUE
560 -- Function : TO get the sequence values for XLA Sequences .
561 ----------------------------------------------------------------------------------------
562
563 FUNCTION get_sequence_value(p_row_number in number,p_tab_name varchar2)
564 return number is
565 BEGIN
566
567 if p_tab_name='xla_transaction_entities' then
568 If p_row_number = 1 then
569 select xla_transaction_entities_s.nextval,
570 xla_events_s.nextval,
571 xla_ae_headers_s.nextval
572 into g_xla_tran_s,g_xla_event_s,g_xla_headers_s
573 from dual;
574 return g_xla_tran_s;
575 else
576 return g_xla_tran_s;
577 end if;
578 end if;
579
580 if p_tab_name='xla_events' then
581 return g_xla_event_s;
582 end if;
583
584 if p_tab_name='xla_ae_headers' then
585 return g_xla_headers_s;
586 end if;
587
588 END get_sequence_value;
589
590 ----------------------------------------------------------------------------------------
591 -- Name : GET_FULL_NAME
592 -- Function : TO get the Full Name from Assignment_Action_Id
593 ----------------------------------------------------------------------------------------
594
595 FUNCTION get_full_name(p_assignment_act_id in pay_assignment_actions.assignment_action_id%type,
596 p_eff_date in date)
597 return varchar2 is
598
599 Cursor csr_full_name(p_assnmnt_act_id in pay_assignment_actions.assignment_action_id%type,
600 p_eff_date in date)
601 is
602 select full_name
603 from per_people_f ppf,
604 per_assignments_f paf,
605 pay_assignment_actions paa
606 where paa.assignment_id=paf.assignment_id
607 and paf.person_id=ppf.person_id
608 and paa.assignment_action_id=p_assnmnt_act_id
609 and p_eff_date between ppf.effective_start_date and ppf.effective_end_date;
610
611 lv_full_name varchar2(240);
612 BEGIN
613
614 open csr_full_name(p_assignment_act_id,p_eff_date);
615 fetch csr_full_name into lv_full_name;
616 close csr_full_name;
617
618 return lv_full_name;
619 END get_full_name;
620
621
622 -------------------------------------------------------------------------------------
623 -- Name : Update_Payroll_Subledger
624 -- Type : Private
625 -- Function : To update Payroll Sub Ledger to SLA data model from Start ID
626 -- to End ID for Ledger (P_LEDGER_ID).
627 -- Pre-reqs :
628 -- Parameters :
629 -- IN : X_start_id in number
630 -- X_end_id in number
631 -- P_LEDGER_ID in varchar2
632 -- P_MGR_REQ_ID in varchar2
633 --
634 -- OUT : X_errbuf out NOCOPY varchar2,
635 -- X_retcode out NOCOPY varchar2
636 --
637 -- Notes : The Procedure is called from Update_Proc_PAY_WKR.
638 --
639 -- End of comments
640 -------------------------------------------------------------------------------------
641
642 PROCEDURE Update_Payroll_Subledger (
643 x_errbuf out nocopy varchar2,
644 x_retcode out nocopy varchar2,
645 x_start_id in number,
646 x_end_id in number,
647 p_ledger_id in varchar2,
648 p_mgr_req_id in varchar2,
649 p_debug_flag in varchar2
650 )
651 IS
652
653 l_module CONSTANT VARCHAR2(90) := 'PAY_SLA_UPDATE_PKG.Update_Payroll_Subledger';
654 lv_application_id CONSTANT number := 801;
655 l_upg_batch_id number;
656 lv_Error_msg varchar2(1000);
657 lv_application_name CONSTANT varchar2(10) := 'Payroll';
658 lv_debug_enabled boolean default FALSE;
659
660 Cursor csr_get_periods(p_start_id in pay_patch_status.id%type,
661 p_end_id in pay_patch_status.id%type,
662 p_process_name in varchar2,
663 p_mgr_req_id in varchar2)
664 is
665 select patch_number ledger_id,patch_name period_name
666 from pay_patch_status
667 where id between p_start_id and p_end_id
668 and process_type=p_process_name
669 and phase = p_process_name||p_mgr_req_id
670 and patch_number=p_ledger_id
671 and status is null;
672
673 Cursor csr_get_headers(p_ledger_id in gl_je_headers.ledger_id%type,
674 p_period_name in gl_je_headers.period_name%type,
675 p_app_name in varchar2)
676 is
677 select je_header_id
678 from gl_je_headers gjh
679 where gjh.period_name=p_period_name
680 and gjh.je_category=p_app_name
681 and gjh.ledger_id=p_ledger_id
682 and gjh.je_source=p_app_name
683 order by je_header_id;
684
685 BEGIN
686
687 if p_debug_flag = 'Y' then
688 lv_debug_enabled := TRUE;
689 end if;
690
691 If lv_debug_enabled then
692 fnd_file.put_line(fnd_file.log,'Enter :'||l_module);
693 fnd_file.put_line(fnd_file.log,'In P_LEDGER_ID :'|| P_LEDGER_ID);
694 fnd_file.put_line(fnd_file.log,'In X_START_ID :'|| to_char(X_start_id));
695 fnd_file.put_line(fnd_file.log,'In X_END_ID :'|| to_char(X_end_id));
696 fnd_file.put_line(fnd_file.log,'In P_MGR_REQ_ID :'|| to_char(p_mgr_req_id));
697 End if;
698
699 l_upg_batch_id := to_number(p_mgr_req_id);
700
701 g_xla_tran_s :=0;
702 g_xla_event_s :=0;
703 g_xla_headers_s :=0;
704
705 For j in csr_get_periods(X_start_id ,X_end_id,gv_process_name,to_char(p_mgr_req_id))
706 Loop
707
708 If lv_debug_enabled then
709 fnd_file.put_line(fnd_file.log,'Before upgrading ledger_id :'|| j.ledger_id);
710 fnd_file.put_line(fnd_file.log,'Before upgrading period_name :'|| j.period_name);
711 fnd_file.put_line(fnd_file.log,'Before upgrading gv_process_name :'|| gv_process_name);
712 End if;
713
714 Begin
715
716 If lv_debug_enabled then
717 fnd_file.put_line(fnd_file.log,'Before updating GL Tables ');
718 End if;
719
720 For l in csr_get_headers(j.ledger_id,j.period_name,lv_application_name)
721 Loop
722
723 update gl_je_headers
724 set je_from_sla_flag='Y'
725 where je_header_id=l.je_header_id;
726
727 update gl_je_lines
728 set gl_sl_link_id=XLA_GL_SL_LINK_ID_S.nextval ,
729 gl_sl_link_table='XLAJEL'
730 where je_header_id =l.je_header_id;
731
732 update gl_import_references gir
733 set gir.gl_sl_link_id = (select gl_sl_link_id from gl_je_lines gjl1
734 where gir.je_header_id = gjl1.je_header_id
735 and gir.je_line_num=gjl1.je_line_num),
736 gir.gl_sl_link_table ='XLAJEL'
737 where gir.je_header_id = l.je_header_id
738 and gir.je_line_num = (select je_line_num from gl_je_lines gjl
739 where gir.je_header_id = gjl.je_header_id
740 and gir.je_line_num=gjl.je_line_num);
741
742 End loop;
743
744 If lv_debug_enabled then
745 fnd_file.put_line(fnd_file.log,'After updating GL Tables ');
746 End if;
747
748 If lv_debug_enabled then
749 fnd_file.put_line(fnd_file.log,'Before Inserting into XLA Tables ');
750 End if;
751
752 INSERT ALL
753 WHEN (rank_id=1) then
754 INTO xla_transaction_entities (
755 upg_batch_id,
756 entity_id,
757 application_id,
758 ledger_id,
759 entity_code,
760 source_id_int_1,
761 source_id_char_1,
762 transaction_number,
763 creation_date,
764 created_by,
765 last_update_date,
766 last_updated_by,
767 last_update_login,
768 source_application_id,
769 upg_source_application_id)
770 VALUES (l_upg_batch_id,
771 xla_transaction_seq,
772 lv_application_id,
773 ledger_id,
774 'ASSIGNMENTS',
775 TGL_ASSIGNMENT_ACTION_ID,
776 To_char(EFFECTIVE_DATE,'YYYY/MM/DD'),
777 TGL_ASSIGNMENT_ACTION_ID,
778 creation_date,
779 created_by,
780 last_update_date,
781 last_updated_by,
782 last_update_login,
783 lv_application_id,
784 lv_application_id)
785 INTO xla_events (
786 upg_batch_id,
787 application_id,
788 entity_id,
789 event_id,
790 event_number,
791 event_type_code,
792 event_date,
793 event_status_code,
794 process_status_code,
795 on_hold_flag,
796 creation_date,
797 created_by,
798 last_update_date,
799 last_updated_by,
800 last_update_login,
801 transaction_date,
802 upg_source_application_id)
803 VALUES (l_upg_batch_id,
804 lv_application_id,
805 xla_transaction_seq,
806 xla_events_seq,
807 1,
808 EVENT_TYPE_CODE,
809 EFFECTIVE_DATE,
810 'P',
811 'P',
812 'N',
813 creation_date,
814 created_by,
815 last_update_date,
816 last_updated_by,
817 last_update_login,
818 EFFECTIVE_DATE,
819 lv_application_id)
820 INTO xla_ae_headers (
821 upg_batch_id,
822 application_id,
823 amb_context_code,
824 entity_id,
825 event_id,
826 event_type_code,
827 ae_header_id,
828 ledger_id,
829 je_category_name,
830 accounting_date,
831 period_name,
832 balance_type_code,
833 gl_transfer_status_code,
834 gl_transfer_date,
835 accounting_entry_status_code,
836 accounting_entry_type_code,
837 description,
838 creation_date,
839 created_by,
840 last_update_date,
841 last_updated_by,
842 last_update_login,
843 zero_amount_flag,
844 accrual_reversal_flag,
845 upg_source_application_id)
846 VALUES (l_upg_batch_id,
847 lv_application_id,
848 'DEFAULT',
849 xla_transaction_seq,
850 xla_events_seq,
851 EVENT_TYPE_CODE,
852 xla_ae_headers_seq,
853 ledger_id,
854 lv_application_name,
855 EFFECTIVE_DATE,
856 period_name,
857 'A',
858 'Y',
859 effective_date,
860 'F',
861 'STANDARD',
862 header_desc,
863 creation_date,
864 created_by,
865 last_update_date,
866 last_updated_by,
867 last_update_login,
868 'N',
869 'N',
870 lv_application_id)
871 INTO pay_xla_events(
872 event_id,
873 assignment_action_id,
874 accounting_date,
875 event_status)
876 VALUES(xla_events_seq,
877 TGL_ASSIGNMENT_ACTION_ID,
878 EFFECTIVE_DATE,
879 'P')
880 WHEN (1=1) then
881 INTO xla_ae_lines (
882 upg_batch_id,
883 application_id,
884 ae_header_id,
885 ae_line_num,
886 code_combination_id,
887 gl_transfer_mode_code,
888 description,
889 accounted_dr,
890 accounted_cr,
891 currency_code,
892 currency_conversion_date,
893 currency_conversion_rate,
894 currency_conversion_type,
895 entered_dr,
896 entered_cr,
897 accounting_class_code,
898 gl_sl_link_id,
899 gl_sl_link_table,
900 gain_or_loss_flag,
901 creation_date,
902 created_by,
903 last_update_date,
904 last_updated_by,
905 last_update_login,
906 accounting_date,
907 ledger_id,
908 mpa_accrual_entry_flag)
909 VALUES (l_upg_batch_id,
910 lv_application_id,
911 xla_ae_headers_seq,
912 rank_id,
913 code_combination_id,
914 'S',
915 line_desc,
916 accounted_dr,
917 accounted_cr,
918 currency_code,
919 currency_conversion_date,
920 currency_conversion_rate,
921 currency_conversion_type,
922 entered_dr,
923 entered_cr,
924 'COST',
925 link_id,
926 'XLAJEL',
927 'N',
928 creation_date,
929 created_by,
930 last_update_date,
931 last_updated_by,
932 last_update_login,
933 EFFECTIVE_DATE,
934 ledger_id,
935 'N')
936 INTO xla_distribution_links (
937 upg_batch_id,
938 application_id,
939 event_id,
940 ae_header_id,
941 ae_line_num,
942 source_distribution_type,
943 source_distribution_id_num_1,
944 merge_duplicate_code,
945 ref_ae_header_id,
946 temp_line_num,
947 event_class_code,
948 event_type_code)
949 VALUES (l_upg_batch_id,
950 lv_application_id,
951 xla_events_seq,
952 xla_ae_headers_seq,
953 rank_id,
954 action_type,
955 TGL_ASSIGNMENT_ACTION_ID,
956 'N',
957 xla_ae_headers_seq,
958 rank_id,
959 EVENT_CLASS_CODE,
960 DIS_EVENT_TYPE_CODE)
961 SELECT row_number() over(partition by tgl_assignment_action_id order by debit_or_credit) RANK_ID,
962 get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_transaction_entities') xla_transaction_seq,
963 get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_events') xla_events_seq,
964 get_sequence_value((row_number() over(partition by tgl_assignment_action_id order by debit_or_credit)),'xla_ae_headers') xla_ae_headers_seq,
965 ledger_id,
966 period_name,
967 je_header_id,
968 currency_code,
969 currency_conversion_date,
970 currency_conversion_rate,
971 currency_conversion_type,
972 effective_date,
973 tgl_assignment_action_id,
974 code_combination_id,
975 costing_assignment_action_id,
976 link_id,
977 cost_allocation_keyflex_id,
978 element_name,
979 debit_or_credit,
980 entered_dr,
981 entered_cr,
982 (entered_dr*currency_conversion_rate) accounted_dr,
983 (entered_cr*currency_conversion_rate) accounted_cr,
984 decode(action_type,'C','COST','CP','PAYMENT_COST','S','RETRO_COST') event_type_code,
985 decode(action_type,'C','COSTS_ALL','CP','PAYMENT_COSTS_ALL','COSTS_ALL') dis_event_type_code,
986 decode(action_type,'C','COSTS','CP','PAYMENT_COSTS','COSTS') event_class_code,
987 decode(action_type,'CP','Payment Cost for '||get_full_name(tgl_assignment_action_id,effective_date)||' on '||effective_date) header_desc,
988 decode(action_type,'C',debit_or_credit ||' Cost for '||Element_name,'CP',debit_or_credit||' payment cost') line_desc,
989 action_type,
990 creation_date,
991 created_by,
992 last_update_date,
993 last_updated_by,
994 last_update_login
995 From
996 (SELECT distinct gjh.ledger_id LEDGER_ID,
997 gjh.period_name PERIOD_NAME,
998 gjh.je_header_id JE_HEADER_ID,
999 gjh.currency_code,
1000 gjh.currency_conversion_date,
1001 gjh.currency_conversion_rate,
1002 gjh.currency_conversion_type,
1003 gjl.effective_date EFFECTIVE_DATE,
1004 pa.assignment_action_id TGL_ASSIGNMENT_ACTION_ID,
1005 gjl.code_combination_id CODE_COMBINATION_ID,
1006 gjl.gl_sl_link_id LINK_ID,
1007 gjl.creation_date,
1008 gjl.created_by,
1009 gjl.last_update_date,
1010 gjl.last_updated_by,
1011 gjl.last_update_login,
1012 pcv.assignment_action_id COSTING_ASSIGNMENT_ACTION_ID,
1013 pcv.cost_allocation_keyflex_id,
1014 pcv.element_name,
1015 pcv.debit_or_credit,
1016 decode(pcv.debit_or_credit,'Debit',pcv.costed_value,null) entered_dr,
1017 decode(pcv.debit_or_credit,'Credit',pcv.costed_value,null) entered_cr ,
1018 ppa1.action_type
1019 FROM pay_payroll_actions ppa1, -- Cost pay actions
1020 pay_assignment_actions pa1, -- Cost asg actions.
1021 pay_action_interlocks pi3, -- Cost - Run
1022 pay_action_interlocks pi1, -- Cost - Trans GL
1023 pay_all_payrolls_f pp,
1024 pay_action_classifications pac,
1025 pay_payroll_actions ppa2, -- Payroll run actions.
1026 pay_assignment_actions pa2, -- Payroll run asg actions.
1027 pay_action_interlocks pi2, -- Run - Trans GL
1028 pay_assignment_actions pa, -- Trans GL asg actions
1029 pay_payroll_actions ppa, -- Trans GL pay actions
1030 pay_costs_v pcv,
1031 gl_je_headers gjh,
1032 gl_je_lines gjl
1033 WHERE ppa.payroll_action_id = to_number(gjl.reference_1)
1034 AND pa.payroll_action_id = ppa.payroll_action_id
1035 AND pa.action_status = 'C'
1036 AND ppa2.payroll_action_id = to_number(gjl.reference_5)
1037 AND pcv.cost_allocation_keyflex_id=to_number(gjl.reference_2)
1038 AND pi2.locking_action_id = pa.assignment_action_id
1039 AND pa2.assignment_action_id = pi2.locked_action_id
1040 AND ppa2.payroll_action_id = pa2.payroll_action_id
1041 AND ppa2.consolidation_set_id +0 = ppa.consolidation_set_id
1042 AND pac.action_type = ppa2.action_type
1043 AND pac.classification_name = 'COSTED'
1044 AND pp.payroll_id = ppa2.payroll_id
1045 AND pi1.locking_action_id = pa.assignment_action_id
1046 AND pa1.assignment_action_id = pi1.locked_action_id
1047 AND pa1.assignment_action_id <> pa2.assignment_action_id
1048 AND pi3.locking_action_id = pa1.assignment_action_id
1049 AND pa2.assignment_action_id = pi3.locked_action_id
1050 AND ppa1.payroll_action_id = pa1.payroll_action_id
1051 AND ppa1.action_type in ('C','S')
1052 AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
1053 AND pcv.assignment_action_id= pa1.assignment_action_id
1054 AND gjl.je_header_id=gjh.je_header_id
1055 AND decode(gjl.entered_cr,0,'Debit','Credit')=pcv.debit_or_credit
1056 AND gjh.period_name=j.period_name
1057 AND gjh.je_category=lv_application_name
1058 AND gjh.ledger_id= j.ledger_id
1059 AND gjh.je_source=lv_application_name
1060 UNION
1061 SELECT distinct gjh.ledger_id LEDGER_ID,
1062 gjh.period_name PERIOD_NAME,
1063 gjh.je_header_id JE_HEADER_ID,
1064 gjh.currency_code,
1065 gjh.currency_conversion_date,
1066 gjh.currency_conversion_rate,
1067 gjh.currency_conversion_type,
1068 gjl.effective_date EFFECTIVE_DATE,
1069 pa.assignment_action_id TGL_ASSIGNMENT_ACTION_ID,
1070 gjl.code_combination_id CODE_COMBINATION_ID,
1071 gjl.gl_sl_link_id LINK_ID,
1072 gjl.creation_date,
1073 gjl.created_by,
1074 gjl.last_update_date,
1075 gjl.last_updated_by,
1076 gjl.last_update_login,
1077 ppc.assignment_action_id COSTING_ASSIGNMENT_ACTION_ID,
1078 ppc.gl_account_ccid,
1079 ppc.payment_method_name,
1080 ppc.debit_or_credit,
1081 decode(ppc.debit_or_credit,'Debit',to_number(ppc.costed_value),null) entered_dr,
1082 decode(ppc.debit_or_credit,'Credit',to_number(ppc.costed_value),null) entered_cr,
1083 ppa1.action_type
1084 FROM pay_payroll_actions ppa, -- Trans GL pay actions
1085 pay_assignment_actions pa, -- Trans GL asg actions
1086 pay_action_interlocks pi1, -- Cost - Trans GL
1087 pay_assignment_actions pa1, -- Cost asg actions
1088 pay_payroll_actions ppa1, -- Cost pay actions
1089 per_all_assignments_f pera,
1090 pay_all_payrolls_f pp,
1091 pay_payment_costs_v ppc,
1092 gl_je_headers gjh,
1093 gl_je_lines gjl
1094 WHERE ppa.payroll_action_id = to_number(gjl.reference_1)
1095 AND pa.payroll_action_id = ppa.payroll_action_id
1096 AND pi1.locking_action_id = pa.assignment_action_id
1097 AND pa1.assignment_action_id = pi1.locked_action_id
1098 AND ppa1.payroll_action_id = pa1.payroll_action_id
1099 AND ppa1.action_type = 'CP'
1100 AND pera.assignment_id = pa.assignment_id
1101 AND ppa1.effective_date BETWEEN pera.effective_start_date AND pera.effective_end_date
1102 AND pp.payroll_id = pera.payroll_id
1103 AND ppa.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
1104 AND ppc.gl_account_ccid =to_number(gjl.reference_2)
1105 AND ppc.assignment_action_id= pa1.assignment_action_id
1106 AND gjl.je_header_id=gjh.je_header_id
1107 AND decode(gjl.entered_cr,0,'Debit','Credit')=ppc.debit_or_credit
1108 AND gjh.period_name=j.period_name
1109 AND gjh.je_category=lv_application_name
1110 AND gjh.ledger_id= j.ledger_id
1111 AND gjh.je_source=lv_application_name) A;
1112
1113
1114 If lv_debug_enabled then
1115 fnd_file.put_line(fnd_file.log,'After Inserting into XLA Tables ');
1116 End if;
1117
1118 update pay_patch_status
1119 set status='U',
1120 description='Period Successfully Upgraded'
1121 where process_type=gv_process_name
1122 and phase =gv_process_name||P_MGR_REQ_ID
1123 and patch_number=j.ledger_id
1124 and patch_name =j.period_name;
1125
1126 commit;
1127
1128 If lv_debug_enabled then
1129 fnd_file.put_line(fnd_file.log,'Period '||j.period_name||' Successfully upgraded ');
1130 End if;
1131
1132
1133 Exception when others then
1134 Rollback;
1135
1136 lv_Error_msg :=SQLCODE||' '||SQLERRM;
1137
1138 update pay_patch_status
1139 set status='E',
1140 description='Error '||lv_Error_msg
1141 where process_type=gv_process_name
1142 and phase =gv_process_name||P_MGR_REQ_ID
1143 and patch_number=j.ledger_id
1144 and patch_name =j.period_name;
1145
1146 fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||j.period_name||' '||lv_Error_msg);
1147
1148 End;
1149
1150 If lv_debug_enabled then
1151 fnd_file.put_line(fnd_file.log,'After upgrading ledger_id :'|| j.ledger_id);
1152 fnd_file.put_line(fnd_file.log,'After upgrading period_name :'|| j.period_name);
1153 End if;
1154
1155 End Loop;
1156
1157 If lv_debug_enabled then
1158 fnd_file.put_line(fnd_file.log,'Leaving :'||l_module);
1159 End if;
1160
1161 EXCEPTION when others then
1162 Rollback;
1163 X_errbuf:=l_module||': '|| SQLERRM;
1164 fnd_file.put_line(FND_FILE.LOG,'Error Update_Payroll_Subledger '||SQLCODE||' '||SQLERRM);
1165 end Update_Payroll_Subledger;
1166
1167 END PAY_SLA_UPDATE_PKG;