[Home] [Help]
PACKAGE BODY: APPS.OKC_UBL_PVT
Source
1 PACKAGE BODY OKC_UBL_PVT AS
2 /* $Header: OKCSUBLB.pls 120.0 2005/05/25 18:05:15 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 -- FUNCTION get_rec for: OKC_USER_BINS
39 ---------------------------------------------------------------------------
40 FUNCTION get_rec (
41 p_ubn_rec IN ubn_rec_type,
42 x_no_data_found OUT NOCOPY BOOLEAN
43 ) RETURN ubn_rec_type IS
44 l_ubn_rec ubn_rec_type;
45 BEGIN
46 x_no_data_found := TRUE;
47 RETURN(l_ubn_rec);
48 END get_rec;
49
50 FUNCTION get_rec (
51 p_ubn_rec IN ubn_rec_type
52 ) RETURN ubn_rec_type IS
53 l_row_notfound BOOLEAN := TRUE;
54 BEGIN
55 RETURN(get_rec(p_ubn_rec, l_row_notfound));
56 END get_rec;
57 ---------------------------------------------------------------------------
58 -- FUNCTION get_rec for: OKC_USER_BINS_V
59 ---------------------------------------------------------------------------
60 FUNCTION get_rec (
61 p_ubnv_rec IN ubnv_rec_type,
62 x_no_data_found OUT NOCOPY BOOLEAN
63 ) RETURN ubnv_rec_type IS
64 l_ubnv_rec ubnv_rec_type;
65 BEGIN
66 x_no_data_found := TRUE;
67 RETURN(l_ubnv_rec);
68 END get_rec;
69
70 FUNCTION get_rec (
71 p_ubnv_rec IN ubnv_rec_type
72 ) RETURN ubnv_rec_type IS
73 l_row_notfound BOOLEAN := TRUE;
74 BEGIN
75 RETURN(get_rec(p_ubnv_rec, l_row_notfound));
76 END get_rec;
77
78 -----------------------------------------------------
79 -- FUNCTION null_out_defaults for: OKC_USER_BINS_V --
80 -----------------------------------------------------
81 FUNCTION null_out_defaults (
82 p_ubnv_rec IN ubnv_rec_type
83 ) RETURN ubnv_rec_type IS
84 l_ubnv_rec ubnv_rec_type := p_ubnv_rec;
85 BEGIN
86 IF (l_ubnv_rec.contract_id = OKC_API.G_MISS_NUM) THEN
87 l_ubnv_rec.contract_id := NULL;
88 END IF;
89 IF (l_ubnv_rec.contract_number = OKC_API.G_MISS_CHAR) THEN
90 l_ubnv_rec.contract_number := NULL;
91 END IF;
92 IF (l_ubnv_rec.bin_type = OKC_API.G_MISS_CHAR) THEN
93 l_ubnv_rec.bin_type := NULL;
94 END IF;
95 IF (l_ubnv_rec.contract_type = OKC_API.G_MISS_CHAR) THEN
96 l_ubnv_rec.contract_type := NULL;
97 END IF;
98 IF (l_ubnv_rec.program_name = OKC_API.G_MISS_CHAR) THEN
99 l_ubnv_rec.program_name := NULL;
100 END IF;
101 IF (l_ubnv_rec.created_by = OKC_API.G_MISS_NUM) THEN
102 l_ubnv_rec.created_by := NULL;
103 END IF;
104 IF (l_ubnv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
105 l_ubnv_rec.creation_date := NULL;
106 END IF;
107 IF (l_ubnv_rec.contract_number_modifier = OKC_API.G_MISS_CHAR) THEN
108 l_ubnv_rec.contract_number_modifier := NULL;
109 END IF;
110 IF (l_ubnv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
111 l_ubnv_rec.short_description := NULL;
112 END IF;
113 RETURN(l_ubnv_rec);
114 END null_out_defaults;
115 ---------------------------------------------------------------------------
116 -- PROCEDURE Validate_Attributes
117 ---------------------------------------------------------------------------
118 ---------------------------------------------
119 -- Validate_Attributes for:OKC_USER_BINS_V --
120 ---------------------------------------------
121 FUNCTION Validate_Attributes (
122 p_ubnv_rec IN ubnv_rec_type
123 ) RETURN VARCHAR2 IS
124 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
125 BEGIN
126 IF p_ubnv_rec.contract_id = OKC_API.G_MISS_NUM OR
127 p_ubnv_rec.contract_id IS NULL
128 THEN
129 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'contract_id');
130 l_return_status := OKC_API.G_RET_STS_ERROR;
131 ELSIF p_ubnv_rec.contract_number = OKC_API.G_MISS_CHAR OR
132 p_ubnv_rec.contract_number IS NULL
133 THEN
134 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'contract_number');
135 l_return_status := OKC_API.G_RET_STS_ERROR;
136 ELSIF p_ubnv_rec.bin_type = OKC_API.G_MISS_CHAR OR
137 p_ubnv_rec.bin_type IS NULL
138 THEN
139 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'bin_type');
140 l_return_status := OKC_API.G_RET_STS_ERROR;
141 ELSIF p_ubnv_rec.contract_type = OKC_API.G_MISS_CHAR OR
142 p_ubnv_rec.contract_type IS NULL
143 THEN
144 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'contract_type');
145 l_return_status := OKC_API.G_RET_STS_ERROR;
146 ELSIF p_ubnv_rec.program_name = OKC_API.G_MISS_CHAR OR
147 p_ubnv_rec.program_name IS NULL
148 THEN
149 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'program_name');
150 l_return_status := OKC_API.G_RET_STS_ERROR;
151 END IF;
152 RETURN(l_return_status);
153 END Validate_Attributes;
154
155 ---------------------------------------------------------------------------
156 -- PROCEDURE Validate_Record
157 ---------------------------------------------------------------------------
158 -----------------------------------------
159 -- Validate_Record for:OKC_USER_BINS_V --
160 -----------------------------------------
161 FUNCTION Validate_Record (
162 p_ubnv_rec IN ubnv_rec_type
163 ) RETURN VARCHAR2 IS
164 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
165 BEGIN
166 RETURN (l_return_status);
167 END Validate_Record;
168
169 ---------------------------------------------------------------------------
170 -- PROCEDURE Migrate
171 ---------------------------------------------------------------------------
172 PROCEDURE migrate (
173 p_from IN ubnv_rec_type,
174 p_to IN OUT NOCOPY ubn_rec_type
175 ) IS
176 BEGIN
177 p_to.contract_id := p_from.contract_id;
178 p_to.contract_number := p_from.contract_number;
179 p_to.bin_type := p_from.bin_type;
180 p_to.contract_type := p_from.contract_type;
181 p_to.program_name := p_from.program_name;
182 p_to.created_by := p_from.created_by;
183 p_to.creation_date := p_from.creation_date;
184 p_to.contract_number_modifier := p_from.contract_number_modifier;
185 p_to.short_description := p_from.short_description;
186 p_to.id := p_from.id;
187 END migrate;
188 PROCEDURE migrate (
189 p_from IN ubn_rec_type,
190 p_to IN OUT NOCOPY ubnv_rec_type
191 ) IS
192 BEGIN
193 p_to.contract_id := p_from.contract_id;
194 p_to.contract_number := p_from.contract_number;
195 p_to.bin_type := p_from.bin_type;
196 p_to.contract_type := p_from.contract_type;
197 p_to.program_name := p_from.program_name;
198 p_to.created_by := p_from.created_by;
199 p_to.creation_date := p_from.creation_date;
200 p_to.contract_number_modifier := p_from.contract_number_modifier;
201 p_to.short_description := p_from.short_description;
202 p_to.id := p_from.id;
203 END migrate;
204
205 ---------------------------------------------------------------------------
206 -- PROCEDURE validate_row
207 ---------------------------------------------------------------------------
208 --------------------------------------
209 -- validate_row for:OKC_USER_BINS_V --
210 --------------------------------------
211 PROCEDURE validate_row(
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2,
214 x_return_status OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_msg_data OUT NOCOPY VARCHAR2,
217 p_ubnv_rec IN ubnv_rec_type) IS
218
219 l_api_version CONSTANT NUMBER := 1;
220 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
221 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
222 -- l_ubnv_rec ubnv_rec_type := p_ubnv_rec;
223 -- l_ubn_rec ubn_rec_type;
224 BEGIN
225 null;
226 /* l_return_status := OKC_API.START_ACTIVITY(l_api_name,
227 G_PKG_NAME,
228 p_init_msg_list,
229 l_api_version,
230 p_api_version,
231 '_PVT',
232 x_return_status);
233 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
234 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
235 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
236 RAISE OKC_API.G_EXCEPTION_ERROR;
237 END IF;
238 --- Validate all non-missing attributes (Item Level Validation)
239 l_return_status := Validate_Attributes(l_ubnv_rec);
240 --- If any errors happen abort API
241 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
242 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
243 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
244 RAISE OKC_API.G_EXCEPTION_ERROR;
245 END IF;
246 l_return_status := Validate_Record(l_ubnv_rec);
247 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
248 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
249 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
250 RAISE OKC_API.G_EXCEPTION_ERROR;
251 END IF;
252 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
253 EXCEPTION
254 WHEN OKC_API.G_EXCEPTION_ERROR THEN
255 x_return_status := OKC_API.HANDLE_EXCEPTIONS
256 (
257 l_api_name,
258 G_PKG_NAME,
259 'OKC_API.G_RET_STS_ERROR',
260 x_msg_count,
261 x_msg_data,
262 '_PVT'
263 );
264 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
265 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
266 (
267 l_api_name,
268 G_PKG_NAME,
269 'OKC_API.G_RET_STS_UNEXP_ERROR',
270 x_msg_count,
271 x_msg_data,
272 '_PVT'
273 );
274 WHEN OTHERS THEN
275 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
276 (
277 l_api_name,
278 G_PKG_NAME,
279 'OTHERS',
280 x_msg_count,
281 x_msg_data,
282 '_PVT'
283 );
284 */
285 END validate_row;
286 ------------------------------------------
287 -- PL/SQL TBL validate_row for:UBNV_TBL --
288 ------------------------------------------
289 PROCEDURE validate_row(
290 p_api_version IN NUMBER,
291 p_init_msg_list IN VARCHAR2,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY NUMBER,
294 x_msg_data OUT NOCOPY VARCHAR2,
295 p_ubnv_tbl IN ubnv_tbl_type) IS
296
297 l_api_version CONSTANT NUMBER := 1;
298 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
299 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
300 i NUMBER := 0;
301 BEGIN
302
303 OKC_API.init_msg_list(p_init_msg_list);
304 -- Make sure PL/SQL table has records in it before passing
305 IF (p_ubnv_tbl.COUNT > 0) THEN
306 i := p_ubnv_tbl.FIRST;
307 LOOP
308 validate_row (
309 p_api_version => p_api_version,
310 p_init_msg_list => OKC_API.G_FALSE,
311 x_return_status => x_return_status,
312 x_msg_count => x_msg_count,
313 x_msg_data => x_msg_data,
314 p_ubnv_rec => p_ubnv_tbl(i));
315 EXIT WHEN (i = p_ubnv_tbl.LAST);
316 i := p_ubnv_tbl.NEXT(i);
317 END LOOP;
318 END IF;
319 EXCEPTION
320 WHEN OKC_API.G_EXCEPTION_ERROR THEN
321 x_return_status := OKC_API.HANDLE_EXCEPTIONS
322 (
323 l_api_name,
324 G_PKG_NAME,
325 'OKC_API.G_RET_STS_ERROR',
326 x_msg_count,
327 x_msg_data,
328 '_PVT'
329 );
330 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
331 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
332 (
333 l_api_name,
334 G_PKG_NAME,
335 'OKC_API.G_RET_STS_UNEXP_ERROR',
336 x_msg_count,
337 x_msg_data,
338 '_PVT'
339 );
340 WHEN OTHERS THEN
341 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
342 (
343 l_api_name,
344 G_PKG_NAME,
345 'OTHERS',
346 x_msg_count,
347 x_msg_data,
348 '_PVT'
349 );
350 END validate_row;
351
352 ---------------------------------------------------------------------------
353 -- PROCEDURE insert_row
354 ---------------------------------------------------------------------------
355 ----------------------------------
356 -- insert_row for:OKC_USER_BINS --
357 ----------------------------------
358 PROCEDURE insert_row(
359 p_init_msg_list IN VARCHAR2,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_msg_count OUT NOCOPY NUMBER,
362 x_msg_data OUT NOCOPY VARCHAR2,
363 p_ubn_rec IN ubn_rec_type,
364 x_ubn_rec OUT NOCOPY ubn_rec_type) IS
365
366 l_api_version CONSTANT NUMBER := 1;
367 l_api_name CONSTANT VARCHAR2(30) := 'BINS_insert_row';
368 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
369 l_ubn_rec ubn_rec_type := p_ubn_rec;
370 l_def_ubn_rec ubn_rec_type;
371 --------------------------------------
372 -- Set_Attributes for:OKC_USER_BINS --
373 --------------------------------------
374 FUNCTION Set_Attributes (
375 p_ubn_rec IN ubn_rec_type,
376 x_ubn_rec OUT NOCOPY ubn_rec_type
377 ) RETURN VARCHAR2 IS
378 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
379 BEGIN
380 x_ubn_rec := p_ubn_rec;
381 RETURN(l_return_status);
382 END Set_Attributes;
383 BEGIN
384
385
386 /* l_return_status := OKC_API.START_ACTIVITY(l_api_name,
387 p_init_msg_list,
388 '_PVT',
389 x_return_status);
390 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
392 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
393 RAISE OKC_API.G_EXCEPTION_ERROR;
394 END IF;
395 */
396 --- Setting item attributes
397
398 l_return_status := Set_Attributes(
399 p_ubn_rec, -- IN
400 l_ubn_rec); -- OUT
401 --- If any errors happen abort API
402 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
403 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
404 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
405 RAISE OKC_API.G_EXCEPTION_ERROR;
406 END IF;
407
408
409 l_ubn_rec.id := get_seq_id;
410
411 INSERT INTO OKC_USER_BINS(
412 contract_id,
413 contract_number,
414 bin_type,
415 contract_type,
416 program_name,
417 created_by,
418 creation_date,
419 contract_number_modifier,
420 short_description,
421 id)
422 VALUES (
423 l_ubn_rec.contract_id,
424 l_ubn_rec.contract_number,
425 l_ubn_rec.bin_type,
426 l_ubn_rec.contract_type,
427 l_ubn_rec.program_name,
428 l_ubn_rec.created_by,
429 l_ubn_rec.creation_date,
430 l_ubn_rec.contract_number_modifier,
431 l_ubn_rec.short_description,
432 l_ubn_rec.id);
433
434 -- Set OUT values
435 x_ubn_rec := l_ubn_rec;
436 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
437 EXCEPTION
438 WHEN OKC_API.G_EXCEPTION_ERROR THEN
439 x_return_status := OKC_API.HANDLE_EXCEPTIONS
440 (
441 l_api_name,
442 G_PKG_NAME,
443 'OKC_API.G_RET_STS_ERROR',
444 x_msg_count,
445 x_msg_data,
446 '_PVT'
447 );
448 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
449 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
450 (
451 l_api_name,
452 G_PKG_NAME,
453 'OKC_API.G_RET_STS_UNEXP_ERROR',
454 x_msg_count,
455 x_msg_data,
456 '_PVT'
457 );
458 WHEN OTHERS THEN
459 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
460 (
461 l_api_name,
462 G_PKG_NAME,
463 'OTHERS',
464 x_msg_count,
465 x_msg_data,
466 '_PVT'
467 );
468 END insert_row;
469 ------------------------------------
470 -- insert_row for:OKC_USER_BINS_V --
471 ------------------------------------
472 PROCEDURE insert_row(
473 p_api_version IN NUMBER,
474 p_init_msg_list IN VARCHAR2,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2,
478 p_ubnv_rec IN ubnv_rec_type,
479 x_ubnv_rec OUT NOCOPY ubnv_rec_type) IS
480
481 l_api_version CONSTANT NUMBER := 1;
482 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
483 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
484 l_ubnv_rec ubnv_rec_type;
485 l_def_ubnv_rec ubnv_rec_type;
486 l_ubn_rec ubn_rec_type;
487 lx_ubn_rec ubn_rec_type;
488 -------------------------------
489 -- FUNCTION fill_who_columns --
490 -------------------------------
491 FUNCTION fill_who_columns (
492 p_ubnv_rec IN ubnv_rec_type
493 ) RETURN ubnv_rec_type IS
494 l_ubnv_rec ubnv_rec_type := p_ubnv_rec;
495 BEGIN
496
497 l_ubnv_rec.CREATION_DATE := SYSDATE;
498 l_ubnv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
499 RETURN(l_ubnv_rec);
500 END fill_who_columns;
501 ----------------------------------------
502 -- Set_Attributes for:OKC_USER_BINS_V --
503 ----------------------------------------
504 FUNCTION Set_Attributes (
505 p_ubnv_rec IN ubnv_rec_type,
506 x_ubnv_rec OUT NOCOPY ubnv_rec_type
507 ) RETURN VARCHAR2 IS
508 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
509 BEGIN
510 x_ubnv_rec := p_ubnv_rec;
511 RETURN(l_return_status);
512 END Set_Attributes;
513 BEGIN
514
515 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
516 G_PKG_NAME,
517 p_init_msg_list,
518 l_api_version,
519 p_api_version,
520 '_PVT',
521 x_return_status);
522
523 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
524 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
525 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
526 RAISE OKC_API.G_EXCEPTION_ERROR;
527 END IF;
528
529 l_ubnv_rec := null_out_defaults(p_ubnv_rec);
530 --- Setting item attributes
531
532 l_return_status := Set_Attributes(
533 l_ubnv_rec, -- IN
534 l_def_ubnv_rec); -- OUT
535 --- If any errors happen abort API
536 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
537 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
538 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
539 RAISE OKC_API.G_EXCEPTION_ERROR;
540 END IF;
541
542 l_def_ubnv_rec := fill_who_columns(l_def_ubnv_rec);
543 --- Validate all non-missing attributes (Item Level Validation)
544
545 l_return_status := Validate_Attributes(l_def_ubnv_rec);
546 --- If any errors happen abort API
547 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
548 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
549 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
550 RAISE OKC_API.G_EXCEPTION_ERROR;
551 END IF;
552
553 l_return_status := Validate_Record(l_def_ubnv_rec);
554 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
555 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
556 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
557 RAISE OKC_API.G_EXCEPTION_ERROR;
558 END IF;
559 --------------------------------------
560 -- Move VIEW record to "Child" records
561 --------------------------------------
562
563 migrate(l_def_ubnv_rec, l_ubn_rec);
564 --------------------------------------------
565 -- Call the INSERT_ROW for each child record
566 --------------------------------------------
567
568 insert_row(
569 p_init_msg_list,
570 x_return_status,
571 x_msg_count,
572 x_msg_data,
573 l_ubn_rec,
574 lx_ubn_rec
575 );
576
577 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
578 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
579 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
580 RAISE OKC_API.G_EXCEPTION_ERROR;
581 END IF;
582
583 -- migrate(lx_ubn_rec, l_def_ubn_rec);
584 -- Set OUT values
585 x_ubnv_rec := l_def_ubnv_rec;
586 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
587 EXCEPTION
588 WHEN OKC_API.G_EXCEPTION_ERROR THEN
589 x_return_status := OKC_API.HANDLE_EXCEPTIONS
590 (
591 l_api_name,
592 G_PKG_NAME,
593 'OKC_API.G_RET_STS_ERROR',
594 x_msg_count,
595 x_msg_data,
596 '_PVT'
597 );
598 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
599 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
600 (
601 l_api_name,
602 G_PKG_NAME,
603 'OKC_API.G_RET_STS_UNEXP_ERROR',
604 x_msg_count,
605 x_msg_data,
606 '_PVT'
607 );
608 WHEN OTHERS THEN
609 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
610 (
611 l_api_name,
612 G_PKG_NAME,
613 'OTHERS',
614 x_msg_count,
615 x_msg_data,
616 '_PVT'
617 );
618 END insert_row;
619 ----------------------------------------
620 -- PL/SQL TBL insert_row for:UBNV_TBL --
621 ----------------------------------------
622 PROCEDURE insert_row(
623 p_api_version IN NUMBER,
624 p_init_msg_list IN VARCHAR2,
625 x_return_status OUT NOCOPY VARCHAR2,
626 x_msg_count OUT NOCOPY NUMBER,
627 x_msg_data OUT NOCOPY VARCHAR2,
628 p_ubnv_tbl IN ubnv_tbl_type,
629 x_ubnv_tbl OUT NOCOPY ubnv_tbl_type) IS
630
631 l_api_version CONSTANT NUMBER := 1;
632 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
633 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
634 i NUMBER := 0;
635 BEGIN
636 OKC_API.init_msg_list(p_init_msg_list);
637 -- Make sure PL/SQL table has records in it before passing
638 IF (p_ubnv_tbl.COUNT > 0) THEN
639 i := p_ubnv_tbl.FIRST;
640 LOOP
641 insert_row (
642 p_api_version => p_api_version,
643 p_init_msg_list => OKC_API.G_FALSE,
644 x_return_status => x_return_status,
645 x_msg_count => x_msg_count,
646 x_msg_data => x_msg_data,
647 p_ubnv_rec => p_ubnv_tbl(i),
648 x_ubnv_rec => x_ubnv_tbl(i));
649 EXIT WHEN (i = p_ubnv_tbl.LAST);
650 i := p_ubnv_tbl.NEXT(i);
651 END LOOP;
652 END IF;
653 EXCEPTION
654 WHEN OKC_API.G_EXCEPTION_ERROR THEN
655 x_return_status := OKC_API.HANDLE_EXCEPTIONS
656 (
657 l_api_name,
658 G_PKG_NAME,
659 'OKC_API.G_RET_STS_ERROR',
660 x_msg_count,
661 x_msg_data,
662 '_PVT'
663 );
664 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
665 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
666 (
667 l_api_name,
668 G_PKG_NAME,
669 'OKC_API.G_RET_STS_UNEXP_ERROR',
670 x_msg_count,
671 x_msg_data,
672 '_PVT'
673 );
674 WHEN OTHERS THEN
675 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
676 (
677 l_api_name,
678 G_PKG_NAME,
679 'OTHERS',
680 x_msg_count,
681 x_msg_data,
682 '_PVT'
683 );
684 END insert_row;
685
686 ---------------------------------------------------------------------------
687 -- PROCEDURE lock_row
688 ---------------------------------------------------------------------------
689 --------------------------------
690 -- lock_row for:OKC_USER_BINS --
691 --------------------------------
692 PROCEDURE lock_row(
693 p_init_msg_list IN VARCHAR2,
694 x_return_status OUT NOCOPY VARCHAR2,
695 x_msg_count OUT NOCOPY NUMBER,
696 x_msg_data OUT NOCOPY VARCHAR2,
697 p_ubn_rec IN ubn_rec_type) IS
698
699 E_Resource_Busy EXCEPTION;
700 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
701 CURSOR lock_csr (p_ubn_rec IN ubn_rec_type) IS
702 SELECT *
703 FROM OKC_USER_BINS
704 FOR UPDATE NOWAIT;
705
706 l_api_version CONSTANT NUMBER := 1;
707 l_api_name CONSTANT VARCHAR2(30) := 'BINS_lock_row';
708 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
709 l_lock_var lock_csr%ROWTYPE;
710 l_row_notfound BOOLEAN := FALSE;
711 lc_row_notfound BOOLEAN := FALSE;
712 BEGIN
713 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
714 p_init_msg_list,
715 '_PVT',
716 x_return_status);
717 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
718 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
719 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
720 RAISE OKC_API.G_EXCEPTION_ERROR;
721 END IF;
722 BEGIN
723 OPEN lock_csr(p_ubn_rec);
724 FETCH lock_csr INTO l_lock_var;
725 l_row_notfound := lock_csr%NOTFOUND;
726 CLOSE lock_csr;
727 EXCEPTION
728 WHEN E_Resource_Busy THEN
729 IF (lock_csr%ISOPEN) THEN
730 CLOSE lock_csr;
731 END IF;
732 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
733 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
734 END;
735
736 IF ( l_row_notfound ) THEN
737 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
738 RAISE OKC_API.G_EXCEPTION_ERROR;
739 ELSE
740 IF (l_lock_var.CONTRACT_ID <> p_ubn_rec.contract_id) THEN
741 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
742 RAISE OKC_API.G_EXCEPTION_ERROR;
743 END IF;
744 IF (l_lock_var.CONTRACT_NUMBER <> p_ubn_rec.contract_number) THEN
745 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
746 RAISE OKC_API.G_EXCEPTION_ERROR;
747 END IF;
748 IF (l_lock_var.BIN_TYPE <> p_ubn_rec.bin_type) THEN
749 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
750 RAISE OKC_API.G_EXCEPTION_ERROR;
751 END IF;
752 IF (l_lock_var.CONTRACT_TYPE <> p_ubn_rec.contract_type) THEN
753 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
754 RAISE OKC_API.G_EXCEPTION_ERROR;
755 END IF;
756 IF (l_lock_var.PROGRAM_NAME <> p_ubn_rec.program_name) THEN
757 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
758 RAISE OKC_API.G_EXCEPTION_ERROR;
759 END IF;
760 IF (l_lock_var.CREATED_BY <> p_ubn_rec.created_by) THEN
761 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
762 RAISE OKC_API.G_EXCEPTION_ERROR;
763 END IF;
764 IF (l_lock_var.CREATION_DATE <> p_ubn_rec.creation_date) THEN
765 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
766 RAISE OKC_API.G_EXCEPTION_ERROR;
767 END IF;
768 IF (l_lock_var.CONTRACT_NUMBER_MODIFIER <> p_ubn_rec.contract_number_modifier) THEN
769 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
770 RAISE OKC_API.G_EXCEPTION_ERROR;
771 END IF;
772 IF (l_lock_var.SHORT_DESCRIPTION <> p_ubn_rec.short_description) THEN
773 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
774 RAISE OKC_API.G_EXCEPTION_ERROR;
775 END IF;
776 END IF;
777 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
778 EXCEPTION
779 WHEN OKC_API.G_EXCEPTION_ERROR THEN
780 x_return_status := OKC_API.HANDLE_EXCEPTIONS
781 (
782 l_api_name,
783 G_PKG_NAME,
784 'OKC_API.G_RET_STS_ERROR',
785 x_msg_count,
786 x_msg_data,
787 '_PVT'
788 );
789 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
790 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
791 (
792 l_api_name,
793 G_PKG_NAME,
794 'OKC_API.G_RET_STS_UNEXP_ERROR',
795 x_msg_count,
796 x_msg_data,
797 '_PVT'
798 );
799 WHEN OTHERS THEN
800 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
801 (
802 l_api_name,
803 G_PKG_NAME,
804 'OTHERS',
805 x_msg_count,
806 x_msg_data,
807 '_PVT'
808 );
809 END lock_row;
810 ----------------------------------
811 -- lock_row for:OKC_USER_BINS_V --
812 ----------------------------------
813 PROCEDURE lock_row(
814 p_api_version IN NUMBER,
815 p_init_msg_list IN VARCHAR2,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 p_ubnv_rec IN ubnv_rec_type) IS
820
821 l_api_version CONSTANT NUMBER := 1;
822 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
823 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
824 l_ubn_rec ubn_rec_type;
825 BEGIN
826 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
827 G_PKG_NAME,
828 p_init_msg_list,
829 l_api_version,
830 p_api_version,
831 '_PVT',
832 x_return_status);
833 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
834 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
835 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
836 RAISE OKC_API.G_EXCEPTION_ERROR;
837 END IF;
838 --------------------------------------
839 -- Move VIEW record to "Child" records
840 --------------------------------------
841 -- migrate(p_ubnv_rec, l_ubn_rec);
842 --------------------------------------------
843 -- Call the LOCK_ROW for each child record
844 --------------------------------------------
845 lock_row(
846 p_init_msg_list,
847 x_return_status,
848 x_msg_count,
849 x_msg_data,
850 l_ubn_rec
851 );
852 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
853 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
855 RAISE OKC_API.G_EXCEPTION_ERROR;
856 END IF;
857 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
858 EXCEPTION
859 WHEN OKC_API.G_EXCEPTION_ERROR THEN
860 x_return_status := OKC_API.HANDLE_EXCEPTIONS
861 (
862 l_api_name,
863 G_PKG_NAME,
864 'OKC_API.G_RET_STS_ERROR',
865 x_msg_count,
866 x_msg_data,
867 '_PVT'
868 );
869 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
870 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
871 (
872 l_api_name,
873 G_PKG_NAME,
874 'OKC_API.G_RET_STS_UNEXP_ERROR',
875 x_msg_count,
876 x_msg_data,
877 '_PVT'
878 );
879 WHEN OTHERS THEN
880 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
881 (
882 l_api_name,
883 G_PKG_NAME,
884 'OTHERS',
885 x_msg_count,
886 x_msg_data,
887 '_PVT'
888 );
889 END lock_row;
890 --------------------------------------
891 -- PL/SQL TBL lock_row for:UBNV_TBL --
892 --------------------------------------
893 PROCEDURE lock_row(
894 p_api_version IN NUMBER,
895 p_init_msg_list IN VARCHAR2,
896 x_return_status OUT NOCOPY VARCHAR2,
897 x_msg_count OUT NOCOPY NUMBER,
898 x_msg_data OUT NOCOPY VARCHAR2,
899 p_ubnv_tbl IN ubnv_tbl_type) IS
900
901 l_api_version CONSTANT NUMBER := 1;
902 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
903 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
904 i NUMBER := 0;
905 BEGIN
906 OKC_API.init_msg_list(p_init_msg_list);
907 -- Make sure PL/SQL table has records in it before passing
908 IF (p_ubnv_tbl.COUNT > 0) THEN
909 i := p_ubnv_tbl.FIRST;
910 LOOP
911 lock_row (
912 p_api_version => p_api_version,
913 p_init_msg_list => OKC_API.G_FALSE,
914 x_return_status => x_return_status,
915 x_msg_count => x_msg_count,
916 x_msg_data => x_msg_data,
917 p_ubnv_rec => p_ubnv_tbl(i));
918 EXIT WHEN (i = p_ubnv_tbl.LAST);
919 i := p_ubnv_tbl.NEXT(i);
920 END LOOP;
921 END IF;
922 EXCEPTION
923 WHEN OKC_API.G_EXCEPTION_ERROR THEN
924 x_return_status := OKC_API.HANDLE_EXCEPTIONS
925 (
926 l_api_name,
927 G_PKG_NAME,
928 'OKC_API.G_RET_STS_ERROR',
929 x_msg_count,
930 x_msg_data,
931 '_PVT'
932 );
933 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
934 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
935 (
936 l_api_name,
937 G_PKG_NAME,
938 'OKC_API.G_RET_STS_UNEXP_ERROR',
939 x_msg_count,
940 x_msg_data,
941 '_PVT'
942 );
943 WHEN OTHERS THEN
944 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
945 (
946 l_api_name,
947 G_PKG_NAME,
948 'OTHERS',
949 x_msg_count,
950 x_msg_data,
951 '_PVT'
952 );
953 END lock_row;
954
955 ---------------------------------------------------------------------------
956 -- PROCEDURE update_row
957 ---------------------------------------------------------------------------
958 ----------------------------------
959 -- update_row for:OKC_USER_BINS --
960 ----------------------------------
961 PROCEDURE update_row(
962 p_init_msg_list IN VARCHAR2,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2,
966 p_ubn_rec IN ubn_rec_type,
967 x_ubn_rec OUT NOCOPY ubn_rec_type) IS
968
969 l_api_version CONSTANT NUMBER := 1;
970 l_api_name CONSTANT VARCHAR2(30) := 'BINS_update_row';
971 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
972 l_ubn_rec ubn_rec_type := p_ubn_rec;
973 l_def_ubn_rec ubn_rec_type;
974 l_row_notfound BOOLEAN := TRUE;
975 ----------------------------------
976 -- FUNCTION populate_new_record --
977 ----------------------------------
978 FUNCTION populate_new_record (
979 p_ubn_rec IN ubn_rec_type,
980 x_ubn_rec OUT NOCOPY ubn_rec_type
981 ) RETURN VARCHAR2 IS
982 l_ubn_rec ubn_rec_type;
983 l_row_notfound BOOLEAN := TRUE;
984 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
985 BEGIN
986 x_ubn_rec := p_ubn_rec;
987 -- Get current database values
988 l_ubn_rec := get_rec(p_ubn_rec, l_row_notfound);
989 IF (l_row_notfound) THEN
990 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
991 END IF;
992 IF (x_ubn_rec.contract_id = OKC_API.G_MISS_NUM)
993 THEN
994 x_ubn_rec.contract_id := l_ubn_rec.contract_id;
995 END IF;
996 IF (x_ubn_rec.contract_number = OKC_API.G_MISS_CHAR)
997 THEN
998 x_ubn_rec.contract_number := l_ubn_rec.contract_number;
999 END IF;
1000 IF (x_ubn_rec.bin_type = OKC_API.G_MISS_CHAR)
1001 THEN
1002 x_ubn_rec.bin_type := l_ubn_rec.bin_type;
1003 END IF;
1004 IF (x_ubn_rec.contract_type = OKC_API.G_MISS_CHAR)
1005 THEN
1006 x_ubn_rec.contract_type := l_ubn_rec.contract_type;
1007 END IF;
1008 IF (x_ubn_rec.program_name = OKC_API.G_MISS_CHAR)
1009 THEN
1010 x_ubn_rec.program_name := l_ubn_rec.program_name;
1011 END IF;
1012 IF (x_ubn_rec.created_by = OKC_API.G_MISS_NUM)
1013 THEN
1014 x_ubn_rec.created_by := l_ubn_rec.created_by;
1015 END IF;
1016 IF (x_ubn_rec.creation_date = OKC_API.G_MISS_DATE)
1017 THEN
1018 x_ubn_rec.creation_date := l_ubn_rec.creation_date;
1019 END IF;
1020 IF (x_ubn_rec.contract_number_modifier = OKC_API.G_MISS_CHAR)
1021 THEN
1022 x_ubn_rec.contract_number_modifier := l_ubn_rec.contract_number_modifier;
1023 END IF;
1024 IF (x_ubn_rec.short_description = OKC_API.G_MISS_CHAR)
1025 THEN
1026 x_ubn_rec.short_description := l_ubn_rec.short_description;
1027 END IF;
1028 RETURN(l_return_status);
1029 END populate_new_record;
1030 --------------------------------------
1031 -- Set_Attributes for:OKC_USER_BINS --
1032 --------------------------------------
1033 FUNCTION Set_Attributes (
1034 p_ubn_rec IN ubn_rec_type,
1035 x_ubn_rec OUT NOCOPY ubn_rec_type
1036 ) RETURN VARCHAR2 IS
1037 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1038 BEGIN
1039 x_ubn_rec := p_ubn_rec;
1040 RETURN(l_return_status);
1041 END Set_Attributes;
1042 BEGIN
1043 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1044 p_init_msg_list,
1045 '_PVT',
1046 x_return_status);
1047 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1048 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1049 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1050 RAISE OKC_API.G_EXCEPTION_ERROR;
1051 END IF;
1052 --- Setting item attributes
1053 l_return_status := Set_Attributes(
1054 p_ubn_rec, -- IN
1055 l_ubn_rec); -- OUT
1056 --- If any errors happen abort API
1057 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1058 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1059 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1060 RAISE OKC_API.G_EXCEPTION_ERROR;
1061 END IF;
1062 l_return_status := populate_new_record(l_ubn_rec, l_def_ubn_rec);
1063 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1064 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1065 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1066 RAISE OKC_API.G_EXCEPTION_ERROR;
1067 END IF;
1068 UPDATE OKC_USER_BINS
1069 SET CONTRACT_ID = l_def_ubn_rec.contract_id,
1070 CONTRACT_NUMBER = l_def_ubn_rec.contract_number,
1071 BIN_TYPE = l_def_ubn_rec.bin_type,
1072 CONTRACT_TYPE = l_def_ubn_rec.contract_type,
1073 PROGRAM_NAME = l_def_ubn_rec.program_name,
1074 CREATED_BY = l_def_ubn_rec.created_by,
1075 CREATION_DATE = l_def_ubn_rec.creation_date,
1076 CONTRACT_NUMBER_MODIFIER = l_def_ubn_rec.contract_number_modifier,
1077 SHORT_DESCRIPTION = l_def_ubn_rec.short_description
1078 WHERE id = l_def_ubn_rec.id;
1079
1080 x_ubn_rec := l_def_ubn_rec;
1081 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1082 EXCEPTION
1083 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1084 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1085 (
1086 l_api_name,
1087 G_PKG_NAME,
1088 'OKC_API.G_RET_STS_ERROR',
1089 x_msg_count,
1090 x_msg_data,
1091 '_PVT'
1092 );
1093 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1094 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1095 (
1096 l_api_name,
1097 G_PKG_NAME,
1098 'OKC_API.G_RET_STS_UNEXP_ERROR',
1099 x_msg_count,
1100 x_msg_data,
1101 '_PVT'
1102 );
1103 WHEN OTHERS THEN
1104 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1105 (
1106 l_api_name,
1107 G_PKG_NAME,
1108 'OTHERS',
1109 x_msg_count,
1110 x_msg_data,
1111 '_PVT'
1112 );
1113 END update_row;
1114 ------------------------------------
1115 -- update_row for:OKC_USER_BINS_V --
1116 ------------------------------------
1117 PROCEDURE update_row(
1118 p_api_version IN NUMBER,
1119 p_init_msg_list IN VARCHAR2,
1120 x_return_status OUT NOCOPY VARCHAR2,
1121 x_msg_count OUT NOCOPY NUMBER,
1122 x_msg_data OUT NOCOPY VARCHAR2,
1123 p_ubnv_rec IN ubnv_rec_type,
1124 x_ubnv_rec OUT NOCOPY ubnv_rec_type) IS
1125
1126 l_api_version CONSTANT NUMBER := 1;
1127 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1128 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1129 l_ubnv_rec ubnv_rec_type := p_ubnv_rec;
1130 l_def_ubnv_rec ubnv_rec_type;
1131 l_ubn_rec ubn_rec_type;
1132 lx_ubn_rec ubn_rec_type;
1133 -------------------------------
1134 -- FUNCTION fill_who_columns --
1135 -------------------------------
1136 FUNCTION fill_who_columns (
1137 p_ubnv_rec IN ubnv_rec_type
1138 ) RETURN ubnv_rec_type IS
1139 l_ubnv_rec ubnv_rec_type := p_ubnv_rec;
1140 BEGIN
1141 RETURN(l_ubnv_rec);
1142 END fill_who_columns;
1143 ----------------------------------
1144 -- FUNCTION populate_new_record --
1145 ----------------------------------
1146 FUNCTION populate_new_record (
1147 p_ubnv_rec IN ubnv_rec_type,
1148 x_ubnv_rec OUT NOCOPY ubnv_rec_type
1149 ) RETURN VARCHAR2 IS
1150 l_ubnv_rec ubnv_rec_type;
1151 l_row_notfound BOOLEAN := TRUE;
1152 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1153 BEGIN
1154 x_ubnv_rec := p_ubnv_rec;
1155 -- Get current database values
1156 l_ubnv_rec := get_rec(p_ubnv_rec, l_row_notfound);
1157 IF (l_row_notfound) THEN
1158 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1159 END IF;
1160 IF (x_ubnv_rec.contract_id = OKC_API.G_MISS_NUM)
1161 THEN
1162 x_ubnv_rec.contract_id := l_ubnv_rec.contract_id;
1163 END IF;
1164 IF (x_ubnv_rec.contract_number = OKC_API.G_MISS_CHAR)
1165 THEN
1166 x_ubnv_rec.contract_number := l_ubnv_rec.contract_number;
1167 END IF;
1168 IF (x_ubnv_rec.bin_type = OKC_API.G_MISS_CHAR)
1169 THEN
1170 x_ubnv_rec.bin_type := l_ubnv_rec.bin_type;
1171 END IF;
1172 IF (x_ubnv_rec.contract_type = OKC_API.G_MISS_CHAR)
1173 THEN
1174 x_ubnv_rec.contract_type := l_ubnv_rec.contract_type;
1175 END IF;
1176 IF (x_ubnv_rec.program_name = OKC_API.G_MISS_CHAR)
1177 THEN
1178 x_ubnv_rec.program_name := l_ubnv_rec.program_name;
1179 END IF;
1180 IF (x_ubnv_rec.created_by = OKC_API.G_MISS_NUM)
1181 THEN
1182 x_ubnv_rec.created_by := l_ubnv_rec.created_by;
1183 END IF;
1184 IF (x_ubnv_rec.creation_date = OKC_API.G_MISS_DATE)
1185 THEN
1186 x_ubnv_rec.creation_date := l_ubnv_rec.creation_date;
1187 END IF;
1188 IF (x_ubnv_rec.contract_number_modifier = OKC_API.G_MISS_CHAR)
1189 THEN
1190 x_ubnv_rec.contract_number_modifier := l_ubnv_rec.contract_number_modifier;
1191 END IF;
1192 IF (x_ubnv_rec.short_description = OKC_API.G_MISS_CHAR)
1193 THEN
1194 x_ubnv_rec.short_description := l_ubnv_rec.short_description;
1195 END IF;
1196 RETURN(l_return_status);
1197 END populate_new_record;
1198 ----------------------------------------
1199 -- Set_Attributes for:OKC_USER_BINS_V --
1200 ----------------------------------------
1201 FUNCTION Set_Attributes (
1202 p_ubnv_rec IN ubnv_rec_type,
1203 x_ubnv_rec OUT NOCOPY ubnv_rec_type
1204 ) RETURN VARCHAR2 IS
1205 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1206 BEGIN
1207 x_ubnv_rec := p_ubnv_rec;
1208 RETURN(l_return_status);
1209 END Set_Attributes;
1210 BEGIN
1211 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1212 G_PKG_NAME,
1213 p_init_msg_list,
1214 l_api_version,
1215 p_api_version,
1216 '_PVT',
1217 x_return_status);
1218 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1219 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1220 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1221 RAISE OKC_API.G_EXCEPTION_ERROR;
1222 END IF;
1223 --- Setting item attributes
1224 l_return_status := Set_Attributes(
1225 p_ubnv_rec, -- IN
1226 l_ubnv_rec); -- OUT
1227 --- If any errors happen abort API
1228 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1229 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1230 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1231 RAISE OKC_API.G_EXCEPTION_ERROR;
1232 END IF;
1233 l_return_status := populate_new_record(l_ubnv_rec, l_def_ubnv_rec);
1234 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1235 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1236 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1237 RAISE OKC_API.G_EXCEPTION_ERROR;
1238 END IF;
1239 l_def_ubnv_rec := fill_who_columns(l_def_ubnv_rec);
1240 --- Validate all non-missing attributes (Item Level Validation)
1241 l_return_status := Validate_Attributes(l_def_ubnv_rec);
1242 --- If any errors happen abort API
1243 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1244 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1245 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1246 RAISE OKC_API.G_EXCEPTION_ERROR;
1247 END IF;
1248 l_return_status := Validate_Record(l_def_ubnv_rec);
1249 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1251 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1252 RAISE OKC_API.G_EXCEPTION_ERROR;
1253 END IF;
1254
1255 --------------------------------------
1256 -- Move VIEW record to "Child" records
1257 --------------------------------------
1258 -- migrate(l_def_ubnv_rec, l_ubn_rec);
1259 --------------------------------------------
1260 -- Call the UPDATE_ROW for each child record
1261 --------------------------------------------
1262 update_row(
1263 p_init_msg_list,
1264 x_return_status,
1265 x_msg_count,
1266 x_msg_data,
1267 l_ubn_rec,
1268 lx_ubn_rec
1269 );
1270 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1272 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1273 RAISE OKC_API.G_EXCEPTION_ERROR;
1274 END IF;
1275 -- migrate(lx_ubn_rec, l_def_ubnv_rec);
1276 x_ubnv_rec := l_def_ubnv_rec;
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 update_row;
1310 ----------------------------------------
1311 -- PL/SQL TBL update_row for:UBNV_TBL --
1312 ----------------------------------------
1313 PROCEDURE update_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_ubnv_tbl IN ubnv_tbl_type,
1320 x_ubnv_tbl OUT NOCOPY ubnv_tbl_type) IS
1321
1322 l_api_version CONSTANT NUMBER := 1;
1323 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1324 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1325 i NUMBER := 0;
1326 BEGIN
1327 OKC_API.init_msg_list(p_init_msg_list);
1328 -- Make sure PL/SQL table has records in it before passing
1329 IF (p_ubnv_tbl.COUNT > 0) THEN
1330 i := p_ubnv_tbl.FIRST;
1331 LOOP
1332 update_row (
1333 p_api_version => p_api_version,
1334 p_init_msg_list => OKC_API.G_FALSE,
1335 x_return_status => x_return_status,
1336 x_msg_count => x_msg_count,
1337 x_msg_data => x_msg_data,
1338 p_ubnv_rec => p_ubnv_tbl(i),
1339 x_ubnv_rec => x_ubnv_tbl(i));
1340 EXIT WHEN (i = p_ubnv_tbl.LAST);
1341 i := p_ubnv_tbl.NEXT(i);
1342 END LOOP;
1343 END IF;
1344 EXCEPTION
1345 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1346 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1347 (
1348 l_api_name,
1349 G_PKG_NAME,
1350 'OKC_API.G_RET_STS_ERROR',
1351 x_msg_count,
1352 x_msg_data,
1353 '_PVT'
1354 );
1355 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1356 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1357 (
1358 l_api_name,
1359 G_PKG_NAME,
1360 'OKC_API.G_RET_STS_UNEXP_ERROR',
1361 x_msg_count,
1362 x_msg_data,
1363 '_PVT'
1364 );
1365 WHEN OTHERS THEN
1366 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1367 (
1368 l_api_name,
1369 G_PKG_NAME,
1370 'OTHERS',
1371 x_msg_count,
1372 x_msg_data,
1373 '_PVT'
1374 );
1375 END update_row;
1376
1377 ---------------------------------------------------------------------------
1378 -- PROCEDURE delete_row
1379 ---------------------------------------------------------------------------
1380 ----------------------------------
1381 -- delete_row for:OKC_USER_BINS --
1382 ----------------------------------
1383 PROCEDURE delete_row(
1384 p_init_msg_list IN VARCHAR2,
1385 x_return_status OUT NOCOPY VARCHAR2,
1386 x_msg_count OUT NOCOPY NUMBER,
1387 x_msg_data OUT NOCOPY VARCHAR2,
1388 p_ubn_rec IN ubn_rec_type) IS
1389
1390 l_api_version CONSTANT NUMBER := 1;
1391 l_api_name CONSTANT VARCHAR2(30) := 'BINS_delete_row';
1392 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1393 l_ubn_rec ubn_rec_type:= p_ubn_rec;
1394 l_row_notfound BOOLEAN := TRUE;
1395 BEGIN
1396 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1397 p_init_msg_list,
1398 '_PVT',
1399 x_return_status);
1400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 END IF;
1405 DELETE FROM OKC_USER_BINS
1406 WHERE id = l_ubn_rec.id;
1407 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1408 EXCEPTION
1409 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1410 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1411 (
1412 l_api_name,
1413 G_PKG_NAME,
1414 'OKC_API.G_RET_STS_ERROR',
1415 x_msg_count,
1416 x_msg_data,
1417 '_PVT'
1418 );
1419 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1420 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1421 (
1422 l_api_name,
1423 G_PKG_NAME,
1424 'OKC_API.G_RET_STS_UNEXP_ERROR',
1425 x_msg_count,
1426 x_msg_data,
1427 '_PVT'
1428 );
1429 WHEN OTHERS THEN
1430 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1431 (
1432 l_api_name,
1433 G_PKG_NAME,
1434 'OTHERS',
1435 x_msg_count,
1436 x_msg_data,
1437 '_PVT'
1438 );
1439 END delete_row;
1440 ------------------------------------
1441 -- delete_row for:OKC_USER_BINS_V --
1442 ------------------------------------
1443 PROCEDURE delete_row(
1444 p_api_version IN NUMBER,
1445 p_init_msg_list IN VARCHAR2,
1446 x_return_status OUT NOCOPY VARCHAR2,
1447 x_msg_count OUT NOCOPY NUMBER,
1448 x_msg_data OUT NOCOPY VARCHAR2,
1449 p_ubnv_rec IN ubnv_rec_type) IS
1450
1451 l_api_version CONSTANT NUMBER := 1;
1452 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1453 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1454 l_ubnv_rec ubnv_rec_type := p_ubnv_rec;
1455 l_ubn_rec ubn_rec_type;
1456 BEGIN
1457 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1458 G_PKG_NAME,
1459 p_init_msg_list,
1460 l_api_version,
1461 p_api_version,
1462 '_PVT',
1463 x_return_status);
1464 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1465 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1466 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1467 RAISE OKC_API.G_EXCEPTION_ERROR;
1468 END IF;
1469 --------------------------------------
1470 -- Move VIEW record to "Child" records
1471 --------------------------------------
1472 migrate(l_ubnv_rec, l_ubn_rec);
1473 --------------------------------------------
1474 -- Call the DELETE_ROW for each child record
1475 --------------------------------------------
1476 delete_row(
1477 p_init_msg_list,
1478 x_return_status,
1479 x_msg_count,
1480 x_msg_data,
1481 l_ubn_rec
1482 );
1483 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1485 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1486 RAISE OKC_API.G_EXCEPTION_ERROR;
1487 END IF;
1488 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1489 EXCEPTION
1490 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1491 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1492 (
1493 l_api_name,
1494 G_PKG_NAME,
1495 'OKC_API.G_RET_STS_ERROR',
1496 x_msg_count,
1497 x_msg_data,
1498 '_PVT'
1499 );
1500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1501 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1502 (
1503 l_api_name,
1504 G_PKG_NAME,
1505 'OKC_API.G_RET_STS_UNEXP_ERROR',
1506 x_msg_count,
1507 x_msg_data,
1508 '_PVT'
1509 );
1510 WHEN OTHERS THEN
1511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1512 (
1513 l_api_name,
1514 G_PKG_NAME,
1515 'OTHERS',
1516 x_msg_count,
1517 x_msg_data,
1518 '_PVT'
1519 );
1520 END delete_row;
1521 ----------------------------------------
1522 -- PL/SQL TBL delete_row for:UBNV_TBL --
1523 ----------------------------------------
1524 PROCEDURE delete_row(
1525 p_api_version IN NUMBER,
1526 p_init_msg_list IN VARCHAR2,
1527 x_return_status OUT NOCOPY VARCHAR2,
1528 x_msg_count OUT NOCOPY NUMBER,
1529 x_msg_data OUT NOCOPY VARCHAR2,
1530 p_ubnv_tbl IN ubnv_tbl_type) IS
1531
1532 l_api_version CONSTANT NUMBER := 1;
1533 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1534 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1535 i NUMBER := 0;
1536 BEGIN
1537 OKC_API.init_msg_list(p_init_msg_list);
1538 -- Make sure PL/SQL table has records in it before passing
1539 IF (p_ubnv_tbl.COUNT > 0) THEN
1540 i := p_ubnv_tbl.FIRST;
1541 LOOP
1542 delete_row (
1543 p_api_version => p_api_version,
1544 p_init_msg_list => OKC_API.G_FALSE,
1545 x_return_status => x_return_status,
1546 x_msg_count => x_msg_count,
1547 x_msg_data => x_msg_data,
1548 p_ubnv_rec => p_ubnv_tbl(i));
1549 EXIT WHEN (i = p_ubnv_tbl.LAST);
1550 i := p_ubnv_tbl.NEXT(i);
1551 END LOOP;
1552 END IF;
1553 EXCEPTION
1554 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1555 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1556 (
1557 l_api_name,
1558 G_PKG_NAME,
1559 'OKC_API.G_RET_STS_ERROR',
1560 x_msg_count,
1561 x_msg_data,
1562 '_PVT'
1563 );
1564 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1565 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1566 (
1567 l_api_name,
1568 G_PKG_NAME,
1569 'OKC_API.G_RET_STS_UNEXP_ERROR',
1570 x_msg_count,
1571 x_msg_data,
1572 '_PVT'
1573 );
1574 WHEN OTHERS THEN
1575 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1576 (
1577 l_api_name,
1578 G_PKG_NAME,
1579 'OTHERS',
1580 x_msg_count,
1581 x_msg_data,
1582 '_PVT'
1583 );
1584 END delete_row;
1585 END OKC_UBL_PVT;