1 PACKAGE BODY IBE_DSP_HIERARCHY_QUERY_PVT AS
2 /* $Header: IBEVCHQB.pls 120.3 2008/01/10 06:32:58 amaheshw ship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- IBE_DSP_HIERARCHY_QUERY_PVT
10 --
11 -- PURPOSE
12 -- Private API for saving, retrieving and updating sections.
13 --
14 -- NOTES
15 -- This is a pulicly accessible pacakge.
16 --
17 --
18 -- HISTORY
19 -- 11/28/99 VPALAIYA Created
20 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) and Debug (Bug # 2691710) Changes.
21 -- 12/19/02 SCHAK Modified for reverting Debug (IBEUtil) Changes.
22 -- 08/16/03 abhandar added procedure load_section_hierarchy() (bug ##3090284)
23 -- 12/13/05 madesai bugfix - 4864940 Modified query in
24 -- Load_SectionItems_For_Section to get item
25 -- based on profile "IBE:Item Validation for
26 -- Organization
27 -- 10.Jan.08 amaheshw bug 6712124. check end date for sections
28
29 -- **********************************************************************************************************
30
31 G_PKG_NAME CONSTANT VARCHAR2(30):='IBE_DSP_HIERAHCY_QUERY_PVT';
32 G_FILE_NAME CONSTANT VARCHAR2(12):='IBEVCHQB.pls';
33
34 FUNCTION Get_Display_Name
35 (
36 p_object_type IN VARCHAR2,
37 p_object_id IN NUMBER
38 ) RETURN VARCHAR2
39 IS
40 TYPE section_path_csr_type IS REF CURSOR;
41 l_section_path_csr section_path_csr_type;
42 l_section_id NUMBER;
43 l_section_disp_name VARCHAR2(120);
44 l_master_msite_id NUMBER;
45 l_display_name VARCHAR2(240);
46
47 BEGIN
48
49 IF p_object_type = 'S' THEN
50
51 -- Get the master minisite id
52 SELECT JMB.msite_id
53 INTO l_master_msite_id
54 FROM ibe_msites_b JMB
55 WHERE JMB.master_msite_flag = 'Y' and
56 JMB.site_type = 'I';
57
58 -- Open a cursor that retrieves the sections path from the root section
59 -- to p_object_id's immediate parent, in the reverse order
60 OPEN l_section_path_csr FOR
61 'SELECT JDMSS.parent_section_id ' ||
62 'FROM ibe_dsp_msite_sct_sects JDMSS ' ||
63 'START WITH JDMSS.child_section_id = :section_id ' ||
64 'AND JDMSS.mini_site_id = :master_mini_site_id1 ' ||
65 'CONNECT BY JDMSS.child_section_id = PRIOR JDMSS.parent_section_id ' ||
66 'AND JDMSS.mini_site_id = :master_mini_site_id2 ' ||
67 'AND JDMSS.parent_section_id IS NOT NULL'
68 USING p_object_id, l_master_msite_id, l_master_msite_id;
69
70 -- Loop through the cursor constructing the section path string
71 LOOP
72 FETCH l_section_path_csr INTO l_section_id;
73 EXIT WHEN l_section_path_csr%NOTFOUND;
74
75 SELECT JDSV.display_name
76 INTO l_section_disp_name
77 FROM ibe_dsp_sections_vl JDSV
78 WHERE JDSV.section_id = l_section_id;
79
80 l_display_name := l_section_disp_name || '/' || l_display_name;
81
82 END LOOP;
83
84 CLOSE l_section_path_csr;
85
86 SELECT JDSV.display_name
87 INTO l_section_disp_name
88 FROM ibe_dsp_sections_vl JDSV
89 WHERE JDSV.section_id = p_object_id;
90
91 l_display_name := l_display_name || l_section_disp_name;
92
93 END IF;
94
95 RETURN l_display_name;
96
97 END Get_Display_Name;
98
99 --
100 -- Return data (association + item data + section data) belonging to
101 -- the section p_section_id
102 --
103 PROCEDURE Load_SectionItems_For_Section
104 (
105 p_api_version IN NUMBER,
106 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
107 p_commit IN VARCHAR2 := FND_API.G_FALSE,
108 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
109 p_section_id IN NUMBER,
110 x_section_csr OUT NOCOPY SECTION_CSR,
111 x_sectionitem_item_csr OUT NOCOPY SECTIONITEM_ITEM_CSR,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2
115 )
116 IS
117 l_api_name CONSTANT VARCHAR2(30) :=
118 'Load_SectionItems_For_Section';
119 l_api_version CONSTANT NUMBER := 1.0;
120 l_organization_id_str VARCHAR2(30);
121 l_organization_id NUMBER;
122 BEGIN
123
124 -- Initialize message list if p_init_msg_list is set to TRUE.
125 IF FND_API.to_Boolean(p_init_msg_list) THEN
126 FND_MSG_PUB.initialize;
127 END IF;
128
129 -- Initialize API return status to success
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131
132 -- Get the section data
133 OPEN x_section_csr FOR SELECT display_name FROM ibe_dsp_sections_vl
134 WHERE section_id = p_section_id;
135 /* bug fix 4864940 */
136 l_organization_id_str
137 := FND_PROFILE.VALUE_SPECIFIC('IBE_ITEM_VALIDATION_ORGANIZATION',null,null,671);
138 IF (l_organization_id_str IS NULL) THEN
139 RAISE FND_API.G_EXC_ERROR;
140 ELSE
141 l_organization_id := to_number(l_organization_id_str);
142 END IF;
143
144 -- Get the section-item data and item data
145 OPEN x_sectionitem_item_csr FOR SELECT MTL.inventory_item_id,
146 MTL.organization_id, MTL.concatenated_segments, MTL.description,
147 MTL.web_status, L.meaning, SI.section_item_id, SI.object_version_number,
148 SI.start_date_active, SI.end_date_active, SI.sort_order
149 FROM ibe_dsp_section_items SI, mtl_system_items_vl MTL, fnd_lookups L
150 WHERE SI.section_id = p_section_id
151 AND SI.inventory_item_id = MTL.inventory_item_id
152 AND SI.organization_id = MTL.organization_id
153 AND L.lookup_type = 'IBE_ITEM_STATUS'
154 AND L.lookup_code = MTL.web_status
155 AND SI.organization_id = l_organization_id /*bug 4864940 */
156 order by SI.sort_order;
157 --order by added by abhandar 05/30/2002
158
159
160 EXCEPTION
161
162 WHEN FND_API.G_EXC_ERROR THEN
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
165 p_data => x_msg_data,
166 p_encoded => 'F');
167
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
171 p_data => x_msg_data,
172 p_encoded => 'F');
173
174 WHEN OTHERS THEN
175 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
176 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
177 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
178 FND_MESSAGE.Set_Token('REASON', SQLERRM);
179 FND_MSG_PUB.Add;
180
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182
183 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
184 THEN
185 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
186 END IF;
187
188 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
189 p_data => x_msg_data,
190 p_encoded => 'F');
191
192 END Load_SectionItems_For_Section;
193
194 --
195 -- Return data (association + item data + section data) belonging to
196 -- the item (p_inventory_item_id, p_organization_id)
197 -- Note: No item data/cursor returned
198 --
199 PROCEDURE Load_SectionItems_For_Item
200 (
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
203 p_commit IN VARCHAR2 := FND_API.G_FALSE,
204 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
205 p_inventory_item_id IN NUMBER,
206 p_organization_id IN NUMBER,
207 x_sectionitem_section_csr OUT NOCOPY SECTIONITEM_SECTION_CSR,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2
211 )
212 IS
213 l_api_name CONSTANT VARCHAR2(30) :=
214 'Load_SectionItems_For_Item';
215 l_api_version CONSTANT NUMBER := 1.0;
216 BEGIN
217
218 -- Initialize message list if p_init_msg_list is set to TRUE.
219 IF FND_API.to_Boolean(p_init_msg_list) THEN
220 FND_MSG_PUB.initialize;
221 END IF;
222
223 -- Initialize API return status to success
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 -- Get the section-item data and section data
227 OPEN x_sectionitem_section_csr FOR SELECT S.section_id, S.display_name,
228 SI.section_item_id, SI.object_version_number, SI.start_date_active,
229 SI.end_date_active, SI.sort_order
230 FROM ibe_dsp_sections_vl S, ibe_dsp_section_items SI
231 WHERE SI.inventory_item_id = p_inventory_item_id
232 AND SI.organization_id = p_organization_id
233 AND SI.section_id = S.section_id;
234
235 EXCEPTION
236
237 WHEN FND_API.G_EXC_ERROR THEN
238 x_return_status := FND_API.G_RET_STS_ERROR;
239 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
240 p_data => x_msg_data,
241 p_encoded => 'F');
242
243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
246 p_data => x_msg_data,
247 p_encoded => 'F');
248
249 WHEN OTHERS THEN
250 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
251 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
252 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
253 FND_MESSAGE.Set_Token('REASON', SQLERRM);
254 FND_MSG_PUB.Add;
255
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257
258 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
259 THEN
260 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
261 END IF;
262
263 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
264 p_data => x_msg_data,
265 p_encoded => 'F');
266
267 END Load_SectionItems_For_Item;
268
269 --
270 -- Returns child-sections for the given section
271 -- Return data (association + child-section data + section data) belonging to
272 -- the section (p_section_id)
273 --
274 PROCEDURE Load_ChildSections_For_Section
275 (
276 p_api_version IN NUMBER,
277 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
278 p_commit IN VARCHAR2 := FND_API.G_FALSE,
279 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
280 p_section_id IN NUMBER,
281 x_section_csr OUT NOCOPY SECTION_CSR,
282 x_section_section_csr OUT NOCOPY SECTION_SECTION_CSR,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2
286 )
287 IS
288 l_api_name CONSTANT VARCHAR2(30) :=
289 'Load_Sections_For_Section';
290 l_api_version CONSTANT NUMBER := 1.0;
291
292 l_master_mini_site_id NUMBER;
293 l_master_root_section_id NUMBER;
294
295 BEGIN
296
297 -- Initialize message list if p_init_msg_list is set to TRUE.
298 IF FND_API.to_Boolean(p_init_msg_list) THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301
302 -- Initialize API return status to success
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 -- Get the master mini-site id
306 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id
307 (
308 x_mini_site_id => l_master_mini_site_id,
309 x_root_section_id => l_master_root_section_id
310 );
311
312 -- Get the (parent) section data
313 OPEN x_section_csr FOR SELECT display_name FROM ibe_dsp_sections_vl
314 WHERE section_id = p_section_id;
315
316 -- Get the section-(child)section association data and child-section data
317 /*
318 OPEN x_section_section_csr FOR SELECT S.section_id, S.access_name,
319 S.display_name, S.section_type_code, L1.meaning, S.status_code, L2.meaning,
320 MSS.mini_site_section_section_id, MSS.object_version_number,
321 MSS.start_date_active, MSS.end_date_active, MSS.sort_order
322 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS, fnd_lookups L1,
323 fnd_lookups L2
324 WHERE S.section_id IN
325 (SELECT child_section_id FROM ibe_dsp_msite_sct_sects
326 WHERE parent_section_id = p_section_id
327 AND mini_site_id = l_master_mini_site_id)
328 AND MSS.child_section_id = S.section_id
329 AND MSS.mini_site_id = l_master_mini_site_id
330 AND L1.lookup_type = 'IBE_SECTION_TYPE'
331 AND L1.lookup_code = S.section_type_code
332 AND L2.lookup_type = 'IBE_SECTION_STATUS'
333 AND L2.lookup_code = S.status_code
334 -- ORDER BY S.display_name;
335 --modified by abhandar 05/30/2002
336 ORDER BY MSS.sort_order;
337 */
338 -- Performance bug fix 2887798
339 OPEN x_section_section_csr FOR SELECT S.section_id, S.access_name,
340 S.display_name, S.section_type_code, L1.meaning, S.status_code, L2.meaning,
341 MSS.mini_site_section_section_id, MSS.object_version_number,
342 MSS.start_date_active, MSS.end_date_active, MSS.sort_order
343 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS, fnd_lookups L1,
344 fnd_lookups L2
345 WHERE MSS.parent_section_id = p_section_id
346 AND MSS.child_section_id = S.section_id
347 AND MSS.mini_site_id = l_master_mini_site_id
348 AND L1.lookup_type = 'IBE_SECTION_TYPE'
352 ---Added by amaheshw on 10.Jan.2008 bug 6712124. check end date also
349 AND L1.lookup_code = S.section_type_code
350 AND L2.lookup_type = 'IBE_SECTION_STATUS'
351 AND L2.lookup_code = S.status_code
353 AND sysdate BETWEEN S.start_date_active AND NVL(S.end_date_active,sysdate)
354 ORDER BY MSS.sort_order;
355 EXCEPTION
356
357 WHEN FND_API.G_EXC_ERROR THEN
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
360 p_data => x_msg_data,
361 p_encoded => 'F');
362
363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
366 p_data => x_msg_data,
367 p_encoded => 'F');
368
369 WHEN OTHERS THEN
370 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
371 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
372 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
373 FND_MESSAGE.Set_Token('REASON', SQLERRM);
374 FND_MSG_PUB.Add;
375
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377
378 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
379 THEN
380 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
381 END IF;
382
383 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
384 p_data => x_msg_data,
385 p_encoded => 'F');
386
387 END Load_ChildSections_For_Section;
388
389 --
390 -- Returns child-sections or child-items for the given section
391 -- Return data (association + child section + section data) or
392 -- (association + item + section data) belonging to the section (p_section_id)
393 -- x_sectionitem_section_csr will be returned for child-items or
394 -- x_section_section_csr will be returned for child-sections
395 -- x_is_leaf_section will be returned if p_section_id is a leaf section
396 -- A section will be a leaf section if it has items associated to it or
397 -- it has neither items nor sections associated to it. It will be a non-leaf
398 -- section only when there are child-sections associated to it.
399 --
400 PROCEDURE Load_Children_For_Section
401 (
402 p_api_version IN NUMBER,
403 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
404 p_commit IN VARCHAR2 := FND_API.G_FALSE,
405 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
406 p_section_id IN NUMBER,
407 x_is_leaf_section OUT NOCOPY VARCHAR2,
408 x_section_path OUT NOCOPY VARCHAR2,
409 x_section_csr OUT NOCOPY SECTION_CSR,
410 x_section_section_csr OUT NOCOPY SECTION_SECTION_CSR,
411 x_sectionitem_item_csr OUT NOCOPY SECTIONITEM_SECTION_CSR,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2
415 )
416 IS
417 l_api_name CONSTANT VARCHAR2(30) :=
418 'Load_Children_For_Section';
419 l_api_version CONSTANT NUMBER := 1.0;
420
421 l_master_mini_site_id NUMBER;
422 l_master_root_section_id NUMBER;
423 l_row_id VARCHAR2(30);
424
425 -- Check if l_c_section_id has child sections
426 CURSOR c1(l_c_section_id IN NUMBER, l_c_master_mini_site_id IN NUMBER) IS
427 SELECT rowid FROM ibe_dsp_msite_sct_sects
428 WHERE mini_site_id = l_c_master_mini_site_id
429 AND parent_section_id = l_c_section_id;
430
431 BEGIN
432
433 -- Initialize message list if p_init_msg_list is set to TRUE.
434 IF FND_API.to_Boolean(p_init_msg_list) THEN
435 FND_MSG_PUB.initialize;
436 END IF;
437
438 -- Initialize API return status to success
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 -- Get the master mini-site id
442 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id
443 (
444 x_mini_site_id => l_master_mini_site_id,
445 x_root_section_id => l_master_root_section_id
446 );
447
448 -- Get the section path
449 BEGIN
450 x_section_path := Get_Display_Name('S', p_section_id);
451 EXCEPTION
452 WHEN OTHERS THEN
453 x_section_path := '';
454 END;
455
456 -- Check if p_section_id is a leaf section or not. If it has child-sections,
457 -- then it is not a leaf section
458 OPEN c1(p_section_id, l_master_mini_site_id);
459 FETCH c1 INTO l_row_id;
460 IF (c1%NOTFOUND) THEN
461 x_is_leaf_section := 'Y';
462 Load_SectionItems_For_Section
463 (
464 p_api_version => p_api_version,
465 p_init_msg_list => FND_API.G_FALSE,
466 p_commit => FND_API.G_FALSE,
467 p_validation_level => p_validation_level,
468 p_section_id => p_section_id,
469 x_section_csr => x_section_csr,
470 x_sectionitem_item_csr => x_sectionitem_item_csr,
471 x_return_status => x_return_status,
475
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data
474 );
476 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
479 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_LOAD_CHILD_ITM_FOR_SCT');
480 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
481 FND_MSG_PUB.Add;
482 RAISE FND_API.G_EXC_ERROR;
483 END IF;
484
485 ELSE
486
487 x_is_leaf_section := 'N';
488 Load_ChildSections_For_Section
489 (
490 p_api_version => p_api_version,
491 p_init_msg_list => FND_API.G_FALSE,
492 p_commit => FND_API.G_FALSE,
493 p_validation_level => p_validation_level,
494 p_section_id => p_section_id,
495 x_section_csr => x_section_csr,
496 x_section_section_csr => x_section_section_csr,
497 x_return_status => x_return_status,
498 x_msg_count => x_msg_count,
499 x_msg_data => x_msg_data
500 );
501
502 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
505 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_LOAD_CHILD_SCT_FOR_SCT');
506 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
507 FND_MSG_PUB.Add;
508 RAISE FND_API.G_EXC_ERROR;
509 END IF;
510
511 END IF;
512
513 CLOSE c1;
514
515
516 EXCEPTION
517
518 WHEN FND_API.G_EXC_ERROR THEN
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
521 p_data => x_msg_data,
522 p_encoded => 'F');
523
524 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
527 p_data => x_msg_data,
528 p_encoded => 'F');
529
530 WHEN OTHERS THEN
531 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
532 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
533 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
534 FND_MESSAGE.Set_Token('REASON', SQLERRM);
535 FND_MSG_PUB.Add;
536
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538
539 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
540 THEN
541 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
542 END IF;
543
544 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
545 p_data => x_msg_data,
546 p_encoded => 'F');
547
548 END Load_Children_For_Section;
549
550 PROCEDURE Load_Section_For_Basic_Desc
551 (
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
554 p_commit IN VARCHAR2 := FND_API.G_FALSE,
555 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
556 p_section_id IN NUMBER,
557 x_section_path OUT NOCOPY VARCHAR2,
558 x_section_csr OUT NOCOPY SECTION_CSR,
559 x_section_type_csr OUT NOCOPY SECTION_TYPE_CSR,
560 x_section_status_csr OUT NOCOPY SECTION_STATUS_CSR,
561 x_deliverable_csr OUT NOCOPY DELIVERABLE_CSR,
562 x_display_context_csr OUT NOCOPY DISPLAY_CONTEXT_CSR,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY NUMBER,
565 x_msg_data OUT NOCOPY VARCHAR2
566 )
567 IS
568 l_api_name CONSTANT VARCHAR2(30) :=
569 'Load_Section_For_Basic_Desc';
570 l_api_version CONSTANT NUMBER := 1.0;
571
572 l_master_mini_site_id NUMBER;
573 l_master_root_section_id NUMBER;
574
575 BEGIN
576
577 -- Initialize message list if p_init_msg_list is set to TRUE.
578 IF FND_API.to_Boolean(p_init_msg_list) THEN
579 FND_MSG_PUB.initialize;
580 END IF;
581
582 -- Initialize API return status to success
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 -- Get the section data
586 OPEN x_section_csr FOR SELECT section_id, object_version_number,
587 display_name, access_name, section_type_code, status_code,
588 start_date_active, end_date_active, description, long_description,
589 keywords, deliverable_id, display_context_id, auto_placement_rule,
590 order_by_clause
591 FROM ibe_dsp_sections_vl
592 WHERE section_id = p_section_id;
593
594 -- Get the section path
595 BEGIN
596 x_section_path := Get_Display_Name('S', p_section_id);
597 EXCEPTION
598 WHEN OTHERS THEN
599 x_section_path := '';
600 END;
601
602 -- Get the 'list of section type' cursor
606 -- Get the 'list of section type' cursor
603 OPEN x_section_type_csr FOR SELECT lookup_code, meaning FROM fnd_lookups
604 WHERE lookup_type = 'IBE_SECTION_TYPE';
605
607 OPEN x_section_status_csr FOR SELECT lookup_code, meaning FROM fnd_lookups
608 WHERE lookup_type = 'IBE_SECTION_STATUS';
609
610 -- Get the deliverable list
611 -- bug 3610994
612 OPEN x_deliverable_csr FOR SELECT item_id, access_name, item_name
613 FROM jtf_amv_items_vl
614 WHERE deliverable_type_code = 'TEMPLATE'
615 AND applicable_to_code LIKE '%SECTION'
616 AND application_id= 671;
617
618 -- Get the display context list
619 OPEN x_display_context_csr FOR SELECT context_id, access_name, name
620 FROM ibe_dsp_context_vl
621 WHERE context_type_code = 'TEMPLATE';
622
623 EXCEPTION
624
625 WHEN FND_API.G_EXC_ERROR THEN
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
628 p_data => x_msg_data,
629 p_encoded => 'F');
630
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
634 p_data => x_msg_data,
635 p_encoded => 'F');
636
637 WHEN OTHERS THEN
638 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
639 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
640 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
641 FND_MESSAGE.Set_Token('REASON', SQLERRM);
642 FND_MSG_PUB.Add;
643
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645
646 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
647 THEN
648 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
649 END IF;
650
651 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
652 p_data => x_msg_data,
653 p_encoded => 'F');
654
655 END Load_Section_For_Basic_Desc;
656
657 PROCEDURE Load_Root_Sct_For_Basic_Desc
658 (
659 p_api_version IN NUMBER,
660 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
661 p_commit IN VARCHAR2 := FND_API.G_FALSE,
662 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
663 x_section_path OUT NOCOPY VARCHAR2,
664 x_section_csr OUT NOCOPY SECTION_CSR,
665 x_section_type_csr OUT NOCOPY SECTION_TYPE_CSR,
666 x_section_status_csr OUT NOCOPY SECTION_STATUS_CSR,
667 x_deliverable_csr OUT NOCOPY DELIVERABLE_CSR,
668 x_display_context_csr OUT NOCOPY DISPLAY_CONTEXT_CSR,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_msg_data OUT NOCOPY VARCHAR2
672 )
673 IS
674 l_api_name CONSTANT VARCHAR2(30) :=
675 'Load_Root_Sct_For_Basic_Desc';
676 l_api_version CONSTANT NUMBER := 1.0;
677
678 l_master_mini_site_id NUMBER;
679 l_master_root_section_id NUMBER;
680
681 BEGIN
682
683 -- Initialize message list if p_init_msg_list is set to TRUE.
684 IF FND_API.to_Boolean(p_init_msg_list) THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687
688 -- Initialize API return status to success
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690
691 --
692 -- Get the master mini-site id
693 --
694 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id
695 (
696 x_mini_site_id => l_master_mini_site_id,
697 x_root_section_id => l_master_root_section_id
698 );
699
700 --
701 -- Load root section's basic description
702 --
703 Load_Section_For_Basic_Desc
704 (
705 p_api_version => p_api_version,
706 p_init_msg_list => FND_API.G_FALSE,
707 p_commit => FND_API.G_FALSE,
708 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
709 p_section_id => l_master_root_section_id,
710 x_section_path => x_section_path,
711 x_section_csr => x_section_csr,
712 x_section_type_csr => x_section_type_csr,
713 x_section_status_csr => x_section_status_csr,
714 x_deliverable_csr => x_deliverable_csr,
715 x_display_context_csr => x_display_context_csr,
716 x_return_status => x_return_status,
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data
719 );
720
721 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
723 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
724 RAISE FND_API.G_EXC_ERROR;
725 END IF;
726
727 EXCEPTION
728
729 WHEN FND_API.G_EXC_ERROR THEN
730 x_return_status := FND_API.G_RET_STS_ERROR;
731 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
732 p_data => x_msg_data,
733 p_encoded => 'F');
734
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
738 p_data => x_msg_data,
739 p_encoded => 'F');
740
741 WHEN OTHERS THEN
742 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
743 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
744 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
745 FND_MESSAGE.Set_Token('REASON', SQLERRM);
746 FND_MSG_PUB.Add;
747
748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
749
750 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
751 THEN
752 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
753 END IF;
754
755 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
756 p_data => x_msg_data,
757 p_encoded => 'F');
758
759 END Load_Root_Sct_For_Basic_Desc;
760
761 --
762 -- Get the list of candidate mini-sites for a section, i.e., the list of all
763 -- the mini-sites to which the section can be associated with. Also get all
764 -- the sites to which the section is included into.
765 -- Also get the available_in_all_sites flag for the section in x_section_csr
766 --
767 PROCEDURE Get_Cand_Incl_MSites_For_Sct
768 (
769 p_api_version IN NUMBER,
770 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
771 p_commit IN VARCHAR2 := FND_API.G_FALSE,
772 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
773 p_section_id IN NUMBER,
774 x_section_path OUT NOCOPY VARCHAR2,
775 x_section_csr OUT NOCOPY SECTION_CSR,
776 x_incl_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
777 x_cndt_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
778 x_return_status OUT NOCOPY VARCHAR2,
779 x_msg_count OUT NOCOPY NUMBER,
780 x_msg_data OUT NOCOPY VARCHAR2
781 )
782 IS
783 l_api_name CONSTANT VARCHAR2(30) :=
784 'Get_Cand_Incl_MSites_For_Sct';
785 l_api_version CONSTANT NUMBER := 1.0;
786
787 l_master_mini_site_id NUMBER;
788 l_master_root_section_id NUMBER;
789 l_parent_section_id NUMBER;
790
791 CURSOR c1(l_c_section_id IN NUMBER, l_c_master_mini_site_id IN NUMBER)
792 IS SELECT parent_section_id FROM ibe_dsp_msite_sct_sects
793 WHERE mini_site_id = l_c_master_mini_site_id
794 AND child_section_id = l_c_section_id;
795
796 BEGIN
797
798 -- Standard call to check for call compatibility.
799 IF NOT FND_API.Compatible_API_Call(l_api_version,
800 p_api_version,
801 l_api_name,
802 G_PKG_NAME)
803 THEN
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805 END IF;
806
807 -- Initialize message list if p_init_msg_list is set to TRUE.
808 IF FND_API.to_Boolean(p_init_msg_list) THEN
809 FND_MSG_PUB.initialize;
810 END IF;
811
812 -- Initialize API return status to success
813 x_return_status := FND_API.G_RET_STS_SUCCESS;
814
815 --
816 -- Get the master mini-site id
817 --
818 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id
819 (
820 x_mini_site_id => l_master_mini_site_id,
821 x_root_section_id => l_master_root_section_id
822 );
823
824 --
825 -- Get the parent section for p_section_id
826 --
827 OPEN c1(p_section_id, l_master_mini_site_id);
828 FETCH c1 INTO l_parent_section_id;
829 IF (c1%NOTFOUND) THEN
830 l_parent_section_id := null;
831 END IF;
832 CLOSE c1;
833
834 --
835 -- Get the section data for p_section_id
836 --
837 OPEN x_section_csr FOR SELECT section_id, object_version_number,
838 display_name, available_in_all_sites_flag
839 FROM ibe_dsp_sections_vl
840 WHERE section_id = p_section_id;
841
842 -- Get the section path
843 BEGIN
844 x_section_path := Get_Display_Name('S', p_section_id);
845 EXCEPTION
846 WHEN OTHERS THEN
847 x_section_path := '';
848 END;
849
850 --
851 -- Get included mini-sites for a section
852 --
853 OPEN x_incl_mini_site_csr FOR SELECT msite_id, msite_name, msite_description
854 FROM ibe_msites_vl
855 WHERE EXISTS
856 (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
857 WHERE mini_site_id = msite_id
858 AND mini_site_id <> l_master_mini_site_id
859 AND child_section_id = p_section_id)
860 ORDER BY msite_name;
861
862 --
863 -- Get candidate mini-sites for a section
864 --
865 IF (l_parent_section_id IS NOT NULL) THEN
866 --
867 -- Get candidate mini-sites for a section, which includes the mini-sites to
868 -- which the section's parent section belong to, plus the one's to which
872 msite_description
869 -- the section is the mini-site's root section
870 --
871 OPEN x_cndt_mini_site_csr FOR SELECT msite_id, msite_name,
873 FROM ibe_msites_vl
874 WHERE msite_id <> l_master_mini_site_id AND
875 (msite_root_section_id = p_section_id OR
876 EXISTS (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
877 WHERE mini_site_id = msite_id
878 AND mini_site_id <> l_master_mini_site_id
879 AND child_section_id = l_parent_section_id))
880 ORDER BY msite_name;
881 ELSE
882 --
883 -- Parent section ID is null (or p_section_id is root section id),
884 -- therefor list of candidate sites will be all the mini-sites in
885 -- ibe_msites_b table for which the msite_root_section is null or the master root section id
886 --
887 OPEN x_cndt_mini_site_csr FOR SELECT msite_id, msite_name,
888 msite_description
889 FROM ibe_msites_vl
890 WHERE msite_id <> l_master_mini_site_id
891 --bug 3410883
892 AND (msite_root_section_id = l_master_root_section_id or msite_root_section_id is null)
893 AND site_type = 'I'
894 ORDER BY msite_name;
895
896 END IF;
897
898 --
899 -- End of main API body.
900
901 -- Standard check of p_commit.
902 IF (FND_API.To_Boolean(p_commit)) THEN
903 COMMIT WORK;
904 END IF;
905
906 -- Standard call to get message count and if count is 1, get message info.
907 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
908 p_data => x_msg_data,
909 p_encoded => 'F');
910
911 EXCEPTION
912
913 WHEN FND_API.G_EXC_ERROR THEN
914 x_return_status := FND_API.G_RET_STS_ERROR;
915 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
916 p_data => x_msg_data,
917 p_encoded => 'F');
918
919 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
922 p_data => x_msg_data,
923 p_encoded => 'F');
924
925 WHEN OTHERS THEN
926 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
927 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
928 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
929 FND_MESSAGE.Set_Token('REASON', SQLERRM);
930 FND_MSG_PUB.Add;
931
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
933
934 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
935 THEN
936 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
937 END IF;
938
939 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
940 p_data => x_msg_data,
941 p_encoded => 'F');
942
943 END Get_Cand_Incl_MSites_For_Sct;
944
945 PROCEDURE Load_Items_For_Basic_Desc
946 (
947 p_api_version IN NUMBER,
948 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
949 p_commit IN VARCHAR2 := FND_API.G_FALSE,
950 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
951 p_inventory_item_id IN NUMBER,
952 p_organization_id IN NUMBER,
953 x_inventory_item_csr OUT NOCOPY INVENTORY_ITEM_CSR,
954 x_web_status_type_csr OUT NOCOPY WEB_STATUS_TYPE_CSR,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2
958 )
959 IS
960 l_api_name CONSTANT VARCHAR2(30) :=
961 'Load_Items_For_Basic_Desc';
962 l_api_version CONSTANT NUMBER := 1.0;
963
964 BEGIN
965
966 -- Initialize message list if p_init_msg_list is set to TRUE.
967 IF FND_API.to_Boolean(p_init_msg_list) THEN
968 FND_MSG_PUB.initialize;
969 END IF;
970
971 -- Initialize API return status to success
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 -- Get the item data
975 OPEN x_inventory_item_csr FOR SELECT inventory_item_id, organization_id,
976 web_status, description, long_description, concatenated_segments,
977 creation_date, last_updated_by, last_update_login, last_update_date
978 FROM mtl_system_items_vl
979 WHERE inventory_item_id = p_inventory_item_id
980 AND organization_id = p_organization_id;
981
982 -- Get the 'list of item web status type' cursor
983 OPEN x_web_status_type_csr FOR SELECT lookup_code, meaning FROM fnd_lookups
984 WHERE lookup_type = 'IBE_ITEM_STATUS';
985
986 EXCEPTION
987
988 WHEN FND_API.G_EXC_ERROR THEN
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
991 p_data => x_msg_data,
992 p_encoded => 'F');
993
994 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1000 WHEN OTHERS THEN
997 p_data => x_msg_data,
998 p_encoded => 'F');
999
1001 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1002 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1003 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1004 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1005 FND_MSG_PUB.Add;
1006
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008
1009 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1010 THEN
1011 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1012 END IF;
1013
1014 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1015 p_data => x_msg_data,
1016 p_encoded => 'F');
1017
1018 END Load_Items_For_Basic_Desc;
1019
1020 --
1021 -- Get the list of candidate mini-sites for an item, i.e., the list of all
1022 -- the mini-sites to which the item can be associated with. Also get all
1023 -- the sites to which the item is included into.
1024 --
1025 PROCEDURE Get_Cand_Incl_MSites_For_Itm
1026 (
1027 p_api_version IN NUMBER,
1028 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1029 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1030 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1031 p_inventory_item_id IN NUMBER,
1032 p_organization_id IN NUMBER,
1033 x_inventory_item_csr OUT NOCOPY INVENTORY_ITEM_CSR,
1034 x_incl_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
1035 x_cndt_mini_site_csr OUT NOCOPY MINI_SITE_CSR,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2
1039 )
1040 IS
1041 l_api_name CONSTANT VARCHAR2(30) :=
1042 'Get_Cand_Incl_MSites_For_Itm';
1043 l_api_version CONSTANT NUMBER := 1.0;
1044
1045 l_master_mini_site_id NUMBER;
1046 l_master_root_section_id NUMBER;
1047
1048 BEGIN
1049
1050 -- Standard call to check for call compatibility.
1051 IF NOT FND_API.Compatible_API_Call(l_api_version,
1052 p_api_version,
1053 l_api_name,
1054 G_PKG_NAME)
1055 THEN
1056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1057 END IF;
1058
1059 -- Initialize message list if p_init_msg_list is set to TRUE.
1060 IF FND_API.to_Boolean(p_init_msg_list) THEN
1061 FND_MSG_PUB.initialize;
1062 END IF;
1063
1064 -- Initialize API return status to success
1065 x_return_status := FND_API.G_RET_STS_SUCCESS;
1066
1067 --
1068 -- Get the master mini-site id
1069 --
1070 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id
1071 (
1072 x_mini_site_id => l_master_mini_site_id,
1073 x_root_section_id => l_master_root_section_id
1074 );
1075
1076 --
1077 -- Get the item data
1078 --
1079 OPEN x_inventory_item_csr FOR SELECT MTL.inventory_item_id,
1080 MTL.organization_id, MTL.web_status, L.meaning, MTL.description,
1081 MTL.concatenated_segments, MTL.creation_date, MTL.last_updated_by,
1082 MTL.last_update_login, MTL.last_update_date
1083 FROM mtl_system_items_vl MTL, fnd_lookups L
1084 WHERE MTL.inventory_item_id = p_inventory_item_id
1085 AND MTL.organization_id = p_organization_id
1086 AND L.lookup_type = 'IBE_ITEM_STATUS'
1087 AND L.lookup_code = MTL.web_status;
1088
1089 --
1090 -- Get included mini-sites for item
1091 --
1092 OPEN x_incl_mini_site_csr FOR SELECT msite_id, msite_name, msite_description
1093 FROM ibe_msites_vl
1094 WHERE EXISTS
1095 (SELECT mini_site_id FROM ibe_dsp_msite_sct_items
1096 WHERE mini_site_id = msite_id
1097 AND section_item_id IN
1098 (SELECT section_item_id FROM ibe_dsp_section_items
1099 WHERE inventory_item_id = p_inventory_item_id
1100 AND organization_id = p_organization_id))
1101 ORDER BY msite_name;
1102
1103 --
1104 -- Get candidate mini-sites for item
1105 --
1106 OPEN x_cndt_mini_site_csr FOR SELECT msite_id, msite_name, msite_description
1107 FROM ibe_msites_vl
1108 WHERE EXISTS
1109 (SELECT mini_site_id FROM ibe_dsp_msite_sct_sects
1110 WHERE mini_site_id = msite_id
1111 AND mini_site_id <> l_master_mini_site_id
1112 AND child_section_id IN
1113 (SELECT section_id FROM ibe_dsp_section_items
1114 WHERE inventory_item_id = p_inventory_item_id
1115 AND organization_id = p_organization_id))
1116 ORDER BY msite_name;
1117
1118 --
1119 -- End of main API body.
1120
1121 -- Standard check of p_commit.
1122 IF (FND_API.To_Boolean(p_commit)) THEN
1123 COMMIT WORK;
1124 END IF;
1125
1126 -- Standard call to get message count and if count is 1, get message info.
1127 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1128 p_data => x_msg_data,
1129 p_encoded => 'F');
1130
1131 EXCEPTION
1132
1133 WHEN FND_API.G_EXC_ERROR THEN
1134 x_return_status := FND_API.G_RET_STS_ERROR;
1138
1135 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1136 p_data => x_msg_data,
1137 p_encoded => 'F');
1139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1141 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1142 p_data => x_msg_data,
1143 p_encoded => 'F');
1144
1145 WHEN OTHERS THEN
1146 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1147 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1148 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1149 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1150 FND_MSG_PUB.Add;
1151
1152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1153
1154 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1155 THEN
1156 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1157 END IF;
1158
1159 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1160 p_data => x_msg_data,
1161 p_encoded => 'F');
1162
1163 END Get_Cand_Incl_MSites_For_Itm;
1164
1165
1166 --
1167 -- Obsoleted and Removed code for the PROCEDURE Get_Item_Id_List :bug 2936693 :05/09/2003 :abhandar
1168 ---
1169 --
1170 -- Obsoleted and Removed code for the PROCEDURE Get_Item_Info_For_Lookup :bug 2936693 :05/09/2003:abhandar
1171 ---
1172 --
1173 -- Obsoleted and Removed code for the PROCEDURE Get_Item_Info_For_Detail_List :bug 2936693 :05/09/2003:abhandar
1174 ---
1175 --
1176 -- Obsoleted and Removed code for the PROCEDURE Get_Section_Id_List :bug 2936693 :05/09/2003:abhandar
1177 ---
1178 --
1179 -- Obsoleted and Removedcode for the PROCEDURE Get_Section_Info_For_Lookup :bug 2936693 :05/09/2003:abhandar
1180 ---
1181 --
1182 --
1183 -- Procedure to retrieve the section hierarchy path
1184 --
1185
1186 PROCEDURE Get_Section_Path
1187 (
1188 p_section_id IN NUMBER,
1189 x_section_path OUT NOCOPY VARCHAR2,
1190 x_section_name OUT NOCOPY VARCHAR2,
1191 x_section_desc OUT NOCOPY VARCHAR2,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2
1195 )
1196 IS
1197
1198 l_master_mini_site_id NUMBER;
1199 l_position NUMBER;
1200 l_master_root_section_id NUMBER;
1201
1202 Cursor c1( p_section_id IN NUMBER) is
1203 select display_name,description
1204 from ibe_dsp_sections_vl where section_id=p_section_id;
1205
1206
1207 BEGIN
1208
1209
1210 -- Initialize API return status to success
1211 x_return_status := FND_API.G_RET_STS_SUCCESS;
1212
1213 -- Get the master mini-site id
1214 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id
1215 (
1216 x_mini_site_id => l_master_mini_site_id,
1217 x_root_section_id => l_master_root_section_id
1218 );
1219
1220 -- Get the section path
1221 BEGIN
1222 x_section_path := Get_Display_Name('S', p_section_id);
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 x_section_path := '';
1227 END;
1228 -- need to remove the last path from the section path string
1229 -- get the last occurence of / in the string.
1230 SELECT INSTR(x_section_path,'/', -1, 1) into l_position FROM DUAL;
1231
1232 -- get the substring uptil the last '/'
1233 SELECT SUBSTR(x_section_path,1,l_position) into x_section_path from dual;
1234
1235 -- get the section name and desc
1236 OPEN c1(p_section_id);
1237 FETCH c1 INTO x_section_name, x_section_desc;
1238
1239 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1241 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1242 RAISE FND_API.G_EXC_ERROR;
1243 END IF;
1244 CLOSE c1;
1245
1246
1247 EXCEPTION
1248
1249 WHEN FND_API.G_EXC_ERROR THEN
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251
1252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1254
1255 WHEN OTHERS THEN
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257
1258
1259 END Get_Section_Path;
1260
1261 PROCEDURE Load_Section_Hierarchy
1262 (
1263 p_msite_id IN NUMBER,
1264 p_section_id IN NUMBER,
1265 p_level_number IN NUMBER,
1266 x_section_hierarchy_csr OUT NOCOPY SECTION_HGRID_CSR,
1267 x_return_status OUT NOCOPY VARCHAR2,
1268 x_msg_count OUT NOCOPY NUMBER,
1269 x_msg_data OUT NOCOPY VARCHAR2
1270 )
1271 IS
1272 l_api_name CONSTANT VARCHAR2(30) :=
1273 'Get_All_Section_Hierarchy';
1274 l_api_version CONSTANT NUMBER := 1.0;
1275
1276
1277
1278 BEGIN
1279
1280 -- Initialize API return status to success
1281 x_return_status := FND_API.G_RET_STS_SUCCESS;
1282
1283
1284 IF p_level_number >0 THEN
1288 /*SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
1285
1286 --load the hgrid tree upto level= p_level_number
1287 OPEN x_section_hierarchy_csr FOR
1289 s.display_name, mss.concat_ids, fl.meaning,s.status_code, s.start_date_active,
1290 child_sct.parent_section_id subsections,child_item.section_id products,
1291 s.section_type_code, fl1.meaning status_name,s.access_name,MSS.level_number
1292 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,fnd_lookups fl,
1293 fnd_lookups fl1, ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
1294 WHERE S.section_id = MSS.child_section_id AND fl.lookup_code=s.section_type_code
1295 and fl.lookup_type= 'IBE_SECTION_TYPE' AND fl1.lookup_code=s.status_code
1296 AND fl1.lookup_type= 'IBE_SECTION_STATUS' AND fl.enabled_flag = fl1.enabled_flag
1297 AND MSS.mini_site_id = p_msite_id AND s.section_id = child_sct.parent_section_id (+)
1298 AND child_sct.mini_site_id (+) = p_msite_id
1299 AND s.section_id = child_item.section_id (+)
1300 AND mss.level_number <= p_level_number
1301 ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;
1302
1303 SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
1304 s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
1305 child_sct.parent_section_id subsections,child_item.section_id products,
1306 s.section_type_code, s.access_name,MSS.level_number
1307 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,
1308 ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
1309 WHERE S.section_id = MSS.child_section_id
1310 AND MSS.mini_site_id = p_msite_id AND s.section_id = child_sct.parent_section_id (+)
1311 AND child_sct.mini_site_id (+) = p_msite_id
1312 AND s.section_id = child_item.section_id (+)
1313 AND mss.level_number <= p_level_number
1314 ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;
1315 */
1316 SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
1317 s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
1318 s.section_type_code, s.access_name,MSS.level_number
1319 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
1320 WHERE S.section_id = MSS.child_section_id
1321 AND MSS.mini_site_id = p_msite_id
1322 AND mss.level_number <= p_level_number
1323 ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;
1324
1325 ELSE
1326 -- load the whole hgrid tree data
1327 OPEN x_section_hierarchy_csr FOR
1328 /* SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
1329 s.display_name, mss.concat_ids, fl.meaning,s.status_code, s.start_date_active,
1330 child_sct.parent_section_id subsections,child_item.section_id products,
1331 s.section_type_code, fl1.meaning status_name,s.access_name,MSS.level_number
1332 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,fnd_lookups fl,
1333 fnd_lookups fl1, ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
1334 WHERE S.section_id = MSS.child_section_id AND fl.lookup_code=s.section_type_code
1335 and fl.lookup_type= 'IBE_SECTION_TYPE' AND fl1.lookup_code=s.status_code
1336 AND fl1.lookup_type= 'IBE_SECTION_STATUS' AND fl.enabled_flag = fl1.enabled_flag
1337 AND MSS.mini_site_id = p_msite_id AND s.section_id = child_sct.parent_section_id (+)
1338 AND child_sct.mini_site_id (+) = p_msite_id
1339 AND s.section_id = child_item.section_id (+)
1340 ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;
1341
1342 SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
1343 s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
1344 child_sct.parent_section_id subsections,child_item.section_id products,
1345 s.section_type_code, s.access_name,MSS.level_number
1346 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS ,
1347 ibe_dsp_msite_sct_sects child_sct,ibe_dsp_section_items child_item
1348 WHERE S.section_id = MSS.child_section_id
1349 AND MSS.mini_site_id = p_msite_id AND s.section_id = child_sct.parent_section_id (+)
1350 AND child_sct.mini_site_id (+) = p_msite_id
1351 AND s.section_id = child_item.section_id (+)
1352 ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;
1353 */
1354 SELECT DISTINCT mss.parent_section_id,mss.child_section_id,
1355 s.display_name, mss.concat_ids, s.status_code,s.start_date_active,
1356 s.section_type_code, s.access_name,MSS.level_number
1357 FROM ibe_dsp_sections_vl S, ibe_dsp_msite_sct_sects MSS
1358 WHERE S.section_id = MSS.child_section_id
1359 AND MSS.mini_site_id = p_msite_id
1360 ORDER BY MSS.level_number, mss.parent_section_id,s.display_name;
1361
1362 END if;
1363
1364 EXCEPTION
1365
1366 WHEN FND_API.G_EXC_ERROR THEN
1367 x_return_status := FND_API.G_RET_STS_ERROR;
1368 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1369 p_data => x_msg_data,
1370 p_encoded => 'F');
1371
1372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1374 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1375 p_data => x_msg_data,
1376 p_encoded => 'F');
1377
1378 WHEN OTHERS THEN
1379 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1380 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1381 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1382 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1383 FND_MSG_PUB.Add;
1384
1385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1386
1387 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1388 THEN
1389 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1390 END IF;
1391
1392 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1393 p_data => x_msg_data,
1394 p_encoded => 'F');
1395
1396 END Load_Section_Hierarchy;
1397 END IBE_DSP_HIERARCHY_QUERY_PVT;
[Home] [Help]