[Home] [Help]
PACKAGE BODY: APPS.FA_RATES_PKG
Source
1 PACKAGE BODY FA_RATES_PKG as
2 /* $Header: faxiratb.pls 120.6 2005/07/28 00:19:56 tkawamur 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 default null) 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
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'
58 ,p_log_level_rec => p_log_level_rec);
59 raise;
60
61 end INSERT_ROW;
62
63 procedure LOCK_ROW (
64 X_METHOD_ID in NUMBER,
65 X_YEAR in NUMBER,
66 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
67 X_RATE in NUMBER,
68 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
69 CURSOR C is
70 SELECT method_id,
71 year,
72 period_placed_in_service,
73 rate
74 FROM fa_rates
75 where method_id = X_Method_Id
76 and year = X_Year
77 and period_placed_in_service = X_Period_Placed_In_Service
78 FOR UPDATE of method_id, year, period_placed_in_service NOWAIT;
79 Recinfo C%ROWTYPE;
80
81 begin
82
83 OPEN C;
84 FETCH C INTO Recinfo;
85 if (C%NOTFOUND) then
86 CLOSE C;
87 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
88 APP_EXCEPTION.Raise_Exception;
89 end if;
90 CLOSE C;
91 if (
92 (Recinfo.Method_ID = X_Method_ID)
93 AND (Recinfo.Year = X_Year)
94 AND (Recinfo.Period_Placed_In_Service = X_Period_Placed_In_Service)
95 AND (Recinfo.Rate = X_Rate)) 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 default null) is
113
114 begin
115
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'
132 ,p_log_level_rec => p_log_level_rec);
133 raise;
134
135 end UPDATE_ROW;
136
137 procedure DELETE_ROW (
138 X_METHOD_ID in NUMBER,
139 X_YEAR in NUMBER,
140 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
141 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
142
143 begin
144
145 DELETE FROM fa_rates
146 where method_id = X_Method_Id
147 and year = X_Year
148 and period_placed_in_service = X_Period_Placed_In_Service;
149
150 if (SQL%NOTFOUND) then
151 Raise NO_DATA_FOUND;
152 end if;
153
154 exception
155 when others then
156 fa_srvr_msg.add_sql_error(
157 calling_fn => 'fa_rates_pkg.delete_row'
158 ,p_log_level_rec => p_log_level_rec);
159 raise;
160
161 end DELETE_ROW;
162
163 procedure LOAD_ROW (
164 X_CUSTOM_MODE in VARCHAR2,
165 X_METHOD_ID in NUMBER,
166 X_DB_LAST_UPDATED_BY NUMBER,
167 X_DB_LAST_UPDATE_DATE DATE,
168 X_OWNER in VARCHAR2,
169 X_LAST_UPDATE_DATE in DATE,
170 X_YEAR in NUMBER,
171 X_PERIOD_PLACED_IN_SERVICE in NUMBER,
172 X_RATE in NUMBER,
173 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
174
175 h_record_exists number(15);
176
177 user_id number;
178 row_id varchar2(64);
179
180 begin
181
182 user_id := fnd_load_util.owner_id (X_Owner);
183
184 select count(*)
185 into h_record_exists
186 from fa_rates
187 where method_id = X_Method_ID
188 and year = X_Year
189 and period_placed_in_service = X_Period_Placed_In_Service;
190
191 if (h_record_exists > 0) then
192
193 if (fnd_load_util.upload_test(user_id, x_last_update_date,
194 x_db_last_updated_by, x_db_last_update_date,
195 X_CUSTOM_MODE)) then
196
197 fa_rates_pkg.update_row (
198 X_Method_ID => X_Method_ID,
199 X_Year => X_Year,
200 X_Period_Placed_In_Service => X_Period_Placed_In_Service,
201 X_Rate => X_Rate,
202 X_Last_Update_Date => sysdate,
203 X_Last_Updated_By => user_id,
204 X_Last_Update_Login => 0
205 ,p_log_level_rec => p_log_level_rec);
206 end if;
207 else
208 fa_rates_pkg.insert_row (
209 X_Rowid => row_id,
210 X_Method_Id => X_Method_Id,
211 X_Year => X_Year,
212 X_Period_Placed_In_Service => X_Period_Placed_In_Service,
213 X_Rate => X_Rate,
214 X_Creation_Date => sysdate,
215 X_Created_By => user_id,
216 X_Last_Update_Date => sysdate,
217 X_Last_Updated_By => user_id,
218 X_Last_Update_Login => 0
219 ,p_log_level_rec => p_log_level_rec);
220 end if;
221
222 exception
223 when others then
224
225 fa_srvr_msg.add_sql_error(
226 calling_fn => 'fa_rates_pkg.load_row'
227 ,p_log_level_rec => p_log_level_rec);
228
229 FA_STANDARD_PKG.RAISE_ERROR(
230 CALLED_FN => 'fa_rates_pkg.load_row',
231 CALLING_FN => 'upload fa_rates'
232 ,p_log_level_rec => p_log_level_rec);
233
234 end LOAD_ROW;
235
236 procedure LOAD_SEED_ROW (
237 x_upload_mode IN VARCHAR2,
238 x_custom_mode IN VARCHAR2,
239 x_method_code IN VARCHAR2,
240 x_life_in_months IN NUMBER,
241 x_owner IN VARCHAR2,
242 x_last_update_date IN DATE,
243 x_year IN NUMBER,
244 x_period_placed_in_service IN NUMBER,
245 x_rate IN NUMBER) IS
246
247
248 methods_err exception;
249 h_method_id number(15);
250 h_last_update_date date;
251 h_last_updated_by number;
252
253 h_depr_last_year_flag boolean;
254 h_rate_source_rule varchar2(10);
255 h_deprn_basis_rule varchar2(4);
256 h_excl_sal_val_flag boolean;
257
258 BEGIN
259
260 if (x_upload_mode = 'NLS') then
261 null;
262 else
263 if not fa_cache_pkg.fazccmt (
264 X_method => x_method_code,
265 X_life => x_life_in_months) then
266 raise methods_err;
267 end if;
268
269 h_method_id := fa_cache_pkg.fazccmt_record.method_id;
270 h_last_update_date := fa_cache_pkg.fazccmt_record.last_update_date;
271 h_last_updated_by := fa_cache_pkg.fazccmt_record.last_updated_by;
272 h_rate_source_rule := fa_cache_pkg.fazccmt_record.rate_source_rule;
273 h_deprn_basis_rule := fa_cache_pkg.fazccmt_record.deprn_basis_rule;
274
275 if fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag = 'YES' then
276 h_excl_sal_val_flag := TRUE;
277 else
278 h_excl_sal_val_flag := FALSE;
279 end if;
280
281 if fa_cache_pkg.fazccmt_record.depreciate_lastyear_flag = 'YES' then
282 h_depr_last_year_flag := TRUE;
283 else
284 h_depr_last_year_flag := FALSE;
285 end if;
286
287 fa_rates_pkg.LOAD_ROW (
288 x_custom_mode => x_custom_mode,
289 x_method_id => h_method_id,
290 x_db_last_update_date => h_last_update_date,
291 x_db_last_updated_by => h_last_updated_by,
292 x_owner => x_owner,
293 x_last_update_date => x_last_update_date,
294 x_year => x_year,
295 x_period_placed_in_service => x_period_placed_in_service,
296 x_rate => x_rate);
297 end if;
298
299 EXCEPTION
300 WHEN methods_err THEN
301 fa_srvr_msg.add_sql_error(
302 calling_fn => 'update fa_flat_rates');
303
304 fa_standard_pkg.raise_error(
305 called_fn => 'farat.lct',
306 calling_fn => 'fa_rates_pkg.load_seed_row');
307
308 END LOAD_SEED_ROW;
309
310 END FA_RATES_PKG;