[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.8 2007/12/07 14:19:26 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
14 /*
15 || Function that can be used to convert "internal" values (LOV Codes etc.) to
16 || their respective "display" values (LOV Meaning etc.).
17 */
18 FUNCTION display_value (
19 p_element_type_id IN pay_batch_lines.element_type_id%TYPE,
20 p_internal_input_value IN pay_batch_lines.value_1%TYPE,
21 p_iv_number IN PLS_INTEGER,
22 p_session_date IN DATE,
23 p_bg_id IN hr_all_organization_units.business_group_id%TYPE
24 )
25 RETURN VARCHAR2
26 IS
27 l_proc CONSTANT proc_name := g_package || 'display_value';
28 l_display_value VARCHAR2 (80);
29
30 /*
31 || This odd looking query was created to be able to retrieve the nth
32 || (p_iv_number) Input Value of an Element Type without having to loop over
33 || all of them and then stop at the nth position.
34 */
35 CURSOR csr_iv_info (
36 p_element_type_id pay_element_types_f.element_type_id%TYPE,
37 p_iv_number PLS_INTEGER,
38 p_session_date DATE
39 )
40 IS
41 SELECT *
42 FROM (SELECT iv_data.*, ROWNUM r
43 FROM (SELECT inv.uom, inv.lookup_type, inv.value_set_id,
44 etp.input_currency_code
45 FROM pay_input_values_f inv,
46 pay_element_types_f etp
47 WHERE inv.element_type_id = p_element_type_id
48 AND etp.element_type_id = p_element_type_id
49 AND p_session_date
50 BETWEEN inv.effective_start_date
51 AND inv.effective_end_date
52 AND p_session_date
53 BETWEEN etp.effective_start_date
54 AND etp.effective_end_date
55 ORDER BY inv.display_sequence, inv.NAME) iv_data
56 WHERE ROWNUM < (p_iv_number + 1))
57 WHERE r > (p_iv_number - 1);
58
59 rec_iv_info csr_iv_info%ROWTYPE;
60
61 FUNCTION lookup_meaning (
62 p_lookup_type IN hr_lookups.lookup_type%TYPE,
63 p_lookup_code IN hr_lookups.lookup_code%TYPE
64 )
65 RETURN hr_lookups.meaning%TYPE
66 AS
67 CURSOR csr_valid_lookup (
68 p_lookup_type VARCHAR2,
69 p_lookup_code VARCHAR2
70 )
71 IS
72 SELECT hl.meaning
73 FROM hr_lookups hl
74 WHERE hl.lookup_type = p_lookup_type
75 AND hl.lookup_code = p_lookup_code;
76
77 l_lookup_meaning hr_lookups.meaning%TYPE;
78 BEGIN
79 OPEN csr_valid_lookup (p_lookup_type, p_lookup_code);
80
81 FETCH csr_valid_lookup
82 INTO l_lookup_meaning;
83
84 CLOSE csr_valid_lookup;
85
86 hr_utility.set_location ( ' l_lookup_meaning = '
87 || l_lookup_meaning,
88 10
89 );
90 RETURN l_lookup_meaning;
91 END lookup_meaning;
92
93 FUNCTION valueset_meaning (
94 p_value_set_id IN fnd_flex_values.flex_value_set_id%TYPE,
95 p_valueset_value IN fnd_flex_values.flex_value%TYPE
96 )
97 RETURN fnd_flex_values_vl.description%TYPE
98 AS
99 l_valueset_meaning fnd_flex_values_vl.description%TYPE;
100 BEGIN
101 l_valueset_meaning :=
102 pay_input_values_pkg.decode_vset_value (p_value_set_id,
103 p_valueset_value
104 );
105 hr_utility.set_location ( ' l_valueset_meaning = '
106 || l_valueset_meaning,
107 10
108 );
109 RETURN l_valueset_meaning;
110 END valueset_meaning;
111 BEGIN
112 hr_utility.set_location ('Entering: ' || l_proc, 10);
113 l_display_value := p_internal_input_value;
114
115 IF (p_internal_input_value IS NOT NULL)
116 THEN
117 hr_utility.set_location
118 ( ' Converting p_internal_input_value = '
119 || p_internal_input_value,
120 20
121 );
122 hr_utility.set_location (' using: ', 30);
123 hr_utility.set_location ( ' p_element_type_id = '
124 || p_element_type_id,
125 40
126 );
127 hr_utility.set_location (' p_iv_number = ' || p_iv_number,
128 50
129 );
130 hr_utility.set_location ( ' p_session_date = '
131 || p_session_date,
132 60
133 );
134 hr_utility.set_location (' p_bg_id = ' || p_bg_id,
135 70);
136
137 OPEN csr_iv_info (p_element_type_id, p_iv_number, p_session_date);
138
139 FETCH csr_iv_info
140 INTO rec_iv_info;
141
142 IF (csr_iv_info%FOUND)
143 THEN
144 IF (rec_iv_info.lookup_type IS NOT NULL)
145 THEN
146 l_display_value :=
147 lookup_meaning (p_lookup_type => rec_iv_info.lookup_type,
148 p_lookup_code => p_internal_input_value
149 );
150 ELSIF (rec_iv_info.value_set_id IS NOT NULL)
151 THEN
152 l_display_value :=
153 valueset_meaning
154 (p_value_set_id => rec_iv_info.value_set_id,
155 p_valueset_value => p_internal_input_value
156 );
157 ELSE
158 hr_chkfmt.changeformat (p_internal_input_value,
159 l_display_value,
160 rec_iv_info.uom,
161 rec_iv_info.input_currency_code
162 );
163 END IF;
164 END IF;
165 END IF;
166
167 hr_utility.set_location ( ' returning l_display_value = '
168 || l_display_value,
169 90
170 );
171 hr_utility.set_location ('Leaving: ' || l_proc, 100);
172 RETURN l_display_value;
173 EXCEPTION
174 WHEN OTHERS
175 THEN
176 hr_utility.set_message ('PAY', 'PAY_6306_INPUT_VALUE_FORMAT');
177 hr_utility.set_message_token
178 ('UNIT_OF_MEASURE',
179 hr_general.decode_lookup ('UNITS',
180 rec_iv_info.uom
181 )
182 );
183 hr_utility.raise_error;
184 END display_value;
185
186 FUNCTION retro_batch_suffix
187 RETURN VARCHAR2
188 IS
189 l_proc CONSTANT proc_name := g_package || 'retro_batch_suffix';
190 BEGIN
191 hr_utility.set_location ('Entering:' || l_proc, 10);
192 hr_utility.set_location ( ' returning g_retro_batch_suffix = '
193 || g_retro_batch_suffix,
194 20
195 );
196 hr_utility.set_location ('Leaving:' || l_proc, 100);
197 RETURN g_retro_batch_suffix;
198 END retro_batch_suffix;
199
200 PROCEDURE set_retro_batch_suffix (p_retro_batch_suffix IN VARCHAR2)
201 IS
202 l_proc CONSTANT proc_name := g_package || 'set_retro_batch_suffix';
203 BEGIN
204 hr_utility.set_location ('Entering:' || l_proc, 10);
205 hr_utility.set_location ( ' setting g_retro_batch_suffix to '
206 || p_retro_batch_suffix,
207 20
208 );
209 g_retro_batch_suffix := p_retro_batch_suffix;
210 hr_utility.set_location ('Leaving:' || l_proc, 100);
211 END set_retro_batch_suffix;
212
213 PROCEDURE record_batch_info (p_batch_rec IN batches_type_rec)
214 IS
215 l_proc CONSTANT proc_name := g_package || 'record_batch_info';
216 BEGIN
217 hr_utility.set_location ('Entering:' || l_proc, 10);
218 g_batches_created (NVL (g_batches_created.LAST, 0) + 1) := p_batch_rec;
219 hr_utility.set_location ('Leaving:' || l_proc, 100);
220 END record_batch_info;
221
222 PROCEDURE record_batch_info (
223 p_batch_id IN pay_batch_headers.batch_id%TYPE,
224 p_business_group_id IN pay_batch_headers.business_group_id%TYPE,
225 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
226 p_batch_name IN pay_batch_headers.batch_name%TYPE
227 )
228 IS
229 l_proc CONSTANT proc_name
230 := g_package || 'record_batch_info (Overloaded)';
231 l_batch_created batches_type_rec;
232 BEGIN
233 hr_utility.set_location ('Entering:' || l_proc, 10);
234 l_batch_created.batch_id := p_batch_id;
235 l_batch_created.business_group_id := p_business_group_id;
236 l_batch_created.batch_reference := p_batch_reference;
237 l_batch_created.batch_name := p_batch_name;
238 record_batch_info (p_batch_rec => l_batch_created);
239 hr_utility.set_location ('Leaving:' || l_proc, 100);
240 END record_batch_info;
241
242 FUNCTION batches_created
243 RETURN batches_type_table
244 IS
245 l_proc CONSTANT proc_name := g_package || 'batches_created';
246 BEGIN
247 hr_utility.set_location ('Entering:' || l_proc, 10);
248 hr_utility.set_location ( ' g_batches_created.count = '
249 || g_batches_created.COUNT,
250 20
251 );
252 hr_utility.set_location ('Leaving:' || l_proc, 100);
253 RETURN g_batches_created;
254 END batches_created;
255
256 PROCEDURE start_bee_process (
257 p_mode IN VARCHAR2,
258 p_batches IN batches_type_table
259 )
260 IS
261 l_proc CONSTANT proc_name := g_package || 'start_bee_process';
262 l_batches_idx PLS_INTEGER := p_batches.FIRST;
263 l_request_id fnd_concurrent_requests.request_id%TYPE;
264 BEGIN
265 hr_utility.set_location ('Entering:' || l_proc, 10);
266
267 <<start_conc_prog_for_batches>>
268 LOOP
269 EXIT start_conc_prog_for_batches WHEN NOT p_batches.EXISTS
270 (l_batches_idx);
271 l_request_id :=
272 pay_paywsqee_pkg.paylink_request_id
273 (p_business_group_id => p_batches (l_batches_idx).business_group_id,
274 p_mode => p_mode,
275 p_batch_id => p_batches (l_batches_idx).batch_id
276 );
277 l_batches_idx := p_batches.NEXT (l_batches_idx);
278 END LOOP start_conc_prog_for_batches;
279
280 hr_utility.set_location ('Leaving:' || l_proc, 100);
281 END start_bee_process;
282
283 PROCEDURE validate_bee_batches (p_batches IN batches_type_table)
284 IS
285 l_proc CONSTANT proc_name := g_package || 'validate_bee_batches';
286 BEGIN
287 hr_utility.set_location ('Entering:' || l_proc, 10);
288 start_bee_process (p_mode => 'VALIDATE', p_batches => p_batches);
289 hr_utility.set_location ('Leaving:' || l_proc, 100);
290 END validate_bee_batches;
291
292 PROCEDURE transfer_bee_batches (p_batches IN batches_type_table)
293 IS
294 l_proc CONSTANT proc_name := g_package || 'transfer_bee_batches';
295 l_batches_idx PLS_INTEGER := p_batches.FIRST;
296 BEGIN
297 hr_utility.set_location ('Entering:' || l_proc, 10);
298 start_bee_process (p_mode => 'TRANSFER', p_batches => p_batches);
299 hr_utility.set_location ('Leaving:' || l_proc, 100);
300 END transfer_bee_batches;
301
302 PROCEDURE process_bee_batches (
303 p_batches IN batches_type_table DEFAULT batches_created,
304 p_status_in_bee IN VARCHAR2
305 )
306 IS
307 l_proc CONSTANT proc_name := g_package || 'process_bee_batches';
308 l_batches_idx PLS_INTEGER := p_batches.FIRST;
309 BEGIN
310 hr_utility.set_location ('Entering:' || l_proc, 10);
311
312 IF (p_status_in_bee = 'V')
313 THEN
314 validate_bee_batches (p_batches);
315 ELSIF (p_status_in_bee = 'T')
316 THEN
317 transfer_bee_batches (p_batches);
318 END IF;
319
320 hr_utility.set_location ('Leaving:' || l_proc, 100);
321 END process_bee_batches;
322
323 FUNCTION where_clause (
324 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
325 p_location_id IN per_all_assignments_f.location_id%TYPE,
326 p_payroll_id IN per_all_assignments_f.payroll_id%TYPE,
327 p_organization_id IN per_all_assignments_f.organization_id%TYPE,
328 p_person_id IN per_all_people_f.person_id%TYPE,
329 p_gre_id IN hr_soft_coding_keyflex.segment1%TYPE
330 )
331 RETURN VARCHAR2
332 IS
333 l_proc CONSTANT proc_name := g_package || 'where_clause';
334 l_where_clause hxt_interface_utilities.max_varchar := NULL;
335 l_payroll hxt_interface_utilities.varchar_256 := NULL;
336 l_person hxt_interface_utilities.varchar_256 := NULL;
337 l_org hxt_interface_utilities.varchar_256 := NULL;
338 l_location hxt_interface_utilities.varchar_256 := NULL;
339
340 -- local functions
341 FUNCTION clause_part (p_id IN NUMBER, p_clause VARCHAR2)
342 RETURN VARCHAR2
343 IS
344 l_clause_part hxt_interface_utilities.max_varchar := NULL;
345 BEGIN
346 IF p_id IS NOT NULL
347 THEN
348 l_clause_part := p_clause || TO_CHAR (p_id);
349 ELSE
350 l_clause_part := NULL;
351 END IF;
352
353 RETURN l_clause_part;
354 END clause_part;
355 BEGIN
356 hr_utility.set_location ('Entering ' || l_proc, 10);
357 l_location := clause_part (p_location_id, ' and paa.location_id = ');
358 l_payroll := clause_part (p_payroll_id, ' and paa.payroll_id = ');
359 l_org := clause_part (p_organization_id, ' and paa.organization_id = ');
360 l_person := clause_part (p_person_id, ' and paa.person_id = ');
361
362 IF ( p_gre_id IS NULL
363 AND p_location_id IS NULL
364 AND p_payroll_id IS NULL
365 AND p_organization_id IS NULL
366 AND p_person_id IS NULL
367 )
368 THEN
369 l_where_clause :=
370 '[DETAIL_BLOCK.RESOURCE_TYPE] {=''PERSON'' AND}
371 [DETAIL_BLOCK.RESOURCE_ID]
372 {in (select peo.person_id
373 from per_all_people_f peo
374 where peo.business_group_id = '
375 || p_bg_id
376 || ')}';
377 ELSIF p_gre_id IS NULL
378 THEN
379 l_where_clause :=
380 '[DETAIL_BLOCK.RESOURCE_TYPE] {=''PERSON'' AND}
381 [DETAIL_BLOCK.RESOURCE_ID]
382 {in (select paa.person_id
383 from per_all_assignments_f paa
384 where paa.business_group_id = '
385 || p_bg_id
386 || l_person
387 || l_payroll
388 || l_location
389 || l_org
390 || ')}';
391 ELSE
392 l_where_clause :=
393 '[DETAIL_BLOCK.RESOURCE_TYPE] {=''PERSON'' AND}
394 [DETAIL_BLOCK.RESOURCE_ID]
395 {in (select paa.person_id
396 from per_all_assignments_f paa,
397 hr_soft_coding_keyflex hsk
398 where paa.business_group_id = '
399 || p_bg_id
400 || l_person
401 || l_payroll
402 || l_location
403 || ' and paa.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
404 and hsk.segment1 = '''
405 || p_gre_id
406 || ''')}';
407 END IF;
408
409 hr_utility.set_location ('Leaving ' || l_proc, 100);
410 RETURN l_where_clause;
411 END where_clause;
412
413 PROCEDURE set_transaction_detail (
414 p_tbb_idx IN PLS_INTEGER,
415 p_status IN hxc_transactions.status%TYPE,
416 p_exception IN hxc_transactions.exception_description%TYPE
417 )
418 IS
419 l_proc CONSTANT proc_name := g_package || 'set_transaction_detail';
420 BEGIN
421 hr_utility.set_location ('Entering ' || l_proc, 10);
422 hxc_generic_retrieval_pkg.t_tx_detail_status (p_tbb_idx) := p_status;
423 hxc_generic_retrieval_pkg.t_tx_detail_exception (p_tbb_idx) :=
424 SUBSTR (p_exception, 1, g_max_message_size);
425 hr_utility.set_location ('Leaving ' || l_proc, 100);
426 END set_transaction_detail;
427
428 PROCEDURE set_transaction (
429 p_process_name IN hxc_retrieval_processes.NAME%TYPE,
430 p_status IN hxc_transactions.status%TYPE,
431 p_exception IN hxc_transactions.exception_description%TYPE
432 )
433 IS
434 l_proc CONSTANT proc_name := g_package || 'set_transaction';
435 BEGIN
436 hr_utility.set_location ('Entering ' || l_proc, 10);
437 hxc_generic_retrieval_utils.set_parent_statuses;
438 hxc_generic_retrieval_pkg.update_transaction_status
439 (p_process => p_process_name,
440 p_status => p_status,
441 p_exception_description => SUBSTR
442 (p_exception,
443 1,
444 g_max_message_size
445 ),
446 p_rollback => FALSE
447 );
448 hr_utility.set_location ('Leaving ' || l_proc, 100);
449 END set_transaction;
450
451 PROCEDURE set_successfull_trx_detail (p_tbb_idx IN PLS_INTEGER)
452 IS
453 l_proc CONSTANT proc_name
454 := g_package || 'set_successfull_trx_detail';
455 BEGIN
456 hr_utility.set_location ('Entering ' || l_proc, 10);
457 fnd_message.set_name (g_hxc_app_short_name, g_trx_detail_success_msg);
458 set_transaction_detail (p_tbb_idx => p_tbb_idx,
459 p_status => g_trx_success,
460 p_exception => fnd_message.get
461 );
462 hr_utility.set_location ('Leaving ' || l_proc, 100);
463 END set_successfull_trx_detail;
464
465 PROCEDURE set_successfull_trx (
466 p_process_name IN hxc_retrieval_processes.NAME%TYPE
467 )
468 IS
469 l_proc CONSTANT proc_name := g_package || 'set_successfull_trx';
470 BEGIN
471 hr_utility.set_location ('Entering ' || l_proc, 10);
472 fnd_message.set_name (g_hxc_app_short_name, g_trx_success_msg);
473 set_transaction (p_process_name => p_process_name,
474 p_status => g_trx_success,
475 p_exception => fnd_message.get
476 );
477 hr_utility.set_location ('Leaving ' || l_proc, 100);
478 END set_successfull_trx;
479
480 PROCEDURE set_sqlerror_trx_detail (p_tbb_idx IN PLS_INTEGER)
481 IS
482 l_proc CONSTANT proc_name := g_package || 'set_sqlerror_trx_detail';
483 BEGIN
484 hr_utility.set_location ('Entering ' || l_proc, 10);
485 hr_utility.set_location (' Error found = ' || SQLERRM, 20);
486 set_transaction_detail (p_tbb_idx => p_tbb_idx,
487 p_status => g_trx_error,
488 p_exception => 'The error is : '
489 || TO_CHAR (SQLCODE)
490 || ' '
491 || SQLERRM
492 );
493 hr_utility.set_location ('Leaving ' || l_proc, 100);
494 END set_sqlerror_trx_detail;
495
496 PROCEDURE set_sqlerror_trx (
497 p_process_name IN hxc_retrieval_processes.NAME%TYPE
498 )
499 IS
500 l_proc CONSTANT proc_name := g_package || 'set_sqlerror_trx';
501 BEGIN
502 hr_utility.set_location ('Entering ' || l_proc, 10);
503 set_transaction (p_process_name => p_process_name,
504 p_status => g_trx_error,
505 p_exception => 'The error is : '
506 || TO_CHAR (SQLCODE)
507 || ' '
508 || SQLERRM
509 );
510 hr_utility.set_location ('Leaving ' || l_proc, 100);
511 END set_sqlerror_trx;
512
513 PROCEDURE process_otlr_employees (
514 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
515 p_session_date IN DATE, -- Bug 6121705, need date for reversal batch creation
516 p_start_date IN VARCHAR2,
517 --hxc_time_building_blocks.start_time%TYPE,
518 p_end_date IN VARCHAR2,
519 --hxc_time_building_blocks.stop_time%TYPE,
520 p_where_clause IN hxt_interface_utilities.max_varchar,
521 p_retrieval_transaction_code IN hxc_transactions.transaction_code%TYPE,
522 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
523 p_unique_params IN hxt_interface_utilities.max_varchar,
524 p_incremental IN hxt_interface_utilities.flag_varchar
525 DEFAULT 'Y',
526 p_transfer_to_bee IN hxt_interface_utilities.flag_varchar
527 DEFAULT 'N',
528 p_no_otm IN OUT NOCOPY hxt_interface_utilities.flag_varchar
529 )
530 AS
531 l_proc CONSTANT proc_name := g_package || 'process_otlr_employees';
532
533 l_dup_count NUMBER; -- Bug 6121705
534
535 BEGIN
536 hr_utility.set_location ('Entering ' || l_proc, 10);
537 -- transfer the employees with OTM Rules = Yes
538 hxt_otc_retrieval_interface.transfer_to_otm
539 (p_bg_id => p_bg_id,
540 p_incremental => p_incremental,
541 p_start_date => p_start_date,
542 p_end_date => p_end_date,
543 p_where_clause => p_where_clause,
544 p_transfer_to_bee => p_transfer_to_bee,
545 p_retrieval_transaction_code => p_retrieval_transaction_code,
546 p_batch_ref => p_batch_ref,
547 p_no_otm => p_no_otm,
548 p_unique_params => p_unique_params,
549 p_since_date => l_since_date
550 );
551
552 -- Bug 6121705
553 -- For automatic reversal batch creation if there is a rule evaluation pref
554 -- change.
555
556 -- Earlier hxc_gen_retrieve_utils.chk_retrieve would have populated
557 -- HXC_BEE_PREF_ADJ_LINES with the details that were previously transferred
558 -- and had a preference change before transfer this time.
559 -- Check the count of those records which are for time store. Now we
560 -- are running OTM batches. Meaning, if there was an earlier batch, it would
561 -- have been from time store. There needs to be reverse batches for those.
562
563 -- The rownum condition is put there especially for performance. On worst
564 -- case there could be thousands of records in there, and you just wanna
565 -- know if there is atleast one. If the optimizer is not set to take the first
566 -- n rows, your count(*) would wait until all rows are returned. With the rownum
567 -- it will only pull out your first record if there exists one, else zero. So
568 -- the result is only 1 or 0.
569
570 SELECT COUNT(*)
571 INTO l_dup_count
572 FROM hxc_bee_pref_adj_lines
573 WHERE batch_source = 'Time Store'
574 AND ROWNUM < 2 ;
575
576
577 -- This condition will call a proc to create reverse Time Store batches for the
578 -- details in there.
579 IF l_dup_count > 0
580 THEN
581 make_adjustments_bee(p_batch_ref, -- we need the current batch reference for our adj batch
582 p_bg_id, -- bg_id to create batch
583 p_session_date); -- batch is created with this session date
584 END IF;
585
586 hr_utility.set_location ('Leaving ' || l_proc, 100);
587 END process_otlr_employees;
588
589 PROCEDURE extract_data_from_attr_tbl (
590 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
591 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
592 p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,
593 p_det_tbb_idx IN PLS_INTEGER,
594 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
595 p_effective_date IN pay_element_types_f.effective_start_date%TYPE,
596 p_attr_tbl_idx IN OUT NOCOPY PLS_INTEGER,
597 p_bee_rec IN OUT NOCOPY hxt_interface_utilities.bee_rec
598 )
599 AS
600 l_proc CONSTANT proc_name
601 := g_package || 'extract_data_from_attr_tbl';
602 e_no_element_type_id EXCEPTION;
603 l_start_attr_tbl_idx PLS_INTEGER;
604 BEGIN
605 hr_utility.set_location ('Entering ' || l_proc, 10);
606
607 IF (hxt_interface_utilities.is_in_sync
608 (p_check_tbb_id => p_attr_tbl (NVL (p_attr_tbl_idx,
609 p_attr_tbl.FIRST
610 )
611 ).bb_id,
612 p_against_tbb_id => p_tbb_id
613 )
614 )
615 THEN
616 -- We first have to find the element_id cause we need that for converting
617 -- the associated IVs.
618 p_bee_rec.pay_batch_line.element_type_id :=
619 hxt_interface_utilities.find_element_id_in_attr_tbl
620 (p_att_table => p_attr_tbl,
621 p_tbb_id => p_tbb_id,
622 p_start_position => p_attr_tbl_idx
623 );
624
625 IF (p_bee_rec.pay_batch_line.element_type_id IS NULL)
626 THEN
627 RAISE e_no_element_type_id;
628 END IF;
629
630 l_start_attr_tbl_idx := p_attr_tbl_idx;
631 -- Now find all the other data (IVs, Asg Data and Cost Segments)
632 hxt_interface_utilities.find_other_in_attr_tbl
633 (p_bg_id => p_bg_id,
634 p_att_table => p_attr_tbl,
635 p_tbb_id => p_tbb_id,
636 p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
637 p_cost_flex_id => p_cost_flex_id,
638 p_effective_date => p_effective_date,
639 p_start_position => l_start_attr_tbl_idx,
640 p_ending_position => p_attr_tbl_idx,
641 p_bee_rec => p_bee_rec
642 );
643 ELSE
644 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
645 fnd_message.set_token ('PROCEDURE', l_proc);
646 fnd_message.set_token ('STEP', 'tbb mismatch');
647 fnd_message.raise_error;
648 END IF;
649
650 hr_utility.set_location ('Leaving ' || l_proc, 100);
651 EXCEPTION
652 WHEN e_no_element_type_id
653 THEN
654 fnd_message.set_name (g_hxc_app_short_name,
655 'HXC_HRPAY_RET_NO_ELE_TYPE_ID'
656 );
657 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
658 p_status => g_trx_error,
659 p_exception => fnd_message.get
660 );
661 RAISE e_continue;
662 END extract_data_from_attr_tbl;
663
664 PROCEDURE parse_cost_flex (
665 p_business_group_id IN pay_batch_headers.business_group_id%TYPE,
666 p_bee_rec IN OUT NOCOPY hxt_interface_utilities.bee_rec
667 )
668 AS
669 l_proc CONSTANT proc_name := g_package || 'parse_cost_flex';
670 BEGIN
671 hr_utility.set_location ('Entering ' || l_proc, 10);
672
673 IF NOT (hxt_interface_utilities.cost_segments_all_null (p_bee_rec))
674 THEN
675 -- get the cost_allocation_flexfield_id (we can do this after the COST
676 -- segments have been set) ...
677 p_bee_rec.pay_batch_line.cost_allocation_keyflex_id :=
678 hxt_interface_utilities.cost_allocation_kff_id
679 (p_business_group_id => p_business_group_id,
680 p_bee_rec => p_bee_rec
681 );
682 -- ... and the concatinated segments
683 p_bee_rec.pay_batch_line.concatenated_segments :=
684 hxt_interface_utilities.costflex_concat_segments
685 (p_cost_allocation_keyflex_id => p_bee_rec.pay_batch_line.cost_allocation_keyflex_id
686 );
687 ELSE
688 p_bee_rec.pay_batch_line.cost_allocation_keyflex_id := NULL;
689 p_bee_rec.pay_batch_line.concatenated_segments := NULL;
690 END IF;
691
692 hr_utility.set_location ('Leaving ' || l_proc, 100);
693 END parse_cost_flex;
694
695 PROCEDURE bee_batch_line (
696 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
697 p_tbb_rec IN hxc_generic_retrieval_pkg.r_building_blocks,
698 p_det_tbb_idx IN PLS_INTEGER,
699 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
700 p_attr_tbl_idx IN OUT NOCOPY PLS_INTEGER,
701 p_bee_rec OUT NOCOPY hxt_interface_utilities.bee_rec,
702 p_cost_flex_id IN per_business_groups_perf.cost_allocation_structure%TYPE,
703 p_is_old IN BOOLEAN DEFAULT FALSE
704 )
705 AS
706 l_proc CONSTANT proc_name := g_package || 'bee_batch_line';
707 l_effective_date DATE := TRUNC (p_tbb_rec.start_time);
708 e_no_assignment EXCEPTION;
709 l_geocode VARCHAR2 (21);
710 l_hours_iv_position PLS_INTEGER;
711 l_jurisdiction_iv_position PLS_INTEGER;
712 BEGIN
713 hr_utility.set_location ('Entering ' || l_proc, 10);
714 extract_data_from_attr_tbl (p_bg_id => p_bg_id,
715 p_attr_tbl => p_attr_tbl,
716 p_tbb_id => p_tbb_rec.bb_id,
717 p_det_tbb_idx => p_det_tbb_idx,
718 p_cost_flex_id => p_cost_flex_id,
719 p_effective_date => l_effective_date,
720 p_attr_tbl_idx => p_attr_tbl_idx,
721 p_bee_rec => p_bee_rec
722 );
723 -- get input value sequence for Hours and Jurisdiction
724 hxt_interface_utilities.hours_iv_position
725 (p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
726 p_effective_date => l_effective_date,
727 p_hours_iv_position => l_hours_iv_position,
728 p_jurisdiction_iv_position => l_jurisdiction_iv_position,
729 p_iv_type => hxt_interface_utilities.g_hour_juris_iv
730 );
731 -- Assign Hours to the right input value
732 hxt_interface_utilities.assign_iv
733 (p_iv_seq => l_hours_iv_position,
734 p_value => (p_tbb_rec.measure --Days Vs Hour Enhancement
735 )
736 * hxt_interface_utilities.hours_factor
737 (p_is_old),
738 p_bee_rec => p_bee_rec
739 );
740
741 -- get geocode if its input value sequence is not null
742 IF (l_jurisdiction_iv_position IS NOT NULL)
743 THEN
744 l_geocode :=
745 hxt_interface_utilities.get_geocode_from_attr_tab
746 (p_attr_tbl,
747 p_tbb_rec.bb_id,
748 NULL
749 );
750
751 IF (l_geocode <> '00-000-0000')
752 THEN
753 hxt_interface_utilities.assign_iv
754 (p_iv_seq => l_jurisdiction_iv_position,
755 p_value => l_geocode,
756 p_bee_rec => p_bee_rec
757 );
758 END IF;
759 END IF;
760
761 -- set the assignment if it has not bee set yet (i.e. there was no
762 -- assignment attribute)
763 IF (p_bee_rec.pay_batch_line.assignment_id IS NULL)
764 THEN
765 hxt_interface_utilities.assignment_info
766 (p_tbb_rec => p_tbb_rec,
767 p_assignment_id => p_bee_rec.pay_batch_line.assignment_id,
768 p_assignment_number => p_bee_rec.pay_batch_line.assignment_number
769 );
770 END IF;
771
772 IF (p_bee_rec.pay_batch_line.assignment_id IS NULL)
773 THEN
774 RAISE e_no_assignment;
775 END IF;
776
777 parse_cost_flex (p_business_group_id => p_bg_id,
778 p_bee_rec => p_bee_rec);
779 hr_utility.set_location ('Leaving ' || l_proc, 100);
780 EXCEPTION
781 WHEN e_no_assignment
782 THEN
783 fnd_message.set_name (g_hxc_app_short_name,
784 'HXC_HRPAY_RET_NO_ASSIGN'
785 );
786 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
787 p_status => g_trx_error,
788 p_exception => fnd_message.get
789 );
790 RAISE e_continue;
791 END bee_batch_line;
792
793 FUNCTION batch_name (
794 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
795 p_bg_id IN pay_batch_headers.business_group_id%TYPE
796 )
797 RETURN pay_batch_headers.batch_name%TYPE
798 AS
799 l_proc CONSTANT proc_name := g_package || 'batch_name';
800 l_batch_suffix pay_batch_headers.batch_name%TYPE; -- NUMBER;
801 l_batch_name pay_batch_headers.batch_name%TYPE;
802 BEGIN
803 hr_utility.set_location ('Entering:' || l_proc, 10);
804 l_batch_suffix :=
805 TO_CHAR (hxt_interface_utilities.conc_request_id_suffix);
806 l_batch_suffix :=
807 l_batch_suffix
808 || hxt_interface_utilities.batchname_suffix_connector
809 || hxt_interface_utilities.free_batch_suffix
810 (p_batch_reference => p_batch_reference,
811 p_bg_id => p_bg_id
812 );
813 l_batch_name :=
814 p_batch_reference
815 || hxt_interface_utilities.batchname_suffix_connector
816 || l_batch_suffix;
817 hr_utility.set_location (' returning batch name: ' || l_batch_name,
818 20);
819 hr_utility.set_location ('Leaving:' || l_proc, 100);
820 RETURN l_batch_name;
821 END batch_name;
822
823 FUNCTION create_batch_header (
824 p_batch_name IN pay_batch_headers.batch_name%TYPE,
825 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
826 p_batch_source IN pay_batch_headers.batch_source%TYPE
827 DEFAULT g_time_store_batch_source,
828 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
829 p_session_date IN DATE,
830 p_det_tbb_idx IN PLS_INTEGER
831 )
832 RETURN pay_batch_headers.batch_id%TYPE
833 AS
834 l_proc CONSTANT proc_name
835 := g_package || 'create_batch_header';
836 l_object_version_number pay_batch_headers.object_version_number%TYPE;
837 l_new_batch pay_batch_headers.batch_id%TYPE;
838 BEGIN
839 hr_utility.set_location ('Entering:' || l_proc, 10);
840 pay_batch_element_entry_api.create_batch_header
841 (p_session_date => p_session_date,
842 p_batch_name => p_batch_name,
843 p_business_group_id => p_bg_id,
844 p_action_if_exists => g_insert_if_exist,
845 p_batch_reference => p_batch_reference,
846 p_batch_source => p_batch_source,
847 p_batch_id => l_new_batch,
848 p_object_version_number => l_object_version_number
849 );
850 record_batch_info (p_batch_id => l_new_batch,
851 p_business_group_id => p_bg_id,
852 p_batch_reference => p_batch_reference,
853 p_batch_name => p_batch_name
854 );
855 hr_utility.set_location (' returning batch_id = :' || l_new_batch, 20);
856 hr_utility.set_location ('Leaving:' || l_proc, 100);
857 RETURN l_new_batch;
858 EXCEPTION
859 WHEN OTHERS
860 THEN
861 fnd_message.set_name (g_hxc_app_short_name,
862 'HXC_HRPAY_RET_BATCH_HDR_API'
863 );
864 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
865 p_status => g_trx_error,
866 p_exception => fnd_message.get
867 );
868 RAISE e_halt;
869 END create_batch_header;
870
871 FUNCTION create_batch_header (
872 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
873 p_batch_source IN pay_batch_headers.batch_source%TYPE
874 DEFAULT g_time_store_batch_source,
875 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
876 p_session_date IN DATE,
877 p_det_tbb_idx IN PLS_INTEGER
878 )
879 RETURN pay_batch_headers.batch_id%TYPE
880 AS
881 l_proc CONSTANT proc_name
882 := g_package || 'create_batch_header';
883 l_object_version_number pay_batch_headers.object_version_number%TYPE;
884 l_batch_name pay_batch_headers.batch_name%TYPE;
885 l_new_batch pay_batch_headers.batch_id%TYPE;
886 BEGIN
887 hr_utility.set_location ('Entering:' || l_proc, 10);
888 l_batch_name :=
889 batch_name (p_batch_reference => p_batch_reference,
890 p_bg_id => p_bg_id
891 );
892 -- I might need to add a check here for max_lines_exceeded if I add
893 -- functionality to add data to existing batches...
894 l_new_batch :=
895 create_batch_header (p_batch_name => l_batch_name,
896 p_batch_reference => p_batch_reference,
897 p_batch_source => p_batch_source,
898 p_bg_id => p_bg_id,
899 p_session_date => p_session_date,
900 p_det_tbb_idx => p_det_tbb_idx
901 );
902 hr_utility.set_location (' returning batch_id = :' || l_new_batch, 20);
903 hr_utility.set_location ('Leaving:' || l_proc, 100);
904 RETURN l_new_batch;
905 END create_batch_header;
906
907 PROCEDURE create_batch_line (
908 p_batch_id IN pay_batch_headers.batch_id%TYPE,
909 p_det_tbb_idx IN PLS_INTEGER,
910 p_session_date IN DATE,
911 p_effective_date IN DATE,
912 p_batch_sequence IN pay_batch_lines.batch_sequence%TYPE,
913 p_bee_rec IN hxt_interface_utilities.bee_rec,
914 p_bg_id IN pay_batch_headers.business_group_id%TYPE
915 )
916 AS
917 l_proc CONSTANT proc_name := g_package || 'create_batch_line';
918 l_total_lines NUMBER;
919 l_batch_line_id pay_batch_lines.batch_line_id%TYPE;
920 l_object_version_number pay_batch_lines.object_version_number%TYPE;
921 BEGIN
922 hr_utility.set_location ('Entering:' || l_proc, 10);
923 pay_batch_element_entry_api.create_batch_line
924 (p_session_date => p_session_date,
925 p_batch_id => p_batch_id,
926 p_assignment_id => p_bee_rec.pay_batch_line.assignment_id,
927 p_assignment_number => p_bee_rec.pay_batch_line.assignment_number,
928 p_batch_sequence => p_batch_sequence,
929 p_concatenated_segments => p_bee_rec.pay_batch_line.concatenated_segments,
930 p_cost_allocation_keyflex_id => p_bee_rec.pay_batch_line.cost_allocation_keyflex_id,
931 p_date_earned => p_effective_date,
932 p_effective_date => p_effective_date,
933 p_effective_start_date => p_effective_date,
934 p_effective_end_date => p_effective_date,
935 p_element_name => p_bee_rec.pay_batch_line.element_name,
936 p_element_type_id => p_bee_rec.pay_batch_line.element_type_id,
937 p_segment1 => p_bee_rec.pay_batch_line.segment1,
938 p_segment2 => p_bee_rec.pay_batch_line.segment2,
939 p_segment3 => p_bee_rec.pay_batch_line.segment3,
940 p_segment4 => p_bee_rec.pay_batch_line.segment4,
941 p_segment5 => p_bee_rec.pay_batch_line.segment5,
942 p_segment6 => p_bee_rec.pay_batch_line.segment6,
943 p_segment7 => p_bee_rec.pay_batch_line.segment7,
944 p_segment8 => p_bee_rec.pay_batch_line.segment8,
945 p_segment9 => p_bee_rec.pay_batch_line.segment9,
946 p_segment10 => p_bee_rec.pay_batch_line.segment10,
947 p_segment11 => p_bee_rec.pay_batch_line.segment11,
948 p_segment12 => p_bee_rec.pay_batch_line.segment12,
949 p_segment13 => p_bee_rec.pay_batch_line.segment13,
950 p_segment14 => p_bee_rec.pay_batch_line.segment14,
951 p_segment15 => p_bee_rec.pay_batch_line.segment15,
952 p_segment16 => p_bee_rec.pay_batch_line.segment16,
953 p_segment17 => p_bee_rec.pay_batch_line.segment17,
954 p_segment18 => p_bee_rec.pay_batch_line.segment18,
955 p_segment19 => p_bee_rec.pay_batch_line.segment19,
956 p_segment20 => p_bee_rec.pay_batch_line.segment20,
957 p_segment21 => p_bee_rec.pay_batch_line.segment21,
958 p_segment22 => p_bee_rec.pay_batch_line.segment22,
959 p_segment23 => p_bee_rec.pay_batch_line.segment23,
960 p_segment24 => p_bee_rec.pay_batch_line.segment24,
961 p_segment25 => p_bee_rec.pay_batch_line.segment25,
962 p_segment26 => p_bee_rec.pay_batch_line.segment26,
963 p_segment27 => p_bee_rec.pay_batch_line.segment27,
964 p_segment28 => p_bee_rec.pay_batch_line.segment28,
965 p_segment29 => p_bee_rec.pay_batch_line.segment29,
966 p_segment30 => p_bee_rec.pay_batch_line.segment30,
967 p_value_1 => display_value
968 (p_bee_rec.pay_batch_line.element_type_id,
969 p_bee_rec.pay_batch_line.value_1,
970 1,
971 p_session_date,
972 p_bg_id
973 ),
974 p_value_2 => display_value
975 (p_bee_rec.pay_batch_line.element_type_id,
976 p_bee_rec.pay_batch_line.value_2,
977 2,
978 p_session_date,
979 p_bg_id
980 ),
981 p_value_3 => display_value
982 (p_bee_rec.pay_batch_line.element_type_id,
983 p_bee_rec.pay_batch_line.value_3,
984 3,
985 p_session_date,
986 p_bg_id
987 ),
988 p_value_4 => display_value
989 (p_bee_rec.pay_batch_line.element_type_id,
990 p_bee_rec.pay_batch_line.value_4,
991 4,
992 p_session_date,
993 p_bg_id
994 ),
995 p_value_5 => display_value
996 (p_bee_rec.pay_batch_line.element_type_id,
997 p_bee_rec.pay_batch_line.value_5,
998 5,
999 p_session_date,
1000 p_bg_id
1001 ),
1002 p_value_6 => display_value
1003 (p_bee_rec.pay_batch_line.element_type_id,
1004 p_bee_rec.pay_batch_line.value_6,
1005 6,
1006 p_session_date,
1007 p_bg_id
1008 ),
1009 p_value_7 => display_value
1010 (p_bee_rec.pay_batch_line.element_type_id,
1011 p_bee_rec.pay_batch_line.value_7,
1012 7,
1013 p_session_date,
1014 p_bg_id
1015 ),
1016 p_value_8 => display_value
1017 (p_bee_rec.pay_batch_line.element_type_id,
1018 p_bee_rec.pay_batch_line.value_8,
1019 8,
1020 p_session_date,
1021 p_bg_id
1022 ),
1023 p_value_9 => display_value
1024 (p_bee_rec.pay_batch_line.element_type_id,
1025 p_bee_rec.pay_batch_line.value_9,
1026 9,
1027 p_session_date,
1028 p_bg_id
1029 ),
1030 p_value_10 => display_value
1031 (p_bee_rec.pay_batch_line.element_type_id,
1032 p_bee_rec.pay_batch_line.value_10,
1033 10,
1034 p_session_date,
1035 p_bg_id
1036 ),
1037 p_value_11 => display_value
1038 (p_bee_rec.pay_batch_line.element_type_id,
1039 p_bee_rec.pay_batch_line.value_11,
1040 11,
1041 p_session_date,
1042 p_bg_id
1043 ),
1044 p_value_12 => display_value
1045 (p_bee_rec.pay_batch_line.element_type_id,
1046 p_bee_rec.pay_batch_line.value_12,
1047 12,
1048 p_session_date,
1049 p_bg_id
1050 ),
1051 p_value_13 => display_value
1052 (p_bee_rec.pay_batch_line.element_type_id,
1053 p_bee_rec.pay_batch_line.value_13,
1054 13,
1055 p_session_date,
1056 p_bg_id
1057 ),
1058 p_value_14 => display_value
1059 (p_bee_rec.pay_batch_line.element_type_id,
1060 p_bee_rec.pay_batch_line.value_14,
1061 14,
1062 p_session_date,
1063 p_bg_id
1064 ),
1065 p_value_15 => display_value
1066 (p_bee_rec.pay_batch_line.element_type_id,
1067 p_bee_rec.pay_batch_line.value_15,
1068 15,
1069 p_session_date,
1070 p_bg_id
1071 ),
1072 p_batch_line_id => l_batch_line_id,
1073 p_object_version_number => l_object_version_number
1074 );
1075 hr_utility.set_location ('Leaving:' || l_proc, 100);
1076 EXCEPTION
1077 WHEN OTHERS
1078 THEN
1079 fnd_message.set_name (g_hxc_app_short_name,
1080 'HXC_HRPAY_RET_BATCH_LINE_API'
1081 );
1082 set_transaction_detail (p_tbb_idx => p_det_tbb_idx,
1083 p_status => g_trx_error,
1084 p_exception => SQLERRM
1085 );
1086 RAISE e_continue;
1087 END create_batch_line;
1088
1089 PROCEDURE add_to_batch (
1090 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1091 p_batch_id IN OUT NOCOPY pay_batch_headers.batch_id%TYPE,
1092 p_det_tbb_idx IN PLS_INTEGER,
1093 p_batch_sequence IN OUT NOCOPY pay_batch_lines.batch_sequence%TYPE,
1094 p_batch_lines IN OUT NOCOPY PLS_INTEGER,
1095 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1096 p_session_date IN DATE,
1097 p_effective_date IN DATE,
1098 p_bee_rec IN hxt_interface_utilities.bee_rec
1099 )
1100 AS
1101 l_proc CONSTANT proc_name := g_package || 'add_to_batch';
1102 l_max_lines_exceeded BOOLEAN;
1103 BEGIN
1104 hr_utility.set_location ('Entering:' || l_proc, 10);
1105
1106 IF (p_batch_id IS NULL)
1107 THEN -- first call
1108 p_batch_id :=
1109 create_batch_header (p_batch_reference => p_batch_reference,
1110 p_bg_id => p_bg_id,
1111 p_session_date => p_session_date,
1112 p_det_tbb_idx => p_det_tbb_idx
1113 );
1114 p_batch_sequence := 1;
1115 p_batch_lines := 0;
1116 ELSE
1117 IF ( g_assignment_id <> p_bee_rec.pay_batch_line.assignment_id
1118 AND g_assignment_id <> -1
1119 )
1120 THEN
1121 hxt_interface_utilities.max_lines_exceeded
1122 (p_batch_id => p_batch_id,
1123 p_number_lines => p_batch_lines,
1124 p_max_lines_exceeded => l_max_lines_exceeded
1125 );
1126
1127 IF (l_max_lines_exceeded)
1128 THEN
1129 p_batch_id :=
1130 create_batch_header
1131 (p_batch_reference => p_batch_reference,
1132 p_bg_id => p_bg_id,
1133 p_session_date => p_session_date,
1134 p_det_tbb_idx => p_det_tbb_idx
1135 );
1136 p_batch_sequence := 1;
1137 p_batch_lines := 0;
1138 END IF; -- max_lines_exceeded
1139 END IF; -- (p_batch_id IS NULL)
1140 END IF; -- g_assignment_id test
1141
1142 create_batch_line (p_batch_id => p_batch_id,
1143 p_det_tbb_idx => p_det_tbb_idx,
1144 p_session_date => p_session_date,
1145 p_effective_date => p_effective_date,
1146 p_batch_sequence => p_batch_sequence,
1147 p_bee_rec => p_bee_rec,
1148 p_bg_id => p_bg_id
1149 );
1150 g_assignment_id := p_bee_rec.pay_batch_line.assignment_id;
1151 p_batch_lines := p_batch_lines + 1;
1152 p_batch_sequence := p_batch_sequence + 1;
1153 hr_utility.set_location (' OUT p_batch_id = ' || p_batch_id, 20);
1154 hr_utility.set_location (' OUT p_batch_sequence = '
1155 || p_batch_sequence,
1156 30
1157 );
1158 hr_utility.set_location (' OUT p_batch_lines = ' || p_batch_lines, 40);
1159 hr_utility.set_location ('Leaving:' || l_proc, 100);
1160 END add_to_batch;
1161
1162 PROCEDURE add_lines_to_bee_batch (
1163 p_batch_reference IN pay_batch_headers.batch_reference%TYPE,
1164 p_bg_id IN pay_batch_headers.business_group_id%TYPE,
1165 p_session_date IN DATE,
1166 p_tbb_tbl IN hxc_generic_retrieval_pkg.t_building_blocks,
1167 p_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
1168 p_old_tbb_tbl IN hxc_generic_retrieval_pkg.t_building_blocks,
1169 p_old_attr_tbl IN hxc_generic_retrieval_pkg.t_time_attribute,
1170 p_batch_id IN OUT NOCOPY NUMBER,
1171 p_retro_batch_id IN OUT NOCOPY NUMBER
1172 )
1173 AS
1174 l_proc CONSTANT proc_name
1175 := g_package || 'add_lines_to_bee_batch';
1176 l_det_tbb_idx PLS_INTEGER;
1177 l_det_old_tbb_idx PLS_INTEGER := 0;
1178 l_det_attr_idx PLS_INTEGER;
1179 l_det_old_attr_idx PLS_INTEGER;
1180 l_bee_rec hxt_interface_utilities.bee_rec;
1181 l_old_bee_rec hxt_interface_utilities.bee_rec;
1182 l_empty_bee_rec hxt_interface_utilities.bee_rec;
1183 l_cost_flex_id per_business_groups_perf.cost_allocation_structure%TYPE
1184 := hxt_interface_utilities.cost_flex_structure_id (p_bg_id);
1185 l_batch_id pay_batch_headers.batch_reference%TYPE;
1186 l_batch_sequence pay_batch_lines.batch_sequence%TYPE;
1187 l_retro_batch_id pay_batch_headers.batch_reference%TYPE;
1188 l_retro_batch_sequence pay_batch_lines.batch_sequence%TYPE;
1189 l_batch_lines PLS_INTEGER;
1190 l_retro_batch_lines PLS_INTEGER;
1191 BEGIN
1192 hr_utility.set_location ('Entering ' || l_proc, 10);
1193 l_batch_id := p_batch_id;
1194 l_retro_batch_id := p_retro_batch_id;
1195 l_det_tbb_idx := p_tbb_tbl.FIRST;
1196
1197 <<process_all_detail_tbb>>
1198 LOOP
1199
1200 <<processing_tbb>>
1201 BEGIN
1202 EXIT process_all_detail_tbb WHEN NOT p_tbb_tbl.EXISTS
1203 (l_det_tbb_idx);
1204
1205 IF (hxt_interface_utilities.is_changed (p_tbb_tbl (l_det_tbb_idx))
1206 )
1207 THEN -- get previously transferred line
1208 l_det_old_tbb_idx := l_det_old_tbb_idx + 1;
1209
1210 IF (hxt_interface_utilities.is_in_sync
1211 (p_check_tbb_id => p_old_tbb_tbl
1212 (l_det_old_tbb_idx).bb_id,
1213 p_against_tbb_id => p_tbb_tbl (l_det_tbb_idx).bb_id
1214 )
1215 )
1216 THEN
1217 bee_batch_line
1218 (p_bg_id => p_bg_id,
1219 p_tbb_rec => p_old_tbb_tbl
1220 (l_det_old_tbb_idx),
1221 p_det_tbb_idx => l_det_old_tbb_idx,
1222 p_attr_tbl => p_old_attr_tbl,
1223 p_attr_tbl_idx => l_det_old_attr_idx,
1224 p_bee_rec => l_old_bee_rec,
1225 p_cost_flex_id => l_cost_flex_id,
1226 p_is_old => TRUE
1227 );
1228 -- add to retro batch for backing out
1229 add_to_batch
1230 (p_batch_reference => p_batch_reference
1231 || retro_batch_suffix,
1232 p_batch_id => l_retro_batch_id,
1233 p_det_tbb_idx => l_det_old_tbb_idx,
1234 p_batch_sequence => l_retro_batch_sequence,
1235 p_batch_lines => l_retro_batch_lines,
1236 p_bg_id => p_bg_id,
1237 p_session_date => p_session_date,
1238 p_effective_date => TRUNC
1239 (p_old_tbb_tbl
1240 (l_det_old_tbb_idx).start_time
1241 ),
1242 p_bee_rec => l_old_bee_rec
1243 );
1244
1245 IF NOT (hxt_interface_utilities.is_deleted
1246 (p_tbb_tbl (l_det_tbb_idx)
1247 )
1248 )
1249 THEN -- must be an update to an existing BEE entry
1250 bee_batch_line (p_bg_id => p_bg_id,
1251 p_tbb_rec => p_tbb_tbl
1252 (l_det_tbb_idx),
1253 p_det_tbb_idx => l_det_tbb_idx,
1254 p_attr_tbl => p_attr_tbl,
1255 p_attr_tbl_idx => l_det_attr_idx,
1256 p_bee_rec => l_bee_rec,
1257 p_cost_flex_id => l_cost_flex_id
1258 );
1259
1260 -- Temporary switch allowing simulation of old usage
1261 -- Old usage = New and Update rows go into New Batch
1262 -- Previously Transferred rows go into Retro Batch
1263 -- New usage = New rows go into New Batch
1264 -- Previously Transferred and Updated rows go into
1265 -- Retro Batch
1266 IF (hxt_interface_utilities.use_old_retro_batches)
1267 THEN
1268 add_to_batch
1269 (p_batch_reference => p_batch_reference,
1270 p_batch_id => l_batch_id,
1271 p_det_tbb_idx => l_det_tbb_idx,
1272 p_batch_sequence => l_batch_sequence,
1273 p_batch_lines => l_batch_lines,
1274 p_bg_id => p_bg_id,
1275 p_session_date => p_session_date,
1276 p_effective_date => TRUNC
1277 (p_tbb_tbl
1278 (l_det_tbb_idx).start_time
1279 ),
1280 p_bee_rec => l_bee_rec
1281 );
1282 ELSE
1283 add_to_batch
1284 (p_batch_reference => p_batch_reference
1285 || retro_batch_suffix,
1286 p_batch_id => l_retro_batch_id,
1287 p_det_tbb_idx => l_det_tbb_idx,
1288 p_batch_sequence => l_retro_batch_sequence,
1289 p_batch_lines => l_retro_batch_lines,
1290 p_bg_id => p_bg_id,
1291 p_session_date => p_session_date,
1292 p_effective_date => TRUNC
1293 (p_tbb_tbl
1294 (l_det_tbb_idx).start_time
1295 ),
1296 p_bee_rec => l_bee_rec
1297 );
1298 END IF;
1299 ELSE
1300 -- Delete, so we only need to backout the previously
1301 -- transferred data which we already did so nothing to do
1302 l_det_attr_idx :=
1303 hxt_interface_utilities.skip_attributes
1304 (p_att_table => p_attr_tbl,
1305 p_tbb_id => p_tbb_tbl
1306 (l_det_tbb_idx).bb_id,
1307 p_start_position => l_det_attr_idx
1308 );
1309 l_bee_rec := l_empty_bee_rec;
1310 END IF;
1311 ELSE -- IF (hxt_interface_utilities.is_in_sync ...)
1312 fnd_message.set_name ('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1313 fnd_message.set_token ('PROCEDURE', l_proc);
1314 fnd_message.set_token ('STEP', 'tbb mismatch');
1315 fnd_message.raise_error;
1316 END IF;
1317 ELSE -- new record (i.e. never transferred to BEE before)
1318 IF NOT (hxt_interface_utilities.is_deleted
1319 (p_tbb_tbl (l_det_tbb_idx)
1320 )
1321 )
1322 THEN
1323 bee_batch_line (p_bg_id => p_bg_id,
1324 p_tbb_rec => p_tbb_tbl
1325 (l_det_tbb_idx),
1326 p_det_tbb_idx => l_det_tbb_idx,
1327 p_attr_tbl => p_attr_tbl,
1328 p_attr_tbl_idx => l_det_attr_idx,
1329 p_bee_rec => l_bee_rec,
1330 p_cost_flex_id => l_cost_flex_id
1331 );
1332 add_to_batch
1333 (p_batch_reference => p_batch_reference,
1334 p_batch_id => l_batch_id,
1335 p_det_tbb_idx => l_det_tbb_idx,
1336 p_batch_sequence => l_batch_sequence,
1337 p_batch_lines => l_batch_lines,
1338 p_bg_id => p_bg_id,
1339 p_session_date => p_session_date,
1340 p_effective_date => TRUNC
1341 (p_tbb_tbl (l_det_tbb_idx).start_time
1342 ),
1343 p_bee_rec => l_bee_rec
1344 );
1345 ELSE
1346 -- deleted but never existed in BEE so we don't have to do anything
1347 l_det_attr_idx :=
1348 hxt_interface_utilities.skip_attributes
1349 (p_att_table => p_attr_tbl,
1350 p_tbb_id => p_tbb_tbl
1351 (l_det_tbb_idx).bb_id,
1352 p_start_position => l_det_attr_idx
1353 );
1354 l_bee_rec := l_empty_bee_rec;
1355 END IF;
1356 END IF;
1357
1358 set_successfull_trx_detail (p_tbb_idx => l_det_tbb_idx);
1359 l_det_tbb_idx := p_tbb_tbl.NEXT (l_det_tbb_idx);
1360 EXCEPTION
1361 WHEN e_continue
1362 THEN
1363 l_det_attr_idx :=
1364 hxt_interface_utilities.skip_attributes
1365 (p_att_table => p_attr_tbl,
1366 p_tbb_id => p_tbb_tbl
1367 (l_det_tbb_idx).bb_id,
1368 p_start_position => l_det_attr_idx
1369 );
1370 l_det_old_attr_idx :=
1371 hxt_interface_utilities.skip_attributes
1372 (p_att_table => p_old_attr_tbl,
1373 p_tbb_id => p_tbb_tbl
1374 (l_det_tbb_idx).bb_id,
1375 p_start_position => l_det_old_attr_idx
1376 );
1377 l_bee_rec := l_empty_bee_rec;
1378 l_det_tbb_idx := p_tbb_tbl.NEXT (l_det_tbb_idx);
1379 WHEN e_halt
1380 THEN
1381 RAISE;
1382 WHEN OTHERS
1383 THEN
1384 set_sqlerror_trx_detail (p_tbb_idx => l_det_tbb_idx);
1385 l_det_tbb_idx := p_tbb_tbl.NEXT (l_det_tbb_idx);
1386 END processing_tbb;
1387 END LOOP process_all_detail_tbb;
1388
1389 set_successfull_trx (g_bee_retrieval_process);
1390 hxt_interface_utilities.perform_commit;
1391 p_batch_id := l_batch_id;
1392 p_retro_batch_id := l_retro_batch_id;
1393 hr_utility.set_location ('Leaving ' || l_proc, 100);
1394 END add_lines_to_bee_batch;
1395
1396 PROCEDURE process_non_otlr_employees (
1397 p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
1398 p_start_date IN hxc_time_building_blocks.start_time%TYPE,
1399 p_end_date IN hxc_time_building_blocks.stop_time%TYPE,
1400 p_session_date IN DATE,
1401 p_where_clause IN hxt_interface_utilities.max_varchar,
1402 p_retrieval_transaction_code IN hxc_transactions.transaction_code%TYPE,
1403 p_batch_ref IN pay_batch_headers.batch_reference%TYPE,
1404 p_unique_params IN hxt_interface_utilities.max_varchar,
1405 p_status_in_bee IN VARCHAR2,
1406 p_incremental IN hxt_interface_utilities.flag_varchar
1407 DEFAULT 'Y',
1408 p_transfer_to_bee IN hxt_interface_utilities.flag_varchar
1409 DEFAULT 'N',
1410 p_no_otm IN hxt_interface_utilities.flag_varchar
1411 DEFAULT 'N'
1412 )
1413 AS
1414 l_proc CONSTANT proc_name
1415 := g_package || 'process_non_otlr_employees';
1416 l_supa_chunk_batch_id pay_batch_headers.batch_reference%TYPE
1417 := NULL;
1418 l_supa_chunk_retro_batch_id pay_batch_headers.batch_reference%TYPE
1419 := NULL;
1420
1421 l_dup_count NUMBER; -- Bug 6121705
1422
1423 BEGIN
1424 hr_utility.set_location ('Entering ' || l_proc, 10);
1425
1426 <<retrieve_in_chuncks>>
1427 LOOP
1428 -- call generic retrieval to get the non-OTM employees Timecards
1429 -- The retrieval process will only retrieve part of the population
1430 -- as set by the profile option OTL: Transfer Batch Size which is why
1431 -- it is called in a loop
1432 hxc_generic_retrieval_pkg.execute_retrieval_process
1433 (p_process => g_bee_retrieval_process,
1434 p_transaction_code => p_retrieval_transaction_code,
1435 p_start_date => p_start_date,
1436 p_end_date => p_end_date,
1437 p_incremental => p_incremental,
1438 p_rerun_flag => 'N',
1439 p_where_clause => p_where_clause,
1440 p_scope => hxc_timecard.c_day_scope,
1441 p_clusive => g_inclusive,
1442 p_unique_params => p_unique_params,
1443 p_since_date => l_since_date
1444 );
1445
1446 IF (hxt_interface_utilities.detail_lines_retrieved
1447 (hxc_generic_retrieval_pkg.t_detail_bld_blks)
1448 )
1449 THEN
1450 add_lines_to_bee_batch
1451 (p_batch_ref,
1452 p_bg_id,
1453 p_session_date,
1454 hxc_generic_retrieval_pkg.t_detail_bld_blks,
1455 hxc_generic_retrieval_pkg.t_detail_attributes,
1456 hxc_generic_retrieval_pkg.t_old_detail_bld_blks,
1457 hxc_generic_retrieval_pkg.t_old_detail_attributes,
1458 l_supa_chunk_batch_id,
1459 l_supa_chunk_retro_batch_id
1460 );
1461 ELSE
1462 hxc_generic_retrieval_pkg.update_transaction_status
1463 (p_process => 'BEE Retrieval Process',
1464 p_status => 'SUCCESS',
1465 p_exception_description => NULL
1466 );
1467 EXIT retrieve_in_chuncks;
1468 END IF;
1469
1470 -- Bug 6121705
1471 -- For automatic reversal of batches transferred for rules evaluation pref change
1472 -- Check the count of OTM details to be reversed and call the adjustments procedure.
1473 -- Count(*) and rownum as in process_otlr_employees for returning 0 or 1 if data
1474 -- exists or not. We dont want the exact number, just wanna know if it exists or not.
1475
1476 SELECT COUNT(*)
1477 INTO l_dup_count
1478 FROM hxc_bee_pref_adj_lines
1479 WHERE batch_source = 'OTM'
1480 AND ROWNUM < 2;
1481
1482
1483 -- If this is true, it means you have to make adjustments. Call the proc.
1484 IF ( l_dup_count > 0)
1485 THEN
1486 make_adjustments_otm( p_bg_id,
1487 p_batch_ref);
1488 END IF;
1489
1490 -- --
1491
1492
1493 hxt_interface_utilities.empty_cache;
1494 END LOOP retrieve_in_chuncks;
1495
1496 hxt_interface_utilities.perform_commit;
1497 -- Transfer or Validate the batches that were created
1498 --(if requested by user)
1499 process_bee_batches (p_status_in_bee => p_status_in_bee);
1500 hr_utility.set_location ('Leaving ' || l_proc, 100);
1501 EXCEPTION
1502 WHEN OTHERS
1503 THEN
1504 set_sqlerror_trx (g_bee_retrieval_process);
1505 hxc_generic_retrieval_utils.set_parent_statuses;
1506 hxc_generic_retrieval_pkg.update_transaction_status
1507 (p_process => g_bee_retrieval_process,
1508 p_status => 'ERRORS',
1509 p_exception_description => SUBSTR
1510 (SQLERRM,
1511 1,
1512 g_max_message_size
1513 ),
1514 p_rollback => FALSE
1515 );
1516
1517 IF (SQLERRM NOT LIKE '%HXC%')
1518 THEN
1519 fnd_message.raise_error;
1520 ELSE
1521 IF (p_no_otm = 'Y')
1522 THEN
1523 fnd_message.raise_error;
1524 END IF;
1525 END IF;
1526
1527 RETURN;
1528 END process_non_otlr_employees;
1529
1530 PROCEDURE transfer_to_hr_payroll (
1531 errbuf OUT NOCOPY VARCHAR2,
1532 retcode OUT NOCOPY NUMBER,
1533 p_bg_id IN NUMBER,
1534 p_session_date IN VARCHAR2,
1535 p_start_date IN VARCHAR2,
1536 p_end_date IN VARCHAR2,
1537 p_start_batch_id IN NUMBER DEFAULT NULL,
1538 p_end_batch_id IN NUMBER DEFAULT NULL,
1539 p_gre_id IN NUMBER DEFAULT NULL,
1540 p_organization_id IN NUMBER DEFAULT NULL,
1541 p_location_id IN NUMBER DEFAULT NULL,
1542 p_payroll_id IN NUMBER DEFAULT NULL,
1543 p_person_id IN NUMBER DEFAULT NULL,
1544 p_retrieval_transaction_code IN VARCHAR2,
1545 p_batch_selection IN VARCHAR2 DEFAULT NULL,
1546 p_is_old IN VARCHAR2 DEFAULT NULL,
1547 p_old_batch_ref IN VARCHAR2 DEFAULT NULL,
1548 p_new_batch_ref IN VARCHAR2 DEFAULT NULL,
1549 p_new_specified IN VARCHAR2 DEFAULT NULL,
1550 p_status_in_bee IN VARCHAR2,
1551 p_otlr_to_bee IN VARCHAR2,
1552 p_since_date IN VARCHAR2
1553 )
1554 AS
1555 l_where_clause hxt_interface_utilities.max_varchar;
1556 l_unique_params hxt_interface_utilities.max_varchar;
1557 l_batch_ref pay_batch_headers.batch_reference%TYPE;
1558 l_no_otm hxt_interface_utilities.flag_varchar := 'N';
1559 l_retrieval_options fnd_profile_option_values.profile_option_value%TYPE
1560 := fnd_profile.VALUE ('HXC_RETRIEVAL_OPTIONS');
1561 BEGIN
1562 -- Set session date
1563 hxc_generic_retrieval_pkg.g_ret_criteria.location_id := p_location_id;
1564 hxc_generic_retrieval_pkg.g_ret_criteria.payroll_id := p_payroll_id;
1565 hxc_generic_retrieval_pkg.g_ret_criteria.organization_id :=
1566 p_organization_id;
1567 hxc_generic_retrieval_pkg.g_ret_criteria.gre_id := p_gre_id;
1568 l_since_date := p_since_date;
1569 pay_db_pay_setup.set_session_date (SYSDATE);
1570 l_batch_ref := NVL (p_old_batch_ref, p_new_batch_ref);
1571 l_where_clause :=
1572 where_clause (p_bg_id,
1573 p_location_id,
1574 p_payroll_id,
1575 p_organization_id,
1576 p_person_id,
1577 p_gre_id
1578 );
1579 l_unique_params :=
1580 p_retrieval_transaction_code
1581 || ':'
1582 || NVL (p_new_batch_ref, 'OLD')
1583 || ':'
1584 || l_batch_ref;
1585
1586 IF ((l_retrieval_options <> 'BEE') OR (l_retrieval_options IS NULL))
1587 THEN
1588 process_otlr_employees
1589 (p_bg_id,
1590 fnd_date.canonical_to_date(p_session_date), -- Bug 6121705, new parameter; needs
1591 -- type conversion from VARCHAR2
1592 p_start_date, --fnd_date.canonical_to_date (p_start_date),
1593 p_end_date, --fnd_date.canonical_to_date (p_end_date),
1594 l_where_clause,
1595 p_retrieval_transaction_code,
1596 l_batch_ref,
1597 l_unique_params,
1598 p_no_otm => l_no_otm
1599 );
1600 ELSE
1601 l_no_otm := 'Y';
1602 END IF;
1603
1604 IF ((l_retrieval_options <> 'OTLR') OR (l_retrieval_options IS NULL))
1605 THEN
1606 process_non_otlr_employees
1607 (p_bg_id,
1608 fnd_date.canonical_to_date (p_start_date),
1609 fnd_date.canonical_to_date (p_end_date),
1610 fnd_date.canonical_to_date (p_session_date),
1611 l_where_clause,
1612 p_retrieval_transaction_code,
1613 l_batch_ref,
1614 l_unique_params,
1615 p_status_in_bee => p_status_in_bee,
1616 p_no_otm => l_no_otm
1617 );
1618 END IF;
1619 END transfer_to_hr_payroll;
1620
1621
1622 -- Bug 6121705
1623 -- Proc created to make adjustments in BEE for batch lines with source as Time Store.
1624 -- chk_retrieve function would have populated the details which are already transferred
1625 -- with a different rules evaluation preference and need adjustments. Now there has
1626 -- to be a second time retrieval and creation of batches, but with negative entries in
1627 -- BEE with batch source as Time Store.
1628
1629
1630 PROCEDURE make_adjustments_bee ( p_batch_ref IN VARCHAR2,
1631 p_bg_id IN NUMBER,
1632 p_session_date IN DATE)
1633 IS
1634
1635
1636 l_sqlcode NUMBER;
1637 l_sqlmsg VARCHAR2(2000);
1638
1639 l_att_cnt NUMBER;
1640
1641 tnull_old_detail_bld_blks hxc_generic_retrieval_pkg.t_building_blocks;
1642 tnull_old_detail_attributes hxc_generic_retrieval_pkg.t_time_attribute;
1643
1644 l_adj_batch_id pay_batch_headers.batch_reference%TYPE := NULL;
1645 l_retro_adj_batch_id pay_batch_headers.batch_reference%TYPE := NULL;
1646
1647
1648 -- Private procedure create_bld_blk_table
1649 -- populates the detail bld blks plsql table like gen. retrieval
1650 -- 1. Pull out the bb details from hxc_time_building_blocks table
1651 -- which has an entry in hxc_bee_pref_adj_lines table, with
1652 -- batch_source as Time Store.
1653 -- 2. Update hxc_bee_pref_adj_lines table with these values for
1654 -- the corresponding bb_ids and ovns.
1655 -- 3. Pull out these into a plsql table for the format prescribed in
1656 -- hxc_generic_retrieval_pkg, so that it can use the batch creation API.
1657
1658
1659 PROCEDURE create_bld_blk_table
1660 IS
1661
1662 CURSOR get_bb_details
1663 IS
1664 SELECT time_building_block_id,
1665 object_Version_number,
1666 type,
1667 DECODE(type,'MEASURE',measure,'RANGE',(stop_time-start_time)*24),
1668 start_time,
1669 stop_time,
1670 parent_building_block_id ,
1671 scope,
1672 resource_type,
1673 comment_text,
1674 unit_of_measure,
1675 'N',
1676 'N'
1677 FROM hxc_time_building_blocks
1678 WHERE (time_building_block_id,object_version_number)
1679 IN ( SELECT detail_bb_id,
1680 detail_bb_ovn
1681 FROM hxc_bee_pref_adj_lines
1682 WHERE batch_source = 'Time Store');
1683
1684 CURSOR get_blocks
1685 IS
1686 SELECT detail_bb_id,
1687 type,
1688 -1*hours, -- To create reverse entries, you need negative hours.
1689 TRUNC(NVL(start_time,date_earned)),
1690 TRUNC(NVL(stop_time,date_earned)),
1691 parent_bb_id ,
1692 scope,
1693 resource_id,
1694 resource_type,
1695 comment_text,
1696 uom,
1697 detail_bb_ovn,
1698 changed,
1699 deleted,
1700 timecard_id,
1701 timecard_ovn
1702 FROM hxc_bee_pref_adj_lines
1703 WHERE batch_source = 'Time Store'
1704 order by detail_bb_id
1705 ;
1706
1707 BEGIN --- create_bld_blk_table
1708 OPEN get_bb_details;
1709
1710 FETCH get_bb_details
1711 BULK COLLECT INTO t_bb_details;
1712
1713 CLOSE get_bb_details;
1714
1715 FOR i IN t_bb_details.FIRST..t_bb_details.LAST
1716 LOOP
1717 UPDATE hxc_bee_pref_adj_lines
1718 SET type = t_bb_details(i).type,
1719 scope = t_bb_details(i).scope,
1720 hours = t_bb_details(i).measure ,
1721 start_time = t_bb_details(i).start_time ,
1722 stop_time = t_bb_details(i).stop_time ,
1723 resource_type = t_bb_details(i).resource_type ,
1724 uom = t_bb_details(i).uom ,
1725 changed = t_bb_details(i).changed ,
1726 deleted = t_bb_details(i).deleted ,
1727 comment_text = t_bb_details(i).comment_text ,
1728 parent_bb_id = t_bb_details(i).parent_bb_id
1729 WHERE detail_bb_id = t_bb_details(i).bb_id
1730 AND detail_bb_ovn = t_bb_details(i).ovn;
1731 END LOOP;
1732
1733 OPEN get_blocks;
1734
1735 FETCH get_blocks BULK COLLECT INTO t_detail_blocks;
1736
1737 CLOSE get_blocks;
1738
1739 END create_bld_blk_table;
1740
1741
1742
1743 -- Private Procedure gather_attributes
1744 -- gathers the attribute information for the blocks into the plsql table
1745 -- again like gen.retrieval.
1746
1747 PROCEDURE gather_attributes
1748 is
1749
1750 CURSOR get_attributes
1751 IS
1752 SELECT hat.attribute_category,
1753 attribute1,
1754 attribute2,
1755 attribute3,
1756 attribute4,
1757 attribute5,
1758 attribute6,
1759 attribute7,
1760 attribute8,
1761 attribute9,
1762 attribute10,
1763 attribute11,
1764 attribute12,
1765 attribute13,
1766 attribute14,
1767 attribute15,
1768 attribute16,
1769 attribute17,
1770 attribute18,
1771 attribute19,
1772 attribute20,
1773 attribute21,
1774 attribute22,
1775 attribute23,
1776 attribute24,
1777 attribute25,
1778 attribute26,
1779 attribute27,
1780 attribute28,
1781 attribute29,
1782 attribute30,
1783 hau.time_building_block_id,
1784 hau.time_building_block_ovn,
1785 hat.bld_blk_info_type_id
1786 FROM hxc_time_attribute_usages hau,
1787 hxc_time_attributes hat
1788 WHERE hau.time_attribute_id = hat.time_attribute_id
1789 AND (hau.time_building_block_id,
1790 hau.time_building_block_ovn) IN ( SELECT detail_bb_id, detail_bb_ovn
1791 FROM hxc_bee_pref_adj_lines
1792 WHERE batch_source = 'Time Store')
1793 ORDER BY hau.time_building_block_id,
1794 hat.bld_blk_info_type_id;
1795
1796 BEGIN
1797
1798 OPEN get_attributes;
1799
1800 FETCH get_attributes BULK COLLECT INTO t_attr_info;
1801
1802 CLOSE get_attributes;
1803
1804 END gather_attributes ;
1805
1806
1807 -- Private Procedure create_attributes_table
1808 -- From the gathered raw list of attributes, creates the plsql table
1809 -- structure that gen. retrieval returns. Loops thru the attributes and
1810 -- matches the mappings to get the required table structure.
1811
1812
1813 PROCEDURE create_attributes_table
1814 IS
1815 l_att_cnt NUMBER := 0;
1816
1817 BEGIN
1818 -- LOOP thru all detail attribute records ---
1819 FOR i IN t_attr_info.FIRST..t_attr_info.LAST
1820 LOOP
1821 -- Loop thru all the field mappings ---
1822 -- You already have the mappings in this global pl/sql table for gen retrieval.
1823 -- Use it, no need to query again.
1824 FOR MAP IN hxc_generic_retrieval_pkg.g_field_mappings_table.FIRST..
1825 hxc_generic_retrieval_pkg.g_field_mappings_table.LAST
1826 LOOP
1827 IF (t_attr_info(i).bld_blk_info_type_id =
1828 hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).
1829 bld_blk_info_type_id)
1830 -- If there is a valid mapping for the bld blk info type id --
1831 THEN
1832 l_att_cnt := l_att_cnt + 1;
1833 -- copy the the context,category AND the field name into the attribute info table --
1834 t_dtl_attributes(l_att_cnt).bb_id := t_attr_info(i).bb_id;
1835 t_dtl_attributes(l_att_cnt).field_name := hxc_generic_retrieval_pkg.
1836 g_field_mappings_table(MAP).field_name;
1837 t_dtl_attributes(l_att_cnt).context := hxc_generic_retrieval_pkg.
1838 g_field_mappings_table(MAP).context;
1839 t_dtl_attributes(l_att_cnt).category := hxc_generic_retrieval_pkg.
1840 g_field_mappings_table(MAP).category;
1841 -- check which attribute this mapping belongs to AND copy down that attribute --
1842
1843 IF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).
1844 ATTRIBUTE = 'ATTRIBUTE_CATEGORY')
1845 THEN
1846 t_dtl_attributes(l_att_cnt).value :=
1847 hxc_deposit_wrapper_utilities.get_dupdff_name(t_attr_info(i).attribute_category);
1848 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE1')
1849 THEN
1850 t_dtl_attributes(l_att_cnt).value :=
1851 t_attr_info(i).attribute1;
1852 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE2')
1853 THEN
1854 t_dtl_attributes(l_att_cnt).value :=
1855 t_attr_info(i).attribute2;
1856 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE3')
1857 THEN
1858 t_dtl_attributes(l_att_cnt).value :=
1859 t_attr_info(i).attribute3;
1860 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE4')
1861 THEN
1862 t_dtl_attributes(l_att_cnt).value :=
1863 t_attr_info(i).attribute4;
1864 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE5')
1865 THEN
1866 t_dtl_attributes(l_att_cnt).value :=
1867 t_attr_info(i).attribute5;
1868 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE6')
1869 THEN
1870 t_dtl_attributes(l_att_cnt).value :=
1871 t_attr_info(i).attribute6;
1872 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE7')
1873 THEN
1874 t_dtl_attributes(l_att_cnt).value :=
1875 t_attr_info(i).attribute7;
1876 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE8')
1877 THEN
1878 t_dtl_attributes(l_att_cnt).value :=
1879 t_attr_info(i).attribute8;
1880 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE9')
1881 THEN
1882 t_dtl_attributes(l_att_cnt).value :=
1883 t_attr_info(i).attribute9;
1884 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE10')
1885 THEN
1886 t_dtl_attributes(l_att_cnt).value :=
1887 t_attr_info(i).attribute10;
1888 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE11')
1889 THEN
1890 t_dtl_attributes(l_att_cnt).value :=
1891 t_attr_info(i).attribute11;
1892 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE12')
1893 THEN
1894 t_dtl_attributes(l_att_cnt).value :=
1895 t_attr_info(i).attribute12;
1896 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE13')
1897 THEN
1898 t_dtl_attributes(l_att_cnt).value :=
1899 t_attr_info(i).attribute13;
1900 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE14')
1901 THEN
1902 t_dtl_attributes(l_att_cnt).value :=
1903 t_attr_info(i).attribute14;
1904 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE15')
1905 THEN
1906 t_dtl_attributes(l_att_cnt).value :=
1907 t_attr_info(i).attribute15;
1908 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE16')
1909 THEN
1910 t_dtl_attributes(l_att_cnt).value :=
1911 t_attr_info(i).attribute16;
1912 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE17')
1913 THEN
1914 t_dtl_attributes(l_att_cnt).value :=
1915 t_attr_info(i).attribute17;
1916 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE18')
1917 THEN
1918 t_dtl_attributes(l_att_cnt).value :=
1919 t_attr_info(i).attribute18;
1920 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE19')
1921 THEN
1922 t_dtl_attributes(l_att_cnt).value :=
1923 t_attr_info(i).attribute19;
1924 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE20')
1925 THEN
1926 t_dtl_attributes(l_att_cnt).value :=
1927 t_attr_info(i).attribute20;
1928 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE21')
1929 THEN
1930 t_dtl_attributes(l_att_cnt).value :=
1931 t_attr_info(i).attribute21;
1932 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE22')
1933 THEN
1934 t_dtl_attributes(l_att_cnt).value :=
1935 t_attr_info(i).attribute22;
1936 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE23')
1937 THEN
1938 t_dtl_attributes(l_att_cnt).value :=
1939 t_attr_info(i).attribute23;
1940 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE24')
1941 THEN
1942 t_dtl_attributes(l_att_cnt).value :=
1943 t_attr_info(i).attribute24;
1944 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE25')
1945 THEN
1946 t_dtl_attributes(l_att_cnt).value :=
1947 t_attr_info(i).attribute25;
1948 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE26')
1949 THEN
1950 t_dtl_attributes(l_att_cnt).value :=
1951 t_attr_info(i).attribute26;
1952 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE27')
1953 THEN
1954 t_dtl_attributes(l_att_cnt).value :=
1955 t_attr_info(i).attribute27;
1956 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE28')
1957 THEN
1958 t_dtl_attributes(l_att_cnt).value :=
1959 t_attr_info(i).attribute28;
1960 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE29')
1961 THEN
1962 t_dtl_attributes(l_att_cnt).value :=
1963 t_attr_info(i).attribute29;
1964 ELSIF (hxc_generic_retrieval_pkg.g_field_mappings_table(MAP).ATTRIBUTE = 'ATTRIBUTE30')
1965 THEN
1966 t_dtl_attributes(l_att_cnt).value :=
1967 t_attr_info(i).attribute30;
1968 END IF;
1969 ELSE
1970 NULL;
1971 END IF;
1972
1973 END LOOP;
1974 END LOOP;
1975
1976 END create_attributes_table;
1977
1978 BEGIN --- Make adjustments Main
1979
1980 -- Create your bld blk table structure
1981 -- Gather your attribute info
1982 -- create the attribute table structure with the mapping info.
1983 -- Call add_lines_to bee_batch function with these values.
1984 -- The adjustment batch that is created will have a batch reference
1985 -- prefixed with 'adjdup' to identify these in future.
1986
1987 create_bld_blk_table;
1988
1989 gather_attributes;
1990
1991 create_attributes_table;
1992
1993 add_lines_to_bee_batch
1994 ('adjdup'||p_batch_ref,
1995 p_bg_id,
1996 p_session_date,
1997 t_detail_blocks,
1998 t_dtl_attributes,
1999 tnull_old_detail_bld_blks,
2000 tnull_old_detail_attributes,
2001 l_adj_batch_id,
2002 l_retro_adj_batch_id
2003 );
2004
2005 -- you dont need the duplicate adjustment lines of this batch source, so delete them.
2006 DELETE FROM hxc_bee_pref_adj_lines
2007 WHERE batch_source = 'Time Store';
2008
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011 l_sqlmsg := SUBSTR(SQLERRM,1,1500)||SQLCODE;
2012 hr_utility.trace('Sql error in make_adjustments :'||l_sqlmsg);
2013 RAISE;
2014
2015 END;
2016
2017
2018
2019 -- Bug 6121705
2020 -- This procedure is used to create reversal batches with Batch source as OTM
2021 -- for the details in hxc_bee_pref_adj_lines table with OTM as batch source.
2022 -- hxc_generic_retrieval_utils.chk_retrieve would have populated these
2023 -- records for which there is a history of a different preference for rules
2024 -- evaluation.
2025
2026
2027 PROCEDURE make_adjustments_otm( p_bg_id IN hr_all_organization_units.business_group_id%TYPE,
2028 p_batch_ref IN VARCHAR2)
2029 IS
2030
2031 l_new_batch NUMBER;
2032 l_batch_ovn NUMBER;
2033 l_sqlmsg VARCHAR2(2000);
2034 l_rec_count NUMBER;
2035
2036
2037 -- Private procedure delete_non_transferred_hours
2038 -- Deletes hours that are transferred earlier to HXT
2039 -- and have not been transferred to BEE.
2040
2041 PROCEDURE delete_non_transferred_hours
2042 IS
2043 BEGIN
2044 DELETE FROM hxt_det_hours_worked_F
2045 WHERE ( date_worked,
2046 assignment_id )
2047 in ( SELECT date_earned,
2048 assignment_id
2049 FROM hxc_bee_pref_adj_lines,
2050 per_all_assignments_f paf,
2051 per_assignment_status_types pas
2052 WHERE resource_id = person_id
2053 AND batch_source = 'OTM'
2054 AND paf.effective_end_date = hr_general.end_of_time
2055 AND paf.effective_start_date <= date_earned
2056 AND paf.primary_flag = 'Y'
2057 AND paf.assignment_status_type_id =
2058 pas.assignment_status_type_id
2059 AND pas.per_system_status = 'ACTIVE_ASSIGN')
2060 AND pay_status <> 'C'
2061 AND effective_end_date = hr_general.end_of_time;
2062 EXCEPTION
2063 WHEN NO_DATA_FOUND THEN
2064 null;
2065 END;
2066
2067
2068 -- Private procedure create_batch_line
2069 -- creates the batch lines for the adjustment batch that is created.
2070
2071 PROCEDURE create_batch_line(p_batch_id NUMBER)
2072 IS
2073 CURSOR get_batch_rec
2074 IS
2075 SELECT *
2076 FROM hxt_batch_values_v
2077 WHERE (date_worked ,assignment_id) in
2078 ( SELECT date_earned,
2079 paf.assignment_id
2080 FROM hxc_bee_pref_adj_lines hoa,
2081 per_all_assignments_f paf
2082 WHERE hoa.resource_id = paf.person_id
2083 AND hoa.date_earned BETWEEN paf.effective_start_date
2084 AND paf.effective_end_date
2085 AND paf.primary_flag = 'Y');
2086 p_batch_rec hxt_batch_values_v%ROWTYPE;
2087 l_sum_retcode NUMBER := 0;
2088 l_batch_sequence NUMBER;
2089
2090 BEGIN
2091 l_batch_sequence := pay_paywsqee_pkg.next_batch_sequence(p_batch_id);
2092 OPEN get_batch_rec;
2093 LOOP
2094 FETCH get_batch_rec
2095 INTO p_batch_rec;
2096 EXIT WHEN get_batch_rec%NOTFOUND;
2097
2098 p_batch_rec.batch_id := p_batch_id;
2099 p_batch_rec.hours := -1*p_batch_rec.hours;
2100
2101 IF (l_sum_retcode = 0)
2102 THEN
2103 hxt_batch_process.dtl_to_bee(p_batch_rec,l_sum_retcode,l_batch_sequence);
2104 l_batch_sequence := l_batch_sequence + 1;
2105 END IF;
2106 END LOOP;
2107 CLOSE get_batch_rec;
2108
2109 END;
2110
2111 BEGIN ---- make_adjustments_otm
2112
2113 -- In case the timecard is still with HXT, no need to create reversal batches.
2114 -- But see that they are deleted from the system, so that they can never be
2115 -- transferred. Delete the non transferred hours first.
2116 -- Create a batch header, and then create the batch lines taking values from
2117 -- HXT_BATCH_VALUES_V. There could have been a timecard edit in HXT, and the changed
2118 -- hours could have gone to BEE. The reversal batch lines has to reverse these, rather
2119 -- than the hours you pulled out from HXC. HXT_BATCH_VALUES_V is a view on hxt detail hours
2120 -- and pay batch lines, so what you get from there would be the transferred values.
2121 -- So after you pull out the batch records, turn around the hours with a negative sign
2122 -- and create the lines.
2123 delete_non_transferred_hours;
2124
2125 -- Check if there is anything left there. If no TC was transferred, no need of the
2126 -- adj dup batch.
2127 SELECT COUNT(*)
2128 INTO l_rec_count
2129 FROM hxc_bee_pref_adj_lines
2130 WHERE batch_source = 'OTM'
2131 AND rownum < 2;
2132
2133 IF l_rec_count > 0
2134 THEN
2135 l_batch_ovn := 1;
2136 PAY_BATCH_ELEMENT_ENTRY_API.create_batch_header
2137 (p_session_date => sysdate
2138 ,p_batch_name => 'adjdup'||p_batch_ref
2139 ,p_batch_status => 'U'
2140 ,p_business_group_id => p_bg_id
2141 ,p_action_if_exists => 'I'
2142 ,p_batch_reference => 'adjdup'||p_batch_ref
2143 ,p_batch_source => 'OTM'
2144 ,p_purge_after_transfer => 'N'
2145 ,p_reject_if_future_changes => 'N'
2146 ,p_batch_id => l_new_batch
2147 ,p_object_version_number => l_batch_ovn
2148 );
2149
2150 create_batch_line(p_batch_id => l_new_batch);
2151
2152
2153 DELETE FROM hxc_bee_pref_adj_lines
2154 WHERE batch_source = 'OTM';
2155 END IF;
2156
2157
2158 EXCEPTION
2159 WHEN NO_DATA_FOUND THEN
2160 NULL;
2161 WHEN others THEN
2162 l_sqlmsg := SUBSTR(SQLERRM,1,1500)||SQLCODE;
2163 hr_utility.trace('Sql error in make_adjustments :'||l_sqlmsg);
2164 RAISE;
2165 END;
2166
2167
2168 END pay_hr_otc_retrieval_interface;