1 PACKAGE BODY JTF_MinisiteRuntime_PVT AS
2 /* $Header: JTFVMSRB.pls 115.12 2004/07/09 18:51:54 applrt ship $ */
3
4 -- PACKAGE
5 -- JTF_MinisiteRuntime_PVT
6 --
7 -- PROCEDURES
8 -- get_minisite_details
9 -- HISTORY
10 -- 11/19/99 drao Created
11 -- ************************************************************************
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'JTF_MINISITERUNTIME_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'JTFVMSRB.pls';
15
16 --+
17 -- Get master mini site id for the store
18 --+
19 PROCEDURE Get_Master_Mini_Site_Id
20 (
21 x_mini_site_id OUT NUMBER,
22 x_root_section_id OUT NUMBER
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) :=
26 'Get_Master_Mini_Site_Id';
27 l_api_version CONSTANT NUMBER := 1.0;
28
29 CURSOR c1 IS
30 SELECT msite_id, msite_root_section_id FROM jtf_msites_b
31 WHERE UPPER(master_msite_flag) = 'Y';
32 BEGIN
33
34 OPEN c1;
35 FETCH c1 INTO x_mini_site_id, x_root_section_id;
36 IF (c1%NOTFOUND) THEN
37 CLOSE c1;
38 RAISE FND_API.G_EXC_ERROR;
39 END IF;
40 CLOSE c1;
41
42 END Get_Master_Mini_Site_Id;
43
44 PROCEDURE Get_Msite_Details
45 (
46 p_api_version IN NUMBER,
47 p_msite_id IN NUMBER,
48 p_access_name IN VARCHAR2,
49 x_master_msite_id OUT NUMBER,
50 x_minisite_cur OUT minisite_cur_type,
51 x_lang_cur OUT lang_cur_type,
52 x_currency_cur OUT currency_cur_type,
53 x_sections_cur OUT sections_cur_type,
54 x_items_cur OUT items_cur_type,
55 x_name_cur OUT name_cur_type ,
56 x_msite_resps_cur OUT msite_resp_cur_type ,
57 x_party_access_cur OUT msite_prty_access_cur_type,
58 x_return_status OUT VARCHAR2,
59 x_msg_count OUT NUMBER,
60 x_msg_data OUT VARCHAR2
61 )
62 IS
63 l_api_name CONSTANT VARCHAR2(30) :=
64 'Get_Msite_Details';
65 l_api_version CONSTANT NUMBER := 1.0;
66
67 l_root_section_id NUMBER;
68 l_application_id NUMBER := 671;
69 l_exclude_flag VARCHAR2(10);
70 l_msite_id NUMBER;
71 l_master_root_section_id NUMBER;
72 l_msite_not_exists_excp EXCEPTION;
73
74 Cursor C_msite_Id(p_access_name Varchar2)
75 Is Select msite_id,msite_root_section_id
76 From jtf_msites_b
77 Where access_name = p_access_name
78 -- And store_id > 0
79 And master_msite_flag = 'N'
80 And sysdate BETWEEN start_date_active
81 And NVL(end_date_active,sysdate) ;
82
83 -- This cursor is used for checking whether a given msite_id is+
84 -- valid and enabled for store. Also it is used to fetch the+
85 -- root section id for a given minisite.+
86
87 Cursor C_msite_valid(l_c_msite_id Number)
88 Is Select msite_root_section_id
89 From jtf_msites_b
90 Where msite_id = l_c_msite_id
91 -- And store_id > 0
92 And master_msite_flag = 'N'
93 And sysdate BETWEEN start_date_active
94 And NVL(end_date_active,sysdate) ;
95
96 BEGIN
97
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 SAVEPOINT get_msite_details;
101 l_msite_id := p_msite_id ;
102 If (l_msite_id IS NULL OR l_msite_id = FND_API.G_MISS_NUM) And
103 (p_access_name IS NOT NULL) Then
104 Open C_msite_Id(p_access_name);
105 Fetch C_msite_Id INTO l_msite_id, l_root_section_id;
106 If C_msite_Id%NOTFOUND Then
107 Close C_msite_Id;
108 RAISE l_msite_not_exists_excp;
109 End If;
110 Close C_msite_Id;
111 Else
112 Open C_msite_valid(l_msite_id);
113 Fetch C_msite_valid INTO l_root_section_id;
114 If C_msite_valid%NOTFOUND Then
115 Close C_msite_valid;
116 RAISE l_msite_not_exists_excp;
117 End If;
118 Close C_msite_valid;
119 End If;
120
121 --+
122 -- Get the master mini-site ID
123 --+
124 Get_Master_Mini_Site_Id
125 (
126 x_mini_site_id => x_master_msite_id,
127 x_root_section_id => l_master_root_section_id
128 );
129
130 --added 3 new fields to the select stmt
131 -- access_name, resp_access_flag, party_access_code
132 OPEN x_minisite_cur FOR SELECT msite_id,default_language_code,
133 default_currency_code,default_org_id,
134 walkin_allowed_flag,msite_root_section_id,
135 master_msite_flag,atp_check_flag ,
136 default_date_format,profile_id,
137 access_name, resp_access_flag, party_access_code,
138 attribute1,attribute2,attribute3,attribute4,
139 attribute5,attribute6,attribute7,attribute8,
140 attribute9,attribute10,attribute11,attribute12,
141 attribute13,attribute14,attribute15
142 FROM jtf_msites_b
143 WHERE msite_id = l_msite_id;
144
145 OPEN x_name_cur FOR SELECT a.language_code,
146 b.msite_name, b.msite_description
147 FROM jtf_msite_languages a, jtf_msites_tl b
148 WHERE a.language_code = b.language
149 AND b.msite_id = a.msite_id
150 AND b.msite_id = l_msite_id;
151
152 OPEN x_lang_cur FOR SELECT language_code from jtf_msite_languages l
153 WHERE l.msite_id = l_msite_id;
154
155 OPEN x_currency_cur FOR SELECT currency_code, bizpartner_prc_listid,
156 registered_prc_listid, walkin_prc_listid, orderable_limit
157 FROM jtf_msite_currencies c
158 WHERE c.msite_id = l_msite_id;
159
160 l_exclude_flag :=
161 FND_PROFILE.Value_Specific('IBE_USE_CATALOG_EXCLUSIONS',null,null,
162 l_application_id);
163
164 IF (l_exclude_flag IS NULL) THEN
165 l_exclude_flag := 'N';
166 END IF;
167
168 IF (l_exclude_flag = 'Y')
169 THEN
170
171 OPEN x_sections_cur FOR 'SELECT child_section_id '
172 || 'FROM jtf_dsp_msite_sct_sects '
173 || 'WHERE mini_site_id = :master_mini_site_id AND '
174 || 'sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate) '
175 || 'AND child_section_id NOT IN '
176 || '(SELECT child_section_id FROM jtf_dsp_msite_sct_sects '
177 || 'WHERE mini_site_id = :msite_id) '
178 || 'START WITH child_section_id = :root_section_id '
179 || 'AND mini_site_id = :master_mini_site_id '
180 || 'CONNECT BY PRIOR child_section_id = parent_section_id '
181 || 'AND mini_site_id = :master_mini_site_id '
182 || 'AND PRIOR mini_site_id = :master_mini_site_id '
183 USING x_master_msite_id, l_msite_id, l_root_section_id,
184 x_master_msite_id, x_master_msite_id, x_master_msite_id;
185
186 OPEN x_items_cur FOR 'SELECT inventory_item_id '
187 || 'FROM jtf_dsp_section_items '
188 || 'WHERE section_item_id IN '
189 || '(SELECT section_item_id FROM jtf_dsp_section_items '
190 || 'WHERE section_id IN '
191 || '(SELECT child_section_id FROM jtf_dsp_msite_sct_sects '
192 || 'WHERE mini_site_id = :master_mini_site_id '
193 || 'AND child_section_id NOT IN '
194 || '(SELECT child_section_id FROM jtf_dsp_msite_sct_sects '
195 || 'WHERE mini_site_id = :msite_id) '
196 || 'START WITH child_section_id = :root_section_id '
197 || 'AND mini_site_id = :master_mini_site_id '
198 || 'CONNECT BY PRIOR child_section_id = parent_section_id '
199 || 'AND PRIOR mini_site_id = :master_mini_site_id '
200 || 'AND mini_site_id = :master_mini_site_id) '
201 || 'OR (section_id IN '
202 || '(SELECT child_section_id FROM jtf_dsp_msite_sct_sects '
203 || 'WHERE mini_site_id = :msite_id) '
204 || 'AND section_item_id NOT IN '
205 || '(SELECT section_item_id FROM jtf_dsp_msite_sct_items '
206 || 'WHERE mini_site_id = :msite_id))) '
207 USING x_master_msite_id, l_msite_id, l_root_section_id,
208 x_master_msite_id, x_master_msite_id, x_master_msite_id,
209 l_msite_id, l_msite_id;
210
211 ELSE
212
213 OPEN x_items_cur FOR select 0 from dual where sysdate < sysdate - 1;
214 OPEN x_sections_cur FOR select 0 from dual where sysdate < sysdate - 1;
215
216 END IF; -- end of exclusion block
217
218 -- added to cache the minisite responsibility association -- ssridhar
219 OPEN x_msite_resps_cur FOR Select respb.msite_resp_id,
220 respb.responsibility_id ,
221 respb.application_id,
222 respt.language,
223 respt.display_name
224 From jtf_msite_resps_b respb ,
225 jtf_msite_resps_tl respt ,
226 jtf_msite_languages lang
227 Where respb.msite_id = l_msite_id
228 And respb.msite_resp_id = respt.msite_resp_id
229 And lang.msite_id = respb.msite_id
230 And lang.language_code = respt.language
231 And sysdate Between respb.start_date_Active
232 And NVL(respb.end_date_active,sysdate)
233 ORDER BY respb.msite_resp_id;
234
235 -- added to cache the minisite party access information -- ssridhar
236 Open x_party_access_cur FOR Select Party_id
237 From jtf_msite_prty_accss accss
238 Where accss.msite_id = l_msite_id
239 And sysdate Between accss.start_date_Active
240 And NVL(accss.end_date_active,sysdate);
241
242 EXCEPTION
243 WHEN FND_API.G_EXC_ERROR THEN
244 ROLLBACK TO get_msite_details;
245 x_return_status := FND_API.g_ret_sts_error;
246 FND_MSG_PUB.count_and_get(
247 p_encoded => FND_API.g_false,
248 p_count => x_msg_count,
249 p_data => x_msg_data );
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 ROLLBACK TO get_msite_details;
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 FND_MSG_PUB.count_and_get(
254 p_encoded => FND_API.g_false,
255 p_count => x_msg_count,
256 p_data => x_msg_data );
257 WHEN l_msite_not_exists_excp THEN
258 ROLLBACK TO get_msite_details;
259 x_return_status := FND_API.g_ret_sts_error;
260 FND_MESSAGE.set_name('JTF','JTF_MSITE_NOT_EXISTS');
261 FND_MSG_PUB.ADD;
262 FND_MSG_PUB.count_and_get(
263 p_encoded => FND_API.g_false,
264 p_count => x_msg_count,
265 p_data => x_msg_data );
266 WHEN OTHERS THEN
267 ROLLBACK TO get_msite_details;
268 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
269 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
270 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
271 FND_MESSAGE.Set_Token('REASON', SQLERRM);
272 FND_MSG_PUB.Add;
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274 FND_MSG_PUB.count_and_get(
275 p_encoded => FND_API.g_false,
276 p_count => x_msg_count,
277 p_data => x_msg_data );
278
279 END Get_Msite_Details;
280
281 --+
282 -- This procedure assumes that the list of mini-site IDs passed in will be
283 -- the active ones, that is, with store_id > 0 and with valid start_date_active
284 -- and end_date_active.
285 -- If p_msite_ids is NULL, then load all the mini-sites which are enabled
286 -- for store, and put the IDs back in x_msite_ids. If p_msite_ids IS NOT NULL,
287 -- then don't put IDs back in the x_msite_ids
288 --+
289 PROCEDURE Load_Msite_List_Details
290 (
291 p_api_version IN NUMBER,
292 p_msite_ids IN JTF_NUMBER_TABLE,
293 x_msite_ids OUT JTF_NUMBER_TABLE,
294 x_master_msite_id OUT NUMBER,
295 x_minisite_cur OUT MINISITE_CUR_TYPE,
296 x_name_cur OUT NAME_CUR_TYPE,
297 x_lang_cur OUT LANG_CUR_TYPE,
298 x_currency_cur OUT CURRENCY_CUR_TYPE,
299 x_msite_resps_cur OUT MSITE_RESP_CUR_TYPE,
300 x_party_access_cur OUT MSITE_PRTY_ACCESS_CUR_TYPE,
301 x_section_msite_ids OUT JTF_NUMBER_TABLE,
302 x_section_ids OUT JTF_NUMBER_TABLE,
303 x_item_msite_ids OUT JTF_NUMBER_TABLE,
304 x_item_ids OUT JTF_NUMBER_TABLE,
305 x_return_status OUT VARCHAR2,
306 x_msg_count OUT NUMBER,
307 x_msg_data OUT VARCHAR2
308 )
309 IS
310 l_api_name CONSTANT VARCHAR2(30) :=
311 'Load_Msite_List_Details';
312 l_api_version CONSTANT NUMBER := 1.0;
313
314 l_master_root_section_id NUMBER;
315 l_application_id NUMBER := 671;
316 l_exclude_flag VARCHAR2(10);
317 l_tmp_str VARCHAR2(4000);
318 l_first_idx BINARY_INTEGER;
319 l_index BINARY_INTEGER;
320
321 CURSOR c1(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER)
322 IS SELECT child_section_id
323 FROM jtf_dsp_msite_sct_sects
324 WHERE mini_site_id = l_c_master_msite_id AND
325 sysdate BETWEEN start_date_active AND NVL(end_date_active,sysdate)
326 AND child_section_id NOT IN
327 (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
328 WHERE mini_site_id = l_c_msite_id)
329 START WITH child_section_id =
330 (SELECT msite_root_section_id FROM jtf_msites_b
331 WHERE msite_id = l_c_msite_id)
332 AND mini_site_id = l_c_master_msite_id
333 CONNECT BY PRIOR child_section_id = parent_section_id
334 AND mini_site_id = l_c_master_msite_id
335 AND PRIOR mini_site_id = l_c_master_msite_id;
336
337 -- Description of the SQL
338 -- IS SELECT inventory_item_id
339 -- FROM jtf_dsp_section_items
340 -- WHERE section_item_id IN
341 ---- Get all the section items for the excluded sections for the mini-site
342 ---- starting from mini-site's root section
343 -- (SELECT section_item_id FROM jtf_dsp_section_items
344 -- WHERE section_id IN
345 -- (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
346 -- WHERE mini_site_id = l_c_master_msite_id
347 -- AND child_section_id NOT IN
348 -- (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
349 -- WHERE mini_site_id = l_c_msite_id)
350 -- START WITH child_section_id =
351 -- (SELECT msite_root_section_id FROM jtf_msites_b
352 -- WHERE msite_id = l_c_msite_id)
353 -- AND mini_site_id = l_c_master_msite_id
354 -- CONNECT BY PRIOR child_section_id = parent_section_id
355 -- AND PRIOR mini_site_id = l_c_master_msite_id
356 -- AND mini_site_id = l_c_master_msite_id)
357 ---- Get all the section items for the included sections for the mini-site
358 ---- starting from mini-site's root section, but the section-items themselves
359 ---- are excluded.
360 -- OR (section_id IN
361 -- (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
362 -- WHERE mini_site_id = l_c_msite_id)
363 -- AND section_item_id NOT IN
364 -- (SELECT section_item_id FROM jtf_dsp_msite_sct_items
365 -- WHERE mini_site_id = l_c_msite_id)));
366
367 CURSOR c2(l_c_master_msite_id IN NUMBER, l_c_msite_id IN NUMBER)
368 IS SELECT inventory_item_id
369 FROM jtf_dsp_section_items
370 WHERE section_item_id IN
371 (SELECT section_item_id FROM jtf_dsp_section_items
372 WHERE section_id IN
373 (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
374 WHERE mini_site_id = l_c_master_msite_id
375 AND child_section_id NOT IN
376 (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
377 WHERE mini_site_id = l_c_msite_id)
378 START WITH child_section_id =
379 (SELECT msite_root_section_id FROM jtf_msites_b
380 WHERE msite_id = l_c_msite_id)
381 AND mini_site_id = l_c_master_msite_id
382 CONNECT BY PRIOR child_section_id = parent_section_id
383 AND PRIOR mini_site_id = l_c_master_msite_id
384 AND mini_site_id = l_c_master_msite_id)
385 OR (section_id IN
386 (SELECT child_section_id FROM jtf_dsp_msite_sct_sects
387 WHERE mini_site_id = l_c_msite_id)
388 AND section_item_id NOT IN
389 (SELECT section_item_id FROM jtf_dsp_msite_sct_items
390 WHERE mini_site_id = l_c_msite_id)));
391
392 CURSOR c3
393 IS SELECT msite_id FROM jtf_msites_b
394 WHERE sysdate BETWEEN start_date_active AND NVL(end_date_active, sysdate)
395 AND master_msite_flag = 'N';
396
397 BEGIN
398
399 x_return_status := FND_API.G_RET_STS_SUCCESS;
400
401 --+
402 -- If p_msite_ids is null, then load all the ids and put it in x_msite_ids
403 --+
404 x_msite_ids := JTF_NUMBER_TABLE();
405 IF (p_msite_ids IS NULL) THEN
406 l_index := 1;
407 FOR r3 IN c3 LOOP
408 x_msite_ids.EXTEND();
409 x_msite_ids(l_index) := r3.msite_id;
410 l_index := l_index + 1;
411 END LOOP;
412 ELSE
413 x_msite_ids := p_msite_ids;
414 END IF;
415
416 l_tmp_str := ' ';
417 l_first_idx := x_msite_ids.FIRST;
418
419 IF (x_msite_ids.COUNT <= 0) THEN
420 FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_NO_MSITES_SPECIFIED');
421 FND_MSG_PUB.Add;
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425 Get_Master_Mini_Site_Id
426 (
427 x_mini_site_id => x_master_msite_id,
428 x_root_section_id => l_master_root_section_id
429 );
430
431 --+
432 -- Prepare the part of the sql query which does selection based on the input
433 --+
434 l_tmp_str := 'MM.msite_id IN (' || x_msite_ids(l_first_idx);
435
436 FOR i IN (l_first_idx+1)..x_msite_ids.LAST LOOP
437 If x_msite_ids.EXISTS(i) Then
438 l_tmp_str := l_tmp_str || ',' || x_msite_ids(i);
439 End If;
440 END LOOP; -- end loop i
441 l_tmp_str := l_tmp_str || ') ';
442
443 -- Basic information (from jtf_msites_b table)
444 OPEN x_minisite_cur FOR 'SELECT msite_id, default_language_code, '
445 || 'default_currency_code, default_org_id, walkin_allowed_flag, '
446 || 'msite_root_section_id, master_msite_flag, atp_check_flag, '
447 || 'default_date_format, profile_id, access_name, resp_access_flag, '
448 || 'party_access_code, attribute1, attribute2, attribute3, attribute4, '
449 || 'attribute5, attribute6, attribute7, attribute8, attribute9, '
450 || 'attribute10, attribute11, attribute12, attribute13, attribute14, '
451 || 'attribute15 '
452 || 'FROM jtf_msites_b MM WHERE '
453 || l_tmp_str
454 || 'ORDER BY msite_id';
455
456 -- Mini-site name and description for all languages
457 OPEN x_name_cur FOR 'SELECT MM.msite_id, L.language_code, TL.msite_name, '
458 || 'TL.msite_description '
459 || 'FROM jtf_msite_languages L, jtf_msites_tl TL, jtf_msites_b MM WHERE '
460 || 'L.language_code = TL.language AND '
461 || 'L.msite_id = MM.msite_id AND '
462 || 'TL.msite_id = MM.msite_id AND '
463 || l_tmp_str
464 || 'ORDER BY MM.msite_id';
465
466 -- Language assocation(from jtf_msite_languages table)
467 OPEN x_lang_cur FOR 'SELECT MM.msite_id, L.language_code '
468 || 'FROM jtf_msite_languages L, jtf_msites_b MM WHERE '
469 || 'MM.msite_id = L.msite_id AND '
470 || l_tmp_str
471 || 'ORDER BY MM.msite_id';
472
473 -- Currency assocation(from jtf_msite_currencies table)
474 OPEN x_currency_cur FOR 'SELECT MM.msite_id, C.currency_code, '
475 || 'C.bizpartner_prc_listid,C.registered_prc_listid, C.walkin_prc_listid, '
476 || 'C.orderable_limit '
477 || 'FROM jtf_msite_currencies C, jtf_msites_b MM WHERE '
478 || 'MM.msite_id = C.msite_id AND '
479 || l_tmp_str
480 || 'ORDER BY MM.msite_id';
481
482 -- Mini-site and responsibility association (from jtf_msite_resps_b, _tl)
483 OPEN x_msite_resps_cur FOR 'SELECT MM.msite_id, MRB.msite_resp_id, '
484 || 'MRB.responsibility_id, MRB.application_id, MRTL.language, '
485 || 'MRTL.display_name '
486 || 'FROM jtf_msite_resps_b MRB, jtf_msite_resps_tl MRTL, '
487 || 'jtf_msites_b MM WHERE '
488 || 'MM.msite_id = MRB.msite_id AND '
489 || 'MRB.msite_resp_id = MRTL.msite_resp_id AND '
490 || 'sysdate BETWEEN MRB.start_date_active AND NVL(MRB.end_date_active,sysdate) AND '
491 || l_tmp_str
492 || 'ORDER BY MM.msite_id, MRB.msite_resp_id';
493
494 -- Mini-site and party association (from jtf_msite_prty_accss table)
495 OPEN x_party_access_cur FOR 'SELECT MM.msite_id, party_id '
496 || 'FROM jtf_msite_prty_accss MP, jtf_msites_b MM WHERE '
497 || 'MM.msite_id = MP.msite_id AND '
498 || 'sysdate BETWEEN MP.start_date_Active AND NVL(MP.end_date_active,sysdate) AND '
499 || l_tmp_str
500 || 'ORDER BY MM.msite_id';
501
502 --+
503 -- Get profile for catalog exclusions
504 --+
505 l_exclude_flag :=
506 FND_PROFILE.Value_Specific('IBE_USE_CATALOG_EXCLUSIONS', null, null,
507 l_application_id);
508
509 IF (l_exclude_flag IS NULL) THEN
510 l_exclude_flag := 'N';
511 END IF;
512
513 -- Initialize the variables
514 x_section_msite_ids := JTF_NUMBER_TABLE();
515 x_section_ids := JTF_NUMBER_TABLE();
516 x_item_msite_ids := JTF_NUMBER_TABLE();
517 x_item_ids := JTF_NUMBER_TABLE();
518
519 IF (l_exclude_flag = 'Y') THEN
520
521 -- For sections
522 l_index := 1;
523 FOR i IN 1..x_msite_ids.COUNT LOOP
524
525 FOR r1 IN c1(x_master_msite_id, x_msite_ids(i)) LOOP
526 x_section_msite_ids.EXTEND();
527 x_section_ids.EXTEND();
528 x_section_msite_ids(l_index) := x_msite_ids(i);
529 x_section_ids(l_index) := r1.child_section_id;
530 l_index := l_index + 1;
531 END LOOP; -- end loop r1
532
533 END LOOP; -- end loop i
534
535 -- For items
536 l_index := 1;
537 FOR i IN 1..x_msite_ids.COUNT LOOP
538
539 FOR r2 IN c2(x_master_msite_id, x_msite_ids(i)) LOOP
540 x_item_msite_ids.EXTEND();
541 x_item_ids.EXTEND();
542 x_item_msite_ids(l_index) := x_msite_ids(i);
543 x_item_ids(l_index) := r2.inventory_item_id;
544 l_index := l_index + 1;
545 END LOOP; -- end loop r2
546
547 END LOOP; -- end loop i
548
549 END IF; -- end of exclusion block
550
551 EXCEPTION
552
553 WHEN FND_API.G_EXC_ERROR THEN
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
556 p_count => x_msg_count,
557 p_data => x_msg_data);
558
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
562 p_count => x_msg_count,
563 p_data => x_msg_data);
564
565 WHEN OTHERS THEN
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
568 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
569 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
570 FND_MESSAGE.Set_Token('REASON', SQLERRM);
571 FND_MSG_PUB.Add;
572 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
573 p_count => x_msg_count,
574 p_data => x_msg_data);
575
576 End Load_Msite_List_Details ;
577
578 END JTF_MinisiteRuntime_PVT;
[Home] [Help]