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