[Home] [Help]
PACKAGE BODY: APPS.PAY_HR_OTC_RETRIEVAL_INTERFACE
Source
1 PACKAGE BODY pay_hr_otc_retrieval_interface AS
2 /* $Header: pytshpri.pkb 120.12.12020000.3 2013/03/27 08:40:20 asrajago ship $ */
3 -- Global package name
4 g_package CONSTANT package_name := 'pay_hr_otc_retrieval_interface.';
5 -- Global variables
6 g_retro_batch_suffix VARCHAR2 (10) := '_RETRO';
7 g_batches_created batches_type_table;
8 -- Global Exceptions
9 e_continue EXCEPTION;
10 e_halt EXCEPTION;
11 g_assignment_id pay_batch_lines.assignment_id%TYPE;
12 l_since_date VARCHAR2 (20);
13 g_debug BOOLEAN := hr_utility.debug_enabled;
14
15 -- Bug 13705204
16 -- Defined the datatype and added a global variable here.
17 CURSOR csr_iv_info_m (
18 p_element_type_id pay_element_types_f.element_type_id%TYPE,
19 p_iv_number PLS_INTEGER,
20 p_session_date DATE
21 )
22 IS
23 SELECT *
24 FROM (SELECT iv_data.*, ROWNUM r
25 FROM (SELECT inv.uom, inv.lookup_type, inv.value_set_id,
26 etp.input_currency_code
27 FROM pay_input_values_f inv,
28 pay_element_types_f etp
29 WHERE inv.element_type_id = p_element_type_id
30 AND etp.element_type_id = p_element_type_id
31 AND p_session_date
32 BETWEEN inv.effective_start_date
33 AND inv.effective_end_date
34 AND p_session_date
35 BETWEEN etp.effective_start_date
36 AND etp.effective_end_date
37 ORDER BY inv.display_sequence, inv.NAME) iv_data
38 WHERE ROWNUM < (p_iv_number + 1))
39 WHERE r > (p_iv_number - 1);
40
41 TYPE IV_INFO_TABLE IS TABLE OF csr_iv_info_m%rowtype INDEX BY VARCHAR2(100);
42 g_iv_info_table IV_INFO_TABLE;
43
44
45 -- Bug 13777315
46 -- Function to verify if BEE_IV_UPGRADE is done.
47
48 FUNCTION get_upgrade_status(p_bg_id IN NUMBER)
49 RETURN VARCHAR2
50 IS
51
52 BEGIN
53
54 pay_core_utils.get_upgrade_status(p_bg_id,'BEE_IV_UPG',g_IV_UPGRADE);
55
56 IF g_iv_upgrade = 'Y'
57 THEN
58 g_IV_format := 'Y';
59 ELSE
60 g_IV_format := 'N';
61 END IF;
62
63 RETURN g_iv_upgrade;
64
65 END get_upgrade_status;
66
67
68 /*
69 || Function that can be used to convert "internal" values (LOV Codes etc.) to
70 || their respective "display" values (LOV Meaning etc.).
71 */
72 FUNCTION display_value (
73 p_element_type_id IN pay_batch_lines.element_type_id%TYPE,
74 p_internal_input_value IN pay_batch_lines.value_1%TYPE,
75 p_iv_number IN PLS_INTEGER,
76 p_session_date IN DATE,
77 p_bg_id IN hr_all_organization_units.business_group_id%TYPE
78 )
79 RETURN VARCHAR2
80 IS
81 l_proc CONSTANT proc_name := g_package || 'display_value';
82 l_display_value VARCHAR2 (80);
83
84 /*
85 || This odd looking query was created to be able to retrieve the nth
86 || (p_iv_number) Input Value of an Element Type without having to loop over
87 || all of them and then stop at the nth position.
88 */
89 CURSOR csr_iv_info (
90 p_element_type_id pay_element_types_f.element_type_id%TYPE,
91 p_iv_number PLS_INTEGER,
92 p_session_date DATE
93 )
94 IS
95 SELECT *
96 FROM (SELECT iv_data.*, ROWNUM r
97 FROM (SELECT inv.uom, inv.lookup_type, inv.value_set_id,
98 etp.input_currency_code
99 FROM pay_input_values_f inv,
100 pay_element_types_f etp
101 WHERE inv.element_type_id = p_element_type_id
102 AND etp.element_type_id = p_element_type_id
103 AND p_session_date
104 BETWEEN inv.effective_start_date
105 AND inv.effective_end_date
106 AND p_session_date
107 BETWEEN etp.effective_start_date
108 AND etp.effective_end_date
109 ORDER BY inv.display_sequence, inv.NAME) iv_data
110 WHERE ROWNUM < (p_iv_number + 1))
111 WHERE r > (p_iv_number - 1);
112
113 rec_iv_info csr_iv_info%ROWTYPE;
114
115 FUNCTION lookup_meaning (
116 p_lookup_type IN hr_lookups.lookup_type%TYPE,
117 p_lookup_code IN hr_lookups.lookup_code%TYPE
118 )
119 RETURN hr_lookups.meaning%TYPE
120 AS
121 CURSOR csr_valid_lookup (
122 p_lookup_type VARCHAR2,
123 p_lookup_code VARCHAR2
124 )
125 IS
126 SELECT hl.meaning
127 FROM hr_lookups hl
128 WHERE hl.lookup_type = p_lookup_type
129 AND hl.lookup_code = p_lookup_code;
130
131 l_lookup_meaning hr_lookups.meaning%TYPE;
132 BEGIN
133 OPEN csr_valid_lookup (p_lookup_type, p_lookup_code);
134
135 FETCH csr_valid_lookup
136 INTO l_lookup_meaning;
137
138 CLOSE csr_valid_lookup;
139
140 hr_utility.set_location ( ' l_lookup_meaning = '
141 || l_lookup_meaning,
142 10
143 );
144 RETURN l_lookup_meaning;
145 END lookup_meaning;
146
147 FUNCTION valueset_meaning (
148 p_value_set_id IN fnd_flex_values.flex_value_set_id%TYPE,
149 p_valueset_value IN fnd_flex_values.flex_value%TYPE
150 )
151 RETURN fnd_flex_values_vl.description%TYPE
152 AS
153 l_valueset_meaning fnd_flex_values_vl.description%TYPE;
154 BEGIN
155 l_valueset_meaning :=
156 pay_input_values_pkg.decode_vset_value (p_value_set_id,
157 p_valueset_value
158 );
159 hr_utility.set_location ( ' l_valueset_meaning = '
160 || l_valueset_meaning,
161 10
162 );
163 RETURN l_valueset_meaning;
164 END valueset_meaning;
165 BEGIN
166 hr_utility.set_location ('Entering: ' || l_proc, 10);
167 l_display_value := p_internal_input_value;
168
169 -- Bug 13777315
170 IF g_iv_upgrade = 'Y'
171 THEN
172 hr_utility.trace('IV In = '||p_internal_input_value);
173 hr_utility.trace('IV upgrade done, not converting any IVs ');
174 RETURN p_internal_input_value;
175 END IF;
176
177 IF (p_internal_input_value IS NOT NULL)
178 THEN
179 hr_utility.set_location
180 ( ' Converting p_internal_input_value = '
181 || p_internal_input_value,
182 20
183 );
184 hr_utility.set_location (' using: ', 30);
185 hr_utility.set_location ( ' p_element_type_id = '
186 || p_element_type_id,
187 40
188 );
189 hr_utility.set_location (' p_iv_number = ' || p_iv_number,
190 50
191 );
192 hr_utility.set_location ( ' p_session_date = '
193 || p_session_date,
194 60
195 );
196 hr_utility.set_location (' p_bg_id = ' || p_bg_id,
197 70);
198
199 -- Bug 13705204
200 -- Added this caching logic here.
201 IF g_iv_info_table.EXISTS(p_element_type_id||'-'||p_iv_number||'-'||p_session_date)
202 THEN
203 rec_iv_info := g_iv_info_table(p_element_type_id||'-'||p_iv_number||'-'||p_session_date);
204
205 IF (rec_iv_info.lookup_type IS NOT NULL)
206 THEN
207 l_display_value :=
208 lookup_meaning (p_lookup_type => rec_iv_info.lookup_type,
209 p_lookup_code => p_internal_input_value
210 );
211 ELSIF (rec_iv_info.value_set_id IS NOT NULL)
212 THEN
213 l_display_value :=
214 valueset_meaning
215 (p_value_set_id => rec_iv_info.value_set_id,
216 p_valueset_value => p_internal_input_value
217 );
218 END IF;
219
220 ELSE
221
222 OPEN csr_iv_info (p_element_type_id, p_iv_number, p_session_date);
223
224 FETCH csr_iv_info
225 INTO rec_iv_info;
226
227 IF (csr_iv_info%FOUND)
228 THEN
229 g_iv_info_table(p_element_type_id||'-'||p_iv_number||'-'||p_session_date) := rec_iv_info;
230
231 IF (rec_iv_info.lookup_type IS NOT NULL)
232 THEN
233 l_display_value :=
234 lookup_meaning (p_lookup_type => rec_iv_info.lookup_type,
235 p_lookup_code => p_internal_input_value
236 );
237 ELSIF (rec_iv_info.value_set_id IS NOT NULL)
238 THEN
239 l_display_value :=
240 valueset_meaning
241 (p_value_set_id => rec_iv_info.value_set_id,
242 p_valueset_value => p_internal_input_value
243 );
244 -- Bug 8411771
245 -- Commenting the below code. Henceforth, all Date, Number,
246 -- Currency input values would go thru in Internal format
247 -- and BEE would take care of the conversion.
248 /*
249 ELSE
250 hr_chkfmt.changeformat (p_internal_input_value,
251 l_display_value,
252 rec_iv_info.uom,
253 rec_iv_info.input_currency_code
254 );
255 */
256 END IF;
257 END IF;
258 END IF;
259 END IF;
260
261 hr_utility.set_location ( ' returning l_display_value = '
262 || l_display_value,
263 90
264 );
265 hr_utility.set_location ('Leaving: ' || l_proc, 100);
266 RETURN l_display_value;
267 EXCEPTION
268 WHEN OTHERS
269 THEN
270 hr_utility.trace(dbms_utility.format_error_backtrace);
271 hr_utility.set_message ('PAY', 'PAY_6306_INPUT_VALUE_FORMAT');
272 hr_utility.set_message_token
273 ('UNIT_OF_MEASURE',
274 hr_general.decode_lookup ('UNITS',
275 rec_iv_info.uom
276 )
277 );
278 hr_utility.raise_error;
279
280 END display_value;
281
282 FUNCTION retro_batch_suffix
283 RETURN VARCHAR2
284 IS
285 l_proc CONSTANT proc_name := g_package || 'retro_batch_suffix';
286 BEGIN
287 hr_utility.set_location ('Entering:' || l_proc, 10);
288 hr_utility.set_location ( ' returning g_retro_batch_suffix = '
289 || g_retro_batch_suffix,
290 20
291 );
292 hr_utility.set_location ('Leaving:' || l_proc, 100);
293 RETURN g_retro_batch_suffix;
294 END retro_batch_suffix;
295
296 PROCEDURE set_retro_batch_suffix (p_retro_batch_suffix IN VARCHAR2)
297 IS
298 l_proc CONSTANT proc_name := g_package || 'set_retro_batch_suffix';
299 BEGIN
300 hr_utility.set_location ('Entering:' || l_proc, 10);
301 hr_utility.set_location ( ' setting g_retro_batch_suffix to '
302 || p_retro_batch_suffix,
303 20
304 );
305 g_retro_batch_suffix := p_retro_batch_suffix;
306 hr_utility.set_location ('Leaving:' || l_proc, 100);
307 END set_retro_batch_suffix;
308
309 PROCEDURE record_batch_info (p_batch_rec IN batches_type_rec)
310 IS
311 l_proc CONSTANT proc_name := g_package || 'record_batch_info';
312 BEGIN
313 hr_utility.set_location ('Entering:' || l_proc, 10);
314 g_batches_created (NVL (g_batches_created.LAST, 0) + 1) := p_batch_rec;
315 hr_utility.set_location ('Leaving:' || l_proc, 100);
316 END record_batch_info;
317
318 PROCEDURE record_batch_info (
319 p_batch_id IN pay_batch_headers.batch_id%TYPE,
320 p_business_group_id IN pay_batch_headers.business_group_id%TYPE,
321 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
322 p_batch_name IN pay_batch_headers.batch_name%TYPE
323 )
324 IS
325 l_proc CONSTANT proc_name
326 := g_package || 'record_batch_info (Overloaded)';
327 l_batch_created batches_type_rec;
328 BEGIN
329 hr_utility.set_location ('Entering:' || l_proc, 10);
330 l_batch_created.batch_id := p_batch_id;
331 l_batch_created.business_group_id := p_business_group_id;
332 l_batch_created.batch_reference := p_batch_reference;
333 l_batch_created.batch_name := p_batch_name;
334 record_batch_info (p_batch_rec => l_batch_created);
335 hr_utility.set_location ('Leaving:' || l_proc, 100);
336 END record_batch_info;
337
338 FUNCTION batches_created
339 RETURN batches_type_table
340 IS
341 l_proc CONSTANT proc_name := g_package || 'batches_created';
342 BEGIN
343 hr_utility.set_location ('Entering:' || l_proc, 10);
344 hr_utility.set_location ( ' g_batches_created.count = '
345 || g_batches_created.COUNT,
346 20
347 );
348 hr_utility.set_location ('Leaving:' || l_proc, 100);
349 RETURN g_batches_created;
350 END batches_created;
351
352 PROCEDURE start_bee_process (
353 p_mode IN VARCHAR2,
354 p_batches IN batches_type_table
355 )
356 IS
357 l_proc CONSTANT proc_name := g_package || 'start_bee_process';
358 l_batches_idx PLS_INTEGER := p_batches.FIRST;
359 l_request_id fnd_concurrent_requests.request_id%TYPE;
360 BEGIN
361 hr_utility.set_location ('Entering:' || l_proc, 10);
362
363 <<start_conc_prog_for_batches>>
364 LOOP
365 EXIT start_conc_prog_for_batches WHEN NOT p_batches.EXISTS
366 (l_batches_idx);
367 l_request_id :=
368 pay_paywsqee_pkg.paylink_request_id
369 (p_business_group_id => p_batches (l_batches_idx).business_group_id,
370 p_mode => p_mode,
371 p_batch_id => p_batches (l_batches_idx).batch_id
372 );
373 l_batches_idx := p_batches.NEXT (l_batches_idx);
374 END LOOP start_conc_prog_for_batches;
375
376 hr_utility.set_location ('Leaving:' || l_proc, 100);
377 END start_bee_process;
378
379 PROCEDURE validate_bee_batches (p_batches IN batches_type_table)
380 IS
381 l_proc CONSTANT proc_name := g_package || 'validate_bee_batches';
382 BEGIN
383 hr_utility.set_location ('Entering:' || l_proc, 10);
384 start_bee_process (p_mode => 'VALIDATE', p_batches => p_batches);
385 hr_utility.set_location ('Leaving:' || l_proc, 100);
386 END validate_bee_batches;
387
388 PROCEDURE transfer_bee_batches (p_batches IN batches_type_table)
389 IS
390 l_proc CONSTANT proc_name := g_package || 'transfer_bee_batches';
391 l_batches_idx PLS_INTEGER := p_batches.FIRST;
392 BEGIN
393 hr_utility.set_location ('Entering:' || l_proc, 10);
394 start_bee_process (p_mode => 'TRANSFER', p_batches => p_batches);
395 hr_utility.set_location ('Leaving:' || l_proc, 100);
396 END transfer_bee_batches;
397
398 PROCEDURE process_bee_batches (
399 p_batches IN batches_type_table DEFAULT batches_created,
400 p_status_in_bee IN VARCHAR2
401 )
402 IS
403 l_proc CONSTANT proc_name := g_package || 'process_bee_batches';
404 l_batches_idx PLS_INTEGER := p_batches.FIRST;
405 BEGIN
406 hr_utility.set_location ('Entering:' || l_proc, 10);
407
408 IF (p_status_in_bee = 'V')
409 THEN
410 validate_bee_batches (p_batches);
411 ELSIF (p_status_in_bee = 'T')
412 THEN
413 transfer_bee_batches (p_batches);
414 END IF;
415
416 hr_utility.set_location ('Leaving:' || l_proc, 100);
417 END process_bee_batches;
418
419 FUNCTION where_clause (
420 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
421 p_location_id IN per_all_assignments_f.location_id%TYPE,
422 p_payroll_id IN per_all_assignments_f.payroll_id%TYPE,
423 p_organization_id IN per_all_assignments_f.organization_id%TYPE,
424 p_person_id IN per_all_people_f.person_id%TYPE,
425 p_gre_id IN hr_soft_coding_keyflex.segment1%TYPE
426 )
427 RETURN VARCHAR2
428 IS
429 l_proc CONSTANT proc_name := g_package || 'where_clause';
430 l_where_clause hxt_interface_utilities.max_varchar := NULL;
431 l_payroll hxt_interface_utilities.varchar_256 := NULL;
432 l_person hxt_interface_utilities.varchar_256 := NULL;
433 l_org hxt_interface_utilities.varchar_256 := NULL;
434 l_location hxt_interface_utilities.varchar_256 := NULL;
435
436 -- local functions
437 FUNCTION clause_part (p_id IN NUMBER, p_clause VARCHAR2)
438 RETURN VARCHAR2
439 IS
440 l_clause_part hxt_interface_utilities.max_varchar := NULL;
441 BEGIN
442 IF p_id IS NOT NULL
443 THEN
444 l_clause_part := p_clause || TO_CHAR (p_id);
445 ELSE
446 l_clause_part := NULL;
447 END IF;
448
449 RETURN l_clause_part;
450 END clause_part;
451 BEGIN
452 hr_utility.set_location ('Entering ' || l_proc, 10);
453 l_location := clause_part (p_location_id, ' and paa.location_id = ');
454 l_payroll := clause_part (p_payroll_id, ' and paa.payroll_id = ');
455 l_org := clause_part (p_organization_id, ' and paa.organization_id = ');
456 l_person := clause_part (p_person_id, ' and paa.person_id = ');
457
458 IF ( p_gre_id IS NULL
459 AND p_location_id IS NULL
460 AND p_payroll_id IS NULL
461 AND p_organization_id IS NULL
462 AND p_person_id IS NULL
463 )
464 THEN
465 l_where_clause :=
466 '[DETAIL_BLOCK.RESOURCE_TYPE] {=''PERSON'' AND}
467 [DETAIL_BLOCK.RESOURCE_ID]
468 {in (select peo.person_id
469 from per_all_people_f peo
470 where peo.business_group_id = '
471 || p_bg_id
472 || ')}';
473 ELSIF p_gre_id IS NULL
474 THEN
475 l_where_clause :=
476 '[DETAIL_BLOCK.RESOURCE_TYPE] {=''PERSON'' AND}
477 [DETAIL_BLOCK.RESOURCE_ID]
478 {in (select paa.person_id
479 from per_all_assignments_f paa
480 where paa.business_group_id = '
481 || p_bg_id
482 || l_person
483 || l_payroll
484 || l_location
485 || l_org
486 || ')}';
487 ELSE
488 l_where_clause :=
489 '[DETAIL_BLOCK.RESOURCE_TYPE] {=''PERSON'' AND}
490 [DETAIL_BLOCK.RESOURCE_ID]
491 {in (select paa.person_id
492 from per_all_assignments_f paa,
493 hr_soft_coding_keyflex hsk
494 where paa.business_group_id = '
495 || p_bg_id
496 || l_person
497 || l_payroll
498 || l_location
499 || ' and paa.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
500 and hsk.segment1 = '''
501 || p_gre_id
502 || ''')}';
503 END IF;
504
505 hr_utility.set_location ('Leaving ' || l_proc, 100);
506 RETURN l_where_clause;
507 END where_clause;
508
509 PROCEDURE set_transaction_detail (
510 p_tbb_idx IN PLS_INTEGER,
511 p_status IN hxc_transactions.status%TYPE,
512 p_exception IN hxc_transactions.exception_description%TYPE
513 )
514 IS
515 l_proc CONSTANT proc_name := g_package || 'set_transaction_detail';
516 BEGIN
517 hr_utility.set_location ('Entering ' || l_proc, 10);
518 hxc_generic_retrieval_pkg.t_tx_detail_status (p_tbb_idx) := p_status;
519 hxc_generic_retrieval_pkg.t_tx_detail_exception (p_tbb_idx) :=
520 SUBSTR (p_exception, 1, g_max_message_size);
521 hr_utility.set_location ('Leaving ' || l_proc, 100);
522 END set_transaction_detail;
523
524 PROCEDURE set_transaction (
525 p_process_name IN hxc_retrieval_processes.NAME%TYPE,
526 p_status IN hxc_transactions.status%TYPE,
527 p_exception IN hxc_transactions.exception_description%TYPE
528 )
529 IS
530 l_proc CONSTANT proc_name := g_package || 'set_transaction';
531 BEGIN
532 hr_utility.set_location ('Entering ' || l_proc, 10);
533 hxc_generic_retrieval_utils.set_parent_statuses;
534 hxc_generic_retrieval_pkg.update_transaction_status
535 (p_process => p_process_name,
536 p_status => p_status,
537 p_exception_description => SUBSTR
538 (p_exception,
539 1,
540 g_max_message_size
541 ),
542 p_rollback => FALSE
543 );
544 hr_utility.set_location ('Leaving ' || l_proc, 100);
545 END set_transaction;
546
547 PROCEDURE set_successfull_trx_detail (p_tbb_idx IN PLS_INTEGER)
548 IS
549 l_proc CONSTANT proc_name
550 := g_package || 'set_successfull_trx_detail';
551 BEGIN
552 hr_utility.set_location ('Entering ' || l_proc, 10);
553 fnd_message.set_name (g_hxc_app_short_name, g_trx_detail_success_msg);
554 set_transaction_detail (p_tbb_idx => p_tbb_idx,
555 p_status => g_trx_success,
556 p_exception => fnd_message.get
557 );
558 hr_utility.set_location ('Leaving ' || l_proc, 100);
559 END set_successfull_trx_detail;
560
561 PROCEDURE set_successfull_trx (
562 p_process_name IN hxc_retrieval_processes.NAME%TYPE
563 )
564 IS
565 l_proc CONSTANT proc_name := g_package || 'set_successfull_trx';
566 BEGIN
567 hr_utility.set_location ('Entering ' || l_proc, 10);
568 fnd_message.set_name (g_hxc_app_short_name, g_trx_success_msg);
569 set_transaction (p_process_name => p_process_name,
570 p_status => g_trx_success,
571 p_exception => fnd_message.get
572 );
573 hr_utility.set_location ('Leaving ' || l_proc, 100);
574 END set_successfull_trx;
575
576 PROCEDURE set_sqlerror_trx_detail (p_tbb_idx IN PLS_INTEGER)
577 IS
578 l_proc CONSTANT proc_name := g_package || 'set_sqlerror_trx_detail';
579 BEGIN
580 hr_utility.set_location ('Entering ' || l_proc, 10);
581 hr_utility.set_location (' Error found = ' || SQLERRM, 20);
582 set_transaction_detail (p_tbb_idx => p_tbb_idx,
583 p_status => g_trx_error,
584 p_exception => 'The error is : '
585 || TO_CHAR (SQLCODE)
586 || ' '
587 || SQLERRM
588 );
589 hr_utility.set_location ('Leaving ' || l_proc, 100);
590 END set_sqlerror_trx_detail;
591
592 PROCEDURE set_sqlerror_trx (
593 p_process_name IN hxc_retrieval_processes.NAME%TYPE
594 )
595 IS
596 l_proc CONSTANT proc_name := g_package || 'set_sqlerror_trx';
597 BEGIN
598 hr_utility.set_location ('Entering ' || l_proc, 10);
599 set_transaction (p_process_name => p_process_name,
600 p_status => g_trx_error,
601 p_exception => 'The error is : '
602 || TO_CHAR (SQLCODE)
603 || ' '
604 || SQLERRM
605 );
606 hr_utility.set_location ('Leaving ' || l_proc, 100);
607 END set_sqlerror_trx;
608
609 -- Bug 10323310
610 -- New function for R12.2 Proj Pay Integration.
611 -- Takes in a resource id and caches the preference for this integration
612 -- if not already done.
613
614 PROCEDURE cache_preferences ( p_resource_id IN NUMBER,
615 p_start_date IN DATE,
616 p_end_date IN DATE)
617 IS
618
619 l_pref_tab hxc_preference_evaluation.t_pref_table;
620 l_index NUMBER := 0;
621
622 BEGIN
623
624 IF g_debug
625 THEN
626 hr_utility.trace('p_resource_id = '||p_resource_id);
627 END IF;
628
629 IF NOT hxc_generic_retrieval_utils.g_resources.EXISTS(p_resource_id)
630 THEN
631 IF g_debug
632 THEN
633 hr_utility.trace(' Resource does not exists; returning here ');
634 END IF;
635 RETURN;
636 END IF;
637
638 IF g_emp_pref_tab.EXISTS(p_resource_id)
639 THEN
640 IF g_debug
641 THEN
642 hr_utility.trace('Preference already cached, returning');
643 END IF;
644 RETURN;
645 END IF;
646
647 hxc_preference_evaluation.resource_preferences
648 (p_resource_id => p_resource_id
649 ,p_start_evaluation_date => NVL(p_start_date,
650 hxc_generic_retrieval_utils.g_resources(p_resource_id).start_time)
651 ,p_end_evaluation_date => NVL(p_end_date,
652 hxc_generic_retrieval_utils.g_resources(p_resource_id).stop_time)
653 ,p_pref_table => l_pref_tab );
654
655 IF l_pref_tab.COUNT > 0
656 THEN
657 IF g_debug
658 THEN
659 hr_utility.trace('Gathered preferences for '||p_resource_id);
660 END IF;
661 g_emp_pref_tab(p_resource_id) := pref_tab();
662 FOR i IN l_pref_tab.FIRST..l_pref_tab.LAST
663 LOOP
664 IF l_pref_tab(i).preference_code = 'TS_PA_PAY_INTG'
665 AND l_pref_tab(i).attribute1 = 'Y'
666 THEN
667 IF g_debug
668 THEN
669 hr_utility.trace('Integration is enabled for this person ');
670 END IF;
671
672 g_emp_pref_tab(p_resource_id).EXTEND;
673 l_index := l_index+1;
674 g_emp_pref_tab(p_resource_id)(l_index).intg_enabled := l_pref_tab(i).attribute1;
675 g_emp_pref_tab(p_resource_id)(l_index).date_start := l_pref_tab(i).start_date;
676 g_emp_pref_tab(p_resource_id)(l_index).date_end := l_pref_tab(i).end_date;
677 END IF;
678 END LOOP;
679 END IF;
680
681 END cache_preferences;
682
683 -- This function checks in the above cached preference for a TRUE
684 -- value for integration enabled.
685
686 FUNCTION chk_intg_enabled(p_resource_id IN NUMBER,
687 p_date IN DATE)
688
689 RETURN BOOLEAN
690 IS
691
692 BEGIN
693
694 IF NOT g_emp_pref_tab.EXISTS(p_resource_id)
695 THEN
696 IF g_debug
697 THEN
698 hr_utility.trace('No preferences gathered; returning ');
699 END IF;
700 RETURN FALSE;
701 END IF;
702
703 IF g_emp_pref_tab(p_resource_id).COUNT > 0
704 THEN
705 FOR i IN g_emp_pref_tab(p_resource_id).FIRST..g_emp_pref_tab(p_resource_id).LAST
706 LOOP
707 IF p_date BETWEEN g_emp_pref_tab(p_resource_id)(i).date_start
708 AND g_emp_pref_tab(p_resource_id)(i).date_end
709 THEN
710 IF g_emp_pref_tab(p_resource_id)(i).intg_enabled = 'Y'
711 THEN
712 IF g_debug
713 THEN
714 hr_utility.trace('Integration enabled, returning TRUE ');
715 END IF;
716 RETURN TRUE;
717 ELSE
718 RETURN FALSE;
719 END IF;
720 END IF;
721 END LOOP;
722 ELSE
723 RETURN FALSE;
724 END IF;
725
726 END chk_intg_enabled;
727
728
729 PROCEDURE process_otlr_employees (
730 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
731 p_session_date IN DATE, -- Bug 6121705, need date for reversal batch creation
732 p_start_date IN VARCHAR2,
733 --hxc_time_building_blocks.start_time%TYPE,
734 p_end_date IN VARCHAR2,
735 --hxc_time_building_blocks.stop_time%TYPE,
736 p_where_clause IN hxt_interface_utilities.max_varchar,
737 p_retrieval_transaction_code IN hxc_transactions.transaction_code%TYPE,
738 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
739 p_unique_params IN hxt_interface_utilities.max_varchar,
740 p_incremental IN hxt_interface_utilities.flag_varchar
741 DEFAULT 'Y',
742 p_transfer_to_bee IN hxt_interface_utilities.flag_varchar
743 DEFAULT 'N',
744 p_no_otm IN OUT NOCOPY hxt_interface_utilities.flag_varchar
745 )
746 AS
747 l_proc CONSTANT proc_name := g_package || 'process_otlr_employees';
748
749 l_dup_count NUMBER; -- Bug 6121705
750
751 BEGIN
752 hr_utility.set_location ('Entering ' || l_proc, 10);
753 -- transfer the employees with OTM Rules = Yes
754 hxt_otc_retrieval_interface.transfer_to_otm
755 (p_bg_id => p_bg_id,
756 p_incremental => p_incremental,
757 p_start_date => p_start_date,
758 p_end_date => p_end_date,
759 p_where_clause => p_where_clause,
760 p_transfer_to_bee => p_transfer_to_bee,
761 p_retrieval_transaction_code => p_retrieval_transaction_code,
762 p_batch_ref => p_batch_ref,
763 p_no_otm => p_no_otm,
764 p_unique_params => p_unique_params,
765 p_since_date => l_since_date
766 );
767
768 -- Bug 6121705
769 -- For automatic reversal batch creation if there is a rule evaluation pref
770 -- change.
771
772 -- Earlier hxc_gen_retrieve_utils.chk_retrieve would have populated
773 -- HXC_BEE_PREF_ADJ_LINES with the details that were previously transferred
774 -- and had a preference change before transfer this time.
775 -- Check the count of those records which are for time store. Now we
776 -- are running OTM batches. Meaning, if there was an earlier batch, it would
777 -- have been from time store. There needs to be reverse batches for those.
778
779 -- The rownum condition is put there especially for performance. On worst
780 -- case there could be thousands of records in there, and you just wanna
781 -- know if there is atleast one. If the optimizer is not set to take the first
782 -- n rows, your count(*) would wait until all rows are returned. With the rownum
783 -- it will only pull out your first record if there exists one, else zero. So
784 -- the result is only 1 or 0.
785
786 SELECT COUNT(*)
787 INTO l_dup_count
788 FROM hxc_bee_pref_adj_lines
789 WHERE batch_source = 'Time Store'
790 AND ROWNUM < 2 ;
791
792
793 -- This condition will call a proc to create reverse Time Store batches for the
794 -- details in there.
795 IF l_dup_count > 0
796 THEN
797 make_adjustments_bee(p_batch_ref, -- we need the current batch reference for our adj batch
798 p_bg_id, -- bg_id to create batch
799 p_session_date); -- batch is created with this session date
800 END IF;
801
802 hr_utility.set_location ('Leaving ' || l_proc, 100);
803 END process_otlr_employees;
804
805
806 -- Bug 10323310
807 -- Proj Pay Integration
808 -- Copies down the attribute records from Attribute table to the global table.
809 PROCEDURE record_pay_pa_details ( p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
810 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
811 p_det_tbb_idx IN PLS_INTEGER,
812 p_is_old IN BOOLEAN DEFAULT FALSE )
813 IS
814
815 l_index BINARY_INTEGER;
816
817 BEGIN
818 IF NOT p_is_old
819 THEN
820 l_index := p_attr_tbl.FIRST;
821 LOOP
822 IF p_attr_tbl(l_index).bb_id = p_tbb_id
823 AND g_pay_pa_link_tab.EXISTS(p_det_tbb_idx)
824 THEN
825 IF p_attr_tbl(l_index).field_name = 'WORKTYPE_ID'
826 THEN
827 g_pay_pa_link_tab(p_det_tbb_idx).work_type_id := p_attr_tbl(l_index).value;
828 ELSIF p_attr_tbl(l_index).field_name = 'LOCATION_ID'
829 THEN
830 g_pay_pa_link_tab(p_det_tbb_idx).location_id := p_attr_tbl(l_index).value;
831 ELSIF p_attr_tbl(l_index).field_name = 'JOB_ID'
832 THEN
833 g_pay_pa_link_tab(p_det_tbb_idx).job_id := p_attr_tbl(l_index).value;
834 ELSIF p_attr_tbl(l_index).field_name = 'PROJECT_ID'
835 THEN
836 g_pay_pa_link_tab(p_det_tbb_idx).PROJECT_ID := p_attr_tbl(l_index).value;
837 ELSIF p_attr_tbl(l_index).field_name = 'TASK_ID'
838 THEN
839 g_pay_pa_link_tab(p_det_tbb_idx).TASK_ID := p_attr_tbl(l_index).value;
840
841 ELSIF p_attr_tbl(l_index).field_name = 'EXPENDITURE_TYPE'
842 THEN
843 g_pay_pa_link_tab(p_det_tbb_idx).EXP_TYPE := p_attr_tbl(l_index).value;
844 ELSIF p_attr_tbl(l_index).field_name = 'SYSTEM_LINKAGE_FUNCTION'
845 THEN
846 g_pay_pa_link_tab(p_det_tbb_idx).SYSTEM_LINKAGE_FUNCTION := p_attr_tbl(l_index).value;
847
848 END IF;
849 END IF;
850 l_index := p_attr_tbl.NEXT(l_index);
851 EXIT WHEN NOT p_attr_tbl.EXISTS(l_index);
852 END LOOP;
853
854 ELSIF p_is_old
855 THEN
856 l_index := p_attr_tbl.FIRST;
857 LOOP
858 IF p_attr_tbl(l_index).bb_id = p_tbb_id
859 AND g_old_pay_pa_link_tab.EXISTS(p_det_tbb_idx)
860 THEN
861 IF p_attr_tbl(l_index).field_name = 'WORKTYPE_ID'
862 THEN
863 g_old_pay_pa_link_tab(p_det_tbb_idx).work_type_id := p_attr_tbl(l_index).value;
864 ELSIF p_attr_tbl(l_index).field_name = 'LOCATION_ID'
865 THEN
866 g_old_pay_pa_link_tab(p_det_tbb_idx).location_id := p_attr_tbl(l_index).value;
867 ELSIF p_attr_tbl(l_index).field_name = 'JOB_ID'
868 THEN
869 g_old_pay_pa_link_tab(p_det_tbb_idx).job_id := p_attr_tbl(l_index).value;
870 ELSIF p_attr_tbl(l_index).field_name = 'PROJECT_ID'
871 THEN
872 g_old_pay_pa_link_tab(p_det_tbb_idx).PROJECT_ID := p_attr_tbl(l_index).value;
873 ELSIF p_attr_tbl(l_index).field_name = 'TASK_ID'
874 THEN
875 g_old_pay_pa_link_tab(p_det_tbb_idx).TASK_ID := p_attr_tbl(l_index).value;
876
877 ELSIF p_attr_tbl(l_index).field_name = 'EXPENDITURE_TYPE'
878 THEN
879 g_old_pay_pa_link_tab(p_det_tbb_idx).EXP_TYPE := p_attr_tbl(l_index).value;
880 ELSIF p_attr_tbl(l_index).field_name = 'SYSTEM_LINKAGE_FUNCTION'
881 THEN
882 g_old_pay_pa_link_tab(p_det_tbb_idx).SYSTEM_LINKAGE_FUNCTION := p_attr_tbl(l_index).value;
883
884 END IF;
885 END IF;
886 l_index := p_attr_tbl.NEXT(l_index);
887 EXIT WHEN NOT p_attr_tbl.EXISTS(l_index);
888 END LOOP;
889 END IF;
890
891 END record_pay_pa_details;
892
893 PROCEDURE extract_data_from_attr_tbl (
894 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
895 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
896 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
897 p_det_tbb_idx IN PLS_INTEGER,
898 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
899 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
900 p_attr_tbl_idx IN OUT NOCOPY PLS_INTEGER,
901 p_bee_rec IN OUT NOCOPY hxt_interface_utilities.bee_rec,
902 p_is_old IN BOOLEAN DEFAULT FALSE
903 )
904
905 AS
906 l_proc CONSTANT proc_name
907 := g_package || 'extract_data_from_attr_tbl';
908 e_no_element_type_id EXCEPTION;
909 l_start_attr_tbl_idx PLS_INTEGER;
910 BEGIN
911 hr_utility.set_location ('Entering ' || l_proc, 10);
912
913 IF (hxt_interface_utilities.is_in_sync
914 (p_check_tbb_id => p_attr_tbl (NVL (p_attr_tbl_idx,
915 p_attr_tbl.FIRST
916 )
917 ).bb_id,
918 p_against_tbb_id => p_tbb_id
919 )
920 )
921 THEN
922 -- We first have to find the element_id cause we need that for converting
923 -- the associated IVs.
924 p_bee_rec.pay_batch_line.element_type_id :=
925 hxt_interface_utilities.find_element_id_in_attr_tbl
926 (p_att_table => p_attr_tbl,
927 p_tbb_id => p_tbb_id,
928 p_start_position => p_attr_tbl_idx
929 );
930
931 IF (p_bee_rec.pay_batch_line.element_type_id IS NULL)
932 THEN
933 RAISE e_no_element_type_id;
934 END IF;
935
936 l_start_attr_tbl_idx := p_attr_tbl_idx;
937 -- Now find all the other data (IVs, Asg Data and Cost Segments)
938 hxt_interface_utilities.find_other_in_attr_tbl
939 (p_bg_id => p_bg_id,
940 p_att_table => p_attr_tbl,
941 p_tbb_id => p_tbb_id,
942 p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
943 p_cost_flex_id => p_cost_flex_id,
944 p_effective_date => p_effective_date,
945 p_start_position => l_start_attr_tbl_idx,
946 p_ending_position => p_attr_tbl_idx,
947 p_bee_rec => p_bee_rec
948 );
949
950 record_pay_pa_details(p_attr_tbl,
951 p_tbb_id,
952 p_det_tbb_idx,
953 p_is_old);
954 ELSE
955 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
956 fnd_message.set_token ('PROCEDURE', l_proc);
957 fnd_message.set_token ('STEP', 'tbb mismatch');
958 fnd_message.raise_error;
959 END IF;
960
961 hr_utility.set_location ('Leaving ' || l_proc, 100);
962 EXCEPTION
963 WHEN e_no_element_type_id
964 THEN
965 fnd_message.set_name (g_hxc_app_short_name,
966 'HXC_HRPAY_RET_NO_ELE_TYPE_ID'
967 );
968 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
969 p_status => g_trx_error,
970 p_exception => fnd_message.get
971 );
972 RAISE e_continue;
973 END extract_data_from_attr_tbl;
974
975 PROCEDURE parse_cost_flex (
976 p_business_group_id IN pay_batch_headers.business_group_id%TYPE,
977 p_bee_rec IN OUT NOCOPY hxt_interface_utilities.bee_rec
978 )
979 AS
980 l_proc CONSTANT proc_name := g_package || 'parse_cost_flex';
981 BEGIN
982 hr_utility.set_location ('Entering ' || l_proc, 10);
983
984 IF NOT (hxt_interface_utilities.cost_segments_all_null (p_bee_rec))
985 THEN
986 -- get the cost_allocation_flexfield_id (we can do this after the COST
987 -- segments have been set) ...
988 p_bee_rec.pay_batch_line.cost_allocation_keyflex_id :=
989 hxt_interface_utilities.cost_allocation_kff_id
990 (p_business_group_id => p_business_group_id,
991 p_bee_rec => p_bee_rec
992 );
993 -- ... and the concatinated segments
994 p_bee_rec.pay_batch_line.concatenated_segments :=
995 hxt_interface_utilities.costflex_concat_segments
996 (p_cost_allocation_keyflex_id => p_bee_rec.pay_batch_line.cost_allocation_keyflex_id
997 );
998 ELSE
999 p_bee_rec.pay_batch_line.cost_allocation_keyflex_id := NULL;
1000 p_bee_rec.pay_batch_line.concatenated_segments := NULL;
1001 END IF;
1002
1003 hr_utility.set_location ('Leaving ' || l_proc, 100);
1004 END parse_cost_flex;
1005
1006 PROCEDURE bee_batch_line (
1007 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1008 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks,
1009 p_det_tbb_idx IN PLS_INTEGER,
1010 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
1011 p_attr_tbl_idx IN OUT NOCOPY PLS_INTEGER,
1012 p_bee_rec OUT NOCOPY hxt_interface_utilities.bee_rec,
1013 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
1014 p_is_old IN BOOLEAN DEFAULT FALSE
1015 )
1016 AS
1017 l_proc CONSTANT proc_name := g_package || 'bee_batch_line';
1018 l_effective_date DATE := TRUNC (p_tbb_rec.start_time);
1019 e_no_assignment EXCEPTION;
1020 l_geocode VARCHAR2 (21);
1021 l_hours_iv_position PLS_INTEGER;
1022 l_jurisdiction_iv_position PLS_INTEGER;
1023 BEGIN
1024 hr_utility.set_location ('Entering ' || l_proc, 10);
1025 extract_data_from_attr_tbl (p_bg_id => p_bg_id,
1026 p_attr_tbl => p_attr_tbl,
1027 p_tbb_id => p_tbb_rec.bb_id,
1028 p_det_tbb_idx => p_det_tbb_idx,
1029 p_cost_flex_id => p_cost_flex_id,
1030 p_effective_date => l_effective_date,
1031 p_attr_tbl_idx => p_attr_tbl_idx,
1032 p_bee_rec => p_bee_rec,
1033 p_is_old => p_is_old
1034 );
1035 -- get input value sequence for Hours and Jurisdiction
1036 hxt_interface_utilities.hours_iv_position
1037 (p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
1038 p_effective_date => l_effective_date,
1039 p_hours_iv_position => l_hours_iv_position,
1040 p_jurisdiction_iv_position => l_jurisdiction_iv_position,
1041 p_iv_type => hxt_interface_utilities.g_hour_juris_iv
1042 );
1043 -- Assign Hours to the right input value
1044
1045 -- Bug 13777315
1046 -- If IV Upgrade is complete, pass the Canonical format.
1047 -- Else pass the number as is, and let it get converted to CHAR implicitly.
1048 IF g_iv_upgrade = 'Y'
1049 THEN
1050 hxt_interface_utilities.assign_iv
1051 (p_iv_seq => l_hours_iv_position,
1052 p_value => FND_NUMBER.NUMBER_TO_CANONICAL( (p_tbb_rec.measure --Days Vs Hour Enhancement
1053 )
1054 * hxt_interface_utilities.hours_factor
1055 (p_is_old)),
1056 p_bee_rec => p_bee_rec
1057 );
1058
1059 ELSE
1060 hxt_interface_utilities.assign_iv
1061 (p_iv_seq => l_hours_iv_position,
1062 p_value => (p_tbb_rec.measure --Days Vs Hour Enhancement
1063 )
1064 * hxt_interface_utilities.hours_factor
1065 (p_is_old),
1066 p_bee_rec => p_bee_rec
1067 );
1068 END IF;
1069
1070
1071 -- get geocode if its input value sequence is not null
1072 IF (l_jurisdiction_iv_position IS NOT NULL)
1073 THEN
1074 l_geocode :=
1075 hxt_interface_utilities.get_geocode_from_attr_tab
1076 (p_attr_tbl,
1077 p_tbb_rec.bb_id,
1078 NULL
1079 );
1080
1081 IF (l_geocode <> '00-000-0000')
1082 THEN
1083 hxt_interface_utilities.assign_iv
1084 (p_iv_seq => l_jurisdiction_iv_position,
1085 p_value => l_geocode,
1086 p_bee_rec => p_bee_rec
1087 );
1088 END IF;
1089 END IF;
1090
1091 -- set the assignment if it has not bee set yet (i.e. there was no
1092 -- assignment attribute)
1093 IF (p_bee_rec.pay_batch_line.assignment_id IS NULL)
1094 THEN
1095 hxt_interface_utilities.assignment_info
1096 (p_tbb_rec => p_tbb_rec,
1097 p_assignment_id => p_bee_rec.pay_batch_line.assignment_id,
1098 p_assignment_number => p_bee_rec.pay_batch_line.assignment_number
1099 );
1100 END IF;
1101
1102 IF (p_bee_rec.pay_batch_line.assignment_id IS NULL)
1103 THEN
1104 RAISE e_no_assignment;
1105 END IF;
1106
1107 parse_cost_flex (p_business_group_id => p_bg_id,
1108 p_bee_rec => p_bee_rec);
1109 hr_utility.set_location ('Leaving ' || l_proc, 100);
1110 EXCEPTION
1111 WHEN e_no_assignment
1112 THEN
1113 fnd_message.set_name (g_hxc_app_short_name,
1114 'HXC_HRPAY_RET_NO_ASSIGN'
1115 );
1116 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
1117 p_status => g_trx_error,
1118 p_exception => fnd_message.get
1119 );
1120 RAISE e_continue;
1121 END bee_batch_line;
1122
1123 FUNCTION batch_name (
1124 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1125 p_bg_id IN pay_batch_headers.business_group_id%TYPE
1126 )
1127 RETURN pay_batch_headers.batch_name%TYPE
1128 AS
1129 l_proc CONSTANT proc_name := g_package || 'batch_name';
1130 l_batch_suffix pay_batch_headers.batch_name%TYPE; -- NUMBER;
1131 l_batch_name pay_batch_headers.batch_name%TYPE;
1132 BEGIN
1133 hr_utility.set_location ('Entering:' || l_proc, 10);
1134 l_batch_suffix :=
1135 TO_CHAR (hxt_interface_utilities.conc_request_id_suffix);
1136 l_batch_suffix :=
1137 l_batch_suffix
1138 || hxt_interface_utilities.batchname_suffix_connector
1139 || hxt_interface_utilities.free_batch_suffix
1140 (p_batch_reference => p_batch_reference,
1141 p_bg_id => p_bg_id
1142 );
1143 l_batch_name :=
1144 p_batch_reference
1145 || hxt_interface_utilities.batchname_suffix_connector
1146 || l_batch_suffix;
1147 hr_utility.set_location (' returning batch name: ' || l_batch_name,
1148 20);
1149 hr_utility.set_location ('Leaving:' || l_proc, 100);
1150 RETURN l_batch_name;
1151 END batch_name;
1152
1153 FUNCTION create_batch_header (
1154 p_batch_name IN pay_batch_headers.batch_name%TYPE,
1155 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1156 p_batch_source IN pay_batch_headers.batch_source%TYPE
1157 DEFAULT g_time_store_batch_source,
1158 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1159 p_session_date IN DATE,
1160 p_det_tbb_idx IN PLS_INTEGER
1161 )
1162 RETURN pay_batch_headers.batch_id%TYPE
1163 AS
1164 l_proc CONSTANT proc_name
1165 := g_package || 'create_batch_header';
1166 l_object_version_number pay_batch_headers.object_version_number%TYPE;
1167 l_new_batch pay_batch_headers.batch_id%TYPE;
1168 BEGIN
1169 hr_utility.set_location ('Entering:' || l_proc, 10);
1170 pay_batch_element_entry_api.create_batch_header
1171 (p_session_date => p_session_date,
1172 p_batch_name => p_batch_name,
1173 p_business_group_id => p_bg_id,
1174 p_action_if_exists => g_insert_if_exist,
1175 p_batch_reference => p_batch_reference,
1176 p_batch_source => p_batch_source,
1177 p_batch_id => l_new_batch,
1178 p_object_version_number => l_object_version_number
1179 );
1180 record_batch_info (p_batch_id => l_new_batch,
1181 p_business_group_id => p_bg_id,
1182 p_batch_reference => p_batch_reference,
1183 p_batch_name => p_batch_name
1184 );
1185 hr_utility.set_location (' returning batch_id = :' || l_new_batch, 20);
1186 hr_utility.set_location ('Leaving:' || l_proc, 100);
1187 RETURN l_new_batch;
1188 EXCEPTION
1189 WHEN OTHERS
1190 THEN
1191 hr_utility.trace(dbms_utility.format_error_backtrace);
1192 fnd_message.set_name (g_hxc_app_short_name,
1193 'HXC_HRPAY_RET_BATCH_HDR_API'
1194 );
1195 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
1196 p_status => g_trx_error,
1197 p_exception => fnd_message.get
1198 );
1199 RAISE e_halt;
1200 END create_batch_header;
1201
1202 FUNCTION create_batch_header (
1203 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1204 p_batch_source IN pay_batch_headers.batch_source%TYPE
1205 DEFAULT g_time_store_batch_source,
1206 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1207 p_session_date IN DATE,
1208 p_det_tbb_idx IN PLS_INTEGER
1209 )
1210 RETURN pay_batch_headers.batch_id%TYPE
1211 AS
1212 l_proc CONSTANT proc_name
1213 := g_package || 'create_batch_header';
1214 l_object_version_number pay_batch_headers.object_version_number%TYPE;
1215 l_batch_name pay_batch_headers.batch_name%TYPE;
1216 l_new_batch pay_batch_headers.batch_id%TYPE;
1217 BEGIN
1218 hr_utility.set_location ('Entering:' || l_proc, 10);
1219 l_batch_name :=
1220 batch_name (p_batch_reference => p_batch_reference,
1221 p_bg_id => p_bg_id
1222 );
1223 -- I might need to add a check here for max_lines_exceeded if I add
1224 -- functionality to add data to existing batches...
1225 l_new_batch :=
1226 create_batch_header (p_batch_name => l_batch_name,
1227 p_batch_reference => p_batch_reference,
1228 p_batch_source => p_batch_source,
1229 p_bg_id => p_bg_id,
1230 p_session_date => p_session_date,
1231 p_det_tbb_idx => p_det_tbb_idx
1232 );
1233 hr_utility.set_location (' returning batch_id = :' || l_new_batch, 20);
1234 hr_utility.set_location ('Leaving:' || l_proc, 100);
1235 RETURN l_new_batch;
1236 END create_batch_header;
1237
1238 -- Bug 9494444
1239 -- Added new paramter to separately record the batch and line
1240 -- information for a retro batch.
1241 PROCEDURE create_batch_line (
1242 p_batch_id IN pay_batch_headers.batch_id%TYPE,
1243 p_det_tbb_idx IN PLS_INTEGER,
1244 p_session_date IN DATE,
1245 p_effective_date IN DATE,
1246 p_batch_sequence IN pay_batch_lines.batch_sequence%TYPE,
1247 p_bee_rec IN hxt_interface_utilities.bee_rec,
1248 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1249 p_is_retro IN BOOLEAN DEFAULT FALSE -- Bug 9494444
1250 )
1251 AS
1252 l_proc CONSTANT proc_name := g_package || 'create_batch_line';
1253 l_total_lines NUMBER;
1254 l_batch_line_id pay_batch_lines.batch_line_id%TYPE;
1255 l_object_version_number pay_batch_lines.object_version_number%TYPE;
1256 BEGIN
1257 hr_utility.set_location ('Entering:' || l_proc, 10);
1258
1259 -- Bug 13777315
1260 -- IF IV Upgrade is done, there is no need to call any function to convert any
1261 -- values; IVs will all go in Internal format, as is.
1262 IF g_iv_upgrade = 'Y'
1263 THEN
1264
1265 pay_batch_element_entry_api.create_batch_line
1266 (p_session_date => p_session_date,
1267 p_batch_id => p_batch_id,
1268 p_assignment_id => p_bee_rec.pay_batch_line.assignment_id,
1269 p_assignment_number => p_bee_rec.pay_batch_line.assignment_number,
1270 p_batch_sequence => p_batch_sequence,
1271 p_concatenated_segments => p_bee_rec.pay_batch_line.concatenated_segments,
1272 p_cost_allocation_keyflex_id => p_bee_rec.pay_batch_line.cost_allocation_keyflex_id,
1273 p_date_earned => p_effective_date,
1274 p_effective_date => p_effective_date,
1275 p_effective_start_date => p_effective_date,
1276 p_effective_end_date => p_effective_date,
1277 p_element_name => p_bee_rec.pay_batch_line.element_name,
1278 p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
1279 p_segment1 => p_bee_rec.pay_batch_line.segment1,
1280 p_segment2 => p_bee_rec.pay_batch_line.segment2,
1281 p_segment3 => p_bee_rec.pay_batch_line.segment3,
1282 p_segment4 => p_bee_rec.pay_batch_line.segment4,
1283 p_segment5 => p_bee_rec.pay_batch_line.segment5,
1284 p_segment6 => p_bee_rec.pay_batch_line.segment6,
1285 p_segment7 => p_bee_rec.pay_batch_line.segment7,
1286 p_segment8 => p_bee_rec.pay_batch_line.segment8,
1287 p_segment9 => p_bee_rec.pay_batch_line.segment9,
1288 p_segment10 => p_bee_rec.pay_batch_line.segment10,
1289 p_segment11 => p_bee_rec.pay_batch_line.segment11,
1290 p_segment12 => p_bee_rec.pay_batch_line.segment12,
1291 p_segment13 => p_bee_rec.pay_batch_line.segment13,
1292 p_segment14 => p_bee_rec.pay_batch_line.segment14,
1293 p_segment15 => p_bee_rec.pay_batch_line.segment15,
1294 p_segment16 => p_bee_rec.pay_batch_line.segment16,
1295 p_segment17 => p_bee_rec.pay_batch_line.segment17,
1296 p_segment18 => p_bee_rec.pay_batch_line.segment18,
1297 p_segment19 => p_bee_rec.pay_batch_line.segment19,
1298 p_segment20 => p_bee_rec.pay_batch_line.segment20,
1299 p_segment21 => p_bee_rec.pay_batch_line.segment21,
1300 p_segment22 => p_bee_rec.pay_batch_line.segment22,
1301 p_segment23 => p_bee_rec.pay_batch_line.segment23,
1302 p_segment24 => p_bee_rec.pay_batch_line.segment24,
1303 p_segment25 => p_bee_rec.pay_batch_line.segment25,
1304 p_segment26 => p_bee_rec.pay_batch_line.segment26,
1305 p_segment27 => p_bee_rec.pay_batch_line.segment27,
1306 p_segment28 => p_bee_rec.pay_batch_line.segment28,
1307 p_segment29 => p_bee_rec.pay_batch_line.segment29,
1308 p_segment30 => p_bee_rec.pay_batch_line.segment30,
1309 p_value_1 => p_bee_rec.pay_batch_line.value_1,
1310 p_value_2 => p_bee_rec.pay_batch_line.value_2,
1311 p_value_3 => p_bee_rec.pay_batch_line.value_3,
1312 p_value_4 => p_bee_rec.pay_batch_line.value_4,
1313 p_value_5 => p_bee_rec.pay_batch_line.value_5,
1314 p_value_6 => p_bee_rec.pay_batch_line.value_6,
1315 p_value_7 => p_bee_rec.pay_batch_line.value_7,
1316 p_value_8 => p_bee_rec.pay_batch_line.value_8,
1317 p_value_9 => p_bee_rec.pay_batch_line.value_9,
1318 p_value_10 => p_bee_rec.pay_batch_line.value_10,
1319 p_value_11 => p_bee_rec.pay_batch_line.value_11,
1320 p_value_12 => p_bee_rec.pay_batch_line.value_12,
1321 p_value_13 => p_bee_rec.pay_batch_line.value_13,
1322 p_value_14 => p_bee_rec.pay_batch_line.value_14,
1323 p_value_15 => p_bee_rec.pay_batch_line.value_15,
1324 p_batch_line_id => l_batch_line_id,
1325 p_object_version_number => l_object_version_number,
1326 p_iv_all_internal_format => g_iv_format
1327 );
1328
1329 ELSE
1330
1331 pay_batch_element_entry_api.create_batch_line
1332 (p_session_date => p_session_date,
1333 p_batch_id => p_batch_id,
1334 p_assignment_id => p_bee_rec.pay_batch_line.assignment_id,
1335 p_assignment_number => p_bee_rec.pay_batch_line.assignment_number,
1336 p_batch_sequence => p_batch_sequence,
1337 p_concatenated_segments => p_bee_rec.pay_batch_line.concatenated_segments,
1338 p_cost_allocation_keyflex_id => p_bee_rec.pay_batch_line.cost_allocation_keyflex_id,
1339 p_date_earned => p_effective_date,
1340 p_effective_date => p_effective_date,
1341 p_effective_start_date => p_effective_date,
1342 p_effective_end_date => p_effective_date,
1343 p_element_name => p_bee_rec.pay_batch_line.element_name,
1344 p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
1345 p_segment1 => p_bee_rec.pay_batch_line.segment1,
1346 p_segment2 => p_bee_rec.pay_batch_line.segment2,
1347 p_segment3 => p_bee_rec.pay_batch_line.segment3,
1348 p_segment4 => p_bee_rec.pay_batch_line.segment4,
1349 p_segment5 => p_bee_rec.pay_batch_line.segment5,
1350 p_segment6 => p_bee_rec.pay_batch_line.segment6,
1351 p_segment7 => p_bee_rec.pay_batch_line.segment7,
1352 p_segment8 => p_bee_rec.pay_batch_line.segment8,
1353 p_segment9 => p_bee_rec.pay_batch_line.segment9,
1354 p_segment10 => p_bee_rec.pay_batch_line.segment10,
1355 p_segment11 => p_bee_rec.pay_batch_line.segment11,
1356 p_segment12 => p_bee_rec.pay_batch_line.segment12,
1357 p_segment13 => p_bee_rec.pay_batch_line.segment13,
1358 p_segment14 => p_bee_rec.pay_batch_line.segment14,
1359 p_segment15 => p_bee_rec.pay_batch_line.segment15,
1360 p_segment16 => p_bee_rec.pay_batch_line.segment16,
1361 p_segment17 => p_bee_rec.pay_batch_line.segment17,
1362 p_segment18 => p_bee_rec.pay_batch_line.segment18,
1363 p_segment19 => p_bee_rec.pay_batch_line.segment19,
1364 p_segment20 => p_bee_rec.pay_batch_line.segment20,
1365 p_segment21 => p_bee_rec.pay_batch_line.segment21,
1366 p_segment22 => p_bee_rec.pay_batch_line.segment22,
1367 p_segment23 => p_bee_rec.pay_batch_line.segment23,
1368 p_segment24 => p_bee_rec.pay_batch_line.segment24,
1369 p_segment25 => p_bee_rec.pay_batch_line.segment25,
1370 p_segment26 => p_bee_rec.pay_batch_line.segment26,
1371 p_segment27 => p_bee_rec.pay_batch_line.segment27,
1372 p_segment28 => p_bee_rec.pay_batch_line.segment28,
1373 p_segment29 => p_bee_rec.pay_batch_line.segment29,
1374 p_segment30 => p_bee_rec.pay_batch_line.segment30,
1375 p_value_1 => display_value
1376 (p_bee_rec.pay_batch_line.element_type_id,
1377 p_bee_rec.pay_batch_line.value_1,
1378 1,
1379 p_session_date,
1380 p_bg_id
1381 ),
1382 p_value_2 => display_value
1383 (p_bee_rec.pay_batch_line.element_type_id,
1384 p_bee_rec.pay_batch_line.value_2,
1385 2,
1386 p_session_date,
1387 p_bg_id
1388 ),
1389 p_value_3 => display_value
1390 (p_bee_rec.pay_batch_line.element_type_id,
1391 p_bee_rec.pay_batch_line.value_3,
1392 3,
1393 p_session_date,
1394 p_bg_id
1395 ),
1396 p_value_4 => display_value
1397 (p_bee_rec.pay_batch_line.element_type_id,
1398 p_bee_rec.pay_batch_line.value_4,
1399 4,
1400 p_session_date,
1401 p_bg_id
1402 ),
1403 p_value_5 => display_value
1404 (p_bee_rec.pay_batch_line.element_type_id,
1405 p_bee_rec.pay_batch_line.value_5,
1406 5,
1407 p_session_date,
1408 p_bg_id
1409 ),
1410 p_value_6 => display_value
1411 (p_bee_rec.pay_batch_line.element_type_id,
1412 p_bee_rec.pay_batch_line.value_6,
1413 6,
1414 p_session_date,
1415 p_bg_id
1416 ),
1417 p_value_7 => display_value
1418 (p_bee_rec.pay_batch_line.element_type_id,
1419 p_bee_rec.pay_batch_line.value_7,
1420 7,
1421 p_session_date,
1422 p_bg_id
1423 ),
1424 p_value_8 => display_value
1425 (p_bee_rec.pay_batch_line.element_type_id,
1426 p_bee_rec.pay_batch_line.value_8,
1427 8,
1428 p_session_date,
1429 p_bg_id
1430 ),
1431 p_value_9 => display_value
1432 (p_bee_rec.pay_batch_line.element_type_id,
1433 p_bee_rec.pay_batch_line.value_9,
1434 9,
1435 p_session_date,
1436 p_bg_id
1437 ),
1438 p_value_10 => display_value
1439 (p_bee_rec.pay_batch_line.element_type_id,
1440 p_bee_rec.pay_batch_line.value_10,
1441 10,
1442 p_session_date,
1443 p_bg_id
1444 ),
1445 p_value_11 => display_value
1446 (p_bee_rec.pay_batch_line.element_type_id,
1447 p_bee_rec.pay_batch_line.value_11,
1448 11,
1449 p_session_date,
1450 p_bg_id
1451 ),
1452 p_value_12 => display_value
1453 (p_bee_rec.pay_batch_line.element_type_id,
1454 p_bee_rec.pay_batch_line.value_12,
1455 12,
1456 p_session_date,
1457 p_bg_id
1458 ),
1459 p_value_13 => display_value
1460 (p_bee_rec.pay_batch_line.element_type_id,
1461 p_bee_rec.pay_batch_line.value_13,
1462 13,
1463 p_session_date,
1464 p_bg_id
1465 ),
1466 p_value_14 => display_value
1467 (p_bee_rec.pay_batch_line.element_type_id,
1468 p_bee_rec.pay_batch_line.value_14,
1469 14,
1470 p_session_date,
1471 p_bg_id
1472 ),
1473 p_value_15 => display_value
1474 (p_bee_rec.pay_batch_line.element_type_id,
1475 p_bee_rec.pay_batch_line.value_15,
1476 15,
1477 p_session_date,
1478 p_bg_id
1479 ),
1480 p_batch_line_id => l_batch_line_id,
1481 p_object_version_number => l_object_version_number
1482 );
1483
1484 END IF;
1485
1486 -- Bug 9494444
1487 -- Record the line and the batch in the respective table to be passed back to
1488 -- OTL.
1489 IF p_is_retro
1490 THEN
1491 hxc_generic_retrieval_pkg.t_old_detail_rec_lines(p_det_tbb_idx).rec_id := l_batch_line_id;
1492 hxc_generic_retrieval_pkg.t_old_detail_rec_lines(p_det_tbb_idx).batch_id := p_batch_id;
1493
1494 -- Bug 10323310
1495 IF g_old_pay_pa_link_tab.EXISTS(p_det_tbb_idx)
1496 THEN
1497 g_old_pay_pa_link_tab(p_det_tbb_idx).batch_id := p_batch_id;
1498 g_old_pay_pa_link_tab(p_det_tbb_idx).batch_line_id := l_batch_line_id;
1499 g_old_pay_pa_link_tab(p_det_tbb_idx).date_worked := p_effective_date;
1500 g_old_pay_pa_link_tab(p_det_tbb_idx).element_type_id:= p_bee_rec.pay_batch_line.element_type_id;
1501 END IF;
1502
1503 -- Bug 12919783
1504 IF NOT g_batch_tab.EXISTS(TO_CHAR(p_batch_id))
1505 THEN
1506 g_batch_tab(TO_CHAR(p_batch_id)) := 1;
1507 END IF;
1508
1509 ELSE
1510 hxc_generic_retrieval_pkg.t_detail_rec_lines(p_det_tbb_idx).rec_id := l_batch_line_id;
1511 hxc_generic_retrieval_pkg.t_detail_rec_lines(p_det_tbb_idx).batch_id := p_batch_id;
1512
1513 -- Bug 10323310
1514 IF g_pay_pa_link_tab.EXISTS(p_det_tbb_idx)
1515 THEN
1516 g_pay_pa_link_tab(p_det_tbb_idx).batch_id := p_batch_id;
1517 g_pay_pa_link_tab(p_det_tbb_idx).batch_line_id := l_batch_line_id;
1518 g_pay_pa_link_tab(p_det_tbb_idx).date_worked := p_effective_date;
1519 g_pay_pa_link_tab(p_det_tbb_idx).element_type_id:= p_bee_rec.pay_batch_line.element_type_id;
1520 END IF;
1521
1522 -- Bug 12919783
1523 IF NOT g_batch_tab.EXISTS(TO_CHAR(p_batch_id))
1524 THEN
1525 g_batch_tab(TO_CHAR(p_batch_id)) := 1;
1526 END IF;
1527 END IF;
1528
1529
1530
1531 hr_utility.set_location ('Leaving:' || l_proc, 100);
1532 EXCEPTION
1533 WHEN OTHERS
1534 THEN
1535 hr_utility.trace(dbms_utility.format_error_backtrace);
1536 fnd_message.set_name (g_hxc_app_short_name,
1537 'HXC_HRPAY_RET_BATCH_LINE_API'
1538 );
1539 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
1540 p_status => g_trx_error,
1541 p_exception => SQLERRM
1542 );
1543 RAISE e_continue;
1544 END create_batch_line;
1545
1546 -- Bug 9494444
1547 -- Added new parameter for marking retro lines.
1548 PROCEDURE add_to_batch (
1549 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1550 p_batch_id IN OUT NOCOPY pay_batch_headers.batch_id%TYPE,
1551 p_det_tbb_idx IN PLS_INTEGER,
1552 p_batch_sequence IN OUT NOCOPY pay_batch_lines.batch_sequence%TYPE,
1553 p_batch_lines IN OUT NOCOPY PLS_INTEGER,
1554 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1555 p_session_date IN DATE,
1556 p_effective_date IN DATE,
1557 p_bee_rec IN hxt_interface_utilities.bee_rec,
1558 p_is_retro IN BOOLEAN DEFAULT FALSE
1559 )
1560 AS
1561 l_proc CONSTANT proc_name := g_package || 'add_to_batch';
1562 l_max_lines_exceeded BOOLEAN;
1563 BEGIN
1564 hr_utility.set_location ('Entering:' || l_proc, 10);
1565
1566 IF (p_batch_id IS NULL)
1567 THEN -- first call
1568 p_batch_id :=
1569 create_batch_header (p_batch_reference => p_batch_reference,
1570 p_bg_id => p_bg_id,
1571 p_session_date => p_session_date,
1572 p_det_tbb_idx => p_det_tbb_idx
1573 );
1574 p_batch_sequence := 1;
1575 p_batch_lines := 0;
1576 ELSE
1577 IF ( g_assignment_id <> p_bee_rec.pay_batch_line.assignment_id
1578 AND g_assignment_id <> -1
1579 )
1580 THEN
1581 hxt_interface_utilities.max_lines_exceeded
1582 (p_batch_id => p_batch_id,
1583 p_number_lines => p_batch_lines,
1584 p_max_lines_exceeded => l_max_lines_exceeded
1585 );
1586
1587 IF (l_max_lines_exceeded)
1588 THEN
1589 p_batch_id :=
1590 create_batch_header
1591 (p_batch_reference => p_batch_reference,
1592 p_bg_id => p_bg_id,
1593 p_session_date => p_session_date,
1594 p_det_tbb_idx => p_det_tbb_idx
1595 );
1596 p_batch_sequence := 1;
1597 p_batch_lines := 0;
1598 END IF; -- max_lines_exceeded
1599 END IF; -- (p_batch_id IS NULL)
1600 END IF; -- g_assignment_id test
1601
1602 create_batch_line (p_batch_id => p_batch_id,
1603 p_det_tbb_idx => p_det_tbb_idx,
1604 p_session_date => p_session_date,
1605 p_effective_date => p_effective_date,
1606 p_batch_sequence => p_batch_sequence,
1607 p_bee_rec => p_bee_rec,
1608 p_bg_id => p_bg_id,
1609 p_is_retro => p_is_retro -- Bug 9394444
1610 );
1611 g_assignment_id := p_bee_rec.pay_batch_line.assignment_id;
1612 p_batch_lines := p_batch_lines + 1;
1613 p_batch_sequence := p_batch_sequence + 1;
1614 hr_utility.set_location (' OUT p_batch_id = ' || p_batch_id, 20);
1615 hr_utility.set_location (' OUT p_batch_sequence = '
1616 || p_batch_sequence,
1617 30
1618 );
1619 hr_utility.set_location (' OUT p_batch_lines = ' || p_batch_lines, 40);
1620 hr_utility.set_location ('Leaving:' || l_proc, 100);
1621 END add_to_batch;
1622
1623 PROCEDURE add_lines_to_bee_batch (
1624 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1625 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1626 p_session_date IN DATE,
1627 p_tbb_tbl IN hxc_generic_retrieval_pkg.t_building_blocks,
1628 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
1629 p_old_tbb_tbl IN hxc_generic_retrieval_pkg.t_building_blocks,
1630 p_old_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
1631 p_batch_id IN OUT NOCOPY NUMBER,
1632 p_retro_batch_id IN OUT NOCOPY NUMBER
1633 )
1634 AS
1635 l_proc CONSTANT proc_name
1636 := g_package || 'add_lines_to_bee_batch';
1637 l_det_tbb_idx PLS_INTEGER;
1638 l_det_old_tbb_idx PLS_INTEGER := 0;
1639 l_det_attr_idx PLS_INTEGER;
1640 l_det_old_attr_idx PLS_INTEGER;
1641 l_bee_rec hxt_interface_utilities.bee_rec;
1642 l_old_bee_rec hxt_interface_utilities.bee_rec;
1643 l_empty_bee_rec hxt_interface_utilities.bee_rec;
1644 l_cost_flex_id per_business_groups_perf.cost_allocation_structure%TYPE
1645 := hxt_interface_utilities.cost_flex_structure_id (p_bg_id);
1646 l_batch_id pay_batch_headers.batch_reference%TYPE;
1647 l_batch_sequence pay_batch_lines.batch_sequence%TYPE;
1648 l_retro_batch_id pay_batch_headers.batch_reference%TYPE;
1649 l_retro_batch_sequence pay_batch_lines.batch_sequence%TYPE;
1650 l_batch_lines PLS_INTEGER;
1651 l_retro_batch_lines PLS_INTEGER;
1652 i BINARY_INTEGER;
1653 l_space VARCHAR2(15) := ' |';
1654 l_line VARCHAR2(15) := '=====|';
1655 BEGIN
1656 hr_utility.set_location ('Entering ' || l_proc, 10);
1657 l_batch_id := p_batch_id;
1658 l_retro_batch_id := p_retro_batch_id;
1659 l_det_tbb_idx := p_tbb_tbl.FIRST;
1660
1661 <<process_all_detail_tbb>>
1662 LOOP
1663
1664 <<processing_tbb>>
1665 BEGIN
1666 EXIT process_all_detail_tbb WHEN NOT p_tbb_tbl.EXISTS
1667 (l_det_tbb_idx);
1668
1669 -- Bug 10323310
1670 cache_preferences(p_tbb_tbl(l_det_tbb_idx).resource_id,
1671 g_start_date,
1672 g_end_date);
1673
1674 IF (hxt_interface_utilities.is_changed (p_tbb_tbl (l_det_tbb_idx))
1675 )
1676 THEN -- get previously transferred line
1677 l_det_old_tbb_idx := l_det_old_tbb_idx + 1;
1678
1679 IF (hxt_interface_utilities.is_in_sync
1680 (p_check_tbb_id => p_old_tbb_tbl
1681 (l_det_old_tbb_idx).bb_id,
1682 p_against_tbb_id => p_tbb_tbl (l_det_tbb_idx).bb_id
1683 )
1684 )
1685 THEN
1686
1687 -- Bug 10323310
1688 IF chk_intg_enabled(p_old_tbb_tbl(l_det_old_tbb_idx).resource_id,
1689 TRUNC(p_old_tbb_tbl(l_det_old_tbb_idx).start_time))
1690 THEN
1691 g_old_pay_pa_link_tab(l_det_tbb_idx).object_version_number
1692 := p_old_tbb_tbl(l_det_old_tbb_idx).ovn;
1693 g_old_pay_pa_link_tab(l_det_tbb_idx).time_building_block_id
1694 := p_old_tbb_tbl(l_det_old_tbb_idx).bb_id;
1695 g_old_pay_pa_link_tab(l_det_tbb_idx).timecard_id
1696 := p_old_tbb_tbl(l_det_old_tbb_idx).timecard_bb_id;
1697 END IF;
1698
1699
1700 bee_batch_line
1701 (p_bg_id => p_bg_id,
1702 p_tbb_rec => p_old_tbb_tbl
1703 (l_det_old_tbb_idx),
1704 p_det_tbb_idx => l_det_old_tbb_idx,
1705 p_attr_tbl => p_old_attr_tbl,
1706 p_attr_tbl_idx => l_det_old_attr_idx,
1707 p_bee_rec => l_old_bee_rec,
1708 p_cost_flex_id => l_cost_flex_id,
1709 p_is_old => TRUE
1710 );
1711 -- add to retro batch for backing out
1712 -- Bug 9494444
1713 -- This is a call for retro batch, so pass the flag.
1714 add_to_batch
1715 (p_batch_reference => p_batch_reference
1716 || retro_batch_suffix,
1717 p_batch_id => l_retro_batch_id,
1718 p_det_tbb_idx => l_det_old_tbb_idx,
1719 p_batch_sequence => l_retro_batch_sequence,
1720 p_batch_lines => l_retro_batch_lines,
1721 p_bg_id => p_bg_id,
1722 p_session_date => p_session_date,
1723 p_effective_date => TRUNC
1724 (p_old_tbb_tbl
1725 (l_det_old_tbb_idx).start_time
1726 ),
1727 p_bee_rec => l_old_bee_rec,
1728 p_is_retro => TRUE -- Bug 9494444
1729 );
1730
1731 IF NOT (hxt_interface_utilities.is_deleted
1732 (p_tbb_tbl (l_det_tbb_idx)
1733 )
1734 )
1735 THEN -- must be an update to an existing BEE entry
1736 IF chk_intg_enabled( p_tbb_tbl(l_det_tbb_idx).resource_id,
1737 TRUNC( p_tbb_tbl(l_det_tbb_idx).start_time))
1738 THEN
1739 g_pay_pa_link_tab(l_det_tbb_idx).object_version_number
1740 := p_tbb_tbl(l_det_tbb_idx).ovn;
1741 g_pay_pa_link_tab(l_det_tbb_idx).time_building_block_id
1742 := p_tbb_tbl(l_det_tbb_idx).bb_id;
1743 g_pay_pa_link_tab(l_det_tbb_idx).timecard_id
1744 := p_tbb_tbl(l_det_tbb_idx).timecard_bb_id;
1745 END IF;
1746
1747 bee_batch_line (p_bg_id => p_bg_id,
1748 p_tbb_rec => p_tbb_tbl
1749 (l_det_tbb_idx),
1750 p_det_tbb_idx => l_det_tbb_idx,
1751 p_attr_tbl => p_attr_tbl,
1752 p_attr_tbl_idx => l_det_attr_idx,
1753 p_bee_rec => l_bee_rec,
1754 p_cost_flex_id => l_cost_flex_id
1755 );
1756
1757 -- Temporary switch allowing simulation of old usage
1758 -- Old usage = New and Update rows go into New Batch
1759 -- Previously Transferred rows go into Retro Batch
1760 -- New usage = New rows go into New Batch
1761 -- Previously Transferred and Updated rows go into
1762 -- Retro Batch
1763 IF (hxt_interface_utilities.use_old_retro_batches)
1764 THEN
1765 add_to_batch
1766 (p_batch_reference => p_batch_reference,
1767 p_batch_id => l_batch_id,
1768 p_det_tbb_idx => l_det_tbb_idx,
1769 p_batch_sequence => l_batch_sequence,
1770 p_batch_lines => l_batch_lines,
1771 p_bg_id => p_bg_id,
1772 p_session_date => p_session_date,
1773 p_effective_date => TRUNC
1774 (p_tbb_tbl
1775 (l_det_tbb_idx).start_time
1776 ),
1777 p_bee_rec => l_bee_rec
1778 );
1779 ELSE
1780 add_to_batch
1781 (p_batch_reference => p_batch_reference
1782 || retro_batch_suffix,
1783 p_batch_id => l_retro_batch_id,
1784 p_det_tbb_idx => l_det_tbb_idx,
1785 p_batch_sequence => l_retro_batch_sequence,
1786 p_batch_lines => l_retro_batch_lines,
1787 p_bg_id => p_bg_id,
1788 p_session_date => p_session_date,
1789 p_effective_date => TRUNC
1790 (p_tbb_tbl
1791 (l_det_tbb_idx).start_time
1792 ),
1793 p_bee_rec => l_bee_rec
1794 );
1795 END IF;
1796 ELSE
1797 -- Delete, so we only need to backout the previously
1798 -- transferred data which we already did so nothing to do
1799 l_det_attr_idx :=
1800 hxt_interface_utilities.skip_attributes
1801 (p_att_table => p_attr_tbl,
1802 p_tbb_id => p_tbb_tbl
1803 (l_det_tbb_idx).bb_id,
1804 p_start_position => l_det_attr_idx
1805 );
1806 l_bee_rec := l_empty_bee_rec;
1807 END IF;
1808 ELSE -- IF (hxt_interface_utilities.is_in_sync ...)
1809 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1810 fnd_message.set_token ('PROCEDURE', l_proc);
1811 fnd_message.set_token ('STEP', 'tbb mismatch');
1812 fnd_message.raise_error;
1813 END IF;
1814 ELSE -- new record (i.e. never transferred to BEE before)
1815 IF chk_intg_enabled( p_tbb_tbl(l_det_tbb_idx).resource_id,
1816 TRUNC( p_tbb_tbl(l_det_tbb_idx).start_time))
1817 THEN
1818 g_pay_pa_link_tab(l_det_tbb_idx).object_version_number
1819 := p_tbb_tbl(l_det_tbb_idx).ovn;
1820 g_pay_pa_link_tab(l_det_tbb_idx).time_building_block_id
1821 := p_tbb_tbl(l_det_tbb_idx).bb_id;
1822 g_pay_pa_link_tab(l_det_tbb_idx).timecard_id
1823 := p_tbb_tbl(l_det_tbb_idx).timecard_bb_id;
1824 END IF;
1825
1826 IF NOT (hxt_interface_utilities.is_deleted
1827 (p_tbb_tbl (l_det_tbb_idx)
1828 )
1829 )
1830 THEN
1831 bee_batch_line (p_bg_id => p_bg_id,
1832 p_tbb_rec => p_tbb_tbl
1833 (l_det_tbb_idx),
1834 p_det_tbb_idx => l_det_tbb_idx,
1835 p_attr_tbl => p_attr_tbl,
1836 p_attr_tbl_idx => l_det_attr_idx,
1837 p_bee_rec => l_bee_rec,
1838 p_cost_flex_id => l_cost_flex_id
1839 );
1840 add_to_batch
1841 (p_batch_reference => p_batch_reference,
1842 p_batch_id => l_batch_id,
1843 p_det_tbb_idx => l_det_tbb_idx,
1844 p_batch_sequence => l_batch_sequence,
1845 p_batch_lines => l_batch_lines,
1846 p_bg_id => p_bg_id,
1847 p_session_date => p_session_date,
1848 p_effective_date => TRUNC
1849 (p_tbb_tbl (l_det_tbb_idx).start_time
1850 ),
1851 p_bee_rec => l_bee_rec
1852 );
1853 ELSE
1854 -- deleted but never existed in BEE so we don't have to do anything
1855 l_det_attr_idx :=
1856 hxt_interface_utilities.skip_attributes
1857 (p_att_table => p_attr_tbl,
1858 p_tbb_id => p_tbb_tbl
1859 (l_det_tbb_idx).bb_id,
1860 p_start_position => l_det_attr_idx
1861 );
1862 l_bee_rec := l_empty_bee_rec;
1863 END IF;
1864 END IF;
1865
1866 set_successfull_trx_detail (p_tbb_idx => l_det_tbb_idx);
1867 l_det_tbb_idx := p_tbb_tbl.NEXT (l_det_tbb_idx);
1868 EXCEPTION
1869 WHEN e_continue
1870 THEN
1871 l_det_attr_idx :=
1872 hxt_interface_utilities.skip_attributes
1873 (p_att_table => p_attr_tbl,
1874 p_tbb_id => p_tbb_tbl
1875 (l_det_tbb_idx).bb_id,
1876 p_start_position => l_det_attr_idx
1877 );
1878 l_det_old_attr_idx :=
1879 hxt_interface_utilities.skip_attributes
1880 (p_att_table => p_old_attr_tbl,
1881 p_tbb_id => p_tbb_tbl
1882 (l_det_tbb_idx).bb_id,
1883 p_start_position => l_det_old_attr_idx
1884 );
1885 l_bee_rec := l_empty_bee_rec;
1886 l_det_tbb_idx := p_tbb_tbl.NEXT (l_det_tbb_idx);
1887 WHEN e_halt
1888 THEN
1889 RAISE;
1890 WHEN OTHERS
1891 THEN
1892 set_sqlerror_trx_detail (p_tbb_idx => l_det_tbb_idx);
1893 l_det_tbb_idx := p_tbb_tbl.NEXT (l_det_tbb_idx);
1894 hr_utility.trace(dbms_utility.format_error_backtrace);
1895 END processing_tbb;
1896 END LOOP process_all_detail_tbb;
1897
1898 set_successfull_trx (g_bee_retrieval_process);
1899 hr_utility.trace('Yeah it came here ');
1900 IF g_pay_pa_link_tab.COUNT > 0
1901 THEN
1902
1903 FND_file.new_line(fnd_file.log);
1904 FND_file.new_line(fnd_file.log);
1905 FND_FILE.put_line(fnd_file.log,
1906 l_space||'=====================================================================================================|');
1907 FND_file.put_line(fnd_file.log,
1908 ' The following details are recorded in the Interface table for Projects Import ');
1909 FND_FILE.put_line(fnd_file.log,
1910 l_space||'=====================================================================================================|');
1911 FND_file.new_line(fnd_file.log);
1912 FND_file.new_line(fnd_file.log);
1913
1914 FND_file.put_line(fnd_file.log,
1915 ' ======= NEW BATCH LINES FOR FRESH DETAILS ========= ');
1916
1917 FORALL i IN INDICES OF g_pay_pa_link_tab
1918 INSERT INTO hxc_pay_pa_link_all
1919 VALUES g_pay_pa_link_tab(i);
1920
1921
1922 fnd_file.put_line(fnd_file.log,l_space||RPAD('==========',10)||
1923 l_line||
1924 RPAD('========',5)||
1925 l_line||
1926 RPAD('==========',10)||
1927 l_line||
1928 RPAD('==========',10)||
1929 l_line||
1930 RPAD('==========',10)||
1931 l_line||
1932 RPAD('==========',10)||
1933 l_line||
1934 RPAD('==========',10)||'|');
1935
1936
1937 fnd_file.put_line(fnd_file.log,l_space||RPAD('TBB_ID',10)||
1938 l_space||
1939 RPAD('OVN',5)||
1940 l_space||
1941 RPAD('BATCH_ID',10)||
1942 l_space||
1943 RPAD('BATCH_LINE_ID',10)||
1944 l_space||
1945 RPAD('ELEMENT',10)||
1946 l_space||
1947 RPAD('PROJECT',10)||
1948 l_space||
1949 RPAD('TASK',10)||'|');
1950
1951 fnd_file.put_line(fnd_file.log,l_space||RPAD('==========',10)||
1952 l_line||
1953 RPAD('========',5)||
1954 l_line||
1955 RPAD('==========',10)||
1956 l_line||
1957 RPAD('==========',10)||
1958 l_line||
1959 RPAD('==========',10)||
1960 l_line||
1961 RPAD('==========',10)||
1962 l_line||
1963 RPAD('==========',10)||'|');
1964
1965
1966 i := g_pay_pa_link_tab.FIRST;
1967 LOOP
1968 fnd_file.put_line(fnd_file.log,l_space||RPAD(g_pay_pa_link_tab(i).time_building_block_id,10)||
1969 l_space||
1970 RPAD(g_pay_pa_link_tab(i).object_version_number,5)||
1971 l_space||
1972 RPAD(g_pay_pa_link_tab(i).batch_id,10)||
1973 l_space||
1974 RPAD(g_pay_pa_link_tab(i).batch_line_id,10)||
1975 l_space||
1976 RPAD(g_pay_pa_link_tab(i).element_type_id,10)||
1977 l_space||
1978 RPAD(g_pay_pa_link_tab(i).project_id,10)||
1979 l_space||
1980 RPAD(g_pay_pa_link_tab(i).task_id,10)||'|');
1981 i := g_pay_pa_link_tab.NEXT(i);
1982 EXIT WHEN NOT g_pay_pa_link_tab.EXISTS(i);
1983 END LOOP;
1984 fnd_file.put_line(fnd_file.log,l_space||RPAD('==========',10)||
1985 l_line||
1986 RPAD('========',5)||
1987 l_line||
1988 RPAD('==========',10)||
1989 l_line||
1990 RPAD('==========',10)||
1991 l_line||
1992 RPAD('==========',10)||
1993 l_line||
1994 RPAD('==========',10)||
1995 l_line||
1996 RPAD('==========',10)||'|');
1997
1998
1999 FND_file.new_line(fnd_file.log);
2000 FND_file.new_line(fnd_file.log);
2001
2002 END IF;
2003
2004 FND_file.new_line(fnd_file.log);
2005 FND_file.new_line(fnd_file.log);
2006
2007 IF g_old_pay_pa_link_tab.COUNT > 0
2008 THEN
2009
2010
2011 FND_file.new_line(fnd_file.log);
2012 FND_file.new_line(fnd_file.log);
2013
2014 FND_file.put_line(fnd_file.log,
2015 ' ======= RETRO BATCH LINES FOR PREVIOUSLY TRANSFERRED DETAILS ========= ');
2016
2017 FORALL i IN INDICES OF g_old_pay_pa_link_tab
2018 INSERT INTO hxc_pay_pa_link_all
2019 values g_old_pay_pa_link_tab(i);
2020
2021
2022 fnd_file.put_line(fnd_file.log,l_space||RPAD('==========',10)||
2023 l_line||
2024 RPAD('========',5)||
2025 l_line||
2026 RPAD('==========',10)||
2027 l_line||
2028 RPAD('==========',10)||
2029 l_line||
2030 RPAD('==========',10)||
2031 l_line||
2032 RPAD('==========',10)||
2033 l_line||
2034 RPAD('==========',10)||'|');
2035
2036
2037 fnd_file.put_line(fnd_file.log,l_space||RPAD('TBB_ID',10)||
2038 l_space||
2039 RPAD('OVN',5)||
2040 l_space||
2041 RPAD('BATCH_ID',10)||
2042 l_space||
2043 RPAD('BATCH_LINE_ID',10)||
2044 l_space||
2045 RPAD('ELEMENT',10)||
2046 l_space||
2047 RPAD('PROJECT',10)||
2048 l_space||
2049 RPAD('TASK',10)||'|');
2050
2051 fnd_file.put_line(fnd_file.log,l_space||RPAD('==========',10)||
2052 l_line||
2053 RPAD('========',5)||
2054 l_line||
2055 RPAD('==========',10)||
2056 l_line||
2057 RPAD('==========',10)||
2058 l_line||
2059 RPAD('==========',10)||
2060 l_line||
2061 RPAD('==========',10)||
2062 l_line||
2063 RPAD('==========',10)||'|');
2064
2065
2066
2067 i := g_old_pay_pa_link_tab.FIRST;
2068 LOOP
2069 fnd_file.put_line(fnd_file.log,l_space||RPAD(g_old_pay_pa_link_tab(i).time_building_block_id,10)||
2070 l_space||
2071 RPAD(g_old_pay_pa_link_tab(i).object_version_number,5)||
2072 l_space||
2073 RPAD(g_old_pay_pa_link_tab(i).batch_id,10)||
2074 l_space||
2075 RPAD(g_old_pay_pa_link_tab(i).batch_line_id,10)||
2076 l_space||
2077 RPAD(g_old_pay_pa_link_tab(i).element_type_id,10)||
2078 l_space||
2079 RPAD(g_old_pay_pa_link_tab(i).project_id,10)||
2080 l_space||
2081 RPAD(g_old_pay_pa_link_tab(i).task_id,10)||'|');
2082 i := g_old_pay_pa_link_tab.NEXT(i);
2083 EXIT WHEN NOT g_old_pay_pa_link_tab.EXISTS(i);
2084 END LOOP;
2085
2086 fnd_file.put_line(fnd_file.log,l_space||RPAD('==========',10)||
2087 l_line||
2088 RPAD('========',5)||
2089 l_line||
2090 RPAD('==========',10)||
2091 l_line||
2092 RPAD('==========',10)||
2093 l_line||
2094 RPAD('==========',10)||
2095 l_line||
2096 RPAD('==========',10)||
2097 l_line||
2098 RPAD('==========',10)||'|');
2099
2100
2101 FND_file.new_line(fnd_file.log);
2102 FND_file.new_line(fnd_file.log);
2103
2104
2105 END IF;
2106
2107 hxt_interface_utilities.perform_commit;
2108 p_batch_id := l_batch_id;
2109 p_retro_batch_id := l_retro_batch_id;
2110 hr_utility.set_location ('Leaving ' || l_proc, 100);
2111 END add_lines_to_bee_batch;
2112
2113 PROCEDURE process_non_otlr_employees (
2114 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
2115 p_start_date IN hxc_time_building_blocks.start_time%TYPE,
2116 p_end_date IN hxc_time_building_blocks.stop_time%TYPE,
2117 p_session_date IN DATE,
2118 p_where_clause IN hxt_interface_utilities.max_varchar,
2119 p_retrieval_transaction_code IN hxc_transactions.transaction_code%TYPE,
2120 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
2121 p_unique_params IN hxt_interface_utilities.max_varchar,
2122 p_status_in_bee IN VARCHAR2,
2123 p_incremental IN hxt_interface_utilities.flag_varchar
2124 DEFAULT 'Y',
2125 p_transfer_to_bee IN hxt_interface_utilities.flag_varchar
2126 DEFAULT 'N',
2127 p_no_otm IN hxt_interface_utilities.flag_varchar
2128 DEFAULT 'N'
2129 )
2130 AS
2131 l_proc CONSTANT proc_name
2132 := g_package || 'process_non_otlr_employees';
2133 l_supa_chunk_batch_id pay_batch_headers.batch_reference%TYPE
2134 := NULL;
2135 l_supa_chunk_retro_batch_id pay_batch_headers.batch_reference%TYPE
2136 := NULL;
2137
2138 l_dup_count NUMBER; -- Bug 6121705
2139
2140 BEGIN
2141 hr_utility.set_location ('Entering ' || l_proc, 10);
2142
2143 -- Bug 10323310
2144 g_start_date := TRUNC(p_start_date);
2145 g_end_date := TRUNC(p_end_date);
2146
2147 <<retrieve_in_chuncks>>
2148 LOOP
2149
2150 -- Bug 10323310
2151 g_pay_pa_link_tab.DELETE;
2152 g_old_pay_pa_link_tab.DELETE;
2153
2154 -- call generic retrieval to get the non-OTM employees Timecards
2155 -- The retrieval process will only retrieve part of the population
2156 -- as set by the profile option OTL: Transfer Batch Size which is why
2157 -- it is called in a loop
2158 hxc_generic_retrieval_pkg.execute_retrieval_process
2159 (p_process => g_bee_retrieval_process,
2160 p_transaction_code => p_retrieval_transaction_code,
2161 p_start_date => p_start_date,
2162 p_end_date => p_end_date,
2163 p_incremental => p_incremental,
2164 p_rerun_flag => 'N',
2165 p_where_clause => p_where_clause,
2166 p_scope => hxc_timecard.c_day_scope,
2167 p_clusive => g_inclusive,
2168 p_unique_params => p_unique_params,
2169 p_since_date => l_since_date
2170 );
2171
2172 IF (hxt_interface_utilities.detail_lines_retrieved
2173 (hxc_generic_retrieval_pkg.t_detail_bld_blks)
2174 )
2175 THEN
2176 add_lines_to_bee_batch
2177 (p_batch_ref,
2178 p_bg_id,
2179 p_session_date,
2180 hxc_generic_retrieval_pkg.t_detail_bld_blks,
2181 hxc_generic_retrieval_pkg.t_detail_attributes,
2182 hxc_generic_retrieval_pkg.t_old_detail_bld_blks,
2183 hxc_generic_retrieval_pkg.t_old_detail_attributes,
2184 l_supa_chunk_batch_id,
2185 l_supa_chunk_retro_batch_id
2186 );
2187 ELSE
2188 hxc_generic_retrieval_pkg.update_transaction_status
2189 (p_process => 'BEE Retrieval Process',
2190 p_status => 'SUCCESS',
2191 p_exception_description => NULL
2192 );
2193 EXIT retrieve_in_chuncks;
2194 END IF;
2195
2196 -- Bug 6121705
2197 -- For automatic reversal of batches transferred for rules evaluation pref change
2198 -- Check the count of OTM details to be reversed and call the adjustments procedure.
2199 -- Count(*) and rownum as in process_otlr_employees for returning 0 or 1 if data
2200 -- exists or not. We dont want the exact number, just wanna know if it exists or not.
2201
2202 SELECT COUNT(*)
2203 INTO l_dup_count
2204 FROM hxc_bee_pref_adj_lines
2205 WHERE batch_source = 'OTM'
2206 AND ROWNUM < 2;
2207
2208
2209 -- If this is true, it means you have to make adjustments. Call the proc.
2210 IF ( l_dup_count > 0)
2211 THEN
2212 make_adjustments_otm( p_bg_id,
2213 p_batch_ref);
2214 END IF;
2215
2216 -- --
2217
2218
2219 hxt_interface_utilities.empty_cache;
2220 END LOOP retrieve_in_chuncks;
2221
2222 -- Bug 12919783
2223 mark_batches_for_rollback;
2224
2225 hxt_interface_utilities.perform_commit;
2226 -- Transfer or Validate the batches that were created
2227 --(if requested by user)
2228 process_bee_batches (p_status_in_bee => p_status_in_bee);
2229 hr_utility.set_location ('Leaving ' || l_proc, 100);
2230 EXCEPTION
2231 WHEN OTHERS
2232 THEN
2233
2234 hr_utility.trace(dbms_utility.format_error_backtrace);
2235 set_sqlerror_trx (g_bee_retrieval_process);
2236 hxc_generic_retrieval_utils.set_parent_statuses;
2237 hxc_generic_retrieval_pkg.update_transaction_status
2238 (p_process => g_bee_retrieval_process,
2239 p_status => 'ERRORS',
2240 p_exception_description => SUBSTR
2241 (SQLERRM,
2242 1,
2243 g_max_message_size
2244 ),
2245 p_rollback => FALSE
2246 );
2247
2248 IF (SQLERRM NOT LIKE '%HXC%')
2249 THEN
2250 fnd_message.raise_error;
2251 ELSE
2252 IF (p_no_otm = 'Y')
2253 THEN
2254 fnd_message.raise_error;
2255 END IF;
2256 END IF;
2257 hr_utility.trace(dbms_utility.format_error_backtrace);
2258
2259 RETURN;
2260 END process_non_otlr_employees;
2261
2262 PROCEDURE transfer_to_hr_payroll (
2263 errbuf OUT NOCOPY VARCHAR2,
2264 retcode OUT NOCOPY NUMBER,
2265 p_bg_id IN NUMBER,
2266 p_session_date IN VARCHAR2,
2267 p_start_date IN VARCHAR2,
2268 p_end_date IN VARCHAR2,
2269 p_start_batch_id IN NUMBER DEFAULT NULL,
2270 p_end_batch_id IN NUMBER DEFAULT NULL,
2271 p_gre_id IN NUMBER DEFAULT NULL,
2272 p_organization_id IN NUMBER DEFAULT NULL,
2273 p_location_id IN NUMBER DEFAULT NULL,
2274 p_payroll_id IN NUMBER DEFAULT NULL,
2275 p_person_id IN NUMBER DEFAULT NULL,
2276 p_retrieval_transaction_code IN VARCHAR2,
2277 p_batch_selection IN VARCHAR2 DEFAULT NULL,
2278 p_is_old IN VARCHAR2 DEFAULT NULL,
2279 p_old_batch_ref IN VARCHAR2 DEFAULT NULL,
2280 p_new_batch_ref IN VARCHAR2 DEFAULT NULL,
2281 p_new_specified IN VARCHAR2 DEFAULT NULL,
2282 p_status_in_bee IN VARCHAR2,
2283 p_otlr_to_bee IN VARCHAR2,
2284 p_since_date IN VARCHAR2
2285 )
2286 AS
2287 l_where_clause hxt_interface_utilities.max_varchar;
2288 l_unique_params hxt_interface_utilities.max_varchar;
2289 l_batch_ref pay_batch_headers.batch_reference%TYPE;
2290 l_no_otm hxt_interface_utilities.flag_varchar := 'N';
2291 l_retrieval_options fnd_profile_option_values.profile_option_value%TYPE
2292 := fnd_profile.VALUE ('HXC_RETRIEVAL_OPTIONS');
2293 BEGIN
2294 -- Set session date
2295 hxc_generic_retrieval_pkg.g_ret_criteria.location_id := p_location_id;
2296 hxc_generic_retrieval_pkg.g_ret_criteria.payroll_id := p_payroll_id;
2297 hxc_generic_retrieval_pkg.g_ret_criteria.organization_id :=
2298 p_organization_id;
2299 hxc_generic_retrieval_pkg.g_ret_criteria.gre_id := p_gre_id;
2300 l_since_date := p_since_date;
2301 pay_db_pay_setup.set_session_date (SYSDATE);
2302 l_batch_ref := NVL (p_old_batch_ref, p_new_batch_ref);
2303 l_where_clause :=
2304 where_clause (p_bg_id,
2305 p_location_id,
2306 p_payroll_id,
2307 p_organization_id,
2308 p_person_id,
2309 p_gre_id
2310 );
2311 l_unique_params :=
2312 p_retrieval_transaction_code
2313 || ':'
2314 || NVL (p_new_batch_ref, 'OLD')
2315 || ':'
2316 || l_batch_ref;
2317
2318 -- Bug 13777315
2319 -- Find out BEE IV Upgrade status
2320 g_IV_upgrade := get_upgrade_status(p_bg_id);
2321
2322 hr_utility.trace('BEE IV Upgrade '||g_iv_upgrade);
2323
2324
2325 IF ((l_retrieval_options <> 'BEE') OR (l_retrieval_options IS NULL))
2326 THEN
2327 process_otlr_employees
2328 (p_bg_id,
2329 fnd_date.canonical_to_date(p_session_date), -- Bug 6121705, new parameter; needs
2330 -- type conversion from VARCHAR2
2331 p_start_date, --fnd_date.canonical_to_date (p_start_date),
2332 p_end_date, --fnd_date.canonical_to_date (p_end_date),
2333 l_where_clause,
2334 p_retrieval_transaction_code,
2335 l_batch_ref,
2336 l_unique_params,
2337 p_no_otm => l_no_otm
2338 );
2339 ELSE
2340 l_no_otm := 'Y';
2341 END IF;
2342
2343 IF ((l_retrieval_options <> 'OTLR') OR (l_retrieval_options IS NULL))
2344 THEN
2345 process_non_otlr_employees
2346 (p_bg_id,
2347 fnd_date.canonical_to_date (p_start_date),
2348 fnd_date.canonical_to_date (p_end_date),
2349 fnd_date.canonical_to_date (p_session_date),
2350 l_where_clause,
2351 p_retrieval_transaction_code,
2352 l_batch_ref,
2353 l_unique_params,
2354 p_status_in_bee => p_status_in_bee,
2355 p_no_otm => l_no_otm
2356 );
2357 END IF;
2358 END transfer_to_hr_payroll;
2359
2360
2361 -- Bug 6121705
2362 -- Proc created to make adjustments in BEE for batch lines with source as Time Store.
2363 -- chk_retrieve function would have populated the details which are already transferred
2364 -- with a different rules evaluation preference and need adjustments. Now there has
2365 -- to be a second time retrieval and creation of batches, but with negative entries in
2366 -- BEE with batch source as Time Store.
2367
2368
2369 PROCEDURE make_adjustments_bee ( p_batch_ref IN VARCHAR2,
2370 p_bg_id IN NUMBER,
2371 p_session_date IN DATE)
2372 IS
2373
2374
2375 l_sqlcode NUMBER;
2376 l_sqlmsg VARCHAR2(2000);
2377
2378 l_att_cnt NUMBER;
2379
2380 tnull_old_detail_bld_blks hxc_generic_retrieval_pkg.t_building_blocks;
2381 tnull_old_detail_attributes hxc_generic_retrieval_pkg.t_time_attribute;
2382
2383 l_adj_batch_id pay_batch_headers.batch_reference%TYPE := NULL;
2384 l_retro_adj_batch_id pay_batch_headers.batch_reference%TYPE := NULL;
2385
2386
2387 -- Private procedure create_bld_blk_table
2388 -- populates the detail bld blks plsql table like gen. retrieval
2389 -- 1. Pull out the bb details from hxc_time_building_blocks table
2390 -- which has an entry in hxc_bee_pref_adj_lines table, with
2391 -- batch_source as Time Store.
2392 -- 2. Update hxc_bee_pref_adj_lines table with these values for
2393 -- the corresponding bb_ids and ovns.
2394 -- 3. Pull out these into a plsql table for the format prescribed in
2395 -- hxc_generic_retrieval_pkg, so that it can use the batch creation API.
2396
2397
2398 PROCEDURE create_bld_blk_table
2399 IS
2400
2401 CURSOR get_bb_details
2402 IS
2403 SELECT time_building_block_id,
2404 object_Version_number,
2405 type,
2406 DECODE(type,'MEASURE',measure,'RANGE',(stop_time-start_time)*24),
2407 start_time,
2408 stop_time,
2409 parent_building_block_id ,
2410 scope,
2411 resource_type,
2412 comment_text,
2413 unit_of_measure,
2414 'N',
2415 'N'
2416 FROM hxc_time_building_blocks
2417 WHERE (time_building_block_id,object_version_number)
2418 IN ( SELECT detail_bb_id,
2419 detail_bb_ovn
2420 FROM hxc_bee_pref_adj_lines
2421 WHERE batch_source = 'Time Store');
2422
2423 CURSOR get_blocks
2424 IS
2425 SELECT detail_bb_id,
2426 type,
2427 -1*hours, -- To create reverse entries, you need negative hours.
2428 TRUNC(NVL(start_time,date_earned)),
2429 TRUNC(NVL(stop_time,date_earned)),
2430 parent_bb_id ,
2431 scope,
2432 resource_id,
2433 resource_type,
2434 comment_text,
2435 uom,
2436 detail_bb_ovn,
2437 changed,
2438 deleted,
2439 timecard_id,
2440 timecard_ovn
2441 FROM hxc_bee_pref_adj_lines
2442 WHERE batch_source = 'Time Store'
2443 order by detail_bb_id
2444 ;
2445
2446 BEGIN --- create_bld_blk_table
2447 OPEN get_bb_details;
2448
2449 FETCH get_bb_details
2450 BULK COLLECT INTO t_bb_details;
2451
2452 CLOSE get_bb_details;
2453
2454 FOR i IN t_bb_details.FIRST..t_bb_details.LAST
2455 LOOP
2456 UPDATE hxc_bee_pref_adj_lines
2457 SET type = t_bb_details(i).type,
2458 scope = t_bb_details(i).scope,
2459 hours = t_bb_details(i).measure ,
2460 start_time = t_bb_details(i).start_time ,
2461 stop_time = t_bb_details(i).stop_time ,
2462 resource_type = t_bb_details(i).resource_type ,
2463 uom = t_bb_details(i).uom ,
2464 changed = t_bb_details(i).changed ,
2465 deleted = t_bb_details(i).deleted ,
2466 comment_text = t_bb_details(i).comment_text ,
2467 parent_bb_id = t_bb_details(i).parent_bb_id
2468 WHERE detail_bb_id = t_bb_details(i).bb_id
2469 AND detail_bb_ovn = t_bb_details(i).ovn;
2470 END LOOP;
2471
2472 OPEN get_blocks;
2473
2474 FETCH get_blocks BULK COLLECT INTO t_detail_blocks;
2475
2476 CLOSE get_blocks;
2477
2478 END create_bld_blk_table;
2479
2480
2481
2482 -- Private Procedure gather_attributes
2483 -- gathers the attribute information for the blocks into the plsql table
2484 -- again like gen.retrieval.
2485
2486 PROCEDURE gather_attributes
2487 is
2488
2489 CURSOR get_attributes
2490 IS
2491 SELECT hat.attribute_category,
2492 attribute1,
2493 attribute2,
2494 attribute3,
2495 attribute4,
2496 attribute5,
2497 attribute6,
2498 attribute7,
2499 attribute8,
2500 attribute9,
2501 attribute10,
2502 attribute11,
2503 attribute12,
2504 attribute13,
2505 attribute14,
2506 attribute15,
2507 attribute16,
2508 attribute17,
2509 attribute18,
2510 attribute19,
2511 attribute20,
2512 attribute21,
2513 attribute22,
2514 attribute23,
2515 attribute24,
2516 attribute25,
2517 attribute26,
2518 attribute27,
2519 attribute28,
2520 attribute29,
2521 attribute30,
2522 hau.time_building_block_id,
2523 hau.time_building_block_ovn,
2524 hat.bld_blk_info_type_id
2525 FROM hxc_time_attribute_usages hau,
2526 hxc_time_attributes hat
2527 WHERE hau.time_attribute_id = hat.time_attribute_id
2528 AND (hau.time_building_block_id,
2529 hau.time_building_block_ovn) IN ( SELECT detail_bb_id, detail_bb_ovn
2530 FROM hxc_bee_pref_adj_lines
2531 WHERE batch_source = 'Time Store')
2532 ORDER BY hau.time_building_block_id,
2533 hat.bld_blk_info_type_id;
2534
2535 BEGIN
2536
2537 OPEN get_attributes;
2538
2539 FETCH get_attributes BULK COLLECT INTO t_attr_info;
2540
2541 CLOSE get_attributes;
2542
2543 END gather_attributes ;
2544
2545
2546 -- Private Procedure create_attributes_table
2547 -- From the gathered raw list of attributes, creates the plsql table
2548 -- structure that gen. retrieval returns. Loops thru the attributes and
2549 -- matches the mappings to get the required table structure.
2550
2551
2552 PROCEDURE create_attributes_table
2553 IS
2554 l_att_cnt NUMBER := 0;
2555
2556 BEGIN
2557 -- LOOP thru all detail attribute records ---
2558 FOR i IN t_attr_info.FIRST..t_attr_info.LAST
2559 LOOP
2560 -- Loop thru all the field mappings ---
2561 -- You already have the mappings in this global pl/sql table for gen retrieval.
2562 -- Use it, no need to query again.
2563 FOR MAP IN hxc_generic_retrieval_pkg.g_field_mappings_table.FIRST..
2564 hxc_generic_retrieval_pkg.g_field_mappings_table.LAST
2565 LOOP
2566 IF (t_attr_info(i).bld_blk_info_type_id =
2567 hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).
2568 bld_blk_info_type_id)
2569 -- If there is a valid mapping for the bld blk info type id --
2570 THEN
2571 l_att_cnt := l_att_cnt + 1;
2572 -- copy the the context,category AND the field name into the attribute info table --
2573 t_dtl_attributes(l_att_cnt).bb_id := t_attr_info(i).bb_id;
2574 t_dtl_attributes(l_att_cnt).field_name := hxc_generic_retrieval_pkg.
2575 g_field_mappings_table(MAP).field_name;
2576 t_dtl_attributes(l_att_cnt).context := hxc_generic_retrieval_pkg.
2577 g_field_mappings_table(MAP).context;
2578 t_dtl_attributes(l_att_cnt).category := hxc_generic_retrieval_pkg.
2579 g_field_mappings_table(MAP).category;
2580 -- check which attribute this mapping belongs to AND copy down that attribute --
2581
2582 IF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).
2583 ATTRIBUTE = 'ATTRIBUTE_CATEGORY')
2584 THEN
2585 t_dtl_attributes(l_att_cnt).value :=
2586 hxc_deposit_wrapper_utilities.get_dupdff_name(t_attr_info(i).attribute_category);
2587 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE1')
2588 THEN
2589 t_dtl_attributes(l_att_cnt).value :=
2590 t_attr_info(i).attribute1;
2591 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE2')
2592 THEN
2593 t_dtl_attributes(l_att_cnt).value :=
2594 t_attr_info(i).attribute2;
2595 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE3')
2596 THEN
2597 t_dtl_attributes(l_att_cnt).value :=
2598 t_attr_info(i).attribute3;
2599 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE4')
2600 THEN
2601 t_dtl_attributes(l_att_cnt).value :=
2602 t_attr_info(i).attribute4;
2603 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE5')
2604 THEN
2605 t_dtl_attributes(l_att_cnt).value :=
2606 t_attr_info(i).attribute5;
2607 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE6')
2608 THEN
2609 t_dtl_attributes(l_att_cnt).value :=
2610 t_attr_info(i).attribute6;
2611 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE7')
2612 THEN
2613 t_dtl_attributes(l_att_cnt).value :=
2614 t_attr_info(i).attribute7;
2615 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE8')
2616 THEN
2617 t_dtl_attributes(l_att_cnt).value :=
2618 t_attr_info(i).attribute8;
2619 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE9')
2620 THEN
2621 t_dtl_attributes(l_att_cnt).value :=
2622 t_attr_info(i).attribute9;
2623 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE10')
2624 THEN
2625 t_dtl_attributes(l_att_cnt).value :=
2626 t_attr_info(i).attribute10;
2627 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE11')
2628 THEN
2629 t_dtl_attributes(l_att_cnt).value :=
2630 t_attr_info(i).attribute11;
2631 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE12')
2632 THEN
2633 t_dtl_attributes(l_att_cnt).value :=
2634 t_attr_info(i).attribute12;
2635 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE13')
2636 THEN
2637 t_dtl_attributes(l_att_cnt).value :=
2638 t_attr_info(i).attribute13;
2639 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE14')
2640 THEN
2641 t_dtl_attributes(l_att_cnt).value :=
2642 t_attr_info(i).attribute14;
2643 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE15')
2644 THEN
2645 t_dtl_attributes(l_att_cnt).value :=
2646 t_attr_info(i).attribute15;
2647 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE16')
2648 THEN
2649 t_dtl_attributes(l_att_cnt).value :=
2650 t_attr_info(i).attribute16;
2651 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE17')
2652 THEN
2653 t_dtl_attributes(l_att_cnt).value :=
2654 t_attr_info(i).attribute17;
2655 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE18')
2656 THEN
2657 t_dtl_attributes(l_att_cnt).value :=
2658 t_attr_info(i).attribute18;
2659 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE19')
2660 THEN
2661 t_dtl_attributes(l_att_cnt).value :=
2662 t_attr_info(i).attribute19;
2663 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE20')
2664 THEN
2665 t_dtl_attributes(l_att_cnt).value :=
2666 t_attr_info(i).attribute20;
2667 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE21')
2668 THEN
2669 t_dtl_attributes(l_att_cnt).value :=
2670 t_attr_info(i).attribute21;
2671 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE22')
2672 THEN
2673 t_dtl_attributes(l_att_cnt).value :=
2674 t_attr_info(i).attribute22;
2675 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE23')
2676 THEN
2677 t_dtl_attributes(l_att_cnt).value :=
2678 t_attr_info(i).attribute23;
2679 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE24')
2680 THEN
2681 t_dtl_attributes(l_att_cnt).value :=
2682 t_attr_info(i).attribute24;
2683 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE25')
2684 THEN
2685 t_dtl_attributes(l_att_cnt).value :=
2686 t_attr_info(i).attribute25;
2687 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE26')
2688 THEN
2689 t_dtl_attributes(l_att_cnt).value :=
2690 t_attr_info(i).attribute26;
2691 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE27')
2692 THEN
2693 t_dtl_attributes(l_att_cnt).value :=
2694 t_attr_info(i).attribute27;
2695 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE28')
2696 THEN
2697 t_dtl_attributes(l_att_cnt).value :=
2698 t_attr_info(i).attribute28;
2699 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE29')
2700 THEN
2701 t_dtl_attributes(l_att_cnt).value :=
2702 t_attr_info(i).attribute29;
2703 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE30')
2704 THEN
2705 t_dtl_attributes(l_att_cnt).value :=
2706 t_attr_info(i).attribute30;
2707 END IF;
2708 ELSE
2709 NULL;
2710 END IF;
2711
2712 END LOOP;
2713 END LOOP;
2714
2715 END create_attributes_table;
2716
2717 BEGIN --- Make adjustments Main
2718
2719 -- Create your bld blk table structure
2720 -- Gather your attribute info
2721 -- create the attribute table structure with the mapping info.
2722 -- Call add_lines_to bee_batch function with these values.
2723 -- The adjustment batch that is created will have a batch reference
2724 -- prefixed with 'adjdup' to identify these in future.
2725
2726 create_bld_blk_table;
2727
2728 gather_attributes;
2729
2730 create_attributes_table;
2731
2732 add_lines_to_bee_batch
2733 ('adjdup'||p_batch_ref,
2734 p_bg_id,
2735 p_session_date,
2736 t_detail_blocks,
2737 t_dtl_attributes,
2738 tnull_old_detail_bld_blks,
2739 tnull_old_detail_attributes,
2740 l_adj_batch_id,
2741 l_retro_adj_batch_id
2742 );
2743
2744 -- you dont need the duplicate adjustment lines of this batch source, so delete them.
2745 DELETE FROM hxc_bee_pref_adj_lines
2746 WHERE batch_source = 'Time Store';
2747
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 l_sqlmsg := SUBSTR(SQLERRM,1,1500)||SQLCODE;
2751 hr_utility.trace('Sql error in make_adjustments :'||l_sqlmsg);
2752 hr_utility.trace(dbms_utility.format_error_backtrace);
2753 RAISE;
2754
2755 END;
2756
2757
2758
2759 -- Bug 6121705
2760 -- This procedure is used to create reversal batches with Batch source as OTM
2761 -- for the details in hxc_bee_pref_adj_lines table with OTM as batch source.
2762 -- hxc_generic_retrieval_utils.chk_retrieve would have populated these
2763 -- records for which there is a history of a different preference for rules
2764 -- evaluation.
2765
2766
2767 PROCEDURE make_adjustments_otm( p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
2768 p_batch_ref IN VARCHAR2)
2769 IS
2770
2771 l_new_batch NUMBER;
2772 l_batch_ovn NUMBER;
2773 l_sqlmsg VARCHAR2(2000);
2774 l_rec_count NUMBER;
2775
2776
2777 -- Private procedure delete_non_transferred_hours
2778 -- Deletes hours that are transferred earlier to HXT
2779 -- and have not been transferred to BEE.
2780
2781 PROCEDURE delete_non_transferred_hours
2782 IS
2783 BEGIN
2784 DELETE FROM hxt_det_hours_worked_F
2785 WHERE ( date_worked,
2786 assignment_id )
2787 in ( SELECT date_earned,
2788 assignment_id
2789 FROM hxc_bee_pref_adj_lines,
2790 per_all_assignments_f paf,
2791 per_assignment_status_types pas
2792 WHERE resource_id = person_id
2793 AND batch_source = 'OTM'
2794 AND paf.effective_end_date = hr_general.end_of_time
2795 AND paf.effective_start_date <= date_earned
2796 AND paf.primary_flag = 'Y'
2797 AND paf.assignment_status_type_id =
2798 pas.assignment_status_type_id
2799 AND pas.per_system_status = 'ACTIVE_ASSIGN')
2800 AND pay_status <> 'C'
2801 AND effective_end_date = hr_general.end_of_time;
2802 EXCEPTION
2803 WHEN NO_DATA_FOUND THEN
2804 null;
2805 END;
2806
2807
2808 -- Private procedure create_batch_line
2809 -- creates the batch lines for the adjustment batch that is created.
2810
2811 PROCEDURE create_batch_line(p_batch_id NUMBER)
2812 IS
2813 CURSOR get_batch_rec
2814 IS
2815 SELECT *
2816 FROM hxt_batch_values_v
2817 WHERE (date_worked ,assignment_id) in
2818 ( SELECT date_earned,
2819 paf.assignment_id
2820 FROM hxc_bee_pref_adj_lines hoa,
2821 per_all_assignments_f paf
2822 WHERE hoa.resource_id = paf.person_id
2823 AND hoa.date_earned BETWEEN paf.effective_start_date
2824 AND paf.effective_end_date
2825 AND paf.primary_flag = 'Y');
2826 p_batch_rec hxt_batch_values_v%ROWTYPE;
2827 l_sum_retcode NUMBER := 0;
2828 l_batch_sequence NUMBER;
2829
2830 BEGIN
2831 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
2832 hxt_batch_process.g_iv_upgrade := hxt_batch_process.get_upgrade_status(p_batch_id);
2833 OPEN get_batch_rec;
2834 LOOP
2835 FETCH get_batch_rec
2836 INTO p_batch_rec;
2837 EXIT WHEN get_batch_rec%NOTFOUND;
2838
2839 p_batch_rec.batch_id := p_batch_id;
2840 p_batch_rec.hours := -1*p_batch_rec.hours;
2841
2842 IF (l_sum_retcode = 0)
2843 THEN
2844 hxt_batch_process.dtl_to_bee(p_batch_rec,l_sum_retcode,l_batch_sequence);
2845 l_batch_sequence := l_batch_sequence + 1;
2846 END IF;
2847 END LOOP;
2848 CLOSE get_batch_rec;
2849
2850 END;
2851
2852 BEGIN ---- make_adjustments_otm
2853
2854 -- In case the timecard is still with HXT, no need to create reversal batches.
2855 -- But see that they are deleted from the system, so that they can never be
2856 -- transferred. Delete the non transferred hours first.
2857 -- Create a batch header, and then create the batch lines taking values from
2858 -- HXT_BATCH_VALUES_V. There could have been a timecard edit in HXT, and the changed
2859 -- hours could have gone to BEE. The reversal batch lines has to reverse these, rather
2860 -- than the hours you pulled out from HXC. HXT_BATCH_VALUES_V is a view on hxt detail hours
2861 -- and pay batch lines, so what you get from there would be the transferred values.
2862 -- So after you pull out the batch records, turn around the hours with a negative sign
2863 -- and create the lines.
2864 delete_non_transferred_hours;
2865
2866 -- Check if there is anything left there. If no TC was transferred, no need of the
2867 -- adj dup batch.
2868 SELECT COUNT(*)
2869 INTO l_rec_count
2870 FROM hxc_bee_pref_adj_lines
2871 WHERE batch_source = 'OTM'
2872 AND rownum < 2;
2873
2874 IF l_rec_count > 0
2875 THEN
2876 l_batch_ovn := 1;
2877 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_header
2878 (p_session_date => sysdate
2879 ,p_batch_name => 'adjdup'||p_batch_ref
2880 ,p_batch_status => 'U'
2881 ,p_business_group_id => p_bg_id
2882 ,p_action_if_exists => 'I'
2883 ,p_batch_reference => 'adjdup'||p_batch_ref
2884 ,p_batch_source => 'OTM'
2885 ,p_purge_after_transfer => 'N'
2886 ,p_reject_if_future_changes => 'N'
2887 ,p_batch_id => l_new_batch
2888 ,p_object_version_number => l_batch_ovn
2889 );
2890
2891 create_batch_line(p_batch_id => l_new_batch);
2892
2893
2894 DELETE FROM hxc_bee_pref_adj_lines
2895 WHERE batch_source = 'OTM';
2896 END IF;
2897
2898
2899 EXCEPTION
2900 WHEN NO_DATA_FOUND THEN
2901 NULL;
2902 WHEN others THEN
2903 l_sqlmsg := SUBSTR(SQLERRM,1,1500)||SQLCODE;
2904 hr_utility.trace('Sql error in make_adjustments :'||l_sqlmsg);
2905 hr_utility.trace(dbms_utility.format_error_backtrace);
2906 RAISE;
2907 END;
2908
2909
2910 -- Bug 12919783
2911 PROCEDURE mark_batches_for_rollback
2912 IS
2913
2914 l_index VARCHAR2(15);
2915 BEGIN
2916
2917 NULL;
2918 IF g_batch_tab.COUNT > 0
2919 THEN
2920 l_index := g_batch_tab.FIRST;
2921 LOOP
2922 INSERT INTO hxc_retrieval_batches_all
2923 (batch_id,
2924 request_id,
2925 user_id,
2926 request_date,
2927 batch_status)
2928 VALUES ( TO_NUMBER(l_index),
2929 FND_GLOBAL.conc_request_id,
2930 FND_GLOBAL.user_id,
2931 TRUNC(SYSDATE),
2932 'ELIGIBLE');
2933 l_index := g_batch_tab.NEXT(l_index);
2934 EXIT WHEN NOT g_batch_tab.EXISTS(l_index);
2935 END LOOP;
2936 END IF;
2937
2938
2939 END mark_batches_for_rollback;
2940
2941
2942
2943 END pay_hr_otc_retrieval_interface;