APPS.CN_JOB_TITLE_PVT SQL Statements

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

Line: 12

      SELECT 1
      INTO l_count
      FROM cn_job_roles      jr
      WHERE jr.job_role_id  = p_job_role_id
      AND EXISTS (
          SELECT 1
          FROM cn_srp_role_dtls_v sr
          WHERE sr.job_title_id = jr.job_title_id
          AND jr.role_id      = sr.role_id );

Line: 44

  SELECT COUNT(sj.srp_id), min(sj.start_date),
         MAX(sj.end_date)
  into l_count,l_min_start_date , l_max_end_date
  from cn_job_roles      jr,
       cn_srp_job_titles sj,
       cn_srp_roles sr
 where jr.job_role_id  = p_rec.job_role_id
   and jr.job_title_id = sj.job_title_id
   and jr.role_id = sr.role_id
   and sj.srp_id = sr.salesrep_id
  ;

Line: 134

   select count(job_role_id) into l_count
     from cn_job_roles
    where job_title_id = l_newrec.job_title_id
      and default_flag = 'Y'
      and ((start_date <= l_newrec.start_date  and
       nvl(end_date, l_null_date) >=
                      l_newrec.start_date ) OR
           (start_date >= l_newrec.start_date  and
      start_date <= nvl(l_newrec.end_date, l_null_date)));

Line: 150

   select count(job_role_id) into l_count
     from cn_job_roles
    where job_title_id = l_newrec.job_title_id
      and role_id = l_newrec.role_id
      and ((start_date <= l_newrec.start_date  and
       nvl(end_date, l_null_date) >=
                      l_newrec.start_date ) OR
           (start_date >= l_newrec.start_date  and
      start_date <= nvl(l_newrec.end_date, l_null_date)));

Line: 166

  select count(1) into l_count
     from cn_job_roles
    where role_id = l_newrec.role_id
      and job_title_id = l_newrec.job_title_id
      and trunc(start_date) = trunc(l_newrec.start_date)
       ;

Line: 179

   cn_job_title_pkg.insert_row(l_newrec);

Line: 228

PROCEDURE Update_Job_Role
  (p_api_version                IN      NUMBER,     -- required
   p_init_msg_list              IN      VARCHAR2 := FND_API.G_FALSE,
   p_commit                     IN      VARCHAR2 := FND_API.G_FALSE,
   p_validation_level           IN      NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_rec                        IN      job_role_rec_type,
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

   l_api_name                   CONSTANT VARCHAR2(30) := 'Update_Job_Role';

Line: 248

   SAVEPOINT   Update_Job_Role;

Line: 277

   select count(*) into l_count
     from cn_job_roles
    where job_role_id = l_newrec.job_role_id;

Line: 282

      FND_MESSAGE.SET_NAME('CN', 'CN_INVALID_UPDATE_REC');

Line: 297

   select start_date, end_date
     into l_old_start, l_old_end
     from cn_job_roles
    where job_role_id = l_newrec.job_role_id;

Line: 308

   select count(job_role_id) into l_count
     from cn_job_roles
    where job_title_id = l_newrec.job_title_id
      and job_role_id <> l_newrec.job_role_id
      and default_flag = 'Y'
      and ((start_date <= l_newrec.start_date  and
        nvl(end_date, l_null_date) >= l_newrec.start_date) OR
           (start_date >= l_newrec.start_date  and
      start_date <= nvl(l_newrec.end_date, l_null_date)));

Line: 323

   select count(job_role_id) into l_count
     from cn_job_roles
    where job_title_id = l_newrec.job_title_id
      and job_role_id <> l_newrec.job_role_id
      and role_id = l_newrec.role_id
      and ((start_date <= l_newrec.start_date  and
       nvl(end_date, l_null_date) >=
                      l_newrec.start_date ) OR
           (start_date >= l_newrec.start_date  and
      start_date <= nvl(l_newrec.end_date, l_null_date)));

Line: 344

   cn_job_title_pkg.update_row(l_newrec);

Line: 357

      ROLLBACK TO Update_Job_Role;

Line: 364

      ROLLBACK TO Update_Job_Role;

Line: 371

      ROLLBACK TO Update_Job_Role;

Line: 380

END Update_Job_Role;

Line: 392

PROCEDURE Delete_Job_Role
  (p_api_version                IN      NUMBER,
   p_init_msg_list              IN      VARCHAR2 := FND_API.G_FALSE,
   p_commit                     IN      VARCHAR2 := FND_API.G_FALSE,
   p_validation_level           IN      NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_job_role_id                IN      cn_job_roles.job_role_id%type,
   p_object_version_number      IN      cn_job_roles.object_version_number%type,
   x_return_status              OUT NOCOPY     VARCHAR2,
   x_msg_count                  OUT NOCOPY     NUMBER,
   x_msg_data                   OUT NOCOPY     VARCHAR2) IS

   l_api_name                   CONSTANT VARCHAR2(30) := 'Delete_Job_Role';

Line: 409

   SAVEPOINT   Delete_Job_Role;

Line: 426

   select count(*) into l_count
     from cn_job_roles
    where job_role_id = p_job_role_id;

Line: 439

   cn_job_title_pkg.delete_row(p_job_role_id);

Line: 452

      ROLLBACK TO Delete_Job_Role;

Line: 459

      ROLLBACK TO Delete_Job_Role;

Line: 466

      ROLLBACK TO Delete_Job_Role;

Line: 475

END Delete_Job_Role;

Line: 493

   select job_role_id, job_title_id, role_id,
          start_date, end_date, nvl(default_flag, 'N'),
          attribute_category,
          attribute1,  attribute2,  attribute3,  attribute4,  attribute5,
          attribute6,  attribute7,  attribute8,  attribute9,  attribute10,
          attribute11, attribute12, attribute13, attribute14, attribute15,
          object_version_number
     from cn_job_roles
    where job_title_id = p_job_title_id
    order by start_date;

Line: 537

   select job_title_id, name, job_code,
    null role_id, null role_name
     from cn_job_titles
    where upper(name)     like l_name
      and upper(job_code) like l_code
    order by name;

Line: 602

   select distinct * from
  (select jr.job_title_id,
          j.name job_name,
          j.job_code,
          jr.role_id,
          r.role_name
     from cn_job_roles  jr,
          cn_job_titles j,
          jtf_rs_roles_vl r
    where jr.job_title_id = j.job_title_id
      and r.role_type_code= 'SALES_COMP'
      and (r.member_flag = 'Y' OR r.manager_flag = 'Y') -- added check
      and jr.role_id      = r.role_id
    order by jr.job_title_id, jr.default_flag DESC);