1 PACKAGE BODY OKL_AM_RV_WRITEDOWN_PVT AS
2 /* $Header: OKLRRVWB.pls 120.5 2009/05/07 06:23:34 rpillay ship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : create_residual_value_trx
7 -- Description : The main body of the package. This procedure gets a table of line ids along with the new
8 -- residual values as parameter. It then validates the new RV to make sure that it is less than
9 -- the current RV and then creates residual value transactions in OKL_TRX_ASSETS_V and
10 -- OKL_TXL_ASSETS_V
11 -- Business Rules :
12 -- Parameters : p_assets_tbl
13 -- History : SECHAWLA 24-DEC-02 : Bug # 2726739
14 -- Added logic to store currency codes and conversion factors
15 -- Version : 1.0
16 -- History : SECHAWLA 07-FEB-03 2789656
17 -- Changed the sequence of validations so that the validation to check for a pending RVW
18 -- transaction is done before all other validations.
19 -- End of comments
20
21
22 PROCEDURE create_residual_value_trx( p_api_version IN NUMBER,
23 p_init_msg_list IN VARCHAR2 ,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2,
27 p_assets_tbl IN assets_tbl_type,
28 x_residual_value_status OUT NOCOPY VARCHAR2) IS -- this flag is redundant,
29 -- we are keeping it for the time
30 -- being to avoid
31 -- rosetta regeneration
32
33 SUBTYPE thpv_rec_type IS okl_trx_assets_pub.thpv_rec_type;
34 SUBTYPE tlpv_rec_type IS okl_txl_assets_pub.tlpv_rec_type;
35
36
37 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
38 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
39 l_record_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
40
41
42 l_api_name CONSTANT VARCHAR2(30) := 'create_residual_value_trx';
43 l_api_version CONSTANT NUMBER := 1;
44
45 i NUMBER := 0;
46 l_name VARCHAR2(150);
47 l_description VARCHAR2(1995);
48 l_old_residual_value NUMBER;
49 l_oec NUMBER;
50 l_chr_id NUMBER;
51 l_try_id okl_trx_types_v.id%TYPE;
52 lp_thpv_rec thpv_rec_type;
53 lx_thpv_rec thpv_rec_type;
54 lp_tlpv_rec tlpv_rec_type;
55 lx_tlpv_rec tlpv_rec_type;
56 l_sysdate DATE;
57 l_sts_code VARCHAR2(30);
58 l_contract_number VARCHAR2(120);
59 l_count NUMBER;
60
61 --SECHAWLA Bug # 2726739 : new declarations
62 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
63 l_contract_curr_code okc_k_headers_b.currency_code%TYPE;
64 lx_contract_currency okl_k_headers_full_v.currency_code%TYPE;
65 lx_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
66 lx_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
67 lx_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
68 lx_converted_amount NUMBER;
69
70
71 -- This cursor selects line item fields and contract status for a given line ID
72 CURSOR l_linesfullv_csr(p_id NUMBER) IS
73 SELECT l.name, l.item_description, l.residual_value, l.oec, l.chr_id, h.contract_number
74 FROM okl_k_lines_full_v l, okc_k_headers_b h
75 WHERE l.chr_id = h.id
76 AND l.id = p_id;
77 -- we can use chr_id in the above cursor as we will be pulling data only for the TOP LINE (Financial Asset) which
78 -- will always have the chr_id.
79
80 -- This cursor is used to check if a pending residual value writedown transaction already exists for a contract.
81 -- Included ERROR for Bug# 7014234
82 CURSOR l_assettrx_csr(p_khr_id NUMBER) IS
83 SELECT count(*)
84 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l
85 WHERE h.id = l.tas_id
86 AND h.tsu_code IN ('ENTERED', 'ERROR')
87 AND h.tas_type = 'ARC'
88 AND l.dnz_khr_id = p_khr_id;
89
90 -- This cursor is used to check if an accepted termination quote exists for an asset line.
91 CURSOR l_quotes_csr(p_kle_id NUMBER) IS
92 SELECT l.name
93 FROM okl_trx_quotes_b qh, okl_txl_quote_lines_b ql, okl_k_lines_full_v l
94 WHERE qh.id = ql.qte_id
95 AND qh.qst_code = 'ACCEPTED'
96 AND ql.qlt_code = 'AMCFIA'
97 AND ql.kle_id = l.id
98 AND ql.kle_id = p_kle_id;
99
100 -- RRAVIKIR Legal Entity Changes
101 CURSOR l_oklheaders_csr(cp_khr_id NUMBER) IS
102 SELECT legal_entity_id
103 FROM okl_k_headers
104 WHERE id = cp_khr_id;
105
106 l_legal_entity_id NUMBER;
107 -- Legal Entity Changes End
108
109 -- Begin -- Check the contract term to allow RV updates.Bug# 7014234
110 CURSOR c_contract_date_csr(p_chr_id IN NUMBER)IS
111 SELECT start_date,
112 end_date
113 FROM okc_k_headers_b
114 WHERE id = p_chr_id;
115
116 chr_rec c_contract_date_csr%ROWTYPE;
117 l_icx_date_format varchar2(240);
118 -- End -- Check the contract term to allow RV updates.Bug# 7014234
119
120 BEGIN
121
122 l_record_status := OKL_API.G_RET_STS_SUCCESS;
123
124 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
125 G_PKG_NAME,
126 p_init_msg_list,
127 l_api_version,
128 p_api_version,
129 '_PVT',
130 x_return_status);
131
132 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
133 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
134 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
135 RAISE OKL_API.G_EXCEPTION_ERROR;
136 END IF;
137
138 SELECT SYSDATE INTO l_sysdate FROM dual;
139
140 IF p_assets_tbl.COUNT > 0 THEN
141
142 okl_am_util_pvt.get_transaction_id(p_try_name => 'Asset Residual Change',
143 x_return_status => x_return_status,
144 x_try_id => l_try_id);
145
146
147 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
148 -- Unable to find a transaction type for this transaction
149 OKL_API.set_message(p_app_name => 'OKL',
150 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
151 p_token1 => 'TRY_NAME',
152 p_token1_value => 'Asset Residual Change');
153 RAISE OKC_API.G_EXCEPTION_ERROR;
154 END IF;
155
156
157
158
159 i := p_assets_tbl.FIRST;
160 -- loop thru the table of records receieved as input. For each record get the line item information from
161 -- cursor l_linesfullv_csr. Validate the input data and then create transaction header and transaction line records
162 -- in okl_trx_assets_v and okl_txl_assets_v
163 LOOP
164 IF p_assets_tbl(i).p_id IS NULL OR p_assets_tbl(i).p_id = OKL_API.G_MISS_NUM THEN
165 l_record_status := OKL_API.G_RET_STS_ERROR;
166 -- Line id is required
167 OKC_API.set_message( p_app_name => 'OKC',
168 p_msg_name => G_REQUIRED_VALUE,
169 p_token1 => G_COL_NAME_TOKEN,
170 p_token1_value => 'LINE_ID');
171 ELSE
172 OPEN l_linesfullv_csr(p_assets_tbl(i).p_id) ;
173 FETCH l_linesfullv_csr INTO l_name, l_description, l_old_residual_value, l_oec, l_chr_id, l_contract_number;
174 IF l_linesfullv_csr%NOTFOUND THEN
175 l_record_status := OKL_API.G_RET_STS_ERROR;
176 -- Asset number is invalid
177 OKC_API.set_message( p_app_name => 'OKC',
178 p_msg_name => G_INVALID_VALUE,
179 p_token1 => G_COL_NAME_TOKEN,
180 p_token1_value => 'ASSET_NUMBER');
181 ELSE
182 -- Begin -- Check the contract term to allow RV updates.Bug# 7014234
183 OPEN c_contract_date_csr(l_chr_id);
184 FETCH c_contract_date_csr INTO chr_rec;
185 CLOSE c_contract_date_csr;
186
187 IF NOT (l_sysdate BETWEEN chr_rec.start_date AND chr_rec.end_date) THEN
188 l_icx_date_format := NVL(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'),'DD-MON-RRRR');
189 OKL_API.SET_MESSAGE(G_APP_NAME,
190 'OKL_LLA_WRONG_TRX_DATE',
191 'START_DATE',
192 TO_CHAR(chr_rec.start_date,l_icx_date_format),
193 'END_DATE',
194 TO_CHAR(chr_rec.end_date,l_icx_date_format),
195 'ASSET_NUMBER',
196 l_name
197 );
198 RAISE OKL_API.G_EXCEPTION_ERROR;
199 END IF;
200 -- End -- Check the contract term to allow RV updates.Bug# 7014234
201
202 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Moved the following validation here in the beginning of the code
203 -- Check if a pending transaction already exists for this contract
204 OPEN l_assettrx_csr(l_chr_id);
205 FETCH l_assettrx_csr INTO l_count;
206 CLOSE l_assettrx_csr;
207
208 IF l_count > 0 THEN
209 l_record_status := OKL_API.G_RET_STS_ERROR;
210 -- Asset failed because a pending salvage value writedown transaction already exists for the financial asset
211 OKL_API.set_message(
212 p_app_name => 'OKL',
213 p_msg_name => 'OKL_AM_RVW_TRX_EXISTS',
214 p_token1 => 'ASSET_NUMBER',
215 p_token1_value => l_name,
216 p_token2 => 'CONTRACT_NUMBER',
217 p_token2_value => l_contract_number);
218 ELSE
219
220 -- Check if an accepted termination quote exists for this line
221 OPEN l_quotes_csr(p_assets_tbl(i).p_id);
222 FETCH l_quotes_csr INTO l_name;
223 IF l_quotes_csr%FOUND THEN
224 l_record_status := OKL_API.G_RET_STS_ERROR;
225 -- Can not change Residual value for asset ASSET_NUMBER as an accepted termination quote exists for this asset.
226 OKL_API.set_message( p_app_name => 'OKL',
227 p_msg_name => 'OKL_AM_RVW_NOT_ALLOWED',
228 p_token1 => 'ASSET_NUMBER',
229 p_token1_value => l_name);
230
231 ELSIF p_assets_tbl(i).p_new_residual_value IS NULL OR p_assets_tbl(i).p_new_residual_value = OKL_API.G_MISS_NUM THEN
232
233 l_record_status := OKL_API.G_RET_STS_ERROR;
234
235 -- Asset failed because the new Residual Value is missing
236 OKL_API.set_message( p_app_name => 'OKL',
237 p_msg_name => 'OKL_AM_NO_NEW_RESIDUAL_VALUE',
238 p_token1 => 'ASSET_NUMBER',
239 p_token1_value => l_name);
240
241 ELSIF p_assets_tbl(i).p_new_residual_value < 0 THEN
242
243 l_record_status := OKL_API.G_RET_STS_ERROR;
244 -- Asset failed because the new Residual Value is negative
245 OKL_API.set_message( p_app_name => 'OKL',
246 p_msg_name => 'OKL_AM_NEGATIVE_RESIDUAL_VALUE',
247 p_token1 => 'ASSET_NUMBER',
248 p_token1_value => l_name);
249
250 ELSIF l_old_residual_value IS NULL THEN
251 l_record_status := OKL_API.G_RET_STS_ERROR;
252 -- Asset failed because the old Residual Value is missing
253 OKL_API.set_message( p_app_name => 'OKL',
254 p_msg_name => 'OKL_AM_NO_OLD_RESIDUAL_VALUE',
255 p_token1 => 'ASSET_NUMBER',
256 p_token1_value => l_name);
257
258 ELSIF p_assets_tbl(i).p_new_residual_value < l_old_residual_value THEN
259
260 -- RRAVIKIR Legal Entity Changes
261 OPEN l_oklheaders_csr(cp_khr_id => l_chr_id);
262 FETCH l_oklheaders_csr into l_legal_entity_id;
263 CLOSE l_oklheaders_csr;
264
265 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
266 l_record_status := OKL_API.G_RET_STS_ERROR;
267 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
268 p_msg_name => g_required_value,
269 p_token1 => g_col_name_token,
270 p_token1_value => 'legal_entity_id');
271 RAISE OKC_API.G_EXCEPTION_ERROR;
272 ELSE
273 lp_thpv_rec.legal_entity_id := l_legal_entity_id;
274 -- create transaction header
275 lp_thpv_rec.tas_type := 'ARC';
276 lp_thpv_rec.tsu_code := 'ENTERED';
277 lp_thpv_rec.try_id := l_try_id;
278 lp_thpv_rec.date_trans_occurred := l_sysdate;
279 END IF;
280
281 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def(
282 p_api_version => p_api_version,
283 p_init_msg_list => OKL_API.G_FALSE,
284 x_return_status => l_record_status,
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data,
287 p_thpv_rec => lp_thpv_rec,
288 x_thpv_rec => lx_thpv_rec);
289
290
291
292 IF l_record_status = OKL_API.G_RET_STS_SUCCESS THEN
293
294 -- SECHAWLA Bug # 2726739 : Added the following piece of code
295
296 -- get the functional currency
297 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
298
299 -- get the contract currency
300 l_contract_curr_code := okl_am_util_pvt.get_chr_currency( p_chr_id => l_chr_id);
301
302 lp_tlpv_rec.currency_code := l_contract_curr_code;
303 -- lp_tlpv_rec.currency_conversion_code := l_func_curr_code;
304
305 IF l_contract_curr_code <> l_func_curr_code THEN
306 -- get the conversion factors from accounting util. No conversion is required here. We use
307 -- convert_to_functional_currency procedure just to get the conversion factors
308
309 okl_accounting_util.convert_to_functional_currency(
310 p_khr_id => l_chr_id,
311 p_to_currency => l_func_curr_code,
312 p_transaction_date => l_sysdate ,
313 p_amount => p_assets_tbl(i).p_new_residual_value,
314 x_return_status => x_return_status,
315 x_contract_currency => lx_contract_currency,
316 x_currency_conversion_type => lx_currency_conversion_type,
317 x_currency_conversion_rate => lx_currency_conversion_rate,
318 x_currency_conversion_date => lx_currency_conversion_date,
319 x_converted_amount => lx_converted_amount );
320
321 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
322 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
323 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
324 RAISE OKL_API.G_EXCEPTION_ERROR;
325 END IF;
326
327 lp_tlpv_rec.currency_conversion_type := lx_currency_conversion_type;
328 lp_tlpv_rec.currency_conversion_rate := lx_currency_conversion_rate;
329 lp_tlpv_rec.currency_conversion_date := lx_currency_conversion_date;
330 END IF;
331 --- SECHAWLA Bug # 2726739 : end new code -----
332
333
334 -- Create transaction Line
335 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
336 lp_tlpv_rec.kle_id := p_assets_tbl(i).p_id;
337 lp_tlpv_rec.line_number := 1;
338 lp_tlpv_rec.tal_type := 'ADL';
339 lp_tlpv_rec.asset_number := l_name;
340 lp_tlpv_rec.description := l_description;
341 lp_tlpv_rec.old_residual_value := l_old_residual_value;
342 lp_tlpv_rec.new_residual_value := p_assets_tbl(i).p_new_residual_value;
343 lp_tlpv_rec.original_cost := l_oec;
344 lp_tlpv_rec.current_units := 1;
345 --lp_tlpv_rec.dnz_asset_id := l_asset_id;
346 lp_tlpv_rec.dnz_khr_id := l_chr_id;
347
348 OKL_TXL_ASSETS_PUB.create_txl_asset_def(
349 p_api_version => p_api_version,
350 p_init_msg_list => OKL_API.G_FALSE,
351 x_return_status => l_record_status,
352 x_msg_count => x_msg_count,
353 x_msg_data => x_msg_data,
354 p_tlpv_rec => lp_tlpv_rec,
355 x_tlpv_rec => lx_tlpv_rec);
356 END IF;
357
358
359 ELSIF p_assets_tbl(i).p_new_residual_value = l_old_residual_value THEN
360 l_record_status := OKL_API.G_RET_STS_ERROR;
361 -- Asset failed because the new Residual Value is same as the old value.
362 OKL_API.set_message( p_app_name => 'OKL',
363 p_msg_name => 'OKL_AM_SAME_RESIDUAL_VALUE',
364 p_token1 => 'ASSET_NUMBER',
365 p_token1_value => l_name);
366
367 ELSE -- new residual < old residual
368
369 l_record_status := OKL_API.G_RET_STS_ERROR;
370 -- Asset failed because the new Residual Value is not lower than the old value.
371 OKL_API.set_message( p_app_name => 'OKL',
372 p_msg_name => 'OKL_AM_INVALID_RESIDUAL_VALUE',
373 p_token1 => 'ASSET_NUMBER',
374 p_token1_value => l_name);
375
376
377
378 END IF;
379 CLOSE l_quotes_csr;
380
381 END IF;
382
383 END IF; -- end fetch
384 CLOSE l_linesfullv_csr;
385
386 END IF;
387 -- If it reaches this point for the current record, that means x_return_status is SUCCESS
388 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
389 IF l_record_status <> OKL_API.G_RET_STS_SUCCESS THEN
390 l_overall_status := OKL_API.G_RET_STS_ERROR;
391 END IF;
392 END IF;
393
394
395 EXIT WHEN (i = p_assets_tbl.LAST);
396 i := p_assets_tbl.NEXT(i);
397 END LOOP;
398 x_return_status := l_overall_status;
399 END IF; -- p_assets_tbl.COUNT > 0
400
401
402 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
403
404 EXCEPTION
405 WHEN OKL_API.G_EXCEPTION_ERROR THEN
406 IF l_linesfullv_csr%ISOPEN THEN
407 CLOSE l_linesfullv_csr;
408 END IF;
409 IF l_assettrx_csr%ISOPEN THEN
410 CLOSE l_assettrx_csr;
411 END IF;
412 IF l_quotes_csr%ISOPEN THEN
413 CLOSE l_quotes_csr;
414 END IF;
415
416 x_return_status := OKL_API.HANDLE_EXCEPTIONS
417 (
418 l_api_name,
419 G_PKG_NAME,
420 'OKL_API.G_RET_STS_ERROR',
421 x_msg_count,
422 x_msg_data,
423 '_PVT'
424 );
425 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
426 IF l_linesfullv_csr%ISOPEN THEN
427 CLOSE l_linesfullv_csr;
428 END IF;
429 IF l_assettrx_csr%ISOPEN THEN
430 CLOSE l_assettrx_csr;
431 END IF;
432 IF l_quotes_csr%ISOPEN THEN
433 CLOSE l_quotes_csr;
434 END IF;
435 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
436 (
437 l_api_name,
438 G_PKG_NAME,
439 'OKL_API.G_RET_STS_UNEXP_ERROR',
440 x_msg_count,
441 x_msg_data,
442 '_PVT'
443 );
444 WHEN OTHERS THEN
445 IF l_linesfullv_csr%ISOPEN THEN
446 CLOSE l_linesfullv_csr;
447 END IF;
448 IF l_assettrx_csr%ISOPEN THEN
449 CLOSE l_assettrx_csr;
450 END IF;
451 IF l_quotes_csr%ISOPEN THEN
452 CLOSE l_quotes_csr;
453 END IF;
454 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
455 (
456 l_api_name,
457 G_PKG_NAME,
458 'OTHERS',
459 x_msg_count,
460 x_msg_data,
461 '_PVT'
462 );
463 END create_residual_value_trx;
464 END OKL_AM_RV_WRITEDOWN_PVT;
[Home] [Help]