[Home] [Help]
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:
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
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,
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 )
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
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,
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
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
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 ');
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
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:
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:
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
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
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,
1578:
1579:
1580:
1581:
1582: END HXC_RPT_TC_AUDIT_TRAIL;
1583: