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