1 package body pay_kw_general as
2 /* $Header: pykwgenr.pkb 120.2.12010000.2 2008/09/23 12:59:02 bkeshary ship $ */
3 g_package varchar2(30);
4 l_organization_id hr_all_organization_units.organization_id%type;
5 l_person_id per_all_people_f.person_id%type;
6 l_nationality_cd hr_lookups.meaning%type;
7 l_nationality_person per_all_people_f.nationality%type;
8 ------------------------------------------------------------------------
9 -- Function LOCAL_NATIONALITY_NOT_DEFINED
10 -- This function return NOTEXISTS If the value for HR: Local Nationality
11 -- Profile has not been defined else it retuns EXISTS.
12 ------------------------------------------------------------------------
13 function local_nationality_not_defined (p_business_group_id IN number) return varchar2
14 is
15 begin
16 BEGIN
17 /* l_organization_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');*/
18 /* Added context business group id */
19 l_organization_id := p_business_group_id;
20 Select Org_Information1
21 Into l_nationality_cd
22 From HR_ORGANIZATION_INFORMATION
23 Where ORG_INFORMATION_CONTEXT = 'KW_BG_DETAILS'
24 And ORGANIZATION_ID = l_organization_id;
25 EXCEPTION
26 WHEN no_data_found Then
27 Null;
28 END;
29 if l_nationality_cd is null then
30 return 'NOTEXISTS';
31 else
32 return 'EXISTS';
33 end if;
34 /**** if FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') is null then
35 return 'NOTEXISTS';
36 else
37 return 'EXISTS';
38 end if;
39 ****/
40 end local_nationality_not_defined;
41 ------------------------------------------------------------------------
42 -- Function LOCAL_NATNATIONALITY_MATCHES
43 -- This function return NOMATCH If the value for HR: Local Nationality
44 -- Profile does not match with the person's nationality else it retuns MATCH.
45 ------------------------------------------------------------------------
46 function local_nationality_matches
47 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
48 p_date_earned IN Date)
49 return varchar2
50 is
51 begin
52 BEGIN
53 Select person_id
54 Into l_person_id
55 From PER_ALL_ASSIGNMENTS_F
56 Where ASSIGNMENT_ID = p_assignment_id
57 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
58 EXCEPTION
59 WHEN no_data_found Then
60 Null;
61 END;
62 BEGIN
63 Select Nationality
64 Into l_nationality_person
65 From PER_ALL_PEOPLE_F
66 Where PERSON_ID = l_person_id
67 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
68 EXCEPTION
69 WHEN no_data_found Then
70 Null;
71 END;
72 if l_nationality_cd = l_nationality_person then
73 return 'MATCH';
74 else
75 return 'NOMATCH';
76 end if;
77 end local_nationality_matches;
78 ------------------------------------------------------------------------
79 -- Function GET_LOCAL_NATIONALITY
80 -- This function is used to obtain a the local nationality defined at
81 -- the Business Group Level.
82 ------------------------------------------------------------------------
83 function get_local_nationality return varchar2
84 is
85 l_nationality hr_lookups.meaning%type;
86 begin
87 l_nationality := hr_general.decode_lookup('NATIONALITY',l_nationality_cd);
88 RETURN l_nationality;
89 END get_local_nationality;
90 ------------------------------------------------------------------------
91 -- Function GET_MESSAGE
92 -- This function is used to obtain a message.
93 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
94 -- If you want to set the value of a token called ELEMENT to Social Ins
95 -- the token parameter would be 'ELEMENT:Social Ins.'
96 ------------------------------------------------------------------------
97 function get_message
98 (p_product in varchar2
99 ,p_message_name in varchar2
100 ,p_token1 in varchar2 default null
101 ,p_token2 in varchar2 default null
102 ,p_token3 in varchar2 default null) return varchar2
103 is
104 l_message varchar2(2000);
105 l_token_name varchar2(20);
106 l_token_value varchar2(80);
107 l_colon_position number;
108 l_proc varchar2(72) ;
109 --
110 begin
111 g_package := 'pay_kw_general';
112 l_proc := g_package||'.get_message';
113 --
114 hr_utility.set_location('Entered '||l_proc,5);
115 hr_utility.set_location('. Message Name: '||p_message_name,40);
116 fnd_message.set_name(p_product, p_message_name);
117 if p_token1 is not null then
118 /* Obtain token 1 name and value */
119 l_colon_position := instr(p_token1,':');
120 l_token_name := substr(p_token1,1,l_colon_position-1);
121 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
122 fnd_message.set_token(l_token_name, l_token_value);
123 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
124 end if;
125 if p_token2 is not null then
126 /* Obtain token 2 name and value */
127 l_colon_position := instr(p_token2,':');
128 l_token_name := substr(p_token2,1,l_colon_position-1);
129 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
130 fnd_message.set_token(l_token_name, l_token_value);
131 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
132 end if;
133 if p_token3 is not null then
134 /* Obtain token 3 name and value */
135 l_colon_position := instr(p_token3,':');
136 l_token_name := substr(p_token3,1,l_colon_position-1);
137 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
138 fnd_message.set_token(l_token_name, l_token_value);
139 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
140 end if;
141 l_message := substr(fnd_message.get,1,254);
142 hr_utility.set_location('leaving '||l_proc,100);
143 return l_message;
144 end get_message;
145 ------------------------------------------------------------------------
146 -- Function GET_TABLE_BANDS
147 -- This function is used to obtain User table's high and low values.
148 ------------------------------------------------------------------------
149 function get_table_bands
150 (p_Date_Earned IN DATE
151 ,p_table_name in varchar2
152 ,p_return_type in varchar2) return number
153 is
154 CURSOR csr_get_user_table_id(l_table_name varchar2) IS
155 SELECT user_table_id
156 FROM pay_user_tables
157 WHERE legislation_code='KW'
158 AND UPPER(user_table_name) = UPPER(l_table_name);
159 CURSOR csr_get_min_low (l_user_table_id NUMBER, l_effective_date DATE) IS
160 SELECT MIN(fnd_number.canonical_to_number(row_low_range_or_name))
161 FROM pay_user_rows_f
162 WHERE user_table_id = l_user_table_id
163 AND legislation_code = 'KW'
164 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
165 CURSOR csr_get_min_high (l_user_table_id number ,l_effective_date DATE ) IS
166 SELECT MIN(fnd_number.canonical_to_number(row_high_range))
167 FROM pay_user_rows_f
168 WHERE user_table_id = l_user_table_id
169 AND legislation_code = 'KW'
170 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
171 CURSOR csr_get_max_high (l_user_table_id number, l_effective_date DATE) IS
172 SELECT MAX(fnd_number.canonical_to_number(row_high_range))
173 FROM pay_user_rows_f
174 WHERE user_table_id = l_user_table_id
175 AND legislation_code = 'KW'
176 AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
177 l_ret_val number(15,3);
178 l_table_id number(9);
179 l_proc varchar2(72) ;
180 --
181 begin
182 g_package := 'pay_kw_general';
183 l_proc := g_package||'.get_table_bands';
184 --
185 hr_utility.set_location('Entered '||l_proc,5);
186 -- Get the User Table ID
187 OPEN csr_get_user_table_id(p_table_name);
188 FETCH csr_get_user_table_id INTO l_table_id;
189 CLOSE csr_get_user_table_id;
190 --
191 IF p_return_type = 'MIN_LOW' THEN
192 OPEN csr_get_min_low (l_table_id,p_Date_Earned);
193 FETCH csr_get_min_low INTO l_ret_val;
194 CLOSE csr_get_min_low;
195 ELSIF p_return_type = 'MIN_HIGH' THEN
196 OPEN csr_get_min_high (l_table_id,p_Date_Earned);
197 FETCH csr_get_min_high INTO l_ret_val;
198 CLOSE csr_get_min_high;
199 ELSIF p_return_type = 'MAX_HIGH' THEN
200 OPEN csr_get_max_high (l_table_id, p_Date_Earned);
201 FETCH csr_get_max_high INTO l_ret_val;
202 CLOSE csr_get_max_high;
203 END IF;
204 return l_ret_val;
205 end get_table_bands;
206 -----------------------------------------------------------------------
207 -- Functions for EFT file
208 -----------------------------------------------------------------------
209 -----------------------------------------------------------------------------
210 -- GET_PARAMETER used in SQL to decode legislative parameters
211 -----------------------------------------------------------------------------
212 FUNCTION get_parameter(
213 p_parameter_string IN VARCHAR2
214 ,p_token IN VARCHAR2
215 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
216 IS
217 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
218 l_start_pos NUMBER;
219 l_delimiter varchar2(1):=' ';
220 l_proc VARCHAR2(60):= g_package||' get parameter ';
221 BEGIN
222 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
223 IF l_start_pos = 0 THEN
224 l_delimiter := '|';
225 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
226 end if;
227 IF l_start_pos <> 0 THEN
228 l_start_pos := l_start_pos + length(p_token||'=');
229 l_parameter := substr(p_parameter_string,
230 l_start_pos,
231 instr(p_parameter_string||' ',
232 ',',l_start_pos)
233 - l_start_pos);
234 IF p_segment_number IS NOT NULL THEN
235 l_parameter := ':'||l_parameter||':';
236 l_parameter := substr(l_parameter,
237 instr(l_parameter,':',1,p_segment_number)+1,
238 instr(l_parameter,':',1,p_segment_number+1) -1
239 - instr(l_parameter,':',1,p_segment_number));
240 END IF;
241 END IF;
242 RETURN l_parameter;
243 END get_parameter;
244 --
245 FUNCTION chk_multiple_assignments(p_effective_date IN DATE
246 ,p_person_id IN NUMBER) RETURN VARCHAR2 AS
247 CURSOR get_multiple_assgts IS
248 SELECT count(DISTINCT paf.assignment_id)
249 FROM per_all_assignments_f paf
250 ,per_assignment_status_types pas
251 WHERE paf.assignment_type = 'E'
252 AND paf.PERSON_ID = p_person_id
253 AND p_effective_date between effective_start_date and effective_end_date
254 AND paf.assignment_status_type_id = pas.assignment_status_type_id
255 AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
256 l_count NUMBER :=0;
257 BEGIN
258 OPEN get_multiple_assgts;
259 FETCH get_multiple_assgts INTO l_count;
260 CLOSE get_multiple_assgts;
261 IF l_count > 1 THEN
262 RETURN 'Y';
263 ELSE
264 RETURN 'N';
265 END IF;
266 END chk_multiple_assignments;
267 -----
268 function get_count RETURN NUMBER as
269 l_count NUMBER(15);
270 CURSOR CSR_KW_EFT_COUNT IS
271 SELECT COUNT(*)
272 FROM per_assignments_f paf
273 ,per_people_f pef
274 ,pay_pre_payments ppp
275 ,pay_assignment_actions paa
276 ,pay_payroll_actions ppa
277 WHERE paa.payroll_action_id =
278 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
279 AND paa.pre_payment_id = ppp.pre_payment_id
280 AND paa.payroll_action_id = ppa.payroll_action_id
281 AND paa.assignment_id = paf.assignment_id
282 AND paf.person_id = pef.person_id
283 AND ppp.value > 0
284 AND ppa.effective_date BETWEEN paf.effective_start_date
285 AND paf.effective_end_date
286 AND ppa.effective_date BETWEEN pef.effective_start_date
287 AND pef.effective_end_date;
288 BEGIN
289 open CSR_KW_EFT_COUNT;
290 fetch CSR_KW_EFT_COUNT into l_count;
291 close CSR_KW_EFT_COUNT;
292 return l_count;
293 END get_count;
294 ---------
295 function get_sum return number as
296 l_sum pay_pre_payments.value%type;
297 CURSOR CSR_KW_EFT_SUM is
298 SELECT SUM(ppp.value)
299 FROM per_assignments_f paf
300 ,per_people_f pef
301 ,pay_pre_payments ppp
302 ,pay_assignment_actions paa
303 ,pay_payroll_actions ppa
304 WHERE paa.payroll_action_id =
305 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
306 AND paa.pre_payment_id = ppp.pre_payment_id
307 AND paa.payroll_action_id = ppa.payroll_action_id
308 AND paa.assignment_id = paf.assignment_id
309 AND paf.person_id = pef.person_id
310 AND ppp.value > 0
311 AND ppa.effective_date BETWEEN paf.effective_start_date
312 AND paf.effective_end_date
313 AND ppa.effective_date BETWEEN pef.effective_start_date
314 AND pef.effective_end_date;
315 BEGIN
316 open CSR_KW_EFT_SUM;
317 fetch CSR_KW_EFT_SUM into l_sum;
318 close CSR_KW_EFT_SUM;
319 return l_sum;
320 END get_sum;
321 --------
322 end pay_kw_general;
[Home] [Help]