[Home] [Help]
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:
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;
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;
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 ,
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
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
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,
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
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,
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 )
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
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,
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
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
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 ');
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
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:
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:
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
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
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,
1600:
1601:
1602:
1603:
1604: END HXC_RPT_TC_AUDIT_TRAIL;
1605: