APPS.ICX_CAT_SCHEMA_UPLOAD_PVT SQL Statements

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

Line: 24

SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('B', 'I');

Line: 84

      SELECT 1
      INTO l_num_val
      FROM DUAL
      WHERE EXISTS (SELECT 1
                    FROM icx_cat_attributes_tl
                    WHERE UPPER(key) = UPPER(p_key)
                    AND language = p_language);

Line: 96

      SELECT 1
      INTO l_num_val
      FROM DUAL
      WHERE EXISTS (SELECT 1
                    FROM icx_cat_attributes_tl
                    WHERE UPPER(key) = UPPER(p_key)
                    AND language = p_language
                    AND (rt_category_id = p_cat_id OR
                         rt_category_id = 0));

Line: 129

        SELECT 1
        INTO l_num_val
        FROM DUAL
        WHERE EXISTS (SELECT 1
                      FROM icx_cat_attributes_tl
                      WHERE UPPER(attribute_name) = UPPER(p_name)
                      AND language = p_language);

Line: 139

        SELECT 1
        INTO l_num_val
        FROM DUAL
        WHERE EXISTS (SELECT 1
                      FROM icx_cat_attributes_tl
                      WHERE UPPER(attribute_name) = UPPER(p_name)
                      AND language = p_language
                      AND (rt_category_id = p_cat_id OR
                           rt_category_id = 0));

Line: 172

      SELECT COUNT(*)
      INTO l_num_val
      FROM icx_cat_attributes_tl
      WHERE rt_category_id = 0
      AND language = p_language
      AND to_char(type) = p_type
      AND attribute_id > 100;

Line: 196

      SELECT instr(section_map, '0', l_start_pos, 1)
      INTO l_first_zero_pos
      FROM icx_cat_categories_tl
      WHERE rt_category_id = 0
      AND language = p_language;

Line: 212

      SELECT COUNT(*)
      INTO l_num_val
      FROM icx_cat_attributes_tl
      WHERE rt_category_id = p_cat_id
      AND language = p_language
      AND to_char(type) = p_type;

Line: 235

      SELECT instr(section_map, '0', l_start_pos, 1)
      INTO l_first_zero_pos
      FROM icx_cat_categories_tl
      WHERE rt_category_id = p_cat_id
      AND language = p_language;

Line: 280

  insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
    'ICX_CAT_KEY', p_key);

Line: 285

  insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
    'ICX_CAT_NAME', p_name);

Line: 290

  insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
    'ICX_CAT_TYPE', p_type);

Line: 295

  insert_failed_line(p_request_id, p_line_number, p_action, 'CATEGORY',
    'ICX_CAT_DESCRIPTION', p_description);

Line: 334

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_KEY', p_key);

Line: 339

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_NAME', p_name);

Line: 344

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_TYPE', p_type);

Line: 349

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_DESCRIPTION', p_description);

Line: 354

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_OWNER_KEY', p_owner_key);

Line: 359

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_OWNER_NAME', p_owner_name);

Line: 364

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_SEQUENCE', p_sequence);

Line: 369

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_SEARCHABLE', p_searchable);

Line: 374

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_ITEM_DETAIL_VISIBLE', p_item_detail_visible);

Line: 379

  insert_failed_line(p_request_id, p_line_number, p_action, 'DESCRIPTOR',
    'ICX_CAT_SEARCH_RESULTS_VISIBLE', p_search_results_visible);

Line: 412

  insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
    'ICX_CAT_PARENT_KEY', p_parent_key);

Line: 417

  insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
    'ICX_CAT_PARENT_NAME', p_parent_name);

Line: 422

  insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
    'ICX_CAT_CHILD_KEY', p_child_key);

Line: 427

  insert_failed_line(p_request_id, p_line_number, p_action, 'RELATIONSHIP',
    'ICX_CAT_CHILD_NAME', p_child_name);

Line: 444

PROCEDURE insert_failed_line
(
  p_request_id IN NUMBER,
  p_line_number IN NUMBER,
  p_action IN VARCHAR2,
  p_row_type IN VARCHAR2,
  p_descriptor_key IN VARCHAR2,
  p_descriptor_value IN VARCHAR2
)
IS
  l_err_loc PLS_INTEGER;

Line: 458

  INSERT INTO icx_por_failed_lines
    (job_number, line_number, action, row_type, descriptor_key, descriptor_value,
    request_id, program_id, program_application_id, program_login_id)
  VALUES (p_request_id, p_line_number, p_action, p_row_type, p_descriptor_key,
    p_descriptor_value, p_request_id, fnd_global.conc_program_id,
    fnd_global.prog_appl_id, fnd_global.conc_login_id);

Line: 473

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.insert_failed_line(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 475

END insert_failed_line;

Line: 478

PROCEDURE insert_failed_message
(
  p_request_id IN NUMBER,
  p_descriptor_key IN VARCHAR2,
  p_message_name IN VARCHAR2,
  p_line_number IN NUMBER
)
IS
  l_err_loc PLS_INTEGER;

Line: 490

  INSERT INTO icx_por_failed_line_messages
    (job_number, descriptor_key, message_name, line_number, request_id, program_id,
    program_application_id, program_login_id)
  VALUES (p_request_id, p_descriptor_key, p_message_name, p_line_number, p_request_id,
    fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);

Line: 504

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.insert_failed_message(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 506

END insert_failed_message;

Line: 531

  SELECT icx_por_categoryid.nextval
  INTO x_category_id
  FROM dual;

Line: 541

    INSERT INTO icx_cat_categories_tl
      (rt_category_id, language, source_lang, category_name, description, type,
      key, upper_key, upper_category_name, request_id, rebuild_flag, section_map,
      created_by, creation_date, last_updated_by, last_update_date, last_update_login,
      program_id, program_application_id, program_login_id)
    VALUES (x_category_id, language_row.language_code, p_language, p_name,
      p_description, p_type, p_key, upper(p_key), upper(p_name), p_request_id,
      'N', lpad('0', '300', '0'), p_user_id, sysdate, p_user_id, sysdate, p_login_id,
      fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id);

Line: 569

PROCEDURE update_category
(
  p_category_id IN NUMBER,
  p_language IN VARCHAR2,
  p_name IN VARCHAR2,
  p_description IN VARCHAR2,
  p_type IN NUMBER,
  p_request_id IN NUMBER,
  p_user_id IN NUMBER,
  p_login_id IN NUMBER
)
IS
  l_err_loc PLS_INTEGER;

Line: 587

    SELECT language
    FROM icx_cat_categories_tl
    WHERE rt_category_id = p_category_id
    and type = p_type
    and source_lang = p_language
    and  source_lang <> language
    UNION
    SELECT p_language FROM dual;

Line: 612

    UPDATE icx_cat_categories_tl
    SET category_name = nvl(p_name, category_name),
      upper_category_name = nvl(upper(p_name), upper_category_name),
      description = decode(p_description, '#DEL', null, null, description, p_description),
      source_lang = p_language,
      last_updated_by = p_user_id,
      last_update_date = sysdate,
      last_update_login = p_login_id,
      request_id = p_request_id,
      rebuild_flag = decode(p_name, category_name, rebuild_flag, null, rebuild_flag, 'Y'),
      program_id = fnd_global.conc_program_id,
      program_application_id = fnd_global.prog_appl_id,
      program_login_id = fnd_global.conc_login_id
    WHERE rt_category_id = p_category_id
    AND language = l_lang_table(i);

Line: 635

      UPDATE icx_cat_items_ctx_hdrs_tlp
      SET ip_category_name = p_name
      WHERE ip_category_id = p_category_id
      AND language = l_lang_table(i);

Line: 650

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_category(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 652

END update_category;

Line: 658

PROCEDURE delete_category
(
  p_category_id IN NUMBER,
  p_language IN VARCHAR2,
  p_request_id IN NUMBER,
  p_user_id IN NUMBER,
  p_login_id IN NUMBER
)
IS
  l_err_loc PLS_INTEGER;

Line: 675

    SELECT browse.child_category_id, cat.type, count(*)
    FROM icx_cat_browse_trees browse, icx_cat_categories_tl cat
    WHERE browse.child_category_id IN
      (SELECT child_category_id
       FROM icx_cat_browse_trees
      WHERE parent_category_id = p_category_id)
    AND browse.child_category_id = cat.rt_category_id
    AND language = p_language
    GROUP BY browse.child_category_id, cat.type;

Line: 695

    IF (can_category_be_deleted(p_category_id) = 0) THEN

      l_err_loc := 300;

Line: 701

      DELETE FROM icx_cat_categories_tl
      WHERE rt_category_id = p_category_id;

Line: 707

      DELETE FROM icx_cat_attributes_tl
      WHERE rt_category_id = p_category_id;

Line: 713

      DELETE FROM icx_por_category_order_map
      WHERE rt_category_id = p_category_id;

Line: 720

      DELETE FROM icx_por_category_data_sources
      WHERE rt_category_id = p_category_id;

Line: 724

      DELETE FROM icx_cat_browse_trees
      WHERE child_category_id = p_category_id;

Line: 735

        DELETE FROM icx_cat_browse_trees
        WHERE parent_category_id = p_category_id
        AND child_category_id = l_child_cat_table(i);

Line: 745

        INSERT INTO icx_cat_browse_trees
         	 (parent_category_id, child_category_id, created_by, creation_date,
           last_updated_by, last_update_date, last_update_login, request_id,
           program_id, program_application_id, program_login_id)
        SELECT 0, l_child_cat_table(i), p_user_id, sysdate, p_user_id, sysdate, p_login_id, p_request_id,
        fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
        FROM dual
        WHERE l_child_cat_type_table(i) = 1
        AND l_parent_cat_count_table(i) = 1;

Line: 767

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_category(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 769

END delete_category;

Line: 800

      insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_KEY_REQUIRED',
        p_line_number);

Line: 816

      SELECT rt_category_id, type
      INTO x_category_id, l_current_type
      FROM icx_cat_categories_tl
      WHERE upper_key = upper(p_key)
      AND language = p_language
      AND rownum = 1;

Line: 829

        x_system_action := 'UPDATE';

Line: 832

        x_system_action := 'DELETE';

Line: 842

      IF (p_user_action = 'DELETE') THEN
        l_err_loc := 260;

Line: 844

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_DOES_NOT_EXIST',
          p_line_number);

Line: 865

    IF (x_system_action IN ('ADD', 'UPDATE')) THEN
      -- we default type to 2 if add and not provided
      IF (x_system_action = 'ADD' AND p_type is null) THEN
        l_err_loc := 320;

Line: 877

          SELECT lookup_code INTO x_converted_type
          FROM fnd_lookup_values
          WHERE lookup_type = 'ICX_CAT_TYPE'
          AND UPPER(meaning) = UPPER(p_type)
          AND language = p_language;

Line: 886

          insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_INVALID_CAT_TYPE',
        p_line_number);

Line: 907

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CATEGORY_NAME_REQUIRED',
          p_line_number);

Line: 912

        SELECT count(1) INTO l_count
        FROM icx_cat_categories_tl
        WHERE upper_category_name = UPPER(p_name);

Line: 920

          insert_failed_message(p_request_id, 'ICX_CAT_NAME', 'ICX_CAT_CAT_NAME_NONUNIQUE_ADD',
            p_line_number);

Line: 926

    ELSIF (x_system_action = 'UPDATE') THEN

      l_err_loc := 600;

Line: 934

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_UPD_ROOT_CAT',
          p_line_number);

Line: 942

        SELECT count(1) INTO l_count
        FROM icx_cat_categories_tl
        WHERE upper_category_name = UPPER(p_name)
        and rt_category_id <> x_category_id;

Line: 951

          insert_failed_message(p_request_id, 'ICX_CAT_NAME', 'ICX_CAT_CAT_NAME_NONUNIQUE_UPD',
            p_line_number);

Line: 962

        insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_CANNOT_CHANGE_CAT_TYPE',
          p_line_number);

Line: 967

    ELSIF (x_system_action = 'DELETE') THEN

      l_err_loc := 700;

Line: 974

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_DEL_ROOT_CAT',
          p_line_number);

Line: 980

      IF (can_category_be_deleted(x_category_id) = 1) THEN
        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CAT_HAS_ITEMS',
          p_line_number);

Line: 1004

FUNCTION can_category_be_deleted
(
  p_ip_category_id IN NUMBER
)
RETURN NUMBER
IS
  l_err_loc PLS_INTEGER;

Line: 1019

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM icx_cat_items_ctx_hdrs_tlp
                  WHERE ip_category_id = p_ip_category_id);

Line: 1039

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM po_lines_all
                  WHERE ip_category_id = p_ip_category_id);

Line: 1058

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM po_lines_draft_all
                  WHERE ip_category_id = p_ip_category_id);

Line: 1076

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_auction_item_prices_all
                  WHERE ip_category_id = p_ip_category_id);

Line: 1093

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_item_prices_interface
                  WHERE ip_category_id = p_ip_category_id);

Line: 1110

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_auc_items_interface
                  WHERE ip_category_id = p_ip_category_id);

Line: 1129

    SELECT 1
    INTO l_category_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_item_prices_interface
                  WHERE ip_category_name IN (SELECT category_name
                                             FROM icx_cat_categories_tl
                                             WHERE rt_category_id = p_ip_category_id));

Line: 1152

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.can_category_be_deleted(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 1154

END can_category_be_deleted;

Line: 1176

  SELECT type
  INTO l_type
  FROM icx_cat_categories_tl
  WHERE rt_category_id = p_parent_id
  AND rownum = 1;

Line: 1191

    INSERT INTO icx_cat_browse_trees(parent_category_id, child_category_id,
      created_by, creation_date, last_updated_by, last_update_date, last_update_login,
      request_id, program_id, program_application_id, program_login_id)
    SELECT p_parent_id, p_child_id, p_user_id, sysdate, p_user_id, sysdate, p_login_id,
    p_request_id, fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
    FROM dual
    WHERE NOT EXISTS (SELECT 1
                      FROM icx_cat_browse_trees
                      WHERE parent_category_id = p_parent_id
                      AND child_category_id = p_child_id);

Line: 1209

      DELETE FROM icx_cat_browse_trees
      WHERE parent_category_id = 0
      AND child_category_id = p_child_id;

Line: 1217

      INSERT INTO icx_cat_browse_trees(parent_category_id, child_category_id,
      created_by, creation_date, last_updated_by, last_update_date, last_update_login,
      request_id, program_id, program_application_id, program_login_id)
      SELECT 0, p_parent_id, p_user_id, sysdate, p_user_id, sysdate, p_login_id,
      p_request_id, fnd_global.conc_program_id, fnd_global.prog_appl_id, fnd_global.conc_login_id
      FROM dual
      WHERE NOT EXISTS (SELECT 1
                        FROM icx_cat_browse_trees
                        WHERE child_category_id = p_parent_id);

Line: 1253

PROCEDURE delete_relationship
(
  p_parent_id IN NUMBER,
  p_child_id IN NUMBER,
  p_request_id IN NUMBER,
  p_line_number IN NUMBER,
  p_action IN VARCHAR2
)
IS
  l_err_loc PLS_INTEGER;

Line: 1270

  SELECT type
  INTO l_type
  FROM icx_cat_categories_tl
  WHERE rt_category_id = p_parent_id
  AND rownum = 1;

Line: 1282

    DELETE FROM icx_cat_browse_trees
    WHERE parent_category_id = p_parent_id
    AND child_category_id = p_child_id;

Line: 1300

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_relationship(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 1302

END delete_relationship;

Line: 1344

    x_system_action := 'DELETE';

Line: 1353

    insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY', 'ICX_CAT_PARENT_KEY_NAME_REQD',
      p_line_number);

Line: 1372

      SELECT rt_category_id, type
      INTO x_parent_id, l_parent_type
      FROM icx_cat_categories_tl
      WHERE upper_key = UPPER(p_parent_key)
      AND language = p_language;

Line: 1383

          insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
            'ICX_CAT_PARENT_KEY_NOT_EXIST', p_line_number);

Line: 1399

      SELECT rt_category_id, type
      INTO l_parent_id_from_name, l_parent_type
      FROM icx_cat_categories_tl
      WHERE upper_category_name = UPPER(p_parent_name)
      AND language = p_language;

Line: 1410

          insert_failed_message(p_request_id, 'ICX_CAT_PARENT_NAME',
            'ICX_CAT_PARENT_NAME_NOT_EXIST', p_line_number);

Line: 1423

        insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
          'ICX_CAT_PARENT_KEY_NAME_DIFF', p_line_number);

Line: 1439

        insert_failed_message(p_request_id, 'ICX_CAT_PARENT_KEY',
          'ICX_CAT_ITEM_CAT_CANNOT_PARENT', p_line_number);

Line: 1444

        insert_failed_message(p_request_id, 'ICX_CAT_PARENT_NAME',
          'ICX_CAT_ITEM_CAT_CANNOT_PARENT', p_line_number);

Line: 1459

    insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY', 'ICX_CAT_CHILD_KEY_NAME_REQD',
      p_line_number);

Line: 1478

      SELECT rt_category_id
      INTO x_child_id
      FROM icx_cat_categories_tl
      WHERE upper_key = UPPER(p_child_key)
      AND language = p_language;

Line: 1489

          insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
            'ICX_CAT_CHILD_KEY_NOT_EXIST', p_line_number);

Line: 1505

      SELECT rt_category_id
      INTO l_child_id_from_name
      FROM icx_cat_categories_tl
      WHERE upper_category_name = UPPER(p_child_name)
      AND language = p_language;

Line: 1516

          insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
            'ICX_CAT_CHILD_NAME_NOT_EXIST', p_line_number);

Line: 1529

        insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
          'ICX_CAT_CHILD_KEY_NAME_DIFF', p_line_number);

Line: 1558

          insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
            'ICX_CAT_SAME_PARENT_CHILD', p_line_number);

Line: 1563

          insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
            'ICX_CAT_SAME_PARENT_CHILD', p_line_number);

Line: 1572

      SELECT COUNT(*)
      INTO l_count
      FROM icx_cat_browse_trees
      WHERE child_category_id = x_parent_id
      START WITH parent_category_id = x_child_id
      CONNECT BY NOCYCLE PRIOR child_category_id = parent_category_id;

Line: 1586

          insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
            'ICX_CAT_NO_CYCLIC_RELATIONSHIP', p_line_number);

Line: 1591

          insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
            'ICX_CAT_NO_CYCLIC_RELATIONSHIP', p_line_number);

Line: 1603

        SELECT 1 INTO l_count
        FROM dual
        WHERE EXISTS (SELECT 1 FROM icx_cat_browse_trees
                      WHERE parent_category_id = x_parent_id
                      AND child_category_id = x_child_id);

Line: 1616

              insert_failed_message(p_request_id, 'ICX_CAT_CHILD_KEY',
                'ICX_CAT_REL_NOT_EXISTS', p_line_number);

Line: 1621

              insert_failed_message(p_request_id, 'ICX_CAT_CHILD_NAME',
                'ICX_CAT_REL_NOT_EXISTS', p_line_number);

Line: 1689

    SELECT floor(nvl(max(sequence), 0)) + 1
    INTO l_sequence
    FROM icx_cat_attributes_tl
    WHERE rt_category_id = p_category_id
    AND language = p_language;

Line: 1733

  SELECT icx_por_descriptorid.nextval
  INTO x_descriptor_id
  FROM dual;

Line: 1740

  INSERT INTO icx_cat_attributes_tl (attribute_id, rt_category_id, language,
    source_lang, attribute_name, description, type, key, sequence, searchable,
    search_results_visible, item_detail_visible, request_id, rebuild_flag,
    created_by, creation_date, last_updated_by, last_update_date, last_update_login,
    program_id, program_application_id, program_login_id)
  SELECT x_descriptor_id, p_category_id, fnd_languages.language_code, p_language,
    p_name, decode(p_description, '#DEL', null, p_description), to_number(l_type),
    p_key, l_sequence, to_number(l_searchable), l_search_results_visible,
    l_item_detail_visible, p_request_id, 'N', p_user_id, sysdate,
    p_user_id, sysdate, p_login_id, fnd_global.conc_program_id,
    fnd_global.prog_appl_id, fnd_global.conc_login_id
  FROM fnd_languages
  WHERE installed_flag IN ('B', 'I');

Line: 1805

  UPDATE icx_cat_schema_versions
  SET version = version + 1,
      last_updated_by = p_user_id,
      last_update_date = sysdate,
      last_update_login = p_login_id,
      request_id = p_request_id,
      program_id = fnd_global.conc_program_id,
      program_application_id = fnd_global.prog_appl_id,
      program_login_id = fnd_global.conc_login_id
  WHERE descriptor_set_id = l_attribute_id;

Line: 1856

  SELECT section_tag, stored_in_table, stored_in_column
  INTO p_section_tag, p_stored_in_table, p_stored_in_column
  FROM icx_cat_attributes_tl
  WHERE attribute_id = p_descriptor_id
  AND rownum = 1;

Line: 1872

      SELECT section_map, INSTR(section_map, '0', 1, 1)
      INTO l_section_map, p_section_tag
      FROM icx_cat_categories_tl
      WHERE rt_category_id = p_category_id
      AND rownum = 1;

Line: 1881

      SELECT section_map, INSTR(section_map, '0', 101, 1)
      INTO l_section_map, p_section_tag
      FROM icx_cat_categories_tl
      WHERE rt_category_id = p_category_id
      AND rownum = 1;

Line: 1890

      SELECT section_map, INSTR(section_map, '0', 201, 1)
      INTO l_section_map, p_section_tag
      FROM icx_cat_categories_tl
      WHERE rt_category_id = p_category_id
      AND rownum = 1;

Line: 1959

      UPDATE icx_cat_categories_tl
      SET section_map = l_section_map,
          request_id = p_request_id,
          program_id = fnd_global.conc_program_id,
          program_application_id = fnd_global.prog_appl_id,
          program_login_id = fnd_global.conc_login_id
      WHERE rt_category_id = p_category_id;

Line: 1970

      UPDATE icx_cat_attributes_tl
      SET section_tag = p_section_tag,
          stored_in_table = p_stored_in_table,
          stored_in_column = p_stored_in_column,
          request_id = p_request_id,
          program_id = fnd_global.conc_program_id,
          program_application_id = fnd_global.prog_appl_id,
          program_login_id = fnd_global.conc_login_id
      WHERE attribute_id = p_descriptor_id;

Line: 2022

  SELECT section_map
  INTO l_section_map
  FROM icx_cat_categories_tl
  WHERE rt_category_id = p_category_id
  AND rownum = 1;

Line: 2031

  SELECT section_tag
  INTO l_section_tag
  FROM icx_cat_attributes_tl
  WHERE attribute_id = p_descriptor_id
  AND rownum = 1;

Line: 2045

    UPDATE icx_cat_attributes_tl
    SET section_tag = null,
        request_id = p_request_id,
        program_id = fnd_global.conc_program_id,
        program_application_id = fnd_global.prog_appl_id,
        program_login_id = fnd_global.conc_login_id
    WHERE attribute_id = p_descriptor_id;

Line: 2071

    UPDATE icx_cat_categories_tl
    SET section_map = l_section_map,
        request_id = p_request_id,
        program_id = fnd_global.conc_program_id,
        program_application_id = fnd_global.prog_appl_id,
        program_login_id = fnd_global.conc_login_id
    WHERE rt_category_id = p_category_id;

Line: 2096

PROCEDURE update_descriptor
(
  p_descriptor_id IN NUMBER,
  p_name IN VARCHAR2,
  p_description IN VARCHAR2,
  p_category_id IN VARCHAR2,
  p_sequence IN NUMBER,
  p_search_results_visible IN VARCHAR2,
  p_item_detail_visible IN VARCHAR2,
  p_searchable IN VARCHAR2,
  p_language IN VARCHAR2,
  p_request_id IN NUMBER,
  p_user_id IN NUMBER,
  p_login_id IN NUMBER
)
IS
  l_err_loc PLS_INTEGER;

Line: 2121

  SELECT to_char(searchable)
  INTO l_searchable
  FROM icx_cat_attributes_tl
  WHERE attribute_id = p_descriptor_id
  AND rownum = 1;

Line: 2139

  UPDATE icx_cat_attributes_tl
  SET attribute_name = nvl (p_name, attribute_name),
      description = decode(p_description, '#DEL', null, null, description, p_description),
      source_lang = p_language,
      last_updated_by = p_user_id,
      last_update_date = sysdate,
      last_update_login = p_login_id,
      request_id = p_request_id,
      program_id = fnd_global.conc_program_id,
      program_application_id = fnd_global.prog_appl_id,
      program_login_id = fnd_global.conc_login_id
  WHERE attribute_id = p_descriptor_id
  AND language = p_language;

Line: 2156

  UPDATE icx_cat_attributes_tl
  SET sequence = decode (p_sequence, '#DEL', null, null, sequence, p_sequence),
      searchable = to_number(nvl(p_searchable, searchable)),
      search_results_visible = to_number(nvl(p_search_results_visible, search_results_visible)),
      item_detail_visible = to_number(nvl(p_item_detail_visible, item_detail_visible)),
      rebuild_flag = l_rebuild_flag,
      last_updated_by = p_user_id,
      last_update_date = sysdate,
      last_update_login = p_login_id,
      request_id = p_request_id,
      program_id = fnd_global.conc_program_id,
      program_application_id = fnd_global.prog_appl_id,
      program_login_id = fnd_global.conc_login_id
  WHERE attribute_id = p_descriptor_id;

Line: 2185

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_descriptor(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 2187

END update_descriptor;

Line: 2192

PROCEDURE delete_descriptor
(
  p_descriptor_id IN NUMBER,
  p_request_id IN NUMBER,
  p_user_id IN NUMBER,
  p_login_id IN NUMBER
)
IS
  l_err_loc PLS_INTEGER;

Line: 2213

    IF (can_descriptor_be_deleted(p_descriptor_id) = 0) THEN

      l_err_loc := 120;

Line: 2218

      SELECT rt_category_id, to_char(searchable)
      INTO l_category_id, l_searchable
      FROM icx_cat_attributes_tl
      WHERE attribute_id = p_descriptor_id
      AND rownum = 1;

Line: 2232

      DELETE from icx_cat_attributes_tl
      WHERE attribute_id = p_descriptor_id;

Line: 2257

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.delete_descriptor(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 2259

END delete_descriptor;

Line: 2266

FUNCTION can_descriptor_be_deleted
(
  p_descriptor_id IN NUMBER
)
RETURN NUMBER
IS
  l_err_loc PLS_INTEGER;

Line: 2282

  SELECT rt_category_id, stored_in_table, stored_in_column
  INTO l_category_id, l_stored_in_table, l_stored_in_column
  FROM icx_cat_attributes_tl
  WHERE attribute_id = p_descriptor_id
  AND rownum = 1;

Line: 2297

    'SELECT 1 '||
    'FROM dual ' ||
    'WHERE EXISTS ( ' ||
           'SELECT 1 ' ||
           'FROM ' || l_stored_in_table ||
           ' WHERE ' || l_stored_in_table || '.' || l_stored_in_column || ' IS NOT NULL' ||
           ' AND ip_category_id = decode(' || l_category_id || ', 0, ip_category_id, ' || l_category_id || '))'
    INTO l_descriptor_referenced;

Line: 2329

    'SELECT 1 '||
    'FROM dual ' ||
    'WHERE EXISTS ( ' ||
           'SELECT 1 ' ||
           'FROM ' || l_draft_stored_in_table ||
           ' WHERE ' || l_draft_stored_in_table || '.' || l_stored_in_column || ' IS NOT NULL' ||
           ' AND ip_category_id = decode(' || l_category_id || ', 0, ip_category_id, ' || l_category_id || '))'
    INTO l_descriptor_referenced;

Line: 2349

    SELECT 1
    INTO l_descriptor_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_auction_attributes
                  WHERE ip_descriptor_id = p_descriptor_id
                  AND ip_category_id = l_category_id);

Line: 2367

    SELECT 1
    INTO l_descriptor_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_auc_attributes_interface
                  WHERE ip_descriptor_id = p_descriptor_id
                  AND ip_category_id = l_category_id);

Line: 2385

    SELECT 1
    INTO l_descriptor_referenced
    FROM dual
    WHERE EXISTS (SELECT 1
                  FROM pon_attributes_interface
                  WHERE ip_descriptor_id = p_descriptor_id
                  AND ip_category_id = l_category_id);

Line: 2407

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.can_descriptor_be_deleted(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 2409

END can_descriptor_be_deleted;

Line: 2453

    insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_OWNER_KEY_NAME_REQD',
      p_line_number);

Line: 2466

        SELECT rt_category_id
        INTO x_owner_id
        FROM icx_cat_categories_tl
        WHERE upper_key = UPPER(p_owner_key)
        AND language = p_language
        AND type IN (0,2);

Line: 2476

          insert_failed_message(p_request_id, 'ICX_CAT_OWNER_KEY',
            'ICX_CAT_INVALID_OWNER_KEY', p_line_number);

Line: 2491

        SELECT rt_category_id
        INTO l_owner_id_from_name
        FROM icx_cat_categories_tl
        WHERE upper_category_name = UPPER(p_owner_name)
        AND language = p_language
        AND type IN (0,2);

Line: 2500

          insert_failed_message(p_request_id, 'ICX_CAT_OWNER_NAME',
            'ICX_CAT_INVALID_OWNER_NAME', p_line_number);

Line: 2510

        insert_failed_message(p_request_id, 'ICX_CAT_OWNER_KEY',
          'ICX_CAT_OWNER_KEY_NAME_DIFF', p_line_number);

Line: 2527

    insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESCRIPTOR_KEY_REQD',
      p_line_number);

Line: 2548

      SELECT attribute_id, to_char(type)
      INTO x_descriptor_id, l_current_type
      FROM icx_cat_attributes_tl
      WHERE UPPER(key) = UPPER(p_key)
      AND language = p_language
      AND rt_category_id = x_owner_id
      AND rownum = 1;

Line: 2562

        x_system_action := 'UPDATE';

Line: 2563

      ELSIF (p_user_action = 'DELETE') THEN
        l_err_loc := 490;

Line: 2565

        x_system_action := 'DELETE';

Line: 2577

        ELSIF (p_user_action = 'DELETE') THEN
          l_err_loc := 210;

Line: 2579

          x_system_action := 'DELETE';

Line: 2580

          insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_DESC_DOES_NOT_EXIST',
            p_line_number);

Line: 2603

          SELECT 1
          INTO l_num_val
          FROM DUAL
          WHERE EXISTS (SELECT 1
                        FROM icx_cat_attributes_tl
                        WHERE UPPER(key) = UPPER(p_key)
                        AND language = p_language);

Line: 2612

          SELECT 1
          INTO l_num_val
          FROM DUAL
          WHERE EXISTS (SELECT 1
                        FROM icx_cat_attributes_tl
                        WHERE UPPER(key) = UPPER(p_key)
                        AND language = p_language
                        AND (rt_category_id = x_owner_id OR
                             rt_category_id = 0));

Line: 2625

        insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_DESC_KEY_NONUNIQUE',
          p_line_number);

Line: 2639

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESCRIPTOR_NAME_REQD',
          p_line_number);

Line: 2654

            SELECT 1
            INTO l_num_val
            FROM DUAL
            WHERE EXISTS (SELECT 1
                          FROM icx_cat_attributes_tl
                          WHERE UPPER(attribute_name) = UPPER(p_name)
                          AND language = p_language);

Line: 2664

            SELECT 1
            INTO l_num_val
            FROM DUAL
            WHERE EXISTS (SELECT 1
                          FROM icx_cat_attributes_tl
                          WHERE UPPER(attribute_name) = UPPER(p_name)
                          AND language = p_language
                          AND (rt_category_id = x_owner_id OR
                               rt_category_id = 0));

Line: 2677

          insert_failed_message(p_request_id,'ICX_CAT_NAME','ICX_CAT_DES_NAME_NONUNIQUE_ADD',
            p_line_number);

Line: 2696

          SELECT COUNT(*)
          INTO l_num_val
          FROM icx_cat_attributes_tl
          WHERE rt_category_id = 0
          AND language = p_language
          AND to_char(type) = p_type
          AND attribute_id > 100;

Line: 2720

          SELECT instr(section_map, '0', l_start_pos, 1)
          INTO l_first_zero_pos
          FROM icx_cat_categories_tl
          WHERE rt_category_id = 0
          AND language = p_language;

Line: 2730

            insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_BASE_ATT_NUM_EXCEED',
              p_line_number);

Line: 2737

          SELECT COUNT(*)
          INTO l_num_val
          FROM icx_cat_attributes_tl
          WHERE rt_category_id = x_owner_id
          AND language = p_language
          AND to_char(type) = p_type;

Line: 2760

          SELECT instr(section_map, '0', l_start_pos, 1)
          INTO l_first_zero_pos
          FROM icx_cat_categories_tl
          WHERE rt_category_id = x_owner_id
          AND language = p_language;

Line: 2770

            insert_failed_message(p_request_id,'ICX_CAT_KEY','ICX_CAT_CAT_ATT_NUM_EXCEED',
              p_line_number);

Line: 2779

    ELSIF (x_system_action = 'UPDATE') THEN

      l_err_loc := 690;

Line: 2794

            SELECT 1
            INTO l_num_val
            FROM dual
            WHERE EXISTS (SELECT 1
                          FROM icx_cat_attributes_tl
                          WHERE UPPER(attribute_name) = UPPER(p_name)
                          AND attribute_id <> x_descriptor_id);

Line: 2804

            SELECT 1
            INTO l_num_val
            FROM dual
            WHERE EXISTS (SELECT 1
                          FROM icx_cat_attributes_tl
                          WHERE UPPER(attribute_name) = UPPER(p_name)
                          AND (rt_category_id = x_owner_id OR
                              rt_category_id = 0)
                          AND attribute_id <> x_descriptor_id);

Line: 2817

          insert_failed_message(p_request_id,'ICX_CAT_NAME','ICX_CAT_DES_NAME_NONUNIQUE_UPD',
            p_line_number);

Line: 2834

          insert_failed_message(p_request_id, 'ICX_CAT_TYPE', 'ICX_CAT_CANNOT_CHANGE_DES_TYPE',
            p_line_number);

Line: 2852

          insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SEQUENCE',
            p_line_number);

Line: 2869

          insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SRCHABLE',
            p_line_number);

Line: 2882

          insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_CHANGE_SRV',
            p_line_number);

Line: 2890

    ELSIF (x_system_action = 'DELETE') THEN

      l_err_loc := 770;

Line: 2897

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_CANNOT_DEL_SEEDED_DESC',
          p_line_number);

Line: 2906

      IF (can_descriptor_be_deleted(x_descriptor_id) = 1) THEN
        l_err_loc := 800;

Line: 2908

        insert_failed_message(p_request_id, 'ICX_CAT_KEY', 'ICX_CAT_DESC_HAS_ITEMS',
          p_line_number);

Line: 2951

    DELETE FROM icx_por_batch_jobs
    WHERE submission_datetime <= (sysdate - l_number_of_days)
    AND rownum <= l_commit_size
    RETURNING job_number BULK COLLECT INTO l_job_number_tbl;

Line: 2966

      DELETE FROM icx_por_failed_line_messages
      WHERE job_number = l_job_number_tbl(i);

Line: 2972

      DELETE FROM icx_por_failed_lines
      WHERE job_number = l_job_number_tbl(i);

Line: 2978

      DELETE FROM icx_por_contract_references
      WHERE job_number = l_job_number_tbl(i);

Line: 2989

  l_job_number_tbl.DELETE;

Line: 2996

    DELETE FROM icx_cat_batch_jobs
    WHERE submission_datetime <= (sysdate - l_number_of_days)
    AND rownum <= l_commit_size
    RETURNING job_number BULK COLLECT INTO l_job_number_tbl;

Line: 3011

      DELETE FROM icx_por_failed_line_messages
      WHERE job_number = l_job_number_tbl(i);

Line: 3017

      DELETE FROM icx_por_failed_lines
      WHERE job_number = l_job_number_tbl(i);

Line: 3023

      DELETE FROM icx_cat_parse_errors
      WHERE request_id = l_job_number_tbl(i);

Line: 3060

    SELECT rt_category_id, category_name, language
    FROM icx_cat_categories_tl
    WHERE request_id = p_request_id
    AND rebuild_flag = 'Y';

Line: 3068

    SELECT key, searchable
    FROM icx_cat_attributes_tl
    WHERE request_id = p_request_id
    AND rebuild_flag = 'Y'
    AND rt_category_id = 0
    AND key IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
                'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
                'SHOPPING_CATEGORY');

Line: 3080

    SELECT distinct rt_category_id
    FROM icx_cat_attributes_tl
    WHERE request_id = p_request_id
    AND rebuild_flag = 'Y'
    AND key NOT IN ('SUPPLIER_PART_NUM', 'SUPPLIER_PART_AUXID', 'SUPPLIER',
                    'INTERNAL_ITEM_NUM', 'SOURCE', 'ITEM_REVISION',
                    'SHOPPING_CATEGORY');

Line: 3170

  UPDATE icx_cat_categories_tl
  SET rebuild_flag = null
  WHERE rebuild_flag is not null;

Line: 3177

  UPDATE icx_cat_attributes_tl
  SET rebuild_flag = null
  WHERE rebuild_flag is not null;

Line: 3219

  update_job_status(l_request_id, 'RUNNING');

Line: 3228

  update_job_status(l_request_id, 'COMPLETED');

Line: 3243

  update_job_status(l_request_id, 'ERROR');

Line: 3269

  update_job_status(l_request_id, 'RUNNING');

Line: 3278

  update_job_status(l_request_id, 'COMPLETED');

Line: 3293

  update_job_status(l_request_id, 'ERROR');

Line: 3298

PROCEDURE update_job_status
(
  p_job_number IN NUMBER,
  p_job_status IN VARCHAR2
)
IS
  l_err_loc PLS_INTEGER;

Line: 3310

  UPDATE icx_cat_batch_jobs
  SET job_status = p_job_status,
      last_updated_by = fnd_global.user_id,
      last_update_date = sysdate,
      last_update_login = fnd_global.login_id
  WHERE job_number = p_job_number;

Line: 3328

     'Exception at ICX_CAT_SCHEMA_UPLOAD_PVT.update_job_status(' ||
     l_err_loc || '), ' || SQLERRM);

Line: 3330

END update_job_status;