[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;