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