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