eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_PROJECT_PARTIES_UTILS SQL Statements

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

Line: 6


FUNCTION VALIDATE_DELETE_PARTY_OK (p_project_id  IN NUMBER,
                                   p_project_party_id IN NUMBER) RETURN VARCHAR2 IS

BEGIN

  RETURN 'Y';

Line: 13


END VALIDATE_DELETE_PARTY_OK;

Line: 36


 /* Commenting this query and  selecting start date from PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE */
 /*  select start_date, completion_date
     into x_project_start_date, x_project_end_date
     from pa_projects_all
    where project_id = p_project_id;  */

Line: 42


    SELECT PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE(p_project_id), completion_date
     INTO x_project_start_date, x_project_end_date
     FROM pa_projects_all
    WHERE project_id = p_project_id;

Line: 84


   SELECT 'Y'
   FROM pa_project_parties ppp,
        pa_project_role_types_b r1,
        pa_project_role_types_b r2
   WHERE r1.project_role_id = p_project_role_id
   AND r1.role_party_class = 'CUSTOMER'
   AND ppp.object_id = p_object_id
   AND ppp.object_type = p_object_type
   AND ppp.resource_type_id = 112
   AND ppp.resource_source_id = p_resource_source_id
   AND r2.project_role_id = ppp.project_role_id
   AND r2.role_party_class = 'CUSTOMER'
   AND ROWNUM=1;

Line: 180


        SELECT 'Y'
        INTO l_dummy
        FROM pa_project_role_types_b
        WHERE project_role_id = p_project_role_id
        AND  p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,p_start_date_active)
        AND (p_end_date_active IS NULL
             OR p_end_date_active BETWEEN start_date_active AND NVL(end_date_active,p_end_date_active));

Line: 199


     IF (p_action = 'INSERT' OR pa_project_parties_utils.get_scheduled_flag(p_project_party_id, p_record_version_number) <> p_scheduled_flag) THEN

     IF p_resource_type_id = 112 THEN
       IF (p_debug_mode = 'Y') THEN
              pa_debug.debug('Validate_project_party: Looking for customer org duplicate.');

Line: 244


     IF p_scheduled_flag = 'N' AND p_action = 'UPDATE' THEN
         IF (p_debug_mode = 'Y') THEN
              pa_debug.debug('Validate_project_party: Need to delete schedule.');

Line: 250


         x_assignment_action := 'DELETE';

Line: 254


   IF x_assignment_action = 'CREATE' OR (p_action = 'INSERT' AND p_calling_module <> 'PROJECT_MEMBER') THEN
       --dbms_output.put_line('trying to get person id');

Line: 288


  IF x_call_overlap = 'Y' OR p_action = 'UPDATE' OR p_calling_module = 'EXCHANGE' THEN

   IF p_project_role_id = 1 THEN  -- hard coded for Project Manager
      IF (p_debug_mode = 'Y') THEN
              pa_debug.debug('Validate_project_party: Calling validate_no_overlap_manager.');

Line: 341


  SELECT scheduled_flag INTO l_scheduled_flag
    FROM pa_project_parties
   WHERE project_party_id = NVL(p_project_party_id,-999)
     AND record_version_number = NVL(p_record_version_number,record_version_number);

Line: 396


  SELECT project_party_id, record_version_number INTO x_project_party_id, x_record_version_number
    FROM pa_project_parties
   WHERE object_type = p_object_type
     AND object_id = p_object_id
     AND project_role_id = p_project_role_id
     AND resource_type_id = p_resource_type_id
     AND resource_source_id = p_resource_source_id
     AND start_date_active = TRUNC(p_start_date_active)
     AND end_date_active = TRUNC(p_end_date_active);

Line: 508


   SELECT 'Y' INTO l_error_occured
     FROM pa_project_parties
    WHERE object_type = p_object_type
      AND object_id = p_object_id
      AND project_role_id = p_project_role_id
      AND project_party_id <> NVL(p_project_party_id,-999)
      AND (p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
       OR NVL(p_end_date_active, p_start_date_active) BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
       OR start_date_active BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
       OR NVL(end_Date_active,start_date_active) BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
       OR (p_start_date_active > start_date_active AND end_date_active IS NULL));

Line: 556


   SELECT 'Y' INTO l_error_occured
     FROM pa_project_parties
    WHERE object_type = p_object_type
      AND object_id = p_object_id
      AND project_role_id = p_project_role_id
      AND resource_type_id = p_resource_type_id
      AND resource_source_id = p_resource_source_id
      AND project_party_id <> NVL(p_project_party_id,-999)
      AND (p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
       OR NVL(p_end_date_active, p_start_date_active) BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
       OR start_date_active BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
       OR NVL(end_date_active,start_date_active) BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
       OR (p_start_date_active > start_date_active AND  end_date_active IS NULL));

Line: 598


       SELECT project_role_id INTO l_project_role_id
         FROM pa_project_role_types_vl
        WHERE (meaning = p_project_role_type AND p_calling_module <> 'FORM')
           OR (project_role_type = p_project_role_type AND p_calling_module = 'FORM');

Line: 624


  SELECT person_id INTO l_resource_id
    FROM pa_employees
   WHERE full_name = p_resource_name
     AND active = '*';

Line: 668


SELECT grant_id INTO l_grant_id
  FROM pa_project_parties
 WHERE project_party_id = p_project_party_id;

Line: 698


  SELECT ppp.resource_source_id,
         ppp.project_party_id,
         ppp.project_role_id,
         pprt.meaning,
         pe.full_name
    INTO x_manager_person_id,
         x_project_party_id,
         x_project_role_id,
         x_project_role_name,
         x_manager_name
    FROM pa_project_parties  ppp,
         pa_project_role_types pprt,
         per_all_people_f pe
   WHERE ppp.project_id = p_project_id
     AND ppp.project_role_id = 1
     AND ppp.project_role_id = pprt.project_role_id
     AND ppp.resource_type_id = 101
     AND ppp.resource_source_id = pe.person_id
     AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date
     AND pe.effective_end_date
     AND ppp.object_type = 'PA_PROJECTS'
     AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active AND NVL(ppp.end_date_active,TRUNC(SYSDATE)+1);

Line: 722


    SELECT ppp.resource_source_id,
         ppp.project_party_id,
         ppp.project_role_id,
         pprt.meaning,
         pe.full_name
    INTO x_manager_person_id,
         x_project_party_id,
         x_project_role_id,
         x_project_role_name,
         x_manager_name
    FROM pa_project_parties  ppp,
         pa_project_role_types pprt,
         per_all_people_f pe
   WHERE ppp.project_id = p_project_id
     AND ppp.project_role_id = 1
     AND ppp.project_role_id = pprt.project_role_id
     AND ppp.resource_type_id = 101
     AND ppp.resource_source_id = pe.person_id
     AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date
     AND pe.effective_end_date
     AND ppp.object_type = 'PA_PROJECTS'
     AND ppp.start_date_active > TRUNC(SYSDATE)
     AND ppp.start_date_active = (SELECT MIN(ppp1.start_date_active)
                                    FROM pa_project_parties ppp1
                                   WHERE ppp1.project_id = p_project_id
                                     AND ppp1.project_role_id = 1
                                     AND ppp1.start_date_active > TRUNC(SYSDATE));

Line: 771


  SELECT project_party_id
  INTO ret
  FROM pa_project_parties p,
       pa_project_role_types_b r,
       pa_customers_v c
  WHERE r.role_party_class = 'CUSTOMER'
    AND p.project_role_id = r.project_role_id
    AND p.project_id = p_project_id
    AND p.resource_type_id = 112
    AND c.party_id = p.resource_source_id

    AND c.customer_id = p_customer_id;

Line: 802


    SELECT party_type
    INTO l_party_type
    FROM hz_parties
    WHERE  party_id = p_resource_source_id;

Line: 809


  SELECT role_party_class
  INTO l_role_party_class
  FROM pa_project_role_types_b
  WHERE project_role_id = p_project_role_id;

Line: 838


Select PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES         PPP  ,
     --PA_PROJECT_ROLE_TYPES      PPRT --bug 4004821
     PA_PROJECT_ROLE_TYPES_B      PPRT
WHERE
    PPP.PROJECT_ID                      = p_project_id
AND PPP.PROJECT_ROLE_ID                 = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id 		= 101             --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE              ='PROJECT MANAGER'
AND trunc(c_as_of_date)  between trunc(PPP.start_date_active)
AND                         NVL(trunc(PPP.end_date_active),c_as_of_date);

Line: 894


Select full_name
FROM per_all_people_f
WHERE PERSON_ID = c_person_id
AND trunc(SYSDATE) between trunc(effective_start_date) and trunc(effective_end_date);  -- Bug 3283351

Line: 917


Select ppf.full_name
FROM PA_PROJECT_PARTIES         PPP,
     --PA_PROJECT_ROLE_TYPES      PPRT, --bug 4004821
     PA_PROJECT_ROLE_TYPES_B      PPRT,
     per_all_people_f           PPF
WHERE
    PPP.PROJECT_ID                      = p_project_id
AND PPP.PROJECT_ROLE_ID                 = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id                = 101             --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE              ='PROJECT MANAGER'
AND trunc(c_as_of_date)  between trunc(PPP.start_date_active)
AND                         NVL(trunc(PPP.end_date_active),c_as_of_date)
AND ppf.person_id = ppp.resource_source_id
AND trunc(c_as_of_date) between trunc(PPF.effective_start_date) AND trunc(PPF.effective_end_date); -- Added for bug 3283351

Line: 991


select ps.project_system_status_code
      ,type.project_type_class_code
      ,proj.project_type  -- Added for bug#5098966
      ,trunc(proj.start_date)
      ,trunc(proj.completion_date)
 from pa_projects_all proj
     ,pa_project_statuses ps
     ,pa_project_types type
where proj.project_status_code = ps.project_status_code
  and proj.project_type = type.project_type
  and proj.project_id = p_project_id
  and ps.STATUS_TYPE = 'PROJECT';

Line: 1008


select person_id
      ,start_date_active
      ,end_date_active
  from pa_project_players
 where project_id = p_project_id
   and project_role_type='PROJECT MANAGER'
order by start_date_active;

Line: 1017


select project_party_id
      ,start_date_active
      ,end_date_active
 from pa_project_parties
where project_id = p_project_id
  and project_role_id = 1
order by start_date_active;

Line: 1040


		select person_id
		      ,trunc(start_date_active)
		      ,trunc(end_date_active)
		  from pa_project_players
		 where project_id = p_project_id
		   and project_role_type='PROJECT MANAGER'
		   AND NOT (
		   exists(
		   SELECT 'y' FROM dual WHERE trunc(start_date_active) > trunc(l_proj_end_date)
		   )
		   OR
		   exists(
		   SELECT 'y' FROM dual WHERE trunc(end_date_active) < trunc(l_proj_start_date)
		   )
		   )
		order by start_date_active;

Line: 1058


		select project_party_id
		      ,trunc(start_date_active)
		      ,trunc(end_date_active)
		 from pa_project_parties
		where project_id = p_project_id
		  and project_role_id = 1
		  AND NOT (
		  exists(
		   SELECT 'y' FROM dual WHERE trunc(start_date_active) > trunc(l_proj_end_date)
		   )
		   OR
		   exists(
		   SELECT 'y' FROM dual WHERE trunc(end_date_active) < trunc(l_proj_start_date)
		   )
		   )
		order by start_date_active;

Line: 1200


  select ps.project_system_status_code
        ,type.project_type_class_code
    from pa_projects_all proj
        ,pa_project_statuses ps
        ,pa_project_types type
where proj.project_status_code = ps.project_status_code
  and proj.project_type=type.project_type
  and proj.project_id = p_project_id
  and ps.STATUS_TYPE ='PROJECT';

Line: 1212


  select count(*)
    from pa_project_parties
   where project_id = p_project_id
     and project_role_id = 1;

Line: 1295


Select PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES         PPP  ,
     --PA_PROJECT_ROLE_TYPES      PPRT --bug 4004821
     PA_PROJECT_ROLE_TYPES_B      PPRT
WHERE
    PPP.PROJECT_ID                      = p_project_id
AND PPP.PROJECT_ROLE_ID                 = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id                = 101             --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE              ='PROJECT MANAGER'
AND trunc(c_as_of_date)  between trunc(PPP.start_date_active)
AND                         NVL(trunc(PPP.end_date_active),c_as_of_date);

Line: 1340


Select ppf.full_name
FROM PA_PROJECT_PARTIES         PPP,
     --PA_PROJECT_ROLE_TYPES      PPRT, --bug 4004821
     PA_PROJECT_ROLE_TYPES_B      PPRT,
     per_all_people_f           PPF
WHERE
    PPP.PROJECT_ID                      = p_project_id
AND PPP.PROJECT_ROLE_ID                 = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id                = 101             --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE              ='PROJECT MANAGER'
AND trunc(c_as_of_date)  between trunc(PPP.start_date_active)
AND                         NVL(trunc(PPP.end_date_active),c_as_of_date)
AND ppf.person_id = ppp.resource_source_id
AND trunc(c_as_of_date) between trunc(PPF.effective_start_date) AND trunc(PPF.effective_end_date)                          -- Added for bug 3283351
   and ppp.object_type = 'PA_PROJECTS'       --Added for Bug 10071170
   and ppp.object_id = p_project_id;         --Added for Bug 10071170