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.

[Home] [Help]

APPS.CN_COLLECTION_CUSTOM_GEN SQL Statements

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

Line: 38


PROCEDURE insert_cn_not_trx(
    x_table_map_id cn_table_maps.table_map_id%TYPE,
    x_event_id     cn_events.event_id%TYPE,
    code           IN OUT NOCOPY cn_utils.code_type,
    x_org_id       IN NUMBER)
IS
  l_return_status VARCHAR2(4000);

Line: 58


    SELECT tmov.object_id,
      LOWER(OBJ.name) object_name
    FROM cn_table_map_objects tmov,
      cn_objects obj
    WHERE tmov.table_map_id = x_table_map_id
    AND tmov.tm_object_type = 'PARAM'
    AND obj.object_id       = tmov.object_id
    AND tmov.org_id         = obj.org_id
    AND tmov.org_id         = x_org_id;

Line: 74


  SELECT *
  INTO l_table_map_rec
  FROM cn_table_maps_v
  WHERE table_map_id = x_table_map_id
  AND org_id         = l_org_id; -- Added For R12 MOAC Change

Line: 97


  cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserting new transactions into CN_NOT_TRX.'');');

Line: 98


  cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Inserting new transactions into CN_NOT_TRX.'');');

Line: 108


  cn_utils.appindcr(code, 'DECLARE -- Notification Insert Block');

Line: 127


    cn_utils.appindcr(code, 'SELECT object_value');

Line: 169


  cn_utils.appindcr(code, '-- Insert new lines into cn_not_trx');

Line: 173


    cn_utils.appindcr(code, 'INSERT /'||'*+ PARALLEL(cn_not_trx) */ INTO  cn_not_trx (');

Line: 175


    cn_utils.appindcr(code, 'INSERT INTO  cn_not_trx (');

Line: 193


    cn_utils.appindcr(code, 'SELECT '||l_parallel_hint);

Line: 195


    cn_utils.appindcr(code, 'SELECT ');

Line: 235


    cn_utils.appindcr(code, '    SELECT /'||'*'||'+ PARALLEL(cn_not_trx) '||'*/ 1');

Line: 237


    cn_utils.appindcr(code, '    SELECT 1');

Line: 244


  cn_utils.appindcr(code, 'END; -- Notification Insert Block');

Line: 246


  cn_utils.appindcr(code, '-- Commit the notification inserts - they are permanent even if collection fails');

Line: 263


END insert_cn_not_trx;

Line: 276


PROCEDURE insert_comm_lines_api_select(
    x_table_map_id  IN cn_table_maps_v.table_map_id%TYPE,
    code            IN OUT NOCOPY cn_utils.code_type,
    x_org_id        IN NUMBER,
    x_parallel_hint IN VARCHAR2)
IS -- Added For R12 MOAC Changes
  CURSOR api_direct_maps
  IS
    SELECT column_map_id,
      expression,
      cno.NAME dest_column
    FROM cn_column_maps ccmv,
      cn_objects cno
    WHERE ccmv.table_map_id        = x_table_map_id
    AND ccmv.expression           IS NOT NULL
    AND ccmv.calc_ext_table_id    IS NULL
    AND ccmv.update_clause        IS NULL
    AND ccmv.org_id                = x_org_id
    AND ccmv.destination_column_id = cno.object_id
    AND ccmv.org_id                = cno.org_id
      -- Added For R12 MOAC Changes
    ORDER BY dest_column;

Line: 302


      cn_utils.appindcr(code, 'INSERT /'||'*+ PARALLEL(cn_comm_lines_api) */ INTO  cn_comm_lines_api (');

Line: 304


      cn_utils.appindcr(code, 'INSERT INTO  cn_comm_lines_api (');

Line: 307


    cn_utils.appindcr(code, 'INSERT INTO  cn_comm_lines_api (');

Line: 326


    cn_utils.appindcr(code, 'SELECT '||x_parallel_hint);

Line: 328


    cn_utils.appindcr(code, 'SELECT ');

Line: 354


END insert_comm_lines_api_select;

Line: 365


PROCEDURE insert_comm_lines_api(
    x_table_map_id cn_table_maps.table_map_id%TYPE,
    x_event_id cn_events.event_id%TYPE,
    code     IN OUT NOCOPY cn_utils.code_type,
    x_org_id IN NUMBER)
IS
  l_return_status VARCHAR2(4000);

Line: 386


  SELECT *
  INTO l_table_map_rec
  FROM cn_table_maps_v
  WHERE table_map_id = x_table_map_id
  AND org_id         = l_org_id;

Line: 417


  cn_utils.appendcr(code, '--******** INSERT CN_COMM_LINES_API *********-- ');

Line: 418


  cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserting into CN_COMM_LINES_API.'');');

Line: 419


  cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''Inserting into CN_COMM_LINES_API.'');');

Line: 426


  cn_utils.appindcr(code, '-- Insert new lines into CN_COMM_LINES_API');

Line: 430


  insert_comm_lines_api_select(x_table_map_id, code, X_org_id, l_parallel_hint);

Line: 463


  cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserted '' || comm_lines_api_count || '' line records into CN_COMM_LINES_API.'');');

Line: 464


  cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Inserted '' || comm_lines_api_count || '' line records into CN_COMM_LINES_API.'');');

Line: 472


    cn_utils.appindcr(code, 'UPDATE /'||'*+ PARALLEL(cnt) */ cn_not_trx cnt');

Line: 474


    cn_utils.appindcr(code, 'UPDATE cn_not_trx cnt');

Line: 482


  cn_utils.appindcr(code, 'cn_message_pkg.debug(''Updated collected_flag in cn_not_trx.'');');

Line: 490


  cn_debug.print_msg('insert_lines: in exception handler for NO_DATA_FOUND',1);

Line: 491


  fnd_file.put_line(fnd_file.Log, 'insert_lines: in exception handler for NO_DATA_FOUND');

Line: 493


END insert_comm_lines_api;

Line: 504


PROCEDURE update_comm_lines_api(
    x_table_map_id cn_table_maps.table_map_id%TYPE,
    code           IN OUT NOCOPY cn_utils.code_type,
    x_org_id       IN NUMBER,
    x_event_id     IN NUMBER DEFAULT NULL,
    x_generic      IN BOOLEAN DEFAULT NULL)
IS
  x_dest_table_name cn_obj_tables_v.name%TYPE;

Line: 517


    SELECT cm.expression,
      cm.calc_ext_table_id,
      cm.update_clause,
      ext.name relationship_name,
      LOWER(obj.NAME) external_table_name,
      LOWER(NVL(ext.alias,obj.NAME)) external_table_alias,
      LOWER(destcol.name) dest_column
    FROM cn_column_maps cm,
      cn_obj_columns_v destcol,
      cn_calc_ext_tables ext,
      cn_objects obj
    WHERE cm.table_map_id        = x_table_map_id
    AND cm.expression           IS NOT NULL
    AND (cm.calc_ext_table_id   IS NOT NULL
    OR cm.update_clause         IS NOT NULL)
    AND ext.calc_ext_table_id(+) = cm.calc_ext_table_id
    AND obj.object_id(+)         = ext.external_table_id
    AND destcol.column_id        = cm.destination_column_id
      -- make sure no old pre 11iv2 mappings to CN_TRX etc. can slip in
    AND destcol.table_id = -1008 --CN_COMM_LINES_API
    AND cm.org_id        = x_org_id
    AND destcol.org_id   = cm.org_id
    AND ext.org_id(+)    = cm.org_id
    AND obj.org_id(+)    = ext.org_id
    ORDER BY destcol.name;

Line: 546


  cn_utils.appendcr(code, '--******** UPDATE CN_COMM_LINES_API ********-- ');

Line: 547


  cn_utils.appindcr(code, '--*** Update columns populated by all INDIRECT mappings');

Line: 561


  SELECT LOWER(cB.name),
    LOWER(NVL(cB.alias , cB.name))
  INTO x_dest_table_name,
    x_dest_alias
  FROM cn_table_maps ctm,
    CN_OBJECTS CB
  WHERE ctm.table_map_id = x_table_map_id
  AND CB.object_id       = CTM.destination_table_id
  AND cb.org_id          = ctm.org_id
  AND cb.org_id          =x_org_id;

Line: 589


    cn_utils.appindcr(code, 'UPDATE ' || x_dest_table_name || ' ' || x_dest_alias);

Line: 595


      cn_utils.appindcr(code, '  SELECT ');

Line: 609


        (SELECT LOWER(pkcol.name) pkcolumn_name,
          LOWER(fkcol.name) fkcolumn_name
        FROM cn_calc_ext_tbl_dtls dtls,
          cn_obj_columns_v pkcol,
          cn_obj_columns_v fkcol
        WHERE dtls.calc_ext_table_id = indmap_rec.calc_ext_table_id
        AND pkcol.column_id          = dtls.external_column_id
        AND fkcol.column_id          = dtls.internal_column_id
        AND dtls.org_id              = x_org_id
        AND pkcol.org_id             = dtls.org_id
        AND fkcol.org_id             = pkcol.org_id
        )
        LOOP
          IF x_count = 0 THEN
            x_count := 1;

Line: 636


        cn_utils.appindcr(code, '  --*** FROM/WHERE taken from Update Clause');

Line: 637


        cn_utils.appindcr(code, '    '||indmap_rec.update_clause||'),');

Line: 659


  cn_utils.appindcr(code, 'cn_message_pkg.debug(''For all INDIRECT mappings updated '' || SQL%ROWCOUNT || '' rows in cn_comm_lines_api.'');');

Line: 660


  cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''For all INDIRECT mappings updated '' || SQL%ROWCOUNT || '' rows in cn_comm_lines_api.'');');

Line: 665


  cn_debug.print_msg('update_lines: in exception handler for NO_DATA_FOUND', 1);

Line: 666


  fnd_file.put_line(fnd_file.Log, 'update_lines: in exception handler for NO_DATA_FOUND');

Line: 668


END update_comm_lines_api;

Line: 684


  l_delete_flag VARCHAR2(1);

Line: 688


    SELECT OBJ.object_value
    FROM cn_table_map_objects tmov,
      cn_objects obj
    WHERE tmov.table_map_id = x_table_map_id
    AND tmov.tm_object_type = 'FILTER'
    AND tmov.object_id      = obj.object_id
    AND tmov.org_id         = obj.org_id
    AND tmov.org_id         = x_org_id;

Line: 700


  SELECT delete_flag
  INTO l_delete_flag
  FROM cn_table_maps
  WHERE table_map_id = x_table_map_id
  AND org_id         = x_org_id;

Line: 705


  IF l_delete_flag   = 'Y' THEN
    l_statement     := 'DELETE FROM cn_comm_lines_api api';

Line: 708


    l_statement := 'UPDATE cn_comm_lines_api api SET load_status = ''FILTERED''';

Line: 755


    SELECT meaning
    FROM cn_lookups
    WHERE lookup_type ='CN_COLL_TRIGGER_EVENTS'
    AND enabled_flag  ='Y'
    AND lookup_code   = 'PRN';

Line: 776


  (SELECT OBJ.object_value
  FROM cn_table_map_objects tmov,
    cn_objects obj
  WHERE tmov.table_map_id = p_table_map_id
  AND obj.object_id       = tmov.object_id
  AND tmov.org_id         = obj.org_id
  AND tmov.tm_object_type = 'USERCODE'
  AND UPPER(OBJ.name)     = UPPER(l_location_name)
  AND tmov.org_id         = x_org_id
  ORDER BY table_map_object_id
  )
  LOOP
    --+
    -- Add a terminal ';' if none was registered