[Home] [Help]
PACKAGE BODY: APPS.OKL_SRM_PVT
Source
1 PACKAGE BODY OKL_SRM_PVT AS
2 /* $Header: OKLSSRMB.pls 115.5 2002/12/18 13:09:41 kjinger noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 null;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 null;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 null;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKL_SIF_RET_ERRORS
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_srm_rec IN srm_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN srm_rec_type IS
42 CURSOR srm_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 SIR_ID,
46 ERROR_CODE,
47 ERROR_MESSAGE,
48 TAG_NAME,
49 TAG_ATTRIBUTE_NAME,
50 TAG_ATTRIBUTE_VALUE,
51 DESCRIPTION,
52 STREAM_INTERFACE_ATTRIBUTE01,
53 STREAM_INTERFACE_ATTRIBUTE02,
54 STREAM_INTERFACE_ATTRIBUTE03,
55 STREAM_INTERFACE_ATTRIBUTE04,
56 STREAM_INTERFACE_ATTRIBUTE05,
57 STREAM_INTERFACE_ATTRIBUTE06,
58 STREAM_INTERFACE_ATTRIBUTE07,
59 STREAM_INTERFACE_ATTRIBUTE08,
60 STREAM_INTERFACE_ATTRIBUTE09,
61 STREAM_INTERFACE_ATTRIBUTE10,
62 STREAM_INTERFACE_ATTRIBUTE11,
63 STREAM_INTERFACE_ATTRIBUTE12,
64 STREAM_INTERFACE_ATTRIBUTE13,
65 STREAM_INTERFACE_ATTRIBUTE14,
66 STREAM_INTERFACE_ATTRIBUTE15,
67 OBJECT_VERSION_NUMBER,
68 CREATED_BY,
69 LAST_UPDATED_BY,
70 CREATION_DATE,
71 LAST_UPDATE_DATE,
72 LAST_UPDATE_LOGIN
73 FROM Okl_Sif_Ret_Errors
74 WHERE okl_sif_ret_errors.id = p_id;
75 l_srm_pk srm_pk_csr%ROWTYPE;
76 l_srm_rec srm_rec_type;
77 BEGIN
78 x_no_data_found := TRUE;
79 -- Get current database values
80 OPEN srm_pk_csr (p_srm_rec.id);
81 FETCH srm_pk_csr INTO
82 l_srm_rec.ID,
83 l_srm_rec.SIR_ID,
84 l_srm_rec.ERROR_CODE,
85 l_srm_rec.ERROR_MESSAGE,
86 l_srm_rec.TAG_NAME,
87 l_srm_rec.TAG_ATTRIBUTE_NAME,
88 l_srm_rec.TAG_ATTRIBUTE_VALUE,
89 l_srm_rec.DESCRIPTION,
90 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE01,
91 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE02,
92 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE03,
93 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE04,
94 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE05,
95 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE06,
96 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE07,
97 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE08,
98 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE09,
99 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE10,
100 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE11,
101 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE12,
102 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE13,
103 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE14,
104 l_srm_rec.STREAM_INTERFACE_ATTRIBUTE15,
105 l_srm_rec.OBJECT_VERSION_NUMBER,
106 l_srm_rec.CREATED_BY,
107 l_srm_rec.LAST_UPDATED_BY,
108 l_srm_rec.CREATION_DATE,
109 l_srm_rec.LAST_UPDATE_DATE,
110 l_srm_rec.LAST_UPDATE_LOGIN;
111 x_no_data_found := srm_pk_csr%NOTFOUND;
112 CLOSE srm_pk_csr;
113 RETURN(l_srm_rec);
114 END get_rec;
115
116 FUNCTION get_rec (
117 p_srm_rec IN srm_rec_type
118 ) RETURN srm_rec_type IS
119 l_row_notfound BOOLEAN := TRUE;
120 BEGIN
121 RETURN(get_rec(p_srm_rec, l_row_notfound));
122 END get_rec;
123 ---------------------------------------------------------------------------
124 -- FUNCTION get_rec for: OKL_SIF_RET_ERRORS_V
125 ---------------------------------------------------------------------------
126 FUNCTION get_rec (
127 p_srmv_rec IN srmv_rec_type,
128 x_no_data_found OUT NOCOPY BOOLEAN
129 ) RETURN srmv_rec_type IS
130 CURSOR srmv_pk_csr (p_id IN NUMBER) IS
131 SELECT
132 ID,
133 SIR_ID,
134 ERROR_CODE,
135 ERROR_MESSAGE,
136 TAG_NAME,
137 TAG_ATTRIBUTE_NAME,
138 TAG_ATTRIBUTE_VALUE,
139 DESCRIPTION,
140 STREAM_INTERFACE_ATTRIBUTE01,
141 STREAM_INTERFACE_ATTRIBUTE02,
142 STREAM_INTERFACE_ATTRIBUTE03,
143 STREAM_INTERFACE_ATTRIBUTE04,
144 STREAM_INTERFACE_ATTRIBUTE05,
145 STREAM_INTERFACE_ATTRIBUTE06,
146 STREAM_INTERFACE_ATTRIBUTE07,
147 STREAM_INTERFACE_ATTRIBUTE08,
148 STREAM_INTERFACE_ATTRIBUTE09,
149 STREAM_INTERFACE_ATTRIBUTE10,
150 STREAM_INTERFACE_ATTRIBUTE11,
151 STREAM_INTERFACE_ATTRIBUTE12,
152 STREAM_INTERFACE_ATTRIBUTE13,
153 STREAM_INTERFACE_ATTRIBUTE14,
154 STREAM_INTERFACE_ATTRIBUTE15,
155 OBJECT_VERSION_NUMBER,
156 CREATED_BY,
157 LAST_UPDATED_BY,
158 CREATION_DATE,
159 LAST_UPDATE_DATE,
160 LAST_UPDATE_LOGIN
161 FROM OKL_SIF_RET_ERRORS_V
162 WHERE OKL_SIF_RET_ERRORS_V.id = p_id;
163 l_srmv_pk srmv_pk_csr%ROWTYPE;
164 l_srmv_rec srmv_rec_type;
165 BEGIN
166 x_no_data_found := TRUE;
167 -- Get current database values
168 OPEN srmv_pk_csr (p_srmv_rec.id);
169 FETCH srmv_pk_csr INTO
170 l_srmv_rec.ID,
171 l_srmv_rec.SIR_ID,
172 l_srmv_rec.ERROR_CODE,
173 l_srmv_rec.ERROR_MESSAGE,
174 l_srmv_rec.TAG_NAME,
175 l_srmv_rec.TAG_ATTRIBUTE_NAME,
176 l_srmv_rec.TAG_ATTRIBUTE_VALUE,
177 l_srmv_rec.DESCRIPTION,
178 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE01,
179 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE02,
180 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE03,
181 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE04,
182 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE05,
183 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE06,
184 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE07,
185 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE08,
186 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE09,
187 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE10,
188 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE11,
189 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE12,
190 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE13,
191 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE14,
192 l_srmv_rec.STREAM_INTERFACE_ATTRIBUTE15,
193 l_srmv_rec.OBJECT_VERSION_NUMBER,
194 l_srmv_rec.CREATED_BY,
195 l_srmv_rec.LAST_UPDATED_BY,
196 l_srmv_rec.CREATION_DATE,
197 l_srmv_rec.LAST_UPDATE_DATE,
198 l_srmv_rec.LAST_UPDATE_LOGIN;
199 x_no_data_found := srmv_pk_csr%NOTFOUND;
200 CLOSE srmv_pk_csr;
201 RETURN(l_srmv_rec);
202 END get_rec;
203
204 FUNCTION get_rec (
205 p_srmv_rec IN srmv_rec_type
206 ) RETURN srmv_rec_type IS
207 l_row_notfound BOOLEAN := TRUE;
208 BEGIN
209 RETURN(get_rec(p_srmv_rec, l_row_notfound));
210 END get_rec;
211
212 ----------------------------------------------------------
213 -- FUNCTION null_out_defaults for: OKL_SIF_RET_ERRORS_V --
214 ----------------------------------------------------------
215 FUNCTION null_out_defaults (
216 p_srmv_rec IN srmv_rec_type
217 ) RETURN srmv_rec_type IS
218 l_srmv_rec srmv_rec_type := p_srmv_rec;
219 BEGIN
220 IF (l_srmv_rec.id = OKC_API.G_MISS_NUM) THEN
221 l_srmv_rec.id := NULL;
222 END IF;
223 IF (l_srmv_rec.error_code = OKC_API.G_MISS_CHAR) THEN
224 l_srmv_rec.error_code := NULL;
225 END IF;
226 IF (l_srmv_rec.description = OKC_API.G_MISS_CHAR) THEN
227 l_srmv_rec.description := NULL;
228 END IF;
229 IF (l_srmv_rec.tag_attribute_name = OKC_API.G_MISS_CHAR) THEN
230 l_srmv_rec.tag_attribute_name := NULL;
231 END IF;
232 IF (l_srmv_rec.tag_name = OKC_API.G_MISS_CHAR) THEN
233 l_srmv_rec.tag_name := NULL;
234 END IF;
235 IF (l_srmv_rec.sir_id = OKC_API.G_MISS_NUM) THEN
236 l_srmv_rec.sir_id := NULL;
237 END IF;
238 IF (l_srmv_rec.error_message = OKC_API.G_MISS_CHAR) THEN
239 l_srmv_rec.error_message := NULL;
240 END IF;
241 IF (l_srmv_rec.tag_attribute_value = OKC_API.G_MISS_CHAR) THEN
242 l_srmv_rec.tag_attribute_value := NULL;
243 END IF;
244 IF (l_srmv_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR) THEN
245 l_srmv_rec.stream_interface_attribute01 := NULL;
246 END IF;
247 IF (l_srmv_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR) THEN
248 l_srmv_rec.stream_interface_attribute02 := NULL;
249 END IF;
250 IF (l_srmv_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR) THEN
251 l_srmv_rec.stream_interface_attribute03 := NULL;
252 END IF;
253 IF (l_srmv_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR) THEN
254 l_srmv_rec.stream_interface_attribute04 := NULL;
255 END IF;
256 IF (l_srmv_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR) THEN
257 l_srmv_rec.stream_interface_attribute05 := NULL;
258 END IF;
259 IF (l_srmv_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR) THEN
260 l_srmv_rec.stream_interface_attribute06 := NULL;
261 END IF;
262 IF (l_srmv_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR) THEN
263 l_srmv_rec.stream_interface_attribute07 := NULL;
264 END IF;
265 IF (l_srmv_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR) THEN
266 l_srmv_rec.stream_interface_attribute08 := NULL;
267 END IF;
268 IF (l_srmv_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR) THEN
269 l_srmv_rec.stream_interface_attribute09 := NULL;
270 END IF;
271 IF (l_srmv_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR) THEN
272 l_srmv_rec.stream_interface_attribute10 := NULL;
273 END IF;
274 IF (l_srmv_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR) THEN
275 l_srmv_rec.stream_interface_attribute11 := NULL;
276 END IF;
277 IF (l_srmv_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR) THEN
278 l_srmv_rec.stream_interface_attribute12 := NULL;
279 END IF;
280 IF (l_srmv_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR) THEN
281 l_srmv_rec.stream_interface_attribute13 := NULL;
282 END IF;
283 IF (l_srmv_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR) THEN
284 l_srmv_rec.stream_interface_attribute14 := NULL;
285 END IF;
286 IF (l_srmv_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR) THEN
287 l_srmv_rec.stream_interface_attribute15 := NULL;
288 END IF;
289 IF (l_srmv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
290 l_srmv_rec.object_version_number := NULL;
291 END IF;
292 IF (l_srmv_rec.created_by = OKC_API.G_MISS_NUM) THEN
293 l_srmv_rec.created_by := NULL;
294 END IF;
295 IF (l_srmv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
296 l_srmv_rec.last_updated_by := NULL;
297 END IF;
298 IF (l_srmv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
299 l_srmv_rec.creation_date := NULL;
300 END IF;
301 IF (l_srmv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
302 l_srmv_rec.last_update_date := NULL;
303 END IF;
304 IF (l_srmv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
305 l_srmv_rec.last_update_login := NULL;
306 END IF;
307 RETURN(l_srmv_rec);
308 END null_out_defaults;
309
310 -- START CHANGE AKJAIN
311 -- commented the tapi generated code in favour of hand coded validate methods
312 /*
313 ---------------------------------------------------------------------------
314 -- PROCEDURE Validate_Attributes
315 ---------------------------------------------------------------------------
316 --------------------------------------------------
317 -- Validate_Attributes for:OKL_SIF_RET_ERRORS_V --
318 --------------------------------------------------
319 FUNCTION Validate_Attributes (
320 p_srmv_rec IN srmv_rec_type
321 ) RETURN VARCHAR2 IS
322 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
323 BEGIN
324 IF p_srmv_rec.id = OKC_API.G_MISS_NUM OR
325 p_srmv_rec.id IS NULL
326 THEN
327 OKC_API.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
328 l_return_status := OKC_API.G_RET_STS_ERROR;
329 ELSIF p_srmv_rec.sir_id = OKC_API.G_MISS_NUM OR
330 p_srmv_rec.sir_id IS NULL
331 THEN
332 OKC_API.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'sir_id');
333 l_return_status := OKC_API.G_RET_STS_ERROR;
334 END IF;
335 RETURN(l_return_status);
336 END Validate_Attributes;
337
338 */
339
340
341 --------------------------------------------------------------------------
342 -- PROCEDURE Validate_Id
343 --------------------------------------------------------------------------
344 -- Start of comments
345 --
346 -- Procedure Name : Validate_Id
347 -- Description :
348 -- Business Rules :
349 -- Parameters :
350 -- Version : 1.0
351 -- End of comments
352 ---------------------------------------------------------------------------
353
354 PROCEDURE Validate_Id(p_srmv_rec IN srmv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
355
356 IS
357
358 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
359
360 BEGIN
361 -- initialize return status
362 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
363 -- check for data before processing
364 IF (p_srmv_rec.id = Okc_Api.G_MISS_NUM) OR
365 (p_srmv_rec.id IS NULL) THEN
366
367 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
368 ,p_msg_name => g_required_value
369 ,p_token1 => g_col_name_token
370 ,p_token1_value => 'id');
371 x_return_status := Okc_Api.G_RET_STS_ERROR;
372 RAISE G_EXCEPTION_HALT_VALIDATION;
373 END IF;
374
375 EXCEPTION
376 WHEN G_EXCEPTION_HALT_VALIDATION THEN
377 -- no processing necessary; validation can continue
378 -- with the next column
379 NULL;
380
381 WHEN OTHERS THEN
382 -- store SQL error message on message stack for caller
383 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
384 p_msg_name => G_OKL_UNEXPECTED_ERROR,
385 p_token1 => G_OKL_SQLCODE_TOKEN,
386 p_token1_value => SQLCODE,
387 p_token2 => G_OKL_SQLERRM_TOKEN,
388 p_token2_value => SQLERRM);
389
390 -- notify caller of an UNEXPECTED error
391 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
392
393 END Validate_Id;
394
395 --------------------------------------------------------------------------
396 -- PROCEDURE Validate_Sir_Id
397 ---------------------------------------------------------------------------
398 -- Start of comments
399 --
400 -- Procedure Name : Validate_Sir_Id
401 -- Description :
402 -- Business Rules :
403 -- Parameters :
404 -- Version : 1.0
405 -- End of comments
406 ---------------------------------------------------------------------------
407
408 PROCEDURE Validate_Sir_Id( p_srmv_rec IN srmv_rec_type
409 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
410
411 CURSOR l_sirid_csr IS
412 SELECT '1'
413 FROM OKL_SIF_RETS
414 WHERE ID = p_srmv_rec.sir_id;
415
416 l_dummy_sir_id VARCHAR2(1);
417 l_row_notfound BOOLEAN :=TRUE;
418 BEGIN
419 -- initialize return status
420 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
421 -- check for data before processing
422 IF (p_srmv_rec.sir_id IS NULL) OR
423 (p_srmv_rec.sir_id = Okc_Api.G_MISS_NUM)
424 THEN
425 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
426 ,p_msg_name => g_required_value
427 ,p_token1 => g_col_name_token
428 ,p_token1_value => 'sir_id');
429 x_return_status := Okc_Api.G_RET_STS_ERROR;
430 RAISE G_EXCEPTION_HALT_VALIDATION;
431
432 ELSIF p_srmv_rec.sir_id IS NOT NULL THEN
433 --Check if sir_id exists in the stream level table or not
434 OPEN l_sirid_csr;
435 FETCH l_sirid_csr INTO l_dummy_sir_id;
436 l_row_notfound :=l_sirid_csr%NOTFOUND;
437 CLOSE l_sirid_csr;
438 IF(l_row_notfound ) THEN
439 OKC_API.SET_MESSAGE(G_APP_NAME,G_OKL_NO_PARENT_RECORD,G_COL_NAME_TOKEN,'SIR_ID',G_CHILD_TABLE_TOKEN,'OKL_STRM_ELEMENTS_V',G_PARENT_TABLE_TOKEN,'OKL_STREAM_LEVELS_V');
440 x_return_status := Okc_Api.G_RET_STS_ERROR;
441 -- raise the exception as there's no matching foreign key value
442 RAISE G_EXCEPTION_HALT_VALIDATION;
443 END IF;
444 END IF;
445 EXCEPTION
446 WHEN G_EXCEPTION_HALT_VALIDATION THEN
447 -- no processing necessary; validation can continue
448 -- with the next column
449 NULL;
450 WHEN OTHERS THEN
451 -- store SQL error message on message stack for caller
452 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
453 p_msg_name => G_OKL_UNEXPECTED_ERROR,
454 p_token1 => G_OKL_SQLCODE_TOKEN,
455 p_token1_value => SQLCODE,
456 p_token2 => G_OKL_SQLERRM_TOKEN,
457 p_token2_value => SQLERRM);
458 -- notify caller of an UNEXPECTED error
459 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
460
461 IF l_sirid_csr%ISOPEN
462 THEN
463 CLOSE l_sirid_csr;
464 END IF;
465 END Validate_sir_Id;
466
467 ---------------------------------------------------------------------------
468 -- PROCEDURE Validate_Object_Version_Number
469 ---------------------------------------------------------------------------
470 -- Start of comments
471 --
472 -- Procedure Name : Validate_Object_Version_Number
473 -- Description :
474 -- Business Rules :
475 -- Parameters :
476 -- Version : 1.0
477 -- End of comments
478 ---------------------------------------------------------------------------
479
480 PROCEDURE Validate_Object_Version_Number(p_srmv_rec IN srmv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
481
482 IS
483
484 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
485
486 BEGIN
487 -- initialize return status
488 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
489 -- check for data before processing
490 IF (p_srmv_rec.object_version_number IS NULL) OR
491 (p_srmv_rec.object_version_number = Okc_Api.G_MISS_NUM) THEN
492 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
493 ,p_msg_name => g_required_value
494 ,p_token1 => g_col_name_token
495 ,p_token1_value => 'object_version_number');
496 x_return_status := Okc_Api.G_RET_STS_ERROR;
497 RAISE G_EXCEPTION_HALT_VALIDATION;
498 END IF;
499
500 EXCEPTION
501 WHEN G_EXCEPTION_HALT_VALIDATION THEN
502 -- no processing necessary; validation can continue
503 -- with the next column
504 NULL;
505
506 WHEN OTHERS THEN
507 -- store SQL error message on message stack for caller
508 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
509 p_msg_name => G_OKL_UNEXPECTED_ERROR,
510 p_token1 => G_OKL_SQLCODE_TOKEN,
511 p_token1_value => SQLCODE,
512 p_token2 => G_OKL_SQLERRM_TOKEN,
513 p_token2_value => SQLERRM);
514
515 -- notify caller of an UNEXPECTED error
516 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
517
518 END Validate_Object_Version_Number;
519
520
521 ---------------------------------------------------------------------------
522 -- FUNCTION Validate_Attributes
523 ---------------------------------------------------------------------------
524 -- Start of comments
525 --
526 -- Procedure Name : Validate_Attributes
527 -- Description :
528 -- Business Rules :
529 -- Parameters :
530 -- Version : 1.0
531 -- End of comments
532 ---------------------------------------------------------------------------
533 FUNCTION Validate_Attributes (
534 p_srmv_rec IN srmv_rec_type
535 ) RETURN VARCHAR2 IS
536
537 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
538 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
539 BEGIN
540
541 -- call each column-level validation
542
543
544 -- Validate_Id
545 Validate_Id(p_srmv_rec, x_return_status);
546 -- store the highest degree of error
547 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
548 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
549 -- need to leave
550 l_return_status := x_return_status;
551 RAISE G_EXCEPTION_HALT_VALIDATION;
552 ELSE
553 -- record that there was an error
554 l_return_status := x_return_status;
555 END IF;
556 END IF;
557
558 -- Validate_Object_Version_Number
559 Validate_Object_Version_Number(p_srmv_rec, x_return_status);
560 -- store the highest degree of error
561 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
562 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
563 -- need to leave
564 l_return_status := x_return_status;
565 RAISE G_EXCEPTION_HALT_VALIDATION;
566 ELSE
567 -- record that there was an error
568 l_return_status := x_return_status;
569 END IF;
570 END IF;
571
572 -- Validate_Sir_Id
573 Validate_Sir_Id(p_srmv_rec, x_return_status);
574
575 -- store the highest degree of error
576 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
577 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
578 -- need to leave
579 l_return_status := x_return_status;
580 RAISE G_EXCEPTION_HALT_VALIDATION;
581 ELSE
582 -- record that there was an error
583 l_return_status := x_return_status;
584 END IF;
585 END IF;
586 RETURN(l_return_status);
587 EXCEPTION
588 WHEN G_EXCEPTION_HALT_VALIDATION THEN
589 -- just come out with return status
590
591 RETURN (l_return_status);
592
593 WHEN OTHERS THEN
594 -- store SQL error message on message stack for caller
595 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
596 p_msg_name => G_OKL_UNEXPECTED_ERROR,
597 p_token1 => G_OKL_SQLCODE_TOKEN,
598 p_token1_value => SQLCODE,
599 p_token2 => G_OKL_SQLERRM_TOKEN,
600 p_token2_value => SQLERRM);
601 -- notify caller of an UNEXPECTED error
602 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
603 RETURN(l_return_status);
604
605 END Validate_Attributes;
606
607
608
609 -- END CHANGE AKJAIN
610
611
612 ---------------------------------------------------------------------------
613 -- PROCEDURE Validate_Record
614 ---------------------------------------------------------------------------
615 ----------------------------------------------
616 -- Validate_Record for:OKL_SIF_RET_ERRORS_V --
617 ----------------------------------------------
618 FUNCTION Validate_Record (
619 p_srmv_rec IN srmv_rec_type
620 ) RETURN VARCHAR2 IS
621 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
622 BEGIN
623 RETURN (l_return_status);
624 END Validate_Record;
625
626 ---------------------------------------------------------------------------
627 -- PROCEDURE Migrate
628 ---------------------------------------------------------------------------
629 PROCEDURE migrate (
630 p_from IN srmv_rec_type,
631 p_to IN OUT NOCOPY srm_rec_type
632 ) IS
633 BEGIN
634 p_to.id := p_from.id;
635 p_to.sir_id := p_from.sir_id;
636 p_to.error_code := p_from.error_code;
637 p_to.error_message := p_from.error_message;
638 p_to.tag_name := p_from.tag_name;
639 p_to.tag_attribute_name := p_from.tag_attribute_name;
640 p_to.tag_attribute_value := p_from.tag_attribute_value;
641 p_to.description := p_from.description;
642 p_to.stream_interface_attribute01 := p_from.stream_interface_attribute01;
643 p_to.stream_interface_attribute02 := p_from.stream_interface_attribute02;
644 p_to.stream_interface_attribute03 := p_from.stream_interface_attribute03;
645 p_to.stream_interface_attribute04 := p_from.stream_interface_attribute04;
646 p_to.stream_interface_attribute05 := p_from.stream_interface_attribute05;
647 p_to.stream_interface_attribute06 := p_from.stream_interface_attribute06;
648 p_to.stream_interface_attribute07 := p_from.stream_interface_attribute07;
649 p_to.stream_interface_attribute08 := p_from.stream_interface_attribute08;
650 p_to.stream_interface_attribute09 := p_from.stream_interface_attribute09;
651 p_to.stream_interface_attribute10 := p_from.stream_interface_attribute10;
652 p_to.stream_interface_attribute11 := p_from.stream_interface_attribute11;
653 p_to.stream_interface_attribute12 := p_from.stream_interface_attribute12;
654 p_to.stream_interface_attribute13 := p_from.stream_interface_attribute13;
655 p_to.stream_interface_attribute14 := p_from.stream_interface_attribute14;
656 p_to.stream_interface_attribute15 := p_from.stream_interface_attribute15;
657 p_to.object_version_number := p_from.object_version_number;
658 p_to.created_by := p_from.created_by;
659 p_to.last_updated_by := p_from.last_updated_by;
660 p_to.creation_date := p_from.creation_date;
661 p_to.last_update_date := p_from.last_update_date;
662 p_to.last_update_login := p_from.last_update_login;
663 END migrate;
664 PROCEDURE migrate (
665 p_from IN srm_rec_type,
666 p_to IN OUT NOCOPY srmv_rec_type
667 ) IS
668 BEGIN
669 p_to.id := p_from.id;
670 p_to.sir_id := p_from.sir_id;
671 p_to.error_code := p_from.error_code;
672 p_to.error_message := p_from.error_message;
673 p_to.tag_name := p_from.tag_name;
674 p_to.tag_attribute_name := p_from.tag_attribute_name;
675 p_to.tag_attribute_value := p_from.tag_attribute_value;
676 p_to.description := p_from.description;
677 p_to.stream_interface_attribute01 := p_from.stream_interface_attribute01;
678 p_to.stream_interface_attribute02 := p_from.stream_interface_attribute02;
679 p_to.stream_interface_attribute03 := p_from.stream_interface_attribute03;
680 p_to.stream_interface_attribute04 := p_from.stream_interface_attribute04;
681 p_to.stream_interface_attribute05 := p_from.stream_interface_attribute05;
682 p_to.stream_interface_attribute06 := p_from.stream_interface_attribute06;
683 p_to.stream_interface_attribute07 := p_from.stream_interface_attribute07;
684 p_to.stream_interface_attribute08 := p_from.stream_interface_attribute08;
685 p_to.stream_interface_attribute09 := p_from.stream_interface_attribute09;
686 p_to.stream_interface_attribute10 := p_from.stream_interface_attribute10;
687 p_to.stream_interface_attribute11 := p_from.stream_interface_attribute11;
688 p_to.stream_interface_attribute12 := p_from.stream_interface_attribute12;
689 p_to.stream_interface_attribute13 := p_from.stream_interface_attribute13;
690 p_to.stream_interface_attribute14 := p_from.stream_interface_attribute14;
691 p_to.stream_interface_attribute15 := p_from.stream_interface_attribute15;
692 p_to.object_version_number := p_from.object_version_number;
693 p_to.created_by := p_from.created_by;
694 p_to.last_updated_by := p_from.last_updated_by;
695 p_to.creation_date := p_from.creation_date;
696 p_to.last_update_date := p_from.last_update_date;
697 p_to.last_update_login := p_from.last_update_login;
698 END migrate;
699
700 ---------------------------------------------------------------------------
701 -- PROCEDURE validate_row
702 ---------------------------------------------------------------------------
703 -------------------------------------------
704 -- validate_row for:OKL_SIF_RET_ERRORS_V --
705 -------------------------------------------
706 PROCEDURE validate_row(
707 p_api_version IN NUMBER,
708 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
709 x_return_status OUT NOCOPY VARCHAR2,
710 x_msg_count OUT NOCOPY NUMBER,
711 x_msg_data OUT NOCOPY VARCHAR2,
712 p_srmv_rec IN srmv_rec_type) IS
713
714 l_api_version CONSTANT NUMBER := 1;
715 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
716 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
717 l_srmv_rec srmv_rec_type := p_srmv_rec;
718 l_srm_rec srm_rec_type;
719 BEGIN
720 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
721 G_PKG_NAME,
722 p_init_msg_list,
723 l_api_version,
724 p_api_version,
725 '_PVT',
726 x_return_status);
727 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
728 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
729 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
730 RAISE OKC_API.G_EXCEPTION_ERROR;
731 END IF;
732 --- Validate all non-missing attributes (Item Level Validation)
733 l_return_status := Validate_Attributes(l_srmv_rec);
734 --- If any errors happen abort API
735 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
736 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
737 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
738 RAISE OKC_API.G_EXCEPTION_ERROR;
739 END IF;
740 l_return_status := Validate_Record(l_srmv_rec);
741 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
742 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
743 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
744 RAISE OKC_API.G_EXCEPTION_ERROR;
745 END IF;
746 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
747 EXCEPTION
748 WHEN OKC_API.G_EXCEPTION_ERROR THEN
749 x_return_status := OKC_API.HANDLE_EXCEPTIONS
750 (
751 l_api_name,
752 G_PKG_NAME,
753 'OKC_API.G_RET_STS_ERROR',
754 x_msg_count,
755 x_msg_data,
756 '_PVT'
757 );
758 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
759 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
760 (
761 l_api_name,
762 G_PKG_NAME,
763 'OKC_API.G_RET_STS_UNEXP_ERROR',
764 x_msg_count,
765 x_msg_data,
766 '_PVT'
767 );
768 WHEN OTHERS THEN
769 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
770 (
771 l_api_name,
772 G_PKG_NAME,
773 'OTHERS',
774 x_msg_count,
775 x_msg_data,
776 '_PVT'
777 );
778 END validate_row;
779 ------------------------------------------
780 -- PL/SQL TBL validate_row for:SRMV_TBL --
781 ------------------------------------------
782 PROCEDURE validate_row(
783 p_api_version IN NUMBER,
784 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
785 x_return_status OUT NOCOPY VARCHAR2,
786 x_msg_count OUT NOCOPY NUMBER,
787 x_msg_data OUT NOCOPY VARCHAR2,
788 p_srmv_tbl IN srmv_tbl_type) IS
789
790 l_api_version CONSTANT NUMBER := 1;
791 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
792 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
793 i NUMBER := 0;
794 -- START change : akjain, 11/05/2001
795 -- Adding OverAll Status Flag
796 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
797 -- END change : akjain
798
799 BEGIN
800 OKC_API.init_msg_list(p_init_msg_list);
801 -- Make sure PL/SQL table has records in it before passing
802 IF (p_srmv_tbl.COUNT > 0) THEN
803 i := p_srmv_tbl.FIRST;
804 LOOP
805 validate_row (
806 p_api_version => p_api_version,
807 p_init_msg_list => OKC_API.G_FALSE,
808 x_return_status => x_return_status,
809 x_msg_count => x_msg_count,
810 x_msg_data => x_msg_data,
811 p_srmv_rec => p_srmv_tbl(i));
812 -- START change : akjain, 11/05/2001
813 -- store the highest degree of error
814 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
815 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
816 l_overall_status := x_return_status;
817 END IF;
818 END IF;
819 -- END change : akjain
820
821 EXIT WHEN (i = p_srmv_tbl.LAST);
822 i := p_srmv_tbl.NEXT(i);
823 END LOOP;
824 -- START change : akjain, 11/05/2001
825 -- return overall status
826 x_return_status := l_overall_status;
827 -- END change : akjain
828
829 END IF;
830 EXCEPTION
831 WHEN OKC_API.G_EXCEPTION_ERROR THEN
832 x_return_status := OKC_API.HANDLE_EXCEPTIONS
833 (
834 l_api_name,
835 G_PKG_NAME,
836 'OKC_API.G_RET_STS_ERROR',
837 x_msg_count,
838 x_msg_data,
839 '_PVT'
840 );
841 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
842 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
843 (
844 l_api_name,
845 G_PKG_NAME,
846 'OKC_API.G_RET_STS_UNEXP_ERROR',
847 x_msg_count,
848 x_msg_data,
849 '_PVT'
850 );
851 WHEN OTHERS THEN
852 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
853 (
854 l_api_name,
855 G_PKG_NAME,
856 'OTHERS',
857 x_msg_count,
858 x_msg_data,
859 '_PVT'
860 );
861 END validate_row;
862
863 ---------------------------------------------------------------------------
864 -- PROCEDURE insert_row
865 ---------------------------------------------------------------------------
866 ---------------------------------------
867 -- insert_row for:OKL_SIF_RET_ERRORS --
868 ---------------------------------------
869 PROCEDURE insert_row(
870 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
871 x_return_status OUT NOCOPY VARCHAR2,
872 x_msg_count OUT NOCOPY NUMBER,
873 x_msg_data OUT NOCOPY VARCHAR2,
874 p_srm_rec IN srm_rec_type,
875 x_srm_rec OUT NOCOPY srm_rec_type) IS
876
877 l_api_version CONSTANT NUMBER := 1;
878 l_api_name CONSTANT VARCHAR2(30) := 'ERRORS_insert_row';
879 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
880 l_srm_rec srm_rec_type := p_srm_rec;
881 l_def_srm_rec srm_rec_type;
882 -------------------------------------------
883 -- Set_Attributes for:OKL_SIF_RET_ERRORS --
884 -------------------------------------------
885 FUNCTION Set_Attributes (
886 p_srm_rec IN srm_rec_type,
887 x_srm_rec OUT NOCOPY srm_rec_type
888 ) RETURN VARCHAR2 IS
889 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
890 BEGIN
891 x_srm_rec := p_srm_rec;
892 RETURN(l_return_status);
893 END Set_Attributes;
894 BEGIN
895 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
896 p_init_msg_list,
897 '_PVT',
898 x_return_status);
899 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
900 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
901 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
902 RAISE OKC_API.G_EXCEPTION_ERROR;
903 END IF;
904 --- Setting item attributes
905 l_return_status := Set_Attributes(
906 p_srm_rec, -- IN
907 l_srm_rec); -- OUT
908 --- If any errors happen abort API
909 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
910 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
911 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
912 RAISE OKC_API.G_EXCEPTION_ERROR;
913 END IF;
914 INSERT INTO OKL_SIF_RET_ERRORS(
915 id,
916 sir_id,
917 error_code,
918 error_message,
919 tag_name,
920 tag_attribute_name,
921 tag_attribute_value,
922 description,
923 stream_interface_attribute01,
924 stream_interface_attribute02,
925 stream_interface_attribute03,
926 stream_interface_attribute04,
927 stream_interface_attribute05,
928 stream_interface_attribute06,
929 stream_interface_attribute07,
930 stream_interface_attribute08,
931 stream_interface_attribute09,
932 stream_interface_attribute10,
933 stream_interface_attribute11,
934 stream_interface_attribute12,
935 stream_interface_attribute13,
936 stream_interface_attribute14,
937 stream_interface_attribute15,
938 object_version_number,
939 created_by,
940 last_updated_by,
941 creation_date,
942 last_update_date,
943 last_update_login)
944 VALUES (
945 l_srm_rec.id,
946 l_srm_rec.sir_id,
947 l_srm_rec.error_code,
948 l_srm_rec.error_message,
949 l_srm_rec.tag_name,
950 l_srm_rec.tag_attribute_name,
951 l_srm_rec.tag_attribute_value,
952 l_srm_rec.description,
953 l_srm_rec.stream_interface_attribute01,
954 l_srm_rec.stream_interface_attribute02,
955 l_srm_rec.stream_interface_attribute03,
956 l_srm_rec.stream_interface_attribute04,
957 l_srm_rec.stream_interface_attribute05,
958 l_srm_rec.stream_interface_attribute06,
959 l_srm_rec.stream_interface_attribute07,
960 l_srm_rec.stream_interface_attribute08,
961 l_srm_rec.stream_interface_attribute09,
962 l_srm_rec.stream_interface_attribute10,
963 l_srm_rec.stream_interface_attribute11,
964 l_srm_rec.stream_interface_attribute12,
965 l_srm_rec.stream_interface_attribute13,
966 l_srm_rec.stream_interface_attribute14,
967 l_srm_rec.stream_interface_attribute15,
968 l_srm_rec.object_version_number,
969 l_srm_rec.created_by,
970 l_srm_rec.last_updated_by,
971 l_srm_rec.creation_date,
972 l_srm_rec.last_update_date,
973 l_srm_rec.last_update_login);
974 -- Set OUT values
975 x_srm_rec := l_srm_rec;
976 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
977 EXCEPTION
978 WHEN OKC_API.G_EXCEPTION_ERROR THEN
979 x_return_status := OKC_API.HANDLE_EXCEPTIONS
980 (
981 l_api_name,
982 G_PKG_NAME,
983 'OKC_API.G_RET_STS_ERROR',
984 x_msg_count,
985 x_msg_data,
986 '_PVT'
987 );
988 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
989 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
990 (
991 l_api_name,
992 G_PKG_NAME,
993 'OKC_API.G_RET_STS_UNEXP_ERROR',
994 x_msg_count,
995 x_msg_data,
996 '_PVT'
997 );
998 WHEN OTHERS THEN
999 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1000 (
1001 l_api_name,
1002 G_PKG_NAME,
1003 'OTHERS',
1004 x_msg_count,
1005 x_msg_data,
1006 '_PVT'
1007 );
1008 END insert_row;
1009 -----------------------------------------
1010 -- insert_row for:OKL_SIF_RET_ERRORS_V --
1011 -----------------------------------------
1012 PROCEDURE insert_row(
1013 p_api_version IN NUMBER,
1014 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1015 x_return_status OUT NOCOPY VARCHAR2,
1016 x_msg_count OUT NOCOPY NUMBER,
1017 x_msg_data OUT NOCOPY VARCHAR2,
1018 p_srmv_rec IN srmv_rec_type,
1019 x_srmv_rec OUT NOCOPY srmv_rec_type) IS
1020
1021 l_api_version CONSTANT NUMBER := 1;
1022 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1023 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1024 l_srmv_rec srmv_rec_type;
1025 l_def_srmv_rec srmv_rec_type;
1026 l_srm_rec srm_rec_type;
1027 lx_srm_rec srm_rec_type;
1028 -------------------------------
1029 -- FUNCTION fill_who_columns --
1030 -------------------------------
1031 FUNCTION fill_who_columns (
1032 p_srmv_rec IN srmv_rec_type
1033 ) RETURN srmv_rec_type IS
1034 l_srmv_rec srmv_rec_type := p_srmv_rec;
1035 BEGIN
1036 l_srmv_rec.CREATION_DATE := SYSDATE;
1037 l_srmv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1038 l_srmv_rec.LAST_UPDATE_DATE := SYSDATE;
1039 l_srmv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1040 l_srmv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1041 RETURN(l_srmv_rec);
1042 END fill_who_columns;
1043 ---------------------------------------------
1044 -- Set_Attributes for:OKL_SIF_RET_ERRORS_V --
1045 ---------------------------------------------
1046 FUNCTION Set_Attributes (
1047 p_srmv_rec IN srmv_rec_type,
1048 x_srmv_rec OUT NOCOPY srmv_rec_type
1049 ) RETURN VARCHAR2 IS
1050 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1051 BEGIN
1052 x_srmv_rec := p_srmv_rec;
1053 x_srmv_rec.OBJECT_VERSION_NUMBER := 1;
1054 RETURN(l_return_status);
1055 END Set_Attributes;
1056 BEGIN
1057 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1058 G_PKG_NAME,
1059 p_init_msg_list,
1060 l_api_version,
1061 p_api_version,
1062 '_PVT',
1063 x_return_status);
1064 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1066 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1067 RAISE OKC_API.G_EXCEPTION_ERROR;
1068 END IF;
1069 l_srmv_rec := null_out_defaults(p_srmv_rec);
1070 -- Set primary key value
1071 l_srmv_rec.ID := get_seq_id;
1072 --- Setting item attributes
1073 l_return_status := Set_Attributes(
1074 l_srmv_rec, -- IN
1075 l_def_srmv_rec); -- OUT
1076 --- If any errors happen abort API
1077 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1078 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1079 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1080 RAISE OKC_API.G_EXCEPTION_ERROR;
1081 END IF;
1082 l_def_srmv_rec := fill_who_columns(l_def_srmv_rec);
1083 --- Validate all non-missing attributes (Item Level Validation)
1084 l_return_status := Validate_Attributes(l_def_srmv_rec);
1085 --- If any errors happen abort API
1086 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1087 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1088 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1089 RAISE OKC_API.G_EXCEPTION_ERROR;
1090 END IF;
1091 l_return_status := Validate_Record(l_def_srmv_rec);
1092 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1093 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1094 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1095 RAISE OKC_API.G_EXCEPTION_ERROR;
1096 END IF;
1097 --------------------------------------
1098 -- Move VIEW record to "Child" records
1099 --------------------------------------
1100 migrate(l_def_srmv_rec, l_srm_rec);
1101 --------------------------------------------
1102 -- Call the INSERT_ROW for each child record
1103 --------------------------------------------
1104 insert_row(
1105 p_init_msg_list,
1106 x_return_status,
1107 x_msg_count,
1108 x_msg_data,
1109 l_srm_rec,
1110 lx_srm_rec
1111 );
1112 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1113 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1114 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1115 RAISE OKC_API.G_EXCEPTION_ERROR;
1116 END IF;
1117 migrate(lx_srm_rec, l_def_srmv_rec);
1118 -- Set OUT values
1119 x_srmv_rec := l_def_srmv_rec;
1120 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1121 EXCEPTION
1122 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1123 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1124 (
1125 l_api_name,
1126 G_PKG_NAME,
1127 'OKC_API.G_RET_STS_ERROR',
1128 x_msg_count,
1129 x_msg_data,
1130 '_PVT'
1131 );
1132 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1133 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1134 (
1135 l_api_name,
1136 G_PKG_NAME,
1137 'OKC_API.G_RET_STS_UNEXP_ERROR',
1138 x_msg_count,
1139 x_msg_data,
1140 '_PVT'
1141 );
1142 WHEN OTHERS THEN
1143 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1144 (
1145 l_api_name,
1146 G_PKG_NAME,
1147 'OTHERS',
1148 x_msg_count,
1149 x_msg_data,
1150 '_PVT'
1151 );
1152 END insert_row;
1153 ----------------------------------------
1154 -- PL/SQL TBL insert_row for:SRMV_TBL --
1155 ----------------------------------------
1156 PROCEDURE insert_row(
1157 p_api_version IN NUMBER,
1158 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1159 x_return_status OUT NOCOPY VARCHAR2,
1160 x_msg_count OUT NOCOPY NUMBER,
1161 x_msg_data OUT NOCOPY VARCHAR2,
1162 p_srmv_tbl IN srmv_tbl_type,
1163 x_srmv_tbl OUT NOCOPY srmv_tbl_type) IS
1164
1165 l_api_version CONSTANT NUMBER := 1;
1166 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1167 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1168 i NUMBER := 0;
1169 -- START change : akjain, 11/05/2001
1170 -- Adding OverAll Status Flag
1171 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1172 -- END change : akjain
1173
1174 BEGIN
1175 OKC_API.init_msg_list(p_init_msg_list);
1176 -- Make sure PL/SQL table has records in it before passing
1177 IF (p_srmv_tbl.COUNT > 0) THEN
1178 i := p_srmv_tbl.FIRST;
1179 LOOP
1180 insert_row (
1181 p_api_version => p_api_version,
1182 p_init_msg_list => OKC_API.G_FALSE,
1183 x_return_status => x_return_status,
1184 x_msg_count => x_msg_count,
1185 x_msg_data => x_msg_data,
1186 p_srmv_rec => p_srmv_tbl(i),
1187 x_srmv_rec => x_srmv_tbl(i));
1188 -- START change : akjain, 11/05/2001
1189 -- store the highest degree of error
1190 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1191 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1192 l_overall_status := x_return_status;
1193 END IF;
1194 END IF;
1195 -- END change : akjain
1196
1197 EXIT WHEN (i = p_srmv_tbl.LAST);
1198 i := p_srmv_tbl.NEXT(i);
1199 END LOOP;
1200 -- START change : akjain, 11/05/2001
1201 -- return overall status
1202 x_return_status := l_overall_status;
1203 -- END change : akjain
1204
1205 END IF;
1206 EXCEPTION
1207 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1208 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1209 (
1210 l_api_name,
1211 G_PKG_NAME,
1212 'OKC_API.G_RET_STS_ERROR',
1213 x_msg_count,
1214 x_msg_data,
1215 '_PVT'
1216 );
1217 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1218 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1219 (
1220 l_api_name,
1221 G_PKG_NAME,
1222 'OKC_API.G_RET_STS_UNEXP_ERROR',
1223 x_msg_count,
1224 x_msg_data,
1225 '_PVT'
1226 );
1227 WHEN OTHERS THEN
1228 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1229 (
1230 l_api_name,
1231 G_PKG_NAME,
1232 'OTHERS',
1233 x_msg_count,
1234 x_msg_data,
1235 '_PVT'
1236 );
1237 END insert_row;
1238
1239 ---------------------------------------------------------------------------
1240 -- PROCEDURE lock_row
1241 ---------------------------------------------------------------------------
1242 -------------------------------------
1243 -- lock_row for:OKL_SIF_RET_ERRORS --
1244 -------------------------------------
1245 PROCEDURE lock_row(
1246 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1247 x_return_status OUT NOCOPY VARCHAR2,
1248 x_msg_count OUT NOCOPY NUMBER,
1249 x_msg_data OUT NOCOPY VARCHAR2,
1250 p_srm_rec IN srm_rec_type) IS
1251
1252 E_Resource_Busy EXCEPTION;
1253 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1254 CURSOR lock_csr (p_srm_rec IN srm_rec_type) IS
1255 SELECT OBJECT_VERSION_NUMBER
1256 FROM OKL_SIF_RET_ERRORS
1257 WHERE ID = p_srm_rec.id
1258 AND OBJECT_VERSION_NUMBER = p_srm_rec.object_version_number
1259 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1260
1261 CURSOR lchk_csr (p_srm_rec IN srm_rec_type) IS
1262 SELECT OBJECT_VERSION_NUMBER
1263 FROM OKL_SIF_RET_ERRORS
1264 WHERE ID = p_srm_rec.id;
1265 l_api_version CONSTANT NUMBER := 1;
1266 l_api_name CONSTANT VARCHAR2(30) := 'ERRORS_lock_row';
1267 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1268 l_object_version_number OKL_SIF_RET_ERRORS.OBJECT_VERSION_NUMBER%TYPE;
1269 lc_object_version_number OKL_SIF_RET_ERRORS.OBJECT_VERSION_NUMBER%TYPE;
1270 l_row_notfound BOOLEAN := FALSE;
1271 lc_row_notfound BOOLEAN := FALSE;
1272 BEGIN
1273 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1274 p_init_msg_list,
1275 '_PVT',
1276 x_return_status);
1277 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1278 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1279 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1280 RAISE OKC_API.G_EXCEPTION_ERROR;
1281 END IF;
1282 BEGIN
1283 OPEN lock_csr(p_srm_rec);
1284 FETCH lock_csr INTO l_object_version_number;
1285 l_row_notfound := lock_csr%NOTFOUND;
1286 CLOSE lock_csr;
1287 EXCEPTION
1288 WHEN E_Resource_Busy THEN
1289 IF (lock_csr%ISOPEN) THEN
1290 CLOSE lock_csr;
1291 END IF;
1292 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1293 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1294 END;
1295
1296 IF ( l_row_notfound ) THEN
1297 OPEN lchk_csr(p_srm_rec);
1298 FETCH lchk_csr INTO lc_object_version_number;
1299 lc_row_notfound := lchk_csr%NOTFOUND;
1300 CLOSE lchk_csr;
1301 END IF;
1302 IF (lc_row_notfound) THEN
1303 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1304 RAISE OKC_API.G_EXCEPTION_ERROR;
1305 ELSIF lc_object_version_number > p_srm_rec.object_version_number THEN
1306 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1307 RAISE OKC_API.G_EXCEPTION_ERROR;
1308 ELSIF lc_object_version_number <> p_srm_rec.object_version_number THEN
1309 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1310 RAISE OKC_API.G_EXCEPTION_ERROR;
1311 ELSIF lc_object_version_number = -1 THEN
1312 OKC_API.set_message(G_OKC_APP,G_RECORD_LOGICALLY_DELETED);
1313 RAISE OKC_API.G_EXCEPTION_ERROR;
1314 END IF;
1315 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1316 EXCEPTION
1317 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1318 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1319 (
1320 l_api_name,
1321 G_PKG_NAME,
1322 'OKC_API.G_RET_STS_ERROR',
1323 x_msg_count,
1324 x_msg_data,
1325 '_PVT'
1326 );
1327 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1328 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1329 (
1330 l_api_name,
1331 G_PKG_NAME,
1332 'OKC_API.G_RET_STS_UNEXP_ERROR',
1333 x_msg_count,
1334 x_msg_data,
1335 '_PVT'
1336 );
1337 WHEN OTHERS THEN
1338 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1339 (
1340 l_api_name,
1341 G_PKG_NAME,
1342 'OTHERS',
1343 x_msg_count,
1344 x_msg_data,
1345 '_PVT'
1346 );
1347 END lock_row;
1348 ---------------------------------------
1349 -- lock_row for:OKL_SIF_RET_ERRORS_V --
1350 ---------------------------------------
1351 PROCEDURE lock_row(
1352 p_api_version IN NUMBER,
1353 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1354 x_return_status OUT NOCOPY VARCHAR2,
1355 x_msg_count OUT NOCOPY NUMBER,
1356 x_msg_data OUT NOCOPY VARCHAR2,
1357 p_srmv_rec IN srmv_rec_type) IS
1358
1359 l_api_version CONSTANT NUMBER := 1;
1360 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1361 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1362 l_srm_rec srm_rec_type;
1363 BEGIN
1364 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1365 G_PKG_NAME,
1366 p_init_msg_list,
1367 l_api_version,
1368 p_api_version,
1369 '_PVT',
1370 x_return_status);
1371 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1372 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1373 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1374 RAISE OKC_API.G_EXCEPTION_ERROR;
1375 END IF;
1376 --------------------------------------
1377 -- Move VIEW record to "Child" records
1378 --------------------------------------
1379 migrate(p_srmv_rec, l_srm_rec);
1380 --------------------------------------------
1381 -- Call the LOCK_ROW for each child record
1382 --------------------------------------------
1383 lock_row(
1384 p_init_msg_list,
1385 x_return_status,
1386 x_msg_count,
1387 x_msg_data,
1388 l_srm_rec
1389 );
1390 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1392 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1393 RAISE OKC_API.G_EXCEPTION_ERROR;
1394 END IF;
1395 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1396 EXCEPTION
1397 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1398 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1399 (
1400 l_api_name,
1401 G_PKG_NAME,
1402 'OKC_API.G_RET_STS_ERROR',
1403 x_msg_count,
1404 x_msg_data,
1405 '_PVT'
1406 );
1407 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1408 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1409 (
1410 l_api_name,
1411 G_PKG_NAME,
1412 'OKC_API.G_RET_STS_UNEXP_ERROR',
1413 x_msg_count,
1414 x_msg_data,
1415 '_PVT'
1416 );
1417 WHEN OTHERS THEN
1418 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1419 (
1420 l_api_name,
1421 G_PKG_NAME,
1422 'OTHERS',
1423 x_msg_count,
1424 x_msg_data,
1425 '_PVT'
1426 );
1427 END lock_row;
1428 --------------------------------------
1429 -- PL/SQL TBL lock_row for:SRMV_TBL --
1430 --------------------------------------
1431 PROCEDURE lock_row(
1432 p_api_version IN NUMBER,
1433 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1434 x_return_status OUT NOCOPY VARCHAR2,
1435 x_msg_count OUT NOCOPY NUMBER,
1436 x_msg_data OUT NOCOPY VARCHAR2,
1437 p_srmv_tbl IN srmv_tbl_type) IS
1438
1439 l_api_version CONSTANT NUMBER := 1;
1440 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1441 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1442 i NUMBER := 0;
1443 -- START change : akjain, 11/05/2001
1444 -- Adding OverAll Status Flag
1445 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1446 -- END change : akjain
1447
1448 BEGIN
1449 OKC_API.init_msg_list(p_init_msg_list);
1450 -- Make sure PL/SQL table has records in it before passing
1451 IF (p_srmv_tbl.COUNT > 0) THEN
1452 i := p_srmv_tbl.FIRST;
1453 LOOP
1454 lock_row (
1455 p_api_version => p_api_version,
1456 p_init_msg_list => OKC_API.G_FALSE,
1457 x_return_status => x_return_status,
1458 x_msg_count => x_msg_count,
1459 x_msg_data => x_msg_data,
1460 p_srmv_rec => p_srmv_tbl(i));
1461 -- START change : akjain, 11/05/2001
1462 -- store the highest degree of error
1463 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1464 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1465 l_overall_status := x_return_status;
1466 END IF;
1467 END IF;
1468 -- END change : akjain
1469
1470 EXIT WHEN (i = p_srmv_tbl.LAST);
1471 i := p_srmv_tbl.NEXT(i);
1472 END LOOP;
1473 -- START change : akjain, 11/05/2001
1474 -- return overall status
1475 x_return_status := l_overall_status;
1476 -- END change : akjain
1477
1478 END IF;
1479 EXCEPTION
1480 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1481 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1482 (
1483 l_api_name,
1484 G_PKG_NAME,
1485 'OKC_API.G_RET_STS_ERROR',
1486 x_msg_count,
1487 x_msg_data,
1488 '_PVT'
1489 );
1490 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1491 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1492 (
1493 l_api_name,
1494 G_PKG_NAME,
1495 'OKC_API.G_RET_STS_UNEXP_ERROR',
1496 x_msg_count,
1497 x_msg_data,
1498 '_PVT'
1499 );
1500 WHEN OTHERS THEN
1501 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1502 (
1503 l_api_name,
1504 G_PKG_NAME,
1505 'OTHERS',
1506 x_msg_count,
1507 x_msg_data,
1508 '_PVT'
1509 );
1510 END lock_row;
1511
1512 ---------------------------------------------------------------------------
1513 -- PROCEDURE update_row
1514 ---------------------------------------------------------------------------
1515 ---------------------------------------
1516 -- update_row for:OKL_SIF_RET_ERRORS --
1517 ---------------------------------------
1518 PROCEDURE update_row(
1519 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1520 x_return_status OUT NOCOPY VARCHAR2,
1521 x_msg_count OUT NOCOPY NUMBER,
1522 x_msg_data OUT NOCOPY VARCHAR2,
1523 p_srm_rec IN srm_rec_type,
1524 x_srm_rec OUT NOCOPY srm_rec_type) IS
1525
1526 l_api_version CONSTANT NUMBER := 1;
1527 l_api_name CONSTANT VARCHAR2(30) := 'ERRORS_update_row';
1528 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1529 l_srm_rec srm_rec_type := p_srm_rec;
1530 l_def_srm_rec srm_rec_type;
1531 l_row_notfound BOOLEAN := TRUE;
1532 ----------------------------------
1533 -- FUNCTION populate_new_record --
1534 ----------------------------------
1535 FUNCTION populate_new_record (
1536 p_srm_rec IN srm_rec_type,
1537 x_srm_rec OUT NOCOPY srm_rec_type
1538 ) RETURN VARCHAR2 IS
1539 l_srm_rec srm_rec_type;
1540 l_row_notfound BOOLEAN := TRUE;
1541 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1542 BEGIN
1543 x_srm_rec := p_srm_rec;
1544 -- Get current database values
1545 l_srm_rec := get_rec(p_srm_rec, l_row_notfound);
1546 IF (l_row_notfound) THEN
1547 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1548 END IF;
1549 IF (x_srm_rec.id = OKC_API.G_MISS_NUM)
1550 THEN
1551 x_srm_rec.id := l_srm_rec.id;
1552 END IF;
1553 IF (x_srm_rec.sir_id = OKC_API.G_MISS_NUM)
1554 THEN
1555 x_srm_rec.sir_id := l_srm_rec.sir_id;
1556 END IF;
1557 IF (x_srm_rec.error_code = OKC_API.G_MISS_CHAR)
1558 THEN
1559 x_srm_rec.error_code := l_srm_rec.error_code;
1560 END IF;
1561 IF (x_srm_rec.error_message = OKC_API.G_MISS_CHAR)
1562 THEN
1563 x_srm_rec.error_message := l_srm_rec.error_message;
1564 END IF;
1565 IF (x_srm_rec.tag_name = OKC_API.G_MISS_CHAR)
1566 THEN
1567 x_srm_rec.tag_name := l_srm_rec.tag_name;
1568 END IF;
1569 IF (x_srm_rec.tag_attribute_name = OKC_API.G_MISS_CHAR)
1570 THEN
1571 x_srm_rec.tag_attribute_name := l_srm_rec.tag_attribute_name;
1572 END IF;
1573 IF (x_srm_rec.tag_attribute_value = OKC_API.G_MISS_CHAR)
1574 THEN
1575 x_srm_rec.tag_attribute_value := l_srm_rec.tag_attribute_value;
1576 END IF;
1577 IF (x_srm_rec.description = OKC_API.G_MISS_CHAR)
1578 THEN
1579 x_srm_rec.description := l_srm_rec.description;
1580 END IF;
1581 IF (x_srm_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR)
1582 THEN
1583 x_srm_rec.stream_interface_attribute01 := l_srm_rec.stream_interface_attribute01;
1584 END IF;
1585 IF (x_srm_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR)
1586 THEN
1587 x_srm_rec.stream_interface_attribute02 := l_srm_rec.stream_interface_attribute02;
1588 END IF;
1589 IF (x_srm_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR)
1590 THEN
1591 x_srm_rec.stream_interface_attribute03 := l_srm_rec.stream_interface_attribute03;
1592 END IF;
1593 IF (x_srm_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR)
1594 THEN
1595 x_srm_rec.stream_interface_attribute04 := l_srm_rec.stream_interface_attribute04;
1596 END IF;
1597 IF (x_srm_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR)
1598 THEN
1599 x_srm_rec.stream_interface_attribute05 := l_srm_rec.stream_interface_attribute05;
1600 END IF;
1601 IF (x_srm_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR)
1602 THEN
1603 x_srm_rec.stream_interface_attribute06 := l_srm_rec.stream_interface_attribute06;
1604 END IF;
1605 IF (x_srm_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR)
1606 THEN
1607 x_srm_rec.stream_interface_attribute07 := l_srm_rec.stream_interface_attribute07;
1608 END IF;
1609 IF (x_srm_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR)
1610 THEN
1611 x_srm_rec.stream_interface_attribute08 := l_srm_rec.stream_interface_attribute08;
1612 END IF;
1613 IF (x_srm_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR)
1614 THEN
1615 x_srm_rec.stream_interface_attribute09 := l_srm_rec.stream_interface_attribute09;
1616 END IF;
1617 IF (x_srm_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR)
1618 THEN
1619 x_srm_rec.stream_interface_attribute10 := l_srm_rec.stream_interface_attribute10;
1620 END IF;
1621 IF (x_srm_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR)
1622 THEN
1623 x_srm_rec.stream_interface_attribute11 := l_srm_rec.stream_interface_attribute11;
1624 END IF;
1625 IF (x_srm_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR)
1626 THEN
1627 x_srm_rec.stream_interface_attribute12 := l_srm_rec.stream_interface_attribute12;
1628 END IF;
1629 IF (x_srm_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR)
1630 THEN
1631 x_srm_rec.stream_interface_attribute13 := l_srm_rec.stream_interface_attribute13;
1632 END IF;
1633 IF (x_srm_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR)
1634 THEN
1635 x_srm_rec.stream_interface_attribute14 := l_srm_rec.stream_interface_attribute14;
1636 END IF;
1637 IF (x_srm_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR)
1638 THEN
1639 x_srm_rec.stream_interface_attribute15 := l_srm_rec.stream_interface_attribute15;
1640 END IF;
1641 IF (x_srm_rec.object_version_number = OKC_API.G_MISS_NUM)
1642 THEN
1643 x_srm_rec.object_version_number := l_srm_rec.object_version_number;
1644 END IF;
1645 IF (x_srm_rec.created_by = OKC_API.G_MISS_NUM)
1646 THEN
1647 x_srm_rec.created_by := l_srm_rec.created_by;
1648 END IF;
1649 IF (x_srm_rec.last_updated_by = OKC_API.G_MISS_NUM)
1650 THEN
1651 x_srm_rec.last_updated_by := l_srm_rec.last_updated_by;
1652 END IF;
1653 IF (x_srm_rec.creation_date = OKC_API.G_MISS_DATE)
1654 THEN
1655 x_srm_rec.creation_date := l_srm_rec.creation_date;
1656 END IF;
1657 IF (x_srm_rec.last_update_date = OKC_API.G_MISS_DATE)
1658 THEN
1659 x_srm_rec.last_update_date := l_srm_rec.last_update_date;
1660 END IF;
1661 IF (x_srm_rec.last_update_login = OKC_API.G_MISS_NUM)
1662 THEN
1663 x_srm_rec.last_update_login := l_srm_rec.last_update_login;
1664 END IF;
1665 RETURN(l_return_status);
1666 END populate_new_record;
1667 -------------------------------------------
1668 -- Set_Attributes for:OKL_SIF_RET_ERRORS --
1669 -------------------------------------------
1670 FUNCTION Set_Attributes (
1671 p_srm_rec IN srm_rec_type,
1672 x_srm_rec OUT NOCOPY srm_rec_type
1673 ) RETURN VARCHAR2 IS
1674 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1675 BEGIN
1676 x_srm_rec := p_srm_rec;
1677 RETURN(l_return_status);
1678 END Set_Attributes;
1679 BEGIN
1680 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1681 p_init_msg_list,
1682 '_PVT',
1683 x_return_status);
1684 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1685 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1686 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1687 RAISE OKC_API.G_EXCEPTION_ERROR;
1688 END IF;
1689 --- Setting item attributes
1690 l_return_status := Set_Attributes(
1691 p_srm_rec, -- IN
1692 l_srm_rec); -- OUT
1693 --- If any errors happen abort API
1694 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1695 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1696 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1697 RAISE OKC_API.G_EXCEPTION_ERROR;
1698 END IF;
1699 l_return_status := populate_new_record(l_srm_rec, l_def_srm_rec);
1700 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1701 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1702 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1703 RAISE OKC_API.G_EXCEPTION_ERROR;
1704 END IF;
1705 UPDATE OKL_SIF_RET_ERRORS
1706 SET SIR_ID = l_def_srm_rec.sir_id,
1707 ERROR_CODE = l_def_srm_rec.error_code,
1708 ERROR_MESSAGE = l_def_srm_rec.error_message,
1709 TAG_NAME = l_def_srm_rec.tag_name,
1710 TAG_ATTRIBUTE_NAME = l_def_srm_rec.tag_attribute_name,
1711 TAG_ATTRIBUTE_VALUE = l_def_srm_rec.tag_attribute_value,
1712 DESCRIPTION = l_def_srm_rec.description,
1713 STREAM_INTERFACE_ATTRIBUTE01 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE01,
1714 STREAM_INTERFACE_ATTRIBUTE02 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE02,
1715 STREAM_INTERFACE_ATTRIBUTE03 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE03,
1716 STREAM_INTERFACE_ATTRIBUTE04 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE04,
1717 STREAM_INTERFACE_ATTRIBUTE05 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE05,
1718 STREAM_INTERFACE_ATTRIBUTE06 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE06,
1719 STREAM_INTERFACE_ATTRIBUTE07 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE07,
1720 STREAM_INTERFACE_ATTRIBUTE08 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE08,
1721 STREAM_INTERFACE_ATTRIBUTE09 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE09,
1722 STREAM_INTERFACE_ATTRIBUTE10 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE10,
1723 STREAM_INTERFACE_ATTRIBUTE11 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE11,
1724 STREAM_INTERFACE_ATTRIBUTE12 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE12,
1725 STREAM_INTERFACE_ATTRIBUTE13 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE13,
1726 STREAM_INTERFACE_ATTRIBUTE14 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE14,
1727 STREAM_INTERFACE_ATTRIBUTE15 = l_def_srm_rec.STREAM_INTERFACE_ATTRIBUTE15,
1728 OBJECT_VERSION_NUMBER = l_def_srm_rec.object_version_number,
1729 CREATED_BY = l_def_srm_rec.created_by,
1730 LAST_UPDATED_BY = l_def_srm_rec.last_updated_by,
1731 CREATION_DATE = l_def_srm_rec.creation_date,
1732 LAST_UPDATE_DATE = l_def_srm_rec.last_update_date,
1733 LAST_UPDATE_LOGIN = l_def_srm_rec.last_update_login
1734 WHERE ID = l_def_srm_rec.id;
1735
1736 x_srm_rec := l_def_srm_rec;
1737 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1738 EXCEPTION
1739 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1740 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1741 (
1742 l_api_name,
1743 G_PKG_NAME,
1744 'OKC_API.G_RET_STS_ERROR',
1745 x_msg_count,
1746 x_msg_data,
1747 '_PVT'
1748 );
1749 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1750 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1751 (
1752 l_api_name,
1753 G_PKG_NAME,
1754 'OKC_API.G_RET_STS_UNEXP_ERROR',
1755 x_msg_count,
1756 x_msg_data,
1757 '_PVT'
1758 );
1759 WHEN OTHERS THEN
1760 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1761 (
1762 l_api_name,
1763 G_PKG_NAME,
1764 'OTHERS',
1765 x_msg_count,
1766 x_msg_data,
1767 '_PVT'
1768 );
1769 END update_row;
1770 -----------------------------------------
1771 -- update_row for:OKL_SIF_RET_ERRORS_V --
1772 -----------------------------------------
1773 PROCEDURE update_row(
1774 p_api_version IN NUMBER,
1775 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1776 x_return_status OUT NOCOPY VARCHAR2,
1777 x_msg_count OUT NOCOPY NUMBER,
1778 x_msg_data OUT NOCOPY VARCHAR2,
1779 p_srmv_rec IN srmv_rec_type,
1780 x_srmv_rec OUT NOCOPY srmv_rec_type) IS
1781
1782 l_api_version CONSTANT NUMBER := 1;
1783 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1784 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1785 l_srmv_rec srmv_rec_type := p_srmv_rec;
1786 l_def_srmv_rec srmv_rec_type;
1787 l_srm_rec srm_rec_type;
1788 lx_srm_rec srm_rec_type;
1789 -------------------------------
1790 -- FUNCTION fill_who_columns --
1791 -------------------------------
1792 FUNCTION fill_who_columns (
1793 p_srmv_rec IN srmv_rec_type
1794 ) RETURN srmv_rec_type IS
1795 l_srmv_rec srmv_rec_type := p_srmv_rec;
1796 BEGIN
1797 l_srmv_rec.LAST_UPDATE_DATE := SYSDATE;
1798 l_srmv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1799 l_srmv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1800 RETURN(l_srmv_rec);
1801 END fill_who_columns;
1802 ----------------------------------
1803 -- FUNCTION populate_new_record --
1804 ----------------------------------
1805 FUNCTION populate_new_record (
1806 p_srmv_rec IN srmv_rec_type,
1807 x_srmv_rec OUT NOCOPY srmv_rec_type
1808 ) RETURN VARCHAR2 IS
1809 l_srmv_rec srmv_rec_type;
1810 l_row_notfound BOOLEAN := TRUE;
1811 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1812 BEGIN
1813 x_srmv_rec := p_srmv_rec;
1814 -- Get current database values
1815 l_srmv_rec := get_rec(p_srmv_rec, l_row_notfound);
1816 IF (l_row_notfound) THEN
1817 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1818 END IF;
1819 IF (x_srmv_rec.id = OKC_API.G_MISS_NUM)
1820 THEN
1821 x_srmv_rec.id := l_srmv_rec.id;
1822 END IF;
1823 IF (x_srmv_rec.error_code = OKC_API.G_MISS_CHAR)
1824 THEN
1825 x_srmv_rec.error_code := l_srmv_rec.error_code;
1826 END IF;
1827 IF (x_srmv_rec.description = OKC_API.G_MISS_CHAR)
1828 THEN
1829 x_srmv_rec.description := l_srmv_rec.description;
1830 END IF;
1831 IF (x_srmv_rec.tag_attribute_name = OKC_API.G_MISS_CHAR)
1832 THEN
1833 x_srmv_rec.tag_attribute_name := l_srmv_rec.tag_attribute_name;
1834 END IF;
1835 IF (x_srmv_rec.tag_name = OKC_API.G_MISS_CHAR)
1836 THEN
1837 x_srmv_rec.tag_name := l_srmv_rec.tag_name;
1838 END IF;
1839 IF (x_srmv_rec.sir_id = OKC_API.G_MISS_NUM)
1840 THEN
1841 x_srmv_rec.sir_id := l_srmv_rec.sir_id;
1842 END IF;
1843 IF (x_srmv_rec.error_message = OKC_API.G_MISS_CHAR)
1844 THEN
1845 x_srmv_rec.error_message := l_srmv_rec.error_message;
1846 END IF;
1847 IF (x_srmv_rec.tag_attribute_value = OKC_API.G_MISS_CHAR)
1848 THEN
1849 x_srmv_rec.tag_attribute_value := l_srmv_rec.tag_attribute_value;
1850 END IF;
1851 IF (x_srmv_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR)
1852 THEN
1853 x_srmv_rec.stream_interface_attribute01 := l_srmv_rec.stream_interface_attribute01;
1854 END IF;
1855 IF (x_srmv_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR)
1856 THEN
1857 x_srmv_rec.stream_interface_attribute02 := l_srmv_rec.stream_interface_attribute02;
1858 END IF;
1859 IF (x_srmv_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR)
1860 THEN
1861 x_srmv_rec.stream_interface_attribute03 := l_srmv_rec.stream_interface_attribute03;
1862 END IF;
1863 IF (x_srmv_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR)
1864 THEN
1865 x_srmv_rec.stream_interface_attribute04 := l_srmv_rec.stream_interface_attribute04;
1866 END IF;
1867 IF (x_srmv_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR)
1868 THEN
1869 x_srmv_rec.stream_interface_attribute05 := l_srmv_rec.stream_interface_attribute05;
1870 END IF;
1871 IF (x_srmv_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR)
1872 THEN
1873 x_srmv_rec.stream_interface_attribute06 := l_srmv_rec.stream_interface_attribute06;
1874 END IF;
1875 IF (x_srmv_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR)
1876 THEN
1877 x_srmv_rec.stream_interface_attribute07 := l_srmv_rec.stream_interface_attribute07;
1878 END IF;
1879 IF (x_srmv_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR)
1880 THEN
1881 x_srmv_rec.stream_interface_attribute08 := l_srmv_rec.stream_interface_attribute08;
1882 END IF;
1883 IF (x_srmv_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR)
1884 THEN
1885 x_srmv_rec.stream_interface_attribute09 := l_srmv_rec.stream_interface_attribute09;
1886 END IF;
1887 IF (x_srmv_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR)
1888 THEN
1889 x_srmv_rec.stream_interface_attribute10 := l_srmv_rec.stream_interface_attribute10;
1890 END IF;
1891 IF (x_srmv_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR)
1892 THEN
1893 x_srmv_rec.stream_interface_attribute11 := l_srmv_rec.stream_interface_attribute11;
1894 END IF;
1895 IF (x_srmv_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR)
1896 THEN
1897 x_srmv_rec.stream_interface_attribute12 := l_srmv_rec.stream_interface_attribute12;
1898 END IF;
1899 IF (x_srmv_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR)
1900 THEN
1901 x_srmv_rec.stream_interface_attribute13 := l_srmv_rec.stream_interface_attribute13;
1902 END IF;
1903 IF (x_srmv_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR)
1904 THEN
1905 x_srmv_rec.stream_interface_attribute14 := l_srmv_rec.stream_interface_attribute14;
1906 END IF;
1907 IF (x_srmv_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR)
1908 THEN
1909 x_srmv_rec.stream_interface_attribute15 := l_srmv_rec.stream_interface_attribute15;
1910 END IF;
1911 IF (x_srmv_rec.object_version_number = OKC_API.G_MISS_NUM)
1912 THEN
1913 x_srmv_rec.object_version_number := l_srmv_rec.object_version_number;
1914 END IF;
1915 IF (x_srmv_rec.created_by = OKC_API.G_MISS_NUM)
1916 THEN
1917 x_srmv_rec.created_by := l_srmv_rec.created_by;
1918 END IF;
1919 IF (x_srmv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1920 THEN
1921 x_srmv_rec.last_updated_by := l_srmv_rec.last_updated_by;
1922 END IF;
1923 IF (x_srmv_rec.creation_date = OKC_API.G_MISS_DATE)
1924 THEN
1925 x_srmv_rec.creation_date := l_srmv_rec.creation_date;
1926 END IF;
1927 IF (x_srmv_rec.last_update_date = OKC_API.G_MISS_DATE)
1928 THEN
1929 x_srmv_rec.last_update_date := l_srmv_rec.last_update_date;
1930 END IF;
1931 IF (x_srmv_rec.last_update_login = OKC_API.G_MISS_NUM)
1932 THEN
1933 x_srmv_rec.last_update_login := l_srmv_rec.last_update_login;
1934 END IF;
1935 RETURN(l_return_status);
1936 END populate_new_record;
1937 ---------------------------------------------
1938 -- Set_Attributes for:OKL_SIF_RET_ERRORS_V --
1939 ---------------------------------------------
1940 FUNCTION Set_Attributes (
1941 p_srmv_rec IN srmv_rec_type,
1942 x_srmv_rec OUT NOCOPY srmv_rec_type
1943 ) RETURN VARCHAR2 IS
1944 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1945 BEGIN
1946 x_srmv_rec := p_srmv_rec;
1947 x_srmv_rec.OBJECT_VERSION_NUMBER := NVL(x_srmv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1948 RETURN(l_return_status);
1949 END Set_Attributes;
1950 BEGIN
1951 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1952 G_PKG_NAME,
1953 p_init_msg_list,
1954 l_api_version,
1955 p_api_version,
1956 '_PVT',
1957 x_return_status);
1958 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1959 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1960 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1961 RAISE OKC_API.G_EXCEPTION_ERROR;
1962 END IF;
1963 --- Setting item attributes
1964 l_return_status := Set_Attributes(
1965 p_srmv_rec, -- IN
1966 l_srmv_rec); -- OUT
1967 --- If any errors happen abort API
1968 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1969 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1970 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1971 RAISE OKC_API.G_EXCEPTION_ERROR;
1972 END IF;
1973 l_return_status := populate_new_record(l_srmv_rec, l_def_srmv_rec);
1974 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1975 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1976 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1977 RAISE OKC_API.G_EXCEPTION_ERROR;
1978 END IF;
1979 l_def_srmv_rec := fill_who_columns(l_def_srmv_rec);
1980 --- Validate all non-missing attributes (Item Level Validation)
1981 l_return_status := Validate_Attributes(l_def_srmv_rec);
1982 --- If any errors happen abort API
1983 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1984 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1985 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1986 RAISE OKC_API.G_EXCEPTION_ERROR;
1987 END IF;
1988 l_return_status := Validate_Record(l_def_srmv_rec);
1989 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1990 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1991 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1992 RAISE OKC_API.G_EXCEPTION_ERROR;
1993 END IF;
1994
1995 --------------------------------------
1996 -- Move VIEW record to "Child" records
1997 --------------------------------------
1998 migrate(l_def_srmv_rec, l_srm_rec);
1999 --------------------------------------------
2000 -- Call the UPDATE_ROW for each child record
2001 --------------------------------------------
2002 update_row(
2003 p_init_msg_list,
2004 x_return_status,
2005 x_msg_count,
2006 x_msg_data,
2007 l_srm_rec,
2008 lx_srm_rec
2009 );
2010 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2011 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2012 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2013 RAISE OKC_API.G_EXCEPTION_ERROR;
2014 END IF;
2015 migrate(lx_srm_rec, l_def_srmv_rec);
2016 x_srmv_rec := l_def_srmv_rec;
2017 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2018 EXCEPTION
2019 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2020 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2021 (
2022 l_api_name,
2023 G_PKG_NAME,
2024 'OKC_API.G_RET_STS_ERROR',
2025 x_msg_count,
2026 x_msg_data,
2027 '_PVT'
2028 );
2029 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2030 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2031 (
2032 l_api_name,
2033 G_PKG_NAME,
2034 'OKC_API.G_RET_STS_UNEXP_ERROR',
2035 x_msg_count,
2036 x_msg_data,
2037 '_PVT'
2038 );
2039 WHEN OTHERS THEN
2040 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2041 (
2042 l_api_name,
2043 G_PKG_NAME,
2044 'OTHERS',
2045 x_msg_count,
2046 x_msg_data,
2047 '_PVT'
2048 );
2049 END update_row;
2050 ----------------------------------------
2051 -- PL/SQL TBL update_row for:SRMV_TBL --
2052 ----------------------------------------
2053 PROCEDURE update_row(
2054 p_api_version IN NUMBER,
2055 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2056 x_return_status OUT NOCOPY VARCHAR2,
2057 x_msg_count OUT NOCOPY NUMBER,
2058 x_msg_data OUT NOCOPY VARCHAR2,
2059 p_srmv_tbl IN srmv_tbl_type,
2060 x_srmv_tbl OUT NOCOPY srmv_tbl_type) IS
2061
2062 l_api_version CONSTANT NUMBER := 1;
2063 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2064 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2065 i NUMBER := 0;
2066 -- START change : akjain, 11/05/2001
2067 -- Adding OverAll Status Flag
2068 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2069 -- END change : akjain
2070
2071 BEGIN
2072 OKC_API.init_msg_list(p_init_msg_list);
2073 -- Make sure PL/SQL table has records in it before passing
2074 IF (p_srmv_tbl.COUNT > 0) THEN
2075 i := p_srmv_tbl.FIRST;
2076 LOOP
2077 update_row (
2078 p_api_version => p_api_version,
2079 p_init_msg_list => OKC_API.G_FALSE,
2080 x_return_status => x_return_status,
2081 x_msg_count => x_msg_count,
2082 x_msg_data => x_msg_data,
2083 p_srmv_rec => p_srmv_tbl(i),
2084 x_srmv_rec => x_srmv_tbl(i));
2085 -- START change : akjain, 11/05/2001
2086 -- store the highest degree of error
2087 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2088 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2089 l_overall_status := x_return_status;
2090 END IF;
2091 END IF;
2092 -- END change : akjain
2093
2094 EXIT WHEN (i = p_srmv_tbl.LAST);
2095 i := p_srmv_tbl.NEXT(i);
2096 END LOOP;
2097 -- START change : akjain, 11/05/2001
2098 -- return overall status
2099 x_return_status := l_overall_status;
2100 -- END change : akjain
2101
2102 END IF;
2103 EXCEPTION
2104 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2105 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2106 (
2107 l_api_name,
2108 G_PKG_NAME,
2109 'OKC_API.G_RET_STS_ERROR',
2110 x_msg_count,
2111 x_msg_data,
2112 '_PVT'
2113 );
2114 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2115 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2116 (
2117 l_api_name,
2118 G_PKG_NAME,
2119 'OKC_API.G_RET_STS_UNEXP_ERROR',
2120 x_msg_count,
2121 x_msg_data,
2122 '_PVT'
2123 );
2124 WHEN OTHERS THEN
2125 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2126 (
2127 l_api_name,
2128 G_PKG_NAME,
2129 'OTHERS',
2130 x_msg_count,
2131 x_msg_data,
2132 '_PVT'
2133 );
2134 END update_row;
2135
2136 ---------------------------------------------------------------------------
2137 -- PROCEDURE delete_row
2138 ---------------------------------------------------------------------------
2139 ---------------------------------------
2140 -- delete_row for:OKL_SIF_RET_ERRORS --
2141 ---------------------------------------
2142 PROCEDURE delete_row(
2143 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2144 x_return_status OUT NOCOPY VARCHAR2,
2145 x_msg_count OUT NOCOPY NUMBER,
2146 x_msg_data OUT NOCOPY VARCHAR2,
2147 p_srm_rec IN srm_rec_type) IS
2148
2149 l_api_version CONSTANT NUMBER := 1;
2150 l_api_name CONSTANT VARCHAR2(30) := 'ERRORS_delete_row';
2151 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2152 l_srm_rec srm_rec_type:= p_srm_rec;
2153 l_row_notfound BOOLEAN := TRUE;
2154 BEGIN
2155 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2156 p_init_msg_list,
2157 '_PVT',
2158 x_return_status);
2159 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2160 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2161 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2162 RAISE OKC_API.G_EXCEPTION_ERROR;
2163 END IF;
2164 DELETE FROM OKL_SIF_RET_ERRORS
2165 WHERE ID = l_srm_rec.id;
2166
2167 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2168 EXCEPTION
2169 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2170 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2171 (
2172 l_api_name,
2173 G_PKG_NAME,
2174 'OKC_API.G_RET_STS_ERROR',
2175 x_msg_count,
2176 x_msg_data,
2177 '_PVT'
2178 );
2179 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2180 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2181 (
2182 l_api_name,
2183 G_PKG_NAME,
2184 'OKC_API.G_RET_STS_UNEXP_ERROR',
2185 x_msg_count,
2186 x_msg_data,
2187 '_PVT'
2188 );
2189 WHEN OTHERS THEN
2190 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2191 (
2192 l_api_name,
2193 G_PKG_NAME,
2194 'OTHERS',
2195 x_msg_count,
2196 x_msg_data,
2197 '_PVT'
2198 );
2199 END delete_row;
2200 -----------------------------------------
2201 -- delete_row for:OKL_SIF_RET_ERRORS_V --
2202 -----------------------------------------
2203 PROCEDURE delete_row(
2204 p_api_version IN NUMBER,
2205 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2206 x_return_status OUT NOCOPY VARCHAR2,
2207 x_msg_count OUT NOCOPY NUMBER,
2208 x_msg_data OUT NOCOPY VARCHAR2,
2209 p_srmv_rec IN srmv_rec_type) IS
2210
2211 l_api_version CONSTANT NUMBER := 1;
2212 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2213 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2214 l_srmv_rec srmv_rec_type := p_srmv_rec;
2215 l_srm_rec srm_rec_type;
2216 BEGIN
2217 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2218 G_PKG_NAME,
2219 p_init_msg_list,
2220 l_api_version,
2221 p_api_version,
2222 '_PVT',
2223 x_return_status);
2224 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2225 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2226 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2227 RAISE OKC_API.G_EXCEPTION_ERROR;
2228 END IF;
2229 --------------------------------------
2230 -- Move VIEW record to "Child" records
2231 --------------------------------------
2232 migrate(l_srmv_rec, l_srm_rec);
2233 --------------------------------------------
2234 -- Call the DELETE_ROW for each child record
2235 --------------------------------------------
2236 delete_row(
2237 p_init_msg_list,
2238 x_return_status,
2239 x_msg_count,
2240 x_msg_data,
2241 l_srm_rec
2242 );
2243 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2244 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2245 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2246 RAISE OKC_API.G_EXCEPTION_ERROR;
2247 END IF;
2248 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2249 EXCEPTION
2250 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2251 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2252 (
2253 l_api_name,
2254 G_PKG_NAME,
2255 'OKC_API.G_RET_STS_ERROR',
2256 x_msg_count,
2257 x_msg_data,
2258 '_PVT'
2259 );
2260 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2261 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2262 (
2263 l_api_name,
2264 G_PKG_NAME,
2265 'OKC_API.G_RET_STS_UNEXP_ERROR',
2266 x_msg_count,
2267 x_msg_data,
2268 '_PVT'
2269 );
2270 WHEN OTHERS THEN
2271 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2272 (
2273 l_api_name,
2274 G_PKG_NAME,
2275 'OTHERS',
2276 x_msg_count,
2277 x_msg_data,
2278 '_PVT'
2279 );
2280 END delete_row;
2281 ----------------------------------------
2282 -- PL/SQL TBL delete_row for:SRMV_TBL --
2283 ----------------------------------------
2284 PROCEDURE delete_row(
2285 p_api_version IN NUMBER,
2286 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2287 x_return_status OUT NOCOPY VARCHAR2,
2288 x_msg_count OUT NOCOPY NUMBER,
2289 x_msg_data OUT NOCOPY VARCHAR2,
2290 p_srmv_tbl IN srmv_tbl_type) IS
2291
2292 l_api_version CONSTANT NUMBER := 1;
2293 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2294 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2295 i NUMBER := 0;
2296 -- START change : akjain, 11/05/2001
2297 -- Adding OverAll Status Flag
2298 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2299 -- END change : akjain
2300
2301 BEGIN
2302 OKC_API.init_msg_list(p_init_msg_list);
2303 -- Make sure PL/SQL table has records in it before passing
2304 IF (p_srmv_tbl.COUNT > 0) THEN
2305 i := p_srmv_tbl.FIRST;
2306 LOOP
2307 delete_row (
2308 p_api_version => p_api_version,
2309 p_init_msg_list => OKC_API.G_FALSE,
2310 x_return_status => x_return_status,
2311 x_msg_count => x_msg_count,
2312 x_msg_data => x_msg_data,
2313 p_srmv_rec => p_srmv_tbl(i));
2314 -- START change : akjain, 11/05/2001
2315 -- store the highest degree of error
2316 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2317 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2318 l_overall_status := x_return_status;
2319 END IF;
2320 END IF;
2321 -- END change : akjain
2322
2323 EXIT WHEN (i = p_srmv_tbl.LAST);
2324 i := p_srmv_tbl.NEXT(i);
2325 END LOOP;
2326 -- START change : akjain, 11/05/2001
2327 -- return overall status
2328 x_return_status := l_overall_status;
2329 -- END change : akjain
2330
2331 END IF;
2332 EXCEPTION
2333 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2334 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2335 (
2336 l_api_name,
2337 G_PKG_NAME,
2338 'OKC_API.G_RET_STS_ERROR',
2339 x_msg_count,
2340 x_msg_data,
2341 '_PVT'
2342 );
2343 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2344 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2345 (
2346 l_api_name,
2347 G_PKG_NAME,
2348 'OKC_API.G_RET_STS_UNEXP_ERROR',
2349 x_msg_count,
2350 x_msg_data,
2351 '_PVT'
2352 );
2353 WHEN OTHERS THEN
2354 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2355 (
2356 l_api_name,
2357 G_PKG_NAME,
2358 'OTHERS',
2359 x_msg_count,
2360 x_msg_data,
2361 '_PVT'
2362 );
2363 END delete_row;
2364 END OKL_SRM_PVT;