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