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