[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_PAYFILE
Source
1 package body PAY_NL_PAYFILE as
2 /* $Header: pynleftp.pkb 120.3.12000000.1 2007/01/17 22:54:49 appldev noship $ */
3 g_package varchar2(33) := ' PAY_NL_PAYFILE.';
4 -- Global Variables
5 hr_formula_error EXCEPTION;
6 g_payee_details_formula_exists BOOLEAN := TRUE;
7 g_payee_details_formula_cached BOOLEAN := FALSE;
8 g_payee_details_formula_id ff_formulas_f.formula_id%TYPE;
9 g_payee_details_formula_name ff_formulas_f.formula_name%TYPE;
10 g_trans_desc_formula_exists BOOLEAN := TRUE;
11 g_trans_desc_formula_cached BOOLEAN := FALSE;
12 g_trans_desc_formula_id ff_formulas_f.formula_id%TYPE;
13 g_trans_desc_formula_name ff_formulas_f.formula_name%TYPE;
14 ------------------------------------------------------------------------------
15 -- Global Variables for over riding Oraganization Name
16 ------------------------------------------------------------------------------
17 g_org_details_formula_exists BOOLEAN := TRUE;
18 g_org_details_formula_cached BOOLEAN := FALSE;
19 g_org_details_formula_id ff_formulas_f.formula_id%TYPE;
20 g_org_details_formula_name ff_formulas_f.formula_name%TYPE;
21 -----------------------------------------------------------------------------
22
23
24 FUNCTION get_payee_details(p_assignment_id IN NUMBER
25 ,p_business_group_id IN NUMBER
26 ,p_per_pay_method_id IN NUMBER
27 ,p_date_earned IN DATE
28 ,p_payee_address OUT NOCOPY VARCHAR2
29 ) RETURN VARCHAR2 IS
30 --
31 CURSOR csr_get_payee_type_id IS
32 SELECT payee_type,payee_id
33 FROM pay_personal_payment_methods_f ppm
34 WHERE ppm.assignment_id = p_assignment_id
35 AND ppm.personal_payment_method_id = p_per_pay_method_id
36 AND p_date_earned BETWEEN ppm.effective_start_date
37 AND ppm.effective_end_date
38 AND ppm.business_group_id = p_business_group_id
39 AND ppm.payee_id is NOT NULL;
40 --
41 cursor csr_org_count (p_org_id pay_personal_payment_methods_f.payee_id%TYPE) is
42 select count(*) from
43 hr_All_organization_units hou,
44 hr_organization_information hoi
45 where hou.organization_id = p_org_id
46 and hoi.organization_id = hou.organization_id
47 and hoi.org_information_context = 'CLASS'
48 and hoi.org_information1 = 'NL_PAYEE_OVERRIDE'
49 and hoi.org_information2='Y';
50 --
51 l_payee_type pay_personal_payment_methods_f.payee_type%TYPE;
52 l_payee_id pay_personal_payment_methods_f.payee_id%TYPE;
53 l_payee_name VARCHAR(35);
54 l_org_count NUMBER;
55 --
56 l_inputs ff_exec.inputs_t;
57 l_outputs ff_exec.outputs_t;
58 p_formula_exists BOOLEAN := TRUE;
59 p_formula_cached BOOLEAN := FALSE;
60 p_formula_id ff_formulas_f.formula_id%TYPE;
61 p_formula_name ff_formulas_f.formula_name%TYPE;
62 --
63 BEGIN
64 --
65 -- To be removed.
66 -- hr_utility.trace_on(null,'EFT');
67 -- hr_utility.set_location('--In Get Payee Details ',10);
68 g_payee_details_formula_name := 'NL_PAYEE_REPORTING_NAME';
69 g_org_details_formula_name := 'NL_ORG_PAYEE_REPORTING_NAME';
70 l_payee_name := ' ';
71 OPEN csr_get_payee_type_id;
72 FETCH csr_get_payee_type_id INTO l_payee_type,l_payee_id;
73 IF csr_get_payee_type_id%FOUND THEN
74 --
75 /* A check is made to see whether thsi organization having the Dutch
76 Payee Override Classification or not */
77 IF l_payee_type = 'O' THEN
78 OPEN csr_org_count(l_payee_id);
79 FETCH csr_org_count INTO l_org_count;
80 IF l_org_count >0 THEN
81 --
82 IF g_org_details_formula_exists = TRUE THEN
83 IF g_org_details_formula_cached = FALSE THEN
84 cache_formula('NL_ORG_PAYEE_REPORTING_NAME',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
85 g_org_details_formula_exists:=p_formula_exists;
86 g_org_details_formula_cached:=p_formula_cached;
87 g_org_details_formula_id:=p_formula_id;
88 END IF;
89 --
90 IF g_org_details_formula_exists THEN
91 --
92 l_inputs(1).name := 'ASSIGNMENT_ID';
93 l_inputs(1).value := p_assignment_id;
94 l_inputs(2).name := 'ORGANIZATION_ID';
95 l_inputs(2).value := l_payee_id;
96 l_inputs(3).name := 'DATE_EARNED';
97 l_inputs(3).value := fnd_date.date_to_canonical(p_date_earned);
98 l_inputs(4).name := 'BUSINESS_GROUP_ID';
99 l_inputs(4).value := p_business_group_id;
100 --
101 l_outputs(1).name := 'REPORTING_NAME';
102 --
103 run_formula(p_formula_id => g_org_details_formula_id,
104 p_effective_date => p_date_earned,
105 p_formula_name => g_org_details_formula_name,
106 p_inputs => l_inputs,
107 p_outputs => l_outputs);
108 --
109 l_payee_name := substr(l_outputs(1).value,1,32);
110 p_payee_address := nvl(substr(get_payee_address(l_payee_id
111 , l_payee_type
112 , p_date_earned),1,35),' ');
113 RETURN l_payee_name;
114 CLOSE csr_org_count;
115 END IF;
116
117 END IF;
118 END IF;
119
120
121 -- Get PAYE Name
122 --
123 l_payee_name:=substr(pay_org_payment_methods_pkg.payee_type(
124 l_payee_type
125 ,l_payee_id
126 ,p_date_earned),1,35); --
127 -- Get PAYE Address
128 --
129
130 p_payee_address := nvl(substr(get_payee_address(l_payee_id
131 , l_payee_type
132 , p_date_earned),1,35),' ');
133 hr_utility.set_location('--In Core PAYEE Return ',11);
134
135 RETURN l_payee_name;
136 CLOSE csr_org_count;
137 CLOSE csr_get_payee_type_id;
138
139 ELSIF l_payee_type = 'P' OR l_payee_type = 'p' THEN
140 --
141 IF g_payee_details_formula_exists = TRUE THEN
142
143 hr_utility.set_location('-- In the formula if ',111);
144
145
146 IF g_payee_details_formula_cached = FALSE THEN
147 cache_formula('NL_PAYEE_REPORTING_NAME',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
148 g_payee_details_formula_exists:=p_formula_exists;
149 g_payee_details_formula_cached:=p_formula_cached;
150 g_payee_details_formula_id:=p_formula_id;
151 END IF;
152 --
153 IF g_payee_details_formula_exists THEN
154 --
155 --
156 l_inputs(1).name := 'ASSIGNMENT_ID';
157 l_inputs(1).value := p_assignment_id;
158 l_inputs(2).name := 'PERSON_ID';
159 l_inputs(2).value := l_payee_id;
160 l_inputs(3).name := 'DATE_EARNED';
161 l_inputs(3).value := fnd_date.date_to_canonical(p_date_earned);
162 l_inputs(4).name := 'BUSINESS_GROUP_ID';
163 l_inputs(4).value := p_business_group_id;
164 --
165 l_outputs(1).name := 'REPORTING_NAME';
166 --
167 run_formula(p_formula_id => g_payee_details_formula_id,
168 p_effective_date => p_date_earned,
169 p_formula_name => g_payee_details_formula_name,
170 p_inputs => l_inputs,
171 p_outputs => l_outputs);
172 --
173 l_payee_name := substr(l_outputs(1).value,1,32);
174 p_payee_address := nvl(substr(get_payee_address(l_payee_id
175 , l_payee_type
176 , p_date_earned),1,35),' ');
177 return l_payee_name;
178 CLOSE csr_get_payee_type_id;
179 END IF;
180 END IF;
181
182 -- Get PAYE Name
183 -- hr_utility.set_location('--After the formula if ',115);
184
185 l_payee_name:=substr(pay_org_payment_methods_pkg.payee_type(
186 l_payee_type
187 ,l_payee_id
188 ,p_date_earned),1,35);
189
190 -- hr_utility.set_location('--l_payee name '||l_payee_name ,117);
191
192 -- Get PAYE Address
193 --
194 -- hr_utility.set_location('--In Formula Return ',11);
195 p_payee_address := nvl(substr(get_payee_address(l_payee_id
196 , l_payee_type
197 , p_date_earned),1,35),' ');
198 -- hr_utility.set_location('--p_payee_address '||p_payee_address ,119);
199
200 RETURN l_payee_name;
201 CLOSE csr_get_payee_type_id;
202
203 END IF;
204 --
205 ELSE
206
207 IF g_payee_details_formula_exists = TRUE THEN
208
209
210 IF g_payee_details_formula_cached = FALSE THEN
211 cache_formula('NL_PAYEE_REPORTING_NAME',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
212 g_payee_details_formula_exists:=p_formula_exists;
213 g_payee_details_formula_cached:=p_formula_cached;
214 g_payee_details_formula_id:=p_formula_id;
215 END IF;
216 --
217 IF g_payee_details_formula_exists THEN
218 --
219 --
220 l_inputs(1).name := 'ASSIGNMENT_ID';
221 l_inputs(1).value := p_assignment_id;
222 l_inputs(2).name := 'PERSON_ID';
223 l_inputs(2).value := l_payee_id;
224 l_inputs(3).name := 'DATE_EARNED';
225 l_inputs(3).value := fnd_date.date_to_canonical(p_date_earned);
226 l_inputs(4).name := 'BUSINESS_GROUP_ID';
227 l_inputs(4).value := p_business_group_id;
228 --
229 l_outputs(1).name := 'REPORTING_NAME';
230 --
231 run_formula(p_formula_id => g_payee_details_formula_id,
232 p_effective_date => p_date_earned,
233 p_formula_name => g_payee_details_formula_name,
234 p_inputs => l_inputs,
235 p_outputs => l_outputs);
236 --
237 l_payee_name := substr(l_outputs(1).value,1,32);
238
239 SELECT person_id INTO l_payee_id
240 FROM per_all_assignments_f paf
241 WHERE paf.assignment_id = p_assignment_id
242 AND p_date_earned BETWEEN paf.effective_start_date
243 AND paf.effective_end_date;
244
245 l_payee_type := 'P';
246
247 p_payee_address := nvl(substr(get_payee_address(l_payee_id
248 , l_payee_type
249 , p_date_earned),1,35),' ');
250
251
252 return l_payee_name;
253 CLOSE csr_get_payee_type_id;
254 END IF;
255 END IF;
256
257 SELECT person_id INTO l_payee_id
258 FROM per_all_assignments_f paf
259 WHERE paf.assignment_id = p_assignment_id
260 AND p_date_earned BETWEEN paf.effective_start_date
261 AND paf.effective_end_date;
262
263 l_payee_type := 'P';
264
265 l_payee_name:= ' ';
266
267 l_payee_name:= nvl(l_payee_name,' ');
268
269 -- hr_utility.set_location('--l_payee name '||l_payee_name ,117);
270
271 -- Get PAYE Address
272 --
273 -- hr_utility.set_location('--In Formula Return ',11);
274 p_payee_address := nvl(substr(get_payee_address(l_payee_id
275 , l_payee_type
276 , p_date_earned),1,35),' ');
277
278 CLOSE csr_get_payee_type_id;
279 return l_payee_name ;
280 END IF;
281 --
282 END get_payee_details;
283
284 FUNCTION get_transaction_desc (p_assignment_id IN NUMBER
285 ,p_date_earned IN DATE
286 ,p_business_group_id IN NUMBER
287 ,p_transaction_desc IN VARCHAR2
288 ,p_prepayment_id IN VARCHAR2
289 ) RETURN VARCHAR2 IS
290 l_transaction_desc varchar(131);
291 l_inputs ff_exec.inputs_t;
292 l_outputs ff_exec.outputs_t;
293 p_formula_exists BOOLEAN := TRUE;
294 p_formula_cached BOOLEAN := FALSE;
295 p_formula_id ff_formulas_f.formula_id%TYPE;
296 p_formula_name ff_formulas_f.formula_name%TYPE;
297 BEGIN
298 g_trans_desc_formula_name := 'NL_TRANSACTION_DESCRIPTION';
299 IF g_trans_desc_formula_exists = TRUE THEN
300 IF g_trans_desc_formula_cached = FALSE THEN
301 cache_formula('NL_TRANSACTION_DESCRIPTION',p_business_group_id,p_date_earned,p_formula_id,p_formula_exists,p_formula_cached);
302 g_trans_desc_formula_exists:=p_formula_exists;
303 g_trans_desc_formula_cached:=p_formula_cached;
304 g_trans_desc_formula_id:=p_formula_id;
305 END IF;
306 --
307 IF g_trans_desc_formula_exists = TRUE THEN
308 -- hr_utility.trace('FORMULA EXISTS');
309 --
310 l_inputs(1).name := 'ASSIGNMENT_ID';
311 l_inputs(1).value := p_assignment_id;
312 l_inputs(2).name := 'DATE_EARNED';
313 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
314 l_inputs(3).name := 'BUSINESS_GROUP_ID';
315 l_inputs(3).value := p_business_group_id;
316 l_inputs(4).name := 'PRE_PAYMENT_ID';
317 l_inputs(4).value := p_prepayment_id;
321 run_formula(p_formula_id => g_trans_desc_formula_id,
318 --
319 l_outputs(1).name := 'TRANSACTION_DESCRIPTION';
320 --
322 p_effective_date => p_date_earned,
323 p_formula_name => g_trans_desc_formula_name,
324 p_inputs => l_inputs,
325 p_outputs => l_outputs);
326 --
327 l_transaction_desc := substr(l_outputs(1).value,1,128);
328 -- hr_utility.trace('p_transaction_desc'||p_transaction_desc);
329 -- hr_utility.trace('l_transaction_desc'||l_transaction_desc);
330 ELSE
331 -- hr_utility.trace('FORMULA DOESNT EXISTS');
332 l_transaction_desc := p_transaction_desc;
333 -- hr_utility.trace('p_transaction_desc'||p_transaction_desc);
334 -- hr_utility.trace('l_transaction_desc'||l_transaction_desc);
335 END IF;
336 ELSIF g_trans_desc_formula_exists = FALSE THEN
337 l_transaction_desc := p_transaction_desc;
338 END IF;
339 RETURN l_transaction_desc;
340 END get_transaction_desc;
341 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
342 ,p_business_group_id IN NUMBER
343 ,p_effective_date IN DATE
344 ,p_formula_id IN OUT NOCOPY NUMBER
345 ,p_formula_exists IN OUT NOCOPY BOOLEAN
346 ,p_formula_cached IN OUT NOCOPY BOOLEAN
347 ) IS
348 --
349 CURSOR c_compiled_formula_exist IS
350 SELECT 'Y'
351 FROM ff_formulas_f ff
352 ,ff_compiled_info_f ffci
353 WHERE ff.formula_id = ffci.formula_id
354 AND ff.effective_start_date = ffci.effective_start_date
355 AND ff.effective_end_date = ffci.effective_end_date
356 AND ff.formula_id = p_formula_id
357 AND ff.business_group_id = p_business_group_id
358 AND p_effective_date BETWEEN ff.effective_start_date
359 AND ff.effective_end_date;
360 --
361 CURSOR c_get_formula(p_formula_name ff_formulas_f.formula_name%TYPE
362 ,p_effective_date DATE) IS
363 SELECT ff.formula_id
364 FROM ff_formulas_f ff
365 WHERE ff.formula_name = p_formula_name
366 AND ff.business_group_id = p_business_group_id
367 AND p_effective_date BETWEEN ff.effective_start_date
368 AND ff.effective_end_date;
369 --
370 l_test VARCHAR2(1);
371 BEGIN
372 --
373 IF p_formula_cached = FALSE THEN
374 --
375 --
376 OPEN c_get_formula(p_formula_name,p_effective_date);
377 FETCH c_get_formula INTO p_formula_id;
378 IF c_get_formula%FOUND THEN
379 OPEN c_compiled_formula_exist;
380 FETCH c_compiled_formula_exist INTO l_test;
381 IF c_compiled_formula_exist%NOTFOUND THEN
382 p_formula_cached := FALSE;
383 p_formula_exists := FALSE;
384 --
385 fnd_message.set_name('PAY','FFX03A_FORMULA_NOT_FOUND');
386 fnd_message.set_token('1', p_formula_name);
387 fnd_message.raise_error;
388 ELSE
389 p_formula_cached := TRUE;
390 p_formula_exists := TRUE;
391 END IF;
392 ELSE
393 p_formula_cached := FALSE;
394 p_formula_exists := FALSE;
395 END IF;
396 CLOSE c_get_formula;
397 END IF;
398 --
399 END cache_formula;
400 PROCEDURE run_formula(p_formula_id IN NUMBER
401 ,p_effective_date IN DATE
402 ,p_formula_name IN VARCHAR2
403 ,p_inputs IN ff_exec.inputs_t
404 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
405 l_inputs ff_exec.inputs_t;
406 l_outputs ff_exec.outputs_t;
407 BEGIN
408 hr_utility.set_location('--In Formula ',20);
409 --
410 -- Initialize the formula
411 --
412 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
413 --
414 -- Set up the input values
415 --
416 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
417 FOR i IN l_inputs.first..l_inputs.last LOOP
418 FOR j IN p_inputs.first..p_inputs.last LOOP
419 IF l_inputs(i).name = p_inputs(j).name THEN
420 l_inputs(i).value := p_inputs(j).value;
421 exit;
422 END IF;
423 END LOOP;
424 END LOOP;
425 END IF;
426 --
427 -- Run the formula
428 --
429 ff_exec.run_formula(l_inputs,l_outputs);
430 --
431 -- Populate the output table
432 --
433 IF l_outputs.count > 0 and p_inputs.count > 0 then
434 FOR i IN l_outputs.first..l_outputs.last LOOP
435 FOR j IN p_outputs.first..p_outputs.last LOOP
436 IF l_outputs(i).name = p_outputs(j).name THEN
437 p_outputs(j).value := l_outputs(i).value;
438 exit;
439 END IF;
440 END LOOP;
441 END LOOP;
442 END IF;
443 hr_utility.set_location('--Leaving Formula ',21);
444 EXCEPTION
445 WHEN hr_formula_error THEN
446 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
447 fnd_message.set_token('1', p_formula_name);
448 fnd_message.raise_error;
449 WHEN OTHERS THEN
450 raise;
451 --
452 END run_formula;
453 FUNCTION get_payee_address(p_payee_id IN NUMBER
454 ,p_payee_type IN VARCHAR2
455 ,p_effective_date IN DATE) RETURN VARCHAR2 AS
456 --
457 CURSOR csr_get_per_address_style(p_payee_id NUMBER) IS -- to get the address style
458 SELECT substr(style,1,35) style
459 FROM per_addresses pas
460 WHERE pas.person_id = p_payee_id
461 AND pas.primary_flag = 'Y'
462 AND p_effective_date BETWEEN pas.date_from
463 AND nvl(pas.date_to,to_date('31/12/4712','DD/MM/YYYY'));
464 --
465 CURSOR csr_get_per_address(p_payee_id NUMBER) IS -- to get the city when address style is Netherlands
466 SELECT substr(hr_general.decode_lookup('HR_NL_CITY',town_or_city),1,35) town_or_city
467 FROM per_addresses pas
468 WHERE pas.person_id = p_payee_id
469 AND pas.primary_flag = 'Y'
470 AND p_effective_date BETWEEN pas.date_from
471 AND nvl(pas.date_to,to_date('31/12/4712','DD/MM/YYYY'));
472 --
473 CURSOR csr_get_per_address1(p_payee_id NUMBER) IS -- to get the city when address style is Netherlands (International)
474 SELECT substr(town_or_city,1,35) town_or_city
475 FROM per_addresses pas
476 WHERE pas.person_id = p_payee_id
477 AND pas.primary_flag = 'Y'
478 AND p_effective_date BETWEEN pas.date_from
479 AND nvl(pas.date_to,to_date('31/12/4712','DD/MM/YYYY'));
480 --
481 CURSOR csr_get_org_address(p_payee_id NUMBER) IS
482 SELECT substr(hr_general.decode_lookup('HR_NL_CITY',town_or_city),1,35) town_or_city
483 FROM hr_locations_all hla
484 ,hr_all_organization_units hou
485 WHERE hou.organization_id = p_payee_id
486 AND hou.location_id = hla.location_id;
487 --
488 l_payee_address VARCHAR2(35);
489 l_payee_address_style VARCHAR2(35);
490 --
491 BEGIN
492 hr_utility.set_location('--In Payee Address ',30);
493 --
494 IF p_payee_type = 'P' THEN -- Person Address
495 OPEN csr_get_per_address_style(p_payee_id);
496 FETCH csr_get_per_address_style INTO l_payee_address_style;
497 CLOSE csr_get_per_address_style;
498 IF l_payee_address_style='NL' THEN
499 OPEN csr_get_per_address(p_payee_id);
500 FETCH csr_get_per_address INTO l_payee_address;
501 CLOSE csr_get_per_address;
502 ELSIF l_payee_address_style='NL_GLB' THEN
503 OPEN csr_get_per_address1(p_payee_id);
504 FETCH csr_get_per_address1 INTO l_payee_address;
505 CLOSE csr_get_per_address1;
506 END IF;
507 ELSIF p_payee_type = 'O' THEN -- Organization Address
508 OPEN csr_get_org_address(p_payee_id);
509 FETCH csr_get_org_address INTO l_payee_address;
510 CLOSE csr_get_org_address;
511 END IF;
512 --
513 hr_utility.set_location('--Leaving Payee Address ',30);
514 RETURN l_payee_address;
515 --
516 END get_payee_address;
517 END PAY_NL_PAYFILE;