APPS.HR_ORI_BUS SQL Statements

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

Line: 32

    select pbg.security_group_id
      from per_business_groups pbg
         , hr_organization_information ori
      --   , EDIT_HERE table_name(s) 333
     where ori.org_information_id = p_org_information_id;

Line: 104

    select max(fnd_date.canonical_to_date(org_information4)) max_end_date
    from   hr_organization_information
    where  organization_id = p_organization_id
    and    org_information_context = 'Organization Name Alias'
    and    org_information_id <> nvl(p_org_information_id,-1)
    and    fnd_date.canonical_to_date(org_information4) < p_start_date;

Line: 112

    select min(fnd_date.canonical_to_date(org_information3)) min_start_date
    from   hr_organization_information
    where  organization_id = p_organization_id
    and    org_information_context = 'Organization Name Alias'
    and    org_information_id <> nvl(p_org_information_id,-1)
    and    fnd_date.canonical_to_date(org_information3)
           > nvl(p_end_date,hr_api.g_eot);

Line: 337

    select period_of_service_id worker_id,
           date_start
    from   per_periods_of_service
    where  l_start_date
           between date_start
           and     nvl(actual_termination_date,hr_api.g_eot)
    and    person_id = l_person_id
    union
    select period_of_placement_id worker_id,
           date_start
    from   per_periods_of_placement
    where  l_start_date
           between date_start
           and     nvl(actual_termination_date,hr_api.g_eot)
    and    person_id = l_person_id;

Line: 358

    select actual_termination_date
    from   per_periods_of_service
    where  nvl(l_end_date,hr_api.g_eot)
           between date_start
           and     nvl(actual_termination_date,hr_api.g_eot)
    and    period_of_service_id = p_worker_id
    and    person_id = l_person_id
    union
    select actual_termination_date
    from   per_periods_of_placement
    where  nvl(l_end_date,hr_api.g_eot)
           between date_start
           and     nvl(actual_termination_date,hr_api.g_eot)
    and    period_of_placement_id = p_worker_id
    and    person_id = l_person_id;

Line: 503

    select null
    from   dual
    where  exists
          (select null
           from   hr_organization_units org,
                  hr_organization_information org2,
                  hr_org_info_types_by_class oitbc
           where  org.organization_id = p_organization_id
           and    org.organization_id = org2.organization_id
           and    org2.org_information_context = 'CLASS'
           and    org2.org_information2 = 'Y'
           and    oitbc.org_classification = org2.org_information1
           and    oitbc.org_information_type = 'Organization Name Alias');

Line: 574

    select null
    from   per_people_f
    where  person_id = l_person_id
    and    p_effective_date
           between effective_start_date
           and     effective_end_date;

Line: 668

    select null
    from   hr_organization_information
    where  organization_id = p_organization_id
    and    org_information_context = 'Organization Name Alias'
    and    (l_start_date
            between fnd_date.canonical_to_date(org_information3)
            and     nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot)
            or
            nvl(l_end_date,hr_api.g_eot)
            between fnd_date.canonical_to_date(org_information3)
            and     nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot)
            or fnd_date.canonical_to_date(org_information3)
            between l_start_date
            and     nvl(l_end_date,hr_api.g_eot)
            or nvl(fnd_date.canonical_to_date(org_information4),hr_api.g_eot)
            between l_start_date
            and     nvl(l_end_date,hr_api.g_eot))
    and     org_information_id <> nvl(p_org_information_id,-1)
    and     org_information3 is not null;

Line: 691

    select date_from,
           date_to
    from   hr_organization_units
    where  organization_id = p_organization_id;

Line: 821

    select pbg.legislation_code
      from per_business_groups     pbg
         , hr_organization_information ori
         , hr_organization_units org
     where ori.org_information_id = p_org_information_id
       and org.organization_id    = ori.organization_id
       and pbg.business_group_id = org.business_group_id; -- AT 27/9/01

Line: 917

 select name into l_name from hr_all_organization_units where organization_id = p_organization_id;

Line: 923

 select count(*)
 into l_exists
 from hr_organization_information i, hr_all_organization_units u
 where i.organization_id <> p_organization_id
 and i.organization_id = u.organization_id
 and i.org_information1='HR_BG'
 and i.org_information_context='CLASS'
 and i.org_information2 ='Y'
 and u.name = l_name;

Line: 1267

Procedure chk_non_updateable_args
  (p_effective_date               in date
  ,p_rec in hr_ori_shd.g_rec_type
  ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 1307

End chk_non_updateable_args;

Line: 1357

   SELECT 'Y'
   INTO l_exists
   FROM sys.dual
   WHERE EXISTS
     (SELECT null
      FROM hr_all_organization_units
      WHERE organization_id = p_organization_id);

Line: 1422

     SELECT 'Y'
        FROM hr_lookups
        WHERE lookup_type = 'ORG_CLASS'
          AND lookup_code = p_org_information1
          AND enabled_flag = 'Y'
          AND p_effective_date BETWEEN nvl(start_date_active,p_effective_date)
          AND nvl(end_date_active,p_effective_date);

Line: 1509

   SELECT 'Y'
   INTO l_exists
   FROM sys.dual
   WHERE EXISTS
     (SELECT null
      FROM hr_organization_information
      WHERE organization_id = p_organization_id
        AND org_information_context = 'CLASS'
        AND org_information1 = p_org_information1);

Line: 1585

   SELECT 'Y'
   INTO l_exists
   FROM sys.dual
   WHERE EXISTS
     (SELECT hoit.org_information_type
      FROM hr_org_information_types hoit
      WHERE hoit.org_information_type = p_org_information_context
        AND (hoit.navigation_method = 'GS' OR hoit.navigation_method = 'GM' OR hoit.navigation_method = 'LOC')
        AND EXISTS
        (SELECT null
         FROM hr_org_info_types_by_class hitbc
           ,hr_organization_information hoi
         WHERE hitbc.org_information_type = hoit.org_information_type
           AND hitbc.org_classification = hoi.org_information1
           AND hoi.org_information2 = 'Y'
           AND hoi.org_information_context = 'CLASS'
           AND hoi.organization_id = p_organization_id)
       );

Line: 1676

   SELECT 'Y'
   INTO l_exists
   FROM sys.dual
   WHERE EXISTS
     (SELECT null
      FROM hr_organization_information hoi
          ,hr_org_information_types hoit
      WHERE hoi.org_information_context = p_org_information_context
        AND hoi.organization_id = p_organization_id
        AND hoit.org_information_type = p_org_information_context
        AND hoit.navigation_method = 'GS');

Line: 1709

   select location_id
   from hr_all_organization_units
   where organization_id = p_organization_id;

Line: 1779

select
        org_information1,
        org_information8
from
        hr_organization_information
where
        org_information_id      = X_org_information_id;

Line: 1789

SELECT /*+ STAR_TRANSFORMATION */
        'x'
FROM
        pay_wc_rates wcr,
        pay_wc_funds wcf
WHERE
        wcf.carrier_id = l_carrier_id   AND
        wcf.state_code = l_state_code   AND
        wcr.fund_id     = wcf.fund_id
AND EXISTS
      ( SELECT  'code referenced in override'
        FROM    per_assignments_f a,
                fnd_id_flex_structures_vl ifs,
                hr_soft_coding_keyflex sck
        WHERE   sck.segment1    = to_char(X_org_information_id) -- #1683897
        AND     segment8        = to_char(wcr.wc_code)
        AND     ifs.id_flex_structure_name = 'GREs and other data'
        AND     sck.id_flex_num = ifs.id_flex_num
        AND     a.assignment_type = 'E'
        AND     a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id );

Line: 1881

   select business_group_id
        from hr_all_organization_units
        where organization_id = p_organization_id;

Line: 1899

Procedure insert_validate
  (p_effective_date               in date
  ,p_rec                          in hr_ori_shd.g_rec_type
  ) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 2067

End insert_validate;

Line: 2072

Procedure update_validate
  (p_effective_date               in date
  ,p_rec                          in hr_ori_shd.g_rec_type
  ) is
--
  cursor c_date (p_id number) is
   select min(effective_start_date), max(effective_end_date)
   from per_all_people_f
   where current_employee_flag='Y'
   and person_id=p_id;

Line: 2082

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

Line: 2116

  chk_non_updateable_args
    (p_effective_date              => p_effective_date
      ,p_rec              => p_rec
    );

Line: 2232

End update_validate;

Line: 2237

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

Line: 2249

End delete_validate;