[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYWSQEE_PKG
Source
1 package body PAY_PAYWSQEE_PKG as
2 /* $Header: paywsqee.pkb 120.3 2011/03/11 04:31:06 bkeshary ship $ */
3 --
4 g_dummy number(1);
5 --------------------------------------------------------------------------------
6 procedure GET_INPUT_VALUE_DETAILS (
7 --
8 -- Returns the input value details for the element selected by an LOV
9 --
10 p_element_type_id number,
11 p_effective_date date,
12 p_input_value_id1 in out nocopy number,
13 p_input_value_id2 in out nocopy number,
14 p_input_value_id3 in out nocopy number,
15 p_input_value_id4 in out nocopy number,
16 p_input_value_id5 in out nocopy number,
17 p_input_value_id6 in out nocopy number,
18 p_input_value_id7 in out nocopy number,
19 p_input_value_id8 in out nocopy number,
20 p_input_value_id9 in out nocopy number,
21 p_input_value_id10 in out nocopy number,
22 p_input_value_id11 in out nocopy number,
23 p_input_value_id12 in out nocopy number,
24 p_input_value_id13 in out nocopy number,
25 p_input_value_id14 in out nocopy number,
26 p_input_value_id15 in out nocopy number,
27 p_name1 in out nocopy varchar2,
28 p_name2 in out nocopy varchar2,
29 p_name3 in out nocopy varchar2,
30 p_name4 in out nocopy varchar2,
31 p_name5 in out nocopy varchar2,
32 p_name6 in out nocopy varchar2,
33 p_name7 in out nocopy varchar2,
34 p_name8 in out nocopy varchar2,
35 p_name9 in out nocopy varchar2,
36 p_name10 in out nocopy varchar2,
37 p_name11 in out nocopy varchar2,
38 p_name12 in out nocopy varchar2,
39 p_name13 in out nocopy varchar2,
40 p_name14 in out nocopy varchar2,
41 p_name15 in out nocopy varchar2,
42 p_lookup_type1 in out nocopy varchar2,
43 p_lookup_type2 in out nocopy varchar2,
44 p_lookup_type3 in out nocopy varchar2,
45 p_lookup_type4 in out nocopy varchar2,
46 p_lookup_type5 in out nocopy varchar2,
47 p_lookup_type6 in out nocopy varchar2,
48 p_lookup_type7 in out nocopy varchar2,
49 p_lookup_type8 in out nocopy varchar2,
50 p_lookup_type9 in out nocopy varchar2,
51 p_lookup_type10 in out nocopy varchar2,
52 p_lookup_type11 in out nocopy varchar2,
53 p_lookup_type12 in out nocopy varchar2,
54 p_lookup_type13 in out nocopy varchar2,
55 p_lookup_type14 in out nocopy varchar2,
56 p_lookup_type15 in out nocopy varchar2,
57 -- UOM
58 p_uom1 in out nocopy varchar2,
59 p_uom2 in out nocopy varchar2,
60 p_uom3 in out nocopy varchar2,
61 p_uom4 in out nocopy varchar2,
62 p_uom5 in out nocopy varchar2,
63 p_uom6 in out nocopy varchar2,
64 p_uom7 in out nocopy varchar2,
65 p_uom8 in out nocopy varchar2,
66 p_uom9 in out nocopy varchar2,
67 p_uom10 in out nocopy varchar2,
68 p_uom11 in out nocopy varchar2,
69 p_uom12 in out nocopy varchar2,
70 p_uom13 in out nocopy varchar2,
71 p_uom14 in out nocopy varchar2,
72 p_uom15 in out nocopy varchar2,
73 -- Value Set Id
74 p_value_set_id1 in out nocopy number,
75 p_value_set_id2 in out nocopy number,
76 p_value_set_id3 in out nocopy number,
77 p_value_set_id4 in out nocopy number,
78 p_value_set_id5 in out nocopy number,
79 p_value_set_id6 in out nocopy number,
80 p_value_set_id7 in out nocopy number,
81 p_value_set_id8 in out nocopy number,
82 p_value_set_id9 in out nocopy number,
83 p_value_set_id10 in out nocopy number,
84 p_value_set_id11 in out nocopy number,
85 p_value_set_id12 in out nocopy number,
86 p_value_set_id13 in out nocopy number,
87 p_value_set_id14 in out nocopy number,
88 p_value_set_id15 in out nocopy number
89 ) is
90 --
91 cursor SET_OF_INPUT_VALUES is
92 --
93 select iv.input_value_id,
94 ivtl.name,
95 iv.lookup_type,
96 iv.uom,
97 iv.value_set_id
98 --
99 from pay_input_values_f iv,
100 pay_input_values_f_tl ivtl
101 --
102 where p_effective_date between iv.effective_start_date
103 and iv.effective_end_date
104 and iv.element_type_id = p_element_type_id
105 and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
106 and ivtl.LANGUAGE = userenv('LANG')
107 order by iv.display_sequence, iv.name;
108 --
109 input_value_number integer;
110 --
111 begin
112 --
113 -- First, nullify all the entry values to ensure that we overwrite any
114 -- previous fetches
115 --
116 p_input_value_id1 := null;
117 p_input_value_id2 := null;
118 p_input_value_id3 := null;
119 p_input_value_id4 := null;
120 p_input_value_id5 := null;
121 p_input_value_id6 := null;
122 p_input_value_id7 := null;
123 p_input_value_id8 := null;
124 p_input_value_id9 := null;
125 p_input_value_id10 := null;
126 p_input_value_id11 := null;
127 p_input_value_id12 := null;
128 p_input_value_id13 := null;
129 p_input_value_id14 := null;
130 p_input_value_id15 := null;
131
132 --
133 p_name1 := null;
134 p_name2 := null;
135 p_name3 := null;
136 p_name4 := null;
137 p_name5 := null;
138 p_name6 := null;
139 p_name7 := null;
140 p_name8 := null;
141 p_name9 := null;
142 p_name10 := null;
143 p_name11 := null;
144 p_name12 := null;
145 p_name13 := null;
146 p_name14 := null;
147 p_name15 := null;
148 --
149 p_lookup_type1 := null;
150 p_lookup_type2 := null;
151 p_lookup_type3 := null;
152 p_lookup_type4 := null;
153 p_lookup_type5 := null;
154 p_lookup_type6 := null;
155 p_lookup_type7 := null;
156 p_lookup_type8 := null;
157 p_lookup_type9 := null;
158 p_lookup_type10 := null;
159 p_lookup_type11 := null;
160 p_lookup_type12 := null;
161 p_lookup_type13 := null;
162 p_lookup_type14 := null;
163 p_lookup_type15 := null;
164 -- UOM
165 p_uom1 := null;
166 p_uom2 := null;
167 p_uom3 := null;
168 p_uom4 := null;
169 p_uom5 := null;
170 p_uom6 := null;
171 p_uom7 := null;
172 p_uom8 := null;
173 p_uom9 := null;
174 p_uom10 := null;
175 p_uom11 := null;
176 p_uom12 := null;
177 p_uom13 := null;
178 p_uom14 := null;
179 p_uom15 := null;
180 --
181 -- Value Set Id
182 p_value_set_id1 := NULL;
183 p_value_set_id2 := NULL;
184 p_value_set_id3 := NULL;
185 p_value_set_id4 := NULL;
186 p_value_set_id5 := NULL;
187 p_value_set_id6 := NULL;
188 p_value_set_id7 := NULL;
189 p_value_set_id8 := NULL;
190 p_value_set_id9 := NULL;
191 p_value_set_id10 := NULL;
192 p_value_set_id11 := NULL;
193 p_value_set_id12 := NULL;
194 p_value_set_id13 := NULL;
195 p_value_set_id14 := NULL;
196 p_value_set_id15 := NULL;
197 --
198 -- Fetch all the input values and their properties
199 --
200 for fetched_input_value in set_of_input_values LOOP
201 --
202 input_value_number := set_of_input_values%rowcount; -- loop index flag
203 --
204 -- Now we need to put the input value details into the right parameters
205 -- to pass back to the form; the comments within the action for
206 -- input_value_number = 1 also apply for all the others
207 --
208 if input_value_number = 1 then
209 --
210 -- assign the out parameters
211 --
212 p_input_value_id1 := fetched_input_value.input_value_id;
213 p_name1 := fetched_input_value.name;
214 p_lookup_type1 := fetched_input_value.lookup_type;
215 p_uom1 := fetched_input_value.uom;
216 p_value_set_id1 := fetched_input_value.value_set_id;
217 --
218 elsif input_value_number =2 then
219 --
220 p_input_value_id2 := fetched_input_value.input_value_id;
221 p_name2 := fetched_input_value.name;
222 p_lookup_type2 := fetched_input_value.lookup_type;
223 p_uom2 := fetched_input_value.uom;
224 p_value_set_id2 := fetched_input_value.value_set_id;
225 --
226 elsif input_value_number =3 then
227 --
228 p_input_value_id3 := fetched_input_value.input_value_id;
229 p_name3 := fetched_input_value.name;
230 p_lookup_type3 := fetched_input_value.lookup_type;
231 p_uom3 := fetched_input_value.uom;
232 p_value_set_id3 := fetched_input_value.value_set_id;
233 --
234 elsif input_value_number =4 then
235 --
236 p_input_value_id4 := fetched_input_value.input_value_id;
237 p_name4 := fetched_input_value.name;
238 p_lookup_type4 := fetched_input_value.lookup_type;
239 p_uom4 := fetched_input_value.uom;
240 p_value_set_id4 := fetched_input_value.value_set_id;
241 --
242 elsif input_value_number =5 then
243 --
244 p_input_value_id5 := fetched_input_value.input_value_id;
245 p_name5 := fetched_input_value.name;
246 p_lookup_type5 := fetched_input_value.lookup_type;
247 p_uom5 := fetched_input_value.uom;
248 p_value_set_id5 := fetched_input_value.value_set_id;
249 --
250 elsif input_value_number =6 then
251 --
252 p_input_value_id6 := fetched_input_value.input_value_id;
253 p_name6 := fetched_input_value.name;
254 p_lookup_type6 := fetched_input_value.lookup_type;
255 p_uom6 := fetched_input_value.uom;
256 p_value_set_id6 := fetched_input_value.value_set_id;
257 --
258 elsif input_value_number =7 then
259 --
260 p_input_value_id7 := fetched_input_value.input_value_id;
261 p_name7 := fetched_input_value.name;
262 p_lookup_type7 := fetched_input_value.lookup_type;
263 p_uom7 := fetched_input_value.uom;
264 p_value_set_id7 := fetched_input_value.value_set_id;
265 --
266 elsif input_value_number =8 then
267 --
268 p_input_value_id8 := fetched_input_value.input_value_id;
269 p_name8 := fetched_input_value.name;
270 p_lookup_type8 := fetched_input_value.lookup_type;
271 p_uom8 := fetched_input_value.uom;
272 p_value_set_id8 := fetched_input_value.value_set_id;
273 --
274 elsif input_value_number =9 then
275 --
276 p_input_value_id9 := fetched_input_value.input_value_id;
277 p_name9 := fetched_input_value.name;
278 p_lookup_type9 := fetched_input_value.lookup_type;
279 p_uom9 := fetched_input_value.uom;
280 p_value_set_id9 := fetched_input_value.value_set_id;
281 --
282 elsif input_value_number =10 then
283 --
284 p_input_value_id10 := fetched_input_value.input_value_id;
285 p_name10 := fetched_input_value.name;
286 p_lookup_type10 := fetched_input_value.lookup_type;
287 p_uom10 := fetched_input_value.uom;
288 p_value_set_id10 := fetched_input_value.value_set_id;
289 --
290 elsif input_value_number =11 then
291 --
292 p_input_value_id11 := fetched_input_value.input_value_id;
293 p_name11 := fetched_input_value.name;
294 p_lookup_type11 := fetched_input_value.lookup_type;
295 p_uom11 := fetched_input_value.uom;
296 p_value_set_id11 := fetched_input_value.value_set_id;
297 --
298 elsif input_value_number =12 then
299 --
300 p_input_value_id12 := fetched_input_value.input_value_id;
301 p_name12 := fetched_input_value.name;
302 p_lookup_type12 := fetched_input_value.lookup_type;
303 p_uom12 := fetched_input_value.uom;
304 p_value_set_id12 := fetched_input_value.value_set_id;
305 --
306 elsif input_value_number =13 then
307 --
308 p_input_value_id13 := fetched_input_value.input_value_id;
309 p_name13 := fetched_input_value.name;
310 p_lookup_type13 := fetched_input_value.lookup_type;
311 p_uom13 := fetched_input_value.uom;
312 p_value_set_id13 := fetched_input_value.value_set_id;
313 --
314 elsif input_value_number =14 then
315 --
316 p_input_value_id14 := fetched_input_value.input_value_id;
317 p_name14 := fetched_input_value.name;
318 p_lookup_type14 := fetched_input_value.lookup_type;
319 p_uom14 := fetched_input_value.uom;
320 p_value_set_id14 := fetched_input_value.value_set_id;
321 --
322 elsif input_value_number =15 then
323 --
324 p_input_value_id15 := fetched_input_value.input_value_id;
325 p_name15 := fetched_input_value.name;
326 p_lookup_type15 := fetched_input_value.lookup_type;
327 p_uom15 := fetched_input_value.uom;
328 p_value_set_id15 := fetched_input_value.value_set_id;
329 --
330 exit; -- stop looping after the fifteenth input value
331 --
332 end if;
333 --
334 end loop;
335 --
336 end get_input_value_details;
337 --------------------------------------------------------------------------------
338 --------------------------------------------------------------------------------
339 procedure GET_INPUT_VALUE_DETAILS (
340 --
341 -- Returns the input value details for the element selected by an LOV
342 --
343 p_element_type_id number,
344 p_effective_date date,
345 p_input_value_id1 in out nocopy number,
346 p_input_value_id2 in out nocopy number,
347 p_input_value_id3 in out nocopy number,
348 p_input_value_id4 in out nocopy number,
349 p_input_value_id5 in out nocopy number,
350 p_input_value_id6 in out nocopy number,
351 p_input_value_id7 in out nocopy number,
352 p_input_value_id8 in out nocopy number,
353 p_input_value_id9 in out nocopy number,
354 p_input_value_id10 in out nocopy number,
355 p_input_value_id11 in out nocopy number,
356 p_input_value_id12 in out nocopy number,
357 p_input_value_id13 in out nocopy number,
358 p_input_value_id14 in out nocopy number,
359 p_input_value_id15 in out nocopy number,
360 p_name1 in out nocopy varchar2,
361 p_name2 in out nocopy varchar2,
362 p_name3 in out nocopy varchar2,
363 p_name4 in out nocopy varchar2,
364 p_name5 in out nocopy varchar2,
365 p_name6 in out nocopy varchar2,
366 p_name7 in out nocopy varchar2,
367 p_name8 in out nocopy varchar2,
368 p_name9 in out nocopy varchar2,
369 p_name10 in out nocopy varchar2,
370 p_name11 in out nocopy varchar2,
371 p_name12 in out nocopy varchar2,
372 p_name13 in out nocopy varchar2,
373 p_name14 in out nocopy varchar2,
374 p_name15 in out nocopy varchar2,
375 p_lookup_type1 in out nocopy varchar2,
376 p_lookup_type2 in out nocopy varchar2,
377 p_lookup_type3 in out nocopy varchar2,
378 p_lookup_type4 in out nocopy varchar2,
379 p_lookup_type5 in out nocopy varchar2,
380 p_lookup_type6 in out nocopy varchar2,
381 p_lookup_type7 in out nocopy varchar2,
382 p_lookup_type8 in out nocopy varchar2,
383 p_lookup_type9 in out nocopy varchar2,
384 p_lookup_type10 in out nocopy varchar2,
385 p_lookup_type11 in out nocopy varchar2,
386 p_lookup_type12 in out nocopy varchar2,
387 p_lookup_type13 in out nocopy varchar2,
388 p_lookup_type14 in out nocopy varchar2,
389 p_lookup_type15 in out nocopy varchar2
390 ) is
391 --
392 cursor SET_OF_INPUT_VALUES is
393 --
394 select iv.input_value_id,
395 ivtl.name,
396 iv.lookup_type,
397 iv.uom
398 --
399 from pay_input_values_f iv,
400 pay_input_values_f_tl ivtl
401 --
402 where p_effective_date between iv.effective_start_date
403 and iv.effective_end_date
404 and iv.element_type_id = p_element_type_id
405 and ivtl.INPUT_VALUE_ID = iv.INPUT_VALUE_ID
406 and ivtl.LANGUAGE = userenv('LANG')
407 order by iv.display_sequence, iv.name;
408 --
409 input_value_number integer;
410 --
411 begin
412 --
413 -- First, nullify all the entry values to ensure that we overwrite any
414 -- previous fetches
415 --
416 p_input_value_id1 := null;
417 p_input_value_id2 := null;
418 p_input_value_id3 := null;
419 p_input_value_id4 := null;
420 p_input_value_id5 := null;
421 p_input_value_id6 := null;
422 p_input_value_id7 := null;
423 p_input_value_id8 := null;
424 p_input_value_id9 := null;
425 p_input_value_id10 := null;
426 p_input_value_id11 := null;
427 p_input_value_id12 := null;
428 p_input_value_id13 := null;
429 p_input_value_id14 := null;
430 p_input_value_id15 := null;
431
432 --
433 p_name1 := null;
434 p_name2 := null;
435 p_name3 := null;
436 p_name4 := null;
437 p_name5 := null;
438 p_name6 := null;
439 p_name7 := null;
440 p_name8 := null;
441 p_name9 := null;
442 p_name10 := null;
443 p_name11 := null;
444 p_name12 := null;
445 p_name13 := null;
446 p_name14 := null;
447 p_name15 := null;
448 --
449 p_lookup_type1 := null;
450 p_lookup_type2 := null;
451 p_lookup_type3 := null;
452 p_lookup_type4 := null;
453 p_lookup_type5 := null;
454 p_lookup_type6 := null;
455 p_lookup_type7 := null;
456 p_lookup_type8 := null;
457 p_lookup_type9 := null;
458 p_lookup_type10 := null;
459 p_lookup_type11 := null;
460 p_lookup_type12 := null;
461 p_lookup_type13 := null;
462 p_lookup_type14 := null;
463 p_lookup_type15 := null;
464 --
465 -- Fetch all the input values and their properties
466 --
467 for fetched_input_value in set_of_input_values LOOP
468 --
469 input_value_number := set_of_input_values%rowcount; -- loop index flag
470 --
471 -- Now we need to put the input value details into the right parameters
472 -- to pass back to the form; the comments within the action for
473 -- input_value_number = 1 also apply for all the others
474 --
475 if input_value_number = 1 then
476 --
477 -- assign the out parameters
478 --
479 p_input_value_id1 := fetched_input_value.input_value_id;
480 p_name1 := fetched_input_value.name;
481 p_lookup_type1 := fetched_input_value.lookup_type;
482 --
483 elsif input_value_number =2 then
484 --
485 p_input_value_id2 := fetched_input_value.input_value_id;
486 p_name2 := fetched_input_value.name;
487 p_lookup_type2 := fetched_input_value.lookup_type;
488 --
489 elsif input_value_number =3 then
490 --
491 p_input_value_id3 := fetched_input_value.input_value_id;
492 p_name3 := fetched_input_value.name;
493 p_lookup_type3 := fetched_input_value.lookup_type;
494
495 --
496 elsif input_value_number =4 then
497 --
498 p_input_value_id4 := fetched_input_value.input_value_id;
499 p_name4 := fetched_input_value.name;
500 p_lookup_type4 := fetched_input_value.lookup_type;
501 --
502 elsif input_value_number =5 then
503 --
504 p_input_value_id5 := fetched_input_value.input_value_id;
505 p_name5 := fetched_input_value.name;
506 p_lookup_type5 := fetched_input_value.lookup_type;
507 --
508 elsif input_value_number =6 then
509 --
510 p_input_value_id6 := fetched_input_value.input_value_id;
511 p_name6 := fetched_input_value.name;
512 p_lookup_type6 := fetched_input_value.lookup_type;
513 --
514 elsif input_value_number =7 then
515 --
516 p_input_value_id7 := fetched_input_value.input_value_id;
517 p_name7 := fetched_input_value.name;
518 p_lookup_type7 := fetched_input_value.lookup_type;
519 --
520 elsif input_value_number =8 then
521 --
522 p_input_value_id8 := fetched_input_value.input_value_id;
523 p_name8 := fetched_input_value.name;
524 p_lookup_type8 := fetched_input_value.lookup_type;
525 --
526 elsif input_value_number =9 then
527 --
528 p_input_value_id9 := fetched_input_value.input_value_id;
529 p_name9 := fetched_input_value.name;
530 p_lookup_type9 := fetched_input_value.lookup_type;
531 --
532 elsif input_value_number =10 then
533 --
534 p_input_value_id10 := fetched_input_value.input_value_id;
535 p_name10 := fetched_input_value.name;
536 p_lookup_type10 := fetched_input_value.lookup_type;
537 --
538 elsif input_value_number =11 then
539 --
540 p_input_value_id11 := fetched_input_value.input_value_id;
541 p_name11 := fetched_input_value.name;
542 p_lookup_type11 := fetched_input_value.lookup_type;
543 --
544 elsif input_value_number =12 then
545 --
546 p_input_value_id12 := fetched_input_value.input_value_id;
547 p_name12 := fetched_input_value.name;
548 p_lookup_type12 := fetched_input_value.lookup_type;
549 --
550 elsif input_value_number =13 then
551 --
552 p_input_value_id13 := fetched_input_value.input_value_id;
553 p_name13 := fetched_input_value.name;
554 p_lookup_type13 := fetched_input_value.lookup_type;
555 --
556 elsif input_value_number =14 then
557 --
558 p_input_value_id14 := fetched_input_value.input_value_id;
559 p_name14 := fetched_input_value.name;
560 p_lookup_type14 := fetched_input_value.lookup_type;
561 --
562 elsif input_value_number =15 then
563 --
564 p_input_value_id15 := fetched_input_value.input_value_id;
565 p_name15 := fetched_input_value.name;
566 p_lookup_type15 := fetched_input_value.lookup_type;
567 --
568 exit; -- stop looping after the fifteenth input value
569 --
570 end if;
571 --
572 end loop;
573 --
574 end get_input_value_details;
575
576 ----------
577 function paylink_request_id (
578 --
579 -- Starts paylink process via concurrent manager
580 -- Returns TRUE if the request was successfully submitted
581 --
582 p_business_group_id number,
583 p_mode varchar2,
584 p_batch_id number,
585 p_wait varchar2 default 'N',
586 p_act_parameter_group_id number default null) return number is
587 --
588 v_request_id number := 0;
589 v_pac_id pay_payroll_actions.payroll_action_id%TYPE;
590 v_batch_status pay_batch_headers.batch_status%TYPE := null;
591 --
592 l_wait_outcome BOOLEAN;
593 l_phase VARCHAR2(80);
594 l_status VARCHAR2(80);
595 l_dev_phase VARCHAR2(80);
596 l_dev_status VARCHAR2(80);
597 l_message VARCHAR2(80);
598 l_max_wait_sec number;
599 l_interval_wait_sec number;
600 l_default_parameter_group_id number(9);
601 --
602
603 cursor csr_pay_acts is
604 select pac.payroll_action_id
605 from pay_payroll_actions pac
606 where pac.action_type = 'BEE'
607 and pac.batch_id = p_batch_id
608 and pac.batch_process_mode = 'TRANSFER'
609 order by pac.payroll_action_id;
610 --
611 --
612 cursor cur_max is
613 select fnd_number.canonical_to_number(parameter_value)
614 from pay_action_parameters
615 where parameter_name = 'BEE_MAX_WAIT_SEC';
616 --
617 cursor cur_intw is
618 select fnd_number.canonical_to_number(parameter_value)
619 from pay_action_parameters
620 where parameter_name = 'BEE_INTERVAL_WAIT_SEC';
621 --
622 --
623 function get_default_action_param
624 return VARCHAR2 is
625
626 Cursor csr_get_parameter_id(r_action_parameter_group_name varchar2) is
627 select to_char(action_parameter_group_id)
628 from pay_action_parameter_groups
629 where action_parameter_group_name=r_action_parameter_group_name;
630
631 Cursor csr_get_cp_defaults is
632 select default_type,default_value
633 from FND_DESCR_FLEX_COLUMN_USAGES
634 where descriptive_flexfield_name='$SRS$.PAYLINK'
635 and end_user_column_name='Action Parameter Group';
636 l_cp_defaults csr_get_cp_defaults%rowtype;
637 l_return varchar2(2000);
638
639 begin
640 open csr_get_cp_defaults;
641 fetch csr_get_cp_defaults into l_cp_defaults;
642 close csr_get_cp_defaults;
643
644 if l_cp_defaults.default_type = 'S'
645 and instr(l_cp_defaults.default_value,':') < 1
646 then --instr to ignore defaults with block references
647
648 execute immediate l_cp_defaults.default_value into l_return;
649
650 elsif l_cp_defaults.default_type='C' then
651
652 open csr_get_parameter_id(l_cp_defaults.default_value);
653 fetch csr_get_parameter_id into l_return;
654 close csr_get_parameter_id;
655
656 elsif l_cp_defaults.default_type = 'P' then
657
658 l_return := fnd_profile.value(l_cp_defaults.default_value);
659
660 end if;
661
662 --if there is an error or default type is not in S,C,P return null value
663 --,so that action parameter group is picked from profile
664
665 return to_number(l_return);
666
667 exception
668 when others then --ignore errors and pick up from profile
669 return null;
670 end;
671 begin
672 --
673 v_batch_status := batch_overall_status(p_batch_id);
674 --
675 -- IF the batch is already transferred then only allow purge.
676 -- IF the batch is partially transferred then allow transfer and purge.
677 -- IF the batch has status mismatch then don't submit anything.
678 if (v_batch_status = 'ST' and p_mode in ('VALIDATE')) or
679 (v_batch_status = 'T' and p_mode in ('VALIDATE','TRANSFER')) or
680 (v_batch_status = 'P') or
681 (v_batch_status = 'SM') then
682 return (null);
683 end if;
684 --
685 open csr_pay_acts;
686 fetch csr_pay_acts into v_pac_id;
687 close csr_pay_acts;
688 --
689 if p_mode = 'PURGE' then
690 --
691 v_request_id := fnd_request.submit_request (
692 --
693 'PER',
694 'PAYLINK(PURGE)',
695 null,
696 null,
697 null,
698 p_business_group_id,
699 'PURGE',
700 p_batch_id);
701 --
702 elsif p_mode = 'ROLLBACK' and v_pac_id is not null then
703 v_request_id := fnd_request.submit_request (
704 --
705 'PAY',
706 'ROLLBACK',
707 null,
708 null,
709 null,
710 'ROLLBACK',
711 v_pac_id,
712 null);
713 --
714 elsif p_mode = 'TRANSFER' and v_pac_id is not null then
715 v_request_id := fnd_request.submit_request (
716 --
717 'PAY',
718 'RETRY',
719 null,
720 null,
721 null,
722 'RERUN',
723 v_pac_id);
724 else
725
726 --5718633 115.27 try to pick action_parameter_group from
727 --the default value attached to Action Parameter Group in BEE CP
728
729 l_default_parameter_group_id := get_default_action_param;
730
731 if l_default_parameter_group_id is null
732 then
733 l_default_parameter_group_id := p_act_parameter_group_id ;
734 end if;
735 v_request_id := fnd_request.submit_request (
736 --
737 'PER',
738 'PAYLINK',
739 null,
740 null,
741 null,
742 'BATCHEE',
743 p_mode,
744 p_batch_id,
745 l_default_parameter_group_id);
746 end if;
747
748 if ( v_request_id <> 0 ) then
749 commit;
750 end if;
751 --
752 if p_wait = 'Y' and v_request_id <> 0 then
753 -- Attempt to find out the BEE Concurrent manager max wait time
754 -- and polling interval time from pay_action_parameters. If values
755 -- cannot be found in this table then default to a max wait of 600
756 -- seconds and polling interval of 2 seconds.
757 --
758 open cur_max;
759 fetch cur_max into l_max_wait_sec;
760 if cur_max %notfound then
761 close cur_max;
762 -- Value not in table, set to the default
763 l_max_wait_sec := 600;
764 else
765 close cur_max;
766 end if;
767 --
768 open cur_intw;
769 fetch cur_intw into l_interval_wait_sec;
770 if cur_intw %notfound then
771 close cur_intw;
772 -- Value not in table, set to the default
773 l_interval_wait_sec := 2;
774 else
775 close cur_intw;
776 end if;
777 --
778 -- Waits for request to finish on the concurrent manager.
779 -- Or gives up if the maximum wait time is reached.
780 --
781 l_wait_outcome := fnd_concurrent.wait_for_request(
782 request_id => v_request_id,
783 interval => l_interval_wait_sec,
784 max_wait => l_max_wait_sec,
785 phase => l_phase,
786 status => l_status,
787 dev_phase => l_dev_phase,
788 dev_status => l_dev_status,
789 message => l_message);
790 end if;
791 --
792 return (v_request_id);
793 --
794 end paylink_request_id;
795 --------------------------------------------------------------------------------
796 function next_batch_sequence (p_batch_id number) return number is
797 --
798 -- Returns the next available batch sequence
799 -- to maintain a sequence of batch lines within a particular batch
800 --
801 v_batch_sequence number := null;
802 --
803 cursor csr_next_batch_sequence is
804 select nvl (max (batch_sequence), 0) +1
805 from pay_batch_lines
806 where batch_id = p_batch_id;
807 --
808 begin
809 --
810 open csr_next_batch_sequence;
811 fetch csr_next_batch_sequence into v_batch_sequence;
812 close csr_next_batch_sequence;
813 --
814 return v_batch_sequence;
815 --
816 end next_batch_sequence;
817 --------------------------------------------------------------------------------
818 function batch_overall_status (p_batch_id number) return varchar2 is
819 --
820 -- Derives the overall status of the batch header, control totals and lines
821 --
822 valid_lines_exist boolean := FALSE;
823 error_lines_exist boolean := FALSE;
824 unprocessed_lines_exist boolean := FALSE;
825 transferred_lines_exist boolean := FALSE;
826 header_transferred boolean := FALSE;
827 header_processing boolean := FALSE;
828 --
829 cursor csr_status is
830 select control_status STATUS
831 from pay_batch_control_totals
832 where batch_id = p_batch_id
833 union
834 select batch_line_status
835 from pay_batch_lines
836 where batch_id = p_batch_id
837 union
838 select batch_status
839 from pay_batch_headers
840 where batch_id = p_batch_id
841 union
842 select 'Y'
843 from pay_batch_headers bth
844 where bth.batch_id = p_batch_id
845 and bth.batch_status = 'T'
846 -- and not exists
847 -- (select null
848 -- from pay_batch_control_totals ctl
849 -- where ctl.batch_id = bth.batch_id
850 -- and ctl.control_status <> 'T')
851 order by 1 desc;
852 --
853 begin
854 --
855 for distinct_status in csr_status LOOP
856 --
857 if distinct_status.status = 'E' then
858 error_lines_exist := TRUE;
859 exit; -- we do not need to know the rest
860 --
861 elsif distinct_status.status = 'U' then
862 unprocessed_lines_exist := TRUE;
863 --
864 elsif distinct_status.status = 'T' then
865 transferred_lines_exist := TRUE;
866 --
867 elsif distinct_status.status = 'V' then
868 valid_lines_exist := TRUE;
869 --
870 elsif distinct_status.status = 'Y' then
871 header_transferred := TRUE;
872 --
873 elsif distinct_status.status = 'P' then
874 header_processing := TRUE;
875 --
876 end if;
877 --
878 -- we do not need to know the rest if it is the following case.
879 if (header_transferred and
880 (unprocessed_lines_exist or valid_lines_exist or error_lines_exist))
881 or (not header_transferred and error_lines_exist) then
882 --
883 exit;
884 --
885 end if;
886 --
887 end loop;
888 --
889 if header_processing then
890 return 'P'; -- batch is currently under process.
891 elsif header_transferred
892 and NOT unprocessed_lines_exist
893 and NOT valid_lines_exist
894 and NOT error_lines_exist then
895 return 'T'; -- all lines (if exists) has been transferred.
896 elsif header_transferred then
897 return 'ST'; -- some lines might not have transferred.
898 elsif error_lines_exist then
899 return 'E'; -- there is at least one error line
900 elsif unprocessed_lines_exist
901 and NOT transferred_lines_exist then
902 return 'U'; -- there is at least one unprocessed line
903 elsif valid_lines_exist
904 and NOT transferred_lines_exist
905 and NOT unprocessed_lines_exist then
906 return 'V'; -- all lines are valid
907 -- elsif transferred_lines_exist
908 -- and NOT valid_lines_exist
909 -- return 'T'; -- all lines are transferred
910 else
911 return 'SM'; -- mismatch of statuses
912 end if;
913 --
914 end batch_overall_status;
915 --------------------------------------------------------------------------------
916 procedure get_batch_element_type (
917 --
918 p_batch_id number,
919 p_element_type_id in out nocopy number,
920 p_element_name in out nocopy varchar2) is
921 --
922 cursor csr_element is
923 select distinct elt.element_type_id, elt.element_name
924 from pay_element_types_f ELT,
925 pay_batch_lines LINE
926 where line.batch_id = p_batch_id and
927 line.element_type_id is not null and
928 line.element_type_id = elt.element_type_id
929 union
930 select distinct elt.element_type_id, elt.element_name
931 from pay_element_types_f ELT,
932 pay_batch_lines LINE
933 where line.batch_id = p_batch_id and
934 line.element_type_id is null and
935 upper (line.element_name) = upper(elt.element_name);
936 --
937 begin
938 --
939 open csr_element;
940 fetch csr_element into p_element_type_id, p_element_name;
941 close csr_element;
942 --
943 end get_batch_element_type;
944 --------------------------------------------------------------------------------
945 function assignment_number (p_assignment_id number) return varchar2 is
946 --
947 -- Returns the assignment number for the assignment id passed in
948 --
949 cursor csr_asgt_no is
950 select distinct assignment_number
951 from per_assignments_f2
952 where assignment_id = p_assignment_id;
953 --
954 v_asgt_no varchar2(80);
955 --
956 begin
957 --
958 open csr_asgt_no;
959 fetch csr_asgt_no into v_asgt_no;
960 close csr_asgt_no;
961 --
962 return v_asgt_no;
963 --
964 end assignment_number;
965 --------------------------------------------------------------------------------
966 procedure populate_context_items (
967 --
968 --******************************************************************************
969 -- Populate form initialisation information
970 --******************************************************************************
971 --
972 p_business_group_id in number, -- User's business group
973 p_cost_allocation_structure in out nocopy varchar2 -- Keyflex structure
974 ) is
975 --
976 -- Define how to retrieve Keyflex structure information
977 --
978 cursor keyflex_structure is
979 select cost_allocation_structure
980 from per_business_groups_perf
981 where business_group_id + 0 = p_business_group_id;
982 --
983 begin
984 --
985 -- Fetch Keyflex information
986 --
987 open keyflex_structure;
988 fetch keyflex_structure into p_cost_allocation_structure;
989 close keyflex_structure;
990 --
991 end populate_context_items;
992 --------------------------------------------------------------------------------
993 procedure check_name_uniqueness (
994 --
995 -- Check that the batch name is unique within business group
996 --
997 p_business_group_id number,
998 p_batch_name varchar2,
999 p_batch_id number) is
1000 --
1001 cursor csr_name is
1002 select 1
1003 from pay_batch_headers
1004 where (batch_id <> p_batch_id or p_batch_id is null)
1005 and business_group_id = p_business_group_id
1006 and upper (batch_name) = upper (p_batch_name);
1007 --
1008 begin
1009 --
1010 open csr_name;
1011 fetch csr_name into g_dummy;
1012 --
1013 if csr_name%found then
1014 close csr_name;
1015 fnd_message.set_name ('PAY', 'HR_BATCH_NAME_CLASH');
1016 fnd_message.raise_error;
1017 else
1018 close csr_name;
1019 end if;
1020 --
1021 end check_name_uniqueness;
1022 --------------------------------------------------------------------------------
1023 function create_batches_request_id (
1024 --
1025 -- Starts create_batches process via concurrent manager
1026 -- Returns TRUE if the request was successfully submitted
1027 --
1028 p_header_name varchar2,
1029 p_header_id number,
1030 p_reason varchar2,
1031 p_business_group_id number,
1032 p_effective_start_date date,
1033 p_effective_s_date date,
1034 p_effective_e_date date,
1035 p_element_type_id number,
1036 p_payroll_id number,
1037 p_assignment_set_id number,
1038 p_cost_allocation_keyflex_id number,
1039 p_mix_transfer_flag varchar2,
1040 p_value_1 varchar2,
1041 p_value_2 varchar2,
1042 p_value_3 varchar2,
1043 p_value_4 varchar2,
1044 p_value_5 varchar2,
1045 p_value_6 varchar2,
1046 p_value_7 varchar2,
1047 p_value_8 varchar2,
1048 p_value_9 varchar2,
1049 p_value_10 varchar2,
1050 p_value_11 varchar2,
1051 p_value_12 varchar2,
1052 p_value_13 varchar2,
1053 p_value_14 varchar2,
1054 p_value_15 varchar2,
1055 p_attribute_category varchar2,
1056 p_attribute1 varchar2,
1057 p_attribute2 varchar2,
1058 p_attribute3 varchar2,
1059 p_attribute4 varchar2,
1060 p_attribute5 varchar2,
1061 p_attribute6 varchar2,
1062 p_attribute7 varchar2,
1063 p_attribute8 varchar2,
1064 p_attribute9 varchar2,
1065 p_attribute10 varchar2,
1066 p_attribute11 varchar2,
1067 p_attribute12 varchar2,
1068 p_attribute13 varchar2,
1069 p_attribute14 varchar2,
1070 p_attribute15 varchar2,
1071 p_attribute16 varchar2,
1072 p_attribute17 varchar2,
1073 p_attribute18 varchar2,
1074 p_attribute19 varchar2,
1075 p_attribute20 varchar2,
1076 p_entry_information_category varchar2,
1077 p_entry_information1 varchar2,
1078 p_entry_information2 varchar2,
1079 p_entry_information3 varchar2,
1080 p_entry_information4 varchar2,
1081 p_entry_information5 varchar2,
1082 p_entry_information6 varchar2,
1083 p_entry_information7 varchar2,
1084 p_entry_information8 varchar2,
1085 p_entry_information9 varchar2,
1086 p_entry_information10 varchar2,
1087 p_entry_information11 varchar2,
1088 p_entry_information12 varchar2,
1089 p_entry_information13 varchar2,
1090 p_entry_information14 varchar2,
1091 p_entry_information15 varchar2,
1092 p_entry_information16 varchar2,
1093 p_entry_information17 varchar2,
1094 p_entry_information18 varchar2,
1095 p_entry_information19 varchar2,
1096 p_entry_information20 varchar2,
1097 p_entry_information21 varchar2,
1098 p_entry_information22 varchar2,
1099 p_entry_information23 varchar2,
1100 p_entry_information24 varchar2,
1101 p_entry_information25 varchar2,
1102 p_entry_information26 varchar2,
1103 p_entry_information27 varchar2,
1104 p_entry_information28 varchar2,
1105 p_entry_information29 varchar2,
1106 p_entry_information30 varchar2,
1107 p_date_earned date,
1108 p_subpriority number,
1109 p_element_set_id number default null,
1110 p_customized_restriction_id number default null,
1111 p_act_parameter_group_id number default null
1112 )
1113 return number is
1114 --
1115 v_request_id number := 0;
1116 --
1117 begin
1118 --
1119 v_request_id := fnd_request.submit_request(
1120 'PAY',
1121 'PYCBTC',
1122 null,
1123 null,
1124 null,
1125 p_business_group_id,
1126 fnd_date.date_to_canonical(p_effective_start_date),
1127 p_element_type_id,
1128 p_payroll_id,
1129 p_assignment_set_id,
1130 p_cost_allocation_keyflex_id,
1131 p_mix_transfer_flag,
1132 p_value_1,
1133 p_value_2,
1134 p_value_3,
1135 p_value_4,
1136 p_value_5,
1137 p_value_6,
1138 p_value_7,
1139 p_value_8,
1140 p_value_9,
1141 p_value_10,
1142 p_value_11,
1143 p_value_12,
1144 p_value_13,
1145 p_value_14,
1146 p_value_15,
1147 p_attribute_category,
1148 p_attribute1,
1149 p_attribute2,
1150 p_attribute3,
1151 p_attribute4,
1152 p_attribute5,
1153 p_attribute6,
1154 p_attribute7,
1155 p_attribute8,
1156 p_attribute9,
1157 p_attribute10,
1158 p_attribute11,
1159 p_attribute12,
1160 p_attribute13,
1161 p_attribute14,
1162 p_attribute15,
1163 p_attribute16,
1164 p_attribute17,
1165 p_attribute18,
1166 p_attribute19,
1167 p_attribute20,
1168 p_header_name,
1169 p_header_id,
1170 p_reason,
1171 fnd_date.date_to_canonical(p_effective_s_date),
1172 fnd_date.date_to_canonical(p_effective_e_date),
1173 fnd_date.date_to_canonical(p_date_earned),
1174 null,
1175 p_subpriority,
1176 p_entry_information_category,
1177 p_entry_information1,
1178 p_entry_information2,
1179 p_entry_information3,
1180 p_entry_information4,
1181 p_entry_information5,
1182 p_entry_information6,
1183 p_entry_information7,
1184 p_entry_information8,
1185 p_entry_information9,
1186 p_entry_information10,
1187 p_entry_information11,
1188 p_entry_information12,
1189 p_entry_information13,
1190 p_entry_information14,
1191 p_entry_information15,
1192 p_entry_information16,
1193 p_entry_information17,
1194 p_entry_information18,
1195 p_entry_information19,
1196 p_entry_information20,
1197 p_entry_information21,
1198 p_entry_information22,
1199 p_entry_information23,
1200 p_entry_information24,
1201 p_entry_information25,
1202 p_entry_information26,
1203 p_entry_information27,
1204 p_entry_information28,
1205 p_entry_information29,
1206 p_entry_information30,
1207 p_element_set_id,
1208 p_customized_restriction_id,
1209 p_act_parameter_group_id
1210 );
1211 if ( v_request_id <> 0 ) then
1212 commit;
1213 end if;
1214 --
1215 return (v_request_id);
1216 --
1217 end create_batches_request_id;
1218 --------------------------------------------------------------------------------
1219 --
1220 function convert_internal_to_display
1221 (p_element_type_id in varchar2,
1222 p_input_value in varchar2,
1223 p_input_value_number in number,
1224 p_session_date in date,
1225 p_batch_id in number
1226 ) return varchar2 is
1227 --
1228 --
1229 l_bee_iv_upgrade varchar2(1);
1230 --
1231 l_display_value varchar2(80) := p_input_value;
1232 l_internal_value varchar2(80) := p_input_value;
1233 l_dummy varchar2(100);
1234 --
1235 l_uom_value pay_input_values_f.UOM%TYPE;
1236 l_lookup_type pay_input_values_f.LOOKUP_TYPE%TYPE;
1237 l_value_set_id pay_input_values_f.VALUE_SET_ID%TYPE;
1238 l_currency_code pay_element_types_f.input_currency_code%TYPE;
1239 l_count number;
1240 l_found number;
1241 --
1242 cursor csr_valid_lookup
1243 (p_lookup_type varchar2,
1244 p_lookup_code varchar2) is
1245 select HL.meaning
1246 from hr_lookups HL
1247 where HL.lookup_type = p_lookup_type
1248 and HL.lookup_code = p_lookup_code;
1249 --
1250 cursor csr_iv is
1251 select inv.UOM,
1252 inv.LOOKUP_TYPE,
1253 inv.VALUE_SET_ID,
1254 etp.input_currency_code
1255 from pay_input_values_f inv,
1256 pay_element_types_f etp
1257 where inv.element_type_id = p_element_type_id
1258 and etp.element_type_id = p_element_type_id
1259 and p_session_date between inv.effective_start_date
1260 and inv.effective_end_date
1261 and p_session_date between etp.effective_start_date
1262 and etp.effective_end_date
1263 order by inv.display_sequence
1264 , inv.name;
1265 --
1266 cursor csr_bg_id is
1267 select bth.business_group_id
1268 from pay_batch_headers bth
1269 where bth.batch_id = p_batch_id;
1270 --
1271 l_business_group_id pay_batch_headers.business_group_id%TYPE;
1272 begin
1273 --
1274 begin
1275 --
1276 open csr_bg_id;
1277 fetch csr_bg_id into l_business_group_id;
1278 close csr_bg_id;
1279 --
1280 pay_core_utils.get_upgrade_status(l_business_group_id,'BEE_IV_UPG',l_bee_iv_upgrade);
1281 --
1282 exception
1283 when others then
1284 l_bee_iv_upgrade := 'E';
1285 end;
1286 --
1287 --
1288 -- Check whether the upgrade process is in progress.
1289 --
1290 if l_bee_iv_upgrade = 'E' then
1291 hr_utility.set_message(800, 'HR_449106_BEE_UPGRADING');
1292 hr_utility.raise_error;
1293 end if;
1294 --
1295 --
1296 if p_input_value is null then
1297 return p_input_value;
1298 end if;
1299 --
1300 l_count := 1;
1301 l_found := 0;
1302 for p_iv_rec in csr_iv loop
1303 --
1304 if l_count = p_input_value_number then
1305 l_uom_value := p_iv_rec.uom;
1306 l_lookup_type := p_iv_rec.LOOKUP_TYPE;
1307 l_value_set_id := p_iv_rec.VALUE_SET_ID;
1308 l_currency_code := p_iv_rec.input_currency_code;
1309 --
1310 l_found := 1;
1311 exit;
1312 end if;
1313 --
1314 l_count := l_count + 1;
1315 --
1316 end loop;
1317 --
1318 if l_found = 0 then
1319 return p_input_value;
1320 end if;
1321 --
1322 --
1323 if l_bee_iv_upgrade = 'N' then
1324 --
1325 -- BEE now handles input value of date in canonical format.
1326 -- However the EE API expects the data in the DD-MON-YYYY format.
1327 -- The DD-MON-YYYY is the default format of the fnd_date.
1328 --
1329 if l_uom_value = 'D' then
1330 begin
1331 l_display_value := fnd_date.date_to_displaydate(
1332 fnd_date.canonical_to_date(p_input_value),fnd_date.calendar_aware_alt); -- modified as per bug 11830805
1333 exception
1334 when others then
1335 raise;
1336 end;
1337 else
1338 l_display_value := p_input_value;
1339 end if;
1340 --
1341 else
1342 --
1343 if (l_lookup_type is not null and
1344 l_internal_value is not null) then
1345 --
1346 open csr_valid_lookup(l_lookup_type, l_internal_value);
1347 fetch csr_valid_lookup into l_display_value ;
1348 close csr_valid_lookup;
1349 --
1350 elsif (l_value_set_id is not null and
1351 l_internal_value is not null) then
1352 --
1353 l_display_value := pay_input_values_pkg.decode_vset_value(
1354 l_value_set_id, l_internal_value);
1355 --
1356 else
1357 --
1358 hr_chkfmt.changeformat (
1359 l_internal_value, /* the value to be formatted (out - display) */
1360 l_display_value, /* the formatted value on output (out - canonical) */
1361 l_uom_value, /* the format to check */
1362 l_currency_code );
1363 --
1364 end if;
1365 --
1366 end if;
1367 --
1368 return l_display_value;
1369 --
1370 exception
1371 when others then
1372 hr_utility.set_message ('PAY','PAY_6306_INPUT_VALUE_FORMAT');
1373 hr_utility.set_message_token ('UNIT_OF_MEASURE', hr_general.decode_lookup ('UNITS', l_uom_value ));
1374 hr_utility.raise_error;
1375 --
1376 end convert_internal_to_display;
1377 --
1378 end PAY_PAYWSQEE_PKG;