[Home] [Help]
PACKAGE BODY: APPS.IBE_CATALOG_PVT
Source
1 PACKAGE BODY IBE_CATALOG_PVT AS
2 /* $Header: IBEVCCTB.pls 120.7 2006/07/19 19:15:01 abhandar ship $ */
3
4
5
6 -------
7 -- (code for PROCEDURE Load_Section removed on 01/19/2005 by rgupta)
8 -- This procedure is no longer necessary due to a redesign of the iStore
9 -- Section cache.
10 --
11
12
13
14 -- Start of comments
15 -- API name : Load_Sections
16 -- Type : Private.
17 -- Function : Given a list of section IDs, loads supersection and item
18 -- information for each section.
19 -- Pre-reqs : None.
20 -- Parameters :
21 -- IN : p_api_version IN NUMBER Required
22 -- p_init_msg_list IN VARCHAR2 Optional
23 -- p_validation_level IN NUMBER Optional
24 -- p_sectid_tbl IN JTF_NUMBER_TABLE
25 -- p_msite_id IN NUMBER
26 --
27 -- OUT : x_return_status OUT VARCHAR2(1)
28 -- x_msg_count OUT NUMBER
29 -- x_msg_data OUT VARCHAR2(2000)
30 -- x_supersect_sect_tbl OUT NOCOPY JTF_NUMBER_TABLE
31 -- x_supersect_supersect_tbl OUT NOCOPY JTF_NUMBER_TABLE
32 -- x_sctitm_sectid_tbl OUT NOCOPY JTF_NUMBER_TABLE
33 -- x_sctitm_itmid_tbl OUT NOCOPY JTF_NUMBER_TABLE
34 -- x_sctitm_usage_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300
35 -- x_sctitm_flags_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300
36 -- x_sctitm_startdt_tbl OUT NOCOPY JTF_DATE_TABLE
37 -- x_sctitm_enddt_tbl OUT NOCOPY JTF_DATE_TABLE
38 -- x_sctitm_assoc_startdt_tbl OUT NOCOPY JTF_DATE_TABLE
39 -- x_sctitm_assoc_enddt_tbl OUT NOCOPY JTF_DATE_TABLE
40 --
41 -- Version : Current version 1.0
42 --
43 -- Previous version None
44 --
45 -- Initial version 1.0
46 --
47 -- Notes : Note text
48 --
49 -- End of comments
50
51 procedure Load_Sections
52 (p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 := NULL,
54 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2,
58 p_sectid_tbl IN JTF_NUMBER_TABLE,
59 p_msite_id IN NUMBER,
60 x_supersect_sect_tbl OUT NOCOPY JTF_NUMBER_TABLE,
61 x_supersect_supersect_tbl OUT NOCOPY JTF_NUMBER_TABLE,
62 x_sctitm_sectid_tbl OUT NOCOPY JTF_NUMBER_TABLE,
63 x_sctitm_itmid_tbl OUT NOCOPY JTF_NUMBER_TABLE,
64 x_sctitm_orgid_tbl OUT NOCOPY JTF_NUMBER_TABLE,
65 x_sctitm_usage_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300,
66 x_sctitm_flags_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300,
67 x_sctitm_startdt_tbl OUT NOCOPY JTF_DATE_TABLE,
68 x_sctitm_enddt_tbl OUT NOCOPY JTF_DATE_TABLE,
69 x_sctitm_assoc_startdt_tbl OUT NOCOPY JTF_DATE_TABLE,
70 x_sctitm_assoc_enddt_tbl OUT NOCOPY JTF_DATE_TABLE
71 ) IS
72
73
74 cursor l_orderby_csr(p_sectid NUMBER) IS
75 SELECT s.order_by_clause
76 FROM IBE_DSP_SECTIONS_VL s
77 WHERE s.section_id = p_sectid;
78
79
80 L_MSIB_STMT CONSTANT VARCHAR2(2000) :=
81 'SELECT si.inventory_item_id, si.organization_id, si.usage_name, MSIB.web_status, ' ||
82 ' si.start_date_active assoc_start_dt, si.end_date_active assoc_end_date, ' ||
83 ' MSIB.start_date_active start_dt, MSIB.end_date_active end_dt ' ||
84 'FROM IBE_DSP_SECTION_ITEMS si, MTL_SYSTEM_ITEMS_B MSIB ' ||
85 'WHERE si.section_id = :sect_id ' ||
86 'AND si.inventory_item_id = MSIB.inventory_item_id ' ||
87 'AND si.organization_id = MSIB.organization_id ' ||
88 'AND (MSIB.WEB_STATUS = ''PUBLISHED'' OR MSIB.WEB_STATUS = ''UNPUBLISHED'') ' ||
89 'AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE ' ||
90 'AND NVL(si.end_date_active, SYSDATE) >= SYSDATE ';
91
92 L_SECT_MSIB_ORDER_STMT CONSTANT VARCHAR2(100) :=
93 'si.SORT_ORDER, MSIB.inventory_item_id';
94
95 L_MSIV_STMT CONSTANT VARCHAR2(2000) :=
96 'SELECT si.inventory_item_id, si.organization_id, si.usage_name, MSIV.web_status, ' ||
97 ' si.start_date_active assoc_start_dt, si.end_date_active assoc_end_dt, ' ||
98 ' MSIV.start_date_active start_dt, MSIV.end_date_active end_dt ' ||
99 'FROM IBE_DSP_SECTION_ITEMS si, MTL_SYSTEM_ITEMS_VL MSIV ' ||
100 'WHERE si.section_id = :sect_id ' ||
101 'AND si.inventory_item_id = MSIV.inventory_item_id ' ||
102 'AND si.organization_id = MSIV.organization_id ' ||
103 'AND (MSIV.WEB_STATUS = ''PUBLISHED'' OR MSIV.WEB_STATUS = ''UNPUBLISHED'') ' ||
104 'AND NVL(MSIV.end_date_active, SYSDATE) >= SYSDATE ' ||
105 'AND NVL(si.end_date_active, SYSDATE) >= SYSDATE ';
106
107 L_SECT_MSIV_ORDER_STMT CONSTANT VARCHAR2(100) :=
108 'si.SORT_ORDER, MSIV.inventory_item_id';
109
110
111 l_api_name CONSTANT VARCHAR2(30) := 'Load_Sections';
112 l_api_version CONSTANT NUMBER := 1.0;
113 l_init_msg_list VARCHAR2(5);
114 l_stmt VARCHAR2(32767);
115
116 l_sectid_tbl JTF_NUMBER_TABLE;
117 l_table_index NUMBER;
118
119 l_supersectid_csr IBE_CATALOG_REFCURSOR_CSR_TYPE;
120 l_get_supersects BOOLEAN;
121 l_processed_sectid_tbl JTF_NUMBER_TABLE;
122 l_processed_sectid_index NUMBER;
123 l_tmp_id NUMBER;
124
125 l_str_itms_per_sct VARCHAR2(20);
126 l_itms_per_sct NUMBER;
127 l_tmp_ord_by_clause VARCHAR2(512);
128 l_ord_by_clause VARCHAR2(1000);
129 l_itmids_csr IBE_CATALOG_REFCURSOR_CSR_TYPE;
130 l_tmp_itmid NUMBER;
131 l_tmp_orgid NUMBER;
132 l_tmp_usage VARCHAR2(255);
133 l_tmp_status_flag VARCHAR2(255);
134 l_tmp_startdt DATE;
135 l_tmp_enddt DATE;
136 l_tmp_assoc_startdt DATE;
137 l_tmp_assoc_enddt DATE;
138 l_itmid_orgid_stmt VARCHAR2(32767);
139 l_sid NUMBER;
140
141
142 BEGIN
143
144 ----------------------
145 -- Standard initialization tasks
146 ----------------------
147
148 -- Standard call to check for call compatibility.
149 IF NOT FND_API.Compatible_API_Call (l_api_version,
150 p_api_version,
151 l_api_name,
152 G_PKG_NAME )
153 THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157 IF p_init_msg_list IS NULL THEN
158 l_init_msg_list := FND_API.G_FALSE;
159 END IF;
160
161 -- Initialize message list if L_init_msg_list is set to TRUE.
162 IF FND_API.to_Boolean(L_init_msg_list) THEN
163 FND_MSG_PUB.initialize;
164 END IF;
165
166 -- Initialize API return status to success.
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 -- Print debugging info.
170 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
171 IBE_UTIL.debug('IBE_CATALOG_PVT.Load_Sections(+)');
172 IBE_UTIL.debug('IBE_CATALOG_PVT.p_msite_id = ' || p_msite_id);
173 END IF;
174
175
176 ----------------------
177 -- Error checking
178 ----------------------
179
180 -- Verify list of section IDs is not null.
181 IF p_sectid_tbl IS NULL THEN
182 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
183 IBE_UTIL.debug('IBE_CATALOG_PVT.Error p_sect_id_tbl is NULL');
184 END IF;
185 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_ID_OR_NAME');
186 FND_MSG_PUB.Add;
187 RAISE FND_API.G_EXC_ERROR;
188 ELSE
189 l_sectid_tbl := p_sectid_tbl;
190 END IF;
191
192
193 ----------------------
194 -- Get supersection information
195 ----------------------
196
197 -- Initialize final output arrays.
198 x_supersect_sect_tbl := JTF_NUMBER_TABLE();
199 x_supersect_supersect_tbl := JTF_NUMBER_TABLE();
200
201
202 -- Loop through our list of requested section IDs and retrieve supersection information.
203 l_table_index := 1;
204 l_processed_sectid_tbl := JTF_NUMBER_TABLE();
205 l_processed_sectid_index := 1;
206
207 FOR j IN 1..l_sectid_tbl.COUNT LOOP
208 l_get_supersects := true;
209 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
210 IBE_UTIL.debug('IBE_CATALOG_PVT.l_sectionid_tbl('||j ||')='||l_sectid_tbl(j));
211 END IF;
212
213 -- Check whether we already retrieved supersection information for the
214 -- current section ID.
215 FOR k IN 1..l_processed_sectid_tbl.COUNT LOOP
216 IF l_processed_sectid_tbl(k) = l_sectid_tbl(j) THEN
217 l_get_supersects := false;
218 EXIT;
219 END IF;
220 END LOOP;
221
222
223 IF l_get_supersects THEN
224 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
225 IBE_UTIL.debug('IBE_CATALOG_PVT.l_get_supersects is TRUE');
226 END IF;
227 -- Record the current section ID in our list of "already processed section IDs".
228 l_processed_sectid_tbl.extend();
229 l_processed_sectid_tbl(l_processed_sectid_index) := l_sectid_tbl(j);
230 l_processed_sectid_index := l_processed_sectid_index + 1;
231
232 -- Retrieve the supersection information for the current section ID.
233 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
234 IBE_UTIL.debug('Start Calling IBE_CATALOG_PVT.GetSuperSectIDs ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
235 END IF;
236 GetSuperSectIDs(p_api_version => p_api_version,
237 x_return_status => x_return_status,
238 x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data,
240 p_sectid => l_sectid_tbl(j),
241 p_msite_id => p_msite_id,
242 x_supersectid_csr => l_supersectid_csr);
243 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
244 IBE_UTIL.debug('End Calling IBE_CATALOG_PVT.GetSuperSectIDs ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
245 END IF;
246
247 -- Extract the supersection information into our final output arrays.
248 LOOP
249 FETCH l_supersectid_csr INTO l_tmp_id;
250 EXIT WHEN l_supersectid_csr%NOTFOUND;
251 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
252 IBE_UTIL.debug('IBE_CATALOG_PVT:value of l_tmp_id= ' || l_tmp_id);
253 END IF;
254 x_supersect_sect_tbl.EXTEND;
255 x_supersect_supersect_tbl.EXTEND;
256 x_supersect_sect_tbl(l_table_index) := l_sectid_tbl(j);
257 x_supersect_supersect_tbl(l_table_index) := l_tmp_id;
258 l_table_index := l_table_index + 1;
259
260 END LOOP;
261 close l_supersectid_csr;
262 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
263 IBE_UTIL.debug('IBE_CATALOG_PVT:closing l_supersectid_csr');
264 END IF;
265 END IF;
266
267 END LOOP;
268
269 IF x_supersect_sect_tbl.COUNT = 0 THEN
270 x_supersect_sect_tbl := NULL;
271 END IF;
272 IF x_supersect_supersect_tbl.COUNT = 0 THEN
273 x_supersect_supersect_tbl := NULL;
274 END IF;
275
276 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
277 IBE_UTIL.debug('IBE_CATALOG_PVT.Before get items per sections');
278 END IF;
279 ----------------------
280 -- Get item information
281 ----------------------
282
283 -- Initialize final output arrays.
284 x_sctitm_sectid_tbl := JTF_NUMBER_TABLE();
285 x_sctitm_itmid_tbl := JTF_NUMBER_TABLE();
286 x_sctitm_orgid_tbl := JTF_NUMBER_TABLE();
287 x_sctitm_usage_tbl := JTF_VARCHAR2_TABLE_300();
288 x_sctitm_flags_tbl := JTF_VARCHAR2_TABLE_300();
289 x_sctitm_startdt_tbl := JTF_DATE_TABLE();
290 x_sctitm_enddt_tbl := JTF_DATE_TABLE();
291 x_sctitm_assoc_startdt_tbl := JTF_DATE_TABLE();
292 x_sctitm_assoc_enddt_tbl := JTF_DATE_TABLE();
293
294
295 -- Get the number of items per section.
296 l_str_itms_per_sct := FND_PROFILE.value_specific('IBE_ITEMS_PER_SECTION', NULL, NULL, 671);
297 l_itms_per_sct := NULL;
298 IF (l_str_itms_per_sct IS NOT NULL) THEN
299
300 l_itms_per_sct := TO_NUMBER(l_str_itms_per_sct);
301 END IF;
302 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
303 IBE_UTIL.debug('IBE_CATALOG_PVT.l_itms_per_sct'||l_itms_per_sct);
304 END IF;
305
306 -- Loop through our list of requested section IDs and retrieve item information.
307 l_table_index := 1;
308 FOR i IN 1..l_sectid_tbl.COUNT LOOP
309
310
311 -- Get the order by clause from the database (if any).
312 l_sid := l_sectid_tbl(i);
313 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
314 IBE_UTIL.debug('IBE_CATALOG_PVT.l_sid'||l_sid);
315 END IF;
316 OPEN l_orderby_csr(l_sid);
317 FETCH l_orderby_csr INTO l_tmp_ord_by_clause;
318 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
319 IBE_UTIL.debug('IBE_CATALOG_PVT.l_tmp_ord_by_clause'||l_tmp_ord_by_clause);
320 END IF;
321 CLOSE l_orderby_csr;
322 Process_Order_By_Clause(l_tmp_ord_by_clause, l_ord_by_clause);
323
324 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
325 IBE_UTIL.debug('IBE_CATALOG_PVT.After Process_order_by');
326 END IF;
327
328 -- Retrieve the item information for the current section ID.
329 IF (l_itms_per_sct IS NULL) THEN
330 IF (l_ord_by_clause IS NULL OR l_ord_by_clause = '') THEN
331 l_stmt := L_MSIB_STMT || 'ORDER BY ' || L_SECT_MSIB_ORDER_STMT;
332 ELSE
333 l_stmt := L_MSIV_STMT || 'ORDER BY ' || l_ord_by_clause || ', '
334 || L_SECT_MSIV_ORDER_STMT;
335 END IF;
336
337 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
338 IBE_UTIL.debug('IBE_CATALOG_PVT.l_stmt First =' ||l_stmt);
339 END IF;
340
341 OPEN l_itmids_csr FOR l_stmt USING l_sid ;
342 ELSE
343 IF (l_ord_by_clause IS NULL OR l_ord_by_clause = '') THEN
344 l_stmt := 'SELECT * FROM (' || L_MSIB_STMT || 'ORDER BY ' ||
345 L_SECT_MSIB_ORDER_STMT || ') WHERE rownum <= :itms_per_sct';
346 ELSE
347 l_stmt := 'SELECT * FROM (' || L_MSIV_STMT || 'ORDER BY ' ||
348 l_ord_by_clause || ', ' || L_SECT_MSIV_ORDER_STMT ||
349 ') WHERE rownum <= :itms_per_sct';
350 END IF;
351 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
352 IBE_UTIL.debug('IBE_CATALOG_PVT.l_stmt Second =' ||l_stmt);
353 END IF;
354 OPEN l_itmids_csr FOR l_stmt USING l_sid, l_itms_per_sct;
355 END IF;
356
357
358 -- Extract the item information into our final output arrays.
359 LOOP
360 FETCH l_itmids_csr INTO l_tmp_itmid, l_tmp_orgid, l_tmp_usage, l_tmp_status_flag,
361 l_tmp_assoc_startdt, l_tmp_assoc_enddt,l_tmp_startdt, l_tmp_enddt;
362 EXIT WHEN l_itmids_csr%NOTFOUND;
363
364 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
365 IBE_UTIL.debug('IBE_CATALOG_PVT.l_tmp_itmid =' ||l_tmp_itmid || ':l_tmp_orgid='||l_tmp_orgid ||
366 ':l_tmp_usage='||l_tmp_usage || ':l_tmp_status_flag='||l_tmp_status_flag||':l_tmp_startdt='||l_tmp_startdt||
367 ':l_tmp_enddt='||l_tmp_enddt|| ':l_tmp_assoc_startdt='||l_tmp_assoc_startdt||':l_tmp_assoc_enddt='||l_tmp_assoc_enddt);
368 END IF;
369 x_sctitm_sectid_tbl.EXTEND;
370 x_sctitm_itmid_tbl.EXTEND;
371 x_sctitm_orgid_tbl.EXTEND;
372 x_sctitm_usage_tbl.EXTEND;
373 x_sctitm_flags_tbl.EXTEND;
374 x_sctitm_startdt_tbl.EXTEND;
375 x_sctitm_enddt_tbl.EXTEND;
376 x_sctitm_assoc_startdt_tbl.EXTEND;
377 x_sctitm_assoc_enddt_tbl.EXTEND;
378 x_sctitm_sectid_tbl(l_table_index) := l_sectid_tbl(i);
379 x_sctitm_itmid_tbl(l_table_index) := l_tmp_itmid;
380 x_sctitm_orgid_tbl(l_table_index) := l_tmp_orgid;
381 x_sctitm_usage_tbl(l_table_index) := l_tmp_usage;
382 x_sctitm_flags_tbl(l_table_index) := l_tmp_status_flag;
383 x_sctitm_startdt_tbl(l_table_index) := l_tmp_startdt;
384 x_sctitm_enddt_tbl(l_table_index) := l_tmp_enddt;
385 x_sctitm_assoc_startdt_tbl(l_table_index) := l_tmp_assoc_startdt;
386 x_sctitm_assoc_enddt_tbl(l_table_index) := l_tmp_assoc_enddt;
387 l_table_index := l_table_index + 1;
388 END LOOP;
389 CLOSE l_itmids_csr;
390
391 END LOOP;
392 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
393 IBE_UTIL.debug('IBE_CATALOG_PVT.After extracting info from output arrays');
394 END IF;
395 IF x_sctitm_itmid_tbl.COUNT = 0 THEN
396 x_sctitm_itmid_tbl := NULL;
397 END IF;
398 IF x_sctitm_orgid_tbl.COUNT = 0 THEN
399 x_sctitm_orgid_tbl := NULL;
400 END IF;
401
402 IF x_sctitm_sectid_tbl.COUNT = 0 THEN
403 x_sctitm_sectid_tbl := NULL;
404 END IF;
405 IF x_sctitm_flags_tbl.COUNT = 0 THEN
406 x_sctitm_flags_tbl := NULL;
407 END IF;
408 IF x_sctitm_startdt_tbl.COUNT = 0 THEN
409 x_sctitm_startdt_tbl := NULL;
410 END IF;
411 IF x_sctitm_enddt_tbl.COUNT = 0 THEN
412 x_sctitm_enddt_tbl := NULL;
413 END IF;
414 IF x_sctitm_assoc_startdt_tbl.COUNT = 0 THEN
415 x_sctitm_assoc_startdt_tbl := NULL;
416 END IF;
417 IF x_sctitm_assoc_enddt_tbl.COUNT = 0 THEN
418 x_sctitm_assoc_enddt_tbl := NULL;
419 END IF;
420
421
422 ----------------------
423 -- Standard cleanup tasks
424 ----------------------
425
426 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
427 IBE_UTIL.debug('IBE_CATALOG_PVT.Load_Sections(-)');
428 END IF;
429
430 -- standard call to get message count and if count is 1, get message info.
431 FND_MSG_PUB.Count_And_Get
432 ( p_encoded => FND_API.G_FALSE,
433 p_count => x_msg_count,
434 p_data => x_msg_data
435 );
436 EXCEPTION
437 WHEN FND_API.G_EXC_ERROR THEN
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 FND_MSG_PUB.Count_And_Get
440 ( p_encoded => FND_API.G_FALSE,
441 p_count => x_msg_count,
442 p_data => x_msg_data
443 );
444 --gzhang 08/08/2002, bug#2488246
445 --ibe_util.disable_debug;
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448 FND_MSG_PUB.Count_And_Get
449 ( p_encoded => FND_API.G_FALSE,
450 p_count => x_msg_count,
451 p_data => x_msg_data
452 );
453 --gzhang 08/08/2002, bug#2488246
454 --ibe_util.disable_debug;
455 WHEN OTHERS THEN
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
458 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
459 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
460 FND_MESSAGE.Set_Token('REASON', SQLERRM);
461 FND_MSG_PUB.Add;
462 IF FND_MSG_PUB.Check_Msg_Level
463 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
464 THEN FND_MSG_PUB.Add_Exc_Msg
465 ( G_PKG_NAME,
466 l_api_name
467 );
468 END IF;
469 FND_MSG_PUB.Count_And_Get
470 ( p_encoded => FND_API.G_FALSE,
471 p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474 END LOAD_SECTIONS;
475
476
477 -- Start of comments
478 -- API name : GetLeafSubSectIDs
479 -- Type : Private.
480 -- Function : Given a section id, drills down to the
481 -- leaf level and returns leaf level section ids.
482 -- If p_preview_flag = 'T' returns sections
483 -- whose status_code is 'PUBLISHED' or 'UNPUBLISHED'.
484 -- Otherwise, returns information for sections whose
485 -- status_code is 'PUBLISHED'
486 --
487 -- Pre-reqs : None.
488 -- Parameters :
489 -- IN : p_api_version IN NUMBER Required
490 -- p_init_msg_list IN VARCHAR2 Optional
491 -- Default = FND_API.G_FALSE
492 -- p_validation_level IN NUMBER Optional
493 -- Default = FND_API.G_VALID_LEVEL_FULL
494 -- p_preview_flag IN VARCHAR2 Optional
495 -- p_msite_id IN NUMBER
496 -- p_sectid IN NUMBER
497 --
498 -- OUT : x_return_status OUT VARCHAR2(1)
499 -- x_msg_count OUT NUMBER
500 -- x_msg_data OUT VARCHAR2(2000)
501 -- x_leafsubsectid_csr OUT IBE_CATALOG_REFCURSOR_CSR_TYPE
502 -- Record Type = (leaf_section_id NUMBER, sort_order NUMBER)
503 --
504 -- Version : Current version 1.0
505 --
506 -- previous version None
507 --
508 -- Initial version 1.0
509 --
510 -- Notes : Note text
511 --
512 -- End of comments
513
514 procedure GetLeafSubSectIDs
515 (
516 p_api_version IN NUMBER,
517 p_init_msg_list IN VARCHAR2 := NULL,
518 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
519 x_return_status OUT NOCOPY VARCHAR2,
520 x_msg_count OUT NOCOPY NUMBER,
521 x_msg_data OUT NOCOPY VARCHAR2,
522
523 p_preview_flag IN VARCHAR2 := NULL,
524 p_sectid IN NUMBER,
525 p_msite_id IN NUMBER,
526 x_leafsubsectid_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
527 ) IS
528 l_api_name CONSTANT VARCHAR2(30) := 'GetLeafSubSectIDs';
529 l_api_version CONSTANT NUMBER := 1.0;
530 l_leafsubsectid NUMBER;
531 l_table_index NUMBER;
532 l_init_msg_list VARCHAR2(5);
533 l_preview_flag VARCHAR2(5);
534
535 BEGIN
536 --gzhang 08/08/2002, bug#2488246
537 --ibe_util.enable_debug;
538 -- standard call to check for call compatibility
539 IF NOT FND_API.Compatible_API_Call (l_api_version,
540 p_api_version,
541 l_api_name,
542 G_PKG_NAME )
543 THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END IF;
546
547 IF p_init_msg_list IS NULL THEN
548 l_init_msg_list := FND_API.G_FALSE;
549 END IF;
550
551 IF p_preview_flag IS NULL THEN
552 l_preview_flag := FND_API.G_FALSE;
553 END IF;
554
555
556 -- initialize message list if l_init_msg_list is set to TRUE
557 IF FND_API.to_Boolean(l_init_msg_list) THEN
558 FND_MSG_PUB.initialize;
559 END IF;
560
561 -- initialize API return status to success
562 x_return_status := FND_API.G_RET_STS_SUCCESS;
563
564 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
565 IBE_UTIL.debug('IBE_CATALOG_PVT.GetLeafSubSectIDs(+)');
566 IBE_UTIL.debug('p_sectid : p_msite_id =' || p_sectid || ' : ' || p_msite_id);
567 END IF;
568
569 -- API Body
570 IF FND_API.to_Boolean(l_preview_flag) THEN
571 OPEN x_leafsubsectid_csr FOR
572 SELECT mss.child_section_id, mss.sort_order
573 FROM IBE_DSP_MSITE_SCT_SECTS mss, IBE_DSP_SECTIONS_B jdsb
574 WHERE mss.mini_site_id = p_msite_id
575 AND mss.child_section_id in
576 ( SELECT mss1.child_section_id
577 FROM IBE_DSP_MSITE_SCT_SECTS mss1
578 START WITH mss1.parent_section_id = p_sectid and mss1.mini_site_id = p_msite_id
579 CONNECT BY PRIOR mss1.child_section_id = mss1.parent_section_id
580 AND mss1.mini_site_id = p_msite_id
581 )
582 AND mss.child_section_id not in
583 (
584 SELECT mss2.parent_section_id
585 FROM IBE_DSP_MSITE_SCT_SECTS mss2
586 WHERE mss2.mini_site_id = p_msite_id
587 AND mss2.parent_section_id is not null
588 )
589 AND jdsb.section_id = mss.child_section_id
590 AND (jdsb.status_code = 'PUBLISHED' OR jdsb.status_code = 'UNPUBLISHED')
591 AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE
592 AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE
593 ORDER BY mss.sort_order;
594 ELSE
595 OPEN x_leafsubsectid_csr FOR
596 SELECT mss.child_section_id, mss.sort_order
597 FROM IBE_DSP_MSITE_SCT_SECTS mss, IBE_DSP_SECTIONS_B jdsb
598 WHERE mss.mini_site_id = p_msite_id
599 AND mss.child_section_id in
600 ( SELECT mss1.child_section_id
601 FROM IBE_DSP_MSITE_SCT_SECTS mss1
602 START WITH mss1.parent_section_id = p_sectid and mss1.mini_site_id = p_msite_id
603 CONNECT BY PRIOR mss1.child_section_id = mss1.parent_section_id
604 AND mss1.mini_site_id = p_msite_id
605 )
606 AND mss.child_section_id not in
607 (
608 SELECT mss2.parent_section_id
609 FROM IBE_DSP_MSITE_SCT_SECTS mss2
610 WHERE mss2.mini_site_id = p_msite_id
611 AND mss2.parent_section_id is not null
612 )
613 AND jdsb.section_id = mss.child_section_id
614 AND jdsb.status_code = 'PUBLISHED'
615 AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE
616 AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE
617 ORDER BY mss.sort_order;
618 END IF;
619
620 -- End API Body
621 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
622 IBE_UTIL.debug('IBE_CATALOG_PVT.GetLeafSubSectIDs(-)');
623 END IF;
624 -- standard call to get message count and if count is 1, get message info.
625 FND_MSG_PUB.Count_And_Get
626 ( p_encoded => FND_API.G_FALSE,
627 p_count => x_msg_count,
628 p_data => x_msg_data
629 );
630 --gzhang 08/08/2002, bug#2488246
631 --ibe_util.disable_debug;
632 EXCEPTION
633 WHEN FND_API.G_EXC_ERROR THEN
634 x_return_status := FND_API.G_RET_STS_ERROR;
635 FND_MSG_PUB.Count_And_Get
636 ( p_encoded => FND_API.G_FALSE,
637 p_count => x_msg_count,
638 p_data => x_msg_data
639 );
640 --gzhang 08/08/2002, bug#2488246
641 --ibe_util.disable_debug;
642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 FND_MSG_PUB.Count_And_Get
645 ( p_encoded => FND_API.G_FALSE,
646 p_count => x_msg_count,
647 p_data => x_msg_data
648 );
649 --gzhang 08/08/2002, bug#2488246
650 --ibe_util.disable_debug;
651 WHEN OTHERS THEN
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 IF FND_MSG_PUB.Check_Msg_Level
654 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
655 THEN FND_MSG_PUB.Add_Exc_Msg
656 ( G_PKG_NAME,
657 l_api_name
658 );
659 END IF;
660 FND_MSG_PUB.Count_And_Get
661 ( p_encoded => FND_API.G_FALSE,
662 p_count => x_msg_count,
663 p_data => x_msg_data
664 );
665 --gzhang 08/08/2002, bug#2488246
666 --ibe_util.disable_debug;
667 END GETLEAFSUBSECTIDS;
668
669
670
671 -- Start of comments
672 -- API name : GetSuperSectIDs
673 -- Type : Private.
674 -- Function : Given a section ID, returns the super sections up to
675 -- the root of the store, ordered from the section's immediate
676 -- parent to the root.
677 -- Pre-reqs : None.
678 -- Parameters :
679 -- IN : p_api_version IN NUMBER Required
680 -- p_init_msg_list IN VARCHAR2 Optional
681 -- Default = FND_API.G_FALSE
682 -- p_validation_level IN NUMBER Optional
683 -- Default = FND_API.G_VALID_LEVEL_FULL
684 -- p_sectid IN NUMBER Required
685 -- p_msite_id IN NUMBER Required
686 --
687 -- OUT : x_return_status OUT VARCHAR2(1)
688 -- x_msg_count OUT NUMBER
689 -- x_msg_data OUT VARCHAR2(2000)
690 -- x_supersectid_csr OUT IBE_CATALOG_REFCURSOR_CSR_TYPE
691 -- Record Type = IBE_ID_REC
692 --
693 -- Version : Current version 1.0
694 --
695 -- Previous version None
696 --
697 -- Initial version 1.0
698 --
699 -- Notes : Note text
700 --
701 -- End of comments
702
703 procedure GetSuperSectIDs
704 (p_api_version IN NUMBER,
705 p_init_msg_list IN VARCHAR2 := NULL,
706 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_count OUT NOCOPY NUMBER,
709 x_msg_data OUT NOCOPY VARCHAR2,
710 p_sectid IN NUMBER,
711 p_msite_id IN NUMBER,
712 x_supersectid_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
713 ) IS
714
715 l_api_name CONSTANT VARCHAR2(30) := 'GetSuperSectIDs';
716 l_api_version CONSTANT NUMBER := 1.0;
717 l_init_msg_list VARCHAR2(5);
718
719 BEGIN
720
721 ----------------------
722 -- Standard initialization tasks
723 ----------------------
724
725 -- Standard call to check for call compatibility.
726 IF NOT FND_API.Compatible_API_Call (l_api_version,
727 p_api_version,
728 l_api_name,
729 G_PKG_NAME )
730 THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733
734 IF p_init_msg_list IS NULL THEN
735 l_init_msg_list := FND_API.G_FALSE;
736 END IF;
737
738 -- Initialize message list if l_init_msg_list is set to TRUE.
739 IF FND_API.to_Boolean(l_init_msg_list) THEN
740 FND_MSG_PUB.initialize;
741 END IF;
742
743 -- Initialize API return status to success.
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745
746 -- Print debugging info.
747 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
748 IBE_UTIL.debug('IBE_CATALOG_PVT.GetSuperSectIDs(+)');
749 IBE_UTIL.debug('p_sectid : p_msite_id =' || p_sectid || ' : ' || p_msite_id);
750 END IF;
751
752
753 ----------------------
754 -- Supersection query
755 ----------------------
756
757 OPEN x_supersectid_csr FOR
758 SELECT mss.parent_section_id
759 FROM IBE_DSP_MSITE_SCT_SECTS mss
760 START WITH mss.child_section_id = p_sectid AND mss.mini_site_id = p_msite_id
761 CONNECT BY PRIOR mss.parent_section_id = mss.child_section_id
762 AND mss.mini_site_id = p_msite_id;
763
764
765 ----------------------
766 -- Standard cleanup tasks
767 ----------------------
768
769 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
770 IBE_UTIL.debug('IBE_CATALOG_PVT.GetSuperSectIDs(-)');
771 END IF;
772
773 -- standard call to get message count and if count is 1, get message info.
774 FND_MSG_PUB.Count_And_Get
775 ( p_encoded => FND_API.G_FALSE,
776 p_count => x_msg_count,
777 p_data => x_msg_data
778 );
779 EXCEPTION
780 WHEN FND_API.G_EXC_ERROR THEN
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 FND_MSG_PUB.Count_And_Get
783 ( p_encoded => FND_API.G_FALSE,
784 p_count => x_msg_count,
785 p_data => x_msg_data
786 );
787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789 FND_MSG_PUB.Count_And_Get
790 ( p_encoded => FND_API.G_FALSE,
791 p_count => x_msg_count,
792 p_data => x_msg_data
793 );
794 WHEN OTHERS THEN
795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
796 IF FND_MSG_PUB.Check_Msg_Level
797 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
798 THEN FND_MSG_PUB.Add_Exc_Msg
799 ( G_PKG_NAME,
800 l_api_name
801 );
802 END IF;
803 FND_MSG_PUB.Count_And_Get
804 ( p_encoded => FND_API.G_FALSE,
805 p_count => x_msg_count,
806 p_data => x_msg_data
807 );
808
809 END GetSuperSectIDs;
810
811
812
813 -- Start of comments
814 -- API name : GetAvailableServices
815 -- Type : Public
816 -- Function : retrieve Service Items related to this Item.
817 -- After a service item is set up, it is generally available to all
818 -- serviceable products. OKS provides functionalites to set up exclusion
819 -- between serviceable product and serivce item; exclusion between customer
820 -- and service item. This API will take the exclusion rules into account
821 -- as well.
822 --
823 -- Pre-reqs : None.
824 -- Parameters :
825 -- IN :
826 -- p_api_version IN NUMBER Required
827 -- p_init_msg_list IN VARCHAR2 Optional
828 -- Default = FND_API.G_FALSE
829 -- p_validation_level IN NUMBER Optional
830 -- Default = FND_API.G_VALID_LEVEL_FULL
831 -- p_preview_flag IN VARCHAR2 Optional
832 -- Default = FND_API.G_FALSE
833 -- p_originid IN NUMBER Required
834 -- p_origintype IN VARCHAR2(240) Required
835 -- p_reltype_code IN VARCHAR2(30) Required
836 -- p_dest_type IN VARCHAR2(240) Required
837 -- p_commit IN VARCHAR2 := FND_API.G_FALSE Optional
838 -- p_product_item_id IN NUMBER Required
839 -- p_customer_id IN NUMBER Optional,
840 -- p_product_revision IN VARCHAR2 Optional
841 -- p_request_date IN DATE Optional
842 --
843 --
844 -- OUT :
845 -- x_return_status OUT VARCHAR2(1)
846 -- x_msg_count OUT NUMBER
847 -- x_msg_data OUT VARCHAR2(2000)
848 -- x_service_item_ids OUT nocopy JTF_NUMBER_TABLE
849 --
850 -- Version : Current version 1.0
851 --
852 -- previous version None
853 --
854 -- Initial version 1.0
855 --
856 -- Notes : Note text
857 --
858 -- End of comments
859 PROCEDURE GetAvailableServices(
860 p_api_version_number IN NUMBER := 1,
861 p_init_msg_list IN VARCHAR2 := NULL,
862 p_commit IN VARCHAR2 := NULL,
863 x_return_status OUT NOCOPY VARCHAR2,
864 x_msg_count OUT NOCOPY NUMBER,
865 x_msg_data OUT NOCOPY VARCHAR2,
866 p_product_item_id IN NUMBER,
867 p_customer_id IN NUMBER,
868 p_product_revision IN VARCHAR2,
869 p_request_date IN DATE,
870 x_service_item_ids OUT NOCOPY JTF_NUMBER_TABLE
871 ) IS
872 --l_avail_service_rec ASO_SERVICE_CONTRACTS_INT.Avail_Service_Rec_Type;
873 --l_orderable_Service_tbl ASO_SERVICE_CONTRACTS_INT.order_service_tbl_type;
874 l_count NUMBER;
875 --new
876 l_api_version_number NUMBER := 1.0;
877 l_api_name CONSTANT VARCHAR2(50) := 'GetAvailableServices';
878
879 l_avail_service_rec OKS_OMINT_PUB.AVAIL_SERVICE_REC_TYPE;
880 l_Orderable_Service_tbl OKS_OMINT_PUB.order_service_tbl_type;
881 l_init_msg_list VARCHAR2(5);
882 L_commit VARCHAR2(5);
883
884 -- new
885 BEGIN
886 SAVEPOINT AVAILABLE_SERVICES_PUB;
887 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
888 IBE_UTIL.Debug('Start IBE_CATALOG_PVT.GetAvailableServices');
889 IBE_UTIL.Debug(' Parms: [p_product_item_id=' || p_product_item_id || ', ' ||
890 p_customer_id || ', ' || p_product_revision || ', ' ||
891 p_request_date || ']');
892 END IF;
893
894 IF p_init_msg_list IS NULL THEN
895 l_init_msg_list := FND_API.G_TRUE;
896 END IF;
897
898 IF p_commit IS NULL THEN
899 l_commit := FND_API.G_FALSE;
900 END IF;
901
902 -- Setting Rec values to be passed to OKS API
903
904 IF p_product_item_id = FND_API.G_MISS_NUM THEN
905 l_avail_service_rec.PRODUCT_ITEM_ID := NULL;
906 ELSE
907 l_avail_service_rec.PRODUCT_ITEM_ID := p_product_item_id;
908 END IF;
909 IF p_customer_id = FND_API.G_MISS_NUM THEN
910 l_avail_service_rec.CUSTOMER_ID := NULL;
911 ELSE
912 l_avail_service_rec.CUSTOMER_ID := p_customer_id;
913 END IF;
914 IF p_product_revision = FND_API.G_MISS_CHAR THEN
915 l_avail_service_rec.PRODUCT_REVISION := NULL;
916 ELSE
917 l_avail_service_rec.PRODUCT_REVISION := p_product_revision;
918 END IF;
919 IF p_request_date = FND_API.G_MISS_DATE THEN
920 l_avail_service_rec.request_date := NULL;
921 ELSE
922 l_avail_service_rec.request_date := p_request_date;
923 END IF;
924
925
926 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
927 IBE_UTIL.Debug('OKS_OMINT_PUB.Available_Services Starts');
928 END IF;
929
930
931 OKS_OMINT_PUB.Available_Services(
932 P_Api_Version => 1.0 ,
933 P_init_msg_list => l_init_msg_list,
934 X_msg_Count => X_msg_count ,
935 X_msg_Data => X_msg_data ,
936 X_Return_Status => X_return_status ,
937 p_avail_service_rec => l_avail_service_rec,
938 X_Orderable_Service_tbl => l_Orderable_Service_tbl
939 );
940
941
942 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
943 RAISE FND_API.G_EXC_ERROR;
944 END IF;
945 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947 END IF;
948
949 l_count := l_orderable_service_tbl.COUNT;
950 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
951 IBE_UTIL.Debug(' OKS_OMINT_PUB.Available_Services Finishes ' || x_return_status || ' ' ||
952 'l_orderable_service_tbl.COUNT=' || l_count);
953 END IF;
954
955 x_service_item_ids := JTF_NUMBER_TABLE();
956
957 IF l_count > 0 THEN
958 x_service_item_ids.extend(l_count);
959 FOR i IN 1..l_count LOOP
960 x_service_item_ids(i) := l_orderable_service_tbl(i).service_item_id;
961 END LOOP;
962 END IF;
963
964 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
965 IBE_UTIL.Debug('End IBE_CATALOG_PVT.GetAvailableServices');
966 END IF;
967
968 EXCEPTION
969 WHEN FND_API.G_EXC_ERROR THEN
970 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
971 IBE_Util.Debug('Expected error IBE_CATALOG_PVT.GetAvailableServices');
972 END IF;
973
974 ROLLBACK TO AVAILABLE_SERVICES_PUB;
975 x_return_status := FND_API.G_RET_STS_ERROR;
976 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
977 p_count => x_msg_count,
978 p_data => x_msg_data);
979 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
980 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
981 IBE_Util.Debug('Expected error IBE_CATALOG_PVT.GetAvailableServices');
982 END IF;
983
984 ROLLBACK TO AVAILABLE_SERVICES_PUB;
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
986 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
987 p_count => x_msg_count,
988 p_data => x_msg_data);
989 WHEN OTHERS THEN
990 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
991 IBE_Util.Debug('unknown error IBE_CATALOG_PVT.GetAvailableServices');
992 END IF;
993
994 ROLLBACK TO AVAILABLE_SERVICES_PUB;
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996
997 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
998 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
999 l_api_name);
1000 END IF;
1001
1002 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1003 p_count => x_msg_count,
1004 p_data => x_msg_data);
1005
1006 END GetAvailableServices;
1007
1008 -- Start of comments
1009 -- API name : GetRelatedCatOrSectIDs
1010 -- Type : Private.
1011 -- Function : Given an origin id, origin type, relationship type code,
1012 -- and destination object type, returns the ids
1013 -- of all objects of the given type related to the
1014 -- section by the given relationship code. This API
1015 -- should only be used for destination types 'S' (section)
1016 -- and 'C' (category). The p_preview_flag is only applicable
1017 -- when the destination object type is 'S' (section). If
1018 -- p_preview_flag is true, returns sections whose
1019 -- web_status is 'PUBLISHED' or 'UNPUBLISHED'.
1020 -- Otherwise, returns sections whose web_status is
1021 -- 'PUBLISHED'.
1022 -- Pre-reqs : None.
1023 -- Parameters :
1024 -- IN : p_api_version IN NUMBER Required
1025 -- p_init_msg_list IN VARCHAR2 Optional
1026 -- Default = FND_API.G_FALSE
1027 -- p_validation_level IN NUMBER Optional
1028 -- Default = FND_API.G_VALID_LEVEL_FULL
1029 -- p_preview_flag IN VARCHAR2 Optional
1030 -- Default = FND_API.G_FALSE
1031 -- p_originid IN NUMBER Required
1032 -- p_origintype IN VARCHAR2(240) Required
1033 -- p_reltype_code IN VARCHAR2(30) Required
1034 -- p_dest_type IN VARCHAR2(240) Required
1035 --
1036 -- OUT : x_return_status OUT VARCHAR2(1)
1037 -- x_msg_count OUT NUMBER
1038 -- x_msg_data OUT VARCHAR2(2000)
1039 -- x_relatedid_tbl OUT nocopy JTF_NUMBER_TABLE
1040 --
1041 -- Version : Current version 1.0
1042 --
1043 -- previous version None
1044 --
1045 -- Initial version 1.0
1046 --
1047 -- Notes : Note text
1048 --
1049 -- End of comments
1050 procedure GetRelatedCatOrSectIDs
1051 (
1052 p_api_version IN NUMBER,
1053 p_init_msg_list IN VARCHAR2 := NULL,
1054 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1055 x_return_status OUT NOCOPY VARCHAR2,
1056 x_msg_count OUT NOCOPY NUMBER,
1057 x_msg_data OUT NOCOPY VARCHAR2,
1058
1059 p_preview_flag IN VARCHAR2 := NULL,
1060 p_originid IN NUMBER,
1061 p_origintype IN VARCHAR2,
1062 p_reltype_code IN VARCHAR2,
1063 p_dest_type IN VARCHAR2,
1064 x_relatedid_tbl OUT NOCOPY JTF_NUMBER_TABLE
1065 ) IS
1066 cursor l_reltype_csr IS
1067 SELECT start_date_active, end_date_active
1068 FROM FND_LOOKUPS
1069 WHERE lookup_type = 'IBE_RELATIONSHIP_TYPES'
1070 AND lookup_code = p_reltype_code
1071 AND enabled_flag = 'Y';
1072
1073 l_relobj_stmt VARCHAR2(4000);
1074 l_relobj_csr IBE_CATALOG_REFCURSOR_CSR_TYPE;
1075 l_relobjid NUMBER;
1076 l_api_name CONSTANT VARCHAR2(30) := 'GetRelatedCatOrSectIDs';
1077 l_api_version CONSTANT NUMBER := 1.0;
1078 l_table_index NUMBER;
1079 l_rel_start_date_active DATE;
1080 l_rel_end_date_active DATE;
1081 l_init_msg_list VARCHAR2(5);
1082 l_preview_flag VARCHAR2(5);
1083
1084
1085 BEGIN
1086 --gzhang 08/08/2002, bug#2488246
1087 --ibe_util.enable_debug;
1088 -- standard call to check for call compatibility
1089 IF NOT FND_API.Compatible_API_Call (l_api_version,
1090 p_api_version,
1091 l_api_name,
1092 G_PKG_NAME )
1093 THEN
1094 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1095 END IF;
1096
1097 IF p_init_msg_list IS NULL THEN
1098 l_init_msg_list := FND_API.G_FALSE;
1099 END IF;
1100
1101 IF p_preview_flag IS NULL THEN
1102 l_preview_flag := FND_API.G_FALSE;
1103 END IF;
1104
1105 -- initialize message list if l_init_msg_list is set to TRUE
1106 IF FND_API.to_Boolean(l_init_msg_list) THEN
1107 FND_MSG_PUB.initialize;
1108 END IF;
1109
1110 -- initialize API return status to success
1111 x_return_status := FND_API.G_RET_STS_SUCCESS;
1112
1113 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1114 IBE_UTIL.debug('IBE_CATALOG_PVT.GETRELATEDCATORSECTIDS(+)');
1115 IBE_UTIL.debug('p_originid : p_origintype : p_reltype_code : p_dest_type ='
1116 || p_originid || ' : ' || p_origintype || ' : ' || p_reltype_code ||
1117 ' : ' || p_dest_type);
1118 END IF;
1119 -- API Body
1120
1121 -- check if relationship exists and is active
1122 OPEN l_reltype_csr;
1123 FETCH l_reltype_csr INTO l_rel_start_date_active, l_rel_end_date_active;
1124 IF l_reltype_csr%NOTFOUND THEN
1125 CLOSE l_reltype_csr;
1126 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
1127 FND_MESSAGE.Set_Token('RELATIONSHIP_TYPE', p_reltype_code);
1128 FND_MSG_PUB.Add;
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131 CLOSE l_reltype_csr;
1132
1133 -- if relationship type code is not active, return NULL table
1134 IF NVL(l_rel_start_date_active, SYSDATE) > SYSDATE
1135 OR NVL(l_rel_end_date_active, SYSDATE) < SYSDATE THEN
1136 x_relatedid_tbl := NULL;
1137 RETURN;
1138 END IF;
1139
1140 -- initialize return value x_relatedid_tbl
1141 x_relatedid_tbl := JTF_NUMBER_TABLE();
1142
1143 -- initialize table index
1144 l_table_index := 1;
1145
1146 IF p_dest_type = 'S' THEN
1147 IF FND_API.to_Boolean(l_preview_flag) THEN
1148 l_relobj_stmt := 'SELECT rr.DEST_OBJECT_ID ' ||
1149 'FROM IBE_CT_RELATION_RULES rr, IBE_DSP_SECTIONS_B jdsb ' ||
1150 'WHERE rr.ORIGIN_OBJECT_TYPE = :p_origintype ' ||
1151 'AND rr.ORIGIN_OBJECT_ID = :p_originid ' ||
1152 'AND rr.RELATION_TYPE_CODE = :p_reltype_code ' ||
1153 'AND rr.DEST_OBJECT_TYPE = :p_dest_type ' ||
1154 'AND rr.DEST_OBJECT_ID = jdsb.SECTION_ID ' ||
1155 'AND (jdsb.status_code = ''PUBLISHED'' OR jdsb.status_code = ''UNPUBLISHED'') ' ||
1156 'AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE ' ||
1157 'AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE ';
1158 ELSE
1159 l_relobj_stmt := 'SELECT rr.DEST_OBJECT_ID ' ||
1160 'FROM IBE_CT_RELATION_RULES rr, IBE_DSP_SECTIONS_B jdsb ' ||
1161 'WHERE rr.ORIGIN_OBJECT_TYPE = :p_origintype ' ||
1162 'AND rr.ORIGIN_OBJECT_ID = :p_originid ' ||
1163 'AND rr.RELATION_TYPE_CODE = :p_reltype_code ' ||
1164 'AND rr.DEST_OBJECT_TYPE = :p_dest_type ' ||
1165 'AND rr.DEST_OBJECT_ID = jdsb.SECTION_ID ' ||
1166 'AND jdsb.status_code = ''PUBLISHED'' ' ||
1167 'AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE ' ||
1168 'AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE ';
1169 END IF;
1170 ELSE
1171 l_relobj_stmt := 'SELECT rr.DEST_OBJECT_ID ' ||
1172 'FROM IBE_CT_RELATION_RULES rr ' ||
1173 'WHERE rr.ORIGIN_OBJECT_TYPE = :p_origintype ' ||
1174 'AND rr.ORIGIN_OBJECT_ID = :p_originid ' ||
1175 'AND rr.RELATION_TYPE_CODE = :p_reltype_code ' ||
1176 'AND rr.DEST_OBJECT_TYPE = :p_dest_type ';
1177 END IF;
1178
1179 OPEN l_relobj_csr FOR l_relobj_stmt
1180 USING p_origintype, p_originid, p_reltype_code, p_dest_type;
1181
1182 LOOP
1183
1184 FETCH l_relobj_csr INTO l_relobjid;
1185 EXIT WHEN l_relobj_csr%NOTFOUND;
1186 IF l_relobjid IS NOT NULL THEN
1187 x_relatedid_tbl.EXTEND;
1188 x_relatedid_tbl(l_table_index) := l_relobjid;
1189 l_table_index := l_table_index + 1;
1190 END IF;
1191
1192 END LOOP;
1193 CLOSE l_relobj_csr;
1194
1195 IF x_relatedid_tbl.COUNT = 0 THEN
1196 x_relatedid_tbl := NULL;
1197 END IF;
1198
1199 -- End API Body
1200 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1201 IBE_UTIL.debug('IBE_CATALOG_PVT.GETRELATEDCATORSECTIDS(-)');
1202 END IF ;
1203
1204 -- standard call to get messgae count and if count is 1, get message info.
1205 FND_MSG_PUB.Count_And_Get
1206 ( p_encoded => FND_API.G_FALSE,
1207 p_count => x_msg_count,
1208 p_data => x_msg_data
1209 );
1210 --gzhang 08/08/2002, bug#2488246
1211 --ibe_util.disable_debug;
1212 EXCEPTION
1213 WHEN FND_API.G_EXC_ERROR THEN
1214 x_return_status := FND_API.G_RET_STS_ERROR;
1215 FND_MSG_PUB.Count_And_Get
1216 ( p_encoded => FND_API.G_FALSE,
1217 p_count => x_msg_count,
1218 p_data => x_msg_data
1219 );
1220 --gzhang 08/08/2002, bug#2488246
1221 --ibe_util.disable_debug;
1222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1224 FND_MSG_PUB.Count_And_Get
1225 ( p_encoded => FND_API.G_FALSE,
1226 p_count => x_msg_count,
1227 p_data => x_msg_data
1228 );
1229 --gzhang 08/08/2002, bug#2488246
1230 --ibe_util.disable_debug;
1231 WHEN OTHERS THEN
1232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233 IF FND_MSG_PUB.Check_Msg_Level
1234 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1235 THEN FND_MSG_PUB.Add_Exc_Msg
1236 ( G_PKG_NAME,
1237 l_api_name
1238 );
1239 END IF;
1240 FND_MSG_PUB.Count_And_Get
1241 ( p_encoded => FND_API.G_FALSE,
1242 p_count => x_msg_count,
1243 p_data => x_msg_data
1244 );
1245 --gzhang 08/08/2002, bug#2488246
1246 --ibe_util.disable_debug;
1247 END GetRelatedCatOrSectIDs;
1248
1249 -- Start of comments
1250 -- API name : Get_Basic_Item_Load_Query
1251 -- Type : Private.
1252 -- Function : Returns select and from clauses for an item load query when given
1253 -- the load level and category set id.
1254 -- Pre-reqs : None.
1255 -- Parameters :
1256 -- IN : p_load_level IN NUMBER
1257 -- Possible Values: G_ITEM_SHALLOW, G_ITEM_DEEP, G_ITEM_DEEP_ONLY
1258 -- p_category_set_id IN NUMBER
1259 --
1260 -- OUT : x_basic_query
1261 --
1262 -- Version : Current version 1.0
1263 --
1264 -- previous version None
1265 --
1266 -- Initial version 1.0
1267 --
1268 -- Notes : Note text
1269 --
1270 -- End of comments
1271 procedure Get_Basic_Item_Load_Query
1272 (
1273 p_load_level IN NUMBER,
1274 x_basic_query OUT NOCOPY VARCHAR2
1275 ) IS
1276
1277 L_BASIC_SHALLOW_QUERY CONSTANT VARCHAR2(1000) :=
1278 'SELECT MSIV.CONFIG_MODEL_TYPE, MSIV.BOM_ENABLED_FLAG, MSIV.ORDERABLE_ON_WEB_FLAG, MSIV.BACK_ORDERABLE_FLAG, ' ||
1279 'MSIV.PRIMARY_UNIT_OF_MEASURE, MSIV.PRIMARY_UOM_CODE, ' ||
1280 'MSIV.ITEM_TYPE, MSIV.BOM_ITEM_TYPE, ' ||
1281 'MSIV.INDIVISIBLE_FLAG, MSIV.SERIAL_NUMBER_CONTROL_CODE, MSIV.WEB_STATUS, ' ||
1282 'MSIV.CONCATENATED_SEGMENTS, MSIV.INVENTORY_ITEM_ID, '||
1283 --gzhang 10/24/2002, ER#2474216
1284 'MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG,MSIV.SERVICE_DURATION_PERIOD_CODE,MSIV.SERVICE_DURATION, '||
1285 'MSIV.SHIPPABLE_ITEM_FLAG,MSIV.INVOICEABLE_ITEM_FLAG,MSIV.INVOICE_ENABLED_FLAG, MSIV.START_DATE_ACTIVE, MSIV.END_DATE_ACTIVE '||
1286 'FROM MTL_SYSTEM_ITEMS_VL MSIV ';
1287
1288
1289 L_BASIC_DEEP_QUERY CONSTANT VARCHAR2(2100) :=
1290 'SELECT MSIV.CONFIG_MODEL_TYPE, MSIV.BOM_ENABLED_FLAG, MSIV.ORDERABLE_ON_WEB_FLAG, MSIV.BACK_ORDERABLE_FLAG, ' ||
1291 'MSIV.PRIMARY_UNIT_OF_MEASURE, MSIV.PRIMARY_UOM_CODE, ' ||
1292 'MSIV.ITEM_TYPE, MSIV.BOM_ITEM_TYPE, ' ||
1293 'MSIV.INDIVISIBLE_FLAG, MSIV.SERIAL_NUMBER_CONTROL_CODE, MSIV.WEB_STATUS, ' ||
1294 'MSIV.CONCATENATED_SEGMENTS, MSIV.INVENTORY_ITEM_ID, '||
1295
1296 --gzhang 10/24/2002, ER#2474216
1297 'MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG,MSIV.SERVICE_DURATION_PERIOD_CODE,MSIV.SERVICE_DURATION, '||
1298
1299 'MSIV.SHIPPABLE_ITEM_FLAG,MSIV.INVOICEABLE_ITEM_FLAG,MSIV.INVOICE_ENABLED_FLAG, MSIV.TAXABLE_FLAG, MSIV.ATP_FLAG, MSIV.RETURNABLE_FLAG, ' ||
1300 'MSIV.DOWNLOADABLE_FLAG, MSIV.MINIMUM_ORDER_QUANTITY, ' ||
1301 'MSIV.MAXIMUM_ORDER_QUANTITY, MSIV.FIXED_ORDER_QUANTITY, ' ||
1302 'MSIV.SERVICE_STARTING_DELAY, MSIV.SEGMENT1, MSIV.SEGMENT2, MSIV.SEGMENT3, MSIV.SEGMENT4, ' ||
1303 'MSIV.SEGMENT5, MSIV.SEGMENT6, MSIV.SEGMENT7, MSIV.SEGMENT8, MSIV.SEGMENT9, MSIV.SEGMENT10, ' ||
1304 'MSIV.SEGMENT11, MSIV.SEGMENT12, MSIV.SEGMENT13, MSIV.SEGMENT14, MSIV.SEGMENT15, MSIV.SEGMENT16, ' ||
1305 'MSIV.SEGMENT17, MSIV.SEGMENT18, MSIV.SEGMENT19, MSIV.SEGMENT20, MSIV.ATTRIBUTE1, ' ||
1306 'MSIV.ATTRIBUTE2, MSIV.ATTRIBUTE3, MSIV.ATTRIBUTE4, MSIV.ATTRIBUTE5, MSIV.ATTRIBUTE6, ' ||
1307 'MSIV.ATTRIBUTE7, MSIV.ATTRIBUTE8, MSIV.ATTRIBUTE9, MSIV.ATTRIBUTE10, MSIV.ATTRIBUTE11, ' ||
1308 'MSIV.ATTRIBUTE12, MSIV.ATTRIBUTE13, MSIV.ATTRIBUTE14, MSIV.ATTRIBUTE15, MSIV.ATTRIBUTE_CATEGORY, ' ||
1309 'MSIV.COUPON_EXEMPT_FLAG, MSIV.VOL_DISCOUNT_EXEMPT_FLAG, MSIV.ELECTRONIC_FLAG, ' ||
1310 'MSIV.GLOBAL_ATTRIBUTE_CATEGORY, ' ||
1311 'MSIV.GLOBAL_ATTRIBUTE1, MSIV.GLOBAL_ATTRIBUTE2, MSIV.GLOBAL_ATTRIBUTE3, MSIV.GLOBAL_ATTRIBUTE4, ' ||
1312 'MSIV.GLOBAL_ATTRIBUTE5, MSIV.GLOBAL_ATTRIBUTE6, MSIV.GLOBAL_ATTRIBUTE7, MSIV.GLOBAL_ATTRIBUTE8, ' ||
1313 'MSIV.GLOBAL_ATTRIBUTE9, MSIV.GLOBAL_ATTRIBUTE10 FROM MTL_SYSTEM_ITEMS_VL MSIV ';
1314
1315
1316 L_BASIC_DEEP_ONLY_QUERY CONSTANT VARCHAR2(2000) := --gzhang 10/24/2002, ER#2474216
1317 'SELECT MSIV.TAXABLE_FLAG, MSIV.ATP_FLAG, MSIV.RETURNABLE_FLAG, ' ||
1318 'MSIV.DOWNLOADABLE_FLAG, MSIV.MINIMUM_ORDER_QUANTITY, MSIV.MAXIMUM_ORDER_QUANTITY, MSIV.FIXED_ORDER_QUANTITY, ' ||
1319 'MSIV.SERVICE_STARTING_DELAY, MSIV.SEGMENT1, MSIV.SEGMENT2, MSIV.SEGMENT3, MSIV.SEGMENT4, ' ||
1320 'MSIV.SEGMENT5, MSIV.SEGMENT6, MSIV.SEGMENT7, MSIV.SEGMENT8, MSIV.SEGMENT9, MSIV.SEGMENT10, ' ||
1321 'MSIV.SEGMENT11, MSIV.SEGMENT12, MSIV.SEGMENT13, MSIV.SEGMENT14, MSIV.SEGMENT15, MSIV.SEGMENT16, ' ||
1322 'MSIV.SEGMENT17, MSIV.SEGMENT18, MSIV.SEGMENT19, MSIV.SEGMENT20, MSIV.ATTRIBUTE1, ' ||
1323 'MSIV.ATTRIBUTE2, MSIV.ATTRIBUTE3, MSIV.ATTRIBUTE4, MSIV.ATTRIBUTE5, MSIV.ATTRIBUTE6, ' ||
1324 'MSIV.ATTRIBUTE7, MSIV.ATTRIBUTE8, MSIV.ATTRIBUTE9, MSIV.ATTRIBUTE10, MSIV.ATTRIBUTE11, ' ||
1325 'MSIV.ATTRIBUTE12, MSIV.ATTRIBUTE13, MSIV.ATTRIBUTE14, MSIV.ATTRIBUTE15, MSIV.ATTRIBUTE_CATEGORY, ' ||
1326 'MSIV.COUPON_EXEMPT_FLAG, MSIV.VOL_DISCOUNT_EXEMPT_FLAG, MSIV.ELECTRONIC_FLAG, ' ||
1327 'MSIV.GLOBAL_ATTRIBUTE_CATEGORY, ' ||
1328 'MSIV.GLOBAL_ATTRIBUTE1, MSIV.GLOBAL_ATTRIBUTE2, MSIV.GLOBAL_ATTRIBUTE3, MSIV.GLOBAL_ATTRIBUTE4, ' ||
1329 'MSIV.GLOBAL_ATTRIBUTE5, MSIV.GLOBAL_ATTRIBUTE6, MSIV.GLOBAL_ATTRIBUTE7, MSIV.GLOBAL_ATTRIBUTE8, ' ||
1330 'MSIV.GLOBAL_ATTRIBUTE9, MSIV.GLOBAL_ATTRIBUTE10, MSIV.CONCATENATED_SEGMENTS, ' ||
1331 'MSIV.INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_VL MSIV ';
1332
1333 BEGIN
1334 IF p_load_level = G_ITEM_SHALLOW THEN
1335 x_basic_query := L_BASIC_SHALLOW_QUERY;
1336 ELSE
1337 IF p_load_level = G_ITEM_DEEP THEN
1338 x_basic_query := L_BASIC_DEEP_QUERY;
1339 ELSE
1340 x_basic_query := L_BASIC_DEEP_ONLY_QUERY;
1341 END IF;
1342 END IF;
1343
1344 END GET_BASIC_ITEM_LOAD_QUERY;
1345
1346 -- Start of comments
1347 -- API name : Process_Order_By_Clause
1348 -- Type : Private.
1349 -- Function : Takes comma separated list of columns (with option asc or desc) of
1350 -- MTL_SYSTEM_ITEMS_VL and appends 'MSIV.' in front of each column name so
1351 -- it can be used in the order by clause of a query that joins with
1352 -- MTL_SYSTEM_ITEMS_VL.
1353 -- Pre-reqs : None.
1354 -- Parameters :
1355 -- IN : p_order_by_clause IN VARCHAR2
1356 --
1357 --
1358 -- OUT : x_order_by_clause OUT VARCHAR2
1359 --
1360 --
1361 -- Version : Current version 1.0
1362 --
1363 -- previous version None
1364 --
1365 -- Initial version 1.0
1366 --
1367 -- Notes : Note text
1368 --
1369 -- End of comments
1370 procedure Process_Order_By_Clause
1371 (p_order_by_clause IN VARCHAR2,
1372 x_order_by_clause OUT NOCOPY VARCHAR2
1373 ) IS
1374 l_start NUMBER := 1;
1375 l_position NUMBER := 1;
1376 l_token VARCHAR2(2000) := NULL;
1377 l_counter NUMBER := 1;
1378
1379 begin
1380 x_order_by_clause := NULL;
1381 IF p_order_by_clause IS NULL THEN
1382 return;
1383 END IF;
1384
1385 WHILE l_position <> 0 LOOP
1386 l_position := INSTR(p_order_by_clause, ',', 1, l_counter);
1387 IF l_position <> 0 THEN
1388 l_token := substr(p_order_by_clause, l_start, l_position - l_start + 1);
1389 ELSE
1390 l_token := substr(p_order_by_clause, l_start);
1391 END IF;
1392 l_token := LTRIM(l_token);
1393
1394 IF (l_token IS NULL OR l_token = '') THEN
1395 NULL; --no-op if NULL or empty
1396 ELSE
1397 x_order_by_clause := x_order_by_clause || ' MSIV.' || l_token;
1398 END IF;
1399 l_start := l_position + 1;
1400 l_counter := l_counter + 1;
1401 END LOOP;
1402 end Process_Order_By_Clause;
1403
1404 -- Start of comments
1405 -- API name : Get_Format_Mask_and_Symbol
1406 -- Type : Private.
1407 -- Function : Given currency code and length, retrieves format mask and
1408 -- currency symbol. Uses FND_CURRENCY.get_format_mask().
1409 -- Pre-reqs : None.
1410 -- Parameters :
1411 -- IN : p_api_version IN NUMBER Required
1412 -- p_init_msg_list IN VARCHAR2 Optional
1413 -- Default = FND_API.G_FALSE
1414 -- p_validation_level IN NUMBER Optional
1415 -- Default = FND_API.G_VALID_LEVEL_FULL
1416 -- p_currency_code IN NUMBER
1417 -- p_length IN NUMBER
1418 --
1419 -- OUT : x_return_status OUT VARCHAR2(1)
1420 -- x_msg_count OUT NUMBER
1421 -- x_msg_data OUT VARCHAR2(2000)
1422 -- x_format_mask OUT nocopy VARCHAR2
1423 -- x_currency_symbol OUT nocopy VARCHAR2
1424 -- Version : Current version 1.0
1425 --
1426 -- previous version None
1427 --
1428 -- Initial version 1.0
1429 --
1430 -- Notes : Note text
1431 --
1432 -- End of comments
1433 procedure Get_Format_Mask_and_Symbol
1434 (p_api_version IN NUMBER,
1435 p_init_msg_list IN VARCHAR2 := NULL,
1436 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1437 x_return_status OUT NOCOPY VARCHAR2,
1438 x_msg_count OUT NOCOPY NUMBER,
1439 x_msg_data OUT NOCOPY VARCHAR2,
1440
1441 p_currency_code IN VARCHAR2,
1442 p_length IN NUMBER,
1443 x_format_mask OUT nocopy VARCHAR2,
1444 x_currency_symbol OUT nocopy VARCHAR2
1445 ) IS
1446
1447 cursor l_currency_symbol_csr(l_currency_code VARCHAR2) IS
1448 SELECT fc.symbol FROM FND_CURRENCIES fc
1449 WHERE fc.currency_code = l_currency_code;
1450
1451 l_api_name CONSTANT VARCHAR2(30) := 'Get_Format_Mask_and_Symbol';
1452 l_api_version CONSTANT NUMBER := 1.0;
1453 l_init_msg_list VARCHAR2(5);
1454
1455 BEGIN
1456 --gzhang 08/08/2002, bug#2488246
1457 --ibe_util.enable_debug;
1458 -- standard call to check for call compatibility
1459 IF NOT FND_API.Compatible_API_Call (l_api_version,
1460 p_api_version,
1461 l_api_name,
1462 G_PKG_NAME )
1463 THEN
1464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1465 END IF;
1466 IF p_init_msg_list IS NULL THEN
1467 l_init_msg_list := FND_API.G_FALSE;
1468 END IF;
1469
1470 -- initialize message list if l_init_msg_list is set to TRUE
1471 IF FND_API.to_Boolean(l_init_msg_list) THEN
1472 FND_MSG_PUB.initialize;
1473 END IF;
1474
1475 -- initialize API return status to success
1476 x_return_status := FND_API.G_RET_STS_SUCCESS;
1477
1478 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1479 IBE_UTIL.debug('IBE_CATALOG_PVT.Get_Format_Mask_and_Symbol(+)');
1480 IBE_UTIL.debug('p_currency_code : p_length = ' || p_currency_code ||
1481 ' : ' || p_length);
1482 END IF;
1483 -- begin API body
1484 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1485 IBE_UTIL.debug('Calling FND_CURRENCY.Get_Format_Mask ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
1486 END IF;
1487 x_format_mask := FND_CURRENCY.Get_Format_Mask(p_currency_code, p_length);
1488 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1489 IBE_UTIL.debug('Return from FND_CURRENCY.Get_Format_Mask ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
1490 END IF;
1491 OPEN l_currency_symbol_csr(p_currency_code);
1492 FETCH l_currency_symbol_csr INTO x_currency_symbol;
1493 CLOSE l_currency_symbol_csr;
1494
1495 -- end API body
1496 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1497 IBE_UTIL.debug('IBE_CATALOG_PVT.Get_Format_Mask_and_Symbol(-)');
1498 END IF;
1499 -- standard call to get message count and if count is 1, get message info.
1500 FND_MSG_PUB.Count_And_Get
1501 ( p_encoded => FND_API.G_FALSE,
1502 p_count => x_msg_count,
1503 p_data => x_msg_data
1504 );
1505 --gzhang 08/08/2002, bug#2488246
1506 --ibe_util.disable_debug;
1507 EXCEPTION
1508 WHEN FND_API.G_EXC_ERROR THEN
1509 x_return_status := FND_API.G_RET_STS_ERROR;
1510 FND_MSG_PUB.Count_And_Get
1511 ( p_encoded => FND_API.G_FALSE,
1512 p_count => x_msg_count,
1513 p_data => x_msg_data
1514 );
1515 --gzhang 08/08/2002, bug#2488246
1516 --ibe_util.disable_debug;
1517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1519 FND_MSG_PUB.Count_And_Get
1520 ( p_encoded => FND_API.G_FALSE,
1521 p_count => x_msg_count,
1522 p_data => x_msg_data
1523 );
1524 --gzhang 08/08/2002, bug#2488246
1525 --ibe_util.disable_debug;
1526 WHEN OTHERS THEN
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1528 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1529 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1530 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1531 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1532 FND_MSG_PUB.Add;
1533 IF FND_MSG_PUB.Check_Msg_Level
1534 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1535 THEN FND_MSG_PUB.Add_Exc_Msg
1536 ( G_PKG_NAME,
1537 l_api_name
1538 );
1539 END IF;
1540 FND_MSG_PUB.Count_And_Get
1541 ( p_encoded => FND_API.G_FALSE,
1542 p_count => x_msg_count,
1543 p_data => x_msg_data
1544 );
1545 --gzhang 08/08/2002, bug#2488246
1546 --ibe_util.disable_debug;
1547 end Get_Format_Mask_and_Symbol;
1548
1549 procedure validate_quantity
1550 (p_api_version IN NUMBER,
1551 p_init_msg_list IN VARCHAR2 := NULL,
1552 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1553 x_return_status OUT NOCOPY VARCHAR2,
1554 x_msg_count OUT NOCOPY NUMBER,
1555 x_msg_data OUT NOCOPY VARCHAR2,
1556
1557 p_item_id_tbl IN JTF_NUMBER_TABLE,
1558 p_organization_id_tbl IN JTF_NUMBER_TABLE,
1559 p_qty_tbl IN JTF_NUMBER_TABLE,
1560 p_uom_code_tbl IN JTF_VARCHAR2_TABLE_100,
1561 x_valid_qty_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
1562 ) is
1563
1564 l_api_name CONSTANT VARCHAR2(30) := 'validate_quantity';
1565 l_api_version CONSTANT NUMBER := 1.0;
1566 l_output_qty NUMBER;
1567 l_primary_qty NUMBER;
1568 l_init_msg_list VARCHAR2(5);
1569 begin
1570
1571 --gzhang 08/08/2002, bug#2488246
1572 --ibe_util.enable_debug;
1573 -- standard call to check for call compatibility
1574 IF NOT FND_API.Compatible_API_Call (l_api_version,
1575 p_api_version,
1576 l_api_name,
1577 G_PKG_NAME )
1578 THEN
1579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1580 END IF;
1581
1582 IF p_init_msg_list IS NULL THEN
1583 l_init_msg_list := FND_API.G_FALSE;
1584 END IF;
1585
1586 -- initialize message list if L_init_msg_list is set to TRUE
1587 IF FND_API.to_Boolean(l_init_msg_list) THEN
1588 FND_MSG_PUB.initialize;
1589 END IF;
1590
1591 -- initialize API return status to success
1592 x_return_status := FND_API.G_RET_STS_SUCCESS;
1593
1594 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1595 IBE_UTIL.debug('IBE_CATALOG_PVT.validate_quantity(+)');
1596 END IF;
1597 -- begin API body
1598
1599 x_valid_qty_tbl := JTF_VARCHAR2_TABLE_100();
1600 x_valid_qty_tbl.extend(p_item_id_tbl.count);
1601
1602 for i in 1..p_item_id_tbl.count loop
1603
1604 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1605 IBE_UTIL.debug('Calling INV_DECIMALS_PUB.validate_quantity ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
1606 END IF;
1607 inv_decimals_pub.validate_quantity(p_item_id_tbl(i), p_organization_id_tbl(i), p_qty_tbl(i),
1608 p_uom_code_tbl(i), l_output_qty, l_primary_qty, x_valid_qty_tbl(i));
1609 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1610 IBE_UTIL.debug('Return from INV_DECIMALS_PUB.validate_quantity ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
1611 END IF;
1612 end loop;
1613
1614 -- end API body
1615 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1616 IBE_UTIL.debug('IBE_CATALOG_PVT.validate_quantity(-)');
1617 END IF;
1618
1619 -- standard call to get message count and if count is 1, get message info.
1620 FND_MSG_PUB.Count_And_Get
1621 ( p_encoded => FND_API.G_FALSE,
1622 p_count => x_msg_count,
1623 p_data => x_msg_data
1624 );
1625 --gzhang 08/08/2002, bug#2488246
1626 --ibe_util.disable_debug;
1627 EXCEPTION
1628 WHEN FND_API.G_EXC_ERROR THEN
1629 x_return_status := FND_API.G_RET_STS_ERROR;
1630 FND_MSG_PUB.Count_And_Get
1631 ( p_encoded => FND_API.G_FALSE,
1632 p_count => x_msg_count,
1633 p_data => x_msg_data
1634 );
1635 --gzhang 08/08/2002, bug#2488246
1636 --ibe_util.disable_debug;
1637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1639 FND_MSG_PUB.Count_And_Get
1640 ( p_encoded => FND_API.G_FALSE,
1641 p_count => x_msg_count,
1642 p_data => x_msg_data
1643 );
1644 --gzhang 08/08/2002, bug#2488246
1645 --ibe_util.disable_debug;
1646 WHEN OTHERS THEN
1647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1648 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1649 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1650 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1651 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1652 FND_MSG_PUB.Add;
1653 IF FND_MSG_PUB.Check_Msg_Level
1654 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1655 THEN FND_MSG_PUB.Add_Exc_Msg
1656 (G_PKG_NAME,
1657 l_api_name
1658 );
1659 END IF;
1660 FND_MSG_PUB.Count_And_Get
1661 ( p_encoded => FND_API.G_FALSE,
1662 p_count => x_msg_count,
1663 p_data => x_msg_data
1664 );
1665 --gzhang 08/08/2002, bug#2488246
1666 --ibe_util.disable_debug;
1667 end validate_quantity;
1668
1669 --Bug 3063233
1670 procedure validate_de_qty_msite_check
1671 (p_api_version IN NUMBER,
1672 p_init_msg_list IN VARCHAR2 := NULL,
1673 p_reqd_validation IN JTF_VARCHAR2_TABLE_100,
1674 p_msite_id IN NUMBER,
1675 x_return_status OUT NOCOPY VARCHAR2,
1676 x_msg_count OUT NOCOPY NUMBER,
1677 x_msg_data OUT NOCOPY VARCHAR2,
1678 p_item_id_tbl IN JTF_NUMBER_TABLE,
1679 p_organization_id_tbl IN JTF_NUMBER_TABLE,
1680 p_qty_tbl IN JTF_NUMBER_TABLE,
1681 p_uom_code_tbl IN JTF_VARCHAR2_TABLE_100,
1682 x_valid_qty_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
1683 ) is
1684
1685 l_api_name CONSTANT VARCHAR2(30) := 'validate_de_qty_msite_check';
1686 l_api_version CONSTANT NUMBER := 1.0;
1687 l_output_qty NUMBER;
1688 l_primary_qty NUMBER;
1689 l_item_exists NUMBER;
1690 l_init_msg_list VARCHAR2(5);
1691
1692 begin
1693
1694 --gzhang 08/08/2002, bug#2488246
1695 --ibe_util.enable_debug;
1696 -- standard call to check for call compatibility
1697 IF NOT FND_API.Compatible_API_Call (l_api_version,
1698 p_api_version,
1699 l_api_name,
1700 G_PKG_NAME )
1701 THEN
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703 END IF;
1704
1705 IF p_init_msg_list IS NULL THEN
1706 l_init_msg_list := FND_API.G_FALSE;
1707 END IF;
1708
1709 -- initialize message list if L_init_msg_list is set to TRUE
1710 IF FND_API.to_Boolean(l_init_msg_list) THEN
1711 FND_MSG_PUB.initialize;
1712 END IF;
1713
1714 -- initialize API return status to success
1715 x_return_status := FND_API.G_RET_STS_SUCCESS;
1716
1717 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1718 IBE_UTIL.debug('IBE_CATALOG_PVT.validate_quantity(+)');
1719 END IF;
1720
1721 -- begin API body
1722
1723 x_valid_qty_tbl := JTF_VARCHAR2_TABLE_100();
1724 x_valid_qty_tbl.extend(p_item_id_tbl.count);
1725
1726 for i in 1..p_item_id_tbl.count loop
1727
1728 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1729 IBE_UTIL.debug('Calling INV_DECIMALS_PUB.validate_quantity ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
1730 END IF;
1731 inv_decimals_pub.validate_quantity(p_item_id_tbl(i), p_organization_id_tbl(i), p_qty_tbl(i),
1732 p_uom_code_tbl(i), l_output_qty, l_primary_qty, x_valid_qty_tbl(i));
1733 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1734 IBE_UTIL.debug('Return from INV_DECIMALS_PUB.validate_quantity ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
1735 END IF;
1736 end loop;
1737
1738 -- end API body
1739 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1740 IBE_UTIL.debug('IBE_CATALOG_PVT.validate_quantity(-)');
1741 END IF;
1742
1743 for i in 1..x_valid_qty_tbl.count loop
1744 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1745 IBE_UTIL.debug('checking the value in x_valid_qty_tbl');
1746 END IF;
1747 if (x_valid_qty_tbl(i) = 'S') then
1748 x_valid_qty_tbl(i) := '';
1749 else
1750 x_valid_qty_tbl(i) := 'IBE_PRMT_SC_DE_ITEM_QTY_INV';
1751 end if;
1752 end loop;
1753
1754 -- standard call to get message count and if count is 1, get message info.
1755 FND_MSG_PUB.Count_And_Get
1756 ( p_encoded => FND_API.G_FALSE,
1757 p_count => x_msg_count,
1758 p_data => x_msg_data
1759 );
1760 --gzhang 08/08/2002, bug#2488246
1761 --ibe_util.disable_debug;
1762
1763 --do the check to see whether item belongs to a section in the minisite
1764 FOR i in 1..p_item_id_tbl.count LOOP
1765 IF (x_valid_qty_tbl(i) IS null or
1766 x_valid_qty_tbl(i)= '') THEN
1767 BEGIN
1768 SELECT count(s.inventory_item_id)
1769 INTO l_item_exists
1770 FROM ibe_dsp_section_items s, ibe_dsp_msite_sct_items b
1771 WHERE s.section_item_id = b.section_item_id
1772 AND b.mini_site_id = p_msite_id
1773 AND s.inventory_item_id = p_item_id_tbl(i)
1774 AND (s.end_date_active > sysdate or s.end_date_active is null )
1775 AND s.start_date_active < sysdate;
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 x_valid_qty_tbl(i) := 'IBE_PRMT_SC_DE_ITEM_NSETUP';
1779 END;
1780
1781 IF( l_item_exists <= 0 ) THEN
1782 --item does not exist in some section
1783 x_valid_qty_tbl(i) := 'IBE_PRMT_SC_DE_ITEM_NSETUP';
1784 END IF;
1785 END IF;
1786 END LOOP;
1787 EXCEPTION
1788 WHEN FND_API.G_EXC_ERROR THEN
1789 x_return_status := FND_API.G_RET_STS_ERROR;
1790 FND_MSG_PUB.Count_And_Get
1791 ( p_encoded => FND_API.G_FALSE,
1792 p_count => x_msg_count,
1793 p_data => x_msg_data
1794 );
1795 --gzhang 08/08/2002, bug#2488246
1796 --ibe_util.disable_debug;
1797 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1799 FND_MSG_PUB.Count_And_Get
1800 ( p_encoded => FND_API.G_FALSE,
1801 p_count => x_msg_count,
1802 p_data => x_msg_data
1803 );
1804 --gzhang 08/08/2002, bug#2488246
1805 --ibe_util.disable_debug;
1806 WHEN OTHERS THEN
1807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1808 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1809 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1810 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1811 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1812 FND_MSG_PUB.Add;
1813 IF FND_MSG_PUB.Check_Msg_Level
1814 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1815 THEN FND_MSG_PUB.Add_Exc_Msg
1816 (G_PKG_NAME,
1817 l_api_name
1818 );
1819 END IF;
1820 FND_MSG_PUB.Count_And_Get
1821 ( p_encoded => FND_API.G_FALSE,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data
1824 );
1825 --gzhang 08/08/2002, bug#2488246
1826 --ibe_util.disable_debug;
1827 end validate_de_qty_msite_check;
1828
1829 -- gzhang, 04/23/02, new APIs for Global Store Selection phase 2
1830 procedure load_msite_languages
1831 (x_lang_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1832 x_tran_lang_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1833 x_desc_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300 --gzhang 07/19/2002, bug#2469521
1834 )
1835 IS
1836 CURSOR l_lang_csr IS
1837 SELECT language_code, language, description
1838 FROM fnd_languages_tl t
1839 WHERE EXISTS (SELECT NULL FROM ibe_msite_languages m WHERE m.language_code = t.language_code)
1840 ORDER BY language_code;
1841
1842 l_index NUMBER := 1;
1843
1844 BEGIN
1845 --gzhang 08/08/2002, bug#2488246
1846 --ibe_util.enable_debug;
1847 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1848 IBE_UTIL.debug('In IBE_CATALOG_PVT.load_msite_languages...');
1849 END IF;
1850
1851 x_lang_code_tbl := JTF_VARCHAR2_TABLE_100();
1852 x_tran_lang_code_tbl := JTF_VARCHAR2_TABLE_100();
1853 x_desc_tbl := JTF_VARCHAR2_TABLE_300(); --gzhang 07/19/2002, bug#2469521
1854
1855 FOR l_lang_rec IN l_lang_csr LOOP
1856
1857 x_lang_code_tbl.EXTEND;
1858 x_tran_lang_code_tbl.EXTEND;
1859 x_desc_tbl.EXTEND;
1860
1861 x_lang_code_tbl(l_index) := l_lang_rec.language_code;
1862 x_tran_lang_code_tbl(l_index) := l_lang_rec.language;
1863 x_desc_tbl(l_index) := l_lang_rec.description;
1864
1865 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1866 IBE_UTIL.debug('language code='||x_lang_code_tbl(l_index)||',translated language code='||x_tran_lang_code_tbl(l_index)||',desc='||x_desc_tbl(l_index));
1867 END IF;
1868
1869 l_index := l_index + 1;
1870 END LOOP;
1871 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1872 IBE_UTIL.debug('IBE_CATALOG_PVT.load_msite_languages:done');
1873 END IF;
1874 --gzhang 08/08/2002, bug#2488246
1875 --ibe_util.disable_debug;
1876 EXCEPTION
1877 WHEN OTHERS THEN
1878 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1879 IBE_UTIL.debug('Exception in IBE_CATALOG_PVT.load_msite_languages');
1880 END IF;
1881 --gzhang 08/08/2002, bug#2488246
1882 --ibe_util.disable_debug;
1883
1884 END load_msite_languages;
1885
1886 procedure load_language
1887 (p_lang_code IN VARCHAR2,
1888 x_tran_lang_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1889 x_desc_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300, --gzhang 07/19/2002, bug#2469521
1890 x_nls_lang OUT NOCOPY VARCHAR2 --jqu 1/19/2005
1891 )
1892 IS
1893 CURSOR l_lang_csr (l_lang_code VARCHAR2) IS
1894 SELECT t.language, t.description
1895 FROM fnd_languages_tl t
1896 WHERE language_code = l_lang_code;
1897 CURSOR l_nls_lang_csr (l_lang_code VARCHAR2) IS
1898 SELECT nls_language
1899 FROM fnd_languages
1900 WHERE language_code = l_lang_code;
1901
1902 l_index NUMBER := 1;
1903
1904 BEGIN
1905 --gzhang 08/08/2002, bug#2488246
1906 --ibe_util.enable_debug;
1907 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1908 IBE_UTIL.debug('In IBE_CATALOG_PVT.LOAD_LANGUAGE...');
1909 IBE_UTIL.debug('p_lang_code ='||p_lang_code);
1910 END IF;
1911
1912 x_tran_lang_code_tbl := JTF_VARCHAR2_TABLE_100();
1913 x_desc_tbl := JTF_VARCHAR2_TABLE_300(); --gzhang 07/19/2002, bug#2469521
1914
1915 FOR l_lang_rec IN l_lang_csr(p_lang_code) LOOP
1916
1917 x_tran_lang_code_tbl.EXTEND;
1918 x_desc_tbl.EXTEND;
1919
1920 x_tran_lang_code_tbl(l_index) := l_lang_rec.language;
1921 x_desc_tbl(l_index) := l_lang_rec.description;
1922
1923 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1924 IBE_UTIL.debug('translated language code='||x_tran_lang_code_tbl(l_index)||',desc='||x_desc_tbl(l_index));
1925 END IF;
1926
1927 l_index := l_index + 1;
1928 END LOOP;
1929
1930 OPEN l_nls_lang_csr(p_lang_code);
1931 FETCH l_nls_lang_csr INTO x_nls_lang;
1932 CLOSE l_nls_lang_csr;
1933
1934 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1935 IBE_UTIL.debug('IBE_CATALOG_PVT.LOAD_LANGUAGE:done');
1936 END IF;
1937 --gzhang 08/08/2002, bug#2488246
1938 --ibe_util.disable_debug;
1939 EXCEPTION
1940 WHEN OTHERS THEN
1941 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1942 IBE_UTIL.debug('Exception in IBE_CATALOG_PVT.load_language');
1943 END IF ;
1944 --gzhang 08/08/2002, bug#2488246
1945 --ibe_util.disable_debug;
1946
1947 END load_language;
1948 -- gzhang, 04/23/02, end of new APIs for Global Store Selection phase 2
1949
1950
1951
1952 --integration with QP_TEMP_TABLE
1953 procedure FETCH_ITEM
1954 (p_api_version IN NUMBER,
1955 p_init_msg_list IN VARCHAR2 := NULL,
1956 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1957 x_return_status OUT NOCOPY VARCHAR2,
1958 x_msg_count OUT NOCOPY NUMBER,
1959 x_msg_data OUT NOCOPY VARCHAR2,
1960
1961 p_load_level IN NUMBER,
1962 p_preview_flag IN VARCHAR2,
1963 p_itmid IN NUMBER,
1964 p_partnum IN VARCHAR2,
1965 p_model_id IN NUMBER := FND_API.G_MISS_NUM,
1966 p_organization_id IN NUMBER,
1967 p_category_set_id IN NUMBER,
1968 x_item_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
1969 x_category_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
1970 x_configurable OUT NOCOPY VARCHAR2,
1971 x_model_bundle_flag OUT NOCOPY VARCHAR2,
1972 x_uom_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
1973
1974 ) IS
1975
1976 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_ITEM';
1977 l_api_version CONSTANT NUMBER := 1.0;
1978
1979 cursor l_itmid_csr(p_item_partnum VARCHAR2) IS
1980 select MSIV.inventory_item_id
1981 from mtl_system_items_vl MSIV
1982 where MSIV.concatenated_segments = p_item_partnum;
1983
1984 l_itmid NUMBER;
1985 l_itm_stmt VARCHAR2(32767);
1986 l_ui_def_id NUMBER;
1987 l_resp_id NUMBER;
1988 l_resp_appl_id NUMBER;
1989 l_retrieve_all_uom VARCHAR2(10);
1990 l_start_time NUMBER;
1991 l_end_time NUMBER;
1992 l_init_msg_list VARCHAR2(5);
1993
1994 BEGIN
1995 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
1996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1997 END IF;
1998
1999 IF p_init_msg_list IS NULL THEN
2000 l_init_msg_list := FND_API.G_FALSE;
2001 END IF;
2002
2003 -- initialize message list if L_init_msg_list is set to TRUE
2004 IF FND_API.to_Boolean(l_init_msg_list) THEN
2005 FND_MSG_PUB.initialize;
2006 END IF;
2007
2008 -- initialize API return status to success
2009 x_return_status := FND_API.G_RET_STS_SUCCESS;
2010
2011
2012 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2013 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEM(+)');
2014 END IF;
2015 l_start_time := DBMS_UTILITY.GET_TIME;
2016
2017 -- get the select and from clauses of the query
2018 Get_Basic_Item_Load_Query(p_load_level, l_itm_stmt);
2019
2020 IF p_itmid IS NULL THEN
2021 IF p_partnum IS NULL THEN
2022 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2023 IBE_UTIL.debug('Error: p_itm_id and p_accessname are both NULL');
2024 END IF;
2025 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_ID_OR_NAME');
2026 FND_MESSAGE.Set_Token('ID_NAME', p_partnum);
2027 FND_MSG_PUB.Add;
2028 RAISE FND_API.G_EXC_ERROR;
2029 ELSE
2030 --loading by part number
2031
2032 -- need to get item id
2033 OPEN l_itmid_csr(p_partnum);
2034 FETCH l_itmid_csr INTO l_itmid;
2035 CLOSE l_itmid_csr;
2036
2037 END IF;
2038 ELSE
2039 -- loading by item_id
2040 l_itmid := p_itmid;
2041 END IF;
2042
2043 -- add check in where clause for organization id, active dates, web_status
2044 l_itm_stmt := l_itm_stmt || 'WHERE MSIV.INVENTORY_ITEM_ID = :p_itmid ' ||
2045 ' AND MSIV.ORGANIZATION_ID = :p_organization_id ';
2046
2047 IF NOT FND_API.to_Boolean(p_preview_flag) THEN
2048 l_itm_stmt := l_itm_stmt || ' AND MSIV.WEB_STATUS = ''PUBLISHED'' ';
2049 END IF;
2050
2051 l_itm_stmt := l_itm_stmt ||
2052 ' AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE ' ||
2053 ' AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE ';
2054
2055 -- open the item cursor for return
2056 OPEN x_item_csr FOR l_itm_stmt USING l_itmid, p_organization_id;
2057
2058 -- open category id cursor for return if category set id is not null
2059 IF (p_category_set_id IS NOT NULL) THEN
2060 OPEN x_category_id_csr FOR
2061 SELECT MSIV.INVENTORY_ITEM_ID, mic.CATEGORY_ID
2062 FROM MTL_SYSTEM_ITEMS_VL MSIV, MTL_ITEM_CATEGORIES mic
2063 WHERE MSIV.INVENTORY_ITEM_ID = l_itmid
2064 AND MSIV.ORGANIZATION_ID = p_organization_id
2065 AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
2066 AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
2067 AND MSIV.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
2068 AND MSIV.ORGANIZATION_ID = mic.ORGANIZATION_ID
2069 AND mic.CATEGORY_SET_ID = p_category_set_id;
2070 END IF;
2071
2072 -- open uom cursor for return if SHALLOW or DEEP load
2073 l_retrieve_all_uom := fnd_profile.value_specific('IBE_RETRIEVE_ALL_ITEM_UOMS', NULL, NULL, 671);
2074 IF ((l_retrieve_all_uom IS NULL) OR (l_retrieve_all_uom = 'Y')) THEN
2075 IF ((p_load_level = G_ITEM_SHALLOW) OR (p_load_level = G_ITEM_DEEP)) THEN
2076 OPEN x_uom_csr FOR
2077 SELECT miuv.INVENTORY_ITEM_ID, miuv.UOM_CODE
2078 FROM MTL_ITEM_UOMS_VIEW miuv
2079 WHERE miuv.INVENTORY_ITEM_ID = l_itmid
2080 AND miuv.ORGANIZATION_ID = p_organization_id
2081 ORDER BY miuv.UOM_CODE;
2082 END IF;
2083 END IF;
2084
2085 -- call configurator API
2086 l_resp_id := FND_PROFILE.value('RESP_ID');
2087 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
2088 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2089 IBE_UTIL.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2090 ibe_util.debug('item id=' || l_itmid);
2091 ibe_util.debug('organization id=' || p_organization_id);
2092 ibe_util.debug('responsibility id=' || l_resp_id);
2093 ibe_util.debug('application id=' || l_resp_appl_id);
2094 END IF;
2095
2096 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (l_itmid, p_organization_id, SYSDATE,
2097 'DHTML', FND_API.G_MISS_NUM, l_resp_id, l_resp_appl_id);
2098 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2099 IBE_UTIL.debug('Return from CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2100 ibe_util.debug('ui_def_id=' || l_ui_def_id);
2101 END IF;
2102 IF l_ui_def_id IS NULL THEN
2103 x_configurable := FND_API.G_FALSE;
2104 ELSE
2105 x_configurable := FND_API.G_TRUE;
2106 END IF;
2107
2108 IF /*l_bom_item_type = 1 AND*/ x_configurable = FND_API.G_FALSE THEN
2109 x_model_bundle_flag := IBE_CCTBOM_PVT.Is_Model_Bundle(p_api_version =>1.0, p_model_id =>l_itmid, p_organization_id => p_organization_id);
2110 ELSE
2111 x_model_bundle_flag := FND_API.G_FALSE;
2112 END IF;
2113
2114 l_end_time := DBMS_UTILITY.GET_TIME;
2115 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2116 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEM(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2117 END IF;
2118 --end API body
2119
2120 -- standard call to get message count and if count is 1, get message info.
2121 FND_MSG_PUB.Count_And_Get
2122 ( p_encoded => FND_API.G_FALSE,
2123 p_count => x_msg_count,
2124 p_data => x_msg_data
2125 );
2126 EXCEPTION
2127 WHEN FND_API.G_EXC_ERROR THEN
2128 x_return_status := FND_API.G_RET_STS_ERROR;
2129 FND_MSG_PUB.Count_And_Get
2130 ( p_encoded => FND_API.G_FALSE,
2131 p_count => x_msg_count,
2132 p_data => x_msg_data
2133 );
2134 --gzhang 08/08/2002, bug#2488246
2135 --ibe_util.disable_debug;
2136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2138 FND_MSG_PUB.Count_And_Get
2139 ( p_encoded => FND_API.G_FALSE,
2140 p_count => x_msg_count,
2141 p_data => x_msg_data
2142 );
2143 --gzhang 08/08/2002, bug#2488246
2144 --ibe_util.disable_debug;
2145 WHEN OTHERS THEN
2146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2148 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2149 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2150 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2151 FND_MSG_PUB.Add;
2152 IF FND_MSG_PUB.Check_Msg_Level
2153 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2154 THEN FND_MSG_PUB.Add_Exc_Msg
2155 ( G_PKG_NAME,
2156 l_api_name
2157 );
2158 END IF;
2159 FND_MSG_PUB.Count_And_Get
2160 ( p_encoded => FND_API.G_FALSE,
2161 p_count => x_msg_count,
2162 p_data => x_msg_data
2163 );
2164 --gzhang 08/08/2002, bug#2488246
2165 --ibe_util.disable_debug;
2166 END FETCH_ITEM;
2167
2168 Procedure FETCH_PRICE
2169 (p_itmid IN NUMBER,
2170 p_model_bundle_flag IN VARCHAR2,
2171 p_model_id IN NUMBER := FND_API.G_MISS_NUM,
2172 p_organization_id IN NUMBER,
2173 p_price_list_id IN NUMBER,
2174 p_currency_code IN VARCHAR2,
2175 p_price_request_type IN VARCHAR2,
2176 p_price_event IN VARCHAR2,
2177 p_minisite_id IN NUMBER := NULL,
2178 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2179 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2180 x_return_status OUT NOCOPY VARCHAR2,
2181 x_return_status_text OUT NOCOPY VARCHAR2
2182 )
2183 IS
2184
2185 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_PRICE';
2186 l_api_version CONSTANT NUMBER := 1.0;
2187
2188 l_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2189 l_itmid_tbl QP_PREQ_GRP.NUMBER_TYPE;
2190 l_model_id_tbl JTF_NUMBER_TABLE;
2191 l_model_id NUMBER;
2192 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
2193 idx BINARY_INTEGER;
2194 l_parentIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2195 l_childIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2196
2197 CURSOR l_item_uom_csr IS
2198 SELECT miuv.INVENTORY_ITEM_ID, miuv.UOM_CODE
2199 FROM MTL_ITEM_UOMS_VIEW miuv
2200 WHERE miuv.INVENTORY_ITEM_ID = p_itmid
2201 AND miuv.ORGANIZATION_ID = p_organization_id
2202 ORDER BY miuv.UOM_CODE;
2203
2204 CURSOR l_primary_uom_csr IS
2205 SELECT MSIV.primary_uom_code
2206 FROM mtl_system_items_vl MSIV
2207 WHERE MSIV.inventory_item_id = p_itmid;
2208
2209 l_retrieve_all_uom VARCHAR2(10);
2210 l_primary_uom VARCHAR2(40);
2211 l_bom_item_csr IBE_CCTBOM_PVT.IBE_CCTBOM_REF_CSR_TYPE;
2212 l_bom_exp_rec IBE_CCTBOM_PVT.IBE_BOM_EXPLOSION_REC;
2213 l_msg_data VARCHAR2(100);
2214 l_msg_count NUMBER;
2215 l_return_status VARCHAR2(30);
2216 l_start_time NUMBER;
2217 l_end_time NUMBER;
2218
2219
2220 BEGIN
2221 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2222 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_PRICE(+), item ='||p_itmid);
2223 END IF;
2224 l_start_time := DBMS_UTILITY.GET_TIME;
2225
2226 --don't convert model_id=-1 here
2227 --IF p_model_id = -1 THEN
2228 --l_model_id := FND_API.G_MISS_NUM;
2229 --ELSE
2230 l_model_id := p_model_id;
2231 --END IF;
2232 l_model_id_tbl := JTF_NUMBER_TABLE();
2233 idx := 1;
2234 l_retrieve_all_uom := fnd_profile.value_specific('IBE_RETRIEVE_ALL_ITEM_UOMS', NULL, NULL, 671);
2235 IF p_model_bundle_flag = FND_API.G_TRUE OR l_retrieve_all_uom = 'N' THEN
2236 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2237 IBE_UTIL.debug('pricing for primary uom only...');
2238 END IF;
2239 OPEN l_primary_uom_csr;
2240 FETCH l_primary_uom_csr INTO l_primary_uom;
2241 IF l_primary_uom_csr%FOUND THEN
2242 l_uom_code_tbl(idx) := l_primary_uom;
2243 ELSE
2244 l_uom_code_tbl(idx) := FND_API.G_MISS_CHAR;
2245 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2246 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':no primary uom code found');
2247 END IF;
2248 END IF;
2249 CLOSE l_primary_uom_csr;
2250
2251 l_itmid_tbl(idx) := p_itmid;
2252 l_line_quantity_tbl(idx) := 1;
2253 l_model_id_tbl.EXTEND;
2254 l_model_id_tbl(idx) := l_model_id;
2255 idx := idx + 1;
2256 IF p_model_bundle_flag = FND_API.G_TRUE THEN
2257 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2258 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':loading component items...');
2259 END IF;
2260 IBE_CCTBOM_PVT.Load_Components(p_api_version =>1.0,
2261 x_return_status=>l_return_status,
2262 x_msg_data=>l_msg_data,
2263 x_msg_count =>l_msg_count,
2264 p_model_id =>p_itmid,
2265 p_organization_id =>p_organization_id,
2266 x_item_csr =>l_bom_item_csr);
2267 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2268 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2269 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':adding component items..., idx='||idx);
2270 END IF;
2271 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2272 WHILE l_bom_item_csr%FOUND LOOP
2273 l_itmid_tbl(idx) := l_bom_exp_rec.component_item_id;
2274 l_uom_code_tbl(idx) := l_bom_exp_rec.primary_uom_code;
2275 l_line_quantity_tbl(idx) := l_bom_exp_rec.component_quantity;
2276 l_model_id_tbl.EXTEND;
2277 l_model_id_tbl(idx) := p_itmid;
2278 idx := idx + 1;
2279 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2280 END LOOP;
2281 CLOSE l_bom_item_csr;
2282 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2283 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':component items added to request line, idx='||idx);
2284 END IF;
2285 ELSE
2286 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2287 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':Failed to load component items');
2288 END IF;
2289 RAISE FND_API.G_EXC_ERROR;
2290 END IF;
2291 END IF;
2292 ELSE
2293 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2294 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': pricing for all uom codes...');
2295 END IF;
2296 FOR uom_rec IN l_item_uom_csr LOOP
2297 l_uom_code_tbl(idx) := uom_rec.UOM_CODE;
2298 l_itmid_tbl(idx) := uom_rec.INVENTORY_ITEM_ID;
2299 l_line_quantity_tbl(idx) := 1;
2300 l_model_id_tbl.extend;
2301 l_model_id_tbl(idx) := l_model_id;
2302 idx := idx + 1;
2303 END LOOP;
2304 END IF;
2305
2306 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2307 IBE_UTIL.debug('Calling IBE_PRICE_PVT.PRICE_REQUEST...');
2308 END IF;
2309 IBE_PRICE_PVT.PRICE_REQUEST(
2310 p_price_list_id => p_price_list_id,
2311 p_currency_code => p_currency_code,
2312 p_item_tbl => l_itmid_tbl,
2313 p_uom_code_tbl => l_uom_code_tbl,
2314 p_model_id_tbl => l_model_id_tbl,
2315 p_line_quantity_tbl => l_line_quantity_tbl,
2316 p_parentIndex_tbl => l_parentIndex_tbl,
2317 p_childIndex_tbl => l_childIndex_tbl,
2318 p_request_type_code => p_price_request_type,
2319 p_pricing_event => p_price_event,
2320 p_minisite_id => p_minisite_id,
2321 x_price_csr => x_price_csr,
2322 x_line_index_tbl => x_line_index_tbl,
2323 x_return_status => x_return_status,
2324 x_return_status_text=> x_return_status_text);
2325
2326 l_end_time := DBMS_UTILITY.GET_TIME;
2327 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2328 IBE_UTIL.debug('Return from IBE_PRICE_PVT.PRICE_REQUEST');
2329 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_PRICE(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2330 END IF;
2331 --end API body
2332
2333 EXCEPTION
2334 WHEN OTHERS THEN
2335 x_return_status := FND_API.G_RET_STS_ERROR;
2336 x_return_status_text :=SQLERRM;
2337 END FETCH_PRICE;
2338
2339 Procedure LOAD_ITEM
2340 (p_api_version IN NUMBER,
2341 p_init_msg_list IN VARCHAR2 := NULL,
2342 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2343 x_return_status OUT NOCOPY VARCHAR2,
2344 x_msg_count OUT NOCOPY NUMBER,
2345 x_msg_data OUT NOCOPY VARCHAR2,
2346
2347 p_load_level IN NUMBER,
2348 p_preview_flag IN VARCHAR2,
2349 p_itmid IN NUMBER,
2350 p_partnum IN VARCHAR2,
2351 p_model_id IN NUMBER := FND_API.G_MISS_NUM,
2352 p_organization_id IN NUMBER,
2353 p_category_set_id IN NUMBER,
2354 p_retrieve_price IN VARCHAR2,
2355
2356 p_price_list_id IN NUMBER,
2357 p_currency_code IN VARCHAR2,
2358 p_price_request_type IN VARCHAR2,
2359 p_price_event IN VARCHAR2,
2360 p_minisite_id IN NUMBER := NULL,
2361 x_item_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
2362 x_category_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
2363 x_configurable OUT NOCOPY VARCHAR2,
2364 x_model_bundle_flag OUT NOCOPY VARCHAR2,
2365 x_uom_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
2366 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2367 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2368 x_price_status_code OUT NOCOPY VARCHAR2,
2369 x_price_status_text OUT NOCOPY VARCHAR2
2370
2371 )
2372 IS
2373
2374 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_ITEM';
2375 l_api_version CONSTANT NUMBER := 1.0;
2376
2377 l_uom_code_tbl JTF_VARCHAR2_TABLE_100;
2378 l_itmid_tbl JTF_NUMBER_TABLE;
2379 l_model_id_tbl JTF_NUMBER_TABLE;
2380 idx BINARY_INTEGER;
2381 l_start_time NUMBER;
2382 l_end_time NUMBER;
2383 l_init_msg_list VARCHAR2(5);
2384
2385 BEGIN
2386 -- standard call to check for call compatibility
2387 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
2388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2389 END IF;
2390 IF p_init_msg_list IS NULL THEN
2391 l_init_msg_list := FND_API.G_FALSE;
2392 END IF;
2393
2394 -- initialize message list if p_init_msg_list is set to TRUE
2395 IF FND_API.to_Boolean(p_init_msg_list) THEN
2396 FND_MSG_PUB.initialize;
2397 END IF;
2398
2399 -- initialize API return status to success
2400 x_return_status := FND_API.G_RET_STS_SUCCESS;
2401
2402 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2403 IBE_UTIL.debug('IBE_CATALOG_PVT.LOAD_ITEM(+)');
2404 END IF;
2405 l_start_time := DBMS_UTILITY.GET_TIME;
2406 -- load item inventory info
2407 FETCH_ITEM(
2408 p_api_version,
2409 p_init_msg_list,
2410 p_validation_level,
2411 x_return_status,
2412 x_msg_count,
2413 x_msg_data,
2414 p_load_level,
2415 p_preview_flag,
2416 p_itmid,
2417 p_partnum,
2418 p_model_id,
2419 p_organization_id,
2420 p_category_set_id,
2421 x_item_csr,
2422 x_category_id_csr,
2423 x_configurable,
2424 x_model_bundle_flag,
2425 x_uom_csr
2426 );
2427
2428
2429 IF FND_API.to_Boolean(p_retrieve_price) THEN
2430 FETCH_PRICE(
2431 p_itmid =>p_itmid,
2432 p_model_bundle_flag => x_model_bundle_flag,
2433 p_model_id =>p_model_id,
2434 p_organization_id => p_organization_id,
2435 p_price_list_id => p_price_list_id,
2436 p_currency_code => p_currency_code,
2437 p_price_request_type => p_price_request_type,
2438 p_price_event => p_price_event,
2439 p_minisite_id => p_minisite_id,
2440 x_price_csr => x_price_csr,
2441 x_line_index_tbl=> x_line_index_tbl,
2442 x_return_status => x_price_status_code,
2443 x_return_status_text => x_price_status_text);
2444 END IF;
2445
2446 l_end_time := DBMS_UTILITY.GET_TIME;
2447 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2448 IBE_UTIL.debug('IBE_CATALOG_PVT.LOAD_ITEM(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2449 END IF;
2450 --end API body
2451
2452 -- standard call to get message count and if count is 1, get message info.
2453 FND_MSG_PUB.Count_And_Get
2454 ( p_encoded => FND_API.G_FALSE,
2455 p_count => x_msg_count,
2456 p_data => x_msg_data
2457 );
2458 EXCEPTION
2459 WHEN FND_API.G_EXC_ERROR THEN
2460 x_return_status := FND_API.G_RET_STS_ERROR;
2461 FND_MSG_PUB.Count_And_Get
2462 ( p_encoded => FND_API.G_FALSE,
2463 p_count => x_msg_count,
2464 p_data => x_msg_data
2465 );
2466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2468 FND_MSG_PUB.Count_And_Get
2469 ( p_encoded => FND_API.G_FALSE,
2470 p_count => x_msg_count,
2471 p_data => x_msg_data
2472 );
2473 WHEN OTHERS THEN
2474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2475 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2476 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2477 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2478 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2479 FND_MSG_PUB.Add;
2480 IF FND_MSG_PUB.Check_Msg_Level
2481 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2482 THEN FND_MSG_PUB.Add_Exc_Msg
2483 ( G_PKG_NAME,
2484 l_api_name
2485 );
2486 END IF;
2487 FND_MSG_PUB.Count_And_Get
2488 ( p_encoded => FND_API.G_FALSE,
2489 p_count => x_msg_count,
2490 p_data => x_msg_data
2491 );
2492 END LOAD_ITEM;
2493
2494
2495
2496 Procedure FETCH_ITEMS(
2497 p_load_level IN NUMBER,
2498 p_itmid_tbl IN JTF_NUMBER_TABLE,
2499 p_partnum_tbl IN JTF_VARCHAR2_TABLE_100,
2500 p_organization_id IN NUMBER,
2501 p_category_set_id IN NUMBER,
2502 x_category_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
2503 x_configurable_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2504 x_model_bundle_flag_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2505 x_return_status OUT NOCOPY VARCHAR2,
2506 x_return_status_text OUT NOCOPY VARCHAR2
2507 )
2508 IS
2509 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_ITEMS';
2510 l_api_version CONSTANT NUMBER := 1.0;
2511 l_ui_def_id NUMBER;
2512
2513 cursor l_itmid_csr(l_partnum VARCHAR2) IS
2514 select MSIV.inventory_item_id
2515 from mtl_system_items_vl MSIV
2516 where MSIV.concatenated_segments = l_partnum;
2517
2518 cursor l_category_id_csr(l_itmid NUMBER,
2519 l_organization_id NUMBER,
2520 l_category_set_id NUMBER) IS
2521 select MIC.category_id
2522 FROM MTL_SYSTEM_ITEMS_B MSIB, MTL_ITEM_CATEGORIES MIC
2523 WHERE MSIB.inventory_item_id = l_itmid
2524 AND MSIB.organization_id = l_organization_id
2525 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
2526 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
2527 AND MSIB.inventory_item_id = MIC.inventory_item_id
2528 AND MSIB.organization_id = MIC.organization_id
2529 AND mic.CATEGORY_SET_ID = l_category_set_id;
2530
2531 l_itmid NUMBER;
2532 l_itmid_tbl JTF_NUMBER_TABLE;
2533 l_table_index NUMBER;
2534 l_partnum VARCHAR2(40);
2535 l_category_id NUMBER;
2536 l_resp_id NUMBER;
2537 l_resp_appl_id NUMBER;
2538 l_itmid_index NUMBER;
2539 l_start_time NUMBER;
2540 l_end_time NUMBER;
2541
2542 BEGIN
2543
2544 -- initialize API return status to success
2545 x_return_status := FND_API.G_RET_STS_SUCCESS;
2546
2547 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2548 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEMS(+)');
2549 END IF;
2550 l_start_time := DBMS_UTILITY.GET_TIME;
2551
2552 -- get the select and from clauses of the query
2553 l_itmid_tbl := JTF_NUMBER_TABLE();
2554
2555 IF p_itmid_tbl IS NULL THEN
2556 IF p_partnum_tbl IS NULL THEN
2557 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2558 IBE_UTIL.debug('Error: p_itmid_tbl and p_partnum_tbl are both NULL');
2559 END IF;
2560 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_ID_OR_NAME');
2561 FND_MESSAGE.Set_Token('ID_NAME', 'NULL');
2562 FND_MSG_PUB.Add;
2563 RAISE FND_API.G_EXC_ERROR;
2564 RETURN;
2565 ELSE
2566 --loading by part number
2567 l_itmid_index := 1;
2568
2569 FOR l_table_index IN 1..p_partnum_tbl.COUNT LOOP
2570
2571 l_partnum := p_partnum_tbl(l_table_index);
2572 OPEN l_itmid_csr(l_partnum);
2573 FETCH l_itmid_csr INTO l_itmid;
2574
2575 -- constuct item id tbl
2576 IF l_itmid_csr%FOUND THEN
2577 l_itmid_tbl.EXTEND;
2578 l_itmid_tbl(l_table_index) := l_itmid;
2579
2580 ELSE
2581 l_itmid_tbl.EXTEND;
2582 l_itmid_tbl(l_table_index) := FND_API.G_MISS_NUM;
2583 END IF;
2584 CLOSE l_itmid_csr;
2585
2586 END LOOP;
2587 END IF;
2588 --p_itmid_tbl := l_itmid_tbl;
2589 ELSE
2590 -- loading by item_id
2591 l_itmid_tbl := p_itmid_tbl;
2592
2593 END IF;
2594
2595 -- populate x_category_id_tbl
2596 x_category_id_tbl := NULL;
2597 IF (p_category_set_id IS NOT NULL) THEN
2598 x_category_id_tbl := JTF_NUMBER_TABLE();
2599 FOR j IN 1..l_itmid_tbl.COUNT LOOP
2600 OPEN l_category_id_csr(l_itmid_tbl(j),
2601 p_organization_id,
2602 p_category_set_id);
2603 FETCH l_category_id_csr INTO l_category_id;
2604 x_category_id_tbl.extend;
2605 IF l_category_id_csr%FOUND THEN
2606 x_category_id_tbl(j) := l_category_id;
2607 ELSE
2608 x_category_id_tbl(j) := -1;
2609 END IF;
2610 CLOSE l_category_id_csr;
2611 END LOOP;
2612 END IF;
2613
2614 --gzhang 01/21/01, model bundle cache
2615 x_configurable_tbl := JTF_VARCHAR2_TABLE_100();
2616 x_model_bundle_flag_tbl := JTF_VARCHAR2_TABLE_100();
2617 IF (p_itmid_tbl IS NOT NULL) THEN
2618 x_configurable_tbl.EXTEND(p_itmid_tbl.COUNT);
2619 x_model_bundle_flag_tbl.EXTEND(p_itmid_tbl.COUNT);
2620 ELSE
2621 x_configurable_tbl.EXTEND(p_partnum_tbl.COUNT);
2622 x_model_bundle_flag_tbl.EXTEND(p_partnum_tbl.COUNT); -- bug fix#2234615
2623 END IF;
2624
2625 l_resp_id := FND_PROFILE.value('RESP_ID');
2626 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
2627
2628 FOR l_table_index IN 1..x_configurable_tbl.COUNT LOOP
2629 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2630 IBE_UTIL.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2631 ibe_util.debug('item id=' || l_itmid_tbl(l_table_index));
2632 ibe_util.debug('organization id=' || p_organization_id);
2633 ibe_util.debug('responsibility id=' || l_resp_id);
2634 ibe_util.debug('application id=' || l_resp_appl_id);
2635 END IF;
2636 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (l_itmid_tbl(l_table_index), p_organization_id,
2637 SYSDATE, 'DHTML', FND_API.G_MISS_NUM,
2638 l_resp_id, l_resp_appl_id);
2639 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2640 IBE_UTIL.debug('Return from CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2641 ibe_util.debug('ui_def_id=' || l_ui_def_id);
2642 END IF;
2643
2644 IF l_ui_def_id IS NULL THEN
2645 x_configurable_tbl(l_table_index) := FND_API.G_FALSE;
2646 ELSE
2647 x_configurable_tbl(l_table_index) := FND_API.G_TRUE;
2648 END IF;
2649
2650 IF x_configurable_tbl(l_table_index) = FND_API.G_FALSE THEN
2651 x_model_bundle_flag_tbl(l_table_index) := IBE_CCTBOM_PVT.Is_Model_Bundle(p_api_version =>1.0, p_model_id =>l_itmid_tbl(l_table_index), p_organization_id => p_organization_id);
2652 ELSE
2653 x_model_bundle_flag_tbl(l_table_index) := FND_API.G_FALSE;
2654 END IF;
2655 END LOOP;
2656
2657 l_end_time := DBMS_UTILITY.GET_TIME;
2658 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2659 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEMS(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2660 END IF;
2661 --end API body
2662
2663 -- standard call to get message count and if count is 1, get message info.
2664 EXCEPTION
2665 WHEN OTHERS THEN
2666 x_return_status := FND_API.G_RET_STS_ERROR;
2667 x_return_status_text :=SQLERRM;
2668 END FETCH_ITEMS;
2669
2670 Procedure FETCH_PRICES(
2671 p_itmid_tbl IN JTF_NUMBER_TABLE,
2672 p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100,
2673 p_model_id_tbl IN JTF_NUMBER_TABLE,
2674 p_organization_id IN NUMBER,
2675 p_price_list_id IN NUMBER,
2676 p_currency_code IN VARCHAR2,
2677 p_price_request_type IN VARCHAR2,
2678 p_price_event IN VARCHAR2,
2679 p_minisite_id IN NUMBER := NULL,
2680 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2681 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2682 x_return_status OUT NOCOPY VARCHAR2,
2683 x_return_status_text OUT NOCOPY VARCHAR2
2684
2685 )
2686 IS
2687 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_PRICES';
2688 l_api_version CONSTANT NUMBER := 1.0;
2689
2690 l_itmid NUMBER;
2691 l_primary_uom VARCHAR2(40);
2692 l_itmid_tbl QP_PREQ_GRP.NUMBER_TYPE;
2693 l_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2694 l_model_id_tbl JTF_NUMBER_TABLE;
2695 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
2696 l_parentIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2697 l_childIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2698 l_table_index NUMBER;
2699 idx BINARY_INTEGER;
2700
2701 CURSOR l_primary_uom_csr(l_item_id NUMBER) IS
2702 SELECT MSIV.primary_uom_code
2703 FROM mtl_system_items_vl MSIV
2704 WHERE MSIV.inventory_item_id = l_item_id;
2705
2706 CURSOR l_uom_csr(l_item_id NUMBER) IS
2707 SELECT miuv.UOM_CODE
2708 FROM MTL_ITEM_UOMS_VIEW miuv
2709 WHERE miuv.INVENTORY_ITEM_ID = l_item_id
2710 AND miuv.ORGANIZATION_ID = p_organization_id
2711 ORDER BY miuv.UOM_CODE;
2712
2713 l_bom_item_csr IBE_CCTBOM_PVT.IBE_CCTBOM_REF_CSR_TYPE;
2714 l_bom_exp_rec IBE_CCTBOM_PVT.IBE_BOM_EXPLOSION_REC;
2715 l_msg_data VARCHAR2(100);
2716 l_msg_count NUMBER;
2717 l_return_status VARCHAR2(30);
2718
2719 l_retrieve_all_uom VARCHAR2(10);
2720 l_start_time NUMBER;
2721 l_end_time NUMBER;
2722
2723 BEGIN
2724 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2725 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':BEGIN');
2726 END IF;
2727 l_start_time := DBMS_UTILITY.GET_TIME;
2728 l_retrieve_all_uom := fnd_profile.value_specific('IBE_RETRIEVE_ALL_ITEM_UOMS', NULL, NULL, 671);
2729 l_model_id_tbl := JTF_NUMBER_TABLE();
2730 idx := 1;
2731 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2732 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':l_retrieve_all_uom='||l_retrieve_all_uom);
2733 END IF;
2734 FOR l_table_index IN 1..p_itmid_tbl.COUNT LOOP
2735 -- construct item id tbl, uomcode tbl to pass into pricing engine if retrieving price
2736 l_itmid := p_itmid_tbl(l_table_index);
2737 IF p_model_bundle_flag_tbl(l_table_index) = FND_API.G_TRUE OR l_retrieve_all_uom = 'N' THEN
2738 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2739 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': only pricing primary uom');
2740 END IF;
2741 OPEN l_primary_uom_csr(l_itmid);
2742 FETCH l_primary_uom_csr INTO l_primary_uom;
2743 IF l_primary_uom_csr%FOUND THEN
2744 l_uom_code_tbl(idx) := l_primary_uom;
2745 ELSE
2746 l_uom_code_tbl(idx) := FND_API.G_MISS_CHAR;
2747 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2748 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':item has no primary uom code, item='||l_itmid);
2749 END IF;
2750 END IF;
2751 CLOSE l_primary_uom_csr;
2752 l_itmid_tbl(idx) := l_itmid;
2753 l_line_quantity_tbl(idx) := 1;
2754 l_model_id_tbl.EXTEND;
2755 --don't covert model_id=-1 here
2756 --IF p_model_id_tbl(l_table_index) <> -1 THEN
2757 l_model_id_tbl(idx) := p_model_id_tbl(l_table_index);
2758 --ELSE
2759 --l_model_id_tbl(idx) := FND_API.G_MISS_NUM;
2760 --END IF;
2761 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2762 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': item ='||l_itmid||', primary uom='||l_primary_uom);
2763 END IF;
2764 idx := idx + 1;
2765
2766 IF p_model_bundle_flag_tbl(l_table_index) = FND_API.G_TRUE THEN
2767 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2768 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':loading component items...');
2769 END IF;
2770 IBE_CCTBOM_PVT.Load_Components(p_api_version =>1.0,
2771 x_return_status=>l_return_status,
2772 x_msg_data=>l_msg_data,
2773 x_msg_count =>l_msg_count,
2774 p_model_id =>l_itmid,
2775 p_organization_id =>p_organization_id,
2776 x_item_csr =>l_bom_item_csr);
2777 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2778 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2779 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':adding component items..., idx='||idx);
2780 END IF;
2781 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2782 WHILE l_bom_item_csr%FOUND LOOP
2783 l_itmid_tbl(idx) := l_bom_exp_rec.component_item_id;
2784 l_uom_code_tbl(idx) := l_bom_exp_rec.primary_uom_code;
2785 l_line_quantity_tbl(idx) := l_bom_exp_rec.component_quantity;
2786 l_model_id_tbl.EXTEND;
2787 l_model_id_tbl(idx) := l_itmid;
2788 idx := idx + 1;
2789 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2790 END LOOP;
2791 CLOSE l_bom_item_csr;
2792 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2793 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':component items added to request line, idx='||idx);
2794 END IF;
2795 ELSE
2796 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2797 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':failed to load component items');
2798 END IF;
2799 END IF;
2800 END IF;
2801 ELSE
2802 FOR l_rec IN l_uom_csr(l_itmid) LOOP
2803 l_uom_code_tbl(idx) := l_rec.uom_code;
2804 l_itmid_tbl(idx) := l_itmid;
2805 l_line_quantity_tbl(idx) := 1;
2806 l_model_id_tbl.EXTEND;
2807 --don't convert model_id=-1 here
2808 --IF p_model_id_tbl(l_table_index) <> -1 THEN
2809 l_model_id_tbl(idx) := p_model_id_tbl(l_table_index);
2810 --ELSE
2811 --l_model_id_tbl(idx) := FND_API.G_MISS_NUM;
2812 --END IF;
2813 --IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': line '||idx||', item='||l_itmid_tbl(idx)||',uom='||l_uom_code_tbl(idx)||',model id='||l_model_id_tbl(idx));
2814 idx := idx + 1;
2815 END LOOP;
2816 END IF;
2817 END LOOP;
2818 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2819 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':uom codes loaded');
2820 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':request pricing...');
2821 END IF;
2822
2823 -- pricing
2824
2825 IBE_PRICE_PVT.PRICE_REQUEST(
2826 p_price_list_id => p_price_list_id,
2827 p_currency_code => p_currency_code,
2828 p_item_tbl => l_itmid_tbl,
2829 p_uom_code_tbl => l_uom_code_tbl,
2830 p_model_id_tbl => l_model_id_tbl,
2831 p_line_quantity_tbl => l_line_quantity_tbl,
2832 p_parentIndex_tbl => l_parentIndex_tbl,
2833 p_childIndex_tbl => l_childIndex_tbl,
2834 p_request_type_code => p_price_request_type,
2835 p_pricing_event => p_price_event,
2836 p_minisite_id => p_minisite_id,
2837 x_price_csr => x_price_csr,
2838 x_line_index_tbl => x_line_index_tbl,
2839 x_return_status => x_return_status,
2840 x_return_status_text=> x_return_status_text);
2841
2842
2843 l_end_time := DBMS_UTILITY.GET_TIME;
2844 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2845 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':pricing done');
2846 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':END, elapsed time (s) ='||(l_end_time-l_start_time)/100);
2847 END IF;
2848 --end API body
2849
2850 EXCEPTION
2851 WHEN OTHERS THEN
2852 x_return_status := FND_API.G_RET_STS_ERROR;
2853 x_return_status_text :=SQLERRM;
2854 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2855 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':'||SQLERRM);
2856 END IF;
2857 END FETCH_PRICES;
2858
2859 Procedure LOAD_ITEMS
2860 (p_api_version IN NUMBER,
2861 p_init_msg_list IN VARCHAR2 := NULL,
2862 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2863 x_return_status OUT NOCOPY VARCHAR2,
2864 x_msg_count OUT NOCOPY NUMBER,
2865 x_msg_data OUT NOCOPY VARCHAR2,
2866
2867 p_load_level IN NUMBER,
2868 p_preview_flag IN VARCHAR2,
2869 p_itmid_tbl IN JTF_NUMBER_TABLE,
2870 p_partnum_tbl IN JTF_VARCHAR2_TABLE_100,
2871 p_model_id_tbl IN JTF_NUMBER_TABLE,
2872 p_organization_id IN NUMBER,
2873 p_category_set_id IN NUMBER,
2874 p_retrieve_price IN VARCHAR2,
2875 p_price_list_id IN NUMBER,
2876 p_currency_code IN VARCHAR2,
2877 p_price_request_type IN VARCHAR2,
2878 p_price_event IN VARCHAR2,
2879 p_minisite_id IN NUMBER := NULL,
2880 x_category_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
2881 x_configurable_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2882 x_model_bundle_flag_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2883 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2884 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2885 x_price_status_code OUT NOCOPY VARCHAR2,
2886 x_price_status_text OUT NOCOPY VARCHAR2
2887
2888 ) IS
2889 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_ITEMS';
2890 l_api_version CONSTANT NUMBER := 1.0;
2891
2892 l_return_status_text VARCHAR2(300);
2893 l_start_time NUMBER;
2894 l_end_time NUMBER;
2895 l_init_msg_list VARCHAR2(5);
2896 BEGIN
2897 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2898 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':BEGIN');
2899 END IF;
2900 IF NOT FND_API.Compatible_API_Call (l_api_version,
2901 p_api_version,
2902 l_api_name,
2903 G_PKG_NAME )
2904 THEN
2905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2906 END IF;
2907 IF p_init_msg_list IS NULL THEN
2908 l_init_msg_list := FND_API.G_FALSE;
2909 END IF;
2910 -- initialize message list if L_init_msg_list is set to TRUE
2911 IF FND_API.to_Boolean(l_init_msg_list) THEN
2912 FND_MSG_PUB.initialize;
2913 END IF;
2914
2915 -- initialize API return status to success
2916 x_return_status := FND_API.G_RET_STS_SUCCESS;
2917
2918 -- load inv info
2919 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2920 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': Loading inventory info...');
2921 END IF;
2922 l_start_time := DBMS_UTILITY.GET_TIME;
2923 FETCH_ITEMS(p_load_level =>p_load_level,
2924 p_itmid_tbl =>p_itmid_tbl,
2925 p_partnum_tbl =>p_partnum_tbl,
2926 p_organization_id =>p_organization_id,
2927 p_category_set_id =>p_category_set_id,
2928 x_category_id_tbl =>x_category_id_tbl,
2929 x_configurable_tbl =>x_configurable_tbl,
2930 x_model_bundle_flag_tbl =>x_model_bundle_flag_tbl,
2931 x_return_status =>x_return_status,
2932 x_return_status_text =>l_return_status_text);
2933
2934
2935 -- pricing
2936 IF FND_API.to_Boolean(p_retrieve_price) THEN
2937 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2938 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': Loading prices...');
2939 END IF;
2940 FETCH_PRICES(
2941 p_itmid_tbl =>p_itmid_tbl,
2942 p_model_bundle_flag_tbl => x_model_bundle_flag_tbl,
2943 p_model_id_tbl =>p_model_id_tbl,
2944 p_organization_id => p_organization_id,
2945 p_price_list_id => p_price_list_id,
2946 p_currency_code => p_currency_code,
2947 p_price_request_type => p_price_request_type,
2948 p_price_event => p_price_event,
2949 p_minisite_id => p_minisite_id,
2950 x_price_csr => x_price_csr,
2951 x_line_index_tbl=> x_line_index_tbl,
2952 x_return_status => x_price_status_code,
2953 x_return_status_text => x_price_status_text);
2954 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2955 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': prices loaded');
2956 END IF;
2957 END IF;
2958 --end API body
2959
2960 -- standard call to get message count and if count is 1, get message info.
2961 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
2962 l_end_time := DBMS_UTILITY.GET_TIME;
2963 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2964 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':END, elapsed time (s) ='||(l_end_time-l_start_time)/100);
2965 END IF;
2966
2967 EXCEPTION
2968 WHEN FND_API.G_EXC_ERROR THEN
2969 x_return_status := FND_API.G_RET_STS_ERROR;
2970 FND_MSG_PUB.Count_And_Get
2971 ( p_encoded => FND_API.G_FALSE,
2972 p_count => x_msg_count,
2973 p_data => x_msg_data
2974 );
2975 --gzhang 08/08/2002, bug#2488246
2976 --ibe_util.disable_debug;
2977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2979 FND_MSG_PUB.Count_And_Get
2980 ( p_encoded => FND_API.G_FALSE,
2981 p_count => x_msg_count,
2982 p_data => x_msg_data
2983 );
2984 --gzhang 08/08/2002, bug#2488246
2985 --ibe_util.disable_debug;
2986 WHEN OTHERS THEN
2987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2988 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2989 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2990 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2991 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2992 FND_MSG_PUB.Add;
2993 IF FND_MSG_PUB.Check_Msg_Level
2994 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2995 THEN FND_MSG_PUB.Add_Exc_Msg
2996 ( G_PKG_NAME,
2997 l_api_name
2998 );
2999 END IF;
3000 FND_MSG_PUB.Count_And_Get
3001 ( p_encoded => FND_API.G_FALSE,
3002 p_count => x_msg_count,
3003 p_data => x_msg_data
3004 );
3005 --gzhang 08/08/2002, bug#2488246
3006 --ibe_util.disable_debug;
3007 END LOAD_ITEMS;
3008
3009 PROCEDURE GET_ITEM_TYPE
3010 (
3011 p_api_version IN NUMBER,
3012 p_init_msg_list IN VARCHAR2 := NULL,
3013 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3014 p_item_ids IN JTF_NUMBER_TABLE,
3015 p_organization_id IN NUMBER,
3016 x_item_type OUT NOCOPY JTF_VARCHAR2_TABLE_100,
3017 x_return_status OUT NOCOPY VARCHAR2,
3018 x_msg_count OUT NOCOPY NUMBER,
3019 x_msg_data OUT NOCOPY VARCHAR2
3020 )
3021 IS
3022 l_api_name CONSTANT VARCHAR2(30):= 'FETCH_ITEM';
3023 l_api_version CONSTANT NUMBER := 1.0;
3024 l_status VARCHAR2(5);
3025 l_service_item_flag VARCHAR2(5);
3026 l_serviceable_product_flag VARCHAR2(5);
3027 l_configurable VARCHAR2(5);
3028 l_model_bundle_flag VARCHAR2(5);
3029 x_query_string VARCHAR2(100);
3030 l_resp_id NUMBER;
3031 l_resp_appl_id NUMBER;
3032 l_ui_def_id NUMBER;
3033 l_start_time NUMBER;
3034 l_end_time NUMBER;
3035 l_table_index NUMBER := 1;
3036 l_temp_key CONSTANT VARCHAR2(20) := 'ITEMIDS_TYPECODE';
3037 l_init_msg_list VARCHAR2(5);
3038 cursor l_itm_attr_csr(l_temp_key VARCHAR2, l_org_id NUMBER) IS
3039 select MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG
3040 from MTL_SYSTEM_ITEMS_VL MSIV
3041 where MSIV.INVENTORY_ITEM_ID IN (select NUM_VAL from IBE_TEMP_TABLE where key =
3042 l_temp_key ) and MSIV.ORGANIZATION_ID = l_org_id;
3043
3044 BEGIN
3045 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
3046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3047 END IF;
3048 IF p_init_msg_list IS NULL THEN
3049 l_init_msg_list := FND_API.G_FALSE;
3050 END IF;
3051 -- initialize message list if p_init_msg_list is set to TRUE
3052 IF FND_API.to_Boolean(p_init_msg_list) THEN
3053 FND_MSG_PUB.initialize;
3054 END IF;
3055
3056 -- initialize API return status to success
3057 x_return_status := FND_API.G_RET_STS_SUCCESS;
3058 -- Populate the itemIds into a temporary table.
3059 FOR i in p_item_ids.FIRST .. p_item_ids.LAST
3060 LOOP
3061
3062 IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
3063 END LOOP;
3064
3065 l_resp_id := FND_PROFILE.value('RESP_ID');
3066 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
3067
3068 x_item_type := JTF_VARCHAR2_TABLE_100();
3069 x_item_type.extend(p_item_ids.COUNT);
3070
3071 FOR l_table_index IN 1..p_item_ids.COUNT LOOP
3072 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3073 ibe_util.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3074 ibe_util.debug('item id=' || p_item_ids(l_table_index));
3075 ibe_util.debug('organization id=' || p_organization_id);
3076 ibe_util.debug('responsibility id=' || l_resp_id);
3077 ibe_util.debug('application id=' || l_resp_appl_id);
3078 END IF;
3079 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (p_item_ids(l_table_index), p_organization_id,
3080 SYSDATE, 'DHTML', FND_API.G_MISS_NUM,
3081 l_resp_id, l_resp_appl_id);
3082 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3083 ibe_util.debug('Return from CZ_CF_API.UI_FOR_ITEM ' ||
3084 TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3085 END IF;
3086 IF l_ui_def_id IS NULL THEN
3087 l_configurable := FND_API.G_FALSE;
3088 ELSE
3089 l_configurable := FND_API.G_TRUE;
3090 END IF;
3091
3092 -- check if the item is a iStore bundle.
3093 IF l_configurable = FND_API.G_FALSE THEN
3094 l_model_bundle_flag := IBE_CCTBOM_PVT.Is_Model_Bundle(p_api_version =>1.0,
3095 p_model_id =>p_item_ids(l_table_index), p_organization_id => p_organization_id);
3096 END IF;
3097
3098
3099 IF (l_model_bundle_flag = FND_API.G_TRUE OR l_configurable = FND_API.G_TRUE) THEN
3100 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_MODEL;
3101 END IF;
3102 END LOOP;
3103
3104 -- check if the item is a service/serviceable product.
3105 l_table_index :=1;
3106 OPEN l_itm_attr_csr(l_temp_key,p_organization_id);
3107 LOOP
3108 FETCH l_itm_attr_csr INTO l_service_item_flag, l_serviceable_product_flag;
3109 EXIT WHEN l_itm_attr_csr%NOTFOUND;
3110 IF ( ( x_item_type(l_table_index) IS NULL ) AND
3111 ( l_service_item_flag = 'Y' )) THEN
3112 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_SERVICE;
3113 ELSIF ( ( x_item_type(l_table_index) IS NULL ) AND
3114 ( l_serviceable_product_flag = 'Y') ) THEN
3115 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_SERVICEABLE;
3116 ELSIF ( x_item_type(l_table_index) IS NULL ) THEN
3117 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_STANDARD;
3118 END IF;
3119 l_table_index := l_table_index + 1;
3120 END LOOP;
3121 l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);
3122 CLOSE l_itm_attr_csr;
3123 EXCEPTION
3124 WHEN FND_API.G_EXC_ERROR THEN
3125 x_return_status := FND_API.G_RET_STS_ERROR;
3126 FND_MSG_PUB.Count_And_Get
3127 ( p_encoded => FND_API.G_FALSE,
3128 p_count => x_msg_count,
3129 p_data => x_msg_data
3130 );
3131 --gzhang 08/08/2002, bug#2488246
3132 --ibe_util.disable_debug;
3133 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3135 FND_MSG_PUB.Count_And_Get
3136 ( p_encoded => FND_API.G_FALSE,
3137 p_count => x_msg_count,
3138 p_data => x_msg_data
3139 );
3140 --gzhang 08/08/2002, bug#2488246
3141 --ibe_util.disable_debug;
3142 WHEN OTHERS THEN
3143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3144 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
3145 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
3146 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
3147 FND_MESSAGE.Set_Token('REASON', SQLERRM);
3148 FND_MSG_PUB.Add;
3149 IF FND_MSG_PUB.Check_Msg_Level
3150 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3151 THEN FND_MSG_PUB.Add_Exc_Msg
3152 ( G_PKG_NAME,
3153 l_api_name
3154 );
3155 END IF;
3156 FND_MSG_PUB.Count_And_Get
3157 ( p_encoded => FND_API.G_FALSE,
3158 p_count => x_msg_count,
3159 p_data => x_msg_data
3160 );
3161
3162 END GET_ITEM_TYPE;
3163
3164 PROCEDURE IS_ITEM_IN_MINISITE
3165 (
3166 p_api_version IN NUMBER,
3167 p_init_msg_list IN VARCHAR2 := NULL,
3168 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3169 p_item_ids IN JTF_NUMBER_TABLE,
3170 p_minisite_id IN NUMBER,
3171 x_minisite_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
3172 x_return_status OUT NOCOPY VARCHAR2,
3173 x_msg_count OUT NOCOPY NUMBER,
3174 x_msg_data OUT NOCOPY VARCHAR2
3175 )
3176 IS
3177 l_api_name CONSTANT VARCHAR2(30):= 'IS_ITEM_IN_MINISITE';
3178 l_api_version CONSTANT NUMBER := 1.0;
3179 x_query_string VARCHAR2(100);
3180 l_temp_key CONSTANT VARCHAR2(20) := 'ITEMIDS_IN_MSITE';
3181 l_item_id NUMBER;
3182 l_item_index NUMBER;
3183 l_status VARCHAR2(5);
3184 l_init_msg_list VARCHAR2(5);
3185 cursor l_itms_msite_csr(l_temp_key VARCHAR2, l_minisite_id NUMBER) IS
3186 select b.inventory_item_id
3187 from ibe_dsp_msite_sct_items a, ibe_dsp_section_items b
3188 where a.section_item_id = b.section_item_id
3189 and a.mini_site_id = l_minisite_id and b.inventory_item_id IN
3190 (select NUM_VAL from IBE_TEMP_TABLE where key = l_temp_key );
3191
3192 BEGIN
3193
3194 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
3195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3196 END IF;
3197 IF p_init_msg_list IS NULL THEN
3198 l_init_msg_list := FND_API.G_FALSE;
3199 END IF;
3200 -- initialize message list if l_init_msg_list is set to TRUE
3201 IF FND_API.to_Boolean(l_init_msg_list) THEN
3202 FND_MSG_PUB.initialize;
3203 END IF;
3204
3205 -- initialize API return status to success
3206 x_return_status := FND_API.G_RET_STS_SUCCESS;
3207 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3208 ibe_util.debug('Calling IS_ITEM_IN_MINISITE ' );
3209 END IF;
3210
3211 -- Populate the itemIds into a temporary table.
3212 FOR i in p_item_ids.FIRST .. p_item_ids.LAST
3213 LOOP
3214 IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
3215 END LOOP;
3216
3217 x_minisite_item_ids := JTF_NUMBER_TABLE();
3218 l_item_index :=1;
3219
3220 OPEN l_itms_msite_csr(l_temp_key,p_minisite_id);
3221 LOOP
3222 FETCH l_itms_msite_csr INTO l_item_id;
3223 EXIT WHEN l_itms_msite_csr%NOTFOUND;
3224 x_minisite_item_ids.extend();
3225 x_minisite_item_ids(l_item_index) := l_item_id;
3226 l_item_index := l_item_index + 1;
3227 END LOOP;
3228 l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);
3229 CLOSE l_itms_msite_csr;
3230 EXCEPTION
3231 WHEN FND_API.G_EXC_ERROR THEN
3232 x_return_status := FND_API.G_RET_STS_ERROR;
3233 FND_MSG_PUB.Count_And_Get
3234 ( p_encoded => FND_API.G_FALSE,
3235 p_count => x_msg_count,
3236 p_data => x_msg_data
3237 );
3238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3240 FND_MSG_PUB.Count_And_Get
3241 ( p_encoded => FND_API.G_FALSE,
3242 p_count => x_msg_count,
3243 p_data => x_msg_data
3244 );
3245 WHEN OTHERS THEN
3246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3247 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
3248 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
3249 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
3250 FND_MESSAGE.Set_Token('REASON', SQLERRM);
3251 FND_MSG_PUB.Add;
3252 IF FND_MSG_PUB.Check_Msg_Level
3253 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3254 THEN FND_MSG_PUB.Add_Exc_Msg
3255 ( G_PKG_NAME,
3256 l_api_name
3257 );
3258 END IF;
3259 FND_MSG_PUB.Count_And_Get
3260 ( p_encoded => FND_API.G_FALSE,
3261 p_count => x_msg_count,
3262 p_data => x_msg_data
3263 );
3264
3265 END IS_ITEM_IN_MINISITE;
3266
3267 PROCEDURE IS_ITEM_CONFIGURABLE
3268 (
3269 p_item_id IN NUMBER,
3270 p_organization_id IN NUMBER,
3271 x_configurable OUT NOCOPY VARCHAR2
3272 )
3273 IS
3274 l_ui_def_id NUMBER;
3275 l_resp_id NUMBER;
3276 l_resp_appl_id NUMBER;
3277 BEGIN
3278 l_resp_id := FND_PROFILE.value('RESP_ID');
3279 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
3280 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3281 IBE_UTIL.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3282 ibe_util.debug('item id=' || p_item_id);
3283 ibe_util.debug('organization id=' || p_organization_id);
3284 ibe_util.debug('responsibility id=' || l_resp_id);
3285 ibe_util.debug('application id=' || l_resp_appl_id);
3286 END IF;
3287
3288 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (p_item_id, p_organization_id, SYSDATE,
3289 'DHTML', FND_API.G_MISS_NUM, l_resp_id, l_resp_appl_id);
3290 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3291 IBE_UTIL.debug('Return from CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3292 ibe_util.debug('ui_def_id=' || l_ui_def_id);
3293 END IF;
3294 IF l_ui_def_id IS NULL THEN
3295 x_configurable := FND_API.G_FALSE;
3296 ELSE
3297 x_configurable := FND_API.G_TRUE;
3298 END IF;
3299 END IS_ITEM_CONFIGURABLE;
3300
3301 END IBE_CATALOG_PVT;