1 PACKAGE BODY FA_FIN_ADD_PKG as
2 /* $Header: faxfaddb.pls 120.4 2005/07/25 09:59:26 yyoon ship $ */
3
4 -- syoung: added x_return_status.
5 procedure gen_deprn_start_date(
6 bks_date_placed_in_service in date,
7 bks_deprn_start_date in out nocopy date,
8 bks_prorate_convention_code in varchar2,
9 bks_fiscal_year_name in varchar2,
10 bks_prorate_date in date,
11 x_return_status out nocopy boolean,
12 bks_calling_fn in varchar2,
13 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
14 is
15 cursor get_dwaf is
16 select depr_when_acquired_flag
17 from fa_convention_types ct
18 where ct.prorate_convention_code = bks_prorate_convention_code
19 and ct.fiscal_year_name = bks_fiscal_year_name;
20
21 dwaf varchar2(3) := 'NO';
22
23 begin
24 open get_dwaf;
25 fetch get_dwaf into dwaf;
26
27 if (get_dwaf%notfound) then
28 close get_dwaf;
29 fnd_message.set_name('OFA', 'FA_FE_CALC_DEPRN_DATE');
30 app_exception.raise_exception;
31 end if;
32
33 close get_dwaf;
34
35 if (dwaf = 'YES') then
36 bks_deprn_start_date := bks_date_placed_in_service;
37 else
38 bks_deprn_start_date := bks_prorate_date;
39 end if;
40
41 x_return_status := true;
42 exception
43 when others then
44 if (bks_calling_fn = 'fa_books_val.dpis_val' or
45 bks_calling_fn = 'fa_books_val3.conv_code_val') then
46 -- called from forms validation module
47 FA_STANDARD_PKG.RAISE_ERROR(
48 CALLED_FN => 'fa_fin_add_pkg.get_deprn_start_date',
49 CALLING_FN => Bks_Calling_Fn,
50 p_log_level_rec => p_log_level_rec);
51 else
52 FA_SRVR_MSG.Add_SQL_Error(
53 CALLING_FN => 'FA_FIN_ADD_PKG.gen_deprn_start_date'
54 ,p_log_level_rec => p_log_level_rec);
55 end if;
56 x_return_status := false;
57
58 end gen_deprn_start_date;
59 --
60 PROCEDURE BOOK_VAL(X_Book_Type_Code VARCHAR2,
61 X_Asset_Type VARCHAR2,
62 X_Category_Id NUMBER,
63 X_Asset_Id NUMBER,
64 X_DPIS IN OUT NOCOPY DATE,
65 X_Expense_Acct IN OUT NOCOPY VARCHAR2,
66 X_Acct_Flex_Num IN OUT NOCOPY NUMBER,
67 X_Calling_Fn VARCHAR2,
68 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
69 IS
70 lv_count number;
71 l_grp_count NUMBER :=0; --HH GROUP ED.
72 lv_mesg varchar2(30);
73 h_status boolean; -- syoung: dummy boolean.
74 validation_error exception;
75 BEGIN
76
77 h_status := FA_FIN_ADD_PKG.CAT_VAL(X_Book_Type_Code => X_Book_Type_Code,
78 X_Asset_Type => X_Asset_Type,
79 X_Category_Id => X_Category_Id,
80 X_Stack_Mesg => 'NO',
81 X_Calling_Fn => 'FA_FIN_ADD_PKG.BOOK_VAL',
82 p_log_level_rec => p_log_level_rec);
83 -- check that book is valid
84 -- syoung: performance tuning: count(*) to count(1)
85 -- and inserted rownum condition.
86
87 select count(1)
88 into lv_count
89 from fa_book_controls
90 where book_type_code = X_BOOK_TYPE_CODE
91 and nvl(date_ineffective, sysdate+1) > sysdate
92 and rownum < 2;
93 --
94 if lv_count = 0 then
95 lv_mesg := 'FA_BOOK_INEFFECTIVE_BOOK';
96 raise validation_error;
97 end if;
98 /*-- HH Group ed.
99 -- Check if defaulted group is enabled. Check is needed because of the
100 -- requirement not to null out the default group asset for a category when
101 -- a group is disabled. However, we can't assign an asset to a disabled group. */
102 SELECT count(1)
103 INTO l_grp_count
104 FROM fa_category_book_defaults cbd, fa_books bks
105 WHERE cbd.category_id = X_Category_Id
106 AND cbd.book_type_code = X_BOOK_TYPE_CODE
107 AND cbd.book_type_code = bks.book_type_code
108 AND cbd.group_asset_id = bks.asset_id
109 AND bks.Date_Placed_In_Service between cbd.start_dpis and
110 nvl(cbd.end_dpis,bks.Date_Placed_In_Service)
111 AND bks.date_ineffective is null
112 AND NVL(bks.disabled_flag, 'N') = 'Y';
113 --
114 if l_grp_count > 0 then
115 lv_mesg := 'FA_DISABLED_DEFAULT_GROUP';
116 raise validation_error;
117 end if; -- end HH.
118 --
119 if X_ASSET_ID is not null then
120 -- check that asset is not already in this book
121 -- syoung: performance tuning: count(*) to count(1)
122 -- and inserted rownum condition.
123 select count(1)
124 into lv_count
125 from fa_books
126 where asset_id = X_ASSET_ID
127 and book_type_code = X_BOOK_TYPE_CODE
128 and date_ineffective is null
129 and rownum < 2;
130 --
131 if lv_count > 0 then
132 lv_mesg := 'FA_QAA_ASSET_IN_BOOK';
133 raise validation_error;
134 end if;
135 -- check that asset has not been added to another Corp book
136 -- syoung: performance tuning: count(*) to count(1)
137 -- and inserted rownum condition.
138 select count(1)
139 into lv_count
140 from fa_books bk, fa_book_controls bc
141 where bk.asset_id= X_ASSET_ID
142 and bk.book_type_code <> X_BOOK_TYPE_CODE
143 and bk.date_ineffective is null
144 and bk.book_type_code = bc.book_type_code
145 and bc.book_class = 'CORPORATE'
146 and rownum < 2;
147 --
148 if lv_count > 0 then
149 lv_mesg := 'FA_BOOK_ASSET_ALREADY_ASSIGN';
150 raise validation_error;
151 end if;
152 end if; /* if X_ASSET_ID is not null */
153 --
154 -- default date placed in service
155 select greatest(dp.calendar_period_open_date,
156 least(sysdate,dp.calendar_period_close_date))
157 into X_DPIS
158 from fa_deprn_periods dp
159 where dp.book_type_code = X_BOOK_TYPE_CODE
160 and dp.period_close_date is null;
161 --
162 -- get acct flex info
163
164 select deprn_expense_acct
165 into X_EXPENSE_ACCT
166 from fa_category_books
167 where category_id = X_CATEGORY_ID
168 and book_type_code = X_BOOK_TYPE_CODE;
169 --
170
171 select accounting_flex_structure
172 into X_ACCT_FLEX_NUM
173 from fa_book_controls
174 where book_type_code = X_BOOK_TYPE_CODE;
175 --
176 EXCEPTION
177 when validation_error then
178 FA_STANDARD_PKG.RAISE_ERROR
179 (Called_Fn => 'FA_FIN_ADD_PKG.BOOK_VAL',
180 Calling_Fn => X_CALLING_FN,
181 Name => lv_mesg,
182 p_log_level_rec => p_log_level_rec);
183
184 when others then
185 FA_STANDARD_PKG.RAISE_ERROR
186 (Called_Fn => 'FA_FIN_ADD_PKG.BOOK_VAL',
187 Calling_Fn => X_CALLING_FN,
188 Name => lv_mesg,
189 p_log_level_rec => p_log_level_rec);
190 END BOOK_VAL;
191 --
192 PROCEDURE DPIS_VAL
193 (X_DPIS DATE,
194 X_Category_Id NUMBER,
195 X_Book_Type_Code VARCHAR2,
196 X_Prorate_Convention_Code IN OUT NOCOPY VARCHAR2,
197 X_Prorate_Date IN OUT NOCOPY DATE,
198 X_Calling_Fn VARCHAR2,
199 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
200 IS
201 lv_count number;
202 lv_mesg varchar2(30);
203 validation_error exception;
204 BEGIN
205 -- dpis must not be in a future period
206 select count(*)
207 into lv_count
208 from fa_deprn_periods
209 where book_type_code = X_BOOK_TYPE_CODE
210 and period_close_date is null
211 and X_DPIS > calendar_period_close_date;
212 --
213 if lv_count > 0 then
214 lv_mesg := 'FA_SHARED_FUTURE_DPIS';
215 raise validation_error;
216 end if;
217 -- default prorate convention code
218 select prorate_convention_code
219 into X_PRORATE_CONVENTION_CODE
220 from fa_category_book_defaults
221 where book_type_code = X_BOOK_TYPE_CODE
222 and category_id = X_CATEGORY_ID
223 and X_DPIS between start_dpis and nvl(end_dpis, X_DPIS);
224 --
225 -- get prorate date
226 begin
227 select prorate_date
228 into X_PRORATE_DATE
229 from fa_conventions
230 where prorate_convention_code = X_PRORATE_CONVENTION_CODE
231 and X_DPIS between start_date and end_date;
232 --
233 exception
234 when no_data_found then
235 lv_mesg := 'FA_BOOK_CANT_GEN_PRORATE_DATE';
236 raise validation_error;
237 end;
238 -- check prorate date
239 select count(*)
240 into lv_count
241 from fa_calendar_periods cp,
242 fa_book_controls bc
243 where bc.book_type_code = X_BOOK_TYPE_CODE
244 and bc.prorate_calendar = cp.calendar_type
245 and X_PRORATE_DATE between cp.start_date and cp.end_date;
246 --
247 if lv_count = 0 then
248 lv_mesg := 'FA_QAA_INVALID_PRORATE_DATE';
249 raise validation_error;
250 end if;
251 --
252 EXCEPTION
253 when validation_error then
254 FA_STANDARD_PKG.RAISE_ERROR
255 (Called_Fn => 'FA_FIN_ADD_PKG.DPIS_VAL',
256 Calling_Fn => X_CALLING_FN,
257 Name => lv_mesg,
258 p_log_level_rec => p_log_level_rec);
259 when others then
260 FA_STANDARD_PKG.RAISE_ERROR
261 (Called_Fn => 'FA_FIN_ADD_PKG.DPIS_VAL',
262 Calling_Fn => X_CALLING_FN,
263 p_log_level_rec => p_log_level_rec);
264 END DPIS_VAL;
265 --
266 -- syoung: changed procedure to function.
267 FUNCTION CAT_VAL(X_Book_Type_Code VARCHAR2,
268 X_Asset_Type VARCHAR2,
269 X_Category_Id NUMBER,
270 X_Stack_Mesg VARCHAR2 DEFAULT 'NO',
271 X_Calling_Fn VARCHAR2,
272 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)RETURN BOOLEAN IS
273 lv_mesg varchar2(50);
274 validation_error exception;
275 lv_count number;
276 BEGIN
277 if X_ASSET_TYPE = 'CIP' then
278 -- check that CIP accounts are set up
279 -- syoung: count(*) to count(1)
280 -- and inserted rownum condition.
281 select count(1)
282 into lv_count
283 from fa_category_books
284 where category_id = X_CATEGORY_ID
285 and book_type_code = X_BOOK_TYPE_CODE
286 and cip_cost_acct is not null
287 and cip_clearing_acct is not null
288 and rownum < 2;
289 --
290 if lv_count = 0 then
291 lv_mesg := 'FA_SHARED_NO_CIP_ACCOUNTS';
292 raise validation_error;
293 end if;
294 end if;
295
296 -- check if cat is set up in this book
297 -- syoung: count(*) to count(1)
298 -- and inserted rownum condition.
299 if (X_Stack_Mesg = 'NO') then
300 -- This check is already done in the reclass validation engine
301 -- for mass reclass before getting to this part.
302 -- Do this check only for single reclass.
303 select count(1)
304 into lv_count
305 from fa_category_books
306 where book_type_code = X_BOOK_TYPE_CODE and
307 category_id = X_CATEGORY_ID
308 and rownum < 2;
309 --
310 if lv_count = 0 then
311 lv_mesg := 'FA_BOOK_CAT_NOT_SET_UP';
312 raise validation_error;
313 end if;
314 end if;
315
316 return (true);
317
318 EXCEPTION
319 when validation_error then
320 if (X_Stack_Mesg = 'NO') then
321 -- invoke client-side mesg.
322 FA_STANDARD_PKG.RAISE_ERROR
323 (Called_Fn => 'FA_FIN_ADD_PKG.CAT_VAL',
324 Calling_Fn => X_CALLING_FN,
325 Name => lv_mesg,
326 p_log_level_rec => p_log_level_rec);
327 else
328 FA_SRVR_MSG.Add_Message(
329 Calling_Fn => 'FA_FIN_ADD_PKG.CAT_VAL',
330 Name => lv_mesg,
331 p_log_level_rec => p_log_level_rec);
332 end if;
333 return (false);
334 when others then
335 if (X_Stack_Mesg = 'NO') then
336 -- invoke client-side mesg.
337 FA_STANDARD_PKG.RAISE_ERROR
338 (Called_Fn => 'FA_FIN_ADD_PKG.CAT_VAL',
339 Calling_Fn => X_CALLING_FN,
340 p_log_level_rec => p_log_level_rec);
341 else
342 FA_SRVR_MSG.Add_SQL_Error(
343 Calling_Fn => 'FA_FIN_ADD_PKG.CAT_VAL'
344 ,p_log_level_rec => p_log_level_rec);
345 end if;
346 return (false);
347 END Cat_Val;
348 --
349 END FA_FIN_ADD_PKG;
[Home] [Help]