[Home] [Help]
PACKAGE BODY: APPS.BIS_RSS_PORTLETS_PUB
Source
1 PACKAGE BODY BIS_RSS_PORTLETS_PUB AS
2 /* $Header: BISPRSSB.pls 120.2 2005/11/03 12:53:09 serao noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISPRSSB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Public package for populating the RSS Portlets tables |
13 REM | - BIS_RSS_PORTLETS |
14 REM | - BIS_RSS_PORTLETS_TL |
15 REM | NOTES |
16 REM | 01/20/05 nbarik Initial Creation. |
17 REM | 10/27/05 ugodavar Bug.Fix.4700227 - Procedure Add_Language |
18 REM | |
19 REM +=======================================================================+
20 */
21
22
23 PROCEDURE Load_Row(
24 p_Commit IN VARCHAR2
25 ,p_Rss_Portlet_Rec IN BIS_RSS_PORTLETS_PUB.Rss_Portlet_Type
26 ,x_Return_Status OUT NOCOPY VARCHAR2
27 ,x_Msg_Count OUT NOCOPY NUMBER
28 ,x_Msg_Data OUT NOCOPY VARCHAR2
29 )
30 IS
31 l_commit VARCHAR2(30);
32 BEGIN
33 FND_MSG_PUB.Initialize;
34 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
35 IF p_Commit IS NULL THEN
36 l_commit := FND_API.G_FALSE;
37 ELSE
38 l_commit := p_Commit;
39 END IF;
40
41 INSERT INTO BIS_RSS_PORTLETS
42 ( PORTLET_SHORT_NAME
43 , XML_URL
44 , XSL_URL
45 , CREATED_BY
46 , CREATION_DATE
47 , LAST_UPDATED_BY
48 , LAST_UPDATE_DATE
49 , LAST_UPDATE_LOGIN
50 )
51 SELECT
52 p_Rss_Portlet_Rec.Portlet_Short_Name
53 , p_Rss_Portlet_Rec.Xml_Url
54 , p_Rss_Portlet_Rec.Xsl_Url
55 , nvl(p_Rss_Portlet_Rec.Created_By, FND_GLOBAL.USER_ID)
56 , nvl(p_Rss_Portlet_Rec.Creation_Date, sysdate)
57 , nvl(p_Rss_Portlet_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
58 , nvl(p_Rss_Portlet_Rec.Last_Update_Date, sysdate)
59 , nvl(p_Rss_Portlet_Rec.Last_Update_login, FND_GLOBAL.LOGIN_ID)
60 FROM DUAL;
61
62 INSERT INTO BIS_RSS_PORTLETS_TL
63 ( PORTLET_SHORT_NAME
64 , NAME
65 , DESCRIPTION
66 , LANGUAGE
67 , SOURCE_LANG
68 , CREATED_BY
69 , CREATION_DATE
70 , LAST_UPDATED_BY
71 , LAST_UPDATE_DATE
72 , LAST_UPDATE_LOGIN
73 )
74 SELECT
75 p_Rss_Portlet_Rec.Portlet_Short_Name
76 , p_Rss_Portlet_Rec.Name
77 , p_Rss_Portlet_Rec.Description
78 , language_code
79 , userenv('LANG')
80 , nvl(p_Rss_Portlet_Rec.Created_By, FND_GLOBAL.USER_ID)
81 , nvl(p_Rss_Portlet_Rec.Creation_Date, sysdate)
82 , nvl(p_Rss_Portlet_Rec.Last_Updated_By, FND_GLOBAL.USER_ID)
83 , nvl(p_Rss_Portlet_Rec.Last_Update_Date, sysdate)
84 , nvl(p_Rss_Portlet_Rec.Last_Update_login, FND_GLOBAL.LOGIN_ID)
85 FROM fnd_languages l
86 WHERE L.INSTALLED_FLAG IN ('I', 'B')
87 AND NOT EXISTS
88 (SELECT null
89 FROM bis_rss_portlets_tl
90 WHERE portlet_short_name = p_Rss_Portlet_Rec.Portlet_Short_Name
91 AND language = l.language_code);
92
93 IF (l_commit = FND_API.G_TRUE) THEN
94 COMMIT;
95 END IF;
96
97 EXCEPTION
98 WHEN FND_API.G_EXC_ERROR THEN
99 x_return_status := FND_API.G_RET_STS_ERROR;
100 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
101 ,p_data => x_msg_data);
102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
105 ,p_data => x_msg_data);
106 WHEN NO_DATA_FOUND THEN
107 x_return_status := FND_API.G_RET_STS_ERROR;
108 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
109 ,p_data => x_msg_data);
110 WHEN OTHERS THEN
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
112 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
113 ,p_data => x_msg_data);
114 if (x_msg_data is null) then
115 x_msg_data := SQLERRM;
116 end if;
117 END Load_Row;
118
119 PROCEDURE Translate_Row (
120 p_Commit IN VARCHAR2
121 ,p_Rss_Portlet_Rec IN BIS_RSS_PORTLETS_PUB.Rss_Portlet_Type
122 ,x_return_status OUT NOCOPY VARCHAR2
123 ,x_msg_count OUT NOCOPY NUMBER
124 ,x_msg_data OUT NOCOPY VARCHAR2
125 ) IS
126
127 BEGIN
128
129 FND_MSG_PUB.Initialize;
130 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
131
132 IF p_Rss_Portlet_Rec.Portlet_Short_Name IS NOT NULL THEN
133 BEGIN
134 UPDATE bis_rss_portlets_tl
135 SET name = p_Rss_Portlet_Rec.Name,
136 description = p_Rss_Portlet_Rec.Description,
137 created_by = nvl(p_Rss_Portlet_Rec.Created_By, FND_GLOBAL.USER_ID),
138 creation_date = nvl(p_Rss_Portlet_Rec.Creation_Date, sysdate),
139 last_updated_by = nvl(p_Rss_Portlet_Rec.Last_Updated_By, FND_GLOBAL.USER_ID),
140 last_update_date = nvl(p_Rss_Portlet_Rec.Last_Update_Date, sysdate),
141 last_update_login = nvl(p_Rss_Portlet_Rec.Last_Update_Login, FND_GLOBAL.LOGIN_ID),
142 source_lang = userenv('LANG')
143 WHERE portlet_short_name = p_Rss_Portlet_Rec.Portlet_Short_Name
144 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
145 EXCEPTION
146 WHEN OTHERS THEN NULL;
147 END;
148 END IF;
149
150 IF (p_Commit = FND_API.G_TRUE) THEN
151 COMMIT;
152 END IF;
153
154
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
159 ,p_data => x_msg_data);
160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
163 ,p_data => x_msg_data);
164 WHEN NO_DATA_FOUND THEN
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
167 ,p_data => x_msg_data);
168 WHEN OTHERS THEN
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 FND_MSG_PUB.Count_And_Get( p_encoded => 'F' ,p_count => x_msg_count
171 ,p_data => x_msg_data);
172 IF (x_msg_data IS NULL) THEN
173 x_msg_data := SQLERRM;
174 END IF;
175 END Translate_Row;
176
177 -- procedure to add a language. Bug.Fix.4700227
178 PROCEDURE Add_Language IS
179 BEGIN
180
181 DELETE FROM BIS_RSS_PORTLETS_TL T
182 WHERE NOT EXISTS
183 (
184 SELECT NULL
185 FROM BIS_RSS_PORTLETS B
186 WHERE B.PORTLET_SHORT_NAME = T.PORTLET_SHORT_NAME
187 );
188
189 UPDATE BIS_RSS_PORTLETS_TL T SET (
190 NAME, DESCRIPTION
191 ) = (SELECT
192 B.NAME, B.DESCRIPTION
193 FROM BIS_RSS_PORTLETS_TL B
194 WHERE B.PORTLET_SHORT_NAME = T.PORTLET_SHORT_NAME
195 AND B.LANGUAGE = T.SOURCE_LANG)
196 WHERE (
197 T.PORTLET_SHORT_NAME,
198 T.LANGUAGE
199 ) IN (SELECT
200 SUBT.PORTLET_SHORT_NAME,
201 SUBT.LANGUAGE
202 FROM BIS_RSS_PORTLETS_TL SUBB, BIS_RSS_PORTLETS_TL SUBT
203 WHERE SUBB.PORTLET_SHORT_NAME = SUBT.PORTLET_SHORT_NAME
204 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
205 AND (SUBB.NAME <> SUBT.NAME
206 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
207 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
208 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
209 ));
210
211 INSERT INTO BIS_RSS_PORTLETS_TL
212 (
213 PORTLET_SHORT_NAME,
214 NAME,
215 DESCRIPTION,
216 LANGUAGE,
217 SOURCE_LANG,
218 CREATED_BY,
219 CREATION_DATE,
220 LAST_UPDATED_BY,
221 LAST_UPDATE_DATE,
222 LAST_UPDATE_LOGIN
223 )
224 SELECT
225 B.PORTLET_SHORT_NAME,
226 B.NAME,
227 B.DESCRIPTION,
228 L.LANGUAGE_CODE,
229 B.SOURCE_LANG,
230 B.CREATED_BY,
231 B.CREATION_DATE,
232 B.LAST_UPDATED_BY,
233 B.LAST_UPDATE_DATE,
234 B.LAST_UPDATE_LOGIN
235 FROM BIS_RSS_PORTLETS_TL B, FND_LANGUAGES L
236 WHERE L.INSTALLED_FLAG IN ('I', 'B')
237 AND B.LANGUAGE = USERENV('LANG')
238 AND NOT EXISTS
239 (
240 SELECT NULL
241 FROM BIS_RSS_PORTLETS_TL T
242 WHERE T.PORTLET_SHORT_NAME = B.PORTLET_SHORT_NAME
243 AND T.LANGUAGE = L.LANGUAGE_CODE
244 );
245
246 END Add_Language;
247
248 END BIS_RSS_PORTLETS_PUB;