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