[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
2311:
2312:
2313:
2314: -- LOG_TIME_CAPTURE
2315: -- Makes an entry in HXC_RPT_TC_HIST_LOG with the timecard identification
2316: -- parameters, for future reference.
2317:
2318: PROCEDURE log_time_capture ( p_request_id IN VARCHAR2,
2319: p_request_sysdate IN DATE )
2325:
2326: BEGIN
2327:
2328: -- Public Procedure log_time_capture
2329: -- If the timecard is already recorded in HXC_RPT_TC_HIST_LOG, update the
2330: -- history_till_date column with the request date.
2331: -- Delete from HXC_RPT_TC_RESOURCE_TEMP all records which are updated as above.
2332: -- For all the distinct timecard records existing in HXC_RPT_TC_RESOURCE_TEMP,
2333: -- insert a relevant record into HXC_RPT_TC_HIST_LOG.
2329: -- If the timecard is already recorded in HXC_RPT_TC_HIST_LOG, update the
2330: -- history_till_date column with the request date.
2331: -- Delete from HXC_RPT_TC_RESOURCE_TEMP all records which are updated as above.
2332: -- For all the distinct timecard records existing in HXC_RPT_TC_RESOURCE_TEMP,
2333: -- insert a relevant record into HXC_RPT_TC_HIST_LOG.
2334:
2335: IF g_debug
2336: THEN
2337: hr_utility.trace('log_time_capture');
2337: hr_utility.trace('log_time_capture');
2338: END IF;
2339:
2340:
2341: UPDATE hxc_rpt_tc_hist_log
2342: SET request_id = p_request_id,
2343: history_till_date = p_request_sysdate
2344: WHERE (resource_id,
2345: tc_start_time,
2355: stop_time_tab ;
2356:
2357: IF g_debug
2358: THEN
2359: hr_utility.trace('Updated hxc_rpt_tc_hist_log ');
2360: hr_utility.trace('Total Number of timecards updated here : '||resource_id_tab.COUNT);
2361: END IF;
2362:
2363: IF resource_id_tab.COUNT > 0
2369: AND tc_stop_time = stop_time_tab(i);
2370:
2371: END IF;
2372:
2373: INSERT INTO hxc_rpt_tc_hist_log
2374: ( resource_id,
2375: tc_start_time,
2376: tc_stop_time,
2377: request_id,
2442: -- Get the request id and sysdates.
2443: -- Pass on the parameters to create the resources list ( comma separated, groups
2444: -- of 20).
2445: -- If chosen to delete history data and recreate, delete all info from
2446: -- HXC_RPT_TC_HIST_LOG and HXC_RPT_TC_DETAILS_ALL.
2447: -- For each valid list of resources picked, execute load_tc_level_info.
2448: -- Execute fetch_history_from_date to get the date from which history has to
2449: -- be considered for each timecard.
2450: -- If chosen to reuse history data, execute update_last_touched_date.
2459: -- Translate CLA reasons and type, if any.
2460: -- Update transaction_ids for the records.
2461: -- Translate the created by user_ids to user_names and employee names.
2462: -- Translate the last updated by user_ids to user_names and employee names.
2463: -- Log the time capture for each timecard records in HXC_RPT_TC_HIST_LOG for
2464: -- future reference.
2465: -- Clear HXC_RPT_TC_RESOURCE_TEMP, for the next iteration ( next 20 resources ).
2466:
2467: g_request_sysdate := SYSDATE;