The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Fixed Bug 1339176 rtripath on 11/27/00 Do the cascading delete */
/* 08/14/01 chtang added create_item_wrap_sss() for 11.5.6 */
/* 06/05/02 chtang fixed 2403484
/* 11/20/02 chtang removed SQL%NOTFOUND in delete_item_wrap_sss
/*****************************************************************/
G_PKG_NAME CONSTANT varchar2(30) :='IEM_CLASSIFICATIONS_PVT';
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';
SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
CLASSIFICATION=p_classification AND rownum=1;
select iem_classifications_s1.nextval into l_seq from dual;
INSERT INTO IEM_CLASSIFICATIONS
(
CLASSIFICATION_ID,
EMAIL_ACCOUNT_ID ,
CLASSIFICATION ,
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,
p_email_account_id,
p_classification,
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);
ROLLBACK TO update_item_PVT;
PROCEDURE delete_item (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_email_account_id in number,
p_classification IN VARCHAR2 ,
p_classification_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';
SAVEPOINT delete_item_PVT;
DELETE FROM IEM_THEMES
WHERE CLASSIFICATION_ID=p_classification_id;
DELETE FROM IEM_CLASSIFICATIONS
WHERE CLASSIFICATION_ID=p_classification_id;
DELETE FROM IEM_CLASSIFICATIONS
WHERE EMAIL_ACCOUNT_ID=P_EMAIL_ACCOUNT_ID
AND CLASSIFICATION=P_CLASSIFICATION;
ROLLBACK TO delete_item_PVT;
ROLLBACK TO delete_item_PVT;
ROLLBACK TO delete_item_PVT;
PROCEDURE update_item (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_classification_id IN NUMBER,
p_email_account_id IN NUMBER,
p_classification IN VARCHAR2,
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';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SAVEPOINT update_item_PVT;
UPDATE IEM_CLASSIFICATIONS
SET CLASSIFICATION=decode(p_classification,FND_API.G_MISS_CHAR,null,null,CLASSIFICATION,p_CLASSIFICATION),
LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
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),
EMAIL_ACCOUNT_ID =decode(p_email_account_id,FND_API.G_MISS_NUM,EMAIL_ACCOUNT_ID,p_email_account_id),
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 CLASSIFICATION_ID=p_classification_id;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
CLASSIFICATION=p_classification AND rownum=1;
select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
from dual;
select replace (replace ( replace (p_classification, '<', '<'), '>', '>'), '"', '''')
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);
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
CLASSIFICATION=p_classification AND rownum=1;
select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
from dual;
select replace (replace ( replace (p_classification, '<', '<'), '>', '>'), '"', '''')
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);
PROCEDURE delete_item_wrap
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_clas_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;
l_api_name varchar2(30):='delete_item_batch';
SAVEPOINT delete_item_wrap;
DELETE
FROM IEM_THEMES
WHERE classification_id = p_clas_ids_tbl(i);
DELETE
FROM IEM_CLASSIFICATIONS
WHERE classification_id = p_clas_ids_tbl(i);
ROLLBACK TO delete_item_wrap;
ROLLBACK TO delete_item_wrap;
ROLLBACK TO delete_item_wrap;
END delete_item_wrap;
PROCEDURE delete_item_wrap_sss
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_clas_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;
l_api_name varchar2(30):='delete_item_batch_sss';
SAVEPOINT delete_item_wrap;
select email_account_id into l_email_account_id
from iem_classifications
where classification_id=l_class_id;
DELETE
FROM IEM_THEMES
WHERE classification_id = p_clas_ids_tbl(i);
DELETE
FROM IEM_CLASSIFICATIONS
WHERE classification_id = p_clas_ids_tbl(i);
delete from iem_account_intent_docs
where classification_id not in
(select classification_id from iem_classifications);
delete from iem_theme_docs
where account_intent_doc_id not in
(select account_intent_doc_id from iem_account_intent_docs);
ROLLBACK TO delete_item_wrap;
ROLLBACK TO delete_item_wrap;
ROLLBACK TO delete_item_wrap;
END delete_item_wrap_sss;
PROCEDURE update_item_wrap (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_classification_id IN NUMBER,
p_email_account_id IN NUMBER,
p_classification 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';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SAVEPOINT update_item_PVT;
select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
from dual;
SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
CLASSIFICATION=l_classification2 AND rownum=1;
IEM_CLASSIFICATIONS_PVT.update_item(
p_api_version_number =>p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_classification_id =>p_classification_id,
p_email_account_id =>p_email_account_id,
p_classification => l_classification,
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);
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
PROCEDURE update_item_wrap_sss (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_classification_id IN NUMBER,
p_email_account_id IN NUMBER,
p_classification 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';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SAVEPOINT update_item_PVT;
select replace (replace ( replace (p_classification, '<', ''), '>', ''), '"', '''') into l_classification
from dual;
SELECT count(*) into l_cnt from iem_classifications WHERE EMAIL_ACCOUNT_ID=p_email_account_id AND
upper(CLASSIFICATION)=upper(l_classification2) AND rownum=1;
select email_account_id into l_email_account_id
from iem_classifications
where classification_id=p_classification_id;
IEM_CLASSIFICATIONS_PVT.update_item(
p_api_version_number =>p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_classification_id =>p_classification_id,
p_email_account_id =>p_email_account_id,
p_classification => l_classification,
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);
update iem_account_intent_docs
set email_account_id=p_email_account_id
where classification_id=p_classification_id;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
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;
SELECT classification_id into l_class_id
from iem_classifications
WHERE EMAIL_ACCOUNT_ID=p_email_account_id
AND upper(CLASSIFICATION)=upper(p_classification) ;
select iem_classifications_s1.nextval into l_seq from dual;
INSERT INTO IEM_CLASSIFICATIONS
(
CLASSIFICATION_ID,
EMAIL_ACCOUNT_ID ,
CLASSIFICATION ,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(l_seq,
p_email_account_id,
p_classification,
p_CREATED_BY,
p_creation_date,
p_LAST_UPDATED_BY,
p_LAST_UPDATE_DATE,
p_LAST_UPDATE_LOGIN);