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