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