1 package body FND_CONC_RELEASE_CLASSES_PKG as
2 /* $Header: AFCPSC1B.pls 120.2 2005/08/19 20:49:41 jtoruno ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_RELEASE_CLASS_ID in NUMBER,
7 X_APPLICATION_ID in NUMBER,
8 X_RELEASE_CLASS_NAME in VARCHAR2,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_START_DATE_ACTIVE in DATE,
11 X_END_DATE_ACTIVE in DATE,
12 X_UPDATED_FLAG in VARCHAR2,
13 X_USER_RELEASE_CLASS_NAME in VARCHAR2,
14 X_DESCRIPTION in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from FND_CONC_RELEASE_CLASSES
22 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
23 and APPLICATION_ID = X_APPLICATION_ID
24 ;
25 begin
26 insert into FND_CONC_RELEASE_CLASSES (
27 APPLICATION_ID,
28 RELEASE_CLASS_ID,
29 RELEASE_CLASS_NAME,
30 ENABLED_FLAG,
31 START_DATE_ACTIVE,
32 END_DATE_ACTIVE,
33 UPDATED_FLAG,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_DATE,
37 LAST_UPDATED_BY,
38 LAST_UPDATE_LOGIN
39 ) values (
40 X_APPLICATION_ID,
41 X_RELEASE_CLASS_ID,
42 X_RELEASE_CLASS_NAME,
43 X_ENABLED_FLAG,
44 X_START_DATE_ACTIVE,
45 X_END_DATE_ACTIVE,
46 X_UPDATED_FLAG,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into FND_CONC_RELEASE_CLASSES_TL (
55 APPLICATION_ID,
56 RELEASE_CLASS_ID,
57 USER_RELEASE_CLASS_NAME,
58 DESCRIPTION,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 LAST_UPDATE_LOGIN,
62 CREATION_DATE,
63 CREATED_BY,
64 LANGUAGE,
65 SOURCE_LANG
66 ) select
67 X_APPLICATION_ID,
68 X_RELEASE_CLASS_ID,
69 X_USER_RELEASE_CLASS_NAME,
70 X_DESCRIPTION,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATED_BY,
73 X_LAST_UPDATE_LOGIN,
74 X_CREATION_DATE,
75 X_CREATED_BY,
76 L.LANGUAGE_CODE,
77 userenv('LANG')
78 from FND_LANGUAGES L
79 where L.INSTALLED_FLAG in ('I', 'B')
80 and not exists
81 (select NULL
82 from FND_CONC_RELEASE_CLASSES_TL T
83 where T.RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
84 and T.APPLICATION_ID = X_APPLICATION_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_RELEASE_CLASS_ID in NUMBER,
99 X_APPLICATION_ID in NUMBER,
100 X_RELEASE_CLASS_NAME in VARCHAR2,
101 X_ENABLED_FLAG in VARCHAR2,
102 X_START_DATE_ACTIVE in DATE,
103 X_END_DATE_ACTIVE in DATE,
104 X_UPDATED_FLAG in VARCHAR2,
105 X_USER_RELEASE_CLASS_NAME in VARCHAR2,
106 X_DESCRIPTION in VARCHAR2
107 ) is
108 cursor c is select
109 RELEASE_CLASS_NAME,
110 ENABLED_FLAG,
111 START_DATE_ACTIVE,
112 END_DATE_ACTIVE,
113 UPDATED_FLAG
114 from FND_CONC_RELEASE_CLASSES
115 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
116 and APPLICATION_ID = X_APPLICATION_ID
117 for update of RELEASE_CLASS_ID nowait;
118 recinfo c%rowtype;
119
120 cursor c1 is select
121 USER_RELEASE_CLASS_NAME,
122 DESCRIPTION
123 from FND_CONC_RELEASE_CLASSES_TL
124 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
125 and APPLICATION_ID = X_APPLICATION_ID
126 and LANGUAGE = userenv('LANG')
127 for update of RELEASE_CLASS_ID nowait;
128 tlinfo c1%rowtype;
129
130 begin
131 open c;
132 fetch c into recinfo;
133 if (c%notfound) then
134 close c;
135 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 end if;
138 close c;
139 if ( (recinfo.RELEASE_CLASS_NAME = X_RELEASE_CLASS_NAME)
140 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
141 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
142 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
143 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
144 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
145 AND (recinfo.UPDATED_FLAG = X_UPDATED_FLAG)
146 ) then
147 null;
148 else
149 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
150 app_exception.raise_exception;
151 end if;
152
153 open c1;
154 fetch c1 into tlinfo;
155 if (c1%notfound) then
156 close c1;
157 return;
158 end if;
159 close c1;
160
161 if ( (tlinfo.USER_RELEASE_CLASS_NAME = X_USER_RELEASE_CLASS_NAME)
162 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
163 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
164 ) then
165 null;
166 else
167 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
168 app_exception.raise_exception;
169 end if;
170 return;
171 end LOCK_ROW;
172
173 procedure UPDATE_ROW (
174 X_RELEASE_CLASS_ID in NUMBER,
175 X_APPLICATION_ID in NUMBER,
176 X_RELEASE_CLASS_NAME in VARCHAR2,
177 X_ENABLED_FLAG in VARCHAR2,
178 X_START_DATE_ACTIVE in DATE,
179 X_END_DATE_ACTIVE in DATE,
180 X_UPDATED_FLAG in VARCHAR2,
181 X_USER_RELEASE_CLASS_NAME in VARCHAR2,
182 X_DESCRIPTION in VARCHAR2,
183 X_LAST_UPDATE_DATE in DATE,
184 X_LAST_UPDATED_BY in NUMBER,
185 X_LAST_UPDATE_LOGIN in NUMBER
186 ) is
187 begin
188 update FND_CONC_RELEASE_CLASSES set
189 RELEASE_CLASS_NAME = X_RELEASE_CLASS_NAME,
190 ENABLED_FLAG = X_ENABLED_FLAG,
191 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
192 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
193 UPDATED_FLAG = X_UPDATED_FLAG,
194 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
195 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
196 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
197 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
198 and APPLICATION_ID = X_APPLICATION_ID;
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203
204 update FND_CONC_RELEASE_CLASSES_TL set
205 USER_RELEASE_CLASS_NAME = X_USER_RELEASE_CLASS_NAME,
206 DESCRIPTION = X_DESCRIPTION,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
210 SOURCE_LANG = userenv('LANG')
211 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
212 and APPLICATION_ID = X_APPLICATION_ID
213 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218 end UPDATE_ROW;
219
220 procedure DELETE_ROW (
221 X_RELEASE_CLASS_ID in NUMBER,
222 X_APPLICATION_ID in NUMBER
223 ) is
224 begin
225 delete from FND_CONC_RELEASE_CLASSES
226 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
227 and APPLICATION_ID = X_APPLICATION_ID;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 delete from FND_CONC_RELEASE_CLASSES_TL
234 where RELEASE_CLASS_ID = X_RELEASE_CLASS_ID
235 and APPLICATION_ID = X_APPLICATION_ID;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240 end DELETE_ROW;
241
242 procedure ADD_LANGUAGE
243 is
244 begin
245
246 /* Mar/19/03 requested by Ric Ginsberg */
247 /* The following delete and update statements are commented out */
248 /* as a quick workaround to fix the time-consuming table handler issue */
249 /* Eventually we'll need to turn them into a separate fix_language procedure */
250 /*
251
252 delete from FND_CONC_RELEASE_CLASSES_TL T
253 where not exists
254 (select NULL
255 from FND_CONC_RELEASE_CLASSES B
256 where B.RELEASE_CLASS_ID = T.RELEASE_CLASS_ID
257 and B.APPLICATION_ID = T.APPLICATION_ID
258 );
259
260 update FND_CONC_RELEASE_CLASSES_TL T set (
261 USER_RELEASE_CLASS_NAME,
262 DESCRIPTION
263 ) = (select
264 B.USER_RELEASE_CLASS_NAME,
265 B.DESCRIPTION
266 from FND_CONC_RELEASE_CLASSES_TL B
267 where B.RELEASE_CLASS_ID = T.RELEASE_CLASS_ID
268 and B.APPLICATION_ID = T.APPLICATION_ID
269 and B.LANGUAGE = T.SOURCE_LANG)
270 where (
271 T.RELEASE_CLASS_ID,
272 T.APPLICATION_ID,
273 T.LANGUAGE
274 ) in (select
275 SUBT.RELEASE_CLASS_ID,
276 SUBT.APPLICATION_ID,
277 SUBT.LANGUAGE
278 from FND_CONC_RELEASE_CLASSES_TL SUBB, FND_CONC_RELEASE_CLASSES_TL SUBT
279 where SUBB.RELEASE_CLASS_ID = SUBT.RELEASE_CLASS_ID
280 and SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
281 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
282 and (SUBB.USER_RELEASE_CLASS_NAME <> SUBT.USER_RELEASE_CLASS_NAME
283 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
284 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
285 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
286 ));
287 */
288
289 insert into FND_CONC_RELEASE_CLASSES_TL (
290 APPLICATION_ID,
291 RELEASE_CLASS_ID,
292 USER_RELEASE_CLASS_NAME,
293 DESCRIPTION,
294 LAST_UPDATE_DATE,
295 LAST_UPDATED_BY,
296 LAST_UPDATE_LOGIN,
297 CREATION_DATE,
298 CREATED_BY,
299 LANGUAGE,
300 SOURCE_LANG
301 ) select
302 B.APPLICATION_ID,
303 B.RELEASE_CLASS_ID,
304 B.USER_RELEASE_CLASS_NAME,
305 B.DESCRIPTION,
306 B.LAST_UPDATE_DATE,
307 B.LAST_UPDATED_BY,
308 B.LAST_UPDATE_LOGIN,
309 B.CREATION_DATE,
310 B.CREATED_BY,
311 L.LANGUAGE_CODE,
312 B.SOURCE_LANG
313 from FND_CONC_RELEASE_CLASSES_TL B, FND_LANGUAGES L
314 where L.INSTALLED_FLAG in ('I', 'B')
315 and B.LANGUAGE = userenv('LANG')
316 and not exists
317 (select NULL
318 from FND_CONC_RELEASE_CLASSES_TL T
319 where T.RELEASE_CLASS_ID = B.RELEASE_CLASS_ID
320 and T.APPLICATION_ID = B.APPLICATION_ID
321 and T.LANGUAGE = L.LANGUAGE_CODE);
322 end ADD_LANGUAGE;
323
324 end FND_CONC_RELEASE_CLASSES_PKG;
[Home] [Help]