[Home] [Help]
PACKAGE BODY: APPS.IBU_HOME_PAGE_PVT
Source
1 package body IBU_HOME_PAGE_PVT
2 /* $Header: ibuhvhpb.pls 120.0 2005/10/06 09:43:08 ktma noship $ */
3 as
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBU_HOME_PAGE_PVT';
5 G_USER_PROFILE_NAME CONSTANT VARCHAR2(30) := 'IBU_PERZ_';
6 G_BIN_DATA_NAME CONSTANT VARCHAR2(30) := 'IBU_BIN';
7
8 G_FILTER_DATA_NAME CONSTANT VARCHAR2(30) := 'IBU_BIN_FILTER_';
9 G_FILTER_DATA_TYPE CONSTANT VARCHAR2(30) := 'IBU_FILTER';
10 G_PREF_DATA_NAME CONSTANT VARCHAR2(30) := 'IBU_PREFERENCES';
11 G_PREF_DATA_TYPE CONSTANT VARCHAR2(30) := 'IBU_PREFERENCES';
12 G_PREF_ACCOUNT_ATTR_NAME CONSTANT VARCHAR2(30) := 'account_id';
13 G_PREF_DATE_ATTR_NAME CONSTANT VARCHAR2(30) := 'date_format';
14
15 G_BIN_PACKAGE_ATTR_NAME CONSTANT VARCHAR2(30) := 'plsql_package';
16 G_BIN_MANDATORY_ATTR_NAME CONSTANT VARCHAR2(30) := 'bin_mandatory_flag';
17 G_BIN_DISABLED_ATTR_NAME CONSTANT VARCHAR2(30) := 'bin_disabled_flag';
18 G_BIN_MES_ID_ATTR_NAME CONSTANT VARCHAR2(30) := 'MES_cat_ID';
19 G_BIN_ROW_NUMBER_ATTR_NAME CONSTANT VARCHAR2(30) := 'row_number';
20
21 G_HOME_REGION_CODE CONSTANT VARCHAR2(30) := 'IBU_HOM_CATEGORY';
22
23 -- Changes for Contracts
24 function is_rollout_enabled return VARCHAR
25 as
26 l_user_id NUMBER;
27 l_resp_id NUMBER;
28 l_app_id NUMBER;
29 l_return_value VARCHAR(10);
30 x_return_status VARCHAR2(1);
31 begin
32 l_user_id := FND_GLOBAL.USER_ID;
33 l_resp_id := FND_GLOBAL.RESP_ID;
34 l_app_id := FND_GLOBAL.RESP_APPL_ID;
35 l_return_value := FND_PROFILE.VALUE_SPECIFIC ('IBU_INTERNAL_ROLLOUT', l_user_id, l_resp_id, l_app_id);
36 if l_return_value = 'Y' then
37 x_return_status := FND_API.G_TRUE;
38 else
39 x_return_status := FND_API.G_FALSE;
40 end if;
41 return x_return_status;
42 end;
43
44 function is_country_contract_enabled return VARCHAR
45 as
46 type Info_Cursor IS REF CURSOR;
47 l_info_cursor Info_Cursor;
48 l_count NUMBER;
49 l_cust_id NUMBER;
50 stmt VARCHAR(200);
51 x_return_status VARCHAR2(1) := FND_API.G_FALSE;
52 begin
53 l_cust_id := get_customer_id();
54 if (l_cust_id > 0) then
55 stmt := 'select count(*) ';
56 stmt := stmt || ' from ibu_party_contracts ' ;
57 stmt := stmt || ' where party_id= :custid ';
58 OPEN l_info_cursor for stmt using l_cust_id;
59 LOOP
60 FETCH l_info_cursor into l_count;
61 EXIT WHEN l_info_cursor%NOTFOUND;
62 END LOOP;
63 CLOSE l_info_cursor;
64
65 if l_count > 0 then
66 x_return_status := FND_API.G_TRUE;
67 else
68 x_return_status := FND_API.G_FALSE;
69 end if;
70 end if;
71 return x_return_status;
72 end;
73
74
75 -- common context info
76 function get_user_id return NUMBER
77 as
78 begin
79 return FND_GLOBAL.USER_ID;
80 end get_user_id;
81
82 function get_user_name return VARCHAR2
83 as
84 begin
85 return FND_GLOBAL.USER_NAME;
86 end get_user_name;
87
88 function get_app_id return NUMBER
89 as
90 begin
91 return 672;
92 end get_app_id;
93
94 function get_resp_id return NUMBER
95 as
96 begin
97 -- get the default resp id for this user
98 -- seems fnd_global may not return the right responsibility
99 return get_resp_id_from_user(get_user_id);
100 end get_resp_id;
101
102 function get_customer_id return NUMBER
103 as
104 begin
105 return get_customer_id_from_user(get_user_id);
106 end;
107
108 function get_employee_id return NUMBER
109 as
110 begin
111 return get_employee_id_from_user(get_user_id);
112 end;
113
114 function get_company_id return NUMBER
115 as
116 begin
117 return get_company_id_from_user(get_user_id);
118 end;
119
120 function get_company_name return VARCHAR2
121 as
122 begin
123 return get_company_name_from_user(get_user_id);
124 end;
125
126 function get_account_id return NUMBER
127 as
128 begin
129 return get_account_id_from_user(get_user_id);
130 end get_account_id;
131
132 function get_lang_code return VARCHAR2
133 as
134 begin
135 return userenv('LANG');
136 end get_lang_code;
137
138 function get_date_format return VARCHAR2
139 as
140 begin
141 return get_date_format_from_user(get_user_id);
142 end get_date_format;
143
144 function get_resp_id_from_user(p_user_id IN NUMBER) return NUMBER
145 as
146 begin
147 -- get the default resp id for this user
148 -- seems fnd_global may not return the right responsibility
149 return FND_PROFILE.VALUE_SPECIFIC(
150 'JTF_PROFILE_DEFAULT_RESPONSIBILITY',
151 p_user_id,
152 null,
153 null);
154 end get_resp_id_from_user;
155
156 function get_customer_id_from_user(p_user_id IN NUMBER) return NUMBER
157 as
158 l_customer_id NUMBER;
159 begin
160 SELECT customer_id
161 INTO l_customer_id
162 FROM fnd_user
163 WHERE user_id = p_user_id;
164
165 return l_customer_id;
166 exception
167 when others then
168 return null;
169 end get_customer_id_from_user;
170
171 function get_employee_id_from_user(p_user_id IN NUMBER) return NUMBER
172 as
173 l_employee_id NUMBER;
174 begin
175 SELECT employee_id
176 INTO l_employee_id
177 FROM fnd_user
178 WHERE user_id = p_user_id;
179
180 return l_employee_id;
181 exception
182 when others then
183 return null;
184 end get_employee_id_from_user;
185
186 function get_party_type_from_user(p_user_id IN NUMBER,
187 x_party_id OUT NOCOPY NUMBER) return VARCHAR2
188 as
189 l_party_type VARCHAR2(30);
190 begin
191 SELECT p.party_type, party_id
192 INTO l_party_type, x_party_id
193 FROM hz_parties p, fnd_user u
194 WHERE u.user_id = p_user_id
195 AND p.party_id = u.customer_id;
196 return l_party_type;
197 exception
198 when NO_DATA_FOUND then
199 return null;
200 when others then
201 raise FND_API.G_EXC_ERROR;
202 end get_party_type_from_user;
203
204 function get_company_id_from_user(p_user_id IN NUMBER) return NUMBER
205 as
206 l_company_id NUMBER;
207 l_party_type VARCHAR2(30) := null;
208 l_party_id NUMBER := null;
209 begin
210 l_party_type := get_party_type_from_user(p_user_id, l_party_id);
211 if (l_party_type = NULL OR l_party_id = NULL) then
212 return null;
213 end if;
214 if (l_party_type = 'PARTY_RELATIONSHIP') then
215 SELECT object_id
216 INTO l_company_id
217 FROM hz_relationships
218 WHERE party_id = l_party_id
219 AND relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
220 AND content_source_type = 'USER_ENTERED'
221 AND status = 'A';
222 elsif (l_party_type = 'PERSON') then
223 SELECT p.object_id
224 INTO l_company_id
225 FROM hz_relationships p
226 WHERE p.subject_id = l_party_id
227 AND p.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
228 AND content_source_type = 'USER_ENTERED'
229 AND status = 'A';
230 else
231 return null;
232 end if;
233
234 return l_company_id;
235 exception
236 when NO_DATA_FOUND then
237 return null;
238 when others then
239 raise FND_API.G_EXC_ERROR;
240 end get_company_id_from_user;
241
242 function get_company_name_from_user(p_user_id IN NUMBER) return VARCHAR2
243 as
244 l_company_id NUMBER;
245 l_company_name VARCHAR2(360);
246 begin
247 l_company_id := get_company_id_from_user(p_user_id);
248 SELECT party_name
249 INTO l_company_name
250 FROM hz_parties
251 WHERE party_id = l_company_id;
252
253 return l_company_name;
254 exception
255 when others then
256 return null;
257 end get_company_name_from_user;
258
259 function get_account_id_from_user(p_user_id IN NUMBER) return NUMBER
260 as
261 l_return_status VARCHAR2(240);
262 l_msg_count NUMBER;
263 l_msg_data VARCHAR2(2000);
264 l_attrib_val VARCHAR2(300);
265 l_pd_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
266
267 l_acct_id NUMBER := 0;
268 l_acct_list Account_List_Type;
269 begin
270
271 -- Get the account id from perz data framework (should validate?)
272 -- BUGBUG : if none found, need to randomly pick one from valid acct
273 -- list. Also it will be good to verify acct valid.
274 get_perz_data_attrib(p_api_version_number => 1.0,
275 x_return_status => l_return_status,
276 x_msg_count => l_msg_count,
277 x_msg_data => l_msg_data,
278 p_user_id => p_user_id,
279 p_pd_id => null,
280 p_pd_name => G_PREF_DATA_NAME,
281 p_pd_type => null,
282 p_pd_attrib_name => G_PREF_ACCOUNT_ATTR_NAME,
283 x_pd_attrib_value => l_attrib_val,
284 x_pd_attrib_tbl => l_pd_attrib_tbl
285 );
286
287 if NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) OR l_attrib_val is null THEN
288 -- default accout does not exist
289 l_acct_list := get_accounts_from_user(p_user_id);
290 if l_acct_list is not null and l_acct_list.count() >0 then
291 l_acct_id := l_acct_list(1).account_id;
292 end if;
293 return l_acct_id;
294 else
295 return to_number(l_attrib_val);
296 end if;
297
298 exception
299 when others then
300 return null;
301 end get_account_id_from_user;
302
303 function get_accounts_from_user(p_user_id IN NUMBER)
304 return Account_List_Type
305 as
306 l_acct_list Account_List_Type;
307 l_acct Account_Data_Type;
308 l_customer_id NUMBER := NULL;
309 l_company_id NUMBER := NULL;
310 l_cind NUMBER := 1;
311
312 CURSOR acct_info_b2c(p_customer_id IN NUMBER) IS
313 select cust_account_id, account_number
314 from HZ_CUST_ACCOUNTS
315 where status = 'A'
316 and cust_account_id in
317 (select cust_account_id
318 from HZ_CUST_ACCOUNT_ROLES
319 where party_id = p_customer_id
320 and current_role_state = 'A')
321 order by account_number;
322
323 /*
324 select r.cust_account_id, r.account_number
325 from hz_cust_accounts r, hz_cust_account_roles s
326 where r.cust_account_id = s.cust_account_id
327 and s.current_role_state = 'A'
328 and s.party_id = p_customer_id
329 order by r.account_number;
330 */
331
332 CURSOR acct_info_b2b(p_customer_id IN NUMBER, p_company_id IN NUMBER) IS
333 select cust_account_id, account_number
334 from HZ_CUST_ACCOUNTS
335 where status = 'A'
336 and party_id = p_company_id
337 and cust_account_id in
338 (select cust_account_id
339 from HZ_CUST_ACCOUNT_ROLES
340 where party_id = p_customer_id
341 and current_role_state = 'A');
342
343 /*
344 select r.cust_account_id, r.account_number
345 from hz_cust_accounts r, hz_cust_account_roles s
346 where r.cust_account_id = s.cust_account_id
347 and s.current_role_state = 'A'
348 and s.party_id = p_customer_id
349 and r.party_id = p_company_id
350 order by r.account_number;
351 */
352
353 begin
354 l_acct_list := Account_List_Type();
355 l_company_id := get_company_id_from_user(p_user_id);
356 l_customer_id := get_customer_id_from_user(p_user_id);
357
358 IF l_customer_id is not null THEN
359 IF (l_company_id = NULL) THEN
360 FOR acct_info_rec IN acct_info_b2c(l_customer_id)
361 LOOP
362 l_acct_list.extend();
363 l_acct.account_id := acct_info_rec.cust_account_id;
364 l_acct.account_number := acct_info_rec.account_number;
365 l_acct_list(l_cind) := l_acct;
366 l_cind := l_cind + 1;
367 END LOOP;
368 ELSE
369 FOR acct_info_rec IN acct_info_b2b(l_customer_id, l_company_id)
370 LOOP
371 l_acct_list.extend();
372 l_acct.account_id := acct_info_rec.cust_account_id;
373 l_acct.account_number := acct_info_rec.account_number;
374 l_acct_list(l_cind) := l_acct;
375 l_cind := l_cind + 1;
376 END LOOP;
377 END IF;
378 END IF;
379
380 return l_acct_list;
381 exception
382 when others then
383 return null;
384 end get_accounts_from_user;
385
386
387 function get_date_format_from_user(p_user_id IN NUMBER) return VARCHAR2
388 as
389 begin
390
391 -- get the default date format for this user
392 return FND_PROFILE.VALUE_SPECIFIC(
393 'ICX_DATE_FORMAT_MASK',
394 p_user_id,
395 null,
396 null);
397 exception
398 when others then
399 return 'MON-DD-YYYY'; -- use this one as default
400 end get_date_format_from_user;
401
402 function get_long_language_from_user(p_user_id IN NUMBER) return VARCHAR2
403 as
404 begin
405
406 -- get the default long language preference for this user
407 return FND_PROFILE.VALUE_SPECIFIC(
408 'ICX_LANGUAGE',
409 p_user_id,
410 null,
411 null);
412 exception
413 when others then
414 return 'AMERICAN'; -- use this one as default
415 end get_long_language_from_user;
416
417 -- other util functions
418
419 function get_close_bin_url(p_bin_id IN NUMBER, p_cookie_url IN VARCHAR2)
420 return VARCHAR2
421 as
422 l_close_url VARCHAR2(5000);
423 begin
424 l_close_url := 'ibuhpage.jsp?action=close' || fnd_global.local_chr(38)
425 || 'binId=' || to_char(p_bin_id);
426 if p_cookie_url is not null then
427 l_close_url := l_close_url || fnd_global.local_chr(38) || p_cookie_url;
428 end if;
429 return l_close_url;
430 end get_close_bin_url;
431
432 function get_edit_bin_url(p_bin_id IN NUMBER,
433 p_jsp_file_name IN VARCHAR2,
434 p_filter_string IN VARCHAR2,
435 p_cookie_url IN VARCHAR2)
436 return VARCHAR2
437 as
438 l_edit_url VARCHAR2(5000);
439 begin
440 l_edit_url := 'ibuhedtf.jsp?binId=' || to_char(p_bin_id)
441 || fnd_global.local_chr(38) || 'filterFile=' || p_jsp_file_name;
442
443 if p_filter_string is not null then
444 l_edit_url := l_edit_url || fnd_global.local_chr(38) || p_filter_string;
445 end if;
446
447 if p_cookie_url is not null then
448 l_edit_url := l_edit_url || fnd_global.local_chr(38) || p_cookie_url;
449 end if;
450 return l_edit_url;
451 end get_edit_bin_url;
452
453 function get_bin_header_html(p_bin_name IN VARCHAR2,
454 p_bin_link_url IN VARCHAR2,
455 p_edit_url IN VARCHAR2,
456 p_close_url IN VARCHAR2)
457 return VARCHAR2
458 as
459 l_html VARCHAR2(18000);
460 l_tmp_str VARCHAR2(10000);
461 newln VARCHAR2(2) := fnd_global.newline ();
462 l_prompts IBU_Home_Page_PVT.IBU_STR_ARR;
463 l_edit_name VARCHAR2(80);
464 l_close_name VARCHAR2(80);
465 begin
466 l_html := newln || ' <table border=0 cellspacing=0 cellpadding=0 width="100%">'
467 || newln
468 || ' <tr><th id=h1><th id=h2><th id=h3><th id=h4><th id=h5></tr>' ||newln
469 || ' <tr>' || newln
470 || ' <td headers=h1><img height=21 src="/OA_MEDIA/jtfutl02.gif" width="7" alt=""></td>'
471 || newln
472 || ' <td headers=h2 nowrap width="100%" class="binHeaderCell">';
473
474 if (p_edit_url is not null OR p_close_url is not null) then
475 IBU_Home_Page_PVT.get_ak_region_items('IBU_HOM_PAGE', l_prompts);
476
477 if l_prompts.count() = 0 then
478 l_edit_name := 'Edit';
479 l_close_name := 'Close';
480 else
481 l_edit_name := l_prompts(4);
482 l_close_name := l_prompts(5);
483 end if;
484 end if;
485
486 if p_bin_link_url is not null AND p_bin_link_url <> '' then
487 l_tmp_str := '<a href="' || p_bin_link_url
488 || '"><font color="#FFFFFF">'
489 || p_bin_name || '</font></a>';
490 else
491 l_tmp_str := p_bin_name;
492 end if;
493
494 l_html := l_html || l_tmp_str || '</td>' || newln;
495
496 l_tmp_str := '';
497
498 if p_edit_url is not null then
499 l_tmp_str := ' <td headers=h3 nowrap class="binHeaderCell"><a href="' || p_edit_url
500 || '"><font size="-1" color="#FFFFFF">' || l_edit_name
501 || '</font></a>' || fnd_global.local_chr(38) || 'nbsp;</td>' || newln;
502 end if;
503
504 if p_close_url is not null then
505 l_tmp_str := l_tmp_str
506 || ' <td headers=h4 nowrap class="binHeaderCell"><a href="' || p_close_url
507 || '"><font size="-1" color="#FFFFFF">' || l_close_name
508 || '</font></a></td>' || newln;
509 end if;
510
511 l_html := l_html || l_tmp_str
512 || ' <td headers=h5><img height=21 src="/OA_MEDIA/jtfutr02.gif" width="7" alt=""></td>'
513 || newln || ' </tr>' || newln || ' </table>' || newln;
514
515 return l_html;
516 end get_bin_header_html;
517
518 procedure get_bin_info(
519 p_api_version_number IN NUMBER,
520 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
521 p_commit IN VARCHAR := FND_API.G_FALSE,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2,
525 p_bin_id IN NUMBER,
526 x_bin_info OUT NOCOPY Bin_Data_Type)
527 as
528 l_attrib_name VARCHAR2(60);
529 l_attrib_val VARCHAR2(300);
530 l_pd_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
531 l_bin_info Bin_Data_Type;
532 begin
533 x_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 l_bin_info.bin_id := p_bin_id;
536
537 get_perz_data_attrib(p_api_version_number => 1.0,
538 x_return_status => x_return_status,
539 x_msg_count => x_msg_count,
540 x_msg_data => x_msg_data,
541 p_prof_name => G_ADMIN_PROFILE_NAME,
542 p_pd_id => p_bin_id,
543 p_pd_name => null,
544 p_pd_type => null,
545 p_one_attrib => FND_API.G_FALSE,
546 x_pd_attrib_value => l_attrib_val,
547 x_pd_attrib_tbl => l_pd_attrib_tbl
548 );
549
550 if NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
551 -- the bin does not exist (maybe deleted)
552 l_bin_info.bin_id := null;
553 return;
554 end if;
555
556 IF l_pd_attrib_tbl is not null AND l_pd_attrib_tbl.count > 0 THEN
557 FOR f_curr_row IN 1..l_pd_attrib_tbl.count
558 LOOP
559 -- check the attribute name and set the return value
560 l_attrib_name := l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_NAME;
561 l_attrib_val := l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_VALUE;
562
563 IF l_attrib_name = G_BIN_PACKAGE_ATTR_NAME THEN
564 l_bin_info.package_name := l_attrib_val;
565 ELSIF l_attrib_name = G_BIN_MANDATORY_ATTR_NAME THEN
566 if l_attrib_val = 'Y' then
567 l_bin_info.mandatory_flag := FND_API.G_TRUE;
568 else
569 l_bin_info.mandatory_flag := FND_API.G_FALSE;
570 end if;
571 ELSIF l_attrib_name = G_BIN_DISABLED_ATTR_NAME THEN
572 if l_attrib_val = 'Y' then
573 l_bin_info.disabled_flag := FND_API.G_TRUE;
574 else
575 l_bin_info.disabled_flag := FND_API.G_FALSE;
576 end if;
577 ELSIF l_attrib_name = G_BIN_MES_ID_ATTR_NAME THEN
578 l_bin_info.MES_cat_ID := to_number(l_attrib_val);
579 ELSIF l_attrib_name = G_BIN_ROW_NUMBER_ATTR_NAME THEN
580 l_bin_info.row_number := to_number(l_attrib_val);
581 END IF;
582 END LOOP;
583 END IF;
584
585 x_bin_info := l_bin_info;
586
587 exception
588 when others then
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
590 FND_MSG_PUB.Count_And_Get
591 (p_count => x_msg_count ,
592 p_data => x_msg_data
593 );
594 x_bin_info := l_bin_info;
595 end get_bin_info;
596
597 function get_formatted_date(p_date in DATE, p_format in VARCHAR2)
598 return VARCHAR2
599 as
600 begin
601 return to_char(p_date, p_format);
602 end get_formatted_date;
603
604 procedure get_ak_region_items(p_region_code IN VARCHAR2,
605 p_prompts OUT NOCOPY IBU_STR_ARR)
606 as
607 l_region_name varchar2(50);
608 l_empty_str varchar2(30) := ' ';
609 l_items_table jtf_region_pub.ak_region_items_table;
610 l_item_name varchar2(50) := null;
611 l_prompts IBU_STR_ARR := IBU_STR_ARR ();
612 begin
613 jtf_region_pub.get_region(p_region_code,
614 get_app_id,
615 get_resp_id,
616 l_empty_str,
617 l_region_name,
618 l_empty_str,
619 l_items_table);
620
621 FOR l in 1..l_items_table.count()
622 loop
623 l_prompts.extend();
624 l_prompts(l) := jtf_region_pub.get_region_item_name(
625 l_items_table(l).attribute_code,
626 p_region_code);
627 end loop;
628 p_prompts := l_prompts;
629 exception
630 when others then
631 -- return empty array
632 p_prompts := l_prompts;
633 end get_ak_region_items;
634
635 function get_ak_bin_prompt(p_region_item_name IN VARCHAR2)
636 return VARCHAR2
637 as
638 l_region_code varchar2(20) := G_HOME_REGION_CODE;
639 l_region_name varchar2(50);
640 l_empty_str varchar2(30) := ' ';
641 l_items_table jtf_region_pub.ak_region_items_table;
642 l_item_name varchar2(50) := null;
643 begin
644 jtf_region_pub.get_region(l_region_code,
645 get_app_id,
646 get_resp_id,
647 l_empty_str,
648 l_region_name,
649 l_empty_str,
650 l_items_table);
651
652 FOR l in 1..l_items_table.count()
653 loop
654 if l_items_table(l).attribute_code like p_region_item_name then
655 return jtf_region_pub.get_region_item_name(
656 l_items_table(l).attribute_code,
657 l_region_code);
658 end if;
659 end loop;
660
661 return null;
662 end get_ak_bin_prompt;
663
664 procedure get_ak_region_items_from_user(p_user_id IN NUMBER,
665 p_region_code IN VARCHAR2,
666 p_prompts OUT NOCOPY IBU_STR_ARR)
667 as
668 l_region_name varchar2(50);
669 l_empty_str varchar2(30) := ' ';
670 l_items_table jtf_region_pub.ak_region_items_table;
671 l_item_name varchar2(50) := null;
672 l_prompts IBU_STR_ARR := IBU_STR_ARR ();
673 begin
674 jtf_region_pub.get_region(p_region_code,
675 get_app_id,
676 get_resp_id_from_user(p_user_id),
677 l_empty_str,
678 l_region_name,
679 l_empty_str,
680 l_items_table);
681
682 FOR l in 1..l_items_table.count
683 loop
684 l_prompts.extend();
685 l_prompts(l) := jtf_region_pub.get_region_item_name(
686 l_items_table(l).attribute_code,
687 p_region_code);
688 end loop;
689 p_prompts := l_prompts;
690 exception
691 when others then
692 -- return empty array
693 p_prompts := l_prompts;
694 end;
695
696 procedure get_filter_list(p_api_version IN NUMBER,
697 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
698 p_commit IN VARCHAR := FND_API.G_FALSE,
699 p_user_id IN NUMBER,
700 p_bin_id In NUMBER,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_msg_count OUT NOCOPY NUMBER,
703 x_msg_data OUT NOCOPY VARCHAR2,
704 x_filter_list OUT NOCOPY Filter_Data_List_Type,
705 x_filter_string OUT NOCOPY VARCHAR2)
706 as
707 l_api_name CONSTANT VARCHAR2(30) := 'Get_Filter_List';
708 l_api_version CONSTANT NUMBER := 1.0;
709
710 l_attrib_val VARCHAR2(300);
711 l_pd_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
712
713 data Filter_Data_Type;
714 filter_list Filter_Data_List_Type;
715 ind NUMBER := 0;
716 begin
717
718 -- Initialize message list if p_init_msg_list is set to TRUE.
719 IF FND_API.to_Boolean( p_init_msg_list ) THEN
720 FND_MSG_PUB.initialize;
721 END IF;
722 -- Initialize API return status to success
723 x_return_status := FND_API.G_RET_STS_SUCCESS;
724
725 -- API Body
726 filter_list := Filter_Data_List_Type ();
727 ind := 1;
728 x_filter_string := null;
729 x_filter_list := null;
730
731 IBU_Home_Page_PVT.get_perz_data_attrib(
732 p_api_version_number => l_api_version,
733 x_return_status => x_return_status,
734 x_msg_count => x_msg_count,
735 x_msg_data => x_msg_data,
736 p_user_id => p_user_id,
737 p_pd_id => null,
738 p_pd_name => G_FILTER_DATA_NAME || to_char(p_bin_id),
739 p_pd_type => G_FILTER_DATA_TYPE,
740 p_one_attrib => FND_API.G_FALSE,
741 p_pd_attrib_name => null,
742 x_pd_attrib_value => l_attrib_val,
743 x_pd_attrib_tbl => l_pd_attrib_tbl
744 );
745
746 IF x_return_status = FND_API.G_RET_STS_SUCCESS
747 AND l_pd_attrib_tbl is not null AND l_pd_attrib_tbl.count > 0 THEN
748 FOR f_curr_row IN 1..l_pd_attrib_tbl.count
749 LOOP
750 -- add one more item in filter list
751 data.name := l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_NAME;
752 data.value := l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_VALUE;
753
754 filter_list.extend ();
755 filter_list (ind) := data;
756 ind := ind + 1;
757
758 -- append it to filter string
759 if l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_VALUE is not null then
760 if f_curr_row = 1 then
761 x_filter_string := l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_NAME
762 || '=' || l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_VALUE;
763 else
764 x_filter_string := x_filter_string || fnd_global.local_chr(38)
765 || l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_NAME
766 || '=' || l_pd_attrib_tbl (f_curr_row).ATTRIBUTE_VALUE;
767 end if;
768 end if;
769
770 END LOOP;
771 END IF;
772
773 x_filter_list := filter_list;
774
775 -- End of API Body
776
777 -- Standard check of p_commit.
778 IF FND_API.To_Boolean( p_commit ) THEN
779 COMMIT WORK;
780 END IF;
781 -- Standard call to get message count and if count is 1, get message info.
782 FND_MSG_PUB.Count_And_Get
783 (p_count => x_msg_count ,
784 p_data => x_msg_data
785 );
786 EXCEPTION
787 WHEN OTHERS THEN
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
789 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
790 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
791 END IF;
792 FND_MSG_PUB.Count_And_Get
793 (p_count => x_msg_count ,
794 p_data => x_msg_data
795 );
796 end get_filter_list;
797
798 procedure get_perz_data_attrib(
799 p_api_version_number IN NUMBER,
800 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
801 p_commit IN VARCHAR := FND_API.G_FALSE,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_msg_count OUT NOCOPY NUMBER,
804 x_msg_data OUT NOCOPY VARCHAR2,
805 p_user_id IN NUMBER := 0,
806 p_prof_name IN VARCHAR2 := NULL,
807 p_pd_id IN NUMBER,
808 p_pd_name IN VARCHAR2,
809 p_pd_type IN VARCHAR2,
810 p_one_attrib IN VARCHAR2 := FND_API.G_TRUE,
811 p_pd_attrib_name IN VARCHAR2 := NULL,
812 --x_pd_id OUT NOCOPY NUMBER,
813 --x_pd_name OUT NOCOPY VARCHAR2,
814 --x_pd_type OUT NOCOPY VARCHAR2,
815 --x_pd_desc OUT NOCOPY VARCHAR2,
816 x_pd_attrib_value OUT NOCOPY VARCHAR2,
817 x_pd_attrib_tbl OUT NOCOPY JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE
818 )
819 as
820 l_profile_id NUMBER;
821 l_profile_name VARCHAR2(60);
822 l_pd_id NUMBER;
823 l_pd_name VARCHAR2(60);
824 l_pd_type VARCHAR2(30);
825 out_perz_data_id NUMBER;
826 out_perz_data_name VARCHAR2(60);
827 out_perz_data_type VARCHAR2(30);
828 out_perz_data_desc VARCHAR2(240);
829 begin
830
831 -- Initialize API return status to success
832 x_return_status := FND_API.G_RET_STS_SUCCESS;
833 x_pd_attrib_value := NULL;
834
835 -- API Body
836
837 -- get the profile name first
838 if p_prof_name is NULL then
839 if p_pd_id = 0 then
840 -- add error message
841 RETURN;
842 else
843 -- get the profile name from user id
844 l_profile_name := G_USER_PROFILE_NAME || to_char(p_user_id);
845 end if;
846 else
847 l_profile_name := p_prof_name;
848 end if;
849
850 if p_pd_id is not NULL then
851 l_pd_id := p_pd_id;
852 end if;
853 if p_pd_name is not NULL then
854 l_pd_name := p_pd_name;
855 end if;
856 if p_pd_type is not NULL then
857 l_pd_type := p_pd_type;
858 end if;
859
860 if p_one_attrib = FND_API.G_TRUE and p_pd_attrib_name is NULL then
861 RETURN;
862 end if;
863
864 JTF_PERZ_DATA_PUB.Get_Perz_Data
865 (
866 p_api_version_number => p_api_version_number,
867 p_init_msg_list => p_init_msg_list,
868 p_application_id => get_app_id,
869 p_profile_id => l_profile_id,
870 p_profile_name => l_profile_name,
871 p_perz_data_id => l_pd_id,
872 p_perz_data_name => l_pd_name,
873 p_perz_data_type => l_pd_type,
874 x_perz_data_id => out_perz_data_id,
875 x_perz_data_name => out_perz_data_name,
876 x_perz_data_type => out_perz_data_type,
877 x_perz_data_desc => out_perz_data_desc,
878 x_data_attrib_tbl => x_pd_attrib_tbl,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data
882 );
883
884 if NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
885 RETURN;
886 end if;
887
888 if p_pd_attrib_name is not null then
889 for l_curr_row in 1..x_pd_attrib_tbl.count
890 loop
891 if x_pd_attrib_tbl(l_curr_row).ATTRIBUTE_NAME = p_pd_attrib_name then
892 x_pd_attrib_value := x_pd_attrib_tbl(l_curr_row).ATTRIBUTE_VALUE;
893 RETURN;
894 end if;
895 end loop;
896
897 if p_one_attrib = FND_API.G_TRUE then
898 -- the value does not exist
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 x_pd_attrib_value := null;
901 end if;
902 end if;
903
904 exception
905 WHEN OTHERS THEN
906 --ROLLBACK TO CS_Process_Order_Line;
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
908 FND_MSG_PUB.Count_And_Get
909 (p_count => x_msg_count ,
910 p_data => x_msg_data
911 );
912 end get_perz_data_attrib;
913
914
915
916 end IBU_HOME_PAGE_PVT;