APPS.IEM_EMAIL_SERVERS_PVT SQL Statements

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

Line: 17

         	p_LAST_UPDATED_BY    NUMBER,
          	p_LAST_UPDATE_DATE    DATE,
          	p_LAST_UPDATE_LOGIN    NUMBER,
         	p_ATTRIBUTE1    VARCHAR2,
          	p_ATTRIBUTE2    VARCHAR2,
          	p_ATTRIBUTE3    VARCHAR2,
          	p_ATTRIBUTE4    VARCHAR2,
          	p_ATTRIBUTE5    VARCHAR2,
          	p_ATTRIBUTE6    VARCHAR2,
          	p_ATTRIBUTE7    VARCHAR2,
          	p_ATTRIBUTE8    VARCHAR2,
          	p_ATTRIBUTE9    VARCHAR2,
          	p_ATTRIBUTE10    VARCHAR2,
          	p_ATTRIBUTE11    VARCHAR2,
          	p_ATTRIBUTE12    VARCHAR2,
          	p_ATTRIBUTE13    VARCHAR2,
          	p_ATTRIBUTE14    VARCHAR2,
          	p_ATTRIBUTE15    VARCHAR2,
		      x_return_status OUT NOCOPY VARCHAR2,
  		      x_msg_count	      OUT NOCOPY    NUMBER,
	  	      x_msg_data OUT NOCOPY VARCHAR2
			 ) is
	l_api_name        		VARCHAR2(255):='create_item_sss';

Line: 46

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

Line: 47

     l_LAST_UPDATE_LOGIN    NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;

Line: 79

	SELECT iem_email_servers_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 85

	Select count(*) into l_type_cnt from iem_email_server_types
	where email_server_type_id=p_server_type_id
	and rownum=1;

Line: 95

	Select count(*) into l_grp_cnt from iem_server_groups
	where server_group_id=p_server_group_id
	and rownum=1;

Line: 105

	INSERT INTO iem_email_servers
	(
	EMAIL_SERVER_ID,
	SERVER_NAME,
	DNS_NAME,
	IP_ADDRESS,
	PORT,
	SERVER_TYPE_ID ,
	RT_AVAILABILITY,
	SERVER_GROUP_ID,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE,
	LAST_UPDATE_LOGIN,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	ATTRIBUTE11,
	ATTRIBUTE12,
	ATTRIBUTE13,
	ATTRIBUTE14,
	ATTRIBUTE15 )
	VALUES
	(
	l_seq_id,
	p_server_name,
	p_dns_name,
	p_ip_address,
	p_port,
	p_server_type_id,
	p_rt_availability,
	p_server_group_id,
	decode(l_CREATED_BY,null,-1,l_CREATED_BY),
	sysdate,
     decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
     sysdate,
     decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
     decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
     decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
     decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
     decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
     decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
     decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
     decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
     decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
     decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
     decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
     decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
     decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
     decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
     decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
     decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
	);

Line: 258

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

Line: 259

     l_LAST_UPDATE_LOGIN    NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;

Line: 296

               	p_LAST_UPDATED_BY  =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
         	p_LAST_UPDATE_DATE  =>SYSDATE,
          	p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
         	p_ATTRIBUTE1   =>null,
          	p_ATTRIBUTE2   =>null,
          	p_ATTRIBUTE3   =>null,
          	p_ATTRIBUTE4   =>null,
          	p_ATTRIBUTE5   =>null,
          	p_ATTRIBUTE6   =>null,
          	p_ATTRIBUTE7   =>null,
          	p_ATTRIBUTE8   =>null,
          	p_ATTRIBUTE9   =>null,
          	p_ATTRIBUTE10  =>null,
          	p_ATTRIBUTE11  =>null,
          	p_ATTRIBUTE12  =>null,
          	p_ATTRIBUTE13  =>null,
          	p_ATTRIBUTE14  =>null,
          	p_ATTRIBUTE15  =>null,
	       x_return_status =>l_stat,
	       x_msg_count    => l_count,
	       x_msg_data      => l_data);

Line: 322

	-- Update Cache Audit Trail
	select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=p_server_type_id;

Line: 326

		select email_server_id into l_email_server_id from iem_email_servers
		where UPPER(dns_name)=UPPER(p_dns_name)
        	and ip_address=p_ip_address and port=p_port;

Line: 330

		SELECT iem_email_servers_s1.currval
		INTO l_email_server_id
		FROM dual;

Line: 398

PROCEDURE delete_item (p_api_version_number    IN   NUMBER,
 		     p_init_msg_list  IN   VARCHAR2,
		    	p_commit	    IN   VARCHAR2,
			p_email_server_id	IN NUMBER,
			p_dns_name IN   VARCHAR2,
			p_ip_address IN   VARCHAR2,
			p_port IN   NUMBER,
			x_return_status OUT NOCOPY VARCHAR2,
  		  	x_msg_count	      OUT NOCOPY    NUMBER,
	  	  	x_msg_data OUT NOCOPY VARCHAR2
			 ) is
	l_api_name        		VARCHAR2(255):='delete_item';

Line: 414

SAVEPOINT		delete_item_PVT;

Line: 431

	delete from iem_email_servers
	where ip_address=p_ip_address and dns_name=p_dns_name and port=p_port;

Line: 434

	delete from iem_email_servers
	where email_server_id=p_email_server_id;

Line: 449

	ROLLBACK TO delete_item_PVT;

Line: 456

	ROLLBACK TO delete_item_PVT;

Line: 463

	ROLLBACK TO delete_item_PVT;

Line: 478

 END	delete_item;

Line: 481

 PROCEDURE update_item_sss (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2,
		    	      p_commit	    IN   VARCHAR2,
				 p_email_server_id IN	NUMBER,
				 p_server_name	 IN	varchar2,
				 p_dns_name IN   VARCHAR2,
				 p_ip_address IN   VARCHAR2,
				 p_port IN   NUMBER,
				 p_server_type_id IN 	number,
				 p_rt_availability	in varchar2,
				 p_server_group_id	in number,
         	p_LAST_UPDATED_BY    NUMBER,
          	p_LAST_UPDATE_DATE    DATE,
          	p_LAST_UPDATE_LOGIN    NUMBER,
         	p_ATTRIBUTE1    VARCHAR2,
          	p_ATTRIBUTE2    VARCHAR2,
          	p_ATTRIBUTE3    VARCHAR2,
          	p_ATTRIBUTE4    VARCHAR2,
          	p_ATTRIBUTE5    VARCHAR2,
          	p_ATTRIBUTE6    VARCHAR2,
          	p_ATTRIBUTE7    VARCHAR2,
          	p_ATTRIBUTE8    VARCHAR2,
          	p_ATTRIBUTE9    VARCHAR2,
          	p_ATTRIBUTE10    VARCHAR2,
          	p_ATTRIBUTE11    VARCHAR2,
          	p_ATTRIBUTE12    VARCHAR2,
          	p_ATTRIBUTE13    VARCHAR2,
          	p_ATTRIBUTE14    VARCHAR2,
          	p_ATTRIBUTE15    VARCHAR2,
			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_sss';

Line: 519

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

Line: 520

     l_LAST_UPDATE_LOGIN    NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;

Line: 526

SAVEPOINT		update_item_PVT;

Line: 554

	Select count(*) into l_type_cnt from iem_email_server_types
	where email_server_type_id=p_server_type_id
	and rownum=1;

Line: 565

	select count(*) into l_grp_cnt from iem_server_groups
	where server_group_id=p_server_group_id
	and rownum=1;

Line: 577

	UPDATE IEM_EMAIL_SERVERS
	set server_name=decode(p_server_name,FND_API.G_MISS_CHAR, NULL, NULL, server_name,p_server_name),
	rt_availability=decode(p_rt_availability,FND_API.G_MISS_CHAR, NULL, NULL,rt_availability,p_rt_availability),
	server_type_id=decode(p_server_type_id,FND_API.G_MISS_NUM, NULL, NULL,server_type_id,p_server_type_id),
	server_group_id=decode(p_server_group_id,FND_API.G_MISS_NUM, NULL, NULL,server_group_id,p_server_group_id),
          LAST_UPDATE_DATE = sysdate,
         LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,-1,l_LAST_UPDATED_BY),
      LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
              ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
              ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
              ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
              ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
              ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
              ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
              ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
              ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
              ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
              ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
              ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
              ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
              ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
              ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
              ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
	where dns_name=p_dns_name and ip_address=p_ip_address and port=p_port;

Line: 603

	UPDATE IEM_EMAIL_SERVERS
	SET server_name=decode(p_server_name,FND_API.G_MISS_CHAR, NULL, NULL,server_name,p_server_name),
	server_type_id=decode(p_server_type_id,FND_API.G_MISS_NUM, NULL, NULL,server_type_id,p_server_type_id),
	rt_availability=decode(p_rt_availability,FND_API.G_MISS_CHAR, NULL, NULL,rt_availability,p_rt_availability),
	server_group_id=decode(p_server_group_id,FND_API.G_MISS_NUM, NULL, NULL,server_group_id,p_server_group_id),
	dns_name=decode(p_dns_name,FND_API.G_MISS_CHAR, NULL, NULL,dns_name,p_dns_name),
	ip_address=decode(p_ip_address,FND_API.G_MISS_CHAR, NULL, NULL,ip_address,p_ip_address),
	port=decode(p_port,FND_API.G_MISS_NUM, NULL, NULL,port,p_port),
          LAST_UPDATE_DATE = sysdate,
          LAST_UPDATED_BY =  l_LAST_UPDATED_BY,
          LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN,
      	      ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
              ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
              ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
              ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
              ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
              ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
              ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
              ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
              ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
              ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
              ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
              ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
              ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
              ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
              ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
	where EMAIL_SERVER_ID=p_email_server_id;

Line: 649

      ROLLBACK TO update_item_pvt;

Line: 656

   ROLLBACK TO update_item_pvt;

Line: 665

	ROLLBACK TO update_item_PVT;

Line: 672

	ROLLBACK TO update_item_PVT;

Line: 679

	ROLLBACK TO update_item_PVT;

Line: 697

 END	update_item_sss;

Line: 700

 PROCEDURE update_item_wrap_sss (p_api_version_number    IN   NUMBER,
 		       p_init_msg_list  IN   VARCHAR2,
		       p_commit	    IN   VARCHAR2,
		       p_email_server_id IN	NUMBER,
			p_server_name	 IN	varchar2,
			p_dns_name IN   VARCHAR2,
			p_ip_address IN   VARCHAR2,
			p_port IN   NUMBER,
			p_server_type_id IN 	number,
			p_rt_availability	in varchar2,
			p_server_group_id	in number,
			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_wrap_sss';

Line: 726

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

Line: 727

     l_LAST_UPDATE_LOGIN    NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;

Line: 751

     	IEM_EMAIL_SERVERS_PVT.update_item_sss(
               p_api_version_number =>1.0,
               p_init_msg_list  => FND_API.G_FALSE,
               p_commit=>FND_API.G_FALSE,
               p_email_server_id => p_email_server_id,
               p_server_name  => p_server_name,
               p_dns_name    => p_dns_name,
               p_ip_address  => p_ip_address,
               p_port  => p_port,
               p_server_type_id => p_server_type_id,
               p_rt_availability => p_rt_availability,
               p_server_group_id => p_server_group_id,
               	p_LAST_UPDATED_BY  =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
         	p_LAST_UPDATE_DATE  =>SYSDATE,
          	p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
         	p_ATTRIBUTE1   =>null,
          	p_ATTRIBUTE2   =>null,
          	p_ATTRIBUTE3   =>null,
          	p_ATTRIBUTE4   =>null,
          	p_ATTRIBUTE5   =>null,
          	p_ATTRIBUTE6   =>null,
          	p_ATTRIBUTE7   =>null,
          	p_ATTRIBUTE8   =>null,
          	p_ATTRIBUTE9   =>null,
          	p_ATTRIBUTE10  =>null,
          	p_ATTRIBUTE11  =>null,
          	p_ATTRIBUTE12  =>null,
          	p_ATTRIBUTE13  =>null,
          	p_ATTRIBUTE14  =>null,
          	p_ATTRIBUTE15  =>null,
	       x_return_status =>l_stat,
	       x_msg_count    => l_count,
	       x_msg_data      => l_data);

Line: 789

	-- Update Cache Audit Trail
	select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=p_server_type_id;

Line: 793

		select email_server_id into l_email_server_id from iem_email_servers
		where UPPER(dns_name)=UPPER(p_dns_name)
        	and ip_address=p_ip_address and port=p_port;

Line: 804

                        p_param => 'UPDATE',
                        p_value => l_email_server_id,
                        x_return_status  => l_stat,
                        x_msg_count      => l_count,
                        x_msg_data      => l_data
                        );

Line: 860

 END	update_item_wrap_sss;

Line: 862

PROCEDURE delete_item_batch
     (p_api_version_number     IN  NUMBER,
      P_init_msg_list   IN  VARCHAR2,
      p_commit          IN  VARCHAR2,
      p_group_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: 872

    l_api_name		varchar2(30):='delete_item_batch';

Line: 881

    SERVER_GROUP_NOT_DELETED     EXCEPTION;

Line: 886

    SAVEPOINT delete_item_batch;

Line: 909

	-- Update Cache Audit Trail
	select server_type_id into l_server_type_id from iem_email_servers WHERE EMAIL_SERVER_ID =l_email_server_id;

Line: 912

	select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=l_server_type_id;

Line: 918

                        p_param => 'DELETE',
                        p_value => l_email_server_id,
                        x_return_status  => l_stat,
                        x_msg_count      => l_count,
                        x_msg_data      => l_data
                        );

Line: 926

         	DELETE FROM IEM_EMAIL_SERVERS
		WHERE EMAIL_SERVER_ID =l_email_server_id;

Line: 938

   WHEN SERVER_GROUP_NOT_DELETED THEN
        ROLLBACK TO delete_item_batch;

Line: 941

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

Line: 946

	   ROLLBACK TO delete_item_batch;

Line: 952

	   ROLLBACK TO delete_item_batch;

Line: 957

	  ROLLBACK TO delete_item_batch;

Line: 965

END delete_item_batch;