[Home] [Help]
PACKAGE BODY: APPS.FA_BONUS_RATES_PKG
Source
1 PACKAGE BODY FA_BONUS_RATES_PKG as
2 /* $Header: faxiborb.pls 120.6 2005/07/28 00:20:51 tkawamur ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_BONUS_RULE in VARCHAR2,
7 X_START_YEAR in NUMBER,
8 X_END_YEAR in NUMBER,
9 X_BONUS_RATE in NUMBER,
10 X_ATTRIBUTE1 in VARCHAR2,
11 X_ATTRIBUTE2 in VARCHAR2,
12 X_ATTRIBUTE3 in VARCHAR2,
13 X_ATTRIBUTE4 in VARCHAR2,
14 X_ATTRIBUTE5 in VARCHAR2,
15 X_ATTRIBUTE6 in VARCHAR2,
16 X_ATTRIBUTE7 in VARCHAR2,
17 X_ATTRIBUTE8 in VARCHAR2,
18 X_ATTRIBUTE9 in VARCHAR2,
19 X_ATTRIBUTE10 in VARCHAR2,
20 X_ATTRIBUTE11 in VARCHAR2,
21 X_ATTRIBUTE12 in VARCHAR2,
22 X_ATTRIBUTE13 in VARCHAR2,
23 X_ATTRIBUTE14 in VARCHAR2,
24 X_ATTRIBUTE15 in VARCHAR2,
25 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER,
31 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
32
33 CURSOR C is SELECT rowid FROM fa_bonus_rates
34 where bonus_rule = X_Bonus_Rule
35 and start_year = X_Start_Year;
36
37 begin
38
39 INSERT INTO fa_bonus_rates (
40 bonus_rule,
41 start_year,
42 end_year,
43 bonus_rate,
44 attribute1,
45 attribute2,
46 attribute3,
47 attribute4,
48 attribute5,
49 attribute6,
50 attribute7,
51 attribute8,
52 attribute9,
53 attribute10,
54 attribute11,
55 attribute12,
56 attribute13,
57 attribute14,
58 attribute15,
59 attribute_category_code,
60 creation_date,
61 created_by,
62 last_update_date,
63 last_updated_by,
64 last_update_login
65 ) VALUES (
66 X_Bonus_Rule,
67 X_Start_Year,
68 X_End_Year,
69 X_Bonus_Rate,
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_Code,
86 X_Creation_Date,
87 X_Created_By,
88 X_Last_Update_Date,
89 X_Last_Updated_By,
90 X_Last_Update_Login
91 );
92
93 OPEN C;
94 FETCH C INTO X_Rowid;
95 if (C%NOTFOUND) then
96 CLOSE C;
97 Raise NO_DATA_FOUND;
98 end if;
99 CLOSE C;
100
101 exception
102 when others then
103 fa_srvr_msg.add_sql_error(
104 calling_fn => 'fa_bonus_rates_pkg.insert_row'
105 ,p_log_level_rec => p_log_level_rec);
106 raise;
107
108 end INSERT_ROW;
109
110 procedure LOCK_ROW (
111 X_BONUS_RULE in VARCHAR2,
112 X_START_YEAR in NUMBER,
113 X_END_YEAR in NUMBER,
114 X_BONUS_RATE in NUMBER,
115 X_ATTRIBUTE1 in VARCHAR2,
116 X_ATTRIBUTE2 in VARCHAR2,
117 X_ATTRIBUTE3 in VARCHAR2,
118 X_ATTRIBUTE4 in VARCHAR2,
119 X_ATTRIBUTE5 in VARCHAR2,
120 X_ATTRIBUTE6 in VARCHAR2,
121 X_ATTRIBUTE7 in VARCHAR2,
122 X_ATTRIBUTE8 in VARCHAR2,
123 X_ATTRIBUTE9 in VARCHAR2,
124 X_ATTRIBUTE10 in VARCHAR2,
125 X_ATTRIBUTE11 in VARCHAR2,
126 X_ATTRIBUTE12 in VARCHAR2,
127 X_ATTRIBUTE13 in VARCHAR2,
128 X_ATTRIBUTE14 in VARCHAR2,
129 X_ATTRIBUTE15 in VARCHAR2,
130 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
131 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
132 CURSOR C is
133 SELECT bonus_rule,
134 start_year,
135 end_year,
136 bonus_rate,
137 attribute1,
138 attribute2,
139 attribute3,
140 attribute4,
141 attribute5,
142 attribute6,
143 attribute7,
144 attribute8,
145 attribute9,
146 attribute10,
147 attribute11,
148 attribute12,
149 attribute13,
150 attribute14,
151 attribute15,
152 attribute_category_code
153 FROM fa_bonus_rates
154 where bonus_rule = X_Bonus_Rule
155 and start_year = X_Start_Year
156 FOR UPDATE of bonus_rule, start_year NOWAIT;
157 Recinfo C%ROWTYPE;
158
159 begin
160
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 (
170 (Recinfo.Bonus_Rule = X_Bonus_Rule)
171 AND (Recinfo.Start_Year = X_Start_Year)
172 AND ((Recinfo.End_Year = X_End_Year)
173 OR ((Recinfo.End_Year is NULL)
174 AND (X_End_Year is NULL)))
175 AND (Recinfo.Bonus_Rate = X_Bonus_Rate)
176 AND ((Recinfo.Attribute1 = X_Attribute1)
177 OR ((Recinfo.Attribute1 IS NULL)
178 AND (X_Attribute1 IS NULL)))
179 AND ((Recinfo.Attribute2 = X_Attribute2)
180 OR ((Recinfo.Attribute2 IS NULL)
181 AND (X_Attribute2 IS NULL)))
182 AND ((Recinfo.Attribute3 = X_Attribute3)
183 OR ((Recinfo.Attribute3 IS NULL)
184 AND (X_Attribute3 IS NULL)))
185 AND ((Recinfo.Attribute4 = X_Attribute4)
186 OR ((Recinfo.Attribute4 IS NULL)
187 AND (X_Attribute4 IS NULL)))
188 AND ((Recinfo.Attribute5 = X_Attribute5)
189 OR ((Recinfo.Attribute5 IS NULL)
190 AND (X_Attribute5 IS NULL)))
191 AND ((Recinfo.Attribute6 = X_Attribute6)
192 OR ((Recinfo.Attribute6 IS NULL)
193 AND (X_Attribute6 IS NULL)))
194 AND ((Recinfo.Attribute7 = X_Attribute7)
195 OR ((Recinfo.Attribute7 IS NULL)
196 AND (X_Attribute7 IS NULL)))
197 AND ((Recinfo.Attribute8 = X_Attribute8)
198 OR ((Recinfo.Attribute8 IS NULL)
199 AND (X_Attribute8 IS NULL)))
200 AND ((Recinfo.Attribute9 = X_Attribute9)
201 OR ((Recinfo.Attribute9 IS NULL)
202 AND (X_Attribute9 IS NULL)))
203 AND ((Recinfo.Attribute10 = X_Attribute10)
204 OR ((Recinfo.Attribute10 IS NULL)
205 AND (X_Attribute10 IS NULL)))
206 AND ((Recinfo.Attribute11 = X_Attribute11)
207 OR ((Recinfo.Attribute11 IS NULL)
208 AND (X_Attribute11 IS NULL)))
209 AND ((Recinfo.Attribute12 = X_Attribute12)
210 OR ((Recinfo.Attribute12 IS NULL)
211 AND (X_Attribute12 IS NULL)))
212 AND ((Recinfo.Attribute13 = X_Attribute13)
213 OR ((Recinfo.Attribute13 IS NULL)
214 AND (X_Attribute13 IS NULL)))
215 AND ((Recinfo.Attribute14 = X_Attribute14)
216 OR ((Recinfo.Attribute14 IS NULL)
217 AND (X_Attribute14 IS NULL)))
218 AND ((Recinfo.Attribute15 = X_Attribute15)
219 OR ((Recinfo.Attribute15 IS NULL)
220 AND (X_Attribute15 IS NULL)))) then
221 return;
222 else
223 FND_MESSAGE.set_Name('FND', 'FORM_RECORD_CHANGED');
224 APP_EXCEPTION.Raise_Exception;
225 end if;
226
227 end LOCK_ROW;
228
229 procedure UPDATE_ROW (
230 X_BONUS_RULE in VARCHAR2,
231 X_START_YEAR in NUMBER,
232 X_END_YEAR in NUMBER,
233 X_BONUS_RATE in NUMBER,
234 X_ATTRIBUTE1 in VARCHAR2,
235 X_ATTRIBUTE2 in VARCHAR2,
236 X_ATTRIBUTE3 in VARCHAR2,
237 X_ATTRIBUTE4 in VARCHAR2,
238 X_ATTRIBUTE5 in VARCHAR2,
239 X_ATTRIBUTE6 in VARCHAR2,
240 X_ATTRIBUTE7 in VARCHAR2,
241 X_ATTRIBUTE8 in VARCHAR2,
242 X_ATTRIBUTE9 in VARCHAR2,
243 X_ATTRIBUTE10 in VARCHAR2,
244 X_ATTRIBUTE11 in VARCHAR2,
245 X_ATTRIBUTE12 in VARCHAR2,
246 X_ATTRIBUTE13 in VARCHAR2,
247 X_ATTRIBUTE14 in VARCHAR2,
248 X_ATTRIBUTE15 in VARCHAR2,
249 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
250 X_LAST_UPDATE_DATE in DATE,
251 X_LAST_UPDATED_BY in NUMBER,
252 X_LAST_UPDATE_LOGIN in NUMBER,
253 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
254
255 begin
256
257 UPDATE fa_bonus_rates
258 SET bonus_rule = X_Bonus_Rule,
259 start_year = X_Start_Year,
260 end_year = X_End_Year,
261 bonus_rate = X_Bonus_Rate,
262 attribute1 = X_Attribute1,
263 attribute2 = X_Attribute2,
264 attribute3 = X_Attribute3,
265 attribute4 = X_Attribute4,
266 attribute5 = X_Attribute5,
267 attribute6 = X_Attribute6,
268 attribute7 = X_Attribute7,
269 attribute8 = X_Attribute8,
270 attribute9 = X_Attribute9,
271 attribute10 = X_Attribute10,
272 attribute11 = X_Attribute11,
273 attribute12 = X_Attribute12,
274 attribute13 = X_Attribute13,
275 attribute14 = X_Attribute14,
276 attribute15 = X_Attribute15,
277 last_update_date = X_Last_Update_Date,
278 last_updated_by = X_Last_Updated_By,
279 last_update_login = X_Last_Update_Login
280 where bonus_rule = X_Bonus_Rule
281 and start_year = X_Start_Year;
282
283 if (SQL%NOTFOUND) then
284 Raise NO_DATA_FOUND;
285 end if;
286
287 exception
288 when others then
289 fa_srvr_msg.add_sql_error(
290 calling_fn => 'fa_bonus_rates_pkg.update_row'
291 ,p_log_level_rec => p_log_level_rec);
292 raise;
293
294 end UPDATE_ROW;
295
296 procedure DELETE_ROW (
297 X_BONUS_RULE in VARCHAR2,
298 X_START_YEAR in NUMBER,
299 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
300
301 begin
302
303 DELETE FROM fa_bonus_rates
304 where bonus_rule = X_Bonus_Rule
305 and start_year = X_Start_Year;
306
307 if (SQL%NOTFOUND) then
308 Raise NO_DATA_FOUND;
309 end if;
310
311 exception
312 when others then
313 fa_srvr_msg.add_sql_error(
314 calling_fn => 'fa_bonus_rates_pkg.delete_row'
315 ,p_log_level_rec => p_log_level_rec);
316 raise;
317
318 end DELETE_ROW;
319
320
321 procedure LOAD_ROW (
322 X_CUSTOM_MODE in VARCHAR2,
323 X_BONUS_RULE in VARCHAR2,
324 X_OWNER in VARCHAR2,
325 X_LAST_UPDATE_DATE in DATE,
326 X_START_YEAR in NUMBER,
327 X_END_YEAR in NUMBER,
328 X_BONUS_RATE in VARCHAR2,
329 X_ATTRIBUTE1 in VARCHAR2,
330 X_ATTRIBUTE2 in VARCHAR2,
331 X_ATTRIBUTE3 in VARCHAR2,
332 X_ATTRIBUTE4 in VARCHAR2,
333 X_ATTRIBUTE5 in VARCHAR2,
334 X_ATTRIBUTE6 in VARCHAR2,
335 X_ATTRIBUTE7 in VARCHAR2,
336 X_ATTRIBUTE8 in VARCHAR2,
337 X_ATTRIBUTE9 in VARCHAR2,
338 X_ATTRIBUTE10 in VARCHAR2,
339 X_ATTRIBUTE11 in VARCHAR2,
340 X_ATTRIBUTE12 in VARCHAR2,
341 X_ATTRIBUTE13 in VARCHAR2,
342 X_ATTRIBUTE14 in VARCHAR2,
343 X_ATTRIBUTE15 in VARCHAR2,
344 X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
345 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
346
347 h_record_exists number(15);
348
349 user_id number;
350 row_id varchar2(64);
351
352 db_last_updated_by number;
353 db_last_update_date date;
354
355 begin
356
357 user_id := fnd_load_util.owner_id (X_Owner);
358
359 select count(*)
360 into h_record_exists
361 from fa_bonus_rates
362 where bonus_rule = X_Bonus_Rule
363 and start_year = X_Start_Year;
364
365 if (h_record_exists > 0) then
366
367 select last_updated_by, last_update_date
368 into db_last_updated_by, db_last_update_date
369 from fa_bonus_rates
370 where bonus_rule = X_Bonus_Rule
371 and start_year = X_Start_Year;
372
373 if (fnd_load_util.upload_test(user_id, x_last_update_date,
374 db_last_updated_by, db_last_update_date,
375 X_CUSTOM_MODE)) then
376
377 fa_bonus_rates_pkg.update_row (
378 X_Bonus_Rule => X_Bonus_Rule,
379 X_Start_Year => X_Start_Year,
380 X_End_Year => X_End_Year,
381 X_Bonus_Rate => X_Bonus_Rate,
382 X_Attribute1 => X_Attribute1,
383 X_Attribute2 => X_Attribute2,
384 X_Attribute3 => X_Attribute3,
385 X_Attribute4 => X_Attribute4,
386 X_Attribute5 => X_Attribute5,
387 X_Attribute6 => X_Attribute6,
388 X_Attribute7 => X_Attribute7,
389 X_Attribute8 => X_Attribute8,
390 X_Attribute9 => X_Attribute9,
391 X_Attribute10 => X_Attribute10,
392 X_Attribute11 => X_Attribute11,
393 X_Attribute12 => X_Attribute12,
397 X_Attribute_Category_Code => X_Attribute_Category_Code,
394 X_Attribute13 => X_Attribute13,
395 X_Attribute14 => X_Attribute14,
396 X_Attribute15 => X_Attribute15,
398 X_Last_Update_Date => sysdate,
399 X_Last_Updated_By => user_id,
400 X_Last_Update_Login => 0
401 ,p_log_level_rec => p_log_level_rec);
402 end if ;
403 else
404
405 fa_bonus_rates_pkg.insert_row (
406 X_Rowid => row_id,
407 X_Bonus_Rule => X_Bonus_Rule,
408 X_Start_Year => X_Start_Year,
409 X_End_Year => X_End_Year,
410 X_Bonus_Rate => X_Bonus_Rate,
411 X_Attribute1 => X_Attribute1,
412 X_Attribute2 => X_Attribute2,
413 X_Attribute3 => X_Attribute3,
414 X_Attribute4 => X_Attribute4,
415 X_Attribute5 => X_Attribute5,
416 X_Attribute6 => X_Attribute6,
417 X_Attribute7 => X_Attribute7,
418 X_Attribute8 => X_Attribute8,
419 X_Attribute9 => X_Attribute9,
420 X_Attribute10 => X_Attribute10,
421 X_Attribute11 => X_Attribute11,
422 X_Attribute12 => X_Attribute12,
423 X_Attribute13 => X_Attribute13,
424 X_Attribute14 => X_Attribute14,
425 X_Attribute15 => X_Attribute15,
426 X_Attribute_Category_Code => X_Attribute_Category_Code,
427 X_Creation_Date => sysdate,
428 X_Created_By => user_id,
429 X_Last_Update_Date => sysdate,
430 X_Last_Updated_By => user_id,
431 X_Last_Update_Login => 0
432 ,p_log_level_rec => p_log_level_rec);
433 end if;
434
435 exception
436 when others then
437 FA_STANDARD_PKG.RAISE_ERROR(
438 CALLED_FN => 'fa_bonus_rates_pkg.load_row',
439 CALLING_FN => 'upload fa_bonus_rates'
440 ,p_log_level_rec => p_log_level_rec);
441
442 end LOAD_ROW;
443
444 procedure LOAD_SEED_ROW (
445 x_upload_mode IN VARCHAR2,
446 x_custom_mode IN VARCHAR2,
447 x_bonus_rule IN VARCHAR2,
448 x_owner IN VARCHAR2,
449 x_last_update_date IN DATE,
450 x_start_year IN NUMBER,
451 x_end_year IN NUMBER,
452 x_bonus_rate IN NUMBER,
453 x_attribute1 IN VARCHAR2,
454 x_attribute2 IN VARCHAR2,
455 x_attribute3 IN VARCHAR2,
456 x_attribute4 IN VARCHAR2,
457 x_attribute5 IN VARCHAR2,
458 x_attribute6 IN VARCHAR2,
459 x_attribute7 IN VARCHAR2,
460 x_attribute8 IN VARCHAR2,
461 x_attribute9 IN VARCHAR2,
462 x_attribute10 IN VARCHAR2,
463 x_attribute11 IN VARCHAR2,
464 x_attribute12 IN VARCHAR2,
465 x_attribute13 IN VARCHAR2,
466 x_attribute14 IN VARCHAR2,
467 x_attribute15 IN VARCHAR2,
468 x_attribute_category_code IN VARCHAR2) IS
469
470 BEGIN
471
472 if (x_upload_mode = 'NLS') then
473 null;
474 else
475 fa_bonus_rates_pkg.LOAD_ROW (
476 x_custom_mode => x_custom_mode,
477 x_bonus_rule => x_bonus_rule,
478 x_owner => x_owner,
479 x_last_update_date => x_last_update_date,
480 x_start_year => x_start_year,
481 x_end_year => x_end_year,
482 x_bonus_rate => x_bonus_rate,
483 x_attribute1 => x_attribute1,
484 x_attribute2 => x_attribute2,
485 x_attribute3 => x_attribute3,
486 x_attribute4 => x_attribute4,
487 x_attribute5 => x_attribute5,
488 x_attribute6 => x_attribute6,
489 x_attribute7 => x_attribute7,
490 x_attribute8 => x_attribute8,
491 x_attribute9 => x_attribute9,
492 x_attribute10 => x_attribute10,
493 x_attribute11 => x_attribute11,
494 x_attribute12 => x_attribute12,
495 x_attribute13 => x_attribute13,
496 x_attribute14 => x_attribute14,
497 x_attribute15 => x_attribute15,
498 x_attribute_category_code => x_attribute_category_code);
499
500 end if;
501
502 END LOAD_SEED_ROW;
503
504 END FA_BONUS_RATES_PKG;