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