[Home] [Help]
PACKAGE: APPS.PAY_FI_TAX_CARD_PKG
Source
1 package PAY_FI_TAX_CARD_PKG 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;