1 PACKAGE BODY HXC_RPT_TC_AUDIT_TRAIL AS
2 /* $Header: hxcrptaudittrail.pkb 120.10.12010000.5 2009/01/03 11:04:28 asrajago ship $ */
3
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5
6 newline VARCHAR2(1) :=
7 '
8 ';
9
10
11
12
13 -- AFTERPFORM
14 -- Calls the main action block and passes on the global parameters.
15
16 FUNCTION afterpform
17 RETURN BOOLEAN
18 AS
19
20 errbuf VARCHAR2(100);
21 retcode VARCHAR2(100);
22
23 BEGIN
24
25
26 -- Public Procedure afterpform
27 -- Calls execute_audit_trail_reporting passing the global parameters
28 -- Is called from the HXCRPTAUD.xml, data definition file.
29
30 execute_audit_trail_reporting (errbuf => errbuf,
31 retcode => retcode,
32 p_date_from => p_from_date ,
33 p_date_to => p_to_date ,
34 p_data_regen => p_dat_regen ,
35 p_record_save => p_record_save,
36 p_org_id => p_org_id ,
37 p_locn_id => p_locn_id ,
38 p_payroll_id => p_payroll_id ,
39 p_supervisor_id => p_supervisor_id ,
40 p_person_id => p_person_id );
41
42 RETURN TRUE;
43 END;
44
45
46 -- AFTERREPORT
47 -- Clears HXC_RPT_TC_AUDIT after the reporting is done.
48
49 FUNCTION afterreport
50 RETURN BOOLEAN
51 AS
52
53 BEGIN
54
55 -- Public Procedure afterreport
56 -- Truncates table HXC_RPT_TC_AUDIT after reporting is done.
57 -- Is called from HXCRPTAUD.xml file.
58
59 --DELETE FROM hxc_rpt_tc_audit;
60 COMMIT;
61
62 RETURN TRUE;
63 END;
64
65 FUNCTION beforereport
66 RETURN BOOLEAN
67 AS
68
69 BEGIN
70 RETURN TRUE;
71 END;
72
73
74 -- TRANSLATE_PARAMETERS
75 -- Translates all the ids that are provided as parameters to the names for
76 -- display in the report.
77
78 PROCEDURE translate_parameters
79 AS
80
81 BEGIN
82
83 -- Public Procedure translate_parameters
84 -- Browse thru all the parameters, to check if they are NULL.
85 -- If anything is not null, get the relevant names from corresponding
86 -- tables.
87 -- Convert the from,to,sysdate, data_regen and record_save flags
88 -- anyway, because you need them always.
89
90
91
92 IF g_debug
93 THEN
94 hr_utility.trace('translate_parameters');
95 END IF;
96
97 lp_from_date := to_char(fnd_date.canonical_to_date(p_from_date),'dd-MON-yyyy');
98 lp_to_date := to_char(fnd_date.canonical_to_date(p_to_date),'dd-MON-yyyy');
99
100 IF p_record_save = 'Y'
101 THEN
102 lp_record_save := 'Yes';
103 ELSE
104 lp_record_save := 'No';
105 END IF;
106
107 IF P_DAT_REGEN = 'Y'
108 THEN
109 lp_dat_regen := 'Yes';
110 ELSE
111 lp_dat_regen := 'No';
112 END IF;
113
114
115 IF (p_org_id IS NOT NULL)
116 THEN
117 SELECT name
118 INTO lp_org
119 FROM hr_all_organization_units_tl
120 WHERE organization_id = p_org_id
121 AND language = USERENV('LANG');
122
123 END IF;
124
125 IF (p_locn_id IS NOT NULL)
126 THEN
127 SELECT location_code
128 INTO lp_location
129 FROM hr_locations_all_tl
130 WHERE location_id = p_locn_id
131 AND language = USERENV('LANG');
132
133 END IF;
134
135 IF (p_payroll_id IS NOT NULL)
136 THEN
137 SELECT payroll_name
138 INTO lp_payroll
139 FROM pay_all_payrolls_f
140 WHERE payroll_id = p_payroll_id
141 AND fnd_date.canonical_TO_DATE(p_from_date) BETWEEN effective_start_date
142 AND effective_END_date;
143
144 END IF;
145
146 IF (p_supervisor_id IS NOT NULL)
147 THEN
148 SELECT full_name
149 INTO lp_supervisor
150 FROM per_all_people_f
151 WHERE person_id = p_supervisor_id
152 AND SYSDATE BETWEEN effective_start_date
153 AND effective_END_date;
154
155 END IF;
156
157 IF (p_person_id IS NOT NULL)
158 THEN
159 SELECT full_name
160 INTO lp_person
161 FROM per_all_people_f
162 WHERE person_id = p_person_id
163 AND SYSDATE BETWEEN effective_start_date
164 AND effective_END_date;
165
166 END IF;
167
168 lp_sysdate := TO_CHAR(SYSDATE,'dd-Mon-yyyy HH:MI:SS ');
169
170
171 SELECT fnd.user_name||' ['||ppf.full_name||']'
172 INTO lp_user
173 FROM per_all_people_f ppf,
174 fnd_user fnd
175 WHERE ppf.person_id = fnd.employee_id
176 AND SYSDATE BETWEEN ppf.effective_start_date
177 AND ppf.effective_end_date
178 AND fnd.user_id = FND_GLOBAL.USER_ID;
179
180
181
182 IF g_debug
183 THEN
184 hr_utility.trace('Translated parameters normally ');
185 hr_utility.trace('lp_from_date : '||lp_from_date);
186 hr_utility.trace('lp_to_date : '||lp_to_date);
187 hr_utility.trace('lp_dat_regen : '||lp_dat_regen);
188 hr_utility.trace('lp_record_save : '||lp_record_save);
189 hr_utility.trace('lp_org : '||lp_org);
190 hr_utility.trace('lp_location : '||lp_location);
191 hr_utility.trace('lp_payroll : '||lp_payroll);
192 hr_utility.trace('lp_supervisor : '||lp_supervisor);
193 hr_utility.trace('lp_person : '||lp_person);
194 hr_utility.trace('lp_user : '||lp_user);
195 END IF;
196
197 END translate_parameters;
198
199
200
201 -- EXECUTE_AUDIT_TRAIL_REPORTING
202 -- Main action block for Timecard Audit Trail Reporting, processes
203 -- the detail records accordingly, and loads data into HXC_RPT_TC_AUDIT.
204
205 PROCEDURE execute_audit_trail_reporting (errbuf OUT NOCOPY VARCHAR2,
206 retcode OUT NOCOPY NUMBER,
207 p_date_from IN VARCHAR2 ,
208 p_date_to IN VARCHAR2 ,
209 p_data_regen IN VARCHAR2 ,
210 p_record_save IN VARCHAR2 ,
211 p_org_id IN NUMBER DEFAULT NULL,
212 p_locn_id IN NUMBER DEFAULT NULL,
213 p_payroll_id IN NUMBER DEFAULT NULL,
214 p_supervisor_id IN NUMBER DEFAULT NULL,
215 p_person_id IN NUMBER DEFAULT NULL )
216 AS
217
218 l_call_status BOOLEAN ;
219 l_interval NUMBER := 30;
220 l_phase VARCHAR2(30);
221 l_status VARCHAR2(30);
222 l_dev_phase VARCHAR2(30);
223 l_dev_status VARCHAR2(30);
224 l_message VARCHAR2(30);
225 l_sqlcode NUMBER;
226 l_sqlmsg VARCHAR2(100);
227
228
229 l_data_load_request_id NUMBER;
230 l_resource_id NUMBER;
231 l_start_time DATE;
232 l_stop_time DATE;
233 l_resource_count NUMBER := 0;
234
235 CURSOR get_timecards ( p_request_id NUMBER)
236 IS SELECT resource_id,
237 tc_start_time,
238 tc_stop_time
239 FROM hxc_rpt_tc_hist_log log
240 WHERE request_id = p_request_id ;
241
242 l_trans_id_tab FLOATTABLE;
243 l_trans_date_tab DATETABLE;
244 l_trans_max_date_tab DATETABLE;
245 l_trans_user_tab VARCHARTABLE;
246 l_trans_name_tab VARCHARTABLE;
247 l_comments_tab VARCHARTABLE;
248 l_bb_id_tab NUMTABLE;
249 l_bb_ovn_tab NUMTABLE;
250 l_max_bb_id_tab NUMTABLE;
251 l_trans_status_tab VARCHARTABLE;
252
253
254 l_del_bb_id_tab NUMTABLE;
255 l_del_bb_ovn_tab NUMTABLE;
256 l_del_date_tab DATETABLE;
257 l_del_id_tab FLOATTABLE;
258
259
260
261
262
263 -- EXECUTE AUDIT TRAIL REPORTING -- FUNCTIONAL FLOW
264 -- ================================================
265 --
266 -- This prog is used to created the Change history for each timecard, whose
267 -- information is retrieved from HXC_TIME_ATTRIBUTES, HXC_TIME_BUILDING_BLOCKS
268 -- and HXC_TRANSACTIONS.
269 --
270 -- The processing is done per timecard -- meaning the table HXC_RPT_TC_DETAILS_ALL
271 -- table is grouped by resource_id, start_time and stop_time for each iteration
272 -- of processing.
273 --
274 -- Timecard Level Actions
275 -- ======================
276 --
277 -- Submissions
278 -- -----------
279 --
280 -- In ideal case, each submission would mean a different transaction_id with atleast
281 -- one detail building block id in SUBMITTED status. For Self Service, Transaction
282 -- is created only when there is a Submission, not for timecard Save, but Timekeeper
283 -- creates a Deposit Transaction for timecard Save as well. So the most basic submission
284 -- has to be captured by looking for distinct DEPOSIT transactions, with Time building
285 -- blocks which are not in WORKING status. In Self service, there is no way you are going
286 -- to submit a timecard with ERROR status, but Timekeeper can do so. Meaning, we are
287 -- showing ERROR status as such too.
288 --
289 -- For the record_save option, a different query would run and pick up the WORKING
290 -- status guys as well.
291 --
292 -- Approvals
293 -- ---------
294 --
295 -- Timecard approvals normally are captured by the APPLICATION_PERIOD scope records
296 -- from hxc_time_building_blocks. A join with our Details_all table and hxc_time_building
297 -- blocks will give you this information. Once you get all the approvals, these has to be placed
298 -- so that the reporting table will get this info in proper place. For this, the submissions
299 -- captured above are browsed thru and a most appropriate submission is found out.
300 -- The transaciton_id of this submission is taken in as the transaction id of the approval
301 -- action and while the report groups the data on transaction_id, we get in the proper order.
302 -- The most appropriate approval is a transaction_id whose action date is equal to
303 -- ( only for Approval on submit style ) or less than the action date for the approval,
304 -- and who has a successive transaction_id with action date greater than the
305 -- approval date.
306 --
307 -- eg. Approval date Transaction_dates
308 -- 08:05:11 07:50:33
309 -- 07:59:15
310 -- 08:03:52
311 -- 08:13:34
312 -- 09:45:23
313 --
314 -- Here the given approval will take the transaction id of 08:03:52.
315 --
316 --
317 --
318 -- Deletions
319 -- ---------
320 --
321 -- Deletions can be of two kinds.
322 --
323 -- 1) Deletion from Recent Timecards page.
324 -- 2) Overwriting with a Template. -- This is deletion and resubmission.
325 --
326 -- The first case is straight forward. When a timecard is deleted this way
327 -- its all time_building_blocks are end dated, and a new set will be
328 -- created with the created date equal to date_to. We track this down by
329 -- looking at transactions where the creation_date is equal to date_to
330 -- and day_date_to ( date_to of the parent day building block ) is
331 -- end dated. Actually for the first case day_date_to is equal to the
332 -- creation_date and date_to of the detail record, and this is the only
333 -- thing you need to check. This also means that a negative condition should
334 -- be added to evade this whilst recording submissions.
335 --
336 -- The second case is slightly more complex. Here the details are end dated
337 -- but a new set wont be created. The worst part is that there might be a
338 -- one or two seconds delay on the date_to timing for the existing set of
339 -- time_building_blocks -- Meaning, the existing OVN of Timecard.Day and
340 -- Detail scopes will have different date_to values. This is exactly the
341 -- reason why we cant equate date_to of the detail record to the day scope
342 -- record and pick up the deleted timecards even in the above case.
343 --
344 -- To escape this, we pick up all records having date_to = creation_date.
345 -- This will pick up records with Timecard Delete as we would expect,
346 -- Overwriting, again expected and those for which a row delete happened
347 -- in the time entry page. Those for which the row delete happened in the time
348 -- entry page would not have a new timebuilding block id for the submitted timecard.
349 -- Where as a template overwritten timecard will have a new set of time building
350 -- block ids.
351 --
355 -- screen ), the rest of the timecard is submitted. So loop thru the submissions
352 -- In either case, the entries would be tracked down as Submissions earlier --
353 -- because though you are deleting timecard as a whole ( while overwriting )
354 -- or deleting a line from the timecard ( while deleting from the time entry
356 -- and pick up the transaction of the deletion we are processing.
357 -- For this, check if the max timecard id and min timecard id for the transaction
358 -- are same to the deleted timecards timecard id.
359 --
360 -- TC bb id OVN date_to
361 -- 1 1 end of time
362 --
363 -- When deleted from the Recent timecards page.
364 --
365 -- TC bb id OVN date_to
366 -- 1 1 sysdate
367 --
368 -- When deleting a row from the Time entry page.
369 --
370 -- TC bb id OVN date_to
371 -- 1 1 sysdate
372 -- 1 2 end of time
373 --
374 -- When overwriting with a template
375 --
376 -- Tc bb id OVN date_to
377 -- 1 1 sysdate
378 -- 2 1 end of time.
379 --
380 --
381 -- Now the first case will be anyway captured from the query, and there neednt
382 -- be any check on this, because the transaction that is holding it will not
383 -- be picked up in submissions.
384 --
385 -- For the second case, we dont want this as a timecard delete, so compare the
386 -- transactions max tc bb id and min tc bb id. Both will be the same, so dont
387 -- use this record.
388 --
389 -- For the third case the comparison would fail and we have to consider this as
390 -- a deletion.
391 --
392 -- As of now, we are not considering a separate query for Deletions with Record save
393 -- option toggling. This is because, as of now, in 12.1.1, a deleting an entry
394 -- and saving the timecard yields a Submitted status time entry as well. This would
395 -- take care of Deletion with Working status being recorded.
396 --
397 --
398 --
399 -- Detail Changes
400 -- --------------
401 --
402 -- The logic followed here is put inline for more clarity.
403 --
404 --
405 --
406 --
407 -- ACTION_TYPE column
408 -- ==================
409 --
410 -- HXC_RPT_TC_AUDIT has this column called Action_type which is used particularly
411 -- in ordering the entries while querying for the report.
412 --
413 --
414 -- Timecard Level Actions
415 -- -----------------------
416 --
417 -- TS --- Timecard Submission
418 -- TSD --- Timecard Deletion
419 -- TSA --- Timecard Approval
420 --
421 --
422 -- These are named so to ease out ordering. A Deletion or an Approval cannot
423 -- happen without a Submission, hence TS ( alphabetically, the first of the three).
424 -- Once submitted, the next action that might come would be Deletion or Approval.
425 -- But in no way can a Deletion be followed by Approval. It has to be
426 -- either Sub->Appr->Deletion or Sub->Deletion, meaning Deletion will always
427 -- come to the end in the transaction. Hence TSD for Deletion and TSA for approval
428 -- because approval falls b/w the other two, if at all it falls.
429 --
430 --
431 --
432 -- Detail Level Actions
433 -- ---------------------
434 --
435 -- EN --- New Entry/Late Entry
436 -- EB --- Before Edit
437 -- EC --- After Edit
438 -- ED --- Deleted
439 --
440 --
441 --
442 -- No complex logic here. Only Before/After Edit can come together, for the same
443 -- date-detail. Hence EB and EC respectively, because Before Edit should appear
444 -- first.
445 --
446 --
447
448
449
450
451
452
453 -- RECORD_SUBMISSIONS
454 -- Queries against HXC_RPT_TC_DETAILS_ALL and captures all the timecard
455 -- submissions.
456
457 PROCEDURE record_submissions( p_resource_id IN NUMBER,
458 p_start_time IN DATE,
459 p_stop_time IN DATE,
460 p_record_save IN VARCHAR2)
461 AS
462
463
464
465
466 -- The following two queries have been given conditions for a range
467 -- for a date check. Creation_date and day_date_to logically could be
468 -- the same value, but for big timecards you would see a one or 2 secs delay.
469 -- Meaning, we check for this condition in a range, rather than an inequality.
470 --
471
472 CURSOR get_transactions ( p_resource_id NUMBER,
473 p_start_time DATE,
474 p_stop_time DATE )
475 IS SELECT transaction_id,
476 MAX(creation_date),
477 MIN(creation_date),
478 MIN(created_by_user),
479 MIN(tc_comments),
480 MIN(tc_bb_id),
481 MIN(tc_bb_ovn),
482 MAX(tc_bb_id),
483 MIN(resource_name),
484 MIN(status)
485 FROM hxc_rpt_tc_details_all
486 WHERE resource_id = p_resource_id
487 AND tc_start_time = p_start_time
488 AND tc_stop_time = p_stop_time
492 AND transaction_detail_id IS NOT NULL
489 AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
490 AND (day_date_to + (2/(24*60*60)))
491 AND transaction_id IS NOT NULL
493 AND status <> 'WORKING'
494 GROUP BY transaction_id
495 ORDER BY MIN(creation_date) ;
496
497 CURSOR get_all_transactions ( p_resource_id NUMBER,
498 p_start_time DATE,
499 p_stop_time DATE )
500 IS SELECT transaction_id,
501 MAX(creation_date),
502 MIN(creation_date),
503 MIN(created_by_user),
504 MIN(tc_comments),
505 MIN(tc_bb_id),
506 MIN(tc_bb_ovn),
507 MAX(tc_bb_id),
508 MIN(resource_name),
509 MIN(decode(transaction_detail_id,NULL,'WORKING',status))
510 FROM hxc_rpt_tc_details_all
511 WHERE resource_id = p_resource_id
512 AND tc_start_time = p_start_time
513 AND tc_stop_time = p_stop_time
514 AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
515 AND (day_date_to + (2/(24*60*60)))
516 AND transaction_id IS NOT NULL
517 GROUP BY transaction_id
518 ORDER BY MIN(creation_date) ;
519
520
521 BEGIN
522
523 -- Private Procedure record_submissions
524 -- Query from HXC_RPT_TC_DETAILS_ALL, grouping by transaction
525 -- id for this timecard, and pick up all the distinct
526 -- transaction ids, and relevant info like the users, dates
527 -- timecard_id, ovn, etc.
528 -- Insert into HXC_RPT_TC_AUDIT, the picked up information.
529
530 IF g_debug
531 THEN
532 hr_utility.trace('record_submissions for '||p_resource_id
533 ||' from '||p_start_time
534 ||' to '||p_stop_time);
535 END IF;
536
537 IF p_record_save = 'Y'
538 THEN
539 OPEN get_all_transactions(p_resource_id,
540 p_start_time,
541 p_stop_time );
542
543 FETCH get_all_transactions BULK COLLECT INTO l_trans_id_tab,
544 l_trans_max_date_tab,
545 l_trans_date_tab,
546 l_trans_user_tab,
547 l_comments_tab,
548 l_bb_id_tab,
549 l_bb_ovn_tab,
550 l_max_bb_id_tab,
551 l_trans_name_tab,
552 l_trans_status_tab ;
553 CLOSE get_all_transactions;
554 ELSE
555 OPEN get_transactions(p_resource_id,
556 p_start_time,
557 p_stop_time );
558
559 FETCH get_transactions BULK COLLECT INTO l_trans_id_tab,
560 l_trans_max_date_tab,
561 l_trans_date_tab,
562 l_trans_user_tab,
563 l_comments_tab,
564 l_bb_id_tab,
565 l_bb_ovn_tab,
566 l_max_bb_id_tab,
567 l_trans_name_tab,
568 l_trans_status_tab ;
569 CLOSE get_transactions;
570 END IF;
571
572 IF g_debug
573 THEN
574 hr_utility.trace('Fetched from get_transactions ');
575 hr_utility.trace('Total number of submissions : '||l_trans_id_tab.COUNT);
576 END IF;
577
578
579 IF l_trans_id_tab.COUNT > 0
580 THEN
581 FORALL i IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST
582 INSERT INTO hxc_rpt_tc_audit
583 ( resource_id,
584 tc_start_time,
585 tc_stop_time,
586 resource_name,
587 action,
588 action_date,
589 action_by,
590 comments,
591 transaction_id,
592 tc_bb_id,
593 tc_bb_ovn,
594 action_type )
595 VALUES ( p_resource_id,
596 p_start_time,
597 p_stop_time,
598 l_trans_name_tab(i),
599 INITCAP(DECODE(l_trans_status_tab(i),'WORKING','Saved',l_trans_status_tab(i))),
600 l_trans_max_date_tab(i),
601 l_trans_user_tab(i),
602 l_comments_tab(i),
603 l_trans_id_tab(i),
604 l_bb_id_tab(i),
605 l_bb_ovn_tab(i),
606 'TS' );
610 THEN
607 END IF;
608
609 IF g_debug
611 hr_utility.trace('record_submissions completed alright ');
612 END IF;
613
614
615 END record_submissions ;
616
617
618
619 -- RECORD_APPROVALS
620 -- Queries against HXC_TIME_BUIDLING_BLOCKS to capture the
621 -- Approval details for this timecard.
622
623 PROCEDURE record_approvals ( p_resource_id IN NUMBER,
624 p_start_time IN DATE,
625 p_stop_time IN DATE )
626 AS
627
628
629 CURSOR get_approvals ( p_resource_id NUMBER,
630 p_start_time DATE,
631 p_stop_time DATE )
632 IS SELECT 0 transaction_id,
633 0 tc_bb_id,
634 hxc.creation_date,
635 fnd.user_name ,
636 NVL(fnd.employee_id,-1),
637 INITCAP(approval_status),
638 DECODE(comment_text,
639 'LIGHT_APPROVAL','Approval On Submit',
640 'AUTO_APPROVE', 'Auto Approved',
641 comment_text),
642 ' '
643 FROM hxc_time_building_blocks hxc,
644 fnd_user fnd
645 WHERE scope = 'APPLICATION_PERIOD'
646 AND resource_id = p_resource_id
647 AND p_start_time BETWEEN start_time
648 AND stop_time
649 AND TRUNC(p_stop_time) BETWEEN start_time
650 AND stop_time
651 AND approval_status IN ('APPROVED','REJECTED')
652 AND fnd.user_id = hxc.created_by
653 AND NVL(hxc.comment_text,' ') <> 'TIMED_OUT'
654 AND NVL(hxc.comment_text,' ') <> 'BLANK_NOTIFICATION'
655 ORDER BY hxc.creation_date ;
656
657
658
659 CURSOR get_approvers ( p_person_id NUMBER,
660 p_appr_date DATE )
661 IS SELECT full_name
662 FROM per_all_people_f
663 WHERE person_id = p_person_id
664 AND p_appr_date BETWEEN effective_start_date
665 AND effective_end_date ;
666
667
668
669 l_appr_trans_tab NUMTABLE;
670 l_appr_bb_tab NUMTABLE;
671 l_appr_date_tab DATETABLE;
672 l_appr_user_tab VARCHARTABLE;
673 l_appr_status_tab VARCHARTABLE;
674 l_appr_comments_tab VARCHARTABLE;
675 l_appr_resource_tab VARCHARTABLE;
676 l_appr_person_tab NUMTABLE;
677 l_approver_name VARCHAR2(400);
678
679 BEGIN
680
681 -- Private Procedure record_approvals
682 -- Queries against HXC_TIME_BUILDING_BLOCKS and FND_USER
683 -- for all the APPLICATION_PERIOD records for this timecard.
684 -- The application periods are joined with BETWEEN & AND
685 -- conditions to pull up those timecards whose Approval period
686 -- spans across multiple timecard periods.
687 -- To find out the user's name fetch the full name from PER_ALL_PEOPLE_F.
688 -- To find out the transaction/submission that matches these approvals,
689 -- loops thru the transaction date pl/sql table already created, to find
690 -- a matching transaction.
691 -- Once all the transactions are picked up, inserts the records into HXC_RPT_TC_AUDIT.
692
693 IF g_debug
694 THEN
695 hr_utility.trace('record_approvals for '||p_resource_id
696 ||' from '||p_start_time
697 ||' to '||p_stop_time);
698 END IF;
699
700 OPEN get_approvals (p_resource_id,
701 p_start_time,
702 p_stop_time ) ;
703
704 FETCH get_approvals BULK COLLECT INTO l_appr_trans_tab,
705 l_appr_bb_tab,
706 l_appr_date_tab,
707 l_appr_user_tab,
708 l_appr_person_tab,
709 l_appr_status_tab,
710 l_appr_comments_tab,
711 l_appr_resource_tab ;
712
713 CLOSE get_approvals;
714
715 IF g_debug
716 THEN
717 hr_utility.trace('Fetched from get_approvals ');
718 hr_utility.trace('Total number of approvals : '||l_appr_trans_tab.COUNT);
719 END IF;
720
721
722 IF l_appr_trans_tab.COUNT > 0
723 THEN
724 FOR i IN l_appr_trans_tab.FIRST..l_appr_trans_tab.LAST
725 LOOP
726
727 -- Initialize the approver's name to blank.
728 l_approver_name := ' ';
729
730 -- Do this only if there exists a valid person. -1 here would mean
731 -- a null for employee_id in FND_USER, so the person who approved
732 -- is a dummy person like SYSADMIN.
733
734 IF l_appr_person_tab(i) <> -1
735 THEN
736 OPEN get_approvers( l_appr_person_tab(i),
737 l_appr_date_tab(i) );
741
738 FETCH get_approvers INTO l_approver_name;
739 CLOSE get_approvers;
740 END IF;
742 l_appr_user_tab(i) := l_appr_user_tab(i)||newline
743 ||'['||l_approver_name||']';
744
745 FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST-1
746 LOOP
747
748 IF ( (l_trans_date_tab(j) <= l_appr_date_tab(i))
749 AND (l_trans_date_tab(j+1) > l_appr_date_tab(i)))
750 THEN
751 l_appr_trans_tab(i) := l_trans_id_tab(j);
752 l_appr_bb_tab(i) := l_bb_id_tab(j);
753 l_appr_resource_tab(i) := l_trans_name_tab(j);
754 EXIT;
755 END IF;
756 END LOOP;
757 IF l_appr_trans_tab(i) = 0
758 THEN
759 l_appr_trans_tab(i) := l_trans_id_tab(l_trans_id_tab.LAST);
760 l_appr_bb_tab(i) := l_bb_id_tab(l_bb_id_tab.LAST);
761 l_appr_resource_tab(i) := l_trans_name_tab(l_bb_id_tab.LAST);
762 END IF;
763
764 END LOOP;
765
766 FORALL i IN l_appr_trans_tab.FIRST..l_appr_trans_tab.LAST
767 INSERT INTO hxc_rpt_tc_audit
768 ( resource_id,
769 tc_start_time,
770 tc_stop_time,
771 resource_name,
772 action,
773 action_date,
774 action_by,
775 comments,
776 transaction_id,
777 tc_bb_id,
778 action_type )
779 VALUES ( p_resource_id,
780 p_start_time,
781 p_stop_time,
782 l_appr_resource_tab(i),
783 l_appr_status_tab(i),
784 l_appr_date_tab(i),
785 l_appr_user_tab(i),
786 l_appr_comments_tab(i),
787 l_appr_trans_tab(i),
788 l_appr_bb_tab(i),
789 'TSA' );
790
791 l_appr_trans_tab.DELETE;
792 l_appr_bb_tab.DELETE;
793 l_appr_date_tab.DELETE;
794 l_appr_user_tab.DELETE;
795 l_appr_resource_tab.DELETE;
796 l_appr_status_tab.DELETE;
797 l_appr_comments_tab.DELETE;
798
799
800 END IF;
801
802 IF g_debug
803 THEN
804 hr_utility.trace('record_approvals completed alright ');
805 END IF;
806
807
808
809 END record_approvals ;
810
811
812
813
814 -- RECORD_DELETIONS
815 -- Queries against HXC_RPT_TC_DETAILS_ALL to find out all the timecard
816 -- deletions, and records these into HXC_RPT_TC_AUDIT.
817
818 PROCEDURE record_deletions ( p_resource_id IN NUMBER,
819 p_start_time IN DATE,
820 p_stop_time IN DATE )
821 AS
822
823 CURSOR get_deletions ( p_resource_id NUMBER,
824 p_start_time DATE,
825 p_stop_time DATE )
826 IS SELECT transaction_id,
827 MIN(creation_date),
828 MIN(created_by_user),
829 MIN(tc_comments),
830 MIN(tc_bb_id),
831 MIN(tc_bb_ovn),
832 MIN(resource_name)
833 FROM hxc_rpt_tc_details_all det
834 WHERE resource_id = p_resource_id
835 AND tc_start_time = p_start_time
836 AND tc_stop_time = p_stop_time
837 AND day_date_to <> hr_general.end_of_time
838 AND creation_date = date_to
839 AND transaction_id IS NOT NULL
840 AND status <> 'WORKING'
841 AND NOT EXISTS ( SELECT 1
842 FROM hxc_timecard_summary hxc
843 WHERE timecard_id = det.tc_bb_id
844 AND hxc.resource_id = det.resource_id
845 AND hxc.start_time = det.tc_start_time
846 )
847 GROUP BY transaction_id
848 ORDER BY MIN(creation_date) ;
849
850
851
852
853 l_del_comments_tab VARCHARTABLE;
854 l_del_user_tab VARCHARTABLE;
855 l_del_name_tab VARCHARTABLE;
856
857
858
859 BEGIN
860
861
862 -- Private Procedure record_deletions
863 -- Queries against HXC_RPT_TC_DETAILS_ALL table for all records
864 -- that have creation_date = deleted date for the day record
865 -- These indicate a timecard delete.
866 -- Insert these including the transaction_ids into HXC_RPT_TC_AUDIT.
867
868 IF g_debug
869 THEN
870 hr_utility.trace('record_deletions for '||p_resource_id
871 ||' from '||p_start_time
872 ||' to '||p_stop_time);
873 END IF;
874
875
879 FETCH get_deletions BULK COLLECT INTO l_del_id_tab,
876 OPEN get_deletions (p_resource_id,
877 p_start_time,
878 p_stop_time );
880 l_del_date_tab,
881 l_del_user_tab,
882 l_del_comments_tab,
883 l_del_bb_id_tab,
884 l_del_bb_ovn_tab,
885 l_del_name_tab ;
886 CLOSE get_deletions;
887
888 IF g_debug
889 THEN
890 hr_utility.trace('Fetched from get_deletions ');
891 hr_utility.trace('Total number of deletions : '||l_del_id_tab.COUNT);
892 END IF;
893
894
895 IF l_del_id_tab.COUNT > 0
896 THEN
897 FOR i IN l_del_id_tab.FIRST..l_del_id_tab.LAST
898 LOOP
899 FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST
900 LOOP
901 IF l_trans_id_tab(j) = l_del_id_tab(i)
902 THEN
903
904 IF l_max_bb_id_tab(j) = l_del_bb_id_tab(i)
905 THEN
906 l_del_id_tab(i) := 0;
907 EXIT;
908 END IF; -- l_max_bb_id_tab(j) = l_bb_id_tab(j)
909
910 IF l_trans_id_tab.EXISTS(j-1)
911 THEN
912 l_del_id_tab(i) := l_trans_id_tab(j-1) ;
913 ELSE
914 l_del_id_tab(i) := l_del_id_tab(i) - 1;
915 END IF; -- l_trans_id_tab.EXISTS(j-1)
916 EXIT; -- l_trans_id_tab.FIRST..l_trans_id_tab.LAST
917 END IF; -- l_trans_id_tab(j) = l_del_id_tab(i)
918 END LOOP; -- l_trans_id_tab.FIRST..l_trans_id_tab.LAST
919 END LOOP; -- l_del_id_tab.FIRST..l_del_id_tab.LAST
920
921
922
923 FOR i IN l_del_id_tab.FIRST..l_del_id_tab.LAST
924 LOOP
925 IF l_del_id_tab(i) <> 0
926 THEN
927 INSERT INTO hxc_rpt_tc_audit
928 ( resource_id,
929 tc_start_time,
930 tc_stop_time,
931 resource_name,
932 action,
933 action_date,
934 action_by,
935 comments,
936 transaction_id,
937 tc_bb_id,
938 tc_bb_ovn,
939 action_type )
940 VALUES ( p_resource_id,
941 p_start_time,
942 p_stop_time,
943 l_del_name_tab(i),
944 'Deleted Timecard',
945 l_del_date_tab(i),
946 l_del_user_tab(i),
947 l_del_comments_tab(i),
948 l_del_id_tab(i),
949 l_del_bb_id_tab(i),
950 l_del_bb_ovn_tab(i),
951 'TSD' );
952 END IF; -- l_del_id_tab(i) <> 0
953 END LOOP;
954
955 l_del_comments_tab.DELETE;
956 l_del_user_tab.DELETE;
957 l_del_name_tab.DELETE;
958
959 END IF;
960
961 IF g_debug
962 THEN
963 hr_utility.trace('record_deletions completed alright ');
964 END IF;
965
966
967 END record_deletions ;
968
969
970
971
972 -- RECORD_DETAILS
973 -- Queries against HXC_RPT_TC_DETAILS_ALL and records the detail records
974 -- in the required format.
975
976
977 PROCEDURE record_details ( p_resource_id IN NUMBER,
978 p_start_time IN DATE,
979 p_stop_time IN DATE,
980 p_record_save IN VARCHAR2 )
981 AS
982
983 l_audit_cnt NUMBER := 0;
984 temp1 VARCHAR2(500);
985 temp2 VARCHAR2(500);
986
987 l_tc_details TIMEDETAILSTABLE;
988 l_audit_details AUDITTABLE ;
989
990 l_delete_done BOOLEAN;
991
992
993 -- The following two queries have been given conditions for a range
994 -- for a date check. Creation_date and day_date_to logically could be
995 -- the same value, but for big timecards you would see a one or 2 secs delay.
996 -- Meaning, we check for this condition in a range, rather than an inequality.
997 --
998 CURSOR get_details ( p_resource_id NUMBER,
999 p_start_time DATE,
1000 p_stop_time DATE )
1001 IS SELECT *
1002 FROM hxc_rpt_tc_details_all
1003 WHERE resource_id = p_resource_id
1004 AND tc_start_time = p_start_time
1005 AND tc_stop_time = p_stop_time
1006 AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
1007 AND (day_date_to + (2/(24*60*60)))
1011 ORDER BY detail_bb_id,
1008 AND transaction_id IS NOT NULL
1009 AND transaction_detail_id IS NOT NULL
1010 AND status <> 'WORKING'
1012 detail_bb_ovn ;
1013
1014
1015 CURSOR get_all_details ( p_resource_id NUMBER,
1016 p_start_time DATE,
1017 p_stop_time DATE )
1018 IS SELECT *
1019 FROM hxc_rpt_tc_details_all
1020 WHERE resource_id = p_resource_id
1021 AND tc_start_time = p_start_time
1022 AND tc_stop_time = p_stop_time
1023 AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
1024 AND (day_date_to + (2/(24*60*60)))
1025 AND transaction_id IS NOT NULL
1026 ORDER BY detail_bb_id,
1027 detail_bb_ovn ;
1028
1029
1030 -- COPY_TIMECARD_TO_AUDIT
1031 -- Copies the timecard record from HXC_RPT_TC_DETAILS_ALL to an
1032 -- audit record format, like the record structure in HXC_RPT_TC_AUDIT.
1033
1034 PROCEDURE copy_timecard_to_audit (p_tc_record IN hxc_rpt_tc_details_all%ROWTYPE,
1035 p_audit_record IN OUT NOCOPY hxc_rpt_tc_audit%ROWTYPE)
1036 AS
1037 BEGIN
1038
1039 -- Private Procedure copy_timecard_to_audit
1040 -- Copies timecard style record from HXC_RPT_TC_DETAILS_ALL
1041 -- to audit type after putting in necessary formatting, like
1042 -- concatenating all attributes, with a Line Feed as separator.
1043
1044 p_audit_record.detail_bb_id := p_tc_record.detail_bb_id;
1045 p_audit_record.detail_bb_ovn := p_tc_record.detail_bb_ovn;
1046 p_audit_record.time_entry_date := p_tc_record.day_start_time;
1047 p_audit_record.attribute_info :=
1048 p_tc_record.attribute1||newline||p_tc_record.attribute2||newline||
1049 p_tc_record.attribute3||newline||p_tc_record.attribute4||newline||
1050 p_tc_record.attribute5||newline||p_tc_record.attribute6||newline||
1051 p_tc_record.attribute7||newline||p_tc_record.attribute8||newline||
1052 p_tc_record.attribute9||newline||p_tc_record.attribute10||newline||
1053 p_tc_record.attribute11||newline||p_tc_record.attribute12||newline||
1054 p_tc_record.attribute13||newline||p_tc_record.attribute14||newline||
1055 p_tc_record.attribute15||newline||p_tc_record.attribute16||newline||
1056 p_tc_record.attribute17||newline||p_tc_record.attribute18||newline||
1057 p_tc_record.attribute19||newline||p_tc_record.attribute20||newline||
1058 p_tc_record.attribute21||newline||p_tc_record.attribute22||newline||
1059 p_tc_record.attribute23||newline||p_tc_record.attribute24||newline||
1060 p_tc_record.attribute25||newline||p_tc_record.attribute26||newline||
1061 p_tc_record.attribute27||newline||p_tc_record.attribute28||newline||
1062 p_tc_record.attribute29||newline||p_tc_record.attribute30;
1063
1064 p_audit_record.attribute_info := LTRIM(RTRIM(p_audit_record.attribute_info,newline),newline) ;
1065 p_audit_record.hours := p_tc_record.hours_measure;
1066 p_audit_record.action_date := p_tc_record.creation_date;
1067 p_audit_record.action_by := p_tc_record.created_by_user;
1068 p_audit_record.comments := p_tc_record.detail_comments;
1069 p_audit_record.transaction_id := p_tc_record.transaction_id;
1070 p_audit_record.resource_id := p_tc_record.resource_id;
1071 p_audit_record.resource_name := p_tc_record.resource_name;
1072 p_audit_record.tc_start_time := p_tc_record.tc_start_time;
1073 p_audit_record.tc_stop_time := p_tc_record.tc_stop_time;
1074 p_audit_record.cla_reason :=
1075 LTRIM(RTRIM(p_tc_record.cla_reason||'-'||
1076 p_tc_record.cla_comments,'-'),'-');
1077
1078 -- Code below added to include start stop times in the report.
1079 -- TO_CHAR(date,'HHMISSAM') will print the time in the following
1080 -- format.
1081 -- 115959PM
1082 -- If the start, stop times are not given, the default time
1083 -- of <date> 11:59:59 PM would be the column value.
1084 -- Just check this up. If its not the default time, record it
1085 -- as start stop time.
1086
1087
1088 IF TO_CHAR(p_tc_record.day_stop_time,'HHMISSAM') <> '115959PM'
1089 THEN
1090 p_audit_record.start_stop_time := to_char(p_tc_record.day_start_time,'HH:MI AM')||newline||
1091 to_char(p_tc_record.day_stop_time,'HH:MI AM');
1092 END IF;
1093
1094
1095
1096 END copy_timecard_to_audit ;
1097
1098 -- TIME_DETAILS
1099 -- For a given timecard record, from HXC_RPT_TC_DETAILS_ALL, returns a
1100 -- concatenated string of attributes, hours and comments.
1101
1102 FUNCTION time_details( p_tc_record IN hxc_rpt_tc_details_all%ROWTYPE )
1103 RETURN VARCHAR2
1104 AS
1105 time_detail VARCHAR2(4000);
1106
1107 BEGIN -- time_details
1108
1109 -- Private function time_details
1110 -- Concatenates the attributes, hours and detail comments and returns these as a
1111 -- string.
1112 time_detail := p_tc_record.attribute1||p_tc_record.attribute2||p_tc_record.attribute3||
1116 p_tc_record.attribute13||p_tc_record.attribute14||p_tc_record.attribute15||
1113 p_tc_record.attribute4||p_tc_record.attribute5||p_tc_record.attribute6||
1114 p_tc_record.attribute7||p_tc_record.attribute8||p_tc_record.attribute9||
1115 p_tc_record.attribute10||p_tc_record.attribute11||p_tc_record.attribute12||
1117 p_tc_record.attribute16||p_tc_record.attribute17||p_tc_record.attribute18||
1118 p_tc_record.attribute19||p_tc_record.attribute20||p_tc_record.attribute21||
1119 p_tc_record.attribute22||p_tc_record.attribute23||p_tc_record.attribute24||
1120 p_tc_record.attribute25||p_tc_record.attribute26||p_tc_record.attribute27||
1121 p_tc_record.attribute28||p_tc_record.attribute29||p_tc_record.attribute30||
1122 to_char(p_tc_record.day_start_time,'dd/mm/yy/hh24/mi/ss')||
1123 to_char(p_tc_record.day_stop_time,'dd/mm/yy/hh24/mi/ss')||
1124 p_tc_record.hours_measure||p_tc_record.detail_comments;
1125 RETURN time_detail;
1126 END time_details;
1127
1128
1129 -- INSERT_DETAILS
1130 -- Inserts all the audit records into HXC_RPT_TC_AUDIT.
1131
1132 PROCEDURE insert_details
1133 AS
1134
1135 BEGIN
1136
1137 -- Private Procedure insert_details
1138 -- Inserts the details collected into audit record pl/sql table, into
1139 -- HXC_RPT_TC_AUDIT.
1140
1141 IF g_debug
1142 THEN
1143 hr_utility.trace('Inserting details into hxc_rpt_tc_audit ');
1144
1145 -- Commenting the below code, which logs all the detail info to be
1146 -- recorded into HXC_RPT_TC_AUDIT. Not too much hit to performance,
1147 -- but uncomment and run only if you see that there is an issue in the way
1148 -- the details are getting framed. Else, this would just add on hundreds
1149 -- of lines to your log files, nothing more.
1150
1151 -- FOR i IN l_audit_details.FIRST..l_audit_details.LAST
1152 -- LOOP
1153 -- hr_utility.trace('Record No.'||i);
1154 -- hr_utility.trace('time_entry_date : '||l_audit_details(i).time_entry_date);
1155 -- hr_utility.trace('attribute_info : '||l_audit_details(i).attribute_info);
1156 -- hr_utility.trace('hours : '||l_audit_details(i).hours);
1157 -- hr_utility.trace('action : '||l_audit_details(i).action);
1158 -- hr_utility.trace('action_date : '||l_audit_details(i).action_date);
1159 -- hr_utility.trace('action_by : '||l_audit_details(i).action_by);
1160 -- hr_utility.trace('comments : '||l_audit_details(i).comments);
1161 -- hr_utility.trace('reasons : '||l_audit_details(i).reasons);
1162 -- hr_utility.trace('transaction_id : '||l_audit_details(i).transaction_id);
1163 -- hr_utility.trace('detail_bb_id : '||l_audit_details(i).detail_bb_id);
1164 -- hr_utility.trace('detail_bb_ovn : '||l_audit_details(i).detail_bb_ovn);
1165 -- hr_utility.trace('tc_bb_id : '||l_audit_details(i).tc_bb_id);
1166 -- hr_utility.trace('tc_bb_ovn : '||l_audit_details(i).tc_bb_ovn);
1167 -- hr_utility.trace('action_type : '||l_audit_details(i).action_type);
1168 -- hr_utility.trace('resource_id : '||l_audit_details(i).resource_id);
1169 -- hr_utility.trace('tc_start_time : '||l_audit_details(i).tc_start_time);
1170 -- hr_utility.trace('tc_stop_time : '||l_audit_details(i).tc_stop_time);
1171 -- hr_utility.trace('action_by_person : '||l_audit_details(i).action_by_person);
1172 -- hr_utility.trace('cla_reason : '||l_audit_details(i).cla_reason);
1173 -- hr_utility.trace('resource_name : '||l_audit_details(i).resource_name);
1174 -- END LOOP;
1175
1176 END IF;
1177
1178 FORALL i IN l_audit_details.FIRST..l_audit_details.LAST
1179 INSERT INTO hxc_rpt_tc_audit
1180 VALUES l_audit_details(i);
1181 COMMIT;
1182 END insert_details ;
1183
1184
1185 BEGIN -- record_details
1186
1187
1188 -- Private Procedure record_details
1189 -- Pick up all the detail records from HXC_RPT_TC_DETAILS_ALL for this timecard into
1190 -- pl/sql table of HXC_RPT_TC_DETAILS_ALL rowtype, ordered by detail bb id. If
1191 -- p_record_save is set to Y, need to pick up WORKING status records also. In that
1192 -- case work with get_all_details cursor, else with get_details.
1193 -- Loop thru these picked up details.
1194 -- * If the OVN is 1, then it is a New Entry, if its not a Late Entry.
1195 -- If CLA reason is NULL, then it is a New Entry, else its a Late Entry.
1196 -- If CLA reason is not NULL, but the entry was made well in time, its
1197 -- a Late Entry, just a New Entry.
1198 -- * If OVN is not 1, then check if there is another element of the same
1199 -- bb id before this element. -- If yes, then its a changed entry,
1200 -- if the date_to column is not end of time, make sure this is an edited entry
1201 -- so record as After Edit after comparing the attributes of this element and
1202 -- the previous element.
1203 -- If there is no element prior to this with the same bb id, then its again a New
1204 -- Entry.
1205 -- * Check the next entry in the table, if it is of the same bb id, then this is
1206 -- changed in the next transaction, so insert a record with Before Edit entry
1210 -- deleted in the next submission. So create an entry saying Deleted.
1207 -- after fetching the proper transaction id and after checking if the attributes
1208 -- have changed.
1209 -- * If the next entry with the same bb id is having an end date, then you know it is
1211 -- Once you have looped thru all the records, insert the details collected into HXC_RPT_
1212 -- TC_AUDIT.
1213
1214 -- Inline comments are put in for more clarity.
1215
1216 l_audit_details := audittable();
1217
1218 IF p_record_save = 'Y'
1219 THEN
1220 OPEN get_all_details(p_resource_id,
1221 p_start_time,
1222 p_stop_time );
1223
1224 FETCH get_all_details BULK
1225 COLLECT INTO l_tc_details ;
1226
1227 CLOSE get_all_details;
1228 ELSE
1229 OPEN get_details(p_resource_id,
1230 p_start_time,
1231 p_stop_time );
1232
1233 FETCH get_details BULK
1234 COLLECT INTO l_tc_details ;
1235
1236 CLOSE get_details;
1237 END IF;
1238
1239 IF g_debug
1240 THEN
1241 hr_utility.trace('Fetched details ');
1242 hr_utility.trace('Total Number of details fetched : '||l_tc_details.COUNT);
1243 END IF;
1244
1245
1246 IF l_tc_details.COUNT > 0
1247 THEN
1248
1249
1250 FOR i IN l_tc_details.FIRST..l_tc_details.LAST
1251 LOOP
1252 -- First of all, make sure if this entry is included in a Deletion
1253 -- ( A deletion can be a deletion from Recent timecards page or
1254 -- an Overwrite with template. An Overwritten timecard entries
1255 -- neednt be shown again, and need to be trimmed off. So check if
1256 -- this time entry belongs to a timecard previously recorded as
1257 -- Deleted )
1258 l_delete_done := FALSE;
1259 IF ( l_del_bb_id_tab.COUNT > 0
1260 AND l_tc_details(i).date_to <> hr_general.end_of_time )
1261 THEN
1262 FOR x IN l_del_bb_id_tab.FIRST..l_del_bb_id_tab.LAST
1263 LOOP
1264 IF ( l_del_bb_id_tab(x) = l_tc_details(i).tc_bb_id
1265 AND l_del_bb_ovn_tab(x) = l_tc_details(i).tc_bb_ovn
1266 AND l_del_id_tab(x) <> 0
1267 )
1268 THEN
1269 -- Found out its true, so mark the flag. We would use this flag before
1270 -- inserting.
1271 l_delete_done := TRUE;
1272 EXIT;
1273 END IF;
1274 END LOOP;
1275 END IF;
1276
1277 IF (l_tc_details(i).detail_bb_ovn = 1) OR
1278 ( (l_tc_details(i).detail_bb_ovn <> 1)
1279 AND l_tc_details.EXISTS(i-1) = FALSE ) OR
1280 ( l_tc_details.EXISTS(i-1) = TRUE
1281 AND l_tc_details(i-1).detail_bb_id <> l_tc_details(i).detail_bb_id)
1282
1283 THEN -- new record
1284 l_audit_details.EXTEND;
1285 l_audit_cnt := l_audit_cnt + 1 ;
1286 copy_timecard_to_audit(l_tc_details(i),
1287 l_audit_details(l_audit_cnt));
1288 IF ( l_tc_details(i).cla_type = 'LATE'
1289 AND TRUNC(l_tc_details(i).creation_date) >
1290 TRUNC(l_tc_details(i).day_start_time) )
1291 THEN
1292 l_audit_details(l_audit_cnt).action := 'Late Entry';
1293 ELSE
1294 l_audit_details(l_audit_cnt).action := 'New Entry';
1295 END IF;
1296 l_audit_details(l_audit_cnt).action_type := 'EN' ;
1297 ELSE -- if not new record
1298 IF l_tc_details(i).detail_bb_id = l_tc_details(i-1).detail_bb_id
1299 THEN
1300 temp1 := time_details(l_tc_details(i));
1301 temp2 := time_details(l_tc_details(i-1));
1302 IF temp1 <> temp2
1303 THEN
1304 l_audit_details.EXTEND(1);
1305 l_audit_cnt := l_audit_cnt + 1 ;
1306
1307 copy_timecard_to_audit(l_tc_details(i),
1308 l_audit_details(l_audit_cnt) );
1309 l_audit_details(l_audit_cnt).action := 'After Edit';
1310 l_audit_details(l_audit_cnt).action_type := 'EC' ;
1311 END IF; -- IF temp1 <> temp2
1312 END IF; -- IF l_tc_details(i-1).detail_bb_id = l_tc_details(i-1).detail_bb_id
1313 END IF; -- IF new record
1314
1315 IF l_tc_details(i).date_to <> hr_general.end_of_time
1316 THEN
1317 IF l_tc_details.EXISTS(i+1)
1318 THEN
1319 IF l_tc_details(i).detail_bb_id = l_tc_details(i+1).detail_bb_id
1320 THEN -- Changed Record
1321 temp1 := time_details(l_tc_details(i));
1322 temp2 := time_details(l_tc_details(i+1));
1323 IF temp1 <> temp2
1324 THEN -- Not just OVN, a real change
1325 l_audit_details.EXTEND(1);
1326 l_audit_cnt := l_audit_cnt + 1 ;
1327 copy_timecard_to_audit(l_tc_details(i),
1328 l_audit_details(l_audit_cnt));
1329 l_audit_details(l_audit_cnt).comments := l_tc_details(i).detail_comments;
1330 l_audit_details(l_audit_cnt).transaction_id := l_tc_details(i+1).transaction_id;
1331 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1332 l_audit_details(l_audit_cnt).action := 'Before Edit';
1333 l_audit_details(l_audit_cnt).action_type := 'EB' ;
1334 END IF; -- IF temp1 <> temp2
1335 ELSE -- its deleted (l_tc_details(i).detail_bb_id = l_tc_details(i+1).detail_bb_id
1336 -- is false )
1337 FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST
1338 LOOP
1339 IF (l_trans_date_tab(j) >= l_tc_details(i).date_to)
1340 AND (l_bb_id_tab(j) = l_tc_details(i).tc_bb_id)
1341 THEN
1342 l_audit_details.EXTEND(1);
1343 l_audit_cnt := l_audit_cnt + 1 ;
1344 copy_timecard_to_audit(l_tc_details(i),
1345 l_audit_details(l_audit_cnt));
1346 l_audit_details(l_audit_cnt).action_date := l_trans_date_tab(j);
1347 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1348 l_audit_details(l_audit_cnt).action := 'Deleted';
1349 l_audit_details(l_audit_cnt).action_type := 'ED' ;
1350 l_audit_details(l_audit_cnt).transaction_id :=
1351 l_trans_id_tab(j);
1352 EXIT;
1353 -- Is the entry deleted, but still the next transaction is of
1354 -- a different timecard id ? In that case, we only need to find
1355 -- out if this belongs to a Deleted Timecard. If not, we need
1356 -- to mark it as a delete for next transaction anyway.
1357 ELSIF (l_trans_date_tab(j) >= l_tc_details(i).date_to)
1358 AND NOT l_delete_done
1359 THEN
1360 l_audit_details.EXTEND(1);
1361 l_audit_cnt := l_audit_cnt + 1 ;
1362 copy_timecard_to_audit(l_tc_details(i),
1363 l_audit_details(l_audit_cnt));
1364 l_audit_details(l_audit_cnt).action_date := l_trans_date_tab(j);
1365 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1366 l_audit_details(l_audit_cnt).action := 'Deleted';
1367 l_audit_details(l_audit_cnt).action_type := 'ED' ;
1368 l_audit_details(l_audit_cnt).transaction_id :=
1369 l_trans_id_tab(j);
1370 EXIT;
1371 END IF; -- IF trans_date_tab = tc_details.date_to
1372 END LOOP; -- FOR j IN l_trans_id_tab
1373 END IF; -- IF tc_details(i).bb_id = tc_details(i+1).bb_id
1374 ELSE -- l_tc_details.EXISTS
1375 FOR j IN l_trans_date_tab.FIRST..l_trans_date_tab.LAST
1376 LOOP
1377 IF l_trans_date_tab(j) >= l_tc_details(i).date_to
1378 THEN
1379 l_audit_details.EXTEND(1);
1380 l_audit_cnt := l_audit_cnt + 1 ;
1381 copy_timecard_to_audit(l_tc_details(i),
1382 l_audit_details(l_audit_cnt));
1383 l_audit_details(l_audit_cnt).action := 'Deleted';
1384 l_audit_details(l_audit_cnt).action_type := 'ED' ;
1385 l_audit_details(l_audit_cnt).action_date := l_trans_date_tab(j);
1386 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1387
1388 l_audit_details(l_audit_cnt).transaction_id := l_trans_id_tab(j);
1389 EXIT;
1390 END IF;
1391 END LOOP;
1392 END IF; -- l_tc_details.EXISTS
1393 END IF; -- IF l_tc_details(i).date_to <> '31-DEC-4712'
1394 END LOOP; -- FOR i IN l_tc_details.FIRST..l_tc_details.LAST
1395
1396 insert_details;
1397
1398 END IF;
1399
1400 l_trans_id_tab.DELETE;
1401 l_trans_date_tab.DELETE;
1402 l_trans_user_tab.DELETE;
1403 l_comments_tab.DELETE;
1404 l_bb_id_tab.DELETE;
1405 l_bb_ovn_tab.DELETE;
1406 l_max_bb_id_tab.DELETE;
1407 l_trans_status_tab.DELETE;
1408
1409 l_del_bb_id_tab.DELETE;
1410 l_del_bb_ovn_tab.DELETE;
1411 l_del_date_tab.DELETE;
1412 l_del_id_tab.DELETE;
1413
1414
1415 l_tc_details.DELETE;
1416 l_audit_details.DELETE;
1417
1418
1419 END record_details ;
1420
1421
1422 BEGIN -- execute_audit_trail_reporting
1423
1424
1425 -- Public Procedure execute_audit_trail_reporting
1426 -- Take all the parameters and initiate request Load Timecard Snapshot
1427 -- passing all the parameters.
1428 -- While waiting for the request, translate all the parameters for display.
1429 -- Wait for the request to complete.
1430 -- Delete from HXC_RPT_TC_AUDIT, just in case last run crashed.
1431 -- Open get_timecards, passing in this request id, and fetch all the timecards.
1432 -- For each timecard,
1433 -- * Record the submissions.
1434 -- * Record approvals
1435 -- * Record deletions
1436 -- * Record details, and changes.
1437 -- Clear HXC_RPT_TC_AUDIT.
1438
1439
1440 IF g_debug
1441 THEN
1442 hr_utility.trace('execute_audit_trail_reporting');
1443 hr_utility.trace('Parameters ');
1444 hr_utility.trace('===========');
1445 hr_utility.trace('p_date_from '||p_date_from);
1446 hr_utility.trace('p_date_to '||p_date_to);
1447 hr_utility.trace('p_data_regen '||p_data_regen);
1448 hr_utility.trace('p_record_save '||p_record_save);
1449 hr_utility.trace('p_org_id '||p_org_id);
1450 hr_utility.trace('p_locn_id '||p_locn_id);
1451 hr_utility.trace('p_payroll_id '||p_payroll_id);
1452 hr_utility.trace('p_supervisor_id '||p_supervisor_id);
1453 hr_utility.trace('p_person_id '||p_person_id);
1454 END IF;
1455
1456
1457 -- Calling Load timecard snapshot passing the required parameters.
1458
1459 l_data_load_request_id := FND_REQUEST.SUBMIT_REQUEST ( application => 'HXC',
1460 program => 'HXCRPTTCSN',
1461 description => NULL,
1462 sub_request => FALSE,
1463 argument1 => p_date_from ,
1464 argument2 => p_date_to ,
1465 argument3 => p_data_regen ,
1466 argument4 => p_record_save ,
1467 argument5 => p_org_id ,
1468 argument6 => p_locn_id ,
1469 argument7 => p_payroll_id ,
1470 argument8 => p_supervisor_id ,
1471 argument9 => p_person_id );
1472 COMMIT;
1473
1474 IF l_data_load_request_id = 0
1475 THEN
1476 hr_utility.trace('There was an error in submitting Load Timecard Snapshot ');
1477 hr_utility.trace('Sql Error : '||SQLCODE);
1478 END IF;
1479
1480
1481 -- Translate_parameters is to translate the given parameters to the process
1482 -- for display to the user. While the request waits for Load Timecard Snapshot
1483 -- process, translate these parameters. After it is finished, come back and wait
1484 -- for Load timecard Snapshot.
1485
1486 translate_parameters;
1487
1488 IF g_debug
1489 THEN
1490 hr_utility.trace('Request went to wait at '||to_char(sysdate,'dd-MON-yyyy HH:MI:SS'));
1491 END IF;
1492
1493 l_call_status := FND_CONCURRENT.WAIT_FOR_REQUEST( request_id => l_data_load_request_id,
1494 interval => l_interval,
1495 max_wait => 0,
1496 phase => l_phase,
1497 status => l_status,
1498 dev_phase => l_dev_phase,
1499 dev_status => l_dev_status,
1500 message => l_message );
1501
1502 IF g_debug
1503 THEN
1504 hr_utility.trace('Request stopped at '||to_char(sysdate,'dd-MON-yyyy HH:MI:SS'));
1505 END IF;
1506
1507 IF l_call_status = FALSE
1508 THEN
1509 IF g_debug
1510 THEN
1511 hr_utility.trace('There was an error in executing Load Timecard Snapshot ');
1512 hr_utility.trace('Sql Error : '||SQLCODE);
1513 END IF;
1514 END IF;
1515
1516 -- Just in case the previous run crashed, clear the reporting table before you
1517 -- insert anything.
1518
1519 DELETE FROM hxc_rpt_tc_audit;
1520
1521 OPEN get_timecards(l_data_load_request_id);
1522 LOOP
1523 FETCH get_timecards INTO l_resource_id,
1524 l_start_time,
1525 l_stop_time ;
1526 EXIT WHEN get_timecards%NOTFOUND;
1527
1528 IF g_debug
1529 THEN
1530 hr_utility.trace('Fetched from get_timecards ');
1531 hr_utility.trace('Resource id : '||l_resource_id);
1532 hr_utility.trace('TC start time : '||l_start_time);
1533 hr_utility.trace('TC stop time : '||l_stop_time);
1534 END IF;
1535
1536 IF l_resource_count = 100
1537 THEN
1538 COMMIT;
1539 l_resource_count := 1;
1540 ELSE
1541 l_resource_count := l_resource_count + 1;
1542 END IF;
1543
1544 record_submissions(l_resource_id,
1545 l_start_time,
1546 l_stop_time,
1547 p_record_save);
1548
1549
1550 record_approvals (l_resource_id,
1551 l_start_time,
1552 l_stop_time );
1553
1554
1555 record_deletions(l_resource_id,
1556 l_start_time,
1557 l_stop_time );
1558
1559 record_details(l_resource_id,
1560 l_start_time,
1561 l_stop_time,
1562 p_record_save );
1563
1564 END LOOP;
1565 CLOSE get_timecards;
1566 COMMIT;
1567 IF g_debug
1568 THEN
1569 hr_utility.trace('Completed Audit Trail Reporting normally ');
1570 hr_utility.trace('Finished processing at '||TO_CHAR(SYSDATE,'dd-MON-yyyy HH:MI:SS'));
1571 END IF;
1572
1573 EXCEPTION
1574 WHEN NO_DATA_FOUND THEN
1575 hr_utility.trace('No Data Found from execute_audit_trail_reporting ');
1576
1577 END execute_audit_trail_reporting;
1578
1579
1580
1581
1582 END HXC_RPT_TC_AUDIT_TRAIL;
1583
[Home] [Help]