DBA Data[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;