[Home] [Help]
PACKAGE BODY: APPS.IBE_CATALOG_PVT
Source
1 PACKAGE BODY IBE_CATALOG_PVT AS
2 /* $Header: IBEVCCTB.pls 120.8 2010/09/14 08:47:50 amaheshw 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.organization_id = p_organization_id_tbl(i) -- bug 10092967, scnagara
1775 AND (s.end_date_active > sysdate or s.end_date_active is null )
1776 AND s.start_date_active < sysdate;
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 x_valid_qty_tbl(i) := 'IBE_PRMT_SC_DE_ITEM_NSETUP';
1780 END;
1781
1782 IF( l_item_exists <= 0 ) THEN
1783 --item does not exist in some section
1784 x_valid_qty_tbl(i) := 'IBE_PRMT_SC_DE_ITEM_NSETUP';
1785 END IF;
1786 END IF;
1787 END LOOP;
1788 EXCEPTION
1789 WHEN FND_API.G_EXC_ERROR THEN
1790 x_return_status := FND_API.G_RET_STS_ERROR;
1791 FND_MSG_PUB.Count_And_Get
1792 ( p_encoded => FND_API.G_FALSE,
1793 p_count => x_msg_count,
1794 p_data => x_msg_data
1795 );
1796 --gzhang 08/08/2002, bug#2488246
1797 --ibe_util.disable_debug;
1798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1800 FND_MSG_PUB.Count_And_Get
1801 ( p_encoded => FND_API.G_FALSE,
1802 p_count => x_msg_count,
1803 p_data => x_msg_data
1804 );
1805 --gzhang 08/08/2002, bug#2488246
1806 --ibe_util.disable_debug;
1807 WHEN OTHERS THEN
1808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1809 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1810 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1811 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1812 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1813 FND_MSG_PUB.Add;
1814 IF FND_MSG_PUB.Check_Msg_Level
1815 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1816 THEN FND_MSG_PUB.Add_Exc_Msg
1817 (G_PKG_NAME,
1818 l_api_name
1819 );
1820 END IF;
1821 FND_MSG_PUB.Count_And_Get
1822 ( p_encoded => FND_API.G_FALSE,
1823 p_count => x_msg_count,
1824 p_data => x_msg_data
1825 );
1826 --gzhang 08/08/2002, bug#2488246
1827 --ibe_util.disable_debug;
1828 end validate_de_qty_msite_check;
1829
1830 -- gzhang, 04/23/02, new APIs for Global Store Selection phase 2
1831 procedure load_msite_languages
1832 (x_lang_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1833 x_tran_lang_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1834 x_desc_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300 --gzhang 07/19/2002, bug#2469521
1835 )
1836 IS
1837 CURSOR l_lang_csr IS
1838 SELECT language_code, language, description
1839 FROM fnd_languages_tl t
1840 WHERE EXISTS (SELECT NULL FROM ibe_msite_languages m WHERE m.language_code = t.language_code)
1841 ORDER BY language_code;
1842
1843 l_index NUMBER := 1;
1844
1845 BEGIN
1846 --gzhang 08/08/2002, bug#2488246
1847 --ibe_util.enable_debug;
1848 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1849 IBE_UTIL.debug('In IBE_CATALOG_PVT.load_msite_languages...');
1850 END IF;
1851
1852 x_lang_code_tbl := JTF_VARCHAR2_TABLE_100();
1853 x_tran_lang_code_tbl := JTF_VARCHAR2_TABLE_100();
1854 x_desc_tbl := JTF_VARCHAR2_TABLE_300(); --gzhang 07/19/2002, bug#2469521
1855
1856 FOR l_lang_rec IN l_lang_csr LOOP
1857
1858 x_lang_code_tbl.EXTEND;
1859 x_tran_lang_code_tbl.EXTEND;
1860 x_desc_tbl.EXTEND;
1861
1862 x_lang_code_tbl(l_index) := l_lang_rec.language_code;
1863 x_tran_lang_code_tbl(l_index) := l_lang_rec.language;
1864 x_desc_tbl(l_index) := l_lang_rec.description;
1865
1866 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1867 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));
1868 END IF;
1869
1870 l_index := l_index + 1;
1871 END LOOP;
1872 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1873 IBE_UTIL.debug('IBE_CATALOG_PVT.load_msite_languages:done');
1874 END IF;
1875 --gzhang 08/08/2002, bug#2488246
1876 --ibe_util.disable_debug;
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1880 IBE_UTIL.debug('Exception in IBE_CATALOG_PVT.load_msite_languages');
1881 END IF;
1882 --gzhang 08/08/2002, bug#2488246
1883 --ibe_util.disable_debug;
1884
1885 END load_msite_languages;
1886
1887 procedure load_language
1888 (p_lang_code IN VARCHAR2,
1889 x_tran_lang_code_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
1890 x_desc_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_300, --gzhang 07/19/2002, bug#2469521
1891 x_nls_lang OUT NOCOPY VARCHAR2 --jqu 1/19/2005
1892 )
1893 IS
1894 CURSOR l_lang_csr (l_lang_code VARCHAR2) IS
1895 SELECT t.language, t.description
1896 FROM fnd_languages_tl t
1897 WHERE language_code = l_lang_code;
1898 CURSOR l_nls_lang_csr (l_lang_code VARCHAR2) IS
1899 SELECT nls_language
1900 FROM fnd_languages
1901 WHERE language_code = l_lang_code;
1902
1903 l_index NUMBER := 1;
1904
1905 BEGIN
1906 --gzhang 08/08/2002, bug#2488246
1907 --ibe_util.enable_debug;
1908 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1909 IBE_UTIL.debug('In IBE_CATALOG_PVT.LOAD_LANGUAGE...');
1910 IBE_UTIL.debug('p_lang_code ='||p_lang_code);
1911 END IF;
1912
1913 x_tran_lang_code_tbl := JTF_VARCHAR2_TABLE_100();
1914 x_desc_tbl := JTF_VARCHAR2_TABLE_300(); --gzhang 07/19/2002, bug#2469521
1915
1916 FOR l_lang_rec IN l_lang_csr(p_lang_code) LOOP
1917
1918 x_tran_lang_code_tbl.EXTEND;
1919 x_desc_tbl.EXTEND;
1920
1921 x_tran_lang_code_tbl(l_index) := l_lang_rec.language;
1922 x_desc_tbl(l_index) := l_lang_rec.description;
1923
1924 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1925 IBE_UTIL.debug('translated language code='||x_tran_lang_code_tbl(l_index)||',desc='||x_desc_tbl(l_index));
1926 END IF;
1927
1928 l_index := l_index + 1;
1929 END LOOP;
1930
1931 OPEN l_nls_lang_csr(p_lang_code);
1932 FETCH l_nls_lang_csr INTO x_nls_lang;
1933 CLOSE l_nls_lang_csr;
1934
1935 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1936 IBE_UTIL.debug('IBE_CATALOG_PVT.LOAD_LANGUAGE:done');
1937 END IF;
1938 --gzhang 08/08/2002, bug#2488246
1939 --ibe_util.disable_debug;
1940 EXCEPTION
1941 WHEN OTHERS THEN
1942 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
1943 IBE_UTIL.debug('Exception in IBE_CATALOG_PVT.load_language');
1944 END IF ;
1945 --gzhang 08/08/2002, bug#2488246
1946 --ibe_util.disable_debug;
1947
1948 END load_language;
1949 -- gzhang, 04/23/02, end of new APIs for Global Store Selection phase 2
1950
1951
1952
1953 --integration with QP_TEMP_TABLE
1954 procedure FETCH_ITEM
1955 (p_api_version IN NUMBER,
1956 p_init_msg_list IN VARCHAR2 := NULL,
1957 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1958 x_return_status OUT NOCOPY VARCHAR2,
1959 x_msg_count OUT NOCOPY NUMBER,
1960 x_msg_data OUT NOCOPY VARCHAR2,
1961
1962 p_load_level IN NUMBER,
1963 p_preview_flag IN VARCHAR2,
1964 p_itmid IN NUMBER,
1965 p_partnum IN VARCHAR2,
1966 p_model_id IN NUMBER := FND_API.G_MISS_NUM,
1967 p_organization_id IN NUMBER,
1968 p_category_set_id IN NUMBER,
1969 x_item_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
1970 x_category_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
1971 x_configurable OUT NOCOPY VARCHAR2,
1972 x_model_bundle_flag OUT NOCOPY VARCHAR2,
1973 x_uom_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE
1974
1975 ) IS
1976
1977 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_ITEM';
1978 l_api_version CONSTANT NUMBER := 1.0;
1979
1980 cursor l_itmid_csr(p_item_partnum VARCHAR2) IS
1981 select MSIV.inventory_item_id
1982 from mtl_system_items_vl MSIV
1983 where MSIV.concatenated_segments = p_item_partnum;
1984
1985 l_itmid NUMBER;
1986 l_itm_stmt VARCHAR2(32767);
1987 l_ui_def_id NUMBER;
1988 l_resp_id NUMBER;
1989 l_resp_appl_id NUMBER;
1990 l_retrieve_all_uom VARCHAR2(10);
1991 l_start_time NUMBER;
1992 l_end_time NUMBER;
1993 l_init_msg_list VARCHAR2(5);
1994
1995 BEGIN
1996 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
1997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1998 END IF;
1999
2000 IF p_init_msg_list IS NULL THEN
2001 l_init_msg_list := FND_API.G_FALSE;
2002 END IF;
2003
2004 -- initialize message list if L_init_msg_list is set to TRUE
2005 IF FND_API.to_Boolean(l_init_msg_list) THEN
2006 FND_MSG_PUB.initialize;
2007 END IF;
2008
2009 -- initialize API return status to success
2010 x_return_status := FND_API.G_RET_STS_SUCCESS;
2011
2012
2013 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2014 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEM(+)');
2015 END IF;
2016 l_start_time := DBMS_UTILITY.GET_TIME;
2017
2018 -- get the select and from clauses of the query
2019 Get_Basic_Item_Load_Query(p_load_level, l_itm_stmt);
2020
2021 IF p_itmid IS NULL THEN
2022 IF p_partnum IS NULL THEN
2023 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2024 IBE_UTIL.debug('Error: p_itm_id and p_accessname are both NULL');
2025 END IF;
2026 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_ID_OR_NAME');
2027 FND_MESSAGE.Set_Token('ID_NAME', p_partnum);
2028 FND_MSG_PUB.Add;
2029 RAISE FND_API.G_EXC_ERROR;
2030 ELSE
2031 --loading by part number
2032
2033 -- need to get item id
2034 OPEN l_itmid_csr(p_partnum);
2035 FETCH l_itmid_csr INTO l_itmid;
2036 CLOSE l_itmid_csr;
2037
2038 END IF;
2039 ELSE
2040 -- loading by item_id
2041 l_itmid := p_itmid;
2042 END IF;
2043
2044 -- add check in where clause for organization id, active dates, web_status
2045 l_itm_stmt := l_itm_stmt || 'WHERE MSIV.INVENTORY_ITEM_ID = :p_itmid ' ||
2046 ' AND MSIV.ORGANIZATION_ID = :p_organization_id ';
2047
2048 IF NOT FND_API.to_Boolean(p_preview_flag) THEN
2049 l_itm_stmt := l_itm_stmt || ' AND MSIV.WEB_STATUS = ''PUBLISHED'' ';
2050 END IF;
2051
2052 l_itm_stmt := l_itm_stmt ||
2053 ' AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE ' ||
2054 ' AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE ';
2055
2056 -- open the item cursor for return
2057 OPEN x_item_csr FOR l_itm_stmt USING l_itmid, p_organization_id;
2058
2059 -- open category id cursor for return if category set id is not null
2060 IF (p_category_set_id IS NOT NULL) THEN
2061 OPEN x_category_id_csr FOR
2062 SELECT MSIV.INVENTORY_ITEM_ID, mic.CATEGORY_ID
2063 FROM MTL_SYSTEM_ITEMS_VL MSIV, MTL_ITEM_CATEGORIES mic
2064 WHERE MSIV.INVENTORY_ITEM_ID = l_itmid
2065 AND MSIV.ORGANIZATION_ID = p_organization_id
2066 AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
2067 AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
2068 AND MSIV.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
2069 AND MSIV.ORGANIZATION_ID = mic.ORGANIZATION_ID
2070 AND mic.CATEGORY_SET_ID = p_category_set_id;
2071 END IF;
2072
2073 -- open uom cursor for return if SHALLOW or DEEP load
2074 l_retrieve_all_uom := fnd_profile.value_specific('IBE_RETRIEVE_ALL_ITEM_UOMS', NULL, NULL, 671);
2075 IF ((l_retrieve_all_uom IS NULL) OR (l_retrieve_all_uom = 'Y')) THEN
2076 IF ((p_load_level = G_ITEM_SHALLOW) OR (p_load_level = G_ITEM_DEEP)) THEN
2077 OPEN x_uom_csr FOR
2078 SELECT miuv.INVENTORY_ITEM_ID, miuv.UOM_CODE
2079 FROM MTL_ITEM_UOMS_VIEW miuv
2080 WHERE miuv.INVENTORY_ITEM_ID = l_itmid
2081 AND miuv.ORGANIZATION_ID = p_organization_id
2082 ORDER BY miuv.UOM_CODE;
2083 END IF;
2084 END IF;
2085
2086 -- call configurator API
2087 l_resp_id := FND_PROFILE.value('RESP_ID');
2088 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
2089 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2090 IBE_UTIL.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2091 ibe_util.debug('item id=' || l_itmid);
2092 ibe_util.debug('organization id=' || p_organization_id);
2093 ibe_util.debug('responsibility id=' || l_resp_id);
2094 ibe_util.debug('application id=' || l_resp_appl_id);
2095 END IF;
2096
2097 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (l_itmid, p_organization_id, SYSDATE,
2098 'DHTML', FND_API.G_MISS_NUM, l_resp_id, l_resp_appl_id);
2099 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2100 IBE_UTIL.debug('Return from CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2101 ibe_util.debug('ui_def_id=' || l_ui_def_id);
2102 END IF;
2103 IF l_ui_def_id IS NULL THEN
2104 x_configurable := FND_API.G_FALSE;
2105 ELSE
2106 x_configurable := FND_API.G_TRUE;
2107 END IF;
2108
2109 IF /*l_bom_item_type = 1 AND*/ x_configurable = FND_API.G_FALSE THEN
2110 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);
2111 ELSE
2112 x_model_bundle_flag := FND_API.G_FALSE;
2113 END IF;
2114
2115 l_end_time := DBMS_UTILITY.GET_TIME;
2116 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2117 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEM(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2118 END IF;
2119 --end API body
2120
2121 -- standard call to get message count and if count is 1, get message info.
2122 FND_MSG_PUB.Count_And_Get
2123 ( p_encoded => FND_API.G_FALSE,
2124 p_count => x_msg_count,
2125 p_data => x_msg_data
2126 );
2127 EXCEPTION
2128 WHEN FND_API.G_EXC_ERROR THEN
2129 x_return_status := FND_API.G_RET_STS_ERROR;
2130 FND_MSG_PUB.Count_And_Get
2131 ( p_encoded => FND_API.G_FALSE,
2132 p_count => x_msg_count,
2133 p_data => x_msg_data
2134 );
2135 --gzhang 08/08/2002, bug#2488246
2136 --ibe_util.disable_debug;
2137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2139 FND_MSG_PUB.Count_And_Get
2140 ( p_encoded => FND_API.G_FALSE,
2141 p_count => x_msg_count,
2142 p_data => x_msg_data
2143 );
2144 --gzhang 08/08/2002, bug#2488246
2145 --ibe_util.disable_debug;
2146 WHEN OTHERS THEN
2147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2148 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2149 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2150 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2151 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2152 FND_MSG_PUB.Add;
2153 IF FND_MSG_PUB.Check_Msg_Level
2154 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2155 THEN FND_MSG_PUB.Add_Exc_Msg
2156 ( G_PKG_NAME,
2157 l_api_name
2158 );
2159 END IF;
2160 FND_MSG_PUB.Count_And_Get
2161 ( p_encoded => FND_API.G_FALSE,
2162 p_count => x_msg_count,
2163 p_data => x_msg_data
2164 );
2165 --gzhang 08/08/2002, bug#2488246
2166 --ibe_util.disable_debug;
2167 END FETCH_ITEM;
2168
2169 Procedure FETCH_PRICE
2170 (p_itmid IN NUMBER,
2171 p_model_bundle_flag IN VARCHAR2,
2172 p_model_id IN NUMBER := FND_API.G_MISS_NUM,
2173 p_organization_id IN NUMBER,
2174 p_price_list_id IN NUMBER,
2175 p_currency_code IN VARCHAR2,
2176 p_price_request_type IN VARCHAR2,
2177 p_price_event IN VARCHAR2,
2178 p_minisite_id IN NUMBER := NULL,
2179 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2180 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2181 x_return_status OUT NOCOPY VARCHAR2,
2182 x_return_status_text OUT NOCOPY VARCHAR2
2183 )
2184 IS
2185
2186 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_PRICE';
2187 l_api_version CONSTANT NUMBER := 1.0;
2188
2189 l_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2190 l_itmid_tbl QP_PREQ_GRP.NUMBER_TYPE;
2191 l_model_id_tbl JTF_NUMBER_TABLE;
2192 l_model_id NUMBER;
2193 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
2194 idx BINARY_INTEGER;
2195 l_parentIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2196 l_childIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2197
2198 CURSOR l_item_uom_csr IS
2199 SELECT miuv.INVENTORY_ITEM_ID, miuv.UOM_CODE
2200 FROM MTL_ITEM_UOMS_VIEW miuv
2201 WHERE miuv.INVENTORY_ITEM_ID = p_itmid
2202 AND miuv.ORGANIZATION_ID = p_organization_id
2203 ORDER BY miuv.UOM_CODE;
2204
2205 CURSOR l_primary_uom_csr IS
2206 SELECT MSIV.primary_uom_code
2207 FROM mtl_system_items_vl MSIV
2208 WHERE MSIV.inventory_item_id = p_itmid;
2209
2210 l_retrieve_all_uom VARCHAR2(10);
2211 l_primary_uom VARCHAR2(40);
2212 l_bom_item_csr IBE_CCTBOM_PVT.IBE_CCTBOM_REF_CSR_TYPE;
2213 l_bom_exp_rec IBE_CCTBOM_PVT.IBE_BOM_EXPLOSION_REC;
2214 l_msg_data VARCHAR2(100);
2215 l_msg_count NUMBER;
2216 l_return_status VARCHAR2(30);
2217 l_start_time NUMBER;
2218 l_end_time NUMBER;
2219
2220
2221 BEGIN
2222 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2223 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_PRICE(+), item ='||p_itmid);
2224 END IF;
2225 l_start_time := DBMS_UTILITY.GET_TIME;
2226
2227 --don't convert model_id=-1 here
2228 --IF p_model_id = -1 THEN
2229 --l_model_id := FND_API.G_MISS_NUM;
2230 --ELSE
2231 l_model_id := p_model_id;
2232 --END IF;
2233 l_model_id_tbl := JTF_NUMBER_TABLE();
2234 idx := 1;
2235 l_retrieve_all_uom := fnd_profile.value_specific('IBE_RETRIEVE_ALL_ITEM_UOMS', NULL, NULL, 671);
2236 IF p_model_bundle_flag = FND_API.G_TRUE OR l_retrieve_all_uom = 'N' THEN
2237 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2238 IBE_UTIL.debug('pricing for primary uom only...');
2239 END IF;
2240 OPEN l_primary_uom_csr;
2241 FETCH l_primary_uom_csr INTO l_primary_uom;
2242 IF l_primary_uom_csr%FOUND THEN
2243 l_uom_code_tbl(idx) := l_primary_uom;
2244 ELSE
2245 l_uom_code_tbl(idx) := FND_API.G_MISS_CHAR;
2246 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2247 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':no primary uom code found');
2248 END IF;
2249 END IF;
2250 CLOSE l_primary_uom_csr;
2251
2252 l_itmid_tbl(idx) := p_itmid;
2253 l_line_quantity_tbl(idx) := 1;
2254 l_model_id_tbl.EXTEND;
2255 l_model_id_tbl(idx) := l_model_id;
2256 idx := idx + 1;
2257 IF p_model_bundle_flag = FND_API.G_TRUE THEN
2258 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2259 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':loading component items...');
2260 END IF;
2261 IBE_CCTBOM_PVT.Load_Components(p_api_version =>1.0,
2262 x_return_status=>l_return_status,
2263 x_msg_data=>l_msg_data,
2264 x_msg_count =>l_msg_count,
2265 p_model_id =>p_itmid,
2266 p_organization_id =>p_organization_id,
2267 x_item_csr =>l_bom_item_csr);
2268 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2269 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2270 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':adding component items..., idx='||idx);
2271 END IF;
2272 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2273 WHILE l_bom_item_csr%FOUND LOOP
2274 l_itmid_tbl(idx) := l_bom_exp_rec.component_item_id;
2275 l_uom_code_tbl(idx) := l_bom_exp_rec.primary_uom_code;
2276 l_line_quantity_tbl(idx) := l_bom_exp_rec.component_quantity;
2277 l_model_id_tbl.EXTEND;
2278 l_model_id_tbl(idx) := p_itmid;
2279 idx := idx + 1;
2280 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2281 END LOOP;
2282 CLOSE l_bom_item_csr;
2283 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2284 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':component items added to request line, idx='||idx);
2285 END IF;
2286 ELSE
2287 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2288 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':Failed to load component items');
2289 END IF;
2290 RAISE FND_API.G_EXC_ERROR;
2291 END IF;
2292 END IF;
2293 ELSE
2294 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2295 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': pricing for all uom codes...');
2296 END IF;
2297 FOR uom_rec IN l_item_uom_csr LOOP
2298 l_uom_code_tbl(idx) := uom_rec.UOM_CODE;
2299 l_itmid_tbl(idx) := uom_rec.INVENTORY_ITEM_ID;
2300 l_line_quantity_tbl(idx) := 1;
2301 l_model_id_tbl.extend;
2302 l_model_id_tbl(idx) := l_model_id;
2303 idx := idx + 1;
2304 END LOOP;
2305 END IF;
2306
2307 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2308 IBE_UTIL.debug('Calling IBE_PRICE_PVT.PRICE_REQUEST...');
2309 END IF;
2310 IBE_PRICE_PVT.PRICE_REQUEST(
2311 p_price_list_id => p_price_list_id,
2312 p_currency_code => p_currency_code,
2313 p_item_tbl => l_itmid_tbl,
2314 p_uom_code_tbl => l_uom_code_tbl,
2315 p_model_id_tbl => l_model_id_tbl,
2316 p_line_quantity_tbl => l_line_quantity_tbl,
2317 p_parentIndex_tbl => l_parentIndex_tbl,
2318 p_childIndex_tbl => l_childIndex_tbl,
2319 p_request_type_code => p_price_request_type,
2320 p_pricing_event => p_price_event,
2321 p_minisite_id => p_minisite_id,
2322 x_price_csr => x_price_csr,
2323 x_line_index_tbl => x_line_index_tbl,
2324 x_return_status => x_return_status,
2325 x_return_status_text=> x_return_status_text);
2326
2327 l_end_time := DBMS_UTILITY.GET_TIME;
2328 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2329 IBE_UTIL.debug('Return from IBE_PRICE_PVT.PRICE_REQUEST');
2330 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_PRICE(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2331 END IF;
2332 --end API body
2333
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 x_return_status := FND_API.G_RET_STS_ERROR;
2337 x_return_status_text :=SQLERRM;
2338 END FETCH_PRICE;
2339
2340 Procedure LOAD_ITEM
2341 (p_api_version IN NUMBER,
2342 p_init_msg_list IN VARCHAR2 := NULL,
2343 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2344 x_return_status OUT NOCOPY VARCHAR2,
2345 x_msg_count OUT NOCOPY NUMBER,
2346 x_msg_data OUT NOCOPY VARCHAR2,
2347
2348 p_load_level IN NUMBER,
2349 p_preview_flag IN VARCHAR2,
2350 p_itmid IN NUMBER,
2351 p_partnum IN VARCHAR2,
2352 p_model_id IN NUMBER := FND_API.G_MISS_NUM,
2353 p_organization_id IN NUMBER,
2354 p_category_set_id IN NUMBER,
2355 p_retrieve_price IN VARCHAR2,
2356
2357 p_price_list_id IN NUMBER,
2358 p_currency_code IN VARCHAR2,
2359 p_price_request_type IN VARCHAR2,
2360 p_price_event IN VARCHAR2,
2361 p_minisite_id IN NUMBER := NULL,
2362 x_item_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
2363 x_category_id_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
2364 x_configurable OUT NOCOPY VARCHAR2,
2365 x_model_bundle_flag OUT NOCOPY VARCHAR2,
2366 x_uom_csr OUT NOCOPY IBE_CATALOG_REFCURSOR_CSR_TYPE,
2367 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2368 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2369 x_price_status_code OUT NOCOPY VARCHAR2,
2370 x_price_status_text OUT NOCOPY VARCHAR2
2371
2372 )
2373 IS
2374
2375 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_ITEM';
2376 l_api_version CONSTANT NUMBER := 1.0;
2377
2378 l_uom_code_tbl JTF_VARCHAR2_TABLE_100;
2379 l_itmid_tbl JTF_NUMBER_TABLE;
2380 l_model_id_tbl JTF_NUMBER_TABLE;
2381 idx BINARY_INTEGER;
2382 l_start_time NUMBER;
2383 l_end_time NUMBER;
2384 l_init_msg_list VARCHAR2(5);
2385
2386 BEGIN
2387 -- standard call to check for call compatibility
2388 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
2389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2390 END IF;
2391 IF p_init_msg_list IS NULL THEN
2392 l_init_msg_list := FND_API.G_FALSE;
2393 END IF;
2394
2395 -- initialize message list if p_init_msg_list is set to TRUE
2396 IF FND_API.to_Boolean(p_init_msg_list) THEN
2397 FND_MSG_PUB.initialize;
2398 END IF;
2399
2400 -- initialize API return status to success
2401 x_return_status := FND_API.G_RET_STS_SUCCESS;
2402
2403 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2404 IBE_UTIL.debug('IBE_CATALOG_PVT.LOAD_ITEM(+)');
2405 END IF;
2406 l_start_time := DBMS_UTILITY.GET_TIME;
2407 -- load item inventory info
2408 FETCH_ITEM(
2409 p_api_version,
2410 p_init_msg_list,
2411 p_validation_level,
2412 x_return_status,
2413 x_msg_count,
2414 x_msg_data,
2415 p_load_level,
2416 p_preview_flag,
2417 p_itmid,
2418 p_partnum,
2419 p_model_id,
2420 p_organization_id,
2421 p_category_set_id,
2422 x_item_csr,
2423 x_category_id_csr,
2424 x_configurable,
2425 x_model_bundle_flag,
2426 x_uom_csr
2427 );
2428
2429
2430 IF FND_API.to_Boolean(p_retrieve_price) THEN
2431 FETCH_PRICE(
2432 p_itmid =>p_itmid,
2433 p_model_bundle_flag => x_model_bundle_flag,
2434 p_model_id =>p_model_id,
2435 p_organization_id => p_organization_id,
2436 p_price_list_id => p_price_list_id,
2437 p_currency_code => p_currency_code,
2438 p_price_request_type => p_price_request_type,
2439 p_price_event => p_price_event,
2440 p_minisite_id => p_minisite_id,
2441 x_price_csr => x_price_csr,
2442 x_line_index_tbl=> x_line_index_tbl,
2443 x_return_status => x_price_status_code,
2444 x_return_status_text => x_price_status_text);
2445 END IF;
2446
2447 l_end_time := DBMS_UTILITY.GET_TIME;
2448 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2449 IBE_UTIL.debug('IBE_CATALOG_PVT.LOAD_ITEM(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2450 END IF;
2451 --end API body
2452
2453 -- standard call to get message count and if count is 1, get message info.
2454 FND_MSG_PUB.Count_And_Get
2455 ( p_encoded => FND_API.G_FALSE,
2456 p_count => x_msg_count,
2457 p_data => x_msg_data
2458 );
2459 EXCEPTION
2460 WHEN FND_API.G_EXC_ERROR THEN
2461 x_return_status := FND_API.G_RET_STS_ERROR;
2462 FND_MSG_PUB.Count_And_Get
2463 ( p_encoded => FND_API.G_FALSE,
2464 p_count => x_msg_count,
2465 p_data => x_msg_data
2466 );
2467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2469 FND_MSG_PUB.Count_And_Get
2470 ( p_encoded => FND_API.G_FALSE,
2471 p_count => x_msg_count,
2472 p_data => x_msg_data
2473 );
2474 WHEN OTHERS THEN
2475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2476 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2477 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2478 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2479 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2480 FND_MSG_PUB.Add;
2481 IF FND_MSG_PUB.Check_Msg_Level
2482 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2483 THEN FND_MSG_PUB.Add_Exc_Msg
2484 ( G_PKG_NAME,
2485 l_api_name
2486 );
2487 END IF;
2488 FND_MSG_PUB.Count_And_Get
2489 ( p_encoded => FND_API.G_FALSE,
2490 p_count => x_msg_count,
2491 p_data => x_msg_data
2492 );
2493 END LOAD_ITEM;
2494
2495
2496
2497 Procedure FETCH_ITEMS(
2498 p_load_level IN NUMBER,
2499 p_itmid_tbl IN JTF_NUMBER_TABLE,
2500 p_partnum_tbl IN JTF_VARCHAR2_TABLE_100,
2501 p_organization_id IN NUMBER,
2502 p_category_set_id IN NUMBER,
2503 x_category_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
2504 x_configurable_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2505 x_model_bundle_flag_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2506 x_return_status OUT NOCOPY VARCHAR2,
2507 x_return_status_text OUT NOCOPY VARCHAR2
2508 )
2509 IS
2510 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_ITEMS';
2511 l_api_version CONSTANT NUMBER := 1.0;
2512 l_ui_def_id NUMBER;
2513
2514 cursor l_itmid_csr(l_partnum VARCHAR2) IS
2515 select MSIV.inventory_item_id
2516 from mtl_system_items_vl MSIV
2517 where MSIV.concatenated_segments = l_partnum;
2518
2519 cursor l_category_id_csr(l_itmid NUMBER,
2520 l_organization_id NUMBER,
2521 l_category_set_id NUMBER) IS
2522 select MIC.category_id
2523 FROM MTL_SYSTEM_ITEMS_B MSIB, MTL_ITEM_CATEGORIES MIC
2524 WHERE MSIB.inventory_item_id = l_itmid
2525 AND MSIB.organization_id = l_organization_id
2526 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
2527 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
2528 AND MSIB.inventory_item_id = MIC.inventory_item_id
2529 AND MSIB.organization_id = MIC.organization_id
2530 AND mic.CATEGORY_SET_ID = l_category_set_id;
2531
2532 l_itmid NUMBER;
2533 l_itmid_tbl JTF_NUMBER_TABLE;
2534 l_table_index NUMBER;
2535 l_partnum VARCHAR2(40);
2536 l_category_id NUMBER;
2537 l_resp_id NUMBER;
2538 l_resp_appl_id NUMBER;
2539 l_itmid_index NUMBER;
2540 l_start_time NUMBER;
2541 l_end_time NUMBER;
2542
2543 BEGIN
2544
2545 -- initialize API return status to success
2546 x_return_status := FND_API.G_RET_STS_SUCCESS;
2547
2548 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2549 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEMS(+)');
2550 END IF;
2551 l_start_time := DBMS_UTILITY.GET_TIME;
2552
2553 -- get the select and from clauses of the query
2554 l_itmid_tbl := JTF_NUMBER_TABLE();
2555
2556 IF p_itmid_tbl IS NULL THEN
2557 IF p_partnum_tbl IS NULL THEN
2558 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2559 IBE_UTIL.debug('Error: p_itmid_tbl and p_partnum_tbl are both NULL');
2560 END IF;
2561 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_ID_OR_NAME');
2562 FND_MESSAGE.Set_Token('ID_NAME', 'NULL');
2563 FND_MSG_PUB.Add;
2564 RAISE FND_API.G_EXC_ERROR;
2565 RETURN;
2566 ELSE
2567 --loading by part number
2568 l_itmid_index := 1;
2569
2570 FOR l_table_index IN 1..p_partnum_tbl.COUNT LOOP
2571
2572 l_partnum := p_partnum_tbl(l_table_index);
2573 OPEN l_itmid_csr(l_partnum);
2574 FETCH l_itmid_csr INTO l_itmid;
2575
2576 -- constuct item id tbl
2577 IF l_itmid_csr%FOUND THEN
2578 l_itmid_tbl.EXTEND;
2579 l_itmid_tbl(l_table_index) := l_itmid;
2580
2581 ELSE
2582 l_itmid_tbl.EXTEND;
2583 l_itmid_tbl(l_table_index) := FND_API.G_MISS_NUM;
2584 END IF;
2585 CLOSE l_itmid_csr;
2586
2587 END LOOP;
2588 END IF;
2589 --p_itmid_tbl := l_itmid_tbl;
2590 ELSE
2591 -- loading by item_id
2592 l_itmid_tbl := p_itmid_tbl;
2593
2594 END IF;
2595
2596 -- populate x_category_id_tbl
2597 x_category_id_tbl := NULL;
2598 IF (p_category_set_id IS NOT NULL) THEN
2599 x_category_id_tbl := JTF_NUMBER_TABLE();
2600 FOR j IN 1..l_itmid_tbl.COUNT LOOP
2601 OPEN l_category_id_csr(l_itmid_tbl(j),
2602 p_organization_id,
2603 p_category_set_id);
2604 FETCH l_category_id_csr INTO l_category_id;
2605 x_category_id_tbl.extend;
2606 IF l_category_id_csr%FOUND THEN
2607 x_category_id_tbl(j) := l_category_id;
2608 ELSE
2609 x_category_id_tbl(j) := -1;
2610 END IF;
2611 CLOSE l_category_id_csr;
2612 END LOOP;
2613 END IF;
2614
2615 --gzhang 01/21/01, model bundle cache
2616 x_configurable_tbl := JTF_VARCHAR2_TABLE_100();
2617 x_model_bundle_flag_tbl := JTF_VARCHAR2_TABLE_100();
2618 IF (p_itmid_tbl IS NOT NULL) THEN
2619 x_configurable_tbl.EXTEND(p_itmid_tbl.COUNT);
2620 x_model_bundle_flag_tbl.EXTEND(p_itmid_tbl.COUNT);
2621 ELSE
2622 x_configurable_tbl.EXTEND(p_partnum_tbl.COUNT);
2623 x_model_bundle_flag_tbl.EXTEND(p_partnum_tbl.COUNT); -- bug fix#2234615
2624 END IF;
2625
2626 l_resp_id := FND_PROFILE.value('RESP_ID');
2627 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
2628
2629 FOR l_table_index IN 1..x_configurable_tbl.COUNT LOOP
2630 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2631 IBE_UTIL.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2632 ibe_util.debug('item id=' || l_itmid_tbl(l_table_index));
2633 ibe_util.debug('organization id=' || p_organization_id);
2634 ibe_util.debug('responsibility id=' || l_resp_id);
2635 ibe_util.debug('application id=' || l_resp_appl_id);
2636 END IF;
2637 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (l_itmid_tbl(l_table_index), p_organization_id,
2638 SYSDATE, 'DHTML', FND_API.G_MISS_NUM,
2639 l_resp_id, l_resp_appl_id);
2640 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2641 IBE_UTIL.debug('Return from CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
2642 ibe_util.debug('ui_def_id=' || l_ui_def_id);
2643 END IF;
2644
2645 IF l_ui_def_id IS NULL THEN
2646 x_configurable_tbl(l_table_index) := FND_API.G_FALSE;
2647 ELSE
2648 x_configurable_tbl(l_table_index) := FND_API.G_TRUE;
2649 END IF;
2650
2651 IF x_configurable_tbl(l_table_index) = FND_API.G_FALSE THEN
2652 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);
2653 ELSE
2654 x_model_bundle_flag_tbl(l_table_index) := FND_API.G_FALSE;
2655 END IF;
2656 END LOOP;
2657
2658 l_end_time := DBMS_UTILITY.GET_TIME;
2659 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2660 IBE_UTIL.debug('IBE_CATALOG_PVT.FETCH_ITEMS(-), elapsed time (s) ='||(l_end_time-l_start_time)/100);
2661 END IF;
2662 --end API body
2663
2664 -- standard call to get message count and if count is 1, get message info.
2665 EXCEPTION
2666 WHEN OTHERS THEN
2667 x_return_status := FND_API.G_RET_STS_ERROR;
2668 x_return_status_text :=SQLERRM;
2669 END FETCH_ITEMS;
2670
2671 Procedure FETCH_PRICES(
2672 p_itmid_tbl IN JTF_NUMBER_TABLE,
2673 p_model_bundle_flag_tbl IN JTF_VARCHAR2_TABLE_100,
2674 p_model_id_tbl IN JTF_NUMBER_TABLE,
2675 p_organization_id IN NUMBER,
2676 p_price_list_id IN NUMBER,
2677 p_currency_code IN VARCHAR2,
2678 p_price_request_type IN VARCHAR2,
2679 p_price_event IN VARCHAR2,
2680 p_minisite_id IN NUMBER := NULL,
2681 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2682 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2683 x_return_status OUT NOCOPY VARCHAR2,
2684 x_return_status_text OUT NOCOPY VARCHAR2
2685
2686 )
2687 IS
2688 l_api_name CONSTANT VARCHAR2(30) := 'FETCH_PRICES';
2689 l_api_version CONSTANT NUMBER := 1.0;
2690
2691 l_itmid NUMBER;
2692 l_primary_uom VARCHAR2(40);
2693 l_itmid_tbl QP_PREQ_GRP.NUMBER_TYPE;
2694 l_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
2695 l_model_id_tbl JTF_NUMBER_TABLE;
2696 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
2697 l_parentIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2698 l_childIndex_tbl QP_PREQ_GRP.NUMBER_TYPE;
2699 l_table_index NUMBER;
2700 idx BINARY_INTEGER;
2701
2702 CURSOR l_primary_uom_csr(l_item_id NUMBER) IS
2703 SELECT MSIV.primary_uom_code
2704 FROM mtl_system_items_vl MSIV
2705 WHERE MSIV.inventory_item_id = l_item_id;
2706
2707 CURSOR l_uom_csr(l_item_id NUMBER) IS
2708 SELECT miuv.UOM_CODE
2709 FROM MTL_ITEM_UOMS_VIEW miuv
2710 WHERE miuv.INVENTORY_ITEM_ID = l_item_id
2711 AND miuv.ORGANIZATION_ID = p_organization_id
2712 ORDER BY miuv.UOM_CODE;
2713
2714 l_bom_item_csr IBE_CCTBOM_PVT.IBE_CCTBOM_REF_CSR_TYPE;
2715 l_bom_exp_rec IBE_CCTBOM_PVT.IBE_BOM_EXPLOSION_REC;
2716 l_msg_data VARCHAR2(100);
2717 l_msg_count NUMBER;
2718 l_return_status VARCHAR2(30);
2719
2720 l_retrieve_all_uom VARCHAR2(10);
2721 l_start_time NUMBER;
2722 l_end_time NUMBER;
2723
2724 BEGIN
2725 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2726 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':BEGIN');
2727 END IF;
2728 l_start_time := DBMS_UTILITY.GET_TIME;
2729 l_retrieve_all_uom := fnd_profile.value_specific('IBE_RETRIEVE_ALL_ITEM_UOMS', NULL, NULL, 671);
2730 l_model_id_tbl := JTF_NUMBER_TABLE();
2731 idx := 1;
2732 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2733 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':l_retrieve_all_uom='||l_retrieve_all_uom);
2734 END IF;
2735 FOR l_table_index IN 1..p_itmid_tbl.COUNT LOOP
2736 -- construct item id tbl, uomcode tbl to pass into pricing engine if retrieving price
2737 l_itmid := p_itmid_tbl(l_table_index);
2738 IF p_model_bundle_flag_tbl(l_table_index) = FND_API.G_TRUE OR l_retrieve_all_uom = 'N' THEN
2739 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2740 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': only pricing primary uom');
2741 END IF;
2742 OPEN l_primary_uom_csr(l_itmid);
2743 FETCH l_primary_uom_csr INTO l_primary_uom;
2744 IF l_primary_uom_csr%FOUND THEN
2745 l_uom_code_tbl(idx) := l_primary_uom;
2746 ELSE
2747 l_uom_code_tbl(idx) := FND_API.G_MISS_CHAR;
2748 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2749 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':item has no primary uom code, item='||l_itmid);
2750 END IF;
2751 END IF;
2752 CLOSE l_primary_uom_csr;
2753 l_itmid_tbl(idx) := l_itmid;
2754 l_line_quantity_tbl(idx) := 1;
2755 l_model_id_tbl.EXTEND;
2756 --don't covert model_id=-1 here
2757 --IF p_model_id_tbl(l_table_index) <> -1 THEN
2758 l_model_id_tbl(idx) := p_model_id_tbl(l_table_index);
2759 --ELSE
2760 --l_model_id_tbl(idx) := FND_API.G_MISS_NUM;
2761 --END IF;
2762 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2763 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': item ='||l_itmid||', primary uom='||l_primary_uom);
2764 END IF;
2765 idx := idx + 1;
2766
2767 IF p_model_bundle_flag_tbl(l_table_index) = FND_API.G_TRUE THEN
2768 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2769 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':loading component items...');
2770 END IF;
2771 IBE_CCTBOM_PVT.Load_Components(p_api_version =>1.0,
2772 x_return_status=>l_return_status,
2773 x_msg_data=>l_msg_data,
2774 x_msg_count =>l_msg_count,
2775 p_model_id =>l_itmid,
2776 p_organization_id =>p_organization_id,
2777 x_item_csr =>l_bom_item_csr);
2778 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2779 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2780 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':adding component items..., idx='||idx);
2781 END IF;
2782 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2783 WHILE l_bom_item_csr%FOUND LOOP
2784 l_itmid_tbl(idx) := l_bom_exp_rec.component_item_id;
2785 l_uom_code_tbl(idx) := l_bom_exp_rec.primary_uom_code;
2786 l_line_quantity_tbl(idx) := l_bom_exp_rec.component_quantity;
2787 l_model_id_tbl.EXTEND;
2788 l_model_id_tbl(idx) := l_itmid;
2789 idx := idx + 1;
2790 FETCH l_bom_item_csr INTO l_bom_exp_rec;
2791 END LOOP;
2792 CLOSE l_bom_item_csr;
2793 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2794 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':component items added to request line, idx='||idx);
2795 END IF;
2796 ELSE
2797 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2798 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':failed to load component items');
2799 END IF;
2800 END IF;
2801 END IF;
2802 ELSE
2803 FOR l_rec IN l_uom_csr(l_itmid) LOOP
2804 l_uom_code_tbl(idx) := l_rec.uom_code;
2805 l_itmid_tbl(idx) := l_itmid;
2806 l_line_quantity_tbl(idx) := 1;
2807 l_model_id_tbl.EXTEND;
2808 --don't convert model_id=-1 here
2809 --IF p_model_id_tbl(l_table_index) <> -1 THEN
2810 l_model_id_tbl(idx) := p_model_id_tbl(l_table_index);
2811 --ELSE
2812 --l_model_id_tbl(idx) := FND_API.G_MISS_NUM;
2813 --END IF;
2814 --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));
2815 idx := idx + 1;
2816 END LOOP;
2817 END IF;
2818 END LOOP;
2819 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2820 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':uom codes loaded');
2821 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':request pricing...');
2822 END IF;
2823
2824 -- pricing
2825
2826 IBE_PRICE_PVT.PRICE_REQUEST(
2827 p_price_list_id => p_price_list_id,
2828 p_currency_code => p_currency_code,
2829 p_item_tbl => l_itmid_tbl,
2830 p_uom_code_tbl => l_uom_code_tbl,
2831 p_model_id_tbl => l_model_id_tbl,
2832 p_line_quantity_tbl => l_line_quantity_tbl,
2833 p_parentIndex_tbl => l_parentIndex_tbl,
2834 p_childIndex_tbl => l_childIndex_tbl,
2835 p_request_type_code => p_price_request_type,
2836 p_pricing_event => p_price_event,
2837 p_minisite_id => p_minisite_id,
2838 x_price_csr => x_price_csr,
2839 x_line_index_tbl => x_line_index_tbl,
2840 x_return_status => x_return_status,
2841 x_return_status_text=> x_return_status_text);
2842
2843
2844 l_end_time := DBMS_UTILITY.GET_TIME;
2845 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2846 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':pricing done');
2847 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':END, elapsed time (s) ='||(l_end_time-l_start_time)/100);
2848 END IF;
2849 --end API body
2850
2851 EXCEPTION
2852 WHEN OTHERS THEN
2853 x_return_status := FND_API.G_RET_STS_ERROR;
2854 x_return_status_text :=SQLERRM;
2855 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2856 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':'||SQLERRM);
2857 END IF;
2858 END FETCH_PRICES;
2859
2860 Procedure LOAD_ITEMS
2861 (p_api_version IN NUMBER,
2862 p_init_msg_list IN VARCHAR2 := NULL,
2863 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2864 x_return_status OUT NOCOPY VARCHAR2,
2865 x_msg_count OUT NOCOPY NUMBER,
2866 x_msg_data OUT NOCOPY VARCHAR2,
2867
2868 p_load_level IN NUMBER,
2869 p_preview_flag IN VARCHAR2,
2870 p_itmid_tbl IN JTF_NUMBER_TABLE,
2871 p_partnum_tbl IN JTF_VARCHAR2_TABLE_100,
2872 p_model_id_tbl IN JTF_NUMBER_TABLE,
2873 p_organization_id IN NUMBER,
2874 p_category_set_id IN NUMBER,
2875 p_retrieve_price IN VARCHAR2,
2876 p_price_list_id IN NUMBER,
2877 p_currency_code IN VARCHAR2,
2878 p_price_request_type IN VARCHAR2,
2879 p_price_event IN VARCHAR2,
2880 p_minisite_id IN NUMBER := NULL,
2881 x_category_id_tbl OUT NOCOPY JTF_NUMBER_TABLE,
2882 x_configurable_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2883 x_model_bundle_flag_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2884 x_price_csr OUT NOCOPY IBE_PRICE_PVT.PRICE_REFCURSOR_TYPE,
2885 x_line_index_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100,
2886 x_price_status_code OUT NOCOPY VARCHAR2,
2887 x_price_status_text OUT NOCOPY VARCHAR2
2888
2889 ) IS
2890 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_ITEMS';
2891 l_api_version CONSTANT NUMBER := 1.0;
2892
2893 l_return_status_text VARCHAR2(300);
2894 l_start_time NUMBER;
2895 l_end_time NUMBER;
2896 l_init_msg_list VARCHAR2(5);
2897 BEGIN
2898 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2899 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':BEGIN');
2900 END IF;
2901 IF NOT FND_API.Compatible_API_Call (l_api_version,
2902 p_api_version,
2903 l_api_name,
2904 G_PKG_NAME )
2905 THEN
2906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2907 END IF;
2908 IF p_init_msg_list IS NULL THEN
2909 l_init_msg_list := FND_API.G_FALSE;
2910 END IF;
2911 -- initialize message list if L_init_msg_list is set to TRUE
2912 IF FND_API.to_Boolean(l_init_msg_list) THEN
2913 FND_MSG_PUB.initialize;
2914 END IF;
2915
2916 -- initialize API return status to success
2917 x_return_status := FND_API.G_RET_STS_SUCCESS;
2918
2919 -- load inv info
2920 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2921 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': Loading inventory info...');
2922 END IF;
2923 l_start_time := DBMS_UTILITY.GET_TIME;
2924 FETCH_ITEMS(p_load_level =>p_load_level,
2925 p_itmid_tbl =>p_itmid_tbl,
2926 p_partnum_tbl =>p_partnum_tbl,
2927 p_organization_id =>p_organization_id,
2928 p_category_set_id =>p_category_set_id,
2929 x_category_id_tbl =>x_category_id_tbl,
2930 x_configurable_tbl =>x_configurable_tbl,
2931 x_model_bundle_flag_tbl =>x_model_bundle_flag_tbl,
2932 x_return_status =>x_return_status,
2933 x_return_status_text =>l_return_status_text);
2934
2935
2936 -- pricing
2937 IF FND_API.to_Boolean(p_retrieve_price) THEN
2938 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2939 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': Loading prices...');
2940 END IF;
2941 FETCH_PRICES(
2942 p_itmid_tbl =>p_itmid_tbl,
2943 p_model_bundle_flag_tbl => x_model_bundle_flag_tbl,
2944 p_model_id_tbl =>p_model_id_tbl,
2945 p_organization_id => p_organization_id,
2946 p_price_list_id => p_price_list_id,
2947 p_currency_code => p_currency_code,
2948 p_price_request_type => p_price_request_type,
2949 p_price_event => p_price_event,
2950 p_minisite_id => p_minisite_id,
2951 x_price_csr => x_price_csr,
2952 x_line_index_tbl=> x_line_index_tbl,
2953 x_return_status => x_price_status_code,
2954 x_return_status_text => x_price_status_text);
2955 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2956 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||': prices loaded');
2957 END IF;
2958 END IF;
2959 --end API body
2960
2961 -- standard call to get message count and if count is 1, get message info.
2962 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
2963 l_end_time := DBMS_UTILITY.GET_TIME;
2964 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
2965 IBE_UTIL.debug(G_PKG_NAME||'.'||l_api_name||':END, elapsed time (s) ='||(l_end_time-l_start_time)/100);
2966 END IF;
2967
2968 EXCEPTION
2969 WHEN FND_API.G_EXC_ERROR THEN
2970 x_return_status := FND_API.G_RET_STS_ERROR;
2971 FND_MSG_PUB.Count_And_Get
2972 ( p_encoded => FND_API.G_FALSE,
2973 p_count => x_msg_count,
2974 p_data => x_msg_data
2975 );
2976 --gzhang 08/08/2002, bug#2488246
2977 --ibe_util.disable_debug;
2978 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2980 FND_MSG_PUB.Count_And_Get
2981 ( p_encoded => FND_API.G_FALSE,
2982 p_count => x_msg_count,
2983 p_data => x_msg_data
2984 );
2985 --gzhang 08/08/2002, bug#2488246
2986 --ibe_util.disable_debug;
2987 WHEN OTHERS THEN
2988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2989 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
2990 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
2991 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
2992 FND_MESSAGE.Set_Token('REASON', SQLERRM);
2993 FND_MSG_PUB.Add;
2994 IF FND_MSG_PUB.Check_Msg_Level
2995 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2996 THEN FND_MSG_PUB.Add_Exc_Msg
2997 ( G_PKG_NAME,
2998 l_api_name
2999 );
3000 END IF;
3001 FND_MSG_PUB.Count_And_Get
3002 ( p_encoded => FND_API.G_FALSE,
3003 p_count => x_msg_count,
3004 p_data => x_msg_data
3005 );
3006 --gzhang 08/08/2002, bug#2488246
3007 --ibe_util.disable_debug;
3008 END LOAD_ITEMS;
3009
3010 PROCEDURE GET_ITEM_TYPE
3011 (
3012 p_api_version IN NUMBER,
3013 p_init_msg_list IN VARCHAR2 := NULL,
3014 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3015 p_item_ids IN JTF_NUMBER_TABLE,
3016 p_organization_id IN NUMBER,
3017 x_item_type OUT NOCOPY JTF_VARCHAR2_TABLE_100,
3018 x_return_status OUT NOCOPY VARCHAR2,
3019 x_msg_count OUT NOCOPY NUMBER,
3020 x_msg_data OUT NOCOPY VARCHAR2
3021 )
3022 IS
3023 l_api_name CONSTANT VARCHAR2(30):= 'FETCH_ITEM';
3024 l_api_version CONSTANT NUMBER := 1.0;
3025 l_status VARCHAR2(5);
3026 l_service_item_flag VARCHAR2(5);
3027 l_serviceable_product_flag VARCHAR2(5);
3028 l_configurable VARCHAR2(5);
3029 l_model_bundle_flag VARCHAR2(5);
3030 x_query_string VARCHAR2(100);
3031 l_resp_id NUMBER;
3032 l_resp_appl_id NUMBER;
3033 l_ui_def_id NUMBER;
3034 l_start_time NUMBER;
3035 l_end_time NUMBER;
3036 l_table_index NUMBER := 1;
3037 l_temp_key CONSTANT VARCHAR2(20) := 'ITEMIDS_TYPECODE';
3038 l_init_msg_list VARCHAR2(5);
3039 cursor l_itm_attr_csr(l_temp_key VARCHAR2, l_org_id NUMBER) IS
3040 select MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG
3041 from MTL_SYSTEM_ITEMS_VL MSIV
3042 where MSIV.INVENTORY_ITEM_ID IN (select NUM_VAL from IBE_TEMP_TABLE where key =
3043 l_temp_key ) and MSIV.ORGANIZATION_ID = l_org_id;
3044
3045 BEGIN
3046 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
3047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3048 END IF;
3049 IF p_init_msg_list IS NULL THEN
3050 l_init_msg_list := FND_API.G_FALSE;
3051 END IF;
3052 -- initialize message list if p_init_msg_list is set to TRUE
3053 IF FND_API.to_Boolean(p_init_msg_list) THEN
3054 FND_MSG_PUB.initialize;
3055 END IF;
3056
3057 -- initialize API return status to success
3058 x_return_status := FND_API.G_RET_STS_SUCCESS;
3059 -- Populate the itemIds into a temporary table.
3060 FOR i in p_item_ids.FIRST .. p_item_ids.LAST
3061 LOOP
3062
3063 IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
3064 END LOOP;
3065
3066 l_resp_id := FND_PROFILE.value('RESP_ID');
3067 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
3068
3069 x_item_type := JTF_VARCHAR2_TABLE_100();
3070 x_item_type.extend(p_item_ids.COUNT);
3071
3072 FOR l_table_index IN 1..p_item_ids.COUNT LOOP
3073 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3074 ibe_util.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3075 ibe_util.debug('item id=' || p_item_ids(l_table_index));
3076 ibe_util.debug('organization id=' || p_organization_id);
3077 ibe_util.debug('responsibility id=' || l_resp_id);
3078 ibe_util.debug('application id=' || l_resp_appl_id);
3079 END IF;
3080 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (p_item_ids(l_table_index), p_organization_id,
3081 SYSDATE, 'DHTML', FND_API.G_MISS_NUM,
3082 l_resp_id, l_resp_appl_id);
3083 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3084 ibe_util.debug('Return from CZ_CF_API.UI_FOR_ITEM ' ||
3085 TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3086 END IF;
3087 IF l_ui_def_id IS NULL THEN
3088 l_configurable := FND_API.G_FALSE;
3089 ELSE
3090 l_configurable := FND_API.G_TRUE;
3091 END IF;
3092
3093 -- check if the item is a iStore bundle.
3094 IF l_configurable = FND_API.G_FALSE THEN
3095 l_model_bundle_flag := IBE_CCTBOM_PVT.Is_Model_Bundle(p_api_version =>1.0,
3096 p_model_id =>p_item_ids(l_table_index), p_organization_id => p_organization_id);
3097 END IF;
3098
3099
3100 IF (l_model_bundle_flag = FND_API.G_TRUE OR l_configurable = FND_API.G_TRUE) THEN
3101 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_MODEL;
3102 END IF;
3103 END LOOP;
3104
3105 -- check if the item is a service/serviceable product.
3106 l_table_index :=1;
3107 OPEN l_itm_attr_csr(l_temp_key,p_organization_id);
3108 LOOP
3109 FETCH l_itm_attr_csr INTO l_service_item_flag, l_serviceable_product_flag;
3110 EXIT WHEN l_itm_attr_csr%NOTFOUND;
3111 IF ( ( x_item_type(l_table_index) IS NULL ) AND
3112 ( l_service_item_flag = 'Y' )) THEN
3113 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_SERVICE;
3114 ELSIF ( ( x_item_type(l_table_index) IS NULL ) AND
3115 ( l_serviceable_product_flag = 'Y') ) THEN
3116 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_SERVICEABLE;
3117 ELSIF ( x_item_type(l_table_index) IS NULL ) THEN
3118 x_item_type(l_table_index) := IBE_CATALOG_PVT.G_ITEM_STANDARD;
3119 END IF;
3120 l_table_index := l_table_index + 1;
3121 END LOOP;
3122 l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);
3123 CLOSE l_itm_attr_csr;
3124 EXCEPTION
3125 WHEN FND_API.G_EXC_ERROR THEN
3126 x_return_status := FND_API.G_RET_STS_ERROR;
3127 FND_MSG_PUB.Count_And_Get
3128 ( p_encoded => FND_API.G_FALSE,
3129 p_count => x_msg_count,
3130 p_data => x_msg_data
3131 );
3132 --gzhang 08/08/2002, bug#2488246
3133 --ibe_util.disable_debug;
3134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3136 FND_MSG_PUB.Count_And_Get
3137 ( p_encoded => FND_API.G_FALSE,
3138 p_count => x_msg_count,
3139 p_data => x_msg_data
3140 );
3141 --gzhang 08/08/2002, bug#2488246
3142 --ibe_util.disable_debug;
3143 WHEN OTHERS THEN
3144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3145 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
3146 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
3147 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
3148 FND_MESSAGE.Set_Token('REASON', SQLERRM);
3149 FND_MSG_PUB.Add;
3150 IF FND_MSG_PUB.Check_Msg_Level
3151 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3152 THEN FND_MSG_PUB.Add_Exc_Msg
3153 ( G_PKG_NAME,
3154 l_api_name
3155 );
3156 END IF;
3157 FND_MSG_PUB.Count_And_Get
3158 ( p_encoded => FND_API.G_FALSE,
3159 p_count => x_msg_count,
3160 p_data => x_msg_data
3161 );
3162
3163 END GET_ITEM_TYPE;
3164
3165 PROCEDURE IS_ITEM_IN_MINISITE
3166 (
3167 p_api_version IN NUMBER,
3168 p_init_msg_list IN VARCHAR2 := NULL,
3169 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3170 p_item_ids IN JTF_NUMBER_TABLE,
3171 p_minisite_id IN NUMBER,
3172 x_minisite_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
3173 x_return_status OUT NOCOPY VARCHAR2,
3174 x_msg_count OUT NOCOPY NUMBER,
3175 x_msg_data OUT NOCOPY VARCHAR2
3176 )
3177 IS
3178 l_api_name CONSTANT VARCHAR2(30):= 'IS_ITEM_IN_MINISITE';
3179 l_api_version CONSTANT NUMBER := 1.0;
3180 x_query_string VARCHAR2(100);
3181 l_temp_key CONSTANT VARCHAR2(20) := 'ITEMIDS_IN_MSITE';
3182 l_item_id NUMBER;
3183 l_item_index NUMBER;
3184 l_status VARCHAR2(5);
3185 l_init_msg_list VARCHAR2(5);
3186 cursor l_itms_msite_csr(l_temp_key VARCHAR2, l_minisite_id NUMBER) IS
3187 select b.inventory_item_id
3188 from ibe_dsp_msite_sct_items a, ibe_dsp_section_items b
3189 where a.section_item_id = b.section_item_id
3190 and a.mini_site_id = l_minisite_id and b.inventory_item_id IN
3191 (select NUM_VAL from IBE_TEMP_TABLE where key = l_temp_key );
3192
3193 BEGIN
3194
3195 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
3196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3197 END IF;
3198 IF p_init_msg_list IS NULL THEN
3199 l_init_msg_list := FND_API.G_FALSE;
3200 END IF;
3201 -- initialize message list if l_init_msg_list is set to TRUE
3202 IF FND_API.to_Boolean(l_init_msg_list) THEN
3203 FND_MSG_PUB.initialize;
3204 END IF;
3205
3206 -- initialize API return status to success
3207 x_return_status := FND_API.G_RET_STS_SUCCESS;
3208 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3209 ibe_util.debug('Calling IS_ITEM_IN_MINISITE ' );
3210 END IF;
3211
3212 -- Populate the itemIds into a temporary table.
3213 FOR i in p_item_ids.FIRST .. p_item_ids.LAST
3214 LOOP
3215 IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
3216 END LOOP;
3217
3218 x_minisite_item_ids := JTF_NUMBER_TABLE();
3219 l_item_index :=1;
3220
3221 OPEN l_itms_msite_csr(l_temp_key,p_minisite_id);
3222 LOOP
3223 FETCH l_itms_msite_csr INTO l_item_id;
3224 EXIT WHEN l_itms_msite_csr%NOTFOUND;
3225 x_minisite_item_ids.extend();
3226 x_minisite_item_ids(l_item_index) := l_item_id;
3227 l_item_index := l_item_index + 1;
3228 END LOOP;
3229 l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);
3230 CLOSE l_itms_msite_csr;
3231 EXCEPTION
3232 WHEN FND_API.G_EXC_ERROR THEN
3233 x_return_status := FND_API.G_RET_STS_ERROR;
3234 FND_MSG_PUB.Count_And_Get
3235 ( p_encoded => FND_API.G_FALSE,
3236 p_count => x_msg_count,
3237 p_data => x_msg_data
3238 );
3239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3241 FND_MSG_PUB.Count_And_Get
3242 ( p_encoded => FND_API.G_FALSE,
3243 p_count => x_msg_count,
3244 p_data => x_msg_data
3245 );
3246 WHEN OTHERS THEN
3247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3248 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
3249 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
3250 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
3251 FND_MESSAGE.Set_Token('REASON', SQLERRM);
3252 FND_MSG_PUB.Add;
3253 IF FND_MSG_PUB.Check_Msg_Level
3254 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3255 THEN FND_MSG_PUB.Add_Exc_Msg
3256 ( G_PKG_NAME,
3257 l_api_name
3258 );
3259 END IF;
3260 FND_MSG_PUB.Count_And_Get
3261 ( p_encoded => FND_API.G_FALSE,
3262 p_count => x_msg_count,
3263 p_data => x_msg_data
3264 );
3265
3266 END IS_ITEM_IN_MINISITE;
3267
3268 PROCEDURE IS_ITEM_CONFIGURABLE
3269 (
3270 p_item_id IN NUMBER,
3271 p_organization_id IN NUMBER,
3272 x_configurable OUT NOCOPY VARCHAR2
3273 )
3274 IS
3275 l_ui_def_id NUMBER;
3276 l_resp_id NUMBER;
3277 l_resp_appl_id NUMBER;
3278 BEGIN
3279 l_resp_id := FND_PROFILE.value('RESP_ID');
3280 l_resp_appl_id := FND_PROFILE.value('RESP_APPL_ID');
3281 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3282 IBE_UTIL.debug('Calling CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3283 ibe_util.debug('item id=' || p_item_id);
3284 ibe_util.debug('organization id=' || p_organization_id);
3285 ibe_util.debug('responsibility id=' || l_resp_id);
3286 ibe_util.debug('application id=' || l_resp_appl_id);
3287 END IF;
3288
3289 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM (p_item_id, p_organization_id, SYSDATE,
3290 'DHTML', FND_API.G_MISS_NUM, l_resp_id, l_resp_appl_id);
3291 IF (IBE_UTIL.G_DEBUGON = FND_API.G_TRUE) THEN
3292 IBE_UTIL.debug('Return from CZ_CF_API.UI_FOR_ITEM ' || TO_CHAR(SYSDATE,'DD-MON-YYYY:HH24:MI:SS'));
3293 ibe_util.debug('ui_def_id=' || l_ui_def_id);
3294 END IF;
3295 IF l_ui_def_id IS NULL THEN
3296 x_configurable := FND_API.G_FALSE;
3297 ELSE
3298 x_configurable := FND_API.G_TRUE;
3299 END IF;
3300 END IS_ITEM_CONFIGURABLE;
3301
3302 END IBE_CATALOG_PVT;