eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HXC_TIMECARD_SUMMARY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 28


  select details.time_building_block_id
        ,details.object_version_number
        ,details.start_time
        ,details.stop_time
        ,details.measure
        ,details.type
        ,details.creation_date
    from hxc_time_building_blocks days, hxc_time_building_blocks details
   where days.parent_building_block_id = p_id
     and days.parent_building_block_ovn = p_ovn
     and details.parent_building_block_id = days.time_building_block_id
     and details.parent_building_block_ovn = days.object_version_number
     and days.date_to = hr_general.end_of_time
     and details.date_to = hr_general.end_of_time;

Line: 47


SELECT tbb.resource_id
FROM   hxc_time_building_blocks tbb
WHERE  tbb.time_building_block_id = p_timecard_id
AND    tbb.object_version_number = p_timecard_ovn;

Line: 56


SELECT tbb.start_time,tbb.stop_time
FROM   hxc_time_building_blocks tbb
WHERE  tbb.time_building_block_id = p_timecard_id
AND    tbb.object_version_number = p_timecard_ovn;

Line: 62


select date_start from per_periods_of_service where person_id=p_resource_id order by date_start desc;

Line: 158


  select 'Y'
    from hxc_time_attribute_usages tau, hxc_time_attributes ta
   where tau.time_building_block_id = p_id
     and tau.time_Attribute_id = ta.time_attribute_Id
     and ta.attribute_category = hxc_timecard.c_reason_attribute;

Line: 210


procedure insert_summary_row(p_timecard_id           in hxc_time_building_blocks.time_building_block_id%type
                            ,p_mode                  in varchar2 default 'NORMAL'
                            ,p_attribute_category    in varchar2 default null
                            ,p_attribute1            in varchar2 default null
                            ,p_attribute2            in varchar2 default null
                            ,p_attribute3            in varchar2 default null
                            ,p_attribute4            in varchar2 default null
                            ,p_attribute5            in varchar2 default null
                            ,p_attribute6            in varchar2 default null
                            ,p_attribute7            in varchar2 default null
                            ,p_attribute8            in varchar2 default null
                            ,p_attribute9            in varchar2 default null
                            ,p_attribute10           in varchar2 default null
                            ,p_attribute11           in varchar2 default null
                            ,p_attribute12           in varchar2 default null
                            ,p_attribute13           in varchar2 default null
                            ,p_attribute14           in varchar2 default null
                            ,p_attribute15           in varchar2 default null
                            ,p_attribute16           in varchar2 default null
                            ,p_attribute17           in varchar2 default null
                            ,p_attribute18           in varchar2 default null
                            ,p_attribute19           in varchar2 default null
                            ,p_attribute20           in varchar2 default null
                            ,p_attribute21           in varchar2 default null
                            ,p_attribute22           in varchar2 default null
                            ,p_attribute23           in varchar2 default null
                            ,p_attribute24           in varchar2 default null
                            ,p_attribute25           in varchar2 default null
                            ,p_attribute26           in varchar2 default null
                            ,p_attribute27           in varchar2 default null
                            ,p_attribute28           in varchar2 default null
                            ,p_attribute29           in varchar2 default null
                            ,p_attribute30           in varchar2 default null
			    ,p_approval_item_type    in varchar2
			    ,p_approval_process_name in varchar2
			    ,p_approval_item_key     in varchar2
		   	    ,p_tk_audit_item_type    in varchar2
			    ,p_tk_audit_process_name in varchar2
			    ,p_tk_audit_item_key     in varchar2
			    ) is

cursor c_timecard_info(p_id in hxc_time_building_blocks.time_building_block_id%type) is
  select resource_id
        ,start_time
        ,stop_time
        ,object_version_number
        ,approval_status
        ,creation_date
        ,data_set_id
    from hxc_time_building_blocks
   where time_building_block_id = p_id
     and date_to = hr_general.end_of_time
     and scope = 'TIMECARD';

Line: 265


  select 'Y'
    from hxc_time_attributes
   where attribute_category = hxc_timecard.c_reason_attribute;

Line: 358


      hr_utility.trace('ABS> In hxc_timecard_summary_pkg.insert_summary_row');

Line: 368


      SELECT nvl(absence_days,0),
             nvl(absence_hours,0)
        INTO l_abs_days,
             l_abs_hours
        FROM hxc_absence_summary_temp
       WHERE resource_id = hxc_retrieve_absences.g_person_id
         AND start_time  = hxc_retrieve_absences.g_start_time
         AND stop_time   = hxc_retrieve_absences.g_stop_time;

Line: 393


      hr_utility.trace('ABS> final values before insert into timecard summary');

Line: 401


      hr_utility.trace('ABS> In hxc_timecard_summary_pkg.insert_summary_row');

Line: 410


  insert into hxc_timecard_summary
  (timecard_id
  ,timecard_ovn
  ,approval_status
  ,resource_id
  ,start_time
  ,stop_time
  ,recorded_hours
  ,has_reasons
  ,submission_date
  ,approval_item_type
  ,approval_process_name
  ,approval_item_key
  ,attribute_category
  ,attribute1
  ,attribute2
  ,attribute3
  ,attribute4
  ,attribute5
  ,attribute6
  ,attribute7
  ,attribute8
  ,attribute9
  ,attribute10
  ,attribute11
  ,attribute12
  ,attribute13
  ,attribute14
  ,attribute15
  ,attribute16
  ,attribute17
  ,attribute18
  ,attribute19
  ,attribute20
  ,attribute21
  ,attribute22
  ,attribute23
  ,attribute24
  ,attribute25
  ,attribute26
  ,attribute27
  ,attribute28
  ,attribute29
  ,attribute30
  ,tk_audit_item_type
  ,tk_audit_process_name
  ,tk_audit_item_key
  ,data_set_id
  ,absence_days
  ,absence_hours
  )
  values
  (p_timecard_id
  ,l_ovn
  ,l_approval_status
  ,l_resource_id
  ,l_start_time
  ,l_stop_time
  ,l_recorded_hours
  ,l_has_reasons
  ,l_submission_date
  ,p_approval_item_type
  ,p_approval_process_name
  ,p_approval_item_key
  ,p_attribute_category
  ,p_attribute1
  ,p_attribute2
  ,p_attribute3
  ,p_attribute4
  ,p_attribute5
  ,p_attribute6
  ,p_attribute7
  ,p_attribute8
  ,p_attribute9
  ,p_attribute10
  ,p_attribute11
  ,p_attribute12
  ,p_attribute13
  ,p_attribute14
  ,p_attribute15
  ,p_attribute16
  ,p_attribute17
  ,p_attribute18
  ,p_attribute19
  ,p_attribute20
  ,p_attribute21
  ,p_attribute22
  ,p_attribute23
  ,p_attribute24
  ,p_attribute25
  ,p_attribute26
  ,p_attribute27
  ,p_attribute28
  ,p_attribute29
  ,p_attribute30
  ,p_tk_audit_item_type
  ,p_tk_audit_process_name
  ,p_tk_audit_item_key
  ,l_data_set_id
  ,l_abs_days  -- Added as part of OTL ABS Integration
  ,l_abs_hours --Added as part of OTL ABS Integration
  );

Line: 521


End insert_summary_row;

Line: 523


procedure update_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type
			    ,p_approval_item_type     in hxc_timecard_summary.approval_item_type%type
			    ,p_approval_process_name  in hxc_timecard_summary.approval_process_name%type
			    ,p_approval_item_key      in hxc_timecard_summary.approval_item_key%type
) is

l_item_key hxc_timecard_summary.approval_item_key%type;

Line: 534


select 'Y'
from wf_item_activity_statuses wias
where item_type = 'HXCEMP'
and item_key = l_item_key
and activity_status = 'DEFERRED';

Line: 542


select approval_item_key
from hxc_timecard_summary
where timecard_id = p_timecard_id;

Line: 567


UPDATE hxc_timecard_summary
SET    approval_item_type = p_approval_item_type,
       approval_process_name = p_approval_process_name,
       approval_item_key =p_approval_item_key
WHERE   TIMECARD_ID=       p_timecard_id;

Line: 574


End update_summary_row;

Line: 576


procedure delete_summary_row(p_timecard_id in hxc_time_building_blocks.time_building_block_id%type) is

Begin

delete from hxc_timecard_summary where timecard_id = p_timecard_id;

Line: 587


End delete_summary_row;

Line: 593


update hxc_timecard_summary
   set approval_status = hxc_timecard.c_rejected_status
 where timecard_id = p_timecard_id;

Line: 603


SELECT resource_id,
       start_time,
       stop_time,
       approval_status
  FROM hxc_timecard_summary
 WHERE timecard_id =  c_timecard_id;

Line: 619


update hxc_timecard_summary
   set approval_status = hxc_timecard.c_approved_status
 where timecard_id = p_timecard_id;

Line: 663


update hxc_timecard_summary
   set approval_status = hxc_timecard.c_submitted_status
 where timecard_id = p_timecard_id;

Line: 674


PROCEDURE update_transferred_to( p_timecard_id    IN   NUMBER,
                                 p_timecard_ovn   IN   NUMBER,
                                 p_process_id     IN   NUMBER )
IS

     CURSOR get_details( p_timecard_id    IN   NUMBER,
                                 p_timecard_ovn   IN   NUMBER,
                                 p_process_id     IN   NUMBER )
         IS SELECT 1
              FROM hxc_timecard_summary sum,
                   hxc_time_building_blocks day,
                   hxc_time_building_blocks det,
                   hxc_latest_details hld
             WHERE sum.timecard_id = p_timecard_id
               AND sum.timecard_ovn = p_timecard_ovn
               AND day.parent_building_block_id = sum.timecard_id
               AND day.parent_building_block_ovn = sum.timecard_ovn
               AND det.parent_building_block_id  = day.time_building_block_id
               AND det.parent_building_block_ovn = day.object_version_number
               AND hld.time_building_block_id    = det.time_building_block_id
               AND hld.object_version_number     = det.object_version_number
               AND NOT EXISTS ( SELECT 1
                                  FROM hxc_transactions ht,
                                       hxc_transaction_details htd
                                 WHERE htd.time_building_block_id = det.time_building_block_id
                                   AND det.object_version_number = htd.time_building_block_ovn
                                   AND htd.status                = 'SUCCESS'
                                   AND ht.transaction_id         = htd.transaction_id
                                   AND ht.type                   = 'RETRIEVAL'
                                   AND ht.status                 = 'SUCCESS'
                                   AND ht.transaction_process_id = p_process_id )
               AND (    det.date_to = hr_general.end_of_time
                     OR (   det.date_to <> hr_general.end_of_time
                         AND EXISTS ( SELECT 1
                                  FROM hxc_transactions ht,
                                       hxc_transaction_details htd
                                 WHERE htd.time_building_block_id = det.time_building_block_id
                                   AND det.object_version_number  > htd.time_building_block_ovn
                                   AND htd.status                 = 'SUCCESS'
                                   AND ht.transaction_id          = htd.transaction_id
                                   AND ht.type                    = 'RETRIEVAL'
                                   AND ht.status                  = 'SUCCESS'
                                   AND ht.transaction_process_id  = p_process_id )
                           )
                    );

Line: 721


            IS SELECT htr.name
                 FROM hxc_retrieval_processes hrp,
                      hxc_time_recipients htr
                WHERE hrp.retrieval_process_id = p_process_id
                  AND hrp.time_recipient_id    = htr.time_recipient_id;

Line: 784


        UPDATE hxc_timecard_summary
           SET transferred_to = RTRIM(LTRIM((NVL(REPLACE(transferred_to,l_recipient_name),',')||','||l_recipient_name),','),',')
         WHERE timecard_id = p_timecard_id
           AND timecard_ovn = p_timecard_ovn;

Line: 792


            SELECT transferred_to
              INTO l_recipient_name
              FROM hxc_timecard_summary
             WHERE timecard_id = p_timecard_id
               AND timecard_ovn = p_timecard_ovn;

Line: 809


END update_transferred_to;