APPS.PJI_PMV_UTIL SQL Statements

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

Line: 42

   SELECT lkp.meaning
   INTO   l_labor_unit
   FROM   pji_lookups lkp,
          pji_system_settings setup
   WHERE  lkp.lookup_type = G_Labor_Units_LT
     AND  lkp.lookup_code = setup.report_labor_units;

Line: 70

         select distinct proj.name
         from  pa_projects_all           proj
              ,pa_forecast_items         fi
              ,pa_forecast_item_details  fid
         where fi.item_date                     = trunc(p_date)
           and proj.project_id                  = fi.project_id
           and fi.person_id                     = p_person_id
           and fi.expenditure_organization_id   = p_exp_org_id
           and fi.forecast_item_id              = fid.forecast_item_id
           and fid.pji_summarized_flag          is null
           and rownum                           = 1;

Line: 130

         SELECT max(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt1_s = 0;

Line: 141

         SELECT max(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt2_s = 0;

Line: 152

         SELECT max(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt3_s = 0;

Line: 163

         SELECT max(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt4_s = 0;

Line: 174

         SELECT max(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt5_s = 0;

Line: 240

         SELECT min(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt1_s = 0;

Line: 251

         SELECT min(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt2_s = 0;

Line: 262

         SELECT min(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt3_s = 0;

Line: 273

         SELECT min(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt4_s = 0;

Line: 284

         SELECT min(time_id)
         INTO   l_j_date
         FROM   pji_rm_res_f
         WHERE  person_id           = p_person_id
           and  expenditure_organization_id  = p_exp_org_id
           and  period_type_id      = 1
           and  time_id between l_start_time and l_end_time
           and  available_res_count_bkt5_s = 0;

Line: 340

			SELECT userenv('LANG') INTO l_User_Lang FROM dual;

Line: 454

		SELECT userenv('LANG') INTO l_User_Lang FROM dual;

Line: 508

     SELECT value, start_date
     FROM fii_time_week_v
     WHERE l_is_date between start_date and end_date;

Line: 601

   select start_date
   into l_start_date
   from fii_time_week_v
   where value = p_week;

Line: 649

     SELECT value, start_date
     FROM fii_time_week_v
     WHERE l_is_date between start_date and end_date;

Line: 659

   select week_id, week_start_date
   into l_week_id, l_week_date
   from fii_time_day
   where report_date = l_date;  */

Line: 753

   select start_date
   into l_start_date
   from fii_time_week_v
   where value = p_week;

Line: 806

        select name
        into l_val_from
        from fii_time_ent_year
        where start_date = l_from_date;

Line: 811

        select name
        into l_val_to
        from fii_time_ent_year
        where end_date = l_to_date;

Line: 821

        select name
        into l_val_from
        from fii_time_ent_qtr
        where start_date = l_from_date;

Line: 826

        select name
        into l_val_to
        from fii_time_ent_qtr
        where end_date = l_to_date;

Line: 836

        select name
        into l_val_from
        from fii_time_ent_period
        where start_date = l_from_date;

Line: 841

        select name
        into l_val_to
        from fii_time_ent_period
        where end_date = l_to_date;

Line: 851

        select name
        into l_val_from
        from fii_time_week
        where start_date = l_from_date;

Line: 856

        select name
        into l_val_to
        from fii_time_week
        where end_date = l_to_date;

Line: 866

        select value
        into l_val_from
        from fii_time_cal_period_v
        where start_date = l_from_date;

Line: 871

        select value
        into l_val_to
        from fii_time_cal_period_v
        where end_date = l_to_date;

Line: 933

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

Line: 944

    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: 961

        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: 976

    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: 990

    x_insert_top_org_flag:='N';

Line: 1006

l_insert_top_org_flag   VARCHAR2(1);

Line: 1017

    x_insert_top_org_flag => l_insert_top_org_flag);

Line: 1022

        SELECT
        PJI_ORGANIZATION_LIST(
        orgd.organization_id_child
        , org.name
        , orgd.organization_id_parent)
        BULK COLLECT INTO l_Organization_List
        FROM
              per_org_structure_elements orgd
            , pji_system_settings pset
            , hr_all_organization_units_tl 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.language = USERENV('LANG');

Line: 1042

        SELECT
        PJI_ORGANIZATION_LIST(
        orgd.organization_id_child
        , org.name
        , orgd.organization_id_parent)
        BULK COLLECT INTO l_Organization_List
        FROM
              per_org_structure_elements orgd
            , pji_system_settings pset
            , per_organization_list sec
            , hr_all_organization_units_tl 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.language = USERENV('LANG')
            AND orgd.organization_id_child = sec.organization_id
            AND sec.security_profile_id = fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');

Line: 1065

        SELECT
        PJI_ORGANIZATION_LIST(
        orgd.organization_id_child
        , org.name
        , orgd.organization_id_parent)
        BULK COLLECT INTO l_Organization_List
        FROM
              per_org_structure_elements orgd
            , pji_system_settings pset
            , hr_all_organization_units_tl 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.language = USERENV('LANG')
            START WITH orgd.organization_id_parent=l_top_organization_id
            CONNECT BY PRIOR orgd.organization_id_child = orgd.organization_id_parent;

Line: 1086

    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

            FOR cur_Top_Organization_List IN (
                SELECT
                orgd.organization_id_child organization_id_child
                , org.name name
                , orgd.organization_id_parent organization_id_parent
                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_tl org
                    , per_security_profiles prof
                WHERE 1=1
                    AND orgd.organization_id_child = org.organization_id
                    AND org.language = USERENV('LANG')
                    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))
            LOOP
                l_Count:=l_Count+1;

Line: 1139

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

Line: 1156

l_insert_top_org_flag   VARCHAR2(1);

Line: 1164

    x_insert_top_org_flag => l_insert_top_org_flag);

Line: 1168

        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: 1182

        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: 1199

        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: 1221

    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: 1259

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

Line: 1275

    SELECT resource_job_level
    INTO   l_job_level
    FROM   pa_resources_denorm
    WHERE  person_id = p_person_id
    AND    p_as_of_date between resource_effective_start_date and resource_effective_end_date;

Line: 1326

			SELECT userenv('LANG')
			INTO G_User_Lang
			FROM dual;

Line: 1335

			SELECT lookup_code, meaning
			BULK COLLECT INTO G_Measure_Short_Code, G_Measure_Label
			FROM pji_lookups
			WHERE lookup_type LIKE 'PJI_PMV_MSR_LABELS';

Line: 1345

			SELECT setup.report_labor_units
			INTO G_FTE_Level
			FROM pji_system_settings setup;

Line: 1354

			SELECT meaning INTO G_Budget_Label
			FROM pji_lookups
			WHERE lookup_type = G_Graph_Labels_LT
			AND lookup_code =   G_Budget_Label_LC ;

Line: 1364

			SELECT meaning INTO G_Prior_Label
			FROM pji_lookups
			WHERE lookup_type = G_Graph_Labels_LT
			AND lookup_code =   G_Prior_Budget_Label_LC ;