[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_PROCESS_RV_TRX_PVT
Source
1 PACKAGE BODY OKL_AM_PROCESS_RV_TRX_PVT AS
2 /* $Header: OKLRRVPB.pls 120.2.12010000.2 2009/08/05 13:03:45 rpillay ship $ */
3
4
5
6
7
8 -- Start of comments
9 --
10 -- Procedure Name : process_transactions_wrap
11 -- Description : This procedure is used to execute OKL_AM_PROCESS_RV_TRX_PVT
12 -- as a concurrent program. It has all the input parameters for
13 -- OKL_AM_PROCESS_RV_TRX_PVT and 2 standard OUT parameters - ERRBUF and RETCODE
14 -- Business Rules :
15 -- Parameters : p_khr_id - contract id
16 -- p_kle_id - line id
17 --
18 --
19 -- Version : 1.0
20 -- History : SECHAWLA 16-JAN-03 Bug # 2754280
21 -- Changed the app name from OKL to OKC for g_unexpected_error
22 -- End of comments
23
24 PROCEDURE process_transactions_wrap( ERRBUF OUT NOCOPY VARCHAR2,
25 RETCODE OUT NOCOPY VARCHAR2 ,
26 p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 ,
28 p_khr_id IN NUMBER ,
29 p_kle_id IN VARCHAR2
30 ) IS
31
32
33 l_return_status VARCHAR2(1);
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(2000);
36
37 l_transaction_status VARCHAR2(1);
38 l_api_name CONSTANT VARCHAR2(30) := 'process_transactions_wrap';
39 l_total_count NUMBER;
40 l_processed_count NUMBER;
41 l_error_count NUMBER;
42
43 BEGIN
44
45 process_transactions(
46 p_api_version => p_api_version,
47 p_init_msg_list => p_init_msg_list ,
48 x_return_status => l_return_status,
49 x_msg_count => l_msg_count,
50 x_msg_data => l_msg_data,
51 p_khr_id => p_khr_id ,
52 p_kle_id => TO_NUMBER(p_kle_id),
53 x_total_count => l_total_count,
54 x_processed_count => l_processed_count,
55 x_error_count => l_error_count
56 );
57
58
59 -- Add couple of blank lines
60 fnd_file.new_line(fnd_file.log,2);
61 fnd_file.new_line(fnd_file.output,2);
62
63
64
65
66 fnd_file.new_line(fnd_file.log,2);
67 fnd_file.new_line(fnd_file.output,2);
68
69 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
70 fnd_file.put_line(fnd_file.log, 'MASS REBOOK Process Failed, None of the transactions got processed');
71 fnd_file.put_line(fnd_file.output, 'MASS REBOOK Process Failed, None of the transactions got processed');
72 END IF;
73
74 IF l_total_count = 0 THEN
75 fnd_file.put_line(fnd_file.log, 'There were no Residual Value Writedown transactions to process.');
76 fnd_file.put_line(fnd_file.output,'There were no Residual Value Writedown transactions to process.');
77 ELSE
78
79 fnd_file.put_line(fnd_file.log, 'Total Transactions : '||l_total_count);
80 fnd_file.put_line(fnd_file.log, 'Transactions Processed Successfully : '||l_processed_count);
81 fnd_file.put_line(fnd_file.log, 'Transactions Failed : '||l_error_count);
82
83 fnd_file.put_line(fnd_file.output, 'Total Transactions : '||l_total_count);
84 fnd_file.put_line(fnd_file.output, 'Transactions Processed Successfully : '||l_processed_count);
85 fnd_file.put_line(fnd_file.output, 'Transactions Failed : '||l_error_count);
86
87 END IF;
88
89
90
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 -- SECHAWLA 16-JAN-03 Bug # 2754280 : Changed the app name from OKL to OKC
95 OKL_API.set_message(p_app_name => 'OKC',
96 p_msg_name => g_unexpected_error,
97 p_token1 => g_sqlcode_token,
98 p_token1_value => sqlcode,
99 p_token2 => g_sqlerrm_token,
100 p_token2_value => sqlerrm);
101
102 END process_transactions_wrap;
103
104
105
106
107 -- Start of comments
108 --
109 -- Procedure Name : process_transactions
110 -- Description : This procedure is used to process Residual Value Writedown Transactions
111 -- Business Rules :
112 -- Parameters : p_khr_id - contract id
113 -- p_kle_id - line id
114 -- x_total_count - Total number of transactions
115 -- x_processed_count - Number of transactions processed
116 -- x_error_count - Number of transactions Errored out
117 -- Version : 1.0
118 -- End of comments
119
120 PROCEDURE process_transactions(
121 p_api_version IN NUMBER,
122 p_init_msg_list IN VARCHAR2 ,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2,
126 p_khr_id IN NUMBER ,
127 p_kle_id IN NUMBER ,
128 x_total_count OUT NOCOPY NUMBER,
129 x_processed_count OUT NOCOPY NUMBER,
130 x_error_count OUT NOCOPY NUMBER) IS
131
132
133 SUBTYPE thpv_rec_type IS okl_trx_assets_pub.thpv_rec_type;
134 SUBTYPE rbk_tbl_type IS okl_mass_rebook_pub.rbk_tbl_type;
135 SUBTYPE strm_lalevl_tbl_type IS okl_mass_rebook_pub.strm_lalevl_tbl_type;
136
137
138 lp_thpv_rec thpv_rec_type;
139 lx_thpv_rec thpv_rec_type;
140 l_total_count NUMBER;
141 l_sysdate DATE;
142 l_strm_lalevl_empty_tbl strm_lalevl_tbl_type ;
143 l_rbk_tbl rbk_tbl_type ;
144 lx_error_rec OKL_API.error_rec_type;
145 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
146
147 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
148 l_api_name CONSTANT VARCHAR2(30) := 'process_transactions';
149
150
151 l_method_code fa_methods.method_code%TYPE;
152 l_fa_cost NUMBER;
153 l_fa_salvage_value NUMBER;
154 l_delta_cost NUMBER;
155 l_delta_salvage_value NUMBER;
156 l_api_version CONSTANT NUMBER := 1;
157 l_transaction_status VARCHAR2(1);
158 l_process_count NUMBER;
159 l_count NUMBER;
160
161 -- This cursor is used to get all active Residual Value Writedown transactions from OKL tables
162 CURSOR l_assettrx_csr(p_sysdate DATE) IS
163 SELECT h.id, h.tas_type, date_trans_occurred, depreciate_yn, dnz_asset_id, corporate_book, in_service_date,
164 deprn_method,life_in_months, nvl(depreciation_cost,0) depreciation_cost , asset_number, old_residual_value,
165 new_residual_value, kle_id, dnz_khr_id, contract_number, sts_code
166 FROM OKL_TRX_ASSETS h, okl_txl_assets_v l, okc_k_headers_b khr
167 WHERE h.id = l.tas_id
168 AND h.tsu_code NOT IN ('PROCESSED')
169 AND h.tas_type = 'ARC'
170 AND khr.id = l.dnz_khr_id
171 AND h.date_trans_occurred <= p_sysdate
172 AND (
173 -- all 2 parameter values are provided
174 ( p_khr_id IS NOT NULL AND p_kle_id IS NOT NULL AND
175 l.dnz_khr_id = p_khr_id AND l.kle_id = p_kle_id)
176 OR
177 -- none of the parameter values are provided
178 ( p_khr_id IS NULL AND p_kle_id IS NULL)
179 OR
180 -- contract Id is provided, kle_id not provided
181 (p_khr_id IS NOT NULL AND l.dnz_khr_id = p_khr_id AND p_kle_id IS NULL)
182 OR
183 -- contract Id is not provided, kle_id is provided
184 (p_khr_id IS NULL AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
185
186 )
187 ORDER BY h.last_update_date;
188
189
190 -- This cursor is used to check if an accepted termination quote exists for a contract.
191 CURSOR l_quotes_csr(p_khr_id NUMBER) IS
192 SELECT count(*)
193 FROM okl_trx_quotes_b
194 WHERE qst_code = 'ACCEPTED'
195 AND khr_id = p_khr_id;
196
197
198
199
200 BEGIN
201
202 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
203 G_PKG_NAME,
204 p_init_msg_list,
205 l_api_version,
206 p_api_version,
207 '_PVT',
208 x_return_status);
209
210 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
211 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
212 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
213 RAISE OKC_API.G_EXCEPTION_ERROR;
214 END IF;
215
216
217
218 SELECT SYSDATE INTO l_sysdate FROM DUAL;
219
220
221 l_total_count := 0;
222 l_process_count := 0;
223
224 -- loop thru all the transactions in the OKL tables and process them
225 FOR l_assettrx_rec IN l_assettrx_csr(l_sysdate) LOOP
226
227 l_transaction_status := OKC_API.G_RET_STS_SUCCESS;
228 l_total_count := l_total_count + 1;
229
230
231
232 IF l_assettrx_rec.dnz_khr_id IS NULL OR l_assettrx_rec.dnz_khr_id = OKL_API.G_MISS_NUM THEN
233 -- Residual Value Writedown transaction could not be processed for asset ASSET_NUMBER because the Contract Id is missing.
234 OKC_API.set_message( p_app_name => 'OKL',
235 p_msg_name => 'OKL_AM_KHR_REQUIRED',
236 p_token1 => 'ASSET_NUMBER',
237 p_token1_value => l_assettrx_rec.asset_number);
238 l_transaction_status := OKC_API.G_RET_STS_ERROR;
239
240 ELSIF l_assettrx_rec.sts_code <> 'BOOKED' THEN
241 -- Residual Value Writedown transaction could not be processed for asset ASSET_NUMBER because the Contract is not booked
242 OKC_API.set_message( p_app_name => 'OKL',
243 p_msg_name => 'OKL_AM_KHR_NOT_BOOKED',
244 p_token1 => 'ASSET_NUMBER',
245 p_token1_value => l_assettrx_rec.asset_number,
246 p_token2 => 'CONTRACT_NUMBER',
247 p_token2_value => l_assettrx_rec.contract_number);
248
249 l_transaction_status := OKC_API.G_RET_STS_ERROR;
250
251 ELSE
252
253 OPEN l_quotes_csr(l_assettrx_rec.dnz_khr_id);
254 FETCH l_quotes_csr INTO l_count;
255 CLOSE l_quotes_csr;
256
257 IF l_count > 0 THEN
258 --Can not process Residual Value Writedown transaction for asset ASSET_NUMBER as an accepted termination quote exists for the contract CONTRACT_NUMBER.
259 OKL_API.set_message( p_app_name => 'OKL',
260 p_msg_name => 'OKL_AM_RVW_NOT_PROCESSED',
261 p_token1 => 'ASSET_NUMBER',
262 p_token1_value => l_assettrx_rec.asset_number,
263 p_token2 => 'CONTRACT_NUMBER',
264 p_token2_value => l_assettrx_rec.contract_number);
265
266 l_transaction_status := OKC_API.G_RET_STS_ERROR;
267
268
269 ELSE
270
271 --Bug# 8756653
272 -- Check if contract has been upgraded for effective dated rebook
273 OKL_LLA_UTIL_PVT.check_rebook_upgrade
274 (p_api_version => p_api_version,
275 p_init_msg_list => OKC_API.G_FALSE,
276 x_return_status => l_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data,
279 p_chr_id => l_assettrx_rec.dnz_khr_id);
280
281 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
282
283 l_transaction_status := OKC_API.G_RET_STS_ERROR;
284
285 ELSE
286
287 IF l_assettrx_rec.new_residual_value IS NULL OR l_assettrx_rec.new_residual_value = OKL_API.G_MISS_NUM THEN
288 --Residual Value Writedown transaction could not be processed for asset ASSET_NUMBER because the new residual value is missing.
289 OKC_API.set_message( p_app_name => 'OKL',
290 p_msg_name => 'OKL_AM_RV_REQUIRED',
291 p_token1 => 'ASSET_NUMBER',
292 p_token1_value => l_assettrx_rec.asset_number);
293
294 l_transaction_status := OKC_API.G_RET_STS_ERROR;
295 ELSE
296
297 l_rbk_tbl(1).khr_id := l_assettrx_rec.dnz_khr_id;
298 l_rbk_tbl(1).kle_id := l_assettrx_rec.kle_id;
299
300 okl_mass_rebook_pub.apply_mass_rebook(
301 p_api_version => p_api_version,
302 p_init_msg_list => OKC_API.G_FALSE,
303 x_return_status => l_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data,
306 p_rbk_tbl => l_rbk_tbl,
307 p_deprn_method_code => NULL,
308 p_in_service_date => NULL,
309 p_life_in_months => NULL,
310 p_basic_rate => NULL,
311 p_adjusted_rate => NULL,
312 p_residual_value => l_assettrx_rec.new_residual_value,
313 p_strm_lalevl_tbl => l_strm_lalevl_empty_tbl);
314
315
316 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
317 -- Residual Value Writedown transaction could not be processed for asset ASSET_NUMBER.
318 OKC_API.set_message( p_app_name => 'OKL',
319 p_msg_name => 'OKL_AM_RVP_TRANS_FAILED',
320 p_token1 => 'ASSET_NUMBER',
321 p_token1_value => l_assettrx_rec.asset_number);
322 l_transaction_status := l_return_status;
323 ELSE
324 -- update the staus (tsu_code) in okl_trx_assets_v
325 lp_thpv_rec.id := l_assettrx_rec.id;
326 lp_thpv_rec.tsu_code := 'PROCESSED';
327 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
328 p_api_version => p_api_version,
329 p_init_msg_list => OKC_API.G_FALSE,
330 x_return_status => l_return_status,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data,
333 p_thpv_rec => lp_thpv_rec,
334 x_thpv_rec => lx_thpv_rec);
335
336 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
337 -- Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER
338 OKC_API.set_message( p_app_name => 'OKL',
339 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
340 p_token1 => 'STATUS',
341 p_token1_value => 'PROCESSED',
342 p_token2 => 'ASSET_NUMBER',
343 p_token2_value => l_assettrx_rec.asset_number);
344 ELSE
345 l_process_count := l_process_count + 1;
346 -- Residual Value Writedown transaction processed successfully for asset ASSET_NUMBER.
347 OKC_API.set_message(
348 p_app_name => 'OKL',
349 p_msg_name => 'OKL_AM_RVW_PROCESSED',
350 p_token1 => 'ASSET_NUMBER',
351 p_token1_value => l_assettrx_rec.asset_number);
352
353 -- Old Residual Value :
354 OKC_API.set_message(
355 p_app_name => 'OKL',
356 p_msg_name => 'OKL_AM_OLD_RESIDUAL_VALUE',
357 p_token1 => 'OLD_RV',
358 p_token1_value => l_assettrx_rec.old_residual_value);
359
360 -- New Residual Value :
361 OKC_API.set_message(
362 p_app_name => 'OKL',
363 p_msg_name => 'OKL_AM_NEW_RESIDUAL_VALUE',
364 p_token1 => 'NEW_RV',
365 p_token1_value => l_assettrx_rec.new_residual_value);
366 END IF;
367 END IF;
368 END IF;
369 END IF;
370 END IF;
371 END IF;
372
373 IF l_transaction_status <> OKC_API.G_RET_STS_SUCCESS THEN
374 -- update the staus (tsu_code) in okl_trx_assets_v
375 lp_thpv_rec.id := l_assettrx_rec.id;
376 lp_thpv_rec.tsu_code := 'ERROR';
377 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
378 p_api_version => p_api_version,
379 p_init_msg_list => OKC_API.G_FALSE,
380 x_return_status => l_return_status,
381 x_msg_count => x_msg_count,
382 x_msg_data => x_msg_data,
383 p_thpv_rec => lp_thpv_rec,
384 x_thpv_rec => lx_thpv_rec);
385
386 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
387 -- Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER
388 OKC_API.set_message( p_app_name => 'OKL',
389 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
390 p_token1 => 'STATUS',
391 p_token1_value => 'ERROR',
392 p_token2 => 'ASSET_NUMBER',
393 p_token2_value => l_assettrx_rec.asset_number);
394 END IF;
395 END IF;
396
397 -- Print the messages from the stack
398
399 -- The following piece of code has been moved from procedure process_transactions_wrap to this procedure,
400 -- to address the problem in the bug 2491164, where we loose messages after call to mass rebook API.
401 -- Printing the message stack after each call to Mass Rebook helps prevent that problem.
402
403 fnd_msg_pub.reset;
404 LOOP
405
406 fnd_msg_pub.get(
407 p_msg_index => l_msg_idx,
408 p_encoded => FND_API.G_FALSE,
409 p_data => lx_error_rec.msg_data,
410 p_msg_index_out => lx_error_rec.msg_count);
411
412 IF (lx_error_rec.msg_count IS NOT NULL) THEN
413
414 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
415 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
416
417 END IF;
418
419 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG) OR (lx_error_rec.msg_count IS NULL));
420 l_msg_idx := FND_MSG_PUB.G_NEXT;
421 END LOOP;
422
423 -- This explicit deletion of messages is required for those contracts in the loop which fail validations
424 -- before the call to mass rebook api. Without the following reset statement, error messages for those contarcts
425 -- will be printed more than once.
426
427 --OKL_API.init_msg_list(p_init_msg_list => OKL_API.G_TRUE);
428 fnd_msg_pub.delete_msg;
429
430
431 END LOOP;
432
433
434
435 x_total_count := l_total_count;
436 x_processed_count := l_process_count;
437 x_error_count := l_total_count - l_process_count;
438
439 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
440
441 EXCEPTION
442
443 WHEN OKC_API.G_EXCEPTION_ERROR THEN
444
445 IF l_assettrx_csr%ISOPEN THEN
446 CLOSE l_assettrx_csr;
447 END IF;
448 IF l_quotes_csr%ISOPEN THEN
449 CLOSE l_quotes_csr;
450 END IF;
451 x_return_status := OKC_API.HANDLE_EXCEPTIONS
452 (
453 l_api_name,
454 G_PKG_NAME,
455 'OKC_API.G_RET_STS_ERROR',
456 x_msg_count,
457 x_msg_data,
458 '_PVT'
459 );
460 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
461
462 IF l_assettrx_csr%ISOPEN THEN
463 CLOSE l_assettrx_csr;
464 END IF;
465 IF l_quotes_csr%ISOPEN THEN
466 CLOSE l_quotes_csr;
467 END IF;
468 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
469 (
470 l_api_name,
471 G_PKG_NAME,
472 'OKC_API.G_RET_STS_UNEXP_ERROR',
473 x_msg_count,
474 x_msg_data,
475 '_PVT'
476 );
477 WHEN OTHERS THEN
478
479 IF l_assettrx_csr%ISOPEN THEN
480 CLOSE l_assettrx_csr;
481 END IF;
482 IF l_quotes_csr%ISOPEN THEN
483 CLOSE l_quotes_csr;
484 END IF;
485 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
486 (
487 l_api_name,
488 G_PKG_NAME,
489 'OTHERS',
490 x_msg_count,
491 x_msg_data,
492 '_PVT'
493 );
494
495
496 END process_transactions;
497
498
499 END;