[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_GRP
Source
1 PACKAGE BODY IBE_MSite_GRP AS
2 /* $Header: IBEGMSTB.pls 120.7 2006/07/03 10:32:33 apgupta ship $ */
3
4 master_msite_exists_exception EXCEPTION;
5 store_not_exists_exception exception;
6 msite_default_lang_missing EXCEPTION;
7 msite_default_org_missing EXCEPTION;
8 msite_default_currency_missing EXCEPTION;
9 msite_languages_missing EXCEPTION;
10 msite_orgs_missing EXCEPTION;
11 msite_currencies_missing EXCEPTION;
12
13
14 FUNCTION msite_default_lang_exists(p_msite_id IN NUMBER )
15 RETURN BOOLEAN
16 IS
17 CURSOR valid_msite_lang_cur(p_msite_id IN NUMBER) IS
18 select 1 from dual where exists (
19 select msite_id
20 from ibe_msites_b
21 where msite_id = p_msite_id
22 and default_language_code is not null );
23
24 l_exists NUMBER;
25 l_return_status Boolean := false;
26 BEGIN
27 OPEN valid_msite_lang_cur(p_msite_id);
28 FETCH valid_msite_lang_cur INTO l_exists;
29 IF valid_msite_lang_cur%FOUND THEN
30 l_return_status := true;
31 end if;
32 close valid_msite_lang_cur;
33 return l_return_status;
34 EXCEPTION
35 WHEN OTHERS THEN
36 IF valid_msite_lang_cur%ISOPEN THEN
37 close valid_msite_lang_cur;
38 END IF;
39 return false;
40 END msite_default_lang_exists;
41
42
43 FUNCTION msite_default_currency_exists(p_msite_id IN NUMBER )
44 RETURN BOOLEAN
45 IS
46 CURSOR valid_msite_currency_cur(p_msite_id IN NUMBER) IS
47 select 1 from dual where exists (
48 select msite_id from ibe_msites_b where
49 msite_id = p_msite_id and default_currency_code is not null );
50
51 l_exists NUMBER;
52 l_return_status Boolean := false;
53
54 BEGIN
55
56 OPEN valid_msite_currency_cur(p_msite_id);
57 FETCH valid_msite_currency_cur INTO l_exists;
58
59 IF valid_msite_currency_cur%FOUND THEN
60 l_return_status := true;
61 end if;
62
63 close valid_msite_currency_cur;
64
65 return l_return_status;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 IF valid_msite_currency_cur%ISOPEN THEN
70 close valid_msite_currency_cur;
71 END IF;
72 return false;
73 END msite_default_currency_exists;
74
75
76 FUNCTION msite_default_org_exists(p_msite_id IN NUMBER )
77 RETURN BOOLEAN
78 IS
79 CURSOR valid_msite_org_cur(p_msite_id IN NUMBER) IS
80 select 1 from dual where exists (
81 select msite_id from ibe_msites_b where
82 msite_id = p_msite_id and default_org_id is not null );
83
84 l_exists NUMBER;
85 l_return_status Boolean := false;
86
87 BEGIN
88
89 OPEN valid_msite_org_cur(p_msite_id);
90 FETCH valid_msite_org_cur INTO l_exists;
91
92 IF valid_msite_org_cur%FOUND THEN
93 l_return_status := true;
94 end if;
95
96 close valid_msite_org_cur;
97
98 return l_return_status;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 IF valid_msite_org_cur%ISOPEN THEN
103 close valid_msite_org_cur;
104 END IF;
105 return false;
106 END msite_default_org_exists;
107
108
109 FUNCTION valid_language(p_language VARCHAR2)
110 RETURN BOOLEAN
111 IS
112 CURSOR valid_language_cur(p_language varchar2) IS
113 select 1 from dual where exists (
114 select language_code from fnd_languages_vl where
115 language_code = p_language);
116 l_exists NUMBER;
117 l_return_status Boolean := true;
118
119 BEGIN
120
121 OPEN valid_language_cur(p_language);
122 FETCH valid_language_cur INTO l_exists;
123
124 IF valid_language_cur%NOTFOUND THEN
125 l_return_status := false;
126 end if;
127
128 close valid_language_cur;
129
130 if l_return_status = false then
131 raise FND_API.g_exc_error;
132 -----dbms_output.put_line('invalid languages:' || p_language);
133 END IF;
134
135 -----dbms_output.put_line('valid languages:' || p_language);
136 return l_return_status;
137
138 EXCEPTION
139 WHEN OTHERS THEN
140 IF valid_language_cur%ISOPEN THEN
141 close valid_language_cur;
142 END IF;
143 FND_MESSAGE.set_name('IBE','IBE_MSITE_LANG_INVLD');
144 FND_MESSAGE.set_token('0', p_language);
145 FND_MSG_PUB.ADD;
146 -----dbms_output.put_line('invalid languages');
147 return false;
148 END valid_language;
149
150
151 FUNCTION valid_orgid(p_orgid NUMBER)
152 RETURN BOOLEAN
153 IS
154 CURSOR valid_orgid_cur(p_orgid varchar2) IS
155 select 1 from dual where exists (
156 select organization_id from hr_all_organization_units where
157 organization_id = p_orgid);
158 l_exists NUMBER;
159 l_return_status Boolean := true;
160
161 BEGIN
162
163 OPEN valid_orgid_cur(p_orgid);
164 FETCH valid_orgid_cur INTO l_exists;
165 IF valid_orgid_cur%NOTFOUND THEN
166 l_return_status := false;
167 end if;
168
169 if l_return_status = false then
170 raise FND_API.g_exc_error;
171 end if;
172
173 -----dbms_output.put_line('invalid orgid:' || p_orgid);
174
175 return l_return_status;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 IF valid_orgid_cur%ISOPEN THEN
180 close valid_orgid_cur;
181 END IF;
182 FND_MESSAGE.set_name('IBE','IBE_MSITE_ORGID_INVLD');
183 FND_MESSAGE.set_token('0', p_orgid);
184 FND_MSG_PUB.ADD;
185 --dbms_output.put_line('invalid Org id');
186 return false;
187 END valid_orgid;
188
189
190 FUNCTION valid_currency(p_currency VARCHAR2)
191 RETURN BOOLEAN
192 IS
193 CURSOR valid_currency_cur(p_currency varchar2) IS
194 select 1 from dual where exists (
195 select currency_code from fnd_currencies_vl where
196 currency_code = p_currency);
197 l_exists NUMBER;
198 l_return_status Boolean := true;
199
200 BEGIN
201
202 OPEN valid_currency_cur(p_currency);
203 FETCH valid_currency_cur INTO l_exists;
204 IF valid_currency_cur%NOTFOUND THEN
205 l_return_status := false;
206 --dbms_output.put_line('error in currency code1:' || p_currency);
207 end if;
208
209 close valid_currency_cur;
210 if l_return_status = false then
211 raise FND_API.g_exc_error;
212 END IF;
213
214 return l_return_status;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 IF valid_currency_cur%ISOPEN THEN
219 close valid_currency_cur;
220 END IF;
221 FND_MESSAGE.set_name('IBE','IBE_MSITE_CURRENCY_INVLD');
222 FND_MESSAGE.set_token('0', p_currency);
223 FND_MSG_PUB.ADD;
224 --dbms_output.put_line('error in currency code');
225 return false;
226
227 END valid_currency;
228
229
230 FUNCTION valid_prc_lstids(p_currency VARCHAR2,
231 p_walkin_prclstid number,
232 p_registered_prclstid number,
233 p_bizpartner_prclstid NUMBER,
234 p_partner_prclstid NUMBER)
235 RETURN BOOLEAN
236 IS
237 CURSOR valid_currency_prclstid_cur(p_currency varchar2,
238 p_walkin_prclstid number,
239 p_registered_prclstid number,
240 p_bizpartner_prclstid NUMBER,
241 p_partner_prclstid NUMBER)
242 IS
243 select 1 from dual where exists (
244 select list_header_id from qp_list_headers_v where
245 currency_code = p_currency and
246 list_header_id in (p_walkin_prclstid,p_registered_prclstid,p_bizpartner_prclstid,p_partner_prclstid));
247
248 l_exists NUMBER;
249 l_return_status Boolean := true;
250
251 BEGIN
252
253 if p_walkin_prclstid is null or p_registered_prclstid is null or
254 p_bizpartner_prclstid is null or
255 p_partner_prclstid is null
256 then
257 FND_MESSAGE.set_name('IBE','IBE_MSITE_PRCLSTID_REQ');
258 FND_MESSAGE.set_token('CURR_CODE', p_currency);
259 FND_MSG_PUB.ADD;
260 raise FND_API.g_exc_error;
261 END IF;
262
263 OPEN valid_currency_prclstid_cur(p_currency,p_walkin_prclstid,p_registered_prclstid,
264 p_bizpartner_prclstid, p_partner_prclstid);
265 FETCH valid_currency_prclstid_cur INTO l_exists;
266
267
268 IF valid_currency_prclstid_cur%NOTFOUND THEN
269 l_return_status := false;
270 END IF;
271 close valid_currency_prclstid_cur;
272
273 if l_return_status = false
274 then
275 FND_MESSAGE.set_name('IBE','IBE_MSITE_PRCLSTID_INVLD');
276 FND_MESSAGE.set_token('WALKIN_ID',p_walkin_prclstid);
277 FND_MESSAGE.set_token('REG_ID' , p_registered_prclstid);
278 FND_MESSAGE.set_token('BIZ_ID' , p_bizpartner_prclstid);
279 FND_MESSAGE.set_token('PARTNER_ID' , p_partner_prclstid);
280 FND_MESSAGE.set_token('CURR_CODE',p_currency);
281 FND_MSG_PUB.ADD;
282 end if;
283
284 return l_return_status;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 IF valid_currency_prclstid_cur%ISOPEN THEN
289 close valid_currency_prclstid_cur;
290 END IF;
291 return FALSE;
292 END valid_prc_lstids;
293
294
295 PROCEDURE save_msite(
296 p_api_version IN NUMBER,
297 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
298 p_commit IN VARCHAR2 := FND_API.g_false,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 p_msite_rec IN OUT NOCOPY Msite_REC_TYPE )
303 IS
304 l_api_name CONSTANT VARCHAR2(30) := 'save_msite';
305 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
306
307 l_operation_type VARCHAR2(10);
308
309 l_msite_id NUMBER;
310 walkin_allowed_code VARCHAR2(1);
311 l_atp_check_flag VARCHAR2(1);
312 l_master_exists NUMBER;
313 l_msite_master_flag VARCHAR2(1);
314 l_store_id NUMBER := NULL;
315 l_exists NUMBER ;
316 l_root_section_flag VARCHAR2(1) := FND_API.g_false;
317 l_return_status VARCHAR2(1);
318 l_msg_count NUMBER;
319 l_msg_data VARCHAR2(80);
320 l_root_section_id NUMBER := NULL;
321 l_resp_access_flag VARCHAR2(1);
322 l_party_access_code VARCHAR2(1);
323
324 l_cur_root_sct_id NUMBER;
325 l_sct_root_flag NUMBER;
326
327 l_payment_thresh_enable_flag VARCHAR2(1);
328 l_enable_traffic_filter VARCHAR2(1);
329 l_reporting_status VARCHAR2(1);
330
331 l_debug VARCHAR2(1);
332
333 CURSOR msite_id_seq IS
334 SELECT ibe_msites_b_s1.NEXTVAL
335 FROM DUAL;
336
337 /*CURSOR store_id_cur IS
338 select store_id from ibe_stores_b
339 where rownum < 2 ;*/
340
341 CURSOR master_msite_any_cur IS
342 select 1 from dual where exists (
343 select msite_id from ibe_msites_b
344 where master_msite_flag = 'Y') ;
345
346 CURSOR master_msite_cur(p_msite_id IN NUMBER) IS
347 select 1 from dual
348 where exists (
349 select msite_id
350 from ibe_msites_b
351 where master_msite_flag = 'Y'
352 AND msite_id <> p_msite_id);
353
354 CURSOR yes_no_cur (p_code in varchar2) IS
355 select 1 from dual where exists (
356 select lookup_code
357 from fnd_lookup_values_vl
358 where lookup_type='YES_NO'
359 and lookup_code=p_code);
360
361 CURSOR C_party_access_code (p_code in varchar2) IS
362 select 1 from fnd_lookup_values_vl
363 where lookup_type = 'IBE_PARTY_ACCESS_CODE'
364 and lookup_code = p_code ;
365 BEGIN
366 l_operation_type:= 'INSERT';
367 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
368
369 IF (l_debug = 'Y') THEN
370 IBE_UTIL.Debug('Start IBE_MSITE_GRP.Save_Msite');
371 END IF;
372
373 --------------------- initialize -----------------------+
374 SAVEPOINT save_msite;
375
376
377 IF NOT FND_API.compatible_api_call(
378 g_api_version,
379 p_api_version,
380 l_api_name,
381 g_pkg_name ) THEN
382 RAISE FND_API.g_exc_unexpected_error;
383 END IF;
384
385
386 IF FND_API.to_boolean(p_init_msg_list) THEN
387 FND_MSG_PUB.initialize;
388 END IF;
389
390
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392
393 --- Check if the msite_id exists
394 IF p_msite_rec.msite_id IS NOT NULL AND
395 p_msite_rec.msite_id <> FND_API.g_miss_num
396 THEN
397 IF (l_debug = 'Y') THEN
398 IBE_UTIL.Debug('[IBE_MSITE_GRP]Minisite id is passed');
399 END IF;
400 if ibe_dspmgrvalidation_grp.check_msite_exists(
401 p_msite_rec.msite_id, p_msite_rec.Object_version_Number) = false
402 then
403 IF (l_debug = 'Y') THEN
404 IBE_UTIL.Debug('[IBE_MSITE_GRP]Raising Exception: MiniSite Exists');
405 END IF;
406 raise FND_API.g_exc_error;
407 end if;
408
409 l_operation_type:='UPDATE';
410
411 IF (l_debug = 'Y') THEN
412 IBE_UTIL.Debug('[IBE_MSITE_GRP]Operation is an Update');
413 END IF;
414
415 END IF;
416 if p_msite_rec.msite_root_section_id <> FND_API.g_miss_num AND
417 p_msite_rec.msite_root_section_id is not null then
418
419 if ibe_dspmgrvalidation_grp.check_root_section_exists(
420 p_msite_rec.msite_root_section_id) = false
421 then
422 IF (l_debug = 'Y') THEN
423 IBE_UTIL.Debug('Raising Exception:Root Section Does not exist');
424 END IF;
425 raise FND_API.g_exc_error;
426 end if;
427
428 l_root_section_flag := FND_API.g_true;
429 l_root_section_id := p_msite_rec.msite_root_section_id;
430 else
431 --- If the minisite is a new one, then root section id can be null ,
432 --todo
433 if (p_msite_rec.msite_id is not null and
434 p_msite_rec.enable_for_store = FND_API.g_true) or
435 p_msite_rec.enable_for_store = FND_API.g_true
436 then
437 FND_MESSAGE.set_name('IBE','IBE_MSITE_RSECID_INVLD');
438 FND_MSG_PUB.ADD;
439 raise FND_API.g_exc_error;
440 end if;
441 end if;
442
443 if p_msite_rec.msite_master_flag = FND_API.g_true then
444 raise master_msite_exists_exception;
445 else
446 l_msite_master_flag := 'N';
447 end if;
448
449
450 --dbms_output.put_line('passed master mini site flag teste ' );
451 -- Check if the access_name for a minisite is unique
452 If ( p_msite_rec.msite_id IS NULL OR
453 p_msite_rec.msite_id = FND_API.g_miss_num) AND
454 (p_msite_rec.access_name IS NOT NULL AND
455 p_msite_rec.access_name <> FND_API.G_MISS_CHAR) Then
456 If Ibe_Dspmgrvalidation_Grp.Check_Msite_Accessname(
457 p_access_name => p_msite_rec.access_name)= FALSE
458 Then
459 IF (l_debug = 'Y') THEN
460 IBE_UTIL.Debug('Exception:Access Name is not Unique');
461 END IF;
462 Raise FND_API.G_EXC_ERROR ;
463 End If;
464 End If;
465
466 if p_msite_rec.enable_for_store = FND_API.g_true then
467 --dbms_output.put_line('****************enabled for store is to true ' );
468 /*OPEN store_id_cur;
469 fetch store_id_cur into l_store_id;
470 if store_id_cur%NOTFOUND then
471 close store_id_cur;
472 raise store_not_exists_exception;
473 end if;
474 close store_id_cur;*/
475
476 if ibe_dspmgrvalidation_grp.check_root_section_exists(
477 p_msite_rec.msite_root_section_id) = false
478 then
479 raise FND_API.g_exc_error;
480 end if;
481
482 if msite_default_lang_exists(p_msite_rec.msite_id) = false
483 then
484 raise msite_default_lang_missing;
485 end if;
486
487 if msite_default_currency_exists(p_msite_rec.msite_id) = false
488 then
489 raise msite_default_currency_missing;
490 end if;
491 /*** REDUNDANT AS ORG ID WILL BE DETERMINED BY RESPONSIBILITY
492 if msite_default_org_exists(p_msite_rec.msite_id) = false then
493 raise msite_default_org_missing;
494 end if;
495 ******/
496 end if;
497
498 walkin_allowed_code := p_msite_rec.walkin_allowed_code;
499 OPEN yes_no_cur(walkin_allowed_code);
500 FETCH yes_no_cur INTO l_exists;
501 IF yes_no_cur%NOTFOUND THEN
502 walkin_allowed_code := 'N';
503 END IF;
504 close yes_no_cur;
505
506 l_atp_check_flag := p_msite_rec.atp_check_flag;
507 OPEN yes_no_cur(l_atp_check_flag);
508 FETCH yes_no_cur INTO l_exists;
509 IF yes_no_cur%NOTFOUND THEN
510 l_atp_check_flag := 'N';
511 END IF;
512 close yes_no_cur;
513
514 l_resp_access_flag := p_msite_rec.resp_access_flag;
515 IF (((l_operation_type = 'UPDATE') AND
516 (l_resp_access_flag IS NOT NULL AND
517 l_resp_access_flag <> FND_API.G_MISS_char)) OR
518 l_operation_type = 'INSERT')
519 THEN
520 OPEN yes_no_cur(l_resp_access_flag );
521 FETCH yes_no_cur INTO l_exists;
522 IF yes_no_cur%NOTFOUND THEN
523 l_resp_access_flag := 'N';
524 END IF;
525 close yes_no_cur;
526 END IF;
527
528 l_party_access_code := p_msite_rec.party_access_code;
529 IF (((l_operation_type = 'UPDATE') AND
530 (l_party_access_code IS NOT NULL AND
531 l_party_access_code <> FND_API.G_MISS_char)) OR
532 l_operation_type = 'INSERT')
533 THEN
534 OPEN C_party_access_code(l_party_access_code );
535 FETCH C_party_access_code INTO l_exists;
536 IF C_party_access_code%NOTFOUND THEN
537 l_party_access_code := 'A';
538 END IF;
539 close C_party_access_code;
540 END IF;
541
542 IF l_operation_type = 'INSERT'
543 THEN
544 OPEN msite_id_seq;
545 FETCH msite_id_seq INTO l_msite_id;
546 CLOSE msite_id_seq;
547 END IF;
548
549 l_sct_root_flag := 1;
550
551 --added by YAXU on 06/11/2002 for payment threshold
552 l_payment_thresh_enable_flag := p_msite_rec.payment_threshold_enable_flag;
553 OPEN yes_no_cur(l_payment_thresh_enable_flag);
554 FETCH yes_no_cur INTO l_exists;
555 IF yes_no_cur%NOTFOUND THEN
556 l_payment_thresh_enable_flag := 'N';
557 END IF;
558 close yes_no_cur;
559
560 l_enable_traffic_filter := p_msite_rec.enable_traffic_filter;
561 IF l_enable_traffic_filter <> 'Y'
562 THEN
563 l_enable_traffic_filter := 'N';
564 END IF;
565
566 l_reporting_status := p_msite_rec.reporting_status;
567 IF l_reporting_status <> 'Y'
568 THEN
569 l_reporting_status := 'N';
570 END IF;
571
572
573 IF l_operation_type = 'INSERT'
574 THEN
575 IF (l_debug = 'Y') THEN
576 IBE_UTIL.Debug('Before insert into IBE_MSITES_B');
577 END IF;
578 INSERT INTO IBE_MSITES_B (
579 MSITE_ID,
580 OBJECT_VERSION_NUMBER,
581 LAST_UPDATE_DATE,
582 LAST_UPDATED_BY,
583 CREATION_DATE,
584 CREATED_BY,
585 LAST_UPDATE_LOGIN,
586 START_DATE_ACTIVE,
587 END_DATE_ACTIVE,
588 DEFAULT_DATE_FORMAT,
589 PROFILE_ID,
590 MASTER_MSITE_FLAG,
591 WALKIN_ALLOWED_FLAG,
592 STORE_ID,
593 ATP_CHECK_FLAG,
594 MSITE_ROOT_SECTION_ID,
595 RESP_ACCESS_FLAG ,
596 PARTY_ACCESS_CODE ,
597 ACCESS_NAME,
598 URL,
599 THEME_ID,
600 PAYMENT_THRESHOLD_ENABLE_FLAG,
601 DOMAIN_NAME,
602 ENABLE_TRAFFIC_FILTER,
603 REPORTING_STATUS,
604 SITE_TYPE)
605 VALUES (
606 l_msite_id,
607 1,
608 SYSDATE,
609 FND_GLOBAL.user_id,
610 SYSDATE,
611 FND_GLOBAL.user_id,
612 FND_GLOBAL.user_id,
613 p_msite_rec.start_date_active,
614 p_msite_rec.end_date_active,
615 p_msite_rec.date_format,
616 p_msite_rec.profile_id,
617 l_msite_master_flag,
618 walkin_allowed_code,
619 l_store_id,
620 l_atp_check_flag,
621 l_root_section_id,
622 l_resp_access_flag ,
623 l_party_access_code ,
624 p_msite_rec.access_name ,
625 DECODE(p_msite_rec.url,FND_API.G_MISS_CHAR,null,p_msite_rec.url) ,
626 DECODE(p_msite_rec.theme_id,FND_API.G_MISS_NUM,null,p_msite_rec.theme_id),
627 l_payment_thresh_enable_flag,
628 p_msite_rec.domain_name,
629 l_enable_traffic_filter,
630 l_reporting_status,
631 p_msite_rec.site_type);
632 IF (l_debug = 'Y') THEN
633 IBE_UTIL.Debug('After insert into IBE_MSITES_B');
634 END IF;
635 --- Insert into the TL table
636 IF (l_debug = 'Y') THEN
637 IBE_UTIL.Debug('Before insert into IBE_MSITES_TL');
638 END IF;
639
640 insert into IBE_MSITES_TL (
641 MSITE_ID,
642 LAST_UPDATE_DATE,
643 LAST_UPDATED_BY,
644 CREATION_DATE,
645 CREATED_BY,
646 LAST_UPDATE_LOGIN,
647 OBJECT_VERSION_NUMBER,
648 MSITE_NAME,
649 MSITE_DESCRIPTION,
650 LANGUAGE,
651 SOURCE_LANG ) select
652 l_msite_id,
653 sysdate,
654 FND_GLOBAL.user_id,
655 sysdate,
656 FND_GLOBAL.user_id,
657 FND_GLOBAL.user_id,
658 1,
659 p_msite_rec.Display_name,
660 p_msite_rec.description,
661 L.LANGUAGE_CODE,
662 userenv('LANG')
663 from FND_LANGUAGES L
664 where L.INSTALLED_FLAG in ('I', 'B')
665 and not exists(
666 select NULL
667 from IBE_MSITES_TL T
668 where T.MSITE_ID =l_msite_id
669 and T.LANGUAGE = L.LANGUAGE_CODE);
670 IF (l_debug = 'Y') THEN
671 IBE_UTIL.Debug('After insert into IBE_MSITES_TL');
672 END IF;
673
674 p_msite_rec.msite_id := l_msite_id;
675 p_msite_rec.object_version_number := 1;
676
677 ELSIF l_operation_type = 'UPDATE'
678 THEN
679 -- added the following code for globalisation -- ssridhar
680 -- RESP_ACCESS_FLAG = l_resp_access_flag ,
681 -- PARTY_ACCESS_CODE = l_party_access_code ,
682 -- ACCESS_NAME = p_msite_rec.access_name
683 --Bug fix for not updating end_date_active
684
685 IF l_resp_access_flag = fnd_api.g_miss_char
686 THEN
687 l_resp_access_flag := NULL;
688 END IF;
689
690 IF l_party_access_code = fnd_api.g_miss_char
691 THEN
692 l_party_access_code := NULL;
693 END IF;
694
695 IF p_msite_rec.access_name = fnd_api.g_miss_char
696 THEN
697 p_msite_rec.access_name := NULL;
698 END IF;
699
700 IF p_msite_rec.start_date_active = fnd_api.g_miss_date
701 THEN
702 p_msite_rec.start_date_active := NULL;
703 END IF;
704
705 IF p_msite_rec.end_date_active = fnd_api.g_miss_date
706 THEN
707 p_msite_rec.end_date_active := NULL;
708 END IF;
709
710 IF p_msite_rec.url = fnd_api.g_miss_char
711 THEN
712 p_msite_rec.url := NULL;
713 END IF;
714
715 IF p_msite_rec.theme_id = fnd_api.g_miss_num
716 THEN
717 p_msite_rec.theme_id := NULL;
718 END IF;
719
720
721 --
722 -- Check if the root section of the p_mini_site_id equals to the p_section_id.
723 -- If it does, then don't do the association again
724 -- Added by YAXU on -3/27/2002
725 --
726
727 SELECT msite_root_section_id into l_cur_root_sct_id
728 FROM ibe_msites_b
729 WHERE msite_id = p_msite_rec.msite_id;
730
731
732 IF l_cur_root_sct_id = p_msite_rec.msite_root_section_id
733 THEN
734 l_sct_root_flag :=0;
735 END IF;
736
737
738 IF (l_debug = 'Y') THEN
739 IBE_UTIL.Debug('Before updating IBE_MSITES_B');
740 END IF;
741
742 UPDATE IBE_MSITES_B SET
743 LAST_UPDATE_DATE = SYSDATE,
744 LAST_UPDATED_BY = FND_GLOBAL.user_id,
745 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
746 PROFILE_ID = p_msite_rec.profile_id,
747 DEFAULT_DATE_FORMAT = p_msite_rec.date_format ,
748 MASTER_MSITE_FLAG = l_msite_master_flag,
749 WALKIN_ALLOWED_FLAG = walkin_allowed_code,
750 STORE_ID = l_store_id ,
751 ATP_CHECK_FLAG = l_atp_check_flag,
752 MSITE_ROOT_SECTION_ID = l_root_section_id ,
753 OBJECT_VERSION_NUMBER = p_msite_rec.object_version_number + 1,
754 RESP_ACCESS_FLAG = NVL(l_resp_access_flag,resp_access_flag),
755 PARTY_ACCESS_CODE = nvl(l_party_access_code,party_access_code),
756 ACCESS_NAME = p_msite_rec.access_name,
757 START_DATE_ACTIVE =
758 nvl(p_msite_rec.start_date_active,start_date_active),
759 END_DATE_ACTIVE = p_msite_rec.end_date_active ,
760 URL = NVL(p_msite_rec.url,url),
761 THEME_ID = NVL(p_msite_rec.theme_id,theme_id),
762 PAYMENT_THRESHOLD_ENABLE_FLAG = l_payment_thresh_enable_flag,
763 DOMAIN_NAME = p_msite_rec.domain_name,
764 ENABLE_TRAFFIC_FILTER = p_msite_rec.enable_traffic_filter,
765 REPORTING_STATUS = p_msite_rec.reporting_status,
766 SITE_TYPE = p_msite_rec.site_type
767 WHERE
768 MSITE_ID = p_msite_rec.msite_id and
769 OBJECT_VERSION_NUMBER = p_msite_rec.object_version_number ;
770
771 IF (l_debug = 'Y') THEN
772 IBE_UTIL.Debug('After updating IBE_MSITES_B');
773 IBE_UTIL.Debug('Before updating IBE_MSITES_TL');
774 END IF;
775
776 UPDATE IBE_MSITES_TL SET
777 MSITE_NAME = decode( p_msite_rec.Display_name, FND_API.G_MISS_CHAR,
778 MSITE_NAME, p_msite_rec.Display_name),
779 MSITE_DESCRIPTION = decode( p_msite_rec.description,
780 FND_API.G_MISS_CHAR, MSITE_DESCRIPTION, p_msite_rec.description),
781 LAST_UPDATE_DATE = SYSDATE,
782 LAST_UPDATED_BY = FND_GLOBAL.user_id,
783 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
784 OBJECT_VERSION_NUMBER= p_msite_rec.object_version_number +1 ,
785 SOURCE_LANG = userenv('LANG')
786 where msite_id = p_msite_rec.msite_id
787 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
788
789 IF (l_debug = 'Y') THEN
790 IBE_UTIL.Debug('After updating IBE_MSITES_TL');
791 END IF;
792
793 p_msite_rec.object_version_number :=
794 p_msite_rec.object_version_number + 1;
795
796 END IF;
797
798 IF(l_sct_root_flag = 1)
799 THEN
800 ----if p_msite_rec.enable_for_store = FND_API.g_true and
801 if l_root_section_flag = FND_API.g_true
802 then
803 IF (l_debug = 'Y') THEN
804 IBE_UTIL.Debug('Before calling IBE_DSP_HIERARCHY_SETUP_PVT.' || 'Associate_Root_Sct_To_MSite ');
805 END IF;
806
807 IBE_DSP_HIERARCHY_SETUP_PVT.Associate_Root_Sct_To_MSite
808 (
809 p_api_version => p_api_version,
810 p_init_msg_list => FND_API.g_false,
811 p_commit => FND_API.g_false,
812 p_validation_level => 100,
813 p_section_id => p_msite_rec.msite_root_section_id,
814 p_mini_site_id => p_msite_rec.msite_id,
815 x_return_status => l_return_status,
816 x_msg_count => x_msg_count,
817 x_msg_data => x_msg_data
818 );
819
820 IF (l_debug = 'Y') THEN
821 IBE_UTIL.Debug('After calling IBE_DSP_HIERARCHY_SETUP_PVT.' || 'Associate_Root_Sct_To_MSite ');
822 END IF;
823
824 if l_return_status = FND_API.G_RET_STS_SUCCESS then
825 update IBE_MSITES_B set msite_root_section_id = l_root_section_id ,
826 store_id = l_store_id
827 where msite_id=p_msite_rec.msite_id;
828 else
829 raise FND_API.g_exc_error;
830 end if;
831 end if;
832 END IF;
833
834 --dbms_output.put_line('Operation is successful ' );
835 --- Check if the caller requested to commit ,
836 --- If p_commit set to true, commit the transaction
837 IF FND_API.to_boolean(p_commit) THEN
838 COMMIT;
839 END IF;
840
841 x_return_status := FND_API.G_RET_STS_SUCCESS;
842
843 FND_MSG_PUB.count_and_get(
844 p_encoded => FND_API.g_false,
845 p_count => x_msg_count,
846 p_data => x_msg_data
847 );
848
849 EXCEPTION
850
851 WHEN FND_API.g_exc_error THEN
852 ROLLBACK TO save_msite;
853 x_return_status := FND_API.g_ret_sts_error;
854 FND_MSG_PUB.count_and_get(
855 p_encoded => FND_API.g_false,
856 p_count => x_msg_count,
857 p_data => x_msg_data
858 );
859
860 WHEN FND_API.g_exc_unexpected_error THEN
861 ROLLBACK TO save_msite;
862 x_return_status := FND_API.g_ret_sts_unexp_error ;
863 --dbms_output.put_line('unexpected error raised');
864 FND_MSG_PUB.count_and_get(
865 p_encoded => FND_API.g_false,
866 p_count => x_msg_count,
867 p_data => x_msg_data
868 );
869
870 WHEN master_msite_exists_exception THEN
871 ROLLBACK TO save_msite;
872 x_return_status := FND_API.g_ret_sts_error ;
873 FND_MESSAGE.set_name('IBE','IBE_MSITE_MASTER_EXISTS');
874 FND_MSG_PUB.ADD;
875 --dbms_output.put_line('master_msite error raised');
876 FND_MSG_PUB.count_and_get(
877 p_encoded => FND_API.g_false,
878 p_count => x_msg_count,
879 p_data => x_msg_data
880 );
881
882 WHEN store_not_exists_exception THEN
883 ROLLBACK TO save_msite;
884 x_return_status := FND_API.g_ret_sts_error ;
885 FND_MSG_PUB.count_and_get(
886 p_encoded => FND_API.g_false,
887 p_count => x_msg_count,
888 p_data => x_msg_data
889 );
890
891 WHEN msite_default_org_missing THEN
892 ROLLBACK TO save_msite;
893 x_return_status := FND_API.g_ret_sts_error;
894 FND_MESSAGE.set_name('IBE','IBE_MSITE_DEF_ORG_REQ');
895 FND_MESSAGE.set_token('ID', p_msite_rec.msite_id);
896 FND_MSG_PUB.ADD;
897 FND_MSG_PUB.count_and_get(
898 p_encoded => FND_API.g_false,
899 p_count => x_msg_count,
900 p_data => x_msg_data
901 );
902 WHEN msite_default_currency_missing THEN
903 ROLLBACK TO save_msite;
904 x_return_status := FND_API.g_ret_sts_error;
905 FND_MESSAGE.set_name('IBE','IBE_MSITE_DEF_CURR_REQ');
906 FND_MESSAGE.set_token('ID', p_msite_rec.msite_id);
907 FND_MSG_PUB.ADD;
908 FND_MSG_PUB.count_and_get(
909 p_encoded => FND_API.g_false,
910 p_count => x_msg_count,
911 p_data => x_msg_data
912 );
913
914 WHEN msite_default_lang_missing THEN
915 ROLLBACK TO save_msite;
916 x_return_status := FND_API.g_ret_sts_error;
917 FND_MESSAGE.set_name('IBE','IBE_MSITE_DEF_LANG_REQ');
918 FND_MESSAGE.set_token('ID', p_msite_rec.msite_id);
919 FND_MSG_PUB.ADD;
920 FND_MSG_PUB.count_and_get(
921 p_encoded => FND_API.g_false,
922 p_count => x_msg_count,
923 p_data => x_msg_data
924 );
925
926 WHEN OTHERS THEN
927 ROLLBACK TO save_msite;
928 x_return_status := FND_API.g_ret_sts_unexp_error ;
929 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
930 THEN
931 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
932 END IF;
933 FND_MSG_PUB.count_and_get(
934 p_encoded => FND_API.g_false,
935 p_count => x_msg_count,
936 p_data => x_msg_data
937 );
938
939 END save_msite;
940
941 --
942 -- Need to put into event handler to flush minisite information
943 -- And minisite responsibility cache
944 -- MerchantMinisiteMgr.flushMinisite("UPDATE", lmstFlushArr);
945 -- MinisiteResponsibilitiesCacheManager.invalidate();
946 PROCEDURE duplicate_msite(
947 p_api_version IN NUMBER,
948 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
949 p_commit IN VARCHAR2 := FND_API.g_false,
950 p_default_language_code IN varchar2,
951 p_default_currency_code IN varchar2,
952 p_walkin_pricing_id IN number,
953 x_minisite_id OUT NOCOPY number,
954 x_version_number OUT NOCOPY NUMBER,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2,
958 p_msite_rec IN OUT NOCOPY MSITE_REC_TYPE)
959
960 IS
961 l_api_version NUMBER := 1.0;
962 l_api_name VARCHAR2(50);
963 l_return_status VARCHAR2(1);
964 l_msg_count NUMBER;
965 l_msg_data VARCHAR2(4000);
966 l_debug VARCHAR2(1);
967
968 l_i NUMBER;
969 -- For Minisite
970 l_party_access_code VARCHAR2(30);
971
972 -- For language
973 l_language_code VARCHAR2(4);
974 l_enable_flag VARCHAR2(3);
975 l_def_found NUMBER;
976 -- For currency and price list
977 l_currency_code VARCHAR2(15);
978 l_bizpartner_prc_listid NUMBER;
979 l_partner_prc_listid NUMBER;
980 l_registered_prc_listid NUMBER;
981 l_walkin_prc_listid NUMBER;
982 l_orderable_limit NUMBER;
983 l_payment_threshold NUMBER;
984
985 -- Responsibility code and group
986 l_copy VARCHAR2(240);
987 l_responsibility_id NUMBER;
988 l_application_id NUMBER;
989 l_start_date_active DATE;
990 l_end_date_active DATE;
991 l_sort_order NUMBER;
992 l_display_name VARCHAR2(240);
993 l_group_code VARCHAR2(30);
994 l_prev_msite_resp_id NUMBER;
995 l_msite_resp_id NUMBER;
996 x_msite_resp_id NUMBER;
997
998 -- Access Restriction
999 l_party_id NUMBER;
1000 l_party_ids JTF_NUMBER_TABLE;
1001 l_start_date_actives JTF_DATE_TABLE;
1002 l_end_date_actives JTF_DATE_TABLE;
1003 l_msite_prty_accss_ids JTF_NUMBER_TABLE;
1004 l_duplicate_association_status JTF_VARCHAR2_TABLE_100;
1005 l_is_any_duplicate_status VARCHAR2(1);
1006
1007 l_msite_id NUMBER;
1008 l_version_number NUMBER;
1009 l_msite_rec IBE_MSite_GRP.Msite_REC_TYPE;
1010 l_msite_languages_tbl IBE_MSite_GRP.msite_languages_tbl_type;
1011 l_msite_currencies_tbl IBE_MSite_GRP.MSITE_CURRENCIES_TBL_TYPE;
1012
1013 CURSOR c_get_msite_detail_csr(c_msite_id NUMBER) IS
1014 select party_access_code
1015 from ibe_msites_b
1016 where msite_id = c_msite_id;
1017
1018 CURSOR c_get_msite_langs_csr(c_msite_id NUMBER) IS
1019 select language_code, enable_flag
1020 from ibe_msite_languages
1021 where msite_id = c_msite_id
1022 order by language_code;
1023
1024 CURSOR c_get_msite_pricing_csr(c_msite_id NUMBER) IS
1025 select m.currency_code, m.bizpartner_prc_listid,
1026 m.registered_prc_listid, m.walkin_prc_listid,
1027 m.orderable_limit, m.payment_threshold,m.partner_prc_listid
1028 from ibe_msite_currencies m
1029 where m.msite_id = c_msite_id
1030 ORDER BY m.currency_code;
1031
1032 CURSOR c_get_msite_resp_csr(c_msite_id NUMBER) IS
1033 SELECT MR.msite_resp_id, MR.responsibility_id, MR.application_id,
1034 MR.display_name, MR.start_date_active, MR.end_date_active,
1035 MR.sort_order, MR.group_code
1036 FROM ibe_msite_resps_vl MR
1037 WHERE MR.msite_id = c_msite_id
1038 ORDER BY MR.msite_resp_id, MR.group_code;
1039
1040 CURSOR c_get_msite_access_csr(c_msite_id NUMBER) IS
1041 SELECT party_id, start_date_active, end_date_active
1042 FROM ibe_msite_prty_accss
1043 WHERE msite_id = c_msite_id;
1044 BEGIN
1045 SAVEPOINT DUPLICATE_MSITE_SAVE;
1046 l_api_name:= 'duplicate_msite';
1047 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
1048
1049 IF (l_debug = 'Y') THEN
1050 IBE_UTIL.debug('duplicate_msite Starts +');
1051 IBE_UTIL.debug('p_api_version = '||p_api_version);
1052 IBE_UTIL.debug('p_init_msg_list = '||p_init_msg_list);
1053 IBE_UTIL.debug('p_commit = '||p_commit);
1054 IBE_UTIL.debug('p_source_msite_id = '||p_msite_rec.msite_id);
1055 IBE_UTIL.debug('p_display_name = '||p_msite_rec.display_name);
1056 IBE_UTIL.debug('p_access_name = '||p_msite_rec.access_name);
1057 IBE_UTIL.debug('p_description = '||p_msite_rec.description);
1058 IBE_UTIL.debug('p_default_language_code = '||p_default_language_code);
1059 IBE_UTIL.debug('p_default_currency_code = '||p_default_currency_code);
1060 IBE_UTIL.debug('p_walkin_pricing_id = '||p_walkin_pricing_id);
1061 IBE_UTIL.debug('p_start_date_active = '||p_msite_rec.start_date_active);
1062 IBE_UTIL.debug('p_end_date_active = '||p_msite_rec.end_date_active);
1063 IBE_UTIL.debug('p_msite_root_section_id = '||p_msite_rec.msite_root_section_id);
1064 IBE_UTIL.debug('p_walkin_allowed_code = '||p_msite_rec.walkin_allowed_code);
1065 IBE_UTIL.debug('p_atp_check_flag = '||p_msite_rec.atp_check_flag);
1066 IBE_UTIL.debug('p_resp_access_flag = '||p_msite_rec.resp_access_flag);
1067 IBE_UTIL.debug('p_payment_thresh_enable_flag = '||p_msite_rec.payment_threshold_enable_flag);
1068 IBE_UTIL.debug('p_domain_name = '||p_msite_rec.domain_name);
1069 IBE_UTIL.debug('p_enable_traffic_filter = '||p_msite_rec.enable_traffic_filter);
1070 IBE_UTIL.debug('p_reporting_status = '||p_msite_rec.reporting_status);
1071 IBE_UTIL.debug('p_site_type = '||p_msite_rec.site_type);
1072 END IF;
1073 IF NOT FND_API.Compatible_API_Call(l_api_version,
1074 p_api_version,
1075 l_api_name,
1076 G_PKG_NAME) THEN
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF;
1079 IF FND_API.to_Boolean(p_init_msg_list) THEN
1080 FND_MSG_PUB.initialize;
1081 END IF;
1082 -- Initialize API return status to SUCCESS
1083 x_return_status := FND_API.G_RET_STS_SUCCESS;
1084
1085 OPEN c_get_msite_detail_csr(p_msite_rec.msite_id);
1086 FETCH c_get_msite_detail_csr INTO l_party_access_code;
1087 CLOSE c_get_msite_detail_csr;
1088
1089 -- Validate on minisite main information
1090 -- Duplicate minisite main information
1091 l_msite_rec.msite_id := null;
1092 l_msite_rec.object_version_number := null;
1093 l_msite_rec.display_name := p_msite_rec.display_name;
1094 l_msite_rec.access_name := p_msite_rec.access_name;
1095 l_msite_rec.description := p_msite_rec.description;
1096 l_msite_rec.start_date_active := p_msite_rec.start_date_active;
1097 l_msite_rec.end_date_active := p_msite_rec.end_date_active;
1098 l_msite_rec.party_access_code := l_party_access_code;
1099 l_msite_rec.msite_root_section_id := p_msite_rec.msite_root_section_id;
1100 l_msite_rec.walkin_allowed_code := p_msite_rec.walkin_allowed_code;
1101 l_msite_rec.atp_check_flag := p_msite_rec.atp_check_flag;
1102 l_msite_rec.resp_access_flag := p_msite_rec.resp_access_flag;
1103 l_msite_rec.payment_threshold_enable_flag := p_msite_rec.payment_threshold_enable_flag;
1104 l_msite_rec.profile_id := null;
1105 l_msite_rec.date_format := null;
1106 l_msite_rec.msite_master_flag := FND_API.g_false;
1107 l_msite_rec.enable_for_store := null;
1108 l_msite_rec.url := null;
1109 l_msite_rec.theme_id := null;
1110 l_msite_rec.enable_traffic_filter := p_msite_rec.enable_traffic_filter;
1111 l_msite_rec.reporting_status := p_msite_rec.reporting_status;
1112 l_msite_rec.site_type := p_msite_rec.site_type;
1113 IBE_MSite_GRP.save_msite(
1114 p_api_version => l_api_version,
1115 p_init_msg_list => FND_API.g_false,
1116 p_commit => FND_API.g_false,
1117 x_return_status => l_return_status,
1118 x_msg_count => l_msg_count,
1119 x_msg_data => l_msg_data,
1120 p_msite_rec => l_msite_rec);
1121 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1122 IF (l_debug = 'Y') THEN
1123 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite');
1124 for i in 1..l_msg_count loop
1125 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1126 IBE_UTIL.debug(l_msg_data);
1127 end loop;
1128 END IF;
1129 RAISE FND_API.G_EXC_ERROR;
1130 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1131 IF (l_debug = 'Y') THEN
1132 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite');
1133 for i in 1..l_msg_count loop
1134 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1135 IBE_UTIL.debug(l_msg_data);
1136 end loop;
1137 END IF;
1138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139 END IF;
1140 l_msite_id := l_msite_rec.msite_id;
1141 l_version_number := l_msite_rec.Object_Version_Number;
1142 x_minisite_id := l_msite_rec.msite_id;
1143 x_version_number := l_msite_rec.Object_Version_Number;
1144
1145 -- Get Language list of the source minisite
1146 -- And set it into new minisite
1147 l_i := 1;
1148 l_def_found := 0;
1149 OPEN c_get_msite_langs_csr(p_msite_rec.msite_id);
1150 LOOP
1151
1152 FETCH c_get_msite_langs_csr INTO l_language_code, l_enable_flag;
1153 EXIT WHEN c_get_msite_langs_csr%NOTFOUND;
1154
1155 IF (l_language_code = p_default_language_code) THEN
1156 l_def_found := 1;
1157 l_msite_languages_tbl(l_i).language_code := l_language_code;
1158 l_msite_languages_tbl(l_i).default_flag := FND_API.g_true;
1159 l_msite_languages_tbl(l_i).enable_flag := 'Y';
1160 ELSE
1161 l_msite_languages_tbl(l_i).language_code := l_language_code;
1162 l_msite_languages_tbl(l_i).default_flag := FND_API.g_false;
1163 l_msite_languages_tbl(l_i).enable_flag := l_enable_flag;
1164 END IF;
1165 l_i := l_i + 1;
1166 END LOOP;
1167 CLOSE c_get_msite_langs_csr;
1168
1169 IF (l_i = 1) OR (l_def_found = 0) THEN
1170 l_msite_languages_tbl(l_i).language_code := p_default_language_code;
1171 l_msite_languages_tbl(l_i).default_flag := FND_API.g_true;
1172 l_msite_languages_tbl(l_i).enable_flag := 'Y';
1173 END IF;
1174 IBE_MSite_GRP.save_msite_languages(
1175 p_api_version => l_api_version,
1176 p_init_msg_list => FND_API.g_false,
1177 p_commit => p_commit,
1178 x_return_status => l_return_status,
1179 x_msg_count => l_msg_count,
1180 x_msg_data => l_msg_data,
1181 p_msite_id => l_msite_id,
1182 p_msite_languages_tbl => l_msite_languages_tbl);
1183 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1184 IF (l_debug = 'Y') THEN
1185 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite_languages');
1186 for i in 1..l_msg_count loop
1187 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1188 IBE_UTIL.debug(l_msg_data);
1189 end loop;
1190 END IF;
1191 RAISE FND_API.G_EXC_ERROR;
1192 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1193 IF (l_debug = 'Y') THEN
1194 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite_languages');
1195 for i in 1..l_msg_count loop
1196 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1197 IBE_UTIL.debug(l_msg_data);
1198 end loop;
1199 END IF;
1200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1201 END IF;
1202
1203 -- Duplicate Currencies and price lists
1204 l_i := 1;
1205 l_def_found := 0;
1206 OPEN c_get_msite_pricing_csr(p_msite_rec.msite_id);
1207 LOOP
1208 FETCH c_get_msite_pricing_csr INTO l_currency_code,
1209 l_bizpartner_prc_listid, l_registered_prc_listid,
1210 l_walkin_prc_listid, l_orderable_limit,
1211 l_payment_threshold, l_partner_prc_listid;
1212 EXIT WHEN c_get_msite_pricing_csr%NOTFOUND;
1213
1214 IF (l_currency_code = p_default_currency_code) THEN
1215 l_def_found := 1;
1216 l_registered_prc_listid := p_walkin_pricing_id;
1217 l_bizpartner_prc_listid := p_walkin_pricing_id;
1218 l_partner_prc_listid := p_walkin_pricing_id;
1219 l_msite_currencies_tbl(l_i).currency_code := l_currency_code;
1220 l_msite_currencies_tbl(l_i).walkin_prc_lst_id
1221 := p_walkin_pricing_id;
1222 l_msite_currencies_tbl(l_i).registered_prc_lst_id
1223 := l_registered_prc_listid;
1224 l_msite_currencies_tbl(l_i).biz_partner_prc_lst_id
1225 := l_bizpartner_prc_listid;
1226 l_msite_currencies_tbl(l_i).orderable_limit
1227 := l_orderable_limit;
1228 l_msite_currencies_tbl(l_i).default_flag := FND_API.g_true;
1229 l_msite_currencies_tbl(l_i).payment_threshold
1230 := l_payment_threshold;
1231
1232 l_msite_currencies_tbl(l_i).partner_prc_lst_id
1233 := l_partner_prc_listid;
1234
1235 ELSE
1236 l_msite_currencies_tbl(l_i).currency_code := l_currency_code;
1237 l_msite_currencies_tbl(l_i).walkin_prc_lst_id
1238 := l_walkin_prc_listid;
1239 l_msite_currencies_tbl(l_i).registered_prc_lst_id
1240 := l_registered_prc_listid;
1241 l_msite_currencies_tbl(l_i).biz_partner_prc_lst_id
1242 := l_bizpartner_prc_listid;
1243 l_msite_currencies_tbl(l_i).orderable_limit
1244 := l_orderable_limit;
1245 l_msite_currencies_tbl(l_i).default_flag := FND_API.g_false;
1246 l_msite_currencies_tbl(l_i).payment_threshold
1247 := l_payment_threshold;
1248
1249 l_msite_currencies_tbl(l_i).partner_prc_lst_id
1250 := l_partner_prc_listid;
1251
1252 END IF;
1253 l_i := l_i + 1;
1254 END LOOP;
1255 CLOSE c_get_msite_pricing_csr;
1256 IF (l_i = 1) OR (l_def_found = 0) THEN
1257 l_msite_currencies_tbl(l_i).currency_code := p_default_currency_code;
1258 l_msite_currencies_tbl(l_i).walkin_prc_lst_id
1259 := p_walkin_pricing_id;
1260 l_msite_currencies_tbl(l_i).registered_prc_lst_id
1261 := p_walkin_pricing_id;
1262 l_msite_currencies_tbl(l_i).biz_partner_prc_lst_id
1263 := p_walkin_pricing_id;
1264 l_msite_currencies_tbl(l_i).orderable_limit
1265 := NULL;
1266 l_msite_currencies_tbl(l_i).default_flag := FND_API.g_true;
1267 l_msite_currencies_tbl(l_i).payment_threshold
1268 := NULL;
1269
1270 l_msite_currencies_tbl(l_i).partner_prc_lst_id
1271 := p_walkin_pricing_id;
1272
1273 END IF;
1274 IBE_MSite_GRP.save_msite_currencies(
1275 p_api_version => 1.0,
1276 p_init_msg_list => FND_API.g_false,
1277 p_commit => p_commit,
1278 x_return_status => l_return_status,
1279 x_msg_count => l_msg_count,
1280 x_msg_data => l_msg_data,
1281 p_msite_id => l_msite_id,
1282 p_msite_currencies_tbl => l_msite_currencies_tbl);
1283 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1284 IF (l_debug = 'Y') THEN
1285 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite_currencies');
1286 for i in 1..l_msg_count loop
1287 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1288 IBE_UTIL.debug(l_msg_data);
1289 end loop;
1290 END IF;
1291 RAISE FND_API.G_EXC_ERROR;
1292 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1293 IF (l_debug = 'Y') THEN
1294 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite_currencies');
1295 for i in 1..l_msg_count loop
1296 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1297 IBE_UTIL.debug(l_msg_data);
1298 end loop;
1299 END IF;
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301 END IF;
1302
1303 -- For Payment Types, Shipping Methods, and Credit Card
1304 IBE_MSITE_INFORMATION_MGR_PVT.duplicate_msite_info (
1305 p_api_version => 1.0,
1306 p_init_msg_list => FND_API.G_FALSE,
1307 p_commit => p_commit,
1308 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1309 p_source_msite_id => p_msite_rec.msite_id,
1310 p_target_msite_id => l_msite_id,
1311 x_return_status => l_return_status,
1312 x_msg_count => l_msg_count,
1313 x_msg_data => l_msg_data
1314 );
1315 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1316 IF (l_debug = 'Y') THEN
1317 IBE_UTIL.debug('Error in IBE_MSITE_INFORMATION_MGR_PVT.'||
1318 'Change_Msite_Info');
1319 for i in 1..l_msg_count loop
1320 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1321 IBE_UTIL.debug(l_msg_data);
1322 end loop;
1323 END IF;
1324 RAISE FND_API.G_EXC_ERROR;
1325 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1326 IF (l_debug = 'Y') THEN
1327 IBE_UTIL.debug('Error in IBE_MSITE_INFORMATION_MGR_PVT.'||
1328 'Change_Msite_Info');
1329 for i in 1..l_msg_count loop
1330 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1331 IBE_UTIL.debug(l_msg_data);
1332 end loop;
1333 END IF;
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335 END IF;
1336
1337 -- For Responsibility Association Plus Group Code
1338 l_prev_msite_resp_id := NULL;
1339 l_msite_resp_id := NULL;
1340 x_msite_resp_id := NULL;
1341 OPEN c_get_msite_resp_csr(p_msite_rec.msite_id);
1342 LOOP
1343 FETCH c_get_msite_resp_csr INTO l_msite_resp_id, l_responsibility_id,
1344 l_application_id, l_display_name, l_start_date_active,
1345 l_end_date_active, l_sort_order, l_group_code;
1346 EXIT WHEN c_get_msite_resp_csr%NOTFOUND;
1347 fnd_message.set_name('IBE','IBE_M_COPY_OF');
1348 fnd_message.set_token('NAME',l_display_name);
1349 l_copy := fnd_message.get;
1350
1351 IF (l_prev_msite_resp_id IS NULL) OR
1352 (l_prev_msite_resp_id <> l_msite_resp_id) THEN
1353 l_prev_msite_resp_id := l_msite_resp_id;
1354 l_msite_resp_id := NULL;
1355
1356 IBE_Msite_Resp_Pvt.Create_Msite_Resp (
1357 p_api_version => 1.0,
1358 p_init_msg_list => FND_API.G_FALSE,
1359 p_commit => p_commit,
1360 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1361 p_msite_resp_id => l_msite_resp_id,
1362 p_msite_id => l_msite_id,
1363 p_responsibility_id => l_responsibility_id,
1364 p_application_id => l_application_id,
1365 p_start_date_active => l_start_date_active,
1366 p_end_date_active => l_end_date_active,
1367 p_sort_order => l_sort_order,
1368 p_display_name => l_copy,
1369 p_group_code => l_group_code,
1370 x_msite_resp_id => x_msite_resp_id,
1371 x_return_status => l_return_status,
1372 x_msg_count => l_msg_count,
1373 x_msg_data => l_msg_data);
1374 ELSE
1375 l_msite_resp_id := x_msite_resp_id;
1376
1377 IBE_Msite_Resp_Pvt.Create_Msite_Resp (
1378 p_api_version => 1.0,
1379 p_init_msg_list => FND_API.G_FALSE,
1380 p_commit => p_commit,
1381 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1382 p_msite_resp_id => l_msite_resp_id,
1383 p_msite_id => l_msite_id,
1384 p_responsibility_id => l_responsibility_id,
1385 p_application_id => l_application_id,
1386 p_start_date_active => l_start_date_active,
1387 p_end_date_active => l_end_date_active,
1388 p_sort_order => l_sort_order,
1389 p_display_name => l_copy,
1390 p_group_code => l_group_code,
1391 x_msite_resp_id => x_msite_resp_id,
1392 x_return_status => l_return_status,
1393 x_msg_count => l_msg_count,
1394 x_msg_data => l_msg_data);
1395 END IF;
1396 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1397 IF (l_debug = 'Y') THEN
1398 IBE_UTIL.debug('Error in IBE_Msite_Resp_Pvt.Create_Msite_Resp');
1399 for i in 1..l_msg_count loop
1400 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1401 IBE_UTIL.debug(l_msg_data);
1402 end loop;
1403 END IF;
1404 RAISE FND_API.G_EXC_ERROR;
1405 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1406 IF (l_debug = 'Y') THEN
1407 IBE_UTIL.debug('Error in IBE_Msite_Resp_Pvt.Create_Msite_Resp');
1408 for i in 1..l_msg_count loop
1409 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1410 IBE_UTIL.debug(l_msg_data);
1411 end loop;
1412 END IF;
1413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1414 END IF;
1415 END LOOP;
1416 CLOSE c_get_msite_resp_csr;
1417
1418 -- Access Restriction
1419 l_i := 1;
1420 l_party_ids := JTF_NUMBER_TABLE();
1421 l_start_date_actives := JTF_DATE_TABLE();
1422 l_end_date_actives := JTF_DATE_TABLE();
1423 OPEN c_get_msite_access_csr(p_msite_rec.msite_id);
1424 LOOP
1425 FETCH c_get_msite_access_csr INTO l_party_id,
1426 l_start_date_active, l_end_date_active;
1427 EXIT WHEN c_get_msite_access_csr%NOTFOUND;
1428 l_party_ids.EXTEND;
1429 l_start_date_actives.EXTEND;
1430 l_end_date_actives.EXTEND;
1431 l_party_ids(l_i) := l_party_id;
1432 l_start_date_actives(l_i) := l_start_date_active;
1433 l_end_date_actives(l_i) := l_end_date_active;
1434 l_i := l_i + 1;
1435 END LOOP;
1436 CLOSE c_get_msite_access_csr;
1437 IF (l_i > 1) THEN
1438 Ibe_Msite_Prty_Accss_Mgr_Pvt.Associate_Parties_To_MSite(
1439 p_api_version => 1.0,
1440 p_init_msg_list => FND_API.G_FALSE,
1441 p_commit => p_commit,
1442 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1443 p_msite_id => l_msite_id,
1444 p_party_ids => l_party_ids,
1445 p_start_date_actives => l_start_date_actives,
1446 p_end_date_actives => l_end_date_actives,
1447 x_msite_prty_accss_ids => l_msite_prty_accss_ids,
1448 x_duplicate_association_status => l_duplicate_association_status,
1449 x_is_any_duplicate_status => l_is_any_duplicate_status,
1450 x_return_status => l_return_status,
1451 x_msg_count => l_msg_count,
1452 x_msg_data => l_msg_data);
1453 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1454 IF (l_debug = 'Y') THEN
1455 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite_currencies');
1456 for i in 1..l_msg_count loop
1457 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1458 IBE_UTIL.debug(l_msg_data);
1459 end loop;
1460 END IF;
1461 RAISE FND_API.G_EXC_ERROR;
1462 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1463 IF (l_debug = 'Y') THEN
1464 IBE_UTIL.debug('Error in IBE_MSite_GRP.save_msite_currencies');
1465 for i in 1..l_msg_count loop
1466 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
1467 IBE_UTIL.debug(l_msg_data);
1468 end loop;
1469 END IF;
1470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1471 END IF;
1472 END IF;
1473
1474 IF (l_debug = 'Y') THEN
1475 IBE_UTIL.debug('Before committing the work:'||p_commit);
1476 END IF;
1477 IF (FND_API.To_Boolean(p_commit)) THEN
1478 COMMIT WORK;
1479 END IF;
1480 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1481 p_data => x_msg_data,
1482 p_encoded => 'F');
1483 IF (l_debug = 'Y') THEN
1484 IBE_UTIL.debug('duplicate_msite Ends +');
1485 END IF;
1486 EXCEPTION
1487 WHEN FND_API.G_EXC_ERROR THEN
1488 ROLLBACK TO DUPLICATE_MSITE_SAVE;
1489 x_return_status := FND_API.G_RET_STS_ERROR;
1490 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1491 p_data => x_msg_data,
1492 p_encoded => 'F');
1493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1494 ROLLBACK TO DUPLICATE_MSITE_SAVE;
1495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1496 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1497 p_data => x_msg_data,
1498 p_encoded => 'F');
1499 WHEN OTHERS THEN
1500 ROLLBACK TO DUPLICATE_MSITE_SAVE;
1501 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1502 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1503 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1504 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1505 FND_MSG_PUB.Add;
1506 IF (l_debug = 'Y') THEN
1507 IBE_UTIL.debug('SQLCODE:'||SQLCODE);
1508 IBE_UTIL.debug('SQLERRM:'||SQLERRM);
1509 END IF;
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1511 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1512 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1513 END IF;
1514 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1515 p_data => x_msg_data,
1516 p_encoded => 'F');
1517 END duplicate_msite;
1518
1519
1520 PROCEDURE save_msite_languages(
1521 p_api_version IN NUMBER,
1522 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1523 p_commit IN VARCHAR2 := FND_API.g_false,
1524 x_return_status OUT NOCOPY VARCHAR2,
1525 x_msg_count OUT NOCOPY NUMBER,
1526 x_msg_data OUT NOCOPY VARCHAR2,
1527 p_msite_id IN NUMBER,
1528 p_msite_languages_tbl IN MSITE_LANGUAGES_TBL_TYPE
1529 )
1530 IS
1531 l_api_name CONSTANT VARCHAR2(30) := 'save_msite_languages';
1532 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1533 l_msite_id NUMBER;
1534 l_exists NUMBER;
1535 default_index NUMBER := 0;
1536
1537 CURSOR msite_languages_id_seq IS
1538 SELECT ibe_msite_languages_s1.NEXTVAL
1539 FROM DUAL;
1540
1541 l_msite_languages_id NUMBER;
1542 l_insert_row NUMBER := 0;
1543 l_debug VARCHAR2(1);
1544
1545
1546 BEGIN
1547 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
1548
1549 --------------------- initialize -----------------------+
1550 SAVEPOINT save_msite_languages;
1551
1552 IF NOT FND_API.compatible_api_call(
1553 g_api_version,
1554 p_api_version,
1555 l_api_name,
1556 g_pkg_name
1557 ) THEN
1558 RAISE FND_API.g_exc_unexpected_error;
1559 END IF;
1560
1561
1562 IF FND_API.to_boolean(p_init_msg_list) THEN
1563 FND_MSG_PUB.initialize;
1564 END IF;
1565
1566
1567 x_return_status := FND_API.G_RET_STS_SUCCESS;
1568
1569 --- Check if the msite_id exists
1570 IF p_msite_id IS NOT NULL and p_msite_id <> FND_API.g_miss_num
1571 THEN
1572
1573 if ibe_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
1574 raise FND_API.g_exc_error;
1575 end if;
1576
1577 --dbms_output.put_line('Minisite id is passed ' );
1578 ---- Delete all the entries for the mini-site
1579
1580 if p_msite_languages_tbl.count > 0
1581 then
1582
1583 DELETE FROM IBE_MSITE_LANGUAGES where
1584 msite_id = p_msite_id;
1585
1586 --- Insert all the rows for the minisite
1587
1588
1589 --dbms_output.put_line('Default language id is passed deleted' );
1590
1591 for l_index in 1..p_msite_languages_tbl.count
1592 LOOP
1593 BEGIN
1594 savepoint save_msite_language;
1595
1596 if valid_language(p_msite_languages_tbl(l_index).language_code) =
1597 false THEN
1598 raise FND_API.g_exc_error;
1599 end if;
1600
1601 OPEN msite_languages_id_seq;
1602 FETCH msite_languages_id_seq INTO l_msite_languages_id;
1603 CLOSE msite_languages_id_seq;
1604
1605 INSERT INTO IBE_MSITE_LANGUAGES (
1606 MSITE_LANGUAGE_ID,
1607 OBJECT_VERSION_NUMBER,
1608 LAST_UPDATE_DATE,
1609 LAST_UPDATED_BY,
1610 CREATION_DATE,
1611 CREATED_BY,
1612 LAST_UPDATE_LOGIN,
1613 MSITE_ID,
1614 LANGUAGE_CODE,
1615 ENABLE_FLAG)
1616 VALUES (
1617 l_msite_languages_id,
1618 1,
1619 SYSDATE,
1620 FND_GLOBAL.user_id,
1621 SYSDATE,
1622 FND_GLOBAL.user_id,
1623 FND_GLOBAL.user_id,
1624 p_msite_id,
1625 p_msite_languages_tbl(l_index).language_code,
1626 p_msite_languages_tbl(l_index).enable_flag);
1627
1628 --dbms_output.put_line('inserted language passed ' );
1629 l_insert_row := l_insert_row + 1;
1630 -- Check if this language is default
1631 if p_msite_languages_tbl(l_index).default_flag = FND_API.g_true
1632 and default_index = 0 then
1633 default_index := l_index;
1634 end if;
1635
1636 EXCEPTION
1637 WHEN OTHERS THEN
1638 ROLLBACK TO save_msite_language;
1639 x_return_status := FND_API.g_ret_sts_error;
1640 END;
1641
1642 END LOOP;
1643 /* else if msite_enabled_for_store(p_msite_id) = true then
1644 raise msite_languages_missing;
1645 end if;
1646 */
1647 END IF;
1648
1649 If default_index > 0 then
1650 --dbms_output.put_line('default is not null');
1651 update IBE_MSITES_B SET
1652 DEFAULT_LANGUAGE_CODE =
1653 p_msite_languages_tbl(default_index).language_code
1654 WHERE MSITE_ID = p_msite_id;
1655 else
1656 --dbms_output.put_line('default is null');
1657 raise msite_default_lang_missing;
1658 end if;
1659
1660 -- changed 08/04/2003. Will not delete langauges. No need to do this.
1661 --ibe_physicalmap_grp.delete_msite_language(p_msite_id);
1662
1663 --- Check if the caller requested to commit ,
1664 --- If p_commit set to true, commit the transaction
1665 if l_insert_row > 0 then
1666 IF FND_API.to_boolean(p_commit) THEN
1667 COMMIT;
1668 END IF;
1669 else
1670 raise FND_API.g_exc_error;
1671 end if;
1672 else
1673 raise ibe_dspmgrvalidation_grp.msite_req_exception;
1674 end if;
1675 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
1676 p_count => x_msg_count,
1677 p_data => x_msg_data
1678 );
1679 EXCEPTION
1680 WHEN FND_API.g_exc_error THEN
1681 ROLLBACK TO save_msite_languages;
1682 x_return_status := FND_API.g_ret_sts_error;
1683 FND_MSG_PUB.count_and_get(
1684 p_encoded => FND_API.g_false,
1685 p_count => x_msg_count,
1686 p_data => x_msg_data
1687 );
1688 WHEN ibe_dspmgrvalidation_grp.msite_req_exception THEN
1689 ROLLBACK TO save_msite_languages;
1690 x_return_status := FND_API.g_ret_sts_error;
1691 FND_MESSAGE.set_name('IBE','IBE_MSITE_REQ');
1692 FND_MSG_PUB.ADD;
1693 FND_MSG_PUB.count_and_get(
1694 p_encoded => FND_API.g_false,
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698 WHEN msite_default_lang_missing THEN
1699 ROLLBACK TO save_msite_languages;
1700 x_return_status := FND_API.g_ret_sts_error;
1701 FND_MESSAGE.set_name('IBE','IBE_MSITE_DEF_LANG_REQ');
1702 FND_MESSAGE.set_token('ID', p_msite_id);
1703 FND_MSG_PUB.ADD;
1704 FND_MSG_PUB.count_and_get(
1705 p_encoded => FND_API.g_false,
1706 p_count => x_msg_count,
1707 p_data => x_msg_data
1708 );
1709 WHEN FND_API.g_exc_unexpected_error THEN
1710 ROLLBACK TO save_msite_languages;
1711 x_return_status := FND_API.g_ret_sts_unexp_error ;
1712 FND_MSG_PUB.count_and_get(
1713 p_encoded => FND_API.g_false,
1714 p_count => x_msg_count,
1715 p_data => x_msg_data );
1716 WHEN OTHERS THEN
1717 ROLLBACK TO save_msite_languages;
1718 x_return_status := FND_API.g_ret_sts_unexp_error ;
1719 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1720 THEN
1721 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1722 END IF;
1723 FND_MSG_PUB.count_and_get(
1724 p_encoded => FND_API.g_false,
1725 p_count => x_msg_count,
1726 p_data => x_msg_data );
1727
1728 END save_msite_languages;
1729
1730 PROCEDURE save_msite_currencies(
1731 p_api_version IN NUMBER,
1732 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1733 p_commit IN VARCHAR2 := FND_API.g_false,
1734 x_return_status OUT NOCOPY VARCHAR2,
1735 x_msg_count OUT NOCOPY NUMBER,
1736 x_msg_data OUT NOCOPY VARCHAR2,
1737 p_msite_id IN NUMBER,
1738 p_msite_currencies_tbl IN MSITE_CURRENCIES_TBL_TYPE
1739 )
1740 IS
1741 l_api_name CONSTANT VARCHAR2(30) := 'save_msite_currencies';
1742 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1743 l_msite_id NUMBER;
1744 l_exists NUMBER;
1745 CURSOR msite_currencies_id_seq IS
1746 SELECT ibe_msite_currencies_s1.NEXTVAL
1747 FROM DUAL;
1748 l_msite_currencies_id NUMBER;
1749 l_payment_threshold NUMBER;
1750 l_insert_row NUMBER := 0;
1751 default_index NUMBER := 0;
1752 l_debug VARCHAR2(1);
1753 l_validate_result VARCHAR2(3);
1754
1755 BEGIN
1756 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
1757
1758 --------------------- initialize -----------------------+
1759
1760 SAVEPOINT save_msite_currencies;
1761
1762 IF NOT FND_API.compatible_api_call(
1763 g_api_version,
1764 p_api_version,
1765 l_api_name,
1766 g_pkg_name ) THEN
1767 RAISE FND_API.g_exc_unexpected_error;
1768 END IF;
1769
1770 IF FND_API.to_boolean(p_init_msg_list) THEN
1771 FND_MSG_PUB.initialize;
1772 END IF;
1773 x_return_status := FND_API.G_RET_STS_SUCCESS;
1774 --- Check if the msite_id exists
1775 IF p_msite_id IS NOT NULL and p_msite_id <> FND_API.g_miss_num
1776 THEN
1777 --dbms_output.put_line('Minisite id is passed ' );
1778 if ibe_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
1779 raise FND_API.g_exc_error;
1780 end if;
1781 if (p_msite_currencies_tbl.count > 0 ) then
1782 ---- Delete all the entries for the mini-site
1783 DELETE FROM IBE_MSITE_CURRENCIES where
1784 msite_id = p_msite_id;
1785
1786 --- Insert all the rows for the minisite
1787 for l_index in 1..p_msite_currencies_tbl.count
1788 LOOP
1789 BEGIN
1790 savepoint save_msite_currency;
1791 if valid_currency(p_msite_currencies_tbl(l_index).currency_code)
1792 = false THEN
1793 raise FND_API.g_exc_error;
1794 end if;
1795
1796 QP_UTIL_PUB.Validate_Price_List_Curr_Code(
1797 p_msite_currencies_tbl(l_index).walkin_prc_lst_id,
1798 p_msite_currencies_tbl(l_index).currency_code,
1799 NULL,
1800 l_validate_result);
1801
1802 if (l_validate_result = 'N') then
1803 raise FND_API.g_exc_error;
1804 end if;
1805
1806 QP_UTIL_PUB.Validate_Price_List_Curr_Code(
1807 p_msite_currencies_tbl(l_index).registered_prc_lst_id,
1808 p_msite_currencies_tbl(l_index).currency_code,
1809 NULL,
1810 l_validate_result);
1811
1812 if (l_validate_result = 'N') then
1813 raise FND_API.g_exc_error;
1814 end if;
1815
1816 QP_UTIL_PUB.Validate_Price_List_Curr_Code(
1817 p_msite_currencies_tbl(l_index).biz_partner_prc_lst_id,
1818 p_msite_currencies_tbl(l_index).currency_code,
1819 NULL,
1820 l_validate_result);
1821
1822 if (l_validate_result = 'N') then
1823 raise FND_API.g_exc_error;
1824 end if;
1825
1826 QP_UTIL_PUB.Validate_Price_List_Curr_Code(
1827 p_msite_currencies_tbl(l_index).partner_prc_lst_id,
1828 p_msite_currencies_tbl(l_index).currency_code,
1829 NULL,
1830 l_validate_result);
1831
1832 if (l_validate_result = 'N') then
1833 raise FND_API.g_exc_error;
1834 end if;
1835
1836 /* Sept-05-2003 JQU
1837 following logic replaced by the 3 QP_UTIL_PUB.Validate_Price_List_Curr_Code
1838 function calls above to support multi-currency price lists
1839
1840 if valid_prc_lstids (
1841 p_msite_currencies_tbl(l_index).currency_code,
1842 p_msite_currencies_tbl(l_index).walkin_prc_lst_id,
1843 p_msite_currencies_tbl(l_index).registered_prc_lst_id,
1844
1845 = false then
1846 --dbms_output.put_line('invliad prc list id test');
1847 raise FND_API.g_exc_error;
1848 end if;
1849 */
1850 l_payment_threshold := p_msite_currencies_tbl(l_index).payment_threshold;
1851 if(l_payment_threshold = FND_API.g_miss_num)
1852 then
1853 l_payment_threshold := NULL;
1854 end if;
1855
1856
1857 OPEN msite_currencies_id_seq;
1858 FETCH msite_currencies_id_seq INTO l_msite_currencies_id;
1859 CLOSE msite_currencies_id_seq;
1860 INSERT INTO IBE_MSITE_CURRENCIES (
1861 MSITE_CURRENCY_ID,
1862 OBJECT_VERSION_NUMBER,
1863 LAST_UPDATE_DATE,
1864 LAST_UPDATED_BY,
1865 CREATION_DATE,
1866 CREATED_BY,
1867 LAST_UPDATE_LOGIN,
1868 MSITE_ID,
1869 CURRENCY_CODE,
1870 WALKIN_PRC_LISTID,
1871 REGISTERED_PRC_LISTID,
1872 BIZPARTNER_PRC_LISTID,
1873 ORDERABLE_limit,
1874 PAYMENT_THRESHOLD,
1875 PARTNER_PRC_LISTID)
1876 VALUES (
1877 l_msite_currencies_id,
1878 1,
1879 SYSDATE,
1880 FND_GLOBAL.user_id,
1881 SYSDATE,
1882 FND_GLOBAL.user_id,
1883 FND_GLOBAL.user_id,
1884 p_msite_id,
1885 p_msite_currencies_tbl(l_index).currency_code,
1886 p_msite_currencies_tbl(l_index).walkin_prc_lst_id,
1887 p_msite_currencies_tbl(l_index).registered_prc_lst_id,
1888 p_msite_currencies_tbl(l_index).biz_partner_prc_lst_id,
1889 p_msite_currencies_tbl(l_index).orderable_limit,
1890 l_payment_threshold,
1891 p_msite_currencies_tbl(l_index).partner_prc_lst_id);
1892 -- p_msite_currencies_tbl(l_index).payment_threshold);
1893 --dbms_output.put_line('inserted into currency');
1894
1895 l_insert_row := l_insert_row + 1;
1896 --dbms_output.put_line('inserted into currency' || l_insert_row);
1897
1898 if p_msite_currencies_tbl(l_index).default_flag = FND_API.g_true
1899 and default_index = 0 then
1900 default_index := l_index;
1901 end if;
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 ROLLBACK TO save_msite_currency;
1905 x_return_status := FND_API.g_ret_sts_error;
1906 END;
1907 END LOOP;
1908 /* else if msite_enabled_for_store(p_msite_id) = true then
1909 raise msite_currencies_missing;
1910 end if;
1911 */
1912 END IF;
1913 If default_index > 0 then
1914 update IBE_MSITES_B SET
1915 DEFAULT_CURRENCY_CODE =
1916 p_msite_currencies_tbl(default_index).currency_code
1917 WHERE MSITE_ID = p_msite_id;
1918 --dbms_output.put_line('set default currency');
1919 else
1920 --dbms_output.put_line('default is null');
1921 raise msite_default_currency_missing;
1922 end if;
1923 --- Check if the caller requested to commit ,
1924 --- If p_commit set to true, commit the transaction
1925 if l_insert_row > 0 then
1926 IF FND_API.to_boolean(p_commit) THEN
1927 COMMIT;
1928 END IF;
1929 else
1930 --dbms_output.put_line('raising an error' || l_insert_row);
1931 raise FND_API.g_exc_error;
1932 end if;
1933 else
1934 raise ibe_dspmgrvalidation_grp.msite_req_exception;
1935 end if;
1936 FND_MSG_PUB.count_and_get(
1937 p_encoded => FND_API.g_false,
1938 p_count => x_msg_count,
1939 p_data => x_msg_data
1940 );
1941
1942 EXCEPTION
1943 WHEN FND_API.g_exc_error THEN
1944 ROLLBACK TO save_msite_currencies;
1945 x_return_status := FND_API.g_ret_sts_error;
1946 FND_MSG_PUB.count_and_get(
1947 p_encoded => FND_API.g_false,
1948 p_count => x_msg_count,
1949 p_data => x_msg_data
1950 );
1951 WHEN ibe_dspmgrvalidation_grp.msite_req_exception THEN
1952 ROLLBACK TO save_msite_currencies;
1953 x_return_status := FND_API.g_ret_sts_error;
1954 FND_MESSAGE.set_name('IBE','IBE_MSITE_REQ');
1955 FND_MSG_PUB.ADD;
1956 FND_MSG_PUB.count_and_get(
1957 p_encoded => FND_API.g_false,
1958 p_count => x_msg_count,
1959 p_data => x_msg_data
1960 );
1961 WHEN msite_default_currency_missing THEN
1962 ROLLBACK TO save_msite_currencies;
1963 x_return_status := FND_API.g_ret_sts_error;
1964 FND_MESSAGE.set_name('IBE','IBE_MSITE_DEF_CURR_REQ');
1965 FND_MESSAGE.set_token('ID', p_msite_id);
1966 FND_MSG_PUB.ADD;
1967 FND_MSG_PUB.count_and_get(
1968 p_encoded => FND_API.g_false,
1969 p_count => x_msg_count,
1970 p_data => x_msg_data
1971 );
1972 WHEN FND_API.g_exc_unexpected_error THEN
1973 ROLLBACK TO save_msite_currencies;
1974 x_return_status := FND_API.g_ret_sts_unexp_error ;
1975 FND_MSG_PUB.count_and_get(
1976 p_encoded => FND_API.g_false,
1977 p_count => x_msg_count,
1978 p_data => x_msg_data
1979 );
1980 WHEN OTHERS THEN
1981 ROLLBACK TO save_msite_currencies;
1982 x_return_status := FND_API.g_ret_sts_unexp_error ;
1983 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1984 THEN
1985 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1986 END IF;
1987 FND_MSG_PUB.count_and_get(
1988 p_encoded => FND_API.g_false,
1989 p_count => x_msg_count,
1990 p_data => x_msg_data
1991 );
1992
1993 END save_msite_currencies;
1994
1995
1996
1997 PROCEDURE save_msite_orgids(
1998 p_api_version IN NUMBER,
1999 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2000 p_commit IN VARCHAR2 := FND_API.g_false,
2001 x_return_status OUT NOCOPY VARCHAR2,
2002 x_msg_count OUT NOCOPY NUMBER,
2003 x_msg_data OUT NOCOPY VARCHAR2,
2004 p_msite_id IN NUMBER,
2005 p_msite_orgids_tbl IN MSITE_ORGIDS_TBL_TYPE
2006 )
2007 IS
2008 l_api_name CONSTANT VARCHAR2(30) := 'save_msite_orgids';
2009 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2010 l_msite_id NUMBER;
2011 l_exists NUMBER;
2012 l_insert_row NUMBER := 0;
2013
2014 CURSOR msite_oprorg_id_seq IS
2015 SELECT ibe_msite_orgs_s1.NEXTVAL
2016 FROM DUAL;
2017
2018 l_msite_org_id NUMBER;
2019 default_index NUMBER := 0;
2020 l_debug VARCHAR2(1);
2021
2022 BEGIN
2023 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
2024
2025 --------------------- initialize -----------------------+
2026 SAVEPOINT save_msite_orgids;
2027
2028 IF NOT FND_API.compatible_api_call(
2029 g_api_version,
2030 p_api_version,
2031 l_api_name,
2032 g_pkg_name
2033 ) THEN
2034 RAISE FND_API.g_exc_unexpected_error;
2035 END IF;
2036
2037
2038 IF FND_API.to_boolean(p_init_msg_list) THEN
2039 FND_MSG_PUB.initialize;
2040 END IF;
2041
2042
2043 x_return_status := FND_API.G_RET_STS_SUCCESS;
2044
2045 --- Check if the msite_id exists
2046 IF p_msite_id IS NOT NULL and p_msite_id <> FND_API.g_miss_num
2047 THEN
2048 --dbms_output.put_line('Minisite id is passed ' );
2049
2050 if ibe_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
2051 raise FND_API.g_exc_error;
2052 end if;
2053
2054 if (p_msite_orgids_tbl.count > 0 ) then
2055 ---- Delete all the entries for the mini-site
2056 DELETE FROM IBE_MSITE_ORGS where
2057 msite_id = p_msite_id;
2058
2059 --- Insert all the rows for the minisite
2060
2061
2062 --dbms_output.put_line('passed defualt orgid test');
2063 for l_index in 1..p_msite_orgids_tbl.count
2064 LOOP
2065 BEGIN
2066 savepoint save_msite_orgid;
2067
2068 if valid_orgid(p_msite_orgids_tbl(l_index).orgid) = false THEN
2069 raise FND_API.g_exc_error;
2070 end if;
2071 OPEN msite_oprorg_id_seq;
2072 FETCH msite_oprorg_id_seq INTO l_msite_org_id;
2073 CLOSE msite_oprorg_id_seq;
2074
2075 INSERT INTO IBE_MSITE_ORGS (
2076 MSITE_ORG_ID,
2077 OBJECT_VERSION_NUMBER,
2078 LAST_UPDATE_DATE,
2079 LAST_UPDATED_BY,
2080 CREATION_DATE,
2081 CREATED_BY,
2082 LAST_UPDATE_LOGIN,
2083 MSITE_ID,
2084 ORG_ID
2085 )
2086 VALUES (
2087 l_msite_org_id,
2088 1,
2089 SYSDATE,
2090 FND_GLOBAL.user_id,
2091 SYSDATE,
2092 FND_GLOBAL.user_id,
2093 FND_GLOBAL.user_id,
2094 p_msite_id,
2095 p_msite_orgids_tbl(l_index).orgid);
2096 --dbms_output.put_line('inserted into opr org');
2097 l_insert_row := l_insert_row + 1;
2098
2099 if p_msite_orgids_tbl(l_index).default_flag = FND_API.g_true
2100 and default_index = 0 then
2101 default_index := l_index;
2102 end if;
2103
2104 EXCEPTION
2105 WHEN OTHERS THEN
2106 ROLLBACK TO save_msite_orgid;
2107 x_return_status := FND_API.g_ret_sts_error;
2108 END;
2109
2110 END LOOP;
2111 /* else
2112 if msite_enabled_for_store(p_msite_id) = true then
2113 raise msite_orgs_missing;
2114 end if;*/
2115 END IF;
2116
2117 If default_index > 0 then
2118 update IBE_MSITES_B SET
2119 DEFAULT_ORG_ID = p_msite_orgids_tbl(default_index).orgid where
2120 MSITE_ID = p_msite_id;
2121 else
2122 raise msite_default_org_missing;
2123 end if;
2124
2125 --- Check if the caller requested to commit ,
2126 --- If p_commit set to true, commit the transaction
2127 if l_insert_row > 0 then
2128 IF FND_API.to_boolean(p_commit) THEN
2129 COMMIT;
2130 END IF;
2131 else
2132 raise FND_API.g_exc_error;
2133 end if;
2134
2135 else
2136 raise ibe_dspmgrvalidation_grp.msite_req_exception;
2137 end if;
2138
2139 FND_MSG_PUB.count_and_get(
2140 p_encoded => FND_API.g_false,
2141 p_count => x_msg_count,
2142 p_data => x_msg_data
2143 );
2144
2145 EXCEPTION
2146
2147 WHEN FND_API.g_exc_error THEN
2148 ROLLBACK TO save_msite_orgids;
2149 x_return_status := FND_API.g_ret_sts_error;
2150 FND_MSG_PUB.count_and_get(
2151 p_encoded => FND_API.g_false,
2152 p_count => x_msg_count,
2153 p_data => x_msg_data
2154 );
2155
2156 WHEN ibe_dspmgrvalidation_grp.msite_req_exception THEN
2157 ROLLBACK TO save_msite_orgids;
2158 x_return_status := FND_API.g_ret_sts_error;
2159 FND_MESSAGE.set_name('IBE','IBE_MSITE_REQ');
2160 FND_MSG_PUB.ADD;
2161 FND_MSG_PUB.count_and_get(
2162 p_encoded => FND_API.g_false,
2163 p_count => x_msg_count,
2164 p_data => x_msg_data
2165 );
2166 WHEN FND_API.g_exc_unexpected_error THEN
2167 ROLLBACK TO save_msite_orgids;
2168 x_return_status := FND_API.g_ret_sts_unexp_error ;
2169 FND_MSG_PUB.count_and_get(
2170 p_encoded => FND_API.g_false,
2171 p_count => x_msg_count,
2172 p_data => x_msg_data
2173 );
2174
2175 WHEN msite_default_org_missing THEN
2176 ROLLBACK TO save_msite_orgids;
2177 x_return_status := FND_API.g_ret_sts_error;
2178 FND_MESSAGE.set_name('IBE','IBE_MSITE_DEF_ORG_REQ');
2179 FND_MESSAGE.set_token('ID', p_msite_id);
2180 FND_MSG_PUB.ADD;
2181 FND_MSG_PUB.count_and_get(
2182 p_encoded => FND_API.g_false,
2183 p_count => x_msg_count,
2184 p_data => x_msg_data
2185 );
2186
2187 WHEN OTHERS THEN
2188 ROLLBACK TO save_msite_orgids;
2189 x_return_status := FND_API.g_ret_sts_unexp_error ;
2190 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2191 THEN
2192 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2193 END IF;
2194 FND_MSG_PUB.count_and_get(
2195 p_encoded => FND_API.g_false,
2196 p_count => x_msg_count,
2197 p_data => x_msg_data
2198 );
2199
2200 END save_msite_orgids;
2201
2202
2203 PROCEDURE delete_msite(
2204 p_api_version IN NUMBER,
2205 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2206 p_commit IN VARCHAR2 := FND_API.g_false,
2207 x_return_status OUT NOCOPY VARCHAR2,
2208 x_msg_count OUT NOCOPY NUMBER,
2209 x_msg_data OUT NOCOPY VARCHAR2,
2210 p_msite_id_tbl IN msite_delete_tbl_type
2211 )
2212 IS
2213
2214 l_api_name CONSTANT VARCHAR2(30) := 'delete_msite';
2215 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2216 l_msite_id NUMBER;
2217 l_exists NUMBER;
2218 l_insert_row NUMBER := 0;
2219 l_index NUMBER := 0;
2220
2221
2222 CURSOR c_msite_resp (p_msite_id Number) Is
2223 Select msite_resp_id
2224 From IBE_MSITE_RESPS_B
2225 Where msite_id = p_msite_id ;
2226 l_debug VARCHAR2(1);
2227
2228 BEGIN
2229 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
2230
2231 --------------------- initialize -----------------------+
2232 SAVEPOINT delete_msite;
2233
2234 IF NOT FND_API.compatible_api_call(
2235 g_api_version,
2236 p_api_version,
2237 l_api_name,
2238 g_pkg_name
2239 ) THEN
2240 RAISE FND_API.g_exc_unexpected_error;
2241 END IF;
2242
2243
2244 IF FND_API.to_boolean(p_init_msg_list) THEN
2245 FND_MSG_PUB.initialize;
2246 END IF;
2247
2248
2249 x_return_status := FND_API.G_RET_STS_SUCCESS;
2250
2251 --- Check if the msite_id exists
2252 for l_index in 1..p_msite_id_tbl.count
2253 LOOP
2254 BEGIN
2255 savepoint delete_msite_id;
2256 IF p_msite_id_tbl(l_index).msite_id IS NOT NULL and
2257 p_msite_id_tbl(l_index).msite_id <> FND_API.g_miss_num
2258 THEN
2259 --dbms_output.put_line('Minisite id is passed ' );
2260
2261 --- if ibe_dspmgrvalidation_grp.check_msite_exists(p_msite_id_tbl(l_index).msite_id) = false then
2262 --- raise FND_API.g_exc_error;
2263 --- end if;
2264
2265 ibe_physicalmap_grp.delete_msite(p_msite_id_tbl(l_index).msite_id);
2266
2267 delete from ibe_msite_languages where msite_id = p_msite_id_tbl(l_index).msite_id;
2268 delete from ibe_msite_currencies where msite_id = p_msite_id_tbl(l_index).msite_id;
2269 delete from ibe_msite_orgs where msite_id = p_msite_id_tbl(l_index).msite_id;
2270 delete from ibe_dsp_msite_sct_sects where mini_site_id=p_msite_id_tbl(l_index).msite_id;
2271 delete from ibe_dsp_msite_sct_items where mini_site_id=p_msite_id_tbl(l_index).msite_id;
2272 delete from ibe_msites_tl where msite_id = p_msite_id_tbl(l_index).msite_id;
2273 delete from ibe_msites_b where msite_id = p_msite_id_tbl(l_index).msite_id;
2274
2275 --added for deleting the rows from the newly added Merchant responsibility
2276 --table -- ssridhar
2277
2278 for rec_msite_resp in c_msite_resp (p_msite_id_tbl(l_index).msite_id )
2279 Loop
2280 Ibe_Msite_Resp_Pvt.Delete_Msite_Resp(
2281 p_api_version => 1.0 ,
2282 p_init_msg_list => FND_API.G_FALSE,
2283 p_commit => FND_API.G_FALSE,
2284 p_validation_level=>FND_API.G_VALID_LEVEL_FULL,
2285 p_msite_resp_id => rec_msite_resp.msite_resp_id ,
2286 -- p_msite_id => FND_API.G_MISS_NUM,
2287 --p_responsibility_id => FND_API.G_MISS_NUM,
2288 --p_application_id => FND_API.G_MISS_NUM,
2289 x_return_status => x_return_status ,
2290 x_msg_count => x_msg_count ,
2291 x_msg_data => x_msg_data );
2292
2293 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2294 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
2295 FND_MSG_PUB.Add;
2296 RAISE FND_API.G_EXC_ERROR;
2297 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2298 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_RESP_FL');
2299 FND_MSG_PUB.Add;
2300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2301 END IF;
2302 End Loop;
2303
2304 delete from ibe_msite_prty_accss
2305 where msite_id = p_msite_id_tbl(l_index).msite_id;
2306
2307 delete from ibe_wf_notif_msg_maps
2308 where msite_id = p_msite_id_tbl(l_index).msite_id;
2309
2310 else
2311 raise ibe_dspmgrvalidation_grp.msite_req_exception;
2312 end if;
2313
2314 EXCEPTION
2315 WHEN OTHERS THEN
2316 ROLLBACK TO delete_msite_id;
2317 x_return_status := FND_API.g_ret_sts_error;
2318 END;
2319
2320 END LOOP;
2321
2322
2323
2324 IF FND_API.to_boolean(p_commit) THEN
2325 COMMIT;
2326 END IF;
2327
2328 x_return_status := FND_API.G_RET_STS_SUCCESS;
2329
2330 FND_MSG_PUB.count_and_get(
2331 p_encoded => FND_API.g_false,
2332 p_count => x_msg_count,
2333 p_data => x_msg_data
2334 );
2335
2336 EXCEPTION
2337 WHEN FND_API.g_exc_error THEN
2338 ROLLBACK TO delete_msite;
2339 x_return_status := FND_API.g_ret_sts_error;
2340 FND_MSG_PUB.count_and_get(
2341 p_encoded => FND_API.g_false,
2342 p_count => x_msg_count,
2343 p_data => x_msg_data
2344 );
2345
2346 WHEN ibe_dspmgrvalidation_grp.msite_req_exception THEN
2347 ROLLBACK TO delete_msite;
2348 x_return_status := FND_API.g_ret_sts_error;
2349 FND_MESSAGE.set_name('IBE','IBE_MSITE_REQ');
2350 FND_MSG_PUB.ADD;
2351 FND_MSG_PUB.count_and_get(
2352 p_encoded => FND_API.g_false,
2353 p_count => x_msg_count,
2354 p_data => x_msg_data
2355 );
2356
2357 WHEN FND_API.g_exc_unexpected_error THEN
2358 ROLLBACK TO delete_msite;
2359 x_return_status := FND_API.g_ret_sts_unexp_error ;
2360 FND_MSG_PUB.count_and_get(
2361 p_encoded => FND_API.g_false,
2362 p_count => x_msg_count,
2363 p_data => x_msg_data
2364 );
2365
2366 WHEN OTHERS THEN
2367 ROLLBACK TO delete_msite;
2368 x_return_status := FND_API.g_ret_sts_unexp_error ;
2369 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2370 THEN
2371 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2372 END IF;
2373 FND_MSG_PUB.count_and_get(
2374 p_encoded => FND_API.g_false,
2375 p_count => x_msg_count,
2376 p_data => x_msg_data
2377 );
2378
2379 end delete_msite;
2380
2381
2382 PROCEDURE get_msite_attribute (
2383 p_api_version IN NUMBER,
2384 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2385 p_commit IN VARCHAR2 := FND_API.g_false,
2386 x_return_status OUT NOCOPY VARCHAR2,
2387 x_msg_count OUT NOCOPY NUMBER,
2388 x_msg_data OUT NOCOPY VARCHAR2,
2389 p_msite_id IN NUMBER,
2390 p_msite_attribute_name IN VARCHAR2,
2391 x_msite_attribute_value OUT NOCOPY VARCHAR2)
2392 IS
2393
2394 l_api_name CONSTANT VARCHAR2(30) := 'get_msite_attribute';
2395 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2396 l_debug VARCHAR2(1);
2397
2398 BEGIN
2399 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
2400
2401 --------------------- initialize -----------------------+
2402 SAVEPOINT get_msite_attribute;
2403
2404 IF NOT FND_API.compatible_api_call(
2405 g_api_version,
2406 p_api_version,
2407 l_api_name,
2408 g_pkg_name
2409 ) THEN
2410 RAISE FND_API.g_exc_unexpected_error;
2411 END IF;
2412
2413
2414 IF FND_API.to_boolean(p_init_msg_list) THEN
2415 FND_MSG_PUB.initialize;
2416 END IF;
2417
2418
2419 x_return_status := FND_API.G_RET_STS_SUCCESS;
2420
2421 IF p_msite_id IS NOT NULL AND p_msite_id <> FND_API.g_miss_num
2422 THEN
2423 --dbms_output.put_line('Minisite id is passed ' );
2424
2425 if ibe_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
2426 raise FND_API.g_exc_error;
2427 end if;
2428
2429 x_msite_attribute_value := FND_PROFILE.VALUE_SPECIFIC(p_msite_attribute_name,null,null,671);
2430 ELSE
2431 raise ibe_dspmgrvalidation_grp.msite_req_exception;
2432 END IF;
2433
2434 x_return_status := FND_API.G_RET_STS_SUCCESS;
2435
2436 FND_MSG_PUB.count_and_get(
2437 p_encoded => FND_API.g_false,
2438 p_count => x_msg_count,
2439 p_data => x_msg_data
2440 );
2441
2442 EXCEPTION
2443 WHEN FND_API.g_exc_error THEN
2444 ROLLBACK TO get_msite_attribute;
2445 x_return_status := FND_API.g_ret_sts_error;
2446 FND_MSG_PUB.count_and_get(
2447 p_encoded => FND_API.g_false,
2448 p_count => x_msg_count,
2449 p_data => x_msg_data
2450 );
2451
2452 WHEN ibe_dspmgrvalidation_grp.msite_req_exception THEN
2453 ROLLBACK TO get_msite_attribute;
2454 x_return_status := FND_API.g_ret_sts_error;
2455 FND_MESSAGE.set_name('IBE','IBE_MSITE_REQ');
2456 FND_MSG_PUB.ADD;
2457 FND_MSG_PUB.count_and_get(
2458 p_encoded => FND_API.g_false,
2459 p_count => x_msg_count,
2460 p_data => x_msg_data
2461 );
2462
2463 WHEN FND_API.g_exc_unexpected_error THEN
2464 ROLLBACK TO get_msite_attribute;
2465 x_return_status := FND_API.g_ret_sts_unexp_error ;
2466 FND_MSG_PUB.count_and_get(
2467 p_encoded => FND_API.g_false,
2468 p_count => x_msg_count,
2469 p_data => x_msg_data
2470 );
2471
2472 WHEN OTHERS THEN
2473 ROLLBACK TO get_msite_attribute;
2474 x_return_status := FND_API.g_ret_sts_unexp_error ;
2475 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2476 THEN
2477 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2478 END IF;
2479 FND_MSG_PUB.count_and_get(
2480 p_encoded => FND_API.g_false,
2481 p_count => x_msg_count,
2482 p_data => x_msg_data
2483 );
2484
2485 end get_msite_attribute;
2486
2487
2488 -- Modifying the procedure to insert 3 new fields for globalisation -- ssridhar
2489 -- RESP_ACCESS_FLAG
2490 -- PARTY_ACCESS_CODE
2491 -- ACCESS_NAME
2492
2493 procedure INSERT_ROW (
2494 X_ROWID in out NOCOPY VARCHAR2,
2495 X_MSITE_ID in NUMBER,
2496 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2497 X_ATTRIBUTE1 in VARCHAR2,
2498 X_ATTRIBUTE2 in VARCHAR2,
2499 X_ATTRIBUTE3 in VARCHAR2,
2500 X_ATTRIBUTE4 in VARCHAR2,
2501 X_ATTRIBUTE5 in VARCHAR2,
2502 X_ATTRIBUTE6 in VARCHAR2,
2503 X_ATTRIBUTE7 in VARCHAR2,
2504 X_ATTRIBUTE8 in VARCHAR2,
2505 X_ATTRIBUTE9 in VARCHAR2,
2506 X_ATTRIBUTE11 in VARCHAR2,
2507 X_ATTRIBUTE10 in VARCHAR2,
2508 X_ATTRIBUTE12 in VARCHAR2,
2509 X_ATTRIBUTE13 in VARCHAR2,
2510 X_ATTRIBUTE14 in VARCHAR2,
2511 X_ATTRIBUTE15 in VARCHAR2,
2512 X_OBJECT_VERSION_NUMBER in NUMBER,
2513 X_STORE_ID in NUMBER,
2514 X_START_DATE_ACTIVE in DATE,
2515 X_END_DATE_ACTIVE in DATE,
2516 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
2517 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
2518 X_DEFAULT_DATE_FORMAT in VARCHAR2,
2519 X_DEFAULT_ORG_ID in NUMBER,
2520 X_ATP_CHECK_FLAG in VARCHAR2,
2521 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
2522 X_MSITE_ROOT_SECTION_ID in NUMBER,
2523 X_PROFILE_ID in NUMBER,
2524 X_MASTER_MSITE_FLAG in VARCHAR2,
2525 X_MSITE_NAME in VARCHAR2,
2526 X_MSITE_DESCRIPTION in VARCHAR2,
2527 X_CREATION_DATE in DATE,
2528 X_CREATED_BY in NUMBER,
2529 X_LAST_UPDATE_DATE in DATE,
2530 X_LAST_UPDATED_BY in NUMBER,
2531 X_LAST_UPDATE_LOGIN in NUMBER,
2532 X_RESP_ACCESS_FLAG in VARCHAR2 ,
2533 X_PARTY_ACCESS_CODE in VARCHAR2 ,
2534 X_ACCESS_NAME in VARCHAR2 ,
2535 X_URL in VARCHAR2 ,
2536 X_THEME_ID in NUMBER ,
2537 X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
2538 X_DOMAIN_NAME in VARCHAR2 := NULL,
2539 X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
2540 X_REPORTING_STATUS in VARCHAR2 := 'N',
2541 X_SITE_TYPE in VARCHAR2 := 'I')
2542 is
2543 cursor C is select ROWID from IBE_MSITES_B
2544 where MSITE_ID = X_MSITE_ID
2545 ;
2546 begin
2547 insert into IBE_MSITES_B (
2548 ATTRIBUTE_CATEGORY,
2549 ATTRIBUTE1,
2550 ATTRIBUTE2,
2551 ATTRIBUTE3,
2552 ATTRIBUTE4,
2553 ATTRIBUTE5,
2554 ATTRIBUTE6,
2555 ATTRIBUTE7,
2556 ATTRIBUTE8,
2557 ATTRIBUTE9,
2558 ATTRIBUTE11,
2559 ATTRIBUTE10,
2560 ATTRIBUTE12,
2561 ATTRIBUTE13,
2562 ATTRIBUTE14,
2563 ATTRIBUTE15,
2564 MSITE_ID,
2565 OBJECT_VERSION_NUMBER,
2566 STORE_ID,
2567 START_DATE_ACTIVE,
2568 END_DATE_ACTIVE,
2569 DEFAULT_LANGUAGE_CODE,
2570 DEFAULT_CURRENCY_CODE,
2571 DEFAULT_DATE_FORMAT,
2572 DEFAULT_ORG_ID,
2573 ATP_CHECK_FLAG,
2574 WALKIN_ALLOWED_FLAG,
2575 MSITE_ROOT_SECTION_ID,
2576 PROFILE_ID,
2577 MASTER_MSITE_FLAG,
2578 CREATION_DATE,
2579 CREATED_BY,
2580 LAST_UPDATE_DATE,
2581 LAST_UPDATED_BY,
2582 LAST_UPDATE_LOGIN ,
2583 RESP_ACCESS_FLAG ,
2584 PARTY_ACCESS_CODE ,
2585 ACCESS_NAME ,
2586 URL ,
2587 THEME_ID,
2588 PAYMENT_THRESHOLD_ENABLE_FLAG,
2589 DOMAIN_NAME,
2590 ENABLE_TRAFFIC_FILTER,
2591 REPORTING_STATUS,
2592 SITE_TYPE
2593 ) values (
2594 X_ATTRIBUTE_CATEGORY,
2595 X_ATTRIBUTE1,
2596 X_ATTRIBUTE2,
2597 X_ATTRIBUTE3,
2598 X_ATTRIBUTE4,
2599 X_ATTRIBUTE5,
2600 X_ATTRIBUTE6,
2601 X_ATTRIBUTE7,
2602 X_ATTRIBUTE8,
2603 X_ATTRIBUTE9,
2604 X_ATTRIBUTE11,
2605 X_ATTRIBUTE10,
2606 X_ATTRIBUTE12,
2607 X_ATTRIBUTE13,
2608 X_ATTRIBUTE14,
2609 X_ATTRIBUTE15,
2610 X_MSITE_ID,
2611 X_OBJECT_VERSION_NUMBER,
2612 X_STORE_ID,
2613 X_START_DATE_ACTIVE,
2614 X_END_DATE_ACTIVE,
2615 X_DEFAULT_LANGUAGE_CODE,
2616 X_DEFAULT_CURRENCY_CODE,
2617 X_DEFAULT_DATE_FORMAT,
2618 X_DEFAULT_ORG_ID,
2619 X_ATP_CHECK_FLAG,
2620 X_WALKIN_ALLOWED_FLAG,
2621 X_MSITE_ROOT_SECTION_ID,
2622 X_PROFILE_ID,
2623 X_MASTER_MSITE_FLAG,
2624 X_CREATION_DATE,
2625 X_CREATED_BY,
2626 X_LAST_UPDATE_DATE,
2627 X_LAST_UPDATED_BY,
2628 X_LAST_UPDATE_LOGIN ,
2629 X_RESP_ACCESS_FLAG ,
2630 X_PARTY_ACCESS_CODE ,
2631 X_ACCESS_NAME,
2632 X_URL,
2633 X_THEME_ID,
2634 X_PAYMENT_THRESH_ENABLE_FLAG,
2635 X_DOMAIN_NAME,
2636 X_ENABLE_TRAFFIC_FILTER,
2637 X_REPORTING_STATUS,
2638 X_SITE_TYPE );
2639
2640 insert into IBE_MSITES_TL (
2641 MSITE_ID,
2642 OBJECT_VERSION_NUMBER,
2643 CREATED_BY,
2644 CREATION_DATE,
2645 LAST_UPDATED_BY,
2646 LAST_UPDATE_DATE,
2647 LAST_UPDATE_LOGIN,
2648 MSITE_NAME,
2649 MSITE_DESCRIPTION,
2650 LANGUAGE,
2651 SOURCE_LANG
2652 ) select
2653 X_MSITE_ID,
2654 X_OBJECT_VERSION_NUMBER,
2655 X_CREATED_BY,
2656 X_CREATION_DATE,
2657 X_LAST_UPDATED_BY,
2658 X_LAST_UPDATE_DATE,
2659 X_LAST_UPDATE_LOGIN,
2660 X_MSITE_NAME,
2661 X_MSITE_DESCRIPTION,
2662 L.LANGUAGE_CODE,
2663 userenv('LANG')
2664 from FND_LANGUAGES L
2665 where L.INSTALLED_FLAG in ('I', 'B')
2666 and not exists
2667 (select NULL
2668 from IBE_MSITES_TL T
2669 where T.MSITE_ID = X_MSITE_ID
2670 and T.LANGUAGE = L.LANGUAGE_CODE);
2671
2672 open c;
2673 fetch c into X_ROWID;
2674 if (c%notfound) then
2675 close c;
2676 raise no_data_found;
2677 end if;
2678 close c;
2679
2680 end INSERT_ROW;
2681
2682 -- Modifying the procedure to accept 3 new fields for globalisation -- ssridhar
2683 -- RESP_ACCESS_FLAG
2684 -- PARTY_ACCESS_CODE
2685 -- ACCESS_NAME
2686
2687 procedure LOCK_ROW (
2688 X_MSITE_ID in NUMBER,
2689 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2690 X_ATTRIBUTE1 in VARCHAR2,
2691 X_ATTRIBUTE2 in VARCHAR2,
2692 X_ATTRIBUTE3 in VARCHAR2,
2693 X_ATTRIBUTE4 in VARCHAR2,
2694 X_ATTRIBUTE5 in VARCHAR2,
2695 X_ATTRIBUTE6 in VARCHAR2,
2696 X_ATTRIBUTE7 in VARCHAR2,
2697 X_ATTRIBUTE8 in VARCHAR2,
2698 X_ATTRIBUTE9 in VARCHAR2,
2699 X_ATTRIBUTE11 in VARCHAR2,
2700 X_ATTRIBUTE10 in VARCHAR2,
2701 X_ATTRIBUTE12 in VARCHAR2,
2702 X_ATTRIBUTE13 in VARCHAR2,
2703 X_ATTRIBUTE14 in VARCHAR2,
2704 X_ATTRIBUTE15 in VARCHAR2,
2705 X_OBJECT_VERSION_NUMBER in NUMBER,
2706 X_STORE_ID in NUMBER,
2707 X_START_DATE_ACTIVE in DATE,
2708 X_END_DATE_ACTIVE in DATE,
2709 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
2710 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
2711 X_DEFAULT_DATE_FORMAT in VARCHAR2,
2712 X_DEFAULT_ORG_ID in NUMBER,
2713 X_ATP_CHECK_FLAG in VARCHAR2,
2714 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
2715 X_MSITE_ROOT_SECTION_ID in NUMBER,
2716 X_PROFILE_ID in NUMBER,
2717 X_MASTER_MSITE_FLAG in VARCHAR2,
2718 X_MSITE_NAME in VARCHAR2,
2719 X_MSITE_DESCRIPTION in VARCHAR2 ,
2720 X_RESP_ACCESS_FLAG in VARCHAR2 ,
2721 X_PARTY_ACCESS_CODE in VARCHAR2 ,
2722 X_ACCESS_NAME in VARCHAR2 ,
2723 X_URL in VARCHAR2 ,
2724 X_THEME_ID in NUMBER ,
2725 X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
2726 X_DOMAIN_NAME in VARCHAR2 := NULL,
2727 X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
2728 X_REPORTING_STATUS in VARCHAR2 := 'N',
2729 X_SITE_TYPE in VARCHAR2 := 'I')
2730 IS
2731 cursor c is select
2732 ATTRIBUTE_CATEGORY,
2733 ATTRIBUTE1,
2734 ATTRIBUTE2,
2735 ATTRIBUTE3,
2736 ATTRIBUTE4,
2737 ATTRIBUTE5,
2738 ATTRIBUTE6,
2739 ATTRIBUTE7,
2740 ATTRIBUTE8,
2741 ATTRIBUTE9,
2742 ATTRIBUTE11,
2743 ATTRIBUTE10,
2744 ATTRIBUTE12,
2745 ATTRIBUTE13,
2746 ATTRIBUTE14,
2747 ATTRIBUTE15,
2748 OBJECT_VERSION_NUMBER,
2749 STORE_ID,
2750 START_DATE_ACTIVE,
2751 END_DATE_ACTIVE,
2752 DEFAULT_LANGUAGE_CODE,
2753 DEFAULT_CURRENCY_CODE,
2754 DEFAULT_DATE_FORMAT,
2755 DEFAULT_ORG_ID,
2756 ATP_CHECK_FLAG,
2757 WALKIN_ALLOWED_FLAG,
2758 MSITE_ROOT_SECTION_ID,
2759 PROFILE_ID,
2760 MASTER_MSITE_FLAG ,
2761 RESP_ACCESS_FLAG ,
2762 PARTY_ACCESS_CODE ,
2763 ACCESS_NAME ,
2764 URL ,
2765 THEME_ID,
2766 PAYMENT_THRESHOLD_ENABLE_FLAG,
2767 DOMAIN_NAME,
2768 ENABLE_TRAFFIC_FILTER,
2769 REPORTING_STATUS,
2770 SITE_TYPE
2771 from IBE_MSITES_B
2772 where MSITE_ID = X_MSITE_ID
2773 for update of MSITE_ID nowait;
2774 recinfo c%rowtype;
2775
2776 cursor c1 is select
2777 MSITE_NAME,
2778 MSITE_DESCRIPTION,
2779 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
2780 from IBE_MSITES_TL
2781 where MSITE_ID = X_MSITE_ID
2782 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
2783 for update of MSITE_ID nowait;
2784 begin
2785 open c;
2786 fetch c into recinfo;
2787 if (c%notfound) then
2788 close c;
2789 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2790 app_exception.raise_exception;
2791 end if;
2792 close c;
2793 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
2794 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
2795 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
2796 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
2797 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
2798 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
2799 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
2800 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
2801 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
2802 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
2803 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
2804 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
2805 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
2806 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
2807 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
2808 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
2809 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
2810 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
2811 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
2812 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
2813 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
2814 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
2815 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
2816 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
2817 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
2818 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
2819 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
2820 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
2821 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
2822 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
2823 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
2824 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
2825 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
2826 AND ((recinfo.STORE_ID = X_STORE_ID)
2827 OR ((recinfo.STORE_ID is null) AND (X_STORE_ID is null)))
2828 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
2829 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
2830 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
2831 AND ((recinfo.DEFAULT_LANGUAGE_CODE = X_DEFAULT_LANGUAGE_CODE)
2832 OR ((recinfo.DEFAULT_LANGUAGE_CODE is null) AND (X_DEFAULT_LANGUAGE_CODE is null)))
2833 AND ((recinfo.DEFAULT_CURRENCY_CODE = X_DEFAULT_CURRENCY_CODE)
2834 OR ((recinfo.DEFAULT_CURRENCY_CODE is null) AND (X_DEFAULT_CURRENCY_CODE is null)))
2835 AND ((recinfo.DEFAULT_DATE_FORMAT = X_DEFAULT_DATE_FORMAT)
2836 OR ((recinfo.DEFAULT_DATE_FORMAT is null) AND (X_DEFAULT_DATE_FORMAT is null)))
2837 AND ((recinfo.DEFAULT_ORG_ID = X_DEFAULT_ORG_ID)
2838 OR ((recinfo.DEFAULT_ORG_ID is null) AND (X_DEFAULT_ORG_ID is null)))
2839 AND ((recinfo.ATP_CHECK_FLAG = X_ATP_CHECK_FLAG)
2840 OR ((recinfo.ATP_CHECK_FLAG is null) AND (X_ATP_CHECK_FLAG is null)))
2841 AND ((recinfo.WALKIN_ALLOWED_FLAG = X_WALKIN_ALLOWED_FLAG)
2842 OR ((recinfo.WALKIN_ALLOWED_FLAG is null) AND (X_WALKIN_ALLOWED_FLAG is null)))
2843 AND ((recinfo.MSITE_ROOT_SECTION_ID = X_MSITE_ROOT_SECTION_ID)
2844 OR ((recinfo.MSITE_ROOT_SECTION_ID is null) AND (X_MSITE_ROOT_SECTION_ID is null)))
2845 AND ((recinfo.PROFILE_ID = X_PROFILE_ID)
2846 OR ((recinfo.PROFILE_ID is null) AND (X_PROFILE_ID is null)))
2847 AND ((recinfo.MASTER_MSITE_FLAG = X_MASTER_MSITE_FLAG)
2848 OR ((recinfo.MASTER_MSITE_FLAG is null) AND (X_MASTER_MSITE_FLAG is null)))
2849 AND ((recinfo.RESP_ACCESS_FLAG = X_RESP_ACCESS_FLAG )
2850 OR ((recinfo.RESP_ACCESS_FLAG is null) AND (X_RESP_ACCESS_FLAG is null)))
2851 AND ((recinfo.PARTY_ACCESS_CODE = X_PARTY_ACCESS_CODE )
2852 OR ((recinfo.PARTY_ACCESS_CODE is null) AND ( X_PARTY_ACCESS_CODE is null)))
2853 AND ((recinfo.ACCESS_NAME = X_ACCESS_NAME )
2854 OR ((recinfo.ACCESS_NAME is null) AND ( X_ACCESS_NAME is null)))
2855 AND ((recinfo.URL = X_URL )
2856 OR ((recinfo.URL is null) AND ( X_URL is null)))
2857 AND ((recinfo.THEME_ID = X_THEME_ID )
2858 OR ((recinfo.THEME_ID is null) AND ( X_THEME_ID is null)))
2859 AND ((recinfo.PAYMENT_THRESHOLD_ENABLE_FLAG = X_PAYMENT_THRESH_ENABLE_FLAG)
2860 OR ((recinfo.PAYMENT_THRESHOLD_ENABLE_FLAG is null) AND (X_PAYMENT_THRESH_ENABLE_FLAG is null)))
2861 AND ((recinfo.DOMAIN_NAME= X_DOMAIN_NAME)
2862 OR ((recinfo.DOMAIN_NAME is null) AND (X_DOMAIN_NAME is null)))
2863 AND ((recinfo.ENABLE_TRAFFIC_FILTER = X_ENABLE_TRAFFIC_FILTER)
2864 OR ((recinfo.ENABLE_TRAFFIC_FILTER is null) AND (X_ENABLE_TRAFFIC_FILTER is null)))
2865 AND ((recinfo.REPORTING_STATUS = X_REPORTING_STATUS)
2866 OR ((recinfo.REPORTING_STATUS is null) AND (X_REPORTING_STATUS is null)))
2867 AND ((recinfo.SITE_TYPE = X_SITE_TYPE)
2868 OR ((recinfo.SITE_TYPE is null) AND (X_SITE_TYPE is null)))
2869 ) then
2870 null;
2871 else
2872 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2873 app_exception.raise_exception;
2874 end if;
2875
2876 for tlinfo in c1 loop
2877 if (tlinfo.BASELANG = 'Y') then
2878 if ( ((tlinfo.MSITE_NAME = X_MSITE_NAME)
2879 OR ((tlinfo.MSITE_NAME is null) AND (X_MSITE_NAME is null)))
2880 AND ((tlinfo.MSITE_DESCRIPTION = X_MSITE_DESCRIPTION)
2881 OR ((tlinfo.MSITE_DESCRIPTION is null) AND (X_MSITE_DESCRIPTION is null)))
2882 ) then
2883 null;
2884 else
2885 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2886 app_exception.raise_exception;
2887 end if;
2888 end if;
2889 end loop;
2890 return;
2891 end LOCK_ROW;
2892
2893 -- UPDATE_ROW procedure is not being called for updating rows in this package
2894 -- Modifying the procedure to updating 3 new fields for globalisation
2895 -- ssridhar
2896 -- RESP_ACCESS_FLAG
2897 -- PARTY_ACCESS_CODE
2898 -- ACCESS_NAME
2899
2900
2901 procedure UPDATE_ROW (
2902 X_MSITE_ID in NUMBER,
2903 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2904 X_ATTRIBUTE1 in VARCHAR2,
2905 X_ATTRIBUTE2 in VARCHAR2,
2906 X_ATTRIBUTE3 in VARCHAR2,
2907 X_ATTRIBUTE4 in VARCHAR2,
2908 X_ATTRIBUTE5 in VARCHAR2,
2909 X_ATTRIBUTE6 in VARCHAR2,
2910 X_ATTRIBUTE7 in VARCHAR2,
2911 X_ATTRIBUTE8 in VARCHAR2,
2912 X_ATTRIBUTE9 in VARCHAR2,
2913 X_ATTRIBUTE11 in VARCHAR2,
2914 X_ATTRIBUTE10 in VARCHAR2,
2915 X_ATTRIBUTE12 in VARCHAR2,
2916 X_ATTRIBUTE13 in VARCHAR2,
2917 X_ATTRIBUTE14 in VARCHAR2,
2918 X_ATTRIBUTE15 in VARCHAR2,
2919 X_OBJECT_VERSION_NUMBER in NUMBER,
2920 X_STORE_ID in NUMBER,
2921 X_START_DATE_ACTIVE in DATE,
2922 X_END_DATE_ACTIVE in DATE,
2923 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
2924 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
2925 X_DEFAULT_DATE_FORMAT in VARCHAR2,
2926 X_DEFAULT_ORG_ID in NUMBER,
2927 X_ATP_CHECK_FLAG in VARCHAR2,
2928 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
2929 X_MSITE_ROOT_SECTION_ID in NUMBER,
2930 X_PROFILE_ID in NUMBER,
2931 X_MASTER_MSITE_FLAG in VARCHAR2,
2932 X_MSITE_NAME in VARCHAR2,
2933 X_MSITE_DESCRIPTION in VARCHAR2,
2934 X_LAST_UPDATE_DATE in DATE,
2935 X_LAST_UPDATED_BY in NUMBER,
2936 X_LAST_UPDATE_LOGIN in NUMBER ,
2937 X_RESP_ACCESS_FLAG in VARCHAR2 ,
2938 X_PARTY_ACCESS_CODE in VARCHAR2 ,
2939 X_ACCESS_NAME in VARCHAR2 ,
2940 X_URL IN VARCHAR2 ,
2941 X_THEME_ID IN NUMBER ,
2942 X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
2943 X_DOMAIN_NAME in VARCHAR2 := NULL,
2944 X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
2945 X_REPORTING_STATUS in VARCHAR2 := 'N',
2946 X_SITE_TYPE in VARCHAR2 := 'I')
2947 IS
2948 begin
2949 update IBE_MSITES_B set
2950 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
2951 ATTRIBUTE1 = X_ATTRIBUTE1,
2952 ATTRIBUTE2 = X_ATTRIBUTE2,
2953 ATTRIBUTE3 = X_ATTRIBUTE3,
2954 ATTRIBUTE4 = X_ATTRIBUTE4,
2955 ATTRIBUTE5 = X_ATTRIBUTE5,
2956 ATTRIBUTE6 = X_ATTRIBUTE6,
2957 ATTRIBUTE7 = X_ATTRIBUTE7,
2958 ATTRIBUTE8 = X_ATTRIBUTE8,
2959 ATTRIBUTE9 = X_ATTRIBUTE9,
2960 ATTRIBUTE11 = X_ATTRIBUTE11,
2961 ATTRIBUTE10 = X_ATTRIBUTE10,
2962 ATTRIBUTE12 = X_ATTRIBUTE12,
2963 ATTRIBUTE13 = X_ATTRIBUTE13,
2964 ATTRIBUTE14 = X_ATTRIBUTE14,
2965 ATTRIBUTE15 = X_ATTRIBUTE15,
2966 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
2967 STORE_ID = X_STORE_ID,
2968 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
2969 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
2970 DEFAULT_LANGUAGE_CODE = X_DEFAULT_LANGUAGE_CODE,
2971 DEFAULT_CURRENCY_CODE = X_DEFAULT_CURRENCY_CODE,
2972 DEFAULT_DATE_FORMAT = X_DEFAULT_DATE_FORMAT,
2973 DEFAULT_ORG_ID = X_DEFAULT_ORG_ID,
2974 ATP_CHECK_FLAG = X_ATP_CHECK_FLAG,
2975 WALKIN_ALLOWED_FLAG = X_WALKIN_ALLOWED_FLAG,
2976 MSITE_ROOT_SECTION_ID = X_MSITE_ROOT_SECTION_ID,
2977 PROFILE_ID = X_PROFILE_ID,
2978 MASTER_MSITE_FLAG = X_MASTER_MSITE_FLAG,
2979 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2980 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2981 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN ,
2982 RESP_ACCESS_FLAG = X_RESP_ACCESS_FLAG ,
2983 PARTY_ACCESS_CODE = X_PARTY_ACCESS_CODE ,
2984 ACCESS_NAME = X_ACCESS_NAME ,
2985 URL = X_URL ,
2986 THEME_ID = X_THEME_ID ,
2987 PAYMENT_THRESHOLD_ENABLE_FLAG = X_PAYMENT_THRESH_ENABLE_FLAG,
2988 DOMAIN_NAME = X_DOMAIN_NAME,
2989 ENABLE_TRAFFIC_FILTER = X_ENABLE_TRAFFIC_FILTER,
2990 REPORTING_STATUS = X_REPORTING_STATUS,
2991 SITE_TYPE = X_SITE_TYPE
2992 WHERE
2993 MSITE_ID = X_MSITE_ID
2994 AND OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
2995 FND_API.G_MISS_NUM,
2996 OBJECT_VERSION_NUMBER,
2997 X_OBJECT_VERSION_NUMBER);
2998
2999 if (sql%notfound) then
3000 raise no_data_found;
3001 end if;
3002
3003 update IBE_MSITES_TL set
3004 MSITE_NAME = X_MSITE_NAME,
3005 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
3006 MSITE_DESCRIPTION = X_MSITE_DESCRIPTION,
3007 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
3008 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3009 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3010 SOURCE_LANG = userenv('LANG')
3011 where MSITE_ID = X_MSITE_ID
3012 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
3013 and OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
3014 FND_API.G_MISS_NUM,
3015 OBJECT_VERSION_NUMBER,
3016 X_OBJECT_VERSION_NUMBER);
3017
3018 if (sql%notfound) then
3019 raise no_data_found;
3020 end if;
3021 end UPDATE_ROW;
3022
3023 procedure DELETE_ROW (
3024 X_MSITE_ID in NUMBER
3025 ) IS
3026 begin
3027 delete from IBE_MSITES_TL
3028 where MSITE_ID = X_MSITE_ID;
3029
3030 if (sql%notfound) then
3031 raise no_data_found;
3032 end if;
3033
3034 delete from IBE_MSITES_B
3035 where MSITE_ID = X_MSITE_ID;
3036
3037 if (sql%notfound) then
3038 raise no_data_found;
3039 end if;
3040 end DELETE_ROW;
3041
3042 procedure TRANSLATE_ROW (
3043 X_MSITE_ID in NUMBER,
3044 X_OWNER in VARCHAR2,
3045 X_MSITE_NAME in VARCHAR2,
3046 X_MSITE_DESCRIPTION in VARCHAR2,
3047 X_LAST_UPDATE_DATE in varchar2,
3048 X_CUSTOM_MODE in Varchar2
3049 ) IS
3050 f_luby number; -- entity owner in file
3051 f_ludate date; -- entity update date in file
3052 db_luby number; -- entity owner in db
3053 db_ludate date; -- entity update date in db
3054
3055 begin
3056 f_luby := fnd_load_util.owner_id(X_OWNER);
3057 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
3058 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3059 into db_luby, db_ludate
3060 from ibe_msites_tl
3061 where msite_id = X_MSITE_ID
3062 and language=userenv('LANG');-- bug #5089259
3063
3064 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) then
3065
3066 update ibe_msites_tl
3067 set language = USERENV('LANG'),
3068 source_lang = USERENV('LANG'),
3069 object_version_number = object_version_number + 1,
3070 msite_name = X_MSITE_NAME,
3071 msite_description = X_MSITE_DESCRIPTION,
3072 last_updated_by = f_luby,--decode(X_OWNER,'SEED',1,0),
3073 last_update_date = f_ludate, --sysdate,
3074 last_update_login=0
3075 Where userenv('LANG') in (language,source_lang)
3076 and msite_id = X_MSITE_ID;
3077 END IF;
3078 end TRANSLATE_ROW;
3079
3080 -- Modifying the procedure to accept 3 new fields for globalisation
3081 -- ssridhar
3082 -- RESP_ACCESS_FLAG
3083 -- PARTY_ACCESS_CODE
3084 -- ACCESS_NAME
3085
3086
3087
3088 procedure LOAD_ROW (
3089 X_MSITE_ID in NUMBER,
3090 X_OWNER in VARCHAR2,
3091 X_ATTRIBUTE_CATEGORY in VARCHAR2,
3092 X_ATTRIBUTE1 in VARCHAR2,
3093 X_ATTRIBUTE2 in VARCHAR2,
3094 X_ATTRIBUTE3 in VARCHAR2,
3095 X_ATTRIBUTE4 in VARCHAR2,
3096 X_ATTRIBUTE5 in VARCHAR2,
3097 X_ATTRIBUTE6 in VARCHAR2,
3098 X_ATTRIBUTE7 in VARCHAR2,
3099 X_ATTRIBUTE8 in VARCHAR2,
3100 X_ATTRIBUTE9 in VARCHAR2,
3101 X_ATTRIBUTE11 in VARCHAR2,
3102 X_ATTRIBUTE10 in VARCHAR2,
3103 X_ATTRIBUTE12 in VARCHAR2,
3104 X_ATTRIBUTE13 in VARCHAR2,
3105 X_ATTRIBUTE14 in VARCHAR2,
3106 X_ATTRIBUTE15 in VARCHAR2,
3107 X_OBJECT_VERSION_NUMBER in NUMBER,
3108 X_STORE_ID in NUMBER,
3109 X_START_DATE_ACTIVE in DATE,
3110 X_END_DATE_ACTIVE in DATE,
3111 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
3112 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
3113 X_DEFAULT_DATE_FORMAT in VARCHAR2,
3114 X_DEFAULT_ORG_ID in NUMBER,
3115 X_ATP_CHECK_FLAG in VARCHAR2,
3116 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
3117 X_MSITE_ROOT_SECTION_ID in NUMBER,
3118 X_PROFILE_ID in NUMBER,
3119 X_MASTER_MSITE_FLAG in VARCHAR2,
3120 X_MSITE_NAME in VARCHAR2,
3121 X_MSITE_DESCRIPTION in VARCHAR2 ,
3122 X_RESP_ACCESS_FLAG in VARCHAR2 ,
3123 X_PARTY_ACCESS_CODE in VARCHAR2 ,
3124 X_ACCESS_NAME in VARCHAR2 ,
3125 X_URL in VARCHAR2 ,
3126 X_THEME_ID in NUMBER ,
3127 X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
3128 X_DOMAIN_NAME in VARCHAR2 := NULL,
3129 X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
3130 X_REPORTING_STATUS in VARCHAR2 := 'N',
3131 X_SITE_TYPE in VARCHAR2 := 'I',
3132 X_LAST_UPDATE_DATE in varchar2,
3133 X_CUSTOM_MODE in Varchar2)
3134 IS
3135
3136 Owner_id NUMBER := 0;
3137 Row_Id VARCHAR2(64);
3138 l_object_version_number NUMBER := 1;
3139 f_luby number; -- entity owner in file
3140 f_ludate date; -- entity update date in file
3141 db_luby number; -- entity owner in db
3142 db_ludate date; -- entity update date in db
3143
3144
3145 Begin
3146 f_luby := fnd_load_util.owner_id(X_OWNER);
3147 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
3148 select LAST_UPDATED_BY, LAST_UPDATE_DATE
3149 into db_luby, db_ludate
3150 from ibe_msites_b
3151 where MSITE_ID = X_MSITE_ID;
3152
3153
3154
3155
3156 IF ((x_object_version_number IS NOT NULL) AND
3157 (x_object_version_number <> FND_API.G_MISS_NUM))
3158 THEN
3159 l_object_version_number := x_object_version_number;
3160 END IF;
3161 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, X_CUSTOM_MODE)) then
3162 UPDATE_ROW(
3163 X_MSITE_ID => X_MSITE_ID,
3164 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
3165 X_ATTRIBUTE1 => X_ATTRIBUTE1,
3166 X_ATTRIBUTE2 => X_ATTRIBUTE2,
3167 X_ATTRIBUTE3 => X_ATTRIBUTE3,
3168 X_ATTRIBUTE4 => X_ATTRIBUTE4,
3169 X_ATTRIBUTE5 => X_ATTRIBUTE5,
3170 X_ATTRIBUTE6 => X_ATTRIBUTE6,
3171 X_ATTRIBUTE7 => X_ATTRIBUTE7,
3172 X_ATTRIBUTE8 => X_ATTRIBUTE8,
3173 X_ATTRIBUTE9 => X_ATTRIBUTE9,
3174 X_ATTRIBUTE11 => X_ATTRIBUTE10,
3175 X_ATTRIBUTE10 => X_ATTRIBUTE11,
3176 X_ATTRIBUTE12 => X_ATTRIBUTE12,
3177 X_ATTRIBUTE13 => X_ATTRIBUTE13,
3178 X_ATTRIBUTE14 => X_ATTRIBUTE14,
3179 X_ATTRIBUTE15 => X_ATTRIBUTE15,
3180 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
3181 X_STORE_ID => X_STORE_ID,
3182 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
3183 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
3184 X_DEFAULT_LANGUAGE_CODE => X_DEFAULT_LANGUAGE_CODE,
3185 X_DEFAULT_CURRENCY_CODE => X_DEFAULT_CURRENCY_CODE,
3186 X_DEFAULT_DATE_FORMAT => X_DEFAULT_DATE_FORMAT,
3187 X_DEFAULT_ORG_ID => X_DEFAULT_ORG_ID,
3188 X_ATP_CHECK_FLAG => X_ATP_CHECK_FLAG,
3189 X_WALKIN_ALLOWED_FLAG => X_WALKIN_ALLOWED_FLAG,
3190 X_MSITE_ROOT_SECTION_ID => X_MSITE_ROOT_SECTION_ID,
3191 X_PROFILE_ID => X_PROFILE_ID,
3192 X_MASTER_MSITE_FLAG => X_MASTER_MSITE_FLAG,
3193 X_MSITE_NAME => X_MSITE_NAME,
3194 X_MSITE_DESCRIPTION => X_MSITE_DESCRIPTION,
3195 X_LAST_UPDATE_DATE => f_ludate, --SYSDATE,
3196 X_LAST_UPDATED_BY => f_luby,--Owner_id,
3197 X_LAST_UPDATE_LOGIN => 0 ,
3198 X_RESP_ACCESS_FLAG => X_RESP_ACCESS_FLAG ,
3199 X_PARTY_ACCESS_CODE => X_PARTY_ACCESS_CODE ,
3200 X_ACCESS_NAME => X_ACCESS_NAME ,
3201 X_URL => X_URL,
3202 X_THEME_ID => X_THEME_ID,
3203 X_PAYMENT_THRESH_ENABLE_FLAG => X_PAYMENT_THRESH_ENABLE_FLAG,
3204 X_DOMAIN_NAME => X_DOMAIN_NAME,
3205 X_ENABLE_TRAFFIC_FILTER => X_ENABLE_TRAFFIC_FILTER,
3206 X_REPORTING_STATUS => X_REPORTING_STATUS,
3207 X_SITE_TYPE => X_SITE_TYPE );
3208 END IF;
3209 Exception
3210
3211 When NO_DATA_FOUND Then
3212 INSERT_ROW(
3213 X_ROWID => Row_id,
3214 X_MSITE_ID => X_MSITE_ID,
3215 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
3216 X_ATTRIBUTE1 => X_ATTRIBUTE1,
3217 X_ATTRIBUTE2 => X_ATTRIBUTE2,
3218 X_ATTRIBUTE3 => X_ATTRIBUTE3,
3219 X_ATTRIBUTE4 => X_ATTRIBUTE4,
3220 X_ATTRIBUTE5 => X_ATTRIBUTE5,
3221 X_ATTRIBUTE6 => X_ATTRIBUTE6,
3222 X_ATTRIBUTE7 => X_ATTRIBUTE7,
3223 X_ATTRIBUTE8 => X_ATTRIBUTE8,
3224 X_ATTRIBUTE9 => X_ATTRIBUTE9,
3225 X_ATTRIBUTE11 => X_ATTRIBUTE10,
3226 X_ATTRIBUTE10 => X_ATTRIBUTE11,
3227 X_ATTRIBUTE12 => X_ATTRIBUTE12,
3228 X_ATTRIBUTE13 => X_ATTRIBUTE13,
3229 X_ATTRIBUTE14 => X_ATTRIBUTE14,
3230 X_ATTRIBUTE15 => X_ATTRIBUTE15,
3231 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
3232 X_STORE_ID => X_STORE_ID,
3233 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
3234 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
3235 X_DEFAULT_LANGUAGE_CODE => X_DEFAULT_LANGUAGE_CODE,
3236 X_DEFAULT_CURRENCY_CODE => X_DEFAULT_CURRENCY_CODE,
3237 X_DEFAULT_DATE_FORMAT => X_DEFAULT_DATE_FORMAT,
3238 X_DEFAULT_ORG_ID => X_DEFAULT_ORG_ID,
3239 X_ATP_CHECK_FLAG => X_ATP_CHECK_FLAG,
3240 X_WALKIN_ALLOWED_FLAG => X_WALKIN_ALLOWED_FLAG,
3241 X_MSITE_ROOT_SECTION_ID => X_MSITE_ROOT_SECTION_ID,
3242 X_PROFILE_ID => X_PROFILE_ID,
3243 X_MASTER_MSITE_FLAG => X_MASTER_MSITE_FLAG,
3244 X_MSITE_NAME => X_MSITE_NAME,
3245 X_MSITE_DESCRIPTION => X_MSITE_DESCRIPTION,
3246 X_CREATION_DATE => f_ludate, --SYSDATE,
3247 X_CREATED_BY => f_luby,--Owner_id,
3248 X_LAST_UPDATE_DATE => f_ludate, --SYSDATE,
3249 X_LAST_UPDATED_BY => f_luby,--Owner_id,
3250 X_LAST_UPDATE_LOGIN => 0 ,
3251 X_RESP_ACCESS_FLAG => X_RESP_ACCESS_FLAG ,
3252 X_PARTY_ACCESS_CODE => X_PARTY_ACCESS_CODE ,
3253 X_ACCESS_NAME => X_ACCESS_NAME ,
3254 X_URL => X_URL,
3255 X_THEME_ID => X_THEME_ID,
3256 X_PAYMENT_THRESH_ENABLE_FLAG => X_PAYMENT_THRESH_ENABLE_FLAG,
3257 X_DOMAIN_NAME => X_DOMAIN_NAME,
3258 X_ENABLE_TRAFFIC_FILTER => X_ENABLE_TRAFFIC_FILTER,
3259 X_REPORTING_STATUS => X_REPORTING_STATUS,
3260 X_SITE_TYPE => X_SITE_TYPE );
3261
3262 End LOAD_ROW;
3263
3264 procedure ADD_LANGUAGE
3265 is
3266 begin
3267 delete from IBE_MSITES_TL T
3268 where not exists
3269 (select NULL
3270 from IBE_MSITES_B B
3271 where B.MSITE_ID = T.MSITE_ID
3272 );
3273
3274 update IBE_MSITES_TL T set (
3275 MSITE_NAME,
3276 MSITE_DESCRIPTION
3277 ) = (select
3278 B.MSITE_NAME,
3279 B.MSITE_DESCRIPTION
3280 from IBE_MSITES_TL B
3281 where B.MSITE_ID = T.MSITE_ID
3282 and B.LANGUAGE = T.SOURCE_LANG)
3283 where (
3284 T.MSITE_ID,
3285 T.LANGUAGE
3286 ) in (select
3287 SUBT.MSITE_ID,
3288 SUBT.LANGUAGE
3289 from IBE_MSITES_TL SUBB, IBE_MSITES_TL SUBT
3290 where SUBB.MSITE_ID = SUBT.MSITE_ID
3291 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
3292 and (SUBB.MSITE_NAME <> SUBT.MSITE_NAME
3293 or (SUBB.MSITE_NAME is null and SUBT.MSITE_NAME is not null)
3294 or (SUBB.MSITE_NAME is not null and SUBT.MSITE_NAME is null)
3295 or SUBB.MSITE_DESCRIPTION <> SUBT.MSITE_DESCRIPTION
3296 or (SUBB.MSITE_DESCRIPTION is null and SUBT.MSITE_DESCRIPTION is not null)
3297 or (SUBB.MSITE_DESCRIPTION is not null and SUBT.MSITE_DESCRIPTION is null)
3298 ));
3299
3300 insert into IBE_MSITES_TL (
3301 MSITE_ID,
3302 OBJECT_VERSION_NUMBER,
3303 CREATED_BY,
3304 CREATION_DATE,
3305 LAST_UPDATED_BY,
3306 LAST_UPDATE_DATE,
3307 LAST_UPDATE_LOGIN,
3308 MSITE_NAME,
3309 MSITE_DESCRIPTION,
3310 LANGUAGE,
3311 SOURCE_LANG
3312 ) select
3313 B.MSITE_ID,
3314 B.OBJECT_VERSION_NUMBER,
3315 B.CREATED_BY,
3316 B.CREATION_DATE,
3317 B.LAST_UPDATED_BY,
3318 B.LAST_UPDATE_DATE,
3319 B.LAST_UPDATE_LOGIN,
3320 B.MSITE_NAME,
3321 B.MSITE_DESCRIPTION,
3322 L.LANGUAGE_CODE,
3323 B.SOURCE_LANG
3324 from IBE_MSITES_TL B, FND_LANGUAGES L
3325 where L.INSTALLED_FLAG in ('I', 'B')
3326 and B.LANGUAGE = userenv('LANG')
3327 and not exists
3328 (select NULL
3329 from IBE_MSITES_TL T
3330 where T.MSITE_ID = B.MSITE_ID
3331 and T.LANGUAGE = L.LANGUAGE_CODE);
3332 end ADD_LANGUAGE;
3333
3334 PROCEDURE LOAD_SEED_ROW
3335 (
3336 X_MSITE_ID in NUMBER,
3337 X_OWNER in VARCHAR2,
3338 X_MSITE_NAME in VARCHAR2,
3339 X_MSITE_DESCRIPTION in VARCHAR2,
3340 X_ATTRIBUTE_CATEGORY in VARCHAR2,
3341 X_ATTRIBUTE1 in VARCHAR2,
3342 X_ATTRIBUTE2 in VARCHAR2,
3343 X_ATTRIBUTE3 in VARCHAR2,
3344 X_ATTRIBUTE4 in VARCHAR2,
3345 X_ATTRIBUTE5 in VARCHAR2,
3346 X_ATTRIBUTE6 in VARCHAR2,
3347 X_ATTRIBUTE7 in VARCHAR2,
3348 X_ATTRIBUTE8 in VARCHAR2,
3349 X_ATTRIBUTE9 in VARCHAR2,
3350 X_ATTRIBUTE10 in VARCHAR2,
3351 X_ATTRIBUTE11 in VARCHAR2,
3352 X_ATTRIBUTE12 in VARCHAR2,
3353 X_ATTRIBUTE13 in VARCHAR2,
3354 X_ATTRIBUTE14 in VARCHAR2,
3355 X_ATTRIBUTE15 in VARCHAR2,
3356 X_OBJECT_VERSION_NUMBER in NUMBER,
3357 X_STORE_ID in NUMBER,
3358 X_START_DATE_ACTIVE IN VARCHAR2,--IN DATE,
3359 X_END_DATE_ACTIVE IN VARCHAR2,-- IN DATE,
3360 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
3361 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
3362 X_DEFAULT_DATE_FORMAT in VARCHAR2,
3363 X_DEFAULT_ORG_ID in NUMBER,
3364 X_ATP_CHECK_FLAG in VARCHAR2,
3365 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
3366 X_MSITE_ROOT_SECTION_ID in NUMBER,
3367 X_PROFILE_ID in NUMBER,
3368 X_MASTER_MSITE_FLAG in VARCHAR2,
3369 X_RESP_ACCESS_FLAG in VARCHAR2 ,
3370 X_PARTY_ACCESS_CODE in VARCHAR2 ,
3371 X_ACCESS_NAME in VARCHAR2 ,
3372 X_URL in VARCHAR2 ,
3373 X_THEME_ID in VARCHAR2 ,
3374 X_PAYMENT_THRESH_ENABLE_FLAG in VARCHAR2 := NULL,
3375 X_DOMAIN_NAME in VARCHAR2 := NULL,
3376 X_ENABLE_TRAFFIC_FILTER in VARCHAR2 := 'N',
3377 X_REPORTING_STATUS in VARCHAR2 := 'N',
3378 X_SITE_TYPE in VARCHAR2 := 'I',
3379 X_LAST_UPDATE_DATE IN VARCHAR2,
3380 X_CUSTOM_MODE IN VARCHAR2,
3381 X_UPLOAD_MODE IN VARCHAR2
3382 )
3383 IS
3384 BEGIN
3385 IF (X_UPLOAD_MODE = 'NLS') then
3386
3387 TRANSLATE_ROW(
3388 X_MSITE_ID,
3389 X_OWNER,
3390 X_MSITE_NAME,
3391 X_MSITE_DESCRIPTION,
3392 X_LAST_UPDATE_DATE,
3393 X_CUSTOM_MODE);
3394 Else
3395 LOAD_ROW(
3396 X_MSITE_ID,
3397 X_OWNER,
3398 X_ATTRIBUTE_CATEGORY,
3399 X_ATTRIBUTE1,
3400 X_ATTRIBUTE2,
3401 X_ATTRIBUTE3,
3402 X_ATTRIBUTE4,
3403 X_ATTRIBUTE5,
3404 X_ATTRIBUTE6,
3405 X_ATTRIBUTE7,
3406 X_ATTRIBUTE8,
3407 X_ATTRIBUTE9,
3408 X_ATTRIBUTE10,
3409 X_ATTRIBUTE11,
3410 X_ATTRIBUTE12,
3411 X_ATTRIBUTE13,
3412 X_ATTRIBUTE14,
3413 X_ATTRIBUTE15,
3414 X_OBJECT_VERSION_NUMBER,
3415 X_STORE_ID,
3416 to_date(X_START_DATE_ACTIVE,'YYYY/MM/DD'),
3417 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD'),
3418 X_DEFAULT_LANGUAGE_CODE,
3419 X_DEFAULT_CURRENCY_CODE,
3420 X_DEFAULT_DATE_FORMAT,
3421 X_DEFAULT_ORG_ID,
3422 X_ATP_CHECK_FLAG,
3423 X_WALKIN_ALLOWED_FLAG,
3424 X_MSITE_ROOT_SECTION_ID,
3425 X_PROFILE_ID,
3426 X_MASTER_MSITE_FLAG,
3427 X_MSITE_NAME,
3428 X_MSITE_DESCRIPTION,
3429 X_RESP_ACCESS_FLAG,
3430 X_PARTY_ACCESS_CODE,
3431 X_ACCESS_NAME,
3432 X_URL,
3433 X_THEME_ID,
3434 X_PAYMENT_THRESH_ENABLE_FLAG,
3435 X_DOMAIN_NAME,
3436 X_ENABLE_TRAFFIC_FILTER,
3437 X_REPORTING_STATUS,
3438 X_SITE_TYPE,
3439 X_LAST_UPDATE_DATE,
3440 X_CUSTOM_MODE);
3441 End If;
3442
3443
3444 END LOAD_SEED_ROW;
3445
3446
3447 END IBE_Msite_GRP;