DBA Data[Home] [Help]

APPS.IEM_EMTA_ADMIN_PVT SQL Statements

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

Line: 18

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

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

PROCEDURE UPDATE_DP_CONFIG_DATA_WRAP(
 		  p_api_version_number  IN   NUMBER,
		  p_init_msg_list       IN   VARCHAR2 := null,
		  p_commit              IN   VARCHAR2 := null,
		  p_email_acct_id       IN   NUMBER,
		  p_action         		IN 	 VARCHAR2,
		  P_ACTIVE_FLAG    		IN 	 varchar2 := null,
		  P_USER_NAME 			IN 	 varchar2 := null,
		  P_USER_PASSWORD 		IN 	 varchar2 := null,
		  P_IN_HOST 			IN 	 varchar2 := null,
		  P_IN_PORT				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):='IS_DLPS_RUNNING';
Line: 145

	l_is_acct_updated     	   VARCHAR2(1);
Line: 166

  SAVEPOINT		UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 196

	        FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
Line: 209

	  l_is_acct_updated := 'Y';
Line: 211

      IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
                 	p_api_version_number  => P_Api_Version_Number,
                    p_init_msg_list       => FND_API.G_FALSE,
                	p_commit              => P_Commit,
            		p_email_acct_id       => p_email_acct_id,
					p_active_flag		  => p_active_flag,
					p_is_acct_update      => l_is_acct_updated,
                	x_return_status       => l_return_status,
                    x_msg_count           => x_msg_count,
                    x_msg_data            => x_msg_data
                );
Line: 227

   elsif ( p_action = 'update' ) then

      select count(*) into l_count from iem_mstemail_accounts where email_account_id=p_email_acct_id;
Line: 251

	  CHECK_IF_ACCOUNT_UPDATED (
	   						   		    p_api_version_number  => l_api_version_number,
                    					p_init_msg_list       => FND_API.G_FALSE,
                						p_commit              => P_Commit,
										p_email_account_id    => p_email_acct_id,
	  					   				P_ACTIVE_FLAG 		  => P_ACTIVE_FLAG,
		  								P_USER_NAME 		  => l_USER_NAME,
		  								P_USER_PASSWORD 	  => P_USER_PASSWORD,
						  				P_IN_HOST 			  => l_IN_HOST,
		  								P_IN_PORT 			  => l_IN_PORT,
										x_is_data_changed 	  => l_is_data_changed,
										x_is_acct_updated 	  => l_is_acct_updated,
               							x_return_status       => l_return_status,
                    					x_msg_count           => x_msg_count,
                    					x_msg_data            => x_msg_data
										 );
Line: 274

	       IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
                 	p_api_version_number  => P_Api_Version_Number,
                    p_init_msg_list       => FND_API.G_FALSE,
                	p_commit              => P_Commit,
            		p_email_acct_id       => p_email_acct_id,
					p_active_flag		  => p_active_flag,
					p_is_acct_update      => l_is_acct_updated,
                	x_return_status       => l_return_status,
                    x_msg_count           => x_msg_count,
                    x_msg_data            => x_msg_data
                );
Line: 291

   elsif ( p_action = 'delete' ) then

   		   l_active_flag := 'N';
Line: 294

		   l_is_acct_updated := 'N';
Line: 296

	       IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
                 	p_api_version_number  => P_Api_Version_Number,
                    p_init_msg_list       => FND_API.G_FALSE,
                	p_commit              => P_Commit,
            		p_email_acct_id       => p_email_acct_id,
					p_active_flag		  => l_active_flag,
					p_is_acct_update      => l_is_acct_updated,
                	x_return_status       => l_return_status,
                    x_msg_count           => x_msg_count,
                    x_msg_data            => x_msg_data
                );
Line: 333

        ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 342

            logMessage := '[input data is invalid, no data inserted to IEM_EMTA_CONFIG_PARAMS,return true!]';
Line: 343

            FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
Line: 347

        ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 356

            logMessage := '[Failed when calling IEM_UPDATE_DP_CONFIG_DATA!]';
Line: 357

            FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
Line: 361

        ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 371

            FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
Line: 374

		 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 383

	   ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 391

	ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
Line: 408

 END UPDATE_DP_CONFIG_DATA_WRAP;
Line: 411

PROCEDURE UPDATE_DP_CONFIG_DATA(
                 			p_api_version_number  IN   NUMBER,
 		  	     			p_init_msg_list       IN   VARCHAR2 := null,
		    	 			p_commit              IN   VARCHAR2 := null,
            				p_email_acct_id       IN   NUMBER,
							p_active_flag		  IN   VARCHAR2,
							p_is_acct_update      IN   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):='IS_DLPS_RUNNING';
Line: 431

	l_update_flag           VARCHAR2(1);
Line: 433

	l_has_updated           VARCHAR2(1);
Line: 440

  SAVEPOINT		UPDATE_DP_CONFIG_DATA_PVT;
Line: 463

   l_has_updated := 'N';
Line: 471

   l_update_flag := p_is_acct_update;
Line: 475

			select emta_config_param_id, action_type, email_account_id, account_update_flag
			from iem_emta_config_params where flag='N' and email_account_id=p_email_acct_id for update nowait
		)
    	LOOP

			 if x.account_update_flag = 'Y' then
		   	   	  update iem_emta_config_params set action_type = l_action,last_update_date = sysdate
		   		  where emta_config_param_id = x.emta_config_param_id;
Line: 484

				  l_has_updated := 'Y';
Line: 487

		   		  update iem_emta_config_params set action_type = l_action, account_update_flag=l_update_flag, last_update_date = sysdate
		   		  where emta_config_param_id = x.emta_config_param_id;
Line: 490

				  l_has_updated := 'Y';
Line: 505

	if l_has_updated = 'N' then
		   	select IEM_EMTA_CONFIG_PARAMS_S1.nextval into l_seq_id from dual;
Line: 509

			INSERT INTO IEM_EMTA_CONFIG_PARAMS
			(
	 		 EMTA_CONFIG_PARAM_ID,
	 		 EMAIL_ACCOUNT_ID,
	 		 ACTION_TYPE,
	 		 ACCOUNT_UPDATE_FLAG,
	 		 FLAG,
     		 CREATED_BY,
     		 CREATION_DATE,
     		 LAST_UPDATED_BY,
   	 		 LAST_UPDATE_DATE,
   	 		 LAST_UPDATE_LOGIN
			 )
			 VALUES
			 (
	 		 l_seq_id,
	 		 p_email_acct_id,
	 		 l_action,
	 		 l_update_flag,
	 		 'N',
			 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: 552

	ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
Line: 561

	   ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
Line: 569

	ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
Line: 586

 END UPDATE_DP_CONFIG_DATA;
Line: 637

		select emta_config_param_id, action_type, email_account_id, account_update_flag
		from iem_emta_config_params where flag='N' for update nowait
		)
    LOOP
	        update IEM_EMTA_CONFIG_PARAMS set FLAG='A', LAST_UPDATE_DATE=SYSDATE
	        where emta_config_param_id=x.emta_config_param_id;
Line: 644

			select count(*) into l_count
			from iem_mstemail_accounts where email_account_id=x.email_account_id;
Line: 648

			   delete IEM_EMTA_CONFIG_PARAMS where emta_config_param_id=x.emta_config_param_id;
Line: 651

				select * into account_rec
				from iem_mstemail_accounts where email_account_id=x.email_account_id;
Line: 656

				l_acct_info(Y).update_flag := x.account_update_flag;
Line: 703

PROCEDURE DELETE_ITEMS (
                 p_api_version_number  IN   NUMBER,
 		  	     p_init_msg_list       IN   VARCHAR2 := null,
		    	 p_commit              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(30):='GET_ACCOUNT_INFO';
Line: 745

	delete iem_emta_config_params where flag='A';
Line: 788

							 x_is_acct_updated 			OUT NOCOPY varchar2 )
return boolean
is
  l_data_changed boolean;
Line: 800

  l_is_acct_updated varchar2(1);
Line: 804

	 l_is_acct_updated := 'N';
Line: 806

	 --select active_flag, user_name, user_password, encrypt_key, in_host, in_port
	 select active_flag, user_name, in_host, in_port
	 into l_active_flag, l_user_name, l_in_host, l_in_port
	 from iem_mstemail_accounts where email_account_id = p_email_account_id ;
Line: 839

	 	l_is_acct_updated := 'Y';
Line: 843

	 x_is_acct_updated := l_is_acct_updated;
Line: 850

	 	x_is_acct_updated := l_is_acct_updated;
Line: 858

        if fnd_log.test(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP') then
            logMessage := '[input data is invalid, no data inserted to IEM_EMTA_CONFIG_PARAMS,return true!]';
Line: 861

            FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
Line: 870

PROCEDURE CHECK_IF_ACCOUNT_UPDATED(
 		  				  	 p_api_version_number  IN   NUMBER,
		  					 p_init_msg_list       IN   VARCHAR2 := null,
		  					 p_commit              IN   VARCHAR2 := null,
		  					 p_email_account_id 		IN number,
	  					 	 P_ACTIVE_FLAG 				IN varchar2,
		  					 P_USER_NAME 				IN varchar2,
		  					 P_USER_PASSWORD 			IN varchar2,
						  	 P_IN_HOST 					IN varchar2,
		  					 P_IN_PORT 					IN varchar2,
							 x_is_data_changed		OUT NOCOPY varchar2,
							 x_is_acct_updated 		OUT NOCOPY 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):='CHECK_IF_ACCOUNT_UPDATED';
Line: 902

  l_is_acct_updated varchar2(1);
Line: 910

  SAVEPOINT		CHECK_IF_ACCOUNT_UPDATED_PVT;
Line: 933

	 x_is_acct_updated := 'N';
Line: 937

	 l_is_acct_updated := 'N';
Line: 939

	 --select active_flag, user_name, user_password, encrypt_key, in_host, in_port
	 select active_flag, user_name,  in_host, in_port
	 into l_active_flag, l_user_name, l_in_host, l_in_port
	 from iem_mstemail_accounts where email_account_id = p_email_account_id ;
Line: 953

	 		l_is_acct_updated := 'Y';
Line: 980

	 		l_is_acct_updated := 'Y';
Line: 988

	 x_is_acct_updated := l_is_acct_updated;
Line: 1005

        ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
Line: 1015

            FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.CHECK_IF_ACCOUNT_UPDATED', logMessage);
Line: 1020

		 ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
Line: 1029

	   ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
Line: 1037

	ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
Line: 1054

 END CHECK_IF_ACCOUNT_UPDATED;