APPS.PJI_PMV_UTIL SQL Statements

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

Line: 17

 x_insert_top_org_flag  OUT  nocopy   VARCHAR2 )
IS
l_security_profile_id   per_security_profiles.security_profile_id%TYPE;

Line: 28

    SELECT organization_id,
           view_all_organizations_flag ,
           include_top_organization_flag
    INTO   l_top_organization_id,
           l_view_all_org_flag,
           x_insert_top_org_flag
    FROM   per_security_profiles
    WHERE  security_profile_id=l_security_profile_id;

Line: 45

        SELECT per.organization_id
        INTO   l_top_organization_id
        FROM   fnd_user fndu,
               per_all_assignments_f per
        WHERE  fndu.user_id=l_user_id
        AND    fndu.employee_id=per.person_id
        AND    per.primary_flag='Y'
        AND   (SYSDATE BETWEEN per.effective_start_Date AND NVL(per.effective_end_date, SYSDATE + 1));

Line: 60

    IF x_insert_top_org_flag = 'Y' THEN

        SELECT name
        INTO   x_top_org_name
        FROM   hr_all_organization_units_tl
        WHERE  organization_id = x_top_org_id
        AND    language = USERENV('LANG');

Line: 74

    x_insert_top_org_flag:='N';

Line: 88

l_insert_top_org_flag   VARCHAR2(1);

Line: 96

    x_insert_top_org_flag => l_insert_top_org_flag);

Line: 100

        SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
	(SELECT orgd.organization_id_child
        FROM
              per_org_structure_elements orgd
            , pji_system_settings pset
            , hr_all_organization_units org
        WHERE 1=1
            AND orgd.org_structure_version_id = pset.org_structure_version_id
            AND orgd.organization_id_child = org.organization_id
            AND org.organization_id = p_org_id);

Line: 114

        SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
	(SELECT orgd.organization_id_child
        FROM
              per_org_structure_elements orgd
            , pji_system_settings pset
            , per_organization_list sec
            , hr_all_organization_units org
        WHERE 1=1
            AND orgd.org_structure_version_id = pset.org_structure_version_id
            AND orgd.organization_id_child = org.organization_id
            AND org.organization_id = p_org_id
            AND orgd.organization_id_child = sec.organization_id
            AND sec.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL'));

Line: 131

        SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
	(SELECT orgd.organization_id_child
        FROM
              per_org_structure_elements orgd
            , pji_system_settings pset
            , hr_all_organization_units org
        WHERE 1=1
            AND orgd.org_structure_version_id = pset.org_structure_version_id
            AND orgd.organization_id_child = org.organization_id
	    AND orgd.organization_id_child = p_org_id
            START WITH orgd.organization_id_parent=l_top_organization_id
            CONNECT BY PRIOR orgd.organization_id_child = orgd.organization_id_parent);

Line: 153

    IF l_insert_top_org_flag= 'Y' THEN

        --Bug 4599990.In case the user has permissions to see all the orgz then the top org has to be derived.
        IF l_top_organization_id=0 THEN

		SELECT p_org_id INTO l_org_id FROM DUAL WHERE EXISTS
		(SELECT orgd.organization_id_child
                FROM
                    (select distinct organization_id_parent organization_id_child, NULL organization_id_parent from
                      per_org_structure_elements p
                    , pji_system_settings pset
                    where p.org_structure_version_id = pset.org_structure_version_id
                    and not exists
                    (select 1 from
                      per_org_structure_elements c
                      where c.organization_id_child = p.organization_id_parent
                      and   c.org_structure_version_id = p.org_structure_version_id)) orgd
                    , per_organization_list sec
                    , hr_all_organization_units org
                    , per_security_profiles prof
                WHERE 1=1
                    AND orgd.organization_id_child = org.organization_id
                    AND orgd.organization_id_child = p_org_id
                    AND orgd.organization_id_child = sec.organization_id (+)
                    AND sec.security_profile_id(+) = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
                    AND fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL') IS NOT NULL
                AND prof.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
                AND ( prof.view_all_organizations_flag = 'Y' OR sec.organization_id IS NOT NULL));

Line: 191

    END IF;--IF l_insert_top_org_flag= 'Y' THEN