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