[Home] [Help]
PACKAGE BODY: APPS.IBE_MINISITERUNTIME_PVT
Source
1 PACKAGE BODY IBE_MinisiteRuntime_PVT AS
2 /* $Header: IBEVMSRB.pls 120.6.12010000.2 2009/01/16 09:40:32 scnagara ship $ */
3
4 -- PACKAGE
5 -- IBE_MinisiteRuntime_PVT
6 --
7 -- PROCEDURES
8 -- get_minisite_details
9 -- HISTORY
10 -- 11/19/99 drao Created
11 -- 01/02/02 ssridhar modified to support bind variables in
12 -- load_msite_list_details.
13 -- 06/10/02 add payment threshold
14 -- 08/06/02 remove the validation of start_date and end_date for minisite and resp
15 -- instea, add the start_date and end_date into the cursor
16 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
17 -- 01/16/09 Bug 7676477 scnagara Removed loading of excluded items, excluded sections from Get_Msite_Details
18 -- procedure,Added Get_Msite_Excluded_Items and Get_Msite_Excluded_Sections procedures
19 -- ************************************************************************
20
21 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MINISITERUNTIME_PVT';
22 G_FILE_NAME CONSTANT VARCHAR2(12) := 'IBEVMSRB.pls';
23
24 --+
25 -- Get master mini site id for the store
26 --+
27 PROCEDURE Get_Master_Mini_Site_Id
28 (
29 x_mini_site_id OUT NOCOPY NUMBER,
30 x_root_section_id OUT NOCOPY NUMBER
31 )
32 IS
33 l_api_name CONSTANT VARCHAR2(30) :=
34 'Get_Master_Mini_Site_Id';
35 l_api_version CONSTANT NUMBER := 1.0;
36
37 CURSOR c1 IS
38 SELECT msite_id, msite_root_section_id FROM ibe_msites_b
39 WHERE UPPER(master_msite_flag) = 'Y' and site_type = 'I';
40 BEGIN
41
42 OPEN c1;
43 FETCH c1 INTO x_mini_site_id, x_root_section_id;
44 IF (c1%NOTFOUND) THEN
45 CLOSE c1;
46 RAISE FND_API.G_EXC_ERROR;
47 END IF;
48 CLOSE c1;
49
50 END Get_Master_Mini_Site_Id;
51
52
53 -- Bug 7676477, scnagara
54 PROCEDURE Get_Msite_Excluded_Items
55 (
56 p_api_version IN NUMBER,
57 p_msite_id IN NUMBER,
58 p_access_name IN VARCHAR2,
59 x_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
60 x_return_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2
63 )
64 IS
65 Cursor C_msite_valid(l_c_msite_id Number)
66 Is Select msite_root_section_id
67 From ibe_msites_b
68 Where msite_id = l_c_msite_id
69 And master_msite_flag = 'N' and site_type = 'I';
70
71 Cursor C_msite_Id(p_access_name Varchar2)
72 Is Select msite_id,msite_root_section_id
73 From ibe_msites_b
74 Where access_name = p_access_name
75 And master_msite_flag = 'N' and site_type = 'I';
76
77 CURSOR c2(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER,
78 l_root_section_id IN NUMBER) IS
79 SELECT /*+ first_rows */ inventory_item_id
80 FROM (
81 SELECT section_item_id, inventory_item_id
82 FROM ibe_dsp_section_items idsi
83 WHERE section_id IN
84 (
85 SELECT child_section_id
86 FROM ibe_dsp_msite_sct_sects s1
87 WHERE mini_site_id = l_c_master_msite_id
88 AND NOT EXISTS
89 (
90 SELECT child_section_id
91 FROM ibe_dsp_msite_sct_sects s2
92 WHERE mini_site_id = l_c_msite_id
93 AND s2.child_section_id = s1.child_section_id
94 )
95 CONNECT BY PRIOR child_section_id = parent_section_id
96 AND PRIOR mini_site_id = l_c_master_msite_id
97 AND mini_site_id = l_c_master_msite_id
98 START WITH child_section_id = l_root_section_id
99 AND mini_site_id = l_c_master_msite_id
100 )
101 AND NOT EXISTS
102 (
103 SELECT inventory_item_id
104 FROM ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2
105 WHERE i1.section_item_id = i2.section_item_id
106 AND i2.mini_site_id = l_c_msite_id
107 AND i1.inventory_item_id = idsi.inventory_item_id
108 )
109 UNION
110 SELECT section_item_id, inventory_item_id
111 FROM ibe_dsp_msite_sct_sects s3,
112 ibe_dsp_section_items i2
113 WHERE i2.section_id = s3.child_section_id
114 AND s3.mini_site_id = l_c_msite_id
115 AND NOT EXISTS
116 (
117 SELECT null
118 FROM ibe_dsp_msite_sct_items i3
119 WHERE mini_site_id = l_c_msite_id
120 AND i3.section_item_id = i2.section_item_id
121 )
122 );
123
124 l_root_section_id NUMBER;
125 l_master_root_section_id NUMBER;
126 l_master_msite_id NUMBER;
127 l_msite_id NUMBER;
128 l_index BINARY_INTEGER;
129 l_msite_not_exists_excp EXCEPTION;
130 l_api_name CONSTANT VARCHAR2(30) := 'Get_Msite_Excluded_Items';
131 BEGIN
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
135 IBE_Util.Debug('IBE_MinisiteRuntime_PVT.Get_Msite_Excluded_Items start');
136 END IF;
137
138 l_msite_id := p_msite_id ;
139 If (l_msite_id IS NULL OR l_msite_id = FND_API.G_MISS_NUM) And
140 (p_access_name IS NOT NULL) Then
141 Open C_msite_Id(p_access_name);
142 Fetch C_msite_Id INTO l_msite_id, l_root_section_id;
143 If C_msite_Id%NOTFOUND Then
144 Close C_msite_Id;
145 RAISE l_msite_not_exists_excp;
146 End If;
147 Close C_msite_Id;
148 End If;
149
150 --+
151 -- Get the master mini-site ID
152 --+
153 Get_Master_Mini_Site_Id
154 (
155 x_mini_site_id => l_master_msite_id,
156 x_root_section_id => l_master_root_section_id
157 );
158
159 l_index := 1;
160 x_item_ids := JTF_NUMBER_TABLE();
161 OPEN C_msite_valid(l_msite_id);
162 FETCH C_msite_valid INTO l_root_section_id;
163 CLOSE C_msite_valid;
164 FOR r2 IN c2(l_master_msite_id, l_msite_id,l_root_section_id) LOOP
165 x_item_ids.EXTEND();
166 x_item_ids(l_index) := r2.inventory_item_id;
167 l_index := l_index + 1;
168 END LOOP; -- end loop r2
169
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
174 p_count => x_msg_count,
175 p_data => x_msg_data);
176
177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
179 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
180 p_count => x_msg_count,
181 p_data => x_msg_data);
182
183 WHEN l_msite_not_exists_excp THEN
184 x_return_status := FND_API.g_ret_sts_error;
185 FND_MESSAGE.set_name('IBE','IBE_MSITE_NOT_EXISTS');
186 FND_MSG_PUB.ADD;
187 FND_MSG_PUB.count_and_get(
188 p_encoded => FND_API.g_false,
189 p_count => x_msg_count,
190 p_data => x_msg_data );
191
192 WHEN OTHERS THEN
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
195 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
196 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
197 FND_MESSAGE.Set_Token('REASON', SQLERRM);
198 FND_MSG_PUB.Add;
199 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
200 p_count => x_msg_count,
201 p_data => x_msg_data);
202 END Get_Msite_Excluded_Items;
203
204
205 -- Bug 7676477, scnagara
206 PROCEDURE Get_Msite_Excluded_Sections
207 (
208 p_api_version IN NUMBER,
209 p_msite_id IN NUMBER,
210 p_access_name IN VARCHAR2,
211 x_section_ids OUT NOCOPY JTF_NUMBER_TABLE,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 )
216 IS
217 l_index BINARY_INTEGER;
218 l_master_msite_id NUMBER;
219 l_master_root_section_id NUMBER;
220 l_msite_id NUMBER;
221 l_root_section_id NUMBER;
222 l_msite_not_exists_excp EXCEPTION;
223 l_api_name CONSTANT VARCHAR2(30) :=
224 'Get_Msite_Excluded_Sections';
225
226 CURSOR c1(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER)
227 IS SELECT child_section_id
228 FROM ibe_dsp_msite_sct_sects
229 WHERE mini_site_id = l_c_master_msite_id AND
230 sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
231 AND child_section_id NOT IN
232 (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
233 WHERE mini_site_id = l_c_msite_id)
234 START WITH child_section_id =
235 (SELECT msite_root_section_id FROM ibe_msites_b
236 WHERE msite_id = l_c_msite_id)
237 AND mini_site_id = l_c_master_msite_id
238 CONNECT BY PRIOR child_section_id = parent_section_id
239 AND mini_site_id = l_c_master_msite_id
240 AND PRIOR mini_site_id = l_c_master_msite_id;
241
242 Cursor C_msite_Id(p_access_name Varchar2)
243 Is Select msite_id,msite_root_section_id
244 From ibe_msites_b
245 Where access_name = p_access_name
246 And master_msite_flag = 'N' and site_type = 'I';
247
248 BEGIN
249
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 l_msite_id := p_msite_id ;
253 If (l_msite_id IS NULL OR l_msite_id = FND_API.G_MISS_NUM) And
254 (p_access_name IS NOT NULL) Then
255 Open C_msite_Id(p_access_name);
256 Fetch C_msite_Id INTO l_msite_id, l_root_section_id;
257 If C_msite_Id%NOTFOUND Then
258 Close C_msite_Id;
259 RAISE l_msite_not_exists_excp;
260 End If;
261 Close C_msite_Id;
262 End If;
263
264 Get_Master_Mini_Site_Id
265 (
266 x_mini_site_id => l_master_msite_id,
267 x_root_section_id => l_master_root_section_id
268 );
269
270 x_section_ids := JTF_NUMBER_TABLE();
271 l_index := 1;
272 FOR r1 IN c1(l_master_msite_id, l_msite_id) LOOP
273 x_section_ids.EXTEND();
274 x_section_ids(l_index) := r1.child_section_id;
275 l_index := l_index + 1;
276 END LOOP; -- end loop r1
277
278 EXCEPTION
279
280 WHEN FND_API.G_EXC_ERROR THEN
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
283 p_count => x_msg_count,
284 p_data => x_msg_data);
285
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
289 p_count => x_msg_count,
290 p_data => x_msg_data);
291
292 WHEN l_msite_not_exists_excp THEN
293 x_return_status := FND_API.g_ret_sts_error;
294 FND_MESSAGE.set_name('IBE','IBE_MSITE_NOT_EXISTS');
295 FND_MSG_PUB.ADD;
296 FND_MSG_PUB.count_and_get(
297 p_encoded => FND_API.g_false,
298 p_count => x_msg_count,
299 p_data => x_msg_data );
300
301 WHEN OTHERS THEN
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
304 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
305 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
306 FND_MESSAGE.Set_Token('REASON', SQLERRM);
307 FND_MSG_PUB.Add;
308 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
309 p_count => x_msg_count,
310 p_data => x_msg_data);
311
312 END Get_Msite_Excluded_Sections;
313
314
315 PROCEDURE Get_Msite_Details
316 (
317 p_api_version IN NUMBER,
318 p_msite_id IN NUMBER,
319 p_access_name IN VARCHAR2,
320 x_master_msite_id OUT NOCOPY NUMBER,
321 x_minisite_cur OUT NOCOPY minisite_cur_type,
322 x_lang_cur OUT NOCOPY lang_cur_type,
323 x_currency_cur OUT NOCOPY currency_cur_type,
324 x_sections_cur OUT NOCOPY sections_cur_type,
325 x_items_cur OUT NOCOPY items_cur_type,
326 x_name_cur OUT NOCOPY name_cur_type ,
327 x_msite_resps_cur OUT NOCOPY msite_resp_cur_type ,
328 x_party_access_cur OUT NOCOPY msite_prty_access_cur_type,
329 x_pm_cc_sm_cur OUT NOCOPY pm_cc_sm_cur_type,
330 x_return_status OUT NOCOPY VARCHAR2,
331 x_msg_count OUT NOCOPY NUMBER,
332 x_msg_data OUT NOCOPY VARCHAR2
333 )
334 IS
335 l_api_name CONSTANT VARCHAR2(30) :=
336 'Get_Msite_Details';
337 l_api_version CONSTANT NUMBER := 1.0;
338
339 l_root_section_id NUMBER;
340 l_application_id NUMBER := 671;
341 l_exclude_flag VARCHAR2(10);
342 l_msite_id NUMBER;
343 l_master_root_section_id NUMBER;
344 l_msite_not_exists_excp EXCEPTION;
345
346 Cursor C_msite_Id(p_access_name Varchar2)
347 Is Select msite_id,msite_root_section_id
348 From ibe_msites_b
349 Where access_name = p_access_name
350 And master_msite_flag = 'N' and site_type = 'I';
351 -- removed by YAXU on 08/06/02
352 -- And sysdate BETWEEN start_date_active
353 -- And NVL(end_date_active,sysdate) ;
354
355 -- This cursor is used for checking whether a given msite_id is+
356 -- valid and enabled for store. Also it is used to fetch the+
357 -- root section id for a given minisite.+
358
359 Cursor C_msite_valid(l_c_msite_id Number)
360 Is Select msite_root_section_id
361 From ibe_msites_b
362 Where msite_id = l_c_msite_id
363 And master_msite_flag = 'N' and site_type = 'I';
364 -- removed by YAXU on 08/06/02
365 -- And sysdate BETWEEN start_date_active
366 -- And NVL(end_date_active,sysdate) ;
367
368 BEGIN
369
370 x_return_status := FND_API.G_RET_STS_SUCCESS;
371
372 l_msite_id := p_msite_id ;
373 If (l_msite_id IS NULL OR l_msite_id = FND_API.G_MISS_NUM) And
374 (p_access_name IS NOT NULL) Then
375 Open C_msite_Id(p_access_name);
376 Fetch C_msite_Id INTO l_msite_id, l_root_section_id;
377 If C_msite_Id%NOTFOUND Then
378 Close C_msite_Id;
379 RAISE l_msite_not_exists_excp;
380 End If;
381 Close C_msite_Id;
382 Else
383 Open C_msite_valid(l_msite_id);
384 Fetch C_msite_valid INTO l_root_section_id;
385 If C_msite_valid%NOTFOUND Then
386 Close C_msite_valid;
387 RAISE l_msite_not_exists_excp;
388 End If;
389 Close C_msite_valid;
390 End If;
391
392 --+
393 -- Get the master mini-site ID
394 --+
395 Get_Master_Mini_Site_Id
396 (
397 x_mini_site_id => x_master_msite_id,
398 x_root_section_id => l_master_root_section_id
399 );
400
401 --added 3 new fields to the select stmt
402 -- access_name, resp_access_flag, party_access_code
403 OPEN x_minisite_cur FOR SELECT msite_id,default_language_code,
404 default_currency_code,default_org_id,
405 walkin_allowed_flag,msite_root_section_id,
406 master_msite_flag,atp_check_flag ,
407 default_date_format,profile_id,
408 access_name, resp_access_flag, party_access_code,
409 attribute1,attribute2,attribute3,attribute4,
410 attribute5,attribute6,attribute7,attribute8,
411 attribute9,attribute10,attribute11,attribute12,
412 attribute13,attribute14,attribute15,
413 payment_threshold_enable_flag, --added by YAXU for payment threshold
414 start_date_active, end_date_active -- added by YAXU on 08/06/02
415 FROM ibe_msites_b
416 WHERE msite_id = l_msite_id
417 AND site_type = 'I';
418
419 OPEN x_name_cur FOR SELECT a.language_code,
420 b.msite_name, b.msite_description
421 FROM ibe_msite_languages a, ibe_msites_tl b
422 WHERE a.language_code = b.language
423 AND b.msite_id = a.msite_id
424 AND b.msite_id = l_msite_id;
425
426 OPEN x_lang_cur FOR SELECT language_code from ibe_msite_languages l
427 WHERE l.msite_id = l_msite_id
428 AND l.enable_flag = 'Y';
429
430 OPEN x_currency_cur FOR SELECT currency_code, bizpartner_prc_listid,
431 registered_prc_listid, walkin_prc_listid, orderable_limit,
432 payment_threshold, partner_prc_listid -- added by YAXU for payment threshold
433 FROM ibe_msite_currencies c
434 WHERE c.msite_id = l_msite_id;
435
436 l_exclude_flag :=
437 FND_PROFILE.Value_Specific('IBE_USE_CATALOG_EXCLUSIONS',null,null,
438 l_application_id);
439
440 IF (l_exclude_flag IS NULL) THEN
441 l_exclude_flag := 'N';
442 END IF;
443
444 IF (l_exclude_flag = 'Y')
445 THEN
446
447 OPEN x_sections_cur FOR 'SELECT child_section_id '
448 || 'FROM ibe_dsp_msite_sct_sects '
449 || 'WHERE mini_site_id = :master_mini_site_id AND '
450 || 'sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate) '
451 || 'AND child_section_id NOT IN '
452 || '(SELECT child_section_id FROM ibe_dsp_msite_sct_sects '
453 || 'WHERE mini_site_id = :msite_id) '
454 || 'START WITH child_section_id = :root_section_id '
455 || 'AND mini_site_id = :master_mini_site_id '
456 || 'CONNECT BY PRIOR child_section_id = parent_section_id '
457 || 'AND mini_site_id = :master_mini_site_id '
458 || 'AND PRIOR mini_site_id = :master_mini_site_id '
459 USING x_master_msite_id, l_msite_id, l_root_section_id,
460 x_master_msite_id, x_master_msite_id, x_master_msite_id;
461
462 OPEN x_items_cur FOR 'SELECT /*+ first_rows */ inventory_item_id '
463 || 'FROM ( '
464 || ' SELECT section_item_id '
465 || ' FROM ibe_dsp_section_items '
466 || ' WHERE section_id IN '
467 || ' ( '
468 || ' SELECT child_section_id '
469 || ' FROM ibe_dsp_msite_sct_sects s1 '
470 || ' WHERE mini_site_id = :l_c_master_msite_id '
471 || ' AND NOT EXISTS '
472 || ' ( '
473 || ' SELECT child_section_id '
474 || ' FROM ibe_dsp_msite_sct_sects s2 '
475 || ' WHERE mini_site_id = :l_c_msite_id '
476 || ' AND s2.child_section_id = s1.child_section_id '
477 || ' ) '
478 || ' CONNECT BY PRIOR child_section_id = parent_section_id '
479 || ' AND PRIOR mini_site_id = :l_c_master_msite_id '
480 || ' AND mini_site_id = :l_c_master_msite_id '
481 || ' START WITH child_section_id = '
482 || ' ( '
483 || ' SELECT msite_root_section_id '
484 || ' FROM ibe_msites_b '
485 || ' WHERE msite_id = :l_c_msite_id '
486 || ' ) '
487 || ' AND mini_site_id = :l_c_master_msite_id '
488 || ' ) '
489 || ' AND inventory_item_id NOT IN '
490 || ' ( '
491 || ' SELECT inventory_item_id '
492 || ' FROM ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2 '
493 || ' WHERE i1.section_item_id = i2.section_item_id '
494 || ' AND i2.mini_site_id = :l_c_msite_id '
495 || ' ) '
496 || ' UNION '
497 || ' SELECT /*+ ordered use_nl(s3,i2) */ section_item_id '
498 || ' FROM ( '
499 || ' SELECT child_section_id '
500 || ' FROM ibe_dsp_msite_sct_sects '
501 || ' WHERE mini_site_id = :l_c_msite_id '
502 || ' ) s3, '
503 || ' ibe_dsp_section_items i2 '
504 || ' WHERE i2.section_id = s3.child_section_id '
505 || ' AND NOT EXISTS '
506 || ' ( '
507 || ' SELECT null '
508 || ' FROM ibe_dsp_msite_sct_items i3 '
509 || ' WHERE mini_site_id = :l_c_msite_id '
510 || ' AND i3.section_item_id = i2.section_item_id '
511 || ' ) '
512 || ' ) v1, '
513 || ' ibe_dsp_section_items i0 '
514 || 'WHERE i0.section_item_id = v1.section_item_id'
515 USING x_master_msite_id, l_msite_id, x_master_msite_id, x_master_msite_id,
516 l_msite_id, x_master_msite_id, l_msite_id, l_msite_id, l_msite_id;
517
518 -- OPEN x_items_cur FOR 'SELECT inventory_item_id '
519 -- || 'FROM ibe_dsp_section_items '
520 -- || 'WHERE section_item_id IN '
521 -- || '(SELECT section_item_id FROM ibe_dsp_section_items '
522 -- || 'WHERE section_id IN '
523 -- || '(SELECT child_section_id FROM ibe_dsp_msite_sct_sects '
524 -- || 'WHERE mini_site_id = :master_mini_site_id '
525 -- || 'AND child_section_id NOT IN '
526 -- || '(SELECT child_section_id FROM ibe_dsp_msite_sct_sects '
527 -- || 'WHERE mini_site_id = :msite_id) '
528 -- || 'START WITH child_section_id = :root_section_id '
529 -- || 'AND mini_site_id = :master_mini_site_id '
530 -- || 'CONNECT BY PRIOR child_section_id = parent_section_id '
531 -- || 'AND PRIOR mini_site_id = :master_mini_site_id '
532 -- || 'AND mini_site_id = :master_mini_site_id) '
533 -- || 'OR (section_id IN '
534 -- || '(SELECT child_section_id FROM ibe_dsp_msite_sct_sects '
535 -- || 'WHERE mini_site_id = :msite_id) '
536 -- || 'AND section_item_id NOT IN '
537 -- || '(SELECT section_item_id FROM ibe_dsp_msite_sct_items '
538 -- || 'WHERE mini_site_id = :msite_id))) '
539 -- USING x_master_msite_id, l_msite_id, l_root_section_id,
540 -- x_master_msite_id, x_master_msite_id, x_master_msite_id,
541 -- l_msite_id, l_msite_id;
542
543 ELSE
544
545 OPEN x_items_cur FOR select 0 from dual where sysdate < sysdate - 1;
546 OPEN x_sections_cur FOR select 0 from dual where sysdate < sysdate - 1;
547
548 END IF; -- end of exclusion block
549
550 -- added to cache the minisite responsibility association -- ssridhar
551 OPEN x_msite_resps_cur FOR Select respb.msite_resp_id,
552 respb.responsibility_id ,
553 respb.application_id,
554 respt.language,
555 respt.display_name,
556 respb.start_date_active, respb.end_date_active -- added by YAXU on 08/06/02
557 From ibe_msite_resps_b respb ,
558 ibe_msite_resps_tl respt ,
559 ibe_msite_languages lang
560 Where respb.msite_id = l_msite_id
561 And respb.msite_resp_id = respt.msite_resp_id
562 And lang.msite_id = respb.msite_id
563 And lang.language_code = respt.language
564 -- And sysdate Between respb.start_date_Active
565 -- And NVL(respb.end_date_active,sysdate) -- removed by YAXU on 08/06/02
566 ORDER BY respb.msite_resp_id;
567
568 -- added to cache the minisite party access information -- ssridhar
569 Open x_party_access_cur FOR Select Party_id
570 From ibe_msite_prty_accss accss
571 Where accss.msite_id = l_msite_id
572 And sysdate Between accss.start_date_Active
573 And NVL(accss.end_date_active,sysdate);
574
575 -- For payment method, credit card type, and shipment method
576 OPEN x_pm_cc_sm_cur FOR SELECT msite_information_context, msite_information1,
577 msite_information2 -- added by YAXU for payment threshold
578 FROM ibe_msite_information
579 WHERE msite_id = l_msite_id
580 AND msite_information_context = 'SHPMT_MTHD'
581 UNION -- added by JQU for validating the TAG.
582 SELECT msite_information_context, msite_information1,
583 msite_information2 -- added by YAXU for payment threshold
584 FROM ibe_msite_information a, fnd_lookup_values b
585 WHERE a.msite_id = l_msite_id
586 AND a.msite_information1 = b.lookup_code
587 AND ((b.LOOKUP_TYPE= 'CREDIT_CARD' and b.VIEW_APPLICATION_ID='660' )
588 OR (b.LOOKUP_TYPE = 'IBE_PAYMENT_TYPE'))
589 AND b.ENABLED_FLAG='Y' AND (b.TAG='Y' or b.TAG is null)
590 AND b.language=userenv('lang')
591 UNION
592 SELECT msite_information_context, msite_information1,
593 msite_information2
594 FROM ibe_msite_information a, iby_creditcard_issuers_b cc
595 WHERE a.msite_id = l_msite_id
596 AND a.msite_information1 = cc.card_issuer_code
597 ORDER BY msite_information_context;
598
599 EXCEPTION
600 WHEN FND_API.G_EXC_ERROR THEN
601 x_return_status := FND_API.g_ret_sts_error;
602 FND_MSG_PUB.count_and_get(
603 p_encoded => FND_API.g_false,
604 p_count => x_msg_count,
605 p_data => x_msg_data );
606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608 FND_MSG_PUB.count_and_get(
609 p_encoded => FND_API.g_false,
610 p_count => x_msg_count,
611 p_data => x_msg_data );
612 WHEN l_msite_not_exists_excp THEN
613 x_return_status := FND_API.g_ret_sts_error;
614 FND_MESSAGE.set_name('IBE','IBE_MSITE_NOT_EXISTS');
615 FND_MSG_PUB.ADD;
616 FND_MSG_PUB.count_and_get(
617 p_encoded => FND_API.g_false,
618 p_count => x_msg_count,
619 p_data => x_msg_data );
620 WHEN OTHERS THEN
621 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
622 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
623 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
624 FND_MESSAGE.Set_Token('REASON', SQLERRM);
625 FND_MSG_PUB.Add;
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627 FND_MSG_PUB.count_and_get(
628 p_encoded => FND_API.g_false,
629 p_count => x_msg_count,
630 p_data => x_msg_data );
631
632 END Get_Msite_Details;
633
634 PROCEDURE Get_Quote_Details
635 (
636 p_api_version IN NUMBER,
637 p_quote_id IN NUMBER,
638 x_ship_method_cur OUT NOCOPY ship_method_cur_type,
639 x_payment_method_cur OUT NOCOPY payment_method_cur_type,
640 x_quote_detail_cur OUT NOCOPY quote_detail_cur_type,
641 x_return_status OUT NOCOPY VARCHAR2,
642 x_msg_count OUT NOCOPY NUMBER,
643 x_msg_data OUT NOCOPY VARCHAR2
644 )
645 IS
646 l_api_name CONSTANT VARCHAR2(30) :=
647 'Get_Quote_Details';
648 l_api_version CONSTANT NUMBER := 1.0;
649
650 l_quote_id NUMBER;
651
652 BEGIN
653
654 l_quote_id := p_quote_id;
655
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657
658 OPEN x_ship_method_cur FOR SELECT SHIP_METHOD_CODE
659 FROM aso_shipments where quote_header_id = l_quote_id;
660
661 OPEN x_payment_method_cur FOR SELECT payment_type_code, credit_card_code
662 FROM aso_payments where quote_header_id = l_quote_id;
663
664 OPEN x_quote_detail_cur FOR SELECT currency_code, user_name,
665 total_quote_price
666 FROM aso_quote_headers_all A, fnd_user F
667 WHERE A.quote_header_id = l_quote_id
668 AND A.party_id = F.customer_id;
669
670 EXCEPTION
671 WHEN FND_API.G_EXC_ERROR THEN
672 x_return_status := FND_API.g_ret_sts_error;
673 FND_MSG_PUB.count_and_get(
674 p_encoded => FND_API.g_false,
675 p_count => x_msg_count,
676 p_data => x_msg_data );
677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 FND_MSG_PUB.count_and_get(
680 p_encoded => FND_API.g_false,
681 p_count => x_msg_count,
682 p_data => x_msg_data );
683 WHEN OTHERS THEN
684 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
685 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
686 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
687 FND_MESSAGE.Set_Token('REASON', SQLERRM);
688 FND_MSG_PUB.Add;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 FND_MSG_PUB.count_and_get(
691 p_encoded => FND_API.g_false,
692 p_count => x_msg_count,
693 p_data => x_msg_data );
694
695 END Get_Quote_Details;
696
697 --+
698 -- This procedure assumes that the list of mini-site IDs passed in will be
699 -- the active ones, that is, with store_id > 0 and with valid start_date_active
700 -- and end_date_active.
701 -- If p_msite_ids is NULL, then load all the mini-sites which are enabled
702 -- for store, and put the IDs back in x_msite_ids. If p_msite_ids IS NOT NULL,
703 -- then don't put IDs back in the x_msite_ids
704 --+
705 PROCEDURE Load_Msite_List_Details
706 (
707 p_api_version IN NUMBER,
708 p_msite_ids IN JTF_NUMBER_TABLE,
709 x_msite_ids OUT NOCOPY JTF_NUMBER_TABLE,
710 x_master_msite_id OUT NOCOPY NUMBER,
711 x_minisite_cur OUT NOCOPY minisite_cur_type,
712 x_name_cur OUT NOCOPY name_cur_type,
713 x_lang_cur OUT NOCOPY lang_cur_type,
714 x_currency_cur OUT NOCOPY currency_cur_type,
715 x_msite_resps_cur OUT NOCOPY msite_resp_cur_type,
716 x_party_access_cur OUT NOCOPY msite_prty_access_cur_type,
717 x_section_msite_ids OUT NOCOPY JTF_NUMBER_TABLE,
718 x_section_ids OUT NOCOPY JTF_NUMBER_TABLE,
719 x_item_msite_ids OUT NOCOPY JTF_NUMBER_TABLE,
720 x_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
721 x_pm_cc_sm_cur OUT NOCOPY pm_cc_sm_cur_type,
722 x_return_status OUT NOCOPY VARCHAR2,
723 x_msg_count OUT NOCOPY NUMBER,
724 x_msg_data OUT NOCOPY VARCHAR2
725 )
726 IS
727 l_api_name CONSTANT VARCHAR2(30) :=
728 'Load_Msite_List_Details';
729 l_api_version CONSTANT NUMBER := 1.0;
730
731 l_master_root_section_id NUMBER;
732 l_application_id NUMBER := 671;
733 l_exclude_flag VARCHAR2(10);
734 l_tmp_str VARCHAR2(4000);
735 l_first_idx BINARY_INTEGER;
736 l_index BINARY_INTEGER;
737 l_root_section_id NUMBER;
738
739 CURSOR c1(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER)
740 IS SELECT child_section_id
741 FROM ibe_dsp_msite_sct_sects
742 WHERE mini_site_id = l_c_master_msite_id AND
743 sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
744 AND child_section_id NOT IN
745 (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
746 WHERE mini_site_id = l_c_msite_id)
747 START WITH child_section_id =
748 (SELECT msite_root_section_id FROM ibe_msites_b
749 WHERE msite_id = l_c_msite_id)
750 AND mini_site_id = l_c_master_msite_id
751 CONNECT BY PRIOR child_section_id = parent_section_id
752 AND mini_site_id = l_c_master_msite_id
753 AND PRIOR mini_site_id = l_c_master_msite_id;
754
755 -- Description of the SQL
756 -- IS SELECT inventory_item_id
757 -- FROM ibe_dsp_section_items
758 -- WHERE section_item_id IN
759 ---- Get all the section items for the excluded sections for the mini-site
760 ---- starting from mini-site's root section
761 -- (SELECT section_item_id FROM ibe_dsp_section_items
762 -- WHERE section_id IN
763 -- (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
764 -- WHERE mini_site_id = l_c_master_msite_id
765 -- AND child_section_id NOT IN
766 -- (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
767 -- WHERE mini_site_id = l_c_msite_id)
768 -- START WITH child_section_id =
769 -- (SELECT msite_root_section_id FROM ibe_msites_b
770 -- WHERE msite_id = l_c_msite_id)
771 -- AND mini_site_id = l_c_master_msite_id
772 -- CONNECT BY PRIOR child_section_id = parent_section_id
773 -- AND PRIOR mini_site_id = l_c_master_msite_id
774 -- AND mini_site_id = l_c_master_msite_id)
775 ---- Get all the section items for the included sections for the mini-site
776 ---- starting from mini-site's root section, but the section-items themselves
777 ---- are excluded.
778 -- OR (section_id IN
779 -- (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
780 -- WHERE mini_site_id = l_c_msite_id)
781 -- AND section_item_id NOT IN
782 -- (SELECT section_item_id FROM ibe_dsp_msite_sct_items
783 -- WHERE mini_site_id = l_c_msite_id)));
784
785 --
786 -- Changing the cursor to new cursor based on new performance query
787 --
788 -- CURSOR c2(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER)
789 -- IS SELECT inventory_item_id
790 -- FROM ibe_dsp_section_items
791 -- WHERE section_item_id IN
792 -- (SELECT section_item_id FROM ibe_dsp_section_items
793 -- WHERE section_id IN
794 -- (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
795 -- WHERE mini_site_id = l_c_master_msite_id
796 -- AND child_section_id NOT IN
797 -- (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
798 -- WHERE mini_site_id = l_c_msite_id)
799 -- START WITH child_section_id =
800 -- (SELECT msite_root_section_id FROM ibe_msites_b
801 -- WHERE msite_id = l_c_msite_id)
802 -- AND mini_site_id = l_c_master_msite_id
803 -- CONNECT BY PRIOR child_section_id = parent_section_id
804 -- AND PRIOR mini_site_id = l_c_master_msite_id
805 -- AND mini_site_id = l_c_master_msite_id)
806 -- OR (section_id IN
807 -- (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
808 -- WHERE mini_site_id = l_c_msite_id)
809 -- AND section_item_id NOT IN
810 -- (SELECT section_item_id FROM ibe_dsp_msite_sct_items
811 -- WHERE mini_site_id = l_c_msite_id)));
812
813 CURSOR c2(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER,
814 l_root_section_id IN NUMBER) IS
815 SELECT /*+ first_rows */ inventory_item_id
816 FROM (
817 SELECT section_item_id, inventory_item_id
818 FROM ibe_dsp_section_items idsi
819 WHERE section_id IN
820 (
821 SELECT child_section_id
822 FROM ibe_dsp_msite_sct_sects s1
823 WHERE mini_site_id = l_c_master_msite_id
824 AND NOT EXISTS
825 (
826 SELECT child_section_id
827 FROM ibe_dsp_msite_sct_sects s2
828 WHERE mini_site_id = l_c_msite_id
829 AND s2.child_section_id = s1.child_section_id
830 )
831 CONNECT BY PRIOR child_section_id = parent_section_id
832 AND PRIOR mini_site_id = l_c_master_msite_id
833 AND mini_site_id = l_c_master_msite_id
834 START WITH child_section_id = l_root_section_id
835 AND mini_site_id = l_c_master_msite_id
836 )
837 AND NOT EXISTS
838 (
839 SELECT inventory_item_id
840 FROM ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2
841 WHERE i1.section_item_id = i2.section_item_id
842 AND i2.mini_site_id = l_c_msite_id
843 AND i1.inventory_item_id = idsi.inventory_item_id
844 )
845 UNION
846 SELECT section_item_id, inventory_item_id
847 FROM ibe_dsp_msite_sct_sects s3,
848 ibe_dsp_section_items i2
849 WHERE i2.section_id = s3.child_section_id
850 AND s3.mini_site_id = l_c_msite_id
851 AND NOT EXISTS
852 (
853 SELECT null
854 FROM ibe_dsp_msite_sct_items i3
855 WHERE mini_site_id = l_c_msite_id
856 AND i3.section_item_id = i2.section_item_id
857 )
858 );
859
860 -- SELECT /*+ first_rows */ inventory_item_id
861 -- FROM (
862 -- SELECT section_item_id
863 -- FROM ibe_dsp_section_items
864 -- WHERE section_id IN
865 -- (
866 -- SELECT child_section_id
867 -- FROM ibe_dsp_msite_sct_sects s1
868 -- WHERE mini_site_id = l_c_master_msite_id
869 -- AND NOT EXISTS
870 -- (
871 -- SELECT child_section_id
872 -- FROM ibe_dsp_msite_sct_sects s2
873 -- WHERE mini_site_id = l_c_msite_id
874 -- AND s2.child_section_id = s1.child_section_id
875 -- )
876 -- CONNECT BY PRIOR child_section_id = parent_section_id
877 -- AND PRIOR mini_site_id = l_c_master_msite_id
878 -- AND mini_site_id = l_c_master_msite_id
879 -- START WITH child_section_id =
880 -- (
881 -- SELECT msite_root_section_id
882 -- FROM ibe_msites_b
883 -- WHERE msite_id = l_c_msite_id
884 -- )
885 -- AND mini_site_id = l_c_master_msite_id
886 -- )
887 -- AND inventory_item_id NOT IN
888 -- (
889 -- SELECT inventory_item_id
890 -- FROM ibe_dsp_section_items i1, ibe_dsp_msite_sct_items i2
891 -- WHERE i1.section_item_id = i2.section_item_id
892 -- AND i2.mini_site_id = l_c_msite_id
893 -- )
894 -- UNION
895 -- SELECT /*+ ordered use_nl(s3,i2) */ section_item_id
896 -- FROM (
897 -- SELECT child_section_id
898 -- FROM ibe_dsp_msite_sct_sects
899 -- WHERE mini_site_id = l_c_msite_id
900 -- ) s3,
901 -- ibe_dsp_section_items i2
902 -- WHERE i2.section_id = s3.child_section_id
903 -- AND NOT EXISTS
904 -- (
905 -- SELECT null
906 -- FROM ibe_dsp_msite_sct_items i3
907 -- WHERE mini_site_id = l_c_msite_id
908 -- AND i3.section_item_id = i2.section_item_id
909 -- )
910 -- ) v1,
911 -- ibe_dsp_section_items i0
912 -- WHERE i0.section_item_id = v1.section_item_id;
913
914 CURSOR c3
915 IS SELECT msite_id FROM ibe_msites_b
916 -- removed by YAXU on 08/06/02
917 -- WHERE sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
918 --AND
919 WHERE master_msite_flag = 'N' and site_type = 'I';
920
921 Cursor C_msite_valid(l_c_msite_id Number)
922 Is Select msite_root_section_id
923 From ibe_msites_b
924 Where msite_id = l_c_msite_id
925 And master_msite_flag = 'N' and site_type = 'I';
926 -- removed by YAXU on 08/06/02
927 -- And sysdate BETWEEN start_date_active
928 -- And NVL(end_date_active,sysdate) ;
929
930
931 BEGIN
932
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934
935 --+
936 -- If p_msite_ids is null, then load all the ids and put it in x_msite_ids
937 --+
938 x_msite_ids := JTF_NUMBER_TABLE();
939 IF (p_msite_ids IS NULL) THEN
940 l_index := 1;
941 FOR r3 IN c3 LOOP
942 x_msite_ids.EXTEND();
943 x_msite_ids(l_index) := r3.msite_id;
944 l_index := l_index + 1;
945 END LOOP;
946 ELSE
947 -- x_msite_ids := p_msite_ids;
948 -- check the validation of the each msite_id
949 l_index := 1;
950 FOR i IN 1..p_msite_ids.COUNT LOOP
951 Open C_msite_valid(p_msite_ids(i));
952 Fetch C_msite_valid INTO l_root_section_id;
953 IF C_msite_valid%FOUND Then
954 x_msite_ids.EXTEND();
955 x_msite_ids(l_index) := p_msite_ids(i);
956 l_index := l_index + 1;
957 END IF;
958 Close C_msite_valid;
959 END LOOP;
960 END IF;
961
962
963 l_tmp_str := ' ';
964 l_first_idx := x_msite_ids.FIRST;
965
966 IF (x_msite_ids.COUNT <= 0) THEN
967 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MSITES_SPECIFIED');
968 FND_MSG_PUB.Add;
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971
972 Get_Master_Mini_Site_Id
973 (
974 x_mini_site_id => x_master_msite_id,
975 x_root_section_id => l_master_root_section_id
976 );
977
978 --+
979 -- Prepare the part of the sql query which does selection based on the input
980 --+
981 -- l_tmp_str := 'MM.msite_id IN (' || x_msite_ids(l_first_idx);
982
983 -- FOR i IN (l_first_idx+1)..x_msite_ids.LAST LOOP
984 -- If x_msite_ids.EXISTS(i) Then
985 -- l_tmp_str := l_tmp_str || ',' || x_msite_ids(i);
986 -- End If;
987 -- END LOOP; -- end loop i
988 -- l_tmp_str := l_tmp_str || ') ';
989
990 -- Basic information (from ibe_msites_b table)
991 -- OPEN x_minisite_cur FOR 'SELECT msite_id, default_language_code, '
992 -- || 'default_currency_code, default_org_id, walkin_allowed_flag, '
993 -- || 'msite_root_section_id, master_msite_flag, atp_check_flag, '
994 -- || 'default_date_format, profile_id, access_name, resp_access_flag, '
995 -- || 'party_access_code, attribute1, attribute2, attribute3, attribute4, '
996 -- || 'attribute5, attribute6, attribute7, attribute8, attribute9, '
997 -- || 'attribute10, attribute11, attribute12, attribute13, attribute14, '
998 -- || 'attribute15 '
999 -- || 'FROM ibe_msites_b MM WHERE '
1000 -- || l_tmp_str
1001 -- || 'ORDER BY msite_id';
1002
1003
1004 -- Basic information (from ibe_msites_b table)
1005 OPEN x_minisite_cur FOR SELECT msite_id, default_language_code,
1006 default_currency_code, default_org_id, walkin_allowed_flag,
1007 msite_root_section_id, master_msite_flag, atp_check_flag,
1008 default_date_format, profile_id, access_name, resp_access_flag,
1009 party_access_code, attribute1, attribute2, attribute3, attribute4,
1010 attribute5, attribute6, attribute7, attribute8, attribute9,
1011 attribute10, attribute11, attribute12, attribute13, attribute14,
1012 attribute15,
1013 payment_threshold_enable_flag, --added by YAXU for payment threshold
1014 start_date_active, end_date_active -- added by YAXU on 08/06/02
1015 FROM ibe_msites_b MM
1016 WHERE MM.msite_id IN
1017 (SELECT t.COLUMN_VALUE
1018 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1019 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125)
1020 ORDER BY msite_id ;
1021
1022 -- Mini-site name and description for all languages
1023 OPEN x_name_cur FOR SELECT /*+ ORDERED USE_NL (V MM TL L) INDEX (MM,IBE_MSITES_B_U1) */
1024 MM.msite_id, L.language_code, TL.msite_name,TL.msite_description
1025 FROM(select distinct to_number(t.column_value) as msite_id
1026 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1027 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1028 ibe_msites_b MM, ibe_msites_tl TL, ibe_msite_languages L
1029 WHERE L.language_code = TL.language
1030 AND L.msite_id = MM.msite_id
1031 AND TL.msite_id = MM.msite_id
1032 AND MM.msite_id = v.msite_id
1033 AND MM.site_type = 'I'
1034 ORDER BY MM.msite_id;
1035
1036 -- Language assocation(from ibe_msite_languages table)
1037 OPEN x_lang_cur FOR SELECT /*+ ORDERED
1038 USE_NL (V MM L)
1039 INDEX (MM,IBE_MSITES_B_U1) */
1040 MM.msite_id, L.language_code
1041 FROM
1042 (select distinct to_number(t.column_value) as msite_id
1043 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1044 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1045 ibe_msites_b MM,
1046 ibe_msite_languages L
1047 WHERE MM.msite_id = L.msite_id
1048 AND MM.msite_id = v.msite_id
1049 AND MM.site_type = 'I'
1050 AND L.enable_flag = 'Y'
1051 ORDER BY MM.msite_id;
1052
1053 -- Currency assocation(from ibe_msite_currencies table)
1054 OPEN x_currency_cur FOR SELECT /*+ ORDERED USE_NL (V MM C) INDEX (MM,IBE_MSITES_B_U1) */
1055 MM.msite_id, C.currency_code,
1056 C.bizpartner_prc_listid,C.registered_prc_listid, C.walkin_prc_listid,
1057 C.orderable_limit,
1058 C.payment_threshold,C.partner_prc_listid
1059 FROM (select distinct to_number(t.column_value) as msite_id
1060 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1061 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1062 ibe_msites_b MM, ibe_msite_currencies C
1063 WHERE MM.msite_id = C.msite_id
1064 AND MM.msite_id = v.msite_id
1065 AND MM.site_type = 'I'
1066 ORDER BY MM.msite_id;
1067
1068 -- Mini-site and responsibility association (from ibe_msite_resps_b, _tl)
1069 OPEN x_msite_resps_cur FOR SELECT /*+ ORDERED USE_NL (V MM MRB MRTL) INDEX (MM,IBE_MSITES_B_U1)*/
1070 MM.msite_id, MRB.msite_resp_id, MRB.responsibility_id, MRB.application_id, MRTL.language,
1071 MRTL.display_name, MRB.start_date_active, MRB.end_date_active
1072 FROM (select distinct to_number(t.column_value) as msite_id
1073 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1074 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1075 ibe_msites_b MM, ibe_msite_resps_b MRB, ibe_msite_resps_tl MRTL
1076 WHERE MM.msite_id = MRB.msite_id
1077 AND MRB.msite_resp_id = MRTL.msite_resp_id
1078 AND MM.msite_id = v.msite_id
1079 AND MM.site_type = 'I'
1080 ORDER BY MM.msite_id, MRB.msite_resp_id;
1081
1082 -- Mini-site and party association (from ibe_msite_prty_accss table)
1083 OPEN x_party_access_cur FOR SELECT /*+ ORDERED USE_NL (V MM MP) INDEX (MM,IBE_MSITES_B_U1) */
1084 MM.msite_id, party_id
1085 FROM (select distinct to_number(t.column_value) as msite_id
1086 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1087 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1088 ibe_msites_b MM, ibe_msite_prty_accss MP
1089 WHERE MM.msite_id = MP.msite_id
1090 AND sysdate BETWEEN MP.start_date_Active AND NVL(MP.end_date_active,sysdate)
1091 AND MM.msite_id = v.msite_id
1092 AND MM.site_type = 'I'
1093 ORDER BY MM.msite_id;
1094
1095 -- Payment method, credit card type and shipment method assocation
1096 -- (from ibe_msite_information table)
1097 OPEN x_pm_cc_sm_cur FOR SELECT /*+ ORDERED USE_NL(V MM MI) INDEX(MM,IBE_MSITES_B_U1) */
1098 MM.msite_id, MI.msite_information_context, MI.msite_information1,MI.msite_information2
1099 FROM(select distinct to_number(t.column_value) as msite_id
1100 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1101 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1102 ibe_msites_b MM, ibe_msite_information MI
1103 WHERE MM.msite_id = MI.msite_id
1104 AND MM.msite_id = v.msite_id
1105 AND MM.site_type = 'I'
1106 AND MI.msite_information_context = 'SHPMT_MTHD'
1107 UNION
1108 SELECT /*+ ORDERED USE_NL(V MM MI) INDEX(MM,IBE_MSITES_B_U1) */
1109 MM.msite_id, MI.msite_information_context,MI.msite_information1,MI.msite_information2
1110 FROM(select distinct to_number(t.column_value) as msite_id
1111 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1112 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1113 ibe_msites_b MM, ibe_msite_information MI, fnd_lookup_values FLV
1114 WHERE MM.msite_id = MI.msite_id
1115 AND MM.msite_id = v.msite_id
1116 AND MM.site_type = 'I'
1117 AND MI.msite_information1 = FLV.lookup_code
1118 AND FLV.lookup_type = 'IBE_PAYMENT_TYPE'
1119 AND FLV.enabled_flag = 'Y' AND (FLV.TAG='Y' or FLV.TAG is null)
1120 AND FLV.language=userenv('lang')
1121
1122 UNION --ssekar bug 5064210 query split to handle multiple sources of payment/credit card info.
1123 SELECT /*+ ORDERED USE_NL(V MM MI) INDEX(MM,IBE_MSITES_B_U1) */
1124 MM.msite_id, MI.msite_information_context,MI.msite_information1,MI.msite_information2
1125 FROM(select distinct to_number(t.column_value) as msite_id
1126 FROM TABLE(CAST(x_msite_ids AS JTF_NUMBER_TABLE)) t
1127 WHERE t.COLUMN_VALUE > 0 AND t.COLUMN_VALUE < 9.99E125) v,
1128 ibe_msites_b MM, ibe_msite_information MI, iby_creditcard_issuers_b cci
1129 WHERE MM.msite_id = MI.msite_id
1130 AND MM.msite_id = v.msite_id
1131 AND MM.site_type = 'I'
1132 AND (MI.msite_information1 = cci.CARD_ISSUER_CODE)
1133 ORDER BY 1, 2;
1134
1135
1136 --+
1137 -- Get profile for catalog exclusions
1138 --+
1139 l_exclude_flag :=
1140 FND_PROFILE.Value_Specific('IBE_USE_CATALOG_EXCLUSIONS', null, null,
1141 l_application_id);
1142
1143 IF (l_exclude_flag IS NULL) THEN
1144 l_exclude_flag := 'N';
1145 END IF;
1146
1147 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1148 IBE_Util.Debug('Get_Msite_Details - Set l_exclude_flag to N to prevent loading excluded items and excluded sections now');
1149 END IF;
1150 l_exclude_flag := 'N'; -- Bug 7676477, scnagara
1151 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1152 IBE_Util.Debug('Get_Msite_Details - l_exclude_flag = ' || l_exclude_flag);
1153 END IF;
1154
1155 -- Initialize the variables
1156 x_section_msite_ids := JTF_NUMBER_TABLE();
1157 x_section_ids := JTF_NUMBER_TABLE();
1158 x_item_msite_ids := JTF_NUMBER_TABLE();
1159 x_item_ids := JTF_NUMBER_TABLE();
1160
1161 IF (l_exclude_flag = 'Y') THEN
1162
1163 -- For sections
1164 l_index := 1;
1165 FOR i IN 1..x_msite_ids.COUNT LOOP
1166
1167 FOR r1 IN c1(x_master_msite_id, x_msite_ids(i)) LOOP
1168 x_section_msite_ids.EXTEND();
1169 x_section_ids.EXTEND();
1170 x_section_msite_ids(l_index) := x_msite_ids(i);
1171 x_section_ids(l_index) := r1.child_section_id;
1172 l_index := l_index + 1;
1173 END LOOP; -- end loop r1
1174
1175 END LOOP; -- end loop i
1176
1177 -- For items
1178 l_index := 1;
1179 FOR i IN 1..x_msite_ids.COUNT LOOP
1180 OPEN C_msite_valid(x_msite_ids(i));
1181 FETCH C_msite_valid INTO l_root_section_id;
1182 CLOSE C_msite_valid;
1183 FOR r2 IN c2(x_master_msite_id, x_msite_ids(i),l_root_section_id) LOOP
1184 x_item_msite_ids.EXTEND();
1185 x_item_ids.EXTEND();
1186 x_item_msite_ids(l_index) := x_msite_ids(i);
1187 x_item_ids(l_index) := r2.inventory_item_id;
1188 l_index := l_index + 1;
1189 END LOOP; -- end loop r2
1190
1191 END LOOP; -- end loop i
1192
1193 END IF; -- end of exclusion block
1194
1195 EXCEPTION
1196
1197 WHEN FND_API.G_EXC_ERROR THEN
1198 x_return_status := FND_API.G_RET_STS_ERROR;
1199 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1200 p_count => x_msg_count,
1201 p_data => x_msg_data);
1202
1203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1206 p_count => x_msg_count,
1207 p_data => x_msg_data);
1208
1209 WHEN OTHERS THEN
1210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1211 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1212 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1213 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1214 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1215 FND_MSG_PUB.Add;
1216 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1217 p_count => x_msg_count,
1218 p_data => x_msg_data);
1219
1220 End Load_Msite_List_Details ;
1221
1222 END IBE_MinisiteRuntime_PVT;