[Home] [Help]
PACKAGE BODY: APPS.PAY_AE_USER_FUNCTION
Source
1 package body pay_ae_user_function as
2 /* $Header: pyaerunf.pkb 120.1.12020000.3 2013/02/07 12:09:16 bkeshary ship $ */
3 g_formula_name ff_formulas_f.formula_name%TYPE;
4
5
6 PROCEDURE run_formula(p_formula_id IN NUMBER
7 ,p_effective_date IN DATE
8 ,p_inputs IN ff_exec.inputs_t
9 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t) IS
10 l_inputs ff_exec.inputs_t;
11 l_outputs ff_exec.outputs_t;
12 BEGIN
13 hr_utility.set_location('--In Formula ',20);
14 --
15 -- Initialize the formula
16 --
17 ff_exec.init_formula(p_formula_id, p_effective_date , l_inputs, l_outputs);
18 --
19 hr_utility.trace('after ff_exec');
20 -- Set up the input values
21 --
22 IF l_inputs.count > 0 and p_inputs.count > 0 THEN
23 FOR i IN l_inputs.first..l_inputs.last LOOP
24 FOR j IN p_inputs.first..p_inputs.last LOOP
25 IF l_inputs(i).name = p_inputs(j).name THEN
26 l_inputs(i).value := p_inputs(j).value;
27 exit;
28 END IF;
29 END LOOP;
30 END LOOP;
31 END IF;
32 --
33 -- Run the formula
34 --
35 hr_utility.trace('about to exec');
36 ff_exec.run_formula(l_inputs,l_outputs);
37 --
38 -- Populate the output table
39 --
40 IF l_outputs.count > 0 and p_inputs.count > 0 then
41 FOR i IN l_outputs.first..l_outputs.last LOOP
42 FOR j IN p_outputs.first..p_outputs.last LOOP
43 IF l_outputs(i).name = p_outputs(j).name THEN
44 p_outputs(j).value := l_outputs(i).value;
45 exit;
46 END IF;
47 END LOOP;
48 END LOOP;
49 END IF;
50 EXCEPTION
51 /*WHEN hr_formula_error THEN
52 fnd_message.set_name('PER','FFX22J_FORMULA_NOT_FOUND');
53 fnd_message.set_token('1', g_formula_name);
54 fnd_message.raise_error;*/
55 WHEN OTHERS THEN
56 raise;
57 --
58 END run_formula;
59
60
61 function run_SI_formula
62 (p_assignment_id IN NUMBER
63 ,p_date_earned IN DATE
64 ,p_business_group_id IN NUMBER
65 ,p_payroll_id IN NUMBER
66 ,p_payroll_action_id IN NUMBER
67 ,p_assignment_action_id IN NUMBER
68 ,p_tax_unit_id IN NUMBER
69 --,p_balance_date IN DATE
70 ,p_element_entry_id IN NUMBER
71 ,p_element_type_id IN NUMBER
72 ,p_original_entry_id IN NUMBER
73 --,p_jurisdiction_code IN VARCHAR2
74 --,p_tax_group IN VARCHAR2
75 --,p_source_id IN NUMBER
76 --,p_source_text IN VARCHAR2
77 )
78 return NUMBER is
79 cursor csr_get_id_flex_num is
80 SELECT id_flex_num
81 FROM FND_ID_FLEX_STRUCTURES_VL
82 WHERE (ID_FLEX_STRUCTURE_CODE = 'AE_STATUTORY_INFO')
83 and (APPLICATION_ID=800)
84 and (ID_FLEX_CODE='SCL') ;
85 cursor csr_get_formula_id ( p_id_flex_num number) is
86 select HOI2.org_information1
87 from hr_organization_units HOU
88 ,hr_organization_information HOI1
89 ,hr_organization_information HOI2
90 ,hr_soft_coding_keyflex HSCK
91 ,per_all_assignments_f PAA
92 where HOU.business_group_id = p_business_group_id
93 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
94 to_date('4712/12/31','YYYY/MM/DD'))
95 and HOU.organization_id = HOI1.organization_id
96 and HOI1.org_information_context = 'CLASS'
97 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
98 and HOI1.organization_id = HOI2.organization_id
99 and PAA.assignment_id = p_assignment_id
100 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
101 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
102 and HSCK.id_flex_num = p_id_flex_num
103 and decode(HSCK.id_flex_num,p_id_flex_num,to_number(HSCK.segment1),-9999) = HOU.organization_id
104 and HOI2.org_information_context = 'AE_REFERENCE_FF';
105 l_formula_id NUMBER;
106 l_inputs ff_exec.inputs_t;
107 l_outputs ff_exec.outputs_t;
108 l_value NUMBER;
109 l_id_flex_num FND_ID_FLEX_STRUCTURES_VL.id_flex_num %type;
110 begin
111 open csr_get_id_flex_num;
112 fetch csr_get_id_flex_num into l_id_flex_num;
113 close csr_get_id_flex_num;
114 open csr_get_formula_id(l_id_flex_num);
115 fetch csr_get_formula_id into l_formula_id;
116 close csr_get_formula_id;
117 l_inputs(1).name := 'ASSIGNMENT_ID';
118 l_inputs(1).value := p_assignment_id;
119 l_inputs(2).name := 'DATE_EARNED';
120 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
121 l_inputs(3).name := 'BUSINESS_GROUP_ID';
122 l_inputs(3).value := p_business_group_id;
123 l_inputs(4).name := 'PAYROLL_ID';
124 l_inputs(4).value := p_payroll_id;
125 l_inputs(5).name := 'PAYROLL_ACTION_ID';
126 l_inputs(5).value := p_payroll_action_id;
127 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
128 l_inputs(6).value := p_assignment_action_id;
129 l_inputs(7).name := 'TAX_UNIT_ID';
130 l_inputs(7).value := p_tax_unit_id;
131 --l_inputs(8).name := 'BALANCE_DATE';
132 --l_inputs(8).value := fnd_date.date_to_canonical(p_balance_date);
133 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
134 l_inputs(8).value := p_element_entry_id;
135 l_inputs(9).name := 'ELEMENT_TYPE_ID';
136 l_inputs(9).value := p_element_type_id;
137 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
138 l_inputs(10).value := p_original_entry_id;
139 --l_inputs(11).name := 'JURISDICTION_CODE';
140 --l_inputs(11).value := p_jurisdiction_code;
141 --l_inputs(11).name := 'TAX_GROUP';
142 --l_inputs(11).value := p_tax_group;
143 --l_inputs(12).name := 'SOURCE_ID';
144 --l_inputs(12).value := p_source_id;
145 --l_inputs(12).name := 'SOURCE_TEXT';
146 --l_inputs(12).value := p_source_text;
147 l_outputs(1).name := 'CONTRIBUTORY_SALARY_REFERENCE';
148 if l_formula_id is not null then
149 --
150 run_formula (l_formula_id
151 ,p_date_earned
152 ,l_inputs
153 ,l_outputs);
154
155 l_value := NVL(l_outputs(l_outputs.first).value,0);
156 else
157 l_value := 0;
158 end if;
159 return(l_value);
160 end run_SI_formula;
161
162 function run_KW_SI_formula
163 (p_assignment_id IN NUMBER
164 ,p_date_earned IN DATE
165 ,p_business_group_id IN NUMBER
166 ,p_payroll_id IN NUMBER
167 ,p_payroll_action_id IN NUMBER
168 ,p_assignment_action_id IN NUMBER
169 ,p_tax_unit_id IN NUMBER
170 --,p_balance_date IN DATE
171 ,p_element_entry_id IN NUMBER
172 ,p_element_type_id IN NUMBER
173 ,p_original_entry_id IN NUMBER
174 --,p_jurisdiction_code IN VARCHAR2
175 --,p_tax_group IN VARCHAR2
176 --,p_source_id IN NUMBER
177 --,p_source_text IN VARCHAR2
178 )
179 return NUMBER is
180 cursor csr_get_id_flex_num is
181 SELECT id_flex_num
182 FROM FND_ID_FLEX_STRUCTURES_VL
183 WHERE (ID_FLEX_STRUCTURE_CODE = 'AE_STATUTORY_INFO')
184 and (APPLICATION_ID=800)
185 and (ID_FLEX_CODE='SCL') ;
186 cursor csr_get_formula_id ( p_id_flex_num number) is
187 select HOI2.org_information1
188 from hr_organization_units HOU
189 ,hr_organization_information HOI1
190 ,hr_organization_information HOI2
191 ,hr_soft_coding_keyflex HSCK
192 ,per_all_assignments_f PAA
193 where HOU.business_group_id = p_business_group_id
194 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
195 to_date('4712/12/31','YYYY/MM/DD'))
196 and HOU.organization_id = HOI1.organization_id
197 and HOI1.org_information_context = 'CLASS'
198 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
199 and HOI1.organization_id = HOI2.organization_id
200 and PAA.assignment_id = p_assignment_id
201 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
202 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
203 and HSCK.id_flex_num = p_id_flex_num
204 and decode(HSCK.id_flex_num,p_id_flex_num,to_number(HSCK.segment1),-9999) = HOU.organization_id
205 and HOI2.org_information_context = 'AE_OTHER_NAT_REFERENCE_FF';
206 l_formula_id NUMBER;
207 l_inputs ff_exec.inputs_t;
208 l_outputs ff_exec.outputs_t;
209 l_value NUMBER;
210 l_id_flex_num FND_ID_FLEX_STRUCTURES_VL.id_flex_num %type;
211 begin
212 open csr_get_id_flex_num;
213 fetch csr_get_id_flex_num into l_id_flex_num;
214 close csr_get_id_flex_num;
215 open csr_get_formula_id(l_id_flex_num);
216 fetch csr_get_formula_id into l_formula_id;
217 close csr_get_formula_id;
218 l_inputs(1).name := 'ASSIGNMENT_ID';
219 l_inputs(1).value := p_assignment_id;
220 l_inputs(2).name := 'DATE_EARNED';
221 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
222 l_inputs(3).name := 'BUSINESS_GROUP_ID';
223 l_inputs(3).value := p_business_group_id;
224 l_inputs(4).name := 'PAYROLL_ID';
225 l_inputs(4).value := p_payroll_id;
226 l_inputs(5).name := 'PAYROLL_ACTION_ID';
227 l_inputs(5).value := p_payroll_action_id;
228 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
229 l_inputs(6).value := p_assignment_action_id;
230 l_inputs(7).name := 'TAX_UNIT_ID';
231 l_inputs(7).value := p_tax_unit_id;
232 --l_inputs(8).name := 'BALANCE_DATE';
233 --l_inputs(8).value := fnd_date.date_to_canonical(p_balance_date);
234 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
235 l_inputs(8).value := p_element_entry_id;
236 l_inputs(9).name := 'ELEMENT_TYPE_ID';
237 l_inputs(9).value := p_element_type_id;
238 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
239 l_inputs(10).value := p_original_entry_id;
240 --l_inputs(11).name := 'JURISDICTION_CODE';
241 --l_inputs(11).value := p_jurisdiction_code;
242 --l_inputs(11).name := 'TAX_GROUP';
243 --l_inputs(11).value := p_tax_group;
244 --l_inputs(12).name := 'SOURCE_ID';
245 --l_inputs(12).value := p_source_id;
246 --l_inputs(12).name := 'SOURCE_TEXT';
247 --l_inputs(12).value := p_source_text;
248 l_outputs(1).name := 'CONTRIBUTORY_SALARY_REFERENCE';
249 if l_formula_id is not null then
250 --
251 run_formula (l_formula_id
252 ,p_date_earned
253 ,l_inputs
254 ,l_outputs);
255
256 l_value := NVL(l_outputs(l_outputs.first).value,0);
257 else
258 l_value := 0;
259 end if;
260 return(l_value);
261 end run_KW_SI_formula;
262
263 -- Added for SA natioanls working in AE
264
265 function run_SA_GOSI_formula
266 (p_assignment_id IN NUMBER
267 ,p_date_earned IN DATE
268 ,p_business_group_id IN NUMBER
269 ,p_payroll_id IN NUMBER
270 ,p_payroll_action_id IN NUMBER
271 ,p_assignment_action_id IN NUMBER
272 ,p_tax_unit_id IN NUMBER
273 --,p_balance_date IN DATE
274 ,p_element_entry_id IN NUMBER
275 ,p_element_type_id IN NUMBER
276 ,p_original_entry_id IN NUMBER
277 --,p_jurisdiction_code IN VARCHAR2
278 --,p_tax_group IN VARCHAR2
279 --,p_source_id IN NUMBER
280 --,p_source_text IN VARCHAR2
281 )
282 return NUMBER is
283 cursor csr_get_id_flex_num is
284 SELECT id_flex_num
285 FROM FND_ID_FLEX_STRUCTURES_VL
286 WHERE (ID_FLEX_STRUCTURE_CODE = 'AE_STATUTORY_INFO')
287 and (APPLICATION_ID=800)
288 and (ID_FLEX_CODE='SCL') ;
289 cursor csr_get_formula_id ( p_id_flex_num number) is
290 select HOI2.org_information2
291 from hr_organization_units HOU
292 ,hr_organization_information HOI1
293 ,hr_organization_information HOI2
294 ,hr_soft_coding_keyflex HSCK
295 ,per_all_assignments_f PAA
296 where HOU.business_group_id = p_business_group_id
297 and trunc(p_date_earned) between HOU.date_from and nvl(HOU.date_to,
298 to_date('4712/12/31','YYYY/MM/DD'))
299 and HOU.organization_id = HOI1.organization_id
300 and HOI1.org_information_context = 'CLASS'
301 and HOI1.org_information1 = 'HR_LEGAL_EMPLOYER'
302 and HOI1.organization_id = HOI2.organization_id
303 and PAA.assignment_id = p_assignment_id
304 and trunc(p_date_earned) between PAA.effective_start_date and PAA.effective_end_date
305 and PAA.soft_coding_keyflex_id = HSCK.soft_coding_keyflex_id
306 and HSCK.id_flex_num = p_id_flex_num
307 and decode(HSCK.id_flex_num,p_id_flex_num,to_number(HSCK.segment1),-9999) = HOU.organization_id
308 and HOI2.org_information_context = 'AE_OTHER_NAT_REFERENCE_FF';
309 l_formula_id NUMBER;
310 l_inputs ff_exec.inputs_t;
311 l_outputs ff_exec.outputs_t;
312 l_value NUMBER;
313 l_id_flex_num FND_ID_FLEX_STRUCTURES_VL.id_flex_num %type;
314 begin
315 hr_utility.set_location('Inside run_SA_GOSI_formula',10);
316 hr_utility.set_location('p_assignment_id' || p_assignment_id ,10);
317 hr_utility.set_location('p_date_earned'|| fnd_date.date_to_canonical(p_date_earned) ,10);
318 hr_utility.set_location('p_business_group_id'|| p_business_group_id,10);
319 hr_utility.set_location('p_payroll_id'|| p_payroll_id,10);
320
321 open csr_get_id_flex_num;
322 fetch csr_get_id_flex_num into l_id_flex_num;
323 close csr_get_id_flex_num;
324 open csr_get_formula_id(l_id_flex_num);
325 fetch csr_get_formula_id into l_formula_id;
326 close csr_get_formula_id;
327 l_inputs(1).name := 'ASSIGNMENT_ID';
328 l_inputs(1).value := p_assignment_id;
329 l_inputs(2).name := 'DATE_EARNED';
330 l_inputs(2).value := fnd_date.date_to_canonical(p_date_earned);
331 l_inputs(3).name := 'BUSINESS_GROUP_ID';
332 l_inputs(3).value := p_business_group_id;
333 l_inputs(4).name := 'PAYROLL_ID';
334 l_inputs(4).value := p_payroll_id;
335 l_inputs(5).name := 'PAYROLL_ACTION_ID';
336 l_inputs(5).value := p_payroll_action_id;
337 l_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
338 l_inputs(6).value := p_assignment_action_id;
339 l_inputs(7).name := 'TAX_UNIT_ID';
340 l_inputs(7).value := p_tax_unit_id;
341 --l_inputs(8).name := 'BALANCE_DATE';
342 --l_inputs(8).value := fnd_date.date_to_canonical(p_balance_date);
343 l_inputs(8).name := 'ELEMENT_ENTRY_ID';
344 l_inputs(8).value := p_element_entry_id;
345 l_inputs(9).name := 'ELEMENT_TYPE_ID';
346 l_inputs(9).value := p_element_type_id;
347 l_inputs(10).name := 'ORIGINAL_ENTRY_ID';
348 l_inputs(10).value := p_original_entry_id;
349 --l_inputs(11).name := 'JURISDICTION_CODE';
350 --l_inputs(11).value := p_jurisdiction_code;
351 --l_inputs(11).name := 'TAX_GROUP';
352 --l_inputs(11).value := p_tax_group;
353 --l_inputs(12).name := 'SOURCE_ID';
354 --l_inputs(12).value := p_source_id;
355 --l_inputs(12).name := 'SOURCE_TEXT';
356 --l_inputs(12).value := p_source_text;
357 l_outputs(1).name := 'CONTRIBUTORY_SALARY_REFERENCE';
358 if l_formula_id is not null then
359 run_formula (l_formula_id
360 ,p_date_earned
361 ,l_inputs
362 ,l_outputs);
363 l_value := NVL(l_outputs(l_outputs.first).value,0);
364 hr_utility.set_location('l_value: '||l_value, 10);
365 else
366 l_value := 0;
367 hr_utility.set_location('l_value: '||l_value, 20);
368 end if;
369 return(l_value);
370 end run_sa_gosi_formula;
371
372
373 end pay_ae_user_function;