[Home] [Help]
PACKAGE BODY: APPS.IEM_ACT_CATG_PVT
Source
1 PACKAGE BODY IEM_ACT_CATG_PVT as
2 /* $Header: iemvacab.pls 115.1 2002/12/05 20:09:34 sboorela shipped $*/
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_ACT_CATG_PVT ';
4
5 PROCEDURE create_item (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2 ,
7 p_commit IN VARCHAR2 ,
8 p_email_account_id IN NUMBER,
9 p_cat_tbl IN jtf_varchar2_Table_100,
10 p_CREATED_BY NUMBER,
11 p_CREATION_DATE DATE,
12 p_LAST_UPDATED_BY NUMBER,
13 p_LAST_UPDATE_DATE DATE,
14 p_LAST_UPDATE_LOGIN NUMBER,
15 p_ATTRIBUTE1 VARCHAR2,
16 p_ATTRIBUTE2 VARCHAR2,
17 p_ATTRIBUTE3 VARCHAR2,
18 p_ATTRIBUTE4 VARCHAR2,
19 p_ATTRIBUTE5 VARCHAR2,
20 p_ATTRIBUTE6 VARCHAR2,
21 p_ATTRIBUTE7 VARCHAR2,
22 p_ATTRIBUTE8 VARCHAR2,
23 p_ATTRIBUTE9 VARCHAR2,
24 p_ATTRIBUTE10 VARCHAR2,
25 p_ATTRIBUTE11 VARCHAR2,
26 p_ATTRIBUTE12 VARCHAR2,
27 p_ATTRIBUTE13 VARCHAR2,
28 p_ATTRIBUTE14 VARCHAR2,
29 p_ATTRIBUTE15 VARCHAR2,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2
33 ) is
34 l_api_name VARCHAR2(255):='create_item';
35 l_api_version_number NUMBER:=1.0;
36 l_cat_id number;
37 l_acct_cat_seq number;
38
39 BEGIN
40 -- Standard call to check for call compatibility.
41 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
42 p_api_version_number,
43 l_api_name,
44 G_PKG_NAME)
45 THEN
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48 x_return_status := FND_API.G_RET_STS_SUCCESS;
49 delete from iem_account_categories
50 where email_account_id=p_email_account_id;
51 IF p_cat_tbl.count>0 THEN
52 FOR i in p_cat_tbl.FIRST..p_cat_tbl.LAST LOOP
53 l_cat_id:=to_number(p_cat_tbl(i));
54 select IEM_ACCOUNT_CATEGORIES_S1.nextval into
55 l_acct_cat_seq
56 from dual;
57 INSERT INTO IEM_ACCOUNT_CATEGORIES (
58 ACCOUNT_CATEGORY_ID,
59 EMAIL_ACCOUNT_ID ,
60 CATEGORY_ID ,
61 CREATED_BY ,
62 CREATION_DATE ,
63 LAST_UPDATED_BY ,
64 LAST_UPDATE_DATE ,
65 LAST_UPDATE_LOGIN ,
66 ATTRIBUTE1 ,
67 ATTRIBUTE2 ,
68 ATTRIBUTE3 ,
69 ATTRIBUTE4 ,
70 ATTRIBUTE5 ,
71 ATTRIBUTE6 ,
72 ATTRIBUTE7 ,
73 ATTRIBUTE8 ,
74 ATTRIBUTE9 ,
75 ATTRIBUTE10 ,
76 ATTRIBUTE11 ,
77 ATTRIBUTE12 ,
78 ATTRIBUTE13 ,
79 ATTRIBUTE14 ,
80 ATTRIBUTE15
81 )
82 VALUES
83 (
84 l_acct_cat_seq,
85 p_email_account_id,
86 l_cat_id,
87 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
88 sysdate,
89 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
90 sysdate,
91 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
92 p_ATTRIBUTE1,
93 p_ATTRIBUTE2,
94 p_ATTRIBUTE3,
95 p_ATTRIBUTE4,
96 p_ATTRIBUTE5,
97 p_ATTRIBUTE6,
98 p_ATTRIBUTE7,
99 p_ATTRIBUTE8,
100 p_ATTRIBUTE9,
101 p_ATTRIBUTE10,
102 p_ATTRIBUTE11,
103 p_ATTRIBUTE12,
104 p_ATTRIBUTE13,
105 p_ATTRIBUTE14,
106 p_ATTRIBUTE15
107 );
108 END LOOP;
109 END IF;
110 -- Standard Check Of p_commit.
111 IF FND_API.To_Boolean(p_commit) THEN
112 COMMIT WORK;
113 END IF;
114 -- Standard callto get message count and if count is 1, get message info.
115 FND_MSG_PUB.Count_And_Get
116 ( p_count => x_msg_count,
117 p_data => x_msg_data
118 );
119 EXCEPTION
120 WHEN FND_API.G_EXC_ERROR THEN
121 x_return_status := FND_API.G_RET_STS_ERROR ;
122 FND_MSG_PUB.Count_And_Get
123 ( p_count => x_msg_count,
124 p_data => x_msg_data
125 );
126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
128 FND_MSG_PUB.Count_And_Get
129 ( p_count => x_msg_count,
130 p_data => x_msg_data
131 );
132 WHEN OTHERS THEN
133 x_return_status := FND_API.G_RET_STS_ERROR;
134 IF FND_MSG_PUB.Check_Msg_Level
135 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
136 THEN
137 FND_MSG_PUB.Add_Exc_Msg
138 ( G_PKG_NAME ,
139 l_api_name
140 );
141 END IF;
142 FND_MSG_PUB.Count_And_Get
143 ( p_count => x_msg_count ,
144 p_data => x_msg_data
145 );
146
147 END create_item;
148
149 PROCEDURE select_item (p_api_version_number IN NUMBER,
150 p_init_msg_list IN VARCHAR2 ,
151 p_commit IN VARCHAR2 ,
152 p_email_account_id in number,
153 x_category_tbl OUT NOCOPY jtf_varchar2_TABLE_100,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2
157 ) is
158 l_api_name VARCHAR2(255):='select_item';
159 l_api_version_number NUMBER:=1.0;
160 cursor c1 is select category_id from iem_account_categories
161 where email_account_id=p_email_account_id;
162 BEGIN
163 -- Standard call to check for call compatibility.
164 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
165 p_api_version_number,
166 l_api_name,
167 G_PKG_NAME)
168 THEN
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
170 END IF;
171 SAVEPOINT select_item_pvt;
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173 open c1;
174 fetch c1 bulk collect into x_category_tbl;
175 close c1;
176
177 -- Standard Check Of p_commit.
178 IF FND_API.To_Boolean(p_commit) THEN
179 COMMIT WORK;
180 END IF;
181 -- Standard callto get message count and if count is 1, get message info.
182 FND_MSG_PUB.Count_And_Get
183 ( p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186 EXCEPTION
187 WHEN FND_API.G_EXC_ERROR THEN
188 ROLLBACK TO select_item_pvt;
189 x_return_status := FND_API.G_RET_STS_ERROR ;
190 FND_MSG_PUB.Count_And_Get
191 ( p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 ROLLBACK TO select_item_pvt;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201 WHEN OTHERS THEN
202 ROLLBACK TO select_item_pvt;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 IF FND_MSG_PUB.Check_Msg_Level
205 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
206 THEN
207 FND_MSG_PUB.Add_Exc_Msg
208 ( G_PKG_NAME ,
209 l_api_name
210 );
211 END IF;
212 FND_MSG_PUB.Count_And_Get
213 ( p_count => x_msg_count ,
214 p_data => x_msg_data
215 );
216
217 END select_item;
218 PROCEDURE create_item_wrap (p_api_version_number IN NUMBER,
219 p_init_msg_list IN VARCHAR2 ,
220 p_commit IN VARCHAR2 ,
221 p_email_account_id IN NUMBER,
222 p_cat_tbl IN jtf_varchar2_Table_100,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2
226 ) IS
227 l_api_name VARCHAR2(255):='create_item_wrap';
228 l_api_version_number NUMBER:=1.0;
229 l_return_status VARCHAR2(20);
230 l_msg_count NUMBER := 0;
231 l_msg_data VARCHAR2(2000);
232 CREATE_ITEM_EXCP EXCEPTION;
233 BEGIN
234 -- Standard Start of API savepoint
235 SAVEPOINT create_item_wrap_PVT;
236
237 -- Standard call to check for call compatibility.
238 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
239 p_api_version_number,
240 l_api_name,
241 G_PKG_NAME)
242 THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 END IF;
245
246 -- Initialize message list if p_init_msg_list is set to TRUE.
247 IF FND_API.to_Boolean( p_init_msg_list )
248 THEN
249 FND_MSG_PUB.initialize;
250 END IF;
251
252 -- Initialize API return status to SUCCESS
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254 IEM_ACT_CATG_PVT.create_item (p_api_version_number=>1.0,
255 p_init_msg_list=>'F' ,
256 p_commit=>'F' ,
257 p_email_account_id=>p_email_account_id,
258 p_cat_tbl=>p_cat_tbl ,
259 p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
260 p_CREATION_DATE =>SYSDATE,
261 p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
262 p_LAST_UPDATE_DATE =>SYSDATE,
263 p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
264 p_ATTRIBUTE1 =>null,
265 p_ATTRIBUTE2 =>null,
266 p_ATTRIBUTE3 =>null,
267 p_ATTRIBUTE4 =>null,
268 p_ATTRIBUTE5 =>null,
269 p_ATTRIBUTE6 =>null,
270 p_ATTRIBUTE7 =>null,
271 p_ATTRIBUTE8 =>null,
272 p_ATTRIBUTE9 =>null,
273 p_ATTRIBUTE10 =>null,
274 p_ATTRIBUTE11 =>null,
275 p_ATTRIBUTE12 =>null,
276 p_ATTRIBUTE13 =>null,
277 p_ATTRIBUTE14 =>null,
278 p_ATTRIBUTE15 =>null,
279 x_return_status=>l_return_status,
280 x_msg_count=>l_msg_count,
281 x_msg_data=>l_msg_data);
282 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
283 raise CREATE_ITEM_EXCP;
284 x_return_status:=FND_API.G_RET_STS_SUCCESS;
285 end if;
286 -- Standard Check Of p_commit.
287 IF FND_API.To_Boolean(p_commit) THEN
288 COMMIT WORK;
289 END IF;
290 EXCEPTION
291 WHEN CREATE_ITEM_EXCP THEN
292 ROLLBACK TO CREATE_ITEM_WRAP_PVT;
293 x_return_status := FND_API.G_RET_STS_ERROR ;
294 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
295 WHEN FND_API.G_EXC_ERROR THEN
296 ROLLBACK TO CREATE_ITEM_WRAP_PVT;
297 x_return_status := FND_API.G_RET_STS_ERROR ;
298 FND_MSG_PUB.Count_And_Get
299 ( p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
303 ROLLBACK TO CREATE_ITEM_WRAP_PVT;
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
305 FND_MSG_PUB.Count_And_Get
306 ( p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309 WHEN OTHERS THEN
310 ROLLBACK TO CREATE_ITEM_WRAP_PVT;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 IF FND_MSG_PUB.Check_Msg_Level
313 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
314 THEN
315 FND_MSG_PUB.Add_Exc_Msg
316 ( G_PKG_NAME ,
317 l_api_name
318 );
319 END IF;
320 FND_MSG_PUB.Count_And_Get
321 ( p_count => x_msg_count ,
322 p_data => x_msg_data
323 );
324 END create_item_wrap;
325 END IEM_ACT_CATG_PVT ;