1 PACKAGE BODY OKL_AM_ASSET_DISPOSE_PVT AS
2 /* $Header: OKLRADPB.pls 120.37.12020000.2 2012/08/28 14:03:41 racheruv 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 --Bug# 9838432: Added parameter cp_kle_id
1863 CURSOR l_offlseassettrx_csr(cp_trx_date IN DATE, cp_asset_number IN VARCHAR2, cp_kle_id IN NUMBER) IS
1864 SELECT h.tsu_code, h.tas_type, h.date_trans_occurred, l.dnz_asset_id,
1865 l.asset_number, l.kle_id ,l.DNZ_KHR_ID
1866 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l
1867 WHERE h.id = l.tas_id
1868 AND h.date_trans_occurred <= cp_trx_date
1869 AND h.tas_type in ('AMT','AUD','AUS')
1870 AND l.asset_number = cp_asset_number
1871 AND l.kle_id = cp_kle_id;
1872
1873 l_trx_status VARCHAR2(30);
1874 --SECHAWLA 10-FEB-06 5016156 : end
1875
1876 l_deal_type VARCHAR2(30);
1877 l_chr_id NUMBER;
1878 l_dummy VARCHAR2(1);
1879 l_contract_number VARCHAR2(120);
1880 l_rulv_rec okl_rule_pub.rulv_rec_type;
1881 l_tax_owner VARCHAR2(10);
1882 l_delta_cost NUMBER;
1883 l_residual_value NUMBER;
1884 l_name VARCHAR2(150);
1885 l_cost NUMBER;
1886 --SECHAWLA 21-NOV-2003 3262519 : end new declarations
1887
1888
1889 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1890 l_tsu_code VARCHAR2(30);
1891 l_try_id OKL_TRX_TYPES_V.id%TYPE;
1892 lp_thpv_rec thpv_rec_type;
1893 lp_thpv_empty_rec thpv_rec_type;
1894 lp_tlpv_empty_rec tlpv_rec_type;
1895 lx_thpv_rec thpv_rec_type;
1896 lp_tlpv_rec tlpv_rec_type;
1897 lx_tlpv_rec tlpv_rec_type;
1898 l_sys_date DATE;
1899 l_api_version CONSTANT NUMBER := 1;
1900 l_trx_type VARCHAR2(30) := 'Asset Disposition';
1901 l_trx_name VARCHAR2(30) := 'ASSET_DISPOSITION';
1902 l_api_name CONSTANT VARCHAR2(30) := 'dispose_asset';
1903
1904
1905 l_dist_quantity NUMBER;
1906 l_dist_tbl asset_dist_tbl ;
1907 i NUMBER;
1908 l_retired_quantity NUMBER;
1909 instance_counter NUMBER;
1910 l_quantity NUMBER;
1911 l_already_retired VARCHAR2(1):= 'N';
1912 l_remaining_units NUMBER;
1913 l_active_end_date DATE;
1914 l_retired_dist_units NUMBER;
1915 l_units_to_be_retired NUMBER;
1916 l_non_retired_quantity NUMBER;
1917 lx_total_amount NUMBER;
1918 l_units_retired NUMBER;
1919
1920 --SECHAWLA 23-DEC-02 Bug # 2701440: new declarations
1921 l_cost_retired NUMBER;
1922
1923 --SECHAWLA 03-JAN-03 Bug # 2683876 : new declaration
1924 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
1925
1926 --SECHAWLA 05-FEB-03 Bug # 2781557 : new declarations
1927 l_fiscal_year_name VARCHAR2(30);
1928 l_period_rec FA_API_TYPES.period_rec_type;
1929 l_count NUMBER;
1930 l_period_of_addition VARCHAR2(1);
1931
1932 --SECHAWLA 11-MAR-03 New Declarations
1933 l_proceeds_of_sale NUMBER ;
1934
1935 -- RMUNJULU 3061751
1936 l_service_int_needed VARCHAR2(1) := 'N';
1937
1938 -- rmunjulu EDAT
1939 l_quote_eff_date DATE;
1940 l_quote_accpt_date DATE;
1941
1942 --SECHAWLA 15-DEC-04 4028371 New Declarations
1943 l_fa_trx_date DATE;
1944
1945 -- rbruno bug 5436987 start
1946 l_functional_currency_code VARCHAR2(15);
1947 l_contract_currency_code VARCHAR2(15);
1948 l_currency_conversion_type VARCHAR2(30);
1949 l_currency_conversion_rate NUMBER;
1950 l_currency_conversion_date DATE;
1951 l_converted_amount NUMBER;
1952
1953 -- rbruno bug 5436987
1954 -- Since we do not use the amount or converted amount
1955 -- set a hardcoded value for the amount (and pass to to
1956 -- OKL_ACCOUNTING_UTIL.convert_to_functional_currency and get back
1957 -- conversion values )
1958 l_hard_coded_amount NUMBER := 100;
1959 --rbruno bug 5436987 end
1960
1961
1962
1963 --akrangan sla populate sources cr start
1964 l_fxhv_rec okl_fxh_pvt.fxhv_rec_type;
1965 l_fxlv_rec okl_fxl_pvt.fxlv_rec_type;
1966 --akrangan sla populate sources cr end
1967
1968
1969 BEGIN
1970
1971 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1972 G_PKG_NAME,
1973 p_init_msg_list,
1974 l_api_version,
1975 p_api_version,
1976 '_PVT',
1977 x_return_status);
1978
1979 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1980 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1981 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1982 RAISE OKC_API.G_EXCEPTION_ERROR;
1983 END IF;
1984
1985 -- Get the sysdate
1986 SELECT SYSDATE INTO l_sys_date FROM DUAL;
1987
1988 -- rmunjulu EDAT Added condition to default
1989 IF p_quote_eff_date IS NOT NULL
1990 AND p_quote_eff_date <> OKL_API.G_MISS_DATE THEN
1991
1992 l_quote_eff_date := p_quote_eff_date;
1993
1994 ELSE
1995
1996 l_quote_eff_date := l_sys_date;
1997
1998 END IF;
1999
2000 -- rmunjulu EDAT Added condition to default
2001 IF p_quote_accpt_date IS NOT NULL
2002 AND p_quote_accpt_date <> OKL_API.G_MISS_DATE THEN
2003
2004 l_quote_accpt_date := p_quote_accpt_date;
2005
2006 ELSE
2007
2008 l_quote_accpt_date := l_sys_date;
2009
2010 END IF;
2011
2012 IF p_financial_asset_id IS NULL OR p_financial_asset_id = OKL_API.G_MISS_NUM THEN
2013 x_return_status := OKL_API.G_RET_STS_ERROR;
2014 -- Finacial Asset id is a required parameter
2015 OKC_API.set_message( p_app_name => 'OKC',
2016 p_msg_name => G_REQUIRED_VALUE,
2017 p_token1 => G_COL_NAME_TOKEN,
2018 p_token1_value => 'FINANCIAL_ASSET_ID');
2019
2020
2021 RAISE OKC_API.G_EXCEPTION_ERROR;
2022 END IF;
2023
2024 -- RRAVIKIR Legal Entity Changes
2025 IF (p_legal_entity_id is null or p_legal_entity_id = OKC_API.G_MISS_NUM) THEN
2026 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2027 p_msg_name => g_required_value,
2028 p_token1 => g_col_name_token,
2029 p_token1_value => 'legal_entity_id');
2030 RAISE OKC_API.G_EXCEPTION_ERROR;
2031 END IF;
2032 -- Legal Entity Changes End
2033
2034 --SECHAWLA 21-NOV-2003 3262519 : Added the following validation
2035 OPEN l_okclines_csr(p_financial_asset_id );
2036 FETCH l_okclines_csr INTO l_dummy;
2037 IF l_okclines_csr%NOTFOUND THEN
2038 x_return_status := OKL_API.G_RET_STS_ERROR;
2039 -- Finacial Asset id is invalid
2040 OKC_API.set_message( p_app_name => 'OKC',
2041 p_msg_name => G_INVALID_VALUE,
2042 p_token1 => G_COL_NAME_TOKEN,
2043 p_token1_value => 'FINANCIAL_ASSET_ID');
2044
2045 RAISE OKC_API.G_EXCEPTION_ERROR;
2046 END IF;
2047 CLOSE l_okclines_csr;
2048 --SECHAWLA 21-NOV-2003 3262519 : end
2049
2050
2051 -- SECHAWLA 11-MAR-03 : assign 0 to l_proceeds_of_sale if NULL
2052 l_proceeds_of_sale := p_proceeds_of_sale ;
2053
2054 IF l_proceeds_of_sale IS NULL OR l_proceeds_of_sale = OKL_API.G_MISS_NUM THEN
2055 l_proceeds_of_sale := 0;
2056 /*
2057 x_return_status := OKL_API.G_RET_STS_ERROR;
2058 -- proceeds_of_sale is required
2059 OKC_API.set_message( p_app_name => 'OKC',
2060 p_msg_name => G_REQUIRED_VALUE,
2061 p_token1 => G_COL_NAME_TOKEN,
2062 p_token1_value => 'PROCEEDS_OF_SALE');
2063
2064
2065 RAISE OKC_API.G_EXCEPTION_ERROR;
2066 */
2067 END IF;
2068
2069 -- SECHAWLA 11-MAR-03 : Allow negative amount
2070 /*
2071 IF p_proceeds_of_sale < 0 THEN
2072 x_return_status := OKL_API.G_RET_STS_ERROR;
2073 -- proceeds_of_sale is invalid
2074 OKC_API.set_message( p_app_name => 'OKC',
2075 p_msg_name => G_INVALID_VALUE,
2076 p_token1 => G_COL_NAME_TOKEN,
2077 p_token1_value => 'PROCEEDS_OF_SALE');
2078
2079
2080 RAISE OKC_API.G_EXCEPTION_ERROR;
2081 END IF;
2082 */
2083
2084 IF p_quantity IS NOT NULL THEN
2085 IF p_quantity < 0 THEN
2086 x_return_status := OKL_API.G_RET_STS_ERROR;
2087 -- Quantity is invalid
2088 OKC_API.set_message( p_app_name => 'OKC',
2089 p_msg_name => G_INVALID_VALUE,
2090 p_token1 => G_COL_NAME_TOKEN,
2091 p_token1_value => 'QUANTITY');
2092
2093
2094 RAISE OKC_API.G_EXCEPTION_ERROR;
2095 END IF;
2096 IF trunc(p_quantity) <> p_quantity THEN
2097 x_return_status := OKL_API.G_RET_STS_ERROR;
2098 -- Units retired should be a whole number.
2099 OKC_API.set_message( p_app_name => 'OKL',
2100 p_msg_name => 'OKL_AM_WHOLE_UNITS_ERR');
2101 RAISE OKC_API.G_EXCEPTION_ERROR;
2102 END IF;
2103 END IF;
2104
2105 --SECHAWLA 21-NOV-2003 3262519 : Added the following code to get the deal type and tax owner
2106
2107 -- get the deal type from the contract
2108 OPEN l_dealtype_csr(p_financial_asset_id);
2109 FETCH l_dealtype_csr INTO l_chr_id, l_deal_type, l_contract_number;
2110 IF l_dealtype_csr%NOTFOUND THEN
2111 x_return_status := OKL_API.G_RET_STS_ERROR;
2112 -- chr id is invalid
2113 OKC_API.set_message( p_app_name => 'OKC',
2114 p_msg_name => G_INVALID_VALUE,
2115 p_token1 => G_COL_NAME_TOKEN,
2116 p_token1_value => 'CHR_ID');
2117
2118 RAISE OKC_API.G_EXCEPTION_ERROR;
2119 END IF;
2120 CLOSE l_dealtype_csr;
2121
2122 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
2123 -- get the tax owner (LESSOR/LESSEE) for the contract
2124
2125 okl_am_util_pvt.get_rule_record(p_rgd_code => 'LATOWN'
2126 ,p_rdf_code =>'LATOWN'
2127 ,p_chr_id => l_chr_id
2128 ,p_cle_id => NULL
2129 ,x_rulv_rec => l_rulv_rec
2130 ,x_return_status => x_return_status
2131 ,x_msg_count => x_msg_count
2132 ,x_msg_data => x_msg_data);
2133
2134 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2135 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2136 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2137 RAISE OKC_API.G_EXCEPTION_ERROR;
2138 END IF;
2139
2140 -- check if tax owner is defined
2141 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
2142
2143 x_return_status := OKL_API.G_RET_STS_ERROR;
2144 -- tax owner is not defined for contract CONTRACT_NUMBER.
2145 OKL_API.set_message( p_app_name => 'OKL',
2146 p_msg_name => 'OKL_AM_NO_TAX_OWNER',
2147 p_token1 => 'CONTRACT_NUMBER',
2148 p_token1_value => l_contract_number);
2149 RAISE OKC_API.G_EXCEPTION_ERROR;
2150
2151 ELSE
2152 -- l_rulv_rec.RULE_INFORMATION1 will contain the value 'LESSEE' or 'LESSOR'
2153 l_tax_owner := l_rulv_rec.RULE_INFORMATION1;
2154 END IF;
2155
2156 -- get the residual value of the fin asset
2157 OPEN l_linesfullv_csr(p_financial_asset_id);
2158 FETCH l_linesfullv_csr INTO l_name, l_residual_value;
2159 CLOSE l_linesfullv_csr;
2160
2161 IF l_residual_value IS NULL THEN
2162 x_return_status := OKL_API.G_RET_STS_ERROR;
2163 -- Residual value is not defined for the asset
2164 OKC_API.set_message( p_app_name => 'OKL',
2165 p_msg_name => 'OKL_AM_NO_RESIDUAL_VALUE',
2166 p_token1 => 'ASSET_NUMBER',
2167 p_token1_value => l_name);
2168
2169
2170 RAISE OKC_API.G_EXCEPTION_ERROR;
2171 END IF;
2172
2173 -- rbruno 5436987 -- start
2174 l_contract_currency_code := OKL_AM_UTIL_PVT.get_chr_currency(l_chr_id);
2175 l_functional_currency_code := OKL_AM_UTIL_PVT.get_functional_currency;
2176
2177 -- currency codes different so need for conversion
2178 IF l_contract_currency_code <> l_functional_currency_code THEN
2179
2180 -- convert the residual value obtained to functional currency
2181 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
2182 p_khr_id => l_chr_id,
2183 p_to_currency => l_functional_currency_code,
2184 p_transaction_date => l_quote_eff_date,
2185 p_amount => l_residual_value, -- convert residual value from Contract to Functional currency
2186 x_return_status => l_return_status,
2187 x_contract_currency => l_contract_currency_code,
2188 x_currency_conversion_type => l_currency_conversion_type,
2189 x_currency_conversion_rate => l_currency_conversion_rate,
2190 x_currency_conversion_date => l_currency_conversion_date,
2191 x_converted_amount => l_converted_amount);
2192
2193 l_residual_value := l_converted_amount; -- residual value is now converted to functional currency
2194
2195 END IF;
2196 --rbruno 5436987 -- End
2197
2198
2199 IF l_residual_value < 0 THEN
2200 x_return_status := OKL_API.G_RET_STS_ERROR;
2201 -- Residual value is negative for the asset
2202 OKC_API.set_message( p_app_name => 'OKL',
2203 p_msg_name => 'OKL_AM_INVALID_RESIDUAL',
2204 p_token1 => 'ASSET_NUMBER',
2205 p_token1_value => l_name);
2206
2207
2208 RAISE OKC_API.G_EXCEPTION_ERROR;
2209 END IF;
2210 END IF;
2211
2212
2213
2214 --SECHAWLA 21-NOV-2003 3262519 : end new code
2215
2216
2217 -- SECHAWLA Bug # 2701440 : Changed OPEN, FETCH to a curosr FOR LOOP, as this cursor now has multiple rows
2218 -- for a given asset id : one row for the corporate book and one or more rows for the tax books
2219 FOR l_okxassetlines_rec IN l_okxassetlines_csr LOOP
2220
2221 --SECHAWLA 21-NOV-2003 3262519 : calculate delta cost for this book class (corporate / tax)
2222 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
2223
2224
2225 --SECHAWLA 10-FEB-06 5016156 Check if any off-lease transactions exist for the asset
2226 -- This will tell if it is termination with purchase or without purchase
2227 l_trx_status := NULL;
2228 --Bug# 9838432: Added parameter p_financial_asset_id
2229 FOR l_offlseassettrx_rec IN l_offlseassettrx_csr(l_quote_eff_date, l_name, p_financial_asset_id) LOOP
2230 l_trx_status := l_offlseassettrx_rec.tsu_code;
2231 IF l_trx_status IN ('ENTERED','ERROR','CANCELED') THEN
2232 EXIT;
2233 END IF;
2234 END LOOP;
2235
2236 IF l_trx_status IS NULL THEN -- This means off-lease trx don't exist. It is termination with purchase
2237 --SECHAWLA 10-FEB-06 5016156: end
2238 l_delta_cost := l_residual_value - l_okxassetlines_rec.cost;
2239 --SECHAWLA 10-FEB-06 5016156 : begin
2240 ELSIF l_trx_status IN ('ENTERED','ERROR') THEN -- if any trx has this status
2241 x_return_status := OKL_API.G_RET_STS_ERROR;
2242 OKL_API.set_message( p_app_name => 'OKL',
2243 p_msg_name => 'OKL_AM_PENDING_OFFLEASE',
2244 p_token1 => 'ASSET_NUMBER',
2245 p_token1_value => l_name);
2246
2247
2248 RAISE OKC_API.G_EXCEPTION_ERROR;
2249
2250
2251 ELSIF l_trx_status IN ( 'PROCESSED','CANCELED') THEN
2252 l_delta_cost := 0; -- no cost update required, as cost has already been updated thru off lease trx
2253 END IF; -- or off-lease trx has been canceled
2254 --SECHAWLA 10-FEB-06 5016156 : end
2255
2256 END IF;
2257 --SECHAWLA 21-NOV-2003 3262519 : end
2258
2259
2260 --SECHAWLA 05-FEB-03 Bug # 2781557 : Moved the following code from do_full_units_retirement, as the check
2261 -- whether the asset is added in the current open period, needs to be done at this stage.
2262
2263 -- This piece of code is included temporarily as a work around , since FA API has errors
2264 -- Set the Fiscal Year name in teh cache,if not already set
2265 -- IF fa_cache_pkg.fazcbc_record.fiscal_year_name IS NULL THEN
2266 OPEN l_bookcontrols_csr(l_okxassetlines_rec.book_type_code);
2267 FETCH l_bookcontrols_csr INTO l_fiscal_year_name;
2268 IF l_bookcontrols_csr%NOTFOUND OR l_fiscal_year_name IS NULL THEN
2269 x_return_status := OKL_API.G_RET_STS_ERROR;
2270 -- Fiscal Year Name is required
2271 OKC_API.set_message( p_app_name => 'OKC',
2272 p_msg_name => G_REQUIRED_VALUE,
2273 p_token1 => G_COL_NAME_TOKEN,
2274 p_token1_value => 'Fiscal Year Name');
2275
2276
2277 RAISE OKC_API.G_EXCEPTION_ERROR;
2278 END IF;
2279 CLOSE l_bookcontrols_csr;
2280 fa_cache_pkg.fazcbc_record.fiscal_year_name := l_fiscal_year_name;
2281 -- END IF;
2282
2283
2284 IF NOT FA_UTIL_PVT.get_period_rec
2285 (
2286 p_book => l_okxassetlines_rec.book_type_code,
2287 p_effective_date => NULL,
2288 x_period_rec => l_period_rec
2289 ) THEN
2290
2291 x_return_status := OKC_API.G_RET_STS_ERROR;
2292 --Error getting current open period for the book BOOK_TYPE_CODE.
2293 OKL_API.set_message(
2294 p_app_name => 'OKL',
2295 p_msg_name => 'OKL_AM_OPEN_PERIOD_ERR',
2296 p_token1 => 'BOOK_CLASS',
2297 p_token1_value => lower(l_okxassetlines_rec.book_class),
2298 p_token2 => 'BOOK_TYPE_CODE',
2299 p_token2_value => l_okxassetlines_rec.book_type_code
2300 );
2301
2302
2303 RAISE OKC_API.G_EXCEPTION_ERROR;
2304 END IF;
2305
2306 --- check period of addition. If 'N' then run retirements
2307 OPEN l_periodofaddition_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code,l_period_rec.period_open_date);
2308 FETCH l_periodofaddition_csr INTO l_count;
2309 CLOSE l_periodofaddition_csr;
2310
2311 IF (l_count <> 0) THEN
2312 l_period_of_addition := 'Y';
2313 ELSE
2314 l_period_of_addition := 'N';
2315 END IF;
2316
2317 /* bug 13581587: this restriction is removed by FA in R12. NOw, addition and
2318 retirement can be done in the period of addition.
2319
2320 IF l_period_of_addition = 'Y' THEN
2321 -- Can nor retire asset ASSET_NUMBER as the asset was added to the book
2322 -- in the current open period. Please retire the asset manually.
2323 x_return_status := OKC_API.G_RET_STS_ERROR;
2324
2325 OKL_API.set_message( p_app_name => 'OKL',
2326 p_msg_name => 'OKL_AM_RETIRE_MANUALLY',
2327 p_token1 => 'ASSET_NUMBER',
2328 p_token1_value => l_okxassetlines_rec.asset_number,
2329 p_token2 => 'BOOK_CLASS',
2330 p_token2_value => lower(l_okxassetlines_rec.book_class),
2331 p_token3 => 'BOOK_TYPE_CODE',
2332 p_token3_value => l_okxassetlines_rec.book_type_code);
2333
2334 RAISE OKC_API.G_EXCEPTION_ERROR;
2335
2336 END IF;
2337 */ -- bug 13581587.
2338
2339 /* ansethur for bug 5664106 -- start
2340 -- SECHAWLA 03-JUN-03 Bug 2999419: Added the following validation
2341 IF l_okxassetlines_rec.prorate_convention_code IS NULL THEN
2342 x_return_status := OKL_API.G_RET_STS_ERROR;
2343 -- Unable to find retirement prorate convention for asset ASSET_NUMBER and book BOOK_TYPE_CODE.
2344 OKC_API.set_message( p_app_name => 'OKL',
2345 p_msg_name => 'OKL_AM_NO_PRORATE_CONVENTION',
2346 p_token1 => 'ASSET_NUMBER',
2347 p_token1_value => l_okxassetlines_rec.asset_number,
2348 p_token2 => 'BOOK_CLASS',
2349 p_token2_value => l_okxassetlines_rec.book_class,
2350 p_token3 => 'BOOK_TYPE_CODE',
2351 p_token3_value => l_okxassetlines_rec.book_type_code);
2352 RAISE OKC_API.G_EXCEPTION_ERROR;
2353 END IF;
2354 -- SECHAWLA 03-JUN-03 Bug 2999419 : end new code
2355 */ -- ansethur for bug 5655545 - end
2356
2357
2358 ---------- SECHAWLA 05-FEB-03 Bug # 2781557 : end moved code ----------------
2359
2360
2361 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM OR p_quantity = l_okxassetlines_rec.current_units THEN
2362 -- user sent request for full retirement
2363
2364 -- check if asset has already been fully/partially retired .
2365 l_retired_quantity := 0;
2366 l_non_retired_quantity := 0;
2367
2368 --This FOR loop will be executed only for the corporate book, as tax books do not have any distributions
2369 -- loop thru all the retirement records for this asset and calculate the total retired quantity
2370 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2371 IF l_disthist_rec.retirement_id IS NOT NULL THEN
2372 l_retired_quantity := l_retired_quantity + abs(l_disthist_rec.transaction_units);
2373 ELSE
2374 l_non_retired_quantity := l_non_retired_quantity + l_disthist_rec.units_assigned;
2375 END IF;
2376 END LOOP;
2377
2378 --For TAX books, both l_retired_quantity and l_non_retired_quantity will be 0 at this stage.
2379
2380
2381 IF l_retired_quantity = 0 AND l_non_retired_quantity > 0 THEN -- True only for corporate book
2382 IF l_non_retired_quantity = l_okxassetlines_rec.current_units THEN --distribution qty = orginal asset qty
2383
2384 -- user requested for full retirement and none of the units have been retired so far
2385 -- perform full retirement
2386
2387 -- we are passing the total number of units and not the cost, for full retirements, because for
2388 -- Direct Finance Lease, okx_asset_lines_v, gives OEC as the cost. FA Retirements compares this cost with
2389 --cost in fa_books. These 2 costs can be different, which will give error. So we are using units instead
2390 -- of cost to avoid that validation check.
2391
2392
2393
2394 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
2395
2396
2397
2398 do_full_units_retirement(
2399 p_api_version => p_api_version,
2400 p_init_msg_list => OKC_API.G_FALSE,
2401 p_tax_owner => l_tax_owner,
2402 p_delta_cost => l_delta_cost,
2403 p_asset_id => l_okxassetlines_rec.asset_id,
2404 p_asset_number => l_okxassetlines_rec.asset_number,
2405 p_proceeds_of_sale => l_proceeds_of_sale,
2406 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : changed the parameter name
2407 p_book_type_code => l_okxassetlines_rec.book_type_code,
2408 --p_cost => l_cost,
2409 p_units => l_okxassetlines_rec.current_units,
2410 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2411 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2412 x_msg_count => x_msg_count,
2413 x_msg_data => x_msg_data,
2414 x_return_status => x_return_status,
2415 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2416 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2417
2418
2419 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2420 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2421 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2422 RAISE OKC_API.G_EXCEPTION_ERROR;
2423 END IF;
2424
2425 l_units_retired := l_okxassetlines_rec.current_units;
2426 ELSE
2427 -- distribution qty is either less or more than the current units
2428 -- and hence we need to consider this as partial retirement, even though the sold
2429 -- quantity = asset quantity (current_units)
2430
2431 IF l_non_retired_quantity > l_okxassetlines_rec.current_units THEN
2432 l_units_to_be_retired := l_okxassetlines_rec.current_units;
2433 ELSE
2434 l_units_to_be_retired := l_non_retired_quantity;
2435 END IF;
2436
2437 -- l_dist_quantity := l_current_units;
2438 l_dist_quantity := l_units_to_be_retired;
2439 i := 0;
2440
2441
2442 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2443 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2444 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2445 -- than requested units then retire that distribution fully and move to next distribution for remaining
2446 -- units, until all the requested units have been retired.
2447
2448
2449 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2450 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2451 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2452 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2453 l_dist_quantity := 0;
2454 EXIT;
2455 ELSE
2456 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2457 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2458 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2459 END IF;
2460 i := i + 1;
2461 END LOOP;
2462
2463
2464 -- If there are no more distributions left and there are still some more units to be retired,
2465 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
2466 -- assigned to all the distributions.
2467
2468
2469 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
2470 -- x_return_status := OKL_API.G_RET_STS_ERROR;
2471 -- Sold quantity is more than the total quantity assigned to asset distributions.
2472 OKC_API.set_message( p_app_name => 'OKL',
2473 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
2474 --RAISE okc_api.G_EXCEPTION_ERROR;
2475 END IF;
2476
2477 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameters to the following procedure call
2478
2479
2480 do_partial_units_retirement(
2481 p_api_version => p_api_version,
2482 p_init_msg_list => OKC_API.G_FALSE,
2483 p_tax_owner => l_tax_owner,
2484 p_delta_cost => l_delta_cost,
2485 p_asset_id => l_okxassetlines_rec.asset_id,
2486 p_asset_number => l_okxassetlines_rec.asset_number,
2487 p_proceeds_of_sale => l_proceeds_of_sale,
2488 -- p_corporate_book => p_corporate_book, --SECHAWLA Bug # 2701440 : changed the parameter name
2489 p_book_type_code => l_okxassetlines_rec.book_type_code,
2490 p_total_quantity => l_units_to_be_retired, -- units to be retired
2491 p_dist_tbl => l_dist_tbl,
2492 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2493 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2494 x_msg_count => x_msg_count,
2495 x_msg_data => x_msg_data,
2496 x_return_status => x_return_status,
2497 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2498 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2499
2500
2501 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2502 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2503 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2504 RAISE OKC_API.G_EXCEPTION_ERROR;
2505 END IF;
2506
2507 l_units_retired := l_units_to_be_retired ;
2508 END IF; --l_non_retired_quantity = l_quantity
2509
2510
2511 ELSIF l_retired_quantity = l_okxassetlines_rec.current_units AND l_non_retired_quantity = 0 THEN -- retired qty = current units
2512 -- Asset is already fully retired.
2513 OKC_API.set_message( p_app_name => 'OKL',
2514 p_msg_name => 'OKL_AM_ALREADY_RETIRED',
2515 p_token1 => 'ASSET_NUMBER',
2516 p_token1_value => l_okxassetlines_rec.asset_number);
2517 l_already_retired := 'Y';
2518
2519 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
2520 -- non-retired qty can be either less or more than l_quantity
2521 IF l_non_retired_quantity >= l_okxassetlines_rec.current_units THEN
2522 l_units_to_be_retired := l_okxassetlines_rec.current_units;
2523 ELSE
2524 l_units_to_be_retired := l_non_retired_quantity;
2525 END IF;
2526
2527
2528 -- l_dist_quantity := l_current_units;
2529 l_dist_quantity := l_units_to_be_retired;
2530 i := 0;
2531
2532 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2533 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2534 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2535 -- than requested units then retire that distribution fully and move to next distribution for remaining
2536 -- units, until all the requested units have been retired.
2537
2538
2539 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2540 IF l_disthist_rec.retirement_id IS NULL THEN
2541 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2542 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2543 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2544 l_dist_quantity := 0;
2545 EXIT;
2546 ELSE
2547 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2548 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2549 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2550 END IF;
2551 i := i + 1;
2552 END IF;
2553 END LOOP;
2554
2555 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
2556 do_partial_units_retirement(
2557 p_api_version => p_api_version,
2558 p_init_msg_list => OKC_API.G_FALSE,
2559 p_tax_owner => l_tax_owner,
2560 p_delta_cost => l_delta_cost,
2561 p_asset_id => l_okxassetlines_rec.asset_id,
2562 p_asset_number => l_okxassetlines_rec.asset_number,
2563 p_proceeds_of_sale => l_proceeds_of_sale,
2564 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : changed the parameter name
2565 p_book_type_code => l_okxassetlines_rec.book_type_code,
2566 p_total_quantity => l_units_to_be_retired, -- units to be retired
2567 p_dist_tbl => l_dist_tbl,
2568 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2569 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2570 x_msg_count => x_msg_count,
2571 x_msg_data => x_msg_data,
2572 x_return_status => x_return_status,
2573 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2574 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2575
2576
2577 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2578 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2579 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2580 RAISE OKC_API.G_EXCEPTION_ERROR;
2581 END IF;
2582
2583 l_units_retired := l_units_to_be_retired;
2584 ELSIF l_retired_quantity > l_okxassetlines_rec.current_units AND l_non_retired_quantity = 0 THEN
2585 -- x_return_status := OKL_API.G_RET_STS_ERROR;
2586 -- Asset ASSET_NUMBER is already retired with invalid retired quantity which is more than the original quantity.
2587 OKC_API.set_message( p_app_name => 'OKL',
2588 p_msg_name => 'OKL_AM_INVALID_RETIRED_QTY',
2589 p_token1 => 'ASSET_NUMBER',
2590 p_token1_value => l_okxassetlines_rec.asset_number);
2591 -- RAISE okc_api.G_EXCEPTION_ERROR;
2592
2593 ELSIF l_retired_quantity < l_okxassetlines_rec.current_units AND l_non_retired_quantity > 0 THEN
2594
2595
2596
2597 IF l_non_retired_quantity >= l_okxassetlines_rec.current_units THEN
2598 l_units_to_be_retired := l_okxassetlines_rec.current_units;
2599 ELSE
2600 l_units_to_be_retired := l_non_retired_quantity;
2601 END IF;
2602
2603 -- l_dist_quantity := l_current_units;
2604 l_dist_quantity := l_units_to_be_retired;
2605 i := 0;
2606
2607 -- l_disthist_csr picks up all active distributions, which could possibly be retired.
2608 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2609 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2610 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2611 -- than requested units then retire that distribution fully and move to next distribution for remaining
2612 -- units, until all the requested units have been retired.
2613
2614 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
2615 IF l_disthist_rec.retirement_id IS NULL THEN
2616 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2617
2618 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2619 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2620 l_dist_quantity := 0;
2621 EXIT;
2622 ELSE
2623 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2624 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2625 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2626 END IF;
2627 i := i + 1;
2628 END IF;
2629 END LOOP;
2630
2631 -- If there are no more active distributions left and there are still some more units to be retired,
2632 -- then the input quantity was invalid. Quantity can not be more than the sum total of the units
2633 -- assigned to non-retired distributions.
2634
2635 IF l_dist_quantity > 0 THEN
2636 IF l_retired_quantity < l_dist_quantity THEN
2637 -- x_return_status := OKL_API.G_RET_STS_ERROR;
2638 -- Sold quantity is more than the total quantity assigned to asset distributions.
2639 OKC_API.set_message( p_app_name => 'OKL',
2640 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
2641 -- RAISE okc_api.G_EXCEPTION_ERROR;
2642 END IF;
2643 END IF;
2644
2645 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
2646 do_partial_units_retirement(
2647 p_api_version => p_api_version,
2648 p_init_msg_list => OKC_API.G_FALSE,
2649 p_tax_owner => l_tax_owner,
2650 p_delta_cost => l_delta_cost,
2651 p_asset_id => l_okxassetlines_rec.asset_id,
2652 p_asset_number => l_okxassetlines_rec.asset_number,
2653 p_proceeds_of_sale => l_proceeds_of_sale,
2654 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 :changed the parameter name
2655 p_book_type_code => l_okxassetlines_rec.book_type_code,
2656 p_total_quantity => l_units_to_be_retired, -- units to be retired
2657 p_dist_tbl => l_dist_tbl,
2658 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2659 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 : added
2660 x_msg_count => x_msg_count,
2661 x_msg_data => x_msg_data,
2662 x_return_status => x_return_status,
2663 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2664 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2665
2666 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2667 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2668 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2669 RAISE OKC_API.G_EXCEPTION_ERROR;
2670 END IF;
2671
2672 l_units_retired := l_units_to_be_retired;
2673
2674
2675 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
2676 ELSIF l_retired_quantity = 0 AND l_non_retired_quantity = 0 THEN -- This condition will be true only for TAX books
2677
2678 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
2679 -- do cost retirement for the tax book
2680
2681 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
2682 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
2683 -- Cost Adjustment will happen in tax book through do_cost_retirement
2684 -- Cost will become Residual Value
2685 l_cost := l_okxassetlines_rec.cost; -- Retire Asset Cost -- for bug 5760603 - Earlier RV
2686 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
2687 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
2688 l_cost := l_okxassetlines_rec.cost; -- Retire the current cost in FA -- this is FA Cost
2689 END IF;
2690 -- SECHAWLA 21-NOV-2003 3262519 : end
2691
2692
2693 -- SECHAWLA 21-nov-03 3262519 Added tax owner and delta cost parameters
2694 do_cost_retirement(
2695 p_api_version => p_api_version,
2696 p_init_msg_list => OKC_API.G_FALSE,
2697 p_tax_owner => l_tax_owner,
2698 p_delta_cost => l_delta_cost,
2699 p_asset_id => l_okxassetlines_rec.asset_id,
2700 p_asset_number => l_okxassetlines_rec.asset_number,
2701 p_proceeds_of_sale => l_proceeds_of_sale,
2702 p_tax_book => l_okxassetlines_rec.book_type_code,
2703 -- p_cost => l_cost_retired, -- SECHAWLA 13-JAN-03 Bug # 2701440
2704 -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the original request is for Full retirement, do a full cost retirement for the tax book
2705 -- p_cost => l_okxassetlines_rec.cost, -- SECHAWLA 21-NOV-2003 3262519
2706 p_cost => l_cost, -- SECHAWLA 21-NOV-2003 3262519
2707 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419: Added this parameter
2708 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
2709 x_msg_count => x_msg_count,
2710 x_msg_data => x_msg_data,
2711 x_return_status => x_return_status,
2712 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2713 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2714
2715 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2716 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2717 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2718 RAISE OKC_API.G_EXCEPTION_ERROR;
2719 END IF;
2720 END IF;
2721 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
2722 END IF;
2723
2724
2725
2726 ELSE -- input quantity is either less or more than the current units
2727 -- user requested for partial retirement by p_quantity units
2728
2729 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN
2730
2731 l_dist_quantity := p_quantity;
2732
2733 i := 0;
2734
2735 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
2736 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
2737 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
2738 -- than requested units then retire that distribution fully and move to next distribution for remaining
2739 -- units, until all the requested units have been retired.
2740
2741
2742 -- l_disthist_csr picks up all active distributions, which could possibly be retired.
2743
2744 l_retired_dist_units := 0;
2745
2746 -- This loop is executed only for corporate book, as tax book does not have any distributions
2747 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.corporate_book) LOOP
2748 -- First retire all non-retired distributions, maintain a unit count of already retired distributions.
2749 -- We will use this count at the end, when all non-retired distributions have been retired, to make
2750 -- sure that the units retired = input quantity
2751
2752 IF l_disthist_rec.retirement_id IS NOT NULL THEN
2753 l_retired_dist_units := l_retired_dist_units + abs(l_disthist_rec.transaction_units);
2754 ELSE
2755
2756 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
2757
2758 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2759 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
2760 l_dist_quantity := 0;
2761 EXIT;
2762 ELSE
2763 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
2764 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
2765 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
2766 END IF;
2767 i := i + 1;
2768 END IF;
2769 END LOOP;
2770
2771 -- If there are no more active distributions left and there are still some more units to be retired,
2772 -- then the input quantity was invalid. Quantity can not be more than the sum total of the units
2773 -- assigned to non-retired distributions.
2774
2775 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
2776
2777 IF l_retired_dist_units < l_dist_quantity THEN -- retired quantity isn't enough to match up with total qty
2778 -- Sold quantity is more than the total quantity assigned to asset distributions.
2779 OKC_API.set_message( p_app_name => 'OKL',
2780 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
2781 END IF;
2782 l_units_to_be_retired := p_quantity - l_dist_quantity;
2783 ELSE
2784 l_units_to_be_retired := p_quantity;
2785 END IF;
2786
2787 IF l_dist_tbl.COUNT > 0 THEN
2788 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
2789 do_partial_units_retirement(
2790 p_api_version => p_api_version,
2791 p_init_msg_list => OKC_API.G_FALSE,
2792 p_tax_owner => l_tax_owner,
2793 p_delta_cost => l_delta_cost,
2794 p_asset_id => l_okxassetlines_rec.asset_id,
2795 p_asset_number => l_okxassetlines_rec.asset_number,
2796 p_proceeds_of_sale => l_proceeds_of_sale,
2797 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : changed the parameter name
2798 p_book_type_code => l_okxassetlines_rec.book_type_code,
2799 p_total_quantity => l_units_to_be_retired, -- quantity to be retired
2800 p_dist_tbl => l_dist_tbl,
2801 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2802 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
2803 x_msg_count => x_msg_count,
2804 x_msg_data => x_msg_data,
2805 x_return_status => x_return_status,
2806 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2807 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2808
2809 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2810 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2811 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2812 RAISE OKC_API.G_EXCEPTION_ERROR;
2813 END IF;
2814
2815
2816 /* SECHAWLA 21-NOV-03 3262519 : This fetch is not required. Cost to be retired from the tax book should be
2817 -- calculated using tax book cost and not the corporate book cost, as the 2 costs can be different
2818
2819 -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this code to get the cost retired for the corporate book
2820 -- This cost is used later to perform a cost retirement for the TAX book
2821 OPEN l_faretirement_csr(l_okxassetlines_rec.asset_id,l_okxassetlines_rec.corporate_book);
2822 FETCH l_faretirement_csr INTO l_cost_retired;
2823 -- Since asset is first retired from corporate book, this fetch will definitely find a row
2824 CLOSE l_faretirement_csr;
2825 */
2826
2827
2828
2829 l_units_retired := l_units_to_be_retired;
2830 ELSE
2831 -- If it reaches here, it means it didn't find any new distributions to retire. Since we are not
2832 -- processing any records in FA in this case, we consider this asset as already retired.
2833
2834 l_already_retired := 'Y';
2835 END IF;
2836 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
2837 ELSIF l_okxassetlines_rec.book_class = 'TAX' THEN
2838
2839 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
2840
2841 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
2842 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
2843 -- Cost Adjustment will happen in tax book through do_cost_retirement
2844 -- Cost will become Residual Value
2845 l_cost := l_okxassetlines_rec.cost; -- for bug 5760603 -- Retire cost Not Rv
2846 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
2847 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
2848 l_cost := l_okxassetlines_rec.cost; -- cost to be considered is the curent cost
2849 END IF;
2850 -- SECHAWLA 21-NOV-2003 3262519 : end
2851
2852 -- 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
2853 --IF l_okxassetlines_rec.cost >= l_cost_retired THEN -- SECHAWLA 21-nov-03 3262519
2854
2855 --SECHAWLA 21-NOV-2003 3262519 : Cost to be retired from tax book should be calculated using
2856 -- tax book's cost and the quentity retired in the corporate book
2857 l_cost_retired := (l_cost / l_okxassetlines_rec.current_units ) * l_units_retired;
2858
2859 IF l_cost >= l_cost_retired THEN
2860 -- This condition should always be true
2861 -- do cost retirement for the tax book
2862
2863 -- SECHAWLA 21-nov-03 3262519 Added tax owner and delta cost parameters
2864
2865
2866 do_cost_retirement(
2867 p_api_version => p_api_version,
2868 p_init_msg_list => OKC_API.G_FALSE,
2869 p_tax_owner => l_tax_owner,
2870 p_delta_cost => l_delta_cost,
2871 p_asset_id => l_okxassetlines_rec.asset_id,
2872 p_asset_number => l_okxassetlines_rec.asset_number,
2873 p_proceeds_of_sale => l_proceeds_of_sale,
2874 p_tax_book => l_okxassetlines_rec.book_type_code,
2875 p_cost => l_cost_retired,
2876 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2877 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
2878 x_msg_count => x_msg_count,
2879 x_msg_data => x_msg_data,
2880 x_return_status => x_return_status,
2881 p_quote_eff_date => l_quote_eff_date, -- rmunjulu EDAT 10-Jan-2005
2882 p_quote_accpt_date => l_quote_accpt_date ); -- rmunjulu EDAT 10-Jan-2005
2883
2884 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2885 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2886 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2887 RAISE OKC_API.G_EXCEPTION_ERROR;
2888 END IF;
2889 -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the tax book's cost is less than the cost retierd from the corp book
2890 -- but has not been fully retired yet, then perform a full cost retirement for tax book
2891
2892 --SECHAWLA 21-NOV-2003 3262519 : This condition will not occur now that we calculate cost to be
2893 -- retired from tax book using tax book cost itself
2894 --ELSIF l_okxassetlines_rec.cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
2895 /* ELSIF l_cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
2896 -- retire the whole remaining cost
2897
2898 -- SECHAWLA 21-nov-03 3262519 Added tax owner and delta cost parameters
2899 do_cost_retirement(
2900 p_api_version => p_api_version,
2901 p_init_msg_list => OKC_API.G_FALSE,
2902 p_tax_owner => l_tax_owner,
2903 p_delta_cost => l_delta_cost,
2904 p_asset_id => l_okxassetlines_rec.asset_id,
2905 p_asset_number => l_okxassetlines_rec.asset_number,
2906 p_proceeds_of_sale => l_proceeds_of_sale,
2907 p_tax_book => l_okxassetlines_rec.book_type_code,
2908 --p_cost => l_okxassetlines_rec.cost -- SECHAWLA 21-nov-03 3262519
2909 p_cost => l_cost, -- SECHAWLA 21-nov-03 3262519
2910 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
2911 x_msg_count => x_msg_count,
2912 x_msg_data => x_msg_data,
2913 x_return_status => x_return_status);
2914
2915 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2916 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2917 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2918 RAISE OKC_API.G_EXCEPTION_ERROR;
2919 END IF;
2920 */
2921 END IF;
2922
2923
2924 END IF;
2925
2926 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
2927 END IF;
2928
2929 END IF; -- IF l_orderlines_rec.ordered_quantity = l_quantity THEN
2930
2931 IF l_already_retired = 'N' THEN
2932 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN -- SECHAWLA Bug # 2701440 : Added this condition to
2933 -- store trx transaction and process a/c entries only
2934 -- for CORPORATE book
2935 -- Store Transaction in OKL
2936 okl_am_util_pvt.get_transaction_id(p_try_name => l_trx_type,
2937 x_return_status => x_return_status,
2938 x_try_id => l_try_id);
2939
2940 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2941 -- Unable to find a transaction type for this transaction.
2942 OKL_API.set_message(p_app_name => 'OKL',
2943 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
2944 p_token1 => 'TRY_NAME',
2945 p_token1_value => 'Asset Disposition');
2946 RAISE OKC_API.G_EXCEPTION_ERROR;
2947 END IF;
2948
2949
2950 lp_thpv_rec.tas_type := 'RFA';
2951 lp_thpv_rec.tsu_code := 'PROCESSED';
2952 lp_thpv_rec.try_id := l_try_id;
2953 lp_thpv_rec.date_trans_occurred := l_quote_accpt_date; -- rmunjulu EDAT changed from sysdate to accpt date
2954
2955 -- RRAVIKIR Legal Entity Changes
2956 lp_thpv_rec.legal_entity_id := p_legal_entity_id;
2957 -- Legal Entity Changes End
2958
2959 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def( p_api_version => p_api_version,
2960 p_init_msg_list => OKC_API.G_FALSE,
2961 x_return_status => x_return_status,
2962 x_msg_count => x_msg_count,
2963 x_msg_data => x_msg_data,
2964 p_thpv_rec => lp_thpv_rec,
2965 x_thpv_rec => lx_thpv_rec);
2966
2967 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2968 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2969 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2970 RAISE OKC_API.G_EXCEPTION_ERROR;
2971 END IF;
2972
2973 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
2974 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
2975 lp_tlpv_rec.currency_code := l_func_curr_code;
2976
2977 -- rbruno 5436987 -- start
2978 --lp_tlpv_rec.currency_code := l_func_curr_code;
2979 l_contract_currency_code := okl_am_util_pvt.get_chr_currency(l_chr_id);
2980 lp_tlpv_rec.currency_code := l_contract_currency_code;
2981
2982 IF l_func_curr_code <> l_contract_currency_code THEN
2983
2984 -- get currency conversion parameters
2985 OKL_ACCOUNTING_UTIL.convert_to_functional_currency(
2986 p_khr_id => l_chr_id,
2987 p_to_currency => l_func_curr_code,
2988 p_transaction_date => l_quote_eff_date,
2989 p_amount => l_hard_coded_amount,
2990 x_return_status => x_return_status,
2991 x_contract_currency => l_contract_currency_code,
2992 x_currency_conversion_type => l_currency_conversion_type,
2993 x_currency_conversion_rate => l_currency_conversion_rate,
2994 x_currency_conversion_date => l_currency_conversion_date,
2995 x_converted_amount => l_converted_amount);
2996
2997 lp_tlpv_rec.currency_conversion_type := l_currency_conversion_type;
2998 lp_tlpv_rec.currency_conversion_rate := l_currency_conversion_rate;
2999 lp_tlpv_rec.currency_conversion_date := l_currency_conversion_date;
3000
3001 END IF;
3002 -- rbruno 5436987 -- end
3003
3004
3005 -- Create transaction Line
3006 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
3007 lp_tlpv_rec.iay_id := l_okxassetlines_rec.depreciation_category;
3008 lp_tlpv_rec.kle_id := p_financial_asset_id;
3009 lp_tlpv_rec.line_number := 1;
3010 lp_tlpv_rec.tal_type := 'RFL';
3011 lp_tlpv_rec.asset_number := l_okxassetlines_rec.asset_number;
3012 lp_tlpv_rec.corporate_book := l_okxassetlines_rec.book_type_code;
3013 lp_tlpv_rec.original_cost := l_okxassetlines_rec.original_cost;
3014 lp_tlpv_rec.current_units := l_okxassetlines_rec.current_units;
3015 lp_tlpv_rec.units_retired := l_units_retired ;
3016 lp_tlpv_rec.dnz_asset_id := l_okxassetlines_rec.asset_id;
3017 lp_tlpv_rec.dnz_khr_id := l_okxassetlines_rec.dnz_chr_id;
3018
3019 -- SECHAWLA 15-DEC-04 4028371 : set FA date on trx line
3020 lp_tlpv_rec.FA_TRX_DATE := l_fa_trx_date;
3021
3022 OKL_TXL_ASSETS_PUB.create_txl_asset_def(p_api_version => p_api_version,
3023 p_init_msg_list => OKC_API.G_FALSE,
3024 x_return_status => x_return_status,
3025 x_msg_count => x_msg_count,
3026 x_msg_data => x_msg_data,
3027 p_tlpv_rec => lp_tlpv_rec,
3028 x_tlpv_rec => lx_tlpv_rec);
3029
3030 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3031 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3032 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3033 RAISE OKC_API.G_EXCEPTION_ERROR;
3034 END IF;
3035
3036 -----------------end Store Transaction in OKL -----------------
3037
3038 -- make call to accounting entries
3039 process_accounting_entries(
3040 p_api_version => p_api_version,
3041 p_init_msg_list => OKC_API.G_FALSE,
3042 x_return_status => x_return_status,
3043 x_msg_count => x_msg_count,
3044 x_msg_data => x_msg_data,
3045 p_kle_id => p_financial_asset_id,
3046 p_try_id => l_try_id,
3047 p_sys_date => l_quote_eff_date, -- rbruno EDAT Changed from sysdate to acceptance date -- rbruno bug 5436987
3048 p_source_id => lx_tlpv_rec.id,
3049 p_trx_type => l_trx_name,
3050 p_amount => l_proceeds_of_sale,
3051 p_func_curr_code => l_func_curr_code,
3052 x_total_amount => lx_total_amount,
3053 --akrangan start
3054 p_legal_entity_id => p_legal_entity_id
3055 --akrangan end
3056 );
3057
3058 -- rollback if error in accounting entries
3059 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3060 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3061 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3062 RAISE OKC_API.G_EXCEPTION_ERROR;
3063 END IF;
3064
3065 -- Store the amount at the header and line level in trx tables
3066
3067 -- Update amount in the header table
3068 lp_thpv_rec := lp_thpv_empty_rec;
3069 lp_thpv_rec.id := lx_thpv_rec.id;
3070 lp_thpv_rec.total_match_amount := lx_total_amount;
3071
3072 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
3073 p_api_version => p_api_version,
3074 p_init_msg_list => OKC_API.G_FALSE,
3075 x_return_status => x_return_status,
3076 x_msg_count => x_msg_count,
3077 x_msg_data => x_msg_data,
3078 p_thpv_rec => lp_thpv_rec,
3079 x_thpv_rec => lx_thpv_rec);
3080
3081 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3082 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3083 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3084 RAISE OKC_API.G_EXCEPTION_ERROR;
3085 END IF;
3086
3087 -- Update amount in the lines table.
3088 lp_tlpv_rec := lp_tlpv_empty_rec;
3089 lp_tlpv_rec.id := lx_tlpv_rec.id;
3090 lp_tlpv_rec.match_amount := lx_total_amount;
3091
3092 --SECHAWLA 03-JAN-03 Added the following statement as a temporary fix to LA's ROUNDING ERROR problem
3093 lp_tlpv_rec.kle_id := p_financial_asset_id;
3094
3095 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
3096 lp_tlpv_rec.currency_code := l_func_curr_code;
3097
3098 -- rbruno 5436987 --- start
3099 --lp_tlpv_rec.currency_code := l_func_curr_code;
3100 IF l_func_curr_code <> l_contract_currency_code THEN
3101 lp_tlpv_rec.currency_conversion_type := l_currency_conversion_type;
3102 lp_tlpv_rec.currency_conversion_rate := l_currency_conversion_rate;
3103 lp_tlpv_rec.currency_conversion_date := l_currency_conversion_date;
3104 END IF;
3105 lp_tlpv_rec.currency_code := l_contract_currency_code;
3106 --rbruno 5436987 --- end
3107
3108
3109 OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
3110 p_api_version => p_api_version,
3111 p_init_msg_list => OKC_API.G_FALSE,
3112 x_return_status => x_return_status,
3113 x_msg_count => x_msg_count,
3114 x_msg_data => x_msg_data,
3115 p_tlpv_rec => lp_tlpv_rec,
3116 x_tlpv_rec => lx_tlpv_rec);
3117
3118 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3119 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3120 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3121 RAISE OKC_API.G_EXCEPTION_ERROR;
3122 END IF;
3123
3124 END IF;
3125 END IF; -- if l_already_retired = 'N'
3126
3127
3128 END LOOP;
3129
3130
3131 --- Expire item in Installed Base
3132 -- commented call to expire_item djanaswa bug 6736148 start
3133 /*
3134 IF p_quantity IS NULL OR p_quantity = OKL_API.G_MISS_NUM THEN
3135 -- Retire all existing instances
3136 FOR l_itemlocation_rec in l_itemlocation_csr LOOP
3137
3138 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
3139 expire_item (
3140 p_api_version => p_api_version,
3141 p_init_msg_list => OKC_API.G_FALSE,
3142 x_msg_count => x_msg_count,
3143 x_msg_data => x_msg_data,
3144 x_return_status => x_return_status ,
3145 p_instance_id => l_itemlocation_rec.instance_id,
3146 p_end_date => l_sys_date); -- rmunjulu EDAT 23-Nov-04 -- change back to sysdate -- rmunjulu EDAT Changed from sysdate to eff date
3147
3148 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3149 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3150 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3151 RAISE OKC_API.G_EXCEPTION_ERROR;
3152 END IF;
3153
3154 END IF;
3155
3156 END LOOP;
3157
3158
3159
3160 ELSE -- quantity < original quantity
3161 instance_counter := 1;
3162 -- retire number of instances equal to the input quantity
3163 FOR l_itemlocation_rec in l_itemlocation_csr LOOP
3164
3165 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
3166 expire_item (
3167 p_api_version => p_api_version,
3168 p_init_msg_list => OKC_API.G_FALSE,
3169 x_msg_count => x_msg_count,
3170 x_msg_data => x_msg_data,
3171 x_return_status => x_return_status ,
3172 p_instance_id => l_itemlocation_rec.instance_id,
3173 p_end_date => l_sys_date); -- rmunjulu EDAT 23-Nov-04 Change back to sysdate -- rmunjulu EDAT Changed from sysdate to eff date
3174
3175 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3177 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3178 RAISE OKC_API.G_EXCEPTION_ERROR;
3179 END IF;
3180
3181 IF instance_counter = p_quantity THEN
3182 EXIT;
3183 END IF;
3184 instance_counter := instance_counter + 1;
3185 END IF;
3186
3187 END LOOP;
3188
3189 END IF;
3190 */
3191 -------------- end IB Retirement -----------------------
3192 -- commented call to expire_item djanaswa bug 6736148 end
3193
3194
3195
3196
3197
3198 -- Loop thru all the pending transactions in okl_trx_assets_v and okl_txl_assets_v
3199 -- and update the status to 'CANCELED'
3200
3201 FOR l_assettrx_rec IN l_assettrx_csr LOOP
3202 -- update the staus (tsu_code) in okl_trx_assets_v
3203 lp_thpv_rec := lp_thpv_empty_rec;
3204 lp_thpv_rec.id := l_assettrx_rec.id;
3205 lp_thpv_rec.tsu_code := 'CANCELED';
3206
3207 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
3208 p_api_version => p_api_version,
3209 p_init_msg_list => OKC_API.G_FALSE,
3210 x_return_status => x_return_status,
3211 x_msg_count => x_msg_count,
3212 x_msg_data => x_msg_data,
3213 p_thpv_rec => lp_thpv_rec,
3214 x_thpv_rec => lx_thpv_rec);
3215
3216 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3217 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3218 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3219 RAISE OKC_API.G_EXCEPTION_ERROR;
3220 END IF;
3221 END LOOP;
3222
3223
3224
3225 -- ++++++++++++++++++++ service contract integration begin ++++++++++++++++++
3226
3227 -- RMUNJULU 3061751 11-SEP-2003
3228 -- Check if linked service contract exists for the asset which is disposed
3229 l_service_int_needed := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed(
3230 p_asset_id => p_financial_asset_id,
3231 p_source => 'DISPOSE');
3232
3233 -- Do the Service Contract Integration Notification for DISPOSE
3234 OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration(
3235 p_transaction_id => p_financial_asset_id,
3236 p_transaction_date => l_quote_accpt_date, -- rmunjulu EDAT changed from sysdate to acceptance date
3237 p_source => 'DISPOSE_1',
3238 p_service_integration_needed => l_service_int_needed);
3239
3240 -- ++++++++++++++++++++ service contract integration end ++++++++++++++++++
3241
3242
3243 -- MDOKAL: 18-SEP-03 - Bug 3082639
3244 -------------- Securitization Processing -----------------------
3245
3246 OKL_AM_SECURITIZATION_PVT.process_securitized_streams(
3247 p_api_version => p_api_version,
3248 p_init_msg_list => OKC_API.G_FALSE,
3249 x_return_status => x_return_status,
3250 x_msg_count => x_msg_count,
3251 x_msg_data => x_msg_data,
3252 p_kle_id => p_financial_asset_id,
3253 p_sale_price => p_proceeds_of_sale,
3254 p_effective_date => l_quote_eff_date, -- rmunjulu EDAT Added
3255 p_transaction_date => l_quote_accpt_date, -- rmunjulu EDAT Added
3256 p_call_origin => OKL_SECURITIZATION_PVT.G_TRX_REASON_ASSET_DISPOSAL);
3257
3258 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3259 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3260 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3261 RAISE OKC_API.G_EXCEPTION_ERROR;
3262 END IF;
3263 -------------- end Securitization Processing -----------------------
3264 --akrangan added for sla populate sources cr start
3265 IF g_trans_id_tbl.COUNT > 0
3266 THEN
3267 FOR i IN g_trans_id_tbl.FIRST .. g_trans_id_tbl.LAST
3268 LOOP
3269 -- header record
3270 l_fxhv_rec.source_id := lx_thpv_rec.id;
3271 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
3272 l_fxhv_rec.khr_id := lx_tlpv_rec.dnz_khr_id;
3273 l_fxhv_rec.try_id := lx_thpv_rec.try_id;
3274 -- line record
3275 l_fxlv_rec.source_id := lx_tlpv_rec.id;
3276 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
3277 l_fxlv_rec.kle_id := p_financial_asset_id;
3278 l_fxlv_rec.asset_id := lx_tlpv_rec.dnz_asset_id;
3279 l_fxlv_rec.fa_transaction_id := g_trans_id_tbl(i);
3280 l_fxlv_rec.asset_book_type_name := lx_tlpv_rec.corporate_book;
3281
3282 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
3283 p_init_msg_list => okc_api.g_false,
3284 p_fxhv_rec => l_fxhv_rec,
3285 p_fxlv_rec => l_fxlv_rec,
3286 x_return_status => x_return_status,
3287 x_msg_count => x_msg_count,
3288 x_msg_data => x_msg_data);
3289
3290 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
3291 THEN
3292 RAISE okl_api.g_exception_unexpected_error;
3293 ELSIF (x_return_status = okc_api.g_ret_sts_error)
3294 THEN
3295 RAISE okl_api.g_exception_error;
3296 END IF;
3297 END LOOP;
3298 END IF;
3299 --akrangan added for sla populate sources cr end
3300
3301 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3302 EXCEPTION
3303 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3304
3305 IF l_okxassetlines_csr%ISOPEN THEN
3306 CLOSE l_okxassetlines_csr;
3307 END IF;
3308
3309 IF l_disthist_csr%ISOPEN THEN
3310 CLOSE l_disthist_csr;
3311 END IF;
3312
3313 IF l_assettrx_csr%ISOPEN THEN
3314 CLOSE l_assettrx_csr;
3315 END IF;
3316
3317 IF l_itemlocation_csr%ISOPEN THEN
3318 CLOSE l_itemlocation_csr;
3319 END IF;
3320
3321 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
3322 IF l_periodofaddition_csr%ISOPEN THEN
3323 CLOSE l_periodofaddition_csr;
3324 END IF;
3325
3326 IF l_bookcontrols_csr%ISOPEN THEN
3327 CLOSE l_bookcontrols_csr;
3328 END IF;
3329
3330 -- SECHAWLA 21-nov-03 3262519 : close new cursors
3331 IF l_okclines_csr%ISOPEN THEN
3332 CLOSE l_okclines_csr;
3333 END IF;
3334
3335 IF l_dealtype_csr%ISOPEN THEN
3336 CLOSE l_dealtype_csr;
3337 END IF;
3338
3339 IF l_linesfullv_csr%ISOPEN THEN
3340 CLOSE l_linesfullv_csr;
3341 END IF;
3342 -- SECHAWLA 21-nov-03 3262519 : end
3343
3344 --SECHAWLA 10-FEB-06 5016156
3345 IF l_offlseassettrx_csr%ISOPEN THEN
3346 CLOSE l_offlseassettrx_csr;
3347 END IF;
3348
3349
3350 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3351 (
3352 l_api_name,
3353 G_PKG_NAME,
3354 'OKC_API.G_RET_STS_ERROR',
3355 x_msg_count,
3356 x_msg_data,
3357 '_PVT'
3358 );
3359 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3360
3361 IF l_okxassetlines_csr%ISOPEN THEN
3362 CLOSE l_okxassetlines_csr;
3363 END IF;
3364
3365 IF l_disthist_csr%ISOPEN THEN
3366 CLOSE l_disthist_csr;
3367 END IF;
3368
3369 IF l_assettrx_csr%ISOPEN THEN
3370 CLOSE l_assettrx_csr;
3371 END IF;
3372
3373 IF l_itemlocation_csr%ISOPEN THEN
3374 CLOSE l_itemlocation_csr;
3375 END IF;
3376
3377 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
3378 IF l_periodofaddition_csr%ISOPEN THEN
3379 CLOSE l_periodofaddition_csr;
3380 END IF;
3381
3382 IF l_bookcontrols_csr%ISOPEN THEN
3383 CLOSE l_bookcontrols_csr;
3384 END IF;
3385
3386 -- SECHAWLA 21-nov-03 3262519 : close new cursors
3387 IF l_okclines_csr%ISOPEN THEN
3388 CLOSE l_okclines_csr;
3389 END IF;
3390
3391 IF l_dealtype_csr%ISOPEN THEN
3392 CLOSE l_dealtype_csr;
3393 END IF;
3394
3395 IF l_linesfullv_csr%ISOPEN THEN
3396 CLOSE l_linesfullv_csr;
3397 END IF;
3398 -- SECHAWLA 21-nov-03 3262519 : end
3399
3400 --SECHAWLA 10-FEB-06 5016156
3401 IF l_offlseassettrx_csr%ISOPEN THEN
3402 CLOSE l_offlseassettrx_csr;
3403 END IF;
3404
3405 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3406 (
3407 l_api_name,
3408 G_PKG_NAME,
3409 'OKC_API.G_RET_STS_UNEXP_ERROR',
3410 x_msg_count,
3411 x_msg_data,
3412 '_PVT'
3413 );
3414 WHEN OTHERS THEN
3415
3416 IF l_okxassetlines_csr%ISOPEN THEN
3417 CLOSE l_okxassetlines_csr;
3418 END IF;
3419
3420 IF l_disthist_csr%ISOPEN THEN
3421 CLOSE l_disthist_csr;
3422 END IF;
3423
3424 IF l_assettrx_csr%ISOPEN THEN
3425 CLOSE l_assettrx_csr;
3426 END IF;
3427
3428 IF l_itemlocation_csr%ISOPEN THEN
3429 CLOSE l_itemlocation_csr;
3430 END IF;
3431
3432 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
3433 IF l_periodofaddition_csr%ISOPEN THEN
3434 CLOSE l_periodofaddition_csr;
3435 END IF;
3436
3437 IF l_bookcontrols_csr%ISOPEN THEN
3438 CLOSE l_bookcontrols_csr;
3439 END IF;
3440
3441 -- SECHAWLA 21-nov-03 3262519 : close new cursors
3442 IF l_okclines_csr%ISOPEN THEN
3443 CLOSE l_okclines_csr;
3444 END IF;
3445
3446 IF l_dealtype_csr%ISOPEN THEN
3447 CLOSE l_dealtype_csr;
3448 END IF;
3449
3450 IF l_linesfullv_csr%ISOPEN THEN
3451 CLOSE l_linesfullv_csr;
3452 END IF;
3453 -- SECHAWLA 21-nov-03 3262519 : end
3454
3455 --SECHAWLA 10-FEB-06 5016156
3456 IF l_offlseassettrx_csr%ISOPEN THEN
3457 CLOSE l_offlseassettrx_csr;
3458 END IF;
3459
3460 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3461 (
3462 l_api_name,
3463 G_PKG_NAME,
3464 'OTHERS',
3465 x_msg_count,
3466 x_msg_data,
3467 '_PVT'
3468 );
3469 END dispose_asset;
3470
3471 -- Start of comments
3472 --
3473 -- Procedure Name : dispose_asset
3474 -- Description : This procedure is used to retire an asset in FA, from Remarketing. It checks if the asset is
3475 -- to be fully or partially retired , based upon the ordered_quantity and then calls the appropriate routine to
3476 -- retire the asset. It then stores the disposition transactions in OKL tables, calls accounting
3477 -- engine and then finally cancels all pending transactions in OKL tables for this asset
3478 -- Business Rules :
3479 -- Parameters : p_order_header_id - Order Header ID
3480 --
3481 -- Version : 1.0
3482 -- History : SECHAWLA 10-DEC-02 Bug # 2701440
3483 -- Modified CURSOR l_okxassetlines_csr to select all the tax books that an asset belongs to,
3484 -- in addition to the Fixed Asset Information.
3485 -- SECHAWLA 23-DEC-02 Bug # 2701440
3486 -- Modified logic to perform cost retirement instead of unit retirement for tax books
3487 -- SECHAWLA 03-JAN-03 Bug # 2683876
3488 -- Modified logic to send currency code while creating/updating amounts columns in txl assets
3489 -- SECHAWLA 13-JAN-03 Bug # 2701440 Modified logic to perform :
3490 -- 1) full tax book retirement when the corporate book gets fully retired. This is to
3491 -- take care of the scenario where tax book cost is more than the corporate book cost
3492 -- 2) full tax book retirement when the corp book is not fully retired but tax book does not
3493 -- have enough cost. This takes care of the scenario where tax book cost is less than the corp book cost
3494 -- SECHAWLA 05-FEB-03 Bug # 2781557
3495 -- Moved the logic to check if the asset was added in the current open period, from individual cost and unit
3496 -- retirement procedures to this procedure.
3497 -- SECHAWLA 11-MAR-03
3498 -- Removed the validation for NULL order quantity, as it is being NVLed to 1. Added a validation
3499 -- for unit_selling_price. If null, assigned 0 to sale_amount
3500 -- SECHAWLA 03-JUN-03 2999419: Use the retirement prorate convention set in Oracle
3501 -- Assets for a particular asset and book, instead of using the constant value "MID-MONTH"
3502 -- RMUNJULU 11-SEP-03 3061751 Added code for SERVICE_K_INTEGRATION
3503 -- SECHAWLA 21-NOV-03 3262519 Update the asset cost with residual value, for DF and Sales lease,
3504 -- before retiring the asset
3505 -- SECHAWLA 21-OCT-04 3924244 Modified procedure to work on order line instead of header
3506 -- girao 18-Jan-2005 4106216 NVL the residual value in l_linesfullv_csr
3507 -- SECHAWLA 10-FEB-06 5016156 in case of termination w/o purchase, asset cost should
3508 -- be updated with NIV (not RV), through Off-lease transactions
3509
3510 -- End of comments
3511 PROCEDURE dispose_asset ( p_api_version IN NUMBER,
3512 p_init_msg_list IN VARCHAR2,
3513 x_return_status OUT NOCOPY VARCHAR2,
3514 x_msg_count OUT NOCOPY NUMBER,
3515 x_msg_data OUT NOCOPY VARCHAR2,
3516 p_order_line_id IN NUMBER -- SECHAWLA 21-OCT-04 3924244
3517 ) IS
3518
3519 SUBTYPE thpv_rec_type IS OKL_TRX_ASSETS_PUB.thpv_rec_type;
3520 SUBTYPE tlpv_rec_type IS OKL_TXL_ASSETS_PUB.tlpv_rec_type;
3521
3522 -- This cursor is used to validate Header ID
3523 CURSOR l_orderheaders_csr(p_header_id NUMBER) IS
3524 SELECT order_number
3525 FROM oe_order_headers_all
3526 WHERE header_id = p_header_id;
3527
3528 -- This cursor is used to get the information about all the line items corresponding to an Order
3529 CURSOR l_orderlines_csr(p_line_id NUMBER) IS -- -- SECHAWLA 21-OCT-04 3924244
3530 SELECT header_id, inventory_item_id, nvl(ordered_quantity,1) ordered_quantity, ship_from_org_id, unit_selling_price
3531 FROM oe_order_lines_all
3532 WHERE line_id = p_line_id;
3533
3534 -- This curosr is used to get the financial asset id for an inventory item
3535 --Changed the cusrsor to use directly base tables instead uv for performance
3536 CURSOR l_assetreturn_csr(p_inventory_item_id NUMBER) IS
3537 SELECT kle.id kle_id,
3538 cim.number_of_items quantity,
3539 -- RRAVIKIR Legal Entity changes
3540 oar.legal_entity_id
3541 -- Legal Entity changes End
3542 FROM okc_k_lines_b kle,
3543 okc_k_headers_all_b okc,
3544 okl_asset_returns_all_b oar,
3545 mtl_system_items_b msi,
3546 okc_k_lines_b kle2,
3547 okc_line_styles_b lse,
3548 okc_k_items cim,
3549 okl_system_params osp
3550 WHERE okc.id = kle.chr_id
3551 AND oar.kle_id = kle.id
3552 AND oar.imr_id = msi.inventory_item_id
3553 AND msi.organization_id = osp.remk_organization_id
3554 AND kle.id = kle2.cle_id
3555 AND kle2.lse_id = lse.id
3556 AND lse.lty_code = 'ITEM'
3557 AND kle2.id = cim.cle_id
3558 AND oar.imr_id = p_inventory_item_id;
3559
3560
3561
3562 -- SECHAWLA Bug # 2701440 :
3563 -- Modified this cursor to select all the tax books that an asset belongs to, in addition to the Fixed Asset Information
3564
3565 --SECHAWLA 23_DEC-02 Bug # 2701440
3566 --Added Order By clause to selct CORPORATE Book first
3567
3568 --SECHAWLA 13-JAN-03 Bug # 2701440
3569 --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
3570
3571 --SECHAWLA 06-JUN-03 Bug # 2999419
3572 --Added prorate_convention_code to the Select clause
3573 CURSOR l_okxassetlines_csr(p_kle_id IN NUMBER) IS
3574 SELECT o.asset_id, o.asset_number, o.corporate_book, a.cost, o.depreciation_category, a.original_cost, o.current_units,
3575 o.dnz_chr_id ,a.book_type_code, b.book_class, a.prorate_convention_code
3576 FROM okx_asset_lines_v o, fa_books a, fa_book_controls b
3577 WHERE o.parent_line_id = p_kle_id
3578 AND o.asset_id = a.asset_id
3579 AND a.book_type_code = b.book_type_code
3580 AND a.date_ineffective IS NULL
3581 AND a.transaction_header_id_out IS NULL
3582 ORDER BY book_class;
3583
3584 --SECHAWLA 23_DEC-02 Bug # 2701440 : Added this cursor to get the cost retired, populated after the retirement of
3585 -- asset from the corporate book. We need this cost to perform cost retirement of the same asset in the TAX book
3586
3587 /* SECHAWLA 21-NOV-03 3262519 : This curosr is not required. Cost to be retired from the tax book should be
3588 -- calculated using tax book cost and not the corporate book cost, as teh 2 costs can be different
3589
3590 --SECHAWLA 13-JAN-03 Bug # 2701440 : Added Order By Clause to select the latest retirement record first
3591 CURSOR l_faretirement_csr(p_asset_id IN NUMBER, p_book_type_code IN VARCHAR2) IS
3592 SELECT cost_retired
3593 FROM fa_retirements
3594 WHERE asset_id = p_asset_id
3595 AND book_type_code = p_book_type_code
3596 ORDER BY last_update_date DESC;
3597 */
3598
3599 -- This cursor is used to get all the active distributions for an asset
3600 CURSOR l_disthist_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2) IS
3601 SELECT distribution_id, units_assigned, retirement_id, transaction_units
3602 FROM fa_distribution_history
3603 WHERE asset_id = p_asset_id
3604 AND book_type_code = p_book_type_code
3605 AND date_ineffective IS NULL
3606 AND transaction_header_id_out IS NULL
3607 --AND retirement_id IS NULL
3608 ORDER BY last_update_date;
3609
3610 -- This cursor is used to get all the pending transactions for an asset. These transactions are to be cancelled
3611 -- once the asset is retired.
3612 CURSOR l_assettrx_csr(p_financial_asset_id NUMBER) IS
3613 SELECT h.id
3614 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l
3615 WHERE h.id = l.tas_id
3616 AND h.tsu_code IN ('ENTERED', 'ERROR')
3617 AND l.kle_id = p_financial_asset_id;
3618
3619 -- This curosr is used to get all the instances for a Financial asset
3620 --Query changed to use base tables instead uv for performance
3621 CURSOR l_itemlocation_csr(p_financial_asset_id NUMBER) IS
3622 SELECT cii.instance_id instance_id, cii.active_end_date instance_end_date
3623 FROM okc_k_headers_b okhv,
3624 okc_k_lines_b kle_fa,
3625 okc_k_lines_tl klet_fa,
3626 okc_line_styles_b lse_fa,
3627 okc_k_lines_b kle_il,
3628 okc_line_styles_b lse_il,
3629 okc_k_lines_b kle_ib,
3630 okc_line_styles_b lse_ib,
3631 okc_k_items ite,
3632 csi_item_instances cii
3633 WHERE kle_fa.id = klet_fa.id
3634 AND klet_fa.language = USERENV('LANG')
3635 AND kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id
3636 AND lse_fa.lty_code = 'FREE_FORM1'
3637 AND kle_il.cle_id = kle_fa.id
3638 AND lse_il.id = kle_il.lse_id
3639 AND lse_il.lty_code = 'FREE_FORM2'
3640 AND kle_ib.cle_id = kle_il.id
3641 AND lse_ib.id = kle_ib.lse_id
3642 AND lse_ib.lty_code = 'INST_ITEM'
3643 AND ite.cle_id = kle_ib.id
3644 AND ite.jtot_object1_code = 'OKX_IB_ITEM'
3645 AND cii.instance_id = ite.object1_id1
3646 AND kle_fa.id = p_financial_asset_id;
3647
3648
3649 --SECHAWLA 05-FEB-03 Bug # 2781557 : new cursor
3650 -- This cursor is used to find out the period_of_addtion for the asset that is to be retired
3651 CURSOR l_periodofaddition_csr(p_asset_id NUMBER, p_book_type_code VARCHAR2, p_period_open_date DATE) IS
3652 SELECT count(*)
3653 FROM fa_transaction_headers th
3654 WHERE th.asset_id = p_asset_id
3655 AND th.book_type_code = p_book_type_code
3656 AND th.transaction_type_code = 'ADDITION'
3657 AND th.date_effective > p_period_open_date;
3658
3659 --SECHAWLA 05-FEB-03 Bug # 2781557 : new cursor
3660 -- This cursor is used temporarily to get the fiscal year name till FA API is fixed
3661 CURSOR l_bookcontrols_csr(p_book_type_code VARCHAR2) IS
3662 SELECT fiscal_year_name
3663 FROM fa_book_controls
3664 WHERE book_type_code = p_book_type_code;
3665
3666 --SECHAWLA 21-NOV-2003 3262519 : Added the following cursor
3667
3668 -- get the deal type from the contract
3669 CURSOR l_dealtype_csr(p_financial_asset_id IN NUMBER) IS
3670 SELECT lkhr.id, lkhr.deal_type, khr.contract_number
3671 FROM okl_k_headers lkhr, okc_k_lines_b cle, okc_k_headers_b khr
3672 WHERE khr.id = cle.chr_id
3673 AND lkhr.id = khr.id
3674 AND cle.id = p_financial_asset_id;
3675
3676 -- get the residual value for the fin asset
3677 CURSOR l_linesfullv_csr(p_fin_asset_id IN NUMBER) IS
3678 SELECT name, NVL(residual_value,0) --girao bug 4106216 NVL the residual value
3679 FROM okl_k_lines_full_v
3680 WHERE id = p_fin_asset_id;
3681
3682 --SECHAWLA 10-FEB-06 5016156
3683 --Bug# 9838432: Added parameter cp_kle_id
3684 CURSOR l_offlseassettrx_csr(cp_trx_date IN DATE, cp_asset_number IN VARCHAR2, cp_kle_id IN NUMBER) IS
3685 SELECT h.tsu_code, h.tas_type, h.date_trans_occurred, l.dnz_asset_id,
3686 l.asset_number, l.kle_id ,l.DNZ_KHR_ID
3687 FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l
3688 WHERE h.id = l.tas_id
3689 AND h.date_trans_occurred <= cp_trx_date
3690 AND h.tas_type in ('AMT','AUD','AUS')
3691 AND l.asset_number = cp_asset_number
3692 AND l.kle_id = cp_kle_id;
3693
3694 l_trx_status VARCHAR2(30);
3695 --SECHAWLA 10-FEB-06 5016156 : end
3696
3697
3698 l_deal_type VARCHAR2(30);
3699 l_chr_id NUMBER;
3700 l_contract_number VARCHAR2(120);
3701 l_rulv_rec okl_rule_pub.rulv_rec_type;
3702 l_tax_owner VARCHAR2(10);
3703 l_delta_cost NUMBER;
3704 l_residual_value NUMBER;
3705 l_name VARCHAR2(150);
3706 l_cost NUMBER;
3707 --SECHAWLA 21-NOV-2003 3262519 : end new declarations
3708
3709
3710 l_dist_quantity NUMBER;
3711 l_dist_tbl asset_dist_tbl;
3712 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3713 l_order_number NUMBER;
3714 i NUMBER;
3715 l_kle_id NUMBER;
3716 l_quantity NUMBER;
3717 l_sale_amount NUMBER;
3718 l_trx_type VARCHAR2(30) := 'Asset Disposition';
3719 l_trx_name VARCHAR2(30) := 'ASSET_DISPOSITION';
3720 l_api_name CONSTANT VARCHAR2(30) := 'dispose_asset';
3721 l_try_id OKL_TRX_TYPES_V.id%TYPE;
3722 l_sys_date DATE;
3723
3724 lp_thpv_rec thpv_rec_type;
3725 lp_thpv_empty_rec thpv_rec_type;
3726 lp_tlpv_empty_rec tlpv_rec_type;
3727 lx_thpv_rec thpv_rec_type;
3728 lp_tlpv_rec tlpv_rec_type;
3729 lx_tlpv_rec tlpv_rec_type;
3730 l_api_version CONSTANT NUMBER := 1;
3731 instance_counter NUMBER;
3732 l_already_retired VARCHAR2(1):= 'N';
3733 l_retired_quantity NUMBER;
3734 l_non_retired_quantity NUMBER;
3735 l_remaining_units NUMBER;
3736 l_retired_dist_units NUMBER;
3737 l_units_to_be_retired NUMBER;
3738 lx_total_amount NUMBER;
3739 l_units_retired NUMBER;
3740
3741 --SECHAWLA 23_DEC-02 Bug # 2701440: new declarations
3742 l_cost_retired NUMBER;
3743
3744 --SECHAWLA 03-JAN-03 Bug # 2683876 : new declaration
3745 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
3746
3747 --SECHAWLA 05-FEB-03 Bug # 2781557 : new declarations
3748 l_fiscal_year_name VARCHAR2(30);
3749 l_period_rec FA_API_TYPES.period_rec_type;
3750 l_count NUMBER;
3751 l_period_of_addition VARCHAR2(1);
3752
3753 -- RMUNJULU 3061751
3754 l_service_int_needed VARCHAR2(1) := 'N';
3755
3756 --SECHAWLA 21-OCT-04 3924244
3757 l_header_id NUMBER;
3758 l_inventory_item_id NUMBER;
3759 l_ordered_quantity NUMBER;
3760 l_ship_from_org_id NUMBER;
3761 l_unit_selling_price NUMBER;
3762
3763 --SECHAWLA 15-DEC-04 4028371 New Declarations
3764 l_fa_trx_date DATE;
3765
3766 -- Legal Entity changes
3767 l_legal_entity_id NUMBER;
3768 -- Legal Entity changes End
3769 --akrangan sla populate sources cr start
3770 l_fxhv_rec okl_fxh_pvt.fxhv_rec_type;
3771 l_fxlv_rec okl_fxl_pvt.fxlv_rec_type;
3772 --akrangan sla populate sources cr end
3773
3774 BEGIN
3775
3776 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3777 G_PKG_NAME,
3778 p_init_msg_list,
3779 l_api_version,
3780 p_api_version,
3781 '_PVT',
3782 x_return_status);
3783
3784 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3785 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3786 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3787 RAISE OKC_API.G_EXCEPTION_ERROR;
3788 END IF;
3789
3790 -- Get the sysdate
3791 SELECT SYSDATE INTO l_sys_date FROM DUAL;
3792
3793 -- SECHAWLA 21-OCT-04 3924244
3794 IF p_order_line_Id IS NULL OR p_order_line_Id = OKL_API.G_MISS_NUM THEN
3795 x_return_status := OKL_API.G_RET_STS_ERROR;
3796 -- Order Line ID is required
3797 OKL_API.set_message( p_app_name => 'OKC',
3798 p_msg_name => G_REQUIRED_VALUE,
3799 p_token1 => G_COL_NAME_TOKEN,
3800 p_token1_value => 'ORDER_LINE_ID');
3801 RAISE okc_api.G_EXCEPTION_ERROR;
3802 END IF;
3803
3804 /* -- SECHAWLA 21-OCT-04 3924244
3805 OPEN l_orderheaders_csr(p_order_header_Id);
3806 FETCH l_orderheaders_csr INTO l_order_number;
3807 IF l_orderheaders_csr%NOTFOUND THEN
3808 x_return_status := OKL_API.G_RET_STS_ERROR;
3809 -- Order Header ID is invalid
3810 OKL_API.set_message( p_app_name => 'OKC',
3811 p_msg_name => G_INVALID_VALUE,
3812 p_token1 => G_COL_NAME_TOKEN,
3813 p_token1_value => 'ORDER_HEADER_ID');
3814 RAISE okc_api.G_EXCEPTION_ERROR;
3815 END IF;
3816 CLOSE l_orderheaders_csr;
3817 */
3818
3819 -- SECHAWLA 21-OCT-04 3924244 : begin
3820 OPEN l_orderlines_csr(p_order_line_Id);
3821 FETCH l_orderlines_csr INTO l_header_id, l_inventory_item_id, l_ordered_quantity,
3822 l_ship_from_org_id, l_unit_selling_price;
3823 IF l_orderlines_csr%NOTFOUND THEN
3824 x_return_status := OKL_API.G_RET_STS_ERROR;
3825 -- Order Line ID is invalid
3826 OKL_API.set_message( p_app_name => 'OKC',
3827 p_msg_name => G_INVALID_VALUE,
3828 p_token1 => G_COL_NAME_TOKEN,
3829 p_token1_value => 'ORDER_LINE_ID');
3830 RAISE okc_api.G_EXCEPTION_ERROR;
3831 END IF;
3832 CLOSE l_orderlines_csr;
3833
3834 OPEN l_orderheaders_csr(l_header_id);
3835 FETCH l_orderheaders_csr INTO l_order_number;
3836 CLOSE l_orderheaders_csr;
3837 -- SECHAWLA 21-OCT-04 3924244 : end
3838
3839
3840 -- SECHAWLA 21-OCT-04 3924244 : Commented out the loop and changed the cursor attribute references to variable references
3841 -- loop thru all the line items for a given order, validate the data and then reduce the quantity of each line item
3842 --FOR l_orderlines_rec IN l_orderlines_csr(p_order_header_id) LOOP
3843
3844
3845
3846 IF l_ordered_quantity < 0 THEN -- SECHAWLA 21-OCT-04 3924244
3847 x_return_status := OKL_API.G_RET_STS_ERROR;
3848 -- ordered quantity is invalid
3849 OKC_API.set_message( p_app_name => 'OKC',
3850 p_msg_name => G_INVALID_VALUE,
3851 p_token1 => G_COL_NAME_TOKEN,
3852 p_token1_value => 'ORDERED_QUANTITY');
3853
3854
3855 RAISE OKC_API.G_EXCEPTION_ERROR;
3856 END IF;
3857
3858 IF trunc(l_ordered_quantity) <> l_ordered_quantity THEN -- SECHAWLA 21-OCT-04 3924244
3859 x_return_status := OKL_API.G_RET_STS_ERROR;
3860 -- Ordered quantity should be a whole number.
3861 OKC_API.set_message( p_app_name => 'OKL',
3862 p_msg_name => 'OKL_AM_WHOLE_QTY_ERR');
3863 RAISE OKC_API.G_EXCEPTION_ERROR;
3864 END IF;
3865
3866
3867
3868 OPEN l_assetreturn_csr(l_inventory_item_id); -- SECHAWLA 21-OCT-04 3924244
3869 FETCH l_assetreturn_csr INTO l_kle_id, l_quantity, l_legal_entity_id; -- RRAVIKIR legal_entity_id added to the Fetch cursor
3870
3871
3872 IF l_assetreturn_csr%NOTFOUND THEN
3873 x_return_status := OKL_API.G_RET_STS_ERROR;
3874 -- Inventory Item for the order ORDER_NUMBER is not defined in Asset Returns.
3875 OKL_API.set_message( p_app_name => 'OKL',
3876 p_msg_name => 'OKL_AM_NO_ASSET_RETURN',
3877 p_token1 => 'ORDER_NUMBER',
3878 p_token1_value => l_order_number);
3879 RAISE okc_api.G_EXCEPTION_ERROR;
3880 END IF;
3881
3882 IF l_quantity IS NULL OR l_quantity = OKL_API.G_MISS_NUM THEN
3883 x_return_status := OKL_API.G_RET_STS_ERROR;
3884 -- Quantity is required
3885 OKL_API.set_message( p_app_name => 'OKC',
3886 p_msg_name => G_REQUIRED_VALUE,
3887 p_token1 => G_COL_NAME_TOKEN,
3888 p_token1_value => 'ASSET_RETURN_QUANTITY');
3889 RAISE okc_api.G_EXCEPTION_ERROR;
3890 END IF;
3891
3892 -- RRAVIKIR Legal Entity Changes
3893 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
3894 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
3895 p_msg_name => g_required_value,
3896 p_token1 => g_col_name_token,
3897 p_token1_value => 'legal_entity_id');
3898 RAISE OKC_API.G_EXCEPTION_ERROR;
3899 END IF;
3900 -- Legal Entity Changes End
3901
3902 IF l_quantity < 0 THEN
3903 x_return_status := OKL_API.G_RET_STS_ERROR;
3904 -- Quantity is invalid
3905 OKL_API.set_message( p_app_name => 'OKC',
3906 p_msg_name => G_INVALID_VALUE,
3907 p_token1 => G_COL_NAME_TOKEN,
3908 p_token1_value => 'ASSET_RETURN_QUANTITY');
3909 RAISE okc_api.G_EXCEPTION_ERROR;
3910 END IF;
3911
3912 CLOSE l_assetreturn_csr;
3913
3914
3915 l_already_retired := 'N';
3916
3917 --SECHAWLA 21-NOV-2003 3262519 : Added the following code to get the deal type and tax owner
3918
3919 -- get the deal type from the contract
3920 OPEN l_dealtype_csr(l_kle_id);
3921 FETCH l_dealtype_csr INTO l_chr_id, l_deal_type, l_contract_number;
3922 IF l_dealtype_csr%NOTFOUND THEN
3923 x_return_status := OKL_API.G_RET_STS_ERROR;
3924 -- chr id is invalid
3925 OKC_API.set_message( p_app_name => 'OKC',
3926 p_msg_name => G_INVALID_VALUE,
3927 p_token1 => G_COL_NAME_TOKEN,
3928 p_token1_value => 'CHR_ID');
3929
3930 RAISE OKC_API.G_EXCEPTION_ERROR;
3931 END IF;
3932 CLOSE l_dealtype_csr;
3933
3934 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
3935 -- get the tax owner (LESSOR/LESSEE) for the contract
3936
3937 okl_am_util_pvt.get_rule_record(p_rgd_code => 'LATOWN'
3938 ,p_rdf_code =>'LATOWN'
3939 ,p_chr_id => l_chr_id
3940 ,p_cle_id => NULL
3941 ,x_rulv_rec => l_rulv_rec
3942 ,x_return_status => x_return_status
3943 ,x_msg_count => x_msg_count
3944 ,x_msg_data => x_msg_data);
3945
3946 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3947 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3948 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3949 RAISE OKC_API.G_EXCEPTION_ERROR;
3950 END IF;
3951
3952 -- check if tax owner is defined
3953 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
3954
3955 x_return_status := OKL_API.G_RET_STS_ERROR;
3956 -- tax owner is not defined for contract CONTRACT_NUMBER.
3957 OKL_API.set_message( p_app_name => 'OKL',
3958 p_msg_name => 'OKL_AM_NO_TAX_OWNER',
3959 p_token1 => 'CONTRACT_NUMBER',
3960 p_token1_value => l_contract_number);
3961 RAISE OKC_API.G_EXCEPTION_ERROR;
3962
3963 ELSE
3964 -- l_rulv_rec.RULE_INFORMATION1 will contain the value 'LESSEE' or 'LESSOR'
3965 l_tax_owner := l_rulv_rec.RULE_INFORMATION1;
3966 END IF;
3967
3968 -- get the residual value of the fin asset
3969 OPEN l_linesfullv_csr(l_kle_id);
3970 FETCH l_linesfullv_csr INTO l_name, l_residual_value;
3971 CLOSE l_linesfullv_csr;
3972
3973 IF l_residual_value IS NULL THEN
3974 x_return_status := OKL_API.G_RET_STS_ERROR;
3975 -- Residual value is not defined for the asset
3976 OKC_API.set_message( p_app_name => 'OKL',
3977 p_msg_name => 'OKL_AM_NO_RESIDUAL_VALUE',
3978 p_token1 => 'ASSET_NUMBER',
3979 p_token1_value => l_name);
3980
3981
3982 RAISE OKC_API.G_EXCEPTION_ERROR;
3983 END IF;
3984
3985 IF l_residual_value < 0 THEN
3986 x_return_status := OKL_API.G_RET_STS_ERROR;
3987 -- Residual value is negative for the asset
3988 OKC_API.set_message( p_app_name => 'OKL',
3989 p_msg_name => 'OKL_AM_INVALID_RESIDUAL',
3990 p_token1 => 'ASSET_NUMBER',
3991 p_token1_value => l_name);
3992
3993
3994 RAISE OKC_API.G_EXCEPTION_ERROR;
3995 END IF;
3996 END IF;
3997
3998
3999
4000 --SECHAWLA 21-NOV-2003 3262519 : end new code
4001
4002
4003 -- SECHAWLA Bug # 2701440 : Changed OPEN, FETCH to a curosr FOR LOOP, as this cursor now has multiple rows
4004 -- for a given asset id : one row for the corporate book and one or more rows for the tax books
4005 FOR l_okxassetlines_rec IN l_okxassetlines_csr(l_kle_id) LOOP
4006
4007 --SECHAWLA 21-NOV-2003 3262519 : Calculate delta cost
4008 IF l_deal_type IN ('LEASEDF','LEASEST') THEN
4009
4010
4011 --SECHAWLA 10-FEB-06 5016156 Check if any off-lease transactions exist for the asset
4012 -- This will tell if it is termination with purchase or without purchase
4013 l_trx_status := NULL;
4014 --Bug# 9838432: Added parameter l_kle_id
4015 FOR l_offlseassettrx_rec IN l_offlseassettrx_csr(l_sys_date, l_name, l_kle_id) LOOP
4016 l_trx_status := l_offlseassettrx_rec.tsu_code;
4017 IF l_trx_status IN ('ENTERED','ERROR','CANCELED') THEN
4018 EXIT;
4019 END IF;
4020 END LOOP;
4021
4022
4023
4024 IF l_trx_status IS NULL THEN -- This means off-lease trx don't exist. It is termination with purchase
4025 --SECHAWLA 10-FEB-06 5016156 : end
4026 l_delta_cost := l_residual_value - l_okxassetlines_rec.cost;
4027
4028 --SECHAWLA 10-FEB-06 5016156 begin
4029 ELSIF l_trx_status IN ('ENTERED','ERROR') THEN -- if any trx has this status
4030 x_return_status := OKL_API.G_RET_STS_ERROR;
4031 OKL_API.set_message( p_app_name => 'OKL',
4032 p_msg_name => 'OKL_AM_PENDING_OFFLEASE',
4033 p_token1 => 'ASSET_NUMBER',
4034 p_token1_value => l_name);
4035 RAISE OKC_API.G_EXCEPTION_ERROR;
4036
4037
4038 ELSIF l_trx_status IN ('PROCESSED','CANCELED') THEN
4039 l_delta_cost := 0; -- no cost update required, as cost has already been updated thru off lease trx
4040 END IF; -- or off-lease trx has been canceled
4041 --SECHAWLA 10-FEB-06 5016156 : end
4042
4043 END IF;
4044
4045 --SECHAWLA 05-FEB-03 Bug # 2781557 : Moved the following code from do_full_units_retirement, as the check
4046 -- whether the asset is added in the current open period, needs to be done at this stage.
4047
4048 -- This piece of code is included temporarily as a work around , since FA API has errors
4049 -- Set the Fiscal Year name in teh cache,if not already set
4050 -- IF fa_cache_pkg.fazcbc_record.fiscal_year_name IS NULL THEN
4051 OPEN l_bookcontrols_csr(l_okxassetlines_rec.book_type_code);
4052 FETCH l_bookcontrols_csr INTO l_fiscal_year_name;
4053 IF l_bookcontrols_csr%NOTFOUND OR l_fiscal_year_name IS NULL THEN
4054 x_return_status := OKL_API.G_RET_STS_ERROR;
4055 -- Fiscal Year Name is required
4056 OKC_API.set_message( p_app_name => 'OKC',
4057 p_msg_name => G_REQUIRED_VALUE,
4058 p_token1 => G_COL_NAME_TOKEN,
4059 p_token1_value => 'Fiscal Year Name');
4060
4061
4062 RAISE OKC_API.G_EXCEPTION_ERROR;
4063 END IF;
4064 CLOSE l_bookcontrols_csr;
4065 fa_cache_pkg.fazcbc_record.fiscal_year_name := l_fiscal_year_name;
4066 -- END IF;
4067
4068
4069 IF NOT FA_UTIL_PVT.get_period_rec
4070 (
4071 p_book => l_okxassetlines_rec.book_type_code,
4072 p_effective_date => NULL,
4073 x_period_rec => l_period_rec
4074 ) THEN
4075
4076 x_return_status := OKC_API.G_RET_STS_ERROR;
4077 --Error getting current open period for the book BOOK_TYPE_CODE.
4078 OKL_API.set_message(
4079 p_app_name => 'OKL',
4080 p_msg_name => 'OKL_AM_OPEN_PERIOD_ERR',
4081 p_token1 => 'BOOK_CLASS',
4082 p_token1_value => lower(l_okxassetlines_rec.book_class),
4083 p_token2 => 'BOOK_TYPE_CODE',
4084 p_token2_value => l_okxassetlines_rec.book_type_code
4085 );
4086
4087
4088 RAISE OKC_API.G_EXCEPTION_ERROR;
4089 END IF;
4090
4091 --- check period of addition. If 'N' then run retirements
4092 OPEN l_periodofaddition_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code,l_period_rec.period_open_date);
4093 FETCH l_periodofaddition_csr INTO l_count;
4094 CLOSE l_periodofaddition_csr;
4095
4096 IF (l_count <> 0) THEN
4097 l_period_of_addition := 'Y';
4098 ELSE
4099 l_period_of_addition := 'N';
4100 END IF;
4101
4102 /* bug 13581587: this restriction is removed by FA in R12. NOw, addition and
4103 retirement can be done in the period of addition.
4104
4105 IF l_period_of_addition = 'Y' THEN
4106 -- Can nor retire asset ASSET_NUMBER as the asset was added to the book
4107 -- in the current open period. Please retire the asset manually.
4108 x_return_status := OKC_API.G_RET_STS_ERROR;
4109
4110 OKL_API.set_message( p_app_name => 'OKL',
4111 p_msg_name => 'OKL_AM_RETIRE_MANUALLY',
4112 p_token1 => 'ASSET_NUMBER',
4113 p_token1_value => l_okxassetlines_rec.asset_number,
4114 p_token2 => 'BOOK_CLASS',
4115 p_token2_value => lower(l_okxassetlines_rec.book_class),
4116 p_token3 => 'BOOK_TYPE_CODE',
4117 p_token3_value => l_okxassetlines_rec.book_type_code);
4118
4119 RAISE OKC_API.G_EXCEPTION_ERROR;
4120
4121 END IF;
4122 */ -- bug 13581587.
4123
4124 ---------- SECHAWLA 05-FEB-03 Bug # 2781557 : end moved code ----------------
4125
4126 /* -- ansethur for Bug:5664106 Start
4127 -- SECHAWLA 03-JUN-03 Bug 2999419 : Added the following validation
4128 IF l_okxassetlines_rec.prorate_convention_code IS NULL THEN
4129 x_return_status := OKL_API.G_RET_STS_ERROR;
4130 -- Unable to find retirement prorate convention for asset ASSET_NUMBER and book BOOK_TYPE_CODE.
4131 OKC_API.set_message( p_app_name => 'OKL',
4132 p_msg_name => 'OKL_AM_NO_PRORATE_CONVENTION',
4133 p_token1 => 'ASSET_NUMBER',
4134 p_token1_value => l_okxassetlines_rec.asset_number,
4135 p_token2 => 'BOOK_CLASS',
4136 p_token2_value => l_okxassetlines_rec.book_class,
4137 p_token3 => 'BOOK_TYPE_CODE',
4138 p_token3_value => l_okxassetlines_rec.book_type_code);
4139 RAISE OKC_API.G_EXCEPTION_ERROR;
4140 END IF;
4141 -- SECHAWLA 03-JUN-03 Bug 2999419: end new code
4142 */ -- ansethur for Bug:5664106 End
4143
4144
4145
4146 --SECHAWLA 11-MAR-03 : Added the following validation
4147 IF l_unit_selling_price IS NULL THEN -- SECHAWLA 21-OCT-04 3924244
4148 l_sale_amount := 0;
4149 ELSE
4150 l_sale_amount := l_ordered_quantity * l_unit_selling_price; -- SECHAWLA 21-OCT-04 3924244
4151 END IF;
4152
4153 IF l_ordered_quantity = l_quantity THEN -- SECHAWLA 21-OCT-04 3924244
4154 -- user sent request for full retirement, since all the units were sold
4155
4156
4157 -- check if asset has already been fully/partially retired .
4158 l_retired_quantity := 0;
4159 l_non_retired_quantity := 0;
4160 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4161 IF l_disthist_rec.retirement_id IS NOT NULL THEN
4162 l_retired_quantity := l_retired_quantity + abs(l_disthist_rec.transaction_units);
4163 ELSE
4164 l_non_retired_quantity := l_non_retired_quantity + l_disthist_rec.units_assigned;
4165 END IF;
4166 END LOOP;
4167
4168 IF l_retired_quantity = 0 AND l_non_retired_quantity > 0 THEN
4169 -- user requested for full retirement and none of the units have been retired so far
4170 -- perform full retirement
4171 IF l_non_retired_quantity = l_quantity THEN --distribution qty = orginal asset return qty
4172
4173 -- we are passing the total number of units and not the cost, for full retirements, because for
4174 -- Direct Finance Lease, okx_asset_lines_v, gives OEC as the cost. FA Retirements compares this cost with
4175 --cost in fa_books. These 2 costs can be different, which will give error. So we are using units instead
4176 -- of cost to avoid that validation check.
4177
4178 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4179 do_full_units_retirement(
4180 p_api_version => p_api_version,
4181 p_init_msg_list => OKC_API.G_FALSE,
4182 p_tax_owner => l_tax_owner,
4183 p_delta_cost => l_delta_cost,
4184 p_asset_id => l_okxassetlines_rec.asset_id,
4185 p_asset_number => l_okxassetlines_rec.asset_number,
4186 p_proceeds_of_sale => l_sale_amount,
4187 --p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4188 p_book_type_code => l_okxassetlines_rec.book_type_code,
4189 --p_cost => l_cost,
4190 p_units => l_quantity,
4191 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4192 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4193 x_msg_count => x_msg_count,
4194 x_msg_data => x_msg_data,
4195 x_return_status => x_return_status);
4196
4197 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4198 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4199 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4200 RAISE OKC_API.G_EXCEPTION_ERROR;
4201 END IF;
4202
4203 l_units_retired := l_quantity;
4204 ELSE -- distribution qty is either less or more than the original asset return qty
4205 -- and hence we need to consider this as partial retirement, even though the sold
4206 -- quantity = original asset return quantity
4207
4208 IF l_non_retired_quantity > l_quantity THEN
4209 l_units_to_be_retired := l_quantity;
4210 ELSE
4211 l_units_to_be_retired := l_non_retired_quantity;
4212 END IF;
4213
4214
4215 -- l_dist_quantity := l_quantity;
4216 l_dist_quantity := l_units_to_be_retired;
4217 i := 0;
4218
4219
4220 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
4221 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
4222 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4223 -- than requested units then retire that distribution fully and move to next distribution for remaining
4224 -- units, until all the requested units have been retired.
4225
4226
4227 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4228 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4229 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4230 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4231 l_dist_quantity := 0;
4232 EXIT;
4233 ELSE
4234 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4235 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4236 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4237 END IF;
4238 i := i + 1;
4239 END LOOP;
4240
4241
4242 -- If there are no more distributions left and there are still some more units to be retired,
4243 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
4244 -- assigned to all the distributions.
4245
4246
4247 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
4248 -- x_return_status := OKL_API.G_RET_STS_ERROR;
4249 -- Sold quantity is more than the total quantity assigned to asset distributions.
4250 OKC_API.set_message( p_app_name => 'OKL',
4251 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
4252 --RAISE okc_api.G_EXCEPTION_ERROR;
4253 END IF;
4254
4255 -- SECHAWLA 21-NOV-03 3262519 : Added p_tax_owner and delta cost parameter to the following procedure call
4256 do_partial_units_retirement(
4257 p_api_version => p_api_version,
4258 p_init_msg_list => OKC_API.G_FALSE,
4259 p_tax_owner => l_tax_owner,
4260 p_delta_cost => l_delta_cost,
4261 p_asset_id => l_okxassetlines_rec.asset_id,
4262 p_asset_number => l_okxassetlines_rec.asset_number,
4263 p_proceeds_of_sale => l_sale_amount,
4264 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4265 p_book_type_code => l_okxassetlines_rec.book_type_code,
4266 p_total_quantity => l_units_to_be_retired, -- units to be retired
4267 p_dist_tbl => l_dist_tbl,
4268 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4269 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4270 x_msg_count => x_msg_count,
4271 x_msg_data => x_msg_data,
4272 x_return_status => x_return_status);
4273
4274
4275 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4276 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4277 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4278 RAISE OKC_API.G_EXCEPTION_ERROR;
4279 END IF;
4280
4281 l_units_retired := l_units_to_be_retired;
4282
4283 END IF; --l_non_retired_quantity = l_quantity
4284 ELSIF l_retired_quantity = l_quantity AND l_non_retired_quantity = 0 THEN -- retired qty = original asset return qty
4285 -- Asset is already fully retired.
4286 OKC_API.set_message( p_app_name => 'OKL',
4287 p_msg_name => 'OKL_AM_ALREADY_RETIRED',
4288 p_token1 => 'ASSET_NUMBER',
4289 p_token1_value => l_okxassetlines_rec.asset_number);
4290 l_already_retired := 'Y';
4291 ELSIF l_retired_quantity >= l_quantity AND l_non_retired_quantity > 0 THEN -- There are still some more units that can be retierd
4292 -- non-retired qty can be either less or more than l_quantity
4293
4294 IF l_non_retired_quantity >= l_quantity THEN
4295 l_units_to_be_retired := l_quantity;
4296 ELSE
4297 l_units_to_be_retired := l_non_retired_quantity;
4298 END IF;
4299
4300 -- l_dist_quantity := l_quantity;
4301 l_dist_quantity := l_units_to_be_retired;
4302 i := 0;
4303
4304 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
4305 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
4306 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4307 -- than requested units then retire that distribution fully and move to next distribution for remaining
4308 -- units, until all the requested units have been retired.
4309
4310
4311 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4312 IF l_disthist_rec.retirement_id IS NULL THEN
4313 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4314 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4315 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4316 l_dist_quantity := 0;
4317 EXIT;
4318 ELSE
4319 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4320 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4321 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4322 END IF;
4323 i := i + 1;
4324 END IF;
4325 END LOOP;
4326
4327 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4328 do_partial_units_retirement(
4329 p_api_version => p_api_version,
4330 p_init_msg_list => OKC_API.G_FALSE,
4331 p_tax_owner => l_tax_owner,
4332 p_delta_cost => l_delta_cost,
4333 p_asset_id => l_okxassetlines_rec.asset_id,
4334 p_asset_number => l_okxassetlines_rec.asset_number,
4335 p_proceeds_of_sale => l_sale_amount,
4336 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4337 p_book_type_code => l_okxassetlines_rec.book_type_code,
4338 p_total_quantity => l_units_to_be_retired, -- units to be retired
4339 p_dist_tbl => l_dist_tbl,
4340 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4341 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4342 x_msg_count => x_msg_count,
4343 x_msg_data => x_msg_data,
4344 x_return_status => x_return_status);
4345
4346
4347 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4348 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4349 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4350 RAISE OKC_API.G_EXCEPTION_ERROR;
4351 END IF;
4352
4353 l_units_retired := l_units_to_be_retired;
4354
4355 ELSIF l_retired_quantity > l_quantity AND l_non_retired_quantity = 0 THEN
4356 -- x_return_status := OKL_API.G_RET_STS_ERROR;
4357 -- Asset ASSET_NUMBER is already retired with invalid retired quantity which is more than the original quantity.
4358 OKC_API.set_message( p_app_name => 'OKL',
4359 p_msg_name => 'OKL_AM_INVALID_RETIRED_QTY',
4360 p_token1 => 'ASSET_NUMBER',
4361 p_token1_value => l_okxassetlines_rec.asset_number);
4362 -- RAISE okc_api.G_EXCEPTION_ERROR;
4363 ELSIF l_retired_quantity < l_quantity AND l_non_retired_quantity > 0 THEN
4364 -- user requested for full retirement, but the asset is already retired partially
4365
4366
4367
4368 IF l_non_retired_quantity >= l_quantity THEN
4369 l_units_to_be_retired := l_quantity;
4370 ELSE
4371 l_units_to_be_retired := l_non_retired_quantity;
4372 END IF;
4373
4374 -- l_dist_quantity := l_quantity;
4375 l_dist_quantity := l_units_to_be_retired;
4376 i := 0;
4377
4378 -- l_disthist_csr picks up all active distributions, which could possibly be retired.
4379
4380
4381 -- loop thru all the distributions of an asset, starting from the first non-retired distribution, compare the requested
4382 -- quantity with the distribution units . If distribution has more units than the requested quantity, then
4383 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4384 -- than requested units then retire that distribution fully and move to next distribution for remaining
4385 -- units, until all the requested units have been retired.
4386
4387
4388 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4389 IF l_disthist_rec.retirement_id IS NULL THEN
4390
4391 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4392 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4393 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4394 l_dist_quantity := 0;
4395 EXIT;
4396 ELSE
4397 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4398 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4399 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4400 END IF;
4401 i := i + 1;
4402 END IF;
4403
4404 END LOOP;
4405
4406 -- If there are no more non-retired distributions left and there are still some more units to be retired,
4407 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
4408 -- assigned to all the distributions.
4409
4410 IF l_dist_quantity > 0 THEN
4411 IF l_retired_quantity < l_dist_quantity THEN
4412 -- x_return_status := OKL_API.G_RET_STS_ERROR;
4413 -- Sold quantity is more than the total quantity assigned to asset distributions.
4414 OKC_API.set_message( p_app_name => 'OKL',
4415 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
4416 -- RAISE okc_api.G_EXCEPTION_ERROR;
4417 END IF;
4418 END IF;
4419
4420 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4421 do_partial_units_retirement(
4422 p_api_version => p_api_version,
4423 p_init_msg_list => OKC_API.G_FALSE,
4424 p_tax_owner => l_tax_owner,
4425 p_delta_cost => l_delta_cost,
4426 p_asset_id => l_okxassetlines_rec.asset_id,
4427 p_asset_number => l_okxassetlines_rec.asset_number,
4428 p_proceeds_of_sale => l_sale_amount,
4429 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4430 p_book_type_code => l_okxassetlines_rec.book_type_code,
4431 p_total_quantity => l_units_to_be_retired, -- units to be retierd
4432 p_dist_tbl => l_dist_tbl,
4433 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4434 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4435 x_msg_count => x_msg_count,
4436 x_msg_data => x_msg_data,
4437 x_return_status => x_return_status);
4438
4439
4440 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4441 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4442 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4443 RAISE OKC_API.G_EXCEPTION_ERROR;
4444 END IF;
4445
4446 l_units_retired := l_units_to_be_retired;
4447
4448 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
4449 ELSIF l_retired_quantity = 0 AND l_non_retired_quantity = 0 THEN -- This condition will be true only for TAX books
4450
4451 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
4452 -- do cost retirement for the tax book
4453
4454 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
4455 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
4456 -- Cost Adjustment will happen in tax book through do_cost_retirement
4457 -- Cost will become Residual Value
4458 l_cost := l_okxassetlines_rec.cost; -- for bug 5760603 -- Retire cost Not Rv
4459 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
4460 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
4461 l_cost := l_okxassetlines_rec.cost; -- Retire the current cost in FA
4462 END IF;
4463 -- SECHAWLA 21-NOV-2003 3262519 : end
4464
4465 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4466 do_cost_retirement(
4467 p_api_version => p_api_version,
4468 p_init_msg_list => OKC_API.G_FALSE,
4469 p_tax_owner => l_tax_owner,
4470 p_delta_cost => l_delta_cost,
4471 p_asset_id => l_okxassetlines_rec.asset_id,
4472 p_asset_number => l_okxassetlines_rec.asset_number,
4473 p_proceeds_of_sale => l_sale_amount,
4474 p_tax_book => l_okxassetlines_rec.book_type_code,
4475 --p_cost => l_cost_retired, -- SECHAWLA 13-JAN-03 Bug # 2701440
4476 -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the original request is for Full retirement, do a full cost retirement for the tax book
4477 --p_cost => l_okxassetlines_rec.cost, -- SECHAWLA 21-NOV-2003 3262519
4478 p_cost => l_cost, -- SECHAWLA 21-NOV-2003 3262519
4479 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4480 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4481 x_msg_count => x_msg_count,
4482 x_msg_data => x_msg_data,
4483 x_return_status => x_return_status);
4484
4485 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4486 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4487 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4488 RAISE OKC_API.G_EXCEPTION_ERROR;
4489 END IF;
4490 END IF;
4491 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
4492
4493 END IF;
4494
4495
4496 ELSE -- ordered quantity is either less or more than the original quantity
4497
4498 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN
4499
4500 l_dist_quantity := l_ordered_quantity; -- SECHAWLA 21-OCT-04 3924244
4501
4502 i := 0;
4503
4504
4505
4506 -- loop thru all the distributions of an asset, starting from the first distribution, compare the requested
4507 -- quantity with the distribution units. If distribution has more units than the requested quantity, then
4508 -- retire that distribution partially with quantity requested and exit the loop. If distribution has less
4509 -- than requested units then retire that distribution fully and move to next distribution for remaining
4510 -- units, until all the requested units have been retired.
4511
4512 l_retired_dist_units := 0;
4513 FOR l_disthist_rec IN l_disthist_csr(l_okxassetlines_rec.asset_id, l_okxassetlines_rec.book_type_code) LOOP
4514 -- First retire all non-retired distributions, maintain a unit count of already retired distributions.
4515 -- We will use this count at the end, when all non-retired distributions have been retired, to make
4516 -- sure that the units retired = ordered quantity
4517 IF l_disthist_rec.retirement_id IS NOT NULL THEN
4518 l_retired_dist_units := l_retired_dist_units + abs(l_disthist_rec.transaction_units);
4519
4520 ELSE
4521
4522 IF l_disthist_rec.units_assigned >= l_dist_quantity THEN
4523 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4524 l_dist_tbl(i).p_units_assigned := l_dist_quantity;
4525 l_dist_quantity := 0;
4526 EXIT;
4527 ELSE
4528 l_dist_tbl(i).p_distribution_id := l_disthist_rec.distribution_id;
4529 l_dist_tbl(i).p_units_assigned := l_disthist_rec.units_assigned;
4530 l_dist_quantity := l_dist_quantity - l_disthist_rec.units_assigned;
4531 END IF;
4532 i := i + 1;
4533 END IF;
4534 END LOOP;
4535
4536
4537 -- If there are no more distributions left and there are still some more units to be retired,
4538 -- then the input quantity was invalid. Quantity can not be more than the some total of the units
4539 -- assigned to all the distributions.
4540
4541
4542 IF l_dist_quantity > 0 THEN -- quantity to be retired (for non-retired distributions)
4543 IF l_retired_dist_units < l_dist_quantity THEN -- retired quantity isn't enough to match up with total qty
4544
4545 -- Sold quantity is more than the total quantity assigned to asset distributions.
4546 OKC_API.set_message( p_app_name => 'OKL',
4547 p_msg_name => 'OKL_AM_INVALID_DIST_QTY');
4548 END IF;
4549 -- SECHAWLA 21-OCT-04 3924244
4550 l_units_to_be_retired := l_ordered_quantity - l_dist_quantity; -- retire whatever is left
4551 ELSE
4552 l_units_to_be_retired := l_ordered_quantity; -- SECHAWLA 21-OCT-04 3924244
4553 END IF;
4554
4555
4556 IF l_dist_tbl.COUNT > 0 THEN
4557 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4558 do_partial_units_retirement(
4559 p_api_version => p_api_version,
4560 p_init_msg_list => OKC_API.G_FALSE,
4561 p_tax_owner => l_tax_owner,
4562 p_delta_cost => l_delta_cost,
4563 p_asset_id => l_okxassetlines_rec.asset_id,
4564 p_asset_number => l_okxassetlines_rec.asset_number,
4565 p_proceeds_of_sale => l_sale_amount,
4566 -- p_corporate_book => l_corporate_book, -- SECHAWLA Bug # 2701440 : Changed the paramete name
4567 p_book_type_code => l_okxassetlines_rec.book_type_code,
4568 p_total_quantity => l_units_to_be_retired, -- units to be retired
4569 p_dist_tbl => l_dist_tbl,
4570 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4571 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4572 x_msg_count => x_msg_count,
4573 x_msg_data => x_msg_data,
4574 x_return_status => x_return_status);
4575
4576
4577 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4578 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4579 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4580 RAISE OKC_API.G_EXCEPTION_ERROR;
4581 END IF;
4582
4583
4584 /* SECHAWLA 21-NOV-03 3262519 : This fetch is not required. Cost to be retired from the tax book should be
4585 -- calculated using tax book cost and not the corporate book cost, as the 2 costs can be different
4586
4587
4588 -- SECHAWLA 13-JAN-03 Bug # 2701440 : Added this code to get the cost retired for the corporate book
4589 -- This cost is used later to perform a cost retirement for the TAX book
4590 OPEN l_faretirement_csr(l_okxassetlines_rec.asset_id,l_okxassetlines_rec.corporate_book);
4591 FETCH l_faretirement_csr INTO l_cost_retired;
4592 -- Since asset is first retired from corporate book, this fetch will definitely find a row
4593 CLOSE l_faretirement_csr;
4594 */
4595
4596 l_units_retired := l_units_to_be_retired;
4597 ELSE
4598 -- If it reaches here, it means it didn't find any new distributions to retire. Since we are not
4599 -- processing any records in FA in this case, we consider this asset as alredy retired.
4600 l_already_retired := 'Y';
4601 END IF;
4602
4603 --SECHAWLA 23-DEC-02 Bug # 2701440 : Added the following code for tax book retirement
4604 ELSIF l_okxassetlines_rec.book_class = 'TAX' THEN
4605
4606 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
4607 -- SECHAWLA 21-NOV-2003 3262519 : get the cost that is to be retired
4608 IF l_tax_owner = 'LESSEE' THEN -- tax owner will have a value for Direct Finance/Sales Lease only.
4609 -- Cost Adjustment will happen in tax book through do_cost_retirement
4610 -- Cost will become Residual Value
4611 l_cost := l_okxassetlines_rec.cost; -- for bug 5760603 -- Retire cost Not Rv
4612 ELSE -- tax owner = 'LESSOR' (cost adj does not happen in tax book)
4613 -- OR tax owner is null (not DF/Sales lease, no cost adjustment)
4614 l_cost := l_okxassetlines_rec.cost; -- cost to be considered is the curent cost
4615 END IF;
4616 -- SECHAWLA 21-NOV-2003 3262519 : end
4617
4618 -- 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
4619 --IF l_okxassetlines_rec.cost >= l_cost_retired THEN -- SECHAWLA 21-nov-03 3262519
4620
4621 --SECHAWLA 21-NOV-2003 3262519 : Cost to be retired from tax book should be calculated using
4622 -- tax book's cost and the quentity retired in the corporate book
4623 l_cost_retired := (l_cost / l_okxassetlines_rec.current_units ) * l_units_retired;
4624
4625 IF l_cost >= l_cost_retired THEN
4626 -- this condition should always be true
4627
4628 -- do cost retirement for the tax book
4629 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4630 do_cost_retirement(
4631 p_api_version => p_api_version,
4632 p_init_msg_list => OKC_API.G_FALSE,
4633 p_tax_owner => l_tax_owner,
4634 p_delta_cost => l_delta_cost,
4635 p_asset_id => l_okxassetlines_rec.asset_id,
4636 p_asset_number => l_okxassetlines_rec.asset_number,
4637 p_proceeds_of_sale => l_sale_amount,
4638 p_tax_book => l_okxassetlines_rec.book_type_code,
4639 p_cost => l_cost_retired,
4640 p_prorate_convention => NULL, -- ansethur for Bug:5664106 l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4641 x_fa_trx_date => l_fa_trx_date, -- 15-DEC-04 SECHAWLA 4028371 added
4642 x_msg_count => x_msg_count,
4643 x_msg_data => x_msg_data,
4644 x_return_status => x_return_status);
4645
4646 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4647 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4648 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4649 RAISE OKC_API.G_EXCEPTION_ERROR;
4650 END IF;
4651
4652 --SECHAWLA 21-NOV-2003 3262519 : This condition will not occur now that we calculate cost to be
4653 -- retired from tax book using tax book cost itself
4654
4655
4656 /* -- SECHAWLA 13-JAN-03 Bug # 2701440 : If the tax book's cost is less than the cost retierd from the corp book
4657 -- but has not been fully retired yet, then perform a full cost retirement for tax book
4658 --ELSIF l_okxassetlines_rec.cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
4659 ELSIF l_cost > 0 THEN -- SECHAWLA 21-nov-03 3262519
4660 -- retire the whole remaining cost
4661 -- SECHAWLA 21-NOV-03, 3262519 : added tax owner and delta cost parameter to the following procedure
4662 do_cost_retirement(
4663 p_api_version => p_api_version,
4664 p_init_msg_list => OKC_API.G_FALSE,
4665 p_tax_owner => l_tax_owner,
4666 p_delta_cost => l_delta_cost,
4667 p_asset_id => l_okxassetlines_rec.asset_id,
4668 p_asset_number => l_okxassetlines_rec.asset_number,
4669 p_proceeds_of_sale => l_sale_amount,
4670 p_tax_book => l_okxassetlines_rec.book_type_code,
4671 --p_cost => l_okxassetlines_rec.cost, -- SECHAWLA 21-nov-03 3262519
4672 p_cost => l_cost, -- SECHAWLA 21-nov-03 3262519
4673 p_prorate_convention => l_okxassetlines_rec.prorate_convention_code, -- SECHAWLA 03-JUN-03 2999419 : Added this parameter
4674 x_msg_count => x_msg_count,
4675 x_msg_data => x_msg_data,
4676 x_return_status => x_return_status);
4677
4678 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4679 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4680 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4681 RAISE OKC_API.G_EXCEPTION_ERROR;
4682 END IF;
4683 */
4684
4685 END IF;
4686 END IF;
4687 -- SECHAWLA 23-DEC-02 Bug # 2701440 : end new code
4688
4689 END IF;
4690 END IF;
4691
4692 IF l_already_retired = 'N' THEN
4693 IF l_okxassetlines_rec.book_class = 'CORPORATE' THEN -- SECHAWLA Bug # 2701440 : Added this condition to
4694 -- store trx transaction and process a/c entries only
4695 -- for CORPORATE book
4696 -- create transaction header
4697 okl_am_util_pvt.get_transaction_id(
4698 p_try_name => l_trx_type,
4699 x_return_status => x_return_status,
4700 x_try_id => l_try_id);
4701
4702 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4703 -- Unable to find a transaction type for this transaction.
4704 OKL_API.set_message(p_app_name => 'OKL',
4705 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
4706 p_token1 => 'TRY_NAME',
4707 p_token1_value => 'Asset Disposition');
4708 RAISE OKC_API.G_EXCEPTION_ERROR;
4709 END IF;
4710
4711 lp_thpv_rec.tas_type := 'RFA';
4712 lp_thpv_rec.tsu_code := 'PROCESSED';
4713 lp_thpv_rec.try_id := l_try_id;
4714 lp_thpv_rec.date_trans_occurred := l_sys_date;
4715
4716 -- RRAVIKIR Legal Entity Changes
4717 lp_thpv_rec.legal_entity_id := l_legal_entity_id;
4718 -- Legal Entity Changes End
4719
4720 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def( p_api_version => p_api_version,
4721 p_init_msg_list => OKC_API.G_FALSE,
4722 x_return_status => x_return_status,
4723 x_msg_count => x_msg_count,
4724 x_msg_data => x_msg_data,
4725 p_thpv_rec => lp_thpv_rec,
4726 x_thpv_rec => lx_thpv_rec);
4727
4728
4729 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4730 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4731 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4732 RAISE OKC_API.G_EXCEPTION_ERROR;
4733 END IF;
4734
4735 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
4736 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
4737 lp_tlpv_rec.currency_code := l_func_curr_code;
4738
4739
4740 -- Create transaction Line
4741 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
4742 lp_tlpv_rec.iay_id := l_okxassetlines_rec.depreciation_category;
4743 lp_tlpv_rec.kle_id := l_kle_id;
4744 lp_tlpv_rec.line_number := 1;
4745 lp_tlpv_rec.tal_type := 'RFL';
4746 lp_tlpv_rec.asset_number := l_okxassetlines_rec.asset_number;
4747 lp_tlpv_rec.corporate_book := l_okxassetlines_rec.book_type_code;
4748 lp_tlpv_rec.original_cost := l_okxassetlines_rec.original_cost;
4749 lp_tlpv_rec.current_units := l_okxassetlines_rec.current_units;
4750 lp_tlpv_rec.units_retired := l_units_retired ;
4751 lp_tlpv_rec.dnz_asset_id := l_okxassetlines_rec.asset_id;
4752 lp_tlpv_rec.dnz_khr_id := l_okxassetlines_rec.dnz_chr_id;
4753
4754 -- SECHAWLA 15-DEC-04 4028371 : set FA date on trx line
4755 lp_tlpv_rec.FA_TRX_DATE := l_fa_trx_date;
4756
4757 OKL_TXL_ASSETS_PUB.create_txl_asset_def(p_api_version => p_api_version,
4758 p_init_msg_list => OKC_API.G_FALSE,
4759 x_return_status => x_return_status,
4760 x_msg_count => x_msg_count,
4761 x_msg_data => x_msg_data,
4762 p_tlpv_rec => lp_tlpv_rec,
4763 x_tlpv_rec => lx_tlpv_rec);
4764
4765 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4766 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4767 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4768 RAISE OKC_API.G_EXCEPTION_ERROR;
4769 END IF;
4770
4771
4772
4773 -- make call to accounting entries
4774 process_accounting_entries(
4775 p_api_version => p_api_version,
4776 p_init_msg_list => OKC_API.G_FALSE,
4777 x_return_status => x_return_status,
4778 x_msg_count => x_msg_count,
4779 x_msg_data => x_msg_data,
4780 p_kle_id => l_kle_id,
4781 p_try_id => l_try_id,
4782 p_sys_date => l_sys_date,
4783 p_source_id => lx_tlpv_rec.id,
4784 p_trx_type => l_trx_name,
4785 p_amount => l_sale_amount,
4786 p_func_curr_code => l_func_curr_code,
4787 x_total_amount => lx_total_amount,
4788 p_legal_entity_id => l_legal_entity_id);
4789
4790 -- rollback if error in accounting entries
4791 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4792 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4793 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4794 RAISE OKC_API.G_EXCEPTION_ERROR;
4795 END IF;
4796
4797 -- Store the amount at the header and line level in trx tables
4798
4799 -- Update amount in the header table
4800 lp_thpv_rec := lp_thpv_empty_rec;
4801 lp_thpv_rec.id := lx_thpv_rec.id;
4802 lp_thpv_rec.total_match_amount := lx_total_amount;
4803
4804 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
4805 p_api_version => p_api_version,
4806 p_init_msg_list => OKC_API.G_FALSE,
4807 x_return_status => x_return_status,
4808 x_msg_count => x_msg_count,
4809 x_msg_data => x_msg_data,
4810 p_thpv_rec => lp_thpv_rec,
4811 x_thpv_rec => lx_thpv_rec);
4812
4813 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4814 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4815 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4816 RAISE OKC_API.G_EXCEPTION_ERROR;
4817 END IF;
4818
4819 -- Update amount in the lines table.
4820 lp_tlpv_rec := lp_tlpv_empty_rec;
4821 lp_tlpv_rec.id := lx_tlpv_rec.id;
4822 lp_tlpv_rec.match_amount := lx_total_amount;
4823
4824 --SECHAWLA 03-JAN-03 Added the following statement as a temporary fix to LA's ROUNDING ERROR problem
4825 lp_tlpv_rec.kle_id := l_kle_id;
4826
4827 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
4828 lp_tlpv_rec.currency_code := l_func_curr_code;
4829
4830 OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
4831 p_api_version => p_api_version,
4832 p_init_msg_list => OKC_API.G_FALSE,
4833 x_return_status => x_return_status,
4834 x_msg_count => x_msg_count,
4835 x_msg_data => x_msg_data,
4836 p_tlpv_rec => lp_tlpv_rec,
4837 x_tlpv_rec => lx_tlpv_rec);
4838
4839 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4840 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4841 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4842 RAISE OKC_API.G_EXCEPTION_ERROR;
4843 END IF;
4844 --- End store amounts at the header and line level
4845
4846 END IF; -- if book_class = corporate
4847
4848 END IF; -- if l_already_retired = 'N'
4849
4850
4851 END LOOP;
4852
4853
4854 -- commented call to expire_item djanaswa bug 6736148 begin
4855
4856 --- Expire item in Installed Base
4857 /*
4858 IF l_ordered_quantity >= l_quantity THEN -- SECHAWLA 21-OCT-04 3924244
4859 -- Retire all existing instances
4860 FOR l_itemlocation_rec in l_itemlocation_csr(l_kle_id) LOOP
4861 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
4862 expire_item (
4863 p_api_version => p_api_version,
4864 p_init_msg_list => OKC_API.G_FALSE,
4865 x_msg_count => x_msg_count,
4866 x_msg_data => x_msg_data,
4867 x_return_status => x_return_status ,
4868 p_instance_id => l_itemlocation_rec.instance_id,
4869 p_end_date => l_sys_date);
4870
4871 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4872 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4873 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4874 RAISE OKC_API.G_EXCEPTION_ERROR;
4875 END IF;
4876
4877 END IF;
4878
4879 END LOOP;
4880
4881 ELSE -- quantity < original quantity
4882 instance_counter := 1;
4883 -- retire number of instances equal to the input quantity
4884 FOR l_itemlocation_rec in l_itemlocation_csr(l_kle_id) LOOP
4885 IF l_itemlocation_rec.instance_end_date IS NULL THEN-- Instance is not already expired.
4886 expire_item (
4887 p_api_version => p_api_version,
4888 p_init_msg_list => OKC_API.G_FALSE,
4889 x_msg_count => x_msg_count,
4890 x_msg_data => x_msg_data,
4891 x_return_status => x_return_status ,
4892 p_instance_id => l_itemlocation_rec.instance_id,
4893 p_end_date => l_sys_date);
4894
4895 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4896 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4897 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4898 RAISE OKC_API.G_EXCEPTION_ERROR;
4899 END IF;
4900
4901 IF instance_counter = l_ordered_quantity THEN -- SECHAWLA 21-OCT-04 3924244
4902 EXIT;
4903 END IF;
4904 instance_counter := instance_counter + 1;
4905 END IF;
4906 END LOOP;
4907
4908 END IF;
4909 */
4910 -------------- end IB Retirement -----------------------
4911 -- commented call to expire_item djanaswa bug 6736148 end
4912
4913
4914
4915 -- Loop thru all the pending transactions in okl_trx_assets_v and okl_txl_assets_v
4916 -- and update the status to 'CANCELED'
4917
4918 FOR l_assettrx_rec IN l_assettrx_csr(l_kle_id) LOOP
4919 -- update the staus (tsu_code) in okl_trx_assets_v
4920 lp_thpv_rec := lp_thpv_empty_rec;
4921 lp_thpv_rec.id := l_assettrx_rec.id;
4922 lp_thpv_rec.tsu_code := 'CANCELED';
4923 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
4924 p_api_version => p_api_version,
4925 p_init_msg_list => OKC_API.G_FALSE,
4926 x_return_status => x_return_status,
4927 x_msg_count => x_msg_count,
4928 x_msg_data => x_msg_data,
4929 p_thpv_rec => lp_thpv_rec,
4930 x_thpv_rec => lx_thpv_rec);
4931
4932 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4933 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4934 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4935 RAISE OKC_API.G_EXCEPTION_ERROR;
4936 END IF;
4937 END LOOP;
4938
4939 -- ++++++++++++++++++++ service contract integration begin ++++++++++++++++++
4940
4941 -- RMUNJULU 3061751 11-SEP-2003
4942 -- Check if linked service contract exists for the asset which is disposed
4943 l_service_int_needed := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed(
4944 p_asset_id => l_kle_id,
4945 p_source => 'DISPOSE');
4946
4947 -- Do the Service Contract Integration Notification for DISPOSE
4948 OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration(
4949 p_transaction_id => l_kle_id,
4950 p_transaction_date => SYSDATE,
4951 p_source => 'DISPOSE_2',
4952 p_service_integration_needed => l_service_int_needed);
4953
4954 -- ++++++++++++++++++++ service contract integration end ++++++++++++++++++
4955
4956 --END LOOP; -- SECHAWLA 21-OCT-04 3924244
4957
4958 -- MDOKAL: 18-SEP-03 - Bug 3082639
4959 -------------- Securitization Processing -----------------------
4960
4961 OKL_AM_SECURITIZATION_PVT.process_securitized_streams(
4962 p_api_version => p_api_version,
4963 p_init_msg_list => OKC_API.G_FALSE,
4964 x_return_status => x_return_status,
4965 x_msg_count => x_msg_count,
4966 x_msg_data => x_msg_data,
4967 p_kle_id => l_kle_id,
4968 p_sale_price => l_sale_amount,
4969 p_call_origin => OKL_SECURITIZATION_PVT.G_TRX_REASON_ASSET_DISPOSAL);
4970
4971 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4972 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4973 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4974 RAISE OKC_API.G_EXCEPTION_ERROR;
4975 END IF;
4976 -------------- end Securitization Processing -----------------------
4977 --akrangan added for sla populate sources cr start
4978 IF g_trans_id_tbl.COUNT > 0
4979 THEN
4980 FOR i IN g_trans_id_tbl.FIRST .. g_trans_id_tbl.LAST
4981 LOOP
4982 -- header record
4983 l_fxhv_rec.source_id := lx_thpv_rec.id;
4984 l_fxhv_rec.source_table := 'OKL_TRX_ASSETS';
4985 l_fxhv_rec.khr_id := lx_tlpv_rec.dnz_khr_id;
4986 l_fxhv_rec.try_id := lx_thpv_rec.try_id;
4987 -- line record
4988 l_fxlv_rec.source_id := lx_tlpv_rec.id;
4989 l_fxlv_rec.source_table := 'OKL_TXL_ASSETS_B';
4990 l_fxlv_rec.kle_id := lx_tlpv_rec.kle_id;
4991 l_fxlv_rec.asset_id := lx_tlpv_rec.dnz_asset_id;
4992 l_fxlv_rec.fa_transaction_id := g_trans_id_tbl(i);
4993 l_fxlv_rec.asset_book_type_name := lx_tlpv_rec.corporate_book;
4994
4995 okl_sla_acc_sources_pvt.populate_sources(p_api_version => p_api_version,
4996 p_init_msg_list => okc_api.g_false,
4997 p_fxhv_rec => l_fxhv_rec,
4998 p_fxlv_rec => l_fxlv_rec,
4999 x_return_status => x_return_status,
5000 x_msg_count => x_msg_count,
5001 x_msg_data => x_msg_data);
5002
5003 IF (x_return_status = okc_api.g_ret_sts_unexp_error)
5004 THEN
5005 RAISE okl_api.g_exception_unexpected_error;
5006 ELSIF (x_return_status = okc_api.g_ret_sts_error)
5007 THEN
5008 RAISE okl_api.g_exception_error;
5009 END IF;
5010 END LOOP;
5011 END IF;
5012 --akrangan added for sla populate sources cr end
5013
5014 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5015 EXCEPTION
5016 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5017
5018 IF l_orderheaders_csr%ISOPEN THEN
5019 CLOSE l_orderheaders_csr;
5020 END IF;
5021
5022 IF l_assetreturn_csr%ISOPEN THEN
5023 CLOSE l_assetreturn_csr;
5024 END IF;
5025
5026 IF l_okxassetlines_csr%ISOPEN THEN
5027 CLOSE l_okxassetlines_csr;
5028 END IF;
5029
5030 IF l_orderlines_csr%ISOPEN THEN
5031 CLOSE l_orderlines_csr;
5032 END IF;
5033
5034 IF l_disthist_csr%ISOPEN THEN
5035 CLOSE l_disthist_csr;
5036 END IF;
5037
5038 IF l_assettrx_csr%ISOPEN THEN
5039 CLOSE l_assettrx_csr;
5040 END IF;
5041
5042 IF l_itemlocation_csr%ISOPEN THEN
5043 CLOSE l_itemlocation_csr;
5044 END IF;
5045
5046 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
5047 IF l_periodofaddition_csr%ISOPEN THEN
5048 CLOSE l_periodofaddition_csr;
5049 END IF;
5050
5051 IF l_bookcontrols_csr%ISOPEN THEN
5052 CLOSE l_bookcontrols_csr;
5053 END IF;
5054
5055 -- SECHAWLA 21-nov-03 3262519 : close new cursors
5056 IF l_dealtype_csr%ISOPEN THEN
5057 CLOSE l_dealtype_csr;
5058 END IF;
5059
5060 IF l_linesfullv_csr%ISOPEN THEN
5061 CLOSE l_linesfullv_csr;
5062 END IF;
5063
5064 --SECHAWLA 10-FEB-06 5016156
5065 IF l_offlseassettrx_csr%ISOPEN THEN
5066 CLOSE l_offlseassettrx_csr;
5067 END IF;
5068
5069 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5070 (
5071 l_api_name,
5072 G_PKG_NAME,
5073 'OKC_API.G_RET_STS_ERROR',
5074 x_msg_count,
5075 x_msg_data,
5076 '_PVT'
5077 );
5078 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5079
5080 IF l_orderheaders_csr%ISOPEN THEN
5081 CLOSE l_orderheaders_csr;
5082 END IF;
5083
5084 IF l_assetreturn_csr%ISOPEN THEN
5085 CLOSE l_assetreturn_csr;
5086 END IF;
5087
5088 IF l_okxassetlines_csr%ISOPEN THEN
5089 CLOSE l_okxassetlines_csr;
5090 END IF;
5091
5092 IF l_orderlines_csr%ISOPEN THEN
5093 CLOSE l_orderlines_csr;
5094 END IF;
5095
5096 IF l_disthist_csr%ISOPEN THEN
5097 CLOSE l_disthist_csr;
5098 END IF;
5099
5100 IF l_assettrx_csr%ISOPEN THEN
5101 CLOSE l_assettrx_csr;
5102 END IF;
5103
5104 IF l_itemlocation_csr%ISOPEN THEN
5105 CLOSE l_itemlocation_csr;
5106 END IF;
5107
5108 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
5109 IF l_periodofaddition_csr%ISOPEN THEN
5110 CLOSE l_periodofaddition_csr;
5111 END IF;
5112
5113 IF l_bookcontrols_csr%ISOPEN THEN
5114 CLOSE l_bookcontrols_csr;
5115 END IF;
5116
5117 -- SECHAWLA 21-nov-03 3262519 : close new cursors
5118 IF l_dealtype_csr%ISOPEN THEN
5119 CLOSE l_dealtype_csr;
5120 END IF;
5121
5122 IF l_linesfullv_csr%ISOPEN THEN
5123 CLOSE l_linesfullv_csr;
5124 END IF;
5125
5126 --SECHAWLA 10-FEB-06 5016156
5127 IF l_offlseassettrx_csr%ISOPEN THEN
5128 CLOSE l_offlseassettrx_csr;
5129 END IF;
5130
5131 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5132 (
5133 l_api_name,
5134 G_PKG_NAME,
5135 'OKC_API.G_RET_STS_UNEXP_ERROR',
5136 x_msg_count,
5137 x_msg_data,
5138 '_PVT'
5139 );
5140 WHEN OTHERS THEN
5141
5142 IF l_orderheaders_csr%ISOPEN THEN
5143 CLOSE l_orderheaders_csr;
5144 END IF;
5145
5146 IF l_assetreturn_csr%ISOPEN THEN
5147 CLOSE l_assetreturn_csr;
5148 END IF;
5149
5150 IF l_okxassetlines_csr%ISOPEN THEN
5151 CLOSE l_okxassetlines_csr;
5152 END IF;
5153
5154 IF l_orderlines_csr%ISOPEN THEN
5155 CLOSE l_orderlines_csr;
5156 END IF;
5157
5158 IF l_disthist_csr%ISOPEN THEN
5159 CLOSE l_disthist_csr;
5160 END IF;
5161
5162 IF l_assettrx_csr%ISOPEN THEN
5163 CLOSE l_assettrx_csr;
5164 END IF;
5165
5166 IF l_itemlocation_csr%ISOPEN THEN
5167 CLOSE l_itemlocation_csr;
5168 END IF;
5169
5170 --SECHAWLA 05-FEB-03 Bug # 2781557 : Close the 2 new cursors
5171 IF l_periodofaddition_csr%ISOPEN THEN
5172 CLOSE l_periodofaddition_csr;
5173 END IF;
5174
5175 IF l_bookcontrols_csr%ISOPEN THEN
5176 CLOSE l_bookcontrols_csr;
5177 END IF;
5178
5179 -- SECHAWLA 21-nov-03 3262519 : close new cursors
5180 IF l_dealtype_csr%ISOPEN THEN
5181 CLOSE l_dealtype_csr;
5182 END IF;
5183
5184 IF l_linesfullv_csr%ISOPEN THEN
5185 CLOSE l_linesfullv_csr;
5186 END IF;
5187
5188 --SECHAWLA 10-FEB-06 5016156
5189 IF l_offlseassettrx_csr%ISOPEN THEN
5190 CLOSE l_offlseassettrx_csr;
5191 END IF;
5192
5193 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5194 (
5195 l_api_name,
5196 G_PKG_NAME,
5197 'OTHERS',
5198 x_msg_count,
5199 x_msg_data,
5200 '_PVT'
5201 );
5202 END dispose_asset;
5203
5204
5205
5206
5207 -- Start of comments
5208 --
5209 -- Procedure Name : undo_retirement
5210 -- Description : This procedure is used to undo the asset retirement
5211 -- Business Rules :
5212 -- Parameters : p_retirement_id
5213
5214 -- Version : 1.0
5215 -- End of comments
5216
5217 PROCEDURE undo_retirement( p_api_version IN NUMBER,
5218 p_init_msg_list IN VARCHAR2,
5219 x_return_status OUT NOCOPY VARCHAR2,
5220 x_msg_count OUT NOCOPY NUMBER,
5221 x_msg_data OUT NOCOPY VARCHAR2,
5222 p_retirement_id IN NUMBER) IS
5223
5224 l_trans_rec FA_API_TYPES.trans_rec_type;
5225 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
5226 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
5227 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
5228 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
5229 l_inv_tbl FA_API_TYPES.inv_tbl_type;
5230 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5231 l_api_name CONSTANT VARCHAR2(30) := 'undo_retirement';
5232 l_api_version CONSTANT NUMBER := 1;
5233 l_dummy VARCHAR2(1);
5234
5235 -- This cursor is used to validate the retirement ID
5236 CURSOR l_faretirement_csr(p_retirement_id NUMBER) IS
5237 SELECT 'x'
5238 FROM fa_retirements
5239 WHERE retirement_id = p_retirement_id;
5240
5241 BEGIN
5242 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
5243 G_PKG_NAME,
5244 p_init_msg_list,
5245 l_api_version,
5246 p_api_version,
5247 '_PVT',
5248 x_return_status);
5249
5250 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5252 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5253 RAISE OKC_API.G_EXCEPTION_ERROR;
5254 END IF;
5255
5256 IF p_retirement_id IS NULL OR p_retirement_id = OKL_API.G_MISS_NUM THEN
5257 x_return_status := OKL_API.G_RET_STS_ERROR;
5258 -- retirement id is required
5259 OKC_API.set_message( p_app_name => 'OKC',
5260 p_msg_name => G_REQUIRED_VALUE,
5261 p_token1 => G_COL_NAME_TOKEN,
5262 p_token1_value => 'RETIREMENT_ID');
5263
5264
5265 RAISE OKC_API.G_EXCEPTION_ERROR;
5266 END IF;
5267
5268 OPEN l_faretirement_csr(p_retirement_id);
5269 FETCH l_faretirement_csr INTO l_dummy;
5270 IF l_faretirement_csr%NOTFOUND THEN
5271 x_return_status := OKL_API.G_RET_STS_ERROR;
5272 -- retirement id is invalid
5273 OKC_API.set_message( p_app_name => 'OKC',
5274 p_msg_name => G_INVALID_VALUE,
5275 p_token1 => G_COL_NAME_TOKEN,
5276 p_token1_value => 'RETIREMENT_ID');
5277
5278
5279 RAISE OKC_API.G_EXCEPTION_ERROR;
5280 END IF;
5281 CLOSE l_faretirement_csr;
5282
5283 -- transaction information
5284 l_trans_rec.transaction_type_code := NULL;
5285 l_trans_rec.transaction_date_entered := NULL;
5286
5287
5288 --SECHAWLA 29-DEC-05 3827148 : added
5289 l_trans_rec.calling_interface := 'OKL:'||'Asset Disposition:'||'RFA';
5290
5291 -- retirement information
5292 l_asset_retire_rec.retirement_id := p_retirement_id;
5293
5294
5295 FA_RETIREMENT_PUB.undo_retirement( p_api_version => p_api_version,
5296 p_init_msg_list => OKC_API.G_FALSE,
5297 p_commit => FND_API.G_FALSE,
5298 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5299 p_calling_fn => NULL,
5300 x_return_status => x_return_status,
5301 x_msg_count => x_msg_count,
5302 x_msg_data => x_msg_data,
5303 px_trans_rec => l_trans_rec,
5304 px_asset_hdr_rec => l_asset_hdr_rec,
5305 px_asset_retire_rec => l_asset_retire_rec );
5306
5307 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5308 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5309 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5310 RAISE OKC_API.G_EXCEPTION_ERROR;
5311 END IF;
5312
5313
5314 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5315 EXCEPTION
5316 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5317 IF l_faretirement_csr%ISOPEN THEN
5318 CLOSE l_faretirement_csr;
5319 END IF;
5320 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5321 (
5322 l_api_name,
5323 G_PKG_NAME,
5324 'OKC_API.G_RET_STS_ERROR',
5325 x_msg_count,
5326 x_msg_data,
5327 '_PVT'
5328 );
5329 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5330 IF l_faretirement_csr%ISOPEN THEN
5331 CLOSE l_faretirement_csr;
5332 END IF;
5333 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5334 (
5335 l_api_name,
5336 G_PKG_NAME,
5337 'OKC_API.G_RET_STS_UNEXP_ERROR',
5338 x_msg_count,
5339 x_msg_data,
5340 '_PVT'
5341 );
5342 WHEN OTHERS THEN
5343 IF l_faretirement_csr%ISOPEN THEN
5344 CLOSE l_faretirement_csr;
5345 END IF;
5346 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5347 (
5348 l_api_name,
5349 G_PKG_NAME,
5350 'OTHERS',
5351 x_msg_count,
5352 x_msg_data,
5353 '_PVT'
5354 );
5355 END undo_retirement;
5356
5357
5358 -- Start of comments
5359 --
5360 -- Procedure Name : expire_item
5361 -- Description : This procedure is expire an item in installed base
5362 -- Business Rules :
5363 -- Parameters : p_instance_id
5364 -- p_end_date
5365
5366 -- Version : 1.0
5367 -- End of comments
5368
5369
5370 PROCEDURE expire_item (
5371 p_api_version IN NUMBER,
5372 p_init_msg_list IN VARCHAR2 ,
5373 x_msg_count OUT NOCOPY NUMBER,
5374 x_msg_data OUT NOCOPY VARCHAR2,
5375 x_return_status OUT NOCOPY VARCHAR2,
5376 p_instance_id IN NUMBER,
5377 p_end_date IN DATE ) IS
5378
5379 -- subtypes moved from okl_am_item_location_pvt
5380
5381 SUBTYPE instance_rec IS
5382 csi_datastructures_pub.instance_rec;
5383 SUBTYPE transaction_rec IS
5384 csi_datastructures_pub.transaction_rec;
5385 SUBTYPE id_tbl IS
5386 csi_datastructures_pub.id_tbl;
5387 SUBTYPE instance_query_rec IS
5388 csi_datastructures_pub.instance_query_rec;
5389 SUBTYPE party_query_rec IS
5390 csi_datastructures_pub.party_query_rec;
5391 SUBTYPE party_account_query_rec IS
5392 csi_datastructures_pub.party_account_query_rec;
5393 SUBTYPE instance_header_tbl IS
5394 csi_datastructures_pub.instance_header_tbl;
5395 SUBTYPE extend_attrib_values_tbl IS
5396 csi_datastructures_pub.extend_attrib_values_tbl;
5397 SUBTYPE party_tbl IS
5398 csi_datastructures_pub.party_tbl;
5399 SUBTYPE party_account_tbl IS
5400 csi_datastructures_pub.party_account_tbl;
5401 SUBTYPE pricing_attribs_tbl IS
5402 csi_datastructures_pub.pricing_attribs_tbl;
5403 SUBTYPE organization_units_tbl IS
5404 csi_datastructures_pub.organization_units_tbl;
5405 SUBTYPE instance_asset_tbl IS
5406 csi_datastructures_pub.instance_asset_tbl;
5407
5408
5409 -- Get Item Instance parameters
5410 l_instance_query_rec instance_query_rec;
5411 l_party_query_rec party_query_rec;
5412 l_account_query_rec party_account_query_rec;
5413 l_instance_header_tbl instance_header_tbl;
5414
5415 -- Expire Item Instance parameters
5416 l_instance_rec instance_rec;
5417 l_txn_rec transaction_rec;
5418 l_instance_id_lst id_tbl;
5419
5420 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5421 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5422
5423 l_api_name CONSTANT VARCHAR2(30) := 'expire_item';
5424 l_api_version CONSTANT NUMBER := 1;
5425 l_msg_count NUMBER := FND_API.G_MISS_NUM;
5426 l_msg_data VARCHAR2(2000);
5427
5428 BEGIN
5429
5430 -- ***************************************************************
5431 -- Check API version, initialize message list and create savepoint
5432 -- ***************************************************************
5433
5434 l_return_status := OKL_API.START_ACTIVITY (
5435 l_api_name,
5436 G_PKG_NAME,
5437 p_init_msg_list,
5438 l_api_version,
5439 p_api_version,
5440 '_PVT',
5441 x_return_status);
5442
5443 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5444 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5445 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5446 RAISE OKL_API.G_EXCEPTION_ERROR;
5447 END IF;
5448
5449 -- ************************
5450 -- Get Item Instance record
5451 -- ************************
5452
5453 l_instance_query_rec.instance_id := p_instance_id;
5454
5455 csi_item_instance_pub.get_item_instances (
5456 p_api_version => l_api_version,
5457 p_commit => FND_API.G_FALSE,
5458 p_init_msg_list => FND_API.G_FALSE,
5459 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5460 p_instance_query_rec => l_instance_query_rec,
5461 p_party_query_rec => l_party_query_rec,
5462 p_account_query_rec => l_account_query_rec,
5463 p_transaction_id => NULL,
5464 p_resolve_id_columns => FND_API.G_FALSE,
5465 p_active_instance_only => FND_API.G_TRUE,
5466 x_instance_header_tbl => l_instance_header_tbl,
5467 x_return_status => l_return_status,
5468 x_msg_count => l_msg_count,
5469 x_msg_data => l_msg_data);
5470
5471 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
5472 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5473 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
5474 RAISE OKL_API.G_EXCEPTION_ERROR;
5475 ELSIF (NVL (l_instance_header_tbl.COUNT, 0) <> 1) THEN
5476 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5477 END IF;
5478
5479 -- *************************************
5480 -- Initialize parameters to be passed in
5481 -- *************************************
5482
5483 l_instance_rec.instance_id := l_instance_header_tbl(1).instance_id;
5484 l_instance_rec.object_version_number := l_instance_header_tbl(1).object_version_number;
5485
5486 l_instance_rec.active_end_date := p_end_date;
5487
5488
5489 okl_am_util_pvt.initialize_txn_rec (l_txn_rec);
5490
5491 -- **************************************
5492 -- Call Installed Base API to expire item
5493 -- **************************************
5494
5495 csi_item_instance_pub.expire_item_instance (
5496 p_api_version => l_api_version,
5497 p_commit => FND_API.G_FALSE,
5498 p_init_msg_list => FND_API.G_FALSE,
5499 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5500 p_instance_rec => l_instance_rec,
5501 --p_expire_children => FND_API.G_FALSE, -- 10-AUG-04 SECHAWLA 3819339
5502 p_expire_children => FND_API.G_TRUE, -- 10-AUG-04 SECHAWLA 3819339 Expire all child instances before expiring parent
5503 p_txn_rec => l_txn_rec,
5504 x_instance_id_lst => l_instance_id_lst,
5505 x_return_status => l_return_status,
5506 x_msg_count => l_msg_count,
5507 x_msg_data => l_msg_data);
5508
5509 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
5510 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
5511 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
5512 RAISE OKL_API.G_EXCEPTION_ERROR;
5513 END IF;
5514
5515 -- **************
5516 -- Return results
5517 -- **************
5518
5519 x_return_status := l_overall_status;
5520
5521 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
5522
5523 EXCEPTION
5524
5525 WHEN OKL_API.G_EXCEPTION_ERROR THEN
5526 x_return_status := OKL_API.HANDLE_EXCEPTIONS
5527 (
5528 l_api_name,
5529 G_PKG_NAME,
5530 'OKL_API.G_RET_STS_ERROR',
5531 x_msg_count,
5532 x_msg_data,
5533 '_PVT'
5534 );
5535
5536 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5537 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5538 (
5539 l_api_name,
5540 G_PKG_NAME,
5541 'OKL_API.G_RET_STS_UNEXP_ERROR',
5542 x_msg_count,
5543 x_msg_data,
5544 '_PVT'
5545 );
5546
5547 WHEN OTHERS THEN
5548
5549 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
5550 (
5551 l_api_name,
5552 G_PKG_NAME,
5553 'OTHERS',
5554 x_msg_count,
5555 x_msg_data,
5556 '_PVT'
5557 );
5558
5559 END expire_item;
5560
5561
5562 END OKL_AM_ASSET_DISPOSE_PVT;
[Home] [Help]