1 package body PQH_DE_LOCAL_COST_LIVING_PKG as
2 /* $Header: pqhdeloc.pkb 120.0 2005/05/29 02:03:04 appldev noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Local cost of living Package
13 Purpose
14 This package is used to calculate the amount for the Local cost of Living
15 for german Public Sector.
16
17 History
18 Version Date Who What?
19 --------- --------- ---------- --------------------------------------------
20 115.0 17-MAR-02 nsinghal Created
21 */
22 --
23 --
24 Function local_cost_of_living
25 (p_effective_date in date
26 ,p_business_group_id in number
27 ,p_ASSIGNMENT_ID in number
28 ,p_pay_grade in varchar2
29 ,p_Tariff_contract in varchar2
30 ,p_tariff_group in varchar2
31 )
32
33 Return number Is
34
35
36 -- cursor to get the Person_id for teh given Assignment_id
37
38 cursor c_person_id (p_ASSIGNMENT_ID number,p_effective_date date) is
39 select person_id from PER_ALL_ASSIGNMENTS_F
40 where ASSIGNMENT_ID = p_ASSIGNMENT_ID
41 and p_effective_date between EFFECTIVE_START_DATE and nvl(EFFECTIVE_end_DATE,p_effective_date );
42
43
44 -- cursor to get the alimony amount of the Employee
45
46 Cursor c_alimony_amount (P_person_id number,p_business_group_id number,p_effective_date date) is
47 select to_number(nvl(pcr.CONT_INFORMATION5,0))
48 from per_contact_relationships pcr,
49 per_all_people_f paf
50 where paf.PERSON_ID = p_PERSON_ID
51 and pcr.person_id = paf.person_id
52 and pcr.CONT_INFORMATION1 = 'N'
53 and pcr.CONT_INFORMATION4 = 'Y'
54 and pcr.CONT_INFORMATION6 is NULL
55 and pcr.CONT_information_category ='DE_PQH'
56 and pcr.contact_type = 'S'
57 and paf.business_group_id = p_business_group_id
58 and pcr.business_group_id = p_business_group_id
59 and p_effective_date between paf.EFFECTIVE_START_DATE and nvl( paf.EFFECTIVE_end_DATE,p_effective_date ) ;
60
61 -- cursor to get the sequence number of the child of the Employee
62
63 Cursor c_seq_number (P_person_id number,p_business_group_id number,p_effective_date date) is
64 select to_number(nvl(pcr.CONT_INFORMATION6,0)) count1
65 from per_contact_relationships pcr,
66 per_all_people_f paf
67 where paf.PERSON_ID = p_PERSON_ID
68 and pcr.person_id = paf.person_id
69 and pcr.CONT_INFORMATION1 = 'N'
70 and pcr.CONT_INFORMATION4 = 'Y'
71 and pcr.CONT_INFORMATION6 is not NULL
72 and pcr.CONT_information_category ='DE_PQH'
73 and pcr.contact_type in ('A','O','OC','T')
74 and paf.business_group_id = p_business_group_id
75 and pcr.business_group_id = p_business_group_id
76 and p_effective_date between paf.EFFECTIVE_START_DATE and nvl( paf.EFFECTIVE_end_DATE ,p_effective_date )
77 ORDER BY COUNT1 ASC;
78
79 -- cursor to get the Marital status of the Employee
80
81 Cursor c_marital_status (P_person_id number,p_business_group_id number ,p_effective_date date) is
82 select NVL(marital_status,'S') from per_all_people_f
83 where PERSON_ID = p_PERSON_ID
84 and business_group_id = p_business_group_id
85 AND p_effective_date between EFFECTIVE_START_DATE and nvl( EFFECTIVE_end_DATE,p_effective_date) ;
86
87 -- cursor to get the grade_id of the required Pay grade of the Employee
88
89 Cursor c_grade_id (p_pay_grade varchar2 ,p_business_group_id number,p_effective_date date) is
90 select grade_id from per_grades
91 where name = p_pay_grade
92 and business_group_id = p_business_group_id
93 and p_effective_date between DATE_from and nvl(DATE_to,p_effective_date ) ;
94
95 -- cursor to get the tariff_class information having the amount to be given based on the Children amd the marital
96 -- status of the Employee
97
98 Cursor c_tariff_class ( l_grade_id number ,p_tariff_group varchar2,p_effective_date date) is
99 select
100 pghn.information1, -- Additional amount First Child
101 pghn.information2, -- Additional amount Second Child
102 pghn.information3, -- Additional amount Further Child
103 pghn.information4, -- Amount if Single
104 pghn.information5, -- Amount if Married
105 pghn.information6 -- Amount if Married and a child
106 from per_gen_hierarchy_nodes pghn,
107 per_gen_hierarchy_versions pghv,
108 per_gen_hierarchy_nodes pghn1,
109 per_gen_hierarchy_nodes pghn2,
110 per_gen_hierarchy pgh
111 where pgh.TYPE='LOCAL_COST_OF_LIVING'
112 and pghv.HIERARCHY_ID=pgh.HIERARCHY_ID
113 and pghn.HIERARCHY_VERSION_ID=pghv.HIERARCHY_VERSION_ID
114 and p_effective_date between pghv.date_from and nvl(pghv.date_to, p_effective_date)
115 and pghn.NODE_TYPE ='TARIFF_CLASS'
116 and pghn2.NODE_TYPE ='LCOL_PAY_GRADES'
117 and pghn1.NODE_TYPE ='TARIFF_GROUP'
118 and pghn1.entity_id = p_tariff_group
119 and pghn2.entity_id = l_grade_id
120 and pghn1.PARENT_HIERARCHY_NODE_ID = pghn.HIERARCHY_NODE_ID
121 and pghn2.PARENT_HIERARCHY_NODE_ID = pghn1.HIERARCHY_NODE_ID;
122
123 l_information1 per_gen_hierarchy_nodes.INFORMATION1%type;
124 l_information2 per_gen_hierarchy_nodes.INFORMATION2%type;
125 l_information3 per_gen_hierarchy_nodes.INFORMATION3%type;
126 l_information4 per_gen_hierarchy_nodes.INFORMATION4%type;
127 l_information5 per_gen_hierarchy_nodes.INFORMATION5%type;
128 l_information6 per_gen_hierarchy_nodes.INFORMATION6%type;
129 l_sum_amount number(15,2):=0.00;
130 total number(15,2) :=0.00;
131 n_further_count number(15) :=0;
132 l_marital_status varchar2(30);
133 l_grade_id number(15);
134 l_alimony_amount per_gen_hierarchy_nodes.INFORMATION1%type;
135
136 count_child NUMBER(15):=0;
137
138 p_person_id NUMBER(10);
139
140 g_package varchar2(30) := 'PQH_DE_LOCAL_COST_LIVING_PKG.';
141 l_proc varchar2(80) := g_package||'local_cost_of_living';
142
143
144 BEGIN
145
146
147 hr_utility.set_location('Entering '||l_proc,10);
148
149 -- Cursor to get the grade id for the given pay Grade
150
151 open c_person_id (p_ASSIGNMENT_ID ,p_effective_date );
152 fetch c_person_id into p_person_id ;
153 close c_person_id ;
154
155 hr_utility.set_location('Entering '||l_proc,20);
156
157
158 -- Cursor to get the grade id for the given pay Grade
159
160 open c_grade_id (p_pay_grade ,p_business_group_id ,p_effective_date );
161 fetch c_grade_id into l_grade_id;
162 close c_grade_id;
163
164 hr_utility.set_location('Entering '||l_proc,30);
165
166 -- Cursor to get the additional amount information for the given pay Grade, tariff group in
167 -- Hierarchy structure .
168
169 open c_tariff_class (l_grade_id ,p_tariff_group ,p_effective_date);
170 fetch c_tariff_class into
171 l_information1,l_information2,l_information3,l_information4,l_information5,l_information6;
172 close c_tariff_class;
173
174 hr_utility.set_location('Entering '||l_proc,30);
175
176 -- Loop for calculation of additional amount
177
178 For c_count in c_seq_number(P_person_id ,p_business_group_id ,p_effective_date)
179 loop
180 if c_count.count1 = 1 then
181 total := Total + to_number(nvl(l_Information1,0)) ;
182 Elsif c_count.count1 >= 2 then
183 total := Total + to_number(nvl(l_Information3,0)) ;
184 end if ;
185 count_child := count_child +1;
186 end loop;
187
188 hr_utility.set_location('Entering '||l_proc,40);
189
190 -- End Loop for calculation of additional amount
191
192 -- To get total amount of money to be paid based on number of Children.
193
194 if count_child <= 2 then
195
196 total := count_child * (to_number(nvl(l_Information6,0)) - to_number(nvl(l_Information5,0))) + Total;
197
198 else
199
200 total := 2 * (to_number(nvl(l_Information6,0)) - to_number(nvl(l_Information5,0))) + Total;
201
202 total := (count_child - 2) * (to_number(nvl(l_Information2,0)) - to_number(nvl(l_Information5,0))) + Total;
203
204 end if;
205
206 -- Cursor to get the alimony amount paid by the employee
207
208 open c_alimony_amount (P_person_id,p_business_group_id,p_effective_date);
209 fetch c_alimony_amount into l_alimony_amount;
210 close c_alimony_amount;
211
212 hr_utility.set_location('Entering '||l_proc,50);
213
214 -- Cursor to get the Marital Status of the employee
215
216 open c_marital_status (P_person_id,p_business_group_id,p_effective_date);
217 fetch c_marital_status into l_marital_status;
218 close c_marital_status;
219
220
221 hr_utility.set_location('Entering '||l_proc,60);
222
223 if l_marital_status ='M' then
224 l_sum_amount := l_sum_amount + Total + to_number(nvl(l_Information5,0)) - to_number(nvl(l_Information4,0)) ;
225 elsif l_marital_status ='S' then
226 l_sum_amount := l_sum_amount + Total ;
227 elsif l_marital_status ='D' AND (c_alimony_amount%FOUND) then
228 l_sum_amount := l_sum_amount + Total + to_number(nvl(l_Information5,0)) - to_number(nvl(l_Information4,0)) ;
229 else
230 l_sum_amount := l_sum_amount + Total ;
231 end if ;
232 l_sum_amount := l_sum_amount + to_number(nvl(l_Information4,0));
233
234 -- Return the Total amount to be paid to the Employee.
235
236 return(l_sum_amount);
237
238 END local_cost_of_living;
239
240 end PQH_DE_LOCAL_COST_LIVING_PKG;
[Home] [Help]