APPS.HXC_DATA_SET SQL Statements

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

Line: 22

SELECT 1
FROM hxc_data_sets
WHERE start_date < p_end_date
AND end_date > p_start_date;

Line: 29

SELECT 1
FROM hxc_data_sets
WHERE data_set_name = p_data_set_name;

Line: 86

select DATA_SET_ID,DATA_SET_NAME,DESCRIPTION,START_DATE,END_DATE,DATA_SET_MODE,
decode(STATUS,'BACKUP_IN_PROGRESS','ARCHIVE_IN_PROGRESS',STATUS) status,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,VALIDATION_STATUS
from hxc_data_sets
where status <> 'MARKING_IN_PROGRESS';

Line: 127

PROCEDURE insert_into_data_set(p_data_set_id   OUT NOCOPY NUMBER,
			       p_data_set_name IN VARCHAR2,
			       p_description   IN VARCHAR2,
                               p_start_date    IN DATE,
                               p_stop_date     IN DATE,
                               p_status	       IN VARCHAR2) is

BEGIN

  --get the sequence from hxc_data_sets_s
  select hxc_data_sets_s.nextval into p_data_set_id from dual;

Line: 139

  insert into hxc_data_sets
	(data_set_id,
	 data_set_name,
	 description,
	 start_date,
	 end_date,
	 data_set_mode,
	 status)
  values
	(p_data_set_id,
	 p_data_set_name,
	 p_description,
	 p_start_date,
	 p_stop_date,
	 'B',
         p_status);

Line: 158

END insert_into_data_set;

Line: 170

SELECT /*+ INDEX( hxc hxc_time_building_blocks_n1) */
distinct time_building_block_id
FROM  hxc_time_building_blocks hxc
WHERE scope ='TIMECARD'
AND (data_set_id <> p_data_set_id OR data_set_id IS NULL)
AND  stop_time BETWEEN p_start_date AND p_stop_date;

Line: 219

    DELETE FROM hxc_temp_timecard_chunks
    WHERE data_set_id = p_data_set_id;

Line: 232

      INSERT INTO hxc_temp_timecard_chunks
      (data_set_id,id, scope)
      VALUES
      (p_data_set_id,l_tbb_id_tab(x),'TIMECARD');

Line: 238

fnd_file.put_line(fnd_file.LOG,'--- >Count of TIMECARD INSERT INTO TEMP table for this chunk: '||sql%rowcount);

Line: 241

      l_tbb_id_tab.DELETE;

Line: 243

      UPDATE hxc_time_building_blocks tbb
      SET    data_set_id = p_data_set_id
      WHERE  scope ='TIMECARD'
      AND    time_building_block_id in
             (SELECT temp.id
              FROM   hxc_temp_timecard_chunks temp
              WHERE  temp.data_set_id = p_data_set_id
              AND    temp.scope = 'TIMECARD');

Line: 252

fnd_file.put_line(fnd_file.LOG,'--- >Count of TIMECARD UPDATE for this chunk: '||sql%rowcount);

Line: 257

/*    INSERT INTO hxc_temp_timecard_chunks (id,data_set_id,scope)
      SELECT distinct day.time_building_block_id, p_data_set_id,'DAY'
      FROM  hxc_time_building_blocks day
      WHERE day.scope = 'DAY'
      AND   day.parent_building_block_id in
      	    (SELECT temp.id
      	     FROM   hxc_temp_timecard_chunks temp
             WHERE  temp.scope = 'TIMECARD'
             AND    temp.data_set_id = p_data_set_id);

Line: 269

      UPDATE hxc_time_building_blocks tbb
      SET    data_set_id = p_data_set_id
      WHERE  scope ='DAY'
      AND    time_building_block_id in
             (SELECT temp.id
              FROM   hxc_temp_timecard_chunks temp
              WHERE  temp.data_set_id = p_data_set_id
              AND    temp.scope = 'DAY');

Line: 279

      INSERT INTO hxc_temp_timecard_chunks (id,data_set_id, scope)
      SELECT distinct det.time_building_block_id,p_data_set_id,'DETAIL'
      FROM   hxc_time_building_blocks det
      WHERE  det.scope = 'DETAIL'
      AND    det.parent_building_block_id IN
              (SELECT temp.id
               FROM   hxc_temp_timecard_chunks temp
               WHERE  temp.scope = 'DAY'
               AND    temp.data_set_id = p_data_set_id);

Line: 291

      UPDATE hxc_time_building_blocks tbb
      SET    data_set_id = p_data_set_id
      WHERE  scope ='DETAIL'
      AND    time_building_block_id in
             (SELECT ID
              FROM   hxc_temp_timecard_chunks temp
              WHERE  temp.data_set_id = p_data_set_id
              AND    temp.scope = 'DETAIL');

Line: 300

      UPDATE hxc_time_attribute_usages
      SET    data_set_id = p_data_set_id
      WHERE  time_building_block_id in
        (SELECT  temp.id
         FROM    hxc_temp_timecard_chunks temp
         WHERE   temp.data_set_id = p_data_set_id
         AND     temp.scope in ('TIMECARD','DAY','DETAIL'));

Line: 310

	update hxc_time_attributes
	set data_set_id = l_data_set_id
	where time_attribute_id in
	   (select time_attribute_id from hxc_time_attribute_usages
	    where data_set_id = l_data_set_id)
	    and data_set_id is null
	and nvl(consolidated_flag,'N') <> 'Y';

Line: 318

      UPDATE hxc_transaction_details htd
      SET    htd.data_set_id = p_data_set_id
      where  htd.time_building_block_id in
        (SELECT  temp.id
         FROM    hxc_temp_timecard_chunks temp
         WHERE   temp.data_set_id = p_data_set_id
         AND     temp.scope in ('TIMECARD','DAY','DETAIL'));

Line: 326

      UPDATE hxc_transactions
      SET    data_set_id = p_data_set_id
      WHERE  transaction_id in
             (SELECT distinct transaction_id
	      FROM hxc_transaction_details txnd,
	       	   hxc_temp_timecard_chunks temp
	      WHERE txnd.time_building_block_id = temp.id
	      AND temp.data_set_id = p_data_set_id
	      AND temp.scope in ('TIMECARD','DAY','DETAIL'))
      AND type = 'DEPOSIT';

Line: 339

      UPDATE hxc_timecard_summary hts
      SET    data_set_id = p_data_set_id
      where  timecard_id in
         (SELECT  temp.id
          FROM    hxc_temp_timecard_chunks temp
          WHERE   temp.data_set_id = p_data_set_id
          AND     temp.scope = 'TIMECARD');

Line: 380

SELECT data_set_name, description, start_date, end_date,
       data_set_mode, status, validation_status
FROM   hxc_data_sets
WHERE  data_set_id = p_data_set_id;

Line: 418

  l_sql := 'update '||p_table_name||
 	   ' set data_set_id = null where data_set_id = '||p_data_set_id||
	   ' and rownum <= '||p_chunk_size;

Line: 494

  DELETE FROM hxc_data_sets
  WHERE data_set_id = p_data_set_id;

Line: 540

	SELECT /*+ NO_INDEX(day HXC_TIME_BUILDING_BLOCKS_N2) NO_INDEX(det HXC_TIME_BUILDING_BLOCKS_N2) *
	  DISTINCT
	    tsum.resource_id,
	    tsum.start_time,
	    tsum.stop_time,
	    tsum.approval_status,
	    nvl(per.employee_number,'Employee Number Unkown') employee_number,
	    nvl(per.full_name,'Full Name Unknown') full_name
	FROM
	    hxc_time_building_blocks day,
	    hxc_time_building_blocks det,
	    hxc_latest_details hld,
	    hxc_data_sets hds,
	    hxc_timecard_summary tsum,
	    hxc_application_sets_v has,
	    hxc_application_set_comps_v hasv,
            per_all_people_f per
	WHERE
	NOT EXISTS
	    (SELECT 1
	     FROM   hxc_transaction_details txnd,
	            hxc_transactions txn,
	            hxc_retrieval_processes rp
	    WHERE  txn.type = 'RETRIEVAL'
	    AND    txn.status = 'SUCCESS'
	    AND    txnd.status = 'SUCCESS'
	    AND    txnd.time_building_block_id = hld.time_building_block_id
	    AND    txnd.time_building_block_ovn = hld.object_version_number
	    AND    txnd.transaction_id = txn.transaction_id
	    AND    decode(txn.transaction_process_id,-1,l_bee_retrieval,txn.transaction_process_id) = rp.retrieval_process_id
	    AND    rp.time_recipient_id = hasv.time_recipient_id
	    )
   	    AND per.person_id = tsum.resource_id
	    AND sysdate between per.effective_start_date and per.effective_end_date
	    AND hds.data_set_id =p_data_set_id
	    AND has.application_set_id = hasv.application_set_id
	    AND has.application_set_id = hld.application_set_id
	    AND hld.time_building_block_id = det.time_building_block_id
	    AND hld.object_version_number = det.object_version_number
	    AND det.parent_building_block_id = day.time_building_block_id
	    AND det.parent_building_block_ovn = day.object_version_number
            AND (    (    det.date_to = hr_general.end_of_time
                     )
                  OR (     det.date_to <> hr_general.end_of_time
                       AND EXISTS  (SELECT 1
	    			    FROM   hxc_transaction_details txnd1,
	    			           hxc_transactions txn1,
	    			           hxc_retrieval_processes rp1
	    			   WHERE  txn1.type = 'RETRIEVAL'
	    			   AND    txn1.status = 'SUCCESS'
	    			   AND    txnd1.status = 'SUCCESS'
	    			   AND    txnd1.time_building_block_id = hld.time_building_block_id
	    			   AND    txnd1.time_building_block_ovn < hld.object_version_number
	    			   AND    txnd1.transaction_id = txn1.transaction_id
	    			   AND    decode(txn1.transaction_process_id,-1,
	    			                                            l_bee_retrieval,
	    			                                            txn1.transaction_process_id) = rp1.retrieval_process_id
	    			   AND    rp1.time_recipient_id = hasv.time_recipient_id
	    			   )
	    	      )
	    	 )
	    AND day.parent_building_block_id = tsum.timecard_id
	    AND day.parent_building_block_ovn = tsum.timecard_ovn
	    --AND det.data_set_id = hds.data_set_id
	    --AND day.data_set_id = det.data_set_id
	    --AND tsum.data_set_id = day.data_set_id
	    AND det.scope = 'DETAIL'
	    AND day.scope = 'DAY'
	    AND tsum.stop_time BETWEEN hds.start_date AND hds.end_date
	    AND tsum.approval_status<>'ERROR'
	    ORDER BY tsum.approval_status,tsum.start_time,tsum.resource_id;

Line: 616

        SELECT/*+ INDEX(hds HXC_DATA_SETS_PK)
	          INDEX(tsum HXC_TIMECARD_SUMMARY_N1)
	          INDEX(day HXC_TIME_BUILDING_BLOCKS_FK3)
	          INDEX(det HXC_TIME_BUILDING_BLOCKS_FK3)
	          INDEX(per PER_PEOPLE_F_PK) */
	  DISTINCT
	    tsum.resource_id,
	    tsum.start_time,
	    tsum.stop_time,
	    tsum.approval_status,
	    nvl(per.employee_number,'Employee Number Unkown') employee_number,
	    nvl(per.full_name,'Full Name Unknown') full_name
	FROM hxc_data_sets hds,
	     hxc_timecard_summary tsum,
	     hxc_time_building_blocks day,
	     hxc_time_building_blocks det,
	     hxc_latest_details hld,
	     hxc_application_sets_v has,
	     hxc_application_set_comps_v hasv,
             per_all_people_f per
	WHERE
	NOT EXISTS
	           (SELECT   /*+ INDEX(rp HXC_RETRIEVAL_PROCESSES_PK) */
	                     1
	              FROM   hxc_transaction_details txnd,
	                     hxc_transactions txn,
	                     hxc_retrieval_processes rp
	              WHERE  txn.type = 'RETRIEVAL'
	                AND    txn.status = 'SUCCESS'
	                AND    txnd.status = 'SUCCESS'
	                AND    txnd.time_building_block_id = hld.time_building_block_id
	                AND    txnd.time_building_block_ovn = hld.object_version_number
	                AND    txnd.transaction_id = txn.transaction_id
	                AND    DECODE(txn.transaction_process_id,-1,
	                                                         l_bee_retrieval,
	                                                         txn.transaction_process_id) = rp.retrieval_process_id
	                AND    rp.time_recipient_id = hasv.time_recipient_id
	              )
   	    AND per.person_id = tsum.resource_id
	    AND SYSDATE BETWEEN per.effective_start_date
	                    AND per.effective_end_date
	    AND hds.data_set_id =p_data_set_id
	    AND has.application_set_id = hasv.application_set_id
	    AND has.application_set_id = hld.application_set_id
	    AND hld.time_building_block_id = det.time_building_block_id
	    AND hld.object_version_number = det.object_version_number
	    AND det.parent_building_block_id = day.time_building_block_id
	    AND det.parent_building_block_ovn = day.object_version_number
            AND (    (    det.date_to = hr_general.end_of_time
                     )
                  OR (     det.date_to <> hr_general.end_of_time
                       AND EXISTS  (SELECT /*+ INDEX(rp1 HXC_RETRIEVAL_PROCESSES_PK) */
                                           1
	    			    FROM   hxc_transaction_details txnd1,
	    			           hxc_transactions txn1,
	    			           hxc_retrieval_processes rp1
	    			   WHERE  txn1.type = 'RETRIEVAL'
	    			   AND    txn1.status = 'SUCCESS'
	    			   AND    txnd1.status = 'SUCCESS'
	    			   AND    txnd1.time_building_block_id = hld.time_building_block_id
	    			   AND    txnd1.time_building_block_ovn < hld.object_version_number
	    			   AND    txnd1.transaction_id = txn1.transaction_id
	    			   AND    decode(txn1.transaction_process_id,-1,
	    			                                            l_bee_retrieval,
	    			                                            txn1.transaction_process_id) = rp1.retrieval_process_id
	    			   AND    rp1.time_recipient_id = hasv.time_recipient_id
	    			   )
	    	      )
	    	 )
	    AND day.parent_building_block_id = tsum.timecard_id
	    AND day.parent_building_block_ovn = tsum.timecard_ovn
	    --AND det.data_set_id = hds.data_set_id
	    --AND day.data_set_id = det.data_set_id
	    AND tsum.data_set_id = hds.data_set_id
	    AND det.scope = 'DETAIL'
	    AND day.scope = 'DAY'
	    AND tsum.stop_time BETWEEN hds.start_date AND hds.end_date
	    AND tsum.approval_status<>'ERROR'
	    ORDER BY tsum.approval_status,tsum.start_time,tsum.resource_id;

Line: 702

	SELECT  tsum.resource_id,
		tsum.start_time,
		tsum.stop_time,
		nvl(per.employee_number,'Employee Number Unkown') employee_number,
	        nvl(per.full_name,'Full Name Unknown') full_name
	   FROM hxc_timecard_summary tsum,
    	        hxc_data_sets d,
		per_all_people_f per
		WHERE tsum.approval_status ='ERROR'
		AND per.person_id = tsum.resource_id
		AND sysdate between per.effective_start_date and per.effective_end_date
		AND tsum.stop_time BETWEEN d.start_date AND d.end_date
		AND d.data_set_id = p_data_set_id
	ORDER BY tsum.start_time,tsum.resource_id;

Line: 721

SELECT
       tsum.resource_id,
       tsum.start_time,
       tsum.stop_time,
       nvl(per.employee_number,'Employee Number Unkown') employee_number,
       nvl(per.full_name,'Full Name Unknown') full_name
  FROM hxc_timecard_summary tsum,
       hxc_app_period_summary apsum,
       hxc_tc_ap_links tap,
       wf_notifications wfn,
       wf_item_activity_statuses wias,
       wf_item_attribute_values wiav,
       per_all_people_f per
 WHERE tsum.approval_status = 'SUBMITTED'
   AND per.person_id = tsum.resource_id
   AND sysdate between per.effective_start_date and per.effective_end_date
   AND tsum.data_set_id =p_data_set_id
   AND apsum.application_period_id = tap.application_period_id
   AND tsum.timecard_id = tap.timecard_id
   AND apsum.approval_item_key is null
   AND wias.item_key = wiav.item_key
   AND tap.application_period_id=wiav.number_value
   AND wias.notification_id=wfn.notification_id
   AND wias.item_key=wiav.item_key
   AND wfn.status='OPEN'
   AND wias.item_type='HXCEMP'
   AND wiav.item_type = 'HXCEMP'
   AND wiav.name = 'APP_BB_ID'
   AND apsum.notification_status = 'NOTIFIED'
   AND apsum.approval_status = 'SUBMITTED'
   AND wfn.message_name in('TIMECARD_APPROVAL_INLINE','TIMECARD_APPROVAL')
   AND wfn.message_type='HXCEMP'
UNION
   SELECT
       tsum.resource_id,
       tsum.start_time,
       tsum.stop_time,
       nvl(per.employee_number,'Employee Number Unkown') employee_number,
       nvl(per.full_name,'Full Name Unknown') full_name
  FROM hxc_timecard_summary tsum,
       hxc_app_period_summary apsum,
       hxc_tc_ap_links tap,
       wf_notifications wfn,
       wf_item_activity_statuses wias,
       per_all_people_f per
 WHERE tsum.approval_status = 'SUBMITTED'
   AND per.person_id = tsum.resource_id
   AND sysdate between per.effective_start_date and per.effective_end_date
   AND tsum.data_set_id =p_data_set_id
   AND apsum.application_period_id = tap.application_period_id
   AND tsum.timecard_id = tap.timecard_id
   AND apsum.approval_item_key is not null
   AND wias.item_key = apsum.approval_item_key
   AND wias.notification_id=wfn.notification_id
   AND wfn.status='OPEN'
   AND wias.item_type='HXCEMP'
   AND apsum.notification_status = 'NOTIFIED'
   AND apsum.approval_status = 'SUBMITTED'
   AND wfn.message_name = 'TIMECARD_APPROVAL_INLINE'
   AND wfn.message_type='HXCEMP'
ORDER BY 2,1;

Line: 785

SELECT retrieval_process_id
FROM hxc_retrieval_processes
WHERE name = 'BEE Retrieval Process';

Line: 928

	l_unretrieved_tctab.DELETE;

Line: 1017

        l_timecard_notifications_tab.DELETE;

Line: 1024

  UPDATE hxc_data_sets
  SET    validation_status = l_validation_status
  WHERE data_set_id = p_data_set_id;

Line: 1042

SELECT distinct resource_id
FROM hxc_time_building_blocks
WHERE data_set_id = p_data_set_id
AND scope = 'TIMECARD';