[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_EXT
Source
1 PACKAGE BODY pay_cn_ext AS
2 /* $Header: pycnext.pkb 120.0.12010000.2 2008/08/06 07:02:52 ubhat ship $ */
3
4 ----------------------------------------------------------------------------
5 -- --
6 -- Name : INITIALIZE_GLOBALS --
7 -- Type : PROCEDURE --
8 -- Access : Private --
9 -- Description : Function to set global variables so that they are --
10 -- accessible to all threads --
11 -- --
12 -- Parameters : --
13 -- IN : p_phf_si_type VARCHAR2 --
14 -- p_start_date DATE --
15 -- p_end_date DATE --
16 -- p_legal_employer_id NUMBER --
17 -- p_business_group_id NUMBER --
18 -- p_contribution_area VARCHAR2 --
19 -- p_contribution_year VARCHAR2 --
20 -- p_filling_date DATE --
21 -- p_report_type VARCHAR2 --
22 -- OUT : --
23 -- Change History : --
24 ----------------------------------------------------------------------------
25 -- Rev# Date Userid Description --
26 ----------------------------------------------------------------------------
27 -- 1.0 10-Jan-2004 bramajey Created this function --
28 ----------------------------------------------------------------------------
29 PROCEDURE initialize_globals ( p_phf_si_type IN VARCHAR2
30 , p_start_date IN DATE
31 , p_end_date IN DATE
32 , p_legal_employer_id IN NUMBER
33 , p_business_group_id IN NUMBER
34 , p_contribution_area IN VARCHAR2
35 , p_contribution_year IN VARCHAR2
36 , p_filling_date IN DATE
37 , p_report_type IN VARCHAR2
38 )
39 IS
40 --
41 l_proc_name VARCHAR2(150);
42 l_request_id NUMBER ;
43 --
44 BEGIN
45 --
46 l_proc_name := 'pay_cn_ext.initialize_globals';
47
48 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
49
50 l_request_id := fnd_global.conc_request_id;
51
52 hr_utility.set_location('China : Request ID -> '||l_request_id, 20);
53
54 hr_utility.set_location('China : Inserting into pay_action_information ', 30);
55
56 -- Insert the parameters into pay_action_information table so that
57 -- the parameters are available to other threads based on the request ID
58 --
59 INSERT INTO pay_action_information
60 ( action_information_id
61 , action_context_id -- Request Id
62 , action_context_type -- EXT
63 , action_information_category -- EXT_INFO
64 , action_information1 -- PHF / SI Type
65 , action_information2 -- Start Date
66 , action_information3 -- End Date
67 , action_information4 -- Legal Employer Id
68 , action_information5 -- Business Group Id
69 , action_information6 -- Contribution Area
70 , action_information7 -- Contribution Year
71 , action_information8 -- Filling Date
72 , action_information9 -- Report Type
73 )
74 VALUES
75 ( pay_action_information_s.nextval
76 , l_request_id
77 , 'EXT'
78 , 'EXT_INFO'
79 , p_phf_si_type
80 , p_start_date
81 , p_end_date
82 , p_legal_employer_id
83 , p_business_group_id
84 , p_contribution_area
85 , p_contribution_year
86 , p_filling_date
87 , p_report_type
88 );
89
90
91 COMMIT;
92
93 hr_utility.set_location('China : Inserted into pay_action_information ', 40);
94
95 hr_utility.set_location('China : Leaving -> '||l_proc_name, 10);
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 50);
100 RAISE;
101
102 END initialize_globals;
103
104
105 ----------------------------------------------------------------------------
106 -- --
107 -- Name : DELETE_GLOBALS --
108 -- Type : PROCEDURE --
109 -- Access : Private --
110 -- Description : Function to delete global variables stored in table --
111 -- --
112 -- Parameters : --
113 -- IN : --
114 -- OUT : --
115 -- Change History : --
116 ----------------------------------------------------------------------------
117 -- Rev# Date Userid Description --
118 ----------------------------------------------------------------------------
119 -- 1.0 10-Jan-2004 bramajey Created this function --
120 ----------------------------------------------------------------------------
121 PROCEDURE delete_globals
122 IS
123 --
124 l_proc_name VARCHAR2(150);
125 l_request_id NUMBER ;
126 --
127 BEGIN
128 --
129 l_proc_name := 'pay_cn_ext.delete_globals';
130
131 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
132
133 l_request_id := fnd_global.conc_request_id;
134
135 hr_utility.set_location('China : Request ID -> '||l_request_id, 20);
136
137 hr_utility.set_location('China : Deleting row pay_action_information ', 30);
138
139 -- Delete from Pay_action_information
140 --
141 DELETE FROM pay_action_information
142 WHERE action_context_id = l_request_id
143 AND action_context_type = 'EXT'
144 AND action_information_category = 'EXT_INFO';
145
146 COMMIT;
147
148 hr_utility.set_location('China : Deleted from pay_action_information ', 40);
149
150 hr_utility.set_location('China : Leaving -> '||l_proc_name, 10);
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 50);
155 RAISE;
156
157 END delete_globals;
158
159
160 ----------------------------------------------------------------------------
161 -- --
162 -- Name : GET_GLOBALS --
163 -- Type : PROCEDURE --
164 -- Access : Private --
165 -- Description : Function to get global variables --
166 -- --
167 -- Parameters : --
168 -- IN : --
169 -- OUT : p_phf_si_type VARCHAR2 --
170 -- p_start_date DATE --
171 -- p_end_date DATE --
172 -- p_legal_employer_id NUMBER --
173 -- p_business_group_id NUMBER --
174 -- p_contribution_area VARCHAR2 --
175 -- p_contribution_year VARCHAR2 --
176 -- p_filling_date DATE --
177 -- p_report_type VARCHAR2 --
178 -- Change History : --
179 ----------------------------------------------------------------------------
180 -- Rev# Date Userid Description --
181 ----------------------------------------------------------------------------
182 -- 1.0 10-Jan-2004 bramajey Created this function --
183 ----------------------------------------------------------------------------
184 PROCEDURE get_globals ( p_phf_si_type OUT NOCOPY VARCHAR2
185 , p_start_date OUT NOCOPY DATE
186 , p_end_date OUT NOCOPY DATE
187 , p_legal_employer_id OUT NOCOPY NUMBER
188 , p_business_group_id OUT NOCOPY NUMBER
189 , p_contribution_area OUT NOCOPY VARCHAR2
190 , p_contribution_year OUT NOCOPY VARCHAR2
191 , p_filling_date OUT NOCOPY DATE
192 , p_report_type OUT NOCOPY VARCHAR2
193 )
194 IS
195 --
196
197 -- Declare local Variables
198 --
199 l_proc_name VARCHAR2(50);
200 l_request_id NUMBER;
201 l_parent_id NUMBER;
202
203 l_phf_si_type VARCHAR2(50);
204 l_start_date DATE;
205 l_end_date DATE;
206 l_legal_employer_id NUMBER;
207 l_business_group_id NUMBER;
208 l_contribution_area VARCHAR2(30);
209 l_contribution_year VARCHAR2(30);
210 l_filling_date DATE;
211 l_report_type VARCHAR2(3);
212
213 -- Cursor to fetch the Parent request id
214 -- If the parent reuest id is -1 (meaning it does not have a parent request)
215 -- then parent request id should be taken as NULL
216 --
217 CURSOR csr_parent_req_id (p_request_id NUMBER)
218 IS
219 --
220 SELECT decode(parent_request_id,-1,null,parent_request_id)
221 FROM fnd_concurrent_requests
222 WHERE request_id = p_request_id;
223 --
224
225
226 -- Cursor to fetch the data from pay_action_information based on
227 -- request IDs
228 --
229 CURSOR csr_ext_info( p_request_id NUMBER
230 , p_parent_id NUMBER)
231 IS
232 --
233 SELECT action_information1 -- PHF / SI Type
234 , action_information2 -- Start Date
235 , action_information3 -- End Date
236 , action_information4 -- Legal Employer Id
237 , action_information5 -- Business Group Id
238 , action_information6 -- Contribution Area
239 , action_information7 -- Contribution Year
240 , action_information8 -- Filling Date
241 , action_information9 -- Report Type
242 FROM pay_action_information
243 WHERE action_context_id IN ( p_request_id, p_parent_id)
244 AND action_context_type = 'EXT'
245 AND action_information_category = 'EXT_INFO';
246 --
247
248 --
249 BEGIN
250 --
251 l_proc_name := 'pay_cn_ext.get_globals';
252
253 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
254
255 l_request_id := fnd_global.conc_request_id;
256
257 hr_utility.set_location('China : Process Request ID -> '||l_request_id, 20);
258
259 -- Get parent request id
260 --
261 OPEN csr_parent_req_id (l_request_id);
262 FETCH csr_parent_req_id
263 INTO l_parent_id;
264 CLOSE csr_parent_req_id;
265
266 hr_utility.set_location('China : Parent Request ID -> '||l_parent_id, 30);
267
268 hr_utility.set_location('China : Before csr_ext_info ', 40);
269
270 -- Get Parameter Values
271 --
272 OPEN csr_ext_info (l_request_id
273 ,l_parent_id);
274 FETCH csr_ext_info
275 INTO l_phf_si_type
276 ,l_start_date
277 ,l_end_date
278 ,l_legal_employer_id
279 ,l_business_group_id
280 ,l_contribution_area
281 ,l_contribution_year
282 ,l_filling_date
283 ,l_report_type;
284
285 CLOSE csr_ext_info;
286
287 hr_utility.set_location('China : After csr_ext_info ', 50);
288
289 -- Copy the local variables into OUT parameters
290 --
291 p_phf_si_type := l_phf_si_type;
292 p_start_date := l_start_date;
293 p_end_date := l_end_date;
294 p_legal_employer_id := l_legal_employer_id;
295 p_business_group_id := l_business_group_id;
296 p_contribution_area := l_contribution_area;
297 p_contribution_year := l_contribution_year;
298 p_filling_date := l_filling_date;
299 p_report_type := l_report_type;
300
301 hr_utility.set_location('China : Leaving -> '||l_proc_name, 60);
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 IF csr_ext_info%ISOPEN THEN
306 CLOSE csr_ext_info;
307 END IF;
308 RAISE;
309
310 END get_globals;
311
312
313 ----------------------------------------------------------------------------
314 -- --
315 -- Name : GET_ELEMENT_NAME --
316 -- Type : FUNCTION --
317 -- Access : Public --
321 -- Parameters : --
318 -- Description : Function to get the element name of the PHF/SI type --
319 -- given in concurrent request --
320 -- --
322 -- IN : --
323 -- Change History : --
324 ----------------------------------------------------------------------------
325 -- Rev# Date Userid Description --
326 ----------------------------------------------------------------------------
327 -- 1.0 10-Jan-2004 bramajey Created this function --
328 -- 1.1 30-Aug-2004 sshankar Added code for Enterprise Annuity --
329 -- (Bug 3860274) --
330 ----------------------------------------------------------------------------
331 FUNCTION get_element_name(p_phf_si_type IN VARCHAR2)
332 RETURN VARCHAR2
333 IS
334 --
335 l_element_name pay_element_types_f.element_name%TYPE;
336 l_proc_name VARCHAR2(150);
337
338 --
339 BEGIN
340 --
341 l_proc_name := 'pay_cn_ext.get_element_name';
342
343 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
344
345 -- Set l_element_name depending on the value of l_phf_si_type
346 --
347 IF p_phf_si_type = 'INJURY' THEN
348 --
349 l_element_name := 'Injury Insurance Information';
350
351 ELSIF p_phf_si_type = 'MATERNITY' THEN
352 --
353 l_element_name := 'Maternity Insurance Information';
354
355 ELSIF p_phf_si_type = 'MEDICAL' THEN
356 --
357 l_element_name := 'Medical Information';
358
359 ELSIF p_phf_si_type = 'PENSION' THEN
360 --
361 l_element_name := 'Pension Information';
362
363 ELSIF p_phf_si_type = 'PHF' THEN
364 --
365 l_element_name := 'PHF Information';
366
367 ELSIF p_phf_si_type = 'SUPPMED' THEN
368 --
369 l_element_name := 'Supplementary Medical Information';
370
371 ELSIF p_phf_si_type = 'UNEMPLOYMENT' THEN
372 --
373 l_element_name := 'Unemployment Insurance Information';
374
375 --
376 -- Bug 3860275
377 -- Enterprise Annuity to be included in this list of element names.
378 --
379 ELSIF p_phf_si_type = 'ENTANN' THEN
380 --
381 l_element_name := 'Enterprise Annuity Information';
382
383 --
384 END IF;
385
386 hr_utility.set_location('China : l_phf_si_type -> ' || p_phf_si_type , 20);
387 hr_utility.set_location('China : l_element_name -> ' || l_element_name , 30);
388 hr_utility.set_location('China : Leaving -> '|| l_proc_name , 40);
389
390 RETURN l_element_name;
391 --
392 END get_element_name;
393
394
395
396 ----------------------------------------------------------------------------
397 -- --
398 -- Name : CB_EXTRACT_PROCESS --
399 -- Type : PROCEDURE --
400 -- Access : Public --
401 -- Description : Procedure for CB Extract --
402 -- --
403 -- Parameters : --
404 -- IN : p_phf_si_type VARCHAR2 --
405 -- p_legal_employer_id NUMBER DEFAULT NULL --
406 -- p_contribution_area VARCHAR2 --
407 -- p_contribution_year VARCHAR2 --
408 -- p_business_group_id NUMBER --
409 -- OUT : errbuf VARCHAR2 --
410 -- retcode VARCHAR2 --
411 -- Change History : --
412 ----------------------------------------------------------------------------
413 -- Rev# Date Userid Description --
414 ----------------------------------------------------------------------------
415 -- 1.0 10-Jan-2004 bramajey Created this procedure --
416 -- 1.1 06-Jul-2004 sshankar Added new parameter p_assignment_id in --
417 -- call to get_phf_si_rates, to support --
418 -- Enterprise Annuity (Bug 3593118) --
419 -- 1.2 14-Mar-2008 dduvvuri Modified the call to get_phf_si_rates(bug 6828199)
420 ----------------------------------------------------------------------------
421 PROCEDURE cb_extract_process( errbuf OUT NOCOPY VARCHAR2
422 , retcode OUT NOCOPY VARCHAR2
423 , p_phf_si_type IN VARCHAR2
424 , p_legal_employer_id IN NUMBER
425 , p_contribution_area IN VARCHAR2
426 , p_contribution_year IN VARCHAR2
427 , p_business_group_id IN NUMBER
428 )
429 IS
430 --
434 l_errbuf VARCHAR2(3000);
431
432 -- Declare local Variables
433 --
435 l_retcode VARCHAR2(2000);
436 l_proc_name VARCHAR2(150);
437 l_extract_def_id ben_ext_dfn.ext_dfn_id%TYPE;
438 l_start_month NUMBER;
439 l_message VARCHAR2(3000);
440 l_ee_rate_type VARCHAR2(30);
441 l_er_rate_type VARCHAR2(30);
442 l_ee_rate VARCHAR2(30);
443 l_er_rate VARCHAR2(30);
444 l_ee_rounding_method VARCHAR2(30);
445 l_er_rounding_method VARCHAR2(30);
446 /* Changes for bug 6828199 start */
447 l_ee_thrhld_rate VARCHAR2(30);
448 l_er_thrhld_rate VARCHAR2(30);
449 /* Changes for bug 6828199 end */
450 l_start_date DATE;
451 l_end_date DATE;
452
453 --
454
455 -- Cursor to fetch extract definition id of 'CB Extract'
456 --
457 CURSOR csr_extract_def_id
458 IS
459 --
460 SELECT ed.ext_dfn_id
461 FROM ben_ext_dfn ed, hr_lookups hrl, per_business_groups bg
462 WHERE ((bg.business_group_id = ed.business_group_id)
463 OR (bg.legislation_code = ed.legislation_code)
464 OR (ed.business_group_id IS NULL AND ed.legislation_code IS NULL))
465 AND bg.business_group_id = p_business_group_id
466 AND ed.data_typ_cd = hrl.lookup_code
467 AND hrl.lookup_type = 'BEN_EXT_DATA_TYP'
468 AND SUBSTR(ed.NAME,1,240) = 'CB Extract';
469 --
470
471 -- Cursor to fetch Switch Period month
472 --
473 -- Bug 3415164
474 -- Added additional condition to check whether org_information3 is null
475 -- and effective_date check
476 -- SYSDATE is used because we assume that CB Report is run on the switch period month and
477 -- Value for 'Switch Period Month' on SYSDATE should be used.
478 --
479 CURSOR csr_start_month
480 IS
481 --
482 SELECT org_information11 -- Switch Period Month
483 FROM hr_organization_information
484 WHERE organization_id = p_business_group_id
485 AND org_information_context = 'PER_CONT_AREA_CONT_BASE_CN'
486 AND org_information1 = p_contribution_area
487 AND org_information2 = p_phf_si_type
488 AND org_information10 = 'YEARLY' -- Switch Period Periodicity is hardcoded.
489 AND org_information3 IS NULL
490 AND SYSDATE BETWEEN TO_DATE(org_information15,'YYYY/MM/DD HH24:MI:SS')
491 AND TO_DATE(NVL(org_information16,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS');
492 --
493 --
494 BEGIN
495 --
496 l_proc_name := 'pay_cn_ext.cb_extract_process';
497
498 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
499
500 hr_utility.set_location('China : Before csr_extract_def_id ', 20);
501
502 -- Fetch Extract Definition Id
503 --
504 OPEN csr_extract_def_id;
505 FETCH csr_extract_def_id
506 INTO l_extract_def_id;
507
508 -- If Extract Definition does not exist return
509 IF csr_extract_def_id%NOTFOUND THEN
510 --
511 hr_utility.set_location('China : Extract Definition not Found ' , 30);
512 CLOSE csr_extract_def_id;
513 RETURN;
514 --
515 END IF;
516 --
517
518 CLOSE csr_extract_def_id;
519
520 hr_utility.set_location('China : After csr_extract_def_id ', 40);
521
522 hr_utility.set_location('China : l_extract_def_id -> ' || l_extract_def_id , 45);
523 hr_utility.set_location('China : p_phf_si_type -> ' || p_phf_si_type , 45);
524 hr_utility.set_location('China : p_start_date -> ' || l_start_date , 45);
525 hr_utility.set_location('China : p_end_date -> ' || l_end_date , 45);
526 hr_utility.set_location('China : p_legal_employer_id -> ' || p_legal_employer_id , 45);
527 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 45);
528 hr_utility.set_location('China : p_contribution_area -> ' || p_contribution_area , 45);
529 hr_utility.set_location('China : p_contribution_year -> ' || p_contribution_year , 45);
530
531 hr_utility.set_location('China : Before csr_start_month ', 50);
532
533 -- Calculation of the start_date
534 --
535 OPEN csr_start_month;
536 FETCH csr_start_month
537 INTO l_start_month;
538
539 -- If Switch month is not found
540 --
541 IF csr_start_month%NOTFOUND THEN
542 --
543 hr_utility.set_location('China : Switch Period Month not found ' , 55);
544 CLOSE csr_start_month;
545 RETURN;
546 --
547 END IF;
548 --
549
550 CLOSE csr_start_month;
551
552 hr_utility.set_location('China : After csr_start_month ', 60);
553
554 -- Set the first Day of the Switch Month as the Start Date for the report
555 l_start_date := TO_DATE( '01-'||l_start_month||'-'||p_contribution_year , 'DD-MM-YYYY');
556
557 hr_utility.set_location('China : l_start_date -> '|| l_start_date , 70);
558
562 hr_utility.set_location('China : l_end_date -> '|| l_end_date , 80);
559 -- Add 11 months to the start date to get the end date
560 l_end_date := LAST_DAY(ADD_MONTHS(l_start_date,11));
561
563
564 -- Set Global Variables
565 --
566
567 initialize_globals ( p_phf_si_type => p_phf_si_type
568 , p_start_date => l_start_date
569 , p_end_date => l_end_date
570 , p_legal_employer_id => p_legal_employer_id
571 , p_business_group_id => p_business_group_id
572 , p_contribution_area => p_contribution_area
573 , p_contribution_year => p_contribution_year
574 , p_filling_date => null
575 , p_report_type => 'CB'
576 );
577 --
578
579 hr_utility.set_location('China : Check whether Legal Employer has fixed amount for given Contribution Area ', 85);
580
581 --
582 -- Bug 3593118
583 -- Enterprise Annuity - Added new parameter p_assignment_id in call to
584 -- get_phf_si_rates
585 --
586
587 l_message := pay_cn_deductions.get_phf_si_rates
588 (p_assignment_id => NULL
589 ,p_business_group_id => p_business_group_id
590 ,p_contribution_area => p_contribution_area
591 ,p_phf_si_type => p_phf_si_type
592 ,p_employer_id => p_legal_employer_id
593 ,p_hukou_type => NULL
594 ,p_effective_date => l_start_date
595 --
596 ,p_ee_rate_type => l_ee_rate_type
597 ,p_er_rate_type => l_er_rate_type
598 ,p_ee_rate => l_ee_rate
599 ,p_er_rate => l_er_rate
600 ,p_ee_thrhld_rate => l_ee_thrhld_rate /* For bug 6828199 */
601 ,p_er_thrhld_rate => l_er_thrhld_rate /* For bug 6828199 */
602 ,p_ee_rounding_method => l_ee_rounding_method
603 ,p_er_rounding_method => l_er_rounding_method
604 );
605
606 IF l_message = 'SUCCESS' THEN
607 --
608 IF (l_er_rate_type <> 'PERCENTAGE') OR (l_ee_rate_type <> 'PERCENTAGE') THEN
609 --
610 hr_utility.set_location('China : Legal Employer has fixed amount for given Contribution Area ', 90);
611 RETURN;
612 --
613 END IF;
614 --
615 END IF;
616
617 hr_utility.set_location('China : Calling -> ben_ext_thread.process', 100);
618
619
620 -- Call the Extract Process
621 --
622 ben_ext_thread.process ( errbuf => l_errbuf
623 , retcode => l_retcode
624 , p_benefit_action_id => NULL
625 , p_ext_dfn_id => l_extract_def_id
626 , p_effective_date => TO_CHAR(l_end_date,'yyyy/mm/dd')
627 , p_business_group_id => p_business_group_id
628 );
629
630 -- Delete the globals stored in the table
631 --
632 delete_globals;
633
634 hr_utility.set_location('China : Leaving -> '|| l_proc_name, 120);
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 delete_globals;
639 IF csr_extract_def_id%ISOPEN THEN
640 CLOSE csr_extract_def_id;
641 END IF;
642
643 IF csr_start_month%ISOPEN THEN
644 CLOSE csr_start_month;
645 END IF;
646
647 hr_utility.set_location('China Exception, Leaving: '||l_proc_name, 130);
648 RAISE;
649 END cb_extract_process;
650
651 ----------------------------------------------------------------------------
652 -- --
653 -- Name : CA_EXTRACT_PROCESS --
654 -- Type : PROCEDURE --
655 -- Access : Public --
656 -- Description : Procedure for CA Extract --
657 -- --
658 -- Parameters : --
659 -- IN : p_phf_si_type VARCHAR2 --
660 -- p_legal_employer_id NUMBER --
661 -- p_contribution_area VARCHAR2 --
662 -- p_contribution_year VARCHAR2 --
663 -- p_business_group_id NUMBER --
664 -- OUT : errbuf VARCHAR2 --
665 -- retcode VARCHAR2 --
666 -- Change History : --
667 ----------------------------------------------------------------------------
671 ----------------------------------------------------------------------------
668 -- Rev# Date Userid Description --
669 ----------------------------------------------------------------------------
670 -- 1.0 10-Jan-2004 bramajey Created this procedure --
672 PROCEDURE ca_extract_process( errbuf OUT NOCOPY VARCHAR2
673 , retcode OUT NOCOPY VARCHAR2
674 , p_phf_si_type IN VARCHAR2
675 , p_legal_employer_id IN NUMBER
676 , p_contribution_area IN VARCHAR2
677 , p_contribution_year IN VARCHAR2
678 , p_contribution_month IN VARCHAR2
679 , p_business_group_id IN NUMBER
680 )
681 IS
682 --
683
684 -- Declare local Variables
685 --
686 l_extract_def_id NUMBER;
687 l_errbuf VARCHAR2(3000);
688 l_retcode VARCHAR2(2000);
689 l_proc_name VARCHAR2(150);
690
691 l_start_date DATE;
692 l_end_date DATE;
693
694 --
695
696 -- Cursor to fetch extract definition id of 'CA Extract'
697 --
698 CURSOR csr_extract_def_id
699 IS
700 --
701 SELECT ed.ext_dfn_id
702 FROM ben_ext_dfn ed, hr_lookups hrl, per_business_groups bg
703 WHERE ((bg.business_group_id = ed.business_group_id)
704 OR (bg.legislation_code = ed.legislation_code)
705 OR (ed.business_group_id is null and ed.legislation_code is null))
706 AND bg.business_group_id = p_business_group_id
707 AND ed.data_typ_cd = hrl.lookup_code
708 AND hrl.lookup_type = 'BEN_EXT_DATA_TYP'
709 AND substr(ed.NAME,1,240) = 'CA Extract';
710
711
712 --
713 BEGIN
714 --
715 l_proc_name := 'pay_cn_ext.ca_extract_process';
716
717 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
718
719 hr_utility.set_location('China : Before csr_extract_def_id ', 20);
720
721 -- Fetch Extract Definition Id
722 --
723 OPEN csr_extract_def_id;
724 FETCH csr_extract_def_id
725 INTO l_extract_def_id;
726
727 -- If Extract Definition does not exist return
728 IF csr_extract_def_id%NOTFOUND THEN
729 --
730 hr_utility.set_location('China : Extract Definition not Found ' , 30);
731 CLOSE csr_extract_def_id;
732 RETURN;
733 --
734 END IF;
735 --
736
737 CLOSE csr_extract_def_id;
738
739 hr_utility.set_location('China : After csr_extract_def_id ', 40);
740
741 hr_utility.set_location('China : l_extract_def_id -> ' || l_extract_def_id , 45);
742 hr_utility.set_location('China : p_phf_si_type -> ' || p_phf_si_type , 45);
743 hr_utility.set_location('China : p_start_date -> ' || l_start_date , 45);
744 hr_utility.set_location('China : p_end_date -> ' || l_end_date , 45);
745 hr_utility.set_location('China : p_legal_employer_id -> ' || p_legal_employer_id , 45);
746 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 45);
747 hr_utility.set_location('China : p_contribution_area -> ' || p_contribution_area , 45);
748 hr_utility.set_location('China : p_contribution_year -> ' || p_contribution_year , 45);
749
750 -- Calculation of the start_date and end_date
751
752 l_start_date := to_date( ('01-'||p_contribution_month||'-'||p_contribution_year) , 'DD-MM-YYYY');
753 hr_utility.set_location('China : l_start_date -> '|| l_start_date , 50);
754
755 l_end_date := LAST_DAY(l_start_date);
756 hr_utility.set_location('China : l_end_date -> '|| l_end_date , 50);
757
758 -- Set Global Variables
759 --
760
761 initialize_globals ( p_phf_si_type => p_phf_si_type
762 , p_start_date => l_start_date
763 , p_end_date => l_end_date
764 , p_legal_employer_id => p_legal_employer_id
765 , p_business_group_id => p_business_group_id
766 , p_contribution_area => p_contribution_area
767 , p_contribution_year => p_contribution_year
768 , p_filling_date => null
769 , p_report_type => 'CA'
770 );
771
772 hr_utility.set_location('China : Calling -> ben_ext_thread.process', 60);
773
774 -- Call the Extract Process
775 --
776 ben_ext_thread.process ( errbuf => l_errbuf
777 , retcode => l_retcode
778 , p_benefit_action_id => NULL
779 , p_ext_dfn_id => l_extract_def_id
780 , p_effective_date => to_char(l_end_date,'yyyy/mm/dd')
781 , p_business_group_id => p_business_group_id
782 );
783
784 -- Delete the globals stored in the table
785 --
786 delete_globals;
790 EXCEPTION
787
788 hr_utility.set_location('China : Leaving -> '|| l_proc_name, 70);
789 --
791 --
792 WHEN OTHERS THEN
793 delete_globals;
794 IF csr_extract_def_id%ISOPEN THEN
795 CLOSE csr_extract_def_id;
796 END IF;
797
798 hr_utility.set_location('China Exception, Leaving: '||l_proc_name, 80);
799 RAISE;
800
801 END ca_extract_process;
802
803 ----------------------------------------------------------------------------
804 -- Name : EM_EXTRACT_PROCESS --
805 -- Access : Public --
806 -- Description : Procedure for EM Extract --
807 -- --
808 -- Parameters : --
809 -- IN : p_phf_si_type VARCHAR2 --
810 -- p_legal_employer_id NUMBER --
811 -- p_contribution_area VARCHAR2 --
812 -- p_contribution_year VARCHAR2 --
813 -- p_contribution_month VARCHAR2 --
814 -- p_business_group_id NUMBER --
815 -- p_filling_date VARCHAR2 --
816 -- OUT : errbuf VARCHAR2 --
817 -- retcode VARCHAR2 --
818 -- Change History : --
819 ----------------------------------------------------------------------------
820 -- Rev# Date Userid Description --
821 ----------------------------------------------------------------------------
822 -- 1.0 10-Jan-2004 bramajey Created this procedure --
823 ----------------------------------------------------------------------------
824 PROCEDURE em_extract_process( errbuf OUT NOCOPY VARCHAR2
825 , retcode OUT NOCOPY VARCHAR2
826 , p_phf_si_type IN VARCHAR2
827 , p_legal_employer_id IN NUMBER
828 , p_contribution_area IN VARCHAR2
829 , p_contribution_year IN VARCHAR2
830 , p_contribution_month IN VARCHAR2
831 , p_business_group_id IN NUMBER
832 , p_filling_date IN VARCHAR2
833 )
834 IS
835 --
836
837 -- Declare local Variables
838 --
839 l_extract_def_id NUMBER;
840 l_errbuf VARCHAR2(3000);
841 l_retcode VARCHAR2(2000);
842 l_proc_name VARCHAR2(150);
843
844 l_start_date DATE;
845 l_end_date DATE;
846 l_filling_date DATE;
847
848 -- Cursor to fetch extract definition id of 'EM Extract'
849 --
850 CURSOR csr_extract_def_id
851 IS
852 --
853 SELECT ed.ext_dfn_id
854 FROM ben_ext_dfn ed, hr_lookups hrl, per_business_groups bg
855 WHERE ((bg.business_group_id = ed.business_group_id)
856 OR (bg.legislation_code = ed.legislation_code)
857 OR (ed.business_group_id is null and ed.legislation_code is null))
858 AND bg.business_group_id = p_business_group_id
859 AND ed.data_typ_cd = hrl.lookup_code
860 AND hrl.lookup_type = 'BEN_EXT_DATA_TYP'
861 AND substr(ed.NAME,1,240) = 'EM Extract';
862 --
863
864 --
865 BEGIN
866 --
867 l_proc_name := 'pay_cn_ext.em_extract_process';
868
869 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
870
871 hr_utility.set_location('China : Before csr_extract_def_id ', 20);
872
873 -- Fetch Extract Definition Id
874 --
875 OPEN csr_extract_def_id;
876 FETCH csr_extract_def_id
877 INTO l_extract_def_id;
878
879 -- If Extract Definition does not exist return
880 IF csr_extract_def_id%NOTFOUND THEN
881 --
882 hr_utility.set_location('China : Extract Definition not Found ' , 30);
883 CLOSE csr_extract_def_id;
884 RETURN;
885 --
886 END IF;
887 --
888 -- Bug 3448316 caused this change
889 l_filling_date:=fnd_date.canonical_to_date(p_filling_date);
890 --
891
892 hr_utility.set_location('China : p_extract_def_id -> ' || l_extract_def_id , 40);
893 hr_utility.set_location('China : p_phf_si_type -> ' || p_phf_si_type , 40);
894 hr_utility.set_location('China : p_start_date -> ' || l_start_date , 40);
895 hr_utility.set_location('China : p_end_date -> ' || l_end_date , 40);
896 hr_utility.set_location('China : p_legal_employer_id -> ' || p_legal_employer_id , 40);
897 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 40);
898 hr_utility.set_location('China : p_filling_date -> ' || l_filling_date , 40);
899 hr_utility.set_location('China : p_contribution_area -> ' || p_contribution_area , 40);
903
900 hr_utility.set_location('China : p_contribution_year -> ' || p_contribution_year , 40);
901
902 -- Calculation of the start_date and end_date
904 l_start_date := TO_DATE( ('01-'||p_contribution_month||'-'||p_contribution_year) , 'DD-MM-YYYY');
905 hr_utility.set_location('China l_start_date -> '|| l_start_date , 50);
906
907 l_end_date := LAST_DAY(l_start_date);
908 hr_utility.set_location('China l_end_date -> '|| l_end_date , 50);
909
910 -- Set Global Variables
911 --
912
913 initialize_globals ( p_phf_si_type => p_phf_si_type
914 , p_start_date => l_start_date
915 , p_end_date => l_end_date
916 , p_legal_employer_id => p_legal_employer_id
917 , p_business_group_id => p_business_group_id
918 , p_contribution_area => p_contribution_area
919 , p_contribution_year => p_contribution_year
920 , p_filling_date => l_filling_date
921 -- bug 3448316 caused change from p_filling_date to l_filling_date
922 , p_report_type => 'EM'
923
924 );
925 hr_utility.set_location('China : Calling -> ben_ext_thread.process', 60);
926
927 ben_ext_thread.process ( errbuf => l_errbuf
928 , retcode => l_retcode
929 , p_benefit_action_id => NULL
930 , p_ext_dfn_id => l_extract_def_id
931 , p_effective_date => TO_CHAR(l_end_date,'yyyy/mm/dd')
932 , p_business_group_id => p_business_group_id
933 );
934
935 -- Delete the globals stored in the table
936 --
937 delete_globals;
938
939 hr_utility.set_location('China : Leaving -> '|| l_proc_name, 70);
940 --
941 EXCEPTION
942 --
943 WHEN OTHERS THEN
944 delete_globals;
945 IF csr_extract_def_id%ISOPEN THEN
946 CLOSE csr_extract_def_id;
947 END IF;
948
949 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 80);
950 RAISE;
951 --
952 END em_extract_process;
953
954 ----------------------------------------------------------------------------
955 -- Name : CB_CRITERIA_PROFILE --
956 -- Type : FUNCTION --
957 -- Access : Public --
958 -- Description : Function to select the assignments to be extracted --
959 -- for CB Report --
960 -- Parameters : --
961 -- IN : p_assignment_id NUMBER --
962 -- p_business_group_id NUMBER --
963 -- p_date_earned DATE --
964 -- OUT : p_warning_message VARCHAR2 --
965 -- p_error_message VARCHAR2 --
966 -- Change History : --
967 ----------------------------------------------------------------------------
968 -- Rev# Date Userid Description --
969 ----------------------------------------------------------------------------
970 -- 1.0 10-Jan-2004 bramajey Created this function --
971 -- 1.1 03-Feb-2004 saikrish Added check for assignment actions(3411273)
972 ----------------------------------------------------------------------------
973 FUNCTION cb_criteria_profile ( p_assignment_id IN VARCHAR2
974 , p_business_group_id IN NUMBER
975 , p_date_earned IN DATE
976 , p_warning_message OUT NOCOPY VARCHAR2
977 , p_error_message OUT NOCOPY VARCHAR2
978 )
979 RETURN VARCHAR2
980 IS
981 --
982
983 l_expat_indicator per_all_people_f.per_information8%TYPE;
984 l_cont_area hr_soft_coding_keyflex.segment21%TYPE;
985 l_assg_legal_employer NUMBER;
986 l_proc_name VARCHAR2(150);
987 l_return_value CHAR(1);
988 l_value CHAR(1);
989
990 l_phf_si_type VARCHAR2(50);
991 l_start_date DATE;
992 l_end_date DATE;
993 l_legal_employer_id NUMBER;
994 l_business_group_id NUMBER;
995 l_contribution_area VARCHAR2(30);
996 l_contribution_year VARCHAR2(30);
997 l_filling_date DATE;
998 l_report_type VARCHAR2(3);
999 l_element_name pay_element_types_f.element_name%TYPE;
1000
1001 l_mod_start_date DATE;
1002 l_mod_end_date DATE;
1003
1004 -- Cursor to return Expatriate Indicator and Legal Employer
1005 --
1006 CURSOR csr_valid_assignment( p_assignment_id IN VARCHAR2
1010 --
1007 ,p_start_date IN DATE
1008 )
1009 IS
1011 SELECT pap.per_information8 exp_indicator
1012 ,fnd_number.canonical_to_number(hsck.segment1) Legal_Employer
1013 FROM per_all_assignments_f paa
1014 ,per_all_people_f pap
1015 ,hr_soft_coding_keyflex hsck
1016 WHERE paa.assignment_id = p_assignment_id
1017 AND paa.business_group_id = p_business_group_id
1018 AND paa.person_id = pap.person_id
1019 AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1020 AND paa.assignment_type = 'E'
1021 AND p_start_date BETWEEN pap.effective_start_date
1022 AND pap.effective_end_date
1023 AND p_start_date BETWEEN paa.effective_start_date
1024 AND paa.effective_end_date;
1025 --
1026 --
1027 BEGIN
1028 --
1029 l_expat_indicator := 'Y';
1030 l_proc_name := 'pay_cn_ext.cb_criteria_profile';
1031 l_return_value := 'N';
1032
1033 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
1034
1035
1036 hr_utility.set_location('China : p_assignment_id -> ' || p_assignment_id , 20);
1037 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 20);
1038 hr_utility.set_location('China : p_date_earned -> ' || p_date_earned , 20);
1039
1040 -- Get Globals
1041 --
1042 get_globals ( p_phf_si_type => l_phf_si_type
1043 , p_start_date => l_start_date
1044 , p_end_date => l_end_date
1045 , p_legal_employer_id => l_legal_employer_id
1046 , p_business_group_id => l_business_group_id
1047 , p_contribution_area => l_contribution_area
1048 , p_contribution_year => l_contribution_year
1049 , p_filling_date => l_filling_date
1050 , p_report_type => l_report_type
1051 );
1052
1053 -- Check whether the assignment's business group id is same as the concurrent program
1054 -- business group id. If not return 'N'
1055 --
1056 IF (p_business_group_id <> l_business_group_id) THEN
1057 --
1058 hr_utility.set_location('China : Business Group does not match' , 30);
1059 RETURN l_return_value;
1060 --
1061 END IF;
1062 --
1063
1064 OPEN csr_valid_assignment(p_assignment_id, l_start_date);
1065 FETCH csr_valid_assignment
1066 INTO l_expat_indicator,l_assg_legal_employer ;
1067
1068 -- Check for valid assignment
1069 --
1070 IF csr_valid_assignment%NOTFOUND THEN
1071 -- Assignment is not live
1072 hr_utility.set_location('China : Assignment not valid' , 40);
1073 CLOSE csr_valid_assignment;
1074 RETURN l_return_value;
1075 --
1076 ELSE
1077 --
1078 -- Assignment is valid
1079 -- If the Expatriate Indicator is 'Y' or Assignment's
1080 -- Legal Employer is not same as the one submitted in Concurrent Request
1081 -- then the assignment is not be included
1082 --
1083 IF (l_expat_indicator = 'Y') OR (l_assg_legal_employer <> l_legal_employer_id) THEN
1084 --
1085 hr_utility.set_location('China : Legal Employer/Expat Ind mismatch', 50);
1086 CLOSE csr_valid_assignment;
1087 RETURN l_return_value;
1088 --
1089 END IF;
1090 --
1091 --
1092 END IF;
1093 --
1094 CLOSE csr_valid_assignment;
1095
1096 -- 3411840, Check the assignment's Override Contribution area is same as the concurrent parameter
1097 l_element_name := get_element_name(l_phf_si_type);
1098 hr_utility.set_location('China : l_element_name ->'|| l_element_name, 60);
1099
1100 l_cont_area := pay_cn_ext.get_override_sic_code(l_element_name,p_assignment_id,p_date_earned);
1101 hr_utility.set_location('China : l_cont_area ->'|| l_cont_area, 60);
1102
1103 IF l_cont_area IS NULL THEN
1104 -- Check the assignment's Contribution area is same as the concurrent parameter
1105 -- Set the contexts
1106 pay_balance_pkg.set_context('ASSIGNMENT_ID',p_assignment_id);
1107 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
1108
1109 -- Run the DBI
1110 l_cont_area := pay_balance_pkg.run_db_item('SCL_ASG_CN_SIC_AREA',p_business_group_id,'CN');
1111
1112 hr_utility.set_location('China : l_cont_area -> '|| l_cont_area, 80);
1113
1114 END IF;
1115
1116 hr_utility.set_location('China : l_cont_area ->'|| l_cont_area, 60);
1117 hr_utility.set_location('China : l_contribution_area ->'|| l_contribution_area, 60);
1118
1119 IF l_cont_area <> l_contribution_area THEN
1120 --
1121 hr_utility.set_location('China : Cont Area does not match' , 90);
1122 RETURN l_return_value;
1123 --
1124 END IF;
1125 --
1126
1127 -- Check whether the element entries exist for the PHF/SI type given
1128 -- in concurrent request
1129 --
1130 -- Bug 3415164
1134
1131 -- Using Start Date as Effective Date
1132 --
1133 l_value := pay_cn_ext.get_element_entry(p_assignment_id, p_business_group_id,l_start_date,l_phf_si_type);
1135 -- If element entry for the given PHF/SI Type does not exist for the assignment
1136 -- then the assignment is not eligible
1137 IF l_value = 'N' THEN
1138 --
1139 hr_utility.set_location('China : Element Entry not found ', 70);
1140 RETURN l_return_value;
1141 --
1142 END IF;
1143
1144 --Bug 3411273, Check whether assignment action exist for PREV_MONTH
1145 hr_utility.set_location('China : Check for PREV_MONTH ', 90);
1146
1147 l_mod_start_date := TRUNC(TRUNC(l_start_date,'MM')-1,'MM');
1148 l_mod_end_date := LAST_DAY(l_mod_start_date);
1149
1150 hr_utility.set_location('China : PREV_MONTH, l_mod_start_date '|| l_mod_start_date , 90);
1151 hr_utility.set_location('China : PREV_MONTH, l_mod_end_date '|| l_mod_end_date , 90);
1152
1153 l_value := pay_cn_ext.get_assignment_action(p_assignment_id, p_business_group_id,l_mod_start_date,l_mod_end_date);
1154 IF l_value = 'N' THEN
1155 hr_utility.set_location('China : Assignment Actions not found ', 90);
1156 RETURN l_return_value;
1157 END IF;
1158
1159
1160 -- Assignment should be included
1161 l_return_value := 'Y';
1162 hr_utility.set_location('China : l_return_value -> ' || l_return_value , 110);
1163
1164 hr_utility.set_location('China : Leaving -> '|| l_proc_name , 120);
1165
1166 RETURN l_return_value;
1167
1168 --
1169 EXCEPTION
1170 --
1171 WHEN OTHERS THEN
1172 IF csr_valid_assignment%ISOPEN THEN
1173 --
1174 CLOSE csr_valid_assignment;
1175 --
1176 END IF;
1177
1178 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 130);
1179 RAISE;
1180 --
1181 END cb_criteria_profile;
1182
1183 ----------------------------------------------------------------------------
1184 -- --
1185 -- Name : CA_CRITERIA_PROFILE --
1186 -- Type : FUNCTION --
1187 -- Access : Public --
1188 -- Description : Function to select the assignments to be extracted --
1189 -- for CA Report --
1190 -- Parameters : --
1191 -- IN : p_assignment_id NUMBER --
1192 -- p_business_group_id NUMBER --
1193 -- p_date_earned DATE --
1194 -- OUT: p_warning_message VARCHAR2 --
1195 -- p_error_message VARCHAR2 --
1196 -- Change History : --
1197 ----------------------------------------------------------------------------
1198 -- Rev# Date Userid Description --
1199 ----------------------------------------------------------------------------
1200 -- 1.0 10-Jan-2004 bramajey Created this function --
1201 -- 1.1 03-Feb-2004 saikrish Added check for assignment actions(3411273)
1202 ----------------------------------------------------------------------------
1203 FUNCTION ca_criteria_profile ( p_assignment_id IN VARCHAR2
1204 , p_business_group_id IN NUMBER
1205 , p_date_earned IN DATE
1206 , p_warning_message OUT NOCOPY VARCHAR2
1207 , p_error_message OUT NOCOPY VARCHAR2
1208 )
1209 RETURN VARCHAR2
1210 IS
1211 --
1212
1213 l_expat_indicator per_all_people_f.per_information8%TYPE;
1214 l_cont_area hr_soft_coding_keyflex.segment21%TYPE;
1215 l_assg_legal_employer NUMBER;
1216 l_proc_name VARCHAR2(150);
1217 l_return_value CHAR(1);
1218 l_value CHAR(1) ;
1219
1220 l_phf_si_type VARCHAR2(50);
1221 l_start_date DATE;
1222 l_end_date DATE;
1223 l_legal_employer_id NUMBER;
1224 l_business_group_id NUMBER;
1225 l_contribution_area VARCHAR2(30);
1226 l_contribution_year VARCHAR2(30);
1227 l_filling_date DATE;
1228 l_report_type VARCHAR2(3);
1229 l_element_name pay_element_types_f.element_name%TYPE;
1230
1231 -- Cursor to return Expatriate Indicator and Legal Employer
1232 --
1233 -- Bug 3415164
1234 -- Changed the cursor to use p_end_date
1235 --
1236 CURSOR csr_valid_assignment( p_assignment_id IN VARCHAR2
1237 ,p_end_date IN DATE
1238 )
1239 IS
1240 --
1241 SELECT pap.per_information8 exp_indicator
1242 ,fnd_number.canonical_to_number(hsck.segment1) Legal_Employer
1243 FROM per_all_assignments_f paa
1244 ,per_all_people_f pap
1245 ,hr_soft_coding_keyflex hsck
1246 WHERE paa.assignment_id = p_assignment_id
1247 AND paa.business_group_id = p_business_group_id
1251 AND p_end_date BETWEEN pap.effective_start_date
1248 AND paa.person_id = pap.person_id
1249 AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1250 AND paa.assignment_type = 'E'
1252 AND pap.effective_end_date
1253 AND p_end_date BETWEEN paa.effective_start_date
1254 AND paa.effective_end_date;
1255 --
1256
1257 --
1258 BEGIN
1259 --
1260 l_expat_indicator := 'Y';
1261 l_return_value := 'N';
1262 l_proc_name := 'pay_cn_ext.ca_criteria_profile';
1263
1264 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
1265
1266 hr_utility.set_location('China : p_assignment_id -> ' || p_assignment_id , 20);
1267 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 20);
1268 hr_utility.set_location('China : p_date_earned -> ' || p_date_earned , 20);
1269
1270 -- Get Globals
1271 --
1272 get_globals ( p_phf_si_type => l_phf_si_type
1273 , p_start_date => l_start_date
1274 , p_end_date => l_end_date
1275 , p_legal_employer_id => l_legal_employer_id
1276 , p_business_group_id => l_business_group_id
1277 , p_contribution_area => l_contribution_area
1278 , p_contribution_year => l_contribution_year
1279 , p_filling_date => l_filling_date
1280 , p_report_type => l_report_type
1281 );
1282
1283 -- Check whether the assignment's business group id is same as the concurrent program
1284 -- business group id. If not return 'N'
1285 --
1286 IF (p_business_group_id <> l_business_group_id) THEN
1287 --
1288 hr_utility.set_location('China : Business Group does not match' , 30);
1289 RETURN l_return_value;
1290 --
1291 END IF;
1292 --
1293
1294 -- Check for valid assignment
1295 -- Bug 3415164
1296 -- Passing l_end_date instead of l_start_date
1297 --
1298 OPEN csr_valid_assignment(p_assignment_id, l_end_date);
1299 FETCH csr_valid_assignment
1300 INTO l_expat_indicator,l_assg_legal_employer ;
1301
1302 IF csr_valid_assignment%NOTFOUND THEN
1303 -- Assignment is not live
1304 hr_utility.set_location('China : Assignment not valid' , 40);
1305 CLOSE csr_valid_assignment;
1306 RETURN l_return_value;
1307 --
1308 ELSE
1309 --
1310 -- Assignment is valid
1311 -- If the Expatriate Indicator is 'Y' or Assignment's
1312 -- Legal Employer is not same as the one submitted in Concurrent Request
1313 -- then the assignment is not be included
1314 --
1315 IF (l_expat_indicator = 'Y') OR (l_assg_legal_employer <> l_legal_employer_id) THEN
1316 --
1317 hr_utility.set_location('China : Legal Employer/Expat Ind mismatch', 50);
1318 CLOSE csr_valid_assignment;
1319 RETURN l_return_value;
1320 --
1321 END IF;
1322 --
1323 --
1324 END IF;
1325 --
1326 CLOSE csr_valid_assignment;
1327
1328 -- 3411840, Check the assignment's Override Contribution area is same as the concurrent parameter
1329 l_element_name := get_element_name(l_phf_si_type);
1330
1331 hr_utility.set_location('China : l_element_name ->'|| l_element_name , 90);
1332
1333 l_cont_area := pay_cn_ext.get_override_sic_code(l_element_name,p_assignment_id,p_date_earned);
1334
1335 hr_utility.set_location('China : l_cont_area ->'|| l_cont_area, 90);
1336
1337 IF l_cont_area IS NULL THEN
1338 -- Check the assignment's Contribution area is same as the concurrent parameter
1339 -- Set the contexts
1340 pay_balance_pkg.set_context('ASSIGNMENT_ID',p_assignment_id);
1341 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
1342
1343 -- Run the DBI
1344 l_cont_area := pay_balance_pkg.run_db_item('SCL_ASG_CN_SIC_AREA',p_business_group_id,'CN');
1345
1346 hr_utility.set_location('China : l_cont_area -> '|| l_cont_area, 80);
1347
1348 END IF;
1349
1350 hr_utility.set_location('China : l_cont_area ->'|| l_cont_area, 90);
1351 hr_utility.set_location('China : l_contribution_area ->'|| l_contribution_area , 90);
1352
1353 IF l_cont_area <> l_contribution_area THEN
1354 --
1355 hr_utility.set_location('China : Cont Area does not match' , 90);
1356 RETURN l_return_value;
1357 --
1358 END IF;
1359 --
1360
1361 -- Check whether the element entries exist for the PHF/SI type given
1362 -- in concurrent request
1363 -- Bug 3415164
1364 -- Using End Date as effective date
1365 --
1366 l_value := pay_cn_ext.get_element_entry(p_assignment_id, p_business_group_id,l_end_date,l_phf_si_type);
1367
1368 -- If element entry for the given PHF/SI Type does not exist for the assignment
1369 -- then the assignment is not eligible
1370 IF l_value = 'N' THEN
1371 --
1375 END IF;
1372 hr_utility.set_location('China : Element Entry not found ', 70);
1373 RETURN l_return_value;
1374 --
1376
1377 -- Bug 3411273, Check whether assignment action ids exist
1378 l_value := pay_cn_ext.get_assignment_action(p_assignment_id, p_business_group_id,l_start_date,l_end_date);
1379 IF l_value = 'N' THEN
1380 hr_utility.set_location('China : Assignment Actions not found ', 80);
1381 RETURN l_return_value;
1382 END IF;
1383
1384 -- Assignment should be included
1385 l_return_value :='Y';
1386 hr_utility.set_location('China : l_return_value -> ' || l_return_value , 110);
1387
1388 hr_utility.set_location('China : Leaving -> '|| l_proc_name , 120);
1389
1390 RETURN l_return_value;
1391 --
1392 EXCEPTION
1393 --
1394 WHEN OTHERS THEN
1395 IF csr_valid_assignment%ISOPEN THEN
1396 --
1397 CLOSE csr_valid_assignment;
1398 --
1399 END IF;
1400
1401 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 80);
1402 RAISE;
1403 --
1404 END ca_criteria_profile;
1405
1406 ----------------------------------------------------------------------------
1407 -- --
1408 -- Name : EM_CRITERIA_PROFILE --
1409 -- Type : FUNCTION --
1410 -- Access : Public --
1411 -- Description : Function to select the assignments to be extracted --
1412 -- for EM Report --
1413 -- Parameters : --
1414 -- IN : p_assignment_id NUMBER --
1415 -- p_business_group_id NUMBER --
1416 -- p_date_earned DATE --
1417 -- OUT: p_warning_message VARCHAR2 --
1418 -- p_error_message VARCHAR2 --
1419 -- Change History : --
1420 ----------------------------------------------------------------------------
1421 -- Rev# Date Userid Description --
1422 ----------------------------------------------------------------------------
1423 -- 1.0 10-Jan-2004 bramajey Created this function --
1424 -- 1.1 13-Jan-2004 Bramajey Changed data type of l_element_name --
1425 -- 1.2 20-Feb-2004 bramajey Introduced cursor csr_prev_acct_status --
1426 -- for bug 3456501 --
1427 ----------------------------------------------------------------------------
1428 FUNCTION em_criteria_profile ( p_assignment_id IN VARCHAR2
1429 , p_business_group_id IN NUMBER
1430 , p_date_earned IN DATE
1431 , p_warning_message OUT NOCOPY VARCHAR2
1432 , p_error_message OUT NOCOPY VARCHAR2
1433 )
1434 RETURN VARCHAR2
1435 IS
1436 --
1437
1438 l_element_name pay_element_types_f.element_name%TYPE;
1439 l_acct_status VARCHAR2(50);
1440 l_prev_acct_status VARCHAR2(50);
1441 l_reason_of_change VARCHAR2(10);
1442 l_expat_indicator per_all_people_f.per_information8%TYPE;
1443 l_cont_area hr_soft_coding_keyflex.segment21%TYPE;
1444 l_assg_legal_employer NUMBER;
1445 l_proc_name VARCHAR2(150);
1446 l_return_value CHAR(1);
1447 l_value CHAR(1);
1448
1449 l_phf_si_type VARCHAR2(50);
1450 l_start_date DATE;
1451 l_end_date DATE;
1452 l_legal_employer_id NUMBER;
1453 l_business_group_id NUMBER;
1454 l_contribution_area VARCHAR2(30);
1455 l_contribution_year VARCHAR2(30);
1456 l_filling_date DATE;
1457 l_report_type VARCHAR2(3);
1458
1459 -- Bug 3456501
1460 -- Included additional date effective check
1461
1462 -- Cursor to get the account status of the assignment
1463 --
1464 CURSOR csr_acct_status(p_element_name VARCHAR2
1465 ,p_input_value VARCHAR2
1466 ,p_start_date DATE
1467 ,p_end_date DATE)
1468 IS
1469 --
1470 SELECT eev.screen_entry_value
1471 FROM pay_element_entry_values_f eev
1472 ,pay_element_entries_f pee
1473 ,pay_element_links_f pil
1474 ,pay_input_values_f piv
1475 ,pay_element_types_f pet
1476 WHERE pet.element_name = p_element_name
1477 AND pet.element_type_id = piv.element_type_id
1478 AND piv.name = p_input_value
1479 AND pet.element_type_id = pil.element_type_id
1480 AND pil.element_link_id = pee.element_link_id
1481 AND pee.assignment_id = p_assignment_id
1482 AND pee.element_entry_id = eev.element_entry_id
1486 AND p_date_earned BETWEEN piv.effective_start_date
1483 AND eev.input_value_id = piv.input_value_id
1484 AND p_date_earned BETWEEN pet.effective_start_date
1485 AND pet.effective_end_date
1487 AND piv.effective_end_date
1488 AND p_date_earned BETWEEN pil.effective_start_date
1489 AND pil.effective_end_date
1490 AND p_date_earned BETWEEN pee.effective_start_date
1491 AND pee.effective_end_date
1492 AND p_date_earned BETWEEN eev.effective_start_date
1493 AND eev.effective_end_date
1494 AND eev.effective_start_date BETWEEN p_start_date
1495 AND p_end_date
1496 AND nvl(pee.entry_type, 'E') = 'E';
1497
1498 --
1499
1500 -- Bug 3456501
1501 -- Cursor to get the account status of the assignment
1502 -- for previous month
1503 --
1504 CURSOR csr_prev_acct_status(p_element_name VARCHAR2
1505 ,p_input_value VARCHAR2
1506 ,p_effective_date DATE)
1507 IS
1508 --
1509 SELECT eev.screen_entry_value
1510 FROM pay_element_entry_values_f eev
1511 ,pay_element_entries_f pee
1512 ,pay_element_links_f pil
1513 ,pay_input_values_f piv
1514 ,pay_element_types_f pet
1515 WHERE pet.element_name = p_element_name
1516 AND pet.element_type_id = piv.element_type_id
1517 AND piv.name = p_input_value
1518 AND pet.element_type_id = pil.element_type_id
1519 AND pil.element_link_id = pee.element_link_id
1520 AND pee.assignment_id = p_assignment_id
1521 AND pee.element_entry_id = eev.element_entry_id
1522 AND eev.input_value_id = piv.input_value_id
1523 AND p_effective_date BETWEEN pet.effective_start_date
1524 AND pet.effective_end_date
1525 AND p_effective_date BETWEEN piv.effective_start_date
1526 AND piv.effective_end_date
1527 AND p_effective_date BETWEEN pil.effective_start_date
1528 AND pil.effective_end_date
1529 AND p_effective_date BETWEEN pee.effective_start_date
1530 AND pee.effective_end_date
1531 AND p_effective_date BETWEEN eev.effective_start_date
1532 AND eev.effective_end_date
1533 AND nvl(pee.entry_type, 'E') = 'E';
1534 --
1535
1536 -- Cursor to return Expatriate Indicator and Legal Employer
1537 --
1538 -- Bug 3415164
1539 -- Changed the cursor to use p_end_date
1540 --
1541 CURSOR csr_valid_assignment( p_assignment_id IN VARCHAR2
1542 ,p_end_date IN DATE
1543 )
1544 IS
1545 --
1546 SELECT pap.per_information8 exp_indicator
1547 ,fnd_number.canonical_to_number(hsck.segment1) Legal_Employer
1548 FROM per_all_assignments_f paa
1549 ,per_all_people_f pap
1550 ,hr_soft_coding_keyflex hsck
1551 WHERE paa.assignment_id = p_assignment_id
1552 AND paa.business_group_id = p_business_group_id
1553 AND paa.person_id = pap.person_id
1554 AND paa.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
1555 AND paa.assignment_type = 'E'
1556 AND p_end_date BETWEEN paa.effective_start_date
1557 AND paa.effective_end_date
1558 AND p_end_date BETWEEN pap.effective_start_date
1559 AND pap.effective_end_date;
1560
1561 --
1562 BEGIN
1563 --
1564 l_proc_name := 'pay_cn_ext.em_criteria_profile';
1565 l_expat_indicator := 'Y';
1566 l_return_value := 'N';
1567
1568 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
1569
1570 hr_utility.set_location('China : p_assignment_id -> ' || p_assignment_id , 20);
1571 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 20);
1572 hr_utility.set_location('China : p_date_earned -> ' || p_date_earned , 20);
1573
1574 -- Get Globals
1575 --
1576 get_globals ( p_phf_si_type => l_phf_si_type
1577 , p_start_date => l_start_date
1578 , p_end_date => l_end_date
1579 , p_legal_employer_id => l_legal_employer_id
1580 , p_business_group_id => l_business_group_id
1581 , p_contribution_area => l_contribution_area
1582 , p_contribution_year => l_contribution_year
1583 , p_filling_date => l_filling_date
1584 , p_report_type => l_report_type
1585 );
1586
1590 --
1587 -- Check whether the assignment's business group id is same as the concurrent program
1588 -- business group id. If not return 'N'
1589 IF (p_business_group_id <> l_business_group_id) THEN
1591 hr_utility.set_location('China : business grp mismtach ' || l_end_date , 10);
1592 RETURN l_return_value;
1593 --
1594 END IF;
1595 --
1596
1597 -- Check for valid assignment
1598 -- Bug 3415164
1599 -- Passing l_end_date instead of l_start_date
1600 OPEN csr_valid_assignment(p_assignment_id, l_end_date);
1601 FETCH csr_valid_assignment
1602 INTO l_expat_indicator,l_assg_legal_employer ;
1603
1604
1605 IF csr_valid_assignment%NOTFOUND THEN
1606 --
1607 -- Assignment is not live
1608 hr_utility.set_location('China : Assignment not valid' , 40);
1609 CLOSE csr_valid_assignment;
1610 RETURN l_return_value;
1611 --
1612 ELSE
1613 --
1614 -- Assignment is valid
1615 -- If the Expatriate Indicator is 'Y' or Assignment's
1616 -- Legal Employer is not same as the one submitted in Concurrent Request
1617 -- then the assignment is not be included
1618 --
1619 IF (l_expat_indicator = 'Y') OR (l_assg_legal_employer <> l_legal_employer_id) THEN
1620 --
1621 hr_utility.set_location('China : Legal Employer/Expat Ind mismatch', 50);
1622 CLOSE csr_valid_assignment;
1623 RETURN l_return_value;
1624 --
1625 END IF;
1626 --
1627 --
1628 END IF;
1629 --
1630 CLOSE csr_valid_assignment;
1631
1632 -- 3411840, Check the assignment's Override Contribution area is same as the concurrent parameter
1633 l_element_name := get_element_name(l_phf_si_type);
1634 hr_utility.set_location('China : l_element_name ->'|| l_element_name, 60);
1635
1636 l_cont_area := pay_cn_ext.get_override_sic_code(l_element_name,p_assignment_id,p_date_earned);
1637 hr_utility.set_location('China : l_cont_area ->'|| l_cont_area, 60);
1638
1639 IF l_cont_area IS NULL THEN
1640 -- Check the assignment's Contribution area is same as the concurrent parameter
1641 -- Set the contexts
1642 pay_balance_pkg.set_context('ASSIGNMENT_ID',p_assignment_id);
1643 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
1644
1645 -- Run the DBI
1646 l_cont_area := pay_balance_pkg.run_db_item('SCL_ASG_CN_SIC_AREA',p_business_group_id,'CN');
1647
1648 hr_utility.set_location('China : l_cont_area -> '|| l_cont_area, 70);
1649
1650 END IF;
1651
1652 hr_utility.set_location('China : l_cont_area ->'|| l_cont_area, 80);
1653 hr_utility.set_location('China : l_contribution_area ->'|| l_contribution_area, 80);
1654
1655 IF l_cont_area <> l_contribution_area THEN
1656 --
1657 hr_utility.set_location('China : Cont Area does not match' , 85);
1658 RETURN l_return_value;
1659 --
1660 END IF;
1661 --
1662
1663 -- Check whether the element entries exist for the PHF/SI type given
1664 -- in concurrent request
1665 --
1666 l_value := pay_cn_ext.get_element_entry(p_assignment_id, p_business_group_id,l_end_date,l_phf_si_type);
1667
1668 -- If element entry for the given PHF/SI Type does not exist for the assignment
1669 -- then the assignment is not eligible
1670 IF l_value = 'N' THEN
1671 --
1672 hr_utility.set_location('China : Element Entry not found ', 90);
1673 RETURN l_return_value;
1674 --
1675 END IF;
1676 --
1677
1678 -- Code to check the account status
1679 --
1680
1681 -- Get the element name
1682 l_element_name := get_element_name(l_phf_si_type);
1683
1684 -- Get the account status
1685 --
1686 OPEN csr_acct_status( l_element_name, 'Account Status',l_start_date,l_end_date);
1687 FETCH csr_acct_status
1688 INTO l_acct_status;
1689
1690 -- Check whether account status exist
1691 --
1692 IF csr_acct_status%NOTFOUND THEN
1693 --
1694 hr_utility.set_location('China : Acct status not found' || l_end_date , 100);
1695 CLOSE csr_acct_status;
1696 RETURN l_return_value;
1697 --
1698 -- Check whether account status is OPEN, CLOSED, TRANSFER IN or TRANSFER OUT
1699 --
1700 ELSIF l_acct_status IN ('OPEN','CLOSED','TRANSFER IN','TRANSFER OUT') THEN
1701 --
1702 CLOSE csr_acct_status;
1703 -- Bug 3456501 starts
1704 -- Check whether Current month's account status is same as previous month
1705 OPEN csr_prev_acct_status( l_element_name, 'Account Status',(l_start_date-1));
1706 FETCH csr_prev_acct_status
1707 INTO l_prev_acct_status;
1708
1709 IF ((csr_prev_acct_status%FOUND) AND (l_prev_acct_status = l_acct_status)) THEN
1710 --
1711 -- Account status is same as previous month. Hence no need to report this
1712 -- assignment
1713 hr_utility.set_location('China : Acct status same as prev month' || l_end_date , 110);
1714 CLOSE csr_prev_acct_status;
1715 RETURN l_return_value;
1716 --
1717 END IF;
1718
1719 CLOSE csr_prev_acct_status;
1720 -- Bug 3456501 ends
1721
1725 OPEN csr_acct_status( l_element_name, 'Status Change Reason',l_start_date,l_end_date);
1722 -- Check whether the reason of change is 'Others'
1723 --
1724
1726 FETCH csr_acct_status
1727 INTO l_reason_of_change;
1728 hr_utility.set_location('China : Reason Of Change' ||l_reason_of_change , 115);
1729 --
1730 IF (l_reason_of_change IS NULL) OR (l_reason_of_change = '11') THEN
1731 --
1732 hr_utility.set_location('China :Reason of change is Others ' , 120);
1733 l_return_value := 'N';
1734 CLOSE csr_acct_status;
1735 RETURN l_return_value;
1736 --
1737 ELSE
1738 --
1739 hr_utility.set_location('China : Valid acct Status' , 125);
1740 l_return_value := 'Y';
1741 CLOSE csr_acct_status;
1742 --
1743 END IF;
1744 --
1745 ELSE
1746 --
1747 -- If not
1748 -- Check whether the reason of change is 'Death of Employee'
1749 --
1750 CLOSE csr_acct_status;
1751 OPEN csr_acct_status( l_element_name, 'Status Change Reason',l_start_date,l_end_date);
1752 FETCH csr_acct_status
1753 INTO l_reason_of_change;
1754 hr_utility.set_location('China : Reason Of Change' ||l_reason_of_change , 130);
1755 --
1756 IF l_reason_of_change = '10' THEN
1757 --
1758 hr_utility.set_location('China : Dead ' , 140);
1759 l_return_value := 'Y';
1760 --
1761 ELSE
1762 --
1763 l_return_value := 'N';
1764 CLOSE csr_acct_status;
1765 RETURN l_return_value;
1766
1767 --
1768 END IF;
1769 --
1770 CLOSE csr_acct_status;
1771 --
1772 END IF;
1773 --
1774
1775 -- Assignment should be included
1776 l_return_value :='Y';
1777 hr_utility.set_location('China : l_return_value -> ' || l_return_value , 150);
1778 hr_utility.set_location('China : China Leaving -> ' || l_proc_name , 160);
1779
1780 RETURN l_return_value;
1781 --
1782 EXCEPTION
1783 --
1784 WHEN OTHERS THEN
1785 IF csr_valid_assignment%ISOPEN THEN
1786 --
1787 CLOSE csr_valid_assignment;
1788 --
1789 END IF;
1790 IF csr_acct_status%ISOPEN THEN
1791 --
1792 CLOSE csr_acct_status;
1793 --
1794 END IF;
1795
1796 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 180);
1797 RAISE;
1798 --
1799 END em_criteria_profile;
1800
1801
1802 ----------------------------------------------------------------------------
1803 -- --
1804 -- Name : GET_EMPLOYER_INFO --
1805 -- Type : FUNCTION --
1806 -- Access : Public --
1807 -- Description : Function to get employer information based on the --
1808 -- info type --
1809 -- --
1810 -- Parameters : --
1811 -- IN : p_info_type VARCHAR2 --
1812 -- p_assignment_id NUMBER --
1813 -- Change History : --
1814 ----------------------------------------------------------------------------
1815 -- Rev# Date Userid Description --
1816 ----------------------------------------------------------------------------
1817 -- 1.0 10-Jan-2004 bramajey Created this function --
1818 -- 1.1 06-Jul-2004 sshankar Modified code as required to support --
1819 -- Enterprise Annuity. (Bug 3593118) --
1820 -- 1.2 15-Sep-2004 snekkala Modified code to fetch hukuo type --
1821 -- 1.3 05-Oct-2004 snekkala Modified the datatype from VARCHAR2(30) --
1822 -- to hr_organization_information --
1823 -- org_information5%TYPE
1824 -- 1.4 14-Mar-2008 dduvvuri Modified call to get_phf_si_rates (6828199) --
1825 ----------------------------------------------------------------------------
1826 FUNCTION get_employer_info(p_assignment_id IN NUMBER
1827 ,p_info_type IN VARCHAR2)
1828 RETURN VARCHAR2
1829 IS
1830 --
1831
1832 l_proc_name VARCHAR2(150);
1833 l_return_value VARCHAR2(300);
1834 l_message VARCHAR2(3000);
1835 l_ee_rate_type VARCHAR2(30);
1836 l_er_rate_type VARCHAR2(30);
1837 l_ee_rate VARCHAR2(30);
1838 l_er_rate VARCHAR2(30);
1839 /* Changes for bug 6828199 starts */
1840 l_ee_thrhld_rate VARCHAR2(30);
1841 l_er_thrhld_rate VARCHAR2(30);
1842 /* Changes for bug 6828199 end */
1843 l_ee_rounding_method VARCHAR2(30);
1844 l_er_rounding_method VARCHAR2(30);
1845 --
1846 -- Bug 3904374 Changes start. Modified the datatype from VARCHAR2(30) to hr_organization_information.org_information5%TYPE
1847 --
1851 l_maternity_reg_num hr_organization_information.org_information5%TYPE;
1848 l_phf_reg_num hr_organization_information.org_information5%TYPE;
1849 l_pension_reg_num hr_organization_information.org_information5%TYPE;
1850 l_injury_reg_num hr_organization_information.org_information5%TYPE;
1852 l_unemp_reg_num hr_organization_information.org_information5%TYPE;
1853 l_medical_reg_num hr_organization_information.org_information5%TYPE;
1854 l_ea_reg_num hr_organization_information.org_information5%TYPE; -- Bug 3593118. Enterprise Annuity Employer Reg Num
1855 --
1856 -- Bug 3904374 Changes end
1857 --
1858
1859 l_filing_date DATE;
1860
1861 l_phf_si_type VARCHAR2(50);
1862 l_start_date DATE;
1863 l_end_date DATE;
1864 l_legal_employer_id NUMBER;
1865 l_business_group_id NUMBER;
1866 l_contribution_area VARCHAR2(30);
1867 l_contribution_year VARCHAR2(30);
1868 l_filling_date DATE;
1869 l_report_type VARCHAR2(3);
1870 l_hukuo_type VARCHAR2(100); -- Bug 3886228. Hukuo type
1871
1872 -- Cursor to fetch Legal Employer Name
1873 --
1874 CURSOR csr_legal_employer_name(p_legal_employer IN NUMBER)
1875 IS
1876 SELECT name
1877 FROM hr_all_organization_units
1878 WHERE organization_id = p_legal_employer;
1879 --
1880
1881 -- Cursor to fetch Business Group Currency
1882 --
1883 CURSOR csr_org_currency(p_business_group_id IN NUMBER)
1884 IS
1885 SELECT hoi.org_information10
1886 FROM hr_organization_information hoi
1887 WHERE hoi.organization_id = p_business_group_id
1888 AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'Business_Group_Information';
1889 --
1890
1891 -- Cursor to fetch Enterprise Organisation Category
1892 --
1893 CURSOR csr_ent_org_category(p_legal_employer_id IN NUMBER)
1894 IS
1895 SELECT hr_general.decode_lookup('CN_ENTRP_CATEGORY',hoi.org_information8)
1896 FROM hr_organization_information hoi
1897 WHERE hoi.organization_id = p_legal_employer_id
1898 AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'PER_CORPORATE_INFO_CN';
1899 --
1900
1901 -- Cursor to fetch Enterprise Organisation code
1902 --
1903 CURSOR csr_ent_org_code(p_legal_employer_id IN NUMBER)
1904 IS
1905 SELECT hoi.org_information7
1906 FROM hr_organization_information hoi
1907 WHERE hoi.organization_id = p_legal_employer_id
1908 AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'PER_CORPORATE_INFO_CN';
1909 --
1910
1911 -- Cursor to fetch PHF/SI Registration number
1912 --
1913 CURSOR csr_phf_si_reg_num(p_legal_employer_id IN NUMBER)
1914 IS
1915 SELECT hoi.org_information5 -- PHF
1916 ,hoi.org_information6 -- Pension
1917 ,hoi.org_information15 -- Injury
1918 ,hoi.org_information17 -- Maternity
1919 ,hoi.org_information19 -- Unemployment
1920 ,hoi.org_information7 -- Medical
1921 ,hoi.org_information3 -- Enterprise Annuity. (Bug 3593118)
1922 FROM hr_organization_information hoi
1923 WHERE hoi.organization_id = p_legal_employer_id
1924 AND REPLACE(ltrim(rtrim(hoi.org_information_context)),' ','_') = 'PER_EMPLOYER_INFO_CN';
1925 --
1926
1927 --
1928 -- Bug 3886228 Changes start
1929 -- Cursor to fetch Hukuo Type
1930 --
1931 CURSOR csr_get_hukuo_type
1932 IS
1933 SELECT ppf.PER_INFORMATION4 -- Hukuo Type
1934 FROM per_assignments_f paf
1935 ,per_people_f ppf
1936 WHERE paf.assignment_id = p_assignment_id
1937 AND paf.person_id = ppf.person_id;
1938 --
1939 -- Bug 3886228 Changes end
1940 --
1941 BEGIN
1942 --
1943 l_proc_name := 'pay_cn_ext.get_employer_info';
1944 l_return_value:= NULL;
1945
1946 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
1947 hr_utility.set_location('China : Info Type-> '||p_info_type, 10);
1948
1949 -- Get Globals
1950 --
1951 get_globals ( p_phf_si_type => l_phf_si_type
1952 , p_start_date => l_start_date
1953 , p_end_date => l_end_date
1954 , p_legal_employer_id => l_legal_employer_id
1955 , p_business_group_id => l_business_group_id
1956 , p_contribution_area => l_contribution_area
1957 , p_contribution_year => l_contribution_year
1958 , p_filling_date => l_filling_date
1959 , p_report_type => l_report_type
1960 );
1961
1962 -- If info required is Filing Date
1963 --
1964 IF p_info_type = 'FILING_DATE' THEN
1965 --
1966 l_filing_date := NVL(l_filling_date,l_start_date);
1967 l_return_value := TO_CHAR(l_filing_date,'YYYY/MM/DD');
1968 hr_utility.set_location('China : Filling Date -> '||l_return_value, 20);
1969 RETURN l_return_value;
1973 -- If info required is Insurance Type Code
1970 --
1971 END IF;
1972
1974 --
1975 IF p_info_type = 'INSURANCE_TYPE_CODE' THEN
1976 --
1977 l_return_value := l_phf_si_type;
1978 hr_utility.set_location('China : Insurance Type Code -> '||l_return_value, 30);
1979 RETURN l_return_value;
1980 --
1981 END IF;
1982
1983 -- If info required is Insurance Type
1984 --
1985 IF p_info_type = 'INSURANCE_TYPE' THEN
1986 --
1987 l_return_value := hr_general.decode_lookup(p_lookup_type => 'CN_PHF_SI_CODE'
1988 ,p_lookup_code => l_phf_si_type
1989 );
1990 hr_utility.set_location('China : Insurance Type -> '||l_return_value, 40);
1991 return l_return_value;
1992 --
1993 END IF;
1994
1995 -- If info required is Employer Name
1996 --
1997 IF p_info_type = 'EMPLOYER_NAME' THEN
1998 --
1999 OPEN csr_legal_employer_name(l_legal_employer_id);
2000 FETCH csr_legal_employer_name
2001 INTO l_return_value;
2002 CLOSE csr_legal_employer_name;
2003 hr_utility.set_location('China : Employer Name -> '||l_return_value, 50);
2004 RETURN l_return_value;
2005 --
2006 END IF;
2007
2008 -- If info required is Enterprise Organization Code
2009 --
2010 IF p_info_type = 'ENT_ORG_CODE' THEN
2011 --
2012 OPEN csr_ent_org_code(l_legal_employer_id);
2013 FETCH csr_ent_org_code
2014 INTO l_return_value;
2015 CLOSE csr_ent_org_code;
2016 hr_utility.set_location('China : Employer Name -> '||l_return_value, 60);
2017 RETURN l_return_value;
2018 --
2019 END IF;
2020
2021 -- If info required is Enterprise Organization Category
2022 --
2023 IF p_info_type = 'ENT_ORG_CATEGORY' THEN
2024 --
2025 OPEN csr_ent_org_category(l_legal_employer_id);
2026 FETCH csr_ent_org_category
2027 INTO l_return_value;
2028 hr_utility.set_location('China : Enterprise Org Category -> '||l_return_value, 70);
2029 CLOSE csr_ent_org_category;
2030 RETURN l_return_value;
2031 --
2032 END IF;
2033
2034 -- If info required is PHF SI Period
2035 --
2036 IF p_info_type = 'PHF_SI_PERIOD' THEN
2037 --
2038 -- Check if report type is EM
2039 IF l_report_type = 'EM' THEN
2040 --
2041 l_return_value := TO_CHAR(l_start_date,'YYYY/MM');
2042 --
2043 ELSE
2044 --
2045 l_return_value := TO_CHAR(l_start_date,'YYYY/MM/DD') ||'-'|| TO_CHAR(l_end_date,'YYYY/MM/DD');
2046 --
2047 END IF;
2048 hr_utility.set_location('China : Enterprise Org Category -> '||l_return_value, 80);
2049 RETURN l_return_value;
2050 --
2051 END IF;
2052
2053 -- If info required is Currency
2054 --
2055 IF p_info_type = 'ORG_CURRENCY' THEN
2056 --
2057 OPEN csr_org_currency(l_business_group_id);
2058 FETCH csr_org_currency
2059 INTO l_return_value;
2060 CLOSE csr_org_currency;
2061 hr_utility.set_location('China : Org Currency -> '||l_return_value, 90);
2062 RETURN l_return_value;
2063 --
2064 END IF;
2065
2066 -- If info required is Account Number
2067 --
2068 IF p_info_type = 'PHF_SI_ACC_NUM' THEN
2069 --
2070 OPEN csr_phf_si_reg_num(l_legal_employer_id);
2071 FETCH csr_phf_si_reg_num
2072 INTO l_phf_reg_num
2073 ,l_pension_reg_num
2074 ,l_injury_reg_num
2075 ,l_maternity_reg_num
2076 ,l_unemp_reg_num
2077 ,l_medical_reg_num
2078 ,l_ea_reg_num; -- Enterprise Annuity. Bug 3593118
2079 CLOSE csr_phf_si_reg_num;
2080
2081 IF l_phf_si_type = 'PHF' THEN
2082 --
2083 l_return_value := l_phf_reg_num;
2084 --
2085 ELSIF l_phf_si_type = 'PENSION' THEN
2086 --
2087 l_return_value := l_pension_reg_num;
2088 --
2089 ELSIF l_phf_si_type = 'INJURY' THEN
2090 --
2091 l_return_value := l_injury_reg_num;
2092 --
2093 ELSIF l_phf_si_type = 'MATERNITY' THEN
2094 --
2095 l_return_value := l_maternity_reg_num;
2096 --
2097 ELSIF l_phf_si_type = 'UNEMPLOYMENT' THEN
2098 --
2099 l_return_value := l_unemp_reg_num;
2100 --
2101 ELSIF l_phf_si_type IN ('MEDICAL','SUPPMED') THEN
2102 --
2103 l_return_value := l_medical_reg_num;
2104 --
2105 ELSIF l_phf_si_type = 'ENTANN' THEN
2106 --
2107 --Enterprise Annuity. Bug 3593118
2108 --
2109 l_return_value := l_ea_reg_num;
2110 --
2111 END IF;
2112
2113 --
2114 hr_utility.set_location('China : PHF/SI Reg Num -> '||l_return_value, 100);
2115 RETURN l_return_value;
2116 --
2117 END IF;
2118
2119 -- If info required is Filing Date
2120 --
2121 IF p_info_type = 'ER_CONT_PERCENT' THEN
2122 --
2123 -- Bug 3886228 Changes start
2124 --
2125 OPEN csr_get_hukuo_type;
2129 CLOSE csr_get_hukuo_type;
2126 FETCH csr_get_hukuo_type INTO l_hukuo_type;
2127 IF csr_get_hukuo_type%NOTFOUND THEN
2128 l_hukuo_type:=NULL;
2130 END IF;
2131 CLOSE csr_get_hukuo_type;
2132 --
2133 -- Bug 3886228 Changes end
2134 --
2135 -- Bug 3593118
2136 -- Enterprise Annuity - Added new parameter p_assignment_id in call to
2137 -- get_phf_si_rates
2138 --
2139 l_message := pay_cn_deductions.get_phf_si_rates
2140 (p_assignment_id => NULL
2141 ,p_business_group_id => l_business_group_id
2142 ,p_contribution_area => l_contribution_area
2143 ,p_phf_si_type => l_phf_si_type
2144 ,p_employer_id => l_legal_employer_id
2145 ,p_hukou_type => l_hukuo_type -- Bug 3886228 Changed NULL to l_hukuo_type
2146 ,p_effective_date => l_start_date
2147 --
2148 ,p_ee_rate_type => l_ee_rate_type
2149 ,p_er_rate_type => l_er_rate_type
2150 ,p_ee_rate => l_ee_rate
2151 ,p_er_rate => l_er_rate
2152 ,p_ee_thrhld_rate => l_ee_thrhld_rate /* For bug 6828199 */
2153 ,p_er_thrhld_rate => l_er_thrhld_rate /* For bug 6828199 */
2154 ,p_ee_rounding_method => l_ee_rounding_method
2155 ,p_er_rounding_method => l_er_rounding_method
2156 );
2157
2158 IF l_message = 'SUCCESS' THEN
2159 --
2160 IF l_er_rate_type = 'PERCENTAGE' THEN
2161 --
2162 l_return_value := l_er_rate;
2163 hr_utility.set_location('China : ER Cont Percent -> '||l_return_value, 110);
2164 RETURN l_return_value;
2165 --
2166 END IF;
2167 --
2168 ELSE
2169 --
2170 RETURN l_return_value;
2171 --
2172 END IF;
2173 --
2174 END IF;
2175
2176 -- No Info Type Matches
2177 --
2178 hr_utility.set_location('China : Leaving -> '|| l_proc_name , 120);
2179 RETURN l_return_value;
2180 --
2181 EXCEPTION
2182 --
2183 WHEN OTHERS THEN
2184 IF csr_legal_employer_name%ISOPEN THEN
2185 --
2186 CLOSE csr_legal_employer_name;
2187 --
2188 END IF;
2189
2190 IF csr_org_currency%ISOPEN THEN
2191 --
2192 CLOSE csr_org_currency;
2193 --
2194 END IF;
2195 IF csr_ent_org_category%ISOPEN THEN
2196 --
2197 CLOSE csr_ent_org_category;
2198 --
2199 END IF;
2200
2201 IF csr_ent_org_code%ISOPEN THEN
2202 --
2203 CLOSE csr_ent_org_code;
2204 --
2205 END IF;
2206
2207 IF csr_phf_si_reg_num%ISOPEN THEN
2208 --
2209 CLOSE csr_phf_si_reg_num;
2210 --
2211 END IF;
2212
2213 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 180);
2214 RAISE;
2215 --
2216 END get_employer_info;
2217
2218
2219 ----------------------------------------------------------------------------
2220 -- --
2221 -- Name : GET_EMPLOYEE_INFO --
2222 -- Type : FUNCTION --
2223 -- Access : Public --
2224 -- Description : Function to get Employee Details based on Info Type --
2225 -- --
2226 -- Parameters : --
2227 -- IN : p_assignment_id NUMBER --
2228 -- p_date_earned DATE --
2229 -- p_info_type VARCHAR2 --
2230 -- Change History : --
2231 ----------------------------------------------------------------------------
2232 -- Rev# Date Userid Description --
2233 ----------------------------------------------------------------------------
2234 -- 1.0 10-Jan-2004 bramajey Created this function --
2235 -- 1.1 06-Jul-2004 sshankar Added new parameter p_assignment_id in --
2236 -- call to get_phf_si_rates (Bug 3593118) --
2237 -- to support Enterprise Annuity --
2238 -- 1.2 15-Sep-2004 snekkala Added code to get phf/si rates based on --
2239 -- Hukuo Type --
2240 -- 1.3 14-Mar-2008 dduvvuri Modified call to get_phf_si_rates (bug 6828199)
2241 ----------------------------------------------------------------------------
2242 FUNCTION get_employee_info(p_assignment_id IN NUMBER
2243 ,p_date_earned IN DATE
2244 ,p_info_type IN VARCHAR2)
2245 RETURN VARCHAR2
2246 IS
2247 --
2248 l_return_value VARCHAR2(300);
2249 l_proc_name VARCHAR2(150);
2250
2254 l_er_rate VARCHAR2(30);
2251 l_ee_rate_type VARCHAR2(30);
2252 l_er_rate_type VARCHAR2(30);
2253 l_ee_rate VARCHAR2(30);
2255 /* Changes for bug 6828199 start */
2256 l_ee_thrhld_rate VARCHAR2(30);
2257 l_er_thrhld_rate VARCHAR2(30);
2258 /* Changes for bug 6828199 end */
2259 l_ee_rounding_method VARCHAR2(30);
2260 l_er_rounding_method VARCHAR2(30);
2261 l_message VARCHAR2(2000);
2262 l_work_life_date VARCHAR2(50);
2263
2264 l_phf_si_type VARCHAR2(50);
2265 l_start_date DATE;
2266 l_end_date DATE;
2267 l_legal_employer_id NUMBER;
2268 l_business_group_id NUMBER;
2269 l_contribution_area VARCHAR2(30);
2270 l_contribution_year VARCHAR2(30);
2271 l_filling_date DATE;
2272 l_report_type VARCHAR2(3);
2273 l_hukuo_type VARCHAR2(100); -- Bug 3886228. Hukuo Type
2274
2275 -- Cursor to fetch Ethnic Group
2276 --
2277 CURSOR csr_ethnic_group
2278 IS
2279 --
2280 SELECT hr_general.decode_lookup('CN_RACE',pap.per_information17)
2281 FROM per_all_assignments_f paa
2282 ,per_all_people_f pap
2283 WHERE paa.assignment_id = p_assignment_id
2284 AND pap.person_id = paa.person_id
2285 AND p_date_earned BETWEEN paa.effective_start_date
2286 AND paa.effective_end_date
2287 AND p_date_earned BETWEEN pap.effective_start_date
2288 AND pap.effective_end_date;
2289 --
2290
2291 -- Cursor to fetch Hukou Type
2292 --
2293 /*3592894, Removed table per_people_extra_info*/
2294 CURSOR csr_hukou_type
2295 IS
2296 --
2297 SELECT hr_general.decode_lookup('CN_HUKOU_TYPE',pap.per_information4 )
2298 FROM per_all_assignments_f paa
2299 ,per_all_people_f pap
2300 WHERE paa.assignment_id = p_assignment_id
2301 AND pap.person_id = paa.person_id
2302 AND p_date_earned BETWEEN paa.effective_start_date
2303 AND paa.effective_end_date
2304 AND p_date_earned BETWEEN pap.effective_start_date
2305 AND pap.effective_end_date;
2306 --
2307
2308 -- Cursor to fetch Work Life Start Date
2309 --
2310 CURSOR csr_work_life_start_date
2311 IS
2312 --
2313 SELECT ppei.pei_information2 Work_Life_Start_Date
2314 FROM per_all_assignments_f paa
2315 ,per_all_people_f pap
2316 ,per_people_extra_info ppei
2317 WHERE paa.assignment_id = p_assignment_id
2318 AND pap.person_id = paa.person_id
2319 AND ppei.person_id = pap.person_id (+)
2320 AND ppei.information_type = 'PER_OTH_EMP_DATA_CN'
2321 AND p_date_earned BETWEEN paa.effective_start_date
2322 AND paa.effective_end_date
2323 AND p_date_earned BETWEEN pap.effective_start_date
2324 AND pap.effective_end_date;
2325 --
2326
2327 -- Cursor to fetch Job Category
2328 --
2329 CURSOR csr_job_category
2330 IS
2331 --
2332 SELECT hr_general.decode_lookup('JOB_CATEGORIES', pjei.jei_information1)
2333 FROM per_all_assignments_f paa
2334 ,per_job_extra_info pjei
2335 WHERE paa.assignment_id = p_assignment_id
2336 AND pjei.job_id = paa.job_id
2337 AND information_type = 'Job Category'
2338 AND jei_information_category = 'Job Category'
2339 AND p_date_earned BETWEEN paa.effective_start_date
2340 AND paa.effective_end_date;
2341 --
2342 --
2343 -- Bug 3886228 Changes start
2344 -- Cursor to fetch Hukuo Type
2345 --
2346 CURSOR csr_get_hukuo_type
2347 IS
2348 SELECT ppf.PER_INFORMATION4 -- Hukuo Type
2349 FROM per_assignments_f paf
2350 ,per_people_f ppf
2351 WHERE paf.assignment_id = p_assignment_id
2352 AND paf.person_id = ppf.person_id;
2353 --
2354 -- Bug 3886228 Changes end
2355 --
2356 BEGIN
2357 --
2358 l_proc_name := 'pay_cn_ext.get_employee_info';
2359 l_return_value := NULL;
2360
2361 hr_utility.set_location('China : Entering -> '||l_proc_name, 10);
2362 hr_utility.set_location('China : Assignment ID -> '||p_assignment_id, 10);
2363 hr_utility.set_location('China : Date Earned -> '||p_date_earned, 10);
2364 hr_utility.set_location('China : Info Type -> '||p_info_type, 10);
2365
2366 -- Get Globals
2367 --
2368 get_globals ( p_phf_si_type => l_phf_si_type
2369 , p_start_date => l_start_date
2370 , p_end_date => l_end_date
2371 , p_legal_employer_id => l_legal_employer_id
2372 , p_business_group_id => l_business_group_id
2373 , p_contribution_area => l_contribution_area
2374 , p_contribution_year => l_contribution_year
2375 , p_filling_date => l_filling_date
2376 , p_report_type => l_report_type
2380 --
2377 );
2378
2379 -- If info Type required is Contribution Percent
2381 IF p_info_type = 'EE_CONT_PERCENT' THEN
2382 --
2383 -- Bug 3886228 Changes start
2384 --
2385 OPEN csr_get_hukuo_type;
2386 FETCH csr_get_hukuo_type INTO l_hukuo_type;
2387 IF csr_get_hukuo_type%NOTFOUND THEN
2388 l_hukuo_type:=NULL;
2389 CLOSE csr_get_hukuo_type;
2390 END IF;
2391 CLOSE csr_get_hukuo_type;
2392 --
2393 -- Bug 3886228 Changes end
2394 --
2395 -- Bug 3593118
2396 -- Enterprise Annuity - Added new parameter p_assignment_id in call to
2397 -- get_phf_si_rates
2398 --
2399 l_message := pay_cn_deductions.get_phf_si_rates
2400 (p_assignment_id => p_assignment_id
2401 ,p_business_group_id => l_business_group_id
2402 ,p_contribution_area => l_contribution_area
2403 ,p_phf_si_type => l_phf_si_type
2404 ,p_employer_id => l_legal_employer_id
2405 ,p_hukou_type => l_hukuo_type -- Bug 3886228 Changed NULL to l_hukuo_type
2406 ,p_effective_date => l_start_date
2407 --
2408 ,p_ee_rate_type => l_ee_rate_type
2409 ,p_er_rate_type => l_er_rate_type
2410 ,p_ee_rate => l_ee_rate
2411 ,p_er_rate => l_er_rate
2412 ,p_ee_thrhld_rate => l_ee_thrhld_rate /* For bug 6828199 */
2413 ,p_er_thrhld_rate => l_er_thrhld_rate /* For bug 6828199 */
2414 ,p_ee_rounding_method => l_ee_rounding_method
2415 ,p_er_rounding_method => l_er_rounding_method
2416 );
2417
2418 IF l_message = 'SUCCESS' THEN
2419 --
2420 IF l_ee_rate_type = 'PERCENTAGE' THEN
2421 --
2422 l_return_value := l_ee_rate;
2423 hr_utility.set_location('China : Employee Cont Percent -> '||l_return_value, 20);
2424 RETURN l_return_value;
2425 --
2426 END IF;
2427 --
2428 ELSE
2429 --
2430 RETURN l_return_value;
2431 --
2432 END IF;
2433 --
2434 END IF;
2435
2436 -- If info Type required is Ethnic Group
2437 --
2438 IF p_info_type = 'ETHNIC_GROUP' THEN
2439 --
2440 OPEN csr_ethnic_group;
2441 FETCH csr_ethnic_group
2442 INTO l_return_value;
2443 CLOSE csr_ethnic_group;
2444 hr_utility.set_location('China : Ethnic Group -> '||l_return_value, 30);
2445 RETURN l_return_value;
2446 --
2447 END IF;
2448
2449 -- If info Type required is Hukou Type
2450 --
2451 IF p_info_type = 'HUKOU_TYPE' THEN
2452 --
2453 OPEN csr_hukou_type;
2454 FETCH csr_hukou_type
2455 INTO l_return_value;
2456 CLOSE csr_hukou_type;
2457 hr_utility.set_location('China : Hukou Type -> '||l_return_value, 40);
2458 RETURN l_return_value;
2459 --
2460 END IF;
2461
2462 -- If info Type required is Work Life Start Date
2463 --
2464 IF p_info_type = 'WORK_LIFE_START_DATE' THEN
2465 --
2466 OPEN csr_work_life_start_date;
2467 FETCH csr_work_life_start_date
2468 INTO l_work_life_date;
2469 IF csr_work_life_start_date%NOTFOUND THEN
2470 --
2471 CLOSE csr_work_life_start_date;
2472 RETURN l_return_value;
2473 --
2474 END IF;
2475 CLOSE csr_work_life_start_date;
2476 l_return_value := TO_CHAR(TO_DATE(l_work_life_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD');
2477 hr_utility.set_location('China : Work Life Start Date -> '||l_return_value, 50);
2478 RETURN l_return_value;
2479 --
2480 END IF;
2481
2482 -- If info Type required is Job Category
2483 --
2484 IF p_info_type = 'JOB_CATEGORY' THEN
2485 --
2486 OPEN csr_job_category;
2487 FETCH csr_job_category
2488 INTO l_return_value;
2489 IF csr_job_category%NOTFOUND THEN
2490 --
2491 CLOSE csr_job_category;
2492 RETURN l_return_value;
2493 --
2494 END IF;
2495 CLOSE csr_job_category;
2496 hr_utility.set_location('China : Job Category -> '||l_return_value, 50);
2497 RETURN l_return_value;
2498 --
2499 END IF;
2500 --
2501
2502 -- No Info Type Matches
2503 --
2504
2505 hr_utility.set_location('China Leaving -> '|| l_proc_name , 60);
2506 RETURN l_return_value;
2507 --
2508 EXCEPTION
2509 --
2510 WHEN OTHERS THEN
2511 IF csr_ethnic_group%ISOPEN THEN
2512 --
2513 CLOSE csr_ethnic_group;
2514 --
2515 END IF;
2516
2517 IF csr_hukou_type%ISOPEN THEN
2518 --
2519 CLOSE csr_hukou_type;
2520 --
2521 END IF;
2522 IF csr_work_life_start_date%ISOPEN THEN
2523 --
2524 CLOSE csr_work_life_start_date;
2525 --
2526 END IF;
2527
2531 --
2528 IF csr_job_category%ISOPEN THEN
2529 --
2530 CLOSE csr_job_category;
2532 END IF;
2533
2534 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 80);
2535 RAISE;
2536 --
2537 END get_employee_info;
2538
2539
2540 ----------------------------------------------------------------------------
2541 -- --
2542 -- Name : GET_BALANCE_VALUE --
2543 -- Type : FUNCTION --
2544 -- Access : Public --
2545 -- Description : Function to set the Balance value of a given Balance --
2546 -- and Balance Dimension --
2547 -- This function returns --
2548 -- o Previous month value if Info Type is PREV_MONTH --
2549 -- o Current month value if Info Type is CURR_MONTH --
2550 -- o Prev Years average value of the defined balance --
2551 -- --
2552 -- Parameters : --
2553 -- IN : p_assignment_id NUMBER --
2554 -- p_business_group_id NUMBER --
2555 -- p_balance_name VARCHAR2 --
2556 -- p_balance_dimension VARCHAR2 --
2557 -- p_info_type VARCHAR2 --
2558 -- Change History : --
2559 ----------------------------------------------------------------------------
2560 -- Rev# Date Userid Description --
2561 ----------------------------------------------------------------------------
2562 -- 1.0 10-Jan-2004 bramajey Created this function --
2563 -- 1.1 03-Feb-2004 saikrish Simplified code(Bug# 3411273) --
2564 -- 1.2 01-Mar-2004 sshankar Modified Return l_bal_value to --
2565 -- Return round(l_bal_valu2,2). Bug 3475437 --
2566 -- 1.3 31-May-2004 snekkala Changed cursor csr_assg_act(Bug# 3603564)--
2567 ----------------------------------------------------------------------------
2568 FUNCTION get_balance_value( p_assignment_id IN NUMBER
2569 , p_business_group_id IN NUMBER
2570 , p_balance_name IN VARCHAR2
2571 , p_balance_dimension IN VARCHAR2
2572 , p_info_type IN VARCHAR2
2573 )
2574 RETURN NUMBER
2575 IS
2576 --
2577
2578 l_bal_value NUMBER;
2579 l_proc_name VARCHAR2(150);
2580 l_date_earned DATE;
2581 l_prev_month DATE;
2582 l_no_of_runs NUMBER;
2583 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2584 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
2585
2586 l_phf_si_type VARCHAR2(50);
2587 l_start_date DATE;
2588 l_end_date DATE;
2589 l_legal_employer_id NUMBER;
2590 l_business_group_id NUMBER;
2591 l_contribution_area VARCHAR2(30);
2592 l_contribution_year VARCHAR2(30);
2593 l_filling_date DATE;
2594 l_report_type VARCHAR2(3);
2595
2596 l_mod_start_date DATE;
2597 l_mod_end_date DATE;
2598
2599 -- Cursor to fetch defined Balance ID
2600 --
2601 CURSOR csr_defined_balance (p_balance_name IN VARCHAR2, p_balance_dimension IN VARCHAR2)
2602 IS
2603 --
2604 SELECT defined.defined_balance_id
2605 FROM pay_balance_types bal
2606 , pay_balance_dimensions dim
2607 , pay_defined_balances defined
2608 WHERE bal.legislation_code = 'CN'
2609 AND bal.balance_name = p_balance_name
2610 AND dim.legislation_code = 'CN'
2611 AND dim.dimension_name = p_balance_dimension
2612 AND bal.balance_type_id = defined.balance_type_id
2613 AND dim.balance_dimension_id = defined.balance_dimension_id;
2614 --
2615
2616 --
2617 -- Bug 3603564 changes start
2618 --
2619 -- Cursor to fetch Assignment Action ID
2620 --
2621 CURSOR csr_assg_act(p_start_date IN DATE
2622 ,p_end_date IN DATE)
2623 IS
2624 --
2625 SELECT max(paa.assignment_action_id)
2626 FROM pay_assignment_actions paa
2627 , pay_payroll_actions ppa
2628 , per_all_assignments_f paf
2629 WHERE paa.payroll_action_id = ppa.payroll_action_id
2630 AND paf.assignment_id = p_assignment_id
2631 AND paf.assignment_id = paa.assignment_id
2632 AND paa.action_status = 'C'
2633 AND ppa.action_status = 'C'
2634 AND ppa.action_type IN ('R','Q')
2635 AND ppa.effective_date BETWEEN p_start_date
2636 AND p_end_date
2637 AND ppa.effective_date BETWEEN paf.effective_start_date
2638 AND paf.effective_end_date
2642 -- Bug 3603564 changes end
2639 AND ppa.business_group_id = p_business_group_id;
2640
2641 --
2643 --
2644 -- Cursor to fetch Date Earned of the given assignment action id
2645 --
2646 CURSOR csr_date_earned(p_assg_act_id IN NUMBER)
2647 IS
2648 --
2649 SELECT ppa.date_earned
2650 FROM pay_assignment_actions paa
2651 , pay_payroll_actions ppa
2652 WHERE paa.assignment_action_id = p_assg_act_id
2653 AND paa.payroll_action_id = ppa.payroll_action_id;
2654
2655 --
2656 BEGIN
2657 --
2658 l_proc_name := 'pay_cn_ext.get_balance_value';
2659 l_bal_value :=0;
2660
2661 hr_utility.set_location('China Entering -> ' || l_proc_name , 10);
2662 hr_utility.set_location('China p_assignment_id -> ' || p_assignment_id , 10);
2663 hr_utility.set_location('China p_business_group_id -> ' || p_business_group_id , 10);
2664 hr_utility.set_location('China p_balance_name -> ' || p_balance_name , 10);
2665 hr_utility.set_location('China p_balance_dimension -> ' || p_balance_dimension , 10);
2666 hr_utility.set_location('China p_info_type -> ' || p_info_type , 10);
2667
2668 -- Get Globals
2669 --
2670 get_globals ( p_phf_si_type => l_phf_si_type
2671 , p_start_date => l_start_date
2672 , p_end_date => l_end_date
2673 , p_legal_employer_id => l_legal_employer_id
2674 , p_business_group_id => l_business_group_id
2675 , p_contribution_area => l_contribution_area
2676 , p_contribution_year => l_contribution_year
2677 , p_filling_date => l_filling_date
2678 , p_report_type => l_report_type
2679 );
2680
2681 -- Fetch the Defined balance id
2682 --
2683 OPEN csr_defined_balance(p_balance_name,p_balance_dimension);
2684 FETCH csr_defined_balance
2685 INTO l_defined_balance_id;
2686 IF csr_defined_balance%NOTFOUND THEN
2687 --
2688 hr_utility.set_location('China : Defined Balance not found', 20);
2689 CLOSE csr_defined_balance;
2690 RETURN l_bal_value;
2691 --
2692 END IF;
2693 CLOSE csr_defined_balance;
2694
2695 hr_utility.set_location('China l_defined_balance_id -> ' || l_defined_balance_id , 30);
2696
2697 IF p_info_type = 'PREV_MONTH' THEN
2698 l_mod_start_date := TRUNC(TRUNC(l_start_date,'MM')-1,'MM');
2699 l_mod_end_date := LAST_DAY(l_mod_start_date);
2700
2701 ELSIF p_info_type = 'CURR_MONTH' THEN
2702 l_mod_start_date := l_start_date;
2703 l_mod_end_date := LAST_DAY(l_mod_start_date);
2704
2705 END IF;
2706
2707 OPEN csr_assg_act(l_mod_start_date,l_mod_end_date);
2708 FETCH csr_assg_act INTO l_assignment_action_id;
2709
2710 IF csr_assg_act%FOUND THEN
2711 hr_utility.set_location('China : Assignment action id -> ' || l_assignment_action_id, 50);
2712
2713 OPEN csr_date_earned(l_assignment_action_id);
2714 FETCH csr_date_earned INTO l_date_earned;
2715 CLOSE csr_date_earned;
2716
2717 hr_utility.set_location('China : Date Earned ->' || l_date_earned, 60);
2718
2719 -- Set context
2720 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(l_date_earned));
2721
2722 -- Get the value
2723 l_bal_value := pay_balance_pkg.get_value ( p_defined_balance_id => l_defined_balance_id
2724 , p_assignment_action_id => l_assignment_action_id);
2725
2726 ELSE
2727 hr_utility.set_location('China : Assignment action id -> ' || l_assignment_action_id, 55);
2728 END IF;
2729
2730 CLOSE csr_assg_act;
2731
2732
2733
2734 -- If the Info Type is Previous Year
2735 --
2736 IF p_info_type = 'PREV_YEAR' THEN
2737 --
2738 hr_utility.set_location('China : Fetch Assignment action id ', 100);
2739
2740 -- Fetch Assignment Action ID of previous month
2741 --
2742 l_prev_month := TRUNC(TRUNC(l_start_date,'MM')-1,'MM');
2743 OPEN csr_assg_act(l_prev_month
2744 ,LAST_DAY(l_prev_month));
2745 FETCH csr_assg_act
2746 INTO l_assignment_action_id;
2747
2748 IF csr_assg_act%FOUND THEN
2749 --
2750 hr_utility.set_location('China : Assignment action id -> ' || l_assignment_action_id, 110);
2751
2752 -- Get Date Earned context
2753 --
2754 OPEN csr_date_earned(l_assignment_action_id);
2755 FETCH csr_date_earned
2756 INTO l_date_earned;
2757 CLOSE csr_date_earned;
2758
2759 hr_utility.set_location('China : Date Earned ->', 60);
2760
2761 -- Set context
2762 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(l_date_earned));
2763
2764 -- Get the value
2765 l_bal_value := pay_balance_pkg.get_value ( p_defined_balance_id => l_defined_balance_id
2766 , p_assignment_action_id => l_assignment_action_id);
2767
2768 -- Set Assignment ID Context
2772 --
2769 pay_balance_pkg.set_context('ASSIGNMENT_ID',p_assignment_id);
2770
2771 -- Fetch Number of runs in Previous Year
2773 l_no_of_runs := pay_balance_pkg.run_db_item('CN_PAYROLL_RUN_MONTHS_PREV_YEAR',p_business_group_id,'CN');
2774 hr_utility.set_location('China : Balance Prev Year ->' || l_bal_value, 120);
2775 hr_utility.set_location('China : Number of runs ->' || l_no_of_runs, 120);
2776 l_bal_value := l_bal_value/l_no_of_runs;
2777 --
2778 ELSE
2779 --
2780 hr_utility.set_location('China : Assignment action id not Found', 110);
2781 --
2782 END IF;
2783 CLOSE csr_assg_act;
2784 --
2785 END IF;
2786
2787 hr_utility.set_location('China l_bal_value -> ' || l_bal_value , 130);
2788 hr_utility.set_location('China Leaving -> '|| l_proc_name , 130);
2789
2790 --
2791 -- Bug 3475437
2792 -- Modified Return l_bal_value to Return round(l_bal_valu2,2)
2793 -- to restrict balance value to be displayed upto 2 decimal places only.
2794 --
2795 RETURN round(l_bal_value,2);
2796
2797 EXCEPTION
2798 WHEN OTHERS THEN
2799 IF csr_defined_balance%ISOPEN THEN
2800 CLOSE csr_defined_balance;
2801 END IF;
2802 IF csr_assg_act%ISOPEN THEN
2803 CLOSE csr_assg_act;
2804 END IF;
2805 IF csr_date_earned%ISOPEN THEN
2806 CLOSE csr_date_earned;
2807 END IF;
2808
2809 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 140);
2810 RAISE;
2811
2812 END get_balance_value;
2813
2814
2815 ----------------------------------------------------------------------------
2816 -- --
2817 -- Name : GET_ELEMENT_ENTRY --
2818 -- Type : FUNCTION --
2819 -- Access : Public --
2820 -- Description : Function to check whether an assignment has element --
2821 -- entries for the given PHF/SI Type --
2822 -- --
2823 -- Parameters : --
2824 -- IN : p_assignment_id NUMBER --
2825 -- p_business_group_id NUMBER --
2826 -- p_effective_date DATE --
2827 -- p_phf_si_type VARCHAR2 --
2828 -- Change History : --
2829 ----------------------------------------------------------------------------
2830 -- Rev# Date Userid Description --
2831 ----------------------------------------------------------------------------
2832 -- 1.0 10-Jan-2004 bramajey Created this function --
2833 -- 1.1 03-Feb-2004 saikrish Added p_start_date,p_end_date, --
2834 -- p_phf_si_type (Bug# 3411273) --
2835 ----------------------------------------------------------------------------
2836 FUNCTION get_element_entry ( p_assignment_id IN NUMBER
2837 , p_business_group_id IN NUMBER
2838 , p_effective_date IN DATE
2839 , p_phf_si_type IN VARCHAR2
2840 )
2841 RETURN VARCHAR2
2842 IS
2843 --
2844
2845 l_return_value CHAR(1);
2846 l_count NUMBER;
2847 l_element_name pay_element_types_f.element_name%TYPE;
2848 l_proc_name VARCHAR2(150);
2849
2850 -- Cursor to fetch count of element entry
2851 -- Bug 3415164
2852 -- Using p_effective_date instead of p_start_date
2853 CURSOR csr_element_entry(p_end_date IN DATE
2854 ,p_element_name IN VARCHAR2)
2855 IS
2856 --
2857 SELECT COUNT(*)
2858 FROM pay_element_entries_f pee
2859 ,pay_element_links_f pel
2860 ,pay_element_types_f pet
2861 ,per_all_assignments_f paa
2862 WHERE paa.assignment_id = p_assignment_id
2863 AND paa.business_group_id = p_business_group_id
2864 AND p_effective_date BETWEEN paa.effective_start_date
2865 AND paa.effective_end_date
2866 AND pee.assignment_id = paa.assignment_id
2867 AND p_effective_date BETWEEN pee.effective_start_date
2868 AND pee.effective_end_date
2869 AND pee.element_link_id = pel.element_link_id
2870 AND p_effective_date BETWEEN pel.effective_start_date
2871 AND pel.effective_end_date
2872 AND pel.element_type_id = pet.element_type_id
2873 AND pet.element_name = p_element_name
2874 AND p_effective_date BETWEEN pet.effective_start_date
2875 AND pet.effective_end_date;
2876
2877 --
2878 BEGIN
2879 --
2880 l_proc_name := 'pay_cn_ext.get_element_entry';
2881 l_return_value := 'N';
2882
2883 hr_utility.set_location('China : Entering -> ' || l_proc_name , 10);
2887 hr_utility.set_location('China : p_phf_si_type -> ' || p_phf_si_type , 10);
2884 hr_utility.set_location('China : p_assignment_id -> ' || p_assignment_id , 10);
2885 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 10);
2886 hr_utility.set_location('China : p_effective_date -> ' || p_effective_date , 10);
2888
2889
2890 -- Get Element Name
2891 l_element_name := get_element_name(p_phf_si_type);
2892
2893 hr_utility.set_location('China l_element_name -> '|| l_element_name , 20);
2894
2895 -- Fetch the count of entries
2896 -- Bug 3415164
2897 -- Passing only p_end_date and element_name
2898 --
2899 OPEN csr_element_entry(p_effective_date,l_element_name);
2900 FETCH csr_element_entry INTO l_count;
2901 CLOSE csr_element_entry;
2902
2903 -- If the count is greater than 0 then the assignment has element entries
2904 -- for that PHF/SI Type
2905 --
2906 IF l_count > 0 THEN
2907 --
2908 l_return_value := 'Y';
2909 --
2910 ELSE
2911 --
2912 -- IF not return N
2913 l_return_value := 'N';
2914 --
2915 END IF;
2916
2917 hr_utility.set_location('China l_return_value -> '|| l_return_value , 30);
2918 hr_utility.set_location('China Leaving -> '|| l_proc_name , 40);
2919
2920 RETURN l_return_value;
2921 --
2922 EXCEPTION
2923 --
2924 WHEN OTHERS THEN
2925 IF csr_element_entry%ISOPEN THEN
2926 --
2927 CLOSE csr_element_entry;
2928 --
2929 END IF;
2930
2931 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 50);
2932 RAISE;
2933 --
2934 END get_element_entry;
2935
2936 ----------------------------------------------------------------------------
2937 -- --
2938 -- Name : GET_OVERRIDE_SIC_CODE --
2939 -- Type : FUNCTION --
2940 -- Access : Privatre --
2941 -- Description : Function to check whether an assignment has Override --
2942 -- SIC code for the given PHF/SI Type --
2943 -- --
2944 -- Parameters : --
2945 -- IN : p_element_name IN VARCHAR2 --
2946 -- p_assignment_id IN NUMBER --
2947 -- p_date_earned IN DATE --
2948 -- Change History : --
2949 ----------------------------------------------------------------------------
2950 -- Rev# Date Userid Description --
2951 ----------------------------------------------------------------------------
2952 -- 1.0 03-Feb-2004 saikrish Created this function(Bug# 3411840) --
2953 -- 1.1 04-Feb-2004 saikrish Corrected return value --
2954 -- 1.2 05-Feb-2004 saikrish Removed p_business_group_id, cursor modified
2955 ----------------------------------------------------------------------------
2956 FUNCTION get_override_sic_code ( p_element_name IN VARCHAR2
2957 , p_assignment_id IN NUMBER
2958 , p_date_earned IN DATE
2959 )
2960 RETURN VARCHAR2
2961 IS
2962 --
2963
2964 l_return_value VARCHAR2(5);
2965 l_proc_name VARCHAR2(150);
2966
2967 CURSOR csr_override_sic_code ( p_element_name IN VARCHAR2
2968 , p_assignment_id IN NUMBER
2969 , p_date_earned IN DATE
2970 ) IS
2971 SELECT target.ENTRY_INFORMATION1
2972 FROM per_all_assignments_f assign
2973 ,pay_element_entries_f target
2974 ,pay_element_links_f link
2975 ,pay_element_types_f type
2976 WHERE assign.assignment_id = p_assignment_id
2977 AND target.assignment_id = assign.assignment_id
2978 AND target.entry_information_category = 'CN_PHF AND SI INFORMATION'
2979 AND target.element_link_id = link.element_link_id
2980 AND link.element_type_id = type.element_type_id
2981 AND type.element_name = p_element_name
2982 AND p_date_earned BETWEEN assign.effective_start_date
2983 AND assign.effective_end_date
2984 AND p_date_earned BETWEEN target.effective_start_date
2985 AND target.effective_end_date
2986 AND p_date_earned BETWEEN link.effective_start_date
2987 AND link.effective_end_date
2988 AND p_date_earned BETWEEN type.effective_start_date
2989 AND type.effective_end_date;
2990
2991
2992 l_entry_information1 pay_element_entries_f.entry_information1%TYPE;
2993
2994 --
2995 BEGIN
2996 --
2997 l_proc_name := 'pay_cn_ext.get_override_sic_code';
2998
2999 hr_utility.set_location('China : Entering -> ' || l_proc_name , 10);
3000 hr_utility.set_location('China : p_element_name -> ' || p_element_name , 10);
3001 hr_utility.set_location('China : p_assignment_id -> ' || p_assignment_id , 10);
3002 hr_utility.set_location('China : p_date_earned -> ' || p_date_earned , 10);
3003
3004 -- Fetch the Override SIC Code
3008 );
3005 OPEN csr_override_sic_code( p_element_name
3006 , p_assignment_id
3007 , p_date_earned
3009 FETCH csr_override_sic_code INTO l_entry_information1;
3010 IF csr_override_sic_code%FOUND AND l_entry_information1 IS NOT NULL THEN
3011 l_return_value := l_entry_information1;
3012 ELSE
3013 l_return_value := NULL;
3014 END IF;
3015
3016 CLOSE csr_override_sic_code;
3017
3018 hr_utility.set_location('China : l_return_value -> '|| NVL(l_return_value,'NULL') , 30);
3019 hr_utility.set_location('China : Leaving -> '|| l_proc_name , 40);
3020
3021 RETURN l_return_value;
3022
3023 EXCEPTION
3024 WHEN OTHERS THEN
3025 IF csr_override_sic_code%ISOPEN THEN
3026 CLOSE csr_override_sic_code;
3027 END IF;
3028
3029 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 50);
3030 RAISE;
3031
3032 END get_override_sic_code;
3033
3034 ----------------------------------------------------------------------------
3035 -- --
3036 -- Name : GET_ASSIGNMENT_ACTION --
3037 -- Type : FUNCTION --
3038 -- Access : Private --
3039 -- Description : Function to check whether an assignment has assignment--
3040 -- action id for the given period --
3041 -- --
3042 -- Parameters : --
3043 -- IN : p_assignment_id IN NUMBER --
3044 -- p_business_group_id IN NUMBER --
3045 -- p_start_date IN DATE --
3046 -- p_end_date IN DATE --
3047 -- Change History : --
3048 ----------------------------------------------------------------------------
3049 -- Rev# Date Userid Description --
3050 ----------------------------------------------------------------------------
3051 -- 1.0 03-Feb-2004 saikrish Created this function(Bug# 3411273) --
3052 -- 1.1 31-May-2004 snekkala Changed cursor csr_assg_act(Bug# 3603564)--
3053 ----------------------------------------------------------------------------
3054 FUNCTION get_assignment_action ( p_assignment_id IN NUMBER
3055 , p_business_group_id IN NUMBER
3056 , p_start_date IN DATE
3057 , p_end_date IN DATE
3058 )
3059 RETURN VARCHAR2
3060 IS
3061 --
3062
3063 l_return_value CHAR(1);
3064 l_proc_name VARCHAR2(150);
3065
3066 --
3067 -- Bug 3603564 changes start
3068 --
3069 CURSOR csr_assg_act(p_start_date IN DATE
3070 ,p_end_date IN DATE)
3071 IS
3072 SELECT max(paa.assignment_action_id)
3073 FROM pay_assignment_actions paa
3074 , pay_payroll_actions ppa
3075 , per_all_assignments_f paf
3076 WHERE paa.payroll_action_id = ppa.payroll_action_id
3077 AND paf.assignment_id = p_assignment_id
3078 AND paf.assignment_id = paa.assignment_id
3079 AND paa.action_status = 'C'
3080 AND ppa.action_status = 'C'
3081 AND ppa.action_type IN ('R','Q')
3082 AND ppa.effective_date BETWEEN p_start_date
3083 AND p_end_date
3084 AND ppa.effective_date BETWEEN paf.effective_start_date
3085 AND paf.effective_end_date
3086 AND ppa.business_group_id = p_business_group_id;
3087 --
3088 -- Bug 3603564 changes end
3089 --
3090 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
3091
3092 BEGIN
3093
3094 l_proc_name := 'pay_cn_ext.get_assignment_action';
3095 l_return_value := 'N';
3096
3097 hr_utility.set_location('China : Entering -> ' || l_proc_name , 10);
3098 hr_utility.set_location('China : p_assignment_id -> ' || p_assignment_id , 10);
3099 hr_utility.set_location('China : p_business_group_id -> ' || p_business_group_id , 10);
3100
3101 -- Fetch the assignment action
3102 OPEN csr_assg_act(p_start_date,p_end_date);
3103 FETCH csr_assg_act INTO l_assignment_action_id;
3104 CLOSE csr_assg_act;
3105
3106 hr_utility.set_location('China : l_assignment_action_id -> ' || l_assignment_action_id , 20);
3107
3108 --In case the assignment actions don't exist, return N
3109 IF NVL(l_assignment_action_id,0) = 0 THEN
3110 l_return_value := 'N';
3111 ELSE
3112 l_return_value := 'Y';
3113 END IF;
3114
3115 hr_utility.set_location('China l_return_value -> '|| l_return_value , 30);
3116 hr_utility.set_location('China Leaving -> '|| l_proc_name , 40);
3117
3118 RETURN l_return_value;
3119
3120 EXCEPTION
3121 WHEN OTHERS THEN
3122 IF csr_assg_act%ISOPEN THEN
3123 CLOSE csr_assg_act;
3124 END IF;
3125
3126 hr_utility.set_location('China : Exception, Leaving: '||l_proc_name, 50);
3127 RAISE;
3128
3129 END get_assignment_action;
3130
3131
3132 --
3133 END pay_cn_ext;