[Home] [Help]
PACKAGE BODY: APPS.OKL_ART_PVT
Source
1 PACKAGE BODY OKL_ART_PVT AS
2 /* $Header: OKLSARTB.pls 120.9 2007/11/09 22:10:42 djanaswa noship $ */
3
4 ----------------------------------------
5 -- Developer Generated Code here --
6 -- Reason : Added code so that the validation functionality is accomplished --
7 ----------------------------------------
8
9 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) :='OKC_NO_PARENT_RECORD';
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) :='OKC_CONTRACTS_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 G_EXCEPTION_HALT_VALIDATION exception;
14
15 -- Start of comments
16 --
17 -- Procedure Name : validate_currency_record
18 -- Description : Used for validation of Currency Code Conversion Coulms
19 -- Business Rules : If transaction currency <> functional currency, then conversion columns
20 -- are mandatory
21 -- Else If transaction currency = functional currency, then conversion columns
22 -- should all be NULL
23 -- Parameters :
24 -- Version : 1.0
25 -- History : RABHUPAT 16-DEC-2002 2667636:Added new procedure
26 -- End of comments
27
28 PROCEDURE validate_currency_record(p_artv_rec IN artv_rec_type,
29 x_return_status OUT NOCOPY VARCHAR2) IS
30 BEGIN
31 x_return_status := OKC_API.G_RET_STS_SUCCESS;
32 -- If transaction currency <> functional currency, then conversion columns
33 -- are mandatory
34 IF (p_artv_rec.currency_code <> p_artv_rec.currency_conversion_code) THEN
35 IF (p_artv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR OR
36 p_artv_rec.currency_conversion_type IS NULL) THEN
37 --SET MESSAGE
38 OKC_API.set_message(p_app_name => G_APP_NAME,
39 p_msg_name => G_REQUIRED_VALUE,
40 p_token1 => G_COL_NAME_TOKEN,
41 p_token1_value => 'currency_conversion_type');
42 x_return_status := OKC_API.G_RET_STS_ERROR;
43 END IF;
44 IF (p_artv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM OR
45 p_artv_rec.currency_conversion_rate IS NULL) THEN
46 --SET MESSAGE
47 OKC_API.set_message(p_app_name => G_APP_NAME,
48 p_msg_name => G_REQUIRED_VALUE,
49 p_token1 => G_COL_NAME_TOKEN,
50 p_token1_value => 'currency_conversion_rate');
51 x_return_status := OKC_API.G_RET_STS_ERROR;
52 END IF;
53 IF (p_artv_rec.currency_conversion_date = OKC_API.G_MISS_DATE OR
54 p_artv_rec.currency_conversion_date IS NULL) THEN
55 --SET MESSAGE
56 OKC_API.set_message(p_app_name => G_APP_NAME,
57 p_msg_name => G_REQUIRED_VALUE,
58 p_token1 => G_COL_NAME_TOKEN,
59 p_token1_value => 'currency_conversion_date');
60 x_return_status := OKC_API.G_RET_STS_ERROR;
61 END IF;
62 -- Else If transaction currency = functional currency, then conversion columns
63 -- should all be NULL
64 ELSIF (p_artv_rec.currency_code = p_artv_rec.currency_conversion_code) THEN
65 IF (p_artv_rec.currency_conversion_type IS NOT NULL) OR
66 (p_artv_rec.currency_conversion_rate IS NOT NULL) OR
67 (p_artv_rec.currency_conversion_date IS NOT NULL) THEN
68 --SET MESSAGE
69 -- Currency conversion columns should be all null
70 IF p_artv_rec.currency_conversion_rate IS NOT NULL THEN
71 OKC_API.set_message(p_app_name => G_APP_NAME,
72 p_msg_name => G_INVALID_VALUE,
73 p_token1 => G_COL_NAME_TOKEN,
74 p_token1_value => 'currency_conversion_rate');
75 END IF;
76 IF p_artv_rec.currency_conversion_date IS NOT NULL THEN
77 OKC_API.set_message(p_app_name => G_APP_NAME,
78 p_msg_name => G_INVALID_VALUE,
79 p_token1 => G_COL_NAME_TOKEN,
80 p_token1_value => 'currency_conversion_date');
81 END IF;
82 IF p_artv_rec.currency_conversion_type IS NOT NULL THEN
83 OKC_API.set_message(p_app_name => G_APP_NAME,
84 p_msg_name => G_INVALID_VALUE,
85 p_token1 => G_COL_NAME_TOKEN,
86 p_token1_value => 'currency_conversion_type');
87 END IF;
88 x_return_status := OKC_API.G_RET_STS_ERROR;
89 END IF;
90 ELSE
91 x_return_status := OKC_API.G_RET_STS_ERROR;
92 END IF;
93 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
94 RAISE G_EXCEPTION_HALT_VALIDATION;
95 END IF;
96 EXCEPTION
97 WHEN G_EXCEPTION_HALT_VALIDATION THEN
98 x_return_status := OKC_API.G_RET_STS_ERROR;
99 WHEN OTHERS THEN
100 -- store SQL error message on message stack for caller
101 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
102 p_msg_name => g_unexpected_error,
103 p_token1 => g_sqlcode_token,
104 p_token1_value => sqlcode,
105 p_token2 => g_sqlerrm_token,
106 p_token2_value => sqlerrm);
107 -- notify caller of an UNEXPECTED error
108 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
109 END validate_currency_record;
110 ---------------------------------------------------------------------------------
111 -- Start of comments
112 --
113 -- Procedure Name : validate_currency_code
114 -- Description : Validation of Currency Code
115 -- Business Rules :
116 -- Parameters :
117 -- Version : 1.0
118 -- History : RABHUPAT 16-DEC-2002 2667636:Added new procedure
119 -- End of comments
120
121 PROCEDURE validate_currency_code(p_artv_rec IN artv_rec_type,
122 x_return_status OUT NOCOPY VARCHAR2) IS
123 l_return_status VARCHAR2(3) := OKC_API.G_TRUE;
124 BEGIN
125 -- initialize return status
126 x_return_status := OKC_API.G_RET_STS_SUCCESS;
127 -- data is required
128 IF (p_artv_rec.currency_code IS NULL) OR
129 (p_artv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN
130 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
131 ,p_msg_name => G_REQUIRED_VALUE
132 ,p_token1 => G_COL_NAME_TOKEN
133 ,p_token1_value => 'currency_code');
134
135 -- halt further validation of this column
136 RAISE G_EXCEPTION_HALT_VALIDATION;
137 END IF;
138 -- check from currency values using the generic okl_util.validate_currency_code
139 l_return_status := OKL_ACCOUNTING_UTIL.validate_currency_code(p_artv_rec.currency_code);
140 IF (l_return_status <> OKC_API.G_TRUE) THEN
141 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
142 p_msg_name => g_invalid_value,
143 p_token1 => g_col_name_token,
144 p_token1_value => 'currency_code');
145 -- halt further validation of this column
146 RAISE G_EXCEPTION_HALT_VALIDATION;
147 END IF;
148 EXCEPTION
149 WHEN G_EXCEPTION_HALT_VALIDATION THEN
150 x_return_status := OKC_API.G_RET_STS_ERROR;
151 WHEN OTHERS THEN
152 -- store SQL error message on message stack for caller
153 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
154 p_msg_name => g_unexpected_error,
155 p_token1 => g_sqlcode_token,
156 p_token1_value => sqlcode,
157 p_token2 => g_sqlerrm_token,
158 p_token2_value => sqlerrm);
159 -- notify caller of an UNEXPECTED error
160 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
161 END validate_currency_code;
162 ---------------------------------------------------------------------------------
163 -- Start of comments
164 --
165 -- Procedure Name : validate_currency_con_code
166 -- Description : Validation of Currency Conversion Code
167 -- Business Rules :
168 -- Parameters :
169 -- Version : 1.0
170 -- History : RABHUPAT 16-DEC-2002 2667636:Added new procedure
171 -- End of comments
172
173 PROCEDURE validate_currency_con_code(p_artv_rec IN artv_rec_type,
174 x_return_status OUT NOCOPY VARCHAR2) IS
175 l_return_status VARCHAR2(3) := OKC_API.G_TRUE;
176 BEGIN
177 -- initialize return status
178 x_return_status := OKC_API.G_RET_STS_SUCCESS;
179 -- data is required
180 IF (p_artv_rec.currency_conversion_code IS NULL) OR
181 (p_artv_rec.currency_conversion_code = OKC_API.G_MISS_CHAR) THEN
182 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
183 ,p_msg_name => G_REQUIRED_VALUE
184 ,p_token1 => G_COL_NAME_TOKEN
185 ,p_token1_value => 'currency_conversion_code');
186 -- halt further validation of this column
187 RAISE G_EXCEPTION_HALT_VALIDATION;
188 END IF;
189 -- check from currency values using the generic okl_util.validate_currency_code
190 l_return_status := OKL_ACCOUNTING_UTIL.validate_currency_code(p_artv_rec.currency_conversion_code);
191 IF (l_return_status <> OKC_API.G_TRUE) THEN
192 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
193 p_msg_name => g_invalid_value,
194 p_token1 => g_col_name_token,
195 p_token1_value => 'currency_conversion_code');
196 -- halt further validation of this column
197 RAISE G_EXCEPTION_HALT_VALIDATION;
198 END IF;
199 EXCEPTION
200 WHEN G_EXCEPTION_HALT_VALIDATION THEN
201 x_return_status := OKC_API.G_RET_STS_ERROR;
202 WHEN OTHERS THEN
203 -- store SQL error message on message stack for caller
204 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
205 p_msg_name => g_unexpected_error,
206 p_token1 => g_sqlcode_token,
207 p_token1_value => sqlcode,
208 p_token2 => g_sqlerrm_token,
209 p_token2_value => sqlerrm);
210
211 -- notify caller of an UNEXPECTED error
212 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
213 END validate_currency_con_code;
214 ---------------------------------------------------------------------------------
215 -- Start of comments
216 --
217 -- Procedure Name : validate_currency_con_type
218 -- Description : Validation of Currency Conversion type
219 -- Business Rules :
220 -- Parameters :
221 -- Version : 1.0
222 -- History : RABHUPAT 16-DEC-2002 2667636:Added new procedure
223 -- End of comments
224
225 PROCEDURE validate_currency_con_type(p_artv_rec IN artv_rec_type,
226 x_return_status OUT NOCOPY VARCHAR2) IS
227 l_return_status VARCHAR2(3) := OKC_API.G_TRUE;
228 BEGIN
229 -- initialize return status
230 x_return_status := OKC_API.G_RET_STS_SUCCESS;
231 IF (p_artv_rec.currency_conversion_type <> OKL_API.G_MISS_CHAR AND
232 p_artv_rec.currency_conversion_type IS NOT NULL) THEN
233 -- check from currency values using the generic okl_util.validate_currency_code
234 l_return_status := OKL_ACCOUNTING_UTIL.validate_currency_con_type(p_artv_rec.currency_conversion_type);
235 IF (l_return_status <> OKC_API.G_TRUE) THEN
236 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
237 p_msg_name => g_invalid_value,
238 p_token1 => g_col_name_token,
239 p_token1_value => 'currency_conversion_type');
240 -- halt further validation of this column
241 RAISE G_EXCEPTION_HALT_VALIDATION;
242 END IF;
243 END IF;
244 EXCEPTION
245 WHEN G_EXCEPTION_HALT_VALIDATION THEN
246 x_return_status := OKC_API.G_RET_STS_ERROR;
247 WHEN OTHERS THEN
248 -- store SQL error message on message stack for caller
249 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
250 p_msg_name => g_unexpected_error,
251 p_token1 => g_sqlcode_token,
252 p_token1_value => sqlcode,
253 p_token2 => g_sqlerrm_token,
254 p_token2_value => sqlerrm);
255 -- notify caller of an UNEXPECTED error
256 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
257 END validate_currency_con_type;
258
259
260
261 -- Start of comments
262 --
263 -- Procedure Name : validate_id
264 -- Description :
265 -- Business Rules :
266 -- Parameters :
267 -- Version : 1.0
268 -- End of comments
269 procedure validate_id(
270 x_return_status out nocopy VARCHAR2,
271 p_artv_rec in artv_rec_type) is
272
273 begin
274 -- initialize return status
275 x_return_status := OKC_API.G_RET_STS_SUCCESS;
276
277 -- data is required
278 if (p_artv_rec.id is null) or (p_artv_rec.id = OKC_API.G_MISS_NUM) then
279 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
280 p_msg_name => g_required_value,
281 p_token1 => g_col_name_token,
282 p_token1_value => 'id');
283
284 -- notify caller of an error
285 x_return_status := OKC_API.G_RET_STS_ERROR;
286
287 -- halt further validation of this column
288 raise G_EXCEPTION_HALT_VALIDATION;
289 end if;
290
291 exception
292 when G_EXCEPTION_HALT_VALIDATION then
293 -- no processing necessary; validation can continue
294 -- with the next column
295 null;
296
297 when OTHERS then
298 -- store SQL error message on message stack for caller
299 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
300 p_msg_name => g_unexpected_error,
301 p_token1 => g_sqlcode_token,
302 p_token1_value => sqlcode,
303 p_token2 => g_sqlerrm_token,
304 p_token2_value => sqlerrm);
305
306 -- notify caller of an UNEXPECTED error
307 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
308
309 end validate_id;
310
311
312 -- Start of comments
313 --
314 -- Procedure Name : validate_object_version_number
315 -- Description :
316 -- Business Rules :
317 -- Parameters :
318 -- Version : 1.0
319 -- End of comments
320 procedure validate_object_version_number(
321 x_return_status out nocopy VARCHAR2,
322 p_artv_rec in artv_rec_type) is
323
324 begin
325 -- initialize return status
326 x_return_status := OKC_API.G_RET_STS_SUCCESS;
327
328 -- data is required
329 if (p_artv_rec.object_version_number is null) or (p_artv_rec.object_version_number = OKC_API.G_MISS_NUM) then
330 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
331 p_msg_name => g_required_value,
332 p_token1 => g_col_name_token,
333 p_token1_value => 'object_version_number');
334
335 -- notify caller of an error
336 x_return_status := OKC_API.G_RET_STS_ERROR;
337
338 -- halt further validation of this column
339 raise G_EXCEPTION_HALT_VALIDATION;
340 end if;
341
342 exception
343 when G_EXCEPTION_HALT_VALIDATION then
344 -- no processing necessary; validation can continue
345 -- with the next column
346 null;
347
348 when OTHERS then
349 -- store SQL error message on message stack for caller
350 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
351 p_msg_name => g_unexpected_error,
352 p_token1 => g_sqlcode_token,
353 p_token1_value => sqlcode,
354 p_token2 => g_sqlerrm_token,
355 p_token2_value => sqlerrm);
356
357 -- notify caller of an UNEXPECTED error
358 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
359
360 end validate_object_version_number;
361
362 ------------------------------------------------------------------------
363 -- PROCEDURE validate_sfwt_flag
364 -- Post-Generation Change
365 ------------------------------------------------------------------------
366 PROCEDURE validate_sfwt_flag(
367 x_return_status OUT NOCOPY VARCHAR2,
368 p_artv_rec IN artv_rec_type) IS
369
370 BEGIN
371 -- initialize return status
372 x_return_status := OKC_API.G_RET_STS_SUCCESS;
373
374 -- data is required
375 IF (p_artv_rec.sfwt_flag IS NULL)
376 OR (p_artv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
377 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
378 p_msg_name => g_required_value,
379 p_token1 => g_col_name_token,
380 p_token1_value => 'sfwt_flag');
381
382 -- notify caller of an error
383 x_return_status := OKC_API.G_RET_STS_ERROR;
384
385 -- halt further validation of this column
386 RAISE G_EXCEPTION_HALT_VALIDATION;
387
388 END IF;
389
390 EXCEPTION
391 WHEN G_EXCEPTION_HALT_VALIDATION THEN
392 -- no processing necessary; validation can continue
393 -- with the next column
394 NULL;
395 WHEN OTHERS THEN
396 -- store SQL error message on message stack for caller
397 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
398 p_msg_name => g_unexpected_error,
399 p_token1 => g_sqlcode_token,
400 p_token1_value => sqlcode,
401 p_token2 => g_sqlerrm_token,
402 p_token2_value => sqlerrm);
403
404 -- notify caller of an UNEXPECTED error
405 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
406
407 END validate_sfwt_flag;
408
409
410 -- Start of comments
411 --
412 -- Procedure Name : validate_kle_id
413 -- Description :
414 -- Business Rules :
415 -- Parameters :
416 -- Version : 1.0
417 -- End of comments
418 procedure validate_kle_id(
419 x_return_status out nocopy VARCHAR2,
420 p_artv_rec in artv_rec_type) is
421 l_dummy_var VARCHAR2(1) := '?';
422 --- Check here ---
423 CURSOR l_kle_csr IS
424 select 'x' from OKL_K_LINES_V
425 where ID = p_artv_rec.kle_id;
426
427 begin
428 -- initialize return status
429 x_return_status := OKC_API.G_RET_STS_SUCCESS;
430
431 -- data is required
432 if (p_artv_rec.kle_id is null) or (p_artv_rec.kle_id = OKC_API.G_MISS_NUM) then
433 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
434 p_msg_name => g_required_value,
435 p_token1 => g_col_name_token,
436 p_token1_value => 'kle_id');
437
438 -- notify caller of an error
439 x_return_status := OKC_API.G_RET_STS_ERROR;
440
441 -- halt further validation of this column
442 raise G_EXCEPTION_HALT_VALIDATION;
443 end if;
444 -- enforce foreign key
445 OPEN l_kle_csr;
446 FETCH l_kle_csr INTO l_dummy_var;
447 CLOSE l_kle_csr;
448
449 -- if l_dummy_var is still set to default, data was not found
450 IF (l_dummy_var = '?') THEN
451 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
452 p_msg_name => G_NO_PARENT_RECORD);
453
454
455 -- notify caller of an error
456 x_return_status := OKC_API.G_RET_STS_ERROR;
457 END IF;
458
459 exception
460 when G_EXCEPTION_HALT_VALIDATION then
461 -- no processing necessary; validation can continue
462 -- with the next column
463 null;
464
465 when OTHERS then
466 -- store SQL error message on message stack for caller
467 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
468 p_msg_name => g_unexpected_error,
469 p_token1 => g_sqlcode_token,
470 p_token1_value => sqlcode,
471 p_token2 => g_sqlerrm_token,
472 p_token2_value => sqlerrm);
473
474 -- notify caller of an UNEXPECTED error
475 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
476 -- verify that cursor was closed
477 IF l_kle_csr%ISOPEN THEN
478 CLOSE l_kle_csr;
479 END IF;
480
481 end validate_kle_id;
482
483 -- Start of comments
484 --
485 -- Procedure Name : validate_legal_entity_id
486 -- Description :
487 -- Business Rules :
488 -- Parameters :
489 -- Version : 1.0
490 -- End of comments
491 procedure validate_legal_entity_id(
492 x_return_status out nocopy VARCHAR2,
493 p_artv_rec in artv_rec_type) is
494 l_dummy_var VARCHAR2(1) := '?';
495
496 begin
497 -- initialize return status
498 x_return_status := OKC_API.G_RET_STS_SUCCESS;
499
500 -- data is required
501 IF (p_artv_rec.legal_entity_id is null) or (p_artv_rec.legal_entity_id = OKC_API.G_MISS_NUM) then
502 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
503 p_msg_name => g_required_value,
504 p_token1 => g_col_name_token,
505 p_token1_value => 'legal_entity_id');
506
507 -- notify caller of an error
508 x_return_status := OKC_API.G_RET_STS_ERROR;
509
510 -- halt further validation of this column
511 RAISE G_EXCEPTION_HALT_VALIDATION;
512 ELSE
513 l_dummy_var := OKL_LEGAL_ENTITY_UTIL.check_le_id_exists(p_artv_rec.legal_entity_id);
514 IF (l_dummy_var <> 1) THEN
515 OKL_API.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'LEGAL_ENTITY_ID');
516 RAISE G_EXCEPTION_HALT_VALIDATION;
517 END IF;
518 END IF;
519
520 EXCEPTION
521 WHEN G_EXCEPTION_HALT_VALIDATION then
522 -- no processing necessary; validation can continue
523 -- with the next column
524 x_return_status := OKL_API.G_RET_STS_ERROR;
525
526 WHEN OTHERS then
527 -- store SQL error message on message stack for caller
528 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
529 p_msg_name => g_unexpected_error,
530 p_token1 => g_sqlcode_token,
531 p_token1_value => sqlcode,
532 p_token2 => g_sqlerrm_token,
533 p_token2_value => sqlerrm);
534
535 -- notify caller of an UNEXPECTED error
536 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
537
538 END validate_legal_entity_id;
539
540
541 -- Start of comments
542 --
543 -- Procedure Name : validate_ars_code
544 -- Description :
545 -- Business Rules :
546 -- Parameters :
547 -- Version : 1.0
548 -- End of comments
549 procedure validate_ars_code(
550 x_return_status out nocopy VARCHAR2,
551 p_artv_rec in artv_rec_type) is
552 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
553
554 begin
555 -- initialize return status
556 x_return_status := OKC_API.G_RET_STS_SUCCESS;
557
558 -- data is required
559 if (p_artv_rec.ars_code is null) or (p_artv_rec.ars_code = OKC_API.G_MISS_CHAR) then
560 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
561 p_msg_name => g_required_value,
562 p_token1 => g_col_name_token,
563 p_token1_value => 'ars_code');
564
565 -- notify caller of an error
566 x_return_status := OKC_API.G_RET_STS_ERROR;
567
568 -- halt further validation of this column
569 raise G_EXCEPTION_HALT_VALIDATION;
570 end if;
571 l_return_status := OKL_UTIL.check_lookup_code(
572 p_lookup_type => 'OKL_ASSET_RETURN_STATUS'
573 ,p_lookup_code => p_artv_rec.ars_code);
574
575 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
576 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
577 p_msg_name => g_invalid_value,
578 p_token1 => g_col_name_token,
579 p_token1_value => 'ars_code');
580
581
582 -- notify caller of an error
583 x_return_status := OKC_API.G_RET_STS_ERROR;
584 raise G_EXCEPTION_HALT_VALIDATION;
585
586 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
587
588 -- notify caller of an error
589 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
590 raise G_EXCEPTION_HALT_VALIDATION;
591
592 end if;
593
594 exception
595 when G_EXCEPTION_HALT_VALIDATION then
596 -- no processing necessary; validation can continue
597 -- with the next column
598 null;
599
600 when OTHERS then
601 -- store SQL error message on message stack for caller
602 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
603 p_msg_name => g_unexpected_error,
604 p_token1 => g_sqlcode_token,
605 p_token1_value => sqlcode,
606 p_token2 => g_sqlerrm_token,
607 p_token2_value => sqlerrm);
608
609 -- notify caller of an UNEXPECTED error
610 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
611
612 end validate_ars_code;
613
614 -- Start of comments
615 --
616 -- Procedure Name : validate_art1_code
617 -- Description :
618 -- Business Rules :
619 -- Parameters :
620 -- Version : 1.0
621 -- End of comments
622
623 procedure validate_art1_code(
624 x_return_status out nocopy VARCHAR2,
625 p_artv_rec in artv_rec_type) is
626 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
627
628 begin
629 -- initialize return status
630 x_return_status := OKC_API.G_RET_STS_SUCCESS;
631
632 -- data is required
633 if (p_artv_rec.art1_code is null) or (p_artv_rec.art1_code = OKC_API.G_MISS_CHAR) then
634 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
635 p_msg_name => g_required_value,
636 p_token1 => g_col_name_token,
637 p_token1_value => 'art1_code');
638
639 -- notify caller of an error
640 x_return_status := OKC_API.G_RET_STS_ERROR;
641
642 -- halt further validation of this column
643 raise G_EXCEPTION_HALT_VALIDATION;
644 end if;
645
646 l_return_status := OKL_UTIL.check_lookup_code(
647 p_lookup_type => 'OKL_ASSET_RETURN_TYPE'
648 ,p_lookup_code => p_artv_rec.art1_code);
649
650 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
651 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
652 p_msg_name => g_invalid_value,
653 p_token1 => g_col_name_token,
654 p_token1_value => 'art1_code');
655
656 -- notify caller of an error
657 x_return_status := OKC_API.G_RET_STS_ERROR;
658 raise G_EXCEPTION_HALT_VALIDATION;
659
660 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
661
662 -- notify caller of an error
663 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
664 raise G_EXCEPTION_HALT_VALIDATION;
665
666 end if;
667
668
669 exception
670 when G_EXCEPTION_HALT_VALIDATION then
671 -- no processing necessary; validation can continue
672 -- with the next column
673 null;
674
675 when OTHERS then
676 -- store SQL error message on message stack for caller
677 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
678 p_msg_name => g_unexpected_error,
679 p_token1 => g_sqlcode_token,
680 p_token1_value => sqlcode,
681 p_token2 => g_sqlerrm_token,
682 p_token2_value => sqlerrm);
683
684 -- notify caller of an UNEXPECTED error
685 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
686
687 end validate_art1_code;
688
689
690 -- Start of comments
691 --
692 -- Procedure Name : validate_relocate_asset_yn
693 -- Description :
694 -- Business Rules :
695 -- Parameters :
696 -- Version : 1.0
697 -- End of comments
698
699
700 PROCEDURE validate_relocate_asset_yn (
701 x_return_status OUT NOCOPY VARCHAR2,
702 p_artv_rec IN artv_rec_type ) IS
703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
704 BEGIN
705 -- intialize return status
706 x_return_status := OKC_API.G_RET_STS_SUCCESS;
707
708 -- check from domain values using the generic okl_util.check_domain_yn
709 l_return_status := OKL_UTIL.check_domain_yn(p_artv_rec.relocate_asset_yn);
710
711 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
712 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
713 p_msg_name => g_invalid_value,
714 p_token1 => g_col_name_token,
715 p_token1_value => 'relocate_asset_yn');
716
717 -- notify caller of an error
718 x_return_status := OKC_API.G_RET_STS_ERROR;
719 raise G_EXCEPTION_HALT_VALIDATION;
720
721 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
722
723 -- notify caller of an error
724 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
725 raise G_EXCEPTION_HALT_VALIDATION;
726
727 END IF;
728
729 EXCEPTION
730 WHEN G_EXCEPTION_HALT_VALIDATION then
731 -- No action necessary. Validation can continue to next attribute/column
732 null;
733
734 WHEN OTHERS THEN
735 -- store SQL error message on message stack for caller
736 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
737 p_msg_name => g_unexpected_error,
738 p_token1 => g_sqlcode_token,
739 p_token1_value => sqlcode,
740 p_token2 => g_sqlerrm_token,
741 p_token2_value => sqlerrm);
742
743 -- notify caller of an UNEXPECTED error
744 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
745
746 END validate_relocate_asset_yn;
747
748
749
750
751
752 PROCEDURE validate_org_id(
753 x_return_status OUT NOCOPY VARCHAR2,
754 p_artv_rec IN artv_rec_type) IS
755 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
756 BEGIN
757 -- initialize return status
758 x_return_status := OKC_API.G_RET_STS_SUCCESS;
759
760 -- check org id validity using the generic function okl_util.check_org_id()
761 l_return_status := OKL_UTIL.check_org_id (p_artv_rec.org_id);
762
763 IF ( l_return_status = OKC_API.G_RET_STS_ERROR) THEN
764 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
765 p_msg_name => g_invalid_value,
766 p_token1 => g_col_name_token,
767 p_token1_value => 'org_id');
768
769 -- notify caller of an error
770 x_return_status := OKC_API.G_RET_STS_ERROR;
771 raise G_EXCEPTION_HALT_VALIDATION;
772
773 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
774
775 -- notify caller of an error
776 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
777 raise G_EXCEPTION_HALT_VALIDATION;
778
779 END IF;
780
781 EXCEPTION
782 WHEN G_EXCEPTION_HALT_VALIDATION then
783 -- No action necessary. Validation can continue to next attribute/column
784 null;
785
786 WHEN OTHERS THEN
787 -- store SQL error message on message stack for caller
788 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
789 p_msg_name => g_unexpected_error,
790 p_token1 => g_sqlcode_token,
791 p_token1_value => sqlcode,
792 p_token2 => g_sqlerrm_token,
793 p_token2_value => sqlerrm);
794
795 -- notify caller of an UNEXPECTED error
796 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
797
798 END validate_org_id;
799
800
801 -- Foreign Key Validation
802
803 -- Start of comments
804 --
805 -- Procedure Name : validate_sec_dep_trx_ap_id
806 -- Description :
807 -- Business Rules :
808 -- Parameters :
809 -- Version : 1.0
810 -- End of comments
811
812 procedure validate_sec_dep_trx_ap_id(
813 x_return_status OUT NOCOPY VARCHAR2,
814 p_artv_rec in artv_rec_type
815 )IS
816 CURSOR l_sdt_csr IS
817 select 'x' from OKL_TRX_AP_INVOICES_V
818 where ID = p_artv_rec.security_dep_trx_ap_id;
819
820 l_dummy_var VARCHAR2(1) := '?';
821
822 begin
823 -- initialize return status
824 x_return_status := OKC_API.G_RET_STS_SUCCESS;
825
826 -- enforce foreign key
827 IF (p_artv_rec.security_dep_trx_ap_id IS NOT NULL) THEN
828 OPEN l_sdt_csr;
829 FETCH l_sdt_csr INTO l_dummy_var;
830 CLOSE l_sdt_csr;
831
832 -- if l_dummy_var is still set to default, data was not found
833 IF (l_dummy_var = '?') THEN
834 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
835 p_msg_name => G_NO_PARENT_RECORD,
836 p_token1 => G_COL_NAME_TOKEN,
837 p_token1_value => 'security_dep_trx_ap_id',
838 p_token2 => G_CHILD_TABLE_TOKEN,
839 p_token2_value => 'OKL_ASSET_RETURNS_V',
840 p_token3 => G_PARENT_TABLE_TOKEN,
841 p_token3_value => 'OKL_TRX_AP_INVOICES_V');
842
843 -- notify caller of an error
844 x_return_status := OKC_API.G_RET_STS_ERROR;
845 END IF;
846 END IF;
847
848 EXCEPTION
849 WHEN OTHERS THEN
850 -- store SQL error message on message stack for caller
851 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
852 p_msg_name => g_unexpected_error,
853 p_token1 => g_sqlcode_token,
854 p_token1_value => sqlcode,
855 p_token2 => g_sqlerrm_token,
856 p_token2_value => sqlerrm);
857
858 -- notify caller of an UNEXPECTED error
859 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
860
861 -- verify that cursor was closed
862 IF l_sdt_csr%ISOPEN THEN
863 CLOSE l_sdt_csr;
864 END IF;
865 end validate_sec_dep_trx_ap_id;
866
867 -- Start of comments
868 --
869 -- Procedure Name : validate_iso_id
870 -- Description :
871 -- Business Rules :
872 -- Parameters :
873 -- Version : 1.0
874 -- End of comments
875
876 procedure validate_iso_id(
877 x_return_status OUT NOCOPY VARCHAR2,
878 p_artv_rec in artv_rec_type
879 )
880 IS
881 /*CURSOR l_iso_csr IS
882 select 'x' from OKX_SELL_ORDERS_V
883 where ID = p_artv_rec.iso_id; */
884 l_dummy_var VARCHAR2(1) := '?';
885
886 begin
887 NULL;
888 /*
889 -- initialize return status
890 x_return_status := OKC_API.G_RET_STS_SUCCESS;
891
892 -- enforce foreign key
893 IF (p_artv_rec.iso_id IS NOT NULL) THEN
894 OPEN l_iso_csr;
895 FETCH l_iso_csr INTO l_dummy_var;
896 CLOSE l_iso_csr;
897
898 -- if l_dummy_var is still set to default, data was not found
899 IF (l_dummy_var = '?') THEN
900 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
901 p_msg_name => G_NO_PARENT_RECORD,
902 p_token1 => G_COL_NAME_TOKEN,
903 p_token1_value => 'iso_id',
904 p_token2 => G_CHILD_TABLE_TOKEN,
905 p_token2_value => 'OKL_ASSET_RETURNS_V',
906 p_token3 => G_PARENT_TABLE_TOKEN,
907 p_token3_value => 'OKX_SELL_ORDERS_V');
908
909 -- notify caller of an error
910 x_return_status := OKC_API.G_RET_STS_ERROR;
911 END IF;
912 END IF;
913
914 EXCEPTION
915 WHEN OTHERS THEN
916 -- store SQL error message on message stack for caller
917 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
918 p_msg_name => g_unexpected_error,
919 p_token1 => g_sqlcode_token,
920 p_token1_value => sqlcode,
921 p_token2 => g_sqlerrm_token,
922 p_token2_value => sqlerrm);
923
924 -- notify caller of an UNEXPECTED error
925 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
926
927 -- verify that cursor was closed
928 IF l_iso_csr%ISOPEN THEN
929 CLOSE l_iso_csr;
930 END IF;
931 */
932 end validate_iso_id;
933
934 -- Start of comments
935 --
936 -- Procedure Name : validate_rna_id
937 -- Description :
938 -- Business Rules :
939 -- Parameters :
940 -- Version : 1.0
941 -- End of comments
942
943 procedure validate_rna_id(
944 x_return_status OUT NOCOPY VARCHAR2,
945 p_artv_rec in artv_rec_type
946 )
947 IS
948
949 CURSOR l_rna_csr IS
950 select 'x' from OKX_VENDORS_V
951 where ID1 = p_artv_rec.rna_id;
952
953 l_dummy_var VARCHAR2(1) := '?';
954 begin
955
956 -- initialize return status
957 x_return_status := OKC_API.G_RET_STS_SUCCESS;
958
959 -- enforce foreign key
960 IF (p_artv_rec.rna_id IS NOT NULL) THEN
961 OPEN l_rna_csr;
962 FETCH l_rna_csr INTO l_dummy_var;
963 CLOSE l_rna_csr;
964
965 -- if l_dummy_var is still set to default, data was not found
966 IF (l_dummy_var = '?') THEN
967 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
968 p_msg_name => G_NO_PARENT_RECORD,
969 p_token1 => G_COL_NAME_TOKEN,
970 p_token1_value => 'rna_id',
971 p_token2 => G_CHILD_TABLE_TOKEN,
972 p_token2_value => 'OKL_ASSET_RETURNS_V',
973 p_token3 => G_PARENT_TABLE_TOKEN,
974 p_token3_value => 'OKX_VENDORS_V');
975
976 -- notify caller of an error
977 x_return_status := OKC_API.G_RET_STS_ERROR;
978 END IF;
979 END IF;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 -- store SQL error message on message stack for caller
984 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
985 p_msg_name => g_unexpected_error,
986 p_token1 => g_sqlcode_token,
987 p_token1_value => sqlcode,
988 p_token2 => g_sqlerrm_token,
989 p_token2_value => sqlerrm);
990
991 -- notify caller of an UNEXPECTED error
992 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
993
994 -- verify that cursor was closed
995 IF l_rna_csr%ISOPEN THEN
996 CLOSE l_rna_csr;
997 END IF;
998
999 end validate_rna_id;
1000
1001 -- Start of comments
1002 --
1003 -- Procedure Name : validate_rmr_id
1004 -- Description :
1005 -- Business Rules :
1006 -- Parameters :
1007 -- Version : 1.0
1008 -- End of comments
1009
1010 procedure validate_rmr_id(
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 p_artv_rec in artv_rec_type
1013 )
1014 IS
1015
1016 CURSOR l_rmr_csr IS
1017 select 'x' from OKL_AM_REMARKET_TEAMS_UV
1018 where ORIG_SYSTEM_ID = p_artv_rec.rmr_id;
1019
1020
1021
1022 l_dummy_var VARCHAR2(1) := '?';
1023 begin
1024
1025 -- initialize return status
1026 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1027
1028 -- enforce foreign key
1029 IF (p_artv_rec.rmr_id IS NOT NULL) THEN
1030 OPEN l_rmr_csr;
1031 FETCH l_rmr_csr INTO l_dummy_var;
1032 CLOSE l_rmr_csr;
1033
1034 -- if l_dummy_var is still set to default, data was not found
1035 IF (l_dummy_var = '?') THEN
1036 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1037 p_msg_name => G_NO_PARENT_RECORD,
1038 p_token1 => G_COL_NAME_TOKEN,
1039 p_token1_value => 'rmr_id',
1040 p_token2 => G_CHILD_TABLE_TOKEN,
1041 p_token2_value => 'OKL_ASSET_RETURNS_V',
1042 p_token3 => G_PARENT_TABLE_TOKEN,
1043 p_token3_value => 'OKL_AM_REMARKET_TEAMS_UV');
1044
1045 -- notify caller of an error
1046 x_return_status := OKC_API.G_RET_STS_ERROR;
1047 END IF;
1048 END IF;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 -- store SQL error message on message stack for caller
1053 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1054 p_msg_name => g_unexpected_error,
1055 p_token1 => g_sqlcode_token,
1056 p_token1_value => sqlcode,
1057 p_token2 => g_sqlerrm_token,
1058 p_token2_value => sqlerrm);
1059
1060 -- notify caller of an UNEXPECTED error
1061 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1062
1063 -- verify that cursor was closed
1064 IF l_rmr_csr%ISOPEN THEN
1065 CLOSE l_rmr_csr;
1066 END IF;
1067
1068 end validate_rmr_id;
1069
1070 -- Start of comments
1071 --
1072 -- Procedure Name : validate_imr_id
1073 -- Description :
1074 -- Business Rules :
1075 -- Parameters :
1076 -- Version : 1.0
1077 -- End of comments
1078
1079 procedure validate_imr_id(
1080 x_return_status OUT NOCOPY VARCHAR2,
1081 p_artv_rec in artv_rec_type
1082 )
1083
1084 IS
1085
1086 CURSOR l_imr_csr IS
1087 select 'x' from OKX_SYSTEM_ITEMS_V
1088 where ID1 = p_artv_rec.imr_id;
1089
1090 l_dummy_var VARCHAR2(1) := '?';
1091 begin
1092
1093
1094 -- initialize return status
1095 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1096
1097 -- enforce foreign key
1098 IF (p_artv_rec.imr_id IS NOT NULL) THEN
1099 OPEN l_imr_csr;
1100 FETCH l_imr_csr INTO l_dummy_var;
1101 CLOSE l_imr_csr;
1102
1103 -- if l_dummy_var is still set to default, data was not found
1104 IF (l_dummy_var = '?') THEN
1105 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1106 p_msg_name => G_NO_PARENT_RECORD,
1107 p_token1 => G_COL_NAME_TOKEN,
1108 p_token1_value => 'imr_id',
1109 p_token2 => G_CHILD_TABLE_TOKEN,
1110 p_token2_value => 'OKL_ASSET_RETURNS_V',
1111 p_token3 => G_PARENT_TABLE_TOKEN,
1112 p_token3_value => 'OKX_SYSTEM_ITEMS_V');
1113
1114 -- notify caller of an error
1115 x_return_status := OKC_API.G_RET_STS_ERROR;
1116 END IF;
1117 END IF;
1118
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 -- store SQL error message on message stack for caller
1122 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1123 p_msg_name => g_unexpected_error,
1124 p_token1 => g_sqlcode_token,
1125 p_token1_value => sqlcode,
1126 p_token2 => g_sqlerrm_token,
1127 p_token2_value => sqlerrm);
1128
1129 -- notify caller of an UNEXPECTED error
1130 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1131
1132 -- verify that cursor was closed
1133 IF l_imr_csr%ISOPEN THEN
1134 CLOSE l_imr_csr;
1135 END IF;
1136
1137 end validate_imr_id;
1138
1139
1140 -- Start of comments
1141 --
1142 -- Procedure Name : validate_asset_relocated_yn
1143 -- Description :
1144 -- Business Rules :
1145 -- Parameters :
1146 -- Version : 1.0
1147 -- End of comments
1148 PROCEDURE validate_asset_relocated_yn(
1149 x_return_status OUT NOCOPY VARCHAR2,
1150 p_artv_rec in artv_rec_type) is
1151
1152
1153 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1154 BEGIN
1155 -- initialize return status
1156 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1157
1158 -- check from domain values using the generic okl_util.check_domain_yn
1159 l_return_status := OKL_UTIL.check_domain_yn(p_artv_rec.asset_relocated_yn);
1160
1161 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1162 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1163 p_msg_name => g_invalid_value,
1164 p_token1 => g_col_name_token,
1165 p_token1_value => 'asset_relocated_yn');
1166
1167 -- notify caller of an error
1168 x_return_status := OKC_API.G_RET_STS_ERROR;
1169 RAISE G_EXCEPTION_HALT_VALIDATION;
1170
1171 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1172
1173 -- notify caller of an error
1174 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1175 RAISE G_EXCEPTION_HALT_VALIDATION;
1176
1177 END IF;
1178
1179 EXCEPTION
1180 WHEN G_EXCEPTION_HALT_VALIDATION then
1181 -- no processing necessary; validation can continue
1182 -- with the next column
1183 null;
1184
1185 WHEN OTHERS THEN
1186 -- store SQL error message on message stack for caller
1187 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1188 p_msg_name => g_unexpected_error,
1189 p_token1 => g_sqlcode_token,
1190 p_token1_value => SQLCODE,
1191 p_token2 => g_sqlerrm_token,
1192 p_token2_value => SQLERRM);
1193
1194 -- notify caller of an UNEXPECTED error
1195 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1196
1197 END validate_asset_relocated_yn;
1198
1199 -- Start of comments
1200 --
1201 -- Procedure Name : validate_commmercially_reas_yn
1202 -- Description :
1203 -- Business Rules :
1204 -- Parameters :
1205 -- Version : 1.0
1206 -- End of comments
1207
1208
1209 PROCEDURE validate_commmercially_reas_yn (
1210 x_return_status OUT NOCOPY VARCHAR2,
1211 p_artv_rec IN artv_rec_type ) IS
1212 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1213 BEGIN
1214 -- intialize return status
1215 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1216
1217 -- check from domain values using the generic okl_util.check_domain_yn
1218 l_return_status := OKL_UTIL.check_domain_yn(p_artv_rec.commmercially_reas_sale_yn);
1219
1220 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1221 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1222 p_msg_name => g_invalid_value,
1223 p_token1 => g_col_name_token,
1224 p_token1_value => 'commmercially_reas_sale_yn');
1225
1226 -- notify caller of an error
1227 x_return_status := OKC_API.G_RET_STS_ERROR;
1228 raise G_EXCEPTION_HALT_VALIDATION;
1229
1230 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1231
1232 -- notify caller of an error
1233 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1234 raise G_EXCEPTION_HALT_VALIDATION;
1235
1236 END IF;
1237
1238 EXCEPTION
1239 WHEN G_EXCEPTION_HALT_VALIDATION then
1240 -- No action necessary. Validation can continue to next attribute/column
1241 null;
1242
1243 WHEN OTHERS THEN
1244 -- store SQL error message on message stack for caller
1245 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1246 p_msg_name => g_unexpected_error,
1247 p_token1 => g_sqlcode_token,
1248 p_token1_value => sqlcode,
1249 p_token2 => g_sqlerrm_token,
1250 p_token2_value => sqlerrm);
1251
1252 -- notify caller of an UNEXPECTED error
1253 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1254
1255 END validate_commmercially_reas_yn;
1256
1257
1258 -- Start of comments
1259 --
1260 -- Procedure Name : validate_voluntary_yn
1261 -- Description :
1262 -- Business Rules :
1263 -- Parameters :
1264 -- Version : 1.0
1265 -- End of comments
1266
1267
1268 PROCEDURE validate_voluntary_yn (
1269 x_return_status OUT NOCOPY VARCHAR2,
1270 p_artv_rec IN artv_rec_type ) IS
1271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1272 BEGIN
1273 -- intialize return status
1274 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1275
1276 -- check from domain values using the generic okl_util.check_domain_yn
1277 l_return_status := OKL_UTIL.check_domain_yn(p_artv_rec.voluntary_yn);
1278
1279 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1280 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1281 p_msg_name => g_invalid_value,
1282 p_token1 => g_col_name_token,
1283 p_token1_value => 'voluntary_yn');
1284
1285 -- notify caller of an error
1286 x_return_status := OKC_API.G_RET_STS_ERROR;
1287 raise G_EXCEPTION_HALT_VALIDATION;
1288
1289 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1290
1291 -- notify caller of an error
1292 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1293 raise G_EXCEPTION_HALT_VALIDATION;
1294
1295 END IF;
1296
1297 EXCEPTION
1298 WHEN G_EXCEPTION_HALT_VALIDATION then
1299 -- No action necessary. Validation can continue to next attribute/column
1300 null;
1301
1302 WHEN OTHERS THEN
1303 -- store SQL error message on message stack for caller
1304 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1305 p_msg_name => g_unexpected_error,
1306 p_token1 => g_sqlcode_token,
1307 p_token1_value => sqlcode,
1308 p_token2 => g_sqlerrm_token,
1309 p_token2_value => sqlerrm);
1310
1311 -- notify caller of an UNEXPECTED error
1312 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1313
1314 END validate_voluntary_yn;
1315
1316
1317
1318 -- Start of comments
1319 --
1320 -- Procedure Name : validate_repurchase_agmt_yn
1321 -- Description :
1322 -- Business Rules :
1323 -- Parameters :
1324 -- Version : 1.0
1325 -- End of comments
1326
1327
1328 PROCEDURE validate_repurchase_agmt_yn (
1329 x_return_status OUT NOCOPY VARCHAR2,
1330 p_artv_rec IN artv_rec_type ) IS
1331 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1332 BEGIN
1333 -- intialize return status
1334 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1335
1336 -- check from domain values using the generic okl_util.check_domain_yn
1337 l_return_status := OKL_UTIL.check_domain_yn(p_artv_rec.repurchase_agmt_yn);
1338
1339 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1340 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1341 p_msg_name => g_invalid_value,
1342 p_token1 => g_col_name_token,
1343 p_token1_value => 'repurchase_agmt_yn');
1344
1345 -- notify caller of an error
1346 x_return_status := OKC_API.G_RET_STS_ERROR;
1347 raise G_EXCEPTION_HALT_VALIDATION;
1348
1349 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1350
1351 -- notify caller of an error
1352 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1353 raise G_EXCEPTION_HALT_VALIDATION;
1354
1355 END IF;
1356
1357 EXCEPTION
1358 WHEN G_EXCEPTION_HALT_VALIDATION then
1359 -- No action necessary. Validation can continue to next attribute/column
1360 null;
1361
1362 WHEN OTHERS THEN
1363 -- store SQL error message on message stack for caller
1364 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1365 p_msg_name => g_unexpected_error,
1366 p_token1 => g_sqlcode_token,
1367 p_token1_value => sqlcode,
1368 p_token2 => g_sqlerrm_token,
1369 p_token2_value => sqlerrm);
1370
1371 -- notify caller of an UNEXPECTED error
1372 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1373
1374 END validate_repurchase_agmt_yn;
1375
1376
1377 -- Start of comments
1378 --
1379 -- Procedure Name : validate_like_kind_yn
1380 -- Description :
1381 -- Business Rules :
1382 -- Parameters :
1383 -- Version : 1.0
1384 -- End of comments
1385
1386
1387 PROCEDURE validate_like_kind_yn (
1388 x_return_status OUT NOCOPY VARCHAR2,
1389 p_artv_rec IN artv_rec_type ) IS
1390 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1391 BEGIN
1392 -- intialize return status
1393 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1394
1395 -- check from domain values using the generic okl_util.check_domain_yn
1396 l_return_status := OKL_UTIL.check_domain_yn(p_artv_rec.like_kind_yn);
1397
1398 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1399 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1400 p_msg_name => g_invalid_value,
1401 p_token1 => g_col_name_token,
1402 p_token1_value => 'like_kind_yn');
1403
1404 -- notify caller of an error
1405 x_return_status := OKC_API.G_RET_STS_ERROR;
1406 raise G_EXCEPTION_HALT_VALIDATION;
1407
1408 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1409
1410 -- notify caller of an error
1411 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1412 raise G_EXCEPTION_HALT_VALIDATION;
1413
1414 END IF;
1415
1416 EXCEPTION
1417 WHEN G_EXCEPTION_HALT_VALIDATION then
1418 -- No action necessary. Validation can continue to next attribute/column
1419 null;
1420
1421 WHEN OTHERS THEN
1422 -- store SQL error message on message stack for caller
1423 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1424 p_msg_name => g_unexpected_error,
1425 p_token1 => g_sqlcode_token,
1426 p_token1_value => sqlcode,
1427 p_token2 => g_sqlerrm_token,
1428 p_token2_value => sqlerrm);
1429
1430 -- notify caller of an UNEXPECTED error
1431 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1432
1433 END validate_like_kind_yn;
1434
1435 -- Start of comments
1436 --
1437 -- Procedure Name : is_unique
1438 -- Description : Do not create a new return if any return exists with status other than cancelled
1439 -- Do not update a return with status cancelled
1440 -- Do not update a return to status other than cancelled,
1441 -- if other return with status other than cancelled exists
1442 -- Business Rules :
1443 -- Parameters :
1444 -- Version : 1.0
1445 -- End of comments
1446 procedure is_unique(
1447 p_artv_rec in artv_rec_type,
1448 x_return_status out nocopy VARCHAR2) is
1449 -- Cursor to check whether create or update mode
1450 CURSOR okl_cre_upd_csr ( p_id IN NUMBER) IS
1451 SELECT id, ars_code
1452 FROM OKL_ASSET_RETURNS_V
1453 WHERE id = p_id;
1454
1455 -- Cursor to get ars code if a asset return exists (create mode)
1456 CURSOR okl_ars_cre_csr ( p_kle_id IN NUMBER) IS
1457 SELECT ars_code
1458 FROM OKL_ASSET_RETURNS_V
1459 WHERE kle_id = p_kle_id;
1460
1461 -- Cursor to get ars code if a asset return exists (update mode)
1462 CURSOR okl_ars_upd_csr ( p_id IN NUMBER, p_kle_id IN NUMBER) IS
1463 SELECT ars_code
1464 FROM OKL_ASSET_RETURNS_V
1465 WHERE kle_id = p_kle_id
1466 AND id <> p_id;
1467
1468 -- Cursor to get the meaning of the fnd_lookup value
1469 CURSOR okl_get_meaning_csr (p_code IN VARCHAR2) IS
1470 SELECT meaning
1471 FROM FND_LOOKUPS
1472 WHERE lookup_code = p_code
1473 AND lookup_type = 'OKL_ASSET_RETURN_STATUS';
1474
1475 -- Cursor to get the name of the asset
1476 CURSOR okl_get_asset_name_csr ( p_kle_id IN NUMBER) IS
1477 SELECT name
1478 FROM OKC_K_LINES_V
1479 WHERE id = p_kle_id;
1480
1481 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1482 l_id NUMBER;
1483 l_ars_code VARCHAR2(200);
1484 l_ars_code_db VARCHAR2(200);
1485 l_meaning VARCHAR2(2000);
1486 l_asset_name VARCHAR2(2000);
1487 begin
1488 -- initialize return status
1489 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1490 -- Check if value passed for id
1491 IF (p_artv_rec.id IS NOT NULL AND p_artv_rec.id <> OKC_API.G_MISS_NUM) THEN
1492 OPEN okl_cre_upd_csr(p_artv_rec.id);
1493 FETCH okl_cre_upd_csr INTO l_id, l_ars_code_db;
1494 -- If id already exists then update mode
1495 IF okl_cre_upd_csr%FOUND THEN
1496 -- If changing the return status which was cancelled.
1497 IF (l_ars_code_db = 'CANCELLED' AND p_artv_rec.ars_code <> 'CANCELLED')THEN
1498
1499 OPEN okl_get_meaning_csr(l_ars_code);
1500 FETCH okl_get_meaning_csr INTO l_meaning;
1501 CLOSE okl_get_meaning_csr;
1502
1503 -- Cannot update a cancelled Asset Return.
1504 OKL_API.SET_MESSAGE( p_app_name => 'OKL'
1505 ,p_msg_name => 'OKL_AM_ASS_RET_UPD_ERR'
1506 ,p_token1 => 'STATUS'
1507 ,p_token1_value => l_meaning);
1508 -- notify caller of an error
1509 l_return_status := OKC_API.G_RET_STS_ERROR;
1510 END IF;
1511 -- enforce uniqueness
1512 OPEN okl_ars_upd_csr(p_artv_rec.id,p_artv_rec.kle_id);
1513 FETCH okl_ars_upd_csr INTO l_ars_code;
1514 IF okl_ars_upd_csr%FOUND THEN
1515 IF (l_ars_code <> 'CANCELLED' AND p_artv_rec.ars_code <> 'CANCELLED') THEN
1516
1517 OPEN okl_get_meaning_csr(l_ars_code);
1518 FETCH okl_get_meaning_csr INTO l_meaning;
1519 CLOSE okl_get_meaning_csr;
1520
1521 OPEN okl_get_asset_name_csr(p_artv_rec.kle_id);
1522 FETCH okl_get_asset_name_csr INTO l_asset_name;
1523 CLOSE okl_get_asset_name_csr;
1524
1525 -- Asset Return already exists for this asset NAME with the status STATUS so cannot create a new asset return now.
1526 OKL_API.SET_MESSAGE( p_app_name => 'OKL'
1527 ,p_msg_name => 'OKL_AM_ASS_RET_ARS_ERR'
1528 ,p_token1 => 'NAME'
1529 ,p_token1_value => l_asset_name
1530 ,p_token2 => 'STATUS'
1531 ,p_token2_value => l_meaning);
1532 -- notify caller of an error
1533 l_return_status := OKC_API.G_RET_STS_ERROR;
1534 END IF;
1535 END IF;
1536 CLOSE okl_ars_upd_csr;
1537 ELSE -- id does not exists, so create mode
1538 -- enforce uniqueness
1539 OPEN okl_ars_cre_csr(p_artv_rec.kle_id);
1540 FETCH okl_ars_cre_csr INTO l_ars_code;
1541 IF okl_ars_cre_csr%FOUND THEN
1542 IF (l_ars_code <> 'CANCELLED') THEN
1543
1544 OPEN okl_get_meaning_csr(l_ars_code);
1545 FETCH okl_get_meaning_csr INTO l_meaning;
1546 CLOSE okl_get_meaning_csr;
1547
1548 OPEN okl_get_asset_name_csr(p_artv_rec.kle_id);
1549 FETCH okl_get_asset_name_csr INTO l_asset_name;
1550 CLOSE okl_get_asset_name_csr;
1551
1552 -- Asset Return already exists for this asset NAME with the status STATUS so cannot create a new asset return now.
1553 OKL_API.SET_MESSAGE( p_app_name => 'OKL'
1554 ,p_msg_name => 'OKL_AM_ASS_RET_ARS_ERR'
1555 ,p_token1 => 'NAME'
1556 ,p_token1_value => l_asset_name
1557 ,p_token2 => 'STATUS'
1558 ,p_token2_value => l_meaning);
1559 -- notify caller of an error
1560 l_return_status := OKC_API.G_RET_STS_ERROR;
1561 END IF;
1562 END IF;
1563 CLOSE okl_ars_cre_csr;
1564 END IF;
1565 CLOSE okl_cre_upd_csr;
1566 END IF;
1567
1568 x_return_status := l_return_status;
1569 EXCEPTION
1570 WHEN OTHERS THEN
1571 IF okl_cre_upd_csr%ISOPEN THEN
1572 CLOSE okl_cre_upd_csr;
1573 END IF;
1574 IF okl_ars_cre_csr%ISOPEN THEN
1575 CLOSE okl_ars_cre_csr;
1576 END IF;
1577 IF okl_ars_upd_csr%ISOPEN THEN
1578 CLOSE okl_ars_upd_csr;
1579 END IF;
1580 IF okl_get_meaning_csr%ISOPEN THEN
1581 CLOSE okl_get_meaning_csr;
1582 END IF;
1583 IF okl_get_asset_name_csr%ISOPEN THEN
1584 CLOSE okl_get_asset_name_csr;
1585 END IF;
1586
1587 -- store SQL error message on message stack for caller
1588 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1589 p_msg_name => g_unexpected_error,
1590 p_token1 => g_sqlcode_token,
1591 p_token1_value => sqlcode,
1592 p_token2 => g_sqlerrm_token,
1593 p_token2_value => sqlerrm);
1594
1595 -- notify caller of an UNEXPECTED error
1596 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1597
1598 end is_unique;
1599
1600 ---------------------------------------------------------------------------
1601 -- FUNCTION get_seq_id
1602 ---------------------------------------------------------------------------
1603 FUNCTION get_seq_id RETURN NUMBER IS
1604 BEGIN
1605 RETURN(okc_p_util.raw_to_number(sys_guid()));
1606 END get_seq_id;
1607
1608 ---------------------------------------------------------------------------
1609 -- PROCEDURE qc
1610 ---------------------------------------------------------------------------
1611 PROCEDURE qc IS
1612 BEGIN
1613 null;
1614 END qc;
1615
1616 ---------------------------------------------------------------------------
1617 -- PROCEDURE change_version
1618 ---------------------------------------------------------------------------
1619 PROCEDURE change_version IS
1620 BEGIN
1621 null;
1622 END change_version;
1623
1624 ---------------------------------------------------------------------------
1625 -- PROCEDURE api_copy
1626 ---------------------------------------------------------------------------
1627 PROCEDURE api_copy IS
1628 BEGIN
1629 null;
1630 END api_copy;
1631
1632 ---------------------------------------------------------------------------
1633 -- PROCEDURE add_language
1634 ---------------------------------------------------------------------------
1635 PROCEDURE add_language IS
1636 BEGIN
1637 DELETE FROM OKL_ASSET_RETURNS_TL T
1638 WHERE NOT EXISTS (
1639 SELECT NULL
1640 FROM OKL_ASSET_RETURNS_ALL_B B
1641 WHERE B.ID = T.ID
1642 );
1643
1644 UPDATE OKL_ASSET_RETURNS_TL T SET (
1645 COMMENTS) = (SELECT
1646 B.COMMENTS
1647 FROM OKL_ASSET_RETURNS_TL B
1648 WHERE B.ID = T.ID
1649 AND B.LANGUAGE = T.SOURCE_LANG)
1650 WHERE (
1651 T.ID,
1652 T.LANGUAGE)
1653 IN (SELECT
1654 SUBT.ID,
1655 SUBT.LANGUAGE
1656 FROM OKL_ASSET_RETURNS_TL SUBB, OKL_ASSET_RETURNS_TL SUBT
1657 WHERE SUBB.ID = SUBT.ID
1658 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
1659 AND (SUBB.COMMENTS <> SUBT.COMMENTS
1660 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
1661 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
1662 ));
1663
1664 INSERT INTO OKL_ASSET_RETURNS_TL (
1665 ID,
1666 LANGUAGE,
1667 SOURCE_LANG,
1668 SFWT_FLAG,
1669 COMMENTS,
1670 CREATED_BY,
1671 CREATION_DATE,
1672 LAST_UPDATED_BY,
1673 LAST_UPDATE_DATE,
1674 LAST_UPDATE_LOGIN,
1675 NEW_ITEM_DESCRIPTION)
1676 SELECT
1677 B.ID,
1678 L.LANGUAGE_CODE,
1679 B.SOURCE_LANG,
1680 B.SFWT_FLAG,
1681 B.COMMENTS,
1682 B.CREATED_BY,
1683 B.CREATION_DATE,
1684 B.LAST_UPDATED_BY,
1685 B.LAST_UPDATE_DATE,
1686 B.LAST_UPDATE_LOGIN,
1687 B.NEW_ITEM_DESCRIPTION
1688 FROM OKL_ASSET_RETURNS_TL B, FND_LANGUAGES L
1689 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1690 AND B.LANGUAGE = USERENV('LANG')
1691 AND NOT EXISTS(
1692 SELECT NULL
1693 FROM OKL_ASSET_RETURNS_TL T
1694 WHERE T.ID = B.ID
1695 AND T.LANGUAGE = L.LANGUAGE_CODE
1696 );
1697
1698 END add_language;
1699
1700 ---------------------------------------------------------------------------
1701 -- FUNCTION get_rec for: OKL_ASSET_RETURNS_B
1702 ---------------------------------------------------------------------------
1703 -- Start of comments
1704 --
1705 -- Function Name : get_rec
1706 -- Description : for: OKL_ASSET_RETURNS_B
1707 -- Business Rules :
1708 -- Parameters :
1709 -- Version : 1.0
1710 -- History : RABHUPAT 16-DEC-2002 2667636
1711 -- :modified for multicurrency changes
1712 -- End of comments
1713 FUNCTION get_rec (
1714 p_art_rec IN art_rec_type,
1715 x_no_data_found OUT NOCOPY BOOLEAN
1716 ) RETURN art_rec_type IS
1717 CURSOR okl_asset_returns_b_pk_csr (p_id IN NUMBER) IS
1718 SELECT
1719 ID,
1720 KLE_ID,
1721 SECURITY_DEP_TRX_AP_ID,
1722 ISO_ID,
1723 RNA_ID,
1724 RMR_ID,
1725 ARS_CODE,
1726 IMR_ID,
1727 ART1_CODE,
1728 DATE_RETURN_DUE,
1729 DATE_RETURN_NOTIFIED,
1730 RELOCATE_ASSET_YN,
1731 OBJECT_VERSION_NUMBER,
1732 VOLUNTARY_YN,
1733 COMMMERCIALLY_REAS_SALE_YN,
1734 DATE_REPOSSESSION_REQUIRED,
1735 DATE_REPOSSESSION_ACTUAL,
1736 DATE_HOLD_UNTIL,
1737 DATE_RETURNED,
1738 DATE_TITLE_RETURNED,
1739 ORG_ID,
1740 REQUEST_ID,
1741 PROGRAM_APPLICATION_ID,
1742 PROGRAM_ID,
1743 PROGRAM_UPDATE_DATE,
1744 ATTRIBUTE_CATEGORY,
1745 ATTRIBUTE1,
1746 ATTRIBUTE2,
1747 ATTRIBUTE3,
1748 ATTRIBUTE4,
1749 ATTRIBUTE5,
1750 ATTRIBUTE6,
1751 ATTRIBUTE7,
1752 ATTRIBUTE8,
1753 ATTRIBUTE9,
1754 ATTRIBUTE10,
1755 ATTRIBUTE11,
1756 ATTRIBUTE12,
1757 ATTRIBUTE13,
1758 ATTRIBUTE14,
1759 ATTRIBUTE15,
1760 CREATED_BY,
1761 CREATION_DATE,
1762 LAST_UPDATED_BY,
1763 LAST_UPDATE_DATE,
1764 LAST_UPDATE_LOGIN,
1765 FLOOR_PRICE,
1766 NEW_ITEM_PRICE,
1767 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
1768 NEW_ITEM_NUMBER,
1769 ASSET_RELOCATED_YN,
1770 REPURCHASE_AGMT_YN,
1771 LIKE_KIND_YN,
1772 -- RABHUPAT - 2667636 - Start
1773 CURRENCY_CODE,
1774 CURRENCY_CONVERSION_CODE,
1775 CURRENCY_CONVERSION_TYPE,
1776 CURRENCY_CONVERSION_RATE,
1777 CURRENCY_CONVERSION_DATE,
1778 -- RABHUPAT - 2667636 - End
1779 -- RRAVIKIR Legal Entity Changes
1780 LEGAL_ENTITY_ID,
1781 -- Legal Entity Changes End
1782 -- DJANASWA Loan Repossession proj start
1783 ASSET_FMV_AMOUNT
1784 -- Loan Repossession proj end
1785 FROM Okl_Asset_Returns_B
1786 WHERE okl_asset_returns_b.id = p_id;
1787 l_okl_asset_returns_b_pk okl_asset_returns_b_pk_csr%ROWTYPE;
1788 l_art_rec art_rec_type;
1789 BEGIN
1790 x_no_data_found := TRUE;
1791 -- Get current database values
1792 OPEN okl_asset_returns_b_pk_csr (p_art_rec.id);
1793 FETCH okl_asset_returns_b_pk_csr INTO
1794 l_art_rec.ID,
1795 l_art_rec.KLE_ID,
1796 l_art_rec.SECURITY_DEP_TRX_AP_ID,
1797 l_art_rec.ISO_ID,
1798 l_art_rec.RNA_ID,
1799 l_art_rec.RMR_ID,
1800 l_art_rec.ARS_CODE,
1801 l_art_rec.IMR_ID,
1802 l_art_rec.ART1_CODE,
1803 l_art_rec.DATE_RETURN_DUE,
1804 l_art_rec.DATE_RETURN_NOTIFIED,
1805 l_art_rec.RELOCATE_ASSET_YN,
1806 l_art_rec.OBJECT_VERSION_NUMBER,
1807 l_art_rec.VOLUNTARY_YN,
1808 l_art_rec.COMMMERCIALLY_REAS_SALE_YN,
1809 l_art_rec.DATE_REPOSSESSION_REQUIRED,
1810 l_art_rec.DATE_REPOSSESSION_ACTUAL,
1811 l_art_rec.DATE_HOLD_UNTIL,
1812 l_art_rec.DATE_RETURNED,
1813 l_art_rec.DATE_TITLE_RETURNED,
1814 l_art_rec.ORG_ID,
1815 l_art_rec.REQUEST_ID,
1816 l_art_rec.PROGRAM_APPLICATION_ID,
1817 l_art_rec.PROGRAM_ID,
1818 l_art_rec.PROGRAM_UPDATE_DATE,
1819 l_art_rec.ATTRIBUTE_CATEGORY,
1820 l_art_rec.ATTRIBUTE1,
1821 l_art_rec.ATTRIBUTE2,
1822 l_art_rec.ATTRIBUTE3,
1823 l_art_rec.ATTRIBUTE4,
1824 l_art_rec.ATTRIBUTE5,
1825 l_art_rec.ATTRIBUTE6,
1826 l_art_rec.ATTRIBUTE7,
1827 l_art_rec.ATTRIBUTE8,
1828 l_art_rec.ATTRIBUTE9,
1829 l_art_rec.ATTRIBUTE10,
1830 l_art_rec.ATTRIBUTE11,
1831 l_art_rec.ATTRIBUTE12,
1832 l_art_rec.ATTRIBUTE13,
1833 l_art_rec.ATTRIBUTE14,
1834 l_art_rec.ATTRIBUTE15,
1835 l_art_rec.CREATED_BY,
1836 l_art_rec.CREATION_DATE,
1837 l_art_rec.LAST_UPDATED_BY,
1838 l_art_rec.LAST_UPDATE_DATE,
1839 l_art_rec.LAST_UPDATE_LOGIN,
1840 l_art_rec.FLOOR_PRICE,
1841 l_art_rec.NEW_ITEM_PRICE,
1842 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
1843 l_art_rec.NEW_ITEM_NUMBER,
1844 l_art_rec.ASSET_RELOCATED_YN,
1845 l_art_rec.REPURCHASE_AGMT_YN,
1846 l_art_rec.LIKE_KIND_YN,
1847 -- RABHUPAT - 2667636 - Start
1848 l_art_rec.CURRENCY_CODE,
1849 l_art_rec.CURRENCY_CONVERSION_CODE,
1850 l_art_rec.CURRENCY_CONVERSION_TYPE,
1851 l_art_rec.CURRENCY_CONVERSION_RATE,
1852 l_art_rec.CURRENCY_CONVERSION_DATE,
1853 -- RABHUPAT - 2667636 - End
1854 -- RRAVIKIR Legal Entity Changes
1855 l_art_rec.LEGAL_ENTITY_ID,
1856 -- Legal Entity Changes End
1857 -- DJANASWA Loan Repossession proj start
1858 l_art_rec.ASSET_FMV_AMOUNT;
1859 -- Loan Repossession proj end
1860 x_no_data_found := okl_asset_returns_b_pk_csr%NOTFOUND;
1861 CLOSE okl_asset_returns_b_pk_csr;
1862 RETURN(l_art_rec);
1863 END get_rec;
1864
1865 FUNCTION get_rec (
1866 p_art_rec IN art_rec_type
1867 ) RETURN art_rec_type IS
1868 l_row_notfound BOOLEAN := TRUE;
1869 BEGIN
1870 RETURN(get_rec(p_art_rec, l_row_notfound));
1871 END get_rec;
1872 ---------------------------------------------------------------------------
1873 -- FUNCTION get_rec for: OKL_ASSET_RETURNS_TL
1874 ---------------------------------------------------------------------------
1875 FUNCTION get_rec (
1876 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
1877 x_no_data_found OUT NOCOPY BOOLEAN
1878 ) RETURN okl_asset_returns_tl_rec_type IS
1879 CURSOR okl_asset_returns_tl_pk_csr (p_id IN NUMBER,
1880 p_language IN VARCHAR2) IS
1881 SELECT
1882 ID,
1883 LANGUAGE,
1884 SOURCE_LANG,
1885 SFWT_FLAG,
1886 COMMENTS,
1887 CREATED_BY,
1888 CREATION_DATE,
1889 LAST_UPDATED_BY,
1890 LAST_UPDATE_DATE,
1891 LAST_UPDATE_LOGIN,
1892 NEW_ITEM_DESCRIPTION
1893 FROM Okl_Asset_Returns_Tl
1894 WHERE okl_asset_returns_tl.id = p_id
1895 AND okl_asset_returns_tl.language = p_language;
1896 l_okl_asset_returns_tl_pk okl_asset_returns_tl_pk_csr%ROWTYPE;
1897 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
1898 BEGIN
1899 x_no_data_found := TRUE;
1900 -- Get current database values
1901 OPEN okl_asset_returns_tl_pk_csr (p_okl_asset_returns_tl_rec.id,
1902 p_okl_asset_returns_tl_rec.language);
1903 FETCH okl_asset_returns_tl_pk_csr INTO
1904 l_okl_asset_returns_tl_rec.ID,
1905 l_okl_asset_returns_tl_rec.LANGUAGE,
1906 l_okl_asset_returns_tl_rec.SOURCE_LANG,
1907 l_okl_asset_returns_tl_rec.SFWT_FLAG,
1908 l_okl_asset_returns_tl_rec.COMMENTS,
1909 l_okl_asset_returns_tl_rec.CREATED_BY,
1910 l_okl_asset_returns_tl_rec.CREATION_DATE,
1911 l_okl_asset_returns_tl_rec.LAST_UPDATED_BY,
1912 l_okl_asset_returns_tl_rec.LAST_UPDATE_DATE,
1913 l_okl_asset_returns_tl_rec.LAST_UPDATE_LOGIN,
1914 l_okl_asset_returns_tl_rec.NEW_ITEM_DESCRIPTION;
1915 x_no_data_found := okl_asset_returns_tl_pk_csr%NOTFOUND;
1916 CLOSE okl_asset_returns_tl_pk_csr;
1917 RETURN(l_okl_asset_returns_tl_rec);
1918 END get_rec;
1919
1920 FUNCTION get_rec (
1921 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type
1922 ) RETURN okl_asset_returns_tl_rec_type IS
1923 l_row_notfound BOOLEAN := TRUE;
1924 BEGIN
1925 RETURN(get_rec(p_okl_asset_returns_tl_rec, l_row_notfound));
1926 END get_rec;
1927 ---------------------------------------------------------------------------
1928 -- FUNCTION get_rec for: OKL_ASSET_RETURNS_V
1929 ---------------------------------------------------------------------------
1930 -- Start of comments
1931 --
1932 -- Function Name : get_rec
1933 -- Description : for: OKL_ASSET_RETURNS_V
1934 -- Business Rules :
1935 -- Parameters :
1936 -- Version : 1.0
1937 -- History : RABHUPAT 16-DEC-2002 2667636
1938 -- :modified for multicurrency changes
1939 -- End of comments
1940 FUNCTION get_rec (
1941 p_artv_rec IN artv_rec_type,
1942 x_no_data_found OUT NOCOPY BOOLEAN
1943 ) RETURN artv_rec_type IS
1944 CURSOR okl_artv_pk_csr (p_id IN NUMBER) IS
1945 SELECT
1946 ID,
1947 OBJECT_VERSION_NUMBER,
1948 SFWT_FLAG,
1949 RMR_ID,
1950 IMR_ID,
1951 RNA_ID,
1952 KLE_ID,
1953 ISO_ID,
1954 SECURITY_DEP_TRX_AP_ID,
1955 ARS_CODE,
1956 ART1_CODE,
1957 DATE_RETURNED,
1958 DATE_TITLE_RETURNED,
1959 DATE_RETURN_DUE,
1960 DATE_RETURN_NOTIFIED,
1961 RELOCATE_ASSET_YN,
1962 VOLUNTARY_YN,
1963 DATE_REPOSSESSION_REQUIRED,
1964 DATE_REPOSSESSION_ACTUAL,
1965 DATE_HOLD_UNTIL,
1966 COMMMERCIALLY_REAS_SALE_YN,
1967 COMMENTS,
1968 ATTRIBUTE_CATEGORY,
1969 ATTRIBUTE1,
1970 ATTRIBUTE2,
1971 ATTRIBUTE3,
1972 ATTRIBUTE4,
1973 ATTRIBUTE5,
1974 ATTRIBUTE6,
1975 ATTRIBUTE7,
1976 ATTRIBUTE8,
1977 ATTRIBUTE9,
1978 ATTRIBUTE10,
1979 ATTRIBUTE11,
1980 ATTRIBUTE12,
1981 ATTRIBUTE13,
1982 ATTRIBUTE14,
1983 ATTRIBUTE15,
1984 ORG_ID,
1985 REQUEST_ID,
1986 PROGRAM_APPLICATION_ID,
1987 PROGRAM_ID,
1988 PROGRAM_UPDATE_DATE,
1989 CREATED_BY,
1990 CREATION_DATE,
1991 LAST_UPDATED_BY,
1992 LAST_UPDATE_DATE,
1993 LAST_UPDATE_LOGIN,
1994 FLOOR_PRICE,
1995 NEW_ITEM_PRICE,
1996 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
1997 NEW_ITEM_NUMBER,
1998 ASSET_RELOCATED_YN,
1999 NEW_ITEM_DESCRIPTION,
2000 REPURCHASE_AGMT_YN,
2001 LIKE_KIND_YN,
2002 -- RABHUPAT - 2667636 - Start
2003 CURRENCY_CODE,
2004 CURRENCY_CONVERSION_CODE,
2005 CURRENCY_CONVERSION_TYPE,
2006 CURRENCY_CONVERSION_RATE,
2007 CURRENCY_CONVERSION_DATE,
2008 -- RABHUPAT - 2667636 - End
2009 -- RRAVIKIR Legal Entity Changes
2010 LEGAL_ENTITY_ID,
2011 -- Legal Entity Changes End
2012 -- DJANASWA Loan Repossession proj start
2013 ASSET_FMV_AMOUNT
2014 -- Loan Repossession proj end
2015 FROM Okl_Asset_Returns_V
2016 WHERE okl_asset_returns_v.id = p_id;
2017 l_okl_artv_pk okl_artv_pk_csr%ROWTYPE;
2018 l_artv_rec artv_rec_type;
2019 BEGIN
2020 x_no_data_found := TRUE;
2021 -- Get current database values
2022 OPEN okl_artv_pk_csr (p_artv_rec.id);
2023 FETCH okl_artv_pk_csr INTO
2024 l_artv_rec.ID,
2025 l_artv_rec.OBJECT_VERSION_NUMBER,
2026 l_artv_rec.SFWT_FLAG,
2027 l_artv_rec.RMR_ID,
2028 l_artv_rec.IMR_ID,
2029 l_artv_rec.RNA_ID,
2030 l_artv_rec.KLE_ID,
2031 l_artv_rec.ISO_ID,
2032 l_artv_rec.SECURITY_DEP_TRX_AP_ID,
2033 l_artv_rec.ARS_CODE,
2034 l_artv_rec.ART1_CODE,
2035 l_artv_rec.DATE_RETURNED,
2036 l_artv_rec.DATE_TITLE_RETURNED,
2037 l_artv_rec.DATE_RETURN_DUE,
2038 l_artv_rec.DATE_RETURN_NOTIFIED,
2039 l_artv_rec.RELOCATE_ASSET_YN,
2040 l_artv_rec.VOLUNTARY_YN,
2041 l_artv_rec.DATE_REPOSSESSION_REQUIRED,
2042 l_artv_rec.DATE_REPOSSESSION_ACTUAL,
2043 l_artv_rec.DATE_HOLD_UNTIL,
2044 l_artv_rec.COMMMERCIALLY_REAS_SALE_YN,
2045 l_artv_rec.COMMENTS,
2046 l_artv_rec.ATTRIBUTE_CATEGORY,
2047 l_artv_rec.ATTRIBUTE1,
2048 l_artv_rec.ATTRIBUTE2,
2049 l_artv_rec.ATTRIBUTE3,
2050 l_artv_rec.ATTRIBUTE4,
2051 l_artv_rec.ATTRIBUTE5,
2052 l_artv_rec.ATTRIBUTE6,
2053 l_artv_rec.ATTRIBUTE7,
2054 l_artv_rec.ATTRIBUTE8,
2055 l_artv_rec.ATTRIBUTE9,
2056 l_artv_rec.ATTRIBUTE10,
2057 l_artv_rec.ATTRIBUTE11,
2058 l_artv_rec.ATTRIBUTE12,
2059 l_artv_rec.ATTRIBUTE13,
2060 l_artv_rec.ATTRIBUTE14,
2061 l_artv_rec.ATTRIBUTE15,
2062 l_artv_rec.ORG_ID,
2063 l_artv_rec.REQUEST_ID,
2064 l_artv_rec.PROGRAM_APPLICATION_ID,
2065 l_artv_rec.PROGRAM_ID,
2066 l_artv_rec.PROGRAM_UPDATE_DATE,
2067 l_artv_rec.CREATED_BY,
2068 l_artv_rec.CREATION_DATE,
2069 l_artv_rec.LAST_UPDATED_BY,
2070 l_artv_rec.LAST_UPDATE_DATE,
2071 l_artv_rec.LAST_UPDATE_LOGIN,
2072 l_artv_rec.FLOOR_PRICE,
2073 l_artv_rec.NEW_ITEM_PRICE,
2074 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
2075 l_artv_rec.NEW_ITEM_NUMBER,
2076 l_artv_rec.ASSET_RELOCATED_YN,
2077 l_artv_rec.NEW_ITEM_DESCRIPTION,
2078 l_artv_rec.REPURCHASE_AGMT_YN,
2079 l_artv_rec.LIKE_KIND_YN,
2080 -- RABHUPAT - 2667636 - Start
2081 l_artv_rec.CURRENCY_CODE,
2082 l_artv_rec.CURRENCY_CONVERSION_CODE,
2083 l_artv_rec.CURRENCY_CONVERSION_TYPE,
2084 l_artv_rec.CURRENCY_CONVERSION_RATE,
2085 l_artv_rec.CURRENCY_CONVERSION_DATE,
2086 -- RABHUPAT - 2667636 - End
2087 -- RRAVIKIR Legal Entity Changes
2088 l_artv_rec.LEGAL_ENTITY_ID,
2089 -- Legal Entity Changes End
2090 -- DJANASWA Loan Repossession proj start
2091 l_artv_rec.ASSET_FMV_AMOUNT;
2092 -- Loan Repossession proj end
2093 x_no_data_found := okl_artv_pk_csr%NOTFOUND;
2094 CLOSE okl_artv_pk_csr;
2095 RETURN(l_artv_rec);
2096 END get_rec;
2097
2098 FUNCTION get_rec (
2099 p_artv_rec IN artv_rec_type
2100 ) RETURN artv_rec_type IS
2101 l_row_notfound BOOLEAN := TRUE;
2102 BEGIN
2103 RETURN(get_rec(p_artv_rec, l_row_notfound));
2104 END get_rec;
2105
2106 ---------------------------------------------------------
2107 -- FUNCTION null_out_defaults for: OKL_ASSET_RETURNS_V --
2108 ---------------------------------------------------------
2109 -- Start of comments
2110 --
2111 -- Function Name : null_out_defaults
2112 -- Description : for: OKL_ASSET_RETURNS_V
2113 -- Business Rules :
2114 -- Parameters :
2115 -- Version : 1.0
2116 -- History : RABHUPAT 16-DEC-2002 2667636
2117 -- :modified for multicurrency changes
2118 -- End of comments
2119 FUNCTION null_out_defaults (
2120 p_artv_rec IN artv_rec_type
2121 ) RETURN artv_rec_type IS
2122 l_artv_rec artv_rec_type := p_artv_rec;
2123 BEGIN
2124 IF (l_artv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
2125 l_artv_rec.object_version_number := NULL;
2126 END IF;
2127 IF (l_artv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
2128 l_artv_rec.sfwt_flag := NULL;
2129 END IF;
2130 IF (l_artv_rec.rmr_id = OKC_API.G_MISS_NUM) THEN
2131 l_artv_rec.rmr_id := NULL;
2132 END IF;
2133 IF (l_artv_rec.imr_id = OKC_API.G_MISS_NUM) THEN
2134 l_artv_rec.imr_id := NULL;
2135 END IF;
2136 IF (l_artv_rec.rna_id = OKC_API.G_MISS_NUM) THEN
2137 l_artv_rec.rna_id := NULL;
2138 END IF;
2139 IF (l_artv_rec.kle_id = OKC_API.G_MISS_NUM) THEN
2140 l_artv_rec.kle_id := NULL;
2141 END IF;
2142 IF (l_artv_rec.iso_id = OKC_API.G_MISS_NUM) THEN
2143 l_artv_rec.iso_id := NULL;
2144 END IF;
2145 IF (l_artv_rec.security_dep_trx_ap_id = OKC_API.G_MISS_NUM) THEN
2146 l_artv_rec.security_dep_trx_ap_id := NULL;
2147 END IF;
2148 IF (l_artv_rec.ars_code = OKC_API.G_MISS_CHAR) THEN
2149 l_artv_rec.ars_code := NULL;
2150 END IF;
2151 IF (l_artv_rec.art1_code = OKC_API.G_MISS_CHAR) THEN
2152 l_artv_rec.art1_code := NULL;
2153 END IF;
2154 IF (l_artv_rec.date_returned = OKC_API.G_MISS_DATE) THEN
2155 l_artv_rec.date_returned := NULL;
2156 END IF;
2157 IF (l_artv_rec.date_title_returned = OKC_API.G_MISS_DATE) THEN
2158 l_artv_rec.date_title_returned := NULL;
2159 END IF;
2160 IF (l_artv_rec.date_return_due = OKC_API.G_MISS_DATE) THEN
2161 l_artv_rec.date_return_due := NULL;
2162 END IF;
2163 IF (l_artv_rec.date_return_notified = OKC_API.G_MISS_DATE) THEN
2164 l_artv_rec.date_return_notified := NULL;
2165 END IF;
2166 IF (l_artv_rec.relocate_asset_yn = OKC_API.G_MISS_CHAR) THEN
2167 l_artv_rec.relocate_asset_yn := NULL;
2168 END IF;
2169 IF (l_artv_rec.voluntary_yn = OKC_API.G_MISS_CHAR) THEN
2170 l_artv_rec.voluntary_yn := NULL;
2171 END IF;
2172 IF (l_artv_rec.date_repossession_required = OKC_API.G_MISS_DATE) THEN
2173 l_artv_rec.date_repossession_required := NULL;
2174 END IF;
2175 IF (l_artv_rec.date_repossession_actual = OKC_API.G_MISS_DATE) THEN
2176 l_artv_rec.date_repossession_actual := NULL;
2177 END IF;
2178 IF (l_artv_rec.date_hold_until = OKC_API.G_MISS_DATE) THEN
2179 l_artv_rec.date_hold_until := NULL;
2180 END IF;
2181 IF (l_artv_rec.commmercially_reas_sale_yn = OKC_API.G_MISS_CHAR) THEN
2182 l_artv_rec.commmercially_reas_sale_yn := NULL;
2183 END IF;
2184 IF (l_artv_rec.comments = OKC_API.G_MISS_CHAR) THEN
2185 l_artv_rec.comments := NULL;
2186 END IF;
2187 IF (l_artv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
2188 l_artv_rec.attribute_category := NULL;
2189 END IF;
2190 IF (l_artv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
2191 l_artv_rec.attribute1 := NULL;
2192 END IF;
2193 IF (l_artv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
2194 l_artv_rec.attribute2 := NULL;
2195 END IF;
2196 IF (l_artv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
2197 l_artv_rec.attribute3 := NULL;
2198 END IF;
2199 IF (l_artv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
2200 l_artv_rec.attribute4 := NULL;
2201 END IF;
2202 IF (l_artv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
2203 l_artv_rec.attribute5 := NULL;
2204 END IF;
2205 IF (l_artv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
2206 l_artv_rec.attribute6 := NULL;
2207 END IF;
2208 IF (l_artv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
2209 l_artv_rec.attribute7 := NULL;
2210 END IF;
2211 IF (l_artv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
2212 l_artv_rec.attribute8 := NULL;
2213 END IF;
2214 IF (l_artv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
2215 l_artv_rec.attribute9 := NULL;
2216 END IF;
2217 IF (l_artv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
2218 l_artv_rec.attribute10 := NULL;
2219 END IF;
2220 IF (l_artv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
2221 l_artv_rec.attribute11 := NULL;
2222 END IF;
2223 IF (l_artv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
2224 l_artv_rec.attribute12 := NULL;
2225 END IF;
2226 IF (l_artv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
2227 l_artv_rec.attribute13 := NULL;
2228 END IF;
2229 IF (l_artv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
2230 l_artv_rec.attribute14 := NULL;
2231 END IF;
2232 IF (l_artv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
2233 l_artv_rec.attribute15 := NULL;
2234 END IF;
2235 IF (l_artv_rec.org_id = OKC_API.G_MISS_NUM) THEN
2236 l_artv_rec.org_id := NULL;
2237 END IF;
2238 IF (l_artv_rec.created_by = OKC_API.G_MISS_NUM) THEN
2239 l_artv_rec.created_by := NULL;
2240 END IF;
2241 IF (l_artv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
2242 l_artv_rec.creation_date := NULL;
2243 END IF;
2244 IF (l_artv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
2245 l_artv_rec.last_updated_by := NULL;
2246 END IF;
2247 IF (l_artv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
2248 l_artv_rec.last_update_date := NULL;
2249 END IF;
2250 IF (l_artv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
2251 l_artv_rec.last_update_login := NULL;
2252 END IF;
2253
2254 IF (l_artv_rec.floor_price = OKC_API.G_MISS_NUM) THEN
2255 l_artv_rec.floor_price := NULL;
2256 END IF;
2257 IF (l_artv_rec.new_item_price = OKC_API.G_MISS_NUM) THEN
2258 l_artv_rec.new_item_price := NULL;
2259 END IF;
2260 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
2261 IF (l_artv_rec.new_item_number = OKC_API.G_MISS_CHAR) THEN
2262 l_artv_rec.new_item_number := NULL;
2263 END IF;
2264
2265
2266 IF (l_artv_rec.asset_relocated_yn = OKC_API.G_MISS_CHAR) THEN
2267 l_artv_rec.asset_relocated_yn := NULL;
2268 END IF;
2269 IF (l_artv_rec.new_item_description = OKC_API.G_MISS_CHAR) THEN
2270 l_artv_rec.new_item_description := NULL;
2271 END IF;
2272 IF (l_artv_rec.repurchase_agmt_yn = OKC_API.G_MISS_CHAR) THEN
2273 l_artv_rec.repurchase_agmt_yn := NULL;
2274 END IF;
2275 IF (l_artv_rec.like_kind_yn = OKC_API.G_MISS_CHAR) THEN
2276 l_artv_rec.like_kind_yn := NULL;
2277 END IF;
2278 -- RABHUPAT - 2667636 -Start
2279 IF (l_artv_rec.currency_code = OKC_API.G_MISS_CHAR) THEN
2280 l_artv_rec.currency_code := NULL;
2281 END IF;
2282 IF (l_artv_rec.currency_conversion_code = OKC_API.G_MISS_CHAR) THEN
2283 l_artv_rec.currency_conversion_code := NULL;
2284 END IF;
2285 IF (l_artv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR) THEN
2286 l_artv_rec.currency_conversion_type := NULL;
2287 END IF;
2288 IF (l_artv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM) THEN
2289 l_artv_rec.currency_conversion_rate := NULL;
2290 END IF;
2291 IF (l_artv_rec.currency_conversion_date = OKC_API.G_MISS_DATE) THEN
2292 l_artv_rec.currency_conversion_date := NULL;
2293 END IF;
2294 -- RABHUPAT - 2667636 -End
2295 -- RRAVIKIR Legal Entity Changes
2296 IF (l_artv_rec.legal_entity_id = OKC_API.G_MISS_NUM) THEN
2297 l_artv_rec.legal_entity_id := NULL;
2298 END IF;
2299 -- Legal Entity Changes End
2300 -- DJANASWA Loan Repossession proj start
2301 IF (l_artv_rec.ASSET_FMV_AMOUNT= OKC_API.G_MISS_NUM) THEN
2302 l_artv_rec.ASSET_FMV_AMOUNT:= NULL;
2303 END IF;
2304 -- Loan Repossession proj end
2305 RETURN(l_artv_rec);
2306 END null_out_defaults;
2307 ---------------------------------------------------------------------------
2308 -- PROCEDURE Validate_Attributes
2309 ---------------------------------------------------------------------------
2310 -------------------------------------------------
2311 -- Validate_Attributes for:OKL_ASSET_RETURNS_V --
2312 -------------------------------------------------
2313 -- Start of comments
2314 --
2315 -- Procedure Name : validate_attributes
2316 -- Description : for:OKL_ASSET_RETURNS_V
2317 -- Business Rules :
2318 -- Parameters :
2319 -- Version : 1.0
2320 -- History : RABHUPAT 16-DEC-2002 2667636
2321 -- : modified for multicurrency changes
2322 -- End of comments
2323 FUNCTION Validate_Attributes (
2324 p_artv_rec IN artv_rec_type
2325 ) RETURN VARCHAR2 IS
2326 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2327 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2328 BEGIN
2329 -- call each column-level validation
2330 validate_id(x_return_status => l_return_status,
2331 p_artv_rec => p_artv_rec);
2332
2333 -- store the highest degree of error
2334 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2335 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2336 x_return_status := l_return_status;
2337 end if;
2338 end if;
2339
2340 validate_object_version_number(x_return_status => l_return_status,
2341 p_artv_rec => p_artv_rec);
2342
2343 -- store the highest degree of error
2344 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2345 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2346 x_return_status := l_return_status;
2347 end if;
2348 end if;
2349
2350 validate_sfwt_flag(x_return_status => l_return_status,
2351 p_artv_rec => p_artv_rec);
2352
2353 -- store the highest degree of error
2354 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2355 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2356 x_return_status := l_return_status;
2357 end if;
2358 end if;
2359
2360
2361
2362 validate_kle_id(x_return_status => l_return_status,
2363 p_artv_rec => p_artv_rec);
2364
2365 -- store the highest degree of error
2366 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2367 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2368 x_return_status := l_return_status;
2369 end if;
2370 end if;
2371
2372 validate_ars_code(x_return_status => l_return_status,
2373 p_artv_rec => p_artv_rec);
2374
2375 -- store the highest degree of error
2376 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2377 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2378 x_return_status := l_return_status;
2379 end if;
2380 end if;
2381
2382 validate_art1_code(x_return_status => l_return_status,
2383 p_artv_rec => p_artv_rec);
2384
2385 -- store the highest degree of error
2386 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2387 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2388 x_return_status := l_return_status;
2389 end if;
2390 end if;
2391
2392 validate_relocate_asset_yn(x_return_status => l_return_status,
2393 p_artv_rec => p_artv_rec);
2394
2395 -- store the highest degree of error
2396 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2397 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2398 x_return_status := l_return_status;
2399 end if;
2400 end if;
2401
2402 validate_org_id(x_return_status => l_return_status,
2403 p_artv_rec => p_artv_rec);
2404
2405 -- store the highest degree of error
2406 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2407 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2408 x_return_status := l_return_status;
2409 end if;
2410 end if;
2411
2412 validate_sec_dep_trx_ap_id(x_return_status => l_return_status,
2413 p_artv_rec => p_artv_rec);
2414
2415 -- store the highest degree of error
2416 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2417 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2418 x_return_status := l_return_status;
2419 end if;
2420 end if;
2421
2422 validate_iso_id(x_return_status => l_return_status,
2423 p_artv_rec => p_artv_rec);
2424
2425 -- store the highest degree of error
2426 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2427 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2428 x_return_status := l_return_status;
2429 end if;
2430 end if;
2431
2432 validate_rna_id(x_return_status => l_return_status,
2433 p_artv_rec => p_artv_rec);
2434
2435 -- store the highest degree of error
2436 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2437 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2438 x_return_status := l_return_status;
2439 end if;
2440 end if;
2441
2442 validate_rmr_id(x_return_status => l_return_status,
2443 p_artv_rec => p_artv_rec);
2444
2445 -- store the highest degree of error
2446 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2447 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2448 x_return_status := l_return_status;
2449 end if;
2450 end if;
2451
2452 validate_imr_id(x_return_status => l_return_status,
2453 p_artv_rec => p_artv_rec);
2454
2455 -- store the highest degree of error
2456 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2457 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2458 x_return_status := l_return_status;
2459 end if;
2460 end if;
2461
2462 validate_asset_relocated_yn(x_return_status => l_return_status,
2463 p_artv_rec => p_artv_rec);
2464
2465 -- store the highest degree of error
2466 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2467 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2468 x_return_status := l_return_status;
2469 end if;
2470 end if;
2471
2472
2473 validate_commmercially_reas_yn(x_return_status => l_return_status,
2474 p_artv_rec => p_artv_rec);
2475
2476 -- store the highest degree of error
2477 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2478 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2479 x_return_status := l_return_status;
2480 end if;
2481 end if;
2482
2483 validate_voluntary_yn(x_return_status => l_return_status,
2484 p_artv_rec => p_artv_rec);
2485
2486 -- store the highest degree of error
2487 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2488 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2489 x_return_status := l_return_status;
2490 end if;
2491 end if;
2492
2493 validate_repurchase_agmt_yn(x_return_status => l_return_status,
2494 p_artv_rec => p_artv_rec);
2495
2496 -- store the highest degree of error
2497 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2498 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2499 x_return_status := l_return_status;
2500 end if;
2501 end if;
2502
2503 validate_like_kind_yn(x_return_status => l_return_status,
2504 p_artv_rec => p_artv_rec);
2505
2506 -- store the highest degree of error
2507 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2508 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2509 x_return_status := l_return_status;
2510 end if;
2511 end if;
2512
2513 -- RABHUPAT - 2667636 - Start
2514 validate_currency_code(p_artv_rec => p_artv_rec,
2515 x_return_status => l_return_status);
2516 -- store the highest degree of error
2517 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2518 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2519 x_return_status := l_return_status;
2520 END IF;
2521 END IF;
2522 validate_currency_con_code(p_artv_rec => p_artv_rec,
2523 x_return_status => l_return_status);
2524 -- store the highest degree of error
2525 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2526 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2527 x_return_status := l_return_status;
2528 END IF;
2529 END IF;
2530 validate_currency_con_type(p_artv_rec => p_artv_rec,
2531 x_return_status => l_return_status);
2532 -- store the highest degree of error
2533 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2534 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2535 x_return_status := l_return_status;
2536 END IF;
2537 END IF;
2538 -- RABHUPAT - 2667636 - End
2539
2540 -- RRAVIKIR Legal Entity validation
2541 validate_legal_entity_id(x_return_status => l_return_status,
2542 p_artv_rec => p_artv_rec);
2543
2544 -- store the highest degree of error
2545 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2546 if (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) then
2547 x_return_status := l_return_status;
2548 end if;
2549 end if;
2550 -- Legal Entity validation End
2551
2552 RETURN(x_return_status);
2553 END Validate_Attributes;
2554
2555 ---------------------------------------------------------------------------
2556 -- PROCEDURE Validate_Record
2557 ---------------------------------------------------------------------------
2558 ---------------------------------------------
2559 -- Validate_Record for:OKL_ASSET_RETURNS_V --
2560 ---------------------------------------------
2561 -- Start of comments
2562 --
2563 -- Function Name : Validate_Record
2564 -- Description : for:OKL_ASSET_RETURNS_V
2565 -- Business Rules :
2566 -- Parameters :
2567 -- Version : 1.0
2568 -- History : RABHUPAT 16-DEC-2002 2667636
2569 -- :modified for multicurrency changes
2570 -- End of comments
2571 FUNCTION Validate_Record (
2572 p_artv_rec IN artv_rec_type
2573 ) RETURN VARCHAR2 IS
2574 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2575 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2576 BEGIN
2577
2578 -- check uniqueness
2579 is_unique(p_artv_rec,l_return_status);
2580
2581 -- store the highest degree of error
2582 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2583 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2584 x_return_status := l_return_status;
2585 END IF;
2586 END IF;
2587
2588 -- RABHUPAT - 2667636 - Start
2589 -- Validate Currency conversion Code,type,rate and Date
2590
2591 validate_currency_record(p_artv_rec => p_artv_rec,
2592 x_return_status => l_return_status);
2593 -- store the highest degree of error
2594 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2595 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2596 x_return_status := l_return_status;
2597 END IF;
2598 END IF;
2599 -- RABHUPAT - 2667636 - End
2600 RETURN (x_return_status);
2601 EXCEPTION
2602 WHEN OTHERS THEN
2603 -- store SQL error message on message stack for caller
2604 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
2605 ,p_msg_name => G_UNEXPECTED_ERROR
2606 ,p_token1 => G_SQLCODE_TOKEN
2607 ,p_token1_value => sqlcode
2608 ,p_token2 => G_SQLERRM_TOKEN
2609 ,p_token2_value => sqlerrm);
2610
2611 --notify caller of an UNEXPECTED error
2612 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2613
2614 --return status to caller
2615 RETURN x_return_status;
2616 END Validate_Record;
2617
2618 ---------------------------------------------------------------------------
2619 -- PROCEDURE Migrate
2620 ---------------------------------------------------------------------------
2621 -- Start of comments
2622 --
2623 -- Procedure Name : Migrate
2624 -- Description : from _V to _B
2625 -- Business Rules :
2626 -- Parameters :
2627 -- Version : 1.0
2628 -- History : RABHUPAT 16-DEC-2002 2667636
2629 -- : modified for multicurrency changes
2630 -- End of comments
2631 PROCEDURE migrate (
2632 p_from IN artv_rec_type,
2633 p_to IN OUT NOCOPY art_rec_type
2634 ) IS
2635 BEGIN
2636 p_to.id := p_from.id;
2637 p_to.kle_id := p_from.kle_id;
2638 p_to.security_dep_trx_ap_id := p_from.security_dep_trx_ap_id;
2639 p_to.iso_id := p_from.iso_id;
2640 p_to.rna_id := p_from.rna_id;
2641 p_to.rmr_id := p_from.rmr_id;
2642 p_to.ars_code := p_from.ars_code;
2643 p_to.imr_id := p_from.imr_id;
2644 p_to.art1_code := p_from.art1_code;
2645 p_to.date_return_due := p_from.date_return_due;
2646 p_to.date_return_notified := p_from.date_return_notified;
2647 p_to.relocate_asset_yn := p_from.relocate_asset_yn;
2648 p_to.object_version_number := p_from.object_version_number;
2649 p_to.voluntary_yn := p_from.voluntary_yn;
2650 p_to.commmercially_reas_sale_yn := p_from.commmercially_reas_sale_yn;
2651 p_to.date_repossession_required := p_from.date_repossession_required;
2652 p_to.date_repossession_actual := p_from.date_repossession_actual;
2653 p_to.date_hold_until := p_from.date_hold_until;
2654 p_to.date_returned := p_from.date_returned;
2655 p_to.date_title_returned := p_from.date_title_returned;
2656 p_to.org_id := p_from.org_id;
2657 p_to.request_id := p_from.request_id;
2658 p_to.program_application_id := p_from.program_application_id;
2659 p_to.program_id := p_from.program_id;
2660 p_to.program_update_date := p_from.program_update_date;
2661 p_to.attribute_category := p_from.attribute_category;
2662 p_to.attribute1 := p_from.attribute1;
2663 p_to.attribute2 := p_from.attribute2;
2664 p_to.attribute3 := p_from.attribute3;
2665 p_to.attribute4 := p_from.attribute4;
2666 p_to.attribute5 := p_from.attribute5;
2667 p_to.attribute6 := p_from.attribute6;
2668 p_to.attribute7 := p_from.attribute7;
2669 p_to.attribute8 := p_from.attribute8;
2670 p_to.attribute9 := p_from.attribute9;
2671 p_to.attribute10 := p_from.attribute10;
2672 p_to.attribute11 := p_from.attribute11;
2673 p_to.attribute12 := p_from.attribute12;
2674 p_to.attribute13 := p_from.attribute13;
2675 p_to.attribute14 := p_from.attribute14;
2676 p_to.attribute15 := p_from.attribute15;
2677 p_to.created_by := p_from.created_by;
2678 p_to.creation_date := p_from.creation_date;
2679 p_to.last_updated_by := p_from.last_updated_by;
2680 p_to.last_update_date := p_from.last_update_date;
2681 p_to.last_update_login := p_from.last_update_login;
2682
2683 p_to.floor_price := p_from.floor_price;
2684 p_to.new_item_price := p_from.new_item_price;
2685
2686 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
2687 p_to.new_item_number := p_from.new_item_number;
2688
2689 p_to.asset_relocated_yn := p_from.asset_relocated_yn;
2690 p_to.repurchase_agmt_yn := p_from.repurchase_agmt_yn;
2691 p_to.like_kind_yn := p_from.like_kind_yn;
2692
2693 -- RABHUPAT - 2667636 - Start
2694 p_to.currency_code := p_from.currency_code;
2695 p_to.currency_conversion_code := p_from.currency_conversion_code;
2696 p_to.currency_conversion_type := p_from.currency_conversion_type;
2697 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2698 p_to.currency_conversion_date := p_from.currency_conversion_date;
2699 -- RABHUPAT - 2667636 - End
2700 -- RRAVIKIR Legal Entity Changes
2701 p_to.legal_entity_id := p_from.legal_entity_id;
2702 -- Legal Entity Changes End
2703 -- DJANASWA Loan Repossession proj start
2704 p_to.ASSET_FMV_AMOUNT:= p_from.ASSET_FMV_AMOUNT;
2705 -- Loan Repossession proj end
2706 END migrate;
2707
2708 -- Start of comments
2709 --
2710 -- Procedure Name : Migrate
2711 -- Description : from _B to _V
2712 -- Business Rules :
2713 -- Parameters :
2714 -- Version : 1.0
2715 -- History : RABHUPAT 16-DEC-2002 2667636
2716 -- : modified for multicurrency changes
2717 -- End of comments
2718 PROCEDURE migrate (
2719 p_from IN art_rec_type,
2720 p_to IN OUT NOCOPY artv_rec_type
2721 ) IS
2722 BEGIN
2723 p_to.id := p_from.id;
2724 p_to.kle_id := p_from.kle_id;
2725 p_to.security_dep_trx_ap_id := p_from.security_dep_trx_ap_id;
2726 p_to.iso_id := p_from.iso_id;
2727 p_to.rna_id := p_from.rna_id;
2728 p_to.rmr_id := p_from.rmr_id;
2729 p_to.ars_code := p_from.ars_code;
2730 p_to.imr_id := p_from.imr_id;
2731 p_to.art1_code := p_from.art1_code;
2732 p_to.date_return_due := p_from.date_return_due;
2733 p_to.date_return_notified := p_from.date_return_notified;
2734 p_to.relocate_asset_yn := p_from.relocate_asset_yn;
2735 p_to.object_version_number := p_from.object_version_number;
2736 p_to.voluntary_yn := p_from.voluntary_yn;
2737 p_to.commmercially_reas_sale_yn := p_from.commmercially_reas_sale_yn;
2738 p_to.date_repossession_required := p_from.date_repossession_required;
2739 p_to.date_repossession_actual := p_from.date_repossession_actual;
2740 p_to.date_hold_until := p_from.date_hold_until;
2741 p_to.date_returned := p_from.date_returned;
2742 p_to.date_title_returned := p_from.date_title_returned;
2743 p_to.org_id := p_from.org_id;
2744 p_to.request_id := p_from.request_id;
2745 p_to.program_application_id := p_from.program_application_id;
2746 p_to.program_id := p_from.program_id;
2747 p_to.program_update_date := p_from.program_update_date;
2748 p_to.attribute_category := p_from.attribute_category;
2749 p_to.attribute1 := p_from.attribute1;
2750 p_to.attribute2 := p_from.attribute2;
2751 p_to.attribute3 := p_from.attribute3;
2752 p_to.attribute4 := p_from.attribute4;
2753 p_to.attribute5 := p_from.attribute5;
2754 p_to.attribute6 := p_from.attribute6;
2755 p_to.attribute7 := p_from.attribute7;
2756 p_to.attribute8 := p_from.attribute8;
2757 p_to.attribute9 := p_from.attribute9;
2758 p_to.attribute10 := p_from.attribute10;
2759 p_to.attribute11 := p_from.attribute11;
2760 p_to.attribute12 := p_from.attribute12;
2761 p_to.attribute13 := p_from.attribute13;
2762 p_to.attribute14 := p_from.attribute14;
2763 p_to.attribute15 := p_from.attribute15;
2764 p_to.created_by := p_from.created_by;
2765 p_to.creation_date := p_from.creation_date;
2766 p_to.last_updated_by := p_from.last_updated_by;
2767 p_to.last_update_date := p_from.last_update_date;
2768 p_to.last_update_login := p_from.last_update_login;
2769
2770 p_to.floor_price := p_from.floor_price;
2771 p_to.new_item_price := p_from.new_item_price;
2772
2773 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
2774 p_to.new_item_number := p_from.new_item_number;
2775
2776 p_to.asset_relocated_yn := p_from.asset_relocated_yn;
2777 p_to.repurchase_agmt_yn := p_from.repurchase_agmt_yn;
2778 p_to.like_kind_yn := p_from.like_kind_yn;
2779
2780 -- RABHUPAT - 2667636 - Start
2781 p_to.currency_code := p_from.currency_code;
2782 p_to.currency_conversion_code := p_from.currency_conversion_code;
2783 p_to.currency_conversion_type := p_from.currency_conversion_type;
2784 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
2785 p_to.currency_conversion_date := p_from.currency_conversion_date;
2786 -- RABHUPAT - 2667636 - End
2787 -- RRAVIKIR Legal Entity Changes
2788 p_to.legal_entity_id := p_from.legal_entity_id;
2789 -- Legal Entity Changes End
2790 -- DJANASWA Loan Repossession proj start
2791 p_to.ASSET_FMV_AMOUNT:= p_from.ASSET_FMV_AMOUNT;
2792 -- Loan Repossession proj end
2793 END migrate;
2794 PROCEDURE migrate (
2795 p_from IN artv_rec_type,
2796 p_to IN OUT NOCOPY okl_asset_returns_tl_rec_type
2797 ) IS
2798 BEGIN
2799 p_to.id := p_from.id;
2800 p_to.sfwt_flag := p_from.sfwt_flag;
2801 p_to.comments := p_from.comments;
2802 p_to.created_by := p_from.created_by;
2803 p_to.creation_date := p_from.creation_date;
2804 p_to.last_updated_by := p_from.last_updated_by;
2805 p_to.last_update_date := p_from.last_update_date;
2806 p_to.last_update_login := p_from.last_update_login;
2807
2808 p_to.new_item_description := p_from.new_item_description;
2809 END migrate;
2810 PROCEDURE migrate (
2811 p_from IN okl_asset_returns_tl_rec_type,
2812 p_to IN OUT NOCOPY artv_rec_type
2813 ) IS
2814 BEGIN
2815 p_to.id := p_from.id;
2816 p_to.sfwt_flag := p_from.sfwt_flag;
2817 p_to.comments := p_from.comments;
2818 p_to.created_by := p_from.created_by;
2819 p_to.creation_date := p_from.creation_date;
2820 p_to.last_updated_by := p_from.last_updated_by;
2821 p_to.last_update_date := p_from.last_update_date;
2822 p_to.last_update_login := p_from.last_update_login;
2823
2824 p_to.new_item_description := p_from.new_item_description;
2825 END migrate;
2826
2827 ---------------------------------------------------------------------------
2828 -- PROCEDURE validate_row
2829 ---------------------------------------------------------------------------
2830 ------------------------------------------
2831 -- validate_row for:OKL_ASSET_RETURNS_V --
2832 ------------------------------------------
2833 PROCEDURE validate_row(
2834 p_api_version IN NUMBER,
2835 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2836 x_return_status OUT NOCOPY VARCHAR2,
2837 x_msg_count OUT NOCOPY NUMBER,
2838 x_msg_data OUT NOCOPY VARCHAR2,
2839 p_artv_rec IN artv_rec_type) IS
2840
2841 l_api_version CONSTANT NUMBER := 1;
2842 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2843 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2844 l_artv_rec artv_rec_type := p_artv_rec;
2845 l_art_rec art_rec_type;
2846 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
2847 BEGIN
2848 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2849 G_PKG_NAME,
2850 p_init_msg_list,
2851 l_api_version,
2852 p_api_version,
2853 '_PVT',
2854 x_return_status);
2855 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2856 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2857 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2858 RAISE OKC_API.G_EXCEPTION_ERROR;
2859 END IF;
2860 --- Validate all non-missing attributes (Item Level Validation)
2861 l_return_status := Validate_Attributes(l_artv_rec);
2862 --- If any errors happen abort API
2863 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2864 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2865 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2866 RAISE OKC_API.G_EXCEPTION_ERROR;
2867 END IF;
2868 l_return_status := Validate_Record(l_artv_rec);
2869 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2870 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2871 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2872 RAISE OKC_API.G_EXCEPTION_ERROR;
2873 END IF;
2874 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2875 EXCEPTION
2876 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2877 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2878 (
2879 l_api_name,
2880 G_PKG_NAME,
2881 'OKC_API.G_RET_STS_ERROR',
2882 x_msg_count,
2883 x_msg_data,
2884 '_PVT'
2885 );
2886 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2887 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2888 (
2889 l_api_name,
2890 G_PKG_NAME,
2891 'OKC_API.G_RET_STS_UNEXP_ERROR',
2892 x_msg_count,
2893 x_msg_data,
2894 '_PVT'
2895 );
2896 WHEN OTHERS THEN
2897 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2898 (
2899 l_api_name,
2900 G_PKG_NAME,
2901 'OTHERS',
2902 x_msg_count,
2903 x_msg_data,
2904 '_PVT'
2905 );
2906 END validate_row;
2907 ------------------------------------------
2908 -- PL/SQL TBL validate_row for:ARTV_TBL --
2909 ------------------------------------------
2910 PROCEDURE validate_row(
2911 p_api_version IN NUMBER,
2912 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2913 x_return_status OUT NOCOPY VARCHAR2,
2914 x_msg_count OUT NOCOPY NUMBER,
2915 x_msg_data OUT NOCOPY VARCHAR2,
2916 p_artv_tbl IN artv_tbl_type) IS
2917
2918 l_api_version CONSTANT NUMBER := 1;
2919 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2920 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2921 i NUMBER := 0;
2922
2923 -- Begin Post-Generation Change
2924 -- overall error status
2925 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2926 -- End Post-Generation Change
2927 BEGIN
2928 OKC_API.init_msg_list(p_init_msg_list);
2929 -- Make sure PL/SQL table has records in it before passing
2930 IF (p_artv_tbl.COUNT > 0) THEN
2931 i := p_artv_tbl.FIRST;
2932 LOOP
2933 validate_row (
2934 p_api_version => p_api_version,
2935 p_init_msg_list => OKC_API.G_FALSE,
2936 x_return_status => x_return_status,
2937 x_msg_count => x_msg_count,
2938 x_msg_data => x_msg_data,
2939 p_artv_rec => p_artv_tbl(i));
2940 -- Begin Post-Generation Change
2941 -- store the highest degree of error
2942 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2943 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2944 l_overall_status := x_return_status;
2945 END IF;
2946 END IF;
2947 -- End Post-Generation Change
2948 EXIT WHEN (i = p_artv_tbl.LAST);
2949 i := p_artv_tbl.NEXT(i);
2950 END LOOP;
2951 -- Begin Post-Generation Change
2952 -- return overall status
2953 x_return_status := l_overall_status;
2954 -- End Post-Generation Change
2955 END IF;
2956 EXCEPTION
2957 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2958 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2959 (
2960 l_api_name,
2961 G_PKG_NAME,
2962 'OKC_API.G_RET_STS_ERROR',
2963 x_msg_count,
2964 x_msg_data,
2965 '_PVT'
2966 );
2967 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2968 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2969 (
2970 l_api_name,
2971 G_PKG_NAME,
2972 'OKC_API.G_RET_STS_UNEXP_ERROR',
2973 x_msg_count,
2974 x_msg_data,
2975 '_PVT'
2976 );
2977 WHEN OTHERS THEN
2978 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2979 (
2980 l_api_name,
2981 G_PKG_NAME,
2982 'OTHERS',
2983 x_msg_count,
2984 x_msg_data,
2985 '_PVT'
2986 );
2987 END validate_row;
2988
2989 ---------------------------------------------------------------------------
2990 -- PROCEDURE insert_row
2991 ---------------------------------------------------------------------------
2992 ----------------------------------------
2993 -- insert_row for:OKL_ASSET_RETURNS_B --
2994 ----------------------------------------
2995 -- Start of comments
2996 --
2997 -- Procedure Name : insert_row
2998 -- Description : for OKL_ASSET_RETURNS_B
2999 -- Business Rules :
3000 -- Parameters :
3001 -- Version : 1.0
3002 -- History : RABHUPAT 16-DEC-2002 2667636
3003 -- : modified for multicurrency changes
3004 -- End of comments
3005 PROCEDURE insert_row(
3006 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3007 x_return_status OUT NOCOPY VARCHAR2,
3008 x_msg_count OUT NOCOPY NUMBER,
3009 x_msg_data OUT NOCOPY VARCHAR2,
3010 p_art_rec IN art_rec_type,
3011 x_art_rec OUT NOCOPY art_rec_type) IS
3012
3013 l_api_version CONSTANT NUMBER := 1;
3014 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
3015 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3016 l_art_rec art_rec_type := p_art_rec;
3017 l_def_art_rec art_rec_type;
3018 --------------------------------------------
3019 -- Set_Attributes for:OKL_ASSET_RETURNS_B --
3020 --------------------------------------------
3021 FUNCTION Set_Attributes (
3022 p_art_rec IN art_rec_type,
3023 x_art_rec OUT NOCOPY art_rec_type
3024 ) RETURN VARCHAR2 IS
3025 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3026 BEGIN
3027 x_art_rec := p_art_rec;
3028 RETURN(l_return_status);
3029 END Set_Attributes;
3030 BEGIN
3031 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3032 p_init_msg_list,
3033 '_PVT',
3034 x_return_status);
3035 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3037 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3038 RAISE OKC_API.G_EXCEPTION_ERROR;
3039 END IF;
3040 --- Setting item attributes
3041 l_return_status := Set_Attributes(
3042 p_art_rec, -- IN
3043 l_art_rec); -- OUT
3044 --- If any errors happen abort API
3045 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3046 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3047 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3048 RAISE OKC_API.G_EXCEPTION_ERROR;
3049 END IF;
3050 INSERT INTO OKL_ASSET_RETURNS_B(
3051 id,
3052 kle_id,
3053 security_dep_trx_ap_id,
3054 iso_id,
3055 rna_id,
3056 rmr_id,
3057 ars_code,
3058 imr_id,
3059 art1_code,
3060 date_return_due,
3061 date_return_notified,
3062 relocate_asset_yn,
3063 object_version_number,
3064 voluntary_yn,
3065 commmercially_reas_sale_yn,
3066 date_repossession_required,
3067 date_repossession_actual,
3068 date_hold_until,
3069 date_returned,
3070 date_title_returned,
3071 org_id,
3072 request_id,
3073 program_application_id,
3074 program_id,
3075 program_update_date,
3076 attribute_category,
3077 attribute1,
3078 attribute2,
3079 attribute3,
3080 attribute4,
3081 attribute5,
3082 attribute6,
3083 attribute7,
3084 attribute8,
3085 attribute9,
3086 attribute10,
3087 attribute11,
3088 attribute12,
3089 attribute13,
3090 attribute14,
3091 attribute15,
3092 created_by,
3093 creation_date,
3094 last_updated_by,
3095 last_update_date,
3096 last_update_login,
3097 floor_price,
3098 new_item_price,
3099
3100 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
3101 new_item_number,
3102
3103 asset_relocated_yn,
3104 repurchase_agmt_yn,
3105 like_kind_yn,
3106 -- RABHUPAT - 2667636 - Start
3107 currency_code,
3108 currency_conversion_code,
3109 currency_conversion_type,
3110 currency_conversion_rate,
3111 currency_conversion_date,
3112 -- RABHUPAT - 2667636 - End
3113 -- RRAVIKIR Legal Entity Changes
3114 legal_entity_id,
3115 -- Legal Entity Changes End
3116 -- DJANASWA Loan Repossession proj start
3117 ASSET_FMV_AMOUNT)
3118 -- Loan Repossession proj end
3119 VALUES (
3120 l_art_rec.id,
3121 l_art_rec.kle_id,
3122 l_art_rec.security_dep_trx_ap_id,
3123 l_art_rec.iso_id,
3124 l_art_rec.rna_id,
3125 l_art_rec.rmr_id,
3126 l_art_rec.ars_code,
3127 l_art_rec.imr_id,
3128 l_art_rec.art1_code,
3129 l_art_rec.date_return_due,
3130 l_art_rec.date_return_notified,
3131 l_art_rec.relocate_asset_yn,
3132 l_art_rec.object_version_number,
3133 l_art_rec.voluntary_yn,
3134 l_art_rec.commmercially_reas_sale_yn,
3135 l_art_rec.date_repossession_required,
3136 l_art_rec.date_repossession_actual,
3137 l_art_rec.date_hold_until,
3138 l_art_rec.date_returned,
3139 l_art_rec.date_title_returned,
3140 l_art_rec.org_id,
3141 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
3142 DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
3143 DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
3144 DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
3145 l_art_rec.attribute_category,
3146 l_art_rec.attribute1,
3147 l_art_rec.attribute2,
3148 l_art_rec.attribute3,
3149 l_art_rec.attribute4,
3150 l_art_rec.attribute5,
3151 l_art_rec.attribute6,
3152 l_art_rec.attribute7,
3153 l_art_rec.attribute8,
3154 l_art_rec.attribute9,
3155 l_art_rec.attribute10,
3156 l_art_rec.attribute11,
3157 l_art_rec.attribute12,
3158 l_art_rec.attribute13,
3159 l_art_rec.attribute14,
3160 l_art_rec.attribute15,
3161 l_art_rec.created_by,
3162 l_art_rec.creation_date,
3163 l_art_rec.last_updated_by,
3164 l_art_rec.last_update_date,
3165 l_art_rec.last_update_login,
3166 l_art_rec.floor_price,
3167 l_art_rec.new_item_price,
3168
3169 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
3170 l_art_rec.new_item_number,
3171
3172 l_art_rec.asset_relocated_yn,
3173 l_art_rec.repurchase_agmt_yn,
3174 l_art_rec.like_kind_yn,
3175 -- RABHUPAT - 2667636 - Start
3176 l_art_rec.currency_code,
3177 l_art_rec.currency_conversion_code,
3178 l_art_rec.currency_conversion_type,
3179 l_art_rec.currency_conversion_rate,
3180 l_art_rec.currency_conversion_date,
3181 -- RABHUPAT - 2667636 - End
3182 -- RRAVIKIR Legal Entity Changes
3183 l_art_rec.legal_entity_id,
3184 -- Legal Entity Changes End
3185 -- DJANASWA Loan Repossession proj start
3186 l_art_rec.ASSET_FMV_AMOUNT);
3187 -- Loan Repossession proj end
3188 -- Set OUT values
3189 x_art_rec := l_art_rec;
3190 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3191 EXCEPTION
3192 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3193 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3194 (
3195 l_api_name,
3196 G_PKG_NAME,
3197 'OKC_API.G_RET_STS_ERROR',
3198 x_msg_count,
3199 x_msg_data,
3200 '_PVT'
3201 );
3202 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3203 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3204 (
3205 l_api_name,
3206 G_PKG_NAME,
3207 'OKC_API.G_RET_STS_UNEXP_ERROR',
3208 x_msg_count,
3209 x_msg_data,
3210 '_PVT'
3211 );
3212 WHEN OTHERS THEN
3213 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3214 (
3215 l_api_name,
3216 G_PKG_NAME,
3217 'OTHERS',
3218 x_msg_count,
3219 x_msg_data,
3220 '_PVT'
3221 );
3222 END insert_row;
3223 -----------------------------------------
3224 -- insert_row for:OKL_ASSET_RETURNS_TL --
3225 -----------------------------------------
3226 PROCEDURE insert_row(
3227 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3228 x_return_status OUT NOCOPY VARCHAR2,
3229 x_msg_count OUT NOCOPY NUMBER,
3230 x_msg_data OUT NOCOPY VARCHAR2,
3231 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
3232 x_okl_asset_returns_tl_rec OUT NOCOPY okl_asset_returns_tl_rec_type) IS
3233
3234 l_api_version CONSTANT NUMBER := 1;
3235 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
3236 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3237 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type := p_okl_asset_returns_tl_rec;
3238 ldefoklassetreturnstlrec okl_asset_returns_tl_rec_type;
3239 CURSOR get_languages IS
3240 SELECT *
3241 FROM FND_LANGUAGES
3242 WHERE INSTALLED_FLAG IN ('I', 'B');
3243 ---------------------------------------------
3244 -- Set_Attributes for:OKL_ASSET_RETURNS_TL --
3245 ---------------------------------------------
3246 FUNCTION Set_Attributes (
3247 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
3248 x_okl_asset_returns_tl_rec OUT NOCOPY okl_asset_returns_tl_rec_type
3249 ) RETURN VARCHAR2 IS
3250 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3251 BEGIN
3252 x_okl_asset_returns_tl_rec := p_okl_asset_returns_tl_rec;
3253 x_okl_asset_returns_tl_rec.LANGUAGE := USERENV('LANG');
3254 x_okl_asset_returns_tl_rec.SOURCE_LANG := USERENV('LANG');
3255 RETURN(l_return_status);
3256 END Set_Attributes;
3257 BEGIN
3258 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3259 p_init_msg_list,
3260 '_PVT',
3261 x_return_status);
3262 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3263 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3264 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3265 RAISE OKC_API.G_EXCEPTION_ERROR;
3266 END IF;
3267 --- Setting item attributes
3268 l_return_status := Set_Attributes(
3269 p_okl_asset_returns_tl_rec, -- IN
3270 l_okl_asset_returns_tl_rec); -- OUT
3271 --- If any errors happen abort API
3272 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3273 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3274 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3275 RAISE OKC_API.G_EXCEPTION_ERROR;
3276 END IF;
3277 FOR l_lang_rec IN get_languages LOOP
3278 l_okl_asset_returns_tl_rec.language := l_lang_rec.language_code;
3279 INSERT INTO OKL_ASSET_RETURNS_TL(
3280 id,
3281 language,
3282 source_lang,
3283 sfwt_flag,
3284 comments,
3285 created_by,
3286 creation_date,
3287 last_updated_by,
3288 last_update_date,
3289 last_update_login,
3290 new_item_description)
3291 VALUES (
3292 l_okl_asset_returns_tl_rec.id,
3293 l_okl_asset_returns_tl_rec.language,
3294 l_okl_asset_returns_tl_rec.source_lang,
3295 l_okl_asset_returns_tl_rec.sfwt_flag,
3296 l_okl_asset_returns_tl_rec.comments,
3297 l_okl_asset_returns_tl_rec.created_by,
3298 l_okl_asset_returns_tl_rec.creation_date,
3299 l_okl_asset_returns_tl_rec.last_updated_by,
3300 l_okl_asset_returns_tl_rec.last_update_date,
3301 l_okl_asset_returns_tl_rec.last_update_login,
3302 l_okl_asset_returns_tl_rec.new_item_description);
3303 END LOOP;
3304 -- Set OUT values
3305 x_okl_asset_returns_tl_rec := l_okl_asset_returns_tl_rec;
3306 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3307 EXCEPTION
3308 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3309 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3310 (
3311 l_api_name,
3312 G_PKG_NAME,
3313 'OKC_API.G_RET_STS_ERROR',
3314 x_msg_count,
3315 x_msg_data,
3316 '_PVT'
3317 );
3318 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3319 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3320 (
3321 l_api_name,
3322 G_PKG_NAME,
3323 'OKC_API.G_RET_STS_UNEXP_ERROR',
3324 x_msg_count,
3325 x_msg_data,
3326 '_PVT'
3327 );
3328 WHEN OTHERS THEN
3329 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3330 (
3331 l_api_name,
3332 G_PKG_NAME,
3333 'OTHERS',
3334 x_msg_count,
3335 x_msg_data,
3336 '_PVT'
3337 );
3338 END insert_row;
3339 ----------------------------------------
3340 -- insert_row for:OKL_ASSET_RETURNS_V --
3341 ----------------------------------------
3342 -- Start of comments
3343 --
3344 -- Procedure Name : insert_row
3345 -- Description : for OKL_ASSET_RETURNS_V
3346 -- Business Rules :
3347 -- Parameters :
3348 -- Version : 1.0
3349 -- History : RABHUPAT 16-DEC-2002 2667636
3350 -- : modified for multicurrency changes
3351 -- End of comments
3352 PROCEDURE insert_row(
3353 p_api_version IN NUMBER,
3354 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3355 x_return_status OUT NOCOPY VARCHAR2,
3356 x_msg_count OUT NOCOPY NUMBER,
3357 x_msg_data OUT NOCOPY VARCHAR2,
3358 p_artv_rec IN artv_rec_type,
3359 x_artv_rec OUT NOCOPY artv_rec_type) IS
3360
3361 l_api_version CONSTANT NUMBER := 1;
3362 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3363 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3364 l_artv_rec artv_rec_type;
3365 l_def_artv_rec artv_rec_type;
3366 l_art_rec art_rec_type;
3367 lx_art_rec art_rec_type;
3368 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
3369 lx_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
3370 -------------------------------
3371 -- FUNCTION fill_who_columns --
3372 -------------------------------
3373 FUNCTION fill_who_columns (
3374 p_artv_rec IN artv_rec_type
3375 ) RETURN artv_rec_type IS
3376 l_artv_rec artv_rec_type := p_artv_rec;
3377 BEGIN
3378 l_artv_rec.CREATION_DATE := SYSDATE;
3379 l_artv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3380 l_artv_rec.LAST_UPDATE_DATE := SYSDATE;
3381 l_artv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3382 l_artv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3383 RETURN(l_artv_rec);
3384 END fill_who_columns;
3385 --------------------------------------------
3386 -- Set_Attributes for:OKL_ASSET_RETURNS_V --
3387 --------------------------------------------
3388 FUNCTION Set_Attributes (
3389 p_artv_rec IN artv_rec_type,
3390 x_artv_rec OUT NOCOPY artv_rec_type
3391 ) RETURN VARCHAR2 IS
3392 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3393 BEGIN
3394 x_artv_rec := p_artv_rec;
3395 x_artv_rec.OBJECT_VERSION_NUMBER := 1;
3396 x_artv_rec.SFWT_FLAG := 'N';
3397
3398 -- Default the YN columns if not passed.
3399 IF p_artv_rec.like_kind_yn IS NULL
3400 OR p_artv_rec.like_kind_yn = OKC_API.G_MISS_CHAR THEN
3401 x_artv_rec.like_kind_yn := 'N';
3402 END IF;
3403
3404 IF p_artv_rec.repurchase_agmt_yn IS NULL
3405 OR p_artv_rec.repurchase_agmt_yn = OKC_API.G_MISS_CHAR THEN
3406 x_artv_rec.repurchase_agmt_yn := 'N';
3407 END IF;
3408 IF p_artv_rec.asset_relocated_yn IS NULL
3409 OR p_artv_rec.asset_relocated_yn = OKC_API.G_MISS_CHAR THEN
3410 x_artv_rec.asset_relocated_yn := 'N';
3411 END IF;
3412 IF p_artv_rec.commmercially_reas_sale_yn IS NULL
3413 OR p_artv_rec.commmercially_reas_sale_yn = OKC_API.G_MISS_CHAR THEN
3414 x_artv_rec.commmercially_reas_sale_yn := 'N';
3415 END IF;
3416 IF p_artv_rec.voluntary_yn IS NULL
3417 OR p_artv_rec.voluntary_yn = OKC_API.G_MISS_CHAR THEN
3418 x_artv_rec.voluntary_yn := 'N';
3419 END IF;
3420 IF p_artv_rec.relocate_asset_yn IS NULL
3421 OR p_artv_rec.relocate_asset_yn = OKC_API.G_MISS_CHAR THEN
3422 x_artv_rec.relocate_asset_yn := 'N';
3423 END IF;
3424
3425 -- Default the ORG ID if a value is not passed
3426 IF p_artv_rec.org_id IS NULL
3427 OR p_artv_rec.org_id = OKC_API.G_MISS_NUM THEN
3428 x_artv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
3429 END IF;
3430
3431 -- RABHUPAT - 2667636 - Start
3432 x_artv_rec.currency_conversion_code := OKL_AM_UTIL_PVT.get_functional_currency;
3433
3434 IF p_artv_rec.currency_code IS NULL
3435 OR p_artv_rec.currency_code = OKC_API.G_MISS_CHAR THEN
3436 x_artv_rec.currency_code := x_artv_rec.currency_conversion_code;
3437 END IF;
3438 -- RABHUPAT- 2667636 - End
3439
3440 RETURN(l_return_status);
3441 END Set_Attributes;
3442 BEGIN
3443 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3444 G_PKG_NAME,
3445 p_init_msg_list,
3446 l_api_version,
3447 p_api_version,
3448 '_PVT',
3449 x_return_status);
3450 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3451 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3452 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3453 RAISE OKC_API.G_EXCEPTION_ERROR;
3454 END IF;
3455 l_artv_rec := null_out_defaults(p_artv_rec);
3456 -- Set primary key value
3457 l_artv_rec.ID := get_seq_id;
3458 --- Setting item attributes
3459 l_return_status := Set_Attributes(
3460 l_artv_rec, -- IN
3461 l_def_artv_rec); -- OUT
3462 --- If any errors happen abort API
3463 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3464 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3465 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3466 RAISE OKC_API.G_EXCEPTION_ERROR;
3467 END IF;
3468 l_def_artv_rec := fill_who_columns(l_def_artv_rec);
3469 --- Validate all non-missing attributes (Item Level Validation)
3470 l_return_status := Validate_Attributes(l_def_artv_rec);
3471 --- If any errors happen abort API
3472 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3473 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3474 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3475 RAISE OKC_API.G_EXCEPTION_ERROR;
3476 END IF;
3477 l_return_status := Validate_Record(l_def_artv_rec);
3478 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3479 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3480 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3481 RAISE OKC_API.G_EXCEPTION_ERROR;
3482 END IF;
3483 --------------------------------------
3484 -- Move VIEW record to "Child" records
3485 --------------------------------------
3486 migrate(l_def_artv_rec, l_art_rec);
3487 migrate(l_def_artv_rec, l_okl_asset_returns_tl_rec);
3488 --------------------------------------------
3489 -- Call the INSERT_ROW for each child record
3490 --------------------------------------------
3491 insert_row(
3492 p_init_msg_list,
3493 x_return_status,
3494 x_msg_count,
3495 x_msg_data,
3496 l_art_rec,
3497 lx_art_rec
3498 );
3499 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3500 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3501 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3502 RAISE OKC_API.G_EXCEPTION_ERROR;
3503 END IF;
3504 migrate(lx_art_rec, l_def_artv_rec);
3505 insert_row(
3506 p_init_msg_list,
3507 x_return_status,
3508 x_msg_count,
3509 x_msg_data,
3510 l_okl_asset_returns_tl_rec,
3511 lx_okl_asset_returns_tl_rec
3512 );
3513 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3514 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3515 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3516 RAISE OKC_API.G_EXCEPTION_ERROR;
3517 END IF;
3518 migrate(lx_okl_asset_returns_tl_rec, l_def_artv_rec);
3519 -- Set OUT values
3520 x_artv_rec := l_def_artv_rec;
3521 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3522 EXCEPTION
3523 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3524 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3525 (
3526 l_api_name,
3527 G_PKG_NAME,
3528 'OKC_API.G_RET_STS_ERROR',
3529 x_msg_count,
3530 x_msg_data,
3531 '_PVT'
3532 );
3533 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3534 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3535 (
3536 l_api_name,
3537 G_PKG_NAME,
3538 'OKC_API.G_RET_STS_UNEXP_ERROR',
3539 x_msg_count,
3540 x_msg_data,
3541 '_PVT'
3542 );
3543 WHEN OTHERS THEN
3544 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3545 (
3546 l_api_name,
3547 G_PKG_NAME,
3548 'OTHERS',
3549 x_msg_count,
3550 x_msg_data,
3551 '_PVT'
3552 );
3553 END insert_row;
3554 ----------------------------------------
3555 -- PL/SQL TBL insert_row for:ARTV_TBL --
3556 ----------------------------------------
3557 PROCEDURE insert_row(
3558 p_api_version IN NUMBER,
3559 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3560 x_return_status OUT NOCOPY VARCHAR2,
3561 x_msg_count OUT NOCOPY NUMBER,
3562 x_msg_data OUT NOCOPY VARCHAR2,
3563 p_artv_tbl IN artv_tbl_type,
3564 x_artv_tbl OUT NOCOPY artv_tbl_type) IS
3565
3566 l_api_version CONSTANT NUMBER := 1;
3567 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3568 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3569 i NUMBER := 0;
3570 -- Begin Post-Generation Change
3571 -- overall error status
3572 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3573 -- End Post-Generation Change
3574 BEGIN
3575 OKC_API.init_msg_list(p_init_msg_list);
3576 -- Make sure PL/SQL table has records in it before passing
3577 IF (p_artv_tbl.COUNT > 0) THEN
3578 i := p_artv_tbl.FIRST;
3579 LOOP
3580 insert_row (
3581 p_api_version => p_api_version,
3582 p_init_msg_list => OKC_API.G_FALSE,
3583 x_return_status => x_return_status,
3584 x_msg_count => x_msg_count,
3585 x_msg_data => x_msg_data,
3586 p_artv_rec => p_artv_tbl(i),
3587 x_artv_rec => x_artv_tbl(i));
3588 -- Begin Post-Generation Change
3589 -- store the highest degree of error
3590 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3591 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3592 l_overall_status := x_return_status;
3593 END IF;
3594 END IF;
3595 -- End Post-Generation Change
3596 EXIT WHEN (i = p_artv_tbl.LAST);
3597 i := p_artv_tbl.NEXT(i);
3598 END LOOP;
3599 -- Begin Post-Generation Change
3600 -- return overall status
3601 x_return_status := l_overall_status;
3602 -- End Post-Generation Change
3603 END IF;
3604 EXCEPTION
3605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3607 (
3608 l_api_name,
3609 G_PKG_NAME,
3610 'OKC_API.G_RET_STS_ERROR',
3611 x_msg_count,
3612 x_msg_data,
3613 '_PVT'
3614 );
3615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3616 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3617 (
3618 l_api_name,
3619 G_PKG_NAME,
3620 'OKC_API.G_RET_STS_UNEXP_ERROR',
3621 x_msg_count,
3622 x_msg_data,
3623 '_PVT'
3624 );
3625 WHEN OTHERS THEN
3626 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3627 (
3628 l_api_name,
3629 G_PKG_NAME,
3630 'OTHERS',
3631 x_msg_count,
3632 x_msg_data,
3633 '_PVT'
3634 );
3635 END insert_row;
3636
3637 ---------------------------------------------------------------------------
3638 -- PROCEDURE lock_row
3639 ---------------------------------------------------------------------------
3640 --------------------------------------
3641 -- lock_row for:OKL_ASSET_RETURNS_B --
3642 --------------------------------------
3643 PROCEDURE lock_row(
3644 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3645 x_return_status OUT NOCOPY VARCHAR2,
3646 x_msg_count OUT NOCOPY NUMBER,
3647 x_msg_data OUT NOCOPY VARCHAR2,
3648 p_art_rec IN art_rec_type) IS
3649
3650 E_Resource_Busy EXCEPTION;
3651 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3652 CURSOR lock_csr (p_art_rec IN art_rec_type) IS
3653 SELECT OBJECT_VERSION_NUMBER
3654 FROM OKL_ASSET_RETURNS_B
3655 WHERE ID = p_art_rec.id
3656 AND OBJECT_VERSION_NUMBER = p_art_rec.object_version_number
3657 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3658
3659 CURSOR lchk_csr (p_art_rec IN art_rec_type) IS
3660 SELECT OBJECT_VERSION_NUMBER
3661 FROM OKL_ASSET_RETURNS_B
3662 WHERE ID = p_art_rec.id;
3663 l_api_version CONSTANT NUMBER := 1;
3664 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3665 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3666 l_object_version_number OKL_ASSET_RETURNS_B.OBJECT_VERSION_NUMBER%TYPE;
3667 lc_object_version_number OKL_ASSET_RETURNS_B.OBJECT_VERSION_NUMBER%TYPE;
3668 l_row_notfound BOOLEAN := FALSE;
3669 lc_row_notfound BOOLEAN := FALSE;
3670 BEGIN
3671 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3672 p_init_msg_list,
3673 '_PVT',
3674 x_return_status);
3675 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3676 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3677 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3678 RAISE OKC_API.G_EXCEPTION_ERROR;
3679 END IF;
3680 BEGIN
3681 OPEN lock_csr(p_art_rec);
3682 FETCH lock_csr INTO l_object_version_number;
3683 l_row_notfound := lock_csr%NOTFOUND;
3684 CLOSE lock_csr;
3685 EXCEPTION
3686 WHEN E_Resource_Busy THEN
3687 IF (lock_csr%ISOPEN) THEN
3688 CLOSE lock_csr;
3689 END IF;
3690 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3691 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3692 END;
3693
3694 IF ( l_row_notfound ) THEN
3695 OPEN lchk_csr(p_art_rec);
3696 FETCH lchk_csr INTO lc_object_version_number;
3697 lc_row_notfound := lchk_csr%NOTFOUND;
3698 CLOSE lchk_csr;
3699 END IF;
3700 IF (lc_row_notfound) THEN
3701 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3702 RAISE OKC_API.G_EXCEPTION_ERROR;
3703 ELSIF lc_object_version_number > p_art_rec.object_version_number THEN
3704 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3705 RAISE OKC_API.G_EXCEPTION_ERROR;
3706 ELSIF lc_object_version_number <> p_art_rec.object_version_number THEN
3707 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3708 RAISE OKC_API.G_EXCEPTION_ERROR;
3709 ELSIF lc_object_version_number = -1 THEN
3710 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3711 RAISE OKC_API.G_EXCEPTION_ERROR;
3712 END IF;
3713 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3714 EXCEPTION
3715 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3716 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3717 (
3718 l_api_name,
3719 G_PKG_NAME,
3720 'OKC_API.G_RET_STS_ERROR',
3721 x_msg_count,
3722 x_msg_data,
3723 '_PVT'
3724 );
3725 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3726 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3727 (
3728 l_api_name,
3729 G_PKG_NAME,
3730 'OKC_API.G_RET_STS_UNEXP_ERROR',
3731 x_msg_count,
3732 x_msg_data,
3733 '_PVT'
3734 );
3735 WHEN OTHERS THEN
3736 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3737 (
3738 l_api_name,
3739 G_PKG_NAME,
3740 'OTHERS',
3741 x_msg_count,
3742 x_msg_data,
3743 '_PVT'
3744 );
3745 END lock_row;
3746 ---------------------------------------
3747 -- lock_row for:OKL_ASSET_RETURNS_TL --
3748 ---------------------------------------
3749 PROCEDURE lock_row(
3750 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3751 x_return_status OUT NOCOPY VARCHAR2,
3752 x_msg_count OUT NOCOPY NUMBER,
3753 x_msg_data OUT NOCOPY VARCHAR2,
3754 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type) IS
3755
3756 E_Resource_Busy EXCEPTION;
3757 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3758 CURSOR lock_csr (p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type) IS
3759 SELECT *
3760 FROM OKL_ASSET_RETURNS_TL
3761 WHERE ID = p_okl_asset_returns_tl_rec.id
3762 FOR UPDATE NOWAIT;
3763
3764 l_api_version CONSTANT NUMBER := 1;
3765 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3766 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3767 l_lock_var lock_csr%ROWTYPE;
3768 l_row_notfound BOOLEAN := FALSE;
3769 lc_row_notfound BOOLEAN := FALSE;
3770 BEGIN
3771 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3772 p_init_msg_list,
3773 '_PVT',
3774 x_return_status);
3775 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3776 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3777 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3778 RAISE OKC_API.G_EXCEPTION_ERROR;
3779 END IF;
3780 BEGIN
3781 OPEN lock_csr(p_okl_asset_returns_tl_rec);
3782 FETCH lock_csr INTO l_lock_var;
3783 l_row_notfound := lock_csr%NOTFOUND;
3784 CLOSE lock_csr;
3785 EXCEPTION
3786 WHEN E_Resource_Busy THEN
3787 IF (lock_csr%ISOPEN) THEN
3788 CLOSE lock_csr;
3789 END IF;
3790 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3791 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3792 END;
3793
3794 IF ( l_row_notfound ) THEN
3795 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3796 RAISE OKC_API.G_EXCEPTION_ERROR;
3797 END IF;
3798 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3799 EXCEPTION
3800 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3801 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3802 (
3803 l_api_name,
3804 G_PKG_NAME,
3805 'OKC_API.G_RET_STS_ERROR',
3806 x_msg_count,
3807 x_msg_data,
3808 '_PVT'
3809 );
3810 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3811 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3812 (
3813 l_api_name,
3814 G_PKG_NAME,
3815 'OKC_API.G_RET_STS_UNEXP_ERROR',
3816 x_msg_count,
3817 x_msg_data,
3818 '_PVT'
3819 );
3820 WHEN OTHERS THEN
3821 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3822 (
3823 l_api_name,
3824 G_PKG_NAME,
3825 'OTHERS',
3826 x_msg_count,
3827 x_msg_data,
3828 '_PVT'
3829 );
3830 END lock_row;
3831 --------------------------------------
3832 -- lock_row for:OKL_ASSET_RETURNS_V --
3833 --------------------------------------
3834 PROCEDURE lock_row(
3835 p_api_version IN NUMBER,
3836 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3837 x_return_status OUT NOCOPY VARCHAR2,
3838 x_msg_count OUT NOCOPY NUMBER,
3839 x_msg_data OUT NOCOPY VARCHAR2,
3840 p_artv_rec IN artv_rec_type) IS
3841
3842 l_api_version CONSTANT NUMBER := 1;
3843 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3845 l_art_rec art_rec_type;
3846 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
3847 BEGIN
3848 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3849 G_PKG_NAME,
3850 p_init_msg_list,
3851 l_api_version,
3852 p_api_version,
3853 '_PVT',
3854 x_return_status);
3855 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3856 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3857 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3858 RAISE OKC_API.G_EXCEPTION_ERROR;
3859 END IF;
3860 --------------------------------------
3861 -- Move VIEW record to "Child" records
3862 --------------------------------------
3863 migrate(p_artv_rec, l_art_rec);
3864 migrate(p_artv_rec, l_okl_asset_returns_tl_rec);
3865 --------------------------------------------
3866 -- Call the LOCK_ROW for each child record
3867 --------------------------------------------
3868 lock_row(
3869 p_init_msg_list,
3870 x_return_status,
3871 x_msg_count,
3872 x_msg_data,
3873 l_art_rec
3874 );
3875 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3876 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3877 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3878 RAISE OKC_API.G_EXCEPTION_ERROR;
3879 END IF;
3880 lock_row(
3881 p_init_msg_list,
3882 x_return_status,
3883 x_msg_count,
3884 x_msg_data,
3885 l_okl_asset_returns_tl_rec
3886 );
3887 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3888 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3889 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3890 RAISE OKC_API.G_EXCEPTION_ERROR;
3891 END IF;
3892 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3893 EXCEPTION
3894 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3895 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3896 (
3897 l_api_name,
3898 G_PKG_NAME,
3899 'OKC_API.G_RET_STS_ERROR',
3900 x_msg_count,
3901 x_msg_data,
3902 '_PVT'
3903 );
3904 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3905 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3906 (
3907 l_api_name,
3908 G_PKG_NAME,
3909 'OKC_API.G_RET_STS_UNEXP_ERROR',
3910 x_msg_count,
3911 x_msg_data,
3912 '_PVT'
3913 );
3914 WHEN OTHERS THEN
3915 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3916 (
3917 l_api_name,
3918 G_PKG_NAME,
3919 'OTHERS',
3920 x_msg_count,
3921 x_msg_data,
3922 '_PVT'
3923 );
3924 END lock_row;
3925 --------------------------------------
3926 -- PL/SQL TBL lock_row for:ARTV_TBL --
3927 --------------------------------------
3928 PROCEDURE lock_row(
3929 p_api_version IN NUMBER,
3930 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3931 x_return_status OUT NOCOPY VARCHAR2,
3932 x_msg_count OUT NOCOPY NUMBER,
3933 x_msg_data OUT NOCOPY VARCHAR2,
3934 p_artv_tbl IN artv_tbl_type) IS
3935
3936 l_api_version CONSTANT NUMBER := 1;
3937 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3938 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3939 i NUMBER := 0;
3940 -- Begin Post-Generation Change
3941 -- overall error status
3942 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3943 -- End Post-Generation Change
3944 BEGIN
3945 OKC_API.init_msg_list(p_init_msg_list);
3946 -- Make sure PL/SQL table has records in it before passing
3947 IF (p_artv_tbl.COUNT > 0) THEN
3948 i := p_artv_tbl.FIRST;
3949 LOOP
3950 lock_row (
3951 p_api_version => p_api_version,
3952 p_init_msg_list => OKC_API.G_FALSE,
3953 x_return_status => x_return_status,
3954 x_msg_count => x_msg_count,
3955 x_msg_data => x_msg_data,
3956 p_artv_rec => p_artv_tbl(i));
3957 -- Begin Post-Generation Change
3958 -- store the highest degree of error
3959 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3960 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3961 l_overall_status := x_return_status;
3962 END IF;
3963 END IF;
3964 -- End Post-Generation Change
3965 EXIT WHEN (i = p_artv_tbl.LAST);
3966 i := p_artv_tbl.NEXT(i);
3967 END LOOP;
3968 -- Begin Post-Generation Change
3969 -- return overall status
3970 x_return_status := l_overall_status;
3971 -- End Post-Generation Change
3972 END IF;
3973 EXCEPTION
3974 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3975 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3976 (
3977 l_api_name,
3978 G_PKG_NAME,
3979 'OKC_API.G_RET_STS_ERROR',
3980 x_msg_count,
3981 x_msg_data,
3982 '_PVT'
3983 );
3984 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3985 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3986 (
3987 l_api_name,
3988 G_PKG_NAME,
3989 'OKC_API.G_RET_STS_UNEXP_ERROR',
3990 x_msg_count,
3991 x_msg_data,
3992 '_PVT'
3993 );
3994 WHEN OTHERS THEN
3995 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3996 (
3997 l_api_name,
3998 G_PKG_NAME,
3999 'OTHERS',
4000 x_msg_count,
4001 x_msg_data,
4002 '_PVT'
4003 );
4004 END lock_row;
4005
4006 ---------------------------------------------------------------------------
4007 -- PROCEDURE update_row
4008 ---------------------------------------------------------------------------
4009 ----------------------------------------
4010 -- update_row for:OKL_ASSET_RETURNS_B --
4011 ----------------------------------------
4012 -- Start of comments
4013 --
4014 -- Procedure Name : update_row
4015 -- Description : for OKL_ASSET_RETURNS_B
4016 -- Business Rules :
4017 -- Parameters :
4018 -- Version : 1.0
4019 -- History : RABHUPAT 16-DEC-2002 2667636
4020 -- : modified for multicurrency changes
4021 -- End of comments
4022 PROCEDURE update_row(
4023 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4024 x_return_status OUT NOCOPY VARCHAR2,
4025 x_msg_count OUT NOCOPY NUMBER,
4026 x_msg_data OUT NOCOPY VARCHAR2,
4027 p_art_rec IN art_rec_type,
4028 x_art_rec OUT NOCOPY art_rec_type) IS
4029
4030 l_api_version CONSTANT NUMBER := 1;
4031 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
4032 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4033 l_art_rec art_rec_type := p_art_rec;
4034 l_def_art_rec art_rec_type;
4035 l_row_notfound BOOLEAN := TRUE;
4036 ----------------------------------
4037 -- FUNCTION populate_new_record --
4038 ----------------------------------
4039 FUNCTION populate_new_record (
4040 p_art_rec IN art_rec_type,
4041 x_art_rec OUT NOCOPY art_rec_type
4042 ) RETURN VARCHAR2 IS
4043 l_art_rec art_rec_type;
4044 l_row_notfound BOOLEAN := TRUE;
4045 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4046 BEGIN
4047 x_art_rec := p_art_rec;
4048 -- Get current database values
4049 l_art_rec := get_rec(p_art_rec, l_row_notfound);
4050 IF (l_row_notfound) THEN
4051 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4052 END IF;
4053 IF (x_art_rec.id = OKC_API.G_MISS_NUM)
4054 THEN
4055 x_art_rec.id := l_art_rec.id;
4056 END IF;
4057 IF (x_art_rec.kle_id = OKC_API.G_MISS_NUM)
4058 THEN
4059 x_art_rec.kle_id := l_art_rec.kle_id;
4060 END IF;
4061 IF (x_art_rec.security_dep_trx_ap_id = OKC_API.G_MISS_NUM)
4062 THEN
4063 x_art_rec.security_dep_trx_ap_id := l_art_rec.security_dep_trx_ap_id;
4064 END IF;
4065 IF (x_art_rec.iso_id = OKC_API.G_MISS_NUM)
4066 THEN
4067 x_art_rec.iso_id := l_art_rec.iso_id;
4068 END IF;
4069 IF (x_art_rec.rna_id = OKC_API.G_MISS_NUM)
4070 THEN
4071 x_art_rec.rna_id := l_art_rec.rna_id;
4072 END IF;
4073 IF (x_art_rec.rmr_id = OKC_API.G_MISS_NUM)
4074 THEN
4075 x_art_rec.rmr_id := l_art_rec.rmr_id;
4076 END IF;
4077 IF (x_art_rec.ars_code = OKC_API.G_MISS_CHAR)
4078 THEN
4079 x_art_rec.ars_code := l_art_rec.ars_code;
4080 END IF;
4081 IF (x_art_rec.imr_id = OKC_API.G_MISS_NUM)
4082 THEN
4083 x_art_rec.imr_id := l_art_rec.imr_id;
4084 END IF;
4085 IF (x_art_rec.art1_code = OKC_API.G_MISS_CHAR)
4086 THEN
4087 x_art_rec.art1_code := l_art_rec.art1_code;
4088 END IF;
4089 IF (x_art_rec.date_return_due = OKC_API.G_MISS_DATE)
4090 THEN
4091 x_art_rec.date_return_due := l_art_rec.date_return_due;
4092 END IF;
4093 IF (x_art_rec.date_return_notified = OKC_API.G_MISS_DATE)
4094 THEN
4095 x_art_rec.date_return_notified := l_art_rec.date_return_notified;
4096 END IF;
4097 IF (x_art_rec.relocate_asset_yn = OKC_API.G_MISS_CHAR)
4098 THEN
4099 x_art_rec.relocate_asset_yn := l_art_rec.relocate_asset_yn;
4100 END IF;
4101 IF (x_art_rec.object_version_number = OKC_API.G_MISS_NUM)
4102 THEN
4103 x_art_rec.object_version_number := l_art_rec.object_version_number;
4104 END IF;
4105 IF (x_art_rec.voluntary_yn = OKC_API.G_MISS_CHAR)
4106 THEN
4107 x_art_rec.voluntary_yn := l_art_rec.voluntary_yn;
4108 END IF;
4109 IF (x_art_rec.commmercially_reas_sale_yn = OKC_API.G_MISS_CHAR)
4110 THEN
4111 x_art_rec.commmercially_reas_sale_yn := l_art_rec.commmercially_reas_sale_yn;
4112 END IF;
4113 IF (x_art_rec.date_repossession_required = OKC_API.G_MISS_DATE)
4114 THEN
4115 x_art_rec.date_repossession_required := l_art_rec.date_repossession_required;
4116 END IF;
4117 IF (x_art_rec.date_repossession_actual = OKC_API.G_MISS_DATE)
4118 THEN
4119 x_art_rec.date_repossession_actual := l_art_rec.date_repossession_actual;
4120 END IF;
4121 IF (x_art_rec.date_hold_until = OKC_API.G_MISS_DATE)
4122 THEN
4123 x_art_rec.date_hold_until := l_art_rec.date_hold_until;
4124 END IF;
4125 IF (x_art_rec.date_returned = OKC_API.G_MISS_DATE)
4126 THEN
4127 x_art_rec.date_returned := l_art_rec.date_returned;
4128 END IF;
4129 IF (x_art_rec.date_title_returned = OKC_API.G_MISS_DATE)
4130 THEN
4131 x_art_rec.date_title_returned := l_art_rec.date_title_returned;
4132 END IF;
4133 IF (x_art_rec.org_id = OKC_API.G_MISS_NUM)
4134 THEN
4135 x_art_rec.org_id := l_art_rec.org_id;
4136 END IF;
4137 IF (x_art_rec.request_id = OKC_API.G_MISS_NUM)
4138 THEN
4139 x_art_rec.request_id := l_art_rec.request_id;
4140 END IF;
4141 IF (x_art_rec.program_application_id = OKC_API.G_MISS_NUM)
4142 THEN
4143 x_art_rec.program_application_id := l_art_rec.program_application_id;
4144 END IF;
4145 IF (x_art_rec.program_id = OKC_API.G_MISS_NUM)
4146 THEN
4147 x_art_rec.program_id := l_art_rec.program_id;
4148 END IF;
4149 IF (x_art_rec.program_update_date = OKC_API.G_MISS_DATE)
4150 THEN
4151 x_art_rec.program_update_date := l_art_rec.program_update_date;
4152 END IF;
4153 IF (x_art_rec.attribute_category = OKC_API.G_MISS_CHAR)
4154 THEN
4155 x_art_rec.attribute_category := l_art_rec.attribute_category;
4156 END IF;
4157 IF (x_art_rec.attribute1 = OKC_API.G_MISS_CHAR)
4158 THEN
4159 x_art_rec.attribute1 := l_art_rec.attribute1;
4160 END IF;
4161 IF (x_art_rec.attribute2 = OKC_API.G_MISS_CHAR)
4162 THEN
4163 x_art_rec.attribute2 := l_art_rec.attribute2;
4164 END IF;
4165 IF (x_art_rec.attribute3 = OKC_API.G_MISS_CHAR)
4166 THEN
4167 x_art_rec.attribute3 := l_art_rec.attribute3;
4168 END IF;
4169 IF (x_art_rec.attribute4 = OKC_API.G_MISS_CHAR)
4170 THEN
4171 x_art_rec.attribute4 := l_art_rec.attribute4;
4172 END IF;
4173 IF (x_art_rec.attribute5 = OKC_API.G_MISS_CHAR)
4174 THEN
4175 x_art_rec.attribute5 := l_art_rec.attribute5;
4176 END IF;
4177 IF (x_art_rec.attribute6 = OKC_API.G_MISS_CHAR)
4178 THEN
4179 x_art_rec.attribute6 := l_art_rec.attribute6;
4180 END IF;
4181 IF (x_art_rec.attribute7 = OKC_API.G_MISS_CHAR)
4182 THEN
4183 x_art_rec.attribute7 := l_art_rec.attribute7;
4184 END IF;
4185 IF (x_art_rec.attribute8 = OKC_API.G_MISS_CHAR)
4186 THEN
4187 x_art_rec.attribute8 := l_art_rec.attribute8;
4188 END IF;
4189 IF (x_art_rec.attribute9 = OKC_API.G_MISS_CHAR)
4190 THEN
4191 x_art_rec.attribute9 := l_art_rec.attribute9;
4192 END IF;
4193 IF (x_art_rec.attribute10 = OKC_API.G_MISS_CHAR)
4194 THEN
4195 x_art_rec.attribute10 := l_art_rec.attribute10;
4196 END IF;
4197 IF (x_art_rec.attribute11 = OKC_API.G_MISS_CHAR)
4198 THEN
4199 x_art_rec.attribute11 := l_art_rec.attribute11;
4200 END IF;
4201 IF (x_art_rec.attribute12 = OKC_API.G_MISS_CHAR)
4202 THEN
4203 x_art_rec.attribute12 := l_art_rec.attribute12;
4204 END IF;
4205 IF (x_art_rec.attribute13 = OKC_API.G_MISS_CHAR)
4206 THEN
4207 x_art_rec.attribute13 := l_art_rec.attribute13;
4208 END IF;
4209 IF (x_art_rec.attribute14 = OKC_API.G_MISS_CHAR)
4210 THEN
4211 x_art_rec.attribute14 := l_art_rec.attribute14;
4212 END IF;
4213 IF (x_art_rec.attribute15 = OKC_API.G_MISS_CHAR)
4214 THEN
4215 x_art_rec.attribute15 := l_art_rec.attribute15;
4216 END IF;
4217 IF (x_art_rec.created_by = OKC_API.G_MISS_NUM)
4218 THEN
4219 x_art_rec.created_by := l_art_rec.created_by;
4220 END IF;
4221 IF (x_art_rec.creation_date = OKC_API.G_MISS_DATE)
4222 THEN
4223 x_art_rec.creation_date := l_art_rec.creation_date;
4224 END IF;
4225 IF (x_art_rec.last_updated_by = OKC_API.G_MISS_NUM)
4226 THEN
4227 x_art_rec.last_updated_by := l_art_rec.last_updated_by;
4228 END IF;
4229 IF (x_art_rec.last_update_date = OKC_API.G_MISS_DATE)
4230 THEN
4231 x_art_rec.last_update_date := l_art_rec.last_update_date;
4232 END IF;
4233 IF (x_art_rec.last_update_login = OKC_API.G_MISS_NUM)
4234 THEN
4235 x_art_rec.last_update_login := l_art_rec.last_update_login;
4236 END IF;
4237
4238 IF (x_art_rec.floor_price = OKC_API.G_MISS_NUM)
4239 THEN
4240 x_art_rec.floor_price := l_art_rec.floor_price;
4241 END IF;
4242 IF (x_art_rec.new_item_price = OKC_API.G_MISS_NUM)
4243 THEN
4244 x_art_rec.new_item_price := l_art_rec.new_item_price;
4245 END IF;
4246
4247 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
4248 IF (x_art_rec.new_item_number = OKC_API.G_MISS_CHAR)
4249 THEN
4250 x_art_rec.new_item_number := l_art_rec.new_item_number;
4251 END IF;
4252
4253
4254 IF (x_art_rec.asset_relocated_yn = OKC_API.G_MISS_CHAR)
4255 THEN
4256 x_art_rec.asset_relocated_yn := l_art_rec.asset_relocated_yn;
4257 END IF;
4258 IF (x_art_rec.repurchase_agmt_yn = OKC_API.G_MISS_CHAR)
4259 THEN
4260 x_art_rec.repurchase_agmt_yn := l_art_rec.repurchase_agmt_yn;
4261 END IF;
4262 IF (x_art_rec.like_kind_yn = OKC_API.G_MISS_CHAR)
4263 THEN
4264 x_art_rec.like_kind_yn := l_art_rec.like_kind_yn;
4265 END IF;
4266
4267 -- RABHUPAT - 2667636 - Start
4268 IF (x_art_rec.currency_code = OKC_API.G_MISS_CHAR)
4269 THEN
4270 x_art_rec.currency_code := l_art_rec.currency_code;
4271 END IF;
4272 IF (x_art_rec.currency_conversion_code = OKC_API.G_MISS_CHAR)
4273 THEN
4274 x_art_rec.currency_conversion_code := l_art_rec.currency_conversion_code;
4275 END IF;
4276 IF (x_art_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
4277 THEN
4278 x_art_rec.currency_conversion_type := l_art_rec.currency_conversion_type;
4279 END IF;
4280 IF (x_art_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
4281 THEN
4282 x_art_rec.currency_conversion_rate := l_art_rec.currency_conversion_rate;
4283 END IF;
4284 IF (x_art_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
4285 THEN
4286 x_art_rec.currency_conversion_date := l_art_rec.currency_conversion_date;
4287 END IF;
4288 -- RABHUPAT - 2667636 - End
4289 -- RRAVIKIR Legal Entity Changes
4290 IF (x_art_rec.legal_entity_id = OKC_API.G_MISS_NUM)
4291 THEN
4292 x_art_rec.legal_entity_id := l_art_rec.legal_entity_id;
4293 END IF;
4294 -- Legal Entity Changes End
4295 -- DJANASWA Loan Repossession proj start
4296 IF (x_art_rec.ASSET_FMV_AMOUNT = OKC_API.G_MISS_NUM)
4297 THEN
4298 x_art_rec.ASSET_FMV_AMOUNT := l_art_rec.ASSET_FMV_AMOUNT;
4299 END IF;
4300 -- Loan Repossession proj end
4301
4302 RETURN(l_return_status);
4303 END populate_new_record;
4304 --------------------------------------------
4305 -- Set_Attributes for:OKL_ASSET_RETURNS_B --
4306 --------------------------------------------
4307 FUNCTION Set_Attributes (
4308 p_art_rec IN art_rec_type,
4309 x_art_rec OUT NOCOPY art_rec_type
4310 ) RETURN VARCHAR2 IS
4311 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4312 BEGIN
4313 x_art_rec := p_art_rec;
4314 RETURN(l_return_status);
4315 END Set_Attributes;
4316 BEGIN
4317 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4318 p_init_msg_list,
4319 '_PVT',
4320 x_return_status);
4321 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4322 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4323 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4324 RAISE OKC_API.G_EXCEPTION_ERROR;
4325 END IF;
4326 --- Setting item attributes
4327 l_return_status := Set_Attributes(
4328 p_art_rec, -- IN
4329 l_art_rec); -- OUT
4330 --- If any errors happen abort API
4331 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4332 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4333 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4334 RAISE OKC_API.G_EXCEPTION_ERROR;
4335 END IF;
4336 l_return_status := populate_new_record(l_art_rec, l_def_art_rec);
4337 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4338 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4339 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4340 RAISE OKC_API.G_EXCEPTION_ERROR;
4341 END IF;
4342 UPDATE OKL_ASSET_RETURNS_B
4343 SET KLE_ID = l_def_art_rec.kle_id,
4344 SECURITY_DEP_TRX_AP_ID = l_def_art_rec.security_dep_trx_ap_id,
4345 ISO_ID = l_def_art_rec.iso_id,
4346 RNA_ID = l_def_art_rec.rna_id,
4347 RMR_ID = l_def_art_rec.rmr_id,
4348 ARS_CODE = l_def_art_rec.ars_code,
4349 IMR_ID = l_def_art_rec.imr_id,
4350 ART1_CODE = l_def_art_rec.art1_code,
4351 DATE_RETURN_DUE = l_def_art_rec.date_return_due,
4352 DATE_RETURN_NOTIFIED = l_def_art_rec.date_return_notified,
4353 RELOCATE_ASSET_YN = l_def_art_rec.relocate_asset_yn,
4354 OBJECT_VERSION_NUMBER = l_def_art_rec.object_version_number,
4355 VOLUNTARY_YN = l_def_art_rec.voluntary_yn,
4356 COMMMERCIALLY_REAS_SALE_YN = l_def_art_rec.commmercially_reas_sale_yn,
4357 DATE_REPOSSESSION_REQUIRED = l_def_art_rec.date_repossession_required,
4358 DATE_REPOSSESSION_ACTUAL = l_def_art_rec.date_repossession_actual,
4359 DATE_HOLD_UNTIL = l_def_art_rec.date_hold_until,
4360 DATE_RETURNED = l_def_art_rec.date_returned,
4361 DATE_TITLE_RETURNED = l_def_art_rec.date_title_returned,
4362 ORG_ID = l_def_art_rec.org_id,
4363 REQUEST_ID = l_def_art_rec.request_id,
4364 PROGRAM_APPLICATION_ID = l_def_art_rec.program_application_id,
4365 PROGRAM_ID = l_def_art_rec.program_id,
4366 PROGRAM_UPDATE_DATE = l_def_art_rec.program_update_date,
4367 ATTRIBUTE_CATEGORY = l_def_art_rec.attribute_category,
4368 ATTRIBUTE1 = l_def_art_rec.attribute1,
4369 ATTRIBUTE2 = l_def_art_rec.attribute2,
4370 ATTRIBUTE3 = l_def_art_rec.attribute3,
4371 ATTRIBUTE4 = l_def_art_rec.attribute4,
4372 ATTRIBUTE5 = l_def_art_rec.attribute5,
4373 ATTRIBUTE6 = l_def_art_rec.attribute6,
4374 ATTRIBUTE7 = l_def_art_rec.attribute7,
4375 ATTRIBUTE8 = l_def_art_rec.attribute8,
4376 ATTRIBUTE9 = l_def_art_rec.attribute9,
4377 ATTRIBUTE10 = l_def_art_rec.attribute10,
4378 ATTRIBUTE11 = l_def_art_rec.attribute11,
4379 ATTRIBUTE12 = l_def_art_rec.attribute12,
4380 ATTRIBUTE13 = l_def_art_rec.attribute13,
4381 ATTRIBUTE14 = l_def_art_rec.attribute14,
4382 ATTRIBUTE15 = l_def_art_rec.attribute15,
4383 CREATED_BY = l_def_art_rec.created_by,
4384 CREATION_DATE = l_def_art_rec.creation_date,
4385 LAST_UPDATED_BY = l_def_art_rec.last_updated_by,
4386 LAST_UPDATE_DATE = l_def_art_rec.last_update_date,
4387 LAST_UPDATE_LOGIN = l_def_art_rec.last_update_login,
4388 FLOOR_PRICE = l_def_art_rec.floor_price,
4389 NEW_ITEM_PRICE = l_def_art_rec.new_item_price,
4390
4391 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
4392 NEW_ITEM_NUMBER = l_def_art_rec.new_item_number,
4393
4394 ASSET_RELOCATED_YN = l_def_art_rec.asset_relocated_yn,
4395 REPURCHASE_AGMT_YN = l_def_art_rec.repurchase_agmt_yn,
4396 LIKE_KIND_YN = l_def_art_rec.like_kind_yn,
4397 -- RABHUPAT - 2667636 - Start
4398 CURRENCY_CODE = l_def_art_rec.currency_code,
4399 CURRENCY_CONVERSION_CODE = l_def_art_rec.currency_conversion_code,
4400 CURRENCY_CONVERSION_TYPE = l_def_art_rec.currency_conversion_type,
4401 CURRENCY_CONVERSION_RATE = l_def_art_rec.currency_conversion_rate,
4402 CURRENCY_CONVERSION_DATE = l_def_art_rec.currency_conversion_date,
4403 -- RABHUPAT - 2667636 - End
4404 -- RRAVIKIR Legal Entity Changes
4405 LEGAL_ENTITY_ID = l_def_art_rec.legal_entity_id,
4406 -- Legal Entity Changes End
4407 -- DJANASWA Loan Repossession proj start
4408 ASSET_FMV_AMOUNT = l_def_art_rec.ASSET_FMV_AMOUNT
4409 -- Loan Repossession proj end
4410 WHERE ID = l_def_art_rec.id;
4411
4412 x_art_rec := l_def_art_rec;
4413 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4414 EXCEPTION
4415 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4416 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4417 (
4418 l_api_name,
4419 G_PKG_NAME,
4420 'OKC_API.G_RET_STS_ERROR',
4421 x_msg_count,
4422 x_msg_data,
4423 '_PVT'
4424 );
4425 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4426 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4427 (
4428 l_api_name,
4429 G_PKG_NAME,
4430 'OKC_API.G_RET_STS_UNEXP_ERROR',
4431 x_msg_count,
4432 x_msg_data,
4433 '_PVT'
4434 );
4435 WHEN OTHERS THEN
4436 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4437 (
4438 l_api_name,
4439 G_PKG_NAME,
4440 'OTHERS',
4441 x_msg_count,
4442 x_msg_data,
4443 '_PVT'
4444 );
4445 END update_row;
4446 -----------------------------------------
4447 -- update_row for:OKL_ASSET_RETURNS_TL --
4448 -----------------------------------------
4449 PROCEDURE update_row(
4450 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4451 x_return_status OUT NOCOPY VARCHAR2,
4452 x_msg_count OUT NOCOPY NUMBER,
4453 x_msg_data OUT NOCOPY VARCHAR2,
4454 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
4455 x_okl_asset_returns_tl_rec OUT NOCOPY okl_asset_returns_tl_rec_type) IS
4456
4457 l_api_version CONSTANT NUMBER := 1;
4458 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4459 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4460 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type := p_okl_asset_returns_tl_rec;
4461 ldefoklassetreturnstlrec okl_asset_returns_tl_rec_type;
4462 l_row_notfound BOOLEAN := TRUE;
4463 ----------------------------------
4464 -- FUNCTION populate_new_record --
4465 ----------------------------------
4466 FUNCTION populate_new_record (
4467 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
4468 x_okl_asset_returns_tl_rec OUT NOCOPY okl_asset_returns_tl_rec_type
4469 ) RETURN VARCHAR2 IS
4470 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
4471 l_row_notfound BOOLEAN := TRUE;
4472 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4473 BEGIN
4474 x_okl_asset_returns_tl_rec := p_okl_asset_returns_tl_rec;
4475 -- Get current database values
4476 l_okl_asset_returns_tl_rec := get_rec(p_okl_asset_returns_tl_rec, l_row_notfound);
4477 IF (l_row_notfound) THEN
4478 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4479 END IF;
4480 IF (x_okl_asset_returns_tl_rec.id = OKC_API.G_MISS_NUM)
4481 THEN
4482 x_okl_asset_returns_tl_rec.id := l_okl_asset_returns_tl_rec.id;
4483 END IF;
4484 IF (x_okl_asset_returns_tl_rec.language = OKC_API.G_MISS_CHAR)
4485 THEN
4486 x_okl_asset_returns_tl_rec.language := l_okl_asset_returns_tl_rec.language;
4487 END IF;
4488 IF (x_okl_asset_returns_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
4489 THEN
4490 x_okl_asset_returns_tl_rec.source_lang := l_okl_asset_returns_tl_rec.source_lang;
4491 END IF;
4492 IF (x_okl_asset_returns_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4493 THEN
4494 x_okl_asset_returns_tl_rec.sfwt_flag := l_okl_asset_returns_tl_rec.sfwt_flag;
4495 END IF;
4496 IF (x_okl_asset_returns_tl_rec.comments = OKC_API.G_MISS_CHAR)
4497 THEN
4498 x_okl_asset_returns_tl_rec.comments := l_okl_asset_returns_tl_rec.comments;
4499 END IF;
4500 IF (x_okl_asset_returns_tl_rec.created_by = OKC_API.G_MISS_NUM)
4501 THEN
4502 x_okl_asset_returns_tl_rec.created_by := l_okl_asset_returns_tl_rec.created_by;
4503 END IF;
4504 IF (x_okl_asset_returns_tl_rec.creation_date = OKC_API.G_MISS_DATE)
4505 THEN
4506 x_okl_asset_returns_tl_rec.creation_date := l_okl_asset_returns_tl_rec.creation_date;
4507 END IF;
4508 IF (x_okl_asset_returns_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
4509 THEN
4510 x_okl_asset_returns_tl_rec.last_updated_by := l_okl_asset_returns_tl_rec.last_updated_by;
4511 END IF;
4512 IF (x_okl_asset_returns_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
4513 THEN
4514 x_okl_asset_returns_tl_rec.last_update_date := l_okl_asset_returns_tl_rec.last_update_date;
4515 END IF;
4516 IF (x_okl_asset_returns_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
4517 THEN
4518 x_okl_asset_returns_tl_rec.last_update_login := l_okl_asset_returns_tl_rec.last_update_login;
4519 END IF;
4520
4521 IF (x_okl_asset_returns_tl_rec.new_item_description = OKC_API.G_MISS_CHAR)
4522 THEN
4523 x_okl_asset_returns_tl_rec.new_item_description := l_okl_asset_returns_tl_rec.new_item_description;
4524 END IF;
4525 RETURN(l_return_status);
4526 END populate_new_record;
4527 ---------------------------------------------
4528 -- Set_Attributes for:OKL_ASSET_RETURNS_TL --
4529 ---------------------------------------------
4530 FUNCTION Set_Attributes (
4531 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
4532 x_okl_asset_returns_tl_rec OUT NOCOPY okl_asset_returns_tl_rec_type
4533 ) RETURN VARCHAR2 IS
4534 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4535 BEGIN
4536 x_okl_asset_returns_tl_rec := p_okl_asset_returns_tl_rec;
4537 x_okl_asset_returns_tl_rec.LANGUAGE := USERENV('LANG');
4538 x_okl_asset_returns_tl_rec.SOURCE_LANG := USERENV('LANG');
4539 RETURN(l_return_status);
4540 END Set_Attributes;
4541 BEGIN
4542 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4543 p_init_msg_list,
4544 '_PVT',
4545 x_return_status);
4546 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4547 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4548 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4549 RAISE OKC_API.G_EXCEPTION_ERROR;
4550 END IF;
4551 --- Setting item attributes
4552 l_return_status := Set_Attributes(
4553 p_okl_asset_returns_tl_rec, -- IN
4554 l_okl_asset_returns_tl_rec); -- OUT
4555 --- If any errors happen abort API
4556 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4557 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4558 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4559 RAISE OKC_API.G_EXCEPTION_ERROR;
4560 END IF;
4561 l_return_status := populate_new_record(l_okl_asset_returns_tl_rec, ldefoklassetreturnstlrec);
4562 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4563 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4564 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4565 RAISE OKC_API.G_EXCEPTION_ERROR;
4566 END IF;
4567 UPDATE OKL_ASSET_RETURNS_TL
4568 SET COMMENTS = ldefoklassetreturnstlrec.comments,
4569 SOURCE_LANG = ldefoklassetreturnstlrec.source_lang, --Fix for bug 3637102
4570 CREATED_BY = ldefoklassetreturnstlrec.created_by,
4571 CREATION_DATE = ldefoklassetreturnstlrec.creation_date,
4572 LAST_UPDATED_BY = ldefoklassetreturnstlrec.last_updated_by,
4573 LAST_UPDATE_DATE = ldefoklassetreturnstlrec.last_update_date,
4574 LAST_UPDATE_LOGIN = ldefoklassetreturnstlrec.last_update_login,
4575 NEW_ITEM_DESCRIPTION = ldefoklassetreturnstlrec.new_item_description
4576 WHERE ID = ldefoklassetreturnstlrec.id
4577 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);-- Fix for bug 3637102
4578 --AND SOURCE_LANG = USERENV('LANG');
4579
4580 UPDATE OKL_ASSET_RETURNS_TL
4581 SET SFWT_FLAG = 'Y'
4582 WHERE ID = ldefoklassetreturnstlrec.id
4583 AND SOURCE_LANG <> USERENV('LANG');
4584
4585 x_okl_asset_returns_tl_rec := ldefoklassetreturnstlrec;
4586 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4587 EXCEPTION
4588 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4589 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4590 (
4591 l_api_name,
4592 G_PKG_NAME,
4593 'OKC_API.G_RET_STS_ERROR',
4594 x_msg_count,
4595 x_msg_data,
4596 '_PVT'
4597 );
4598 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4599 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4600 (
4601 l_api_name,
4602 G_PKG_NAME,
4603 'OKC_API.G_RET_STS_UNEXP_ERROR',
4604 x_msg_count,
4605 x_msg_data,
4606 '_PVT'
4607 );
4608 WHEN OTHERS THEN
4609 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4610 (
4611 l_api_name,
4612 G_PKG_NAME,
4613 'OTHERS',
4614 x_msg_count,
4615 x_msg_data,
4616 '_PVT'
4617 );
4618 END update_row;
4619 ----------------------------------------
4620 -- update_row for:OKL_ASSET_RETURNS_V --
4621 ----------------------------------------
4622 -- Start of comments
4623 --
4624 -- Procedure Name : update_row
4625 -- Description : for OKL_ASSET_RETURNS_V
4626 -- Business Rules :
4627 -- Parameters :
4628 -- Version : 1.0
4629 -- History : RABHUPAT 16-DEC-2002 2667636
4630 -- : modified for multicurrency changes
4631 -- End of comments
4632 PROCEDURE update_row(
4633 p_api_version IN NUMBER,
4634 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4635 x_return_status OUT NOCOPY VARCHAR2,
4636 x_msg_count OUT NOCOPY NUMBER,
4637 x_msg_data OUT NOCOPY VARCHAR2,
4638 p_artv_rec IN artv_rec_type,
4639 x_artv_rec OUT NOCOPY artv_rec_type) IS
4640
4641 l_api_version CONSTANT NUMBER := 1;
4642 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4643 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4644 l_artv_rec artv_rec_type := p_artv_rec;
4645 l_def_artv_rec artv_rec_type;
4646 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
4647 lx_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
4648 l_art_rec art_rec_type;
4649 lx_art_rec art_rec_type;
4650 -------------------------------
4651 -- FUNCTION fill_who_columns --
4652 -------------------------------
4653 FUNCTION fill_who_columns (
4654 p_artv_rec IN artv_rec_type
4655 ) RETURN artv_rec_type IS
4656 l_artv_rec artv_rec_type := p_artv_rec;
4657 BEGIN
4658 l_artv_rec.LAST_UPDATE_DATE := SYSDATE;
4659 l_artv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4660 l_artv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4661 RETURN(l_artv_rec);
4662 END fill_who_columns;
4663 ----------------------------------
4664 -- FUNCTION populate_new_record --
4665 ----------------------------------
4666 FUNCTION populate_new_record (
4667 p_artv_rec IN artv_rec_type,
4668 x_artv_rec OUT NOCOPY artv_rec_type
4669 ) RETURN VARCHAR2 IS
4670 l_artv_rec artv_rec_type;
4671 l_row_notfound BOOLEAN := TRUE;
4672 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4673 BEGIN
4674 x_artv_rec := p_artv_rec;
4675 -- Get current database values
4676 l_artv_rec := get_rec(p_artv_rec, l_row_notfound);
4677 IF (l_row_notfound) THEN
4678 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4679 END IF;
4680 IF (x_artv_rec.id = OKC_API.G_MISS_NUM)
4681 THEN
4682 x_artv_rec.id := l_artv_rec.id;
4683 END IF;
4684 IF (x_artv_rec.object_version_number = OKC_API.G_MISS_NUM)
4685 THEN
4686 x_artv_rec.object_version_number := l_artv_rec.object_version_number;
4687 END IF;
4688 IF (x_artv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4689 THEN
4690 x_artv_rec.sfwt_flag := l_artv_rec.sfwt_flag;
4691 END IF;
4692 IF (x_artv_rec.rmr_id = OKC_API.G_MISS_NUM)
4693 THEN
4694 x_artv_rec.rmr_id := l_artv_rec.rmr_id;
4695 END IF;
4696 IF (x_artv_rec.imr_id = OKC_API.G_MISS_NUM)
4697 THEN
4698 x_artv_rec.imr_id := l_artv_rec.imr_id;
4699 END IF;
4700 IF (x_artv_rec.rna_id = OKC_API.G_MISS_NUM)
4701 THEN
4702 x_artv_rec.rna_id := l_artv_rec.rna_id;
4703 END IF;
4704 IF (x_artv_rec.kle_id = OKC_API.G_MISS_NUM)
4705 THEN
4706 x_artv_rec.kle_id := l_artv_rec.kle_id;
4707 END IF;
4708 IF (x_artv_rec.iso_id = OKC_API.G_MISS_NUM)
4709 THEN
4710 x_artv_rec.iso_id := l_artv_rec.iso_id;
4711 END IF;
4712 IF (x_artv_rec.security_dep_trx_ap_id = OKC_API.G_MISS_NUM)
4713 THEN
4714 x_artv_rec.security_dep_trx_ap_id := l_artv_rec.security_dep_trx_ap_id;
4715 END IF;
4716 IF (x_artv_rec.ars_code = OKC_API.G_MISS_CHAR)
4717 THEN
4718 x_artv_rec.ars_code := l_artv_rec.ars_code;
4719 END IF;
4720 IF (x_artv_rec.art1_code = OKC_API.G_MISS_CHAR)
4721 THEN
4722 x_artv_rec.art1_code := l_artv_rec.art1_code;
4723 END IF;
4724 IF (x_artv_rec.date_returned = OKC_API.G_MISS_DATE)
4725 THEN
4726 x_artv_rec.date_returned := l_artv_rec.date_returned;
4727 END IF;
4728 IF (x_artv_rec.date_title_returned = OKC_API.G_MISS_DATE)
4729 THEN
4730 x_artv_rec.date_title_returned := l_artv_rec.date_title_returned;
4731 END IF;
4732 IF (x_artv_rec.date_return_due = OKC_API.G_MISS_DATE)
4733 THEN
4734 x_artv_rec.date_return_due := l_artv_rec.date_return_due;
4735 END IF;
4736 IF (x_artv_rec.date_return_notified = OKC_API.G_MISS_DATE)
4737 THEN
4738 x_artv_rec.date_return_notified := l_artv_rec.date_return_notified;
4739 END IF;
4740 IF (x_artv_rec.relocate_asset_yn = OKC_API.G_MISS_CHAR)
4741 THEN
4742 x_artv_rec.relocate_asset_yn := l_artv_rec.relocate_asset_yn;
4743 END IF;
4744 IF (x_artv_rec.voluntary_yn = OKC_API.G_MISS_CHAR)
4745 THEN
4746 x_artv_rec.voluntary_yn := l_artv_rec.voluntary_yn;
4747 END IF;
4748 IF (x_artv_rec.date_repossession_required = OKC_API.G_MISS_DATE)
4749 THEN
4750 x_artv_rec.date_repossession_required := l_artv_rec.date_repossession_required;
4751 END IF;
4752 IF (x_artv_rec.date_repossession_actual = OKC_API.G_MISS_DATE)
4753 THEN
4754 x_artv_rec.date_repossession_actual := l_artv_rec.date_repossession_actual;
4755 END IF;
4756 IF (x_artv_rec.date_hold_until = OKC_API.G_MISS_DATE)
4757 THEN
4758 x_artv_rec.date_hold_until := l_artv_rec.date_hold_until;
4759 END IF;
4760 IF (x_artv_rec.commmercially_reas_sale_yn = OKC_API.G_MISS_CHAR)
4761 THEN
4762 x_artv_rec.commmercially_reas_sale_yn := l_artv_rec.commmercially_reas_sale_yn;
4763 END IF;
4764 IF (x_artv_rec.comments = OKC_API.G_MISS_CHAR)
4765 THEN
4766 x_artv_rec.comments := l_artv_rec.comments;
4767 END IF;
4768 IF (x_artv_rec.attribute_category = OKC_API.G_MISS_CHAR)
4769 THEN
4770 x_artv_rec.attribute_category := l_artv_rec.attribute_category;
4771 END IF;
4772 IF (x_artv_rec.attribute1 = OKC_API.G_MISS_CHAR)
4773 THEN
4774 x_artv_rec.attribute1 := l_artv_rec.attribute1;
4775 END IF;
4776 IF (x_artv_rec.attribute2 = OKC_API.G_MISS_CHAR)
4777 THEN
4778 x_artv_rec.attribute2 := l_artv_rec.attribute2;
4779 END IF;
4780 IF (x_artv_rec.attribute3 = OKC_API.G_MISS_CHAR)
4781 THEN
4782 x_artv_rec.attribute3 := l_artv_rec.attribute3;
4783 END IF;
4784 IF (x_artv_rec.attribute4 = OKC_API.G_MISS_CHAR)
4785 THEN
4786 x_artv_rec.attribute4 := l_artv_rec.attribute4;
4787 END IF;
4788 IF (x_artv_rec.attribute5 = OKC_API.G_MISS_CHAR)
4789 THEN
4790 x_artv_rec.attribute5 := l_artv_rec.attribute5;
4791 END IF;
4792 IF (x_artv_rec.attribute6 = OKC_API.G_MISS_CHAR)
4793 THEN
4794 x_artv_rec.attribute6 := l_artv_rec.attribute6;
4795 END IF;
4796 IF (x_artv_rec.attribute7 = OKC_API.G_MISS_CHAR)
4797 THEN
4798 x_artv_rec.attribute7 := l_artv_rec.attribute7;
4799 END IF;
4800 IF (x_artv_rec.attribute8 = OKC_API.G_MISS_CHAR)
4801 THEN
4802 x_artv_rec.attribute8 := l_artv_rec.attribute8;
4803 END IF;
4804 IF (x_artv_rec.attribute9 = OKC_API.G_MISS_CHAR)
4805 THEN
4806 x_artv_rec.attribute9 := l_artv_rec.attribute9;
4807 END IF;
4808 IF (x_artv_rec.attribute10 = OKC_API.G_MISS_CHAR)
4809 THEN
4810 x_artv_rec.attribute10 := l_artv_rec.attribute10;
4811 END IF;
4812 IF (x_artv_rec.attribute11 = OKC_API.G_MISS_CHAR)
4813 THEN
4814 x_artv_rec.attribute11 := l_artv_rec.attribute11;
4815 END IF;
4816 IF (x_artv_rec.attribute12 = OKC_API.G_MISS_CHAR)
4817 THEN
4818 x_artv_rec.attribute12 := l_artv_rec.attribute12;
4819 END IF;
4820 IF (x_artv_rec.attribute13 = OKC_API.G_MISS_CHAR)
4821 THEN
4822 x_artv_rec.attribute13 := l_artv_rec.attribute13;
4823 END IF;
4824 IF (x_artv_rec.attribute14 = OKC_API.G_MISS_CHAR)
4825 THEN
4826 x_artv_rec.attribute14 := l_artv_rec.attribute14;
4827 END IF;
4828 IF (x_artv_rec.attribute15 = OKC_API.G_MISS_CHAR)
4829 THEN
4830 x_artv_rec.attribute15 := l_artv_rec.attribute15;
4831 END IF;
4832 IF (x_artv_rec.org_id = OKC_API.G_MISS_NUM)
4833 THEN
4834 x_artv_rec.org_id := l_artv_rec.org_id;
4835 END IF;
4836 IF (x_artv_rec.request_id = OKC_API.G_MISS_NUM)
4837 THEN
4838 x_artv_rec.request_id := l_artv_rec.request_id;
4839 END IF;
4840 IF (x_artv_rec.program_application_id = OKC_API.G_MISS_NUM)
4841 THEN
4842 x_artv_rec.program_application_id := l_artv_rec.program_application_id;
4843 END IF;
4844 IF (x_artv_rec.program_id = OKC_API.G_MISS_NUM)
4845 THEN
4846 x_artv_rec.program_id := l_artv_rec.program_id;
4847 END IF;
4848 IF (x_artv_rec.program_update_date = OKC_API.G_MISS_DATE)
4849 THEN
4850 x_artv_rec.program_update_date := l_artv_rec.program_update_date;
4851 END IF;
4852 IF (x_artv_rec.created_by = OKC_API.G_MISS_NUM)
4853 THEN
4854 x_artv_rec.created_by := l_artv_rec.created_by;
4855 END IF;
4856 IF (x_artv_rec.creation_date = OKC_API.G_MISS_DATE)
4857 THEN
4858 x_artv_rec.creation_date := l_artv_rec.creation_date;
4859 END IF;
4860 IF (x_artv_rec.last_updated_by = OKC_API.G_MISS_NUM)
4861 THEN
4862 x_artv_rec.last_updated_by := l_artv_rec.last_updated_by;
4863 END IF;
4864 IF (x_artv_rec.last_update_date = OKC_API.G_MISS_DATE)
4865 THEN
4866 x_artv_rec.last_update_date := l_artv_rec.last_update_date;
4867 END IF;
4868 IF (x_artv_rec.last_update_login = OKC_API.G_MISS_NUM)
4869 THEN
4870 x_artv_rec.last_update_login := l_artv_rec.last_update_login;
4871 END IF;
4872
4873 IF (x_artv_rec.floor_price = OKC_API.G_MISS_NUM)
4874 THEN
4875 x_artv_rec.floor_price := l_artv_rec.floor_price;
4876 END IF;
4877 IF (x_artv_rec.new_item_price = OKC_API.G_MISS_NUM)
4878 THEN
4879 x_artv_rec.new_item_price := l_artv_rec.new_item_price;
4880 END IF;
4881
4882 -- SECHAWLA 30-SEP-04 3924244 : added a new column new_item_number
4883 IF (x_artv_rec.new_item_number = OKC_API.G_MISS_CHAR)
4884 THEN
4885 x_artv_rec.new_item_number := l_artv_rec.new_item_number;
4886 END IF;
4887
4888 IF (x_artv_rec.asset_relocated_yn = OKC_API.G_MISS_CHAR)
4889 THEN
4890 x_artv_rec.asset_relocated_yn := l_artv_rec.asset_relocated_yn;
4891 END IF;
4892 IF (x_artv_rec.new_item_description = OKC_API.G_MISS_CHAR)
4893 THEN
4894 x_artv_rec.new_item_description := l_artv_rec.new_item_description;
4895 END IF;
4896 IF (x_artv_rec.repurchase_agmt_yn = OKC_API.G_MISS_CHAR)
4897 THEN
4898 x_artv_rec.repurchase_agmt_yn := l_artv_rec.repurchase_agmt_yn;
4899 END IF;
4900 IF (x_artv_rec.like_kind_yn = OKC_API.G_MISS_CHAR)
4901 THEN
4902 x_artv_rec.like_kind_yn := l_artv_rec.like_kind_yn;
4903 END IF;
4904
4905 -- RABHUPAT - 2667636 - Start
4906 IF (x_artv_rec.currency_code = OKC_API.G_MISS_CHAR)
4907 THEN
4908 x_artv_rec.currency_code := l_artv_rec.currency_code;
4909 END IF;
4910 IF (x_artv_rec.currency_conversion_code = OKC_API.G_MISS_CHAR)
4911 THEN
4912 x_artv_rec.currency_conversion_code := l_artv_rec.currency_conversion_code;
4913 END IF;
4914 IF (x_artv_rec.currency_conversion_type = OKC_API.G_MISS_CHAR)
4915 THEN
4916 x_artv_rec.currency_conversion_type := l_artv_rec.currency_conversion_type;
4917 END IF;
4918 IF (x_artv_rec.currency_conversion_rate = OKC_API.G_MISS_NUM)
4919 THEN
4920 x_artv_rec.currency_conversion_rate := l_artv_rec.currency_conversion_rate;
4921 END IF;
4922 IF (x_artv_rec.currency_conversion_date = OKC_API.G_MISS_DATE)
4923 THEN
4924 x_artv_rec.currency_conversion_date := l_artv_rec.currency_conversion_date;
4925 END IF;
4926 -- RABHUPAT - 2667636 - End
4927 -- RRAVIKIR Legal Entity Changes
4928 IF (x_artv_rec.legal_entity_id = OKC_API.G_MISS_NUM)
4929 THEN
4930 x_artv_rec.legal_entity_id := l_artv_rec.legal_entity_id;
4931 END IF;
4932 -- Legal Entity Changes End
4933 -- DJANASWA Loan Repossession proj start
4934 IF (x_artv_rec.ASSET_FMV_AMOUNT = OKC_API.G_MISS_NUM)
4935 THEN
4936 x_artv_rec.ASSET_FMV_AMOUNT := l_artv_rec.ASSET_FMV_AMOUNT;
4937 END IF;
4938 -- Loan Repossession proj end
4939 RETURN(l_return_status);
4940 END populate_new_record;
4941 --------------------------------------------
4942 -- Set_Attributes for:OKL_ASSET_RETURNS_V --
4943 --------------------------------------------
4944 FUNCTION Set_Attributes (
4945 p_artv_rec IN artv_rec_type,
4946 x_artv_rec OUT NOCOPY artv_rec_type
4947 ) RETURN VARCHAR2 IS
4948 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4949 BEGIN
4950 x_artv_rec := p_artv_rec;
4951 x_artv_rec.OBJECT_VERSION_NUMBER := NVL(x_artv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
4952 RETURN(l_return_status);
4953 END Set_Attributes;
4954 BEGIN
4955 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4956 G_PKG_NAME,
4957 p_init_msg_list,
4958 l_api_version,
4959 p_api_version,
4960 '_PVT',
4961 x_return_status);
4962 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4963 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4964 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4965 RAISE OKC_API.G_EXCEPTION_ERROR;
4966 END IF;
4967 --- Setting item attributes
4968 l_return_status := Set_Attributes(
4969 p_artv_rec, -- IN
4970 l_artv_rec); -- OUT
4971 --- If any errors happen abort API
4972 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4973 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4974 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4975 RAISE OKC_API.G_EXCEPTION_ERROR;
4976 END IF;
4977 l_return_status := populate_new_record(l_artv_rec, l_def_artv_rec);
4978 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4979 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4980 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4981 RAISE OKC_API.G_EXCEPTION_ERROR;
4982 END IF;
4983 l_def_artv_rec := fill_who_columns(l_def_artv_rec);
4984 --- Validate all non-missing attributes (Item Level Validation)
4985 l_return_status := Validate_Attributes(l_def_artv_rec);
4986 --- If any errors happen abort API
4987 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4988 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4989 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4990 RAISE OKC_API.G_EXCEPTION_ERROR;
4991 END IF;
4992 l_return_status := Validate_Record(l_def_artv_rec);
4993 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4994 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4995 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4996 RAISE OKC_API.G_EXCEPTION_ERROR;
4997 END IF;
4998
4999 --------------------------------------
5000 -- Move VIEW record to "Child" records
5001 --------------------------------------
5002 migrate(l_def_artv_rec, l_okl_asset_returns_tl_rec);
5003 migrate(l_def_artv_rec, l_art_rec);
5004 --------------------------------------------
5005 -- Call the UPDATE_ROW for each child record
5006 --------------------------------------------
5007 update_row(
5008 p_init_msg_list,
5009 x_return_status,
5010 x_msg_count,
5011 x_msg_data,
5012 l_okl_asset_returns_tl_rec,
5013 lx_okl_asset_returns_tl_rec
5014 );
5015 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5016 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5017 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5018 RAISE OKC_API.G_EXCEPTION_ERROR;
5019 END IF;
5020 migrate(lx_okl_asset_returns_tl_rec, l_def_artv_rec);
5021 update_row(
5022 p_init_msg_list,
5023 x_return_status,
5024 x_msg_count,
5025 x_msg_data,
5026 l_art_rec,
5027 lx_art_rec
5028 );
5029 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5031 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5032 RAISE OKC_API.G_EXCEPTION_ERROR;
5033 END IF;
5034 migrate(lx_art_rec, l_def_artv_rec);
5035 x_artv_rec := l_def_artv_rec;
5036 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5037 EXCEPTION
5038 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5039 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5040 (
5041 l_api_name,
5042 G_PKG_NAME,
5043 'OKC_API.G_RET_STS_ERROR',
5044 x_msg_count,
5045 x_msg_data,
5046 '_PVT'
5047 );
5048 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5049 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5050 (
5051 l_api_name,
5052 G_PKG_NAME,
5053 'OKC_API.G_RET_STS_UNEXP_ERROR',
5054 x_msg_count,
5055 x_msg_data,
5056 '_PVT'
5057 );
5058 WHEN OTHERS THEN
5059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5060 (
5061 l_api_name,
5062 G_PKG_NAME,
5063 'OTHERS',
5064 x_msg_count,
5065 x_msg_data,
5066 '_PVT'
5067 );
5068 END update_row;
5069 ----------------------------------------
5070 -- PL/SQL TBL update_row for:ARTV_TBL --
5071 ----------------------------------------
5072 PROCEDURE update_row(
5073 p_api_version IN NUMBER,
5074 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5075 x_return_status OUT NOCOPY VARCHAR2,
5076 x_msg_count OUT NOCOPY NUMBER,
5077 x_msg_data OUT NOCOPY VARCHAR2,
5078 p_artv_tbl IN artv_tbl_type,
5079 x_artv_tbl OUT NOCOPY artv_tbl_type) IS
5080
5081 l_api_version CONSTANT NUMBER := 1;
5082 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
5083 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5084 i NUMBER := 0;
5085 -- Begin Post-Generation Change
5086 -- overall error status
5087 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5088 -- End Post-Generation Change
5089 BEGIN
5090 OKC_API.init_msg_list(p_init_msg_list);
5091 -- Make sure PL/SQL table has records in it before passing
5092 IF (p_artv_tbl.COUNT > 0) THEN
5093 i := p_artv_tbl.FIRST;
5094 LOOP
5095 update_row (
5096 p_api_version => p_api_version,
5097 p_init_msg_list => OKC_API.G_FALSE,
5098 x_return_status => x_return_status,
5099 x_msg_count => x_msg_count,
5100 x_msg_data => x_msg_data,
5101 p_artv_rec => p_artv_tbl(i),
5102 x_artv_rec => x_artv_tbl(i));
5103 -- Begin Post-Generation Change
5104 -- store the highest degree of error
5105 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
5106 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
5107 l_overall_status := x_return_status;
5108 END IF;
5109 END IF;
5110 -- End Post-Generation Change
5111 EXIT WHEN (i = p_artv_tbl.LAST);
5112 i := p_artv_tbl.NEXT(i);
5113 END LOOP;
5114 -- Begin Post-Generation Change
5115 -- return overall status
5116 x_return_status := l_overall_status;
5117 -- End Post-Generation Change
5118 END IF;
5119 EXCEPTION
5120 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5121 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5122 (
5123 l_api_name,
5124 G_PKG_NAME,
5125 'OKC_API.G_RET_STS_ERROR',
5126 x_msg_count,
5127 x_msg_data,
5128 '_PVT'
5129 );
5130 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5131 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5132 (
5133 l_api_name,
5134 G_PKG_NAME,
5135 'OKC_API.G_RET_STS_UNEXP_ERROR',
5136 x_msg_count,
5137 x_msg_data,
5138 '_PVT'
5139 );
5140 WHEN OTHERS THEN
5141 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5142 (
5143 l_api_name,
5144 G_PKG_NAME,
5145 'OTHERS',
5146 x_msg_count,
5147 x_msg_data,
5148 '_PVT'
5149 );
5150 END update_row;
5151
5152 ---------------------------------------------------------------------------
5153 -- PROCEDURE delete_row
5154 ---------------------------------------------------------------------------
5155 ----------------------------------------
5156 -- delete_row for:OKL_ASSET_RETURNS_B --
5157 ----------------------------------------
5158 PROCEDURE delete_row(
5159 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5160 x_return_status OUT NOCOPY VARCHAR2,
5161 x_msg_count OUT NOCOPY NUMBER,
5162 x_msg_data OUT NOCOPY VARCHAR2,
5163 p_art_rec IN art_rec_type) IS
5164
5165 l_api_version CONSTANT NUMBER := 1;
5166 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
5167 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5168 l_art_rec art_rec_type:= p_art_rec;
5169 l_row_notfound BOOLEAN := TRUE;
5170 BEGIN
5171 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5172 p_init_msg_list,
5173 '_PVT',
5174 x_return_status);
5175 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5177 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5178 RAISE OKC_API.G_EXCEPTION_ERROR;
5179 END IF;
5180 DELETE FROM OKL_ASSET_RETURNS_B
5181 WHERE ID = l_art_rec.id;
5182
5183 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5184 EXCEPTION
5185 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5186 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5187 (
5188 l_api_name,
5189 G_PKG_NAME,
5190 'OKC_API.G_RET_STS_ERROR',
5191 x_msg_count,
5192 x_msg_data,
5193 '_PVT'
5194 );
5195 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5196 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5197 (
5198 l_api_name,
5199 G_PKG_NAME,
5200 'OKC_API.G_RET_STS_UNEXP_ERROR',
5201 x_msg_count,
5202 x_msg_data,
5203 '_PVT'
5204 );
5205 WHEN OTHERS THEN
5206 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5207 (
5208 l_api_name,
5209 G_PKG_NAME,
5210 'OTHERS',
5211 x_msg_count,
5212 x_msg_data,
5213 '_PVT'
5214 );
5215 END delete_row;
5216 -----------------------------------------
5217 -- delete_row for:OKL_ASSET_RETURNS_TL --
5218 -----------------------------------------
5219 PROCEDURE delete_row(
5220 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5221 x_return_status OUT NOCOPY VARCHAR2,
5222 x_msg_count OUT NOCOPY NUMBER,
5223 x_msg_data OUT NOCOPY VARCHAR2,
5224 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type) IS
5225
5226 l_api_version CONSTANT NUMBER := 1;
5227 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
5228 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5229 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type:= p_okl_asset_returns_tl_rec;
5230 l_row_notfound BOOLEAN := TRUE;
5231 ---------------------------------------------
5232 -- Set_Attributes for:OKL_ASSET_RETURNS_TL --
5233 ---------------------------------------------
5234 FUNCTION Set_Attributes (
5235 p_okl_asset_returns_tl_rec IN okl_asset_returns_tl_rec_type,
5236 x_okl_asset_returns_tl_rec OUT NOCOPY okl_asset_returns_tl_rec_type
5237 ) RETURN VARCHAR2 IS
5238 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5239 BEGIN
5240 x_okl_asset_returns_tl_rec := p_okl_asset_returns_tl_rec;
5241 x_okl_asset_returns_tl_rec.LANGUAGE := USERENV('LANG');
5242 RETURN(l_return_status);
5243 END Set_Attributes;
5244 BEGIN
5245 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5246 p_init_msg_list,
5247 '_PVT',
5248 x_return_status);
5249 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5251 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5252 RAISE OKC_API.G_EXCEPTION_ERROR;
5253 END IF;
5254 --- Setting item attributes
5255 l_return_status := Set_Attributes(
5256 p_okl_asset_returns_tl_rec, -- IN
5257 l_okl_asset_returns_tl_rec); -- OUT
5258 --- If any errors happen abort API
5259 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5260 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5261 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5262 RAISE OKC_API.G_EXCEPTION_ERROR;
5263 END IF;
5264 DELETE FROM OKL_ASSET_RETURNS_TL
5265 WHERE ID = l_okl_asset_returns_tl_rec.id;
5266
5267 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5268 EXCEPTION
5269 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5270 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5271 (
5272 l_api_name,
5273 G_PKG_NAME,
5274 'OKC_API.G_RET_STS_ERROR',
5275 x_msg_count,
5276 x_msg_data,
5277 '_PVT'
5278 );
5279 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5280 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5281 (
5282 l_api_name,
5283 G_PKG_NAME,
5284 'OKC_API.G_RET_STS_UNEXP_ERROR',
5285 x_msg_count,
5286 x_msg_data,
5287 '_PVT'
5288 );
5289 WHEN OTHERS THEN
5290 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5291 (
5292 l_api_name,
5293 G_PKG_NAME,
5294 'OTHERS',
5295 x_msg_count,
5296 x_msg_data,
5297 '_PVT'
5298 );
5299 END delete_row;
5300 ----------------------------------------
5301 -- delete_row for:OKL_ASSET_RETURNS_V --
5302 ----------------------------------------
5303 PROCEDURE delete_row(
5304 p_api_version IN NUMBER,
5305 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5306 x_return_status OUT NOCOPY VARCHAR2,
5307 x_msg_count OUT NOCOPY NUMBER,
5308 x_msg_data OUT NOCOPY VARCHAR2,
5309 p_artv_rec IN artv_rec_type) IS
5310
5311 l_api_version CONSTANT NUMBER := 1;
5312 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5313 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5314 l_artv_rec artv_rec_type := p_artv_rec;
5315 l_okl_asset_returns_tl_rec okl_asset_returns_tl_rec_type;
5316 l_art_rec art_rec_type;
5317 BEGIN
5318 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5319 G_PKG_NAME,
5320 p_init_msg_list,
5321 l_api_version,
5322 p_api_version,
5323 '_PVT',
5324 x_return_status);
5325 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5327 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5328 RAISE OKC_API.G_EXCEPTION_ERROR;
5329 END IF;
5330 --------------------------------------
5331 -- Move VIEW record to "Child" records
5332 --------------------------------------
5333 migrate(l_artv_rec, l_okl_asset_returns_tl_rec);
5334 migrate(l_artv_rec, l_art_rec);
5335 --------------------------------------------
5336 -- Call the DELETE_ROW for each child record
5337 --------------------------------------------
5338 delete_row(
5339 p_init_msg_list,
5340 x_return_status,
5341 x_msg_count,
5342 x_msg_data,
5343 l_okl_asset_returns_tl_rec
5344 );
5345 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5346 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5347 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5348 RAISE OKC_API.G_EXCEPTION_ERROR;
5349 END IF;
5350 delete_row(
5351 p_init_msg_list,
5352 x_return_status,
5353 x_msg_count,
5354 x_msg_data,
5355 l_art_rec
5356 );
5357 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5358 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5359 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5360 RAISE OKC_API.G_EXCEPTION_ERROR;
5361 END IF;
5362 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5363 EXCEPTION
5364 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5365 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5366 (
5367 l_api_name,
5368 G_PKG_NAME,
5369 'OKC_API.G_RET_STS_ERROR',
5370 x_msg_count,
5371 x_msg_data,
5372 '_PVT'
5373 );
5374 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5375 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5376 (
5377 l_api_name,
5378 G_PKG_NAME,
5379 'OKC_API.G_RET_STS_UNEXP_ERROR',
5380 x_msg_count,
5381 x_msg_data,
5382 '_PVT'
5383 );
5384 WHEN OTHERS THEN
5385 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5386 (
5387 l_api_name,
5388 G_PKG_NAME,
5389 'OTHERS',
5390 x_msg_count,
5391 x_msg_data,
5392 '_PVT'
5393 );
5394 END delete_row;
5395 ----------------------------------------
5396 -- PL/SQL TBL delete_row for:ARTV_TBL --
5397 ----------------------------------------
5398 PROCEDURE delete_row(
5399 p_api_version IN NUMBER,
5400 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
5401 x_return_status OUT NOCOPY VARCHAR2,
5402 x_msg_count OUT NOCOPY NUMBER,
5403 x_msg_data OUT NOCOPY VARCHAR2,
5404 p_artv_tbl IN artv_tbl_type) IS
5405
5406 l_api_version CONSTANT NUMBER := 1;
5407 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5408 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5409 i NUMBER := 0;
5410 -- Begin Post-Generation Change
5411 -- overall error status
5412 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5413 -- End Post-Generation Change
5414 BEGIN
5415 OKC_API.init_msg_list(p_init_msg_list);
5416 -- Make sure PL/SQL table has records in it before passing
5417 IF (p_artv_tbl.COUNT > 0) THEN
5418 i := p_artv_tbl.FIRST;
5419 LOOP
5420 delete_row (
5421 p_api_version => p_api_version,
5422 p_init_msg_list => OKC_API.G_FALSE,
5423 x_return_status => x_return_status,
5424 x_msg_count => x_msg_count,
5425 x_msg_data => x_msg_data,
5426 p_artv_rec => p_artv_tbl(i));
5427 -- Begin Post-Generation Change
5428 -- store the highest degree of error
5429 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
5430 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
5431 l_overall_status := x_return_status;
5432 END IF;
5433 END IF;
5434 -- End Post-Generation Change
5435 EXIT WHEN (i = p_artv_tbl.LAST);
5436 i := p_artv_tbl.NEXT(i);
5437 END LOOP;
5438 -- Begin Post-Generation Change
5439 -- return overall status
5440 x_return_status := l_overall_status;
5441 -- End Post-Generation Change
5442 END IF;
5443 EXCEPTION
5444 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5445 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5446 (
5447 l_api_name,
5448 G_PKG_NAME,
5449 'OKC_API.G_RET_STS_ERROR',
5450 x_msg_count,
5451 x_msg_data,
5452 '_PVT'
5453 );
5454 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5455 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5456 (
5457 l_api_name,
5458 G_PKG_NAME,
5459 'OKC_API.G_RET_STS_UNEXP_ERROR',
5460 x_msg_count,
5461 x_msg_data,
5462 '_PVT'
5463 );
5464 WHEN OTHERS THEN
5465 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5466 (
5467 l_api_name,
5468 G_PKG_NAME,
5469 'OTHERS',
5470 x_msg_count,
5471 x_msg_data,
5472 '_PVT'
5473 );
5474 END delete_row;
5475 END OKL_ART_PVT;