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