APPS.CN_PLAN_TEXTS_PVT SQL Statements

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

Line: 10

SELECT 1
    FROM cn_srp_role_dtls_v s, cn_role_quota_cates r
    WHERE s.status not in ('PENDING','ACCEPTED')
    AND s.role_id = r.role_id
    AND r.role_model_id is NULL
    AND s.role_model_id is NULL
    AND s.role_id = c_role_id;

Line: 66

  G_LAST_UPDATE_DATE          DATE := Sysdate;

Line: 67

  G_LAST_UPDATED_BY           NUMBER := fnd_global.user_id;

Line: 70

  G_LAST_UPDATE_LOGIN         NUMBER := fnd_global.login_id;

Line: 117

   SELECT count(1)
     INTO l_temp_count
     FROM cn_plan_texts
    WHERE role_id = p_plan_text.role_id
      AND nvl(role_model_id, -1) = nvl(p_plan_text.role_model_id, -1)
      AND text_type = p_plan_text.text_type
      AND ( quota_category_id is NULL OR
            quota_category_id = nvl(p_plan_text.quota_category_id,
                                      quota_category_id)
          )
      AND ( sequence_id is NULL OR
            sequence_id = nvl(p_plan_text.sequence_id, sequence_id)
          );

Line: 139

   SELECT cn_plan_texts_s.NEXTVAL INTO l_plan_text_id FROM DUAL;

Line: 141

   CN_PLAN_TEXTS_PKG.Insert_Row
   (
    P_PLAN_TEXT_ID       => l_plan_text_id,
    P_ROLE_ID            => p_plan_text.role_id,
    P_SEQUENCE_ID        => p_plan_text.sequence_id,
    P_QUOTA_CATEGORY_ID  => p_plan_text.quota_category_id,
    P_TEXT_TYPE          => p_plan_text.text_type,
    P_TEXT               => p_plan_text.text,
    P_TEXT2              => p_plan_text.text2,
    P_OBJECT_VERSION_NUMBER  => 1,
    P_ROLE_MODEL_ID      => p_plan_text.role_model_id,
    P_CREATION_DATE      => G_CREATION_DATE,
    P_CREATED_BY         => G_CREATED_BY,
    P_LAST_UPDATE_DATE   => G_LAST_UPDATE_DATE,
    P_LAST_UPDATED_BY    => G_LAST_UPDATED_BY,
    P_LAST_UPDATE_LOGIN  => G_LAST_UPDATE_LOGIN
   );

Line: 223

PROCEDURE Update_Plan_Text (
  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_plan_text                   IN      plan_text_rec_type,
  x_return_status               OUT NOCOPY     VARCHAR2,
  x_msg_count                   OUT NOCOPY     NUMBER,
  x_msg_data                    OUT NOCOPY     VARCHAR2
) IS

  G_LAST_UPDATE_DATE          DATE := Sysdate;

Line: 235

  G_LAST_UPDATED_BY           NUMBER := fnd_global.user_id;

Line: 236

  G_LAST_UPDATE_LOGIN         NUMBER := fnd_global.login_id;

Line: 238

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

Line: 242

    SELECT
      object_version_number,
      attribute_category,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15
    FROM cn_plan_texts
    WHERE plan_text_id = P_PLAN_TEXT_ID;

Line: 270

   SAVEPOINT   Update_Plan_Text;

Line: 315

   SELECT count(1)
     INTO l_temp_count
     FROM cn_plan_texts
    WHERE role_id = p_plan_text.role_id
      AND nvl(role_model_id, -1) = nvl(p_plan_text.role_model_id, -1)
      AND text_type = p_plan_text.text_type
      AND ( quota_category_id is NULL OR
            quota_category_id = nvl(p_plan_text.quota_category_id,
                                    quota_category_id)
          )
      AND ( sequence_id is NULL OR
            sequence_id = nvl(p_plan_text.sequence_id, sequence_id)
          )
      AND plan_text_id <> p_plan_text.plan_text_id
          ;

Line: 351

   CN_PLAN_TEXTS_PKG.Update_Row
   (
    P_PLAN_TEXT_ID       => p_plan_text.plan_text_id,
    P_ROLE_ID            => p_plan_text.role_id,
    P_SEQUENCE_ID        => p_plan_text.sequence_id,
    P_QUOTA_CATEGORY_ID  => p_plan_text.quota_category_id,
    P_TEXT_TYPE          => p_plan_text.text_type,
    P_TEXT               => p_plan_text.text,
    P_TEXT2              => p_plan_text.text2,
    P_OBJECT_VERSION_NUMBER   => p_plan_text.object_version_number + 1,
    P_ROLE_MODEL_ID      => p_plan_text.role_model_id,
    P_ATTRIBUTE_CATEGORY => l_plan_text.attribute_category,
    P_ATTRIBUTE1         => l_plan_text.attribute1,
    P_ATTRIBUTE2         => l_plan_text.attribute2,
    P_ATTRIBUTE3         => l_plan_text.attribute3,
    P_ATTRIBUTE4         => l_plan_text.attribute4,
    P_ATTRIBUTE5         => l_plan_text.attribute5,
    P_ATTRIBUTE6         => l_plan_text.attribute6,
    P_ATTRIBUTE7         => l_plan_text.attribute7,
    P_ATTRIBUTE8         => l_plan_text.attribute8,
    P_ATTRIBUTE9         => l_plan_text.attribute9,
    P_ATTRIBUTE10        => l_plan_text.attribute10,
    P_ATTRIBUTE11        => l_plan_text.attribute11,
    P_ATTRIBUTE12        => l_plan_text.attribute12,
    P_ATTRIBUTE13        => l_plan_text.attribute13,
    P_ATTRIBUTE14        => l_plan_text.attribute14,
    P_ATTRIBUTE15        => l_plan_text.attribute15,
    P_LAST_UPDATE_DATE   => G_LAST_UPDATE_DATE,
    P_LAST_UPDATED_BY    => G_LAST_UPDATED_BY,
    P_LAST_UPDATE_LOGIN  => G_LAST_UPDATE_LOGIN
   );

Line: 395

     ROLLBACK TO Update_Plan_Text;

Line: 402

     ROLLBACK TO Update_Plan_Text;

Line: 409

     ROLLBACK TO Update_Plan_Text;

Line: 422

END Update_Plan_Text;

Line: 448

PROCEDURE Delete_Plan_Text (
  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_plan_text                   IN      plan_text_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) := 'Delete_Plan_Text';

Line: 464

   SAVEPOINT   Delete_Plan_Text;

Line: 482

   CN_PLAN_TEXTS_PKG.Delete_Row(p_plan_text.plan_text_id);

Line: 496

     ROLLBACK TO Delete_Plan_Text;

Line: 503

     ROLLBACK TO Delete_Plan_Text;

Line: 510

     ROLLBACK TO Delete_Plan_Text;

Line: 523

END Delete_Plan_Text;

Line: 571

   SELECT srp_id
     FROM cn_srp_role_dtls_v
    WHERE role_id = P_ROLE_ID
      AND role_model_id is NULL
      AND (status <> 'PENDING' or non_std_flag = 'Y' );

Line: 579

    SELECT
      plan_text_id,
      role_id,
      role_model_id,
      sequence_id,
      quota_category_id,
      text_type,
      text,
      text2,
      object_version_number
    FROM cn_plan_texts
    WHERE role_id = c_role_id
      AND nvl(role_model_id, -1) = nvl(c_role_model_id, -1)
    ORDER BY sequence_id;

Line: 624

     SELECT contract_title, terms_and_conditions,
            club_qual_text, approver_name,
            approver_title, approver_org_name
       INTO l_temp_con_title, l_temp_term_con,
            l_temp_club, l_temp_app_name,
            l_temp_app_title, l_temp_app_org
       FROM cn_sf_repositories;

Line: 792

    SELECT qc.quota_category_id quota_cate_id,
           qc.name quota_name
      FROM cn_quota_categories qc,
           cn_role_quota_cates pqc
     WHERE pqc.role_id = c_role_id
       AND qc.quota_category_id = pqc.quota_category_id
       AND qc.type = 'FIXED'
       AND nvl(pqc.role_model_id, -1) = nvl(c_role_model_id, -1)
    ORDER BY quota_cate_id;

Line: 927

    SELECT qc.quota_category_id quota_cate_id,
           qc.name quota_name
      FROM cn_quota_categories qc,
           cn_role_quota_cates pqc
     WHERE pqc.role_id = c_role_id
       and (NOT pqc.rate_schedule_id IS NULL)
       and qc.quota_category_id = pqc.quota_category_id
       and qc.type = 'VAR_QUOTA'
       and nvl(pqc.role_model_id, -1) = nvl(c_role_model_id, -1)
    ORDER BY quota_cate_id;

Line: 970

        SELECT meaning
          INTO x_quota_cates(l_ctr).quota_name
          FROM cn_lookups
         WHERE lookup_type = 'QUOTA_CATEGORY'
           AND lookup_code = 'TOTAL_QUOTA';

Line: 1075

    SELECT qc.quota_category_id quota_cate_id,
           qc.name quota_name
      FROM cn_quota_categories qc,
           cn_role_quota_cates pqc
     WHERE pqc.role_id = c_role_id
       and (NOT pqc.rate_schedule_id IS NULL)
       and qc.quota_category_id = pqc.quota_category_id
       and qc.type = c_quota_cate_type
       and nvl(pqc.role_model_id, -1) = nvl(c_role_model_id, -1)
    ORDER BY quota_cate_id;

Line: 1121

        SELECT meaning
          INTO x_quota_cates(l_ctr).quota_name
          FROM cn_lookups
         WHERE lookup_type = 'QUOTA_CATEGORY'
           AND lookup_code = 'TOTAL_QUOTA';

Line: 1241

     SELECT name INTO x_role_name
       FROM cn_role_details_v
      WHERE role_id = P_ROLE_ID;

Line: 1245

     SELECT name INTO x_role_name
       FROM cn_role_models
      WHERE role_model_id = P_ROLE_MODEL_ID;

Line: 1310

    SELECT  text,
            NVL(text2, ' ') text2
    FROM  cn_plan_texts
    WHERE role_id = i_role_id
    AND NVL(role_model_id, 0) = NVL(p_role_model_id, 0)
    AND quota_category_id = i_quota_category_id
    AND text_type = i_text_type
    ;

Line: 1321

    SELECT  NVL(text,  ' ') text,
            NVL(text2, ' ') text2
    FROM cn_plan_texts
    WHERE role_id = i_role_id
    AND NVL(role_model_id, 0) = NVL(p_role_model_id, 0)
    AND text_type = i_text_type
    ORDER BY sequence_id
    ;