APPS.ICX_POR_EXT_PURGE SQL Statements

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

Line: 41

    gRtCategoryIds.DELETE;

Line: 42

    gCategoryKeys.DELETE;

Line: 43

    gCategoryTypes.DELETE;

Line: 47

    gRtItemIds.DELETE;

Line: 79

PROCEDURE deleteCategories IS

  CURSOR cCatItems(p_rt_category_id	IN NUMBER) IS
    SELECT rt_item_id
    FROM   icx_cat_category_items
    WHERE  rt_category_id = p_rt_category_id;

Line: 104

    ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_DATA_SOURCES');

Line: 107

    DELETE FROM icx_por_category_data_sources
    WHERE  category_key = gCategoryKeys(i)
    AND    external_source = 'Oracle';

Line: 115

    ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_ORDER_MAP');

Line: 118

    DELETE FROM icx_por_category_order_map
    WHERE  rt_category_id = gRtCategoryIds(i)
    AND    external_source = 'Oracle';

Line: 126

    ICX_POR_EXT_UTL.debug('Delete from ICX_POR_TABLE_OF_CONTENTS_TL');

Line: 129

    DELETE FROM icx_por_table_of_contents_tl
    WHERE child = gRtCategoryIds(i);

Line: 142

      xRtItemIds.DELETE;

Line: 155

        ICX_POR_DELETE_CATALOG.setCommitSize(ICX_POR_EXT_UTL.gCommitSize);

Line: 157

        ICX_POR_DELETE_CATALOG.deleteCommonTables(xRtItemIds,
          ICX_POR_DELETE_CATALOG.CATITEM_TABLE_LAST);

Line: 164

          ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_CATEGORY_ITEMS');

Line: 167

          DELETE FROM icx_cat_category_items
          WHERE  rt_item_id = xRtItemIds(j)
          AND    rt_category_id = gRtCategoryIds(i);

Line: 185

    ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_CATEGORIES_TL');

Line: 188

    DELETE FROM icx_cat_categories_tl
    WHERE  rt_category_id = gRtCategoryIds(i);

Line: 198

    ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.deleteCategories-'||
      xErrLoc||' '||SQLERRM);

Line: 209

END deleteCategories;

Line: 212

procedure deleteItems IS

  xErrLoc       PLS_INTEGER := 100;

Line: 229

  ICX_POR_DELETE_CATALOG.setCommitSize(ICX_POR_EXT_UTL.gCommitSize);

Line: 232

  ICX_POR_DELETE_CATALOG.deleteCommonTables(gRtItemIds,
    ICX_POR_DELETE_CATALOG.ITEM_TABLE_LAST);

Line: 241

    ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_PURGE.deleteItems-'||
      xErrLoc||' '||SQLERRM);

Line: 245

end deleteItems;

Line: 266

    deleteCategories;

Line: 280

    deleteItems;

Line: 314

    SELECT category_set_id,
           validate_flag,
           structure_id
    INTO   xCategorySetId,
           xValidateFlag,
           xStructureId
    FROM   mtl_default_sets_view
    WHERE  functional_area_id = 2;

Line: 330

      'SELECT cat.rt_category_id, cat.key, cat.type ' ||
      'FROM   icx_cat_categories_tl cat ' ||
      'WHERE  title = ''Oracle'' ' ||
      'AND    language = ''' || ICX_POR_EXTRACTOR.gBaseLang || ''' ' ||
      'AND    ((type = '||ICX_POR_EXT_CLASS.TEMPLATE_HEADER_TYPE||' AND '||
      '         NOT EXISTS (SELECT ''active template header'' ';

Line: 349

      '      NOT EXISTS (SELECT ''active oracle category'' ';

Line: 388

        'AND cat.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID;

Line: 408

      SELECT cat.rt_category_id, cat.key, cat.type
      FROM   icx_cat_categories_tl cat
      WHERE  cat.title = 'Oracle'
      AND    cat.language = ICX_POR_EXTRACTOR.gBaseLang
      AND    NOT EXISTS (SELECT 'Bulkloaded items'
                         FROM   icx_cat_items_b i,
                                icx_cat_category_items ci
                         WHERE  ci.rt_category_id = cat.rt_category_id
                         AND    ci.rt_item_id = i.rt_item_id
                         AND    NVL(i.extractor_updated_flag, 'N') = 'N');

Line: 421

      SELECT cat.rt_category_id, cat.key, cat.type
      FROM   icx_cat_categories_tl cat
      WHERE  cat.title = 'Oracle'
      AND    cat.language = ICX_POR_EXTRACTOR.gBaseLang
      AND    key = to_char(pInvCatId)
      AND    NOT EXISTS (SELECT 'Bulkloaded items'
                         FROM   icx_cat_items_b i,
                                icx_cat_category_items ci
                         WHERE  ci.rt_category_id = cat.rt_category_id
                         AND    ci.rt_item_id = i.rt_item_id
                         AND    NVL(i.extractor_updated_flag, 'N') = 'N');

Line: 476

    xRtCategoryIds.DELETE;

Line: 477

    xKeys.DELETE;

Line: 478

    xTypes.DELETE;

Line: 524

      'Total deleted categories: ' || gCompletedCount);

Line: 590

    IF (pType = 'DELETED_ITEMS') THEN
      xErrLoc := 240;

Line: 593

        'SELECT i.rt_item_id ' ||
        'FROM   icx_cat_items_b i ' ||
        'WHERE  i.extractor_updated_flag = ''Y'' ' ||
        'AND    i.internal_item_id is not null ';

Line: 600

          'AND i.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID||' ';

Line: 604

        'AND NOT EXISTS (SELECT ''item deleted from item master'' ';

Line: 622

        'SELECT i.rt_item_id ' ||
        'FROM   icx_cat_items_b i, ' ||
         'icx_cat_item_prices p1 ' ||
        'WHERE  i.extractor_updated_flag = ''Y'' ' ||
        'AND    i.internal_item_id is not null ';

Line: 630

          'AND i.last_updated_by = '||ICX_POR_EXT_TEST.TEST_USER_ID||' ';

Line: 637

        'AND NOT EXISTS (SELECT ''Invalid item category association'' ';

Line: 668

      SELECT i.rt_item_id
      FROM   icx_cat_items_b i
      WHERE  i.extractor_updated_flag = 'Y';

Line: 675

      SELECT distinct i.rt_item_id
      FROM   icx_cat_items_b i,
             icx_cat_item_prices p
      WHERE  p.mtl_category_id = pInvCatItemId
      AND    p.rt_item_id = i.rt_item_id
      AND    i.extractor_updated_flag = 'Y';

Line: 685

      SELECT i.rt_item_id
      FROM   icx_cat_items_b i
      WHERE  i.internal_item_id = pInvCatItemId
      AND    i.extractor_updated_flag = 'Y';

Line: 717

    xRtItemIds.DELETE;

Line: 760

      'Total deleted items: ' || gCompletedCount);

Line: 794

    openItemCursor(pMode, pInvCatItemId, 'DELETED_ITEMS', cItem);

Line: 797

      'Purge items deleted from item masters');