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_APPROVAL_UTILITIES SQL Statements

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

Line: 28


FUNCTION is_selected(
  p_selected_ids IN hxc_deposit_wrapper_utilities.t_simple_table
 ,p_block_id     IN hxc_time_building_blocks.time_building_block_id%TYPE
)
RETURN BOOLEAN
IS
 l_proc VARCHAR2(100);

Line: 38


	l_proc := 'is_selected';

Line: 42


  FOR l_index IN p_selected_ids.first .. p_selected_ids.last
  LOOP
    IF p_selected_ids(l_index) = TO_CHAR(p_block_id)
    THEN

      RETURN TRUE;

Line: 53


END is_selected;

Line: 135


  l_sql := 'SELECT ''Y'''
        || '  FROM hxc_time_attribute_usages htau,'
        || '       hxc_time_attributes hta'
        || ' WHERE ' || p_block_id || ' = htau.time_building_block_id'
        || '   AND ' || p_block_ovn || '= htau.time_building_block_ovn'
        || '   AND htau.time_attribute_id = hta.time_attribute_id'
        || '   AND hta.attribute_category = ''APPROVAL'''
        || '   AND NVL(hta.attribute6,  '' '') '
        || p_operator
        || ' '''
        || l_like_string
        || p_comment
        || l_like_string
        || '''';

Line: 151


l_sql := 'SELECT ''Y'''
        || '  FROM hxc_time_building_blocks htbb,'
        || '       hxc_time_building_blocks htbb_tc'
        || ' WHERE htbb.time_building_block_id = ' || p_block_id
        || '   AND htbb.object_version_number = ' || p_block_ovn
        || '   AND htbb_tc.scope = ''TIMECARD'''
        || '   AND htbb_tc.resource_id = htbb.resource_id'
        || '   AND TRUNC(htbb_tc.start_time) >= TRUNC(htbb.start_time)'
        || '   AND TRUNC(htbb_tc.stop_time)  <= TRUNC(htbb.stop_time)'
        || '   AND htbb_tc.date_to = hr_general.end_of_time'
        || '   AND NVL(htbb_tc.comment_text, '' '') '
        || p_operator
        || ' '''
        || l_like_string
        || p_comment
        || l_like_string
        || '''';

Line: 202


  l_sql := 'SELECT ''Y'''
        || '  FROM hxc_ap_detail_links aplinks,'
        || '       hxc_time_building_blocks htbb_detail'
        || ' WHERE aplinks.application_period_id = ' || p_block_id
        || '   AND aplinks.time_building_block_id = htbb_detail.time_building_block_id '
        || '   AND aplinks.time_building_block_ovn = htbb_detail.object_version_number '
        || '   AND htbb_detail.date_to = hr_general.end_of_time'
        || '   AND NVL(htbb_detail.comment_text, '' '') '
        || p_operator
        || ' '''
        || l_like_string
        || p_comment
        || l_like_string
        || '''';

Line: 241


    SELECT context, segment, bld_blk_info_type_id
    FROM hxc_mapping_attributes_v
    WHERE map = 'OTL Deposit Process Mapping'
      AND upper(field_name) = upper(p_field_name);

Line: 323


  l_sql := 'SELECT ''Y'''
          || '  FROM hxc_ap_detail_links aplinks,'
          || '       hxc_time_building_blocks htbb_detail,'
          || '       hxc_time_attribute_usages htau,'
          || '       hxc_time_attributes hta'
          || ' WHERE aplinks.application_period_id = ' || p_block_id
          || '   AND aplinks.time_building_block_id = htbb_detail.time_building_block_id '
          || '   AND aplinks.time_building_block_ovn = htbb_detail.object_version_number '
          || '   AND htbb_detail.date_to = hr_general.end_of_time'
          || '   AND htau.time_building_block_id = htbb_detail.time_building_block_id'
          || '   AND htau.time_building_block_ovn = htbb_detail.object_version_number'
          || '   AND htau.time_attribute_id = hta.time_attribute_id'
          || '   AND hta.attribute_category = ' || '''' || l_context || ''''
          || '   AND hta.' || l_segment ||' '|| p_search_operator||' '||''''||l_like_string || p_search_value||l_like_string||'''';

Line: 338


  l_sql := 'SELECT ''Y'''
          || '  FROM hxc_ap_detail_links aplinks,'
          || '       hxc_time_building_blocks htbb_detail,'
          || '       hxc_time_attribute_usages htau,'
          || '       hxc_time_attributes hta'
          || ' WHERE aplinks.application_period_id = ' || p_block_id
          || '   AND aplinks.time_building_block_id = htbb_detail.time_building_block_id '
          || '   AND aplinks.time_building_block_ovn = htbb_detail.object_version_number '
          || '   AND htbb_detail.date_to = hr_general.end_of_time'
          || '   AND htau.time_building_block_id = htbb_detail.time_building_block_id'
          || '   AND htau.time_building_block_ovn = htbb_detail.object_version_number'
          || '   AND htau.time_attribute_id = hta.time_attribute_id'
          || '   AND hta.attribute_category = ' || '''' || l_context || ''''
          || '   AND hta.' || l_segment || ' IN (' || l_flex_search_value || ')';

Line: 373


 ,p_selected_ids OUT NOCOPY hxc_deposit_wrapper_utilities.t_simple_table
)
IS

l_adv_table hxc_deposit_wrapper_utilities.t_simple_table;

Line: 386


l_sql_select        VARCHAR2(1000);

Line: 394


l_selected_id_index NUMBER;

Line: 421


  l_sql_select := 'SELECT time_building_block_id';

Line: 524


      l_one_where := 'htbb.resource_id in (select p.person_id
      					from per_people_f p,per_person_types ppt,
      					per_person_type_usages_f pptu
      					where pptu.person_id = p.person_id and
      					ppt.person_type_id = pptu.person_type_id and
      					ppt.user_person_type '
			      || l_search_operator ||''''||l_like_string||l_search_value
			      ||l_like_string||''''||')';

Line: 545


     l_one_where := 'htbb.time_building_block_id in (select distinct hadl.APPLICATION_PERIOD_ID
     						from hxc_time_attributes hta, hxc_time_attribute_usages htau,
     						po_vendors pv, po_headers_all pha, hxc_time_building_blocks detail,
     						hxc_ap_detail_links hadl, po_lines_all pla
     						where hta.attribute_category ='||''''||l_context||''''
     										||' and  hta.'||l_segment
     										||'= pla.po_line_id and pv.vendor_name '
     										||l_search_operator||''''||l_like_string
     										||l_search_value||l_like_string||''''
     										||' and pha.vendor_id=pv.VENDOR_ID
     									and pla.po_header_id= pha.po_header_id
     									and htau.TIME_ATTRIBUTE_ID = hta.time_attribute_id
     									and htau.time_building_block_id = detail.time_building_block_id
     									and detail.date_to = hr_general.end_of_time
     									and hadl.time_building_block_id = detail.time_building_block_id
     									and hadl.time_building_block_ovn = detail.object_version_number
     									and hta.bld_blk_info_type_id ='|| l_bld_blk_info_type_id||')';

Line: 605


  l_complete_sql := l_sql_select
                 || ' '
                 || l_sql_from
                 || ' '
                 || l_sql_where;

Line: 614


  l_selected_id_index := 0;

Line: 618


    FETCH c_sql INTO p_selected_ids(l_selected_id_index);

Line: 623


	hr_utility.trace('selected id=' ||  p_selected_ids(l_selected_id_index));

Line: 625


    l_selected_id_index := l_selected_id_index + 1;

Line: 662


    SELECT full_name
    FROM per_all_people_f
   WHERE person_id = p_person_id
     AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Line: 697


  l_selected_ids hxc_deposit_wrapper_utilities.t_simple_table;

Line: 712


    SELECT wias.item_key
      FROM WF_NOTIFICATIONS wn,
           wf_item_activity_statuses wias,
           fnd_user fu
     WHERE wn.recipient_role = fu.user_name
       AND wn.status = 'OPEN'
       AND wn.message_name IN ('TIMECARD_APPROVAL','TIMECARD_APPROVAL_INLINE', 'TIMECARD_APPROVAL_INLINE_ABS')
       AND wias.notification_id = wn.notification_id
       AND wias.activity_status = 'NOTIFIED'
       AND wias.item_type = p_item_type
       AND fu.employee_id = p_approver_id
     ORDER BY from_user desc;

Line: 730


    SELECT   /*+ leading(apsum) */
	  apsum.application_period_id
          ,apsum.application_period_ovn
          ,apsum.start_time
          ,apsum.stop_time
          ,hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status)
          ,ppf.full_name
          ,htbb.comment_text
          ,favtl.application_name
          ,apsum.resource_id
          ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, '', apsum.application_period_id)
          ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total2', apsum.application_period_id),
           hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total3', apsum.application_period_id),
           apsum.time_recipient_id
          ,nvl(ppf.employee_number,ppf.NPW_NUMBER)
          ,hxc_self_service_timecard.get_timecard_transferred_to(hts.timecard_id, hts.timecard_ovn)
      FROM hxc_app_period_summary apsum
          ,hxc_time_building_blocks htbb
          ,fnd_application_tl favtl
          ,per_all_people_f ppf
          ,hxc_time_recipients htr
          ,hxc_tc_ap_links htal
          ,hxc_timecard_summary hts


     WHERE apsum.application_period_id = p_app_bb_id
       AND apsum.application_period_ovn = p_app_bb_ovn
       AND htal.application_period_id = apsum.application_period_id
       AND hts.timecard_id = htal.timecard_id
       AND apsum.resource_id = ppf.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
       AND htbb.time_building_block_id = apsum.application_period_id
       AND htbb.object_version_number = apsum.application_period_ovn
       AND favtl.application_id = htr.application_id
       AND htr.time_recipient_id = apsum.time_recipient_id
       AND favtl.language = userenv('LANG');

Line: 774


  SELECT HXC_TRANSACTION_LOCK_S.nextval
    INTO g_transaction_id
    FROM DUAL;

Line: 798


      SELECT NUMBER_VALUE
      INTO l_resource_id
      FROM wf_item_attribute_values
     WHERE item_type = l_item_type
       AND item_key = l_item_key
       AND name = 'RESOURCE_ID';

Line: 821


        SELECT DATE_VALUE
          INTO l_end_date
          FROM wf_item_attribute_values
         WHERE item_type = l_item_type
           AND item_key = l_item_key
           AND name = 'APP_END_DATE';

Line: 844


        SELECT DATE_VALUE
          INTO l_start_date
          FROM wf_item_attribute_values
         WHERE item_type = l_item_type
           AND item_key = l_item_key
           AND name = 'APP_START_DATE';

Line: 866


      SELECT NUMBER_VALUE
      INTO l_app_bb_id
      FROM wf_item_attribute_values
     WHERE item_type = l_item_type
       AND item_key = l_item_key
       AND name = 'APP_BB_ID';

Line: 873


      SELECT NUMBER_VALUE
        INTO l_app_bb_ovn
        FROM wf_item_attribute_values
       WHERE item_type = l_item_type
         AND item_key = l_item_key
         AND name = 'APP_BB_OVN';

Line: 964


     ,p_selected_ids  => l_selected_ids
    );

Line: 971


    IF l_selected_ids.count > 0
    THEN
      if g_debug then
	hr_utility.set_location(g_package||l_proc, 110);

Line: 983


        IF is_selected(l_selected_ids,
               l_approval_array(l_index).time_building_block_id)
        THEN

          add_records(p_approval_array, l_approval_array(l_index));

Line: 1031


    SELECT  /*+ leading(apsum) */
           apsum.application_period_id
          ,apsum.application_period_ovn
          ,apsum.start_time
          ,apsum.stop_time
          ,hr_general.decode_lookup('HXC_APPROVAL_STATUS', apsum.approval_status)
          ,ppf.full_name
          ,htbb.comment_text
          ,favtl.application_name
          ,apsum.resource_id
          ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, '', apsum.application_period_id)
          ,hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total2', apsum.application_period_id),
           hxc_time_category_utils_pkg.category_app_period_tc_hrs(apsum.start_time, apsum.stop_time, apsum.resource_id, 'Total3', apsum.application_period_id),
           apsum.time_recipient_id
	  ,nvl(ppf.employee_number,ppf.NPW_NUMBER)
	  ,hxc_self_service_timecard.get_timecard_transferred_to(hts.timecard_id, hts.timecard_ovn)
      FROM hxc_app_period_summary apsum
          ,hxc_time_building_blocks htbb
          ,fnd_application_tl favtl
          ,per_all_people_f ppf
          ,hxc_time_recipients htr
          ,hxc_tc_ap_links htal
          ,hxc_timecard_summary hts

     WHERE apsum.approver_id = p_approver_id
       AND apsum.approval_status <> 'SUBMITTED'
       AND htal.application_period_id = apsum.application_period_id
       AND hts.timecard_id = htal.timecard_id
       AND apsum.resource_id = ppf.person_id
       AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
       AND htbb.time_building_block_id = apsum.application_period_id
       AND htbb.object_version_number = apsum.application_period_ovn
       AND favtl.application_id = htr.application_id
       AND htr.time_recipient_id = apsum.time_recipient_id
       AND favtl.language = userenv('LANG')
       AND NVL(p_resource_id, apsum.resource_id) = apsum.resource_id
       AND NVL(p_from_date, TO_CHAR(apsum.stop_time, 'YYYY/MM/DD')) <=  TO_CHAR(apsum.stop_time, 'YYYY/MM/DD')
       AND NVL(p_to_date, TO_CHAR(apsum.start_time, 'YYYY/MM/DD')) >= TO_CHAR(apsum.start_time, 'YYYY/MM/DD')
       ORDER BY ppf.full_name desc, apsum.start_time desc;

Line: 1074


  l_selected_ids    hxc_deposit_wrapper_utilities.t_simple_table;

Line: 1137


     ,p_selected_ids  => l_selected_ids
    );

Line: 1144


    IF l_selected_ids.count > 0
    THEN
      if g_debug then
	hr_utility.set_location(g_package||l_proc, 110);

Line: 1156


        IF is_selected(l_selected_ids,
               l_approval_array(l_index).time_building_block_id)
        THEN

          add_records(p_approval_array, l_approval_array(l_index));