1 package body FND_BUSINESS_PURPOSES_PKG as
2 /* $Header: fndpipub.pls 115.3 2004/04/09 19:19:45 rcsingh noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_PURPOSE_CODE in VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_SEEDED_FLAG in VARCHAR2,
8 X_ATTRIBUTE1 in VARCHAR2,
9 X_ATTRIBUTE2 in VARCHAR2,
10 X_ATTRIBUTE3 in VARCHAR2,
11 X_ATTRIBUTE4 in VARCHAR2,
12 X_ATTRIBUTE5 in VARCHAR2,
13 X_ATTRIBUTE6 in VARCHAR2,
14 X_ATTRIBUTE7 in VARCHAR2,
15 X_ATTRIBUTE8 in VARCHAR2,
16 X_ATTRIBUTE9 in VARCHAR2,
17 X_ATTRIBUTE10 in VARCHAR2,
18 X_ATTRIBUTE11 in VARCHAR2,
19 X_ATTRIBUTE12 in VARCHAR2,
20 X_ATTRIBUTE13 in VARCHAR2,
21 X_ATTRIBUTE14 in VARCHAR2,
22 X_ATTRIBUTE15 in VARCHAR2,
23 X_ATTRIBUTE_CATEGORY in VARCHAR2,
24 X_OBJECT_VERSION_NUMBER in NUMBER,
25 X_PURPOSE_NAME in VARCHAR2,
26 X_DESCRIPTION in VARCHAR2,
27 X_DESCRIPTION_INT in VARCHAR2,
28 X_CREATION_DATE in DATE,
29 X_CREATED_BY in NUMBER,
30 X_LAST_UPDATE_DATE in DATE,
31 X_LAST_UPDATED_BY in NUMBER,
32 X_LAST_UPDATE_LOGIN in NUMBER,
33 X_PURPOSE_DEFAULT_CODE in VARCHAR2
34 ) is
35 cursor C is select ROWID from FND_BUSINESS_PURPOSES_B
36 where PURPOSE_CODE = X_PURPOSE_CODE
37 ;
38 begin
39 insert into FND_BUSINESS_PURPOSES_B (
40 PURPOSE_CODE,
41 APPLICATION_ID,
42 SEEDED_FLAG,
43 ATTRIBUTE1,
44 ATTRIBUTE2,
45 ATTRIBUTE3,
46 ATTRIBUTE4,
47 ATTRIBUTE5,
48 ATTRIBUTE6,
49 ATTRIBUTE7,
50 ATTRIBUTE8,
51 ATTRIBUTE9,
52 ATTRIBUTE10,
53 ATTRIBUTE11,
54 ATTRIBUTE12,
55 ATTRIBUTE13,
56 ATTRIBUTE14,
57 ATTRIBUTE15,
58 ATTRIBUTE_CATEGORY,
59 OBJECT_VERSION_NUMBER,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN,
65 PURPOSE_DEFAULT_CODE
66 ) values (
67 X_PURPOSE_CODE,
68 X_APPLICATION_ID,
69 X_SEEDED_FLAG,
70 X_ATTRIBUTE1,
71 X_ATTRIBUTE2,
72 X_ATTRIBUTE3,
73 X_ATTRIBUTE4,
74 X_ATTRIBUTE5,
75 X_ATTRIBUTE6,
76 X_ATTRIBUTE7,
77 X_ATTRIBUTE8,
78 X_ATTRIBUTE9,
79 X_ATTRIBUTE10,
80 X_ATTRIBUTE11,
81 X_ATTRIBUTE12,
82 X_ATTRIBUTE13,
83 X_ATTRIBUTE14,
84 X_ATTRIBUTE15,
85 X_ATTRIBUTE_CATEGORY,
86 X_OBJECT_VERSION_NUMBER,
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_PURPOSE_DEFAULT_CODE
93 );
94
95 insert into FND_BUSINESS_PURPOSES_TL (
96 PURPOSE_CODE,
97 PURPOSE_NAME,
98 DESCRIPTION,
99 DESCRIPTION_INT,
100 CREATED_BY,
101 CREATION_DATE,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATE_LOGIN,
105 LANGUAGE,
106 SOURCE_LANG
107 ) select
108 X_PURPOSE_CODE,
109 X_PURPOSE_NAME,
110 X_DESCRIPTION,
111 X_DESCRIPTION_INT,
112 X_CREATED_BY,
113 X_CREATION_DATE,
114 X_LAST_UPDATED_BY,
115 X_LAST_UPDATE_DATE,
116 X_LAST_UPDATE_LOGIN,
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 FND_BUSINESS_PURPOSES_TL T
124 where T.PURPOSE_CODE = X_PURPOSE_CODE
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_PURPOSE_CODE in VARCHAR2,
139 X_APPLICATION_ID in NUMBER,
140 X_SEEDED_FLAG in VARCHAR2,
141 X_ATTRIBUTE1 in VARCHAR2,
142 X_ATTRIBUTE2 in VARCHAR2,
143 X_ATTRIBUTE3 in VARCHAR2,
144 X_ATTRIBUTE4 in VARCHAR2,
145 X_ATTRIBUTE5 in VARCHAR2,
146 X_ATTRIBUTE6 in VARCHAR2,
147 X_ATTRIBUTE7 in VARCHAR2,
148 X_ATTRIBUTE8 in VARCHAR2,
149 X_ATTRIBUTE9 in VARCHAR2,
150 X_ATTRIBUTE10 in VARCHAR2,
151 X_ATTRIBUTE11 in VARCHAR2,
152 X_ATTRIBUTE12 in VARCHAR2,
153 X_ATTRIBUTE13 in VARCHAR2,
154 X_ATTRIBUTE14 in VARCHAR2,
155 X_ATTRIBUTE15 in VARCHAR2,
156 X_ATTRIBUTE_CATEGORY in VARCHAR2,
157 X_OBJECT_VERSION_NUMBER in NUMBER,
158 X_PURPOSE_NAME in VARCHAR2,
159 X_DESCRIPTION in VARCHAR2,
160 X_DESCRIPTION_INT in VARCHAR2,
161 X_PURPOSE_DEFAULT_CODE in VARCHAR2
162 ) is
163 cursor c is select
164 APPLICATION_ID,
165 SEEDED_FLAG,
166 ATTRIBUTE1,
167 ATTRIBUTE2,
168 ATTRIBUTE3,
169 ATTRIBUTE4,
170 ATTRIBUTE5,
171 ATTRIBUTE6,
172 ATTRIBUTE7,
173 ATTRIBUTE8,
174 ATTRIBUTE9,
175 ATTRIBUTE10,
176 ATTRIBUTE11,
177 ATTRIBUTE12,
178 ATTRIBUTE13,
179 ATTRIBUTE14,
180 ATTRIBUTE15,
181 ATTRIBUTE_CATEGORY,
182 OBJECT_VERSION_NUMBER,
183 PURPOSE_DEFAULT_CODE
184 from FND_BUSINESS_PURPOSES_B
185 where PURPOSE_CODE = X_PURPOSE_CODE
186 for update of PURPOSE_CODE nowait;
187 recinfo c%rowtype;
188
189 cursor c1 is select
190 PURPOSE_NAME,
191 DESCRIPTION,
192 DESCRIPTION_INT,
193 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
194 from FND_BUSINESS_PURPOSES_TL
195 where PURPOSE_CODE = X_PURPOSE_CODE
196 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
197 for update of PURPOSE_CODE nowait;
198 begin
199 open c;
200 fetch c into recinfo;
201 if (c%notfound) then
202 close c;
203 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
204 app_exception.raise_exception;
205 end if;
206 close c;
207 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
208 AND (recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
209 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
210 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
211 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
212 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
213 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
214 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
215 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
216 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
217 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
218 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
219 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
220 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
221 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
222 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
223 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
224 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
225 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
226 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
227 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
228 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
229 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
230 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
231 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
232 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
233 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
234 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
235 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
236 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
237 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
238 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
239 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
240 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
241 AND ((recinfo.PURPOSE_DEFAULT_CODE = X_PURPOSE_DEFAULT_CODE)
242 OR ((recinfo.PURPOSE_DEFAULT_CODE is null) AND (X_PURPOSE_DEFAULT_CODE is null)))
243 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
244 ) then
245 null;
246 else
247 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
248 app_exception.raise_exception;
249 end if;
250
251 for tlinfo in c1 loop
252 if (tlinfo.BASELANG = 'Y') then
253 if ( (tlinfo.PURPOSE_NAME = X_PURPOSE_NAME)
254 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
255 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
256 AND ((tlinfo.DESCRIPTION_INT = X_DESCRIPTION_INT)
257 OR ((tlinfo.DESCRIPTION_INT is null) AND (X_DESCRIPTION_INT is null)))
258 ) then
259 null;
260 else
261 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
262 app_exception.raise_exception;
263 end if;
264 end if;
265 end loop;
266 return;
267 end LOCK_ROW;
268
269 procedure UPDATE_ROW (
270 X_PURPOSE_CODE in VARCHAR2,
271 X_APPLICATION_ID in NUMBER,
272 X_SEEDED_FLAG in VARCHAR2,
273 X_ATTRIBUTE1 in VARCHAR2,
274 X_ATTRIBUTE2 in VARCHAR2,
275 X_ATTRIBUTE3 in VARCHAR2,
276 X_ATTRIBUTE4 in VARCHAR2,
277 X_ATTRIBUTE5 in VARCHAR2,
278 X_ATTRIBUTE6 in VARCHAR2,
279 X_ATTRIBUTE7 in VARCHAR2,
280 X_ATTRIBUTE8 in VARCHAR2,
281 X_ATTRIBUTE9 in VARCHAR2,
282 X_ATTRIBUTE10 in VARCHAR2,
283 X_ATTRIBUTE11 in VARCHAR2,
284 X_ATTRIBUTE12 in VARCHAR2,
285 X_ATTRIBUTE13 in VARCHAR2,
286 X_ATTRIBUTE14 in VARCHAR2,
287 X_ATTRIBUTE15 in VARCHAR2,
288 X_ATTRIBUTE_CATEGORY in VARCHAR2,
289 X_OBJECT_VERSION_NUMBER in NUMBER,
290 X_PURPOSE_NAME in VARCHAR2,
291 X_DESCRIPTION in VARCHAR2,
292 X_DESCRIPTION_INT in VARCHAR2,
293 X_LAST_UPDATE_DATE in DATE,
294 X_LAST_UPDATED_BY in NUMBER,
295 X_LAST_UPDATE_LOGIN in NUMBER,
296 X_PURPOSE_DEFAULT_CODE in VARCHAR2
297 ) is
298 begin
299 update FND_BUSINESS_PURPOSES_B set
300 APPLICATION_ID = X_APPLICATION_ID,
301 SEEDED_FLAG = X_SEEDED_FLAG,
302 ATTRIBUTE1 = X_ATTRIBUTE1,
303 ATTRIBUTE2 = X_ATTRIBUTE2,
304 ATTRIBUTE3 = X_ATTRIBUTE3,
305 ATTRIBUTE4 = X_ATTRIBUTE4,
306 ATTRIBUTE5 = X_ATTRIBUTE5,
307 ATTRIBUTE6 = X_ATTRIBUTE6,
308 ATTRIBUTE7 = X_ATTRIBUTE7,
309 ATTRIBUTE8 = X_ATTRIBUTE8,
310 ATTRIBUTE9 = X_ATTRIBUTE9,
311 ATTRIBUTE10 = X_ATTRIBUTE10,
312 ATTRIBUTE11 = X_ATTRIBUTE11,
313 ATTRIBUTE12 = X_ATTRIBUTE12,
314 ATTRIBUTE13 = X_ATTRIBUTE13,
315 ATTRIBUTE14 = X_ATTRIBUTE14,
316 ATTRIBUTE15 = X_ATTRIBUTE15,
317 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
318 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
319 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
320 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
321 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
322 PURPOSE_DEFAULT_CODE = X_PURPOSE_DEFAULT_CODE
323 where PURPOSE_CODE = X_PURPOSE_CODE;
324
325 if (sql%notfound) then
326 raise no_data_found;
327 end if;
328
329 update FND_BUSINESS_PURPOSES_TL set
330 PURPOSE_NAME = X_PURPOSE_NAME,
331 DESCRIPTION = X_DESCRIPTION,
332 DESCRIPTION_INT = X_DESCRIPTION_INT,
333 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
334 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
335 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
336 SOURCE_LANG = userenv('LANG')
337 where PURPOSE_CODE = X_PURPOSE_CODE
338 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
339
340 if (sql%notfound) then
341 raise no_data_found;
342 end if;
343 end UPDATE_ROW;
344
345 procedure DELETE_ROW (
346 X_PURPOSE_CODE in VARCHAR2
347 ) is
348 begin
349 delete from FND_BUSINESS_PURPOSES_TL
350 where PURPOSE_CODE = X_PURPOSE_CODE;
351
352 if (sql%notfound) then
353 raise no_data_found;
354 end if;
355
356 delete from FND_BUSINESS_PURPOSES_B
357 where PURPOSE_CODE = X_PURPOSE_CODE;
358
359 if (sql%notfound) then
360 raise no_data_found;
361 end if;
362 end DELETE_ROW;
363
364 procedure ADD_LANGUAGE
365 is
366 begin
367 delete from FND_BUSINESS_PURPOSES_TL T
368 where not exists
369 (select NULL
370 from FND_BUSINESS_PURPOSES_B B
371 where B.PURPOSE_CODE = T.PURPOSE_CODE
372 );
373
374 update FND_BUSINESS_PURPOSES_TL T set (
375 PURPOSE_NAME,
376 DESCRIPTION,
377 DESCRIPTION_INT
378 ) = (select
379 B.PURPOSE_NAME,
380 B.DESCRIPTION,
381 B.DESCRIPTION_INT
382 from FND_BUSINESS_PURPOSES_TL B
383 where B.PURPOSE_CODE = T.PURPOSE_CODE
384 and B.LANGUAGE = T.SOURCE_LANG)
385 where (
386 T.PURPOSE_CODE,
387 T.LANGUAGE
388 ) in (select
389 SUBT.PURPOSE_CODE,
390 SUBT.LANGUAGE
391 from FND_BUSINESS_PURPOSES_TL SUBB, FND_BUSINESS_PURPOSES_TL SUBT
392 where SUBB.PURPOSE_CODE = SUBT.PURPOSE_CODE
393 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
394 and (SUBB.PURPOSE_NAME <> SUBT.PURPOSE_NAME
395 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
396 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
397 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
398 or SUBB.DESCRIPTION_INT <> SUBT.DESCRIPTION_INT
399 or (SUBB.DESCRIPTION_INT is null and SUBT.DESCRIPTION_INT is not null)
400 or (SUBB.DESCRIPTION_INT is not null and SUBT.DESCRIPTION_INT is null)
401 ));
402
403 insert into FND_BUSINESS_PURPOSES_TL (
404 PURPOSE_CODE,
405 PURPOSE_NAME,
406 DESCRIPTION,
407 DESCRIPTION_INT,
408 CREATED_BY,
409 CREATION_DATE,
410 LAST_UPDATED_BY,
411 LAST_UPDATE_DATE,
412 LAST_UPDATE_LOGIN,
413 LANGUAGE,
414 SOURCE_LANG
415 ) select /*+ ORDERED */
416 B.PURPOSE_CODE,
417 B.PURPOSE_NAME,
418 B.DESCRIPTION,
419 B.DESCRIPTION_INT,
420 B.CREATED_BY,
421 B.CREATION_DATE,
422 B.LAST_UPDATED_BY,
423 B.LAST_UPDATE_DATE,
424 B.LAST_UPDATE_LOGIN,
425 L.LANGUAGE_CODE,
426 B.SOURCE_LANG
427 from FND_BUSINESS_PURPOSES_TL B, FND_LANGUAGES L
428 where L.INSTALLED_FLAG in ('I', 'B')
429 and B.LANGUAGE = userenv('LANG')
430 and not exists
431 (select NULL
432 from FND_BUSINESS_PURPOSES_TL T
433 where T.PURPOSE_CODE = B.PURPOSE_CODE
434 and T.LANGUAGE = L.LANGUAGE_CODE);
435 end ADD_LANGUAGE;
436
437 Procedure TRANSLATE_ROW
438 (x_PURPOSE_CODE in varchar2,
439 x_PURPOSE_NAME in varchar2,
440 x_Last_update_date in date,
441 x_last_updated_by in number,
442 x_last_update_login in number)
443 is
444 begin
445
446 Update FND_BUSINESS_PURPOSES_TL set
447 purpose_name = nvl(x_PURPOSE_NAME,purpose_name),
448 last_update_date = nvl(x_last_update_date,sysdate),
449 last_updated_by = x_last_updated_by,
450 last_update_login = 0,
451 source_lang = userenv('LANG')
452 where purpose_code = x_PURPOSE_CODE
453 and userenv('LANG') in (LANGUAGE,SOURCE_LANG);
454
455 if (sql%notfound) then
456 raise no_data_found;
457 end if;
458 end TRANSLATE_ROW;
459
460
461 end FND_BUSINESS_PURPOSES_PKG;
[Home] [Help]