[Home] [Help]
PACKAGE BODY: APPS.OKL_XMP_PVT
Source
1 PACKAGE BODY OKL_XMP_PVT AS
2 /* $Header: OKLSXMPB.pls 120.2 2007/01/11 14:02:18 udhenuko noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_XMLP_PARAMS
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_xmp_rec IN xmp_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN xmp_rec_type IS
100 CURSOR okl_xmp_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 BATCH_ID,
104 PARAM_NAME,
105 OBJECT_VERSION_NUMBER,
106 PARAM_TYPE_CODE,
107 PARAM_VALUE,
108 ATTRIBUTE_CATEGORY,
109 ATTRIBUTE1,
110 ATTRIBUTE2,
111 ATTRIBUTE3,
112 ATTRIBUTE4,
113 ATTRIBUTE5,
114 ATTRIBUTE6,
115 ATTRIBUTE7,
116 ATTRIBUTE8,
117 ATTRIBUTE9,
118 ATTRIBUTE10,
119 ATTRIBUTE11,
120 ATTRIBUTE12,
121 ATTRIBUTE13,
122 ATTRIBUTE14,
123 ATTRIBUTE15,
124 CREATED_BY,
125 CREATION_DATE,
126 LAST_UPDATED_BY,
127 LAST_UPDATE_DATE,
128 LAST_UPDATE_LOGIN
129 FROM Okl_Xmlp_Params
130 WHERE okl_xmlp_params.id = p_id;
131 l_okl_xmp_pk okl_xmp_pk_csr%ROWTYPE;
132 l_xmp_rec xmp_rec_type;
133 BEGIN
134 x_no_data_found := TRUE;
135 -- Get current database values
136 OPEN okl_xmp_pk_csr (p_xmp_rec.id);
137 FETCH okl_xmp_pk_csr INTO
138 l_xmp_rec.id,
139 l_xmp_rec.batch_id,
140 l_xmp_rec.param_name,
141 l_xmp_rec.object_version_number,
142 l_xmp_rec.param_type_code,
143 l_xmp_rec.param_value,
144 l_xmp_rec.attribute_category,
145 l_xmp_rec.attribute1,
146 l_xmp_rec.attribute2,
147 l_xmp_rec.attribute3,
148 l_xmp_rec.attribute4,
149 l_xmp_rec.attribute5,
150 l_xmp_rec.attribute6,
151 l_xmp_rec.attribute7,
152 l_xmp_rec.attribute8,
153 l_xmp_rec.attribute9,
154 l_xmp_rec.attribute10,
155 l_xmp_rec.attribute11,
156 l_xmp_rec.attribute12,
157 l_xmp_rec.attribute13,
158 l_xmp_rec.attribute14,
159 l_xmp_rec.attribute15,
160 l_xmp_rec.created_by,
161 l_xmp_rec.creation_date,
162 l_xmp_rec.last_updated_by,
163 l_xmp_rec.last_update_date,
164 l_xmp_rec.last_update_login;
165 x_no_data_found := okl_xmp_pk_csr%NOTFOUND;
166 CLOSE okl_xmp_pk_csr;
167 RETURN(l_xmp_rec);
168 END get_rec;
169
170 ------------------------------------------------------------------
171 -- This version of get_rec sets error messages if no data found --
172 ------------------------------------------------------------------
173 FUNCTION get_rec (
174 p_xmp_rec IN xmp_rec_type,
175 x_return_status OUT NOCOPY VARCHAR2
176 ) RETURN xmp_rec_type IS
177 l_xmp_rec xmp_rec_type;
178 l_row_notfound BOOLEAN := TRUE;
179 BEGIN
180 x_return_status := OKL_API.G_RET_STS_SUCCESS;
181 l_xmp_rec := get_rec(p_xmp_rec, l_row_notfound);
182 IF (l_row_notfound) THEN
183 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
184 x_return_status := OKL_API.G_RET_STS_ERROR;
185 END IF;
186 RETURN(l_xmp_rec);
187 END get_rec;
188 -----------------------------------------------------------
189 -- So we don't have to pass an "l_row_notfound" variable --
190 -----------------------------------------------------------
191 FUNCTION get_rec (
192 p_xmp_rec IN xmp_rec_type
193 ) RETURN xmp_rec_type IS
194 l_row_not_found BOOLEAN := TRUE;
195 BEGIN
196 RETURN(get_rec(p_xmp_rec, l_row_not_found));
197 END get_rec;
198 ---------------------------------------------------------------------------
199 -- FUNCTION null_out_defaults for: OKL_XMLP_PARAMS
200 ---------------------------------------------------------------------------
201 FUNCTION null_out_defaults (
202 p_xmp_rec IN xmp_rec_type
203 ) RETURN xmp_rec_type IS
204 l_xmp_rec xmp_rec_type := p_xmp_rec;
205 BEGIN
206 /*IF (l_xmp_rec.id = OKL_API.G_MISS_NUM ) THEN
207 l_xmp_rec.id := NULL;
208 END IF;
209 IF (l_xmp_rec.batch_id = OKL_API.G_MISS_NUM ) THEN
210 l_xmp_rec.batch_id := NULL;
211 END IF;*/
212 IF (l_xmp_rec.param_name = OKL_API.G_MISS_CHAR ) THEN
213 l_xmp_rec.param_name := NULL;
214 END IF;
215 IF (l_xmp_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
216 l_xmp_rec.object_version_number := NULL;
217 END IF;
218 IF (l_xmp_rec.param_type_code = OKL_API.G_MISS_CHAR ) THEN
219 l_xmp_rec.param_type_code := NULL;
220 END IF;
221 IF (l_xmp_rec.param_value = OKL_API.G_MISS_CHAR ) THEN
222 l_xmp_rec.param_value := NULL;
223 END IF;
224 IF (l_xmp_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
225 l_xmp_rec.attribute_category := NULL;
226 END IF;
227 IF (l_xmp_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
228 l_xmp_rec.attribute1 := NULL;
229 END IF;
230 IF (l_xmp_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
231 l_xmp_rec.attribute2 := NULL;
232 END IF;
233 IF (l_xmp_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
234 l_xmp_rec.attribute3 := NULL;
235 END IF;
236 IF (l_xmp_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
237 l_xmp_rec.attribute4 := NULL;
238 END IF;
239 IF (l_xmp_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
240 l_xmp_rec.attribute5 := NULL;
241 END IF;
242 IF (l_xmp_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
243 l_xmp_rec.attribute6 := NULL;
244 END IF;
245 IF (l_xmp_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
246 l_xmp_rec.attribute7 := NULL;
247 END IF;
248 IF (l_xmp_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
249 l_xmp_rec.attribute8 := NULL;
250 END IF;
251 IF (l_xmp_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
252 l_xmp_rec.attribute9 := NULL;
253 END IF;
254 IF (l_xmp_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
255 l_xmp_rec.attribute10 := NULL;
256 END IF;
257 IF (l_xmp_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
258 l_xmp_rec.attribute11 := NULL;
259 END IF;
260 IF (l_xmp_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
261 l_xmp_rec.attribute12 := NULL;
262 END IF;
263 IF (l_xmp_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
264 l_xmp_rec.attribute13 := NULL;
265 END IF;
266 IF (l_xmp_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
267 l_xmp_rec.attribute14 := NULL;
268 END IF;
269 IF (l_xmp_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
270 l_xmp_rec.attribute15 := NULL;
271 END IF;
272 /*IF (l_xmp_rec.created_by = OKL_API.G_MISS_NUM ) THEN
273 l_xmp_rec.created_by := NULL;
274 END IF;
275 IF (l_xmp_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
276 l_xmp_rec.creation_date := NULL;
277 END IF;
278 IF (l_xmp_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
279 l_xmp_rec.last_updated_by := NULL;
280 END IF;
281 IF (l_xmp_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
282 l_xmp_rec.last_update_date := NULL;
283 END IF;
284 IF (l_xmp_rec.last_update_login = OKL_API.G_MISS_CHAR ) THEN
285 l_xmp_rec.last_update_login := NULL;
286 END IF;*/
287 RETURN(l_xmp_rec);
288 END null_out_defaults;
289 ---------------------------------
290 -- Validate_Attributes for: ID --
291 ---------------------------------
292 PROCEDURE validate_id(
293 x_return_status OUT NOCOPY VARCHAR2,
294 p_id IN NUMBER) IS
295 BEGIN
296 x_return_status := OKL_API.G_RET_STS_SUCCESS;
297 IF (p_id = OKL_API.G_MISS_NUM OR
298 p_id IS NULL)
299 THEN
300 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
301 x_return_status := OKL_API.G_RET_STS_ERROR;
302 RAISE G_EXCEPTION_HALT_VALIDATION;
303 END IF;
304 EXCEPTION
305 WHEN G_EXCEPTION_HALT_VALIDATION THEN
306 null;
307 WHEN OTHERS THEN
308 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
309 ,p_msg_name => G_UNEXPECTED_ERROR
310 ,p_token1 => G_SQLCODE_TOKEN
311 ,p_token1_value => SQLCODE
312 ,p_token2 => G_SQLERRM_TOKEN
313 ,p_token2_value => SQLERRM);
314 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
315 END validate_id;
316 ---------------------------------------
317 -- Validate_Attributes for: BATCH_ID --
318 ---------------------------------------
319 PROCEDURE validate_batch_id(
320 x_return_status OUT NOCOPY VARCHAR2,
321 p_batch_id IN NUMBER) IS
322 BEGIN
323 x_return_status := OKL_API.G_RET_STS_SUCCESS;
324 IF (p_batch_id = OKL_API.G_MISS_NUM OR
325 p_batch_id IS NULL)
326 THEN
327 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'batch_id');
328 x_return_status := OKL_API.G_RET_STS_ERROR;
329 RAISE G_EXCEPTION_HALT_VALIDATION;
330 END IF;
331 EXCEPTION
332 WHEN G_EXCEPTION_HALT_VALIDATION THEN
333 null;
334 WHEN OTHERS THEN
335 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
336 ,p_msg_name => G_UNEXPECTED_ERROR
337 ,p_token1 => G_SQLCODE_TOKEN
338 ,p_token1_value => SQLCODE
339 ,p_token2 => G_SQLERRM_TOKEN
340 ,p_token2_value => SQLERRM);
341 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
342 END validate_batch_id;
343
344 ---------------------------------------
345 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
346 ---------------------------------------
347 PROCEDURE validate_object_version_number(
348 x_return_status OUT NOCOPY VARCHAR2,
349 p_object_version_number IN NUMBER) IS
350 BEGIN
351 x_return_status := OKL_API.G_RET_STS_SUCCESS;
352 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
353 p_object_version_number IS NULL)
354 THEN
355 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
356 x_return_status := OKL_API.G_RET_STS_ERROR;
357 RAISE G_EXCEPTION_HALT_VALIDATION;
358 END IF;
359 EXCEPTION
360 WHEN G_EXCEPTION_HALT_VALIDATION THEN
361 null;
362 WHEN OTHERS THEN
363 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
364 ,p_msg_name => G_UNEXPECTED_ERROR
365 ,p_token1 => G_SQLCODE_TOKEN
366 ,p_token1_value => SQLCODE
367 ,p_token2 => G_SQLERRM_TOKEN
368 ,p_token2_value => SQLERRM);
369 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
370 END validate_object_version_number;
371
372 ---------------------------------------
373 -- Validate_Attributes for: PARAM_TYPE_CODE --
374 ---------------------------------------
375 PROCEDURE validate_param_type_code(
376 x_return_status OUT NOCOPY VARCHAR2,
377 p_param_type_code IN VARCHAR2) IS
378
379 l_value NUMBER ;
380 l_row_found BOOLEAN ;
381
382 CURSOR c_check_param_type(l_param_type_code IN OKL_XMLP_PARAMS.Param_type_code%TYPE) IS
383 SELECT 1
384 FROM FND_LOOKUPS
385 WHERE LOOKUP_TYPE = 'OKL_ECC_DATA_TYPE'
386 AND LOOKUP_CODE = l_param_type_code;
387
388 BEGIN
389
390
391 x_return_status := OKL_API.G_RET_STS_SUCCESS;
392
393 OPEN c_check_param_type(p_param_type_code);
394 FETCH c_check_param_type INTO l_value;
395 l_row_found := c_check_param_type%FOUND;
396 CLOSE c_check_param_type;
397 IF NOT l_row_found THEN
398 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'param_type_code');
399 x_return_status := OKL_API.G_RET_STS_ERROR;
400 RAISE G_EXCEPTION_HALT_VALIDATION;
401 END IF;
402 EXCEPTION
403 WHEN G_EXCEPTION_HALT_VALIDATION THEN
404 null;
405 WHEN OTHERS THEN
406 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
407 ,p_msg_name => G_UNEXPECTED_ERROR
408 ,p_token1 => G_SQLCODE_TOKEN
409 ,p_token1_value => SQLCODE
410 ,p_token2 => G_SQLERRM_TOKEN
411 ,p_token2_value => SQLERRM);
412 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
413 END validate_param_type_code;
414
415 ---------------------------------------------------------------------------
416 -- FUNCTION Validate_Attributes
417 ---------------------------------------------------------------------------
418 ---------------------------------------------
419 -- Validate_Attributes for:OKL_XMLP_PARAMS --
420 ---------------------------------------------
421 FUNCTION Validate_Attributes (
422 p_xmp_rec IN xmp_rec_type
423 ) RETURN VARCHAR2 IS
424 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
425 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
426 BEGIN
427 -----------------------------
428 -- Column Level Validation --
429 -----------------------------
430 -- ***
431 -- id
432 -- ***
433 validate_id(x_return_status, p_xmp_rec.id);
434 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
435 l_return_status := x_return_status;
436 RAISE G_EXCEPTION_HALT_VALIDATION;
437 END IF;
438
439 -- ***
440 -- batch_id
441 -- ***
442 validate_batch_id(x_return_status, p_xmp_rec.batch_id);
443 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
444 l_return_status := x_return_status;
445 RAISE G_EXCEPTION_HALT_VALIDATION;
446 END IF;
447
448 -- ***
449 -- object_version_number
450 -- ***
451 validate_object_version_number(x_return_status, p_xmp_rec.object_version_number);
452 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
453 l_return_status := x_return_status;
454 RAISE G_EXCEPTION_HALT_VALIDATION;
455 END IF;
456
457 -- ***
458 -- param_type_code
459 -- ***
460 validate_param_type_code(x_return_status, p_xmp_rec.param_type_code);
461 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
462 l_return_status := x_return_status;
463 RAISE G_EXCEPTION_HALT_VALIDATION;
464 END IF;
465
466 RETURN(l_return_status);
467 EXCEPTION
468 WHEN G_EXCEPTION_HALT_VALIDATION THEN
469 RETURN(l_return_status);
470 WHEN OTHERS THEN
471 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
472 ,p_msg_name => G_UNEXPECTED_ERROR
473 ,p_token1 => G_SQLCODE_TOKEN
474 ,p_token1_value => SQLCODE
475 ,p_token2 => G_SQLERRM_TOKEN
476 ,p_token2_value => SQLERRM);
477 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
478 RETURN(l_return_status);
479 END Validate_Attributes;
480 ---------------------------------------------------------------------------
481 -- PROCEDURE Validate_Record
482 ---------------------------------------------------------------------------
483 -----------------------------------------
484 -- Validate Record for:OKL_XMLP_PARAMS --
485 -----------------------------------------
486 FUNCTION Validate_Record (
487 p_xmp_rec IN xmp_rec_type,
488 p_db_xmp_rec IN xmp_rec_type
489 ) RETURN VARCHAR2 IS
490 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
491 BEGIN
492 RETURN (l_return_status);
493 END Validate_Record;
494 FUNCTION Validate_Record (
495 p_xmp_rec IN xmp_rec_type
496 ) RETURN VARCHAR2 IS
497 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
498 l_db_xmp_rec xmp_rec_type := get_rec(p_xmp_rec);
499 BEGIN
500 l_return_status := Validate_Record(p_xmp_rec => p_xmp_rec,
501 p_db_xmp_rec => l_db_xmp_rec);
502 RETURN (l_return_status);
503 END Validate_Record;
504
505 ---------------------------------------------------------------------------
506 -- PROCEDURE Migrate
507 ---------------------------------------------------------------------------
508 PROCEDURE migrate (
509 p_from IN xmp_rec_type,
510 p_to IN OUT NOCOPY xmp_rec_type
511 ) IS
512 BEGIN
513 p_to.id := p_from.id;
514 p_to.batch_id := p_from.batch_id;
515 p_to.param_name := p_from.param_name;
516 p_to.object_version_number := p_from.object_version_number;
517 p_to.param_type_code := p_from.param_type_code;
518 p_to.param_value := p_from.param_value;
519 p_to.attribute_category := p_from.attribute_category;
520 p_to.attribute1 := p_from.attribute1;
521 p_to.attribute2 := p_from.attribute2;
522 p_to.attribute3 := p_from.attribute3;
523 p_to.attribute4 := p_from.attribute4;
524 p_to.attribute5 := p_from.attribute5;
525 p_to.attribute6 := p_from.attribute6;
526 p_to.attribute7 := p_from.attribute7;
527 p_to.attribute8 := p_from.attribute8;
528 p_to.attribute9 := p_from.attribute9;
529 p_to.attribute10 := p_from.attribute10;
530 p_to.attribute11 := p_from.attribute11;
531 p_to.attribute12 := p_from.attribute12;
532 p_to.attribute13 := p_from.attribute13;
533 p_to.attribute14 := p_from.attribute14;
534 p_to.attribute15 := p_from.attribute15;
535 p_to.created_by := p_from.created_by;
536 p_to.creation_date := p_from.creation_date;
537 p_to.last_updated_by := p_from.last_updated_by;
538 p_to.last_update_date := p_from.last_update_date;
539 p_to.last_update_login := p_from.last_update_login;
540 END migrate;
541
542 ---------------------------------------------------------------------------
543 -- PROCEDURE validate_row
544 ---------------------------------------------------------------------------
545 --------------------------------------
546 -- validate_row for:OKL_XMLP_PARAMS --
547 --------------------------------------
548 PROCEDURE validate_row(
549 p_api_version IN NUMBER,
550 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
551 x_return_status OUT NOCOPY VARCHAR2,
552 x_msg_count OUT NOCOPY NUMBER,
553 x_msg_data OUT NOCOPY VARCHAR2,
554 p_xmp_rec IN xmp_rec_type) IS
555
556 l_api_version CONSTANT NUMBER := 1;
557 l_api_name CONSTANT VARCHAR2(30) := 'validate_row (rec)';
558 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
559 l_prog_name VARCHAR2(61);
560 l_xmp_rec xmp_rec_type := p_xmp_rec;
561
562 BEGIN
563 l_prog_name := G_PKG_NAME||'.validate_row (rec)';
564 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
565 G_PKG_NAME,
566 p_init_msg_list,
567 l_api_version,
568 p_api_version,
569 '_PVT',
570 x_return_status);
571 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
572 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
573 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
574 RAISE OKL_API.G_EXCEPTION_ERROR;
575 END IF;
576 --- Validate all non-missing attributes (Item Level Validation)
577 l_return_status := Validate_Attributes(l_xmp_rec);
578 --- If any errors happen abort API
579 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
580 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
581 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
582 RAISE OKL_API.G_EXCEPTION_ERROR;
583 END IF;
584 l_return_status := Validate_Record(l_xmp_rec);
585 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
586 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
587 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
588 RAISE OKL_API.G_EXCEPTION_ERROR;
589 END IF;
590 x_return_status := l_return_status;
591 EXCEPTION
592 WHEN OKL_API.G_EXCEPTION_ERROR THEN
593
594 x_return_status := G_RET_STS_ERROR;
595
596 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
597
598 x_return_status := G_RET_STS_UNEXP_ERROR;
599
600 WHEN OTHERS THEN
601
602 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
603 p_msg_name => G_DB_ERROR,
604 p_token1 => G_PROG_NAME_TOKEN,
605 p_token1_value => l_prog_name,
606 p_token2 => G_SQLCODE_TOKEN,
607 p_token2_value => sqlcode,
608 p_token3 => G_SQLERRM_TOKEN,
609 p_token3_value => sqlerrm);
610
611 x_return_status := G_RET_STS_UNEXP_ERROR;
612 END validate_row;
613 -------------------------------------------------
614 -- PL/SQL TBL validate_row for:OKL_XMLP_PARAMS --
615 -------------------------------------------------
616 PROCEDURE validate_row(
617 p_api_version IN NUMBER,
618 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_msg_count OUT NOCOPY NUMBER,
621 x_msg_data OUT NOCOPY VARCHAR2,
622 p_xmp_tbl IN xmp_tbl_type,
623 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
624
625 l_prog_name VARCHAR2(61);
626 l_api_version CONSTANT NUMBER := 1;
627 l_api_name CONSTANT VARCHAR2(30) := 'validate_row (tbl)';
628 i NUMBER := 0;
629 BEGIN
630 l_prog_name := G_PKG_NAME||'.validate_row (tbl)';
631 OKL_API.init_msg_list(p_init_msg_list);
632 -- Make sure PL/SQL table has records in it before passing
633 IF (p_xmp_tbl.COUNT > 0) THEN
634 i := p_xmp_tbl.FIRST;
635 LOOP
636 DECLARE
637 l_error_rec OKL_API.ERROR_REC_TYPE;
638 BEGIN
639 l_error_rec.api_name := l_api_name;
640 l_error_rec.api_package := G_PKG_NAME;
641 l_error_rec.idx := i;
642 validate_row (
643 p_api_version => p_api_version,
644 p_init_msg_list => OKL_API.G_FALSE,
645 x_return_status => l_error_rec.error_type,
646 x_msg_count => l_error_rec.msg_count,
647 x_msg_data => l_error_rec.msg_data,
648 p_xmp_rec => p_xmp_tbl(i));
649 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
650 l_error_rec.sqlcode := SQLCODE;
651 load_error_tbl(l_error_rec, px_error_tbl);
652 ELSE
653 x_msg_count := l_error_rec.msg_count;
654 x_msg_data := l_error_rec.msg_data;
655 END IF;
656 EXCEPTION
657 WHEN OKL_API.G_EXCEPTION_ERROR THEN
658 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
659 l_error_rec.sqlcode := SQLCODE;
660 load_error_tbl(l_error_rec, px_error_tbl);
661 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
662 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
663 l_error_rec.sqlcode := SQLCODE;
664 load_error_tbl(l_error_rec, px_error_tbl);
665 WHEN OTHERS THEN
666 l_error_rec.error_type := 'OTHERS';
667 l_error_rec.sqlcode := SQLCODE;
668 load_error_tbl(l_error_rec, px_error_tbl);
669 END;
670 EXIT WHEN (i = p_xmp_tbl.LAST);
671 i := p_xmp_tbl.NEXT(i);
672 END LOOP;
673 END IF;
674 -- Loop through the error_tbl to find the error with the highest severity
675 -- and return it.
676 x_return_status := find_highest_exception(px_error_tbl);
677 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
678 EXCEPTION
679 WHEN OKL_API.G_EXCEPTION_ERROR THEN
680
681 x_return_status := G_RET_STS_ERROR;
682
683 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
684
685 x_return_status := G_RET_STS_UNEXP_ERROR;
686
687 WHEN OTHERS THEN
688
689 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
690 p_msg_name => G_DB_ERROR,
691 p_token1 => G_PROG_NAME_TOKEN,
692 p_token1_value => l_prog_name,
693 p_token2 => G_SQLCODE_TOKEN,
694 p_token2_value => sqlcode,
695 p_token3 => G_SQLERRM_TOKEN,
696 p_token3_value => sqlerrm);
697
698 x_return_status := G_RET_STS_UNEXP_ERROR;
699 END validate_row;
700
701 -------------------------------------------------
702 -- PL/SQL TBL validate_row for:OKL_XMLP_PARAMS --
703 -------------------------------------------------
704 PROCEDURE validate_row(
705 p_api_version IN NUMBER,
706 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_count OUT NOCOPY NUMBER,
709 x_msg_data OUT NOCOPY VARCHAR2,
710 p_xmp_tbl IN xmp_tbl_type) IS
711
712 l_prog_name VARCHAR2(61);
713 l_api_version CONSTANT NUMBER := 1;
714 l_api_name CONSTANT VARCHAR2(30) := 'validate_row (tbl)';
715 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
716 l_error_tbl OKL_API.ERROR_TBL_TYPE;
717 BEGIN
718 l_prog_name := G_PKG_NAME||'.validate_row (tbl)';
719 OKL_API.init_msg_list(p_init_msg_list);
720 -- Make sure PL/SQL table has records in it before passing
721 IF (p_xmp_tbl.COUNT > 0) THEN
722 validate_row (
723 p_api_version => p_api_version,
724 p_init_msg_list => OKL_API.G_FALSE,
725 x_return_status => x_return_status,
726 x_msg_count => x_msg_count,
727 x_msg_data => x_msg_data,
728 p_xmp_tbl => p_xmp_tbl,
729 px_error_tbl => l_error_tbl);
730 END IF;
731 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
732 EXCEPTION
733 WHEN OKL_API.G_EXCEPTION_ERROR THEN
734
735 x_return_status := G_RET_STS_ERROR;
736
737 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
738
739 x_return_status := G_RET_STS_UNEXP_ERROR;
740
741 WHEN OTHERS THEN
742
743 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
744 p_msg_name => G_DB_ERROR,
745 p_token1 => G_PROG_NAME_TOKEN,
746 p_token1_value => l_prog_name,
747 p_token2 => G_SQLCODE_TOKEN,
748 p_token2_value => sqlcode,
749 p_token3 => G_SQLERRM_TOKEN,
750 p_token3_value => sqlerrm);
751
752 x_return_status := G_RET_STS_UNEXP_ERROR;
753 END validate_row;
754
755 ---------------------------------------------------------------------------
756 -- PROCEDURE insert_row
757 ---------------------------------------------------------------------------
758 ------------------------------------
759 -- insert_row for:OKL_XMLP_PARAMS --
760 ------------------------------------
761 PROCEDURE insert_row(
762 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
763 x_return_status OUT NOCOPY VARCHAR2,
764 x_msg_count OUT NOCOPY NUMBER,
765 x_msg_data OUT NOCOPY VARCHAR2,
766 p_xmp_rec IN xmp_rec_type,
767 x_xmp_rec OUT NOCOPY xmp_rec_type) IS
768
769 l_prog_name VARCHAR2(61);
770 l_api_version CONSTANT NUMBER := 1;
771 l_api_name CONSTANT VARCHAR2(30) := 'insert_row (rec)';
772 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
773 l_xmp_rec xmp_rec_type := p_xmp_rec;
774 l_def_xmp_rec xmp_rec_type;
775 ----------------------------------------
776 -- Set_Attributes for:OKL_XMLP_PARAMS --
777 ----------------------------------------
778 FUNCTION Set_Attributes (
779 p_xmp_rec IN xmp_rec_type,
780 x_xmp_rec OUT NOCOPY xmp_rec_type
781 ) RETURN VARCHAR2 IS
782 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
783 BEGIN
784 x_xmp_rec := p_xmp_rec;
785 RETURN(l_return_status);
786 END Set_Attributes;
787 BEGIN
788 l_prog_name := G_PKG_NAME||'.insert_row (Rec)';
789 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
790 p_init_msg_list,
791 '_PVT',
792 x_return_status);
793 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
794 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
795 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
796 RAISE OKL_API.G_EXCEPTION_ERROR;
797 END IF;
798
799 --- Setting item atributes
800 l_return_status := Set_Attributes(
801 p_xmp_rec, -- IN
802 l_xmp_rec); -- OUT
803 --- If any errors happen abort API
804 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
806 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
807 RAISE OKL_API.G_EXCEPTION_ERROR;
808 END IF;
809
810 INSERT INTO OKL_XMLP_PARAMS(
811 id,
812 batch_id,
813 param_name,
814 object_version_number,
815 param_type_code,
816 param_value,
817 attribute_category,
818 attribute1,
819 attribute2,
820 attribute3,
821 attribute4,
822 attribute5,
823 attribute6,
824 attribute7,
825 attribute8,
826 attribute9,
827 attribute10,
828 attribute11,
829 attribute12,
830 attribute13,
831 attribute14,
832 attribute15,
833 created_by,
834 creation_date,
835 last_updated_by,
836 last_update_date,
837 last_update_login)
838 VALUES (
839 l_xmp_rec.id,
840 l_xmp_rec.batch_id,
841 l_xmp_rec.param_name,
842 l_xmp_rec.object_version_number,
843 l_xmp_rec.param_type_code,
844 l_xmp_rec.param_value,
845 l_xmp_rec.attribute_category,
846 l_xmp_rec.attribute1,
847 l_xmp_rec.attribute2,
848 l_xmp_rec.attribute3,
849 l_xmp_rec.attribute4,
850 l_xmp_rec.attribute5,
851 l_xmp_rec.attribute6,
852 l_xmp_rec.attribute7,
853 l_xmp_rec.attribute8,
854 l_xmp_rec.attribute9,
855 l_xmp_rec.attribute10,
856 l_xmp_rec.attribute11,
857 l_xmp_rec.attribute12,
858 l_xmp_rec.attribute13,
859 l_xmp_rec.attribute14,
860 l_xmp_rec.attribute15,
861 G_USER_ID,
862 SYSDATE,
863 G_USER_ID,
864 SYSDATE,
865 G_LOGIN_ID);
866 -- Set OUT values
867 x_xmp_rec := l_xmp_rec;
868 x_return_status := l_return_status;
869 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
870 EXCEPTION
871 WHEN OKL_API.G_EXCEPTION_ERROR THEN
872
873 x_return_status := G_RET_STS_ERROR;
874
875 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
876
877 x_return_status := G_RET_STS_UNEXP_ERROR;
878
879 WHEN OTHERS THEN
880
881 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
882 p_msg_name => G_DB_ERROR,
883 p_token1 => G_PROG_NAME_TOKEN,
884 p_token1_value => l_prog_name,
885 p_token2 => G_SQLCODE_TOKEN,
886 p_token2_value => sqlcode,
887 p_token3 => G_SQLERRM_TOKEN,
888 p_token3_value => sqlerrm);
889
890 x_return_status := G_RET_STS_UNEXP_ERROR;
891 END insert_row;
892
893 -----------------------------------------------------
894 -- PL/SQL TBL insert_row for:OKL_XMLP_PARAMS_TBL --
895 -----------------------------------------------------
896 PROCEDURE insert_row(
897 p_api_version IN NUMBER,
898 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
899 x_return_status OUT NOCOPY VARCHAR2,
900 x_msg_count OUT NOCOPY NUMBER,
901 x_msg_data OUT NOCOPY VARCHAR2,
902 p_xmp_rec IN xmp_rec_type,
903 x_xmp_rec OUT NOCOPY xmp_rec_type) IS
904
905 l_api_version CONSTANT NUMBER := 1;
906 l_api_name CONSTANT VARCHAR2(30) := 'insert_row (REC)';
907 l_prog_name VARCHAR2(61);
908 l_xmp_rec xmp_rec_type := p_xmp_rec;
909 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
910 BEGIN
911 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
912 OKL_API.init_msg_list(p_init_msg_list);
913
914 -- Generate the id
915 SELECT okl_xmp_seq.nextval INTO l_xmp_rec.id FROM DUAL;
916
917 IF l_xmp_rec.batch_id IS NULL THEN
918 -- Generate the batch id
919 SELECT okl_xmp_batch_seq.nextval INTO l_xmp_rec.batch_id FROM DUAL;
920 END IF;
921
922 l_xmp_rec.object_version_number := 1;
923 l_xmp_rec := null_out_defaults(l_xmp_rec);
924
925 validate_row (
926 p_api_version => p_api_version,
927 p_init_msg_list => OKL_API.G_FALSE,
928 x_return_status => l_return_status,
929 x_msg_count => x_msg_count,
930 x_msg_data => x_msg_data,
931 p_xmp_rec => l_xmp_rec);
932 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
933 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
934 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
935 RAISE OKL_API.G_EXCEPTION_ERROR;
936 END IF;
937
938 insert_row (
939 p_init_msg_list => OKL_API.G_FALSE,
940 x_return_status => l_return_status,
941 x_msg_count => x_msg_count,
942 x_msg_data => x_msg_data,
943 p_xmp_rec => l_xmp_rec,
944 x_xmp_rec => x_xmp_rec);
945
946 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
947 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
948 ELSIF l_return_status = G_RET_STS_ERROR THEN
949 RAISE OKL_API.G_EXCEPTION_ERROR;
950 END IF;
951 x_return_status := l_return_status;
952 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
953 EXCEPTION
954 WHEN OKL_API.G_EXCEPTION_ERROR THEN
955
956 x_return_status := G_RET_STS_ERROR;
957
958 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
959
960 x_return_status := G_RET_STS_UNEXP_ERROR;
961
962 WHEN OTHERS THEN
963
964 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
965 p_msg_name => G_DB_ERROR,
966 p_token1 => G_PROG_NAME_TOKEN,
967 p_token1_value => l_prog_name,
968 p_token2 => G_SQLCODE_TOKEN,
969 p_token2_value => sqlcode,
970 p_token3 => G_SQLERRM_TOKEN,
971 p_token3_value => sqlerrm);
972
973 x_return_status := G_RET_STS_UNEXP_ERROR;
974 END insert_row;
975
976 -----------------------------------------------------
977 -- PL/SQL TBL insert_row for:OKL_XMLP_PARAMS_TBL --
978 -----------------------------------------------------
979 PROCEDURE insert_row(
980 p_api_version IN NUMBER,
981 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
982 x_return_status OUT NOCOPY VARCHAR2,
983 x_msg_count OUT NOCOPY NUMBER,
984 x_msg_data OUT NOCOPY VARCHAR2,
985 p_xmp_tbl IN xmp_tbl_type,
986 x_xmp_tbl OUT NOCOPY xmp_tbl_type) IS
987
988 l_api_version CONSTANT NUMBER := 1;
989 l_prog_name VARCHAR2(61);
990 l_api_name CONSTANT VARCHAR2(30) := 'insert_row (TBL)';
991 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
992 i NUMBER := 0;
993 l_batch_id NUMBER := 0;
994 l_xmp_rec xmp_rec_type;
995 BEGIN
996 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
997 OKL_API.init_msg_list(p_init_msg_list);
998 -- Generate the batch id
999 SELECT okl_xmp_batch_seq.nextval INTO l_batch_id FROM DUAL;
1000
1001 -- Make sure PL/SQL table has records in it before passing
1002 IF (p_xmp_tbl.COUNT > 0) THEN
1003 i := p_xmp_tbl.FIRST;
1004 LOOP
1005 l_xmp_rec := p_xmp_tbl(i);
1006 l_xmp_rec.batch_id := l_batch_id;
1007 l_xmp_rec.object_version_number := 1;
1008 insert_row (
1009 p_api_version => p_api_version,
1010 p_init_msg_list => OKL_API.G_FALSE,
1011 x_return_status => l_return_status,
1012 x_msg_count => x_msg_count,
1013 x_msg_data => x_msg_data,
1014 p_xmp_rec => l_xmp_rec,
1015 x_xmp_rec => x_xmp_tbl(i));
1016
1017 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1018 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1019 ELSIF l_return_status = G_RET_STS_ERROR THEN
1020 RAISE OKL_API.G_EXCEPTION_ERROR;
1021 END IF;
1022
1023 EXIT WHEN (i = p_xmp_tbl.LAST);
1024 i := p_xmp_tbl.NEXT(i);
1025 END LOOP;
1026 END IF;
1027 x_return_status := l_return_status;
1028 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1029 EXCEPTION
1030 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1031
1032 x_return_status := G_RET_STS_ERROR;
1033
1034 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1035
1036 x_return_status := G_RET_STS_UNEXP_ERROR;
1037
1038 WHEN OTHERS THEN
1039
1040 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1041 p_msg_name => G_DB_ERROR,
1042 p_token1 => G_PROG_NAME_TOKEN,
1043 p_token1_value => l_prog_name,
1044 p_token2 => G_SQLCODE_TOKEN,
1045 p_token2_value => sqlcode,
1046 p_token3 => G_SQLERRM_TOKEN,
1047 p_token3_value => sqlerrm);
1048
1049 x_return_status := G_RET_STS_UNEXP_ERROR;
1050 END insert_row;
1051
1052 ---------------------------------------------------------------------------
1053 -- PROCEDURE lock_row
1054 ---------------------------------------------------------------------------
1055 ----------------------------------
1056 -- lock_row for:OKL_XMLP_PARAMS --
1057 ----------------------------------
1058 PROCEDURE lock_row(
1059 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1060 x_return_status OUT NOCOPY VARCHAR2,
1061 x_msg_count OUT NOCOPY NUMBER,
1062 x_msg_data OUT NOCOPY VARCHAR2,
1063 p_xmp_rec IN xmp_rec_type) IS
1064
1065 E_Resource_Busy EXCEPTION;
1066 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1067 CURSOR lock_csr (p_xmp_rec IN xmp_rec_type) IS
1068 SELECT OBJECT_VERSION_NUMBER
1069 FROM OKL_XMLP_PARAMS
1070 WHERE ID = p_xmp_rec.id
1071 AND OBJECT_VERSION_NUMBER = p_xmp_rec.object_version_number
1072 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1073
1074 CURSOR lchk_csr (p_xmp_rec IN xmp_rec_type) IS
1075 SELECT OBJECT_VERSION_NUMBER
1076 FROM OKL_XMLP_PARAMS
1077 WHERE ID = p_xmp_rec.id;
1078 l_api_version CONSTANT NUMBER := 1;
1079 l_api_name CONSTANT VARCHAR2(30) := 'lock_row (rec)';
1080 l_prog_name VARCHAR2(61);
1081 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1082 l_object_version_number OKL_XMLP_PARAMS.OBJECT_VERSION_NUMBER%TYPE;
1083 lc_object_version_number OKL_XMLP_PARAMS.OBJECT_VERSION_NUMBER%TYPE;
1084 l_row_notfound BOOLEAN := FALSE;
1085 lc_row_notfound BOOLEAN := FALSE;
1086 BEGIN
1087 l_prog_name := G_PKG_NAME||'.lock_row (rec)';
1088 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1089 p_init_msg_list,
1090 '_PVT',
1091 x_return_status);
1092 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1093 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1094 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1095 RAISE OKL_API.G_EXCEPTION_ERROR;
1096 END IF;
1097 BEGIN
1098 OPEN lock_csr(p_xmp_rec);
1099 FETCH lock_csr INTO l_object_version_number;
1100 l_row_notfound := lock_csr%NOTFOUND;
1101 CLOSE lock_csr;
1102 EXCEPTION
1103 WHEN E_Resource_Busy THEN
1104 IF (lock_csr%ISOPEN) THEN
1105 CLOSE lock_csr;
1106 END IF;
1107 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1108 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1109 END;
1110
1111 IF ( l_row_notfound ) THEN
1112 OPEN lchk_csr(p_xmp_rec);
1113 FETCH lchk_csr INTO lc_object_version_number;
1114 lc_row_notfound := lchk_csr%NOTFOUND;
1115 CLOSE lchk_csr;
1116 END IF;
1117 IF (lc_row_notfound) THEN
1118 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1119 RAISE OKL_API.G_EXCEPTION_ERROR;
1120 ELSIF lc_object_version_number > p_xmp_rec.object_version_number THEN
1121 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1122 RAISE OKL_API.G_EXCEPTION_ERROR;
1123 ELSIF lc_object_version_number <> p_xmp_rec.object_version_number THEN
1124 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1125 RAISE OKL_API.G_EXCEPTION_ERROR;
1126 ELSIF lc_object_version_number = -1 THEN
1127 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1128 RAISE OKL_API.G_EXCEPTION_ERROR;
1129 END IF;
1130 x_return_status := l_return_status;
1131 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1132 EXCEPTION
1133 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1134
1135 x_return_status := G_RET_STS_ERROR;
1136
1137 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1138
1139 x_return_status := G_RET_STS_UNEXP_ERROR;
1140
1141 WHEN OTHERS THEN
1142
1143 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1144 p_msg_name => G_DB_ERROR,
1145 p_token1 => G_PROG_NAME_TOKEN,
1146 p_token1_value => l_prog_name,
1147 p_token2 => G_SQLCODE_TOKEN,
1148 p_token2_value => sqlcode,
1149 p_token3 => G_SQLERRM_TOKEN,
1150 p_token3_value => sqlerrm);
1151
1152 x_return_status := G_RET_STS_UNEXP_ERROR;
1153 END lock_row;
1154 -----------------------------------
1155 -- lock_row for: OKL_XMLP_PARAMS --
1156 -----------------------------------
1157 PROCEDURE lock_row(
1158 p_api_version IN NUMBER,
1159 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1160 x_return_status OUT NOCOPY VARCHAR2,
1161 x_msg_count OUT NOCOPY NUMBER,
1162 x_msg_data OUT NOCOPY VARCHAR2,
1163 p_xmp_rec IN xmp_rec_type) IS
1164
1165 l_api_version CONSTANT NUMBER := 1;
1166 l_api_name CONSTANT VARCHAR2(30) := 'lock_row (rec)';
1167 l_prog_name VARCHAR2(61);
1168 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1169 l_xmp_rec xmp_rec_type;
1170 BEGIN
1171 l_prog_name := G_PKG_NAME||'.lock_row (rec)';
1172 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1173 G_PKG_NAME,
1174 p_init_msg_list,
1175 l_api_version,
1176 p_api_version,
1177 '_PVT',
1178 x_return_status);
1179 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1180 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1181 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1182 RAISE OKL_API.G_EXCEPTION_ERROR;
1183 END IF;
1184 -----------------------------------------
1185 -- Move VIEW record to "Child" records --
1186 -----------------------------------------
1187 migrate(p_xmp_rec, l_xmp_rec);
1188 ---------------------------------------------
1189 -- Call the LOCK_ROW for each child record --
1190 ---------------------------------------------
1191 lock_row(
1192 p_init_msg_list,
1193 l_return_status,
1194 x_msg_count,
1195 x_msg_data,
1196 l_xmp_rec
1197 );
1198 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1200 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1201 RAISE OKL_API.G_EXCEPTION_ERROR;
1202 END IF;
1203 x_return_status := l_return_status;
1204 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1205 EXCEPTION
1206 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1207
1208 x_return_status := G_RET_STS_ERROR;
1209
1210 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1211
1212 x_return_status := G_RET_STS_UNEXP_ERROR;
1213
1214 WHEN OTHERS THEN
1215
1216 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1217 p_msg_name => G_DB_ERROR,
1218 p_token1 => G_PROG_NAME_TOKEN,
1219 p_token1_value => l_prog_name,
1220 p_token2 => G_SQLCODE_TOKEN,
1221 p_token2_value => sqlcode,
1222 p_token3 => G_SQLERRM_TOKEN,
1223 p_token3_value => sqlerrm);
1224
1225 x_return_status := G_RET_STS_UNEXP_ERROR;
1226 END lock_row;
1227 -------------------------------------
1228 -- PL/SQL TBL lock_row for:XMP_TBL --
1229 -------------------------------------
1230 PROCEDURE lock_row(
1231 p_api_version IN NUMBER,
1232 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1233 x_return_status OUT NOCOPY VARCHAR2,
1234 x_msg_count OUT NOCOPY NUMBER,
1235 x_msg_data OUT NOCOPY VARCHAR2,
1236 p_xmp_tbl IN xmp_tbl_type,
1237 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1238
1239 l_api_version CONSTANT NUMBER := 1;
1240 l_api_name CONSTANT VARCHAR2(30) := 'lock_row (tbl)';
1241 l_prog_name VARCHAR2(61);
1242 i NUMBER := 0;
1243 BEGIN
1244 l_prog_name := G_PKG_NAME||'.lock_row (tbl)';
1245 OKL_API.init_msg_list(p_init_msg_list);
1246 -- Make sure PL/SQL table has recrods in it before passing
1247 IF (p_xmp_tbl.COUNT > 0) THEN
1248 i := p_xmp_tbl.FIRST;
1249 LOOP
1250 DECLARE
1251 l_error_rec OKL_API.ERROR_REC_TYPE;
1252 BEGIN
1253 l_error_rec.api_name := l_api_name;
1254 l_error_rec.api_package := G_PKG_NAME;
1255 l_error_rec.idx := i;
1256 lock_row(
1257 p_api_version => p_api_version,
1258 p_init_msg_list => OKL_API.G_FALSE,
1259 x_return_status => l_error_rec.error_type,
1260 x_msg_count => l_error_rec.msg_count,
1261 x_msg_data => l_error_rec.msg_data,
1262 p_xmp_rec => p_xmp_tbl(i));
1263 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1264 l_error_rec.sqlcode := SQLCODE;
1265 load_error_tbl(l_error_rec, px_error_tbl);
1266 ELSE
1267 x_msg_count := l_error_rec.msg_count;
1268 x_msg_data := l_error_rec.msg_data;
1269 END IF;
1270 EXCEPTION
1271 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1272 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1273 l_error_rec.sqlcode := SQLCODE;
1274 load_error_tbl(l_error_rec, px_error_tbl);
1275 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1276 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1277 l_error_rec.sqlcode := SQLCODE;
1278 load_error_tbl(l_error_rec, px_error_tbl);
1279 WHEN OTHERS THEN
1280 l_error_rec.error_type := 'OTHERS';
1281 l_error_rec.sqlcode := SQLCODE;
1282 load_error_tbl(l_error_rec, px_error_tbl);
1283 END;
1284 EXIT WHEN (i = p_xmp_tbl.LAST);
1285 i := p_xmp_tbl.NEXT(i);
1286 END LOOP;
1287 END IF;
1288 -- Loop through the error_tbl to find the error with the highest severity
1289 -- and return it.
1290 x_return_status := find_highest_exception(px_error_tbl);
1291 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1292 EXCEPTION
1293 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1294
1295 x_return_status := G_RET_STS_ERROR;
1296
1297 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1298
1299 x_return_status := G_RET_STS_UNEXP_ERROR;
1300
1301 WHEN OTHERS THEN
1302
1303 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1304 p_msg_name => G_DB_ERROR,
1305 p_token1 => G_PROG_NAME_TOKEN,
1306 p_token1_value => l_prog_name,
1307 p_token2 => G_SQLCODE_TOKEN,
1308 p_token2_value => sqlcode,
1309 p_token3 => G_SQLERRM_TOKEN,
1310 p_token3_value => sqlerrm);
1311
1312 x_return_status := G_RET_STS_UNEXP_ERROR;
1313 END lock_row;
1314 -------------------------------------
1315 -- PL/SQL TBL lock_row for:XMP_TBL --
1316 -------------------------------------
1317 PROCEDURE lock_row(
1318 p_api_version IN NUMBER,
1319 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1320 x_return_status OUT NOCOPY VARCHAR2,
1321 x_msg_count OUT NOCOPY NUMBER,
1322 x_msg_data OUT NOCOPY VARCHAR2,
1323 p_xmp_tbl IN xmp_tbl_type) IS
1324
1325 l_api_version CONSTANT NUMBER := 1;
1326 l_api_name CONSTANT VARCHAR2(30) := 'lock_row (tbl)';
1327 l_prog_name VARCHAR2(61);
1328 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1329 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1330 BEGIN
1331 l_prog_name := G_PKG_NAME||'.lock_row (tbl)';
1332 OKL_API.init_msg_list(p_init_msg_list);
1333 -- Make sure PL/SQL table has recrods in it before passing
1334 IF (p_xmp_tbl.COUNT > 0) THEN
1335 lock_row(
1336 p_api_version => p_api_version,
1337 p_init_msg_list => OKL_API.G_FALSE,
1338 x_return_status => x_return_status,
1339 x_msg_count => x_msg_count,
1340 x_msg_data => x_msg_data,
1341 p_xmp_tbl => p_xmp_tbl,
1342 px_error_tbl => l_error_tbl);
1343 END IF;
1344 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1345 EXCEPTION
1346 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1347
1348 x_return_status := G_RET_STS_ERROR;
1349
1350 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1351
1352 x_return_status := G_RET_STS_UNEXP_ERROR;
1353
1354 WHEN OTHERS THEN
1355
1356 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1357 p_msg_name => G_DB_ERROR,
1358 p_token1 => G_PROG_NAME_TOKEN,
1359 p_token1_value => l_prog_name,
1360 p_token2 => G_SQLCODE_TOKEN,
1361 p_token2_value => sqlcode,
1362 p_token3 => G_SQLERRM_TOKEN,
1363 p_token3_value => sqlerrm);
1364
1365 x_return_status := G_RET_STS_UNEXP_ERROR;
1366 END lock_row;
1367 ---------------------------------------------------------------------------
1368 -- PROCEDURE update_row
1369 ---------------------------------------------------------------------------
1370 ------------------------------------
1371 -- update_row for:OKL_XMLP_PARAMS --
1372 ------------------------------------
1373 PROCEDURE update_row(
1374 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1375 x_return_status OUT NOCOPY VARCHAR2,
1376 x_msg_count OUT NOCOPY NUMBER,
1377 x_msg_data OUT NOCOPY VARCHAR2,
1378 p_xmp_rec IN xmp_rec_type,
1379 x_xmp_rec OUT NOCOPY xmp_rec_type) IS
1380
1381 l_api_version CONSTANT NUMBER := 1;
1382 l_api_name CONSTANT VARCHAR2(30) := 'update_row (rec)';
1383 l_prog_name VARCHAR2(61);
1384 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1385 l_xmp_rec xmp_rec_type := p_xmp_rec;
1386 l_def_xmp_rec xmp_rec_type;
1387 l_row_notfound BOOLEAN := TRUE;
1388 ----------------------------------
1389 -- FUNCTION populate_new_record --
1390 ----------------------------------
1391 FUNCTION populate_new_record (
1392 p_xmp_rec IN xmp_rec_type,
1393 x_xmp_rec OUT NOCOPY xmp_rec_type
1394 ) RETURN VARCHAR2 IS
1395 l_xmp_rec xmp_rec_type;
1396 l_row_notfound BOOLEAN := TRUE;
1397 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1398 BEGIN
1399 x_xmp_rec := p_xmp_rec;
1400 -- Get current database values
1401 l_xmp_rec := get_rec(p_xmp_rec, l_return_status);
1402 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1403 IF (x_xmp_rec.id IS NULL)
1404 THEN
1405 x_xmp_rec.id := l_xmp_rec.id;
1406 END IF;
1407 IF (x_xmp_rec.batch_id IS NULL)
1408 THEN
1409 x_xmp_rec.batch_id := l_xmp_rec.batch_id;
1410 END IF;
1411 IF x_xmp_rec.object_version_number IS NULL THEN
1412 x_xmp_rec.object_version_number := l_xmp_rec.object_version_number;
1413 END IF;
1414 IF (x_xmp_rec.param_name IS NULL)
1415 THEN
1416 x_xmp_rec.param_name := l_xmp_rec.param_name;
1417 END IF;
1418 IF (x_xmp_rec.param_value IS NULL)
1419 THEN
1420 x_xmp_rec.param_value := l_xmp_rec.param_value;
1421 END IF;
1422 IF (x_xmp_rec.param_type_code IS NULL)
1423 THEN
1424 x_xmp_rec.param_type_code := l_xmp_rec.param_type_code;
1425 END IF;
1426 IF (x_xmp_rec.attribute_category IS NULL)
1427 THEN
1428 x_xmp_rec.attribute_category := l_xmp_rec.attribute_category;
1429 END IF;
1430 IF (x_xmp_rec.attribute1 IS NULL)
1431 THEN
1432 x_xmp_rec.attribute1 := l_xmp_rec.attribute1;
1433 END IF;
1434 IF (x_xmp_rec.attribute2 IS NULL)
1435 THEN
1436 x_xmp_rec.attribute2 := l_xmp_rec.attribute2;
1437 END IF;
1438 IF (x_xmp_rec.attribute3 IS NULL)
1439 THEN
1440 x_xmp_rec.attribute3 := l_xmp_rec.attribute3;
1441 END IF;
1442 IF (x_xmp_rec.attribute4 IS NULL)
1443 THEN
1444 x_xmp_rec.attribute4 := l_xmp_rec.attribute4;
1445 END IF;
1446 IF (x_xmp_rec.attribute5 IS NULL)
1447 THEN
1448 x_xmp_rec.attribute5 := l_xmp_rec.attribute5;
1449 END IF;
1450 IF (x_xmp_rec.attribute6 IS NULL)
1451 THEN
1452 x_xmp_rec.attribute6 := l_xmp_rec.attribute6;
1453 END IF;
1454 IF (x_xmp_rec.attribute7 IS NULL)
1455 THEN
1456 x_xmp_rec.attribute7 := l_xmp_rec.attribute7;
1457 END IF;
1458 IF (x_xmp_rec.attribute8 IS NULL)
1459 THEN
1460 x_xmp_rec.attribute8 := l_xmp_rec.attribute8;
1461 END IF;
1462 IF (x_xmp_rec.attribute9 IS NULL)
1463 THEN
1464 x_xmp_rec.attribute9 := l_xmp_rec.attribute9;
1465 END IF;
1466 IF (x_xmp_rec.attribute10 IS NULL)
1467 THEN
1468 x_xmp_rec.attribute10 := l_xmp_rec.attribute10;
1469 END IF;
1470 IF (x_xmp_rec.attribute11 IS NULL)
1471 THEN
1472 x_xmp_rec.attribute11 := l_xmp_rec.attribute11;
1473 END IF;
1474 IF (x_xmp_rec.attribute12 IS NULL)
1475 THEN
1476 x_xmp_rec.attribute12 := l_xmp_rec.attribute12;
1477 END IF;
1478 IF (x_xmp_rec.attribute13 IS NULL)
1479 THEN
1480 x_xmp_rec.attribute13 := l_xmp_rec.attribute13;
1481 END IF;
1482 IF (x_xmp_rec.attribute14 IS NULL)
1483 THEN
1484 x_xmp_rec.attribute14 := l_xmp_rec.attribute14;
1485 END IF;
1486 IF (x_xmp_rec.attribute15 IS NULL)
1487 THEN
1488 x_xmp_rec.attribute15 := l_xmp_rec.attribute15;
1489 END IF;
1490 IF (x_xmp_rec.created_by IS NULL)
1491 THEN
1492 x_xmp_rec.created_by := l_xmp_rec.created_by;
1493 END IF;
1494 IF (x_xmp_rec.creation_date IS NULL)
1495 THEN
1496 x_xmp_rec.creation_date := l_xmp_rec.creation_date;
1497 END IF;
1498 IF (x_xmp_rec.last_updated_by IS NULL)
1499 THEN
1500 x_xmp_rec.last_updated_by := l_xmp_rec.last_updated_by;
1501 END IF;
1502 IF (x_xmp_rec.last_update_date IS NULL)
1503 THEN
1504 x_xmp_rec.last_update_date := l_xmp_rec.last_update_date;
1505 END IF;
1506 IF (x_xmp_rec.last_update_login IS NULL)
1507 THEN
1508 x_xmp_rec.last_update_login := l_xmp_rec.last_update_login;
1509 END IF;
1510 END IF;
1511 RETURN(l_return_status);
1512 END populate_new_record;
1513 ----------------------------------------
1514 -- Set_Attributes for:OKL_XMLP_PARAMS --
1515 ----------------------------------------
1516 FUNCTION Set_Attributes (
1517 p_xmp_rec IN xmp_rec_type,
1518 x_xmp_rec OUT NOCOPY xmp_rec_type
1519 ) RETURN VARCHAR2 IS
1520 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1521 BEGIN
1522 x_xmp_rec := p_xmp_rec;
1523 x_xmp_rec.OBJECT_VERSION_NUMBER := p_xmp_rec.OBJECT_VERSION_NUMBER + 1;
1524 RETURN(l_return_status);
1525 END Set_Attributes;
1526 BEGIN
1527 l_prog_name := G_PKG_NAME||'.update_row (rec)';
1528 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1529 p_init_msg_list,
1530 '_PVT',
1531 x_return_status);
1532 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1533 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1534 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1535 RAISE OKL_API.G_EXCEPTION_ERROR;
1536 END IF;
1537 --- Setting item attributes
1538 l_return_status := Set_Attributes(
1539 p_xmp_rec, -- IN
1540 l_xmp_rec); -- OUT
1541 --- If any errors happen abort API
1542 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1543 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1544 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1545 RAISE OKL_API.G_EXCEPTION_ERROR;
1546 END IF;
1547 l_return_status := populate_new_record(l_xmp_rec, l_def_xmp_rec);
1548 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1549 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1550 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1551 RAISE OKL_API.G_EXCEPTION_ERROR;
1552 END IF;
1553
1554 UPDATE OKL_XMLP_PARAMS
1555 SET BATCH_ID = l_def_xmp_rec.batch_id,
1556 PARAM_NAME = l_def_xmp_rec.param_name,
1557 OBJECT_VERSION_NUMBER = l_def_xmp_rec.object_version_number,
1558 PARAM_TYPE_CODE = l_def_xmp_rec.param_type_code,
1559 PARAM_VALUE = l_def_xmp_rec.param_value,
1560 ATTRIBUTE_CATEGORY = l_def_xmp_rec.attribute_category,
1561 ATTRIBUTE1 = l_def_xmp_rec.attribute1,
1562 ATTRIBUTE2 = l_def_xmp_rec.attribute2,
1563 ATTRIBUTE3 = l_def_xmp_rec.attribute3,
1564 ATTRIBUTE4 = l_def_xmp_rec.attribute4,
1565 ATTRIBUTE5 = l_def_xmp_rec.attribute5,
1566 ATTRIBUTE6 = l_def_xmp_rec.attribute6,
1567 ATTRIBUTE7 = l_def_xmp_rec.attribute7,
1568 ATTRIBUTE8 = l_def_xmp_rec.attribute8,
1569 ATTRIBUTE9 = l_def_xmp_rec.attribute9,
1570 ATTRIBUTE10 = l_def_xmp_rec.attribute10,
1571 ATTRIBUTE11 = l_def_xmp_rec.attribute11,
1572 ATTRIBUTE12 = l_def_xmp_rec.attribute12,
1573 ATTRIBUTE13 = l_def_xmp_rec.attribute13,
1574 ATTRIBUTE14 = l_def_xmp_rec.attribute14,
1575 ATTRIBUTE15 = l_def_xmp_rec.attribute15,
1576 CREATED_BY = l_def_xmp_rec.created_by,
1577 CREATION_DATE = l_def_xmp_rec.creation_date,
1578 LAST_UPDATED_BY = l_def_xmp_rec.last_updated_by,
1579 LAST_UPDATE_DATE = l_def_xmp_rec.last_update_date,
1580 LAST_UPDATE_LOGIN = l_def_xmp_rec.last_update_login
1581 WHERE ID = l_def_xmp_rec.id;
1582
1583 x_xmp_rec := l_xmp_rec;
1584 x_return_status := l_return_status;
1585 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1586 EXCEPTION
1587 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1588
1589 x_return_status := G_RET_STS_ERROR;
1590
1591 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1592
1593 x_return_status := G_RET_STS_UNEXP_ERROR;
1594
1595 WHEN OTHERS THEN
1596
1597 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1598 p_msg_name => G_DB_ERROR,
1599 p_token1 => G_PROG_NAME_TOKEN,
1600 p_token1_value => l_prog_name,
1601 p_token2 => G_SQLCODE_TOKEN,
1602 p_token2_value => sqlcode,
1603 p_token3 => G_SQLERRM_TOKEN,
1604 p_token3_value => sqlerrm);
1605
1606 x_return_status := G_RET_STS_UNEXP_ERROR;
1607 END update_row;
1608 ------------------------------------
1609 -- update_row for:OKL_XMLP_PARAMS --
1610 ------------------------------------
1611 PROCEDURE update_row(
1612 p_api_version IN NUMBER,
1613 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1614 x_return_status OUT NOCOPY VARCHAR2,
1615 x_msg_count OUT NOCOPY NUMBER,
1616 x_msg_data OUT NOCOPY VARCHAR2,
1617 p_xmp_rec IN xmp_rec_type,
1618 x_xmp_rec OUT NOCOPY xmp_rec_type) IS
1619
1620 l_api_version CONSTANT NUMBER := 1;
1621 l_api_name CONSTANT VARCHAR2(30) := 'update_row (rec)';
1622 l_prog_name VARCHAR2(61);
1623 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1624 l_xmp_rec xmp_rec_type := p_xmp_rec;
1625 l_def_xmp_rec xmp_rec_type;
1626 l_db_xmp_rec xmp_rec_type;
1627 lx_xmp_rec xmp_rec_type;
1628 -------------------------------
1629 -- FUNCTION fill_who_columns --
1630 -------------------------------
1631 FUNCTION fill_who_columns (
1632 p_xmp_rec IN xmp_rec_type
1633 ) RETURN xmp_rec_type IS
1634 l_xmp_rec xmp_rec_type := p_xmp_rec;
1635 BEGIN
1636 l_xmp_rec.LAST_UPDATE_DATE := SYSDATE;
1637 l_xmp_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1638 l_xmp_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1639 RETURN(l_xmp_rec);
1640 END fill_who_columns;
1641 ----------------------------------
1642 -- FUNCTION populate_new_record --
1643 ----------------------------------
1644 FUNCTION populate_new_record (
1645 p_xmp_rec IN xmp_rec_type,
1646 x_xmp_rec OUT NOCOPY xmp_rec_type
1647 ) RETURN VARCHAR2 IS
1648 l_row_notfound BOOLEAN := TRUE;
1649 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1650 BEGIN
1651 x_xmp_rec := p_xmp_rec;
1652 -- Get current database values
1653 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1654 -- so it may be verified through LOCK_ROW.
1655 l_db_xmp_rec := get_rec(p_xmp_rec, l_return_status);
1656 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1657 IF (x_xmp_rec.id IS NULL)
1658 THEN
1659 x_xmp_rec.id := l_xmp_rec.id;
1660 END IF;
1661 IF (x_xmp_rec.batch_id IS NULL)
1662 THEN
1663 x_xmp_rec.batch_id := l_xmp_rec.batch_id;
1664 END IF;
1665 IF x_xmp_rec.object_version_number IS NULL THEN
1666 x_xmp_rec.object_version_number := l_xmp_rec.object_version_number;
1667 END IF;
1668 IF (x_xmp_rec.param_name IS NULL)
1669 THEN
1670 x_xmp_rec.param_name := l_xmp_rec.param_name;
1671 END IF;
1672 IF (x_xmp_rec.param_value IS NULL)
1673 THEN
1674 x_xmp_rec.param_value := l_xmp_rec.param_value;
1675 END IF;
1676 IF (x_xmp_rec.param_type_code IS NULL)
1677 THEN
1678 x_xmp_rec.param_type_code := l_xmp_rec.param_type_code;
1679 END IF;
1680 IF (x_xmp_rec.attribute_category IS NULL)
1681 THEN
1682 x_xmp_rec.attribute_category := l_xmp_rec.attribute_category;
1683 END IF;
1684 IF (x_xmp_rec.attribute1 IS NULL)
1685 THEN
1686 x_xmp_rec.attribute1 := l_xmp_rec.attribute1;
1687 END IF;
1688 IF (x_xmp_rec.attribute2 IS NULL)
1689 THEN
1690 x_xmp_rec.attribute2 := l_xmp_rec.attribute2;
1691 END IF;
1692 IF (x_xmp_rec.attribute3 IS NULL)
1693 THEN
1694 x_xmp_rec.attribute3 := l_xmp_rec.attribute3;
1695 END IF;
1696 IF (x_xmp_rec.attribute4 IS NULL)
1697 THEN
1698 x_xmp_rec.attribute4 := l_xmp_rec.attribute4;
1699 END IF;
1700 IF (x_xmp_rec.attribute5 IS NULL)
1701 THEN
1702 x_xmp_rec.attribute5 := l_xmp_rec.attribute5;
1703 END IF;
1704 IF (x_xmp_rec.attribute6 IS NULL)
1705 THEN
1706 x_xmp_rec.attribute6 := l_xmp_rec.attribute6;
1707 END IF;
1708 IF (x_xmp_rec.attribute7 IS NULL)
1709 THEN
1710 x_xmp_rec.attribute7 := l_xmp_rec.attribute7;
1711 END IF;
1712 IF (x_xmp_rec.attribute8 IS NULL)
1713 THEN
1714 x_xmp_rec.attribute8 := l_xmp_rec.attribute8;
1715 END IF;
1716 IF (x_xmp_rec.attribute9 IS NULL)
1717 THEN
1718 x_xmp_rec.attribute9 := l_xmp_rec.attribute9;
1719 END IF;
1720 IF (x_xmp_rec.attribute10 IS NULL)
1721 THEN
1722 x_xmp_rec.attribute10 := l_xmp_rec.attribute10;
1723 END IF;
1724 IF (x_xmp_rec.attribute11 IS NULL)
1725 THEN
1726 x_xmp_rec.attribute11 := l_xmp_rec.attribute11;
1727 END IF;
1728 IF (x_xmp_rec.attribute12 IS NULL)
1729 THEN
1730 x_xmp_rec.attribute12 := l_xmp_rec.attribute12;
1731 END IF;
1732 IF (x_xmp_rec.attribute13 IS NULL)
1733 THEN
1734 x_xmp_rec.attribute13 := l_xmp_rec.attribute13;
1735 END IF;
1736 IF (x_xmp_rec.attribute14 IS NULL)
1737 THEN
1738 x_xmp_rec.attribute14 := l_xmp_rec.attribute14;
1739 END IF;
1740 IF (x_xmp_rec.attribute15 IS NULL)
1741 THEN
1742 x_xmp_rec.attribute15 := l_xmp_rec.attribute15;
1743 END IF;
1744 IF (x_xmp_rec.created_by IS NULL)
1745 THEN
1746 x_xmp_rec.created_by := l_xmp_rec.created_by;
1747 END IF;
1748 IF (x_xmp_rec.creation_date IS NULL)
1749 THEN
1750 x_xmp_rec.creation_date := l_xmp_rec.creation_date;
1751 END IF;
1752 IF (x_xmp_rec.last_updated_by IS NULL)
1753 THEN
1754 x_xmp_rec.last_updated_by := l_xmp_rec.last_updated_by;
1755 END IF;
1756 IF (x_xmp_rec.last_update_date IS NULL)
1757 THEN
1758 x_xmp_rec.last_update_date := l_xmp_rec.last_update_date;
1759 END IF;
1760 IF (x_xmp_rec.last_update_login IS NULL)
1761 THEN
1762 x_xmp_rec.last_update_login := l_xmp_rec.last_update_login;
1763 END IF;
1764 END IF;
1765 RETURN(l_return_status);
1766 END populate_new_record;
1767 ----------------------------------------
1768 -- Set_Attributes for:OKL_XMLP_PARAMS --
1769 ----------------------------------------
1770 FUNCTION Set_Attributes (
1771 p_xmp_rec IN xmp_rec_type,
1772 x_xmp_rec OUT NOCOPY xmp_rec_type
1773 ) RETURN VARCHAR2 IS
1774 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1775 BEGIN
1776 x_xmp_rec := p_xmp_rec;
1777 RETURN(l_return_status);
1778 END Set_Attributes;
1779 BEGIN
1780 l_prog_name := G_PKG_NAME||'.update_row (rec)';
1781 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1782 G_PKG_NAME,
1783 p_init_msg_list,
1784 l_api_version,
1785 p_api_version,
1786 '_PVT',
1787 x_return_status);
1788 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1789 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1790 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1791 RAISE OKL_API.G_EXCEPTION_ERROR;
1792 END IF;
1793 --- Setting item attributes
1794 l_return_status := Set_Attributes(
1795 p_xmp_rec, -- IN
1796 x_xmp_rec); -- OUT
1797 --- If any errors happen abort API
1798 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1799 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1800 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1801 RAISE OKL_API.G_EXCEPTION_ERROR;
1802 END IF;
1803 l_return_status := populate_new_record(l_xmp_rec, l_def_xmp_rec);
1804 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1806 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1807 RAISE OKL_API.G_EXCEPTION_ERROR;
1808 END IF;
1809 l_def_xmp_rec := fill_who_columns(l_def_xmp_rec);
1810 --- Validate all non-missing attributes (Item Level Validation)
1811 l_return_status := Validate_Attributes(l_def_xmp_rec);
1812 --- If any errors happen abort API
1813 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1814 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1815 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1816 RAISE OKL_API.G_EXCEPTION_ERROR;
1817 END IF;
1818 l_return_status := Validate_Record(l_def_xmp_rec, l_db_xmp_rec);
1819 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1820 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1821 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1822 RAISE OKL_API.G_EXCEPTION_ERROR;
1823 END IF;
1824
1825 -- Lock the Record
1826 lock_row(
1827 p_api_version => p_api_version,
1828 p_init_msg_list => p_init_msg_list,
1829 x_return_status => l_return_status,
1830 x_msg_count => x_msg_count,
1831 x_msg_data => x_msg_data,
1832 p_xmp_rec => p_xmp_rec);
1833 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1834 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1835 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1836 RAISE OKL_API.G_EXCEPTION_ERROR;
1837 END IF;
1838
1839 -----------------------------------------
1840 -- Move VIEW record to "Child" records --
1841 -----------------------------------------
1842 migrate(l_def_xmp_rec, l_xmp_rec);
1843 -----------------------------------------------
1844 -- Call the UPDATE_ROW for each child record --
1845 -----------------------------------------------
1846 update_row(
1847 p_init_msg_list,
1848 l_return_status,
1849 x_msg_count,
1850 x_msg_data,
1851 l_xmp_rec,
1852 lx_xmp_rec
1853 );
1854 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1855 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1856 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1857 RAISE OKL_API.G_EXCEPTION_ERROR;
1858 END IF;
1859 migrate(lx_xmp_rec, l_def_xmp_rec);
1860 x_xmp_rec := l_def_xmp_rec;
1861 x_return_status := l_return_status;
1862 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1863 EXCEPTION
1864 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1865
1866 x_return_status := G_RET_STS_ERROR;
1867
1868 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1869
1870 x_return_status := G_RET_STS_UNEXP_ERROR;
1871
1872 WHEN OTHERS THEN
1873
1874 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1875 p_msg_name => G_DB_ERROR,
1876 p_token1 => G_PROG_NAME_TOKEN,
1877 p_token1_value => l_prog_name,
1878 p_token2 => G_SQLCODE_TOKEN,
1879 p_token2_value => sqlcode,
1880 p_token3 => G_SQLERRM_TOKEN,
1881 p_token3_value => sqlerrm);
1882
1883 x_return_status := G_RET_STS_UNEXP_ERROR;
1884 END update_row;
1885 ---------------------------------------
1886 -- PL/SQL TBL update_row for:xmp_tbl --
1887 ---------------------------------------
1888 PROCEDURE update_row(
1889 p_api_version IN NUMBER,
1890 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1891 x_return_status OUT NOCOPY VARCHAR2,
1892 x_msg_count OUT NOCOPY NUMBER,
1893 x_msg_data OUT NOCOPY VARCHAR2,
1894 p_xmp_tbl IN xmp_tbl_type,
1895 x_xmp_tbl OUT NOCOPY xmp_tbl_type,
1896 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1897
1898 l_api_version CONSTANT NUMBER := 1;
1899 l_api_name CONSTANT VARCHAR2(30) := 'update_row (tbl)';
1900 l_prog_name VARCHAR2(61);
1901 i NUMBER := 0;
1902 BEGIN
1903 l_prog_name := G_PKG_NAME||'.update_row (tbl)';
1904 OKL_API.init_msg_list(p_init_msg_list);
1905 -- Make sure PL/SQL table has records in it before passing
1906 IF (p_xmp_tbl.COUNT > 0) THEN
1907 i := p_xmp_tbl.FIRST;
1908 LOOP
1909 DECLARE
1910 l_error_rec OKL_API.ERROR_REC_TYPE;
1911 BEGIN
1912 l_error_rec.api_name := l_api_name;
1913 l_error_rec.api_package := G_PKG_NAME;
1914 l_error_rec.idx := i;
1915 update_row (
1916 p_api_version => p_api_version,
1917 p_init_msg_list => OKL_API.G_FALSE,
1918 x_return_status => l_error_rec.error_type,
1919 x_msg_count => l_error_rec.msg_count,
1920 x_msg_data => l_error_rec.msg_data,
1921 p_xmp_rec => p_xmp_tbl(i),
1922 x_xmp_rec => x_xmp_tbl(i));
1923 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1924 l_error_rec.sqlcode := SQLCODE;
1925 load_error_tbl(l_error_rec, px_error_tbl);
1926 ELSE
1927 x_msg_count := l_error_rec.msg_count;
1928 x_msg_data := l_error_rec.msg_data;
1929 END IF;
1930 EXCEPTION
1931 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1932 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1933 l_error_rec.sqlcode := SQLCODE;
1934 load_error_tbl(l_error_rec, px_error_tbl);
1935 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1936 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1937 l_error_rec.sqlcode := SQLCODE;
1938 load_error_tbl(l_error_rec, px_error_tbl);
1939 WHEN OTHERS THEN
1940 l_error_rec.error_type := 'OTHERS';
1941 l_error_rec.sqlcode := SQLCODE;
1942 load_error_tbl(l_error_rec, px_error_tbl);
1943 END;
1944 EXIT WHEN (i = p_xmp_tbl.LAST);
1945 i := p_xmp_tbl.NEXT(i);
1946 END LOOP;
1947 END IF;
1948 -- Loop through the error_tbl to find the error with the highest severity
1949 -- and return it.
1950 x_return_status := find_highest_exception(px_error_tbl);
1951 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1952 EXCEPTION
1953 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1954
1955 x_return_status := G_RET_STS_ERROR;
1956
1957 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1958
1959 x_return_status := G_RET_STS_UNEXP_ERROR;
1960
1961 WHEN OTHERS THEN
1962
1963 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1964 p_msg_name => G_DB_ERROR,
1965 p_token1 => G_PROG_NAME_TOKEN,
1966 p_token1_value => l_prog_name,
1967 p_token2 => G_SQLCODE_TOKEN,
1968 p_token2_value => sqlcode,
1969 p_token3 => G_SQLERRM_TOKEN,
1970 p_token3_value => sqlerrm);
1971
1972 x_return_status := G_RET_STS_UNEXP_ERROR;
1973 END update_row;
1974
1975 ---------------------------------------
1976 -- PL/SQL TBL update_row for:XMP_TBL --
1977 ---------------------------------------
1978 PROCEDURE update_row(
1979 p_api_version IN NUMBER,
1980 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1981 x_return_status OUT NOCOPY VARCHAR2,
1982 x_msg_count OUT NOCOPY NUMBER,
1983 x_msg_data OUT NOCOPY VARCHAR2,
1984 p_xmp_tbl IN xmp_tbl_type,
1985 x_xmp_tbl OUT NOCOPY xmp_tbl_type) IS
1986
1987 l_api_version CONSTANT NUMBER := 1;
1988 l_api_name CONSTANT VARCHAR2(30) := 'update_row (tbl)';
1989 l_prog_name VARCHAR2(61);
1990 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1991 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1992 i NUMBER := 0;
1993 BEGIN
1994 l_prog_name := G_PKG_NAME||'.update_row (tbl)';
1995 OKL_API.init_msg_list(p_init_msg_list);
1996 -- Make sure PL/SQL table has records in it before passing
1997 IF (p_xmp_tbl.COUNT > 0) THEN
1998 i := p_xmp_tbl.FIRST;
1999 LOOP
2000 update_row (
2001 p_api_version => p_api_version,
2002 p_init_msg_list => OKL_API.G_FALSE,
2003 x_return_status => l_return_status,
2004 x_msg_count => x_msg_count,
2005 x_msg_data => x_msg_data,
2006 p_xmp_rec => p_xmp_tbl(i),
2007 x_xmp_rec => x_xmp_tbl(i));
2008
2009 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2010 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2011 ELSIF l_return_status = G_RET_STS_ERROR THEN
2012 RAISE OKL_API.G_EXCEPTION_ERROR;
2013 END IF;
2014
2015 EXIT WHEN (i = p_xmp_tbl.LAST);
2016 i := p_xmp_tbl.NEXT(i);
2017 END LOOP;
2018 END IF;
2019 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2020 EXCEPTION
2021 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2022
2023 x_return_status := G_RET_STS_ERROR;
2024
2025 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2026
2027 x_return_status := G_RET_STS_UNEXP_ERROR;
2028
2029 WHEN OTHERS THEN
2030
2031 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2032 p_msg_name => G_DB_ERROR,
2033 p_token1 => G_PROG_NAME_TOKEN,
2034 p_token1_value => l_prog_name,
2035 p_token2 => G_SQLCODE_TOKEN,
2036 p_token2_value => sqlcode,
2037 p_token3 => G_SQLERRM_TOKEN,
2038 p_token3_value => sqlerrm);
2039
2040 x_return_status := G_RET_STS_UNEXP_ERROR;
2041 END update_row;
2042
2043 ---------------------------------------------------------------------------
2044 -- PROCEDURE delete_row
2045 ---------------------------------------------------------------------------
2046 ------------------------------------
2047 -- delete_row for:OKL_XMLP_PARAMS --
2048 ------------------------------------
2049 PROCEDURE delete_row(
2050 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2051 x_return_status OUT NOCOPY VARCHAR2,
2052 x_msg_count OUT NOCOPY NUMBER,
2053 x_msg_data OUT NOCOPY VARCHAR2,
2054 p_xmp_rec IN xmp_rec_type) IS
2055
2056 l_api_version CONSTANT NUMBER := 1;
2057 l_api_name CONSTANT VARCHAR2(30) := 'delete_row (rec)';
2058 l_prog_name VARCHAR2(61);
2059 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2060 l_xmp_rec xmp_rec_type := p_xmp_rec;
2061 l_row_notfound BOOLEAN := TRUE;
2062 BEGIN
2063 l_prog_name := G_PKG_NAME||'.delete_row (rec)';
2064 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2065 p_init_msg_list,
2066 '_PVT',
2067 x_return_status);
2068 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2069 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2070 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2071 RAISE OKL_API.G_EXCEPTION_ERROR;
2072 END IF;
2073
2074 DELETE FROM OKL_XMLP_PARAMS
2075 WHERE ID = p_xmp_rec.id;
2076
2077 x_return_status := l_return_status;
2078 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2079 EXCEPTION
2080 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2081
2082 x_return_status := G_RET_STS_ERROR;
2083
2084 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2085
2086 x_return_status := G_RET_STS_UNEXP_ERROR;
2087
2088 WHEN OTHERS THEN
2089
2090 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2091 p_msg_name => G_DB_ERROR,
2092 p_token1 => G_PROG_NAME_TOKEN,
2093 p_token1_value => l_prog_name,
2094 p_token2 => G_SQLCODE_TOKEN,
2095 p_token2_value => sqlcode,
2096 p_token3 => G_SQLERRM_TOKEN,
2097 p_token3_value => sqlerrm);
2098
2099 x_return_status := G_RET_STS_UNEXP_ERROR;
2100 END delete_row;
2101 ------------------------------------
2102 -- delete_row for:OKL_XMLP_PARAMS --
2103 ------------------------------------
2104 PROCEDURE delete_row(
2105 p_api_version IN NUMBER,
2106 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2107 x_return_status OUT NOCOPY VARCHAR2,
2108 x_msg_count OUT NOCOPY NUMBER,
2109 x_msg_data OUT NOCOPY VARCHAR2,
2110 p_xmp_rec IN xmp_rec_type) IS
2111
2112 l_api_version CONSTANT NUMBER := 1;
2113 l_api_name CONSTANT VARCHAR2(30) := 'delete_row (rec)';
2114 l_prog_name VARCHAR2(61);
2115 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2116 l_xmp_rec xmp_rec_type := p_xmp_rec;
2117
2118 BEGIN
2119 l_prog_name := G_PKG_NAME||'.delete_row (rec)';
2120 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2121 G_PKG_NAME,
2122 p_init_msg_list,
2123 l_api_version,
2124 p_api_version,
2125 '_PVT',
2126 x_return_status);
2127 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2128 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2129 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2130 RAISE OKL_API.G_EXCEPTION_ERROR;
2131 END IF;
2132 -----------------------------------------
2133 -- Move VIEW record to "Child" records --
2134 -----------------------------------------
2135 migrate(l_xmp_rec, l_xmp_rec);
2136 -----------------------------------------------
2137 -- Call the DELETE_ROW for each child record --
2138 -----------------------------------------------
2139 delete_row(
2140 p_init_msg_list,
2141 l_return_status,
2142 x_msg_count,
2143 x_msg_data,
2144 l_xmp_rec
2145 );
2146 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2149 RAISE OKL_API.G_EXCEPTION_ERROR;
2150 END IF;
2151 x_return_status := l_return_status;
2152 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2153 EXCEPTION
2154 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2155
2156 x_return_status := G_RET_STS_ERROR;
2157
2158 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2159
2160 x_return_status := G_RET_STS_UNEXP_ERROR;
2161
2162 WHEN OTHERS THEN
2163
2164 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2165 p_msg_name => G_DB_ERROR,
2166 p_token1 => G_PROG_NAME_TOKEN,
2167 p_token1_value => l_prog_name,
2168 p_token2 => G_SQLCODE_TOKEN,
2169 p_token2_value => sqlcode,
2170 p_token3 => G_SQLERRM_TOKEN,
2171 p_token3_value => sqlerrm);
2172
2173 x_return_status := G_RET_STS_UNEXP_ERROR;
2174 END delete_row;
2175 -----------------------------------------------
2176 -- PL/SQL TBL delete_row for:OKL_XMLP_PARAMS --
2177 -----------------------------------------------
2178 PROCEDURE delete_row(
2179 p_api_version IN NUMBER,
2180 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2181 x_return_status OUT NOCOPY VARCHAR2,
2182 x_msg_count OUT NOCOPY NUMBER,
2183 x_msg_data OUT NOCOPY VARCHAR2,
2184 p_xmp_tbl IN xmp_tbl_type,
2185 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2186
2187 l_api_version CONSTANT NUMBER := 1;
2188 l_api_name CONSTANT VARCHAR2(30) := 'delete_row (tbl)';
2189 l_prog_name VARCHAR2(61);
2190 i NUMBER := 0;
2191 BEGIN
2192 l_prog_name := G_PKG_NAME||'.delete_row (tbl)';
2193 OKL_API.init_msg_list(p_init_msg_list);
2194 -- Make sure PL/SQL table has records in it before passing
2195 IF (p_xmp_tbl.COUNT > 0) THEN
2196 i := p_xmp_tbl.FIRST;
2197 LOOP
2198 DECLARE
2199 l_error_rec OKL_API.ERROR_REC_TYPE;
2200 BEGIN
2201 l_error_rec.api_name := l_api_name;
2202 l_error_rec.api_package := G_PKG_NAME;
2203 l_error_rec.idx := i;
2204 delete_row (
2205 p_api_version => p_api_version,
2206 p_init_msg_list => OKL_API.G_FALSE,
2207 x_return_status => l_error_rec.error_type,
2208 x_msg_count => l_error_rec.msg_count,
2209 x_msg_data => l_error_rec.msg_data,
2210 p_xmp_rec => p_xmp_tbl(i));
2211 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2212 l_error_rec.sqlcode := SQLCODE;
2213 load_error_tbl(l_error_rec, px_error_tbl);
2214 ELSE
2215 x_msg_count := l_error_rec.msg_count;
2216 x_msg_data := l_error_rec.msg_data;
2217 END IF;
2218 EXCEPTION
2219 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2220 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2221 l_error_rec.sqlcode := SQLCODE;
2222 load_error_tbl(l_error_rec, px_error_tbl);
2223 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2224 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2225 l_error_rec.sqlcode := SQLCODE;
2226 load_error_tbl(l_error_rec, px_error_tbl);
2227 WHEN OTHERS THEN
2228 l_error_rec.error_type := 'OTHERS';
2229 l_error_rec.sqlcode := SQLCODE;
2230 load_error_tbl(l_error_rec, px_error_tbl);
2231 END;
2232 EXIT WHEN (i = p_xmp_tbl.LAST);
2233 i := p_xmp_tbl.NEXT(i);
2234 END LOOP;
2235 END IF;
2236 -- Loop through the error_tbl to find the error with the highest severity
2237 -- and return it.
2238 x_return_status := find_highest_exception(px_error_tbl);
2239 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2240 EXCEPTION
2241 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2242
2243 x_return_status := G_RET_STS_ERROR;
2244
2245 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2246
2247 x_return_status := G_RET_STS_UNEXP_ERROR;
2248
2249 WHEN OTHERS THEN
2250
2251 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2252 p_msg_name => G_DB_ERROR,
2253 p_token1 => G_PROG_NAME_TOKEN,
2254 p_token1_value => l_prog_name,
2255 p_token2 => G_SQLCODE_TOKEN,
2256 p_token2_value => sqlcode,
2257 p_token3 => G_SQLERRM_TOKEN,
2258 p_token3_value => sqlerrm);
2259
2260 x_return_status := G_RET_STS_UNEXP_ERROR;
2261 END delete_row;
2262
2263 -----------------------------------------------
2264 -- PL/SQL TBL delete_row for:OKL_XMLP_PARAMS --
2265 -----------------------------------------------
2266 PROCEDURE delete_row(
2267 p_api_version IN NUMBER,
2268 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2269 x_return_status OUT NOCOPY VARCHAR2,
2270 x_msg_count OUT NOCOPY NUMBER,
2271 x_msg_data OUT NOCOPY VARCHAR2,
2272 p_xmp_tbl IN xmp_tbl_type) IS
2273
2274 l_api_version CONSTANT NUMBER := 1;
2275 l_api_name CONSTANT VARCHAR2(30) := 'delete_row (tbl)';
2276 l_prog_name VARCHAR2(61);
2277 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2278 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2279 BEGIN
2280 l_prog_name := G_PKG_NAME||'.delete_row (tbl)';
2281 OKL_API.init_msg_list(p_init_msg_list);
2282 -- Make sure PL/SQL table has records in it before passing
2283 IF (p_xmp_tbl.COUNT > 0) THEN
2284 delete_row (
2285 p_api_version => p_api_version,
2286 p_init_msg_list => OKL_API.G_FALSE,
2287 x_return_status => x_return_status,
2288 x_msg_count => x_msg_count,
2289 x_msg_data => x_msg_data,
2290 p_xmp_tbl => p_xmp_tbl,
2291 px_error_tbl => l_error_tbl);
2292 END IF;
2293 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2294 EXCEPTION
2295 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2296
2297 x_return_status := G_RET_STS_ERROR;
2298
2299 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2300
2301 x_return_status := G_RET_STS_UNEXP_ERROR;
2302
2303 WHEN OTHERS THEN
2304
2305 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2306 p_msg_name => G_DB_ERROR,
2307 p_token1 => G_PROG_NAME_TOKEN,
2308 p_token1_value => l_prog_name,
2309 p_token2 => G_SQLCODE_TOKEN,
2310 p_token2_value => sqlcode,
2311 p_token3 => G_SQLERRM_TOKEN,
2312 p_token3_value => sqlerrm);
2313
2314 x_return_status := G_RET_STS_UNEXP_ERROR;
2315 END delete_row;
2316
2317 END OKL_XMP_PVT;