[Home] [Help]
PACKAGE BODY: APPS.PAY_SA_EFT
Source
1 package body PAY_SA_EFT as
2 /* $Header: pysaeftp.pkb 120.0 2005/05/29 08:29:56 appldev noship $ */
3 g_package varchar2(33) := 'PAY_SA_PAYFILE.';
4 -- Global Variables
5 hr_formula_error EXCEPTION;
6 g_formula_exists BOOLEAN := TRUE;
7 g_formula_cached BOOLEAN := FALSE;
8 g_formula_id ff_formulas_f.formula_id%TYPE;
9 g_formula_name ff_formulas_f.formula_name%TYPE;
10 -----------------------------------------------------------------------------
11 --
12 FUNCTION get_customer_formula_header (
13 p_Date_Earned IN DATE
14 ,p_payment_method_id IN number
15 ,p_business_group_id IN number
16 ,p_payroll_id IN number
17 ,p_payroll_action_id IN number
18 ,p_creation_date IN VARCHAR2
19 ,p_process_date IN VARCHAR2
20 ,p_count IN VARCHAR2
21 ,p_sum IN VARCHAR2
22 ,p_write_text1 OUT NOCOPY VARCHAR2
23 ,p_write_text2 OUT NOCOPY VARCHAR2
24 ,p_write_text3 OUT NOCOPY VARCHAR2
25 ,p_write_text4 OUT NOCOPY VARCHAR2
26 ,p_write_text5 OUT NOCOPY VARCHAR2
27 ,p_report_text1 OUT NOCOPY VARCHAR2
28 ,p_report_text2 OUT NOCOPY VARCHAR2
29 ,p_report_text3 OUT NOCOPY VARCHAR2
30 ,p_report_text4 OUT NOCOPY VARCHAR2
31 ,p_report_text5 OUT NOCOPY VARCHAR2
32 ,p_report_text6 OUT NOCOPY VARCHAR2
33 ,p_report_text7 OUT NOCOPY VARCHAR2
34 ,p_report_text8 OUT NOCOPY VARCHAR2
35 ,p_report_text9 OUT NOCOPY VARCHAR2
36 ,p_report_text10 OUT NOCOPY VARCHAR2) return varchar2 IS
37 l_header varchar2(100);
38 l_body varchar2(100);
39 l_footer varchar2(100);
40 l_inputs ff_exec.inputs_t;
41 l_outputs ff_exec.outputs_t;
42 cursor c_get_name(p_payment_method_id NUMBER) is
43 select PMETH_INFORMATION1,PMETH_INFORMATION2,PMETH_INFORMATION3
44 from PAY_ORG_PAYMENT_METHODS_F where ORG_PAYMENT_METHOD_ID = p_payment_method_id;
45 begin
46 --hr_utility.trace_on(null,'EFT');
47 l_payment_method_id := p_payment_method_id;
48 open c_get_name(p_payment_method_id);
49 fetch c_get_name into l_id_header,l_id_body,l_id_footer;
50 close c_get_name;
51 l_inputs(1).name := 'DATE_EARNED';
52 l_inputs(1).value := fnd_date.date_to_canonical(p_date_earned);
53 l_inputs(2).name := 'ORG_PAY_METHOD_ID';
54 l_inputs(2).value := p_payment_method_id;
55 l_inputs(3).name := 'BUSINESS_GROUP_ID';
56 l_inputs(3).value := p_business_group_id;
57 l_inputs(4).name := 'PAYROLL_ID';
58 l_inputs(4).value := p_payroll_id;
59 l_inputs(5).name := 'PAYROLL_ACTION_ID';
60 l_inputs(5).value := p_payroll_action_id;
61 l_inputs(6).name := 'CREATION_DATE';
62 l_inputs(6).value := p_creation_date;
63 l_inputs(7).name := 'PROCESS_DATE';
64 l_inputs(7).value := p_process_date;
65 l_inputs(8).name := 'COUNT1';
66 l_inputs(8).value := p_count;
67 l_inputs(9).name := 'SUM1';
68 l_inputs(9).value := p_sum;
69
70 l_outputs(1).name := 'WRITE_TEXT1';
71 l_outputs(2).name := 'WRITE_TEXT2';
72 l_outputs(3).name := 'WRITE_TEXT3';
73 l_outputs(4).name := 'WRITE_TEXT4';
74 l_outputs(5).name := 'WRITE_TEXT5';
75 l_outputs(6).name := 'REPORT1_TEXT1';
76 l_outputs(7).name := 'REPORT1_TEXT2';
77 l_outputs(8).name := 'REPORT1_TEXT3';
78 l_outputs(9).name := 'REPORT1_TEXT4';
79 l_outputs(10).name := 'REPORT1_TEXT5';
80 l_outputs(11).name := 'REPORT2_TEXT1';
81 l_outputs(12).name := 'REPORT2_TEXT2';
82 l_outputs(13).name := 'REPORT2_TEXT3';
83 l_outputs(14).name := 'REPORT2_TEXT4';
84 l_outputs(15).name := 'REPORT2_TEXT5';
85 IF l_id_header is not null then
86 run_formula
87 (l_id_header
88 ,p_Date_Earned
89 ,l_inputs
90 ,l_outputs);
91 END IF;
92 IF l_outputs.count > 0 and l_outputs.count > 0 THEN
93 FOR i IN l_outputs.first..l_outputs.last LOOP
94 IF l_outputs(i).name like 'WRITE_TEXT1' THEN
95 p_write_text1 := l_outputs(i).value;
96 ELSIF l_outputs(i).name like 'WRITE_TEXT2' THEN
97 p_write_text2 := l_outputs(i).value;
98 ELSIF l_outputs(i).name like 'WRITE_TEXT3' THEN
99 p_write_text3 := l_outputs(i).value;
100 ELSIF l_outputs(i).name like 'WRITE_TEXT4' THEN
101 p_write_text4 := l_outputs(i).value;
102 ELSIF l_outputs(i).name like 'WRITE_TEXT5' THEN
103 p_write_text5 := l_outputs(i).value;
104 ELSIF l_outputs(i).name like 'REPORT1_TEXT1' THEN
105 p_report_text1 := l_outputs(i).value;
106 ELSIF l_outputs(i).name like 'REPORT1_TEXT2' THEN
107 p_report_text2 := l_outputs(i).value;
108 ELSIF l_outputs(i).name like 'REPORT1_TEXT3' THEN
109 p_report_text3 := l_outputs(i).value;
110 ELSIF l_outputs(i).name like 'REPORT1_TEXT4' THEN
111 p_report_text4 := l_outputs(i).value;
112 ELSIF l_outputs(i).name like 'REPORT1_TEXT5' THEN
113 p_report_text5 := l_outputs(i).value;
114 ELSIF l_outputs(i).name like 'REPORT2_TEXT1' THEN
115 p_report_text6 := l_outputs(i).value;
116 ELSIF l_outputs(i).name like 'REPORT2_TEXT2' THEN
117 p_report_text7 := l_outputs(i).value;
118 ELSIF l_outputs(i).name like 'REPORT2_TEXT3' THEN
119 p_report_text8 := l_outputs(i).value;
120 ELSIF l_outputs(i).name like 'REPORT2_TEXT4' THEN
121 p_report_text9 := l_outputs(i).value;
122 ELSIF l_outputs(i).name like 'REPORT2_TEXT5' THEN
123 p_report_text10 := l_outputs(i).value;
124 END IF;
125 END LOOP;
126 END IF;
127 RETURN '1';
128 END get_customer_formula_header;
129 --
130 FUNCTION get_customer_formula_body (
131 p_assignment_id IN number,
132 p_business_group_id IN number,
133 p_per_pay_method_id IN number,
134 p_date_earned IN date,
135 p_payroll_id IN number,
136 p_payroll_action_id IN number,
137 p_assignment_action_id IN number,
138 p_organization_id IN number,
139 p_tax_unit_id IN number,
140 p_amount IN varchar2,
141 p_first_name IN varchar2,
142 p_last_name IN varchar2,
143 p_initials IN varchar2,
144 p_emp_no IN varchar2,
145 p_asg_no IN varchar2,
146 p_count IN varchar2,
147 p_sum IN varchar2
148 ,p_write_text1 OUT NOCOPY VARCHAR2
149 ,p_write_text2 OUT NOCOPY VARCHAR2
150 ,p_write_text3 OUT NOCOPY VARCHAR2
151 ,p_write_text4 OUT NOCOPY VARCHAR2
152 ,p_write_text5 OUT NOCOPY VARCHAR2
153 ,p_report_text1 OUT NOCOPY VARCHAR2
154 ,p_report_text2 OUT NOCOPY VARCHAR2
155 ,p_report_text3 OUT NOCOPY VARCHAR2
156 ,p_report_text4 OUT NOCOPY VARCHAR2
157 ,p_report_text5 OUT NOCOPY VARCHAR2
158 ,p_report_text6 OUT NOCOPY VARCHAR2
159 ,p_report_text7 OUT NOCOPY VARCHAR2
160 ,p_report_text8 OUT NOCOPY VARCHAR2
161 ,p_report_text9 OUT NOCOPY VARCHAR2
162 ,p_report_text10 OUT NOCOPY VARCHAR2
163 ,p_local_nationality IN VARCHAR2) return varchar2 IS
164 l_header varchar2(100);
165 l_body varchar2(100);
166 l_footer varchar2(100);
167 l_inputs ff_exec.inputs_t;
168 l_outputs ff_exec.outputs_t;
169 cursor c_get_name(p_payment_method_id NUMBER) is
170 select PMETH_INFORMATION1,PMETH_INFORMATION2,PMETH_INFORMATION3
171 from PAY_ORG_PAYMENT_METHODS_F where ORG_PAYMENT_METHOD_ID = p_payment_method_id;
172 begin
173 l_inputs(1).name := 'ASSIGNMENT_ID';
174 l_inputs(1).value := p_assignment_id;
175 l_inputs(2).name := 'BUSINESS_GROUP_ID';
176 l_inputs(2).value := p_business_group_id;
177 l_inputs(3).name := 'PER_PAY_METHOD_ID';
178 l_inputs(3).value := p_per_pay_method_id;
179 l_inputs(4).name := 'DATE_EARNED';
180 l_inputs(4).value := fnd_date.date_to_canonical(p_date_earned);
181 l_inputs(5).name := 'PAYROLL_ID';
182 l_inputs(5).value := p_payroll_id;
183 l_inputs(6).name := 'PAYROLL_ACTION_ID';
184 l_inputs(6).value := p_payroll_action_id;
185 l_inputs(7).name := 'ASSIGNMENT_ACTION_ID';
186 l_inputs(7).value := p_assignment_action_id;
187 l_inputs(8).name := 'ORGANIZATION_ID';
188 l_inputs(8).value := p_organization_id;
189 l_inputs(9).name := 'TAX_UNIT_ID';
190 l_inputs(9).value := p_tax_unit_id;
191 l_inputs(10).name := 'AMOUNT';
192 l_inputs(10).value := p_amount;
193 l_inputs(11).name := 'FIRST_NAME';
194 l_inputs(11).value := p_first_name;
195 l_inputs(12).name := 'LAST_NAME';
196 l_inputs(12).value := p_last_name;
197 l_inputs(13).name := 'INITIALS';
198 l_inputs(13).value := p_initials;
199 l_inputs(14).name := 'EMP_NO';
200 l_inputs(14).value := p_emp_no;
201 l_inputs(15).name := 'ASG_NO';
202 l_inputs(15).value := p_asg_no;
203 l_inputs(16).name := 'TRANSFER_COUNT1';
204 l_inputs(16).value := p_count;
205 l_inputs(17).name := 'TRANSFER_SUM1';
206 l_inputs(17).value := p_sum;
207 l_inputs(18).name := 'ORG_PAY_METHOD_ID';
208 l_inputs(18).value := l_payment_method_id;
209 l_inputs(19).name := 'LOCAL_NATIONALITY';
210 l_inputs(19).value := p_local_nationality;
211
212
213 l_outputs(1).name := 'WRITE_TEXT1';
214 l_outputs(2).name := 'WRITE_TEXT2';
215 l_outputs(3).name := 'WRITE_TEXT3';
216 l_outputs(4).name := 'WRITE_TEXT4';
217 l_outputs(5).name := 'WRITE_TEXT5';
218 l_outputs(6).name := 'REPORT1_TEXT1';
219 l_outputs(7).name := 'REPORT1_TEXT2';
220 l_outputs(8).name := 'REPORT1_TEXT3';
221 l_outputs(9).name := 'REPORT1_TEXT4';
222 l_outputs(10).name := 'REPORT1_TEXT5';
223 l_outputs(11).name := 'REPORT2_TEXT1';
224 l_outputs(12).name := 'REPORT2_TEXT2';
225 l_outputs(13).name := 'REPORT2_TEXT3';
226 l_outputs(14).name := 'REPORT2_TEXT4';
227 l_outputs(15).name := 'REPORT2_TEXT5';
228 IF l_id_body is not null then
229 run_formula
230 (l_id_body
231 ,p_Date_Earned
232 ,l_inputs
233 ,l_outputs);
234 END IF;
235 IF l_outputs.count > 0 and l_outputs.count > 0 THEN
236 FOR i IN l_outputs.first..l_outputs.last LOOP
237 IF l_outputs(i).name like 'WRITE_TEXT1' THEN
238 p_write_text1 := l_outputs(i).value;
239 ELSIF l_outputs(i).name like 'WRITE_TEXT2' THEN
240 p_write_text2 := l_outputs(i).value;
241 ELSIF l_outputs(i).name like 'WRITE_TEXT3' THEN
242 p_write_text3 := l_outputs(i).value;
243 ELSIF l_outputs(i).name like 'WRITE_TEXT4' THEN
244 p_write_text4 := l_outputs(i).value;
245 ELSIF l_outputs(i).name like 'WRITE_TEXT5' THEN
246 p_write_text5 := l_outputs(i).value;
247 ELSIF l_outputs(i).name like 'REPORT1_TEXT1' THEN
248 p_report_text1 := l_outputs(i).value;
249 ELSIF l_outputs(i).name like 'REPORT1_TEXT2' THEN
250 p_report_text2 := l_outputs(i).value;
251 ELSIF l_outputs(i).name like 'REPORT1_TEXT3' THEN
252 p_report_text3 := l_outputs(i).value;
253 ELSIF l_outputs(i).name like 'REPORT1_TEXT4' THEN
254 p_report_text4 := l_outputs(i).value;
255 ELSIF l_outputs(i).name like 'REPORT1_TEXT5' THEN
256 p_report_text5 := l_outputs(i).value;
257 ELSIF l_outputs(i).name like 'REPORT2_TEXT1' THEN
258 p_report_text6 := l_outputs(i).value;
259 ELSIF l_outputs(i).name like 'REPORT2_TEXT2' THEN
260 p_report_text7 := l_outputs(i).value;
261 ELSIF l_outputs(i).name like 'REPORT2_TEXT3' THEN
262 p_report_text8 := l_outputs(i).value;
263 ELSIF l_outputs(i).name like 'REPORT2_TEXT4' THEN
264 p_report_text9 := l_outputs(i).value;
265 ELSIF l_outputs(i).name like 'REPORT2_TEXT5' THEN
266 p_report_text10 := l_outputs(i).value;
267 END IF;
268 END LOOP;
269 END IF;
270 RETURN '1';
271 end get_customer_formula_body;
272 ------
273 -------------
274 --
275 FUNCTION get_customer_formula_footer (
276 p_Date_Earned IN DATE
277 ,p_payment_method_id IN number
278 ,p_business_group_id IN number
279 ,p_payroll_id IN number
280 ,p_payroll_action_id IN number
281 ,p_creation_date IN VARCHAR2
282 ,p_process_date IN VARCHAR2
283 ,p_count IN VARCHAR2
284 ,p_sum IN VARCHAR2
285 ,p_write_text1 OUT NOCOPY VARCHAR2
286 ,p_write_text2 OUT NOCOPY VARCHAR2
287 ,p_write_text3 OUT NOCOPY VARCHAR2
288 ,p_write_text4 OUT NOCOPY VARCHAR2
289 ,p_write_text5 OUT NOCOPY VARCHAR2
290 ,p_report_text1 OUT NOCOPY VARCHAR2
291 ,p_report_text2 OUT NOCOPY VARCHAR2
292 ,p_report_text3 OUT NOCOPY VARCHAR2
293 ,p_report_text4 OUT NOCOPY VARCHAR2
294 ,p_report_text5 OUT NOCOPY VARCHAR2
295 ,p_report_text6 OUT NOCOPY VARCHAR2
296 ,p_report_text7 OUT NOCOPY VARCHAR2
297 ,p_report_text8 OUT NOCOPY VARCHAR2
298 ,p_report_text9 OUT NOCOPY VARCHAR2
299 ,p_report_text10 OUT NOCOPY VARCHAR2) return varchar2 IS
300 l_header varchar2(100);
301 l_body varchar2(100);
302 l_footer varchar2(100);
303 l_inputs ff_exec.inputs_t;
304 l_outputs ff_exec.outputs_t;
305 cursor c_get_name(p_payment_method_id NUMBER) is
306 select PMETH_INFORMATION1,PMETH_INFORMATION2,PMETH_INFORMATION3
307 from PAY_ORG_PAYMENT_METHODS_F where ORG_PAYMENT_METHOD_ID = p_payment_method_id;
311 close c_get_name;
308 begin
309 open c_get_name(p_payment_method_id);
310 fetch c_get_name into l_id_header,l_id_body,l_id_footer;
312 l_inputs(1).name := 'DATE_EARNED';
313 l_inputs(1).value := fnd_date.date_to_canonical(p_date_earned);
314 l_inputs(2).name := 'ORG_PAY_METHOD_ID';
315 l_inputs(2).value := p_payment_method_id;
316 l_inputs(3).name := 'BUSINESS_GROUP_ID';
317 l_inputs(3).value := p_business_group_id;
318 l_inputs(4).name := 'PAYROLL_ID';
319 l_inputs(4).value := p_payroll_id;
320 l_inputs(5).name := 'PAYROLL_ACTION_ID';
321 l_inputs(5).value := p_payroll_action_id;
322 l_inputs(6).name := 'CREATION_DATE';
323 l_inputs(6).value := p_creation_date;
324 l_inputs(7).name := 'PROCESS_DATE';
325 l_inputs(7).value := p_process_date;
326 l_inputs(8).name := 'COUNT1';
327 l_inputs(8).value := p_count;
328 l_inputs(9).name := 'SUM1';
329 l_inputs(9).value := p_sum;
330 l_outputs(1).name := 'WRITE_TEXT1';
331 l_outputs(2).name := 'WRITE_TEXT2';
332 l_outputs(3).name := 'WRITE_TEXT3';
333 l_outputs(4).name := 'WRITE_TEXT4';
334 l_outputs(5).name := 'WRITE_TEXT5';
335 l_outputs(6).name := 'REPORT1_TEXT1';
336 l_outputs(7).name := 'REPORT1_TEXT2';
337 l_outputs(8).name := 'REPORT1_TEXT3';
338 l_outputs(9).name := 'REPORT1_TEXT4';
339 l_outputs(10).name := 'REPORT1_TEXT5';
340 l_outputs(11).name := 'REPORT2_TEXT1';
341 l_outputs(12).name := 'REPORT2_TEXT2';
342 l_outputs(13).name := 'REPORT2_TEXT3';
343 l_outputs(14).name := 'REPORT2_TEXT4';
344 l_outputs(15).name := 'REPORT2_TEXT5';
345 IF l_id_footer is not null then
346 run_formula
347 (l_id_footer
348 ,p_Date_Earned
349 ,l_inputs
350 ,l_outputs);
351 END IF;
352 IF l_outputs.count > 0 and l_outputs.count > 0 THEN
353 FOR i IN l_outputs.first..l_outputs.last LOOP
354 IF l_outputs(i).name like 'WRITE_TEXT1' THEN
355 p_write_text1 := l_outputs(i).value;
356 ELSIF l_outputs(i).name like 'WRITE_TEXT2' THEN
357 p_write_text2 := l_outputs(i).value;
358 ELSIF l_outputs(i).name like 'WRITE_TEXT3' THEN
359 p_write_text3 := l_outputs(i).value;
360 ELSIF l_outputs(i).name like 'WRITE_TEXT4' THEN
361 p_write_text4 := l_outputs(i).value;
362 ELSIF l_outputs(i).name like 'WRITE_TEXT5' THEN
363 p_write_text5 := l_outputs(i).value;
364 ELSIF l_outputs(i).name like 'REPORT1_TEXT1' THEN
365 p_report_text1 := l_outputs(i).value;
366 ELSIF l_outputs(i).name like 'REPORT1_TEXT2' THEN
367 p_report_text2 := l_outputs(i).value;
368 ELSIF l_outputs(i).name like 'REPORT1_TEXT3' THEN
369 p_report_text3 := l_outputs(i).value;
370 ELSIF l_outputs(i).name like 'REPORT1_TEXT4' THEN
371 p_report_text4 := l_outputs(i).value;
372 ELSIF l_outputs(i).name like 'REPORT1_TEXT5' THEN
373 p_report_text5 := l_outputs(i).value;
374 ELSIF l_outputs(i).name like 'REPORT2_TEXT1' THEN
375 p_report_text6 := l_outputs(i).value;
376 ELSIF l_outputs(i).name like 'REPORT2_TEXT2' THEN
377 p_report_text7 := l_outputs(i).value;
378 ELSIF l_outputs(i).name like 'REPORT2_TEXT3' THEN
379 p_report_text8 := l_outputs(i).value;
380 ELSIF l_outputs(i).name like 'REPORT2_TEXT4' THEN
381 p_report_text9 := l_outputs(i).value;
382 ELSIF l_outputs(i).name like 'REPORT2_TEXT5' THEN
383 p_report_text10 := l_outputs(i).value;
384 END IF;
385 END LOOP;
386 END IF;
387 RETURN '1';
388 end get_customer_formula_footer;
389 ---
390 ----------
391 -----
392 PROCEDURE run_formula(p_formula_id IN NUMBER
393 ,p_effective_date IN DATE
394 ,p_inputs IN ff_exec.inputs_t
395 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
396 l_inputs ff_exec.inputs_t;
397 l_outputs ff_exec.outputs_t;
398 BEGIN
399 hr_utility.set_location('--In Formula ',20);
400 --
401 -- Initialize the formula
402 --
403 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
404 --
405 hr_utility.trace('after ff_exec');
406 -- Set up the input values
407 --
408 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
409 FOR i IN l_inputs.first..l_inputs.last LOOP
410 FOR j IN p_inputs.first..p_inputs.last LOOP
411 IF l_inputs(i).name = p_inputs(j).name THEN
412 l_inputs(i).value := p_inputs(j).value;
413 exit;
414 END IF;
415 END LOOP;
416 END LOOP;
417 END IF;
418 --
419 -- Run the formula
420 --
421 hr_utility.trace('about to exec');
422 ff_exec.run_formula(l_inputs,l_outputs);
423 --
424 hr_utility.trace('After exec');
425 -- Populate the output table
426 --
427 IF l_outputs.count > 0 and p_inputs.count > 0 then
428 FOR i IN l_outputs.first..l_outputs.last LOOP
429 FOR j IN p_outputs.first..p_outputs.last LOOP
430 IF l_outputs(i).name = p_outputs(j).name THEN
431 p_outputs(j).value := l_outputs(i).value;
432 exit;
433 END IF;
434 END LOOP;
435 END LOOP;
436 END IF;
437 hr_utility.set_location('--Leaving Formula ',21);
438 EXCEPTION
439 WHEN hr_formula_error THEN
440 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
441 fnd_message.set_token('1', g_formula_name);
442 fnd_message.raise_error;
443 WHEN OTHERS THEN
444 raise;
445 --
446 END run_formula;
447
448
449 END PAY_SA_EFT;