[Home] [Help]
PACKAGE BODY: APPS.BEN_TCS_COMPENSATION
Source
6
1 PACKAGE BODY BEN_TCS_COMPENSATION as
2 /* $Header: betcscmp.pkb 120.12.12010000.11 2010/02/03 11:37:57 vkodedal ship $ */
3 g_period_table period_table;
4 g_status varchar2(3) ;
5 g_package varchar2(30) := 'BEN_TCS_COMPENSATION';
7 PROCEDURE WRITE (p_string IN VARCHAR2)
8 IS
9 BEGIN
10 ben_batch_utils.WRITE (p_string);
11 END WRITE ;
12
13 procedure delete_rows is
14 l_proc varchar2(60) := g_package||'delete_rows';
15 begin
16 hr_utility.set_location('Entering into '||l_proc,10);
17 g_period_table.delete;
18 hr_utility.set_location('Leaving '||l_proc,10);
19 end delete_rows;
20
21 Procedure Insert_period( p_period_start_date in date,
22 p_period_end_date in date
23 ) is
24
25 l_number_of_rows number;
26 l_flag varchar2(1);
27 l_next_row number;
28 l_proc varchar2(60) := g_package||'Insert_period';
29 Begin
30 hr_utility.set_location('Entering into '||l_proc,10);
31
32 l_next_row := NVL(g_period_table.LAST, 0) + 1;
33 g_period_table(l_next_row).start_date := p_period_start_date;
34 g_period_table(l_next_row).end_date := p_period_end_date;
35
36 hr_utility.set_location('Leaving '||l_proc,10);
37 end Insert_period;
38
39 procedure get_salary_basis_details ( p_input_value_id in number,
40 p_effective_date in date,
41 p_period_start_date in date,
42 p_assignment_id in number,
43 p_pay_basis_id out nocopy number,
44 p_rate_basis out nocopy varchar2,
45 p_PAY_ANN_FACTOR out nocopy number,
46 p_name out nocopy varchar2
47 ) is
48
49 l_proc varchar2(1000) := g_package||'get_salary_basis_details';
50
51 cursor csr_salary_basis_details is
52 select pay.pay_basis_id,
53 pay_basis,
54 PAY_ANNUALIZATION_FACTOR,
55 name
56 from per_pay_bases pay,
57 per_all_assignments_f asg
58 where pay.input_value_id = p_input_value_id
59 and pay.business_group_id = asg.business_group_id
60 and pay.pay_basis_id = asg.pay_basis_id
61 and asg.assignment_id = p_assignment_id
62 and p_period_start_date <= asg.effective_end_date
63 and p_effective_date >= asg.effective_start_date;
64
65 l_salary_rec csr_salary_basis_details%ROWTYPE;
66
67 begin
68 hr_utility.set_location('Entering: '||l_proc,30);
69
70 open csr_salary_basis_details;
71 fetch csr_salary_basis_details into l_salary_rec;
72 if csr_salary_basis_details%notfound then
73 g_status := '1B';
74 hr_utility.set_location('No Salary basis linked to input value id passed ', 40);
75 end if;
76 close csr_salary_basis_details;
77 p_pay_basis_id := l_salary_rec.pay_basis_id;
78 p_rate_basis := l_salary_rec.pay_basis;
79 p_PAY_ANN_FACTOR :=l_salary_rec.PAY_ANNUALIZATION_FACTOR;
80 p_name :=l_salary_rec.name ;
81
82 hr_utility.set_location('Leaving: '||l_proc,30);
83 exception
84 when no_data_found then
85 g_status := '1B';
86 hr_utility.set_location('No Salary basis linked to input value id passed ', 40);
87 end get_salary_basis_details;
88
89
90 procedure element_entry(p_assignment_id in number,
91 p_element_type_id in number,
92 p_input_value_id in number,
93 p_perd_st_dt in date,
94 p_perd_en_dt in date,
95 p_summary out nocopy period_table,
96 p_actual_uom in varchar2,
97 p_lookup_type in varchar2,
98 p_value_set_id in varchar2 ,
99 p_actual_currency_code in varchar2 ) is
100 cursor csr_elmnt_entry is
101 select ee.effective_start_date ee_esd,
102 ee.effective_end_date ee_eed,
103 ee.CREATOR_TYPE creator_type,
104 eev.screen_entry_value eev_amt,
105 eev.ELEMENT_ENTRY_VALUE_ID eev_id
106 from pay_element_entries_f ee,
107 pay_element_entry_values_f eev
108 where ee.assignment_id = p_assignment_id
109 and ee.element_type_id = p_element_type_id
110 and ee.effective_start_date < p_perd_en_dt
111 and ee.effective_end_date > p_perd_st_dt
112 and ee.element_entry_id = eev.element_entry_id
113 and eev.input_value_id = p_input_value_id
114 and eev.effective_start_date = ee.effective_start_date
115 and eev.effective_end_date = ee.effective_end_date
116 and eev.screen_entry_value is not null
117 order by ee.effective_start_date;
118
119 i integer;
120 l_proc varchar2(60) := g_package||'element_entry';
121 begin
122 hr_utility.set_location('Entering'||l_proc,10);
123 hr_utility.set_location('Elmnt_id'||p_element_type_id,10);
124 hr_utility.set_location('iv_id'||p_input_value_id,10);
125 i := 0;
126 for elmnt_entry_row in csr_elmnt_entry loop
127 i := i + 1;
128 hr_utility.set_location('ee being recorded :'||elmnt_entry_row.eev_amt,10);
129
130 p_summary(i).start_date := elmnt_entry_row.ee_esd;
131 p_summary(i).end_date := elmnt_entry_row.ee_eed;
132 if p_value_set_id is null and p_lookup_type is null then
133 p_summary(i).value := elmnt_entry_row.eev_amt;
134 /* p_summary(i).value := hr_chkfmt.changeformat(
135 elmnt_entry_row.eev_amt,
136 p_actual_uom,
140 p_lookup_type,
137 p_actual_currency_code);*/
138 elsif p_lookup_type is not null then
139 p_summary(i).value := hr_general.decode_lookup(
141 elmnt_entry_row.eev_amt) ;
142 elsif p_value_set_id is not null then
143 p_summary(i).value := pay_input_values_pkg.decode_vset_value(
144 p_value_set_id,
145 elmnt_entry_row.eev_amt);
146 end if ;
147 p_summary(i).creator_type := elmnt_entry_row.creator_type;
148 p_summary(i).output_key := elmnt_entry_row.eev_id;
149
150 end loop;
151 hr_utility.set_location('p summary count '||p_summary.count,45);
152 hr_utility.set_location('# of entries'||to_char(i),20);
153 hr_utility.set_location('Leaving'||l_proc,10);
154 end element_entry;
155
156 procedure bnfts_entries (p_person_id in number,
157 p_perd_st_dt in date,
158 p_perd_en_dt in date,
159 p_input_value_id in number,
160 p_comp_typ_cd in varchar2,
161 p_currency_cd in varchar2,
162 p_uom in varchar2,
163 p_bnfts_table out nocopy period_table) is
164
165 cursor bnfts_rows is
166 select pbb.effective_start_date,
167 pbb.effective_end_date,
168 pbb.per_bnfts_bal_id,
169 pbb.val,
170 bb.uom,
171 bb.nnmntry_uom
172 from ben_bnfts_bal_f bb,
173 ben_per_bnfts_bal_f pbb
174 where bb.bnfts_bal_id = p_input_value_id
175 and pbb.bnfts_bal_id = bb.bnfts_bal_id
176 and pbb.person_id = p_person_id
177 and pbb.effective_start_date between p_perd_st_dt and p_perd_en_dt
178 and bb.effective_start_date < p_perd_en_dt
179 and bb.effective_end_date > p_perd_st_dt
180 and pbb.val is not null;
181 l_proc varchar2(1000) := g_package||'bnfts_entries';
182 i integer := 0;
183 Begin
184 hr_utility.set_location('Entering '||l_proc,20);
185 for bnfts_rec in bnfts_rows loop
186 i := i + 1;
187 p_bnfts_table(i).start_date := bnfts_rec.effective_start_date;
188 p_bnfts_table(i).end_date := bnfts_rec.effective_end_date;
189 p_bnfts_table(i).value := fnd_number.number_to_canonical(bnfts_rec.val) ;
190 p_bnfts_table(i).output_key := bnfts_rec.per_bnfts_bal_id;
191
192 if p_comp_typ_cd = 'MNTRY' then
193 p_bnfts_table(i).currency_cd := nvl(p_currency_cd,bnfts_rec.uom);
194 elsif p_comp_typ_cd = 'NNMNTRY' then
195 p_bnfts_table(i).uom := nvl(p_uom,bnfts_rec.nnmntry_uom);
196 end if;
197 p_bnfts_table(i).actual_uom := bnfts_rec.nnmntry_uom;
198 end loop;
199 exception
200 when others then
201 g_status := '3';
202
203 hr_utility.set_location('Leaving '||l_proc,20);
204 end bnfts_entries;
205
206 procedure rule_entries (p_assignment_id in number,
207 p_perd_start_date in date,
208 p_perd_end_date in date,
209 p_date_earned in date,
210 p_input_value_id in number,
211 p_comp_typ_cd in varchar2,
212 p_currency_cd in varchar2,
213 p_uom in varchar2,
214 p_rule_table out nocopy period_table) is
215
216 l_inputs ff_exec.inputs_t;
217 l_outputs ff_exec.outputs_t;
218 -- vkodedal modified the length of varchar Bug# 6992595
219 l_comp_dates varchar2(2000);
220 l_comp_values varchar2(2000);
221 --
222 l_proc varchar2(100) := g_package || 'get_commitment_from_formula';
223 --
224 l_input_count number;
225 i_dt number := 1;
226 j_dt number;
227 i_vl number := 1;
228 j_vl number;
229 -- vkodedal modified the length of varchar Bug# 6992595
230 l_date varchar2(2000);
231 l_value varchar2(2000);
232
233 k number := 1;
234 z number := 1;
235 ---- vkodedal new variables to store additional dates and values 14-May-2008 Bug# 6992595
236 l_comp_dates1 varchar2(1000);
237 l_comp_values1 varchar2(1000);
238 l_comp_dates2 varchar2(1000);
239 l_comp_values2 varchar2(1000);
240 l_comp_dates3 varchar2(1000);
241 l_comp_values3 varchar2(1000);
242
243 begin
244 hr_utility.set_location ('Entering: '||l_proc,05);
245 --
246 -- Initialise the formula .
247 --
248
249
250 ff_exec.init_formula
251 (p_formula_id => p_input_value_id,
252 p_effective_date => p_date_earned,
253 p_inputs => l_inputs,
254 p_outputs => l_outputs);
255
256 hr_utility.set_location ('Set Context '||l_proc,10);
257 hr_utility.set_location ('Set Context :'||l_inputs.first|| ' : ' || l_inputs.last,10);
258 --
259 for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
260 --
261 if l_inputs(l_count).name = 'ASSIGNMENT_ID' then
262 --
263 l_inputs(l_count).value := p_assignment_id;
264 --
265 --
266 elsif l_inputs(l_count).name = 'PERIOD_START_DATE' then
267 --
268 l_inputs(l_count).value := fnd_date.date_to_canonical(p_perd_start_date);
269
270 elsif l_inputs(l_count).name = 'PERIOD_END_DATE' then
271 --
272 l_inputs(l_count).value := fnd_date.date_to_canonical(p_perd_end_date);
273 --
274 elsif l_inputs(l_count).name = 'DATE_EARNED' then
275
276 l_inputs(l_count).value := fnd_date.date_to_canonical(p_date_earned);
277 --
278 end if;
279 --
280 end loop;
281
285 --
282 hr_utility.set_location ('Run formula: '||l_proc,15);
283 --
284 -- We have loaded the input record . Now run the formula.
286 ff_exec.run_formula(p_inputs => l_inputs,
287 p_outputs => l_outputs);
288
289 hr_utility.set_location ('After Run formula: '||l_outputs.first || ' : ' ||l_outputs.last ,15);
290 --
291 --
292 -- Loop through the returned table and make sure that the returned
293 -- values have been found
294 -- vkodedal Bug 6992595 added variables and modified condition
295 --
296 for l_count in l_outputs.first..l_outputs.last loop
297 --
298 --
299 hr_utility.set_location ('After Run formula:'||l_outputs(l_count).name ,15);
300 --
301 if l_outputs(l_count).name = 'COMPENSATION_DATES' then
302 --
303 l_comp_dates := l_outputs(l_count).value;
304 --
305 Elsif l_outputs(l_count).name = 'COMPENSATION_DATES1' then
306 --
307 l_comp_dates1 := l_outputs(l_count).value;
308 --
309 Elsif l_outputs(l_count).name = 'COMPENSATION_DATES2' then
310 --
311 l_comp_dates2 := l_outputs(l_count).value;
312 --
313 Elsif l_outputs(l_count).name = 'COMPENSATION_DATES3' then
314 --
315 l_comp_dates3 := l_outputs(l_count).value;
316 --
317 Elsif l_outputs(l_count).name = 'VALUES' then
318 --
319 l_comp_values := l_outputs(l_count).value;
320 --
321 Elsif l_outputs(l_count).name = 'VALUES1' then
322 --
323 l_comp_values1 := l_outputs(l_count).value;
324 --
325 Elsif l_outputs(l_count).name = 'VALUES2' then
326 --
327 l_comp_values2 := l_outputs(l_count).value;
328 --
329 Elsif l_outputs(l_count).name = 'VALUES3' then
330 --
331 l_comp_values3 := l_outputs(l_count).value;
332 --
333 end if;
334 --
335 end loop;
336 -- vkodedal Bug 6992595 -- new changes
337 if(length(l_comp_dates1) > 0 and length(l_comp_values1) > 0 ) then
338 l_comp_dates :=l_comp_dates||';'||l_comp_dates1;
339 l_comp_values :=l_comp_values||';'||l_comp_values1;
340 hr_utility.set_location ('Dates from Additional variable 1 appended.',17);
341 end if;
342
343 if(length(l_comp_dates2) > 0 and length(l_comp_values2) > 0 ) then
344 l_comp_dates :=l_comp_dates||';'||l_comp_dates2;
345 l_comp_values :=l_comp_values||';'||l_comp_values2;
346 hr_utility.set_location ('Dates from Additional variable 2 appended.',18);
347 end if;
348
349 if(length(l_comp_dates3) > 0 and length(l_comp_values3) > 0 ) then
350 l_comp_dates :=l_comp_dates||';'||l_comp_dates3;
351 l_comp_values :=l_comp_values||';'||l_comp_values3;
352 hr_utility.set_location ('Dates from Additional variable 3 appended.',19);
353 end if;
354 ---------------------end of new changes
355 if(length(l_comp_dates) > 0 and length(l_comp_values) > 0 ) then
356 loop
357 j_dt := instr(l_comp_dates,';',1,k);
358 j_vl := instr(l_comp_values,';',1,k);
359 l_date := substr(l_comp_dates,i_dt,j_dt-i_dt);
360 l_value := substr(l_comp_values,i_vl,j_vl-i_vl);
361 if j_dt <> 0 then
362 i_dt := j_dt+1;
363 else
364 if i_dt < length(l_comp_dates) then
365 l_date := substr(l_comp_dates,i_dt);
366 end if;
367 i_dt := length(l_comp_dates) + 1;
368 end if;
369 if j_vl <> 0 then
370 i_vl := j_vl+1;
371 else --Bug#8451436 vkodedal 29-Apr-2009 added =
372 if i_vl <= length(l_comp_values) then
373 l_value := substr(l_comp_values,i_vl);
374 end if;
375 i_vl := length(l_comp_values) +1;
376 end if;
377 if z > 1 then
378 p_rule_table(z-1).end_date :=to_date(l_date,'yyyy/mm/dd')-1;
379 end if;
380
381 IF ( to_date(l_date,'yyyy/mm/dd') >= p_perd_start_date and to_date(l_date,'yyyy/mm/dd')<= p_perd_end_date ) THEN
382 p_rule_table(z).start_date :=to_date(l_date,'yyyy/mm/dd');
383 -- p_bnfts_table(i).end_date := bnfts_rec.effective_end_date;
384 p_rule_table(z).value := l_value;
385 p_rule_table(z).output_key := z;
386 if p_comp_typ_cd = 'MNTRY' then
387 p_rule_table(z).currency_cd := p_currency_cd;
388 elsif p_comp_typ_cd = 'NNMNTRY' then
389 p_rule_table(z).uom := p_uom;
390 end if;
391 z := z +1;
392 END IF;
393 hr_utility.set_location('value of l_date '||l_date, 30);
394 hr_utility.set_location('value of j_dt '||j_dt, 30);
395 hr_utility.set_location('new value of i_dt '||i_dt, 30);
396 hr_utility.set_location('value of l_value '||l_value, 30);
397 hr_utility.set_location('value of j_vl '||j_vl, 30);
398 hr_utility.set_location('new value of i_vl '||i_vl, 30);
399
400 /* write('value of l_date '||l_date);
401 write('value of j_dt '||j_dt);
402 write('new value of i_dt '||i_dt);
403 write('value of l_value '||l_value);
404 write('value of j_vl '||j_vl);
405 write('new value of i_vl '||i_vl); */
406 k := k+1;
407 /*WRITE (i_dt);
408 WRITE(length(l_comp_dates));*/
409 exit when i_vl > length(l_comp_values);
410 exit when i_dt > length(l_comp_dates);
411 end loop;
412 end if ;
413 hr_utility.set_location('Leaving '||l_proc,20);
414 exception
415 when others then
416 g_status := '5';
417 WRITE('error in date format:' || sqlerrm);
418 hr_utility.set_location('exception '||sqlerrm,20);
419 end rule_entries;
420
421 procedure thrd_pty_entries (p_assignment_id in number,
422 p_perd_start_date in date,
423 p_perd_end_date in date,
424 p_input_value_id in number,
425 p_comp_typ_cd in varchar2,
429 cursor thrd_pty_rows is
426 p_currency_cd in varchar2,
427 p_uom in varchar2,
428 p_thrd_pty_table out nocopy period_table) is
430 -- 08-Apr-2008 vkodedal 6945274 - currency issue -3rd party payroll
431 -- get the currency from element when it's not available from balance types table
432 select pr.processing_date period_start_date,
433 pr.processing_date period_end_date,
434 ba.run_amount amount ,
435 bt.uom,
436 bt.currency,
437 ba.balance_amount_id,
438 pe.input_currency_code,
439 pi.uom input_value_uom,
440 bt.displayed_name
441 from
442 per_bf_balance_types bt,
443 per_bf_processed_assignments pa,
444 per_bf_balance_amounts ba,
445 per_bf_payroll_runs pr,
446 pay_element_types_f pe,
447 pay_input_values_f pi
448 where
449 pa.assignment_id = p_assignment_id
450 and pa.payroll_run_id = pr.payroll_run_id
451 and bt.input_value_id = p_input_value_id
452 and ba.balance_type_id = bt.balance_type_id
453 and ba.processed_assignment_id = pa.processed_assignment_id
454 and pr.processing_date between p_perd_start_date and p_perd_end_date
455 and ba.run_amount is not null
456 and pi.input_value_id = bt.input_value_id(+)
457 and pe.element_type_id = pi.element_type_id(+)
458 and trunc(sysdate) between pe.EFFECTIVE_START_DATE(+) and pe.EFFECTIVE_END_DATE(+)
459 and trunc(sysdate) between pi.EFFECTIVE_START_DATE(+) and pi.EFFECTIVE_END_DATE(+);
460
461 i integer := 1;
462 l_proc varchar2(1000) := g_package||'thrd_pty_entries';
463 begin
464 hr_utility.set_location('entering '||l_proc,10);
465 hr_utility.set_location('inside cost entries with iv'||p_input_value_id,10);
466 hr_utility.set_location('asg id'||p_assignment_id,11);
467 hr_utility.set_location('date range is '||to_char(p_perd_start_date,'dd/mm/yyyy')||' - '||to_char(p_perd_end_date,'dd/mm/yyyy'),12);
468 for thrd_pty_rec in thrd_pty_rows loop
469 p_thrd_pty_table(i).start_date := thrd_pty_rec.period_start_date;
470 p_thrd_pty_table(i).end_date := thrd_pty_rec.period_end_date;
471 p_thrd_pty_table(i).value := fnd_number.number_to_canonical(thrd_pty_rec.amount);
472
473 if p_comp_typ_cd = 'MNTRY' then
474 --- vkodedal 6945274 - currency issue -3rd party payroll
475 ---p_thrd_pty_table(i).currency_cd := nvl(p_currency_cd,thrd_pty_rec.currency);
476 p_thrd_pty_table(i).currency_cd := nvl(p_currency_cd,nvl(thrd_pty_rec.currency,thrd_pty_rec.input_currency_code));
477 hr_utility.set_location('Back feed : '||thrd_pty_rec.displayed_name||': Currency '||p_thrd_pty_table(i).currency_cd,15);
478
479 elsif p_comp_typ_cd = 'NNMNTRY' then
480 p_thrd_pty_table(i).uom := nvl(p_uom,nvl(thrd_pty_rec.uom,thrd_pty_rec.input_value_uom));
481 hr_utility.set_location('UOM '||p_thrd_pty_table(i).uom,16);
482 end if;
483 p_thrd_pty_table(i).output_key := thrd_pty_rec.balance_amount_id;
484 p_thrd_pty_table(i).actual_uom := thrd_pty_rec.uom;
485 i := i + 1;
486 end loop;
487 hr_utility.set_location('# of entries'||i,30);
488 hr_utility.set_location('Leaving '||l_proc,10);
489 exception
490 when others then
491 g_status := '4';
492 end thrd_pty_entries;
493
494 procedure cost_entries (p_assignment_id in number,
495 p_perd_start_date in date,
496 p_perd_end_date in date,
497 p_input_value_id in number,
498 p_comp_typ_cd in varchar2,
499 p_currency_cd in varchar2,
500 p_uom in varchar2,
501 p_cost_table out nocopy period_table) is
502
503 ---vkodedal 25-Jun-2009 query modified to fix performance issue bug#8438036
504 cursor cost_rows is
505 SELECT ppa.effective_date,
506 ppa.effective_date date_earned,
507 paa.assignment_id,
508 pivf.element_type_id,
509 pc.input_value_id,
510 pc.debit_or_credit,
511 pec.costing_debit_or_credit,
512 pc.costed_value,
513 petf.output_currency_code,
514 pc.cost_id
515 FROM
516 pay_assignment_actions paa,
517 pay_payroll_actions ppa,
518 pay_costs pc,
519 pay_run_results prr,
520 pay_input_values_f pivf,
521 pay_element_types_f petf,
522 pay_element_classifications pec
523 WHERE paa.payroll_action_id = ppa.payroll_action_id
524 AND paa.assignment_action_id = pc.assignment_action_id
525 --and paa.assignment_action_id = prr.assignment_action_id
526 and prr.RUN_RESULT_ID = pc.run_result_id
527 AND NVL (pc.distributed_input_value_id, pc.input_value_id) =
528 pivf.input_value_id
529 AND pivf.input_value_id = p_input_value_id
530 AND petf.element_type_id = pivf.element_type_id
531 AND ppa.action_type IN ('C', 'S')
532 AND pc.balance_or_cost = 'C'
533 AND pec.classification_id = petf.classification_id
534 AND ppa.effective_date BETWEEN pivf.effective_start_date
535 AND pivf.effective_end_date
536 AND ppa.effective_date BETWEEN petf.effective_start_date
537 AND petf.effective_end_date
538 AND paa.assignment_id = p_assignment_id
539 AND ppa.effective_date BETWEEN p_perd_start_date AND p_perd_end_date
540 AND pc.costed_value IS NOT NULL;
541
542 i integer := 0;
543 l_amt number;
544 l_proc varchar2(1000) := g_package||' cost_entries';
545 begin
546 hr_utility.set_location('entering '||l_proc,10);
547 hr_utility.set_location('inside cost entries with iv'||p_input_value_id,10);
548 hr_utility.set_location('asg id'||p_assignment_id,11);
552 p_cost_table(i).start_date := cost_rec.effective_date;
549 hr_utility.set_location('date range is '||to_char(p_perd_start_date,'dd/mm/yyyy')||' - '||to_char(p_perd_end_date,'dd/mm/yyyy'),12);
550 for cost_rec in cost_rows loop
551 i := i + 1;
553 p_cost_table(i).end_date := cost_rec.effective_date;
554 if ( cost_rec.debit_or_credit = 'D' AND cost_rec.costing_debit_or_credit = 'C' ) OR
555 ( cost_rec.debit_or_credit = 'C' AND cost_rec.costing_debit_or_credit = 'D') then
556 l_amt := -1 * cost_rec.costed_value;
557 else
558 l_amt := cost_rec.costed_value;
559 end if;
560 p_cost_table(i).value := fnd_number.number_to_canonical(l_amt);
561 if p_comp_typ_cd = 'MNTRY' then
562 p_cost_table(i).currency_cd := nvl(p_currency_cd,cost_rec.output_currency_code);
563 elsif p_comp_typ_cd = 'NNMNTRY' then
564 p_cost_table(i).uom := p_uom;
565 end if;
566
567 p_cost_table(i).output_key := cost_rec.cost_id;
568
569 end loop;
570 hr_utility.set_location('# of entries'||i,30);
571 hr_utility.set_location('Leaving '||l_proc,10);
572 exception
573 when others then
574 g_status := '2';
575 end cost_entries;
576 ----------------------------------------------------------------------
577 --vkodedal 7012521 Run Results ER
578 procedure run_results (p_assignment_id in number,
579 p_perd_start_date in date,
580 p_perd_end_date in date,
581 p_input_value_id in number,
582 p_comp_typ_cd in varchar2,
583 p_currency_cd in varchar2,
584 p_uom in varchar2,
585 p_result_table out nocopy period_table) is
586 cursor run_results is
587 SELECT ppa.effective_date
588 ,prrv.RESULT_VALUE
589 ,paaf.assignment_id
590 ,piv.element_type_id
591 ,prrv.input_value_id
592 ,prr.run_result_id
593 ,pet.output_currency_code
594 FROM per_all_assignments_f paaf,
595 pay_run_results prr,
596 Pay_run_result_values prrv,
597 pay_assignment_actions paa,
598 pay_payroll_actions ppa,
599 pay_input_values_f piv,
600 pay_element_types_f pet
601 WHERE paaf.assignment_id = paa.assignment_id
602 AND prr.assignment_action_id = paa.assignment_action_id
603 AND prr.run_result_id=prrv.run_result_id
604 AND paa.PAYROLL_ACTION_ID=ppa.PAYROLL_ACTION_ID
605 And piv.input_value_id = p_input_value_id
606 and piv.input_value_id=prrv.input_value_id
607 And pet.element_type_id = piv.element_type_id
608 AND ppa.action_type IN ('Q', 'R')
609 AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
610 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
611 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
612 AND paaf.assignment_id = p_assignment_id
613 AND ppa.effective_date BETWEEN p_perd_start_date AND p_perd_end_date;
614 i integer := 0;
615 l_proc varchar2(1000) := g_package||' run_results';
616 begin
617 hr_utility.set_location('entering '||l_proc,10);
618 hr_utility.set_location('inside run results with iv'||p_input_value_id,10);
619 hr_utility.set_location('asg id'||p_assignment_id,11);
620 hr_utility.set_location('date range is '||to_char(p_perd_start_date,'dd/mm/yyyy')||' - '||to_char(p_perd_end_date,'dd/mm/yyyy'),12);
621 for run_rec in run_results loop
622 i := i + 1;
623 p_result_table(i).start_date := run_rec.effective_date;
624 p_result_table(i).end_date := run_rec.effective_date;
625 p_result_table(i).value := fnd_number.number_to_canonical(run_rec.result_value);
626 if p_comp_typ_cd = 'MNTRY' then
627 p_result_table(i).currency_cd := nvl(p_currency_cd,run_rec.output_currency_code);
628 elsif p_comp_typ_cd = 'NNMNTRY' then
629 p_result_table(i).uom := p_uom;
630 end if;
631
632 p_result_table(i).output_key := run_rec.run_result_id;
633
634 end loop;
635 hr_utility.set_location('# of entries'||i,30);
636 hr_utility.set_location('Leaving '||l_proc,10);
637 exception
638 when others then
639 g_status := '7';
640 end run_results;
641 --------------------------------------------------------
642 procedure get_assignment_details( p_person_id in number,
643 p_effective_date in date,
644 p_assignment_id in number,
645 p_payroll_id out nocopy number,
646 p_salary_basis_id out nocopy number
647 ) is
648
649 cursor csr_assignment_details is
650 Select * from per_all_assignments_f
651 where person_id = p_person_id
652 and p_effective_date between effective_start_date and effective_end_date
653 and assignment_id = p_assignment_id ;
654
655 l_assignment_rec csr_assignment_details%rowtype;
656 l_proc varchar2(60) := g_package||'get_person_details';
657 Begin
658 hr_utility.set_location('Entering'||l_proc,10);
659
660 open csr_assignment_details;
661 fetch csr_assignment_details into l_assignment_rec;
662 if csr_assignment_details%notfound then
663 hr_utility.set_location('invalid Person id passed',10);
664
665 end if;
666 close csr_assignment_details;
667
668 p_payroll_id := l_assignment_rec.payroll_id;
669 p_salary_basis_id:= l_assignment_rec.pay_basis_id;
670 hr_utility.set_location('Leaving'||l_proc,10);
671 end get_assignment_details;
672
673
674 procedure get_element_details(p_item_key in number,
675 p_effective_date in date,
679 p_input_currency_code in out nocopy varchar2,
676 p_comp_type_cd in varchar2,
677 p_element_type_id out nocopy number,
678 p_processing_type out nocopy varchar2,
680 p_uom in out nocopy varchar2,
681 p_actual_uom out nocopy varchar2,
682 p_lookup_type out nocopy varchar2,
683 p_value_set_id out nocopy varchar2 ,
684 p_actual_currency_code out nocopy varchar2,
685 p_period_start_date in date ) is
686
687 cursor csr_element_details is
688 select inv.input_value_id input_value_id,
689 elm.element_type_id element_type_id,
690 elm.processing_type processing_type,
691 elm.INPUT_CURRENCY_CODE inp_cur_code,
692 inv.uom uom , inv.lookup_type ,inv.value_set_id
693 from pay_input_values_f inv, pay_element_types_f elm
694 where elm.element_type_id = inv.element_type_id
695 and inv.input_value_id = p_item_key
696 and inv.effective_start_date <= p_effective_date
697 and inv.effective_end_date >= p_period_start_date
698 and elm.effective_start_date <= p_effective_date
699 and elm.effective_end_date >= p_period_start_date ;
700 --and p_effective_date between inv.effective_start_date and inv.effective_end_date
701 --and p_effective_date between elm.effective_start_date and elm.effective_end_date;
702
703 l_element_rec csr_element_details%rowtype;
704 l_proc varchar2(60) := g_package||'get_element_details';
705 Begin
706
707 hr_utility.set_location('Entering'||l_proc,10);
708 hr_utility.set_location('date'||p_effective_date,1900);
709 hr_utility.set_location('item'||p_item_key,190);
710
711 open csr_element_details;
712 fetch csr_element_details into l_element_rec;
713 if csr_element_details%notfound then
714 g_status := '1A';
715 hr_utility.set_location('Wrong input_value_id passed for Element Entry ', 40);
716 end if;
717 close csr_element_details;
718
719 p_element_type_id := l_element_rec.element_type_id;
720 p_processing_type := l_element_rec.processing_type;
721 p_lookup_type := l_element_rec.lookup_type;
722 p_value_set_id := l_element_rec.value_set_id ;
723 p_actual_currency_code := l_element_rec.inp_cur_code;
724
725 if (p_comp_type_cd = 'MNTRY' and p_input_currency_code is null) then
726 p_input_currency_code := l_element_rec.inp_cur_code;
727 elsif (p_comp_type_cd = 'NNMNTRY' and p_uom is null) then
728 p_uom := l_element_rec.uom;
729 end if;
730
731 p_actual_uom := l_element_rec.uom;
732
733 hr_utility.set_location('p_actual_uom'||p_actual_uom,10);
734
735 hr_utility.set_location('Leaving'||l_proc,10);
736 end get_element_details;
737
738 procedure get_pay_periods( p_perd_st_dt in date,
739 p_perd_en_dt in date,
740 p_frequency in varchar2
741 ) is
742 period_start_date date;
743 period_end_Date date;
744 flag varchar2(1) := 'Y' ;
745 l_proc varchar2(60) := g_package||'get_pay_periods';
746 begin
747 hr_utility.set_location('Entering'||l_proc,10);
748 period_start_date := p_perd_st_dt;
749 period_end_date := p_perd_en_dt;
750 hr_utility.set_location('freq_cd'||p_frequency,10);
751 if p_frequency = 'ANN' then
752 while flag = 'Y'
753 loop
754 if months_between(p_perd_en_dt,period_start_date) < 12 then
755 flag := 'N';
756 Insert_period ( p_period_start_date => period_start_date,
757 p_period_end_date => p_perd_en_dt
758 );
759 else
760 period_end_date := add_months(period_start_date,12) - 1;
761 Insert_period ( p_period_start_date => period_start_date,
762 p_period_end_date => period_end_date
763 );
764 period_start_date := period_end_date + 1;
765 end if;
766 end loop;
767 elsif p_frequency = 'MON' then
768 while flag = 'Y'
769 loop
770 if months_between(p_perd_en_dt,period_start_date) < 1 then
771 flag := 'N';
772 Insert_period ( p_period_start_date => period_start_date,
773 p_period_end_date => p_perd_en_dt
774 );
775 else
776 period_end_date := add_months(period_start_date,1) - 1;
777 Insert_period ( p_period_start_date => period_start_date,
778 p_period_end_date => period_end_date
779 );
780 period_start_date := period_end_date + 1;
781 end if;
782 end loop;
783 elsif p_frequency = 'DA' then
784 while flag = 'Y'
785 loop
786 if ((p_perd_en_dt - period_start_date) < 1) then
787 flag := 'N';
788 Insert_period ( p_period_start_date => period_start_date,
789 p_period_end_date => p_perd_en_dt
790 );
791 else
792 period_end_date := period_start_date;
793 Insert_period ( p_period_start_date => period_start_date,
794 p_period_end_date => period_end_date
795 );
796 period_start_date := period_end_date + 1;
797 end if;
798 end loop;
799 end if;
800 hr_utility.set_location('Leaving'||l_proc,10);
801 end get_pay_periods;
802
803
804 procedure get_payroll_periods(p_assignment_id in number,
805 p_perd_st_dt in date,
806 p_perd_en_dt in date,
810 p_uom in varchar2,
807 p_value in varchar2,
808 p_output_key in number,
809 p_currency_cd in varchar2,
811 p_creator_type in varchar2,
812 p_actual_uom in varchar2,
813 p_salary_basis in varchar2 default 'N'
814 ) is
815
816 cursor csr_asg_payroll is
817 select distinct asg.payroll_id,
818 asg.effective_start_date,
819 asg.effective_end_date,
820 number_per_fiscal_year,
821 status.pay_system_status
822 from per_all_assignments_f asg,
823 pay_payrolls_f pay ,per_time_period_types period,PER_ASSIGNMENT_STATUS_TYPES status
824 where assignment_id = p_assignment_id
825 --vkodedal = is added to fix payroll period issue 07-Sep-07
826 and asg.effective_start_date <= p_perd_en_dt
827 and asg.effective_end_date >= p_perd_st_dt
828 and asg.payroll_id is not null
829 and pay.payroll_id = asg.payroll_id
830 and pay.effective_end_date = (select max(effective_end_date) from pay_payrolls_f where payroll_id =
831 asg.payroll_id )
832 and period.period_type = pay.period_type
833 AND nvl(status.business_group_id,asg.business_group_id) = asg.business_group_id
834 AND status.active_flag = 'Y'
835 AND asg.ASSIGNMENT_STATUS_TYPE_ID = status.ASSIGNMENT_STATUS_TYPE_ID
836 --vkodedal 20-Nov-2009 Bug#9082203
837 AND status.per_system_status IN ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'SUSP_ASSIGN')
838 --AND status.pay_system_status = 'P'
839 --vkodedal 21-Apr-2009 Bug#8446898
840 order by effective_start_date;
841
842 cursor csr_payroll_periods (p_pay_id number, p_psd date, p_ped date )is
843 Select * from PER_TIME_PERIODS_V
844 where payroll_id = p_pay_id
845 and not(start_date > p_ped)
846 and not(end_date < p_psd )
847 --vkodedal 03-Feb-2010 Bug#8446898
848 order by end_date;
849
850 l_payroll_periods csr_payroll_periods%rowtype;
851 l_asg_payroll csr_asg_payroll%rowtype;
852 l_start_date date;
853 l_end_date date;
854 l_next_row number;
855 l_proc varchar2(60) := g_package||'get_payroll_periods';
856 l_asg_pay varchar2(1) := 'N';
857 l_value varchar2(240);
858 begin
859 hr_utility.set_location('Entering'||l_proc,10);
860 hr_utility.set_location(l_proc||' '||p_perd_st_dt,10);
861 hr_utility.set_location(l_proc||' '||p_perd_en_dt,10);
862 hr_utility.set_location(l_proc||' '||p_value,10);
863 hr_utility.set_location('Entering'||l_proc,10);
864 open csr_asg_payroll;
865 loop
866 fetch csr_asg_payroll into l_asg_payroll;
867 exit when csr_asg_payroll%notfound;
868 l_asg_pay := 'Y';
869 ----26-Nov-2009 vkodedal ignore if Pay Status is D Bug#9082203
870 if l_asg_payroll.pay_system_status = 'P' then
871
872 if l_asg_payroll.effective_start_date < p_perd_st_dt then
873 l_start_date := p_perd_st_dt;
874 else
875 l_start_date := l_asg_payroll.effective_start_date;
876 end if;
877
878 if l_asg_payroll.effective_end_date > p_perd_en_dt then
879 l_end_date := p_perd_en_dt;
880 else
881 l_end_date := l_asg_payroll.effective_end_date;
882 end if;
883 if (p_salary_basis = 'Y' ) then
884
885 l_value := fnd_number.number_to_canonical(fnd_number.canonical_to_number(p_value)/l_asg_payroll.number_per_fiscal_year );
886 else
887 l_value := p_value;
888 end if;
889 hr_utility.set_location('l_start_date '||l_start_date, 57);
890 hr_utility.set_location('l_end_date '||l_end_date, 57);
891 hr_utility.set_location('payroll_id '||l_asg_payroll.payroll_id, 57);
892 open csr_payroll_periods(l_asg_payroll.payroll_id, l_start_date, l_end_date);
893 loop
894 fetch csr_payroll_periods into l_payroll_periods;
895 exit when csr_payroll_periods%notfound;
896 if l_payroll_periods.end_date <= p_perd_en_dt then
897 if( NVL(g_period_table.LAST, 0) > 0 and l_payroll_periods.end_date = g_period_table(NVL(g_period_table.LAST, 0)).end_date) then
898 hr_utility.set_location('Entry already created for this period '||l_payroll_periods.end_date ,10);
899 else
900 -- if l_payroll_periods.end_date <= l_end_date then
901 hr_utility.set_location('into the if statment '||p_value,10);
902 l_next_row := NVL(g_period_table.LAST, 0) + 1;
903 g_period_table(l_next_row).start_date := l_payroll_periods.start_date;
904 g_period_table(l_next_row).end_date := l_payroll_periods.end_date;
905 g_period_table(l_next_row).value := l_value;
906 g_period_table(l_next_row).currency_cd := p_currency_cd;
907 g_period_table(l_next_row).uom := p_uom;
908 g_period_table(l_next_row).output_key := p_output_key;
909 g_period_table(l_next_row).creator_type := p_creator_type;
910 g_period_table(l_next_row).actual_uom := p_actual_uom;
911 end if;
912 end if;
913
914 end loop;
915 close csr_payroll_periods;
916
917 end if; --end of pay status ='P'
918 end loop;
919
920 if l_asg_pay = 'N' then
921 g_status := '1C';
922 hr_utility.set_location('No payroll attached to the assignment',10);
923 end if;
924
925 close csr_asg_payroll;
926 hr_utility.set_location('Leaving'||l_proc,10);
927 end get_payroll_periods;
928
929
930 procedure pay_details (p_elm_table in period_table,
931 p_source_key in number,
932 p_assignment_id in number,
933 p_effective_date in date,
934 p_perd_st_dt in date,
935 p_perd_en_dt in date,
936 p_currency_cd in varchar2,
937 p_uom in varchar2,
941 l_number_of_rows number;
938 p_actual_uom in varchar2
939 ) is
940
942 l_perd_amount number;
943 l_elm_rows number;
944 l_proc varchar2(60) := g_package||'pay_details';
945 l_esd date;
946 l_eed date;
947 l_pay_basis_id number;
948 l_rate_basis varchar2(2000);
949 l_pay_ann_factor number;
950 l_name varchar2(2000);
951 l_no_of_periods number;
952 l_value number;
953 l_next_row number;
954 l_salary_basis varchar2(1);
955
956 Begin
957 hr_utility.set_location('Entering'||l_proc,10);
958 l_number_of_rows := p_elm_table.count;
959 for ee_row in 1..l_number_of_rows
960 loop
961 if p_elm_table(ee_row).end_date > p_perd_en_dt then
962 l_eed := p_perd_en_dt;
963 else
964 l_eed := p_elm_table(ee_row).end_date;
965 end if;
966
967 if p_elm_table(ee_row).start_date < p_perd_st_dt then
968 l_esd := p_perd_st_dt;
969 else
970 l_esd := p_elm_table(ee_row).start_date;
971 end if;
972
973 hr_utility.set_location('value'||p_elm_table(ee_row).value, 40);
974 hr_utility.set_location('stend1'||l_eed, 40);
975 hr_utility.set_location('start1'||l_esd, 40);
976 if p_elm_table(ee_row).creator_type = 'SP' then
977 /* Get the pay basis id, pay annulization factor and rate basis from
978 per pay bases depending on input_value_id */
979 hr_utility.set_location('Creator for element_entry is salary proposal', 40);
980 get_salary_basis_details ( p_input_value_id => p_source_key,
981 p_effective_date => p_effective_date,
982 p_period_start_date => p_perd_st_dt ,
983 p_assignment_id => p_assignment_id,
984 p_pay_basis_id => l_pay_basis_id,
985 p_rate_basis => l_rate_basis,
986 p_PAY_ANN_FACTOR => l_pay_ann_factor,
987 p_name => l_name
988 );
989
990 if g_status = '0' then
991 if l_pay_ann_factor is not null then
992 hr_utility.set_location('Annulization factor is not null', 30);
993 l_salary_basis := 'Y';
994 if l_pay_ann_factor <= 0 then
995 l_pay_ann_factor := 1;
996 end if;
997 IF l_rate_basis = 'HOURLY' THEN
998 --Bug 7537970 - pass element entry start date not period start date to get fte factor
999 l_pay_ann_factor := l_pay_ann_factor *PER_SALADMIN_UTILITY.get_fte_factor(p_assignment_id,l_esd);
1000 END If;
1001 l_value := l_pay_ann_factor * fnd_number.canonical_to_number(p_elm_table(ee_row).value);
1002 else
1003 l_value := p_elm_table(ee_row).value;
1004 l_salary_basis := 'N';
1005 end if ;
1006 hr_utility.set_location('Annulization factor is null', 30);
1007 get_payroll_periods(p_assignment_id => p_assignment_id,
1008 p_perd_st_dt => l_esd,
1009 p_perd_en_dt => l_eed,
1010 p_value => fnd_number.number_to_canonical(l_value),
1011 p_output_key => p_elm_table(ee_row).output_key,
1012 p_currency_cd => p_currency_cd,
1013 p_uom => p_uom,
1014 p_creator_type => p_elm_table(ee_row).creator_type,
1015 p_actual_uom => p_actual_uom,
1016 p_salary_basis => l_salary_basis
1017 );
1018
1019 -- end if;
1020 end if;
1021
1022 else
1023 get_payroll_periods(p_assignment_id => p_assignment_id,
1024 p_perd_st_dt => l_esd,
1025 p_perd_en_dt => l_eed,
1026 p_value => p_elm_table(ee_row).value,
1027 p_output_key => p_elm_table(ee_row).output_key,
1028 p_currency_cd => p_currency_cd,
1029 p_uom => p_uom,
1030 p_creator_type => p_elm_table(ee_row).creator_type,
1031 p_actual_uom => p_actual_uom
1032 );
1033 end if;
1034
1035 end loop;
1036
1037 hr_utility.set_location('Leaving'||l_proc,10);
1038
1039 End pay_details;
1040
1041 procedure populate_result_table(p_elm_table in period_table,
1042 p_currency_cd in varchar2,
1043 p_uom in varchar2,
1044 p_actual_uom in varchar2
1045 ) is
1046 l_number_of_rows number;
1047 l_next_row number;
1048 Begin
1049 l_number_of_rows := p_elm_table.count;
1050 hr_utility.set_location('number of rows in p_elm_table '||p_elm_table.count,46);
1051 for ee_row in 1..l_number_of_rows
1052 loop
1053
1054 l_next_row := NVL(g_period_table.LAST, 0) + 1;
1055 g_period_table(l_next_row).start_date := p_elm_table(ee_row).start_date;
1056 g_period_table(l_next_row).end_date := p_elm_table(ee_row).end_date;
1057 g_period_table(l_next_row).value := p_elm_table(ee_row).value;
1058 g_period_table(l_next_row).currency_cd := p_currency_cd;
1059 g_period_table(l_next_row).uom := p_uom;
1060 g_period_table(l_next_row).actual_uom := p_actual_uom;
1061 g_period_table(l_next_row).output_key := p_elm_table(ee_row).output_key;
1065 end populate_result_table;
1062 g_period_table(l_next_row).creator_type := p_elm_table(ee_row).creator_type;
1063
1064 end loop;
1066
1067 procedure get_value_for_item(p_source_cd in varchar2,
1068 p_source_key in varchar2,
1069 p_perd_st_dt in date,
1070 p_perd_en_dt in date,
1071 p_person_id in number,
1072 p_assignment_id in number,
1073 p_effective_date in date,
1074 p_comp_typ_cd in varchar2,
1075 p_currency_cd in varchar2,
1076 p_uom in varchar2,
1077 p_result out nocopy period_table,
1078 p_status out nocopy varchar2) is
1079
1080 /* cursor csr_item is select * from ben_tcs_item
1081 where item_id = p_comp_item_id; */
1082 -- l_item_rec csr_item%rowtype;
1083
1084 l_element_id number;
1085 l_input_value_id number;
1086 l_proc_type varchar2(30);
1087 l_lookup_type varchar2(240);
1088 l_value_set_id varchar2(240);
1089 l_actual_currency_code varchar2(30);
1090 l_el_curr_cd pay_element_types_f.input_currency_code%type;
1091 l_summary period_table;
1092 l_uom pay_input_values_f.uom%type;
1093 l_proc varchar2(60) := g_package||'get_value_for_item';
1094 l_actual_uom pay_input_values_f.uom%type;
1095
1096 begin
1097 hr_utility.set_location('Entering'||l_proc,10);
1098 hr_utility.set_location('For Person '||p_person_id,499);
1099 hr_utility.set_location('For Assignment id'||p_assignment_id,499);
1100 hr_utility.set_location('For p_perd_en_dt '||p_perd_en_dt,499);
1101
1102 g_status := '0';
1103 l_el_curr_cd := p_currency_cd;
1104 l_uom := p_uom;
1105 if p_source_cd = 'EE' then
1106
1107 hr_utility.set_location('source is element entry',20);
1108
1109 get_element_details(p_item_key => p_source_key,
1110 p_effective_date => p_effective_date,
1111 p_comp_type_cd => p_comp_typ_cd,
1112 p_element_type_id => l_element_id,
1113 p_processing_type => l_proc_type,
1114 p_input_currency_code => l_el_curr_cd,
1115 p_uom => l_uom,
1116 p_actual_uom => l_actual_uom,
1117 p_lookup_type => l_lookup_type ,
1118 p_value_set_id => l_value_set_id ,
1119 p_actual_currency_code=> l_actual_currency_code,
1120 p_period_start_date => p_perd_st_dt
1121 );
1122
1123
1124 hr_utility.set_location('element processing type is '||l_proc_type,31);
1125 hr_utility.set_location('element uom is '||l_uom,31);
1126 hr_utility.set_location('element input currency code is '||l_el_curr_cd,31);
1127
1128 IF (g_status = '1A' )THEN
1129 WRITE(' The Input Value is not valid as of the Period End Date');
1130 hr_utility.set_location('1a' ,99);
1131 END IF;
1132
1133
1134 if g_status = '0' then
1135 delete_rows;
1136 element_entry(p_assignment_id => p_assignment_id,
1137 p_element_type_id => l_element_id,
1138 p_input_value_id => p_source_key,
1139 p_perd_st_dt => p_perd_st_dt,
1140 p_perd_en_dt => p_perd_en_dt,
1141 p_summary => l_summary,
1142 p_actual_uom => l_actual_uom,
1143 p_lookup_type => l_lookup_type,
1144 p_value_set_id => l_value_set_id ,
1145 p_actual_currency_code=> l_actual_currency_code );
1146
1147 hr_utility.set_location('summary table count '||l_summary.count, 30);
1148 hr_utility.set_location('l_proc_type '||l_proc_type, 30);
1149 hr_utility.set_location('l_uom '||l_uom, 30);
1150
1151 --commented for Bug#5098869
1152 -- if l_proc_type ='R' and l_uom is null then
1153 -- added for for Bug#5098869
1154
1155 if ( l_summary.count > 0) then
1156 if l_proc_type ='R' then
1157 hr_utility.set_location('Processing type is Recurring ', 30);
1158 hr_utility.set_location('st'||p_perd_st_dt ||'end'||p_perd_en_dt,40);
1159
1160 pay_details ( p_elm_table => l_summary,
1161 p_source_key => p_source_key,
1162 p_assignment_id => p_assignment_id,
1163 p_effective_date => p_effective_date,
1164 p_perd_st_dt => p_perd_st_dt,
1165 p_perd_en_dt => p_perd_en_dt,
1166 p_currency_cd => l_el_curr_cd,
1167 p_uom => l_uom,
1168 p_actual_uom => l_actual_uom
1169 );
1170 elsif l_proc_type ='N' then
1171
1172 hr_utility.set_location('Processing tye is N '||l_summary.count, 30);
1173
1174 populate_result_table(p_elm_table => l_summary,
1175 p_currency_cd => l_el_curr_cd,
1176 p_uom => l_uom,
1177 p_actual_uom => l_actual_uom
1178 );
1179 end if;
1180 end if;
1181
1182 if g_status = '0' then
1183 p_result := g_period_table;
1184 end if;
1185
1189
1186 end if;
1187
1188 elsif p_source_cd = 'PAYCOSTG' then
1190 hr_utility.set_location('source is Payroll Costing',20);
1191
1192 cost_entries (p_assignment_id => p_assignment_id,
1193 p_perd_start_date => p_perd_st_dt,
1194 p_perd_end_date => p_perd_en_dt,
1195 p_input_value_id => p_source_key,
1196 p_comp_typ_cd => p_comp_typ_cd,
1197 p_currency_cd => p_currency_cd,
1198 p_uom => p_uom,
1199 p_cost_table => p_result);
1200
1201 elsif p_source_cd = 'BB' then
1202
1203 hr_utility.set_location('source is Benefit balances',20);
1204
1205 bnfts_entries(p_person_id => p_person_id,
1206 p_perd_st_dt => p_perd_st_dt,
1207 p_perd_en_dt => p_perd_en_dt,
1208 p_input_value_id => p_source_key,
1209 p_comp_typ_cd => p_comp_typ_cd,
1210 p_currency_cd => p_currency_cd,
1211 p_uom => p_uom,
1212 p_bnfts_table => p_result
1213 );
1214
1215 elsif p_source_cd = 'THRDPTYPAY' then
1216
1217 hr_utility.set_location('source is Third Party payroll',20);
1218
1219 thrd_pty_entries(p_assignment_id => p_assignment_id,
1220 p_perd_start_date => p_perd_st_dt,
1221 p_perd_end_date => p_perd_en_dt,
1222 p_input_value_id => p_source_key,
1223 p_comp_typ_cd => p_comp_typ_cd,
1224 p_currency_cd => p_currency_cd,
1225 p_uom => p_uom,
1226 p_thrd_pty_table => p_result);
1227
1228 elsif p_source_cd = 'RULE' then
1229
1230 hr_utility.set_location('source is Rule',20);
1231
1232 rule_entries(p_assignment_id => p_assignment_id,
1236 p_input_value_id => p_source_key,
1233 p_perd_start_date => p_perd_st_dt,
1234 p_perd_end_date => p_perd_en_dt,
1235 p_date_earned => p_effective_date,
1237 p_comp_typ_cd => p_comp_typ_cd,
1238 p_currency_cd => p_currency_cd,
1239 p_uom => p_uom,
1240 p_rule_table => p_result);
1241 --VKODEDAL 7012521 RUN RESULT ER
1242 ----------------------------------------------------------------
1243 elsif p_source_cd = 'RR' then
1244
1245 hr_utility.set_location('source is Run Results',20);
1246
1247 run_results (p_assignment_id => p_assignment_id,
1248 p_perd_start_date => p_perd_st_dt,
1249 p_perd_end_date => p_perd_en_dt,
1250 p_input_value_id => p_source_key,
1251 p_comp_typ_cd => p_comp_typ_cd,
1252 p_currency_cd => p_currency_cd,
1253 p_uom => p_uom,
1254 p_result_table => p_result);
1255 -----------------------------------------------------------------------
1256 else
1257 hr_utility.set_location('Invalid Source Code '||p_source_cd, 200);
1258 g_status := '6';
1259
1260 end if;
1261
1262 IF ( g_status = '1A' ) THEN
1263 p_status := '0' ;
1264 ELSE
1265 p_status := g_status;
1266 END IF;
1267
1268 hr_utility.set_location('Leaving'||l_proc,10);
1269
1270 end get_value_for_item;
1271 end BEN_TCS_COMPENSATION;