APPS.PAY_SA_GENERAL SQL Statements

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

Line: 42

			Select person_id
			Into l_person_id
			From PER_ALL_ASSIGNMENTS_F
			Where ASSIGNMENT_ID = p_assignment_id
			AND p_date_earned between EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 53

			SELECT NATIONALITY
			INTO   l_nat_cd
			FROM   per_all_people_f
			WHERE  person_id = l_person_id
			AND    p_date_earned BETWEEN effective_start_date AND effective_end_date;

Line: 174

   SELECT count(DISTINCT paf.assignment_id)
   FROM   per_all_assignments_f paf
         ,per_assignment_status_types pas
   WHERE  paf.assignment_type    = 'E'
   AND    paf.PERSON_ID          = p_person_id
   AND    p_effective_date between effective_start_date and effective_end_date
   AND    paf.assignment_status_type_id = pas.assignment_status_type_id
   AND    pas.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');

Line: 197

 SELECT SUM(ppp.value)
 FROM  per_assignments_f            paf
       ,per_people_f                 pef
       ,pay_pre_payments             ppp
       ,pay_assignment_actions       paa
       ,pay_payroll_actions          ppa
 WHERE  paa.payroll_action_id          =
        pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
 AND    paa.pre_payment_id             = ppp.pre_payment_id
 AND    paa.payroll_action_id          = ppa.payroll_action_id
 AND    paa.assignment_id              = paf.assignment_id
 AND    paf.person_id                  = pef.person_id
 AND    ppp.value                      > 0
 AND    ppa.effective_date BETWEEN paf.effective_start_date
                               AND paf.effective_end_date
 AND    ppa.effective_date BETWEEN pef.effective_start_date
                               AND pef.effective_end_date;

Line: 224

 SELECT COUNT(*)
 FROM  per_assignments_f            paf
       ,per_people_f                 pef
       ,pay_pre_payments             ppp
       ,pay_assignment_actions       paa
       ,pay_payroll_actions          ppa
 WHERE  paa.payroll_action_id          =
        pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
 AND    paa.pre_payment_id             = ppp.pre_payment_id
 AND    paa.payroll_action_id          = ppa.payroll_action_id
 AND    paa.assignment_id              = paf.assignment_id
 AND    paf.person_id                  = pef.person_id
 AND    ppp.value                      > 0
 AND    ppa.effective_date BETWEEN paf.effective_start_date
                               AND paf.effective_end_date
 AND    ppa.effective_date BETWEEN pef.effective_start_date
                               AND pef.effective_end_date;

Line: 260

  SELECT  u.creator_id
  FROM    ff_user_entities  u,
                 ff_database_items d
  WHERE   d.user_name = p_def_bal_name
  AND      u.user_entity_id = d.user_entity_id
  AND      u.legislation_code = 'SA'
  AND      u.business_group_id is null
  AND      u.creator_type = 'B';

Line: 270

  SELECT paa.assignment_action_id
                ,ppa.date_earned
  FROM pay_assignment_actions paa
             ,pay_payroll_actions ppa
             ,pay_run_results prr
             ,pay_element_types_f pet
  WHERE paa.assignment_id = p_assignment_id
  AND   paa.assignment_action_id = prr.assignment_action_id
  AND   paa.payroll_action_id = ppa.payroll_action_id
  AND   paa.action_status = 'C'
  AND   ppa.action_status = 'C'
  AND   ppa.action_type in ('R','Q')
  AND   prr.element_type_id = pet.element_type_id
  AND   pet.element_name ='GOSI'
  AND   p_date_earned between pet.effective_start_date and pet.effective_end_date
  AND   prr.status = 'P'
  AND   paa.assignment_action_id < p_assignment_action_id
  --AND   ppa.date_earned >= ADD_MONTHS(TRUNC(p_date_earned,'YYYY'),-12)
  ORDER BY paa.assignment_action_id DESC;