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