1 package body pay_sa_general as
2 /* $Header: pysagenr.pkb 120.4.12020000.2 2012/10/16 11:05:23 bkeshary 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 /*+ index(pef) index(ppa) use_nl(paa ppa) use_nl(paa paf) */
198 SUM(ppp.value)
199 FROM per_assignments_f paf
200 ,per_people_f pef
201 ,pay_pre_payments ppp
202 ,pay_assignment_actions paa
203 ,pay_payroll_actions ppa
204 WHERE paa.payroll_action_id =
205 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
206 AND paa.pre_payment_id = ppp.pre_payment_id
207 AND paa.payroll_action_id = ppa.payroll_action_id
208 AND paa.assignment_id = paf.assignment_id
209 AND paf.person_id = pef.person_id
210 AND ppp.value > 0
211 AND ppa.effective_date BETWEEN paf.effective_start_date
212 AND paf.effective_end_date
213 AND ppa.effective_date BETWEEN pef.effective_start_date
214 AND pef.effective_end_date;
215 BEGIN
216 open CSR_SA_EFT_SUM;
217 fetch CSR_SA_EFT_SUM into l_sum;
218 close CSR_SA_EFT_SUM;
219 return l_sum;
220 END get_sum;
221 ----------------
222 function get_count RETURN NUMBER as
223 l_count NUMBER(15);
224 CURSOR CSR_SA_EFT_COUNT IS
225 SELECT /*+ index(pef) index(ppa) use_nl(paa ppa) use_nl(paa paf) */
226 COUNT(*)
227 FROM per_assignments_f paf
228 ,per_people_f pef
229 ,pay_pre_payments ppp
230 ,pay_assignment_actions paa
231 ,pay_payroll_actions ppa
232 WHERE paa.payroll_action_id =
233 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
234 AND paa.pre_payment_id = ppp.pre_payment_id
235 AND paa.payroll_action_id = ppa.payroll_action_id
236 AND paa.assignment_id = paf.assignment_id
237 AND paf.person_id = pef.person_id
238 AND ppp.value > 0
239 AND ppa.effective_date BETWEEN paf.effective_start_date
240 AND paf.effective_end_date
241 AND ppa.effective_date BETWEEN pef.effective_start_date
242 AND pef.effective_end_date;
243 BEGIN
244 open CSR_SA_EFT_COUNT;
245 fetch CSR_SA_EFT_COUNT into l_count;
246 close CSR_SA_EFT_COUNT;
247 return l_count;
248 END get_count;
249 ------------------------
250 ------------------------------------------------------------------------
251 -- Function for returning contributory wage of employees over 50 years
252 ------------------------------------------------------------------------
253 FUNCTION get_cont_wage_emp_50 (
254 p_assignment_action_id IN NUMBER
255 ,p_assignment_id IN NUMBER
256 ,p_date_earned IN DATE
257 ,p_pct_value IN NUMBER
258 ,p_subject_to_gosi IN NUMBER)
259 RETURN NUMBER AS
260
261 CURSOR csr_get_def_bal_id (p_def_bal_name IN VARCHAR2) IS
262 SELECT u.creator_id
263 FROM ff_user_entities u,
264 ff_database_items d
265 WHERE d.user_name = p_def_bal_name
266 AND u.user_entity_id = d.user_entity_id
267 AND u.legislation_code = 'SA'
268 AND u.business_group_id is null
269 AND u.creator_type = 'B';
270
271 CURSOR csr_get_assact_id IS
272 SELECT paa.assignment_action_id
273 ,ppa.date_earned
274 FROM pay_assignment_actions paa
275 ,pay_payroll_actions ppa
276 ,pay_run_results prr
277 ,pay_element_types_f pet
278 WHERE paa.assignment_id = p_assignment_id
279 AND paa.assignment_action_id = prr.assignment_action_id
280 AND paa.payroll_action_id = ppa.payroll_action_id
281 AND paa.action_status IN ('C','S')
282 AND ppa.action_status = 'C'
283 AND ppa.action_type in ('R','Q')
284 AND prr.element_type_id = pet.element_type_id
285 AND pet.element_name ='GOSI'
286 AND p_date_earned between pet.effective_start_date and pet.effective_end_date
287 AND prr.status = 'P'
288 AND paa.assignment_action_id < p_assignment_action_id
289 --AND ppa.date_earned >= ADD_MONTHS(TRUNC(p_date_earned,'YYYY'),-12)
290 ORDER BY paa.assignment_action_id DESC;
291
292 rec_get_assact_id csr_get_assact_id%ROWTYPE;
293
294 l_prev_salary NUMBER;
295 l_subject_gosi_id NUMBER;
296 l_old_assact_id NUMBER;
297 l_prev_assact_id NUMBER;
298 l_ref_earnings_id NUMBER;
299 l_diff_exist NUMBER;
300 l_diff_salary NUMBER;
301 l_old_wage NUMBER;
302 l_c_wage_1 NUMBER;
303 l_c_wage_2 NUMBER;
304 l_old_date_earned DATE;
305
306 BEGIN
307 l_diff_exist := 0;
308 l_ref_earnings_id := NULL;
309 OPEN csr_get_def_bal_id( 'GOSI_REFERENCE_EARNINGS_ASG_YTD');
310 FETCH csr_get_def_bal_id INTO l_ref_earnings_id;
311 CLOSE csr_get_def_bal_id;
312
313 l_subject_gosi_id := NULL;
314 OPEN csr_get_def_bal_id( 'SUBJECT_TO_GOSI_ASG_RUN');
315 FETCH csr_get_def_bal_id INTO l_subject_gosi_id;
316 CLOSE csr_get_def_bal_id;
317
318 l_old_assact_id := NULL;
319
320 /*check if the earlier run was in previous year and get the assignment action_id*/
321 OPEN csr_get_assact_id;
322 LOOP
323 FETCH csr_get_assact_id INTO rec_get_assact_id;
324 EXIT WHEN csr_get_assact_id%NOTFOUND;
325 IF l_old_assact_id IS NULL THEN
326 IF TRUNC(rec_get_assact_id.date_earned,'YYYY') < TRUNC(p_date_earned, 'YYYY') THEN
327 l_old_assact_id := rec_get_assact_id.assignment_action_id;
328 l_old_date_earned := rec_get_assact_id.date_earned;
329 l_old_wage := pay_balance_pkg.get_value(l_ref_earnings_id,l_old_assact_id);
330 ELSE
331 /*Exit if the earlier run is in the same year. This indicates there is no need to calculate contributory wage*/
332 EXIT;
333 END IF;
334 END IF;
335 /*l_prev_salary := pay_balance_pkg.get_value(l_subject_gosi_id,rec_get_assact_id.assignment_action_id);*/
336 /*Check if there is any salary change in the previous year*/
337 IF TRUNC(l_old_date_earned ,'YYYY') = TRUNC(rec_get_assact_id.date_earned,'YYYY') THEN
338 /*Bug No6976224*/
339 /*IF l_prev_salary <> p_subject_to_gosi THEN*/
340 IF l_old_wage <> p_subject_to_gosi THEN
341 l_diff_exist := 1;
342 EXIT;
343 END IF;
344 END IF;
345
346 END LOOP;
347 CLOSE csr_get_assact_id;
348
349 IF (l_diff_exist = 1) AND (l_old_assact_id IS NOT NULL) THEN
350 /*Calculate contributory wage if there is any change*/
351 l_diff_salary := p_subject_to_gosi - l_prev_salary;
352 /*Bug No 6976224*/
353 /*l_c_wage_1 := ((p_pct_value/100) * (l_diff_salary)) + l_old_wage;*/
354 l_c_wage_1 := ((p_pct_value/100) * (l_old_wage)) + l_old_wage;
355 l_c_wage_2 := p_subject_to_gosi;
356
357 RETURN (LEAST(l_c_wage_1, l_c_wage_2));
358 END IF;
359
360 RETURN(nvl(l_old_wage,0));
361
362 END get_cont_wage_emp_50;
363
364 end pay_sa_general;
365
[Home] [Help]