[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 2006/07/11 09:57:49 dkagrawa noship $ */
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 IF l_assettrx_rec.new_residual_value IS NULL OR l_assettrx_rec.new_residual_value = OKL_API.G_MISS_NUM THEN
272 --Residual Value Writedown transaction could not be processed for asset ASSET_NUMBER because the new residual value is missing.
273 OKC_API.set_message( p_app_name => 'OKL',
274 p_msg_name => 'OKL_AM_RV_REQUIRED',
275 p_token1 => 'ASSET_NUMBER',
276 p_token1_value => l_assettrx_rec.asset_number);
277
278 l_transaction_status := OKC_API.G_RET_STS_ERROR;
279 ELSE
280
281 l_rbk_tbl(1).khr_id := l_assettrx_rec.dnz_khr_id;
282 l_rbk_tbl(1).kle_id := l_assettrx_rec.kle_id;
283
284 okl_mass_rebook_pub.apply_mass_rebook(
285 p_api_version => p_api_version,
286 p_init_msg_list => OKC_API.G_FALSE,
287 x_return_status => l_return_status,
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_rbk_tbl => l_rbk_tbl,
291 p_deprn_method_code => NULL,
292 p_in_service_date => NULL,
293 p_life_in_months => NULL,
294 p_basic_rate => NULL,
295 p_adjusted_rate => NULL,
296 p_residual_value => l_assettrx_rec.new_residual_value,
297 p_strm_lalevl_tbl => l_strm_lalevl_empty_tbl);
298
299
300 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
301 -- Residual Value Writedown transaction could not be processed for asset ASSET_NUMBER.
302 OKC_API.set_message( p_app_name => 'OKL',
303 p_msg_name => 'OKL_AM_RVP_TRANS_FAILED',
304 p_token1 => 'ASSET_NUMBER',
305 p_token1_value => l_assettrx_rec.asset_number);
306 l_transaction_status := l_return_status;
307 ELSE
308 -- update the staus (tsu_code) in okl_trx_assets_v
309 lp_thpv_rec.id := l_assettrx_rec.id;
310 lp_thpv_rec.tsu_code := 'PROCESSED';
311 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
312 p_api_version => p_api_version,
313 p_init_msg_list => OKC_API.G_FALSE,
314 x_return_status => l_return_status,
315 x_msg_count => x_msg_count,
316 x_msg_data => x_msg_data,
317 p_thpv_rec => lp_thpv_rec,
318 x_thpv_rec => lx_thpv_rec);
319
320 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
321 -- Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER
322 OKC_API.set_message( p_app_name => 'OKL',
323 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
324 p_token1 => 'STATUS',
325 p_token1_value => 'PROCESSED',
326 p_token2 => 'ASSET_NUMBER',
327 p_token2_value => l_assettrx_rec.asset_number);
328 ELSE
329 l_process_count := l_process_count + 1;
330 -- Residual Value Writedown transaction processed successfully for asset ASSET_NUMBER.
331 OKC_API.set_message(
332 p_app_name => 'OKL',
333 p_msg_name => 'OKL_AM_RVW_PROCESSED',
334 p_token1 => 'ASSET_NUMBER',
335 p_token1_value => l_assettrx_rec.asset_number);
336
337 -- Old Residual Value :
338 OKC_API.set_message(
339 p_app_name => 'OKL',
340 p_msg_name => 'OKL_AM_OLD_RESIDUAL_VALUE',
341 p_token1 => 'OLD_RV',
342 p_token1_value => l_assettrx_rec.old_residual_value);
343
344 -- New Residual Value :
345 OKC_API.set_message(
346 p_app_name => 'OKL',
347 p_msg_name => 'OKL_AM_NEW_RESIDUAL_VALUE',
348 p_token1 => 'NEW_RV',
349 p_token1_value => l_assettrx_rec.new_residual_value);
350 END IF;
351 END IF;
352 END IF;
353 END IF;
354 END IF;
355
356 IF l_transaction_status <> OKC_API.G_RET_STS_SUCCESS THEN
357 -- update the staus (tsu_code) in okl_trx_assets_v
358 lp_thpv_rec.id := l_assettrx_rec.id;
359 lp_thpv_rec.tsu_code := 'ERROR';
360 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
361 p_api_version => p_api_version,
362 p_init_msg_list => OKC_API.G_FALSE,
363 x_return_status => l_return_status,
364 x_msg_count => x_msg_count,
365 x_msg_data => x_msg_data,
366 p_thpv_rec => lp_thpv_rec,
367 x_thpv_rec => lx_thpv_rec);
368
369 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
370 -- Transaction status STATUS could not be updated in OKL for asset ASSET_NUMBER
371 OKC_API.set_message( p_app_name => 'OKL',
372 p_msg_name => 'OKL_AM_TRXASSET_UPD_FAILED',
373 p_token1 => 'STATUS',
374 p_token1_value => 'ERROR',
375 p_token2 => 'ASSET_NUMBER',
376 p_token2_value => l_assettrx_rec.asset_number);
377 END IF;
378 END IF;
379
380 -- Print the messages from the stack
381
382 -- The following piece of code has been moved from procedure process_transactions_wrap to this procedure,
383 -- to address the problem in the bug 2491164, where we loose messages after call to mass rebook API.
384 -- Printing the message stack after each call to Mass Rebook helps prevent that problem.
385
386 fnd_msg_pub.reset;
387 LOOP
388
389 fnd_msg_pub.get(
390 p_msg_index => l_msg_idx,
391 p_encoded => FND_API.G_FALSE,
392 p_data => lx_error_rec.msg_data,
393 p_msg_index_out => lx_error_rec.msg_count);
394
395 IF (lx_error_rec.msg_count IS NOT NULL) THEN
396
397 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
398 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
399
400 END IF;
401
402 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG) OR (lx_error_rec.msg_count IS NULL));
403 l_msg_idx := FND_MSG_PUB.G_NEXT;
404 END LOOP;
405
406 -- This explicit deletion of messages is required for those contracts in the loop which fail validations
407 -- before the call to mass rebook api. Without the following reset statement, error messages for those contarcts
408 -- will be printed more than once.
409
410 --OKL_API.init_msg_list(p_init_msg_list => OKL_API.G_TRUE);
411 fnd_msg_pub.delete_msg;
412
413
414 END LOOP;
415
416
417
418 x_total_count := l_total_count;
419 x_processed_count := l_process_count;
420 x_error_count := l_total_count - l_process_count;
421
422 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
423
424 EXCEPTION
425
426 WHEN OKC_API.G_EXCEPTION_ERROR THEN
427
428 IF l_assettrx_csr%ISOPEN THEN
429 CLOSE l_assettrx_csr;
430 END IF;
431 IF l_quotes_csr%ISOPEN THEN
432 CLOSE l_quotes_csr;
433 END IF;
434 x_return_status := OKC_API.HANDLE_EXCEPTIONS
435 (
436 l_api_name,
437 G_PKG_NAME,
438 'OKC_API.G_RET_STS_ERROR',
439 x_msg_count,
440 x_msg_data,
441 '_PVT'
442 );
443 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_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_UNEXP_ERROR',
456 x_msg_count,
457 x_msg_data,
458 '_PVT'
459 );
460 WHEN OTHERS 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 'OTHERS',
473 x_msg_count,
474 x_msg_data,
475 '_PVT'
476 );
477
478
479 END process_transactions;
480
481
482 END;