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