[Home] [Help]
PACKAGE BODY: APPS.PER_JP_WRKREG_REPORT_PKG
Source
1 PACKAGE BODY per_jp_wrkreg_report_pkg
2 -- $Header: pejpwrpt.pkb 120.1.12010000.25 2009/08/19 14:41:40 rdarasi noship $
3 -- *************************************************************************
4 -- * Copyright (c) Oracle Corporation Japan,2009 Product Development.
5 -- * All rights reserved
6 -- *************************************************************************
7 -- *
8 -- * PROGRAM NAME
9 -- * pejpwrpt.pkb
10 -- *
11 -- * DESCRIPTION
12 -- * This script creates the package body of per_jp_wrkreg_report_pkg
13 -- *
14 -- * DEPENDENCIES
15 -- * None
16 -- *
17 -- * CALLED BY
18 -- * Concurrent Program
19 -- *
20 -- * LAST UPDATE DATE 11-JUN-2009
21 -- * Date the program has been modified for the last time
22 -- *
23 -- * HISTORY
24 -- * =======
25 -- *
26 -- * DATE AUTHOR(S) VERSION BUG NO DESCRIPTION
27 -- * -----------+-----------+-------------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
28 -- * 19-MAR-2009 MDARBHA 120.0.12010000.1 8558615 Creation
29 -- * 11-JUN-2009 MDARBHA 120.1.12010000.6 8558615 Changed as per review Comments
30 --* 24-JUN-2009 MDARBHA 120.1.12010000.10 8608463,8623767 Changed Procedure 'action_creation' for Organization Hierarchy as per the bug 8623767
31 -- Changed Procedure 'assact_xml' for Job History
32 --* 01-JUL-2009 MDARBHA 120.1.12010000.11 8623733 Chnaged the 'action_creation' for Organization Hierarchy as per the bug 8623733
33 --* 12-JUL-2009 MDARBHA 120.1.12010000.12 8666468 Changed the procedure Assact XML to fetch the lookup meaning for Termination Reason and Death Cause
34 --* 12-JUL-2009 MDARBHA 120.1.12010000.12 8667163 Changed the procedure action creation to fetch the correct organzations if Organization Parameter is null
35 -- Removed the code adedd for Bug 8623733 as not required as per Bug 8667163
36 --* 12-JUL-2009 MDARBHA 120.1.12010000.12 8667702 Changed the procedure Assact XML .Added space between Address Line1 ,Address Line2 and Address Line3.
37 --* 13-JUL-2009 MDARBHA 120.1.12010000.13 8679904 Changed action creation to consider future terminated employees.
38 --* 20-JUL-2009 MDARBHA 120.1.12010000.14 8686503 Changed the date format in Assact XML
39 --* 31-JUL-2009 MDARBHA 120.1.12010000.15 8691511 Changed the sort action cursor
40 --* 31-JUL-2009 MDARBHA 120.1.12010000.16 8691511 Changed the sort action cursor
41 --* 31-JUL-2009 MDARBHA 120.1.12010000.17 8721997 Changed the action creation for rehired employees scenario
42 --* 31-JUL-2009 MDARBHA 120.1.12010000.18 8721997 Changed the action creation for rehired employees scenario
43 --* 31-JUL-2009 MDARBHA 120.1.12010000.19 8691511 Changed the sort action cursor
44 --* 04-AUG-2009 MDARBHA 120.1.12010000.20 8691511 Changed the sort action cursor
45 --* 13-AUG-2009 RDARASI 120.1.12010000.21 8774489 Changed the sort action Cursor
46 --* 14-AUG-2009 RDARASI 120.1.12010000.22 8774489 Changed the sort action Cursor
47 --* 19-AUG-2009 RDARASI 120.1.12010000.23 8814071 Changed the cur_wrk_reg_emp Cursor
48 -- *************************************************************************************************************************************************************
49 AS
50 --
51 g_write_xml CLOB;
52 g_xfdf_string CLOB;
53 gc_eol VARCHAR2(5) := fnd_global.local_chr(10);
54 gc_proc_name VARCHAR2(240);
55 gc_pkg_name VARCHAR2(30):= 'per_jp_wrkreg_report_pkg.';
56 gb_debug BOOLEAN;
57 gn_dummy NUMBER := -99 ;
58 gn_all_exclusions_flag NUMBER;
59 gn_vctr NUMBER;
60 gc_exception EXCEPTION;
61 --
62 FUNCTION cnv_str( p_text IN VARCHAR2
63 , p_start IN NUMBER DEFAULT NULL
64 , p_end IN NUMBER DEFAULT NULL
65 )
66 RETURN VARCHAR2
67 --************************************************************************
68 -- FUNCTION
69 -- cnv_str
70 --
71 -- DESCRIPTION
72 -- This fucntion retunrs the string based on the start and end positions
73 -- from the given text
74 --
75 -- ACCESS
76 -- PUBLIC
77 --
78 -- PARAMETERS
79 -- ==========
80 -- NAME TYPE DESCRIPTION
81 -- ----------------- -------- ---------------------------------------
82 -- p_text IN This parameter passes Assignment Set Id
83 -- p_start IN This parameter passes Start Position
84 -- p_end IN This parameter passes End Position
85 --
86 -- PREREQUISITES
87 -- None
88 --
89 -- CALLED BY
90 -- None
91 --************************************************************************
92 IS
93 lc_text VARCHAR2(4000);
94 BEGIN
95 --
96 gb_debug := hr_utility.debug_enabled;
97 --
98 IF gb_debug
99 THEN
100 hr_utility.trace ('Entering CNV_STR');
101 END IF;
102 --
103 lc_text := LTRIM(RTRIM(REPLACE(p_text,TO_MULTI_BYTE(' '),' ')));
104 --
105 IF p_start IS NOT NULL
106 AND p_end IS NOT NULL THEN
107 lc_text := SUBSTR(lc_text,p_start,p_end);
108 END IF;
109 --
110 IF gb_debug THEN
111 hr_utility.trace ('Leaving CNV_STR');
112 END IF;
113 --
114 RETURN lc_text;
115 --
116 END cnv_str;
117 --
118 FUNCTION htmlspchar(p_text IN VARCHAR2)
119 RETURN VARCHAR2
120 --************************************************************************
121 -- FUNCTION
122 -- htmlspchar
123 --
124 -- DESCRIPTION
125 -- This fucntion retunrs the string based on the start and end positions
126 -- from the given text
127 --
128 -- ACCESS
129 -- PUBLIC
130 --
131 -- PARAMETERS
132 -- ==========
133 -- NAME TYPE DESCRIPTION
134 -- ----------------- -------- ---------------------------------------
135 -- p_text IN This parameter passes Assignment Set Id
136 --
137 -- PREREQUISITES
138 -- None
139 --
140 -- CALLED BY
141 -- None
142 --************************************************************************
143 IS
144 lc_htmlspchar VARCHAR2(1) := 'N';
145 BEGIN
146 --
147 gb_debug := hr_utility.debug_enabled;
148 --
149 IF gb_debug THEN
150 hr_utility.trace ('Entering htmlspchar');
151 END IF;
152 --
153 IF NVL(INSTR(p_text,'<'),0) > 0 THEN
154 lc_htmlspchar := 'Y';
155 END IF;
156 --
157 IF lc_htmlspchar = 'N'
158 AND NVL(INSTR(p_text,'>'),0) > 0 THEN
159 lc_htmlspchar := 'Y';
160 END IF;
161 --
162 IF lc_htmlspchar = 'N'
163 AND NVL(INSTR(p_text,'&'),0) > 0 THEN
164 lc_htmlspchar := 'Y';
165 END IF;
166 --
167 IF lc_htmlspchar = 'N'
168 AND NVL(INSTR(p_text,''''),0) > 0 THEN
169 lc_htmlspchar := 'Y';
170 END IF;
171 --
172 IF lc_htmlspchar = 'N'
173 AND NVL(INSTR(p_text,'"'),0) > 0 THEN
174 lc_htmlspchar := 'Y';
175 END IF;
176 --
177 IF lc_htmlspchar = 'Y' then
178 RETURN '<![CDATA['||p_text||']]>';
179 ELSE
180 RETURN p_text;
181 END IF;
182 --
183 END htmlspchar;
184 --
185 PROCEDURE print_clob(p_clob CLOB)
186 --************************************************************************
187 -- PROCEDURE
188 -- print_clob
189 --
190 -- DESCRIPTION
191 -- This procedure prints contents of a CLOB object passed as parameter.
192 --
193 -- ACCESS
194 -- PUBLIC
195 --
196 -- PARAMETERS
197 -- ==========
198 -- NAME TYPE DESCRIPTION
199 -- ----------------- -------- ---------------------------------------
200 -- p_clob IN This parameter passes clob object
201 --
202 -- PREREQUISITES
203 -- None
204 --
205 -- CALLED BY
206 -- None
207 --************************************************************************
208 IS
209 ln_chars NUMBER;
210 ln_offset NUMBER;
211 lc_buf VARCHAR2(255);
212 --
213 BEGIN
214 --
215 gb_debug := hr_utility.debug_enabled;
216 --
217 IF gb_debug THEN
218 hr_utility.trace ('Entering PRINT_CLOB');
219 END IF;
220 --
221 ln_chars := 240;
222 ln_offset := 1;
223 LOOP
224 lc_buf := NULL;
225 dbms_lob.read( p_clob
226 , ln_chars
227 , ln_offset
228 , lc_buf
229 );
230 hr_utility.trace(lc_buf);
231 ln_offset := ln_offset + ln_chars;
232 END LOOP;
233 --
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 IF gb_debug THEN
237 hr_utility.trace ('CLOB contents end.');
238 END IF;
239 --
240 END print_clob;
241 --
242 PROCEDURE initialize(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE )
243 --***************************************************************************
244 -- PROCEDURE
245 -- initialize
246 -- DESCRIPTION
247 -- This procedure is used to set global contexts
248 --
249 -- ACCESS
250 -- PUBLIC
251 --
252 -- PARAMETERS
253 -- ==========
254 -- NAME TYPE DESCRIPTION
255 -- ----------------- -------- ---------------------------------------
256 -- p_payroll_action_id IN This parameter passes Payroll Action Id
257 --
258 -- PREREQUISITES
259 -- None
260 --
261 -- CALLED BY
262 -- action_creation
263 --*************************************************************************
264 IS
265 --
266 CURSOR lcr_params(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
267 IS
268 SELECT fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',legislative_parameters)) payroll_action_id
269 ,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASS_SETID',legislative_parameters)) ass_setid
270 ,pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id
271 ,NVL(pay_core_utils.get_parameter('IOH',legislative_parameters),'Y') include_org_hierarchy
272 ,pay_core_utils.get_parameter('ORG',legislative_parameters) organization_id
273 ,pay_core_utils.get_parameter('LOC',legislative_parameters) location_id
274 ,FND_DATE.canonical_to_date(pay_core_utils.get_parameter('EFFDATE',legislative_parameters)) effective_date
275 ,NVL(pay_core_utils.get_parameter('S1',legislative_parameters),'ZZ') sort_order_1
276 ,NVL(pay_core_utils.get_parameter('S2',legislative_parameters),'ZZ') sort_order_2
277 ,NVL(pay_core_utils.get_parameter('S3',legislative_parameters),'ZZ') sort_order_3
278 ,pay_core_utils.get_parameter('ITE',legislative_parameters) incl_term_emp
279 ,TO_DATE(pay_core_utils.get_parameter('TEDF',legislative_parameters),'YYYY/MM/DD') term_eff_date_from
280 ,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD') term_eff_date_to
281 FROM pay_payroll_actions PPA
282 WHERE PPA.payroll_action_id = p_payroll_action_id;
283 --
284 -- Local Variables
285 lc_procedure VARCHAR2(200);
286 --
287 BEGIN
288 --
289 gb_debug :=hr_utility.debug_enabled ;
290 IF gb_debug THEN
291 lc_procedure := gc_pkg_name||'initialize';
292 hr_utility.set_location('Entering '||lc_procedure,1);
293 END IF;
294 --
295 -- Fetch the parameters passed by user into global variable.
296 --
297 OPEN lcr_params(p_payroll_action_id);
298 FETCH lcr_params into gr_parameters;
299 CLOSE lcr_params;
300 --
301 SELECT TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')
302 INTO gr_parameters.effective_date
303 FROM pay_payroll_actions PPA
304 WHERE PPA.payroll_action_id = gr_parameters.payroll_action_id;
305 --
306 IF gb_debug THEN
307 hr_utility.set_location('p_payroll_action_id......... = ' || p_payroll_action_id,30);
308 hr_utility.set_location('gr_parameters.payroll_action_id . . = ' || gr_parameters.payroll_action_id,30);
309 hr_utility.set_location('gr_parameters. ass_setid............... . = ' || gr_parameters. ass_setid,30);
310 hr_utility.set_location('gr_parameters.include_org_hierarchy . . = ' || gr_parameters.include_org_hierarchy,30);
311 hr_utility.set_location('gr_parameters.organization_id............... .= ' || gr_parameters.organization_id,30);
312 hr_utility.set_location('gr_parameters.business_group_id....... = ' || gr_parameters.business_group_id,30);
313 hr_utility.set_location('gr_parameters.location_id....... = ' || gr_parameters.location_id,30);
314 hr_utility.set_location('gr_parameters.effective_date.......... = ' || gr_parameters.effective_date,30);
315 hr_utility.set_location('gr_parameters.incl_term_emp...... = ' || gr_parameters.incl_term_emp,30);
316 hr_utility.set_location('gr_parameters.term_eff_date_from...... = ' || gr_parameters.term_eff_date_from,30);
317 hr_utility.set_location('gr_parameters.term_eff_date_to...... = ' || gr_parameters.term_eff_date_to,30);
318 hr_utility.set_location('gr_parameters.sort_order_1...... = ' || gr_parameters.sort_order_1,30);
319 hr_utility.set_location('gr_parameters.sort_order_2...... = ' || gr_parameters.sort_order_2,30);
320 hr_utility.set_location('gr_parameters.sort_order_3...... = ' || gr_parameters.sort_order_3,30);
321
322 END IF;
323 --
324 IF gb_debug THEN
325 hr_utility.set_location('Leaving '||lc_procedure,1000);
326 END IF;
327 --
328 EXCEPTION
329 WHEN gc_exception THEN
330 IF gb_debug THEN
331 hr_utility.set_location('Error in '||lc_procedure,999999);
332 END IF;
333 RAISE;
334 WHEN OTHERS THEN
335 RAISE gc_exception;
336 END initialize;
337 --
338 PROCEDURE range_cursor( p_payroll_action_id IN NUMBER
339 , p_sqlstr OUT NOCOPY VARCHAR2
340 )
341 --************************************************************************
342 -- PROCEDURE
343 -- range_cursor
344 --
345 -- DESCRIPTION
346 -- This procedure defines a SQL statement to fetch all the people to be
347 -- included in the report.This SQL statement is used to define the
348 -- 'chunks' for multi-threaded operation.
349 --
350 -- ACCESS
351 -- PUBLIC
352 --
353 -- PARAMETERS
354 -- ==========
355 -- NAME TYPE DESCRIPTION
356 -- ----------------- -------- ------------------------------------
357 -- p_payroll_action_id IN This parameter passes payroll_action_id object
358 -- p_sqlstr OUT This parameter returns the SQL Statement
359 --
360 -- PREREQUISITES
361 -- None
362 --
363 -- CALLED BY
364 -- None
365 --************************************************************************
366 AS
367 lc_proc_name VARCHAR2(100);
368 BEGIN
369 --
370 lc_proc_name := gc_pkg_name ||'RANGE_CURSOR';
371 gb_debug := hr_utility.debug_enabled;
372 --
373 IF gb_debug THEN
374 hr_utility.trace ('Entering '||lc_proc_name);
375 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
376 END IF;
377 --
378 initialize(p_payroll_action_id);
379 --
380 p_sqlstr := ' select distinct p.person_id'||
381 ' from per_people_f p,'||
382 ' pay_payroll_actions pa'||
383 ' where pa.payroll_action_id = :payroll_action_id'||
384 ' and p.business_group_id = pa.business_group_id'||
385 ' order by p.person_id ';
386 g_mag_payroll_action_id:=P_PAYROLL_ACTION_ID;
387 --
388 IF gb_debug THEN
389 hr_utility.trace ('Range cursor query : ' || p_sqlstr);
390 hr_utility.trace ('Leaving '||lc_proc_name);
391 END IF;
392 --
393 END range_cursor;
394 --
395 PROCEDURE sort_action( p_payroll_action_id IN NUMBER
396 ,sqlstr IN OUT NOCOPY VARCHAR2
397 ,len OUT NOCOPY NUMBER
398 )
399 --************************************************************************
400 -- PROCEDURE
401 -- sort_action
402 --
403 -- DESCRIPTION
404 -- This procedure sorts the assignments actions according to the user entered
405 -- sort orders 1,2,3.
406 --
407 -- ACCESS
408 -- PUBLIC
409 --
410 -- PARAMETERS
411 -- ==========
412 -- NAME TYPE DESCRIPTION
413 -- ----------------- -------- ------------------------------------
414 -- p_payroll_action_id IN This parameter passes payroll_action_id object
415 -- sqlstr IN OUT This parameter returns the SQL Statement
416 -- len OUT This parameter returns the length of the SQL Statement
417 -- PREREQUISITES
418 -- None
419 --
420 -- CALLED BY
421 -- None
422 --************************************************************************
423 AS
424
425 BEGIN
426 --
427 gb_debug := hr_utility.debug_enabled;
428 --
429 IF gb_debug THEN
430 hr_utility.set_location('Entering sort_action procedure',20);
431 END IF;
432 --
433 hr_utility.trace('Beginning of the sort_action cursor');
434 --
435 initialize(p_payroll_action_id);
436 -- Bug 8691511
437 sqlstr := ' SELECT PAA.rowid
438 FROM per_assignments_f PAF
439 ,pay_assignment_actions PAA
440 ,per_people_f PPF
441 ,hr_all_organization_units_tl HAOUT
442 ,hr_all_organization_units HAOU
443 ,per_periods_of_service PPS
444 WHERE PAA.payroll_action_id = :pactid
445 AND PAF.assignment_id = PAA.assignment_id
446 AND PPF.person_id = PAF.person_id
447 AND PAF.organization_id = HAOU.organization_id
448 AND HAOUT.organization_id = HAOU.organization_id
449 AND HAOUT.language = USERENV(''LANG'')
450 AND PPS.period_of_service_id = PAF.period_of_service_id
451 AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PPF.effective_start_date
452 AND PPF.effective_end_date
453 AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PAF.effective_start_date
454 AND PAF.effective_end_date
455 /*AND (( NVL('''||gr_parameters.incl_term_emp||''',''N'') = ''Y''
456 AND( PPS.actual_termination_date IS NULL
457 OR (TRUNC(PPS.actual_termination_date) BETWEEN '''||gr_parameters.term_eff_date_from||'''
458 AND '''||gr_parameters.term_eff_date_to||''')
459 )
460 )
461 OR
462 ( NVL('''||gr_parameters.incl_term_emp||''',''N'') = ''N''
463 AND PPS.actual_termination_date IS NULL
464 )
465 )*/
466 ORDER BY DECODE('''||gr_parameters.sort_order_1||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name) -- changed by RDARASI for BUG# 8774489
467 ,''ORGANIZATION_CODE'',UPPER(HAOUT.name) -- Added UPPER by RDARASI for BUG# 8774489
468 ,UPPER(PPF.employee_number) -- Added UPPER by RDARASI for BUG# 8774489
469 )
470 ,DECODE('''||gr_parameters.sort_order_2||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name) -- changed by RDARASI for BUG# 8774489
471 ,''ORGANIZATION_CODE'',UPPER(HAOUT.name) -- Added UPPER by RDARASI for BUG# 8774489
472 ,UPPER(PPF.employee_number) -- Added UPPER by RDARASI for BUG# 8774489
473 )
474 ,DECODE('''||gr_parameters.sort_order_3||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name) -- changed by RDARASI for BUG# 8774489
475 ,''ORGANIZATION_CODE'',UPPER(HAOUT.name) -- Added UPPER by RDARASI for BUG# 8774489
476 ,UPPER(PPF.employee_number) -- Added UPPER by RDARASI for BUG# 8774489
477 )';
478 --Bug 8691511
479 len := length(sqlstr); -- return the length of the string.
480 IF gb_debug
481 THEN
482 hr_utility.trace('End of the sort_Action cursor');
483 END IF;
484 --
485 EXCEPTION WHEN NO_DATA_FOUND THEN
486 IF gb_debug THEN
487 hr_utility.trace('Error in Sort Procedure - getting legislative param');
488 END IF;
489 RAISE;
490 --
491 END sort_action;
492 --
493 FUNCTION range_person_on
494 --************************************************************************
495 -- FUNCTION
496 -- range_person_on
497 --
498 -- DESCRIPTION
499 -- Checks if RANGE_PERSON_ID is enabled for
500 -- Archive process.
501 --
502 -- ACCESS
503 -- PRIVATE
504 --
505 -- PREREQUISITES
506 -- None
507 --
508 -- CALLED BY
509 -- action_creation
510 --************************************************************************
511 RETURN BOOLEAN
512 IS
513 --
514 CURSOR lcu_action_parameter
515 IS
516 SELECT parameter_value
517 FROM pay_action_parameters
518 WHERE parameter_name = 'RANGE_PERSON_ID';
519 --
520 lb_return BOOLEAN;
521 lc_action_param_val VARCHAR2(30);
522 --
523 BEGIN
524 --
525 gb_debug := hr_utility.debug_enabled;
526 --
527 IF gb_debug THEN
528 hr_utility.set_location('Entering range_person_on',10);
529 END IF;
530 --
531 OPEN lcu_action_parameter;
532 FETCH lcu_action_parameter INTO lc_action_param_val;
533 CLOSE lcu_action_parameter;
534 --
535 IF lc_action_param_val = 'Y' THEN
536 lb_return := TRUE;
537 IF gb_debug THEN
538 hr_utility.set_location('Range Person = True',10);
539 END IF;
540 ELSE
541 lb_return := FALSE;
542 END IF;
543 --
544 IF gb_debug THEN
545 hr_utility.set_location('Leaving range_person_on',10);
546 END IF;
547 RETURN lb_return;
548 --
549 EXCEPTION WHEN NO_DATA_FOUND THEN
550 IF gb_debug THEN
551 hr_utility.set_location('No Data Found Exception in range_person_on',10);
552 END IF;
553 lb_return := FALSE;
554 RETURN lb_return;
555 END range_person_on;
556 --
557 PROCEDURE action_creation( p_payroll_action_id IN NUMBER
558 , p_start_person_id IN NUMBER
559 , p_end_person_id IN NUMBER
560 , p_chunk IN NUMBER
561 )
562 --************************************************************************
563 -- PROCEDURE
564 -- action_creation
565 --
566 -- DESCRIPTION
567 -- This procedure defines a SQL statement to fetch all the people to be
568 -- included in the report.This SQL statement is used to define the
569 -- 'chunks' for multi-threaded operation.
570 --
571 -- ACCESS
572 -- PUBLIC
573 --
574 -- PARAMETERS
575 -- ==========
576 -- NAME TYPE DESCRIPTION
577 -- ----------------- -------- ------------------------------------
578 -- p_payroll_action_id IN This parameter passes Payroll Action ID
579 -- p_start_person_id IN This parameter passes Start Person ID
580 -- p_end_person_id IN This parameter passes End Person ID
581 -- p_chunk IN This parameter passes Chunk value
582 --
583 -- PREREQUISITES
584 -- None
585 --
586 -- CALLED BY
587 -- None
588 --************************************************************************
589 AS
590 --
591 CURSOR lcu_assact_r( p_payroll_action_id_arch pay_payroll_actions.payroll_action_id%TYPE
592 ,p_business_group_id per_assignments_f.business_group_id%TYPE
593 ,p_organization_id per_assignments_f.organization_id%TYPE
594 ,p_location_id per_assignments_f.location_id%TYPE
595 ,p_effective_date DATE
596 ,p_term_eff_date_from DATE
597 ,p_term_eff_date_to DATE
598 ,p_include_term_flag VARCHAR2
599 )
600 IS
601 SELECT PJWREV.assignment_id
602 ,PJWREV.effective_date
603 FROM per_assignments_f PAA
604 ,per_people_f PAP
605 ,pay_assignment_actions PAS
606 ,per_jp_wrkreg_emp_v PJWREV
607 ,per_periods_of_service PPOF
608 ,pay_population_ranges PPR
609 ,pay_payroll_actions PPA
610 ,hr_all_organization_units HAOU
611 WHERE PAA.person_id = PAP.person_id
612 AND PPA.payroll_action_id = PPR.payroll_action_id
613 AND PPA.payroll_action_id = p_payroll_action_id
614 AND PPR.chunk_number = p_chunk
615 AND PPR.person_id = PAP.person_id
616 AND PAS.assignment_id = PAA.assignment_id
617 AND HAOU.organization_id = PAA.organization_id
618 AND PAS.payroll_action_id = p_payroll_action_id_arch
619 AND PPOF.person_id = PAP.person_id
620 AND PJWREV.assignment_action_id = PAS.assignment_action_id
621 AND PJWREV.assignment_id = PAS.assignment_id
622 AND PAA.business_group_id = p_business_group_id
623 AND PAA.organization_id = NVL(p_organization_id,PAA.organization_id)
624 AND NVL(PAA.location_id,0) = NVL(p_location_id,NVL( PAA.location_id,0))
625 AND PAA.primary_flag = 'Y'
626 AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
627 AND PAP.effective_end_date
628 AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAA.effective_start_date
629 AND PAA.effective_end_date
630 AND (( NVL(p_include_term_flag,'N') = 'Y'
631 AND( (PJWREV.terminate_flag = 'C'
632 AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
633 OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START )))
634 OR ( PJWREV.terminate_flag = 'T'
635 AND TRUNC(PPOF.actual_termination_date) BETWEEN p_term_eff_date_from
636 AND p_term_eff_date_to)
637 )
638 )
639 OR
640 ( NVL(p_include_term_flag,'N') = 'N'
641 AND PJWREV.terminate_flag = 'C'
642 AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
643 OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START ))
644 )
645 );
646 --
647 CURSOR lcu_assact( p_payroll_action_id_arch pay_payroll_actions.payroll_action_id%TYPE
648 ,p_business_group_id per_assignments_f.business_group_id%TYPE
649 ,p_organization_id per_assignments_f.organization_id%TYPE
650 ,p_location_id per_assignments_f.location_id%TYPE
651 ,p_effective_date DATE
652 ,p_term_eff_date_from DATE
653 ,p_term_eff_date_to DATE
654 ,p_include_term_flag VARCHAR2
655 )
656 IS
657 SELECT PJWREV.assignment_id
658 ,PJWREV.effective_date
659 FROM per_assignments_f PAA
660 ,per_people_f PAP
661 ,pay_assignment_actions PAS
662 ,per_jp_wrkreg_emp_v PJWREV
663 ,per_periods_of_service PPOF
664 ,hr_all_organization_units HAOU
665 WHERE PAA.person_id = PAP.person_id
666 AND PAA.person_id BETWEEN p_start_person_id
667 AND p_end_person_id
668 AND PAS.assignment_id = PAA.assignment_id
669 AND PAS.payroll_action_id = p_payroll_action_id_arch
670 AND PPOF.person_id = PAP.person_id
671 AND PJWREV.assignment_action_id = PAS.assignment_action_id
672 AND PJWREV.assignment_id = PAS.assignment_id
673 AND HAOU.organization_id = PAA.organization_id
674 AND PAA.business_group_id = p_business_group_id
675 AND PAA.organization_id = NVL(p_organization_id,PAA.organization_id)
676 AND NVL(PAA.location_id,0) = NVL(p_location_id,NVL( PAA.location_id,0))
677 AND PAA.primary_flag = 'Y'
678 AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
679 AND PAP.effective_end_date
680 AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAA.effective_start_date
681 AND PAA.effective_end_date
682 AND (( NVL(p_include_term_flag,'N') = 'Y'
683 AND( (PJWREV.terminate_flag = 'C'
684 AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
685 OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START )))
686 OR ( PJWREV.terminate_flag = 'T'
687 AND TRUNC(PPOF.actual_termination_date) BETWEEN p_term_eff_date_from
688 AND p_term_eff_date_to)
689 )
690 )
691 OR
692 ( NVL(p_include_term_flag,'N') = 'N'
693 AND PJWREV.terminate_flag = 'C'
694 AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
695 OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START ))
696 )
697 );
698 --
699 ln_assact pay_assignment_actions.assignment_action_id%TYPE ;
700 lc_proc_name VARCHAR2(60);
701 lc_legislative_parameters VARCHAR2(2000);
702 lc_result1 VARCHAR2(30);
703 ln_old_pact_id NUMBER;
704 ln_cur_pact NUMBER;
705 ln_ass_set_id NUMBER;
706 ln_formula_id NUMBER;
707 lb_result2 BOOLEAN;
708 lc_include_flag VARCHAR2(1);
709 lt_org_id per_jp_report_common_pkg.gt_org_tbl;
710 --
711 BEGIN
712 --
713 gb_debug := hr_utility.debug_enabled;
714 --
715 IF gb_debug
716 THEN
717 hr_utility.trace('Entering ACTION_CREATION');
718 END IF;
719 --
720 SELECT legislative_parameters
721 INTO lc_legislative_parameters
722 FROM pay_payroll_actions
723 WHERE payroll_action_id = p_payroll_action_id;
724 --
725 ln_old_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',lc_legislative_parameters));
726 ln_ass_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASS_SETID',lc_legislative_parameters));
727 --
728 g_mag_payroll_action_id:=p_payroll_action_id;
729 gb_debug :=hr_utility.debug_enabled ;
730 --
731
732 IF gb_debug THEN
733 lc_proc_name := gc_pkg_name ||'ACTION_CREATION';
734 hr_utility.trace ('Entering '||lc_proc_name);
735 hr_utility.trace ('Parameters ....');
736 hr_utility.trace ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id);
737 hr_utility.trace ('P_START_PERSON_ID = '|| p_start_person_id);
738 hr_utility.trace ('P_END_PERSON_ID = '|| p_end_person_id);
739 hr_utility.trace ('P_CHUNK = '|| p_chunk);
740 END IF;
741 --
742 initialize(g_mag_payroll_action_id);
743 --
744 IF gr_parameters.organization_id IS NOT NULL THEN
745 -- Getting Organization ID's as per hierarchy
746 lt_org_id := per_jp_report_common_pkg.get_org_hirerachy(p_business_group_id => gr_parameters.business_group_id
747 ,p_organization_id => gr_parameters.organization_id
748 ,p_include_org_hierarchy => gr_parameters.include_org_hierarchy
749 );
750 FOR i in 1..lt_org_id.COUNT
751 LOOP
752 --
753 IF range_person_on THEN
754 -- Range person is enabled
755 IF gb_debug THEN
756 hr_utility.set_location('Inside Range person if condition',20);
757 END IF;
758 -- Assignment Action for Current and Terminated Employees
759 FOR lr_assact IN lcu_assact_r(ln_old_pact_id
760 ,gr_parameters.business_group_id
761 ,lt_org_id(i)
762 ,gr_parameters.location_id
763 ,gr_parameters.effective_date
764 ,gr_parameters.term_eff_date_from
765 ,gr_parameters.term_eff_date_to
766 ,gr_parameters.incl_term_emp
767 )
768 LOOP
769 -- Added NVL to overcome NULL issue.
770 --
771 IF (NVL(ln_ass_set_id ,0) = 0) THEN
772 -- NO assignment set passed as parameter
773 hr_utility.trace ('ass_id = '||lr_assact.assignment_id);
774 --
775 SELECT pay_assignment_actions_s.nextval
776 INTO ln_assact
777 FROM dual;
778 hr_nonrun_asact.insact(ln_assact
779 ,lr_assact.assignment_id
780 ,p_payroll_action_id
781 ,p_chunk
782 ,NULL
783 );
784 ELSE
785 -- assignment set is pa ssed as parameter
786 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id =>ln_ass_set_id
787 ,p_assignment_id => lr_assact.assignment_id
788 ,p_effective_date => gr_parameters.effective_date
789 );
790 IF lc_include_flag = 'Y' THEN
791 --
792 SELECT pay_assignment_actions_s.nextval
793 INTO ln_assact
794 FROM dual;
795 hr_nonrun_asact.insact(ln_assact
796 ,lr_assact.assignment_id
797 ,p_payroll_action_id
798 ,p_chunk
799 ,NULL
800 );
801
802 END IF;
803 END IF;
804 END LOOP;-- End loop for assignment details cursor
805 ELSE
806 -- Range person is not enabled
807 IF gb_debug THEN
808 hr_utility.set_location('Range person returns false',20);
809 hr_utility.set_location(ln_old_pact_id,20);
810 hr_utility.set_location(lt_org_id(i),20);
811 END IF;
812 -- Assignment Action for Current and Terminated Employe
813 FOR lr_assact IN lcu_assact(ln_old_pact_id
814 ,gr_parameters.business_group_id
815 ,lt_org_id(i)
816 ,gr_parameters.location_id
817 ,gr_parameters.effective_date
818 ,gr_parameters.term_eff_date_from
819 ,gr_parameters.term_eff_date_to
820 ,gr_parameters.incl_term_emp
821 )
822 LOOP
823 -- Added NVL to overcome NULL issue.
824 --
825 IF (NVL(ln_ass_set_id ,0) = 0) THEN
826 -- NO assignment set passed as parameter
827 hr_utility.trace ('ass_id = '||lr_assact.assignment_id);
828 --
829 SELECT pay_assignment_actions_s.nextval
830 INTO ln_assact
831 FROM dual;
832 hr_nonrun_asact.insact(ln_assact
833 ,lr_assact.assignment_id
834 ,p_payroll_action_id
835 ,p_chunk
836 ,NULL
837 );
838 ELSE
839 -- assignment set is pa ssed as parameter
840 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id =>ln_ass_set_id
841 ,p_assignment_id => lr_assact.assignment_id
842 ,p_effective_date => gr_parameters.effective_date
843 );
844 IF lc_include_flag = 'Y' THEN
845 --
846 SELECT pay_assignment_actions_s.nextval
847 INTO ln_assact
848 FROM dual;
849 hr_nonrun_asact.insact(ln_assact
850 ,lr_assact.assignment_id
851 ,p_payroll_action_id
852 ,p_chunk
853 ,NULL
854 );
855
856 END IF;
857 END IF;
858 END LOOP;-- End loop for assignment details cursor
859 END IF; -- End If for range_person_on
860 END LOOP;--End Loop for Organization
861 --
862 ELSE--Org id is null
863 IF range_person_on THEN
864 -- Assignment Action for Current and Terminated Employees
865 IF gb_debug THEN
866 hr_utility.set_location('Inside Range person if condition',20);
867 END IF;
868 -- Assignment Action for Current and Terminated Employees
869 --
870 FOR lr_assact IN lcu_assact_r(ln_old_pact_id
871 ,gr_parameters.business_group_id
872 ,null
873 ,gr_parameters.location_id
874 ,gr_parameters.effective_date
875 ,gr_parameters.term_eff_date_from
876 ,gr_parameters.term_eff_date_to
877 ,gr_parameters.incl_term_emp
878 )
879 LOOP
880 -- Added NVL to overcome NULL issue.
881 IF (NVL(ln_ass_set_id ,0) = 0) THEN
882 -- NO assignment set passed as parameter
883 hr_utility.trace ('ass_id = '||lr_assact.assignment_id);
884 --
885 SELECT pay_assignment_actions_s.nextval
886 INTO ln_assact
887 FROM dual;
888 hr_nonrun_asact.insact(ln_assact
889 ,lr_assact.assignment_id
890 ,p_payroll_action_id
891 ,p_chunk
892 ,NULL
893 );
894 ELSE
895 -- assignment set is pa ssed as parameter
896 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id =>ln_ass_set_id
897 ,p_assignment_id => lr_assact.assignment_id
898 ,p_effective_date => gr_parameters.effective_date
899 );
900 IF lc_include_flag = 'Y' THEN
901 --
902 SELECT pay_assignment_actions_s.nextval
903 INTO ln_assact
904 FROM dual;
905 hr_nonrun_asact.insact(ln_assact
906 ,lr_assact.assignment_id
907 ,p_payroll_action_id
908 ,p_chunk
909 ,NULL
910 );
911
912 END IF;
913 END IF;
914 END LOOP;-- End loop for assignment details cursor
915 --
916 ELSE
917 IF gb_debug THEN
918 hr_utility.set_location('Range person returns false',20);
919 END IF;
920 -- Assignment Action for Current and Terminated Employe
921 --
922 FOR lr_assact IN lcu_assact(ln_old_pact_id
923 ,gr_parameters.business_group_id
924 ,null
925 ,gr_parameters.location_id
926 ,gr_parameters.effective_date
927 ,gr_parameters.term_eff_date_from
928 ,gr_parameters.term_eff_date_to
929 ,gr_parameters.incl_term_emp
930 )
931 LOOP
932 -- Added NVL to overcome NULL issue.
933 IF (NVL(ln_ass_set_id ,0) = 0) THEN
934 -- NO assignment set passed as parameter
935 hr_utility.trace ('ass_id = '||lr_assact.assignment_id);
936 --
937 SELECT pay_assignment_actions_s.nextval
938 INTO ln_assact
939 FROM dual;
940 hr_nonrun_asact.insact(ln_assact
941 ,lr_assact.assignment_id
942 ,p_payroll_action_id
943 ,p_chunk
944 ,NULL
945 );
946 ELSE
947 -- assignment set is pa ssed as parameter
948 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id =>ln_ass_set_id
949 ,p_assignment_id => lr_assact.assignment_id
950 ,p_effective_date => gr_parameters.effective_date
951 );
952 IF lc_include_flag = 'Y' THEN
953 --
954 SELECT pay_assignment_actions_s.nextval
955 INTO ln_assact
956 FROM dual;
957 hr_nonrun_asact.insact(ln_assact
958 ,lr_assact.assignment_id
959 ,p_payroll_action_id
960 ,p_chunk
961 ,NULL
962 );
963
964 END IF;
965 END IF;
966 END LOOP;-- End loop for assignment details cursor
967 --
968 END IF; -- End If for range_person_on
969 END IF;
970 --
971 IF gb_debug
972 THEN
973 hr_utility.trace('Leaving ACTION_CREATION');
974 END IF;
975 --
976 END action_creation;
977 --
978 PROCEDURE init_code ( p_payroll_action_id IN NUMBER)
979 --************************************************************************
980 -- PROCEDURE
981 -- init_code
982 --
983 -- DESCRIPTION
984 -- None
985 --
986 -- ACCESS
987 -- PUBLIC
988 --
989 -- PARAMETERS
990 -- ==========
991 -- NAME TYPE DESCRIPTION
992 -- ----------------- -------- ------------------------------------
993 -- p_payroll_action_id IN This parameter passes Payroll Action ID
994 --
995 -- PREREQUISITES
996 -- None
997 --
998 -- CALLED BY
999 -- None
1000 --************************************************************************
1001 IS
1002 BEGIN
1003 gb_debug := hr_utility.debug_enabled;
1004 --
1005 IF gb_debug
1006 THEN
1007 hr_utility.trace ('inside INIT_CODE ');
1008 END IF;
1009 --
1010 g_mag_payroll_action_id:=p_payroll_action_id;
1011 --
1012 END init_code;
1013 --
1014 PROCEDURE archive_code ( p_assignment_action_id IN NUMBER
1015 , p_effective_date IN DATE
1016 )
1017 --************************************************************************
1018 -- PROCEDURE
1019 -- archive_code
1020 --
1021 -- DESCRIPTION
1022 -- None
1023 --
1024 -- ACCESS
1025 -- PUBLIC
1026 --
1027 -- PARAMETERS
1028 -- ==========
1029 -- NAME TYPE DESCRIPTION
1030 -- ----------------- -------- -----------------------------------
1031 -- p_assignment_action_id IN This parameter passes Payroll Action ID
1032 -- p_effective_date IN This parameter passes Effective Date
1033 --
1034 -- PREREQUISITES
1035 -- None
1036 --
1037 -- CALLED BY
1038 -- None
1039 --************************************************************************
1040 IS
1041 BEGIN
1042 --
1043 gb_debug := hr_utility.debug_enabled;
1044 --
1045 IF gb_debug
1046 THEN
1047 hr_utility.trace ('inside ARCHIVE_CODE ');
1048 END IF;
1049 --
1050 END archive_code;
1051 --
1052 PROCEDURE assact_xml(p_assignment_action_id IN NUMBER)
1053 --************************************************************************
1054 -- PROCEDURE
1055 -- assact_xml
1056 --
1057 -- DESCRIPTION
1058 -- This procedure creates xml for the assignment_action_id passed
1059 -- as parameter. It then writes the xml into vXMLTable.
1060 --
1061 -- ACCESS
1062 -- PUBLIC
1063 --
1064 -- PARAMETERS
1065 -- ==========
1066 -- NAME TYPE DESCRIPTION
1067 -- ----------------- -------- -----------------------------------
1068 -- p_assignment_action_id IN This parameter passes Payroll Action ID
1069 --
1070 -- PREREQUISITES
1071 -- None
1072 --
1073 -- CALLED BY
1074 -- None
1075 --************************************************************************
1076 IS
1077 --Cursor to pick the employee details
1078 --Bug #8667702.Added space for Address Line1,Address Line2,Address Line3
1079 CURSOR cur_wrk_reg_emp(p_mag_asg_action_id NUMBER
1080 )
1081 IS
1082 SELECT PJWREV.FULL_NAME_KANA
1083 ,PJWREV.FULL_NAME_KANJI
1084 ,PJWREV.DATE_OF_BIRTH
1085 ,DECODE(PJWREV.GENDER,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'SEX' AND lookup_code= PJWREV.GENDER)) GENDER
1086 ,SUBSTR(PJWREV.POSTAL_CODE,1,3)||NVL2(PJWREV.POSTAL_CODE,'-',' ')|| SUBSTR(PJWREV.POSTAL_CODE,4) POSTAL_CODE -- added by rdarasi for Bug #8814071
1087 ,PJWREV.ADDRESS_LINE1||' '||pjwrev.ADDRESS_LINE2||' '||pjwrev.ADDRESS_LINE3 Address
1088 ,PJWREV.REGION1||PJWREV.REGION2||PJWREV.REGION3 Address_kana
1089 ,PJWREV.KIND_OF_BUSINESS
1090 ,PJWREV.HIRE_DATE
1091 ,PJWREV.TERMINATION_DATE
1092 ,DECODE(PJWREV.TERMINATION_REASON,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'LEAV_REAS' AND lookup_code= PJWREV.TERMINATION_REASON)) TERMINATION_REASON
1093 ,PJWREV.DATE_OF_DEATH
1094 ,PJWREV.EMPLOYEE_NUMBER
1095 ,PJWREV.EFFECTIVE_DATE
1096 FROM per_jp_wrkreg_emp_v PJWREV
1097 WHERE PJWREV.assignment_action_id = p_mag_asg_action_id;
1098 --
1099 wrk_reg_emp_c cur_wrk_reg_emp%ROWTYPE;
1100 --
1101 --Cursor to pick the Job history of an employee
1102 CURSOR cur_wrk_reg_job_hist_emp(p_mag_asg_action_id NUMBER
1103 )
1104 IS
1105 SELECT PJWRGHV.POSITION
1106 ,PJWRGHV.JOB
1107 ,PJWRGHV.START_DATE
1108 ,DECODE(PJWRGHV.END_DATE,TO_DATE('12/31/4712','mm/dd/yyyy'),null,PJWRGHV.END_DATE) END_DATE
1109 ,PJWRGHV.ORGANIZATION
1110 FROM per_jp_wrkreg_job_v PJWRGHV
1111 WHERE PJWRGHV.assignment_action_id = p_mag_asg_action_id
1112 ORDER BY PJWRGHV.START_DATE DESC,PJWRGHV.END_DATE DESC;
1113 --
1114 --Cursor to pick the Previous Job History of an Employee
1115 CURSOR cur_wrk_reg_prev_job_hist(p_mag_asg_action_id NUMBER
1116 )
1117 IS
1118 SELECT PJWPJV.COMPANY_NAME
1119 ,PJWPJV.START_DATE
1120 ,PJWPJV.END_DATE
1121 ,PJWPJV.JOB
1122 FROM per_jp_wrkreg_prev_job_v PJWPJV
1123 WHERE pjwpjv.assignment_action_id = p_mag_asg_action_id
1124 ORDER BY PJWPJV.START_DATE DESC,PJWPJV.END_DATE DESC;
1125 --
1126 --Cursor to pick the additional information
1127 CURSOR cur_wrk_add_info(p_mag_asg_action_id NUMBER
1128 )
1129 IS
1130 SELECT PJWRAI.ADDITIONAL_INFORMATION1
1131 ,PJWRAI.ADDITIONAL_INFORMATION2
1132 ,PJWRAI.ADDITIONAL_INFORMATION3
1133 ,PJWRAI.ADDITIONAL_INFORMATION4
1134 ,PJWRAI.ADDITIONAL_INFORMATION5
1135 ,PJWRAI.ADDITIONAL_INFORMATION6
1136 ,PJWRAI.ADDITIONAL_INFORMATION7
1137 ,PJWRAI.ADDITIONAL_INFORMATION8
1138 ,PJWRAI.ADDITIONAL_INFORMATION9
1139 ,PJWRAI.ADDITIONAL_INFORMATION10
1140 ,PJWRAI.ADDITIONAL_INFORMATION11
1141 ,PJWRAI.ADDITIONAL_INFORMATION12
1142 ,PJWRAI.ADDITIONAL_INFORMATION13
1143 ,PJWRAI.ADDITIONAL_INFORMATION14
1144 ,PJWRAI.ADDITIONAL_INFORMATION15
1145 ,PJWRAI.ADDITIONAL_INFORMATION16
1146 ,PJWRAI.ADDITIONAL_INFORMATION17
1147 ,PJWRAI.ADDITIONAL_INFORMATION18
1148 ,PJWRAI.ADDITIONAL_INFORMATION19
1149 ,PJWRAI.ADDITIONAL_INFORMATION20
1150 ,PJWRAI.ADDITIONAL_INFORMATION21
1151 ,PJWRAI.ADDITIONAL_INFORMATION22
1152 ,PJWRAI.ADDITIONAL_INFORMATION23
1153 ,PJWRAI.ADDITIONAL_INFORMATION24
1154 ,PJWRAI.ADDITIONAL_INFORMATION25
1155 ,PJWRAI.ADDITIONAL_INFORMATION26
1156 ,PJWRAI.ADDITIONAL_INFORMATION27
1157 ,PJWRAI.ADDITIONAL_INFORMATION28
1158 ,PJWRAI.ADDITIONAL_INFORMATION29
1159 ,PJWRAI.ADDITIONAL_INFORMATION30
1160 FROM per_jp_wrkreg_extra_info_v PJWRAI
1161 WHERE PJWRAI.assignment_action_id = p_mag_asg_action_id;
1162 --
1163 wrk_reg_add_info cur_wrk_add_info%ROWTYPE;
1164 --
1165 --Cursor to Check if the user has entered the Organization level additional information.
1166 CURSOR cur_wrk_org_add_info(p_info_type VARCHAR2
1167 ,p_business_group_id NUMBER
1168 ,p_effective_date DATE
1169 )
1170 IS
1171 SELECT HOI.org_information3
1172 ,HOI.org_information4
1173 ,HOI.org_information7
1174 FROM hr_organization_information HOI
1175 WHERE HOI.org_information_context = 'JP_REPORTS_ADDITIONAL_INFO'
1176 AND HOI.org_information1 = 'JPEMPLDETAILSREPORT'
1177 AND HOI.organization_id = p_business_group_id
1178 AND HOI.org_information3 = p_info_type
1179 AND p_effective_date BETWEEN fnd_date.canonical_to_date(HOI.org_information5)
1180 AND fnd_date.canonical_to_date(HOI.org_information6);
1181 --
1182 wrk_reg_org_add_info cur_wrk_org_add_info%ROWTYPE;
1183 --
1184 --Variables-----
1185 l_xml CLOB;
1186 l_xml2 CLOB;
1187 l_common_xml CLOB;
1188 l_xml_begin VARCHAR2(200);
1189 l_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1190 l_emp_no VARCHAR2(80);
1191 l_job_hist_xml CLOB ;
1192 l_additional_info_xml CLOB;
1193 l_add_msg_xml CLOB;
1194 l_cnt NUMBER;
1195 l_cnte NUMBER;
1196 --Added for Job History for bug 8608463
1197 TYPE job_hist_type IS RECORD(position per_positions.name%TYPE
1198 ,job per_jobs_tl.name %TYPE
1199 ,organization hr_organization_units.name%TYPE
1200 ,start_date per_assignments_f.effective_start_date%TYPE
1201 ,end_date per_assignments_f.effective_end_date%TYPE
1202 );
1203
1204 TYPE prev_job_hist_type IS RECORD(company_name per_previous_employers.employer_name%TYPE
1205 ,start_date per_assignments_f.effective_start_date%TYPE
1206 ,end_date per_assignments_f.effective_end_date%TYPE
1207 ,job per_jobs_tl.name %TYPE
1208 );
1209 TYPE gt_job_tbl IS TABLE of job_hist_type INDEX BY binary_integer;
1210 TYPE gt_prev_job_tbl IS TABLE of prev_job_hist_type INDEX BY binary_integer;
1211 lt_job_tbl gt_job_tbl;
1212 lt_prev_job_tbl gt_prev_job_tbl;
1213 l_index NUMBER;
1214 l_prev_job_count NUMBER;
1215 l_curr_job_count NUMBER;
1216 --End for bug 8608463
1217 --
1218 BEGIN
1219 --
1220 gb_debug := hr_utility.debug_enabled;
1221 --
1222 IF gb_debug THEN
1223 hr_utility.trace ('Entering assact_xml');
1224 END IF;
1225 --
1226 vXMLTable.DELETE;
1227 gn_vctr := 0;
1228 l_job_hist_xml:=null;
1229 l_index:=0;
1230 --
1231 IF gb_debug THEN
1232 hr_utility.trace('wrkreg_xml');
1233 END IF;
1234 --
1235 l_mag_asg_action_id :=p_assignment_action_id;
1236 --
1237 initialize(g_mag_payroll_action_id);
1238 --
1239 -- Fetching the employee details.
1240 OPEN cur_wrk_reg_emp(l_mag_asg_action_id
1241 );
1242 FETCH cur_wrk_reg_emp INTO wrk_reg_emp_c;
1243 --
1244 IF cur_wrk_reg_emp%FOUND THEN
1245 --
1246 l_xml_begin := '<wrkreg>'||gc_eol;
1247 vXMLTable(gn_vctr).xmlstring := l_xml_begin;
1248 gn_vctr := gn_vctr + 1;
1249 --
1250 l_common_xml := '<c1>' ||htmlspchar(cnv_str(wrk_reg_emp_c.FULL_NAME_KANA))||'</c1>' ||gc_eol --Full Name Kana
1251 ||'<c2>'||htmlspchar(cnv_str(wrk_reg_emp_c.FULL_NAME_KANJI))||'</c2>' ||gc_eol --Full Name Kanji
1252 ||'<c3>' ||htmlspchar(cnv_str(wrk_reg_emp_c.KIND_OF_BUSINESS))||'</c3>' ||gc_eol --Kind of Employ Business
1253 ||'<c4_m>'||TO_CHAR(wrk_reg_emp_c.DATE_OF_BIRTH,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.DATE_OF_BIRTH,'hh:mm:ss-HH:MM')||'</c4_m>' ||gc_eol -- Date Of Birth --For Bug 8608463 ,8686503
1254 ||'<c4_d>'||TO_CHAR(wrk_reg_emp_c.DATE_OF_BIRTH,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.DATE_OF_BIRTH,'hh:mm:ss-HH:MM')||'</c4_d>' ||gc_eol
1255 ||'<c4_era>'||TO_CHAR(wrk_reg_emp_c.DATE_OF_BIRTH, 'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</c4_era>' ||gc_eol -- Date Of Birth Era Format --For Bug 8608463
1256 ||'<c5>' ||htmlspchar(cnv_str(wrk_reg_emp_c.GENDER)) ||'</c5>' ||gc_eol --Gender
1257 ||'<c6>' ||htmlspchar(cnv_str(wrk_reg_emp_c.POSTAL_CODE)) ||'</c6>' ||gc_eol --Postal Code
1258 ||'<c7>' ||htmlspchar(cnv_str(wrk_reg_emp_c.Address)) ||'</c7>' ||gc_eol --Address
1259 ||'<c8>' ||htmlspchar(cnv_str(wrk_reg_emp_c.Address_Kana)) ||'</c8>' ||gc_eol --Address Kana
1260 ||'<c9_m>' ||TO_CHAR(wrk_reg_emp_c.HIRE_DATE,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.HIRE_DATE,'hh:mm:ss-HH:MM')||'</c9_m>' ||gc_eol --Hire Date --For Bug 8608463 , 8686503
1261 ||'<c9_d>' ||TO_CHAR(wrk_reg_emp_c.HIRE_DATE,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.HIRE_DATE,'hh:mm:ss-HH:MM')||'</c9_d>' ||gc_eol
1262 ||'<c9_era>'||TO_CHAR(wrk_reg_emp_c.HIRE_DATE,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</c9_era>'||gc_eol; -- Hire DateEra Format --For Bug 8608463
1263 --
1264 IF wrk_reg_emp_c.DATE_OF_DEATH IS NULL THEN
1265 l_common_xml := l_common_xml
1266 ||'<c10_m>' ||TO_CHAR(wrk_reg_emp_c.TERMINATION_DATE,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.TERMINATION_DATE,'hh:mm:ss-HH:MM') ||'</c10_m>' ||gc_eol --For Bug 8608463 , 8686503
1267 ||'<c10_d>' ||TO_CHAR(wrk_reg_emp_c.TERMINATION_DATE,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.TERMINATION_DATE,'hh:mm:ss-HH:MM') ||'</c10_d>' ||gc_eol
1268 ||'<c10_era>'||TO_CHAR(wrk_reg_emp_c.TERMINATION_DATE, 'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</c10_era>' ||gc_eol -- Termination DateEra Format --For Bug 8608463
1269 ||'<c11>' ||htmlspchar(cnv_str(wrk_reg_emp_c.TERMINATION_REASON)) ||'</c11>' ||gc_eol;
1270 ELSE
1271 l_common_xml := l_common_xml
1272 ||'<c10_m>' ||TO_CHAR(wrk_reg_emp_c.DATE_OF_DEATH,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.DATE_OF_DEATH,'hh:mm:ss-HH:MM') ||'</c10_m>' ||gc_eol --For Bug 8608463 , 8686503
1273 ||'<c10_d>' ||TO_CHAR(wrk_reg_emp_c.DATE_OF_DEATH,'yyyy-mm-dd')||TO_CHAR(wrk_reg_emp_c.DATE_OF_DEATH,'hh:mm:ss-HH:MM') ||'</c10_d>' ||gc_eol --For Bug 8608463 , 8686503
1274 ||'<c10_era>'||TO_CHAR(wrk_reg_emp_c.DATE_OF_DEATH,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</c10_era>' ||gc_eol -- Date Of Death Era Format --For Bug 8608463
1275 ||'<c11></c11>'||gc_eol;
1276 END IF;
1277 --
1278 l_common_xml := l_common_xml||'<c12_m>' ||TO_CHAR(wrk_reg_emp_c.EFFECTIVE_DATE,'yyyy-mm-dd') ||TO_CHAR(wrk_reg_emp_c.EFFECTIVE_DATE,'hh:mm:ss-HH:MM')||'</c12_m>' ||gc_eol --For Bug 8608463 , 8686503
1279 ||'<c12_d>' ||TO_CHAR(wrk_reg_emp_c.EFFECTIVE_DATE,'yyyy-mm-dd') ||TO_CHAR(wrk_reg_emp_c.EFFECTIVE_DATE,'hh:mm:ss-HH:MM')||'</c12_d>' ||gc_eol --For Bug 8608463 , 8686503
1280 ||'<c12_era>'||TO_CHAR(wrk_reg_emp_c.EFFECTIVE_DATE,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</c12_era>' ||gc_eol -- Effective Date --For Bug 8608463
1281 ||'<c13>' ||htmlspchar(cnv_str(wrk_reg_emp_c.EMPLOYEE_NUMBER)) ||'</c13>' ||gc_eol;
1282 --
1283 l_cnt:=1;
1284 --
1285 -- Added for job history in bug 8608463
1286 FOR lr_prev_job IN cur_wrk_reg_prev_job_hist(l_mag_asg_action_id)
1287 LOOP
1288 IF cur_wrk_reg_prev_job_hist%ROWCOUNT<4 THEN
1289 l_index:=l_index+1;
1290 lt_prev_job_tbl(l_index).company_name:=lr_prev_job.COMPANY_NAME;
1291 lt_prev_job_tbl(l_index).start_date:=lr_prev_job.START_DATE;
1292 lt_prev_job_tbl(l_index).end_date:=lr_prev_job.END_DATE;
1293 lt_prev_job_tbl(l_index).job:=lr_prev_job.JOB;
1294 END IF;
1295 END LOOP;
1296 --
1297 l_prev_job_count:=l_index;
1298 --
1299 IF l_index <> 0 THEN
1300 FOR i IN REVERSE 1..l_index
1301 LOOP
1302 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'_m>'||TO_CHAR(lt_prev_job_tbl(i).start_date,'yyyy-mm-dd')
1303 ||TO_CHAR(lt_prev_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_m>'||gc_eol
1304 ||'<cj'||l_cnt||'_d>'||TO_CHAR(lt_prev_job_tbl(i).start_date,'yyyy-mm-dd')
1305 ||TO_CHAR(lt_prev_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_d>'||gc_eol
1306 ||'<cj'||l_cnt||'_era>'||'*'||TO_CHAR(lt_prev_job_tbl(i).start_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cj'||l_cnt||'_era>'||gc_eol;
1307 l_cnt:=l_cnt+1;
1308 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'_m>'||TO_CHAR(lt_prev_job_tbl(i).end_date,'yyyy-mm-dd')
1309 ||TO_CHAR(lt_prev_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_m>'||gc_eol
1310 ||'<cj'||l_cnt||'_d>'||TO_CHAR(lt_prev_job_tbl(i).end_date,'yyyy-mm-dd')
1311 ||TO_CHAR(lt_prev_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_d>'||gc_eol
1312 ||'<cj'||l_cnt||'_era>'||TO_CHAR(lt_prev_job_tbl(i).end_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cj'||l_cnt||'_era>'||gc_eol;
1313 l_cnt:=l_cnt+1;
1314 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_prev_job_tbl(i).company_name))||'</cj'||l_cnt||'>'||gc_eol;
1315 l_cnt:=l_cnt+1;
1316 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_prev_job_tbl(i).job))||'</cj'||l_cnt||'>'||gc_eol;
1317 l_cnt:=l_cnt+1;
1318 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||'</cj'||l_cnt||'>'||gc_eol;
1319 l_cnt:=l_cnt+1;
1320 END LOOP;
1321 END IF;
1322 --
1323 IF l_prev_job_count<3 THEN
1324 l_curr_job_count:=8-(l_prev_job_count);
1325 ELSE
1326 l_curr_job_count:=5;
1327 END IF;
1328 --
1329 l_index:=0;
1330 FOR lr_job_hist IN cur_wrk_reg_job_hist_emp(l_mag_asg_action_id
1331 )
1332 LOOP
1333 l_index:=l_index+1;
1334 lt_job_tbl(l_index).job:=lr_job_hist.JOB;
1335 lt_job_tbl(l_index).position:=lr_job_hist.POSITION;
1336 lt_job_tbl(l_index).organization:=lr_job_hist.ORGANIZATION;
1337 lt_job_tbl(l_index).start_date:=lr_job_hist.START_DATE;
1338 lt_job_tbl(l_index).end_date:=lr_job_hist.END_DATE;
1339 END LOOP;
1340 --
1341 IF (l_index<5 OR (l_index < l_curr_job_count)) AND (l_index <> 0) THEN
1342 FOR i IN REVERSE 1..l_index
1343 LOOP
1344 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'_m>'||TO_CHAR(lt_job_tbl(i).start_date,'yyyy-mm-dd')
1345 ||TO_CHAR(lt_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_m>'||gc_eol
1346 ||'<cj'||l_cnt||'_d>'||TO_CHAR(lt_job_tbl(i).start_date,'yyyy-mm-dd')
1347 ||TO_CHAR(lt_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_d>'||gc_eol
1348 ||'<cj'||l_cnt||'_era>'||' '||TO_CHAR(lt_job_tbl(i).start_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cj'||l_cnt||'_era>'||gc_eol;
1349 l_cnt:=l_cnt+1;
1350 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'_m>'||TO_CHAR(lt_job_tbl(i).end_date,'yyyy-mm-dd')
1351 ||TO_CHAR(lt_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_m>'||gc_eol
1352 ||'<cj'||l_cnt||'_d>'||TO_CHAR(lt_job_tbl(i).end_date,'yyyy-mm-dd')
1353 ||TO_CHAR(lt_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_d>'||gc_eol
1354 ||'<cj'||l_cnt||'_era>'||TO_CHAR(lt_job_tbl(i).end_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cj'||l_cnt||'_era>'||gc_eol;
1355 l_cnt:=l_cnt+1;
1356 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_job_tbl(i).organization))||'</cj'||l_cnt||'>'||gc_eol;
1357 l_cnt:=l_cnt+1;
1358 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_job_tbl(i).job))||'</cj'||l_cnt||'>'||gc_eol;
1359 l_cnt:=l_cnt+1;
1360 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_job_tbl(i).position))||'</cj'||l_cnt||'>'||gc_eol;
1361 l_cnt:=l_cnt+1;
1362 END LOOP;
1363 ELSE
1364 IF (l_index <> 0) THEN
1365 FOR i IN REVERSE 1..l_curr_job_count
1366 LOOP
1367 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'_m>'||TO_CHAR(lt_job_tbl(i).start_date,'yyyy-mm-dd')
1368 ||TO_CHAR(lt_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_m>'||gc_eol
1369 ||'<cj'||l_cnt||'_d>'||TO_CHAR(lt_job_tbl(i).start_date,'yyyy-mm-dd')
1370 ||TO_CHAR(lt_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_d>'||gc_eol
1371 ||'<cj'||l_cnt||'_era>'||' '||TO_CHAR(lt_job_tbl(i).start_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cj'||l_cnt||'_era>'||gc_eol;
1372 l_cnt:=l_cnt+1;
1373 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'_m>'||TO_CHAR(lt_job_tbl(i).end_date,'yyyy-mm-dd')
1374 ||TO_CHAR(lt_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_m>'||gc_eol
1375 ||'<cj'||l_cnt||'_d>'||TO_CHAR(lt_job_tbl(i).end_date,'yyyy-mm-dd')
1376 ||TO_CHAR(lt_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cj'||l_cnt||'_d>'||gc_eol
1377 ||'<cj'||l_cnt||'_era>'||TO_CHAR(lt_job_tbl(i).end_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cj'||l_cnt||'_era>'||gc_eol;
1378 l_cnt:=l_cnt+1;
1379 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_job_tbl(i).organization))||'</cj'||l_cnt||'>'||gc_eol;
1380 l_cnt:=l_cnt+1;
1381 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_job_tbl(i).job))||'</cj'||l_cnt||'>'||gc_eol;
1382 l_cnt:=l_cnt+1;
1383 l_job_hist_xml:=l_job_hist_xml||'<cj'||l_cnt||'>'||htmlspchar(cnv_str(lt_job_tbl(i).position))||'</cj'||l_cnt||'>'||gc_eol;
1384 l_cnt:=l_cnt+1;
1385 END LOOP;
1386 l_cnte:=1;
1387 FOR i IN REVERSE ( l_curr_job_count+1)..l_index
1388 LOOP
1389 l_job_hist_xml:=l_job_hist_xml||'<cje'||l_cnte||'_m>'||TO_CHAR(lt_job_tbl(i).start_date,'yyyy-mm-dd')
1390 ||TO_CHAR(lt_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cje'||l_cnte||'_m>'||gc_eol
1391 ||'<cje'||l_cnte||'_d>'||TO_CHAR(lt_job_tbl(i).start_date,'yyyy-mm-dd')
1392 ||TO_CHAR(lt_job_tbl(i).start_date,'hh:mm:ss-HH:MM')||'</cje'||l_cnte||'_d>'||gc_eol
1393 ||'<cje'||l_cnte||'_era>'||TO_CHAR(lt_job_tbl(i).start_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cje'||l_cnte||'_era>'||gc_eol;
1394 l_cnte:=l_cnte+1;
1395 l_job_hist_xml:=l_job_hist_xml||'<cje'||l_cnte||'_m>'||TO_CHAR(lt_job_tbl(i).end_date,'yyyy-mm-dd')
1396 ||TO_CHAR(lt_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cje'||l_cnte||'_m>'||gc_eol
1397 ||'<cje'||l_cnte||'_d>'||TO_CHAR(lt_job_tbl(i).end_date,'yyyy-mm-dd')
1398 ||TO_CHAR(lt_job_tbl(i).end_date,'hh:mm:ss-HH:MM')||'</cje'||l_cnte||'_d>'||gc_eol
1399 ||'<cje'||l_cnte||'_era>'||TO_CHAR(lt_job_tbl(i).end_date,'EEYY','NLS_CALENDAR=''Japanese Imperial''')||'</cje'||l_cnte||'_era>'||gc_eol;
1400 l_cnte:=l_cnte+1;
1401 l_job_hist_xml:=l_job_hist_xml||'<cje'||l_cnte||'>'||htmlspchar(cnv_str(lt_job_tbl(i).organization))||'</cje'||l_cnte||'>'||gc_eol;
1402 l_cnte:=l_cnte+1;
1403 l_job_hist_xml:=l_job_hist_xml||'<cje'||l_cnte||'>'||htmlspchar(cnv_str(lt_job_tbl(i).job))||'</cje'||l_cnte||'>'||gc_eol;
1404 l_cnte:=l_cnte+1;
1405 l_job_hist_xml:=l_job_hist_xml||'<cje'||l_cnte||'>'||htmlspchar(cnv_str(lt_job_tbl(i).position))||'</cje'||l_cnte||'>'||gc_eol;
1406 l_cnte:=l_cnte+1;
1407 END LOOP;
1408 END IF;
1409 END IF;
1410 --End For for bug 8608463
1411 l_common_xml:=l_common_xml||l_job_hist_xml;
1412 --
1413 l_xml :=gc_eol||l_common_xml||gc_eol;
1414 --
1415 -- Checking if additional message is entered by the user.
1416 OPEN cur_wrk_org_add_info('MESG'
1417 ,gr_parameters.business_group_id
1418 ,gr_parameters.effective_date
1419 );
1420 FETCH cur_wrk_org_add_info into wrk_reg_org_add_info;
1421 CLOSE cur_wrk_org_add_info;
1422
1423 IF wrk_reg_org_add_info.org_information7 IS NOT NULL THEN
1424 l_add_msg_xml:='<m>'||htmlspchar(cnv_str(wrk_reg_org_add_info.org_information7))||'</m>'||gc_eol;
1425 l_xml :=l_xml||gc_eol||l_add_msg_xml||gc_eol;
1426 END IF;
1427 -- Checking if Additional Information is entered by the user.
1428 OPEN cur_wrk_org_add_info( 'ADDINFO'
1429 ,gr_parameters.business_group_id
1430 ,gr_parameters.effective_date);
1431 FETCH cur_wrk_org_add_info into wrk_reg_org_add_info;
1432 CLOSE cur_wrk_org_add_info;
1433
1434 IF wrk_reg_org_add_info.org_information4 IS NOT NULL THEN
1435 OPEN cur_wrk_add_info( l_mag_asg_action_id
1436 );
1437 FETCH cur_wrk_add_info into wrk_reg_add_info;
1438 CLOSE cur_wrk_add_info;
1439
1440 l_additional_info_xml:='<x1>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION1))||'</x1>' ||gc_eol||
1441 '<x2>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION2))||'</x2>'||gc_eol||
1442 '<x3>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION3))||'</x3>' ||gc_eol||
1443 '<x4>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION4))||'</x4>'||gc_eol||
1444 '<x5>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION5))||'</x5>' ||gc_eol||
1445 '<x6>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION6))||'</x6>'||gc_eol||
1446 '<x7>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION7))||'</x7>' ||gc_eol||
1447 '<x8>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION8))||'</x8>' ||gc_eol||
1448 '<x9>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION9))||'</x9>'||gc_eol||
1449 '<x10>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION10))||'</x10>' ||gc_eol||
1450 '<x11>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION11))||'</x11>' ||gc_eol||
1451 '<x12>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION12))||'</x12>'||gc_eol||
1452 '<x13>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION13))||'</x13>' ||gc_eol||
1453 '<x14>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION14))||'</x14>'||gc_eol||
1454 '<x15>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION15))||'</x15>' ||gc_eol||
1455 '<x16>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION16))||'</x16>'||gc_eol||
1456 '<x17>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION17))||'</x17>' ||gc_eol||
1457 '<x18>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION18))||'</x18>' ||gc_eol||
1458 '<x19>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION19))||'</x19>'||gc_eol||
1459 '<x20>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION20))||'</x20>' ||gc_eol||
1460 '<x21>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION21))||'</x21>' ||gc_eol||
1461 '<x22>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION22))||'</x22>'||gc_eol||
1462 '<x23>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION23))||'</x23>' ||gc_eol||
1463 '<x24>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION24))||'</x24>'||gc_eol||
1464 '<x25>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION25))||'</x25>' ||gc_eol||
1465 '<x26>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION26))||'</x26>'||gc_eol||
1466 '<x27>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION27))||'</x27>' ||gc_eol||
1467 '<x28>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION28))||'</x28>' ||gc_eol||
1468 '<x29>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION29))||'</x29>'||gc_eol||
1469 '<x30>'||htmlspchar(cnv_str(wrk_reg_add_info.ADDITIONAL_INFORMATION30))||'</x30>';
1470
1471 l_xml :=l_xml||gc_eol||l_additional_info_xml||gc_eol;
1472 END IF;
1473 -- writing first part of xml to vXMLtable
1474 vXMLTable(gn_vctr).xmlstring := l_xml;
1475 gn_vctr := gn_vctr + 1;
1476 --
1477 l_xml2 :='</wrkreg>'||gc_eol ;
1478 --
1479 vXMLTable(gn_vctr).xmlstring := l_xml2;
1480 gn_vctr := gn_vctr + 1;
1481 END IF;
1482 CLOSE cur_wrk_reg_emp;
1483 --
1484 IF gb_debug
1485 THEN
1486 hr_utility.trace ('Leaving assact_xml');
1487 END IF;
1488 --
1489 EXCEPTION
1490 WHEN gc_exception THEN
1491 IF gb_debug THEN
1492 hr_utility.set_location('Error in assact_xml ',999999);
1493 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1494 hr_utility.raise_error;
1495 END IF;
1496 WHEN OTHERS THEN
1497 RAISE gc_exception;
1498 END assact_xml;
1499 --
1500 PROCEDURE writetoclob (p_write_xml OUT NOCOPY CLOB)
1501 --************************************************************************
1502 -- PROCEDURE
1503 -- writetoclob
1504 --
1505 -- DESCRIPTION
1506 -- This procedure selects the xml from vxmltable and writes it
1507 -- into a clob variable. This clob variable is then returned
1508 --
1509 -- ACCESS
1510 -- PUBLIC
1511 --
1512 -- PARAMETERS
1513 -- ==========
1514 -- NAME TYPE DESCRIPTION
1515 -- ----------------- -------- ----------------------------------
1516 -- p_write_xml OUT This parameter returns XML String
1517 --
1518 -- PREREQUISITES
1519 -- None
1520 --
1521 -- CALLED BY
1522 -- None
1523 --************************************************************************
1524 IS
1525 g_xfdf_string CLOB;
1526 l_tempclob CLOB;
1527 ln_ctr_table NUMBER;
1528 BEGIN
1529 --
1530 gb_debug := hr_utility.debug_enabled;
1531 --
1532 IF gb_debug
1533 THEN
1534 hr_utility.trace('Entering WRITETOCLOB');
1535 END IF;
1536 --
1537 dbms_lob.createtemporary(g_xfdf_string,FALSE,DBMS_LOB.CALL);
1538 dbms_lob.open(g_xfdf_string,dbms_lob.lob_readwrite);
1539 FOR ln_ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1540 LOOP
1541 dbms_lob.writeAppend(g_xfdf_string
1542 ,LENGTH(vxmltable(ln_ctr_table).xmlstring)
1543 ,vxmltable(ln_ctr_table).xmlstring );
1544 END LOOP;
1545 p_write_xml := g_xfdf_string;
1546 --
1547 IF gb_debug
1548 THEN
1549 hr_utility.set_location('Out of loop ', 99);
1550 END IF;
1551 --
1552 dbms_lob.close(g_xfdf_string);
1553 IF gb_debug
1554 THEN
1555 hr_utility.trace('Leaving WRITETOCLOB');
1556 END IF;
1557 --
1558 EXCEPTION
1559 WHEN gc_exception THEN
1560 IF gb_debug
1561 THEN
1562 hr_utility.set_location('Error in writetoclob ',999999);
1563 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1564 hr_utility.raise_error;
1565 END IF;
1566 WHEN OTHERS THEN
1567 RAISE gc_exception;
1568 --
1569 END writetoclob;
1570 --
1571 PROCEDURE get_cp_xml(p_assignment_action_id IN NUMBER
1572 ,p_xml OUT NOCOPY CLOB
1573 )
1574 --************************************************************************
1575 -- PROCEDURE
1576 -- get_cp_xml
1577 --
1578 -- DESCRIPTION
1579 -- This procedure creates and returns the xml for the
1580 -- assignment_action_id passed as parameter
1581 --
1582 -- ACCESS
1583 -- PUBLIC
1584 --
1585 -- PARAMETERS
1586 -- ==========
1587 -- NAME TYPE DESCRIPTION
1588 -- ----------------- -------- ----------------------------------
1589 -- p_assignment_action_id IN This parameter passes assignment Action ID
1590 -- p_xml OUT This parameter returns XML
1591 --
1592 -- PREREQUISITES
1593 -- None
1594 --
1595 -- CALLED BY
1596 -- None
1597 --************************************************************************
1598 IS
1599 --
1600 BEGIN
1601 --
1602 gb_debug := hr_utility.debug_enabled;
1603 --
1604 IF gb_debug
1605 THEN
1606 hr_utility.trace('Entering GET_CP_XML');
1607 END IF;
1608 --
1609 assact_xml(p_assignment_action_id);
1610 writetoclob (p_xml);
1611 --
1612 IF gb_debug
1613 THEN
1614 hr_utility.trace('Leaving GET_CP_XML');
1615 END IF;
1616 --
1617 END get_cp_xml;
1618 --
1619 PROCEDURE generate_xml
1620 --************************************************************************
1621 -- PROCEDURE
1622 -- generate_xml
1623 --
1624 -- DESCRIPTION
1625 -- This procedure fetches archived data, converts it to XML
1626 -- format and appends to pay_mag_tape.g_clob_value.
1627 --
1628 -- ACCESS
1629 -- PUBLIC
1630 --
1631 -- PARAMETERS
1632 -- ==========
1633 -- None
1634 --
1635 -- PREREQUISITES
1636 -- None
1637 --
1638 -- CALLED BY
1639 -- None
1640 --************************************************************************
1641 AS
1642 l_final_xml_string CLOB;
1643 lc_xml_string1 VARCHAR2(2000);
1644 lc_proc_name VARCHAR2(60);
1645 lc_legislative_parameters VARCHAR(2000);
1646 ln_old_assact_id NUMBER;
1647 ln_pact_id NUMBER;
1648 ln_cur_pact NUMBER;
1649 ln_cur_assact NUMBER ;
1650 ln_offset NUMBER;
1651 ln_amount NUMBER;
1652 --
1653 BEGIN
1654 --
1655 gb_debug := hr_utility.debug_enabled;
1656 --
1657 IF gb_debug THEN
1658 lc_proc_name := gc_pkg_name || 'GENERATE_XML';
1659 hr_utility.trace ('Entering '||lc_proc_name);
1660 END IF ;
1661 --
1662 ln_cur_assact := pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID' );
1663 ln_cur_pact := pay_magtape_generic.get_parameter_value ('TRANSFER_PAYROLL_ACTION_ID' );
1664 --
1665 SELECT legislative_parameters
1666 INTO lc_legislative_parameters
1667 FROM pay_payroll_actions
1668 WHERE payroll_action_id = ln_cur_pact;
1669 --
1670 ln_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',lc_legislative_parameters));
1671 --
1672 SELECT PAA1.assignment_action_id
1673 INTO ln_old_assact_id
1674 FROM pay_assignment_actions PAA,
1675 pay_assignment_actions PAA1
1676 WHERE PAA.assignment_action_id = ln_cur_assact
1677 AND PAA.assignment_id = PAA1.assignment_id
1678 AND PAA1.payroll_action_id = ln_pact_id;
1679 --
1680 get_cp_xml(ln_old_assact_id, l_final_xml_string);
1681 --
1682 ln_offset := 1 ;
1683 ln_amount := 500;
1684 --
1685 LOOP
1686 lc_xml_string1 := NULL;
1687 dbms_lob.read(l_final_xml_string,ln_amount,ln_offset,lc_xml_string1);
1688 pay_core_files.write_to_magtape_lob(lc_xml_string1);
1689 ln_offset := ln_offset + ln_amount ;
1690 END LOOP;
1691 --
1692 EXCEPTION
1693 WHEN NO_DATA_FOUND THEN
1694 IF gb_debug THEN
1695 hr_utility.set_location ('Leaving '||lc_proc_name,20);
1696 END IF ;
1697 WHEN gc_exception THEN
1698 IF gb_debug THEN
1699 hr_utility.set_location('Error in '||lc_proc_name,999999);
1700 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1701 END IF ;
1702 hr_utility.raise_error;
1703 RAISE;
1704 WHEN OTHERS THEN
1705 RAISE gc_exception;
1706 --
1707 END generate_xml;
1708 --
1709 PROCEDURE gen_xml_header
1710 --************************************************************************
1711 -- PROCEDURE
1712 -- gen_xml_header
1713 --
1714 -- DESCRIPTION
1715 -- This procedure generates XML header information and appends to
1716 -- pay_mag_tape.g_clob_value
1717 --
1718 -- ACCESS
1719 -- PUBLIC
1720 --
1721 -- PARAMETERS
1722 -- ==========
1723 -- None
1724 --
1725 -- PREREQUISITES
1726 -- None
1727 --
1728 -- CALLED BY
1729 -- None
1730 --************************************************************************
1731 AS
1732 lc_proc_name VARCHAR2(100);
1733 lc_buf VARCHAR2(2000);
1734 --
1735 BEGIN
1736 --
1737 gb_debug := hr_utility.debug_enabled;
1738 --
1739 IF gb_debug THEN
1740 lc_proc_name := gc_pkg_name || 'GEN_XML_HEADER';
1741 hr_utility.trace ('Entering '||lc_proc_name);
1742 END IF ;
1743 --
1744 vxmltable.DELETE; -- delete the pl/sql table
1745 --
1746 --lc_buf := '<:1xml version="1.0" encoding="UTF-8":2>'||gc_eol ;
1747 lc_buf := gc_eol ||'<ROOT>'||gc_eol ;
1748 --
1749 pay_core_files.write_to_magtape_lob(lc_buf);
1750 --
1751 IF gb_debug THEN
1752 hr_utility.trace ('CLOB contents after appending header information');
1753 hr_utility.trace ('Leaving '||lc_proc_name);
1754 END IF ;
1755 --
1756 END gen_xml_header;
1757 --
1758 PROCEDURE gen_xml_footer
1759 --************************************************************************
1760 -- PROCEDURE
1761 -- gen_xml_footer
1762 --
1763 -- DESCRIPTION
1764 -- This procedure generates XML Footer information and appends to
1765 -- pay_mag_tape.g_clob_value
1766 --
1767 -- ACCESS
1768 -- PUBLIC
1769 --
1770 -- PARAMETERS
1771 -- ==========
1772 -- None
1773 --
1774 -- PREREQUISITES
1775 -- None
1776 --
1777 -- CALLED BY
1778 -- None
1779 --************************************************************************
1780 AS
1781 lc_buf VARCHAR2(2000) ;
1782 lc_proc_name VARCHAR2(100);
1783 --
1784 BEGIN
1785 --
1786 gb_debug := hr_utility.debug_enabled;
1787 --
1788 IF gb_debug THEN
1789 lc_proc_name := gc_pkg_name || 'GEN_XML_FOOTER';
1790 hr_utility.trace ('Entering '||lc_proc_name);
1791 END IF ;
1792 lc_buf := '</ROOT>' ;
1793 --
1794 pay_core_files.write_to_magtape_lob(lc_buf);
1795 --
1796 IF gb_debug THEN
1797 hr_utility.trace ('CLOB contents after appending footer information');
1798 hr_utility.trace ('Leaving '||lc_proc_name);
1799 END IF ;
1800 --
1801 END gen_xml_footer;
1802 --
1803 PROCEDURE deinitialise (p_payroll_action_id IN NUMBER)
1804 IS
1805 --
1806 BEGIN
1807 --
1808 --pay_archive.remove_report_actions(p_payroll_action_id);
1809 null;
1810 --
1811 END deinitialise;
1812
1813 END per_jp_wrkreg_report_pkg;