[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_UITE_REPORT_PKG
Source
1 PACKAGE BODY PAY_JP_UITE_REPORT_PKG AS
2 -- $Header: pyjpuirp.pkb 120.0.12010000.14 2010/06/02 12:33:20 mpothala noship $
3 -- **************************************************************************************************
4 -- * Copyright (c) Oracle Corporation Japan,2009 Product Development. *
5 -- * All rights reserved *
6 -- **************************************************************************************************
7 -- * *
8 -- * PROGRAM NAME *
9 -- * PAY_JP_UITE_REPORT_PKG.pks *
10 -- * *
11 -- * DESCRIPTION *
12 -- * This script creates the package body of PAY_JP_UITE_REPORT_PKG. *
13 -- * *
14 -- * USAGE *
15 -- * To Install sqlplus <apps_user>/<apps_pwd> @PAYJPUITEREPORTPKG.pkb *
16 -- * To Execute sqlplus <apps_user>/<apps_pwd> EXEC PAY_JP_UITE_REPORT_PKG<procedure name> *
17 -- * *
18 -- * PROGRAM LIST *
19 -- * ========== *
20 -- * NAME DESCRIPTION *
21 -- * ----------------- -------------------------------------------------- *
22 -- * RANGE_CURSOR *
23 -- * ACTION_CREATION *
24 -- * GEN_XML_HEADER *
25 -- * GENERATE_XML *
26 -- * PRINT_CLOB *
27 -- * GEN_XML_FOOTER *
28 -- * INIT_CODE *
29 -- * ARCHIVE_CODE *
30 -- * ASSACT_XML *
31 -- * GET_CP_XML *
32 -- * WRITETOCLOB *
33 -- * CALLED BY *
34 -- * Concurrent Program Japan, Terminated Employee Report *
35 -- * *
36 -- * LAST UPDATE DATE *
37 -- * Date the program has been modified for the last time *
38 -- * *
39 -- * HISTORY *
40 -- * ======= *
41 -- * *
42 -- * VERSION DATE AUTHOR(S) DESCRIPTION *
43 -- * ------- ----------- ---------------- ---------------------------- *
44 -- * Draft 17/02/2010 RDARASI intial *
45 -- * 120.0.12010000.8 11/05/2010 MPOTHALA Bug 9648106 *
46 -- * 120.0.12010000.9 14/05/2010 MPOTHALA Bug 9648106 *
47 -- * 120.0.12010000.10 14/05/2010 MPOTHALA Bug 9648106 *
48 -- * 120.0.12010000.11 21/05/2010 MPOTHALA Bug 9728602 *
49 -- * 120.0.12010000.12 26/05/2010 MPOTHALA Bug 9728572 *
50 -- * 120.0.12010000.13 26/05/2010 MPOTHALA Bug 9764235 *
51 -- * 120.0.12010000.14 02/06/2010 MPOTHALA Bug 9764235 *
52 -- **************************************************************************************************
53 --
54 g_write_xml CLOB;
55 g_xfdf_string CLOB;
56 gc_eol VARCHAR2(5) := fnd_global.local_chr(10);
57 gc_proc_name VARCHAR2(240);
58 gc_pkg_name VARCHAR2(30):= 'PAY_JP_UITE_REPORT_PKG.';
59 gb_debug BOOLEAN;
60 gn_dummy NUMBER := -99 ;
61 gn_all_exclusions_flag NUMBER;
62 gn_vctr NUMBER;
63 gc_exception EXCEPTION;
64 --
65 FUNCTION cnv_str( p_text IN VARCHAR2
66 , p_start IN NUMBER DEFAULT NULL
67 , p_end IN NUMBER DEFAULT NULL
68 )
69 RETURN VARCHAR2
70 --************************************************************************
71 -- FUNCTION
72 -- cnv_str
73 --
74 -- DESCRIPTION
75 -- This fucntion retunrs the string based on the start and end positions
76 -- from the given text
77 --
78 -- ACCESS
79 -- PUBLIC
80 --
81 -- PARAMETERS
82 -- ==========
83 -- NAME TYPE DESCRIPTION
84 -- ----------------- -------- ---------------------------------------
85 -- p_text IN This parameter passes Assignment Set Id
86 -- p_start IN This parameter passes Start Position
87 -- p_end IN This parameter passes End Position
88 --
89 -- PREREQUISITES
90 -- None
91 --
92 -- CALLED BY
93 -- None
94 --************************************************************************
95 IS
96 lc_text VARCHAR2(4000);
97 BEGIN
98 --
99 gb_debug := hr_utility.debug_enabled;
100 --
101 IF gb_debug
102 THEN
103 hr_utility.trace ('Entering CNV_STR');
104 END IF;
105 --
106 lc_text := LTRIM(RTRIM(REPLACE(p_text,TO_MULTI_BYTE(' '),' ')));
107 --
108 IF p_start IS NOT NULL
109 AND p_end IS NOT NULL THEN
110 lc_text := SUBSTR(lc_text,p_start,p_end);
111 END IF;
112 --
113 IF gb_debug THEN
114 hr_utility.trace ('Leaving CNV_STR');
115 END IF;
116 --
117 RETURN lc_text;
118 --
119 END cnv_str;
120 --
121 FUNCTION htmlspchar(p_text IN VARCHAR2)
122 RETURN VARCHAR2
123 --************************************************************************
124 -- FUNCTION
125 -- htmlspchar
126 --
127 -- DESCRIPTION
128 -- This fucntion retunrs the string based on the start and end positions
129 -- from the given text
130 --
131 -- ACCESS
132 -- PUBLIC
133 --
134 -- PARAMETERS
135 -- ==========
136 -- NAME TYPE DESCRIPTION
137 -- ----------------- -------- ---------------------------------------
138 -- p_text IN This parameter passes Assignment Set Id
139 --
140 -- PREREQUISITES
141 -- None
142 --
143 -- CALLED BY
144 -- None
145 --************************************************************************
146 IS
147 lc_htmlspchar VARCHAR2(1) := 'N';
148 BEGIN
149 --
150 gb_debug := hr_utility.debug_enabled;
151 --
152 IF gb_debug THEN
153 hr_utility.trace ('Entering htmlspchar');
154 END IF;
155 --
156 IF NVL(INSTR(p_text,'<'),0) > 0 THEN
157 lc_htmlspchar := 'Y';
158 END IF;
159 --
160 IF lc_htmlspchar = 'N'
161 AND NVL(INSTR(p_text,'>'),0) > 0 THEN
162 lc_htmlspchar := 'Y';
163 END IF;
164 --
165 IF lc_htmlspchar = 'N'
166 AND NVL(INSTR(p_text,'&'),0) > 0 THEN
167 lc_htmlspchar := 'Y';
168 END IF;
169 --
170 IF lc_htmlspchar = 'N'
171 AND NVL(INSTR(p_text,''''),0) > 0 THEN
172 lc_htmlspchar := 'Y';
173 END IF;
174 --
175 IF lc_htmlspchar = 'N'
176 AND NVL(INSTR(p_text,'"'),0) > 0 THEN
177 lc_htmlspchar := 'Y';
178 END IF;
179 --
180 IF lc_htmlspchar = 'Y' then
181 RETURN '<![CDATA['||p_text||']]>';
182 ELSE
183 RETURN p_text;
184 END IF;
185 --
186 END htmlspchar;
187 --
188 FUNCTION range_person_on
189 --************************************************************************
190 -- FUNCTION
191 -- range_person_on
192 --
193 -- DESCRIPTION
194 -- Checks if RANGE_PERSON_ID is enabled for
195 -- Archive process.
196 --
197 -- ACCESS
198 -- PRIVATE
199 --
200 -- PREREQUISITES
201 -- None
202 --
203 -- CALLED BY
204 -- action_creation
205 --************************************************************************
206 RETURN BOOLEAN
207 IS
208 --
209 CURSOR lcu_action_parameter
210 IS
211 SELECT parameter_value
212 FROM pay_action_parameters
213 WHERE parameter_name = 'RANGE_PERSON_ID';
214 --
215 lb_return BOOLEAN;
216 lc_action_param_val VARCHAR2(30);
217 --
218 BEGIN
219 --
220 gb_debug := hr_utility.debug_enabled;
221 --
222 IF gb_debug THEN
223 hr_utility.set_location('Entering range_person_on',10);
224 END IF;
225 --
226 OPEN lcu_action_parameter;
227 FETCH lcu_action_parameter INTO lc_action_param_val;
228 CLOSE lcu_action_parameter;
229 --
230 IF lc_action_param_val = 'Y' THEN
231 lb_return := TRUE;
232 IF gb_debug THEN
233 hr_utility.set_location('Range Person = True',10);
234 END IF;
235 ELSE
236 lb_return := FALSE;
237 END IF;
238 --
239 IF gb_debug THEN
240 hr_utility.set_location('Leaving range_person_on',10);
241 END IF;
242
243 RETURN lb_return;
244 --
245 EXCEPTION WHEN NO_DATA_FOUND THEN
246 IF gb_debug THEN
247 hr_utility.set_location('No Data Found Exception in range_person_on',10);
248 END IF;
249 lb_return := FALSE;
250 RETURN lb_return;
251 END range_person_on;
252
253 --
254 PROCEDURE get_numbers ( p_input IN NUMBER
255 , p_input1 IN VARCHAR2
256 , p_output OUT NOCOPY VARCHAR2
257 )
258 --************************************************************************
259 -- PROCEDURE
260 -- get_numbers
261 --
262 -- DESCRIPTION
263 -- This is used to split the values into 1 Character
264 --
265 -- ACCESS
266 -- PUBLIC
267 --
268 -- PARAMETERS
269 -- ==========
270 -- NAME TYPE DESCRIPTION
271 -- ----------------- -------- ------------------------------------
272 -- p_input IN
273 -- p_input1 IN
274 -- p_output OUT
275 --
276 -- PREREQUISITES
277 -- None
278 --
279 -- CALLED BY
280 -- None
281 --************************************************************************
282 AS
283 lc_proc_name VARCHAR2(100);
284 ln_input_num NUMBER;
285 lc_input_value VARCHAR2(100);
286 lc_output_value VARCHAR2(4000);
287 BEGIN
288 --
289 lc_proc_name := gc_pkg_name ||'GET_NUMBERS';
290 gb_debug := hr_utility.debug_enabled;
291 --
292 IF gb_debug THEN
293 hr_utility.trace ('Entering '||lc_proc_name);
294 END IF;
295
296 lc_input_value := p_input1;
297 ln_input_num := p_input;
298 lc_output_value := NULL;
299
300 IF (lc_input_value IS NOT NULL) THEN
301
302 FOR i in 1 .. LENGTH(lc_input_value)
303 LOOP
304 lc_output_value := lc_output_value || '<P'||ln_input_num||'-'||i||'>'||htmlspchar(cnv_str(SUBSTR(lc_input_value,i,1)))||'</P'||ln_input_num||'-'||i||'>'||gc_eol;
305
306 END LOOP;
307 ELSE
308 FOR i in 1 .. 11
309 LOOP
310 lc_output_value := lc_output_value || '<P'||ln_input_num||'-'||i||'></P'||ln_input_num||'-'||i||'>'||gc_eol;
311
312 END LOOP;
313
314 END IF;
315
316 p_output := lc_output_value;
317 --
318 IF gb_debug THEN
319 hr_utility.trace ('Leaving '||lc_proc_name);
320 END IF;
321 --
322 END get_numbers;
323 --
324 PROCEDURE initialize(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE )
325 --***************************************************************************
326 -- PROCEDURE
327 -- initialize
328 -- DESCRIPTION
329 -- This procedure is used to set global contexts
330 --
331 -- ACCESS
332 -- PUBLIC
333 --
334 -- PARAMETERS
335 -- ==========
336 -- NAME TYPE DESCRIPTION
337 -- ----------------- -------- ---------------------------------------
338 -- p_payroll_action_id IN This parameter passes Payroll Action Id
339 --
340 -- PREREQUISITES
341 -- None
342 --
343 -- CALLED BY
344 -- action_creation
345 --*************************************************************************
346 IS
347 --
348 CURSOR lcr_params(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
349 IS
350 SELECT pay_core_utils.get_parameter('PAYROLL_ACTION_ID',legislative_parameters) payroll_action_id
351 ,pay_core_utils.get_parameter('ASSETID',legislative_parameters) assignment_set_id
352 ,pay_core_utils.get_parameter('REP_GROUP',legislative_parameters) rep_group
353 ,pay_core_utils.get_parameter('REP_CAT',legislative_parameters) rep_cat
354 ,pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id
355 ,pay_core_utils.get_parameter('SUB',legislative_parameters) subject_year
356 ,TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') effective_date
357 ,pay_core_utils.get_parameter('PAY_ARCH',legislative_parameters) payroll_arch
358 ,pay_core_utils.get_parameter('SORT1',legislative_parameters) sort_order
359 FROM pay_payroll_actions PPA
360 WHERE PPA.payroll_action_id = p_payroll_action_id;
361 --
362 -- Local Variables
363 lc_procedure VARCHAR2(200);
364 --
365 BEGIN
366 --
367 gb_debug :=hr_utility.debug_enabled ;
368 IF gb_debug THEN
369 lc_procedure := gc_pkg_name||'initialize';
370 hr_utility.set_location('Entering '||lc_procedure,1);
371 END IF;
372 --
373 -- Fetch the parameters passed by user into global variable.
374 --
375 OPEN lcr_params(p_payroll_action_id);
376 FETCH lcr_params into gr_parameters;
377 CLOSE lcr_params;
378
379 -- ADDED By rdarasi above
380 --
381 IF gb_debug THEN
382 hr_utility.set_location('p_payroll_action_id = ' || p_payroll_action_id,30);
383 hr_utility.set_location('gr_parameters.payroll_action_id = ' || gr_parameters.payroll_action_id,30);
384 hr_utility.set_location('gr_parameters.ass_setid = ' || gr_parameters.ass_setid,30);
385 hr_utility.set_location('gr_parameters.rep_group = ' || gr_parameters.rep_group,30);
386 hr_utility.set_location('gr_parameters.rep_cat = ' || gr_parameters.rep_cat,30);
387 hr_utility.set_location('gr_parameters.business_group_id = ' || gr_parameters.business_group_id,30);
388 hr_utility.set_location('gr_parameters.subject_year = ' || gr_parameters.subject_year,30);
389 hr_utility.set_location('gr_parameters.effective_date = ' || gr_parameters.effective_date,30);
390 hr_utility.set_location('gr_parameters.payroll_arch = ' || gr_parameters.payroll_arch,30);
391 hr_utility.set_location('gr_parameters.sort_order = ' || gr_parameters.sort_order,30);
392 END IF;
393 --
394 IF gb_debug THEN
395 hr_utility.set_location('Leaving '||lc_procedure,1000);
396 END IF;
397 --
398 EXCEPTION
399 WHEN gc_exception THEN
400 IF gb_debug THEN
401 hr_utility.set_location('Error in '||lc_procedure,999999);
402 END IF;
403 RAISE;
404 WHEN OTHERS THEN
405 RAISE gc_exception;
406 END initialize;
407 --
408 PROCEDURE gen_xml_header
409 --************************************************************************
410 -- PROCEDURE
411 -- gen_xml_header
412 --
413 -- DESCRIPTION
414 -- This procedure generates XML header information and appends to
415 -- pay_mag_tape.g_clob_value
416 --
417 -- ACCESS
418 -- PUBLIC
419 --
420 -- PARAMETERS
421 -- ==========
422 -- None
423 --
424 -- PREREQUISITES
425 -- None
426 --
427 -- CALLED BY
428 -- None
429 --************************************************************************
430 AS
431 lc_proc_name VARCHAR2(100);
432 lc_buf VARCHAR2(2000);
433 --
434 BEGIN
435 --
436 gb_debug := hr_utility.debug_enabled;
437 --
438 IF gb_debug THEN
439 lc_proc_name := gc_pkg_name || 'GEN_XML_HEADER';
440 hr_utility.trace ('Entering '||lc_proc_name);
441 END IF ;
442 --
443 vxmltable.DELETE;
444 --
445
446 lc_buf := gc_eol ||'<ROOT>'||gc_eol ;
447 --
448 pay_core_files.write_to_magtape_lob(lc_buf);
449 --
450 IF gb_debug THEN
451 hr_utility.trace ('CLOB contents after appending header information');
452 hr_utility.trace ('Leaving '||lc_proc_name);
453 END IF ;
454 --
455 END gen_xml_header;
456 --
457 PROCEDURE gen_xml_footer
458 --************************************************************************
459 -- PROCEDURE
460 -- gen_xml_footer
461 --
462 -- DESCRIPTION
463 -- This procedure generates XML Footer information and appends to
464 -- pay_mag_tape.g_clob_value
465 --
466 -- ACCESS
467 -- PUBLIC
468 --
469 -- PARAMETERS
470 -- ==========
471 -- None
472 --
473 -- PREREQUISITES
474 -- None
475 --
476 -- CALLED BY
477 -- None
478 --************************************************************************
479 AS
480 lc_buf VARCHAR2(2000) ;
481 lc_proc_name VARCHAR2(100);
482 --
483 BEGIN
484 --
485 gb_debug := hr_utility.debug_enabled;
486 --
487 IF gb_debug THEN
488 lc_proc_name := gc_pkg_name || 'GEN_XML_FOOTER';
489 hr_utility.trace ('Entering '||lc_proc_name);
490 END IF ;
491 lc_buf := '</ROOT>' ;
492 --
493 pay_core_files.write_to_magtape_lob(lc_buf);
494 --
495 IF gb_debug THEN
496 hr_utility.trace ('CLOB contents after appending footer information');
497 hr_utility.trace ('Leaving '||lc_proc_name);
498 END IF ;
499 --
500 END gen_xml_footer;
501 --
502 PROCEDURE generate_xml
503 --************************************************************************
504 -- PROCEDURE
505 -- generate_xml
506 --
507 -- DESCRIPTION
508 -- This procedure fetches archived data, converts it to XML
509 -- format and appends to pay_mag_tape.g_clob_value.
510 --
511 -- ACCESS
512 -- PUBLIC
513 --
514 -- PARAMETERS
515 -- ==========
516 -- None
517 --
518 -- PREREQUISITES
519 -- None
520 --
521 -- CALLED BY
522 -- None
523 --************************************************************************
524 AS
525 l_final_xml_string CLOB;
526 lc_xml_string1 VARCHAR2(2000);
527 lc_proc_name VARCHAR2(60);
528 lc_legislative_parameters VARCHAR(2000);
529 ln_old_assact_id NUMBER;
530 ln_pact_id NUMBER;
531 ln_cur_pact NUMBER;
532 ln_cur_assact NUMBER ;
533 ln_offset NUMBER;
534 ln_amount NUMBER;
535 --
536 BEGIN
537 --
538 gb_debug := hr_utility.debug_enabled;
539 --
540 IF gb_debug THEN
541 lc_proc_name := gc_pkg_name || 'GENERATE_XML';
542 hr_utility.trace ('Entering '||lc_proc_name);
543 END IF ;
544 --
545 ln_cur_assact := pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID' );
546 ln_cur_pact := pay_magtape_generic.get_parameter_value ('TRANSFER_PAYROLL_ACTION_ID' );
547 --
548 SELECT legislative_parameters
549 INTO lc_legislative_parameters
550 FROM pay_payroll_actions
551 WHERE payroll_action_id = ln_cur_pact;
552 --
553 ln_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAY_ARCH',lc_legislative_parameters));
554
555 hr_utility.set_location ('ln_cur_assact.. '||ln_cur_assact,20);
556 hr_utility.set_location ('ln_cur_pact... '||ln_cur_pact,20);
557 hr_utility.set_location ('ln_pact_id .. '||ln_pact_id,20);
558 --
559 SELECT PAA1.assignment_action_id
560 INTO ln_old_assact_id
561 FROM pay_assignment_actions PAA,
562 pay_assignment_actions PAA1
563 WHERE PAA.assignment_action_id = ln_cur_assact
564 AND PAA.assignment_id = PAA1.assignment_id
565 AND PAA1.payroll_action_id = ln_pact_id;
566 --
567 hr_utility.set_location ('ln_old_assact_id ..'||ln_old_assact_id,20);
568 get_cp_xml(ln_old_assact_id, l_final_xml_string);
569 --
570 ln_offset := 1 ;
571 ln_amount := 500;
572
573 --
574 LOOP
575 lc_xml_string1 := NULL;
576 dbms_lob.read(l_final_xml_string,ln_amount,ln_offset,lc_xml_string1);
577 pay_core_files.write_to_magtape_lob(lc_xml_string1);
578 ln_offset := ln_offset + ln_amount ;
579 END LOOP;
580 --
581 EXCEPTION
582 WHEN NO_DATA_FOUND THEN
583 IF gb_debug THEN
584 hr_utility.set_location ('Leaving '||lc_proc_name,20);
585 END IF ;
586 WHEN gc_exception THEN
587 IF gb_debug THEN
588 hr_utility.set_location('Error in '||lc_proc_name,999999);
589 hr_utility.set_location('sqleerm ' || SQLERRM,20);
590 END IF ;
591 hr_utility.raise_error;
592 RAISE;
593 WHEN OTHERS THEN
594 RAISE gc_exception;
595 --
596 END generate_xml;
597 --
598 PROCEDURE range_cursor( p_payroll_action_id IN NUMBER
599 , p_sqlstr OUT NOCOPY VARCHAR2
600 )
601 --************************************************************************
602 -- PROCEDURE
603 -- range_cursor
604 --
605 -- DESCRIPTION
606 -- This procedure defines a SQL statement to fetch all the people to be
607 -- included in the report.This SQL statement is used to define the
608 -- 'chunks' for multi-threaded operation.
609 --
610 -- ACCESS
611 -- PUBLIC
612 --
613 -- PARAMETERS
614 -- ==========
615 -- NAME TYPE DESCRIPTION
616 -- ----------------- -------- ------------------------------------
617 -- p_payroll_action_id IN This parameter passes payroll_action_id object
618 -- p_sqlstr OUT This parameter returns the SQL Statement
619 --
620 -- PREREQUISITES
621 -- None
622 --
623 -- CALLED BY
624 -- None
625 --************************************************************************
626 AS
627 lc_proc_name VARCHAR2(100);
628 BEGIN
629 --
630 lc_proc_name := gc_pkg_name ||'RANGE_CURSOR';
631 gb_debug := hr_utility.debug_enabled;
632 --
633 IF gb_debug THEN
634 hr_utility.trace ('Entering '||lc_proc_name);
635 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
636 END IF;
637 --
638 p_sqlstr := ' select distinct p.person_id'||
639 ' from per_people_f p,'||
640 ' pay_payroll_actions pa'||
641 ' where pa.payroll_action_id = :payroll_action_id'||
642 ' and p.business_group_id = pa.business_group_id'||
643 ' order by p.person_id ';
644 --
645 g_mag_payroll_action_id := P_PAYROLL_ACTION_ID;
646 --
647 IF gb_debug THEN
648 hr_utility.trace ('Range cursor query : ' || p_sqlstr);
649 hr_utility.trace ('Leaving '||lc_proc_name);
650 END IF;
651 --
652 END range_cursor;
653
654 PROCEDURE action_creation( p_payroll_action_id IN NUMBER
655 , p_start_person_id IN NUMBER
656 , p_end_person_id IN NUMBER
657 , p_chunk IN NUMBER
658 )
659 --************************************************************************
660 -- PROCEDURE
661 -- action_creation
662 --
663 -- DESCRIPTION
664 -- This procedure defines a SQL statement to fetch all the people to be
665 -- included in the report.This SQL statement is used to define the
666 -- 'chunks' for multi-threaded operation.
667 --
668 -- ACCESS
669 -- PUBLIC
670 --
671 -- PARAMETERS
672 -- ==========
673 -- NAME TYPE DESCRIPTION
674 -- ----------------- -------- ------------------------------------
675 -- p_payroll_action_id IN This parameter passes Payroll Action ID
676 -- p_start_person_id IN This parameter passes Start Person ID
677 -- p_end_person_id IN This parameter passes End Person ID
678 -- p_chunk IN This parameter passes Chunk value
679 --
680 -- PREREQUISITES
681 -- None
682 --
683 -- CALLED BY
684 -- None
685 --************************************************************************
686 AS
687 CURSOR lcu_assact_r( p_payroll_action_id_arch pay_payroll_actions.payroll_action_id%TYPE
688 , p_business_group_id per_assignments_f.business_group_id%TYPE
689 )
690 IS
691 SELECT DISTINCT PJUIEV.assignment_id
692 ,PJUIEV.termination_date
693 FROM per_assignments_f PAA
694 ,per_people_f PAP
695 ,pay_assignment_actions PAS
696 ,pay_jp_uite_emp_v PJUIEV
697 ,per_periods_of_service PPOF
698 ,pay_population_ranges PPR
699 ,pay_payroll_actions PPA
700 ,hr_all_organization_units HAOU
701 WHERE PAA.person_id = PAP.person_id
702 AND PPA.payroll_action_id = PPR.payroll_action_id
703 AND PPR.person_id = PAP.person_id
704 AND PAS.assignment_id = PAA.assignment_id
705 AND HAOU.organization_id = PAA.organization_id
706 AND PPOF.person_id = PAP.person_id
707 AND PJUIEV.assignment_action_id = PAS.assignment_action_id
708 AND PJUIEV.assignment_id = PAS.assignment_id
709 AND PPA.payroll_action_id = p_payroll_action_id
710 AND PPR.chunk_number = p_chunk
711 AND PAS.payroll_action_id = p_payroll_action_id_arch
712 AND PAA.business_group_id = p_business_group_id
713 AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAP.effective_start_date
714 AND PAP.effective_end_date
715 AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAA.effective_start_date
716 AND PAA.effective_end_date; -- Bug 9728577
717
718
719 --
720 CURSOR lcu_assact( p_payroll_action_id_arch pay_payroll_actions.payroll_action_id%TYPE
721 , p_business_group_id per_assignments_f.business_group_id%TYPE
722 )
723 IS
724 SELECT DISTINCT PJUIEV.assignment_id
725 ,PJUIEV.termination_date
726 FROM per_assignments_f PAA
727 ,per_people_f PAP
728 ,pay_assignment_actions PAS
729 ,pay_jp_uite_emp_v PJUIEV
730 ,per_periods_of_service PPOF
731 ,hr_all_organization_units HAOU
732 WHERE PAA.person_id = PAP.person_id
733 AND PAA.person_id BETWEEN p_start_person_id
734 AND p_end_person_id
735 AND PAS.assignment_id = PAA.assignment_id
736 AND PAS.payroll_action_id = p_payroll_action_id_arch
737 AND PPOF.person_id = PAP.person_id
738 AND PJUIEV.assignment_action_id = PAS.assignment_action_id
739 AND PJUIEV.assignment_id = PAS.assignment_id
740 AND HAOU.organization_id = PAA.organization_id
741 AND PAA.business_group_id = p_business_group_id
742 AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAP.effective_start_date
743 AND PAP.effective_end_date
744 AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAA.effective_start_date
745 AND PAA.effective_end_date; -- Bug 9728577
746
747 --
748 ln_assact pay_assignment_actions.assignment_action_id%TYPE ;
749 lc_proc_name VARCHAR2(60);
750 lc_legislative_parameters VARCHAR2(2000);
751 ln_old_pact_id NUMBER;
752 ln_ass_set_id NUMBER;
753 lc_include_flag VARCHAR2(1);
754 --
755 BEGIN
756 gb_debug := hr_utility.debug_enabled;
757 --
758 IF gb_debug
759 THEN
760 hr_utility.trace('Entering ACTION_CREATION');
761 END IF;
762 --
763 SELECT legislative_parameters
764 INTO lc_legislative_parameters
765 FROM pay_payroll_actions
766 WHERE payroll_action_id = p_payroll_action_id;
767 --
768 ln_old_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAY_ARCH',lc_legislative_parameters));
769 ln_ass_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSETID',lc_legislative_parameters));
770 --
771 g_mag_payroll_action_id:=p_payroll_action_id;
772 gb_debug :=hr_utility.debug_enabled ;
773 --
774
775 IF gb_debug THEN
776 lc_proc_name := gc_pkg_name ||'ACTION_CREATION';
777 hr_utility.trace ('Entering '||lc_proc_name);
778 hr_utility.trace ('Parameters ....');
779 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
780 hr_utility.trace ('P_START_PERSON_ID = '|| p_start_person_id);
781 hr_utility.trace ('P_END_PERSON_ID = '|| p_end_person_id);
782 hr_utility.trace ('P_CHUNK = '|| p_chunk);
783 END IF;
784 --
785 initialize(g_mag_payroll_action_id);
786 --
787 --
788 IF range_person_on THEN
789 -- Range person is enabled
790 IF gb_debug THEN
791 hr_utility.set_location('Inside Range person if condition',20);
792 END IF;
793 -- Assignment Action for Current and Terminated Employees
794 FOR lr_assact IN lcu_assact_r( ln_old_pact_id
795 , gr_parameters.business_group_id
796 )
797 LOOP
798 -- Added NVL to overcome NULL issue.
799 --
800 IF (NVL(ln_ass_set_id ,0) = 0) THEN
801 -- NO assignment set passed as parameter
802 hr_utility.trace ('ass_id = '||lr_assact.assignment_id);
803 --
804 SELECT pay_assignment_actions_s.nextval
805 INTO ln_assact
806 FROM dual;
807 hr_nonrun_asact.insact (ln_assact
808 ,lr_assact.assignment_id
809 ,p_payroll_action_id
810 ,p_chunk
811 ,NULL
812 );
813 ELSE
814 -- assignment set is passed as parameter
815 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => ln_ass_set_id
816 ,p_assignment_id => lr_assact.assignment_id
817 ,p_effective_date => lr_assact.termination_date
818 ,p_populate_fs_flag => 'Y' -- added for the bug #9652329
819 );
820 IF lc_include_flag = 'Y' THEN
821 --
822 SELECT pay_assignment_actions_s.nextval
823 INTO ln_assact
824 FROM dual;
825 hr_nonrun_asact.insact (ln_assact
826 ,lr_assact.assignment_id
827 ,p_payroll_action_id
828 ,p_chunk
829 ,NULL
830 );
831
832 END IF;
833 END IF;
834 END LOOP;-- End loop for assignment details cursor
835 ELSE
836 -- Range person is not enabled
837 IF gb_debug THEN
838 hr_utility.set_location('Range person returns false',20);
839 hr_utility.set_location(ln_old_pact_id,20);
840 END IF;
841 -- Assignment Action for Current and Terminated Employe
842 FOR lr_assact IN lcu_assact ( ln_old_pact_id
843 , gr_parameters.business_group_id
844 )
845 LOOP
846 -- Added NVL to overcome NULL issue.
847 --
848 IF (NVL(ln_ass_set_id ,0) = 0) THEN
849 -- NO assignment set passed as parameter
850 hr_utility.trace ('ass_id = '||lr_assact.assignment_id);
851 --
852 SELECT pay_assignment_actions_s.nextval
853 INTO ln_assact
854 FROM dual;
855 hr_nonrun_asact.insact( ln_assact
856 ,lr_assact.assignment_id
857 ,p_payroll_action_id
858 ,p_chunk
859 ,NULL
860 );
861 ELSE
862 -- assignment set is passed as parameter
863 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate (p_assignment_set_id => ln_ass_set_id
864 ,p_assignment_id => lr_assact.assignment_id
865 ,p_effective_date => lr_assact.termination_date
866 );
867 IF lc_include_flag = 'Y' THEN
868 SELECT pay_assignment_actions_s.nextval
869 INTO ln_assact
870 FROM dual;
871 hr_nonrun_asact.insact( ln_assact
872 ,lr_assact.assignment_id
873 ,p_payroll_action_id
874 ,p_chunk
875 ,NULL
876 );
877 END IF;
878
879 END IF;
880 END LOOP;-- End loop for assignment details cursor
881 END IF; -- End If for range_person_on
882 --
883 IF gb_debug
884 THEN
885 hr_utility.trace('Leaving ACTION_CREATION');
886 END IF;
887 --
888 END action_creation;
889
890 --
891 PROCEDURE init_code ( p_payroll_action_id IN NUMBER)
892 --************************************************************************
893 -- PROCEDURE
894 -- init_code
895 --
896 -- DESCRIPTION
897 -- None
898 --
899 -- ACCESS
900 -- PUBLIC
901 --
902 -- PARAMETERS
903 -- ==========
904 -- NAME TYPE DESCRIPTION
905 -- ----------------- -------- ------------------------------------
906 -- p_payroll_action_id IN This parameter passes Payroll Action ID
907 --
908 -- PREREQUISITES
909 -- None
910 --
911 -- CALLED BY
912 -- None
913 --************************************************************************
914 IS
915 BEGIN
916 gb_debug := hr_utility.debug_enabled;
917 --
918 IF gb_debug THEN
919 hr_utility.trace ('inside INIT_CODE ');
920 END IF;
921 --
922 g_mag_payroll_action_id := p_payroll_action_id;
923 --
924 END init_code;
925 --
926 PROCEDURE archive_code ( p_assignment_action_id IN NUMBER
927 , p_effective_date IN DATE
928 )
929 --************************************************************************
930 -- PROCEDURE
931 -- archive_code
932 --
933 -- DESCRIPTION
934 -- None
935 --
936 -- ACCESS
937 -- PUBLIC
938 --
939 -- PARAMETERS
940 -- ==========
941 -- NAME TYPE DESCRIPTION
942 -- ----------------- -------- -----------------------------------
943 -- p_assignment_action_id IN This parameter passes Payroll Action ID
944 -- p_effective_date IN This parameter passes Effective Date
945 --
946 -- PREREQUISITES
947 -- None
948 --
949 -- CALLED BY
950 -- None
951 --************************************************************************
952 IS
953 BEGIN
954 --
955 gb_debug := hr_utility.debug_enabled;
956 --
957 IF gb_debug
958 THEN
959 hr_utility.trace ('Inside ARCHIVE_CODE ');
960 END IF;
961 --
962 END archive_code;
963 --
964 --
965 PROCEDURE assact_xml(p_assignment_action_id IN NUMBER)
966 --************************************************************************
967 -- PROCEDURE
968 -- assact_xml
969 --
970 -- DESCRIPTION
971 -- This procedure creates xml for the assignment_action_id passed
972 -- as parameter. It then writes the xml into vXMLTable.
973 --
974 -- ACCESS
975 -- PUBLIC
976 --
977 -- PARAMETERS
978 -- ==========
979 -- NAME TYPE DESCRIPTION
980 -- ----------------- -------- -----------------------------------
981 -- p_assignment_action_id IN This parameter passes Payroll Action ID
982 --
983 -- PREREQUISITES
984 -- None
985 --
986 -- CALLED BY
987 -- None
988 --************************************************************************
989 IS
990 --Cursor to pick the employee details
991
992 CURSOR cur_uite_emp(p_mag_asg_action_id NUMBER)
993 IS
994 SELECT PJUIEV.employee_number
995 , PJUIEV.insured_number INSURED_NUMBER
996 , PJUIEV.last_name_kana
997 , PJUIEV.first_name_kana
998 , PJUIEV.last_name
999 , PJUIEV.first_name
1000 , TO_CHAR(PJUIEV.termination_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE
1001 , TO_CHAR(PJUIEV.termination_date, 'YY', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_YY
1002 , TO_CHAR(PJUIEV.termination_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_MM
1003 , TO_CHAR(PJUIEV.termination_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_DD
1004 , TO_CHAR(PJUIEV.termination_date + 1, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') FOLLOWING_DATE_TERM
1005 , TO_CHAR(PJUIEV.termination_date + 1, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') FOLLOWING_DATE_TERM_MM
1006 , TO_CHAR(PJUIEV.termination_date + 1, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') FOLLOWING_DATE_TERM_DD
1007 , SUBSTR(PJUIEV.postal_code,1,3)||NVL2(PJUIEV.postal_code,'-',' ')|| SUBSTR(PJUIEV.postal_code,4) POSTAL_CODE -- changed as per the bug#9648063
1008 , PJUIEV.address_line1
1009 , PJUIEV.address_line2
1010 , PJUIEV.address_line3
1011 , PJUIEV.phone_number
1012 FROM pay_jp_uite_emp_v PJUIEV
1013 WHERE PJUIEV.assignment_action_id = p_mag_asg_action_id;
1014
1015 CURSOR cur_uite_office(p_payroll_action_id NUMBER)
1016 IS
1017 SELECT PJUIOV.location_number LOCATION_NUMBER
1018 , PJUIOV.location_name
1019 , PJUIOV.loc_address_line1
1020 , PJUIOV.loc_address_line2
1021 , PJUIOV.loc_address_line3
1022 , PJUIOV.loc_phone_number
1023 , PJUIOV.employer_full_name
1024 , PJUIOV.employer_address_line1
1025 , PJUIOV.employer_address_line2
1026 , PJUIOV.employer_address_line3
1027 , PJUIOV.employer_name -- Added as per the bug#9648053
1028 FROM pay_jp_uite_office_v PJUIOV
1029 WHERE PJUIOV.payroll_action_id = p_payroll_action_id;
1030
1031 CURSOR cur_uite_sal(p_mag_asg_action_id NUMBER)
1032 IS
1033 SELECT TO_CHAR(PJUISV.payment_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') PAYMENT_DATE
1034 , TO_CHAR(PJUISV.ins_period_start_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_START_DATE
1035 , TO_CHAR(PJUISV.ins_period_start_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_START_DATE_MM
1036 , TO_CHAR(PJUISV.ins_period_start_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_START_DATE_DD
1037 , TO_CHAR(PJUISV.ins_period_end_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_END_DATE
1038 , TO_CHAR(PJUISV.ins_period_end_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_END_DATE_MM
1039 , TO_CHAR(PJUISV.ins_period_end_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_END_DATE_DD
1040 , PJUISV.ins_period_base_days
1041 , TO_CHAR(PJUISV.pay_period_start_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_START_DATE
1042 , TO_CHAR(PJUISV.pay_period_start_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_START_DATE_MM
1043 , TO_CHAR(PJUISV.pay_period_start_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_START_DATE_DD
1044 , TO_CHAR(PJUISV.pay_period_end_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_END_DATE
1045 , TO_CHAR(PJUISV.pay_period_end_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_END_DATE_MM
1046 , TO_CHAR(PJUISV.pay_period_end_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_END_DATE_DD
1047 , PJUISV.pay_period_base_days
1048 , PJUISV.wage_amount_a
1049 , PJUISV.wage_amount_b
1050 , PJUISV.wage_amount_total
1051 , PJUISV.remarks
1052 , PJUISV.exclude_period
1053 , PJUISV.line_number
1054 FROM pay_jp_uite_sal_v PJUISV
1055 WHERE PJUISV.assignment_action_id = p_mag_asg_action_id
1056 AND PJUISV.exclude_period = 'N'
1057 ORDER BY PJUISV.line_number; -- Added for the Bug# 9648278
1058
1059 CURSOR cur_uite_term(p_mag_asg_action_id NUMBER)
1060 IS
1061 SELECT PJUITV.termination_reason
1062 , PJUITV.concrete_circumstance1
1063 , PJUITV.concrete_circumstance2
1064 FROM pay_jp_uite_term_v PJUITV
1065 WHERE PJUITV.assignment_action_id = p_mag_asg_action_id;
1066
1067 CURSOR cur_uite_wage(p_mag_asg_action_id NUMBER)
1068 IS
1069 SELECT PJUIIV.wage_instruction1
1070 , PJUIIV.wage_instruction2
1071 FROM PAY_JP_UITE_INSTR_V PJUIIV
1072 WHERE PJUIIV.assignment_action_id = p_mag_asg_action_id;
1073
1074 --
1075 lr_uite_emp cur_uite_emp%ROWTYPE;
1076 lr_uite_office cur_uite_office%ROWTYPE;
1077 lr_uite_sal cur_uite_sal%ROWTYPE;
1078 lr_uite_term cur_uite_term%ROWTYPE;
1079 lr_uite_wage cur_uite_wage%ROWTYPE;
1080
1081 --Variables-----
1082 l_xml CLOB;
1083 l_xml2 CLOB;
1084 l_common_xml CLOB;
1085 l_emp_xml CLOB;
1086 l_sal_xml CLOB;
1087 l_term_xml CLOB;
1088 l_wage_xml CLOB;
1089 l_xml_begin VARCHAR2(200);
1090 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1091 seque NUMBER;
1092 lc_insured_number VARCHAR2(4000);
1093 lc_location_number VARCHAR2(4000);
1094 ln_termination_value NUMBER;
1095 ln_row_count NUMBER:=0; --bug 9764235
1096 --
1097 BEGIN
1098 --
1099 gb_debug := hr_utility.debug_enabled;
1100 --
1101 IF gb_debug THEN
1102 hr_utility.trace ('Entering assact_xml');
1103 END IF;
1104 --
1105 vXMLTable.DELETE;
1106 gn_vctr := 0;
1107
1108 --
1109 l_mag_asg_action_id := p_assignment_action_id;
1110 --
1111 initialize(g_mag_payroll_action_id);
1112 --
1113 -- Fetching the employee details.
1114 OPEN cur_uite_emp(l_mag_asg_action_id);
1115 FETCH cur_uite_emp INTO lr_uite_emp;
1116
1117 --
1118 IF (cur_uite_emp%FOUND) THEN
1119 --
1120 l_xml_begin := '<uiterpt>'||gc_eol;
1121 vXMLTable(gn_vctr).xmlstring := l_xml_begin;
1122 gn_vctr := gn_vctr + 1;
1123
1124 --
1125 OPEN cur_uite_office(gr_parameters.payroll_arch);
1126 FETCH cur_uite_office INTO lr_uite_office;
1127 CLOSE cur_uite_office;
1128
1129 get_numbers(1,lr_uite_emp.insured_number,lc_insured_number);
1130
1131 get_numbers(2,lr_uite_office.location_number,lc_location_number);
1132
1133
1134 l_common_xml := lc_insured_number||lc_location_number||'<YR>'||htmlspchar(cnv_str(gr_parameters.subject_year))||'</YR>'
1135 ||'<P1>'||htmlspchar(cnv_str(lr_uite_emp.insured_number))||'</P1>'||gc_eol
1136 ||'<P2>'||htmlspchar(cnv_str(lr_uite_office.location_number))||'</P2>'||gc_eol
1137 ||'<P3>'||htmlspchar(cnv_str(lr_uite_emp.last_name_kana||' '||lr_uite_emp.first_name_kana))||'</P3>'||gc_eol
1138 ||'<P4>'||htmlspchar(cnv_str(lr_uite_emp.last_name||' '||lr_uite_emp.first_name))||'</P4>'||gc_eol
1139 ||'<P5>'||htmlspchar(cnv_str(lr_uite_emp.termination_date))||'</P5>'||gc_eol
1140 ||'<P5_YY>'||htmlspchar(cnv_str(lr_uite_emp.termination_date_yy))||'</P5_YY>'||gc_eol
1141 ||'<P5_MM>'||htmlspchar(cnv_str(lr_uite_emp.termination_date_mm))||'</P5_MM>'||gc_eol
1142 ||'<P5_DD>'||htmlspchar(cnv_str(lr_uite_emp.termination_date_dd))||'</P5_DD>'||gc_eol
1143 ||'<P6>'||htmlspchar(cnv_str(lr_uite_office.location_name))||'</P6>'||gc_eol
1144 ||'<P7>'||htmlspchar(cnv_str(lr_uite_office.loc_address_line1||lr_uite_office.loc_address_line2||lr_uite_office.loc_address_line3))||'</P7>'||gc_eol
1145 ||'<P8>'||htmlspchar(cnv_str(lr_uite_office.loc_phone_number))||'</P8>'||gc_eol
1146 ||'<P9>'||htmlspchar(cnv_str(lr_uite_emp.address_line1||lr_uite_emp.address_line2||lr_uite_emp.address_line3))||'</P9>'||gc_eol
1147 ||'<P10>'||htmlspchar(cnv_str(lr_uite_emp.phone_number))||'</P10>'||gc_eol
1148 ||'<P11>'||htmlspchar(cnv_str(lr_uite_office.employer_address_line1||lr_uite_office.employer_address_line2||lr_uite_office.employer_address_line3))||'</P11>'||gc_eol
1149 ||'<P12>'||htmlspchar(cnv_str(lr_uite_office.employer_full_name))||'</P12>'||gc_eol
1150 ||'<P13>'||htmlspchar(cnv_str(lr_uite_emp.following_date_term))||'</P13>'||gc_eol
1151 ||'<P13_MM>'||htmlspchar(cnv_str(lr_uite_emp.following_date_term_mm))||'</P13_MM>'||gc_eol
1152 ||'<P13_DD>'||htmlspchar(cnv_str(lr_uite_emp.following_date_term_dd))||'</P13_DD>'||gc_eol
1153 ||'<P14>'||htmlspchar(cnv_str(lr_uite_emp.employee_number))||'</P14>'||gc_eol
1154 ||'<P15>'||htmlspchar(cnv_str(lr_uite_emp.postal_code))||'</P15>'||gc_eol
1155 ||'<P16>'||htmlspchar(cnv_str(lr_uite_office.employer_name))||'</P16>'||gc_eol; -- Added as per the bug#9648053
1156
1157 l_xml :=gc_eol||l_common_xml||gc_eol;
1158 vXMLTable(gn_vctr).xmlstring := l_xml;
1159 gn_vctr := gn_vctr + 1;
1160 -- Bug 9648106 start
1161 l_emp_xml := lc_insured_number||lc_location_number||'<YR>'||htmlspchar(cnv_str(gr_parameters.subject_year))||'</YR>'
1162 ||'<P1>'||htmlspchar(cnv_str(lr_uite_emp.insured_number))||'</P1>'||gc_eol
1163 ||'<P2>'||htmlspchar(cnv_str(lr_uite_office.location_number))||'</P2>'||gc_eol
1164 ||'<P3>'||htmlspchar(cnv_str(lr_uite_emp.last_name_kana||' '||lr_uite_emp.first_name_kana))||'</P3>'||gc_eol
1165 ||'<P4>'||htmlspchar(cnv_str(lr_uite_emp.last_name||' '||lr_uite_emp.first_name))||'</P4>'||gc_eol
1166 ||'<P5>'||htmlspchar(cnv_str(lr_uite_emp.termination_date))||'</P5>'||gc_eol
1167 ||'<P5_YY>'||htmlspchar(cnv_str(lr_uite_emp.termination_date_yy))||'</P5_YY>'||gc_eol
1168 ||'<P5_MM>'||htmlspchar(cnv_str(lr_uite_emp.termination_date_mm))||'</P5_MM>'||gc_eol
1169 ||'<P5_DD>'||htmlspchar(cnv_str(lr_uite_emp.termination_date_dd))||'</P5_DD>'||gc_eol
1170 ||'<P6></P6>'||gc_eol
1171 ||'<P7></P7>'||gc_eol
1172 ||'<P8></P8>'||gc_eol
1173 ||'<P9></P9>'||gc_eol
1174 ||'<P10></P10>'||gc_eol
1175 ||'<P11>'||htmlspchar(cnv_str(lr_uite_office.employer_address_line1||lr_uite_office.employer_address_line2||lr_uite_office.employer_address_line3))||'</P11>'||gc_eol
1176 ||'<P12>'||htmlspchar(cnv_str(lr_uite_office.employer_full_name))||'</P12>'||gc_eol
1177 ||'<P13>'||htmlspchar(cnv_str(lr_uite_emp.following_date_term))||'</P13>'||gc_eol
1178 ||'<P13_MM>'||htmlspchar(cnv_str(lr_uite_emp.following_date_term_mm))||'</P13_MM>'||gc_eol
1179 ||'<P13_DD>'||htmlspchar(cnv_str(lr_uite_emp.following_date_term_dd))||'</P13_DD>'||gc_eol
1180 ||'<P14>'||htmlspchar(cnv_str(lr_uite_emp.employee_number))||'</P14>'||gc_eol
1181 ||'<P15></P15>'||gc_eol
1182 ||'<P16>'||htmlspchar(cnv_str(lr_uite_office.employer_name))||'</P16>'||gc_eol;
1183 -- Bug 9648106 end
1184 --
1185 lr_uite_office := NULL;
1186 l_common_xml := NULL;
1187 l_term_xml := NULL;
1188 seque := 1;
1189
1190
1191 OPEN cur_uite_term(l_mag_asg_action_id);
1192 FETCH cur_uite_term INTO lr_uite_term;
1193 CLOSE cur_uite_term;
1194
1195
1196 CASE
1197 WHEN lr_uite_term.termination_reason = '1_1' THEN ln_termination_value := 1;
1198 WHEN lr_uite_term.termination_reason = '1_2' THEN ln_termination_value := 2;
1199 WHEN lr_uite_term.termination_reason = '2_1' THEN ln_termination_value := 3;
1200 WHEN lr_uite_term.termination_reason = '2_2' THEN ln_termination_value := 4;
1201 WHEN lr_uite_term.termination_reason = '2_3' THEN ln_termination_value := 5;
1202 WHEN lr_uite_term.termination_reason = '2_4' THEN ln_termination_value := 6;
1203 WHEN lr_uite_term.termination_reason = '2_5' THEN ln_termination_value := 7;
1204 WHEN lr_uite_term.termination_reason = '3_1' THEN ln_termination_value := 8;
1205 WHEN lr_uite_term.termination_reason = '3_2' THEN ln_termination_value := 9;
1206 WHEN lr_uite_term.termination_reason = '3_3_1' THEN ln_termination_value := 10;
1207 WHEN lr_uite_term.termination_reason = '3_3_2' THEN ln_termination_value := 11;
1208 WHEN lr_uite_term.termination_reason = '4_1_1' THEN ln_termination_value := 12;
1209 WHEN lr_uite_term.termination_reason = '4_1_2' THEN ln_termination_value := 13;
1210 WHEN lr_uite_term.termination_reason = '4_1_3' THEN ln_termination_value := 14;
1211 WHEN lr_uite_term.termination_reason = '4_1_4' THEN ln_termination_value := 15;
1212 WHEN lr_uite_term.termination_reason = '4_1_5' THEN ln_termination_value := 16;
1213 WHEN lr_uite_term.termination_reason = '4_1_6' THEN ln_termination_value := 17;
1214 WHEN lr_uite_term.termination_reason = '4_2' THEN ln_termination_value := 18;
1215 WHEN lr_uite_term.termination_reason = '5' THEN ln_termination_value := 19;
1216 ELSE ln_termination_value := 0;
1217 END CASE;
1218
1219 FOR i in 1 .. 19
1220 LOOP
1221 IF i = ln_termination_value THEN
1222 l_common_xml := l_common_xml||'<T'||i||'>'||htmlspchar(cnv_str('O'))||'</T'||i||'>'||gc_eol; -- changed as per the bug #9648122
1223 ELSE
1224 l_common_xml := l_common_xml||'<T'||i||'></T'||i||'>'||gc_eol;
1225 END IF;
1226 END LOOP;
1227
1228 l_common_xml := l_common_xml||'<T20>'||htmlspchar(cnv_str(lr_uite_term.concrete_circumstance1||lr_uite_term.concrete_circumstance2))||'</T20>'||gc_eol;
1229
1230 l_xml :=gc_eol||l_common_xml||gc_eol;
1231 vXMLTable(gn_vctr).xmlstring := l_xml;
1232 gn_vctr := gn_vctr + 1;
1233
1234 ln_termination_value := NULL;
1235 lr_uite_term := NULL;
1236 l_common_xml := NULL;
1237
1238 OPEN cur_uite_wage(l_mag_asg_action_id);
1239 FETCH cur_uite_wage INTO lr_uite_wage;
1240 CLOSE cur_uite_wage;
1241
1242 l_common_xml := '<W1>'||htmlspchar(cnv_str(lr_uite_wage.wage_instruction1 || lr_uite_wage.wage_instruction2))||'</W1>'||gc_eol;
1243 l_wage_xml:= '<W1>'||htmlspchar(cnv_str(lr_uite_wage.wage_instruction1 || lr_uite_wage.wage_instruction2))||'</W1>'||gc_eol;
1244
1245 l_xml :=gc_eol||l_common_xml||gc_eol;
1246 vXMLTable(gn_vctr).xmlstring := l_xml;
1247 gn_vctr := gn_vctr + 1;
1248
1249 lr_uite_wage := NULL;
1250 l_common_xml := NULL;
1251
1252
1253
1254 OPEN cur_uite_sal(l_mag_asg_action_id);
1255 LOOP
1256 FETCH cur_uite_sal INTO lr_uite_sal;
1257 EXIT WHEN (seque = 15 AND cur_uite_sal%NOTFOUND);
1258
1259 IF cur_uite_sal%NOTFOUND THEN
1260 lr_uite_sal := NULL;
1261 END IF;
1262 -- Changed by 9728602 --
1263 IF (lr_uite_sal.line_number >= 13 AND seque = 14) THEN
1264 seque := 2;
1265 l_common_xml := l_common_xml || l_sal_xml ;
1266
1267 l_term_xml := l_wage_xml;
1268
1269 FOR i in 1 .. 20
1270 LOOP
1271 l_term_xml := l_term_xml||'<T'||i||'></T'||i||'>'||gc_eol;
1272 END LOOP;
1273 l_common_xml := l_common_xml || l_term_xml;
1274 l_common_xml := l_common_xml || l_emp_xml;
1275 l_sal_xml := NULL;
1276 l_term_xml := NULL;
1277
1278 IF l_common_xml IS NOT NULL THEN
1279 l_xml :=gc_eol||l_common_xml||gc_eol;
1280 vXMLTable(gn_vctr).xmlstring := l_xml;
1281 gn_vctr := gn_vctr + 1;
1282 END IF;
1283 l_common_xml := NULL;
1284
1285 END IF;
1286 -- Changed for 9728602 --
1287
1288 IF seque <= 13 THEN
1289 l_sal_xml := l_sal_xml ||'<S'||seque||'-1>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_start_date))||'</S'||seque||'-1>'||gc_eol
1290 ||'<S'||seque||'-1_MM>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_start_date_mm))||'</S'||seque||'-1_MM>'||gc_eol
1291 ||'<S'||seque||'-1_DD>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_start_date_dd))||'</S'||seque||'-1_DD>'||gc_eol
1292 ||'<S'||seque||'-2>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_end_date))||'</S'||seque||'-2>'||gc_eol
1293 ||'<S'||seque||'-2_MM>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_end_date_mm))||'</S'||seque||'-2_MM>'||gc_eol
1294 ||'<S'||seque||'-2_DD>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_end_date_dd))||'</S'||seque||'-2_DD>'||gc_eol
1295 ||'<S'||seque||'-3>'||htmlspchar(cnv_str(lr_uite_sal.ins_period_base_days))||'</S'||seque||'-3>'||gc_eol
1296 ||'<S'||seque||'-4>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_start_date))||'</S'||seque||'-4>'||gc_eol
1297 ||'<S'||seque||'-4_MM>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_start_date_mm))||'</S'||seque||'-4_MM>'||gc_eol
1298 ||'<S'||seque||'-4_DD>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_start_date_dd))||'</S'||seque||'-4_DD>'||gc_eol
1299 ||'<S'||seque||'-5>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_end_date))||'</S'||seque||'-5>'||gc_eol
1300 ||'<S'||seque||'-5_MM>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_end_date_mm))||'</S'||seque||'-5_MM>'||gc_eol
1301 ||'<S'||seque||'-5_DD>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_end_date_dd))||'</S'||seque||'-5_DD>'||gc_eol
1302 ||'<S'||seque||'-6>'||htmlspchar(cnv_str(lr_uite_sal.pay_period_base_days))||'</S'||seque||'-6>'||gc_eol
1303 ||'<S'||seque||'-7>'||htmlspchar(cnv_str(lr_uite_sal.wage_amount_a))||'</S'||seque||'-7>'||gc_eol
1304 ||'<S'||seque||'-8>'||htmlspchar(cnv_str(lr_uite_sal.wage_amount_b))||'</S'||seque||'-8>'||gc_eol
1305 ||'<S'||seque||'-9>'||htmlspchar(cnv_str(lr_uite_sal.wage_amount_total))||'</S'||seque||'-9>'||gc_eol
1306 ||'<S'||seque||'-10>'||htmlspchar(cnv_str(lr_uite_sal.remarks))||'</S'||seque||'-10>'||gc_eol
1307 ||'<S'||seque||'-11>'||htmlspchar(cnv_str(lr_uite_sal.exclude_period))||'</S'||seque||'-11>'||gc_eol
1308 ||'<S'||seque||'-12>'||htmlspchar(cnv_str(lr_uite_sal.line_number))||'</S'||seque||'-12>';
1309
1310 END IF;
1311 seque := seque + 1;
1312
1313 END LOOP;
1314 CLOSE cur_uite_sal;
1315
1316
1317
1318 l_xml :=gc_eol||l_sal_xml||gc_eol;
1319 vXMLTable(gn_vctr).xmlstring := l_xml;
1320 gn_vctr := gn_vctr + 1;
1321
1322 lr_uite_sal := NULL;
1323 l_emp_xml := NULL;
1324 l_common_xml := NULL;
1325 l_sal_xml := NULL;
1326 l_wage_xml := NULL;
1327
1328 l_xml2 :='</uiterpt>'||gc_eol ;
1329 vXMLTable(gn_vctr).xmlstring := l_xml2;
1330 gn_vctr := gn_vctr + 1;
1331
1332 END IF;
1333 lr_uite_emp := NULL;
1334
1335 CLOSE cur_uite_emp;
1336 --
1337 --
1338 IF gb_debug THEN
1339 hr_utility.trace ('Leaving assact_xml');
1340 END IF;
1341 --
1342 EXCEPTION
1343 WHEN gc_exception THEN
1344 IF gb_debug THEN
1345 hr_utility.set_location('Error in assact_xml ',999999);
1346 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1347 hr_utility.raise_error;
1348 END IF;
1349 WHEN OTHERS THEN
1350 RAISE gc_exception;
1351 END assact_xml;
1352 --
1353 PROCEDURE print_clob(p_clob CLOB)
1354 --************************************************************************
1355 -- PROCEDURE
1356 -- print_clob
1357 --
1358 -- DESCRIPTION
1359 -- This procedure prints contents of a CLOB object passed as parameter.
1360 --
1361 -- ACCESS
1362 -- PUBLIC
1363 --
1364 -- PARAMETERS
1365 -- ==========
1366 -- NAME TYPE DESCRIPTION
1367 -- ----------------- -------- ---------------------------------------
1368 -- p_clob IN This parameter passes clob object
1369 --
1370 -- PREREQUISITES
1371 -- None
1372 --
1373 -- CALLED BY
1374 -- None
1375 --************************************************************************
1376 IS
1377 ln_chars NUMBER;
1378 ln_offset NUMBER;
1379 lc_buf VARCHAR2(255);
1380 --
1381 BEGIN
1382 --
1383 gb_debug := hr_utility.debug_enabled;
1384 --
1385 IF gb_debug THEN
1386 hr_utility.trace ('Entering PRINT_CLOB');
1387 END IF;
1388 --
1389 ln_chars := 240;
1390 ln_offset := 1;
1391 LOOP
1392 lc_buf := NULL;
1393 dbms_lob.read( p_clob
1394 , ln_chars
1395 , ln_offset
1396 , lc_buf
1397 );
1398 hr_utility.trace(lc_buf);
1399 ln_offset := ln_offset + ln_chars;
1400 END LOOP;
1401 --
1402 EXCEPTION
1403 WHEN NO_DATA_FOUND THEN
1404 IF gb_debug THEN
1405 hr_utility.trace ('CLOB contents end.');
1406 END IF;
1407 --
1408 END print_clob;
1409 --
1410
1411 PROCEDURE writetoclob (p_write_xml OUT NOCOPY CLOB)
1412 --************************************************************************
1413 -- PROCEDURE
1414 -- writetoclob
1415 --
1416 -- DESCRIPTION
1417 -- This procedure selects the xml from vxmltable and writes it
1418 -- into a clob variable. This clob variable is then returned
1419 --
1420 -- ACCESS
1421 -- PUBLIC
1422 --
1423 -- PARAMETERS
1424 -- ==========
1425 -- NAME TYPE DESCRIPTION
1426 -- ----------------- -------- ----------------------------------
1427 -- p_write_xml OUT This parameter returns XML String
1428 --
1429 -- PREREQUISITES
1430 -- None
1431 --
1432 -- CALLED BY
1433 -- None
1434 --************************************************************************
1435 IS
1436 g_xfdf_string CLOB;
1437 l_tempclob CLOB;
1438 ln_ctr_table NUMBER;
1439 BEGIN
1440 --
1441 gb_debug := hr_utility.debug_enabled;
1442 --
1443 IF gb_debug THEN
1444 hr_utility.trace('Entering WRITETOCLOB');
1445 END IF;
1446 --
1447 dbms_lob.createtemporary(g_xfdf_string,FALSE,DBMS_LOB.CALL);
1448 dbms_lob.open(g_xfdf_string,dbms_lob.lob_readwrite);
1449 --
1450 FOR ln_ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1451 LOOP
1452 dbms_lob.writeAppend(g_xfdf_string
1453 ,LENGTH(vxmltable(ln_ctr_table).xmlstring)
1454 ,vxmltable(ln_ctr_table).xmlstring );
1455 END LOOP;
1456 p_write_xml := g_xfdf_string;
1457 --
1458 IF gb_debug THEN
1459 hr_utility.set_location('Out of loop ', 99);
1460 END IF;
1461 --
1462 dbms_lob.close(g_xfdf_string);
1463 IF gb_debug THEN
1464 hr_utility.trace('Leaving WRITETOCLOB');
1465 END IF;
1466 --
1467 EXCEPTION
1468 WHEN gc_exception THEN
1469 IF gb_debug THEN
1470 hr_utility.set_location('Error in writetoclob ',999999);
1471 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1472 hr_utility.raise_error;
1473 END IF;
1474 WHEN OTHERS THEN
1475 RAISE gc_exception;
1476 --
1477 END writetoclob;
1478 --
1479 PROCEDURE get_cp_xml(p_assignment_action_id IN NUMBER
1480 ,p_xml OUT NOCOPY CLOB
1481 )
1482 --************************************************************************
1483 -- PROCEDURE
1484 -- get_cp_xml
1485 --
1486 -- DESCRIPTION
1487 -- This procedure creates and returns the xml for the
1488 -- assignment_action_id passed as parameter
1489 --
1490 -- ACCESS
1491 -- PUBLIC
1492 --
1493 -- PARAMETERS
1494 -- ==========
1495 -- NAME TYPE DESCRIPTION
1496 -- ----------------- -------- ----------------------------------
1497 -- p_assignment_action_id IN This parameter passes assignment Action ID
1498 -- p_xml OUT This parameter returns XML
1499 --
1500 -- PREREQUISITES
1501 -- None
1502 --
1503 -- CALLED BY
1504 -- None
1505 --************************************************************************
1506 IS
1507 --
1508 BEGIN
1509 --
1510 gb_debug := hr_utility.debug_enabled;
1511 --
1512 IF gb_debug THEN
1513 hr_utility.trace('Entering GET_CP_XML');
1514 END IF;
1515 --
1516 assact_xml(p_assignment_action_id);
1517 writetoclob (p_xml);
1518 --
1519 IF gb_debug THEN
1520 hr_utility.trace('Leaving GET_CP_XML');
1521 END IF;
1522 --
1523 END get_cp_xml;
1524 --
1525
1526 --
1527 PROCEDURE deinitialise (p_payroll_action_id IN NUMBER)
1528 IS
1529 --
1530 BEGIN
1531
1532 --pay_archive.remove_report_actions(p_payroll_action_id);
1533 NULL;
1534
1535 END deinitialise;
1536
1537 PROCEDURE sort_action( p_payroll_action_id IN NUMBER
1538 , sqlstr IN OUT NOCOPY VARCHAR2
1539 , len OUT NOCOPY NUMBER
1540 )
1541 --************************************************************************
1542 -- PROCEDURE
1543 -- sort_action
1544 --
1545 -- DESCRIPTION
1546 -- This procedure defines a SQL statement to fetch all the people
1547 -- according to the sort order parameters.
1548 --
1549 -- ACCESS
1550 -- PUBLIC
1551 --
1552 -- PARAMETERS
1553 -- ==========
1554 -- NAME TYPE DESCRIPTION
1555 -- ----------------- -------- ------------------------------------
1556 -- p_payroll_action_id IN This parameter passes payroll_action_id object
1557 -- p_sqlstr IN OUT This parameter returns the SQL Statement
1558 -- len OUT This parameter returns the length of the SQL string
1559 --
1560 -- PREREQUISITES
1561 -- None
1562 --
1563 -- CALLED BY
1564 -- None
1565 --************************************************************************
1566 AS
1567 lc_order_by VARCHAR2(100);
1568
1569 BEGIN
1570 --
1571 gb_debug := hr_utility.debug_enabled;
1572 --
1573 IF gb_debug THEN
1574 hr_utility.set_location('Entering sort_action procedure',20);
1575 END IF;
1576 --
1577 hr_utility.trace('Beginning of the sort_action cursor');
1578 --
1579 initialize(p_payroll_action_id);
1580
1581
1582 IF (gr_parameters.sort_order = 'EMPLOYEE_NUMBER') THEN
1583 lc_order_by := 'UPPER(PPF.employee_number), NVL(PPS.actual_termination_date,PPS.projected_termination_date)' ; -- Bug 9728577
1584 ELSE
1585 lc_order_by := 'NVL(PPS.actual_termination_date,PPS.projected_termination_date),UPPER(PPF.employee_number)' ;
1586 END IF;
1587
1588 sqlstr := ' SELECT PAA.rowid
1589 FROM per_assignments_f PAF
1590 ,pay_assignment_actions PAA
1591 ,per_people_f PPF
1592 ,hr_all_organization_units_tl HAOUT
1593 ,hr_all_organization_units HAOU
1594 ,per_periods_of_service PPS
1595 WHERE PAA.payroll_action_id = :pactid
1596 AND PAF.assignment_id = PAA.assignment_id
1597 AND PPF.person_id = PAF.person_id
1598 AND PAF.organization_id = HAOU.organization_id
1599 AND HAOUT.organization_id = HAOU.organization_id
1600 AND HAOUT.language = USERENV(''LANG'')
1601 AND PPS.period_of_service_id = PAF.period_of_service_id
1602 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PPF.effective_start_date
1603 AND PPF.effective_end_date
1604 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PAF.effective_start_date
1605 AND PAF.effective_end_date
1606 ORDER BY '||lc_order_by; -- Bug 9728577
1607
1608 len := length(sqlstr);
1609 IF gb_debug
1610 THEN
1611 hr_utility.trace('End of the sort_Action cursor');
1612 END IF;
1613 --
1614 EXCEPTION WHEN NO_DATA_FOUND THEN
1615 IF gb_debug THEN
1616 hr_utility.trace('Error in Sort Procedure - getting legislative param');
1617 END IF;
1618 RAISE;
1619 --
1620 END sort_action;
1621
1622 END PAY_JP_UITE_REPORT_PKG;