DBA Data[Home] [Help]

PACKAGE BODY: APPS.OKL_IRV_PVT

Source


1 PACKAGE BODY OKL_IRV_PVT AS
2   /* $Header: OKLSIRVB.pls 120.1 2005/07/22 10:02:59 smadhava noship $ */
3 
4 G_NO_PARENT_RECORD	CONSTANT VARCHAR2(200):='OKC_NO_PARENT_RECORD';
5 G_UNEXPECTED_ERROR	CONSTANT VARCHAR2(200) :='OKC_CONTRACTS_UNEXPECTED_ERROR';
6 G_SQLERRM_TOKEN	CONSTANT VARCHAR2(200) := 'SQLerrm';
7 G_SQLCODE_TOKEN	CONSTANT VARCHAR2(200) := 'SQLcode';
8 G_EXCEPTION_HALT_VALIDATION exception;
9 PROCEDURE api_copy IS
10 BEGIN
11   null;
12 END api_copy;
13 
14 PROCEDURE change_version IS
15 BEGIN
16   null;
17 END change_version;
18 
19 --------------------------------------------------------------------------------
20 -- Procedure get_rec for OKL_FE_ITEM_RESDL_VALUES
21 --------------------------------------------------------------------------------
22 
23  FUNCTION get_rec(
24   p_irv_rec	IN okl_irv_rec,
25   x_no_data_found	OUT NOCOPY BOOLEAN
26  )RETURN okl_irv_rec IS
27    CURSOR irv_pk_csr(p_id IN NUMBER) IS
28    SELECT
29 	ITEM_RESDL_VALUE_ID,
30     OBJECT_VERSION_NUMBER,
31     ITEM_RESIDUAL_ID,
32 	ITEM_RESDL_VERSION_ID,
33 	TERM_IN_MONTHS,
34 	RESIDUAL_VALUE,
35 	CREATED_BY,
36 	CREATION_DATE,
37 	LAST_UPDATED_BY,
38 	LAST_UPDATE_DATE,
39 	LAST_UPDATE_LOGIN
40    FROM OKL_FE_ITEM_RESDL_VALUES IRV WHERE IRV.item_resdl_value_id=p_id;
41   l_irv_pk	irv_pk_csr%ROWTYPE;
42   l_irv_rec	okl_irv_rec;
43   BEGIN
44   x_no_data_found:= TRUE;
45   --Get current data base values
46   OPEN irv_pk_csr(p_irv_rec.item_resdl_value_id);
47   FETCH irv_pk_csr INTO
48 	l_irv_rec.ITEM_RESDL_VALUE_ID,
49 	l_irv_rec.OBJECT_VERSION_NUMBER,
50 	l_irv_rec.ITEM_RESIDUAL_ID,
51 	l_irv_rec.ITEM_RESDL_VERSION_ID,
52 	l_irv_rec.TERM_IN_MONTHS,
53 	l_irv_rec.RESIDUAL_VALUE,
54 	l_irv_rec.CREATED_BY,
55 	l_irv_rec.CREATION_DATE,
56 	l_irv_rec.LAST_UPDATED_BY,
57 	l_irv_rec.LAST_UPDATE_DATE,
58 	l_irv_rec.LAST_UPDATE_LOGIN;
59 	  x_no_data_found := irv_pk_csr%NOTFOUND;
60   CLOSE irv_pk_csr;
61   RETURN (l_irv_rec);
62  END get_rec;
63  FUNCTION get_rec(
64   p_irv_rec	IN okl_irv_rec
65  )RETURN okl_irv_rec IS
66  l_row_notfound	BOOLEAN:=TRUE; BEGIN
67   RETURN(get_rec(p_irv_rec,l_row_notfound));
68  END get_rec;
69 
70 
71  FUNCTION null_out_defaults(
72  p_irv_rec IN okl_irv_rec
73  ) RETURN okl_irv_rec IS
74  l_irv_rec	okl_irv_rec:= p_irv_rec;
75  BEGIN
76 	IF (l_irv_rec.ITEM_RESDL_VALUE_ID=OKL_API.G_MISS_NUM) THEN
77 	 l_irv_rec.ITEM_RESDL_VALUE_ID:=NULL;
78 	END IF;
79 	IF (l_irv_rec.OBJECT_VERSION_NUMBER=OKL_API.G_MISS_NUM) THEN
80 	 l_irv_rec.OBJECT_VERSION_NUMBER:=NULL;
81 	END IF;
82 	IF (l_irv_rec.ITEM_RESIDUAL_ID=OKL_API.G_MISS_NUM) THEN
83 	 l_irv_rec.ITEM_RESIDUAL_ID:=NULL;
84 	END IF;
85 	IF (l_irv_rec.ITEM_RESDL_VERSION_ID=OKL_API.G_MISS_NUM) THEN
86 	 l_irv_rec.ITEM_RESDL_VERSION_ID:=NULL;
87 	END IF;
88 	IF (l_irv_rec.TERM_IN_MONTHS=OKL_API.G_MISS_NUM) THEN
89 	 l_irv_rec.TERM_IN_MONTHS:=NULL;
90 	END IF;
91 	IF (l_irv_rec.RESIDUAL_VALUE=OKL_API.G_MISS_NUM) THEN
92 	 l_irv_rec.RESIDUAL_VALUE:=NULL;
93 	END IF;
94 	IF (l_irv_rec.CREATED_BY=OKL_API.G_MISS_NUM) THEN
95 	 l_irv_rec.CREATED_BY:=NULL;
96 	END IF;
97 	IF (l_irv_rec.CREATION_DATE=OKL_API.G_MISS_DATE) THEN
98 	 l_irv_rec.CREATION_DATE:=NULL;
99 	END IF;
100 	IF (l_irv_rec.LAST_UPDATED_BY=OKL_API.G_MISS_NUM) THEN
101 	 l_irv_rec.LAST_UPDATED_BY:=NULL;
102 	END IF;
103 	IF (l_irv_rec.LAST_UPDATE_DATE=OKL_API.G_MISS_DATE) THEN
104 	 l_irv_rec.LAST_UPDATE_DATE:=NULL;
105 	END IF;
106 	IF (l_irv_rec.LAST_UPDATE_LOGIN=OKL_API.G_MISS_NUM) THEN
107 	 l_irv_rec.LAST_UPDATE_LOGIN:=NULL;
108 	END IF;
109   RETURN(l_irv_rec);
110  END null_out_defaults;
111 
112  FUNCTION get_seq_id RETURN NUMBER IS
113   BEGIN
114 	RETURN(okc_p_util.raw_to_number(sys_guid()));
115 	END get_seq_id;
116 
117   ---------------------------------
118   -- FUNCTION validate_item_resdl_value_id
119   ---------------------------------
120   FUNCTION validate_item_resdl_value_id (p_item_resdl_value_id IN NUMBER) Return Varchar2 IS
121     l_api_name            CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_item_resdl_value_id';
122   BEGIN
123     IF p_item_resdl_value_id IS NULL THEN
124       OKL_API.set_message(p_app_name      => G_APP_NAME,
125                           p_msg_name      => G_REQUIRED_VALUE,
126                           p_token1        => G_COL_NAME_TOKEN,
127                           p_token1_value  => 'item_resdl_value_id');
128       RAISE OKL_API.G_EXCEPTION_ERROR;
129     END IF;
130     Return G_RET_STS_SUCCESS;
131   EXCEPTION
132     WHEN OKL_API.G_EXCEPTION_ERROR THEN
133       Return G_RET_STS_ERROR;
134     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
135       Return G_RET_STS_UNEXP_ERROR;
136     WHEN OTHERS THEN
137       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
138                            p_msg_name     => G_DB_ERROR,
139                            p_token1       => G_PROG_NAME_TOKEN,
140                            p_token1_value => l_api_name,
141                            p_token2       => 'SQLCODE',
142                            p_token2_value => sqlcode,
143                            p_token3       => 'SQLERRM',
144                            p_token3_value => sqlerrm);
145       Return G_RET_STS_UNEXP_ERROR;
146   END validate_item_resdl_value_id;
147 
148   -------------------------------------------
149   -- Function validate_object_version_number
150   -------------------------------------------
151   FUNCTION validate_object_version_number (p_object_version_number IN NUMBER) Return Varchar2 IS
152     l_api_name  CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_object_version_number';
153   BEGIN
154     IF (p_object_version_number IS NULL) OR (p_object_version_number = OKL_API.G_MISS_NUM) THEN
155       OKL_API.set_message(p_app_name      => G_APP_NAME,
156                           p_msg_name      => G_REQUIRED_VALUE,
157                           p_token1        => G_COL_NAME_TOKEN,
158                           p_token1_value  => 'object_version_number');
159       RAISE OKL_API.G_EXCEPTION_ERROR;
160     END IF;
161     Return G_RET_STS_SUCCESS;
162   EXCEPTION
163     WHEN OKL_API.G_EXCEPTION_ERROR THEN
164       Return G_RET_STS_ERROR;
165     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
166       Return G_RET_STS_UNEXP_ERROR;
167     WHEN OTHERS THEN
168       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
169                            p_msg_name     => G_DB_ERROR,
170                            p_token1       => G_PROG_NAME_TOKEN,
171                            p_token1_value => l_api_name,
172                            p_token2       => 'SQLCODE',
173                            p_token2_value => sqlcode,
174                            p_token3       => 'SQLERRM',
175                            p_token3_value => sqlerrm);
176       Return G_RET_STS_UNEXP_ERROR;
177 END validate_object_version_number;
178 
179   ---------------------------------
180   -- FUNCTION validate_item_resdl_version_id
181   ---------------------------------
182   FUNCTION validate_item_residual_id (p_item_residual_id IN NUMBER) Return Varchar2 IS
183     l_api_name            CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_item_residual_id';
184   BEGIN
185     IF p_item_residual_id IS NULL THEN
186       OKL_API.set_message(p_app_name      => G_APP_NAME,
187                           p_msg_name      => G_REQUIRED_VALUE,
188                           p_token1        => G_COL_NAME_TOKEN,
189                           p_token1_value  => 'item_residual_id');
190       RAISE OKL_API.G_EXCEPTION_ERROR;
191     END IF;
192     Return G_RET_STS_SUCCESS;
193   EXCEPTION
194     WHEN OKL_API.G_EXCEPTION_ERROR THEN
195       Return G_RET_STS_ERROR;
196     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
197       Return G_RET_STS_UNEXP_ERROR;
198     WHEN OTHERS THEN
199       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
200                            p_msg_name     => G_DB_ERROR,
201                            p_token1       => G_PROG_NAME_TOKEN,
202                            p_token1_value => l_api_name,
203                            p_token2       => 'SQLCODE',
204                            p_token2_value => sqlcode,
205                            p_token3       => 'SQLERRM',
206                            p_token3_value => sqlerrm);
207       Return G_RET_STS_UNEXP_ERROR;
208   END validate_item_residual_id;
209 
210   ---------------------------------
211   -- FUNCTION validate_item_resdl_version_id
212   ---------------------------------
213   FUNCTION validate_item_resdl_version_id (p_item_resdl_version_id IN NUMBER) Return Varchar2 IS
214     l_api_name            CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_item_resdl_version_id';
215   BEGIN
216     IF p_item_resdl_version_id IS NULL THEN
217       OKL_API.set_message(p_app_name      => G_APP_NAME,
218                           p_msg_name      => G_REQUIRED_VALUE,
219                           p_token1        => G_COL_NAME_TOKEN,
220                           p_token1_value  => 'item_resdl_version_id');
221       RAISE OKL_API.G_EXCEPTION_ERROR;
222     END IF;
223     Return G_RET_STS_SUCCESS;
224   EXCEPTION
225     WHEN OKL_API.G_EXCEPTION_ERROR THEN
226       Return G_RET_STS_ERROR;
227     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
228       Return G_RET_STS_UNEXP_ERROR;
229     WHEN OTHERS THEN
230       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
231                            p_msg_name     => G_DB_ERROR,
232                            p_token1       => G_PROG_NAME_TOKEN,
233                            p_token1_value => l_api_name,
234                            p_token2       => 'SQLCODE',
235                            p_token2_value => sqlcode,
236                            p_token3       => 'SQLERRM',
237                            p_token3_value => sqlerrm);
238       Return G_RET_STS_UNEXP_ERROR;
239   END validate_item_resdl_version_id;
240 
241   ---------------------------------
242   -- FUNCTION validate_term
243   ---------------------------------
244   FUNCTION validate_term (p_term IN NUMBER) Return Varchar2 IS
245     l_api_name            CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_term';
246   BEGIN
247     IF p_term IS NULL THEN
248       OKL_API.set_message(p_app_name      => G_APP_NAME,
249                           p_msg_name      => G_REQUIRED_VALUE,
250                           p_token1        => G_COL_NAME_TOKEN,
251                           p_token1_value  => 'term');
252       RAISE OKL_API.G_EXCEPTION_ERROR;
253     ELSIF p_term <=0 THEN
254       OKL_API.set_message(p_app_name      => G_APP_NAME,
255                           p_msg_name      => 'OKL_INVALID_TERM');
256       RAISE OKL_API.G_EXCEPTION_ERROR;
257     END IF;
258     Return G_RET_STS_SUCCESS;
259   EXCEPTION
260     WHEN OKL_API.G_EXCEPTION_ERROR THEN
261       Return G_RET_STS_ERROR;
262     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
263       Return G_RET_STS_UNEXP_ERROR;
264     WHEN OTHERS THEN
265       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
266                            p_msg_name     => G_DB_ERROR,
267                            p_token1       => G_PROG_NAME_TOKEN,
268                            p_token1_value => l_api_name,
269                            p_token2       => 'SQLCODE',
270                            p_token2_value => sqlcode,
271                            p_token3       => 'SQLERRM',
272                            p_token3_value => sqlerrm);
273       Return G_RET_STS_UNEXP_ERROR;
274   END validate_term;
275 
276   ---------------------------------
277   -- FUNCTION validate_value
278   ---------------------------------
279   FUNCTION validate_value (p_value IN NUMBER, p_term IN NUMBER) Return Varchar2 IS
280     l_api_name            CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_value';
281   BEGIN
282     IF p_value IS NULL THEN
283       OKL_API.set_message(p_app_name      => G_APP_NAME,
284                           p_msg_name      => G_REQUIRED_VALUE,
285                           p_token1        => G_COL_NAME_TOKEN,
286                           p_token1_value  => 'value');
287       RAISE OKL_API.G_EXCEPTION_ERROR;
288     ELSIF p_value < 0 THEN
289       OKL_API.set_message(p_app_name      => G_APP_NAME,
290                           p_msg_name      => 'OKL_INVALID_VALUE',
291                           p_token1        => G_COL_NAME_TOKEN,
292                           p_token1_value  => 'Term ' || p_term );
293       RAISE OKL_API.G_EXCEPTION_ERROR;
294     END IF;
295     Return G_RET_STS_SUCCESS;
296   EXCEPTION
297     WHEN OKL_API.G_EXCEPTION_ERROR THEN
298       Return G_RET_STS_ERROR;
299     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
300       Return G_RET_STS_UNEXP_ERROR;
301     WHEN OTHERS THEN
302       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
303                            p_msg_name     => G_DB_ERROR,
304                            p_token1       => G_PROG_NAME_TOKEN,
305                            p_token1_value => l_api_name,
306                            p_token2       => 'SQLCODE',
307                            p_token2_value => sqlcode,
308                            p_token3       => 'SQLERRM',
309                            p_token3_value => sqlerrm);
310       Return G_RET_STS_UNEXP_ERROR;
311   END validate_value;
312 
313 
314  FUNCTION Validate_Attributes (
315  p_irv_rec IN okl_irv_rec
316  ) RETURN VARCHAR2 IS
317   l_return_status	VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
318   x_return_status	VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
319   l_api_name  CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_attributes';
320     BEGIN
321 
322     -- ***
323     -- item_resdl_value_id
324     -- ***
325     l_return_status := validate_item_resdl_value_id(p_irv_rec.item_resdl_value_id);
326 
327     IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
328       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
329     ELSIF (l_return_status = G_RET_STS_ERROR) THEN
330       RAISE OKL_API.G_EXCEPTION_ERROR;
331     END IF;
332 
333     -- ***
334     -- object_version_number
335     -- ***
336     l_return_status := validate_object_version_number(p_irv_rec.object_version_number);
337 
338     IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
339       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
340     ELSIF (l_return_status = G_RET_STS_ERROR) THEN
341       RAISE OKL_API.G_EXCEPTION_ERROR;
342     END IF;
343 
344     -- ***
345     -- item_residual_id
346     -- ***
347     l_return_status := validate_item_residual_id(p_irv_rec.item_residual_id);
348 
349     IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
350       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
351     ELSIF (l_return_status = G_RET_STS_ERROR) THEN
352       RAISE OKL_API.G_EXCEPTION_ERROR;
353     END IF;
354 
355     -- ***
356     -- item_resdl_version_id
357     -- ***
361       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
358     l_return_status := validate_item_resdl_version_id(p_irv_rec.item_resdl_version_id);
359 
360     IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
362     ELSIF (l_return_status = G_RET_STS_ERROR) THEN
363       RAISE OKL_API.G_EXCEPTION_ERROR;
364     END IF;
365 
366     -- ***
367     -- term
368     -- ***
369     l_return_status := validate_term(p_irv_rec.term_in_months);
370 
371     IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
372       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
373     ELSIF (l_return_status = G_RET_STS_ERROR) THEN
374       RAISE OKL_API.G_EXCEPTION_ERROR;
375     END IF;
376 
377     -- ***
378     -- value
379     -- ***
380     l_return_status := validate_value(p_irv_rec.residual_value, p_irv_rec.term_in_months);
381 
382     IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
383       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
384     ELSIF (l_return_status = G_RET_STS_ERROR) THEN
385       RAISE OKL_API.G_EXCEPTION_ERROR;
386     END IF;
387 
388 	RETURN (x_return_status);
389    EXCEPTION
390     WHEN OKL_API.G_EXCEPTION_ERROR THEN
391       RETURN  G_RET_STS_ERROR;
392     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
393       return G_RET_STS_UNEXP_ERROR;
394     WHEN OTHERS THEN
395       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
396                            p_msg_name     => G_DB_ERROR,
397                            p_token1       => G_PROG_NAME_TOKEN,
398                            p_token1_value => l_api_name,
399                            p_token2       => 'SQLCODE',
400                            p_token2_value => sqlcode,
401                            p_token3       => 'SQLERRM',
402                            p_token3_value => sqlerrm);
403       return G_RET_STS_UNEXP_ERROR;
404    END Validate_Attributes;
405 
406  FUNCTION Validate_Record (
407  p_irv_rec IN okl_irv_rec
408  ) RETURN VARCHAR2 IS
409   l_return_status	VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
410   x_return_status	VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
411     BEGIN
412   	  RETURN (x_return_status);
413    END Validate_Record;
414 
415  FUNCTION check_existence (
416  p_irv_rec IN okl_irv_rec
417  ) RETURN VARCHAR2 IS
418   l_return_status	VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
419   x_return_status	VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
420   l_temp_item_resdl_version_id     NUMBER := NULL;
421   l_api_name  CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'check_existence';
422 
423   CURSOR l_irv_csr IS
424         SELECT
425             item_resdl_value_id
426          FROM
427              OKL_FE_ITEM_RESDL_VALUES IRV
428          WHERE
429               IRV.ITEM_RESDL_VERSION_ID         = p_irv_rec.item_resdl_version_id
430           AND IRV.TERM_IN_MONTHS = p_irv_rec.term_in_months;
431     BEGIN
432       /* Check if a record is present for the same term */
433       OPEN l_irv_csr;
434        FETCH l_irv_csr INTO l_temp_item_resdl_version_id;
435        IF l_irv_csr%FOUND THEN
436          OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
437                               p_msg_name     => 'OKL_TRM_VAL_EXISTS');
438          RAISE OKL_API.G_EXCEPTION_ERROR;
439        END IF;
440       CLOSE l_irv_csr;
441 
442   	  RETURN (x_return_status);
443  EXCEPTION
444     WHEN OKL_API.G_EXCEPTION_ERROR THEN
445       RETURN G_RET_STS_ERROR;
446     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
447       RETURN G_RET_STS_UNEXP_ERROR;
448     WHEN OTHERS THEN
449       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
450                            p_msg_name     => G_DB_ERROR,
451                            p_token1       => G_PROG_NAME_TOKEN,
452                            p_token1_value => l_api_name,
453                            p_token2       => G_SQLCODE_TOKEN,
454                            p_token2_value => sqlcode,
455                            p_token3       => G_SQLERRM_TOKEN,
456                            p_token3_value => sqlerrm);
457       RETURN G_RET_STS_UNEXP_ERROR;
458    END check_existence;
459 
460 --------------------------------------------------------------------------------
461 -- Procedure insert_row
462 --------------------------------------------------------------------------------
463 PROCEDURE insert_row(
464 	 p_api_version			 IN NUMBER ,
465 	 p_init_msg_list		 IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
466 	 x_return_status		 OUT NOCOPY VARCHAR2,
467 	 x_msg_count			 OUT NOCOPY NUMBER,
468 	 x_msg_data			 OUT NOCOPY VARCHAR2,
469 	 p_irv_rec			 IN okl_irv_rec,
470 	 x_irv_rec			 OUT NOCOPY okl_irv_rec)IS
471 
472 l_api_version			CONSTANT NUMBER:=1;
473 l_api_name			CONSTANT VARCHAR2(30):='insert_row';
474 l_return_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
475 l_irv_rec			okl_irv_rec;
476 l_def_irv_rec			okl_irv_rec;
477 
478  FUNCTION fill_who_columns(
479  p_irv_rec	IN okl_irv_rec
480  )RETURN okl_irv_rec IS
481   l_irv_rec okl_irv_rec:=p_irv_rec;
482  BEGIN
483   l_irv_rec.CREATION_DATE := SYSDATE;
484   l_irv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
485   l_irv_rec.LAST_UPDATE_DATE := SYSDATE;
486   l_irv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
487   l_irv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
488   RETURN (l_irv_rec);
489  END fill_who_columns;
490 
491  FUNCTION Set_Attributes(
492 	p_irv_rec IN okl_irv_rec,
493 	x_irv_rec OUT NOCOPY okl_irv_rec
494  ) RETURN VARCHAR2 IS
495  l_return_status			VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
496 	BEGIN
497 		x_irv_rec := p_irv_rec;
498  RETURN (l_return_status);
499  END Set_Attributes;
500    BEGIN
501 	l_return_status := OKL_API.START_ACTIVITY(l_api_name,
502 							G_PKG_NAME,
506 							'_PVT',
503 							p_init_msg_list,
504 							l_api_version,
505 							p_api_version,
507 							x_return_status);
508 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
509 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
510 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
511 	 RAISE OKL_API.G_EXCEPTION_ERROR;
512 	END IF;
513 	l_irv_rec:=null_out_defaults(p_irv_rec);
514 	-- Set Primary key value
515 	l_irv_rec.item_resdl_value_id := get_seq_id;
516 	--Setting Item Attributes
517 	l_return_status:=Set_Attributes(l_irv_rec,l_def_irv_rec);
518 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
519 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
520 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
521 	 RAISE OKL_API.G_EXCEPTION_ERROR;
522 	END IF;
523 
524 	l_def_irv_rec := fill_who_columns(l_def_irv_rec);
525 
526 	l_return_status := Validate_Attributes(l_def_irv_rec);
527 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
528 	RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
529 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
530 	 RAISE OKL_API.G_EXCEPTION_ERROR;
531 	END IF;
532 	l_return_status := check_existence(l_def_irv_rec);
533 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
534 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
535 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
536 	 RAISE OKL_API.G_EXCEPTION_ERROR;
537 	END IF;
538 	l_return_status := Validate_Record(l_def_irv_rec);
539 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
540 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
541 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
542 	 RAISE OKL_API.G_EXCEPTION_ERROR;
543 	END IF;
544 	INSERT INTO OKL_FE_ITEM_RESDL_VALUES(
545 	   ITEM_RESDL_VALUE_ID,
546 	   OBJECT_VERSION_NUMBER,
547        ITEM_RESIDUAL_ID,
548 	   ITEM_RESDL_VERSION_ID,
549 	   TERM_IN_MONTHS,
550 	   RESIDUAL_VALUE,
551 	   CREATED_BY,
552 	   CREATION_DATE,
553 	   LAST_UPDATED_BY,
554 	   LAST_UPDATE_DATE,
555 	   LAST_UPDATE_LOGIN)
556 	VALUES (
557 	   l_def_irv_rec.ITEM_RESDL_VALUE_ID,
558 	   l_def_irv_rec.OBJECT_VERSION_NUMBER,
559 	   l_def_irv_rec.ITEM_RESIDUAL_ID,
560 	   l_def_irv_rec.ITEM_RESDL_VERSION_ID,
561 	   l_def_irv_rec.TERM_IN_MONTHS,
562 	   l_def_irv_rec.RESIDUAL_VALUE,
563 	   l_def_irv_rec.CREATED_BY,
564 	   l_def_irv_rec.CREATION_DATE,
565 	   l_def_irv_rec.LAST_UPDATED_BY,
566 	   l_def_irv_rec.LAST_UPDATE_DATE,
567 	   l_def_irv_rec.LAST_UPDATE_LOGIN);
568 
569 
570 	--Set OUT Values
571 	x_irv_rec:= l_def_irv_rec;
572         x_return_status := l_return_status;
573 	OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
574 
575 	EXCEPTION
576 	  WHEN G_EXCEPTION_HALT_VALIDATION then
577 	-- No action necessary. Validation can continue to next attribute/column
578 		 null;
579 
580 	  WHEN OKL_API.G_EXCEPTION_ERROR THEN
581 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
582 		(
583 			l_api_name,
584 			G_PKG_NAME,
585 			'OKL_API.G_RET_STS_ERROR',
586 			x_msg_count,
587 			x_msg_data,
588 			'_PVT'
589 		);
590 
591 	  WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
592 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
593 		(
594 			l_api_name,
595 			G_PKG_NAME,
596 			'OKL_API.G_RET_STS_UNEXP_ERROR',
597 			x_msg_count,
598 			x_msg_data,
599 			'_PVT'
600 		);
601 
602 	  WHEN OTHERS THEN
603 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
604 		(
605 			l_api_name,
606 			G_PKG_NAME,
607 			'OTHERS',
608 			x_msg_count,
609 			x_msg_data,
610 			'_PVT'
611 		);
612    END insert_row;
613 --------------------------------------------------------------------------------
614 -- Procedure insert_row_tbl
615 --------------------------------------------------------------------------------
616 PROCEDURE insert_row(
617 	 p_api_version			 IN NUMBER ,
618 	 p_init_msg_list		 IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
619 	 x_return_status		 OUT NOCOPY VARCHAR2,
620 	 x_msg_count			 OUT NOCOPY NUMBER,
621 	 x_msg_data			 OUT NOCOPY VARCHAR2,
622 	 p_irv_tbl			 IN okl_irv_tbl,
623 	 x_irv_tbl			 OUT NOCOPY okl_irv_tbl)IS
624 
625 	l_api_version			CONSTANT NUMBER:=1;
626 	l_api_name			CONSTANT VARCHAR2(30):='insert_row_tbl';
627 	l_return_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
628 	i			NUMBER:=0;
629 	l_overall_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
630    BEGIN
631 	OKL_API.init_msg_list(p_init_msg_list);
632 	-- Make sure PL/SQL table has records in it before passing
633 	IF (p_irv_tbl.COUNT > 0) THEN
634 	  i := p_irv_tbl.FIRST;
635 	 LOOP
636 	   insert_row (p_api_version			=> p_api_version,
637 		p_init_msg_list			=> OKL_API.G_FALSE,
638 		x_return_status			=> x_return_status,
639 		x_msg_count			=> x_msg_count,
640 		x_msg_data			=> x_msg_data,
641 		p_irv_rec			=> p_irv_tbl(i),
642 		x_irv_rec			=> x_irv_tbl(i));
643 	IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
644 	 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
645 	   l_overall_status := x_return_status;
646 	 END IF;
647 	END IF;
648 
649 	EXIT WHEN (i = p_irv_tbl.LAST);
650 	i := p_irv_tbl.NEXT(i);
651 	END LOOP;
652 	x_return_status := l_overall_status;
653 	END IF;
654 
655 	EXCEPTION
656 	  WHEN G_EXCEPTION_HALT_VALIDATION then
657 	-- No action necessary. Validation can continue to next attribute/column
658 		 null;
659 
660 	  WHEN OKL_API.G_EXCEPTION_ERROR THEN
661 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
662 		(
663 			l_api_name,
664 			G_PKG_NAME,
665 			'OKL_API.G_RET_STS_ERROR',
669 		);
666 			x_msg_count,
667 			x_msg_data,
668 			'_PVT'
670 
671 	  WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
672 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
673 		(
674 			l_api_name,
675 			G_PKG_NAME,
676 			'OKL_API.G_RET_STS_UNEXP_ERROR',
677 			x_msg_count,
678 			x_msg_data,
679 			'_PVT'
680 		);
681 
682 	  WHEN OTHERS THEN
683 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
684 		(
685 			l_api_name,
686 			G_PKG_NAME,
687 			'OTHERS',
688 			x_msg_count,
689 			x_msg_data,
690 			'_PVT'
691 		);
692    END insert_row;
693 
694 -----------------
695   -- lock_row (REC)
696   -----------------
697   PROCEDURE lock_row(
698     p_api_version                  IN NUMBER,
699     p_init_msg_list                IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
700     x_return_status                OUT NOCOPY VARCHAR2,
701     x_msg_count                    OUT NOCOPY NUMBER,
702     x_msg_data                     OUT NOCOPY VARCHAR2,
703     p_def_irv_rec                  IN  okl_irv_rec) IS
704 
705     l_api_name                     CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'lock_row (REC)';
706 
707     E_Resource_Busy                EXCEPTION;
708 
709     PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
710 
711     CURSOR lock_csr (p_def_irv_rec IN okl_irv_rec) IS
712     SELECT OBJECT_VERSION_NUMBER
713       FROM OKL_FE_ITEM_RESDL_VALUES
714      WHERE ITEM_RESDL_VALUE_ID = p_def_irv_rec.item_resdl_value_id
715        AND OBJECT_VERSION_NUMBER = p_def_irv_rec.object_version_number
716     FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
717 
718     CURSOR lchk_csr (p_def_irv_rec IN okl_irv_rec) IS
719     SELECT OBJECT_VERSION_NUMBER
720       FROM OKL_FE_ITEM_RESDL_VALUES
721      WHERE item_resdl_value_id = p_def_irv_rec.item_resdl_value_id;
722 
723     l_return_status                VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
724     l_object_version_number        OKL_ITM_CAT_RV_PRCS.OBJECT_VERSION_NUMBER%TYPE;
725     lc_object_version_number       OKL_ITM_CAT_RV_PRCS.OBJECT_VERSION_NUMBER%TYPE;
726     l_row_notfound                 BOOLEAN := FALSE;
727     lc_row_notfound                BOOLEAN := FALSE;
728 
729   BEGIN
730 
731     BEGIN
732       OPEN lock_csr(p_def_irv_rec);
733       FETCH lock_csr INTO l_object_version_number;
734       l_row_notfound := lock_csr%NOTFOUND;
735       CLOSE lock_csr;
736     EXCEPTION
737       WHEN E_Resource_Busy THEN
738         IF (lock_csr%ISOPEN) THEN
739           CLOSE lock_csr;
740         END IF;
741         OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
742         RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
743     END;
744 
745     IF ( l_row_notfound ) THEN
746       OPEN lchk_csr(p_def_irv_rec);
747       FETCH lchk_csr INTO lc_object_version_number;
748       lc_row_notfound := lchk_csr%NOTFOUND;
749       CLOSE lchk_csr;
750     END IF;
751     IF (lc_row_notfound) THEN
752       OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
753       RAISE OKL_API.G_EXCEPTION_ERROR;
754     ELSIF lc_object_version_number > p_def_irv_rec.object_version_number THEN
755       OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
756       RAISE OKL_API.G_EXCEPTION_ERROR;
757     ELSIF lc_object_version_number <> p_def_irv_rec.object_version_number THEN
758       OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
759       RAISE OKL_API.G_EXCEPTION_ERROR;
760     ELSIF lc_object_version_number = -1 THEN
761       OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
762       RAISE OKL_API.G_EXCEPTION_ERROR;
763     END IF;
764 
765     x_return_status := l_return_status;
766 
767   EXCEPTION
768 
769     WHEN OKL_API.G_EXCEPTION_ERROR THEN
770 
771       x_return_status := G_RET_STS_ERROR;
772 
773     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
774 
775       x_return_status := G_RET_STS_UNEXP_ERROR;
776 
777     WHEN OTHERS THEN
778 
779       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
780                            p_msg_name     => G_DB_ERROR,
781                            p_token1       => G_PROG_NAME_TOKEN,
782                            p_token1_value => l_api_name,
783                            p_token2       => G_SQLCODE_TOKEN,
784                            p_token2_value => sqlcode,
785                            p_token3       => G_SQLERRM_TOKEN,
786                            p_token3_value => sqlerrm);
787 
788       x_return_status := G_RET_STS_UNEXP_ERROR;
789 
790   END lock_row;
791 
792 
793   -----------------
794   -- lock_row (TBL)
795   -----------------
796   PROCEDURE lock_row(
797     p_api_version                  IN NUMBER,
798     p_init_msg_list                IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
799     x_return_status                OUT NOCOPY VARCHAR2,
800     x_msg_count                    OUT NOCOPY NUMBER,
801     x_msg_data                     OUT NOCOPY VARCHAR2,
802     okl_irv_tbl                     IN okl_irv_tbl) IS
803 
804     l_api_name                     CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'lock_row (TBL)';
805     l_return_status                VARCHAR2(1)           := G_RET_STS_SUCCESS;
806     i                              BINARY_INTEGER;
807 
808   BEGIN
809 
810     IF (okl_irv_tbl.COUNT > 0) THEN
811 
812       i := okl_irv_tbl.FIRST;
813 
814       LOOP
815 
816         IF okl_irv_tbl.EXISTS(i) THEN
817 
818           lock_row (p_api_version                  => G_API_VERSION,
819                     p_init_msg_list                => G_FALSE,
820                     x_return_status                => l_return_status,
824 
821                     x_msg_count                    => x_msg_count,
822                     x_msg_data                     => x_msg_data,
823                     p_def_irv_rec                     => okl_irv_tbl(i));
825           IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
826             RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
827           ELSIF l_return_status = G_RET_STS_ERROR THEN
828             RAISE OKL_API.G_EXCEPTION_ERROR;
829           END IF;
830 
831           EXIT WHEN (i = okl_irv_tbl.LAST);
832           i := okl_irv_tbl.NEXT(i);
833 
834         END IF;
835 
836       END LOOP;
837 
838     END IF;
839 
840     x_return_status := l_return_status;
841 
842   EXCEPTION
843 
844     WHEN OKL_API.G_EXCEPTION_ERROR THEN
845 
846       x_return_status := G_RET_STS_ERROR;
847 
848     WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
849 
850       x_return_status := G_RET_STS_UNEXP_ERROR;
851 
852     WHEN OTHERS THEN
853 
854       OKL_API.SET_MESSAGE (p_app_name     => G_APP_NAME,
855                            p_msg_name     => G_DB_ERROR,
856                            p_token1       => G_PROG_NAME_TOKEN,
857                            p_token1_value => l_api_name,
858                            p_token2       => G_SQLCODE_TOKEN,
859                            p_token2_value => sqlcode,
860                            p_token3       => G_SQLERRM_TOKEN,
861                            p_token3_value => sqlerrm);
862 
863       x_return_status := G_RET_STS_UNEXP_ERROR;
864 
865   END lock_row;
866 
867 
868 --------------------------------------------------------------------------------
869 -- Procedure update_row
870 --------------------------------------------------------------------------------
871 PROCEDURE update_row(
872 	 p_api_version			 IN NUMBER ,
873 	 p_init_msg_list		 IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
874 	 x_return_status		 OUT NOCOPY VARCHAR2,
875 	 x_msg_count			 OUT NOCOPY NUMBER,
876 	 x_msg_data			 OUT NOCOPY VARCHAR2,
877 	 p_irv_rec			 IN okl_irv_rec,
878 	 x_irv_rec			 OUT NOCOPY okl_irv_rec)IS
879 	l_api_version			CONSTANT NUMBER:=1;
880 	l_api_name			CONSTANT VARCHAR2(30):='update_row';
881 	l_return_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
882 l_irv_rec			okl_irv_rec:=p_irv_rec;
883 lx_irv_rec 		    okl_irv_rec;
884 l_def_irv_rec			okl_irv_rec;
885 
886  FUNCTION fill_who_columns(
887  p_irv_rec	IN okl_irv_rec
888  )RETURN okl_irv_rec IS
889 l_irv_rec 	okl_irv_rec:=p_irv_rec;
890  BEGIN
891    l_irv_rec .LAST_UPDATE_DATE := SYSDATE;
892   l_irv_rec .LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
893   l_irv_rec .LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
894   RETURN (l_irv_rec );
895  END fill_who_columns;
896  FUNCTION populate_new_record(
897 	p_irv_rec	IN okl_irv_rec,
898 	x_irv_rec	OUT NOCOPY okl_irv_rec
899 	)RETURN VARCHAR2 is
900 	l_irv_rec	okl_irv_rec;
901 	l_row_notfound	BOOLEAN:=TRUE;
902 	l_return_status	VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
903 	 BEGIN
904 
905 	x_irv_rec := p_irv_rec;
906 	--Get current database values
907 	l_irv_rec := get_rec(p_irv_rec,l_row_notfound);
908 	IF(l_row_notfound) THEN
909 	 l_return_status:= OKL_API.G_RET_STS_UNEXP_ERROR;
910   END IF;
911 
912 	IF (x_irv_rec.ITEM_RESDL_VALUE_ID IS NULL)
913 	THEN
914 	 x_irv_rec.ITEM_RESDL_VALUE_ID:=l_irv_rec.ITEM_RESDL_VALUE_ID;
915 	END IF;
916 	IF (x_irv_rec.OBJECT_VERSION_NUMBER IS NULL)
917 	THEN
918 	 x_irv_rec.OBJECT_VERSION_NUMBER:=l_irv_rec.OBJECT_VERSION_NUMBER;
919 	END IF;
920 	IF (x_irv_rec.ITEM_RESIDUAL_ID IS NULL)
921 	THEN
922 	 x_irv_rec.ITEM_RESIDUAL_ID:=l_irv_rec.ITEM_RESIDUAL_ID;
923 	END IF;
924 
925 	IF (x_irv_rec.ITEM_RESDL_VERSION_ID IS NULL)
926 	THEN
927 	 x_irv_rec.ITEM_RESDL_VERSION_ID:=l_irv_rec.ITEM_RESDL_VERSION_ID;
928 	END IF;
929 	IF (x_irv_rec.TERM_IN_MONTHS IS NULL)
930 	THEN
931 	 x_irv_rec.TERM_IN_MONTHS:=l_irv_rec.TERM_IN_MONTHS;
932 	END IF;
933 	IF (x_irv_rec.RESIDUAL_VALUE IS NULL)
934 	THEN
935 	 x_irv_rec.RESIDUAL_VALUE:=l_irv_rec.RESIDUAL_VALUE;
936 	END IF;
937 	IF (x_irv_rec.CREATED_BY IS NULL)
938 	THEN
939 	 x_irv_rec.CREATED_BY:=l_irv_rec.CREATED_BY;
940 	END IF;
941 	IF (x_irv_rec.CREATION_DATE IS NULL)
942 	THEN
943 	 x_irv_rec.CREATION_DATE:=l_irv_rec.CREATION_DATE;
944 	END IF;
945 	IF (x_irv_rec.LAST_UPDATED_BY IS NULL)
946 	THEN
947 	 x_irv_rec.LAST_UPDATED_BY:=l_irv_rec.LAST_UPDATED_BY;
948 	END IF;
949 	IF (x_irv_rec.LAST_UPDATE_DATE IS NULL)
950 	THEN
951 	 x_irv_rec.LAST_UPDATE_DATE:=l_irv_rec.LAST_UPDATE_DATE;
952 	END IF;
953 	IF (x_irv_rec.LAST_UPDATE_LOGIN IS NULL)
954 	THEN
955 	 x_irv_rec.LAST_UPDATE_LOGIN:=l_irv_rec.LAST_UPDATE_LOGIN;
956 	END IF;
957 	RETURN(l_return_status);
958    END populate_new_record;
959 
960  FUNCTION Set_Attributes(
961 	p_irv_rec IN okl_irv_rec,
962 	x_irv_rec OUT NOCOPY okl_irv_rec
963  ) RETURN VARCHAR2 IS
964  l_return_status			VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
965 	BEGIN
966 		x_irv_rec := p_irv_rec;
967  RETURN (l_return_status);
968  END Set_Attributes;
969    BEGIN
970 	l_return_status := OKL_API.START_ACTIVITY(l_api_name,
971 							G_PKG_NAME,
972 							p_init_msg_list,
973 							l_api_version,
974 							p_api_version,
975 							'_PVT',
976 							x_return_status);
977 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
978 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
980 	 RAISE OKL_API.G_EXCEPTION_ERROR;
981 	END IF;
982 	--Setting Item Attributes
986 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
983 	l_return_status:=Set_Attributes(l_irv_rec,lx_irv_rec);
984 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
985 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
987 	 RAISE OKL_API.G_EXCEPTION_ERROR;
988 	END IF;
989 	l_return_status := populate_new_record(lx_irv_rec,l_def_irv_rec);
990 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
991 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
992 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
993 	 RAISE OKL_API.G_EXCEPTION_ERROR;
994 	END IF;
995   	l_def_irv_rec:=null_out_defaults(l_def_irv_rec);
996 
997 	l_def_irv_rec := fill_who_columns(l_def_irv_rec);
998 
999 	l_return_status := Validate_Attributes(l_def_irv_rec);
1000 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1001 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1002 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1003 	 RAISE OKL_API.G_EXCEPTION_ERROR;
1004 	END IF;
1005 	l_return_status := Validate_Record(l_def_irv_rec);
1006 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1007 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1009 	 RAISE OKL_API.G_EXCEPTION_ERROR;
1010 	END IF;
1011 	-- Lock the row before updating
1012     lock_row(p_api_version    => G_API_VERSION,
1013              p_init_msg_list  => G_FALSE,
1014              x_return_status  => l_return_status,
1015              x_msg_count      => x_msg_count,
1016              x_msg_data       => x_msg_data,
1017              p_def_irv_rec    => l_def_irv_rec);
1018 
1019     IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1020       RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1021     ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1022       RAISE OKL_API.G_EXCEPTION_ERROR;
1023     END IF;
1024 UPDATE OKL_FE_ITEM_RESDL_VALUES
1025  SET
1026 	ITEM_RESDL_VALUE_ID= l_def_irv_rec.ITEM_RESDL_VALUE_ID,
1027 	OBJECT_VERSION_NUMBER=l_def_irv_rec.OBJECT_VERSION_NUMBER+1,
1028 	ITEM_RESIDUAL_ID= l_def_irv_rec.ITEM_RESIDUAL_ID,
1029 	ITEM_RESDL_VERSION_ID= l_def_irv_rec.ITEM_RESDL_VERSION_ID,
1030 	TERM_IN_MONTHS= l_def_irv_rec.TERM_IN_MONTHS,
1031 	RESIDUAL_VALUE= l_def_irv_rec.RESIDUAL_VALUE,
1032 	CREATED_BY= l_def_irv_rec.CREATED_BY,
1033 	CREATION_DATE= l_def_irv_rec.CREATION_DATE,
1034 	LAST_UPDATED_BY= l_def_irv_rec.LAST_UPDATED_BY,
1035 	LAST_UPDATE_DATE= l_def_irv_rec.LAST_UPDATE_DATE,
1036 	LAST_UPDATE_LOGIN= l_def_irv_rec.LAST_UPDATE_LOGIN
1037  WHERE ITEM_RESDL_VALUE_ID = l_def_irv_rec.item_resdl_value_id;
1038 	--Set OUT Values
1039 	x_irv_rec:= l_def_irv_rec;
1040         x_return_status := l_return_status;
1041 	OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1042 	EXCEPTION
1043 	  WHEN G_EXCEPTION_HALT_VALIDATION then
1044 	-- No action necessary. Validation can continue to next attribute/column
1045 		 null;
1046 
1047 	  WHEN OKL_API.G_EXCEPTION_ERROR THEN
1048 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1049 		(
1050 			l_api_name,
1051 			G_PKG_NAME,
1052 			'OKL_API.G_RET_STS_ERROR',
1053 			x_msg_count,
1054 			x_msg_data,
1055 			'_PVT'
1056 		);
1057 
1058 	  WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1059 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1060 		(
1061 			l_api_name,
1062 			G_PKG_NAME,
1063 			'OKL_API.G_RET_STS_UNEXP_ERROR',
1064 			x_msg_count,
1065 			x_msg_data,
1066 			'_PVT'
1067 		);
1068 
1069 	  WHEN OTHERS THEN
1070 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1071 		(
1072 			l_api_name,
1073 			G_PKG_NAME,
1074 			'OTHERS',
1075 			x_msg_count,
1076 			x_msg_data,
1077 			'_PVT'
1078 		);
1079    END update_row;
1080 --------------------------------------------------------------------------------
1081 -- Procedure insert_row_tbl
1082 --------------------------------------------------------------------------------
1083 PROCEDURE update_row(
1084 	 p_api_version			 IN NUMBER ,
1085 	 p_init_msg_list		 IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1086 	 x_return_status		 OUT NOCOPY VARCHAR2,
1087 	 x_msg_count			 OUT NOCOPY NUMBER,
1088 	 x_msg_data			 OUT NOCOPY VARCHAR2,
1089 	 p_irv_tbl			 IN okl_irv_tbl,
1090 	 x_irv_tbl			 OUT NOCOPY okl_irv_tbl)IS
1091 	l_api_version			CONSTANT NUMBER:=1;
1092 	l_api_name			CONSTANT VARCHAR2(30):='update_tbl';
1093 	l_return_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1094 	i			NUMBER:=0;
1095 	l_overall_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1096    BEGIN
1097 	OKL_API.init_msg_list(p_init_msg_list);
1098 	-- Make sure PL/SQL table has records in it before passing
1099 	IF (p_irv_tbl.COUNT > 0) THEN
1100 	  i := p_irv_tbl.FIRST;
1101 	 LOOP
1102 	   update_row (p_api_version			=> p_api_version,
1103 		p_init_msg_list			=> OKL_API.G_FALSE,
1104 		x_return_status			=> x_return_status,
1105 		x_msg_count			=> x_msg_count,
1106 		x_msg_data			=> x_msg_data,
1107 		p_irv_rec			=> p_irv_tbl(i),
1108 		x_irv_rec			=> x_irv_tbl(i));
1109 	IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1110 	 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1111 	   l_overall_status := x_return_status;
1112 	 END IF;
1113 	END IF;
1114 
1115 	EXIT WHEN (i = p_irv_tbl.LAST);
1116 	i := p_irv_tbl.NEXT(i);
1117 	END LOOP;
1118 	x_return_status := l_overall_status;
1119 	END IF;
1120 
1121 	EXCEPTION
1122 	  WHEN G_EXCEPTION_HALT_VALIDATION then
1123 	-- No action necessary. Validation can continue to next attribute/column
1124 		 null;
1125 
1126 	  WHEN OKL_API.G_EXCEPTION_ERROR THEN
1127 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1128 		(
1129 			l_api_name,
1130 			G_PKG_NAME,
1131 			'OKL_API.G_RET_STS_ERROR',
1132 			x_msg_count,
1133 			x_msg_data,
1134 			'_PVT'
1135 		);
1136 
1137 	  WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1138 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1139 		(
1140 			l_api_name,
1141 			G_PKG_NAME,
1142 			'OKL_API.G_RET_STS_UNEXP_ERROR',
1143 			x_msg_count,
1144 			x_msg_data,
1145 			'_PVT'
1146 		);
1147 
1148 	  WHEN OTHERS THEN
1149 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1150 		(
1151 			l_api_name,
1152 			G_PKG_NAME,
1153 			'OTHERS',
1154 			x_msg_count,
1155 			x_msg_data,
1156 			'_PVT'
1157 		);
1158    END update_row;
1159 
1160 --------------------------------------------------------------------------------
1161 -- Procedure delete_row
1162 --------------------------------------------------------------------------------
1163 PROCEDURE delete_row(
1164 	 p_api_version			 IN NUMBER ,
1165 	 p_init_msg_list		 IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1166 	 x_return_status		 OUT NOCOPY VARCHAR2,
1167 	 x_msg_count			 OUT NOCOPY NUMBER,
1168 	 x_msg_data			 OUT NOCOPY VARCHAR2,
1169 	 p_irv_rec			 IN okl_irv_rec)IS
1170 	l_api_version			CONSTANT NUMBER:=1;
1171 	l_api_name			CONSTANT VARCHAR2(30):='v_delete_row';
1172 	l_return_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1173         l_irv_rec			okl_irv_rec:=p_irv_rec;
1174 
1175 
1176    BEGIN
1177 	l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1178 							G_PKG_NAME,
1179 							p_init_msg_list,
1180 							l_api_version,
1181 							p_api_version,
1182 							'_PVT',
1183 							x_return_status);
1184 	IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1185 	 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1186 	ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1187 	 RAISE OKL_API.G_EXCEPTION_ERROR;
1188 	END IF;
1189 
1190 	DELETE FROM OKL_FE_ITEM_RESDL_VALUES
1191 	WHERE ITEM_RESDL_VALUE_ID=l_irv_rec.item_resdl_value_id;
1192 
1193 
1194 	OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1195 
1196 	EXCEPTION
1197 	  WHEN G_EXCEPTION_HALT_VALIDATION then
1198 	-- No action necessary. Validation can continue to next attribute/column
1199 		 null;
1200 
1201 	  WHEN OKL_API.G_EXCEPTION_ERROR THEN
1202 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1203 		(
1204 			l_api_name,
1205 			G_PKG_NAME,
1206 			'OKL_API.G_RET_STS_ERROR',
1207 			x_msg_count,
1208 			x_msg_data,
1209 			'_PVT'
1210 		);
1211 
1212 	  WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1213 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1214 		(
1215 			l_api_name,
1216 			G_PKG_NAME,
1217 			'OKL_API.G_RET_STS_UNEXP_ERROR',
1218 			x_msg_count,
1219 			x_msg_data,
1220 			'_PVT'
1221 		);
1222 
1223 	  WHEN OTHERS THEN
1224 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1225 		(
1226 			l_api_name,
1227 			G_PKG_NAME,
1228 			'OTHERS',
1229 			x_msg_count,
1230 			x_msg_data,
1231 			'_PVT'
1232 		);
1233    END delete_row;
1234 --------------------------------------------------------------------------------
1235 -- Procedure delete_row_tbl
1236 --------------------------------------------------------------------------------
1237 PROCEDURE delete_row(
1238 	 p_api_version			 IN NUMBER ,
1239 	 p_init_msg_list		 IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1240 	 x_return_status		 OUT NOCOPY VARCHAR2,
1241 	 x_msg_count			 OUT NOCOPY NUMBER,
1242 	 x_msg_data			 OUT NOCOPY VARCHAR2,
1243 	 p_irv_tbl			 IN okl_irv_tbl)IS
1244 	l_api_version			CONSTANT NUMBER:=1;
1245 	l_api_name			CONSTANT VARCHAR2(30):='v_delete_row';
1246 	l_return_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1247 	i			NUMBER:=0;
1248 	l_overall_status			VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1249    BEGIN
1250 	OKL_API.init_msg_list(p_init_msg_list);
1251 	-- Make sure PL/SQL table has records in it before passing
1252 	IF (p_irv_tbl.COUNT > 0) THEN
1253 	  i := p_irv_tbl.FIRST;
1254 	 LOOP
1255 	   delete_row (p_api_version			=> p_api_version,
1256 		p_init_msg_list			=> OKL_API.G_FALSE,
1257 		x_return_status			=> x_return_status,
1258 		x_msg_count			=> x_msg_count,
1259 		x_msg_data			=> x_msg_data,
1260 		p_irv_rec			=> p_irv_tbl(i));
1261 	IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1262 	 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1263 	   l_overall_status := x_return_status;
1264 	 END IF;
1265 	END IF;
1266 
1267 	EXIT WHEN (i = p_irv_tbl.LAST);
1268 	i := p_irv_tbl.NEXT(i);
1269 	END LOOP;
1270 	x_return_status := l_overall_status;
1271 	END IF;
1272 
1273 	EXCEPTION
1274 	  WHEN G_EXCEPTION_HALT_VALIDATION then
1275 	-- No action necessary. Validation can continue to next attribute/column
1276 		 null;
1277 
1278 	  WHEN OKL_API.G_EXCEPTION_ERROR THEN
1279 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1280 		(
1281 			l_api_name,
1282 			G_PKG_NAME,
1283 			'OKL_API.G_RET_STS_ERROR',
1284 			x_msg_count,
1285 			x_msg_data,
1286 			'_PVT'
1287 		);
1288 
1289 	  WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1290 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1291 		(
1292 			l_api_name,
1293 			G_PKG_NAME,
1294 			'OKL_API.G_RET_STS_UNEXP_ERROR',
1295 			x_msg_count,
1296 			x_msg_data,
1297 			'_PVT'
1298 		);
1299 
1300 	  WHEN OTHERS THEN
1301 		x_return_status := OKL_API.HANDLE_EXCEPTIONS
1302 		(
1303 			l_api_name,
1304 			G_PKG_NAME,
1305 			'OTHERS',
1306 			x_msg_count,
1307 			x_msg_data,
1308 			'_PVT'
1309 		);
1310    END delete_row;
1311 END OKL_IRV_PVT;