APPS.PO_STORE_TIMECARD_PKG_GRP SQL Statements

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

Line: 153

       if (upper(p_action) = 'UPDATE' OR upper(p_action) = 'DELETE') then
           --Get the mandatory data to create a new row with the above action.
           l_stage := 'Getting ready to UPDATE or DELETE';

Line: 157

           select VENDOR_ID, VENDOR_SITE_ID, PO_HEADER_ID, PO_NUMBER, PO_LINE_ID, PO_LINE_NUMBER,
                  ORG_ID, PO_CREATION_DATE,
	     	         PO_CONTRACTOR_FULL_NAME, TC_UOM, TC_APPROVED_DATE,
	     	         TC_START_DATE, TC_ENTRY_DATE, TC_END_DATE,
	     	         CONTINGENT_WORKER_ID, LINE_RATE_TYPE, LINE_RATE, LINE_RATE_CURRENCY
	     	    into l_vendor_id, l_vendor_site_id, l_po_header_id, l_po_number,
	     	         l_po_line_id, l_po_line_number,
	     	         l_org_id, l_po_creation_date,
	     	         l_contractor_full_name, l_tc_uom, l_tc_approved_date,
	     	         l_tc_start_date, l_tc_entry_date, l_tc_end_date,
	     	         l_contingent_worker_id, l_line_rate_type, l_line_rate, l_po_currency
	     	    from po_retrieved_timecards
  	           where tc_detail_id = p_tc_detail_id and tc_day_id = p_tc_day_id and
  	                 tc_id = p_tc_id and rownum = 1;  --all we need is mandatory data; so first row is sufficient.

Line: 175

	   l_stage := 'Got data for UPDATE or DELETE';

Line: 177

  	   IF ( (NOT (upper(p_action) = 'UPDATE' OR upper(p_action) = 'DELETE') ) OR
 	          l_get_fresh = 'Y') THEN
  	        l_action := 'I';

Line: 180

  	        l_stage := 'Getting ready for INSERT';

Line: 182

  	       	 select po_header_id, currency_code, creation_date,
	   	            vendor_id, vendor_site_id
	           into l_po_header_id, l_po_currency, l_po_creation_date,
	   	            l_vendor_id, l_vendor_site_id
	           from po_headers_all
  	          where segment1 = p_po_num and org_id = p_org_id;

Line: 190

	          SELECT po_line_id
	            INTO l_PO_LINE_ID
	            FROM  po_lines_all pla
	           WHERE pla.po_header_id = l_po_header_id
                 AND pla.line_num = p_po_line_number;

Line: 201

  	             SELECT ptlv.rate_value
	               INTO l_line_rate
	               FROM po_temp_labor_rates_v ptlv, po_lines_all pla
                  WHERE ptlv.po_line_id = pla.po_line_id
                    AND ptlv.asg_rate_type = p_line_rate_type
                    AND pla.po_header_id = l_po_header_id
                    AND pla.line_num = p_po_line_number;

Line: 218

  	             select first_name || last_name contractor_full_name,
	   	                first_name, last_name
	     	       into l_contractor_full_name,
	   	                l_contractor_first_name, l_contractor_last_name
	   	           from per_all_people_f
  	              where person_id = p_contingent_worker_id and
  	                    sysdate between effective_start_date and effective_end_date;

Line: 229

  	             select SEGMENT1
  	               into l_project_name
  	               from PA_PROJECTS_ALL
  	              where project_id = p_project_id;

Line: 236

  	                 select TASK_NUMBER
  	                   into l_task_number
  	                   from PA_TASKS
  	                  where project_id = p_project_id and task_id = p_task_id;

Line: 243

          /* set the local vars right to get ready to insert */
          l_stage := 'Getting some misc data before INSERT';

Line: 257

       end if;  --end of if insert

Line: 259

  	if (upper(p_action) = 'UPDATE') then
  	  l_stage := 'updating';

Line: 262

  	  update PO_RETRIEVED_TIMECARDS
  	  set tc_time_received = p_tc_time_received, tc_comment_text = p_tc_comment_text,
  	      line_rate_type = p_line_rate_type, line_rate =  p_line_rate
  	  where tc_detail_id = p_tc_detail_id and tc_day_id = p_tc_day_id and tc_id = p_tc_id;

Line: 270

  	elsif (upper(p_action) = 'DELETE') then
  	  l_stage := 'deleting';

Line: 274

  	  delete PO_RETRIEVED_TIMECARDS
  	  where tc_detail_id = p_tc_detail_id and tc_day_id = p_tc_day_id and tc_id = p_tc_id;

Line: 279

    select po_timecards_entry_s.nextval into l_tc_entry_seq  from dual;

Line: 280

    l_stage := 'inserting';

Line: 282

  	insert into PO_RETRIEVED_TIMECARDS
  	(
  	  PO_HEADER_ID,
	  PO_NUMBER,
	  PO_LINE_ID,
	  PO_LINE_NUMBER,
	  ORG_ID,
	  PO_CREATION_DATE,
	  PO_CONTRACTOR_FULL_NAME,
	  PROJECT_ID,
	  PROJECT_NAME,
	  TASK_ID,
	  TASK_NAME,
	  TC_ID,
	  TC_DAY_ID,
	  TC_DETAIL_ID,
	  TC_SCOPE,
	  TC_UOM,
	  TC_START_DATE,
	  TC_END_DATE,
	  TC_ENTRY_DATE,
	  TC_TIME_RECEIVED,
	  TC_SUBMISSION_DATE,
	  TC_APPROVED_DATE,
	  TC_APPROVAL_STATUS,
	  CONTINGENT_WORKER_ID,
	  TC_COMMENT_TEXT,
	  LINE_RATE_TYPE,
	  LINE_RATE,
	  LINE_RATE_CURRENCY,
	  VENDOR_ID,
	  VENDOR_SITE_ID,
	  VENDOR_CONTACT_ID,
	  PO_CONTRACTOR_FIRST_NAME,
      PO_CONTRACTOR_LAST_NAME,
      INTERFACE_TRANSACTION_ID,
      ACTION_FLAG,
      TC_ENTRY_SEQUENCE
  	)
  	values
  	(
  	 l_PO_HEADER_ID,
	 l_PO_NUMBER,
	 l_PO_LINE_ID,
	 l_PO_LINE_NUMBER,
	 l_ORG_ID,
     l_po_creation_date,
	 l_CONTRACTOR_FULL_NAME,
     p_PROJECT_ID,
	 l_PROJECT_NAME,
	 p_TASK_ID,
	 l_TASK_NUMBER,
	 p_TC_ID,
	 p_TC_DAY_ID,
	 p_TC_DETAIL_ID,
     'DETAIL',
	 l_TC_UOM,
	 l_TC_START_DATE,
	 l_TC_END_DATE,
	 l_TC_ENTRY_DATE,
	 p_TC_TIME_RECEIVED,
	 p_TC_SUBMISSION_DATE,
	 l_tc_approved_date,
	 p_TC_APPROVAL_STATUS,
	 l_CONTINGENT_WORKER_ID,
	 p_TC_COMMENT_TEXT,
	 l_LINE_RATE_TYPE,
	 l_LINE_RATE,
	 l_PO_CURRENCY,
	 l_VENDOR_ID,
	 l_VENDOR_SITE_ID,
	 p_VENDOR_CONTACT_ID,
	 l_contractor_first_name,
     l_contractor_last_name,
     p_interface_transaction_id,
     l_action,
     l_tc_entry_seq
  	);

Line: 386

   select /*+ PO_RETRIEVED_TIMECARDS_N6 */ tc_id, tc_day_id, tc_detail_id,
          action_flag, tc_time_received,
          tc_comment_text, interface_transaction_id
     from po_retrieved_timecards
     where action_flag in ('I', 'U', 'D')
     order by TC_ENTRY_SEQUENCE;

Line: 404

 delete /*+ PO_RETRIEVED_TIMECARDS_N6 */po_retrieved_timecards prt
    where action_flag in ('I', 'U', 'D') and
          not exists (select interface_transaction_id from rcv_transactions
                      where interface_transaction_id = prt.interface_transaction_id);

Line: 422

       update po_retrieved_timecards
       set action_flag = 'P'
       where tc_id = l_tc_id and
             tc_day_id = l_tc_day_id and
             tc_detail_id = l_tc_detail_id and
             action_flag = 'I';

Line: 430

         update po_retrieved_timecards
            set tc_time_received = l_tc_time_received,
                tc_comment_text = l_tc_comment_text
          where tc_id = l_tc_id and
                tc_day_id = l_tc_day_id and
                tc_detail_id = l_tc_detail_id and
                action_flag = 'P';

Line: 438

         update po_retrieved_timecards
            set action_flag = 'DP'
         where tc_id = l_tc_id and
               tc_day_id = l_tc_day_id and
               tc_detail_id = l_tc_detail_id and
               action_flag = 'P';

Line: 451

 delete po_retrieved_timecards
  where action_flag in ('U', 'D');

Line: 528

 if (upper(p_action) = 'UPDATE') then
    forall i in p_rtrvd_tcs.po_number.first..p_rtrvd_tcs.po_number.last
       SAVE EXCEPTIONS
  	  update PO_RETRIEVED_TIMECARDS
  	  set tc_time_received = p_rtrvd_tcs.tc_time_received(i),
  	      tc_comment_text = p_rtrvd_tcs.tc_comment_text(i),
  	      line_rate_type = p_rtrvd_tcs.line_rate_type(i),
  	      line_rate =  p_rtrvd_tcs.line_rate(i)
  	  where tc_detail_id = p_rtrvd_tcs.tc_detail_id(i) and
  	        tc_day_id = p_rtrvd_tcs.tc_day_id(i) and
  	        tc_id = p_rtrvd_tcs.tc_id(i);

Line: 540

 elsif (upper(p_action) = 'DELETE') then
 forall i in p_rtrvd_tcs.po_number.first..p_rtrvd_tcs.po_number.last
     SAVE EXCEPTIONS
  	  delete PO_RETRIEVED_TIMECARDS
  	  where tc_detail_id = p_rtrvd_tcs.tc_detail_id(i) and
  	        tc_day_id = p_rtrvd_tcs.tc_day_id(i) and
  	        tc_id = p_rtrvd_tcs.tc_id(i);

Line: 552

    insert into PO_RETRIEVED_TIMECARDS
  	(
  	  PO_HEADER_ID,
	  PO_NUMBER,
	  PO_LINE_ID,
	  PO_LINE_NUMBER,
	  ORG_ID,
	  PO_APPROVED_DATE,
	  PO_CONTRACTOR_FULL_NAME,
	  PROJECT_ID,
	  PROJECT_NAME,
	  TASK_ID,
	  TASK_NAME,
	  TC_ID,
	  TC_DAY_ID,
	  TC_DETAIL_ID,
	  TC_SCOPE,
	  TC_UOM,
	  TC_START_DATE,
	  TC_END_DATE,
	  TC_ENTRY_DATE,
	  TC_TIME_RECEIVED,
	  TC_SUBMISSION_DATE,
	  TC_APPROVED_DATE,
	  TC_APPROVAL_STATUS,
	  CONTINGENT_WORKER_ID,
	  TC_COMMENT_TEXT,
	  LINE_RATE_TYPE,
	  LINE_RATE,
	  LINE_RATE_CURRENCY,
	  VENDOR_ID,
	  VENDOR_SITE_ID,
	  VENDOR_CONTACT_ID,
	  PO_CONTRACTOR_FIRST_NAME,
      PO_CONTRACTOR_LAST_NAME
  	)
  	values
  	(
  	 p_rtrvd_tcs.PO_HEADER_ID(i),
	 p_rtrvd_tcs.PO_NUMBER(i),
	 p_rtrvd_tcs.PO_LINE_ID(i),
	 p_rtrvd_tcs.PO_LINE_NUMBER(i),
	 p_rtrvd_tcs.ORG_ID(i),
     p_rtrvd_tcs.po_CREATION_DATE(i),
	 p_rtrvd_tcs.CONTRACTOR_FULL_NAME(i),
     p_rtrvd_tcs.PROJECT_ID(i),
	 p_rtrvd_tcs.PROJECT_NAME(i),
	 p_rtrvd_tcs.TASK_ID(i),
	 p_rtrvd_tcs.TASK_NUMBER(i),
	 p_rtrvd_tcs.TC_ID(i),
	 p_rtrvd_tcs.TC_DAY_ID(i),
	 p_rtrvd_tcs.TC_DETAIL_ID(i),
     'DETAIL',
	 p_rtrvd_tcs.TC_UOM(i),
	 p_rtrvd_tcs.TC_START_DATE(i),
	 p_rtrvd_tcs.TC_END_DATE(i),
	 p_rtrvd_tcs.TC_ENTRY_DATE(i),
	 p_rtrvd_tcs.TC_TIME_RECEIVED(i),
	 p_rtrvd_tcs.TC_SUBMISSION_DATE(i),
	 p_rtrvd_tcs.TC_APPROVAL_DATE(i),
	 p_rtrvd_tcs.TC_APPROVAL_STATUS(i),
	 p_rtrvd_tcs.CONTINGENT_WORKER_ID(i),
	 p_rtrvd_tcs.TC_COMMENT_TEXT(i),
	 p_rtrvd_tcs.LINE_RATE_TYPE(i),
	 p_rtrvd_tcs.LINE_RATE(i),
	 p_rtrvd_tcs.PO_CURRENCY(i),
	 p_rtrvd_tcs.VENDOR_ID(i),
	 p_rtrvd_tcs.VENDOR_SITE_ID(i),
	 p_rtrvd_tcs.VENDOR_CONTACT_ID(i),
	 p_rtrvd_tcs.contractor_first_name(i),
     p_rtrvd_tcs.contractor_last_name(i)
  	);