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