[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_BASIS_RULES_PKG
Source
1 PACKAGE BODY FA_DEPRN_BASIS_RULES_PKG as
2 /* $Header: faxtdbrb.pls 120.10 2005/08/16 21:12:27 glchen noship $ */
3
4 PROCEDURE Insert_Row( X_deprn_basis_rule_id IN OUT NOCOPY NUMBER,
5 X_rule_name VARCHAR2,
6 X_user_rule_name VARCHAR2,
7 X_last_update_date DATE,
8 X_last_updated_by NUMBER,
9 X_created_by NUMBER,
10 X_creation_date DATE,
11 X_last_update_login NUMBER,
12 X_rate_source VARCHAR2,
13 X_calculation_basis VARCHAR2,
14 X_enabled_flag VARCHAR2,
15 X_program_name VARCHAR2,
16 X_description VARCHAR2,
17 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
18 CURSOR C is SELECT FA_DEPRN_BASIS_RULES_S.nextval from sys.dual;
19
20 CURSOR C_MAX_SEQ
21 is
22 select nvl(max(deprn_basis_rule_id),0)+1
23 from FA_DEPRN_BASIS_RULES;
24
25 Begin
26
27 if (X_deprn_basis_rule_id is null) then
28 if X_created_by=1 then
29 OPEN C_MAX_SEQ;
30 FETCH C_MAX_SEQ INTO X_deprn_basis_rule_id;
31 CLOSE C_MAX_SEQ;
32 else
33 OPEN C;
34 FETCH C INTO X_deprn_basis_rule_id;
35 CLOSE C;
36 end if;
37 end if;
38
39
40 Insert into fa_deprn_basis_rules (
41 deprn_basis_rule_id,
42 rule_name,
43 user_rule_name,
44 last_update_date,
45 last_updated_by,
46 created_by,
47 creation_date,
48 last_update_login,
49 rate_source,
50 deprn_basis,
51 enabled_flag,
52 program_name,
53 description
54 )
55 values (
56 X_deprn_basis_rule_id,
57 X_rule_name,
58 X_user_rule_name,
59 X_last_update_date,
60 X_last_updated_by,
61 X_created_by,
62 X_creation_date,
63 X_last_update_login,
64 X_rate_source,
65 X_calculation_basis,
66 X_enabled_flag,
67 X_program_name,
68 X_description
69 );
70
71 exception
72 when others then
73
74 fa_srvr_msg.add_sql_error(
75 calling_fn => 'fa_deprn_basis_rules_pkg.insert_row'
76 ,p_log_level_rec => p_log_level_rec);
77
78 END Insert_Row;
79
80 PROCEDURE Lock_Row ( X_deprn_basis_rule_id NUMBER,
81 X_rule_name VARCHAR2,
82 X_user_rule_name VARCHAR2,
83 X_last_update_date DATE,
84 X_last_updated_by NUMBER,
85 X_last_update_login NUMBER,
86 X_rate_source VARCHAR2,
87 X_calculation_basis VARCHAR2,
88 X_enabled_flag VARCHAR2,
89 X_program_name VARCHAR2,
90 X_description VARCHAR2,
91 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
92 CURSOR C IS
93 Select deprn_basis_rule_id,
94 rule_name,
95 user_rule_name,
96 last_update_date,
97 last_updated_by,
98 last_update_login,
99 rate_source,
100 deprn_basis,
101 enabled_flag,
102 program_name,
103 description
104 from FA_DEPRN_BASIS_RULES
105 Where DEPRN_BASIS_RULE_ID = x_deprn_basis_rule_id
106 for update of deprn_basis_rule_id nowait;
107
108 Recinfo C%ROWTYPE;
109
110 BEGIN
111
112 OPEN C;
113 FETCH C INTO Recinfo;
114
115 if (C%NOTFOUND) then
116 CLOSE C;
117 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
118 APP_EXCEPTION.Raise_Exception;
119 end if;
120 CLOSE C;
121
122 if (
123 (Recinfo.deprn_basis_rule_id = X_deprn_basis_rule_id)
124 AND (Recinfo.rule_name = X_rule_name)
125 -- AND (Recinfo.user_rule_name = X_user_rule_name)
126 -- AND (Recinfo.rate_source = X_rate_source)
127 -- AND (Recinfo.deprn_basis = X_calculation_basis)
128 AND (Recinfo.enabled_flag = X_enabled_flag)
129 AND (Nvl(Recinfo.program_name,'NULL')
130 = Nvl(X_program_name,'NULL'))
131 AND (Recinfo.description
132 = X_description)) then
133 return;
134 else
135 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
136 APP_EXCEPTION.Raise_Exception;
137 end if;
138 END Lock_Row;
139
140 PROCEDURE Update_Row( X_deprn_basis_rule_id NUMBER,
141 X_rule_name VARCHAR2,
142 X_user_rule_name VARCHAR2,
143 X_last_update_date DATE,
144 X_last_updated_by NUMBER,
145 X_last_update_login NUMBER,
146 X_rate_source VARCHAR2,
147 X_calculation_basis VARCHAR2,
148 X_enabled_flag VARCHAR2,
149 X_program_name VARCHAR2,
150 X_description VARCHAR2,
151 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
152
153 BEGIN
154
155 UPDATE FA_DEPRN_BASIS_RULES
156 SET
157 rule_name = X_rule_name,
158 user_rule_name = X_user_rule_name,
159 last_update_date = X_last_update_date,
160 last_updated_by = X_last_updated_by,
161 last_update_login = X_last_update_login,
162 rate_source = X_rate_source,
163 deprn_basis = X_calculation_basis,
164 enabled_flag = X_enabled_flag,
165 program_name = X_program_name,
166 description = X_description
167 WHERE deprn_basis_rule_id = X_deprn_basis_rule_id;
168
169 if (SQL%NOTFOUND) then
170 Raise NO_DATA_FOUND;
171 end if;
172 exception
173 when others then
174
175 fa_srvr_msg.add_sql_error(
176 calling_fn => 'fa_deprn_basis_rules_pkg.update_row'
177 ,p_log_level_rec => p_log_level_rec);
178
179 END Update_Row;
180
181 PROCEDURE Delete_Row(X_deprn_basis_rule_id NUMBER,
182 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
183
184 h_count number;
185
186 BEGIN
187 DELETE FROM fa_deprn_basis_rules
188 WHERE deprn_basis_rule_id = x_deprn_basis_rule_id;
189
190 if (SQL%NOTFOUND) then
191 Raise NO_DATA_FOUND;
192 end if;
193
194 select count(*)
195 into h_count
196 from FA_DEPRN_RULE_DETAILS
197 where deprn_basis_rule_id = x_deprn_basis_rule_id;
198
199 if h_count >0 then
200 DELETE from fa_deprn_rule_details
201 WHERE deprn_basis_rule_id = x_deprn_basis_rule_id;
202 end if;
203
204 exception
205 when others then
206
207 fa_srvr_msg.add_sql_error(
208 calling_fn => 'fa_deprn_basis_rules_pkg.delete_row'
209 ,p_log_level_rec => p_log_level_rec);
210
211 END DELETE_ROW;
212
213 PROCEDURE LOAD_ROW (X_custom_mode IN VARCHAR2,
214 X_deprn_basis_rule_id IN NUMBER,
215 X_owner IN VARCHAR2,
216 X_last_update_date IN DATE,
217 X_rule_name IN VARCHAR2,
218 X_user_rule_name IN VARCHAR2 DEFAULT NULL,
219 X_rate_source IN VARCHAR2 DEFAULT NULL,
220 X_calculation_basis IN VARCHAR2 DEFAULT NULL,
221 X_enabled_flag IN VARCHAR2,
222 X_program_name IN VARCHAR2,
223 X_description IN VARCHAR2 DEFAULT NULL,
224 p_log_level_rec IN FA_API_TYPES.log_level_rec_type
225 DEFAULT NULL) IS
226
227
228 h_record_exists number(15);
229 h_deprn_basis_rule_id number(15);
230 user_id number(15);
231
232 db_last_updated_by number;
233 db_last_update_date date;
234
235 begin
236
237 h_deprn_basis_rule_id := x_deprn_basis_rule_id;
238
239 user_id := fnd_load_util.owner_id (X_Owner);
240
241 select count(*)
242 into h_record_exists
243 from fa_deprn_basis_rules
244 where deprn_basis_rule_id = x_deprn_basis_rule_id;
245
246 if (h_record_exists > 0) then
247
248 select last_updated_by, last_update_date
249 into db_last_updated_by, db_last_update_date
250 from fa_deprn_basis_rules
251 where deprn_basis_rule_id = x_deprn_basis_rule_id;
252
253 if (fnd_load_util.upload_test(user_id, x_last_update_date,
254 db_last_updated_by, db_last_update_date,
255 X_CUSTOM_MODE)) then
256
257 fa_deprn_basis_rules_pkg.update_row (
258 X_deprn_basis_rule_id => h_deprn_basis_rule_id,
259 X_rule_name => X_rule_name,
260 X_user_rule_name => X_user_rule_name,
261 X_last_update_date => sysdate,
262 X_last_updated_by => user_id,
263 X_last_update_login => 0,
264 X_rate_source => X_rate_source,
265 X_calculation_basis => X_calculation_basis,
266 X_enabled_flag => X_enabled_flag,
267 X_program_name => X_program_name,
268 X_description => X_description,
269 p_log_level_rec => p_log_level_rec);
270
271 end if;
272 else
273
274 -- Bug#2234906
275 -- When Seed data is insereted, deprn_basis_rule_id on
276 -- FA_DEPRN_BASIS_RULES doesn't match to deprn_basis_rule_id on
277 -- FA_METHODS.
278 -- h_deprn_basis_rule_id := null;
279
280 fa_deprn_basis_rules_pkg.insert_row (
281 X_deprn_basis_rule_id => h_deprn_basis_rule_id,
282 X_rule_name => X_rule_name,
283 X_user_rule_name => X_user_rule_name,
284 X_last_update_date => sysdate,
285 X_last_updated_by => user_id,
286 X_created_by => user_id,
287 X_creation_date => sysdate,
288 X_last_update_login => 0,
289 X_rate_source => X_rate_source,
290 X_calculation_basis => X_calculation_basis,
291 X_enabled_flag => X_enabled_flag,
292 X_program_name => X_program_name,
293 X_description => X_description,
294 p_log_level_rec => p_log_level_rec);
295 end if;
296
297 exception
298 when others then
299
300 fa_srvr_msg.add_sql_error(
301 calling_fn => 'fa_deprn_basis_rules_pkg.load_row'
302 ,p_log_level_rec => p_log_level_rec);
303
304 end LOAD_ROW;
305
306 procedure TRANSLATE_ROW (
307 X_CUSTOM_MODE in VARCHAR2,
308 X_DEPRN_BASIS_RULE_ID in NUMBER,
309 X_OWNER in VARCHAR2,
310 X_LAST_UPDATE_DATE in DATE,
311 X_USER_RULE_NAME in VARCHAR2 DEFAULT NULL,
312 X_RULE_NAME in VARCHAR2 DEFAULT NULL,
313 X_DESCRIPTION in VARCHAR2 DEFAULT NULL,
314 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
315
316 user_id number;
317
318 db_last_updated_by number;
319 db_last_update_date date;
320
321 BEGIN
322
323 user_id := fnd_load_util.owner_id (X_Owner);
324
325 select last_updated_by, last_update_date
326 into db_last_updated_by, db_last_update_date
327 from fa_deprn_basis_rules
328 where deprn_basis_rule_id = x_deprn_basis_rule_id;
329
330 if (fnd_load_util.upload_test(user_id, x_last_update_date,
331 db_last_updated_by, db_last_update_date,
332 X_CUSTOM_MODE)) then
333
334 update FA_DEPRN_BASIS_RULES set
335 USER_RULE_NAME =nvl(X_USER_RULE_NAME, USER_RULE_NAME),
336 LAST_UPDATED_BY = user_id,
337 LAST_UPDATE_LOGIN = 0,
338 DESCRIPTION = X_DESCRIPTION
339 where DEPRN_BASIS_RULE_ID = nvl(X_DEPRN_BASIS_RULE_ID,DEPRN_BASIS_RULE_ID)
340 and RULE_NAME = nvl(X_RULE_NAME,RULE_NAME)
341 and userenv('LANG') =
342 (select language_code
343 from FND_LANGUAGES
344 where installed_flag = 'B');
345
346 end if;
347
348 EXCEPTION
349 when others then
350
351 fa_srvr_msg.add_sql_error(
352 calling_fn => 'fa_deprn_basis_rules_pkg.translate_row'
353 ,p_log_level_rec => p_log_level_rec);
354
355 FA_STANDARD_PKG.RAISE_ERROR(
356 CALLED_FN => 'fa_deprn_basis_rules_pkg.translate_row',
357 CALLING_FN => 'upload fa_deprn_basis_rules'
358 ,p_log_level_rec => p_log_level_rec);
359
360 END TRANSLATE_ROW;
361
362 procedure LOAD_SEED_ROW (
363 X_upload_mode IN VARCHAR2,
364 X_custom_mode IN VARCHAR2,
365 X_deprn_basis_rule_id IN NUMBER,
366 X_owner IN VARCHAR2,
367 X_last_update_date IN DATE,
368 X_rule_name IN VARCHAR2,
369 X_user_rule_name IN VARCHAR2,
370 X_description IN VARCHAR2,
371 X_enabled_flag IN VARCHAR2,
372 X_program_name IN VARCHAR2) IS
373
374
375 BEGIN
376
377 if (X_upload_mode = 'NLS') then
378 fa_deprn_basis_rules_pkg.TRANSLATE_ROW (
379 X_custom_mode => X_custom_mode,
380 X_deprn_basis_rule_id => X_deprn_basis_rule_id,
381 X_owner => X_owner,
382 X_last_update_date => X_last_update_date,
383 X_user_rule_name => X_user_rule_name,
384 X_description => X_description);
385 else
386 fa_deprn_basis_rules_pkg.LOAD_ROW (
387 X_custom_mode => X_custom_mode,
388 X_deprn_basis_rule_id => X_deprn_basis_rule_id,
389 X_owner => X_owner,
390 X_last_update_date => X_last_update_date,
391 X_rule_name => X_rule_name,
392 X_user_rule_name => X_user_rule_name,
393 X_description => X_description,
394 X_enabled_flag => X_enabled_flag,
395 X_program_name => X_program_name);
396 end if;
397
398 END LOAD_SEED_ROW;
399
400 END FA_DEPRN_BASIS_RULES_PKG;