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