DBA Data[Home] [Help]

APPS.IEM_AGENT_ACT_PVT SQL Statements

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

Line: 13

         		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';
Line: 44

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

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

select iem_agents_s1.nextval into l_agent_account_id
from dual;
Line: 51

INSERT INTO IEM_AGENTS (
AGENT_ID   ,
EMAIL_ACCOUNT_ID   ,
RESOURCE_ID        ,
SIGNATURE          ,
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_AGENT_ACCOUNT_ID   ,
p_EMAIL_ACCOUNT_ID   ,
p_resource_id         ,
decode(p_SIGNATURE,FND_API.G_MISS_CHAR,NULL,p_signature),
	decode(p_CREATED_BY,null,-1,p_CREATED_BY),
	sysdate,
	decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
	sysdate,
	decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
	p_ATTRIBUTE1,
	p_ATTRIBUTE2,
	p_ATTRIBUTE3,
	p_ATTRIBUTE4,
	p_ATTRIBUTE5,
	p_ATTRIBUTE6,
	p_ATTRIBUTE7,
	p_ATTRIBUTE8,
	p_ATTRIBUTE9,
	p_ATTRIBUTE10,
	p_ATTRIBUTE11,
	p_ATTRIBUTE12,
	p_ATTRIBUTE13,
	p_ATTRIBUTE14,
	p_ATTRIBUTE15
 );
Line: 152

PROCEDURE delete_item (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
				 p_resource_id	in number,
				 p_email_account_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):='delete_item';
Line: 180

   select agent_id into l_agent_id
   from IEM_AGENTS
   where resource_id=p_resource_id
   and email_account_id=p_email_account_id;
Line: 185

   delete from IEM_AGENTS
   where resource_id=p_resource_id
   and email_account_id=p_email_account_id;
Line: 195

						p_param => 'DELETE',
						p_value => l_agent_id,
						x_return_status  => l_stat,
						x_msg_count      => l_count,
						x_msg_data      => l_data);
Line: 212

	ROLLBACK TO delete_item_PVT;
Line: 219

	ROLLBACK TO delete_item_PVT;
Line: 226

	ROLLBACK TO delete_item_PVT;
Line: 241

 END	delete_item;
Line: 288

		SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_in_resource_tbl(i);
Line: 295

    		select count(*) into l_agent_account_count from iem_agents where resource_id=p_in_resource_tbl(i)
    		and email_account_id=p_email_account_id;
Line: 326

    	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_msg_count=>l_msg_count,
						x_msg_data=>l_msg_data,
						x_return_status=>l_return_status);
Line: 430

 PROCEDURE delete_agntacct_by_agent (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
			      p_out_resource_tbl             IN  jtf_varchar2_Table_100,
			      p_email_account_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):='delete_agntacct_by_agent';
Line: 468

SAVEPOINT delete_agntacct_by_agent_PVT;
Line: 489

		SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_out_resource_tbl(i);
Line: 496

    		select count(*) into l_agent_account_count from iem_agents where resource_id=p_out_resource_tbl(i)
    		and email_account_id=p_email_account_id;
Line: 529

    			   	IEM_AGENT_ACT_PVT.delete_item(p_api_version_number=>1.0,
 					p_init_msg_list=>'F' ,
					p_commit=>'F'	    ,
					p_resource_id => p_out_resource_tbl(i),
					p_email_account_id => p_email_account_id,
					x_msg_count=>l_msg_count,
					x_msg_data=>l_msg_data,
					x_return_status=>l_return_status);
Line: 540

  	   		/*	   select count(*) into l_resource_param_count from jtf_rs_resource_values
    		 		   where value_type=p_email_account_id and resource_id=p_out_resource_tbl(i) and value='IEM_DEFAULT_VALUE';
Line: 545

    		 		   	select resource_param_value_id, object_version_number into l_resource_param_value_id, l_object_version_number from jtf_rs_resource_values
    		 		   	where value_type=p_email_account_id and resource_id=p_out_resource_tbl(i) and value='IEM_DEFAULT_VALUE';
Line: 548

				   	JTF_RS_RESOURCE_VALUES_PUB.DELETE_RS_RESOURCE_VALUES(
      						P_Api_Version => 1.0,
     	 					P_Init_Msg_List  => FND_API.G_FALSE,
      						P_Commit  => FND_API.G_FALSE,
      						p_resource_param_value_id => l_resource_param_value_id,
      						p_object_version_number => l_object_version_number,
      						X_Return_Status => l_return_status,
      						X_Msg_Count => l_msg_count,
      						X_Msg_Data => l_msg_data);
Line: 589

    		FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT1_DELETED');
Line: 604

    		 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGNTACCT9_DELETED');
Line: 620

    		FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT5_DELETED');
Line: 642

	ROLLBACK TO delete_agntacct_by_agent_PVT;
Line: 649

	ROLLBACK TO delete_agntacct_by_agent_PVT;
Line: 656

	ROLLBACK TO delete_agntacct_by_agent_PVT;
Line: 671

 END delete_agntacct_by_agent;
Line: 674

 PROCEDURE update_agntacct_by_agent_wrap (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
		    	      p_in_resource_tbl              IN  jtf_varchar2_Table_100,
			      p_out_resource_tbl             IN  jtf_varchar2_Table_100,
			      p_email_account_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_agntacct_by_agent_wrap';
Line: 733

	iem_agent_act_pvt.delete_agntacct_by_agent (p_api_version_number =>1.0,
 						p_init_msg_list=>'F' ,
						p_commit=>'F'	    ,
			      	p_out_resource_tbl  => p_out_resource_tbl,
			      	p_email_account_id => p_email_account_id,
			      	x_return_status =>l_return_status,
  				x_msg_count    => l_msg_count,
  				x_msg_data      => l_msg_data);
Line: 800

 END update_agntacct_by_agent_wrap;
Line: 852

	SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_resource_id;
Line: 861

	SELECT count(*) into l_count
	FROM jtf_rs_resource_extns
	WHERE resource_id = p_resource_id
        AND ( end_date_active is null OR
		    trunc(end_date_active) >= trunc(sysdate) );
Line: 873

   	    select count(*) into l_account_count FROM IEM_MSTEMAIL_ACCOUNTS
   	    WHERE EMAIL_ACCOUNT_ID =  p_in_email_account_tbl(i);
Line: 878

		SELECT USER_NAME
  		INTO l_email_user
		FROM IEM_MSTEMAIL_ACCOUNTS
   		WHERE EMAIL_ACCOUNT_ID = p_in_email_account_tbl(i);
Line: 884

    		select count(*) into l_agent_account_count from iem_agents where email_account_id= p_in_email_account_tbl(i) and resource_id=p_resource_id;
Line: 914

    	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_msg_count=>l_msg_count,
						x_msg_data=>l_msg_data,
						x_return_status=>l_return_status);
Line: 1031

  PROCEDURE delete_agntacct_by_account (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
			      p_out_email_account_tbl             IN  jtf_varchar2_Table_100,
			      p_resource_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):='delete_agntacct_by_account';
Line: 1069

SAVEPOINT delete_agntacct_by_account_PVT;
Line: 1087

   	SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_resource_id;
Line: 1094

	   select count(*) into l_account_count FROM IEM_MSTEMAIL_ACCOUNTS
   	   WHERE EMAIL_ACCOUNT_ID = p_out_email_account_tbl(i);
Line: 1099

		SELECT USER_NAME
  		INTO l_email_user
		FROM IEM_MSTEMAIL_ACCOUNTS
   		WHERE EMAIL_ACCOUNT_ID = p_out_email_account_tbl(i);
Line: 1107

    		select count(*) into l_agent_account_count from iem_agents where email_account_id=p_out_email_account_tbl(i)
    		and resource_id=p_resource_id;
Line: 1140

    			   	IEM_AGENT_ACT_PVT.delete_item(p_api_version_number=>1.0,
     	 				P_Init_Msg_List  =>'F',
      					P_Commit  => 'F',
					 p_resource_id => p_resource_id,
					 p_email_account_id => p_out_email_account_tbl(i),
					x_msg_count=>l_msg_count,
					x_msg_data=>l_msg_data,
					x_return_status=>l_return_status);
Line: 1151

  	   		/*		select count(*) into l_resource_param_count from jtf_rs_resource_values
    		 			where value_type=p_out_email_account_tbl(i) and resource_id=p_resource_id and value='IEM_DEFAULT_VALUE';
Line: 1156

    		 		   	   select resource_param_value_id, object_version_number into l_resource_param_value_id, l_object_version_number from jtf_rs_resource_values
    		 		    	   where value_type=p_out_email_account_tbl(i) and resource_id=p_resource_id and value='IEM_DEFAULT_VALUE';
Line: 1159

				   	   JTF_RS_RESOURCE_VALUES_PUB.DELETE_RS_RESOURCE_VALUES(
      						P_Api_Version => 1.0,
     	 					P_Init_Msg_List  => FND_API.G_FALSE,
      						P_Commit  => FND_API.G_FALSE,
      						p_resource_param_value_id => l_resource_param_value_id,
      						p_object_version_number => l_object_version_number,
      						X_Return_Status => l_return_status,
      						X_Msg_Count => l_msg_count,
      						X_Msg_Data => l_msg_data);
Line: 1198

    		FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT2_DELETED');
Line: 1212

    		 FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGNTACCT10_DELETED');
Line: 1227

    		FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT6_DELETED');
Line: 1249

	ROLLBACK TO delete_agntacct_by_account_PVT;
Line: 1256

	ROLLBACK TO delete_agntacct_by_account_PVT;
Line: 1263

	ROLLBACK TO delete_agntacct_by_account_PVT;
Line: 1278

 END delete_agntacct_by_account;
Line: 1280

 PROCEDURE update_agntacct_by_acct_wrap (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
		    	      p_in_email_account_tbl             IN  jtf_varchar2_Table_100,
			      p_out_email_account_tbl            IN  jtf_varchar2_Table_100,
			      p_resource_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_agntacct_by_acct_wrap';
Line: 1342

	iem_agent_act_pvt.delete_agntacct_by_account (p_api_version_number =>1.0,
     	 		P_Init_Msg_List  =>'F',
      			P_Commit  => 'F',
			      	p_out_email_account_tbl  => p_out_email_account_tbl,
			      	p_resource_id => p_resource_id,
			      	x_return_status =>l_return_status,
  				x_msg_count    => l_msg_count,
  				x_msg_data      => l_msg_data);
Line: 1408

 END update_agntacct_by_acct_wrap;
Line: 1413

 PROCEDURE update_agent_cherrypick (
	                      p_api_version_number  IN   NUMBER,
 		  	      p_init_msg_list       IN   VARCHAR2 ,
		    	      p_commit	            IN   VARCHAR2 ,
			      p_in_cherrypick_tbl   IN  jtf_varchar2_Table_100,
			      p_out_cherrypick_tbl  IN  jtf_varchar2_Table_100,
			      p_email_account_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_agent_cherrypick';
Line: 1453

  SAVEPOINT update_agent_cherrypick_PVT;
Line: 1474

		SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_in_cherrypick_tbl(i);
Line: 1481

    		select count(*) into l_agent_account_count
		from iem_agents
		where resource_id=p_in_cherrypick_tbl(i)
    		and email_account_id=p_email_account_id;
Line: 1491

		          UPDATE iem_agents set cherry_pick_flag = 'Y'
			  WHERE resource_id=p_in_cherrypick_tbl(i)
    		          and email_account_id=p_email_account_id;
Line: 1516

		SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_out_cherrypick_tbl(i);
Line: 1523

    		select count(*) into l_agent_account_count
		from iem_agents
		where resource_id=p_out_cherrypick_tbl(i)
    		and email_account_id=p_email_account_id;
Line: 1533

		          UPDATE iem_agents set cherry_pick_flag = null
			  WHERE resource_id=p_out_cherrypick_tbl(i)
    		          and email_account_id=p_email_account_id;
Line: 1568

	ROLLBACK TO update_agent_cherrypick_PVT;
Line: 1575

	ROLLBACK TO update_agent_cherrypick_PVT;
Line: 1582

      ROLLBACK TO update_agent_cherrypick_PVT;
Line: 1597

 END update_agent_cherrypick;
Line: 1601

 PROCEDURE update_acct_cherrypick (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
		    	      p_in_acct_chrypick_tbl             IN  jtf_varchar2_Table_100,
			      p_out_acct_chrypick_tbl            IN  jtf_varchar2_Table_100,
			      p_resource_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_acct_cherrypick';
Line: 1640

   SAVEPOINT update_acct_chrypick_PVT;
Line: 1661

		SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_resource_id;
Line: 1668

    		select count(*) into l_agent_account_count
		from iem_agents
		where resource_id=p_resource_id
    		and email_account_id=p_in_acct_chrypick_tbl(i);
Line: 1678

		          UPDATE iem_agents set cherry_pick_flag = 'Y'
		          where resource_id=p_resource_id
    		          and email_account_id=p_in_acct_chrypick_tbl(i);
Line: 1703

		SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
		INTO l_user_id, l_user_name, l_res_name
		FROM JTF_RS_RESOURCE_EXTNS
		WHERE RESOURCE_ID = p_resource_id;
Line: 1710

    		select count(*) into l_agent_account_count
		from iem_agents
		where resource_id=p_resource_id
    		and email_account_id=p_out_acct_chrypick_tbl(i);
Line: 1720

		          UPDATE iem_agents set cherry_pick_flag = null
		          where resource_id=p_resource_id
    		          and email_account_id=p_out_acct_chrypick_tbl(i);
Line: 1724

               		   FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ACCT_DELETED');
Line: 1729

               		      FND_MESSAGE.SET_NAME('IEM','IEM_CHRRYPICK_ACCT_DELETE_NO_DATA_ERROR');
Line: 1733

               		      FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ACCT_DELETE_OTHERS_ERROR');
Line: 1755

       ROLLBACK TO update_acct_chrypick_PVT;
Line: 1762

       ROLLBACK TO update_acct_chrypick_PVT;
Line: 1769

      ROLLBACK TO update_acct_chrypick_PVT;
Line: 1784

 END update_acct_cherrypick;