[Home] [Help]
PACKAGE BODY: APPS.FA_FORMULAS_PKG
Source
1 PACKAGE BODY FA_FORMULAS_PKG as
2 /* $Header: faxiforb.pls 120.9.12010000.1 2008/07/28 13:25:30 appldev 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 default null) 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'
65 ,p_log_level_rec => p_log_level_rec);
66 raise;
67
68 end INSERT_ROW;
69
70 procedure LOCK_ROW (
71 X_METHOD_ID in NUMBER,
72 X_FORMULA_ACTUAL in VARCHAR2,
73 X_FORMULA_DISPLAYED in VARCHAR2,
74 X_FORMULA_PARSED in VARCHAR2,
75 X_ORIGINAL_RATE IN NUMBER,
76 X_REVISED_RATE IN NUMBER,
77 X_GUARANTEE_RATE IN NUMBER,
78 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
79 CURSOR C is
80 SELECT method_id,
81 formula_actual,
82 formula_displayed,
83 formula_parsed,
84 original_rate,
85 revised_rate,
86 guarantee_rate
87 FROM fa_formulas
88 where method_id = X_Method_Id
89 FOR UPDATE of method_id NOWAIT;
90 Recinfo C%ROWTYPE;
91
92 begin
93
94 OPEN C;
95 FETCH C INTO Recinfo;
96 if (C%NOTFOUND) then
97 CLOSE C;
98 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
99 APP_EXCEPTION.Raise_Exception;
100 end if;
101 CLOSE C;
102 if (
103 (Recinfo.Method_ID = X_Method_ID)
104 AND (Recinfo.Formula_Actual = X_Formula_Actual)
105 AND (Recinfo.Formula_Displayed = X_Formula_Displayed)
106 AND (Recinfo.Formula_Parsed = X_Formula_Parsed)
107 AND (nvl(Recinfo.Original_Rate, -999) = nvl(X_Original_Rate,-999))
108 AND (nvl(Recinfo.Revised_Rate, -999) = nvl(X_Revised_Rate,-999))
109 AND (nvl(Recinfo.Guarantee_Rate, -999) = nvl(X_Guarantee_Rate,-999))
110 ) then
111 return;
112 else
113 FND_MESSAGE.set_Name('FND', 'FORM_RECORD_CHANGED');
114 APP_EXCEPTION.Raise_Exception;
115 end if;
116
117 end LOCK_ROW;
118
119 procedure UPDATE_ROW (
120 X_METHOD_ID in NUMBER,
121 X_FORMULA_ACTUAL in VARCHAR2,
122 X_FORMULA_DISPLAYED in VARCHAR2,
123 X_FORMULA_PARSED in VARCHAR2,
124 X_LAST_UPDATE_DATE in DATE,
125 X_LAST_UPDATED_BY in NUMBER,
126 X_LAST_UPDATE_LOGIN in NUMBER,
127 X_ORIGINAL_RATE IN NUMBER,
128 X_REVISED_RATE IN NUMBER,
129 X_GUARANTEE_RATE IN NUMBER,
130 p_log_level_rec in fa_api_types.log_level_rec_type default null) is
131
132 begin
133
134 UPDATE fa_formulas
135 SET method_id = X_Method_ID,
136 formula_actual = X_Formula_Actual,
137 formula_displayed = X_Formula_Displayed,
138 formula_parsed = X_Formula_Parsed,
139 last_update_date = X_Last_Update_Date,
140 last_updated_by = X_Last_Updated_By,
141 last_update_login = X_Last_Update_Login,
142 original_rate = X_Original_Rate,
143 revised_rate = X_Revised_Rate,
144 guarantee_rate = X_Guarantee_Rate
145 WHERE method_id = X_method_id;
146
147 exception
148 when others then
149 fa_srvr_msg.add_sql_error(
150 calling_fn => 'fa_formulas_pkg.update_row'
151 ,p_log_level_rec => p_log_level_rec);
152 raise;
153
154 end UPDATE_ROW;
155
156 procedure DELETE_ROW (
157 X_METHOD_ID in NUMBER
158 ,p_log_level_rec in fa_api_types.log_level_rec_type default null) is
159
160 begin
161
162 DELETE FROM fa_formulas
163 where method_id = X_Method_Id;
164
165 if (SQL%NOTFOUND) then
166 Raise NO_DATA_FOUND;
167 end if;
168
169 exception
170 when others then
171 fa_srvr_msg.add_sql_error(
172 calling_fn => 'fa_formulas_pkg.delete_row'
173 ,p_log_level_rec => p_log_level_rec);
174 raise;
175
176 end DELETE_ROW;
177
178 procedure LOAD_ROW (
179 X_CUSTOM_MODE in VARCHAR2,
180 X_METHOD_ID in NUMBER,
181 X_DB_LAST_UPDATED_BY NUMBER,
182 X_DB_LAST_UPDATE_DATE DATE,
183 X_OWNER in VARCHAR2,
184 X_LAST_UPDATE_DATE in DATE,
185 X_FORMULA_ACTUAL in VARCHAR2,
186 X_FORMULA_DISPLAYED in VARCHAR2,
187 X_FORMULA_PARSED in VARCHAR2,
188 X_ORIGINAL_RATE IN NUMBER DEFAULT NULL,
189 X_REVISED_RATE IN NUMBER DEFAULT NULL,
190 X_GUARANTEE_RATE IN NUMBER DEFAULT NULL,
191 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
192
193 h_record_exists number(15);
194
195 user_id number;
196 row_id varchar2(64);
197
198 begin
199
200 user_id := fnd_load_util.owner_id (X_Owner);
201
202 select count(*)
203 into h_record_exists
204 from fa_formulas
205 where method_id = X_Method_ID;
206
207 if (h_record_exists > 0) then
208 if (fnd_load_util.upload_test(user_id, x_last_update_date,
209 x_db_last_updated_by,x_db_last_update_date,
210 X_CUSTOM_MODE)) then
211
212 fa_formulas_pkg.update_row (
213 X_Method_ID => X_Method_ID,
214 X_Formula_Actual => X_Formula_Actual,
215 X_Formula_Displayed => X_Formula_Displayed,
216 X_Formula_Parsed => X_Formula_Parsed,
217 X_Last_Update_Date => sysdate,
218 X_Last_Updated_By => user_id,
219 X_Last_Update_Login => 0,
220 X_Original_Rate => X_Original_Rate,
221 X_Revised_Rate => X_Revised_Rate,
222 X_Guarantee_Rate => X_Guarantee_Rate
223 ,p_log_level_rec => p_log_level_rec);
224 end if;
225 else
226
227 fa_formulas_pkg.insert_row (
228 X_Rowid => row_id,
229 X_Method_ID => X_Method_ID,
230 X_Formula_Actual => X_Formula_Actual,
231 X_Formula_Displayed => X_Formula_Displayed,
232 X_Formula_Parsed => X_Formula_Parsed,
233 X_Creation_Date => sysdate,
234 X_Created_By => user_id,
235 X_Last_Update_Date => sysdate,
236 X_Last_Updated_By => user_id,
237 X_Last_Update_Login => 0,
238 X_Original_Rate => X_Original_Rate,
239 X_Revised_Rate => X_Revised_Rate,
240 X_Guarantee_Rate => X_Guarantee_Rate
241 ,p_log_level_rec => p_log_level_rec);
242 end if;
243
244 exception
245 when others then
246
247 FA_STANDARD_PKG.RAISE_ERROR(
248 CALLED_FN => 'fa_formulas_pkg.load_row',
249 CALLING_FN => 'upload fa_formulas'
250 ,p_log_level_rec => p_log_level_rec);
251
252 end LOAD_ROW;
253
254 procedure LOAD_SEED_ROW (
255 x_upload_mode IN VARCHAR2,
256 x_custom_mode IN VARCHAR2,
257 x_method_code IN VARCHAR2,
258 x_life_in_months IN NUMBER,
259 x_owner IN VARCHAR2,
260 x_last_update_date IN DATE,
261 x_formula_actual IN VARCHAR2,
262 x_formula_displayed IN VARCHAR2,
263 x_formula_parsed IN VARCHAR2,
264 x_original_rate IN NUMBER DEFAULT NULL,
265 x_revised_rate IN NUMBER DEFAULT NULL,
266 x_guarantee_rate IN NUMBER DEFAULT NULL
267 ) IS
268
269
270 methods_err exception;
271 h_method_id number(15);
272 h_last_update_date date;
273 h_last_updated_by number;
274
275 h_depr_last_year_flag boolean;
276 h_rate_source_rule varchar2(10);
277 h_deprn_basis_rule varchar2(4);
278 h_excl_sal_val_flag boolean;
279
280 BEGIN
281
282 if (x_upload_mode = 'NLS') then
283 null;
284 else
285 if not fa_cache_pkg.fazccmt (
286 X_method => x_method_code,
287 X_life => x_life_in_months) then
288 h_method_id := null;
289 end if;
290
291 h_method_id := fa_cache_pkg.fazccmt_record.method_id;
292 h_last_update_date := fa_cache_pkg.fazccmt_record.last_update_date;
293 h_last_updated_by := fa_cache_pkg.fazccmt_record.last_updated_by;
294 h_rate_source_rule := fa_cache_pkg.fazccmt_record.rate_source_rule;
295 h_deprn_basis_rule := fa_cache_pkg.fazccmt_record.deprn_basis_rule;
296
297 if fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag = 'YES' then
298 h_excl_sal_val_flag := TRUE;
299 else
300 h_excl_sal_val_flag := FALSE;
301 end if;
302
303 if fa_cache_pkg.fazccmt_record.depreciate_lastyear_flag = 'YES' then
304 h_depr_last_year_flag := TRUE;
305 else
306 h_depr_last_year_flag := FALSE;
307 end if;
308
309 fa_formulas_pkg.LOAD_ROW (
310 x_custom_mode => x_custom_mode,
311 x_method_id => h_method_id,
312 x_db_last_update_date => h_last_update_date,
313 x_db_last_updated_by => h_last_updated_by,
314 x_owner => x_owner,
315 x_last_update_date => x_last_update_date,
316 x_formula_actual => x_formula_actual,
317 x_formula_displayed => x_formula_displayed,
318 x_formula_parsed => x_formula_parsed,
319 x_original_rate => x_original_rate,
320 x_revised_rate => x_revised_rate,
321 x_guarantee_rate => x_guarantee_rate);
322 end if;
323
324 EXCEPTION
325 WHEN methods_err THEN
326
327 fa_srvr_msg.add_sql_error(
328 calling_fn => 'update fa_formulas');
329
330 fa_standard_pkg.raise_error(
331 called_fn => 'farat.lct',
332 calling_fn => 'fa_formulas_pkg.load_seed_row');
333
334 END LOAD_SEED_ROW;
335
336 END FA_FORMULAS_PKG;