[Home] [Help]
PACKAGE BODY: APPS.IEM_AMV_PVT
Source
1 package body IEM_AMV_PVT as
2 /* $Header: iemvamvb.pls 120.1 2005/06/10 12:34:50 appldev $*/
3 G_PKG_NAME varchar2(100):='IEM_AMV_PVT';
4 G_cat_tbl category_tbl;
5
6 PROCEDURE get_categories (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 := NULL,
8 p_commit IN VARCHAR2 := NULL,
9 x_category_tbl out nocopy category_tbl,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2) IS
13
14 l_api_name VARCHAR2(255):='get_categories';
15 l_api_version_number NUMBER:=1.0;
16
17 l_index number:=0;
18 l_out_index number;
19
20 l_cat_ids_tbl jtf_number_table:=jtf_number_table();
21 l_cat_names_tbl jtf_varchar2_Table_100:=jtf_varchar2_Table_100();
22
23
24 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
25 l_msg_count NUMBER := 0;
26 l_msg_data VARCHAR2(2000);
27
28 IEM_ERROR_GET_SUB_CATEGORIES EXCEPTION;
29
30 BEGIN
31 SAVEPOINT search_message_pvt;
32 -- Standard call to check for call compatibility.
33 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
34 p_api_version_number,
35 l_api_name,
36 G_PKG_NAME)
37 THEN
38 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
39 END IF;
40 -- Initialize message list if p_init_msg_list is set to TRUE.
41 IF FND_API.to_Boolean( p_init_msg_list )
42 THEN
43 FND_MSG_PUB.initialize;
44 END IF;
45
46 -- Initialize API return status to SUCCESS
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 l_cat_ids_tbl.extend;
50 l_cat_names_tbl.extend;
51
52 select channel_category_id, channel_category_name bulk collect into l_cat_ids_tbl, l_cat_names_tbl
53 from amv_c_categories_vl
54 where channel_category_name like '%'
55 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
56 and application_id = 520
57 and parent_channel_category_id is null
58 order by channel_category_name;
59
60 -- First Value is NONE
61 G_cat_tbl(l_index).category_id := '-1';
62 G_cat_tbl(l_index).category_name := 'None';
63
64 l_index := l_index + 1;
65
66 FOR i IN l_cat_ids_tbl.FIRST..l_cat_ids_tbl.LAST LOOP
67
68 -- x_category_tbl(l_index).category_id := l_cat_ids_tbl(i);
69 -- x_category_tbl(l_index).category_name := '--' || l_cat_names_tbl(i);
70
71
72 G_cat_tbl(l_index).category_id := l_cat_ids_tbl(i);
73 G_cat_tbl(l_index).category_name := '--' || l_cat_names_tbl(i);
74
75 l_index := l_index + 1;
76
77 iem_amv_pvt.get_sub_categories (p_api_version_number => p_api_version_number,
78 p_init_msg_list => p_init_msg_list,
79 p_commit => p_commit,
80 p_category_id => l_cat_ids_tbl(i),
81 p_index => l_index,
82 p_string => '--',
83 x_index => l_out_index,
84 x_return_status => l_return_status,
85 x_msg_count => l_msg_count,
86 x_msg_data => l_msg_data);
87
88 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
89 raise IEM_ERROR_GET_SUB_CATEGORIES;
90 end if;
91
92 l_index := l_out_index;
93
94
95 END LOOP;
96
97 FOR j IN 0..l_index-1 LOOP
98
99 x_category_tbl(j).category_id := G_cat_tbl(j).category_id;
100 x_category_tbl(j).category_name := G_cat_tbl(j).category_name;
101
102
103 END LOOP;
104
105 -- Standard Check Of p_commit.
106 IF FND_API.To_Boolean(p_commit) THEN
107 COMMIT WORK;
108 END IF;
109 -- Standard callto get message count and if count is 1, get message info.
110 FND_MSG_PUB.Count_And_Get
111 ( p_count => x_msg_count,
112 p_data => x_msg_data
113 );
114 EXCEPTION
115 WHEN IEM_ERROR_GET_SUB_CATEGORIES THEN
116 ROLLBACK TO create_item_wrap_pvt;
117 FND_MESSAGE.SET_NAME('IEM','IEM_ERROR_GET_SUBCAT');
118 FND_MSG_PUB.Add;
119 x_return_status := FND_API.G_RET_STS_ERROR ;
120 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
121
122 WHEN FND_API.G_EXC_ERROR THEN
123 ROLLBACK TO search_message_pvt;
124 x_return_status := FND_API.G_RET_STS_ERROR ;
125 FND_MSG_PUB.Count_And_Get
126 ( p_count => x_msg_count,
127 p_data => x_msg_data
128 );
129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
130 ROLLBACK TO search_message_pvt;
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data
135 );
136 WHEN OTHERS THEN
137 ROLLBACK TO search_message_pvt;
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 IF FND_MSG_PUB.Check_Msg_Level
140 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
141 THEN
142 FND_MSG_PUB.Add_Exc_Msg
143 ( G_PKG_NAME,
144 l_api_name
145 );
146 END IF;
147 FND_MSG_PUB.Count_And_Get
148 (p_count => x_msg_count,
149 p_data => x_msg_data
150 );
151
152 END get_categories;
153
154
155
156 PROCEDURE get_sub_categories (p_api_version_number IN NUMBER,
157 p_init_msg_list IN VARCHAR2 := NULL,
158 p_commit IN VARCHAR2 := NULL,
159 p_category_id IN NUMBER,
160 p_index IN NUMBER,
161 p_string IN VARCHAR2,
162 x_index OUT NOCOPY NUMBER,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_count OUT NOCOPY NUMBER,
165 x_msg_data OUT NOCOPY VARCHAR2) IS
166
167 l_api_name VARCHAR2(255):='get_sub_categories';
168 l_api_version_number NUMBER:=1.0;
169
170 l_index number:=0;
171 l_out_index number:=0;
172 l_cat_count number:=0;
173 l_string VARCHAR2(500);
174
175 l_cat_ids_tbl jtf_number_table:=jtf_number_table();
176 l_cat_names_tbl jtf_varchar2_Table_100:=jtf_varchar2_Table_100();
177
178 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
179 l_msg_count NUMBER := 0;
180 l_msg_data VARCHAR2(2000);
181
182
183 IEM_ERROR_GET_SUB_CATEGORIES EXCEPTION;
184 BEGIN
185 SAVEPOINT search_message_pvt;
186 -- Standard call to check for call compatibility.
187 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
188 p_api_version_number,
189 l_api_name,
190 G_PKG_NAME)
191 THEN
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
193 END IF;
194 -- Initialize message list if p_init_msg_list is set to TRUE.
195 IF FND_API.to_Boolean( p_init_msg_list )
196 THEN
197 FND_MSG_PUB.initialize;
198 END IF;
199
200 -- Initialize API return status to SUCCESS
201 x_return_status := FND_API.G_RET_STS_SUCCESS;
202
203 l_cat_ids_tbl.extend;
204 l_cat_names_tbl.extend;
205
206 select count(*) into l_cat_count
207 from amv_c_categories_vl
208 where channel_category_name like '%'
209 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
210 and application_id = 520
211 and parent_channel_category_id = p_category_id
212 order by channel_category_name;
213
214
215 if (l_cat_count > 0) then
216 select channel_category_id, channel_category_name bulk collect into l_cat_ids_tbl, l_cat_names_tbl
217 from amv_c_categories_vl
218 where channel_category_name like '%'
219 and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
220 and application_id = 520
221 and parent_channel_category_id = p_category_id
222 order by channel_category_name;
223
224 x_index := p_index;
225
226 FOR i IN l_cat_ids_tbl.FIRST..l_cat_ids_tbl.LAST LOOP
227
228 l_string := '--' || p_string;
229
230 G_cat_tbl(x_index).category_id := l_cat_ids_tbl(i);
231 G_cat_tbl(x_index).category_name := l_string || l_cat_names_tbl(i);
232
233 x_index := x_index + 1;
234
235 iem_amv_pvt.get_sub_categories (p_api_version_number => p_api_version_number,
236 p_init_msg_list => p_init_msg_list,
237 p_commit => p_commit,
238 p_category_id => l_cat_ids_tbl(i),
239 p_index => x_index,
240 p_string => l_string,
241 x_index => l_out_index,
242 x_return_status => l_return_status,
243 x_msg_count => l_msg_count,
244 x_msg_data => l_msg_data);
245
246 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
247 raise IEM_ERROR_GET_SUB_CATEGORIES;
248 end if;
249
250 x_index := l_out_index;
251
252 END LOOP;
253 else
254 x_index := p_index;
255 end if;
256
257
258 -- Standard Check Of p_commit.
259 IF FND_API.To_Boolean(p_commit) THEN
260 COMMIT WORK;
261 END IF;
262 -- Standard callto get message count and if count is 1, get message info.
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267 EXCEPTION
268
269 WHEN IEM_ERROR_GET_SUB_CATEGORIES THEN
270 ROLLBACK TO create_item_wrap_pvt;
271 FND_MESSAGE.SET_NAME('IEM','IEM_ERROR_GET_SUBCAT');
272 FND_MSG_PUB.Add;
273 x_return_status := FND_API.G_RET_STS_ERROR ;
274 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
275
276 WHEN FND_API.G_EXC_ERROR THEN
277 ROLLBACK TO search_message_pvt;
278 x_return_status := FND_API.G_RET_STS_ERROR ;
279 FND_MSG_PUB.Count_And_Get
280 ( p_count => x_msg_count,
281 p_data => x_msg_data
282 );
283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
284 ROLLBACK TO search_message_pvt;
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
286 FND_MSG_PUB.Count_And_Get
287 ( p_count => x_msg_count,
288 p_data => x_msg_data
289 );
290 WHEN OTHERS THEN
291 ROLLBACK TO search_message_pvt;
292 x_return_status := FND_API.G_RET_STS_ERROR;
293 IF FND_MSG_PUB.Check_Msg_Level
294 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
295 THEN
296 FND_MSG_PUB.Add_Exc_Msg
297 ( G_PKG_NAME,
298 l_api_name
299 );
300 END IF;
301 FND_MSG_PUB.Count_And_Get
302 (p_count => x_msg_count,
303 p_data => x_msg_data
304 );
305
306 END get_sub_categories;
307
308 end IEM_AMV_PVT ;