[Home] [Help]
PACKAGE BODY: APPS.IBC_STYLESHEETS_GRP
Source
1 Package Body IBC_STYLESHEETS_GRP as
2 /* $Header: ibcgsshb.pls 120.2 2005/12/29 04:59:26 hsaiyed noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBC_STYLESHEETS_GRP';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ibcgsshb.pls';
6
7 /***********************************************************************************
8 *************************** Private Procedures ************************************
9 ***********************************************************************************/
10
11 PROCEDURE Validate_StyleSheetItem (
12 p_init_msg_list IN VARCHAR2,
13 p_stylesheet_item_id IN NUMBER,
14 x_live_citem_version_id OUT NOCOPY NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 ) AS
19 l_content_item_status VARCHAR2(30);
20 --
21 CURSOR Get_Citem IS
22 select LIVE_CITEM_VERSION_ID, CONTENT_ITEM_STATUS
23 from IBC_CONTENT_ITEMS
24 where content_item_id = p_stylesheet_item_id;
25
26 BEGIN
27 -- Initialize message list if p_init_msg_list is set to TRUE.
28 IF FND_API.to_Boolean( p_init_msg_list ) THEN
29 FND_MSG_PUB.initialize;
30 END IF;
31 -- Initialize API return status to success
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33
34 OPEN Get_Citem;
35 FETCH Get_Citem INTO x_live_citem_version_id, l_content_item_status;
36 -- check if p_content_item_id is valid
37 IF Get_Citem%NOTFOUND THEN
38 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
39 FND_MESSAGE.Set_Name('IBC', 'INVALID_STYLESHEET_ITEM_ID');
40 FND_MESSAGE.Set_token('STYLESHEET_ITEM_ID', p_stylesheet_item_id);
41 FND_MSG_PUB.ADD;
42 END IF;
43 RAISE FND_API.G_EXC_ERROR;
44 END IF;
45 CLOSE Get_Citem;
46
47 -- check if content_item_status is APPROVED
48 IF (l_content_item_status IS NULL OR
49 l_content_item_status <> IBC_UTILITIES_PUB.G_STI_APPROVED) THEN
50 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
51 FND_MESSAGE.Set_Name('IBC', 'IBC_STYLESHEET_NOT_APPROVED');
52 FND_MESSAGE.Set_token('STYLESHEET_ITEM_ID', p_stylesheet_item_id);
53 FND_MSG_PUB.ADD;
54 END IF;
55 RAISE FND_API.G_EXC_ERROR;
56 END IF;
57
58 EXCEPTION
59 WHEN FND_API.G_EXC_ERROR THEN
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
62 p_data => x_msg_data);
63 END Validate_StyleSheetItem;
64
65
66 PROCEDURE Validate_Start_End_Date (
67 p_init_msg_list IN VARCHAR2,
68 p_stylesheet_item_id IN NUMBER,
69 p_stylesheet_version_id IN NUMBER,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2
73 ) AS
74 l_start_date DATE;
75 l_end_date DATE;
76 --
77 CURSOR Get_Citem_Version IS
78 select START_DATE, END_DATE
79 from IBC_CITEM_VERSIONS_B
80 where CITEM_VERSION_ID = p_stylesheet_version_id;
81 BEGIN
82 -- Initialize message list if p_init_msg_list is set to TRUE.
83 IF FND_API.to_Boolean( p_init_msg_list ) THEN
84 FND_MSG_PUB.initialize;
85 END IF;
86 -- Initialize API return status to success
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 OPEN Get_Citem_Version;
90 FETCH Get_Citem_Version INTO l_start_date, l_end_date;
91 CLOSE Get_Citem_Version;
92
93 -- Check Profile if availabe date is enforced
94 IF (FND_PROFILE.Value('IBC_ENFORCE_AVAILABLE_DATE') IS NULL) OR
95 (FND_PROFILE.Value('IBC_ENFORCE_AVAILABLE_DATE') = 'Y') THEN
96 IF (NVL(l_start_date, SYSDATE) > SYSDATE) THEN
97 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
98 FND_MESSAGE.Set_Name('IBC', 'IBC_STYLESHEET_NOT_AVAILABLE');
99 FND_MESSAGE.Set_token('STYLESHEET_ITEM_ID', p_stylesheet_item_id);
100 FND_MESSAGE.Set_token('START_DATE', l_start_date);
101 FND_MSG_PUB.ADD;
102 END IF;
103 RAISE FND_API.G_EXC_ERROR;
104 END IF;
105 END IF;
106
107 -- Check Profile if expiration date is enforced
108 IF (FND_PROFILE.Value('IBC_ENFORCE_EXPIRATION_DATE') IS NULL) OR
109 (FND_PROFILE.Value('IBC_ENFORCE_EXPIRATION_DATE') = 'Y') THEN
110 IF (NVL(l_end_date, SYSDATE) < SYSDATE) THEN
111 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
112 FND_MESSAGE.Set_Name('IBC', 'IBC_STYLESHEET_EXPIRED');
113 FND_MESSAGE.Set_token('STYLESHEET_ITEM_ID', p_stylesheet_item_id);
114 FND_MESSAGE.Set_token('END_DATE', l_end_date);
115 FND_MSG_PUB.ADD;
116 END IF;
117 RAISE FND_API.G_EXC_ERROR;
118 END IF;
119 END IF;
120
121 EXCEPTION
122 WHEN FND_API.G_EXC_ERROR THEN
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
125 p_data => x_msg_data);
126 END Validate_Start_End_Date;
127
128
129
130 PROCEDURE Get_StyleSheet_Private (
131 p_init_msg_list IN VARCHAR2,
132 p_stylesheet_item_id IN NUMBER,
133 p_stylesheet_label_code IN VARCHAR2,
134 x_stylesheet OUT NOCOPY BLOB,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2
138 ) AS
139 l_live_citem_version_id NUMBER;
140 l_citem_version_id NUMBER;
141 --
142 CURSOR Get_Citem_Ver_By_Label IS
143 select citem_version_id
144 from IBC_CITEM_VERSION_LABELS
145 where label_code = p_stylesheet_label_code and
146 content_item_id = p_stylesheet_item_id;
147
148 CURSOR Get_StyleSheet IS
149 select f.FILE_DATA
150 from FND_LOBS f, IBC_CITEM_VERSIONS_VL v
151 where v.CITEM_VERSION_ID = l_citem_version_id
152 and v.ATTACHMENT_FILE_ID = f.FILE_ID;
153
154 BEGIN
155 -- Initialize message list if p_init_msg_list is set to TRUE.
156 IF FND_API.to_Boolean( p_init_msg_list ) THEN
157 FND_MSG_PUB.initialize;
158 END IF;
159 -- Initialize API return status to success
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161
162 --******************* Real Logic Start *********************
163 Validate_StyleSheetItem (
164 p_init_msg_list => p_init_msg_list,
165 p_stylesheet_item_id => p_stylesheet_item_id,
166 x_live_citem_version_id => l_live_citem_version_id,
167 x_return_status => x_return_status,
168 x_msg_count => x_msg_count,
169 x_msg_data => x_msg_data
170 );
171 -- Content Item requested is not valid
172 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
173 RAISE FND_API.G_EXC_ERROR;
174 END IF;
175
176 -- Check if there is a label for this content item
177 IF (p_stylesheet_label_code is NULL) THEN
178 l_citem_version_id := l_live_citem_version_id;
179 ELSE
180 OPEN Get_Citem_Ver_By_Label;
181 FETCH Get_Citem_Ver_By_Label INTO l_citem_version_id;
182 -- Label doesn't exist for this content item id
183 IF (Get_Citem_Ver_By_Label%NOTFOUND) THEN
184 -- Validate Label
185 IF (Ibc_Validate_Pvt.isValidLabel(p_stylesheet_label_code) = FND_API.g_false) THEN
186 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
187 FND_MESSAGE.Set_Name('IBC', 'INVALID_LABEL_CODE');
188 FND_MESSAGE.Set_token('LABEL_CODE', p_stylesheet_label_code);
189 FND_MSG_PUB.ADD;
190 END IF;
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193 x_stylesheet := NULL;
194 return;
195 END IF;
196 CLOSE Get_Citem_Ver_By_Label;
197 END IF;
198
199 -- Check if stylesheet is available yet or expired
200 Validate_Start_End_Date (
201 p_init_msg_list => p_init_msg_list,
202 p_stylesheet_item_id => p_stylesheet_item_id,
203 p_stylesheet_version_id => l_citem_version_id,
204 x_return_status => x_return_status,
205 x_msg_count => x_msg_count,
206 x_msg_data => x_msg_data
207 );
208 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
209 RAISE FND_API.G_EXC_ERROR;
210 END IF;
211
212 -- Retrieve Stylesheet binary file
213 OPEN Get_StyleSheet;
214 FETCH Get_StyleSheet INTO x_stylesheet;
215 CLOSE Get_StyleSheet;
216
217 EXCEPTION
218 WHEN FND_API.G_EXC_ERROR THEN
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
221 p_data => x_msg_data);
222 END Get_StyleSheet_Private;
223
224
225
226 /***********************************************************************************
227 *************************** Public Procedures *************************************
228 ***********************************************************************************/
229
230 PROCEDURE Get_Approved_Default_StyleSht (
231 p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2,
233 p_content_item_id IN NUMBER,
234 p_stylesheet_label_code IN VARCHAR2,
235 x_stylesheet OUT NOCOPY BLOB,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2
239 ) AS
240 --******** local variable for standards **********
241 l_api_name CONSTANT VARCHAR2(40) := 'Get_Approved_Default_StyleSht';
242 l_api_version CONSTANT NUMBER := 1.0;
243 --
244 l_stylesheet_id NUMBER;
245 --
246 CURSOR Get_StyleSheet_Id IS
247 select s.CONTENT_ITEM_ID
248 from IBC_STYLESHEETS s, IBC_CONTENT_ITEMS i
249 where i.CONTENT_ITEM_ID = p_content_item_id
250 and i.CONTENT_TYPE_CODE = s.CONTENT_TYPE_CODE
251 and s.DEFAULT_STYLESHEET_FLAG = FND_API.G_TRUE;
252
253 BEGIN
254 -- ******* Standard Begins ********
255 -- Standard call to check for call compatibility.
256 IF NOT FND_API.Compatible_API_Call (
257 l_api_version,
258 p_api_version,
259 l_api_name,
260 G_PKG_NAME)
261 THEN
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264 -- Initialize message list if p_init_msg_list is set to TRUE.
265 IF FND_API.to_Boolean( p_init_msg_list )
266 THEN
267 FND_MSG_PUB.initialize;
268 END IF;
269
270 -- Initialize API return status to success
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272 --******************* Real Logic Start *********************
273
274 OPEN Get_StyleSheet_Id;
275 FETCH Get_StyleSheet_Id INTO l_stylesheet_id;
276 -- check if default style sheet exists
277 IF Get_StyleSheet_Id%NOTFOUND THEN
278 x_stylesheet := NULL;
279 return;
280 END IF;
281 CLOSE Get_StyleSheet_Id;
282
283 Get_StyleSheet_Private (
284 p_init_msg_list => p_init_msg_list,
285 p_stylesheet_item_id => l_stylesheet_id,
286 p_stylesheet_label_code => p_stylesheet_label_code,
287 x_stylesheet => x_stylesheet,
288 x_return_status => x_return_status,
289 x_msg_count => x_msg_count,
290 x_msg_data => x_msg_data
291 );
292
293 --******************* Real Logic End ***********************
294
295 -- Standard call to get message count and if count=1, get the message
296 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
297 p_data => x_msg_data);
298 EXCEPTION
299 WHEN FND_API.G_EXC_ERROR THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
302 p_data => x_msg_data);
303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
306 p_data => x_msg_data);
307 WHEN OTHERS THEN
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
310 THEN
311 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
312 END IF;
313 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
314 p_data => x_msg_data);
315 END Get_Approved_Default_StyleSht;
316
317
318 PROCEDURE Get_Apprv_Default_StyleSht_Id(
319 p_api_version IN NUMBER,
320 p_init_msg_list IN VARCHAR2,
321 p_content_item_id IN NUMBER,
322 p_stylesheet_label_code IN VARCHAR2,
323 x_stylesheet_id OUT NOCOPY NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2
327 ) AS
328 --******** local variable for standards **********
329 l_api_name CONSTANT VARCHAR2(40) := 'Get_Apprv_Default_StyleSht_Id';
330 l_api_version CONSTANT NUMBER := 1.0;
331 --
332 l_stylesheet_id NUMBER;
333 --
334 CURSOR Get_StyleSheet_Id IS
335 select s.CONTENT_ITEM_ID
336 from IBC_STYLESHEETS s, IBC_CONTENT_ITEMS i
337 where i.CONTENT_ITEM_ID = p_content_item_id
338 and i.CONTENT_TYPE_CODE = s.CONTENT_TYPE_CODE
339 and s.DEFAULT_STYLESHEET_FLAG = FND_API.G_TRUE;
340
341 BEGIN
342 -- ******* Standard Begins ********
343 -- Standard call to check for call compatibility.
344 IF NOT FND_API.Compatible_API_Call (
345 l_api_version,
346 p_api_version,
347 l_api_name,
348 G_PKG_NAME)
349 THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352 -- Initialize message list if p_init_msg_list is set to TRUE.
353 IF FND_API.to_Boolean( p_init_msg_list )
354 THEN
355 FND_MSG_PUB.initialize;
356 END IF;
357
358 -- Initialize API return status to success
359 x_return_status := FND_API.G_RET_STS_SUCCESS;
360 --******************* Real Logic Start *********************
361
362 OPEN Get_StyleSheet_Id;
363 FETCH Get_StyleSheet_Id INTO x_stylesheet_id;
364 -- check if default style sheet exists
365 IF Get_StyleSheet_Id%NOTFOUND THEN
366 x_stylesheet_id := NULL;
367 return;
368 END IF;
369 CLOSE Get_StyleSheet_Id;
370
371 --******************* Real Logic End ***********************
372
373
374 EXCEPTION
375 WHEN FND_API.G_EXC_ERROR THEN
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
378 p_data => x_msg_data);
379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
382 p_data => x_msg_data);
383 WHEN OTHERS THEN
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
386 THEN
387 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
388 END IF;
389 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
390 p_data => x_msg_data);
391 END Get_Apprv_Default_StyleSht_Id;
392
393
394
395 PROCEDURE Get_Approved_StyleSheet (
396 p_api_version IN NUMBER,
397 p_init_msg_list IN VARCHAR2,
398 p_stylesheet_item_id IN NUMBER,
399 p_stylesheet_label_code IN VARCHAR2,
400 x_stylesheet OUT NOCOPY BLOB,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2
404 ) AS
405 --******** local variable for standards **********
406 l_api_name CONSTANT VARCHAR2(40) := 'Get_Approved_StyleSheet';
407 l_api_version CONSTANT NUMBER := 1.0;
408 --
409
410 BEGIN
411 -- ******* Standard Begins ********
412 -- Standard call to check for call compatibility.
413 IF NOT FND_API.Compatible_API_Call (
414 l_api_version,
415 p_api_version,
416 l_api_name,
417 G_PKG_NAME)
418 THEN
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421 -- Initialize message list if p_init_msg_list is set to TRUE.
422 IF FND_API.to_Boolean( p_init_msg_list )
423 THEN
424 FND_MSG_PUB.initialize;
425 END IF;
426
427 -- Initialize API return status to success
428 x_return_status := FND_API.G_RET_STS_SUCCESS;
429 --******************* Real Logic Start *********************
430
431 Get_StyleSheet_Private (
432 p_init_msg_list => p_init_msg_list,
433 p_stylesheet_item_id => p_stylesheet_item_id,
434 p_stylesheet_label_code => p_stylesheet_label_code,
435 x_stylesheet => x_stylesheet,
436 x_return_status => x_return_status,
437 x_msg_count => x_msg_count,
438 x_msg_data => x_msg_data
439 );
440
441 --******************* Real Logic End ***********************
442
443 -- Standard call to get message count and if count=1, get the message
444 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
445 p_data => x_msg_data);
446 EXCEPTION
447 WHEN FND_API.G_EXC_ERROR THEN
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
450 p_data => x_msg_data);
451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
453 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
454 p_data => x_msg_data);
455 WHEN OTHERS THEN
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
458 THEN
459 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
460 END IF;
461 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
462 p_data => x_msg_data);
463 END Get_Approved_StyleSheet;
464
465
466
467 PROCEDURE Get_Approved_StyleSht_RC (
468 p_api_version IN NUMBER,
469 p_init_msg_list IN VARCHAR2,
470 p_stylesheet_ref_code IN VARCHAR2,
471 p_stylesheet_label_code IN VARCHAR2,
472 x_stylesheet OUT NOCOPY BLOB,
473 x_return_status OUT NOCOPY VARCHAR2,
474 x_msg_count OUT NOCOPY NUMBER,
475 x_msg_data OUT NOCOPY VARCHAR2
476 ) AS
477 --******** local variable for standards **********
478 l_api_name CONSTANT VARCHAR2(40) := 'Get_Approved_StyleSht_RC';
479 l_api_version CONSTANT NUMBER := 1.0;
480 --
481 l_stylesheet_id NUMBER;
482 --
483 CURSOR Get_StyleSheet_Id IS
484 select CONTENT_ITEM_ID
485 from IBC_CONTENT_ITEMS
486 where ITEM_REFERENCE_CODE = p_stylesheet_ref_code;
487
488 BEGIN
489 -- ******* Standard Begins ********
490 -- Standard call to check for call compatibility.
491 IF NOT FND_API.Compatible_API_Call (
492 l_api_version,
493 p_api_version,
494 l_api_name,
495 G_PKG_NAME)
496 THEN
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499 -- Initialize message list if p_init_msg_list is set to TRUE.
500 IF FND_API.to_Boolean( p_init_msg_list )
501 THEN
502 FND_MSG_PUB.initialize;
503 END IF;
504
505 -- Initialize API return status to success
506 x_return_status := FND_API.G_RET_STS_SUCCESS;
507 --******************* Real Logic Start *********************
508
509 OPEN Get_StyleSheet_Id;
510 FETCH Get_StyleSheet_Id INTO l_stylesheet_id;
511 IF Get_StyleSheet_Id%NOTFOUND THEN
512 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
513 FND_MESSAGE.Set_Name('IBC', 'IBC_STYLESHT_RC_NOT_FOUND');
514 FND_MESSAGE.Set_token('REF_CODE', p_stylesheet_ref_code);
515 FND_MSG_PUB.ADD;
516 END IF;
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519 CLOSE Get_StyleSheet_Id;
520
521 Get_StyleSheet_Private (
522 p_init_msg_list => p_init_msg_list,
523 p_stylesheet_item_id => l_stylesheet_id,
524 p_stylesheet_label_code => p_stylesheet_label_code,
525 x_stylesheet => x_stylesheet,
526 x_return_status => x_return_status,
527 x_msg_count => x_msg_count,
528 x_msg_data => x_msg_data
529 );
530
531 --******************* Real Logic End ***********************
532
533 -- Standard call to get message count and if count=1, get the message
534 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
535 p_data => x_msg_data);
536 EXCEPTION
537 WHEN FND_API.G_EXC_ERROR THEN
538 x_return_status := FND_API.G_RET_STS_ERROR;
539 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
540 p_data => x_msg_data);
541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
544 p_data => x_msg_data);
545 WHEN OTHERS THEN
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
548 THEN
549 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
550 END IF;
551 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
552 p_data => x_msg_data);
553 END Get_Approved_StyleSht_RC;
554
555
556 -- -----------------------------------------------------------------
557 -- Return the stylesheets associated with the content type of the
558 -- given content item.
559 -- If there is no label-version mapping for a particular stylesheet,
560 -- or the stylesheet does not satisfy all the Runtimer delivery
561 -- requirement, that stylesheet item will NOT be included in the list returned.
562 -- -----------------------------------------------------------------
563 PROCEDURE Get_StyleSheet_Items (
564 p_api_version IN NUMBER,
565 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
566 p_content_item_id IN NUMBER,
567 p_stylesheets_label_code IN VARCHAR2 DEFAULT NULL,
568 x_stylesheet_item_clobs OUT NOCOPY JTF_CLOB_TABLE,
569 x_stylesheet_item_ids OUT NOCOPY JTF_NUMBER_TABLE,
570 x_stylesheet_lang_codes OUT NOCOPY JTF_VARCHAR2_TABLE_100,
571 x_return_status OUT NOCOPY VARCHAR2,
572 x_msg_count OUT NOCOPY NUMBER,
573 x_msg_data OUT NOCOPY VARCHAR2
574 ) AS
575 --******** local variable for standards **********
576 l_api_name CONSTANT VARCHAR2(40) := 'Get_StyleSheet_Items';
577 l_api_version CONSTANT NUMBER := 1.0;
578 --
579 l_in_citem_ids JTF_NUMBER_TABLE;
580 l_in_labels JTF_VARCHAR2_TABLE_100;
581 l_in_lang_codes JTF_VARCHAR2_TABLE_100;
582 --
583 l_out_labels JTF_VARCHAR2_TABLE_100;
584 --
585 CURSOR Get_StyleSheet_Ids IS
586 select s.CONTENT_ITEM_ID, p_stylesheets_label_code as LABEL,
587 userenv('LANG') as LANG
588 from IBC_STYLESHEETS s, IBC_CONTENT_ITEMS i
589 where i.CONTENT_ITEM_ID = p_content_item_id
590 and i.CONTENT_TYPE_CODE = s.CONTENT_TYPE_CODE;
591
592 BEGIN
593 -- ******* Standard Begins ********
594 -- Standard call to check for call compatibility.
595 IF NOT FND_API.Compatible_API_Call (
596 l_api_version,
597 p_api_version,
598 l_api_name,
599 G_PKG_NAME)
600 THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603 -- Initialize message list if p_init_msg_list is set to TRUE.
604 IF FND_API.to_Boolean( p_init_msg_list )
605 THEN
606 FND_MSG_PUB.initialize;
607 END IF;
608
609 -- Initialize API return status to success
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611 --******************* Real Logic Start *********************
612
613 -- // Bulk fetch stylesheet item ids, labels, lang codes into array.
614 OPEN Get_StyleSheet_Ids;
615 FETCH Get_StyleSheet_Ids BULK COLLECT INTO l_in_citem_ids, l_in_labels, l_in_lang_codes;
616
617 -- // Validate p_content_item_id
618 IF (Get_StyleSheet_Ids%NOTFOUND) THEN
619 IF (Ibc_Validate_Pvt.isValidCitem(p_content_item_id) = FND_API.g_false) THEN
620 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
621 FND_MESSAGE.Set_Name('IBC', 'INVALID_CITEM_ID');
622 FND_MESSAGE.Set_token('CITEM_ID', p_content_item_id);
623 FND_MSG_PUB.ADD;
624 END IF;
625 RAISE FND_API.G_EXC_ERROR;
626 END IF;
627 END IF;
628 CLOSE Get_StyleSheet_Ids;
629
630 -- // Call Load_Translated_Content_Items (do VALIDATE dates)
631 IBC_CITEM_RUNTIME_PVT.Load_Translated_Content_Items (
632 p_init_msg_list => p_init_msg_list
633 ,p_content_item_ids => l_in_citem_ids
634 ,p_label_codes => l_in_labels
635 ,p_lang_codes => l_in_lang_codes
636 ,p_validate_dates => FND_API.G_TRUE -- // validate dates
637 ,x_clobs => x_stylesheet_item_clobs
638 ,x_content_item_ids => x_stylesheet_item_ids
639 ,x_label_codes => l_out_labels
640 ,x_lang_codes => x_stylesheet_lang_codes
641 ,x_return_status => x_return_status
642 ,x_msg_count => x_msg_count
643 ,x_msg_data => x_msg_data
644 );
645 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
646 RAISE FND_API.G_EXC_ERROR;
647 END IF;
648
649 -- // List of stylesheets returned is 0, validate Label Code
650 IF (x_stylesheet_item_ids.COUNT = 0) THEN
651 IF (Ibc_Validate_Pvt.isValidLabel(p_stylesheets_label_code) = FND_API.g_false) THEN
652 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
653 FND_MESSAGE.Set_Name('IBC', 'INVALID_LABEL_CODE');
654 FND_MESSAGE.Set_token('LABEL_CODE', p_stylesheets_label_code);
655 FND_MSG_PUB.ADD;
656 END IF;
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659 END IF;
660 --******************* Real Logic End ***********************
661
662 -- Standard call to get message count and if count=1, get the message
663 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
664 p_data => x_msg_data);
665 EXCEPTION
666 WHEN FND_API.G_EXC_ERROR THEN
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
669 p_data => x_msg_data);
670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
673 p_data => x_msg_data);
674 WHEN OTHERS THEN
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
677 THEN
678 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
679 END IF;
680 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
681 p_data => x_msg_data);
682 END Get_StyleSheet_Items;
683
684
685
686
687
688 END IBC_STYLESHEETS_GRP;