[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PS_CNDCLSES_B_PKG
Source
1 PACKAGE BODY AMS_IBA_PS_CNDCLSES_B_PKG as
2 /* $Header: amstcclb.pls 120.0 2005/05/31 16:25:45 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_IBA_PS_CNDCLSES_B_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_IBA_PS_CNDCLSES_B_PKG';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstcclb.pls';
18
19 ----------------------------------------------------------
20 ---- MEDIA ----
21 ----------------------------------------------------------
22
23 -- ========================================================
24 --
25 -- NAME
26 -- createInsertBody
27 --
28 -- PURPOSE
29 --
30 -- NOTES
31 --
32 -- HISTORY
33 --
34 -- ========================================================
35 PROCEDURE Insert_Row(
36 p_created_by NUMBER,
37 p_creation_date DATE,
38 p_last_updated_by NUMBER,
39 p_last_update_date DATE,
40 p_last_update_login NUMBER,
41 px_object_version_number IN OUT NOCOPY NUMBER,
42 px_cnd_clause_id IN OUT NOCOPY NUMBER,
43 p_cnd_clause_datatype VARCHAR2,
44 p_cnd_clause_ref_code VARCHAR2,
45 p_cnd_comp_operator VARCHAR2,
46 p_cnd_default_value VARCHAR2,
47 p_cnd_clause_name VARCHAR2,
48 p_cnd_clause_description VARCHAR2)
49 IS
50 x_rowid VARCHAR2(30);
51
52 BEGIN
53
54 px_object_version_number := 1;
55
56 INSERT INTO AMS_IBA_PS_CNDCLSES_B(
57 created_by,
58 creation_date,
59 last_updated_by,
60 last_update_date,
61 last_update_login,
62 object_version_number,
63 cnd_clause_id,
64 cnd_clause_datatype,
65 cnd_clause_ref_code,
66 cnd_comp_operator,
67 cnd_default_value
68 ) VALUES (
69 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
70 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
71 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
72 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
73 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
74 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
75 DECODE( px_cnd_clause_id, FND_API.g_miss_num, NULL, px_cnd_clause_id),
76 DECODE( p_cnd_clause_datatype, FND_API.g_miss_char, NULL, p_cnd_clause_datatype),
77 DECODE( p_cnd_clause_ref_code, FND_API.g_miss_char, NULL, p_cnd_clause_ref_code),
78 DECODE( p_cnd_comp_operator, FND_API.g_miss_char, NULL, p_cnd_comp_operator),
79 DECODE( p_cnd_default_value, FND_API.g_miss_char, NULL, p_cnd_default_value));
80
81 INSERT INTO ams_iba_ps_cndclses_tl (
82 created_by,
83 creation_date,
84 last_updated_by,
85 last_update_date,
86 last_update_login,
87 cnd_clause_id,
88 object_version_number,
89 cnd_clause_name,
90 cnd_clause_description,
91 language,
92 source_lang
93 ) SELECT
94 FND_GLOBAL.user_id,
95 SYSDATE,
96 FND_GLOBAL.user_id,
97 SYSDATE,
98 FND_GLOBAL.conc_login_id,
99 DECODE( px_cnd_clause_id, FND_API.g_miss_num, NULL, px_cnd_clause_id),
100 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
101
102 DECODE( p_CND_CLAUSE_NAME, FND_API.G_MISS_CHAR, NULL, p_CND_CLAUSE_NAME),
103 DECODE( p_CND_CLAUSE_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_CND_CLAUSE_DESCRIPTION),
104 l.language_code,
105 USERENV('LANG')
106 FROM fnd_languages l
107 WHERE l.installed_flag IN ('I', 'B')
108 AND NOT EXISTS
109 (SELECT null
110 FROM ams_iba_ps_cndclses_tl t
111 WHERE t.cnd_clause_id = DECODE( px_cnd_clause_id, FND_API.g_miss_num, NULL, px_cnd_clause_id)
112 AND t.language = l.language_code);
113
114 END Insert_Row;
115
116
117 ----------------------------------------------------------
118 ---- MEDIA ----
119 ----------------------------------------------------------
120
121 -- ========================================================
122 --
123 -- NAME
124 -- createUpdateBody
125 --
126 -- PURPOSE
127 --
128 -- NOTES
129 --
130 -- HISTORY
131 --
132 -- ========================================================
133 PROCEDURE Update_Row(
134 p_created_by NUMBER,
135 p_creation_date DATE,
136 p_last_updated_by NUMBER,
137 p_last_update_date DATE,
138 p_last_update_login NUMBER,
139 p_object_version_number NUMBER,
140 p_cnd_clause_id NUMBER,
141 p_cnd_clause_datatype VARCHAR2,
142 p_cnd_clause_ref_code VARCHAR2,
143 p_cnd_comp_operator VARCHAR2,
144 p_cnd_default_value VARCHAR2,
145 p_cnd_clause_name VARCHAR2,
146 p_cnd_clause_description VARCHAR2)
147
148 IS
149 BEGIN
150 Update AMS_IBA_PS_CNDCLSES_B
151 SET
152 created_by = DECODE( p_created_by, FND_API.g_miss_num, created_by, p_created_by),
153 creation_date = DECODE( p_creation_date, FND_API.g_miss_date, creation_date, p_creation_date),
154 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
155 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
156 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
157 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
158 cnd_clause_id = DECODE( p_cnd_clause_id, FND_API.g_miss_num, cnd_clause_id, p_cnd_clause_id),
159 cnd_clause_datatype = DECODE( p_cnd_clause_datatype, FND_API.g_miss_char, cnd_clause_datatype, p_cnd_clause_datatype),
160 cnd_clause_ref_code = DECODE( p_cnd_clause_ref_code, FND_API.g_miss_char, cnd_clause_ref_code, p_cnd_clause_ref_code),
161 cnd_comp_operator = DECODE( p_cnd_comp_operator, FND_API.g_miss_char, cnd_comp_operator, p_cnd_comp_operator),
162 cnd_default_value = DECODE( p_cnd_default_value, FND_API.g_miss_char, cnd_default_value, p_cnd_default_value)
163 WHERE CND_CLAUSE_ID = p_CND_CLAUSE_ID
164 AND object_version_number = p_object_version_number;
165
166 IF (SQL%NOTFOUND) THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 UPDATE ams_iba_ps_cndclses_tl SET
171 cnd_clause_name = DECODE( p_CND_CLAUSE_NAME, FND_API.G_MISS_CHAR, cnd_clause_name, p_CND_CLAUSE_NAME),
172 cnd_clause_description = DECODE( p_CND_CLAUSE_DESCRIPTION, FND_API.G_MISS_CHAR, cnd_clause_description, p_CND_CLAUSE_DESCRIPTION),
173 last_update_date = SYSDATE,
174 last_updated_by = FND_GLOBAL.user_id,
175 last_update_login = FND_GLOBAL.conc_login_id,
176 source_lang = USERENV('LANG')
177 WHERE cnd_clause_id = p_cnd_clause_id
178 AND USERENV('LANG') IN (language, source_lang);
179
180 IF (SQL%NOTFOUND) THEN
181 RAISE NO_DATA_FOUND;
182 END IF;
183
184 END Update_Row;
185
186 ----------------------------------------------------------
187 ---- MEDIA ----
188 ----------------------------------------------------------
189
190 -- ========================================================
191 --
192 -- NAME
193 -- createDeleteBody
194 --
195 -- PURPOSE
196 --
197 -- NOTES
198 --
199 -- HISTORY
200 --
201 -- ========================================================
202 PROCEDURE Delete_Row(
203 p_CND_CLAUSE_ID NUMBER)
204 IS
205 BEGIN
206 DELETE FROM AMS_IBA_PS_CNDCLSES_B
207 WHERE CND_CLAUSE_ID = p_CND_CLAUSE_ID;
208 If (SQL%NOTFOUND) then
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 End If;
211
212 DELETE FROM AMS_IBA_PS_CNDCLSES_TL
213 WHERE CND_CLAUSE_ID = p_CND_CLAUSE_ID;
214 If (SQL%NOTFOUND) then
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 End If;
217
218 END Delete_Row ;
219
220 procedure ADD_LANGUAGE
221 is
222 begin
223 delete from AMS_IBA_PS_CNDCLSES_TL T
224 where not exists
225 (select NULL
226 from AMS_IBA_PS_CNDCLSES_B B
227 where B.CND_CLAUSE_ID = T.CND_CLAUSE_ID
228 );
229
230 update AMS_IBA_PS_CNDCLSES_TL T set (
231 CND_CLAUSE_NAME,
232 CND_CLAUSE_DESCRIPTION
233 ) = (select
234 B.CND_CLAUSE_NAME,
235 B.CND_CLAUSE_DESCRIPTION
236 from AMS_IBA_PS_CNDCLSES_TL B
237 where B.CND_CLAUSE_ID = T.CND_CLAUSE_ID
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.CND_CLAUSE_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.CND_CLAUSE_ID,
244 SUBT.LANGUAGE
245 from AMS_IBA_PS_CNDCLSES_TL SUBB, AMS_IBA_PS_CNDCLSES_TL SUBT
246 where SUBB.CND_CLAUSE_ID = SUBT.CND_CLAUSE_ID
247 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248 and (SUBB.CND_CLAUSE_NAME <> SUBT.CND_CLAUSE_NAME
249 or SUBB.CND_CLAUSE_DESCRIPTION <> SUBT.CND_CLAUSE_DESCRIPTION
250 or (SUBB.CND_CLAUSE_DESCRIPTION is null and SUBT.CND_CLAUSE_DESCRIPTION is not null)
251 or (SUBB.CND_CLAUSE_DESCRIPTION is not null and SUBT.CND_CLAUSE_DESCRIPTION is null)
252 ));
253
254 insert into AMS_IBA_PS_CNDCLSES_TL (
255 LAST_UPDATE_DATE,
256 CREATION_DATE,
257 LAST_UPDATED_BY,
258 CREATED_BY,
259 CND_CLAUSE_ID,
260 CND_CLAUSE_NAME,
261 CND_CLAUSE_DESCRIPTION,
262 LAST_UPDATE_LOGIN,
263 OBJECT_VERSION_NUMBER,
264 LANGUAGE,
265 SOURCE_LANG
266 ) select
267 B.LAST_UPDATE_DATE,
268 B.CREATION_DATE,
269 B.LAST_UPDATED_BY,
270 B.CREATED_BY,
271 B.CND_CLAUSE_ID,
272 B.CND_CLAUSE_NAME,
273 B.CND_CLAUSE_DESCRIPTION,
274 B.LAST_UPDATE_LOGIN,
275 B.OBJECT_VERSION_NUMBER,
276 L.LANGUAGE_CODE,
277 B.SOURCE_LANG
278 from AMS_IBA_PS_CNDCLSES_TL B, FND_LANGUAGES L
279 where L.INSTALLED_FLAG in ('I', 'B')
280 and B.LANGUAGE = userenv('LANG')
281 and not exists
282 (select NULL
283 from AMS_IBA_PS_CNDCLSES_TL T
284 where T.CND_CLAUSE_ID = B.CND_CLAUSE_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286 end ADD_LANGUAGE;
287
288
289 ---------------------------------------------------
290 ---- MEDIA ----
291 ---------------------------------------------------
292
293 -- ========================================================
294 --
295 -- NAME
296 -- createLockBody
297 --
298 -- PURPOSE
299 --
300 -- NOTES
301 --
302 -- HISTORY
303 --
304 -- ========================================================
305 PROCEDURE Lock_Row(
306 p_created_by NUMBER,
307 p_creation_date DATE,
308 p_last_updated_by NUMBER,
309 p_last_update_date DATE,
310 p_last_update_login NUMBER,
311 p_object_version_number NUMBER,
312 p_cnd_clause_id NUMBER,
313 p_cnd_clause_datatype VARCHAR2,
314 p_cnd_clause_ref_code VARCHAR2,
315 p_cnd_comp_operator VARCHAR2,
316 p_cnd_default_value VARCHAR2)
317
318 IS
319 CURSOR C IS
320 SELECT *
321 FROM AMS_IBA_PS_CNDCLSES_B
322 WHERE CND_CLAUSE_ID = p_CND_CLAUSE_ID
323 FOR UPDATE of CND_CLAUSE_ID NOWAIT;
324 Recinfo C%ROWTYPE;
325 BEGIN
326 OPEN c;
327 FETCH c INTO Recinfo;
328 If (c%NOTFOUND) then
329 CLOSE c;
330 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
331 APP_EXCEPTION.RAISE_EXCEPTION;
332 END IF;
333 CLOSE C;
334 IF (
335 ( Recinfo.created_by = p_created_by)
336 AND ( ( Recinfo.creation_date = p_creation_date)
337 OR ( ( Recinfo.creation_date IS NULL )
338 AND ( p_creation_date IS NULL )))
339 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
340 OR ( ( Recinfo.last_updated_by IS NULL )
341 AND ( p_last_updated_by IS NULL )))
342 AND ( ( Recinfo.last_update_date = p_last_update_date)
343 OR ( ( Recinfo.last_update_date IS NULL )
344 AND ( p_last_update_date IS NULL )))
345 AND ( ( Recinfo.last_update_login = p_last_update_login)
346 OR ( ( Recinfo.last_update_login IS NULL )
347 AND ( p_last_update_login IS NULL )))
348 AND ( ( Recinfo.object_version_number = p_object_version_number)
349 OR ( ( Recinfo.object_version_number IS NULL )
350 AND ( p_object_version_number IS NULL )))
351 AND ( ( Recinfo.cnd_clause_id = p_cnd_clause_id)
352 OR ( ( Recinfo.cnd_clause_id IS NULL )
353 AND ( p_cnd_clause_id IS NULL )))
354 AND ( ( Recinfo.cnd_clause_datatype = p_cnd_clause_datatype)
355 OR ( ( Recinfo.cnd_clause_datatype IS NULL )
356 AND ( p_cnd_clause_datatype IS NULL )))
357 AND ( ( Recinfo.cnd_clause_ref_code = p_cnd_clause_ref_code)
358 OR ( ( Recinfo.cnd_clause_ref_code IS NULL )
359 AND ( p_cnd_clause_ref_code IS NULL )))
360 AND ( ( Recinfo.cnd_comp_operator = p_cnd_comp_operator)
361 OR ( ( Recinfo.cnd_comp_operator IS NULL )
362 AND ( p_cnd_comp_operator IS NULL )))
363 AND ( ( Recinfo.cnd_default_value = p_cnd_default_value)
364 OR ( ( Recinfo.cnd_default_value IS NULL )
365 AND ( p_cnd_default_value IS NULL )))
366 ) THEN
367 RETURN;
368 ELSE
369 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
370 APP_EXCEPTION.RAISE_EXCEPTION;
371 END IF;
372 END Lock_Row;
373
374 END AMS_IBA_PS_CNDCLSES_B_PKG;