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