APPS.IEM_THEMES_PVT SQL Statements

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

Line: 5

/* 	05/07/02     chtang  added update last_update_date of keyword in calculate_weight */
/*      10/15/02     chtang  added update last_update_date of intent in calculate_weight */
/*****************************************************************************/
G_PKG_NAME CONSTANT varchar2(30) :='IEM_THEMES_PVT ';

Line: 20

         		p_LAST_UPDATED_BY  IN  NUMBER ,
          	p_LAST_UPDATE_DATE  IN  DATE,
          	p_LAST_UPDATE_LOGIN  IN  NUMBER ,
         		p_ATTRIBUTE1   IN VARCHAR2,
          	p_ATTRIBUTE2   IN VARCHAR2,
          	p_ATTRIBUTE3   IN VARCHAR2,
          	p_ATTRIBUTE4   IN VARCHAR2,
          	p_ATTRIBUTE5   IN VARCHAR2,
          	p_ATTRIBUTE6   IN VARCHAR2,
          	p_ATTRIBUTE7   IN VARCHAR2,
          	p_ATTRIBUTE8   IN VARCHAR2,
          	p_ATTRIBUTE9   IN VARCHAR2,
          	p_ATTRIBUTE10  IN  VARCHAR2,
          	p_ATTRIBUTE11  IN  VARCHAR2,
          	p_ATTRIBUTE12  IN  VARCHAR2,
          	p_ATTRIBUTE13  IN  VARCHAR2,
          	p_ATTRIBUTE14  IN  VARCHAR2,
          	p_ATTRIBUTE15  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):='create_item';

Line: 54

   SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
   and theme=p_theme and query_response=p_query_response;

Line: 58

     SELECT iem_themes_s1.nextval
     INTO l_seq_id
     FROM dual;

Line: 61

     INSERT INTO iem_themes (theme_id,
                    classification_id,
                    theme,
				score,
				query_response,
				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_classification_id,
				p_theme,
				p_score,
				p_query_response,
			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: 118

PROCEDURE delete_item (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
				 p_theme_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: 132

SAVEPOINT		delete_item_PVT;

Line: 149

   DELETE FROM IEM_THEMES WHERE THEME_ID = p_theme_id;

Line: 162

	ROLLBACK TO delete_item_PVT;

Line: 169

	ROLLBACK TO delete_item_PVT;

Line: 176

	ROLLBACK TO delete_item_PVT;

Line: 192

 PROCEDURE update_item (p_api_version_number    IN   NUMBER,
 		  	      p_init_msg_list  IN   VARCHAR2 ,
		    	      p_commit	    IN   VARCHAR2 ,
				 p_theme_id IN NUMBER,
				 p_classification_id	IN   NUMBER,
		           p_theme IN VARCHAR2 ,
				 p_score IN NUMBER,
		           p_query_response  IN VARCHAR2,
         		p_LAST_UPDATED_BY  IN  NUMBER ,
          	p_LAST_UPDATE_DATE  IN  DATE,
          	p_LAST_UPDATE_LOGIN  IN  NUMBER ,
         		p_ATTRIBUTE1   IN VARCHAR2,
          	p_ATTRIBUTE2   IN VARCHAR2,
          	p_ATTRIBUTE3   IN VARCHAR2,
          	p_ATTRIBUTE4   IN VARCHAR2,
          	p_ATTRIBUTE5   IN VARCHAR2,
          	p_ATTRIBUTE6   IN VARCHAR2,
          	p_ATTRIBUTE7   IN VARCHAR2,
          	p_ATTRIBUTE8   IN VARCHAR2,
          	p_ATTRIBUTE9   IN VARCHAR2,
          	p_ATTRIBUTE10  IN  VARCHAR2,
          	p_ATTRIBUTE11  IN  VARCHAR2,
          	p_ATTRIBUTE12  IN  VARCHAR2,
          	p_ATTRIBUTE13  IN  VARCHAR2,
          	p_ATTRIBUTE14  IN  VARCHAR2,
          	p_ATTRIBUTE15  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):='update_item';

Line: 229

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

Line: 230

     l_LAST_UPDATE_DATE    DATE:=SYSDATE;

Line: 231

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

Line: 238

   SAVEPOINT		update_item_PVT;

Line: 255

   update IEM_THEMES SET
   classification_id = decode( p_classification_id, FND_API.G_MISS_NUM,null,null, classification_id, p_classification_id),
   theme =  decode(p_theme,FND_API.G_MISS_CHAR,null,null,theme,p_theme),
   score =  decode(p_score,FND_API.G_MISS_CHAR,null,null,score,p_score),
   query_response = decode( p_query_response, FND_API.G_MISS_CHAR,null,null, query_response, p_query_response),
          LAST_UPDATE_DATE = sysdate,
         LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,last_updated_by,l_LAST_UPDATED_BY),
      LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,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 theme_id = p_theme_id;

Line: 290

	ROLLBACK TO update_item_PVT;

Line: 297

	ROLLBACK TO update_item_PVT;

Line: 304

     ROLLBACK TO update_item_PVT;

Line: 316

	ROLLBACK TO update_item_PVT;

Line: 373

   select replace (replace ( replace (p_theme, '<', ''), '>', ''), '"', '''') into l_theme
   from dual;

Line: 379

   SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
   and theme=p_theme and query_response=p_query_response;

Line: 396

    	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 =>x_return_status,
                             x_msg_count   => x_msg_count,
                             x_msg_data => x_msg_data);

Line: 504

   select replace (replace ( replace (p_theme, '<', ''), '>', ''), '"', '''') into l_theme
   from dual;

Line: 510

   SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id
   and theme=p_theme and query_response=p_query_response;

Line: 527

    	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 =>x_return_status,
                             x_msg_count   => x_msg_count,
                             x_msg_data => x_msg_data);

Line: 597

PROCEDURE delete_item_wrap
     (p_api_version_number     IN  NUMBER,
      P_init_msg_list   IN  VARCHAR2 ,
      p_commit          IN  VARCHAR2 ,
      p_thes_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: 607

    l_api_name		varchar2(30):='delete_item_batch';

Line: 612

    SAVEPOINT delete_item_wrap;

Line: 632

        DELETE
        FROM IEM_THEMES
        WHERE theme_id = p_thes_ids_tbl(i);

Line: 654

	ROLLBACK TO delete_item_wrap;

Line: 661

	ROLLBACK TO delete_item_wrap;

Line: 668

	ROLLBACK TO delete_item_wrap;

Line: 685

END delete_item_wrap;

Line: 686

PROCEDURE delete_item_wrap_sss
     (p_api_version_number     IN  NUMBER,
      P_init_msg_list   IN  VARCHAR2 ,
      p_commit          IN  VARCHAR2 ,
      p_thes_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: 696

    l_api_name		varchar2(30):='delete_item_batch';

Line: 704

    SAVEPOINT delete_item_wrap;

Line: 724

    select classification_id into l_class_id
    from iem_themes
    where theme_id=p_thes_ids_tbl(j);

Line: 730

        DELETE
        FROM IEM_THEMES
        WHERE theme_id = p_thes_ids_tbl(i);

Line: 734

	delete from iem_theme_docs where theme_id not in
	(select theme_id from iem_themes);

Line: 736

	delete from iem_account_intent_docs where account_intent_doc_id
	not in (select account_intent_doc_id from iem_theme_docs);

Line: 738

		select email_account_id into l_email_account_id
		from iem_classifications
		where classification_id=l_class_id;

Line: 766

	ROLLBACK TO delete_item_wrap;

Line: 773

	ROLLBACK TO delete_item_wrap;

Line: 780

	ROLLBACK TO delete_item_wrap;

Line: 797

END delete_item_wrap_sss;

Line: 799

PROCEDURE update_item_wrap (p_api_version_number    IN   NUMBER,
 		      p_init_msg_list  IN   VARCHAR2 ,
		      p_commit	    IN   VARCHAR2 ,
                p_theme_id IN NUMBER,
                p_classification_id     IN   NUMBER,
                p_theme IN VARCHAR2 ,
                p_score IN NUMBER,
                p_query_response  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):='update_item';

Line: 815

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

Line: 816

     l_LAST_UPDATE_DATE    DATE:=SYSDATE;

Line: 817

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

Line: 824

SAVEPOINT		update_item_PVT;

Line: 841

  select replace (replace ( replace (p_theme, '<', ''), '>', ''), '"', '''') into l_theme
	from dual;

Line: 848

  SELECT COUNT(*) INTO l_cnt from IEM_THEMES WHERE classification_id=p_classification_id and theme=l_theme2 and query_response=p_query_response and NOT theme_id=p_theme_id;

Line: 852

 IEM_THEMES_PVT.update_item(
                           p_api_version_number =>p_api_version_number,
                           p_init_msg_list => p_init_msg_list,
                           p_commit => p_commit,
                           p_theme_id => p_theme_id,
                           p_classification_id  => p_classification_id,
                           p_theme => l_theme2,
                           p_score => p_score,
                           p_query_response => p_query_response,
    	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 =>x_return_status,
                           x_msg_count   => x_msg_count,
                           x_msg_data => x_msg_data);

Line: 895

      ROLLBACK TO update_item_PVT;

Line: 903

	ROLLBACK TO update_item_PVT;

Line: 910

	ROLLBACK TO update_item_PVT;

Line: 917

	ROLLBACK TO update_item_PVT;

Line: 940

               p_LAST_UPDATED_BY    NUMBER,
               p_LAST_UPDATE_DATE    DATE,
               p_LAST_UPDATE_LOGIN    NUMBER,
		  x_return_status OUT NOCOPY VARCHAR2,
  		    x_msg_count	      OUT NOCOPY NUMBER,
	  	    x_msg_data OUT NOCOPY VARCHAR2) IS

	l_cnt 	NUMBER;

Line: 963

	select nvl(sum(doc_count),0)+1 into l_cnt
	from iem_themes
	where query_response=p_query_response
	and classification_id=p_classification_id
	and theme=p_theme;

Line: 968

	select email_account_id into l_email_account_id
	from iem_classifications
	where classification_id=p_classification_id;

Line: 972

     SELECT iem_themes_s1.nextval
     INTO l_seq_id
     FROM dual;

Line: 975

     INSERT INTO iem_themes (theme_id,
                    classification_id,
                    theme,
				score,
				query_response,
				created_by,
				creation_date,
				last_updated_by,
				last_update_date,
				last_update_login,
				doc_count)
		VALUES
				(l_seq_id,
				p_classification_id,
				p_theme,
				0,
				p_query_response,
     			p_created_by,
				p_CREATION_DATE,
     			p_LAST_UPDATED_BY,
     			p_LAST_UPDATE_DATE,
     			p_LAST_UPDATE_LOGIN,
				l_cnt);

Line: 1005

			update iem_themes
			set doc_count=l_cnt
			where query_response=p_query_response
			and classification_id=p_classification_id
			and theme=p_theme;

Line: 1010

		select theme_id into l_theme_id
		from iem_themes
		where query_response=p_query_response
		and classification_id=p_classification_id
		and theme=p_theme;

Line: 1046

		select a.theme_id,a.classification_id,
		a.theme,a.query_response,a.score
		from iem_themes a,iem_classifications b
		where a.classification_id=b.classification_id
		and b.email_account_id=p_email_account_id
		and a.query_response=p_query_response;

Line: 1053

 cursor c1 is select a.classification_id,sum(power(a.score,2)) score
		from iem_themes a,iem_classifications b
		where a.classification_id=b.classification_id
		and b.email_account_id=p_email_account_id
		and a.query_response=p_query_response
		group by a.classification_id;

Line: 1060

 cursor c_calc is select a.theme_id,a.score
		from iem_themes a,iem_classifications b
		where a.classification_id=b.classification_id
		and b.email_account_id=p_email_account_id
		and a.query_response=p_query_response
		and a.classification_id=l_class_id;

Line: 1071

	select count(*)
	into l_N
	from iem_account_intent_docs
	where email_account_id=p_email_account_id
	and query_response=p_query_response;

Line: 1082

	select count(*)
	into l_R
	from iem_account_intent_docs
	where classification_id=v1.classification_id
	and query_response=v1.query_response;

Line: 1090

	select nvl(sum(a.doc_count),0) into l_Nt
	from iem_themes A,iem_classifications B
	where A.classification_id=B.classification_id
	and B.email_account_id=p_email_account_id
	and A.query_response=v1.query_response
	and A.theme=v1.theme;

Line: 1099

	select nvl(sum(doc_count),0) into l_Rt
	from iem_themes
	where query_response=v1.query_response
	and classification_id=v1.classification_id
	and theme=v1.theme;

Line: 1108

	update iem_themes
	set score=l_weight
	where theme_id=v1.theme_id;

Line: 1115

		update iem_classifications
		set last_update_date=sysdate
		where classification_id=v1.classification_id;

Line: 1119

		update iem_themes
		set score=round(v2.score/sqrt(v1.score),2),
		last_update_date=sysdate
		where theme_id=v2.theme_id;