[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_RULE_DETAILS_PKG
Source
1 PACKAGE BODY FA_DEPRN_RULE_DETAILS_PKG as
2 /* $Header: faxtdrdb.pls 120.7 2005/07/28 00:20:56 tkawamur noship $ */
3
4 PROCEDURE Insert_Row(
5 p_deprn_rule_detail_id IN OUT NOCOPY NUMBER,
6 p_deprn_basis_rule_id IN OUT NOCOPY NUMBER,
7 p_rule_name VARCHAR2,
8 p_rate_source_rule VARCHAR2,
9 p_deprn_basis_rule VARCHAR2,
10 p_asset_type VARCHAR2,
11 p_period_update_flag VARCHAR2,
12 p_subtract_ytd_flag VARCHAR2,
13 p_allow_reduction_rate_flag VARCHAR2,
14 p_use_eofy_reserve_flag VARCHAR2,
15 p_last_update_date DATE,
16 p_last_updated_by NUMBER,
17 p_created_by NUMBER,
18 p_creation_date DATE,
19 p_last_update_login NUMBER,
20 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
21 CURSOR C is SELECT FA_DEPRN_RULE_DETAILS_S.nextval from sys.dual;
22
23 CURSOR C_RULE is
24 select deprn_basis_rule_id
25 from FA_DEPRN_BASIS_RULES
26 where rule_name = p_rule_name;
27
28 CURSOR C_MAX_SEQ
29 is
30 select nvl(max(deprn_rule_detail_id),0)+1
31 from FA_DEPRN_RULE_DETAILS;
32
33 Begin
34
35 if (p_deprn_rule_detail_id is null) then
36 if p_created_by=1 then
37 OPEN C_MAX_SEQ;
38 FETCH C_MAX_SEQ INTO p_deprn_rule_detail_id;
39 CLOSE C_MAX_SEQ;
40 else
41 OPEN C;
42 FETCH C INTO p_deprn_rule_detail_id;
43 CLOSE C;
44 end if;
45 end if;
46
47 if (p_deprn_basis_rule_id is null) then
48 OPEN C_RULE;
49 FETCH C_RULE INTO p_deprn_basis_rule_id;
50 CLOSE C_RULE;
51 end if;
52
53 Insert into fa_deprn_rule_details (
54 deprn_rule_detail_id,
55 deprn_basis_rule_id,
56 rule_name,
57 rate_source_rule,
58 deprn_basis_rule,
59 asset_type,
60 period_update_flag,
61 subtract_ytd_flag,
62 allow_reduction_rate_flag,
63 use_eofy_reserve_flag,
64 last_update_date,
65 last_updated_by,
66 created_by,
67 creation_date,
68 last_update_login
69 )
70 values (
71 p_deprn_rule_detail_id,
72 p_deprn_basis_rule_id,
73 p_rule_name,
74 p_rate_source_rule,
75 p_deprn_basis_rule,
76 p_asset_type,
77 p_period_update_flag,
78 p_subtract_ytd_flag,
79 p_allow_reduction_rate_flag,
80 p_use_eofy_reserve_flag,
81 p_last_update_date,
82 p_last_updated_by,
83 p_created_by,
84 p_creation_date,
85 p_last_update_login
86 );
87
88 exception
89 when others then
90
91 fa_srvr_msg.add_sql_error(
92 calling_fn => 'fa_deprn_rule_details_pkg.insert_row'
93 ,p_log_level_rec => p_log_level_rec);
94
95 END Insert_Row;
96
97 PROCEDURE Lock_Row (
98 p_deprn_rule_detail_id NUMBER,
99 p_deprn_basis_rule_id NUMBER,
100 p_rule_name VARCHAR2,
101 p_rate_source_rule VARCHAR2,
102 p_deprn_basis_rule VARCHAR2,
103 p_asset_type VARCHAR2,
104 p_period_update_flag VARCHAR2,
105 p_subtract_ytd_flag VARCHAR2,
106 p_allow_reduction_rate_flag VARCHAR2,
107 p_use_eofy_reserve_flag VARCHAR2,
108 p_last_update_date DATE,
109 p_last_updated_by NUMBER,
110 p_last_update_login NUMBER,
111 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
112 CURSOR C IS
113 Select deprn_rule_detail_id,
114 deprn_basis_rule_id,
115 rule_name,
116 rate_source_rule,
117 deprn_basis_rule,
118 asset_type,
119 period_update_flag,
120 subtract_ytd_flag,
121 allow_reduction_rate_flag,
122 use_eofy_reserve_flag,
123 last_update_date,
124 last_updated_by,
125 last_update_login
126 from FA_DEPRN_RULE_DETAILS
127 Where rule_name = p_rule_name
128 and rate_source_rule = p_rate_source_rule
129 and deprn_basis_rule = p_deprn_basis_rule
130 for update of deprn_basis_rule_id nowait;
131
132 Recinfo C%ROWTYPE;
133
134 BEGIN
135
136 OPEN C;
137 FETCH C INTO Recinfo;
138
139 if (C%NOTFOUND) then
140 CLOSE C;
141 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
142 APP_EXCEPTION.Raise_Exception;
143 end if;
144 CLOSE C;
145
146 if (
147 (Recinfo.deprn_rule_detail_id = p_deprn_rule_detail_id)
148 AND (Recinfo.deprn_basis_rule_id = p_deprn_basis_rule_id)
149 AND (Recinfo.rule_name = p_rule_name)
150 AND (Recinfo.rate_source_rule = p_rate_source_rule)
151 AND (Recinfo.deprn_basis_rule = p_deprn_basis_rule)
152 AND (Recinfo.asset_type = p_asset_type)
153 AND (Recinfo.period_update_flag = p_period_update_flag)
154 AND (Recinfo.subtract_ytd_flag = p_subtract_ytd_flag)
155 AND (Recinfo.allow_reduction_rate_flag
156 = p_allow_reduction_rate_flag)
157 AND (Recinfo.use_eofy_reserve_flag = p_use_eofy_reserve_flag)) then
158 return;
159 else
160 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
161 APP_EXCEPTION.Raise_Exception;
162 end if;
163 END Lock_Row;
164
165 PROCEDURE Update_Row(
166 p_deprn_rule_detail_id NUMBER,
167 p_deprn_basis_rule_id NUMBER,
168 p_rule_name VARCHAR2,
169 p_rate_source_rule VARCHAR2,
170 p_deprn_basis_rule VARCHAR2,
171 p_asset_type VARCHAR2,
172 p_period_update_flag VARCHAR2,
173 p_subtract_ytd_flag VARCHAR2,
174 p_allow_reduction_rate_flag VARCHAR2,
175 p_use_eofy_reserve_flag VARCHAR2,
176 p_last_update_date DATE,
177 p_last_updated_by NUMBER,
178 p_last_update_login NUMBER,
179 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
180
181 BEGIN
182
183 UPDATE FA_DEPRN_RULE_DETAILS
184 SET
185 asset_type = p_asset_type,
186 period_update_flag = p_period_update_flag,
187 subtract_ytd_flag = p_subtract_ytd_flag,
188 allow_reduction_rate_flag = p_allow_reduction_rate_flag,
189 use_eofy_reserve_flag = p_use_eofy_reserve_flag,
190 last_update_date = p_last_update_date,
191 last_updated_by = p_last_updated_by,
192 last_update_login = p_last_update_login
193 WHERE deprn_rule_detail_id = p_deprn_rule_detail_id;
194
195 if (SQL%NOTFOUND) then
196 Raise NO_DATA_FOUND;
197 end if;
198 exception
199 when others then
200
201 fa_srvr_msg.add_sql_error(
202 calling_fn => 'fa_deprn_rule_details_pkg.update_row'
203 ,p_log_level_rec => p_log_level_rec);
204
205 END Update_Row;
206
207 PROCEDURE Delete_Row(p_deprn_rule_detail_id NUMBER,
208 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
209 BEGIN
210
211 DELETE FROM fa_deprn_rule_details
212 WHERE deprn_rule_detail_id = p_deprn_rule_detail_id;
213
214 if (SQL%NOTFOUND) then
215 Raise NO_DATA_FOUND;
216 end if;
217 exception
218 when others then
219
220 fa_srvr_msg.add_sql_error(
221 calling_fn => 'fa_deprn_rule_details_pkg.delete_row'
222 ,p_log_level_rec => p_log_level_rec);
223
224 END DELETE_ROW;
225
226 PROCEDURE LOAD_ROW (
227 p_custom_mode IN VARCHAR2,
228 p_deprn_rule_detail_id IN NUMBER,
229 p_owner IN VARCHAR2,
230 p_last_update_date IN DATE,
231 p_deprn_basis_rule_id IN NUMBER,
232 p_rule_name IN VARCHAR2,
233 p_rate_source_rule IN VARCHAR2,
234 p_deprn_basis_rule IN VARCHAR2,
235 p_asset_type IN VARCHAR2,
236 p_period_update_flag IN VARCHAR2,
237 p_subtract_ytd_flag IN VARCHAR2,
238 p_allow_reduction_rate_flag IN VARCHAR2,
239 p_use_eofy_reserve_flag IN VARCHAR2,
240 p_log_level_rec IN FA_API_TYPES.log_level_rec_type
241 default null ) IS
242
243
244
245 h_record_exists number(15);
246 h_deprn_basis_rule_id number(15);
247 h_deprn_rule_detail_id number(15);
248 user_id number(15);
249
250 db_last_updated_by number;
251 db_last_update_date date;
252
253 BEGIN
254
255 user_id := fnd_load_util.owner_id (p_Owner);
256
257 select count(*)
258 into h_record_exists
259 from fa_deprn_rule_details
260 where rule_name = p_rule_name
261 and rate_source_rule = p_rate_source_rule
262 and deprn_basis_rule = p_deprn_basis_rule;
263
264 if (h_record_exists > 0) then
265
266 select last_updated_by, last_update_date
267 into db_last_updated_by, db_last_update_date
268 from fa_deprn_rule_details
269 where rule_name = p_rule_name
270 and rate_source_rule = p_rate_source_rule
271 and deprn_basis_rule = p_deprn_basis_rule;
272
273 if (fnd_load_util.upload_test(user_id, p_last_update_date,
274 db_last_updated_by, db_last_update_date,
275 P_CUSTOM_MODE)) then
276
277 fa_deprn_rule_details_pkg.update_row (
278 p_deprn_rule_detail_id => p_deprn_rule_detail_id,
279 p_deprn_basis_rule_id => p_deprn_basis_rule_id,
280 p_rule_name => p_rule_name,
281 p_rate_source_rule => p_rate_source_rule,
282 p_deprn_basis_rule => p_deprn_basis_rule,
283 p_asset_type => p_asset_type,
284 p_period_update_flag => p_period_update_flag,
285 p_subtract_ytd_flag => p_subtract_ytd_flag,
286 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
287 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
288 p_last_update_date => sysdate,
289 p_last_updated_by => user_id,
290 p_last_update_login => 0,
291 p_log_level_rec => p_log_level_rec);
292
293 end if;
294 else
295
296 h_deprn_rule_detail_id := p_deprn_rule_detail_id;
297 h_deprn_basis_rule_id := p_deprn_basis_rule_id;
298
299 fa_deprn_rule_details_pkg.insert_row (
300 p_deprn_rule_detail_id => h_deprn_rule_detail_id,
301 p_deprn_basis_rule_id => h_deprn_basis_rule_id,
302 p_rule_name => p_rule_name,
303 p_rate_source_rule => p_rate_source_rule,
304 p_deprn_basis_rule => p_deprn_basis_rule,
305 p_asset_type => p_asset_type,
306 p_period_update_flag => p_period_update_flag,
307 p_subtract_ytd_flag => p_subtract_ytd_flag,
308 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
309 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
310 p_last_update_date => sysdate,
311 p_last_updated_by => user_id,
312 p_created_by => user_id,
313 p_creation_date => sysdate,
314 p_last_update_login => 0,
315 p_log_level_rec => p_log_level_rec);
316 end if;
317
318 EXCEPTION
319 when others then
320
321 fa_srvr_msg.add_sql_error(
322 calling_fn => 'fa_deprn_rule_details_pkg.load_row'
323 ,p_log_level_rec => p_log_level_rec);
324
325 END LOAD_ROW;
326
327 PROCEDURE LOAD_SEED_ROW (
328 p_upload_mode IN VARCHAR2,
329 p_custom_mode IN VARCHAR2,
330 p_deprn_rule_detail_id IN NUMBER,
331 p_owner IN VARCHAR2,
332 p_last_update_date IN DATE,
333 p_deprn_basis_rule_id IN NUMBER,
334 p_rule_name IN VARCHAR2,
335 p_rate_source_rule IN VARCHAR2,
336 p_deprn_basis_rule IN VARCHAR2,
337 p_asset_type IN VARCHAR2,
338 p_period_update_flag IN VARCHAR2,
339 p_subtract_ytd_flag IN VARCHAR2,
340 p_allow_reduction_rate_flag IN VARCHAR2,
341 p_use_eofy_reserve_flag IN VARCHAR2) IS
342
343
344
345 BEGIN
346
347 if (p_upload_mode = 'NLS') then
348 null;
349 else
350 FA_DEPRN_RULE_DETAILS_PKG.LOAD_ROW (
351 p_custom_mode => p_custom_mode,
352 p_deprn_rule_detail_id => p_deprn_rule_detail_id,
353 p_owner => p_owner,
354 p_last_update_date => p_last_update_date,
355 p_deprn_basis_rule_id => p_deprn_basis_rule_id,
356 p_rule_name => p_rule_name,
357 p_rate_source_rule => p_rate_source_rule,
358 p_deprn_basis_rule => p_deprn_basis_rule,
359 p_asset_type => p_asset_type,
360 p_period_update_flag => p_period_update_flag,
361 p_subtract_ytd_flag => p_subtract_ytd_flag,
362 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
363 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag);
364
365 end if;
366
367 END LOAD_SEED_ROW;
368
369 END FA_DEPRN_RULE_DETAILS_PKG;