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.CN_COLLECTION_CUSTOM_GEN SQL Statements

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

Line: 58


  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: 79


      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: 98


    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: 126


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

Line: 127


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

Line: 137


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

Line: 156


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

Line: 207


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

Line: 211


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

Line: 213


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

Line: 231


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

Line: 233


    cn_utils.appindcr(code, 'SELECT ');

Line: 277


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

Line: 279


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

Line: 287


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

Line: 290


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

Line: 312


  END insert_cn_not_trx;

Line: 326


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: 352


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

Line: 354


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

Line: 357


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

Line: 380


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

Line: 382


    cn_utils.appindcr(code, 'SELECT ');

Line: 414


END insert_comm_lines_api_select;

Line: 426


  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: 453


    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: 488


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

Line: 489


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

Line: 490


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

Line: 497


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

Line: 502


    insert_comm_lines_api_select(x_table_map_id, code, X_org_id, l_parallel_hint);

Line: 544


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

Line: 545


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

Line: 557


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

Line: 559


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

Line: 568


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

Line: 579


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

Line: 580


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

Line: 583


  END insert_comm_lines_api;

Line: 595


  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)
	IS

    x_dest_table_name	cn_obj_tables_v.name%TYPE;

Line: 606


        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: 639


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

Line: 640


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

Line: 658


    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: 688


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

Line: 694


	    cn_utils.appindcr(code, '  SELECT ');

Line: 708


             (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: 734


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

Line: 735


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

Line: 750


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

Line: 751


     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: 757


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

Line: 758


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

Line: 760


  END update_comm_lines_api;

Line: 778


    l_delete_flag VARCHAR2(1);

Line: 781


      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: 792


    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: 798


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

Line: 801


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

Line: 854


	 (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(p_location_name)
		    AND tmov.org_id = x_org_id
       ORDER BY table_map_object_id)
    LOOP
      --+
      -- Add a terminal ';' if none was registered