APPS.CSK_SETUP_UTILITY_PKG SQL Statements

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

Line: 9

  insert into CS_KB_CATEGORY_GROUPS_B (
    CATEGORY_GROUP_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    P_ID,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351 );

Line: 24

  insert into CS_KB_CATEGORY_GROUPS_TL (
    CATEGORY_GROUP_ID,
    NAME,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    P_ID,
    P_NAME,
    P_NAME,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from CS_KB_CATEGORY_GROUPS_TL T
    where T.CATEGORY_GROUP_ID = P_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 59

  insert into CS_KB_SET_TYPES_B (
    SET_TYPE_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    P_ID,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351 );

Line: 74

  insert into CS_KB_SET_TYPES_TL (
    SET_TYPE_ID,
    NAME,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    P_ID,
    P_NAME,
    P_NAME,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from CS_KB_SET_TYPES_TL T
    where T.SET_TYPE_ID = P_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 109

  insert into CS_KB_ELEMENT_TYPES_B (
   ELEMENT_TYPE_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    P_ID,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351 );

Line: 124

  insert into CS_KB_ELEMENT_TYPES_TL (
    ELEMENT_TYPE_ID,
    NAME,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    P_ID,
    P_NAME,
    P_NAME,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from CS_KB_ELEMENT_TYPES_TL T
    where T.ELEMENT_TYPE_ID = P_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 159

  insert into CS_KB_VISIBILITIES_B (
    VISIBILITY_ID,
    POSITION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    P_ID,
    P_POSN,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351 );

Line: 176

  insert into CS_KB_VISIBILITIES_TL (
    VISIBILITY_ID,
    NAME,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    P_ID,
    P_NAME,
    P_NAME,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from CS_KB_VISIBILITIES_TL T
    where T.VISIBILITY_ID = P_ID
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 223

 DELETE FROM CS_KB_CATEGORY_GROUPS_B  WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_DEFAULT;

Line: 224

 DELETE FROM CS_KB_CATEGORY_GROUPS_TL WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_DEFAULT;

Line: 227

 DELETE FROM CS_KB_CATEGORY_GROUPS_B  WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG1;

Line: 228

 DELETE FROM CS_KB_CATEGORY_GROUPS_TL WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG1;

Line: 231

 DELETE FROM CS_KB_CATEGORY_GROUPS_B  WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG2;

Line: 232

 DELETE FROM CS_KB_CATEGORY_GROUPS_TL WHERE CATEGORY_GROUP_ID = CAT_GROUP_API_TEST_CG2;

Line: 238

 DELETE FROM CS_KB_SET_TYPES_B  WHERE SET_TYPE_ID = SOLN_TYPE_FAQ_API_TEST;

Line: 239

 DELETE FROM CS_KB_SET_TYPES_TL WHERE SET_TYPE_ID = SOLN_TYPE_FAQ_API_TEST;

Line: 245

 DELETE FROM CS_KB_ELEMENT_TYPES_B  WHERE ELEMENT_TYPE_ID = STMT_TYPE_FAQ_API_TEST;

Line: 246

 DELETE FROM CS_KB_ELEMENT_TYPES_TL WHERE ELEMENT_TYPE_ID = STMT_TYPE_FAQ_API_TEST;

Line: 252

 DELETE FROM CS_KB_SET_ELE_TYPES WHERE SET_TYPE_ID = SOLN_TYPE_FAQ_API_TEST AND ELEMENT_TYPE_ID = STMT_TYPE_FAQ_API_TEST;

Line: 253

 INSERT INTO CS_KB_SET_ELE_TYPES ( SET_TYPE_ID,ELEMENT_TYPE_ID,ELEMENT_TYPE_ORDER
                                  ,OPTIONAL_FLAG,CREATION_DATE,CREATED_BY
                                  ,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
 VALUES (SOLN_TYPE_FAQ_API_TEST, STMT_TYPE_FAQ_API_TEST, 1, 'Y', sysdate,
 -1351, sysdate, -1351, -1351);

Line: 262

 SELECT MAX(Position) INTO l_max
 FROM CS_KB_VISIBILITIES_B
 WHERE Visibility_id > 1;

Line: 266

 DELETE FROM CS_KB_VISIBILITIES_B  WHERE Visibility_ID = VISIBILITY_RESTRICTED_API_TEST;

Line: 267

 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_RESTRICTED_API_TEST;

Line: 269

 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+1
 WHERE VISIBILITY_ID = VISIBILITY_RESTRICTED_API_TEST;

Line: 272

 DELETE FROM CS_KB_VISIBILITIES_B  WHERE Visibility_ID = VISIBILITY_INTERNAL_API_TEST;

Line: 273

 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_INTERNAL_API_TEST;

Line: 275

 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+2
 WHERE VISIBILITY_ID = VISIBILITY_INTERNAL_API_TEST;

Line: 278

 DELETE FROM CS_KB_VISIBILITIES_B  WHERE Visibility_ID = VISIBILITY_LIMITED_API_TEST;

Line: 279

 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_LIMITED_API_TEST;

Line: 281

 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+3
 WHERE VISIBILITY_ID = VISIBILITY_LIMITED_API_TEST;

Line: 284

 DELETE FROM CS_KB_VISIBILITIES_B  WHERE Visibility_ID = VISIBILITY_EXTERNAL_API_TEST;

Line: 285

 DELETE FROM CS_KB_VISIBILITIES_TL WHERE Visibility_ID = VISIBILITY_EXTERNAL_API_TEST;

Line: 287

 UPDATE CS_KB_CAT_GROUP_DENORM SET VISIBILITY_POSITION = l_max+4
 WHERE VISIBILITY_ID = VISIBILITY_EXTERNAL_API_TEST;

Line: 293

 DELETE FROM CS_KB_WF_FLOWS_B  WHERE FLOW_ID = FLOW_API_TEST_FLOW;

Line: 294

 DELETE FROM CS_KB_WF_FLOWS_TL WHERE FLOW_ID = FLOW_API_TEST_FLOW;

Line: 295

 DELETE FROM CS_KB_WF_FLOW_DETAILS WHERE FLOW_ID = FLOW_API_TEST_FLOW;

Line: 297

   insert into CS_KB_WF_FLOWS_B (
    FLOW_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    FLOW_API_TEST_FLOW,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351 );

Line: 314

  insert into CS_KB_WF_FLOWS_TL (
    FLOW_ID,
    NAME,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    FLOW_API_TEST_FLOW,
    'Api Test Flow',
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    L.LANGUAGE_CODE,
    userenv('LANG')
  from FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and not exists
    (select NULL
    from CS_KB_WF_FLOWS_TL T
    where T.FLOW_ID = -1
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 342

 INSERT INTO CS_KB_WF_FLOW_DETAILS (FLOW_DETAILS_ID, FLOW_ID, STEP, ORDER_NUM,
                     ACTION, GROUP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
                     LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
 VALUES (-1,FLOW_API_TEST_FLOW,'TECHNICAL_REVIEW',10, 'NOT',100000121, -1351,sysdate,-1351,sysdate,-1351);

Line: 347

 INSERT INTO CS_KB_WF_FLOW_DETAILS (FLOW_DETAILS_ID, FLOW_ID, STEP, ORDER_NUM,
                     ACTION, GROUP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
                     LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
 VALUES (-2,FLOW_API_TEST_FLOW,'PUBLISHED',20, 'PUB',100000121, -1351,sysdate,-1351,sysdate,-1351);

Line: 375

 SELECT Position
 FROM CS_KB_VISIBILITIES_B
 WHERE VISIBILITY_ID = P_VISIBILITY_ID;

Line: 382

 DELETE FROM cs_kb_soln_categories_b WHERE CATEGORY_ID = P_CATEGORY_ID;

Line: 383

 DELETE FROM cs_kb_soln_categories_tl WHERE CATEGORY_ID = P_CATEGORY_ID;

Line: 385

 insert into CS_KB_SOLN_CATEGORIES_B
 (
      CATEGORY_ID,
      PARENT_CATEGORY_ID,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      VISIBILITY_ID
    )
    values
    ( P_CATEGORY_ID,
      P_PARENT_CATEGORY_ID,
      sysdate,
      -1351,
      sysdate,
      -1351,
      -1351,
      P_VISIBILITY_ID
 );

Line: 407

 insert into CS_KB_SOLN_CATEGORIES_TL
 (
      CATEGORY_ID,
      NAME,
      DESCRIPTION,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN,
      LANGUAGE,
      SOURCE_LANG
    )
    select
      P_CATEGORY_ID,
      P_CATEGORY_NAME,
      P_CATEGORY_NAME,
      sysdate,
      -1351,
      sysdate,
      -1351,
      -1351,
      L.LANGUAGE_CODE,
      userenv('LANG')
    from FND_LANGUAGES L
    where L.INSTALLED_FLAG in ('I', 'B')
    and not exists
      (select NULL
       from CS_KB_SOLN_CATEGORIES_TL T
       where T.CATEGORY_ID = P_CATEGORY_ID
       and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 443

 UPDATE CS_KB_CAT_GROUP_DENORM
 SET VISIBILITY_ID = P_VISIBILITY_ID,
     VISIBILITY_POSITION = l_posn
 WHERE CHILD_CATEGORY_ID = P_CATEGORY_ID;

Line: 454

procedure Delete_Category(
    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,
    x_return_status      OUT NOCOPY varchar2,
    x_msg_count          OUT NOCOPY number,
    x_msg_data           OUT NOCOPY varchar2,
    p_category_id in number)
is
    n_child_solutions number;

Line: 466

    l_delete_status   number;

Line: 469

  select /*+ index(sl) */ count( * ) into n_child_solutions
  from cs_kb_set_categories sl, cs_kb_sets_b b
  where sl.category_id = p_category_id
    and b.set_id = sl.set_id
    and (b.status = 'PUB' or (b.status <> 'OBS' and b.latest_version_flag = 'Y'));

Line: 475

  select count( * ) into n_subcatgories
  from cs_kb_soln_categories_b
  where parent_category_id = p_category_id;

Line: 484

     FND_MESSAGE.set_name('CS', 'CS_KB_C_CAT_DELETE_FAILED');

Line: 493

    delete /*+ index(sl) */  from cs_kb_set_categories sl
    where sl.category_id = p_category_id;

Line: 497

    delete from cs_kb_soln_categories_tl
    where category_id = p_category_id;

Line: 500

    delete from cs_kb_soln_categories_b
    where category_id = p_category_id;

Line: 505

    DELETE FROM CS_KB_CAT_GROUP_MEMBERS
    WHERE Category_Id = P_CATEGORY_ID;

Line: 508

    DELETE FROM CS_KB_CAT_GROUP_DENORM
    WHERE Child_Category_Id = P_CATEGORY_ID;

Line: 516

end Delete_Category;

Line: 544

dbms_output.put_line('Before Delete');

Line: 545

 DELETE FROM CS_KB_SETS_B  WHERE SET_ID = P_SOLN_REC.Set_id;

Line: 546

 DELETE FROM CS_KB_SETS_TL WHERE SET_ID = P_SOLN_REC.Set_id;

Line: 547

 DELETE FROM CS_KB_SET_CATEGORIES WHERE SET_ID = P_SOLN_REC.Set_id;

Line: 548

 DELETE FROM CS_KB_SET_ELES WHERE SET_ID = P_SOLN_REC.Set_id;

Line: 554

   DELETE FROM CS_KB_ELEMENTS_B  WHERE ELEMENT_ID = P_STMT_TBL(a).element_id;

Line: 556

   DELETE FROM CS_KB_ELEMENTS_TL WHERE ELEMENT_ID = P_STMT_TBL(a).element_id;

Line: 561

dbms_output.put_line('After Delete');

Line: 573

   INSERT INTO CS_KB_SETS_B (
    set_id,
    set_number,
    set_type_id,
    status,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    locked_by,
    priority_code,
    original_author,
    original_author_date,
    visibility_id,
    latest_version_flag,
    USAGE_SCORE,
    Flow_Details_id )
  VALUES (
    P_SOLN_REC.set_id,
    P_SOLN_REC.set_number,
    P_SOLN_REC.set_type_id,
    l_status,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    l_locked_by,
    4,
    -1351,
    sysdate,
    P_SOLN_REC.visibility_id,
    'Y',
     0,
     l_fdi );

Line: 610

  INSERT INTO CS_KB_SETS_TL (
    set_id,
    name,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    language,
    source_lang
  ) SELECT
    P_SOLN_REC.set_id,
    P_SOLN_REC.name,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    l.language_code,
    USERENV('LANG')
  FROM fnd_languages l
  WHERE l.installed_flag IN ('I', 'B')
  AND NOT EXISTS
    (SELECT NULL
     FROM CS_KB_SETS_TL t
     WHERE t.set_id = P_SOLN_REC.set_id
     AND t.language = l.language_code);

Line: 639

dbms_output.put_line('After Insert Solution');

Line: 651

  INSERT INTO CS_KB_ELEMENTS_B (
    element_id,
    element_number,
    element_type_id,
    status,
    access_level,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    content_type
  ) VALUES (
    P_STMT_TBL(i1).element_id,
    P_STMT_TBL(i1).element_number,
    P_STMT_TBL(i1).element_type_id,
    l_status,
    P_STMT_TBL(i1).access_level,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    P_STMT_TBL(i1).content_type );

Line: 676

  INSERT INTO CS_KB_ELEMENTS_TL (
    element_id,
    name,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login,
    language,
    source_lang
  ) SELECT
    P_STMT_TBL(i1).element_id,
    P_STMT_TBL(i1).name,
    sysdate,
    -1351,
    sysdate,
    -1351,
    -1351,
    l.language_code,
    USERENV('LANG')
  FROM FND_LANGUAGES l
  WHERE l.installed_flag IN ('I', 'B')
  AND NOT EXISTS
    (SELECT NULL
    FROM CS_KB_ELEMENTS_TL t
    WHERE t.element_id = P_STMT_TBL(i1).element_id
    AND t.language = l.language_code);

Line: 704

  INSERT INTO CS_KB_SET_ELES (SET_ID,
                             ELEMENT_ID,
                             ELEMENT_ORDER,
                             CREATION_DATE,
                             CREATED_BY,
                             LAST_UPDATE_DATE,
                             LAST_UPDATED_BY,
                             LAST_UPDATE_LOGIN)
                      VALUES( P_SOLN_REC.set_id,
                              P_STMT_TBL(i1).element_id,
                              b,
                              sysdate,
                              -1351,
                              sysdate,
                              -1351,
                              -1351);

Line: 727

     INSERT INTO CS_KB_SET_CATEGORIES (SET_ID,
                                       CATEGORY_ID,
                                       CREATION_DATE,
                                       CREATED_BY,
                                       LAST_UPDATE_DATE,
                                       LAST_UPDATED_BY,
                                       LAST_UPDATE_LOGIN )
     VALUES ( P_SOLN_REC.set_id,
              p_CAT_TBL(i2),
              sysdate,
              -1351,
              sysdate,
              -1351,
              -1351);

Line: 750

PROCEDURE Delete_Solution (
    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,
    x_return_status      OUT NOCOPY varchar2,
    x_msg_count          OUT NOCOPY number,
    x_msg_data           OUT NOCOPY varchar2,
    P_SET_ID             IN NUMBER)
IS
    CURSOR Get_Elements IS
        SELECT ELEMENT_ID
        FROM CS_KB_SET_ELES
        WHERE SET_ID = P_SET_ID;

Line: 767

    DELETE FROM CS_KB_SETS_B  WHERE SET_ID = P_SET_ID;

Line: 768

    DELETE FROM CS_KB_SETS_TL WHERE SET_ID = P_SET_ID;

Line: 770

    DELETE FROM CS_KB_SET_CATEGORIES WHERE SET_ID = P_SET_ID;

Line: 776

        DELETE FROM CS_KB_ELEMENTS_B  WHERE ELEMENT_ID = l_element_id;

Line: 777

        DELETE FROM CS_KB_ELEMENTS_TL WHERE ELEMENT_ID = l_element_id;

Line: 778

        DELETE FROM CS_KB_SET_ELES WHERE SET_ID = P_SET_ID and ELEMENT_ID = l_element_id;

Line: 784

END Delete_Solution;

Line: 799

    UPDATE CS_KB_SETS_B  SET STATUS = 'OBS' WHERE SET_ID = P_SET_ID;

Line: 808

    SELECT CS_KB_SETS_S.NEXTVAL INTO l_next_val FROM DUAL;

Line: 818

    SELECT CS_KB_SET_NUMBER_S.NEXTVAL INTO l_next_val FROM DUAL;

Line: 828

    SELECT CS_KB_ELEMENTS_S.NEXTVAL INTO l_next_val FROM DUAL;

Line: 838

    SELECT CS_KB_ELEMENT_NUMBER_S.NEXTVAL INTO l_next_val FROM DUAL;

Line: 848

    SELECT CS_KB_SOLN_CATEGORIES_S.NEXTVAL INTO l_next_val FROM DUAL;