1 package body fa_mass_add_validate as
2 --$Header: faxmadb.pls 120.3 2005/07/25 09:58:48 yyoon ship $
3
4 --
5 --
6 -- FUNCTION
7 -- check_valid_asset_number
8 -- PURPOSE
9 -- This function returns
10 -- 1 if asset number has not been used by FA and
11 -- does not conflict with FA automatic numbers
12 -- 0 if asset number is already in use
13 -- 2 if asset number is not in use, but conflicts with FA
14 -- automatic numbering
15 --
16 -- If Oracle error occurs, Oracle error number is returned.
17 --
18 --
19 -- HISTORY
20 -- 28-NOV-95 C. Conlin Created
21 --
22 --
23
24 function check_valid_asset_number (x_asset_number IN varchar2,
25 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
26 return number
27 is
28
29 cursor c2 is
30 select asset_number
31 from fa_additions
32 where asset_number = x_asset_number;
33
34 c2_rec c2%rowtype;
35
36 cursor c3 is
37 select asset_number
38 from fa_mass_additions
39 where asset_number = x_asset_number;
40
41 c3_rec c3%rowtype;
42
43 x_numeric_asset_number NUMBER;
44
45 cursor c5 is
46 select initial_asset_id
47 from fa_system_controls
48 where initial_asset_id < x_numeric_asset_number;
49
50 c5_rec c5%rowtype;
51
52
53 begin
54 if (x_asset_number is null) then
55 return(null);
56 end if;
57
58 open c2;
59 fetch c2 into c2_rec;
60 --asset number used in fa_additions table?
61 if c2%notfound then
62 open c3;
63 fetch c3 into c3_rec;
64 --asset number used in fa_mass_additions table?
65 if c3%notfound then
66 BEGIN
67 x_numeric_asset_number := TO_NUMBER(x_asset_number);
68 --asset number all numeric?
69 EXCEPTION
70 WHEN VALUE_ERROR THEN
71 return(1);
72 END;
73 --asset number conflict with automatic numbering?
74 open c5;
75 fetch c5 into c5_rec;
76 if c5%notfound then
77 return(1);
78 else
79 return(2);
80 end if;
81 close c5;
82 else
83 return(0);
84 end if;
85 close c3;
86 else
87 return(0);
88 end if;
89 close c2;
90
91 exception
92 when others then
93 return(SQLCODE);
94
95 end check_valid_asset_number;
96
97
98 --
99 -- FUNCTION
100 -- can_add_to_asset
101 -- PURPOSE
102 -- This function returns 1 if the asset can receive
103 -- additional cost and returns 0 if the asset cannot
104 -- receive additional cost.
105 --
106 -- If Oracle error occurs, Oracle error number is returned.
107 --
108 -- USAGE The asset_id parameter should be the FA (not PA) asset_id
109 -- (find in fa_additions) for the asset to be added to.
110 --
111 -- The book_type_code parameter should be the book_type_code
112 -- found on the invoice line you are attempting to add. (This
113 -- should be the same book type code as on the asset.)
114 --
115 --
116 --
117 -- HISTORY
118 -- 28-NOV-95 C. Conlin Created
119 --
120 --
121
122 function can_add_to_asset(x_asset_id IN number,
123 x_book_type_code IN varchar2,
124 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
125 return number
126 is
127
128 cursor c1 is
129 select asset_type
130 from fa_additions
131 where asset_id = x_asset_id;
132
133 c1_rec c1%rowtype;
134
135
136 cursor c2 is
137 select book_class
138 from fa_book_controls
139 where book_type_code = x_book_type_code;
140
141 c2_rec c2%rowtype;
142
143 cursor c3 is
144 select asset_id
145 from fa_books
146 where x_asset_id = fa_books.asset_id
147 and exists
148 (select 1 from fa_books fabk
149 where x_asset_id = fabk.asset_id
150 and fabk.book_type_code = x_book_type_code
151 and fabk.date_ineffective is null
152 and fabk.period_counter_fully_retired is null
153 and (fabk.period_counter_fully_reserved is null or
154 (fabk.period_counter_fully_reserved is not null
155 and fabk.period_counter_life_complete is not null))
156 and not exists
157 (select 1 from fa_retirements faret
158 where faret.asset_id = x_asset_id
159 and faret.book_type_code = x_book_type_code
160 and faret.status in
161 ('PENDING','REINSTATE','PARTIAL')));
162 c3_rec c3%rowtype;
163
164
165
166 BEGIN
167 open c1;
168 fetch c1 into c1_rec;
169 if c1_rec.asset_type = 'EXPENSED' then
170 close c1;
171 return(0);
172 else
173 close c1;
174 open c2;
175 fetch c2 into c2_rec;
176 if c2_rec.book_class <> 'CORPORATE' then
177 close c2;
178 return(0);
179 else
180 close c2;
181 open c3;
182 fetch c3 into c3_rec;
183 if c3%notfound then
184 close c3;
185 return(0);
186 else
187 close c3;
188 return(1);
189 end if;
190 end if;
191 end if;
192 exception
193 when others then
194 return(SQLCODE);
195 end can_add_to_asset;
196
197 -- FUNCTION
198 -- valid_date_in_service
199 -- PURPOSE
200 -- The function returns a 1 if the date in service is valid
201 -- and returns a 0 if the date in service is not valid.
202 --
203 --
204 --
205 -- HISTORY
206 -- 28-NOV-95 C. Conlin Created
207
208
209
210 function valid_date_in_service(x_date_in_service IN date,
211 x_book_type_code IN varchar2,
212 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
213 return number
214 is
215 cursor c1 (x_date_in_service IN date) is
216 select count(*) dummy
217 from fa_deprn_periods dp, fa_book_controls bc
218 where trunc(x_date_in_service) >
219 trunc(nvl(dp.calendar_period_close_date,
220 x_date_in_service))
221 and bc.book_class = 'CORPORATE'
222 and bc.book_type_code = nvl(x_book_type_code, 'X')
223 and dp.book_type_code = nvl(x_book_type_code, 'X')
224 and dp.period_close_date is null;
225 c1_rec c1%rowtype;
226
227 /*
228 cursor c2 is
229 select greatest(dp.calendar_period_open_date,
230 least(sysdate, dp.calendar_period_close_date))
231 valid_date
232 from fa_deprn_periods dp
233 where dp.book_type_code = nvl(x_book_type_code, 'X')
234 and dp.period_close_date is null;
235 c2_rec c2%rowtype;
236 */
237 --not necessary for PA purposes. FA should include in their packages.
238
239
240 BEGIN
241
242 IF (x_date_in_service is null) OR
243 (x_book_type_code is null)THEN
244 return(0);
245 END IF;
246
247 open c1(x_date_in_service);
248 fetch c1 into c1_rec;
249 if c1_rec.dummy = 0 then
250 close c1;
251 return(1);
252 else
253 close c1;
254 return(0);
255 end if;
256
257 exception
258 when others then
259 return(SQLCODE);
260 end valid_date_in_service;
261
262
263 end fa_mass_add_validate;
[Home] [Help]