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