APPS.PER_ADD_BUS SQL Statements

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

Line: 164

         select business_group_id
           from per_all_people_f ppf
          where ppf.person_id = p_person_id;

Line: 304

  select party_id
  from    per_all_people_f per
    where   per.person_id = p_rec.person_id
    and     p_rec.date_from
    between per.effective_start_date
    and     nvl(per.effective_end_date,hr_api.g_eot);

Line: 312

  select party_id
  from hz_parties hzp
  where hzp.party_id = p_rec.party_id;

Line: 601

     select null
     from fnd_territories ft
     where ft.territory_code = p_country;

Line: 927

    select assignment_id
      from per_all_assignments_f
     where person_id = p_person_id;

Line: 932

    select payroll_action_id
      from pay_assignment_actions
     where assignment_id = p_assignment_id;

Line: 937

    select null
    from   per_addresses pa
    where  p_date_from <= nvl(pa.date_to, hr_api.g_eot)
    and    l_date_to >= pa.date_from
    and    pa.address_type = p_address_type
    and    (pa.person_id = p_person_id OR   -- HR/TCA merge
            (pa.party_id = p_party_id  and p_person_id is null)) -- #3406505
    and   (p_address_id is null
    or    (p_address_id is not null
    and    pa.address_id <> p_address_id));

Line: 949

    select null
    from   per_addresses pa
    where  p_date_from <= nvl(pa.date_to, hr_api.g_eot)
    and    l_date_to >= pa.date_from
    and    pa.primary_flag = 'Y'
    and    (pa.person_id = p_person_id OR  --
            (pa.party_id = p_party_id  and p_person_id is null)) -- HR/TCA merge -- #3406505
    and   (p_address_id is null
    or    (p_address_id is not null
    and    pa.address_id <> p_address_id));

Line: 961

    select null
    from   per_addresses pa
    where  p_date_from  >= pa.date_from
    and    exists (select null
                    from   per_addresses pa2
                    where  nvl(pa2.date_to, hr_api.g_eot) >= l_date_to
                    and    (pa2.person_id = p_person_id OR --
                            (pa2.party_id = p_party_id and p_person_id is null)) -- HR/TCA merge -- #3406505
                    and    pa2.primary_flag = 'Y')
    and    pa.primary_flag = 'Y'
    and    (pa.person_id    = p_person_id  OR  -- HR/TCA merge
            (pa.party_id     = p_party_id and p_person_id is null));  --#3406505

Line: 977

  select null
  from   sys.dual
  where exists(select null
               from   per_addresses pa
               where ((pa.date_from < p_date_from
                       and nvl(pa.date_to, hr_api.g_eot) >=
                       (select date_from
                        from per_addresses
                        where address_id = p_address_id)
                       and p_date_from <> (select date_from
                                           from per_addresses
                                           where address_id = p_address_id) )
                     or (nvl(pa.date_to, hr_api.g_eot) >
                         nvl(p_date_to, hr_api.g_eot)
                         and pa.date_from <=(select nvl(date_to, hr_api.g_eot)
                                             from per_addresses
                                             where address_id = p_address_id)
                         and nvl(p_date_to, hr_api.g_eot) <>
                             (select nvl(date_to, hr_api.g_eot)
                              from per_addresses
                              where address_id = p_address_id) ))
               and   pa.primary_flag = 'N'
               and   (pa.person_id = p_person_id OR
                     (pa.party_id  = p_party_id and p_person_id is null)));-- HR/TCA merge --#3406505

Line: 1004

    select null
    from   sys.dual
    where exists(select null
                from   per_addresses pa
                where  (pa.person_id = p_person_id OR
                        (pa.party_id  = p_party_id and p_person_id is null))  -- HR/TCA merge --#3406505
                and    pa.primary_flag = 'Y'
                and    (p_address_id is null
                or     (p_address_id is not null
                and     p_address_id <> pa.address_id)));

Line: 1016

    select pa.date_from,
           pa.date_to
    from   per_addresses pa
    where  (pa.person_id = p_person_id OR
            (pa.party_id  = p_party_id and p_person_id is null))  -- HR/TCA merge --#3406505
    and    pa.primary_flag = 'Y'
    and    pa.date_to < p_date_from
    and    (p_address_id is null
    or     (p_address_id is not null
    and     p_address_id <> pa.address_id));

Line: 1028

    select pa.date_from, pa.date_to
    from   per_addresses pa
    where  (pa.person_id = p_person_id OR
            (pa.party_id  = p_party_id and p_person_id is null))  -- HR/TCA merge --#3406505
    and    pa.primary_flag = 'Y'
    and    pa.date_from > p_date_to
    and    (p_address_id is null
    or     (p_address_id is not null
    and     p_address_id <> pa.address_id));

Line: 1287

          select effective_date
            into l_action_eff_date
            from pay_payroll_actions
           where payroll_action_id = actionCursor.payroll_action_id;

Line: 1294

            select count(*)
              into l_address_count
              from per_addresses
             where person_id = p_person_id
               and address_id <> p_address_id
               and l_action_eff_date between date_from
                                     and nvl(date_to,l_action_eff_date);

Line: 1356

     select null
     from fnd_descr_flex_contexts
     where descriptive_flexfield_name  = 'Address Structure'
     and descriptive_flex_context_code = p_style
     and enabled_flag                  = 'Y'
     and application_id                = 800;

Line: 1668

    select legislation_code
    from per_business_groups pbg
    where pbg.business_group_id = p_business_group_id;

Line: 1708

      l_dynamic_sql  := 'select  t.address_line_1, '                        ||
                        '        t.address_line_1_kana '                    ||
                        'from    per_jp_address_lookups t '                ||
                        'where   t.district_code = :p_town_or_city';

Line: 1767

          'select  t.district_code,'                                     ||
          '        t.address_line_1_kana '                                ||
          'from    per_jp_address_lookups t '                            ||
          'where   t.address_line_1 = :p_address_line1';

Line: 1977

    l_dynamic_sql  := 'select  null '                          ||
                      'from    per_jp_postal_codes p '         ||
                      'where   p.postal_code = :p_postal_code';

Line: 2068

select  /*+index(Z PAY_US_ZIP_CODES_N1)
           index(Z PAY_US_ZIP_CODES_N2) */ c.state_abbrev
, c.state_code
,a.city_name
, a.city_code
, z.zip_start
, z.zip_end
, b.county_name
, b.county_code
from
pay_us_city_names a
, pay_us_counties b
, pay_us_states c
, pay_us_zip_codes z
where a.state_code = c.state_code
and a.county_code = b.county_code
and b.state_code = c.state_code
and a.city_code=z.city_code
and a.state_code=z.state_code
and a.county_code=z.county_code
and substr(p_postal_code,1,5) between z.zip_start and z.zip_end
and upper(a.city_name)=upper(p_town_or_city)
order by z.zip_start desc;

Line: 2581

     select null
     from pay_us_counties
     where county_name = p_region_1;

Line: 2733

     select null
     from pay_us_counties
     where county_name = p_tax_county;

Line: 2860

    select null
    from pay_us_states
    where state_abbrev = p_region_2;

Line: 3020

    select null
    from pay_us_states
    where state_abbrev = p_tax_state ;

Line: 3158

     select null
     from pay_us_city_names
     where city_name = p_town_or_city;

Line: 3273

     select null
     from pay_us_city_names
     where city_name = p_tax_city;

Line: 3394

    select st.state_code,
           cou.county_code
    from  pay_us_states st,
          pay_us_counties cou
    where cou.state_code = st.state_code
    and   cou.county_name =p_region_1
    and   st.state_abbrev = p_region_2;

Line: 3404

    select cty.city_code
    from  pay_us_city_names cty
    where cty.state_code  = l_state_code
    and   cty.county_code = l_county_code
    and   cty.city_name   = p_town_or_city;

Line: 3411

    select 1
    from  pay_us_zip_codes zip,
          pay_us_city_names cty
    where zip.state_code  = l_state_code
    and   zip.county_code = l_county_code
    and   cty.city_name = p_town_or_city
    and   zip.state_code = cty.state_code
    and   zip.county_code = cty.county_code
    and   zip.city_code = cty.city_code
    and   l_postal_code between zip.zip_start
    and   zip.zip_end;

Line: 3425

    select st.state_code
    from  pay_us_city_names cty
    ,     pay_us_states st
    where cty.state_code  = st.state_code
    and   cty.city_name   = p_town_or_city
    and   st.state_abbrev  = p_region_2;

Line: 3434

    select 1
    from  pay_us_zip_codes zip,
          pay_us_city_names cty
    where cty.city_name  = p_town_or_city
    and   cty.state_code = l_state_code
    and   zip.state_code = cty.state_code
    and   zip.city_code = cty.city_code
    and   l_postal_code between zip.zip_start
    and   zip.zip_end;

Line: 3695

    select st.state_code,
           cou.county_code
    from  pay_us_states st,
          pay_us_counties cou
    where cou.state_code = st.state_code
    and   cou.county_name = p_tax_county
    and   st.state_abbrev = p_tax_state;

Line: 3705

    select cty.city_code
    from  pay_us_city_names cty
    where cty.state_code  = l_state_code
    and   cty.county_code = l_county_code
    and   cty.city_name   = p_tax_city;

Line: 3712

    select 1
    from  pay_us_zip_codes zip,
          pay_us_city_names cty
    where zip.state_code  = l_state_code
    and   zip.county_code = l_county_code
    and   cty.city_name = p_tax_city
    and   zip.state_code = cty.state_code
    and   zip.county_code = cty.county_code
    and   zip.city_code = cty.city_code
    and   l_postal_code between zip.zip_start
    and   zip.zip_end;

Line: 3726

    select st.state_code
    from  pay_us_city_names cty
    ,     pay_us_states st
    where cty.state_code  = st.state_code
    and   cty.city_name   = p_tax_city
    and   st.state_abbrev  = p_tax_state;

Line: 3735

    select 1
    from  pay_us_zip_codes zip,
          pay_us_city_names cty
    where cty.city_name  = p_tax_city
    and   cty.state_code = l_state_code
    and   zip.state_code = cty.state_code
    and   zip.city_code = cty.city_code
    and   l_postal_code between zip.zip_start
    and   zip.zip_end;

Line: 3988

     select null
     from per_addresses pa
     where pa.date_from between per_add_shd.g_old_rec.date_from
                            and l_date
     and pa.person_id = per_add_shd.g_old_rec.person_id
     and pa.primary_flag = 'N';

Line: 3996

     select null
     from  sys.dual
     where exists(select null
                  from   per_addresses pa2
                  where  pa2.date_from > l_date
                  and    pa2.person_id = per_add_shd.g_old_rec.person_id
                  and    pa2.primary_flag = 'Y');

Line: 4074

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

Line: 4133

end check_non_updateable_args;

Line: 4393

procedure df_update_validate
  (p_rec in per_add_shd.g_rec_type) is
--
  l_proc    varchar2(72) := g_package||'df_update_validate';

Line: 4468

end df_update_validate;

Line: 4473

Procedure insert_validate
  (p_rec               in out nocopy per_add_shd.g_rec_type
  ,p_effective_date    in date
  ,p_validate_county   in boolean          default true
  ) is
--
  l_proc        varchar2(72) := g_package||'insert_validate';

Line: 4846

End insert_validate;

Line: 4851

Procedure update_validate
  (p_rec                in out nocopy per_add_shd.g_rec_type
  ,p_effective_date     in date
  ,p_prflagval_override in boolean      default false
  ,p_validate_county    in boolean      default true
  ) is
--
  l_proc  varchar2(72) := g_package||'update_validate';

Line: 4876

  check_non_updateable_args(p_rec => p_rec);

Line: 5221

End update_validate;

Line: 5226

Procedure delete_validate(p_rec in out nocopy per_add_shd.g_rec_type) is
--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 5243

End delete_validate;

Line: 5256

    select pbg.legislation_code
      from per_business_groups  pbg
         , per_addresses        adr
     where adr.address_id        = p_address_id
       and pbg.business_group_id = adr.business_group_id;