PACKAGE: APPS.PAY_FI_TAX_CARD_PKG

Source

1 package PAY_FI_TAX_CARD_PKG AUTHID CURRENT_USER as
2 /* $Header: pyfitaxr.pkh 120.1 2005/12/13 21:35:34 vetsrini noship $ */
3 
4 
5 level_cnt NUMBER;
6 
7 FUNCTION  get_parameter (
8           p_parameter_string  in varchar2
9          ,p_token             in varchar2
10          ,p_segment_number    in number default null) RETURN varchar2;
11 
12 PROCEDURE range_code(
13                 p_payroll_action_id     IN  NUMBER,
14                 p_sqlstr                OUT NOCOPY VARCHAR2);
15 
16 
17 PROCEDURE assignment_action_code(
18                           pactid    IN NUMBER,
19                           stperson  IN NUMBER,
20                           endperson IN NUMBER,
21                           chunk     IN NUMBER);
22 
23 FUNCTION get_employment_status(
24 			 p_assignment_id	IN	NUMBER
25 			,p_effective_date	IN	DATE)	RETURN	VARCHAR2;
26 
27 
28 /********************************************************
29 *   Cursor to fetch Body record information		*
30 ********************************************************/
31 
32 CURSOR CSR_FI_TAX_CARD IS
33 SELECT /*+ INDEX(scl, HR_SOFT_CODING_KEYFLEX_PK) */	 'EMPLOYERS_ID=P'
34        ,hoi2.org_information1 || '-'|| hoi5.org_information1
35        ,'EMPLOYEES_ID=P'
36        ,pap.national_identifier
37        ,'PREPAYMENT_YEAR=P'
38        ,pay_fi_tax_card_pkg.get_parameter(ppa.legislative_parameters, 'PREPAYMENT_YEAR')
39        ,'TYVI_ID=P'
40        ,pay_fi_tax_card_pkg.get_parameter(ppa.legislative_parameters,'TYVI_ID')
41        ,'SALARIED_EMP=P'      ,DECODE(paa.hourly_salaried_code,NULL,DECODE(hoi2.org_information6,NULL,hoi5.org_information9,hoi2.org_information6 ) , paa.hourly_salaried_code)
42        ,'PRIMARY_EMPLOYMENT=P'
43        ,pay_fi_tax_card_pkg.get_employment_status(paa.assignment_id,ppa.effective_date)
44 FROM          PAY_PAYROLL_ACTIONS ppa
45       , PER_ALL_PEOPLE_F pap
46       , PER_ALL_ASSIGNMENTS_F paa
47       , PAY_LEGISLATION_RULES plr
48       , HR_SOFT_CODING_KEYFLEX scl
49       , HR_ORGANIZATION_INFORMATION hoi1
50       , HR_ORGANIZATION_INFORMATION hoi2
51       , HR_ORGANIZATION_INFORMATION hoi3
52       , HR_ORGANIZATION_INFORMATION hoi4
53       , HR_ORGANIZATION_INFORMATION hoi5
54 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
55 and pap.person_id = paa.person_id
56 and pap.BUSINESS_GROUP_ID = ppa.BUSINESS_GROUP_ID
57 and ppa.effective_date BETWEEN pap.effective_start_date and pap.effective_end_date
58 and paa.BUSINESS_GROUP_ID = ppa.BUSINESS_GROUP_ID
59 and ppa.effective_date BETWEEN paa.effective_start_date and paa.effective_end_date
60 and paa.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
61 and scl.id_flex_num = plr.rule_mode
62 and scl.enabled_flag='Y'
63 and plr.legislation_code = 'FI'
64 and plr.rule_type = 'S'
65 and (paa.assignment_id,pap.person_id )  in (SELECT  /*+ INDEX(scl1, HR_SOFT_CODING_KEYFLEX_PK) */ min(paa.assignment_id),pap1.person_id
66 	         FROM per_all_assignments_f paa
67 		      ,per_all_people_f pap1
68 		      ,HR_SOFT_CODING_KEYFLEX scl1
69 		      ,HR_ORGANIZATION_INFORMATION hoi6
70 		 WHERE paa.person_id = pap1.person_id
71 and ppa.BUSINESS_GROUP_ID = pap1.BUSINESS_GROUP_ID
72 and ppa.effective_date BETWEEN pap1.effective_start_date and pap1.effective_end_date
73 and ppa.effective_date BETWEEN paa.effective_start_date and paa.effective_end_date
74 and ppa.BUSINESS_GROUP_ID = paa.BUSINESS_GROUP_ID
75                  	AND paa.soft_coding_keyflex_id= scl1.soft_coding_keyflex_id
76 	AND scl1.segment2 = nvl(pay_fi_tax_card_pkg.get_parameter(ppa.legislative_parameters,'LOCAL_UNIT'),scl1.segment2)
77 	and scl1.enabled_flag='Y'
78 		AND hoi6.organization_id =pay_fi_tax_card_pkg.get_parameter(ppa.legislative_parameters, 'LEGAL_EMPLOYER')
79 		AND hoi6.ORG_INFORMATION_CONTEXT = 'FI_LOCAL_UNITS'
80 		        AND  hoi6.org_information1 = scl1.segment2
81 			group by pap1.person_id
82 			)
83 and hoi1.organization_id = pay_fi_tax_card_pkg.get_parameter(ppa.legislative_parameters, 'LEGAL_EMPLOYER')
84 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
85 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
86 and hoi1.organization_id =  hoi2.organization_id
87 and hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS'
88 and hoi2.organization_id = hoi3.organization_id
89 and hoi3.ORG_INFORMATION_CONTEXT = 'FI_LOCAL_UNITS'
90 and hoi3.org_information1= hoi4.organization_id
91 and hoi4.ORG_INFORMATION_CONTEXT='CLASS'
92 and hoi4.org_information1='FI_LOCAL_UNIT'
93 and hoi4.organization_id = nvl(pay_fi_tax_card_pkg.get_parameter(ppa.legislative_parameters,'LOCAL_UNIT'),hoi4.organization_id)
94 and hoi4.organization_id = hoi5.organization_id
95 and hoi5.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS'
96 and to_char(hoi5.organization_id) = scl.segment2;
97 
98 END PAY_FI_TAX_CARD_PKG;