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