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

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

Line: 19


G_created_updated_by   NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;

Line: 20


G_LAST_UPDATE_LOGIN    NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;

Line: 22


PROCEDURE delete_item_batch
             (p_api_version_number      IN  NUMBER,
              P_init_msg_list           IN  VARCHAR2 := null,
              p_commit                  IN  VARCHAR2 := null,
              p_tagKey_ids_tbl          IN  jtf_varchar2_Table_100,
              x_return_status           OUT NOCOPY VARCHAR2,
              x_msg_count               OUT NOCOPY NUMBER,
              x_msg_data                OUT NOCOPY VARCHAR2)
IS
    i                       INTEGER;

Line: 32


    l_api_name		        varchar2(30):='delete_item_batch';

Line: 41


    IEM_TAG_NOT_DELETED     EXCEPTION;

Line: 45


    SAVEPOINT delete_item_batch;

Line: 73


            select count(*) into l_route_count
            from iem_tag_keys a, iem_route_rules b
            where a.tag_key_id=p_tagKey_ids_tbl(i) and upper('IEMS'||a.tag_id) = UPPER(b.key_type_code);

Line: 77


            select count(*) into l_class_count
            from iem_tag_keys a, iem_route_class_rules b
            where a.tag_key_id=p_tagKey_ids_tbl(i) and upper('IEMS'||a.tag_id) = UPPER(b.key_type_code);

Line: 81


            select count(*) into l_emailproc_count
            from iem_tag_keys a, iem_emailproc_rules b
            where a.tag_key_id=p_tagKey_ids_tbl(i) and upper('IEMS'||a.tag_id) = UPPER(b.key_type_code);

Line: 86


                select tag_name into l_tag_name from iem_tag_keys where tag_key_id = p_tagKey_ids_tbl(i);

Line: 89


                DELETE
                FROM IEM_TAG_KEYS
                WHERE TAG_KEY_ID = p_tagKey_ids_tbl(i);

Line: 94


                    raise IEM_TAG_NOT_DELETED;

Line: 97


                DELETE
                FROM IEM_ACCOUNT_TAG_KEYS
                WHERE TAG_KEY_ID = p_tagKey_ids_tbl(i);

Line: 108


        DELETE
        FROM IEM_ACCOUNT_TAG_KEYS
        WHERE TAG_KEY_ID = p_tagKey_ids_tbl(i);

Line: 120


        FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_TAG_NOT_DELETED');

Line: 141


   WHEN IEM_TAG_NOT_DELETED THEN
        ROLLBACK TO delete_item_batch;

Line: 144


        FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_NOT_DELETED');

Line: 150


  	     ROLLBACK TO delete_item_batch;

Line: 157


	   ROLLBACK TO delete_item_batch;

Line: 163


	  ROLLBACK TO delete_item_batch;

Line: 171


END delete_item_batch;

Line: 174


PROCEDURE delete_acct_tag_on_acct_ID
             (p_api_version_number      IN  NUMBER,
              P_init_msg_list           IN  VARCHAR2 := null,
              p_commit                  IN  VARCHAR2 := null,
              p_email_acct_id           IN  iem_mstemail_accounts.email_account_id%type,
              x_return_status           OUT NOCOPY VARCHAR2,
              x_msg_count               OUT NOCOPY NUMBER,
              x_msg_data                OUT NOCOPY VARCHAR2)
IS
    i                       INTEGER;

Line: 184


    l_api_name		        varchar2(30):='delete_acct_tag_on_acct_ID';

Line: 189


    IEM_TAG_NOT_DELETED     EXCEPTION;

Line: 193


    SAVEPOINT delete_association_on_acct_ID;

Line: 216


    delete from iem_account_tag_keys where email_account_id = l_acct_id;

Line: 227


  	     ROLLBACK TO delete_acct_tag_on_acct_ID;

Line: 234


	   ROLLBACK TO delete_acct_tag_on_acct_ID;

Line: 240


	  ROLLBACK TO delete_acct_tag_on_acct_ID;

Line: 248


END delete_acct_tag_on_acct_ID;

Line: 327


    select count(*) into l_name_count from iem_tag_keys where UPPER(tag_name) = UPPER(l_key_name);

Line: 332


    select count(*) into l_id_count from iem_tag_keys where UPPER(tag_id) = UPPER(l_key_id_temp);

Line: 337


    SELECT count(*) into l_id_count_rt from FND_LOOKUPS WHERE upper(lookup_code)=upper(l_key_id) and enabled_flag = 'Y' AND NVL(start_date_active, SYSDATE) <= SYSDATE AND NVL(end_date_active, SYSDATE)   >= SYSDATE  AND lookup_type = 'IEM_KEY_TYPE_CODE';

Line: 342


    SELECT count(*) into l_id_count_cls from FND_LOOKUPS
    WHERE upper(lookup_code)=upper(l_key_id) and enabled_flag = 'Y'
    AND NVL(start_date_active, SYSDATE) <= SYSDATE AND NVL(end_date_active, SYSDATE)   >= SYSDATE  AND lookup_type = 'IEM_CLASS_KEY_TYPE_CODE';

Line: 393


   	SELECT IEM_TAG_KEYS_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 399


	INSERT INTO IEM_TAG_KEYS
	(
	TAG_KEY_ID,
	TAG_ID,
	TAG_NAME,
    TAG_TYPE_CODE,
    VALUE,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	ATTRIBUTE11,
	ATTRIBUTE12,
	ATTRIBUTE13,
	ATTRIBUTE14,
	ATTRIBUTE15,
    ATTRIBUTE_CATEGORY,
    CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN
	)
	VALUES
	(
	l_seq_id,
	l_key_id_temp,
	l_key_name,
	p_type_type_code,
    l_value,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    decode(G_created_updated_by,null,-1,G_created_updated_by),
	sysdate,
    decode(G_created_updated_by,null,-1,G_created_updated_by),
    sysdate,
    decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
	);

Line: 617


    select count(*) into l_count from iem_tag_keys where tag_key_id = p_tag_key_id;

Line: 623


   select count(*) into l_count from iem_mstemail_accounts where email_account_id = p_email_account_id;

Line: 629


	SELECT IEM_ACCOUNT_TAG_KEYS_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 633


	INSERT INTO IEM_ACCOUNT_TAG_KEYS
	(
	ACCOUNT_TAG_KEY_ID,
	EMAIL_ACCOUNT_ID,
    TAG_KEY_ID,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	ATTRIBUTE11,
	ATTRIBUTE12,
	ATTRIBUTE13,
	ATTRIBUTE14,
	ATTRIBUTE15,
    ATTRIBUTE_CATEGORY,
    CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN
	)
   VALUES
   (
   l_seq_id,
   p_email_account_id,
   p_tag_key_id,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL,
   decode(G_created_updated_by,null,-1,G_created_updated_by),
   sysdate,
   decode(G_created_updated_by,null,-1,G_created_updated_by),
   sysdate,
   decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
	);

Line: 749


PROCEDURE update_acct_tag_wrap (p_api_version_number     IN   NUMBER,
 	                         p_init_msg_list         IN   VARCHAR2 := null,
	                         p_commit	             IN   VARCHAR2 := null,
  	                         p_account_id	         IN   NUMBER,
                             p_in_key_id             IN   VARCHAR2:= null,
                             p_out_key_id            IN   VARCHAR2 := null,
                             x_return_status         OUT  NOCOPY VARCHAR2,
                             x_msg_count             OUT  NOCOPY NUMBER,
                             x_msg_data              OUT  NOCOPY VARCHAR2 )is

    l_api_name              VARCHAR2(255):='update_acct_tag_wrap';

Line: 767


    IEM_NO_RULE_UPDATE          EXCEPTION;

Line: 777


SAVEPOINT  update_acct_tag_wrap;

Line: 804


        delete from iem_account_tag_keys a where a.email_account_id=p_account_id and a.tag_key_id =
               (select  b.tag_key_id from iem_tag_keys b where UPPER(b.tag_id) = UPPER(l_out_tab(i)) );

Line: 810


        select count(*) into l_count from iem_account_tag_keys a, iem_tag_keys b
                                    where a.email_account_id=p_account_id and a.tag_key_id=b.tag_key_id and b.tag_id=l_in_tab(j);

Line: 814


            select tag_key_id into l_tag_key_id from iem_tag_keys where UPPER(tag_id) = UPPER(l_in_tab(j));

Line: 842


	    ROLLBACK TO update_acct_tag_wrap;

Line: 849


	    ROLLBACK TO update_acct_tag_wrap;

Line: 856


            ROLLBACK TO update_acct_tag_wrap;

Line: 862


            ROLLBACK TO update_acct_tag_wrap;

Line: 868


            ROLLBACK TO update_acct_tag_wrap;

Line: 877


END update_acct_tag_wrap;

Line: 880


PROCEDURE update_item_tag_key (
                 p_api_version_number       IN   NUMBER,
    	  	     p_init_msg_list            IN   VARCHAR2 := null,
    	    	 p_commit	                IN   VARCHAR2 := null,
    			 p_tag_key_id               IN   NUMBER,
                 p_key_id                   IN   VARCHAR2:= null,
    			 p_key_name                 IN   VARCHAR2:= null,
                 p_type_type_code           IN   VARCHAR2:= null,
    			 p_value	                IN   VARCHAR2:= null,
			     x_return_status	        OUT	 NOCOPY VARCHAR2,
  		  	     x_msg_count	            OUT	 NOCOPY NUMBER,
	  	  	     x_msg_data	                OUT	 NOCOPY VARCHAR2
			 ) is
	l_api_name        		VARCHAR2(255):='update_item_tag_key';

Line: 912


  SAVEPOINT		update_item_tag_key;

Line: 939


    select count(*) into l_name_count from iem_tag_keys where UPPER(tag_name) = UPPER(p_key_name) and tag_key_id <> p_tag_key_id;

Line: 946


    select count(*) into l_id_count from iem_tag_keys where UPPER(tag_id) = UPPER(p_key_id) and tag_key_id <> p_tag_key_id;

Line: 988


	update IEM_TAG_KEYS
	set
           --tag_id=decode(p_key_id,FND_API.G_MISS_CHAR,tag_id,p_key_id),
	       tag_name=decode(p_key_name,null,tag_name,p_key_name),
	       tag_type_code=decode(p_type_type_code,null,tag_type_code,p_type_type_code),
           value=decode(p_value,null,tag_type_code,p_value),
           LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
           LAST_UPDATE_DATE = sysdate,
           LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
	where tag_key_id=p_tag_key_id;

Line: 1012


	 ROLLBACK TO update_item_tag_key;

Line: 1017


	 ROLLBACK TO update_item_tag_key;

Line: 1022


	    ROLLBACK TO update_item_tag_key;

Line: 1029


	    ROLLBACK TO update_item_tag_key;

Line: 1036


	    ROLLBACK TO update_item_tag_key;

Line: 1043


	   ROLLBACK TO update_item_tag_key;

Line: 1051


	   ROLLBACK TO update_item_tag_key;

Line: 1060


	ROLLBACK TO update_item_tag_key;

Line: 1077


END	update_item_tag_key;