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