APPS.PA_ASSIGNMENT_UTILS SQL Statements

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

Line: 28

       SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
       INTO  x_in_use_flag, x_error_message_code
       FROM dual
       WHERE EXISTS
       (SELECT 'x' FROM pa_project_assignments
    WHERE status_code = p_status_code)
        OR EXISTS
       (SELECT 'x' FROM pa_schedules
        WHERE status_code = p_status_code)
        OR EXISTS
       (SELECT 'x' FROM pa_project_assignments
        WHERE apprvl_status_code = p_status_code);

Line: 41

       SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
       INTO  x_in_use_flag, x_error_message_code
       FROM pa_project_assignments ppa,
            pa_schedules ps
       WHERE
       (    ( ppa.status_code = p_status_code)
       OR  ( ps.status_code = p_status_code)
       OR  ( ppa.apprvl_status_code = p_status_code))
       AND rownum = 1;

Line: 53

       SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
       INTO x_in_use_flag, x_error_message_code
       FROM pa_project_assignments
       WHERE status_code = p_status_code
       AND  rownum = 1;

Line: 65

          SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
          INTO x_in_use_flag, x_error_message_code
          FROM pa_project_assignments
          WHERE apprvl_status_code = p_status_code
          AND rownum = 1;

Line: 78

          SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
          INTO x_in_use_flag, x_error_message_code
          FROM pa_schedules
          WHERE status_code = p_status_code
          AND rownum = 1;

Line: 136

SELECT business_group_id FROM per_competences
WHERE  competence_id = p_competence_id;

Line: 231

    SELECT project_id
    INTO l_project_id
    FROM pa_project_assignments
    WHERE assignment_id = p_assignment_id ;

Line: 359

        SELECT DECODE (project_system_status_code,
               p_in_system_status_code,'Y','N')
    INTO l_ret_val
    FROM pa_project_statuses
    WHERE project_status_code = p_status_code
    AND   status_type = p_status_type;

Line: 384

    SELECT 'Y', 'PA_PROJ_ASSIGNMENTS_EXIST'
    INTO  x_assignments_exist_flag, x_error_message_code
    FROM dual
    WHERE EXISTS
    (SELECT 'x' FROM pa_project_assignments
     WHERE project_id = p_project_id);

Line: 391

    SELECT 'Y', 'PA_PROJ_ASSIGNMENTS_EXIST'
    INTO  x_assignments_exist_flag, x_error_message_code
    FROM pa_project_assignments
    WHERE project_id = p_project_id
    AND rownum=1;

Line: 422

 SELECT assignment_id
 FROM pa_project_assignments
 WHERE assignment_number = p_assignment_number;

Line: 433

           SELECT assignment_id
           INTO   x_assignment_id
           FROM   pa_project_assignments
           WHERE  assignment_number = p_assignment_number;

Line: 472

           SELECT assignment_id
           INTO   x_assignment_id
           FROM   pa_project_assignments
           WHERE  assignment_number = p_assignment_number;

Line: 515

            SELECT lookup_code
                INTO   x_staffing_priority_code
                FROM   pa_lookups
                WHERE  lookup_type = 'STAFFING_PRIORITY_CODE'
                        AND    lookup_code = p_staffing_priority_code;

Line: 525

        SELECT lookup_code
            INTO   x_staffing_priority_code
            FROM   pa_lookups
            WHERE  lookup_type = 'STAFFING_PRIORITY_CODE'
                AND    meaning = p_staffing_priority_name;

Line: 566

SELECT meaning
FROM pa_lookups
WHERE lookup_code = l_lookup_code
AND   lookup_type = 'TEAM_ROLE_ACTIVITY_TYPE';

Line: 638

SELECT message_text
  FROM fnd_new_messages
 WHERE message_name = p_msg_name
 and application_id = 275
 and language_code = userenv('LANG');

Line: 645

SELECT team_template_name
  FROM pa_team_templates
 WHERE team_template_id = g_team_template_id;

Line: 755

 SELECT proj.project_status_code, ps.project_status_name, ps2.project_system_status_code, ps2.project_status_name
 FROM   pa_projects_all proj,
        pa_project_statuses ps,
        pa_project_statuses ps2
 WHERE  project_id = p_project_id
   AND  proj.project_status_code = ps.project_status_code
   AND  ps2.project_status_code = p_asgmt_status_code;

Line: 854

SELECT assignment_id, assignment_name
FROM pa_proj_assignments_actuals_v
WHERE project_id = p_project_id
AND   person_id = p_person_id
AND   p_ei_date between start_date and end_date
AND   assignment_name = x_assignment_name
ORDER BY assignment_start_date DESC;

Line: 863

SELECT assignment_id, assignment_name
FROM pa_proj_assignments_actuals_v
WHERE project_id = p_project_id
AND   person_id = p_person_id
AND   p_ei_date between start_date and end_date
ORDER BY assignment_start_date DESC;

Line: 988

  SELECT sum(capacity_quantity)
  INTO   l_res_capacity_hrs
  FROM   pa_forecast_items
  WHERE  forecast_item_type = 'U'
  AND    delete_flag = 'N'
  AND    resource_id = p_resource_id
  AND    item_date BETWEEN p_asgn_start_date AND p_asgn_end_date;

Line: 1001

     SELECT sum(item_quantity)
     INTO   g_provisional_hours
     FROM   pa_forecast_items
     WHERE  forecast_item_type = 'A'
     AND    delete_flag = 'N'
     AND    provisional_flag = 'Y'
     AND    assignment_id = p_assignment_id;

Line: 1009

     SELECT provisional_flag
     INTO   l_provisional_flag
     FROM   pa_forecast_items
     WHERE  forecast_item_type = 'A'
     AND    delete_flag = 'N'
     AND    provisional_flag = 'Y'
     AND    assignment_id = p_assignment_id
     AND    resource_id = p_resource_id
     AND    item_date = p_asgn_start_date;

Line: 1092

SELECT pp.resource_source_id, res.full_name
FROM pa_project_parties pp
    ,per_all_people_f res
WHERE pp.project_role_id = c_project_role_id
AND pp.resource_type_id = 101  -- Bug 4752052 - added to improve performance
AND TRUNC(sysdate) between TRUNC(pp.start_date_active) and TRUNC(nvl(pp.end_date_active, sysdate))
AND pp.project_id = p_project_id
AND pp.resource_source_id = res.person_id
AND trunc(SYSDATE) BETWEEN res.effective_start_date AND res.effective_end_date --added for bug 4103207
AND (res.current_employee_flag = 'Y' OR res.current_npw_flag = 'Y')-- Added for bug 4938392
and pp.object_type = 'PA_PROJECTS'
and pp.object_id = p_project_id; -- Bug Ref # 	6802604

Line: 1110

   SELECT carrying_out_organization_id INTO l_exp_org_id
     FROM pa_projects_all
    WHERE project_id = p_project_id;

Line: 1168

 SELECT resource_source_id
   FROM pa_project_parties ,
        per_all_people_f ppf
  WHERE project_role_id  = 8
    AND resource_type_id = 101 -- Bug 4752052 - added to improve performance
    AND TRUNC ( sysdate ) BETWEEN TRUNC ( start_date_active ) AND TRUNC ( NVL ( end_date_active, sysdate ) )
    AND project_id    = p_project_id
    AND object_type   = 'PA_PROJECTS' -- Bug Ref # 6802604
    AND object_id     = p_project_id  -- Bug Ref # 6802604
    AND ppf.person_id = resource_source_id -- Bug Ref # 6802697
    AND TRUNC ( sysdate ) BETWEEN effective_start_date AND effective_end_date
    AND ( ppf.current_employee_flag = 'Y' OR ppf.current_npw_flag        = 'Y' )
UNION ALL
 SELECT staffing_owner_person_id
   FROM pa_project_assignments,
        per_all_people_f ppf
  WHERE assignment_id = p_assignment_id
    AND ppf.person_id = staffing_owner_person_id -- Bug Ref # 6802697
    AND TRUNC ( sysdate ) BETWEEN effective_start_date AND effective_end_date
    AND ( ppf.current_employee_flag = 'Y' OR ppf.current_npw_flag        = 'Y' ) ;

Line: 1253

select record_version_number
from pa_projects_all
where project_id = p_project_id;

Line: 1260

select asgn.assignment_id,
       asgn.fcst_job_id,
       asgn.expenditure_organization_id,
       asgn.expenditure_type,
       asgn.project_role_id,
       asgn.assignment_name
from pa_project_assignments asgn,
     pa_project_statuses ps
where asgn.project_id = p_project_id
and asgn.assignment_type = 'OPEN_ASSIGNMENT'
and asgn.status_code = ps.project_status_code(+)
and (ps.project_system_status_code = 'OPEN_ASGMT'
    OR ps.project_system_status_code IS NULL);

Line: 1279

select asgn.assignment_id,
       rta.person_id,
       aaf.job_id, --asgn.fcst_job_id,
       rd.resource_organization_id, --asgn.expenditure_organization_id,
       asgn.expenditure_type,
       asgn.project_role_id,
       decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK'), --ppt.system_person_type,
       asgn.assignment_name
from pa_project_assignments asgn,
     pa_project_statuses ps,
     per_person_type_usages_f ptuf,
     per_person_types ppt,
     per_all_assignments_f aaf,
     pa_resource_txn_attributes rta,
     pa_resources_denorm rd,
     per_all_people_f peo
where asgn.project_id = p_project_id
and asgn.resource_id = rta.resource_id
and rta.person_id = aaf.person_id
and asgn.start_date between aaf.effective_start_date AND aaf.effective_end_date
and asgn.assignment_type <> 'OPEN_ASSIGNMENT'
and asgn.status_code = ps.project_status_code(+)
and ps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
and rta.person_id = ptuf.person_id
and ptuf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('CWK', 'EMP')
and asgn.start_date between ptuf.effective_start_date AND ptuf.effective_end_date
and asgn.start_date between rd.resource_effective_start_date AND rd.resource_effective_end_date
and rd.resource_id = asgn.resource_id
and aaf.assignment_type in ('C','E')
and aaf.primary_flag = 'Y'
and peo.person_id = aaf.person_id
and asgn.start_date between peo.effective_start_date AND peo.effective_end_date;

Line: 1312

/*select asgn.assignment_id,
       rta.person_id,
       asgn.fcst_job_id,
       asgn.expenditure_organization_id,
       asgn.expenditure_type,
       asgn.project_role_id,
       ppt.system_person_type,
       asgn.assignment_name
from pa_project_assignments asgn,
     pa_project_statuses ps,
     per_person_type_usages_f ptuf,
     per_person_types ppt,
     per_all_assignments_f aaf,
     pa_resource_txn_attributes rta
where asgn.project_id = p_project_id
and asgn.resource_id = rta.resource_id
and rta.person_id = aaf.person_id
and asgn.start_date between aaf.effective_start_date AND aaf.effective_end_date
and asgn.assignment_type <> 'OPEN_ASSIGNMENT'
and asgn.status_code = ps.project_status_code(+)
and ps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
and rta.person_id = ptuf.person_id
and ptuf.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('CWK', 'EMP')
and asgn.start_date between ptuf.effective_start_date AND ptuf.effective_end_date;

Line: 1339

SELECT 'T'
from pa_project_assignments
where resource_list_member_id is not null
and project_id = p_project_id
and rownum = 1;

Line: 1410

    pa_resource_setup_pvt.UPDATE_ADDITIONAL_STAFF_INFO
         ( p_init_msg_list => FND_API.G_FALSE
          ,p_validate_only => FND_API.G_FALSE
          ,p_project_id    => p_project_id
          ,p_record_version_number => l_proj_rec_ver_num
          ,p_proj_req_res_format_id  => l_req_res_format_id
          ,p_proj_asgmt_res_format_id   => l_asgmt_res_format_id
          ,x_return_status        => l_return_status
          ,x_msg_count            => x_msg_count
          ,x_msg_data             => x_msg_data        );

Line: 1463

        UPDATE pa_project_assignments
         SET resource_list_member_id = l_res_list_member_id_tbl(i),
             record_version_number = nvl(record_version_number,0) + 1
         WHERE assignment_id = l_assignment_id_tbl(i);

Line: 1509

        UPDATE pa_project_assignments
         SET resource_list_member_id = l_res_list_member_id_tbl(i),
             record_version_number = nvl(record_version_number, 0) + 1
         WHERE assignment_id = l_assignment_id_tbl(i);

Line: 1578

select distinct pap.assignment_id
  from pa_project_assignments pap, pa_project_statuses stat
 where pap.resource_list_member_id = p_resource_list_member_id
   and pap.project_id = p_project_id
   and pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+)
   and nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not  in ('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');

Line: 1607

select distinct pap.assignment_type
  from pa_project_assignments pap, pa_project_statuses stat
 where pap.resource_list_member_id = p_resource_list_member_id
   and pap.project_id = p_project_id
   and pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+)
   and nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not  in ('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');

Line: 1638

    SELECT 'Y'
    INTO l_format_used_flag
    FROM pa_projects_all pa,
             pa_proj_fp_options pfo
    WHERE (pa.proj_req_res_format_id = p_res_format_id OR pa.proj_asgmt_res_format_id = p_res_format_id)
    AND pa.project_id = pfo.project_id
    AND pfo.cost_resource_list_id = p_resource_list_id
    AND pfo.fin_plan_type_id = (SELECT fin_plan_type_id
                                    FROM pa_fin_plan_types_b
                                    WHERE use_for_workplan_flag = 'Y')
    AND pfo.fin_plan_option_level_code = 'PLAN_TYPE'
    AND rownum = 1;

Line: 1660

select count(*) from pa_project_assignments pa
  where pa.project_id =  p_project_id
  and pa.resource_list_member_id = p_resource_list_member_id;

Line: 1665

select count(*) from pa_project_assignments pa
  where pa.project_id =  p_project_id
  and pa.resource_list_member_id = p_resource_list_member_id
  and pa.APPRVL_STATUS_CODE =  'ASGMT_APPRVL_SUBMITTED';

Line: 1711

select ra.schedule_start_date, pa.start_date, pa.end_date
from pa_resource_assignments ra , pa_project_assignments pa
where pa.assignment_id = ra.project_assignment_id
and ra.project_id = p_project_id
and ra.budget_version_id = p_budget_version_id
and ra.resource_list_member_id = p_resource_list_member_id;

Line: 1720

select ra.schedule_end_date, pa.end_date, pa.start_date
from pa_resource_assignments ra , pa_project_assignments pa
where pa.assignment_id = ra.project_assignment_id
and ra.project_id = p_project_id
and ra.budget_version_id = p_budget_version_id
and ra.resource_list_member_id = p_resource_list_member_id;

Line: 1798

select min(pap.start_date) team_role_start
from pa_project_assignments pap, pa_project_statuses stat
where
pap.resource_list_member_id = p_resource_list_member_id
and pap.project_id = p_project_id
and
pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+) and
nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not  in
('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');

Line: 1825

select max(pap.End_date) team_role_End
from pa_project_assignments pap, pa_project_statuses stat
where
pap.resource_list_member_id = p_resource_list_member_id
and pap.project_id = p_project_id
and
pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+) and
nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not  in
('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');

Line: 1860

select org_id
from pa_implementations where rownum = 1;

Line: 1864

select 'Y'
from pa_implementations
where org_id = p_org_id ;