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