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