APPS.PAY_DK_SICKPAY_PKG SQL Statements

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

Line: 38

  SELECT eev.screen_entry_value  screen_entry_value
  FROM   per_all_assignments_f      asg
        ,per_all_people_f           per
        ,pay_element_links_f        el
        ,pay_element_types_f        et
        ,pay_input_values_f         iv
        ,pay_element_entries_f      ee
        ,pay_element_entry_values_f eev
   WHERE  asg.assignment_id     = p_assignment_id
     AND  per.person_id         = asg.person_id
     AND  et.element_name       IN ('Absent Sick','Absent Holiday')
     AND  et.legislation_code   = 'DK'
     AND  iv.element_type_id    = et.element_type_id
     AND  iv.name               = p_input_value
     AND  el.business_group_id  = per.business_group_id
     AND  el.element_type_id    = et.element_type_id
     AND  ee.element_link_id    = el.element_link_id
     and  ee.assignment_id      = asg.assignment_id
     AND  eev.element_entry_id  = ee.element_entry_id
     AND  eev.input_value_id    = iv.input_value_id
     AND  ((p_period_start_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
           OR
	   (p_period_end_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
	   OR
	   (p_period_start_date < ee.effective_start_date AND p_period_end_date > ee.effective_end_date))
    ORDER BY ee.element_entry_id;

Line: 178

   SELECT ee.element_entry_id element_entry_id
          , eev1.screen_entry_value  screen_entry_value
          , iv1.name
   FROM   per_all_assignments_f      asg1
         ,per_all_assignments_f      asg2
         ,per_all_people_f           per
         ,pay_element_links_f        el
         ,pay_element_types_f        et
         ,pay_input_values_f         iv1
         ,pay_element_entries_f      ee
         ,pay_element_entry_values_f eev1
   WHERE  asg1.assignment_id    = p_assignment_id
     AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
     AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
     AND  per.person_id         = asg1.person_id
     AND  asg2.person_id        = per.person_id
     AND  et.element_name       = 'Override Sickness Duration'
     AND  et.legislation_code   = 'DK'
     AND  iv1.element_type_id   = et.element_type_id
     /*Bug 5047360 fix */
    /* AND  iv1.name            in ('Start Date', 'End Date', 'Sick Days', 'Sick Hours')*/
     AND  iv1.name              in ('Start Date', 'End Date', 'Sick Days','Start Time','End Time', 'Sick Hours')
     AND  el.business_group_id  = per.business_group_id
     AND  el.element_type_id    = et.element_type_id
     AND  ee.assignment_id      = asg2.assignment_id
     AND  ee.element_link_id    = el.element_link_id
     AND  eev1.element_entry_id = ee.element_entry_id
     AND  eev1.input_value_id   = iv1.input_value_id
     AND  p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
     AND  p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
     ORDER BY ee.element_entry_id, DECODE(iv1.name, 'Start Date', 1,'End Date', 2, 'Sick Days', 3,'Start Time', 4,'End Time', 5,'Sick Hours', 6) ;

Line: 313

	 SELECT org_information1
	 FROM hr_organization_information
	 WHERE organization_id = p_org_id
	 and org_information_context = 'DK_SICKPAY_DEFAULTS'
	 AND p_effective_date BETWEEN fnd_date.canonical_to_date( org_information2) and fnd_date.canonical_to_date( org_information3)
	 order by org_information2;

Line: 343

SELECT
    NVL(hsck.segment18,'N')
FROM
	per_all_assignments_f paaf
	,hr_soft_coding_keyflex hsck
WHERE
	paaf.assignment_id = p_assignment_id
	AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;

Line: 397

  SELECT eev.screen_entry_value  screen_entry_value
  FROM   per_all_assignments_f      asg
        ,per_all_people_f           per
        ,pay_element_links_f        el
        ,pay_element_types_f        et
        ,pay_input_values_f         iv
        ,pay_element_entries_f      ee
        ,pay_element_entry_values_f eev
   WHERE  asg.assignment_id     = p_assignment_id
     AND  per.person_id         = asg.person_id
     AND  et.element_name       IN ('Absent Sick','Absent Holiday')
     AND  et.legislation_code   = 'DK'
     AND  iv.element_type_id    = et.element_type_id
     AND  iv.name               = p_input_value
     AND  el.business_group_id  = per.business_group_id
     AND  el.element_type_id    = et.element_type_id
     AND  ee.element_link_id    = el.element_link_id
     and  ee.assignment_id      = asg.assignment_id
     AND  eev.element_entry_id  = ee.element_entry_id
     AND  eev.input_value_id    = iv.input_value_id
     AND  ((p_period_start_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
           OR
	   (p_period_end_date BETWEEN ee.effective_start_date AND ee.effective_end_date)
	   OR
	   (p_period_start_date < ee.effective_start_date AND p_period_end_date > ee.effective_end_date))
    ORDER BY ee.element_entry_id;