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