[Home] [Help]
PACKAGE BODY: APPS.PER_JP_EMPDET_REPORT_PKG
Source
1 PACKAGE BODY per_jp_empdet_report_pkg
2 -- $Header: pejperpt.pkb 120.0.12010000.25 2009/09/29 13:38:10 mpothala noship $
3 -- *************************************************************************
4 -- * Copyright (c) Oracle Corporation Japan,2009 Product Development.
5 -- * All rights reserved
6 -- *************************************************************************
7 -- *
8 -- * PROGRAM NAME
9 -- * pejperpt.pkb
10 -- *
11 -- * DESCRIPTION
12 -- * This script creates the package body of per_jp_empdet_report_pkg
13 -- *
14 -- * DEPENDENCIES
15 -- * None
16 -- *
17 -- * CALLED BY
18 -- * Concurrent Program
19 -- *
20 -- * LAST UPDATE DATE 08-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 -- * 17-APR-2009 SPATTEM 120.0.12010000.1 8574160 Creation
29 -- * 18-APR-2009 SPATTEM 120.0.12010000.8 8574160 Removed the usage of hr_locations table
30 -- * Included Employee Contact Information
31 -- * 23-JUN-2009 SPATTEM 120.0.12010000.9 8623733 included the condition for Organization Id is null in action_creation
32 -- * 01-JUL-2009 MDARBHA 120.1.12010000.10 8623733 Chnaged the 'action_creation' for Organization Hierarchy as per the bug 8623733
33 -- * 12-JUL-2009 MDARBHA 120.1.12010000.11 8574160 Changed the procedure Assact XML to fetch the lookup meaning.
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 -- * 27-JUL-2009 MDARBHA 120.1.12010000.12 8666416 Chnaged the assact_xml procedure for date format issue in PDF
37 -- * 03-AUG-2009 MDARBHA 120.1.12010000.14 8740684 Changed the assact_xml procedure for ordering in asignment history
38 -- * 03-AUG-2009 MDARBHA 120.1.12010000.15 8740649 Changed the assact_xml procedure for previous job history
39 -- * 03-AUG-2009 MDARBHA 120.1.12010000.15 8740607 Changed the assact_xml procedure to display reason in termination reason.
40 -- * 04-AUG-2009 MDARBHA 120.1.12010000.16 8740684 Changed the assact_xml procedure for ordering in asignment history
41 -- * 04-AUG-2009 MDARBHA 120.1.12010000.17 8740684 Changed the assact_xml procedure for ordering in asignment history
42 -- * 04-AUG-2009 MDARBHA 120.1.12010000.18 8740684 Changed the assact_xml procedure for ordering in asignment history
43 -- * 04-AUG-2009 MDARBHA 120.1.12010000.19 8740684 Changed the assact_xml procedure for ordering in asignment history
44 -- * 19-AUG-2009 RDARASI 120.1.12010000.20 8766043 Changed sort_action Procedure
45 -- * 19-AUG-2009 RDARASI 120.1.12010000.20 8765317 Changed lcu_emp_det Cursor Query.
46 -- * 19-AUG-2009 RDARASI 120.1.12010000.20 8814075 Changed lcu_emp_det Cursor Query.
47 -- * 24-AUG-2009 RDARASI 120.1.12010000.21 8740649 Changed lcu_prev_job_hist Cursor Query.
48 -- * 09-Sep-2009 MPOTHALA 120.1.12010000.22 8843783 Added function to show correct previous job history.
49 -- * 14-Sep-2009 MPOTHALA 120.1.12010000.23 8843783 To Correct the function for the bug get_previous_job_history
50 -- * 15-Sep-2009 MPOTHALA 120.1.12010000.24 8843783 To Correct the function for the bug get_previous_job_history
51 -- * 15-Sep-2009 MPOTHALA 120.1.12010000.25 8843783 To Correct the function for the bug get_previous_job_history
52 -- *******************************************************************************************************************************************************
53 AS
54 --
55 g_write_xml CLOB;
56 g_xfdf_string CLOB;
57 gc_eol VARCHAR2(5) := fnd_global.local_chr(10);
58 gc_proc_name VARCHAR2(240);
59 gc_pkg_name VARCHAR2(30) := 'per_jp_empdet_report_pkg.';
60 gb_debug BOOLEAN;
61 gn_bg_id NUMBER;
62 gn_dummy NUMBER := -99 ;
63 gn_all_exclusions_flag NUMBER;
64 gn_vctr NUMBER;
65
66 gc_exception EXCEPTION;
67 --
68 FUNCTION cnv_str( p_text IN VARCHAR2
69 , p_start IN NUMBER DEFAULT NULL
70 , p_end IN NUMBER DEFAULT NULL
71 )
72 RETURN VARCHAR2
73 --************************************************************************
74 -- PROCEDURE
75 -- cnv_str
76 --
77 -- DESCRIPTION
78 -- This fucntion retunrs the string based on the start and end positions
79 -- from the given text
80 --
81 -- ACCESS
82 -- PUBLIC
83 --
84 -- PARAMETERS
85 -- ==========
86 -- NAME TYPE DESCRIPTION
87 -- ----------------- -------- ---------------------------------------
88 -- p_text IN This parameter passes Assignment Set Id
89 -- p_start IN This parameter passes Start Position
90 -- p_end IN This parameter passes End Position
91 --
92 -- PREREQUISITES
93 -- None
94 --
95 -- CALLED BY
96 -- None
97 --************************************************************************
98 IS
99 lc_text VARCHAR2(4000);
100 BEGIN
101 --
102 gb_debug := hr_utility.debug_enabled;
103 IF gb_debug THEN
104 hr_utility.set_location ('Entering CNV_STR',10);
105 END IF;
106 --
107 lc_text := LTRIM(RTRIM(REPLACE(p_text,TO_MULTI_BYTE(' '),' ')));
108 --
109 IF p_start IS NOT NULL
110 AND p_end IS NOT NULL THEN
111 lc_text := SUBSTR(lc_text,p_start,p_end);
112 END IF;
113 --
114 IF gb_debug THEN
115 hr_utility.set_location ('Leaving CNV_STR',10);
116 END IF;
117 --
118 RETURN lc_text;
119 --
120 END cnv_str;
121 --
122 FUNCTION htmlspchar(p_text IN VARCHAR2)
123 RETURN VARCHAR2
124 --************************************************************************
125 -- PROCEDURE
126 -- htmlspchar
127 --
128 -- DESCRIPTION
129 -- This fucntion retunrs the string based on the start and end positions
130 -- from the given text
131 --
132 -- ACCESS
133 -- PUBLIC
134 --
135 -- PARAMETERS
136 -- ==========
137 -- NAME TYPE DESCRIPTION
138 -- ----------------- -------- ---------------------------------------
139 -- p_text IN This parameter passes Assignment Set Id
140 --
141 -- PREREQUISITES
142 -- None
143 --
144 -- CALLED BY
145 -- None
146 --************************************************************************
147 IS
148 lc_htmlspchar VARCHAR2(1) := 'N';
149 BEGIN
150 --
151 gb_debug := hr_utility.debug_enabled;
152 IF gb_debug THEN
153 hr_utility.set_location ('Entering htmlspchar',20);
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 IF gb_debug THEN
187 hr_utility.set_location ('Leaving htmlspchar',20);
188 END IF;
189 --
190 END htmlspchar;
191 --
192 PROCEDURE print_clob(p_clob CLOB)
193 --************************************************************************
194 -- PROCEDURE
195 -- print_clob
196 --
197 -- DESCRIPTION
198 -- This procedure prints contents of a CLOB object passed as parameter.
199 --
200 -- ACCESS
201 -- PUBLIC
202 --
203 -- PARAMETERS
204 -- ==========
205 -- NAME TYPE DESCRIPTION
206 -- ----------------- -------- ---------------------------------------
207 -- p_clob IN This parameter passes clob object
208 --
209 -- PREREQUISITES
210 -- None
211 --
212 -- CALLED BY
213 -- None
214 --************************************************************************
215 IS
216 ln_chars NUMBER;
217 ln_offset NUMBER;
218 lc_buf VARCHAR2(255);
219 --
220 BEGIN
221 --
222 gb_debug := hr_utility.debug_enabled;
223 IF gb_debug THEN
224 hr_utility.set_location ('Entering print_clob',30);
225 END IF;
226 --
227 ln_chars := 240;
228 ln_offset := 1;
229 LOOP
230 lc_buf := NULL;
231 dbms_lob.read( p_clob
232 , ln_chars
233 , ln_offset
234 , lc_buf
235 );
236 IF gb_debug THEN
237 hr_utility.set_location(lc_buf,20);
238 END IF;
239 ln_offset := ln_offset + ln_chars;
240 END LOOP;
241 --
242 IF gb_debug THEN
243 hr_utility.set_location ('Leaving print_clob',30);
244 END IF;
245 --
246 EXCEPTION
247 WHEN NO_DATA_FOUND THEN
248 IF gb_debug THEN
249 hr_utility.set_location ('No Data Found in Print Clob',999999);
250 END IF;
251 END print_clob;
252 --
253 FUNCTION get_previous_job_history( p_mag_asg_action_id IN NUMBER)
254 --************************************************************************
255 -- FUNCTION
256 -- get_job_history
257 --
258 -- DESCRIPTION
259 -- Gets the job previous history for a person
260 -- Added this function to resolve the bug 8843783
261 -- ACCESS
262 -- PRIVATE
263 --
264 -- PREREQUISITES
265 -- None
266 --
267 -- CALLED BY
268 -- archive_code
269 --************************************************************************
270 RETURN per_jp_empdet_report_pkg.gt_job_tbl
271 AS
272 CURSOR lcu_job_history(p_mag_asg_action_id NUMBER)
273 IS
274 SELECT PJEDPJV.assignment_id
275 ,PJEDPJV.company_name
276 ,DECODE(PJEDPJV.employee_category,'REHIRE','*','')
277 ||TO_CHAR(PJEDPJV.start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') start_date
278 ,DECODE(TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''),'H24.12.31',NULL
279 ,TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')) end_date
280 FROM per_jp_empdet_prev_job_v PJEDPJV
281 WHERE PJEDPJV.assignment_action_id = p_mag_asg_action_id
282 AND (PJEDPJV.employee_category IS NULL OR PJEDPJV.employee_category <> 'REHIRE')
283 ORDER BY PJEDPJV.end_date;
284 --
285 lt_job_id per_jp_empdet_report_pkg.gt_job_tbl;
286 lt_res_tb per_jp_empdet_report_pkg.gt_job_tbl;
287 ln_index NUMBER := 0;
288 ld_start_date VARCHAR2(20); -- Fix for the Bug 8843783
289 ln_count NUMBER:=0;
290
291 BEGIN
292
293 FOR lr_job_history in lcu_job_history(p_mag_asg_action_id => p_mag_asg_action_id)
294 LOOP
295 ln_index := ln_index + 1;
296 lt_job_id(ln_index).assignment_id := lr_job_history.assignment_id;
297 lt_job_id(ln_index).start_date := lr_job_history.start_date;
298 lt_job_id(ln_index).end_date := lr_job_history.end_date;
299 lt_job_id(ln_index).company_name := lr_job_history.company_name;
300 hr_utility.set_location('step1.... ',20);
301 --
302 IF gb_debug THEN
303 hr_utility.set_location('p_mag_asg_action_id '||p_mag_asg_action_id,40);
304 hr_utility.set_location('p_prev_assignment_id '||lr_job_history.assignment_id,40);
305 END IF;
306
307 END LOOP;
308 --
309 hr_utility.set_location(ln_index,20);
310 IF ln_index=1 THEN
311 lt_res_tb(1).assignment_id :=lt_job_id(1).assignment_id;
312 lt_res_tb(1).start_date :=lt_job_id(1).start_date;
313 lt_res_tb(1).end_date :=lt_job_id(1).end_date;
314 lt_res_tb(1).company_name :=lt_job_id(1).company_name;
315 hr_utility.set_location('step2.... ',20);
316 ELSE
317 FOR i in 1..ln_index
318 LOOP
319 IF i<ln_index AND (lt_job_id(i).assignment_id=lt_job_id(i+1).assignment_id) THEN
320 IF NVL(UPPER(lt_job_id(i).company_name),-999) <> NVL(UPPER(lt_job_id(i+1).company_name),-999) -- Added by RDARASI for BUG#8774235
321 THEN
322 IF i<>1 AND (lt_job_id(i).assignment_id = lt_job_id(i-1).assignment_id)
323 AND NVL(UPPER(lt_job_id(i).company_name),-999) = NVL(UPPER(lt_job_id(i-1).company_name),-999) -- Added by RDARASI for BUG#8774235
324 THEN
325 lt_res_tb(i).assignment_id :=lt_job_id(i).assignment_id;
326 lt_res_tb(i).start_date :=ld_start_date;
327 lt_res_tb(i).end_date :=lt_job_id(i).end_date;
328 lt_res_tb(i).company_name :=lt_job_id(i).company_name;
329 ln_count:=0;
330 hr_utility.set_location('step3.... ',20);
331 ELSE
332 lt_res_tb(i).assignment_id :=lt_job_id(i).assignment_id;
333 lt_res_tb(i).start_date :=lt_job_id(i).start_date;
334 lt_res_tb(i).end_date :=lt_job_id(i).end_date;
335 lt_res_tb(i).company_name :=lt_job_id(i).company_name;
336 hr_utility.set_location('step4.... ',20);
337 END IF;
338 ELSE
339 IF ln_count=0 THEN
340 ld_start_date:=lt_job_id(i).start_date;
341 hr_utility.set_location(' ld_start_date'||ld_start_date,20);
342 ln_count:=1;
343 END IF;
344 END IF;
345 ELSE
346 IF i<ln_index THEN
347 IF (lt_job_id(i).assignment_id <> lt_job_id(i+1).assignment_id) THEN
348 IF i<>1 AND (lt_job_id(i).assignment_id = lt_job_id(i-1).assignment_id)
349 AND NVL(UPPER(lt_job_id(i).company_name),-999) = NVL(UPPER(lt_job_id(i-1).company_name),-999)-- Added by RDARASI for BUG#8774235
350 THEN
351 lt_res_tb(i).assignment_id :=lt_job_id(i).assignment_id;
352 lt_res_tb(i).start_date :=ld_start_date;
353 lt_res_tb(i).end_date :=lt_job_id(i).end_date;
354 lt_res_tb(i).company_name :=lt_job_id(i).company_name;
355 hr_utility.set_location('step5.... ',20);
356 ELSE
357 lt_res_tb(i).assignment_id:=lt_job_id(i).assignment_id;
358 lt_res_tb(i).start_date:=lt_job_id(i).start_date;
359 lt_res_tb(i).end_date:=lt_job_id(i).end_date;
360 lt_res_tb(i).company_name:=lt_job_id(i).company_name;
361 hr_utility.set_location('step6.... ',20);
362 END IF;
363 END IF;
364 ELSE
365 IF (lt_job_id(i).assignment_id = lt_job_id(i-1).assignment_id)
366 AND NVL(UPPER(lt_job_id(i).company_name),-999) = NVL(UPPER(lt_job_id(i-1).company_name),-999)-- Added by RDARASI for BUG#8774235
367 THEN
368 lt_res_tb(i).assignment_id:=lt_job_id(i).assignment_id;
369 lt_res_tb(i).start_date:=ld_start_date;
370 lt_res_tb(i).end_date:=lt_job_id(i).end_date;
371 lt_res_tb(i).company_name:=lt_job_id(i).company_name;
372 hr_utility.set_location('step7.... ',20);
373 ELSE
374 lt_res_tb(i).assignment_id:=lt_job_id(i).assignment_id;
375 lt_res_tb(i).start_date:=lt_job_id(i).start_date;
376 lt_res_tb(i).end_date:=lt_job_id(i).end_date;
377 lt_res_tb(i).company_name:=lt_job_id(i).company_name;
378 hr_utility.set_location('step8.... ',20);
379 END IF;
380 END IF;
381 END IF;
382 END LOOP;
383 END IF;
384 RETURN lt_res_tb;
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 IF gb_debug THEN
388 hr_utility.set_location('No Data Found Exception in get_job_history',10);
389 END IF;
390 RETURN lt_res_tb;
391 END get_previous_job_history;
392 --
393 PROCEDURE initialize(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE )
394 --***************************************************************************
395 -- PROCEDURE
396 -- initialize
397 --
398 -- DESCRIPTION
399 -- This procedure is used to set global contexts
400 --
401 -- ACCESS
402 -- PUBLIC
403 --
404 -- PARAMETERS
405 -- ==========
406 -- NAME TYPE DESCRIPTION
407 -- ----------------- -------- ---------------------------------------
408 -- p_payroll_action_id IN This parameter passes Payroll Action Id
409 --
410 -- PREREQUISITES
411 -- None
412 --
413 -- CALLED BY
414 -- initialization_code
415 --*************************************************************************
416 IS
417 --
418 CURSOR lcr_params(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
419 IS
420 SELECT fnd_number.canonical_to_number(pay_core_utils.get_parameter('PACTID',legislative_parameters)) payroll_action_id
421 ,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASETID',legislative_parameters)) assignment_set_id
422 ,pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id
423 ,pay_core_utils.get_parameter('ORG',legislative_parameters) organization_id
424 ,pay_core_utils.get_parameter('LOC',legislative_parameters) location_id
425 ,TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') effective_date
426 ,NVL(pay_core_utils.get_parameter('IOH',legislative_parameters),'Y') include_org_hierarchy
427 ,pay_core_utils.get_parameter('ITE',legislative_parameters) incl_term_emp
428 ,TO_DATE(pay_core_utils.get_parameter('TEDF',legislative_parameters),'YYYY/MM/DD') term_date_from
429 ,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD') term_eff_date_to
430 ,pay_core_utils.get_parameter('IMG',legislative_parameters) img_display
431 ,pay_core_utils.get_parameter('S1',legislative_parameters) sort_order_1
432 ,pay_core_utils.get_parameter('S2',legislative_parameters) sort_order_2
433 ,pay_core_utils.get_parameter('S3',legislative_parameters) sort_order_3
434 FROM pay_payroll_actions PPA
435 WHERE PPA.payroll_action_id = p_payroll_action_id;
436 --
437 -- Local Variables
438 lc_procedure VARCHAR2(200);
439 --
440 BEGIN
441 --
442 gb_debug := hr_utility.debug_enabled;
443 IF gb_debug THEN
444 lc_procedure := gc_pkg_name||'initialize';
445 hr_utility.set_location('Entering '||lc_procedure,40);
446 END IF;
447 --
448 -- Fetch the parameters passed by user into global variable.
449 OPEN lcr_params(p_payroll_action_id);
450 FETCH lcr_params into gr_parameters;
451 CLOSE lcr_params;
452
453 SELECT TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')
454 INTO gr_parameters.effective_date
455 FROM pay_payroll_actions PPA
456 WHERE PPA.payroll_action_id = gr_parameters.payroll_action_id;
457
458 --
459 IF gb_debug THEN
460 hr_utility.set_location('p_payroll_action_id......... = ' || p_payroll_action_id,30);
461 hr_utility.set_location('gr_parameters.payroll_action_id = ' || gr_parameters.payroll_action_id,30);
462 hr_utility.set_location('gr_parameters.assignment_set_id = ' || gr_parameters.assignment_set_id,30);
463 hr_utility.set_location('gr_parameters.organization_id...... = ' || gr_parameters.organization_id,30);
464 hr_utility.set_location('gr_parameters.business_group_id.... = ' || gr_parameters.business_group_id,30);
465 hr_utility.set_location('gr_parameters.location_id....... = ' || gr_parameters.location_id,30);
466 hr_utility.set_location('gr_parameters.effective_date....... = ' || gr_parameters.effective_date,30);
467 hr_utility.set_location('gr_parameters.include_org_hierarchy = ' || gr_parameters.include_org_hierarchy,30);
468 hr_utility.set_location('gr_parameters.incl_term_emp... ... = ' || gr_parameters.incl_term_emp,30);
469 hr_utility.set_location('gr_parameters.term_eff_date_from... = ' || gr_parameters.term_date_from,30);
470 hr_utility.set_location('gr_parameters.term_eff_date_to..... = ' || gr_parameters.term_date_to,30);
471 hr_utility.set_location('gr_parameters.img_display = ' || gr_parameters.img_display,30);
472 hr_utility.set_location('gr_parameters.sort_order_1 = ' || gr_parameters.sort_order_1,30);
473 hr_utility.set_location('gr_parameters.sort_order_2 = ' || gr_parameters.sort_order_2,30);
474 hr_utility.set_location('gr_parameters.sort_order_3 = ' || gr_parameters.sort_order_3,30);
475 END IF;
476 --
477 IF gb_debug THEN
478 hr_utility.set_location('Leaving '||lc_procedure,40);
479 END IF;
480 --
481 EXCEPTION
482 WHEN gc_exception THEN
483 IF gb_debug THEN
484 hr_utility.set_location('Error in '||lc_procedure,999999);
485 END IF;
486 RAISE;
487 WHEN OTHERS THEN
488 RAISE gc_exception;
489 END initialize;
490 --
491 PROCEDURE range_cursor( p_payroll_action_id IN NUMBER
492 , p_sqlstr OUT NOCOPY VARCHAR2
493 )
494 --************************************************************************
495 -- PROCEDURE
496 -- range_cursor
497 --
498 -- DESCRIPTION
499 -- This procedure defines a SQL statement to fetch all the people to be
500 -- included in the report.This SQL statement is used to define the
501 -- 'chunks' for multi-threaded operation.
502 --
503 -- ACCESS
504 -- PUBLIC
505 --
506 -- PARAMETERS
507 -- ==========
508 -- NAME TYPE DESCRIPTION
509 -- ----------------- -------- ------------------------------------
510 -- p_payroll_action_id IN This parameter passes payroll_action_id object
511 -- p_sqlstr OUT This parameter returns the SQL Statement
512 --
513 -- PREREQUISITES
514 -- None
515 --
516 -- CALLED BY
517 -- None
518 --************************************************************************
519 AS
520 lc_proc_name VARCHAR2(100);
521 BEGIN
522 gb_debug := hr_utility.debug_enabled;
523 --
524 IF gb_debug THEN
525 lc_proc_name := gc_pkg_name ||'range_cursor';
526 hr_utility.set_location ('Entering '||lc_proc_name,50);
527 hr_utility.set_location ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id,50);
528 END IF;
529 --
530 p_sqlstr := ' select distinct p.person_id'||
531 ' from per_people_f p,'||
532 ' pay_payroll_actions pa'||
533 ' where pa.payroll_action_id = :payroll_action_id'||
534 ' and p.business_group_id = pa.business_group_id'||
535 ' order by p.person_id ';
536 --
537 g_mag_payroll_action_id := p_payroll_action_id;
538 --
539 IF gb_debug THEN
540 hr_utility.set_location ('Range cursor query : ' || p_sqlstr,50);
541 hr_utility.set_location ('Leaving '||lc_proc_name,50);
542 END IF;
543 END range_cursor;
544 --
545 --Commented below as per the bug #8766043
546 /*
547 PROCEDURE sort_action( p_payroll_action_id IN VARCHAR2
548 , sqlstr IN OUT NOCOPY VARCHAR2
549 , len OUT NOCOPY NUMBER
550 )
551 --************************************************************************
552 -- PROCEDURE
553 -- sort_action
554 --
555 -- DESCRIPTION
556 -- This procedure defines a SQL statement to fetch all the people
557 -- according to the sort order parameters.
558 --
559 -- ACCESS
560 -- PUBLIC
561 --
562 -- PARAMETERS
563 -- ==========
564 -- NAME TYPE DESCRIPTION
565 -- ----------------- -------- ------------------------------------
566 -- p_payroll_action_id IN This parameter passes payroll_action_id object
567 -- p_sqlstr IN OUT This parameter returns the SQL Statement
568 -- len OUT This parameter returns the length of the SQL string
569 --
570 -- PREREQUISITES
571 -- None
572 --
573 -- CALLED BY
574 -- None
575 --************************************************************************
576 IS
577
578 lc_sort1 VARCHAR2(60);
579 lc_sort2 VARCHAR2(60);
580 lc_sort3 VARCHAR2(60);
581 lc_term_flag VARCHAR2(10);
582 ld_term_strt_date DATE;
583 ld_term_end_date DATE;
584 ld_effective_date DATE;
585 --
586 BEGIN
587 --
588 gb_debug := hr_utility.debug_enabled;
589 IF gb_debug THEN
590 hr_utility.set_location('Entering sort_action procedure',20);
591 END IF;
592 --
593 initialize(p_payroll_action_id);
594
595 sqlstr := ' SELECT PAA.rowid
596 FROM per_assignments_f PAF
597 ,pay_assignment_actions PAA
598 ,per_people_f PPF
599 ,hr_all_organization_units HAOU
600 ,per_periods_of_service PPS
601 WHERE PAA.payroll_action_id = :pactid
602 AND PAF.assignment_id = PAA.assignment_id
603 AND PPF.person_id = PAF.person_id
604 AND PAF.organization_id = HAOU.organization_id
605 AND PPS.period_of_service_id = PAF.period_of_service_id
606 AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PPF.effective_start_date
607 AND PPF.effective_end_date
608 AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PAF.effective_start_date
609 AND PAF.effective_end_date
610 AND (( NVL('''||gr_parameters.incl_term_emp||''',''N'') = ''Y''
611 AND( PPS.actual_termination_date IS NULL
612 OR (TRUNC(PPS.actual_termination_date) BETWEEN '''||gr_parameters.term_date_from||'''
613 AND '''||gr_parameters.term_date_to||''')
614 )
615 )
616 OR
617 ( NVL('''||gr_parameters.incl_term_emp||''',''N'') = ''N''
618 AND PPS.actual_termination_date IS NULL
619 )
620 )
621 ORDER BY DECODE('''||gr_parameters.sort_order_1||''',''EMPLOYEE_NAME'',PPF.full_name
622 ,''ORGANIZATION_CODE'',HAOU.name
623 ,PPF.employee_number
624 )
625 ,DECODE('''||gr_parameters.sort_order_2||''',''EMPLOYEE_NAME'',PPF.full_name
626 ,''ORGANIZATION_CODE'',HAOU.name
627 ,PPF.employee_number
628 )
629 ,DECODE('''||gr_parameters.sort_order_3||''',''EMPLOYEE_NAME'',PPF.full_name
630 ,''ORGANIZATION_CODE'',HAOU.name
631 ,PPF.employee_number
632 )';
633
634 len := length(sqlstr); -- return the length of the string
635 --
636 IF gb_debug THEN
637 hr_utility.set_location('End of the sort_Action cursor',20);
638 hr_utility.set_location('Leaving sort_action procedure',20);
639 END IF;
640 --
641 END sort_action;
642 */
643 -- Commented above as per the bug #8766043
644 --
645 -- Added below as per the bug #8766043
646 PROCEDURE sort_action( p_payroll_action_id IN NUMBER
647 ,sqlstr IN OUT NOCOPY VARCHAR2
648 ,len OUT NOCOPY NUMBER
649 )
650 --************************************************************************
651 -- PROCEDURE
652 -- sort_action
653 --
654 -- DESCRIPTION
655 -- This procedure defines a SQL statement to fetch all the people
656 -- according to the sort order parameters.
657 --
658 -- ACCESS
659 -- PUBLIC
660 --
661 -- PARAMETERS
662 -- ==========
663 -- NAME TYPE DESCRIPTION
664 -- ----------------- -------- ------------------------------------
665 -- p_payroll_action_id IN This parameter passes payroll_action_id object
666 -- p_sqlstr IN OUT This parameter returns the SQL Statement
667 -- len OUT This parameter returns the length of the SQL string
668 --
669 -- PREREQUISITES
670 -- None
671 --
672 -- CALLED BY
673 -- None
674 --************************************************************************
675 AS
676
677 BEGIN
678 --
679 gb_debug := hr_utility.debug_enabled;
680 --
681 IF gb_debug THEN
682 hr_utility.set_location('Entering sort_action procedure',20);
683 END IF;
684 --
685 hr_utility.trace('Beginning of the sort_action cursor');
686 --
687 initialize(p_payroll_action_id);
688
689 sqlstr := ' SELECT PAA.rowid
690 FROM per_assignments_f PAF
691 ,pay_assignment_actions PAA
692 ,per_people_f PPF
693 ,hr_all_organization_units_tl HAOUT
694 ,hr_all_organization_units HAOU
695 ,per_periods_of_service PPS
696 WHERE PAA.payroll_action_id = :pactid
697 AND PAF.assignment_id = PAA.assignment_id
698 AND PPF.person_id = PAF.person_id
699 AND PAF.organization_id = HAOU.organization_id
700 AND HAOUT.organization_id = HAOU.organization_id
701 AND HAOUT.language = USERENV(''LANG'')
702 AND PPS.period_of_service_id = PAF.period_of_service_id
703 AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PPF.effective_start_date
704 AND PPF.effective_end_date
705 AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PAF.effective_start_date
706 AND PAF.effective_end_date
707 ORDER BY DECODE('''||gr_parameters.sort_order_1||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name)
708 ,''ORGANIZATION_CODE'',UPPER(HAOUT.name)
709 ,UPPER(PPF.employee_number)
710 )
711 ,DECODE('''||gr_parameters.sort_order_2||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name)
712 ,''ORGANIZATION_CODE'',UPPER(HAOUT.name)
713 ,UPPER(PPF.employee_number)
714 )
715 ,DECODE('''||gr_parameters.sort_order_3||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name)
716 ,''ORGANIZATION_CODE'',UPPER(HAOUT.name)
717 ,UPPER(PPF.employee_number)
718 )';
719
720 len := length(sqlstr);
721 IF gb_debug
722 THEN
723 hr_utility.trace('End of the sort_Action cursor');
724 END IF;
725 --
726 EXCEPTION WHEN NO_DATA_FOUND THEN
727 IF gb_debug THEN
728 hr_utility.trace('Error in Sort Procedure - getting legislative param');
729 END IF;
730 RAISE;
731 --
732 END sort_action;
733 --
734 -- Added above as per the bug #8766043
735 --
736 FUNCTION range_person_on
737 --************************************************************************
738 -- FUNCTION
739 -- range_person_on
740 --
741 -- DESCRIPTION
742 -- Checks if RANGE_PERSON_ID is enabled for
743 -- Archive process.
744 --
745 -- ACCESS
746 -- PRIVATE
747 --
748 -- PREREQUISITES
749 -- None
750 --
751 -- CALLED BY
752 -- assignment_action_code
753 --************************************************************************
754 RETURN BOOLEAN
755 IS
756 --
757 CURSOR lcu_action_parameter
758 IS
759 SELECT parameter_value
760 FROM pay_action_parameters
761 WHERE parameter_name = 'RANGE_PERSON_ID';
762 --
763 lb_return BOOLEAN;
764 lc_action_param_val VARCHAR2(30);
765 --
766 BEGIN
767 --
768 gb_debug := hr_utility.debug_enabled;
769 --
770 IF gb_debug THEN
771 hr_utility.set_location('Entering range_person_on',10);
772 END IF;
773 --
774 OPEN lcu_action_parameter;
775 FETCH lcu_action_parameter INTO lc_action_param_val;
776 CLOSE lcu_action_parameter;
777 --
778 IF lc_action_param_val = 'Y' THEN
779 lb_return := TRUE;
780 IF gb_debug THEN
781 hr_utility.set_location('Range Person = True',10);
782 END IF;
783 ELSE
784 lb_return := FALSE;
785 END IF;
786 --
787 IF gb_debug THEN
788 hr_utility.set_location('Leaving range_person_on',10);
789 END IF;
790 RETURN lb_return;
791 --
792 EXCEPTION WHEN NO_DATA_FOUND THEN
793 IF gb_debug THEN
794 hr_utility.set_location('No Data Found Exception in range_person_on',10);
795 END IF;
796 lb_return := FALSE;
797 RETURN lb_return;
798 END range_person_on;
799 --
800 PROCEDURE action_creation( p_payroll_action_id IN NUMBER
801 , p_start_person_id IN NUMBER
802 , p_end_person_id IN NUMBER
803 , p_chunk IN NUMBER
804 )
805 --************************************************************************
806 -- PROCEDURE
807 -- action_creation
808 --
809 -- DESCRIPTION
810 -- This procedure defines a SQL statement to fetch all the people to be
811 -- included in the report.This SQL statement is used to define the
812 -- 'chunks' for multi-threaded operation.
813 --
814 -- ACCESS
815 -- PUBLIC
816 --
817 -- PARAMETERS
818 -- ==========
819 -- NAME TYPE DESCRIPTION
820 -- ----------------- -------- ------------------------------------
821 -- p_payroll_action_id IN This parameter passes Payroll Action ID
822 -- p_start_person_id IN This parameter passes Start Person ID
823 -- p_end_person_id IN This parameter passes End Person ID
824 -- p_chunk IN This parameter passes Chunk value
825 --
826 -- PREREQUISITES
827 -- None
828 --
829 -- CALLED BY
830 -- None
831 --************************************************************************
832 AS
833 --
834 CURSOR lcu_assact_r( p_payroll_action_id_arch pay_payroll_actions.payroll_action_id%TYPE
835 , p_business_group_id per_assignments_f.business_group_id%TYPE
836 , p_organization_id per_assignments_f.organization_id%TYPE
837 , p_location_id per_assignments_f.location_id%TYPE
838 , p_include_term_flag VARCHAR2
839 , p_term_eff_date_from DATE
840 , p_term_eff_date_to DATE
841 , p_effective_date DATE
842 )
843 IS
844 SELECT PJEDV.assignment_id
845 ,PJEDV.effective_date
846 FROM per_assignments_f PAA
847 ,per_people_f PAP
848 ,pay_assignment_actions PAS
849 ,per_jp_empdet_emp_v PJEDV
850 ,per_periods_of_service PPOF
851 ,pay_population_ranges PPR
852 ,pay_payroll_actions PPA
853 WHERE PAA.person_id = PAP.person_id
854 AND PPA.payroll_action_id = PPR.payroll_action_id
855 AND PPA.payroll_action_id = p_payroll_action_id
856 AND PPR.chunk_number = p_chunk
857 AND PPR.person_id = PAP.person_id
858 AND PAS.assignment_id = PAA.assignment_id
859 AND PAS.payroll_action_id = p_payroll_action_id_arch
860 AND PPOF.person_id = PAP.person_id
861 AND PJEDV.assignment_action_id = PAS.assignment_action_id
862 AND PJEDV.assignment_id = PAS.assignment_id
863 AND PAA.business_group_id = p_business_group_id
864 AND PAA.organization_id = NVL(p_organization_id,PAA.organization_id)
865 AND NVL(PAA.location_id,0) = NVL(p_location_id,NVL(PAA.location_id,0))
866 AND PAA.primary_flag = 'Y'
867 AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
868 AND PAP.effective_end_date
869 AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAA.effective_start_date
870 AND PAA.effective_end_date
871 AND (( NVL(p_include_term_flag,'N') = 'Y'
872 AND( (PJEDV.terminate_flag = 'C'
873 AND((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
874 OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START )))
875 OR ( PJEDV.terminate_flag = 'T'
876 AND TRUNC(PPOF.actual_termination_date) BETWEEN p_term_eff_date_from
877 AND p_term_eff_date_to)
878 )
879 )
880 OR
881 ( NVL(p_include_term_flag,'N') = 'N'
882 AND PJEDV.terminate_flag = 'C'
883 AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
884 OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START ))
885 )
886 );
887 --
888 CURSOR lcu_assact( p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
889 , p_business_group_id per_assignments_f.business_group_id%TYPE
890 , p_organization_id per_assignments_f.organization_id%TYPE
891 , p_location_id per_assignments_f.location_id%TYPE
892 , p_include_term_flag VARCHAR2
893 , p_term_eff_date_from DATE
894 , p_term_eff_date_to DATE
895 , p_effective_date DATE
896 )
897 IS
898 SELECT PJEDV.assignment_id
899 ,PJEDV.effective_date
900 FROM per_assignments_f PAA
901 ,per_people_f PAP
902 ,pay_assignment_actions PAS
903 ,per_periods_of_service PPS
904 ,per_jp_empdet_emp_v PJEDV
905 WHERE PAA.person_id = PAP.person_id
906 AND PAA.person_id BETWEEN p_start_person_id
907 AND p_end_person_id
908 AND PAP.person_id = PPS.person_id
909 AND PPS.period_of_service_id = PAA.period_of_service_id
910 AND PAS.assignment_id = PAA.assignment_id
911 AND PAS.payroll_action_id = p_payroll_action_id
912 AND PJEDV.assignment_action_id = PAS.assignment_action_id
913 AND PJEDV.assignment_id = PAS.assignment_id
914 AND PAA.business_group_id = p_business_group_id
915 AND PAA.organization_id = NVL(p_organization_id,PAA.organization_id)
916 AND NVL(PAA.location_id,0) = NVL(p_location_id,NVL(PAA.location_id,0))
917 AND PAA.primary_flag = 'Y'
918 AND NVL(TRUNC(PPS.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
919 AND PAP.effective_end_date
920 AND NVL(TRUNC(PPS.actual_termination_date),p_effective_date) BETWEEN PAA.effective_start_date
921 AND PAA.effective_end_date
922 AND (( NVL(p_include_term_flag,'N') = 'Y'
923 AND(( ( (PPS.actual_termination_date IS NULL AND p_effective_date > = PPS.DATE_START)
924 OR (PPS.actual_termination_date > = p_effective_date AND p_effective_date > = PPS.DATE_START ))
925 AND PJEDV.terminate_flag = 'C'
926 )
927 OR
928 (TRUNC(PPS.actual_termination_date) BETWEEN p_term_eff_date_from
929 AND p_term_eff_date_to
930 AND PJEDV.terminate_flag = 'T'
931 )
932 )
933 )
934 OR
935 ( NVL(p_include_term_flag,'N') = 'N'
936 AND ((PPS.actual_termination_date IS NULL AND p_effective_date > = PPS.DATE_START)
937 OR (PPS.actual_termination_date > = p_effective_date AND p_effective_date > = PPS.DATE_START ))
938 AND PJEDV.terminate_flag = 'C'
939 )
940 );
941 --
942 ln_assact pay_assignment_actions.assignment_action_id%TYPE ;
943 lt_org_id per_jp_report_common_pkg.gt_org_tbl;
944 lc_proc_name VARCHAR2(60);
945 lc_legislative_parameters VARCHAR2(2000);
946 lc_result1 VARCHAR2(30);
947 lc_include_flag VARCHAR2(1);
948 ln_old_pact_id NUMBER;
949 ln_cur_pact NUMBER;
950 ln_ass_set_id NUMBER;
951 lb_result2 BOOLEAN;
952 --
953 BEGIN
954 gb_debug := hr_utility.debug_enabled;
955 --
956 IF gb_debug THEN
957 lc_proc_name := gc_pkg_name ||'action_creation';
958 hr_utility.set_location ('Entering '||lc_proc_name,50);
959 END IF;
960 --
961 SELECT legislative_parameters
962 INTO lc_legislative_parameters
963 FROM pay_payroll_actions
964 WHERE payroll_action_id = p_payroll_action_id;
965 --
966 ln_old_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PACTID',lc_legislative_parameters));
967 ln_ass_set_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASETID',lc_legislative_parameters));
968 --
969 g_mag_payroll_action_id := p_payroll_action_id;
970 --
971 IF gb_debug THEN
972 hr_utility.set_location ('Parameters ....',20);
973 hr_utility.set_location ('P_PAYROLL_ACTION_ID = '|| p_payroll_action_id,20);
974 hr_utility.set_location ('P_START_PERSON_ID = '|| p_start_person_id,20);
975 hr_utility.set_location ('P_END_PERSON_ID = '|| p_end_person_id,20);
976 hr_utility.set_location ('P_CHUNK = '|| p_chunk,20);
977 hr_utility.set_location ('P_OLD_PAYROLL_ACTION-ID = '|| ln_old_pact_id,20);
978 hr_utility.set_location ('P_ASS_SET_ID = '|| ln_ass_set_id,20);
979 END IF;
980 --
981 initialize(g_mag_payroll_action_id);
982 --
983 IF gn_bg_id IS NULL THEN
984 SELECT PPA.business_group_id
985 INTO gn_bg_id
986 FROM pay_payroll_actions PPA
987 WHERE PPA.payroll_action_id = p_payroll_action_id ;
988 END IF ;
989 --
990 IF gr_parameters.organization_id IS NOT NULL THEN
991 -- Getting Organization ID's as per hierarchy
992 lt_org_id := per_jp_report_common_pkg.get_org_hirerachy(p_business_group_id => gr_parameters.business_group_id
993 ,p_organization_id => gr_parameters.organization_id
994 ,p_include_org_hierarchy => gr_parameters.include_org_hierarchy
995 );
996 --
997 FOR i in 1..lt_org_id.COUNT
998 LOOP
999 --
1000 IF gb_debug THEN
1001 hr_utility.set_location ('In org hierarchy loop',20);
1002 END IF;
1003 --
1004 IF range_person_on THEN
1005 --
1006 IF gb_debug THEN
1007 hr_utility.set_location('Inside Range person if condition, with org id',20);
1008 END IF;
1009 --
1010 FOR lr_assact IN lcu_assact_r(ln_old_pact_id
1011 ,gr_parameters.business_group_id
1012 ,lt_org_id(i)
1013 ,gr_parameters.location_id
1014 ,gr_parameters.incl_term_emp
1015 ,gr_parameters.term_date_from
1016 ,gr_parameters.term_date_to
1017 ,gr_parameters.effective_date
1018 )
1019 LOOP
1020 --
1021 IF gb_debug THEN
1022 hr_utility.set_location ('In org - assact loop',20);
1023 END IF;
1024 --
1025 -- Added NVL to overcome NULL issue.
1026 IF (NVL(ln_ass_set_id,0) = 0) THEN
1027 -- NO assignment set passed as parameter
1028 SELECT pay_assignment_actions_s.nextval
1029 INTO ln_assact
1030 FROM dual;
1031 hr_nonrun_asact.insact(ln_assact
1032 ,lr_assact.assignment_id
1033 ,p_payroll_action_id
1034 ,p_chunk
1035 ,NULL
1036 );
1037 ELSE
1038 -- assignment set is passed as paramete r
1039 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => ln_ass_set_id
1040 ,p_assignment_id => lr_assact.assignment_id
1041 ,p_effective_date => gr_parameters.effective_date
1042 );
1043 IF lc_include_flag = 'Y' THEN
1044 SELECT pay_assignment_actions_s.nextval
1045 INTO ln_assact
1046 FROM dual;
1047 hr_nonrun_asact.insact(ln_assact
1048 ,lr_assact.assignment_id
1049 ,p_payroll_action_id
1050 ,p_chunk
1051 ,NULL
1052 );
1053 END IF;
1054 END IF;
1055 END LOOP; -- End loop for lcu_assact_r
1056 ELSE
1057 FOR lr_assact IN lcu_assact(ln_old_pact_id
1058 ,gr_parameters.business_group_id
1059 ,lt_org_id(i)
1060 ,gr_parameters.location_id
1061 ,gr_parameters.incl_term_emp
1062 ,gr_parameters.term_date_from
1063 ,gr_parameters.term_date_to
1064 ,gr_parameters.effective_date
1065 )
1066 LOOP
1067 --
1068 IF gb_debug THEN
1069 hr_utility.set_location ('In org - assact loop',20);
1070 END IF;
1071 --
1072 -- Added NVL to overcome NULL issue.
1073 IF (NVL(ln_ass_set_id,0) = 0) THEN
1074 -- NO assignment set passed as parameter
1075 SELECT pay_assignment_actions_s.nextval
1076 INTO ln_assact
1077 FROM dual;
1078 hr_nonrun_asact.insact(ln_assact
1079 ,lr_assact.assignment_id
1080 ,p_payroll_action_id
1081 ,p_chunk
1082 ,NULL
1083 );
1084 ELSE
1085 -- assignment set is passed as parameter
1086 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => ln_ass_set_id
1087 ,p_assignment_id => lr_assact.assignment_id
1088 ,p_effective_date => gr_parameters.effective_date
1089 );
1090 IF lc_include_flag = 'Y' THEN
1091 SELECT pay_assignment_actions_s.nextval
1092 INTO ln_assact
1093 FROM dual;
1094 hr_nonrun_asact.insact(ln_assact
1095 ,lr_assact.assignment_id
1096 ,p_payroll_action_id
1097 ,p_chunk
1098 ,NULL
1099 );
1100 END IF;
1101 END IF;
1102 END LOOP; -- End loop for lcu_assact
1103 END IF; -- End if for range person on
1104 END LOOP; -- End loop for Org hierarchy
1105 --
1106 ELSE -- If org id is null
1107 IF range_person_on THEN
1108 IF gb_debug THEN
1109 hr_utility.set_location('Inside Range person if condition, with org id as null',20);
1110 END IF;
1111 FOR lr_assact IN lcu_assact_r(ln_old_pact_id
1112 ,gr_parameters.business_group_id
1113 ,NULL
1114 ,gr_parameters.location_id
1115 ,gr_parameters.incl_term_emp
1116 ,gr_parameters.term_date_from
1117 ,gr_parameters.term_date_to
1118 ,gr_parameters.effective_date
1119 )
1120 LOOP
1121 IF gb_debug THEN
1122 hr_utility.set_location ('In org assact loop',20);
1123 END IF;
1124 --
1125 -- Added NVL to overcome NULL issue.
1126 IF (NVL(ln_ass_set_id,0) = 0) THEN
1127 --
1128 -- NO assignment set passed as parameter
1129 SELECT pay_assignment_actions_s.nextval
1130 INTO ln_assact
1131 FROM dual;
1132 hr_nonrun_asact.insact(ln_assact
1133 ,lr_assact.assignment_id
1134 ,p_payroll_action_id
1135 ,p_chunk
1136 ,NULL
1137 );
1138 ELSE
1139 -- assignment set is passed as parameter
1140 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => ln_ass_set_id
1141 ,p_assignment_id => lr_assact.assignment_id
1142 ,p_effective_date => gr_parameters.effective_date
1143 );
1144 IF lc_include_flag = 'Y' THEN
1145 SELECT pay_assignment_actions_s.nextval
1146 INTO ln_assact
1147 FROM dual;
1148 hr_nonrun_asact.insact(ln_assact
1149 ,lr_assact.assignment_id
1150 ,p_payroll_action_id
1151 ,p_chunk
1152 ,NULL
1153 );
1154 END IF;
1155 END IF;
1156 END LOOP; -- for lcu_assact_r
1157 ELSE
1158 IF gb_debug THEN
1159 hr_utility.set_location('Range person returns false',20);
1160 END IF;
1161 -- Assignment Action for Current and Terminated Employe
1162 FOR lr_assact IN lcu_assact(ln_old_pact_id
1163 ,gr_parameters.business_group_id
1164 ,NULL
1165 ,gr_parameters.location_id
1166 ,gr_parameters.incl_term_emp
1167 ,gr_parameters.term_date_from
1168 ,gr_parameters.term_date_to
1169 ,gr_parameters.effective_date
1170 )
1171 LOOP
1172 IF gb_debug THEN
1173 hr_utility.set_location ('In org assact loop',20);
1174 END IF;
1175 --
1176 -- Added NVL to overcome NULL issue.
1177 IF (NVL(ln_ass_set_id,0) = 0) THEN
1178 --
1179 -- NO assignment set passed as parameter
1180 SELECT pay_assignment_actions_s.nextval
1181 INTO ln_assact
1182 FROM dual;
1183 hr_nonrun_asact.insact(ln_assact
1184 ,lr_assact.assignment_id
1185 ,p_payroll_action_id
1186 ,p_chunk
1187 ,NULL
1188 );
1189 ELSE
1190 -- assignment set is passed as parameter
1191 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => ln_ass_set_id
1192 ,p_assignment_id => lr_assact.assignment_id
1193 ,p_effective_date => gr_parameters.effective_date
1194 );
1195 IF lc_include_flag = 'Y' THEN
1196 SELECT pay_assignment_actions_s.nextval
1197 INTO ln_assact
1198 FROM dual;
1199 hr_nonrun_asact.insact(ln_assact
1200 ,lr_assact.assignment_id
1201 ,p_payroll_action_id
1202 ,p_chunk
1203 ,NULL
1204 );
1205 END IF;
1206 END IF;
1207 END LOOP; -- for lcu_assact
1208 END IF; -- for range person
1209 END IF; -- for org id is null
1210 --
1211 IF gb_debug THEN
1212 hr_utility.set_location ('Leaving '||lc_proc_name,50);
1213 END IF;
1214 --
1215 END action_creation;
1216 --
1217 PROCEDURE init_code ( p_payroll_action_id IN NUMBER)
1218 --************************************************************************
1219 -- PROCEDURE
1220 -- init_code
1221 --
1222 -- DESCRIPTION
1223 -- None
1224 --
1225 -- ACCESS
1226 -- PUBLIC
1227 --
1228 -- PARAMETERS
1229 -- ==========
1230 -- NAME TYPE DESCRIPTION
1231 -- ----------------- -------- ------------------------------------
1232 -- p_payroll_action_id IN This parameter passes Payroll Action ID
1233 --
1234 -- PREREQUISITES
1235 -- None
1236 --
1237 -- CALLED BY
1238 -- None
1239 --************************************************************************
1240 IS
1241 BEGIN
1242 gb_debug := hr_utility.debug_enabled;
1243 IF gb_debug THEN
1244 hr_utility.set_location ('inside init_code ',20);
1245 END IF;
1246 g_mag_payroll_action_id := p_payroll_action_id;
1247 NULL;
1248 END init_code;
1249 --
1250 PROCEDURE archive_code ( p_assignment_action_id IN NUMBER
1251 , p_effective_date IN DATE
1252 )
1253 --************************************************************************
1254 -- PROCEDURE
1255 -- archive_code
1256 --
1257 -- DESCRIPTION
1258 -- None
1259 --
1260 -- ACCESS
1261 -- PUBLIC
1262 --
1263 -- PARAMETERS
1264 -- ==========
1265 -- NAME TYPE DESCRIPTION
1266 -- ----------------- -------- -----------------------------------
1267 -- p_assignment_action_id IN This parameter passes Payroll Action ID
1268 -- p_effective_date IN This parameter passes Effective Date
1269 --
1270 -- PREREQUISITES
1271 -- None
1272 --
1273 -- CALLED BY
1274 -- None
1275 --************************************************************************
1276 IS
1277 BEGIN
1278 gb_debug := hr_utility.debug_enabled;
1279 IF gb_debug THEN
1280 hr_utility.set_location ('inside archive_code ',20);
1281 END IF;
1282 END archive_code;
1283 --
1284
1285 PROCEDURE assact_xml(p_assignment_action_id IN NUMBER)
1286 --************************************************************************
1287 -- PROCEDURE
1288 -- assact_xml
1289 --
1290 -- DESCRIPTION
1291 -- This procedure creates xml for the assignment_action_id passed
1292 -- as parameter. It then writes the xml into vXMLTable.
1293 --
1294 -- ACCESS
1295 -- PUBLIC
1296 --
1297 -- PARAMETERS
1298 -- ==========
1299 -- NAME TYPE DESCRIPTION
1300 -- ----------------- -------- -----------------------------------
1301 -- p_assignment_action_id IN This parameter passes Payroll Action ID
1302 --
1303 -- PREREQUISITES
1304 -- None
1305 --
1306 -- CALLED BY
1307 -- None
1308 --************************************************************************
1309 IS
1310 CURSOR lcu_emp_det(p_mag_asg_action_id NUMBER)
1311 IS
1312 SELECT PJEDV.full_name_kana
1313 ,PJEDV.full_name_kanji
1314 ,PJEDV.date_of_birth
1315 ,DECODE(PJEDV.gender,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'SEX' AND lookup_code=PJEDV.gender)) gender
1316 ,SUBSTR(PJEDV.postal_code,1,3)||NVL2(PJEDV.postal_code,'-',' ')|| SUBSTR(PJEDV.postal_code,4) postal_code -- Changed by rdarasi for Bug# 8814075
1317 ,PJEDV.address_line1||' '||PJEDV.address_line2||' '||PJEDV.address_line3 address
1318 ,PJEDV.region1||' '||PJEDV.region2||' '||PJEDV.region3 address_kana
1319 ,PJEDV.address_line1
1320 ,PJEDV.address_line2
1321 ,PJEDV.address_line3
1322 ,PJEDV.country
1323 ,PJEDV.hire_date
1324 ,PJEDV.kind_of_business
1325 ,PJEDV.termination_date
1326 ,DECODE(PJEDV.termination_reason,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'LEAV_REAS' AND lookup_code= PJEDV.termination_reason)) termination_reason -- 8740607
1327 ,PJEDV.hi_num
1328 ,SUBSTR(PJEDV.wp_num,1,4)||NVL2(PJEDV.wp_num,'-',' ')|| SUBSTR(PJEDV.wp_num,5) wp_num -- changed by rdarasi for Bug# 8765317
1329 ,PJEDV.wpf_num
1330 ,SUBSTR(PJEDV.ui_num,1,4)||NVL2(PJEDV.ui_num,'-',' ')|| SUBSTR(PJEDV.ui_num,5,6)||NVL2(PJEDV.ui_num,'-',' ')|| SUBSTR(PJEDV.ui_num,11) ui_num -- changed by rdarasi for bug# 8765317
1331 ,TO_CHAR(PJEDV.term_allowance_amt,'99G999G999') term_allowance_amt
1332 ,PJEDV.payment_date_term
1333 ,PJEDV.hi_qualified_date
1334 ,PJEDV.wp_qualified_date
1335 ,PJEDV.wpf_qualified_date
1336 ,PJEDV.ui_qualified_date
1337 ,PJEDV.terminate_flag
1338 ,PJEDV.employee_number
1339 ,PJEDV.effective_date
1340 FROM per_jp_empdet_emp_v PJEDV
1341 WHERE PJEDV.assignment_action_id = p_mag_asg_action_id;
1342 --
1343 CURSOR lcu_prev_job_hist(p_mag_asg_action_id NUMBER)
1344 IS
1345 SELECT PJEDPJV.company_name
1346 ,DECODE(PJEDPJV.employee_category,'REHIRE','*','')
1347 ||TO_CHAR(PJEDPJV.start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') start_date -- Changed By RDARASI as per the Bug 8740649
1348 ,DECODE(TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''),'H24.12.31',NULL
1349 ,TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')) end_date
1350 FROM per_jp_empdet_prev_job_v PJEDPJV
1351 WHERE PJEDPJV.assignment_action_id = p_mag_asg_action_id
1352 ORDER BY PJEDPJV.start_date;
1353 --
1354 CURSOR lcu_phone_det(p_mag_asg_action_id NUMBER)
1355 IS
1356 SELECT PJEDPV.phone_home
1357 ,PJEDPV.phone_mobile
1358 ,PJEDPV.phone_work
1359 FROM per_jp_empdet_phone_v PJEDPV
1360 WHERE PJEDPV.assignment_action_id = p_mag_asg_action_id;
1361 --
1362 CURSOR lcu_education_det(p_mag_asg_action_id NUMBER)
1363 IS
1364 SELECT PJEEV.school_name
1365 ,PJEEV.school_name_kana
1366 ,PJEEV.faculty_name
1367 ,PJEEV.faculty_name_kana
1368 ,PJEEV.department_name
1369 ,PJEEV.graduation_date graduation_date
1370 FROM per_jp_empdet_education_det_v PJEEV
1371 WHERE PJEEV.assignment_action_id = p_mag_asg_action_id
1372 ORDER BY PJEEV.graduation_date;
1373 --
1374 CURSOR lcu_qualification_det(p_mag_asg_action_id NUMBER)
1375 IS
1376 SELECT PJEQV.type
1377 ,PJEQV.title
1378 ,PJEQV.status
1379 ,PJEQV.grade
1380 ,PJEQV.establishment
1381 ,PJEQV.license_number
1382 ,TO_CHAR(PJEQV.start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') start_date
1383 ,DECODE(TO_CHAR(PJEQV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''),'H24.12.31',NULL
1384 ,TO_CHAR(PJEQV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')) end_date
1385 FROM per_jp_empdet_qualifications_v PJEQV
1386 WHERE PJEQV.assignment_action_id = p_mag_asg_action_id
1387 ORDER BY PJEQV.start_date;
1388 --
1389 CURSOR lcu_assignment_det(p_mag_asg_action_id NUMBER
1390 )
1391 IS
1392 SELECT PJEAV.organization_name
1393 ,PJEAV.job
1394 ,PJEAV.position
1395 ,PJEAV.grade
1396 ,PJEAV.start_date
1397 ,DECODE(PJEAV.end_date,TO_DATE('12/31/4712','mm/dd/yyyy'),null,PJEAV.end_date) end_date
1398 ,PJEAV.assignment_number
1399 FROM per_jp_empdet_assignments_v PJEAV
1400 WHERE PJEAV.assignment_action_id = p_mag_asg_action_id
1401 ORDER BY PJEAV.start_date;
1402 --
1403 CURSOR lcu_contact_info(p_mag_asg_action_id NUMBER)
1404 IS
1405 SELECT PJECIV.full_name_kana
1406 ,PJECIV.full_name_kanji
1407 ,DECODE(PJECIV.relationship,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'CONTACT' AND lookup_code=PJECIV.relationship)) relationship
1408 ,DECODE(PJECIV.gender,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'SEX' AND lookup_code=PJECIV.gender)) gender
1409 ,TO_CHAR(PJECIV.birth_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') birth_date
1410 ,PJECIV.age
1411 ,DECODE(PJECIV.primary_contact,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.primary_contact)) primary_contact
1412 ,DECODE(PJECIV.dependent,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.dependent)) dependent
1413 ,DECODE(PJECIV.shared_residence,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.shared_residence)) shared_residence
1414 ,PJECIV.sequence
1415 ,DECODE(PJECIV.household_head,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.household_head)) household_head
1416 ,DECODE(PJECIV.si_itax,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.si_itax)) si_itax
1417 FROM per_jp_empdet_contact_info_v PJECIV
1418 WHERE PJECIV.assignment_action_id = p_mag_asg_action_id
1419 ORDER BY PJECIV.full_name_kana
1420 ,PJECIV.full_name_kanji;
1421 --
1422 CURSOR lcu_proc_info(p_info_type VARCHAR2
1423 ,p_business_group_id NUMBER
1424 ,p_effective_date DATE
1425 )
1426 IS
1427 SELECT HOI.org_information3
1428 ,HOI.org_information4
1429 ,HOI.org_information7
1430 FROM hr_organization_information HOI
1431 WHERE HOI.org_information_context = 'JP_REPORTS_ADDITIONAL_INFO'
1432 AND HOI.org_information1 = 'JPEMPLDETAILSREPORT'
1433 AND HOI.organization_id = p_business_group_id
1434 AND HOI.org_information3 = p_info_type
1435 AND p_effective_date BETWEEN FND_DATE.canonical_to_date(HOI.org_information5)
1436 AND FND_DATE.canonical_to_date(HOI.org_information6);
1437 --
1438 CURSOR lcu_emp_add_info(p_mag_asg_action_id NUMBER)
1439 IS
1440 SELECT PJEDAI.additional_information1
1441 ,PJEDAI.additional_information2
1442 ,PJEDAI.additional_information3
1443 ,PJEDAI.additional_information4
1444 ,PJEDAI.additional_information5
1445 ,PJEDAI.additional_information6
1446 ,PJEDAI.additional_information7
1447 ,PJEDAI.additional_information8
1448 ,PJEDAI.additional_information9
1449 ,PJEDAI.additional_information10
1450 ,PJEDAI.additional_information11
1451 ,PJEDAI.additional_information12
1452 ,PJEDAI.additional_information13
1453 ,PJEDAI.additional_information14
1454 ,PJEDAI.additional_information15
1455 ,PJEDAI.additional_information16
1456 ,PJEDAI.additional_information17
1457 ,PJEDAI.additional_information18
1458 ,PJEDAI.additional_information19
1459 ,PJEDAI.additional_information20
1460 ,PJEDAI.additional_information21
1461 ,PJEDAI.additional_information22
1462 ,PJEDAI.additional_information23
1463 ,PJEDAI.additional_information24
1464 ,PJEDAI.additional_information25
1465 ,PJEDAI.additional_information26
1466 ,PJEDAI.additional_information27
1467 ,PJEDAI.additional_information28
1468 ,PJEDAI.additional_information29
1469 ,PJEDAI.additional_information30
1470 FROM per_jp_wrkreg_extra_info_v PJEDAI
1471 WHERE PJEDAI.assignment_action_id = p_mag_asg_action_id;
1472 --
1473 lr_emp_det lcu_emp_det%ROWTYPE;
1474 lr_proc_info lcu_proc_info%ROWTYPE;
1475 lr_emp_add_info lcu_emp_add_info%ROWTYPE;
1476 --Local Variables
1477 ln_mag_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
1478 lblob_image per_images.image%TYPE;
1479 lc_xml CLOB;
1480 lc_xml2 CLOB;
1481 lc_common_xml CLOB;
1482 lc_xml_begin VARCHAR2(200);
1483 lc_emp_no VARCHAR2(80);
1484 lc_job_hist_xml CLOB;
1485 lc_profile_value VARCHAR2(100);
1486 lc_additional_info_xml CLOB;
1487 lc_add_msg_xml CLOB;
1488 lc_phone_det_xml CLOB;
1489 lc_edu_det_xml CLOB;
1490 lc_qua_det_xml CLOB;
1491 lc_ass_det_xml CLOB;
1492 lc_contact_xml CLOB;
1493 ln_cnt NUMBER(2);
1494 l_index NUMBER:=0;-- Bug 8740684
1495 l_asmnt_count NUMBER;
1496 i NUMBER;
1497 j NUMBER:=0;-- Bug 8843783
1498 --
1499 TYPE asmt_hist_type IS RECORD( assignment_number per_assignments_f.assignment_number%TYPE
1500 ,organization_name hr_organization_units.name%TYPE
1501 ,job per_jobs_tl.name %TYPE
1502 ,position per_positions.name%TYPE
1503 ,grade per_grades_tl.name%TYPE
1504 ,start_date per_assignments_f.effective_start_date%TYPE
1505 ,end_date per_assignments_f.effective_end_date%TYPE
1506 );--8740684
1507 TYPE gt_asmnt_tbl IS TABLE OF asmt_hist_type INDEX BY binary_integer; -- 8740684
1508 lt_asmnt_tbl gt_asmnt_tbl;--8740684
1509 lt_res_tb per_jp_empdet_report_pkg.gt_job_tbl; -- Bug No 8843783
1510 lt_dis_res_tb per_jp_empdet_report_pkg.gt_job_tbl; -- Bug No 8843783
1511 --
1512 BEGIN
1513 --
1514 vXMLTable.DELETE;
1515 gn_vctr := 0;
1516 lc_job_hist_xml := null;
1517 --
1518 gb_debug := hr_utility.debug_enabled;
1519 IF gb_debug THEN
1520 hr_utility.set_location ('Entering assact_xml',20);
1521 hr_utility.set_location('empdet_xml',20);
1522 END IF;
1523 --
1524 ln_mag_asg_action_id := p_assignment_action_id;
1525 --
1526 initialize(g_mag_payroll_action_id);
1527 --
1528 OPEN lcu_emp_det(ln_mag_asg_action_id);
1529 FETCH lcu_emp_det INTO lr_emp_det;
1530 --
1531 IF lcu_emp_det%FOUND THEN
1532 lc_xml_begin := '<EMPDET>'||gc_eol;
1533 vXMLTable(gn_vctr).xmlstring := lc_xml_begin;
1534 gn_vctr := gn_vctr + 1;
1535 lc_common_xml := '<c1>' ||htmlspchar(cnv_str(lr_emp_det.full_name_kana)) ||'</c1>' ||gc_eol
1536 ||'<c2>' ||htmlspchar(cnv_str(lr_emp_det.full_name_kanji)) ||'</c2>' ||gc_eol
1537 ||'<c3_m>' ||TO_CHAR(lr_emp_det.date_of_birth,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.date_of_birth,'hh:mm:ss-HH:MM') ||'</c3_m>' ||gc_eol
1538 ||'<c3_d>' ||TO_CHAR(lr_emp_det.date_of_birth,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.date_of_birth,'hh:mm:ss-HH:MM') ||'</c3_d>' ||gc_eol
1539 ||'<c3_era>'||TO_CHAR(lr_emp_det.date_of_birth, 'EEYY','NLS_CALENDAR=''Japanese Imperial''') ||'</c3_era>' ||gc_eol
1540 ||'<c4>' ||htmlspchar(cnv_str(lr_emp_det.gender)) ||'</c4>' ||gc_eol
1541 ||'<c5>' ||htmlspchar(cnv_str(lr_emp_det.postal_code)) ||'</c5>' ||gc_eol
1542 ||'<c6>' ||htmlspchar(cnv_str(lr_emp_det.address)) ||'</c6>' ||gc_eol
1543 ||'<c7>' ||htmlspchar(cnv_str(lr_emp_det.address_kana)) ||'</c7>' ||gc_eol
1544 ||'<c8_m>' ||TO_CHAR(lr_emp_det.hire_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.hire_date,'hh:mm:ss-HH:MM') ||'</c8_m>' ||gc_eol
1545 ||'<c8_d>' ||TO_CHAR(lr_emp_det.hire_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.hire_date,'hh:mm:ss-HH:MM') ||'</c8_d>' ||gc_eol
1546 ||'<c8_era>'||TO_CHAR(lr_emp_det.hire_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c8_era>' ||gc_eol
1547 ||'<c9>' ||htmlspchar(cnv_str(lr_emp_det.kind_of_business)) ||'</c9>' ||gc_eol
1548 ||'<c10>' ||htmlspchar(cnv_str(lr_emp_det.hi_num)) ||'</c10>'||gc_eol
1549 ||'<c11_m>' ||TO_CHAR(lr_emp_det.hi_qualified_date,'yyyy-mm-dd')||TO_CHAR(lr_emp_det.hi_qualified_date,'hh:mm:ss-HH:MM')||'</c11_m>' ||gc_eol
1550 ||'<c11_d>' ||TO_CHAR(lr_emp_det.hi_qualified_date,'yyyy-mm-dd')||TO_CHAR(lr_emp_det.hi_qualified_date,'hh:mm:ss-HH:MM')||'</c11_d>' ||gc_eol
1551 ||'<c11_era>'||TO_CHAR(lr_emp_det.hi_qualified_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c11_era>' ||gc_eol
1552 ||'<c12>' ||htmlspchar(cnv_str(lr_emp_det.wp_num))||'</c12>'||gc_eol
1553 ||'<c13_m>' ||TO_CHAR(lr_emp_det.wp_qualified_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.wp_qualified_date,'hh:mm:ss-HH:MM')||'</c13_m>' ||gc_eol
1554 ||'<c13_d>' ||TO_CHAR(lr_emp_det.wp_qualified_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.wp_qualified_date,'hh:mm:ss-HH:MM')||'</c13_d>' ||gc_eol
1555 ||'<c13_era>'||TO_CHAR(lr_emp_det.wp_qualified_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c13_era>' ||gc_eol
1556 ||'<c14>' ||htmlspchar(cnv_str(lr_emp_det.wpf_num)) ||'</c14>'||gc_eol
1557 ||'<c15_m>' ||TO_CHAR(lr_emp_det.wpf_qualified_date,'yyyy-mm-dd')||TO_CHAR(lr_emp_det.wpf_qualified_date,'hh:mm:ss-HH:MM')||'</c15_m>' ||gc_eol
1558 ||'<c15_d>' ||TO_CHAR(lr_emp_det.wpf_qualified_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.wpf_qualified_date,'hh:mm:ss-HH:MM')||'</c15_d>' ||gc_eol
1559 ||'<c15_era>'||TO_CHAR(lr_emp_det.wpf_qualified_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c15_era>' ||gc_eol
1560 ||'<c16>' ||htmlspchar(cnv_str( lr_emp_det.ui_num))||'</c16>'||gc_eol
1561 ||'<c17_m>' ||TO_CHAR(lr_emp_det.ui_qualified_date,'yyyy-mm-dd')||TO_CHAR(lr_emp_det.ui_qualified_date,'hh:mm:ss-HH:MM')||'</c17_m>' ||gc_eol
1562 ||'<c17_d>' ||TO_CHAR(lr_emp_det.ui_qualified_date,'yyyy-mm-dd')||TO_CHAR(lr_emp_det.ui_qualified_date,'hh:mm:ss-HH:MM')||'</c17_d>' ||gc_eol
1563 ||'<c17_era>'||TO_CHAR(lr_emp_det.ui_qualified_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c17_era>' ||gc_eol
1564 ||'<c18_m>' ||TO_CHAR(lr_emp_det.termination_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.termination_date,'hh:mm:ss-HH:MM') ||'</c18_m>'||gc_eol
1565 ||'<c18_d>' ||TO_CHAR(lr_emp_det.termination_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.termination_date,'hh:mm:ss-HH:MM') ||'</c18_d>'||gc_eol
1566 ||'<c18_era>'||TO_CHAR(lr_emp_det.termination_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c18_era>' ||gc_eol
1567 ||'<c19>' ||htmlspchar(cnv_str(lr_emp_det.termination_reason))||'</c19>'||gc_eol
1568 ||'<c20>' ||htmlspchar(cnv_str(lr_emp_det.term_allowance_amt))||'</c20>'||gc_eol
1569 ||'<c21_m>' ||TO_CHAR(lr_emp_det.payment_date_term,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.payment_date_term,'hh:mm:ss-HH:MM')||'</c21_m>'||gc_eol
1570 ||'<c21_d>' ||TO_CHAR(lr_emp_det.payment_date_term,'yyyy-mm-dd')||TO_CHAR(lr_emp_det.payment_date_term,'hh:mm:ss-HH:MM') ||'</c21_d>'||gc_eol
1571 ||'<c21_era>'||TO_CHAR(lr_emp_det.payment_date_term, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c21_era>' ||gc_eol
1572 ||'<c22>' ||htmlspchar(cnv_str(lr_emp_det.employee_number)) ||'</c22>'||gc_eol
1573 ||'<c23_m>' ||TO_CHAR(lr_emp_det.effective_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.effective_date,'hh:mm:ss-HH:MM') ||'</c23_m>'||gc_eol
1574 ||'<c23_d>' ||TO_CHAR(lr_emp_det.effective_date,'yyyy-mm-dd') ||TO_CHAR(lr_emp_det.effective_date,'hh:mm:ss-HH:MM') ||'</c23_d>'||gc_eol
1575 ||'<c23_era>'||TO_CHAR(lr_emp_det.effective_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') ||'</c23_era>' ||gc_eol
1576 ||'<c24>' ||htmlspchar(cnv_str(lr_emp_det.address_line1)) ||'</c24>'||gc_eol
1577 ||'<c25>' ||htmlspchar(cnv_str(lr_emp_det.address_line2)) ||'</c25>'||gc_eol
1578 ||'<c26>' ||htmlspchar(cnv_str(lr_emp_det.address_line3)) ||'</c26>'||gc_eol
1579 ||'<c27>' ||htmlspchar(cnv_str(lr_emp_det.country)) ||'</c27>'||gc_eol;
1580 --
1581 FOR lr_phone_det IN lcu_phone_det(ln_mag_asg_action_id)
1582 LOOP
1583 lc_phone_det_xml := lc_phone_det_xml||
1584 '<phone_home>'||htmlspchar(cnv_str(lr_phone_det.phone_home))||'</phone_home>'||gc_eol
1585 ||'<phone_mobile>'||htmlspchar(cnv_str(lr_phone_det.phone_mobile))||'</phone_mobile>'||gc_eol
1586 ||'<phone_work>'||htmlspchar(cnv_str(lr_phone_det.phone_work))||'</phone_work>'||gc_eol;
1587 END LOOP;
1588 lc_common_xml := lc_common_xml || lc_phone_det_xml;
1589 --
1590 ln_cnt := 0;
1591 FOR lr_education_det IN lcu_education_det(ln_mag_asg_action_id)
1592 LOOP
1593 ln_cnt := ln_cnt + 1;
1594 lc_edu_det_xml := lc_edu_det_xml
1595 ||'<ced'||ln_cnt||'>'||htmlspchar(cnv_str(lr_education_det.graduation_date))||'</ced'||ln_cnt||'>'||gc_eol
1596 ||'<ce'||ln_cnt||'>'||htmlspchar(cnv_str(lr_education_det.school_name))||' '||htmlspchar(cnv_str(lr_education_det.faculty_name))||' '||htmlspchar(cnv_str(lr_education_det.department_name))||'</ce'||ln_cnt||'>'||gc_eol;
1597 END LOOP;
1598 lc_common_xml := lc_common_xml || lc_edu_det_xml;
1599 --
1600 -- The below code added for bug number 8843783
1601 lt_res_tb :=get_previous_job_history(ln_mag_asg_action_id);
1602 j := lt_res_tb.count;
1603 ln_cnt := 0;
1604 IF j <=4 THEN
1605 i := lt_res_tb.first;
1606 ln_cnt := 0;
1607 WHILE i IS NOT NULL LOOP
1608 ln_cnt := ln_cnt + 1;
1609 lc_job_hist_xml:=lc_job_hist_xml
1610 ||'<cpjsd'||ln_cnt||'>'||htmlspchar(cnv_str(lt_res_tb(i).start_date))||'</cpjsd'||ln_cnt||'>'||gc_eol
1611 ||'<cpjed'||ln_cnt||'>'||htmlspchar(cnv_str(lt_res_tb(i).end_date)) ||'</cpjed'||ln_cnt||'>'||gc_eol
1612 ||'<cpj' ||ln_cnt||'>'||htmlspchar(cnv_str(lt_res_tb(i).company_name))||'</cpj'||ln_cnt||'>'||gc_eol;
1613 i:=lt_res_tb.next(i);
1614 END LOOP;
1615 lc_common_xml := lc_common_xml || lc_job_hist_xml;
1616 ELSIF j > 4 THEN
1617 i := lt_res_tb.last;
1618 ln_cnt := 0;
1619 WHILE i <> 0
1620 LOOP
1621 ln_cnt := ln_cnt + 1;
1622 lt_dis_res_tb(ln_cnt).start_date := lt_res_tb(i).start_date;
1623 lt_dis_res_tb(ln_cnt).end_date := lt_res_tb(i).end_date;
1624 lt_dis_res_tb(ln_cnt).company_name := lt_res_tb(i).company_name;
1625 EXIT WHEN ln_cnt >= 4;
1626 i := lt_res_tb.prior(i);
1627 END LOOP;
1628 i := lt_dis_res_tb.last;
1629 ln_cnt := 0;
1630 WHILE i <> 0
1631 LOOP
1632 ln_cnt := ln_cnt + 1;
1633 lc_job_hist_xml:=lc_job_hist_xml
1634 ||'<cpjsd'||ln_cnt||'>'||htmlspchar(cnv_str(lt_dis_res_tb(i).start_date))||'</cpjsd'||ln_cnt||'>'||gc_eol
1635 ||'<cpjed'||ln_cnt||'>'||htmlspchar(cnv_str(lt_dis_res_tb(i).end_date)) ||'</cpjed'||ln_cnt||'>'||gc_eol
1636 ||'<cpj' ||ln_cnt||'>'||htmlspchar(cnv_str(lt_dis_res_tb(i).company_name))||'</cpj'||ln_cnt||'>'||gc_eol;
1637 i := lt_dis_res_tb.prior(i);
1638 END LOOP;
1639 lc_common_xml := lc_common_xml || lc_job_hist_xml;
1640 END IF;
1641 --
1642 ln_cnt := 0;
1643 FOR lr_qualification_det IN lcu_qualification_det(ln_mag_asg_action_id)
1644 LOOP
1645 ln_cnt := ln_cnt + 1;
1646 lc_qua_det_xml := lc_qua_det_xml
1647 ||'<cqt'||ln_cnt||'>'||htmlspchar(cnv_str(lr_qualification_det.type)) ||'</cqt'||ln_cnt||'>'||gc_eol
1648 ||'<cqtl'||ln_cnt||'>'||htmlspchar(cnv_str(lr_qualification_det.title)) ||'</cqtl'||ln_cnt||'>'||gc_eol
1649 ||'<cqs'||ln_cnt||'>'||htmlspchar(cnv_str(lr_qualification_det.status)) ||'</cqs'||ln_cnt||'>'||gc_eol
1650 ||'<cqg'||ln_cnt||'>'||htmlspchar(cnv_str(lr_qualification_det.grade)) ||'</cqg'||ln_cnt||'>'||gc_eol
1651 ||'<cqd'||ln_cnt||'>'||htmlspchar(cnv_str(lr_qualification_det.start_date)) ||'</cqd'||ln_cnt||'>'||gc_eol;
1652 END LOOP;
1653 lc_common_xml := lc_common_xml || lc_qua_det_xml;
1654 --
1655 l_index:=0;
1656 -- 8740684
1657 FOR lr_assignment_det IN lcu_assignment_det(ln_mag_asg_action_id)
1658 LOOP
1659 l_index:=l_index+1;
1660 lt_asmnt_tbl(l_index).assignment_number:=lr_assignment_det.assignment_number;
1661 lt_asmnt_tbl(l_index).organization_name:=lr_assignment_det.organization_name;
1662 lt_asmnt_tbl(l_index).job:=lr_assignment_det.job;
1663 lt_asmnt_tbl(l_index).position:=lr_assignment_det.position;
1664 lt_asmnt_tbl(l_index).grade:=lr_assignment_det.grade;
1665 lt_asmnt_tbl(l_index).start_date:=lr_assignment_det.start_date;
1666 lt_asmnt_tbl(l_index).end_date:=lr_assignment_det.end_date;
1667 END LOOP;
1668 -- 8740684
1669 ln_cnt := 0;
1670 IF l_index < 15 THEN
1671 FOR i IN 1..l_index
1672 LOOP
1673 ln_cnt := ln_cnt + 1;
1674 lc_ass_det_xml := lc_ass_det_xml
1675 ||'<canum'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).assignment_number))||'</canum'||ln_cnt||'>'||gc_eol
1676 ||'<caorg'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).organization_name))||'</caorg'||ln_cnt||'>'||gc_eol
1677 ||'<caj'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).job)) ||'</caj'||ln_cnt||'>'||gc_eol
1678 ||'<cap'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).position)) ||'</cap'||ln_cnt||'>'||gc_eol
1679 ||'<cag'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).grade)) ||'</cag'||ln_cnt||'>'||gc_eol
1680 ||'<casd'||ln_cnt||'>'||htmlspchar(cnv_str(TO_CHAR(lt_asmnt_tbl(i).start_date,'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''))) ||'</casd'||ln_cnt||'>'||gc_eol
1681 ||'<caed'||ln_cnt||'>'||htmlspchar(cnv_str(TO_CHAR(lt_asmnt_tbl(i).end_date,'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''))) ||'</caed'||ln_cnt||'>'||gc_eol;
1682 END LOOP;
1683 ELSE
1684 l_asmnt_count:= l_index-15;
1685 FOR i in (l_asmnt_count+1)..l_index
1686 LOOP
1687 ln_cnt := ln_cnt + 1;
1688 lc_ass_det_xml := lc_ass_det_xml
1689 ||'<canum'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).assignment_number))||'</canum'||ln_cnt||'>'||gc_eol
1690 ||'<caorg'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).organization_name))||'</caorg'||ln_cnt||'>'||gc_eol
1691 ||'<caj'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).job)) ||'</caj'||ln_cnt||'>'||gc_eol
1692 ||'<cap'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).position)) ||'</cap'||ln_cnt||'>'||gc_eol
1693 ||'<cag'||ln_cnt||'>'||htmlspchar(cnv_str(lt_asmnt_tbl(i).grade)) ||'</cag'||ln_cnt||'>'||gc_eol
1694 ||'<casd'||ln_cnt||'>'||htmlspchar(cnv_str(TO_CHAR(lt_asmnt_tbl(i).start_date,'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''))) ||'</casd'||ln_cnt||'>'||gc_eol
1695 ||'<caed'||ln_cnt||'>'||htmlspchar(cnv_str(TO_CHAR(lt_asmnt_tbl(i).end_date,'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''))) ||'</caed'||ln_cnt||'>'||gc_eol;
1696 END LOOP;
1697 --
1698 END IF;--8740684
1699 --
1700 lc_common_xml := lc_common_xml || lc_ass_det_xml;
1701 --
1702 ln_cnt := 0;
1703 FOR lr_contact_info IN lcu_contact_info(ln_mag_asg_action_id)
1704 LOOP
1705 ln_cnt := ln_cnt + 1;
1706 lc_contact_xml := lc_contact_xml
1707 ||'<cckana'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.full_name_kana)) ||'</cckana'||ln_cnt||'>'||gc_eol
1708 ||'<cckanji'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.full_name_kanji)) ||'</cckanji'||ln_cnt||'>'||gc_eol
1709 ||'<ccrel'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.relationship)) ||'</ccrel'||ln_cnt||'>'||gc_eol
1710 ||'<ccgen'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.gender)) ||'</ccgen'||ln_cnt||'>'||gc_eol
1711 ||'<ccbd'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.birth_date)) ||'</ccbd'||ln_cnt||'>'||gc_eol
1712 ||'<ccage'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.age)) ||'</ccage'||ln_cnt||'>'||gc_eol
1713 ||'<ccpc'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.primary_contact)) ||'</ccpc'||ln_cnt||'>'||gc_eol
1714 ||'<ccdep'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.dependent)) ||'</ccdep'||ln_cnt||'>'||gc_eol
1715 ||'<ccshr'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.shared_residence))||'</ccshr'||ln_cnt||'>'||gc_eol
1716 ||'<ccseq'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.sequence)) ||'</ccseq'||ln_cnt||'>'||gc_eol
1717 ||'<cchhd'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.household_head)) ||'</cchhd'||ln_cnt||'>'||gc_eol
1718 ||'<ccsitax'||ln_cnt||'>'||htmlspchar(cnv_str(lr_contact_info.si_itax)) ||'</ccsitax'||ln_cnt||'>'||gc_eol;
1719 END LOOP;
1720 lc_common_xml := lc_common_xml || lc_contact_xml;
1721 --
1722 lc_xml := gc_eol || lc_common_xml || gc_eol;
1723 --
1724 OPEN lcu_proc_info('MESG'
1725 , gr_parameters.business_group_id
1726 , gr_parameters.effective_date
1727 );
1728 FETCH lcu_proc_info INTO lr_proc_info;
1729 CLOSE lcu_proc_info;
1730 --
1731 IF lr_proc_info.org_information7 IS NOT NULL THEN
1732 lc_add_msg_xml := '<MESG>'||htmlspchar(cnv_str(lr_proc_info.org_information7))||'</MESG>'||gc_eol;
1733 lc_xml := lc_xml || lc_add_msg_xml || gc_eol;
1734 END IF;
1735
1736 OPEN lcu_proc_info('ADDINFO'
1737 , gr_parameters.business_group_id
1738 , gr_parameters.effective_date
1739 );
1740 FETCH lcu_proc_info INTO lr_proc_info;
1741 CLOSE lcu_proc_info;
1742 --
1743 IF lr_proc_info.org_information4 IS NOT NULL THEN
1744 --
1745 OPEN lcu_emp_add_info(ln_mag_asg_action_id);
1746 FETCH lcu_emp_add_info into lr_emp_add_info;
1747 CLOSE lcu_emp_add_info;
1748 --
1749 lc_additional_info_xml:= '<x1>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION1)) ||'</x1>' ||gc_eol
1750 ||'<x2>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION2)) ||'</x2>' ||gc_eol
1751 ||'<x3>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION3)) ||'</x3>' ||gc_eol
1752 ||'<x4>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION4)) ||'</x4>' ||gc_eol
1753 ||'<x5>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION5)) ||'</x5>' ||gc_eol
1754 ||'<x6>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION6)) ||'</x6>' ||gc_eol
1755 ||'<x7>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION7)) ||'</x7>' ||gc_eol
1756 ||'<x8>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION8)) ||'</x8>' ||gc_eol
1757 ||'<x9>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION9)) ||'</x9>' ||gc_eol
1758 ||'<x10>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION10))||'</x10>'||gc_eol
1759 ||'<x11>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION11))||'</x11>'||gc_eol
1760 ||'<x12>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION12))||'</x12>'||gc_eol
1761 ||'<x13>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION13))||'</x13>'||gc_eol
1762 ||'<x14>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION14))||'</x14>'||gc_eol
1763 ||'<x15>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION15))||'</x15>'||gc_eol
1764 ||'<x16>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION16))||'</x16>'||gc_eol
1765 ||'<x17>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION17))||'</x17>'||gc_eol
1766 ||'<x18>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION18))||'</x18>'||gc_eol
1767 ||'<x19>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION19))||'</x19>'||gc_eol
1768 ||'<x20>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION20))||'</x20>'||gc_eol
1769 ||'<x21>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION21))||'</x21>'||gc_eol
1770 ||'<x22>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION22))||'</x22>'||gc_eol
1771 ||'<x23>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION23))||'</x23>'||gc_eol
1772 ||'<x24>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION24))||'</x24>'||gc_eol
1773 ||'<x25>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION25))||'</x25>'||gc_eol
1774 ||'<x26>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION26))||'</x26>'||gc_eol
1775 ||'<x27>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION27))||'</x27>'||gc_eol
1776 ||'<x28>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION28))||'</x28>'||gc_eol
1777 ||'<x29>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION29))||'</x29>'||gc_eol
1778 ||'<x30>'||htmlspchar(cnv_str(lr_emp_add_info.ADDITIONAL_INFORMATION30))||'</x30>'||gc_eol;
1779 --
1780 lc_xml := lc_xml || lc_additional_info_xml;
1781 END IF;
1782 -- writing first part of xml to vXMLtable
1783 vXMLTable(gn_vctr).xmlstring := lc_xml;
1784 gn_vctr := gn_vctr + 1;
1785 --
1786 END IF;
1787 CLOSE lcu_emp_det;
1788 --
1789 IF gb_debug THEN
1790 hr_utility.set_location ('Leaving assact_xml',20);
1791 END IF;
1792 --
1793 EXCEPTION
1794 WHEN gc_exception THEN
1795 IF gb_debug THEN
1796 hr_utility.set_location('Error in assact_xml ',999999);
1797 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1798 END IF;
1799 hr_utility.raise_error;
1800 RAISE;
1801 WHEN OTHERS THEN
1802 RAISE gc_exception;
1803 END assact_xml;
1804 --
1805 PROCEDURE writetoclob (p_write_xml OUT NOCOPY CLOB)
1806 --************************************************************************
1807 -- PROCEDURE
1808 -- writetoclob
1809 --
1810 -- DESCRIPTION
1811 -- This procedure selects the xml from vxmltable and writes it
1812 -- into a clob variable. This clob variable is then returned
1813 --
1814 -- ACCESS
1815 -- PUBLIC
1816 --
1817 -- PARAMETERS
1818 -- ==========
1819 -- NAME TYPE DESCRIPTION
1820 -- ----------------- -------- ----------------------------------
1821 -- p_write_xml OUT This parameter returns XML String
1822 --
1823 -- PREREQUISITES
1824 -- None
1825 --
1826 -- CALLED BY
1827 -- None
1828 --************************************************************************
1829 IS
1830 g_xfdf_string CLOB;
1831 l_tempclob CLOB;
1832 ln_ctr_table NUMBER;
1833 lc_xml2 CLOB;
1834 BEGIN
1835 gb_debug := hr_utility.debug_enabled;
1836 IF gb_debug THEN
1837 hr_utility.set_location('Entering writetoclob',20);
1838 END IF;
1839 dbms_lob.createtemporary(g_xfdf_string,FALSE,DBMS_LOB.CALL);
1840 dbms_lob.open(g_xfdf_string,dbms_lob.lob_readwrite);
1841 --
1842 FOR ln_ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST
1843 LOOP
1844 dbms_lob.writeAppend(g_xfdf_string
1845 ,LENGTH(vxmltable(ln_ctr_table).xmlstring)
1846 ,vxmltable(ln_ctr_table).xmlstring );
1847 END LOOP;
1848 --
1849 p_write_xml := g_xfdf_string;
1850 dbms_lob.close(g_xfdf_string);
1851 IF gb_debug THEN
1852 hr_utility.set_location('Out of loop ',99);
1853 hr_utility.set_location('Leaving writetoclob',20);
1854 END IF;
1855 --
1856 EXCEPTION
1857 WHEN gc_exception THEN
1858 IF gb_debug THEN
1859 hr_utility.set_location('Error in writetoclob ',999999);
1860 hr_utility.set_location('sqleerm ' || SQLERRM,20);
1861 END IF;
1862 hr_utility.raise_error;
1863 RAISE;
1864 WHEN OTHERS THEN
1865 RAISE gc_exception;
1866 END writetoclob;
1867 --
1868 PROCEDURE get_cp_xml(p_assignment_action_id IN NUMBER
1869 ,p_xml OUT NOCOPY CLOB
1870 )
1871 --************************************************************************
1872 -- PROCEDURE
1873 -- get_cp_xml
1874 --
1875 -- DESCRIPTION
1876 -- This procedure creates and returns the xml for the
1877 -- assignment_action_id passed as parameter
1878 --
1879 -- ACCESS
1880 -- PUBLIC
1881 --
1882 -- PARAMETERS
1883 -- ==========
1884 -- NAME TYPE DESCRIPTION
1885 -- ----------------- -------- ----------------------------------
1886 -- p_assignment_action_id IN This parameter passes assignment Action ID
1887 -- p_xml OUT This parameter returns XML
1888 --
1889 -- PREREQUISITES
1890 -- None
1891 --
1892 -- CALLED BY
1893 -- None
1894 --************************************************************************
1895 IS
1896 --
1897 BEGIN
1898 gb_debug := hr_utility.debug_enabled;
1899 IF gb_debug THEN
1900 hr_utility.set_location('Entering get_cp_xml',20);
1901 hr_utility.set_location('Leaving get_cp_xml',20);
1902 END IF;
1903 assact_xml(p_assignment_action_id);
1904 writetoclob (p_xml);
1905 END get_cp_xml;
1906 --
1907 PROCEDURE generate_xml
1908 --************************************************************************
1909 -- PROCEDURE
1910 -- generate_xml
1911 --
1912 -- DESCRIPTION
1913 -- This procedure fetches archived data, converts it to XML
1914 -- format and appends to pay_mag_tape.g_clob_value.
1915 --
1916 -- ACCESS
1917 -- PUBLIC
1918 --
1919 -- PARAMETERS
1920 -- ==========
1921 -- None
1922 --
1923 -- PREREQUISITES
1924 -- None
1925 --
1926 -- CALLED BY
1927 -- None
1928 --************************************************************************
1929 AS
1930
1931 CURSOR lcu_get_image(p_assignment_id NUMBER)
1932 IS
1933 SELECT PIMG.parent_id
1934 ,PIMG.image
1935 FROM per_images PIMG
1936 WHERE PIMG.parent_id = (SELECT DISTINCT PPF.person_id
1937 FROM per_assignments_f PPF
1938 WHERE PPF.assignment_id = p_assignment_id
1939 );
1940 --
1941 CURSOR lcu_emp_det_blob(p_mag_asg_action_id NUMBER)
1942 IS
1943 SELECT PJEDV.assignment_id
1944 FROM per_jp_empdet_emp_v PJEDV
1945 WHERE PJEDV.assignment_action_id = p_mag_asg_action_id;
1946 --
1947 l_final_xml_string CLOB;
1948 lblob_image per_images.image%TYPE;
1949 lc_xml_string1 VARCHAR2(2000);
1950 lc_proc_name VARCHAR2(60);
1951 lc_legislative_parameters VARCHAR(2000);
1952 ln_assignment_id NUMBER;
1953 ln_old_assact_id NUMBER;
1954 ln_pact_id NUMBER;
1955 ln_cur_pact NUMBER;
1956 ln_cur_assact NUMBER;
1957 ln_offset NUMBER;
1958 ln_amount NUMBER;
1959 ln_amount_blob BINARY_INTEGER;
1960 ln_offset_blob INTEGER;
1961 ln_parent_id NUMBER;
1962 lc_xml_raw RAW(45);
1963 lc_xml_varchar VARCHAR2(2000);
1964
1965 --
1966 BEGIN
1967 --
1968 gb_debug := hr_utility.debug_enabled;
1969 IF gb_debug THEN
1970 lc_proc_name := gc_pkg_name || 'generate_xml';
1971 hr_utility.set_location ('Entering '||lc_proc_name,20);
1972 END IF;
1973 --
1974 ln_cur_assact := pay_magtape_generic.get_parameter_value ('TRANSFER_ACT_ID' );
1975 ln_cur_pact := pay_magtape_generic.get_parameter_value ('TRANSFER_PAYROLL_ACTION_ID' );
1976 --
1977 SELECT legislative_parameters
1978 INTO lc_legislative_parameters
1979 FROM pay_payroll_actions
1980 WHERE payroll_action_id = ln_cur_pact;
1981 --
1982 ln_pact_id := fnd_number.canonical_to_number(pay_core_utils.get_parameter('PACTID',lc_legislative_parameters));
1983 --
1984 SELECT PAA1.assignment_action_id
1985 INTO ln_old_assact_id
1986 FROM pay_assignment_actions PAA
1987 ,pay_assignment_actions PAA1
1988 WHERE PAA.assignment_action_id = ln_cur_assact
1989 AND PAA.assignment_id = PAA1.assignment_id
1990 AND PAA1.payroll_action_id = ln_pact_id;
1991 --
1992 get_cp_xml(ln_old_assact_id, l_final_xml_string);
1993 --
1994 ln_offset := 1 ;
1995 ln_amount := 500;
1996 --
1997 BEGIN
1998 LOOP
1999 lc_xml_string1 := NULL;
2000 dbms_lob.read(l_final_xml_string,ln_amount,ln_offset,lc_xml_string1);
2001 pay_core_files.write_to_magtape_lob(lc_xml_string1);
2002 ln_offset := ln_offset + ln_amount;
2003 END LOOP;
2004 EXCEPTION
2005 WHEN no_data_found THEN
2006 IF gb_debug THEN
2007 hr_utility.set_location ('exiting from loop',20);
2008 END IF;
2009 END;
2010 --
2011 IF gr_parameters.img_display = 'Y' THEN
2012 --
2013 lc_xml_string1 :='<IMG>';
2014 pay_core_files.write_to_magtape_lob(lc_xml_string1);
2015 --
2016
2017 OPEN lcu_emp_det_blob(ln_old_assact_id);
2018 FETCH lcu_emp_det_blob INTO ln_assignment_id;
2019 CLOSE lcu_emp_det_blob;
2020
2021 IF ln_assignment_id IS NOT NULL THEN
2022 OPEN lcu_get_image(ln_assignment_id);
2023 FETCH lcu_get_image INTO ln_parent_id
2024 ,lblob_image;
2025 CLOSE lcu_get_image;
2026 END IF;
2027
2028 IF ln_parent_id IS NOT NULL THEN
2029
2030 ln_offset_blob := 1;
2031 ln_amount_blob := 45;
2032 --
2033 BEGIN
2034 LOOP
2035 lc_xml_raw := '';
2036 lc_xml_varchar := NULL;
2037 dbms_lob.read(lblob_image,ln_amount_blob,ln_offset_blob,lc_xml_raw);
2038 lc_xml_varchar := utl_raw.cast_to_varchar2(utl_encode.base64_encode(lc_xml_raw));
2039 pay_core_files.write_to_magtape_lob(lc_xml_varchar);
2040 ln_offset_blob := ln_offset_blob + ln_amount_blob;
2041 END LOOP;
2042 EXCEPTION
2043 WHEN no_data_found THEN
2044 IF gb_debug THEN
2045 hr_utility.set_location ('exiting from loop',20);
2046 END IF;
2047 END;
2048 END IF;
2049
2050 lc_xml_string1 := '</IMG>'||gc_eol||'</EMPDET>'||gc_eol ;
2051 pay_core_files.write_to_magtape_lob(lc_xml_string1);
2052 ELSE
2053 lc_xml_string1 :='</EMPDET>'||gc_eol ;
2054 pay_core_files.write_to_magtape_lob(lc_xml_string1);
2055 END IF;
2056
2057 EXCEPTION
2058 WHEN NO_DATA_FOUND THEN
2059 IF gb_debug THEN
2060 hr_utility.set_location ('Leaving '||lc_proc_name,20);
2061 END IF;
2062 WHEN gc_exception THEN
2063 IF gb_debug THEN
2064 hr_utility.set_location('Error in '||lc_proc_name,999999);
2065 hr_utility.set_location('sqleerm ' || SQLERRM,20);
2066 END IF;
2067 hr_utility.raise_error;
2068 RAISE;
2069 WHEN OTHERS THEN
2070 RAISE gc_exception;
2071 END generate_xml;
2072 --
2073 PROCEDURE gen_xml_header
2074 --************************************************************************
2075 -- PROCEDURE
2076 -- gen_xml_header
2077 --
2078 -- DESCRIPTION
2079 -- This procedure generates XML header information and appends to
2080 -- pay_mag_tape.g_clob_value
2081 --
2082 -- ACCESS
2083 -- PUBLIC
2084 --
2085 -- PARAMETERS
2086 -- ==========
2087 -- None
2088 --
2089 -- PREREQUISITES
2090 -- None
2091 --
2092 -- CALLED BY
2093 -- None
2094 --************************************************************************
2095 AS
2096 lc_proc_name VARCHAR2(100);
2097 lc_buf VARCHAR2(2000);
2098 --
2099 BEGIN
2100 gb_debug := hr_utility.debug_enabled;
2101 IF gb_debug THEN
2102 lc_proc_name := gc_pkg_name || 'gen_xml_header';
2103 hr_utility.set_location ('Entering '||lc_proc_name,20);
2104 END IF ;
2105 --
2106 vxmltable.DELETE; -- delete the pl/sql table
2107 --
2108 lc_buf := gc_eol ||'<ROOT>'||gc_eol ;
2109 --
2110 pay_core_files.write_to_magtape_lob(lc_buf);
2111 --
2112 IF gb_debug THEN
2113 hr_utility.set_location ('CLOB contents after appending header information',20);
2114 hr_utility.set_location ('Leaving '||lc_proc_name,20);
2115 END IF ;
2116 --
2117 END gen_xml_header;
2118 --
2119 PROCEDURE gen_xml_footer
2120 --************************************************************************
2121 -- PROCEDURE
2122 -- gen_xml_header
2123 --
2124 -- DESCRIPTION
2125 -- This procedure generates XML Footer information and appends to
2126 -- pay_mag_tape.g_clob_value
2127 --
2128 -- ACCESS
2129 -- PUBLIC
2130 --
2131 -- PARAMETERS
2132 -- ==========
2133 -- None
2134 --
2135 -- PREREQUISITES
2136 -- None
2137 --
2138 -- CALLED BY
2139 -- None
2140 --************************************************************************
2141 AS
2142 lc_buf VARCHAR2(2000) ;
2143 lc_proc_name VARCHAR2(100);
2144 --
2145 BEGIN
2146 --
2147 gb_debug := hr_utility.debug_enabled;
2148 IF gb_debug THEN
2149 lc_proc_name := gc_pkg_name || 'gen_xml_footer';
2150 hr_utility.set_location ('Entering '||lc_proc_name,20);
2151 END IF ;
2152 lc_buf := '</ROOT>' ;
2153 --
2154 pay_core_files.write_to_magtape_lob(lc_buf);
2155 --
2156 IF gb_debug THEN
2157 hr_utility.set_location ('CLOB contents after appending footer information',20);
2158 hr_utility.set_location ('Leaving '||lc_proc_name,20);
2159 END IF ;
2160 --
2161 END gen_xml_footer;
2162 --
2163 PROCEDURE deinitialise (p_payroll_action_id IN NUMBER)
2164 --************************************************************************
2165 -- PROCEDURE
2166 -- deinitialise
2167 --
2168 -- DESCRIPTION
2169 -- This procedure deletes the temporary archive data
2170 --
2171 -- ACCESS
2172 -- PUBLIC
2173 --
2174 -- PARAMETERS
2175 -- ==========
2176 -- NAME TYPE DESCRIPTION
2177 -- ----------------- -------- ----------------------------------
2178 -- p_assignment_action_id IN This parameter passes assignment Action ID
2179 --
2180 -- PREREQUISITES
2181 -- None
2182 --
2183 -- CALLED BY
2184 -- None
2185 --************************************************************************
2186 IS
2187 BEGIN
2188 pay_archive.remove_report_actions(p_payroll_action_id);
2189 END deinitialise;
2190
2191 END per_jp_empdet_report_pkg;