eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PER_PYP_BUS SQL Statements

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

Line: 17


Procedure check_non_updateable_args(p_rec in per_pyp_shd.g_rec_type) is
--
  l_proc     varchar2(72) := g_package||'check_non_updateable_args';

Line: 58


/* change_date can be updated provided that the proposal has not already
   been approved.

 if p_rec.change_date <> per_pyp_shd.g_old_rec.change_date then
     l_argument := 'change_date';

Line: 80


end check_non_updateable_args;

Line: 96


    select min(change_date)
      from per_pay_proposals
     where assignment_id = p_assignment_id
       and change_date >  p_change_date
       and approved = 'N'
       and pay_proposal_id <>  p_pay_proposal_id;

Line: 104


    select min(change_date)
      from per_pay_proposals
     where assignment_id = p_assignment_id
       and change_date >  p_change_date
       and approved = 'Y'
       and pay_proposal_id <>  p_pay_proposal_id;

Line: 161


  select max(pro.change_date)
       from per_pay_proposals pro
       where pro.assignment_id = p_rec.assignment_id
       and pro.change_date


Line: 234

select null
from   per_pay_proposals
where  assignment_id  = p_assignment_id
and    approved   = 'Y';

Line: 242

select null
from   pay_element_entries_f
where  assignment_id  = p_assignment_id
and    creator_type = 'SP'
and    effective_end_date = hr_general.end_of_time;

Line: 252

select max(peef.effective_end_date)
from   pay_element_entries_f peef
,      pay_element_links_f pel
,      pay_input_values_f piv
,      per_pay_bases ppb
,      per_all_assignments_f asg
where  asg.assignment_id = p_assignment_id
and    asg.pay_basis_id=ppb.pay_basis_id
and    ppb.input_value_id=piv.input_value_id
and    asg.effective_start_date
       between piv.effective_start_date and piv.effective_end_date
and    piv.element_type_id=pel.element_type_id
/**
 * Bug Fix : 3036147
 * Description: To allow the user create new salary proposal
 *              when salary element got changed.
 *and    asg.effective_start_date
 *      between pel.effective_start_date and pel.effective_end_date
 **/
and    pel.element_link_id=peef.element_link_id
and    peef.assignment_id=p_assignment_id
and    asg.assignment_id=peef.assignment_id
and    peef.creator_type = 'SP';

Line: 277

select max(effective_end_date)
from   per_all_assignments_f asg,
       per_assignment_status_types ast
where  asg.assignment_id = p_assignment_id
and    asg.assignment_status_type_id=ast.assignment_status_type_id
and    ast.per_system_status='ACTIVE_ASSIGN';

Line: 289

select final_process_date,
       last_standard_process_date,
       actual_termination_date
from  per_periods_of_service pds
,     per_all_assignments_f asg
where asg.assignment_id=p_assignment_id
and   p_change_date between asg.effective_start_date and asg.effective_end_date
and   asg.person_id=pds.person_id
and   p_change_date between pds.date_start
      and NVL(pds.final_process_date,hr_general.end_of_time);

Line: 305

select null
from   per_all_assignments_f asg1
where  assignment_id    = p_assignment_id
and    exists (select null
       from   per_all_assignments_f asg2
       where  asg2.assignment_id  = p_assignment_id
       and    asg1.pay_basis_id +0  <> asg2.pay_basis_id
       and    asg2.effective_start_date > p_change_date
       and    asg1.effective_end_date  >= p_change_date);

Line: 323

  SELECT  pet.post_termination_rule
    FROM  pay_element_types_f pet,
          per_all_assignments_f asg,
          per_pay_bases ppb,
          pay_input_values_f iv
   WHERE  pet.element_type_id = iv.element_type_id
     AND  iv.input_value_id = ppb.input_value_id
     AND  ppb.pay_basis_id  = asg.pay_basis_id
     AND  asg.assignment_id = p_assignment_id
     AND  p_change_date BETWEEN iv.effective_start_date  AND iv.effective_end_date
     AND  p_change_date BETWEEN asg.effective_start_date AND asg.effective_end_date
     AND  p_change_date BETWEEN pet.effective_start_date AND pet.effective_end_date;

Line: 512

    select ASSIGNMENT_TYPE
    from per_all_assignments_f
     where assignment_id=p_assignment_id
     and p_change_date between effective_start_date
                             and effective_end_date;

Line: 521

   select null
     from per_assignments_f2 asg
     where asg.assignment_id = p_assignment_id
       and p_change_date between asg.effective_start_date
                             and asg.effective_end_date;

Line: 637

     select null
     from   per_pay_proposals
     where  assignment_id         = p_assignment_id
     and    business_group_id + 0 = p_business_group_id
     and    change_date           = p_change_date
     and    pay_proposal_id      <> nvl(p_pay_proposal_id,-1);

Line: 645

     select max(change_date)
     from   per_pay_proposals
     where  assignment_id = p_assignment_id
     and    business_group_id + 0 = p_business_group_id
     and    pay_proposal_id<>nvl(p_pay_proposal_id,-1);

Line: 654

   select null
   from   per_pay_proposals
   where  assignment_id        = p_assignment_id
   and    approved = 'N'
   and    pay_proposal_id<>nvl(p_pay_proposal_id,-1);

Line: 661

   select ast.per_system_status,
          asg.business_group_id,
          asg.assignment_type,
          ptp.status,
          asg.pay_basis_id,
          asg.payroll_id
   from   per_all_assignments_f                   asg,
          per_time_periods                  ptp,
          per_assignment_status_types          ast
   where  asg.assignment_id        =        p_assignment_id
   and    asg.assignment_status_type_id = ast.assignment_status_type_id
   and    p_change_date       between asg.effective_start_date
                                and   asg.effective_end_date
   and    asg.payroll_id=ptp.payroll_id(+)
   and    (p_change_date between ptp.start_date(+)
      and ptp.end_date(+)); --bug 2694178, 2801228

Line: 1018

     select     null
     from       per_all_assignments_f assg,
                per_assignment_status_types ast
     where      assg.assignment_id             = p_assignment_id
     and        assg.assignment_status_type_id = ast.assignment_status_type_id
     and        assg.business_group_id + 0     = p_business_group_id
     and        p_next_sal_review_date       between assg.effective_start_date
                                    and nvl(assg.effective_end_date, hr_api.g_eot)
     and        ast.per_system_status          = 'TERM_ASSIGN';

Line: 1165

     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_api.g_eot);

Line: 1177

     select     null
     from       per_all_assignments_f assg,
                per_assignment_status_types ast
     where      assg.assignment_id             = p_assignment_id
     and        assg.assignment_status_type_id = ast.assignment_status_type_id
     and        assg.business_group_id + 0      = p_business_group_id
     and        p_next_sal_review_date       between assg.effective_start_date
                                    and nvl(assg.effective_end_date, hr_api.g_eot)
     and        ast.per_system_status          = 'TERM_ASSIGN';

Line: 1312

   select null
   from   per_pay_proposals pro,
          per_all_assignments_f ass
   where  pro.assignment_id  = p_assignment_id
   and    ass.assignment_id  = pro.assignment_id
   and    p_change_date between ass.effective_start_date
                        AND  ass.effective_end_date;

Line: 1525

   select organization_id
   ,pay_basis_id
   ,position_id
   ,grade_id
   ,normal_hours
   ,frequency
   from per_all_assignments_f
   where assignment_id=p_assignment_id
   and p_change_date between effective_start_date and effective_end_date;

Line: 1752

   select iv.warning_or_error,
          fnd_number.canonical_to_number(iv.min_value),
          fnd_number.canonical_to_number(iv.max_value),
          liv.warning_or_error,
          fnd_number.canonical_to_number(liv.min_value),
          fnd_number.canonical_to_number(liv.max_value)
   from   pay_link_input_values_f liv,
          pay_input_values_f iv,
          pay_element_links_f el,
          per_pay_bases     ppb
   where
        p_pay_basis_id=ppb.pay_basis_id
   and  ppb.input_value_id=iv.input_value_id and
        p_change_date BETWEEN
        iv.effective_start_date AND iv.effective_end_date
   and  iv.element_type_id      = el.element_type_id  and
        p_change_date BETWEEN
        el.effective_start_date AND el.effective_end_date
   and  liv.element_link_id     = el.element_link_id    and
        liv.input_value_id      = iv.input_value_id   and
        p_change_date BETWEEN
        liv.effective_start_date AND liv.effective_end_date;

Line: 1780

   select  p_normal_hours,
           p_frequency,
           fnd_number.canonical_to_number(O2.ORG_INFORMATION3) working_hours,
           O2.ORG_INFORMATION4 frequency,
           fnd_number.canonical_to_number(b2.ORG_INFORMATION3) working_hours,
           b2.ORG_INFORMATION4 frequency,
           fnd_number.canonical_to_number(pgr.minimum),
           fnd_number.canonical_to_number(pgr.maximum)
   from
           hr_all_organization_units bus, HR_ORGANIZATION_INFORMATION b2 ,
           hr_all_organization_units org, HR_ORGANIZATION_INFORMATION O2 ,
           pay_grade_rules_f pgr,
           per_pay_bases     ppb
   where
          org.organization_id = p_organization_id
   and    org.ORGANIZATION_ID = O2.ORGANIZATION_ID (+)
   and    O2.ORG_INFORMATION_CONTEXT (+) = 'Work Day Information'
   and
          pgr.grade_or_spinal_point_id  = p_grade_id   and
          pgr.rate_id                   = ppb.rate_id  and
          p_change_date
          between pgr.effective_start_date and pgr.effective_end_date
   and
          ppb.pay_basis_id    =  p_pay_basis_id
   and    bus.organization_id = p_business_group_id
   and    bus.ORGANIZATION_ID = b2.ORGANIZATION_ID (+)
   and    b2.ORG_INFORMATION_CONTEXT (+) = 'Work Day Information';

Line: 1809

   select  pos.working_hours,
           pos.frequency
   from    hr_all_positions_f   pos
   where   p_position_id              = pos.position_id
   and     p_change_date
           BETWEEN pos.effective_start_date AND pos.effective_end_date;

Line: 1818

  SELECT PET.INPUT_CURRENCY_CODE
, PPB.PAY_ANNUALIZATION_FACTOR
, PPB.GRADE_ANNUALIZATION_FACTOR
, PPB.PAY_BASIS
, PPB.RATE_BASIS
, PET.ELEMENT_TYPE_ID
, PIV.UOM
  FROM PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F       PIV
, PER_PAY_BASES            PPB
--
  WHERE PPB.PAY_BASIS_ID=P_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: 2232

   select null
   from   per_pay_proposal_components
   where  pay_proposal_id     = p_pay_proposal_id
   and    business_group_id + 0 = p_business_group_id
   and    approved = 'N';

Line: 2241

   select null
   from   per_pay_proposals
   where  assignment_id                 = p_assignment_id
   and    business_group_id        + 0        = p_business_group_id
   and    pay_proposal_id<>nvl(p_pay_proposal_id,-1);

Line: 2248

   select assignment_type
   from per_all_assignments_f
   where assignment_id=p_assignment_id
   and p_change_date between
       effective_start_date and effective_end_date;

Line: 2258

   select null
   from   per_pay_proposals
   where  assignment_id        = p_assignment_id
   and    approved = 'Y'
   and    change_date > p_change_date;

Line: 2475

   select assignment_id,business_group_id,change_date,
    multiple_components,approved
   from   per_pay_proposals
   where  pay_proposal_id = p_pay_proposal_id
   and    object_version_number = p_object_version_number;

Line: 2484

   select null
   from   per_pay_proposal_components
   where  pay_proposal_id = p_pay_proposal_id
   and    approved    = 'N';

Line: 2492

   select proposed_salary_n
   from   per_pay_proposals
   where  assignment_id = l_assignment_id
   and    change_date < l_change_date
   order  by change_date desc;

Line: 2499

   select max(change_date)
   from   per_pay_proposals
   where  assignment_id = l_assignment_id;

Line: 2508

   select null
   from   per_pay_proposal_components
   where  pay_proposal_id = p_pay_proposal_id;

Line: 2780

     select null
     from   per_performance_reviews prv
     ,      per_all_assignments_f asg
     where  asg.assignment_id  = p_assignment_id
     and    asg.person_id=prv.person_id
     and    prv.performance_review_id       = p_performance_review_id;

Line: 2974

Procedure insert_validate
        (p_rec                   in out nocopy  per_pyp_shd.g_rec_type
        ,p_inv_next_sal_date_warning   out nocopy  boolean
        ,p_proposed_salary_warning   out nocopy  boolean
        ,p_approved_warning    out nocopy  boolean
  ,p_payroll_warning     out nocopy  boolean
        ) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 3190

End insert_validate;

Line: 3195

Procedure update_validate
        (p_rec                                 in out nocopy  per_pyp_shd.g_rec_type
        ,p_inv_next_sal_date_warning              out nocopy  boolean
        ,p_proposed_salary_warning                out nocopy  boolean
        ,p_approved_warning                       out nocopy  boolean
        ,p_payroll_warning                        out nocopy  boolean
        ) is

--
  l_proc  varchar2(72) := g_package||'update_validate';

Line: 3229

  per_pyp_bus.check_non_updateable_args
    (p_rec    =>p_rec);

Line: 3417

End update_validate;

Line: 3422

Procedure delete_validate
  (p_rec    in  per_pyp_shd.g_rec_type
  ,p_salary_warning out nocopy boolean
 ) is

--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 3452

End delete_validate;

Line: 3465

    select pbg.legislation_code
      from per_business_groups  pbg
         , per_pay_proposals    pyp
     where pyp.pay_proposal_id   = p_pay_proposal_id
       and pbg.business_group_id = pyp.business_group_id;