eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FA_FLAT_RATES_PKG

Source


1 PACKAGE BODY FA_FLAT_RATES_PKG as
2 /* $Header: faxiflrb.pls 120.6 2005/07/25 10:02:20 yyoon ship $ */
3 
4 procedure INSERT_ROW (
5    X_ROWID in out nocopy VARCHAR2,
6    X_METHOD_ID in NUMBER,
7    X_BASIC_RATE in NUMBER,
8    X_ADJUSTED_RATE in NUMBER,
9    X_ADJUSTING_RATE in NUMBER,
10    X_ATTRIBUTE1 in VARCHAR2,
11    X_ATTRIBUTE2 in VARCHAR2,
12    X_ATTRIBUTE3 in VARCHAR2,
13    X_ATTRIBUTE4 in VARCHAR2,
14    X_ATTRIBUTE5 in VARCHAR2,
15    X_ATTRIBUTE6 in VARCHAR2,
16    X_ATTRIBUTE7 in VARCHAR2,
17    X_ATTRIBUTE8 in VARCHAR2,
18    X_ATTRIBUTE9 in VARCHAR2,
19    X_ATTRIBUTE10 in VARCHAR2,
20    X_ATTRIBUTE11 in VARCHAR2,
21    X_ATTRIBUTE12 in VARCHAR2,
22    X_ATTRIBUTE13 in VARCHAR2,
23    X_ATTRIBUTE14 in VARCHAR2,
24    X_ATTRIBUTE15 in VARCHAR2,
25    X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
26    X_CREATION_DATE in DATE,
27    X_CREATED_BY in NUMBER,
28    X_LAST_UPDATE_DATE in DATE,
29    X_LAST_UPDATED_BY in NUMBER,
30    X_LAST_UPDATE_LOGIN in NUMBER,
31    p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) is
32 
33   CURSOR C is SELECT rowid FROM fa_flat_rates
34               where  method_id = X_Method_Id
35               and    basic_rate = X_Basic_Rate
36               and    adjusted_rate = X_Adjusted_Rate;
37 
38 begin
39 
40    INSERT INTO fa_flat_rates (
41 	method_id,
42 	basic_rate,
43 	adjusted_rate,
44 	adjusting_rate,
45 	attribute1,
46 	attribute2,
47 	attribute3,
48 	attribute4,
49 	attribute5,
50 	attribute6,
51 	attribute7,
52 	attribute8,
53 	attribute9,
54 	attribute10,
55 	attribute11,
56 	attribute12,
57 	attribute13,
58 	attribute14,
59 	attribute15,
60 	attribute_category_code,
61 	creation_date,
62 	created_by,
63 	last_update_date,
64 	last_updated_by,
65 	last_update_login
66     ) VALUES (
67 	X_Method_ID,
68         X_Basic_Rate,
69         X_Adjusted_Rate,
70         X_Adjusting_Rate,
71         X_Attribute1,
72         X_Attribute2,
73         X_Attribute3,
74         X_Attribute4,
75         X_Attribute5,
76         X_Attribute6,
77         X_Attribute7,
78         X_Attribute8,
79         X_Attribute9,
80         X_Attribute10,
81         X_Attribute11,
82         X_Attribute12,
83         X_Attribute13,
84         X_Attribute14,
85         X_Attribute15,
86         X_Attribute_Category_Code,
87         X_Creation_Date,
88         X_Created_By,
89         X_Last_Update_Date,
90         X_Last_Updated_By,
91         X_Last_Update_Login
92 );
93 
94 	OPEN C;
95 	FETCH C INTO X_Rowid;
96 	if (C%NOTFOUND) then
97 	   CLOSE C;
98 	   Raise NO_DATA_FOUND;
99 	end if;
100 	CLOSE C;
101 
102 exception
103    when others then
104         fa_srvr_msg.add_sql_error(
105 	calling_fn => 'fa_flat_rates_pkg.insert_row'
106 	,p_log_level_rec => p_log_level_rec);
107    raise;
108 
109 end INSERT_ROW;
110 
111 procedure LOCK_ROW (
112    X_METHOD_ID in NUMBER,
113    X_BASIC_RATE in NUMBER,
114    X_ADJUSTED_RATE in NUMBER,
115    X_ADJUSTING_RATE in NUMBER,
116    X_ATTRIBUTE1 in VARCHAR2,
117    X_ATTRIBUTE2 in VARCHAR2,
118    X_ATTRIBUTE3 in VARCHAR2,
119    X_ATTRIBUTE4 in VARCHAR2,
120    X_ATTRIBUTE5 in VARCHAR2,
121    X_ATTRIBUTE6 in VARCHAR2,
122    X_ATTRIBUTE7 in VARCHAR2,
123    X_ATTRIBUTE8 in VARCHAR2,
124    X_ATTRIBUTE9 in VARCHAR2,
125    X_ATTRIBUTE10 in VARCHAR2,
126    X_ATTRIBUTE11 in VARCHAR2,
127    X_ATTRIBUTE12 in VARCHAR2,
128    X_ATTRIBUTE13 in VARCHAR2,
129    X_ATTRIBUTE14 in VARCHAR2,
130    X_ATTRIBUTE15 in VARCHAR2,
131    X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
132    p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) is
133   CURSOR C is
134       	SELECT method_id,
135                basic_rate,
136                adjusted_rate,
137                adjusting_rate,
138                attribute1,
139                attribute2,
140                attribute3,
141                attribute4,
142                attribute5,
143                attribute6,
144                attribute7,
145                attribute8,
146                attribute9,
147                attribute10,
148                attribute11,
149                attribute12,
150                attribute13,
151                attribute14,
152                attribute15,
153                attribute_category_code
154          FROM  fa_flat_rates
155          where  method_id = X_Method_Id
156          and    basic_rate = X_Basic_Rate
157          and    adjusted_rate = X_Adjusted_Rate
158 	 FOR UPDATE of method_id, basic_rate, adjusted_rate NOWAIT;
159    Recinfo C%ROWTYPE;
160 
161 begin
162 
163   OPEN C;
164   FETCH C INTO Recinfo;
165   if (C%NOTFOUND) then
166 	CLOSE C;
167 	FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
168 	APP_EXCEPTION.Raise_Exception;
169   end if;
170   CLOSE C;
171   if (
172 		(Recinfo.Method_ID = X_Method_ID)
173 	AND	(Recinfo.Basic_Rate = X_Basic_Rate)
174 	AND	(Recinfo.Adjusting_Rate = X_Adjusting_Rate)
175 	AND	(Recinfo.Adjusted_Rate = X_Adjusted_Rate)
176 	AND	((Recinfo.Attribute1 = X_Attribute1)
177 	    OR  ((Recinfo.Attribute1 IS NULL)
178 	    AND (X_Attribute1 IS NULL)))
179         AND     ((Recinfo.Attribute2 = X_Attribute2)
180             OR  ((Recinfo.Attribute2 IS NULL)
181             AND (X_Attribute2 IS NULL)))
182         AND     ((Recinfo.Attribute3 = X_Attribute3)
183             OR  ((Recinfo.Attribute3 IS NULL)
184             AND (X_Attribute3 IS NULL)))
185         AND     ((Recinfo.Attribute4 = X_Attribute4)
186             OR  ((Recinfo.Attribute4 IS NULL)
187             AND (X_Attribute4 IS NULL)))
188         AND     ((Recinfo.Attribute5 = X_Attribute5)
189             OR  ((Recinfo.Attribute5 IS NULL)
190             AND (X_Attribute5 IS NULL)))
191         AND     ((Recinfo.Attribute6 = X_Attribute6)
192             OR  ((Recinfo.Attribute6 IS NULL)
193             AND (X_Attribute6 IS NULL)))
194         AND     ((Recinfo.Attribute7 = X_Attribute7)
195             OR  ((Recinfo.Attribute7 IS NULL)
196             AND (X_Attribute7 IS NULL)))
197         AND     ((Recinfo.Attribute8 = X_Attribute8)
198             OR  ((Recinfo.Attribute8 IS NULL)
199             AND (X_Attribute8 IS NULL)))
200         AND     ((Recinfo.Attribute9 = X_Attribute9)
201             OR  ((Recinfo.Attribute9 IS NULL)
202             AND (X_Attribute9 IS NULL)))
203         AND     ((Recinfo.Attribute10 = X_Attribute10)
204             OR  ((Recinfo.Attribute10 IS NULL)
205             AND (X_Attribute10 IS NULL)))
206         AND     ((Recinfo.Attribute11 = X_Attribute11)
207             OR  ((Recinfo.Attribute11 IS NULL)
208             AND (X_Attribute11 IS NULL)))
209         AND     ((Recinfo.Attribute12 = X_Attribute12)
210             OR  ((Recinfo.Attribute12 IS NULL)
211             AND (X_Attribute12 IS NULL)))
212         AND     ((Recinfo.Attribute13 = X_Attribute13)
213             OR  ((Recinfo.Attribute13 IS NULL)
214             AND (X_Attribute13 IS NULL)))
215         AND     ((Recinfo.Attribute14 = X_Attribute14)
216             OR  ((Recinfo.Attribute14 IS NULL)
217             AND (X_Attribute14 IS NULL)))
218         AND     ((Recinfo.Attribute15 = X_Attribute15)
219             OR  ((Recinfo.Attribute15 IS NULL)
220             AND (X_Attribute15 IS NULL))) )   then
221 	    return;
222   else
223 	FND_MESSAGE.set_Name('FND', 'FORM_RECORD_CHANGED');
224 	APP_EXCEPTION.Raise_Exception;
225   end if;
226 
227 end LOCK_ROW;
228 
229 procedure UPDATE_ROW (
230    X_METHOD_ID in NUMBER,
231    X_BASIC_RATE in NUMBER,
232    X_ADJUSTED_RATE in NUMBER,
233    X_ADJUSTING_RATE in NUMBER,
234    X_ATTRIBUTE1 in VARCHAR2,
235    X_ATTRIBUTE2 in VARCHAR2,
236    X_ATTRIBUTE3 in VARCHAR2,
237    X_ATTRIBUTE4 in VARCHAR2,
238    X_ATTRIBUTE5 in VARCHAR2,
239    X_ATTRIBUTE6 in VARCHAR2,
240    X_ATTRIBUTE7 in VARCHAR2,
241    X_ATTRIBUTE8 in VARCHAR2,
242    X_ATTRIBUTE9 in VARCHAR2,
243    X_ATTRIBUTE10 in VARCHAR2,
244    X_ATTRIBUTE11 in VARCHAR2,
245    X_ATTRIBUTE12 in VARCHAR2,
246    X_ATTRIBUTE13 in VARCHAR2,
247    X_ATTRIBUTE14 in VARCHAR2,
248    X_ATTRIBUTE15 in VARCHAR2,
249    X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
250    X_LAST_UPDATE_DATE in DATE,
251    X_LAST_UPDATED_BY in NUMBER,
252    X_LAST_UPDATE_LOGIN in NUMBER,
253    p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) is
254 
255 begin
256 
257    UPDATE fa_flat_rates
258    SET  method_id		= X_Method_ID,
259 	basic_rate		= X_Basic_Rate,
260 	adjusted_rate		= X_Adjusted_Rate,
261 	adjusting_rate		= X_Adjusting_Rate,
262 	attribute1		= X_Attribute1,
263 	attribute2              = X_Attribute2,
264         attribute3              = X_Attribute3,
265         attribute4              = X_Attribute4,
266         attribute5              = X_Attribute5,
267         attribute6              = X_Attribute6,
268         attribute7              = X_Attribute7,
269         attribute8              = X_Attribute8,
270         attribute9              = X_Attribute9,
271         attribute10             = X_Attribute10,
272         attribute11             = X_Attribute11,
273         attribute12             = X_Attribute12,
274         attribute13             = X_Attribute13,
275         attribute14             = X_Attribute14,
276         attribute15             = X_Attribute15
277    where  method_id = X_Method_Id
278    and    basic_rate = X_Basic_Rate
279    and    adjusted_rate = X_Adjusted_Rate;
280 
281    if (SQL%NOTFOUND) then
282            Raise NO_DATA_FOUND;
283         end if;
284 
285 exception
286    when others then
287         fa_srvr_msg.add_sql_error(
288         calling_fn => 'fa_flat_rates_pkg.update_row'
289         ,p_log_level_rec => p_log_level_rec);
290    raise;
291 
292 end UPDATE_ROW;
293 
294 procedure DELETE_ROW (
295    X_METHOD_ID in NUMBER,
296    X_BASIC_RATE in NUMBER,
297    X_ADJUSTED_RATE in NUMBER,
298    p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) is
299 
300 begin
301 
302    DELETE FROM fa_flat_rates
303    where  method_id = X_Method_Id
304    and    basic_rate = X_Basic_Rate
305    and    adjusted_rate = X_Adjusted_Rate;
306 
307    if (SQL%NOTFOUND) then
308            Raise NO_DATA_FOUND;
309         end if;
310 
311 exception
312    when others then
313         fa_srvr_msg.add_sql_error(
314         calling_fn => 'fa_flat_rates_pkg.delete_row'
315         ,p_log_level_rec => p_log_level_rec);
316    raise;
317 
318 end DELETE_ROW;
319 
320 procedure LOAD_ROW (
321    X_CUSTOM_MODE in VARCHAR2,
322    X_METHOD_ID in NUMBER,
323    X_DB_LAST_UPDATED_BY NUMBER,
324    X_DB_LAST_UPDATE_DATE DATE,
325    X_OWNER in VARCHAR2,
326    X_LAST_UPDATE_DATE in DATE,
327    X_BASIC_RATE in NUMBER,
328    X_ADJUSTING_RATE in NUMBER,
329    X_ADJUSTED_RATE in NUMBER,
330    X_ATTRIBUTE1 in VARCHAR2,
331    X_ATTRIBUTE2 in VARCHAR2,
332    X_ATTRIBUTE3 in VARCHAR2,
333    X_ATTRIBUTE4 in VARCHAR2,
334    X_ATTRIBUTE5 in VARCHAR2,
335    X_ATTRIBUTE6 in VARCHAR2,
336    X_ATTRIBUTE7 in VARCHAR2,
337    X_ATTRIBUTE8 in VARCHAR2,
338    X_ATTRIBUTE9 in VARCHAR2,
339    X_ATTRIBUTE10 in VARCHAR2,
340    X_ATTRIBUTE11 in VARCHAR2,
341    X_ATTRIBUTE12 in VARCHAR2,
342    X_ATTRIBUTE13 in VARCHAR2,
343    X_ATTRIBUTE14 in VARCHAR2,
344    X_ATTRIBUTE15 in VARCHAR2,
345    X_ATTRIBUTE_CATEGORY_CODE in VARCHAR2,
346    p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) is
347 
348    h_record_exists	number(15);
349 
350    user_id		number;
351    row_id		varchar2(64);
352 
353 begin
354 
355    user_id := fnd_load_util.owner_id (X_Owner);
356 
357    select count(*)
358    into   h_record_exists
359    from   fa_flat_rates fr
360    where  fr.method_id = X_Method_Id
361    and    fr.basic_rate = X_Basic_Rate
362    and    fr.adjusted_rate = X_Adjusted_Rate;
363 
364    if (h_record_exists > 0) then
365       if (fnd_load_util.upload_test(user_id, x_last_update_date,
366                                     x_db_last_updated_by,x_db_last_update_date,
367                                     X_CUSTOM_MODE )) then
368 
369          fa_flat_rates_pkg.update_row (
370             X_Method_Id	                 => X_Method_Id,
371             X_Basic_Rate                 => X_Basic_Rate,
372             X_Adjusted_Rate              => X_Adjusted_Rate,
373             X_Adjusting_Rate             => X_Adjusting_Rate,
374             X_Attribute1                 => X_Attribute1,
375             X_Attribute2                 => X_Attribute2,
376             X_Attribute3                 => X_Attribute3,
377             X_Attribute4                 => X_Attribute4,
378             X_Attribute5                 => X_Attribute5,
379             X_Attribute6                 => X_Attribute6,
380             X_Attribute7                 => X_Attribute7,
381             X_Attribute8                 => X_Attribute8,
382             X_Attribute9                 => X_Attribute9,
383             X_Attribute10                => X_Attribute10,
384             X_Attribute11                => X_Attribute11,
385             X_Attribute12                => X_Attribute12,
386             X_Attribute13                => X_Attribute13,
387             X_Attribute14                => X_Attribute14,
388             X_Attribute15                => X_Attribute15,
389             X_Attribute_Category_Code    => X_Attribute_Category_Code,
390             X_Last_Update_Date	         => sysdate,
391             X_Last_Updated_By	         => user_id,
392             X_Last_Update_Login	         => 0
393             ,p_log_level_rec => p_log_level_rec);
394       end if;
395 
396    else
397 
398       fa_flat_rates_pkg.insert_row (
399          X_Rowid		      => row_id,
400          X_Method_Id                  => X_Method_Id,
401          X_Basic_Rate                 => X_Basic_Rate,
402          X_Adjusted_Rate              => X_Adjusted_Rate,
403          X_Adjusting_Rate             => X_Adjusting_Rate,
404          X_Attribute1                 => X_Attribute1,
405          X_Attribute2                 => X_Attribute2,
406          X_Attribute3                 => X_Attribute3,
407          X_Attribute4                 => X_Attribute4,
408          X_Attribute5                 => X_Attribute5,
409          X_Attribute6                 => X_Attribute6,
410          X_Attribute7                 => X_Attribute7,
411          X_Attribute8                 => X_Attribute8,
412          X_Attribute9                 => X_Attribute9,
413          X_Attribute10                => X_Attribute10,
414          X_Attribute11                => X_Attribute11,
415          X_Attribute12                => X_Attribute12,
416          X_Attribute13                => X_Attribute13,
417          X_Attribute14                => X_Attribute14,
418          X_Attribute15                => X_Attribute15,
419          X_Attribute_Category_Code    => X_Attribute_Category_Code,
420          X_Creation_Date	      => sysdate,
421          X_Created_By		      => user_id,
422          X_Last_Update_Date           => sysdate,
423          X_Last_Updated_By            => user_id,
424          X_Last_Update_Login          => 0
425          ,p_log_level_rec => p_log_level_rec);
426    end if;
427 
428 exception
429   when others then
430     FA_STANDARD_PKG.RAISE_ERROR(
431 		CALLED_FN => 'fa_flat_rates_pkg.load_row',
432 		CALLING_FN => 'upload fa_flat_rates'
433 		,p_log_level_rec => p_log_level_rec);
434 
435 end LOAD_ROW;
436 
437 procedure LOAD_SEED_ROW (
438                x_upload_mode             IN VARCHAR2,
439                x_custom_mode             IN VARCHAR2,
440                x_method_code             IN VARCHAR2,
441                x_life_in_months          IN NUMBER,
442                x_owner                   IN VARCHAR2,
443                x_last_update_date        IN DATE,
444                x_basic_rate              IN NUMBER,
445                x_adjusting_rate          IN NUMBER,
446                x_adjusted_rate           IN NUMBER,
447                x_attribute1              IN VARCHAR2,
448                x_attribute2              IN VARCHAR2,
449                x_attribute3              IN VARCHAR2,
450                x_attribute4              IN VARCHAR2,
451                x_attribute5              IN VARCHAR2,
452                x_attribute6              IN VARCHAR2,
453                x_attribute7              IN VARCHAR2,
454                x_attribute8              IN VARCHAR2,
455                x_attribute9              IN VARCHAR2,
456                x_attribute10             IN VARCHAR2,
457                x_attribute11             IN VARCHAR2,
458                x_attribute12             IN VARCHAR2,
459                x_attribute13             IN VARCHAR2,
460                x_attribute14             IN VARCHAR2,
461                x_attribute15             IN VARCHAR2,
462                x_attribute_category_code IN VARCHAR2
463                ,p_log_level_rec        IN     FA_API_TYPES.log_level_rec_type default null) IS
464 
465    methods_err           exception;
466    h_method_id           number(15);
467    h_last_update_date    date;
468    h_last_updated_by     number;
469 
470    h_depr_last_year_flag boolean;
471    h_rate_source_rule    varchar2(10);
472    h_deprn_basis_rule    varchar2(4);
473    h_excl_sal_val_flag   boolean;
474 
475 BEGIN
476 
477    if (x_upload_mode = 'NLS') then
478       null;
479    else
480 
481       if not fa_cache_pkg.fazccmt (
482          X_method                => x_method_code,
483          X_life                  => x_life_in_months
484          ,p_log_level_rec => p_log_level_rec) then
485          raise methods_err;
486       end if;
487 
488       h_method_id        := fa_cache_pkg.fazccmt_record.method_id;
489       h_last_update_date := fa_cache_pkg.fazccmt_record.last_update_date;
490       h_last_updated_by := fa_cache_pkg.fazccmt_record.last_updated_by;
491       h_rate_source_rule := fa_cache_pkg.fazccmt_record.rate_source_rule;
492       h_deprn_basis_rule := fa_cache_pkg.fazccmt_record.deprn_basis_rule;
493 
494       if fa_cache_pkg.fazccmt_record.exclude_salvage_value_flag = 'YES' then
495          h_excl_sal_val_flag := TRUE;
496       else
497          h_excl_sal_val_flag := FALSE;
498       end if;
499 
500       if fa_cache_pkg.fazccmt_record.depreciate_lastyear_flag = 'YES' then
501          h_depr_last_year_flag := TRUE;
502       else
503          h_depr_last_year_flag := FALSE;
504       end if;
505 
506       fa_flat_rates_pkg.LOAD_ROW (
507                x_custom_mode             => x_custom_mode,
508                x_method_id               => h_method_id,
509                x_db_last_update_date     => h_last_update_date,
510                x_db_last_updated_by      => h_last_updated_by,
511                x_owner                   => x_owner,
512                x_last_update_date        => x_last_update_date,
513                x_basic_rate              => x_basic_rate,
514                x_adjusting_rate          => x_adjusting_rate,
515                x_adjusted_rate           => x_adjusted_rate,
516                x_attribute1              => x_attribute1,
517                x_attribute2              => x_attribute2,
518                x_attribute3              => x_attribute3,
519                x_attribute4              => x_attribute4,
520                x_attribute5              => x_attribute5,
521                x_attribute6              => x_attribute6,
522                x_attribute7              => x_attribute7,
523                x_attribute8              => x_attribute8,
524                x_attribute9              => x_attribute9,
525                x_attribute10             => x_attribute10,
526                x_attribute11             => x_attribute11,
527                x_attribute12             => x_attribute12,
528                x_attribute13             => x_attribute13,
529                x_attribute14             => x_attribute14,
530                x_attribute15             => x_attribute15,
531                x_attribute_category_code => x_attribute_category_code
532                ,p_log_level_rec => p_log_level_rec);
533 
534    end if;
535 
536 EXCEPTION
537    WHEN methods_err THEN
538       fa_srvr_msg.add_sql_error(
539          calling_fn => 'updating flat_rates'
540          ,p_log_level_rec => p_log_level_rec);
541 
542       fa_standard_pkg.raise_error(
543          called_fn => 'farat.lct',
544          calling_fn => 'fa_flat_rates_pkg.load_seed_row'
545          ,p_log_level_rec => p_log_level_rec);
546 
547 END LOAD_SEED_ROW;
548 
549 END FA_FLAT_RATES_PKG;