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