1 PACKAGE BODY HXC_RPT_TC_AUDIT_TRAIL AS
2 /* $Header: hxcrptaudittrail.pkb 120.10.12010000.7 2010/02/16 16:56:01 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 --
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
355 -- screen ), the rest of the timecard is submitted. So loop thru the submissions
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
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
492 AND transaction_detail_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' );
607 END IF;
608
609 IF g_debug
610 THEN
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) );
738 FETCH get_approvers INTO l_approver_name;
739 CLOSE get_approvers;
740 END IF;
741
742 l_appr_user_tab(i) := l_appr_user_tab(i)||newline
743 ||'['||l_approver_name||']';
744
745 -- Bug 9137834
746 -- Added the following IF condition to take care of
747 -- BLANK auto approved timecards.
748 IF l_trans_id_tab.COUNT > 0
749 THEN
750
751 FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST-1
752 LOOP
753
754 IF ( (l_trans_date_tab(j) <= l_appr_date_tab(i))
755 AND (l_trans_date_tab(j+1) > l_appr_date_tab(i)))
756 THEN
757 l_appr_trans_tab(i) := l_trans_id_tab(j);
758 l_appr_bb_tab(i) := l_bb_id_tab(j);
759 l_appr_resource_tab(i) := l_trans_name_tab(j);
760 EXIT;
761 END IF;
762 END LOOP;
763
764 IF l_appr_trans_tab(i) = 0
765 THEN
766 l_appr_trans_tab(i) := l_trans_id_tab(l_trans_id_tab.LAST);
767 l_appr_bb_tab(i) := l_bb_id_tab(l_bb_id_tab.LAST);
768 l_appr_resource_tab(i) := l_trans_name_tab(l_bb_id_tab.LAST);
769 END IF;
770
771 END IF;
772
773 END LOOP;
774
775 FORALL i IN l_appr_trans_tab.FIRST..l_appr_trans_tab.LAST
776 INSERT INTO hxc_rpt_tc_audit
777 ( resource_id,
778 tc_start_time,
779 tc_stop_time,
780 resource_name,
781 action,
782 action_date,
783 action_by,
784 comments,
785 transaction_id,
786 tc_bb_id,
787 action_type )
788 VALUES ( p_resource_id,
789 p_start_time,
790 p_stop_time,
791 l_appr_resource_tab(i),
792 l_appr_status_tab(i),
793 l_appr_date_tab(i),
794 l_appr_user_tab(i),
795 l_appr_comments_tab(i),
796 l_appr_trans_tab(i),
797 l_appr_bb_tab(i),
798 'TSA' );
799
800 l_appr_trans_tab.DELETE;
801 l_appr_bb_tab.DELETE;
802 l_appr_date_tab.DELETE;
803 l_appr_user_tab.DELETE;
804 l_appr_resource_tab.DELETE;
805 l_appr_status_tab.DELETE;
806 l_appr_comments_tab.DELETE;
807
808
809 END IF;
810
811 IF g_debug
812 THEN
813 hr_utility.trace('record_approvals completed alright ');
814 END IF;
815
816
817
818 END record_approvals ;
819
820
821
822
823 -- RECORD_DELETIONS
824 -- Queries against HXC_RPT_TC_DETAILS_ALL to find out all the timecard
825 -- deletions, and records these into HXC_RPT_TC_AUDIT.
826
827 PROCEDURE record_deletions ( p_resource_id IN NUMBER,
828 p_start_time IN DATE,
829 p_stop_time IN DATE )
830 AS
831
832 CURSOR get_deletions ( p_resource_id NUMBER,
833 p_start_time DATE,
834 p_stop_time DATE )
835 IS SELECT transaction_id,
836 MIN(creation_date),
837 MIN(created_by_user),
838 MIN(tc_comments),
839 MIN(tc_bb_id),
840 MIN(tc_bb_ovn),
841 MIN(resource_name)
842 FROM hxc_rpt_tc_details_all det
843 WHERE resource_id = p_resource_id
844 AND tc_start_time = p_start_time
845 AND tc_stop_time = p_stop_time
846 AND day_date_to <> hr_general.end_of_time
847 AND creation_date = date_to
848 AND transaction_id IS NOT NULL
849 AND status <> 'WORKING'
850 AND NOT EXISTS ( SELECT 1
851 FROM hxc_timecard_summary hxc
852 WHERE timecard_id = det.tc_bb_id
853 AND hxc.resource_id = det.resource_id
854 AND hxc.start_time = det.tc_start_time
855 )
856 GROUP BY transaction_id
857 ORDER BY MIN(creation_date) ;
858
859
860
861
862 l_del_comments_tab VARCHARTABLE;
863 l_del_user_tab VARCHARTABLE;
864 l_del_name_tab VARCHARTABLE;
865
866
867
868 BEGIN
869
870
871 -- Private Procedure record_deletions
872 -- Queries against HXC_RPT_TC_DETAILS_ALL table for all records
873 -- that have creation_date = deleted date for the day record
874 -- These indicate a timecard delete.
875 -- Insert these including the transaction_ids into HXC_RPT_TC_AUDIT.
876
877 IF g_debug
878 THEN
879 hr_utility.trace('record_deletions for '||p_resource_id
880 ||' from '||p_start_time
881 ||' to '||p_stop_time);
882 END IF;
883
884
885 OPEN get_deletions (p_resource_id,
886 p_start_time,
887 p_stop_time );
888 FETCH get_deletions BULK COLLECT INTO l_del_id_tab,
889 l_del_date_tab,
890 l_del_user_tab,
891 l_del_comments_tab,
892 l_del_bb_id_tab,
893 l_del_bb_ovn_tab,
894 l_del_name_tab ;
895 CLOSE get_deletions;
896
897 IF g_debug
898 THEN
899 hr_utility.trace('Fetched from get_deletions ');
900 hr_utility.trace('Total number of deletions : '||l_del_id_tab.COUNT);
901 END IF;
902
903
904 IF l_del_id_tab.COUNT > 0
905 THEN
906 FOR i IN l_del_id_tab.FIRST..l_del_id_tab.LAST
907 LOOP
908 FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST
909 LOOP
910 IF l_trans_id_tab(j) = l_del_id_tab(i)
911 THEN
912
913 IF l_max_bb_id_tab(j) = l_del_bb_id_tab(i)
914 THEN
915 l_del_id_tab(i) := 0;
916 EXIT;
917 END IF; -- l_max_bb_id_tab(j) = l_bb_id_tab(j)
918
919 IF l_trans_id_tab.EXISTS(j-1)
920 THEN
921 l_del_id_tab(i) := l_trans_id_tab(j-1) ;
922 ELSE
923 l_del_id_tab(i) := l_del_id_tab(i) - 1;
924 END IF; -- l_trans_id_tab.EXISTS(j-1)
925 EXIT; -- l_trans_id_tab.FIRST..l_trans_id_tab.LAST
926 END IF; -- l_trans_id_tab(j) = l_del_id_tab(i)
927 END LOOP; -- l_trans_id_tab.FIRST..l_trans_id_tab.LAST
928 END LOOP; -- l_del_id_tab.FIRST..l_del_id_tab.LAST
929
930
931
932 FOR i IN l_del_id_tab.FIRST..l_del_id_tab.LAST
933 LOOP
934 IF l_del_id_tab(i) <> 0
935 THEN
936 INSERT INTO hxc_rpt_tc_audit
937 ( resource_id,
938 tc_start_time,
939 tc_stop_time,
940 resource_name,
941 action,
942 action_date,
943 action_by,
944 comments,
945 transaction_id,
946 tc_bb_id,
947 tc_bb_ovn,
948 action_type )
949 VALUES ( p_resource_id,
950 p_start_time,
951 p_stop_time,
952 l_del_name_tab(i),
953 'Deleted Timecard',
954 l_del_date_tab(i),
955 l_del_user_tab(i),
956 l_del_comments_tab(i),
957 l_del_id_tab(i),
958 l_del_bb_id_tab(i),
959 l_del_bb_ovn_tab(i),
960 'TSD' );
961 END IF; -- l_del_id_tab(i) <> 0
962 END LOOP;
963
964 l_del_comments_tab.DELETE;
965 l_del_user_tab.DELETE;
966 l_del_name_tab.DELETE;
967
968 END IF;
969
970 IF g_debug
971 THEN
972 hr_utility.trace('record_deletions completed alright ');
973 END IF;
974
975
976 END record_deletions ;
977
978
979
980
981 -- RECORD_DETAILS
982 -- Queries against HXC_RPT_TC_DETAILS_ALL and records the detail records
983 -- in the required format.
984
985
986 PROCEDURE record_details ( p_resource_id IN NUMBER,
987 p_start_time IN DATE,
988 p_stop_time IN DATE,
989 p_record_save IN VARCHAR2 )
990 AS
991
992 l_audit_cnt NUMBER := 0;
993 temp1 VARCHAR2(500);
994 temp2 VARCHAR2(500);
995
996 l_tc_details TIMEDETAILSTABLE;
997 l_audit_details AUDITTABLE ;
998
999 l_delete_done BOOLEAN;
1000
1001
1002 -- The following two queries have been given conditions for a range
1003 -- for a date check. Creation_date and day_date_to logically could be
1004 -- the same value, but for big timecards you would see a one or 2 secs delay.
1005 -- Meaning, we check for this condition in a range, rather than an inequality.
1006 --
1007 CURSOR get_details ( p_resource_id NUMBER,
1008 p_start_time DATE,
1009 p_stop_time DATE )
1010 IS SELECT *
1011 FROM hxc_rpt_tc_details_all
1012 WHERE resource_id = p_resource_id
1013 AND tc_start_time = p_start_time
1014 AND tc_stop_time = p_stop_time
1015 AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
1016 AND (day_date_to + (2/(24*60*60)))
1017 AND transaction_id IS NOT NULL
1018 AND transaction_detail_id IS NOT NULL
1019 AND status <> 'WORKING'
1020 ORDER BY detail_bb_id,
1021 detail_bb_ovn ;
1022
1023
1024 CURSOR get_all_details ( p_resource_id NUMBER,
1025 p_start_time DATE,
1026 p_stop_time DATE )
1027 IS SELECT *
1028 FROM hxc_rpt_tc_details_all
1029 WHERE resource_id = p_resource_id
1030 AND tc_start_time = p_start_time
1031 AND tc_stop_time = p_stop_time
1032 AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
1033 AND (day_date_to + (2/(24*60*60)))
1034 AND transaction_id IS NOT NULL
1035 ORDER BY detail_bb_id,
1036 detail_bb_ovn ;
1037
1038
1039 -- COPY_TIMECARD_TO_AUDIT
1040 -- Copies the timecard record from HXC_RPT_TC_DETAILS_ALL to an
1041 -- audit record format, like the record structure in HXC_RPT_TC_AUDIT.
1042
1043 PROCEDURE copy_timecard_to_audit (p_tc_record IN hxc_rpt_tc_details_all%ROWTYPE,
1044 p_audit_record IN OUT NOCOPY hxc_rpt_tc_audit%ROWTYPE)
1045 AS
1046 BEGIN
1047
1048 -- Private Procedure copy_timecard_to_audit
1049 -- Copies timecard style record from HXC_RPT_TC_DETAILS_ALL
1050 -- to audit type after putting in necessary formatting, like
1051 -- concatenating all attributes, with a Line Feed as separator.
1052
1053 p_audit_record.detail_bb_id := p_tc_record.detail_bb_id;
1054 p_audit_record.detail_bb_ovn := p_tc_record.detail_bb_ovn;
1055 p_audit_record.time_entry_date := p_tc_record.day_start_time;
1056 p_audit_record.attribute_info :=
1057 p_tc_record.attribute1||newline||p_tc_record.attribute2||newline||
1058 p_tc_record.attribute3||newline||p_tc_record.attribute4||newline||
1059 p_tc_record.attribute5||newline||p_tc_record.attribute6||newline||
1060 p_tc_record.attribute7||newline||p_tc_record.attribute8||newline||
1061 p_tc_record.attribute9||newline||p_tc_record.attribute10||newline||
1062 p_tc_record.attribute11||newline||p_tc_record.attribute12||newline||
1063 p_tc_record.attribute13||newline||p_tc_record.attribute14||newline||
1064 p_tc_record.attribute15||newline||p_tc_record.attribute16||newline||
1065 p_tc_record.attribute17||newline||p_tc_record.attribute18||newline||
1066 p_tc_record.attribute19||newline||p_tc_record.attribute20||newline||
1067 p_tc_record.attribute21||newline||p_tc_record.attribute22||newline||
1068 p_tc_record.attribute23||newline||p_tc_record.attribute24||newline||
1069 p_tc_record.attribute25||newline||p_tc_record.attribute26||newline||
1070 p_tc_record.attribute27||newline||p_tc_record.attribute28||newline||
1071 p_tc_record.attribute29||newline||p_tc_record.attribute30;
1072
1073 p_audit_record.attribute_info := LTRIM(RTRIM(p_audit_record.attribute_info,newline),newline) ;
1074 p_audit_record.hours := p_tc_record.hours_measure;
1075 p_audit_record.action_date := p_tc_record.creation_date;
1076 p_audit_record.action_by := p_tc_record.created_by_user;
1077 p_audit_record.comments := p_tc_record.detail_comments;
1078 p_audit_record.transaction_id := p_tc_record.transaction_id;
1079 p_audit_record.resource_id := p_tc_record.resource_id;
1080 p_audit_record.resource_name := p_tc_record.resource_name;
1081 p_audit_record.tc_start_time := p_tc_record.tc_start_time;
1082 p_audit_record.tc_stop_time := p_tc_record.tc_stop_time;
1083 p_audit_record.cla_reason :=
1084 LTRIM(RTRIM(p_tc_record.cla_reason||'-'||
1085 p_tc_record.cla_comments,'-'),'-');
1086
1087 -- Code below added to include start stop times in the report.
1088 -- TO_CHAR(date,'HHMISSAM') will print the time in the following
1089 -- format.
1090 -- 115959PM
1091 -- If the start, stop times are not given, the default time
1092 -- of <date> 11:59:59 PM would be the column value.
1093 -- Just check this up. If its not the default time, record it
1094 -- as start stop time.
1095
1096
1097 IF TO_CHAR(p_tc_record.day_stop_time,'HHMISSAM') <> '115959PM'
1098 THEN
1099 p_audit_record.start_stop_time := to_char(p_tc_record.day_start_time,'HH:MI AM')||newline||
1100 to_char(p_tc_record.day_stop_time,'HH:MI AM');
1101 END IF;
1102
1103
1104
1105 END copy_timecard_to_audit ;
1106
1107 -- TIME_DETAILS
1108 -- For a given timecard record, from HXC_RPT_TC_DETAILS_ALL, returns a
1109 -- concatenated string of attributes, hours and comments.
1110
1111 FUNCTION time_details( p_tc_record IN hxc_rpt_tc_details_all%ROWTYPE )
1112 RETURN VARCHAR2
1113 AS
1114 time_detail VARCHAR2(4000);
1115
1116 BEGIN -- time_details
1117
1118 -- Private function time_details
1119 -- Concatenates the attributes, hours and detail comments and returns these as a
1120 -- string.
1121 time_detail := p_tc_record.attribute1||p_tc_record.attribute2||p_tc_record.attribute3||
1122 p_tc_record.attribute4||p_tc_record.attribute5||p_tc_record.attribute6||
1123 p_tc_record.attribute7||p_tc_record.attribute8||p_tc_record.attribute9||
1124 p_tc_record.attribute10||p_tc_record.attribute11||p_tc_record.attribute12||
1125 p_tc_record.attribute13||p_tc_record.attribute14||p_tc_record.attribute15||
1126 p_tc_record.attribute16||p_tc_record.attribute17||p_tc_record.attribute18||
1127 p_tc_record.attribute19||p_tc_record.attribute20||p_tc_record.attribute21||
1128 p_tc_record.attribute22||p_tc_record.attribute23||p_tc_record.attribute24||
1129 p_tc_record.attribute25||p_tc_record.attribute26||p_tc_record.attribute27||
1130 p_tc_record.attribute28||p_tc_record.attribute29||p_tc_record.attribute30||
1131 to_char(p_tc_record.day_start_time,'dd/mm/yy/hh24/mi/ss')||
1132 to_char(p_tc_record.day_stop_time,'dd/mm/yy/hh24/mi/ss')||
1133 p_tc_record.hours_measure||p_tc_record.detail_comments;
1134 RETURN time_detail;
1135 END time_details;
1136
1137
1138 -- INSERT_DETAILS
1139 -- Inserts all the audit records into HXC_RPT_TC_AUDIT.
1140
1141 PROCEDURE insert_details
1142 AS
1143
1144 BEGIN
1145
1146 -- Private Procedure insert_details
1147 -- Inserts the details collected into audit record pl/sql table, into
1148 -- HXC_RPT_TC_AUDIT.
1149
1150 IF g_debug
1151 THEN
1152 hr_utility.trace('Inserting details into hxc_rpt_tc_audit ');
1153
1154 -- Commenting the below code, which logs all the detail info to be
1155 -- recorded into HXC_RPT_TC_AUDIT. Not too much hit to performance,
1156 -- but uncomment and run only if you see that there is an issue in the way
1157 -- the details are getting framed. Else, this would just add on hundreds
1158 -- of lines to your log files, nothing more.
1159
1160 -- FOR i IN l_audit_details.FIRST..l_audit_details.LAST
1161 -- LOOP
1162 -- hr_utility.trace('Record No.'||i);
1163 -- hr_utility.trace('time_entry_date : '||l_audit_details(i).time_entry_date);
1164 -- hr_utility.trace('attribute_info : '||l_audit_details(i).attribute_info);
1165 -- hr_utility.trace('hours : '||l_audit_details(i).hours);
1166 -- hr_utility.trace('action : '||l_audit_details(i).action);
1167 -- hr_utility.trace('action_date : '||l_audit_details(i).action_date);
1168 -- hr_utility.trace('action_by : '||l_audit_details(i).action_by);
1169 -- hr_utility.trace('comments : '||l_audit_details(i).comments);
1170 -- hr_utility.trace('reasons : '||l_audit_details(i).reasons);
1171 -- hr_utility.trace('transaction_id : '||l_audit_details(i).transaction_id);
1172 -- hr_utility.trace('detail_bb_id : '||l_audit_details(i).detail_bb_id);
1173 -- hr_utility.trace('detail_bb_ovn : '||l_audit_details(i).detail_bb_ovn);
1174 -- hr_utility.trace('tc_bb_id : '||l_audit_details(i).tc_bb_id);
1175 -- hr_utility.trace('tc_bb_ovn : '||l_audit_details(i).tc_bb_ovn);
1176 -- hr_utility.trace('action_type : '||l_audit_details(i).action_type);
1177 -- hr_utility.trace('resource_id : '||l_audit_details(i).resource_id);
1178 -- hr_utility.trace('tc_start_time : '||l_audit_details(i).tc_start_time);
1179 -- hr_utility.trace('tc_stop_time : '||l_audit_details(i).tc_stop_time);
1180 -- hr_utility.trace('action_by_person : '||l_audit_details(i).action_by_person);
1181 -- hr_utility.trace('cla_reason : '||l_audit_details(i).cla_reason);
1182 -- hr_utility.trace('resource_name : '||l_audit_details(i).resource_name);
1183 -- END LOOP;
1184
1185 END IF;
1186
1187 FORALL i IN l_audit_details.FIRST..l_audit_details.LAST
1188 INSERT INTO hxc_rpt_tc_audit
1189 VALUES l_audit_details(i);
1190 COMMIT;
1191 END insert_details ;
1192
1193
1194 BEGIN -- record_details
1195
1196
1197 -- Private Procedure record_details
1198 -- Pick up all the detail records from HXC_RPT_TC_DETAILS_ALL for this timecard into
1199 -- pl/sql table of HXC_RPT_TC_DETAILS_ALL rowtype, ordered by detail bb id. If
1200 -- p_record_save is set to Y, need to pick up WORKING status records also. In that
1201 -- case work with get_all_details cursor, else with get_details.
1202 -- Loop thru these picked up details.
1203 -- * If the OVN is 1, then it is a New Entry, if its not a Late Entry.
1204 -- If CLA reason is NULL, then it is a New Entry, else its a Late Entry.
1205 -- If CLA reason is not NULL, but the entry was made well in time, its
1206 -- a Late Entry, just a New Entry.
1207 -- * If OVN is not 1, then check if there is another element of the same
1208 -- bb id before this element. -- If yes, then its a changed entry,
1209 -- if the date_to column is not end of time, make sure this is an edited entry
1210 -- so record as After Edit after comparing the attributes of this element and
1211 -- the previous element.
1212 -- If there is no element prior to this with the same bb id, then its again a New
1213 -- Entry.
1214 -- * Check the next entry in the table, if it is of the same bb id, then this is
1215 -- changed in the next transaction, so insert a record with Before Edit entry
1216 -- after fetching the proper transaction id and after checking if the attributes
1217 -- have changed.
1218 -- * If the next entry with the same bb id is having an end date, then you know it is
1219 -- deleted in the next submission. So create an entry saying Deleted.
1220 -- Once you have looped thru all the records, insert the details collected into HXC_RPT_
1221 -- TC_AUDIT.
1222
1223 -- Inline comments are put in for more clarity.
1224
1225 l_audit_details := audittable();
1226
1227 IF p_record_save = 'Y'
1228 THEN
1229 OPEN get_all_details(p_resource_id,
1230 p_start_time,
1231 p_stop_time );
1232
1233 FETCH get_all_details BULK
1234 COLLECT INTO l_tc_details ;
1235
1236 CLOSE get_all_details;
1237 ELSE
1238 OPEN get_details(p_resource_id,
1239 p_start_time,
1240 p_stop_time );
1241
1242 FETCH get_details BULK
1243 COLLECT INTO l_tc_details ;
1244
1245 CLOSE get_details;
1246 END IF;
1247
1248 IF g_debug
1249 THEN
1250 hr_utility.trace('Fetched details ');
1251 hr_utility.trace('Total Number of details fetched : '||l_tc_details.COUNT);
1252 END IF;
1253
1254
1255 IF l_tc_details.COUNT > 0
1256 THEN
1257
1258
1259 FOR i IN l_tc_details.FIRST..l_tc_details.LAST
1260 LOOP
1261 -- First of all, make sure if this entry is included in a Deletion
1262 -- ( A deletion can be a deletion from Recent timecards page or
1263 -- an Overwrite with template. An Overwritten timecard entries
1264 -- neednt be shown again, and need to be trimmed off. So check if
1265 -- this time entry belongs to a timecard previously recorded as
1266 -- Deleted )
1267 l_delete_done := FALSE;
1268 IF ( l_del_bb_id_tab.COUNT > 0
1269 AND l_tc_details(i).date_to <> hr_general.end_of_time )
1270 THEN
1271 FOR x IN l_del_bb_id_tab.FIRST..l_del_bb_id_tab.LAST
1272 LOOP
1273 IF ( l_del_bb_id_tab(x) = l_tc_details(i).tc_bb_id
1274 AND l_del_bb_ovn_tab(x) = l_tc_details(i).tc_bb_ovn
1275 AND l_del_id_tab(x) <> 0
1276 )
1277 THEN
1278 -- Found out its true, so mark the flag. We would use this flag before
1279 -- inserting.
1280 l_delete_done := TRUE;
1281 EXIT;
1282 END IF;
1283 END LOOP;
1284 END IF;
1285
1286 IF (l_tc_details(i).detail_bb_ovn = 1) OR
1287 ( (l_tc_details(i).detail_bb_ovn <> 1)
1288 AND l_tc_details.EXISTS(i-1) = FALSE ) OR
1289 ( l_tc_details.EXISTS(i-1) = TRUE
1290 AND l_tc_details(i-1).detail_bb_id <> l_tc_details(i).detail_bb_id)
1291
1292 THEN -- new record
1293 l_audit_details.EXTEND;
1294 l_audit_cnt := l_audit_cnt + 1 ;
1295 copy_timecard_to_audit(l_tc_details(i),
1296 l_audit_details(l_audit_cnt));
1297 IF ( l_tc_details(i).cla_type = 'LATE'
1298 AND TRUNC(l_tc_details(i).creation_date) >
1299 TRUNC(l_tc_details(i).day_start_time) )
1300 THEN
1301 l_audit_details(l_audit_cnt).action := 'Late Entry';
1302 ELSE
1303 l_audit_details(l_audit_cnt).action := 'New Entry';
1304 END IF;
1305 l_audit_details(l_audit_cnt).action_type := 'EN' ;
1306 ELSE -- if not new record
1307 IF l_tc_details(i).detail_bb_id = l_tc_details(i-1).detail_bb_id
1308 THEN
1309 temp1 := time_details(l_tc_details(i));
1310 temp2 := time_details(l_tc_details(i-1));
1311 IF temp1 <> temp2
1312 THEN
1313 l_audit_details.EXTEND(1);
1314 l_audit_cnt := l_audit_cnt + 1 ;
1315
1316 copy_timecard_to_audit(l_tc_details(i),
1317 l_audit_details(l_audit_cnt) );
1318 l_audit_details(l_audit_cnt).action := 'After Edit';
1319 l_audit_details(l_audit_cnt).action_type := 'EC' ;
1320 END IF; -- IF temp1 <> temp2
1321 END IF; -- IF l_tc_details(i-1).detail_bb_id = l_tc_details(i-1).detail_bb_id
1322 END IF; -- IF new record
1323
1324 IF l_tc_details(i).date_to <> hr_general.end_of_time
1325 THEN
1326 IF l_tc_details.EXISTS(i+1)
1327 THEN
1328 IF l_tc_details(i).detail_bb_id = l_tc_details(i+1).detail_bb_id
1329 THEN -- Changed Record
1330 temp1 := time_details(l_tc_details(i));
1331 temp2 := time_details(l_tc_details(i+1));
1332 IF temp1 <> temp2
1333 THEN -- Not just OVN, a real change
1334 l_audit_details.EXTEND(1);
1335 l_audit_cnt := l_audit_cnt + 1 ;
1336 copy_timecard_to_audit(l_tc_details(i),
1337 l_audit_details(l_audit_cnt));
1338 l_audit_details(l_audit_cnt).comments := l_tc_details(i).detail_comments;
1339 l_audit_details(l_audit_cnt).transaction_id := l_tc_details(i+1).transaction_id;
1340 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1341 l_audit_details(l_audit_cnt).action := 'Before Edit';
1342 l_audit_details(l_audit_cnt).action_type := 'EB' ;
1343 END IF; -- IF temp1 <> temp2
1344 ELSE -- its deleted (l_tc_details(i).detail_bb_id = l_tc_details(i+1).detail_bb_id
1345 -- is false )
1346 FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST
1347 LOOP
1348 IF (l_trans_date_tab(j) >= l_tc_details(i).date_to)
1349 AND (l_bb_id_tab(j) = l_tc_details(i).tc_bb_id)
1350 THEN
1351 l_audit_details.EXTEND(1);
1352 l_audit_cnt := l_audit_cnt + 1 ;
1353 copy_timecard_to_audit(l_tc_details(i),
1354 l_audit_details(l_audit_cnt));
1355 l_audit_details(l_audit_cnt).action_date := l_trans_date_tab(j);
1356 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1357 l_audit_details(l_audit_cnt).action := 'Deleted';
1358 l_audit_details(l_audit_cnt).action_type := 'ED' ;
1359 l_audit_details(l_audit_cnt).transaction_id :=
1360 l_trans_id_tab(j);
1361 EXIT;
1362 -- Is the entry deleted, but still the next transaction is of
1363 -- a different timecard id ? In that case, we only need to find
1364 -- out if this belongs to a Deleted Timecard. If not, we need
1365 -- to mark it as a delete for next transaction anyway.
1366 ELSIF (l_trans_date_tab(j) >= l_tc_details(i).date_to)
1367 AND NOT l_delete_done
1368 THEN
1369 l_audit_details.EXTEND(1);
1370 l_audit_cnt := l_audit_cnt + 1 ;
1371 copy_timecard_to_audit(l_tc_details(i),
1372 l_audit_details(l_audit_cnt));
1373 l_audit_details(l_audit_cnt).action_date := l_trans_date_tab(j);
1374 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1375 l_audit_details(l_audit_cnt).action := 'Deleted';
1376 l_audit_details(l_audit_cnt).action_type := 'ED' ;
1377 l_audit_details(l_audit_cnt).transaction_id :=
1378 l_trans_id_tab(j);
1379 EXIT;
1380 END IF; -- IF trans_date_tab = tc_details.date_to
1381 END LOOP; -- FOR j IN l_trans_id_tab
1382 END IF; -- IF tc_details(i).bb_id = tc_details(i+1).bb_id
1383 ELSE -- l_tc_details.EXISTS
1384 FOR j IN l_trans_date_tab.FIRST..l_trans_date_tab.LAST
1385 LOOP
1386 IF l_trans_date_tab(j) >= l_tc_details(i).date_to
1387 THEN
1388 l_audit_details.EXTEND(1);
1389 l_audit_cnt := l_audit_cnt + 1 ;
1390 copy_timecard_to_audit(l_tc_details(i),
1391 l_audit_details(l_audit_cnt));
1392 l_audit_details(l_audit_cnt).action := 'Deleted';
1393 l_audit_details(l_audit_cnt).action_type := 'ED' ;
1394 l_audit_details(l_audit_cnt).action_date := l_trans_date_tab(j);
1395 l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
1396
1397 l_audit_details(l_audit_cnt).transaction_id := l_trans_id_tab(j);
1398 EXIT;
1399 END IF;
1400 END LOOP;
1401 END IF; -- l_tc_details.EXISTS
1402 END IF; -- IF l_tc_details(i).date_to <> '31-DEC-4712'
1403 END LOOP; -- FOR i IN l_tc_details.FIRST..l_tc_details.LAST
1404
1405 insert_details;
1406
1407 END IF;
1408
1409 l_trans_id_tab.DELETE;
1410 l_trans_date_tab.DELETE;
1411 l_trans_user_tab.DELETE;
1412 l_comments_tab.DELETE;
1413 l_bb_id_tab.DELETE;
1414 l_bb_ovn_tab.DELETE;
1415 l_max_bb_id_tab.DELETE;
1416 l_trans_status_tab.DELETE;
1417
1418 l_del_bb_id_tab.DELETE;
1419 l_del_bb_ovn_tab.DELETE;
1420 l_del_date_tab.DELETE;
1421 l_del_id_tab.DELETE;
1422
1423
1424 l_tc_details.DELETE;
1425 l_audit_details.DELETE;
1426
1427
1428 END record_details ;
1429
1430
1431 BEGIN -- execute_audit_trail_reporting
1432
1433
1434 -- Public Procedure execute_audit_trail_reporting
1435 -- Take all the parameters and initiate request Load Timecard Snapshot
1436 -- passing all the parameters.
1437 -- While waiting for the request, translate all the parameters for display.
1438 -- Wait for the request to complete.
1439 -- Delete from HXC_RPT_TC_AUDIT, just in case last run crashed.
1440 -- Open get_timecards, passing in this request id, and fetch all the timecards.
1441 -- For each timecard,
1442 -- * Record the submissions.
1443 -- * Record approvals
1444 -- * Record deletions
1445 -- * Record details, and changes.
1446 -- Clear HXC_RPT_TC_AUDIT.
1447
1448
1449 IF g_debug
1450 THEN
1451 hr_utility.trace('execute_audit_trail_reporting');
1452 hr_utility.trace('Parameters ');
1453 hr_utility.trace('===========');
1454 hr_utility.trace('p_date_from '||p_date_from);
1455 hr_utility.trace('p_date_to '||p_date_to);
1456 hr_utility.trace('p_data_regen '||p_data_regen);
1457 hr_utility.trace('p_record_save '||p_record_save);
1458 hr_utility.trace('p_org_id '||p_org_id);
1459 hr_utility.trace('p_locn_id '||p_locn_id);
1460 hr_utility.trace('p_payroll_id '||p_payroll_id);
1461 hr_utility.trace('p_supervisor_id '||p_supervisor_id);
1462 hr_utility.trace('p_person_id '||p_person_id);
1463 END IF;
1464
1465
1466 -- Calling Load timecard snapshot passing the required parameters.
1467
1468 l_data_load_request_id := FND_REQUEST.SUBMIT_REQUEST ( application => 'HXC',
1469 program => 'HXCRPTTCSN',
1470 description => NULL,
1471 sub_request => FALSE,
1472 argument1 => p_date_from ,
1473 argument2 => p_date_to ,
1474 argument3 => p_data_regen ,
1475 argument4 => p_record_save ,
1476 argument5 => p_org_id ,
1477 argument6 => p_locn_id ,
1478 argument7 => p_payroll_id ,
1479 argument8 => p_supervisor_id ,
1480 argument9 => p_person_id );
1481 COMMIT;
1482
1483 IF l_data_load_request_id = 0
1484 THEN
1485 hr_utility.trace('There was an error in submitting Load Timecard Snapshot ');
1486 hr_utility.trace('Sql Error : '||SQLCODE);
1487 END IF;
1488
1489
1490 -- Translate_parameters is to translate the given parameters to the process
1491 -- for display to the user. While the request waits for Load Timecard Snapshot
1492 -- process, translate these parameters. After it is finished, come back and wait
1493 -- for Load timecard Snapshot.
1494
1495 translate_parameters;
1496
1497 IF g_debug
1498 THEN
1499 hr_utility.trace('Request went to wait at '||to_char(sysdate,'dd-MON-yyyy HH:MI:SS'));
1500 END IF;
1501
1502 l_call_status := FND_CONCURRENT.WAIT_FOR_REQUEST( request_id => l_data_load_request_id,
1503 interval => l_interval,
1504 max_wait => 0,
1505 phase => l_phase,
1506 status => l_status,
1507 dev_phase => l_dev_phase,
1508 dev_status => l_dev_status,
1509 message => l_message );
1510
1511 IF g_debug
1512 THEN
1513 hr_utility.trace('Request stopped at '||to_char(sysdate,'dd-MON-yyyy HH:MI:SS'));
1514 END IF;
1515
1516 IF l_call_status = FALSE
1517 THEN
1518 IF g_debug
1519 THEN
1520 hr_utility.trace('There was an error in executing Load Timecard Snapshot ');
1521 hr_utility.trace('Sql Error : '||SQLCODE);
1522 END IF;
1523 END IF;
1524
1525 -- Just in case the previous run crashed, clear the reporting table before you
1526 -- insert anything.
1527
1528 DELETE FROM hxc_rpt_tc_audit;
1529
1530 OPEN get_timecards(l_data_load_request_id);
1531 LOOP
1532 FETCH get_timecards INTO l_resource_id,
1533 l_start_time,
1534 l_stop_time ;
1535 EXIT WHEN get_timecards%NOTFOUND;
1536
1537 IF g_debug
1538 THEN
1539 hr_utility.trace('Fetched from get_timecards ');
1540 hr_utility.trace('Resource id : '||l_resource_id);
1541 hr_utility.trace('TC start time : '||l_start_time);
1542 hr_utility.trace('TC stop time : '||l_stop_time);
1543 END IF;
1544
1545 IF l_resource_count = 100
1546 THEN
1547 COMMIT;
1548 l_resource_count := 1;
1549 ELSE
1550 l_resource_count := l_resource_count + 1;
1551 END IF;
1552
1553 -- Bug 9137834
1554 -- Added the exception block
1555 BEGIN
1556 record_submissions(l_resource_id,
1557 l_start_time,
1558 l_stop_time,
1559 p_record_save);
1560
1561
1562 record_approvals (l_resource_id,
1563 l_start_time,
1564 l_stop_time );
1565
1566
1567 record_deletions(l_resource_id,
1568 l_start_time,
1569 l_stop_time );
1570
1571 record_details(l_resource_id,
1572 l_start_time,
1573 l_stop_time,
1574 p_record_save );
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 hr_utility.trace('Error stack ');
1578 hr_utility.trace(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
1579 hr_utility.trace(' Exception '||SQLERRM||' while processing the following timecard ');
1580 hr_utility.trace(' Resource_id '||l_resource_id);
1581 hr_utility.trace(' Start_time '||l_start_time);
1582 hr_utility.trace(' Stop_time '||l_stop_time);
1583
1584 END;
1585
1586 END LOOP;
1587 CLOSE get_timecards;
1588 COMMIT;
1589 IF g_debug
1590 THEN
1591 hr_utility.trace('Completed Audit Trail Reporting normally ');
1592 hr_utility.trace('Finished processing at '||TO_CHAR(SYSDATE,'dd-MON-yyyy HH:MI:SS'));
1593 END IF;
1594
1595 EXCEPTION
1596 WHEN NO_DATA_FOUND THEN
1597 hr_utility.trace('No Data Found from execute_audit_trail_reporting ');
1598
1599 END execute_audit_trail_reporting;
1600
1601
1602
1603
1604 END HXC_RPT_TC_AUDIT_TRAIL;
1605
[Home] [Help]