[Home] [Help]
PACKAGE BODY: APPS.OKC_RTV_PVT
Source
1 PACKAGE BODY OKC_RTV_PVT AS
2 /* $Header: OKCSRTVB.pls 120.0 2005/05/26 09:43:01 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_RESOLVED_TIMEVALUES
39 ---------------------------------------------------------------------------
40 FUNCTION get_rec (
41 p_rtv_rec IN rtv_rec_type,
42 x_no_data_found OUT NOCOPY BOOLEAN
43 ) RETURN rtv_rec_type IS
44 CURSOR rtv_pk_csr (p_id IN NUMBER) IS
45 SELECT
46 ID,
47 TVE_ID,
48 COE_ID,
49 DATETIME,
50 OBJECT_VERSION_NUMBER,
51 CREATED_BY,
52 CREATION_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATE_LOGIN
56 FROM Okc_Resolved_Timevalues
57 WHERE okc_resolved_timevalues.id = p_id;
58 l_rtv_pk rtv_pk_csr%ROWTYPE;
59 l_rtv_rec rtv_rec_type;
60 BEGIN
61 x_no_data_found := TRUE;
62 -- Get current database values
63 OPEN rtv_pk_csr (p_rtv_rec.id);
64 FETCH rtv_pk_csr INTO
65 l_rtv_rec.ID,
66 l_rtv_rec.TVE_ID,
67 l_rtv_rec.COE_ID,
68 l_rtv_rec.DATETIME,
69 l_rtv_rec.OBJECT_VERSION_NUMBER,
70 l_rtv_rec.CREATED_BY,
71 l_rtv_rec.CREATION_DATE,
72 l_rtv_rec.LAST_UPDATED_BY,
73 l_rtv_rec.LAST_UPDATE_DATE,
74 l_rtv_rec.LAST_UPDATE_LOGIN;
75 x_no_data_found := rtv_pk_csr%NOTFOUND;
76 CLOSE rtv_pk_csr;
77 RETURN(l_rtv_rec);
78 END get_rec;
79
80 FUNCTION get_rec (
81 p_rtv_rec IN rtv_rec_type
82 ) RETURN rtv_rec_type IS
83 l_row_notfound BOOLEAN := TRUE;
84 BEGIN
85 RETURN(get_rec(p_rtv_rec, l_row_notfound));
86 END get_rec;
87 ---------------------------------------------------------------------------
88 -- FUNCTION get_rec for: OKC_RESOLVED_TIMEVALUES_V
89 ---------------------------------------------------------------------------
90 FUNCTION get_rec (
91 p_rtvv_rec IN rtvv_rec_type,
92 x_no_data_found OUT NOCOPY BOOLEAN
93 ) RETURN rtvv_rec_type IS
94 CURSOR okc_ttvv_pk_csr (p_id IN NUMBER) IS
95 SELECT
96 ID,
97 OBJECT_VERSION_NUMBER,
98 TVE_ID,
99 COE_ID,
100 DATETIME,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_LOGIN
106 FROM Okc_Resolved_Timevalues_V
107 WHERE okc_resolved_timevalues_v.id = p_id;
108 l_okc_ttvv_pk okc_ttvv_pk_csr%ROWTYPE;
109 l_rtvv_rec rtvv_rec_type;
110 BEGIN
111 x_no_data_found := TRUE;
112 -- Get current database values
113 OPEN okc_ttvv_pk_csr (p_rtvv_rec.id);
114 FETCH okc_ttvv_pk_csr INTO
115 l_rtvv_rec.ID,
116 l_rtvv_rec.OBJECT_VERSION_NUMBER,
117 l_rtvv_rec.TVE_ID,
118 l_rtvv_rec.COE_ID,
119 l_rtvv_rec.DATETIME,
120 l_rtvv_rec.CREATED_BY,
121 l_rtvv_rec.CREATION_DATE,
122 l_rtvv_rec.LAST_UPDATED_BY,
123 l_rtvv_rec.LAST_UPDATE_DATE,
124 l_rtvv_rec.LAST_UPDATE_LOGIN;
125 x_no_data_found := okc_ttvv_pk_csr%NOTFOUND;
126 CLOSE okc_ttvv_pk_csr;
127 RETURN(l_rtvv_rec);
128 END get_rec;
129
130 FUNCTION get_rec (
131 p_rtvv_rec IN rtvv_rec_type
132 ) RETURN rtvv_rec_type IS
133 l_row_notfound BOOLEAN := TRUE;
134 BEGIN
135 RETURN(get_rec(p_rtvv_rec, l_row_notfound));
136 END get_rec;
137
138 ---------------------------------------------------------------
139 -- FUNCTION null_out_defaults for: OKC_RESOLVED_TIMEVALUES_V --
140 ---------------------------------------------------------------
141 FUNCTION null_out_defaults (
142 p_rtvv_rec IN rtvv_rec_type
143 ) RETURN rtvv_rec_type IS
144 l_rtvv_rec rtvv_rec_type := p_rtvv_rec;
145 BEGIN
146 IF (l_rtvv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
147 l_rtvv_rec.object_version_number := NULL;
148 END IF;
149 IF (l_rtvv_rec.tve_id = OKC_API.G_MISS_NUM) THEN
150 l_rtvv_rec.tve_id := NULL;
151 END IF;
152 IF (l_rtvv_rec.coe_id = OKC_API.G_MISS_NUM) THEN
153 l_rtvv_rec.coe_id := NULL;
154 END IF;
155 IF (l_rtvv_rec.datetime = OKC_API.G_MISS_DATE) THEN
156 l_rtvv_rec.datetime := NULL;
157 END IF;
158 IF (l_rtvv_rec.created_by = OKC_API.G_MISS_NUM) THEN
159 l_rtvv_rec.created_by := NULL;
160 END IF;
161 IF (l_rtvv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
162 l_rtvv_rec.creation_date := NULL;
163 END IF;
164 IF (l_rtvv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
165 l_rtvv_rec.last_updated_by := NULL;
166 END IF;
167 IF (l_rtvv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
168 l_rtvv_rec.last_update_date := NULL;
169 END IF;
170 IF (l_rtvv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
171 l_rtvv_rec.last_update_login := NULL;
172 END IF;
173 RETURN(l_rtvv_rec);
174 END null_out_defaults;
175 ---------------------------------------------------------------------------
176 -- PROCEDURE Validate_Attributes
177 ---------------------------------------------------------------------------
178 --**** Change from TAPI Code---follow till end of change---------------
179 -- 1. Moved all column validations (including FK) to Validate_column
180 -- and is called from Validate_Attributes
181 -- 2. Validate_Records will have tuple rule checks.
182 -------------------------------------------------------
183 -- Validate_Attributes for:OKC_RESOLVED_TIMEVALUES_V --
184 -------------------------------------------------------
185 ---------------------------------------------------------------------------
186
187 PROCEDURE Validate_Tve_Id (
188 x_return_status OUT NOCOPY VARCHAR2,
189 p_rtvv_rec IN rtvv_rec_type) IS
190 item_not_found_error EXCEPTION;
191 l_row_notfound BOOLEAN := TRUE;
192 CURSOR okc_tvev_pk_csr (p_id IN NUMBER) IS
193 SELECT '1'
194 FROM Okc_Timevalues
195 WHERE okc_timevalues.id = p_id;
196 l_okc_tvev_pk okc_tvev_pk_csr%ROWTYPE;
197 BEGIN
198 x_return_status := OKC_API.G_RET_STS_SUCCESS;
199 IF (p_rtvv_rec.TVE_ID IS NOT NULL AND
200 p_rtvv_rec.TVE_ID <> OKC_API.G_MISS_NUM)
201 THEN
202 OPEN okc_tvev_pk_csr(p_rtvv_rec.TVE_ID);
203 FETCH okc_tvev_pk_csr INTO l_okc_tvev_pk;
204 l_row_notfound := okc_tvev_pk_csr%NOTFOUND;
205 CLOSE okc_tvev_pk_csr;
206 IF (l_row_notfound) THEN
207 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
208 RAISE item_not_found_error;
209 END IF;
210 ELSE
211 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
212 x_return_status := OKC_API.G_RET_STS_ERROR;
213 END IF;
214 EXCEPTION
215 WHEN item_not_found_error THEN
216 x_return_status := OKC_API.G_RET_STS_ERROR;
217 WHEN OTHERS THEN
218 -- store SQL error message on message stack for caller
219 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
220 p_msg_name => g_unexpected_error,
221 p_token1 => g_sqlcode_token,
222 p_token1_value => sqlcode,
223 p_token2 => g_col_name_token,
224 p_token2_value => 'TVE_ID',
225 p_token3 => g_sqlerrm_token,
226 p_token3_value => sqlerrm);
227 -- notify caller of an UNEXPECTED error
228 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
229 END Validate_Tve_Id ;
230
231 PROCEDURE Validate_Coe_Id (
232 x_return_status OUT NOCOPY VARCHAR2,
233 p_rtvv_rec IN rtvv_rec_type) IS
234 item_not_found_error EXCEPTION;
235 l_row_notfound BOOLEAN := TRUE;
236 CURSOR okc_coev_pk_csr (p_id IN NUMBER) IS
237 SELECT
238 '1'
239 FROM okc_condition_occurs
240 WHERE okc_condition_occurs.id = p_id;
241 l_okc_coev_pk okc_coev_pk_csr%ROWTYPE;
242 BEGIN
243 x_return_status := OKC_API.G_RET_STS_SUCCESS;
244 IF (p_rtvv_rec.COE_ID IS NOT NULL AND
245 p_rtvv_rec.COE_ID <> OKC_API.G_MISS_NUM)
246 THEN
247 OPEN okc_coev_pk_csr(p_rtvv_rec.COE_ID);
248 FETCH okc_coev_pk_csr INTO l_okc_coev_pk;
249 l_row_notfound := okc_coev_pk_csr%NOTFOUND;
250 CLOSE okc_coev_pk_csr;
251 IF (l_row_notfound) THEN
252 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'COE_ID');
253 RAISE item_not_found_error;
254 END IF;
255 END IF;
256 EXCEPTION
257 WHEN item_not_found_error THEN
258 x_return_status := OKC_API.G_RET_STS_ERROR;
259 WHEN OTHERS THEN
260 -- store SQL error message on message stack for caller
261 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
262 p_msg_name => g_unexpected_error,
263 p_token1 => g_sqlcode_token,
264 p_token1_value => sqlcode,
265 p_token2 => g_col_name_token,
266 p_token2_value => 'COE_ID',
267 p_token3 => g_sqlerrm_token,
268 p_token3_value => sqlerrm);
269 -- notify caller of an UNEXPECTED error
270 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
271 END Validate_Coe_Id ;
272
273 PROCEDURE Validate_Datetime (
274 x_return_status OUT NOCOPY VARCHAR2,
275 p_rtvv_rec IN rtvv_rec_type) IS
276 BEGIN
277 x_return_status := OKC_API.G_RET_STS_SUCCESS;
278 IF (p_rtvv_rec.datetime = OKC_API.G_MISS_DATE OR
279 p_rtvv_rec.datetime IS NULL) THEN
280 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'datetime');
281 x_return_status := OKC_API.G_RET_STS_ERROR;
282 END IF;
283 EXCEPTION
284 WHEN OTHERS THEN
285 -- store SQL error message on message stack for caller
286 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
287 p_msg_name => g_unexpected_error,
288 p_token1 => g_sqlcode_token,
289 p_token1_value => sqlcode,
290 p_token2 => g_col_name_token,
291 p_token2_value => 'DATETIME',
292 p_token3 => g_sqlerrm_token,
293 p_token3_value => sqlerrm);
294 -- notify caller of an UNEXPECTED error
295 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
296 END Validate_Datetime;
297
298 FUNCTION Validate_Attributes (
299 p_rtvv_rec IN rtvv_rec_type
300 ) RETURN VARCHAR2 IS
301 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
302 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
303 BEGIN
304 IF p_rtvv_rec.id = OKC_API.G_MISS_NUM OR
305 p_rtvv_rec.id IS NULL
306 THEN
307 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
308 x_return_status := OKC_API.G_RET_STS_ERROR;
309 ELSIF p_rtvv_rec.object_version_number = OKC_API.G_MISS_NUM OR
310 p_rtvv_rec.object_version_number IS NULL
311 THEN
312 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
313 x_return_status := OKC_API.G_RET_STS_ERROR;
314 END IF;
315 Validate_Tve_Id (l_return_status,
316 p_rtvv_rec);
317 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
318 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
319 x_return_status := l_return_status;
320 RAISE G_EXCEPTION_HALT_VALIDATION;
321 ELSE
322 x_return_status := l_return_status; -- record that there was an error
323 END IF;
324 END IF;
325 IF (p_rtvv_rec.coe_id is NOT NULL AND
326 p_rtvv_rec.coe_id <> OKC_API.G_MISS_NUM) THEN
327 Validate_Coe_Id (l_return_status,
328 p_rtvv_rec);
329 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
330 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
331 x_return_status := l_return_status;
332 RAISE G_EXCEPTION_HALT_VALIDATION;
333 ELSE
334 x_return_status := l_return_status; -- record that there was an error
335 END IF;
336 END IF;
337 END IF;
338 Validate_Datetime (l_return_status,
339 p_rtvv_rec);
340 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
341 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
342 x_return_status := l_return_status;
343 RAISE G_EXCEPTION_HALT_VALIDATION;
344 ELSE
345 x_return_status := l_return_status; -- record that there was an error
346 END IF;
347 END IF;
348 RETURN(x_return_status);
349 EXCEPTION
350 WHEN G_EXCEPTION_HALT_VALIDATION THEN
351 RETURN(x_return_status);
352
353 WHEN OTHERS THEN
354 -- store SQL error message on message stack for caller
355 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
356 p_msg_name => g_unexpected_error,
357 p_token1 => g_sqlcode_token,
358 p_token1_value => sqlcode,
359 p_token2 => g_sqlerrm_token,
360 p_token2_value => sqlerrm);
361
362 -- notify caller of an UNEXPECTED error
363 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
364 RETURN(x_return_status);
365 END Validate_Attributes;
366
367 ---------------------------------------------------------------------------
368 -- PROCEDURE Validate_Record
369 ---------------------------------------------------------------------------
370 ---------------------------------------------------
371 -- Validate_Record for:OKC_RESOLVED_TIMEVALUES_V --
372 ---------------------------------------------------
373 FUNCTION Validate_Record (
374 p_rtvv_rec IN rtvv_rec_type
375 ) RETURN VARCHAR2 IS
376 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
377 ------------------------------------
378 -- FUNCTION validate_foreign_keys --
379 ------------------------------------
380 BEGIN
381 RETURN (l_return_status);
382 END Validate_Record;
383
384 --**** End of Change -------------------------------------------------------
385 ---------------------------------------------------------------------------
386 -- PROCEDURE Migrate
387 ---------------------------------------------------------------------------
388 PROCEDURE migrate (
389 p_from IN rtvv_rec_type,
390 p_to IN OUT NOCOPY rtv_rec_type
391 ) IS
392 BEGIN
393 p_to.id := p_from.id;
394 p_to.tve_id := p_from.tve_id;
395 p_to.coe_id := p_from.coe_id;
396 p_to.datetime := p_from.datetime;
397 p_to.object_version_number := p_from.object_version_number;
398 p_to.created_by := p_from.created_by;
399 p_to.creation_date := p_from.creation_date;
400 p_to.last_updated_by := p_from.last_updated_by;
401 p_to.last_update_date := p_from.last_update_date;
402 p_to.last_update_login := p_from.last_update_login;
403 END migrate;
404 PROCEDURE migrate (
405 p_from IN rtv_rec_type,
406 p_to IN OUT NOCOPY rtvv_rec_type
407 ) IS
408 BEGIN
409 p_to.id := p_from.id;
410 p_to.tve_id := p_from.tve_id;
411 p_to.coe_id := p_from.coe_id;
412 p_to.datetime := p_from.datetime;
413 p_to.object_version_number := p_from.object_version_number;
414 p_to.created_by := p_from.created_by;
415 p_to.creation_date := p_from.creation_date;
416 p_to.last_updated_by := p_from.last_updated_by;
417 p_to.last_update_date := p_from.last_update_date;
418 p_to.last_update_login := p_from.last_update_login;
419 END migrate;
420
421 ---------------------------------------------------------------------------
422 -- PROCEDURE validate_row
423 ---------------------------------------------------------------------------
424 ------------------------------------------------
425 -- validate_row for:OKC_RESOLVED_TIMEVALUES_V --
426 ------------------------------------------------
427 PROCEDURE validate_row(
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_count OUT NOCOPY NUMBER,
432 x_msg_data OUT NOCOPY VARCHAR2,
433 p_rtvv_rec IN rtvv_rec_type) IS
434
435 l_api_version CONSTANT NUMBER := 1;
436 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
437 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
438 l_rtvv_rec rtvv_rec_type := p_rtvv_rec;
439 l_rtv_rec rtv_rec_type;
440 BEGIN
441 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
442 G_PKG_NAME,
443 p_init_msg_list,
444 l_api_version,
445 p_api_version,
446 '_PVT',
447 x_return_status);
448 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
449 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
450 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
451 RAISE OKC_API.G_EXCEPTION_ERROR;
452 END IF;
453 --- Validate all non-missing attributes (Item Level Validation)
454 l_return_status := Validate_Attributes(l_rtvv_rec);
455 --- If any errors happen abort API
456 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
457 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
458 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
459 RAISE OKC_API.G_EXCEPTION_ERROR;
460 END IF;
461 l_return_status := Validate_Record(l_rtvv_rec);
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 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
468 EXCEPTION
469 WHEN OKC_API.G_EXCEPTION_ERROR THEN
470 x_return_status := OKC_API.HANDLE_EXCEPTIONS
471 (
472 l_api_name,
473 G_PKG_NAME,
474 'OKC_API.G_RET_STS_ERROR',
475 x_msg_count,
476 x_msg_data,
477 '_PVT'
478 );
479 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
480 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
481 (
482 l_api_name,
483 G_PKG_NAME,
484 'OKC_API.G_RET_STS_UNEXP_ERROR',
485 x_msg_count,
486 x_msg_data,
487 '_PVT'
488 );
489 WHEN OTHERS THEN
490 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
491 (
492 l_api_name,
493 G_PKG_NAME,
494 'OTHERS',
495 x_msg_count,
496 x_msg_data,
497 '_PVT'
498 );
499 END validate_row;
500 ------------------------------------------
501 -- PL/SQL TBL validate_row for:RTVV_TBL --
502 ------------------------------------------
503 PROCEDURE validate_row(
504 p_api_version IN NUMBER,
505 p_init_msg_list IN VARCHAR2,
506 x_return_status OUT NOCOPY VARCHAR2,
507 x_msg_count OUT NOCOPY NUMBER,
508 x_msg_data OUT NOCOPY VARCHAR2,
509 p_rtvv_tbl IN rtvv_tbl_type) IS
510
511 l_api_version CONSTANT NUMBER := 1;
512 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
513 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
514 i NUMBER := 0;
515 BEGIN
516 OKC_API.init_msg_list(p_init_msg_list);
517 -- Make sure PL/SQL table has records in it before passing
518 IF (p_rtvv_tbl.COUNT > 0) THEN
519 i := p_rtvv_tbl.FIRST;
520 LOOP
521 validate_row (
522 p_api_version => p_api_version,
523 p_init_msg_list => OKC_API.G_FALSE,
524 x_return_status => x_return_status,
525 x_msg_count => x_msg_count,
526 x_msg_data => x_msg_data,
527 p_rtvv_rec => p_rtvv_tbl(i));
528 EXIT WHEN (i = p_rtvv_tbl.LAST);
529 i := p_rtvv_tbl.NEXT(i);
530 END LOOP;
531 END IF;
532 EXCEPTION
533 WHEN OKC_API.G_EXCEPTION_ERROR THEN
534 x_return_status := OKC_API.HANDLE_EXCEPTIONS
535 (
536 l_api_name,
537 G_PKG_NAME,
538 'OKC_API.G_RET_STS_ERROR',
539 x_msg_count,
540 x_msg_data,
541 '_PVT'
542 );
543 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
544 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
545 (
546 l_api_name,
547 G_PKG_NAME,
548 'OKC_API.G_RET_STS_UNEXP_ERROR',
549 x_msg_count,
550 x_msg_data,
551 '_PVT'
552 );
553 WHEN OTHERS THEN
554 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
555 (
556 l_api_name,
557 G_PKG_NAME,
558 'OTHERS',
559 x_msg_count,
560 x_msg_data,
561 '_PVT'
562 );
563 END validate_row;
564
565 ---------------------------------------------------------------------------
566 -- PROCEDURE insert_row
567 ---------------------------------------------------------------------------
568 --------------------------------------------
569 -- insert_row for:OKC_RESOLVED_TIMEVALUES --
570 --------------------------------------------
571 PROCEDURE insert_row(
572 p_init_msg_list IN VARCHAR2,
573 x_return_status OUT NOCOPY VARCHAR2,
574 x_msg_count OUT NOCOPY NUMBER,
575 x_msg_data OUT NOCOPY VARCHAR2,
576 p_rtv_rec IN rtv_rec_type,
577 x_rtv_rec OUT NOCOPY rtv_rec_type) IS
578
579 l_api_version CONSTANT NUMBER := 1;
580 l_api_name CONSTANT VARCHAR2(30) := 'TIMEVALUES_insert_row';
581 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
582 l_rtv_rec rtv_rec_type := p_rtv_rec;
583 l_def_rtv_rec rtv_rec_type;
584 ------------------------------------------------
585 -- Set_Attributes for:OKC_RESOLVED_TIMEVALUES --
586 ------------------------------------------------
587 FUNCTION Set_Attributes (
588 p_rtv_rec IN rtv_rec_type,
589 x_rtv_rec OUT NOCOPY rtv_rec_type
590 ) RETURN VARCHAR2 IS
591 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
592 BEGIN
593 x_rtv_rec := p_rtv_rec;
594 RETURN(l_return_status);
595 END Set_Attributes;
596 BEGIN
597 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
598 p_init_msg_list,
599 '_PVT',
600 x_return_status);
601 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
602 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
603 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
604 RAISE OKC_API.G_EXCEPTION_ERROR;
605 END IF;
606 --- Setting item attributes
607 l_return_status := Set_Attributes(
608 p_rtv_rec, -- IN
609 l_rtv_rec); -- OUT
610 --- If any errors happen abort API
611 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
612 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
613 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
614 RAISE OKC_API.G_EXCEPTION_ERROR;
615 END IF;
616 INSERT INTO OKC_RESOLVED_TIMEVALUES(
617 id,
618 tve_id,
619 coe_id,
620 datetime,
621 object_version_number,
622 created_by,
623 creation_date,
624 last_updated_by,
625 last_update_date,
626 last_update_login)
627 VALUES (
628 l_rtv_rec.id,
629 l_rtv_rec.tve_id,
630 l_rtv_rec.coe_id,
631 l_rtv_rec.datetime,
632 l_rtv_rec.object_version_number,
633 l_rtv_rec.created_by,
634 l_rtv_rec.creation_date,
635 l_rtv_rec.last_updated_by,
636 l_rtv_rec.last_update_date,
637 l_rtv_rec.last_update_login);
638 -- Set OUT values
639 x_rtv_rec := l_rtv_rec;
640 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
641 EXCEPTION
642 WHEN OKC_API.G_EXCEPTION_ERROR THEN
643 x_return_status := OKC_API.HANDLE_EXCEPTIONS
644 (
645 l_api_name,
646 G_PKG_NAME,
647 'OKC_API.G_RET_STS_ERROR',
648 x_msg_count,
649 x_msg_data,
650 '_PVT'
651 );
652 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
653 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
654 (
655 l_api_name,
656 G_PKG_NAME,
657 'OKC_API.G_RET_STS_UNEXP_ERROR',
658 x_msg_count,
659 x_msg_data,
660 '_PVT'
661 );
662 WHEN OTHERS THEN
663 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
664 (
665 l_api_name,
666 G_PKG_NAME,
667 'OTHERS',
668 x_msg_count,
669 x_msg_data,
670 '_PVT'
671 );
672 END insert_row;
673 ----------------------------------------------
674 -- insert_row for:OKC_RESOLVED_TIMEVALUES_V --
675 ----------------------------------------------
676 PROCEDURE insert_row(
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2,
682 p_rtvv_rec IN rtvv_rec_type,
683 x_rtvv_rec OUT NOCOPY rtvv_rec_type) IS
684
685 l_api_version CONSTANT NUMBER := 1;
686 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
687 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
688 l_rtvv_rec rtvv_rec_type;
689 l_def_rtvv_rec rtvv_rec_type;
690 l_rtv_rec rtv_rec_type;
691 lx_rtv_rec rtv_rec_type;
692 -------------------------------
693 -- FUNCTION fill_who_columns --
694 -------------------------------
695 FUNCTION fill_who_columns (
696 p_rtvv_rec IN rtvv_rec_type
697 ) RETURN rtvv_rec_type IS
698 l_rtvv_rec rtvv_rec_type := p_rtvv_rec;
699 BEGIN
700 l_rtvv_rec.CREATION_DATE := SYSDATE;
701 l_rtvv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
702 l_rtvv_rec.LAST_UPDATE_DATE := l_rtvv_rec.CREATION_DATE;
703 l_rtvv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
704 l_rtvv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
705 RETURN(l_rtvv_rec);
706 END fill_who_columns;
707 --------------------------------------------------
708 -- Set_Attributes for:OKC_RESOLVED_TIMEVALUES_V --
709 --------------------------------------------------
710 FUNCTION Set_Attributes (
711 p_rtvv_rec IN rtvv_rec_type,
712 x_rtvv_rec OUT NOCOPY rtvv_rec_type
713 ) RETURN VARCHAR2 IS
714 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
715 BEGIN
716 x_rtvv_rec := p_rtvv_rec;
717 x_rtvv_rec.OBJECT_VERSION_NUMBER := 1;
718 RETURN(l_return_status);
719 END Set_Attributes;
720 BEGIN
721 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
722 G_PKG_NAME,
723 p_init_msg_list,
724 l_api_version,
725 p_api_version,
726 '_PVT',
727 x_return_status);
728 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
729 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
730 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
731 RAISE OKC_API.G_EXCEPTION_ERROR;
732 END IF;
733 l_rtvv_rec := null_out_defaults(p_rtvv_rec);
734 -- Set primary key value
735 l_rtvv_rec.ID := get_seq_id;
736 --- Setting item attributes
737 l_return_status := Set_Attributes(
738 l_rtvv_rec, -- IN
739 l_def_rtvv_rec); -- OUT
740 --- If any errors happen abort API
741 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
742 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
743 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
744 RAISE OKC_API.G_EXCEPTION_ERROR;
745 END IF;
746 l_def_rtvv_rec := fill_who_columns(l_def_rtvv_rec);
747 --- Validate all non-missing attributes (Item Level Validation)
748 l_return_status := Validate_Attributes(l_def_rtvv_rec);
749 --- If any errors happen abort API
750 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
751 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
752 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
753 RAISE OKC_API.G_EXCEPTION_ERROR;
754 END IF;
755 l_return_status := Validate_Record(l_def_rtvv_rec);
756 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
757 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
758 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
759 RAISE OKC_API.G_EXCEPTION_ERROR;
760 END IF;
761 --------------------------------------
762 -- Move VIEW record to "Child" records
763 --------------------------------------
764 migrate(l_def_rtvv_rec, l_rtv_rec);
765 --------------------------------------------
766 -- Call the INSERT_ROW for each child record
767 --------------------------------------------
768 insert_row(
769 p_init_msg_list,
770 x_return_status,
771 x_msg_count,
772 x_msg_data,
773 l_rtv_rec,
774 lx_rtv_rec
775 );
776 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
777 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
778 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
779 RAISE OKC_API.G_EXCEPTION_ERROR;
780 END IF;
781 migrate(lx_rtv_rec, l_def_rtvv_rec);
782 -- Set OUT values
783 x_rtvv_rec := l_def_rtvv_rec;
784 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
785 EXCEPTION
786 WHEN OKC_API.G_EXCEPTION_ERROR THEN
787 x_return_status := OKC_API.HANDLE_EXCEPTIONS
788 (
789 l_api_name,
790 G_PKG_NAME,
791 'OKC_API.G_RET_STS_ERROR',
792 x_msg_count,
793 x_msg_data,
794 '_PVT'
795 );
796 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
797 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
798 (
799 l_api_name,
800 G_PKG_NAME,
801 'OKC_API.G_RET_STS_UNEXP_ERROR',
802 x_msg_count,
803 x_msg_data,
804 '_PVT'
805 );
806 WHEN OTHERS THEN
807 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
808 (
809 l_api_name,
810 G_PKG_NAME,
811 'OTHERS',
812 x_msg_count,
813 x_msg_data,
814 '_PVT'
815 );
816 END insert_row;
817 ----------------------------------------
818 -- PL/SQL TBL insert_row for:RTVV_TBL --
819 ----------------------------------------
820 PROCEDURE insert_row(
821 p_api_version IN NUMBER,
822 p_init_msg_list IN VARCHAR2,
823 x_return_status OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER,
825 x_msg_data OUT NOCOPY VARCHAR2,
826 p_rtvv_tbl IN rtvv_tbl_type,
827 x_rtvv_tbl OUT NOCOPY rtvv_tbl_type) IS
828
829 l_api_version CONSTANT NUMBER := 1;
830 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
831 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
832 i NUMBER := 0;
833 BEGIN
834 OKC_API.init_msg_list(p_init_msg_list);
835 -- Make sure PL/SQL table has records in it before passing
836 IF (p_rtvv_tbl.COUNT > 0) THEN
837 i := p_rtvv_tbl.FIRST;
838 LOOP
839 insert_row (
840 p_api_version => p_api_version,
841 p_init_msg_list => OKC_API.G_FALSE,
842 x_return_status => x_return_status,
843 x_msg_count => x_msg_count,
844 x_msg_data => x_msg_data,
845 p_rtvv_rec => p_rtvv_tbl(i),
846 x_rtvv_rec => x_rtvv_tbl(i));
847 EXIT WHEN (i = p_rtvv_tbl.LAST);
848 i := p_rtvv_tbl.NEXT(i);
849 END LOOP;
850 END IF;
851 EXCEPTION
852 WHEN OKC_API.G_EXCEPTION_ERROR THEN
853 x_return_status := OKC_API.HANDLE_EXCEPTIONS
854 (
855 l_api_name,
856 G_PKG_NAME,
857 'OKC_API.G_RET_STS_ERROR',
858 x_msg_count,
859 x_msg_data,
860 '_PVT'
861 );
862 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
863 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
864 (
865 l_api_name,
866 G_PKG_NAME,
867 'OKC_API.G_RET_STS_UNEXP_ERROR',
868 x_msg_count,
869 x_msg_data,
870 '_PVT'
871 );
872 WHEN OTHERS THEN
873 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
874 (
875 l_api_name,
876 G_PKG_NAME,
877 'OTHERS',
878 x_msg_count,
879 x_msg_data,
880 '_PVT'
881 );
882 END insert_row;
883
884 ---------------------------------------------------------------------------
885 -- PROCEDURE lock_row
886 ---------------------------------------------------------------------------
887 ------------------------------------------
888 -- lock_row for:OKC_RESOLVED_TIMEVALUES --
889 ------------------------------------------
890 PROCEDURE lock_row(
891 p_init_msg_list IN VARCHAR2,
892 x_return_status OUT NOCOPY VARCHAR2,
893 x_msg_count OUT NOCOPY NUMBER,
894 x_msg_data OUT NOCOPY VARCHAR2,
895 p_rtv_rec IN rtv_rec_type) IS
896
897 E_Resource_Busy EXCEPTION;
898 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
899 CURSOR lock_csr (p_rtv_rec IN rtv_rec_type) IS
900 SELECT OBJECT_VERSION_NUMBER
901 FROM OKC_RESOLVED_TIMEVALUES
902 WHERE ID = p_rtv_rec.id
903 AND OBJECT_VERSION_NUMBER = p_rtv_rec.object_version_number
904 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
905
906 CURSOR lchk_csr (p_rtv_rec IN rtv_rec_type) IS
907 SELECT OBJECT_VERSION_NUMBER
908 FROM OKC_RESOLVED_TIMEVALUES
909 WHERE ID = p_rtv_rec.id;
910 l_api_version CONSTANT NUMBER := 1;
911 l_api_name CONSTANT VARCHAR2(30) := 'TIMEVALUES_lock_row';
912 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
913 l_object_version_number OKC_RESOLVED_TIMEVALUES.OBJECT_VERSION_NUMBER%TYPE;
914 lc_object_version_number OKC_RESOLVED_TIMEVALUES.OBJECT_VERSION_NUMBER%TYPE;
915 l_row_notfound BOOLEAN := FALSE;
916 lc_row_notfound BOOLEAN := FALSE;
917 BEGIN
918 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
919 p_init_msg_list,
920 '_PVT',
921 x_return_status);
922 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
923 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
924 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
925 RAISE OKC_API.G_EXCEPTION_ERROR;
926 END IF;
927 BEGIN
928 OPEN lock_csr(p_rtv_rec);
929 FETCH lock_csr INTO l_object_version_number;
930 l_row_notfound := lock_csr%NOTFOUND;
931 CLOSE lock_csr;
932 EXCEPTION
933 WHEN E_Resource_Busy THEN
934 IF (lock_csr%ISOPEN) THEN
935 CLOSE lock_csr;
936 END IF;
937 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
938 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
939 END;
940
941 IF ( l_row_notfound ) THEN
942 OPEN lchk_csr(p_rtv_rec);
943 FETCH lchk_csr INTO lc_object_version_number;
944 lc_row_notfound := lchk_csr%NOTFOUND;
945 CLOSE lchk_csr;
946 END IF;
947 IF (lc_row_notfound) THEN
948 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
949 RAISE OKC_API.G_EXCEPTION_ERROR;
950 ELSIF lc_object_version_number > p_rtv_rec.object_version_number THEN
951 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
952 RAISE OKC_API.G_EXCEPTION_ERROR;
953 ELSIF lc_object_version_number <> p_rtv_rec.object_version_number THEN
954 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
955 RAISE OKC_API.G_EXCEPTION_ERROR;
956 ELSIF lc_object_version_number = -1 THEN
957 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
958 RAISE OKC_API.G_EXCEPTION_ERROR;
959 END IF;
960 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
961 EXCEPTION
962 WHEN OKC_API.G_EXCEPTION_ERROR THEN
963 x_return_status := OKC_API.HANDLE_EXCEPTIONS
964 (
965 l_api_name,
966 G_PKG_NAME,
967 'OKC_API.G_RET_STS_ERROR',
968 x_msg_count,
969 x_msg_data,
970 '_PVT'
971 );
972 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
973 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
974 (
975 l_api_name,
976 G_PKG_NAME,
977 'OKC_API.G_RET_STS_UNEXP_ERROR',
978 x_msg_count,
979 x_msg_data,
980 '_PVT'
981 );
982 WHEN OTHERS THEN
983 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
984 (
985 l_api_name,
986 G_PKG_NAME,
987 'OTHERS',
988 x_msg_count,
989 x_msg_data,
990 '_PVT'
991 );
992 END lock_row;
993 --------------------------------------------
994 -- lock_row for:OKC_RESOLVED_TIMEVALUES_V --
995 --------------------------------------------
996 PROCEDURE lock_row(
997 p_api_version IN NUMBER,
998 p_init_msg_list IN VARCHAR2,
999 x_return_status OUT NOCOPY VARCHAR2,
1000 x_msg_count OUT NOCOPY NUMBER,
1001 x_msg_data OUT NOCOPY VARCHAR2,
1002 p_rtvv_rec IN rtvv_rec_type) IS
1003
1004 l_api_version CONSTANT NUMBER := 1;
1005 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1006 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1007 l_rtv_rec rtv_rec_type;
1008 BEGIN
1009 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1010 G_PKG_NAME,
1011 p_init_msg_list,
1012 l_api_version,
1013 p_api_version,
1014 '_PVT',
1015 x_return_status);
1016 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1017 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1018 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1019 RAISE OKC_API.G_EXCEPTION_ERROR;
1020 END IF;
1021 --------------------------------------
1022 -- Move VIEW record to "Child" records
1023 --------------------------------------
1024 migrate(p_rtvv_rec, l_rtv_rec);
1025 --------------------------------------------
1026 -- Call the LOCK_ROW for each child record
1027 --------------------------------------------
1028 lock_row(
1029 p_init_msg_list,
1030 x_return_status,
1031 x_msg_count,
1032 x_msg_data,
1033 l_rtv_rec
1034 );
1035 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1037 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1038 RAISE OKC_API.G_EXCEPTION_ERROR;
1039 END IF;
1040 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1041 EXCEPTION
1042 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1043 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1044 (
1045 l_api_name,
1046 G_PKG_NAME,
1047 'OKC_API.G_RET_STS_ERROR',
1048 x_msg_count,
1049 x_msg_data,
1050 '_PVT'
1051 );
1052 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1053 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1054 (
1055 l_api_name,
1056 G_PKG_NAME,
1057 'OKC_API.G_RET_STS_UNEXP_ERROR',
1058 x_msg_count,
1059 x_msg_data,
1060 '_PVT'
1061 );
1062 WHEN OTHERS THEN
1063 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1064 (
1065 l_api_name,
1066 G_PKG_NAME,
1067 'OTHERS',
1068 x_msg_count,
1069 x_msg_data,
1070 '_PVT'
1071 );
1072 END lock_row;
1073 --------------------------------------
1074 -- PL/SQL TBL lock_row for:RTVV_TBL --
1075 --------------------------------------
1076 PROCEDURE lock_row(
1077 p_api_version IN NUMBER,
1078 p_init_msg_list IN VARCHAR2,
1079 x_return_status OUT NOCOPY VARCHAR2,
1080 x_msg_count OUT NOCOPY NUMBER,
1081 x_msg_data OUT NOCOPY VARCHAR2,
1082 p_rtvv_tbl IN rtvv_tbl_type) IS
1083
1084 l_api_version CONSTANT NUMBER := 1;
1085 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1086 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1087 i NUMBER := 0;
1088 BEGIN
1089 OKC_API.init_msg_list(p_init_msg_list);
1090 -- Make sure PL/SQL table has records in it before passing
1091 IF (p_rtvv_tbl.COUNT > 0) THEN
1092 i := p_rtvv_tbl.FIRST;
1093 LOOP
1094 lock_row (
1095 p_api_version => p_api_version,
1096 p_init_msg_list => OKC_API.G_FALSE,
1097 x_return_status => x_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 p_rtvv_rec => p_rtvv_tbl(i));
1101 EXIT WHEN (i = p_rtvv_tbl.LAST);
1102 i := p_rtvv_tbl.NEXT(i);
1103 END LOOP;
1104 END IF;
1105 EXCEPTION
1106 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1107 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1108 (
1109 l_api_name,
1110 G_PKG_NAME,
1111 'OKC_API.G_RET_STS_ERROR',
1112 x_msg_count,
1113 x_msg_data,
1114 '_PVT'
1115 );
1116 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1117 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1118 (
1119 l_api_name,
1120 G_PKG_NAME,
1121 'OKC_API.G_RET_STS_UNEXP_ERROR',
1122 x_msg_count,
1123 x_msg_data,
1124 '_PVT'
1125 );
1126 WHEN OTHERS THEN
1127 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1128 (
1129 l_api_name,
1130 G_PKG_NAME,
1131 'OTHERS',
1132 x_msg_count,
1133 x_msg_data,
1134 '_PVT'
1135 );
1136 END lock_row;
1137
1138 ---------------------------------------------------------------------------
1139 -- PROCEDURE update_row
1140 ---------------------------------------------------------------------------
1141 --------------------------------------------
1142 -- update_row for:OKC_RESOLVED_TIMEVALUES --
1143 --------------------------------------------
1144 PROCEDURE update_row(
1145 p_init_msg_list IN VARCHAR2,
1146 x_return_status OUT NOCOPY VARCHAR2,
1147 x_msg_count OUT NOCOPY NUMBER,
1148 x_msg_data OUT NOCOPY VARCHAR2,
1149 p_rtv_rec IN rtv_rec_type,
1150 x_rtv_rec OUT NOCOPY rtv_rec_type) IS
1151
1152 l_api_version CONSTANT NUMBER := 1;
1153 l_api_name CONSTANT VARCHAR2(30) := 'TIMEVALUES_update_row';
1154 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1155 l_rtv_rec rtv_rec_type := p_rtv_rec;
1156 l_def_rtv_rec rtv_rec_type;
1157 l_row_notfound BOOLEAN := TRUE;
1158 ----------------------------------
1159 -- FUNCTION populate_new_record --
1160 ----------------------------------
1161 FUNCTION populate_new_record (
1162 p_rtv_rec IN rtv_rec_type,
1163 x_rtv_rec OUT NOCOPY rtv_rec_type
1164 ) RETURN VARCHAR2 IS
1165 l_rtv_rec rtv_rec_type;
1166 l_row_notfound BOOLEAN := TRUE;
1167 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1168 BEGIN
1169 x_rtv_rec := p_rtv_rec;
1170 -- Get current database values
1171 l_rtv_rec := get_rec(p_rtv_rec, l_row_notfound);
1172 IF (l_row_notfound) THEN
1173 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1174 END IF;
1175 IF (x_rtv_rec.id = OKC_API.G_MISS_NUM)
1176 THEN
1177 x_rtv_rec.id := l_rtv_rec.id;
1178 END IF;
1179 IF (x_rtv_rec.tve_id = OKC_API.G_MISS_NUM)
1180 THEN
1181 x_rtv_rec.tve_id := l_rtv_rec.tve_id;
1182 END IF;
1183 IF (x_rtv_rec.coe_id = OKC_API.G_MISS_NUM)
1184 THEN
1185 x_rtv_rec.coe_id := l_rtv_rec.coe_id;
1186 END IF;
1187 IF (x_rtv_rec.datetime = OKC_API.G_MISS_DATE)
1188 THEN
1189 x_rtv_rec.datetime := l_rtv_rec.datetime;
1190 END IF;
1191 IF (x_rtv_rec.object_version_number = OKC_API.G_MISS_NUM)
1192 THEN
1193 x_rtv_rec.object_version_number := l_rtv_rec.object_version_number;
1194 END IF;
1195 IF (x_rtv_rec.created_by = OKC_API.G_MISS_NUM)
1196 THEN
1197 x_rtv_rec.created_by := l_rtv_rec.created_by;
1198 END IF;
1199 IF (x_rtv_rec.creation_date = OKC_API.G_MISS_DATE)
1200 THEN
1201 x_rtv_rec.creation_date := l_rtv_rec.creation_date;
1202 END IF;
1203 IF (x_rtv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1204 THEN
1205 x_rtv_rec.last_updated_by := l_rtv_rec.last_updated_by;
1206 END IF;
1207 IF (x_rtv_rec.last_update_date = OKC_API.G_MISS_DATE)
1208 THEN
1209 x_rtv_rec.last_update_date := l_rtv_rec.last_update_date;
1210 END IF;
1211 IF (x_rtv_rec.last_update_login = OKC_API.G_MISS_NUM)
1212 THEN
1213 x_rtv_rec.last_update_login := l_rtv_rec.last_update_login;
1214 END IF;
1215 RETURN(l_return_status);
1216 END populate_new_record;
1217 ------------------------------------------------
1218 -- Set_Attributes for:OKC_RESOLVED_TIMEVALUES --
1219 ------------------------------------------------
1220 FUNCTION Set_Attributes (
1221 p_rtv_rec IN rtv_rec_type,
1222 x_rtv_rec OUT NOCOPY rtv_rec_type
1223 ) RETURN VARCHAR2 IS
1224 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1225 BEGIN
1226 x_rtv_rec := p_rtv_rec;
1227 RETURN(l_return_status);
1228 END Set_Attributes;
1229 BEGIN
1230 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1231 p_init_msg_list,
1232 '_PVT',
1233 x_return_status);
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 --- Setting item attributes
1240 l_return_status := Set_Attributes(
1241 p_rtv_rec, -- IN
1242 l_rtv_rec); -- OUT
1243 --- If any errors happen abort API
1244 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1245 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1246 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1247 RAISE OKC_API.G_EXCEPTION_ERROR;
1248 END IF;
1249 l_return_status := populate_new_record(l_rtv_rec, l_def_rtv_rec);
1250 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1251 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1252 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1253 RAISE OKC_API.G_EXCEPTION_ERROR;
1254 END IF;
1255 UPDATE OKC_RESOLVED_TIMEVALUES
1256 SET TVE_ID = l_def_rtv_rec.tve_id,
1257 COE_ID = l_def_rtv_rec.coe_id,
1258 DATETIME = l_def_rtv_rec.datetime,
1259 OBJECT_VERSION_NUMBER = l_def_rtv_rec.object_version_number,
1260 CREATED_BY = l_def_rtv_rec.created_by,
1261 CREATION_DATE = l_def_rtv_rec.creation_date,
1262 LAST_UPDATED_BY = l_def_rtv_rec.last_updated_by,
1263 LAST_UPDATE_DATE = l_def_rtv_rec.last_update_date,
1264 LAST_UPDATE_LOGIN = l_def_rtv_rec.last_update_login
1265 WHERE ID = l_def_rtv_rec.id;
1266
1267 x_rtv_rec := l_def_rtv_rec;
1268 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1269 EXCEPTION
1270 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1271 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1272 (
1273 l_api_name,
1274 G_PKG_NAME,
1275 'OKC_API.G_RET_STS_ERROR',
1276 x_msg_count,
1277 x_msg_data,
1278 '_PVT'
1279 );
1280 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1281 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1282 (
1283 l_api_name,
1284 G_PKG_NAME,
1285 'OKC_API.G_RET_STS_UNEXP_ERROR',
1286 x_msg_count,
1287 x_msg_data,
1288 '_PVT'
1289 );
1290 WHEN OTHERS THEN
1291 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1292 (
1293 l_api_name,
1294 G_PKG_NAME,
1295 'OTHERS',
1296 x_msg_count,
1297 x_msg_data,
1298 '_PVT'
1299 );
1300 END update_row;
1301 ----------------------------------------------
1302 -- update_row for:OKC_RESOLVED_TIMEVALUES_V --
1303 ----------------------------------------------
1304 PROCEDURE update_row(
1305 p_api_version IN NUMBER,
1306 p_init_msg_list IN VARCHAR2,
1307 x_return_status OUT NOCOPY VARCHAR2,
1308 x_msg_count OUT NOCOPY NUMBER,
1309 x_msg_data OUT NOCOPY VARCHAR2,
1310 p_rtvv_rec IN rtvv_rec_type,
1311 x_rtvv_rec OUT NOCOPY rtvv_rec_type) IS
1312
1313 l_api_version CONSTANT NUMBER := 1;
1314 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1315 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1316 l_rtvv_rec rtvv_rec_type := p_rtvv_rec;
1317 l_def_rtvv_rec rtvv_rec_type;
1318 l_rtv_rec rtv_rec_type;
1319 lx_rtv_rec rtv_rec_type;
1320 -------------------------------
1321 -- FUNCTION fill_who_columns --
1322 -------------------------------
1323 FUNCTION fill_who_columns (
1324 p_rtvv_rec IN rtvv_rec_type
1325 ) RETURN rtvv_rec_type IS
1326 l_rtvv_rec rtvv_rec_type := p_rtvv_rec;
1327 BEGIN
1328 l_rtvv_rec.LAST_UPDATE_DATE := SYSDATE;
1329 l_rtvv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1330 l_rtvv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1331 RETURN(l_rtvv_rec);
1332 END fill_who_columns;
1333 ----------------------------------
1334 -- FUNCTION populate_new_record --
1335 ----------------------------------
1336 FUNCTION populate_new_record (
1337 p_rtvv_rec IN rtvv_rec_type,
1338 x_rtvv_rec OUT NOCOPY rtvv_rec_type
1339 ) RETURN VARCHAR2 IS
1340 l_rtvv_rec rtvv_rec_type;
1341 l_row_notfound BOOLEAN := TRUE;
1342 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1343 BEGIN
1344 x_rtvv_rec := p_rtvv_rec;
1345 -- Get current database values
1346 l_rtvv_rec := get_rec(p_rtvv_rec, l_row_notfound);
1347 IF (l_row_notfound) THEN
1348 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1349 END IF;
1350 IF (x_rtvv_rec.id = OKC_API.G_MISS_NUM)
1351 THEN
1352 x_rtvv_rec.id := l_rtvv_rec.id;
1353 END IF;
1354 IF (x_rtvv_rec.object_version_number = OKC_API.G_MISS_NUM)
1355 THEN
1356 x_rtvv_rec.object_version_number := l_rtvv_rec.object_version_number;
1357 END IF;
1358 IF (x_rtvv_rec.tve_id = OKC_API.G_MISS_NUM)
1359 THEN
1360 x_rtvv_rec.tve_id := l_rtvv_rec.tve_id;
1361 END IF;
1362 IF (x_rtvv_rec.coe_id = OKC_API.G_MISS_NUM)
1363 THEN
1364 x_rtvv_rec.coe_id := l_rtvv_rec.coe_id;
1365 END IF;
1366 IF (x_rtvv_rec.datetime = OKC_API.G_MISS_DATE)
1367 THEN
1368 x_rtvv_rec.datetime := l_rtvv_rec.datetime;
1369 END IF;
1370 IF (x_rtvv_rec.created_by = OKC_API.G_MISS_NUM)
1371 THEN
1372 x_rtvv_rec.created_by := l_rtvv_rec.created_by;
1373 END IF;
1374 IF (x_rtvv_rec.creation_date = OKC_API.G_MISS_DATE)
1375 THEN
1376 x_rtvv_rec.creation_date := l_rtvv_rec.creation_date;
1377 END IF;
1378 IF (x_rtvv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1379 THEN
1380 x_rtvv_rec.last_updated_by := l_rtvv_rec.last_updated_by;
1381 END IF;
1382 IF (x_rtvv_rec.last_update_date = OKC_API.G_MISS_DATE)
1383 THEN
1384 x_rtvv_rec.last_update_date := l_rtvv_rec.last_update_date;
1385 END IF;
1386 IF (x_rtvv_rec.last_update_login = OKC_API.G_MISS_NUM)
1387 THEN
1388 x_rtvv_rec.last_update_login := l_rtvv_rec.last_update_login;
1389 END IF;
1390 RETURN(l_return_status);
1391 END populate_new_record;
1392 --------------------------------------------------
1393 -- Set_Attributes for:OKC_RESOLVED_TIMEVALUES_V --
1394 --------------------------------------------------
1395 FUNCTION Set_Attributes (
1396 p_rtvv_rec IN rtvv_rec_type,
1397 x_rtvv_rec OUT NOCOPY rtvv_rec_type
1398 ) RETURN VARCHAR2 IS
1399 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1400 BEGIN
1401 x_rtvv_rec := p_rtvv_rec;
1402 x_rtvv_rec.OBJECT_VERSION_NUMBER := NVL(x_rtvv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1403 RETURN(l_return_status);
1404 END Set_Attributes;
1405 BEGIN
1406 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1407 G_PKG_NAME,
1408 p_init_msg_list,
1409 l_api_version,
1410 p_api_version,
1411 '_PVT',
1412 x_return_status);
1413 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1414 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1415 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1416 RAISE OKC_API.G_EXCEPTION_ERROR;
1417 END IF;
1418 --- Setting item attributes
1419 l_return_status := Set_Attributes(
1420 p_rtvv_rec, -- IN
1421 l_rtvv_rec); -- OUT
1422 --- If any errors happen abort API
1423 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1424 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1425 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1426 RAISE OKC_API.G_EXCEPTION_ERROR;
1427 END IF;
1428 l_return_status := populate_new_record(l_rtvv_rec, l_def_rtvv_rec);
1429 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1430 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1431 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1432 RAISE OKC_API.G_EXCEPTION_ERROR;
1433 END IF;
1434 l_def_rtvv_rec := fill_who_columns(l_def_rtvv_rec);
1435 --- Validate all non-missing attributes (Item Level Validation)
1436 l_return_status := Validate_Attributes(l_def_rtvv_rec);
1437 --- If any errors happen abort API
1438 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1439 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1440 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1441 RAISE OKC_API.G_EXCEPTION_ERROR;
1442 END IF;
1443 l_return_status := Validate_Record(l_def_rtvv_rec);
1444 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1445 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1446 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1447 RAISE OKC_API.G_EXCEPTION_ERROR;
1448 END IF;
1449
1450 --------------------------------------
1451 -- Move VIEW record to "Child" records
1452 --------------------------------------
1453 migrate(l_def_rtvv_rec, l_rtv_rec);
1454 --------------------------------------------
1455 -- Call the UPDATE_ROW for each child record
1456 --------------------------------------------
1457 update_row(
1458 p_init_msg_list,
1459 x_return_status,
1460 x_msg_count,
1461 x_msg_data,
1462 l_rtv_rec,
1463 lx_rtv_rec
1464 );
1465 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1466 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1467 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1468 RAISE OKC_API.G_EXCEPTION_ERROR;
1469 END IF;
1470 migrate(lx_rtv_rec, l_def_rtvv_rec);
1471 x_rtvv_rec := l_def_rtvv_rec;
1472 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1473 EXCEPTION
1474 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1475 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1476 (
1477 l_api_name,
1478 G_PKG_NAME,
1479 'OKC_API.G_RET_STS_ERROR',
1480 x_msg_count,
1481 x_msg_data,
1482 '_PVT'
1483 );
1484 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1485 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1486 (
1487 l_api_name,
1488 G_PKG_NAME,
1489 'OKC_API.G_RET_STS_UNEXP_ERROR',
1490 x_msg_count,
1491 x_msg_data,
1492 '_PVT'
1493 );
1494 WHEN OTHERS THEN
1495 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1496 (
1497 l_api_name,
1498 G_PKG_NAME,
1499 'OTHERS',
1500 x_msg_count,
1501 x_msg_data,
1502 '_PVT'
1503 );
1504 END update_row;
1505 ----------------------------------------
1506 -- PL/SQL TBL update_row for:RTVV_TBL --
1507 ----------------------------------------
1508 PROCEDURE update_row(
1509 p_api_version IN NUMBER,
1510 p_init_msg_list IN VARCHAR2,
1511 x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2,
1514 p_rtvv_tbl IN rtvv_tbl_type,
1515 x_rtvv_tbl OUT NOCOPY rtvv_tbl_type) IS
1516
1517 l_api_version CONSTANT NUMBER := 1;
1518 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1519 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1520 i NUMBER := 0;
1521 BEGIN
1522 OKC_API.init_msg_list(p_init_msg_list);
1523 -- Make sure PL/SQL table has records in it before passing
1524 IF (p_rtvv_tbl.COUNT > 0) THEN
1525 i := p_rtvv_tbl.FIRST;
1526 LOOP
1527 update_row (
1528 p_api_version => p_api_version,
1529 p_init_msg_list => OKC_API.G_FALSE,
1530 x_return_status => x_return_status,
1531 x_msg_count => x_msg_count,
1532 x_msg_data => x_msg_data,
1533 p_rtvv_rec => p_rtvv_tbl(i),
1534 x_rtvv_rec => x_rtvv_tbl(i));
1535 EXIT WHEN (i = p_rtvv_tbl.LAST);
1536 i := p_rtvv_tbl.NEXT(i);
1537 END LOOP;
1538 END IF;
1539 EXCEPTION
1540 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1541 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1542 (
1543 l_api_name,
1544 G_PKG_NAME,
1545 'OKC_API.G_RET_STS_ERROR',
1546 x_msg_count,
1547 x_msg_data,
1548 '_PVT'
1549 );
1550 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1551 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1552 (
1553 l_api_name,
1554 G_PKG_NAME,
1555 'OKC_API.G_RET_STS_UNEXP_ERROR',
1556 x_msg_count,
1557 x_msg_data,
1558 '_PVT'
1559 );
1560 WHEN OTHERS THEN
1561 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1562 (
1563 l_api_name,
1564 G_PKG_NAME,
1565 'OTHERS',
1566 x_msg_count,
1567 x_msg_data,
1568 '_PVT'
1569 );
1570 END update_row;
1571
1572 ---------------------------------------------------------------------------
1573 -- PROCEDURE delete_row
1574 ---------------------------------------------------------------------------
1575 --------------------------------------------
1576 -- delete_row for:OKC_RESOLVED_TIMEVALUES --
1577 --------------------------------------------
1578 PROCEDURE delete_row(
1579 p_init_msg_list IN VARCHAR2,
1580 x_return_status OUT NOCOPY VARCHAR2,
1581 x_msg_count OUT NOCOPY NUMBER,
1582 x_msg_data OUT NOCOPY VARCHAR2,
1583 p_rtv_rec IN rtv_rec_type) IS
1584
1585 l_api_version CONSTANT NUMBER := 1;
1586 l_api_name CONSTANT VARCHAR2(30) := 'TIMEVALUES_delete_row';
1587 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1588 l_rtv_rec rtv_rec_type:= p_rtv_rec;
1589 l_row_notfound BOOLEAN := TRUE;
1590 BEGIN
1591 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1592 p_init_msg_list,
1593 '_PVT',
1594 x_return_status);
1595 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1596 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1597 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1598 RAISE OKC_API.G_EXCEPTION_ERROR;
1599 END IF;
1600 DELETE FROM OKC_RESOLVED_TIMEVALUES
1601 WHERE ID = l_rtv_rec.id;
1602
1603 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1604 EXCEPTION
1605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1607 (
1608 l_api_name,
1609 G_PKG_NAME,
1610 'OKC_API.G_RET_STS_ERROR',
1611 x_msg_count,
1612 x_msg_data,
1613 '_PVT'
1614 );
1615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1616 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1617 (
1618 l_api_name,
1619 G_PKG_NAME,
1620 'OKC_API.G_RET_STS_UNEXP_ERROR',
1621 x_msg_count,
1622 x_msg_data,
1623 '_PVT'
1624 );
1625 WHEN OTHERS THEN
1626 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1627 (
1628 l_api_name,
1629 G_PKG_NAME,
1630 'OTHERS',
1631 x_msg_count,
1632 x_msg_data,
1633 '_PVT'
1634 );
1635 END delete_row;
1636 ----------------------------------------------
1637 -- delete_row for:OKC_RESOLVED_TIMEVALUES_V --
1638 ----------------------------------------------
1639 PROCEDURE delete_row(
1640 p_api_version IN NUMBER,
1641 p_init_msg_list IN VARCHAR2,
1642 x_return_status OUT NOCOPY VARCHAR2,
1643 x_msg_count OUT NOCOPY NUMBER,
1644 x_msg_data OUT NOCOPY VARCHAR2,
1645 p_rtvv_rec IN rtvv_rec_type) IS
1646
1647 l_api_version CONSTANT NUMBER := 1;
1648 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1649 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1650 l_rtvv_rec rtvv_rec_type := p_rtvv_rec;
1651 l_rtv_rec rtv_rec_type;
1652 BEGIN
1653 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1654 G_PKG_NAME,
1655 p_init_msg_list,
1656 l_api_version,
1657 p_api_version,
1658 '_PVT',
1659 x_return_status);
1660 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1661 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1662 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1663 RAISE OKC_API.G_EXCEPTION_ERROR;
1664 END IF;
1665 --------------------------------------
1666 -- Move VIEW record to "Child" records
1667 --------------------------------------
1668 migrate(l_rtvv_rec, l_rtv_rec);
1669 --------------------------------------------
1670 -- Call the DELETE_ROW for each child record
1671 --------------------------------------------
1672 delete_row(
1673 p_init_msg_list,
1674 x_return_status,
1675 x_msg_count,
1676 x_msg_data,
1677 l_rtv_rec
1678 );
1679 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1680 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1681 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1682 RAISE OKC_API.G_EXCEPTION_ERROR;
1683 END IF;
1684 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1685 EXCEPTION
1686 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1687 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1688 (
1689 l_api_name,
1690 G_PKG_NAME,
1691 'OKC_API.G_RET_STS_ERROR',
1692 x_msg_count,
1693 x_msg_data,
1694 '_PVT'
1695 );
1696 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1697 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1698 (
1699 l_api_name,
1700 G_PKG_NAME,
1701 'OKC_API.G_RET_STS_UNEXP_ERROR',
1702 x_msg_count,
1703 x_msg_data,
1704 '_PVT'
1705 );
1706 WHEN OTHERS THEN
1707 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1708 (
1709 l_api_name,
1710 G_PKG_NAME,
1711 'OTHERS',
1712 x_msg_count,
1713 x_msg_data,
1714 '_PVT'
1715 );
1716 END delete_row;
1717 ----------------------------------------
1718 -- PL/SQL TBL delete_row for:RTVV_TBL --
1719 ----------------------------------------
1720 PROCEDURE delete_row(
1721 p_api_version IN NUMBER,
1722 p_init_msg_list IN VARCHAR2,
1723 x_return_status OUT NOCOPY VARCHAR2,
1724 x_msg_count OUT NOCOPY NUMBER,
1725 x_msg_data OUT NOCOPY VARCHAR2,
1726 p_rtvv_tbl IN rtvv_tbl_type) IS
1727
1728 l_api_version CONSTANT NUMBER := 1;
1729 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1730 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1731 i NUMBER := 0;
1732 BEGIN
1733 OKC_API.init_msg_list(p_init_msg_list);
1734 -- Make sure PL/SQL table has records in it before passing
1735 IF (p_rtvv_tbl.COUNT > 0) THEN
1736 i := p_rtvv_tbl.FIRST;
1737 LOOP
1738 delete_row (
1739 p_api_version => p_api_version,
1740 p_init_msg_list => OKC_API.G_FALSE,
1741 x_return_status => x_return_status,
1742 x_msg_count => x_msg_count,
1743 x_msg_data => x_msg_data,
1744 p_rtvv_rec => p_rtvv_tbl(i));
1745 EXIT WHEN (i = p_rtvv_tbl.LAST);
1746 i := p_rtvv_tbl.NEXT(i);
1747 END LOOP;
1748 END IF;
1749 EXCEPTION
1750 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1751 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1752 (
1753 l_api_name,
1754 G_PKG_NAME,
1755 'OKC_API.G_RET_STS_ERROR',
1756 x_msg_count,
1757 x_msg_data,
1758 '_PVT'
1759 );
1760 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1761 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1762 (
1763 l_api_name,
1764 G_PKG_NAME,
1765 'OKC_API.G_RET_STS_UNEXP_ERROR',
1766 x_msg_count,
1767 x_msg_data,
1768 '_PVT'
1769 );
1770 WHEN OTHERS THEN
1771 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1772 (
1773 l_api_name,
1774 G_PKG_NAME,
1775 'OTHERS',
1776 x_msg_count,
1777 x_msg_data,
1778 '_PVT'
1779 );
1780 END delete_row;
1781 -----------------------------------------------------------
1782 ---- ******** Add View for registering the view (which will be used for check length)
1783 -----------------------------------------------------------
1784 BEGIN
1785 -- call OKC_UTIL.ADD_VIEW to prepare the PL/SQL table to hold columns of view
1786 OKC_UTIL.ADD_VIEW(x_return_status => g_return_status,
1787 p_view_name => 'OKC_RESOLVED_TIMEVALUES_V');
1788 END OKC_RTV_PVT;