APPS.PA_PURGE_VALIDATE_PJRM SQL Statements

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

Line: 48

      select 'Assignment or Requirement Exists' , 'PA_ARPR_ASG_REQ_EXISTS'
      from dual
      where exists ( select NULL
                     from   pa_project_assignments pa
                     where  nvl(pa.project_id, 0) = p_project_id
                    )
      UNION
      select 'Administrative or Unassigned Time Type' , 'PA_ARPR_ADM_UNASS_PRJ_TYP'
      from dual
      where exists ( select    pt.project_type
                     from      pa_project_types_all pt,
                               pa_projects_all p
                      where    p.project_id = p_project_id
                      and      pt.project_type = p.project_type
                      and    ( nvl(pt.administrative_flag, 'N') = 'Y'
                            or nvl(pt.unassigned_time, 'N') = 'Y' ));

Line: 146

The below cursor will select any open requirement for the passed project id. If the
Cursor return any row, it means that project contains requirements in open status, so
Requirement and project cannot be purged. This curor is applicable in case of Closed
Projects.

  CURSOR CUR_REQUIREMENTS_CLOSED IS
  SELECT 1
  FROM pa_project_assignments pa, pa_project_statuses ps, pa_projects pr
  WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
  AND pa.status_code = ps.project_status_code
  AND ps.status_type='OPEN_ASGMT'
  AND ps.project_system_status_code ='OPEN_ASGMT'
  AND pa.project_id = P_PROJECT_ID
  AND pa.end_date > nvl(p_txn_to_date,pr.closed_date)
  AND pa.project_id=pr.project_id;

Line: 164

/*The below cursor will select any open requirement for the passed project id which
exist before purge Till Date. If the cursor return any row,it means that project
contains requirements in open status, so Requirement and project cannot be purged.
This cursor is applicable for Open Indirect Project Purge.*/

   CURSOR CUR_REQUIREMENTS_ACTIVE IS
   SELECT 1
   FROM pa_project_assignments pa, pa_project_statuses ps
   WHERE pa.assignment_type = 'OPEN_ASSIGNMENT'
   AND pa.status_code = ps.project_status_code
   AND ps.status_type='OPEN_ASGMT'
   AND ps.project_system_status_code ='OPEN_ASGMT'
   AND pa.project_id = P_PROJECT_ID
   AND p_active_flag = 'A'
   AND pa.end_date <= P_txn_to_date;

Line: 277

/*This cursor will select any assignments for project passed (closed Project)
  Which is having assignment end date greater than Purge Till Date and NOT cancelled.
  This is required, as even there exist any assignment with cancelled status with end
  Date Greater than purge till date, the assignment can be purged.
  Also, as p_txn_to_date will be NULL in case of closed project purge, the assignment
  Dates are compared with project Closed Date*/

    CURSOR CUR_ASSIGNMENTS IS
    SELECT 1
    FROM  Pa_project_assignments pa
	, pa_project_statuses ps
	, pa_projects pr
   WHERE pa.project_id = P_Project_Id
   AND pa.assignment_type <>'OPEN_ASSIGNMENT'
   AND nvl(p_txn_to_date,Pr.CLOSED_DATE) < pa.end_date
   AND pa.status_code = ps.project_status_code
   AND ps.status_type = 'STAFFED_ASGMT'
   AND ps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
   AND pr.project_id =pa.project_id;

Line: 401

   SELECT 1
     FROM DUAL
    WHERE EXISTS
            (SELECT NULL
               FROM pa_project_fundings
              WHERE project_id = p_project_id
                AND pji_summarized_flag = 'N');

Line: 410

   SELECT 1
     FROM DUAL
    WHERE EXISTS
            (SELECT NULL
               FROM pa_draft_revenues_all
              WHERE project_id = p_project_id
                AND released_date IS NOT NULL
                AND transfer_status_code = 'A'
                AND pji_summarized_flag = 'N');

Line: 421

   SELECT 1
     FROM DUAL
    WHERE EXISTS
            (SELECT NULL
               FROM pa_cost_distribution_lines_all
              WHERE project_id = p_project_id
                AND line_type IN ('R', 'I')
                AND pji_summarized_flag = 'N');

Line: 431

   SELECT 1
     FROM DUAL
    WHERE EXISTS
            (SELECT NULL
               FROM pa_draft_invoices_all
              WHERE project_id = p_project_id
                AND system_reference IS NOT NULL
                AND system_reference <> 0
                AND pji_summarized_flag = 'N');

Line: 442

   SELECT 1
     FROM DUAL
    WHERE EXISTS
            (SELECT NULL
               FROM pa_pji_proj_events_log
              WHERE event_type in ('Projects', 'Classifications', 'DRAFT_REVENUES')
	      and event_object =  p_project_id); /* Added this condition for bug 3807671 */

Line: 463

         l_string := 'SELECT COUNT(*) FROM pji_system_parameters';

Line: 474

      l_string := 'SELECT BIS_COMMON_PARAMETERS.get_global_start_date FROM DUAL';

Line: 477

      SELECT project_status_code
        INTO l_project_status
        FROM pa_projects_all
       WHERE project_id = p_project_id;

Line: 531

   SELECT 1 from dual
      where exists (select 1
                    from   pa_projects_all
                    where  project_id=p_project_id
                    and    pji_source_flag='Y');