1 PACKAGE BODY OKL_AM_SV_WRITEDOWN_PVT AS
2 /* $Header: OKLRSVWB.pls 120.4 2006/11/22 18:45:11 rravikir noship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : create_salvage_value_trx
7 -- Description : The main body of the package. This procedure gets a table of line ids along with the new
8 -- salvage values as parameter. It then validates the new SV to make sure that it is less than
9 -- the current SV and then creates salvage value transactions in OKL_TRX_ASSETS_V and
10 -- OKL_TXL_ASSETS_V
11 -- Business Rules :
12 -- Parameters : p_assets_tbl
13 -- Version : 1.0
14 -- History : SECHAWLA 03-JAN-03 2683876
15 -- Added logic to populate currency code while creating/updating amounts in txl assets
16 -- SECHAWLA 07-FEB-03 2789656
17 -- Changed the sequence of validations so that the validation to check for a pending SVW
18 -- transaction is done before all other validations.
19 -- End of comments
20
21
22 PROCEDURE create_salvage_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_salvage_value_status OUT NOCOPY VARCHAR2) IS
29
30 SUBTYPE thpv_rec_type IS okl_trx_assets_pub.thpv_rec_type;
31 SUBTYPE tlpv_rec_type IS okl_txl_assets_pub.tlpv_rec_type;
32
33
34 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
35 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
36 l_record_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
37
38 l_api_name CONSTANT VARCHAR2(30) := 'create_salvage_value_trx';
39 l_api_version CONSTANT NUMBER := 1;
40
41 i NUMBER := 0;
42 l_asset_id NUMBER;
43 l_asset_number VARCHAR2(15);
44 l_description VARCHAR2(1995);
45 l_old_salvage_value NUMBER;
46 l_original_cost NUMBER;
47 l_current_units NUMBER;
48 l_corporate_book VARCHAR2(70);
49 l_dnz_chr_id NUMBER;
50 l_try_id okl_trx_types_v.id%TYPE;
51 lp_thpv_rec thpv_rec_type;
52 lx_thpv_rec thpv_rec_type;
53 lp_tlpv_rec tlpv_rec_type;
54 lx_tlpv_rec tlpv_rec_type;
55 l_sysdate DATE;
56 l_contract_number VARCHAR2(120);
57 l_count NUMBER;
58
59 --SECHAWLA 03-JAN-03 Bug # 2683876 : new declaration
60 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
61
62 -- This cursor fetches the asset lines from okx_asset_lines_v corresponding to the parent_line_id passed as
63 -- input parameter
64 CURSOR l_assetlinesv_csr(p_cle_id NUMBER) IS
65 SELECT to_number(l.asset_id) , l.asset_number, l.item_description, l.salvage_value, l.original_cost, l.current_units,
66 l.corporate_book, l.dnz_chr_id, h.contract_number
67 FROM okx_asset_lines_v l, okc_k_headers_b h
68 WHERE l.dnz_chr_id = h.id
69 AND l.parent_line_id = p_cle_id;
70
71
72 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Changed the cursor to select asset_number instead of count(*)
73 -- This cursor is used to check if a pending salvage value writedown transaction already exists for a financial asset.
74 CURSOR l_assettrx_csr(p_kle_id NUMBER) IS
75 SELECT l.asset_number
76 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l
77 WHERE h.id = l.tas_id
78 AND h.tsu_code = 'ENTERED'
79 AND h.tas_type = 'FSC'
80 AND l.kle_id = p_kle_id;
81
82 -- This cursor is used to check if an accepted termination quote exists for an asset line.
83 CURSOR l_quotes_csr(p_kle_id NUMBER) IS
84 SELECT l.asset_number
85 FROM okl_trx_quotes_b qh, okl_txl_quote_lines_b ql, okx_asset_lines_v l
86 WHERE qh.id = ql.qte_id
87 AND qh.qst_code = 'ACCEPTED'
88 AND ql.qlt_code = 'AMCFIA'
89 AND ql.kle_id = l.parent_line_id
90 AND ql.kle_id = p_kle_id;
91
92 -- RRAVIKIR Legal Entity Changes
93 CURSOR l_oklheaders_csr(cp_khr_id NUMBER) IS
94 SELECT legal_entity_id
95 FROM okl_k_headers
96 WHERE id = cp_khr_id;
97
98 l_legal_entity_id NUMBER;
99 -- Legal Entity Changes End
100
101 BEGIN
102
103 l_record_status := OKL_API.G_RET_STS_SUCCESS;
104
105
106 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
107 G_PKG_NAME,
108 p_init_msg_list,
109 l_api_version,
110 p_api_version,
111 '_PVT',
112 x_return_status);
113
114
115 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
116 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
117 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
118 RAISE OKL_API.G_EXCEPTION_ERROR;
119 END IF;
120
121 SELECT SYSDATE INTO l_sysdate FROM dual;
122
123 IF p_assets_tbl.COUNT > 0 THEN
124
125
126 okl_am_util_pvt.get_transaction_id(p_try_name => 'Fixed Asset Salvage Change',
127 x_return_status => x_return_status,
128 x_try_id => l_try_id);
129
130 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
131 -- Unable to find a transaction type for this transaction
132 OKL_API.set_message(p_app_name => 'OKL',
133 p_msg_name => 'OKL_AM_NO_TRX_TYPE_FOUND',
134 p_token1 => 'TRY_NAME',
135 p_token1_value => 'Fixed Asset Salvage Change');
136 RAISE OKC_API.G_EXCEPTION_ERROR;
137 END IF;
138
139
140
141
142
143 i := p_assets_tbl.FIRST;
144 -- fetch the asset line information from okx_asset_lines_v for each cle_id in the input table,
145 -- validate the old salvage value(in the view), new salvage value (in the input table) and then
146 -- create salvage value writedown transactions (FSC/FSL) in OKL tables.
147 LOOP
148
149
150 IF p_assets_tbl(i).p_cle_id IS NULL OR p_assets_tbl(i).p_cle_id = OKL_API.G_MISS_NUM THEN
151 l_record_status := OKL_API.G_RET_STS_ERROR;
152 -- cle_id is required
153 OKC_API.set_message( p_app_name => 'OKC',
154 p_msg_name => G_REQUIRED_VALUE,
155 p_token1 => G_COL_NAME_TOKEN,
156 p_token1_value => 'CLE_ID');
157 ELSE
158 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Moved the following validation here in the beginning of the code
159 -- Check if a pending transaction already exists for this financial asset
160 OPEN l_assettrx_csr(p_assets_tbl(i).p_cle_id);
161 FETCH l_assettrx_csr INTO l_asset_number;
162 IF l_assettrx_csr%FOUND THEN
163 l_record_status := OKL_API.G_RET_STS_ERROR;
164 -- Asset failed because a pending salvage value writedown transaction already exists for the financial asset
165 OKL_API.set_message(
166 p_app_name => 'OKL',
167 p_msg_name => 'OKL_AM_SVW_TRX_EXISTS',
168 p_token1 => 'ASSET_NUMBER',
169 p_token1_value => l_asset_number);
170 ELSE
171
172
173 -- Check if an accepted termination quote exists for this line
174 OPEN l_quotes_csr(p_assets_tbl(i).p_cle_id);
175 FETCH l_quotes_csr INTO l_asset_number;
176 IF l_quotes_csr%FOUND THEN
177 l_record_status := OKL_API.G_RET_STS_ERROR;
178 -- Can not change Salvage value for asset ASSET_NUMBER as an accepted termination quote exists for this asset.
179 OKL_API.set_message( p_app_name => 'OKL',
180 p_msg_name => 'OKL_AM_SVW_NOT_ALLOWED',
181 p_token1 => 'ASSET_NUMBER',
182 p_token1_value => l_asset_number);
183 ELSE
184
185 OPEN l_assetlinesv_csr(p_assets_tbl(i).p_cle_id) ;
186 FETCH l_assetlinesv_csr INTO l_asset_id, l_asset_number, l_description, l_old_salvage_value,
187 l_original_cost, l_current_units, l_corporate_book, l_dnz_chr_id, l_contract_number;
188
189
190 IF l_assetlinesv_csr%NOTFOUND THEN
191
192 l_record_status := OKL_API.G_RET_STS_ERROR;
193
194 -- asset_number is invalid
195 OKC_API.set_message( p_app_name => 'OKC',
196 p_msg_name => G_INVALID_VALUE,
197 p_token1 => G_COL_NAME_TOKEN,
198 p_token1_value => 'ASSET_NUMBER');
199
200
201
202
203 ELSIF p_assets_tbl(i).p_new_salvage_value IS NULL OR p_assets_tbl(i).p_new_salvage_value = OKL_API.G_MISS_NUM THEN
204
205 l_record_status := OKL_API.G_RET_STS_ERROR;
206 --Asset failed because the new Salvage Value is missing
207 OKL_API.set_message( p_app_name => 'OKL',
208 p_msg_name => 'OKL_AM_NO_NEW_SALVAGE_VALUE',
209 p_token1 => 'ASSET_NUMBER',
210 p_token1_value => l_asset_number);
211
212 ELSIF p_assets_tbl(i).p_new_salvage_value < 0 THEN
213
214 l_record_status := OKL_API.G_RET_STS_ERROR;
215 -- Asset failed because the new Salvage Value is negative
216 OKL_API.set_message( p_app_name => 'OKL',
217 p_msg_name => 'OKL_AM_NEGATIVE_SALVAGE_VALUE',
218 p_token1 => 'ASSET_NUMBER',
219 p_token1_value => l_asset_number);
220
221 ELSE
222
223
224
225 IF l_old_salvage_value IS NULL THEN
226 l_record_status := OKL_API.G_RET_STS_ERROR;
227 -- Asset failed because the old Salvage Value is missing
228 OKL_API.set_message( p_app_name => 'OKL',
229 p_msg_name => 'OKL_AM_NO_OLD_SALVAGE_VALUE',
230 p_token1 => 'ASSET_NUMBER',
231 p_token1_value => l_asset_number);
232
233 ELSE
234 IF p_assets_tbl(i).p_new_salvage_value < l_old_salvage_value THEN
235
236 -- RRAVIKIR Legal Entity Changes
237 OPEN l_oklheaders_csr(cp_khr_id => l_dnz_chr_id);
238 FETCH l_oklheaders_csr into l_legal_entity_id;
239 CLOSE l_oklheaders_csr;
240
241 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
242 l_record_status := OKL_API.G_RET_STS_ERROR;
243 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
244 p_msg_name => g_required_value,
245 p_token1 => g_col_name_token,
246 p_token1_value => 'legal_entity_id');
247 RAISE OKC_API.G_EXCEPTION_ERROR;
248 ELSE
249 lp_thpv_rec.legal_entity_id := l_legal_entity_id;
250 lp_thpv_rec.tas_type := 'FSC';
251 lp_thpv_rec.tsu_code := 'ENTERED';
252 lp_thpv_rec.try_id := l_try_id;
253 lp_thpv_rec.date_trans_occurred := l_sysdate;
254 END IF;
255 -- Legal Entity Changes End
256
257 -- create transaction header
258 OKL_TRX_ASSETS_PUB.create_trx_ass_h_def(
259 p_api_version => p_api_version,
260 p_init_msg_list => OKL_API.G_FALSE,
261 x_return_status => l_record_status,
262 x_msg_count => x_msg_count,
263 x_msg_data => x_msg_data,
264 p_thpv_rec => lp_thpv_rec,
265 x_thpv_rec => lx_thpv_rec);
266
267 IF l_record_status = OKL_API.G_RET_STS_SUCCESS THEN
268
269 --SECHAWLA 03-JAN-03 2683876 Pass the currency code if creating/updating amounts in txl assets
270 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
271 lp_tlpv_rec.currency_code := l_func_curr_code;
272
273 -- Create transaction Line
274 lp_tlpv_rec.tas_id := lx_thpv_rec.id; -- FK
275 lp_tlpv_rec.kle_id := p_assets_tbl(i).p_cle_id;
276 lp_tlpv_rec.line_number := 1;
277 lp_tlpv_rec.tal_type := 'FSL';
278 lp_tlpv_rec.asset_number := l_asset_number;
279 lp_tlpv_rec.description := l_description;
280 lp_tlpv_rec.old_salvage_value := l_old_salvage_value;
281 lp_tlpv_rec.salvage_value := p_assets_tbl(i).p_new_salvage_value;
282 lp_tlpv_rec.corporate_book := l_corporate_book;
283 lp_tlpv_rec.original_cost := l_original_cost;
284 lp_tlpv_rec.current_units := l_current_units;
285 lp_tlpv_rec.dnz_asset_id := l_asset_id;
286 lp_tlpv_rec.dnz_khr_id := l_dnz_chr_id;
287
288 OKL_TXL_ASSETS_PUB.create_txl_asset_def(
289 p_api_version => p_api_version,
290 p_init_msg_list => OKL_API.G_FALSE,
291 x_return_status => l_record_status,
292 x_msg_count => x_msg_count,
293 x_msg_data => x_msg_data,
294 p_tlpv_rec => lp_tlpv_rec,
295 x_tlpv_rec => lx_tlpv_rec);
296 END IF;
297
298 ELSIF p_assets_tbl(i).p_new_salvage_value = l_old_salvage_value THEN
299 l_record_status := OKL_API.G_RET_STS_ERROR;
300 -- Asset failed because the new Salvage Value is not lower than the old value.
301 OKL_API.set_message( p_app_name => 'OKL',
302 p_msg_name => 'OKL_AM_SAME_SALVAGE_VALUE',
303 p_token1 => 'ASSET_NUMBER',
304 p_token1_value => l_asset_number);
305 ELSE -- if new sv > old sv
306
307 l_record_status := OKL_API.G_RET_STS_ERROR;
308 -- Asset failed because the new Salvage Value is not lower than the old value.
309 OKL_API.set_message(
310 p_app_name => 'OKL',
311 p_msg_name => 'OKL_AM_INVALID_SALVAGE_VALUE',
312 p_token1 => 'ASSET_NUMBER',
313 p_token1_value => l_asset_number);
314
315
316
317
318 END IF;
319
320
321 END IF ; --l_old_salvage_value is null
322 END IF; -- end fetch
323 CLOSE l_assetlinesv_csr;
324 END IF;
325 CLOSE l_quotes_csr;
326 END IF;
327 CLOSE l_assettrx_csr;
328
329 END IF;
330 -- If it reaches this point for the current record, that means x_return_status is SUCCESS
331 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
332 IF l_record_status <> OKL_API.G_RET_STS_SUCCESS THEN
333 l_overall_status := OKL_API.G_RET_STS_ERROR;
334 END IF;
335 END IF;
336
337
338 EXIT WHEN (i = p_assets_tbl.LAST);
339 i := p_assets_tbl.NEXT(i);
340
341
342 END LOOP;
343
344 x_return_status := l_overall_status;
345
346 END IF;
347
348
349 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
350
351 EXCEPTION
352 WHEN OKL_API.G_EXCEPTION_ERROR THEN
353
354
355 IF l_assetlinesv_csr%ISOPEN THEN
356 CLOSE l_assetlinesv_csr;
357 END IF;
358
359 IF l_quotes_csr%ISOPEN THEN
360 CLOSE l_quotes_csr;
361 END IF;
362
363 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Close the cursor
364 IF l_assettrx_csr%ISOPEN THEN
365 CLOSE l_assettrx_csr;
366 END IF;
367
368 -- RRAVIKIR Legal Entity Changes
369 IF l_oklheaders_csr%ISOPEN THEN
370 CLOSE l_oklheaders_csr;
371 END IF;
372 -- Legal Entity Changes End
373
374 x_return_status := OKL_API.HANDLE_EXCEPTIONS
375 (
376 l_api_name,
377 G_PKG_NAME,
378 'OKL_API.G_RET_STS_ERROR',
379 x_msg_count,
380 x_msg_data,
381 '_PVT'
382 );
383 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
384
385
386 IF l_assetlinesv_csr%ISOPEN THEN
387 CLOSE l_assetlinesv_csr;
388 END IF;
389 IF l_quotes_csr%ISOPEN THEN
390 CLOSE l_quotes_csr;
391 END IF;
392
393 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Close the cursor
394 IF l_assettrx_csr%ISOPEN THEN
395 CLOSE l_assettrx_csr;
396 END IF;
397
398 -- RRAVIKIR Legal Entity Changes
399 IF l_oklheaders_csr%ISOPEN THEN
400 CLOSE l_oklheaders_csr;
401 END IF;
402 -- Legal Entity Changes End
403
404 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
405 (
406 l_api_name,
407 G_PKG_NAME,
408 'OKL_API.G_RET_STS_UNEXP_ERROR',
409 x_msg_count,
410 x_msg_data,
411 '_PVT'
412 );
413 WHEN OTHERS THEN
414
415 IF l_assetlinesv_csr%ISOPEN THEN
416 CLOSE l_assetlinesv_csr;
417 END IF;
418 IF l_quotes_csr%ISOPEN THEN
419 CLOSE l_quotes_csr;
420 END IF;
421
422 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Close the cursor
423 IF l_assettrx_csr%ISOPEN THEN
424 CLOSE l_assettrx_csr;
425 END IF;
426
427 -- RRAVIKIR Legal Entity Changes
428 IF l_oklheaders_csr%ISOPEN THEN
429 CLOSE l_oklheaders_csr;
430 END IF;
431 -- Legal Entity Changes End
432
433 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
434 (
435 l_api_name,
436 G_PKG_NAME,
437 'OTHERS',
438 x_msg_count,
439 x_msg_data,
440 '_PVT'
441 );
442 END create_salvage_value_trx;
443 END OKL_AM_SV_WRITEDOWN_PVT;
[Home] [Help]