APPS.PER_PL_ASSIGNMENT SQL Statements

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

Line: 7

       select per_information4,per_information6
        from  per_all_people_f
        where person_id=r_person_id
        and   r_date between effective_start_date and effective_end_date ;

Line: 13

        select '1'
        from   pay_pl_sii_details_f
        where  per_or_asg_id=r_join_variable
        and    r_date between effective_start_date and effective_end_date;

Line: 19

        select '1'
        from  pay_pl_paye_details_f
        where per_or_asg_id=r_join_variable
        and   r_date between effective_start_date and effective_end_date;

Line: 25

    select per_system_status
    from   per_assignment_status_types paat , per_all_assignments_f paaf
    where  paat.assignment_status_type_id=paaf.assignment_status_type_id
    and    assignment_id=r_assignment_id
    and    r_date between effective_start_date and effective_end_date;

Line: 32

            select person_id
            from   per_all_assignments_f
            where  assignment_id=l_assignment_id
            and    l_date between effective_start_date and effective_end_date;

Line: 338

 procedure update_pl_emp_asg
                     (P_EFFECTIVE_DATE               DATE
                     ,P_ASSIGNMENT_ID                NUMBER
                     ,P_ASSIGNMENT_STATUS_TYPE_ID    NUMBER
                     ,P_SEGMENT3                     VARCHAR2
                     ,P_SEGMENT4                     VARCHAR2
                     ,P_SEGMENT5                     VARCHAR2
                     ,P_SEGMENT6                     VARCHAR2
                     ,P_SEGMENT7                     VARCHAR2
                     ,P_SEGMENT8                     VARCHAR2
                     ,P_SEGMENT9                     VARCHAR2
                     ,P_SEGMENT11                    VARCHAR2
                     ,P_SEGMENT12                    VARCHAR2
                     ,P_SEGMENT13                    VARCHAR2
                     ,P_SEGMENT14                    VARCHAR2
                     ,P_SEGMENT15                    VARCHAR2
                     ,P_SEGMENT16                    VARCHAR2
                     ,P_NOTICE_PERIOD                NUMBER
                     ,P_NOTICE_PERIOD_UOM            VARCHAR2
                     )is

cursor csr_get_payroll_emp_cat is
select payroll_id,employment_category
from   per_all_assignments_f
where  assignment_id=P_ASSIGNMENT_ID
and    P_EFFECTIVE_DATE between effective_start_date and effective_end_date;

Line: 366

select kyflx.segment3,kyflx.segment4,kyflx.segment6,fnd_date.canonical_to_date(segment13)
from   hr_soft_coding_keyflex kyflx , per_all_assignments_f paaf
where  paaf.assignment_id          = P_ASSIGNMENT_ID
and    paaf.soft_coding_keyflex_id= kyflx.soft_coding_keyflex_id
and    r_date between effective_start_date and effective_end_date;

Line: 374

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

Line: 405

l_proc:=g_package_name||'UPDATE_PL_EMP_ASG';

Line: 448

      1)update_emp_asg_criteria will be called first..this is where the payroll id,employment_category will be set
      2)since these values are not available here...we have to query for the changes....
      3) do not use per_asg_shd.g_old_rec.employment_category or payroll_id
    */
     if p_segment3='NORMAL' then
       l_employment_category:=null;

Line: 642

        select person_id,business_group_id
         from  per_all_assignments_f
        where  assignment_id=p_assignment_id
        and    p_effective_date between effective_start_date and effective_end_date;

Line: 648

         select '1' from pay_pl_sii_details_f
         where contract_category='TERM_NORMAL'
          and  p_effective_date between effective_start_date and effective_end_date
          and  per_or_asg_id=p_assignment_id;

Line: 654

         select emp_social_security_info,old_age_contribution,pension_contribution,sickness_contribution,
               work_injury_contribution,labor_contribution,health_contribution,unemployment_contribution,
                  old_age_cont_end_reason,pension_cont_end_reason,sickness_cont_end_reason,
               work_injury_cont_end_reason,labor_fund_cont_end_reason,health_cont_end_reason,unemployment_cont_end_reason
        from   pay_pl_sii_details_f
        where  per_or_asg_id=r_person_id
          and  contract_category='NORMAL'
          and  p_effective_date between effective_start_date and effective_end_date;

Line: 723

          select person_id,business_group_id
          from   per_all_assignments_f
          where  assignment_id=p_assignment_id
           and   p_effective_date between effective_start_date and effective_end_date;

Line: 729

         select '1' from pay_pl_paye_details_f
         where contract_category='TERM_NORMAL'
          and  p_effective_date between effective_start_date and effective_end_date
          and  per_or_asg_id=p_assignment_id;

Line: 735

          select  tax_reduction,tax_calc_with_spouse_child,income_reduction,income_reduction_amount,rate_of_tax
           from   pay_pl_paye_details_f
           where  per_or_asg_id=r_person_id
            and   contract_category='NORMAL'
            and   p_effective_date between effective_start_date and effective_end_date;

Line: 852

procedure update_pl_emp_asg_criteria
(P_EFFECTIVE_DATE in DATE
,P_ASSIGNMENT_ID in NUMBER
,P_PAYROLL_ID in NUMBER
,P_EMPLOYMENT_CATEGORY in VARCHAR2) is

cursor  csr_get_contract_cat is
    select segment3
    from  hr_soft_coding_keyflex keyflx,per_all_assignments_f paaf
    where paaf.soft_coding_keyflex_id=keyflx.soft_coding_keyflex_id   --Changed the join condition for Bug 7425845
    and paaf.effective_start_date between effective_start_date and effective_end_date
    and assignment_id=p_assignment_id;

Line: 875

l_proc:=g_package_name||'UPDATE_PL_EMP_ASG_CRITERIA';

Line: 980

  select business_group_id
    from per_all_assignments_f
   where assignment_id = p_assignment_id;

Line: 1027

PROCEDURE UPDATE_PL_EMP_ASG_A
           (P_EFFECTIVE_DATE     in date,
            P_SEGMENT3           in varchar2,
            P_ASSIGNMENT_ID      in number) is


cursor csr_business_group is
  select business_group_id
    from per_all_assignments_f
   where assignment_id = p_assignment_id;

Line: 1039

  select soft.segment3
    from hr_soft_coding_keyflex soft, per_all_assignments_f paaf
   where paaf.soft_coding_keyflex_id = soft.soft_coding_keyflex_id
     and paaf.assignment_id = p_assignment_id
     and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;

Line: 1055

  select '1'
    from pay_pl_paye_details_f
   where per_or_asg_id = p_assignment_id
     and contract_category = l_segment3;

Line: 1061

l_proc:='UPDATE_PL_EMP_ASG_A';

Line: 1109

END UPDATE_PL_EMP_ASG_A;