1 PACKAGE BODY FA_REC_PVT_PKG1 AS
2 /* $Header: FAXVRC1B.pls 120.5 2005/07/25 10:06:22 yyoon ship $ */
3
4 --
5 -- FUNCTION Validate_Reclass_Basic
6 --
7
8 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
9
10 FUNCTION Validate_Reclass_Basic(
11 p_asset_id IN NUMBER,
12 p_old_category_id IN NUMBER,
13 p_new_category_id IN NUMBER,
14 p_mr_req_id IN NUMBER,
15 x_old_cat_type IN OUT NOCOPY VARCHAR2,
16 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
17 BEGIN
18 if (g_print_debug) then
19 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
20 'Starting Validate_reclass_basic',
21 p_asset_id
22 ,p_log_level_rec => p_log_level_rec);
23 end if;
24
25 /* Otherwise, mass reclass. Just silently skip validation and transaction
26 engine if old and new categories are the same.
27 Redefault will be skipped as well. */
28 -- This check is now handled by Mass Reclass Program.
29 /*
30 IF (p_old_category_id = p_new_category_id) THEN
31 -- The rest can be skipped.
32 RETURN (TRUE);
33 END IF;
34 */
35
36 /* Check if an asset is retired in any book, or if a retirement is pending. */
37 IF NOT Check_Retirements(p_asset_id => p_asset_id
38 ,p_log_level_rec => p_log_level_rec) THEN
39 if (g_print_debug) then
40 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
41 'Check_retirments skipped asset:',
42 p_asset_id
43 ,p_log_level_rec => p_log_level_rec);
44 end if;
45
46 FA_SRVR_MSG.Add_Message(
47 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Reclass_Basic'
48 ,p_log_level_rec => p_log_level_rec);
49 RETURN (FALSE);
50 END IF;
51
52 /* Check if category chage is feasible. */
53 IF NOT Validate_Category_Change(
54 p_asset_id => p_asset_id,
55 p_old_category_id => p_old_category_id,
56 p_new_category_id => p_new_category_id,
57 p_mr_req_id => p_mr_req_id,
58 x_old_cat_type => x_old_cat_type,
59 p_log_level_rec => p_log_level_rec)
60 THEN
61 if (g_print_debug) then
62 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
63 'Validate_Category_Change skipped asset:',
64 p_asset_id
65 ,p_log_level_rec => p_log_level_rec);
66 end if;
67
68 FA_SRVR_MSG.Add_Message(
69 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Reclass_Basic'
70 ,p_log_level_rec => p_log_level_rec);
71 RETURN (FALSE);
72 END IF;
73
74 if (g_print_debug) then
75 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
76 'Validate_reclass_basic successful:',
77 p_asset_id
78 ,p_log_level_rec => p_log_level_rec);
79 end if;
80
81 RETURN (TRUE);
82
83 /* May add validations for FA_ADDITIONS items in the future for single reclass.
84 It is not necessary to add validations for these items, since mass reclass
85 won't need validations for these items. May borrow validation engine from
86 ADDITIONS validation engine in the future. */
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 FA_SRVR_MSG.Add_SQL_Error(
91 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Reclass_Basic'
92 ,p_log_level_rec => p_log_level_rec);
93 RETURN (FALSE);
94 END Validate_Reclass_Basic;
95
96
97 --
98 -- FUNCTION Check_Retirements
99 --
100
101 FUNCTION Check_Retirements(
102 p_asset_id IN NUMBER,
103 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
104 l_is_retired NUMBER := 0;
105 l_pending_retirements NUMBER := 0;
106 faurcl_error EXCEPTION;
107 BEGIN
108 /* Check if the asset is fully retired in any book. */
109 -- Use the existing function in FA_ASSET_RECLASS_PKG.
110
111 SELECT count(*)
112 INTO l_is_retired
113 FROM FA_BOOKS BK,
114 FA_BOOK_CONTROLS BC
115 WHERE
116 BK.ASSET_ID = p_asset_id AND
117 BK.PERIOD_COUNTER_FULLY_RETIRED IS NOT NULL AND
118 BK.DATE_INEFFECTIVE IS NULL AND
119 BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE AND
120 BC.DATE_INEFFECTIVE IS NULL;
121
122 IF l_is_retired > 0 THEN
123 FA_SRVR_MSG.Add_Message(
124 CALLING_FN => 'FA_REC_PVT_PKG1.Check_Retirements',
125 NAME => 'FA_REC_RETIRED'
126 ,p_log_level_rec => p_log_level_rec);
127 RETURN (FALSE);
128 END IF;
129
130 /* Check if there is any pending retirements for the asset. */
131 SELECT count(1) INTO l_pending_retirements
132 FROM fa_retirements
133 WHERE asset_id = p_asset_id
134 AND status IN ('PENDING', 'REINSTATE')
135 AND rownum < 2;
136
137 IF (l_pending_retirements > 0) THEN
138 FA_SRVR_MSG.Add_Message(
139 CALLING_FN => 'FA_REC_PVT_PKG1.Check_Retirements',
140 NAME => 'FA_RET_PENDING_RETIREMENTS'
141 ,p_log_level_rec => p_log_level_rec);
142 RETURN (FALSE);
143 END IF;
144
145 RETURN (TRUE);
146 EXCEPTION
147 WHEN faurcl_error THEN
148 FA_SRVR_MSG.Add_Message(
149 CALLING_FN => 'FA_REC_PVT_PKG1.Check_Retirements'
150 ,p_log_level_rec => p_log_level_rec);
151 RETURN (FALSE);
152 WHEN OTHERS THEN
153 FA_SRVR_MSG.Add_SQL_Error(
154 CALLING_FN => 'FA_REC_PVT_PKG1.Check_Retirements'
155 ,p_log_level_rec => p_log_level_rec);
156 RETURN (FALSE);
157 END Check_Retirements;
158
159
160 --
161 -- FUNCTION Validate_Category_Change
162 --
163
164 FUNCTION Validate_Category_Change(
165 p_asset_id IN NUMBER,
166 p_old_category_id IN NUMBER,
167 p_new_category_id IN NUMBER,
168 p_mr_req_id IN NUMBER,
169 x_old_cat_type IN OUT NOCOPY VARCHAR2,
170 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
171 l_asset_type VARCHAR2(11);
172 l_old_cap_flag VARCHAR2(3);
173 l_new_cat_type VARCHAR2(30);
174 l_lease_id NUMBER(15) := NULL;
175 l_check_count1 NUMBER := 0;
176 l_check_count2 NUMBER := 0;
177 l_ah_units NUMBER := 0;
178 l_dh_units NUMBER := 0;
179 CURSOR get_asset_type IS
180 SELECT asset_type FROM FA_ADDITIONS
181 WHERE asset_id = p_asset_id;
182 CURSOR get_old_cat_info IS
183 SELECT capitalize_flag, category_type
184 FROM FA_CATEGORIES WHERE category_id = p_old_category_id;
185 CURSOR get_lease_id IS
186 SELECT lease_id FROM FA_ADDITIONS
187 WHERE asset_id = p_asset_id;
188 CURSOR get_ah_units IS
189 SELECT units FROM FA_ASSET_HISTORY
190 WHERE asset_id = p_asset_id
191 AND date_ineffective IS NULL;
192 CURSOR get_dh_units IS
193 SELECT sum(units_assigned) FROM FA_DISTRIBUTION_HISTORY
194 WHERE asset_id = p_asset_id
195 AND date_ineffective is NULL;
196 BEGIN
197 -- Check from transction approval per asset. Units in asset history and
198 -- distribution history must match.
199 OPEN get_ah_units;
200 FETCH get_ah_units INTO l_ah_units;
201 CLOSE get_ah_units;
202
203 OPEN get_dh_units;
204 FETCH get_dh_units INTO l_dh_units;
205 CLOSE get_dh_units;
206
207 IF (l_ah_units <> l_dh_units) THEN
208 FA_SRVR_MSG.Add_Message(
209 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Category_Change',
210 NAME => 'FA_SHARED_UNITS_UNBAL'
211 ,p_log_level_rec => p_log_level_rec);
212 RETURN (FALSE);
213 END IF;
214
215 -- Make sure the new category is defined in all the books the asset belongs to.
216 -- Get the number of books in which the new category is defined for the asset.
217 SELECT count(*) INTO l_check_count1
218 FROM FA_CATEGORY_BOOKS cb, FA_BOOKS bk, fa_book_controls bc
219 WHERE bk.asset_id = p_asset_id
220 AND bk.date_ineffective IS NULL
221 AND bk.book_type_code = cb.book_type_code
222 AND cb.category_id = p_new_category_id
223 AND bc.book_type_code = bk.book_type_code
224 AND nvl(bc.date_ineffective,sysdate) >= sysdate;
225
226 -- Get the total number of books the asset belongs to.
227 SELECT count(*) INTO l_check_count2
228 FROM FA_BOOKS bk, FA_BOOK_CONTROLS bc
229 WHERE bk.asset_id = p_asset_id
230 AND bk.date_ineffective IS NULL
231 AND bk.book_type_code = bc.book_type_code
232 AND nvl(bc.date_ineffective,sysdate) >= sysdate;
233
234
235 IF (l_check_count1 <> l_check_count2) THEN
236 if (g_print_debug) then
237 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
238 'Validate_category_change skipped asset:',
239 p_asset_id
240 ,p_log_level_rec => p_log_level_rec);
241
242 fa_debug_pkg.add('FARX_RP.Preview_Reclass',
243 'You may need to define all books your asset belongs to, ',
244 'for the new category. See Setup Categories form.'
245 ,p_log_level_rec => p_log_level_rec);
246 end if;
247
248 FA_SRVR_MSG.Add_Message(
249 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Category_Change',
250 NAME => 'FA_REC_CAT_BOOK_NOT_SETUP'
251 ,p_log_level_rec => p_log_level_rec);
252 /* Message text:
253 'The new category is not defined in at least one of the depreciation
254 books the asset belongs to.' */
255 RETURN (FALSE);
256 END IF;
257
258 OPEN get_asset_type;
259 FETCH get_asset_type INTO l_asset_type;
260 CLOSE get_asset_type;
261
262 OPEN get_old_cat_info;
263 FETCH get_old_cat_info INTO l_old_cap_flag, x_old_cat_type;
264 CLOSE get_old_cat_info;
265
266 IF x_old_cat_type = 'LEASE' THEN
267 OPEN get_lease_id;
268 FETCH get_lease_id INTO l_lease_id;
269 CLOSE get_lease_id;
270 END IF;
271
272 -- x_old_cat_type value returned from Val_Reclass.
273 IF NOT FA_DET_ADD_PKG.Val_Reclass(
274 X_Old_Cat_Id => p_old_category_id,
275 X_New_Cat_Id => p_new_category_id,
276 X_Asset_Id => p_asset_id,
277 X_Asset_Type => l_asset_type,
278 X_Old_Cap_Flag => l_old_cap_flag,
279 X_Old_Cat_Type => x_old_cat_type,
280 X_New_Cat_Type => l_new_cat_type,
281 X_Lease_Id => l_lease_id,
282 X_Calling_Fn => 'FA_REC_PVT_PKG1.Validate_Category_Change',
283 p_log_level_rec => p_log_level_rec)
284 THEN
285 FA_SRVR_MSG.Add_Message(
286 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Category_Change'
287 ,p_log_level_rec => p_log_level_rec);
288 RETURN (FALSE);
289 END IF;
290
291 RETURN (TRUE);
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 FA_SRVR_MSG.Add_SQL_Error(
296 CALLING_FN => 'FA_REC_PVT_PKG1.Validate_Category_Change'
297 ,p_log_level_rec => p_log_level_rec);
298 RETURN (FALSE);
299 END Validate_Category_Change;
300
301
302 /*===================================================================================+
303 | FUNCTION Check_Trans_Date
304 |
305 +====================================================================================*/
306
307 FUNCTION Check_Trans_Date(
308 p_asset_id IN NUMBER,
309 p_book_type_code IN VARCHAR2,
310 p_trans_date IN DATE,
311 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
312
313 prior_trans_date DATE;
314 prior_date_effective DATE;
315 check_another_trans NUMBER := 0;
316 CURSOR get_prior_trans_date IS
317 SELECT max(transaction_date_entered)
318 FROM FA_TRANSACTION_HEADERS
319 WHERE asset_id = p_asset_id
320 AND book_type_code = p_book_type_code
321 AND transaction_type_code not like '%/VOID';
322
323 CURSOR get_prior_date_effective IS
324 SELECT max(date_effective)
325 FROM FA_TRANSACTION_HEADERS
326 WHERE asset_id = p_asset_id
327 AND book_type_code = p_book_type_code;
328 BEGIN
329 /* Logic from FA_BOOKS_VAL5.Amortization_Start_Date from FAXASSET. */
330 -- Check another transaction between transaction date and current period.
331 OPEN get_prior_trans_date;
332 FETCH get_prior_trans_date INTO prior_trans_date;
333 CLOSE get_prior_trans_date;
334
335 IF (p_trans_date < prior_trans_date) THEN
336 FA_SRVR_MSG.Add_Message(
337 CALLING_FN => 'FA_REC_PVT_PKG5.Check_Trans_Date',
338 NAME => 'FA_SHARED_OTHER_TRX_FOLLOW'
339 ,p_log_level_rec => p_log_level_rec);
340 RETURN (FALSE);
341 END IF;
342
343 OPEN get_prior_date_effective;
344 FETCH get_prior_date_effective INTO prior_date_effective;
345 CLOSE get_prior_date_effective;
346
347 SELECT count(1) INTO check_another_trans
348 FROM FA_DEPRN_PERIODS pdp, FA_DEPRN_PERIODS adp
349 WHERE pdp.book_type_code = p_book_type_code
350 AND pdp.book_type_code = adp.book_type_code
351 AND pdp.period_counter > adp.period_counter
352 AND prior_date_effective between pdp.period_open_date
353 and nvl(pdp.period_close_date, to_date('31-12-4712','DD-MM-YYYY'))
354 AND p_trans_date between
355 adp.calendar_period_open_date and adp.calendar_period_close_date;
356
357 IF (check_another_trans > 0) THEN
358 FA_SRVR_MSG.Add_Message(
359 CALLING_FN => 'FA_REC_PVT_PKG5.Check_Trans_Date',
360 NAME => 'FA_SHARED_OTHER_TRX_FOLLOW'
361 ,p_log_level_rec => p_log_level_rec);
362 RETURN (FALSE);
363 END IF;
364
365 RETURN (TRUE);
366
367 EXCEPTION
368 WHEN OTHERS THEN
369 FA_SRVR_MSG.Add_SQL_Error(
370 CALLING_FN => 'FA_REC_PVT_PKG5.Check_Trans_Date'
371 ,p_log_level_rec => p_log_level_rec);
372 RETURN (FALSE);
373 END Check_Trans_Date;
374
375
376 END FA_REC_PVT_PKG1;
[Home] [Help]