APPS.INV_ORGHIERARCHY_PVT SQL Statements

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

Line: 138

SELECT organization_id_child
FROM
(
  (
    SELECT     organization_id_child
    FROM      per_org_structure_elements  arc
    WHERE
     (
       NOT EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                   WHERE acc.organization_id = organization_id_child
                 )
       OR  EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                   WHERE acc.organization_id = organization_id_child
                         AND acc.responsibility_id  =  l_responsibility_id
                 )
     )
    CONNECT BY
          arc.organization_id_parent       =  PRIOR arc.organization_id_child
          AND arc.org_structure_version_id =  PRIOR arc.org_structure_version_id
    START WITH
          arc.organization_id_parent       =  p_origin_org_id
          AND arc.org_structure_version_id =  l_hierarchy_version_id
  )
  INTERSECT
  (
    (
      SELECT        organization_id_child
      FROM        per_org_structure_elements  arc
      WHERE
       (
         NOT EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                     WHERE acc.organization_id = organization_id_child
                   )
         OR  EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                     WHERE acc.organization_id = organization_id_child
                           AND acc.responsibility_id  =  l_responsibility_id
                   )
       )
      CONNECT BY
            arc.organization_id_parent       =  PRIOR arc.organization_id_child
            AND arc.org_structure_version_id =  PRIOR arc.org_structure_version_id
      START WITH
            arc.organization_id_parent       =  l_sec_origin_org_id
            AND arc.org_structure_version_id =  l_sec_hierarchy_version_id
    )
    UNION
    ( SELECT  l_sec_origin_org_id FROM DUAL WHERE  l_include_origin_flag = 'Y')
  )
),
hr_all_organization_units  org,
hr_organization_information hoi,
mtl_parameters mp
WHERE
    org.organization_id  = hoi.organization_id
AND org.organization_id  = mp.organization_id
AND hoi.org_information1 = 'INV'
AND hoi.org_information2 = 'Y'
AND hoi.org_information_context = 'CLASS'
AND org.organization_id  =  organization_id_child
AND  (   org.date_to  >=  SYSDATE OR  org.date_to  IS  NULL )

;

Line: 211

SELECT organization_id_child
FROM


(
  -- full set of organizations from origin, not including origin
  SELECT
    organization_id_child
  FROM
    per_org_structure_elements  arc
  CONNECT BY
        arc.organization_id_parent   =  PRIOR arc.organization_id_child
    AND arc.org_structure_version_id =  PRIOR arc.org_structure_version_id
  START WITH
        arc.organization_id_parent      =  p_origin_org_id
    AND arc.org_structure_version_id    =  l_hierarchy_version_id
)

, hr_all_organization_units   org
, hr_organization_information hoi
, mtl_parameters mp


WHERE
-- only inventory organizations as part of bug#2563291 fix
    org.organization_id  = hoi.organization_id
AND org.organization_id  = mp.organization_id
AND hoi.org_information1 = 'INV'
AND hoi.org_information2 = 'Y' -- inventory enabled flag
AND hoi.org_information_context = 'CLASS'

-- expiration check
AND org.organization_id  =  organization_id_child
AND  (   org.date_to  >=  SYSDATE
     OR  org.date_to  IS  NULL
     )

-- inv security access check
AND  (  NOT EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                    WHERE acc.organization_id  =  organization_id_child )
     OR     EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                    WHERE acc.organization_id    =  organization_id_child
                    AND   acc.responsibility_id  =  l_responsibility_id
                  )
     )
;

Line: 271

SELECT organization_id_child
FROM

(
  (
    -- full set of organizations from origin, not including origin
    SELECT
      organization_id_child
    FROM
      per_org_structure_elements  arc
    CONNECT BY
          arc.organization_id_parent   =  PRIOR arc.organization_id_child
      AND arc.org_structure_version_id =  PRIOR arc.org_structure_version_id
    START WITH
          arc.organization_id_parent      =  p_origin_org_id
      AND arc.org_structure_version_id    =  l_hierarchy_version_id
  )

  INTERSECT

  (
    -- security hierarchy check
    SELECT
      organization_id_child
    FROM
      per_org_structure_elements  arc
    WHERE
      arc.org_structure_version_id  =  l_sec_hierarchy_version_id
  )

)


, hr_all_organization_units  org
, hr_organization_information hoi
, mtl_parameters mp


WHERE
-- only inventory organizations as part of bug#2563291 fix
    org.organization_id  = hoi.organization_id
AND org.organization_id  = mp.organization_id
AND hoi.org_information1 = 'INV'
AND hoi.org_information2 = 'Y' -- inventory enabled flag
AND hoi.org_information_context = 'CLASS'

-- expiration check
AND org.organization_id  =  organization_id_child
AND  (   org.date_to  >=  SYSDATE
     OR  org.date_to  IS  NULL
      )

-- inv security access check
AND  (  NOT EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                    WHERE acc.organization_id  =  organization_id_child )
     OR     EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                    WHERE acc.organization_id    =  organization_id_child
                    AND   acc.responsibility_id  =  l_responsibility_id
                  )
     )
;

Line: 358

  SELECT
    hier.name
  , hier.organization_structure_id
  , prof.view_all_organizations_flag
  , prof.include_top_organization_flag
  , prof.organization_id
  INTO
    l_sec_hierarchy_name
  , l_sec_hierarchy_id
  , l_view_all_flag
  , l_include_origin_flag
  , l_sec_origin_org_id
  FROM
    per_security_profiles         prof
  , per_organization_structures   hier
  WHERE
       prof.security_profile_id  =  l_security_profile_id
  AND  hier.organization_structure_id (+)  =  prof.organization_structure_id
  ;

Line: 383

  SELECT
    hierv.org_structure_version_id
  INTO
    l_hierarchy_version_id
  FROM
    PER_ORG_STRUCTURE_VERSIONS   hierv
  WHERE
       hierv.organization_structure_id  =  p_hierarchy_id
  AND  (   hierv.date_to   >=  SYSDATE
       OR  hierv.date_to   IS  NULL
       )
  AND      hierv.date_from <= SYSDATE
  ;

Line: 402

  SELECT
    hierv.org_structure_version_id
  INTO
    l_sec_hierarchy_version_id
  FROM
    PER_ORG_STRUCTURE_VERSIONS   hierv
  WHERE
       hierv.organization_structure_id  =  l_sec_hierarchy_id
  AND  (   hierv.date_to   >=  SYSDATE
       OR  hierv.date_to   IS  NULL
       )
  AND      hierv.date_from <= SYSDATE
  ;

Line: 498

  SELECT
    sv.org_structure_version_id
  FROM
    per_org_structure_versions   sv
  , per_organization_structures  s
  WHERE
       sv.organization_structure_id  =  s.organization_structure_id
  AND  SYSDATE      >=  sv.date_from
  AND  (   SYSDATE  <=  sv.date_to
       OR  sv.date_to  IS NULL
       )
  AND  s.name              =  p_org_hierarchy_name
  --Bug 9775787
  AND  (s.business_group_id =  l_business_group_id OR
        s.business_group_id IS NULL)
  ;

Line: 517

  SELECT
    organization_id_parent
  FROM
    per_org_structure_elements
  WHERE
         (   organization_id_parent      =  p_org_id
         OR  organization_id_child       =  p_org_id
         )
    AND  org_structure_version_id  =  l_org_structure_version_id
  ;

Line: 642

SELECT  pos.name,
        psp.view_all_organizations_flag,
        psp.include_top_organization_flag,
        psp.organization_id
FROM    per_security_profiles psp,
        per_organization_structures pos
WHERE   psp.security_profile_id = FND_PROFILE.value('PER_SECURITY_PROFILE_ID')
AND     pos.organization_structure_id(+) = psp.organization_structure_id;

Line: 946

SELECT  pos.name,
        psp.view_all_organizations_flag,
        psp.include_top_organization_flag,
        psp.organization_id
FROM      per_security_profiles psp,
          per_organization_structures pos
WHERE     psp.security_profile_id = FND_PROFILE.value('PER_SECURITY_PROFILE_ID')
AND       pos.organization_structure_id(+) = psp.organization_structure_id;

Line: 1273

SELECT
  OSV.org_structure_version_id
, OSV.business_group_id
FROM
  PER_ORG_STRUCTURE_VERSIONS OSV,
  PER_ORGANIZATION_STRUCTURES OS
WHERE
  OSV.ORGANIZATION_STRUCTURE_ID = OS.ORGANIZATION_STRUCTURE_ID

-- rschaub: replaced NVL and date truncation so date index is used
-- otherwise full table scan each time
AND  SYSDATE  >=  OSV.DATE_FROM
AND  (   SYSDATE  <=  OSV.DATE_TO
     OR  OSV.DATE_TO  IS NULL
     )

AND  ltrim(rtrim(OS.NAME)) = ltrim(rtrim(c_hierarchy_name))
--Bug 9775787
AND  (OS.BUSINESS_GROUP_ID =
       -- Bugfix 16344080: fnd_profile.value is not working for 'PER_BUSINESS_GROUP_ID'.
       -- Using fnd_global.per_business_group_id instead.
       -- TO_NUMBER( FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') ) OR
       TO_NUMBER(FND_GLOBAL.per_business_group_id) OR
      OS.BUSINESS_GROUP_ID IS NULL);

Line: 1302

SELECT  organization_id_parent
FROM    PER_ORG_STRUCTURE_ELEMENTS
WHERE   ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id
AND     ORGANIZATION_ID_PARENT NOT IN
                (SELECT ORGANIZATION_ID_CHILD
                 FROM   PER_ORG_STRUCTURE_ELEMENTS
                 WHERE  ORG_STRUCTURE_VERSION_ID = c_org_structure_version_id);

Line: 1320

SELECT
  organization_id_child
, level
FROM
  PER_ORG_STRUCTURE_ELEMENTS POE
CONNECT BY
      POE.ORGANIZATION_ID_PARENT   = PRIOR POE.ORGANIZATION_ID_CHILD
  AND POE.ORG_STRUCTURE_VERSION_ID = PRIOR POE.ORG_STRUCTURE_VERSION_ID
START WITH
      POE.ORGANIZATION_ID_PARENT      = c_org_id
  AND POE.ORG_STRUCTURE_VERSION_ID    = c_org_structure_version_id;

Line: 1659

        SELECT  master_organization_id
        INTO    l_master_org_id
        FROM    mtl_parameters
        WHERE   organization_id  =  l_org_id;

Line: 1712

        SELECT
          period_set_name
        INTO
          l_calendar_name
        FROM
          gl_sets_of_books, hr_organization_information
        WHERE UPPER( org_information_context ) = UPPER( 'Accounting Information' )
        AND organization_id                  = l_org_id
        AND set_of_books_id                  = TO_NUMBER(org_information1);

Line: 1776

        SELECT
          chart_of_accounts_id
        INTO
          l_chart_of_accounts_id
        FROM
          gl_sets_of_books, hr_organization_information
        WHERE UPPER( org_information_context ) = UPPER( 'Accounting Information' )
        AND organization_id                  = l_org_id
        AND set_of_books_id                  = TO_NUMBER(org_information1);

Line: 1886

SELECT  master_organization_id
FROM    MTL_PARAMETERS
WHERE     ORGANIZATION_ID = c_organization_id;

Line: 1892

SELECT
  chart_of_accounts_id
FROM
  gl_sets_of_books, hr_organization_information
WHERE upper( org_information_context ) = upper( 'Accounting Information' )
  AND organization_id                  = c_organization_id
  AND set_of_books_id                  = to_number(org_information1);

Line: 1908

SELECT
  period_set_name
FROM
  gl_sets_of_books, hr_organization_information
WHERE upper( org_information_context ) = upper( 'Accounting Information' )
  AND organization_id                  = c_organization_id
  AND set_of_books_id                  = to_number(org_information1);

Line: 2151

SELECT 'Y'
FROM   HR_ALL_ORGANIZATION_UNITS
WHERE  ORGANIZATION_ID = c_org_id
AND    BUSINESS_GROUP_ID = c_business_group_id
AND    NVL(DATE_TO,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);

Line: 2159

SELECT 'Y'
FROM   HR_ALL_ORGANIZATION_UNITS
WHERE  ORGANIZATION_ID = c_org_id
AND    NVL(DATE_TO,TRUNC(SYSDATE)) >= TRUNC(SYSDATE);

Line: 2165

SELECT RESPONSIBILITY_ID
FROM   ORG_ACCESS
WHERE  ORGANIZATION_ID = c_organization_id;

Line: 2376

SELECT 'Y'
  FROM HR_ALL_ORGANIZATION_UNITS org
WHERE

-- expiration check
  org.organization_id  =  c_org_id
AND  (   org.date_to  >=  SYSDATE
     OR  org.date_to  IS  NULL
     )

-- inv security access check
AND  (  NOT EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                    WHERE acc.organization_id  =  c_org_id )
     OR     EXISTS( SELECT 1 FROM ORG_ACCESS  acc
                    WHERE acc.organization_id    =  c_org_id
                    AND   acc.responsibility_id  =  c_responsibility_id
                  )
     );

Line: 2609

PROCEDURE Insert_hierarchy_index_list
 ( p_orgid_tbl_list   IN orgID_tbl_type)
IS

  l_org_index BINARY_INTEGER;

Line: 2620

     ,'Start of Proc: Insert hierarchy index list'
    );

Line: 2633

     ,'End of Proc: Insert hierarchy index list'
    );