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