[Home] [Help]
PACKAGE BODY: APPS.FA_RECLASS_PUB
Source
1 PACKAGE BODY FA_RECLASS_PUB as
2 /* $Header: FAPRECB.pls 120.8 2009/05/09 11:13:28 gigupta ship $ */
3
4 --*********************** Global constants *******************************--
5 G_PKG_NAME CONSTANT varchar2(30) := 'FA_RECLASS_PUB';
6 G_API_NAME CONSTANT varchar2(30) := 'Reclass API';
7 G_API_VERSION CONSTANT number := 1.0;
8
9 g_log_level_rec fa_api_types.log_level_rec_type;
10
11
12 /* ---------------------------------------------------------------
13 * Name : Do_reclass
14 * Type : Procedure
15 * Returns : N/A
16 * Purpose : Perform reclass transaction for an asset
17 * Calling Details : This procedure expects the following parameters with
18 * valid data for it to perform the Reclass transaction
19 * successfully
20 * px_trans_rec.amortization_start_date
21 * px_asset_hdr_rec.asset_id
22 * px_asset_cat_rec_new.category_id
23 * ---------------------------------------------------------------- */
24
25 PROCEDURE do_reclass (
26 -- std parameters
27 p_api_version IN NUMBER,
28 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
29 p_commit IN VARCHAR2 := FND_API.G_FALSE,
30 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
31 p_calling_fn IN VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 -- api parameters
36 px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
37 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
38 px_asset_cat_rec_new IN OUT NOCOPY FA_API_TYPES.asset_cat_rec_type,
39 p_recl_opt_rec IN FA_API_TYPES.reclass_options_rec_type ) IS
40
41 l_api_version CONSTANT NUMBER := 1.0;
42 l_api_name CONSTANT VARCHAR2(30) := 'RECLASS API';
43 l_corp_book VARCHAR2(30);
44 l_status BOOLEAN;
45 l_sysdate date;
46 l_addition_rec fa_additions%ROWTYPE;
47 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
48 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
49 l_asset_cat_rec_old FA_API_TYPES.asset_cat_rec_type;
50 l_period_rec FA_API_TYPES.period_rec_type;
51 l_num_dummy number:= 0;
52
53 l_calling_fn varchar2(40) := 'FA_RECLASS_PUB.do_reclass';
54
55 CURSOR C_corp_book( p_asset_id number ) IS
56 SELECT bc.book_type_code
57 FROM fa_books bks,
58 fa_book_controls bc
59 WHERE bks.book_type_code = bc.distribution_source_book
60 AND bks.book_type_code = bc.book_type_code
61 AND bks.asset_id = p_asset_id
62 AND bks.date_ineffective is null;
63
64 CURSOR c_asset_parent is
65 select parent_hierarchy_id
66 from fa_asset_hierarchy
67 where asset_id = px_asset_hdr_rec.asset_id;
68
69 CURSOR C_txn_date is
70 select greatest(calendar_period_open_date,
71 least(sysdate, calendar_period_close_date))
72 from fa_deprn_periods
73 where book_type_code = px_asset_hdr_rec.book_type_code
74 and period_close_date is null;
75
76 l_err_stage varchar2(640);
77 l_override_flag varchar2(1);
78 l_asset_hr_rec FA_API_TYPES.asset_hierarchy_rec_type;
79 l_asset_hr_opt_rec FA_API_TYPES.asset_hr_options_rec_type;
80 l_crl_enabled BOOLEAN := FALSE;
81 BEGIN
82
83 -- initialize date;
84 l_sysdate := sysdate;
85
86 -- Standard start of API savepoint.
87 SAVEPOINT Reclass_Asset_Begin;
88
89 if (not g_log_level_rec.initialized) then
90 if (NOT fa_util_pub.get_log_level_rec (
91 x_log_level_rec => g_log_level_rec
92 )) then
93 raise FND_API.G_EXC_UNEXPECTED_ERROR;
94 end if;
95 end if;
96
97 l_err_stage:= 'Standard call to check for call compatibility.';
98 -- dbms_output.put_line(l_err_stage);
99 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
100 l_api_name, G_PKG_NAME) then
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END IF;
103
104 l_err_stage:= 'Initialize message list if p_init_msg_list is set to TRUE.';
105 -- dbms_output.put_line(l_err_stage);
106 IF FND_API.To_Boolean(p_init_msg_list) THEN
107 -- Initialize error message stack.
108 FA_SRVR_MSG.Init_Server_Message;
109 -- Initialize debug message stack.
110 FA_DEBUG_PKG.Initialize;
111 END IF;
112
113 -- bugfix 2158910 Override FA: PRINT_DEBUG profile option.
114 -- IF FND_API.To_Boolean(p_debug_flag) THEN
115 -- FA_DEBUG_PKG.Set_Debug_Flag;
116 -- ELSE
117 -- FA_DEBUG_PKG.Set_Debug_Flag('NO', p_log_level_rec => g_log_level_rec);
118 -- END IF;
119
120 l_err_stage := 'validate required parameters';
121 -- -- dbms_output.put_line(l_err_stage);
122 if ( px_asset_hdr_rec.asset_id is null or
123 px_asset_cat_rec_new.category_id is null ) then
124 fa_srvr_msg.add_message(
125 calling_fn => l_calling_fn,
126 name => 'FA_SHARED_ITEM_NULL' , p_log_level_rec => g_log_level_rec);
127 raise FND_API.G_EXC_ERROR;
128 end if;
129
130
131 l_err_stage:= 'check that asset is valid';
132 -- -- dbms_output.put_line(l_err_stage);
133 select count(book_type_code)
134 into l_num_dummy
135 from fa_books b
136 where asset_id = px_asset_hdr_rec.asset_id
137 and date_ineffective is null;
138 if l_num_dummy = 0 then
139 fa_srvr_msg.add_message(
140 calling_fn => l_calling_fn,
141 name => 'FA_INVALID_ASSET' , p_log_level_rec => g_log_level_rec);
142 raise FND_API.G_EXC_ERROR;
143 end if;
144
145 l_err_stage:= 'determine the corporate book';
146 -- dbms_output.put_line(l_err_stage);
147 open c_corp_book( px_asset_hdr_rec.asset_id );
148 fetch c_corp_book into l_corp_book;
149 if l_corp_book is null then
150 close c_corp_book;
151 FA_SRVR_MSG.Add_Message(
152 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
153 raise FND_API.G_EXC_ERROR;
154 end if;
155 close c_corp_book;
156
157 px_asset_hdr_rec.book_type_code := l_corp_book;
158
159 -- load the book controls cache
160 if not fa_cache_pkg.fazcbc(X_book => px_asset_hdr_rec.book_type_code, p_log_level_rec => g_log_level_rec) then
161 raise FND_API.G_EXC_ERROR;
162 end if;
163
164 --Verify if impairment has happened in same period
165 if not FA_ASSET_VAL_PVT.validate_impairment_exists
166 (p_asset_id => px_asset_hdr_rec.asset_id,
167 p_book => px_asset_hdr_rec.book_type_code,
168 p_mrc_sob_type_code => 'P',
169 p_set_of_books_id => px_asset_hdr_rec.set_of_books_id,
170 p_log_level_rec => g_log_level_rec) then
171 raise FND_API.G_EXC_ERROR;
172 end if;
173
174 if (g_log_level_rec.statement_level) then
175 fa_debug_pkg.add(l_calling_fn, 'Book', px_asset_hdr_rec.book_type_code, p_log_level_rec => g_log_level_rec);
176 fa_debug_pkg.add(l_calling_fn, 'Asset_id', px_asset_hdr_rec.asset_id, p_log_level_rec => g_log_level_rec);
177 fa_debug_pkg.add(l_calling_fn, 'Transaction_date_entered', px_trans_rec.transaction_date_entered, p_log_level_rec => g_log_level_rec);
178 fa_debug_pkg.add(l_calling_fn, '-', 'calling FA_TRX_APPROVAL_PKG.faxcat', p_log_level_rec => g_log_level_rec);
179 end if;
180
181 -- call asset level transaction approval
182 -- only if not called from mass process
183 if nvl(p_recl_opt_rec.mass_request_id, -1) = -1 then
184 l_err_stage:= 'FA_TRX_APPROVAL_PKG.faxcat';
185 -- dbms_output.put_line(l_err_stage);
186 if NOT FA_TRX_APPROVAL_PKG.faxcat(
187 X_book =>px_asset_hdr_rec.book_type_code,
188 X_asset_id =>px_asset_hdr_rec.asset_id,
189 X_trx_type =>'RECLASS',
190 X_trx_date =>px_trans_rec.transaction_date_entered,
191 X_init_message_flag=>'NO', p_log_level_rec => g_log_level_rec) then
192 FA_SRVR_MSG.Add_Message(
193 Calling_FN => l_calling_fn, p_log_level_rec => g_log_level_rec);
194 raise FND_API.G_EXC_ERROR;
195 end if;
196 end if;
197
198 -- BUG# 3549470
199 -- force the population of trx_date_entered here
200
201 if not FA_UTIL_PVT.get_period_rec
202 (p_book => px_asset_hdr_rec.book_type_code,
203 x_period_rec => l_period_rec, p_log_level_rec => g_log_level_rec) then
204 raise FND_API.G_EXC_ERROR;
205 end if;
206
207 px_trans_rec.transaction_date_entered :=
208 greatest(l_period_rec.calendar_period_open_date,
209 least(sysdate,l_period_rec.calendar_period_close_date));
210
211 px_trans_rec.transaction_date_entered :=
212 to_date(to_char(px_trans_rec.transaction_date_entered,'DD/MM/YYYY'),'DD/MM/YYYY');
213
214 -- for redefault the user must specify whether to
215 -- expense/amortize the adjustments. If not then
216 -- consider it as expense.
217 if ( p_recl_opt_rec.redefault_flag = 'YES' and
218 ( nvl(px_trans_rec.transaction_subtype, 'XX') not in
219 ('EXPENSED', 'AMORTIZED')) ) then
220 px_trans_rec.transaction_subtype := 'EXPENSED';
221 end if;
222
223 -- dbms_output.put_line('transaction_subtype '|| px_trans_rec.transaction_subtype );
224
225 l_err_stage:= 'fa_utils_pvt.get_asset_desc_rec';
226 -- dbms_output.put_line(l_err_stage);
227 if not fa_util_pvt.get_asset_desc_rec(
228 p_asset_hdr_rec => px_asset_hdr_rec ,
229 px_asset_desc_rec => l_asset_desc_rec , p_log_level_rec => g_log_level_rec) then
230 fa_srvr_msg.add_message(
231 calling_fn => l_calling_fn,
232 name => 'FA_PROJ_GET_ASSET_INFO' , p_log_level_rec => g_log_level_rec);
233 raise FND_API.G_EXC_ERROR;
234 end if;
235
236 l_err_stage:= 'fa_utils_pvt.get_asset_type_rec';
237 -- dbms_output.put_line(l_err_stage);
238 if not fa_util_pvt.get_asset_type_rec(
239 p_asset_hdr_rec => px_asset_hdr_rec,
240 px_asset_type_rec => l_asset_type_rec,
241 p_date_effective => null , p_log_level_rec => g_log_level_rec) then
242 fa_srvr_msg.add_message(
243 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
244 raise FND_API.G_EXC_ERROR;
245 end if;
246
247 -- get current category details
248 l_err_stage:= 'fa_utils_pvt.get_asset_cat_rec';
249 -- dbms_output.put_line(l_err_stage);
250 if not fa_util_pvt.get_asset_cat_rec(
251 p_asset_hdr_rec => px_asset_hdr_rec,
252 px_asset_cat_rec => l_asset_cat_rec_old,
253 p_date_effective => null , p_log_level_rec => g_log_level_rec) then
254 fa_srvr_msg.add_message(
255 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
256 raise FND_API.G_EXC_ERROR;
257 end if;
258
259 l_err_stage:= 'check if old and new categories are same';
260 -- dbms_output.put_line(l_err_stage);
261 if ( l_asset_cat_rec_old.category_id = px_asset_cat_rec_new.category_id ) then
262 -- do nothing. Skip
263 return ;
264 end if;
265
266 l_err_stage:= 'Check if CRL enabled';
267 -- dbms_output.put_line(l_err_stage);
268 if (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
269 l_crl_enabled := TRUE;
270 end if;
271
272 if l_crl_enabled then
273 -- dbms_output.put_line('CRL Is Enabled');
274
275 l_err_stage:= 'Check_override_allowed';
276 -- dbms_output.put_line(l_err_stage);
277 if (not fa_cua_asset_APIS.check_override_allowed(
278 p_attribute_name => 'CATEGORY',
279 p_book_type_code => px_asset_hdr_rec.book_type_code,
280 p_asset_id => px_asset_hdr_rec.asset_id,
281 x_override_flag => l_override_flag,
282 p_log_level_rec => g_log_level_rec)) then
283 fa_srvr_msg.add_message(
284 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
285 raise FND_API.G_EXC_ERROR;
286 end if;
287 if l_override_flag = 'N' then
288 fa_srvr_msg.add_message(
289 calling_fn => l_calling_fn,
290 name => 'FA_OVERRIDE_NOT_ALLOWED',
291 token1 => 'CATEGORY', p_log_level_rec => g_log_level_rec);
292 raise FND_API.G_EXC_ERROR;
293 end if;
294
295 l_err_stage:= 'c_asset_parent';
296 -- dbms_output.put_line(l_err_stage);
297 open C_asset_parent;
298 fetch C_asset_parent into l_asset_hr_rec.parent_hierarchy_id;
299 close C_asset_parent;
300
301 if l_asset_hr_rec.parent_hierarchy_id is not null then
302 -- create/submit batch to derive asset hierarchy attributes
303
304 l_asset_hr_opt_rec.event_code := 'CHANGE_ASSET_CATEGORY';
305 l_asset_hr_opt_rec.status_code := 'N';
306 l_asset_hr_opt_rec.source_entity_name := 'ASSET';
307 l_asset_hr_opt_rec.source_entity_value:= px_asset_hdr_rec.asset_id;
308 l_asset_hr_opt_rec.source_attribute_name:= 'CATEGORY';
309 l_asset_hr_opt_rec.source_attribute_old_id:= l_asset_cat_rec_old.category_id;
310 l_asset_hr_opt_rec.source_attribute_new_id:= px_asset_cat_rec_new.category_id;
311 l_asset_hr_opt_rec.description:= null;
312
313 if px_trans_rec.transaction_subtype = 'AMORTIZED' then
314 l_asset_hr_opt_rec.amortize_flag:= 'Y';
315 if px_trans_rec.amortization_start_date is null then
316 l_err_stage:= 'C_txn_date';
317 -- dbms_output.put_line(l_err_stage);
318 Open C_txn_date;
319 Fetch C_txn_date into l_asset_hr_opt_rec.amortization_start_date;
320 Close C_txn_date;
321 else
322 l_asset_hr_opt_rec.amortization_start_date := px_trans_rec.amortization_start_date;
323 end if;
324 else
325 l_asset_hr_opt_rec.amortize_flag:= 'N';
326 l_asset_hr_opt_rec.amortization_start_date := null;
327 end if;
328
329 l_err_stage:= 'calling FA_ASSET_HIERARCHY_PVT.create_batch';
330 -- dbms_output.put_line(l_err_stage );
331 if not FA_ASSET_HIERARCHY_PVT.create_batch(
332 p_asset_hdr_rec => px_asset_hdr_rec,
333 p_trans_rec => px_trans_rec,
334 p_asset_hr_opt_rec => l_asset_hr_opt_rec , p_log_level_rec => g_log_level_rec) then
335 raise FND_API.G_EXC_ERROR;
336 end if;
337 end if; -- parent_hierarchy not null
338 end if; -- if l_crl_enabled
339
340 if ( (not l_crl_enabled) OR
341 l_asset_hr_rec.parent_hierarchy_id is null ) then
342 -- dbms_output.put_line('CRL Not Enabled');
343 px_trans_rec.transaction_type_code := 'RECLASS';
344
345 if (g_log_level_rec.statement_level) then
346 fa_debug_pkg.add(l_calling_fn, 'Transaction_type_code', px_trans_rec.transaction_type_code , p_log_level_rec => g_log_level_rec);
347 fa_debug_pkg.add(l_calling_fn, '-', 'calling FA_RECLASS_PVT.do_reclass', p_log_level_rec => g_log_level_rec);
348 end if;
349
350
351 l_err_stage:= 'calling FA_RECLASS_PVT.do_reclass';
352 -- dbms_output.put_line(l_err_stage);
353 if not FA_RECLASS_PVT.do_reclass(
354 px_trans_rec,
355 l_asset_desc_rec,
356 px_asset_hdr_rec,
357 l_asset_type_rec,
358 l_asset_cat_rec_old,
359 px_asset_cat_rec_new,
360 p_recl_opt_rec , p_log_level_rec => g_log_level_rec) then
361
362 raise FND_API.G_EXC_ERROR;
363 end if;
364 end if; -- if not crl_enabled
365
366 if FND_API.To_Boolean(p_commit) THEN
367 commit;
368 end if;
369
370 FND_MSG_PUB.Count_And_Get(
371 p_count => x_msg_count,
372 p_data => x_msg_data );
373
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376 EXCEPTION
377 when FND_API.G_EXC_ERROR then
378 rollback to Reclass_Asset_Begin;
379 FA_SRVR_MSG.Add_Message(
380 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
381 FND_MSG_PUB.Count_And_Get(
382 p_count => x_msg_count,
383 p_data => x_msg_data );
384 x_return_status := FND_API.G_RET_STS_ERROR;
385
386 when others then
387 rollback to Reclass_Asset_Begin;
388 fa_srvr_msg.add_sql_error(
389 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
390 FND_MSG_PUB.Count_And_Get(
391 p_count => x_msg_count,
392 p_data => x_msg_data );
393 raise FND_API.G_EXC_UNEXPECTED_ERROR;
394 END do_reclass;
395
396
397 END FA_RECLASS_PUB;