1 package body FND_REQUEST_SETS_PKG as
2 /* $Header: AFRSFRSB.pls 120.2 2005/08/19 20:18:41 ckclark ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_REQUEST_SET_ID in NUMBER,
7 X_APPLICATION_ID in NUMBER,
8 X_REQUEST_SET_NAME in VARCHAR2,
9 X_ALLOW_CONSTRAINTS_FLAG in VARCHAR2,
10 X_PRINT_TOGETHER_FLAG in VARCHAR2,
11 X_START_DATE_ACTIVE in DATE,
12 X_START_STAGE in NUMBER,
13 X_END_DATE_ACTIVE in DATE,
14 X_CONCURRENT_PROGRAM_ID in NUMBER,
15 X_OWNER in NUMBER,
16 X_PRINTER in VARCHAR2,
17 X_PRINT_STYLE in VARCHAR2,
18 X_ICON_NAME in VARCHAR2,
19 X_USER_REQUEST_SET_NAME in VARCHAR2,
20 X_DESCRIPTION in VARCHAR2,
21 X_CREATION_DATE in DATE,
22 X_CREATED_BY in NUMBER,
23 X_LAST_UPDATE_DATE in DATE,
24 X_LAST_UPDATED_BY in NUMBER,
25 X_LAST_UPDATE_LOGIN in NUMBER
26 ) is
27 cursor C is select ROWID from FND_REQUEST_SETS
28 where REQUEST_SET_ID = X_REQUEST_SET_ID
29 and APPLICATION_ID = X_APPLICATION_ID
30 ;
31 begin
32 insert into FND_REQUEST_SETS (
33 APPLICATION_ID,
34 REQUEST_SET_ID,
35 REQUEST_SET_NAME,
36 ALLOW_CONSTRAINTS_FLAG,
37 PRINT_TOGETHER_FLAG,
38 START_DATE_ACTIVE,
39 START_STAGE,
40 END_DATE_ACTIVE,
41 CONCURRENT_PROGRAM_ID,
42 OWNER,
43 PRINTER,
44 PRINT_STYLE,
45 ICON_NAME,
46 CREATION_DATE,
47 CREATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN
51 ) values (
52 X_APPLICATION_ID,
53 X_REQUEST_SET_ID,
54 X_REQUEST_SET_NAME,
55 X_ALLOW_CONSTRAINTS_FLAG,
56 X_PRINT_TOGETHER_FLAG,
57 X_START_DATE_ACTIVE,
58 X_START_STAGE,
59 X_END_DATE_ACTIVE,
60 X_CONCURRENT_PROGRAM_ID,
61 X_OWNER,
62 X_PRINTER,
63 X_PRINT_STYLE,
64 X_ICON_NAME,
65 X_CREATION_DATE,
66 X_CREATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATED_BY,
69 X_LAST_UPDATE_LOGIN
70 );
71
72 insert into FND_REQUEST_SETS_TL (
73 APPLICATION_ID,
74 REQUEST_SET_ID,
75 CREATION_DATE,
76 CREATED_BY,
77 LAST_UPDATE_DATE,
78 LAST_UPDATED_BY,
79 LAST_UPDATE_LOGIN,
80 USER_REQUEST_SET_NAME,
81 DESCRIPTION,
82 LANGUAGE,
83 SOURCE_LANG
84 ) select
85 X_APPLICATION_ID,
86 X_REQUEST_SET_ID,
87 X_CREATION_DATE,
88 X_CREATED_BY,
89 X_LAST_UPDATE_DATE,
90 X_LAST_UPDATED_BY,
91 X_LAST_UPDATE_LOGIN,
92 X_USER_REQUEST_SET_NAME,
93 X_DESCRIPTION,
94 L.LANGUAGE_CODE,
95 userenv('LANG')
96 from FND_LANGUAGES L
97 where L.INSTALLED_FLAG in ('I', 'B')
98 and not exists
99 (select NULL
100 from FND_REQUEST_SETS_TL T
101 where T.REQUEST_SET_ID = X_REQUEST_SET_ID
102 and T.APPLICATION_ID = X_APPLICATION_ID
103 and T.LANGUAGE = L.LANGUAGE_CODE);
104
105 open c;
106 fetch c into X_ROWID;
107 if (c%notfound) then
108 close c;
109 raise no_data_found;
110 end if;
111 close c;
112
113 end INSERT_ROW;
114
115 procedure LOCK_ROW (
116 X_REQUEST_SET_ID in NUMBER,
117 X_APPLICATION_ID in NUMBER,
118 X_REQUEST_SET_NAME in VARCHAR2,
119 X_ALLOW_CONSTRAINTS_FLAG in VARCHAR2,
120 X_PRINT_TOGETHER_FLAG in VARCHAR2,
121 X_START_DATE_ACTIVE in DATE,
122 X_START_STAGE in NUMBER,
123 X_END_DATE_ACTIVE in DATE,
124 X_CONCURRENT_PROGRAM_ID in NUMBER,
125 X_OWNER in NUMBER,
126 X_PRINTER in VARCHAR2,
127 X_PRINT_STYLE in VARCHAR2,
128 X_ICON_NAME in VARCHAR2,
129 X_USER_REQUEST_SET_NAME in VARCHAR2,
130 X_DESCRIPTION in VARCHAR2
131 ) is
132 cursor c is select
133 REQUEST_SET_NAME,
134 ALLOW_CONSTRAINTS_FLAG,
135 PRINT_TOGETHER_FLAG,
136 START_DATE_ACTIVE,
137 START_STAGE,
138 END_DATE_ACTIVE,
139 CONCURRENT_PROGRAM_ID,
140 OWNER,
141 PRINTER,
142 PRINT_STYLE,
143 ICON_NAME
144 from FND_REQUEST_SETS
145 where REQUEST_SET_ID = X_REQUEST_SET_ID
146 and APPLICATION_ID = X_APPLICATION_ID
147 for update of REQUEST_SET_ID nowait;
148 recinfo c%rowtype;
149
150 cursor c1 is select
151 USER_REQUEST_SET_NAME,
152 DESCRIPTION
153 from FND_REQUEST_SETS_TL
154 where REQUEST_SET_ID = X_REQUEST_SET_ID
155 and APPLICATION_ID = X_APPLICATION_ID
156 and LANGUAGE = userenv('LANG')
157 for update of REQUEST_SET_ID nowait;
158 tlinfo c1%rowtype;
159
160 begin
161 open c;
162 fetch c into recinfo;
163 if (c%notfound) then
164 close c;
165 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
166 app_exception.raise_exception;
167 end if;
168 close c;
169 if ( (recinfo.REQUEST_SET_NAME = X_REQUEST_SET_NAME)
170 AND (recinfo.ALLOW_CONSTRAINTS_FLAG = X_ALLOW_CONSTRAINTS_FLAG)
171 AND (recinfo.PRINT_TOGETHER_FLAG = X_PRINT_TOGETHER_FLAG)
172 AND (recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
173 AND ((recinfo.START_STAGE = X_START_STAGE)
174 OR ((recinfo.START_STAGE is null) AND (X_START_STAGE is null)))
175 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
176 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
177 AND ((recinfo.CONCURRENT_PROGRAM_ID = X_CONCURRENT_PROGRAM_ID)
178 OR ((recinfo.CONCURRENT_PROGRAM_ID is null) AND (X_CONCURRENT_PROGRAM_ID is null)))
179 AND ((recinfo.OWNER = X_OWNER)
180 OR ((recinfo.OWNER is null) AND (X_OWNER is null)))
181 AND ((recinfo.PRINTER = X_PRINTER)
182 OR ((recinfo.PRINTER is null) AND (X_PRINTER is null)))
183 AND ((recinfo.PRINT_STYLE = X_PRINT_STYLE)
184 OR ((recinfo.PRINT_STYLE is null) AND (X_PRINT_STYLE is null)))
185 AND ((recinfo.ICON_NAME = X_ICON_NAME)
186 OR ((recinfo.ICON_NAME is null) AND (X_ICON_NAME is null)))
187 ) then
188 null;
189 else
190 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
191 app_exception.raise_exception;
192 end if;
193
194 open c1;
195 fetch c1 into tlinfo;
196 if (c1%notfound) then
197 close c1;
198 return;
199 end if;
200 close c1;
201
202 if ( (tlinfo.USER_REQUEST_SET_NAME = X_USER_REQUEST_SET_NAME)
203 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
204 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
205 ) then
206 null;
207 else
208 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
209 app_exception.raise_exception;
210 end if;
211 return;
212 end LOCK_ROW;
213
214 procedure UPDATE_ROW (
215 X_REQUEST_SET_ID in NUMBER,
216 X_APPLICATION_ID in NUMBER,
217 X_REQUEST_SET_NAME in VARCHAR2,
218 X_ALLOW_CONSTRAINTS_FLAG in VARCHAR2,
219 X_PRINT_TOGETHER_FLAG in VARCHAR2,
220 X_START_DATE_ACTIVE in DATE,
221 X_START_STAGE in NUMBER,
222 X_END_DATE_ACTIVE in DATE,
223 X_CONCURRENT_PROGRAM_ID in NUMBER,
224 X_OWNER in NUMBER,
225 X_PRINTER in VARCHAR2,
226 X_PRINT_STYLE in VARCHAR2,
227 X_ICON_NAME in VARCHAR2,
228 X_USER_REQUEST_SET_NAME in VARCHAR2,
229 X_DESCRIPTION in VARCHAR2,
230 X_LAST_UPDATE_DATE in DATE,
231 X_LAST_UPDATED_BY in NUMBER,
232 X_LAST_UPDATE_LOGIN in NUMBER
233 ) is
234 begin
235 update FND_REQUEST_SETS set
236 REQUEST_SET_NAME = X_REQUEST_SET_NAME,
237 ALLOW_CONSTRAINTS_FLAG = X_ALLOW_CONSTRAINTS_FLAG,
238 PRINT_TOGETHER_FLAG = X_PRINT_TOGETHER_FLAG,
239 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
240 START_STAGE = X_START_STAGE,
241 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
242 CONCURRENT_PROGRAM_ID = X_CONCURRENT_PROGRAM_ID,
243 OWNER = X_OWNER,
244 PRINTER = X_PRINTER,
245 PRINT_STYLE = X_PRINT_STYLE,
246 ICON_NAME = X_ICON_NAME,
247 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
248 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
249 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
250 where REQUEST_SET_ID = X_REQUEST_SET_ID
251 and APPLICATION_ID = X_APPLICATION_ID;
252
253 if (sql%notfound) then
254 raise no_data_found;
255 end if;
256
257 update FND_REQUEST_SETS_TL set
258 USER_REQUEST_SET_NAME = X_USER_REQUEST_SET_NAME,
259 DESCRIPTION = X_DESCRIPTION,
260 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
261 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
262 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
263 SOURCE_LANG = userenv('LANG')
264 where REQUEST_SET_ID = X_REQUEST_SET_ID
265 and APPLICATION_ID = X_APPLICATION_ID
266 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
267
268 if (sql%notfound) then
269 raise no_data_found;
270 end if;
271 end UPDATE_ROW;
272
273
274
275 procedure DELETE_ROW (
276 X_REQUEST_SET_ID in NUMBER,
277 X_APPLICATION_ID in NUMBER
278 ) is
279 begin
280
281 -- Disable the concurrent program (if any).
282 begin
283 update fnd_concurrent_programs
284 set enabled_flag='N'
285 where application_id = x_application_id
286 and concurrent_program_id in
287 (select concurrent_program_id
288 from fnd_request_sets
289 where application_id = x_application_id
290 and request_set_id = x_request_set_id
291 and concurrent_program_id is not null);
292 exception
293 when no_data_found then -- We don't care.
294 null;
295 end;
296
297 delete from FND_REQUEST_SETS
298 where APPLICATION_ID = X_APPLICATION_ID
299 and REQUEST_SET_ID = X_REQUEST_SET_ID;
300
301 if (sql%notfound) then
302 raise no_data_found;
303 end if;
304
305 delete from FND_REQUEST_SETS_TL
306 where APPLICATION_ID = X_APPLICATION_ID
307 and REQUEST_SET_ID = X_REQUEST_SET_ID;
308
309 if (sql%notfound) then
310 raise no_data_found;
311 end if;
312
313 /* Do not raise no_data_found on the following rows! */
314
315 delete from FND_STAGE_FN_PARAMETER_VALUES
316 where SET_APPLICATION_ID = X_APPLICATION_ID
317 and REQUEST_SET_ID = X_REQUEST_SET_ID;
318
319 delete from FND_REQUEST_SET_STAGES
320 where SET_APPLICATION_ID = X_APPLICATION_ID
321 and REQUEST_SET_ID = X_REQUEST_SET_ID;
322
323 delete from FND_REQUEST_SET_STAGES_TL
324 where SET_APPLICATION_ID = X_APPLICATION_ID
325 and REQUEST_SET_ID = X_REQUEST_SET_ID;
326
327 delete from FND_REQUEST_SET_PROGRAMS
328 where SET_APPLICATION_ID = X_APPLICATION_ID
329 and REQUEST_SET_ID = X_REQUEST_SET_ID;
330
331 delete from FND_REQUEST_SET_PROGRAM_ARGS
332 where APPLICATION_ID = X_APPLICATION_ID
333 and REQUEST_SET_ID = X_REQUEST_SET_ID;
334
335 end DELETE_ROW;
336
337 procedure ADD_LANGUAGE
338 is
339 begin
340 /* Mar/19/03 requested by Ric Ginsberg */
341 /* The following delete and update statements are commented out */
342 /* as a quick workaround to fix the time-consuming table handler issue */
343 /* Eventually we'll need to turn them into a separate fix_language procedure */
344 /*
345
346 delete from FND_REQUEST_SETS_TL T
347 where not exists
348 (select NULL
349 from FND_REQUEST_SETS B
350 where B.REQUEST_SET_ID = T.REQUEST_SET_ID
351 and B.APPLICATION_ID = T.APPLICATION_ID
352 );
353
354 update FND_REQUEST_SETS_TL T set (
355 USER_REQUEST_SET_NAME,
356 DESCRIPTION
357 ) = (select
358 B.USER_REQUEST_SET_NAME,
359 B.DESCRIPTION
360 from FND_REQUEST_SETS_TL B
361 where B.REQUEST_SET_ID = T.REQUEST_SET_ID
362 and B.APPLICATION_ID = T.APPLICATION_ID
363 and B.LANGUAGE = T.SOURCE_LANG)
364 where (
365 T.REQUEST_SET_ID,
366 T.APPLICATION_ID,
367 T.LANGUAGE
368 ) in (select
369 SUBT.REQUEST_SET_ID,
370 SUBT.APPLICATION_ID,
371 SUBT.LANGUAGE
372 from FND_REQUEST_SETS_TL SUBB, FND_REQUEST_SETS_TL SUBT
373 where SUBB.REQUEST_SET_ID = SUBT.REQUEST_SET_ID
374 and SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
375 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
376 and (SUBB.USER_REQUEST_SET_NAME <> SUBT.USER_REQUEST_SET_NAME
377 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
378 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
379 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
380 ));
381 */
382
383 insert into FND_REQUEST_SETS_TL (
384 APPLICATION_ID,
385 REQUEST_SET_ID,
386 CREATION_DATE,
387 CREATED_BY,
388 LAST_UPDATE_DATE,
389 LAST_UPDATED_BY,
390 LAST_UPDATE_LOGIN,
391 USER_REQUEST_SET_NAME,
392 DESCRIPTION,
393 LANGUAGE,
394 SOURCE_LANG
395 ) select
396 B.APPLICATION_ID,
397 B.REQUEST_SET_ID,
398 B.CREATION_DATE,
399 B.CREATED_BY,
400 B.LAST_UPDATE_DATE,
401 B.LAST_UPDATED_BY,
402 B.LAST_UPDATE_LOGIN,
403 B.USER_REQUEST_SET_NAME,
404 B.DESCRIPTION,
405 L.LANGUAGE_CODE,
406 B.SOURCE_LANG
407 from FND_REQUEST_SETS_TL B, FND_LANGUAGES L
408 where L.INSTALLED_FLAG in ('I', 'B')
409 and B.LANGUAGE = userenv('LANG')
410 and not exists
411 (select NULL
412 from FND_REQUEST_SETS_TL T
413 where T.REQUEST_SET_ID = B.REQUEST_SET_ID
414 and T.APPLICATION_ID = B.APPLICATION_ID
415 and T.LANGUAGE = L.LANGUAGE_CODE);
416 end ADD_LANGUAGE;
417
418 end FND_REQUEST_SETS_PKG;
[Home] [Help]