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_EXT_CLASS SQL Statements

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

Line: 36


gUpdateRtCategoryIds	DBMS_SQL.NUMBER_TABLE;

Line: 37


gUpdateCategoryKeys	DBMS_SQL.VARCHAR2_TABLE;

Line: 38


gUpdateCategoryNames	DBMS_SQL.VARCHAR2_TABLE;

Line: 39


gUpdateLanguages	DBMS_SQL.VARCHAR2_TABLE;

Line: 40


gUpdateSourceLangs	DBMS_SQL.VARCHAR2_TABLE;

Line: 49


    gAddRtCategoryIds.DELETE;

Line: 50


    gAddCategoryKeys.DELETE;

Line: 51


    gAddCategoryNames.DELETE;

Line: 52


    gAddLanguages.DELETE;

Line: 53


    gAddSourceLangs.DELETE;

Line: 54


    gAddNewRtCategoryIds.DELETE;

Line: 55


    gAddNewCategoryKeys.DELETE;

Line: 58


  IF (pMode IN ('ALL', 'UPDATE')) THEN
    gUpdateRtCategoryIds.DELETE;

Line: 60


    gUpdateCategoryKeys.DELETE;

Line: 61


    gUpdateCategoryNames.DELETE;

Line: 62


    gUpdateLanguages.DELETE;

Line: 63


    gUpdateSourceLangs.DELETE;

Line: 91


  ELSIF (pMode = 'UPDATE') THEN
    xShot := xShot || ' gUpdateRtCategoryId: ' ||
      ICX_POR_EXT_UTL.getTableElement(gUpdateRtCategoryIds, pIndex) || ',';

Line: 94


    xShot := xShot || ' gUpdateCategoryKey: ' ||
      ICX_POR_EXT_UTL.getTableElement(gUpdateCategoryKeys, pIndex) || ',';

Line: 96


    xShot := xShot || ' gUpdateCategoryName: ' ||
      ICX_POR_EXT_UTL.getTableElement(gUpdateCategoryNames, pIndex) || ',';

Line: 98


    xShot := xShot || ' gUpdateLanguage: ' ||
      ICX_POR_EXT_UTL.getTableElement(gUpdateLanguages, pIndex) || ',';

Line: 100


    xShot := xShot || ' gUpdateSourceLang: ' ||
      ICX_POR_EXT_UTL.getTableElement(gUpdateSourceLangs, pIndex);

Line: 118


    SELECT rowid
    FROM   icx_cat_items_tlp
    WHERE  primary_category_id = p_rt_category_id
    AND    language = p_language;

Line: 140


    ICX_POR_EXT_UTL.debug('Insert icx_cat_categories_tl');

Line: 144


    INSERT INTO icx_cat_categories_tl(
      rt_category_id, category_name, key, title, type, language,
      source_lang, upper_category_name, upper_key, section_map,
      created_by, creation_date, last_updated_by, last_update_date,
      last_update_login, request_id, program_application_id,
      program_id, program_update_date)
    VALUES(gAddRtCategoryIds(i),gAddCategoryNames(i),
           gAddCategoryKeys(i), 'Oracle', pType,
           gAddLanguages(i), gAddSourceLangs(i),
           upper(gAddCategoryNames(i)), upper(gAddCategoryKeys(i)),
           rpad('0', 300, 0),
           ICX_POR_EXTRACTOR.gUserId, SYSDATE,
           ICX_POR_EXTRACTOR.gUserId, SYSDATE,
           ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gRequestId,
           ICX_POR_EXTRACTOR.gProgramApplicationId,
           ICX_POR_EXTRACTOR.gProgramId, SYSDATE);

Line: 172


      INSERT INTO icx_cat_categories_tl(
        rt_category_id, category_name, key, title, type, language,
        source_lang, upper_category_name, upper_key, section_map,
        created_by, creation_date, last_updated_by, last_update_date,
        last_update_login, request_id, program_application_id,
        program_id, program_update_date)
      SELECT gAddRtCategoryIds(i), gAddCategoryNames(i),
             gAddCategoryKeys(i), 'Oracle', TEMPLATE_HEADER_TYPE,
             language_code, gAddLanguages(i),
             upper(gAddCategoryNames(i)), upper(gAddCategoryKeys(i)),
             rpad('0', 300, 0),
             ICX_POR_EXTRACTOR.gUserId, SYSDATE,
             ICX_POR_EXTRACTOR.gUserId, SYSDATE,
             ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gRequestId,
             ICX_POR_EXTRACTOR.gProgramApplicationId,
             ICX_POR_EXTRACTOR.gProgramId, SYSDATE
        FROM fnd_languages
       WHERE installed_flag = 'I';

Line: 206


   INSERT INTO icx_por_category_data_sources (
   -- Bug: 3291430 - Also populate rt_category_id with value from icx_cat_categories_tl
         rt_category_id,
         category_key,
         external_source, external_source_key,
         created_by, creation_date, last_updated_by, last_update_date,
         last_update_login, request_id, program_application_id,
         program_id, program_update_date)
     SELECT rt_category_id, key,'Oracle', key,
            ICX_POR_EXTRACTOR.gUserId, SYSDATE,
            ICX_POR_EXTRACTOR.gUserId, SYSDATE,
            ICX_POR_EXTRACTOR.gLoginId, ICX_POR_EXTRACTOR.gRequestId,
            ICX_POR_EXTRACTOR.gProgramApplicationId,
            ICX_POR_EXTRACTOR.gProgramId, SYSDATE
     FROM   icx_cat_categories_tl
     WHERE
            rt_category_id = gAddRtCategoryIds(i) and
            language = gAddLanguages(i) and
            not exists (select 1
                        from   icx_por_category_data_sources
                        where  external_source = 'Oracle'
                           and external_source_key = key);

Line: 237


      INSERT INTO icx_por_category_order_map (
        rt_category_id, external_source, external_source_key,
        created_by, creation_date, last_updated_by,
        last_update_date, last_update_login)
      VALUES(gAddNewRtCategoryIds(i), 'Oracle', gAddNewCategoryKeys(i),
             ICX_POR_EXTRACTOR.gUserId, SYSDATE,
             ICX_POR_EXTRACTOR.gUserId, SYSDATE,
             ICX_POR_EXTRACTOR.gLoginId);

Line: 264


PROCEDURE updateCategories(pType	IN PLS_INTEGER) IS
  xErrLoc	PLS_INTEGER := 100;

Line: 266


  xMode		VARCHAR2(20) := 'UPDATE';

Line: 270


    SELECT rowid
    FROM   icx_cat_items_tlp
    WHERE  primary_category_id = p_rt_category_id
    AND    language = p_language;

Line: 280


    FOR i in 1..gUpdateRtCategoryIds.COUNT LOOP
      ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DETIL_LEVEL, snapShot(i, xMode));

Line: 296


    FOR i IN 1..gUpdateRtCategoryIds.COUNT LOOP

      OPEN cRebuildItems(gUpdateRtCategoryIds(i), gUpdateLanguages(i));

Line: 302


        xRowIds.DELETE;

Line: 312


          UPDATE icx_cat_items_tlp
             SET primary_category_name = gUpdateCategoryNames(i),
                 last_updated_by = ICX_POR_EXTRACTOR.gUserId,
		 last_update_date = SYSDATE,
		 last_update_login = ICX_POR_EXTRACTOR.gLoginId,
		 request_id = ICX_POR_EXTRACTOR.gRequestId,
		 program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
		 program_id = ICX_POR_EXTRACTOR.gProgramId,
		 program_update_date = SYSDATE
           WHERE rowid = xRowIds(j);

Line: 335


    ICX_POR_EXT_UTL.debug('Update ICX_CAT_CATEGORIES_TL');

Line: 338


  FORALL i IN 1..gUpdateRtCategoryIds.COUNT
    UPDATE icx_cat_categories_tl
      SET  category_name = gUpdateCategoryNames(i),
           upper_category_name = upper(gUpdateCategoryNames(i)),
           source_lang = gUpdateSourceLangs(i),
           last_updated_by = ICX_POR_EXTRACTOR.gUserId,
	   last_update_date = SYSDATE,
	   last_update_login = ICX_POR_EXTRACTOR.gLoginId,
	   request_id = ICX_POR_EXTRACTOR.gRequestId,
	   program_application_id = ICX_POR_EXTRACTOR.gProgramApplicationId,
	   program_id = ICX_POR_EXTRACTOR.gProgramId,
	   program_update_date = SYSDATE
     WHERE rt_category_id = gUpdateRtCategoryIds(i)
       AND language = gUpdateLanguages(i);

Line: 364


    ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_CLASS.updateCategories-'||
      xErrLoc||' '||SQLERRM);

Line: 372


END updateCategories;

Line: 398


        'Process batch category extract -- Pending[Insert: ' ||
        gAddRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');

Line: 402


        'Process batch template header extract -- Pending[Insert: ' ||
        gAddRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');

Line: 411


      gUpdateRtCategoryIds.COUNT >= ICX_POR_EXT_UTL.gCommitSize)
  THEN
    xErrLoc := 300;

Line: 416


        'Process batch category extract -- Pending[Update: ' ||
        gUpdateRtCategoryIds.COUNT || '], Completed[' ||
        gCompletedCount || ']');

Line: 421


        'Process batch template header extract -- Pending[Update: ' ||
        gUpdateRtCategoryIds.COUNT || '], Completed[' || gCompletedCount || ']');

Line: 425


    gCompletedCount := gCompletedCount + gUpdateRtCategoryIds.COUNT;

Line: 426


    updateCategories(pType);

Line: 467


    select category_set_id,
           validate_flag,
           structure_id
    into   xCategorySetId,
           xValidateFlag,
           xStructureId
    from   mtl_default_sets_view
    where  functional_area_id = 2;

Line: 486


      'select distinct mck.category_id category_id, ' ||
      ' nvl(mctl.description, mck.concatenated_segments) category_name, ' ||
      ' mctl.language language, ' ||
      ' mctl.source_lang source_lang, ' ||
      ' icat.rt_category_id rt_category_id, ' ||
      ' icat2.category_name old_category_name ';

Line: 523


      'and GREATEST(mck.last_update_date, mctl.last_update_date';

Line: 525


      xString := xString || ', mcsvc.last_update_date';

Line: 528


      ') > NVL(:last_run_date, mck.last_update_date-1) ' ||
      'and mctl.category_id = mck.category_id ' ||
      'and mctl.language in (select language_code ' ||
      ' from fnd_languages ' ||
      ' where installed_flag in (''B'', ''I'')) ' ||
      'and to_char(mctl.category_id) = icat.key (+) ' ||
      'and to_char(mctl.category_id) = icat2.key (+) ' ||
      'and mctl.language = icat2.language (+) ';

Line: 576


      'select distinct to_number(NULL) category_id, ' ||
      ' templates.express_name category_name, ' ||
      ' to_char(NULL) language, ' ||
      ' to_char(NULL) source_lang, ' ||
      ' icat.rt_category_id rt_category_id, ' ||
      ' icat.category_name old_category_name ';

Line: 596


      'where templates.last_update_date > NVL(:last_run_date, ' ||
      ' templates.last_update_date-1) ' ||
      'and NVL(templates.inactive_date, sysdate+1) > sysdate ' ||
      'and exists (select -1 ';

Line: 676


    xCategoryIds.DELETE;

Line: 677


    xCategoryNames.DELETE;

Line: 678


    xLanguages.DELETE;

Line: 679


    xSourceLangs.DELETE;

Line: 680


    xRtCategoryIds.DELETE;

Line: 681


    xOldCategoryNames.DELETE;

Line: 732


            select icx_por_categoryid.nextval
              into xRtCategoryId
              from dual;

Line: 749


          select icx_por_categoryid.nextval
            into xRtCategoryId
            from dual;

Line: 793


            xPendingCount := gUpdateCategoryKeys.COUNT + 1;

Line: 794


            gUpdateCategoryKeys(xPendingCount) := xCategoryNames(i)||'_tmpl';

Line: 795


            gUpdateCategoryNames(xPendingCount) := xCategoryNames(i);

Line: 796


            gUpdateRtCategoryIds(xPendingCount) := xRtCategoryIds(i);

Line: 798


              gUpdateLanguages(xPendingCount) := xLanguages(i);

Line: 799


              gUpdateSourceLangs(xPendingCount) := xSourceLangs(i);

Line: 801


              gUpdateLanguages(xPendingCount) := ICX_POR_EXTRACTOR.gBaseLang;

Line: 802


              gUpdateSourceLangs(xPendingCount) := ICX_POR_EXTRACTOR.gBaseLang;