eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ICX_POR_DELETE_CATALOG SQL Statements

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

Line: 21


 ** Proc : delete_items_in_category
 ** Procedure called when a category is deleted from ecmanager.
 ** Desc : Deletes the items and prices in the category id specified
 **        Deletes the category related info from icx_cat_browse_trees,
 **        icx_por_category_data_sources, icx_por_category_order_map
 **/
PROCEDURE delete_items_in_category (
                                    errbuf            OUT NOCOPY VARCHAR2,
                                    retcode           OUT NOCOPY VARCHAR2,
                                    p_rt_category_id   IN NUMBER,
                                    p_category_key     IN VARCHAR2)
IS
BEGIN
   retcode := 0;

Line: 37


   delete_items_in_category(p_rt_category_id, p_category_key);

Line: 44


END delete_items_in_category;

Line: 47


 ** Proc : delete_items_in_category
 ** Procedure called when a category is deleted from ecmanager.
 ** Desc : Deletes the items and prices in the category id specified
 **        Deletes the category related info from icx_cat_browse_trees,
 **        icx_por_category_data_sources, icx_por_category_order_map
 **/
PROCEDURE delete_items_in_category (p_rt_category_id        IN NUMBER,
                                    p_category_key          IN VARCHAR2)
IS
  --TYPE CursorType       IS REF CURSOR;

Line: 60


   SELECT rt_item_id
   FROM   icx_cat_category_items
   WHERE  rt_category_id = c_rt_category_id;

Line: 92


    deleteCommonTables(l_rt_item_ids, CATITEM_TABLE_LAST);

Line: 94


    l_rt_item_ids.DELETE;

Line: 101


        'ICX_POR_DELETE_CATALOG.delete_items_in_category(' || xErrLoc|| ')'||     ', sqlerrm:' ||sqlerrm ||
        '; so close the cursor and reopen the cursor-');

Line: 115


  deleteCategoryRelatedInfo(p_rt_category_id, p_category_key);

Line: 126


      'ICX_POR_DELETE_CATALOG.delete_items_in_category(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 129


END delete_items_in_category;

Line: 132


 ** Proc : delete_items_in_catalog
 ** Desc : Deletes the items and prices in the catalog name specified
 **/
PROCEDURE delete_items_in_catalog (p_catalog_name        IN VARCHAR2)
IS
  --TYPE CursorType       IS REF CURSOR;

Line: 141


    SELECT rt_item_id
    FROM   icx_cat_items_b
    WHERE  catalog_name = c_catalog_name
    AND    extractor_updated_flag = 'N';

Line: 174


    deleteCommonTables(l_rt_item_ids);

Line: 176


    l_rt_item_ids.DELETE;

Line: 183


      'ICX_POR_DELETE_CATALOG.delete_items_in_catalog(' || xErrLoc|| ')'||
      ', sqlerrm:' ||sqlerrm ||
      '; so close the cursor and reopen the cursor-');

Line: 205


      'ICX_POR_DELETE_CATALOG.delete_items_in_catalog(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 208


END delete_items_in_catalog;

Line: 211


 ** Proc : delete_supplier_catalog_opUnit
 ** Desc : Deletes the catalog for the supplier and Operating Unit specified.
 **/
PROCEDURE delete_supplier_catalog_opUnit (p_supplier IN VARCHAR2,
                                          p_operating_unit_id IN NUMBER DEFAULT -2)
IS
  --TYPE CursorType	IS REF CURSOR;

Line: 222


    SELECT rt_item_id
    FROM   icx_cat_items_b
    WHERE  supplier = c_supplier
    AND    org_id = c_operating_unit_id
    AND    extractor_updated_flag = 'N';

Line: 256


    deleteCommonTables(l_rt_item_ids);

Line: 258


    l_rt_item_ids.DELETE;

Line: 265


       'ICX_POR_DELETE_CATALOG.delete_supplier_catalog_opUnit(' || xErrLoc|| ')'||     ', sqlerrm:' ||sqlerrm ||
       '; so close the cursor and reopen the cursor-');

Line: 285


      'ICX_POR_DELETE_CATALOG.delete_supplier_catalog_opUnit(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 288


END delete_supplier_catalog_opUnit;

Line: 292


 ** Desc : Deletes the data from ICX_CAT_PRICE_HISTORY
 **/
PROCEDURE delPriceHistory(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 300


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_PRICE_HISTORY');

Line: 304


      DELETE FROM icx_cat_price_history
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize ;

Line: 316


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delPriceHistory-'||
      xErrLoc||' '||SQLERRM);

Line: 321


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delPriceHistory(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 328


 ** Desc : Deletes the data from ICX_CAT_ITEMS_TLP
 **/
PROCEDURE delItemsTLP(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 336


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEMS_TLP');

Line: 340


      DELETE FROM icx_cat_items_tlp
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize ;

Line: 352


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemsTLP-'||
      xErrLoc||' '||SQLERRM);

Line: 357


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemsTLP(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 364


 ** Desc : Deletes the data from ICX_CAT_EXT_ITEMS_TLP
 **/
PROCEDURE delExtItemsTLP(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 372


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_EXT_ITEMS_TLP');

Line: 376


      DELETE FROM icx_cat_ext_items_tlp
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize ;

Line: 388


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delExtItemsTLP-'||
      xErrLoc||' '||SQLERRM);

Line: 393


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delExtItemsTLP(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 400


 ** Desc : Deletes the data from ICX_CAT_ITEMS_CTX_TLP
 **/
PROCEDURE delItemsCtxTLP(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 408


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEMS_CTX_TLP');

Line: 412


      DELETE FROM icx_cat_items_ctx_tlp
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize ;

Line: 424


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemsCtxTLP-'||
      xErrLoc||' '||SQLERRM);

Line: 429


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemsCtxTLP(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 436


 ** Desc : Deletes the data from POR_FAVORITE_LIST_LINES
 **/
PROCEDURE delFavoriteList(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 444


  ICX_POR_EXT_UTL.debug('Delete from POR_FAVORITE_LIST_LINES');

Line: 448


      DELETE FROM por_favorite_list_lines
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize ;

Line: 460


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delFavoriteList-'||
      xErrLoc||' '||SQLERRM);

Line: 465


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delFavoriteList(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 472


 ** Desc : Deletes the data from ICX_CAT_CATEGORY_ITEMS
 **/
PROCEDURE delCategoryItems(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 480


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_CATEGORY_ITEMS');

Line: 484


      DELETE FROM icx_cat_category_items
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize ;

Line: 496


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delCategoryItems-'||
      xErrLoc||' '||SQLERRM);

Line: 501


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delCategoryItems(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 508


 ** Desc : Deletes the data from ICX_CAT_PRICE_LISTS
 **/
PROCEDURE delPriceLists(pPriceListIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 514


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_PRICE_LISTS');

Line: 517


    DELETE FROM icx_cat_price_lists pl
    WHERE  pl.price_list_id = pPriceListIds(i)
    AND    NOT EXISTS (SELECT 'price line'
                       FROM   icx_cat_item_prices p
                       WHERE  p.price_list_id = pl.price_list_id);

Line: 526


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delPriceLists-'||
      xErrLoc||' '||SQLERRM);

Line: 530


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delPriceLists(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 537


 ** Desc : Deletes the data from ICX_CAT_ITEM_PRICES
 **/
PROCEDURE delItemPrices(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 546


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEM_PRICES');

Line: 549


    xPriceListIds.DELETE;

Line: 553


      DELETE FROM icx_cat_item_prices
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize
      RETURNING price_list_id BULK COLLECT INTO xPriceListIds;

Line: 569


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemPrices-'||
      xErrLoc||' '||SQLERRM);

Line: 574


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemPrices(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 581


 ** Desc : Deletes the data from ICX_CAT_ITEMS_B
 **/
PROCEDURE delItemsB(pRtItemIds	IN DBMS_SQL.NUMBER_TABLE)
IS
  xErrLoc	PLS_INTEGER := 0;

Line: 589


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_ITEMS_B');

Line: 593


      DELETE FROM icx_cat_items_b
      WHERE  rt_item_id = pRtItemIds(i)
      AND    rownum <= gCommitSize;

Line: 605


    ICX_POR_EXT_UTL.pushError('ICX_POR_DELETE_CATALOG.delItemsB-'||
      xErrLoc||' '||SQLERRM);

Line: 610


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.delItemsB(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 616


 ** Proc : deleteCommonTables
 ** Desc : Deletes the data from common tables used by Extractor and DeleteCatalog
 **/
PROCEDURE deleteCommonTables(pRtItemIds 	IN dbms_sql.number_table,
                             pDeleteOrder 	IN PLS_INTEGER DEFAULT ITEM_TABLE_LAST)
IS
  xErrLoc PLS_INTEGER := 0;

Line: 642


  IF (pDeleteOrder = ITEM_TABLE_LAST) THEN
    xErrLoc := 200;

Line: 649


  ELSIF (pDeleteOrder = CATITEM_TABLE_LAST) THEN
    xErrLoc := 300;

Line: 665


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.deleteCommonTables(' ||
      xErrLoc||'): '||sqlerrm;

Line: 668


END deleteCommonTables;

Line: 671


 ** Proc : deleteCategoryRelatedInfo
 ** Desc : Deletes the data from ICX_CAT_BROWSE_TREES,
 **        ICX_POR_CATEGORY_ORDER_MAP, ICX_POR_CATEGORY_DATA_SOURCES
 **/
PROCEDURE deleteCategoryRelatedInfo(pRtCategoryId  IN NUMBER,
                                    pCategoryKey   IN VARCHAR2)
IS
  xErrLoc       PLS_INTEGER := 0;

Line: 682


  ICX_POR_EXT_UTL.debug('Delete from ICX_CAT_BROWSE_TREES');

Line: 685


  DELETE FROM icx_cat_browse_trees
  WHERE  child_category_id = pRtCategoryId;

Line: 688


  ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_ORDER_MAP');

Line: 691


  DELETE FROM icx_por_category_order_map
  WHERE  rt_category_id = pRtCategoryId;

Line: 694


  ICX_POR_EXT_UTL.debug('Delete from ICX_POR_CATEGORY_DATA_SOURCES');

Line: 697


  DELETE FROM icx_por_category_data_sources
  WHERE  category_key = pCategoryKey;

Line: 702


    gReturnError := gReturnError || 'ICX_POR_DELETE_CATALOG.deleteCategoryRelatedInfo(' ||
      xErrLoc|| '): '||sqlerrm;

Line: 705


END deleteCategoryRelatedInfo;