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