[Home] [Help]
PACKAGE BODY: APPS.OKL_SRS_PVT
Source
1 PACKAGE BODY OKL_SRS_PVT AS
2 /* $Header: OKLSSRSB.pls 120.2 2005/10/30 04:44:33 appldev 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_STRMS
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_srs_rec IN srs_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN srs_rec_type IS
42 CURSOR srs_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 STREAM_TYPE_NAME,
46 INDEX_NUMBER,
47 ACTIVITY_TYPE,
48 SEQUENCE_NUMBER,
49 SRE_DATE,
50 AMOUNT,
51 SIR_ID,
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_Strms
74 WHERE okl_sif_ret_strms.id = p_id;
75 l_srs_pk srs_pk_csr%ROWTYPE;
76 l_srs_rec srs_rec_type;
77 BEGIN
78 x_no_data_found := TRUE;
79 -- Get current database values
80 OPEN srs_pk_csr (p_srs_rec.id);
81 FETCH srs_pk_csr INTO
82 l_srs_rec.ID,
83 l_srs_rec.STREAM_TYPE_NAME,
84 l_srs_rec.INDEX_NUMBER,
85 l_srs_rec.ACTIVITY_TYPE,
86 l_srs_rec.SEQUENCE_NUMBER,
87 l_srs_rec.SRE_DATE,
88 l_srs_rec.AMOUNT,
89 l_srs_rec.SIR_ID,
90 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE01,
91 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE02,
92 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE03,
93 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE04,
94 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE05,
95 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE06,
96 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE07,
97 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE08,
98 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE09,
99 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE10,
100 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE11,
101 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE12,
102 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE13,
103 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE14,
104 l_srs_rec.STREAM_INTERFACE_ATTRIBUTE15,
105 l_srs_rec.OBJECT_VERSION_NUMBER,
106 l_srs_rec.CREATED_BY,
107 l_srs_rec.LAST_UPDATED_BY,
108 l_srs_rec.CREATION_DATE,
109 l_srs_rec.LAST_UPDATE_DATE,
110 l_srs_rec.LAST_UPDATE_LOGIN;
111 x_no_data_found := srs_pk_csr%NOTFOUND;
112 CLOSE srs_pk_csr;
113 RETURN(l_srs_rec);
114 END get_rec;
115
116 FUNCTION get_rec (
117 p_srs_rec IN srs_rec_type
118 ) RETURN srs_rec_type IS
119 l_row_notfound BOOLEAN := TRUE;
120 BEGIN
121 RETURN(get_rec(p_srs_rec, l_row_notfound));
122 END get_rec;
123 ---------------------------------------------------------------------------
124 -- FUNCTION get_rec for: OKL_SIF_RET_STRMS_V
125 ---------------------------------------------------------------------------
126 FUNCTION get_rec (
127 p_srsv_rec IN srsv_rec_type,
128 x_no_data_found OUT NOCOPY BOOLEAN
129 ) RETURN srsv_rec_type IS
130 CURSOR srsv_pk_csr (p_id IN NUMBER) IS
131 SELECT
132 ID,
133 STREAM_TYPE_NAME,
134 INDEX_NUMBER,
135 ACTIVITY_TYPE,
136 SEQUENCE_NUMBER,
137 SRE_DATE,
138 AMOUNT,
139 SIR_ID,
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_Strms_V
162 WHERE okl_sif_ret_strms_v.id = p_id;
163 l_srsv_pk srsv_pk_csr%ROWTYPE;
164 l_srsv_rec srsv_rec_type;
165 BEGIN
166 x_no_data_found := TRUE;
167 -- Get current database values
168 OPEN srsv_pk_csr (p_srsv_rec.id);
169 FETCH srsv_pk_csr INTO
170 l_srsv_rec.ID,
171 l_srsv_rec.STREAM_TYPE_NAME,
172 l_srsv_rec.INDEX_NUMBER,
173 l_srsv_rec.ACTIVITY_TYPE,
174 l_srsv_rec.SEQUENCE_NUMBER,
175 l_srsv_rec.SRE_DATE,
176 l_srsv_rec.AMOUNT,
177 l_srsv_rec.SIR_ID,
178 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE01,
179 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE02,
180 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE03,
181 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE04,
182 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE05,
183 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE06,
184 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE07,
185 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE08,
186 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE09,
187 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE10,
188 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE11,
189 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE12,
190 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE13,
191 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE14,
192 l_srsv_rec.STREAM_INTERFACE_ATTRIBUTE15,
193 l_srsv_rec.OBJECT_VERSION_NUMBER,
194 l_srsv_rec.CREATED_BY,
195 l_srsv_rec.LAST_UPDATED_BY,
196 l_srsv_rec.CREATION_DATE,
197 l_srsv_rec.LAST_UPDATE_DATE,
198 l_srsv_rec.LAST_UPDATE_LOGIN;
199 x_no_data_found := srsv_pk_csr%NOTFOUND;
200 CLOSE srsv_pk_csr;
201 RETURN(l_srsv_rec);
202 END get_rec;
203
204 FUNCTION get_rec (
205 p_srsv_rec IN srsv_rec_type
206 ) RETURN srsv_rec_type IS
207 l_row_notfound BOOLEAN := TRUE;
208 BEGIN
209 RETURN(get_rec(p_srsv_rec, l_row_notfound));
210 END get_rec;
211
212 ---------------------------------------------------------
213 -- FUNCTION null_out_defaults for: OKL_SIF_RET_STRMS_V --
214 ---------------------------------------------------------
215 FUNCTION null_out_defaults (
216 p_srsv_rec IN srsv_rec_type
217 ) RETURN srsv_rec_type IS
218 l_srsv_rec srsv_rec_type := p_srsv_rec;
219 BEGIN
220 IF (l_srsv_rec.stream_type_name = OKC_API.G_MISS_CHAR) THEN
221 l_srsv_rec.stream_type_name := NULL;
222 END IF;
223 IF (l_srsv_rec.index_number = OKC_API.G_MISS_NUM) THEN
224 l_srsv_rec.index_number := NULL;
225 END IF;
226 IF (l_srsv_rec.activity_type = OKC_API.G_MISS_CHAR) THEN
227 l_srsv_rec.activity_type := NULL;
228 END IF;
229 IF (l_srsv_rec.sequence_number = OKC_API.G_MISS_NUM) THEN
230 l_srsv_rec.sequence_number := NULL;
231 END IF;
232 IF (l_srsv_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR) THEN
233 l_srsv_rec.stream_interface_attribute01 := NULL;
234 END IF;
235 IF (l_srsv_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR) THEN
236 l_srsv_rec.stream_interface_attribute02 := NULL;
237 END IF;
238 IF (l_srsv_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR) THEN
239 l_srsv_rec.stream_interface_attribute03 := NULL;
240 END IF;
241 IF (l_srsv_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR) THEN
242 l_srsv_rec.stream_interface_attribute04 := NULL;
243 END IF;
244 IF (l_srsv_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR) THEN
245 l_srsv_rec.stream_interface_attribute05 := NULL;
246 END IF;
247 IF (l_srsv_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR) THEN
248 l_srsv_rec.stream_interface_attribute06 := NULL;
249 END IF;
250 IF (l_srsv_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR) THEN
251 l_srsv_rec.stream_interface_attribute07 := NULL;
252 END IF;
253 IF (l_srsv_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR) THEN
254 l_srsv_rec.stream_interface_attribute08 := NULL;
255 END IF;
256 IF (l_srsv_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR) THEN
257 l_srsv_rec.stream_interface_attribute09 := NULL;
258 END IF;
259 IF (l_srsv_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR) THEN
260 l_srsv_rec.stream_interface_attribute10 := NULL;
261 END IF;
262 IF (l_srsv_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR) THEN
263 l_srsv_rec.stream_interface_attribute11 := NULL;
264 END IF;
265 IF (l_srsv_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR) THEN
266 l_srsv_rec.stream_interface_attribute12 := NULL;
267 END IF;
268 IF (l_srsv_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR) THEN
269 l_srsv_rec.stream_interface_attribute13 := NULL;
270 END IF;
271 IF (l_srsv_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR) THEN
272 l_srsv_rec.stream_interface_attribute14 := NULL;
273 END IF;
274 IF (l_srsv_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR) THEN
275 l_srsv_rec.stream_interface_attribute15 := NULL;
276 END IF;
277
278 IF (l_srsv_rec.sre_date = OKC_API.G_MISS_DATE) THEN
279 l_srsv_rec.sre_date := NULL;
280 END IF;
281 IF (l_srsv_rec.amount = OKC_API.G_MISS_NUM) THEN
282 l_srsv_rec.amount := NULL;
283 END IF;
284 IF (l_srsv_rec.sir_id = OKC_API.G_MISS_NUM) THEN
285 l_srsv_rec.sir_id := NULL;
286 END IF;
287 IF (l_srsv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
288 l_srsv_rec.object_version_number := NULL;
289 END IF;
290 IF (l_srsv_rec.created_by = OKC_API.G_MISS_NUM) THEN
291 l_srsv_rec.created_by := NULL;
292 END IF;
293 IF (l_srsv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
294 l_srsv_rec.last_updated_by := NULL;
295 END IF;
296 IF (l_srsv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
297 l_srsv_rec.creation_date := NULL;
298 END IF;
299 IF (l_srsv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
300 l_srsv_rec.last_update_date := NULL;
301 END IF;
302 IF (l_srsv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
303 l_srsv_rec.last_update_login := NULL;
304 END IF;
305 RETURN(l_srsv_rec);
306 END null_out_defaults;
307 /*
308 -- TAPI CODE COMMENTED OUT IN FAVOUR OF WRITING SEPARATE PROCEDURES FOR EACH ATTRIBUTE/COLUMN
309 ---------------------------------------------------------------------------
310 -- PROCEDURE Validate_Attributes
311 ---------------------------------------------------------------------------
312 -------------------------------------------------
313 -- Validate_Attributes for:OKL_SIF_RET_STRMS_V --
314 -------------------------------------------------
315 FUNCTION Validate_Attributes (
316 p_srsv_rec IN srsv_rec_type
317 ) RETURN VARCHAR2 IS
318 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
319 BEGIN
320 IF p_srsv_rec.id = OKC_API.G_MISS_NUM OR
321 p_srsv_rec.id IS NULL
322 THEN
323 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
324 l_return_status := OKC_API.G_RET_STS_ERROR;
325 ELSIF p_srsv_rec.stream_type_name = OKC_API.G_MISS_CHAR OR
326 p_srsv_rec.stream_type_name IS NULL
327 THEN
328 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'stream_type_name');
329 l_return_status := OKC_API.G_RET_STS_ERROR;
330 ELSIF p_srsv_rec.sir_id = OKC_API.G_MISS_NUM OR
331 p_srsv_rec.sir_id IS NULL
332 THEN
333 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'sir_id');
334 l_return_status := OKC_API.G_RET_STS_ERROR;
335 ELSIF p_srsv_rec.object_version_number = OKC_API.G_MISS_NUM OR
336 p_srsv_rec.object_version_number IS NULL
337 THEN
338 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
339 l_return_status := OKC_API.G_RET_STS_ERROR;
340 END IF;
341 RETURN(l_return_status);
342 END Validate_Attributes;
343 */
344
345 /**
346 * Adding Individual Procedures for each Attribute that
347 * needs to be validated
348 */
349 ---------------------------------------------------------------------------
350 -- PROCEDURE Validate_Id
351 ---------------------------------------------------------------------------
352 -- Start of comments
353 --
354 -- Procedure Name : Validate_Id
355 -- Description :
356 -- Business Rules :
357 -- Parameters :
358 -- Version : 1.0
359 -- End of comments
360 ---------------------------------------------------------------------------
361 PROCEDURE Validate_Id(
362 p_srsv_rec IN srsv_rec_type,
363 x_return_status OUT NOCOPY VARCHAR2
364 ) IS
365
366 BEGIN
367 -- initialize return status
368 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
369
370 IF p_srsv_rec.id = Okc_Api.G_MISS_NUM OR
371 p_srsv_rec.id IS NULL
372 THEN
373 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
374 x_return_status := Okc_Api.G_RET_STS_ERROR;
375 RAISE G_EXCEPTION_HALT_VALIDATION;
376 END IF;
377
378 EXCEPTION
379 WHEN G_EXCEPTION_HALT_VALIDATION THEN
380 -- no processing necessary; validation can continue
381 -- with the next column
382 NULL;
383
384 WHEN OTHERS THEN
385 -- store SQL error message on message stack for caller
386 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
387 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
388 ,p_token1 => G_OKL_SQLCODE_TOKEN
389 ,p_token1_value => SQLCODE
390 ,p_token2 => G_OKL_SQLERRM_TOKEN
391 ,p_token2_value => SQLERRM);
392
393 -- notify caller of an UNEXPECTED error
394 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
395
396 END Validate_Id;
397
398 ---------------------------------------------------------------------------
399 -- PROCEDURE Validate_Object_Version_Number
400 ---------------------------------------------------------------------------
401 -- Start of comments
402 --
403 -- Procedure Name : Validate_Object_Version_Number
404 -- Description :
405 -- Business Rules :
406 -- Parameters :
407 -- Version : 1.0
408 -- End of comments
409 ---------------------------------------------------------------------------
410 PROCEDURE Validate_Object_Version_Number(
411 p_srsv_rec IN srsv_rec_type,
412 x_return_status OUT NOCOPY VARCHAR2
413 ) IS
414
415 BEGIN
416
417 -- initialize return status
418 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
419
420 IF p_srsv_rec.object_version_number = Okc_Api.G_MISS_NUM OR
421 p_srsv_rec.object_version_number IS NULL
422 THEN
423 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
424 x_return_status := Okc_Api.G_RET_STS_ERROR;
425 RAISE G_EXCEPTION_HALT_VALIDATION;
426 END IF;
427
428 EXCEPTION
429 WHEN G_EXCEPTION_HALT_VALIDATION THEN
430 -- no processing necessary; validation can continue
431 -- with the next column
432 NULL;
433
434 WHEN OTHERS THEN
435 -- store SQL error message on message stack for caller
436 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
437 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
438 ,p_token1 => G_OKL_SQLCODE_TOKEN
439 ,p_token1_value => SQLCODE
440 ,p_token2 => G_OKL_SQLERRM_TOKEN
441 ,p_token2_value => SQLERRM);
442
443 -- notify caller of an UNEXPECTED error
444 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
445
446 END Validate_Object_Version_Number;
447
448 ---------------------------------------------------------------------------
449 -- PROCEDURE Validate_Index_Number
450 ---------------------------------------------------------------------------
451 -- Start of comments
452 --
453 -- Procedure Name : Validate_Index_Number
454 -- Description :
455 -- Business Rules :
456 -- Parameters :
457 -- Version : 1.0
458 -- End of comments
459 ---------------------------------------------------------------------------
460 PROCEDURE Validate_Index_Number(
461 p_srsv_rec IN srsv_rec_type,
462 x_return_status OUT NOCOPY VARCHAR2
463 ) IS
464
465 BEGIN
466 -- initialize return status
467 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
468
469 IF p_srsv_rec.index_number = Okc_Api.G_MISS_NUM OR
470 p_srsv_rec.index_number IS NULL
471 THEN
472 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'index_number');
473 x_return_status := Okc_Api.G_RET_STS_ERROR;
474 RAISE G_EXCEPTION_HALT_VALIDATION;
475 END IF;
476
477 EXCEPTION
478 WHEN G_EXCEPTION_HALT_VALIDATION THEN
479 -- no processing necessary; validation can continue
480 -- with the next column
481 NULL;
482
483 WHEN OTHERS THEN
484 -- store SQL error message on message stack for caller
485 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
486 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
487 ,p_token1 => G_OKL_SQLCODE_TOKEN
488 ,p_token1_value => SQLCODE
489 ,p_token2 => G_OKL_SQLERRM_TOKEN
490 ,p_token2_value => SQLERRM);
491
492 -- notify caller of an UNEXPECTED error
493 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
494
495 END Validate_Index_Number;
496
497 ---------------------------------------------------------------------------
498 -- PROCEDURE Validate_Sre_Date
499 ---------------------------------------------------------------------------
500 -- Start of comments
501 --
502 -- Procedure Name : Validate_Sre_Date
503 -- Description :
504 -- Business Rules :
505 -- Parameters :
506 -- Version : 1.0
507 -- End of comments
508 ---------------------------------------------------------------------------
509 PROCEDURE Validate_Sre_Date(
510 p_srsv_rec IN srsv_rec_type,
511 x_return_status OUT NOCOPY VARCHAR2
512 ) IS
513
514 BEGIN
515 -- initialize return status
516 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
517
518 IF p_srsv_rec.Sre_Date = Okc_Api.G_MISS_DATE OR
519 p_srsv_rec.Sre_Date IS NULL
520 THEN
521 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Sre_Date');
522 x_return_status := Okc_Api.G_RET_STS_ERROR;
523 RAISE G_EXCEPTION_HALT_VALIDATION;
524 END IF;
525
526 EXCEPTION
527 WHEN G_EXCEPTION_HALT_VALIDATION THEN
528 -- no processing necessary; validation can continue
529 -- with the next column
530 NULL;
531
532 WHEN OTHERS THEN
533 -- store SQL error message on message stack for caller
534 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
535 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
536 ,p_token1 => G_OKL_SQLCODE_TOKEN
537 ,p_token1_value => SQLCODE
538 ,p_token2 => G_OKL_SQLERRM_TOKEN
539 ,p_token2_value => SQLERRM);
540
541 -- notify caller of an UNEXPECTED error
542 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
543
544 END Validate_Sre_Date;
545
546 ---------------------------------------------------------------------------
547 -- PROCEDURE Validate_Amount
548 ---------------------------------------------------------------------------
549 -- Start of comments
550 --
551 -- Procedure Name : Validate_Amount
552 -- Description :
553 -- Business Rules :
554 -- Parameters :
555 -- Version : 1.0
556 -- End of comments
557 ---------------------------------------------------------------------------
558 PROCEDURE Validate_Amount(
559 p_srsv_rec IN srsv_rec_type,
560 x_return_status OUT NOCOPY VARCHAR2
561 ) IS
562
563 BEGIN
564 -- initialize return status
565 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
566
567 IF p_srsv_rec.Amount = Okc_Api.G_MISS_NUM OR
568 p_srsv_rec.Amount IS NULL
569 THEN
570 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Amount');
571 x_return_status := Okc_Api.G_RET_STS_ERROR;
572 RAISE G_EXCEPTION_HALT_VALIDATION;
573 END IF;
574
575 EXCEPTION
576 WHEN G_EXCEPTION_HALT_VALIDATION THEN
577 -- no processing necessary; validation can continue
578 -- with the next column
579 NULL;
580
581 WHEN OTHERS THEN
582 -- store SQL error message on message stack for caller
583 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
584 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
585 ,p_token1 => G_OKL_SQLCODE_TOKEN
586 ,p_token1_value => SQLCODE
587 ,p_token2 => G_OKL_SQLERRM_TOKEN
588 ,p_token2_value => SQLERRM);
589
590 -- notify caller of an UNEXPECTED error
591 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
592
593 END Validate_Amount;
594
595 ---------------------------------------------------------------------------
596 -- PROCEDURE Validate_Stream_Type_Name
597 ---------------------------------------------------------------------------
598 -- Start of comments
599 --
600 -- Procedure Name : Validate_Stream_Type_Name
601 -- Description :
602 -- Business Rules :
603 -- Parameters :
604 -- Version : 1.0
605 -- End of comments
606 ---------------------------------------------------------------------------
607 PROCEDURE Validate_Stream_Type_Name(
608 p_srsv_rec IN srsv_rec_type,
609 x_return_status OUT NOCOPY VARCHAR2
610 ) IS
611
612 BEGIN
613
614 -- initialize return status
615 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
616
617 IF p_srsv_rec.Stream_Type_Name = Okc_Api.G_MISS_CHAR OR
618 p_srsv_rec.Stream_Type_Name IS NULL
619 THEN
620 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Stream_Type_Name');
621 x_return_status := Okc_Api.G_RET_STS_ERROR;
622 RAISE G_EXCEPTION_HALT_VALIDATION;
623 END IF;
624
625 EXCEPTION
626 WHEN G_EXCEPTION_HALT_VALIDATION THEN
627 -- no processing necessary; validation can continue
628 -- with the next column
629 NULL;
630
631 WHEN OTHERS THEN
632 -- store SQL error message on message stack for caller
633 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
634 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
635 ,p_token1 => G_OKL_SQLCODE_TOKEN
636 ,p_token1_value => SQLCODE
637 ,p_token2 => G_OKL_SQLERRM_TOKEN
638 ,p_token2_value => SQLERRM);
639
640 -- notify caller of an UNEXPECTED error
641 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
642
643 END Validate_Stream_Type_Name;
644
645 ---------------------------------------------------------------------------
646 -- PROCEDURE Validate_Sir_Id
647 ---------------------------------------------------------------------------
648 -- Start of comments
649 --
650 -- Procedure Name : Validate_Sir_Id
651 -- Description :
652 -- Business Rules :
653 -- Parameters :
654 -- Version : 1.0
655 -- End of comments
656 ---------------------------------------------------------------------------
657 PROCEDURE Validate_Sir_Id(
658 p_srsv_rec IN srsv_rec_type,
659 x_return_status OUT NOCOPY VARCHAR2
660 ) IS
661
662 l_dummy VARCHAR2(1) := '?';
663 l_row_not_found BOOLEAN := FALSE;
664
665 -- Cursor For OKL_SRY_SIR_FK;
666 CURSOR okl_sirv_pk_csr (p_id IN OKL_SIF_RETS_V.Id%TYPE) IS
667 SELECT '1'
668 FROM OKL_SIF_RETS_V
669 WHERE OKL_SIF_RETS_V.id = p_id;
670
671 BEGIN
672
673 -- initialize return status
674 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
675
676 IF p_srsv_rec.sir_id = Okc_Api.G_MISS_NUM OR
677 p_srsv_rec.sir_id IS NULL
678 THEN
679 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'sir_id');
680 x_return_status := Okc_Api.G_RET_STS_ERROR;
681 RAISE G_EXCEPTION_HALT_VALIDATION;
682 END IF;
683
684 OPEN okl_sirv_pk_csr(p_srsv_rec.sir_id);
685 FETCH okl_sirv_pk_csr INTO l_dummy;
686 l_row_not_found := okl_sirv_pk_csr%NOTFOUND;
687 CLOSE okl_sirv_pk_csr;
688
689 IF l_row_not_found THEN
690 Okc_Api.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'sir_id');
691 x_return_status := Okc_Api.G_RET_STS_ERROR;
692 END IF;
693
694 EXCEPTION
695 WHEN G_EXCEPTION_HALT_VALIDATION THEN
696 -- no processing necessary; validation can continue
697 -- with the next column
698 NULL;
699
700 WHEN OTHERS THEN
701 -- store SQL error message on message stack for caller
702 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
703 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
704 ,p_token1 => G_OKL_SQLCODE_TOKEN
705 ,p_token1_value => SQLCODE
706 ,p_token2 => G_OKL_SQLERRM_TOKEN
707 ,p_token2_value => SQLERRM);
708
709 -- notify caller of an UNEXPECTED error
710 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
711
712 -- verify that the cursor was closed
713 IF okl_sirv_pk_csr%ISOPEN THEN
714 CLOSE okl_sirv_pk_csr;
715 END IF;
716
717 END Validate_Sir_Id;
718
719 ---------------------------------------------------------------------------
720 -- FUNCTION Validate_Attributes
721 ---------------------------------------------------------------------------
722 -- Start of comments
723 --
724 -- Procedure Name : Validate_Attributes
725 -- Description :
726 -- Business Rules :
727 -- Parameters :
728 -- Version : 1.0
729 -- End of comments
730 ---------------------------------------------------------------------------
731
732 FUNCTION Validate_Attributes (
733 p_srsv_rec IN srsv_rec_type
734 ) RETURN VARCHAR2 IS
735
736 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
737 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
738 BEGIN
739 -- call each column-level validation
740
741 -- Validate_Id
742 Validate_Id(p_srsv_rec, x_return_status);
743 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
744 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
745 -- need to exit
746 l_return_status := x_return_status;
747 RAISE G_EXCEPTION_HALT_VALIDATION;
748 ELSE
749 -- there was an error
750 l_return_status := x_return_status;
751 END IF;
752 END IF;
753
754 -- Validate_Object_Version_Number
755 Validate_Object_Version_Number(p_srsv_rec, x_return_status);
756 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
757 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
758 -- need to exit
759 l_return_status := x_return_status;
760 RAISE G_EXCEPTION_HALT_VALIDATION;
761 ELSE
762 -- there was an error
763 l_return_status := x_return_status;
764 END IF;
765 END IF;
766
767 -- Validate_Stream_Type_Name
768 Validate_Stream_Type_Name(p_srsv_rec, x_return_status);
769 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
770 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
771 -- need to exit
772 l_return_status := x_return_status;
773 RAISE G_EXCEPTION_HALT_VALIDATION;
774 ELSE
775 -- there was an error
776 l_return_status := x_return_status;
777 END IF;
778 END IF;
779
780 -- Validate_Sre_Date
781 Validate_Sre_Date(p_srsv_rec, x_return_status);
782 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
783 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
784 -- need to exit
785 l_return_status := x_return_status;
786 RAISE G_EXCEPTION_HALT_VALIDATION;
787 ELSE
788 -- there was an error
789 l_return_status := x_return_status;
790 END IF;
791 END IF;
792
793 -- Validate_Amount
794 Validate_Amount(p_srsv_rec, x_return_status);
795 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
796 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
797 -- need to exit
798 l_return_status := x_return_status;
799 RAISE G_EXCEPTION_HALT_VALIDATION;
800 ELSE
801 -- there was an error
802 l_return_status := x_return_status;
803 END IF;
804 END IF;
805
806 -- Validate_Sir_id
807 Validate_Sir_id(p_srsv_rec, x_return_status);
808 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
809 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
810 -- need to exit
811 l_return_status := x_return_status;
812 RAISE G_EXCEPTION_HALT_VALIDATION;
813 ELSE
814 -- there was an error
815 l_return_status := x_return_status;
816 END IF;
817 END IF;
818
819 RETURN(l_return_status);
820 EXCEPTION
821 WHEN G_EXCEPTION_HALT_VALIDATION THEN
822 -- exit with return status
823 NULL;
824 RETURN (l_return_status);
825
826 WHEN OTHERS THEN
827 -- store SQL error message on message stack for caller
828 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
829 p_msg_name => G_OKL_UNEXPECTED_ERROR,
830 p_token1 => G_OKL_SQLCODE_TOKEN,
831 p_token1_value => SQLCODE,
832 p_token2 => G_OKL_SQLERRM_TOKEN,
833 p_token2_value => SQLERRM);
834 -- notify caller of an UNEXPECTED error
835 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
836
837 RETURN(l_return_status);
838 END Validate_Attributes;
839 -- END change : mvasudev
840
841 ---------------------------------------------------------------------------
842 -- PROCEDURE Validate_Record
843 ---------------------------------------------------------------------------
844 ---------------------------------------------
845 -- Validate_Record for:OKL_SIF_RET_STRMS_V --
846 ---------------------------------------------
847 FUNCTION Validate_Record (
848 p_srsv_rec IN srsv_rec_type
849 ) RETURN VARCHAR2 IS
850 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
851 BEGIN
852 RETURN (l_return_status);
853 END Validate_Record;
854
855 ---------------------------------------------------------------------------
856 -- PROCEDURE Migrate
857 ---------------------------------------------------------------------------
858 PROCEDURE migrate (
859 p_from IN srsv_rec_type,
860 -- START change : mvasudev, 09/05/2001
861 -- Changing OUT Parameter to IN OUT
862 -- p_to OUT NOCOPY srs_rec_type
863 p_to IN OUT NOCOPY srs_rec_type
864 -- END change : mvasudev
865 ) IS
866 BEGIN
867 p_to.id := p_from.id;
868 p_to.stream_type_name := p_from.stream_type_name;
869 p_to.index_number := p_from.index_number;
870 p_to.activity_type := p_from.activity_type;
871 p_to.sequence_number := p_from.sequence_number;
872 p_to.stream_interface_attribute01 := p_from.stream_interface_attribute01;
873 p_to.stream_interface_attribute02 := p_from.stream_interface_attribute02;
874 p_to.stream_interface_attribute03 := p_from.stream_interface_attribute03;
875 p_to.stream_interface_attribute04 := p_from.stream_interface_attribute04;
876 p_to.stream_interface_attribute05 := p_from.stream_interface_attribute05;
877 p_to.stream_interface_attribute06 := p_from.stream_interface_attribute06;
878 p_to.stream_interface_attribute07 := p_from.stream_interface_attribute07;
879 p_to.stream_interface_attribute08 := p_from.stream_interface_attribute08;
880 p_to.stream_interface_attribute09 := p_from.stream_interface_attribute09;
881 p_to.stream_interface_attribute10 := p_from.stream_interface_attribute10;
882 p_to.stream_interface_attribute11 := p_from.stream_interface_attribute11;
883 p_to.stream_interface_attribute12 := p_from.stream_interface_attribute12;
884 p_to.stream_interface_attribute13 := p_from.stream_interface_attribute13;
885 p_to.stream_interface_attribute14 := p_from.stream_interface_attribute14;
886 p_to.stream_interface_attribute15 := p_from.stream_interface_attribute15;
887 p_to.sre_date := p_from.sre_date;
888 p_to.amount := p_from.amount;
889 p_to.sir_id := p_from.sir_id;
890 p_to.object_version_number := p_from.object_version_number;
891 p_to.created_by := p_from.created_by;
892 p_to.last_updated_by := p_from.last_updated_by;
893 p_to.creation_date := p_from.creation_date;
894 p_to.last_update_date := p_from.last_update_date;
895 p_to.last_update_login := p_from.last_update_login;
896 END migrate;
897 PROCEDURE migrate (
898 p_from IN srs_rec_type,
899 -- START change : mvasudev, 09/05/2001
900 -- Changing OUT Parameter to IN OUT
901 p_to IN OUT NOCOPY srsv_rec_type
902 -- END change : mvasudev
903 ) IS
904 BEGIN
905 p_to.id := p_from.id;
906 p_to.stream_type_name := p_from.stream_type_name;
907 p_to.index_number := p_from.index_number;
908 p_to.activity_type := p_from.activity_type;
909 p_to.sequence_number := p_from.sequence_number;
910 p_to.stream_interface_attribute01 := p_from.stream_interface_attribute01;
911 p_to.stream_interface_attribute02 := p_from.stream_interface_attribute02;
912 p_to.stream_interface_attribute03 := p_from.stream_interface_attribute03;
913 p_to.stream_interface_attribute04 := p_from.stream_interface_attribute04;
914 p_to.stream_interface_attribute05 := p_from.stream_interface_attribute05;
915 p_to.stream_interface_attribute06 := p_from.stream_interface_attribute06;
916 p_to.stream_interface_attribute07 := p_from.stream_interface_attribute07;
917 p_to.stream_interface_attribute08 := p_from.stream_interface_attribute08;
918 p_to.stream_interface_attribute09 := p_from.stream_interface_attribute09;
919 p_to.stream_interface_attribute10 := p_from.stream_interface_attribute10;
920 p_to.stream_interface_attribute11 := p_from.stream_interface_attribute11;
921 p_to.stream_interface_attribute12 := p_from.stream_interface_attribute12;
922 p_to.stream_interface_attribute13 := p_from.stream_interface_attribute13;
923 p_to.stream_interface_attribute14 := p_from.stream_interface_attribute14;
924 p_to.stream_interface_attribute15 := p_from.stream_interface_attribute15;
925 p_to.sre_date := p_from.sre_date;
926 p_to.amount := p_from.amount;
927 p_to.sir_id := p_from.sir_id;
928 p_to.object_version_number := p_from.object_version_number;
929 p_to.created_by := p_from.created_by;
930 p_to.last_updated_by := p_from.last_updated_by;
931 p_to.creation_date := p_from.creation_date;
932 p_to.last_update_date := p_from.last_update_date;
933 p_to.last_update_login := p_from.last_update_login;
934 END migrate;
935
936 ---------------------------------------------------------------------------
937 -- PROCEDURE validate_row
938 ---------------------------------------------------------------------------
939 ------------------------------------------
940 -- validate_row for:OKL_SIF_RET_STRMS_V --
941 ------------------------------------------
942 PROCEDURE validate_row(
943 p_api_version IN NUMBER,
944 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
945 x_return_status OUT NOCOPY VARCHAR2,
946 x_msg_count OUT NOCOPY NUMBER,
947 x_msg_data OUT NOCOPY VARCHAR2,
948 p_srsv_rec IN srsv_rec_type) IS
949
950 l_api_version CONSTANT NUMBER := 1;
951 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
952 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
953 l_srsv_rec srsv_rec_type := p_srsv_rec;
954 l_srs_rec srs_rec_type;
955 BEGIN
956 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
957 G_PKG_NAME,
958 p_init_msg_list,
959 l_api_version,
960 p_api_version,
961 '_PVT',
962 x_return_status);
963 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
964 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
965 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
966 RAISE OKC_API.G_EXCEPTION_ERROR;
967 END IF;
968 --- Validate all non-missing attributes (Item Level Validation)
969 l_return_status := Validate_Attributes(l_srsv_rec);
970 --- If any errors happen abort API
971 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
972 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
973 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
974 RAISE OKC_API.G_EXCEPTION_ERROR;
975 END IF;
976 l_return_status := Validate_Record(l_srsv_rec);
977 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
979 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
980 RAISE OKC_API.G_EXCEPTION_ERROR;
981 END IF;
982 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
983 EXCEPTION
984 WHEN OKC_API.G_EXCEPTION_ERROR THEN
985 x_return_status := OKC_API.HANDLE_EXCEPTIONS
986 (
987 l_api_name,
988 G_PKG_NAME,
989 'OKC_API.G_RET_STS_ERROR',
990 x_msg_count,
991 x_msg_data,
992 '_PVT'
993 );
994 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
995 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
996 (
997 l_api_name,
998 G_PKG_NAME,
999 'OKC_API.G_RET_STS_UNEXP_ERROR',
1000 x_msg_count,
1001 x_msg_data,
1002 '_PVT'
1003 );
1004 WHEN OTHERS THEN
1005 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1006 (
1007 l_api_name,
1008 G_PKG_NAME,
1009 'OTHERS',
1010 x_msg_count,
1011 x_msg_data,
1012 '_PVT'
1013 );
1014 END validate_row;
1015 ------------------------------------------
1016 -- PL/SQL TBL validate_row for:SRSV_TBL --
1017 ------------------------------------------
1018 PROCEDURE validate_row(
1019 p_api_version IN NUMBER,
1020 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1021 x_return_status OUT NOCOPY VARCHAR2,
1022 x_msg_count OUT NOCOPY NUMBER,
1023 x_msg_data OUT NOCOPY VARCHAR2,
1024 p_srsv_tbl IN srsv_tbl_type) IS
1025
1026 l_api_version CONSTANT NUMBER := 1;
1027 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1028 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1029 i NUMBER := 0;
1030 -- START change : mvasudev, 09/05/2001
1031 -- Adding OverAll Status Flag
1032 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1033 -- END change : mvasudev
1034 BEGIN
1035 OKC_API.init_msg_list(p_init_msg_list);
1036 -- Make sure PL/SQL table has records in it before passing
1037 IF (p_srsv_tbl.COUNT > 0) THEN
1038 i := p_srsv_tbl.FIRST;
1039 LOOP
1040 validate_row (
1041 p_api_version => p_api_version,
1042 p_init_msg_list => OKC_API.G_FALSE,
1043 x_return_status => x_return_status,
1044 x_msg_count => x_msg_count,
1045 x_msg_data => x_msg_data,
1046 p_srsv_rec => p_srsv_tbl(i));
1047 -- START change : mvasudev, 09/05/2001
1048 -- store the highest degree of error
1049 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1050 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1051 l_overall_status := x_return_status;
1052 END IF;
1053 END IF;
1054 -- END change : mvasudev
1055 EXIT WHEN (i = p_srsv_tbl.LAST);
1056 i := p_srsv_tbl.NEXT(i);
1057 END LOOP;
1058 -- START change : mvasudev, 09/05/2001
1059 -- return overall status
1060 x_return_status := l_overall_status;
1061 -- END change : mvasudev
1062 END IF;
1063 EXCEPTION
1064 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1065 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1066 (
1067 l_api_name,
1068 G_PKG_NAME,
1069 'OKC_API.G_RET_STS_ERROR',
1070 x_msg_count,
1071 x_msg_data,
1072 '_PVT'
1073 );
1074 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1075 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1076 (
1077 l_api_name,
1078 G_PKG_NAME,
1079 'OKC_API.G_RET_STS_UNEXP_ERROR',
1080 x_msg_count,
1081 x_msg_data,
1082 '_PVT'
1083 );
1084 WHEN OTHERS THEN
1085 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1086 (
1087 l_api_name,
1088 G_PKG_NAME,
1089 'OTHERS',
1090 x_msg_count,
1091 x_msg_data,
1092 '_PVT'
1093 );
1094 END validate_row;
1095
1096 ---------------------------------------------------------------------------
1097 -- PROCEDURE insert_row
1098 ---------------------------------------------------------------------------
1099 --------------------------------------
1100 -- insert_row for:OKL_SIF_RET_STRMS --
1101 --------------------------------------
1102 PROCEDURE insert_row(
1103 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1104 x_return_status OUT NOCOPY VARCHAR2,
1105 x_msg_count OUT NOCOPY NUMBER,
1106 x_msg_data OUT NOCOPY VARCHAR2,
1107 p_srs_rec IN srs_rec_type,
1108 x_srs_rec OUT NOCOPY srs_rec_type) IS
1109
1110 l_api_version CONSTANT NUMBER := 1;
1111 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_insert_row';
1112 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1113 l_srs_rec srs_rec_type := p_srs_rec;
1114 l_def_srs_rec srs_rec_type;
1115 ------------------------------------------
1116 -- Set_Attributes for:OKL_SIF_RET_STRMS --
1117 ------------------------------------------
1118 FUNCTION Set_Attributes (
1119 p_srs_rec IN srs_rec_type,
1120 x_srs_rec OUT NOCOPY srs_rec_type
1121 ) RETURN VARCHAR2 IS
1122 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1123 BEGIN
1124 x_srs_rec := p_srs_rec;
1125 RETURN(l_return_status);
1126 END Set_Attributes;
1127 BEGIN
1128 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1129 p_init_msg_list,
1130 '_PVT',
1131 x_return_status);
1132 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1133 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1134 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1135 RAISE OKC_API.G_EXCEPTION_ERROR;
1136 END IF;
1137 --- Setting item attributes
1138 l_return_status := Set_Attributes(
1139 p_srs_rec, -- IN
1140 l_srs_rec); -- OUT
1141 --- If any errors happen abort API
1142 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1143 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1144 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1145 RAISE OKC_API.G_EXCEPTION_ERROR;
1146 END IF;
1147
1148 INSERT INTO OKL_SIF_RET_STRMS(
1149 id,
1150 stream_type_name,
1151 index_number,
1152 activity_type,
1153 sequence_number,
1154 stream_interface_attribute01,
1155 stream_interface_attribute02,
1156 stream_interface_attribute03,
1157 stream_interface_attribute04,
1158 stream_interface_attribute05,
1159 stream_interface_attribute06,
1160 stream_interface_attribute07,
1161 stream_interface_attribute08,
1162 stream_interface_attribute09,
1163 stream_interface_attribute10,
1164 stream_interface_attribute11,
1165 stream_interface_attribute12,
1166 stream_interface_attribute13,
1167 stream_interface_attribute14,
1168 stream_interface_attribute15,
1169 sre_date,
1170 amount,
1171 sir_id,
1172 object_version_number,
1173 created_by,
1174 last_updated_by,
1175 creation_date,
1176 last_update_date,
1177 last_update_login)
1178 VALUES (
1179 l_srs_rec.id,
1180 l_srs_rec.stream_type_name,
1181 l_srs_rec.index_number,
1182 l_srs_rec.activity_type,
1183 l_srs_rec.sequence_number,
1184 l_srs_rec.stream_interface_attribute01,
1185 l_srs_rec.stream_interface_attribute02,
1186 l_srs_rec.stream_interface_attribute03,
1187 l_srs_rec.stream_interface_attribute04,
1188 l_srs_rec.stream_interface_attribute05,
1189 l_srs_rec.stream_interface_attribute06,
1190 l_srs_rec.stream_interface_attribute07,
1191 l_srs_rec.stream_interface_attribute08,
1192 l_srs_rec.stream_interface_attribute09,
1193 l_srs_rec.stream_interface_attribute10,
1194 l_srs_rec.stream_interface_attribute11,
1195 l_srs_rec.stream_interface_attribute12,
1196 l_srs_rec.stream_interface_attribute13,
1197 l_srs_rec.stream_interface_attribute14,
1198 l_srs_rec.stream_interface_attribute15,
1199 l_srs_rec.sre_date,
1200 l_srs_rec.amount,
1201 l_srs_rec.sir_id,
1202 l_srs_rec.object_version_number,
1203 l_srs_rec.created_by,
1204 l_srs_rec.last_updated_by,
1205 l_srs_rec.creation_date,
1206 l_srs_rec.last_update_date,
1207 l_srs_rec.last_update_login);
1208 -- Set OUT values
1209 x_srs_rec := l_srs_rec;
1210 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1211 EXCEPTION
1212 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1213 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1214 (
1215 l_api_name,
1216 G_PKG_NAME,
1217 'OKC_API.G_RET_STS_ERROR',
1218 x_msg_count,
1219 x_msg_data,
1220 '_PVT'
1221 );
1222 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1223 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1224 (
1225 l_api_name,
1226 G_PKG_NAME,
1227 'OKC_API.G_RET_STS_UNEXP_ERROR',
1228 x_msg_count,
1229 x_msg_data,
1230 '_PVT'
1231 );
1232 WHEN OTHERS THEN
1233 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1234 (
1235 l_api_name,
1236 G_PKG_NAME,
1237 'OTHERS',
1238 x_msg_count,
1239 x_msg_data,
1240 '_PVT'
1241 );
1242 END insert_row;
1243
1244
1245
1246 ----------------------------------------
1247 -- insert_row for:OKL_SIF_RET_STRMS_V --
1248 ----------------------------------------
1249
1250 PROCEDURE insert_row(
1251 p_api_version IN NUMBER,
1252 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2,
1256 p_srsv_rec IN srsv_rec_type,
1257 x_srsv_rec OUT NOCOPY srsv_rec_type) IS
1258
1259 l_api_version CONSTANT NUMBER := 1;
1260 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1261 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1262 l_srsv_rec srsv_rec_type;
1263 l_def_srsv_rec srsv_rec_type;
1264 l_srs_rec srs_rec_type;
1265 lx_srs_rec srs_rec_type;
1266
1267 -------------------------------
1268 -- FUNCTION fill_who_columns --
1269 -------------------------------
1270 FUNCTION fill_who_columns (
1271 p_srsv_rec IN srsv_rec_type
1272 ) RETURN srsv_rec_type IS
1273 l_srsv_rec srsv_rec_type := p_srsv_rec;
1274 BEGIN
1275 l_srsv_rec.CREATION_DATE := SYSDATE;
1276 l_srsv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1277 l_srsv_rec.LAST_UPDATE_DATE := SYSDATE;
1278 l_srsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1279 l_srsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1280 RETURN(l_srsv_rec);
1281 END fill_who_columns;
1282 --------------------------------------------
1283 -- Set_Attributes for:OKL_SIF_RET_STRMS_V --
1284 --------------------------------------------
1285 FUNCTION Set_Attributes (
1286 p_srsv_rec IN srsv_rec_type,
1287 x_srsv_rec OUT NOCOPY srsv_rec_type
1288 ) RETURN VARCHAR2 IS
1289 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1290 BEGIN
1291 x_srsv_rec := p_srsv_rec;
1292 x_srsv_rec.OBJECT_VERSION_NUMBER := 1;
1293 RETURN(l_return_status);
1294 END Set_Attributes;
1295 BEGIN
1296 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1297 G_PKG_NAME,
1298 p_init_msg_list,
1299 l_api_version,
1300 p_api_version,
1301 '_PVT',
1302 x_return_status);
1303 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1304 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1305 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1306 RAISE OKC_API.G_EXCEPTION_ERROR;
1307 END IF;
1308 l_srsv_rec := null_out_defaults(p_srsv_rec);
1309 -- Set primary key value
1310 l_srsv_rec.ID := get_seq_id;
1311 --- Setting item attributes
1312 l_return_status := Set_Attributes(
1313 l_srsv_rec, -- IN
1314 l_def_srsv_rec); -- OUT
1315 --- If any errors happen abort API
1316 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1317 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1318 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1319 RAISE OKC_API.G_EXCEPTION_ERROR;
1320 END IF;
1321 l_def_srsv_rec := fill_who_columns(l_def_srsv_rec);
1322 --- Validate all non-missing attributes (Item Level Validation)
1323 l_return_status := Validate_Attributes(l_def_srsv_rec);
1324 --- If any errors happen abort API
1325 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1327 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1328 RAISE OKC_API.G_EXCEPTION_ERROR;
1329 END IF;
1330 l_return_status := Validate_Record(l_def_srsv_rec);
1331 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1332 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1333 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1334 RAISE OKC_API.G_EXCEPTION_ERROR;
1335 END IF;
1336 --------------------------------------
1337 -- Move VIEW record to "Child" records
1338 --------------------------------------
1339 migrate(l_def_srsv_rec, l_srs_rec);
1340 --------------------------------------------
1341 -- Call the INSERT_ROW for each child record
1342 --------------------------------------------
1343 insert_row(
1344 p_init_msg_list,
1345 x_return_status,
1346 x_msg_count,
1347 x_msg_data,
1348 l_srs_rec,
1349 lx_srs_rec
1350 );
1351 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1352 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1353 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1354 RAISE OKC_API.G_EXCEPTION_ERROR;
1355 END IF;
1356 migrate(lx_srs_rec, l_def_srsv_rec);
1357 -- Set OUT values
1358 x_srsv_rec := l_def_srsv_rec;
1359 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1360
1361 EXCEPTION
1362 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1363 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1364 (
1365 l_api_name,
1366 G_PKG_NAME,
1367 'OKC_API.G_RET_STS_ERROR',
1368 x_msg_count,
1369 x_msg_data,
1370 '_PVT'
1371 );
1372 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1373 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1374 (
1375 l_api_name,
1376 G_PKG_NAME,
1377 'OKC_API.G_RET_STS_UNEXP_ERROR',
1378 x_msg_count,
1379 x_msg_data,
1380 '_PVT'
1381 );
1382 WHEN OTHERS THEN
1383 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1384 (
1385 l_api_name,
1386 G_PKG_NAME,
1387 'OTHERS',
1388 x_msg_count,
1389 x_msg_data,
1390 '_PVT'
1391 );
1392
1393 END insert_row;
1394
1395
1396 ----------------------------------------
1397 -- PL/SQL TBL insert_row for:SRSV_TBL --
1398 ----------------------------------------
1399 PROCEDURE insert_row(
1400 p_api_version IN NUMBER,
1401 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1402 x_return_status OUT NOCOPY VARCHAR2,
1403 x_msg_count OUT NOCOPY NUMBER,
1404 x_msg_data OUT NOCOPY VARCHAR2,
1405 p_srsv_tbl IN srsv_tbl_type,
1406 x_srsv_tbl OUT NOCOPY srsv_tbl_type) IS
1407
1408 l_api_version CONSTANT NUMBER := 1;
1409 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1410 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1411 i NUMBER := 0;
1412 -- START change : mvasudev, 09/05/2001
1413 -- Adding OverAll Status Flag
1414 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1415 -- END change : mvasudev
1416 BEGIN
1417 OKC_API.init_msg_list(p_init_msg_list);
1418 -- Make sure PL/SQL table has records in it before passing
1419 IF (p_srsv_tbl.COUNT > 0) THEN
1420 i := p_srsv_tbl.FIRST;
1421 LOOP
1422 insert_row (
1423 p_api_version => p_api_version,
1424 p_init_msg_list => OKC_API.G_FALSE,
1425 x_return_status => x_return_status,
1426 x_msg_count => x_msg_count,
1427 x_msg_data => x_msg_data,
1428 p_srsv_rec => p_srsv_tbl(i),
1429 x_srsv_rec => x_srsv_tbl(i));
1430 -- START change : mvasudev, 09/05/2001
1431 -- store the highest degree of error
1432 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1433 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1434 l_overall_status := x_return_status;
1435 END IF;
1436 END IF;
1437 -- END change : mvasudev
1438 EXIT WHEN (i = p_srsv_tbl.LAST);
1439 i := p_srsv_tbl.NEXT(i);
1440 END LOOP;
1441 -- START change : mvasudev, 09/05/2001
1442 -- return overall status
1443 x_return_status := l_overall_status;
1444 -- END change : mvasudev
1445 END IF;
1446 EXCEPTION
1447 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1448 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1449 (
1450 l_api_name,
1451 G_PKG_NAME,
1452 'OKC_API.G_RET_STS_ERROR',
1453 x_msg_count,
1454 x_msg_data,
1455 '_PVT'
1456 );
1457 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1458 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1459 (
1460 l_api_name,
1461 G_PKG_NAME,
1462 'OKC_API.G_RET_STS_UNEXP_ERROR',
1463 x_msg_count,
1464 x_msg_data,
1465 '_PVT'
1466 );
1467 WHEN OTHERS THEN
1468 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1469 (
1470 l_api_name,
1471 G_PKG_NAME,
1472 'OTHERS',
1473 x_msg_count,
1474 x_msg_data,
1475 '_PVT'
1476 );
1477 END insert_row;
1478
1479 ---------------------------------------------------------------------------
1480 -- PROCEDURE lock_row
1481 ---------------------------------------------------------------------------
1482 ------------------------------------
1483 -- lock_row for:OKL_SIF_RET_STRMS --
1484 ------------------------------------
1485 PROCEDURE lock_row(
1486 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1487 x_return_status OUT NOCOPY VARCHAR2,
1488 x_msg_count OUT NOCOPY NUMBER,
1489 x_msg_data OUT NOCOPY VARCHAR2,
1490 p_srs_rec IN srs_rec_type) IS
1491
1492 E_Resource_Busy EXCEPTION;
1493 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1494 CURSOR lock_csr (p_srs_rec IN srs_rec_type) IS
1495 SELECT OBJECT_VERSION_NUMBER
1496 FROM OKL_SIF_RET_STRMS
1497 WHERE ID = p_srs_rec.id
1498 AND OBJECT_VERSION_NUMBER = p_srs_rec.object_version_number
1499 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1500
1501 CURSOR lchk_csr (p_srs_rec IN srs_rec_type) IS
1502 SELECT OBJECT_VERSION_NUMBER
1503 FROM OKL_SIF_RET_STRMS
1504 WHERE ID = p_srs_rec.id;
1505 l_api_version CONSTANT NUMBER := 1;
1506 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_lock_row';
1507 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1508 l_object_version_number OKL_SIF_RET_STRMS.OBJECT_VERSION_NUMBER%TYPE;
1509 lc_object_version_number OKL_SIF_RET_STRMS.OBJECT_VERSION_NUMBER%TYPE;
1510 l_row_notfound BOOLEAN := FALSE;
1511 lc_row_notfound BOOLEAN := FALSE;
1512 BEGIN
1513 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1514 p_init_msg_list,
1515 '_PVT',
1516 x_return_status);
1517 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1518 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1519 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1520 RAISE OKC_API.G_EXCEPTION_ERROR;
1521 END IF;
1522 BEGIN
1523 OPEN lock_csr(p_srs_rec);
1524 FETCH lock_csr INTO l_object_version_number;
1525 l_row_notfound := lock_csr%NOTFOUND;
1526 CLOSE lock_csr;
1527 EXCEPTION
1528 WHEN E_Resource_Busy THEN
1529 IF (lock_csr%ISOPEN) THEN
1530 CLOSE lock_csr;
1531 END IF;
1532 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1533 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1534 END;
1535
1536 IF ( l_row_notfound ) THEN
1537 OPEN lchk_csr(p_srs_rec);
1538 FETCH lchk_csr INTO lc_object_version_number;
1539 lc_row_notfound := lchk_csr%NOTFOUND;
1540 CLOSE lchk_csr;
1541 END IF;
1542 IF (lc_row_notfound) THEN
1543 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1544 RAISE OKC_API.G_EXCEPTION_ERROR;
1545 ELSIF lc_object_version_number > p_srs_rec.object_version_number THEN
1546 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1547 RAISE OKC_API.G_EXCEPTION_ERROR;
1548 ELSIF lc_object_version_number <> p_srs_rec.object_version_number THEN
1549 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1550 RAISE OKC_API.G_EXCEPTION_ERROR;
1551 ELSIF lc_object_version_number = -1 THEN
1552 OKC_API.set_message(G_OKC_APP,G_RECORD_LOGICALLY_DELETED);
1553 RAISE OKC_API.G_EXCEPTION_ERROR;
1554 END IF;
1555 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1556 EXCEPTION
1557 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1558 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1559 (
1560 l_api_name,
1561 G_PKG_NAME,
1562 'OKC_API.G_RET_STS_ERROR',
1563 x_msg_count,
1564 x_msg_data,
1565 '_PVT'
1566 );
1567 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1568 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1569 (
1570 l_api_name,
1571 G_PKG_NAME,
1572 'OKC_API.G_RET_STS_UNEXP_ERROR',
1573 x_msg_count,
1574 x_msg_data,
1575 '_PVT'
1576 );
1577 WHEN OTHERS THEN
1578 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1579 (
1580 l_api_name,
1581 G_PKG_NAME,
1582 'OTHERS',
1583 x_msg_count,
1584 x_msg_data,
1585 '_PVT'
1586 );
1587 END lock_row;
1588 --------------------------------------
1589 -- lock_row for:OKL_SIF_RET_STRMS_V --
1590 --------------------------------------
1591 PROCEDURE lock_row(
1592 p_api_version IN NUMBER,
1593 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1594 x_return_status OUT NOCOPY VARCHAR2,
1595 x_msg_count OUT NOCOPY NUMBER,
1596 x_msg_data OUT NOCOPY VARCHAR2,
1597 p_srsv_rec IN srsv_rec_type) IS
1598
1599 l_api_version CONSTANT NUMBER := 1;
1600 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1601 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1602 l_srs_rec srs_rec_type;
1603 BEGIN
1604 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1605 G_PKG_NAME,
1606 p_init_msg_list,
1607 l_api_version,
1608 p_api_version,
1609 '_PVT',
1610 x_return_status);
1611 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1614 RAISE OKC_API.G_EXCEPTION_ERROR;
1615 END IF;
1616 --------------------------------------
1617 -- Move VIEW record to "Child" records
1618 --------------------------------------
1619 migrate(p_srsv_rec, l_srs_rec);
1620 --------------------------------------------
1621 -- Call the LOCK_ROW for each child record
1622 --------------------------------------------
1623 lock_row(
1624 p_init_msg_list,
1625 x_return_status,
1626 x_msg_count,
1627 x_msg_data,
1628 l_srs_rec
1629 );
1630 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1631 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1632 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1633 RAISE OKC_API.G_EXCEPTION_ERROR;
1634 END IF;
1635 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1636 EXCEPTION
1637 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1638 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1639 (
1640 l_api_name,
1641 G_PKG_NAME,
1642 'OKC_API.G_RET_STS_ERROR',
1643 x_msg_count,
1644 x_msg_data,
1645 '_PVT'
1646 );
1647 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1648 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1649 (
1650 l_api_name,
1651 G_PKG_NAME,
1652 'OKC_API.G_RET_STS_UNEXP_ERROR',
1653 x_msg_count,
1654 x_msg_data,
1655 '_PVT'
1656 );
1657 WHEN OTHERS THEN
1658 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1659 (
1660 l_api_name,
1661 G_PKG_NAME,
1662 'OTHERS',
1663 x_msg_count,
1664 x_msg_data,
1665 '_PVT'
1666 );
1667 END lock_row;
1668 --------------------------------------
1669 -- PL/SQL TBL lock_row for:SRSV_TBL --
1670 --------------------------------------
1671 PROCEDURE lock_row(
1672 p_api_version IN NUMBER,
1673 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1674 x_return_status OUT NOCOPY VARCHAR2,
1675 x_msg_count OUT NOCOPY NUMBER,
1676 x_msg_data OUT NOCOPY VARCHAR2,
1677 p_srsv_tbl IN srsv_tbl_type) IS
1678
1679 l_api_version CONSTANT NUMBER := 1;
1680 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1681 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1682 i NUMBER := 0;
1683 -- START change : mvasudev, 09/05/2001
1684 -- Adding OverAll Status Flag
1685 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1686 -- END change : mvasudev
1687 BEGIN
1688 OKC_API.init_msg_list(p_init_msg_list);
1689 -- Make sure PL/SQL table has records in it before passing
1690 IF (p_srsv_tbl.COUNT > 0) THEN
1691 i := p_srsv_tbl.FIRST;
1692 LOOP
1693 lock_row (
1694 p_api_version => p_api_version,
1695 p_init_msg_list => OKC_API.G_FALSE,
1696 x_return_status => x_return_status,
1697 x_msg_count => x_msg_count,
1698 x_msg_data => x_msg_data,
1699 p_srsv_rec => p_srsv_tbl(i));
1700 -- START change : mvasudev, 09/05/2001
1701 -- store the highest degree of error
1702 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1703 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1704 l_overall_status := x_return_status;
1705 END IF;
1706 END IF;
1707 -- END change : mvasudev
1708 EXIT WHEN (i = p_srsv_tbl.LAST);
1709 i := p_srsv_tbl.NEXT(i);
1710 END LOOP;
1711 -- START change : mvasudev, 09/05/2001
1712 -- return overall status
1713 x_return_status := l_overall_status;
1714 -- END change : mvasudev
1715 END IF;
1716 EXCEPTION
1717 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1718 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1719 (
1720 l_api_name,
1721 G_PKG_NAME,
1722 'OKC_API.G_RET_STS_ERROR',
1723 x_msg_count,
1724 x_msg_data,
1725 '_PVT'
1726 );
1727 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1728 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1729 (
1730 l_api_name,
1731 G_PKG_NAME,
1732 'OKC_API.G_RET_STS_UNEXP_ERROR',
1733 x_msg_count,
1734 x_msg_data,
1735 '_PVT'
1736 );
1737 WHEN OTHERS THEN
1738 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1739 (
1740 l_api_name,
1741 G_PKG_NAME,
1742 'OTHERS',
1743 x_msg_count,
1744 x_msg_data,
1745 '_PVT'
1746 );
1747 END lock_row;
1748
1749 ---------------------------------------------------------------------------
1750 -- PROCEDURE update_row
1751 ---------------------------------------------------------------------------
1752 --------------------------------------
1753 -- update_row for:OKL_SIF_RET_STRMS --
1754 --------------------------------------
1755 PROCEDURE update_row(
1756 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1757 x_return_status OUT NOCOPY VARCHAR2,
1758 x_msg_count OUT NOCOPY NUMBER,
1759 x_msg_data OUT NOCOPY VARCHAR2,
1760 p_srs_rec IN srs_rec_type,
1761 x_srs_rec OUT NOCOPY srs_rec_type) IS
1762
1763 l_api_version CONSTANT NUMBER := 1;
1764 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_update_row';
1765 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1766 l_srs_rec srs_rec_type := p_srs_rec;
1767 l_def_srs_rec srs_rec_type;
1768 l_row_notfound BOOLEAN := TRUE;
1769 ----------------------------------
1770 -- FUNCTION populate_new_record --
1771 ----------------------------------
1772 FUNCTION populate_new_record (
1773 p_srs_rec IN srs_rec_type,
1774 x_srs_rec OUT NOCOPY srs_rec_type
1775 ) RETURN VARCHAR2 IS
1776 l_srs_rec srs_rec_type;
1777 l_row_notfound BOOLEAN := TRUE;
1778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1779 BEGIN
1780 x_srs_rec := p_srs_rec;
1781 -- Get current database values
1782 l_srs_rec := get_rec(p_srs_rec, l_row_notfound);
1783 IF (l_row_notfound) THEN
1784 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1785 END IF;
1786 IF (x_srs_rec.id IS NULL)
1787 THEN
1788 x_srs_rec.id := l_srs_rec.id;
1789 END IF;
1790 IF (x_srs_rec.stream_type_name IS NULL)
1791 THEN
1792 x_srs_rec.stream_type_name := l_srs_rec.stream_type_name;
1793 END IF;
1794 IF (x_srs_rec.index_number IS NULL)
1795 THEN
1796 x_srs_rec.index_number := l_srs_rec.index_number;
1797 END IF;
1798 IF (x_srs_rec.activity_type IS NULL)
1799 THEN
1800 x_srs_rec.activity_type := l_srs_rec.activity_type;
1801 END IF;
1802 IF (x_srs_rec.sequence_number IS NULL)
1803 THEN
1804 x_srs_rec.sequence_number := l_srs_rec.sequence_number;
1805 END IF;
1806 IF (x_srs_rec.stream_interface_attribute01 IS NULL)
1807 THEN
1808 x_srs_rec.stream_interface_attribute01 := l_srs_rec.stream_interface_attribute01;
1809 END IF;
1810 IF (x_srs_rec.stream_interface_attribute02 IS NULL)
1811 THEN
1812 x_srs_rec.stream_interface_attribute02 := l_srs_rec.stream_interface_attribute02;
1813 END IF;
1814 IF (x_srs_rec.stream_interface_attribute03 IS NULL)
1815 THEN
1816 x_srs_rec.stream_interface_attribute03 := l_srs_rec.stream_interface_attribute03;
1817 END IF;
1818 IF (x_srs_rec.stream_interface_attribute04 IS NULL)
1819 THEN
1820 x_srs_rec.stream_interface_attribute04 := l_srs_rec.stream_interface_attribute04;
1821 END IF;
1822 IF (x_srs_rec.stream_interface_attribute05 IS NULL)
1823 THEN
1824 x_srs_rec.stream_interface_attribute05 := l_srs_rec.stream_interface_attribute05;
1825 END IF;
1826 IF (x_srs_rec.stream_interface_attribute06 IS NULL)
1827 THEN
1828 x_srs_rec.stream_interface_attribute06 := l_srs_rec.stream_interface_attribute06;
1829 END IF;
1830 IF (x_srs_rec.stream_interface_attribute07 IS NULL)
1831 THEN
1832 x_srs_rec.stream_interface_attribute07 := l_srs_rec.stream_interface_attribute07;
1833 END IF;
1834 IF (x_srs_rec.stream_interface_attribute08 IS NULL)
1835 THEN
1836 x_srs_rec.stream_interface_attribute08 := l_srs_rec.stream_interface_attribute08;
1837 END IF;
1838 IF (x_srs_rec.stream_interface_attribute09 IS NULL)
1839 THEN
1840 x_srs_rec.stream_interface_attribute09 := l_srs_rec.stream_interface_attribute09;
1841 END IF;
1842 IF (x_srs_rec.stream_interface_attribute10 IS NULL)
1843 THEN
1844 x_srs_rec.stream_interface_attribute10 := l_srs_rec.stream_interface_attribute10;
1845 END IF;
1846 IF (x_srs_rec.stream_interface_attribute11 IS NULL)
1847 THEN
1848 x_srs_rec.stream_interface_attribute11 := l_srs_rec.stream_interface_attribute11;
1849 END IF;
1850 IF (x_srs_rec.stream_interface_attribute12 IS NULL)
1851 THEN
1852 x_srs_rec.stream_interface_attribute12 := l_srs_rec.stream_interface_attribute12;
1853 END IF;
1854 IF (x_srs_rec.stream_interface_attribute13 IS NULL)
1855 THEN
1856 x_srs_rec.stream_interface_attribute13 := l_srs_rec.stream_interface_attribute13;
1857 END IF;
1858 IF (x_srs_rec.stream_interface_attribute14 IS NULL)
1859 THEN
1860 x_srs_rec.stream_interface_attribute14 := l_srs_rec.stream_interface_attribute14;
1861 END IF;
1862 IF (x_srs_rec.stream_interface_attribute15 IS NULL)
1863 THEN
1864 x_srs_rec.stream_interface_attribute15 := l_srs_rec.stream_interface_attribute15;
1865 END IF;
1866 IF (x_srs_rec.sre_date IS NULL)
1867 THEN
1868 x_srs_rec.sre_date := l_srs_rec.sre_date;
1869 END IF;
1870 IF (x_srs_rec.amount IS NULL)
1871 THEN
1872 x_srs_rec.amount := l_srs_rec.amount;
1873 END IF;
1874 IF (x_srs_rec.sir_id IS NULL)
1875 THEN
1876 x_srs_rec.sir_id := l_srs_rec.sir_id;
1877 END IF;
1878 IF (x_srs_rec.object_version_number IS NULL)
1879 THEN
1880 x_srs_rec.object_version_number := l_srs_rec.object_version_number;
1881 END IF;
1882 IF (x_srs_rec.created_by IS NULL)
1883 THEN
1884 x_srs_rec.created_by := l_srs_rec.created_by;
1885 END IF;
1886 IF (x_srs_rec.last_updated_by IS NULL)
1887 THEN
1888 x_srs_rec.last_updated_by := l_srs_rec.last_updated_by;
1889 END IF;
1890 IF (x_srs_rec.creation_date IS NULL)
1891 THEN
1892 x_srs_rec.creation_date := l_srs_rec.creation_date;
1893 END IF;
1894 IF (x_srs_rec.last_update_date IS NULL)
1895 THEN
1896 x_srs_rec.last_update_date := l_srs_rec.last_update_date;
1897 END IF;
1898 IF (x_srs_rec.last_update_login IS NULL)
1899 THEN
1900 x_srs_rec.last_update_login := l_srs_rec.last_update_login;
1901 END IF;
1902 RETURN(l_return_status);
1903 END populate_new_record;
1904 ------------------------------------------
1905 -- Set_Attributes for:OKL_SIF_RET_STRMS --
1906 ------------------------------------------
1907 FUNCTION Set_Attributes (
1908 p_srs_rec IN srs_rec_type,
1909 x_srs_rec OUT NOCOPY srs_rec_type
1910 ) RETURN VARCHAR2 IS
1911 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1912 BEGIN
1913 x_srs_rec := p_srs_rec;
1914 RETURN(l_return_status);
1915 END Set_Attributes;
1916 BEGIN
1917 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1918 p_init_msg_list,
1919 '_PVT',
1920 x_return_status);
1921 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1922 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1923 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1924 RAISE OKC_API.G_EXCEPTION_ERROR;
1925 END IF;
1926 --- Setting item attributes
1927 l_return_status := Set_Attributes(
1928 p_srs_rec, -- IN
1929 l_srs_rec); -- OUT
1930 --- If any errors happen abort API
1931 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1932 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1933 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1934 RAISE OKC_API.G_EXCEPTION_ERROR;
1935 END IF;
1936 l_return_status := populate_new_record(l_srs_rec, l_def_srs_rec);
1937 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1938 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1939 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1940 RAISE OKC_API.G_EXCEPTION_ERROR;
1941 END IF;
1942 UPDATE OKL_SIF_RET_STRMS
1943 SET STREAM_TYPE_NAME = l_def_srs_rec.stream_type_name,
1944 INDEX_NUMBER = l_def_srs_rec.index_number,
1945 ACTIVITY_TYPE = l_def_srs_rec.activity_type,
1946 SEQUENCE_NUMBER = l_def_srs_rec.sequence_number,
1947 STREAM_INTERFACE_ATTRIBUTE01 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE01,
1948 STREAM_INTERFACE_ATTRIBUTE02 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE02,
1949 STREAM_INTERFACE_ATTRIBUTE03 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE03,
1950 STREAM_INTERFACE_ATTRIBUTE04 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE04,
1951 STREAM_INTERFACE_ATTRIBUTE05 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE05,
1952 STREAM_INTERFACE_ATTRIBUTE06 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE06,
1953 STREAM_INTERFACE_ATTRIBUTE07 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE07,
1954 STREAM_INTERFACE_ATTRIBUTE08 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE08,
1955 STREAM_INTERFACE_ATTRIBUTE09 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE09,
1956 STREAM_INTERFACE_ATTRIBUTE10 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE10,
1957 STREAM_INTERFACE_ATTRIBUTE11 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE11,
1958 STREAM_INTERFACE_ATTRIBUTE12 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE12,
1959 STREAM_INTERFACE_ATTRIBUTE13 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE13,
1960 STREAM_INTERFACE_ATTRIBUTE14 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE14,
1961 STREAM_INTERFACE_ATTRIBUTE15 = l_def_srs_rec.STREAM_INTERFACE_ATTRIBUTE15,
1962 SRE_DATE = l_def_srs_rec.sre_date,
1963 AMOUNT = l_def_srs_rec.amount,
1964 SIR_ID = l_def_srs_rec.sir_id,
1965 OBJECT_VERSION_NUMBER = l_def_srs_rec.object_version_number,
1966 CREATED_BY = l_def_srs_rec.created_by,
1967 LAST_UPDATED_BY = l_def_srs_rec.last_updated_by,
1968 CREATION_DATE = l_def_srs_rec.creation_date,
1969 LAST_UPDATE_DATE = l_def_srs_rec.last_update_date,
1970 LAST_UPDATE_LOGIN = l_def_srs_rec.last_update_login
1971 WHERE ID = l_def_srs_rec.id;
1972
1973 x_srs_rec := l_def_srs_rec;
1974 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1975 EXCEPTION
1976 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1977 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1978 (
1979 l_api_name,
1980 G_PKG_NAME,
1981 'OKC_API.G_RET_STS_ERROR',
1982 x_msg_count,
1983 x_msg_data,
1984 '_PVT'
1985 );
1986 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1987 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1988 (
1989 l_api_name,
1990 G_PKG_NAME,
1991 'OKC_API.G_RET_STS_UNEXP_ERROR',
1992 x_msg_count,
1993 x_msg_data,
1994 '_PVT'
1995 );
1996 WHEN OTHERS THEN
1997 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1998 (
1999 l_api_name,
2000 G_PKG_NAME,
2001 'OTHERS',
2002 x_msg_count,
2003 x_msg_data,
2004 '_PVT'
2005 );
2006 END update_row;
2007 ----------------------------------------
2008 -- update_row for:OKL_SIF_RET_STRMS_V --
2009 ----------------------------------------
2010 PROCEDURE update_row(
2011 p_api_version IN NUMBER,
2012 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2013 x_return_status OUT NOCOPY VARCHAR2,
2014 x_msg_count OUT NOCOPY NUMBER,
2015 x_msg_data OUT NOCOPY VARCHAR2,
2016 p_srsv_rec IN srsv_rec_type,
2017 x_srsv_rec OUT NOCOPY srsv_rec_type) IS
2018
2019 l_api_version CONSTANT NUMBER := 1;
2020 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2021 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2022 l_srsv_rec srsv_rec_type := p_srsv_rec;
2023 l_def_srsv_rec srsv_rec_type;
2024 l_srs_rec srs_rec_type;
2025 lx_srs_rec srs_rec_type;
2026 -------------------------------
2027 -- FUNCTION fill_who_columns --
2028 -------------------------------
2029 FUNCTION fill_who_columns (
2030 p_srsv_rec IN srsv_rec_type
2031 ) RETURN srsv_rec_type IS
2032 l_srsv_rec srsv_rec_type := p_srsv_rec;
2033 BEGIN
2034 l_srsv_rec.LAST_UPDATE_DATE := SYSDATE;
2035 l_srsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2036 l_srsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2037 RETURN(l_srsv_rec);
2038 END fill_who_columns;
2039 ----------------------------------
2040 -- FUNCTION populate_new_record --
2041 ----------------------------------
2042 FUNCTION populate_new_record (
2043 p_srsv_rec IN srsv_rec_type,
2044 x_srsv_rec OUT NOCOPY srsv_rec_type
2045 ) RETURN VARCHAR2 IS
2046 l_srsv_rec srsv_rec_type;
2047 l_row_notfound BOOLEAN := TRUE;
2048 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2049 BEGIN
2050 x_srsv_rec := p_srsv_rec;
2051 -- Get current database values
2052 l_srsv_rec := get_rec(p_srsv_rec, l_row_notfound);
2053 IF (l_row_notfound) THEN
2054 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2055 END IF;
2056 IF (x_srsv_rec.id IS NULL)
2057 THEN
2058 x_srsv_rec.id := l_srsv_rec.id;
2059 END IF;
2060 IF (x_srsv_rec.stream_type_name IS NULL)
2061 THEN
2062 x_srsv_rec.stream_type_name := l_srsv_rec.stream_type_name;
2063 END IF;
2064 IF (x_srsv_rec.index_number IS NULL)
2065 THEN
2066 x_srsv_rec.index_number := l_srsv_rec.index_number;
2067 END IF;
2068 IF (x_srsv_rec.activity_type IS NULL)
2069 THEN
2070 x_srsv_rec.activity_type := l_srsv_rec.activity_type;
2071 END IF;
2072 IF (x_srsv_rec.sequence_number IS NULL)
2073 THEN
2074 x_srsv_rec.sequence_number := l_srsv_rec.sequence_number;
2075 END IF;
2076 IF (x_srsv_rec.stream_interface_attribute01 IS NULL)
2077 THEN
2078 x_srsv_rec.stream_interface_attribute01 := l_srsv_rec.stream_interface_attribute01;
2079 END IF;
2080 IF (x_srsv_rec.stream_interface_attribute02 IS NULL)
2081 THEN
2082 x_srsv_rec.stream_interface_attribute02 := l_srsv_rec.stream_interface_attribute02;
2083 END IF;
2084 IF (x_srsv_rec.stream_interface_attribute03 IS NULL)
2085 THEN
2086 x_srsv_rec.stream_interface_attribute03 := l_srsv_rec.stream_interface_attribute03;
2087 END IF;
2088 IF (x_srsv_rec.stream_interface_attribute04 IS NULL)
2089 THEN
2090 x_srsv_rec.stream_interface_attribute04 := l_srsv_rec.stream_interface_attribute04;
2091 END IF;
2092 IF (x_srsv_rec.stream_interface_attribute05 IS NULL)
2093 THEN
2094 x_srsv_rec.stream_interface_attribute05 := l_srsv_rec.stream_interface_attribute05;
2095 END IF;
2096 IF (x_srsv_rec.stream_interface_attribute06 IS NULL)
2097 THEN
2098 x_srsv_rec.stream_interface_attribute06 := l_srsv_rec.stream_interface_attribute06;
2099 END IF;
2100 IF (x_srsv_rec.stream_interface_attribute07 IS NULL)
2101 THEN
2102 x_srsv_rec.stream_interface_attribute07 := l_srsv_rec.stream_interface_attribute07;
2103 END IF;
2104 IF (x_srsv_rec.stream_interface_attribute08 IS NULL)
2105 THEN
2106 x_srsv_rec.stream_interface_attribute08 := l_srsv_rec.stream_interface_attribute08;
2107 END IF;
2108 IF (x_srsv_rec.stream_interface_attribute09 IS NULL)
2109 THEN
2110 x_srsv_rec.stream_interface_attribute09 := l_srsv_rec.stream_interface_attribute09;
2111 END IF;
2112 IF (x_srsv_rec.stream_interface_attribute10 IS NULL)
2113 THEN
2114 x_srsv_rec.stream_interface_attribute10 := l_srsv_rec.stream_interface_attribute10;
2115 END IF;
2116 IF (x_srsv_rec.stream_interface_attribute11 IS NULL)
2117 THEN
2118 x_srsv_rec.stream_interface_attribute11 := l_srsv_rec.stream_interface_attribute11;
2119 END IF;
2120 IF (x_srsv_rec.stream_interface_attribute12 IS NULL)
2121 THEN
2122 x_srsv_rec.stream_interface_attribute12 := l_srsv_rec.stream_interface_attribute12;
2123 END IF;
2124 IF (x_srsv_rec.stream_interface_attribute13 IS NULL)
2125 THEN
2126 x_srsv_rec.stream_interface_attribute13 := l_srsv_rec.stream_interface_attribute13;
2127 END IF;
2128 IF (x_srsv_rec.stream_interface_attribute14 IS NULL)
2129 THEN
2130 x_srsv_rec.stream_interface_attribute14 := l_srsv_rec.stream_interface_attribute14;
2131 END IF;
2132 IF (x_srsv_rec.stream_interface_attribute15 IS NULL)
2133 THEN
2134 x_srsv_rec.stream_interface_attribute15 := l_srsv_rec.stream_interface_attribute15;
2135 END IF;
2136 IF (x_srsv_rec.sre_date IS NULL)
2137 THEN
2138 x_srsv_rec.sre_date := l_srsv_rec.sre_date;
2139 END IF;
2140 IF (x_srsv_rec.amount IS NULL)
2141 THEN
2142 x_srsv_rec.amount := l_srsv_rec.amount;
2143 END IF;
2144 IF (x_srsv_rec.sir_id IS NULL)
2145 THEN
2146 x_srsv_rec.sir_id := l_srsv_rec.sir_id;
2147 END IF;
2148 IF (x_srsv_rec.object_version_number IS NULL)
2149 THEN
2150 x_srsv_rec.object_version_number := l_srsv_rec.object_version_number;
2151 END IF;
2152 IF (x_srsv_rec.created_by IS NULL)
2153 THEN
2154 x_srsv_rec.created_by := l_srsv_rec.created_by;
2155 END IF;
2156 IF (x_srsv_rec.last_updated_by IS NULL)
2157 THEN
2158 x_srsv_rec.last_updated_by := l_srsv_rec.last_updated_by;
2159 END IF;
2160 IF (x_srsv_rec.creation_date IS NULL)
2161 THEN
2162 x_srsv_rec.creation_date := l_srsv_rec.creation_date;
2163 END IF;
2164 IF (x_srsv_rec.last_update_date IS NULL)
2165 THEN
2166 x_srsv_rec.last_update_date := l_srsv_rec.last_update_date;
2167 END IF;
2168 IF (x_srsv_rec.last_update_login IS NULL)
2169 THEN
2170 x_srsv_rec.last_update_login := l_srsv_rec.last_update_login;
2171 END IF;
2172 RETURN(l_return_status);
2173 END populate_new_record;
2174 --------------------------------------------
2175 -- Set_Attributes for:OKL_SIF_RET_STRMS_V --
2176 --------------------------------------------
2177 FUNCTION Set_Attributes (
2178 p_srsv_rec IN srsv_rec_type,
2179 x_srsv_rec OUT NOCOPY srsv_rec_type
2180 ) RETURN VARCHAR2 IS
2181 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2182 BEGIN
2183 x_srsv_rec := p_srsv_rec;
2184 x_srsv_rec.OBJECT_VERSION_NUMBER := NVL(x_srsv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2185 RETURN(l_return_status);
2186 END Set_Attributes;
2187 BEGIN
2188 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2189 G_PKG_NAME,
2190 p_init_msg_list,
2191 l_api_version,
2192 p_api_version,
2193 '_PVT',
2194 x_return_status);
2195 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2196 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2197 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2198 RAISE OKC_API.G_EXCEPTION_ERROR;
2199 END IF;
2200 --- Setting item attributes
2201 l_return_status := Set_Attributes(
2202 p_srsv_rec, -- IN
2203 l_srsv_rec); -- OUT
2204 --- If any errors happen abort API
2205 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2206 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2207 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2208 RAISE OKC_API.G_EXCEPTION_ERROR;
2209 END IF;
2210 /*
2211 l_return_status := populate_new_record(l_srsv_rec, l_def_srsv_rec);
2212 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2214 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2215 RAISE OKC_API.G_EXCEPTION_ERROR;
2216 END IF;
2217 */
2218 l_def_srsv_rec := fill_who_columns(l_srsv_rec);
2219 --- Validate all non-missing attributes (Item Level Validation)
2220
2221 /*
2222 l_return_status := Validate_Attributes(l_def_srsv_rec);
2223 --- If any errors happen abort API
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 l_return_status := Validate_Record(l_def_srsv_rec);
2230 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2231 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2232 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2233 RAISE OKC_API.G_EXCEPTION_ERROR;
2234 END IF;
2235 */
2236 --------------------------------------
2237 -- Move VIEW record to "Child" records
2238 --------------------------------------
2239 migrate(l_def_srsv_rec, l_srs_rec);
2240 --------------------------------------------
2241 -- Call the UPDATE_ROW for each child record
2242 --------------------------------------------
2243 update_row(
2244 p_init_msg_list,
2245 x_return_status,
2246 x_msg_count,
2247 x_msg_data,
2248 l_srs_rec,
2249 lx_srs_rec
2250 );
2251 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2252 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2253 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2254 RAISE OKC_API.G_EXCEPTION_ERROR;
2255 END IF;
2256 migrate(lx_srs_rec, l_def_srsv_rec);
2257 x_srsv_rec := l_def_srsv_rec;
2258 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2259 EXCEPTION
2260 WHEN OKC_API.G_EXCEPTION_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_ERROR',
2266 x_msg_count,
2267 x_msg_data,
2268 '_PVT'
2269 );
2270 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2271 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2272 (
2273 l_api_name,
2274 G_PKG_NAME,
2275 'OKC_API.G_RET_STS_UNEXP_ERROR',
2276 x_msg_count,
2277 x_msg_data,
2278 '_PVT'
2279 );
2280 WHEN OTHERS THEN
2281 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2282 (
2283 l_api_name,
2284 G_PKG_NAME,
2285 'OTHERS',
2286 x_msg_count,
2287 x_msg_data,
2288 '_PVT'
2289 );
2290 END update_row;
2291 ----------------------------------------
2292 -- PL/SQL TBL update_row for:SRSV_TBL --
2293 ----------------------------------------
2294 PROCEDURE update_row(
2295 p_api_version IN NUMBER,
2296 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2297 x_return_status OUT NOCOPY VARCHAR2,
2298 x_msg_count OUT NOCOPY NUMBER,
2299 x_msg_data OUT NOCOPY VARCHAR2,
2300 p_srsv_tbl IN srsv_tbl_type,
2301 x_srsv_tbl OUT NOCOPY srsv_tbl_type) IS
2302
2303 l_api_version CONSTANT NUMBER := 1;
2304 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2305 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2306 i NUMBER := 0;
2307 -- START change : mvasudev, 09/05/2001
2308 -- Adding OverAll Status Flag
2309 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2310 -- END change : mvasudev
2311 BEGIN
2312 OKC_API.init_msg_list(p_init_msg_list);
2313 -- Make sure PL/SQL table has records in it before passing
2314 IF (p_srsv_tbl.COUNT > 0) THEN
2315 i := p_srsv_tbl.FIRST;
2316 LOOP
2317 update_row (
2318 p_api_version => p_api_version,
2319 p_init_msg_list => OKC_API.G_FALSE,
2320 x_return_status => x_return_status,
2321 x_msg_count => x_msg_count,
2322 x_msg_data => x_msg_data,
2323 p_srsv_rec => p_srsv_tbl(i),
2324 x_srsv_rec => x_srsv_tbl(i));
2325 -- START change : mvasudev, 09/05/2001
2326 -- store the highest degree of error
2327 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2328 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2329 l_overall_status := x_return_status;
2330 END IF;
2331 END IF;
2332 -- END change : mvasudev
2333 EXIT WHEN (i = p_srsv_tbl.LAST);
2334 i := p_srsv_tbl.NEXT(i);
2335 END LOOP;
2336 -- START change : mvasudev, 09/05/2001
2337 -- return overall status
2338 x_return_status := l_overall_status;
2339 -- END change : mvasudev
2340 END IF;
2341 EXCEPTION
2342 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2343 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2344 (
2345 l_api_name,
2346 G_PKG_NAME,
2347 'OKC_API.G_RET_STS_ERROR',
2348 x_msg_count,
2349 x_msg_data,
2350 '_PVT'
2351 );
2352 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2353 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2354 (
2355 l_api_name,
2356 G_PKG_NAME,
2357 'OKC_API.G_RET_STS_UNEXP_ERROR',
2358 x_msg_count,
2359 x_msg_data,
2360 '_PVT'
2361 );
2362 WHEN OTHERS THEN
2363 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2364 (
2365 l_api_name,
2366 G_PKG_NAME,
2367 'OTHERS',
2368 x_msg_count,
2369 x_msg_data,
2370 '_PVT'
2371 );
2372 END update_row;
2373
2374 ---------------------------------------------------------------------------
2375 -- PROCEDURE delete_row
2376 ---------------------------------------------------------------------------
2377 --------------------------------------
2378 -- delete_row for:OKL_SIF_RET_STRMS --
2379 --------------------------------------
2380 PROCEDURE delete_row(
2381 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2382 x_return_status OUT NOCOPY VARCHAR2,
2383 x_msg_count OUT NOCOPY NUMBER,
2384 x_msg_data OUT NOCOPY VARCHAR2,
2385 p_srs_rec IN srs_rec_type) IS
2386
2387 l_api_version CONSTANT NUMBER := 1;
2388 l_api_name CONSTANT VARCHAR2(30) := 'STRMS_delete_row';
2389 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2390 l_srs_rec srs_rec_type:= p_srs_rec;
2391 l_row_notfound BOOLEAN := TRUE;
2392 BEGIN
2393 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2394 p_init_msg_list,
2395 '_PVT',
2396 x_return_status);
2397 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2398 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2399 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2400 RAISE OKC_API.G_EXCEPTION_ERROR;
2401 END IF;
2402 DELETE FROM OKL_SIF_RET_STRMS
2403 WHERE ID = l_srs_rec.id;
2404
2405 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2406 EXCEPTION
2407 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2408 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2409 (
2410 l_api_name,
2411 G_PKG_NAME,
2412 'OKC_API.G_RET_STS_ERROR',
2413 x_msg_count,
2414 x_msg_data,
2415 '_PVT'
2416 );
2417 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2418 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'OKC_API.G_RET_STS_UNEXP_ERROR',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 WHEN OTHERS THEN
2428 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2429 (
2430 l_api_name,
2431 G_PKG_NAME,
2432 'OTHERS',
2433 x_msg_count,
2434 x_msg_data,
2435 '_PVT'
2436 );
2437 END delete_row;
2438 ----------------------------------------
2439 -- delete_row for:OKL_SIF_RET_STRMS_V --
2440 ----------------------------------------
2441 PROCEDURE delete_row(
2442 p_api_version IN NUMBER,
2443 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2444 x_return_status OUT NOCOPY VARCHAR2,
2445 x_msg_count OUT NOCOPY NUMBER,
2446 x_msg_data OUT NOCOPY VARCHAR2,
2447 p_srsv_rec IN srsv_rec_type) IS
2448
2449 l_api_version CONSTANT NUMBER := 1;
2450 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2451 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2452 l_srsv_rec srsv_rec_type := p_srsv_rec;
2453 l_srs_rec srs_rec_type;
2454 BEGIN
2455 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2456 G_PKG_NAME,
2457 p_init_msg_list,
2458 l_api_version,
2459 p_api_version,
2460 '_PVT',
2461 x_return_status);
2462 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2463 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2464 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2465 RAISE OKC_API.G_EXCEPTION_ERROR;
2466 END IF;
2467 --------------------------------------
2468 -- Move VIEW record to "Child" records
2469 --------------------------------------
2470 migrate(l_srsv_rec, l_srs_rec);
2471 --------------------------------------------
2472 -- Call the DELETE_ROW for each child record
2473 --------------------------------------------
2474 delete_row(
2475 p_init_msg_list,
2476 x_return_status,
2477 x_msg_count,
2478 x_msg_data,
2479 l_srs_rec
2480 );
2481 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2482 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2483 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2484 RAISE OKC_API.G_EXCEPTION_ERROR;
2485 END IF;
2486 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2487 EXCEPTION
2488 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2489 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2490 (
2491 l_api_name,
2492 G_PKG_NAME,
2493 'OKC_API.G_RET_STS_ERROR',
2494 x_msg_count,
2495 x_msg_data,
2496 '_PVT'
2497 );
2498 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2499 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2500 (
2501 l_api_name,
2502 G_PKG_NAME,
2503 'OKC_API.G_RET_STS_UNEXP_ERROR',
2504 x_msg_count,
2505 x_msg_data,
2506 '_PVT'
2507 );
2508 WHEN OTHERS THEN
2509 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2510 (
2511 l_api_name,
2512 G_PKG_NAME,
2513 'OTHERS',
2514 x_msg_count,
2515 x_msg_data,
2516 '_PVT'
2517 );
2518 END delete_row;
2519 ----------------------------------------
2520 -- PL/SQL TBL delete_row for:SRSV_TBL --
2521 ----------------------------------------
2522 PROCEDURE delete_row(
2523 p_api_version IN NUMBER,
2524 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2525 x_return_status OUT NOCOPY VARCHAR2,
2526 x_msg_count OUT NOCOPY NUMBER,
2527 x_msg_data OUT NOCOPY VARCHAR2,
2528 p_srsv_tbl IN srsv_tbl_type) IS
2529
2530 l_api_version CONSTANT NUMBER := 1;
2531 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2532 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2533 i NUMBER := 0;
2534 -- START change : mvasudev, 09/05/2001
2535 -- Adding OverAll Status Flag
2536 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2537 -- END change : mvasudev
2538 BEGIN
2539 OKC_API.init_msg_list(p_init_msg_list);
2540 -- Make sure PL/SQL table has records in it before passing
2541 IF (p_srsv_tbl.COUNT > 0) THEN
2542 i := p_srsv_tbl.FIRST;
2543 LOOP
2544 delete_row (
2545 p_api_version => p_api_version,
2546 p_init_msg_list => OKC_API.G_FALSE,
2547 x_return_status => x_return_status,
2548 x_msg_count => x_msg_count,
2549 x_msg_data => x_msg_data,
2550 p_srsv_rec => p_srsv_tbl(i));
2551 -- START change : mvasudev, 09/05/2001
2552 -- store the highest degree of error
2553 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2554 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2555 l_overall_status := x_return_status;
2556 END IF;
2557 END IF;
2558 -- END change : mvasudev
2559 EXIT WHEN (i = p_srsv_tbl.LAST);
2560 i := p_srsv_tbl.NEXT(i);
2561 END LOOP;
2562 -- START change : mvasudev, 09/05/2001
2563 -- return overall status
2564 x_return_status := l_overall_status;
2565 -- END change : mvasudev
2566 END IF;
2567 EXCEPTION
2568 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2569 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2570 (
2571 l_api_name,
2572 G_PKG_NAME,
2573 'OKC_API.G_RET_STS_ERROR',
2574 x_msg_count,
2575 x_msg_data,
2576 '_PVT'
2577 );
2578 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2579 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2580 (
2581 l_api_name,
2582 G_PKG_NAME,
2583 'OKC_API.G_RET_STS_UNEXP_ERROR',
2584 x_msg_count,
2585 x_msg_data,
2586 '_PVT'
2587 );
2588 WHEN OTHERS THEN
2589 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2590 (
2591 l_api_name,
2592 G_PKG_NAME,
2593 'OTHERS',
2594 x_msg_count,
2595 x_msg_data,
2596 '_PVT'
2597 );
2598 END delete_row;
2599
2600 --BAKUCHIB Bug#2807737 start
2601 ---------------------------------------------
2602 --- insert_row_per for:OKL_SIF_RET_STRMS_V --
2603 ---------------------------------------------
2604 -- Start of comments
2605 -- Procedure Name : insert_row_per
2606 -- Description : Used extend insert_row without having the actuall insert
2607 -- into the table
2608 -- Business Rules :
2609 -- Parameters : Record structure of OKL_SIF_RET_STRMS table
2610 -- Version : 1.0
2611 -- History : 09-MAY-20023 BAKUCHIB :Added new procedure
2612 -- End of comments
2613
2614 PROCEDURE insert_row_per(
2615 p_api_version IN NUMBER,
2616 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2617 x_return_status OUT NOCOPY VARCHAR2,
2618 x_msg_count OUT NOCOPY NUMBER,
2619 x_msg_data OUT NOCOPY VARCHAR2,
2620 p_srsv_rec IN srsv_rec_type,
2621 x_srsv_rec OUT NOCOPY srsv_rec_type) IS
2622
2623 l_api_version CONSTANT NUMBER := 1;
2624 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2625 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2626 l_srsv_rec srsv_rec_type;
2627 l_def_srsv_rec srsv_rec_type;
2628 l_srs_rec srs_rec_type;
2629 lx_srs_rec srs_rec_type;
2630
2631 -------------------------------
2632 -- FUNCTION fill_who_columns --
2633 -------------------------------
2634
2635 FUNCTION fill_who_columns (
2636 p_srsv_rec IN srsv_rec_type
2637 ) RETURN srsv_rec_type IS
2638 l_srsv_rec srsv_rec_type := p_srsv_rec;
2639 BEGIN
2640 l_srsv_rec.CREATION_DATE := SYSDATE;
2641 l_srsv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2642 l_srsv_rec.LAST_UPDATE_DATE := SYSDATE;
2643 l_srsv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2644 l_srsv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2645 RETURN(l_srsv_rec);
2646 END fill_who_columns;
2647 --------------------------------------------
2648 -- Set_Attributes for:OKL_SIF_RET_STRMS_V --
2649 --------------------------------------------
2650 FUNCTION Set_Attributes (
2651 p_srsv_rec IN srsv_rec_type,
2652 x_srsv_rec OUT NOCOPY srsv_rec_type
2653 ) RETURN VARCHAR2 IS
2654 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2655 BEGIN
2656 x_srsv_rec := p_srsv_rec;
2657 x_srsv_rec.OBJECT_VERSION_NUMBER := 1;
2658 RETURN(l_return_status);
2659 END Set_Attributes;
2660
2661 BEGIN
2662 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2663 G_PKG_NAME,
2664 p_init_msg_list,
2665 l_api_version,
2666 p_api_version,
2667 '_PVT',
2668 x_return_status);
2669 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2670 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2671 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2672 RAISE OKC_API.G_EXCEPTION_ERROR;
2673 END IF;
2674 l_srsv_rec := null_out_defaults(p_srsv_rec);
2675 -- Set primary key value
2676 l_srsv_rec.ID := get_seq_id;
2677 --- Setting item attributes
2678 l_return_status := Set_Attributes(
2679 l_srsv_rec, -- IN
2680 l_def_srsv_rec); -- OUT
2681 --- If any errors happen abort API
2682 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2683 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2684 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2685 RAISE OKC_API.G_EXCEPTION_ERROR;
2686 END IF;
2687 l_def_srsv_rec := fill_who_columns(l_def_srsv_rec);
2688 x_srsv_rec := l_def_srsv_rec;
2689 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2690 EXCEPTION
2691 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2692 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2693 (
2694 l_api_name,
2695 G_PKG_NAME,
2696 'OKC_API.G_RET_STS_ERROR',
2697 x_msg_count,
2698 x_msg_data,
2699 '_PVT'
2700 );
2701 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2702 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2703 (
2704 l_api_name,
2705 G_PKG_NAME,
2706 'OKC_API.G_RET_STS_UNEXP_ERROR',
2707 x_msg_count,
2708 x_msg_data,
2709 '_PVT'
2710 );
2711 WHEN OTHERS THEN
2712 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2713 (
2714 l_api_name,
2715 G_PKG_NAME,
2716 'OTHERS',
2717 x_msg_count,
2718 x_msg_data,
2719 '_PVT'
2720 );
2721
2722 END insert_row_per;
2723 ---------------------------------------------
2724 --- insert_row_upg for:OKL_SIF_RET_STRMS_V --
2725 ---------------------------------------------
2726 -- Start of comments
2727 -- Procedure Name : insert_row_upg
2728 -- Description : Used for the Bulk insert into the OKL_SIF_RET_STRMS table
2729 -- Business Rules :
2730 -- Parameters : PL/SQL Table structure of OKL_SIF_RET_STRMS table
2731 -- Version : 1.0
2732 -- History : 09-MAY-20023 BAKUCHIB :Added new procedure
2733 PROCEDURE insert_row_upg(p_srsv_tbl srsv_Tbl_type) IS
2734 l_tabsize NUMBER := p_srsv_tbl.COUNT;
2735 in_id Okl_Streams_Util.NumberTabTyp;
2736 in_stream_type_name Okl_Streams_Util.Var150TabTyp;
2737 in_index_number Okl_Streams_Util.NumberTabTyp;
2738 in_activity_type Okl_Streams_Util.Var150TabTyp;
2739 in_sequence_number Okl_Streams_Util.NumberTabTyp;
2740 in_sre_date Okl_Streams_Util.DateTabTyp;
2741 in_amount Okl_Streams_Util.NumberTabTyp;
2742 in_sir_id Okl_Streams_Util.NumberTabTyp;
2743 in_stream_int_attribute01 Okl_Streams_Util.Var450TabTyp;
2744 in_stream_int_attribute02 Okl_Streams_Util.Var450TabTyp;
2745 in_stream_int_attribute03 Okl_Streams_Util.Var450TabTyp;
2746 in_stream_int_attribute04 Okl_Streams_Util.Var450TabTyp;
2747 in_stream_int_attribute05 Okl_Streams_Util.Var450TabTyp;
2748 in_stream_int_attribute06 Okl_Streams_Util.Var450TabTyp;
2749 in_stream_int_attribute07 Okl_Streams_Util.Var450TabTyp;
2750 in_stream_int_attribute08 Okl_Streams_Util.Var450TabTyp;
2751 in_stream_int_attribute09 Okl_Streams_Util.Var450TabTyp;
2752 in_stream_int_attribute10 Okl_Streams_Util.Var450TabTyp;
2753 in_stream_int_attribute11 Okl_Streams_Util.Var450TabTyp;
2754 in_stream_int_attribute12 Okl_Streams_Util.Var450TabTyp;
2755 in_stream_int_attribute13 Okl_Streams_Util.Var450TabTyp;
2756 in_stream_int_attribute14 Okl_Streams_Util.Var450TabTyp;
2757 in_stream_int_attribute15 Okl_Streams_Util.Var450TabTyp;
2758 in_object_version_number Okl_Streams_Util.NumberTabTyp;
2759 in_created_by Okl_Streams_Util.NumberTabTyp;
2760 in_last_updated_by Okl_Streams_Util.NumberTabTyp;
2761 in_creation_date Okl_Streams_Util.DateTabTyp;
2762 in_last_update_date Okl_Streams_Util.DateTabTyp;
2763 in_last_update_login Okl_Streams_Util.NumberTabTyp;
2764 i number;
2765 j number;
2766 --Declaring the temp variables used for assignment purposes
2767 --Added by KTHIRUVA
2768 --Bug 4302322 - Start of Changes
2769 l_created_by NUMBER;
2770 l_last_updated_by NUMBER;
2771 l_creation_date DATE;
2772 l_last_update_date DATE;
2773 l_last_update_login NUMBER;
2774 --End of Changes
2775
2776 BEGIN
2777 i := p_srsv_tbl.FIRST; j:=0;
2778 --Modified by KTHIRUVA
2779 --Initialising the params
2780 --Bug No 4302322 - Start Of Changes
2781 l_created_by := FND_GLOBAL.USER_ID;
2782 l_last_updated_by := FND_GLOBAL.USER_ID;
2783 l_creation_date := SYSDATE;
2784 l_last_update_date := SYSDATE;
2785 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2786 --Bug -End of Changes
2787
2788 WHILE i is not null LOOP
2789 j:=j+1;
2790 --Modified by BKATRAGA on 07-Apr-2005
2791 --Bug No 4302322 - Start of Changes
2792 --Obtaining the id from the sequence
2793 in_id(j) := get_seq_id;
2794 --Bug No - End of Changes
2795 in_stream_type_name(j) := p_srsv_tbl(i).stream_type_name;
2796 in_index_number(j) := p_srsv_tbl(i).index_number;
2797 in_activity_type(j) := p_srsv_tbl(i).activity_type;
2798 in_sequence_number(j) := p_srsv_tbl(i).sequence_number;
2799 in_sre_date(j) := p_srsv_tbl(i).sre_date;
2800 in_amount(j) := p_srsv_tbl(i).amount;
2801 in_sir_id(j) := p_srsv_tbl(i).sir_id;
2802 in_stream_int_attribute01(j) := p_srsv_tbl(i).stream_interface_attribute01;
2803 in_stream_int_attribute02(j) := p_srsv_tbl(i).stream_interface_attribute02;
2804 in_stream_int_attribute03(j) := p_srsv_tbl(i).stream_interface_attribute03;
2805 in_stream_int_attribute04(j) := p_srsv_tbl(i).stream_interface_attribute04;
2806 in_stream_int_attribute05(j) := p_srsv_tbl(i).stream_interface_attribute05;
2807 in_stream_int_attribute06(j) := p_srsv_tbl(i).stream_interface_attribute06;
2808 in_stream_int_attribute07(j) := p_srsv_tbl(i).stream_interface_attribute07;
2809 in_stream_int_attribute08(j) := p_srsv_tbl(i).stream_interface_attribute08;
2810 in_stream_int_attribute09(j) := p_srsv_tbl(i).stream_interface_attribute09;
2811 in_stream_int_attribute10(j) := p_srsv_tbl(i).stream_interface_attribute10;
2812 in_stream_int_attribute11(j) := p_srsv_tbl(i).stream_interface_attribute11;
2813 in_stream_int_attribute12(j) := p_srsv_tbl(i).stream_interface_attribute12;
2814 in_stream_int_attribute13(j) := p_srsv_tbl(i).stream_interface_attribute13;
2815 in_stream_int_attribute14(j) := p_srsv_tbl(i).stream_interface_attribute14;
2816 in_stream_int_attribute15(j) := p_srsv_tbl(i).stream_interface_attribute15;
2817 --Modified by BKATRAGA on 07-Apr-2005
2818 --Bug No 4302322- Start of Changes
2819 in_object_version_number(j) := 1;
2820 in_created_by(j) := l_created_by;
2821 in_last_updated_by(j) := l_last_updated_by;
2822 in_creation_date(j) := l_creation_date;
2823 in_last_update_date(j) := l_last_update_date;
2824 in_last_update_login(j) := l_last_update_login;
2825 --Bug No - End of Changes
2826 i:= p_srsv_tbl.next(i);
2827 END LOOP;
2828
2829 FORALL i in 1..l_tabsize
2830 INSERT INTO OKL_SIF_RET_STRMS(
2831 id,
2832 stream_type_name,
2833 index_number,
2834 activity_type,
2835 sequence_number,
2836 stream_interface_attribute01,
2837 stream_interface_attribute02,
2838 stream_interface_attribute03,
2839 stream_interface_attribute04,
2840 stream_interface_attribute05,
2841 stream_interface_attribute06,
2842 stream_interface_attribute07,
2843 stream_interface_attribute08,
2844 stream_interface_attribute09,
2845 stream_interface_attribute10,
2846 stream_interface_attribute11,
2847 stream_interface_attribute12,
2848 stream_interface_attribute13,
2849 stream_interface_attribute14,
2850 stream_interface_attribute15,
2851 sre_date,
2852 amount,
2853 sir_id,
2854 object_version_number,
2855 created_by,
2856 last_updated_by,
2857 creation_date,
2858 last_update_date,
2859 last_update_login)
2860 VALUES (in_id(i),
2861 in_stream_type_name(i),
2862 in_index_number(i),
2863 in_activity_type(i),
2864 in_sequence_number(i),
2865 in_stream_int_attribute01(i),
2866 in_stream_int_attribute02(i),
2867 in_stream_int_attribute03(i),
2868 in_stream_int_attribute04(i),
2869 in_stream_int_attribute05(i),
2870 in_stream_int_attribute06(i),
2871 in_stream_int_attribute07(i),
2872 in_stream_int_attribute08(i),
2873 in_stream_int_attribute09(i),
2874 in_stream_int_attribute10(i),
2875 in_stream_int_attribute11(i),
2876 in_stream_int_attribute12(i),
2877 in_stream_int_attribute13(i),
2878 in_stream_int_attribute14(i),
2879 in_stream_int_attribute15(i),
2880 in_sre_date(i),
2881 in_amount(i),
2882 in_sir_id(i),
2883 in_object_version_number(i),
2884 in_created_by(i),
2885 in_last_updated_by(i),
2886 in_creation_date(i),
2887 in_last_update_date(i),
2888 in_last_update_login(i));
2889
2890 END insert_row_upg;
2891 --BAKUCHIB Bug#2807737 End
2892 END OKL_SRS_PVT ;