[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_ASSET_RETURN_PVT
Source
1 PACKAGE BODY OKL_AM_ASSET_RETURN_PVT AS
2 /* $Header: OKLRARRB.pls 120.22 2007/12/14 22:26:25 rmunjulu noship $ */
3
4
5 -- GLOBAL VARIABLES
6
7 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10
11 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_asset_return_pvt.';
12
13
14 -- To Do
15 -- 1. Need to update the floor amount in the contract Header
16 -- 2. Set the inventory item id
17
18
19 -- Start of comments
20 --
21 -- Procedure Name : set_defaults
22 -- Description :
23 -- Default the values of parameters if the values are not passed to this API
24 -- This assumption is necessary because this API can either be called from
25 -- a screen or from some other process api and not all parameters are passed
26 -- Business Rules :
27 -- Parameters :
28 -- Version : 1.0
29 --
30 -- End of comments
31 PROCEDURE set_defaults(
32 px_artv_rec IN OUT NOCOPY artv_rec_type,
33 x_return_status OUT NOCOPY VARCHAR2) IS
34 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'set_defaults';
35 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
36 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
37 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
38 BEGIN
39
40 IF (is_debug_procedure_on) THEN
41 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name ,'Begin(+)');
42 END IF;
43 IF (is_debug_statement_on) THEN
44 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In out param, px_artv_rec.relocate_asset_yn: '||px_artv_rec.relocate_asset_yn);
45 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In out param, px_artv_rec.asset_relocated_yn: '||px_artv_rec.asset_relocated_yn);
46 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In out param, px_artv_rec.commmercially_reas_sale_yn: '||px_artv_rec.commmercially_reas_sale_yn);
47 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In out param, px_artv_rec.voluntary_yn: '||px_artv_rec.voluntary_yn);
48 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In out param, px_artv_rec.repurchase_agmt_yn: '||px_artv_rec.repurchase_agmt_yn);
49 END IF;
50
51 -- Set the relocate_asset_yn if null
52 IF ((px_artv_rec.relocate_asset_yn IS NULL) OR
53 (px_artv_rec.relocate_asset_yn = OKL_API.G_MISS_CHAR)) THEN
54 px_artv_rec.relocate_asset_yn := 'N';
55 END IF;
56
57 -- Set the asset_relocated_yn if null
58 IF ((px_artv_rec.asset_relocated_yn IS NULL) OR
59 (px_artv_rec.asset_relocated_yn = OKL_API.G_MISS_CHAR)) THEN
60 px_artv_rec.asset_relocated_yn := 'N';
61 END IF;
62
63 -- Set the commmercially_reas_sale_yn if null
64 IF ((px_artv_rec.commmercially_reas_sale_yn IS NULL) OR
65 (px_artv_rec.commmercially_reas_sale_yn = OKL_API.G_MISS_CHAR)) THEN
66 px_artv_rec.commmercially_reas_sale_yn := 'N';
67 END IF;
68
69 -- Set the voluntary_yn if null
70 IF ((px_artv_rec.voluntary_yn IS NULL) OR
71 (px_artv_rec.voluntary_yn = OKL_API.G_MISS_CHAR)) THEN
72 px_artv_rec.voluntary_yn := 'N';
73 END IF;
74
75 -- Set the repurchase_agmt_yn if null
76 IF ((px_artv_rec.repurchase_agmt_yn IS NULL) OR
77 (px_artv_rec.repurchase_agmt_yn = OKL_API.G_MISS_CHAR)) THEN
78 px_artv_rec.repurchase_agmt_yn := 'N';
79 END IF;
80
81 x_return_status := OKL_API.G_RET_STS_SUCCESS;
82
83 IF (is_debug_procedure_on) THEN
84 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
85 END IF;
86
87 EXCEPTION
88 WHEN OTHERS THEN
89 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
90 -- Unexpected error
91 OKL_API.set_message(p_app_name => 'OKL',
92 p_msg_name => g_unexpected_error,
93 p_token1 => g_sqlcode_token,
94 p_token1_value => sqlcode,
95 p_token2 => g_sqlerrm_token,
96 p_token2_value => sqlerrm);
97 IF (is_debug_exception_on) THEN
98 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
99 || sqlcode || ' , SQLERRM : ' || sqlerrm);
100 END IF;
101
102 END set_defaults;
103
104
105
106
107 -- Start of comments
108 --
109 -- Procedure Name : calculate_floor_price
110 -- Description : This procedure calculates the floor price.
111 -- Business Rules :
112 -- Parameters : p_chr_id - Contract header ID, p_kle_id - Contract Line ID
113 -- Version : 1.0
114 --
115 -- End of comments
116
117 FUNCTION get_floor_price( p_chr_id IN NUMBER,
118 p_kle_id IN NUMBER,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2,
121 x_return_status OUT NOCOPY VARCHAR2 ) RETURN NUMBER AS
122
123 l_floor_price NUMBER ;
124 l_rulv_rec okl_rule_pub.rulv_rec_type;
125 floor_price_error EXCEPTION;
126 l_formula_name VARCHAR2(150) := 'DEFAULT ASSET FLOOR PRICE';
127 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_floor_price';
128 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
129 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
130 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
131
132 BEGIN
133
134 IF (is_debug_procedure_on) THEN
135 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
136 END IF;
137 IF (is_debug_statement_on) THEN
138 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_chr_id: '||p_chr_id);
139 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_kle_id: '||p_kle_id);
140 END IF;
141
142 IF (is_debug_statement_on) THEN
143 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_rule_record');
144 END IF;
145
146 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMLARL'
147 ,p_rdf_code => 'AMCFPR'
148 ,p_chr_id => p_chr_id
149 ,p_cle_id => NULL
150 ,p_message_yn => FALSE
151 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
152 ,x_return_status => x_return_status
153 ,x_msg_count => x_msg_count
154 ,x_msg_data => x_msg_data);
155 IF (is_debug_statement_on) THEN
156 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called okl_am_util_pvt.get_rule_record, return status :'||x_return_status);
157 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_rulv_rec.rule_information1 :'||l_rulv_rec.rule_information1);
158 END IF;
159
160 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
161 -- Rule instance is found and formula is found
162 IF (l_rulv_rec.rule_information1 IS NOT NULL) AND
163 (l_rulv_rec.rule_information1 <> OKL_API.G_MISS_CHAR) THEN
164
165 l_formula_name := l_rulv_rec.rule_information1;
166 END IF;
167 END IF;
168
169 IF (is_debug_statement_on) THEN
170 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_formula_value');
171 END IF;
172
173 okl_am_util_pvt.get_formula_value(
174 p_formula_name => l_formula_name,
175 p_chr_id => p_chr_id,
176 p_cle_id => p_kle_id,
177 x_formula_value => l_floor_price,
178 x_return_status => x_return_status);
179 IF (is_debug_statement_on) THEN
180 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called okl_am_util_pvt.get_formula_value, return status :'||x_return_status);
181 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_floor_price :'||l_floor_price);
182 END IF;
183
184 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
185 -- Unable to create Asset Return because of the missing floor price formula.
186 OKL_API.set_message( p_app_name => 'OKL',
187 p_msg_name => 'OKL_AM_MISSING_FORMULA');
188 RAISE floor_price_error;
189 END IF;
190 IF (is_debug_statement_on) THEN
191 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'Returning l_floor_price: '||l_floor_price);
192 END IF;
193
194 IF (is_debug_procedure_on) THEN
195 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
196 END IF;
197
198
199 RETURN l_floor_price;
200
201 EXCEPTION
202 WHEN floor_price_error THEN
203 x_return_status := OKL_API.G_RET_STS_ERROR;
204 IF (is_debug_exception_on) THEN
205 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'floor_price_error');
206 END IF;
207
208 RETURN NULL;
209
210 WHEN OTHERS THEN
211 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
212 -- Unexpected error
213 OKL_API.set_message(p_app_name => 'OKL',
214 p_msg_name => g_unexpected_error,
215 p_token1 => g_sqlcode_token,
216 p_token1_value => sqlcode,
217 p_token2 => g_sqlerrm_token,
218 p_token2_value => sqlerrm);
219 IF (is_debug_exception_on) THEN
220 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
221 || sqlcode || ' , SQLERRM : ' || sqlerrm);
222 END IF;
223
224 RETURN NULL;
225
226 END get_floor_price;
227
228 -- Start of comments
229 --
230 -- Procedure Name : get_item_price
231 -- Description : This procedure is used to calculate the Item Price.
232 -- Business Rules :
233 -- Parameters : p_chr_id - Contract Header ID, p_kle_id - Contract Line ID
234 -- Version : 1.0
235 -- History : SECHAWLA 07-FEB-03 Bug # 2758114
236 -- Default item price to 0 if rule instance or formula is not found
237 -- End of comments
238
239 FUNCTION get_item_price (p_chr_id IN NUMBER,
240 p_kle_id IN NUMBER,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2,
243 x_return_status OUT NOCOPY VARCHAR2 ) RETURN NUMBER AS
244
245 l_item_price NUMBER ;
246 l_rulv_rec okl_rule_pub.rulv_rec_type;
247 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_item_price';
248 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
249 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
250 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
251
252 BEGIN
253
254 IF (is_debug_procedure_on) THEN
255 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
256 END IF;
257 IF (is_debug_statement_on) THEN
258 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_chr_id: '||p_chr_id);
259 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_kle_id: '||p_kle_id);
260 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_rule_record');
261 END IF;
262 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMLARL'
263 ,p_rdf_code => 'AMCFPR'
264 ,p_chr_id => p_chr_id
265 ,p_cle_id => NULL
266 ,p_message_yn => FALSE
267 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
268 ,x_return_status => x_return_status
269 ,x_msg_count => x_msg_count
270 ,x_msg_data => x_msg_data);
271 IF (is_debug_statement_on) THEN
272 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_rule_record, return status: ' || x_return_status);
273 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_rulv_rec.rule_information2: ' || l_rulv_rec.rule_information2);
274 END IF;
275
276 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
277 -- Rule instance is found, but formula not found
278 IF l_rulv_rec.rule_information2 IS NULL OR l_rulv_rec.rule_information2 = OKL_API.G_MISS_CHAR THEN
279 --SECHAWLA 07-FEB-03 Bug # 2758114 : Default item price to 0 if no formula found
280 RETURN NULL;
281 END IF;
282 ELSE
283 --SECHAWLA 07-FEB-03 Bug # 2758114 : Default item price to 0 if rule instance is not found
284 x_return_status := OKL_API.G_RET_STS_SUCCESS;
285 RETURN NULL;
286 END IF;
287
288 IF (is_debug_statement_on) THEN
289 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_formula_value');
290 END IF;
291 okl_am_util_pvt.get_formula_value(
292 p_formula_name => l_rulv_rec.rule_information2,
293 p_chr_id => p_chr_id,
294 p_cle_id => p_kle_id,
295 x_formula_value => l_item_price,
296 x_return_status => x_return_status);
297 IF (is_debug_statement_on) THEN
298 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_formula_value, return status: ' || x_return_status);
299 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'l_item_price: ' || l_item_price);
300 END IF;
301 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
302 RETURN NULL;
303 END IF;
304 IF (is_debug_statement_on) THEN
305 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'Returning l_item_price: '||l_item_price);
306 END IF;
307 IF (is_debug_procedure_on) THEN
308 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
309 END IF;
310
311 RETURN l_item_price;
312
313 EXCEPTION
314
315 WHEN OTHERS THEN
316 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
317 -- Unexpected Error
318 OKL_API.set_message(p_app_name => 'OKL',
319 p_msg_name => g_unexpected_error,
320 p_token1 => g_sqlcode_token,
321 p_token1_value => sqlcode,
322 p_token2 => g_sqlerrm_token,
323 p_token2_value => sqlerrm);
324 IF (is_debug_exception_on) THEN
325 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
326 || sqlcode || ' , SQLERRM : ' || sqlerrm);
327 END IF;
328
329 RETURN NULL;
330
331 END get_item_price;
332
333
334 -- Start of comments
335 --
336 -- Procedure Name : get_repurchase_agreement
337 -- Description : This procedure is used to get the repurchase agreement Y/N flag
338 -- Business Rules :
339 -- Parameters : p_chr_id - Contract Header ID, p_kle_id - Contract Line ID
340 -- Version : 1.0
341 --
342 -- End of comments
343
344 FUNCTION get_repurchase_agreement(p_chr_id IN NUMBER,
345 p_kle_id IN NUMBER,
346 x_msg_count OUT NOCOPY NUMBER,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 AS
349
350 --Check if Vendor program is attached to the Lease contract
351 CURSOR l_khr_csr(p_id NUMBER) IS
352 SELECT khr.khr_id
353 FROM okl_k_headers khr
354 WHERE khr.id = p_id;
355
356 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'get_repurchase_agreement';
357 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
358 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
359 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
360 l_repuchase_agreement_yn VARCHAR2(1);
361 l_program_khr_id NUMBER := NULL;
362 l_rulv_rec okl_rule_pub.rulv_rec_type;
363 repurchase_agreement_error EXCEPTION;
364 BEGIN
365
366 IF (is_debug_procedure_on) THEN
367 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
368 END IF;
369 IF (is_debug_statement_on) THEN
370 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_chr_id: '||p_chr_id);
371 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_kle_id: '||p_kle_id);
372 END IF;
373
374 --Check if Vendor program is attached to the Lease contract
375 OPEN l_khr_csr(p_chr_id);
376 FETCH l_khr_csr INTO l_program_khr_id;
377 CLOSE l_khr_csr;
378
379 --Is a Vendor program attached to the Lease contract
380 IF l_program_khr_id IS NULL THEN
381 RAISE repurchase_agreement_error;
382 END IF;
383
384 IF (is_debug_statement_on) THEN
385 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_am_util_pvt.get_rule_record');
386 END IF;
387 okl_am_util_pvt.get_rule_record( p_rgd_code => 'AMREPQ'
388 ,p_rdf_code => 'AMARQC'
389 ,p_chr_id => p_chr_id
390 ,p_cle_id => NULL
391 ,p_message_yn => FALSE
392 ,x_rulv_rec => l_rulv_rec -- hold a rule instance from okc_rules_b
393 ,x_return_status => x_return_status
394 ,x_msg_count => x_msg_count
395 ,x_msg_data => x_msg_data);
396
397 IF (is_debug_statement_on) THEN
398 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_rule_record, x_return_status: ' || x_return_status);
399 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_am_util_pvt.get_rule_record, l_rulv_rec.rule_information1: ' || l_rulv_rec.rule_information1);
400 END IF;
401
402 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
403 -- Rule instance is found, but formula not found
404 IF l_rulv_rec.rule_information1 IS NULL OR l_rulv_rec.rule_information1 = OKL_API.G_MISS_CHAR THEN
405 RAISE repurchase_agreement_error;
406 END IF;
407 ELSE
408 RAISE repurchase_agreement_error;
409 END IF;
410
411 l_repuchase_agreement_yn := l_rulv_rec.rule_information1;
412 IF (is_debug_statement_on) THEN
413 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'Returning l_repuchase_agreement_yn: '||l_repuchase_agreement_yn);
414 END IF;
415
416 IF (is_debug_procedure_on) THEN
417 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
418 END IF;
419
420 RETURN l_repuchase_agreement_yn;
421
422 EXCEPTION
423 WHEN repurchase_agreement_error THEN
424 x_return_status := OKL_API.G_RET_STS_ERROR;
425 IF l_khr_csr%ISOPEN THEN
426 CLOSE l_khr_csr;
427 END IF;
428 IF (is_debug_exception_on) THEN
429 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'repurchase_agreement_error');
430 END IF;
431
432 RETURN 'N';
433
434 WHEN OTHERS THEN
435 IF l_khr_csr%ISOPEN THEN
436 CLOSE l_khr_csr;
437 END IF;
438
439 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
440 -- Unexpected Error
441 OKL_API.set_message(p_app_name => 'OKL',
442 p_msg_name => g_unexpected_error,
443 p_token1 => g_sqlcode_token,
444 p_token1_value => sqlcode,
445 p_token2 => g_sqlerrm_token,
446 p_token2_value => sqlerrm);
447 IF (is_debug_exception_on) THEN
448 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
449 || sqlcode || ' , SQLERRM : ' || sqlerrm);
450 END IF;
451
452 RETURN 'N';
453
454 END get_repurchase_agreement;
455
456
457
458 -- Start of comments
459 --
460 -- Procedure Name : assign_remarketer
461 -- Description : assign default remarketer if a default remarketer is found
462 -- Business Rules :
463 -- Parameters :
464 -- Version : 1.0
465 --
466 -- End of comments
467 PROCEDURE assign_remarketer(
468 p_kle_id IN NUMBER,
469 x_rmr_id OUT NOCOPY NUMBER,
470 x_return_status OUT NOCOPY VARCHAR2) IS
471
472 -- This cursor is used to get the item catalog
473 CURSOR l_catgrp_csr(p_id NUMBER) IS
474 SELECT st.item_catalog_group_id
475 FROM MTL_SYSTEM_ITEMS_VL st, OKX_MODEL_LINES_V ml
476 WHERE ml.inventory_item_id = st.inventory_item_id
477 AND ml.parent_line_id = p_id;
478
479 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
480 p_catalog_group_id NUMBER;
481 p_rmr_id NUMBER;
482 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'assign_remarketer';
483 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
484 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
485 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
486
487 -- This cursor is used to get the remarketer
488 CURSOR l_remarketcombo_csr(p_id NUMBER) IS
489 SELECT RC.rmr_id
490 FROM OKL_DF_CTGY_RMK_TMS RC, OKL_AM_REMARKET_TEAMS_UV T
491 WHERE RC.rmr_id = T.ORIG_SYSTEM_ID
492 AND RC.ico_id = p_id
493 AND RC.date_effective_from <= SYSDATE
494 AND NVL(RC.date_effective_to, SYSDATE+1) >= SYSDATE;
495
496
497
498 BEGIN
499
500 IF (is_debug_procedure_on) THEN
501 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
502 END IF;
503 IF (is_debug_statement_on) THEN
504 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'In param, p_kle_id: '||p_kle_id);
505 END IF;
506
507 OPEN l_catgrp_csr(p_kle_id);
508 FETCH l_catgrp_csr into p_catalog_group_id;
509 CLOSE l_catgrp_csr;
510
511 IF (is_debug_statement_on) THEN
512 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'p_catalog_group_id: '||p_catalog_group_id);
513 END IF;
514
515 IF p_catalog_group_id IS NOT NULL AND p_catalog_group_id <> OKL_API.G_MISS_NUM THEN
516
517 OPEN l_remarketcombo_csr(p_catalog_group_id);
518 FETCH l_remarketcombo_csr INTO p_rmr_id;
519 CLOSE l_remarketcombo_csr;
520
521 IF p_rmr_id IS NOT NULL AND p_rmr_id <> OKL_API.G_MISS_NUM THEN
522
523 x_rmr_id := p_rmr_id;
524 x_return_status := OKL_API.G_RET_STS_SUCCESS;
525
526 ELSE
527
528 p_rmr_id := TO_NUMBER(fnd_profile.value('OKL_DEFAULT_REMARKETER'));
529
530 -- validate default remarketer
531 IF p_rmr_id IS NULL OR p_rmr_id = OKL_API.G_MISS_NUM THEN
532 x_return_status := OKL_API.G_RET_STS_ERROR;
533 ELSE
534 x_rmr_id := p_rmr_id;
535 x_return_status := OKL_API.G_RET_STS_SUCCESS;
536 END IF;
537
538 END IF;
539
540 ELSE
541
542 p_rmr_id := TO_NUMBER(fnd_profile.value('OKL_DEFAULT_REMARKETER'));
543
544 -- validate default remarketer
545 IF p_rmr_id IS NULL OR p_rmr_id = OKL_API.G_MISS_NUM THEN
546 x_return_status := OKL_API.G_RET_STS_ERROR;
547 ELSE
548 x_rmr_id := p_rmr_id;
549 x_return_status := OKL_API.G_RET_STS_SUCCESS;
550 END IF;
551
552 END IF;
553 IF (is_debug_statement_on) THEN
554 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'x_rmr_id: '||x_rmr_id);
555 END IF;
556
557 IF (is_debug_procedure_on) THEN
558 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
559 END IF;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 IF l_catgrp_csr%ISOPEN THEN
564 CLOSE l_catgrp_csr;
565 END IF;
566 IF l_remarketcombo_csr%ISOPEN THEN
567 CLOSE l_remarketcombo_csr;
568 END IF;
569 -- unexpected error
570 OKL_API.set_message(p_app_name => 'OKL',
571 p_msg_name => g_unexpected_error,
572 p_token1 => g_sqlcode_token,
573 p_token1_value => sqlcode,
574 p_token2 => g_sqlerrm_token,
575 p_token2_value => sqlerrm);
576 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
577 IF (is_debug_exception_on) THEN
578 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
579 || sqlcode || ' , SQLERRM : ' || sqlerrm);
580 END IF;
581
582 END assign_remarketer;
583
584
585
586
587 -- Start of comments
588 --
589 -- Procedure Name : create_asset_return
590 -- Description : This procedure creates an Asset Return record
591 -- Business Rules :
592 -- Parameters : p_artv_rec - Asset Return record.
593 -- Version : 1.0
594 -- History : SECHAWLA - 19-DEC-2002 : Bug # 2667636
595 -- Added logic to convert floor price an item price from contract currency to functional currency
596 -- SECHAWLA - 16-JAN-03 Bug # 2754280
597 -- Modified code to display user profile option name in messages instead of profile option name
598 -- Removed DEFAULT hint from procedure parameters
599 -- SECHAWLA - 07-FEB-03 Bug # 2758114
600 -- Remove defaulting logic for Item Price and default to 0 if no formula found.
601 -- Propogate error if a formula is found and execution returns error.
602 -- SECHAWLA 07-FEB-03 Bug # 2789656 : Added x_return_status parameter to okl_accounting_util call
603 -- RMUNJULU 3061751 SERVICE CONTRACT INTEGRATION STEPS
604 -- : 29 Oct 2004 PAGARG Bug# 3925453
605 -- : Additional Input parameter quote id that will
606 -- : be used to obtain quote type and validate
607 -- : ARS_CODE
608 -- : DJANASWA Changes for 'Asset repossession for a loan' project
609 -- 13-Nov-2007 Added validation for ASSET_FMV_AMOUNT column
610 --
611 -- : RKUTTIYA 14-NOV-07 Sprint 2 of Loans Repossession
612 -- Added validations for Repossession Indicator
613 -- End of comments
614
615 PROCEDURE create_asset_return(
616 p_api_version IN NUMBER,
617 p_init_msg_list IN VARCHAR2,
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2,
621 p_artv_rec IN artv_rec_type,
622 x_artv_rec OUT NOCOPY artv_rec_type,
623 p_quote_id IN NUMBER DEFAULT NULL) AS
624
625 SUBTYPE rasv_rec_type IS OKL_AM_SHIPPING_INSTR_PUB.rasv_rec_type;
626
627 lp_artv_rec artv_rec_type := p_artv_rec;
628 lx_artv_rec artv_rec_type;
629
630 lp_rasv_rec rasv_rec_type;
631 lx_rasv_rec rasv_rec_type;
632
633 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
634 l_api_name CONSTANT VARCHAR2(30) := 'create_asset_return';
635 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_asset_return';
636 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
637 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
638 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
639 l_floor_price NUMBER := OKL_API.G_MISS_NUM;
640 l_inventory_item_id NUMBER := OKL_API.G_MISS_NUM;
641 l_Item_Price NUMBER := OKL_API.G_MISS_NUM;
642 l_New_Item_Number VARCHAR2(25);
643 l_New_Item_Id NUMBER := OKL_API.G_MISS_NUM;
644 l_rmr_id NUMBER := OKL_API.G_MISS_NUM;
645 l_api_version CONSTANT NUMBER := 1;
646 l_repurchase_yn VARCHAR2(1);
647 l_chr_id NUMBER;
648 l_name VARCHAR2(150);
649 l_item_description VARCHAR2(1995);
650 l_rulv_rec okl_rule_pub.rulv_rec_type;
651 floor_price_error EXCEPTION;
652
653 l_contract_status VARCHAR2(30);
654
655 --SECHAWLA Bug # 2667636 : new declarations
656 l_func_curr_code GL_LEDGERS_PUBLIC_V.CURRENCY_CODE%TYPE;
657 l_contract_curr_code okc_k_headers_b.currency_code%TYPE;
658 lx_contract_currency okl_k_headers_full_v.currency_code%TYPE;
659 lx_currency_conversion_type okl_k_headers_full_v.currency_conversion_type%TYPE;
660 lx_currency_conversion_rate okl_k_headers_full_v.currency_conversion_rate%TYPE;
661 lx_currency_conversion_date okl_k_headers_full_v.currency_conversion_date%TYPE;
662 lx_converted_amount NUMBER;
663 l_sysdate DATE;
664
665 -- This cursor is used to get contract ID for a given financial asset line
666 CURSOR l_okcklinesv_csr(p_id NUMBER) IS
667 SELECT chr_id, name, item_description
668 FROM okc_k_lines_v
669 WHERE id = p_id;
670 --SECHAWLA 16-JAN-03 Bug # 2754280 : new declarations
671 l_user_profile_name VARCHAR2(240);
672
673 -- SECHAWLA 07-FEB-03 Bug # 2758114 : New declarations
674 item_price_error EXCEPTION;
675
676 -- RMUNJULU 3061751
677 l_service_int_needed VARCHAR2(1) := 'N';
678
679 --Bug# 3925453: pagarg +++ T and A +++++++ Start ++++++++++
680 CURSOR l_qte_type_csr(p_quote_id NUMBER) IS
681 SELECT qtb.qtp_code
682 FROM okl_trx_quotes_b qtb
683 WHERE qtb.id = p_quote_id;
684
685 l_qtp_code okl_trx_quotes_b.qtp_code%TYPE;
686 --Bug# 3925453: pagarg +++ T and A +++++++ End ++++++++++
687
688 -- RRAVIKIR Legal Entity Changes
689 CURSOR fetch_legal_entity(p_khr_id NUMBER) IS
690 SELECT legal_entity_id
691 FROM okl_k_headers
692 WHERE id = p_khr_id;
693
694 l_legal_entity_id NUMBER;
695 -- Legal Entity Changes
696
697 --rkuttiya added for Loans Repossession
698 l_repo_yn VARCHAR2(1);
699 lx_return_Status VARCHAR2(1);
700 BEGIN
701
702 IF (is_debug_procedure_on) THEN
703 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
704 END IF;
705 IF (is_debug_statement_on) THEN
706 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.id:' || lp_artv_rec.id);
707 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.kle_id:' || lp_artv_rec.kle_id);
708 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.legal_entity_id:' || lp_artv_rec.legal_entity_id);
709 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.new_item_description:' || lp_artv_rec.new_item_description);
710 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.ARS_CODE:' || lp_artv_rec.ARS_CODE);
711 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.art1_code:' || lp_artv_rec.art1_code);
712 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.rna_id:' || lp_artv_rec.rna_id);
713 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.date_repossession_required:' || lp_artv_rec.date_repossession_required);
714 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.date_repossession_actual:' || lp_artv_rec.date_repossession_actual);
715 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.date_hold_until:' || lp_artv_rec.date_hold_until);
716 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.floor_price:' || lp_artv_rec.floor_price);
717 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.new_item_price:' || lp_artv_rec.new_item_price);
718 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.currency_code:' || lp_artv_rec.currency_code);
719 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.currency_conversion_code:' || lp_artv_rec.currency_conversion_code);
720 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.repurchase_agmt_yn:' || lp_artv_rec.repurchase_agmt_yn);
721 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.rmr_id:' || lp_artv_rec.rmr_id);
722 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.ASSET_FMV_AMOUNT:' || lp_artv_rec.ASSET_FMV_AMOUNT);
723 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.legal_entity_id:' || lp_artv_rec.legal_entity_id);
724 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.imr_id:' || lp_artv_rec.imr_id);
725 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.attribute14:' || lp_artv_rec.attribute14);
726 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.new_item_number:' || lp_artv_rec.new_item_number);
727 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, p_quote_id:' || p_quote_id);
728 END IF;
729
730 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
731 G_PKG_NAME,
732 p_init_msg_list,
733 l_api_version,
734 p_api_version,
735 '_PVT',
736 x_return_status);
737
738
739 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
740 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
741 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
742 RAISE OKL_API.G_EXCEPTION_ERROR;
743 END IF;
744
745 -- SECHAWLA Bug # 2667636 : using sysdate as transaction date for currency conversion routines
746 SELECT SYSDATE INTO l_sysdate FROM DUAL;
747
748 IF lp_artv_rec.kle_id IS NULL OR lp_artv_rec.kle_id = OKL_API.G_MISS_NUM THEN
749 x_return_status := OKL_API.G_RET_STS_ERROR;
750 -- Asset Number is required
751 OKL_API.set_message( p_app_name => 'OKC',
752 p_msg_name => G_REQUIRED_VALUE,
753 p_token1 => G_COL_NAME_TOKEN,
754 p_token1_value => 'Asset Number');
755 RAISE OKL_API.G_EXCEPTION_ERROR;
756 END IF;
757
758 OPEN l_okcklinesv_csr(lp_artv_rec.kle_id);
759 FETCH l_okcklinesv_csr INTO l_chr_id, l_name, l_item_description;
760 IF l_okcklinesv_csr%NOTFOUND THEN
761 x_return_status := OKL_API.G_RET_STS_ERROR;
762 -- Invalid Asset Number
763 OKL_API.set_message( p_app_name => 'OKC',
764 p_msg_name => G_INVALID_VALUE,
765 p_token1 => G_COL_NAME_TOKEN,
766 p_token1_value => 'Asset Number');
767 RAISE OKL_API.G_EXCEPTION_ERROR;
768 END IF;
769 IF (is_debug_statement_on) THEN
770 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_chr_id :'||l_chr_id);
771 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_name :'||l_name);
772 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_item_description :'||l_item_description);
773 END IF;
774
775 IF l_chr_id IS NULL OR l_chr_id = OKL_API.G_MISS_NUM THEN
776 x_return_status := OKL_API.G_RET_STS_ERROR;
777 -- Contract ID is required
778 OKL_API.set_message( p_app_name => 'OKC',
779 p_msg_name => G_REQUIRED_VALUE,
780 p_token1 => G_COL_NAME_TOKEN,
781 p_token1_value => 'Contract Id');
782 RAISE OKL_API.G_EXCEPTION_ERROR;
783 END IF;
784 CLOSE l_okcklinesv_csr;
785
786 -- RRAVIKIR Legal Entity Changes
787 OPEN fetch_legal_entity(p_khr_id => l_chr_id);
788 FETCH fetch_legal_entity INTO l_legal_entity_id;
789 CLOSE fetch_legal_entity;
790
791 IF (is_debug_statement_on) THEN
792 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_legal_entity_id :'||l_legal_entity_id);
793 END IF;
794
795 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
796 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
797 p_msg_name => g_required_value,
798 p_token1 => g_col_name_token,
799 p_token1_value => 'legal_entity_id');
800 RAISE OKC_API.G_EXCEPTION_ERROR;
801 END IF;
802
803 lp_artv_rec.legal_entity_id := l_legal_entity_id;
804 -- Legal Entity Changes
805
806 IF l_item_description IS NULL OR l_item_description = OKL_API.G_MISS_CHAR THEN
807 lp_artv_rec.new_item_description := l_name ;
808 ELSE
809 lp_artv_rec.new_item_description := l_name||', '||l_item_description;
810 END IF;
811
812 IF (is_debug_statement_on) THEN
813 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_LEASE_LOAN_TRMNT_PVT.validate_contract');
814 END IF;
815
816 OKL_AM_LEASE_LOAN_TRMNT_PVT.validate_contract(
817 p_api_version => p_api_version,
818 p_init_msg_list => OKL_API.G_FALSE,
819 x_return_status => x_return_status,
820 x_msg_count => x_msg_count,
821 x_msg_data => x_msg_data,
822 p_contract_id => l_chr_id,
823 p_control_flag => 'ASSET_RETURN_CREATE',
824 x_contract_status => l_contract_status);
825
826 IF (is_debug_statement_on) THEN
827 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_LEASE_LOAN_TRMNT_PVT.validate_contract, x_return_status: ' || x_return_status);
828 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_contract_status: ' || l_contract_status);
829 END IF;
830
831 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
833 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
834 RAISE OKL_API.G_EXCEPTION_ERROR;
835 END IF;
836
837 --Bug# 3925453: pagarg +++ T and A +++++++ Start ++++++++++
838 IF p_quote_id <> NULL AND p_quote_id <> OKL_API.G_MISS_NUM
839 THEN
840 OPEN l_qte_type_csr(p_quote_id => p_quote_id);
841 FETCH l_qte_type_csr INTO l_qtp_code;
842 CLOSE l_qte_type_csr;
843 END IF;
844
845 IF (is_debug_statement_on) THEN
846 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_qtp_code: ' || l_qtp_code);
847 END IF;
848
849 --Bug# 3925453: pagarg +++ T and A +++++++ End ++++++++++
850
851 --Bug# 3925453: pagarg +++ T and A ++++
852 -- Validating for ARS_CODE
853 -- Valid values for ARS_CODE are SCHEDULED, RETURNED, RELEASE_IN_PROCESS
854 -- If quote type is TER_RELEASE_WO_PURCHASE then ARS_CODE must be RELEASE_IN_PROCESS
855 IF lp_artv_rec.ARS_CODE IS NULL OR lp_artv_rec.ARS_CODE = OKL_API.G_MISS_CHAR THEN
856 lp_artv_rec.ARS_CODE := 'SCHEDULED';
857 ELSIF lp_artv_rec.ARS_CODE NOT IN ('SCHEDULED','RETURNED', 'RELEASE_IN_PROCESS')
858 OR (l_qtp_code = 'TER_RELEASE_WO_PURCHASE' AND lp_artv_rec.ARS_CODE <> 'RELEASE_IN_PROCESS')
859 THEN
860 x_return_status := OKL_API.G_RET_STS_ERROR;
861 -- Asset Return status should be set to Scheduled or Returned.
862 OKL_API.set_message( p_app_name => 'OKL',
863 p_msg_name => 'OKL_AM_INVALID_CREATE_STATUS');
864 RAISE OKL_API.G_EXCEPTION_ERROR;
865 END IF;
866 --rkuttiya added for Loans Repossession
867 -- validate that it is not a Loans Repossession
868 IF (is_debug_statement_on) THEN
869 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_CREATE_QUOTE_PVT.check_repo_quote');
870 END IF;
871
872 l_repo_yn := OKL_AM_CREATE_QUOTE_PVT.check_repo_quote(p_quote_id,
873 lx_return_status);
874 IF (is_debug_statement_on) THEN
875 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_CREATE_QUOTE_PVT.check_repo_quote, lx_return_status: ' || lx_return_status);
876 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_repo_yn: ' || l_repo_yn);
877 END IF;
878
879 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
880 RAISE OKC_API.G_EXCEPTION_ERROR;
881 END IF;
882
883 IF lp_artv_rec.art1_code = 'REPOS_REQUEST' AND l_repo_yn = 'N' THEN
884
885 IF lp_artv_rec.rna_id IS NULL OR lp_artv_rec.rna_id = OKL_API.G_MISS_NUM THEN
886
887 x_return_status := OKL_API.G_RET_STS_ERROR;
888 -- Agent Name is required
889 OKL_API.set_message( p_app_name => 'OKL',
890 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
891 p_token1 => 'PROMPT',
892 p_token1_value => 'rna_id');
893 RAISE OKL_API.G_EXCEPTION_ERROR;
894
895 ELSIF lp_artv_rec.date_repossession_required IS NULL OR lp_artv_rec.date_repossession_required = OKL_API.G_MISS_DATE THEN
896
897 x_return_status := OKL_API.G_RET_STS_ERROR;
898 -- Date Required is required
899 OKL_API.set_message( p_app_name => 'OKL',
900 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
901 p_token1 => 'PROMPT',
902 p_token1_value => 'date_repossession_required');
903 RAISE OKL_API.G_EXCEPTION_ERROR;
904
905 ELSIF lp_artv_rec.date_repossession_actual IS NULL OR lp_artv_rec.date_repossession_actual = OKL_API.G_MISS_DATE THEN
906
907 x_return_status := OKL_API.G_RET_STS_ERROR;
908 -- Date Actual is required
909 OKL_API.set_message( p_app_name => 'OKL',
910 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
911 p_token1 => 'PROMPT',
912 p_token1_value => 'date_repossession_actual');
913 RAISE OKL_API.G_EXCEPTION_ERROR;
914
915 ELSIF lp_artv_rec.date_hold_until IS NULL OR lp_artv_rec.date_hold_until = OKL_API.G_MISS_DATE THEN
916
917 x_return_status := OKL_API.G_RET_STS_ERROR;
918 -- Date Hold Until is required
919 OKL_API.set_message( p_app_name => 'OKL',
920 p_msg_name => 'OKL_AM_REQ_FIELD_ERR',
921 p_token1 => 'PROMPT',
922 p_token1_value => 'date_hold_until');
923 RAISE OKL_API.G_EXCEPTION_ERROR;
924
925 END IF;
926
927 END IF;
928
929 -- SECHAWLA Bug # 2667636 : get the functional and contract currency
930
931 -- get the functional currency
932 l_func_curr_code := okl_am_util_pvt.get_functional_currency;
933 -- get the contract currency
934 l_contract_curr_code := okl_am_util_pvt.get_chr_currency( p_chr_id => l_chr_id);
935
936
937 -- get the floor price
938
939 l_floor_price := get_floor_price(p_chr_id => l_chr_id,
940 p_kle_id => lp_artv_rec.kle_id,
941 x_msg_count => x_msg_count,
942 x_msg_data => x_msg_data,
943 x_return_status => x_return_status);
944
945 IF (is_debug_statement_on) THEN
946 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'x_return_status of get_floor_price: ' || x_return_status);
947 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_func_curr_code: ' || l_func_curr_code);
948 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_contract_curr_code: ' || l_contract_curr_code);
949 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_floor_price: ' || l_floor_price);
950 END IF;
951
952
953 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
954 RAISE floor_price_error;
955 ELSE
956 -- SECHAWLA Bug # 2667636 : added the following logic to convert floor price to functional currency
957
958 -- Formula amounts are in contract currency. For Asset Return transaction, the amounts should be stored in
959 -- functional currency. If the contract currency is different than the functional currency, then convert
960 -- the floor price to functional currency amount
961
962
963
964 IF l_contract_curr_code <> l_func_curr_code THEN
965 -- convert amount to functional currency
966 --SECHAWLA 07-FEB-03 Bug # 2789656 : Added x_return_status parameter to the following procedure call
967 IF (is_debug_statement_on) THEN
968 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling okl_accounting_util.convert_to_functional_currency');
969 END IF;
970 okl_accounting_util.convert_to_functional_currency(
971 p_khr_id => l_chr_id,
972 p_to_currency => l_func_curr_code,
973 p_transaction_date => l_sysdate,
974 p_amount => l_floor_price,
975 x_return_status => x_return_status,
976 x_contract_currency => lx_contract_currency,
977 x_currency_conversion_type => lx_currency_conversion_type,
978 x_currency_conversion_rate => lx_currency_conversion_rate,
979 x_currency_conversion_date => lx_currency_conversion_date,
980 x_converted_amount => lx_converted_amount );
981 IF (is_debug_statement_on) THEN
982 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called okl_accounting_util.convert_to_functional_currency, x_return_status: ' || x_return_status);
983 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_contract_currency: ' || lx_contract_currency);
984 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_currency_conversion_type: ' || lx_currency_conversion_type);
985 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_currency_conversion_rate: ' || lx_currency_conversion_rate);
986 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_currency_conversion_date: ' || lx_currency_conversion_date);
987 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_converted_amount: ' || lx_converted_amount);
988 END IF;
989
990 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
991 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
992 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
993 RAISE OKL_API.G_EXCEPTION_ERROR;
994 END IF;
995
996 lp_artv_rec.floor_price := lx_converted_amount ;
997
998 ELSE
999 lp_artv_rec.floor_price := l_floor_price;
1000 END IF;
1001
1002 END IF;
1003
1004
1005
1006 -- get item price
1007
1008 l_item_price := get_item_price(p_chr_id => l_chr_id,
1009 p_kle_id => lp_artv_rec.kle_id,
1010 x_msg_count => x_msg_count,
1011 x_msg_data => x_msg_data,
1012 x_return_status => x_return_status);
1013
1014 IF (is_debug_statement_on) THEN
1015 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'x_return_status of get_item_price: ' || x_return_status);
1016 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_item_price: ' || l_item_price);
1017 END IF;
1018
1019 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1020 -- SECHAWLA 07-FEB-03 Bug # 2758114 : Do not default item price to floor price. Propogate error if a formula
1021 -- is found and execution returns error.
1022 --lp_artv_rec.new_item_price := l_floor_price;
1023 RAISE item_price_error;
1024 ELSE
1025
1026 -- SECHAWLA 07-FEB-03 Bug # 2758114 : Perform currency conversion if the item price formula executed successfully
1027 -- If the formula was not found, then item price gets defaulted to 0. Bypass currency conversionin this case.
1028 IF l_item_price IS NOT NULL THEN
1029
1030 -- SECHAWLA Bug # 2667636 : added the following logic to convert item price to functional currency
1031 IF l_contract_curr_code <> l_func_curr_code THEN
1032 -- convert amount to functional currency
1033
1034 --SECHAWLA 07-FEB-03 Bug # 2789656 : Added x_return_status parameter to the following procedure call
1035 okl_accounting_util.convert_to_functional_currency(
1036 p_khr_id => l_chr_id,
1037 p_to_currency => l_func_curr_code,
1038 p_transaction_date => l_sysdate ,
1039 p_amount => l_item_price,
1040 x_return_status => x_return_status,
1041 x_contract_currency => lx_contract_currency,
1042 x_currency_conversion_type => lx_currency_conversion_type,
1043 x_currency_conversion_rate => lx_currency_conversion_rate,
1044 x_currency_conversion_date => lx_currency_conversion_date,
1045 x_converted_amount => lx_converted_amount );
1046
1047 IF (is_debug_statement_on) THEN
1048 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called okl_accounting_util.convert_to_functional_currency, x_return_status: ' || x_return_status);
1049 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_contract_currency: ' || lx_contract_currency);
1050 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_currency_conversion_type: ' || lx_currency_conversion_type);
1051 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_currency_conversion_rate: ' || lx_currency_conversion_rate);
1052 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_currency_conversion_date: ' || lx_currency_conversion_date);
1053 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lx_converted_amount: ' || lx_converted_amount);
1054 END IF;
1055
1056 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1057 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1058 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1059 RAISE OKL_API.G_EXCEPTION_ERROR;
1060 END IF;
1061
1062 lp_artv_rec.new_item_price := lx_converted_amount ;
1063
1064 ELSE
1065 lp_artv_rec.new_item_price := l_item_price;
1066 END IF;
1067 ELSE
1068 -- SECHAWLA 07-FEB-03 Bug # 2758114 : Defalut item price to 0 if item price formula not found. In this
1069 -- case funtional currency amount is also assumed to be 0.
1070 lp_artv_rec.new_item_price := 0;
1071 END IF;
1072
1073 END IF;
1074
1075 -- SECHAWLA Bug # 2667636 : populate currency code and currency conversion code
1076 lp_artv_rec.currency_code := l_func_curr_code;
1077 lp_artv_rec.currency_conversion_code := l_func_curr_code;
1078
1079
1080 -- get repurchase agreement
1081
1082 l_repurchase_yn := get_repurchase_agreement( p_chr_id => l_chr_id,
1083 p_kle_id => lp_artv_rec.kle_id,
1084 x_msg_count => x_msg_count,
1085 x_msg_data => x_msg_data,
1086 x_return_status => x_return_status);
1087
1088 IF (is_debug_statement_on) THEN
1089 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'x_return_status of get_repurchase_agreement: ' || x_return_status);
1090 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_repurchase_yn: ' || l_repurchase_yn);
1091 END IF;
1092 lp_artv_rec.repurchase_agmt_yn := l_repurchase_yn;
1093
1094 -- Call AssignRemarker procedure to assign the default remarketer
1095 IF (lp_artv_rec.rmr_id = OKL_API.G_MISS_NUM)
1096 OR (lp_artv_rec.rmr_id IS NULL) THEN
1097
1098 assign_remarketer(
1099 p_kle_id => lp_artv_rec.kle_id,
1100 x_rmr_id => l_rmr_id,
1101 x_return_status => x_return_status);
1102
1103 IF (is_debug_statement_on) THEN
1104 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'x_return_status of assign_remarketer: ' || x_return_status);
1105 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_rmr_id: ' || l_rmr_id);
1106 END IF;
1107
1108 -- Set the message if no default remarketer found
1109 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1110
1111 --SECHAWLA 16-JAN-03 Bug# 2754280: Added the following code to display user profile option name in messages
1112 -- instead of profile option name
1113 l_user_profile_name := okl_am_util_pvt.get_user_profile_option_name(
1114 p_profile_option_name => 'OKL_DEFAULT_REMARKETER',
1115 x_return_status => x_return_status);
1116 IF (is_debug_statement_on) THEN
1117 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'x_return_status of okl_am_util_pvt.get_user_profile_option_name: ' || x_return_status);
1118 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_user_profile_name: ' || l_user_profile_name);
1119 END IF;
1120
1121 IF x_return_status = OKL_API.G_RET_STS_ERROR THEN
1122 --Default Remarketer profile is missing.
1123 OKL_API.set_message( p_app_name => 'OKL',
1124 p_msg_name => 'OKL_AM_NO_DEF_RMK_PROFILE'
1125 );
1126 RAISE okl_api.G_EXCEPTION_ERROR;
1127 ELSIF x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1128 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1129 END IF;
1130 -- SECHAWLA 16-JAN-03 Bug# 2754280 -- end new code
1131
1132
1133 OKL_API.set_message( p_app_name => 'OKL',
1134 p_msg_name => 'OKL_AM_RMK_NO_PROFILE_VALUE',
1135 p_token1 => 'PROFILE',
1136 p_token1_value => l_user_profile_name -- SECHAWLA 16-JAN-03 Bug# 2754280 : Modified to display user profile option name
1137 );
1138 RAISE okl_api.G_EXCEPTION_ERROR;
1139 END IF;
1140
1141 -- Set the default remarketer
1142 lp_artv_rec.rmr_id := l_rmr_id;
1143 END IF;
1144
1145 -- DJANASWA Changes for 'Asset repossession for a loan' project BEGIN
1146 IF (lp_artv_rec.ASSET_FMV_AMOUNT IS NOT NULL
1147 AND lp_artv_rec.ASSET_FMV_AMOUNT <> OKL_API.G_MISS_NUM) THEN
1148 IF lp_artv_rec.ASSET_FMV_AMOUNT < 0 THEN
1149 x_return_status := OKL_API.G_RET_STS_ERROR;
1150 -- Asset FMV Amount cannot be less than zero.
1151
1152 OKL_API.set_message( p_app_name => 'OKL',
1153 p_msg_name => 'OKL_AM_ASSET_FMV_AMT_ERR');
1154
1155 RAISE OKL_API.G_EXCEPTION_ERROR;
1156 END IF;
1157 END IF;
1158 -- DJANASWA Changes for 'Asset repossession for a loan' project END
1159
1160
1161 -- set the defaults if not passed
1162 set_defaults(
1163 px_artv_rec => lp_artv_rec,
1164 x_return_status => x_return_status);
1165
1166 IF (is_debug_statement_on) THEN
1167 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'x_return_status of set_defaults: ' || x_return_status);
1168 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_ASSET_RETURNS_PUB.insert_asset_returns');
1169 END IF;
1170
1171 -- call insert of tapi
1172 OKL_ASSET_RETURNS_PUB.insert_asset_returns(
1173 p_api_version => p_api_version,
1174 p_init_msg_list => OKL_API.G_FALSE,
1175 x_return_status => x_return_status,
1176 x_msg_count => x_msg_count,
1177 x_msg_data => x_msg_data,
1178 p_artv_rec => lp_artv_rec,
1179 x_artv_rec => lx_artv_rec);
1180
1181 IF (is_debug_statement_on) THEN
1182 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_ASSET_RETURNS_PUB.insert_asset_returns, x_return_status: ' || x_return_status);
1183 END IF;
1184
1185 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1186 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1187 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1188 RAISE OKL_API.G_EXCEPTION_ERROR;
1189 END IF;
1190
1191
1192 x_artv_rec := lx_artv_rec;
1193
1194
1195 -- Notify Repossession Agent
1196 --rkuttiya added validation to check that it is not Loans Repossession
1197
1198 IF lp_artv_rec.art1_code = 'REPOS_REQUEST' AND l_repo_yn = 'N' THEN
1199 -- call notify repossession agent wf
1200 IF (is_debug_statement_on) THEN
1201 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'Raising workflow OKL_AM_WF.raise_business_event,oracle.apps.okl.am.notifyrepoagent, lx_artv_rec.id:' || lx_artv_rec.id);
1202 END IF;
1203 OKL_AM_WF.raise_business_event(lx_artv_rec.id,'oracle.apps.okl.am.notifyrepoagent');
1204 END IF;
1205
1206 -- create shipping instructions
1207
1208 lp_rasv_rec.art_id := lx_artv_rec.id;
1209 lp_rasv_rec.trans_option_accepted_yn := 'N';
1210
1211 IF (is_debug_statement_on) THEN
1212 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_SHIPPING_INSTR_PUB.create_shipping_instr');
1213 END IF;
1214 OKL_AM_SHIPPING_INSTR_PUB.create_shipping_instr(p_api_version => p_api_version
1215 ,p_init_msg_list => OKL_API.G_FALSE
1216 ,x_return_status => x_return_status
1217 ,x_msg_count => x_msg_count
1218 ,x_msg_data => x_msg_data
1219 ,p_rasv_rec => lp_rasv_rec
1220 ,x_rasv_rec => lx_rasv_rec);
1221 IF (is_debug_statement_on) THEN
1222 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_SHIPPING_INSTR_PUB.create_shipping_instr, x_return_status: ' || x_return_status);
1223 END IF;
1224
1225 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1228 RAISE OKL_API.G_EXCEPTION_ERROR;
1229 END IF;
1230
1231 IF (is_debug_statement_on) THEN
1232 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'Raising workflow OKL_AM_WF.raise_business_event,oracle.apps.okl.am.notifyremarketer, lx_artv_rec.id:' || lx_artv_rec.id);
1233 END IF;
1234 -- notify remarketer
1235 OKL_AM_WF.raise_business_event(lx_artv_rec.id,'oracle.apps.okl.am.notifyremarketer');
1236
1237
1238 IF (is_debug_statement_on) THEN
1239 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling okl_am_util_pvt.get_rule_record');
1240 END IF;
1241 -- First get the party id from the rule if the custodian is a 3rd party
1242 okl_am_util_pvt.get_rule_record (
1243 p_rgd_code => 'LAAFLG',
1244 p_rdf_code => 'LAFLTL',
1245 p_chr_id => l_chr_id,
1246 p_cle_id => lp_artv_rec.kle_id,
1247 x_rulv_rec => l_rulv_rec,
1248 x_return_status => l_return_status,
1249 p_message_yn => FALSE); -- put error message on stack if there is no rule
1250
1251 IF (is_debug_statement_on) THEN
1252 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called okl_am_util_pvt.get_rule_record, l_return_status: ' || l_return_status);
1253 END IF;
1254
1255 IF l_return_status = OKL_API.G_RET_STS_SUCCESS AND l_rulv_rec.object2_id1 IS NOT NULL AND l_rulv_rec.object2_id1 <> OKL_API.G_MISS_NUM THEN
1256 IF (is_debug_statement_on) THEN
1257 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'Raising workflow OKL_AM_WF.raise_business_event,oracle.apps.okl.am.notifytitleholder, lx_artv_rec.id:' || lx_artv_rec.id);
1258 END IF;
1259 OKL_AM_WF.raise_business_event(lx_artv_rec.id,'oracle.apps.okl.am.notifytitleholder');
1260
1261 END IF;
1262
1263 -- ++++++++++++++++++++ service contract integration begin ++++++++++++++++++
1264
1265 -- RMUNJULU 3061751 27-AUG-2003
1266 -- Check if linked service contract exists for the asset which is returned
1267 IF (is_debug_statement_on) THEN
1268 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed');
1269 END IF;
1270 l_service_int_needed := OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed(
1271 p_asset_id => lp_artv_rec.kle_id,
1272 p_source => 'RETURN');
1273 IF (is_debug_statement_on) THEN
1274 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_LEASE_LOAN_TRMNT_PVT.check_service_k_int_needed');
1275 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_service_int_needed: ' || l_service_int_needed);
1276 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration');
1277 END IF;
1278
1279 -- Do the Service Contract Integration Notification for RETURN
1280 OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration(
1281 p_transaction_id => lp_artv_rec.kle_id,
1282 p_transaction_date => SYSDATE,
1283 p_source => 'RETURN',
1284 p_service_integration_needed => l_service_int_needed);
1285 IF (is_debug_statement_on) THEN
1286 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_LEASE_LOAN_TRMNT_PVT.service_k_integration');
1287 END IF;
1288
1289 -- ++++++++++++++++++++ service contract integration end ++++++++++++++++++
1290
1291
1292 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1293
1294 IF (is_debug_procedure_on) THEN
1295 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
1296 END IF;
1297
1298 EXCEPTION
1299
1300 WHEN floor_price_error THEN
1301 IF (is_debug_exception_on) THEN
1302 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'floor_price_error');
1303 END IF;
1304 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
1305
1306 -- SECHAWLA 07-FEB-03 Bug # 2758114 : Added a new exception
1307 WHEN item_price_error THEN
1308 IF (is_debug_exception_on) THEN
1309 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'item_price_error');
1310 END IF;
1311 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(l_api_name || '_PVT');
1312
1313 WHEN G_EXCEPTION_INSURANCE_ERROR THEN
1314 IF (is_debug_exception_on) THEN
1315 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_INSURANCE_ERROR');
1316 END IF;
1317
1318 -- notify caller of an UNEXPECTED error
1319 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1320
1321 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1322 IF l_okcklinesv_csr%ISOPEN THEN
1323 CLOSE l_okcklinesv_csr;
1324 END IF;
1325
1326 -- RRAVIKIR Legal Entity Changes
1327 IF fetch_legal_entity%ISOPEN THEN
1328 CLOSE fetch_legal_entity;
1329 END IF;
1330 -- Legal Entity Changes End
1331 IF (is_debug_exception_on) THEN
1332 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
1333 END IF;
1334
1335 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1336 (
1337 l_api_name,
1338 G_PKG_NAME,
1339 'OKL_API.G_RET_STS_ERROR',
1340 x_msg_count,
1341 x_msg_data,
1342 '_PVT'
1343 );
1344
1345 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1346 IF l_okcklinesv_csr%ISOPEN THEN
1347 CLOSE l_okcklinesv_csr;
1348 END IF;
1349
1350 -- RRAVIKIR Legal Entity Changes
1351 IF fetch_legal_entity%ISOPEN THEN
1352 CLOSE fetch_legal_entity;
1353 END IF;
1354 -- Legal Entity Changes End
1355 IF (is_debug_exception_on) THEN
1356 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
1357 END IF;
1358
1359 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1360 (
1361 l_api_name,
1362 G_PKG_NAME,
1363 'OKL_API.G_RET_STS_UNEXP_ERROR',
1364 x_msg_count,
1365 x_msg_data,
1366 '_PVT'
1367 );
1368
1369 WHEN OTHERS THEN
1370 IF l_okcklinesv_csr%ISOPEN THEN
1371 CLOSE l_okcklinesv_csr;
1372 END IF;
1373
1374 -- RRAVIKIR Legal Entity Changes
1375 IF fetch_legal_entity%ISOPEN THEN
1376 CLOSE fetch_legal_entity;
1377 END IF;
1378 -- Legal Entity Changes End
1379 IF (is_debug_exception_on) THEN
1380 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1381 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1382 END IF;
1383
1384 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1385 (
1386 l_api_name,
1387 G_PKG_NAME,
1388 'OTHERS',
1389 x_msg_count,
1390 x_msg_data,
1391 '_PVT'
1392 );
1393 END create_asset_return;
1394
1395
1396
1397
1398
1399 -- Start of comments
1400 --
1401 -- Procedure Name : perform_cancellation
1402 -- Description : perform_cancellation
1403 -- Business Rules :
1404 -- Parameters :
1405 -- Version : 1.0
1406 --
1407 -- End of comments
1408 PROCEDURE perform_cancellation IS
1409 BEGIN
1410 NULL;
1411 END ;
1412
1413 -- Start of comments
1414 --
1415 -- Procedure Name : check_asset_status
1416 -- Description : This procedure is used to check the status of the asset line
1417 -- Business Rules :
1418 -- Parameters : p_kle_id - financial asset id
1419 -- p_asset_numbner - asset number
1420 -- Version : 1.0
1421 -- History : SECHAWLA 22-JAN-03 Bug # 2762419 : Created
1422 -- End of comments
1423
1424 PROCEDURE check_asset_status(
1425 p_kle_id IN NUMBER,
1426 p_asset_number IN VARCHAR2,
1427 x_return_status OUT NOCOPY VARCHAR2) AS
1428
1429 -- This cursor to used to check the status of the asset line
1430 CURSOR l_okclines_csr IS
1431 SELECT sts_code
1432 FROM okc_k_lines_b
1433 WHERE id = p_kle_id;
1434
1435 l_sts_code okc_k_lines_b.sts_code%TYPE;
1436 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'check_asset_status';
1437 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1438 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1439 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1440 BEGIN
1441
1442 IF (is_debug_procedure_on) THEN
1443 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
1444 END IF;
1445 IF (is_debug_statement_on) THEN
1446 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, p_kle_id:' || p_kle_id);
1447 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, p_asset_number:' || p_asset_number);
1448 END IF;
1449
1450 OPEN l_okclines_csr;
1451 FETCH l_okclines_csr INTO l_sts_code;
1452 IF l_okclines_csr%NOTFOUND THEN
1453 x_return_status := OKL_API.G_RET_STS_ERROR;
1454 -- Kle ID is invalid
1455 OKL_API.set_message(
1456 p_app_name => 'OKC',
1457 p_msg_name => G_INVALID_VALUE,
1458 p_token1 => G_COL_NAME_TOKEN,
1459 p_token1_value => 'KLE_ID');
1460
1461 END IF;
1462 CLOSE l_okclines_csr;
1463
1464 IF l_sts_code NOT IN ( 'TERMINATED','EXPIRED') THEN
1465 x_return_status := OKL_API.G_RET_STS_ERROR;
1466 -- Asset ASSET_NUMBER is still STATUS. Asset should be terminated or expired.
1467 OKL_API.set_message(
1468 p_app_name => 'OKL',
1469 p_msg_name => 'OKL_AM_ASSET_NOT_TERMINATED',
1470 p_token1 => 'ASSET_NUMBER',
1471 p_token1_value => p_asset_number,
1472 p_token2 => 'STATUS',
1473 p_token2_value => l_sts_code);
1474
1475 END IF;
1476
1477 IF (is_debug_procedure_on) THEN
1478 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
1479 END IF;
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483
1484 IF l_okclines_csr%ISOPEN THEN
1485 CLOSE l_okclines_csr;
1486 END IF;
1487 IF (is_debug_exception_on) THEN
1488 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1489 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1490 END IF;
1491
1492 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1493 OKC_API.set_message(p_app_name => 'OKC',
1494 p_msg_name => g_unexpected_error,
1495 p_token1 => g_sqlcode_token,
1496 p_token1_value => sqlcode,
1497 p_token2 => g_sqlerrm_token,
1498 p_token2_value => sqlerrm);
1499 END check_asset_status;
1500
1501 -- Start of comments
1502 --
1503 -- Procedure Name : update_asset_return
1504 -- Description : This procedure is used to update an Asset Return record
1505 -- Business Rules :
1506 -- Parameters :
1507 -- Version : 1.0
1508 -- History : SECHAWLA 16-JAN-03 Bug # 2754280 : Removed DEFAULT hint from procedure parameters
1509 -- SECHAWLA 22-JAN-03 Bug # 2762419 : Modified certain validations that were checking for a
1510 -- Terminated/Expired contracts, to instead check for Terminated/Expired asset line.
1511 -- PAGARG 28-SEP-04 Bug 3918852: Pass meaning as tokens
1512 -- to error message instead of lookup code
1513 -- SECHAWLA 04-OCT-04 3924244 : Validate the new item number entered by the user. Call the
1514 -- Remarketing API or WF depending upon the Remarketing setup
1515 -- SECHAWLA 29-OCT-04 3924244 : preserve the new_item_number and imr_id updated by the custom
1516 -- remk WF during update
1517 -- SECHAWLA 10-NOV-04 4000128 : Added a message
1518 -- SECHAWLA 18-JAN-04 4125635 : Added an additional check before updating the new item number
1519 -- during the custom flow
1520 -- SECHAWLA 23-MAR-05 4241558 : Removed the item price > 0 validation
1521 -- SECHAWLA 24-MAR-05 4241558 : Added validation to check -ve item price
1522 -- nikshah -- Bug # 5484903 Fixed,
1523 -- Changed CURSOR l_repurchasetasset_csr(p_art_id NUMBER) SQL definition
1524 -- DJANASWA 13-Nov-2007 Changes for 'Asset repossession for a loan' project
1525 -- Added validation for ASSET_FMV_AMOUNT column
1526
1527 -- End of comments
1528 PROCEDURE update_asset_return(
1529 p_api_version IN NUMBER,
1530 p_init_msg_list IN VARCHAR2,
1531 x_return_status OUT NOCOPY VARCHAR2,
1532 x_msg_count OUT NOCOPY NUMBER,
1533 x_msg_data OUT NOCOPY VARCHAR2,
1534 p_artv_rec IN artv_rec_type,
1535 x_artv_rec OUT NOCOPY artv_rec_type) AS
1536
1537 lp_artv_rec artv_rec_type := p_artv_rec;
1538 lx_artv_rec artv_rec_type;
1539 l_floor_amt NUMBER := OKL_API.G_MISS_NUM;
1540 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1541 l_api_name CONSTANT VARCHAR2(30) := 'update_asset_return';
1542 l_inventory_item_id NUMBER := OKL_API.G_MISS_NUM;
1543 l_Item_Price NUMBER := OKL_API.G_MISS_NUM;
1544 l_New_Item_Number VARCHAR2(25);
1545 l_New_Item_Id NUMBER := OKL_API.G_MISS_NUM;
1546 l_quantity NUMBER;
1547 l_api_version CONSTANT NUMBER := 1;
1548 l_current_db_status VARCHAR2(30);
1549 l_kle_id NUMBER;
1550 l_chr_id NUMBER;
1551 l_contract_status VARCHAR2(30);
1552 l_quote_id NUMBER;
1553 l_accepted_yn VARCHAR2(1);
1554 i NUMBER;
1555 l_total_quantity NUMBER;
1556 l_item_number VARCHAR2(40);
1557 l_meaning fnd_lookups.meaning%TYPE := NULL;
1558 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'update_asset_return';
1559 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1560 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1561 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1562
1563
1564
1565 -- This cursor is used to get the quantity for the inventory Item
1566 --SECHAWLA 22-JAN-03 Bug # 2762419 : Added asset_number for displaying in new messages
1567 --start changed by abhsaxen for Bug#6174484
1568 CURSOR l_assetreturnuv_csr(p_id NUMBER) IS
1569 SELECT kle.name asset_number,
1570 cim.number_of_items quantity,
1571 oar.ars_code ars_code,
1572 kle.id kle_id,
1573 kle.chr_id chr_id,
1574 msi.segment1 inventory_item_number
1575 FROM okl_k_lines_full_v kle,
1576 okl_asset_returns_all_b oar,
1577 mtl_system_items_b msi,
1578 okc_k_lines_b kle2,
1579 okc_line_styles_b lse,
1580 okc_k_items cim
1581 WHERE oar.kle_id = kle.id
1582 AND oar.imr_id = msi.inventory_item_id(+)
1583 AND kle.id = kle2.cle_id
1584 AND kle2.lse_id = lse.id
1585 AND lse.lty_code = 'ITEM'
1586 AND kle2.id = cim.cle_id
1587 AND oar.id = p_id;
1588 --end changed by abhsaxen for Bug#6174484
1589
1590 -- This cursor is used to validate that a BOOKED order exists when asset retun status changes to 'REMARKETED'. It also
1591 -- validates ordered_quantity with asset return quantity
1592 CURSOR l_assetsaleuv_csr(p_art_id NUMBER) IS
1593 SELECT art_id, ordered_quantity
1594 FROM okl_am_asset_sale_uv
1595 WHERE art_id = p_art_id
1596 AND order_status = 'BOOKED';
1597
1598 -- This cursor is used to get an accepted repurchase quote for an asset
1599 CURSOR l_repurchasetasset_csr(p_art_id NUMBER) IS
1600 SELECT OTQB.ID QUOTE_ID , OTQB.ACCEPTED_YN ACCEPTED_YN
1601 FROM OKL_TRX_QUOTES_B OTQB ,
1602 OKL_ASSET_RETURNS_B OAR
1603 WHERE OTQB.ART_ID = OAR.ID
1604 AND OTQB.ART_ID = p_art_id;
1605
1606 --SECHAWLA 22-JAN-03 Bug # 2762419 : new declarations
1607 l_asset_number okl_am_asset_returns_uv.asset_number%TYPE;
1608
1609 --Bug 3918852 fix starts
1610 --define the cursor to obtain meaning for a given lookup type and code
1611 CURSOR l_lookup_meaning_csr(p_lookup_type VARCHAR2, p_lookup_code VARCHAR2)
1612 IS
1613 SELECT meaning
1614 FROM fnd_lookups
1615 WHERE lookup_type = p_lookup_type
1616 AND lookup_code = p_lookup_code;
1617
1618 l_asset_return_type fnd_lookups.meaning%TYPE;
1619 l_asset_return_status fnd_lookups.meaning%TYPE;
1620 --Bug 3918852 fix ends
1621
1622 -- SECHAWLA 30-SEP-04 3924244 : New declarations begin
1623
1624 -- check if item already exists in inventory
1625
1626 CURSOR l_mtlsystemitems_csr(cp_inv_item_number IN VARCHAR2) IS
1627 SELECT 'x'
1628 FROM MTL_SYSTEM_ITEMS_B
1629 WHERE segment1 = cp_inv_item_number;
1630
1631 -- check the Remarketing flow option from the setup
1632 CURSOR l_systemparamsall_csr IS
1633 SELECT REMK_PROCESS_CODE
1634 FROM OKL_SYSTEM_PARAMS ;
1635
1636 -- get the wf display name
1637 CURSOR l_get_wf_details_csr (c_event_name VARCHAR2) IS
1638 SELECT IT.display_name
1639 , RP.display_name
1640 FROM WF_EVENTS WFEV,
1641 WF_EVENT_SUBSCRIPTIONS WFES,
1642 wf_runnable_processes_v RP,
1643 wf_item_types_vl IT
1644 WHERE WFEV.guid = WFES.event_filter_guid
1645 AND WFES.WF_PROCESS_TYPE = RP.ITEM_TYPE
1646 AND WFES.WF_PROCESS_NAME = RP.PROCESS_NAME
1647 AND RP.ITEM_TYPE = IT.NAME
1648 AND WFEV.NAME = c_event_name;
1649
1650 l_wf_desc VARCHAR2(100);
1651 l_process_desc VARCHAR2(100);
1652 l_item_cnt NUMBER := 0;
1653 l_remk_process VARCHAR2(15);
1654 -- SECHAWLA 30-SEP-04 3924244 : New declarations end
1655
1656 -- SECHAWLA 29-OCT-04 3924244 : new declaraions
1657 CURSOR l_assetreturn_csr(cp_id IN NUMBER) IS
1658 SELECT imr_id, new_item_number
1659 FROM okl_asset_returns_b
1660 WHERE id = cp_id;
1661
1662 l_wf_imr_id NUMBER;
1663 l_wf_new_item_number VARCHAR2(40);
1664
1665 l_custom_rmk_wf VARCHAR2(1) := 'N';
1666 -- SECHAWLA 29-OCT-04 3924244 : new declaraions end
1667
1668 l_dummy VARCHAR2(1);
1669
1670 -- RRAVIKIR Legal Entity Changes
1671 CURSOR fetch_legal_entity(p_khr_id NUMBER) IS
1672 SELECT legal_entity_id, deal_type --6674730
1673 FROM okl_k_headers
1674 WHERE id = p_khr_id;
1675
1676 l_legal_entity_id NUMBER;
1677 -- Legal Entity Changes
1678 -- Bug 6674730 start
1679 CURSOR l_okclines_csr(p_kle_id NUMBER) IS
1680 SELECT sts_code
1681 FROM okc_k_lines_b
1682 WHERE id = p_kle_id;
1683
1684 l_sts_code okc_k_lines_b.sts_code%TYPE;
1685 l_deal_type VARCHAR2(150);
1686 -- Bug 6674730 end
1687
1688 -- rmunjulu 6674730
1689 CURSOR l_mtl_instance_csr (p_kle_id NUMBER) IS
1690 SELECT cle.id ID,
1691 mtlb.description item_description,
1692 mtlb.asset_category_id asset_category_id,
1693 hrou.name organization_name
1694 FROM OKC_K_LINES_B CLE,
1695 OKC_K_ITEMS CIM,
1696 OKC_LINE_STYLES_B LSE,
1697 MTL_SYSTEM_ITEMS_B MTLB ,
1698 OKC_K_LINES_B KLE,
1699 HR_OPERATING_UNITS HROU
1700 WHERE kle.id = p_kle_id
1701 AND cle.cle_id = kle.id
1702 AND cle.id = cim.cle_id
1703 AND cle.lse_id = lse.id
1704 AND lse.lty_code = 'ITEM'
1705 AND cim.dnz_chr_id = cle.dnz_chr_id
1706 AND cim.object1_id1 = mtlb.inventory_item_id
1707 AND cim.object1_id2 = to_char(mtlb.organization_id)
1708 AND mtlb.organization_id = hrou.organization_id;
1709
1710 l_mtl_instance_rec l_mtl_instance_csr%ROWTYPE;
1711
1712 -- bug 6674730 get the operational options setup
1713 CURSOR l_operational_csr IS
1714 SELECT syp.asst_add_book_type_code corp_book,
1715 syp.tax_book_1 tax_book_1,
1716 syp.fa_location_id fa_location_id
1717 FROM OKL_SYSTEM_PARAMS SYP;
1718
1719 l_operational_rec l_operational_csr%ROWTYPE;
1720 l_error VARCHAR2(3) := 'N';
1721
1722
1723 BEGIN
1724 IF (is_debug_procedure_on) THEN
1725 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
1726 END IF;
1727 IF (is_debug_statement_on) THEN
1728 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.id:' || lp_artv_rec.id);
1729 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.kle_id:' || lp_artv_rec.kle_id);
1730 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.legal_entity_id:' || lp_artv_rec.legal_entity_id);
1731 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.new_item_description:' || lp_artv_rec.new_item_description);
1732 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.ARS_CODE:' || lp_artv_rec.ARS_CODE);
1733 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.art1_code:' || lp_artv_rec.art1_code);
1734 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.rna_id:' || lp_artv_rec.rna_id);
1735 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.date_repossession_required:' || lp_artv_rec.date_repossession_required);
1736 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.date_repossession_actual:' || lp_artv_rec.date_repossession_actual);
1737 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.date_hold_until:' || lp_artv_rec.date_hold_until);
1738 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.floor_price:' || lp_artv_rec.floor_price);
1739 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.new_item_price:' || lp_artv_rec.new_item_price);
1740 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.currency_code:' || lp_artv_rec.currency_code);
1741 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.currency_conversion_code:' || lp_artv_rec.currency_conversion_code);
1742 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.repurchase_agmt_yn:' || lp_artv_rec.repurchase_agmt_yn);
1743 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.rmr_id:' || lp_artv_rec.rmr_id);
1744 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.ASSET_FMV_AMOUNT:' || lp_artv_rec.ASSET_FMV_AMOUNT);
1745 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.legal_entity_id:' || lp_artv_rec.legal_entity_id);
1746 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.imr_id:' || lp_artv_rec.imr_id);
1747 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.attribute14:' || lp_artv_rec.attribute14);
1748 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'In param, lp_artv_rec.new_item_number:' || lp_artv_rec.new_item_number);
1749 END IF;
1750
1751 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
1752 G_PKG_NAME,
1753 p_init_msg_list,
1754 l_api_version,
1755 p_api_version,
1756 '_PVT',
1757 x_return_status);
1758
1759 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1760 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1761 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1762 RAISE OKL_API.G_EXCEPTION_ERROR;
1763 END IF;
1764
1765 IF lp_artv_rec.id IS NULL OR lp_artv_rec.id = OKL_API.G_MISS_NUM THEN
1766 x_return_status := OKL_API.G_RET_STS_ERROR;
1767 -- Asset Return ID is required
1768 OKL_API.set_message( p_app_name => 'OKC',
1769 p_msg_name => G_REQUIRED_VALUE,
1770 p_token1 => G_COL_NAME_TOKEN,
1771 p_token1_value => 'ASSET_RETURN_ID');
1772 RAISE OKL_API.G_EXCEPTION_ERROR;
1773 END IF;
1774
1775 OPEN l_assetreturnuv_csr(lp_artv_rec.id);
1776 FETCH l_assetreturnuv_csr INTO l_asset_number, l_quantity, l_current_db_status, l_kle_id, l_chr_id, l_item_number;
1777 IF l_assetreturnuv_csr%NOTFOUND THEN
1778 x_return_status := OKL_API.G_RET_STS_ERROR;
1779 -- Invalid Asset Return ID
1780 OKL_API.set_message( p_app_name => 'OKC',
1781 p_msg_name => G_INVALID_VALUE,
1782 p_token1 => G_COL_NAME_TOKEN,
1783 p_token1_value => 'ASSET_RETURN_ID');
1784 RAISE OKL_API.G_EXCEPTION_ERROR;
1785 END IF;
1786 CLOSE l_assetreturnuv_csr;
1787
1788 -- RRAVIKIR Legal Entity Changes
1789 OPEN fetch_legal_entity(p_khr_id => l_chr_id);
1790 FETCH fetch_legal_entity INTO l_legal_entity_id, l_deal_type; --6674730
1791 CLOSE fetch_legal_entity;
1792
1793 IF (is_debug_statement_on) THEN
1794 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_asset_number :'||l_asset_number);
1795 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_quantity :'||l_quantity);
1796 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_current_db_status :'||l_current_db_status);
1797 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_kle_id :'||l_kle_id);
1798 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_chr_id :'||l_chr_id);
1799 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_item_number :'||l_item_number);
1800 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_legal_entity_id :'||l_legal_entity_id);
1801 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_deal_type :'||l_deal_type);
1802 END IF;
1803
1804 IF (l_legal_entity_id is null or l_legal_entity_id = OKC_API.G_MISS_NUM) THEN
1805 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1806 p_msg_name => g_required_value,
1807 p_token1 => g_col_name_token,
1808 p_token1_value => 'legal_entity_id');
1809 RAISE OKC_API.G_EXCEPTION_ERROR;
1810 END IF;
1811
1812 lp_artv_rec.legal_entity_id := l_legal_entity_id;
1813 -- Legal Entity Changes
1814
1815 -- If db status is "available for sale" it can only change to "REMARKETED" or remain "AVAILABLE_FOR_SALE"
1816 IF l_current_db_status = 'AVAILABLE_FOR_SALE' THEN
1817 IF lp_artv_rec.ARS_CODE NOT IN ('AVAILABLE_FOR_SALE', 'REMARKETED') THEN
1818 x_return_status := OKL_API.G_RET_STS_ERROR;
1819
1820 l_meaning := okl_am_util_pvt.get_lookup_meaning(
1821 p_lookup_type => 'OKL_ASSET_RETURN_STATUS',
1822 p_lookup_code => l_current_db_status);
1823
1824 --Can not change the asset status when it is already Available For Sale.
1825 OKL_API.set_message( p_app_name => 'OKL',
1826 p_msg_name => 'OKL_AM_NO_STATUS_CHANGE',
1827 p_token1 => 'DB_STATUS',
1828 p_token1_value => l_meaning);
1829 RAISE OKL_API.G_EXCEPTION_ERROR;
1830 END IF;
1831 END IF;
1832
1833 -- If db status is scrapped
1834 IF l_current_db_status = 'SCRAPPED' THEN
1835 IF lp_artv_rec.ARS_CODE <> 'SCRAPPED' THEN
1836 x_return_status := OKL_API.G_RET_STS_ERROR;
1837
1838 l_meaning := okl_am_util_pvt.get_lookup_meaning(
1839 p_lookup_type => 'OKL_ASSET_RETURN_STATUS',
1840 p_lookup_code => l_current_db_status);
1841
1842 --Can not change the asset status when it is SCRAPPED.
1843 OKL_API.set_message( p_app_name => 'OKL',
1844 p_msg_name => 'OKL_AM_NO_STATUS_CHANGE',
1845 p_token1 => 'DB_STATUS',
1846 p_token1_value => l_meaning);
1847 RAISE OKL_API.G_EXCEPTION_ERROR;
1848 END IF;
1849 END IF;
1850
1851 -- If db status is CANCELLED
1852 IF l_current_db_status = 'CANCELLED' THEN
1853 IF lp_artv_rec.ARS_CODE <> 'CANCELLED' THEN
1854 x_return_status := OKL_API.G_RET_STS_ERROR;
1855
1856 l_meaning := okl_am_util_pvt.get_lookup_meaning(
1857 p_lookup_type => 'OKL_ASSET_RETURN_STATUS',
1858 p_lookup_code => l_current_db_status);
1859
1860
1861 --Can not change the asset status when it is 'CANCELLED'
1862 OKL_API.set_message( p_app_name => 'OKL',
1863 p_msg_name => 'OKL_AM_NO_STATUS_CHANGE',
1864 p_token1 => 'DB_STATUS',
1865 p_token1_value => l_meaning);
1866 RAISE OKL_API.G_EXCEPTION_ERROR;
1867 END IF;
1868 END IF;
1869
1870 -- if db status is REMARKETED
1871 IF l_current_db_status = 'REMARKETED' THEN
1872 IF lp_artv_rec.ARS_CODE <> 'REMARKETED' THEN
1873 x_return_status := OKL_API.G_RET_STS_ERROR;
1874
1875 l_meaning := okl_am_util_pvt.get_lookup_meaning(
1876 p_lookup_type => 'OKL_ASSET_RETURN_STATUS',
1877 p_lookup_code => l_current_db_status);
1878
1879
1880 --Can not change the asset status when it is 'REMARKETED'
1881 OKL_API.set_message( p_app_name => 'OKL',
1882 p_msg_name => 'OKL_AM_NO_STATUS_CHANGE',
1883 p_token1 => 'DB_STATUS',
1884 p_token1_value => l_meaning);
1885 RAISE OKL_API.G_EXCEPTION_ERROR;
1886 END IF;
1887 END IF;
1888
1889 -- if db status is REPURCHASE
1890 IF l_current_db_status = 'REPURCHASE' THEN
1891 IF lp_artv_rec.ARS_CODE <> 'REPURCHASE' THEN
1892 x_return_status := OKL_API.G_RET_STS_ERROR;
1893
1894 l_meaning := okl_am_util_pvt.get_lookup_meaning(
1895 p_lookup_type => 'OKL_ASSET_RETURN_STATUS',
1896 p_lookup_code => l_current_db_status);
1897
1898 --Can not change the asset status when it is 'REPURCHASE'
1899 OKL_API.set_message( p_app_name => 'OKL',
1900 p_msg_name => 'OKL_AM_NO_STATUS_CHANGE',
1901 p_token1 => 'DB_STATUS',
1902 p_token1_value => l_meaning);
1903 RAISE OKL_API.G_EXCEPTION_ERROR;
1904 END IF;
1905 END IF;
1906
1907 IF l_quantity IS NULL OR l_quantity = OKL_API.G_MISS_NUM THEN
1908 x_return_status := OKL_API.G_RET_STS_ERROR;
1909 -- Quantity is required
1910 OKL_API.set_message( p_app_name => 'OKC',
1911 p_msg_name => G_REQUIRED_VALUE,
1912 p_token1 => G_COL_NAME_TOKEN,
1913 p_token1_value => 'QUANTITY');
1914 RAISE OKL_API.G_EXCEPTION_ERROR;
1915 END IF;
1916
1917 -- l_kle_id and l_current_db_status can not be null
1918
1919
1920 -- If status is changing to 'AVAILABLE FOR SALE'
1921 IF l_current_db_status <> 'AVAILABLE_FOR_SALE' THEN
1922
1923 IF ((lp_artv_rec.imr_id IS NULL OR lp_artv_rec.imr_id = OKL_API.G_MISS_NUM)
1924 AND (lp_artv_rec.ARS_CODE = 'AVAILABLE_FOR_SALE') ) THEN
1925
1926 -- when the status first changes to Av for sale, make sure that the asset is terminated or expired.
1927
1928 -- SECHAWLA 22-JAN-03 Bug # 2762419 : call the new procedure to check that the asset line is terminated/expired
1929 check_asset_status(p_kle_id => l_kle_id,
1930 p_asset_number => l_asset_number,
1931 x_return_status => x_return_status);
1932
1933 IF (is_debug_statement_on) THEN
1934 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called check_asset_status, x_return_status: '||x_return_status);
1935 END IF;
1936 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1937 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1938 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1939 RAISE OKL_API.G_EXCEPTION_ERROR;
1940 END IF;
1941
1942 -- SECHAWLA 22-JAN-03 Bug # 2762419 : end modification
1943
1944 -- SECHAWLA 30-SEP-04 3924244 : begin
1945 -- If the user enters an item number, then validate to ensure that item does not already exist
1946 -- in inventory
1947 -- 03-DEC-04 SECHAWLA 4047159 : Added validation for duplicate item number
1948 IF p_artv_rec.new_item_number IS NOT NULL THEN
1949 OPEN l_mtlsystemitems_csr(p_artv_rec.new_item_number);
1950 FETCH l_mtlsystemitems_csr INTO l_dummy;
1951 IF l_mtlsystemitems_csr%FOUND THEN
1952 --Item number ITEM_NUMBER already exists in Inventory. Please enter another item number.
1953 OKL_API.set_message( p_app_name => 'OKL',
1954 p_msg_name => 'OKL_AM_ITEM_ALREADY_EXISTS',
1955 p_token1 => 'ITEM_NUMBER',
1956 p_token1_value => p_artv_rec.new_item_number);
1957 x_return_status := OKL_API.G_RET_STS_ERROR;
1958 RAISE OKL_API.G_EXCEPTION_ERROR;
1959 END IF;
1960 CLOSE l_mtlsystemitems_csr;
1961
1962
1963 END IF;
1964
1965 -- Check the remarketing flow setup
1966 OPEN l_systemparamsall_csr;
1967 FETCH l_systemparamsall_csr INTO l_remk_process;
1968 IF l_systemparamsall_csr%NOTFOUND THEN
1969 -- Remarketing options are not setup for this operating unit.
1970 OKL_API.set_message(
1971 p_app_name => 'OKL',
1972 p_msg_name => 'OKL_AM_NO_REMK_SETUP');
1973 x_return_status := OKL_API.G_RET_STS_ERROR;
1974 RAISE OKL_API.G_EXCEPTION_ERROR;
1975 END IF;
1976 CLOSE l_systemparamsall_csr;
1977
1978 IF (is_debug_statement_on) THEN
1979 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_remk_process: '||l_remk_process);
1980 END IF;
1981
1982 IF l_remk_process IS NULL THEN
1983 -- Remarketing process is not setup for this operating unit.
1984 OKL_API.set_message(
1985 p_app_name => 'OKL',
1986 p_msg_name => 'OKL_AM_NO_REMK_PROCESS');
1987 x_return_status := OKL_API.G_RET_STS_ERROR;
1988 RAISE OKL_API.G_EXCEPTION_ERROR;
1989 END IF;
1990
1991 -- SECHAWLA 30-SEP-04 3924244 : end
1992
1993 IF p_artv_rec.new_item_description IS NULL OR p_artv_rec.new_item_description = OKL_API.G_MISS_CHAR THEN
1994 x_return_status := OKL_API.G_RET_STS_ERROR;
1995 -- Item Description is required
1996 OKL_API.set_message(
1997 p_app_name => 'OKC',
1998 p_msg_name => G_REQUIRED_VALUE,
1999 p_token1 => G_COL_NAME_TOKEN,
2000 p_token1_value => 'Item Description');
2001 RAISE OKL_API.G_EXCEPTION_ERROR;
2002 END IF;
2003
2004 IF p_artv_rec.new_Item_Price IS NULL OR p_artv_rec.new_Item_Price = OKL_API.G_MISS_NUM THEN
2005 x_return_status := OKL_API.G_RET_STS_ERROR;
2006 -- Item Price is required
2007 OKL_API.set_message(
2008 p_app_name => 'OKC',
2009 p_msg_name => G_REQUIRED_VALUE,
2010 p_token1 => G_COL_NAME_TOKEN,
2011 p_token1_value => 'Item Price');
2012 RAISE OKL_API.G_EXCEPTION_ERROR;
2013 END IF;
2014
2015
2016 -- SECHAWLA 29-OCT-04 3924244 : item price should not be zero
2017 -- IF p_artv_rec.new_Item_Price = 0 THEN -- SECHAWLA 23-MAR-05 4241558 : Removed the item price validation
2018 IF p_artv_rec.new_Item_Price < 0 THEN -- SECHAWLA 24-MAR-05 4241558 : do not allow -ve item price
2019 x_return_status := OKL_API.G_RET_STS_ERROR;
2020 -- Item Price is invalid
2021 OKL_API.set_message(
2022 p_app_name => 'OKC',
2023 p_msg_name => G_INVALID_VALUE,
2024 p_token1 => G_COL_NAME_TOKEN,
2025 p_token1_value => 'Item Price');
2026 RAISE OKL_API.G_EXCEPTION_ERROR;
2027 END IF;
2028
2029
2030 -- SECHAWLA 04-OCT-04 3924244 : Call the following API if the Remarketing process is setup as "Standard"
2031 -- Pass the item number entered by the user. Item number may be NULL (if user does not enter)
2032 IF l_remk_process = 'STANDARD' THEN
2033 IF (is_debug_statement_on) THEN
2034 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_REMARKET_ASSET_PUB.create_rmk_item');
2035 END IF;
2036
2037 OKL_AM_REMARKET_ASSET_PUB.create_rmk_item(
2038 p_api_version => p_api_version,
2039 p_init_msg_list => OKL_API.G_FALSE,
2040 p_item_number => p_artv_rec.new_item_number, -- SECHAWLA 04-OCT-04 3924244 : added this parameter
2041 p_Item_Description => p_artv_rec.new_item_description,
2042 p_Item_Price => p_artv_rec.new_Item_Price,
2043 p_quantity => l_quantity,
2044 x_return_status => x_return_status,
2045 x_msg_count => x_msg_count,
2046 x_msg_data => x_msg_data,
2047 x_New_Item_Number => l_New_Item_Number,
2048 x_New_Item_Id => l_New_Item_id);
2049 IF (is_debug_statement_on) THEN
2050 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_REMARKET_ASSET_PUB.create_rmk_item, x_return_status: '|| x_return_status);
2051 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_New_Item_Number: '|| l_New_Item_Number);
2052 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_New_Item_id: '|| l_New_Item_id);
2053 END IF;
2054
2055 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2056 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2057 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2058 RAISE OKL_API.G_EXCEPTION_ERROR;
2059 END IF;
2060
2061 -- Message Name: OKL_AM_RETURN_INVENTORY_ERROR
2062 -- Message Text: Error Processing Inventory Return
2063 IF l_New_Item_id = OKL_API.G_MISS_NUM THEN
2064 x_return_status := OKL_API.G_RET_STS_ERROR;
2065 --Error creating Inventory item for remarketing.
2066 OKL_API.set_message( p_app_name => 'OKL',
2067 p_msg_name =>'OKL_AM_NO_INVENTORY_ITEM');
2068 RAISE OKL_API.G_EXCEPTION_ERROR;
2069 END IF;
2070
2071 -- set the item_id and Item_name
2072 lp_artv_rec.imr_id := l_new_item_id;
2073 lp_artv_rec.attribute14 := l_New_Item_Number; -- not being used, setting in base table
2074
2075
2076 -- SECHAWLA 04-OCT-04 3924244 : If item number is automatically generated, then
2077 -- populate new col new_item_number with item number
2078 IF p_artv_rec.new_item_number IS NULL THEN -- user did not enter item no.
2079 lp_artv_rec.new_item_number := l_New_Item_Number;
2080 END IF;
2081
2082 -- SECHAWLA 10-NOV-04 4000128 : added the following message
2083 OKL_API.set_message(p_app_name => 'OKL',
2084 p_msg_name => 'OKL_CONFIRM_UPDATE');
2085
2086 -- SECHAWLA 04-OCT-04 3924244 : For Custom Remarketing Process, launch a WF
2087 ELSIF l_remk_process = 'CUSTOM' THEN
2088 -- Raise event to launch the service k integration workflow
2089 IF (is_debug_statement_on) THEN
2090 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_REMARKET_ASSET_WF.RAISE_RMK_CUSTOM_PROCESS_EVENT');
2091 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'lp_artv_rec.id: '|| lp_artv_rec.id);
2092 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'p_artv_rec.new_item_number: '|| p_artv_rec.new_item_number);
2093 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'p_artv_rec.new_item_description: '|| p_artv_rec.new_item_description);
2094 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'p_artv_rec.new_Item_Price: '|| p_artv_rec.new_Item_Price);
2095 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'l_quantity: '|| l_quantity);
2096 END IF;
2097
2098 OKL_AM_REMARKET_ASSET_WF.RAISE_RMK_CUSTOM_PROCESS_EVENT(
2099 p_asset_return_id => lp_artv_rec.id,
2100 p_item_number => p_artv_rec.new_item_number,
2101 p_Item_Description => p_artv_rec.new_item_description,
2102 p_Item_Price => p_artv_rec.new_Item_Price,
2103 p_quantity => l_quantity);
2104 IF (is_debug_statement_on) THEN
2105 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_REMARKET_ASSET_WF.RAISE_RMK_CUSTOM_PROCESS_EVENT');
2106 END IF;
2107
2108 l_custom_rmk_wf := 'Y'; -- SECHAWLA 29-OCT-04 3924244 added
2109
2110 OPEN l_get_wf_details_csr('oracle.apps.okl.am.remkcustomflow');
2111 FETCH l_get_wf_details_csr INTO l_wf_desc, l_process_desc;
2112 IF l_get_wf_details_csr%found THEN
2113
2114 OKL_API.set_message(p_app_name => 'OKL',
2115 p_msg_name => 'OKL_AM_REMK_WF_RAISED',
2116 p_token1 => 'ITEM_DESC',
2117 p_token1_value => l_wf_desc);
2118 END IF;
2119 CLOSE l_get_wf_details_csr;
2120
2121 END IF;
2122 END IF;
2123 END IF;
2124
2125 --------------Repossession------------------------
2126 IF (lp_artv_rec.ARS_CODE = 'REPOSSESSED' AND l_current_db_status <> 'REPOSSESSED') OR
2127 (lp_artv_rec.ARS_CODE = 'UNSUCCESS_REPO' AND l_current_db_status <> 'UNSUCCESS_REPO') THEN
2128
2129 -- MDOKAL Bug 2883292, changed = to <>
2130 IF lp_artv_rec.art1_code <> 'REPOS_REQUEST' THEN
2131
2132 -- rmunjulu Bug 6674730 check if loan and MANUAL then allow
2133 IF l_deal_type = 'LOAN' AND lp_artv_rec.art1_code = 'NOTIFY_OF_INTENT_TO_RETURN' THEN
2134
2135 null;
2136 ELSE
2137
2138 -- send notification to the user who created the notification request
2139
2140 -- MDOKAL Bug 2883292, No longer called here
2141 -- okl_am_wf.raise_business_event(lp_artv_rec.id,'oracle.apps.okl.am.notifycollections');
2142
2143 -- MDOKAL Bug 2883292, Changed condition
2144 -- ELSE
2145 x_return_status := OKL_API.G_RET_STS_ERROR;
2146 --Repossession status ARS_CODE is invalid for asset return type ART1_CODE.
2147
2148 --Bug 3918852 fix starts
2149 --Obtain the meaning for given lookup type and code and pass that as
2150 --token to display error message.
2151 OPEN l_lookup_meaning_csr ('OKL_ASSET_RETURN_TYPE', lp_artv_rec.art1_code);
2152 FETCH l_lookup_meaning_csr INTO l_asset_return_type;
2153 CLOSE l_lookup_meaning_csr;
2154
2155 OPEN l_lookup_meaning_csr ('OKL_ASSET_RETURN_STATUS', lp_artv_rec.ARS_CODE);
2156 FETCH l_lookup_meaning_csr INTO l_asset_return_status;
2157 CLOSE l_lookup_meaning_csr;
2158
2159 OKL_API.set_message( p_app_name => 'OKL',
2160 p_msg_name => 'OKL_AM_INVALID_STATUS_TYPE',
2161 p_token1 => 'ARS_CODE',
2162 p_token1_value => l_asset_return_status,
2163 p_token2 => 'ART1_CODE',
2164 p_token2_value => l_asset_return_type);
2165 --Bug 3918852 fix ends
2166
2167 RAISE OKL_API.G_EXCEPTION_ERROR;
2168 END IF;
2169 END IF;
2170 END IF;
2171 ----------------------------------------------------
2172
2173 IF lp_artv_rec.ARS_CODE = 'AVAILABLE_FOR_REPAIR' AND l_current_db_status <> 'AVAILABLE_FOR_REPAIR' THEN
2174 -- permits the creation of asset condition lines
2175 NULL;
2176 END IF;
2177
2178 ---------------Re-Lease-----------------------------
2179 IF lp_artv_rec.ARS_CODE = 'RE_LEASE' AND l_current_db_status <> 'RE_LEASE' THEN
2180
2181
2182
2183 -- make sure that the asset is terminated or expired
2184
2185 -- SECHAWLA 22-JAN-03 Bug # 2762419 : call the new procedure to check that the asset line is terminated/expired
2186 check_asset_status(p_kle_id => l_kle_id,
2187 p_asset_number => l_asset_number,
2188 x_return_status => x_return_status);
2189 IF (is_debug_statement_on) THEN
2190 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called check_asset_status, x_return_status: ' || x_return_status);
2191 END IF;
2192
2193 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2194 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2195 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2196 RAISE OKL_API.G_EXCEPTION_ERROR;
2197 END IF;
2198
2199 -- SECHAWLA 22-JAN-03 Bug # 2762419 : end modification
2200
2201
2202 END IF;
2203 ------------------------------------------------------
2204
2205 IF lp_artv_rec.ARS_CODE = 'SCRAPPED' AND l_current_db_status <> 'SCRAPPED' THEN
2206 -- make sure that the asset is terminated or expired
2207
2208 -- SECHAWLA 22-JAN-03 Bug # 2762419 : call the new procedure to check that the asset line is terminated/expired
2209 check_asset_status(p_kle_id => l_kle_id,
2210 p_asset_number => l_asset_number,
2211 x_return_status => x_return_status);
2212 IF (is_debug_statement_on) THEN
2213 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called check_asset_status, x_return_status: ' || x_return_status);
2214 END IF;
2215
2216 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2217 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2218 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2219 RAISE OKL_API.G_EXCEPTION_ERROR;
2220 END IF;
2221
2222 -- SECHAWLA 22-JAN-03 Bug # 2762419 : end modification
2223
2224 IF (is_debug_statement_on) THEN
2225 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_AM_ASSET_DISPOSE_PVT.dispose_asset');
2226 END IF;
2227
2228 -- call asset disposition for full retirement of the asset
2229 OKL_AM_ASSET_DISPOSE_PVT.dispose_asset (
2230 p_api_version => p_api_version,
2231 p_init_msg_list => OKC_API.G_FALSE,
2232 x_return_status => x_return_status,
2233 x_msg_count => x_msg_count,
2234 x_msg_data => x_msg_data,
2235 p_financial_asset_id => l_kle_id,
2236 p_quantity => l_quantity,
2237 p_proceeds_of_sale => 0,
2238 p_legal_entity_id => lp_artv_rec.legal_entity_id);
2239 IF (is_debug_statement_on) THEN
2240 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_AM_ASSET_DISPOSE_PVT.dispose_asset, x_return_status: ' || x_return_status);
2241 END IF;
2242
2243 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2244 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2245 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2246 RAISE OKL_API.G_EXCEPTION_ERROR;
2247 END IF;
2248
2249 END IF;
2250
2251 IF lp_artv_rec.ARS_CODE = 'CANCELLED' AND l_current_db_status <> 'CANCELLED' THEN
2252 perform_cancellation();
2253 END IF;
2254
2255 -- status can change to "Remarketing" only from "Available for Sale"
2256 IF lp_artv_rec.ARS_CODE = 'REMARKETED' AND l_current_db_status <> 'REMARKETED' THEN
2257 IF l_current_db_status = 'AVAILABLE_FOR_SALE' THEN
2258 i := 0;
2259 l_total_quantity := 0;
2260 -- Loop thru all of the order lines and calculate the sum total of order quantities for all order lines
2261 FOR l_assetsaleuv_rec IN l_assetsaleuv_csr(lp_artv_rec.id) LOOP
2262 l_total_quantity := l_total_quantity + l_assetsaleuv_rec.ordered_quantity;
2263 i := i +1;
2264 END LOOP;
2265
2266 IF i = 0 THEN
2267 x_return_status := OKL_API.G_RET_STS_ERROR;
2268 --Can not change the Asset Return status to REMARKETING , as there is no order booked for theis asset
2269
2270 OKL_API.set_message( p_app_name => 'OKL',
2271 p_msg_name => 'OKL_AM_NO_ORDER_EXISTS');
2272
2273
2274 RAISE OKL_API.G_EXCEPTION_ERROR;
2275 END IF;
2276
2277 IF l_total_quantity <> l_quantity THEN
2278 x_return_status := OKL_API.G_RET_STS_ERROR;
2279 -- Ordered quantity does not match the original Asset Return quantity for this asset.
2280 OKL_API.set_message( p_app_name => 'OKL',
2281 p_msg_name => 'OKL_AM_QUANTITY_MISMATCH');
2282 RAISE OKL_API.G_EXCEPTION_ERROR;
2283 END IF;
2284 ELSE
2285 x_return_status := OKL_API.G_RET_STS_ERROR;
2286 -- Asset can be Remarketed only if it is Available for Sale
2287 OKL_API.set_message( p_app_name => 'OKL',
2288 p_msg_name => 'OKL_AM_NO_REMARKET');
2289 RAISE OKL_API.G_EXCEPTION_ERROR;
2290 END IF;
2291
2292 END IF;
2293
2294 IF lp_artv_rec.ARS_CODE = 'REPURCHASE' AND l_current_db_status <> 'REPURCHASE' THEN
2295 OPEN l_repurchasetasset_csr(lp_artv_rec.id);
2296 FETCH l_repurchasetasset_csr INTO l_quote_id, l_accepted_yn;
2297 IF l_repurchasetasset_csr%NOTFOUND OR l_quote_id IS NULL OR l_quote_id = OKL_API.G_MISS_NUM OR
2298 l_accepted_yn IS NULL OR l_accepted_yn = OKL_API.G_MISS_CHAR OR l_accepted_yn <> 'Y' THEN
2299
2300 x_return_status := OKL_API.G_RET_STS_ERROR;
2301 -- Can not change Asset Return status to REPURCHASE, as there is no accepted repurchase quote existing for this asset.
2302 OKL_API.set_message( p_app_name => 'OKL',
2303 p_msg_name => 'OKL_AM_NO_REPURCHASE_QUOTE');
2304 RAISE OKL_API.G_EXCEPTION_ERROR;
2305 END IF;
2306 CLOSE l_repurchasetasset_csr;
2307 END IF;
2308
2309 -- SECHAWLA 29-OCT-04 3924244 : check if the item has ben created by the WF
2310 -- need to assign the item id and item number in lp_artv_rec before updation
2311 -- If user entered item number on the screen, lp_artv_rec.new_item_number will have
2312 -- the item number at this point, lp_artv_rec.imr_id wil be null. If user did not enter item number,
2313 -- both lp_artv_rec.imr_id and lp_artv_rec.new_item_number would be null at this point
2314 IF l_custom_rmk_wf = 'Y' THEN
2315 OPEN l_assetreturn_csr(lp_artv_rec.id);
2316 FETCH l_assetreturn_csr INTO l_wf_imr_id, l_wf_new_item_number;
2317 CLOSE l_assetreturn_csr;
2318
2319
2320 IF l_wf_imr_id IS NOT NULL THEN -- wf has created the item
2321 lp_artv_rec.imr_id := l_wf_imr_id;
2322 -- SECHAWLA 18-JAN-04 4125635 : added the following IF statement
2323 IF lp_artv_rec.new_item_number IS NULL -- user did not enter item no.
2324 AND l_wf_new_item_number IS NOT NULL THEN -- custom wf API updated item no.
2325 lp_artv_rec.new_item_number := l_wf_new_item_number; -- SECHAWLA 03-DEC-04 4047159 : Moved here
2326 END IF;
2327 END IF;
2328
2329 -- -- SECHAWLA 03-DEC-04 4047159
2330 /*IF l_wf_new_item_number IS NOT NULL THEN -- wf has creaed the item
2331 lp_artv_rec.new_item_number := l_wf_new_item_number;
2332 END IF;
2333 */
2334 END IF;
2335 -- SECHAWLA 29-OCT-04 3924244 : added the following piece of code - END
2336
2337 -- DJANASWA Changes for 'Asset repossession for a loan' project BEGIN
2338 IF (lp_artv_rec.ASSET_FMV_AMOUNT IS NOT NULL
2339 AND lp_artv_rec.ASSET_FMV_AMOUNT <> OKL_API.G_MISS_NUM) THEN
2340 IF lp_artv_rec.ASSET_FMV_AMOUNT < 0 THEN
2341 x_return_status := OKL_API.G_RET_STS_ERROR;
2342 -- Asset FMV Amount cannot be less than zero.
2343
2344 OKL_API.set_message( p_app_name => 'OKL',
2345 p_msg_name => 'OKL_AM_ASSET_FMV_AMT_ERR');
2346
2347 RAISE OKL_API.G_EXCEPTION_ERROR;
2348 END IF;
2349 END IF;
2350 -- DJANASWA Changes for 'Asset repossession for a loan' project END
2351
2352 -- rmunjulu Bug 6674730 start
2353 -- Only for Loans - Do some checks
2354 IF (l_deal_type = 'LOAN') THEN
2355
2356 -- If date returned is stamped as greater thnn sysdate then throw error
2357 IF (p_artv_rec.DATE_RETURNED IS NOT NULL AND
2358 p_artv_rec.DATE_RETURNED <> OKL_API.G_MISS_DATE) THEN
2359 IF (TRUNC(p_artv_rec.DATE_RETURNED) > TRUNC(SYSDATE)) THEN
2360 -- You cannot enter a future date as the Date Returned.
2361 OKL_API.set_message( p_app_name => 'OKL',
2362 p_msg_name => 'OKL_AM_ASSET_DT_RET');
2363 RAISE OKL_API.G_EXCEPTION_ERROR;
2364 END IF;
2365 END IF;
2366
2367 OPEN l_okclines_csr(l_kle_id);
2368 FETCH l_okclines_csr INTO l_sts_code;
2369 CLOSE l_okclines_csr;
2370
2371 -- If the asset return is being set to Repossessed (only for Loans) do some checks
2372 IF p_artv_rec.ars_code IS NOT NULL
2373 AND p_artv_rec.ars_code <> OKL_API.G_MISS_CHAR
2374 AND p_artv_rec.ars_code = 'REPOSSESSED' THEN
2375
2376 -- Asset cannot be in terminated status when changing status to repossessed
2377 IF (l_sts_code = 'TERMINATED' ) THEN
2378
2379 -- The asset has been terminated on the contract. You cannot update the return status to Repossessed for a terminated asset.
2380 OKL_API.set_message( p_app_name => 'OKL',
2381 p_msg_name => 'OKL_AM_ASSET_LN_TERM');
2382 RAISE OKL_API.G_EXCEPTION_ERROR;
2383 END IF;
2384
2385 -- Date returned cannot be null when changing status to repossessed
2386 IF (p_artv_rec.DATE_RETURNED IS NULL OR
2387 p_artv_rec.DATE_RETURNED = OKL_API.G_MISS_DATE) THEN
2388
2389 --You cannot update the return status to Repossessed. Please enter the Date Returned.
2390 OKL_API.set_message( p_app_name => 'OKL',
2391 p_msg_name => 'OKL_AM_ASSET_DATE_RET_REQ');
2392 RAISE OKL_API.G_EXCEPTION_ERROR;
2393 END IF;
2394
2395 -- Asset return value cannot be null when changing status to repossessed
2396 IF (p_artv_rec.ASSET_FMV_AMOUNT IS NULL OR
2397 p_artv_rec.ASSET_FMV_AMOUNT = OKL_API.G_MISS_NUM) THEN
2398
2399 --You cannot update the return status to Repossessed. Please enter the Return Value.
2400 OKL_API.set_message( p_app_name => 'OKL',
2401 p_msg_name => 'OKL_AM_ASSET_RET_VAL_REQ');
2402 RAISE OKL_API.G_EXCEPTION_ERROR;
2403 END IF;
2404
2405 -- Check for Asset category existence in Inventory (MTL_SYSTEM_ITEMS)
2406 OPEN l_mtl_instance_csr(l_kle_id);
2407 FETCH l_mtl_instance_csr INTO l_mtl_instance_rec;
2408 CLOSE l_mtl_instance_csr;
2409
2410 IF l_mtl_instance_rec.asset_category_id IS NULL THEN
2411
2412 OKL_API.set_message( p_app_name => 'OKL',
2413 p_msg_name => 'OKL_AM_ASSET_CAT_DOESNOT_EXIST',
2414 p_token1 => 'ITEM_DESCRIPTION',
2415 p_token1_value => l_mtl_instance_rec.item_description,
2416 p_token2 => 'ORGANIZATION_NAME',
2417 p_token2_value => l_mtl_instance_rec.organization_name);
2418 RAISE OKL_API.G_EXCEPTION_ERROR;
2419 END IF;
2420
2421 -- Check for setup in operational options
2422 OPEN l_operational_csr;
2423 FETCH l_operational_csr INTO l_operational_rec;
2424 IF l_operational_csr%NOTFOUND THEN
2425 l_error := 'Y';
2426 END IF;
2427 CLOSE l_operational_csr;
2428
2429 IF l_error = 'Y'
2430 OR l_operational_rec.corp_book IS NULL
2431 OR l_operational_rec.tax_book_1 IS NULL
2432 OR l_operational_rec.fa_location_id IS NULL THEN
2433
2434 -- You cannot update the return status to Repossessed as the System Options
2435 -- for Asset Return has not been defined for this Operating Unit.
2436 OKL_API.set_message( p_app_name => 'OKL',
2437 p_msg_name => 'OKL_AM_ASSET_REPO_OPTIONS');
2438
2439 RAISE OKL_API.G_EXCEPTION_ERROR;
2440
2441 END IF;
2442
2443 END IF; -- ars_code NOT NULL
2444
2445 -- If current status is Repossessed and cancelling or unsuccessful repo or returned or scheduled then error
2446 IF (l_current_db_status = 'REPOSSESSED') AND
2447 (p_artv_rec.ars_code IN ('CANCELLED', 'UNSUCCESS_REPO', 'RETURNED', 'SCHEDULED')) THEN
2448
2449 -- You have selected an invalid return status.
2450 -- Assets in Repossessed return status cannot be updated to Canceled, Unsuccessful Repossession, Returned or Scheduled for a loan. Please select a valid return status.
2451 OKL_API.set_message( p_app_name => 'OKL',
2452 p_msg_name => 'OKL_AM_ASSET_REPO_STATUS');
2453 RAISE OKL_API.G_EXCEPTION_ERROR;
2454 END IF;
2455 END IF; -- for LOAN
2456 -- rmunjulu Bug 6674730 end
2457
2458 IF (is_debug_statement_on) THEN
2459 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling OKL_ASSET_RETURNS_PUB.update_asset_returns');
2460 END IF;
2461 -- call update of tapi
2462 OKL_ASSET_RETURNS_PUB.update_asset_returns(
2463 p_api_version => p_api_version,
2464 p_init_msg_list => OKL_API.G_FALSE,
2465 x_return_status => x_return_status,
2466 x_msg_count => x_msg_count,
2467 x_msg_data => x_msg_data,
2468 p_artv_rec => lp_artv_rec,
2469 x_artv_rec => lx_artv_rec);
2470 IF (is_debug_statement_on) THEN
2471 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called OKL_ASSET_RETURNS_PUB.update_asset_returns, x_return_status: ' || x_return_status);
2472 END IF;
2473
2474 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2475 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2476 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2477 RAISE OKL_API.G_EXCEPTION_ERROR;
2478 END IF;
2479
2480 -- MDOKAL Bug 2883292, Logic moved to call WF
2481 --------------Collections WF Request------------------------
2482 IF (lp_artv_rec.ARS_CODE = 'REPOSSESSED' AND l_current_db_status <> 'REPOSSESSED') OR
2483 (lp_artv_rec.ARS_CODE = 'UNSUCCESS_REPO' AND l_current_db_status <> 'UNSUCCESS_REPO') THEN
2484
2485 IF lp_artv_rec.art1_code = 'REPOS_REQUEST' THEN
2486
2487 IF (is_debug_statement_on) THEN
2488 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling okl_am_wf.raise_business_event,oracle.apps.okl.am.notifycollections, lp_artv_rec.id: ' || lp_artv_rec.id);
2489 END IF;
2490 -- send notification to the user who created the notification request
2491 okl_am_wf.raise_business_event(lp_artv_rec.id,'oracle.apps.okl.am.notifycollections');
2492 END IF;
2493 END IF;
2494 ----------------------------------------------------
2495
2496
2497 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2498 IF (is_debug_procedure_on) THEN
2499 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
2500 END IF;
2501
2502 EXCEPTION
2503 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2504 IF (is_debug_exception_on) THEN
2505 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
2506 END IF;
2507
2508 IF l_assetreturnuv_csr%ISOPEN THEN
2509 CLOSE l_assetreturnuv_csr;
2510 END IF;
2511
2512 IF l_assetsaleuv_csr%ISOPEN THEN
2513 CLOSE l_assetsaleuv_csr;
2514 END IF;
2515
2516 IF l_repurchasetasset_csr%ISOPEN THEN
2517 CLOSE l_repurchasetasset_csr;
2518 END IF;
2519
2520 -- SECHAWLA 04-OCT-04 3924244 : close new cursors
2521 IF l_get_wf_details_csr%ISOPEN THEN
2522 CLOSE l_get_wf_details_csr;
2523 END IF;
2524 --IF l_mtlsystemitems_csr%ISOPEN THEN
2525 -- CLOSE l_mtlsystemitems_csr;
2526 -- END IF;
2527
2528 IF l_systemparamsall_csr%ISOPEN THEN
2529 CLOSE l_systemparamsall_csr;
2530 END IF;
2531
2532 -- SECHAWLA 29-OCT-04 3924244
2533 IF l_assetreturn_csr%ISOPEN THEN
2534 CLOSE l_assetreturn_csr;
2535 END IF;
2536
2537 -- RRAVIKIR Legal Entity Changes
2538 IF fetch_legal_entity%ISOPEN THEN
2539 CLOSE fetch_legal_entity;
2540 END IF;
2541 -- Legal Entity Changes End
2542
2543 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2544 (
2545 l_api_name,
2546 G_PKG_NAME,
2547 'OKL_API.G_RET_STS_ERROR',
2548 x_msg_count,
2549 x_msg_data,
2550 '_PVT'
2551 );
2552
2553 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2554 IF (is_debug_exception_on) THEN
2555 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
2556 END IF;
2557
2558 IF l_assetreturnuv_csr%ISOPEN THEN
2559 CLOSE l_assetreturnuv_csr;
2560 END IF;
2561
2562 IF l_assetsaleuv_csr%ISOPEN THEN
2563 CLOSE l_assetsaleuv_csr;
2564 END IF;
2565
2566 IF l_repurchasetasset_csr%ISOPEN THEN
2567 CLOSE l_repurchasetasset_csr;
2568 END IF;
2569
2570 -- SECHAWLA 04-OCT-04 3924244 : close new cursors
2571
2572 IF l_get_wf_details_csr%ISOPEN THEN
2573 CLOSE l_get_wf_details_csr;
2574 END IF;
2575 -- IF l_mtlsystemitems_csr%ISOPEN THEN
2576 -- CLOSE l_mtlsystemitems_csr;
2577 -- END IF;
2578
2579 IF l_systemparamsall_csr%ISOPEN THEN
2580 CLOSE l_systemparamsall_csr;
2581 END IF;
2582
2583 -- SECHAWLA 29-OCT-04 3924244
2584 IF l_assetreturn_csr%ISOPEN THEN
2585 CLOSE l_assetreturn_csr;
2586 END IF;
2587
2588 -- RRAVIKIR Legal Entity Changes
2589 IF fetch_legal_entity%ISOPEN THEN
2590 CLOSE fetch_legal_entity;
2591 END IF;
2592 -- Legal Entity Changes End
2593
2594 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2595 (
2596 l_api_name,
2597 G_PKG_NAME,
2598 'OKL_API.G_RET_STS_UNEXP_ERROR',
2599 x_msg_count,
2600 x_msg_data,
2601 '_PVT'
2602 );
2603
2604 WHEN OTHERS THEN
2605 IF (is_debug_exception_on) THEN
2606 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2607 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2608 END IF;
2609
2610 IF l_assetreturnuv_csr%ISOPEN THEN
2611 CLOSE l_assetreturnuv_csr;
2612 END IF;
2613
2614 IF l_assetsaleuv_csr%ISOPEN THEN
2615 CLOSE l_assetsaleuv_csr;
2616 END IF;
2617
2618 IF l_repurchasetasset_csr%ISOPEN THEN
2619 CLOSE l_repurchasetasset_csr;
2620 END IF;
2621
2622 -- SECHAWLA 04-OCT-04 3924244 : close new cursors
2623 IF l_get_wf_details_csr%ISOPEN THEN
2624 CLOSE l_get_wf_details_csr;
2625 END IF;
2626
2627 -- IF l_mtlsystemitems_csr%ISOPEN THEN
2628 -- CLOSE l_mtlsystemitems_csr;
2629 -- END IF;
2630
2631 IF l_systemparamsall_csr%ISOPEN THEN
2632 CLOSE l_systemparamsall_csr;
2633 END IF;
2634
2635 -- SECHAWLA 29-OCT-04 3924244
2636 IF l_assetreturn_csr%ISOPEN THEN
2637 CLOSE l_assetreturn_csr;
2638 END IF;
2639
2640 -- RRAVIKIR Legal Entity Changes
2641 IF fetch_legal_entity%ISOPEN THEN
2642 CLOSE fetch_legal_entity;
2643 END IF;
2644 -- Legal Entity Changes End
2645
2646 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2647 (
2648 l_api_name,
2649 G_PKG_NAME,
2650 'OTHERS',
2651 x_msg_count,
2652 x_msg_data,
2653 '_PVT'
2654 );
2655 END update_asset_return;
2656
2657
2658 -- Start of comments
2659 --
2660 -- Procedure Name : create_asset_return
2661 -- Description : Create multiple asset returns
2662 -- Business Rules :
2663 -- Parameters :
2664 -- Version : 1.0
2665 -- History : SECHAWLA 16-JAN-03 Bug # 2754280 : Removed DEFAULT hint from procedure parameters
2666 -- : 29 Oct 2004 PAGARG Bug# 3925453
2667 -- : Additional Input parameter quote id
2668 -- End of comments
2669 PROCEDURE create_asset_return(
2670 p_api_version IN NUMBER,
2671 p_init_msg_list IN VARCHAR2,
2672 x_return_status OUT NOCOPY VARCHAR2,
2673 x_msg_count OUT NOCOPY NUMBER,
2674 x_msg_data OUT NOCOPY VARCHAR2,
2675 p_artv_tbl IN artv_tbl_type,
2676 x_artv_tbl OUT NOCOPY artv_tbl_type,
2677 p_quote_id IN NUMBER DEFAULT NULL) AS
2678
2679 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2680 i NUMBER := 0;
2681 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2682 l_api_name CONSTANT VARCHAR2(30) := 'update_asset_return';
2683 l_api_version CONSTANT NUMBER := 1;
2684 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'create_asset_return';
2685 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2686 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2687 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2688 BEGIN
2689 IF (is_debug_procedure_on) THEN
2690 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
2691 END IF;
2692 IF (is_debug_statement_on) THEN
2693 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'p_artv_tbl.COUNT:'||p_artv_tbl.COUNT);
2694 END IF;
2695
2696 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
2697 G_PKG_NAME,
2698 p_init_msg_list,
2699 l_api_version,
2700 p_api_version,
2701 '_PVT',
2702 x_return_status);
2703
2704
2705 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2706 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2707 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2708 RAISE OKL_API.G_EXCEPTION_ERROR;
2709 END IF;
2710
2711 IF (p_artv_tbl.COUNT > 0) THEN
2712 i := p_artv_tbl.FIRST;
2713 -- loop thru the table of records and create asset return for each record
2714 LOOP
2715 IF (is_debug_statement_on) THEN
2716 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling create_asset_return');
2717 END IF;
2718 create_asset_return (
2719 p_api_version => p_api_version,
2720 p_init_msg_list => OKL_API.G_FALSE,
2721 x_return_status => l_return_status,
2722 x_msg_count => x_msg_count,
2723 x_msg_data => x_msg_data,
2724 p_artv_rec => p_artv_tbl(i),
2725 x_artv_rec => x_artv_tbl(i),
2726 p_quote_id => p_quote_id); -- 29 Oct 2004 PAGARG Bug# 3925453
2727 IF (is_debug_statement_on) THEN
2728 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called create_asset_return, l_return_status: ' || l_return_status);
2729 END IF;
2730
2731 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2732 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
2733 l_overall_status := l_return_status;
2734 END IF;
2735 END IF;
2736 EXIT WHEN (i = p_artv_tbl.LAST);
2737 i := p_artv_tbl.NEXT(i);
2738 END LOOP;
2739 END IF;
2740
2741 x_return_status := l_overall_status;
2742
2743 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2744 IF (is_debug_procedure_on) THEN
2745 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
2746 END IF;
2747
2748 EXCEPTION
2749 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2750 IF (is_debug_exception_on) THEN
2751 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
2752 END IF;
2753
2754 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2755 (
2756 l_api_name,
2757 G_PKG_NAME,
2758 'OKL_API.G_RET_STS_ERROR',
2759 x_msg_count,
2760 x_msg_data,
2761 '_PVT'
2762 );
2763
2764 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2765 IF (is_debug_exception_on) THEN
2766 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
2767 END IF;
2768
2769 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2770 (
2771 l_api_name,
2772 G_PKG_NAME,
2773 'OKL_API.G_RET_STS_UNEXP_ERROR',
2774 x_msg_count,
2775 x_msg_data,
2776 '_PVT'
2777 );
2778
2779 WHEN OTHERS THEN
2780 IF (is_debug_exception_on) THEN
2781 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2782 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2783 END IF;
2784
2785
2786 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2787 (
2788 l_api_name,
2789 G_PKG_NAME,
2790 'OTHERS',
2791 x_msg_count,
2792 x_msg_data,
2793 '_PVT'
2794 );
2795
2796 END create_asset_return;
2797
2798
2799 -- Start of comments
2800 --
2801 -- Procedure Name : update_asset_return
2802 -- Description : Update multiple asset returns
2803 -- Business Rules :
2804 -- Parameters :
2805 -- Version : 1.0
2806 -- History : SECHAWLA 16-JAN-03 Bug # 2754280 : Removed DEFAULT hint from procedure parameters
2807 -- End of comments
2808 PROCEDURE update_asset_return(
2809 p_api_version IN NUMBER,
2810 p_init_msg_list IN VARCHAR2,
2811 x_return_status OUT NOCOPY VARCHAR2,
2812 x_msg_count OUT NOCOPY NUMBER,
2813 x_msg_data OUT NOCOPY VARCHAR2,
2814 p_artv_tbl IN artv_tbl_type,
2815 x_artv_tbl OUT NOCOPY artv_tbl_type) AS
2816
2817 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2818 i NUMBER := 0;
2819 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2820 l_api_name CONSTANT VARCHAR2(30) := 'update_asset_return';
2821 l_api_version CONSTANT NUMBER := 1;
2822 l_module_name VARCHAR2(500) := G_MODULE_NAME || 'update_asset_return';
2823 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2824 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2825 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2826 BEGIN
2827 IF (is_debug_procedure_on) THEN
2828 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'Begin(+)');
2829 END IF;
2830 IF (is_debug_statement_on) THEN
2831 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'p_artv_tbl.COUNT:'||p_artv_tbl.COUNT);
2832 END IF;
2833
2834 l_return_status := OKL_API.START_ACTIVITY( l_api_name,
2835 G_PKG_NAME,
2836 p_init_msg_list,
2837 l_api_version,
2838 p_api_version,
2839 '_PVT',
2840 x_return_status);
2841
2842
2843 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2844 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2845 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2846 RAISE OKL_API.G_EXCEPTION_ERROR;
2847 END IF;
2848
2849 IF (p_artv_tbl.COUNT > 0) THEN
2850 i := p_artv_tbl.FIRST;
2851 -- update asset return for each table record
2852 LOOP
2853 IF (is_debug_statement_on) THEN
2854 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'calling update_asset_return');
2855 END IF;
2856
2857 update_asset_return (
2858 p_api_version => p_api_version,
2859 p_init_msg_list => OKL_API.G_FALSE,
2860 x_return_status => l_return_status,
2861 x_msg_count => x_msg_count,
2862 x_msg_data => x_msg_data,
2863 p_artv_rec => p_artv_tbl(i),
2864 x_artv_rec => x_artv_tbl(i));
2865 IF (is_debug_statement_on) THEN
2866 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name, 'called update_asset_return, l_return_status: ' || l_return_status);
2867 END IF;
2868
2869 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2870 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
2871 l_overall_status := l_return_status;
2872 END IF;
2873 END IF;
2874
2875 EXIT WHEN (i = p_artv_tbl.LAST);
2876 i := p_artv_tbl.NEXT(i);
2877 END LOOP;
2878 END IF;
2879
2880 x_return_status := l_overall_status;
2881
2882 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2883 IF (is_debug_procedure_on) THEN
2884 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,l_module_name,'End(-)');
2885 END IF;
2886
2887 EXCEPTION
2888 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2889 IF (is_debug_exception_on) THEN
2890 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_ERROR');
2891 END IF;
2892
2893 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2894 (
2895 l_api_name,
2896 G_PKG_NAME,
2897 'OKL_API.G_RET_STS_ERROR',
2898 x_msg_count,
2899 x_msg_data,
2900 '_PVT'
2901 );
2902
2903 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2904 IF (is_debug_exception_on) THEN
2905 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'G_EXCEPTION_UNEXPECTED_ERROR');
2906 END IF;
2907
2908 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2909 (
2910 l_api_name,
2911 G_PKG_NAME,
2912 'OKL_API.G_RET_STS_UNEXP_ERROR',
2913 x_msg_count,
2914 x_msg_data,
2915 '_PVT'
2916 );
2917
2918 WHEN OTHERS THEN
2919 IF (is_debug_exception_on) THEN
2920 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,l_module_name, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2921 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2922 END IF;
2923 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2924 (
2925 l_api_name,
2926 G_PKG_NAME,
2927 'OTHERS',
2928 x_msg_count,
2929 x_msg_data,
2930 '_PVT'
2931 );
2932
2933 END update_asset_return;
2934
2935 END OKL_AM_ASSET_RETURN_PVT;