1 PACKAGE BODY OKL_AM_RV_WRITEDOWN_PVT AS
2 /* $Header: OKLRRVWB.pls 120.4 2006/11/22 18:44:08 rravikir noship $ */
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 CURSOR l_assettrx_csr(p_khr_id NUMBER) IS
82 SELECT count(*)
83 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l
84 WHERE h.id = l.tas_id
85 AND h.tsu_code = 'ENTERED'
86 AND h.tas_type = 'ARC'
87 AND l.dnz_khr_id = p_khr_id;
88
89 -- This cursor is used to check if an accepted termination quote exists for an asset line.
90 CURSOR l_quotes_csr(p_kle_id NUMBER) IS
91 SELECT l.name
92 FROM okl_trx_quotes_b qh, okl_txl_quote_lines_b ql, okl_k_lines_full_v l
93 WHERE qh.id = ql.qte_id
94 AND qh.qst_code = 'ACCEPTED'
95 AND ql.qlt_code = 'AMCFIA'
96 AND ql.kle_id = l.id
97 AND ql.kle_id = p_kle_id;
98
99 -- RRAVIKIR Legal Entity Changes
100 CURSOR l_oklheaders_csr(cp_khr_id NUMBER) IS
101 SELECT legal_entity_id
102 FROM okl_k_headers
103 WHERE id = cp_khr_id;
104
105 l_legal_entity_id NUMBER;
106 -- Legal Entity Changes End
107
108 BEGIN
109
110 l_record_status := OKL_API.G_RET_STS_SUCCESS;
111
112 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
113 G_PKG_NAME,
114 p_init_msg_list,
115 l_api_version,
116 p_api_version,
117 '_PVT',
118 x_return_status);
119
120 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
121 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
122 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
123 RAISE OKL_API.G_EXCEPTION_ERROR;
124 END IF;
125
126 SELECT SYSDATE INTO l_sysdate FROM dual;
127
128 IF p_assets_tbl.COUNT > 0 THEN
129
130 okl_am_util_pvt.get_transaction_id(p_try_name => 'Asset Residual Change',
131 x_return_status => x_return_status,
132 x_try_id => l_try_id);
133
134
135 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
136 -- Unable to find a transaction type for this transaction
137 OKL_API.set_message(p_app_name => 'OKL',
138 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
139 p_token1 => 'TRY_NAME',
140 p_token1_value => 'Asset Residual Change');
141 RAISE OKC_API.G_EXCEPTION_ERROR;
142 END IF;
143
144
145
146
147 i := p_assets_tbl.FIRST;
148 -- loop thru the table of records receieved as input. For each record get the line item information from
149 -- cursor l_linesfullv_csr. Validate the input data and then create transaction header and transaction line records
150 -- in okl_trx_assets_v and okl_txl_assets_v
151 LOOP
152 IF p_assets_tbl(i).p_id IS NULL OR p_assets_tbl(i).p_id = OKL_API.G_MISS_NUM THEN
153 l_record_status := OKL_API.G_RET_STS_ERROR;
154 -- Line id is required
155 OKC_API.set_message( p_app_name => 'OKC',
156 p_msg_name => G_REQUIRED_VALUE,
157 p_token1 => G_COL_NAME_TOKEN,
158 p_token1_value => 'LINE_ID');
159 ELSE
160 OPEN l_linesfullv_csr(p_assets_tbl(i).p_id) ;
161 FETCH l_linesfullv_csr INTO l_name, l_description, l_old_residual_value, l_oec, l_chr_id, l_contract_number;
162 IF l_linesfullv_csr%NOTFOUND THEN
163 l_record_status := OKL_API.G_RET_STS_ERROR;
164 -- Asset number is invalid
165 OKC_API.set_message( p_app_name => 'OKC',
166 p_msg_name => G_INVALID_VALUE,
167 p_token1 => G_COL_NAME_TOKEN,
168 p_token1_value => 'ASSET_NUMBER');
169 ELSE
170 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Moved the following validation here in the beginning of the code
171 -- Check if a pending transaction already exists for this contract
172 OPEN l_assettrx_csr(l_chr_id);
173 FETCH l_assettrx_csr INTO l_count;
174 CLOSE l_assettrx_csr;
175
176 IF l_count > 0 THEN
177 l_record_status := OKL_API.G_RET_STS_ERROR;
178 -- Asset failed because a pending salvage value writedown transaction already exists for the financial asset
179 OKL_API.set_message(
180 p_app_name => 'OKL',
181 p_msg_name => 'OKL_AM_RVW_TRX_EXISTS',
182 p_token1 => 'ASSET_NUMBER',
183 p_token1_value => l_name,
184 p_token2 => 'CONTRACT_NUMBER',
185 p_token2_value => l_contract_number);
186 ELSE
187
188 -- Check if an accepted termination quote exists for this line
189 OPEN l_quotes_csr(p_assets_tbl(i).p_id);
190 FETCH l_quotes_csr INTO l_name;
191 IF l_quotes_csr%FOUND THEN
192 l_record_status := OKL_API.G_RET_STS_ERROR;
193 -- Can not change Residual value for asset ASSET_NUMBER as an accepted termination quote exists for this asset.
194 OKL_API.set_message( p_app_name => 'OKL',
195 p_msg_name => 'OKL_AM_RVW_NOT_ALLOWED',
196 p_token1 => 'ASSET_NUMBER',
197 p_token1_value => l_name);
198
199 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
200
201 l_record_status := OKL_API.G_RET_STS_ERROR;
202
203 -- Asset failed because the new Residual Value is missing
204 OKL_API.set_message( p_app_name => 'OKL',
205 p_msg_name => 'OKL_AM_NO_NEW_RESIDUAL_VALUE',
206 p_token1 => 'ASSET_NUMBER',
207 p_token1_value => l_name);
208
209 ELSIF p_assets_tbl(i).p_new_residual_value < 0 THEN
210
211 l_record_status := OKL_API.G_RET_STS_ERROR;
212 -- Asset failed because the new Residual Value is negative
213 OKL_API.set_message( p_app_name => 'OKL',
214 p_msg_name => 'OKL_AM_NEGATIVE_RESIDUAL_VALUE',
215 p_token1 => 'ASSET_NUMBER',
216 p_token1_value => l_name);
217
218 ELSIF l_old_residual_value IS NULL THEN
219 l_record_status := OKL_API.G_RET_STS_ERROR;
220 -- Asset failed because the old Residual Value is missing
221 OKL_API.set_message( p_app_name => 'OKL',
222 p_msg_name => 'OKL_AM_NO_OLD_RESIDUAL_VALUE',
223 p_token1 => 'ASSET_NUMBER',
224 p_token1_value => l_name);
225
226 ELSIF p_assets_tbl(i).p_new_residual_value < l_old_residual_value THEN
227
228 -- RRAVIKIR Legal Entity Changes
229 OPEN l_oklheaders_csr(cp_khr_id => l_chr_id);
230 FETCH l_oklheaders_csr into l_legal_entity_id;
231 CLOSE l_oklheaders_csr;
232
233 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
234 l_record_status := OKL_API.G_RET_STS_ERROR;
235 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
236 p_msg_name => g_required_value,
237 p_token1 => g_col_name_token,
238 p_token1_value => 'legal_entity_id');
239 RAISE OKC_API.G_EXCEPTION_ERROR;
240 ELSE
241 lp_thpv_rec.legal_entity_id := l_legal_entity_id;
242 -- create transaction header
243 lp_thpv_rec.tas_type := 'ARC';
244 lp_thpv_rec.tsu_code := 'ENTERED';
245 lp_thpv_rec.try_id := l_try_id;
246 lp_thpv_rec.date_trans_occurred := l_sysdate;
247 END IF;
248
249 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def(
250 p_api_version => p_api_version,
251 p_init_msg_list => OKL_API.G_FALSE,
252 x_return_status => l_record_status,
253 x_msg_count => x_msg_count,
254 x_msg_data => x_msg_data,
255 p_thpv_rec => lp_thpv_rec,
256 x_thpv_rec => lx_thpv_rec);
257
258
259
260 IF l_record_status = OKL_API.G_RET_STS_SUCCESS THEN
261
262 -- SECHAWLA Bug # 2726739 : Added the following piece of code
263
264 -- get the functional currency
265 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
266
267 -- get the contract currency
268 l_contract_curr_code := okl_am_util_pvt.get_chr_currency( p_chr_id => l_chr_id);
269
270 lp_tlpv_rec.currency_code := l_contract_curr_code;
271 -- lp_tlpv_rec.currency_conversion_code := l_func_curr_code;
272
273 IF l_contract_curr_code <> l_func_curr_code THEN
274 -- get the conversion factors from accounting util. No conversion is required here. We use
275 -- convert_to_functional_currency procedure just to get the conversion factors
276
277 okl_accounting_util.convert_to_functional_currency(
278 p_khr_id => l_chr_id,
279 p_to_currency => l_func_curr_code,
280 p_transaction_date => l_sysdate ,
281 p_amount => p_assets_tbl(i).p_new_residual_value,
282 x_return_status => x_return_status,
283 x_contract_currency => lx_contract_currency,
284 x_currency_conversion_type => lx_currency_conversion_type,
285 x_currency_conversion_rate => lx_currency_conversion_rate,
286 x_currency_conversion_date => lx_currency_conversion_date,
287 x_converted_amount => lx_converted_amount );
288
289 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
290 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
292 RAISE OKL_API.G_EXCEPTION_ERROR;
293 END IF;
294
295 lp_tlpv_rec.currency_conversion_type := lx_currency_conversion_type;
296 lp_tlpv_rec.currency_conversion_rate := lx_currency_conversion_rate;
297 lp_tlpv_rec.currency_conversion_date := lx_currency_conversion_date;
298 END IF;
299 --- SECHAWLA Bug # 2726739 : end new code -----
300
301
302 -- Create transaction Line
303 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
304 lp_tlpv_rec.kle_id := p_assets_tbl(i).p_id;
305 lp_tlpv_rec.line_number := 1;
306 lp_tlpv_rec.tal_type := 'ADL';
307 lp_tlpv_rec.asset_number := l_name;
308 lp_tlpv_rec.description := l_description;
309 lp_tlpv_rec.old_residual_value := l_old_residual_value;
310 lp_tlpv_rec.new_residual_value := p_assets_tbl(i).p_new_residual_value;
311 lp_tlpv_rec.original_cost := l_oec;
312 lp_tlpv_rec.current_units := 1;
313 --lp_tlpv_rec.dnz_asset_id := l_asset_id;
314 lp_tlpv_rec.dnz_khr_id := l_chr_id;
315
316 OKL_TXL_ASSETS_PUB.create_txl_asset_def(
317 p_api_version => p_api_version,
318 p_init_msg_list => OKL_API.G_FALSE,
319 x_return_status => l_record_status,
320 x_msg_count => x_msg_count,
321 x_msg_data => x_msg_data,
322 p_tlpv_rec => lp_tlpv_rec,
323 x_tlpv_rec => lx_tlpv_rec);
324 END IF;
325
326
327 ELSIF p_assets_tbl(i).p_new_residual_value = l_old_residual_value THEN
328 l_record_status := OKL_API.G_RET_STS_ERROR;
329 -- Asset failed because the new Residual Value is same as the old value.
330 OKL_API.set_message( p_app_name => 'OKL',
331 p_msg_name => 'OKL_AM_SAME_RESIDUAL_VALUE',
332 p_token1 => 'ASSET_NUMBER',
333 p_token1_value => l_name);
334
335 ELSE -- new residual < old residual
336
337 l_record_status := OKL_API.G_RET_STS_ERROR;
338 -- Asset failed because the new Residual Value is not lower than the old value.
339 OKL_API.set_message( p_app_name => 'OKL',
340 p_msg_name => 'OKL_AM_INVALID_RESIDUAL_VALUE',
341 p_token1 => 'ASSET_NUMBER',
342 p_token1_value => l_name);
343
344
345
346 END IF;
347 CLOSE l_quotes_csr;
348
349 END IF;
350
351 END IF; -- end fetch
352 CLOSE l_linesfullv_csr;
353
354 END IF;
355 -- If it reaches this point for the current record, that means x_return_status is SUCCESS
356 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
357 IF l_record_status <> OKL_API.G_RET_STS_SUCCESS THEN
358 l_overall_status := OKL_API.G_RET_STS_ERROR;
359 END IF;
360 END IF;
361
362
363 EXIT WHEN (i = p_assets_tbl.LAST);
364 i := p_assets_tbl.NEXT(i);
365 END LOOP;
366 x_return_status := l_overall_status;
367 END IF; -- p_assets_tbl.COUNT > 0
368
369
370 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
371
372 EXCEPTION
373 WHEN OKL_API.G_EXCEPTION_ERROR THEN
374 IF l_linesfullv_csr%ISOPEN THEN
375 CLOSE l_linesfullv_csr;
376 END IF;
377 IF l_assettrx_csr%ISOPEN THEN
378 CLOSE l_assettrx_csr;
379 END IF;
380 IF l_quotes_csr%ISOPEN THEN
381 CLOSE l_quotes_csr;
382 END IF;
383
384 x_return_status := OKL_API.HANDLE_EXCEPTIONS
385 (
386 l_api_name,
387 G_PKG_NAME,
388 'OKL_API.G_RET_STS_ERROR',
389 x_msg_count,
390 x_msg_data,
391 '_PVT'
392 );
393 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
394 IF l_linesfullv_csr%ISOPEN THEN
395 CLOSE l_linesfullv_csr;
396 END IF;
397 IF l_assettrx_csr%ISOPEN THEN
398 CLOSE l_assettrx_csr;
399 END IF;
400 IF l_quotes_csr%ISOPEN THEN
401 CLOSE l_quotes_csr;
402 END IF;
403 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
404 (
405 l_api_name,
406 G_PKG_NAME,
407 'OKL_API.G_RET_STS_UNEXP_ERROR',
408 x_msg_count,
409 x_msg_data,
410 '_PVT'
411 );
412 WHEN OTHERS THEN
413 IF l_linesfullv_csr%ISOPEN THEN
414 CLOSE l_linesfullv_csr;
415 END IF;
416 IF l_assettrx_csr%ISOPEN THEN
417 CLOSE l_assettrx_csr;
418 END IF;
419 IF l_quotes_csr%ISOPEN THEN
420 CLOSE l_quotes_csr;
421 END IF;
422 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
423 (
424 l_api_name,
425 G_PKG_NAME,
426 'OTHERS',
427 x_msg_count,
428 x_msg_data,
429 '_PVT'
430 );
431 END create_residual_value_trx;
432 END OKL_AM_RV_WRITEDOWN_PVT;
[Home] [Help]