1 PACKAGE BODY OKL_AM_ASSET_DISPOSE_PVT AS
2 /* $Header: OKLRADPB.pls 120.35.12010000.2 2008/09/05 22:43:19 smereddy ship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : process_accounting_entries
7 -- Description : This procedure is used to do accounting entries for the disposed asset(s)
8 -- Business Rules :
9 -- Parameters :
10 -- Version : 1.0
11 -- History : SECHAWLA 31-DEC-02 Bug # 2726739
12 -- Added logic to send functional currency code to AE
13 -- SECHAWLA 03-JAN-03 Bug # 2683876
14 -- Added p_func_curr_code parameter. Changed the logic to use functional curr code passed by the
15 -- dispose_asset procedure, instead of deriving the func currency code in process_accounting_entries
16 -- : RMUNJULU 28-APR-04 3596626 Added code to set lp_acc_gen_primary_key_tbl
17 -- : AKRANGAN 28-Apr-07 SLA Single AE Call Uptake Changes
18 -- : rbruno 04-Sep-07 5436987 Asset Disposition Accounting currency code should be contract currency,
19 -- also accounting date will be quote eff date
20 -- End of comments
21 --akrangan added for sla populate sources cr start
22 G_TRAN_TBL_IDX NUMBER := 0;
23 TYPE G_ID_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
24 G_TRANS_ID_TBL G_ID_TBL_TYPE;
25 --akrangan added for sla populate sources cr end
26 TYPE asset_dist_rec IS RECORD ( p_distribution_id NUMBER,
27 p_units_assigned NUMBER);
28
29 TYPE asset_dist_tbl IS TABLE OF asset_dist_rec INDEX BY BINARY_INTEGER;
30 -- These types can not be moved to a procedure as these are used in procedure parameters of one of the
31 -- private procedures.
32
33 PROCEDURE process_accounting_entries(p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 p_kle_id IN NUMBER,
39 p_try_id IN NUMBER,
40 p_sys_date IN DATE,
41 p_source_id IN NUMBER,
42 p_trx_type IN VARCHAR2,
43 p_amount IN NUMBER,
44 p_func_curr_code IN VARCHAR2,
45 x_total_amount OUT NOCOPY NUMBER,
46 p_legal_entity_id IN NUMBER) IS
47
48 -- Cursor to get the product id for the contract header (change to get for contract line)
49 CURSOR prod_id_csr(p_kle_id IN NUMBER) IS
50 SELECT khr.pdt_id,
51 chrb.contract_number,
52 khr.id,
53 chrb.scs_code, -- rmunjulu 4622198
54 chrb.org_id --added by akrangan to get the org_id of the contract
55 FROM okc_k_headers_b chrb,
56 okl_k_headers khr,
57 okc_k_lines_b okc
58 WHERE okc.id = p_kle_id
59 AND khr.id = chrb.id
60 AND okc.chr_id = chrb.id;
61 --akrangan sla cr start
62 --hdr dff fields cursor
63 --this cursor is to populate the
64 -- desc flex fields columns in okl_trx_contracts
65 CURSOR trx_contracts_dff_csr(p_khr_id IN NUMBER) IS
66 SELECT attribute_category,
67 attribute1,
68 attribute2,
69 attribute3,
70 attribute4,
71 attribute5,
72 attribute6,
73 attribute7,
74 attribute8,
75 attribute9,
76 attribute10,
77 attribute11,
78 attribute12,
79 attribute13,
80 attribute14,
81 attribute15
82 FROM okl_k_headers okl
83 WHERE okl.id = p_khr_id;
84 --line dff fields cursor
85 --this cursor is to populate the
86 -- desc flex fields columns in okl_txl_xontract_lines_b
87 CURSOR txl_contracts_dff_csr(p_kle_id IN NUMBER) IS
88 SELECT attribute_category,
89 attribute1,
90 attribute2,
91 attribute3,
92 attribute4,
93 attribute5,
94 attribute6,
95 attribute7,
96 attribute8,
97 attribute9,
98 attribute10,
99 attribute11,
100 attribute12,
101 attribute13,
102 attribute14,
103 attribute15
104 FROM okl_k_lines okl
105 WHERE okl.id = p_kle_id;
106 --record for storing okl_k_lines dffs
107 TYPE dff_rec_type IS RECORD(
108 attribute_category okl_k_lines.attribute_category%TYPE,
109 attribute1 okl_k_lines.attribute1%TYPE,
110 attribute2 okl_k_lines.attribute2%TYPE,
111 attribute3 okl_k_lines.attribute3%TYPE,
112 attribute4 okl_k_lines.attribute4%TYPE,
113 attribute5 okl_k_lines.attribute5%TYPE,
114 attribute6 okl_k_lines.attribute6%TYPE,
115 attribute7 okl_k_lines.attribute7%TYPE,
116 attribute8 okl_k_lines.attribute8%TYPE,
117 attribute9 okl_k_lines.attribute9%TYPE,
118 attribute10 okl_k_lines.attribute10%TYPE,
119 attribute11 okl_k_lines.attribute11%TYPE,
120 attribute12 okl_k_lines.attribute12%TYPE,
121 attribute13 okl_k_lines.attribute13%TYPE,
122 attribute14 okl_k_lines.attribute14%TYPE,
123 attribute15 okl_k_lines.attribute15%TYPE);
124 txl_contracts_dff_rec dff_rec_type;
125 --product name and tax owner
126 CURSOR product_name_csr(p_pdt_id IN NUMBER) IS
127 SELECT NAME,
128 tax_owner
129 FROM okl_product_parameters_v
130 WHERE id = p_pdt_id;
131 --akrangan sla cr end
132 lp_tmpl_identify_rec okl_account_dist_pub.tmpl_identify_rec_type;
133 lp_dist_info_rec okl_account_dist_pub.dist_info_rec_type;
134 lp_ctxt_val_tbl okl_account_dist_pub.ctxt_val_tbl_type;
135 lp_acc_gen_primary_key_tbl okl_account_dist_pub.acc_gen_primary_key;
136 lx_template_tbl okl_account_dist_pub.avlv_tbl_type;
137 lx_amount_tbl okl_account_dist_pub.amount_tbl_type;
138
139 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
140 l_api_name VARCHAR2(30) := 'process_accounting_entries';
141 l_pdt_id NUMBER := 0;
142 l_contract_number VARCHAR2(120);
143 l_khr_id NUMBER;
144 l_trans_meaning VARCHAR2(200);
145 l_total_amount NUMBER := 0;
146 --akrangan sla cr start
147 --loop variables
148 i NUMBER;
149 k NUMBER;
150 l NUMBER;
151 m NUMBER;
152 --akranagna sla cr end
153
154 -- rmunjulu 4622198
155 l_scs_code okc_k_headers_b.scs_code%TYPE;
156 l_fact_synd_code fnd_lookups.lookup_code%TYPE;
157 l_inv_acct_code okc_rules_b.rule_information1%TYPE;
158
159 --akrangan sla cr start
160 --local variables and types declared here
161 l_org_id NUMBER(15);
162 l_currency_code okl_trx_contracts.currency_code%TYPE;
163 l_contract_currency okl_trx_contracts.currency_code%TYPE;
164 l_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
165 l_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
166 l_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
167 l_amount NUMBER;
168 l_total_trx_amount NUMBER;
169 l_validity_date DATE;
170 --trx contracts specific tbl types
171 l_tcnv_rec okl_trx_contracts_pub.tcnv_rec_type;
172 lx_tcnv_rec okl_trx_contracts_pub.tcnv_rec_type;
173 --txl contracts specific tbl types
174 l_tclv_tbl okl_trx_contracts_pub.tclv_tbl_type;
175 lx_tclv_tbl okl_trx_contracts_pub.tclv_tbl_type;
176 --accounting engine specific tbl types and variables
177 l_template_tbl okl_account_dist_pub.avlv_tbl_type;
178 l_tmpl_identify_tbl okl_account_dist_pvt.tmpl_identify_tbl_type;
179 l_dist_info_tbl okl_account_dist_pvt.dist_info_tbl_type;
180 l_ctxt_tbl okl_account_dist_pvt.ctxt_tbl_type;
181 l_template_out_tbl okl_account_dist_pvt.avlv_out_tbl_type;
182 l_amount_out_tbl okl_account_dist_pvt.amount_out_tbl_type;
183 l_acc_gen_tbl okl_account_dist_pvt.acc_gen_tbl_type;
184 l_tcn_id NUMBER;
185 l_line_number NUMBER := 1;
186 --akrangan sla cr end
187
188 -- rbruno bug 5436987
189 l_functional_currency_code VARCHAR2(15);
190 l_contract_currency_code VARCHAR2(15);
191 --l_currency_conversion_type VARCHAR2(30);
192 --l_currency_conversion_rate NUMBER;
193 --l_currency_conversion_date DATE;
194 l_converted_amount NUMBER;
195
196 -- rbruno bug 5436987
197 -- Since we do not use the amount or converted amount
198 -- set a hardcoded value for the amount (and pass to to
199 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
200 -- conversion values )
201 l_hard_coded_amount NUMBER := 100;
202 --end bug 5436987
203
204 BEGIN
205 --akrangan sla cr start
206 -- Get the meaning of lookup
207 l_trans_meaning := okl_am_util_pvt.get_lookup_meaning(p_lookup_type => 'OKL_ACCOUNTING_EVENT_TYPE',
208 p_lookup_code => upper(p_trx_type),
209 p_validate_yn => 'Y');
210
211 -- get the product id
212 --get org_id for the contract
213 OPEN prod_id_csr(p_kle_id);
214 FETCH prod_id_csr
215 INTO l_pdt_id, l_contract_number, l_khr_id, l_scs_code, -- rmunjulu 4622198,
216 l_org_id; --akrangan added
217 CLOSE prod_id_csr;
218
219 --akrangan sla cr end
220 IF l_pdt_id IS NULL OR l_pdt_id = 0
221 THEN
222 -- Error: Unable to create accounting entries because of a missing
223 -- Product Type for the contract CONTRACT_NUMBER.
224 okl_api.set_message(p_app_name => 'OKL',
225 p_msg_name => 'OKL_AM_PRODUCT_ID_ERROR',
226 p_token1 => 'CONTRACT_NUMBER',
227 p_token1_value => l_contract_number);
228
229 END IF;
230
231 -- rmunjulu 4622198 SPECIAL_ACCNT Get special accounting details
232 okl_securitization_pvt.check_khr_ia_associated(p_api_version => p_api_version,
233 p_init_msg_list => okl_api.g_false,
234 x_return_status => l_return_status,
235 x_msg_count => x_msg_count,
236 x_msg_data => x_msg_data,
237 p_khr_id => l_khr_id,
238 p_scs_code => l_scs_code,
239 p_trx_date => p_sys_date,
240 x_fact_synd_code => l_fact_synd_code,
241 x_inv_acct_code => l_inv_acct_code);
242
243 IF l_return_status <> okl_api.g_ret_sts_success
244 THEN
245 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
246 okl_api.set_message(p_app_name => g_app_name,
247 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
248 p_token1 => 'TRX_TYPE',
249 p_token1_value => l_trans_meaning);
250
251 END IF;
252
253 -- Raise exception to rollback to savepoint for this block
254 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
255 THEN
256 RAISE okl_api.g_exception_unexpected_error;
257 ELSIF (l_return_status = okl_api.g_ret_sts_error)
258 THEN
259 RAISE okl_api.g_exception_error;
260 END IF;
261 -- rmunjulu 4622198 SPECIAL_ACCNT set the special accounting parameters
262 lp_tmpl_identify_rec.factoring_synd_flag := l_fact_synd_code;
263 lp_tmpl_identify_rec.investor_code := l_inv_acct_code;
264
265 --getting currency code and currency related attributes
266 l_currency_code := okl_am_util_pvt.get_chr_currency(l_khr_id);
267 --currency conversion variables
268 IF ((p_func_curr_code IS NOT NULL) AND
269 (l_currency_code <> p_func_curr_code))
270 THEN
271 okl_accounting_util.convert_to_functional_currency(p_khr_id => l_khr_id,
272 p_to_currency => p_func_curr_code,
273 p_transaction_date => p_sys_date,
274 p_amount => p_amount,
275 x_return_status => l_return_status,
276 x_contract_currency => l_contract_currency,
277 x_currency_conversion_type => l_currency_conversion_type,
278 x_currency_conversion_rate => l_currency_conversion_rate,
279 x_currency_conversion_date => l_currency_conversion_date,
280 x_converted_amount => l_amount);
281 --setting the currency conversion fields of the rec
282 l_tcnv_rec.currency_conversion_type := l_currency_conversion_type;
283 l_tcnv_rec.currency_conversion_rate := l_currency_conversion_rate;
284 l_tcnv_rec.currency_conversion_date := l_currency_conversion_date;
285 --trap the conversion exception
286 --if conv rate is not found GL API returns negative
287 IF l_return_status <> okl_api.g_ret_sts_success
288 THEN
289 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
290 --currency conversion rate was not found in Oracle GL
291 okc_api.set_message(p_app_name => 'OKL',
292 p_msg_name => 'OKL_LLA_CONV_RATE_NOT_FOUND',
293 p_token1 => 'FROM_CURRENCY',
294 p_token1_value => l_contract_currency,
295 p_token2 => 'TO_CURRENCY',
296 p_token2_value => p_func_curr_code,
297 p_token3 => 'CONVERSION_TYPE',
298 p_token3_value => l_currency_conversion_type,
299 p_token4 => 'CONVERSION_DATE',
300 p_token4_value => to_char(l_currency_conversion_date,
301 'DD-MON-YYYY'));
302
303 END IF;
304
305 -- Raise exception to rollback to savepoint for this block
306 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
307 THEN
308 RAISE okl_api.g_exception_unexpected_error;
309 ELSIF (l_return_status = okl_api.g_ret_sts_error)
310 THEN
311 RAISE okl_api.g_exception_error;
312 END IF;
313 END IF;
314
315 -- *********************************************
316 -- Populate Trx Contracts Header Record
317 -- *********************************************
318 --setting the record with data
319 l_tcnv_rec.khr_id := l_khr_id;
320 l_tcnv_rec.pdt_id := l_pdt_id;
321 l_tcnv_rec.try_id := p_try_id;
322 l_tcnv_rec.tsu_code := 'PROCESSED';
323 l_tcnv_rec.tcn_type := 'ADP';
324 l_tcnv_rec.description := 'Lease transaction on asset disposition';
325 l_tcnv_rec.date_transaction_occurred := p_sys_date;
326 l_tcnv_rec.currency_code := l_currency_code;
327 l_tcnv_rec.org_id := l_org_id;
328 l_tcnv_rec.legal_entity_id := p_legal_entity_id;
329
330 --product name and tax owner code
331 OPEN product_name_csr(l_pdt_id);
332 FETCH product_name_csr
333 INTO l_tcnv_rec.product_name, l_tcnv_rec.tax_owner_code;
334 CLOSE product_name_csr;
335
336 --trx contracts hdr dffs
337 OPEN trx_contracts_dff_csr(l_khr_id);
338 FETCH trx_contracts_dff_csr
339 INTO l_tcnv_rec.attribute_category, l_tcnv_rec.attribute1, l_tcnv_rec.attribute2,
340 l_tcnv_rec.attribute3, l_tcnv_rec.attribute4, l_tcnv_rec.attribute5,
341 l_tcnv_rec.attribute6, l_tcnv_rec.attribute7, l_tcnv_rec.attribute8,
342 l_tcnv_rec.attribute9, l_tcnv_rec.attribute10, l_tcnv_rec.attribute11,
343 l_tcnv_rec.attribute12, l_tcnv_rec.attribute13, l_tcnv_rec.attribute14,
344 l_tcnv_rec.attribute15;
345 CLOSE trx_contracts_dff_csr;
346
347 --call trx contracts to populate the hdr record
348 okl_trx_contracts_pub.create_trx_contracts(
349 p_api_version => p_api_version,
350 p_init_msg_list => okl_api.g_false,
351 x_return_status => l_return_status,
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data,
354 p_tcnv_rec => l_tcnv_rec,
355 x_tcnv_rec => lx_tcnv_rec
356 );
357
358 IF l_return_status <> okl_api.g_ret_sts_success
359 THEN
360 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
361 okl_api.set_message(p_app_name => g_app_name,
362 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
363 p_token1 => 'TRX_TYPE',
364 p_token1_value => l_trans_meaning);
365
366 END IF;
367
368 -- Raise exception to rollback to savepoint for this block
369 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
370 THEN
371 RAISE okl_api.g_exception_unexpected_error;
372 ELSIF (l_return_status = okl_api.g_ret_sts_error)
373 THEN
374 RAISE okl_api.g_exception_error;
375 END IF;
376 --assigning the ourput record type value to the record type sent as input
377 l_tcnv_rec := lx_tcnv_rec;
378
379 -- *********************************************
380 -- Get all The Templates
381 -- *********************************************
382
383 -- Form the tmpl_identify_rec in parameter
384 lp_tmpl_identify_rec.product_id := l_pdt_id;
385 lp_tmpl_identify_rec.transaction_type_id := p_try_id;
386 lp_tmpl_identify_rec.memo_yn := 'N';
387 lp_tmpl_identify_rec.prior_year_yn := 'N';
388 l_validity_date := okl_accounting_util.get_valid_gl_date(p_sys_date);
389 --get template info from accounting distributions API
390 okl_account_dist_pub.get_template_info(
391 p_api_version => p_api_version,
392 p_init_msg_list => okl_api.g_false,
393 x_return_status => l_return_status,
394 x_msg_count => x_msg_count,
395 x_msg_data => x_msg_data,
396 p_tmpl_identify_rec => lp_tmpl_identify_rec,
397 x_template_tbl => l_template_tbl,
398 p_validity_date => l_validity_date
399 );
400 --set error message No Accounting Templates
401 IF l_template_tbl.COUNT = 0
402 THEN
403 l_return_status := okl_api.g_ret_sts_error;
404 END IF;
405
406 IF l_return_status <> okl_api.g_ret_sts_success
407 THEN
408 -- Error occurred No Accounting Templates.
409 okl_api.set_message(p_app_name => 'OKL',
410 p_msg_name => 'OKL_AM_NO_ACC_TEMPLATES',
411 p_token1 => 'PRODUCT',
412 p_token1_value => l_pdt_id);
413
414 END IF;
415
416 -- Raise exception to rollback to savepoint for this block
417 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
418 THEN
419 RAISE okl_api.g_exception_unexpected_error;
420 ELSIF (l_return_status = okl_api.g_ret_sts_error)
421 THEN
422 RAISE okl_api.g_exception_error;
423 END IF;
424
425 -- *********************************************
426 -- Populate Txl Contracts Lines Record
427 -- *********************************************
428
429 --setting the tcl dff records
430 OPEN txl_contracts_dff_csr(p_kle_id);
431 FETCH txl_contracts_dff_csr
432 INTO txl_contracts_dff_rec;
433 CLOSE txl_contracts_dff_csr;
434 --creating trx_contracts lines by calling the API
435 IF l_template_tbl.COUNT > 0
436 THEN
437 i := l_template_tbl.FIRST;
438 LOOP
439 l_tclv_tbl(i).line_number := l_line_number;
440 l_tclv_tbl(i).tcn_id := l_tcnv_rec.id;
441 l_tclv_tbl(i).khr_id := l_khr_id;
442 l_tclv_tbl(i).sty_id := l_template_tbl(i).sty_id;
443 l_tclv_tbl(i).tcl_type := 'ADP';
444 l_tclv_tbl(i).description := 'Lease transaction on asset disposition';
445 l_tclv_tbl(i).currency_code := l_currency_code;
446 l_tclv_tbl(i).kle_id := p_kle_id;
447 l_tclv_tbl(i).org_id := l_org_id;
448 --set dffs
449 l_tclv_tbl(i).attribute_category := txl_contracts_dff_rec.attribute_category;
450 l_tclv_tbl(i).attribute1 := txl_contracts_dff_rec.attribute1;
451 l_tclv_tbl(i).attribute2 := txl_contracts_dff_rec.attribute2;
452 l_tclv_tbl(i).attribute3 := txl_contracts_dff_rec.attribute3;
453 l_tclv_tbl(i).attribute4 := txl_contracts_dff_rec.attribute4;
454 l_tclv_tbl(i).attribute5 := txl_contracts_dff_rec.attribute5;
455 l_tclv_tbl(i).attribute6 := txl_contracts_dff_rec.attribute6;
456 l_tclv_tbl(i).attribute7 := txl_contracts_dff_rec.attribute7;
457 l_tclv_tbl(i).attribute8 := txl_contracts_dff_rec.attribute8;
458 l_tclv_tbl(i).attribute9 := txl_contracts_dff_rec.attribute9;
459 l_tclv_tbl(i).attribute10 := txl_contracts_dff_rec.attribute10;
460 l_tclv_tbl(i).attribute11 := txl_contracts_dff_rec.attribute11;
461 l_tclv_tbl(i).attribute12 := txl_contracts_dff_rec.attribute12;
462 l_tclv_tbl(i).attribute13 := txl_contracts_dff_rec.attribute13;
463 l_tclv_tbl(i).attribute14 := txl_contracts_dff_rec.attribute14;
464 l_tclv_tbl(i).attribute15 := txl_contracts_dff_rec.attribute15;
465 -- This will calculate the amount and generate accounting entries
466 -- Set the tmpl_identify_tbl in parameter
467 l_tmpl_identify_tbl(i).product_id := l_pdt_id;
468 l_tmpl_identify_tbl(i).transaction_type_id := p_try_id;
469 l_tmpl_identify_tbl(i).memo_yn := 'N';
470 l_tmpl_identify_tbl(i).prior_year_yn := 'N';
471 l_tmpl_identify_tbl(i).stream_type_id :=
472 l_template_tbl(i).sty_id;
473 l_tmpl_identify_tbl(i).advance_arrears :=
474 l_template_tbl(i).advance_arrears;
475 l_tmpl_identify_tbl(i).factoring_synd_flag :=
476 l_template_tbl(i).factoring_synd_flag;
477 l_tmpl_identify_tbl(i).investor_code :=
478 l_template_tbl(i).inv_code;
479 l_tmpl_identify_tbl(i).syndication_code :=
480 l_template_tbl(i).syt_code;
481 l_tmpl_identify_tbl(i).factoring_code :=
482 l_template_tbl(i).fac_code;
483
484 EXIT WHEN(i = l_template_tbl.LAST);
485 l_line_number := l_line_number + 1;
486 i := l_template_tbl.NEXT(i);
487 END LOOP;
488 END IF;
489 --create trx contract lines table
490 okl_trx_contracts_pub.create_trx_cntrct_lines(p_api_version => p_api_version,
491 p_init_msg_list => okl_api.g_false,
492 x_return_status => l_return_status,
493 x_msg_count => x_msg_count,
494 x_msg_data => x_msg_data,
495 p_tclv_tbl => l_tclv_tbl,
496 x_tclv_tbl => lx_tclv_tbl);
497
498 IF l_return_status <> okl_api.g_ret_sts_success
499 THEN
500 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
501 okl_api.set_message(p_app_name => g_app_name,
502 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
503 p_token1 => 'TRX_TYPE',
504 p_token1_value => l_trans_meaning);
505
506 END IF;
507 -- Raise exception to rollback to savepoint for this block
508 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
509 THEN
510 RAISE okl_api.g_exception_unexpected_error;
511 ELSIF (l_return_status = okl_api.g_ret_sts_error)
512 THEN
513 RAISE okl_api.g_exception_error;
514 END IF;
515 --setting the input table type to the obtained outout table type
516 l_tclv_tbl := lx_tclv_tbl;
517
518 -- *********************************************
519 -- Populate Accounting Gen
520 -- *********************************************
521
522 -- RMUNJULU 28-APR-04 3596626 Added code to set lp_acc_gen_primary_key_tbl
523 -- for account generator
524
525 okl_acc_call_pvt.okl_populate_acc_gen(
526 p_contract_id => l_khr_id,
527 p_contract_line_id => p_kle_id,
528 x_acc_gen_tbl => lp_acc_gen_primary_key_tbl,
529 x_return_status => l_return_status
530 );
531
532 IF l_return_status <> okl_api.g_ret_sts_success
533 THEN
534 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
535 okl_api.set_message(p_app_name => g_app_name,
536 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
537 p_token1 => 'TRX_TYPE',
538 p_token1_value => l_trans_meaning);
539
540 END IF;
541
542 -- Raise exception to rollback to savepoint for this block
543 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
544 THEN
545 RAISE okl_api.g_exception_unexpected_error;
546 ELSIF (l_return_status = okl_api.g_ret_sts_error)
547 THEN
548 RAISE okl_api.g_exception_error;
549 END IF;
550
551 -- *********************************************
552 -- Accounting Engine Call
553 -- *********************************************
554
555 IF l_tclv_tbl.COUNT <> 0
556 THEN
557 i := l_tclv_tbl.FIRST;
558 LOOP
559 --Assigning the account generator table
560 l_acc_gen_tbl(i).acc_gen_key_tbl := lp_acc_gen_primary_key_tbl;
561 l_acc_gen_tbl(i).source_id := l_tclv_tbl(i).id;
562 --populating dist info tbl
563 l_dist_info_tbl(i).source_id := l_tclv_tbl(i).id;
564 l_dist_info_tbl(i).source_table := 'OKL_TXL_CNTRCT_LNS';
565 l_dist_info_tbl(i).accounting_date := p_sys_date;
566 l_dist_info_tbl(i).gl_reversal_flag := 'N';
567 l_dist_info_tbl(i).post_to_gl := 'Y';
568 l_dist_info_tbl(i).contract_id := l_khr_id;
569 l_dist_info_tbl(i).contract_line_id := p_kle_id;
570 l_dist_info_tbl(i).currency_code := l_currency_code;
571 IF ((p_func_curr_code IS NOT NULL) AND
572 (l_currency_code <> p_func_curr_code))
573 THEN
574 l_dist_info_tbl(i).currency_conversion_rate := l_currency_conversion_rate;
575 l_dist_info_tbl(i).currency_conversion_type := l_currency_conversion_type;
576 l_dist_info_tbl(i).currency_conversion_date := l_currency_conversion_date;
577 END IF;
578 EXIT WHEN i = l_tclv_tbl.LAST;
579 i := l_tclv_tbl.NEXT(i);
580 END LOOP;
581 END IF;
582 l_tcn_id := l_tcnv_rec.id;
583 -- call accounting engine
584 -- This will calculate the amount and generate accounting entries
585 okl_account_dist_pvt.create_accounting_dist(
586 p_api_version => p_api_version,
587 p_init_msg_list => okl_api.g_false,
588 x_return_status => l_return_status,
589 x_msg_count => x_msg_count,
590 x_msg_data => x_msg_data,
591 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
592 p_dist_info_tbl => l_dist_info_tbl,
593 p_ctxt_val_tbl => l_ctxt_tbl,
594 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
595 x_template_tbl => l_template_out_tbl,
596 x_amount_tbl => l_amount_out_tbl,
597 p_trx_header_id => l_tcn_id
598 );
599 IF l_amount_out_tbl.COUNT = 0
600 THEN
601 l_return_status := okl_api.g_ret_sts_error;
602 END IF;
603
604 IF l_return_status <> okl_api.g_ret_sts_success
605 THEN
606 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
607 okl_api.set_message(p_app_name => g_app_name,
608 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
609 p_token1 => 'TRX_TYPE',
610 p_token1_value => l_trans_meaning);
611
612 END IF;
613
614 -- Raise exception to rollback to savepoint for this block
615 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
616 THEN
617 RAISE okl_api.g_exception_unexpected_error;
618 ELSIF (l_return_status = okl_api.g_ret_sts_error)
619 THEN
620 RAISE okl_api.g_exception_error;
621 END IF;
622
623 -- ******************************************************
624 -- Update Trx Contracts with Header and Line Amounts
625 -- ******************************************************
626
627 --call the update trx contract api to update amount per stream type
628
629 l_tcnv_rec.amount := 0;
630 IF (l_tclv_tbl.COUNT) > 0 AND (l_amount_out_tbl.COUNT > 0)
631 THEN
632 k := l_tclv_tbl.FIRST;
633 m := l_amount_out_tbl.FIRST;
634 LOOP
635 l_tclv_tbl(k).amount := 0;
636 IF l_tclv_tbl(k).id = l_amount_out_tbl(m).source_id
637 THEN
638 lx_amount_tbl := l_amount_out_tbl(m).amount_tbl;
639 IF (lx_amount_tbl.COUNT > 0)
640 THEN
641 l := lx_amount_tbl.FIRST;
642 LOOP
643 --update line amount
644 l_tclv_tbl(k).amount := ( l_tclv_tbl(k).amount
645 + nvl(lx_amount_tbl(l),0) );
646 EXIT WHEN(l = lx_amount_tbl.LAST);
647 l := lx_amount_tbl.NEXT(l);
648 END LOOP;
649 END IF ;
650 END IF;
651 --update total header amount
652 l_tcnv_rec.amount :=
653 l_tcnv_rec.amount + l_tclv_tbl(k).amount;
654 EXIT WHEN(k = l_tclv_tbl.LAST) OR (m = l_amount_out_tbl.LAST);
655 k := l_tclv_tbl.NEXT(k);
656 m := l_amount_out_tbl.NEXT(m);
657 END LOOP;
658 END IF;
659 --call the api to update trx contracts hdr and lines
660 okl_trx_contracts_pub.update_trx_contracts(
661 p_api_version => p_api_version,
662 p_init_msg_list => p_init_msg_list,
663 x_return_status => l_return_status,
664 x_msg_count => x_msg_count,
665 x_msg_data => x_msg_data,
666 p_tcnv_rec => l_tcnv_rec,
667 p_tclv_tbl => l_tclv_tbl,
668 x_tcnv_rec => lx_tcnv_rec,
669 x_tclv_tbl => lx_tclv_tbl
670 );
671 --handle exception
672 IF l_return_status <> okl_api.g_ret_sts_success
673 THEN
674 -- Error occurred when creating accounting entries for transaction TRX_TYPE.
675 okl_api.set_message(p_app_name => g_app_name,
676 p_msg_name => 'OKL_AM_ERR_ACC_ENT',
677 p_token1 => 'TRX_TYPE',
678 p_token1_value => l_trans_meaning);
679
680 END IF;
681
682 -- Raise exception to rollback to savepoint for this block
683 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
684 THEN
685 RAISE okl_api.g_exception_unexpected_error;
686 ELSIF (l_return_status = okl_api.g_ret_sts_error)
687 THEN
688 RAISE okl_api.g_exception_error;
689 END IF;
690 --set output parameters of the api.
691 x_total_amount := l_tcnv_rec.amount;
692
693 OKL_MULTIGAAP_ENGINE_PVT.CREATE_SEC_REP_TRX
694 (p_api_version => p_api_version
695 ,p_init_msg_list => p_init_msg_list
696 ,x_return_status => l_return_status
697 ,x_msg_count => x_msg_count
698 ,x_msg_data => x_msg_data
699 ,P_TCNV_REC => lx_tcnv_rec
700 ,P_TCLV_TBL => lx_tclv_tbl
701 ,p_ctxt_val_tbl => l_ctxt_tbl
702 ,p_acc_gen_primary_key_tbl => lp_acc_gen_primary_key_tbl);
703
704 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
705 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
706 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
707 RAISE Okl_Api.G_EXCEPTION_ERROR;
708 END IF;
709
710
711 x_return_status := l_return_status;
712
713 IF l_return_status = okl_api.g_ret_sts_success
714 THEN
715 -- Accounting entries created for transaction type TRX_TYPE.
716 okl_api.set_message(p_app_name => 'OKL',
717 p_msg_name => 'OKL_AM_ACC_ENT_CREATED',
718 p_token1 => 'TRX_TYPE',
719 p_token1_value => l_trans_meaning);
720 END IF;
721
722 EXCEPTION
723 -- RMUNJULU 3596626 Added exception
724 WHEN okl_api.g_exception_error THEN
725 IF prod_id_csr%ISOPEN
726 THEN
727 CLOSE prod_id_csr;
728 END IF;
729 IF trx_contracts_dff_csr%ISOPEN
730 THEN
731 CLOSE trx_contracts_dff_csr;
732 END IF;
733 IF txl_contracts_dff_csr%ISOPEN
734 THEN
735 CLOSE txl_contracts_dff_csr;
736 END IF;
737 IF product_name_csr%ISOPEN
738 THEN
739 CLOSE product_name_csr;
740 END IF;
741
742 x_return_status := okl_api.g_ret_sts_error;
743
744 -- RMUNJULU 3596626 Added exception
745 WHEN okl_api.g_exception_unexpected_error THEN
746 IF prod_id_csr%ISOPEN
747 THEN
748 CLOSE prod_id_csr;
749 END IF;
750 IF trx_contracts_dff_csr%ISOPEN
751 THEN
752 CLOSE trx_contracts_dff_csr;
753 END IF;
754 IF txl_contracts_dff_csr%ISOPEN
755 THEN
756 CLOSE txl_contracts_dff_csr;
757 END IF;
758 IF product_name_csr%ISOPEN
759 THEN
760 CLOSE product_name_csr;
761 END IF;
762
763 x_return_status := okl_api.g_ret_sts_unexp_error;
764
765 WHEN OTHERS THEN
766 IF prod_id_csr%ISOPEN
767 THEN
768 CLOSE prod_id_csr;
769 END IF;
770 IF trx_contracts_dff_csr%ISOPEN
771 THEN
772 CLOSE trx_contracts_dff_csr;
773 END IF;
774 IF txl_contracts_dff_csr%ISOPEN
775 THEN
776 CLOSE txl_contracts_dff_csr;
777 END IF;
778 IF product_name_csr%ISOPEN
779 THEN
780 CLOSE product_name_csr;
781 END IF;
782
783 -- store SQL error message on message stack for caller
784 okl_api.set_message(p_app_name => 'OKL',
785 p_msg_name => g_unexpected_error,
786 p_token1 => g_sqlcode_token,
787 p_token1_value => SQLCODE,
788 p_token2 => g_sqlerrm_token,
789 p_token2_value => SQLERRM);
790
791 x_return_status := okl_api.g_ret_sts_error;
792 END process_accounting_entries;
793
794
795 -- Start of comments
796 --
797 -- Procedure Name : do_cost_retirement
798 -- Description : This procedure performs a full / partial cost retirement for an asset in the TAX Book
799 -- Business Rules :
800 -- Parameters : p_asset_id - asset id of the asset that is to be retired
801 -- p_asset_number - asset number
802 -- p_proceeds_of_sale - amount for which the asset is to be sold
803 -- p_tax_book - tax book
804 -- p_cost - cost retired
805 -- Version : 1.0
806 -- History : SECHAWLA 23-DEC-02 Bug # 2701440 : Created
807 -- SECHAWLA 16-JAN-03 Bug # 2754280
808 -- Changed the app name from OKL to OKC for g_unexpected_error
809 -- SECHAWLA 05-FEB-03 Bug # 2781557
810 -- Moved the logic to check if asset is added in the current open period, from this procedure
811 -- to dispose_asset procedure.
812 -- SECHAWLA 03-JUN-03 Bug # 2999419 : Added a new parameter for retirement prorate convention which
813 -- contains the prorate convention value set in Oracle Assets for a particular asset and book
814 -- SECHAWLA 21-NOV-03 3262519: Added tax owner and delta cost parameter to this procedure
815 -- rmunjulu EDAT Added 2 new parameters p_quote_eff_date and p_quote_accpt_date
816 -- also set some dates with quote eff date and quote accpt date passed
817 -- rmunjulu bug # 4480371
818 -- SECHAWLA 10-FEB-06 5016156 raise error if adjustment transaction fails
819 -- sechawla 13-FEB-08 6765119 - Cost retired in the tax book should be the current cost in FA
820 -- as it exists after the tax book cost adjustment to RV
821 -- End of comments
822
823 PROCEDURE do_cost_retirement( p_api_version IN NUMBER,
824 p_init_msg_list IN VARCHAR2,
825 p_tax_owner IN VARCHAR2,
826 p_delta_cost IN NUMBER,
827 p_asset_id IN NUMBER,
828 p_asset_number IN VARCHAR2,
829 p_proceeds_of_sale IN NUMBER,
830 p_tax_book IN VARCHAR2,
831 p_cost IN NUMBER,
832 p_prorate_convention IN VARCHAR2, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
833 x_fa_trx_date OUT NOCOPY DATE, -- SECHAWLA 15-DEC-04 4028371 : Added this parameter
834 x_msg_count OUT NOCOPY NUMBER,
835 x_msg_data OUT NOCOPY VARCHAR2,
836 x_return_status OUT NOCOPY VARCHAR2,
837 p_quote_eff_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
838 p_quote_accpt_date IN DATE DEFAULT NULL -- rmunjulu EDAT
839 ) IS
840
841
842 l_trans_rec FA_API_TYPES.trans_rec_type;
843 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
844 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
845 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
846 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
847 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
848 l_inv_tbl FA_API_TYPES.inv_tbl_type;
849
850 -- SECHAWLA 21-NOV-03 3262519: New Declarations
851 l_adj_trans_rec FA_API_TYPES.trans_rec_type;
852 l_adj_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
853 l_adj_inv_tbl FA_API_TYPES.inv_tbl_type;
854 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
855 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
856 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
857
858 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
859 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
860 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
861 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
862 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
863
864
865 -- SECHAWLA 21-NOV-03 3262519: end
866
867 --SECHAWLA 10-FEB-06 5016156 added
868 l_adj_error EXCEPTION;
869 l_retire_error EXCEPTION;
870
871 -- sechawla 13-FEB-08 6765119
872 l_current_fa_cost number;
873
874 BEGIN
875
876 -- All the input parameterd to this procedure will definitely have not-null values, as these are required
877 -- columns of okx_asset_liens_v
878
879 x_return_status := okl_api.G_RET_STS_SUCCESS;
880
881 --sechawla 13-FEB-08 6765119
882 l_current_fa_cost := p_cost;
883
884 -- SECHAWLA 21-NOV-03 3262519 : update the tax book with residual value as asset cost
885 --------------------------------------- Adjustments begin-------------------------------------------
886
887 -- p_tax_owner will have a value only if the contract is on direct finance or sales type of lease
888 IF p_tax_owner = 'LESSEE' THEN
889 IF p_delta_cost <> 0 THEN --SECHAWLA 15-DEC-04 Bug # 4028371 : added this condition
890 --update the tax asset book with residual value as asset cost
891
892 l_adj_trans_rec.transaction_subtype := 'AMORTIZED';
893 l_adj_asset_hdr_rec.asset_id := p_asset_id;
894 l_adj_asset_hdr_rec.book_type_code := p_tax_book;
895 l_asset_fin_rec_adj.cost := p_delta_cost;
896
897 -- rmunjulu EDAT -- Set new parameters with dates ------ start +++++++
898 IF p_quote_accpt_date IS NOT NULL
899 AND p_quote_eff_date IS NOT NULL THEN
900
901 l_adj_trans_rec.transaction_date_entered := p_quote_eff_date; -- rmunjulu bug # 4480371 p_quote_accpt_date; -- rmunjulu EDAT
902
903 -- rmunjulu EDAT No need to set below dates
904 --l_asset_fin_rec_adj.deprn_start_date := p_quote_eff_date; -- rmunjulu EDAT
905 --l_asset_fin_rec_adj.prorate_date := p_quote_eff_date; -- rmunjulu EDAT
906
907 END IF;
908 -- rmunjulu EDAT -- Set new parameters with dates ------ end +++++++
909
910 fa_adjustment_pub.do_adjustment(
911 p_api_version => p_api_version,
912 p_init_msg_list => OKC_API.G_FALSE,
913 p_commit => FND_API.G_FALSE,
914 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
915 p_calling_fn => NULL,
916 x_return_status => x_return_status,
917 x_msg_count => x_msg_count,
918 x_msg_data => x_msg_data,
919 px_trans_rec => l_adj_trans_rec,
920 px_asset_hdr_rec => l_adj_asset_hdr_rec,
921 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
922 x_asset_fin_rec_new => l_asset_fin_rec_new,
923 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
924 px_inv_trans_rec => l_inv_trans_rec,
925 px_inv_tbl => l_adj_inv_tbl,
926 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
927 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
928 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
929 p_group_reclass_options_rec => l_group_reclass_options_rec);
930
931 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
932
933 -- Error updating asset cost for asset ASSET_NUMBER in BOOK_CLASS book BOOK.
934 OKC_API.set_message( p_app_name => 'OKL',
935 p_msg_name => 'OKL_AM_ADJ_TRANS_FAILED',
936 p_token1 => 'ASSET_NUMBER',
937 p_token1_value => p_asset_number,
938 p_token2 => 'BOOK_CLASS',
939 p_token2_value => 'tax',
940 p_token3 => 'BOOK',
941 p_token3_value => p_tax_book);
942 RAISE l_adj_error; --SECHAWLA 10-FEB-06 5016156 added
943
944 END IF;
945
946 --sechawla 13-FEB-08 6765119
947 l_current_fa_cost := p_cost+ p_delta_cost;
948
949 --akrangan added for sla populate sources cr start
950 g_tran_tbl_idx := g_tran_tbl_idx + 1;
951 --akrangan fix for bug 6409121 begin
952 --changed l_trans_rec to l_adj_trans_rec which is passed to the FA api as input
953 --corrected the wrong assignment
954 g_trans_id_tbl(g_tran_tbl_idx) := l_adj_trans_rec.transaction_header_id;
955 --akrangan fix for bug 6409121 end
956 --akrangan added for sla populate sources cr end
957 END IF;
958 END IF;
959 --------------------------------------- Adjustments End -------------------------------------------
960
961 -- SECHAWLA 21-NOV-03 3262519 : end new code
962
963
964
965 -------------------------------------- Retirement Begin ------------------------------------------
966 -- transaction information
967 l_trans_rec.transaction_type_code := NULL;
968
969 -- rmunjulu EDAT No need to set below date
970 --IF p_quote_accpt_date IS NOT NULL THEN -- rmunjulu EDAT
971 --l_trans_rec.transaction_date_entered := p_quote_accpt_date; -- rmunjulu EDAT
972 --ELSE
973 l_trans_rec.transaction_date_entered := NULL;
974 --END IF;
975
976 --SECHAWLA 29-DEC-05 3827148 : added
977 l_trans_rec.calling_interface := 'OKL:'||'Asset Disposition:'||'RFA';
978
979
980 -- header information
981 l_asset_hdr_rec.asset_id := p_asset_id;
982 l_asset_hdr_rec.book_type_code := p_tax_book;
983
984 -- retirement information
985
986 -- SECHAWLA 03-JUN-03 2999419 : Use the prorate convention set in Oracle Assets for this asset and book,
987 -- instead of using the constant value MID-MONTH
988 -- l_asset_retire_rec.retirement_prorate_convention := 'MID-MONTH';
989 l_asset_retire_rec.retirement_prorate_convention := p_prorate_convention;
990
991 IF p_quote_eff_date IS NOT NULL THEN -- rmunjulu EDAT
992 l_asset_retire_rec.date_retired := p_quote_eff_date; -- rmunjulu EDAT
993 ELSE
994 l_asset_retire_rec.date_retired := NULL;
995 END IF;
996
997 --sechawla 13-FEB-08 6765119
998 -- l_asset_retire_rec.cost_retired := p_cost;
999
1000 -- sechawla 13-FEB-08 6765119
1001 l_asset_retire_rec.cost_retired := l_current_fa_cost;
1002
1003
1004
1005 l_asset_retire_rec.proceeds_of_sale := p_proceeds_of_sale;
1006 l_asset_retire_rec.cost_of_removal := 0;
1007 l_asset_retire_rec.retirement_type_code := 'SALE';
1008 l_asset_retire_rec.trade_in_asset_id := NULL;
1009 l_asset_retire_rec.calculate_gain_loss := FND_API.G_FALSE;
1010 --SECHAWLA 13-JAN-03 Bug # 2701440 : calculate gain and loss should be set to TRUE if multiple partial retirements are performed on the same asset in the same period
1011 --l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
1012
1013 -- p_user_id must be properly set to run calc gain/loss
1014 --fnd_profile.put('USER_ID',p_user_id);
1015
1016 l_asset_dist_tbl.DELETE;
1017
1018 FA_RETIREMENT_PUB.do_retirement( p_api_version => p_api_version,
1019 p_init_msg_list => OKC_API.G_FALSE,
1020 p_commit => FND_API.G_FALSE,
1021 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1022 p_calling_fn => NULL,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data,
1026 px_trans_rec => l_trans_rec,
1027 px_dist_trans_rec => l_dist_trans_rec,
1028 px_asset_hdr_rec => l_asset_hdr_rec,
1029 px_asset_retire_rec => l_asset_retire_rec,
1030 p_asset_dist_tbl => l_asset_dist_tbl,
1031 p_subcomp_tbl => l_subcomp_tbl,
1032 p_inv_tbl => l_inv_tbl);
1033
1034
1035 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1036
1037 -- Error retiring asset ASSET_NUMBER in book BOOK. Retirement transaction was not performed for this asset in Fixed Assets.
1038 OKC_API.set_message( p_app_name => 'OKL',
1039 p_msg_name => 'OKL_AM_RET_TRANS_FAILED',
1040 p_token1 => 'ASSET_NUMBER',
1041 p_token1_value => p_asset_number,
1042 p_token2 => 'BOOK_CLASS',
1043 p_token2_value => 'tax',
1044 p_token3 => 'BOOK',
1045 p_token3_value => p_tax_book);
1046 RAISE l_retire_error; --SECHAWLA 10-FEB-06 5016156 added
1047 ELSE -- 15-DEC-04 SECHAWLA 4028371 added else section
1048 x_fa_trx_date := l_trans_rec.transaction_date_entered;
1049 END IF;
1050 --akrangan added for sla populate sources cr start
1051 g_tran_tbl_idx := g_tran_tbl_idx + 1;
1052 g_trans_id_tbl(g_tran_tbl_idx) := l_trans_rec.transaction_header_id;
1053 --akrangan added for sla populate sources cr end
1054
1055
1056 -- x_return_status of the above procedure call becomes the x_return_status of the current procedure
1057 -- which is then handled in the calling procedure dispose_asset()
1058
1059 --------------------------------------- Retirement End ----------------------------------------------
1060
1061 EXCEPTION
1062 --SECHAWLA 10-FEB-06 5016156
1063 WHEN l_adj_error THEN
1064 NULL;
1065 WHEN l_retire_error THEN
1066 NULL;
1067
1068 WHEN OTHERS THEN
1069
1070 -- unexpected error
1071 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1072 OKL_API.set_message(
1073 p_app_name => 'OKC',
1074 p_msg_name => g_unexpected_error,
1075 p_token1 => g_sqlcode_token,
1076 p_token1_value => sqlcode,
1077 p_token2 => g_sqlerrm_token,
1078 p_token2_value => sqlerrm);
1079 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1080
1081 END do_cost_retirement;
1082
1083
1084 -- Start of comments
1085 --
1086 -- Procedure Name : do_full_units_retirement
1087 -- Description : This procedure retires an asset in FA if period_of_addition = 'N'
1088 -- This procedure is called only for the corporate book retirement
1089 -- Business Rules :
1090 -- Parameters : p_asset_id - asset id of the asset that is to be retired
1091 -- p_asset_number - asset number
1092 -- p_proceeds_of_sale - amount for which the asset is to be sold
1093 -- p_book_type_code - corporate / tax book
1094 -- p_cost - cost retired
1095 -- Version : 1.0
1096 -- History : SECHAWLA 10-DEC-02 Bug # 2701440
1097 -- Changed the parameter name p_corporate_book to p_book_type_code
1098 -- SECHAWLA 23-DEC-02 Bug # 2701440
1099 -- Changed the procedure name from do_full_retirement to do_full_units_retirement
1100 -- Added BOOK_CLASS token in messages.
1101 -- SECHAWLA 16-JAN-03 Bug # 2754280
1102 -- Changed the app name from OKL to OKC for g_unexpected_error
1103 -- SECHAWLA 05-FEB-03 Bug # 2781557
1104 -- Moved the logic to check if asset is added in the current open period, from this procedure
1105 -- to dispose_asset procedure.
1106 -- SECHAWLA 03-JUN-03 2999419: Added a new parameter for retirement prorate convention which
1107 -- contains the prorate convention value set in Oracle Assets for a particular asset and book
1108 -- SECHAWLA 21-NOV-03 3262519: Added tax owner and delta cost parameter to this procedure
1109 -- rmunjulu EDAT Added 2 new parameters p_quote_eff_date and p_quote_accpt_date
1110 -- also set some dates with quote eff date and quote accpt date passed
1111 -- rmunjulu bug # 4480371
1112 -- SECHAWLA 10-FEB-06 5016156 raise error if adjusment transaction fails
1113
1114 -- End of comments
1115
1116 PROCEDURE do_full_units_retirement( p_api_version IN NUMBER,
1117 p_init_msg_list IN VARCHAR2,
1118 p_tax_owner IN VARCHAR2,
1119 p_delta_cost IN NUMBER,
1120 p_asset_id IN NUMBER,
1121 p_asset_number IN VARCHAR2,
1122 p_proceeds_of_sale IN NUMBER,
1123 -- SECHAWLA 10-DEC-02 Bug # 2701440
1124 --p_corporate_book IN VARCHAR2,
1125 p_book_type_code IN VARCHAR2,
1126 p_units IN NUMBER,
1127 p_prorate_convention IN VARCHAR2, -- SECHAWLA 03-JUN-03 2999419: Added this parameter
1128 x_fa_trx_date OUT NOCOPY DATE, -- SECHAWLA 15-DEC-04 4028371 : Added this parameter
1129 x_msg_count OUT NOCOPY NUMBER,
1130 x_msg_data OUT NOCOPY VARCHAR2,
1131 x_return_status OUT NOCOPY VARCHAR2,
1132 p_quote_eff_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
1133 p_quote_accpt_date IN DATE DEFAULT NULL -- rmunjulu EDAT
1134
1135 ) IS
1136
1137
1138 l_trans_rec FA_API_TYPES.trans_rec_type;
1139 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1140 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
1141 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
1142 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1143 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
1144 l_inv_tbl FA_API_TYPES.inv_tbl_type;
1145
1146 -- SECHAWLA 21-NOV-03 3262519: New Declarations
1147 l_adj_trans_rec FA_API_TYPES.trans_rec_type;
1148 l_adj_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1149 l_adj_inv_tbl FA_API_TYPES.inv_tbl_type;
1150 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
1151 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
1152 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
1153
1154 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
1155 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
1156 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
1157 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
1158 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
1159 -- SECHAWLA 21-NOV-03 3262519: end
1160
1161 --SECHAWLA 10-FEB-06 5016156
1162 l_adj_error EXCEPTION;
1163 l_retire_error EXCEPTION;
1164
1165 BEGIN
1166
1167 -- All the input parameterd to this procedure will definitely have not-null values, as these are required
1168 -- columns of okx_asset_liens_v
1169
1170 x_return_status := okl_api.G_RET_STS_SUCCESS;
1171
1172 -- SECHAWLA 21-NOV-03 3262519 : update the corporate book with residual value as asset cost
1173 --------------------------------------- Adjustments begin-------------------------------------------
1174
1175 -- p_tax_owner will have a value only if the contract is on direct finance or sales type of lease
1176 IF p_tax_owner IN ('LESSOR','LESSEE') THEN
1177 IF p_delta_cost <> 0 THEN --SECHAWLA 15-DEC-04 Bug # 4028371 : added this condition
1178 --update the tax asset book with residual value as asset cost
1179
1180 l_adj_trans_rec.transaction_subtype := 'AMORTIZED';
1181 l_adj_asset_hdr_rec.asset_id := p_asset_id;
1182 l_adj_asset_hdr_rec.book_type_code := p_book_type_code;
1183 l_asset_fin_rec_adj.cost := p_delta_cost;
1184
1185 -- rmunjulu EDAT -- Set new parameters with dates ------ start +++++++
1186 IF p_quote_accpt_date IS NOT NULL
1187 AND p_quote_eff_date IS NOT NULL THEN
1188
1189 l_adj_trans_rec.transaction_date_entered := p_quote_eff_date; -- rmunjulu bug # 4480371 p_quote_accpt_date; -- rmunjulu EDAT
1190
1191 -- rmunjulu EDAT No need to set below dates
1192 --l_asset_fin_rec_adj.deprn_start_date := p_quote_eff_date; -- rmunjulu EDAT
1193 --l_asset_fin_rec_adj.prorate_date := p_quote_eff_date; -- rmunjulu EDAT
1194
1195 END IF;
1196 -- rmunjulu EDAT -- Set new parameters with dates ------ end +++++++
1197
1198 fa_adjustment_pub.do_adjustment(
1199 p_api_version => p_api_version,
1200 p_init_msg_list => OKC_API.G_FALSE,
1201 p_commit => FND_API.G_FALSE,
1202 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1203 p_calling_fn => NULL,
1204 x_return_status => x_return_status,
1205 x_msg_count => x_msg_count,
1206 x_msg_data => x_msg_data,
1207 px_trans_rec => l_adj_trans_rec,
1208 px_asset_hdr_rec => l_adj_asset_hdr_rec,
1209 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1210 x_asset_fin_rec_new => l_asset_fin_rec_new,
1211 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1212 px_inv_trans_rec => l_inv_trans_rec,
1213 px_inv_tbl => l_adj_inv_tbl,
1214 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1215 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1216 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1217 p_group_reclass_options_rec => l_group_reclass_options_rec);
1218
1219 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1220
1221 -- Error updating asset cost for asset ASSET_NUMBER in BOOK_CLASS book BOOK.
1222 OKC_API.set_message( p_app_name => 'OKL',
1223 p_msg_name => 'OKL_AM_ADJ_TRANS_FAILED',
1224 p_token1 => 'ASSET_NUMBER',
1225 p_token1_value => p_asset_number,
1226 p_token2 => 'BOOK_CLASS',
1227 p_token2_value => 'corporate',
1228 p_token3 => 'BOOK',
1229 p_token3_value => p_book_type_code);
1230 RAISE l_adj_error; --SECHAWLA 10-FEB-06 5016156
1231 END IF;
1232 --akrangan added for sla populate sources cr start
1233 g_tran_tbl_idx := g_tran_tbl_idx + 1;
1234 --akrangan fix for bug 6409121 begin
1235 --changed l_trans_rec to l_adj_trans_rec which is passed to the FA api as input
1236 --corrected the wrong assignment
1237 g_trans_id_tbl(g_tran_tbl_idx) := l_adj_trans_rec.transaction_header_id;
1238 --akrangan fix for bug 6409121 end
1239 --akrangan added for sla populate sources cr end
1240 END IF;
1241 END IF;
1242 --------------------------------------- Adjustments End -------------------------------------------
1243 -- SECHAWLA 21-NOV-03 3262519 : end new code
1244
1245
1246 --------------------------------------- Retirements begin -------------------------------------------
1247 -- transaction information
1248 l_trans_rec.transaction_type_code := NULL;
1249
1250 -- rmunjulu EDAT No need to set below date
1251 --IF p_quote_accpt_date IS NOT NULL THEN -- rmunjulu EDAT
1252 --l_trans_rec.transaction_date_entered := p_quote_accpt_date; -- rmunjulu EDAT
1253 --ELSE
1254 l_trans_rec.transaction_date_entered := NULL;
1255 --END IF;
1256
1257 --SECHAWLA 29-DEC-05 3827148 : added
1258 l_trans_rec.calling_interface := 'OKL:'||'Asset Disposition:'||'RFA';
1259
1260
1261 -- header information
1262 l_asset_hdr_rec.asset_id := p_asset_id;
1263 l_asset_hdr_rec.book_type_code := p_book_type_code; -- SECHAWLA 10-DEC-02 Bug # 2701440: changed the parameter name
1264
1265 -- retirement information
1266
1267 -- SECHAWLA 03-JUN-03 2999419 : Use the prorate convention set in Oracle Assets for this asset and book,
1268 -- instead of using the hard coded value of MID-MONTH
1269 -- l_asset_retire_rec.retirement_prorate_convention := 'MID-MONTH';
1270 l_asset_retire_rec.retirement_prorate_convention := p_prorate_convention;
1271
1272
1273 IF p_quote_eff_date IS NOT NULL THEN -- rmunjulu EDAT
1274 l_asset_retire_rec.date_retired := p_quote_eff_date; -- rmunjulu EDAT
1275 ELSE
1276 l_asset_retire_rec.date_retired := NULL;
1277 END IF;
1278
1279 -- l_asset_retire_rec.cost_retired := p_cost;
1280 --l_asset_retire_rec.units_retired := NULL;
1281
1282 l_asset_retire_rec.units_retired := p_units;
1283 l_asset_retire_rec.proceeds_of_sale := p_proceeds_of_sale;
1284 l_asset_retire_rec.cost_of_removal := 0;
1285 l_asset_retire_rec.retirement_type_code := 'SALE';
1286 l_asset_retire_rec.trade_in_asset_id := NULL;
1287 --akrangan changed calculate_gain_loss flag from FALSE to TRUE
1288 --to address FA enhancement beign
1289 l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
1290 --akrangan changed ends here
1291 --SECHAWLA 13-JAN-03 Bug # 2701440 : calculate gain and loss should be set to TRUE if multiple partial retirements are performed on the same asset in the same period
1292 --l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
1293
1294 -- p_user_id must be properly set to run calc gain/loss
1295 --fnd_profile.put('USER_ID',p_user_id);
1296
1297 l_asset_dist_tbl.DELETE;
1298
1299
1300
1301 FA_RETIREMENT_PUB.do_retirement( p_api_version => p_api_version,
1302 p_init_msg_list => OKC_API.G_FALSE,
1303 p_commit => FND_API.G_FALSE,
1304 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1305 p_calling_fn => NULL,
1306 x_return_status => x_return_status,
1307 x_msg_count => x_msg_count,
1308 x_msg_data => x_msg_data,
1309 px_trans_rec => l_trans_rec,
1310 px_dist_trans_rec => l_dist_trans_rec,
1311 px_asset_hdr_rec => l_asset_hdr_rec,
1312 px_asset_retire_rec => l_asset_retire_rec,
1313 p_asset_dist_tbl => l_asset_dist_tbl,
1314 p_subcomp_tbl => l_subcomp_tbl,
1315 p_inv_tbl => l_inv_tbl);
1316
1317 -- SECHAWLA Bug # 2701440 : Added this message
1318 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1319 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added BOOK_CLASS token
1320 -- Error retiring asset ASSET_NUMBER in book BOOK. Retirement transaction was not performed for this asset in Fixed Assets.
1321 OKC_API.set_message( p_app_name => 'OKL',
1322 p_msg_name => 'OKL_AM_RET_TRANS_FAILED',
1323 p_token1 => 'ASSET_NUMBER',
1324 p_token1_value => p_asset_number,
1325 p_token2 => 'BOOK_CLASS',
1326 p_token2_value => 'corporate',
1327 p_token3 => 'BOOK',
1328 p_token3_value => p_book_type_code);
1329 RAISE l_retire_error; --SECHAWLA 10-FEB-06 5016156
1330 ELSE -- 15-DEC-04 SECHAWLA 4028371 added else section
1331 x_fa_trx_date := l_trans_rec.transaction_date_entered;
1332 END IF;
1333 --akrangan added for sla populate sources cr start
1334 g_tran_tbl_idx := g_tran_tbl_idx + 1;
1335 g_trans_id_tbl(g_tran_tbl_idx) := l_trans_rec.transaction_header_id;
1336 --akrangan added for sla populate sources cr end
1337
1338 -- x_return_status of the above procedure call becomes the x_return_status of the current procedure
1339 -- which is then handled in the calling procedure dispose_asset()
1340
1341 --------------------------------------- Retirements end -------------------------------------------
1342 EXCEPTION
1343 --SECHAWLA 10-FEB-06 5016156
1344 WHEN l_adj_error THEN
1345 NULL;
1346 WHEN l_retire_error THEN
1347 NULL;
1348
1349 WHEN OTHERS THEN
1350
1351 -- unexpected error
1352 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1353 OKL_API.set_message(
1354 p_app_name => 'OKC',
1355 p_msg_name => g_unexpected_error,
1356 p_token1 => g_sqlcode_token,
1357 p_token1_value => sqlcode,
1358 p_token2 => g_sqlerrm_token,
1359 p_token2_value => sqlerrm);
1360 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1361
1362 END do_full_units_retirement;
1363
1364 -- Start of comments
1365 --
1366 -- Procedure Name : do_partial_retirement
1367 -- Description : This procedure is used to partially retire an asset in FA if period_of_addition = 'N'
1368 -- This procedure is called only for corporate book retirements
1369 -- Business Rules :
1370 -- Parameters : p_asset_id - asset id of the asset that is to be retired
1371 -- p_asset_number - asset number
1372 -- p_proceeds_of_sale - amount for which the asset is to be sold
1373 -- p_book_type_code - corporate / tax book
1374 -- p_total_quantity - units retired
1375 -- p_dist_tbl - table of distribution Ids that are to be retired
1376 -- Version : 1.0
1377 -- History : SECHAWLA 10-DEC-02 Bug # 2701440
1378 -- Changed the parameter name p_corporate_book to p_book_type_code
1379 -- SECHAWLA 23-DEC-02 Bug # 2701440
1380 -- Changed the procedure name from do_partial_retirement to do_partial_units_retirement
1381 -- Added BOOK_CLASS token in messages.
1382 -- SECHAWLA 16-JAN-03 Bug # 2754280
1383 -- Changed the app name from OKL to OKC for g_unexpected_error
1384 -- SECHAWLA 05-FEB-03 Bug # 2781557
1385 -- Moved the logic to check if asset is added in the current open period, from this procedure
1386 -- to dispose_asset procedure.
1387 -- SECHAWLA 03-JUN-03 Bug # 2999419: Added a new parameter for retirement prorate convention which
1388 -- contains the prorate convention value set in Oracle Assets for a particular asset and book
1389 -- SECHAWLA 21-NOV-03 3262519: Added tax owner and delta cost parameter to this procedure
1390 -- rmunjulu EDAT Added 2 new parameters p_quote_eff_date and p_quote_accpt_date
1391 -- also set some dates with quote eff date and quote accpt date passed
1392 -- rmunjulu bug # 4480371
1393 -- SECHAWLA 10-FEB-06 5016156 raise error if adjustment transaction fails
1394
1395 -- End of comments
1396 PROCEDURE do_partial_units_retirement(
1397 p_api_version IN NUMBER,
1398 p_init_msg_list IN VARCHAR2,
1399 p_tax_owner IN VARCHAR2,
1400 p_delta_cost IN NUMBER,
1401 p_asset_id IN NUMBER,
1402 p_asset_number IN VARCHAR2,
1403 p_proceeds_of_sale IN NUMBER,
1404 -- p_corporate_book IN VARCHAR2, --SECHAWLA Bug # 2701440
1405 p_book_type_code IN VARCHAR2,
1406 p_total_quantity IN NUMBER,
1407 p_dist_tbl IN asset_dist_tbl,
1408 p_prorate_convention IN VARCHAR2, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
1409 x_fa_trx_date OUT NOCOPY DATE, -- SECHAWLA 15-DEC-04 4028371 : Added this parameter
1410 x_msg_count OUT NOCOPY NUMBER,
1411 x_msg_data OUT NOCOPY VARCHAR2,
1412 x_return_status OUT NOCOPY VARCHAR2,
1413 p_quote_eff_date IN DATE DEFAULT NULL, -- rmunjulu EDAT
1414 p_quote_accpt_date IN DATE DEFAULT NULL -- rmunjulu EDAT
1415 ) IS
1416
1417
1418
1419 l_trans_rec FA_API_TYPES.trans_rec_type;
1420 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1421 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
1422 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
1423 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1424 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
1425 l_inv_tbl FA_API_TYPES.inv_tbl_type;
1426 i NUMBER;
1427
1428 -- SECHAWLA 21-NOV-03 3262519: New Declarations
1429 l_adj_trans_rec FA_API_TYPES.trans_rec_type;
1430 l_adj_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1431 l_adj_inv_tbl FA_API_TYPES.inv_tbl_type;
1432 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
1433 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
1434 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
1435
1436 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
1437 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
1438 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
1439 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
1440 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
1441 -- SECHAWLA 21-NOV-03 3262519: end
1442
1443 --SECHAWLA 10-FEB-06 5016156
1444 l_adj_error EXCEPTION;
1445 l_retire_error EXCEPTION;
1446
1447 BEGIN
1448
1449 -- All the input parameterd to this procedure will definitely have not-null values, as these are required
1450 -- columns of okx_asset_liens_v
1451
1452 x_return_status := okl_api.G_RET_STS_SUCCESS;
1453
1454 -- SECHAWLA 21-NOV-03 3262519 : update the corporate book with residual value as asset cost
1455 --------------------------------------- Adjustments begin-------------------------------------------
1456
1457 -- p_tax_owner will have a value only if the contract is on direct finance or sales type of lease
1458 IF p_tax_owner IN ('LESSOR','LESSEE') THEN
1459 IF p_delta_cost <> 0 THEN --SECHAWLA 15-DEC-04 Bug # 4028371 : added this condition
1460 --update the tax asset book with residual value as asset cost
1461
1462 l_adj_trans_rec.transaction_subtype := 'AMORTIZED';
1463 l_adj_asset_hdr_rec.asset_id := p_asset_id;
1464 l_adj_asset_hdr_rec.book_type_code := p_book_type_code;
1465 l_asset_fin_rec_adj.cost := p_delta_cost;
1466
1467 -- rmunjulu EDAT -- Set new parameters with dates ------ start +++++++
1468 IF p_quote_accpt_date IS NOT NULL
1469 AND p_quote_eff_date IS NOT NULL THEN
1470
1471 l_adj_trans_rec.transaction_date_entered := p_quote_eff_date; -- rmunjulu bug # 4480371 p_quote_accpt_date; -- rmunjulu EDAT
1472
1473 -- rmunjulu EDAT No need to set below dates
1474 --l_asset_fin_rec_adj.deprn_start_date := p_quote_eff_date; -- rmunjulu EDAT
1475 --l_asset_fin_rec_adj.prorate_date := p_quote_eff_date; -- rmunjulu EDAT
1476
1477 END IF;
1478 -- rmunjulu EDAT -- Set new parameters with dates ------ end +++++++
1479
1480 fa_adjustment_pub.do_adjustment(
1481 p_api_version => p_api_version,
1482 p_init_msg_list => OKC_API.G_FALSE,
1483 p_commit => FND_API.G_FALSE,
1484 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1485 p_calling_fn => NULL,
1486 x_return_status => x_return_status,
1487 x_msg_count => x_msg_count,
1488 x_msg_data => x_msg_data,
1489 px_trans_rec => l_adj_trans_rec,
1490 px_asset_hdr_rec => l_adj_asset_hdr_rec,
1491 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1492 x_asset_fin_rec_new => l_asset_fin_rec_new,
1493 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1494 px_inv_trans_rec => l_inv_trans_rec,
1495 px_inv_tbl => l_adj_inv_tbl,
1496 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1497 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1498 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1499 p_group_reclass_options_rec => l_group_reclass_options_rec);
1500
1501 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1502
1503 -- Error updating asset cost for asset ASSET_NUMBER in BOOK_CLASS book BOOK.
1504 OKC_API.set_message( p_app_name => 'OKL',
1505 p_msg_name => 'OKL_AM_ADJ_TRANS_FAILED',
1506 p_token1 => 'ASSET_NUMBER',
1507 p_token1_value => p_asset_number,
1508 p_token2 => 'BOOK_CLASS',
1509 p_token2_value => 'corporate',
1510 p_token3 => 'BOOK',
1511 p_token3_value => p_book_type_code);
1512 RAISE l_adj_error; --SECHAWLA 10-FEB-06 5016156
1513 END IF;
1514 --akrangan added for sla populate sources cr start
1515 g_tran_tbl_idx := g_tran_tbl_idx + 1;
1516 --akrangan fix for bug 6409121 begin
1517 --changed l_trans_rec to l_adj_trans_rec which is passed to the FA api as input
1518 --corrected the wrong assignment
1519 g_trans_id_tbl(g_tran_tbl_idx) := l_adj_trans_rec.transaction_header_id;
1520 --akrangan fix for bug 6409121 end
1521 --akrangan added for sla populate sources cr end
1522 END IF;
1523 END IF;
1524 --------------------------------------- Adjustments End -------------------------------------------
1525 -- SECHAWLA 21-NOV-03 3262519 : end new code
1526
1527
1528 --------------------------------------- Retirement Begin ------------------------------------------
1529 -- transaction information
1530 l_trans_rec.transaction_type_code := NULL;
1531
1532 -- rmunjulu EDAT No need to set below date
1533 --IF p_quote_accpt_date IS NOT NULL THEN -- rmunjulu EDAT
1534 --l_trans_rec.transaction_date_entered := p_quote_accpt_date; -- rmunjulu EDAT
1535 --ELSE
1536 l_trans_rec.transaction_date_entered := NULL;
1537 --END IF;
1538
1539 --SECHAWLA 29-DEC-05 3827148 : added
1540 l_trans_rec.calling_interface := 'OKL:'||'Asset Disposition:'||'RFA';
1541
1542
1543 -- header information
1544 l_asset_hdr_rec.asset_id := p_asset_id;
1545 l_asset_hdr_rec.book_type_code := p_book_type_code; --SECHAWLA Bug # 2701440 : changed the parameter name
1546
1547 -- retirement information
1548
1549 -- SECHAWLA 03-JUN-03 2999419: Use the prorate convention set in Oracle Assets for this asset and book,
1550 -- instead of using the hard coded value of MID-MONTH
1551 -- l_asset_retire_rec.retirement_prorate_convention := 'MID-MONTH';
1552 l_asset_retire_rec.retirement_prorate_convention := p_prorate_convention;
1553
1554
1555 IF p_quote_eff_date IS NOT NULL THEN -- rmunjulu EDAT
1556 l_asset_retire_rec.date_retired := p_quote_eff_date; -- rmunjulu EDAT
1557 ELSE
1558 l_asset_retire_rec.date_retired := NULL;
1559 END IF;
1560 l_asset_retire_rec.units_retired := p_total_quantity;
1561 -- l_asset_retire_rec.cost_retired := p_cost;
1562 l_asset_retire_rec.proceeds_of_sale := p_proceeds_of_sale;
1563 l_asset_retire_rec.cost_of_removal := 0;
1564 l_asset_retire_rec.retirement_type_code := 'SALE';
1565 l_asset_retire_rec.trade_in_asset_id := NULL;
1566 --akrangan changed calculate_gain_loss flag from FALSE to TRUE
1567 --to address FA enhancement beign
1568 l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
1569 --akrangan changed ends here
1570 --SECHAWLA 13-JAN-03 Bug # 2701440 : calculate gain and loss should be set to TRUE if multiple partial retirements are performed on the same asset in the same period
1571 --l_asset_retire_rec.calculate_gain_loss := FND_API.G_TRUE;
1572
1573 -- p_user_id must be properly set to run calc gain/loss
1574 --fnd_profile.put('USER_ID',p_user_id);
1575
1576 l_asset_dist_tbl.DELETE;
1577
1578
1579
1580 IF p_dist_tbl.COUNT > 0 THEN
1581 i := p_dist_tbl.FIRST ;
1582 -- Loop thru all the distributions that are to be retired and assign them to l_asset_dist_tbl
1583 LOOP
1584 l_asset_dist_tbl(i+1).distribution_id := p_dist_tbl(i).p_distribution_id;
1585 l_asset_dist_tbl(i+1).transaction_units := -(p_dist_tbl(i).p_units_assigned);
1586 l_asset_dist_tbl(i+1).units_assigned := NULL;
1587 l_asset_dist_tbl(i+1).assigned_to := NULL;
1588 l_asset_dist_tbl(i+1).expense_ccid := NULL;
1589 l_asset_dist_tbl(i+1).location_ccid := NULL;
1590
1591 EXIT WHEN (i = p_dist_tbl.LAST);
1592 i := p_dist_tbl.NEXT(i);
1593 END LOOP;
1594 END IF;
1595
1596
1597 FA_RETIREMENT_PUB.do_retirement( p_api_version => p_api_version,
1598 p_init_msg_list => OKC_API.G_FALSE,
1599 p_commit => FND_API.G_FALSE,
1600 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1601 p_calling_fn => NULL,
1602 x_return_status => x_return_status,
1603 x_msg_count => x_msg_count,
1604 x_msg_data => x_msg_data,
1605 px_trans_rec => l_trans_rec,
1606 px_dist_trans_rec => l_dist_trans_rec,
1607 px_asset_hdr_rec => l_asset_hdr_rec,
1608 px_asset_retire_rec => l_asset_retire_rec,
1609 p_asset_dist_tbl => l_asset_dist_tbl,
1610 p_subcomp_tbl => l_subcomp_tbl,
1611 p_inv_tbl => l_inv_tbl);
1612
1613 -- SECHAWLA Bug # 2701440 : Added this message
1614 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1615
1616 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added BOOK_CLASS token
1617 -- Error retiring asset ASSET_NUMBER in book BOOK. Retirement transaction was not performed for this asset in Fixed Assets.
1618 OKC_API.set_message( p_app_name => 'OKL',
1619 p_msg_name => 'OKL_AM_RET_TRANS_FAILED',
1620 p_token1 => 'ASSET_NUMBER',
1621 p_token1_value => p_asset_number,
1622 p_token2 => 'BOOK_CLASS',
1623 p_token2_value => 'corporate',
1624 p_token3 => 'BOOK',
1625 p_token3_value => p_book_type_code);
1626 RAISE l_retire_error; --SECHAWLA 10-FEB-06 5016156
1627 ELSE -- 15-DEC-04 SECHAWLA 4028371 added else section
1628 x_fa_trx_date := l_trans_rec.transaction_date_entered;
1629 END IF;
1630 --akrangan added for sla populate sources cr start
1631 g_tran_tbl_idx := g_tran_tbl_idx + 1;
1632 g_trans_id_tbl(g_tran_tbl_idx) := l_trans_rec.transaction_header_id;
1633 --akrangan added for sla populate sources cr end
1634
1635
1636 -- x_return_status of the above procedure call becomes the x_return_status of the current procedure
1637 -- which is then handled in the calling procedure dispose_asset()
1638
1639 --------------------------------------- Retirements end ------------------------------------------
1640 EXCEPTION
1641 --SECHAWLA 10-FEB-06 5016156
1642 WHEN l_adj_error THEN
1643 NULL;
1644 WHEN l_retire_error THEN
1645 NULL;
1646
1647 WHEN OTHERS THEN
1648
1649 -- unexpected error
1650 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
1651 OKL_API.set_message(
1652 p_app_name => 'OKC',
1653 p_msg_name => g_unexpected_error,
1654 p_token1 => g_sqlcode_token,
1655 p_token1_value => sqlcode,
1656 p_token2 => g_sqlerrm_token,
1657 p_token2_value => sqlerrm);
1658 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1659
1660 END do_partial_units_retirement;
1661
1662
1663 -- Start of comments
1664 --
1665 -- Procedure Name : dispose_asset
1666 -- Description : This procedure is used to retire an asset in FA. It checks if the asset is to be fully or
1667 -- partially retired , based upon the p_quantity parameter and then calls the appropriate routine to
1668 -- retire the asset. It then stores the disposition transactions in OKL tables, calls accounting
1669 -- engine and then finally cancels all pending transactions in OKL tables for this asset.
1670 -- Business Rules :
1671 -- Parameters : p_financial_asset_id - Financial asset id(kle_id) of the asset that is to be retired
1672 -- p_quantity - units that are to be retired (optional)
1673 -- p_proceeds_of_sale - amount for which the asset is to be sold
1674 --
1675 -- Version : 1.0
1676 -- History : SECHAWLA 10-DEC-02 Bug # 2701440
1677 -- Modified CURSOR l_okxassetlines_csr to select all the tax books that an asset belongs to,
1678 -- in addition to the Fixed Asset Information.
1679 -- SECHAWLA 23-DEC-02 Bug # 2701440
1680 -- Modified logic to perform cost retirement instead of unit retirement for tax books
1681 -- SECHAWLA 03-JAN-03 Bug # 2683876
1682 -- Modified logic to send currency code while creating/updating amounts columns in txl assets
1683 -- SECHAWLA 13-JAN-03 Bug # 2701440 Modified logic to perform :
1684 -- 1) full tax book retirement when the corporate book gets fully retired. This is to
1685 -- take care of the scenario where tax book cost is more than the corporate book cost
1686 -- 2) full tax book retirement when the corp book is not fully retired but tax book does not
1687 -- have enough cost. This takes care of the scenario where tax book cost is less than the corp book cost
1688 -- SECHAWLA 05-FEB-03 Bug # 2781557
1689 -- Moved the logic to check if the asset was added in the current open period, from individual cost and unit
1690 -- retirement procedures to this procedure.
1691 -- SECHAWLA 11-MAR-03
1692 -- assumed 0 amount for proceeds of sale, if null. Allowed negative values for proceeds of sale
1693 -- SECHAWLA 03-JUN-03 2999419: Use the retirement prorate convention set in Oracle
1694 -- Assets for a particular asset and book, instead of using the constant value "MID-MONTH"
1695 -- RMUNJULU 11-SEP-03 3061751 Added code for SERVICE_K_INTEGRATION
1696 -- SECHAWLA 21-NOV-03 3262519 Update the asset cost with residual value, for DF and Sales lease,
1697 -- before retiring the asset
1698 -- rmunjulu EDAT
1699 -- Added 2 new parameters and Changed code to
1700 -- 1. set date trn occured as quote eff date
1701 -- 2. set disposal accounting date as quote acceptance date
1702 -- 3. set service k notification date as quote eff date
1703 -- 4. expire item in IB date as quote eff date
1704 -- 5. set trn date as quote eff date to full units retirement
1705 -- 6. set trn date as quote eff date to partial untis retirement
1706 -- 7. set trn date as quote eff date to cost retirement
1707 -- rmunjulu EDAT 23-Nov-04 Set back IB End date as sysdate
1708 -- rmunjulu EDAT 10-Jan-05 Pass quote eff date and quote accpt date to adjust/retire proc
1709 -- girao 18-Jan-2005 4106216 NVL the residual value in l_linesfullv_csr
1710 -- SECHAWLA 10-FEB-06 5016156 In case of termination w/o purchase, asset cost should
1711 -- be updated with NIV (not RV), through Off-lease transactions
1712 -- rbruno 04-sep-07 bug 5436987 Adjustment transaction should have amount in functional currency
1713 --
1714 --
1715 -- End of comments
1716 PROCEDURE dispose_asset ( p_api_version IN NUMBER,
1717 p_init_msg_list IN VARCHAR2,
1718 x_return_status OUT NOCOPY VARCHAR2,
1719 x_msg_count OUT NOCOPY NUMBER,
1720 x_msg_data OUT NOCOPY VARCHAR2,
1721 p_financial_asset_id IN NUMBER,
1722 p_quantity in number,
1723 p_proceeds_of_sale in number,
1724 p_quote_eff_date in date default null, -- rmunjulu edat
1725 p_quote_accpt_date in date default null, -- rmunjulu edat
1726 p_legal_entity_id in number -- rravikir legal entity changes
1727 ) IS
1728
1729 SUBTYPE thpv_rec_type IS OKL_TRX_ASSETS_PUB.thpv_rec_type;
1730 SUBTYPE tlpv_rec_type IS OKL_TXL_ASSETS_PUB.tlpv_rec_type;
1731
1732
1733
1734 -- SECHAWLA Bug # 2701440 :
1735 -- Modified this cursor to select all the tax books that an asset belongs to, in addition to the Fixed Asset Information
1736
1737 --SECHAWLA 23-DEC-02 Bug # 2701440
1738 --Added Order By clause to select CORPORATE Book first
1739
1740 --SECHAWLA 13-JAN-03 Bug # 2701440
1741 --Changed the cursor to select cost columns from fa_books instead of okx_asset_lines_v, as the latter has info for corporate book only
1742
1743 --SECHAWLA 03-JUN-03 Bug # 2999419
1744 --Added prorate_convention_code to the Select clause
1745 CURSOR l_okxassetlines_csr IS
1746 SELECT o.asset_id, o.asset_number, o.corporate_book, a.cost, o.depreciation_category, a.original_cost, o.current_units,
1747 o.dnz_chr_id ,a.book_type_code, b.book_class, prorate_convention_code
1748 FROM okx_asset_lines_v o, fa_books a, fa_book_controls b
1749 WHERE o.parent_line_id = p_financial_asset_id
1750 AND o.asset_id = a.asset_id
1751 AND a.book_type_code = b.book_type_code
1752 AND a.date_ineffective IS NULL
1753 AND a.transaction_header_id_out IS NULL
1754 ORDER BY book_class;
1755
1756
1757 --SECHAWLA 23_DEC-02 Bug # 2701440 : Added this cursor to get the cost retired, populated after the retirement of
1758 -- asset from the corporate book. We need this cost to perform cost retirement of the same asset in the TAX book
1759
1760 /* SECHAWLA 21-NOV-03 3262519 : This curosr is not required. Cost to be retired from the tax book should be
1761 -- calculated using tax book cost and not the corporate book cost, as teh 2 costs can be different
1762
1763 --SECHAWLA 13-JAN-03 Bug # 2701440 : Added Order By Clause to select the latest retirement record first
1764 CURSOR l_faretirement_csr(p_asset_id IN NUMBER, p_book_type_code IN VARCHAR2) IS
1765 SELECT cost_retired
1766 FROM fa_retirements
1767 WHERE asset_id = p_asset_id
1768 AND book_type_code = p_book_type_code
1769 ORDER BY last_update_date DESC;
1770 */
1771
1772 --This cursor is used to get all the active distributions for an asset
1773 CURSOR l_disthist_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2) IS
1774 SELECT distribution_id, units_assigned, retirement_id, transaction_units
1775 FROM fa_distribution_history
1776 WHERE asset_id = p_asset_id
1777 AND book_type_code = p_book_type_code
1778 AND date_ineffective IS NULL
1779 AND transaction_header_id_out IS NULL
1780 -- AND retirement_id IS NULL
1781 ORDER BY last_update_date;
1782
1783 -- This cursor is used to get all the pending transactions for an asset. These transactions are to be calcelled
1784 -- once the asset is retired.
1785 CURSOR l_assettrx_csr IS
1786 SELECT h.id
1787 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l
1788 WHERE h.id = l.tas_id
1789 AND h.tsu_code IN ('ENTERED', 'ERROR')
1790 AND l.kle_id = p_financial_asset_id;
1791
1792 -- This curosr is used to get all the instances for a Financial asset
1793 --Changed query to use base tables instead uv for performance
1794 CURSOR l_itemlocation_csr IS
1795 SELECT cii.instance_id instance_id, cii.active_end_date instance_end_date
1796 FROM okc_k_headers_b okhv,
1797 okc_k_lines_b kle_fa,
1798 okc_k_lines_tl klet_fa,
1799 okc_line_styles_b lse_fa,
1800 okc_k_lines_b kle_il,
1801 okc_line_styles_b lse_il,
1802 okc_k_lines_b kle_ib,
1803 okc_line_styles_b lse_ib,
1804 okc_k_items ite,
1805 csi_item_instances cii
1806 WHERE kle_fa.id = klet_fa.id
1807 AND klet_fa.language = USERENV('LANG')
1808 AND kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id
1809 AND lse_fa.lty_code = 'FREE_FORM1'
1810 AND kle_il.cle_id = kle_fa.id
1811 AND lse_il.id = kle_il.lse_id
1812 AND lse_il.lty_code = 'FREE_FORM2'
1813 AND kle_ib.cle_id = kle_il.id
1814 AND lse_ib.id = kle_ib.lse_id
1815 AND lse_ib.lty_code = 'INST_ITEM'
1816 AND ite.cle_id = kle_ib.id
1817 AND ite.jtot_object1_code = 'OKX_IB_ITEM'
1818 AND cii.instance_id = ite.object1_id1
1819 AND kle_fa.id = p_financial_asset_id;
1820
1821 -- This cursor is used to find out the period_of_addtion for the asset that is to be retired
1822 CURSOR l_periodofaddition_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2, p_period_open_date DATE) IS
1823 SELECT count(*)
1824 FROM fa_transaction_headers th
1825 WHERE th.asset_id = p_asset_id
1826 AND th.book_type_code = p_book_type_code
1827 AND th.transaction_type_code = 'ADDITION'
1828 AND th.date_effective > p_period_open_date;
1829
1830 -- This cursor is used temporarily to get the fiscal year name till FA API is fixed
1831 CURSOR l_bookcontrols_csr(p_book_type_code VARCHAR2) IS
1832 SELECT fiscal_year_name
1833 FROM fa_book_controls
1834 WHERE book_type_code = p_book_type_code;
1835
1836
1837 --SECHAWLA 21-NOV-2003 3262519 : Added the following cursors
1838
1839 -- validate the financial asset id
1840 CURSOR l_okclines_csr(p_financial_asset_id IN NUMBER) IS
1841 SELECT 'x'
1842 FROM okc_k_lines_b cle, okc_line_styles_b lse
1843 WHERE cle.lse_id = lse.id
1844 AND lse.lty_code = 'FREE_FORM1'
1845 AND cle.id = p_financial_asset_id;
1846
1847 -- get the deal type from the contract
1848 CURSOR l_dealtype_csr(p_financial_asset_id IN NUMBER) IS
1849 SELECT lkhr.id, lkhr.deal_type, khr.contract_number
1850 FROM okl_k_headers lkhr, okc_k_lines_b cle, okc_k_headers_b khr
1851 WHERE khr.id = cle.chr_id
1852 AND lkhr.id = khr.id
1853 AND cle.id = p_financial_asset_id;
1854
1855 -- get the residual value for the fin asset
1856 CURSOR l_linesfullv_csr(p_fin_asset_id IN NUMBER) IS
1857 SELECT name, NVL(residual_value,0) -- girao Bug 4106216 NVL the residual value
1858 FROM okl_k_lines_full_v
1859 WHERE id = p_fin_asset_id;
1860
1861 --SECHAWLA 10-FEB-06 5016156 : begin
1862 CURSOR l_offlseassettrx_csr(cp_trx_date IN DATE, cp_asset_number IN VARCHAR2) IS
1863 SELECT h.tsu_code, h.tas_type, h.date_trans_occurred, l.dnz_asset_id,
1864 l.asset_number, l.kle_id ,l.DNZ_KHR_ID
1865 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l
1866 WHERE h.id = l.tas_id
1867 AND h.date_trans_occurred <= cp_trx_date
1868 AND h.tas_type in ('AMT','AUD','AUS')
1869 AND l.asset_number = cp_asset_number;
1870
1871 l_trx_status VARCHAR2(30);
1872 --SECHAWLA 10-FEB-06 5016156 : end
1873
1874 l_deal_type VARCHAR2(30);
1875 l_chr_id NUMBER;
1876 l_dummy VARCHAR2(1);
1877 l_contract_number VARCHAR2(120);
1878 l_rulv_rec okl_rule_pub.rulv_rec_type;
1879 l_tax_owner VARCHAR2(10);
1880 l_delta_cost NUMBER;
1881 l_residual_value NUMBER;
1882 l_name VARCHAR2(150);
1883 l_cost NUMBER;
1884 --SECHAWLA 21-NOV-2003 3262519 : end new declarations
1885
1886
1887 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1888 l_tsu_code VARCHAR2(30);
1889 l_try_id OKL_TRX_TYPES_V.id%TYPE;
1890 lp_thpv_rec thpv_rec_type;
1891 lp_thpv_empty_rec thpv_rec_type;
1892 lp_tlpv_empty_rec tlpv_rec_type;
1893 lx_thpv_rec thpv_rec_type;
1894 lp_tlpv_rec tlpv_rec_type;
1895 lx_tlpv_rec tlpv_rec_type;
1896 l_sys_date DATE;
1897 l_api_version CONSTANT NUMBER := 1;
1898 l_trx_type VARCHAR2(30) := 'Asset Disposition';
1899 l_trx_name VARCHAR2(30) := 'ASSET_DISPOSITION';
1900 l_api_name CONSTANT VARCHAR2(30) := 'dispose_asset';
1901
1902
1903 l_dist_quantity NUMBER;
1904 l_dist_tbl asset_dist_tbl ;
1905 i NUMBER;
1906 l_retired_quantity NUMBER;
1907 instance_counter NUMBER;
1908 l_quantity NUMBER;
1909 l_already_retired VARCHAR2(1):= 'N';
1910 l_remaining_units NUMBER;
1911 l_active_end_date DATE;
1912 l_retired_dist_units NUMBER;
1913 l_units_to_be_retired NUMBER;
1914 l_non_retired_quantity NUMBER;
1915 lx_total_amount NUMBER;
1916 l_units_retired NUMBER;
1917
1918 --SECHAWLA 23-DEC-02 Bug # 2701440: new declarations
1919 l_cost_retired NUMBER;
1920
1921 --SECHAWLA 03-JAN-03 Bug # 2683876 : new declaration
1922 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
1923
1924 --SECHAWLA 05-FEB-03 Bug # 2781557 : new declarations
1925 l_fiscal_year_name VARCHAR2(30);
1926 l_period_rec FA_API_TYPES.period_rec_type;
1927 l_count NUMBER;
1928 l_period_of_addition VARCHAR2(1);
1929
1930 --SECHAWLA 11-MAR-03 New Declarations
1931 l_proceeds_of_sale NUMBER ;
1932
1933 -- RMUNJULU 3061751
1934 l_service_int_needed VARCHAR2(1) := 'N';
1935
1936 -- rmunjulu EDAT
1937 l_quote_eff_date DATE;
1938 l_quote_accpt_date DATE;
1939
1940 --SECHAWLA 15-DEC-04 4028371 New Declarations
1941 l_fa_trx_date DATE;
1942
1943 -- rbruno bug 5436987 start
1944 l_functional_currency_code VARCHAR2(15);
1945 l_contract_currency_code VARCHAR2(15);
1946 l_currency_conversion_type VARCHAR2(30);
1947 l_currency_conversion_rate NUMBER;
1948 l_currency_conversion_date DATE;
1949 l_converted_amount NUMBER;
1950
1951 -- rbruno bug 5436987
1952 -- Since we do not use the amount or converted amount
1953 -- set a hardcoded value for the amount (and pass to to
1954 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
1955 -- conversion values )
1956 l_hard_coded_amount NUMBER := 100;
1957 --rbruno bug 5436987 end
1958
1959
1960
1961 --akrangan sla populate sources cr start
1962 l_fxhv_rec okl_fxh_pvt.fxhv_rec_type;
1963 l_fxlv_rec okl_fxl_pvt.fxlv_rec_type;
1964 --akrangan sla populate sources cr end
1965
1966
1967 BEGIN
1968
1969 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1970 G_PKG_NAME,
1971 p_init_msg_list,
1972 l_api_version,
1973 p_api_version,
1974 '_PVT',
1975 x_return_status);
1976
1977 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1979 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1980 RAISE OKC_API.G_EXCEPTION_ERROR;
1981 END IF;
1982
1983 -- Get the sysdate
1984 SELECT SYSDATE INTO l_sys_date FROM DUAL;
1985
1986 -- rmunjulu EDAT Added condition to default
1987 IF p_quote_eff_date IS NOT NULL
1988 AND p_quote_eff_date <> OKL_API.G_MISS_DATE THEN
1989
1990 l_quote_eff_date := p_quote_eff_date;
1991
1992 ELSE
1993
1994 l_quote_eff_date := l_sys_date;
1995
1996 END IF;
1997
1998 -- rmunjulu EDAT Added condition to default
1999 IF p_quote_accpt_date IS NOT NULL
2000 AND p_quote_accpt_date <> OKL_API.G_MISS_DATE THEN
2001
2002 l_quote_accpt_date := p_quote_accpt_date;
2003
2004 ELSE
2005
2006 l_quote_accpt_date := l_sys_date;
2007
2008 END IF;
2009
2010 IF p_financial_asset_id IS NULL OR p_financial_asset_id = OKL_API.G_MISS_NUM THEN
2011 x_return_status := OKL_API.G_RET_STS_ERROR;
2012 -- Finacial Asset id is a required parameter
2013 OKC_API.set_message( p_app_name => 'OKC',
2014 p_msg_name => G_REQUIRED_VALUE,
2015 p_token1 => G_COL_NAME_TOKEN,
2016 p_token1_value => 'FINANCIAL_ASSET_ID');
2017
2018
2019 RAISE OKC_API.G_EXCEPTION_ERROR;
2020 END IF;
2021
2022 -- RRAVIKIR Legal Entity Changes
2023 IF (p_legal_entity_id is null or p_legal_entity_id = OKC_API.G_MISS_NUM) THEN
2024 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2025 p_msg_name => g_required_value,
2026 p_token1 => g_col_name_token,
2027 p_token1_value => 'legal_entity_id');
2028 RAISE OKC_API.G_EXCEPTION_ERROR;
2029 END IF;
2030 -- Legal Entity Changes End
2031
2032 --SECHAWLA 21-NOV-2003 3262519 : Added the following validation
2033 OPEN l_okclines_csr(p_financial_asset_id );
2034 FETCH l_okclines_csr INTO l_dummy;
2035 IF l_okclines_csr%NOTFOUND THEN
2036 x_return_status := OKL_API.G_RET_STS_ERROR;
2037 -- Finacial Asset id is invalid
2038 OKC_API.set_message( p_app_name => 'OKC',
2039 p_msg_name => G_INVALID_VALUE,
2040 p_token1 => G_COL_NAME_TOKEN,
2041 p_token1_value => 'FINANCIAL_ASSET_ID');
2042
2043 RAISE OKC_API.G_EXCEPTION_ERROR;
2044 END IF;
2045 CLOSE l_okclines_csr;
2046 --SECHAWLA 21-NOV-2003 3262519 : end
2047
2048
2049 -- SECHAWLA 11-MAR-03 : assign 0 to l_proceeds_of_sale if NULL
2050 l_proceeds_of_sale := p_proceeds_of_sale ;
2051
2052 IF l_proceeds_of_sale IS NULL OR l_proceeds_of_sale = OKL_API.G_MISS_NUM THEN
2053 l_proceeds_of_sale := 0;
2054 /*
2055 x_return_status := OKL_API.G_RET_STS_ERROR;
2056 -- proceeds_of_sale is required
2057 OKC_API.set_message( p_app_name => 'OKC',
2058 p_msg_name => G_REQUIRED_VALUE,
2059 p_token1 => G_COL_NAME_TOKEN,
2060 p_token1_value => 'PROCEEDS_OF_SALE');
2061
2062
2063 RAISE OKC_API.G_EXCEPTION_ERROR;
2064 */
2065 END IF;
2066
2067 -- SECHAWLA 11-MAR-03 : Allow negative amount
2068 /*
2069 IF p_proceeds_of_sale < 0 THEN
2070 x_return_status := OKL_API.G_RET_STS_ERROR;
2071 -- proceeds_of_sale is invalid
2072 OKC_API.set_message( p_app_name => 'OKC',
2073 p_msg_name => G_INVALID_VALUE,
2074 p_token1 => G_COL_NAME_TOKEN,
2075 p_token1_value => 'PROCEEDS_OF_SALE');
2076
2077
2078 RAISE OKC_API.G_EXCEPTION_ERROR;
2079 END IF;
2080 */
2081
2082 IF p_quantity IS NOT NULL THEN
2083 IF p_quantity < 0 THEN
2084 x_return_status := OKL_API.G_RET_STS_ERROR;
2085 -- Quantity is invalid
2086 OKC_API.set_message( p_app_name => 'OKC',
2087 p_msg_name => G_INVALID_VALUE,
2088 p_token1 => G_COL_NAME_TOKEN,
2089 p_token1_value => 'QUANTITY');
2090
2091
2092 RAISE OKC_API.G_EXCEPTION_ERROR;
2093 END IF;
2094 IF trunc(p_quantity) <> p_quantity THEN
2095 x_return_status := OKL_API.G_RET_STS_ERROR;
2096 -- Units retired should be a whole number.
2097 OKC_API.set_message( p_app_name => 'OKL',
2098 p_msg_name => 'OKL_AM_WHOLE_UNITS_ERR');
2099 RAISE OKC_API.G_EXCEPTION_ERROR;
2100 END IF;
2101 END IF;
2102
2103 --SECHAWLA 21-NOV-2003 3262519 : Added the following code to get the deal type and tax owner
2104
2105 -- get the deal type from the contract
2106 OPEN l_dealtype_csr(p_financial_asset_id);
2107 FETCH l_dealtype_csr INTO l_chr_id, l_deal_type, l_contract_number;
2108 IF l_dealtype_csr%NOTFOUND THEN
2109 x_return_status := OKL_API.G_RET_STS_ERROR;
2110 -- chr id is invalid
2111 OKC_API.set_message( p_app_name => 'OKC',
2112 p_msg_name => G_INVALID_VALUE,
2113 p_token1 => G_COL_NAME_TOKEN,
2114 p_token1_value => 'CHR_ID');
2115
2116 RAISE OKC_API.G_EXCEPTION_ERROR;
2117 END IF;
2118 CLOSE l_dealtype_csr;
2119
2120 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
2121 -- get the tax owner (LESSOR/LESSEE) for the contract
2122
2123 okl_am_util_pvt.get_rule_record(p_rgd_code => 'LATOWN'
2124 ,p_rdf_code =>'LATOWN'
2125 ,p_chr_id => l_chr_id
2126 ,p_cle_id => NULL
2127 ,x_rulv_rec => l_rulv_rec
2128 ,x_return_status => x_return_status
2129 ,x_msg_count => x_msg_count
2130 ,x_msg_data => x_msg_data);
2131
2132 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2133 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2134 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2135 RAISE OKC_API.G_EXCEPTION_ERROR;
2136 END IF;
2137
2138 -- check if tax owner is defined
2139 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
2140
2141 x_return_status := OKL_API.G_RET_STS_ERROR;
2142 -- tax owner is not defined for contract CONTRACT_NUMBER.
2143 OKL_API.set_message( p_app_name => 'OKL',
2144 p_msg_name => 'OKL_AM_NO_TAX_OWNER',
2145 p_token1 => 'CONTRACT_NUMBER',
2146 p_token1_value => l_contract_number);
2147 RAISE OKC_API.G_EXCEPTION_ERROR;
2148
2149 ELSE
2150 -- l_rulv_rec.RULE_INFORMATION1 will contain the value 'LESSEE' or 'LESSOR'
2151 l_tax_owner := l_rulv_rec.RULE_INFORMATION1;
2152 END IF;
2153
2154 -- get the residual value of the fin asset
2155 OPEN l_linesfullv_csr(p_financial_asset_id);
2156 FETCH l_linesfullv_csr INTO l_name, l_residual_value;
2157 CLOSE l_linesfullv_csr;
2158
2159 IF l_residual_value IS NULL THEN
2160 x_return_status := OKL_API.G_RET_STS_ERROR;
2161 -- Residual value is not defined for the asset
2162 OKC_API.set_message( p_app_name => 'OKL',
2163 p_msg_name => 'OKL_AM_NO_RESIDUAL_VALUE',
2164 p_token1 => 'ASSET_NUMBER',
2165 p_token1_value => l_name);
2166
2167
2168 RAISE OKC_API.G_EXCEPTION_ERROR;
2169 END IF;
2170
2171 -- rbruno 5436987 -- start
2172 l_contract_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(l_chr_id);
2173 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency;
2174
2175 -- currency codes different so need for conversion
2176 IF l_contract_currency_code <> l_functional_currency_code THEN
2177
2178 -- convert the residual value obtained to functional currency
2179 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
2180 p_khr_id => l_chr_id,
2181 p_to_currency => l_functional_currency_code,
2182 p_transaction_date => l_quote_eff_date,
2183 p_amount => l_residual_value, -- convert residual value from Contract to Functional currency
2184 x_return_status => l_return_status,
2185 x_contract_currency => l_contract_currency_code,
2186 x_currency_conversion_type => l_currency_conversion_type,
2187 x_currency_conversion_rate => l_currency_conversion_rate,
2188 x_currency_conversion_date => l_currency_conversion_date,
2189 x_converted_amount => l_converted_amount);
2190
2191 l_residual_value := l_converted_amount; -- residual value is now converted to functional currency
2192
2193 END IF;
2194 --rbruno 5436987 -- End
2195
2196
2197 IF l_residual_value < 0 THEN
2198 x_return_status := OKL_API.G_RET_STS_ERROR;
2199 -- Residual value is negative for the asset
2200 OKC_API.set_message( p_app_name => 'OKL',
2201 p_msg_name => 'OKL_AM_INVALID_RESIDUAL',
2202 p_token1 => 'ASSET_NUMBER',
2203 p_token1_value => l_name);
2204
2205
2206 RAISE OKC_API.G_EXCEPTION_ERROR;
2207 END IF;
2208 END IF;
2209
2210
2211
2212 --SECHAWLA 21-NOV-2003 3262519 : end new code
2213
2214
2215 -- SECHAWLA Bug # 2701440 : Changed OPEN, FETCH to a curosr FOR LOOP, as this cursor now has multiple rows
2216 -- for a given asset id : one row for the corporate book and one or more rows for the tax books
2217 FOR l_okxassetlines_rec IN l_okxassetlines_csr LOOP
2218
2219 --SECHAWLA 21-NOV-2003 3262519 : calculate delta cost for this book class (corporate / tax)
2220 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
2221
2222
2223 --SECHAWLA 10-FEB-06 5016156 Check if any off-lease transactions exist for the asset
2224 -- This will tell if it is termination with purchase or without purchase
2225 l_trx_status := NULL;
2226 FOR l_offlseassettrx_rec IN l_offlseassettrx_csr(l_quote_eff_date, l_name) LOOP
2227 l_trx_status := l_offlseassettrx_rec.tsu_code;
2228 IF l_trx_status IN ('ENTERED','ERROR','CANCELED') THEN
2229 EXIT;
2230 END IF;
2231 END LOOP;
2232
2233 IF l_trx_status IS NULL THEN -- This means off-lease trx don't exist. It is termination with purchase
2234 --SECHAWLA 10-FEB-06 5016156: end
2235 l_delta_cost := l_residual_value - l_okxassetlines_rec.cost;
2236 --SECHAWLA 10-FEB-06 5016156 : begin
2237 ELSIF l_trx_status IN ('ENTERED','ERROR') THEN -- if any trx has this status
2238 x_return_status := OKL_API.G_RET_STS_ERROR;
2239 OKL_API.set_message( p_app_name => 'OKL',
2240 p_msg_name => 'OKL_AM_PENDING_OFFLEASE',
2241 p_token1 => 'ASSET_NUMBER',
2242 p_token1_value => l_name);
2243
2244
2245 RAISE OKC_API.G_EXCEPTION_ERROR;
2246
2247
2248 ELSIF l_trx_status IN ( 'PROCESSED','CANCELED') THEN
2249 l_delta_cost := 0; -- no cost update required, as cost has already been updated thru off lease trx
2250 END IF; -- or off-lease trx has been canceled
2251 --SECHAWLA 10-FEB-06 5016156 : end
2252
2253 END IF;
2254 --SECHAWLA 21-NOV-2003 3262519 : end
2255
2256
2257 --SECHAWLA 05-FEB-03 Bug # 2781557 : Moved the following code from do_full_units_retirement, as the check
2258 -- whether the asset is added in the current open period, needs to be done at this stage.
2259
2260 -- This piece of code is included temporarily as a work around , since FA API has errors
2261 -- Set the Fiscal Year name in teh cache,if not already set
2262 -- IF fa_cache_pkg.fazcbc_record.fiscal_year_name IS NULL THEN
2263 OPEN l_bookcontrols_csr(l_okxassetlines_rec.book_type_code);
2264 FETCH l_bookcontrols_csr INTO l_fiscal_year_name;
2265 IF l_bookcontrols_csr%NOTFOUND OR l_fiscal_year_name IS NULL THEN
2266 x_return_status := OKL_API.G_RET_STS_ERROR;
2267 -- Fiscal Year Name is required
2268 OKC_API.set_message( p_app_name => 'OKC',
2269 p_msg_name => G_REQUIRED_VALUE,
2270 p_token1 => G_COL_NAME_TOKEN,
2271 p_token1_value => 'Fiscal Year Name');
2272
2273
2274 RAISE OKC_API.G_EXCEPTION_ERROR;
2275 END IF;
2276 CLOSE l_bookcontrols_csr;
2277 fa_cache_pkg.fazcbc_record.fiscal_year_name := l_fiscal_year_name;
2278 -- END IF;
2279
2280
2281 IF NOT FA_UTIL_PVT.get_period_rec
2282 (
2283 p_book => l_okxassetlines_rec.book_type_code,
2284 p_effective_date => NULL,
2285 x_period_rec => l_period_rec
2286 ) THEN
2287
2288 x_return_status := OKC_API.G_RET_STS_ERROR;
2289 --Error getting current open period for the book BOOK_TYPE_CODE.
2290 OKL_API.set_message(
2291 p_app_name => 'OKL',
2292 p_msg_name => 'OKL_AM_OPEN_PERIOD_ERR',
2293 p_token1 => 'BOOK_CLASS',
2294 p_token1_value => lower(l_okxassetlines_rec.book_class),
2295 p_token2 => 'BOOK_TYPE_CODE',
2296 p_token2_value => l_okxassetlines_rec.book_type_code
2297 );
2298
2299
2300 RAISE OKC_API.G_EXCEPTION_ERROR;
2301 END IF;
2302
2303 --- check period of addition. If 'N' then run retirements
2304 OPEN l_periodofaddition_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code,l_period_rec.period_open_date);
2305 FETCH l_periodofaddition_csr INTO l_count;
2306 CLOSE l_periodofaddition_csr;
2307
2308 IF (l_count <> 0) THEN
2309 l_period_of_addition := 'Y';
2310 ELSE
2311 l_period_of_addition := 'N';
2312 END IF;
2313
2314 IF l_period_of_addition = 'Y' THEN
2315 -- Can nor retire asset ASSET_NUMBER as the asset was added to the book
2316 -- in the current open period. Please retire the asset manually.
2317 x_return_status := OKC_API.G_RET_STS_ERROR;
2318
2319 OKL_API.set_message( p_app_name => 'OKL',
2320 p_msg_name => 'OKL_AM_RETIRE_MANUALLY',
2321 p_token1 => 'ASSET_NUMBER',
2322 p_token1_value => l_okxassetlines_rec.asset_number,
2323 p_token2 => 'BOOK_CLASS',
2324 p_token2_value => lower(l_okxassetlines_rec.book_class),
2325 p_token3 => 'BOOK_TYPE_CODE',
2326 p_token3_value => l_okxassetlines_rec.book_type_code);
2327
2328 RAISE OKC_API.G_EXCEPTION_ERROR;
2329
2330 END IF;
2331 /* ansethur for bug 5664106 -- start
2332 -- SECHAWLA 03-JUN-03 Bug 2999419: Added the following validation
2333 IF l_okxassetlines_rec.prorate_convention_code IS NULL THEN
2334 x_return_status := OKL_API.G_RET_STS_ERROR;
2335 -- Unable to find retirement prorate convention for asset ASSET_NUMBER and book BOOK_TYPE_CODE.
2336 OKC_API.set_message( p_app_name => 'OKL',
2337 p_msg_name => 'OKL_AM_NO_PRORATE_CONVENTION',
2338 p_token1 => 'ASSET_NUMBER',
2339 p_token1_value => l_okxassetlines_rec.asset_number,
2340 p_token2 => 'BOOK_CLASS',
2341 p_token2_value => l_okxassetlines_rec.book_class,
2342 p_token3 => 'BOOK_TYPE_CODE',
2343 p_token3_value => l_okxassetlines_rec.book_type_code);
2344 RAISE OKC_API.G_EXCEPTION_ERROR;
2345 END IF;
2346 -- SECHAWLA 03-JUN-03 Bug 2999419 : end new code
2347 */ -- ansethur for bug 5655545 - end
2348
2349
2350 ---------- SECHAWLA 05-FEB-03 Bug # 2781557 : end moved code ----------------
2351
2352
2353 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM OR p_quantity = l_okxassetlines_rec.current_units THEN
2354 -- user sent request for full retirement
2355
2356 -- check if asset has already been fully/partially retired .
2357 l_retired_quantity := 0;
2358 l_non_retired_quantity := 0;
2359
2360 --This FOR loop will be executed only for the corporate book, as tax books do not have any distributions
2361 -- loop thru all the retirement records for this asset and calculate the total retired quantity
2362 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2363 IF l_disthist_rec.retirement_id IS NOT NULL THEN
2364 l_retired_quantity := l_retired_quantity + abs(l_disthist_rec.transaction_units);
2365 ELSE
2366 l_non_retired_quantity := l_non_retired_quantity + l_disthist_rec.units_assigned;
2367 END IF;
2368 END LOOP;
2369
2370 --For TAX books, both l_retired_quantity and l_non_retired_quantity will be 0 at this stage.
2371
2372
2373 IF l_retired_quantity = 0 AND l_non_retired_quantity > 0 THEN -- True only for corporate book
2374 IF l_non_retired_quantity = l_okxassetlines_rec.current_units THEN --distribution qty = orginal asset qty
2375
2376 -- user requested for full retirement and none of the units have been retired so far
2377 -- perform full retirement
2378
2379 -- we are passing the total number of units and not the cost, for full retirements, because for
2380 -- Direct Finance Lease, okx_asset_lines_v, gives OEC as the cost. FA Retirements compares this cost with
2381 --cost in fa_books. These 2 costs can be different, which will give error. So we are using units instead
2382 -- of cost to avoid that validation check.
2383
2384
2385
2386 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
2387
2388
2389
2390 do_full_units_retirement(
2391 p_api_version => p_api_version,
2392 p_init_msg_list => OKC_API.G_FALSE,
2393 p_tax_owner => l_tax_owner,
2394 p_delta_cost => l_delta_cost,
2395 p_asset_id => l_okxassetlines_rec.asset_id,
2396 p_asset_number => l_okxassetlines_rec.asset_number,
2397 p_proceeds_of_sale => l_proceeds_of_sale,
2398 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : changed the parameter name
2399 p_book_type_code => l_okxassetlines_rec.book_type_code,
2400 --p_cost => l_cost,
2401 p_units => l_okxassetlines_rec.current_units,
2402 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2403 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2404 x_msg_count => x_msg_count,
2405 x_msg_data => x_msg_data,
2406 x_return_status => x_return_status,
2407 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2408 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2409
2410
2411 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2412 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2413 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2414 RAISE OKC_API.G_EXCEPTION_ERROR;
2415 END IF;
2416
2417 l_units_retired := l_okxassetlines_rec.current_units;
2418 ELSE
2419 -- distribution qty is either less or more than the current units
2420 -- and hence we need to consider this as partial retirement, even though the sold
2421 -- quantity = asset quantity (current_units)
2422
2423 IF l_non_retired_quantity > l_okxassetlines_rec.current_units THEN
2424 l_units_to_be_retired := l_okxassetlines_rec.current_units;
2425 ELSE
2426 l_units_to_be_retired := l_non_retired_quantity;
2427 END IF;
2428
2429 -- l_dist_quantity := l_current_units;
2430 l_dist_quantity := l_units_to_be_retired;
2431 i := 0;
2432
2433
2434 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2435 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2436 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2437 -- than requested units then retire that distribution fully and move to next distribution for remaining
2438 -- units, until all the requested units have been retired.
2439
2440
2441 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2442 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2443 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2444 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2445 l_dist_quantity := 0;
2446 EXIT;
2447 ELSE
2448 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2449 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2450 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2451 END IF;
2452 i := i + 1;
2453 END LOOP;
2454
2455
2456 -- If there are no more distributions left and there are still some more units to be retired,
2457 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
2458 -- assigned to all the distributions.
2459
2460
2461 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
2462 -- x_return_status := OKL_API.G_RET_STS_ERROR;
2463 -- Sold quantity is more than the total quantity assigned to asset distributions.
2464 OKC_API.set_message( p_app_name => 'OKL',
2465 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
2466 --RAISE okc_api.G_EXCEPTION_ERROR;
2467 END IF;
2468
2469 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameters to the following procedure call
2470
2471
2472 do_partial_units_retirement(
2473 p_api_version => p_api_version,
2474 p_init_msg_list => OKC_API.G_FALSE,
2475 p_tax_owner => l_tax_owner,
2476 p_delta_cost => l_delta_cost,
2477 p_asset_id => l_okxassetlines_rec.asset_id,
2478 p_asset_number => l_okxassetlines_rec.asset_number,
2479 p_proceeds_of_sale => l_proceeds_of_sale,
2480 -- p_corporate_book => p_corporate_book, --SECHAWLA Bug # 2701440 : changed the parameter name
2481 p_book_type_code => l_okxassetlines_rec.book_type_code,
2482 p_total_quantity => l_units_to_be_retired, -- units to be retired
2483 p_dist_tbl => l_dist_tbl,
2484 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2485 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2486 x_msg_count => x_msg_count,
2487 x_msg_data => x_msg_data,
2488 x_return_status => x_return_status,
2489 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2490 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2491
2492
2493 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2494 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2495 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2496 RAISE OKC_API.G_EXCEPTION_ERROR;
2497 END IF;
2498
2499 l_units_retired := l_units_to_be_retired ;
2500 END IF; --l_non_retired_quantity = l_quantity
2501
2502
2503 ELSIF l_retired_quantity = l_okxassetlines_rec.current_units AND l_non_retired_quantity = 0 THEN -- retired qty = current units
2504 -- Asset is already fully retired.
2505 OKC_API.set_message( p_app_name => 'OKL',
2506 p_msg_name => 'OKL_AM_ALREADY_RETIRED',
2507 p_token1 => 'ASSET_NUMBER',
2508 p_token1_value => l_okxassetlines_rec.asset_number);
2509 l_already_retired := 'Y';
2510
2511 ELSIF l_retired_quantity >= l_okxassetlines_rec.current_units AND l_non_retired_quantity > 0 THEN -- There are still some more units that can be retierd
2512 -- non-retired qty can be either less or more than l_quantity
2513 IF l_non_retired_quantity >= l_okxassetlines_rec.current_units THEN
2514 l_units_to_be_retired := l_okxassetlines_rec.current_units;
2515 ELSE
2516 l_units_to_be_retired := l_non_retired_quantity;
2517 END IF;
2518
2519
2520 -- l_dist_quantity := l_current_units;
2521 l_dist_quantity := l_units_to_be_retired;
2522 i := 0;
2523
2524 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2525 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2526 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2527 -- than requested units then retire that distribution fully and move to next distribution for remaining
2528 -- units, until all the requested units have been retired.
2529
2530
2531 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2532 IF l_disthist_rec.retirement_id IS NULL THEN
2533 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2534 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2535 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2536 l_dist_quantity := 0;
2537 EXIT;
2538 ELSE
2539 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2540 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2541 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2542 END IF;
2543 i := i + 1;
2544 END IF;
2545 END LOOP;
2546
2547 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
2548 do_partial_units_retirement(
2549 p_api_version => p_api_version,
2550 p_init_msg_list => OKC_API.G_FALSE,
2551 p_tax_owner => l_tax_owner,
2552 p_delta_cost => l_delta_cost,
2553 p_asset_id => l_okxassetlines_rec.asset_id,
2554 p_asset_number => l_okxassetlines_rec.asset_number,
2555 p_proceeds_of_sale => l_proceeds_of_sale,
2556 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : changed the parameter name
2557 p_book_type_code => l_okxassetlines_rec.book_type_code,
2558 p_total_quantity => l_units_to_be_retired, -- units to be retired
2559 p_dist_tbl => l_dist_tbl,
2560 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2561 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2562 x_msg_count => x_msg_count,
2563 x_msg_data => x_msg_data,
2564 x_return_status => x_return_status,
2565 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2566 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2567
2568
2569 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2570 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2571 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2572 RAISE OKC_API.G_EXCEPTION_ERROR;
2573 END IF;
2574
2575 l_units_retired := l_units_to_be_retired;
2576 ELSIF l_retired_quantity > l_okxassetlines_rec.current_units AND l_non_retired_quantity = 0 THEN
2577 -- x_return_status := OKL_API.G_RET_STS_ERROR;
2578 -- Asset ASSET_NUMBER is already retired with invalid retired quantity which is more than the original quantity.
2579 OKC_API.set_message( p_app_name => 'OKL',
2580 p_msg_name => 'OKL_AM_INVALID_RETIRED_QTY',
2581 p_token1 => 'ASSET_NUMBER',
2582 p_token1_value => l_okxassetlines_rec.asset_number);
2583 -- RAISE okc_api.G_EXCEPTION_ERROR;
2584
2585 ELSIF l_retired_quantity < l_okxassetlines_rec.current_units AND l_non_retired_quantity > 0 THEN
2586
2587
2588
2589 IF l_non_retired_quantity >= l_okxassetlines_rec.current_units THEN
2590 l_units_to_be_retired := l_okxassetlines_rec.current_units;
2591 ELSE
2592 l_units_to_be_retired := l_non_retired_quantity;
2593 END IF;
2594
2595 -- l_dist_quantity := l_current_units;
2596 l_dist_quantity := l_units_to_be_retired;
2597 i := 0;
2598
2599 -- l_disthist_csr picks up all active distributions, which could possibly be retired.
2600 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2601 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2602 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2603 -- than requested units then retire that distribution fully and move to next distribution for remaining
2604 -- units, until all the requested units have been retired.
2605
2606 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2607 IF l_disthist_rec.retirement_id IS NULL THEN
2608 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2609
2610 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2611 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2612 l_dist_quantity := 0;
2613 EXIT;
2614 ELSE
2615 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2616 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2617 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2618 END IF;
2619 i := i + 1;
2620 END IF;
2621 END LOOP;
2622
2623 -- If there are no more active distributions left and there are still some more units to be retired,
2624 -- then the input quantity was invalid. Quantity can not be more than the sum total of the units
2625 -- assigned to non-retired distributions.
2626
2627 IF l_dist_quantity > 0 THEN
2628 IF l_retired_quantity < l_dist_quantity THEN
2629 -- x_return_status := OKL_API.G_RET_STS_ERROR;
2630 -- Sold quantity is more than the total quantity assigned to asset distributions.
2631 OKC_API.set_message( p_app_name => 'OKL',
2632 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
2633 -- RAISE okc_api.G_EXCEPTION_ERROR;
2634 END IF;
2635 END IF;
2636
2637 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
2638 do_partial_units_retirement(
2639 p_api_version => p_api_version,
2640 p_init_msg_list => OKC_API.G_FALSE,
2641 p_tax_owner => l_tax_owner,
2642 p_delta_cost => l_delta_cost,
2643 p_asset_id => l_okxassetlines_rec.asset_id,
2644 p_asset_number => l_okxassetlines_rec.asset_number,
2645 p_proceeds_of_sale => l_proceeds_of_sale,
2646 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 :changed the parameter name
2647 p_book_type_code => l_okxassetlines_rec.book_type_code,
2648 p_total_quantity => l_units_to_be_retired, -- units to be retired
2649 p_dist_tbl => l_dist_tbl,
2650 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2651 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2652 x_msg_count => x_msg_count,
2653 x_msg_data => x_msg_data,
2654 x_return_status => x_return_status,
2655 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2656 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2657
2658 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2659 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2660 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2661 RAISE OKC_API.G_EXCEPTION_ERROR;
2662 END IF;
2663
2664 l_units_retired := l_units_to_be_retired;
2665
2666
2667 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
2668 ELSIF l_retired_quantity = 0 AND l_non_retired_quantity = 0 THEN -- This condition will be true only for TAX books
2669
2670 IF l_already_retired = 'N' THEN -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this condition to stop cost retirement of tax book if asset is already fully retierd in corp book
2671 -- do cost retirement for the tax book
2672
2673 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
2674 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
2675 -- Cost Adjustment will happen in tax book through do_cost_retirement
2676 -- Cost will become Residual Value
2677 l_cost := l_okxassetlines_rec.cost; -- Retire Asset Cost -- for bug 5760603 - Earlier RV
2678 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
2679 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
2680 l_cost := l_okxassetlines_rec.cost; -- Retire the current cost in FA -- this is FA Cost
2681 END IF;
2682 -- SECHAWLA 21-NOV-2003 3262519 : end
2683
2684
2685 -- SECHAWLA 21-nov-03 3262519 Added tax owner and delta cost parameters
2686 do_cost_retirement(
2687 p_api_version => p_api_version,
2688 p_init_msg_list => OKC_API.G_FALSE,
2689 p_tax_owner => l_tax_owner,
2690 p_delta_cost => l_delta_cost,
2691 p_asset_id => l_okxassetlines_rec.asset_id,
2692 p_asset_number => l_okxassetlines_rec.asset_number,
2693 p_proceeds_of_sale => l_proceeds_of_sale,
2694 p_tax_book => l_okxassetlines_rec.book_type_code,
2695 -- p_cost => l_cost_retired, -- SECHAWLA 13-JAN-03 Bug # 2701440
2696 -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the original request is for Full retirement, do a full cost retirement for the tax book
2697 -- p_cost => l_okxassetlines_rec.cost, -- SECHAWLA 21-NOV-2003 3262519
2698 p_cost => l_cost, -- SECHAWLA 21-NOV-2003 3262519
2699 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419: Added this parameter
2700 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
2701 x_msg_count => x_msg_count,
2702 x_msg_data => x_msg_data,
2703 x_return_status => x_return_status,
2704 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2705 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2706
2707 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2708 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2709 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2710 RAISE OKC_API.G_EXCEPTION_ERROR;
2711 END IF;
2712 END IF;
2713 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
2714 END IF;
2715
2716
2717
2718 ELSE -- input quantity is either less or more than the current units
2719 -- user requested for partial retirement by p_quantity units
2720
2721 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN
2722
2723 l_dist_quantity := p_quantity;
2724
2725 i := 0;
2726
2727 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2728 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2729 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2730 -- than requested units then retire that distribution fully and move to next distribution for remaining
2731 -- units, until all the requested units have been retired.
2732
2733
2734 -- l_disthist_csr picks up all active distributions, which could possibly be retired.
2735
2736 l_retired_dist_units := 0;
2737
2738 -- This loop is executed only for corporate book, as tax book does not have any distributions
2739 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.corporate_book) LOOP
2740 -- First retire all non-retired distributions, maintain a unit count of already retired distributions.
2741 -- We will use this count at the end, when all non-retired distributions have been retired, to make
2742 -- sure that the units retired = input quantity
2743
2744 IF l_disthist_rec.retirement_id IS NOT NULL THEN
2745 l_retired_dist_units := l_retired_dist_units + abs(l_disthist_rec.transaction_units);
2746 ELSE
2747
2748 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2749
2750 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2751 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2752 l_dist_quantity := 0;
2753 EXIT;
2754 ELSE
2755 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2756 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2757 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2758 END IF;
2759 i := i + 1;
2760 END IF;
2761 END LOOP;
2762
2763 -- If there are no more active distributions left and there are still some more units to be retired,
2764 -- then the input quantity was invalid. Quantity can not be more than the sum total of the units
2765 -- assigned to non-retired distributions.
2766
2767 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
2768
2769 IF l_retired_dist_units < l_dist_quantity THEN -- retired quantity isn't enough to match up with total qty
2770 -- Sold quantity is more than the total quantity assigned to asset distributions.
2771 OKC_API.set_message( p_app_name => 'OKL',
2772 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
2773 END IF;
2774 l_units_to_be_retired := p_quantity - l_dist_quantity;
2775 ELSE
2776 l_units_to_be_retired := p_quantity;
2777 END IF;
2778
2779 IF l_dist_tbl.COUNT > 0 THEN
2780 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
2781 do_partial_units_retirement(
2782 p_api_version => p_api_version,
2783 p_init_msg_list => OKC_API.G_FALSE,
2784 p_tax_owner => l_tax_owner,
2785 p_delta_cost => l_delta_cost,
2786 p_asset_id => l_okxassetlines_rec.asset_id,
2787 p_asset_number => l_okxassetlines_rec.asset_number,
2788 p_proceeds_of_sale => l_proceeds_of_sale,
2789 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : changed the parameter name
2790 p_book_type_code => l_okxassetlines_rec.book_type_code,
2791 p_total_quantity => l_units_to_be_retired, -- quantity to be retired
2792 p_dist_tbl => l_dist_tbl,
2793 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2794 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
2795 x_msg_count => x_msg_count,
2796 x_msg_data => x_msg_data,
2797 x_return_status => x_return_status,
2798 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2799 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2800
2801 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2802 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2803 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2804 RAISE OKC_API.G_EXCEPTION_ERROR;
2805 END IF;
2806
2807
2808 /* SECHAWLA 21-NOV-03 3262519 : This fetch is not required. Cost to be retired from the tax book should be
2809 -- calculated using tax book cost and not the corporate book cost, as the 2 costs can be different
2810
2811 -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this code to get the cost retired for the corporate book
2812 -- This cost is used later to perform a cost retirement for the TAX book
2813 OPEN l_faretirement_csr(l_okxassetlines_rec.asset_id,l_okxassetlines_rec.corporate_book);
2814 FETCH l_faretirement_csr INTO l_cost_retired;
2815 -- Since asset is first retired from corporate book, this fetch will definitely find a row
2816 CLOSE l_faretirement_csr;
2817 */
2818
2819
2820
2821 l_units_retired := l_units_to_be_retired;
2822 ELSE
2823 -- If it reaches here, it means it didn't find any new distributions to retire. Since we are not
2824 -- processing any records in FA in this case, we consider this asset as already retired.
2825
2826 l_already_retired := 'Y';
2827 END IF;
2828 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
2829 ELSIF l_okxassetlines_rec.book_class = 'TAX' THEN
2830
2831 IF l_already_retired = 'N' THEN -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this condition to stop cost retirement of tax book if asset is already fully retierd in corp book
2832
2833 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
2834 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
2835 -- Cost Adjustment will happen in tax book through do_cost_retirement
2836 -- Cost will become Residual Value
2837 l_cost := l_okxassetlines_rec.cost; -- for bug 5760603 -- Retire cost Not Rv
2838 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
2839 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
2840 l_cost := l_okxassetlines_rec.cost; -- cost to be considered is the curent cost
2841 END IF;
2842 -- SECHAWLA 21-NOV-2003 3262519 : end
2843
2844 -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this condition for teh scenario where tax book's initial cost is less than the corp book cost
2845 --IF l_okxassetlines_rec.cost >= l_cost_retired THEN -- SECHAWLA 21-nov-03 3262519
2846
2847 --SECHAWLA 21-NOV-2003 3262519 : Cost to be retired from tax book should be calculated using
2848 -- tax book's cost and the quentity retired in the corporate book
2849 l_cost_retired := (l_cost / l_okxassetlines_rec.current_units ) * l_units_retired;
2850
2851 IF l_cost >= l_cost_retired THEN
2852 -- This condition should always be true
2853 -- do cost retirement for the tax book
2854
2855 -- SECHAWLA 21-nov-03 3262519 Added tax owner and delta cost parameters
2856
2857
2858 do_cost_retirement(
2859 p_api_version => p_api_version,
2860 p_init_msg_list => OKC_API.G_FALSE,
2861 p_tax_owner => l_tax_owner,
2862 p_delta_cost => l_delta_cost,
2863 p_asset_id => l_okxassetlines_rec.asset_id,
2864 p_asset_number => l_okxassetlines_rec.asset_number,
2865 p_proceeds_of_sale => l_proceeds_of_sale,
2866 p_tax_book => l_okxassetlines_rec.book_type_code,
2867 p_cost => l_cost_retired,
2868 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2869 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
2870 x_msg_count => x_msg_count,
2871 x_msg_data => x_msg_data,
2872 x_return_status => x_return_status,
2873 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2874 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2875
2876 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2877 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2878 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2879 RAISE OKC_API.G_EXCEPTION_ERROR;
2880 END IF;
2881 -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the tax book's cost is less than the cost retierd from the corp book
2882 -- but has not been fully retired yet, then perform a full cost retirement for tax book
2883
2884 --SECHAWLA 21-NOV-2003 3262519 : This condition will not occur now that we calculate cost to be
2885 -- retired from tax book using tax book cost itself
2886 --ELSIF l_okxassetlines_rec.cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
2887 /* ELSIF l_cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
2888 -- retire the whole remaining cost
2889
2890 -- SECHAWLA 21-nov-03 3262519 Added tax owner and delta cost parameters
2891 do_cost_retirement(
2892 p_api_version => p_api_version,
2893 p_init_msg_list => OKC_API.G_FALSE,
2894 p_tax_owner => l_tax_owner,
2895 p_delta_cost => l_delta_cost,
2896 p_asset_id => l_okxassetlines_rec.asset_id,
2897 p_asset_number => l_okxassetlines_rec.asset_number,
2898 p_proceeds_of_sale => l_proceeds_of_sale,
2899 p_tax_book => l_okxassetlines_rec.book_type_code,
2900 --p_cost => l_okxassetlines_rec.cost -- SECHAWLA 21-nov-03 3262519
2901 p_cost => l_cost, -- SECHAWLA 21-nov-03 3262519
2902 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2903 x_msg_count => x_msg_count,
2904 x_msg_data => x_msg_data,
2905 x_return_status => x_return_status);
2906
2907 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2908 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2909 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2910 RAISE OKC_API.G_EXCEPTION_ERROR;
2911 END IF;
2912 */
2913 END IF;
2914
2915
2916 END IF;
2917
2918 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
2919 END IF;
2920
2921 END IF; -- IF l_orderlines_rec.ordered_quantity = l_quantity THEN
2922
2923 IF l_already_retired = 'N' THEN
2924 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN -- SECHAWLA Bug # 2701440 : Added this condition to
2925 -- store trx transaction and process a/c entries only
2926 -- for CORPORATE book
2927 -- Store Transaction in OKL
2928 okl_am_util_pvt.get_transaction_id(p_try_name => l_trx_type,
2929 x_return_status => x_return_status,
2930 x_try_id => l_try_id);
2931
2932 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2933 -- Unable to find a transaction type for this transaction.
2934 OKL_API.set_message(p_app_name => 'OKL',
2935 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
2936 p_token1 => 'TRY_NAME',
2937 p_token1_value => 'Asset Disposition');
2938 RAISE OKC_API.G_EXCEPTION_ERROR;
2939 END IF;
2940
2941
2942 lp_thpv_rec.tas_type := 'RFA';
2943 lp_thpv_rec.tsu_code := 'PROCESSED';
2944 lp_thpv_rec.try_id := l_try_id;
2945 lp_thpv_rec.date_trans_occurred := l_quote_accpt_date; -- rmunjulu EDAT changed from sysdate to accpt date
2946
2947 -- RRAVIKIR Legal Entity Changes
2948 lp_thpv_rec.legal_entity_id := p_legal_entity_id;
2949 -- Legal Entity Changes End
2950
2951 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def( p_api_version => p_api_version,
2952 p_init_msg_list => OKC_API.G_FALSE,
2953 x_return_status => x_return_status,
2954 x_msg_count => x_msg_count,
2955 x_msg_data => x_msg_data,
2956 p_thpv_rec => lp_thpv_rec,
2957 x_thpv_rec => lx_thpv_rec);
2958
2959 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2960 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2961 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2962 RAISE OKC_API.G_EXCEPTION_ERROR;
2963 END IF;
2964
2965 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
2966 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
2967 lp_tlpv_rec.currency_code := l_func_curr_code;
2968
2969 -- rbruno 5436987 -- start
2970 --lp_tlpv_rec.currency_code := l_func_curr_code;
2971 l_contract_currency_code := okl_am_util_pvt.get_chr_currency(l_chr_id);
2972 lp_tlpv_rec.currency_code := l_contract_currency_code;
2973
2974 IF l_func_curr_code <> l_contract_currency_code THEN
2975
2976 -- get currency conversion parameters
2977 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
2978 p_khr_id => l_chr_id,
2979 p_to_currency => l_func_curr_code,
2980 p_transaction_date => l_quote_eff_date,
2981 p_amount => l_hard_coded_amount,
2982 x_return_status => x_return_status,
2983 x_contract_currency => l_contract_currency_code,
2984 x_currency_conversion_type => l_currency_conversion_type,
2985 x_currency_conversion_rate => l_currency_conversion_rate,
2986 x_currency_conversion_date => l_currency_conversion_date,
2987 x_converted_amount => l_converted_amount);
2988
2989 lp_tlpv_rec.currency_conversion_type := l_currency_conversion_type;
2990 lp_tlpv_rec.currency_conversion_rate := l_currency_conversion_rate;
2991 lp_tlpv_rec.currency_conversion_date := l_currency_conversion_date;
2992
2993 END IF;
2994 -- rbruno 5436987 -- end
2995
2996
2997 -- Create transaction Line
2998 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
2999 lp_tlpv_rec.iay_id := l_okxassetlines_rec.depreciation_category;
3000 lp_tlpv_rec.kle_id := p_financial_asset_id;
3001 lp_tlpv_rec.line_number := 1;
3002 lp_tlpv_rec.tal_type := 'RFL';
3003 lp_tlpv_rec.asset_number := l_okxassetlines_rec.asset_number;
3004 lp_tlpv_rec.corporate_book := l_okxassetlines_rec.book_type_code;
3005 lp_tlpv_rec.original_cost := l_okxassetlines_rec.original_cost;
3006 lp_tlpv_rec.current_units := l_okxassetlines_rec.current_units;
3007 lp_tlpv_rec.units_retired := l_units_retired ;
3008 lp_tlpv_rec.dnz_asset_id := l_okxassetlines_rec.asset_id;
3009 lp_tlpv_rec.dnz_khr_id := l_okxassetlines_rec.dnz_chr_id;
3010
3011 -- SECHAWLA 15-DEC-04 4028371 : set FA date on trx line
3012 lp_tlpv_rec.FA_TRX_DATE := l_fa_trx_date;
3013
3014 OKL_TXL_ASSETS_PUB.create_txl_asset_def(p_api_version => p_api_version,
3015 p_init_msg_list => OKC_API.G_FALSE,
3016 x_return_status => x_return_status,
3017 x_msg_count => x_msg_count,
3018 x_msg_data => x_msg_data,
3019 p_tlpv_rec => lp_tlpv_rec,
3020 x_tlpv_rec => lx_tlpv_rec);
3021
3022 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3023 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3024 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3025 RAISE OKC_API.G_EXCEPTION_ERROR;
3026 END IF;
3027
3028 -----------------end Store Transaction in OKL -----------------
3029
3030 -- make call to accounting entries
3031 process_accounting_entries(
3032 p_api_version => p_api_version,
3033 p_init_msg_list => OKC_API.G_FALSE,
3034 x_return_status => x_return_status,
3035 x_msg_count => x_msg_count,
3036 x_msg_data => x_msg_data,
3037 p_kle_id => p_financial_asset_id,
3038 p_try_id => l_try_id,
3039 p_sys_date => l_quote_eff_date, -- rbruno EDAT Changed from sysdate to acceptance date -- rbruno bug 5436987
3040 p_source_id => lx_tlpv_rec.id,
3041 p_trx_type => l_trx_name,
3042 p_amount => l_proceeds_of_sale,
3043 p_func_curr_code => l_func_curr_code,
3044 x_total_amount => lx_total_amount,
3045 --akrangan start
3046 p_legal_entity_id => p_legal_entity_id
3047 --akrangan end
3048 );
3049
3050 -- rollback if error in accounting entries
3051 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3052 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3053 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3054 RAISE OKC_API.G_EXCEPTION_ERROR;
3055 END IF;
3056
3057 -- Store the amount at the header and line level in trx tables
3058
3059 -- Update amount in the header table
3060 lp_thpv_rec := lp_thpv_empty_rec;
3061 lp_thpv_rec.id := lx_thpv_rec.id;
3062 lp_thpv_rec.total_match_amount := lx_total_amount;
3063
3064 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
3065 p_api_version => p_api_version,
3066 p_init_msg_list => OKC_API.G_FALSE,
3067 x_return_status => x_return_status,
3068 x_msg_count => x_msg_count,
3069 x_msg_data => x_msg_data,
3070 p_thpv_rec => lp_thpv_rec,
3071 x_thpv_rec => lx_thpv_rec);
3072
3073 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3074 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3075 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3076 RAISE OKC_API.G_EXCEPTION_ERROR;
3077 END IF;
3078
3079 -- Update amount in the lines table.
3080 lp_tlpv_rec := lp_tlpv_empty_rec;
3081 lp_tlpv_rec.id := lx_tlpv_rec.id;
3082 lp_tlpv_rec.match_amount := lx_total_amount;
3083
3084 --SECHAWLA 03-JAN-03 Added the following statement as a temporary fix to LA's ROUNDING ERROR problem
3085 lp_tlpv_rec.kle_id := p_financial_asset_id;
3086
3087 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
3088 lp_tlpv_rec.currency_code := l_func_curr_code;
3089
3090 -- rbruno 5436987 --- start
3091 --lp_tlpv_rec.currency_code := l_func_curr_code;
3092 IF l_func_curr_code <> l_contract_currency_code THEN
3093 lp_tlpv_rec.currency_conversion_type := l_currency_conversion_type;
3094 lp_tlpv_rec.currency_conversion_rate := l_currency_conversion_rate;
3095 lp_tlpv_rec.currency_conversion_date := l_currency_conversion_date;
3096 END IF;
3097 lp_tlpv_rec.currency_code := l_contract_currency_code;
3098 --rbruno 5436987 --- end
3099
3100
3101 OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
3102 p_api_version => p_api_version,
3103 p_init_msg_list => OKC_API.G_FALSE,
3104 x_return_status => x_return_status,
3105 x_msg_count => x_msg_count,
3106 x_msg_data => x_msg_data,
3107 p_tlpv_rec => lp_tlpv_rec,
3108 x_tlpv_rec => lx_tlpv_rec);
3109
3110 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3111 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3112 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3113 RAISE OKC_API.G_EXCEPTION_ERROR;
3114 END IF;
3115
3116 END IF;
3117 END IF; -- if l_already_retired = 'N'
3118
3119
3120 END LOOP;
3121
3122
3123 --- Expire item in Installed Base
3124 -- commented call to expire_item djanaswa bug 6736148 start
3125 /*
3126 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM THEN
3127 -- Retire all existing instances
3128 FOR l_itemlocation_rec in l_itemlocation_csr LOOP
3129
3130 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
3131 expire_item (
3132 p_api_version => p_api_version,
3133 p_init_msg_list => OKC_API.G_FALSE,
3134 x_msg_count => x_msg_count,
3135 x_msg_data => x_msg_data,
3136 x_return_status => x_return_status ,
3137 p_instance_id => l_itemlocation_rec.instance_id,
3138 p_end_date => l_sys_date); -- rmunjulu EDAT 23-Nov-04 -- change back to sysdate -- rmunjulu EDAT Changed from sysdate to eff date
3139
3140 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3142 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3143 RAISE OKC_API.G_EXCEPTION_ERROR;
3144 END IF;
3145
3146 END IF;
3147
3148 END LOOP;
3149
3150
3151
3152 ELSE -- quantity < original quantity
3153 instance_counter := 1;
3154 -- retire number of instances equal to the input quantity
3155 FOR l_itemlocation_rec in l_itemlocation_csr LOOP
3156
3157 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
3158 expire_item (
3159 p_api_version => p_api_version,
3160 p_init_msg_list => OKC_API.G_FALSE,
3161 x_msg_count => x_msg_count,
3162 x_msg_data => x_msg_data,
3163 x_return_status => x_return_status ,
3164 p_instance_id => l_itemlocation_rec.instance_id,
3165 p_end_date => l_sys_date); -- rmunjulu EDAT 23-Nov-04 Change back to sysdate -- rmunjulu EDAT Changed from sysdate to eff date
3166
3167 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3168 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3169 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3170 RAISE OKC_API.G_EXCEPTION_ERROR;
3171 END IF;
3172
3173 IF instance_counter = p_quantity THEN
3174 EXIT;
3175 END IF;
3176 instance_counter := instance_counter + 1;
3177 END IF;
3178
3179 END LOOP;
3180
3181 END IF;
3182 */
3183 -------------- end IB Retirement -----------------------
3184 -- commented call to expire_item djanaswa bug 6736148 end
3185
3186
3187
3188
3189
3190 -- Loop thru all the pending transactions in okl_trx_assets_v and okl_txl_assets_v
3191 -- and update the status to 'CANCELED'
3192
3193 FOR l_assettrx_rec IN l_assettrx_csr LOOP
3194 -- update the staus (tsu_code) in okl_trx_assets_v
3195 lp_thpv_rec := lp_thpv_empty_rec;
3196 lp_thpv_rec.id := l_assettrx_rec.id;
3197 lp_thpv_rec.tsu_code := 'CANCELED';
3198
3199 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
3200 p_api_version => p_api_version,
3201 p_init_msg_list => OKC_API.G_FALSE,
3202 x_return_status => x_return_status,
3203 x_msg_count => x_msg_count,
3204 x_msg_data => x_msg_data,
3205 p_thpv_rec => lp_thpv_rec,
3206 x_thpv_rec => lx_thpv_rec);
3207
3208 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3209 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3210 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3211 RAISE OKC_API.G_EXCEPTION_ERROR;
3212 END IF;
3213 END LOOP;
3214
3215
3216
3217 -- ++++++++++++++++++++ service contract integration begin ++++++++++++++++++
3218
3219 -- RMUNJULU 3061751 11-SEP-2003
3220 -- Check if linked service contract exists for the asset which is disposed
3221 l_service_int_needed := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed(
3222 p_asset_id => p_financial_asset_id,
3223 p_source => 'DISPOSE');
3224
3225 -- Do the Service Contract Integration Notification for DISPOSE
3226 OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration(
3227 p_transaction_id => p_financial_asset_id,
3228 p_transaction_date => l_quote_accpt_date, -- rmunjulu EDAT changed from sysdate to acceptance date
3229 p_source => 'DISPOSE_1',
3230 p_service_integration_needed => l_service_int_needed);
3231
3232 -- ++++++++++++++++++++ service contract integration end ++++++++++++++++++
3233
3234
3235 -- MDOKAL: 18-SEP-03 - Bug 3082639
3236 -------------- Securitization Processing -----------------------
3237
3238 OKL_AM_SECURITIZATION_PVT.process_securitized_streams(
3239 p_api_version => p_api_version,
3240 p_init_msg_list => OKC_API.G_FALSE,
3241 x_return_status => x_return_status,
3242 x_msg_count => x_msg_count,
3243 x_msg_data => x_msg_data,
3244 p_kle_id => p_financial_asset_id,
3245 p_sale_price => p_proceeds_of_sale,
3246 p_effective_date => l_quote_eff_date, -- rmunjulu EDAT Added
3247 p_transaction_date => l_quote_accpt_date, -- rmunjulu EDAT Added
3248 p_call_origin => OKL_SECURITIZATION_PVT.G_TRX_REASON_ASSET_DISPOSAL);
3249
3250 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3252 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3253 RAISE OKC_API.G_EXCEPTION_ERROR;
3254 END IF;
3255 -------------- end Securitization Processing -----------------------
3256 --akrangan added for sla populate sources cr start
3257 IF g_trans_id_tbl.COUNT > 0
3258 THEN
3259 FOR i IN g_trans_id_tbl.FIRST .. g_trans_id_tbl.LAST
3260 LOOP
3261 -- header record
3262 l_fxhv_rec.source_id := lx_thpv_rec.id;
3263 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
3264 l_fxhv_rec.khr_id := lx_tlpv_rec.dnz_khr_id;
3265 l_fxhv_rec.try_id := lx_thpv_rec.try_id;
3266 -- line record
3267 l_fxlv_rec.source_id := lx_tlpv_rec.id;
3268 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
3269 l_fxlv_rec.kle_id := p_financial_asset_id;
3270 l_fxlv_rec.asset_id := lx_tlpv_rec.dnz_asset_id;
3271 l_fxlv_rec.fa_transaction_id := g_trans_id_tbl(i);
3272 l_fxlv_rec.asset_book_type_name := lx_tlpv_rec.corporate_book;
3273
3274 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
3275 p_init_msg_list => okc_api.g_false,
3276 p_fxhv_rec => l_fxhv_rec,
3277 p_fxlv_rec => l_fxlv_rec,
3278 x_return_status => x_return_status,
3279 x_msg_count => x_msg_count,
3280 x_msg_data => x_msg_data);
3281
3282 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
3283 THEN
3284 RAISE okl_api.g_exception_unexpected_error;
3285 ELSIF (x_return_status = okc_api.g_ret_sts_error)
3286 THEN
3287 RAISE okl_api.g_exception_error;
3288 END IF;
3289 END LOOP;
3290 END IF;
3291 --akrangan added for sla populate sources cr end
3292
3293 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3294 EXCEPTION
3295 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3296
3297 IF l_okxassetlines_csr%ISOPEN THEN
3298 CLOSE l_okxassetlines_csr;
3299 END IF;
3300
3301 IF l_disthist_csr%ISOPEN THEN
3302 CLOSE l_disthist_csr;
3303 END IF;
3304
3305 IF l_assettrx_csr%ISOPEN THEN
3306 CLOSE l_assettrx_csr;
3307 END IF;
3308
3309 IF l_itemlocation_csr%ISOPEN THEN
3310 CLOSE l_itemlocation_csr;
3311 END IF;
3312
3313 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
3314 IF l_periodofaddition_csr%ISOPEN THEN
3315 CLOSE l_periodofaddition_csr;
3316 END IF;
3317
3318 IF l_bookcontrols_csr%ISOPEN THEN
3319 CLOSE l_bookcontrols_csr;
3320 END IF;
3321
3322 -- SECHAWLA 21-nov-03 3262519 : close new cursors
3323 IF l_okclines_csr%ISOPEN THEN
3324 CLOSE l_okclines_csr;
3325 END IF;
3326
3327 IF l_dealtype_csr%ISOPEN THEN
3328 CLOSE l_dealtype_csr;
3329 END IF;
3330
3331 IF l_linesfullv_csr%ISOPEN THEN
3332 CLOSE l_linesfullv_csr;
3333 END IF;
3334 -- SECHAWLA 21-nov-03 3262519 : end
3335
3336 --SECHAWLA 10-FEB-06 5016156
3337 IF l_offlseassettrx_csr%ISOPEN THEN
3338 CLOSE l_offlseassettrx_csr;
3339 END IF;
3340
3341
3342 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3343 (
3344 l_api_name,
3345 G_PKG_NAME,
3346 'OKC_API.G_RET_STS_ERROR',
3347 x_msg_count,
3348 x_msg_data,
3349 '_PVT'
3350 );
3351 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3352
3353 IF l_okxassetlines_csr%ISOPEN THEN
3354 CLOSE l_okxassetlines_csr;
3355 END IF;
3356
3357 IF l_disthist_csr%ISOPEN THEN
3358 CLOSE l_disthist_csr;
3359 END IF;
3360
3361 IF l_assettrx_csr%ISOPEN THEN
3362 CLOSE l_assettrx_csr;
3363 END IF;
3364
3365 IF l_itemlocation_csr%ISOPEN THEN
3366 CLOSE l_itemlocation_csr;
3367 END IF;
3368
3369 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
3370 IF l_periodofaddition_csr%ISOPEN THEN
3371 CLOSE l_periodofaddition_csr;
3372 END IF;
3373
3374 IF l_bookcontrols_csr%ISOPEN THEN
3375 CLOSE l_bookcontrols_csr;
3376 END IF;
3377
3378 -- SECHAWLA 21-nov-03 3262519 : close new cursors
3379 IF l_okclines_csr%ISOPEN THEN
3380 CLOSE l_okclines_csr;
3381 END IF;
3382
3383 IF l_dealtype_csr%ISOPEN THEN
3384 CLOSE l_dealtype_csr;
3385 END IF;
3386
3387 IF l_linesfullv_csr%ISOPEN THEN
3388 CLOSE l_linesfullv_csr;
3389 END IF;
3390 -- SECHAWLA 21-nov-03 3262519 : end
3391
3392 --SECHAWLA 10-FEB-06 5016156
3393 IF l_offlseassettrx_csr%ISOPEN THEN
3394 CLOSE l_offlseassettrx_csr;
3395 END IF;
3396
3397 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3398 (
3399 l_api_name,
3400 G_PKG_NAME,
3401 'OKC_API.G_RET_STS_UNEXP_ERROR',
3402 x_msg_count,
3403 x_msg_data,
3404 '_PVT'
3405 );
3406 WHEN OTHERS THEN
3407
3408 IF l_okxassetlines_csr%ISOPEN THEN
3409 CLOSE l_okxassetlines_csr;
3410 END IF;
3411
3412 IF l_disthist_csr%ISOPEN THEN
3413 CLOSE l_disthist_csr;
3414 END IF;
3415
3416 IF l_assettrx_csr%ISOPEN THEN
3417 CLOSE l_assettrx_csr;
3418 END IF;
3419
3420 IF l_itemlocation_csr%ISOPEN THEN
3421 CLOSE l_itemlocation_csr;
3422 END IF;
3423
3424 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
3425 IF l_periodofaddition_csr%ISOPEN THEN
3426 CLOSE l_periodofaddition_csr;
3427 END IF;
3428
3429 IF l_bookcontrols_csr%ISOPEN THEN
3430 CLOSE l_bookcontrols_csr;
3431 END IF;
3432
3433 -- SECHAWLA 21-nov-03 3262519 : close new cursors
3434 IF l_okclines_csr%ISOPEN THEN
3435 CLOSE l_okclines_csr;
3436 END IF;
3437
3438 IF l_dealtype_csr%ISOPEN THEN
3439 CLOSE l_dealtype_csr;
3440 END IF;
3441
3442 IF l_linesfullv_csr%ISOPEN THEN
3443 CLOSE l_linesfullv_csr;
3444 END IF;
3445 -- SECHAWLA 21-nov-03 3262519 : end
3446
3447 --SECHAWLA 10-FEB-06 5016156
3448 IF l_offlseassettrx_csr%ISOPEN THEN
3449 CLOSE l_offlseassettrx_csr;
3450 END IF;
3451
3452 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3453 (
3454 l_api_name,
3455 G_PKG_NAME,
3456 'OTHERS',
3457 x_msg_count,
3458 x_msg_data,
3459 '_PVT'
3460 );
3461 END dispose_asset;
3462
3463 -- Start of comments
3464 --
3465 -- Procedure Name : dispose_asset
3466 -- Description : This procedure is used to retire an asset in FA, from Remarketing. It checks if the asset is
3467 -- to be fully or partially retired , based upon the ordered_quantity and then calls the appropriate routine to
3468 -- retire the asset. It then stores the disposition transactions in OKL tables, calls accounting
3469 -- engine and then finally cancels all pending transactions in OKL tables for this asset
3470 -- Business Rules :
3471 -- Parameters : p_order_header_id - Order Header ID
3472 --
3473 -- Version : 1.0
3474 -- History : SECHAWLA 10-DEC-02 Bug # 2701440
3475 -- Modified CURSOR l_okxassetlines_csr to select all the tax books that an asset belongs to,
3476 -- in addition to the Fixed Asset Information.
3477 -- SECHAWLA 23-DEC-02 Bug # 2701440
3478 -- Modified logic to perform cost retirement instead of unit retirement for tax books
3479 -- SECHAWLA 03-JAN-03 Bug # 2683876
3480 -- Modified logic to send currency code while creating/updating amounts columns in txl assets
3481 -- SECHAWLA 13-JAN-03 Bug # 2701440 Modified logic to perform :
3482 -- 1) full tax book retirement when the corporate book gets fully retired. This is to
3483 -- take care of the scenario where tax book cost is more than the corporate book cost
3484 -- 2) full tax book retirement when the corp book is not fully retired but tax book does not
3485 -- have enough cost. This takes care of the scenario where tax book cost is less than the corp book cost
3486 -- SECHAWLA 05-FEB-03 Bug # 2781557
3487 -- Moved the logic to check if the asset was added in the current open period, from individual cost and unit
3488 -- retirement procedures to this procedure.
3489 -- SECHAWLA 11-MAR-03
3490 -- Removed the validation for NULL order quantity, as it is being NVLed to 1. Added a validation
3491 -- for unit_selling_price. If null, assigned 0 to sale_amount
3492 -- SECHAWLA 03-JUN-03 2999419: Use the retirement prorate convention set in Oracle
3493 -- Assets for a particular asset and book, instead of using the constant value "MID-MONTH"
3494 -- RMUNJULU 11-SEP-03 3061751 Added code for SERVICE_K_INTEGRATION
3495 -- SECHAWLA 21-NOV-03 3262519 Update the asset cost with residual value, for DF and Sales lease,
3496 -- before retiring the asset
3497 -- SECHAWLA 21-OCT-04 3924244 Modified procedure to work on order line instead of header
3498 -- girao 18-Jan-2005 4106216 NVL the residual value in l_linesfullv_csr
3499 -- SECHAWLA 10-FEB-06 5016156 in case of termination w/o purchase, asset cost should
3500 -- be updated with NIV (not RV), through Off-lease transactions
3501
3502 -- End of comments
3503 PROCEDURE dispose_asset ( p_api_version IN NUMBER,
3504 p_init_msg_list IN VARCHAR2,
3505 x_return_status OUT NOCOPY VARCHAR2,
3506 x_msg_count OUT NOCOPY NUMBER,
3507 x_msg_data OUT NOCOPY VARCHAR2,
3508 p_order_line_id IN NUMBER -- SECHAWLA 21-OCT-04 3924244
3509 ) IS
3510
3511 SUBTYPE thpv_rec_type IS OKL_TRX_ASSETS_PUB.thpv_rec_type;
3512 SUBTYPE tlpv_rec_type IS OKL_TXL_ASSETS_PUB.tlpv_rec_type;
3513
3514 -- This cursor is used to validate Header ID
3515 CURSOR l_orderheaders_csr(p_header_id NUMBER) IS
3516 SELECT order_number
3517 FROM oe_order_headers_all
3518 WHERE header_id = p_header_id;
3519
3520 -- This cursor is used to get the information about all the line items corresponding to an Order
3521 CURSOR l_orderlines_csr(p_line_id NUMBER) IS -- -- SECHAWLA 21-OCT-04 3924244
3522 SELECT header_id, inventory_item_id, nvl(ordered_quantity,1) ordered_quantity, ship_from_org_id, unit_selling_price
3523 FROM oe_order_lines_all
3524 WHERE line_id = p_line_id;
3525
3526 -- This curosr is used to get the financial asset id for an inventory item
3527 --Changed the cusrsor to use directly base tables instead uv for performance
3528 CURSOR l_assetreturn_csr(p_inventory_item_id NUMBER) IS
3529 SELECT kle.id kle_id,
3530 cim.number_of_items quantity,
3531 -- RRAVIKIR Legal Entity changes
3532 oar.legal_entity_id
3533 -- Legal Entity changes End
3534 FROM okc_k_lines_b kle,
3535 okc_k_headers_all_b okc,
3536 okl_asset_returns_all_b oar,
3537 mtl_system_items_b msi,
3538 okc_k_lines_b kle2,
3539 okc_line_styles_b lse,
3540 okc_k_items cim,
3541 okl_system_params osp
3542 WHERE okc.id = kle.chr_id
3543 AND oar.kle_id = kle.id
3544 AND oar.imr_id = msi.inventory_item_id
3545 AND msi.organization_id = osp.remk_organization_id
3546 AND kle.id = kle2.cle_id
3547 AND kle2.lse_id = lse.id
3548 AND lse.lty_code = 'ITEM'
3549 AND kle2.id = cim.cle_id
3550 AND oar.imr_id = p_inventory_item_id;
3551
3552
3553
3554 -- SECHAWLA Bug # 2701440 :
3555 -- Modified this cursor to select all the tax books that an asset belongs to, in addition to the Fixed Asset Information
3556
3557 --SECHAWLA 23_DEC-02 Bug # 2701440
3558 --Added Order By clause to selct CORPORATE Book first
3559
3560 --SECHAWLA 13-JAN-03 Bug # 2701440
3561 --Changed the cursor to select cost columns from fa_books instead of okx_asset_lines_v, as the latter has info for corporate book only
3562
3563 --SECHAWLA 06-JUN-03 Bug # 2999419
3564 --Added prorate_convention_code to the Select clause
3565 CURSOR l_okxassetlines_csr(p_kle_id IN NUMBER) IS
3566 SELECT o.asset_id, o.asset_number, o.corporate_book, a.cost, o.depreciation_category, a.original_cost, o.current_units,
3567 o.dnz_chr_id ,a.book_type_code, b.book_class, a.prorate_convention_code
3568 FROM okx_asset_lines_v o, fa_books a, fa_book_controls b
3569 WHERE o.parent_line_id = p_kle_id
3570 AND o.asset_id = a.asset_id
3571 AND a.book_type_code = b.book_type_code
3572 AND a.date_ineffective IS NULL
3573 AND a.transaction_header_id_out IS NULL
3574 ORDER BY book_class;
3575
3576 --SECHAWLA 23_DEC-02 Bug # 2701440 : Added this cursor to get the cost retired, populated after the retirement of
3577 -- asset from the corporate book. We need this cost to perform cost retirement of the same asset in the TAX book
3578
3579 /* SECHAWLA 21-NOV-03 3262519 : This curosr is not required. Cost to be retired from the tax book should be
3580 -- calculated using tax book cost and not the corporate book cost, as teh 2 costs can be different
3581
3582 --SECHAWLA 13-JAN-03 Bug # 2701440 : Added Order By Clause to select the latest retirement record first
3583 CURSOR l_faretirement_csr(p_asset_id IN NUMBER, p_book_type_code IN VARCHAR2) IS
3584 SELECT cost_retired
3585 FROM fa_retirements
3586 WHERE asset_id = p_asset_id
3587 AND book_type_code = p_book_type_code
3588 ORDER BY last_update_date DESC;
3589 */
3590
3591 -- This cursor is used to get all the active distributions for an asset
3592 CURSOR l_disthist_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2) IS
3593 SELECT distribution_id, units_assigned, retirement_id, transaction_units
3594 FROM fa_distribution_history
3595 WHERE asset_id = p_asset_id
3596 AND book_type_code = p_book_type_code
3597 AND date_ineffective IS NULL
3598 AND transaction_header_id_out IS NULL
3599 --AND retirement_id IS NULL
3600 ORDER BY last_update_date;
3601
3602 -- This cursor is used to get all the pending transactions for an asset. These transactions are to be cancelled
3603 -- once the asset is retired.
3604 CURSOR l_assettrx_csr(p_financial_asset_id NUMBER) IS
3605 SELECT h.id
3606 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l
3607 WHERE h.id = l.tas_id
3608 AND h.tsu_code IN ('ENTERED', 'ERROR')
3609 AND l.kle_id = p_financial_asset_id;
3610
3611 -- This curosr is used to get all the instances for a Financial asset
3612 --Query changed to use base tables instead uv for performance
3613 CURSOR l_itemlocation_csr(p_financial_asset_id NUMBER) IS
3614 SELECT cii.instance_id instance_id, cii.active_end_date instance_end_date
3615 FROM okc_k_headers_b okhv,
3616 okc_k_lines_b kle_fa,
3617 okc_k_lines_tl klet_fa,
3618 okc_line_styles_b lse_fa,
3619 okc_k_lines_b kle_il,
3620 okc_line_styles_b lse_il,
3621 okc_k_lines_b kle_ib,
3622 okc_line_styles_b lse_ib,
3623 okc_k_items ite,
3624 csi_item_instances cii
3625 WHERE kle_fa.id = klet_fa.id
3626 AND klet_fa.language = USERENV('LANG')
3627 AND kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id
3628 AND lse_fa.lty_code = 'FREE_FORM1'
3629 AND kle_il.cle_id = kle_fa.id
3630 AND lse_il.id = kle_il.lse_id
3631 AND lse_il.lty_code = 'FREE_FORM2'
3632 AND kle_ib.cle_id = kle_il.id
3633 AND lse_ib.id = kle_ib.lse_id
3634 AND lse_ib.lty_code = 'INST_ITEM'
3635 AND ite.cle_id = kle_ib.id
3636 AND ite.jtot_object1_code = 'OKX_IB_ITEM'
3637 AND cii.instance_id = ite.object1_id1
3638 AND kle_fa.id = p_financial_asset_id;
3639
3640
3641 --SECHAWLA 05-FEB-03 Bug # 2781557 : new cursor
3642 -- This cursor is used to find out the period_of_addtion for the asset that is to be retired
3643 CURSOR l_periodofaddition_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2, p_period_open_date DATE) IS
3644 SELECT count(*)
3645 FROM fa_transaction_headers th
3646 WHERE th.asset_id = p_asset_id
3647 AND th.book_type_code = p_book_type_code
3648 AND th.transaction_type_code = 'ADDITION'
3649 AND th.date_effective > p_period_open_date;
3650
3651 --SECHAWLA 05-FEB-03 Bug # 2781557 : new cursor
3652 -- This cursor is used temporarily to get the fiscal year name till FA API is fixed
3653 CURSOR l_bookcontrols_csr(p_book_type_code VARCHAR2) IS
3654 SELECT fiscal_year_name
3655 FROM fa_book_controls
3656 WHERE book_type_code = p_book_type_code;
3657
3658 --SECHAWLA 21-NOV-2003 3262519 : Added the following cursor
3659
3660 -- get the deal type from the contract
3661 CURSOR l_dealtype_csr(p_financial_asset_id IN NUMBER) IS
3662 SELECT lkhr.id, lkhr.deal_type, khr.contract_number
3663 FROM okl_k_headers lkhr, okc_k_lines_b cle, okc_k_headers_b khr
3664 WHERE khr.id = cle.chr_id
3665 AND lkhr.id = khr.id
3666 AND cle.id = p_financial_asset_id;
3667
3668 -- get the residual value for the fin asset
3669 CURSOR l_linesfullv_csr(p_fin_asset_id IN NUMBER) IS
3670 SELECT name, NVL(residual_value,0) --girao bug 4106216 NVL the residual value
3671 FROM okl_k_lines_full_v
3672 WHERE id = p_fin_asset_id;
3673
3674 --SECHAWLA 10-FEB-06 5016156
3675 CURSOR l_offlseassettrx_csr(cp_trx_date IN DATE, cp_asset_number IN VARCHAR2) IS
3676 SELECT h.tsu_code, h.tas_type, h.date_trans_occurred, l.dnz_asset_id,
3677 l.asset_number, l.kle_id ,l.DNZ_KHR_ID
3678 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l
3679 WHERE h.id = l.tas_id
3680 AND h.date_trans_occurred <= cp_trx_date
3681 AND h.tas_type in ('AMT','AUD','AUS')
3682 AND l.asset_number = cp_asset_number;
3683
3684 l_trx_status VARCHAR2(30);
3685 --SECHAWLA 10-FEB-06 5016156 : end
3686
3687
3688 l_deal_type VARCHAR2(30);
3689 l_chr_id NUMBER;
3690 l_contract_number VARCHAR2(120);
3691 l_rulv_rec okl_rule_pub.rulv_rec_type;
3692 l_tax_owner VARCHAR2(10);
3693 l_delta_cost NUMBER;
3694 l_residual_value NUMBER;
3695 l_name VARCHAR2(150);
3696 l_cost NUMBER;
3697 --SECHAWLA 21-NOV-2003 3262519 : end new declarations
3698
3699
3700 l_dist_quantity NUMBER;
3701 l_dist_tbl asset_dist_tbl;
3702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3703 l_order_number NUMBER;
3704 i NUMBER;
3705 l_kle_id NUMBER;
3706 l_quantity NUMBER;
3707 l_sale_amount NUMBER;
3708 l_trx_type VARCHAR2(30) := 'Asset Disposition';
3709 l_trx_name VARCHAR2(30) := 'ASSET_DISPOSITION';
3710 l_api_name CONSTANT VARCHAR2(30) := 'dispose_asset';
3711 l_try_id OKL_TRX_TYPES_V.id%TYPE;
3712 l_sys_date DATE;
3713
3714 lp_thpv_rec thpv_rec_type;
3715 lp_thpv_empty_rec thpv_rec_type;
3716 lp_tlpv_empty_rec tlpv_rec_type;
3717 lx_thpv_rec thpv_rec_type;
3718 lp_tlpv_rec tlpv_rec_type;
3719 lx_tlpv_rec tlpv_rec_type;
3720 l_api_version CONSTANT NUMBER := 1;
3721 instance_counter NUMBER;
3722 l_already_retired VARCHAR2(1):= 'N';
3723 l_retired_quantity NUMBER;
3724 l_non_retired_quantity NUMBER;
3725 l_remaining_units NUMBER;
3726 l_retired_dist_units NUMBER;
3727 l_units_to_be_retired NUMBER;
3728 lx_total_amount NUMBER;
3729 l_units_retired NUMBER;
3730
3731 --SECHAWLA 23_DEC-02 Bug # 2701440: new declarations
3732 l_cost_retired NUMBER;
3733
3734 --SECHAWLA 03-JAN-03 Bug # 2683876 : new declaration
3735 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
3736
3737 --SECHAWLA 05-FEB-03 Bug # 2781557 : new declarations
3738 l_fiscal_year_name VARCHAR2(30);
3739 l_period_rec FA_API_TYPES.period_rec_type;
3740 l_count NUMBER;
3741 l_period_of_addition VARCHAR2(1);
3742
3743 -- RMUNJULU 3061751
3744 l_service_int_needed VARCHAR2(1) := 'N';
3745
3746 --SECHAWLA 21-OCT-04 3924244
3747 l_header_id NUMBER;
3748 l_inventory_item_id NUMBER;
3749 l_ordered_quantity NUMBER;
3750 l_ship_from_org_id NUMBER;
3751 l_unit_selling_price NUMBER;
3752
3753 --SECHAWLA 15-DEC-04 4028371 New Declarations
3754 l_fa_trx_date DATE;
3755
3756 -- Legal Entity changes
3757 l_legal_entity_id NUMBER;
3758 -- Legal Entity changes End
3759 --akrangan sla populate sources cr start
3760 l_fxhv_rec okl_fxh_pvt.fxhv_rec_type;
3761 l_fxlv_rec okl_fxl_pvt.fxlv_rec_type;
3762 --akrangan sla populate sources cr end
3763
3764 BEGIN
3765
3766 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3767 G_PKG_NAME,
3768 p_init_msg_list,
3769 l_api_version,
3770 p_api_version,
3771 '_PVT',
3772 x_return_status);
3773
3774 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3775 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3776 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3777 RAISE OKC_API.G_EXCEPTION_ERROR;
3778 END IF;
3779
3780 -- Get the sysdate
3781 SELECT SYSDATE INTO l_sys_date FROM DUAL;
3782
3783 -- SECHAWLA 21-OCT-04 3924244
3784 IF p_order_line_Id IS NULL OR p_order_line_Id = OKL_API.G_MISS_NUM THEN
3785 x_return_status := OKL_API.G_RET_STS_ERROR;
3786 -- Order Line ID is required
3787 OKL_API.set_message( p_app_name => 'OKC',
3788 p_msg_name => G_REQUIRED_VALUE,
3789 p_token1 => G_COL_NAME_TOKEN,
3790 p_token1_value => 'ORDER_LINE_ID');
3791 RAISE okc_api.G_EXCEPTION_ERROR;
3792 END IF;
3793
3794 /* -- SECHAWLA 21-OCT-04 3924244
3795 OPEN l_orderheaders_csr(p_order_header_Id);
3796 FETCH l_orderheaders_csr INTO l_order_number;
3797 IF l_orderheaders_csr%NOTFOUND THEN
3798 x_return_status := OKL_API.G_RET_STS_ERROR;
3799 -- Order Header ID is invalid
3800 OKL_API.set_message( p_app_name => 'OKC',
3801 p_msg_name => G_INVALID_VALUE,
3802 p_token1 => G_COL_NAME_TOKEN,
3803 p_token1_value => 'ORDER_HEADER_ID');
3804 RAISE okc_api.G_EXCEPTION_ERROR;
3805 END IF;
3806 CLOSE l_orderheaders_csr;
3807 */
3808
3809 -- SECHAWLA 21-OCT-04 3924244 : begin
3810 OPEN l_orderlines_csr(p_order_line_Id);
3811 FETCH l_orderlines_csr INTO l_header_id, l_inventory_item_id, l_ordered_quantity,
3812 l_ship_from_org_id, l_unit_selling_price;
3813 IF l_orderlines_csr%NOTFOUND THEN
3814 x_return_status := OKL_API.G_RET_STS_ERROR;
3815 -- Order Line ID is invalid
3816 OKL_API.set_message( p_app_name => 'OKC',
3817 p_msg_name => G_INVALID_VALUE,
3818 p_token1 => G_COL_NAME_TOKEN,
3819 p_token1_value => 'ORDER_LINE_ID');
3820 RAISE okc_api.G_EXCEPTION_ERROR;
3821 END IF;
3822 CLOSE l_orderlines_csr;
3823
3824 OPEN l_orderheaders_csr(l_header_id);
3825 FETCH l_orderheaders_csr INTO l_order_number;
3826 CLOSE l_orderheaders_csr;
3827 -- SECHAWLA 21-OCT-04 3924244 : end
3828
3829
3830 -- SECHAWLA 21-OCT-04 3924244 : Commented out the loop and changed the cursor attribute references to variable references
3831 -- loop thru all the line items for a given order, validate the data and then reduce the quantity of each line item
3832 --FOR l_orderlines_rec IN l_orderlines_csr(p_order_header_id) LOOP
3833
3834
3835
3836 IF l_ordered_quantity < 0 THEN -- SECHAWLA 21-OCT-04 3924244
3837 x_return_status := OKL_API.G_RET_STS_ERROR;
3838 -- ordered quantity is invalid
3839 OKC_API.set_message( p_app_name => 'OKC',
3840 p_msg_name => G_INVALID_VALUE,
3841 p_token1 => G_COL_NAME_TOKEN,
3842 p_token1_value => 'ORDERED_QUANTITY');
3843
3844
3845 RAISE OKC_API.G_EXCEPTION_ERROR;
3846 END IF;
3847
3848 IF trunc(l_ordered_quantity) <> l_ordered_quantity THEN -- SECHAWLA 21-OCT-04 3924244
3849 x_return_status := OKL_API.G_RET_STS_ERROR;
3850 -- Ordered quantity should be a whole number.
3851 OKC_API.set_message( p_app_name => 'OKL',
3852 p_msg_name => 'OKL_AM_WHOLE_QTY_ERR');
3853 RAISE OKC_API.G_EXCEPTION_ERROR;
3854 END IF;
3855
3856
3857
3858 OPEN l_assetreturn_csr(l_inventory_item_id); -- SECHAWLA 21-OCT-04 3924244
3859 FETCH l_assetreturn_csr INTO l_kle_id, l_quantity, l_legal_entity_id; -- RRAVIKIR legal_entity_id added to the Fetch cursor
3860
3861
3862 IF l_assetreturn_csr%NOTFOUND THEN
3863 x_return_status := OKL_API.G_RET_STS_ERROR;
3864 -- Inventory Item for the order ORDER_NUMBER is not defined in Asset Returns.
3865 OKL_API.set_message( p_app_name => 'OKL',
3866 p_msg_name => 'OKL_AM_NO_ASSET_RETURN',
3867 p_token1 => 'ORDER_NUMBER',
3868 p_token1_value => l_order_number);
3869 RAISE okc_api.G_EXCEPTION_ERROR;
3870 END IF;
3871
3872 IF l_quantity IS NULL OR l_quantity = OKL_API.G_MISS_NUM THEN
3873 x_return_status := OKL_API.G_RET_STS_ERROR;
3874 -- Quantity is required
3875 OKL_API.set_message( p_app_name => 'OKC',
3876 p_msg_name => G_REQUIRED_VALUE,
3877 p_token1 => G_COL_NAME_TOKEN,
3878 p_token1_value => 'ASSET_RETURN_QUANTITY');
3879 RAISE okc_api.G_EXCEPTION_ERROR;
3880 END IF;
3881
3882 -- RRAVIKIR Legal Entity Changes
3883 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
3884 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
3885 p_msg_name => g_required_value,
3886 p_token1 => g_col_name_token,
3887 p_token1_value => 'legal_entity_id');
3888 RAISE OKC_API.G_EXCEPTION_ERROR;
3889 END IF;
3890 -- Legal Entity Changes End
3891
3892 IF l_quantity < 0 THEN
3893 x_return_status := OKL_API.G_RET_STS_ERROR;
3894 -- Quantity is invalid
3895 OKL_API.set_message( p_app_name => 'OKC',
3896 p_msg_name => G_INVALID_VALUE,
3897 p_token1 => G_COL_NAME_TOKEN,
3898 p_token1_value => 'ASSET_RETURN_QUANTITY');
3899 RAISE okc_api.G_EXCEPTION_ERROR;
3900 END IF;
3901
3902 CLOSE l_assetreturn_csr;
3903
3904
3905 l_already_retired := 'N';
3906
3907 --SECHAWLA 21-NOV-2003 3262519 : Added the following code to get the deal type and tax owner
3908
3909 -- get the deal type from the contract
3910 OPEN l_dealtype_csr(l_kle_id);
3911 FETCH l_dealtype_csr INTO l_chr_id, l_deal_type, l_contract_number;
3912 IF l_dealtype_csr%NOTFOUND THEN
3913 x_return_status := OKL_API.G_RET_STS_ERROR;
3914 -- chr id is invalid
3915 OKC_API.set_message( p_app_name => 'OKC',
3916 p_msg_name => G_INVALID_VALUE,
3917 p_token1 => G_COL_NAME_TOKEN,
3918 p_token1_value => 'CHR_ID');
3919
3920 RAISE OKC_API.G_EXCEPTION_ERROR;
3921 END IF;
3922 CLOSE l_dealtype_csr;
3923
3924 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
3925 -- get the tax owner (LESSOR/LESSEE) for the contract
3926
3927 okl_am_util_pvt.get_rule_record(p_rgd_code => 'LATOWN'
3928 ,p_rdf_code =>'LATOWN'
3929 ,p_chr_id => l_chr_id
3930 ,p_cle_id => NULL
3931 ,x_rulv_rec => l_rulv_rec
3932 ,x_return_status => x_return_status
3933 ,x_msg_count => x_msg_count
3934 ,x_msg_data => x_msg_data);
3935
3936 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3937 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3938 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3939 RAISE OKC_API.G_EXCEPTION_ERROR;
3940 END IF;
3941
3942 -- check if tax owner is defined
3943 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
3944
3945 x_return_status := OKL_API.G_RET_STS_ERROR;
3946 -- tax owner is not defined for contract CONTRACT_NUMBER.
3947 OKL_API.set_message( p_app_name => 'OKL',
3948 p_msg_name => 'OKL_AM_NO_TAX_OWNER',
3949 p_token1 => 'CONTRACT_NUMBER',
3950 p_token1_value => l_contract_number);
3951 RAISE OKC_API.G_EXCEPTION_ERROR;
3952
3953 ELSE
3954 -- l_rulv_rec.RULE_INFORMATION1 will contain the value 'LESSEE' or 'LESSOR'
3955 l_tax_owner := l_rulv_rec.RULE_INFORMATION1;
3956 END IF;
3957
3958 -- get the residual value of the fin asset
3959 OPEN l_linesfullv_csr(l_kle_id);
3960 FETCH l_linesfullv_csr INTO l_name, l_residual_value;
3961 CLOSE l_linesfullv_csr;
3962
3963 IF l_residual_value IS NULL THEN
3964 x_return_status := OKL_API.G_RET_STS_ERROR;
3965 -- Residual value is not defined for the asset
3966 OKC_API.set_message( p_app_name => 'OKL',
3967 p_msg_name => 'OKL_AM_NO_RESIDUAL_VALUE',
3968 p_token1 => 'ASSET_NUMBER',
3969 p_token1_value => l_name);
3970
3971
3972 RAISE OKC_API.G_EXCEPTION_ERROR;
3973 END IF;
3974
3975 IF l_residual_value < 0 THEN
3976 x_return_status := OKL_API.G_RET_STS_ERROR;
3977 -- Residual value is negative for the asset
3978 OKC_API.set_message( p_app_name => 'OKL',
3979 p_msg_name => 'OKL_AM_INVALID_RESIDUAL',
3980 p_token1 => 'ASSET_NUMBER',
3981 p_token1_value => l_name);
3982
3983
3984 RAISE OKC_API.G_EXCEPTION_ERROR;
3985 END IF;
3986 END IF;
3987
3988
3989
3990 --SECHAWLA 21-NOV-2003 3262519 : end new code
3991
3992
3993 -- SECHAWLA Bug # 2701440 : Changed OPEN, FETCH to a curosr FOR LOOP, as this cursor now has multiple rows
3994 -- for a given asset id : one row for the corporate book and one or more rows for the tax books
3995 FOR l_okxassetlines_rec IN l_okxassetlines_csr(l_kle_id) LOOP
3996
3997 --SECHAWLA 21-NOV-2003 3262519 : Calculate delta cost
3998 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
3999
4000
4001 --SECHAWLA 10-FEB-06 5016156 Check if any off-lease transactions exist for the asset
4002 -- This will tell if it is termination with purchase or without purchase
4003 l_trx_status := NULL;
4004 FOR l_offlseassettrx_rec IN l_offlseassettrx_csr(l_sys_date, l_name) LOOP
4005 l_trx_status := l_offlseassettrx_rec.tsu_code;
4006 IF l_trx_status IN ('ENTERED','ERROR','CANCELED') THEN
4007 EXIT;
4008 END IF;
4009 END LOOP;
4010
4011
4012
4013 IF l_trx_status IS NULL THEN -- This means off-lease trx don't exist. It is termination with purchase
4014 --SECHAWLA 10-FEB-06 5016156 : end
4015 l_delta_cost := l_residual_value - l_okxassetlines_rec.cost;
4016
4017 --SECHAWLA 10-FEB-06 5016156 begin
4018 ELSIF l_trx_status IN ('ENTERED','ERROR') THEN -- if any trx has this status
4019 x_return_status := OKL_API.G_RET_STS_ERROR;
4020 OKL_API.set_message( p_app_name => 'OKL',
4021 p_msg_name => 'OKL_AM_PENDING_OFFLEASE',
4022 p_token1 => 'ASSET_NUMBER',
4023 p_token1_value => l_name);
4024 RAISE OKC_API.G_EXCEPTION_ERROR;
4025
4026
4027 ELSIF l_trx_status IN ('PROCESSED','CANCELED') THEN
4028 l_delta_cost := 0; -- no cost update required, as cost has already been updated thru off lease trx
4029 END IF; -- or off-lease trx has been canceled
4030 --SECHAWLA 10-FEB-06 5016156 : end
4031
4032 END IF;
4033
4034 --SECHAWLA 05-FEB-03 Bug # 2781557 : Moved the following code from do_full_units_retirement, as the check
4035 -- whether the asset is added in the current open period, needs to be done at this stage.
4036
4037 -- This piece of code is included temporarily as a work around , since FA API has errors
4038 -- Set the Fiscal Year name in teh cache,if not already set
4039 -- IF fa_cache_pkg.fazcbc_record.fiscal_year_name IS NULL THEN
4040 OPEN l_bookcontrols_csr(l_okxassetlines_rec.book_type_code);
4041 FETCH l_bookcontrols_csr INTO l_fiscal_year_name;
4042 IF l_bookcontrols_csr%NOTFOUND OR l_fiscal_year_name IS NULL THEN
4043 x_return_status := OKL_API.G_RET_STS_ERROR;
4044 -- Fiscal Year Name is required
4045 OKC_API.set_message( p_app_name => 'OKC',
4046 p_msg_name => G_REQUIRED_VALUE,
4047 p_token1 => G_COL_NAME_TOKEN,
4048 p_token1_value => 'Fiscal Year Name');
4049
4050
4051 RAISE OKC_API.G_EXCEPTION_ERROR;
4052 END IF;
4053 CLOSE l_bookcontrols_csr;
4054 fa_cache_pkg.fazcbc_record.fiscal_year_name := l_fiscal_year_name;
4055 -- END IF;
4056
4057
4058 IF NOT FA_UTIL_PVT.get_period_rec
4059 (
4060 p_book => l_okxassetlines_rec.book_type_code,
4061 p_effective_date => NULL,
4062 x_period_rec => l_period_rec
4063 ) THEN
4064
4065 x_return_status := OKC_API.G_RET_STS_ERROR;
4066 --Error getting current open period for the book BOOK_TYPE_CODE.
4067 OKL_API.set_message(
4068 p_app_name => 'OKL',
4069 p_msg_name => 'OKL_AM_OPEN_PERIOD_ERR',
4070 p_token1 => 'BOOK_CLASS',
4071 p_token1_value => lower(l_okxassetlines_rec.book_class),
4072 p_token2 => 'BOOK_TYPE_CODE',
4073 p_token2_value => l_okxassetlines_rec.book_type_code
4074 );
4075
4076
4077 RAISE OKC_API.G_EXCEPTION_ERROR;
4078 END IF;
4079
4080 --- check period of addition. If 'N' then run retirements
4081 OPEN l_periodofaddition_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code,l_period_rec.period_open_date);
4082 FETCH l_periodofaddition_csr INTO l_count;
4083 CLOSE l_periodofaddition_csr;
4084
4085 IF (l_count <> 0) THEN
4086 l_period_of_addition := 'Y';
4087 ELSE
4088 l_period_of_addition := 'N';
4089 END IF;
4090
4091 IF l_period_of_addition = 'Y' THEN
4092 -- Can nor retire asset ASSET_NUMBER as the asset was added to the book
4093 -- in the current open period. Please retire the asset manually.
4094 x_return_status := OKC_API.G_RET_STS_ERROR;
4095
4096 OKL_API.set_message( p_app_name => 'OKL',
4097 p_msg_name => 'OKL_AM_RETIRE_MANUALLY',
4098 p_token1 => 'ASSET_NUMBER',
4099 p_token1_value => l_okxassetlines_rec.asset_number,
4100 p_token2 => 'BOOK_CLASS',
4101 p_token2_value => lower(l_okxassetlines_rec.book_class),
4102 p_token3 => 'BOOK_TYPE_CODE',
4103 p_token3_value => l_okxassetlines_rec.book_type_code);
4104
4105 RAISE OKC_API.G_EXCEPTION_ERROR;
4106
4107 END IF;
4108
4109 ---------- SECHAWLA 05-FEB-03 Bug # 2781557 : end moved code ----------------
4110
4111 /* -- ansethur for Bug:5664106 Start
4112 -- SECHAWLA 03-JUN-03 Bug 2999419 : Added the following validation
4113 IF l_okxassetlines_rec.prorate_convention_code IS NULL THEN
4114 x_return_status := OKL_API.G_RET_STS_ERROR;
4115 -- Unable to find retirement prorate convention for asset ASSET_NUMBER and book BOOK_TYPE_CODE.
4116 OKC_API.set_message( p_app_name => 'OKL',
4117 p_msg_name => 'OKL_AM_NO_PRORATE_CONVENTION',
4118 p_token1 => 'ASSET_NUMBER',
4119 p_token1_value => l_okxassetlines_rec.asset_number,
4120 p_token2 => 'BOOK_CLASS',
4121 p_token2_value => l_okxassetlines_rec.book_class,
4122 p_token3 => 'BOOK_TYPE_CODE',
4123 p_token3_value => l_okxassetlines_rec.book_type_code);
4124 RAISE OKC_API.G_EXCEPTION_ERROR;
4125 END IF;
4126 -- SECHAWLA 03-JUN-03 Bug 2999419: end new code
4127 */ -- ansethur for Bug:5664106 End
4128
4129
4130
4131 --SECHAWLA 11-MAR-03 : Added the following validation
4132 IF l_unit_selling_price IS NULL THEN -- SECHAWLA 21-OCT-04 3924244
4133 l_sale_amount := 0;
4134 ELSE
4135 l_sale_amount := l_ordered_quantity * l_unit_selling_price; -- SECHAWLA 21-OCT-04 3924244
4136 END IF;
4137
4138 IF l_ordered_quantity = l_quantity THEN -- SECHAWLA 21-OCT-04 3924244
4139 -- user sent request for full retirement, since all the units were sold
4140
4141
4142 -- check if asset has already been fully/partially retired .
4143 l_retired_quantity := 0;
4144 l_non_retired_quantity := 0;
4145 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4146 IF l_disthist_rec.retirement_id IS NOT NULL THEN
4147 l_retired_quantity := l_retired_quantity + abs(l_disthist_rec.transaction_units);
4148 ELSE
4149 l_non_retired_quantity := l_non_retired_quantity + l_disthist_rec.units_assigned;
4150 END IF;
4151 END LOOP;
4152
4153 IF l_retired_quantity = 0 AND l_non_retired_quantity > 0 THEN
4154 -- user requested for full retirement and none of the units have been retired so far
4155 -- perform full retirement
4156 IF l_non_retired_quantity = l_quantity THEN --distribution qty = orginal asset return qty
4157
4158 -- we are passing the total number of units and not the cost, for full retirements, because for
4159 -- Direct Finance Lease, okx_asset_lines_v, gives OEC as the cost. FA Retirements compares this cost with
4160 --cost in fa_books. These 2 costs can be different, which will give error. So we are using units instead
4161 -- of cost to avoid that validation check.
4162
4163 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4164 do_full_units_retirement(
4165 p_api_version => p_api_version,
4166 p_init_msg_list => OKC_API.G_FALSE,
4167 p_tax_owner => l_tax_owner,
4168 p_delta_cost => l_delta_cost,
4169 p_asset_id => l_okxassetlines_rec.asset_id,
4170 p_asset_number => l_okxassetlines_rec.asset_number,
4171 p_proceeds_of_sale => l_sale_amount,
4172 --p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4173 p_book_type_code => l_okxassetlines_rec.book_type_code,
4174 --p_cost => l_cost,
4175 p_units => l_quantity,
4176 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4177 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4178 x_msg_count => x_msg_count,
4179 x_msg_data => x_msg_data,
4180 x_return_status => x_return_status);
4181
4182 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4183 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4184 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4185 RAISE OKC_API.G_EXCEPTION_ERROR;
4186 END IF;
4187
4188 l_units_retired := l_quantity;
4189 ELSE -- distribution qty is either less or more than the original asset return qty
4190 -- and hence we need to consider this as partial retirement, even though the sold
4191 -- quantity = original asset return quantity
4192
4193 IF l_non_retired_quantity > l_quantity THEN
4194 l_units_to_be_retired := l_quantity;
4195 ELSE
4196 l_units_to_be_retired := l_non_retired_quantity;
4197 END IF;
4198
4199
4200 -- l_dist_quantity := l_quantity;
4201 l_dist_quantity := l_units_to_be_retired;
4202 i := 0;
4203
4204
4205 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
4206 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
4207 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4208 -- than requested units then retire that distribution fully and move to next distribution for remaining
4209 -- units, until all the requested units have been retired.
4210
4211
4212 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4213 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4214 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4215 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4216 l_dist_quantity := 0;
4217 EXIT;
4218 ELSE
4219 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4220 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4221 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4222 END IF;
4223 i := i + 1;
4224 END LOOP;
4225
4226
4227 -- If there are no more distributions left and there are still some more units to be retired,
4228 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
4229 -- assigned to all the distributions.
4230
4231
4232 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
4233 -- x_return_status := OKL_API.G_RET_STS_ERROR;
4234 -- Sold quantity is more than the total quantity assigned to asset distributions.
4235 OKC_API.set_message( p_app_name => 'OKL',
4236 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
4237 --RAISE okc_api.G_EXCEPTION_ERROR;
4238 END IF;
4239
4240 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
4241 do_partial_units_retirement(
4242 p_api_version => p_api_version,
4243 p_init_msg_list => OKC_API.G_FALSE,
4244 p_tax_owner => l_tax_owner,
4245 p_delta_cost => l_delta_cost,
4246 p_asset_id => l_okxassetlines_rec.asset_id,
4247 p_asset_number => l_okxassetlines_rec.asset_number,
4248 p_proceeds_of_sale => l_sale_amount,
4249 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4250 p_book_type_code => l_okxassetlines_rec.book_type_code,
4251 p_total_quantity => l_units_to_be_retired, -- units to be retired
4252 p_dist_tbl => l_dist_tbl,
4253 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4254 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4255 x_msg_count => x_msg_count,
4256 x_msg_data => x_msg_data,
4257 x_return_status => x_return_status);
4258
4259
4260 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4261 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4262 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4263 RAISE OKC_API.G_EXCEPTION_ERROR;
4264 END IF;
4265
4266 l_units_retired := l_units_to_be_retired;
4267
4268 END IF; --l_non_retired_quantity = l_quantity
4269 ELSIF l_retired_quantity = l_quantity AND l_non_retired_quantity = 0 THEN -- retired qty = original asset return qty
4270 -- Asset is already fully retired.
4271 OKC_API.set_message( p_app_name => 'OKL',
4272 p_msg_name => 'OKL_AM_ALREADY_RETIRED',
4273 p_token1 => 'ASSET_NUMBER',
4274 p_token1_value => l_okxassetlines_rec.asset_number);
4275 l_already_retired := 'Y';
4276 ELSIF l_retired_quantity >= l_quantity AND l_non_retired_quantity > 0 THEN -- There are still some more units that can be retierd
4277 -- non-retired qty can be either less or more than l_quantity
4278
4279 IF l_non_retired_quantity >= l_quantity THEN
4280 l_units_to_be_retired := l_quantity;
4281 ELSE
4282 l_units_to_be_retired := l_non_retired_quantity;
4283 END IF;
4284
4285 -- l_dist_quantity := l_quantity;
4286 l_dist_quantity := l_units_to_be_retired;
4287 i := 0;
4288
4289 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
4290 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
4291 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4292 -- than requested units then retire that distribution fully and move to next distribution for remaining
4293 -- units, until all the requested units have been retired.
4294
4295
4296 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4297 IF l_disthist_rec.retirement_id IS NULL THEN
4298 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4299 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4300 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4301 l_dist_quantity := 0;
4302 EXIT;
4303 ELSE
4304 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4305 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4306 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4307 END IF;
4308 i := i + 1;
4309 END IF;
4310 END LOOP;
4311
4312 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4313 do_partial_units_retirement(
4314 p_api_version => p_api_version,
4315 p_init_msg_list => OKC_API.G_FALSE,
4316 p_tax_owner => l_tax_owner,
4317 p_delta_cost => l_delta_cost,
4318 p_asset_id => l_okxassetlines_rec.asset_id,
4319 p_asset_number => l_okxassetlines_rec.asset_number,
4320 p_proceeds_of_sale => l_sale_amount,
4321 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4322 p_book_type_code => l_okxassetlines_rec.book_type_code,
4323 p_total_quantity => l_units_to_be_retired, -- units to be retired
4324 p_dist_tbl => l_dist_tbl,
4325 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4326 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4327 x_msg_count => x_msg_count,
4328 x_msg_data => x_msg_data,
4329 x_return_status => x_return_status);
4330
4331
4332 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4333 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4334 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4335 RAISE OKC_API.G_EXCEPTION_ERROR;
4336 END IF;
4337
4338 l_units_retired := l_units_to_be_retired;
4339
4340 ELSIF l_retired_quantity > l_quantity AND l_non_retired_quantity = 0 THEN
4341 -- x_return_status := OKL_API.G_RET_STS_ERROR;
4342 -- Asset ASSET_NUMBER is already retired with invalid retired quantity which is more than the original quantity.
4343 OKC_API.set_message( p_app_name => 'OKL',
4344 p_msg_name => 'OKL_AM_INVALID_RETIRED_QTY',
4345 p_token1 => 'ASSET_NUMBER',
4346 p_token1_value => l_okxassetlines_rec.asset_number);
4347 -- RAISE okc_api.G_EXCEPTION_ERROR;
4348 ELSIF l_retired_quantity < l_quantity AND l_non_retired_quantity > 0 THEN
4349 -- user requested for full retirement, but the asset is already retired partially
4350
4351
4352
4353 IF l_non_retired_quantity >= l_quantity THEN
4354 l_units_to_be_retired := l_quantity;
4355 ELSE
4356 l_units_to_be_retired := l_non_retired_quantity;
4357 END IF;
4358
4359 -- l_dist_quantity := l_quantity;
4360 l_dist_quantity := l_units_to_be_retired;
4361 i := 0;
4362
4363 -- l_disthist_csr picks up all active distributions, which could possibly be retired.
4364
4365
4366 -- loop thru all the distributions of an asset, starting from the first non-retired distribution, compare the requested
4367 -- quantity with the distribution units . If distribution has more units than the requested quantity, then
4368 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4369 -- than requested units then retire that distribution fully and move to next distribution for remaining
4370 -- units, until all the requested units have been retired.
4371
4372
4373 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4374 IF l_disthist_rec.retirement_id IS NULL THEN
4375
4376 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4377 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4378 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4379 l_dist_quantity := 0;
4380 EXIT;
4381 ELSE
4382 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4383 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4384 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4385 END IF;
4386 i := i + 1;
4387 END IF;
4388
4389 END LOOP;
4390
4391 -- If there are no more non-retired distributions left and there are still some more units to be retired,
4392 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
4393 -- assigned to all the distributions.
4394
4395 IF l_dist_quantity > 0 THEN
4396 IF l_retired_quantity < l_dist_quantity THEN
4397 -- x_return_status := OKL_API.G_RET_STS_ERROR;
4398 -- Sold quantity is more than the total quantity assigned to asset distributions.
4399 OKC_API.set_message( p_app_name => 'OKL',
4400 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
4401 -- RAISE okc_api.G_EXCEPTION_ERROR;
4402 END IF;
4403 END IF;
4404
4405 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4406 do_partial_units_retirement(
4407 p_api_version => p_api_version,
4408 p_init_msg_list => OKC_API.G_FALSE,
4409 p_tax_owner => l_tax_owner,
4410 p_delta_cost => l_delta_cost,
4411 p_asset_id => l_okxassetlines_rec.asset_id,
4412 p_asset_number => l_okxassetlines_rec.asset_number,
4413 p_proceeds_of_sale => l_sale_amount,
4414 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4415 p_book_type_code => l_okxassetlines_rec.book_type_code,
4416 p_total_quantity => l_units_to_be_retired, -- units to be retierd
4417 p_dist_tbl => l_dist_tbl,
4418 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4419 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4420 x_msg_count => x_msg_count,
4421 x_msg_data => x_msg_data,
4422 x_return_status => x_return_status);
4423
4424
4425 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4426 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4427 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4428 RAISE OKC_API.G_EXCEPTION_ERROR;
4429 END IF;
4430
4431 l_units_retired := l_units_to_be_retired;
4432
4433 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
4434 ELSIF l_retired_quantity = 0 AND l_non_retired_quantity = 0 THEN -- This condition will be true only for TAX books
4435
4436 IF l_already_retired = 'N' THEN -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this condition to stop cost retirement of tax book if asset is already fully retierd in corp book
4437 -- do cost retirement for the tax book
4438
4439 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
4440 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
4441 -- Cost Adjustment will happen in tax book through do_cost_retirement
4442 -- Cost will become Residual Value
4443 l_cost := l_okxassetlines_rec.cost; -- for bug 5760603 -- Retire cost Not Rv
4444 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
4445 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
4446 l_cost := l_okxassetlines_rec.cost; -- Retire the current cost in FA
4447 END IF;
4448 -- SECHAWLA 21-NOV-2003 3262519 : end
4449
4450 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4451 do_cost_retirement(
4452 p_api_version => p_api_version,
4453 p_init_msg_list => OKC_API.G_FALSE,
4454 p_tax_owner => l_tax_owner,
4455 p_delta_cost => l_delta_cost,
4456 p_asset_id => l_okxassetlines_rec.asset_id,
4457 p_asset_number => l_okxassetlines_rec.asset_number,
4458 p_proceeds_of_sale => l_sale_amount,
4459 p_tax_book => l_okxassetlines_rec.book_type_code,
4460 --p_cost => l_cost_retired, -- SECHAWLA 13-JAN-03 Bug # 2701440
4461 -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the original request is for Full retirement, do a full cost retirement for the tax book
4462 --p_cost => l_okxassetlines_rec.cost, -- SECHAWLA 21-NOV-2003 3262519
4463 p_cost => l_cost, -- SECHAWLA 21-NOV-2003 3262519
4464 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4465 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4466 x_msg_count => x_msg_count,
4467 x_msg_data => x_msg_data,
4468 x_return_status => x_return_status);
4469
4470 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4471 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4472 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4473 RAISE OKC_API.G_EXCEPTION_ERROR;
4474 END IF;
4475 END IF;
4476 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
4477
4478 END IF;
4479
4480
4481 ELSE -- ordered quantity is either less or more than the original quantity
4482
4483 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN
4484
4485 l_dist_quantity := l_ordered_quantity; -- SECHAWLA 21-OCT-04 3924244
4486
4487 i := 0;
4488
4489
4490
4491 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
4492 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
4493 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4494 -- than requested units then retire that distribution fully and move to next distribution for remaining
4495 -- units, until all the requested units have been retired.
4496
4497 l_retired_dist_units := 0;
4498 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4499 -- First retire all non-retired distributions, maintain a unit count of already retired distributions.
4500 -- We will use this count at the end, when all non-retired distributions have been retired, to make
4501 -- sure that the units retired = ordered quantity
4502 IF l_disthist_rec.retirement_id IS NOT NULL THEN
4503 l_retired_dist_units := l_retired_dist_units + abs(l_disthist_rec.transaction_units);
4504
4505 ELSE
4506
4507 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4508 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4509 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4510 l_dist_quantity := 0;
4511 EXIT;
4512 ELSE
4513 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4514 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4515 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4516 END IF;
4517 i := i + 1;
4518 END IF;
4519 END LOOP;
4520
4521
4522 -- If there are no more distributions left and there are still some more units to be retired,
4523 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
4524 -- assigned to all the distributions.
4525
4526
4527 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
4528 IF l_retired_dist_units < l_dist_quantity THEN -- retired quantity isn't enough to match up with total qty
4529
4530 -- Sold quantity is more than the total quantity assigned to asset distributions.
4531 OKC_API.set_message( p_app_name => 'OKL',
4532 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
4533 END IF;
4534 -- SECHAWLA 21-OCT-04 3924244
4535 l_units_to_be_retired := l_ordered_quantity - l_dist_quantity; -- retire whatever is left
4536 ELSE
4537 l_units_to_be_retired := l_ordered_quantity; -- SECHAWLA 21-OCT-04 3924244
4538 END IF;
4539
4540
4541 IF l_dist_tbl.COUNT > 0 THEN
4542 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4543 do_partial_units_retirement(
4544 p_api_version => p_api_version,
4545 p_init_msg_list => OKC_API.G_FALSE,
4546 p_tax_owner => l_tax_owner,
4547 p_delta_cost => l_delta_cost,
4548 p_asset_id => l_okxassetlines_rec.asset_id,
4549 p_asset_number => l_okxassetlines_rec.asset_number,
4550 p_proceeds_of_sale => l_sale_amount,
4551 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4552 p_book_type_code => l_okxassetlines_rec.book_type_code,
4553 p_total_quantity => l_units_to_be_retired, -- units to be retired
4554 p_dist_tbl => l_dist_tbl,
4555 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4556 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4557 x_msg_count => x_msg_count,
4558 x_msg_data => x_msg_data,
4559 x_return_status => x_return_status);
4560
4561
4562 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4563 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4564 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4565 RAISE OKC_API.G_EXCEPTION_ERROR;
4566 END IF;
4567
4568
4569 /* SECHAWLA 21-NOV-03 3262519 : This fetch is not required. Cost to be retired from the tax book should be
4570 -- calculated using tax book cost and not the corporate book cost, as the 2 costs can be different
4571
4572
4573 -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this code to get the cost retired for the corporate book
4574 -- This cost is used later to perform a cost retirement for the TAX book
4575 OPEN l_faretirement_csr(l_okxassetlines_rec.asset_id,l_okxassetlines_rec.corporate_book);
4576 FETCH l_faretirement_csr INTO l_cost_retired;
4577 -- Since asset is first retired from corporate book, this fetch will definitely find a row
4578 CLOSE l_faretirement_csr;
4579 */
4580
4581 l_units_retired := l_units_to_be_retired;
4582 ELSE
4583 -- If it reaches here, it means it didn't find any new distributions to retire. Since we are not
4584 -- processing any records in FA in this case, we consider this asset as alredy retired.
4585 l_already_retired := 'Y';
4586 END IF;
4587
4588 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
4589 ELSIF l_okxassetlines_rec.book_class = 'TAX' THEN
4590
4591 IF l_already_retired = 'N' THEN -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this condition to stop cost retirement of tax book if asset is already fully retierd in corp book
4592 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
4593 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
4594 -- Cost Adjustment will happen in tax book through do_cost_retirement
4595 -- Cost will become Residual Value
4596 l_cost := l_okxassetlines_rec.cost; -- for bug 5760603 -- Retire cost Not Rv
4597 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
4598 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
4599 l_cost := l_okxassetlines_rec.cost; -- cost to be considered is the curent cost
4600 END IF;
4601 -- SECHAWLA 21-NOV-2003 3262519 : end
4602
4603 -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this condition for teh scenario where tax book's initial cost is less than the corp book cost
4604 --IF l_okxassetlines_rec.cost >= l_cost_retired THEN -- SECHAWLA 21-nov-03 3262519
4605
4606 --SECHAWLA 21-NOV-2003 3262519 : Cost to be retired from tax book should be calculated using
4607 -- tax book's cost and the quentity retired in the corporate book
4608 l_cost_retired := (l_cost / l_okxassetlines_rec.current_units ) * l_units_retired;
4609
4610 IF l_cost >= l_cost_retired THEN
4611 -- this condition should always be true
4612
4613 -- do cost retirement for the tax book
4614 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4615 do_cost_retirement(
4616 p_api_version => p_api_version,
4617 p_init_msg_list => OKC_API.G_FALSE,
4618 p_tax_owner => l_tax_owner,
4619 p_delta_cost => l_delta_cost,
4620 p_asset_id => l_okxassetlines_rec.asset_id,
4621 p_asset_number => l_okxassetlines_rec.asset_number,
4622 p_proceeds_of_sale => l_sale_amount,
4623 p_tax_book => l_okxassetlines_rec.book_type_code,
4624 p_cost => l_cost_retired,
4625 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4626 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4627 x_msg_count => x_msg_count,
4628 x_msg_data => x_msg_data,
4629 x_return_status => x_return_status);
4630
4631 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4632 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4633 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4634 RAISE OKC_API.G_EXCEPTION_ERROR;
4635 END IF;
4636
4637 --SECHAWLA 21-NOV-2003 3262519 : This condition will not occur now that we calculate cost to be
4638 -- retired from tax book using tax book cost itself
4639
4640
4641 /* -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the tax book's cost is less than the cost retierd from the corp book
4642 -- but has not been fully retired yet, then perform a full cost retirement for tax book
4643 --ELSIF l_okxassetlines_rec.cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
4644 ELSIF l_cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
4645 -- retire the whole remaining cost
4646 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4647 do_cost_retirement(
4648 p_api_version => p_api_version,
4649 p_init_msg_list => OKC_API.G_FALSE,
4650 p_tax_owner => l_tax_owner,
4651 p_delta_cost => l_delta_cost,
4652 p_asset_id => l_okxassetlines_rec.asset_id,
4653 p_asset_number => l_okxassetlines_rec.asset_number,
4654 p_proceeds_of_sale => l_sale_amount,
4655 p_tax_book => l_okxassetlines_rec.book_type_code,
4656 --p_cost => l_okxassetlines_rec.cost, -- SECHAWLA 21-nov-03 3262519
4657 p_cost => l_cost, -- SECHAWLA 21-nov-03 3262519
4658 p_prorate_convention => l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4659 x_msg_count => x_msg_count,
4660 x_msg_data => x_msg_data,
4661 x_return_status => x_return_status);
4662
4663 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4664 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4665 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4666 RAISE OKC_API.G_EXCEPTION_ERROR;
4667 END IF;
4668 */
4669
4670 END IF;
4671 END IF;
4672 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
4673
4674 END IF;
4675 END IF;
4676
4677 IF l_already_retired = 'N' THEN
4678 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN -- SECHAWLA Bug # 2701440 : Added this condition to
4679 -- store trx transaction and process a/c entries only
4680 -- for CORPORATE book
4681 -- create transaction header
4682 okl_am_util_pvt.get_transaction_id(
4683 p_try_name => l_trx_type,
4684 x_return_status => x_return_status,
4685 x_try_id => l_try_id);
4686
4687 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4688 -- Unable to find a transaction type for this transaction.
4689 OKL_API.set_message(p_app_name => 'OKL',
4690 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
4691 p_token1 => 'TRY_NAME',
4692 p_token1_value => 'Asset Disposition');
4693 RAISE OKC_API.G_EXCEPTION_ERROR;
4694 END IF;
4695
4696 lp_thpv_rec.tas_type := 'RFA';
4697 lp_thpv_rec.tsu_code := 'PROCESSED';
4698 lp_thpv_rec.try_id := l_try_id;
4699 lp_thpv_rec.date_trans_occurred := l_sys_date;
4700
4701 -- RRAVIKIR Legal Entity Changes
4702 lp_thpv_rec.legal_entity_id := l_legal_entity_id;
4703 -- Legal Entity Changes End
4704
4705 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def( p_api_version => p_api_version,
4706 p_init_msg_list => OKC_API.G_FALSE,
4707 x_return_status => x_return_status,
4708 x_msg_count => x_msg_count,
4709 x_msg_data => x_msg_data,
4710 p_thpv_rec => lp_thpv_rec,
4711 x_thpv_rec => lx_thpv_rec);
4712
4713
4714 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4715 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4716 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4717 RAISE OKC_API.G_EXCEPTION_ERROR;
4718 END IF;
4719
4720 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
4721 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
4722 lp_tlpv_rec.currency_code := l_func_curr_code;
4723
4724
4725 -- Create transaction Line
4726 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
4727 lp_tlpv_rec.iay_id := l_okxassetlines_rec.depreciation_category;
4728 lp_tlpv_rec.kle_id := l_kle_id;
4729 lp_tlpv_rec.line_number := 1;
4730 lp_tlpv_rec.tal_type := 'RFL';
4731 lp_tlpv_rec.asset_number := l_okxassetlines_rec.asset_number;
4732 lp_tlpv_rec.corporate_book := l_okxassetlines_rec.book_type_code;
4733 lp_tlpv_rec.original_cost := l_okxassetlines_rec.original_cost;
4734 lp_tlpv_rec.current_units := l_okxassetlines_rec.current_units;
4735 lp_tlpv_rec.units_retired := l_units_retired ;
4736 lp_tlpv_rec.dnz_asset_id := l_okxassetlines_rec.asset_id;
4737 lp_tlpv_rec.dnz_khr_id := l_okxassetlines_rec.dnz_chr_id;
4738
4739 -- SECHAWLA 15-DEC-04 4028371 : set FA date on trx line
4740 lp_tlpv_rec.FA_TRX_DATE := l_fa_trx_date;
4741
4742 OKL_TXL_ASSETS_PUB.create_txl_asset_def(p_api_version => p_api_version,
4743 p_init_msg_list => OKC_API.G_FALSE,
4744 x_return_status => x_return_status,
4745 x_msg_count => x_msg_count,
4746 x_msg_data => x_msg_data,
4747 p_tlpv_rec => lp_tlpv_rec,
4748 x_tlpv_rec => lx_tlpv_rec);
4749
4750 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4751 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4752 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4753 RAISE OKC_API.G_EXCEPTION_ERROR;
4754 END IF;
4755
4756
4757
4758 -- make call to accounting entries
4759 process_accounting_entries(
4760 p_api_version => p_api_version,
4761 p_init_msg_list => OKC_API.G_FALSE,
4762 x_return_status => x_return_status,
4763 x_msg_count => x_msg_count,
4764 x_msg_data => x_msg_data,
4765 p_kle_id => l_kle_id,
4766 p_try_id => l_try_id,
4767 p_sys_date => l_sys_date,
4768 p_source_id => lx_tlpv_rec.id,
4769 p_trx_type => l_trx_name,
4770 p_amount => l_sale_amount,
4771 p_func_curr_code => l_func_curr_code,
4772 x_total_amount => lx_total_amount,
4773 p_legal_entity_id => l_legal_entity_id);
4774
4775 -- rollback if error in accounting entries
4776 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4777 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4778 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4779 RAISE OKC_API.G_EXCEPTION_ERROR;
4780 END IF;
4781
4782 -- Store the amount at the header and line level in trx tables
4783
4784 -- Update amount in the header table
4785 lp_thpv_rec := lp_thpv_empty_rec;
4786 lp_thpv_rec.id := lx_thpv_rec.id;
4787 lp_thpv_rec.total_match_amount := lx_total_amount;
4788
4789 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
4790 p_api_version => p_api_version,
4791 p_init_msg_list => OKC_API.G_FALSE,
4792 x_return_status => x_return_status,
4793 x_msg_count => x_msg_count,
4794 x_msg_data => x_msg_data,
4795 p_thpv_rec => lp_thpv_rec,
4796 x_thpv_rec => lx_thpv_rec);
4797
4798 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4799 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4800 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4801 RAISE OKC_API.G_EXCEPTION_ERROR;
4802 END IF;
4803
4804 -- Update amount in the lines table.
4805 lp_tlpv_rec := lp_tlpv_empty_rec;
4806 lp_tlpv_rec.id := lx_tlpv_rec.id;
4807 lp_tlpv_rec.match_amount := lx_total_amount;
4808
4809 --SECHAWLA 03-JAN-03 Added the following statement as a temporary fix to LA's ROUNDING ERROR problem
4810 lp_tlpv_rec.kle_id := l_kle_id;
4811
4812 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
4813 lp_tlpv_rec.currency_code := l_func_curr_code;
4814
4815 OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
4816 p_api_version => p_api_version,
4817 p_init_msg_list => OKC_API.G_FALSE,
4818 x_return_status => x_return_status,
4819 x_msg_count => x_msg_count,
4820 x_msg_data => x_msg_data,
4821 p_tlpv_rec => lp_tlpv_rec,
4822 x_tlpv_rec => lx_tlpv_rec);
4823
4824 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4825 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4826 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4827 RAISE OKC_API.G_EXCEPTION_ERROR;
4828 END IF;
4829 --- End store amounts at the header and line level
4830
4831 END IF; -- if book_class = corporate
4832
4833 END IF; -- if l_already_retired = 'N'
4834
4835
4836 END LOOP;
4837
4838
4839 -- commented call to expire_item djanaswa bug 6736148 begin
4840
4841 --- Expire item in Installed Base
4842 /*
4843 IF l_ordered_quantity >= l_quantity THEN -- SECHAWLA 21-OCT-04 3924244
4844 -- Retire all existing instances
4845 FOR l_itemlocation_rec in l_itemlocation_csr(l_kle_id) LOOP
4846 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
4847 expire_item (
4848 p_api_version => p_api_version,
4849 p_init_msg_list => OKC_API.G_FALSE,
4850 x_msg_count => x_msg_count,
4851 x_msg_data => x_msg_data,
4852 x_return_status => x_return_status ,
4853 p_instance_id => l_itemlocation_rec.instance_id,
4854 p_end_date => l_sys_date);
4855
4856 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4857 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4858 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4859 RAISE OKC_API.G_EXCEPTION_ERROR;
4860 END IF;
4861
4862 END IF;
4863
4864 END LOOP;
4865
4866 ELSE -- quantity < original quantity
4867 instance_counter := 1;
4868 -- retire number of instances equal to the input quantity
4869 FOR l_itemlocation_rec in l_itemlocation_csr(l_kle_id) LOOP
4870 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
4871 expire_item (
4872 p_api_version => p_api_version,
4873 p_init_msg_list => OKC_API.G_FALSE,
4874 x_msg_count => x_msg_count,
4875 x_msg_data => x_msg_data,
4876 x_return_status => x_return_status ,
4877 p_instance_id => l_itemlocation_rec.instance_id,
4878 p_end_date => l_sys_date);
4879
4880 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4881 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4882 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4883 RAISE OKC_API.G_EXCEPTION_ERROR;
4884 END IF;
4885
4886 IF instance_counter = l_ordered_quantity THEN -- SECHAWLA 21-OCT-04 3924244
4887 EXIT;
4888 END IF;
4889 instance_counter := instance_counter + 1;
4890 END IF;
4891 END LOOP;
4892
4893 END IF;
4894 */
4895 -------------- end IB Retirement -----------------------
4896 -- commented call to expire_item djanaswa bug 6736148 end
4897
4898
4899
4900 -- Loop thru all the pending transactions in okl_trx_assets_v and okl_txl_assets_v
4901 -- and update the status to 'CANCELED'
4902
4903 FOR l_assettrx_rec IN l_assettrx_csr(l_kle_id) LOOP
4904 -- update the staus (tsu_code) in okl_trx_assets_v
4905 lp_thpv_rec := lp_thpv_empty_rec;
4906 lp_thpv_rec.id := l_assettrx_rec.id;
4907 lp_thpv_rec.tsu_code := 'CANCELED';
4908 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
4909 p_api_version => p_api_version,
4910 p_init_msg_list => OKC_API.G_FALSE,
4911 x_return_status => x_return_status,
4912 x_msg_count => x_msg_count,
4913 x_msg_data => x_msg_data,
4914 p_thpv_rec => lp_thpv_rec,
4915 x_thpv_rec => lx_thpv_rec);
4916
4917 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4918 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4919 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4920 RAISE OKC_API.G_EXCEPTION_ERROR;
4921 END IF;
4922 END LOOP;
4923
4924 -- ++++++++++++++++++++ service contract integration begin ++++++++++++++++++
4925
4926 -- RMUNJULU 3061751 11-SEP-2003
4927 -- Check if linked service contract exists for the asset which is disposed
4928 l_service_int_needed := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed(
4929 p_asset_id => l_kle_id,
4930 p_source => 'DISPOSE');
4931
4932 -- Do the Service Contract Integration Notification for DISPOSE
4933 OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration(
4934 p_transaction_id => l_kle_id,
4935 p_transaction_date => SYSDATE,
4936 p_source => 'DISPOSE_2',
4937 p_service_integration_needed => l_service_int_needed);
4938
4939 -- ++++++++++++++++++++ service contract integration end ++++++++++++++++++
4940
4941 --END LOOP; -- SECHAWLA 21-OCT-04 3924244
4942
4943 -- MDOKAL: 18-SEP-03 - Bug 3082639
4944 -------------- Securitization Processing -----------------------
4945
4946 OKL_AM_SECURITIZATION_PVT.process_securitized_streams(
4947 p_api_version => p_api_version,
4948 p_init_msg_list => OKC_API.G_FALSE,
4949 x_return_status => x_return_status,
4950 x_msg_count => x_msg_count,
4951 x_msg_data => x_msg_data,
4952 p_kle_id => l_kle_id,
4953 p_sale_price => l_sale_amount,
4954 p_call_origin => OKL_SECURITIZATION_PVT.G_TRX_REASON_ASSET_DISPOSAL);
4955
4956 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4957 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4958 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4959 RAISE OKC_API.G_EXCEPTION_ERROR;
4960 END IF;
4961 -------------- end Securitization Processing -----------------------
4962 --akrangan added for sla populate sources cr start
4963 IF g_trans_id_tbl.COUNT > 0
4964 THEN
4965 FOR i IN g_trans_id_tbl.FIRST .. g_trans_id_tbl.LAST
4966 LOOP
4967 -- header record
4968 l_fxhv_rec.source_id := lx_thpv_rec.id;
4969 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
4970 l_fxhv_rec.khr_id := lx_tlpv_rec.dnz_khr_id;
4971 l_fxhv_rec.try_id := lx_thpv_rec.try_id;
4972 -- line record
4973 l_fxlv_rec.source_id := lx_tlpv_rec.id;
4974 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
4975 l_fxlv_rec.kle_id := lx_tlpv_rec.kle_id;
4976 l_fxlv_rec.asset_id := lx_tlpv_rec.dnz_asset_id;
4977 l_fxlv_rec.fa_transaction_id := g_trans_id_tbl(i);
4978 l_fxlv_rec.asset_book_type_name := lx_tlpv_rec.corporate_book;
4979
4980 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
4981 p_init_msg_list => okc_api.g_false,
4982 p_fxhv_rec => l_fxhv_rec,
4983 p_fxlv_rec => l_fxlv_rec,
4984 x_return_status => x_return_status,
4985 x_msg_count => x_msg_count,
4986 x_msg_data => x_msg_data);
4987
4988 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
4989 THEN
4990 RAISE okl_api.g_exception_unexpected_error;
4991 ELSIF (x_return_status = okc_api.g_ret_sts_error)
4992 THEN
4993 RAISE okl_api.g_exception_error;
4994 END IF;
4995 END LOOP;
4996 END IF;
4997 --akrangan added for sla populate sources cr end
4998
4999 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5000 EXCEPTION
5001 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5002
5003 IF l_orderheaders_csr%ISOPEN THEN
5004 CLOSE l_orderheaders_csr;
5005 END IF;
5006
5007 IF l_assetreturn_csr%ISOPEN THEN
5008 CLOSE l_assetreturn_csr;
5009 END IF;
5010
5011 IF l_okxassetlines_csr%ISOPEN THEN
5012 CLOSE l_okxassetlines_csr;
5013 END IF;
5014
5015 IF l_orderlines_csr%ISOPEN THEN
5016 CLOSE l_orderlines_csr;
5017 END IF;
5018
5019 IF l_disthist_csr%ISOPEN THEN
5020 CLOSE l_disthist_csr;
5021 END IF;
5022
5023 IF l_assettrx_csr%ISOPEN THEN
5024 CLOSE l_assettrx_csr;
5025 END IF;
5026
5027 IF l_itemlocation_csr%ISOPEN THEN
5028 CLOSE l_itemlocation_csr;
5029 END IF;
5030
5031 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
5032 IF l_periodofaddition_csr%ISOPEN THEN
5033 CLOSE l_periodofaddition_csr;
5034 END IF;
5035
5036 IF l_bookcontrols_csr%ISOPEN THEN
5037 CLOSE l_bookcontrols_csr;
5038 END IF;
5039
5040 -- SECHAWLA 21-nov-03 3262519 : close new cursors
5041 IF l_dealtype_csr%ISOPEN THEN
5042 CLOSE l_dealtype_csr;
5043 END IF;
5044
5045 IF l_linesfullv_csr%ISOPEN THEN
5046 CLOSE l_linesfullv_csr;
5047 END IF;
5048
5049 --SECHAWLA 10-FEB-06 5016156
5050 IF l_offlseassettrx_csr%ISOPEN THEN
5051 CLOSE l_offlseassettrx_csr;
5052 END IF;
5053
5054 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5055 (
5056 l_api_name,
5057 G_PKG_NAME,
5058 'OKC_API.G_RET_STS_ERROR',
5059 x_msg_count,
5060 x_msg_data,
5061 '_PVT'
5062 );
5063 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5064
5065 IF l_orderheaders_csr%ISOPEN THEN
5066 CLOSE l_orderheaders_csr;
5067 END IF;
5068
5069 IF l_assetreturn_csr%ISOPEN THEN
5070 CLOSE l_assetreturn_csr;
5071 END IF;
5072
5073 IF l_okxassetlines_csr%ISOPEN THEN
5074 CLOSE l_okxassetlines_csr;
5075 END IF;
5076
5077 IF l_orderlines_csr%ISOPEN THEN
5078 CLOSE l_orderlines_csr;
5079 END IF;
5080
5081 IF l_disthist_csr%ISOPEN THEN
5082 CLOSE l_disthist_csr;
5083 END IF;
5084
5085 IF l_assettrx_csr%ISOPEN THEN
5086 CLOSE l_assettrx_csr;
5087 END IF;
5088
5089 IF l_itemlocation_csr%ISOPEN THEN
5090 CLOSE l_itemlocation_csr;
5091 END IF;
5092
5093 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
5094 IF l_periodofaddition_csr%ISOPEN THEN
5095 CLOSE l_periodofaddition_csr;
5096 END IF;
5097
5098 IF l_bookcontrols_csr%ISOPEN THEN
5099 CLOSE l_bookcontrols_csr;
5100 END IF;
5101
5102 -- SECHAWLA 21-nov-03 3262519 : close new cursors
5103 IF l_dealtype_csr%ISOPEN THEN
5104 CLOSE l_dealtype_csr;
5105 END IF;
5106
5107 IF l_linesfullv_csr%ISOPEN THEN
5108 CLOSE l_linesfullv_csr;
5109 END IF;
5110
5111 --SECHAWLA 10-FEB-06 5016156
5112 IF l_offlseassettrx_csr%ISOPEN THEN
5113 CLOSE l_offlseassettrx_csr;
5114 END IF;
5115
5116 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5117 (
5118 l_api_name,
5119 G_PKG_NAME,
5120 'OKC_API.G_RET_STS_UNEXP_ERROR',
5121 x_msg_count,
5122 x_msg_data,
5123 '_PVT'
5124 );
5125 WHEN OTHERS THEN
5126
5127 IF l_orderheaders_csr%ISOPEN THEN
5128 CLOSE l_orderheaders_csr;
5129 END IF;
5130
5131 IF l_assetreturn_csr%ISOPEN THEN
5132 CLOSE l_assetreturn_csr;
5133 END IF;
5134
5135 IF l_okxassetlines_csr%ISOPEN THEN
5136 CLOSE l_okxassetlines_csr;
5137 END IF;
5138
5139 IF l_orderlines_csr%ISOPEN THEN
5140 CLOSE l_orderlines_csr;
5141 END IF;
5142
5143 IF l_disthist_csr%ISOPEN THEN
5144 CLOSE l_disthist_csr;
5145 END IF;
5146
5147 IF l_assettrx_csr%ISOPEN THEN
5148 CLOSE l_assettrx_csr;
5149 END IF;
5150
5151 IF l_itemlocation_csr%ISOPEN THEN
5152 CLOSE l_itemlocation_csr;
5153 END IF;
5154
5155 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
5156 IF l_periodofaddition_csr%ISOPEN THEN
5157 CLOSE l_periodofaddition_csr;
5158 END IF;
5159
5160 IF l_bookcontrols_csr%ISOPEN THEN
5161 CLOSE l_bookcontrols_csr;
5162 END IF;
5163
5164 -- SECHAWLA 21-nov-03 3262519 : close new cursors
5165 IF l_dealtype_csr%ISOPEN THEN
5166 CLOSE l_dealtype_csr;
5167 END IF;
5168
5169 IF l_linesfullv_csr%ISOPEN THEN
5170 CLOSE l_linesfullv_csr;
5171 END IF;
5172
5173 --SECHAWLA 10-FEB-06 5016156
5174 IF l_offlseassettrx_csr%ISOPEN THEN
5175 CLOSE l_offlseassettrx_csr;
5176 END IF;
5177
5178 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5179 (
5180 l_api_name,
5181 G_PKG_NAME,
5182 'OTHERS',
5183 x_msg_count,
5184 x_msg_data,
5185 '_PVT'
5186 );
5187 END dispose_asset;
5188
5189
5190
5191
5192 -- Start of comments
5193 --
5194 -- Procedure Name : undo_retirement
5195 -- Description : This procedure is used to undo the asset retirement
5196 -- Business Rules :
5197 -- Parameters : p_retirement_id
5198
5199 -- Version : 1.0
5200 -- End of comments
5201
5202 PROCEDURE undo_retirement( p_api_version IN NUMBER,
5203 p_init_msg_list IN VARCHAR2,
5204 x_return_status OUT NOCOPY VARCHAR2,
5205 x_msg_count OUT NOCOPY NUMBER,
5206 x_msg_data OUT NOCOPY VARCHAR2,
5207 p_retirement_id IN NUMBER) IS
5208
5209 l_trans_rec FA_API_TYPES.trans_rec_type;
5210 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
5211 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
5212 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
5213 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
5214 l_inv_tbl FA_API_TYPES.inv_tbl_type;
5215 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5216 l_api_name CONSTANT VARCHAR2(30) := 'undo_retirement';
5217 l_api_version CONSTANT NUMBER := 1;
5218 l_dummy VARCHAR2(1);
5219
5220 -- This cursor is used to validate the retirement ID
5221 CURSOR l_faretirement_csr(p_retirement_id NUMBER) IS
5222 SELECT 'x'
5223 FROM fa_retirements
5224 WHERE retirement_id = p_retirement_id;
5225
5226 BEGIN
5227 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5228 G_PKG_NAME,
5229 p_init_msg_list,
5230 l_api_version,
5231 p_api_version,
5232 '_PVT',
5233 x_return_status);
5234
5235 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5236 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5237 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5238 RAISE OKC_API.G_EXCEPTION_ERROR;
5239 END IF;
5240
5241 IF p_retirement_id IS NULL OR p_retirement_id = OKL_API.G_MISS_NUM THEN
5242 x_return_status := OKL_API.G_RET_STS_ERROR;
5243 -- retirement id is required
5244 OKC_API.set_message( p_app_name => 'OKC',
5245 p_msg_name => G_REQUIRED_VALUE,
5246 p_token1 => G_COL_NAME_TOKEN,
5247 p_token1_value => 'RETIREMENT_ID');
5248
5249
5250 RAISE OKC_API.G_EXCEPTION_ERROR;
5251 END IF;
5252
5253 OPEN l_faretirement_csr(p_retirement_id);
5254 FETCH l_faretirement_csr INTO l_dummy;
5255 IF l_faretirement_csr%NOTFOUND THEN
5256 x_return_status := OKL_API.G_RET_STS_ERROR;
5257 -- retirement id is invalid
5258 OKC_API.set_message( p_app_name => 'OKC',
5259 p_msg_name => G_INVALID_VALUE,
5260 p_token1 => G_COL_NAME_TOKEN,
5261 p_token1_value => 'RETIREMENT_ID');
5262
5263
5264 RAISE OKC_API.G_EXCEPTION_ERROR;
5265 END IF;
5266 CLOSE l_faretirement_csr;
5267
5268 -- transaction information
5269 l_trans_rec.transaction_type_code := NULL;
5270 l_trans_rec.transaction_date_entered := NULL;
5271
5272
5273 --SECHAWLA 29-DEC-05 3827148 : added
5274 l_trans_rec.calling_interface := 'OKL:'||'Asset Disposition:'||'RFA';
5275
5276 -- retirement information
5277 l_asset_retire_rec.retirement_id := p_retirement_id;
5278
5279
5280 FA_RETIREMENT_PUB.undo_retirement( p_api_version => p_api_version,
5281 p_init_msg_list => OKC_API.G_FALSE,
5282 p_commit => FND_API.G_FALSE,
5283 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5284 p_calling_fn => NULL,
5285 x_return_status => x_return_status,
5286 x_msg_count => x_msg_count,
5287 x_msg_data => x_msg_data,
5288 px_trans_rec => l_trans_rec,
5289 px_asset_hdr_rec => l_asset_hdr_rec,
5290 px_asset_retire_rec => l_asset_retire_rec );
5291
5292 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5293 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5294 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5295 RAISE OKC_API.G_EXCEPTION_ERROR;
5296 END IF;
5297
5298
5299 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5300 EXCEPTION
5301 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5302 IF l_faretirement_csr%ISOPEN THEN
5303 CLOSE l_faretirement_csr;
5304 END IF;
5305 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5306 (
5307 l_api_name,
5308 G_PKG_NAME,
5309 'OKC_API.G_RET_STS_ERROR',
5310 x_msg_count,
5311 x_msg_data,
5312 '_PVT'
5313 );
5314 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5315 IF l_faretirement_csr%ISOPEN THEN
5316 CLOSE l_faretirement_csr;
5317 END IF;
5318 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5319 (
5320 l_api_name,
5321 G_PKG_NAME,
5322 'OKC_API.G_RET_STS_UNEXP_ERROR',
5323 x_msg_count,
5324 x_msg_data,
5325 '_PVT'
5326 );
5327 WHEN OTHERS THEN
5328 IF l_faretirement_csr%ISOPEN THEN
5329 CLOSE l_faretirement_csr;
5330 END IF;
5331 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5332 (
5333 l_api_name,
5334 G_PKG_NAME,
5335 'OTHERS',
5336 x_msg_count,
5337 x_msg_data,
5338 '_PVT'
5339 );
5340 END undo_retirement;
5341
5342
5343 -- Start of comments
5344 --
5345 -- Procedure Name : expire_item
5346 -- Description : This procedure is expire an item in installed base
5347 -- Business Rules :
5348 -- Parameters : p_instance_id
5349 -- p_end_date
5350
5351 -- Version : 1.0
5352 -- End of comments
5353
5354
5355 PROCEDURE expire_item (
5356 p_api_version IN NUMBER,
5357 p_init_msg_list IN VARCHAR2 ,
5358 x_msg_count OUT NOCOPY NUMBER,
5359 x_msg_data OUT NOCOPY VARCHAR2,
5360 x_return_status OUT NOCOPY VARCHAR2,
5361 p_instance_id IN NUMBER,
5362 p_end_date IN DATE ) IS
5363
5364 -- subtypes moved from okl_am_item_location_pvt
5365
5366 SUBTYPE instance_rec IS
5367 csi_datastructures_pub.instance_rec;
5368 SUBTYPE transaction_rec IS
5369 csi_datastructures_pub.transaction_rec;
5370 SUBTYPE id_tbl IS
5371 csi_datastructures_pub.id_tbl;
5372 SUBTYPE instance_query_rec IS
5373 csi_datastructures_pub.instance_query_rec;
5374 SUBTYPE party_query_rec IS
5375 csi_datastructures_pub.party_query_rec;
5376 SUBTYPE party_account_query_rec IS
5377 csi_datastructures_pub.party_account_query_rec;
5378 SUBTYPE instance_header_tbl IS
5379 csi_datastructures_pub.instance_header_tbl;
5380 SUBTYPE extend_attrib_values_tbl IS
5381 csi_datastructures_pub.extend_attrib_values_tbl;
5382 SUBTYPE party_tbl IS
5383 csi_datastructures_pub.party_tbl;
5384 SUBTYPE party_account_tbl IS
5385 csi_datastructures_pub.party_account_tbl;
5386 SUBTYPE pricing_attribs_tbl IS
5387 csi_datastructures_pub.pricing_attribs_tbl;
5388 SUBTYPE organization_units_tbl IS
5389 csi_datastructures_pub.organization_units_tbl;
5390 SUBTYPE instance_asset_tbl IS
5391 csi_datastructures_pub.instance_asset_tbl;
5392
5393
5394 -- Get Item Instance parameters
5395 l_instance_query_rec instance_query_rec;
5396 l_party_query_rec party_query_rec;
5397 l_account_query_rec party_account_query_rec;
5398 l_instance_header_tbl instance_header_tbl;
5399
5400 -- Expire Item Instance parameters
5401 l_instance_rec instance_rec;
5402 l_txn_rec transaction_rec;
5403 l_instance_id_lst id_tbl;
5404
5405 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5406 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5407
5408 l_api_name CONSTANT VARCHAR2(30) := 'expire_item';
5409 l_api_version CONSTANT NUMBER := 1;
5410 l_msg_count NUMBER := FND_API.G_MISS_NUM;
5411 l_msg_data VARCHAR2(2000);
5412
5413 BEGIN
5414
5415 -- ***************************************************************
5416 -- Check API version, initialize message list and create savepoint
5417 -- ***************************************************************
5418
5419 l_return_status := OKL_API.START_ACTIVITY (
5420 l_api_name,
5421 G_PKG_NAME,
5422 p_init_msg_list,
5423 l_api_version,
5424 p_api_version,
5425 '_PVT',
5426 x_return_status);
5427
5428 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5429 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5430 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5431 RAISE OKL_API.G_EXCEPTION_ERROR;
5432 END IF;
5433
5434 -- ************************
5435 -- Get Item Instance record
5436 -- ************************
5437
5438 l_instance_query_rec.instance_id := p_instance_id;
5439
5440 csi_item_instance_pub.get_item_instances (
5441 p_api_version => l_api_version,
5442 p_commit => FND_API.G_FALSE,
5443 p_init_msg_list => FND_API.G_FALSE,
5444 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5445 p_instance_query_rec => l_instance_query_rec,
5446 p_party_query_rec => l_party_query_rec,
5447 p_account_query_rec => l_account_query_rec,
5448 p_transaction_id => NULL,
5449 p_resolve_id_columns => FND_API.G_FALSE,
5450 p_active_instance_only => FND_API.G_TRUE,
5451 x_instance_header_tbl => l_instance_header_tbl,
5452 x_return_status => l_return_status,
5453 x_msg_count => l_msg_count,
5454 x_msg_data => l_msg_data);
5455
5456 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5457 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5458 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5459 RAISE OKL_API.G_EXCEPTION_ERROR;
5460 ELSIF (NVL (l_instance_header_tbl.COUNT, 0) <> 1) THEN
5461 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5462 END IF;
5463
5464 -- *************************************
5465 -- Initialize parameters to be passed in
5466 -- *************************************
5467
5468 l_instance_rec.instance_id := l_instance_header_tbl(1).instance_id;
5469 l_instance_rec.object_version_number := l_instance_header_tbl(1).object_version_number;
5470
5471 l_instance_rec.active_end_date := p_end_date;
5472
5473
5474 okl_am_util_pvt.initialize_txn_rec (l_txn_rec);
5475
5476 -- **************************************
5477 -- Call Installed Base API to expire item
5478 -- **************************************
5479
5480 csi_item_instance_pub.expire_item_instance (
5481 p_api_version => l_api_version,
5482 p_commit => FND_API.G_FALSE,
5483 p_init_msg_list => FND_API.G_FALSE,
5484 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5485 p_instance_rec => l_instance_rec,
5486 --p_expire_children => FND_API.G_FALSE, -- 10-AUG-04 SECHAWLA 3819339
5487 p_expire_children => FND_API.G_TRUE, -- 10-AUG-04 SECHAWLA 3819339 Expire all child instances before expiring parent
5488 p_txn_rec => l_txn_rec,
5489 x_instance_id_lst => l_instance_id_lst,
5490 x_return_status => l_return_status,
5491 x_msg_count => l_msg_count,
5492 x_msg_data => l_msg_data);
5493
5494 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5495 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5496 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5497 RAISE OKL_API.G_EXCEPTION_ERROR;
5498 END IF;
5499
5500 -- **************
5501 -- Return results
5502 -- **************
5503
5504 x_return_status := l_overall_status;
5505
5506 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
5507
5508 EXCEPTION
5509
5510 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5511 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5512 (
5513 l_api_name,
5514 G_PKG_NAME,
5515 'OKL_API.G_RET_STS_ERROR',
5516 x_msg_count,
5517 x_msg_data,
5518 '_PVT'
5519 );
5520
5521 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5522 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5523 (
5524 l_api_name,
5525 G_PKG_NAME,
5526 'OKL_API.G_RET_STS_UNEXP_ERROR',
5527 x_msg_count,
5528 x_msg_data,
5529 '_PVT'
5530 );
5531
5532 WHEN OTHERS THEN
5533
5534 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5535 (
5536 l_api_name,
5537 G_PKG_NAME,
5538 'OTHERS',
5539 x_msg_count,
5540 x_msg_data,
5541 '_PVT'
5542 );
5543
5544 END expire_item;
5545
5546
5547 END OKL_AM_ASSET_DISPOSE_PVT;
[Home] [Help]