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