[Home] [Help]
PACKAGE BODY: APPS.OKL_LIKE_KIND_EXCHANGE_PVT
Source
1 PACKAGE BODY OKL_LIKE_KIND_EXCHANGE_PVT AS
2 /* $Header: OKLRLKXB.pls 120.10 2006/11/27 13:21:04 kthiruva noship $ */
3
4 FUNCTION GET_TOTAL_MATCH_AMT (p_asset_id IN NUMBER,
5 p_tax_book IN VARCHAR2) RETURN NUMBER IS
6
7 l_asset_id NUMBER;
8 l_total_match_amount NUMBER;
9
10 CURSOR get_total_match_amt_csr (p_req_asset_id NUMBER, p_tax_book_code VARCHAR2) IS
11 SELECT trx.req_asset_id, sum(trx.total_match_amount)
12 FROM okl_trx_assets trx, okl_txl_assets_v txl, okl_txd_assets_v txd
13 WHERE trx.id = txl.tas_id
14 AND txl.id = txd.tal_id
15 AND trx.total_match_amount IS NOT NULL
16 AND txd.tax_book = p_tax_book_code
17 AND trx.req_asset_id = p_req_asset_id
18 GROUP BY trx.req_asset_id;
19
20 BEGIN
21
22 OPEN get_total_match_amt_csr(p_asset_id, p_tax_book);
23 FETCH get_total_match_amt_csr INTO l_asset_id, l_total_match_amount;
24 IF get_total_match_amt_csr%NOTFOUND THEN
25 CLOSE get_total_match_amt_csr;
26 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
27 END IF;
28
29 RETURN(l_total_match_amount);
30
31 EXCEPTION
32 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
33 RETURN NULL;
34
35 WHEN OTHERS THEN
36 RETURN NULL;
37
38 END GET_TOTAL_MATCH_AMT;
39
40 FUNCTION GET_BALANCE_SALE_PROCEEDS (p_asset_id IN NUMBER,
41 p_tax_book IN VARCHAR2) RETURN NUMBER IS
42
43 l_bal_sale_proceeds NUMBER;
44 l_total_match_amount NUMBER;
45 l_sale_proceeds NUMBER;
46
47 CURSOR get_sale_proceeds_csr (p_req_asset_id NUMBER, p_tax_book_code VARCHAR2) IS
48 SELECT proceeds_of_sale
49 FROM OKL_LIKE_KIND_EXCHANGE_V
50 WHERE asset_id = p_req_asset_id
51 AND book_type_code = p_tax_book_code;
52
53 BEGIN
54
55 l_total_match_amount := GET_TOTAL_MATCH_AMT(p_asset_id, p_tax_book);
56 IF l_total_match_amount IS NULL THEN
57 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
58 END IF;
59
60 OPEN get_sale_proceeds_csr(p_asset_id, p_tax_book);
61 FETCH get_sale_proceeds_csr INTO l_sale_proceeds;
62 IF get_sale_proceeds_csr%NOTFOUND THEN
63 CLOSE get_sale_proceeds_csr;
64 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
65 END IF;
66
67 l_bal_sale_proceeds := l_sale_proceeds - l_total_match_amount;
68
69 RETURN(l_bal_sale_proceeds);
70
71 EXCEPTION
72 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
73 RETURN NULL;
74
75 WHEN OTHERS THEN
76 RETURN NULL;
77
78 END GET_BALANCE_SALE_PROCEEDS;
79
80 FUNCTION GET_DEFERRED_GAIN (p_asset_id IN VARCHAR2,
81 p_tax_book IN VARCHAR2) RETURN NUMBER IS
82
83 l_bal_sale_proceeds NUMBER;
84 l_orig_gain_loss NUMBER;
85 l_orig_sale_proceeds NUMBER;
86 l_deferred_gain NUMBER;
87
88 CURSOR get_orig_amounts_csr (p_req_asset_id NUMBER, p_tax_book_code VARCHAR2) IS
89 SELECT proceeds_of_sale, gain_loss_amount
90 FROM OKL_LIKE_KIND_EXCHANGE_V
91 WHERE asset_id = p_req_asset_id
92 AND book_type_code = p_tax_book_code;
93
94 BEGIN
95
96 OPEN get_orig_amounts_csr(p_asset_id, p_tax_book);
97 FETCH get_orig_amounts_csr INTO l_orig_sale_proceeds, l_orig_gain_loss;
98 IF get_orig_amounts_csr%NOTFOUND THEN
99 CLOSE get_orig_amounts_csr;
100 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
101 END IF;
102
103 l_bal_sale_proceeds := GET_BALANCE_SALE_PROCEEDS(p_asset_id,p_tax_book);
104 l_deferred_gain := ROUND((l_bal_sale_proceeds/l_orig_sale_proceeds)*l_orig_gain_loss,2);
105
106 RETURN(l_deferred_gain);
107
108 EXCEPTION
109 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
110 RETURN NULL;
111
112 WHEN OTHERS THEN
113 RETURN NULL;
114
115 END GET_DEFERRED_GAIN;
116
117 -------------------------------------------------------------------------------
118 --Function to get FA location id. An asset after being sent to FA may have been
119 -- assigned to different FA locations. Since OKL takes only only one FA location
120 --right now , we will pick up only one location.
121 ------------------------------------------------------------------------------
122 FUNCTION get_fa_location (p_asset_id IN VARCHAR2,
123 p_book_type_code IN VARCHAR2,
124 x_location_id OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
125 CURSOR fa_location_curs(p_asset_id IN VARCHAR2,
126 p_book_type_code IN VARCHAR2) is
127 SELECT location_id
128 FROM okx_ast_dst_hst_v
129 WHERE asset_id = p_asset_id
130 AND book_type_code = p_book_type_code
131 AND status = 'A'
132 AND nvl(start_date_active,sysdate) <= sysdate
133 AND nvl(end_date_active,sysdate+1) > sysdate
134 AND transaction_header_id_out is null
135 AND retirement_id is null
136 AND rownum < 2;
137 -- sgiyer 03-JUN-02 Copied from
138 -- from okl we are creating an asset with one location only. Verified
139 -- with AVSINGH.
140 --This is strange way to get one location
141 --since asset can be assigned to multiple
142 --fa locations. But till we know what we have to do
143 --this is it.
144 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
145 l_location_id NUMBER default Null;
146 BEGIN
147 l_return_status := OKL_API.G_RET_STS_SUCCESS;
148 OPEN fa_location_curs(p_asset_id,
149 p_book_type_code);
150 FETCH fa_location_curs
151 INTO l_location_id;
152 IF fa_location_curs%NotFound THEN
153 NULL; --location not found that is not a problem
154 --as it is not a mandatory field
155 END IF;
156 CLOSE fa_location_curs;
157 RETURN(l_return_status);
158 EXCEPTION
159 WHEN Others THEN
160 -- notify caller of an UNEXPECTED error
161 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
162 OKL_API.set_message(
163 G_APP_NAME,
164 G_UNEXPECTED_ERROR,
165 G_SQLCODE_TOKEN,
166 SQLCODE,
167 G_SQLERRM_TOKEN,
168 SQLERRM);
169 -- if the cursor is open
170 IF fa_location_curs%ISOPEN THEN
171 CLOSE fa_location_curs;
172 END IF;
173 RETURN(l_return_status);
174 END Get_fa_Location;
175
176 --------------------------------------------------------------------------------
177 --Start of Comments
178 --Procedure Name : CREATE_FIXED_ASSET
179 --Description : Calls FA additions api to create new like kind assets
180 --History :
181 -- 24-Apr-2002 Shri Iyer Created
182 --End of Comments
183 --------------------------------------------------------------------------------
184 PROCEDURE CREATE_FIXED_ASSET(p_api_version IN NUMBER,
185 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2,
189 p_split_factor IN NUMBER,
190 p_rep_asset_rec IN rep_asset_rec_type,
191 p_asdt_rec IN asset_details_rec_type,
192 p_txlv_rec IN OKL_TXL_ASSETS_PUB.tlpv_rec_type,
193 p_txdv_rec IN OKL_TXD_ASSETS_PUB.adpv_rec_type,
194 x_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type) is
195
196 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
197 l_api_name CONSTANT VARCHAR2(2000) := 'CREATE_FIXED_ASSET';
198 l_api_version CONSTANT NUMBER := 1.0;
199
200 l_trans_rec FA_API_TYPES.trans_rec_type;
201 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
202 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
203 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
204 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
205 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
206 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
207 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
208 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
209 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
210 l_inv_tbl FA_API_TYPES.inv_tbl_type;
211 l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
212
213 l_split_factor NUMBER;
214 l_mesg VARCHAR2(2000);
215 l_mesg_len NUMBER;
216 BEGIN
217
218 -- Call start_activity to create savepoint, check compatibility
219 -- and initialize message list
220 l_return_status := OKL_API.START_ACTIVITY (
221 l_api_name
222 ,p_init_msg_list
223 ,'_PVT'
224 ,l_return_status);
225 -- Check if activity started successfully
226 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
228 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
229 RAISE OKL_API.G_EXCEPTION_ERROR;
230 END IF;
231
232 --FA_SRVR_MSG.Init_Server_Message;
233 --FA_DEBUG_PKG.Initialize;
234
235 --trans_rec_info
236 l_trans_rec.transaction_type_code := 'ADDITION';
237 l_trans_rec.transaction_date_entered := p_asdt_rec.date_placed_in_service;
238 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
239 l_trans_rec.calling_interface := l_api_name;
240
241 --hdr_rec info
242 IF p_asdt_rec.book_class = 'TAX' THEN
243 l_asset_hdr_rec.asset_id := x_asset_hdr_rec.asset_id;
244 END IF;
245 l_asset_hdr_rec.book_type_code := p_asdt_rec.book_type_code;
246
247 -- desc info
248 l_asset_desc_rec.asset_number := p_txlv_rec.asset_number;
249 l_asset_desc_rec.description := p_txlv_rec.description;
250 l_asset_desc_rec.serial_number := p_asdt_rec.serial_number;
251 l_asset_desc_rec.asset_key_ccid := p_asdt_rec.asset_key_ccid;
252 l_asset_desc_rec.manufacturer_name := p_asdt_rec.manufacturer_name;
253 l_asset_desc_rec.model_number := p_asdt_rec.model_number;
254 l_asset_desc_rec.lease_id := p_asdt_rec.lease_id;
255 l_asset_desc_rec.in_use_flag := p_asdt_rec.in_use_flag;
256 l_asset_desc_rec.inventorial := p_asdt_rec.inventorial;
257 l_asset_desc_rec.property_type_code := p_asdt_rec.property_type_code;
258 l_asset_desc_rec.property_1245_1250_code := p_asdt_rec.property_1245_1250_code;
259 l_asset_desc_rec.owned_leased := p_asdt_rec.owned_leased;
260 l_asset_desc_rec.new_used := p_asdt_rec.new_used;
261 l_asset_desc_rec.current_units := p_txlv_rec.current_units;
262
263 --asset_type_rec info
264 l_asset_type_rec.asset_type := p_asdt_rec.asset_type;
265
266 --asset_cat_rec_info
267 l_asset_cat_rec.category_id := p_asdt_rec.asset_category_id;
268
269 --asset_fin_rec
270 l_asset_fin_rec.date_placed_in_service := p_asdt_rec.date_placed_in_service;
271 l_asset_fin_rec.deprn_method_code := p_asdt_rec.deprn_method_code;
272 l_asset_fin_rec.life_in_months := p_asdt_rec.life_in_months;
273 l_asset_fin_rec.cost := p_txlv_rec.depreciation_cost;
274 l_asset_fin_rec.original_cost := p_txlv_rec.original_cost;
275 l_asset_fin_rec.prorate_convention_code := p_asdt_rec.prorate_convention_code;
276 l_asset_fin_rec.depreciate_flag := p_asdt_rec.depreciate_flag;
277 l_asset_fin_rec.itc_amount_id := p_asdt_rec.itc_amount_id;
278 l_asset_fin_rec.basic_rate := p_asdt_rec.basic_rate;
279 l_asset_fin_rec.adjusted_rate := p_asdt_rec.adjusted_rate;
280 l_asset_fin_rec.bonus_rule := p_asdt_rec.bonus_rule;
281 l_asset_fin_rec.ceiling_name := p_asdt_rec.ceiling_name;
282 l_asset_fin_rec.production_capacity := p_asdt_rec.production_capacity;
283 l_asset_fin_rec.unit_of_measure := p_asdt_rec.unit_of_measure;
284 l_asset_fin_rec.reval_ceiling := p_asdt_rec.reval_ceiling;
285 l_asset_fin_rec.unrevalued_cost := p_asdt_rec.unrevalued_cost*p_split_factor;
286 l_asset_fin_rec.short_fiscal_year_flag := p_asdt_rec.short_fiscal_year_flag;
287 l_asset_fin_rec.conversion_date := p_asdt_rec.conversion_date;
288 l_asset_fin_rec.orig_deprn_start_date := p_asdt_rec.original_deprn_start_date;
289 l_asset_fin_rec.group_asset_id := p_asdt_rec.group_asset_id;
290
291 -- asset_deprn_rec
292 IF p_asdt_rec.book_class ='CORPORATE' THEN
293 -- All depreciation information is zero
294 -- because when asset cost is adjusted to zero
295 -- any depreciation taken is reversed and
296 -- an entry is passed for the same. We do not
297 -- want this to happen as technically no
298 -- depreciation was taken.
299 l_asset_deprn_rec.ytd_deprn :=0;
300 l_asset_deprn_rec.deprn_reserve :=0;
301 l_asset_deprn_rec.reval_deprn_reserve :=0;
302 ELSIF p_asdt_rec.book_class = 'TAX' THEN
303 SELECT deprn_reserve*p_split_factor,
304 deprn_reserve*p_split_factor,
305 reval_deprn_expense*p_split_factor
306 INTO l_asset_deprn_rec.ytd_deprn,
307 l_asset_deprn_rec.deprn_reserve,
308 l_asset_deprn_rec.reval_deprn_reserve
309 FROM okx_ast_dprtns_v
310 WHERE asset_id = p_asdt_rec.asset_id
311 AND book_type_code = p_asdt_rec.book_type_code
312 AND deprn_run_date = (SELECT max(deprn_run_date)
313 FROM okx_ast_dprtns_v
314 WHERE asset_id = p_asdt_rec.asset_id
315 AND book_type_code = p_asdt_rec.book_type_code);
316
317 END IF;
318
319 --asset_dist_rec
320 -- no need to prortae again as it has already been done while creating txd
321 select p_txdv_rec.quantity,
322 assigned_to,
323 code_combination_id,
324 location_id
325 into l_asset_dist_rec.units_assigned,
326 l_asset_dist_rec.assigned_to,
327 l_asset_dist_rec.expense_ccid,
328 l_asset_dist_rec.location_ccid
329 from okx_ast_dst_hst_v
330 where asset_id = p_asdt_rec.asset_id
331 and book_type_code = p_txlv_rec.corporate_book
332 and transaction_header_id_out is null
333 and retirement_id is not null
334 and rownum < 2;
335
336 l_asset_dist_tbl(1) := l_asset_dist_rec;
337
338 -- call the api
339 fa_addition_pub.do_addition
340 (p_api_version => 1.0,
341 p_init_msg_list => OKL_API.G_FALSE,
342 p_commit => OKL_API.G_FALSE,
343 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
344 x_return_status => x_return_status,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data,
347 p_calling_fn => null,
348 px_trans_rec => l_trans_rec,
349 px_dist_trans_rec => l_dist_trans_rec,
350 px_asset_hdr_rec => l_asset_hdr_rec,
351 px_asset_desc_rec => l_asset_desc_rec,
352 px_asset_type_rec => l_asset_type_rec,
353 px_asset_cat_rec => l_asset_cat_rec,
354 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
355 px_asset_fin_rec => l_asset_fin_rec,
356 px_asset_deprn_rec => l_asset_deprn_rec,
357 px_asset_dist_tbl => l_asset_dist_tbl,
358 px_inv_tbl => l_inv_tbl
359 );
360
361 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
362 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
363 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
364 RAISE OKL_API.G_EXCEPTION_ERROR;
365 END IF;
366 x_asset_hdr_rec := l_asset_hdr_rec;
367 OKL_API.END_ACTIVITY (x_msg_count,
368 x_msg_data );
369 EXCEPTION
370 WHEN OKL_API.G_EXCEPTION_ERROR THEN
371 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
372 l_api_name,
373 G_PKG_NAME,
374 'OKL_API.G_RET_STS_ERROR',
375 x_msg_count,
376 x_msg_data,
377 '_PVT');
378 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
379 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
380 l_api_name,
381 G_PKG_NAME,
382 'OKL_API.G_RET_STS_UNEXP_ERROR',
383 x_msg_count,
384 x_msg_data,
385 '_PVT');
386 WHEN OTHERS THEN
387 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
388 l_api_name,
389 G_PKG_NAME,
390 'OTHERS',
391 x_msg_count,
392 x_msg_data,
393 '_PVT');
394 END CREATE_FIXED_ASSET;
395
396 --------------------------------------------------------------------------------
397 --Start of Comments
398 --Procedure Name : ADJUST_FIXED_ASSET
399 --Description : Calls FA adJUSTMENTS api to adjust the book costs
400 --History :
401 -- 29-Apr-2002 Shri Iyer Created
402 -- Notes :
403 -- IN Parameters
404 -- p_asset_id - asset for which cost is to be adjusted
405 -- p_book_type_code - Book in whic cost cost is to be adjusted
406 -- p_adjust_cost - cost to be adjusted
407 -- OUT Parameters
408 -- x_asset_fin_rec - asset financial info record with adjusted
409 -- costs
410 --End of Comments
411 --------------------------------------------------------------------------------
412 PROCEDURE ADJUST_FIXED_ASSET(p_api_version IN NUMBER,
413 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2,
417 p_asset_id IN NUMBER,
418 p_book_type_code IN OKL_LIKE_KIND_EXCHANGE_V.BOOK_TYPE_CODE%TYPE,
419 p_adjust_cost IN NUMBER,
420 x_asset_fin_rec OUT NOCOPY FA_API_TYPES.asset_fin_rec_type) IS
421
422
423 l_api_name CONSTANT varchar2(30) := 'ADJUST_FIXED_ASSET';
424 l_api_version CONSTANT NUMBER := 1.0;
425 l_trans_rec FA_API_TYPES.trans_rec_type;
426 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
427 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
428 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
429 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
430 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
431 l_inv_tbl FA_API_TYPES.inv_tbl_type;
432 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
433 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
434 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
435 l_inv_rec FA_API_TYPES.inv_rec_type;
436 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
437 l_group_recalss_option_rec FA_API_TYPES.group_reclass_options_rec_type;
438 l_asset_id NUMBER := p_asset_id;
439 l_book_type_code OKL_LIKE_KIND_EXCHANGE_V.BOOK_TYPE_CODE%TYPE := p_book_type_code;
440 l_adjust_cost NUMBER := p_adjust_cost;
441 BEGIN
442 -- Call start_activity to create savepoint, check compatibility
443 -- and initialize message list
444 x_return_status := OKL_API.START_ACTIVITY (
445 l_api_name
446 ,p_init_msg_list
447 ,'_PVT'
448 ,x_return_status);
449 -- Check if activity started successfully
450 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
451 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
452 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
453 RAISE OKL_API.G_EXCEPTION_ERROR;
454 END IF;
455
456 -- FA_SRVR_MSG.Init_Server_Message;
457 -- FA_DEBUG_PKG.Initialize;
458
459 -- asset header info
460 l_asset_hdr_rec.asset_id := l_asset_id ;
461 l_asset_hdr_rec.book_type_code := l_book_type_code;
462
463 -- trans struct
464 l_trans_rec.transaction_type_code := G_ADJ_TRX_TYPE_CODE;
465
466 -- fin info
467 l_asset_fin_rec_adj.cost := l_adjust_cost;
468
469 FA_ADJUSTMENT_PUB.do_adjustment
470 (p_api_version => p_api_version,
471 p_init_msg_list => p_init_msg_list,
472 p_commit => FND_API.G_FALSE,
473 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
474 x_return_status => x_return_status,
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data,
477 p_calling_fn => l_api_name,
478 px_trans_rec => l_trans_rec,
479 px_asset_hdr_rec => l_asset_hdr_rec,
480 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
481 x_asset_fin_rec_new => l_asset_fin_rec_new,
482 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
483 px_inv_trans_rec => l_inv_trans_rec,
484 px_inv_tbl => l_inv_tbl,
485 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
486 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
487 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
488 p_group_reclass_options_rec => l_group_recalss_option_rec
489 );
490
491 --dbms_output.put_line('After Call to FA ADJUST API "'||l_return_status||'"');
492 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
493 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
494 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
495 RAISE OKL_API.G_EXCEPTION_ERROR;
496 END IF;
497 OKL_API.END_ACTIVITY (x_msg_count,
498 x_msg_data );
499 EXCEPTION
500 WHEN OKL_API.G_EXCEPTION_ERROR THEN
501 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
502 l_api_name,
503 G_PKG_NAME,
504 'OKL_API.G_RET_STS_ERROR',
505 x_msg_count,
506 x_msg_data,
507 '_PVT');
508 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
509 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
510 l_api_name,
511 G_PKG_NAME,
512 'OKL_API.G_RET_STS_UNEXP_ERROR',
513 x_msg_count,
514 x_msg_data,
515 '_PVT');
516 WHEN OTHERS THEN
517 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
518 l_api_name,
519 G_PKG_NAME,
520 'OTHERS',
521 x_msg_count,
522 x_msg_data,
523 '_PVT');
524 END ADJUST_FIXED_ASSET;
525
526 -- this procedure is used create a like kind exchange transaction
527 PROCEDURE CREATE_LIKE_KIND_EXCHANGE(
528 p_api_version IN NUMBER
529 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
530 ,x_return_status OUT NOCOPY VARCHAR2
531 ,x_msg_count OUT NOCOPY NUMBER
532 ,x_msg_data OUT NOCOPY VARCHAR2
533 ,p_corporate_book IN VARCHAR2
534 ,p_tax_book IN VARCHAR2
535 ,p_comments IN VARCHAR2
536 ,p_rep_asset_rec IN rep_asset_rec_type
537 ,p_req_asset_tbl IN req_asset_tbl_type)
538
539 IS
540 -- constants
541 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_LIKE_KIND_EXCHANGE';
542 l_api_version CONSTANT NUMBER := 1.0;
543 l_tas_type CONSTANT OKL_TRX_ASSETS.TAS_TYPE%TYPE := 'LKE';
544 l_tal_type CONSTANT OKL_TXL_ASSETS_V.TAL_TYPE%TYPE := 'LKE';
545 l_tsu_code CONSTANT OKL_TRX_CONTRACTS.TSU_CODE%TYPE := 'PROCESSED';
546 l_try_name CONSTANT OKL_TRX_TYPES_V.NAME%TYPE := 'Like Kind Exchange';
547 --variables
548 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
549 l_try_id OKL_TRX_TYPES_V.ID%TYPE;
550 l_sysdate DATE := SYSDATE;
551 l_total_match_amount NUMBER := 0;
552 l_line_number NUMBER := 1;
553 l_adjust_cost NUMBER := 0;
554 l_balance_match NUMBER := 0;
555 l_balance_sale_proceeds NUMBER := 0;
556 l_fa_location_id NUMBER;
557 l_split_factor NUMBER;
558 l_cat_bk_exists VARCHAR2(1) :='?';
559 l_ast_bk_exists VARCHAR2(1) :='?';
560 l_mass_cpy_book VARCHAR2(1) :='?';
561 l_match_amount_found VARCHAR2(1) := 'N';
562 -- record and table structure variables
563 l_req_asset_tbl req_asset_tbl_type;
564 l_tasv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
565 l_talv_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
566 l_txdv_rec OKL_TXD_ASSETS_PUB.adpv_rec_type;
567 x_tasv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
568 x_talv_rec OKL_TXL_ASSETS_PUB.tlpv_rec_type;
569 x_txdv_rec OKL_TXD_ASSETS_PUB.adpv_rec_type;
570 l_asdt_rec asset_details_rec_type;
571 l_txdt_rec asset_details_rec_type;
572 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
573 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
574
575 -- cursor to get transaction type id
576 CURSOR trx_types_csr IS
577 SELECT id
578 FROM OKL_TRX_TYPES_TL
579 WHERE NAME = l_try_name
580 AND LANGUAGE = 'US';
581
582 -- cursor to get relinquished asset corporate details
583 CURSOR get_asset_corp_details_csr(p_asset_id OKL_LIKE_KIND_EXCHANGE_V.ASSET_ID%TYPE
584 ,p_book_type_code OKL_LIKE_KIND_EXCHANGE_V.BOOK_TYPE_CODE%TYPE) IS
585 SELECT *
586 FROM OKL_LIKE_KIND_EXCHANGE_V
587 WHERE ASSET_ID = p_asset_id
588 AND BOOK_TYPE_CODE = p_book_type_code;
589
590 -- cursor to get relinquished asset tax details
591 CURSOR get_asset_tax_details_csr(p_asset_id OKL_LIKE_KIND_EXCHANGE_V.ASSET_ID%TYPE
592 ,p_book_type_code OKL_LIKE_KIND_EXCHANGE_V.BOOK_TYPE_CODE%TYPE) IS
593 SELECT *
594 FROM OKL_LIKE_KIND_EXCHANGE_V
595 WHERE ASSET_ID = p_asset_id
596 AND BOOK_TYPE_CODE = p_book_type_code;
597
598 --Cursor to chk book validity for an asset category
599 CURSOR chk_cat_bk_csr(p_book_type_code IN VARCHAR2,
600 p_category_id IN NUMBER) is
601 SELECT 'F'
602 FROM OKX_AST_CAT_BKS_V
603 WHERE CATEGORY_ID = p_category_id
604 AND BOOK_TYPE_CODE = p_book_type_code
605 AND STATUS = 'A';
606
607 --Cursor to check if asset_id already exists in tax_book
608 CURSOR chk_ast_bk_csr(p_book_type_code IN Varchar2,
609 p_asset_id IN Number) is
610 SELECT 'F'
611 FROM OKX_AST_BKS_V
612 WHERE asset_id = p_asset_id
613 AND book_type_code = p_book_type_code
614 AND status = 'A';
615
616 --Cursor chk if corp book is the mass copy source book
617 CURSOR chk_mass_cpy_book(p_corp_book IN Varchar2,
618 p_tax_book IN Varchar2) is
619 SELECT 'F'
620 FROM OKX_ASST_BK_CONTROLS_V
621 WHERE book_type_code = p_tax_book
622 AND book_class = 'TAX'
623 AND mass_copy_source_book = p_corp_book
624 AND allow_mass_copy = 'YES'
625 AND copy_additions_flag = 'YES';
626
627 --Cursor to fetch the contract number for a given line_id
628 CURSOR get_contract_number_csr(p_kle_id NUMBER)
629 IS
630 SELECT CHR.CONTRACT_NUMBER
631 FROM OKC_K_HEADERS_B CHR,
632 OKC_K_LINES_B CLE
633 WHERE CLE.DNZ_CHR_ID = CHR.ID
634 AND CLE.ID = p_kle_id;
635
636 get_contract_number_rec get_contract_number_csr%ROWTYPE;
637 l_legal_entity_id NUMBER;
638
639 BEGIN
640 l_return_status := OKL_API.START_ACTIVITY(l_api_name
641 ,G_PKG_NAME
642 ,p_init_msg_list
643 ,l_api_version
644 ,p_api_version
645 ,'_PVT'
646 ,l_return_status);
647
648 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
649 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
650 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
651 RAISE OKL_API.G_EXCEPTION_ERROR;
652 END IF;
653
654 --perform necessary validations
655 -- validate corporate book
656 IF (p_corporate_book IS NULL OR p_corporate_book = OKL_API.G_MISS_CHAR) THEN
657 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
658 p_msg_name => 'OKL_LKE_CORP_BOOK_ERROR');
659 RAISE OKL_API.G_EXCEPTION_ERROR;
660 END IF;
661
662 -- validate tax book
663 IF (p_tax_book IS NULL OR p_tax_book = OKL_API.G_MISS_CHAR) THEN
664 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
665 p_msg_name => 'OKL_LKE_TAX_BOOK_ERROR');
666 RAISE OKL_API.G_EXCEPTION_ERROR;
667 END IF;
668
669 -- validate asset category
670 IF (p_rep_asset_rec.asset_category_id IS NULL
671 OR p_rep_asset_rec.asset_category_id = OKL_API.G_MISS_NUM) THEN
672 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
673 p_msg_name => 'OKL_LKE_AST_CAT_ERROR');
674 RAISE OKL_API.G_EXCEPTION_ERROR;
675 END IF;
676
677 -- validate replacement asset id
678 IF (p_rep_asset_rec.rep_asset_id IS NULL
679 OR p_rep_asset_rec.rep_asset_id = OKL_API.G_MISS_NUM) THEN
680 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
681 p_msg_name => 'OKL_LKE_REP_ASSET_ID_ERROR');
682 RAISE OKL_API.G_EXCEPTION_ERROR;
683 END IF;
684
685 -- validate replacement assets current cost
686 IF (p_rep_asset_rec.current_cost IS NULL
687 OR p_rep_asset_rec.current_cost = OKL_API.G_MISS_NUM) THEN
688 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
689 p_msg_name => 'OKL_LKE_CURR_COST_ERROR',
690 p_token1 => 'ASSET_NUMBER',
691 p_token1_value => p_rep_asset_rec.rep_asset_number);
692 RAISE OKL_API.G_EXCEPTION_ERROR;
693 END IF;
694
695 -- validate req asset id
696 IF p_req_asset_tbl.COUNT = 0 THEN
697 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
698 p_msg_name => 'OKL_LKE_REQ_ASSET_ID_ERROR');
699 RAISE OKL_API.G_EXCEPTION_ERROR;
700 ELSE
701 FOR i IN p_req_asset_tbl.FIRST..p_req_asset_tbl.LAST
702 LOOP
703 IF (p_req_asset_tbl(i).match_amount IS NOT NULL
704 AND p_req_asset_tbl(i).match_amount <> 0) THEN
705 l_match_amount_found := 'Y';
706 l_total_match_amount := l_total_match_amount + p_req_asset_tbl(i).match_amount;
707 END IF;
708 END LOOP;
709 END IF;
710 l_req_asset_tbl := p_req_asset_tbl;
711
712 IF l_match_amount_found = 'Y' THEN
713 IF l_total_match_amount > p_rep_asset_rec.current_cost THEN
714 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
715 p_msg_name => 'OKL_LKE_MATCH_AMT_ERROR');
716 RAISE OKL_API.G_EXCEPTION_ERROR;
717 END IF;
718 ELSE
719 -- automatch
720 l_balance_match := p_rep_asset_rec.current_cost;
721 FOR i IN l_req_asset_tbl.FIRST..l_req_asset_tbl.LAST
722 LOOP
723 IF l_balance_match > 0 THEN
724 l_balance_sale_proceeds := l_req_asset_tbl(i).balance_sale_proceeds;
725 IF l_balance_sale_proceeds >= l_balance_match THEN
726 l_req_asset_tbl(i).match_amount := l_balance_match;
727 l_balance_match := 0;
728 ELSE
729 l_req_asset_tbl(i).match_amount := l_balance_sale_proceeds;
730 l_balance_match := l_balance_match - l_balance_sale_proceeds;
731 END IF;
732 ELSE
733 l_req_asset_tbl.DELETE(i);
734 END IF;
735 END LOOP;
736 END IF;
737
738 IF l_balance_match = p_rep_asset_rec.current_cost THEN
739 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
740 p_msg_name => 'OKL_LKE_AUTO_MATCH_ERROR');
741 RAISE OKL_API.G_EXCEPTION_ERROR;
742 END IF;
743
744 -- retrieve the transaction type id
745 OPEN trx_types_csr;
746 FETCH trx_types_csr INTO l_try_id;
747 IF trx_types_csr%NOTFOUND THEN
748 CLOSE trx_types_csr;
749 -- store SQL error message on message stack for caller
750 Okl_Api.set_message(p_app_name => g_app_name,
751 p_msg_name => 'OKL_AGN_TRX_TYPE_ERROR',
752 p_token1 => 'TRANSACTION_TYPE',
753 p_token1_value => l_try_name);
754 RAISE OKL_API.G_EXCEPTION_ERROR;
755 END IF;
756 CLOSE trx_types_csr;
757
758 -- create relinquished trx header
759 FOR i IN l_req_asset_tbl.FIRST..l_req_asset_tbl.LAST
760 LOOP
761 -- get relinquished asset corporate details
762 OPEN get_asset_corp_details_csr (l_req_asset_tbl(i).req_asset_id, p_corporate_book);
763 FETCH get_asset_corp_details_csr INTO l_asdt_rec;
764 IF get_asset_corp_details_csr%NOTFOUND THEN
765 CLOSE get_asset_corp_details_csr;
766 -- store SQL error message on message stack for caller
767 Okl_Api.set_message(p_app_name => g_app_name,
768 p_msg_name => 'OKL_LKE_ASSET_DTLS_ERROR',
769 p_token1 => 'ASSET_NUMBER',
770 p_token1_value => l_req_asset_tbl(i).req_asset_number,
771 p_token2 => 'CORP_BOOK',
772 p_token2_value => p_corporate_book);
773 RAISE OKL_API.G_EXCEPTION_ERROR;
774 END IF;
775 CLOSE get_asset_corp_details_csr;
776
777 -- get relinquished asset tax details
778 OPEN get_asset_tax_details_csr (l_req_asset_tbl(i).req_asset_id, p_tax_book);
779 FETCH get_asset_tax_details_csr INTO l_txdt_rec;
780 IF get_asset_tax_details_csr%NOTFOUND THEN
781 CLOSE get_asset_tax_details_csr;
782 -- store SQL error message on message stack for caller
783 Okl_Api.set_message(p_app_name => g_app_name,
784 p_msg_name => 'OKL_LKE_AST_TAX_DTLS_ERROR',
785 p_token1 => 'ASSET_NUMBER',
786 p_token1_value => l_req_asset_tbl(i).req_asset_number,
787 p_token2 => 'TAX_BOOK',
788 p_token2_value => p_tax_book);
789 RAISE OKL_API.G_EXCEPTION_ERROR;
790 END IF;
791 CLOSE get_asset_tax_details_csr;
792
793 -- calculate split factor
794 l_split_factor := l_req_asset_tbl(i).match_amount/l_txdt_rec.proceeds_of_sale;
795
796 -- populate the transaction header record
797 l_tasv_rec.req_asset_id := l_req_asset_tbl(i).req_asset_id;
798 l_tasv_rec.tas_type := l_tas_type;
799 l_tasv_rec.tsu_code := l_tsu_code;
800 l_tasv_rec.try_id := l_try_id;
801 l_tasv_rec.date_trans_occurred := l_sysdate;
802 l_tasv_rec.comments := p_comments;
803 l_tasv_rec.total_match_amount := l_req_asset_tbl(i).match_amount;
804 --Added by kthiruva for bug 5581186 - LE Uptake project
805
806 l_legal_entity_id := okl_legal_entity_util.get_khr_line_le_id(l_asdt_rec.kle_id);
807 IF l_legal_entity_id IS NOT NULL THEN
808 l_tasv_rec.legal_entity_id := l_legal_entity_id;
809 ELSE
810 OPEN get_contract_number_csr(l_asdt_rec.kle_id);
811 FETCH get_contract_number_csr INTO get_contract_number_rec;
812 CLOSE get_contract_number_csr;
813
814 Okl_Api.set_message( p_app_name => g_app_name,
815 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
816 p_token1 => 'CONTRACT_NUMBER',
817 p_token1_value => get_contract_number_rec.contract_number);
818 RAISE OKL_API.G_EXCEPTION_ERROR;
819 END IF;
820
821
822 -- call the trx assets public api to create transaction header record
823 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def(
824 p_api_version => p_api_version,
825 p_init_msg_list => p_init_msg_list,
826 x_return_status => l_return_status,
827 x_msg_count => x_msg_count,
828 x_msg_data => x_msg_data,
829 p_thpv_rec => l_tasv_rec,
830 x_thpv_rec => x_tasv_rec);
831 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
832 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
833 p_msg_name => 'OKL_LKE_TRX_CRE_ERROR');
834 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
835 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
836 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
837 p_msg_name => 'OKL_LKE_TRX_CRE_ERROR');
838 RAISE OKL_API.G_EXCEPTION_ERROR;
839 END IF;
840
841 -- populate the transaction line record
842 l_talv_rec.tas_id := x_tasv_rec.id;
843 l_talv_rec.line_number := l_line_number;
844 l_talv_rec.tal_type := l_tal_type;
845 l_return_status := Get_Fa_Location(l_req_asset_tbl(i).req_asset_id,
846 p_corporate_book,
847 l_fa_location_id);
848
849 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
850 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
851 p_msg_name => 'OKL_LKE_AST_LOC_ERROR',
852 p_token1 => 'ASSET_NUMBER',
853 p_token1_value => l_req_asset_tbl(i).req_asset_number);
854 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
855 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
856 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
857 p_msg_name => 'OKL_LKE_AST_LOC_ERROR',
858 p_token1 => 'ASSET_NUMBER',
859 p_token1_value => l_req_asset_tbl(i).req_asset_number);
860 RAISE OKL_API.G_EXCEPTION_ERROR;
861 END IF;
862 l_talv_rec.fa_location_id := l_fa_location_id;
863 l_talv_rec.original_cost := l_asdt_rec.original_cost*l_split_factor;
864 l_talv_rec.current_units := l_asdt_rec.current_units;
865 l_talv_rec.manufacturer_name := l_asdt_rec.manufacturer_name;
866
867 IF l_asdt_rec.new_used = 'NEW' THEN
868 l_talv_rec.used_asset_yn := 'Y';
869 ELSIF l_asdt_rec.new_used = 'USED' THEN
870 l_talv_rec.used_asset_yn := 'N';
871 END IF;
872 l_talv_rec.model_number := l_asdt_rec.model_number;
873 l_talv_rec.corporate_book := p_corporate_book;
874 l_talv_rec.in_service_date := l_asdt_rec.date_placed_in_service;
875 l_talv_rec.life_in_months := l_asdt_rec.life_in_months;
876 l_talv_rec.depreciation_id := l_asdt_rec.asset_category_id;
877 l_talv_rec.depreciation_cost := l_asdt_rec.cost_retired*l_split_factor;
878 l_talv_rec.deprn_method := l_asdt_rec.deprn_method_code;
879 l_talv_rec.deprn_rate := l_asdt_rec.basic_rate;
880 l_talv_rec.rep_asset_id := p_rep_asset_rec.rep_asset_id;
881 l_talv_rec.match_amount := l_req_asset_tbl(i).match_amount;
882 l_talv_rec.description := p_comments;
883 l_talv_rec.kle_id := l_asdt_rec.kle_id;
884 SELECT 'OKL'||OKL_FAN_SEQ.NEXTVAL INTO l_talv_rec.asset_number FROM DUAL;
885
886 -- call the trx assets public api to create transaction header record
887 OKL_TXL_ASSETS_PUB.create_txl_asset_Def(
888 p_api_version => p_api_version,
889 p_init_msg_list => p_init_msg_list,
890 x_return_status => l_return_status,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data,
893 p_tlpv_rec => l_talv_rec,
894 x_tlpv_rec => x_talv_rec);
895 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
896 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
897 p_msg_name => 'OKL_LKE_TXL_CRE_ERROR');
898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
899 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
900 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
901 p_msg_name => 'OKL_LKE_TXL_CRE_ERROR');
902 RAISE OKL_API.G_EXCEPTION_ERROR;
903 END IF;
904
905 -- populate the tax book detail record
906 l_txdv_rec.tal_id := x_talv_rec.id;
907 l_txdv_rec.line_detail_number := l_line_number;
908 l_txdv_rec.quantity := l_txdt_rec.current_units;
909 l_txdv_rec.cost := l_txdt_rec.cost_retired*l_split_factor;
910 l_txdv_rec.tax_book := p_tax_book;
911 l_txdv_rec.life_in_months_tax := l_txdt_rec.life_in_months;
912 l_txdv_rec.deprn_method_tax := l_txdt_rec.deprn_method_code;
913 l_txdv_rec.deprn_rate_tax := l_txdt_rec.adjusted_rate;
914 l_txdv_rec.asset_number := l_talv_rec.asset_number;
915
916 --call the txd details API
917 OKL_TXD_ASSETS_PUB.create_txd_asset_def(
918 p_api_version => p_api_version,
919 p_init_msg_list => p_init_msg_list,
920 x_return_status => l_return_status,
921 x_msg_count => x_msg_count,
922 x_msg_data => x_msg_data,
923 p_adpv_rec => l_txdv_rec,
924 x_adpv_rec => x_txdv_rec);
925 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
926 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
927 p_msg_name => 'OKL_LKE_TXD_CRE_ERROR');
928 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
929 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
930 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
931 p_msg_name => 'OKL_LKE_TXD_CRE_ERROR');
932 RAISE OKL_API.G_EXCEPTION_ERROR;
933 END IF;
934
935 --check for category-id book type code validity
936 OPEN chk_cat_bk_csr(p_book_type_code => x_talv_rec.corporate_book,
937 p_category_id => x_talv_rec.depreciation_id);
938 FETCH chk_cat_bk_csr into l_cat_bk_exists;
939 IF chk_cat_bk_csr%NOTFOUND THEN
940 NULL;
941 END IF;
942 CLOSE chk_cat_bk_csr;
943 IF l_cat_bk_exists = '?' THEN
944 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
945 p_msg_name => G_FA_INVALID_BK_CAT,
946 p_token1 => G_FA_BOOK,
947 p_token1_value => l_talv_rec.corporate_book,
948 p_token2 => G_ASSET_CATEGORY,
949 p_token2_value => to_char(l_talv_rec.depreciation_id)
950 );
951 RAISE OKL_API.G_EXCEPTION_ERROR;
952 ELSE
953 CREATE_FIXED_ASSET(p_api_version => p_api_version,
954 p_init_msg_list => p_init_msg_list,
955 x_return_status => l_return_status,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data,
958 p_split_factor => l_split_factor,
959 p_rep_asset_rec => p_rep_asset_rec,
960 p_asdt_rec => l_asdt_rec,
961 p_txlv_rec => x_talv_rec,
962 p_txdv_rec => x_txdv_rec,
963 x_asset_hdr_rec => l_asset_hdr_rec);
964 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
965 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
966 p_msg_name => 'OKL_LKE_FA_CRE_ERROR',
967 p_token1 => 'CORP_BOOK',
968 p_token1_value => p_corporate_book);
969 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
970 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
971 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
972 p_msg_name => 'OKL_LKE_FA_CRE_ERROR',
973 p_token1 => 'CORP_BOOK',
974 p_token1_value => p_corporate_book);
975 RAISE OKL_API.G_EXCEPTION_ERROR;
976 END IF;
977
978 -- create asset in tax book
979 l_cat_bk_exists := '?';
980 OPEN chk_cat_bk_csr(p_book_type_code => x_txdv_rec.tax_book,
981 p_category_id => x_talv_rec.depreciation_id);
982 FETCH chk_cat_bk_csr INTO l_cat_bk_exists;
983 IF chk_cat_bk_csr%NOTFOUND THEN
984 NULL;
985 END IF;
986 CLOSE chk_cat_bk_csr;
987 IF l_cat_bk_exists = '?' THEN
988 --raise appropriate error
989 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
990 p_msg_name => G_FA_INVALID_BK_CAT,
991 p_token1 => G_FA_BOOK,
992 p_token1_value => x_txdv_rec.tax_book,
993 p_token2 => G_ASSET_CATEGORY,
994 p_token2_value => to_char(x_talv_rec.depreciation_id)
995 );
996 RAISE OKL_API.G_EXCEPTION_ERROR;
997 ELSE
998 --check if asset already exists in tax book
999 l_ast_bk_exists := '?';
1000 OPEN chk_ast_bk_csr(p_book_type_code => x_txdv_rec.tax_book,
1001 p_asset_id => l_asset_hdr_rec.asset_id);
1002 FETCH chk_ast_bk_csr INTO l_ast_bk_exists;
1003 IF chk_ast_bk_csr%NOTFOUND THEN
1004 NULL;
1005 END IF;
1006 CLOSE chk_ast_bk_csr;
1007 IF l_ast_bk_exists = 'F' THEN --asset already exists in tax book
1008 NULL; --do not have to add again
1009 ELSE
1010 --chk if corp book is the mass copy book for the tax book
1011 l_mass_cpy_book := '?';
1012 OPEN chk_mass_cpy_book(p_corp_book => x_talv_rec.corporate_book,
1013 p_tax_book => x_txdv_rec.tax_book);
1014 FETCH chk_mass_cpy_book INTO l_mass_cpy_book;
1015 IF chk_mass_cpy_book%NOTFOUND THEN
1016 NULL;
1017 END IF;
1018 CLOSE chk_mass_cpy_book;
1019 IF l_mass_cpy_book = '?' THEN
1020 --can not mass copy into tax book
1021 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1022 p_msg_name => G_FA_TAX_CPY_NOT_ALLOWED,
1023 p_token1 => G_FA_BOOK,
1024 p_token1_value => x_txdv_rec.tax_book
1025 );
1026 RAISE OKL_API.G_EXCEPTION_ERROR;
1027 ELSE
1028 --can masscopy, create asset
1029 CREATE_FIXED_ASSET(p_api_version => p_api_version,
1030 p_init_msg_list => p_init_msg_list,
1031 x_return_status => l_return_status,
1032 x_msg_count => x_msg_count,
1033 x_msg_data => x_msg_data,
1034 p_split_factor => l_split_factor,
1035 p_rep_asset_rec => p_rep_asset_rec,
1036 p_asdt_rec => l_txdt_rec,
1037 p_txlv_rec => l_talv_rec,
1038 p_txdv_rec => l_txdv_rec,
1039 x_asset_hdr_rec => l_asset_hdr_rec);
1040 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1041 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1042 p_msg_name => 'OKL_LKE_FA_TAX_CRE_ERROR',
1043 p_token1 => 'TAX_BOOK',
1044 p_token1_value => p_tax_book);
1045 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1046 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1047 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1048 p_msg_name => 'OKL_LKE_FA_TAX_CRE_ERROR',
1049 p_token1 => 'TAX_BOOK',
1050 p_token1_value => p_tax_book);
1051 RAISE OKL_API.G_EXCEPTION_ERROR;
1052 END IF;
1053 END IF; --can mass copy into tax book
1054 END IF; -- asset does not exist in tax book
1055 END IF; -- valid tax book for category
1056 END IF;
1057
1058 --Tie back new asset records to OKL
1059 l_talv_rec := x_talv_rec;
1060 -- populate record with updated information
1061 l_talv_rec.lke_asset_id := l_asset_hdr_rec.asset_id;
1062 x_talv_rec := NULL;
1063 OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
1064 p_api_version => p_api_version,
1065 p_init_msg_list => p_init_msg_list,
1066 x_return_status => l_return_status,
1067 x_msg_count => x_msg_count,
1068 x_msg_data => x_msg_data,
1069 p_tlpv_rec => l_talv_rec,
1070 x_tlpv_rec => x_talv_rec);
1071 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1072 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1073 p_msg_name => 'OKL_LKE_TIE_BACK_ERROR');
1074 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1075 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1076 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1077 p_msg_name => 'OKL_LKE_TIE_BACK_ERROR');
1078 RAISE OKL_API.G_EXCEPTION_ERROR;
1079 END IF;
1080
1081 -- Adjust the cost of the LKE asset in corp books to zero as we do not
1082 -- want it to depreciate
1083 l_adjust_cost := (-1) * x_talv_rec.depreciation_cost;
1084 -- to_make asset cost zero
1085 ADJUST_FIXED_ASSET
1086 (p_api_version => p_api_version,
1087 p_init_msg_list => p_init_msg_list,
1088 x_return_status => l_return_status,
1089 x_msg_count => x_msg_count,
1090 x_msg_data => x_msg_data,
1091 p_asset_id => l_asset_hdr_rec.asset_id,
1092 p_book_type_code => l_asdt_rec.book_type_code,
1093 p_adjust_cost => l_adjust_cost,
1094 x_asset_fin_rec => l_asset_fin_rec);
1095
1096 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1097 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1098 p_msg_name => 'OKL_LKE_AST_ADJ_ERROR',
1099 p_token1 => 'CORP_BOOK',
1100 p_token1_value => p_corporate_book);
1101 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1102 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1103 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1104 p_msg_name => 'OKL_LKE_AST_ADJ_ERROR',
1105 p_token1 => 'CORP_BOOK',
1106 p_token1_value => p_corporate_book);
1107 RAISE OKL_API.G_EXCEPTION_ERROR;
1108 END IF;
1109
1110 -- Adjust the cost of the Replacement asset in corp books less the cost of the LKE Asset
1111 l_adjust_cost :=0;
1112 l_adjust_cost := (-1) * x_talv_rec.match_amount;
1113 -- to_make asset cost zero
1114 ADJUST_FIXED_ASSET
1115 (p_api_version => p_api_version,
1116 p_init_msg_list => p_init_msg_list,
1117 x_return_status => l_return_status,
1118 x_msg_count => x_msg_count,
1119 x_msg_data => x_msg_data,
1120 p_asset_id => p_rep_asset_rec.rep_asset_id,
1121 p_book_type_code => p_tax_book,
1122 p_adjust_cost => l_adjust_cost,
1123 x_asset_fin_rec => l_asset_fin_rec);
1124
1125 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1126 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1127 p_msg_name => 'OKL_LKE_REP_AST_ADJ_ERROR',
1128 p_token1 => 'ASSET_NUMBER',
1129 p_token1_value => l_req_asset_tbl(i).req_asset_number,
1130 p_token2 => 'CORP_BOOK',
1131 p_token2_value => p_corporate_book);
1132 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1133 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1134 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1135 p_msg_name => 'OKL_LKE_REP_AST_ADJ_ERROR',
1136 p_token1 => 'ASSET_NUMBER',
1137 p_token1_value => l_req_asset_tbl(i).req_asset_number,
1138 p_token2 => 'CORP_BOOK',
1139 p_token2_value => p_corporate_book);
1140 RAISE OKL_API.G_EXCEPTION_ERROR;
1141 END IF;
1142 END LOOP;
1143 -- set the return status
1144 x_return_status := l_return_status;
1145
1146 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1147
1148 EXCEPTION
1149 -- bug 2404937. Message was being appeneded with the following text:
1150 -- User defined exception in package <name> procedure <name>
1151 -- This was because OKL_API was Okl_Api (everything needs to be caps)
1152 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1153 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1154 ,g_pkg_name
1155 ,'OKL_API.G_RET_STS_ERROR'
1156 ,x_msg_count
1157 ,x_msg_data
1158 ,'_PVT');
1159
1160 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1161 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(l_api_name
1162 ,g_pkg_name
1163 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1164 ,x_msg_count
1165 ,x_msg_data
1166 ,'_PVT');
1167
1168 WHEN OTHERS THEN
1169 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
1170 (l_api_name,
1171 G_PKG_NAME,
1172 'OTHERS',
1173 x_msg_count,
1174 x_msg_data,
1175 '_PVT');
1176
1177 END CREATE_LIKE_KIND_EXCHANGE;
1178
1179 END OKL_LIKE_KIND_EXCHANGE_PVT;