APPS.PER_SALADMIN_UTILITY SQL Statements

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

Line: 9

   Select Nvl(Gsp_Allow_Override_Flag,'Y')
     From Ben_Pgm_f Pgm,
          Per_all_assignments_F paa
    Where paa.Assignment_Id = p_assignment_id
      and p_effective_date between paa.Effective_Start_Date and paa.Effective_End_Date
      and paa.GRADE_LADDER_PGM_ID     is Not NULL
      and pgm.pgm_id = paa.Grade_Ladder_Pgm_Id
      and p_effective_date between Pgm.Effective_Start_Date and Pgm.Effective_End_Date
      and Pgm_typ_Cd = 'GSP'
      and Pgm_stat_Cd = 'A'
      and Update_Salary_Cd = 'SALARY_BASIS';

Line: 54

select ppb.grade_annualization_factor
	  ,ppb.pay_basis
	  ,ppb.rate_basis
	  ,pet.input_currency_code as element_currency_code
	  ,PER_SALADMIN_UTILITY.get_grade_currency(paa.grade_id,ppb.rate_id,p_effective_date,paa.business_group_id) as grade_rate_currency_code
      ,ben_cwb_person_info_pkg.get_grd_min_val(paa.grade_id,ppb.rate_id,p_effective_date)
from per_all_assignments_f paa
	,per_pay_bases ppb
	,pay_input_values_f piv
	,pay_element_types_f pet
where paa.assignment_id = p_assignment_id
and   paa.pay_basis_id = ppb.pay_basis_id
and   ppb.input_value_id = piv.input_value_id
and   piv.element_type_id = pet.element_type_id
and   p_effective_date between paa.effective_start_date and paa.effective_end_date;

Line: 112

select ppb.grade_annualization_factor
	  ,ppb.pay_basis
	  ,ppb.rate_basis
	  ,pet.input_currency_code as element_currency_code
	  ,PER_SALADMIN_UTILITY.get_grade_currency(paa.grade_id,ppb.rate_id,p_effective_date,paa.business_group_id) as grade_rate_currency_code
      ,ben_cwb_person_info_pkg.get_grd_max_val(paa.grade_id,ppb.rate_id,p_effective_date)
from per_all_assignments_f paa
	,per_pay_bases ppb
	,pay_input_values_f piv
	,pay_element_types_f pet
where paa.assignment_id = p_assignment_id
and   paa.pay_basis_id = ppb.pay_basis_id
and   ppb.input_value_id = piv.input_value_id
and   piv.element_type_id = pet.element_type_id
and   p_effective_date between paa.effective_start_date and paa.effective_end_date;

Line: 206

     select sal_review_period,
            sal_review_period_frequency
     from   per_all_assignments_f
     where  assignment_id = p_assignment_id
     and    business_group_id + 0 = p_business_group_id
     and    p_change_date between effective_start_date
                          and nvl(effective_end_date, hr_general.end_of_time);

Line: 241

         SELECT piv.uom
           FROM pay_element_types_f pet,
                per_all_assignments_f paaf,
                pay_input_values_f piv,
                per_pay_bases ppb,
                per_pay_proposals ppp
          WHERE ppp.pay_proposal_id = p_pay_proposal_id
          and   paaf.assignment_id = ppp.assignment_id
          and   ppp.change_date BETWEEN paaf.effective_start_date
                and paaf.effective_end_date
          and   ppb.pay_basis_id = paaf.pay_basis_id
          and   ppb.input_value_id = piv.input_value_id
          and   ppp.change_date BETWEEN piv.effective_start_date
                and piv.effective_end_date
          and   piv.element_type_id = pet.element_type_id
          and   ppp.change_date BETWEEN pet.effective_start_date
                and pet.effective_end_date;

Line: 270

      select max(change_date)
      from per_pay_proposals pro
      where pro.assignment_id = p_assignment_id
      and pro.change_date <  p_change_date ;

Line: 294

select change_date
from per_pay_proposals
where assignment_id = p_assignment_id
and multiple_components = 'Y'
and change_date =
(select min(change_date)
from per_pay_proposals
where assignment_id = p_assignment_id
and change_date > p_session_date);

Line: 328

select pay_proposal_id, change_date,date_to
from per_pay_proposals
where assignment_id = p_assignment_id
and approved = 'Y';

Line: 335

select min(change_date)
from per_pay_proposals
where change_date > p_change_date
and assignment_id = p_assignment_id;

Line: 342

select approved
from per_pay_proposals
where change_date = p_change_date
and assignment_id = p_assignment_id;

Line: 349

select effective_end_date
From pay_element_entries_f
where assignment_id = p_assignment_id
and creator_type = 'SP'
and creator_id = p_pay_proposal_id
and effective_start_date =p_change_date;

Line: 406

        hr_utility.set_location('About to update', 70);

Line: 409

        update per_pay_proposals
        set date_to =l_element_entry_end_date
        where pay_proposal_id = i.pay_proposal_id;

Line: 414

        hr_utility.set_location('Updated successfully', 70);

Line: 433

  select min(effective_start_date)
  from per_all_assignments_f
  where assignment_id = p_assignment_id;

Line: 459

      select pro.proposed_salary_n
      from per_pay_proposals pro
      where pro.assignment_id = p_assignment_id
      and pro.change_date =(select max(pro2.change_date)
                                  from per_pay_proposals pro2
                                  where pro2.assignment_id = p_assignment_id
                                  and pro2.change_date < (select change_date from per_pay_proposals
                            where pay_proposal_id =p_proposal_id));

Line: 486

    select proposed_salary_n
from per_pay_proposals
where assignment_id = p_assignment_id
and p_effective_date between nvl(change_date,hr_general.start_of_time) and nvl(date_to,hr_general.end_of_time);

Line: 507

  SELECT PAF.PAY_BASIS_ID
  FROM PER_ALL_ASSIGNMENTS_F PAF
  WHERE PAF.ASSIGNMENT_ID=p_assignment_id
  AND p_effective_date  BETWEEN
  PAF.EFFECTIVE_START_DATE AND
  PAF.EFFECTIVE_END_DATE;

Line: 515

  SELECT HR_GENERAL.DECODE_LOOKUP('PAY_BASIS',PPB.PAY_BASIS)
  ,PPB.PAY_ANNUALIZATION_FACTOR
  FROM PAY_ELEMENT_TYPES_F PET
  ,PAY_INPUT_VALUES_F       PIV
  ,PER_PAY_BASES            PPB
  WHERE PPB.PAY_BASIS_ID=L_PAY_BASIS_ID
  AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
  AND p_effective_date  BETWEEN
  PIV.EFFECTIVE_START_DATE AND
  PIV.EFFECTIVE_END_DATE
  AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
  AND p_effective_date  BETWEEN
  PET.EFFECTIVE_START_DATE AND
  PET.EFFECTIVE_END_DATE;

Line: 531

  select tpt.number_per_fiscal_year
  from pay_all_payrolls_f prl
  ,    per_all_assignments_f paf
  ,    per_time_period_types tpt
  where paf.assignment_id=p_assignment_id
  and p_effective_date between paf.effective_start_date
      and paf.effective_end_date
  and paf.payroll_id=prl.payroll_id
  and p_effective_date between prl.effective_start_date
      and prl.effective_end_date
  and prl.period_type = tpt.period_type(+);

Line: 575

    select HR_GENERAL.DECODE_GRADE(paa.grade_id) as grade
	from per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date;

Line: 593

       select grdrule.currency_code
   from pay_grade_rules_f grdrule
   where grdrule.rate_id  = p_rate_id
   and   grdrule.grade_or_spinal_point_id = p_grade_id
   and   p_effective_date between grdrule.effective_start_date
                  and grdrule.effective_end_date;

Line: 619

        select pet.input_currency_code as currency_code
		from per_pay_bases ppb
			,pay_input_values_f piv
			,pay_element_types_f pet
			,per_all_assignments_f paa
		where paa.pay_basis_id = ppb.pay_basis_id
		and   ppb.input_value_id = piv.input_value_id
		and   piv.element_type_id = pet.element_type_id
		and   paa.assignment_id = p_assignment_id
		and   p_effective_date between nvl(paa.effective_start_date,hr_general.start_of_time) and nvl(paa.effective_end_date,hr_general.end_of_time)
		and  p_effective_date between nvl(pet.effective_start_date,hr_general.start_of_time) and nvl(pet.effective_end_date,hr_general.end_of_time)
		and  p_effective_date between nvl(piv.effective_start_date,hr_general.start_of_time) and nvl(piv.effective_end_date,hr_general.end_of_time);

Line: 645

        select description
        from    hr_lookups
        where   lookup_type     = p_lookup_type
        and     lookup_code     = p_lookup_code;

Line: 650

		select description
		from pay_all_payrolls_f pap
		,per_all_assignments_f paa
		,hr_lookups
		where pap.payroll_id = paa.payroll_id
		and paa.assignment_id =  p_assignment_id
        and p_effective_date between paa.effective_start_date and paa.effective_end_date
		and meaning = pap.period_type
		and lookup_type = 'PROC_PERIOD_TYPE';

Line: 660

        select nvl(DESCRIPTION,ptt.period_type)
        from PER_TIME_PERIOD_TYPES ptt
        ,pay_all_payrolls_f pap
		,per_all_assignments_f paa
		where pap.payroll_id = paa.payroll_id
		and paa.assignment_id =  p_assignment_id
        and p_effective_date between paa.effective_start_date and paa.effective_end_date
        and ptt.period_type = pap.period_type;

Line: 713

        select description
        from    hr_lookups
        where   lookup_type     = p_lookup_type
        and     lookup_code     = p_lookup_code;

Line: 732

         select    name
         from      ben_pgm_f pgm
         where     pgm_id      = p_grade_ladder_id
         and       p_effective_date between
                   pgm.effective_start_date and pgm.effective_end_date;

Line: 757

select abv.value
from per_assignment_budget_values_f abv, per_all_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and abv.assignment_id = asg.assignment_id
and asg.assignment_type in ('E', 'C')
and abv.unit = 'FTE'
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 787

         SELECT ppb.pay_annualization_factor
           FROM per_all_assignments_f paaf, per_pay_bases ppb
          WHERE paaf.assignment_id = p_assignment_id
            AND p_change_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
            AND ppb.pay_basis_id = paaf.pay_basis_id;

Line: 811

         SELECT pet.input_currency_code
           FROM pay_element_types_f pet,
                per_all_assignments_f paaf,
                pay_input_values_f piv,
                per_pay_bases ppb
          WHERE paaf.assignment_id = p_assignment_id
            AND p_change_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
            AND ppb.pay_basis_id = paaf.pay_basis_id
            AND ppb.input_value_id = piv.input_value_id
            AND p_change_date BETWEEN piv.effective_start_date
                                  AND piv.effective_end_date
            AND piv.element_type_id = pet.element_type_id
            AND p_change_date BETWEEN pet.effective_start_date
                                  AND pet.effective_end_date;

Line: 828

        Select Name
        from Fnd_Currencies_Vl
         Where Currency_Code = l_currency;

Line: 862

         SELECT pet.input_currency_code,piv.uom
           FROM pay_element_types_f pet,
                per_all_assignments_f paaf,
                pay_input_values_f piv,
                per_pay_bases ppb
          WHERE paaf.assignment_id = p_assignment_id
            AND p_change_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
            AND ppb.pay_basis_id = paaf.pay_basis_id
            AND ppb.input_value_id = piv.input_value_id
            AND p_change_date BETWEEN piv.effective_start_date
                                  AND piv.effective_end_date
            AND piv.element_type_id = pet.element_type_id
            AND p_change_date BETWEEN pet.effective_start_date
                                  AND pet.effective_end_date;

Line: 879

        Select CURRENCY_CODE
        from Fnd_Currencies_Vl
         Where PRECISION = 5
         and rownum = 1;

Line: 910

         SELECT ppb.NAME
           FROM per_all_assignments_f paaf, per_pay_bases ppb
          WHERE paaf.assignment_id = p_assignment_id
            AND p_change_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
            AND ppb.pay_basis_id = paaf.pay_basis_id;

Line: 1033

   select * from per_pay_proposals pro
   where pro.assignment_id = c_assignment_id
    and  (p_effective_date - 1) between change_date and nvl(date_to,to_date('31/12/4712','dd/mm/yyyy'));

Line: 1039

 SELECT PET.INPUT_CURRENCY_CODE
, PPB.PAY_BASIS
, PPB.PAY_ANNUALIZATION_FACTOR
,PIV.UOM
  FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F       PIV
, PER_PAY_BASES            PPB
  WHERE PPB.PAY_BASIS_ID=L_PAY_BASIS_ID
  AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
  AND p_effective_date  BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
  AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
  AND p_effective_date  BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE;

Line: 1053

  select tpt.number_per_fiscal_year
  from pay_all_payrolls_f prl ,
      per_time_period_types tpt
  where prl.payroll_id = p_curr_payroll_id
  and p_effective_date between prl.effective_start_date and prl.effective_end_date
  and prl.period_type = tpt.period_type(+);

Line: 1250

/* This procedure is called from the assignment form to insert the new salary value after a pay basis change */
procedure insert_pay_proposal(p_assignment_id in number, p_validation_start_date in date) is

  l_pay_proposal_id            NUMBER;

Line: 1267

 hr_maintain_proposal_api.insert_salary_proposal
                    (p_pay_proposal_id            => l_pay_proposal_id
                    ,p_assignment_id              => p_assignment_id
                    ,p_business_group_id          => g_proposal_rec.business_group_id
                    ,p_change_date                => p_validation_start_date
                    ,p_comments                   => g_proposal_rec.comments
                    --,p_next_sal_review_date       => l_review_date
                    ,p_proposal_reason            => g_proposal_rec.proposal_reason
                    ,p_proposed_salary_n          => g_new_sal_value
                    ,p_forced_ranking             => g_proposal_rec.forced_ranking
                    ,p_performance_review_id      => g_proposal_rec.performance_review_id
                    ,p_attribute_category         => g_proposal_rec.attribute_category
                    ,p_attribute1                 => g_proposal_rec.attribute1
                    ,p_attribute2                 => g_proposal_rec.attribute2
                    ,p_attribute3                 => g_proposal_rec.attribute3
                    ,p_attribute4                 => g_proposal_rec.attribute4
                    ,p_attribute5                 => g_proposal_rec.attribute5
                    ,p_attribute6                 => g_proposal_rec.attribute6
                    ,p_attribute7                 => g_proposal_rec.attribute7
                    ,p_attribute8                 => g_proposal_rec.attribute8
                    ,p_attribute9                 => g_proposal_rec.attribute9
                    ,p_attribute10                => g_proposal_rec.attribute10
                    ,p_attribute11                => g_proposal_rec.attribute11
                    ,p_attribute12                => g_proposal_rec.attribute12
                    ,p_attribute13                => g_proposal_rec.attribute13
                    ,p_attribute14                => g_proposal_rec.attribute14
                    ,p_attribute15                => g_proposal_rec.attribute15
                    ,p_attribute16                => g_proposal_rec.attribute16
                    ,p_attribute17                => g_proposal_rec.attribute17
                    ,p_attribute18                => g_proposal_rec.attribute18
                    ,p_attribute19                => g_proposal_rec.attribute19
                    ,p_attribute20                => g_proposal_rec.attribute20
                    ,p_object_version_number      => l_ovn
                    ,p_multiple_components        => g_proposal_rec.multiple_components
                    ,p_approved                   => 'Y'
                    ,p_validate                   => FALSE
                    ,p_element_entry_id           => l_element_entry_id
                    ,p_inv_next_sal_date_warning  => l_inv_next_sal_date_warning
                    ,p_proposed_salary_warning    => l_proposed_salary_warning
                    ,p_approved_warning           => l_approved_warning
                    ,p_payroll_warning            => l_payroll_warning
                    ,p_date_to                    => to_date('31/12/4712','dd/mm/yyyy')
                    );

Line: 1317

hr_utility.set_location('Exception: PER_SALADMIN_UTILITY.insert_pay_proposal',100);

Line: 1367

select nvl(value, 1) val
  from  per_assignment_budget_values_f
 where  assignment_id   = p_assignment_id
   and  unit = 'FTE'
   and  p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 1374

select nvl(value, 1) val
 from  per_assignment_budget_values_f
where  assignment_id    = p_assignment_id
  and  unit = 'PFT'
  and p_effective_date BETWEEN effective_start_date AND effective_end_date;

Line: 1428

      select null
      from per_pay_proposals pro
      where pro.assignment_id = p_assignment_id
      and pro.change_date = p_date;

Line: 1447

      select min(change_date)
      from per_pay_proposals pro
      where pro.assignment_id = p_assignment_id;

Line: 1464

select nvl(abv.value,0)
from per_assignment_budget_values_f abv, per_all_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id = p_assignment_id
and abv.assignment_id = asg.assignment_id
and asg.assignment_type in ('E', 'C')
and abv.unit = 'FTE'
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and p_effective_date between abv.effective_start_date and abv.effective_end_date
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status <> 'TERM_ASSIGN';

Line: 1490

         SELECT ppb.Pay_basis
           FROM per_all_assignments_f paaf, per_pay_bases ppb
          WHERE paaf.assignment_id = p_assignment_id
            AND p_change_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
            AND ppb.pay_basis_id = paaf.pay_basis_id;

Line: 1511

  select min(effective_start_date)
  from per_all_assignments_f
  where assignment_id = p_assignment_id
  and effective_start_date > p_from_date
  and pay_basis_id not in
   (select pay_basis_id
    from per_all_assignments_f
    where assignment_id = p_assignment_id
    and p_from_date
     between effective_start_date and effective_end_date );

Line: 1538

   select pay_basis_id
   from per_all_assignments_f
   where assignment_id = p_assignment_id
   and p_from_date
     between effective_start_date and effective_end_date;

Line: 1559

  select min(effective_start_date) - 1
  from per_all_assignments_f
  where assignment_id = p_assignment_id
  and effective_start_date >= p_from_date
  and nvl(pay_basis_id,-1) <> p_pay_basis_id;