[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
468:
469: CURSOR get_last_touched_date
470: IS SELECT /*+ ORDERED
471: INDEX(det HXC_RPT_TC_DETAILS_FK2)
472: INDEX(bb HXC_TIME_BUILDING_BLOCKS_PK) */
473: bb.time_building_block_id,
474: bb.object_version_number,
475: bb.date_to,
476: bb.last_update_date,
474: bb.object_version_number,
475: bb.date_to,
476: bb.last_update_date,
477: bb.last_updated_by
478: FROM hxc_time_building_blocks bb,
479: hxc_rpt_tc_details_all det,
480: hxc_rpt_tc_resource_temp gt
481: WHERE bb.scope = 'DETAIL'
482: AND bb.time_building_block_id = det.detail_bb_id
498: -- HXC_RPT_TC_DETAILS_ALL table.
499: -- For those records in HXC_RPT_TC_DETAILS_ALL which belong to
500: -- the timecard records in HXC_RPT_TC_RESOURCE_TEMP, get the
501: -- last_updated_date, last_updated_by, and date_to values
502: -- from HXC_TIME_BUILDING_BLOCKS table, if the date_to column
503: -- is different.
504: -- Update all such records with the last_updated_date, last_updated_by
505: -- and date_to columns in HXC_RPT_TC_DETAILS_ALL
506:
564: IS SELECT /*+ INDEX(det HXC_RPT_TC_DETAILS_FK2)*/
565: comment_text,
566: detail_bb_id,
567: detail_bb_ovn
568: FROM hxc_time_building_blocks bb,
569: hxc_rpt_tc_details_all det,
570: hxc_rpt_tc_resource_temp gt
571: WHERE bb.time_building_block_id = det.tc_bb_id
572: AND bb.object_version_number = det.tc_bb_ovn
581: BEGIN
582:
583: -- Public Procedure update_timecard_comments
584: -- For all the records previously loaded into HXC_RPT_TC_DETAILS_ALL
585: -- query from HXC_TIME_BUILDING_BLOCKS, their relevant timecard
586: -- comments.
587: -- Update HXC_RPT_TC_DETAILS_ALL with the comments picked up above.
588:
589: IF g_debug
645: BEGIN
646:
647: -- Public Procedure load_detail_info
648: -- Insert into HXC_RPT_TC_DETAILS_ALL detail information
649: -- and day information from HXC_TIME_BUILDING_BLOCKS
650: -- WHERE clauses are placed taking care that data is picked up
651: -- only from the history_from_date already recorded -- meaning
652: -- we need data that is not existing only. Anyways, at this
653: -- point, an already existing detail is picked up again, the request
708: gt.resource_name,
709: day.date_to,
710: detail.approval_status
711: FROM hxc_rpt_tc_resource_temp gt,
712: hxc_time_building_blocks day,
713: hxc_time_building_blocks detail
714: WHERE gt.tc_bb_id = day.parent_building_block_id
715: AND gt.resource_id = day.resource_id
716: AND day.time_building_block_id = detail.parent_building_block_id
709: day.date_to,
710: detail.approval_status
711: FROM hxc_rpt_tc_resource_temp gt,
712: hxc_time_building_blocks day,
713: hxc_time_building_blocks detail
714: WHERE gt.tc_bb_id = day.parent_building_block_id
715: AND gt.resource_id = day.resource_id
716: AND day.time_building_block_id = detail.parent_building_block_id
717: AND day.object_version_number = detail.parent_building_block_ovn