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_CEILINGS_PKG

Source


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