[Home] [Help]
PACKAGE BODY: APPS.HXC_EXT_TIMECARD
Source
1 Package Body hxc_ext_timecard as
2 /* $Header: hxcxtime.pkb 120.3 2005/10/05 16:52 jdupont noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Public Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 /*
9 g_debug boolean := hr_utility.debug_enabled;
10 CURSOR csr_get_po_tc ( p_person_id NUMBER,
11 p_start_date DATE,
12 p_end_date DATE,
13 p_status VARCHAR2 ) IS
14 SELECT tbb_time.start_time
15 , tbb_time.stop_time
16 , tbb_time.comment_text comments
17 , DECODE( tbb_time.date_to, hr_general.end_of_time, 'N', 'Y' ) deleted
18 , tbb_time.time_building_block_id
19 , tbb_time.object_version_number
20 , tbb_summary.approval_status
21 FROM
22 hxc_time_building_blocks tbb_time
23 , hxc_timecard_summary tbb_summary
24 WHERE
25 tbb_time.resource_id = p_person_id AND
26 tbb_time.scope = 'TIMECARD' AND
27 tbb_time.object_version_number = ( select MAX( tbb.object_version_number )
28 from hxc_time_building_Blocks tbb
29 where tbb.time_building_block_id = tbb_time.time_building_block_id )
30 AND
31 p_start_date <= tbb_time.stop_time AND
32 p_end_date >= tbb_time.start_time
33 AND
34 tbb_summary.timecard_id = tbb_time.time_building_block_id AND
35 tbb_summary.timecard_ovn = tbb_time.object_version_number AND
36 tbb_summary.approval_status = p_status
37 ORDER BY
38 tbb_time.start_time;
39
40 CURSOR csr_get_tc ( p_person_id NUMBER ) IS
41 SELECT
42 tbb_time.start_time
43 , tbb_time.stop_time
44 , tbb_time.comment_text comments
45 , DECODE( tbb_time.date_to, hr_general.end_of_time, 'N', 'Y' ) deleted
46 , tbb_time.time_building_block_id
47 , tbb_time.object_version_number
48 , tbb_summary.approval_status
49 FROM
50 hxc_time_building_blocks tbb_time
51 , hxc_timecard_summary tbb_summary
52 WHERE
53 tbb_time.resource_id = p_person_id AND
54 tbb_time.scope = 'TIMECARD' AND
55 tbb_time.object_version_number = ( select MAX( tbb.object_version_number )
56 from hxc_time_building_Blocks tbb
57 where tbb.time_building_block_id = tbb_time.time_building_block_id )
58 AND
59 tbb_summary.timecard_id = tbb_time.time_building_block_id AND
60 tbb_summary.timecard_ovn = tbb_time.object_version_number
61 ORDER BY
62 tbb_time.start_time;
63
64 CURSOR csr_get_day ( p_tbb_id NUMBER, p_tbb_ovn NUMBER ) IS
65 SELECT /*+ ORDERED /
66 tbb_day.time_building_block_id tbb_day_id
67 , tbb_day.start_time day_start_time
68 , tbb_day.comment_text comments
69 , tbb_detail.time_building_block_id
70 , tbb_detail.object_version_number
71 , tbb_detail.measure
72 , tbb_detail.start_time
73 , tbb_detail.stop_time
74 , ta.attribute_category
75 , ta.attribute1
76 , ta.attribute2
77 , ta.attribute3
78 , ta.attribute4
79 , ta.attribute5
80 , ta.attribute6
81 , ta.attribute7
82 FROM
83 hxc_time_building_blocks tbb_day
84 , hxc_time_building_blocks tbb_detail
85 , hxc_time_attribute_usages tau
86 , hxc_time_attributes ta
87 WHERE
88 tbb_day.parent_building_block_id = p_tbb_id AND
89 tbb_day.parent_building_block_ovn = p_tbb_ovn AND
90 tbb_day.object_version_number = ( select MAX ( tbb.object_version_number )
91 from hxc_time_building_blocks tbb
92 where tbb.time_building_block_id = tbb_day.time_building_block_id )
93 AND
94 tbb_detail.parent_building_block_id = tbb_day.time_building_block_id AND
95 tbb_detail.object_version_number = ( select MAX ( tbb1.object_version_number )
96 from hxc_time_building_blocks tbb1
97 where tbb1.time_building_block_id
98 = tbb_detail.time_building_block_id )
99 AND
100 tau.time_building_block_id = tbb_detail.time_building_block_id AND
101 tau.time_building_block_ovn = tbb_detail.object_version_number
102 AND
103 ta.time_attribute_id = tau.time_attribute_id AND
104 ta.attribute_category <> 'SECURITY'
105 ORDER BY
106 tbb_day.start_time
107 , tbb_detail.time_building_block_id;
108
109 -- Inclusion Variables
110
111 l_project_id VARCHAR2(150);
112 l_task_id VARCHAR2(150);
113 l_exp_typ_id VARCHAR2(150);
114 l_element_type_id VARCHAR2(150);
115 l_po_num VARCHAR2(150);
116
117 */
118
119 PROCEDURE clear_summary_globals IS
120
121 l_proc VARCHAR2(72) ;
122
123 BEGIN
124 /*
125 if g_debug then
126 l_proc := g_package||'summary_globals';
127 hr_utility.set_location('Entering '||l_proc, 10);
128 end if;
129
130 hxc_ext_timecard.OTL_TC_START_DATE := NULL;
131 hxc_ext_timecard.OTL_TC_END_DATE := NULL;
132 hxc_ext_timecard.OTL_TC_STATUS := NULL;
133 hxc_ext_timecard.OTL_TC_COMMENTS := NULL;
134 hxc_ext_timecard.OTL_TC_DELTED := NULL;
135
136 if g_debug then
137 hr_utility.set_location('Leaving '||l_proc, 20);
138 end if;
139 */ null;
140 END clear_summary_globals;
141 /*
142 PROCEDURE populate_summary ( p_summary_rec csr_get_po_tc%ROWTYPE ) IS
143
144 l_proc VARCHAR2(72) ;
145
146 BEGIN
147 /*
148 if g_debug then
149 l_proc := g_package||'populate_summary';
150 hr_utility.set_location('Entering '||l_proc, 10);
151 end if;
152
153 hxc_ext_timecard.OTL_TC_START_DATE := p_summary_rec.start_time;
154 hxc_ext_timecard.OTL_TC_END_DATE := p_summary_rec.stop_time;
155 hxc_ext_timecard.OTL_TC_STATUS := p_summary_rec.approval_status;
156 hxc_ext_timecard.OTL_TC_COMMENTS := p_summary_rec.comments;
157 hxc_ext_timecard.OTL_TC_DELTED := p_summary_rec.deleted;
158
159 if g_debug then
160 hr_utility.set_location('Leaving '||l_proc, 20);
161 end if;
162
163 END populate_summary;
164 */
165 PROCEDURE clear_detail_globals IS
166
167 l_proc VARCHAR2(72) ;
168
169 BEGIN
170 /*
171 if g_debug then
172 l_proc := g_package||'clear_Details';
173 hr_utility.set_location('Entering '||l_proc, 10);
174 end if;
175
176 l_project_id := NULL;
177 l_task_id := NULL;
178 l_exp_typ_id := NULL;
179 l_element_type_id := NULL;
180 l_po_num := NULL;
181
182 hxc_ext_timecard.OTL_DAY := NULL;
183 hxc_ext_timecard.OTL_DAY_COMMENTS := NULL;
184
185 hxc_ext_timecard.OTL_MEASURE := NULL;
186 hxc_ext_timecard.OTL_DAY_START := NULL;
187 hxc_ext_timecard.OTL_DAY_STOP := NULL;
188 hxc_ext_timecard.OTL_PA_SYS_LINK_FUNCN := NULL;
189 hxc_ext_timecard.OTL_PA_BILLABLE_FLAG := NULL;
190 hxc_ext_timecard.OTL_PA_TASK := NULL;
191 hxc_ext_timecard.OTL_PA_PROJECT := NULL;
192 hxc_ext_timecard.OTL_PA_EXPENDITURE_TYPE := NULL;
193 hxc_ext_timecard.OTL_PA_EXPENDITURE_COMMENT:= NULL;
194 hxc_ext_timecard.OTL_PAY_ELEMENT_NAME := NULL;
195 hxc_ext_timecard.OTL_PAY_COST_CENTRE := NULL;
196 hxc_ext_timecard.OTL_PO_NUMBER := NULL;
197 hxc_ext_timecard.OTL_PO_LINE_ID := NULL;
198 hxc_ext_timecard.OTL_PO_PRICE_TYPE := NULL;
199 hxc_ext_timecard.OTL_ALIAS_ELEMENTS_EXP_SLF := NULL;
200 hxc_ext_timecard.OTL_ALIAS_EXPENDITURE_ELEMENTS := NULL;
201 hxc_ext_timecard.OTL_ALIAS_EXPENDITURE_TYPES := NULL;
202 hxc_ext_timecard.OTL_ALIAS_LOCATIONS := NULL;
206 hxc_ext_timecard.OTL_ALIAS_RATE_TYPE_EXP_SLF := NULL;
203 hxc_ext_timecard.OTL_ALIAS_PAYROLL_ELEMENTS := NULL;
204 hxc_ext_timecard.OTL_ALIAS_PROJECTS := NULL;
205 hxc_ext_timecard.OTL_ALIAS_TASKS := NULL;
207
208 if g_debug then
209 hr_utility.set_location('Leaving '||l_proc, 20);
210 end if;
211 */ null;
212 END clear_detail_globals;
213
214 /*
215 PROCEDURE populate_details ( p_detail_rec csr_get_day%ROWTYPE ) IS
216
217 CURSOR csr_get_po_line ( p_line_id VARCHAR2 ) IS
218 SELECT line_num
219 FROM po_lines_all
220 WHERE po_line_id = TO_NUMBER(p_line_id);
221
222
223 CURSOR csr_get_po_price_type ( p_price_type VARCHAR2 ) IS
224 SELECT meaning
225 FROM fnd_lookups
226 WHERE lookup_type = 'PRICE DIFFERENTIALS'
227 AND lookup_code = p_price_type;
228
229 CURSOR csr_get_pa_task ( p_task_id VARCHAR2 ) IS
230 SELECT task_name
231 FROM pa_online_tasks_v
232 WHERE task_id = TO_NUMBER( p_task_id );
233
234 CURSOR csr_get_pa_project ( p_project_id VARCHAR2 ) IS
235 SELECT project_name
236 FROM pa_online_projects_v
237 WHERE project_id = TO_NUMBER( p_project_id );
238
239 CURSOR csr_get_element_name ( p_element_type_id VARCHAR2 ) IS
240 SELECT element_name
241 from pay_element_types_f
242 where element_type_id = TO_NUMBER(p_element_type_id);
243
244 l_att_cat hxc_time_attributes.attribute_category%TYPE;
245
246 l_proc VARCHAR2(72) ;
247
248 BEGIN
249 /*
250 if g_debug then
251 l_proc := g_package||'populate_details';
252 hr_utility.set_location('Entering '||l_proc, 10);
253 end if;
254
255 hxc_ext_timecard.OTL_DAY := p_detail_rec.day_start_time;
256 hxc_ext_timecard.OTL_DAY_COMMENTS := p_detail_rec.comments;
257
258 hxc_ext_timecard.OTL_MEASURE := p_detail_rec.measure;
259 hxc_ext_timecard.OTL_DAY_START := p_detail_rec.start_time;
260 hxc_ext_timecard.OTL_DAY_STOP := p_detail_rec.stop_time;
261
262 IF ( p_detail_rec.attribute_category = 'PROJECTS' )
263 THEN
264
265 if g_debug then
266 hr_utility.set_location('Processing '||l_proc, 20);
267 end if;
268
269 hxc_ext_timecard.OTL_PA_SYS_LINK_FUNCN := p_detail_rec.attribute5;
270 hxc_ext_timecard.OTL_PA_BILLABLE_FLAG := p_detail_rec.attribute7;
271
272 OPEN csr_get_pa_task ( p_detail_rec.attribute2 );
273 FETCH csr_get_pa_task INTO hxc_ext_timecard.OTL_PA_TASK;
274 CLOSE csr_get_pa_task;
275
276 if g_debug then
277 hr_utility.set_location('Processing '||l_proc, 30);
278 end if;
279
280 l_task_id := p_detail_rec.attribute2;
281
282 OPEN csr_get_pa_project ( p_detail_rec.attribute1 );
283 FETCH csr_get_pa_project INTO hxc_ext_timecard.OTL_PA_PROJECT;
284 CLOSE csr_get_pa_project;
285
286 if g_debug then
287 hr_utility.set_location('Processing '||l_proc, 40);
288 end if;
289
290 l_project_id := p_detail_rec.attribute1;
291
292 l_exp_typ_id := p_detail_rec.attribute3;
293
294 hxc_ext_timecard.OTL_PA_EXPENDITURE_TYPE := p_detail_rec.attribute3;
295 hxc_ext_timecard.OTL_PA_EXPENDITURE_COMMENT:= p_detail_rec.attribute4;
296
297 if g_debug then
298 hr_utility.set_location('Processing '||l_proc, 50);
299 end if;
300
301 ELSIF ( p_detail_rec.attribute_category like 'ELEMENT%' )
302 THEN
303
304 if g_debug then
305 hr_utility.set_location('Processing '||l_proc, 60);
306 end if;
307
308 l_att_cat := p_detail_rec.attribute_category;
309 l_element_type_id := SUBSTR( l_att_cat, (INSTR(l_att_cat,'-',1,1)+2));
310
311 if g_debug then
312 hr_utility.set_location('Processing '||l_proc, 70);
313 end if;
314
315 OPEN csr_get_element_name ( l_element_type_id );
316 FETCH csr_get_element_name INTO hxc_ext_timecard.OTL_PAY_ELEMENT_NAME;
317 CLOSE csr_get_element_name;
318
319 if g_debug then
320 hr_utility.set_location('Processing '||l_proc, 80);
321 end if;
322
323 ELSIF ( p_detail_rec.attribute_category like 'COST%' )
324 THEN
325
326 hxc_ext_timecard.OTL_PAY_COST_CENTRE := 'Not Currently Supported';
327
328 ELSIF ( p_detail_rec.attribute_category = 'PURCHASING' )
329 THEN
330
331 if g_debug then
332 hr_utility.set_location('Processing '||l_proc, 90);
333 end if;
334
335 OPEN csr_get_po_line ( p_detail_rec.attribute2 );
336 FETCH csr_get_po_line INTO hxc_ext_timecard.OTL_PO_LINE_ID;
337 CLOSE csr_get_po_line;
338
339 if g_debug then
340 hr_utility.set_location('Processing '||l_proc, 100);
341 end if;
342
343 OPEN csr_get_po_price_type ( p_detail_rec.attribute3 );
344 FETCH csr_get_po_price_type INTO hxc_ext_timecard.OTL_PO_PRICE_TYPE;
345 CLOSE csr_get_po_price_type;
346
347 hxc_ext_timecard.OTL_PO_NUMBER := p_detail_rec.attribute1;
348
349 l_po_num := p_detail_rec.attribute1;
350
351 if g_debug then
352 hr_utility.set_location('Leaving '||l_proc, 110);
353 end if;
354
355 END IF;
356
357 END populate_details;
358 */
359
360 PROCEDURE populate_alias_values (
361 p_detail_id NUMBER
362 , p_detail_ovn NUMBER
363 , p_resource_id NUMBER ) IS
364
365 l_messages HXC_MESSAGE_TABLE_TYPE;
366
367 l_alias_att HXC_ATTRIBUTE_TABLE_TYPE;
368
369 l_index BINARY_INTEGER;
370
371 l_proc VARCHAR2(72) ;
372
373 BEGIN
374 /*
375
376 if g_debug then
377 l_proc := g_package||'populate_alias_values';
378 hr_utility.set_location('Entering '||l_proc, 10);
379 end if;
380
384 , p_resource_id => p_resource_id
381 hxc_alias_utility.get_translated_detail (
382 p_detail_bb_id => p_detail_id
383 , p_detail_bb_ovn => p_detail_ovn
385 , p_attributes => l_alias_att
386 , p_messages => l_messages );
387
388 if g_debug then
389 hr_utility.set_location('Processing '||l_proc, 20);
390 end if;
391
392 l_index := l_alias_att.FIRST;
393
394 WHILE l_index IS NOT NULL
395 LOOP
396
397 if g_debug then
398 hr_utility.set_location('Processing '||l_proc, 30);
399 end if;
400
401 IF ( l_alias_att(l_index).attribute29 = 'ELEMENTS_EXPENDITURE_SLF' )
402 THEN
403
404 if g_debug then
405 hr_utility.set_location('Processing '||l_proc, 40);
406 end if;
407
408 hxc_ext_timecard.OTL_ALIAS_ELEMENTS_EXP_SLF
409 := l_alias_att(l_index).attribute30;
410
411 ELSIF ( l_alias_att(l_index).attribute29 = 'EXPENDITURE_ELEMENTS' )
412 THEN
413
414 if g_debug then
415 hr_utility.set_location('Processing '||l_proc, 50);
416 end if;
417
418 hxc_ext_timecard.OTL_ALIAS_EXPENDITURE_ELEMENTS
419 := l_alias_att(l_index).attribute30;
420
421 ELSIF ( l_alias_att(l_index).attribute29 = 'EXPENDITURE_TYPES' )
422 THEN
423
424 if g_debug then
425 hr_utility.set_location('Processing '||l_proc, 60);
426 end if;
427
428 hxc_ext_timecard.OTL_ALIAS_EXPENDITURE_TYPES
429 := l_alias_att(l_index).attribute30;
430
431 ELSIF ( l_alias_att(l_index).attribute29 = 'LOCATIONS' )
432 THEN
433
434 if g_debug then
435 hr_utility.set_location('Processing '||l_proc, 70);
436 end if;
437
438 hxc_ext_timecard.OTL_ALIAS_LOCATIONS
439 := l_alias_att(l_index).attribute30;
440
441 ELSIF ( l_alias_att(l_index).attribute29 = 'PAYROLL_ELEMENTS' )
442 THEN
443
444 if g_debug then
445 hr_utility.set_location('Processing '||l_proc, 80);
446 end if;
447
448 hxc_ext_timecard.OTL_ALIAS_PAYROLL_ELEMENTS
449 := l_alias_att(l_index).attribute30;
450
451 ELSIF ( l_alias_att(l_index).attribute29 = 'PROJECTS' )
452 THEN
453
454 if g_debug then
455 hr_utility.set_location('Processing '||l_proc, 90);
456 end if;
457
458 hxc_ext_timecard.OTL_ALIAS_PROJECTS
459 := l_alias_att(l_index).attribute30;
460
461 ELSIF ( l_alias_att(l_index).attribute29 = 'TASKS' )
462 THEN
463
464 if g_debug then
465 hr_utility.set_location('Processing '||l_proc, 100);
466 end if;
467
468 hxc_ext_timecard.OTL_ALIAS_TASKS
469 := l_alias_att(l_index).attribute30;
470
471 ELSIF ( l_alias_att(l_index).attribute29 = 'RATE_TYPE_EXPENDITURE_SLF' )
472 THEN
473
474 if g_debug then
475 hr_utility.set_location('Processing '||l_proc, 110);
476 end if;
477
478 hxc_ext_timecard.OTL_ALIAS_RATE_TYPE_EXP_SLF
479 := l_alias_att(l_index).attribute30;
480
481 ENd IF;
482
483 l_index := l_alias_att.NEXT( l_index );
484
485 END LOOP;
486
487 if g_debug then
488 hr_utility.set_location('Leaving '||l_proc, 120);
489 end if;
490 */
491
492 null;
493 END populate_alias_values;
494
495
496 PROCEDURE clear_all_globals IS
497
498 l_proc VARCHAR2(72) ;
499
500 BEGIN
501 /*
502 if g_debug then
503 l_proc := g_package||'clear_all_globals';
504 hr_utility.set_location('Entering '||l_proc, 10);
505 end if;
506
507 clear_summary_globals;
508 clear_detail_globals;
509
510 if g_debug then
511 hr_utility.set_location('Leaving '||l_proc, 20);
512 end if;
513 */
514
515 null;
516 END clear_all_globals;
517
518
519 PROCEDURE process_summary (
520 p_person_id in number,
521 p_ext_rslt_id in number,
522 p_ext_file_id in number,
523 p_ext_crit_prfl_id in number,
524 p_data_typ_cd in varchar2,
525 p_ext_typ_cd in varchar2,
526 p_effective_date in date ) IS
527
528 l_start_date DATE;
529 l_end_date DATE;
530 l_tc_status VARCHAR2(30);
531
532 --l_tc_summary csr_get_po_tc%ROWTYPE;
533
534 l_include VARCHAR2(1);
535
536 l_proc VARCHAR2(72) ;
537
538 BEGIN
539 /*
540 g_debug :=hr_utility.debug_enabled;
541 if g_debug then
542 l_proc := g_package||'process_summary';
543 hr_utility.trace('p person id is '||to_char(p_person_id));
544 hr_utility.trace('p bg id is '||to_char(hxc_ext_timecard.g_params.p_business_group_id));
545 hr_utility.set_location('Entering '||l_proc, 10);
546 end if;
547
548 clear_all_globals;
549
550 IF ( hxc_ext_timecard.g_params.retrieval_process = 'Purchasing Retrieval Process' AND
551 p_person_id = NVL( hxc_ext_timecard.g_params.p_person_id, p_person_id ) )
552 THEN
553
554 if g_debug then
555 hr_utility.set_location('Processing '||l_proc, 20);
556 end if;
557
558 l_tc_status := hxc_ext_timecard.g_params.status;
559 l_start_date := TRUNC(hxc_ext_timecard.g_params.start_date);
560 l_end_date := TRUNC(hxc_ext_timecard.g_params.end_date);
561
562 if g_debug then
563 hr_utility.trace('start date is '||to_char(l_start_date,'hh24:mi:ss dd/mm/yyyy'));
564 hr_utility.trace('end date is '||to_char(l_end_date,'hh24:mi:ss dd/mm/yyyy'));
565 hr_utility.trace('status is '||l_tc_status);
566 end if;
567
568 OPEN csr_get_po_tc ( p_person_id,
569 l_start_date,
570 l_end_date,
574
571 l_tc_status );
572
573 FETCH csr_get_po_tc INTO l_tc_summary;
575 if g_debug then
576 hr_utility.set_location('Processing '||l_proc, 30);
577 end if;
578
579 WHILE csr_get_po_tc%FOUND
580 LOOP
581
582 if g_debug then
583 hr_utility.set_location('Processing '||l_proc, 40);
584 end if;
585
586 ben_ext_evaluate_inclusion.evaluate_timecard_incl
587 (p_otl_lvl => 'SUMMARY'
588 ,p_tc_status => l_tc_summary.approval_status
589 ,p_tc_deleted => l_tc_summary.deleted
590 ,p_include => l_include );
591
592
593 if g_debug then
594 hr_utility.set_location('Processing '||l_proc, 50);
595 end if;
596
597 IF ( l_include = 'Y' )
598 THEN
599
600 if g_debug then
601 hr_utility.set_location('Processing '||l_proc, 60);
602 end if;
603
604 populate_summary ( l_tc_summary );
605
606 -- IF ( ben_extract.g_otl_summ_lvl = 'Y' )
607 --THEN
608
609 -- if g_debug then
610 -- hr_utility.set_location('Processing '||l_proc, 70);
611 -- end if;
612
613 -- do not want to o/p until we know there
614 -- are details for this timecard
615
616 -- null;
617
618 -- END IF;
619
620 --IF ( ben_extract.g_otl_detl_lvl = 'Y' )
621 --THEN
622
623 if g_debug then
624 hr_utility.set_location('Processing '||l_proc, 80);
625 end if;
626
627 process_detail ( p_tbb_id => l_tc_summary.time_building_block_id
628 , p_tbb_ovn => l_tc_summary.object_version_number
629 , p_person_id => p_person_id
630 , p_ext_rslt_id => p_ext_rslt_id
631 , p_ext_file_id => p_ext_file_id
632 , p_ext_crit_prfl_id=> p_ext_crit_prfl_id
633 , p_data_typ_cd => p_data_typ_cd
634 , p_ext_typ_cd => p_ext_typ_cd
635 , p_effective_date => p_effective_date );
636
637 if g_debug then
638 hr_utility.set_location('Processing '||l_proc, 90);
639 end if;
640
641 --END IF;
642
643 if g_debug then
644 hr_utility.set_location('Processing '||l_proc, 100);
645 end if;
646
647 END IF; -- ben_ext_evaluate_inclusion.otl_inclusion_function
648
649 if g_debug then
650 hr_utility.set_location('Processing '||l_proc, 110);
651 end if;
652
653 clear_all_globals;
654
655 FETCH csr_get_po_tc INTO l_tc_summary;
656
657 END LOOP;
658
659 if g_debug then
660 hr_utility.set_location('Processing '||l_proc, 120);
661 end if;
662
663 CLOSE csr_get_po_tc;
664
665 END IF; -- ( hxc_ext_timecard.g_params.retrieval_process = 'Purchasing Retrieval Process' )
666
667 if g_debug then
668 hr_utility.set_location('Leaving '||l_proc, 130);
669 end if;
670
671
672 */
673
674 null;
675
676
677 END process_summary;
678
679 PROCEDURE process_detail (
680 p_tbb_id in number,
681 p_tbb_ovn in number,
682 p_person_id in number,
683 p_ext_rslt_id in number,
684 p_ext_file_id in number,
685 p_ext_crit_prfl_id in number,
686 p_data_typ_cd in varchar2,
687 p_ext_typ_cd in varchar2,
688 p_effective_date in date ) IS
689
690
691 --l_day_rec csr_get_day%ROWTYPE;
692
693 l_summary_written BOOLEAN := FALSE;
694 l_skip_PO BOOLEAN := FALSE;
695 l_already_called_PO BOOLEAN := FALSE;
696
697 l_old_det_id hxc_time_building_blocks.time_building_block_id%TYPE := -1;
698
699 l_include VARCHAR2(1) := 'N';
700
701 l_proc VARCHAR2(72) ;
702
703 BEGIN
704 /*
705 g_debug :=hr_utility.debug_enabled;
706 if g_debug then
707 l_proc := g_package||'process_detail';
708 hr_utility.set_location('Entering '||l_proc, 10);
709 end if;
710
711 OPEN csr_get_day ( p_tbb_id, p_tbb_ovn );
712
713 FETCH csr_get_day INTO l_day_rec;
714
715 WHILE csr_get_day%FOUND
716 LOOP
717
718 if g_debug then
719 hr_utility.set_location('Processing '||l_proc, 20);
720 end if;
721
722 IF ( l_old_det_id <> l_day_rec.time_building_block_id )
723 THEN
724
725 if g_debug then
726 hr_utility.trace('New Detail is '||to_char(l_day_rec.time_building_block_id));
727 hr_utility.set_location('Processing '||l_proc, 30);
728 end if;
729
730 -- new detail so all globals for OLD detail populated
731 -- decide if we are going to write this information
732
733 IF ( ( NOT l_skip_PO ) AND ( l_old_det_id <> -1 ) )
734 THEN
735
736 if g_debug then
737 hr_utility.set_location('Processing '||l_proc, 40);
738 end if;
739
740 --IF NOT l_summary_written AND ben_extract.g_otl_summ_lvl = 'Y'
741 --THEN
742 -- if g_debug then
743 -- hr_utility.set_location('Processing '||l_proc, 50);
744 -- end if;
745
746 ben_ext_fmt.process_ext_recs(
747 p_ext_rslt_id => p_ext_rslt_id,
748 p_ext_file_id => p_ext_file_id,
749 p_data_typ_cd => p_data_typ_cd,
750 p_ext_typ_cd => p_ext_typ_cd,
751 p_rcd_typ_cd => 'D',
752 p_low_lvl_cd => 'T',
756 p_effective_date => p_effective_date );
753 p_person_id => p_person_id,
754 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
755 p_business_group_id=> hxc_ext_timecard.g_params.p_business_group_id,
757
758 l_summary_written := TRUE;
759 --END IF;
760
761 if g_debug then
762 hr_utility.set_location('Processing '||l_proc, 60);
763 end if;
764
765 -- call detail inclusion
766
767 l_include := 'N';
768
769 ben_ext_evaluate_inclusion.evaluate_timecard_incl
770 (p_otl_lvl => 'DETAIL'
771 ,p_project_id => l_project_id
772 ,p_task_id => l_task_id
773 ,p_exp_typ_id => l_exp_typ_id
774 ,p_element_type_id => l_element_type_id
775 ,p_po_num => l_po_num
776 ,p_include => l_include );
777
778 if g_debug then
779 hr_utility.set_location('Processing '||l_proc, 70);
780 end if;
781
782 IF ( l_include = 'Y' )
783 THEN
784
785 if g_debug then
786 hr_utility.set_location('Processing '||l_proc, 80);
787 end if;
788
789 --IF ( ben_extract.g_otl_detl_lvl = 'Y' )
790 --THEN
791
792 if g_debug then
793 hr_utility.set_location('Processing '||l_proc, 90);
794 hr_utility.trace('Writing Detail for '||to_char(l_day_rec.time_building_block_id));
795 end if;
796
797 -- write l_detail
798
799 ben_ext_fmt.process_ext_recs(
800 p_ext_rslt_id => p_ext_rslt_id,
801 p_ext_file_id => p_ext_file_id,
802 p_data_typ_cd => p_data_typ_cd,
803 p_ext_typ_cd => p_ext_typ_cd,
804 p_rcd_typ_cd => 'D',
805 p_low_lvl_cd => 'TS',
806 p_person_id => p_person_id,
807 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
808 p_business_group_id=> hxc_ext_timecard.g_params.p_business_group_id,
809 p_effective_date => p_effective_date );
810
811 --END IF;
812
813 END IF; -- detail inclusion
814
815 if g_debug then
816 hr_utility.set_location('Processing '||l_proc, 100);
817 end if;
818
819 l_skip_PO := FALSE;
820 l_already_called_PO := FALSE;
821 clear_detail_globals;
822
823 END IF; -- ( NOT l_skip_PO ) AND ( l_old_det_id <> -1 )
824
825 if g_debug then
826 hr_utility.set_location('Processing '||l_proc, 110);
827 end if;
828
829 populate_alias_values (
830 p_detail_id => l_day_rec.time_building_block_id
831 , p_detail_ovn => l_day_rec.object_version_number
832 , p_resource_id => p_person_id );
833
834 l_old_det_id := l_day_rec.time_building_block_id;
835
836 END IF; -- l_old_det_id <> l_day_rec.time_building_block_id
837
838 if g_debug then
839 hr_utility.set_location('Processing '||l_proc, 120);
840 end if;
841
842
843 -- check whether or not the PO assocaited with this line
844 -- is permitted to be shown thos this Supplier
845
846 IF ( hxc_ext_timecard.g_params.buyer_supplier = 'SUPPLIER' AND
847 l_day_rec.attribute_category = 'PURCHASING' AND
848 NOT l_already_called_PO )
849 THEN
850
851 if g_debug then
852 hr_utility.set_location('Processing '||l_proc, 130);
853 end if;
854
855 -- call PO inclusion function
856
857 IF ( FALSE )
858 THEN
859
860 if g_debug then
861 hr_utility.set_location('Processing '||l_proc, 140);
862 end if;
863
864 l_skip_PO := TRUE;
865
866 END IF;
867
868 if g_debug then
869 hr_utility.set_location('Processing '||l_proc, 150);
870 end if;
871
872 l_already_called_PO := TRUE;
873
874 END IF; -- ( hxc_ext_timecard.g_params.buyer_supplier = 'SUPPLIER' )
875
876 if g_debug then
877 hr_utility.set_location('Processing '||l_proc, 160);
878 end if;
879
880 populate_details ( l_day_rec );
881
882 FETCH csr_get_day INTO l_day_rec;
883
884 END LOOP;
885
886 CLOSE csr_get_day;
887
888 -- INCLUDE writing record logic here too !!!
889 -- since the last record will not go back into the loop
890
891 if g_debug then
892 hr_utility.set_location('Processing '||l_proc, 170);
893 end if;
894
895 IF ( NOT l_skip_PO )
896 THEN
897
898 if g_debug then
899 hr_utility.set_location('Processing '||l_proc, 180);
900 end if;
901
902 IF NOT l_summary_written --AND ben_extract.g_otl_summ_lvl = 'Y'
903 THEN
904 if g_debug then
905 hr_utility.set_location('Processing '||l_proc, 190);
906 end if;
907
908 --write header
909
910 ben_ext_fmt.process_ext_recs(
911 p_ext_rslt_id => p_ext_rslt_id,
912 p_ext_file_id => p_ext_file_id,
913 p_data_typ_cd => p_data_typ_cd,
914 p_ext_typ_cd => p_ext_typ_cd,
915 p_rcd_typ_cd => 'D',
916 p_low_lvl_cd => 'T',
917 p_person_id => p_person_id,
918 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
922 l_summary_written := TRUE;
919 p_business_group_id=> hxc_ext_timecard.g_params.p_business_group_id,
920 p_effective_date => p_effective_date );
921
923 END IF;
924
925 if g_debug then
926 hr_utility.set_location('Processing '||l_proc, 200);
927 end if;
928
929 -- call detail inclusion
930
931 l_include := 'N';
932
933 ben_ext_evaluate_inclusion.evaluate_timecard_incl
934 (p_otl_lvl => 'DETAIL'
935 ,p_project_id => l_project_id
936 ,p_task_id => l_task_id
937 ,p_exp_typ_id => l_exp_typ_id
938 ,p_element_type_id => l_element_type_id
939 ,p_po_num => l_po_num
940 ,p_include => l_include );
941
942 if g_debug then
943 hr_utility.set_location('Processing '||l_proc, 210);
944 end if;
945
946 IF ( l_include = 'Y' )
947 THEN
948
949 if g_debug then
950 hr_utility.set_location('Processing '||l_proc, 220);
951 end if;
952
953 --IF ( ben_extract.g_otl_detl_lvl = 'Y' )
954 --THEN
955
956 if g_debug then
957 hr_utility.set_location('Processing '||l_proc, 230);
958 end if;
959
960 -- write l_detail
961
962 ben_ext_fmt.process_ext_recs(
963 p_ext_rslt_id => p_ext_rslt_id,
964 p_ext_file_id => p_ext_file_id,
965 p_data_typ_cd => p_data_typ_cd,
966 p_ext_typ_cd => p_ext_typ_cd,
967 p_rcd_typ_cd => 'D',
968 p_low_lvl_cd => 'TS',
969 p_person_id => p_person_id,
970 p_chg_evt_cd => ben_ext_person.g_chg_evt_cd,
971 p_business_group_id=> hxc_ext_timecard.g_params.p_business_group_id,
972 p_effective_date => p_effective_date );
973
974 --END IF;
975
976 if g_debug then
977 hr_utility.set_location('Processing '||l_proc, 240);
978 end if;
979
980 END IF; -- detail inclusion
981
982 if g_debug then
983 hr_utility.set_location('Processing '||l_proc, 250);
984 end if;
985
986 END IF; -- ( NOT l_skip_PO )
987
988 if g_debug then
989 hr_utility.set_location('Leaving '||l_proc, 260);
990 end if;
991 */
992 null;
993
994 END process_detail;
995
996 PROCEDURE po_otl_extract (
997 errbuf out NOCOPY varchar2,
998 retcode out NOCOPY number,
999 p_ext_dfn_id in number,
1000 p_effective_date in varchar2,
1001 p_business_group_id in number,
1002 p_start_date in varchar2,
1003 p_end_date in varchar2,
1004 p_timecard_status in varchar2,
1005 p_vendor_id in varchar2 default null,
1006 p_person_id in varchar2 default null,
1007 p_retrieval_process in varchar2,
1008 p_buyer_supplier in varchar2 default 'BUYER' ) IS
1009
1010 l_dummy_errbuf varchar2(2000);
1011 l_dummy_retcode number;
1012
1013 l_proc VARCHAR2(72) ;
1014
1015 BEGIN
1016 /*
1017 g_debug :=hr_utility.debug_enabled;
1018 if g_debug then
1019 l_proc := g_package||'po_otl_extract';
1020 hr_utility.set_location('Entering '||l_proc, 10);
1021 end if;
1022
1023 if g_debug then
1024 hr_utility.trace('po_otl_extract : 1');
1025 hr_utility.trace('date is '||p_start_date);
1026 end if;
1027
1028 hxc_ext_timecard.g_params.start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
1029 hxc_ext_timecard.g_params.end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
1030 hxc_ext_timecard.g_params.status := p_timecard_status;
1031 hxc_ext_timecard.g_params.vendor_id := p_vendor_id;
1032 hxc_ext_timecard.g_params.retrieval_process := p_retrieval_process;
1033 hxc_ext_timecard.g_params.retrieval_process := 'Purchasing Retrieval Process';
1034 hxc_ext_timecard.g_params.buyer_supplier := p_buyer_supplier;
1035 hxc_ext_timecard.g_params.p_business_group_id := p_business_group_id;
1036 hxc_ext_timecard.g_params.p_person_id := p_person_id;
1037
1038 if g_debug then
1039 hr_utility.trace('po_otl_extract : 2');
1040 end if;
1041
1042 -- Code to dynamically set Person criteria
1043
1044 if g_debug then
1045 hr_utility.trace('po_otl_extract : 3');
1046 hr_utility.set_location('Processing '||l_proc, 20);
1047 end if;
1048
1049 ben_ext_thread.process (
1050 errbuf => l_dummy_errbuf
1051 ,retcode => l_dummy_retcode
1052 ,p_benefit_action_id => NULL
1053 ,p_ext_dfn_id => p_ext_dfn_id
1054 ,p_effective_date => p_effective_date
1055 ,p_business_group_id => p_business_group_id );
1056
1057 if g_debug then
1058 hr_utility.trace('l err buff is '||l_dummy_errbuf);
1059 hr_utility.trace('l ret code is '||l_dummy_retcode);
1060 hr_utility.trace('po_otl_extract : 4');
1061 hr_utility.set_location('Leaving '||l_proc, 30);
1062 end if;
1063 */
1064 null;
1065 END po_otl_extract;
1066
1067 end hxc_ext_timecard;