[Home] [Help]
PACKAGE BODY: APPS.JTF_MSITE_GRP
Source
1 PACKAGE BODY JTF_MSite_GRP AS
2 /* $Header: JTFGMSTB.pls 115.26 2004/07/09 18:50:04 applrt 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 FUNCTION msite_enabled_for_store(p_msite_id IN NUMBER )
14 RETURN BOOLEAN
15 IS
16 CURSOR valid_store_msite_cur(p_msite_id IN NUMBER) IS
17 select 1 from dual where exists (
18 select msite_id
19 from jtf_msites_b a ,
20 jtf_stores_b b
21 WHERE a.msite_id = p_msite_id
22 and b.store_id = a.store_id );
23 l_exists NUMBER;
24 l_return_status Boolean := false;
25 BEGIN
26 OPEN valid_store_msite_cur(p_msite_id);
27 FETCH valid_store_msite_cur INTO l_exists;
28 IF valid_store_msite_cur%FOUND THEN
29 l_return_status := true;
30 end if;
31 close valid_store_msite_cur;
32 return l_return_status;
33 EXCEPTION
34 WHEN OTHERS THEN
35 IF valid_store_msite_cur%ISOPEN THEN
36 close valid_store_msite_cur;
37 END IF;
38 return false;
39 END msite_enabled_for_store;
40
41 FUNCTION msite_default_lang_exists(p_msite_id IN NUMBER )
42 RETURN BOOLEAN
43 IS
44 CURSOR valid_msite_lang_cur(p_msite_id IN NUMBER) IS
45 select 1 from dual where exists (
46 select msite_id
47 from jtf_msites_b
48 where msite_id = p_msite_id
49 and default_language_code is not null );
50
51 l_exists NUMBER;
52 l_return_status Boolean := false;
53 BEGIN
54 OPEN valid_msite_lang_cur(p_msite_id);
55 FETCH valid_msite_lang_cur INTO l_exists;
56 IF valid_msite_lang_cur%FOUND THEN
57 l_return_status := true;
58 end if;
59 close valid_msite_lang_cur;
60 return l_return_status;
61 EXCEPTION
62 WHEN OTHERS THEN
63 IF valid_msite_lang_cur%ISOPEN THEN
64 close valid_msite_lang_cur;
65 END IF;
66 return false;
67 END msite_default_lang_exists;
68
69
70 FUNCTION msite_default_currency_exists(p_msite_id IN NUMBER )
71 RETURN BOOLEAN
72 IS
73 CURSOR valid_msite_currency_cur(p_msite_id IN NUMBER) IS
74 select 1 from dual where exists (
75 select msite_id from jtf_msites_b where
76 msite_id = p_msite_id and default_currency_code is not null );
77
78 l_exists NUMBER;
79 l_return_status Boolean := false;
80
81 BEGIN
82
83 OPEN valid_msite_currency_cur(p_msite_id);
84 FETCH valid_msite_currency_cur INTO l_exists;
85
86 IF valid_msite_currency_cur%FOUND THEN
87 l_return_status := true;
88 end if;
89
90 close valid_msite_currency_cur;
91
92 return l_return_status;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 IF valid_msite_currency_cur%ISOPEN THEN
97 close valid_msite_currency_cur;
98 END IF;
99 return false;
100 END msite_default_currency_exists;
101
102
103 FUNCTION msite_default_org_exists(p_msite_id IN NUMBER )
104 RETURN BOOLEAN
105 IS
106 CURSOR valid_msite_org_cur(p_msite_id IN NUMBER) IS
107 select 1 from dual where exists (
108 select msite_id from jtf_msites_b where
109 msite_id = p_msite_id and default_org_id is not null );
110
111 l_exists NUMBER;
112 l_return_status Boolean := false;
113
114 BEGIN
115
116 OPEN valid_msite_org_cur(p_msite_id);
117 FETCH valid_msite_org_cur INTO l_exists;
118
119 IF valid_msite_org_cur%FOUND THEN
120 l_return_status := true;
121 end if;
122
123 close valid_msite_org_cur;
124
125 return l_return_status;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF valid_msite_org_cur%ISOPEN THEN
130 close valid_msite_org_cur;
131 END IF;
132 return false;
133 END msite_default_org_exists;
134
135
136 FUNCTION valid_language(p_language VARCHAR2)
137 RETURN BOOLEAN
138 IS
139 CURSOR valid_language_cur(p_language varchar2) IS
140 select 1 from dual where exists (
141 select language_code from fnd_languages_vl where
142 language_code = p_language);
143 l_exists NUMBER;
144 l_return_status Boolean := true;
145
146 BEGIN
147
148 OPEN valid_language_cur(p_language);
149 FETCH valid_language_cur INTO l_exists;
150
151 IF valid_language_cur%NOTFOUND THEN
152 l_return_status := false;
153 end if;
154
155 close valid_language_cur;
156
157 if l_return_status = false then
158 raise FND_API.g_exc_error;
159 -----dbms_output.put_line('invalid languages:' || p_language);
160 END IF;
161
162 -----dbms_output.put_line('valid languages:' || p_language);
163 return l_return_status;
164
165 EXCEPTION
166 WHEN OTHERS THEN
167 IF valid_language_cur%ISOPEN THEN
168 close valid_language_cur;
169 END IF;
170 FND_MESSAGE.set_name('JTF','JTF_MSITE_LANG_INVLD');
171 FND_MESSAGE.set_token('0', p_language);
172 FND_MSG_PUB.ADD;
173 -----dbms_output.put_line('invalid languages');
174 return false;
175 END valid_language;
176
177
178 FUNCTION valid_orgid(p_orgid NUMBER)
179 RETURN BOOLEAN
180 IS
181 CURSOR valid_orgid_cur(p_orgid varchar2) IS
182 select 1 from dual where exists (
183 select organization_id from hr_operating_units where
184 organization_id = p_orgid);
185 l_exists NUMBER;
186 l_return_status Boolean := true;
187
188 BEGIN
189
190 OPEN valid_orgid_cur(p_orgid);
191 FETCH valid_orgid_cur INTO l_exists;
192 IF valid_orgid_cur%NOTFOUND THEN
193 l_return_status := false;
194 end if;
195
196 if l_return_status = false then
197 raise FND_API.g_exc_error;
198 end if;
199
200 -----dbms_output.put_line('invalid orgid:' || p_orgid);
201
202 return l_return_status;
203
204 EXCEPTION
205 WHEN OTHERS THEN
206 IF valid_orgid_cur%ISOPEN THEN
207 close valid_orgid_cur;
208 END IF;
209 FND_MESSAGE.set_name('JTF','JTF_MSITE_ORGID_INVLD');
210 FND_MESSAGE.set_token('0', p_orgid);
211 FND_MSG_PUB.ADD;
212 --dbms_output.put_line('invalid Org id');
213 return false;
214 END valid_orgid;
215
216
217 FUNCTION valid_currency(p_currency VARCHAR2)
218 RETURN BOOLEAN
219 IS
220 CURSOR valid_currency_cur(p_currency varchar2) IS
221 select 1 from dual where exists (
222 select currency_code from fnd_currencies_vl where
223 currency_code = p_currency);
224 l_exists NUMBER;
225 l_return_status Boolean := true;
226
227 BEGIN
228
229 OPEN valid_currency_cur(p_currency);
230 FETCH valid_currency_cur INTO l_exists;
231 IF valid_currency_cur%NOTFOUND THEN
232 l_return_status := false;
233 --dbms_output.put_line('error in currency code1:' || p_currency);
234 end if;
235
236 close valid_currency_cur;
237 if l_return_status = false then
238 raise FND_API.g_exc_error;
239 END IF;
240
241 return l_return_status;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 IF valid_currency_cur%ISOPEN THEN
246 close valid_currency_cur;
247 END IF;
248 FND_MESSAGE.set_name('JTF','JTF_MSITE_CURRENCY_INVLD');
249 FND_MESSAGE.set_token('0', p_currency);
250 FND_MSG_PUB.ADD;
251 --dbms_output.put_line('error in currency code');
252 return false;
253
254 END valid_currency;
255
256
257 FUNCTION valid_prc_lstids(p_currency VARCHAR2,
258 p_walkin_prclstid number,
259 p_registered_prclstid number,
260 p_bizpartner_prclstid NUMBER)
261 RETURN BOOLEAN
262 IS
263 CURSOR valid_currency_prclstid_cur(p_currency varchar2,
264 p_walkin_prclstid number,
265 p_registered_prclstid number,
266 p_bizpartner_prclstid NUMBER)
267 IS
268 select 1 from dual where exists (
269 select list_header_id from qp_list_headers_v where
270 currency_code = p_currency and
271 list_header_id in (p_walkin_prclstid,p_registered_prclstid,p_bizpartner_prclstid));
272
273 l_exists NUMBER;
274 l_return_status Boolean := true;
275
276 BEGIN
277
278 if p_walkin_prclstid is null or p_registered_prclstid is null or
279 p_bizpartner_prclstid is null
280 then
281 FND_MESSAGE.set_name('JTF','JTF_MSITE_PRCLSTID_REQ');
282 FND_MESSAGE.set_token('CURR_CODE', p_currency);
283 FND_MSG_PUB.ADD;
284 raise FND_API.g_exc_error;
285 END IF;
286
287 OPEN valid_currency_prclstid_cur(p_currency,p_walkin_prclstid,p_registered_prclstid,
288 p_bizpartner_prclstid);
289 FETCH valid_currency_prclstid_cur INTO l_exists;
290
291
292 IF valid_currency_prclstid_cur%NOTFOUND THEN
293 l_return_status := false;
294 END IF;
295 close valid_currency_prclstid_cur;
296
297 if l_return_status = false
298 then
299 FND_MESSAGE.set_name('JTF','JTF_MSITE_PRCLSTID_INVLD');
300 FND_MESSAGE.set_token('WALKIN_ID',p_walkin_prclstid);
301 FND_MESSAGE.set_token('REG_ID' , p_registered_prclstid);
302 FND_MESSAGE.set_token('BIZ_ID' , p_bizpartner_prclstid);
303 FND_MESSAGE.set_token('CURR_CODE',p_currency);
304 FND_MSG_PUB.ADD;
305 end if;
306
307 return l_return_status;
308
309 EXCEPTION
310 WHEN OTHERS THEN
311 IF valid_currency_prclstid_cur%ISOPEN THEN
312 close valid_currency_prclstid_cur;
313 END IF;
314 return FALSE;
315 END valid_prc_lstids;
316
317
318 PROCEDURE save_msite(
319 p_api_version IN NUMBER,
320 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
321 p_commit IN VARCHAR2 := FND_API.g_false,
322 x_return_status OUT VARCHAR2,
323 x_msg_count OUT NUMBER,
324 x_msg_data OUT VARCHAR2,
325 p_msite_rec IN OUT Msite_REC_TYPE )
326 IS
327 l_api_name CONSTANT VARCHAR2(30) := 'save_msite';
328 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
329 l_operation_type VARCHAR2(10) := 'INSERT';
330 l_msite_id NUMBER;
331 walkin_allowed_code VARCHAR2(1);
332 l_atp_check_flag VARCHAR2(1);
333 l_master_exists NUMBER;
334 l_msite_master_flag VARCHAR2(1);
335 l_store_id NUMBER := NULL;
336 l_exists NUMBER ;
337 l_root_section_flag VARCHAR2(1) := FND_API.g_false;
338 l_return_status VARCHAR2(1);
339 l_msg_count NUMBER;
340 l_msg_data VARCHAR2(80);
341 l_root_section_id NUMBER := NULL;
342 l_resp_access_flag VARCHAR2(1);
343 l_party_access_code VARCHAR2(1);
344
345 CURSOR msite_id_seq IS
346 SELECT jtf_msites_b_s1.NEXTVAL
347 FROM DUAL;
348
349 CURSOR store_id_cur IS
350 select store_id from jtf_stores_b
351 where rownum < 2 ;
352
353 CURSOR master_msite_any_cur IS
354 select 1 from dual where exists (
355 select msite_id from jtf_msites_b
356 where master_msite_flag = 'Y') ;
357
358 CURSOR master_msite_cur(p_msite_id IN NUMBER) IS
359 select 1 from dual
360 where exists (
361 select msite_id
362 from jtf_msites_b
363 where master_msite_flag = 'Y'
364 AND msite_id <> p_msite_id);
365
366 CURSOR yes_no_cur (p_code in varchar2) IS
367 select 1 from dual where exists (
368 select lookup_code
369 from fnd_lookup_values_vl
370 where lookup_type='YES_NO'
371 and lookup_code=p_code);
372
373 CURSOR C_party_access_code (p_code in varchar2) IS
374 select 1 from fnd_lookup_values_vl
375 where lookup_type = 'JTF_PARTY_ACCESS_CODE'
376 and lookup_code = p_code ;
377 BEGIN
378
379 --------------------- initialize -----------------------+
380 SAVEPOINT save_msite;
381
382 IF NOT FND_API.compatible_api_call(
383 g_api_version,
384 p_api_version,
385 l_api_name,
386 g_pkg_name ) THEN
387 RAISE FND_API.g_exc_unexpected_error;
388 END IF;
389
390
391 IF FND_API.to_boolean(p_init_msg_list) THEN
392 FND_MSG_PUB.initialize;
393 END IF;
394
395
396 x_return_status := FND_API.G_RET_STS_SUCCESS;
397
398 --- Check if the msite_id exists
399 IF p_msite_rec.msite_id IS NOT NULL AND
400 p_msite_rec.msite_id <> FND_API.g_miss_num
401 THEN
402 --dbms_output.put_line('Minisite id is passed ' );
403
404 if jtf_dspmgrvalidation_grp.check_msite_exists(
405 p_msite_rec.msite_id, p_msite_rec.Object_version_Number) = false
406 then
407 raise FND_API.g_exc_error;
408 end if;
409
410 l_operation_type:='UPDATE';
411
412 --dbms_output.put_line('Operation is an update ' );
413
414 END IF;
415 if p_msite_rec.msite_root_section_id <> FND_API.g_miss_num AND
416 p_msite_rec.msite_root_section_id is not null then
417
418 if jtf_dspmgrvalidation_grp.check_root_section_exists(
419 p_msite_rec.msite_root_section_id) = false
420 then
421 raise FND_API.g_exc_error;
422 end if;
423
424 l_root_section_flag := FND_API.g_true;
425 l_root_section_id := p_msite_rec.msite_root_section_id;
426 else
427 --- If the minisite is a new one, then root section id can be null ,
428 --todo
429 if (p_msite_rec.msite_id is not null and
430 p_msite_rec.enable_for_store = FND_API.g_true) or
431 p_msite_rec.enable_for_store = FND_API.g_true
432 then
433 FND_MESSAGE.set_name('JTF','JTF_MSITE_RSECID_INVLD');
434 FND_MSG_PUB.ADD;
435 raise FND_API.g_exc_error;
436 end if;
437 end if;
438
439 if p_msite_rec.msite_master_flag = FND_API.g_true then
440 raise master_msite_exists_exception;
441 else
442 l_msite_master_flag := 'N';
443 end if;
444
445
446 --dbms_output.put_line('passed master mini site flag teste ' );
447 -- Check if the access_name for a minisite is unique
448 If ( p_msite_rec.msite_id IS NULL OR
449 p_msite_rec.msite_id = FND_API.g_miss_num) AND
450 (p_msite_rec.access_name IS NOT NULL AND
451 p_msite_rec.access_name <> FND_API.G_MISS_CHAR) Then
452 If Jtf_Dspmgrvalidation_Grp.Check_Msite_Accessname(
453 p_access_name => p_msite_rec.access_name)= FALSE
454 Then
455 Raise FND_API.G_EXC_ERROR ;
456 End If;
457 End If;
458
459 if p_msite_rec.enable_for_store = FND_API.g_true then
460 --dbms_output.put_line('****************enabled for store is to true ' );
461 OPEN store_id_cur;
462 fetch store_id_cur into l_store_id;
463 if store_id_cur%NOTFOUND then
464 close store_id_cur;
465 raise store_not_exists_exception;
466 end if;
467 close store_id_cur;
468
469 if jtf_dspmgrvalidation_grp.check_root_section_exists(
470 p_msite_rec.msite_root_section_id) = false
471 then
472 raise FND_API.g_exc_error;
473 end if;
474
475 if msite_default_lang_exists(p_msite_rec.msite_id) = false
476 then
477 raise msite_default_lang_missing;
478 end if;
479
480 if msite_default_currency_exists(p_msite_rec.msite_id) = false
481 then
482 raise msite_default_currency_missing;
483 end if;
484 /*** REDUNDANT AS ORG ID WILL BE DETERMINED BY RESPONSIBILITY
485 if msite_default_org_exists(p_msite_rec.msite_id) = false then
486 raise msite_default_org_missing;
487 end if;
488 ******/
489 end if;
490
491 walkin_allowed_code := p_msite_rec.walkin_allowed_code;
492 OPEN yes_no_cur(walkin_allowed_code);
493 FETCH yes_no_cur INTO l_exists;
494 IF yes_no_cur%NOTFOUND THEN
495 walkin_allowed_code := 'N';
496 END IF;
497 close yes_no_cur;
498
499 l_atp_check_flag := p_msite_rec.atp_check_flag;
500 OPEN yes_no_cur(l_atp_check_flag);
501 FETCH yes_no_cur INTO l_exists;
502 IF yes_no_cur%NOTFOUND THEN
503 l_atp_check_flag := 'N';
504 END IF;
505 close yes_no_cur;
506
507 l_resp_access_flag := p_msite_rec.resp_access_flag;
508 IF (((l_operation_type = 'UPDATE') AND
509 (l_resp_access_flag IS NOT NULL AND
510 l_resp_access_flag <> FND_API.G_MISS_char)) OR
511 l_operation_type = 'INSERT')
512 THEN
513 OPEN yes_no_cur(l_resp_access_flag );
514 FETCH yes_no_cur INTO l_exists;
515 IF yes_no_cur%NOTFOUND THEN
516 l_resp_access_flag := 'N';
517 END IF;
518 close yes_no_cur;
519 END IF;
520
521 l_party_access_code := p_msite_rec.party_access_code;
522 IF (((l_operation_type = 'UPDATE') AND
523 (l_party_access_code IS NOT NULL AND
524 l_party_access_code <> FND_API.G_MISS_char)) OR
525 l_operation_type = 'INSERT')
526 THEN
527 OPEN C_party_access_code(l_party_access_code );
528 FETCH C_party_access_code INTO l_exists;
529 IF C_party_access_code%NOTFOUND THEN
530 l_party_access_code := 'A';
531 END IF;
532 close C_party_access_code;
533 END IF;
534
535 IF l_operation_type = 'INSERT'
536 THEN
537 OPEN msite_id_seq;
538 FETCH msite_id_seq INTO l_msite_id;
539 CLOSE msite_id_seq;
540 END IF;
541
542 IF l_operation_type = 'INSERT'
543 THEN
544 INSERT INTO JTF_MSITES_B (
545 MSITE_ID,
546 OBJECT_VERSION_NUMBER,
547 LAST_UPDATE_DATE,
548 LAST_UPDATED_BY,
549 CREATION_DATE,
550 CREATED_BY,
551 LAST_UPDATE_LOGIN,
552 START_DATE_ACTIVE,
553 END_DATE_ACTIVE,
554 DEFAULT_DATE_FORMAT,
555 PROFILE_ID,
556 MASTER_MSITE_FLAG,
557 WALKIN_ALLOWED_FLAG,
558 STORE_ID,
559 ATP_CHECK_FLAG,
560 MSITE_ROOT_SECTION_ID,
561 --SECURITY_GROUP_ID // ??,
562 RESP_ACCESS_FLAG ,
563 PARTY_ACCESS_CODE ,
564 ACCESS_NAME,
565 URL,
566 THEME_ID )
567 VALUES (
568 l_msite_id,
569 1,
570 SYSDATE,
571 FND_GLOBAL.user_id,
572 SYSDATE,
573 FND_GLOBAL.user_id,
574 FND_GLOBAL.user_id,
575 p_msite_rec.start_date_active,
576 p_msite_rec.end_date_active,
577 p_msite_rec.date_format,
578 p_msite_rec.profile_id,
579 l_msite_master_flag,
580 walkin_allowed_code,
581 l_store_id,
582 l_atp_check_flag,
583 l_root_section_id,
584 --p_msite_rec.security_group_id,
585 l_resp_access_flag ,
586 l_party_access_code ,
587 p_msite_rec.access_name ,
588 DECODE(p_msite_rec.url,FND_API.G_MISS_CHAR,null,p_msite_rec.url) ,
589 DECODE(p_msite_rec.theme_id,FND_API.G_MISS_NUM,null,
590 p_msite_rec.theme_id));
591 --- Insert into the TL table
592 insert into JTF_MSITES_TL (
593 MSITE_ID,
594 LAST_UPDATE_DATE,
595 LAST_UPDATED_BY,
596 CREATION_DATE,
597 CREATED_BY,
598 LAST_UPDATE_LOGIN,
599 OBJECT_VERSION_NUMBER,
600 MSITE_NAME,
601 MSITE_DESCRIPTION,
602 LANGUAGE,
603 SOURCE_LANG ) select
604 l_msite_id,
605 sysdate,
606 FND_GLOBAL.user_id,
607 sysdate,
608 FND_GLOBAL.user_id,
609 FND_GLOBAL.user_id,
610 1,
611 p_msite_rec.Display_name,
612 p_msite_rec.description,
613 L.LANGUAGE_CODE,
614 userenv('LANG')
615 from FND_LANGUAGES L
616 where L.INSTALLED_FLAG in ('I', 'B')
617 and not exists(
618 select NULL
619 from JTF_MSITES_TL T
620 where T.MSITE_ID =l_msite_id
621 and T.LANGUAGE = L.LANGUAGE_CODE);
622
623 p_msite_rec.msite_id := l_msite_id;
624 p_msite_rec.object_version_number := 1;
625
626 ELSIF l_operation_type = 'UPDATE'
627 THEN
628 -- added the following code for globalisation -- ssridhar
629 -- RESP_ACCESS_FLAG = l_resp_access_flag ,
630 -- PARTY_ACCESS_CODE = l_party_access_code ,
631 -- ACCESS_NAME = p_msite_rec.access_name
632 --Bug fix for not updating end_date_active
633
634 IF l_resp_access_flag = fnd_api.g_miss_char
635 THEN
636 l_resp_access_flag := NULL;
637 END IF;
638
639 IF l_party_access_code = fnd_api.g_miss_char
640 THEN
641 l_party_access_code := NULL;
642 END IF;
643
644 IF p_msite_rec.access_name = fnd_api.g_miss_char
645 THEN
646 p_msite_rec.access_name := NULL;
647 END IF;
648
649 IF p_msite_rec.start_date_active = fnd_api.g_miss_date
650 THEN
651 p_msite_rec.start_date_active := NULL;
652 END IF;
653
654 IF p_msite_rec.end_date_active = fnd_api.g_miss_date
655 THEN
656 p_msite_rec.end_date_active := NULL;
657 END IF;
658
659 IF p_msite_rec.url = fnd_api.g_miss_char
660 THEN
661 p_msite_rec.url := NULL;
662 END IF;
663
664 IF p_msite_rec.theme_id = fnd_api.g_miss_num
665 THEN
666 p_msite_rec.theme_id := NULL;
667 END IF;
668
669 UPDATE JTF_MSITES_B SET
670 LAST_UPDATE_DATE = SYSDATE,
671 LAST_UPDATED_BY = FND_GLOBAL.user_id,
672 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
673 PROFILE_ID = p_msite_rec.profile_id,
674 DEFAULT_DATE_FORMAT = p_msite_rec.date_format ,
675 MASTER_MSITE_FLAG = l_msite_master_flag,
676 WALKIN_ALLOWED_FLAG = walkin_allowed_code,
677 STORE_ID = l_store_id ,
678 ATP_CHECK_FLAG = l_atp_check_flag,
679 MSITE_ROOT_SECTION_ID = l_root_section_id ,
680 OBJECT_VERSION_NUMBER = p_msite_rec.object_version_number + 1,
681 RESP_ACCESS_FLAG = NVL(l_resp_access_flag,resp_access_flag),
682 PARTY_ACCESS_CODE = nvl(l_party_access_code,party_access_code),
683 ACCESS_NAME = p_msite_rec.access_name,
684 START_DATE_ACTIVE =
685 nvl(p_msite_rec.start_date_active,start_date_active),
686 END_DATE_ACTIVE = p_msite_rec.end_date_active ,
687 URL = NVL(p_msite_rec.url,url),
688 THEME_ID = NVL(p_msite_rec.theme_id,theme_id)
689 WHERE
690 MSITE_ID = p_msite_rec.msite_id and
691 OBJECT_VERSION_NUMBER = p_msite_rec.object_version_number ;
692
693 UPDATE JTF_MSITES_TL SET
694 MSITE_NAME = decode( p_msite_rec.Display_name, FND_API.G_MISS_CHAR,
695 MSITE_NAME, p_msite_rec.Display_name),
696 MSITE_DESCRIPTION = decode( p_msite_rec.description,
697 FND_API.G_MISS_CHAR, MSITE_DESCRIPTION, p_msite_rec.description),
698 LAST_UPDATE_DATE = SYSDATE,
699 LAST_UPDATED_BY = FND_GLOBAL.user_id,
700 LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
701 OBJECT_VERSION_NUMBER= p_msite_rec.object_version_number +1 ,
702 SOURCE_LANG = userenv('LANG')
703 where msite_id = p_msite_rec.msite_id
704 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
705
706 p_msite_rec.object_version_number :=
707 p_msite_rec.object_version_number + 1;
708
709 END IF;
710
711 ----if p_msite_rec.enable_for_store = FND_API.g_true and
712 -- if l_root_section_flag = FND_API.g_true
713 -- then
714 -- IBE_DSP_HIERARCHY_SETUP_PVT.Associate_Root_Sct_To_MSite
715 -- (
716 -- p_api_version => p_api_version,
717 -- p_init_msg_list => FND_API.g_false,
718 -- p_commit => FND_API.g_false,
719 -- p_validation_level => 100,
720 -- p_section_id => p_msite_rec.msite_root_section_id,
721 -- p_mini_site_id => p_msite_rec.msite_id,
722 -- x_return_status => l_return_status,
723 -- x_msg_count => l_msg_count,
724 -- x_msg_data => l_msg_data
725 -- );
726 --
727 -- if l_return_status = FND_API.G_RET_STS_SUCCESS then
728 -- update JTF_MSITES_B set msite_root_section_id = l_root_section_id ,
729 -- store_id = l_store_id
730 -- where msite_id=p_msite_rec.msite_id;
731 -- else
732 -- raise FND_API.g_exc_error;
733 -- end if;
734 -- end if;
735
736
737 --dbms_output.put_line('Operation is successful ' );
738 --- Check if the caller requested to commit ,
739 --- If p_commit set to true, commit the transaction
740 IF FND_API.to_boolean(p_commit) THEN
741 COMMIT;
742 END IF;
743
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745
746 FND_MSG_PUB.count_and_get(
747 p_encoded => FND_API.g_false,
748 p_count => x_msg_count,
749 p_data => x_msg_data
750 );
751
752 EXCEPTION
753
754 WHEN FND_API.g_exc_error THEN
755 ROLLBACK TO save_msite;
756 x_return_status := FND_API.g_ret_sts_error;
757 FND_MSG_PUB.count_and_get(
758 p_encoded => FND_API.g_false,
759 p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762
763 WHEN FND_API.g_exc_unexpected_error THEN
764 ROLLBACK TO save_msite;
765 x_return_status := FND_API.g_ret_sts_unexp_error ;
766 --dbms_output.put_line('unexpected error raised');
767 FND_MSG_PUB.count_and_get(
768 p_encoded => FND_API.g_false,
769 p_count => x_msg_count,
770 p_data => x_msg_data
771 );
772
773 WHEN master_msite_exists_exception THEN
774 ROLLBACK TO save_msite;
775 x_return_status := FND_API.g_ret_sts_error ;
776 FND_MESSAGE.set_name('JTF','JTF_MSITE_MASTER_EXISTS');
777 FND_MSG_PUB.ADD;
778 --dbms_output.put_line('master_msite error raised');
779 FND_MSG_PUB.count_and_get(
780 p_encoded => FND_API.g_false,
781 p_count => x_msg_count,
782 p_data => x_msg_data
783 );
784
785 WHEN store_not_exists_exception THEN
786 ROLLBACK TO save_msite;
787 x_return_status := FND_API.g_ret_sts_error ;
788 FND_MSG_PUB.count_and_get(
789 p_encoded => FND_API.g_false,
790 p_count => x_msg_count,
791 p_data => x_msg_data
792 );
793
794 WHEN msite_default_org_missing THEN
795 ROLLBACK TO save_msite;
796 x_return_status := FND_API.g_ret_sts_error;
797 FND_MESSAGE.set_name('JTF','JTF_MSITE_DEF_ORG_REQ');
798 FND_MESSAGE.set_token('ID', p_msite_rec.msite_id);
799 FND_MSG_PUB.ADD;
800 FND_MSG_PUB.count_and_get(
801 p_encoded => FND_API.g_false,
802 p_count => x_msg_count,
803 p_data => x_msg_data
804 );
805 WHEN msite_default_currency_missing THEN
806 ROLLBACK TO save_msite;
807 x_return_status := FND_API.g_ret_sts_error;
808 FND_MESSAGE.set_name('JTF','JTF_MSITE_DEF_CURR_REQ');
809 FND_MESSAGE.set_token('ID', p_msite_rec.msite_id);
810 FND_MSG_PUB.ADD;
811 FND_MSG_PUB.count_and_get(
812 p_encoded => FND_API.g_false,
813 p_count => x_msg_count,
814 p_data => x_msg_data
815 );
816
817 WHEN msite_default_lang_missing THEN
818 ROLLBACK TO save_msite;
819 x_return_status := FND_API.g_ret_sts_error;
820 FND_MESSAGE.set_name('JTF','JTF_MSITE_DEF_LANG_REQ');
821 FND_MESSAGE.set_token('ID', p_msite_rec.msite_id);
822 FND_MSG_PUB.ADD;
823 FND_MSG_PUB.count_and_get(
824 p_encoded => FND_API.g_false,
825 p_count => x_msg_count,
826 p_data => x_msg_data
827 );
828
829 WHEN OTHERS THEN
830 ROLLBACK TO save_msite;
831 x_return_status := FND_API.g_ret_sts_unexp_error ;
832 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
833 THEN
834 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
835 END IF;
836 FND_MSG_PUB.count_and_get(
837 p_encoded => FND_API.g_false,
838 p_count => x_msg_count,
839 p_data => x_msg_data
840 );
841
842 END save_msite;
843
844
845 PROCEDURE save_msite_languages(
846 p_api_version IN NUMBER,
847 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
848 p_commit IN VARCHAR2 := FND_API.g_false,
849 x_return_status OUT VARCHAR2,
850 x_msg_count OUT NUMBER,
851 x_msg_data OUT VARCHAR2,
852 p_msite_id IN NUMBER,
853 p_msite_languages_tbl IN MSITE_LANGUAGES_TBL_TYPE
854 )
855 IS
856 l_api_name CONSTANT VARCHAR2(30) := 'save_msite_languages';
857 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
858 l_msite_id NUMBER;
859 l_exists NUMBER;
860 default_index NUMBER := 0;
861
862 CURSOR msite_languages_id_seq IS
863 SELECT jtf_msite_languages_s1.NEXTVAL
864 FROM DUAL;
865
866 l_msite_languages_id NUMBER;
867 l_insert_row NUMBER := 0;
868
869 BEGIN
870
871 --------------------- initialize -----------------------+
872 SAVEPOINT save_msite_languages;
873
874 IF NOT FND_API.compatible_api_call(
875 g_api_version,
876 p_api_version,
877 l_api_name,
878 g_pkg_name
879 ) THEN
880 RAISE FND_API.g_exc_unexpected_error;
881 END IF;
882
883
884 IF FND_API.to_boolean(p_init_msg_list) THEN
885 FND_MSG_PUB.initialize;
886 END IF;
887
888
889 x_return_status := FND_API.G_RET_STS_SUCCESS;
890
891 --- Check if the msite_id exists
892 IF p_msite_id IS NOT NULL and p_msite_id <> FND_API.g_miss_num
893 THEN
894
895 if jtf_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
896 raise FND_API.g_exc_error;
897 end if;
898
899 --dbms_output.put_line('Minisite id is passed ' );
900 ---- Delete all the entries for the mini-site
901
902 if p_msite_languages_tbl.count > 0
903 then
904
905 DELETE FROM JTF_MSITE_LANGUAGES where
906 msite_id = p_msite_id;
907
908 --- Insert all the rows for the minisite
909
910
911 --dbms_output.put_line('Default language id is passed deleted' );
912
913 for l_index in 1..p_msite_languages_tbl.count
914 LOOP
915 BEGIN
916 savepoint save_msite_language;
917
918 if valid_language(p_msite_languages_tbl(l_index).language_code) =
919 false THEN
920 raise FND_API.g_exc_error;
921 end if;
922
923 OPEN msite_languages_id_seq;
924 FETCH msite_languages_id_seq INTO l_msite_languages_id;
925 CLOSE msite_languages_id_seq;
926
927 INSERT INTO JTF_MSITE_LANGUAGES (
928 MSITE_LANGUAGE_ID,
929 OBJECT_VERSION_NUMBER,
930 LAST_UPDATE_DATE,
931 LAST_UPDATED_BY,
932 CREATION_DATE,
933 CREATED_BY,
934 LAST_UPDATE_LOGIN,
935 MSITE_ID,
936 LANGUAGE_CODE)
937 VALUES (
938 l_msite_languages_id,
939 1,
940 SYSDATE,
941 FND_GLOBAL.user_id,
942 SYSDATE,
943 FND_GLOBAL.user_id,
944 FND_GLOBAL.user_id,
945 p_msite_id,
946 p_msite_languages_tbl(l_index).language_code);
947
948 --dbms_output.put_line('inserted language passed ' );
949 l_insert_row := l_insert_row + 1;
950 -- Check if this language is default
951 if p_msite_languages_tbl(l_index).default_flag = FND_API.g_true
952 and default_index = 0 then
953 default_index := l_index;
954 end if;
955
956 EXCEPTION
957 WHEN OTHERS THEN
958 ROLLBACK TO save_msite_language;
959 x_return_status := FND_API.g_ret_sts_error;
960 END;
961
962 END LOOP;
963 /* else if msite_enabled_for_store(p_msite_id) = true then
964 raise msite_languages_missing;
965 end if;
966 */
967 END IF;
968
969 If default_index > 0 then
970 --dbms_output.put_line('default is not null');
971 update JTF_MSITES_B SET
972 DEFAULT_LANGUAGE_CODE =
973 p_msite_languages_tbl(default_index).language_code
974 WHERE MSITE_ID = p_msite_id;
975 else
976 --dbms_output.put_line('default is null');
977 raise msite_default_lang_missing;
978 end if;
979
980 jtf_physicalmap_grp.delete_msite_language(p_msite_id);
981
982 --- Check if the caller requested to commit ,
983 --- If p_commit set to true, commit the transaction
984 if l_insert_row > 0 then
985 IF FND_API.to_boolean(p_commit) THEN
986 COMMIT;
987 END IF;
988 else
989 raise FND_API.g_exc_error;
990 end if;
991 else
992 raise jtf_dspmgrvalidation_grp.msite_req_exception;
993 end if;
994 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
995 p_count => x_msg_count,
996 p_data => x_msg_data
997 );
998 EXCEPTION
999 WHEN FND_API.g_exc_error THEN
1000 ROLLBACK TO save_msite_languages;
1001 x_return_status := FND_API.g_ret_sts_error;
1002 FND_MSG_PUB.count_and_get(
1003 p_encoded => FND_API.g_false,
1004 p_count => x_msg_count,
1005 p_data => x_msg_data
1006 );
1007 WHEN jtf_dspmgrvalidation_grp.msite_req_exception THEN
1008 ROLLBACK TO save_msite_languages;
1009 x_return_status := FND_API.g_ret_sts_error;
1010 FND_MESSAGE.set_name('JTF','JTF_MSITE_REQ');
1011 FND_MSG_PUB.ADD;
1012 FND_MSG_PUB.count_and_get(
1013 p_encoded => FND_API.g_false,
1014 p_count => x_msg_count,
1015 p_data => x_msg_data
1016 );
1017 WHEN msite_default_lang_missing THEN
1018 ROLLBACK TO save_msite_languages;
1019 x_return_status := FND_API.g_ret_sts_error;
1020 FND_MESSAGE.set_name('JTF','JTF_MSITE_DEF_LANG_REQ');
1021 FND_MESSAGE.set_token('ID', p_msite_id);
1022 FND_MSG_PUB.ADD;
1023 FND_MSG_PUB.count_and_get(
1024 p_encoded => FND_API.g_false,
1025 p_count => x_msg_count,
1026 p_data => x_msg_data
1027 );
1028 WHEN FND_API.g_exc_unexpected_error THEN
1029 ROLLBACK TO save_msite_languages;
1030 x_return_status := FND_API.g_ret_sts_unexp_error ;
1031 FND_MSG_PUB.count_and_get(
1032 p_encoded => FND_API.g_false,
1033 p_count => x_msg_count,
1034 p_data => x_msg_data );
1035 WHEN OTHERS THEN
1036 ROLLBACK TO save_msite_languages;
1037 x_return_status := FND_API.g_ret_sts_unexp_error ;
1038 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1039 THEN
1040 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1041 END IF;
1042 FND_MSG_PUB.count_and_get(
1043 p_encoded => FND_API.g_false,
1044 p_count => x_msg_count,
1045 p_data => x_msg_data );
1046
1047 END save_msite_languages;
1048
1049 PROCEDURE save_msite_currencies(
1050 p_api_version IN NUMBER,
1051 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1052 p_commit IN VARCHAR2 := FND_API.g_false,
1053 x_return_status OUT VARCHAR2,
1054 x_msg_count OUT NUMBER,
1055 x_msg_data OUT VARCHAR2,
1056 p_msite_id IN NUMBER,
1057 p_msite_currencies_tbl IN MSITE_CURRENCIES_TBL_TYPE
1058 )
1059 IS
1060 l_api_name CONSTANT VARCHAR2(30) := 'save_msite_currencies';
1061 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1062 l_msite_id NUMBER;
1063 l_exists NUMBER;
1064 CURSOR msite_currencies_id_seq IS
1065 SELECT jtf_msite_currencies_s1.NEXTVAL
1066 FROM DUAL;
1067 l_msite_currencies_id NUMBER;
1068 l_insert_row NUMBER := 0;
1069 default_index NUMBER := 0;
1070 BEGIN
1071 --------------------- initialize -----------------------+
1072 SAVEPOINT save_msite_currencies;
1073 IF NOT FND_API.compatible_api_call(
1074 g_api_version,
1075 p_api_version,
1076 l_api_name,
1077 g_pkg_name ) THEN
1078 RAISE FND_API.g_exc_unexpected_error;
1079 END IF;
1080
1081 IF FND_API.to_boolean(p_init_msg_list) THEN
1082 FND_MSG_PUB.initialize;
1083 END IF;
1084 x_return_status := FND_API.G_RET_STS_SUCCESS;
1085 --- Check if the msite_id exists
1086 IF p_msite_id IS NOT NULL and p_msite_id <> FND_API.g_miss_num
1087 THEN
1088 --dbms_output.put_line('Minisite id is passed ' );
1089 if jtf_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
1090 raise FND_API.g_exc_error;
1091 end if;
1092 if (p_msite_currencies_tbl.count > 0 ) then
1093 ---- Delete all the entries for the mini-site
1094 DELETE FROM JTF_MSITE_CURRENCIES where
1095 msite_id = p_msite_id;
1096
1097 --- Insert all the rows for the minisite
1098 for l_index in 1..p_msite_currencies_tbl.count
1099 LOOP
1100 BEGIN
1101 savepoint save_msite_currency;
1102 if valid_currency(p_msite_currencies_tbl(l_index).currency_code)
1103 = false THEN
1104 raise FND_API.g_exc_error;
1105 end if;
1106 if valid_prc_lstids (
1107 p_msite_currencies_tbl(l_index).currency_code,
1108 p_msite_currencies_tbl(l_index).walkin_prc_lst_id,
1109 p_msite_currencies_tbl(l_index).registered_prc_lst_id,
1110 p_msite_currencies_tbl(l_index).biz_partner_prc_lst_id)
1111 = false then
1112 --dbms_output.put_line('invliad prc list id test');
1113 raise FND_API.g_exc_error;
1114 end if;
1115
1116 OPEN msite_currencies_id_seq;
1117 FETCH msite_currencies_id_seq INTO l_msite_currencies_id;
1118 CLOSE msite_currencies_id_seq;
1119 INSERT INTO JTF_MSITE_CURRENCIES (
1120 MSITE_CURRENCY_ID,
1121 OBJECT_VERSION_NUMBER,
1122 LAST_UPDATE_DATE,
1123 LAST_UPDATED_BY,
1124 CREATION_DATE,
1125 CREATED_BY,
1126 LAST_UPDATE_LOGIN,
1127 MSITE_ID,
1128 CURRENCY_CODE,
1129 WALKIN_PRC_LISTID,
1130 REGISTERED_PRC_LISTID,
1131 BIZPARTNER_PRC_LISTID,
1132 ORDERABLE_limit )
1133 VALUES (
1134 l_msite_currencies_id,
1135 1,
1136 SYSDATE,
1137 FND_GLOBAL.user_id,
1138 SYSDATE,
1139 FND_GLOBAL.user_id,
1140 FND_GLOBAL.user_id,
1141 p_msite_id,
1142 p_msite_currencies_tbl(l_index).currency_code,
1143 p_msite_currencies_tbl(l_index).walkin_prc_lst_id,
1144 p_msite_currencies_tbl(l_index).registered_prc_lst_id,
1145 p_msite_currencies_tbl(l_index).biz_partner_prc_lst_id,
1146 p_msite_currencies_tbl(l_index).orderable_limit);
1147 --dbms_output.put_line('inserted into currency');
1148 l_insert_row := l_insert_row + 1;
1149 --dbms_output.put_line('inserted into currency' || l_insert_row);
1150 if p_msite_currencies_tbl(l_index).default_flag = FND_API.g_true
1151 and default_index = 0 then
1152 default_index := l_index;
1153 end if;
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 ROLLBACK TO save_msite_currency;
1157 x_return_status := FND_API.g_ret_sts_error;
1158 END;
1159 END LOOP;
1160 /* else if msite_enabled_for_store(p_msite_id) = true then
1161 raise msite_currencies_missing;
1162 end if;
1163 */
1164 END IF;
1165 If default_index > 0 then
1166 update JTF_MSITES_B SET
1167 DEFAULT_CURRENCY_CODE =
1168 p_msite_currencies_tbl(default_index).currency_code
1169 WHERE MSITE_ID = p_msite_id;
1170 --dbms_output.put_line('set default currency');
1171 else
1172 --dbms_output.put_line('default is null');
1173 raise msite_default_currency_missing;
1174 end if;
1175 --- Check if the caller requested to commit ,
1176 --- If p_commit set to true, commit the transaction
1177 if l_insert_row > 0 then
1178 IF FND_API.to_boolean(p_commit) THEN
1179 COMMIT;
1180 END IF;
1181 else
1182 --dbms_output.put_line('raising an error' || l_insert_row);
1183 raise FND_API.g_exc_error;
1184 end if;
1185 else
1186 raise jtf_dspmgrvalidation_grp.msite_req_exception;
1187 end if;
1188 FND_MSG_PUB.count_and_get(
1189 p_encoded => FND_API.g_false,
1190 p_count => x_msg_count,
1191 p_data => x_msg_data
1192 );
1193
1194 EXCEPTION
1195 WHEN FND_API.g_exc_error THEN
1196 ROLLBACK TO save_msite_currencies;
1197 x_return_status := FND_API.g_ret_sts_error;
1198 FND_MSG_PUB.count_and_get(
1199 p_encoded => FND_API.g_false,
1200 p_count => x_msg_count,
1201 p_data => x_msg_data
1202 );
1203 WHEN jtf_dspmgrvalidation_grp.msite_req_exception THEN
1204 ROLLBACK TO save_msite_currencies;
1205 x_return_status := FND_API.g_ret_sts_error;
1206 FND_MESSAGE.set_name('JTF','JTF_MSITE_REQ');
1207 FND_MSG_PUB.ADD;
1208 FND_MSG_PUB.count_and_get(
1209 p_encoded => FND_API.g_false,
1210 p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213 WHEN msite_default_currency_missing THEN
1214 ROLLBACK TO save_msite_currencies;
1215 x_return_status := FND_API.g_ret_sts_error;
1216 FND_MESSAGE.set_name('JTF','JTF_MSITE_DEF_CURR_REQ');
1217 FND_MESSAGE.set_token('ID', p_msite_id);
1218 FND_MSG_PUB.ADD;
1219 FND_MSG_PUB.count_and_get(
1220 p_encoded => FND_API.g_false,
1221 p_count => x_msg_count,
1222 p_data => x_msg_data
1223 );
1224 WHEN FND_API.g_exc_unexpected_error THEN
1225 ROLLBACK TO save_msite_currencies;
1226 x_return_status := FND_API.g_ret_sts_unexp_error ;
1227 FND_MSG_PUB.count_and_get(
1228 p_encoded => FND_API.g_false,
1229 p_count => x_msg_count,
1230 p_data => x_msg_data
1231 );
1232 WHEN OTHERS THEN
1233 ROLLBACK TO save_msite_currencies;
1234 x_return_status := FND_API.g_ret_sts_unexp_error ;
1235 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1236 THEN
1237 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1238 END IF;
1239 FND_MSG_PUB.count_and_get(
1240 p_encoded => FND_API.g_false,
1241 p_count => x_msg_count,
1242 p_data => x_msg_data
1243 );
1244
1245 END save_msite_currencies;
1246
1247
1248
1249 PROCEDURE save_msite_orgids(
1250 p_api_version IN NUMBER,
1251 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1252 p_commit IN VARCHAR2 := FND_API.g_false,
1253 x_return_status OUT VARCHAR2,
1254 x_msg_count OUT NUMBER,
1255 x_msg_data OUT VARCHAR2,
1256 p_msite_id IN NUMBER,
1257 p_msite_orgids_tbl IN MSITE_ORGIDS_TBL_TYPE
1258 )
1259 IS
1260 l_api_name CONSTANT VARCHAR2(30) := 'save_msite_orgids';
1261 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1262 l_msite_id NUMBER;
1263 l_exists NUMBER;
1264 l_insert_row NUMBER := 0;
1265
1266 CURSOR msite_oprorg_id_seq IS
1267 SELECT jtf_msite_orgs_s1.NEXTVAL
1268 FROM DUAL;
1269
1270 l_msite_org_id NUMBER;
1271 default_index NUMBER := 0;
1272
1273 BEGIN
1274
1275 --------------------- initialize -----------------------+
1276 SAVEPOINT save_msite_orgids;
1277
1278 IF NOT FND_API.compatible_api_call(
1279 g_api_version,
1280 p_api_version,
1281 l_api_name,
1282 g_pkg_name
1283 ) THEN
1284 RAISE FND_API.g_exc_unexpected_error;
1285 END IF;
1286
1287
1288 IF FND_API.to_boolean(p_init_msg_list) THEN
1289 FND_MSG_PUB.initialize;
1290 END IF;
1291
1292
1293 x_return_status := FND_API.G_RET_STS_SUCCESS;
1294
1295 --- Check if the msite_id exists
1296 IF p_msite_id IS NOT NULL and p_msite_id <> FND_API.g_miss_num
1297 THEN
1298 --dbms_output.put_line('Minisite id is passed ' );
1299
1300 if jtf_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
1301 raise FND_API.g_exc_error;
1302 end if;
1303
1304 if (p_msite_orgids_tbl.count > 0 ) then
1305 ---- Delete all the entries for the mini-site
1306 DELETE FROM JTF_MSITE_ORGS where
1307 msite_id = p_msite_id;
1308
1309 --- Insert all the rows for the minisite
1310
1311
1312 --dbms_output.put_line('passed defualt orgid test');
1313 for l_index in 1..p_msite_orgids_tbl.count
1314 LOOP
1315 BEGIN
1316 savepoint save_msite_orgid;
1317
1318 if valid_orgid(p_msite_orgids_tbl(l_index).orgid) = false THEN
1319 raise FND_API.g_exc_error;
1320 end if;
1321 OPEN msite_oprorg_id_seq;
1322 FETCH msite_oprorg_id_seq INTO l_msite_org_id;
1323 CLOSE msite_oprorg_id_seq;
1324
1325 INSERT INTO JTF_MSITE_ORGS (
1326 MSITE_ORG_ID,
1327 OBJECT_VERSION_NUMBER,
1328 LAST_UPDATE_DATE,
1329 LAST_UPDATED_BY,
1330 CREATION_DATE,
1331 CREATED_BY,
1332 LAST_UPDATE_LOGIN,
1333 MSITE_ID,
1334 ORG_ID
1335 )
1336 VALUES (
1337 l_msite_org_id,
1338 1,
1339 SYSDATE,
1340 FND_GLOBAL.user_id,
1341 SYSDATE,
1342 FND_GLOBAL.user_id,
1343 FND_GLOBAL.user_id,
1344 p_msite_id,
1345 p_msite_orgids_tbl(l_index).orgid);
1346 --dbms_output.put_line('inserted into opr org');
1347 l_insert_row := l_insert_row + 1;
1348
1349 if p_msite_orgids_tbl(l_index).default_flag = FND_API.g_true
1350 and default_index = 0 then
1351 default_index := l_index;
1352 end if;
1353
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 ROLLBACK TO save_msite_orgid;
1357 x_return_status := FND_API.g_ret_sts_error;
1358 END;
1359
1360 END LOOP;
1361 else
1362 if msite_enabled_for_store(p_msite_id) = true then
1363 raise msite_orgs_missing;
1364 end if;
1365 END IF;
1366
1367 If default_index > 0 then
1368 update JTF_MSITES_B SET
1369 DEFAULT_ORG_ID = p_msite_orgids_tbl(default_index).orgid where
1370 MSITE_ID = p_msite_id;
1371 else
1372 raise msite_default_org_missing;
1373 end if;
1374
1375 --- Check if the caller requested to commit ,
1376 --- If p_commit set to true, commit the transaction
1377 if l_insert_row > 0 then
1378 IF FND_API.to_boolean(p_commit) THEN
1379 COMMIT;
1380 END IF;
1381 else
1382 raise FND_API.g_exc_error;
1383 end if;
1384
1385 else
1386 raise jtf_dspmgrvalidation_grp.msite_req_exception;
1387 end if;
1388
1389 FND_MSG_PUB.count_and_get(
1390 p_encoded => FND_API.g_false,
1391 p_count => x_msg_count,
1392 p_data => x_msg_data
1393 );
1394
1395 EXCEPTION
1396
1397 WHEN FND_API.g_exc_error THEN
1398 ROLLBACK TO save_msite_orgids;
1399 x_return_status := FND_API.g_ret_sts_error;
1400 FND_MSG_PUB.count_and_get(
1401 p_encoded => FND_API.g_false,
1402 p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405
1406 WHEN jtf_dspmgrvalidation_grp.msite_req_exception THEN
1407 ROLLBACK TO save_msite_orgids;
1408 x_return_status := FND_API.g_ret_sts_error;
1409 FND_MESSAGE.set_name('JTF','JTF_MSITE_REQ');
1410 FND_MSG_PUB.ADD;
1411 FND_MSG_PUB.count_and_get(
1412 p_encoded => FND_API.g_false,
1413 p_count => x_msg_count,
1414 p_data => x_msg_data
1415 );
1416 WHEN FND_API.g_exc_unexpected_error THEN
1417 ROLLBACK TO save_msite_orgids;
1418 x_return_status := FND_API.g_ret_sts_unexp_error ;
1419 FND_MSG_PUB.count_and_get(
1420 p_encoded => FND_API.g_false,
1421 p_count => x_msg_count,
1422 p_data => x_msg_data
1423 );
1424
1425 WHEN msite_default_org_missing THEN
1426 ROLLBACK TO save_msite_orgids;
1427 x_return_status := FND_API.g_ret_sts_error;
1428 FND_MESSAGE.set_name('JTF','JTF_MSITE_DEF_ORG_REQ');
1429 FND_MESSAGE.set_token('ID', p_msite_id);
1430 FND_MSG_PUB.ADD;
1431 FND_MSG_PUB.count_and_get(
1432 p_encoded => FND_API.g_false,
1433 p_count => x_msg_count,
1434 p_data => x_msg_data
1435 );
1436
1437 WHEN OTHERS THEN
1438 ROLLBACK TO save_msite_orgids;
1439 x_return_status := FND_API.g_ret_sts_unexp_error ;
1440 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1441 THEN
1442 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1443 END IF;
1444 FND_MSG_PUB.count_and_get(
1445 p_encoded => FND_API.g_false,
1446 p_count => x_msg_count,
1447 p_data => x_msg_data
1448 );
1449
1450 END save_msite_orgids;
1451
1452
1453 PROCEDURE delete_msite(
1454 p_api_version IN NUMBER,
1455 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1456 p_commit IN VARCHAR2 := FND_API.g_false,
1457 x_return_status OUT VARCHAR2,
1458 x_msg_count OUT NUMBER,
1459 x_msg_data OUT VARCHAR2,
1460 p_msite_id_tbl IN msite_delete_tbl_type
1461 )
1462 IS
1463
1464 l_api_name CONSTANT VARCHAR2(30) := 'delete_msite';
1465 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1466 l_msite_id NUMBER;
1467 l_exists NUMBER;
1468 l_insert_row NUMBER := 0;
1469 l_index NUMBER := 0;
1470
1471
1472 CURSOR c_msite_resp (p_msite_id Number) Is
1473 Select msite_resp_id
1474 From JTF_MSITE_RESPS_B
1475 Where msite_id = p_msite_id ;
1476 BEGIN
1477
1478 --------------------- initialize -----------------------+
1479 SAVEPOINT delete_msite;
1480
1481 IF NOT FND_API.compatible_api_call(
1482 g_api_version,
1483 p_api_version,
1484 l_api_name,
1485 g_pkg_name
1486 ) THEN
1487 RAISE FND_API.g_exc_unexpected_error;
1488 END IF;
1489
1490
1491 IF FND_API.to_boolean(p_init_msg_list) THEN
1492 FND_MSG_PUB.initialize;
1493 END IF;
1494
1495
1496 x_return_status := FND_API.G_RET_STS_SUCCESS;
1497
1498 --- Check if the msite_id exists
1499 for l_index in 1..p_msite_id_tbl.count
1500 LOOP
1501 BEGIN
1502 savepoint delete_msite_id;
1503 IF p_msite_id_tbl(l_index).msite_id IS NOT NULL and
1504 p_msite_id_tbl(l_index).msite_id <> FND_API.g_miss_num
1505 THEN
1506 --dbms_output.put_line('Minisite id is passed ' );
1507
1508 --- if jtf_dspmgrvalidation_grp.check_msite_exists(p_msite_id_tbl(l_index).msite_id) = false then
1509 --- raise FND_API.g_exc_error;
1510 --- end if;
1511
1512 jtf_physicalmap_grp.delete_msite(p_msite_id_tbl(l_index).msite_id);
1513
1514 delete from jtf_msite_languages where msite_id = p_msite_id_tbl(l_index).msite_id;
1515 delete from jtf_msite_currencies where msite_id = p_msite_id_tbl(l_index).msite_id;
1516 delete from jtf_msite_orgs where msite_id = p_msite_id_tbl(l_index).msite_id;
1517 delete from jtf_dsp_msite_sct_sects where mini_site_id=p_msite_id_tbl(l_index).msite_id;
1518 delete from jtf_dsp_msite_sct_items where mini_site_id=p_msite_id_tbl(l_index).msite_id;
1519 delete from jtf_msites_tl where msite_id = p_msite_id_tbl(l_index).msite_id;
1520 delete from jtf_msites_b where msite_id = p_msite_id_tbl(l_index).msite_id;
1521
1522 --added for deleting the rows from the newly added Merchant responsibility
1523 --table -- ssridhar
1524
1525 for rec_msite_resp in c_msite_resp (p_msite_id_tbl(l_index).msite_id )
1526 Loop
1527 Jtf_Msite_Resp_Pvt.Delete_Msite_Resp(
1528 p_api_version => 1.0 ,
1529 p_init_msg_list => FND_API.G_FALSE,
1530 p_commit => FND_API.G_FALSE,
1531 p_validation_level=>FND_API.G_VALID_LEVEL_FULL,
1532 p_msite_resp_id => rec_msite_resp.msite_resp_id ,
1533 -- p_msite_id => FND_API.G_MISS_NUM,
1534 --p_responsibility_id => FND_API.G_MISS_NUM,
1535 --p_application_id => FND_API.G_MISS_NUM,
1536 x_return_status => x_return_status ,
1537 x_msg_count => x_msg_count ,
1538 x_msg_data => x_msg_data );
1539
1540 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1541 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
1542 FND_MSG_PUB.Add;
1543 RAISE FND_API.G_EXC_ERROR;
1544 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1545 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
1546 FND_MSG_PUB.Add;
1547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1548 END IF;
1549 End Loop;
1550
1551 delete from jtf_msite_prty_accss
1552 where msite_id = p_msite_id_tbl(l_index).msite_id;
1553
1554 else
1555 raise jtf_dspmgrvalidation_grp.msite_req_exception;
1556 end if;
1557
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 ROLLBACK TO delete_msite_id;
1561 x_return_status := FND_API.g_ret_sts_error;
1562 END;
1563
1564 END LOOP;
1565
1566
1567
1568 IF FND_API.to_boolean(p_commit) THEN
1569 COMMIT;
1570 END IF;
1571
1572 x_return_status := FND_API.G_RET_STS_SUCCESS;
1573
1574 FND_MSG_PUB.count_and_get(
1575 p_encoded => FND_API.g_false,
1576 p_count => x_msg_count,
1577 p_data => x_msg_data
1578 );
1579
1580 EXCEPTION
1581 WHEN FND_API.g_exc_error THEN
1582 ROLLBACK TO delete_msite;
1583 x_return_status := FND_API.g_ret_sts_error;
1584 FND_MSG_PUB.count_and_get(
1585 p_encoded => FND_API.g_false,
1586 p_count => x_msg_count,
1587 p_data => x_msg_data
1588 );
1589
1590 WHEN jtf_dspmgrvalidation_grp.msite_req_exception THEN
1591 ROLLBACK TO delete_msite;
1592 x_return_status := FND_API.g_ret_sts_error;
1593 FND_MESSAGE.set_name('JTF','JTF_MSITE_REQ');
1594 FND_MSG_PUB.ADD;
1595 FND_MSG_PUB.count_and_get(
1596 p_encoded => FND_API.g_false,
1597 p_count => x_msg_count,
1598 p_data => x_msg_data
1599 );
1600
1601 WHEN FND_API.g_exc_unexpected_error THEN
1602 ROLLBACK TO delete_msite;
1603 x_return_status := FND_API.g_ret_sts_unexp_error ;
1604 FND_MSG_PUB.count_and_get(
1605 p_encoded => FND_API.g_false,
1606 p_count => x_msg_count,
1607 p_data => x_msg_data
1608 );
1609
1610 WHEN OTHERS THEN
1611 ROLLBACK TO delete_msite;
1612 x_return_status := FND_API.g_ret_sts_unexp_error ;
1613 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1614 THEN
1615 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1616 END IF;
1617 FND_MSG_PUB.count_and_get(
1618 p_encoded => FND_API.g_false,
1619 p_count => x_msg_count,
1620 p_data => x_msg_data
1621 );
1622
1623 end delete_msite;
1624
1625
1626 PROCEDURE get_msite_attribute (
1627 p_api_version IN NUMBER,
1628 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1629 p_commit IN VARCHAR2 := FND_API.g_false,
1630 x_return_status OUT VARCHAR2,
1631 x_msg_count OUT NUMBER,
1632 x_msg_data OUT VARCHAR2,
1633 p_msite_id IN NUMBER,
1634 p_msite_attribute_name IN VARCHAR2,
1635 x_msite_attribute_value OUT VARCHAR2)
1636 IS
1637
1638 l_api_name CONSTANT VARCHAR2(30) := 'get_msite_attribute';
1639 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1640
1641 BEGIN
1642
1643 --------------------- initialize -----------------------+
1644 SAVEPOINT get_msite_attribute;
1645
1646 IF NOT FND_API.compatible_api_call(
1647 g_api_version,
1648 p_api_version,
1649 l_api_name,
1650 g_pkg_name
1651 ) THEN
1652 RAISE FND_API.g_exc_unexpected_error;
1653 END IF;
1654
1655
1656 IF FND_API.to_boolean(p_init_msg_list) THEN
1657 FND_MSG_PUB.initialize;
1658 END IF;
1659
1660
1661 x_return_status := FND_API.G_RET_STS_SUCCESS;
1662
1663 IF p_msite_id IS NOT NULL AND p_msite_id <> FND_API.g_miss_num
1664 THEN
1665 --dbms_output.put_line('Minisite id is passed ' );
1666
1667 if jtf_dspmgrvalidation_grp.check_msite_exists(p_msite_id) = false then
1668 raise FND_API.g_exc_error;
1669 end if;
1670
1671 x_msite_attribute_value := FND_PROFILE.VALUE_SPECIFIC(p_msite_attribute_name,null,null,671);
1672 ELSE
1673 raise jtf_dspmgrvalidation_grp.msite_req_exception;
1674 END IF;
1675
1676 x_return_status := FND_API.G_RET_STS_SUCCESS;
1677
1678 FND_MSG_PUB.count_and_get(
1679 p_encoded => FND_API.g_false,
1680 p_count => x_msg_count,
1681 p_data => x_msg_data
1682 );
1683
1684 EXCEPTION
1685 WHEN FND_API.g_exc_error THEN
1686 ROLLBACK TO get_msite_attribute;
1687 x_return_status := FND_API.g_ret_sts_error;
1688 FND_MSG_PUB.count_and_get(
1689 p_encoded => FND_API.g_false,
1690 p_count => x_msg_count,
1691 p_data => x_msg_data
1692 );
1693
1694 WHEN jtf_dspmgrvalidation_grp.msite_req_exception THEN
1695 ROLLBACK TO get_msite_attribute;
1696 x_return_status := FND_API.g_ret_sts_error;
1697 FND_MESSAGE.set_name('JTF','JTF_MSITE_REQ');
1698 FND_MSG_PUB.ADD;
1699 FND_MSG_PUB.count_and_get(
1700 p_encoded => FND_API.g_false,
1701 p_count => x_msg_count,
1702 p_data => x_msg_data
1703 );
1704
1705 WHEN FND_API.g_exc_unexpected_error THEN
1706 ROLLBACK TO get_msite_attribute;
1707 x_return_status := FND_API.g_ret_sts_unexp_error ;
1708 FND_MSG_PUB.count_and_get(
1709 p_encoded => FND_API.g_false,
1710 p_count => x_msg_count,
1711 p_data => x_msg_data
1712 );
1713
1714 WHEN OTHERS THEN
1715 ROLLBACK TO get_msite_attribute;
1716 x_return_status := FND_API.g_ret_sts_unexp_error ;
1717 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1718 THEN
1719 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1720 END IF;
1721 FND_MSG_PUB.count_and_get(
1722 p_encoded => FND_API.g_false,
1723 p_count => x_msg_count,
1724 p_data => x_msg_data
1725 );
1726
1727 end get_msite_attribute;
1728
1729
1730 -- Modifying the procedure to insert 3 new fields for globalisation -- ssridhar
1731 -- RESP_ACCESS_FLAG
1732 -- PARTY_ACCESS_CODE
1733 -- ACCESS_NAME
1734
1735 procedure INSERT_ROW (
1736 X_ROWID in out VARCHAR2,
1737 X_MSITE_ID in NUMBER,
1738 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1739 X_ATTRIBUTE1 in VARCHAR2,
1740 X_ATTRIBUTE2 in VARCHAR2,
1741 X_ATTRIBUTE3 in VARCHAR2,
1742 X_ATTRIBUTE4 in VARCHAR2,
1743 X_ATTRIBUTE5 in VARCHAR2,
1744 X_ATTRIBUTE6 in VARCHAR2,
1745 X_ATTRIBUTE7 in VARCHAR2,
1746 X_ATTRIBUTE8 in VARCHAR2,
1747 X_ATTRIBUTE9 in VARCHAR2,
1748 X_ATTRIBUTE11 in VARCHAR2,
1749 X_ATTRIBUTE10 in VARCHAR2,
1750 X_ATTRIBUTE12 in VARCHAR2,
1751 X_ATTRIBUTE13 in VARCHAR2,
1752 X_ATTRIBUTE14 in VARCHAR2,
1753 X_ATTRIBUTE15 in VARCHAR2,
1754 X_SECURITY_GROUP_ID in NUMBER,
1755 X_OBJECT_VERSION_NUMBER in NUMBER,
1756 X_STORE_ID in NUMBER,
1757 X_START_DATE_ACTIVE in DATE,
1758 X_END_DATE_ACTIVE in DATE,
1759 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
1760 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
1761 X_DEFAULT_DATE_FORMAT in VARCHAR2,
1762 X_DEFAULT_ORG_ID in NUMBER,
1763 X_ATP_CHECK_FLAG in VARCHAR2,
1764 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
1765 X_MSITE_ROOT_SECTION_ID in NUMBER,
1766 X_PROFILE_ID in NUMBER,
1767 X_MASTER_MSITE_FLAG in VARCHAR2,
1768 X_MSITE_NAME in VARCHAR2,
1769 X_MSITE_DESCRIPTION in VARCHAR2,
1770 X_CREATION_DATE in DATE,
1771 X_CREATED_BY in NUMBER,
1772 X_LAST_UPDATE_DATE in DATE,
1773 X_LAST_UPDATED_BY in NUMBER,
1774 X_LAST_UPDATE_LOGIN in NUMBER,
1775 X_RESP_ACCESS_FLAG in VARCHAR2 ,
1776 X_PARTY_ACCESS_CODE in VARCHAR2 ,
1777 X_ACCESS_NAME in VARCHAR2 ,
1778 X_URL in VARCHAR2 ,
1779 X_THEME_ID in NUMBER)
1780 is
1781 cursor C is select ROWID from JTF_MSITES_B
1782 where MSITE_ID = X_MSITE_ID
1783 ;
1784 begin
1785 insert into JTF_MSITES_B (
1786 ATTRIBUTE_CATEGORY,
1787 ATTRIBUTE1,
1788 ATTRIBUTE2,
1789 ATTRIBUTE3,
1790 ATTRIBUTE4,
1791 ATTRIBUTE5,
1792 ATTRIBUTE6,
1793 ATTRIBUTE7,
1794 ATTRIBUTE8,
1795 ATTRIBUTE9,
1796 ATTRIBUTE11,
1797 ATTRIBUTE10,
1798 ATTRIBUTE12,
1799 ATTRIBUTE13,
1800 ATTRIBUTE14,
1801 ATTRIBUTE15,
1802 SECURITY_GROUP_ID,
1803 MSITE_ID,
1804 OBJECT_VERSION_NUMBER,
1805 STORE_ID,
1806 START_DATE_ACTIVE,
1807 END_DATE_ACTIVE,
1808 DEFAULT_LANGUAGE_CODE,
1809 DEFAULT_CURRENCY_CODE,
1810 DEFAULT_DATE_FORMAT,
1811 DEFAULT_ORG_ID,
1812 ATP_CHECK_FLAG,
1813 WALKIN_ALLOWED_FLAG,
1814 MSITE_ROOT_SECTION_ID,
1815 PROFILE_ID,
1816 MASTER_MSITE_FLAG,
1817 CREATION_DATE,
1818 CREATED_BY,
1819 LAST_UPDATE_DATE,
1820 LAST_UPDATED_BY,
1821 LAST_UPDATE_LOGIN ,
1822 RESP_ACCESS_FLAG ,
1823 PARTY_ACCESS_CODE ,
1824 ACCESS_NAME ,
1825 URL ,
1826 THEME_ID ) values (
1827 X_ATTRIBUTE_CATEGORY,
1828 X_ATTRIBUTE1,
1829 X_ATTRIBUTE2,
1830 X_ATTRIBUTE3,
1831 X_ATTRIBUTE4,
1832 X_ATTRIBUTE5,
1833 X_ATTRIBUTE6,
1834 X_ATTRIBUTE7,
1835 X_ATTRIBUTE8,
1836 X_ATTRIBUTE9,
1837 X_ATTRIBUTE11,
1838 X_ATTRIBUTE10,
1839 X_ATTRIBUTE12,
1840 X_ATTRIBUTE13,
1841 X_ATTRIBUTE14,
1842 X_ATTRIBUTE15,
1843 X_SECURITY_GROUP_ID,
1844 X_MSITE_ID,
1845 X_OBJECT_VERSION_NUMBER,
1846 X_STORE_ID,
1847 X_START_DATE_ACTIVE,
1848 X_END_DATE_ACTIVE,
1849 X_DEFAULT_LANGUAGE_CODE,
1850 X_DEFAULT_CURRENCY_CODE,
1851 X_DEFAULT_DATE_FORMAT,
1852 X_DEFAULT_ORG_ID,
1853 X_ATP_CHECK_FLAG,
1854 X_WALKIN_ALLOWED_FLAG,
1855 X_MSITE_ROOT_SECTION_ID,
1856 X_PROFILE_ID,
1857 X_MASTER_MSITE_FLAG,
1858 X_CREATION_DATE,
1859 X_CREATED_BY,
1860 X_LAST_UPDATE_DATE,
1861 X_LAST_UPDATED_BY,
1862 X_LAST_UPDATE_LOGIN ,
1863 X_RESP_ACCESS_FLAG ,
1864 X_PARTY_ACCESS_CODE ,
1865 X_ACCESS_NAME,
1866 X_URL,
1867 X_THEME_ID );
1868
1869 insert into JTF_MSITES_TL (
1870 SECURITY_GROUP_ID,
1871 MSITE_ID,
1872 OBJECT_VERSION_NUMBER,
1873 CREATED_BY,
1874 CREATION_DATE,
1875 LAST_UPDATED_BY,
1876 LAST_UPDATE_DATE,
1877 LAST_UPDATE_LOGIN,
1878 MSITE_NAME,
1879 MSITE_DESCRIPTION,
1880 LANGUAGE,
1881 SOURCE_LANG
1882 ) select
1883 X_SECURITY_GROUP_ID,
1884 X_MSITE_ID,
1885 X_OBJECT_VERSION_NUMBER,
1886 X_CREATED_BY,
1887 X_CREATION_DATE,
1888 X_LAST_UPDATED_BY,
1889 X_LAST_UPDATE_DATE,
1890 X_LAST_UPDATE_LOGIN,
1891 X_MSITE_NAME,
1892 X_MSITE_DESCRIPTION,
1893 L.LANGUAGE_CODE,
1894 userenv('LANG')
1895 from FND_LANGUAGES L
1896 where L.INSTALLED_FLAG in ('I', 'B')
1897 and not exists
1898 (select NULL
1899 from JTF_MSITES_TL T
1900 where T.MSITE_ID = X_MSITE_ID
1901 and T.LANGUAGE = L.LANGUAGE_CODE);
1902
1903 open c;
1904 fetch c into X_ROWID;
1905 if (c%notfound) then
1906 close c;
1907 raise no_data_found;
1908 end if;
1909 close c;
1910
1911 end INSERT_ROW;
1912
1913 -- Modifying the procedure to accept 3 new fields for globalisation -- ssridhar
1914 -- RESP_ACCESS_FLAG
1915 -- PARTY_ACCESS_CODE
1916 -- ACCESS_NAME
1917
1918 procedure LOCK_ROW (
1919 X_MSITE_ID in NUMBER,
1920 X_ATTRIBUTE_CATEGORY in VARCHAR2,
1921 X_ATTRIBUTE1 in VARCHAR2,
1922 X_ATTRIBUTE2 in VARCHAR2,
1923 X_ATTRIBUTE3 in VARCHAR2,
1924 X_ATTRIBUTE4 in VARCHAR2,
1925 X_ATTRIBUTE5 in VARCHAR2,
1926 X_ATTRIBUTE6 in VARCHAR2,
1927 X_ATTRIBUTE7 in VARCHAR2,
1928 X_ATTRIBUTE8 in VARCHAR2,
1929 X_ATTRIBUTE9 in VARCHAR2,
1930 X_ATTRIBUTE11 in VARCHAR2,
1931 X_ATTRIBUTE10 in VARCHAR2,
1932 X_ATTRIBUTE12 in VARCHAR2,
1933 X_ATTRIBUTE13 in VARCHAR2,
1934 X_ATTRIBUTE14 in VARCHAR2,
1935 X_ATTRIBUTE15 in VARCHAR2,
1936 X_SECURITY_GROUP_ID in NUMBER,
1937 X_OBJECT_VERSION_NUMBER in NUMBER,
1938 X_STORE_ID in NUMBER,
1939 X_START_DATE_ACTIVE in DATE,
1940 X_END_DATE_ACTIVE in DATE,
1941 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
1942 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
1943 X_DEFAULT_DATE_FORMAT in VARCHAR2,
1944 X_DEFAULT_ORG_ID in NUMBER,
1945 X_ATP_CHECK_FLAG in VARCHAR2,
1946 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
1947 X_MSITE_ROOT_SECTION_ID in NUMBER,
1948 X_PROFILE_ID in NUMBER,
1949 X_MASTER_MSITE_FLAG in VARCHAR2,
1950 X_MSITE_NAME in VARCHAR2,
1951 X_MSITE_DESCRIPTION in VARCHAR2 ,
1952 X_RESP_ACCESS_FLAG in VARCHAR2 ,
1953 X_PARTY_ACCESS_CODE in VARCHAR2 ,
1954 X_ACCESS_NAME in VARCHAR2 ,
1955 X_URL in VARCHAR2 ,
1956 X_THEME_ID in NUMBER )
1957 IS
1958 cursor c is select
1959 ATTRIBUTE_CATEGORY,
1960 ATTRIBUTE1,
1961 ATTRIBUTE2,
1962 ATTRIBUTE3,
1963 ATTRIBUTE4,
1964 ATTRIBUTE5,
1965 ATTRIBUTE6,
1966 ATTRIBUTE7,
1967 ATTRIBUTE8,
1968 ATTRIBUTE9,
1969 ATTRIBUTE11,
1970 ATTRIBUTE10,
1971 ATTRIBUTE12,
1972 ATTRIBUTE13,
1973 ATTRIBUTE14,
1974 ATTRIBUTE15,
1975 SECURITY_GROUP_ID,
1976 OBJECT_VERSION_NUMBER,
1977 STORE_ID,
1978 START_DATE_ACTIVE,
1979 END_DATE_ACTIVE,
1980 DEFAULT_LANGUAGE_CODE,
1981 DEFAULT_CURRENCY_CODE,
1982 DEFAULT_DATE_FORMAT,
1983 DEFAULT_ORG_ID,
1984 ATP_CHECK_FLAG,
1985 WALKIN_ALLOWED_FLAG,
1986 MSITE_ROOT_SECTION_ID,
1987 PROFILE_ID,
1988 MASTER_MSITE_FLAG ,
1989 RESP_ACCESS_FLAG ,
1990 PARTY_ACCESS_CODE ,
1991 ACCESS_NAME ,
1992 URL ,
1993 THEME_ID
1994 from JTF_MSITES_B
1995 where MSITE_ID = X_MSITE_ID
1996 for update of MSITE_ID nowait;
1997 recinfo c%rowtype;
1998
1999 cursor c1 is select
2000 MSITE_NAME,
2001 MSITE_DESCRIPTION,
2002 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
2003 from JTF_MSITES_TL
2004 where MSITE_ID = X_MSITE_ID
2005 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
2006 for update of MSITE_ID nowait;
2007 begin
2008 open c;
2009 fetch c into recinfo;
2010 if (c%notfound) then
2011 close c;
2012 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2013 app_exception.raise_exception;
2014 end if;
2015 close c;
2016 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
2017 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
2018 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
2019 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
2020 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
2021 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
2022 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
2023 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
2024 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
2025 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
2026 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
2027 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
2028 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
2029 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
2030 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
2031 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
2032 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
2033 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
2034 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
2035 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
2036 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
2037 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
2038 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
2039 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
2040 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
2041 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
2042 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
2043 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
2044 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
2045 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
2046 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
2047 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
2048 AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
2049 OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
2050 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
2051 AND ((recinfo.STORE_ID = X_STORE_ID)
2052 OR ((recinfo.STORE_ID is null) AND (X_STORE_ID is null)))
2053 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
2054 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
2055 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
2056 AND ((recinfo.DEFAULT_LANGUAGE_CODE = X_DEFAULT_LANGUAGE_CODE)
2057 OR ((recinfo.DEFAULT_LANGUAGE_CODE is null) AND (X_DEFAULT_LANGUAGE_CODE is null)))
2058 AND ((recinfo.DEFAULT_CURRENCY_CODE = X_DEFAULT_CURRENCY_CODE)
2059 OR ((recinfo.DEFAULT_CURRENCY_CODE is null) AND (X_DEFAULT_CURRENCY_CODE is null)))
2060 AND ((recinfo.DEFAULT_DATE_FORMAT = X_DEFAULT_DATE_FORMAT)
2061 OR ((recinfo.DEFAULT_DATE_FORMAT is null) AND (X_DEFAULT_DATE_FORMAT is null)))
2062 AND ((recinfo.DEFAULT_ORG_ID = X_DEFAULT_ORG_ID)
2063 OR ((recinfo.DEFAULT_ORG_ID is null) AND (X_DEFAULT_ORG_ID is null)))
2064 AND ((recinfo.ATP_CHECK_FLAG = X_ATP_CHECK_FLAG)
2065 OR ((recinfo.ATP_CHECK_FLAG is null) AND (X_ATP_CHECK_FLAG is null)))
2066 AND ((recinfo.WALKIN_ALLOWED_FLAG = X_WALKIN_ALLOWED_FLAG)
2067 OR ((recinfo.WALKIN_ALLOWED_FLAG is null) AND (X_WALKIN_ALLOWED_FLAG is null)))
2068 AND ((recinfo.MSITE_ROOT_SECTION_ID = X_MSITE_ROOT_SECTION_ID)
2069 OR ((recinfo.MSITE_ROOT_SECTION_ID is null) AND (X_MSITE_ROOT_SECTION_ID is null)))
2070 AND ((recinfo.PROFILE_ID = X_PROFILE_ID)
2071 OR ((recinfo.PROFILE_ID is null) AND (X_PROFILE_ID is null)))
2072 AND ((recinfo.MASTER_MSITE_FLAG = X_MASTER_MSITE_FLAG)
2073 OR ((recinfo.MASTER_MSITE_FLAG is null) AND (X_MASTER_MSITE_FLAG is null)))
2074 AND ((recinfo.RESP_ACCESS_FLAG = X_RESP_ACCESS_FLAG )
2075 OR ((recinfo.RESP_ACCESS_FLAG is null) AND (X_RESP_ACCESS_FLAG is null)))
2076 AND ((recinfo.PARTY_ACCESS_CODE = X_PARTY_ACCESS_CODE )
2077 OR ((recinfo.PARTY_ACCESS_CODE is null) AND ( X_PARTY_ACCESS_CODE is null)))
2078 AND ((recinfo.ACCESS_NAME = X_ACCESS_NAME )
2079 OR ((recinfo.ACCESS_NAME is null) AND ( X_ACCESS_NAME is null)))
2080 AND ((recinfo.URL = X_URL )
2081 OR ((recinfo.URL is null) AND ( X_URL is null)))
2082 AND ((recinfo.THEME_ID = X_THEME_ID )
2083 OR ((recinfo.THEME_ID is null) AND ( X_THEME_ID is null)))
2084 ) then
2085 null;
2086 else
2087 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2088 app_exception.raise_exception;
2089 end if;
2090
2091 for tlinfo in c1 loop
2092 if (tlinfo.BASELANG = 'Y') then
2093 if ( ((tlinfo.MSITE_NAME = X_MSITE_NAME)
2094 OR ((tlinfo.MSITE_NAME is null) AND (X_MSITE_NAME is null)))
2095 AND ((tlinfo.MSITE_DESCRIPTION = X_MSITE_DESCRIPTION)
2096 OR ((tlinfo.MSITE_DESCRIPTION is null) AND (X_MSITE_DESCRIPTION is null)))
2097 ) then
2098 null;
2099 else
2100 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2101 app_exception.raise_exception;
2102 end if;
2103 end if;
2104 end loop;
2105 return;
2106 end LOCK_ROW;
2107
2108 -- UPDATE_ROW procedure is not being called for updating rows in this package
2109 -- Modifying the procedure to updating 3 new fields for globalisation
2110 -- ssridhar
2111 -- RESP_ACCESS_FLAG
2112 -- PARTY_ACCESS_CODE
2113 -- ACCESS_NAME
2114
2115
2116 procedure UPDATE_ROW (
2117 X_MSITE_ID in NUMBER,
2118 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2119 X_ATTRIBUTE1 in VARCHAR2,
2120 X_ATTRIBUTE2 in VARCHAR2,
2121 X_ATTRIBUTE3 in VARCHAR2,
2122 X_ATTRIBUTE4 in VARCHAR2,
2123 X_ATTRIBUTE5 in VARCHAR2,
2124 X_ATTRIBUTE6 in VARCHAR2,
2125 X_ATTRIBUTE7 in VARCHAR2,
2126 X_ATTRIBUTE8 in VARCHAR2,
2127 X_ATTRIBUTE9 in VARCHAR2,
2128 X_ATTRIBUTE11 in VARCHAR2,
2129 X_ATTRIBUTE10 in VARCHAR2,
2130 X_ATTRIBUTE12 in VARCHAR2,
2131 X_ATTRIBUTE13 in VARCHAR2,
2132 X_ATTRIBUTE14 in VARCHAR2,
2133 X_ATTRIBUTE15 in VARCHAR2,
2134 X_SECURITY_GROUP_ID in NUMBER,
2135 X_OBJECT_VERSION_NUMBER in NUMBER,
2136 X_STORE_ID in NUMBER,
2137 X_START_DATE_ACTIVE in DATE,
2138 X_END_DATE_ACTIVE in DATE,
2139 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
2140 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
2141 X_DEFAULT_DATE_FORMAT in VARCHAR2,
2142 X_DEFAULT_ORG_ID in NUMBER,
2143 X_ATP_CHECK_FLAG in VARCHAR2,
2144 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
2145 X_MSITE_ROOT_SECTION_ID in NUMBER,
2146 X_PROFILE_ID in NUMBER,
2147 X_MASTER_MSITE_FLAG in VARCHAR2,
2148 X_MSITE_NAME in VARCHAR2,
2149 X_MSITE_DESCRIPTION in VARCHAR2,
2150 X_LAST_UPDATE_DATE in DATE,
2151 X_LAST_UPDATED_BY in NUMBER,
2152 X_LAST_UPDATE_LOGIN in NUMBER ,
2153 X_RESP_ACCESS_FLAG in VARCHAR2 ,
2154 X_PARTY_ACCESS_CODE in VARCHAR2 ,
2155 X_ACCESS_NAME in VARCHAR2 ,
2156 X_URL IN VARCHAR2 ,
2157 X_THEME_ID IN NUMBER )
2158 IS
2159 begin
2160 update JTF_MSITES_B set
2161 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
2162 ATTRIBUTE1 = X_ATTRIBUTE1,
2163 ATTRIBUTE2 = X_ATTRIBUTE2,
2164 ATTRIBUTE3 = X_ATTRIBUTE3,
2165 ATTRIBUTE4 = X_ATTRIBUTE4,
2166 ATTRIBUTE5 = X_ATTRIBUTE5,
2167 ATTRIBUTE6 = X_ATTRIBUTE6,
2168 ATTRIBUTE7 = X_ATTRIBUTE7,
2169 ATTRIBUTE8 = X_ATTRIBUTE8,
2170 ATTRIBUTE9 = X_ATTRIBUTE9,
2171 ATTRIBUTE11 = X_ATTRIBUTE11,
2172 ATTRIBUTE10 = X_ATTRIBUTE10,
2173 ATTRIBUTE12 = X_ATTRIBUTE12,
2174 ATTRIBUTE13 = X_ATTRIBUTE13,
2175 ATTRIBUTE14 = X_ATTRIBUTE14,
2176 ATTRIBUTE15 = X_ATTRIBUTE15,
2177 SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
2178 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
2179 STORE_ID = X_STORE_ID,
2180 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
2181 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
2182 DEFAULT_LANGUAGE_CODE = X_DEFAULT_LANGUAGE_CODE,
2183 DEFAULT_CURRENCY_CODE = X_DEFAULT_CURRENCY_CODE,
2184 DEFAULT_DATE_FORMAT = X_DEFAULT_DATE_FORMAT,
2185 DEFAULT_ORG_ID = X_DEFAULT_ORG_ID,
2186 ATP_CHECK_FLAG = X_ATP_CHECK_FLAG,
2187 WALKIN_ALLOWED_FLAG = X_WALKIN_ALLOWED_FLAG,
2188 MSITE_ROOT_SECTION_ID = X_MSITE_ROOT_SECTION_ID,
2189 PROFILE_ID = X_PROFILE_ID,
2190 MASTER_MSITE_FLAG = X_MASTER_MSITE_FLAG,
2191 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2192 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2193 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN ,
2194 RESP_ACCESS_FLAG = X_RESP_ACCESS_FLAG ,
2195 PARTY_ACCESS_CODE = X_PARTY_ACCESS_CODE ,
2196 ACCESS_NAME = X_ACCESS_NAME ,
2197 URL = X_URL ,
2198 THEME_ID = X_THEME_ID
2199 WHERE
2200 MSITE_ID = X_MSITE_ID
2201 AND OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
2202 FND_API.G_MISS_NUM,
2203 OBJECT_VERSION_NUMBER,
2204 X_OBJECT_VERSION_NUMBER);
2205
2206 if (sql%notfound) then
2207 raise no_data_found;
2208 end if;
2209
2210 update JTF_MSITES_TL set
2211 MSITE_NAME = X_MSITE_NAME,
2212 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
2213 MSITE_DESCRIPTION = X_MSITE_DESCRIPTION,
2214 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
2215 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2216 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2217 SOURCE_LANG = userenv('LANG')
2218 where MSITE_ID = X_MSITE_ID
2219 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
2220 and OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
2221 FND_API.G_MISS_NUM,
2222 OBJECT_VERSION_NUMBER,
2223 X_OBJECT_VERSION_NUMBER);
2224
2225 if (sql%notfound) then
2226 raise no_data_found;
2227 end if;
2228 end UPDATE_ROW;
2229
2230 procedure DELETE_ROW (
2231 X_MSITE_ID in NUMBER
2232 ) IS
2233 begin
2234 delete from JTF_MSITES_TL
2235 where MSITE_ID = X_MSITE_ID;
2236
2237 if (sql%notfound) then
2238 raise no_data_found;
2239 end if;
2240
2241 delete from JTF_MSITES_B
2242 where MSITE_ID = X_MSITE_ID;
2243
2244 if (sql%notfound) then
2245 raise no_data_found;
2246 end if;
2247 end DELETE_ROW;
2248
2249 procedure TRANSLATE_ROW (
2250 X_MSITE_ID in NUMBER,
2251 X_OWNER in VARCHAR2,
2252 X_MSITE_NAME in VARCHAR2,
2253 X_MSITE_DESCRIPTION in VARCHAR2
2254 ) IS
2255
2256 begin
2257
2258 update jtf_msites_tl
2259 set language = USERENV('LANG'),
2260 source_lang = USERENV('LANG'),
2261 object_version_number = object_version_number + 1,
2262 msite_name = X_MSITE_NAME,
2263 msite_description = X_MSITE_DESCRIPTION,
2264 last_updated_by = decode(X_OWNER,'SEED',1,0),
2265 last_update_date = sysdate,
2266 last_update_login=0
2267 Where userenv('LANG') in (language,source_lang)
2268 and msite_id = X_MSITE_ID;
2269
2270 end TRANSLATE_ROW;
2271
2272 -- Modifying the procedure to accept 3 new fields for globalisation
2273 -- ssridhar
2274 -- RESP_ACCESS_FLAG
2275 -- PARTY_ACCESS_CODE
2276 -- ACCESS_NAME
2277
2278
2279
2280 procedure LOAD_ROW (
2281 X_MSITE_ID in NUMBER,
2282 X_OWNER in VARCHAR2,
2283 X_ATTRIBUTE_CATEGORY in VARCHAR2,
2284 X_ATTRIBUTE1 in VARCHAR2,
2285 X_ATTRIBUTE2 in VARCHAR2,
2286 X_ATTRIBUTE3 in VARCHAR2,
2287 X_ATTRIBUTE4 in VARCHAR2,
2288 X_ATTRIBUTE5 in VARCHAR2,
2289 X_ATTRIBUTE6 in VARCHAR2,
2290 X_ATTRIBUTE7 in VARCHAR2,
2291 X_ATTRIBUTE8 in VARCHAR2,
2292 X_ATTRIBUTE9 in VARCHAR2,
2293 X_ATTRIBUTE11 in VARCHAR2,
2294 X_ATTRIBUTE10 in VARCHAR2,
2295 X_ATTRIBUTE12 in VARCHAR2,
2296 X_ATTRIBUTE13 in VARCHAR2,
2297 X_ATTRIBUTE14 in VARCHAR2,
2298 X_ATTRIBUTE15 in VARCHAR2,
2299 X_SECURITY_GROUP_ID in NUMBER,
2300 X_OBJECT_VERSION_NUMBER in NUMBER,
2301 X_STORE_ID in NUMBER,
2302 X_START_DATE_ACTIVE in DATE,
2303 X_END_DATE_ACTIVE in DATE,
2304 X_DEFAULT_LANGUAGE_CODE in VARCHAR2,
2305 X_DEFAULT_CURRENCY_CODE in VARCHAR2,
2306 X_DEFAULT_DATE_FORMAT in VARCHAR2,
2307 X_DEFAULT_ORG_ID in NUMBER,
2308 X_ATP_CHECK_FLAG in VARCHAR2,
2309 X_WALKIN_ALLOWED_FLAG in VARCHAR2,
2310 X_MSITE_ROOT_SECTION_ID in NUMBER,
2311 X_PROFILE_ID in NUMBER,
2312 X_MASTER_MSITE_FLAG in VARCHAR2,
2313 X_MSITE_NAME in VARCHAR2,
2314 X_MSITE_DESCRIPTION in VARCHAR2 ,
2315 X_RESP_ACCESS_FLAG in VARCHAR2 ,
2316 X_PARTY_ACCESS_CODE in VARCHAR2 ,
2317 X_ACCESS_NAME in VARCHAR2 ,
2318 X_URL in VARCHAR2 ,
2319 X_THEME_ID in NUMBER )
2320 IS
2321
2322 Owner_id NUMBER := 0;
2323 Row_Id VARCHAR2(64);
2324 l_object_version_number NUMBER := 1;
2325
2326 Begin
2327
2328 If X_OWNER = 'SEED' Then
2329 Owner_id := 1;
2330 End If;
2331
2332 IF ((x_object_version_number IS NOT NULL) AND
2333 (x_object_version_number <> FND_API.G_MISS_NUM))
2334 THEN
2335 l_object_version_number := x_object_version_number;
2336 END IF;
2337
2338 UPDATE_ROW(
2339 X_MSITE_ID => X_MSITE_ID,
2340 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
2341 X_ATTRIBUTE1 => X_ATTRIBUTE1,
2342 X_ATTRIBUTE2 => X_ATTRIBUTE2,
2343 X_ATTRIBUTE3 => X_ATTRIBUTE3,
2344 X_ATTRIBUTE4 => X_ATTRIBUTE4,
2345 X_ATTRIBUTE5 => X_ATTRIBUTE5,
2346 X_ATTRIBUTE6 => X_ATTRIBUTE6,
2347 X_ATTRIBUTE7 => X_ATTRIBUTE7,
2348 X_ATTRIBUTE8 => X_ATTRIBUTE8,
2349 X_ATTRIBUTE9 => X_ATTRIBUTE9,
2350 X_ATTRIBUTE11 => X_ATTRIBUTE10,
2351 X_ATTRIBUTE10 => X_ATTRIBUTE11,
2352 X_ATTRIBUTE12 => X_ATTRIBUTE12,
2353 X_ATTRIBUTE13 => X_ATTRIBUTE13,
2354 X_ATTRIBUTE14 => X_ATTRIBUTE14,
2355 X_ATTRIBUTE15 => X_ATTRIBUTE15,
2356 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
2357 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
2358 X_STORE_ID => X_STORE_ID,
2359 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
2360 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
2361 X_DEFAULT_LANGUAGE_CODE => X_DEFAULT_LANGUAGE_CODE,
2362 X_DEFAULT_CURRENCY_CODE => X_DEFAULT_CURRENCY_CODE,
2363 X_DEFAULT_DATE_FORMAT => X_DEFAULT_DATE_FORMAT,
2364 X_DEFAULT_ORG_ID => X_DEFAULT_ORG_ID,
2365 X_ATP_CHECK_FLAG => X_ATP_CHECK_FLAG,
2366 X_WALKIN_ALLOWED_FLAG => X_WALKIN_ALLOWED_FLAG,
2367 X_MSITE_ROOT_SECTION_ID => X_MSITE_ROOT_SECTION_ID,
2368 X_PROFILE_ID => X_PROFILE_ID,
2369 X_MASTER_MSITE_FLAG => X_MASTER_MSITE_FLAG,
2370 X_MSITE_NAME => X_MSITE_NAME,
2371 X_MSITE_DESCRIPTION => X_MSITE_DESCRIPTION,
2372 X_LAST_UPDATE_DATE => SYSDATE,
2373 X_LAST_UPDATED_BY => Owner_id,
2374 X_LAST_UPDATE_LOGIN => 0 ,
2375 X_RESP_ACCESS_FLAG => X_RESP_ACCESS_FLAG ,
2376 X_PARTY_ACCESS_CODE => X_PARTY_ACCESS_CODE ,
2377 X_ACCESS_NAME => X_ACCESS_NAME ,
2378 X_URL => X_URL,
2379 X_THEME_ID => X_THEME_ID );
2380
2381 Exception
2382
2383 When NO_DATA_FOUND Then
2384 INSERT_ROW(
2385 X_ROWID => Row_id,
2386 X_MSITE_ID => X_MSITE_ID,
2387 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
2388 X_ATTRIBUTE1 => X_ATTRIBUTE1,
2389 X_ATTRIBUTE2 => X_ATTRIBUTE2,
2390 X_ATTRIBUTE3 => X_ATTRIBUTE3,
2391 X_ATTRIBUTE4 => X_ATTRIBUTE4,
2392 X_ATTRIBUTE5 => X_ATTRIBUTE5,
2393 X_ATTRIBUTE6 => X_ATTRIBUTE6,
2394 X_ATTRIBUTE7 => X_ATTRIBUTE7,
2395 X_ATTRIBUTE8 => X_ATTRIBUTE8,
2396 X_ATTRIBUTE9 => X_ATTRIBUTE9,
2397 X_ATTRIBUTE11 => X_ATTRIBUTE10,
2398 X_ATTRIBUTE10 => X_ATTRIBUTE11,
2399 X_ATTRIBUTE12 => X_ATTRIBUTE12,
2400 X_ATTRIBUTE13 => X_ATTRIBUTE13,
2401 X_ATTRIBUTE14 => X_ATTRIBUTE14,
2402 X_ATTRIBUTE15 => X_ATTRIBUTE15,
2403 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
2404 X_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER,
2405 X_STORE_ID => X_STORE_ID,
2406 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
2407 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
2408 X_DEFAULT_LANGUAGE_CODE => X_DEFAULT_LANGUAGE_CODE,
2409 X_DEFAULT_CURRENCY_CODE => X_DEFAULT_CURRENCY_CODE,
2410 X_DEFAULT_DATE_FORMAT => X_DEFAULT_DATE_FORMAT,
2411 X_DEFAULT_ORG_ID => X_DEFAULT_ORG_ID,
2412 X_ATP_CHECK_FLAG => X_ATP_CHECK_FLAG,
2413 X_WALKIN_ALLOWED_FLAG => X_WALKIN_ALLOWED_FLAG,
2414 X_MSITE_ROOT_SECTION_ID => X_MSITE_ROOT_SECTION_ID,
2415 X_PROFILE_ID => X_PROFILE_ID,
2416 X_MASTER_MSITE_FLAG => X_MASTER_MSITE_FLAG,
2417 X_MSITE_NAME => X_MSITE_NAME,
2418 X_MSITE_DESCRIPTION => X_MSITE_DESCRIPTION,
2419 X_CREATION_DATE => SYSDATE,
2420 X_CREATED_BY => Owner_id,
2421 X_LAST_UPDATE_DATE => SYSDATE,
2422 X_LAST_UPDATED_BY => Owner_id,
2423 X_LAST_UPDATE_LOGIN => 0 ,
2424 X_RESP_ACCESS_FLAG => X_RESP_ACCESS_FLAG ,
2425 X_PARTY_ACCESS_CODE => X_PARTY_ACCESS_CODE ,
2426 X_ACCESS_NAME => X_ACCESS_NAME ,
2427 X_URL => X_URL,
2428 X_THEME_ID => X_THEME_ID ) ;
2429
2430 End LOAD_ROW;
2431
2432 procedure ADD_LANGUAGE
2433 is
2434 begin
2435 delete from JTF_MSITES_TL T
2436 where not exists
2437 (select NULL
2438 from JTF_MSITES_B B
2439 where B.MSITE_ID = T.MSITE_ID
2440 );
2441
2442 update JTF_MSITES_TL T set (
2443 MSITE_NAME,
2444 MSITE_DESCRIPTION
2445 ) = (select
2446 B.MSITE_NAME,
2447 B.MSITE_DESCRIPTION
2448 from JTF_MSITES_TL B
2449 where B.MSITE_ID = T.MSITE_ID
2450 and B.LANGUAGE = T.SOURCE_LANG)
2451 where (
2452 T.MSITE_ID,
2453 T.LANGUAGE
2454 ) in (select
2455 SUBT.MSITE_ID,
2456 SUBT.LANGUAGE
2457 from JTF_MSITES_TL SUBB, JTF_MSITES_TL SUBT
2458 where SUBB.MSITE_ID = SUBT.MSITE_ID
2459 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2460 and (SUBB.MSITE_NAME <> SUBT.MSITE_NAME
2461 or (SUBB.MSITE_NAME is null and SUBT.MSITE_NAME is not null)
2462 or (SUBB.MSITE_NAME is not null and SUBT.MSITE_NAME is null)
2463 or SUBB.MSITE_DESCRIPTION <> SUBT.MSITE_DESCRIPTION
2464 or (SUBB.MSITE_DESCRIPTION is null and SUBT.MSITE_DESCRIPTION is not null)
2465 or (SUBB.MSITE_DESCRIPTION is not null and SUBT.MSITE_DESCRIPTION is null)
2466 ));
2467
2468 insert into JTF_MSITES_TL (
2469 MSITE_ID,
2470 OBJECT_VERSION_NUMBER,
2471 CREATED_BY,
2472 CREATION_DATE,
2473 LAST_UPDATED_BY,
2474 LAST_UPDATE_DATE,
2475 LAST_UPDATE_LOGIN,
2476 MSITE_NAME,
2477 MSITE_DESCRIPTION,
2478 LANGUAGE,
2479 SOURCE_LANG
2480 ) select
2481 B.MSITE_ID,
2482 B.OBJECT_VERSION_NUMBER,
2483 B.CREATED_BY,
2484 B.CREATION_DATE,
2485 B.LAST_UPDATED_BY,
2486 B.LAST_UPDATE_DATE,
2487 B.LAST_UPDATE_LOGIN,
2488 B.MSITE_NAME,
2489 B.MSITE_DESCRIPTION,
2490 L.LANGUAGE_CODE,
2491 B.SOURCE_LANG
2492 from JTF_MSITES_TL B, FND_LANGUAGES L
2493 where L.INSTALLED_FLAG in ('I', 'B')
2494 and B.LANGUAGE = userenv('LANG')
2495 and not exists
2496 (select NULL
2497 from JTF_MSITES_TL T
2498 where T.MSITE_ID = B.MSITE_ID
2499 and T.LANGUAGE = L.LANGUAGE_CODE);
2500 end ADD_LANGUAGE;
2501 END JTF_Msite_GRP;
2502