[Home] [Help]
PACKAGE BODY: APPS.AMV_I_CONTENT_TYPES_PKG
Source
1 package body AMV_I_CONTENT_TYPES_PKG as
2 /* $Header: amvvcthb.pls 120.1 2005/06/21 17:42:14 appldev ship $ */
3 procedure Load_Row(
4 x_content_type_id in VARCHAR2,
5 x_object_version_number in varchar2,
6 x_content_type_name in VARCHAR2,
7 x_description in VARCHAR2,
8 x_owner in varchar2
9 ) IS
10 l_user_id number := 0;
11 l_content_type_id number := 0;
12 l_object_version_number number := 0;
13 l_row_id varchar2(64);
14 begin
15 if (X_OWNER = 'SEED') then
16 l_user_id := 1;
17 end if;
18 l_content_type_id := to_number(x_content_type_id);
19 l_object_version_number := to_number(x_object_version_number);
20 --
21 AMV_I_CONTENT_TYPES_PKG.UPDATE_ROW (
22 X_CONTENT_TYPE_ID => l_content_type_id,
23 X_OBJECT_VERSION_NUMBER => l_object_version_number,
24 X_CONTENT_TYPE_NAME => x_content_type_name,
25 X_DESCRIPTION => x_description,
26 X_LAST_UPDATE_DATE => sysdate,
27 X_LAST_UPDATED_BY => l_user_id,
28 X_LAST_UPDATE_LOGIN => 0
29 );
30 exception
31 when NO_DATA_FOUND then
32 AMV_I_CONTENT_TYPES_PKG.INSERT_ROW (
33 X_ROWID => l_row_id,
34 X_CONTENT_TYPE_ID => l_content_type_id,
35 X_OBJECT_VERSION_NUMBER => l_object_version_number,
36 X_CONTENT_TYPE_NAME => x_content_type_name,
37 X_DESCRIPTION => x_description,
38 X_CREATION_DATE => sysdate,
39 X_CREATED_BY => l_user_id,
40 X_LAST_UPDATE_DATE => sysdate,
41 X_LAST_UPDATED_BY => l_user_id,
42 X_LAST_UPDATE_LOGIN => 0
43 );
44 end Load_Row;
45 --
46 procedure Translate_row (
47 x_content_type_id in NUMBER,
48 x_content_type_name in VARCHAR2,
49 x_description in VARCHAR2,
50 x_owner in varchar2
51 ) IS
52 begin
53 update AMV_I_CONTENT_TYPES_TL set
54 CONTENT_TYPE_NAME = x_content_type_name,
55 DESCRIPTION = x_description,
56 LAST_UPDATE_DATE = sysdate,
57 LAST_UPDATED_BY = decode(x_owner, 'SEED', 1, 0),
58 LAST_UPDATE_LOGIN = 0,
59 SOURCE_LANG = userenv('LANG')
60 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
61 and CONTENT_TYPE_ID = x_content_type_id;
62 end Translate_row;
63 --
64 procedure INSERT_ROW (
65 X_ROWID in out NOCOPY VARCHAR2,
66 X_CONTENT_TYPE_ID in NUMBER,
67 X_OBJECT_VERSION_NUMBER in NUMBER,
68 X_CONTENT_TYPE_NAME in VARCHAR2,
69 X_DESCRIPTION in VARCHAR2,
70 X_CREATION_DATE in DATE,
71 X_CREATED_BY in NUMBER,
72 X_LAST_UPDATE_DATE in DATE,
73 X_LAST_UPDATED_BY in NUMBER,
74 X_LAST_UPDATE_LOGIN in NUMBER
75 ) is
76 cursor C is select ROWID from AMV_I_CONTENT_TYPES_B
77 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID
78 ;
79 begin
80 insert into AMV_I_CONTENT_TYPES_B (
81 CONTENT_TYPE_ID,
82 OBJECT_VERSION_NUMBER,
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_DATE,
86 LAST_UPDATED_BY,
87 LAST_UPDATE_LOGIN
88 ) values (
89 X_CONTENT_TYPE_ID,
90 X_OBJECT_VERSION_NUMBER,
91 X_CREATION_DATE,
92 X_CREATED_BY,
93 X_LAST_UPDATE_DATE,
94 X_LAST_UPDATED_BY,
95 X_LAST_UPDATE_LOGIN
96 );
97
98 insert into AMV_I_CONTENT_TYPES_TL (
99 CONTENT_TYPE_ID,
100 LAST_UPDATE_DATE,
101 LAST_UPDATED_BY,
102 CREATION_DATE,
103 CREATED_BY,
104 LAST_UPDATE_LOGIN,
105 CONTENT_TYPE_NAME,
106 DESCRIPTION,
107 LANGUAGE,
108 SOURCE_LANG
109 ) select
110 X_CONTENT_TYPE_ID,
111 X_LAST_UPDATE_DATE,
112 X_LAST_UPDATED_BY,
113 X_CREATION_DATE,
114 X_CREATED_BY,
115 X_LAST_UPDATE_LOGIN,
116 X_CONTENT_TYPE_NAME,
117 X_DESCRIPTION,
118 L.LANGUAGE_CODE,
119 userenv('LANG')
120 from FND_LANGUAGES L
121 where L.INSTALLED_FLAG in ('I', 'B')
122 and not exists
123 (select NULL
124 from AMV_I_CONTENT_TYPES_TL T
125 where T.CONTENT_TYPE_ID = X_CONTENT_TYPE_ID
126 and T.LANGUAGE = L.LANGUAGE_CODE);
127
128 open c;
129 fetch c into X_ROWID;
130 if (c%notfound) then
131 close c;
132 raise no_data_found;
133 end if;
134 close c;
135
136 end INSERT_ROW;
137
138 procedure LOCK_ROW (
139 X_CONTENT_TYPE_ID in NUMBER,
140 X_OBJECT_VERSION_NUMBER in NUMBER,
141 X_CONTENT_TYPE_NAME in VARCHAR2,
142 X_DESCRIPTION in VARCHAR2
143 ) is
144 cursor c is select
145 OBJECT_VERSION_NUMBER
146 from AMV_I_CONTENT_TYPES_B
147 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID
148 for update of CONTENT_TYPE_ID nowait;
149 recinfo c%rowtype;
150
151 cursor c1 is select
152 CONTENT_TYPE_NAME,
153 DESCRIPTION,
154 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
155 from AMV_I_CONTENT_TYPES_TL
156 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID
157 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
158 for update of CONTENT_TYPE_ID nowait;
159 begin
160 open c;
161 fetch c into recinfo;
162 if (c%notfound) then
163 close c;
164 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
165 app_exception.raise_exception;
166 end if;
167 close c;
168 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
169 ) then
170 null;
171 else
172 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
173 app_exception.raise_exception;
174 end if;
175
176 for tlinfo in c1 loop
177 if (tlinfo.BASELANG = 'Y') then
178 if ( (tlinfo.CONTENT_TYPE_NAME = X_CONTENT_TYPE_NAME)
179 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
180 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
181 ) then
182 null;
183 else
184 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
185 app_exception.raise_exception;
186 end if;
187 end if;
188 end loop;
189 return;
190 end LOCK_ROW;
191
192 procedure UPDATE_ROW (
193 X_CONTENT_TYPE_ID in NUMBER,
194 X_OBJECT_VERSION_NUMBER in NUMBER,
195 X_CONTENT_TYPE_NAME in VARCHAR2,
196 X_DESCRIPTION in VARCHAR2,
197 X_LAST_UPDATE_DATE in DATE,
198 X_LAST_UPDATED_BY in NUMBER,
199 X_LAST_UPDATE_LOGIN in NUMBER
200 ) is
201 begin
202 update AMV_I_CONTENT_TYPES_B set
203 OBJECT_VERSION_NUMBER = decode(X_OBJECT_VERSION_NUMBER,
204 FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER + 1,
205 X_OBJECT_VERSION_NUMBER),
206 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
207 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
208 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
209 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID;
210
211 if (sql%notfound) then
212 raise no_data_found;
213 end if;
214
215 update AMV_I_CONTENT_TYPES_TL set
216 CONTENT_TYPE_NAME =
217 decode(X_CONTENT_TYPE_NAME, FND_API.G_MISS_CHAR, CONTENT_TYPE_NAME,
218 X_CONTENT_TYPE_NAME),
219 DESCRIPTION =
220 decode(X_DESCRIPTION, FND_API.G_MISS_CHAR, DESCRIPTION,
221 X_DESCRIPTION),
222 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
223 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
224 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
225 SOURCE_LANG = userenv('LANG')
226 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID
227 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232 end UPDATE_ROW;
233
234 procedure DELETE_ROW (
235 X_CONTENT_TYPE_ID in NUMBER
236 ) is
237 begin
238 delete from AMV_I_CONTENT_TYPES_TL
239 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID;
240
241 if (sql%notfound) then
242 raise no_data_found;
243 end if;
244
245 delete from AMV_I_CONTENT_TYPES_B
246 where CONTENT_TYPE_ID = X_CONTENT_TYPE_ID;
247
248 if (sql%notfound) then
249 raise no_data_found;
250 end if;
251 end DELETE_ROW;
252
253 procedure ADD_LANGUAGE
254 is
255 begin
256 delete from AMV_I_CONTENT_TYPES_TL T
257 where not exists
258 (select NULL
259 from AMV_I_CONTENT_TYPES_B B
260 where B.CONTENT_TYPE_ID = T.CONTENT_TYPE_ID
261 );
262
263 update AMV_I_CONTENT_TYPES_TL T set (
264 CONTENT_TYPE_NAME,
265 DESCRIPTION
266 ) = (select
267 B.CONTENT_TYPE_NAME,
268 B.DESCRIPTION
269 from AMV_I_CONTENT_TYPES_TL B
270 where B.CONTENT_TYPE_ID = T.CONTENT_TYPE_ID
271 and B.LANGUAGE = T.SOURCE_LANG)
272 where (
273 T.CONTENT_TYPE_ID,
274 T.LANGUAGE
275 ) in (select
276 SUBT.CONTENT_TYPE_ID,
277 SUBT.LANGUAGE
278 from AMV_I_CONTENT_TYPES_TL SUBB, AMV_I_CONTENT_TYPES_TL SUBT
279 where SUBB.CONTENT_TYPE_ID = SUBT.CONTENT_TYPE_ID
280 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
281 and (SUBB.CONTENT_TYPE_NAME <> SUBT.CONTENT_TYPE_NAME
282 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
283 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
284 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
285 ));
286
287 insert into AMV_I_CONTENT_TYPES_TL (
288 CONTENT_TYPE_ID,
289 LAST_UPDATE_DATE,
290 LAST_UPDATED_BY,
291 CREATION_DATE,
292 CREATED_BY,
293 LAST_UPDATE_LOGIN,
294 CONTENT_TYPE_NAME,
295 DESCRIPTION,
296 LANGUAGE,
297 SOURCE_LANG
298 ) select
299 B.CONTENT_TYPE_ID,
300 B.LAST_UPDATE_DATE,
301 B.LAST_UPDATED_BY,
302 B.CREATION_DATE,
303 B.CREATED_BY,
304 B.LAST_UPDATE_LOGIN,
305 B.CONTENT_TYPE_NAME,
306 B.DESCRIPTION,
307 L.LANGUAGE_CODE,
308 B.SOURCE_LANG
309 from AMV_I_CONTENT_TYPES_TL B, FND_LANGUAGES L
310 where L.INSTALLED_FLAG in ('I', 'B')
311 and B.LANGUAGE = userenv('LANG')
312 and not exists
313 (select NULL
314 from AMV_I_CONTENT_TYPES_TL T
315 where T.CONTENT_TYPE_ID = B.CONTENT_TYPE_ID
316 and T.LANGUAGE = L.LANGUAGE_CODE);
317 end ADD_LANGUAGE;
318
319 end AMV_I_CONTENT_TYPES_PKG;