1 PACKAGE BODY HZ_CREDIT_USAGE_RULE_SETS_PKG AS
2 /* $Header: ARHCRUSB.pls 115.10 2003/08/18 17:56:27 rajkrish ship $ */
3
4
5 ---------------------------
6 -- PROCEDURES AND FUNCTIONS
7 ---------------------------
8
9 --========================================================================
10 -- PROCEDURE : Insert_row PUBLIC
11 -- COMMENT : Procedure inserts record into the table HZ_CREDIT_USAGE_RULE_SETS_B
12 -- and HZ_CREDIT_USAGE_RULE_SETS_TL
13 --========================================================================
14 procedure INSERT_ROW (
15 X_ROWID in out NOCOPY VARCHAR2,
16 X_CREDIT_USAGE_RULE_SET_ID in NUMBER,
17 X_ATTRIBUTE_CATEGORY in VARCHAR2,
18 X_ATTRIBUTE1 in VARCHAR2,
19 X_ATTRIBUTE2 in VARCHAR2,
20 X_ATTRIBUTE3 in VARCHAR2,
21 X_ATTRIBUTE4 in VARCHAR2,
22 X_ATTRIBUTE5 in VARCHAR2,
23 X_ATTRIBUTE6 in VARCHAR2,
24 X_ATTRIBUTE7 in VARCHAR2,
25 X_ATTRIBUTE8 in VARCHAR2,
26 X_ATTRIBUTE9 in VARCHAR2,
27 X_ATTRIBUTE10 in VARCHAR2,
28 X_ATTRIBUTE11 in VARCHAR2,
29 X_ATTRIBUTE12 in VARCHAR2,
30 X_ATTRIBUTE13 in VARCHAR2,
31 X_ATTRIBUTE14 in VARCHAR2,
32 X_ATTRIBUTE15 in VARCHAR2,
33 X_REQUEST_ID in NUMBER,
34 X_NAME in VARCHAR2,
35 X_CREATION_DATE in DATE,
36 X_CREATED_BY in NUMBER,
37 X_LAST_UPDATE_DATE in DATE,
38 X_LAST_UPDATED_BY in NUMBER,
39 X_LAST_UPDATE_LOGIN in NUMBER,
40 X_GLOBAL_EXPOSURE_FLAG IN VARCHAR2
41 ) is
42 cursor C is select ROWID from HZ_CREDIT_USAGE_RULE_SETS_B
43 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID
44 ;
45 begin
46 insert into HZ_CREDIT_USAGE_RULE_SETS_B (
47 ATTRIBUTE_CATEGORY,
48 ATTRIBUTE1,
49 ATTRIBUTE2,
50 ATTRIBUTE3,
51 ATTRIBUTE4,
52 ATTRIBUTE5,
53 ATTRIBUTE6,
54 ATTRIBUTE7,
55 ATTRIBUTE8,
56 ATTRIBUTE9,
57 ATTRIBUTE10,
58 ATTRIBUTE11,
59 ATTRIBUTE12,
60 ATTRIBUTE13,
61 ATTRIBUTE14,
62 ATTRIBUTE15,
63 REQUEST_ID,
64 CREDIT_USAGE_RULE_SET_ID,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN,
70 GLOBAL_EXPOSURE_FLAG
71 ) values (
72 X_ATTRIBUTE_CATEGORY,
73 X_ATTRIBUTE1,
74 X_ATTRIBUTE2,
75 X_ATTRIBUTE3,
76 X_ATTRIBUTE4,
77 X_ATTRIBUTE5,
78 X_ATTRIBUTE6,
79 X_ATTRIBUTE7,
80 X_ATTRIBUTE8,
81 X_ATTRIBUTE9,
82 X_ATTRIBUTE10,
83 X_ATTRIBUTE11,
84 X_ATTRIBUTE12,
85 X_ATTRIBUTE13,
86 X_ATTRIBUTE14,
87 X_ATTRIBUTE15,
88 X_REQUEST_ID,
89 X_CREDIT_USAGE_RULE_SET_ID,
90 X_CREATION_DATE,
91 X_CREATED_BY,
92 X_LAST_UPDATE_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_LOGIN,
95 X_GLOBAL_EXPOSURE_FLAG
96 );
97
98 insert into HZ_CREDIT_USAGE_RULE_SETS_TL (
99 CREDIT_USAGE_RULE_SET_ID,
100 NAME,
101 CREATION_DATE,
102 CREATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_LOGIN,
106 LANGUAGE,
107 SOURCE_LANG
108 ) select
109 X_CREDIT_USAGE_RULE_SET_ID,
110 X_NAME,
111 X_CREATION_DATE,
112 X_CREATED_BY,
113 X_LAST_UPDATE_DATE,
114 X_LAST_UPDATED_BY,
115 X_LAST_UPDATE_LOGIN,
116 L.LANGUAGE_CODE,
117 userenv('LANG')
118 from FND_LANGUAGES L
119 where L.INSTALLED_FLAG in ('I', 'B')
120 and not exists
121 (select NULL
122 from HZ_CREDIT_USAGE_RULE_SETS_TL T
123 where T.CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID
124 and T.LANGUAGE = L.LANGUAGE_CODE);
125
126 open c;
127 fetch c into X_ROWID;
128 if (c%notfound) then
129 close c;
130 raise no_data_found;
131 end if;
132 close c;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
137 THEN
138 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Insert_row');
139 END IF;
140 RAISE;
141
142 END Insert_row;
143
144 --========================================================================
145 -- PROCEDURE : Lock_row PUBLIC
146 -- PARAMETERS: p_credit_usage_rule_set_id credit_usage_rule_set_id
147 -- p_rule_set_name rule set name
148 -- p_last_update_date
149 -- COMMENT : Procedure locks record in the table HZ_CREDIT_USAGE_RULE_SETS_B
150 -- and HZ_CREDIT_USAGE_RULE_SETS_TL
151 --========================================================================
152 procedure LOCK_ROW (
153 X_CREDIT_USAGE_RULE_SET_ID in NUMBER,
154 X_ATTRIBUTE_CATEGORY in VARCHAR2,
155 X_ATTRIBUTE1 in VARCHAR2,
156 X_ATTRIBUTE2 in VARCHAR2,
157 X_ATTRIBUTE3 in VARCHAR2,
158 X_ATTRIBUTE4 in VARCHAR2,
159 X_ATTRIBUTE5 in VARCHAR2,
160 X_ATTRIBUTE6 in VARCHAR2,
161 X_ATTRIBUTE7 in VARCHAR2,
162 X_ATTRIBUTE8 in VARCHAR2,
163 X_ATTRIBUTE9 in VARCHAR2,
164 X_ATTRIBUTE10 in VARCHAR2,
165 X_ATTRIBUTE11 in VARCHAR2,
166 X_ATTRIBUTE12 in VARCHAR2,
167 X_ATTRIBUTE13 in VARCHAR2,
168 X_ATTRIBUTE14 in VARCHAR2,
169 X_ATTRIBUTE15 in VARCHAR2,
170 X_REQUEST_ID in NUMBER,
171 X_NAME in VARCHAR2
172 ) is
173 cursor c is select
174 ATTRIBUTE_CATEGORY,
175 ATTRIBUTE1,
176 ATTRIBUTE2,
177 ATTRIBUTE3,
178 ATTRIBUTE4,
179 ATTRIBUTE5,
180 ATTRIBUTE6,
181 ATTRIBUTE7,
182 ATTRIBUTE8,
183 ATTRIBUTE9,
184 ATTRIBUTE10,
185 ATTRIBUTE11,
186 ATTRIBUTE12,
187 ATTRIBUTE13,
188 ATTRIBUTE14,
189 ATTRIBUTE15,
190 REQUEST_ID
191 from HZ_CREDIT_USAGE_RULE_SETS_B
192 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID
193 for update of CREDIT_USAGE_RULE_SET_ID nowait;
194 recinfo c%rowtype;
195
196 cursor c1 is select
197 NAME,
198 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
199 from HZ_CREDIT_USAGE_RULE_SETS_TL
200 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID
201 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
202 for update of CREDIT_USAGE_RULE_SET_ID nowait;
203 begin
204 open c;
205 fetch c into recinfo;
206 if (c%notfound) then
207 close c;
208 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
209 app_exception.raise_exception;
210 end if;
211 close c;
212 if ( ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
213 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
214 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
215 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
216 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
217 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
218 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
219 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
220 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
221 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
222 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
223 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
224 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
225 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
226 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
227 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
228 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
229 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
230 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
231 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
232 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
233 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
234 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
235 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
236 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
237 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
238 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
239 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
240 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
241 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
242 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
243 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
244 AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
245 OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
246 ) then
247 null;
248 else
249 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
250 app_exception.raise_exception;
251 end if;
252
253 for tlinfo in c1 loop
254 if (tlinfo.BASELANG = 'Y') then
255 if ( (tlinfo.NAME = X_NAME)
256 ) then
257 null;
258 else
259 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
260 app_exception.raise_exception;
261 end if;
262 end if;
263 end loop;
264 return;
265 end LOCK_ROW;
266
267 --========================================================================
268 -- PROCEDURE : Update_row PUBLIC
269 -- COMMENT : Procedure updates record in the table HZ_CREDIT_USAGE_RULE_SETS_B
270 -- and HZ_CREDIT_USAGE_RULE_SETS_TL
271 --========================================================================
272 procedure UPDATE_ROW (
273 X_CREDIT_USAGE_RULE_SET_ID in NUMBER,
274 X_ATTRIBUTE_CATEGORY in VARCHAR2,
275 X_ATTRIBUTE1 in VARCHAR2,
276 X_ATTRIBUTE2 in VARCHAR2,
277 X_ATTRIBUTE3 in VARCHAR2,
278 X_ATTRIBUTE4 in VARCHAR2,
279 X_ATTRIBUTE5 in VARCHAR2,
280 X_ATTRIBUTE6 in VARCHAR2,
281 X_ATTRIBUTE7 in VARCHAR2,
282 X_ATTRIBUTE8 in VARCHAR2,
283 X_ATTRIBUTE9 in VARCHAR2,
284 X_ATTRIBUTE10 in VARCHAR2,
285 X_ATTRIBUTE11 in VARCHAR2,
286 X_ATTRIBUTE12 in VARCHAR2,
287 X_ATTRIBUTE13 in VARCHAR2,
288 X_ATTRIBUTE14 in VARCHAR2,
289 X_ATTRIBUTE15 in VARCHAR2,
290 X_REQUEST_ID in NUMBER,
291 X_NAME in VARCHAR2,
292 X_LAST_UPDATE_DATE in DATE,
293 X_LAST_UPDATED_BY in NUMBER,
294 X_LAST_UPDATE_LOGIN in NUMBER,
295 X_GLOBAL_EXPOSURE_FLAG IN VARCHAR2
296 ) is
297 begin
298 update HZ_CREDIT_USAGE_RULE_SETS_B set
299 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
300 ATTRIBUTE1 = X_ATTRIBUTE1,
301 ATTRIBUTE2 = X_ATTRIBUTE2,
302 ATTRIBUTE3 = X_ATTRIBUTE3,
303 ATTRIBUTE4 = X_ATTRIBUTE4,
304 ATTRIBUTE5 = X_ATTRIBUTE5,
305 ATTRIBUTE6 = X_ATTRIBUTE6,
306 ATTRIBUTE7 = X_ATTRIBUTE7,
307 ATTRIBUTE8 = X_ATTRIBUTE8,
308 ATTRIBUTE9 = X_ATTRIBUTE9,
309 ATTRIBUTE10 = X_ATTRIBUTE10,
310 ATTRIBUTE11 = X_ATTRIBUTE11,
311 ATTRIBUTE12 = X_ATTRIBUTE12,
312 ATTRIBUTE13 = X_ATTRIBUTE13,
313 ATTRIBUTE14 = X_ATTRIBUTE14,
314 ATTRIBUTE15 = X_ATTRIBUTE15,
315 REQUEST_ID = X_REQUEST_ID,
316 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
317 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
318 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
319 GLOBAL_EXPOSURE_FLAG = X_GLOBAL_EXPOSURE_FLAG
320 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID;
321
322 if (sql%notfound) then
323 raise no_data_found;
324 end if;
325
326 update HZ_CREDIT_USAGE_RULE_SETS_TL set
327 NAME = X_NAME,
328 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
329 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
330 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
331 SOURCE_LANG = userenv('LANG')
332 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID
333 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
334
335 if (sql%notfound) then
336 raise no_data_found;
337 end if;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
342 THEN
343 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Update_row');
344 END IF;
345 RAISE;
346
347 END Update_Row;
348
349 --========================================================================
350 -- PROCEDURE : Delete_row PUBLIC
351 -- COMMENT : Procedure deletes record from the
352 -- table HZ_CREDIT_USAGE_RULE_SETS_B
353 -- and HZ_CREDIT_USAGE_RULE_SETS_TL
354 --========================================================================
355 procedure DELETE_ROW (
356 X_CREDIT_USAGE_RULE_SET_ID in NUMBER
357 ) is
358 begin
359 delete from HZ_CREDIT_USAGE_RULE_SETS_TL
360 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID;
361
362 if (sql%notfound) then
363 raise no_data_found;
364 end if;
365
366 delete from HZ_CREDIT_USAGE_RULE_SETS_B
367 where CREDIT_USAGE_RULE_SET_ID = X_CREDIT_USAGE_RULE_SET_ID;
368
369 if (sql%notfound) then
370 raise no_data_found;
371 end if;
372
373 -- BUG 2056313
374 -- Delete the attached records from the HZ_CREDIT_USAGES
375 -- to prevent unwanted data that has no parent. This is important
376 -- to enable the validations in the Assign Usages Rules form to
377 -- function correctly.
378
379 DELETE from HZ_credit_usages
380 WHERE credit_usage_rule_set_id = X_CREDIT_USAGE_RULE_SET_ID ;
381
382 /*if (sql%notfound) then
383 raise no_data_found;
384 end if;
385 */
386
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
391 THEN
392 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
393 END IF;
394 RAISE;
395
396 END Delete_row;
397
398 --========================================================================
399 -- PROCEDURE : ADD_LANGUAGE PUBLIC
400 --
401 -- COMMENT : Procedure adds new language
402 --========================================================================
403 procedure ADD_LANGUAGE
404 is
405 begin
406 delete from HZ_CREDIT_USAGE_RULE_SETS_TL T
407 where not exists
408 (select NULL
409 from HZ_CREDIT_USAGE_RULE_SETS_B B
410 where B.CREDIT_USAGE_RULE_SET_ID = T.CREDIT_USAGE_RULE_SET_ID
411 );
412
413 update HZ_CREDIT_USAGE_RULE_SETS_TL T set (
414 NAME
415 ) = (select
416 B.NAME
417 from HZ_CREDIT_USAGE_RULE_SETS_TL B
418 where B.CREDIT_USAGE_RULE_SET_ID = T.CREDIT_USAGE_RULE_SET_ID
419 and B.LANGUAGE = T.SOURCE_LANG)
420 where (
421 T.CREDIT_USAGE_RULE_SET_ID,
422 T.LANGUAGE
423 ) in (select
424 SUBT.CREDIT_USAGE_RULE_SET_ID,
425 SUBT.LANGUAGE
426 from HZ_CREDIT_USAGE_RULE_SETS_TL SUBB, HZ_CREDIT_USAGE_RULE_SETS_TL SUBT
427 where SUBB.CREDIT_USAGE_RULE_SET_ID = SUBT.CREDIT_USAGE_RULE_SET_ID
428 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
429 and (SUBB.NAME <> SUBT.NAME
430 ));
431
432 insert into HZ_CREDIT_USAGE_RULE_SETS_TL (
433 CREDIT_USAGE_RULE_SET_ID,
434 NAME,
435 CREATION_DATE,
436 CREATED_BY,
437 LAST_UPDATE_DATE,
438 LAST_UPDATED_BY,
439 LAST_UPDATE_LOGIN,
440 LANGUAGE,
441 SOURCE_LANG
442 ) select /*+ ORDERED */
443 B.CREDIT_USAGE_RULE_SET_ID,
444 B.NAME,
445 B.CREATION_DATE,
446 B.CREATED_BY,
447 B.LAST_UPDATE_DATE,
448 B.LAST_UPDATED_BY,
449 B.LAST_UPDATE_LOGIN,
450 L.LANGUAGE_CODE,
451 B.SOURCE_LANG
452 from HZ_CREDIT_USAGE_RULE_SETS_TL B, FND_LANGUAGES L
453 where L.INSTALLED_FLAG in ('I', 'B')
454 and B.LANGUAGE = userenv('LANG')
455 and not exists
456 (select NULL
457 from HZ_CREDIT_USAGE_RULE_SETS_TL T
458 where T.CREDIT_USAGE_RULE_SET_ID = B.CREDIT_USAGE_RULE_SET_ID
459 and T.LANGUAGE = L.LANGUAGE_CODE);
460 end ADD_LANGUAGE;
461
462 END HZ_CREDIT_USAGE_RULE_SETS_PKG;
[Home] [Help]