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