[Home] [Help]
PACKAGE BODY: APPS.PAY_ADHOC_UTILS_PKG
Source
1 PACKAGE BODY PAY_ADHOC_UTILS_PKG AS
2 /* $Header: pyadcutl.pkb 120.3.12000000.1 2007/01/17 15:14:59 appldev noship $ */
3
4 g_package constant varchar2(33) := ' pay_adhoc_utils_pkg.';
5 --
6 --
7 PROCEDURE pupulate_input_name(p_element_entry_id number,
8 p_start_date date,
9 p_end_date date,
10 p_ele_start_date date,
11 p_ele_end_date date ) is
12 --
13 CURSOR c_input_name_value(cp_element_entry_id number,
14 cp_start_date date,
15 cp_end_date date,
16 cp_ele_start_date date,
17 cp_ele_end_date date) is
18 SELECT pivtl.name Name,
19 peev.screen_entry_value value ,
20 piv.lookup_type lookup_type,
21 piv.value_set_id ,
22 hr_bis.bis_decode_lookup('PROCESSING_TYPE',pet.PROCESSING_TYPE) Recurring,
23 pettl.element_name element_name,
24 pectl.classification_name classification
25 FROM pay_element_entries_f pee,
26 pay_element_types_f pet,
27 pay_element_types_f_tl pettl,
28 pay_element_entry_values_f peev,
29 pay_input_values_f piv,
30 pay_input_values_f_tl pivtl ,
31 pay_element_classifications pec,
32 pay_element_classifications_tl pectl
33 WHERE pet.element_type_id = pee.element_type_id
34 and pet.element_type_id = pettl.element_type_id
35 and pettl.language = userenv('LANG')
36 and pec.classification_id = pectl.classification_id
37 and pectl.language = userenv('LANG')
38 and pet.classification_id = pec.classification_id
39 AND piv.input_value_id = pivtl.input_value_id
40 AND pivtl.language = userenv('LANG')
41 AND peev.input_value_id = piv.input_value_id
42 AND pet.element_type_id = piv.element_type_id
43 AND pee.element_entry_id = peev.element_entry_id
44 AND pee.creator_type <> 'UT'
45 AND cp_start_date between pet.effective_start_date
46 and pet.effective_end_date
47 AND cp_start_date between piv.effective_start_date
48 and piv.effective_end_date
49 AND pee.effective_start_date = cp_ele_start_date
50 AND pee.effective_end_date = cp_ele_end_date
51 AND peev.effective_start_date = cp_ele_start_date
52 AND peev.effective_end_date = cp_ele_end_date
53 AND pee.element_entry_id = cp_element_entry_id
54 ORDER BY piv.display_sequence ;
55 --
56 v_input_name_value c_input_name_value%rowtype;
57 v_index number ;
58 --
59 BEGIN
60 --
61 v_index := 1 ;
62 --
63 FOR v_input_name_value IN c_input_name_value(p_element_entry_id,
64 p_start_date,
65 p_end_date,
66 p_ele_start_date,
67 p_ele_end_date) LOOP
68 --
69 --
70 hr_utility.set_location('v_index '||v_index,30);
71 --
72 if v_index = 1 then
73 g_input_name_value_tab(v_index).v_element_name := v_input_name_value.element_name;
74 g_input_name_value_tab(v_index).v_classification := v_input_name_value.classification;
75 g_input_name_value_tab(v_index). v_recurring := v_input_name_value.recurring;
76 end if;
77 --
78 g_input_name_value_tab(v_index).v_input_name := v_input_name_value.name;
79 --
80 IF v_input_name_value.lookup_type IS NOT NULL THEN
81 g_input_name_value_tab(v_index).v_input_value := hr_bis.bis_decode_lookup
82 (v_input_name_value.lookup_type,v_input_name_value.value);
83 --
84 ELSIF v_input_name_value.value_set_id IS NOT NULL THEN
85 g_input_name_value_tab(v_index).v_input_value := pay_input_values_pkg.decode_vset_value
86 (v_input_name_value.value_set_id,v_input_name_value.value);
87 --
88 ELSE
89 g_input_name_value_tab(v_index).v_input_value := v_input_name_value.value;
90 --
91 END IF;
92 --
93 hr_utility.set_location('g_input_name_value_tab(v_index) '||
94 g_input_name_value_tab(v_index).v_input_name,40);
95 hr_utility.set_location('g_input_name_value_tab(v_index) '||
96 g_input_name_value_tab(v_index).v_input_value,50);
97 v_index := v_index + 1 ;
98 --
99 END LOOP;
100 --
101 FOR x IN v_index..15 LOOP
102 g_input_name_value_tab(x).v_input_name := null;
103 g_input_name_value_tab(x).v_input_value := null;
104 END LOOP;
105 --
106 --
107 g_element_entry_id := p_element_entry_id ;
108 g_effective_start_date := p_ele_start_date;
109 g_effective_end_date := p_ele_end_date ;
110 --
111 --
112 EXCEPTION
113 WHEN others THEN
114 NULL;
115 --
116 END pupulate_input_name;
117 --
118 --
119 FUNCTION decode_OPM_territory ( p_territory_code varchar2,
120 p_business_group_id number )
121 RETURN VARCHAR2
122 IS
123 --
124 l_territory_short_name fnd_territories_vl.territory_short_name%type;
125 l_proc constant varchar2(72) := g_package||'decode_OPM_territory';
126
127 cursor csr_territory is
128 select territory_short_name
129 from fnd_territories_vl
130 where territory_code = nvl(p_territory_code, hr_api.return_legislation_code(p_business_group_id));
131 --
132 BEGIN
133 --
134 hr_utility.set_location('Entering:'|| l_proc, 10);
135
136 open csr_territory;
137 fetch csr_territory into l_territory_short_name;
138 close csr_territory;
139
140 hr_utility.set_location('Leaving:'|| l_proc, 20);
141 return l_territory_short_name;
142 --
143 END decode_OPM_territory;
144 --
145 FUNCTION decode_currency_code ( p_currency_code varchar2 )
146 RETURN VARCHAR2
147 IS
148 --
149 l_currency_name fnd_currencies_vl.name%type;
150 l_proc constant varchar2(72) := g_package||'decode_currency_code';
151
152 cursor csr_currency is
153 select name
154 from fnd_currencies_vl
155 where currency_code = p_currency_code;
156 --
157 BEGIN
158 --
159 hr_utility.set_location('Entering:'|| l_proc, 10);
160 if p_currency_code is not null then
161
162 open csr_currency;
163 fetch csr_currency into l_currency_name;
164 close csr_currency;
165
166 end if;
167
168 hr_utility.set_location('Leaving:'|| l_proc, 20);
169 return l_currency_name;
170 --
171 END decode_currency_code;
172 --
173 FUNCTION decode_event_group ( p_event_group_id varchar2 )
174 RETURN VARCHAR2
175 IS
176 --
177 l_event_group_name pay_event_groups.event_group_name%type;
178 l_proc constant varchar2(72) := g_package||'decode_event_group';
179
180 cursor csr_event_group is
181 select event_group_name
182 from pay_event_groups
183 where event_group_id = p_event_group_id;
184 --
185 BEGIN
186 --
187 hr_utility.set_location('Entering:'|| l_proc, 10);
188 if p_event_group_id is not null then
189
190 open csr_event_group;
191 fetch csr_event_group into l_event_group_name;
192 close csr_event_group;
193
194 end if;
195
196 hr_utility.set_location('Leaving:'|| l_proc, 20);
197 return l_event_group_name;
198 --
199 END decode_event_group;
200 --
201 FUNCTION get_element_link_status ( p_status varchar2,
202 p_link_start_date date,
203 p_link_end_date date,
204 p_effective_start_date date,
205 p_effective_end_date date,
206 p_effective_date date
207 )
208 RETURN VARCHAR2
209 IS
210 --
211 l_active hr_lookups.meaning%type;
212 l_inactive hr_lookups.meaning%type;
213 l_proc constant varchar2(72) := g_package||'get_element_link_status';
214 --
215 BEGIN
216 --
217 hr_utility.set_location('Entering:'|| l_proc, 10);
218 l_active := hr_bis.bis_decode_lookup( 'ACTIVE_INACTIVE', 'A');
219 l_inactive := hr_bis.bis_decode_lookup( 'ACTIVE_INACTIVE', 'I');
220
221 -- If status is null, both active and inactive records are displayed.
222 -- If the status is active then only active records as of the effective dates are displayed.
223 -- If the status is inactive then only inactive records as of the effective dates are displayed.
224 -- For Active records, row with effective date between effective start date
225 -- and effective end date is displayed.
226 -- For Inactive records, the first row is displayed since none of the rows have
227 -- effective date between effective start date and effective end date.
228
229 if p_status is null then
230 if p_effective_date between p_link_start_date and p_link_end_date then
231 if p_effective_date between p_effective_start_date and p_effective_end_date then
232 hr_utility.set_location(l_proc, 15);
233 return 'ACTIVE';
234 else
235 hr_utility.set_location(l_proc, 20);
236 return l_inactive;
237 end if;
238 else
239 if p_effective_start_date = p_link_start_date then
240 hr_utility.set_location(l_proc, 25);
241 return 'ACTIVE';
242 else
243 hr_utility.set_location(l_proc, 30);
244 return l_inactive;
245 end if;
246 end if;
247 elsif p_status = l_active then
248 if p_effective_date between p_effective_start_date and p_effective_end_date then
249 hr_utility.set_location(l_proc, 35);
250 return l_active;
251 else
252 hr_utility.set_location(l_proc, 40);
253 return l_inactive;
254 end if;
255 elsif p_status = l_inactive then
256 if p_effective_date not between p_link_start_date and p_link_end_date then
257 if p_effective_start_date = p_link_start_date then
258 hr_utility.set_location(l_proc, 45);
259 return l_inactive;
260 else
261 hr_utility.set_location(l_proc, 50);
262 return l_active;
263 end if;
264 else
265 hr_utility.set_location(l_proc, 55);
266 return l_active;
267 end if;
268 end if;
269
270 hr_utility.set_location('Leaving:'|| l_proc, 60);
271 return l_active;
272 --
273 END get_element_link_status;
274 --
275 FUNCTION decode_element_type ( p_element_type_id varchar2,
276 p_effective_date date )
277 RETURN VARCHAR2
278 IS
279 --
280 l_proc constant varchar2(72) := g_package||'decode_element_type';
281 l_element_name pay_element_types_f.element_name%type;
282
283 cursor csr_element is
284 select pettl.element_name
285 from pay_element_types_f pet,
286 pay_element_types_f_tl pettl
287 where pet.element_type_id = pettl.element_type_id
288 and pettl.language = userenv('LANG')
289 and pet.element_type_id = p_element_type_id
290 and p_effective_date between pet.effective_start_date and pet.effective_end_date;
291 --
292 BEGIN
293 --
294 hr_utility.set_location('Entering:'|| l_proc, 10);
295 if p_element_type_id is not null then
296
297 open csr_element;
298 fetch csr_element into l_element_name;
299 close csr_element;
300
301 end if;
302
303 hr_utility.set_location('Leaving:'|| l_proc, 20);
304 return l_element_name;
305 --
306 END decode_element_type;
307 --
308 FUNCTION get_bank_details ( p_external_account_id in number )
309 RETURN VARCHAR2
310 IS
311 --
312 l_concat_string varchar2(2000);
313 l_proc constant varchar2(72) := g_package||'get_bank_details';
314
315 l_flex_num pay_external_accounts.id_flex_num%type;
316 l_segment1 pay_external_accounts.segment1%type;
317 l_segment2 pay_external_accounts.segment2%type;
318 l_segment3 pay_external_accounts.segment3%type;
319 l_segment4 pay_external_accounts.segment4%type;
320 l_segment5 pay_external_accounts.segment5%type;
321 l_segment6 pay_external_accounts.segment6%type;
322 l_segment7 pay_external_accounts.segment7%type;
323 l_segment8 pay_external_accounts.segment8%type;
324 l_segment9 pay_external_accounts.segment9%type;
325 l_segment10 pay_external_accounts.segment10%type;
326 l_segment11 pay_external_accounts.segment11%type;
327 l_segment12 pay_external_accounts.segment12%type;
328 l_segment13 pay_external_accounts.segment13%type;
329 l_segment14 pay_external_accounts.segment14%type;
330 l_segment15 pay_external_accounts.segment15%type;
331 l_segment16 pay_external_accounts.segment16%type;
332 l_segment17 pay_external_accounts.segment17%type;
333 l_segment18 pay_external_accounts.segment18%type;
334 l_segment19 pay_external_accounts.segment19%type;
335 l_segment20 pay_external_accounts.segment20%type;
336 l_segment21 pay_external_accounts.segment21%type;
337 l_segment22 pay_external_accounts.segment22%type;
338 l_segment23 pay_external_accounts.segment23%type;
339 l_segment24 pay_external_accounts.segment24%type;
340 l_segment25 pay_external_accounts.segment25%type;
341 l_segment26 pay_external_accounts.segment26%type;
342 l_segment27 pay_external_accounts.segment27%type;
343 l_segment28 pay_external_accounts.segment28%type;
344 l_segment29 pay_external_accounts.segment29%type;
345 l_segment30 pay_external_accounts.segment30%type;
346
347 cursor csr_flex_num is
348 select exa.id_flex_num,
349 exa.segment1,
350 exa.segment2,
351 exa.segment3,
352 exa.segment4,
353 exa.segment5,
354 exa.segment6,
355 exa.segment7,
356 exa.segment8,
357 exa.segment9,
358 exa.segment10,
359 exa.segment11,
360 exa.segment12,
361 exa.segment13,
362 exa.segment14,
363 exa.segment15,
364 exa.segment16,
365 exa.segment17,
366 exa.segment18,
367 exa.segment19,
368 exa.segment20,
369 exa.segment21,
370 exa.segment22,
371 exa.segment23,
372 exa.segment24,
373 exa.segment25,
374 exa.segment26,
375 exa.segment27,
376 exa.segment28,
377 exa.segment29,
378 exa.segment30
379 from pay_external_accounts exa
380 where exa.external_account_id = p_external_account_id;
381 --
382 BEGIN
383 --
384 hr_utility.set_location('Entering:'|| l_proc, 10);
385 if p_external_account_id is not null then
386
387 open csr_flex_num;
388 fetch csr_flex_num into l_flex_num,
389 l_segment1, l_segment2, l_segment3, l_segment4, l_segment5, l_segment6, l_segment7, l_segment8, l_segment9, l_segment10,
390 l_segment11, l_segment12, l_segment13, l_segment14, l_segment15, l_segment16, l_segment17, l_segment18, l_segment19, l_segment20,
391 l_segment21, l_segment22, l_segment23, l_segment24, l_segment25, l_segment26, l_segment27, l_segment28, l_segment29, l_segment30 ;
392 close csr_flex_num;
393
394 l_concat_string := PAY_ADHOC_UTILS_PKG.FLEX_CONCATENATED
395 ( 'PAY', 'BANK', l_flex_num , 'SEGMENT', 30, 'KEY',
396 l_segment1, l_segment2, l_segment3, l_segment4, l_segment5, l_segment6, l_segment7, l_segment8, l_segment9, l_segment10,
397 l_segment11, l_segment12, l_segment13, l_segment14, l_segment15, l_segment16, l_segment17, l_segment18, l_segment19, l_segment20,
398 l_segment21, l_segment22, l_segment23, l_segment24, l_segment25, l_segment26, l_segment27, l_segment28, l_segment29, l_segment30 );
399
400 end if;
401
402 hr_utility.set_location('Leaving:'|| l_proc, 20);
403 return l_concat_string;
404 --
405 END get_bank_details;
406 --
407 FUNCTION flex_concatenated (app_short_name in varchar2,
408 flex_name in varchar2,
409 flex_context_or_struct in varchar2,
410 column_name in varchar2,
411 no_of_columns in varchar2 default null,
412 flex_type in varchar2, -- 'DESCRIPTIVE' or 'KEY'
413 v1 in varchar2 default null,
414 v2 in varchar2 default null,
415 v3 in varchar2 default null,
416 v4 in varchar2 default null,
417 v5 in varchar2 default null,
418 v6 in varchar2 default null,
419 v7 in varchar2 default null,
420 v8 in varchar2 default null,
421 v9 in varchar2 default null,
422 v10 in varchar2 default null,
423 v11 in varchar2 default null,
424 v12 in varchar2 default null,
425 v13 in varchar2 default null,
426 v14 in varchar2 default null,
427 v15 in varchar2 default null,
428 v16 in varchar2 default null,
429 v17 in varchar2 default null,
430 v18 in varchar2 default null,
431 v19 in varchar2 default null,
432 v20 in varchar2 default null,
433 v21 in varchar2 default null,
434 v22 in varchar2 default null,
435 v23 in varchar2 default null,
436 v24 in varchar2 default null,
437 v25 in varchar2 default null,
438 v26 in varchar2 default null,
439 v27 in varchar2 default null,
440 v28 in varchar2 default null,
441 v29 in varchar2 default null,
442 v30 in varchar2 default null
443 ) return varchar2
444 is
445 --
446 l_proc constant varchar2(72) := g_package||'flex_concatenated';
447 l_delimiter varchar2(1);
448 l_disp_no number;
449 first_seg boolean;
450 l_concat_string varchar2(2000);
451 type segment_table is table of varchar2(60)
452 index by binary_integer;
453 segment segment_table;
454 --
455 cursor get_seg_order is
456 SELECT REPLACE(fs.APPLICATION_COLUMN_NAME,column_name,'')
457 FROM FND_ID_FLEX_SEGMENTS fs,
458 FND_APPLICATION fap
459 WHERE fs.id_flex_num = flex_context_or_struct
460 and fs.id_flex_code = flex_name
461 and fs.enabled_flag = 'Y'
462 and fs.application_id = fap.application_id
463 and fap.APPLICATION_SHORT_NAME = app_short_name
464 order by fs.SEGMENT_NUM;
465 --
466 procedure desc_flex_set_column_value(column_name in varchar2,
467 column_number in number,
468 column_value in varchar2,
469 total_columns in number) is
470 begin
471 if column_number <= total_columns then
472 fnd_flex_descval.set_column_value(column_name||column_number,column_value);
473 end if;
474 end desc_flex_set_column_value;
475 --
476 begin
477 hr_utility.set_location('Entering:'|| l_proc, 10);
478 if flex_type = 'DESCRIPTIVE' then
479 --
480 fnd_flex_descval.set_context_value(flex_context_or_struct);
481 desc_flex_set_column_value(column_name,1,v1,no_of_columns);
482 desc_flex_set_column_value(column_name,2,v2,no_of_columns);
483 desc_flex_set_column_value(column_name,3,v3,no_of_columns);
484 desc_flex_set_column_value(column_name,4,v4,no_of_columns);
485 desc_flex_set_column_value(column_name,5,v5,no_of_columns);
486 desc_flex_set_column_value(column_name,6,v6,no_of_columns);
487 desc_flex_set_column_value(column_name,7,v7,no_of_columns);
488 desc_flex_set_column_value(column_name,8,v8,no_of_columns);
489 desc_flex_set_column_value(column_name,9,v9,no_of_columns);
490 desc_flex_set_column_value(column_name,10,v10,no_of_columns);
491 desc_flex_set_column_value(column_name,11,v11,no_of_columns);
492 desc_flex_set_column_value(column_name,12,v12,no_of_columns);
493 desc_flex_set_column_value(column_name,13,v13,no_of_columns);
494 desc_flex_set_column_value(column_name,14,v14,no_of_columns);
495 desc_flex_set_column_value(column_name,15,v15,no_of_columns);
496 desc_flex_set_column_value(column_name,16,v16,no_of_columns);
497 desc_flex_set_column_value(column_name,17,v17,no_of_columns);
498 desc_flex_set_column_value(column_name,18,v18,no_of_columns);
499 desc_flex_set_column_value(column_name,19,v19,no_of_columns);
500 desc_flex_set_column_value(column_name,20,v20,no_of_columns);
501 desc_flex_set_column_value(column_name,21,v21,no_of_columns);
502 desc_flex_set_column_value(column_name,22,v22,no_of_columns);
503 desc_flex_set_column_value(column_name,23,v23,no_of_columns);
504 desc_flex_set_column_value(column_name,24,v24,no_of_columns);
505 desc_flex_set_column_value(column_name,25,v25,no_of_columns);
506 desc_flex_set_column_value(column_name,26,v26,no_of_columns);
507 desc_flex_set_column_value(column_name,27,v27,no_of_columns);
508 desc_flex_set_column_value(column_name,28,v28,no_of_columns);
509 desc_flex_set_column_value(column_name,29,v29,no_of_columns);
510 desc_flex_set_column_value(column_name,30,v30,no_of_columns);
511 --
512 if fnd_flex_descval.validate_desccols(appl_short_name => app_short_name,
513 desc_flex_name => flex_name) then
514 return (substrb(fnd_flex_descval.concatenated_values,length(flex_context_or_struct)+1));
515 else
516 return (FND_FLEX_DESCVAL.error_message);
517 end if;
518 --
519 end if;
520 --
521 if flex_type = 'KEY' then
522 --
523 segment(1) := v1;
524 segment(2) := v2;
525 segment(3) := v3;
526 segment(4) := v4;
527 segment(5) := v5;
528 segment(6) := v6;
529 segment(7) := v7;
530 segment(8) := v8;
531 segment(9) := v9;
532 segment(10) := v10;
533 segment(11) := v11;
534 segment(12) := v12;
535 segment(13) := v13;
536 segment(14) := v14;
537 segment(15) := v15;
538 segment(16) := v16;
539 segment(17) := v17;
540 segment(18) := v18;
541 segment(19) := v19;
542 segment(20) := v20;
543 segment(21) := v21;
544 segment(22) := v22;
545 segment(23) := v23;
546 segment(24) := v24;
547 segment(25) := v25;
548 segment(26) := v26;
549 segment(27) := v27;
550 segment(28) := v28;
551 segment(29) := v29;
552 segment(30) := v30;
553 --
554 l_delimiter := fnd_flex_ext.get_delimiter
555 (app_short_name
556 ,flex_name
557 ,flex_context_or_struct
558 );
559 --
560 first_seg := true;
561 open get_seg_order;
562 loop
563 fetch get_seg_order into l_disp_no;
564 exit when get_seg_order%NOTFOUND;
565
566 if first_seg = false then
567 l_concat_string := l_concat_string || l_delimiter;
568 else
569 first_seg := false;
570 end if;
571
572 if segment(l_disp_no) is not null then
573 l_concat_string := l_concat_string || segment(l_disp_no);
574 end if;
575 end loop;
576 close get_seg_order;
577 --
578 return l_concat_string;
579 --
580 end if;
581 --
582 hr_utility.set_location('Leaving:'|| l_proc, 20);
583 return null;
584 --
585 end FLEX_CONCATENATED;
586 --
587 --
588 FUNCTION get_prev_salary(p_assignment_id NUMBER,
589 p_start_date DATE,
590 p_end_date DATE,
591 p_sal_type VARCHAR2) RETURN NUMBER IS
592 --
593 --
594 CURSOR previous_pay(c_assignment_id NUMBER,
595 c_start_date DATE,
596 c_end_date DATE,
597 c_date DATE) IS
598 SELECT pro.proposed_salary_n
599 FROM per_pay_proposals pro
600 WHERE pro.assignment_id = c_assignment_id
601 AND pro.change_date =(SELECT max(pro2.change_date)
602 FROM per_pay_proposals pro2
603 WHERE pro2.assignment_id = c_assignment_id
604 AND pro2.change_date < c_date)
605 AND pro.change_date < c_date ;
606 --
607 ln_prev_sal NUMBER;
608 ld_date DATE;
609 --
610 BEGIN
611 IF p_sal_type = 'STARTING' THEN
612
613 ld_date := p_start_DATE;
614
615 OPEN previous_pay(p_assignment_id,
616 p_start_date,
617 p_end_date,
618 ld_date);
619 FETCH previous_pay INTO ln_prev_sal ;
620 CLOSE previous_pay;
621 --
622 ELSIF p_sal_type = 'ENDING' THEN
623 ld_date := p_end_date;
624
625 OPEN previous_pay(p_assignment_id,
626 p_start_date,
627 p_end_date,
628 ld_date);
629 FETCH previous_pay INTO ln_prev_sal ;
630 CLOSE previous_pay;
631 --
632 END IF;
633 --
634 RETURN(ln_prev_sal);
635 END get_prev_salary;
636 --
637 --
638 FUNCTION get_prev_sal_change_date(p_assignment_id NUMBER,
639 p_end_date DATE) RETURN DATE IS
640 --
641 --
642 CURSOR previous_pay_date(c_assignment_id NUMBER,
643 c_period_end_date DATE) IS
644 SELECT max(pro.change_date)
645 FROM per_pay_proposals pro
646 WHERE pro.assignment_id = c_assignment_id
647 AND pro.change_date < c_period_end_date;
648 --
649 ld_date DATE;
650 --
651 BEGIN
652
653 OPEN previous_pay_date(p_assignment_id,
654 p_end_date);
655 FETCH previous_pay_date INTO ld_date ;
656 CLOSE previous_pay_date;
657 --
658 RETURN(ld_date);
659 --
660 EXCEPTION
661 WHEN others THEN
662 RETURN(NULL);
663 END get_prev_sal_change_date;
664 --
665 --
666 FUNCTION get_multiple_sal_change_flag(p_assignment_id NUMBER,
667 p_start_date DATE,
668 p_end_date DATE) RETURN VARCHAR2 IS
669 --
670 v_count NUMBER ;
671 multiple_flag VARCHAR2(1);
672 --
673 BEGIN
674 SELECT count(*) INTO v_count
675 FROM per_pay_proposals
676 WHERE assignment_id = p_assignment_id
677 AND change_date between p_start_date and p_end_date;
678
679 IF v_count < 0 or v_count = 1 THEN
680 multiple_flag := 'N' ;
681
682 ELSIF v_count > 1 then
683 multiple_flag := 'Y' ;
684 END IF;
685
686 RETURN(multiple_flag);
687 --
688 END get_multiple_sal_change_flag;
689 --
690 --
691 FUNCTION get_input_name(p_element_entry_id number,
692 p_sequence number,
693 p_inputname_or_value varchar2,
694 p_start_date date,
695 p_end_date date,
696 p_ele_start_date date,
697 p_ele_end_date date ) return varchar2 is
698 BEGIN
699 hr_utility.set_location('g_element_entry_id '||g_element_entry_id,10);
700 hr_utility.set_location('p_element_entry_id '||p_element_entry_id,20);
701 --
702 IF g_element_entry_id = p_element_entry_id AND
703 g_effective_start_date = p_ele_start_date AND
704 g_effective_end_date = p_ele_end_date THEN
705 NULL;
706 hr_utility.set_location('p_element_entry_id if'||p_element_entry_id,30);
707 ELSE
708 pupulate_input_name(p_element_entry_id => p_element_entry_id,
709 p_start_date => p_start_date,
710 p_end_date => p_end_date,
711 p_ele_start_date => p_ele_start_date,
712 p_ele_end_date => p_ele_end_date);
713 hr_utility.set_location('p_element_entry_id else'||p_element_entry_id,40);
714 --
715 END IF;
716 --
717 IF g_element_entry_id = p_element_entry_id AND
718 p_inputname_or_value = 'NAME' THEN
719 RETURN(g_input_name_value_tab(p_sequence).v_input_name);
720 ELSIF
721 g_element_entry_id = p_element_entry_id AND
722 p_inputname_or_value = 'VALUE' THEN
723 RETURN(g_input_name_value_tab(p_sequence).v_input_value);
724 ELSIF
725 g_element_entry_id = p_element_entry_id AND
726 p_inputname_or_value = 'ELEMENT_NAME' THEN
727 RETURN(g_input_name_value_tab(p_sequence).v_element_name);
728 ELSIF
729 g_element_entry_id = p_element_entry_id AND
730 p_inputname_or_value = 'CLASSIFICATION' THEN
731 RETURN(g_input_name_value_tab(p_sequence).v_classification);
732 ELSIF
733 g_element_entry_id = p_element_entry_id AND
734 p_inputname_or_value = 'RECURRING' THEN
735 RETURN(g_input_name_value_tab(p_sequence).v_recurring);
736 END IF;
737 --
738 EXCEPTION
739 WHEN others THEN
740 RETURN(null);
741 END get_input_name;
742 --
743 --
744 FUNCTION check_assignment_in_set(p_assignmentset_name VARCHAR2,
745 p_assignment_id NUMBER,
746 p_business_group_id NUMBER,
747 p_payroll_id NUMBER)
748 RETURN VARCHAR2 IS
749 --
750 --Cursor to check the assignment exists in assignment set
751
752 CURSOR c_assignment_set(c_assignmentset_name VARCHAR2,
753 c_assignment_id NUMBER,
754 c_business_group_id NUMBER,
755 c_payroll_id NUMBER) IS
756
757 SELECT 'Y'
758 FROM hr_assignment_sets aset
759 WHERE aset.assignment_set_name = c_assignmentset_name
760 and nvl(aset.payroll_id,c_payroll_id) = c_payroll_id
761 and aset.business_group_id = c_business_group_id
762 and (not exists
763 (select 1
764 from hr_assignment_set_amendments hasa
765 where hasa.assignment_set_id = aset.assignment_set_id
766 and hasa.include_or_exclude = 'I')
767 or exists
768 (select 1
769 from hr_assignment_set_amendments hasa
770 where hasa.assignment_set_id = aset.assignment_set_id
771 and hasa.assignment_id = c_assignment_id
772 and hasa.include_or_exclude = 'I'))
773 and not exists
774 (select 1
775 from hr_assignment_set_amendments hasa
776 where hasa.assignment_set_id = aset.assignment_set_id
777 and hasa.assignment_id = c_assignment_id
778 and hasa.include_or_exclude = 'E') ;
779 --
780 --
781 v_value VARCHAR2(1);
782
783 BEGIN
784 --
785 IF p_assignmentset_name IS NULL THEN
786 RETURN 'Y';
787 END IF;
788 --
789 --
790 OPEN c_assignment_set(p_assignmentset_name,p_assignment_id,
791 p_business_group_id,p_payroll_id);
792 FETCH c_assignment_set INTO v_value;
793 CLOSE c_assignment_set ;
794 --
795 IF v_value ='Y' THEN
796 RETURN 'Y';
797 ELSE
798 RETURN 'N';
799 END IF;
800 --
801 EXCEPTION
802 WHEN OTHERS THEN
803 RETURN 'N';
804 END check_assignment_in_set;
805 --
806 --
807 FUNCTION check_balance_exists(p_defined_balance_id NUMBER,
808 p_business_group_id NUMBER,
809 p_attribute_name VARCHAR2)
810 RETURN VARCHAR2 IS
811 --
812 CURSOR check_balance_exists(c_defined_balance_id NUMBER,
813 c_business_group_id NUMBER,
814 c_attribute_name VARCHAR2) is
815 SELECT pba.defined_balance_id
816 FROM pay_balance_attributes pba,
817 pay_bal_attribute_definitions pbad
818 WHERE pba.attribute_id = pbad.attribute_id
819 AND pba.defined_balance_id = c_defined_balance_id
820 AND pbad.attribute_name = c_attribute_name
821 AND ((pba.business_group_id = c_business_group_id and pba.legislation_code is null) or
822 (pba.legislation_code = hr_bis.get_legislation_code and pba.business_group_id is null));
823
824 v_balance_exists VARCHAR2(1);
825 v_defined_balance_id NUMBER;
826
827 BEGIN
828
829 v_balance_exists := 'Y' ;
830
831 IF p_attribute_name is not null THEN
832 OPEN check_balance_exists(p_defined_balance_id,p_business_group_id,p_attribute_name);
833 FETCH check_balance_exists into v_defined_balance_id;
834 IF check_balance_exists%FOUND THEN
835 v_balance_exists := 'Y' ;
836 ELSE
837 v_balance_exists := 'N' ;
838 END IF;
839 CLOSE check_balance_exists;
840 END IF;
841
842 RETURN (v_balance_exists);
843 END check_balance_exists;
844 --
845 --
846 FUNCTION get_bal_valid_load_date(p_attribute_name varchar2,
847 p_balance_name varchar2,
848 p_business_group_id number,
849 p_database_item_suffix varchar2,
850 p_defined_balance_id number DEFAULT NULL)
851 RETURN DATE IS
852
853 --To get the balance load date from single defined_balance_id
854 CURSOR get_balance_date IS
855 SELECT pbv.balance_load_date
856 FROM pay_balance_validation pbv
857 WHERE pbv.business_group_id = p_business_group_id
858 AND pbv.defined_balance_id = p_defined_balance_id
859 AND pbv.run_balance_status = 'V' ;
860
861 --To get the balance load date when Attribute Name is passed
862
863 CURSOR get_attribute_bal_date(c_attribute_name VARCHAR2,
864 c_business_group_id NUMBER,
865 c_database_item_suffix VARCHAR2) IS
866 SELECT max(balance_load_date) balance_load_date
867 FROM pay_balance_validation pbv,
868 pay_balance_attributes pba,
869 pay_bal_attribute_definitions pbad,
870 pay_defined_balances pdb,
871 pay_balance_dimensions pbd
872 WHERE pbv.defined_balance_id = pdb.defined_balance_id
873 AND pdb.defined_balance_id = pba.defined_balance_id
874 AND pba.attribute_id = pbad.attribute_id
875 AND pdb.balance_dimension_id = pbd.balance_dimension_id
876 AND pbv.business_group_id = c_business_group_id
877 AND pbad.attribute_name = c_attribute_name
878 AND pbd.database_item_suffix = c_database_item_suffix
879 AND pbv.run_balance_status = 'V' ;
880
881 ld_balance_load_date DATE;
882
883 BEGIN
884 IF (p_balance_name IS NOT NULL AND p_attribute_name IS NOT NULL) OR
885 (p_balance_name IS NOT NULL AND p_attribute_name IS NULL) THEN
886 --
887 --
888 IF g_balance_name = p_balance_name THEN
889 hr_utility.set_location(' g_balance_name if '||g_balance_name,10);
890 RETURN(g_balance_load_date);
891 ELSE
892 OPEN get_balance_date ;
893 FETCH get_balance_date INTO ld_balance_load_date;
894 CLOSE get_balance_date;
895 --
896 g_balance_load_date := ld_balance_load_date;
897 g_balance_name := p_balance_name;
898 hr_utility.set_location(' g_balance_name else '||g_balance_name,20);
899 --
900 RETURN(ld_balance_load_date);
901 END IF;
902 --
903 ELSIF p_balance_name IS NULL AND p_attribute_name IS NOT NULL THEN
904 --
905 IF g_attribute_name = p_attribute_name THEN
906 hr_utility.set_location(' g_attribute_name if '||g_attribute_name,30);
907 RETURN(g_balance_load_date);
908 ELSE
909 OPEN get_attribute_bal_date(p_attribute_name,p_business_group_id,p_database_item_suffix) ;
910 FETCH get_attribute_bal_date INTO ld_balance_load_date;
911 CLOSE get_attribute_bal_date;
912 --
913 g_balance_load_date := ld_balance_load_date;
914 g_attribute_name := p_attribute_name ;
915 hr_utility.set_location(' g_attribute_name else '||g_attribute_name,40);
916 --
917 RETURN(ld_balance_load_date);
918 END IF;
919 ELSE
920 RETURN(null);
921 END IF;
922 END get_bal_valid_load_date;
923 --
924 --
925 FUNCTION chk_post_r11i RETURN VARCHAR2 is
926 --
927 cursor csr_r12_release is
928 select 'Y'
929 from FND_PRODUCT_INSTALLATIONS
930 where APPLICATION_ID = 800
931 and to_number(substr(PRODUCT_VERSION,1,2)) >= 12;
932 --
933 BEGIN
934 --
935 if g_post_r11i is null then
936 open csr_r12_release;
937 fetch csr_r12_release into g_post_r11i;
938 if csr_r12_release%notfound then
939 g_post_r11i := 'N';
940 end if;
941 close csr_r12_release;
942 end if;
943 --
944 return g_post_r11i;
945 --
946 END chk_post_r11i;
947 --
948 --
949 FUNCTION get_element_name(p_element_entry_id number,
950 p_retro_run_date date,
951 p_payroll_run_date date)
952 RETURN VARCHAR2 IS
953 --
954 l_proc constant varchar2(72) := g_package||'get_element_name';
955 l_element_name pay_element_types_f.element_name%type;
956 --
957 cursor csr_element is
958 select pettl.element_name
959 from pay_element_types_f pet,
960 pay_element_types_f_tl pettl,
961 pay_element_entries_f pee
962 where pet.element_type_id = pettl.element_type_id
963 and pettl.language = userenv('LANG')
964 and pet.element_type_id = pee.element_type_id
965 and pee.element_entry_id = p_element_entry_id
966 and p_retro_run_date between pet.effective_start_date and pet.effective_end_date
967 and p_payroll_run_date between pee.effective_start_date and pee.effective_end_date;
968 --
969 BEGIN
970 --
971 hr_utility.set_location('Entering:'|| l_proc, 10);
972 --
973 if p_element_entry_id is not null then
974 open csr_element;
975 fetch csr_element into l_element_name;
976 close csr_element;
977 end if;
978 --
979 hr_utility.set_location('Leaving:'|| l_proc, 20);
980 return l_element_name;
981 --
982 END get_element_name ;
983 --
984 --
985 END PAY_ADHOC_UTILS_PKG;