[Home] [Help]
PACKAGE BODY: APPS.IBU_ALERT_BIN
Source
1 package body ibu_alert_bin as
2 /* $Header: ibuhaltb.pls 115.18.1159.2 2003/07/01 23:26:37 lahuang ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables and types
6 -- ---------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBU_ALERT_BIN';
8
9
10 -----------------------------------------------------------
11 -- Define procedure for rendering name.
12 -- ---------------------------------------------------------
13 procedure get_bin_name (p_api_version_number IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_commit IN VARCHAR := FND_API.G_FALSE,
16 p_bin_id IN NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2,
20 x_bin_name out NOCOPY VARCHAR2)
21 as
22 l_api_name CONSTANT VARCHAR2(30) := 'Get_Bin_Name';
23 begin
24 x_return_status := FND_API.G_RET_STS_SUCCESS;
25 x_bin_name := ibu_home_page_pvt.get_ak_bin_prompt ('IBU_HOM_CAT_ALERT');
26
27 EXCEPTION
28 WHEN OTHERS THEN
29 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
30 x_bin_name := 'Alerts';
31
32 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
33 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
34 END IF;
35 FND_MSG_PUB.Count_And_Get
36 (
37 p_count => x_msg_count ,
38 p_data => x_msg_data
39 );
40
41 end get_bin_name;
42
43 -----------------------------------------------------------
44 -- Define procedure for email text.
45 -- ---------------------------------------------------------
46 procedure get_email_text(p_api_version_number IN NUMBER,
47 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
48 p_commit IN VARCHAR := FND_API.G_FALSE,
49 p_user_id IN NUMBER,
50 p_lang_code IN VARCHAR2,
51 p_bin_id IN NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_msg_count OUT NOCOPY NUMBER,
54 x_msg_data OUT NOCOPY VARCHAR2,
55 x_clob out NOCOPY CLOB)
56 as
57 l_api_name CONSTANT VARCHAR2(30) := 'Get_Email_Text';
58 l_api_version CONSTANT NUMBER := 1.0;
59 res_clob CLOB;
60 tmp_str VARCHAR(400);
61 l_bin_name VARCHAR2(80);
62 l_more VARCHAR2(80);
63 amt binary_integer;
64 msg_count NUMBER;
65 location integer;
66 msg_data VARCHAR2(1000);
67 batch_size NUMBER := 3;
68
69 request_obj AMV_MYCHANNEL_PVT.AMV_REQUEST_OBJ_TYPE;
70 return_obj AMV_MYCHANNEL_PVT.AMV_RETURN_OBJ_TYPE;
71 items_array AMV_MYCHANNEL_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE;
72 resource_id NUMBER;
73 newln VARCHAR2(2) := fnd_global.newline();
74 no_items_fnd VARCHAR2(1000);
75
76 l_employee_id fnd_user.employee_id%TYPE;
77 l_customer_id fnd_user.customer_id%TYPE;
78 l_supplier_id fnd_user.supplier_id%TYPE;
79
80 begin
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 IF FND_API.to_Boolean( p_init_msg_list ) THEN
83 FND_MSG_PUB.initialize;
84 END IF;
85 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 -- API Body
89
90 -- do validation
91 IF p_bin_id is NULL OR p_user_id is null THEN
92 x_return_status := FND_API.G_RET_STS_ERROR;
93 FND_MESSAGE.SET_NAME('IBU','IBU_HOM_BIN_ID_MISSING');
94 FND_MSG_PUB.Add;
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 request_obj.records_requested := batch_size;
99 request_obj.start_record_position := 1;
100 request_obj.return_total_count_flag := 'T';
101
102 get_bin_name(p_api_version_number => l_api_version,
103 p_init_msg_list => p_init_msg_list,
104 p_bin_id => p_bin_id,
105 x_return_status => x_return_status,
106 x_msg_count => x_msg_count,
107 x_msg_data => x_msg_data,
108 x_bin_name => l_bin_name);
109 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
110 FND_MESSAGE.SET_NAME('IBU','IBU_HOM_BINNAME_ERROR');
111 FND_MSG_PUB.Add;
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114
115 l_more := ibu_home_page_pvt.get_ak_bin_prompt ('IBU_HOM_CAT_MORE');
116 IF l_more is NULL OR l_more ='' THEN
117 l_more := 'More';
118 end if;
119
120
121 begin
122 -- to be patched
123 select employee_id, customer_id, supplier_id
124 into l_employee_id, l_customer_id, l_supplier_id
125 from fnd_user
126 Where user_id = p_user_id;
127
128 If (l_employee_id is not null ) Then
129 select resource_id
130 into resource_id
131 from jtf_rs_resource_extns a, fnd_user b
132 where a.source_id = b.employee_id
133 and a.category = 'EMPLOYEE'
134 and b.user_id = p_user_id
135 and ( (a.end_date_active is null ) Or (a.end_date_active > sysdate) );
136 Elsif (l_customer_id is not null )Then
137 select resource_id
138 into resource_id
139 from jtf_rs_resource_extns a, fnd_user b
140 where a.source_id = b.customer_id
141 and a.category = 'PARTY'
142 and b.user_id = p_user_id
143 and ( (a.end_date_active is null ) Or (a.end_date_active > sysdate) );
144 ElsIf (l_supplier_id is not null )Then
145 select resource_id
146 into resource_id
147 from jtf_rs_resource_extns a, fnd_user b
148 where a.source_id = b.supplier_id
149 and a.category = 'SUPPLIER_CONTACT'
150 and b.user_id = p_user_id
151 and ( (a.end_date_active is null ) Or (a.end_date_active > sysdate) );
152 End If;
153 exception
154 when NO_DATA_FOUND then
155 resource_id := NULL;
156 when others then
157 raise;
158 end;
159
160 dbms_lob.createtemporary(res_clob, TRUE, DBMS_LOB.SESSION);
161 tmp_str := l_bin_name;
162 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
163 dbms_lob.writeappend(res_clob, length(newln), newln);
164 dbms_lob.writeappend(res_clob, length(newln), newln);
165
166 IF (resource_id IS NULL)
167
168 THEN
169 --tmp_str := fnd_message.get_string('IBU', 'IBU_HOM_NO_RESOURCE_ID');
170 tmp_str := fnd_message.get_string('IBU', 'IBU_HOM_NO_RESOURCE_ID_ALERTS');
171 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
172 dbms_lob.writeappend(res_clob, length(newln), newln);
173 --raise NO_DATA_FOUND;
174
175 ELSE
176
177 --dbms_output.put_line('Getting items for user ' || resource_id);
178 AMV_MYCHANNEL_GRP.Get_ItemsPerUser(p_api_version => 1.0,
179 p_init_msg_list => 'T',
180 x_return_status => x_return_status,
181 x_msg_count => x_msg_count,
182 x_msg_data => x_msg_data,
183 p_check_login_user => 'F',
184 p_user_id => resource_id,
185 p_request_obj => request_obj,
186 x_return_obj => return_obj,
187 x_items_array => items_array);
188
189 /*dbms_output.put_line('return_status is ' || return_status); */
190
191 if NOT (x_return_status = FND_API.G_RET_STS_SUCCESS)
192 THEN
193 tmp_str := fnd_message.get_string('IBU', 'IBU_HOM_ITEMS_ERROR');
194 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
195 dbms_lob.writeappend(res_clob, length(newln), newln);
196 --raise NO_DATA_FOUND;
197
198 ELSIF (return_obj.TOTAL_RECORD_COUNT = 0)
199 then
200
201 -- no_items_fnd := IBU_CATEGORY_MANAGER.IBU_GET_AK_DISPLAY_NAME('IBU_HOM_NO_ITEMS_FOUND');
202 -- dbms_lob.writeappend(res_clob,length(no_items_fnd),no_items_fnd);
203 tmp_str := fnd_message.get_string('IBU', 'IBU_HOM_NO_ITEMS_FOUND');
204 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
205 dbms_lob.writeappend(res_clob, length(newln), newln);
206
207 ELSE
208
209 for i IN 1 .. items_array.COUNT LOOP
210
211 amt := length (items_array(i).NAME);
212 dbms_lob.writeappend(res_clob, amt, items_array(i).NAME);
213 dbms_lob.writeappend(res_clob, length(newln), newln);
214 end loop;
215 END IF;
216 END IF;
217
218 dbms_lob.writeappend(res_clob, length(newln), newln);
219
220 x_clob := res_clob;
221 dbms_lob.freetemporary(res_clob);
222
223 -- End of API Body
224
225 -- Standard check of p_commit.
226 IF FND_API.To_Boolean( p_commit ) THEN
227 COMMIT WORK;
228 END IF;
229 -- Standard call to get message count and if count is 1, get message info.
230 FND_MSG_PUB.Count_And_Get
231 (p_count => x_msg_count ,
232 p_data => x_msg_data
233 );
234 EXCEPTION
235 WHEN FND_API.G_EXC_ERROR THEN
236 FND_MSG_PUB.Count_And_Get
237 (p_count => x_msg_count ,
238 p_data => x_msg_data
239 );
240 WHEN OTHERS THEN
241 -- ROLLBACK TO Get_Filter;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
243 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
244 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
245 END IF;
246 FND_MSG_PUB.Count_And_Get
247 (p_count => x_msg_count ,
248 p_data => x_msg_data
249 );
250 end get_email_text;
251
252 -----------------------------------------------------------
253 -- Define procedure for rendering html.
254 -- ---------------------------------------------------------
255 procedure get_html (p_api_version_number IN NUMBER,
256 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
257 p_commit IN VARCHAR := FND_API.G_FALSE,
258 p_bin_id IN NUMBER,
259 p_cookie_url IN VARCHAR2,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_msg_data OUT NOCOPY VARCHAR2,
263 x_clob out NOCOPY CLOB)
264 as
265 l_api_name CONSTANT VARCHAR2(30) := 'Get_HTML';
266 l_api_version CONSTANT NUMBER := 1.0;
267
268 l_user_id NUMBER := IBU_Home_Page_PVT.get_user_id;
269 l_lang VARCHAR2(5) := IBU_Home_Page_PVT.get_lang_code;
270
271 l_bin_name VARCHAR2(80);
272 l_more VARCHAR2(80);
273 l_bin_info IBU_Home_Page_PVT.Bin_Data_Type;
274 header_format VARCHAR2(30) := 'tableSubHeaderCell';
275 cell_format VARCHAR2(20) := 'tableDataCell';
276 tmp_str VARCHAR2 (400);
277 res_clob CLOB;
278 s_all_header_prompts IBU_HOME_PAGE_PVT.IBU_STR_ARR;
279
280 l_data IBU_Home_Page_PVT.Filter_Data_Type;
281 l_filter_list IBU_Home_Page_PVT.Filter_Data_List_Type;
282 l_filter_string VARCHAR2(500);
283
284 l_edit_url VARCHAR2(5000);
285 l_close_url VARCHAR2(5000);
286 l_detail_url VARCHAR2(5000);
287 l_more_url VARCHAR2(1000);
288 l_edit_name VARCHAR2(30);
289 l_close_name VARCHAR2(30);
290 header_str VARCHAR2(18000);
291
292 l_msg_data VARCHAR2(1000);
293 l_msg_index NUMBER;
294 l_more_msg_data BOOLEAN := FALSE;
295
296 batch_size NUMBER := 5;
297 request_obj AMV_MYCHANNEL_PVT.AMV_REQUEST_OBJ_TYPE;
298 return_obj AMV_MYCHANNEL_PVT.AMV_RETURN_OBJ_TYPE;
299 items_array AMV_MYCHANNEL_PVT.AMV_CAT_HIERARCHY_VARRAY_TYPE;
300 resource_id NUMBER;
301 newln VARCHAR2(2) := fnd_global.newline();
302 no_items_fnd VARCHAR2(1000);
303 DETAIL_URL VARCHAR2(100) := 'ibumis11.jsp';
304 item_object AMV_ITEM_PUB.AMV_ITEM_OBJ_TYPE;
305 item_file_array AMV_ITEM_PUB.AMV_NUMBER_VARRAY_TYPE;
306 item_persp_array AMV_ITEM_PUB.AMV_NAMEID_VARRAY_TYPE;
307 item_author_array AMV_ITEM_PUB.AMV_CHAR_VARRAY_TYPE;
308 item_keyword_array AMV_ITEM_PUB.AMV_CHAR_VARRAY_TYPE;
309
310 l_employee_id fnd_user.employee_id%TYPE;
311 l_customer_id fnd_user.customer_id%TYPE;
312 l_supplier_id fnd_user.supplier_id%TYPE;
313
314 begin
315
316 -- Initialize message list if p_init_msg_list is set to TRUE.
317 IF FND_API.to_Boolean( p_init_msg_list ) THEN
318 FND_MSG_PUB.initialize;
319 END IF;
320 -- Initialize API return status to success
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323 -- API Body
324
325 -- do validation
326 IF p_bin_id is NULL THEN
327 x_return_status := FND_API.G_RET_STS_ERROR;
328 FND_MESSAGE.SET_NAME('IBU','IBU_HOM_BIN_ID_MISSING');
329 FND_MSG_PUB.Add;
330 RAISE FND_API.G_EXC_ERROR;
331 END IF;
332
333 -- Get the bin name, the mandatory flag
334
335 get_bin_name(p_api_version_number => l_api_version,
336 p_init_msg_list => p_init_msg_list,
337 p_bin_id => p_bin_id,
338 x_return_status => x_return_status,
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data,
341 x_bin_name => l_bin_name);
342 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
343 FND_MESSAGE.SET_NAME('IBU','IBU_HOM_BINNAME_ERROR');
344 FND_MSG_PUB.Add;
345 RAISE FND_API.G_EXC_ERROR;
346 END IF;
347
348 l_more := ibu_home_page_pvt.get_ak_bin_prompt ('IBU_HOM_CAT_MORE');
349 IF l_more is NULL OR l_more ='' THEN
350 l_more := 'More';
351 end if;
352
353 IBU_Home_Page_PVT.get_bin_info(p_api_version_number => l_api_version,
354 p_init_msg_list => p_init_msg_list,
355 x_return_status => x_return_status,
356 x_msg_count => x_msg_count,
357 x_msg_data => x_msg_data,
358 p_bin_id => p_bin_id,
359 x_bin_info => l_bin_info);
360
361 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
362 FND_MESSAGE.SET_NAME('IBU','IBU_HOM_BININFO_ERROR');
363 FND_MSG_PUB.Add;
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366
367 if l_bin_info.row_number > -1 then
368 batch_size := l_bin_info.row_number ;
369 end if;
370
371 IBU_Home_Page_PVT.get_filter_list(p_api_version => l_api_version,
372 x_return_status => x_return_status,
373 x_msg_count => x_msg_count,
374 x_msg_data => x_msg_data,
375 p_user_id => l_user_id,
376 p_bin_id => p_bin_id,
377 x_filter_list => l_filter_list,
378 x_filter_string => l_filter_string);
379
380 IF (NOT (x_return_status = FND_API.G_RET_STS_SUCCESS))
381 OR l_filter_string is null THEN
382 -- no filter criteria exist, so use hard coded default
383 x_return_status := FND_API.G_RET_STS_SUCCESS;
384 l_filter_string := 'end=5';
385 ELSE
386 FOR l_curr_row IN 1..l_filter_list.count()
387 LOOP
388 l_data.name := l_filter_list (l_curr_row).name;
389 l_data.value := l_filter_list (l_curr_row).value;
390
391 if l_data.name IS NOT NULL AND l_data.name like 'end' then
392 batch_size := to_number(l_data.value);
393 exit;
394 end if;
395 END LOOP;
396 END IF;
397
398 l_edit_url := IBU_Home_Page_PVT.get_edit_bin_url(p_bin_id, 'ibuhalrt.jsp', l_filter_string, p_cookie_url);
399 if l_bin_info.mandatory_flag = FND_API.G_FALSE then
400 l_close_url := IBU_Home_Page_PVT.get_close_bin_url(p_bin_id, p_cookie_url);
401 else
402 l_close_url := null;
403 end if;
404 l_detail_url := DETAIL_URL || '?' || p_cookie_url;
405
406 -- now create the clob and store the bin html in
407 dbms_lob.createtemporary(res_clob, TRUE, DBMS_LOB.SESSION);
408
409 -- store the bin header in clob, including bin name, edit and close button
410 -- render_bin_header
411 dbms_lob.writeappend(res_clob,length(newln), newln);
412 tmp_str := '<table border="0" width="100%" cellspacing="0" cellpadding="0">';
413 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
414 dbms_lob.writeappend(res_clob,length(newln), newln);
415
416 dbms_lob.writeappend(res_clob,6,' <tr>');
417 dbms_lob.writeappend(res_clob,length(newln), newln);
418
419 tmp_str := ' <th id="h1">';
420 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
421 dbms_lob.writeappend(res_clob,length(newln), newln);
422
423 dbms_lob.writeappend(res_clob,7,' </tr>');
424 dbms_lob.writeappend(res_clob,length(newln), newln);
425
426 dbms_lob.writeappend(res_clob,6,' <tr>');
427 dbms_lob.writeappend(res_clob,length(newln), newln);
428
429 tmp_str := ' <td headers="h1">';
430 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
431 dbms_lob.writeappend(res_clob,length(newln), newln);
432
433
434 header_str := IBU_Home_Page_PVT.get_bin_header_html(l_bin_name,
435 '',
436 l_edit_url,
437 l_close_url);
438
439
440 dbms_lob.writeappend(res_clob, length(header_str), header_str);
441
442 /*
443 tmp_str := '<td><img height=21 src="/OA_MEDIA/ibuutl02.gif" width="7"></td>';
444 dbms_lob.writeappend(res_clob,length(tmp_str), tmp_str);
445 dbms_lob.writeappend(res_clob,length(newln), newln);
446 tmp_str := '<td align="center" nowrap width="100%" class="binHeaderCell">';
447 dbms_lob.writeappend(res_clob,length(tmp_str), tmp_str);
448
449 if l_bin_info.mandatory_flag = FND_API.G_FALSE then
450 tmp_str := '<a href="' || l_close_url || '"><img align=right border=0 src="../media/jtfcrs0l.gif"></a>';
451 dbms_lob.writeappend(res_clob,length(tmp_str), tmp_str);
452 end if;
453
454 tmp_str := '<a href="' || l_edit_url || '"><img align=right border=0 src="../media/jtfedt0l.gif"></a>' || l_bin_name || '</td>';
455 dbms_lob.writeappend(res_clob,length(tmp_str), tmp_str);
456 dbms_lob.writeappend(res_clob,length(newln), newln);
457
458 tmp_str := '<td><img height=21 src="/OA_MEDIA/ibuutr02.gif" width="7"></td>';
459 dbms_lob.writeappend(res_clob,length(tmp_str), tmp_str);
460 dbms_lob.writeappend(res_clob,length(newln), newln);
461 */
462 dbms_lob.writeappend(res_clob,9,' </td>');
463 dbms_lob.writeappend(res_clob,length(newln), newln);
464 dbms_lob.writeappend(res_clob,7,' </tr>');
465 dbms_lob.writeappend(res_clob,length(newln), newln);
466 dbms_lob.writeappend(res_clob,6,' <tr>');
467 dbms_lob.writeappend(res_clob,length(newln), newln);
468 tmp_str := ' <td headers=h1>';
469 dbms_lob.writeappend(res_clob,length(tmp_str),tmp_str);
470 dbms_lob.writeappend(res_clob,length(newln), newln);
471 tmp_str := ' <table border="0" width="100%" cellspacing="1" cellpadding="1">';
472 dbms_lob.writeappend(res_clob,length(tmp_str),tmp_str);
473 dbms_lob.writeappend(res_clob,length(newln), newln);
474
475 dbms_lob.writeappend(res_clob,12,' <tr>');
476 dbms_lob.writeappend(res_clob,length(newln), newln);
477
478 tmp_str := ' <th id="h1">';
479 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
480 dbms_lob.writeappend(res_clob,length(newln), newln);
481
482 dbms_lob.writeappend(res_clob,13,' </tr>');
483 dbms_lob.writeappend(res_clob,length(newln), newln);
484
485
486
487 request_obj.records_requested := batch_size;
488 request_obj.start_record_position := 1;
489 request_obj.return_total_count_flag := 'T';
490 --dbms_output.put_line ('getting resource id');
491
492 begin
493 -- to be patched
494 select employee_id, customer_id, supplier_id
495 into l_employee_id, l_customer_id, l_supplier_id
496 from fnd_user
497 Where user_id = l_user_id;
498
499 If (l_employee_id is not null ) Then
500 select resource_id
501 into resource_id
502 from jtf_rs_resource_extns a, fnd_user b
503 where a.source_id = b.employee_id
504 and a.category = 'EMPLOYEE'
505 and b.user_id = l_user_id
506 and ( (a.end_date_active is null ) Or (a.end_date_active > sysdate) );
507 Elsif (l_customer_id is not null )Then
508 select resource_id
509 into resource_id
510 from jtf_rs_resource_extns a, fnd_user b
511 where a.source_id = b.customer_id
512 and a.category = 'PARTY'
513 and b.user_id = l_user_id
514 and ( (a.end_date_active is null ) Or (a.end_date_active > sysdate) );
515 ElsIf (l_supplier_id is not null )Then
516 select resource_id
517 into resource_id
518 from jtf_rs_resource_extns a, fnd_user b
519 where a.source_id = b.supplier_id
520 and a.category = 'SUPPLIER_CONTACT'
521 and b.user_id = l_user_id
522 and ( (a.end_date_active is null ) Or (a.end_date_active > sysdate) );
523 End If;
524 exception
525 when NO_DATA_FOUND then
526 resource_id := NULL;
527 when others then
528 raise;
529 end;
530
531
532 IF (resource_id IS NULL)
533 THEN
534 tmp_str := '<tr><td headers=h1 class="binContentCell">'
535 || FND_MESSAGE.GET_STRING('IBU', 'IBU_HOM_NO_RESOURCE_ID_ALERTS')
536 || '</td></tr>';
537 dbms_lob.writeappend(res_clob,length(tmp_str),tmp_str);
538 dbms_lob.writeappend(res_clob,length(newln), newln);
539 ELSE
540
541 AMV_MYCHANNEL_GRP.Get_ItemsPerUser(p_api_version=>1.0,
542 p_init_msg_list=>'T',
543 x_return_status=>x_return_status,
544 x_msg_count =>x_msg_count,
545 x_msg_data => x_msg_data,
546 p_check_login_user => 'F',
547 p_user_id => resource_id,
548 p_request_obj => request_obj,
549 x_return_obj => return_obj,
550 x_items_array => items_array);
551
552 --dbms_output.put_line ('got items for user' || resource_id);
553 if NOT (x_return_status = FND_API.G_RET_STS_SUCCESS)
554 THEN
555 x_return_status := FND_API.G_RET_STS_SUCCESS;
556 tmp_str := '<tr><td headers=h1 class="binContentCell">'
557 || FND_MESSAGE.GET_STRING('IBU', 'IBU_HOM_ITEMS_ERROR')
558 || '</td></tr>';
559 dbms_lob.writeappend(res_clob,length(tmp_str),tmp_str);
560 dbms_lob.writeappend(res_clob,length(newln), newln);
561 l_more_msg_data := TRUE;
562
563 ELSIF (return_obj.TOTAL_RECORD_COUNT = 0)
564 then
565 no_items_fnd := '<tr><td headers=h1 class="binContentCell">'
566 || FND_MESSAGE.GET_STRING('IBU', 'IBU_HOM_NO_ITEMS_FOUND')
567 || '</td></tr>';
568 dbms_lob.writeappend(res_clob,length(no_items_fnd),no_items_fnd);
569 dbms_lob.writeappend(res_clob,length(newln), newln);
570
571 ELSE
572 --dbms_output.put_line('No of items'||items_array.count);
573
574 for i IN 1 .. items_array.COUNT LOOP
575 AMV_ITEM_PUB.get_item(p_api_version => 1.0,
576 p_init_msg_list => 'T',
577 x_return_status => x_return_status,
578 x_msg_count => x_msg_count,
579 x_msg_data => x_msg_data,
580 p_check_login_user => 'F',
581 p_item_id => items_array(i).id,
582 x_item_obj => item_object,
583 x_file_array => item_file_array,
584 x_persp_array => item_persp_array,
585 x_author_array => item_author_array,
586 x_keyword_array => item_keyword_array);
587
588 if NOT (x_return_status = FND_API.G_RET_STS_SUCCESS)
589 THEN
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591 --dbms_output.put_line('Error while getting Item for this user');
592 tmp_str := '<tr><td headers=h1 class="binContentCell">'
593 || FND_MESSAGE.GET_STRING('IBU', 'IBU_HOM_ITEMS_ERROR')
594 || '</td></tr>';
595 dbms_lob.writeappend(res_clob,length(tmp_str),tmp_str);
596 dbms_lob.writeappend(res_clob,length(newln), newln);
597 l_more_msg_data := TRUE;
598 ELSE
599
600 --dbms_output.put_line('Item name='||items_array(i).name);
601 --dbms_output.put_line('Item id='||items_array(i).id);
602 --dbms_output.put_line('Item type='||items_array(i).type);
603
604 dbms_lob.writeappend(res_clob, 4,'<tr>');
605 dbms_lob.writeappend(res_clob, length(newln),newln);
606
607 if (item_object.item_type like 'URL_ITEM') then
608 -- tmp_str := '<td class="binContentCell"> <li><a href="http://' ||item_object.url_string|| '" target="new">' || item_object.item_name || '</a> </td>';
609 if ( instr(item_object.url_string,'http://') =0 ) then
610 tmp_str := '<td headers=h1 class="binContentCell"><li><a href="http://' || item_object.url_string|| '" target="new">' || item_object.item_name || '</a> </td>';
611 else
612 tmp_str := '<td headers=h1 class="binContentCell"><li><a href="' || item_object.url_string|| '" target="new">' || item_object.item_name || '</a> </td>';
613 end if;
614
615 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
616 dbms_lob.writeappend(res_clob, length(newln), newln);
617
618 elsif item_object.item_type like 'FILE_ITEM' then
619 tmp_str := '<td headers=h1 class="binContentCell"><li><a href="ibuzbot.jsp?itemid=' || item_object.item_id || '" >' || item_object.item_name || '</a> </td>';
620
621 /* tmp_str := '<td class="binContentCell"> <li><a href="http://' ||DETAIL_URL||'?itemid=' || item_object.item_id || '" target="new">' || item_object.item_name || '</a> </td>'; */
622 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
623 dbms_lob.writeappend(res_clob, length(newln), newln);
624
625 else
626 tmp_str := '<td class="binContentCell"><li><a href="ibuzbot.jsp?itemid=' || item_object.item_id || '">' || item_object.item_name || '</a> </td>';
627
628 /* tmp_str := '<td class="binContentCell"> <li><a href="http://' ||DETAIL_URL|| '?itemid=' || item_object.item_id || '" target="new">' || item_object.item_name || '</a> </td>'; */
629 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
630 dbms_lob.writeappend(res_clob, length(newln), newln);
631 end if;
632 dbms_lob.writeappend(res_clob,5,'</tr>');
633 dbms_lob.writeappend(res_clob, length(newln), newln);
634
635 END IF;
636 end loop;
637
638 -- more link
639 if return_obj.TOTAL_RECORD_COUNT > batch_size then
640 dbms_lob.writeappend(res_clob,4,'<tr>');
641 dbms_lob.writeappend(res_clob, length(newln), newln);
642 tmp_str := ' <td headers=h1 class="binContentCell" headers="c1" align=right><a href="' || l_detail_url || '">' || l_more || '</a> </td>';
643 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
644 dbms_lob.writeappend(res_clob, length(newln), newln);
645 dbms_lob.writeappend(res_clob,5,'</tr>');
646 dbms_lob.writeappend(res_clob, length(newln), newln);
647 end if;
648
649 END IF; -- if NOT (x_return_status = FND_API.G_RET_STS_SUCCESS)
650
651 END IF ; -- IF (resource_id IS NULL)
652
653 if l_more_msg_data = TRUE then
654 -- need to add more error messages to clob
655 l_msg_index := 1;
656 WHILE x_msg_count > 0 LOOP
657 l_msg_data := FND_MSG_PUB.GET(
658 l_msg_index,
659 FND_API.G_FALSE
660 );
661 tmp_str := '<tr><td headers=h1 class="binContentCell">' || l_msg_data || '</td></tr>';
662 dbms_lob.writeappend(res_clob, length(tmp_str), tmp_str);
663 dbms_lob.writeappend(res_clob, length(newln), newln);
664 l_msg_index := l_msg_index + 1;
665 x_msg_count := x_msg_count - 1;
666 END LOOP;
667 end if;
668
669 -- Bin Content footer
670 dbms_lob.writeappend(res_clob, 8, '</table>');
671 dbms_lob.writeappend(res_clob,length(newln), newln);
672 dbms_lob.writeappend(res_clob, 5, '</td>');
673 dbms_lob.writeappend(res_clob,length(newln), newln);
674 dbms_lob.writeappend(res_clob, 5, '</tr>');
675 dbms_lob.writeappend(res_clob,length(newln), newln);
676 dbms_lob.writeappend(res_clob, 8, '</table>');
677 dbms_lob.writeappend(res_clob,length(newln), newln);
678 /*
679 dbms_lob.writeappend(res_clob, 4, '<tr>');
680 dbms_lob.writeappend(res_clob,length(newln), newln);
681 -- chr(38) is special char for 'and'
682 tmp_str := '<td colspan="3" height="15">' || CHR(38) || 'nbsp;</td>';
683 dbms_lob.writeappend(res_clob,length(tmp_str), tmp_str);
684 dbms_lob.writeappend(res_clob,length(newln), newln);
685 dbms_lob.writeappend(res_clob, 5, '</tr>');
686 dbms_lob.writeappend(res_clob,length(newln), newln);
687 */
688 x_clob := res_clob;
689 dbms_lob.freetemporary(res_clob);
690
691 -- End of API Body
692
693 -- Standard check of p_commit.
694 IF FND_API.To_Boolean( p_commit ) THEN
695 COMMIT WORK;
696 END IF;
697 -- Standard call to get message count and if count is 1, get message info.
698 FND_MSG_PUB.Count_And_Get
699 (p_count => x_msg_count ,
700 p_data => x_msg_data
701 );
702 EXCEPTION
703 WHEN FND_API.G_EXC_ERROR THEN
704 FND_MSG_PUB.Count_And_Get
705 (p_count => x_msg_count ,
706 p_data => x_msg_data
707 );
708 WHEN OTHERS THEN
709 -- ROLLBACK TO Get_Filter;
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
711 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
712 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
713 END IF;
714 FND_MSG_PUB.Count_And_Get
715 (p_count => x_msg_count ,
716 p_data => x_msg_data
717 );
718 end get_html;
719 end ibu_alert_bin;
720