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