1 package body pay_sa_general as
2 /* $Header: pysagenr.pkb 120.2.12010000.2 2008/08/06 08:16:32 ubhat ship $ */
3
4 g_package varchar2(30) := 'pay_sa_general';
5
6 ------------------------------------------------------------------------
7 -- Function LOCAL_NATNATIONALITY_NOT_DEFINED
8 -- This function return NOTEXISTS If the value for HR: Local Nationality
9 -- Profile has not been defined else it retuns EXISTS.
10 ------------------------------------------------------------------------
11
12 function local_nationality_not_defined return varchar2
13 is
14
15 begin
16
17 if FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') is null then
18 return 'NOTEXISTS';
19 else
20 return 'EXISTS';
21 end if;
22 end local_nationality_not_defined;
23
24
25 ------------------------------------------------------------------------
26 ------------------------------------------------------------------------
27 -- Function LOCAL_NATIONALITY_MATCHES
28 -- This function return NOMATCH If the value for HR: Local Nationality
29 -- Profile does not match with the person's nationality else it retuns MATCH.
30 ------------------------------------------------------------------------
31 function local_nationality_matches
32 (p_assignment_id IN per_all_assignments_f.assignment_id%type,
33 p_date_earned IN Date)
34 return varchar2
35 is
36 l_person_id number;
37 l_nat_cd varchar2(100);
38
39 begin
40
41 BEGIN
42 Select person_id
43 Into l_person_id
44 From PER_ALL_ASSIGNMENTS_F
45 Where ASSIGNMENT_ID = p_assignment_id
46 AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
47 EXCEPTION
48 WHEN no_data_found Then
49 Null;
50 END;
51
52 BEGIN
53 SELECT NATIONALITY
54 INTO l_nat_cd
55 FROM per_all_people_f
56 WHERE person_id = l_person_id
57 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
58
59 EXCEPTION
60 WHEN no_data_found Then
61 Null;
62 END;
63
64 if UPPER(l_nat_cd) = FND_PROFILE.VALUE('PER_LOCAL_NATIONALITY') then
65 return 'MATCH';
66 else
67 return 'NOMATCH';
68 end if;
69 end local_nationality_matches;
70 ------------------------------------------------------------------------
71 -- Function GET_MESSAGE
72 -- This function is used to obtain a message.
73 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
74 -- If you want to set the value of a token called ELEMENT to Social Ins
75 -- the token parameter would be 'ELEMENT:Social Ins.'
76 ------------------------------------------------------------------------
77
78 function get_message
79 (p_product in varchar2
80 ,p_message_name in varchar2
81 ,p_token1 in varchar2 default null
82 ,p_token2 in varchar2 default null
83 ,p_token3 in varchar2 default null) return varchar2
84 is
85
86 l_message varchar2(2000);
87 l_token_name varchar2(20);
88 l_token_value varchar2(80);
89 l_colon_position number;
90 l_proc varchar2(72) := g_package||'.get_message';
91 --
92 begin
93 --
94 hr_utility.set_location('Entered '||l_proc,5);
95 hr_utility.set_location('. Message Name: '||p_message_name,40);
96
97 fnd_message.set_name(p_product, p_message_name);
98
99 if p_token1 is not null then
100 /* Obtain token 1 name and value */
101 l_colon_position := instr(p_token1,':');
102 l_token_name := substr(p_token1,1,l_colon_position-1);
103 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
104 fnd_message.set_token(l_token_name, l_token_value);
105 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
106 end if;
107
108 if p_token2 is not null then
109 /* Obtain token 2 name and value */
110 l_colon_position := instr(p_token2,':');
111 l_token_name := substr(p_token2,1,l_colon_position-1);
112 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
113 fnd_message.set_token(l_token_name, l_token_value);
114 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
115 end if;
116
117 if p_token3 is not null then
118 /* Obtain token 3 name and value */
119 l_colon_position := instr(p_token3,':');
120 l_token_name := substr(p_token3,1,l_colon_position-1);
121 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
122 fnd_message.set_token(l_token_name, l_token_value);
123 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
124 end if;
125
126 l_message := substr(fnd_message.get,1,254);
127
128 hr_utility.set_location('leaving '||l_proc,100);
129
130 return l_message;
131
132 end get_message;
133 ------------------------------------------------------------------
134 --Functions for EFT
135 ------------------------------------------------------------------
136 -- GET_PARAMETER used in SQL to decode legislative parameters
137 -----------------------------------------------------------------------------
138 FUNCTION get_parameter(
139 p_parameter_string IN VARCHAR2
140 ,p_token IN VARCHAR2
141 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
142 IS
143 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
144 l_start_pos NUMBER;
145 l_delimiter varchar2(1):=' ';
146 l_proc VARCHAR2(60):= g_package||' get parameter ';
147 BEGIN
148 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
149 IF l_start_pos = 0 THEN
150 l_delimiter := '|';
151 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
152 end if;
153 IF l_start_pos <> 0 THEN
154 l_start_pos := l_start_pos + length(p_token||'=');
155 l_parameter := substr(p_parameter_string,
156 l_start_pos,
157 instr(p_parameter_string||' ',
158 ',',l_start_pos)
159 - l_start_pos);
160 IF p_segment_number IS NOT NULL THEN
161 l_parameter := ':'||l_parameter||':';
162 l_parameter := substr(l_parameter,
163 instr(l_parameter,':',1,p_segment_number)+1,
164 instr(l_parameter,':',1,p_segment_number+1) -1
165 - instr(l_parameter,':',1,p_segment_number));
166 END IF;
167 END IF;
168 RETURN l_parameter;
169 END get_parameter;
170 --
171 FUNCTION chk_multiple_assignments(p_effective_date IN DATE
172 ,p_person_id IN NUMBER) RETURN VARCHAR2 AS
173 CURSOR get_multiple_assgts IS
174 SELECT count(DISTINCT paf.assignment_id)
175 FROM per_all_assignments_f paf
176 ,per_assignment_status_types pas
177 WHERE paf.assignment_type = 'E'
178 AND paf.PERSON_ID = p_person_id
179 AND p_effective_date between effective_start_date and effective_end_date
180 AND paf.assignment_status_type_id = pas.assignment_status_type_id
181 AND pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
182 l_count NUMBER :=0;
183 BEGIN
184 OPEN get_multiple_assgts;
185 FETCH get_multiple_assgts INTO l_count;
186 CLOSE get_multiple_assgts;
187 IF l_count > 1 THEN
188 RETURN 'Y';
189 ELSE
190 RETURN 'N';
191 END IF;
192 END chk_multiple_assignments;
193 -----
194 function get_sum return number as
195 l_sum pay_pre_payments.value%type;
196 CURSOR CSR_SA_EFT_SUM is
197 SELECT SUM(ppp.value)
198 FROM per_assignments_f paf
199 ,per_people_f pef
200 ,pay_pre_payments ppp
201 ,pay_assignment_actions paa
202 ,pay_payroll_actions ppa
203 WHERE paa.payroll_action_id =
204 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
205 AND paa.pre_payment_id = ppp.pre_payment_id
206 AND paa.payroll_action_id = ppa.payroll_action_id
207 AND paa.assignment_id = paf.assignment_id
208 AND paf.person_id = pef.person_id
209 AND ppp.value > 0
210 AND ppa.effective_date BETWEEN paf.effective_start_date
211 AND paf.effective_end_date
212 AND ppa.effective_date BETWEEN pef.effective_start_date
213 AND pef.effective_end_date;
214 BEGIN
215 open CSR_SA_EFT_SUM;
216 fetch CSR_SA_EFT_SUM into l_sum;
217 close CSR_SA_EFT_SUM;
218 return l_sum;
219 END get_sum;
220 ----------------
221 function get_count RETURN NUMBER as
222 l_count NUMBER(15);
223 CURSOR CSR_SA_EFT_COUNT IS
224 SELECT COUNT(*)
225 FROM per_assignments_f paf
226 ,per_people_f pef
227 ,pay_pre_payments ppp
228 ,pay_assignment_actions paa
229 ,pay_payroll_actions ppa
230 WHERE paa.payroll_action_id =
231 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
232 AND paa.pre_payment_id = ppp.pre_payment_id
233 AND paa.payroll_action_id = ppa.payroll_action_id
234 AND paa.assignment_id = paf.assignment_id
235 AND paf.person_id = pef.person_id
236 AND ppp.value > 0
237 AND ppa.effective_date BETWEEN paf.effective_start_date
238 AND paf.effective_end_date
239 AND ppa.effective_date BETWEEN pef.effective_start_date
240 AND pef.effective_end_date;
241 BEGIN
242 open CSR_SA_EFT_COUNT;
243 fetch CSR_SA_EFT_COUNT into l_count;
244 close CSR_SA_EFT_COUNT;
245 return l_count;
246 END get_count;
247 ------------------------
248 ------------------------------------------------------------------------
249 -- Function for returning contributory wage of employees over 50 years
250 ------------------------------------------------------------------------
251 FUNCTION get_cont_wage_emp_50 (
252 p_assignment_action_id IN NUMBER
253 ,p_assignment_id IN NUMBER
254 ,p_date_earned IN DATE
255 ,p_pct_value IN NUMBER
256 ,p_subject_to_gosi IN NUMBER)
257 RETURN NUMBER AS
258
259 CURSOR csr_get_def_bal_id (p_def_bal_name IN VARCHAR2) IS
260 SELECT u.creator_id
261 FROM ff_user_entities u,
262 ff_database_items d
263 WHERE d.user_name = p_def_bal_name
264 AND u.user_entity_id = d.user_entity_id
265 AND u.legislation_code = 'SA'
266 AND u.business_group_id is null
267 AND u.creator_type = 'B';
268
269 CURSOR csr_get_assact_id IS
270 SELECT paa.assignment_action_id
271 ,ppa.date_earned
272 FROM pay_assignment_actions paa
273 ,pay_payroll_actions ppa
274 ,pay_run_results prr
275 ,pay_element_types_f pet
276 WHERE paa.assignment_id = p_assignment_id
277 AND paa.assignment_action_id = prr.assignment_action_id
278 AND paa.payroll_action_id = ppa.payroll_action_id
279 AND paa.action_status = 'C'
280 AND ppa.action_status = 'C'
281 AND ppa.action_type in ('R','Q')
282 AND prr.element_type_id = pet.element_type_id
283 AND pet.element_name ='GOSI'
284 AND p_date_earned between pet.effective_start_date and pet.effective_end_date
285 AND prr.status = 'P'
286 AND paa.assignment_action_id < p_assignment_action_id
287 --AND ppa.date_earned >= ADD_MONTHS(TRUNC(p_date_earned,'YYYY'),-12)
288 ORDER BY paa.assignment_action_id DESC;
289
290 rec_get_assact_id csr_get_assact_id%ROWTYPE;
291
292 l_prev_salary NUMBER;
293 l_subject_gosi_id NUMBER;
294 l_old_assact_id NUMBER;
295 l_prev_assact_id NUMBER;
296 l_ref_earnings_id NUMBER;
297 l_diff_exist NUMBER;
298 l_diff_salary NUMBER;
299 l_old_wage NUMBER;
300 l_c_wage_1 NUMBER;
301 l_c_wage_2 NUMBER;
302 l_old_date_earned DATE;
303
304 BEGIN
305 l_diff_exist := 0;
306 l_ref_earnings_id := NULL;
307 OPEN csr_get_def_bal_id( 'GOSI_REFERENCE_EARNINGS_ASG_YTD');
308 FETCH csr_get_def_bal_id INTO l_ref_earnings_id;
309 CLOSE csr_get_def_bal_id;
310
311 l_subject_gosi_id := NULL;
312 OPEN csr_get_def_bal_id( 'SUBJECT_TO_GOSI_ASG_RUN');
313 FETCH csr_get_def_bal_id INTO l_subject_gosi_id;
314 CLOSE csr_get_def_bal_id;
315
316 l_old_assact_id := NULL;
317
318 /*check if the earlier run was in previous year and get the assignment action_id*/
319 OPEN csr_get_assact_id;
320 LOOP
321 FETCH csr_get_assact_id INTO rec_get_assact_id;
322 EXIT WHEN csr_get_assact_id%NOTFOUND;
323 IF l_old_assact_id IS NULL THEN
324 IF TRUNC(rec_get_assact_id.date_earned,'YYYY') < TRUNC(p_date_earned, 'YYYY') THEN
325 l_old_assact_id := rec_get_assact_id.assignment_action_id;
326 l_old_date_earned := rec_get_assact_id.date_earned;
327 l_old_wage := pay_balance_pkg.get_value(l_ref_earnings_id,l_old_assact_id);
328 ELSE
329 /*Exit if the earlier run is in the same year. This indicates there is no need to calculate contributory wage*/
330 EXIT;
331 END IF;
332 END IF;
333 /*l_prev_salary := pay_balance_pkg.get_value(l_subject_gosi_id,rec_get_assact_id.assignment_action_id);*/
334 /*Check if there is any salary change in the previous year*/
335 IF TRUNC(l_old_date_earned ,'YYYY') = TRUNC(rec_get_assact_id.date_earned,'YYYY') THEN
336 /*Bug No6976224*/
337 /*IF l_prev_salary <> p_subject_to_gosi THEN*/
338 IF l_old_wage <> p_subject_to_gosi THEN
339 l_diff_exist := 1;
340 EXIT;
341 END IF;
342 END IF;
343
344 END LOOP;
345 CLOSE csr_get_assact_id;
346
347 IF (l_diff_exist = 1) AND (l_old_assact_id IS NOT NULL) THEN
348 /*Calculate contributory wage if there is any change*/
349 l_diff_salary := p_subject_to_gosi - l_prev_salary;
350 /*Bug No 6976224*/
351 /*l_c_wage_1 := ((p_pct_value/100) * (l_diff_salary)) + l_old_wage;*/
352 l_c_wage_1 := ((p_pct_value/100) * (l_old_wage)) + l_old_wage;
353 l_c_wage_2 := p_subject_to_gosi;
354
355 RETURN (LEAST(l_c_wage_1, l_c_wage_2));
356 END IF;
357
358 RETURN(nvl(l_old_wage,0));
359
360 END get_cont_wage_emp_50;
361
362 end pay_sa_general;
363
[Home] [Help]