[Home] [Help]
PACKAGE BODY: APPS.FA_RATES_PKG
Source
1 PACKAGE BODY FA_RATES_PKG as
2 /* $Header: faxiratb.pls 120.10 2011/03/11 02:42:57 saalampa ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_METHOD_ID in NUMBER,
7 X_YEAR in NUMBER,
8 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
9 X_RATE in NUMBER,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
16
17 CURSOR C is SELECT rowid FROM fa_rates
18 where method_id = X_Method_Id
19 and year = X_Year
20 and period_placed_in_service = X_Period_Placed_In_Service;
21
22 begin
23 ad_zd_seed.prepare('fa_rates');
24 INSERT INTO fa_rates (
25 method_id,
26 year,
27 period_placed_in_service,
28 rate,
29 creation_date,
30 created_by,
31 last_update_date,
32 last_updated_by,
33 last_update_login
34 ) VALUES (
35 X_Method_ID,
36 X_Year,
37 X_Period_Placed_In_Service,
38 X_Rate,
39 X_Creation_Date,
40 X_Created_By,
41 X_Last_Update_Date,
42 X_Last_Updated_By,
43 X_Last_Update_Login
44 );
45
46 OPEN C;
47 FETCH C INTO X_Rowid;
48 if (C%NOTFOUND) then
49 CLOSE C;
50 Raise NO_DATA_FOUND;
51 end if;
52 CLOSE C;
53
54 exception
55 when others then
56 fa_srvr_msg.add_sql_error(
57 calling_fn => 'fa_rates.insert_row', p_log_level_rec => p_log_level_rec);
58 raise;
59
60 end INSERT_ROW;
61
62 procedure LOCK_ROW (
63 X_METHOD_ID in NUMBER,
64 X_YEAR in NUMBER,
65 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
66 X_RATE in NUMBER
67 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
68 CURSOR C is
69 SELECT method_id,
70 year,
71 period_placed_in_service,
72 rate
73 FROM fa_rates
74 where method_id = X_Method_Id
75 and year = X_Year
76 and period_placed_in_service = X_Period_Placed_In_Service
77 FOR UPDATE of method_id, year, period_placed_in_service NOWAIT;
78 Recinfo C%ROWTYPE;
79
80 begin
81
82 OPEN C;
83 FETCH C INTO Recinfo;
84 if (C%NOTFOUND) then
85 CLOSE C;
86 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
87 APP_EXCEPTION.Raise_Exception;
88 end if;
89 CLOSE C;
90 if (
91 (Recinfo.Method_ID = X_Method_ID)
92 AND (Recinfo.Year = X_Year)
93 AND (Recinfo.Period_Placed_In_Service = X_Period_Placed_In_Service)
94 AND (Recinfo.Rate = X_Rate)
95 ) then
96 return;
97 else
98 FND_MESSAGE.set_Name('FND', 'FORM_RECORD_CHANGED');
99 APP_EXCEPTION.Raise_Exception;
100 end if;
101
102 end LOCK_ROW;
103
104 procedure UPDATE_ROW (
105 X_METHOD_ID in NUMBER,
106 X_YEAR in NUMBER,
107 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
108 X_RATE in NUMBER,
109 X_LAST_UPDATE_DATE in DATE,
110 X_LAST_UPDATED_BY in NUMBER,
111 X_LAST_UPDATE_LOGIN in NUMBER
112 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
113
114 begin
115 ad_zd_seed.prepare('fa_rates');
116 UPDATE fa_rates
117 SET method_id = X_Method_ID,
118 year = X_Year,
119 period_placed_in_service= X_Period_Placed_In_Service,
120 rate = X_Rate,
121 last_update_date = X_Last_Update_Date,
122 last_updated_by = X_Last_Updated_By,
123 last_update_login = X_Last_Update_Login
124 where method_id = X_Method_Id
125 and year = X_Year
126 and period_placed_in_service = X_Period_Placed_In_Service;
127
128 exception
129 when others then
130 fa_srvr_msg.add_sql_error(
131 calling_fn => 'fa_rates_pkg.update_row', p_log_level_rec => p_log_level_rec);
132 raise;
133
134 end UPDATE_ROW;
135
136 procedure DELETE_ROW (
137 X_METHOD_ID in NUMBER,
138 X_YEAR in NUMBER,
139 X_PERIOD_PLACED_IN_SERVICE in NUMBER
140 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
141
142 begin
143 ad_zd_seed.prepare('fa_rates');
144 DELETE FROM fa_rates
145 where method_id = X_Method_Id
146 and year = X_Year
147 and period_placed_in_service = X_Period_Placed_In_Service;
148
149 if (SQL%NOTFOUND) then
150 Raise NO_DATA_FOUND;
151 end if;
152
153 exception
154 when others then
155 fa_srvr_msg.add_sql_error(
156 calling_fn => 'fa_rates_pkg.delete_row', p_log_level_rec => p_log_level_rec);
157 raise;
158
159 end DELETE_ROW;
160
161 procedure LOAD_ROW (
162 X_METHOD_ID in NUMBER,
163 X_OWNER in VARCHAR2,
164 X_YEAR in NUMBER,
165 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
166 X_RATE in NUMBER
167 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) is
168
169 h_record_exists number(15);
170 h_rate number;
171
172 user_id number;
173 row_id varchar2(64);
174
175 begin
176
177 if (X_Owner = 'SEED') then
178 user_id := 1;
179 else
180 user_id := 0;
181 end if;
182
183 begin
184
185 select rate
186 into h_rate
187 from fa_rates
188 where method_id = X_Method_ID
189 and year = X_Year
190 and period_placed_in_service = X_Period_Placed_In_Service;
191
192 h_record_exists := 1;
193
194 exception
195 when no_data_found then
196 h_record_exists := 0;
197 end;
198
199 if (h_record_exists > 0) then
200 -- Update only if a change occurred.
201 if (h_rate <> X_Rate) then
202 fa_rates_pkg.update_row (
203 X_Method_ID => X_Method_ID,
204 X_Year => X_Year,
205 X_Period_Placed_In_Service => X_Period_Placed_In_Service,
206 X_Rate => X_Rate,
207 X_Last_Update_Date => sysdate,
208 X_Last_Updated_By => user_id,
209 X_Last_Update_Login => 0
210 , p_log_level_rec => p_log_level_rec);
211 end if;
212 else
213 fa_rates_pkg.insert_row (
214 X_Rowid => row_id,
215 X_Method_Id => X_Method_Id,
216 X_Year => X_Year,
217 X_Period_Placed_In_Service => X_Period_Placed_In_Service,
218 X_Rate => X_Rate,
219 X_Creation_Date => sysdate,
220 X_Created_By => user_id,
221 X_Last_Update_Date => sysdate,
222 X_Last_Updated_By => user_id,
223 X_Last_Update_Login => 0
224 , p_log_level_rec => p_log_level_rec);
225 end if;
226
227 exception
228 when others then
229
230 fa_srvr_msg.add_sql_error(
231 calling_fn => 'fa_rates_pkg.load_row', p_log_level_rec => p_log_level_rec);
232
233 FA_STANDARD_PKG.RAISE_ERROR(
234 CALLED_FN => 'fa_rates_pkg.load_row',
235 CALLING_FN => 'upload fa_rates',
236 p_log_level_rec => p_log_level_rec);
237
238 end LOAD_ROW;
239 /*Bug 8355119 overloading function for release specific signatures*/
240 procedure LOAD_ROW (
241 X_CUSTOM_MODE in VARCHAR2,
242 X_METHOD_ID in NUMBER,
243 X_DB_LAST_UPDATED_BY NUMBER,
244 X_DB_LAST_UPDATE_DATE DATE,
245 X_OWNER in VARCHAR2,
246 X_LAST_UPDATE_DATE in DATE,
247 X_YEAR in NUMBER,
248 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
249 X_RATE in NUMBER,
250 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
251
252 h_record_exists number(15);
253
254 user_id number;
255 row_id varchar2(64);
256
257 begin
258
259 user_id := fnd_load_util.owner_id (X_Owner);
260
261 select count(*)
262 into h_record_exists
263 from fa_rates
264 where method_id = X_Method_ID
265 and year = X_Year
266 and period_placed_in_service = X_Period_Placed_In_Service;
267
268 if (h_record_exists > 0) then
269
270 if (fnd_load_util.upload_test(user_id, x_last_update_date,
271 x_db_last_updated_by, x_db_last_update_date,
272 X_CUSTOM_MODE)) then
273
274 fa_rates_pkg.update_row (
275 X_Method_ID => X_Method_ID,
276 X_Year => X_Year,
277 X_Period_Placed_In_Service => X_Period_Placed_In_Service,
278 X_Rate => X_Rate,
279 X_Last_Update_Date => sysdate,
280 X_Last_Updated_By => user_id,
281 X_Last_Update_Login => 0
282 ,p_log_level_rec => p_log_level_rec);
283 end if;
284 else
285 fa_rates_pkg.insert_row (
286 X_Rowid => row_id,
287 X_Method_Id => X_Method_Id,
288 X_Year => X_Year,
289 X_Period_Placed_In_Service => X_Period_Placed_In_Service,
290 X_Rate => X_Rate,
291 X_Creation_Date => sysdate,
292 X_Created_By => user_id,
293 X_Last_Update_Date => sysdate,
294 X_Last_Updated_By => user_id,
295 X_Last_Update_Login => 0
296 ,p_log_level_rec => p_log_level_rec);
297 end if;
298
299 exception
300 when others then
301
302 fa_srvr_msg.add_sql_error(
303 calling_fn => 'fa_rates_pkg.load_row'
304 ,p_log_level_rec => p_log_level_rec);
305
306 FA_STANDARD_PKG.RAISE_ERROR(
307 CALLED_FN => 'fa_rates_pkg.load_row',
308 CALLING_FN => 'upload fa_rates'
309 ,p_log_level_rec => p_log_level_rec);
310
311 end LOAD_ROW;
312 /*bug 8355119 adding R12 specific funtion LOAD_SEED_ROW*/
313 procedure LOAD_SEED_ROW (
314 x_upload_mode IN VARCHAR2,
315 x_custom_mode IN VARCHAR2,
316 x_method_code IN VARCHAR2,
317 x_life_in_months IN NUMBER,
318 x_owner IN VARCHAR2,
319 x_last_update_date IN DATE,
320 x_year IN NUMBER,
321 x_period_placed_in_service IN NUMBER,
322 x_rate IN NUMBER) IS
323
324
325 methods_err exception;
326 h_method_id number(15);
327 h_last_update_date date;
328 h_last_updated_by number;
329
330 h_depr_last_year_flag boolean;
331 h_rate_source_rule varchar2(10);
332 h_deprn_basis_rule varchar2(4);
333 h_excl_sal_val_flag boolean;
334
335 BEGIN
336
337 if (x_upload_mode = 'NLS') then
338 null;
339 else
340 if not fa_cache_pkg.fazccmt (
341 X_method => x_method_code,
342 X_life => x_life_in_months) then
343 raise methods_err;
344 end if;
345
346 h_method_id := fa_cache_pkg.fazccmt_record.method_id;
347 h_last_update_date := fa_cache_pkg.fazccmt_record.last_update_date;
348 h_last_updated_by := fa_cache_pkg.fazccmt_record.last_updated_by;
349 h_rate_source_rule := fa_cache_pkg.fazccmt_record.rate_source_rule;
350 h_deprn_basis_rule := fa_cache_pkg.fazccmt_record.deprn_basis_rule;
351
352 if fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag = 'YES' then
353 h_excl_sal_val_flag := TRUE;
354 else
355 h_excl_sal_val_flag := FALSE;
356 end if;
357
358 if fa_cache_pkg.fazccmt_record.depreciate_lastyear_flag = 'YES' then
359 h_depr_last_year_flag := TRUE;
360 else
361 h_depr_last_year_flag := FALSE;
362 end if;
363
364 fa_rates_pkg.LOAD_ROW (
365 x_custom_mode => x_custom_mode,
366 x_method_id => h_method_id,
367 x_db_last_update_date => h_last_update_date,
368 x_db_last_updated_by => h_last_updated_by,
369 x_owner => x_owner,
370 x_last_update_date => x_last_update_date,
371 x_year => x_year,
372 x_period_placed_in_service => x_period_placed_in_service,
373 x_rate => x_rate);
374 end if;
375
376 EXCEPTION
377 WHEN methods_err THEN
378 fa_srvr_msg.add_sql_error(
379 calling_fn => 'update fa_flat_rates');
380
381 fa_standard_pkg.raise_error(
382 called_fn => 'farat.lct',
383 calling_fn => 'fa_rates_pkg.load_seed_row');
384
385 END LOAD_SEED_ROW;
386
387 END FA_RATES_PKG;