[Home] [Help]
182: DECODE(current_npw_flag,''Y'',npw_number),
183: '' ''
184: )||'']''),
185: ''p_request_id''
186: FROM hxc_time_building_blocks hxc,
187: per_all_people_f ppf
188: WHERE scope = ''TIMECARD''
189: AND person_id = resource_id
190: AND start_time >= effective_start_date
200: -- Takes in a comma separated list of resource ids, the timecard start date,
201: -- stop_date and request id.
202: -- Attaches the parameters to the timecard query defined above, in the date
203: -- and the resource_id AND clauses.
204: -- Execute the query, which selects from HXC_TIME_BUILDING_BLOCKS and PER_ALL
205: -- _PEOPLE_F and inserts into HXC_RPT_TC_RESOURCE_TEMP
206:
207:
208: IF g_debug
470: -- Reordered the tables in the FROM list
471: CURSOR get_last_touched_date
472: IS SELECT /*+ ORDERED
473: INDEX(det HXC_RPT_TC_DETAILS_FK2)
474: INDEX(bb HXC_TIME_BUILDING_BLOCKS_PK) */
475: bb.time_building_block_id,
476: bb.object_version_number,
477: bb.date_to,
478: bb.last_update_date,
478: bb.last_update_date,
479: bb.last_updated_by
480: FROM hxc_rpt_tc_resource_temp gt,
481: hxc_rpt_tc_details_all det,
482: hxc_time_building_blocks bb
483: WHERE bb.scope = 'DETAIL'
484: AND bb.time_building_block_id = det.detail_bb_id
485: AND bb.object_version_number = det.detail_bb_ovn
486: AND det.tc_bb_id = gt.tc_bb_id
500: -- HXC_RPT_TC_DETAILS_ALL table.
501: -- For those records in HXC_RPT_TC_DETAILS_ALL which belong to
502: -- the timecard records in HXC_RPT_TC_RESOURCE_TEMP, get the
503: -- last_updated_date, last_updated_by, and date_to values
504: -- from HXC_TIME_BUILDING_BLOCKS table, if the date_to column
505: -- is different.
506: -- Update all such records with the last_updated_date, last_updated_by
507: -- and date_to columns in HXC_RPT_TC_DETAILS_ALL
508:
566: IS SELECT /*+ INDEX(det HXC_RPT_TC_DETAILS_FK2)*/
567: comment_text,
568: detail_bb_id,
569: detail_bb_ovn
570: FROM hxc_time_building_blocks bb,
571: hxc_rpt_tc_details_all det,
572: hxc_rpt_tc_resource_temp gt
573: WHERE bb.time_building_block_id = det.tc_bb_id
574: AND bb.object_version_number = det.tc_bb_ovn
583: BEGIN
584:
585: -- Public Procedure update_timecard_comments
586: -- For all the records previously loaded into HXC_RPT_TC_DETAILS_ALL
587: -- query from HXC_TIME_BUILDING_BLOCKS, their relevant timecard
588: -- comments.
589: -- Update HXC_RPT_TC_DETAILS_ALL with the comments picked up above.
590:
591: IF g_debug
647: BEGIN
648:
649: -- Public Procedure load_detail_info
650: -- Insert into HXC_RPT_TC_DETAILS_ALL detail information
651: -- and day information from HXC_TIME_BUILDING_BLOCKS
652: -- WHERE clauses are placed taking care that data is picked up
653: -- only from the history_from_date already recorded -- meaning
654: -- we need data that is not existing only. Anyways, at this
655: -- point, an already existing detail is picked up again, the request
710: gt.resource_name,
711: day.date_to,
712: detail.approval_status
713: FROM hxc_rpt_tc_resource_temp gt,
714: hxc_time_building_blocks day,
715: hxc_time_building_blocks detail
716: WHERE gt.tc_bb_id = day.parent_building_block_id
717: AND gt.resource_id = day.resource_id
718: AND day.time_building_block_id = detail.parent_building_block_id
711: day.date_to,
712: detail.approval_status
713: FROM hxc_rpt_tc_resource_temp gt,
714: hxc_time_building_blocks day,
715: hxc_time_building_blocks detail
716: WHERE gt.tc_bb_id = day.parent_building_block_id
717: AND gt.resource_id = day.resource_id
718: AND day.time_building_block_id = detail.parent_building_block_id
719: AND day.object_version_number = detail.parent_building_block_ovn