[Home] [Help]
250:
251:
252: -- CLEAR_HISTORY_DATA
253: -- If chosen to regenerate all data and clear history data, deletes all information
254: -- already collected from HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
255:
256: PROCEDURE clear_history_data
257: AS
258:
258:
259: BEGIN
260:
261: -- Public Procedure clear_history_data
262: -- Delete from HXC_RPT_TC_HIST_LOG
263: -- Delete from HXC_RPT_TC_DETAILS_ALL
264: -- Commit the changes.
265:
266: IF g_debug
267: THEN
268: hr_utility.trace('Started clear_history_data');
269: END IF;
270:
271: DELETE FROM hxc_rpt_tc_hist_log;
272:
273: DELETE FROM hxc_rpt_tc_details_all;
274:
275: COMMIT;
303:
304: CURSOR get_history_date
305: IS SELECT /*+ LEADING(gt)
306: USE_NL(gt hist)
307: INDEX(hist HXC_RPT_TC_HIST_LOG_PK) */
308: hist.resource_id,
309: hist.tc_start_time,
310: hist.tc_stop_time,
311: hist.history_till_date
308: hist.resource_id,
309: hist.tc_start_time,
310: hist.tc_stop_time,
311: hist.history_till_date
312: FROM hxc_rpt_tc_hist_log hist,
313: hxc_rpt_tc_resource_temp gt
314: WHERE gt.resource_id = hist.resource_id
315: AND gt.tc_start_time = hist.tc_start_time
316: AND gt.tc_stop_time = hist.tc_stop_time ;
317:
318: BEGIN
319:
320: -- Public Procedure fetch_history_from_date
321: -- Look into HXC_RPT_TC_HIST_LOG to find out upto which details are
322: -- captured into HXC_RPT_TC_DETAILS_ALL for all the
323: -- resource_id-tc_start_time-tc_stop_time combinations loaded
324: -- right now into HXC_RPT_TC_RESOURCE_TEMP.
325: -- Update these values as history_from_date in HXC_RPT_TC_RESOURCE_TEMP
324: -- right now into HXC_RPT_TC_RESOURCE_TEMP.
325: -- Update these values as history_from_date in HXC_RPT_TC_RESOURCE_TEMP
326: -- for corresponding resource_id-tc_start_time-tc_stop_time
327: -- combinations.
328: -- For those combinations which there is no record in HXC_RPT_TC_HIST_LOG
329: -- update history_from_date as hr_general.start_of_time
330:
331:
332: IF g_debug
2239:
2240:
2241:
2242: -- LOG_TIME_CAPTURE
2243: -- Makes an entry in HXC_RPT_TC_HIST_LOG with the timecard identification
2244: -- parameters, for future reference.
2245:
2246: PROCEDURE log_time_capture ( p_request_id IN VARCHAR2,
2247: p_request_sysdate IN DATE )
2253:
2254: BEGIN
2255:
2256: -- Public Procedure log_time_capture
2257: -- If the timecard is already recorded in HXC_RPT_TC_HIST_LOG, update the
2258: -- history_till_date column with the request date.
2259: -- Delete from HXC_RPT_TC_RESOURCE_TEMP all records which are updated as above.
2260: -- For all the distinct timecard records existing in HXC_RPT_TC_RESOURCE_TEMP,
2261: -- insert a relevant record into HXC_RPT_TC_HIST_LOG.
2257: -- If the timecard is already recorded in HXC_RPT_TC_HIST_LOG, update the
2258: -- history_till_date column with the request date.
2259: -- Delete from HXC_RPT_TC_RESOURCE_TEMP all records which are updated as above.
2260: -- For all the distinct timecard records existing in HXC_RPT_TC_RESOURCE_TEMP,
2261: -- insert a relevant record into HXC_RPT_TC_HIST_LOG.
2262:
2263: IF g_debug
2264: THEN
2265: hr_utility.trace('log_time_capture');
2265: hr_utility.trace('log_time_capture');
2266: END IF;
2267:
2268:
2269: UPDATE hxc_rpt_tc_hist_log
2270: SET request_id = p_request_id,
2271: history_till_date = p_request_sysdate
2272: WHERE (resource_id,
2273: tc_start_time,
2283: stop_time_tab ;
2284:
2285: IF g_debug
2286: THEN
2287: hr_utility.trace('Updated hxc_rpt_tc_hist_log ');
2288: hr_utility.trace('Total Number of timecards updated here : '||resource_id_tab.COUNT);
2289: END IF;
2290:
2291: IF resource_id_tab.COUNT > 0
2297: AND tc_stop_time = stop_time_tab(i);
2298:
2299: END IF;
2300:
2301: INSERT INTO hxc_rpt_tc_hist_log
2302: ( resource_id,
2303: tc_start_time,
2304: tc_stop_time,
2305: request_id,
2370: -- Get the request id and sysdates.
2371: -- Pass on the parameters to create the resources list ( comma separated, groups
2372: -- of 20).
2373: -- If chosen to delete history data and recreate, delete all info from
2374: -- HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
2375: -- For each valid list of resources picked, execute load_tc_level_info.
2376: -- Execute fetch_history_from_date to get the date from which history has to
2377: -- be considered for each timecard.
2378: -- If chosen to reuse history data, execute update_last_touched_date.
2387: -- Translate CLA reasons and type, if any.
2388: -- Update transaction_ids for the records.
2389: -- Translate the created by user_ids to user_names and employee names.
2390: -- Translate the last updated by user_ids to user_names and employee names.
2391: -- Log the time capture for each timecard records in HXC_RPT_TC_HIST_LOG for
2392: -- future reference.
2393: -- Clear HXC_RPT_TC_RESOURCE_TEMP, for the next iteration ( next 20 resources ).
2394:
2395: g_request_sysdate := SYSDATE;