APPS.IEM_EMAILPROC_HDL_PVT SQL Statements

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

Line: 17

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

	SELECT IEM_ACCOUNT_EMAILPROCS_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 90

	INSERT INTO IEM_ACCOUNT_EMAILPROCS
	(
	EMAILPROC_ID,
	EMAIL_ACCOUNT_ID,
    ACCOUNT_EMAILPROC_ID,
	ENABLED_FLAG,
    PRIORITY,
	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
   (
   p_emailproc_id,
   p_email_account_id,
   l_seq_id,
   p_enabled_flag,
   p_priority,
   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: 204

PROCEDURE update_item_emailproc (
                 p_api_version_number    IN   NUMBER,
    	  	     p_init_msg_list    IN   VARCHAR2 := null,
    	    	 p_commit	        IN   VARCHAR2 := null,
    			 p_emailproc_id     IN   NUMBER,
    			 p_name             IN   VARCHAR2:= null,
                 p_description	    IN   VARCHAR2:= null,
                 p_ruling_chain	    IN   VARCHAR2:= null,
                 p_all_email	    IN   VARCHAR2:= null,
                 p_rule_type	    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_emailproc';

Line: 232

  SAVEPOINT		update_item_route;

Line: 255

    select count(*) into l_name_count
    from iem_emailprocs
    where UPPER(name) = UPPER(p_name) and rule_type=p_rule_type and emailproc_id <> p_emailproc_id;

Line: 303

	update IEM_EMAILPROCS
	set
           name=decode(p_name,null,name,p_name),
	       description=decode(p_description,FND_API.G_MISS_CHAR,null,null,description,p_description),
	       boolean_type_code=decode(p_ruling_chain,null,boolean_type_code,p_ruling_chain),
           all_email=decode(p_all_email,null,all_email,p_all_email),
           rule_type=decode(p_rule_type,null, rule_type, p_rule_type),
           LAST_UPDATED_BY = decode(G_created_updated_by,FND_API.G_MISS_CHAR,-1,G_created_updated_by),
           LAST_UPDATE_DATE = sysdate,
           LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,FND_API.G_MISS_CHAR,-1,G_LAST_UPDATE_LOGIN)

	where emailproc_id=p_emailproc_id;

Line: 330

	 ROLLBACK TO update_item_route;

Line: 335

	   ROLLBACK TO update_item_route;

Line: 342

	   ROLLBACK TO update_item_route;

Line: 349

	   ROLLBACK TO update_item_route;

Line: 356

	   ROLLBACK TO update_item_route;

Line: 366

	   ROLLBACK TO update_item_route;

Line: 376

	ROLLBACK TO update_item_route;

Line: 393

END	update_item_emailproc;

Line: 396

PROCEDURE update_item_rule (p_api_version_number    IN   NUMBER,
     	  	     p_init_msg_list            IN   VARCHAR2 := null,
    	    	 p_commit	                IN   VARCHAR2 := null,
                 p_emailproc_rule_id        IN   NUMBER,
      			 p_key_type_code            IN   VARCHAR2:= null,
      			 p_operator_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_rule';

Line: 412

     IEM_NO_RULE_UPDATE     EXCEPTION;

Line: 418

  SAVEPOINT		update_item_rule;

Line: 439

  select count(*) into l_rule from iem_emailproc_rules
  where emailproc_rule_id = p_emailproc_rule_id;

Line: 443

    raise IEM_NO_RULE_UPDATE;

Line: 470

	update IEM_EMAILPROC_RULES
	set
           key_type_code=decode(p_key_type_code,null,key_type_code,p_key_type_code),
	       operator_type_code=decode(p_operator_type_code,null,operator_type_code,p_operator_type_code),
	       value=decode(p_value,null,value,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 emailproc_rule_id=p_emailproc_rule_id;

Line: 491

    WHEN IEM_NO_RULE_UPDATE THEN
    	   ROLLBACK TO update_item_rule;

Line: 493

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

Line: 500

    	   ROLLBACK TO update_item_rule;

Line: 508

    	   ROLLBACK TO update_item_rule;

Line: 515

	ROLLBACK TO update_item_rule;

Line: 524

	ROLLBACK TO update_item_rule;

Line: 532

	ROLLBACK TO update_item_rule;

Line: 550

 END	update_item_rule;

Line: 555

PROCEDURE update_account_emailprocs
                (p_api_version_number       IN  NUMBER,
 	  	        p_init_msg_list             IN  VARCHAR2 := null,
	    	    p_commit	                IN  VARCHAR2 := null,
                p_emailproc_id              IN  NUMBER,
			    p_email_account_id          IN  NUMBER,
  			    p_enabled_flag	            IN  VARCHAR2:= NULL,
  			    p_priority	                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_account_emailprocs';

Line: 571

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

Line: 572

     l_LAST_UPDATE_DATE    DATE:=SYSDATE;

Line: 573

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

Line: 578

SAVEPOINT		update_account_emailprocs_PVT;

Line: 599

	Select count(*) into l_emailproc_cnt from iem_emailprocs
	where emailproc_id=p_emailproc_id;

Line: 608

	Select count(*) into l_acct_cnt from iem_mstemail_accounts
	where email_account_id=p_email_account_id;

Line: 617

 	    update IEM_ACCOUNT_EMAILPROCS
        set
	       priority=decode(p_priority,null,priority,p_priority),
           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 emailproc_id = p_emailproc_id and email_account_id = p_email_account_id;

Line: 625

	    update IEM_ACCOUNT_EMAILPROCS
        set
	       enabled_flag='N',
	       priority=decode(p_priority,null,priority,p_priority),
           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 emailproc_id = p_emailproc_id and email_account_id = p_email_account_id;

Line: 634

	    update IEM_ACCOUNT_EMAILPROCS
        set
  	       enabled_flag=p_enabled_flag,
	       priority=decode(p_priority,null,priority,p_priority),
           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 emailproc_id = p_emailproc_id and email_account_id = p_email_account_id;

Line: 662

	ROLLBACK TO update_account_emailprocs_PVT;

Line: 670

	ROLLBACK TO update_account_emailprocs_PVT;

Line: 678

	ROLLBACK TO update_account_emailprocs_PVT;

Line: 696

 END	update_account_emailprocs;

Line: 761

    select count(*) into l_name_count from iem_emailprocs where rule_type=p_rule_type and UPPER(name) = UPPER(p_name);

Line: 801

   	SELECT IEM_EMAILPROCS_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 805

	INSERT INTO IEM_EMAILPROCS
	(
	EMAILPROC_ID,
	NAME,
	DESCRIPTION,
	BOOLEAN_TYPE_CODE,
    ALL_EMAIL,
    RULE_TYPE,
	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_name,
	l_description,
	p_boolean_type_code,
    l_all_email,
    P_RULE_TYPE,
    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: 997

   	SELECT IEM_EMAILPROC_RULES_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 1001

	INSERT INTO IEM_EMAILPROC_RULES
	(
	EMAILPROC_RULE_ID,
	EMAILPROC_ID,
	KEY_TYPE_CODE,
	OPERATOR_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,
	p_emailproc_id,
	p_key_type_code,
	p_operator_type_code,
    p_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: 1148

	SELECT IEM_ACTIONS_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 1152

	INSERT INTO IEM_ACTIONS
	(
    ACTION_ID,
	EMAILPROC_ID,
	ACTION,
	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_emailproc_id,
   p_action_name,
   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: 1304

	SELECT IEM_ACTION_DTLS_s1.nextval
	INTO l_seq_id
	FROM dual;

Line: 1308

	INSERT INTO IEM_ACTION_DTLS
	(
    ACTION_DTL_ID,
	ACTION_ID,
	PARAMETER1,
    PARAMETER2,
    PARAMETER_TAG,
	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,
        PARAMETER3
	)
   VALUES
   (
   l_seq_id,
   p_action_id,
   p_param1,
   p_param2,
   p_param_tag,
   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),
   p_param3
	);

Line: 1424

PROCEDURE delete_acct_emailproc_batch
     (p_api_version_number      IN  NUMBER,
      P_init_msg_list           IN  VARCHAR2 := null,
      p_commit                  IN  VARCHAR2 := null,
      p_emailproc_ids_tbl       IN  jtf_varchar2_Table_100,
      p_account_id              IN NUMBER,
      p_rule_type               IN VARCHAR2,
      x_return_status   OUT NOCOPY VARCHAR2,
      x_msg_count       OUT NOCOPY NUMBER,
      x_msg_data        OUT NOCOPY VARCHAR2)
IS
    i       INTEGER;

Line: 1436

    l_api_name		varchar2(30):='delete_acct_emailproc_batch';

Line: 1444

SAVEPOINT delete_acct_emailproc_batch;

Line: 1471

        Update iem_account_emailprocs set priority=priority-1
        where email_account_id=p_account_id
            and priority >
               (Select priority from iem_account_emailprocs
			    where emailproc_id=p_emailproc_ids_tbl(i)
                and  email_account_id=p_account_id)
            and emailproc_id in
                ( select emailproc_id from iem_emailprocs
                where rule_type= p_rule_type);

Line: 1481

        DELETE
        FROM IEM_ACCOUNT_EMAILPROCS
        WHERE emailproc_id = p_emailproc_ids_tbl(i) and email_account_id = p_account_id;

Line: 1503

	   ROLLBACK TO delete_acct_emailproc_batch;

Line: 1510

	   ROLLBACK TO delete_acct_emailproc_batch;

Line: 1516

	  ROLLBACK TO delete_acct_emailproc_batch;

Line: 1525

END delete_acct_emailproc_batch;