[Home] [Help]
PACKAGE BODY: APPS.OKL_VCR_PVT
Source
1 PACKAGE BODY OKL_VCR_PVT AS
2 /* $Header: OKLSVCRB.pls 120.0 2005/07/28 11:37:24 sjalasut noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_VP_CHANGE_REQUESTS_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_vcrv_rec IN vcrv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN vcrv_rec_type IS
100 CURSOR okl_vp_change_reque1 (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 CHANGE_REQUEST_NUMBER,
105 CHR_ID,
106 CHANGE_TYPE_CODE,
107 STATUS_CODE,
108 REQUEST_DATE,
109 EFFECTIVE_DATE,
110 APPROVED_DATE,
111 REJECTED_DATE,
112 INEFFECTIVE_DATE,
113 APPLIED_DATE,
114 ATTRIBUTE_CATEGORY,
115 ATTRIBUTE1,
116 ATTRIBUTE2,
117 ATTRIBUTE3,
118 ATTRIBUTE4,
119 ATTRIBUTE5,
120 ATTRIBUTE6,
121 ATTRIBUTE7,
122 ATTRIBUTE8,
123 ATTRIBUTE9,
124 ATTRIBUTE10,
125 ATTRIBUTE11,
126 ATTRIBUTE12,
127 ATTRIBUTE13,
128 ATTRIBUTE14,
129 ATTRIBUTE15,
130 REQUEST_ID,
131 PROGRAM_APPLICATION_ID,
132 PROGRAM_ID,
133 PROGRAM_UPDATE_DATE,
134 CREATED_BY,
135 CREATION_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_DATE,
138 LAST_UPDATE_LOGIN
139 FROM Okl_Vp_Change_Requests_V
140 WHERE okl_vp_change_requests_v.id = p_id;
141 l_okl_vp_change_requests_v_pk okl_vp_change_reque1%ROWTYPE;
142 l_vcrv_rec vcrv_rec_type;
143 BEGIN
144 x_no_data_found := TRUE;
145 -- Get current database values
146 OPEN okl_vp_change_reque1 (p_vcrv_rec.id);
147 FETCH okl_vp_change_reque1 INTO
148 l_vcrv_rec.id,
149 l_vcrv_rec.object_version_number,
150 l_vcrv_rec.change_request_number,
151 l_vcrv_rec.chr_id,
152 l_vcrv_rec.change_type_code,
153 l_vcrv_rec.status_code,
154 l_vcrv_rec.request_date,
155 l_vcrv_rec.effective_date,
156 l_vcrv_rec.approved_date,
157 l_vcrv_rec.rejected_date,
158 l_vcrv_rec.ineffective_date,
159 l_vcrv_rec.applied_date,
160 l_vcrv_rec.attribute_category,
161 l_vcrv_rec.attribute1,
162 l_vcrv_rec.attribute2,
163 l_vcrv_rec.attribute3,
164 l_vcrv_rec.attribute4,
165 l_vcrv_rec.attribute5,
166 l_vcrv_rec.attribute6,
167 l_vcrv_rec.attribute7,
168 l_vcrv_rec.attribute8,
169 l_vcrv_rec.attribute9,
170 l_vcrv_rec.attribute10,
171 l_vcrv_rec.attribute11,
172 l_vcrv_rec.attribute12,
173 l_vcrv_rec.attribute13,
174 l_vcrv_rec.attribute14,
175 l_vcrv_rec.attribute15,
176 l_vcrv_rec.request_id,
177 l_vcrv_rec.program_application_id,
178 l_vcrv_rec.program_id,
179 l_vcrv_rec.program_update_date,
180 l_vcrv_rec.created_by,
181 l_vcrv_rec.creation_date,
182 l_vcrv_rec.last_updated_by,
183 l_vcrv_rec.last_update_date,
184 l_vcrv_rec.last_update_login;
185 x_no_data_found := okl_vp_change_reque1%NOTFOUND;
186 CLOSE okl_vp_change_reque1;
187 RETURN(l_vcrv_rec);
188 END get_rec;
189
190 ------------------------------------------------------------------
191 -- This version of get_rec sets error messages if no data found --
192 ------------------------------------------------------------------
193 FUNCTION get_rec (
194 p_vcrv_rec IN vcrv_rec_type,
195 x_return_status OUT NOCOPY VARCHAR2
196 ) RETURN vcrv_rec_type IS
197 l_vcrv_rec vcrv_rec_type;
198 l_row_notfound BOOLEAN := TRUE;
199 BEGIN
200 x_return_status := OKL_API.G_RET_STS_SUCCESS;
201 l_vcrv_rec := get_rec(p_vcrv_rec, l_row_notfound);
202 IF (l_row_notfound) THEN
203 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
204 x_return_status := OKL_API.G_RET_STS_ERROR;
205 END IF;
206 RETURN(l_vcrv_rec);
207 END get_rec;
208 -----------------------------------------------------------
209 -- So we don't have to pass an "l_row_notfound" variable --
210 -----------------------------------------------------------
211 FUNCTION get_rec (
212 p_vcrv_rec IN vcrv_rec_type
213 ) RETURN vcrv_rec_type IS
214 l_row_not_found BOOLEAN := TRUE;
215 BEGIN
216 RETURN(get_rec(p_vcrv_rec, l_row_not_found));
217 END get_rec;
218 ---------------------------------------------------------------------------
219 -- FUNCTION get_rec for: OKL_VP_CHANGE_REQUESTS
220 ---------------------------------------------------------------------------
221 FUNCTION get_rec (
222 p_vcr_rec IN vcr_rec_type,
223 x_no_data_found OUT NOCOPY BOOLEAN
224 ) RETURN vcr_rec_type IS
225 CURSOR okl_vp_change_requests_pk_csr (p_id IN NUMBER) IS
226 SELECT
227 ID,
228 OBJECT_VERSION_NUMBER,
229 CHANGE_REQUEST_NUMBER,
230 CHR_ID,
231 CHANGE_TYPE_CODE,
232 STATUS_CODE,
233 REQUEST_DATE,
234 EFFECTIVE_DATE,
235 APPROVED_DATE,
236 REJECTED_DATE,
237 INEFFECTIVE_DATE,
238 APPLIED_DATE,
239 ATTRIBUTE_CATEGORY,
240 ATTRIBUTE1,
241 ATTRIBUTE2,
242 ATTRIBUTE3,
243 ATTRIBUTE4,
244 ATTRIBUTE5,
245 ATTRIBUTE6,
246 ATTRIBUTE7,
247 ATTRIBUTE8,
248 ATTRIBUTE9,
249 ATTRIBUTE10,
250 ATTRIBUTE11,
251 ATTRIBUTE12,
252 ATTRIBUTE13,
253 ATTRIBUTE14,
254 ATTRIBUTE15,
255 REQUEST_ID,
256 PROGRAM_APPLICATION_ID,
257 PROGRAM_ID,
258 PROGRAM_UPDATE_DATE,
259 CREATED_BY,
260 CREATION_DATE,
261 LAST_UPDATED_BY,
262 LAST_UPDATE_DATE,
263 LAST_UPDATE_LOGIN
264 FROM Okl_Vp_Change_Requests
265 WHERE okl_vp_change_requests.id = p_id;
266 l_okl_vp_change_requests_pk okl_vp_change_requests_pk_csr%ROWTYPE;
267 l_vcr_rec vcr_rec_type;
268 BEGIN
269 x_no_data_found := TRUE;
270 -- Get current database values
271 OPEN okl_vp_change_requests_pk_csr (p_vcr_rec.id);
272 FETCH okl_vp_change_requests_pk_csr INTO
273 l_vcr_rec.id,
274 l_vcr_rec.object_version_number,
275 l_vcr_rec.change_request_number,
276 l_vcr_rec.chr_id,
277 l_vcr_rec.change_type_code,
278 l_vcr_rec.status_code,
279 l_vcr_rec.request_date,
280 l_vcr_rec.effective_date,
281 l_vcr_rec.approved_date,
282 l_vcr_rec.rejected_date,
283 l_vcr_rec.ineffective_date,
284 l_vcr_rec.applied_date,
285 l_vcr_rec.attribute_category,
286 l_vcr_rec.attribute1,
287 l_vcr_rec.attribute2,
288 l_vcr_rec.attribute3,
289 l_vcr_rec.attribute4,
290 l_vcr_rec.attribute5,
291 l_vcr_rec.attribute6,
292 l_vcr_rec.attribute7,
293 l_vcr_rec.attribute8,
294 l_vcr_rec.attribute9,
295 l_vcr_rec.attribute10,
296 l_vcr_rec.attribute11,
297 l_vcr_rec.attribute12,
298 l_vcr_rec.attribute13,
299 l_vcr_rec.attribute14,
300 l_vcr_rec.attribute15,
301 l_vcr_rec.request_id,
302 l_vcr_rec.program_application_id,
303 l_vcr_rec.program_id,
304 l_vcr_rec.program_update_date,
305 l_vcr_rec.created_by,
306 l_vcr_rec.creation_date,
307 l_vcr_rec.last_updated_by,
308 l_vcr_rec.last_update_date,
309 l_vcr_rec.last_update_login;
310 x_no_data_found := okl_vp_change_requests_pk_csr%NOTFOUND;
311 CLOSE okl_vp_change_requests_pk_csr;
312 RETURN(l_vcr_rec);
313 END get_rec;
314
315 ------------------------------------------------------------------
316 -- This version of get_rec sets error messages if no data found --
317 ------------------------------------------------------------------
318 FUNCTION get_rec (
319 p_vcr_rec IN vcr_rec_type,
320 x_return_status OUT NOCOPY VARCHAR2
321 ) RETURN vcr_rec_type IS
322 l_vcr_rec vcr_rec_type;
323 l_row_notfound BOOLEAN := TRUE;
324 BEGIN
325 x_return_status := OKL_API.G_RET_STS_SUCCESS;
326 l_vcr_rec := get_rec(p_vcr_rec, l_row_notfound);
327 IF (l_row_notfound) THEN
328 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
329 x_return_status := OKL_API.G_RET_STS_ERROR;
330 END IF;
331 RETURN(l_vcr_rec);
332 END get_rec;
333 -----------------------------------------------------------
334 -- So we don't have to pass an "l_row_notfound" variable --
335 -----------------------------------------------------------
336 FUNCTION get_rec (
337 p_vcr_rec IN vcr_rec_type
338 ) RETURN vcr_rec_type IS
339 l_row_not_found BOOLEAN := TRUE;
340 BEGIN
341 RETURN(get_rec(p_vcr_rec, l_row_not_found));
342 END get_rec;
343 ---------------------------------------------------------------------------
344 -- FUNCTION null_out_defaults for: OKL_VP_CHANGE_REQUESTS_V
345 ---------------------------------------------------------------------------
346 FUNCTION null_out_defaults (
347 p_vcrv_rec IN vcrv_rec_type
348 ) RETURN vcrv_rec_type IS
349 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
350 BEGIN
351 IF (l_vcrv_rec.id = OKL_API.G_MISS_NUM ) THEN
352 l_vcrv_rec.id := NULL;
353 END IF;
354 IF (l_vcrv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
355 l_vcrv_rec.object_version_number := NULL;
356 END IF;
357 IF (l_vcrv_rec.change_request_number = OKL_API.G_MISS_NUM ) THEN
358 l_vcrv_rec.change_request_number := NULL;
359 END IF;
360 IF (l_vcrv_rec.chr_id = OKL_API.G_MISS_NUM ) THEN
361 l_vcrv_rec.chr_id := NULL;
362 END IF;
363 IF (l_vcrv_rec.change_type_code = OKL_API.G_MISS_CHAR ) THEN
364 l_vcrv_rec.change_type_code := NULL;
365 END IF;
366 IF (l_vcrv_rec.status_code = OKL_API.G_MISS_CHAR ) THEN
367 l_vcrv_rec.status_code := NULL;
368 END IF;
369 IF (l_vcrv_rec.request_date = OKL_API.G_MISS_DATE ) THEN
370 l_vcrv_rec.request_date := NULL;
371 END IF;
372 IF (l_vcrv_rec.effective_date = OKL_API.G_MISS_DATE ) THEN
373 l_vcrv_rec.effective_date := NULL;
374 END IF;
375 IF (l_vcrv_rec.approved_date = OKL_API.G_MISS_DATE ) THEN
376 l_vcrv_rec.approved_date := NULL;
377 END IF;
378 IF (l_vcrv_rec.rejected_date = OKL_API.G_MISS_DATE ) THEN
379 l_vcrv_rec.rejected_date := NULL;
380 END IF;
381 IF (l_vcrv_rec.ineffective_date = OKL_API.G_MISS_DATE ) THEN
382 l_vcrv_rec.ineffective_date := NULL;
383 END IF;
384 IF (l_vcrv_rec.applied_date = OKL_API.G_MISS_DATE ) THEN
385 l_vcrv_rec.applied_date := NULL;
386 END IF;
387 IF (l_vcrv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
388 l_vcrv_rec.attribute_category := NULL;
389 END IF;
390 IF (l_vcrv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
391 l_vcrv_rec.attribute1 := NULL;
392 END IF;
393 IF (l_vcrv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
394 l_vcrv_rec.attribute2 := NULL;
395 END IF;
396 IF (l_vcrv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
397 l_vcrv_rec.attribute3 := NULL;
398 END IF;
399 IF (l_vcrv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
400 l_vcrv_rec.attribute4 := NULL;
401 END IF;
402 IF (l_vcrv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
403 l_vcrv_rec.attribute5 := NULL;
404 END IF;
405 IF (l_vcrv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
406 l_vcrv_rec.attribute6 := NULL;
407 END IF;
408 IF (l_vcrv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
409 l_vcrv_rec.attribute7 := NULL;
410 END IF;
411 IF (l_vcrv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
412 l_vcrv_rec.attribute8 := NULL;
413 END IF;
414 IF (l_vcrv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
415 l_vcrv_rec.attribute9 := NULL;
416 END IF;
417 IF (l_vcrv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
418 l_vcrv_rec.attribute10 := NULL;
419 END IF;
420 IF (l_vcrv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
421 l_vcrv_rec.attribute11 := NULL;
422 END IF;
423 IF (l_vcrv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
424 l_vcrv_rec.attribute12 := NULL;
425 END IF;
426 IF (l_vcrv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
427 l_vcrv_rec.attribute13 := NULL;
428 END IF;
429 IF (l_vcrv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
430 l_vcrv_rec.attribute14 := NULL;
431 END IF;
432 IF (l_vcrv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
433 l_vcrv_rec.attribute15 := NULL;
434 END IF;
435 IF (l_vcrv_rec.request_id = OKL_API.G_MISS_NUM ) THEN
436 l_vcrv_rec.request_id := NULL;
437 END IF;
438 IF (l_vcrv_rec.program_application_id = OKL_API.G_MISS_NUM ) THEN
439 l_vcrv_rec.program_application_id := NULL;
440 END IF;
441 IF (l_vcrv_rec.program_id = OKL_API.G_MISS_NUM ) THEN
442 l_vcrv_rec.program_id := NULL;
443 END IF;
444 IF (l_vcrv_rec.program_update_date = OKL_API.G_MISS_DATE ) THEN
445 l_vcrv_rec.program_update_date := NULL;
446 END IF;
447 IF (l_vcrv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
448 l_vcrv_rec.created_by := NULL;
449 END IF;
450 IF (l_vcrv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
451 l_vcrv_rec.creation_date := NULL;
452 END IF;
453 IF (l_vcrv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
454 l_vcrv_rec.last_updated_by := NULL;
455 END IF;
456 IF (l_vcrv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
457 l_vcrv_rec.last_update_date := NULL;
458 END IF;
459 IF (l_vcrv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
460 l_vcrv_rec.last_update_login := NULL;
461 END IF;
462 RETURN(l_vcrv_rec);
463 END null_out_defaults;
464 ---------------------------------
465 -- Validate_Attributes for: ID --
466 ---------------------------------
467 PROCEDURE validate_id(
468 x_return_status OUT NOCOPY VARCHAR2,
469 p_id IN NUMBER) IS
470 BEGIN
471 x_return_status := OKL_API.G_RET_STS_SUCCESS;
472 IF (p_id = OKL_API.G_MISS_NUM OR
473 p_id IS NULL)
474 THEN
475 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
476 x_return_status := OKL_API.G_RET_STS_ERROR;
477 RAISE G_EXCEPTION_HALT_VALIDATION;
478 END IF;
479 EXCEPTION
480 WHEN G_EXCEPTION_HALT_VALIDATION THEN
481 null;
482 WHEN OTHERS THEN
483 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
484 ,p_msg_name => G_UNEXPECTED_ERROR
485 ,p_token1 => G_SQLCODE_TOKEN
486 ,p_token1_value => SQLCODE
487 ,p_token2 => G_SQLERRM_TOKEN
488 ,p_token2_value => SQLERRM);
489 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
490 END validate_id;
491 ----------------------------------------------------
492 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
493 ----------------------------------------------------
494 PROCEDURE validate_object_version_number(
495 x_return_status OUT NOCOPY VARCHAR2,
496 p_object_version_number IN NUMBER) IS
497 BEGIN
498 x_return_status := OKL_API.G_RET_STS_SUCCESS;
499 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
500 p_object_version_number IS NULL)
501 THEN
502 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
503 x_return_status := OKL_API.G_RET_STS_ERROR;
504 RAISE G_EXCEPTION_HALT_VALIDATION;
505 END IF;
506 EXCEPTION
507 WHEN G_EXCEPTION_HALT_VALIDATION THEN
508 null;
509 WHEN OTHERS THEN
510 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
511 ,p_msg_name => G_UNEXPECTED_ERROR
512 ,p_token1 => G_SQLCODE_TOKEN
513 ,p_token1_value => SQLCODE
514 ,p_token2 => G_SQLERRM_TOKEN
515 ,p_token2_value => SQLERRM);
516 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
517 END validate_object_version_number;
518 ----------------------------------------------------
519 -- Validate_Attributes for: CHANGE_REQUEST_NUMBER --
520 ----------------------------------------------------
521 PROCEDURE validate_change_request_number(
522 x_return_status OUT NOCOPY VARCHAR2,
523 p_change_request_number IN NUMBER) IS
524 BEGIN
525 x_return_status := OKL_API.G_RET_STS_SUCCESS;
526 IF (p_change_request_number = OKL_API.G_MISS_NUM OR
527 p_change_request_number IS NULL)
528 THEN
529 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'change_request_number');
530 x_return_status := OKL_API.G_RET_STS_ERROR;
531 RAISE G_EXCEPTION_HALT_VALIDATION;
532 END IF;
533 EXCEPTION
534 WHEN G_EXCEPTION_HALT_VALIDATION THEN
535 null;
536 WHEN OTHERS THEN
537 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
538 ,p_msg_name => G_UNEXPECTED_ERROR
539 ,p_token1 => G_SQLCODE_TOKEN
540 ,p_token1_value => SQLCODE
541 ,p_token2 => G_SQLERRM_TOKEN
542 ,p_token2_value => SQLERRM);
543 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
544 END validate_change_request_number;
545 -------------------------------------
546 -- Validate_Attributes for: CHR_ID --
547 -------------------------------------
548 PROCEDURE validate_chr_id(
549 x_return_status OUT NOCOPY VARCHAR2,
550 p_chr_id IN NUMBER) IS
551
552 CURSOR c_valid_khr_csr IS
553 SELECT 'X'
554 FROM okc_k_headers_b
555 WHERE id = p_chr_id
556 AND (scs_code = 'PROGRAM' or SCS_CODE = 'OPERATING');
557 lv_dummy VARCHAR2(1);
558 BEGIN
559 x_return_status := OKL_API.G_RET_STS_SUCCESS;
560 IF (p_chr_id = OKL_API.G_MISS_NUM OR p_chr_id IS NULL)THEN
561 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'chr_id');
562 x_return_status := OKL_API.G_RET_STS_ERROR;
563 RAISE G_EXCEPTION_HALT_VALIDATION;
564 ELSE
565 -- sjalasut, added code to validate the program agreement. START of code changes
566 OPEN c_valid_khr_csr; FETCH c_valid_khr_csr INTO lv_dummy;
567 CLOSE c_valid_khr_csr;
568 IF(NVL(lv_dummy,'Y')<>'X')THEN
569 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'chr_id');
570 x_return_status := OKL_API.G_RET_STS_ERROR;
571 RAISE G_EXCEPTION_HALT_VALIDATION;
572 END IF;
573 -- sjalasut, added code to validate the program agreement. END of code changes
574 END IF;
575 EXCEPTION
576 WHEN G_EXCEPTION_HALT_VALIDATION THEN
577 null;
578 WHEN OTHERS THEN
579 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
580 ,p_msg_name => G_UNEXPECTED_ERROR
581 ,p_token1 => G_SQLCODE_TOKEN
582 ,p_token1_value => SQLCODE
583 ,p_token2 => G_SQLERRM_TOKEN
584 ,p_token2_value => SQLERRM);
585 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
586 END validate_chr_id;
587 -----------------------------------------------
588 -- Validate_Attributes for: CHANGE_TYPE_CODE --
589 -----------------------------------------------
590 PROCEDURE validate_change_type_code(
591 x_return_status OUT NOCOPY VARCHAR2,
592 p_change_type_code IN VARCHAR2) IS
593 -- sjalasut added cursor to validate the change type code
594 CURSOR c_valid_change_csr IS
595 SELECT 'X'
596 FROM fnd_lookups
597 WHERE lookup_type = 'OKL_VP_CHANGE_REQUEST_TYPES'
598 AND lookup_code = p_change_type_code;
599 lv_dummy VARCHAR2(1);
600 BEGIN
601 x_return_status := OKL_API.G_RET_STS_SUCCESS;
602 IF(p_change_type_code = OKL_API.G_MISS_CHAR OR p_change_type_code IS NULL) THEN
603 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'change_type_code');
604 x_return_status := OKL_API.G_RET_STS_ERROR;
605 RAISE G_EXCEPTION_HALT_VALIDATION;
606 ELSE
607 -- sjalasut, added code to validate the change request code. START of code changes
608 OPEN c_valid_change_csr; FETCH c_valid_change_csr INTO lv_dummy;
609 CLOSE c_valid_change_csr;
610 IF(NVL(lv_dummy,'Y')<>'X')THEN
611 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'change_type_code');
612 x_return_status := OKL_API.G_RET_STS_ERROR;
613 RAISE G_EXCEPTION_HALT_VALIDATION;
614 END IF;
615 -- sjalasut, added code to validate the change request code. END of code changes
616 END IF;
617 EXCEPTION
618 WHEN G_EXCEPTION_HALT_VALIDATION THEN
619 null;
620 WHEN OTHERS THEN
621 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
622 ,p_msg_name => G_UNEXPECTED_ERROR
623 ,p_token1 => G_SQLCODE_TOKEN
624 ,p_token1_value => SQLCODE
625 ,p_token2 => G_SQLERRM_TOKEN
626 ,p_token2_value => SQLERRM);
627 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
628 END validate_change_type_code;
629
630 -----------------------------------------------
631 -- Validate_Attributes for: STATUS_CODE --
632 -----------------------------------------------
633 PROCEDURE validate_status_code(
634 x_return_status OUT NOCOPY VARCHAR2,
635 p_status_code IN VARCHAR2) IS
636 -- sjalasut added cursor to validate the change type code
637 CURSOR c_valid_status_csr IS
638 SELECT 'X'
639 FROM fnd_lookups
640 WHERE lookup_type = 'OKL_VP_CR_STATUS_CODE'
641 AND lookup_code = p_status_code;
642 lv_dummy VARCHAR2(1);
643 BEGIN
644 x_return_status := OKL_API.G_RET_STS_SUCCESS;
645 IF(p_status_code = OKL_API.G_MISS_CHAR OR p_status_code IS NULL) THEN
646 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'status_code');
647 x_return_status := OKL_API.G_RET_STS_ERROR;
648 RAISE G_EXCEPTION_HALT_VALIDATION;
649 ELSE
650 -- sjalasut, added code to validate the status code. START of code changes
651 OPEN c_valid_status_csr; FETCH c_valid_status_csr INTO lv_dummy;
652 CLOSE c_valid_status_csr;
653 IF(NVL(lv_dummy,'Y')<>'X')THEN
654 OKL_API.set_message(G_APP_NAME, G_INVALID_VALUE, G_COL_NAME_TOKEN, 'status_code');
655 x_return_status := OKL_API.G_RET_STS_ERROR;
656 RAISE G_EXCEPTION_HALT_VALIDATION;
657 END IF;
658 -- sjalasut, added code to validate the status code. END of code changes
659 END IF;
660 EXCEPTION
661 WHEN G_EXCEPTION_HALT_VALIDATION THEN
662 null;
663 WHEN OTHERS THEN
664 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
665 ,p_msg_name => G_UNEXPECTED_ERROR
666 ,p_token1 => G_SQLCODE_TOKEN
667 ,p_token1_value => SQLCODE
668 ,p_token2 => G_SQLERRM_TOKEN
669 ,p_token2_value => SQLERRM);
670 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
671 END validate_status_code;
672
673
674 ---------------------------------------------------------------------------
675 -- FUNCTION Validate_Attributes
676 ---------------------------------------------------------------------------
677 ------------------------------------------------------
678 -- Validate_Attributes for:OKL_VP_CHANGE_REQUESTS_V --
679 ------------------------------------------------------
680 FUNCTION Validate_Attributes (
681 p_vcrv_rec IN vcrv_rec_type
682 ) RETURN VARCHAR2 IS
683 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
684 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
685 BEGIN
686 -----------------------------
687 -- Column Level Validation --
688 -----------------------------
689 -- ***
690 -- id
691 -- ***
692 validate_id(x_return_status, p_vcrv_rec.id);
693 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
694 l_return_status := x_return_status;
695 RAISE G_EXCEPTION_HALT_VALIDATION;
696 END IF;
697
698 -- ***
699 -- object_version_number
700 -- ***
701 -- validate_object_version_number(x_return_status, p_vcrv_rec.object_version_number);
702 -- IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
703 -- l_return_status := x_return_status;
704 -- RAISE G_EXCEPTION_HALT_VALIDATION;
705 -- END IF;
706
707 -- ***
708 -- change_request_number
709 -- ***
710 validate_change_request_number(x_return_status, p_vcrv_rec.change_request_number);
711 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
712 l_return_status := x_return_status;
713 RAISE G_EXCEPTION_HALT_VALIDATION;
714 END IF;
715
716 -- ***
717 -- chr_id
718 -- ***
719 validate_chr_id(x_return_status, p_vcrv_rec.chr_id);
720 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
721 l_return_status := x_return_status;
722 RAISE G_EXCEPTION_HALT_VALIDATION;
723 END IF;
724
725 -- ***
726 -- change_type_code
727 -- ***
728 validate_change_type_code(x_return_status, p_vcrv_rec.change_type_code);
729 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
730 l_return_status := x_return_status;
731 RAISE G_EXCEPTION_HALT_VALIDATION;
732 END IF;
733
734 -- ***
735 -- status_code
736 -- ***
737 validate_status_code(x_return_status, p_vcrv_rec.status_code);
738 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
739 l_return_status := x_return_status;
740 RAISE G_EXCEPTION_HALT_VALIDATION;
741 END IF;
742
743 RETURN(l_return_status);
744 EXCEPTION
745 WHEN G_EXCEPTION_HALT_VALIDATION THEN
746 RETURN(l_return_status);
747 WHEN OTHERS THEN
748 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
749 ,p_msg_name => G_UNEXPECTED_ERROR
750 ,p_token1 => G_SQLCODE_TOKEN
751 ,p_token1_value => SQLCODE
752 ,p_token2 => G_SQLERRM_TOKEN
753 ,p_token2_value => SQLERRM);
754 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
755 RETURN(l_return_status);
756 END Validate_Attributes;
757 ---------------------------------------------------------------------------
758 -- PROCEDURE Validate_Record
759 ---------------------------------------------------------------------------
760 --------------------------------------------------
761 -- Validate Record for:OKL_VP_CHANGE_REQUESTS_V --
762 --------------------------------------------------
763 FUNCTION Validate_Record (
764 p_vcrv_rec IN vcrv_rec_type,
765 p_db_vcrv_rec IN vcrv_rec_type
766 ) RETURN VARCHAR2 IS
767 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
768 BEGIN
769 RETURN (l_return_status);
770 END Validate_Record;
771 FUNCTION Validate_Record (
772 p_vcrv_rec IN vcrv_rec_type
773 ) RETURN VARCHAR2 IS
774 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
775 l_db_vcrv_rec vcrv_rec_type := get_rec(p_vcrv_rec);
776 BEGIN
777 l_return_status := Validate_Record(p_vcrv_rec => p_vcrv_rec,
778 p_db_vcrv_rec => l_db_vcrv_rec);
779 RETURN (l_return_status);
780 END Validate_Record;
781
782 ---------------------------------------------------------------------------
783 -- PROCEDURE Migrate
784 ---------------------------------------------------------------------------
785 PROCEDURE migrate (
786 p_from IN vcrv_rec_type,
787 p_to IN OUT NOCOPY vcr_rec_type
788 ) IS
789 BEGIN
790 p_to.id := p_from.id;
791 p_to.object_version_number := p_from.object_version_number;
792 p_to.change_request_number := p_from.change_request_number;
793 p_to.chr_id := p_from.chr_id;
794 p_to.change_type_code := p_from.change_type_code;
795 p_to.status_code := p_from.status_code;
796 p_to.request_date := p_from.request_date;
797 p_to.effective_date := p_from.effective_date;
798 p_to.approved_date := p_from.approved_date;
799 p_to.rejected_date := p_from.rejected_date;
800 p_to.ineffective_date := p_from.ineffective_date;
801 p_to.applied_date := p_from.applied_date;
802 p_to.attribute_category := p_from.attribute_category;
803 p_to.attribute1 := p_from.attribute1;
804 p_to.attribute2 := p_from.attribute2;
805 p_to.attribute3 := p_from.attribute3;
806 p_to.attribute4 := p_from.attribute4;
807 p_to.attribute5 := p_from.attribute5;
808 p_to.attribute6 := p_from.attribute6;
809 p_to.attribute7 := p_from.attribute7;
810 p_to.attribute8 := p_from.attribute8;
811 p_to.attribute9 := p_from.attribute9;
812 p_to.attribute10 := p_from.attribute10;
813 p_to.attribute11 := p_from.attribute11;
814 p_to.attribute12 := p_from.attribute12;
815 p_to.attribute13 := p_from.attribute13;
816 p_to.attribute14 := p_from.attribute14;
817 p_to.attribute15 := p_from.attribute15;
818 p_to.request_id := p_from.request_id;
819 p_to.program_application_id := p_from.program_application_id;
820 p_to.program_id := p_from.program_id;
821 p_to.program_update_date := p_from.program_update_date;
822 p_to.created_by := p_from.created_by;
823 p_to.creation_date := p_from.creation_date;
824 p_to.last_updated_by := p_from.last_updated_by;
825 p_to.last_update_date := p_from.last_update_date;
826 p_to.last_update_login := p_from.last_update_login;
827 END migrate;
828 PROCEDURE migrate (
829 p_from IN vcr_rec_type,
830 p_to IN OUT NOCOPY vcrv_rec_type
831 ) IS
832 BEGIN
833 p_to.id := p_from.id;
834 p_to.object_version_number := p_from.object_version_number;
835 p_to.change_request_number := p_from.change_request_number;
836 p_to.chr_id := p_from.chr_id;
837 p_to.change_type_code := p_from.change_type_code;
838 p_to.status_code := p_from.status_code;
839 p_to.request_date := p_from.request_date;
840 p_to.effective_date := p_from.effective_date;
841 p_to.approved_date := p_from.approved_date;
842 p_to.rejected_date := p_from.rejected_date;
843 p_to.ineffective_date := p_from.ineffective_date;
844 p_to.applied_date := p_from.applied_date;
845 p_to.attribute_category := p_from.attribute_category;
846 p_to.attribute1 := p_from.attribute1;
847 p_to.attribute2 := p_from.attribute2;
848 p_to.attribute3 := p_from.attribute3;
849 p_to.attribute4 := p_from.attribute4;
850 p_to.attribute5 := p_from.attribute5;
851 p_to.attribute6 := p_from.attribute6;
852 p_to.attribute7 := p_from.attribute7;
853 p_to.attribute8 := p_from.attribute8;
854 p_to.attribute9 := p_from.attribute9;
855 p_to.attribute10 := p_from.attribute10;
856 p_to.attribute11 := p_from.attribute11;
857 p_to.attribute12 := p_from.attribute12;
858 p_to.attribute13 := p_from.attribute13;
859 p_to.attribute14 := p_from.attribute14;
860 p_to.attribute15 := p_from.attribute15;
861 p_to.request_id := p_from.request_id;
862 p_to.program_application_id := p_from.program_application_id;
863 p_to.program_id := p_from.program_id;
864 p_to.program_update_date := p_from.program_update_date;
865 p_to.created_by := p_from.created_by;
866 p_to.creation_date := p_from.creation_date;
867 p_to.last_updated_by := p_from.last_updated_by;
868 p_to.last_update_date := p_from.last_update_date;
869 p_to.last_update_login := p_from.last_update_login;
870 END migrate;
871 ---------------------------------------------------------------------------
872 -- PROCEDURE validate_row
873 ---------------------------------------------------------------------------
874 -----------------------------------------------
875 -- validate_row for:OKL_VP_CHANGE_REQUESTS_V --
876 -----------------------------------------------
877 PROCEDURE validate_row(
878 p_api_version IN NUMBER,
879 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
880 x_return_status OUT NOCOPY VARCHAR2,
881 x_msg_count OUT NOCOPY NUMBER,
882 x_msg_data OUT NOCOPY VARCHAR2,
883 p_vcrv_rec IN vcrv_rec_type) IS
884
885 l_api_version CONSTANT NUMBER := 1;
886 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
887 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
888 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
889 l_vcr_rec vcr_rec_type;
890 l_vcr_rec vcr_rec_type;
891 BEGIN
892 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
893 G_PKG_NAME,
894 p_init_msg_list,
895 l_api_version,
896 p_api_version,
897 '_PVT',
898 x_return_status);
899 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
900 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
901 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
902 RAISE OKL_API.G_EXCEPTION_ERROR;
903 END IF;
904 --- Validate all non-missing attributes (Item Level Validation)
905 l_return_status := Validate_Attributes(l_vcrv_rec);
906 --- If any errors happen abort API
907 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
908 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
909 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
910 RAISE OKL_API.G_EXCEPTION_ERROR;
911 END IF;
912 l_return_status := Validate_Record(l_vcrv_rec);
913 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
914 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
915 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
916 RAISE OKL_API.G_EXCEPTION_ERROR;
917 END IF;
918 x_return_status := l_return_status;
919 EXCEPTION
920 WHEN OKL_API.G_EXCEPTION_ERROR THEN
921 x_return_status := OKL_API.HANDLE_EXCEPTIONS
922 (
923 l_api_name,
924 G_PKG_NAME,
925 'OKL_API.G_RET_STS_ERROR',
926 x_msg_count,
927 x_msg_data,
928 '_PVT'
929 );
930 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
931 x_return_status := OKL_API.HANDLE_EXCEPTIONS
932 (
933 l_api_name,
934 G_PKG_NAME,
935 'OKL_API.G_RET_STS_UNEXP_ERROR',
936 x_msg_count,
937 x_msg_data,
938 '_PVT'
939 );
940 WHEN OTHERS THEN
941 x_return_status := OKL_API.HANDLE_EXCEPTIONS
942 (
943 l_api_name,
944 G_PKG_NAME,
945 'OTHERS',
946 x_msg_count,
947 x_msg_data,
948 '_PVT'
949 );
950 END validate_row;
951 ----------------------------------------------------------
952 -- PL/SQL TBL validate_row for:OKL_VP_CHANGE_REQUESTS_V --
953 ----------------------------------------------------------
954 PROCEDURE validate_row(
955 p_api_version IN NUMBER,
956 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
957 x_return_status OUT NOCOPY VARCHAR2,
958 x_msg_count OUT NOCOPY NUMBER,
959 x_msg_data OUT NOCOPY VARCHAR2,
960 p_vcrv_tbl IN vcrv_tbl_type,
961 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
962
963 l_api_version CONSTANT NUMBER := 1;
964 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
965 i NUMBER := 0;
966 BEGIN
967 OKL_API.init_msg_list(p_init_msg_list);
968 -- Make sure PL/SQL table has records in it before passing
969 IF (p_vcrv_tbl.COUNT > 0) THEN
970 i := p_vcrv_tbl.FIRST;
971 LOOP
972 DECLARE
973 l_error_rec OKL_API.ERROR_REC_TYPE;
974 BEGIN
975 l_error_rec.api_name := l_api_name;
976 l_error_rec.api_package := G_PKG_NAME;
977 l_error_rec.idx := i;
978 validate_row (
979 p_api_version => p_api_version,
980 p_init_msg_list => OKL_API.G_FALSE,
981 x_return_status => l_error_rec.error_type,
982 x_msg_count => l_error_rec.msg_count,
983 x_msg_data => l_error_rec.msg_data,
984 p_vcrv_rec => p_vcrv_tbl(i));
985 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
986 l_error_rec.sqlcode := SQLCODE;
987 load_error_tbl(l_error_rec, px_error_tbl);
988 ELSE
989 x_msg_count := l_error_rec.msg_count;
990 x_msg_data := l_error_rec.msg_data;
991 END IF;
992 EXCEPTION
993 WHEN OKL_API.G_EXCEPTION_ERROR THEN
994 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
995 l_error_rec.sqlcode := SQLCODE;
996 load_error_tbl(l_error_rec, px_error_tbl);
997 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
998 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
999 l_error_rec.sqlcode := SQLCODE;
1000 load_error_tbl(l_error_rec, px_error_tbl);
1001 WHEN OTHERS THEN
1002 l_error_rec.error_type := 'OTHERS';
1003 l_error_rec.sqlcode := SQLCODE;
1004 load_error_tbl(l_error_rec, px_error_tbl);
1005 END;
1006 EXIT WHEN (i = p_vcrv_tbl.LAST);
1007 i := p_vcrv_tbl.NEXT(i);
1008 END LOOP;
1009 END IF;
1010 -- Loop through the error_tbl to find the error with the highest severity
1011 -- and return it.
1012 x_return_status := find_highest_exception(px_error_tbl);
1013 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1014 EXCEPTION
1015 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1016 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1017 (
1018 l_api_name,
1019 G_PKG_NAME,
1020 'OKL_API.G_RET_STS_ERROR',
1021 x_msg_count,
1022 x_msg_data,
1023 '_PVT'
1024 );
1025 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1026 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1027 (
1028 l_api_name,
1029 G_PKG_NAME,
1030 'OKL_API.G_RET_STS_UNEXP_ERROR',
1031 x_msg_count,
1032 x_msg_data,
1033 '_PVT'
1034 );
1035 WHEN OTHERS THEN
1036 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1037 (
1038 l_api_name,
1039 G_PKG_NAME,
1040 'OTHERS',
1041 x_msg_count,
1042 x_msg_data,
1043 '_PVT'
1044 );
1045 END validate_row;
1046
1047 ----------------------------------------------------------
1048 -- PL/SQL TBL validate_row for:OKL_VP_CHANGE_REQUESTS_V --
1049 ----------------------------------------------------------
1050 PROCEDURE validate_row(
1051 p_api_version IN NUMBER,
1052 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_msg_count OUT NOCOPY NUMBER,
1055 x_msg_data OUT NOCOPY VARCHAR2,
1056 p_vcrv_tbl IN vcrv_tbl_type) IS
1057
1058 l_api_version CONSTANT NUMBER := 1;
1059 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1060 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1061 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1062 BEGIN
1063 OKL_API.init_msg_list(p_init_msg_list);
1064 -- Make sure PL/SQL table has records in it before passing
1065 IF (p_vcrv_tbl.COUNT > 0) THEN
1066 validate_row (
1067 p_api_version => p_api_version,
1068 p_init_msg_list => OKL_API.G_FALSE,
1069 x_return_status => x_return_status,
1070 x_msg_count => x_msg_count,
1071 x_msg_data => x_msg_data,
1072 p_vcrv_tbl => p_vcrv_tbl,
1073 px_error_tbl => l_error_tbl);
1074 END IF;
1075 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1076 EXCEPTION
1077 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1078 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1079 (
1080 l_api_name,
1081 G_PKG_NAME,
1082 'OKL_API.G_RET_STS_ERROR',
1083 x_msg_count,
1084 x_msg_data,
1085 '_PVT'
1086 );
1087 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1088 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1089 (
1090 l_api_name,
1091 G_PKG_NAME,
1092 'OKL_API.G_RET_STS_UNEXP_ERROR',
1093 x_msg_count,
1094 x_msg_data,
1095 '_PVT'
1096 );
1097 WHEN OTHERS THEN
1098 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1099 (
1100 l_api_name,
1101 G_PKG_NAME,
1102 'OTHERS',
1103 x_msg_count,
1104 x_msg_data,
1105 '_PVT'
1106 );
1107 END validate_row;
1108
1109 ---------------------------------------------------------------------------
1110 -- PROCEDURE insert_row
1111 ---------------------------------------------------------------------------
1112 -------------------------------------------
1113 -- insert_row for:OKL_VP_CHANGE_REQUESTS --
1114 -------------------------------------------
1115 PROCEDURE insert_row(
1116 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1117 x_return_status OUT NOCOPY VARCHAR2,
1118 x_msg_count OUT NOCOPY NUMBER,
1119 x_msg_data OUT NOCOPY VARCHAR2,
1120 p_vcr_rec IN vcr_rec_type,
1121 x_vcr_rec OUT NOCOPY vcr_rec_type) IS
1122
1123 l_api_version CONSTANT NUMBER := 1;
1124 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1125 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1126 l_vcr_rec vcr_rec_type := p_vcr_rec;
1127 l_def_vcr_rec vcr_rec_type;
1128 -----------------------------------------------
1129 -- Set_Attributes for:OKL_VP_CHANGE_REQUESTS --
1130 -----------------------------------------------
1131 FUNCTION Set_Attributes (
1132 p_vcr_rec IN vcr_rec_type,
1133 x_vcr_rec OUT NOCOPY vcr_rec_type
1134 ) RETURN VARCHAR2 IS
1135 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1136 BEGIN
1137 x_vcr_rec := p_vcr_rec;
1138 RETURN(l_return_status);
1139 END Set_Attributes;
1140 BEGIN
1141 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1142 p_init_msg_list,
1143 '_PVT',
1144 x_return_status);
1145 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1146 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1147 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1148 RAISE OKL_API.G_EXCEPTION_ERROR;
1149 END IF;
1150 --- Setting item atributes
1151 l_return_status := Set_Attributes(
1152 p_vcr_rec, -- IN
1153 l_vcr_rec); -- OUT
1154 --- If any errors happen abort API
1155 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1156 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1157 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1158 RAISE OKL_API.G_EXCEPTION_ERROR;
1159 END IF;
1160 INSERT INTO OKL_VP_CHANGE_REQUESTS(
1161 id,
1162 object_version_number,
1163 change_request_number,
1164 chr_id,
1165 change_type_code,
1166 status_code,
1167 request_date,
1168 effective_date,
1169 approved_date,
1170 rejected_date,
1171 ineffective_date,
1172 applied_date,
1173 attribute_category,
1174 attribute1,
1175 attribute2,
1176 attribute3,
1177 attribute4,
1178 attribute5,
1179 attribute6,
1180 attribute7,
1181 attribute8,
1182 attribute9,
1183 attribute10,
1184 attribute11,
1185 attribute12,
1186 attribute13,
1187 attribute14,
1188 attribute15,
1189 request_id,
1190 program_application_id,
1191 program_id,
1192 program_update_date,
1193 created_by,
1194 creation_date,
1195 last_updated_by,
1196 last_update_date,
1197 last_update_login)
1198 VALUES (
1199 l_vcr_rec.id,
1200 l_vcr_rec.object_version_number,
1201 l_vcr_rec.change_request_number,
1202 l_vcr_rec.chr_id,
1203 l_vcr_rec.change_type_code,
1204 l_vcr_rec.status_code,
1205 l_vcr_rec.request_date,
1206 l_vcr_rec.effective_date,
1207 l_vcr_rec.approved_date,
1208 l_vcr_rec.rejected_date,
1209 l_vcr_rec.ineffective_date,
1210 l_vcr_rec.applied_date,
1211 l_vcr_rec.attribute_category,
1212 l_vcr_rec.attribute1,
1213 l_vcr_rec.attribute2,
1214 l_vcr_rec.attribute3,
1215 l_vcr_rec.attribute4,
1216 l_vcr_rec.attribute5,
1217 l_vcr_rec.attribute6,
1218 l_vcr_rec.attribute7,
1219 l_vcr_rec.attribute8,
1220 l_vcr_rec.attribute9,
1221 l_vcr_rec.attribute10,
1222 l_vcr_rec.attribute11,
1223 l_vcr_rec.attribute12,
1224 l_vcr_rec.attribute13,
1225 l_vcr_rec.attribute14,
1226 l_vcr_rec.attribute15,
1227 l_vcr_rec.request_id,
1228 l_vcr_rec.program_application_id,
1229 l_vcr_rec.program_id,
1230 l_vcr_rec.program_update_date,
1231 l_vcr_rec.created_by,
1232 l_vcr_rec.creation_date,
1233 l_vcr_rec.last_updated_by,
1234 l_vcr_rec.last_update_date,
1235 l_vcr_rec.last_update_login);
1236 -- Set OUT values
1237 x_vcr_rec := l_vcr_rec;
1238 x_return_status := l_return_status;
1239 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1240 EXCEPTION
1241 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1242 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1243 (
1244 l_api_name,
1245 G_PKG_NAME,
1246 'OKL_API.G_RET_STS_ERROR',
1247 x_msg_count,
1248 x_msg_data,
1249 '_PVT'
1250 );
1251 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1252 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1253 (
1254 l_api_name,
1255 G_PKG_NAME,
1256 'OKL_API.G_RET_STS_UNEXP_ERROR',
1257 x_msg_count,
1258 x_msg_data,
1259 '_PVT'
1260 );
1261 WHEN OTHERS THEN
1262 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1263 (
1264 l_api_name,
1265 G_PKG_NAME,
1266 'OTHERS',
1267 x_msg_count,
1268 x_msg_data,
1269 '_PVT'
1270 );
1271 END insert_row;
1272 ----------------------------------------------
1273 -- insert_row for :OKL_VP_CHANGE_REQUESTS_V --
1274 ----------------------------------------------
1275 PROCEDURE insert_row(
1276 p_api_version IN NUMBER,
1277 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1278 x_return_status OUT NOCOPY VARCHAR2,
1279 x_msg_count OUT NOCOPY NUMBER,
1280 x_msg_data OUT NOCOPY VARCHAR2,
1281 p_vcrv_rec IN vcrv_rec_type,
1282 x_vcrv_rec OUT NOCOPY vcrv_rec_type) IS
1283
1284 l_api_version CONSTANT NUMBER := 1;
1285 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1286 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1287 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
1288 l_def_vcrv_rec vcrv_rec_type;
1289 l_vcr_rec vcr_rec_type;
1290 lx_vcr_rec vcr_rec_type;
1291 -------------------------------
1292 -- FUNCTION fill_who_columns --
1293 -------------------------------
1294 FUNCTION fill_who_columns (
1295 p_vcrv_rec IN vcrv_rec_type
1296 ) RETURN vcrv_rec_type IS
1297 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
1298 BEGIN
1299 l_vcrv_rec.CREATION_DATE := SYSDATE;
1300 l_vcrv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1301 l_vcrv_rec.LAST_UPDATE_DATE := l_vcrv_rec.CREATION_DATE;
1302 l_vcrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1303 l_vcrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1304 RETURN(l_vcrv_rec);
1305 END fill_who_columns;
1306 -------------------------------------------------
1307 -- Set_Attributes for:OKL_VP_CHANGE_REQUESTS_V --
1308 -------------------------------------------------
1309 FUNCTION Set_Attributes (
1310 p_vcrv_rec IN vcrv_rec_type,
1311 x_vcrv_rec OUT NOCOPY vcrv_rec_type
1312 ) RETURN VARCHAR2 IS
1313 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1314 BEGIN
1315 x_vcrv_rec := p_vcrv_rec;
1316 x_vcrv_rec.OBJECT_VERSION_NUMBER := 1;
1317 RETURN(l_return_status);
1318 END Set_Attributes;
1319 BEGIN
1320 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1321 G_PKG_NAME,
1322 p_init_msg_list,
1323 l_api_version,
1324 p_api_version,
1325 '_PVT',
1326 x_return_status);
1327 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1330 RAISE OKL_API.G_EXCEPTION_ERROR;
1331 END IF;
1332 l_vcrv_rec := null_out_defaults(p_vcrv_rec);
1333 -- Set primary key value
1334 l_vcrv_rec.ID := get_seq_id;
1335 -- Setting item attributes
1336 l_return_Status := Set_Attributes(
1337 l_vcrv_rec, -- IN
1338 l_def_vcrv_rec); -- OUT
1339 --- If any errors happen abort API
1340 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1341 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1342 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1343 RAISE OKL_API.G_EXCEPTION_ERROR;
1344 END IF;
1345 l_def_vcrv_rec := fill_who_columns(l_def_vcrv_rec);
1346 --- Validate all non-missing attributes (Item Level Validation)
1347 l_return_status := Validate_Attributes(l_def_vcrv_rec);
1348 --- If any errors happen abort API
1349 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1350 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1351 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1352 RAISE OKL_API.G_EXCEPTION_ERROR;
1353 END IF;
1354 l_return_status := Validate_Record(l_def_vcrv_rec);
1355 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1356 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1357 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1358 RAISE OKL_API.G_EXCEPTION_ERROR;
1359 END IF;
1360 -----------------------------------------
1361 -- Move VIEW record to "Child" records --
1362 -----------------------------------------
1363 migrate(l_def_vcrv_rec, l_vcr_rec);
1364 -----------------------------------------------
1365 -- Call the INSERT_ROW for each child record --
1366 -----------------------------------------------
1367 insert_row(
1368 p_init_msg_list,
1369 l_return_status,
1370 x_msg_count,
1371 x_msg_data,
1372 l_vcr_rec,
1373 lx_vcr_rec
1374 );
1375 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1376 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1377 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1378 RAISE OKL_API.G_EXCEPTION_ERROR;
1379 END IF;
1380 migrate(lx_vcr_rec, l_def_vcrv_rec);
1381 -- Set OUT values
1382 x_vcrv_rec := l_def_vcrv_rec;
1383 x_return_status := l_return_status;
1384 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1385 EXCEPTION
1386 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1387 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1388 (
1389 l_api_name,
1390 G_PKG_NAME,
1391 'OKL_API.G_RET_STS_ERROR',
1392 x_msg_count,
1393 x_msg_data,
1394 '_PVT'
1395 );
1396 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1397 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1398 (
1399 l_api_name,
1400 G_PKG_NAME,
1401 'OKL_API.G_RET_STS_UNEXP_ERROR',
1402 x_msg_count,
1403 x_msg_data,
1404 '_PVT'
1405 );
1406 WHEN OTHERS THEN
1407 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1408 (
1409 l_api_name,
1410 G_PKG_NAME,
1411 'OTHERS',
1412 x_msg_count,
1413 x_msg_data,
1414 '_PVT'
1415 );
1416 END insert_row;
1417 ----------------------------------------
1418 -- PL/SQL TBL insert_row for:VCRV_TBL --
1419 ----------------------------------------
1420 PROCEDURE insert_row(
1421 p_api_version IN NUMBER,
1422 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1423 x_return_status OUT NOCOPY VARCHAR2,
1424 x_msg_count OUT NOCOPY NUMBER,
1425 x_msg_data OUT NOCOPY VARCHAR2,
1426 p_vcrv_tbl IN vcrv_tbl_type,
1427 x_vcrv_tbl OUT NOCOPY vcrv_tbl_type,
1428 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1429
1430 l_api_version CONSTANT NUMBER := 1;
1431 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1432 i NUMBER := 0;
1433 BEGIN
1434 OKL_API.init_msg_list(p_init_msg_list);
1435 -- Make sure PL/SQL table has records in it before passing
1436 IF (p_vcrv_tbl.COUNT > 0) THEN
1437 i := p_vcrv_tbl.FIRST;
1438 LOOP
1439 DECLARE
1440 l_error_rec OKL_API.ERROR_REC_TYPE;
1441 BEGIN
1442 l_error_rec.api_name := l_api_name;
1443 l_error_rec.api_package := G_PKG_NAME;
1444 l_error_rec.idx := i;
1445 insert_row (
1446 p_api_version => p_api_version,
1447 p_init_msg_list => OKL_API.G_FALSE,
1448 x_return_status => l_error_rec.error_type,
1449 x_msg_count => l_error_rec.msg_count,
1450 x_msg_data => l_error_rec.msg_data,
1451 p_vcrv_rec => p_vcrv_tbl(i),
1452 x_vcrv_rec => x_vcrv_tbl(i));
1453 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1454 l_error_rec.sqlcode := SQLCODE;
1455 load_error_tbl(l_error_rec, px_error_tbl);
1456 ELSE
1457 x_msg_count := l_error_rec.msg_count;
1458 x_msg_data := l_error_rec.msg_data;
1459 END IF;
1460 EXCEPTION
1461 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1462 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1463 l_error_rec.sqlcode := SQLCODE;
1464 load_error_tbl(l_error_rec, px_error_tbl);
1465 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1466 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1467 l_error_rec.sqlcode := SQLCODE;
1468 load_error_tbl(l_error_rec, px_error_tbl);
1469 WHEN OTHERS THEN
1470 l_error_rec.error_type := 'OTHERS';
1471 l_error_rec.sqlcode := SQLCODE;
1472 load_error_tbl(l_error_rec, px_error_tbl);
1473 END;
1474 EXIT WHEN (i = p_vcrv_tbl.LAST);
1475 i := p_vcrv_tbl.NEXT(i);
1476 END LOOP;
1477 END IF;
1478 -- Loop through the error_tbl to find the error with the highest severity
1479 -- and return it.
1480 x_return_status := find_highest_exception(px_error_tbl);
1481 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1482 EXCEPTION
1483 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1484 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1485 (
1486 l_api_name,
1487 G_PKG_NAME,
1488 'OKL_API.G_RET_STS_ERROR',
1489 x_msg_count,
1490 x_msg_data,
1491 '_PVT'
1492 );
1493 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1494 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1495 (
1496 l_api_name,
1497 G_PKG_NAME,
1498 'OKL_API.G_RET_STS_UNEXP_ERROR',
1499 x_msg_count,
1500 x_msg_data,
1501 '_PVT'
1502 );
1503 WHEN OTHERS THEN
1504 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1505 (
1506 l_api_name,
1507 G_PKG_NAME,
1508 'OTHERS',
1509 x_msg_count,
1510 x_msg_data,
1511 '_PVT'
1512 );
1513 END insert_row;
1514
1515 ----------------------------------------
1516 -- PL/SQL TBL insert_row for:VCRV_TBL --
1517 ----------------------------------------
1518 PROCEDURE insert_row(
1519 p_api_version IN NUMBER,
1520 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1521 x_return_status OUT NOCOPY VARCHAR2,
1522 x_msg_count OUT NOCOPY NUMBER,
1523 x_msg_data OUT NOCOPY VARCHAR2,
1524 p_vcrv_tbl IN vcrv_tbl_type,
1525 x_vcrv_tbl OUT NOCOPY vcrv_tbl_type) IS
1526
1527 l_api_version CONSTANT NUMBER := 1;
1528 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1529 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1530 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1531 BEGIN
1532 OKL_API.init_msg_list(p_init_msg_list);
1533 -- Make sure PL/SQL table has records in it before passing
1534 IF (p_vcrv_tbl.COUNT > 0) THEN
1535 insert_row (
1536 p_api_version => p_api_version,
1537 p_init_msg_list => OKL_API.G_FALSE,
1538 x_return_status => x_return_status,
1539 x_msg_count => x_msg_count,
1540 x_msg_data => x_msg_data,
1541 p_vcrv_tbl => p_vcrv_tbl,
1542 x_vcrv_tbl => x_vcrv_tbl,
1543 px_error_tbl => l_error_tbl);
1544 END IF;
1545 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1546 EXCEPTION
1547 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1548 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1549 (
1550 l_api_name,
1551 G_PKG_NAME,
1552 'OKL_API.G_RET_STS_ERROR',
1553 x_msg_count,
1554 x_msg_data,
1555 '_PVT'
1556 );
1557 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1558 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1559 (
1560 l_api_name,
1561 G_PKG_NAME,
1562 'OKL_API.G_RET_STS_UNEXP_ERROR',
1563 x_msg_count,
1564 x_msg_data,
1565 '_PVT'
1566 );
1567 WHEN OTHERS THEN
1568 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1569 (
1570 l_api_name,
1571 G_PKG_NAME,
1572 'OTHERS',
1573 x_msg_count,
1574 x_msg_data,
1575 '_PVT'
1576 );
1577 END insert_row;
1578
1579 ---------------------------------------------------------------------------
1580 -- PROCEDURE lock_row
1581 ---------------------------------------------------------------------------
1582 -----------------------------------------
1583 -- lock_row for:OKL_VP_CHANGE_REQUESTS --
1584 -----------------------------------------
1585 PROCEDURE lock_row(
1586 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1587 x_return_status OUT NOCOPY VARCHAR2,
1588 x_msg_count OUT NOCOPY NUMBER,
1589 x_msg_data OUT NOCOPY VARCHAR2,
1590 p_vcr_rec IN vcr_rec_type) IS
1591
1592 E_Resource_Busy EXCEPTION;
1593 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1594 CURSOR lock_csr (p_vcr_rec IN vcr_rec_type) IS
1595 SELECT OBJECT_VERSION_NUMBER
1596 FROM OKL_VP_CHANGE_REQUESTS
1597 WHERE ID = p_vcr_rec.id
1598 AND OBJECT_VERSION_NUMBER = p_vcr_rec.object_version_number
1599 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1600
1601 CURSOR lchk_csr (p_vcr_rec IN vcr_rec_type) IS
1602 SELECT OBJECT_VERSION_NUMBER
1603 FROM OKL_VP_CHANGE_REQUESTS
1604 WHERE ID = p_vcr_rec.id;
1605 l_api_version CONSTANT NUMBER := 1;
1606 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1607 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1608 l_object_version_number OKL_VP_CHANGE_REQUESTS.OBJECT_VERSION_NUMBER%TYPE;
1609 lc_object_version_number OKL_VP_CHANGE_REQUESTS.OBJECT_VERSION_NUMBER%TYPE;
1610 l_row_notfound BOOLEAN := FALSE;
1611 lc_row_notfound BOOLEAN := FALSE;
1612 BEGIN
1613 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1614 p_init_msg_list,
1615 '_PVT',
1616 x_return_status);
1617 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1618 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1619 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1620 RAISE OKL_API.G_EXCEPTION_ERROR;
1621 END IF;
1622 BEGIN
1623 OPEN lock_csr(p_vcr_rec);
1624 FETCH lock_csr INTO l_object_version_number;
1625 l_row_notfound := lock_csr%NOTFOUND;
1626 CLOSE lock_csr;
1627 EXCEPTION
1628 WHEN E_Resource_Busy THEN
1629 IF (lock_csr%ISOPEN) THEN
1630 CLOSE lock_csr;
1631 END IF;
1632 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1633 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1634 END;
1635
1636 IF ( l_row_notfound ) THEN
1637 OPEN lchk_csr(p_vcr_rec);
1638 FETCH lchk_csr INTO lc_object_version_number;
1639 lc_row_notfound := lchk_csr%NOTFOUND;
1640 CLOSE lchk_csr;
1641 END IF;
1642 IF (lc_row_notfound) THEN
1643 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1644 RAISE OKL_API.G_EXCEPTION_ERROR;
1645 ELSIF lc_object_version_number > p_vcr_rec.object_version_number THEN
1646 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1647 RAISE OKL_API.G_EXCEPTION_ERROR;
1648 ELSIF lc_object_version_number <> p_vcr_rec.object_version_number THEN
1649 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1650 RAISE OKL_API.G_EXCEPTION_ERROR;
1651 ELSIF lc_object_version_number = -1 THEN
1652 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1653 RAISE OKL_API.G_EXCEPTION_ERROR;
1654 END IF;
1655 x_return_status := l_return_status;
1656 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1657 EXCEPTION
1658 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1659 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1660 (
1661 l_api_name,
1662 G_PKG_NAME,
1663 'OKL_API.G_RET_STS_ERROR',
1664 x_msg_count,
1665 x_msg_data,
1666 '_PVT'
1667 );
1668 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1669 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1670 (
1671 l_api_name,
1672 G_PKG_NAME,
1673 'OKL_API.G_RET_STS_UNEXP_ERROR',
1674 x_msg_count,
1675 x_msg_data,
1676 '_PVT'
1677 );
1678 WHEN OTHERS THEN
1679 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1680 (
1681 l_api_name,
1682 G_PKG_NAME,
1683 'OTHERS',
1684 x_msg_count,
1685 x_msg_data,
1686 '_PVT'
1687 );
1688 END lock_row;
1689 --------------------------------------------
1690 -- lock_row for: OKL_VP_CHANGE_REQUESTS_V --
1691 --------------------------------------------
1692 PROCEDURE lock_row(
1693 p_api_version IN NUMBER,
1694 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1695 x_return_status OUT NOCOPY VARCHAR2,
1696 x_msg_count OUT NOCOPY NUMBER,
1697 x_msg_data OUT NOCOPY VARCHAR2,
1698 p_vcrv_rec IN vcrv_rec_type) IS
1699
1700 l_api_version CONSTANT NUMBER := 1;
1701 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1702 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1703 l_vcr_rec vcr_rec_type;
1704 BEGIN
1705 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1706 G_PKG_NAME,
1707 p_init_msg_list,
1708 l_api_version,
1709 p_api_version,
1710 '_PVT',
1711 x_return_status);
1712 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1713 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1714 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1715 RAISE OKL_API.G_EXCEPTION_ERROR;
1716 END IF;
1717 -----------------------------------------
1718 -- Move VIEW record to "Child" records --
1719 -----------------------------------------
1720 migrate(p_vcrv_rec, l_vcr_rec);
1721 ---------------------------------------------
1722 -- Call the LOCK_ROW for each child record --
1723 ---------------------------------------------
1724 lock_row(
1725 p_init_msg_list,
1726 l_return_status,
1727 x_msg_count,
1728 x_msg_data,
1729 l_vcr_rec
1730 );
1731 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1732 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1733 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1734 RAISE OKL_API.G_EXCEPTION_ERROR;
1735 END IF;
1736 x_return_status := l_return_status;
1737 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1738 EXCEPTION
1739 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1740 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1741 (
1742 l_api_name,
1743 G_PKG_NAME,
1744 'OKL_API.G_RET_STS_ERROR',
1745 x_msg_count,
1746 x_msg_data,
1747 '_PVT'
1748 );
1749 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1750 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1751 (
1752 l_api_name,
1753 G_PKG_NAME,
1754 'OKL_API.G_RET_STS_UNEXP_ERROR',
1755 x_msg_count,
1756 x_msg_data,
1757 '_PVT'
1758 );
1759 WHEN OTHERS THEN
1760 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1761 (
1762 l_api_name,
1763 G_PKG_NAME,
1764 'OTHERS',
1765 x_msg_count,
1766 x_msg_data,
1767 '_PVT'
1768 );
1769 END lock_row;
1770 --------------------------------------
1771 -- PL/SQL TBL lock_row for:VCRV_TBL --
1772 --------------------------------------
1773 PROCEDURE lock_row(
1774 p_api_version IN NUMBER,
1775 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1776 x_return_status OUT NOCOPY VARCHAR2,
1777 x_msg_count OUT NOCOPY NUMBER,
1778 x_msg_data OUT NOCOPY VARCHAR2,
1779 p_vcrv_tbl IN vcrv_tbl_type,
1780 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1781
1782 l_api_version CONSTANT NUMBER := 1;
1783 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1784 i NUMBER := 0;
1785 BEGIN
1786 OKL_API.init_msg_list(p_init_msg_list);
1787 -- Make sure PL/SQL table has recrods in it before passing
1788 IF (p_vcrv_tbl.COUNT > 0) THEN
1789 i := p_vcrv_tbl.FIRST;
1790 LOOP
1791 DECLARE
1792 l_error_rec OKL_API.ERROR_REC_TYPE;
1793 BEGIN
1794 l_error_rec.api_name := l_api_name;
1795 l_error_rec.api_package := G_PKG_NAME;
1796 l_error_rec.idx := i;
1797 lock_row(
1798 p_api_version => p_api_version,
1799 p_init_msg_list => OKL_API.G_FALSE,
1800 x_return_status => l_error_rec.error_type,
1801 x_msg_count => l_error_rec.msg_count,
1802 x_msg_data => l_error_rec.msg_data,
1803 p_vcrv_rec => p_vcrv_tbl(i));
1804 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1805 l_error_rec.sqlcode := SQLCODE;
1806 load_error_tbl(l_error_rec, px_error_tbl);
1807 ELSE
1808 x_msg_count := l_error_rec.msg_count;
1809 x_msg_data := l_error_rec.msg_data;
1810 END IF;
1811 EXCEPTION
1812 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1813 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1814 l_error_rec.sqlcode := SQLCODE;
1815 load_error_tbl(l_error_rec, px_error_tbl);
1816 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1817 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1818 l_error_rec.sqlcode := SQLCODE;
1819 load_error_tbl(l_error_rec, px_error_tbl);
1820 WHEN OTHERS THEN
1821 l_error_rec.error_type := 'OTHERS';
1822 l_error_rec.sqlcode := SQLCODE;
1823 load_error_tbl(l_error_rec, px_error_tbl);
1824 END;
1825 EXIT WHEN (i = p_vcrv_tbl.LAST);
1826 i := p_vcrv_tbl.NEXT(i);
1827 END LOOP;
1828 END IF;
1829 -- Loop through the error_tbl to find the error with the highest severity
1830 -- and return it.
1831 x_return_status := find_highest_exception(px_error_tbl);
1832 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1833 EXCEPTION
1834 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1835 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1836 (
1837 l_api_name,
1838 G_PKG_NAME,
1839 'OKL_API.G_RET_STS_ERROR',
1840 x_msg_count,
1841 x_msg_data,
1842 '_PVT'
1843 );
1844 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1845 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1846 (
1847 l_api_name,
1848 G_PKG_NAME,
1849 'OKL_API.G_RET_STS_UNEXP_ERROR',
1850 x_msg_count,
1851 x_msg_data,
1852 '_PVT'
1853 );
1854 WHEN OTHERS THEN
1855 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1856 (
1857 l_api_name,
1858 G_PKG_NAME,
1859 'OTHERS',
1860 x_msg_count,
1861 x_msg_data,
1862 '_PVT'
1863 );
1864 END lock_row;
1865 --------------------------------------
1866 -- PL/SQL TBL lock_row for:VCRV_TBL --
1867 --------------------------------------
1868 PROCEDURE lock_row(
1869 p_api_version IN NUMBER,
1870 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1871 x_return_status OUT NOCOPY VARCHAR2,
1872 x_msg_count OUT NOCOPY NUMBER,
1873 x_msg_data OUT NOCOPY VARCHAR2,
1874 p_vcrv_tbl IN vcrv_tbl_type) IS
1875
1876 l_api_version CONSTANT NUMBER := 1;
1877 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1878 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1879 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1880 BEGIN
1881 OKL_API.init_msg_list(p_init_msg_list);
1882 -- Make sure PL/SQL table has recrods in it before passing
1883 IF (p_vcrv_tbl.COUNT > 0) THEN
1884 lock_row(
1885 p_api_version => p_api_version,
1886 p_init_msg_list => OKL_API.G_FALSE,
1887 x_return_status => x_return_status,
1888 x_msg_count => x_msg_count,
1889 x_msg_data => x_msg_data,
1890 p_vcrv_tbl => p_vcrv_tbl,
1891 px_error_tbl => l_error_tbl);
1892 END IF;
1893 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1894 EXCEPTION
1895 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1896 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1897 (
1898 l_api_name,
1899 G_PKG_NAME,
1900 'OKL_API.G_RET_STS_ERROR',
1901 x_msg_count,
1902 x_msg_data,
1903 '_PVT'
1904 );
1905 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1906 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1907 (
1908 l_api_name,
1909 G_PKG_NAME,
1910 'OKL_API.G_RET_STS_UNEXP_ERROR',
1911 x_msg_count,
1912 x_msg_data,
1913 '_PVT'
1914 );
1915 WHEN OTHERS THEN
1916 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1917 (
1918 l_api_name,
1919 G_PKG_NAME,
1920 'OTHERS',
1921 x_msg_count,
1922 x_msg_data,
1923 '_PVT'
1924 );
1925 END lock_row;
1926 ---------------------------------------------------------------------------
1927 -- PROCEDURE update_row
1928 ---------------------------------------------------------------------------
1929 -------------------------------------------
1930 -- update_row for:OKL_VP_CHANGE_REQUESTS --
1931 -------------------------------------------
1932 PROCEDURE update_row(
1933 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1934 x_return_status OUT NOCOPY VARCHAR2,
1935 x_msg_count OUT NOCOPY NUMBER,
1936 x_msg_data OUT NOCOPY VARCHAR2,
1937 p_vcr_rec IN vcr_rec_type,
1938 x_vcr_rec OUT NOCOPY vcr_rec_type) IS
1939
1940 l_api_version CONSTANT NUMBER := 1;
1941 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1942 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1943 l_vcr_rec vcr_rec_type := p_vcr_rec;
1944 l_def_vcr_rec vcr_rec_type;
1945 l_row_notfound BOOLEAN := TRUE;
1946 ----------------------------------
1947 -- FUNCTION populate_new_record --
1948 ----------------------------------
1949 FUNCTION populate_new_record (
1950 p_vcr_rec IN vcr_rec_type,
1951 x_vcr_rec OUT NOCOPY vcr_rec_type
1952 ) RETURN VARCHAR2 IS
1953 l_vcr_rec vcr_rec_type;
1954 l_row_notfound BOOLEAN := TRUE;
1955 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1956 BEGIN
1957 x_vcr_rec := p_vcr_rec;
1958 -- Get current database values
1959 l_vcr_rec := get_rec(p_vcr_rec, l_return_status);
1960 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1961 IF (x_vcr_rec.id = OKL_API.G_MISS_NUM)
1962 THEN
1963 x_vcr_rec.id := l_vcr_rec.id;
1964 END IF;
1965 IF (x_vcr_rec.object_version_number = OKL_API.G_MISS_NUM)
1966 THEN
1967 x_vcr_rec.object_version_number := l_vcr_rec.object_version_number;
1968 END IF;
1969 IF (x_vcr_rec.change_request_number = OKL_API.G_MISS_NUM)
1970 THEN
1971 x_vcr_rec.change_request_number := l_vcr_rec.change_request_number;
1972 END IF;
1973 IF (x_vcr_rec.chr_id = OKL_API.G_MISS_NUM)
1974 THEN
1975 x_vcr_rec.chr_id := l_vcr_rec.chr_id;
1976 END IF;
1977 IF (x_vcr_rec.change_type_code = OKL_API.G_MISS_CHAR)
1978 THEN
1979 x_vcr_rec.change_type_code := l_vcr_rec.change_type_code;
1980 END IF;
1981 IF (x_vcr_rec.status_code = OKL_API.G_MISS_CHAR)
1982 THEN
1983 x_vcr_rec.status_code := l_vcr_rec.status_code;
1984 END IF;
1985 IF (x_vcr_rec.request_date = OKL_API.G_MISS_DATE)
1986 THEN
1987 x_vcr_rec.request_date := l_vcr_rec.request_date;
1988 END IF;
1989 IF (x_vcr_rec.effective_date = OKL_API.G_MISS_DATE)
1990 THEN
1991 x_vcr_rec.effective_date := l_vcr_rec.effective_date;
1992 END IF;
1993 IF (x_vcr_rec.approved_date = OKL_API.G_MISS_DATE)
1994 THEN
1995 x_vcr_rec.approved_date := l_vcr_rec.approved_date;
1996 END IF;
1997 IF (x_vcr_rec.rejected_date = OKL_API.G_MISS_DATE)
1998 THEN
1999 x_vcr_rec.rejected_date := l_vcr_rec.rejected_date;
2000 END IF;
2001 IF (x_vcr_rec.ineffective_date = OKL_API.G_MISS_DATE)
2002 THEN
2003 x_vcr_rec.ineffective_date := l_vcr_rec.ineffective_date;
2004 END IF;
2005 IF (x_vcr_rec.applied_date = OKL_API.G_MISS_DATE)
2006 THEN
2007 x_vcr_rec.applied_date := l_vcr_rec.applied_date;
2008 END IF;
2009 IF (x_vcr_rec.attribute_category = OKL_API.G_MISS_CHAR)
2010 THEN
2011 x_vcr_rec.attribute_category := l_vcr_rec.attribute_category;
2012 END IF;
2013 IF (x_vcr_rec.attribute1 = OKL_API.G_MISS_CHAR)
2014 THEN
2015 x_vcr_rec.attribute1 := l_vcr_rec.attribute1;
2016 END IF;
2017 IF (x_vcr_rec.attribute2 = OKL_API.G_MISS_CHAR)
2018 THEN
2019 x_vcr_rec.attribute2 := l_vcr_rec.attribute2;
2020 END IF;
2021 IF (x_vcr_rec.attribute3 = OKL_API.G_MISS_CHAR)
2022 THEN
2023 x_vcr_rec.attribute3 := l_vcr_rec.attribute3;
2024 END IF;
2025 IF (x_vcr_rec.attribute4 = OKL_API.G_MISS_CHAR)
2026 THEN
2027 x_vcr_rec.attribute4 := l_vcr_rec.attribute4;
2028 END IF;
2029 IF (x_vcr_rec.attribute5 = OKL_API.G_MISS_CHAR)
2030 THEN
2031 x_vcr_rec.attribute5 := l_vcr_rec.attribute5;
2032 END IF;
2033 IF (x_vcr_rec.attribute6 = OKL_API.G_MISS_CHAR)
2034 THEN
2035 x_vcr_rec.attribute6 := l_vcr_rec.attribute6;
2036 END IF;
2037 IF (x_vcr_rec.attribute7 = OKL_API.G_MISS_CHAR)
2038 THEN
2039 x_vcr_rec.attribute7 := l_vcr_rec.attribute7;
2040 END IF;
2041 IF (x_vcr_rec.attribute8 = OKL_API.G_MISS_CHAR)
2042 THEN
2043 x_vcr_rec.attribute8 := l_vcr_rec.attribute8;
2044 END IF;
2045 IF (x_vcr_rec.attribute9 = OKL_API.G_MISS_CHAR)
2046 THEN
2047 x_vcr_rec.attribute9 := l_vcr_rec.attribute9;
2048 END IF;
2049 IF (x_vcr_rec.attribute10 = OKL_API.G_MISS_CHAR)
2050 THEN
2051 x_vcr_rec.attribute10 := l_vcr_rec.attribute10;
2052 END IF;
2053 IF (x_vcr_rec.attribute11 = OKL_API.G_MISS_CHAR)
2054 THEN
2055 x_vcr_rec.attribute11 := l_vcr_rec.attribute11;
2056 END IF;
2057 IF (x_vcr_rec.attribute12 = OKL_API.G_MISS_CHAR)
2058 THEN
2059 x_vcr_rec.attribute12 := l_vcr_rec.attribute12;
2060 END IF;
2061 IF (x_vcr_rec.attribute13 = OKL_API.G_MISS_CHAR)
2062 THEN
2063 x_vcr_rec.attribute13 := l_vcr_rec.attribute13;
2064 END IF;
2065 IF (x_vcr_rec.attribute14 = OKL_API.G_MISS_CHAR)
2066 THEN
2067 x_vcr_rec.attribute14 := l_vcr_rec.attribute14;
2068 END IF;
2069 IF (x_vcr_rec.attribute15 = OKL_API.G_MISS_CHAR)
2070 THEN
2071 x_vcr_rec.attribute15 := l_vcr_rec.attribute15;
2072 END IF;
2073 IF (x_vcr_rec.request_id = OKL_API.G_MISS_NUM)
2074 THEN
2075 x_vcr_rec.request_id := l_vcr_rec.request_id;
2076 END IF;
2077 IF (x_vcr_rec.program_application_id = OKL_API.G_MISS_NUM)
2078 THEN
2079 x_vcr_rec.program_application_id := l_vcr_rec.program_application_id;
2080 END IF;
2081 IF (x_vcr_rec.program_id = OKL_API.G_MISS_NUM)
2082 THEN
2083 x_vcr_rec.program_id := l_vcr_rec.program_id;
2084 END IF;
2085 IF (x_vcr_rec.program_update_date = OKL_API.G_MISS_DATE)
2086 THEN
2087 x_vcr_rec.program_update_date := l_vcr_rec.program_update_date;
2088 END IF;
2089 IF (x_vcr_rec.created_by = OKL_API.G_MISS_NUM)
2090 THEN
2091 x_vcr_rec.created_by := l_vcr_rec.created_by;
2092 END IF;
2093 IF (x_vcr_rec.creation_date = OKL_API.G_MISS_DATE)
2094 THEN
2095 x_vcr_rec.creation_date := l_vcr_rec.creation_date;
2096 END IF;
2097 IF (x_vcr_rec.last_updated_by = OKL_API.G_MISS_NUM)
2098 THEN
2099 x_vcr_rec.last_updated_by := l_vcr_rec.last_updated_by;
2100 END IF;
2101 IF (x_vcr_rec.last_update_date = OKL_API.G_MISS_DATE)
2102 THEN
2103 x_vcr_rec.last_update_date := l_vcr_rec.last_update_date;
2104 END IF;
2105 IF (x_vcr_rec.last_update_login = OKL_API.G_MISS_NUM)
2106 THEN
2107 x_vcr_rec.last_update_login := l_vcr_rec.last_update_login;
2108 END IF;
2109 END IF;
2110 RETURN(l_return_status);
2111 END populate_new_record;
2112 -----------------------------------------------
2113 -- Set_Attributes for:OKL_VP_CHANGE_REQUESTS --
2114 -----------------------------------------------
2115 FUNCTION Set_Attributes (
2116 p_vcr_rec IN vcr_rec_type,
2117 x_vcr_rec OUT NOCOPY vcr_rec_type
2118 ) RETURN VARCHAR2 IS
2119 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2120 BEGIN
2121 x_vcr_rec := p_vcr_rec;
2122 x_vcr_rec.OBJECT_VERSION_NUMBER := p_vcr_rec.OBJECT_VERSION_NUMBER + 1;
2123 RETURN(l_return_status);
2124 END Set_Attributes;
2125 BEGIN
2126 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2127 p_init_msg_list,
2128 '_PVT',
2129 x_return_status);
2130 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2131 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2132 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2133 RAISE OKL_API.G_EXCEPTION_ERROR;
2134 END IF;
2135 --- Setting item attributes
2136 l_return_status := Set_Attributes(
2137 p_vcr_rec, -- IN
2138 l_vcr_rec); -- OUT
2139 --- If any errors happen abort API
2140 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2141 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2142 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2143 RAISE OKL_API.G_EXCEPTION_ERROR;
2144 END IF;
2145 l_return_status := populate_new_record(l_vcr_rec, l_def_vcr_rec);
2146 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2149 RAISE OKL_API.G_EXCEPTION_ERROR;
2150 END IF;
2151 UPDATE OKL_VP_CHANGE_REQUESTS
2152 SET OBJECT_VERSION_NUMBER = l_def_vcr_rec.object_version_number,
2153 CHANGE_REQUEST_NUMBER = l_def_vcr_rec.change_request_number,
2154 CHR_ID = l_def_vcr_rec.chr_id,
2155 CHANGE_TYPE_CODE = l_def_vcr_rec.change_type_code,
2156 STATUS_CODE = l_def_vcr_rec.status_code,
2157 REQUEST_DATE = l_def_vcr_rec.request_date,
2158 EFFECTIVE_DATE = l_def_vcr_rec.effective_date,
2159 APPROVED_DATE = l_def_vcr_rec.approved_date,
2160 REJECTED_DATE = l_def_vcr_rec.rejected_date,
2161 INEFFECTIVE_DATE = l_def_vcr_rec.ineffective_date,
2162 APPLIED_DATE = l_def_vcr_rec.applied_date,
2163 ATTRIBUTE_CATEGORY = l_def_vcr_rec.attribute_category,
2164 ATTRIBUTE1 = l_def_vcr_rec.attribute1,
2165 ATTRIBUTE2 = l_def_vcr_rec.attribute2,
2166 ATTRIBUTE3 = l_def_vcr_rec.attribute3,
2167 ATTRIBUTE4 = l_def_vcr_rec.attribute4,
2168 ATTRIBUTE5 = l_def_vcr_rec.attribute5,
2169 ATTRIBUTE6 = l_def_vcr_rec.attribute6,
2170 ATTRIBUTE7 = l_def_vcr_rec.attribute7,
2171 ATTRIBUTE8 = l_def_vcr_rec.attribute8,
2172 ATTRIBUTE9 = l_def_vcr_rec.attribute9,
2173 ATTRIBUTE10 = l_def_vcr_rec.attribute10,
2174 ATTRIBUTE11 = l_def_vcr_rec.attribute11,
2175 ATTRIBUTE12 = l_def_vcr_rec.attribute12,
2176 ATTRIBUTE13 = l_def_vcr_rec.attribute13,
2177 ATTRIBUTE14 = l_def_vcr_rec.attribute14,
2178 ATTRIBUTE15 = l_def_vcr_rec.attribute15,
2179 REQUEST_ID = l_def_vcr_rec.request_id,
2180 PROGRAM_APPLICATION_ID = l_def_vcr_rec.program_application_id,
2181 PROGRAM_ID = l_def_vcr_rec.program_id,
2182 PROGRAM_UPDATE_DATE = l_def_vcr_rec.program_update_date,
2183 CREATED_BY = l_def_vcr_rec.created_by,
2184 CREATION_DATE = l_def_vcr_rec.creation_date,
2185 LAST_UPDATED_BY = l_def_vcr_rec.last_updated_by,
2186 LAST_UPDATE_DATE = l_def_vcr_rec.last_update_date,
2187 LAST_UPDATE_LOGIN = l_def_vcr_rec.last_update_login
2188 WHERE ID = l_def_vcr_rec.id;
2189
2190 x_vcr_rec := l_vcr_rec;
2191 x_return_status := l_return_status;
2192 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2193 EXCEPTION
2194 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2195 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2196 (
2197 l_api_name,
2198 G_PKG_NAME,
2199 'OKL_API.G_RET_STS_ERROR',
2200 x_msg_count,
2201 x_msg_data,
2202 '_PVT'
2203 );
2204 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2205 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2206 (
2207 l_api_name,
2208 G_PKG_NAME,
2209 'OKL_API.G_RET_STS_UNEXP_ERROR',
2210 x_msg_count,
2211 x_msg_data,
2212 '_PVT'
2213 );
2214 WHEN OTHERS THEN
2215 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2216 (
2217 l_api_name,
2218 G_PKG_NAME,
2219 'OTHERS',
2220 x_msg_count,
2221 x_msg_data,
2222 '_PVT'
2223 );
2224 END update_row;
2225 ---------------------------------------------
2226 -- update_row for:OKL_VP_CHANGE_REQUESTS_V --
2227 ---------------------------------------------
2228 PROCEDURE update_row(
2229 p_api_version IN NUMBER,
2230 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2231 x_return_status OUT NOCOPY VARCHAR2,
2232 x_msg_count OUT NOCOPY NUMBER,
2233 x_msg_data OUT NOCOPY VARCHAR2,
2234 p_vcrv_rec IN vcrv_rec_type,
2235 x_vcrv_rec OUT NOCOPY vcrv_rec_type) IS
2236
2237 l_api_version CONSTANT NUMBER := 1;
2238 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2239 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2240 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
2241 l_def_vcrv_rec vcrv_rec_type;
2242 l_db_vcrv_rec vcrv_rec_type;
2243 l_vcr_rec vcr_rec_type;
2244 lx_vcr_rec vcr_rec_type;
2245 -------------------------------
2246 -- FUNCTION fill_who_columns --
2247 -------------------------------
2248 FUNCTION fill_who_columns (
2249 p_vcrv_rec IN vcrv_rec_type
2250 ) RETURN vcrv_rec_type IS
2251 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
2252 BEGIN
2253 l_vcrv_rec.LAST_UPDATE_DATE := SYSDATE;
2254 l_vcrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2255 l_vcrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2256 RETURN(l_vcrv_rec);
2257 END fill_who_columns;
2258 ----------------------------------
2259 -- FUNCTION populate_new_record --
2260 ----------------------------------
2261 FUNCTION populate_new_record (
2262 p_vcrv_rec IN vcrv_rec_type,
2263 x_vcrv_rec OUT NOCOPY vcrv_rec_type
2264 ) RETURN VARCHAR2 IS
2265 l_row_notfound BOOLEAN := TRUE;
2266 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2267 BEGIN
2268 x_vcrv_rec := p_vcrv_rec;
2269 -- Get current database values
2270 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2271 -- so it may be verified through LOCK_ROW.
2272 l_db_vcrv_rec := get_rec(p_vcrv_rec, l_return_status);
2273 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2274 IF (x_vcrv_rec.id = OKL_API.G_MISS_NUM)
2275 THEN
2276 x_vcrv_rec.id := l_db_vcrv_rec.id;
2277 END IF;
2278 IF (x_vcrv_rec.change_request_number = OKL_API.G_MISS_NUM)
2279 THEN
2280 x_vcrv_rec.change_request_number := l_db_vcrv_rec.change_request_number;
2281 END IF;
2282 IF (x_vcrv_rec.chr_id = OKL_API.G_MISS_NUM)
2283 THEN
2284 x_vcrv_rec.chr_id := l_db_vcrv_rec.chr_id;
2285 END IF;
2286 IF (x_vcrv_rec.change_type_code = OKL_API.G_MISS_CHAR)
2287 THEN
2288 x_vcrv_rec.change_type_code := l_db_vcrv_rec.change_type_code;
2289 END IF;
2290 IF (x_vcrv_rec.status_code = OKL_API.G_MISS_CHAR)
2291 THEN
2292 x_vcrv_rec.status_code := l_db_vcrv_rec.status_code;
2293 END IF;
2294 IF (x_vcrv_rec.request_date = OKL_API.G_MISS_DATE)
2295 THEN
2296 x_vcrv_rec.request_date := l_db_vcrv_rec.request_date;
2297 END IF;
2298 IF (x_vcrv_rec.effective_date = OKL_API.G_MISS_DATE)
2299 THEN
2300 x_vcrv_rec.effective_date := l_db_vcrv_rec.effective_date;
2301 END IF;
2302 IF (x_vcrv_rec.approved_date = OKL_API.G_MISS_DATE)
2303 THEN
2304 x_vcrv_rec.approved_date := l_db_vcrv_rec.approved_date;
2305 END IF;
2306 IF (x_vcrv_rec.rejected_date = OKL_API.G_MISS_DATE)
2307 THEN
2308 x_vcrv_rec.rejected_date := l_db_vcrv_rec.rejected_date;
2309 END IF;
2310 IF (x_vcrv_rec.ineffective_date = OKL_API.G_MISS_DATE)
2311 THEN
2312 x_vcrv_rec.ineffective_date := l_db_vcrv_rec.ineffective_date;
2313 END IF;
2314 IF (x_vcrv_rec.applied_date = OKL_API.G_MISS_DATE)
2315 THEN
2316 x_vcrv_rec.applied_date := l_db_vcrv_rec.applied_date;
2317 END IF;
2318 IF (x_vcrv_rec.attribute_category = OKL_API.G_MISS_CHAR)
2319 THEN
2320 x_vcrv_rec.attribute_category := l_db_vcrv_rec.attribute_category;
2321 END IF;
2322 IF (x_vcrv_rec.attribute1 = OKL_API.G_MISS_CHAR)
2323 THEN
2324 x_vcrv_rec.attribute1 := l_db_vcrv_rec.attribute1;
2325 END IF;
2326 IF (x_vcrv_rec.attribute2 = OKL_API.G_MISS_CHAR)
2327 THEN
2328 x_vcrv_rec.attribute2 := l_db_vcrv_rec.attribute2;
2329 END IF;
2330 IF (x_vcrv_rec.attribute3 = OKL_API.G_MISS_CHAR)
2331 THEN
2332 x_vcrv_rec.attribute3 := l_db_vcrv_rec.attribute3;
2333 END IF;
2334 IF (x_vcrv_rec.attribute4 = OKL_API.G_MISS_CHAR)
2335 THEN
2336 x_vcrv_rec.attribute4 := l_db_vcrv_rec.attribute4;
2337 END IF;
2338 IF (x_vcrv_rec.attribute5 = OKL_API.G_MISS_CHAR)
2339 THEN
2340 x_vcrv_rec.attribute5 := l_db_vcrv_rec.attribute5;
2341 END IF;
2342 IF (x_vcrv_rec.attribute6 = OKL_API.G_MISS_CHAR)
2343 THEN
2344 x_vcrv_rec.attribute6 := l_db_vcrv_rec.attribute6;
2345 END IF;
2346 IF (x_vcrv_rec.attribute7 = OKL_API.G_MISS_CHAR)
2347 THEN
2348 x_vcrv_rec.attribute7 := l_db_vcrv_rec.attribute7;
2349 END IF;
2350 IF (x_vcrv_rec.attribute8 = OKL_API.G_MISS_CHAR)
2351 THEN
2352 x_vcrv_rec.attribute8 := l_db_vcrv_rec.attribute8;
2353 END IF;
2354 IF (x_vcrv_rec.attribute9 = OKL_API.G_MISS_CHAR)
2355 THEN
2356 x_vcrv_rec.attribute9 := l_db_vcrv_rec.attribute9;
2357 END IF;
2358 IF (x_vcrv_rec.attribute10 = OKL_API.G_MISS_CHAR)
2359 THEN
2360 x_vcrv_rec.attribute10 := l_db_vcrv_rec.attribute10;
2361 END IF;
2362 IF (x_vcrv_rec.attribute11 = OKL_API.G_MISS_CHAR)
2363 THEN
2364 x_vcrv_rec.attribute11 := l_db_vcrv_rec.attribute11;
2365 END IF;
2366 IF (x_vcrv_rec.attribute12 = OKL_API.G_MISS_CHAR)
2367 THEN
2368 x_vcrv_rec.attribute12 := l_db_vcrv_rec.attribute12;
2369 END IF;
2370 IF (x_vcrv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2371 THEN
2372 x_vcrv_rec.attribute13 := l_db_vcrv_rec.attribute13;
2373 END IF;
2374 IF (x_vcrv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2375 THEN
2376 x_vcrv_rec.attribute14 := l_db_vcrv_rec.attribute14;
2377 END IF;
2378 IF (x_vcrv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2379 THEN
2380 x_vcrv_rec.attribute15 := l_db_vcrv_rec.attribute15;
2381 END IF;
2382 IF (x_vcrv_rec.request_id = OKL_API.G_MISS_NUM)
2383 THEN
2384 x_vcrv_rec.request_id := l_db_vcrv_rec.request_id;
2385 END IF;
2386 IF (x_vcrv_rec.program_application_id = OKL_API.G_MISS_NUM)
2387 THEN
2388 x_vcrv_rec.program_application_id := l_db_vcrv_rec.program_application_id;
2389 END IF;
2390 IF (x_vcrv_rec.program_id = OKL_API.G_MISS_NUM)
2391 THEN
2392 x_vcrv_rec.program_id := l_db_vcrv_rec.program_id;
2393 END IF;
2394 IF (x_vcrv_rec.program_update_date = OKL_API.G_MISS_DATE)
2395 THEN
2396 x_vcrv_rec.program_update_date := l_db_vcrv_rec.program_update_date;
2397 END IF;
2398 IF (x_vcrv_rec.created_by = OKL_API.G_MISS_NUM)
2399 THEN
2400 x_vcrv_rec.created_by := l_db_vcrv_rec.created_by;
2401 END IF;
2402 IF (x_vcrv_rec.creation_date = OKL_API.G_MISS_DATE)
2403 THEN
2404 x_vcrv_rec.creation_date := l_db_vcrv_rec.creation_date;
2405 END IF;
2406 IF (x_vcrv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2407 THEN
2408 x_vcrv_rec.last_updated_by := l_db_vcrv_rec.last_updated_by;
2409 END IF;
2410 IF (x_vcrv_rec.last_update_date = OKL_API.G_MISS_DATE)
2411 THEN
2412 x_vcrv_rec.last_update_date := l_db_vcrv_rec.last_update_date;
2413 END IF;
2414 IF (x_vcrv_rec.last_update_login = OKL_API.G_MISS_NUM)
2415 THEN
2416 x_vcrv_rec.last_update_login := l_db_vcrv_rec.last_update_login;
2417 END IF;
2418 END IF;
2419 RETURN(l_return_status);
2420 END populate_new_record;
2421 -------------------------------------------------
2422 -- Set_Attributes for:OKL_VP_CHANGE_REQUESTS_V --
2423 -------------------------------------------------
2424 FUNCTION Set_Attributes (
2425 p_vcrv_rec IN vcrv_rec_type,
2426 x_vcrv_rec OUT NOCOPY vcrv_rec_type
2427 ) RETURN VARCHAR2 IS
2428 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2429 BEGIN
2430 x_vcrv_rec := p_vcrv_rec;
2431 RETURN(l_return_status);
2432 END Set_Attributes;
2433 BEGIN
2434 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2435 G_PKG_NAME,
2436 p_init_msg_list,
2437 l_api_version,
2438 p_api_version,
2439 '_PVT',
2440 x_return_status);
2441 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2442 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2443 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2444 RAISE OKL_API.G_EXCEPTION_ERROR;
2445 END IF;
2446 --- Setting item attributes
2447 l_return_status := Set_Attributes(
2448 p_vcrv_rec, -- IN
2449 x_vcrv_rec); -- OUT
2450 --- If any errors happen abort API
2451 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2453 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2454 RAISE OKL_API.G_EXCEPTION_ERROR;
2455 END IF;
2456 l_return_status := populate_new_record(l_vcrv_rec, l_def_vcrv_rec);
2457 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2458 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2459 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2460 RAISE OKL_API.G_EXCEPTION_ERROR;
2461 END IF;
2462 l_def_vcrv_rec := fill_who_columns(l_def_vcrv_rec);
2463 --- Validate all non-missing attributes (Item Level Validation)
2464 l_return_status := Validate_Attributes(l_def_vcrv_rec);
2465 --- If any errors happen abort API
2466 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2467 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2468 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2469 RAISE OKL_API.G_EXCEPTION_ERROR;
2470 END IF;
2471 l_return_status := Validate_Record(l_def_vcrv_rec, l_db_vcrv_rec);
2472 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2474 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2475 RAISE OKL_API.G_EXCEPTION_ERROR;
2476 END IF;
2477 --avsingh
2478 /*****************commented**************
2479 -- Lock the Record
2480 lock_row(
2481 p_api_version => p_api_version,
2482 p_init_msg_list => p_init_msg_list,
2483 x_return_status => l_return_status,
2484 x_msg_count => x_msg_count,
2485 x_msg_data => x_msg_data,
2486 p_vcrv_rec => p_vcrv_rec);
2487 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2488 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2489 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2490 RAISE OKL_API.G_EXCEPTION_ERROR;
2491 END IF;
2492 *****************commented**************/
2493 -----------------------------------------
2494 -- Move VIEW record to "Child" records --
2495 -----------------------------------------
2496 migrate(l_def_vcrv_rec, l_vcr_rec);
2497 -----------------------------------------------
2498 -- Call the UPDATE_ROW for each child record --
2499 -----------------------------------------------
2500 update_row(
2501 p_init_msg_list,
2502 l_return_status,
2503 x_msg_count,
2504 x_msg_data,
2505 l_vcr_rec,
2506 lx_vcr_rec
2507 );
2508 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2509 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2510 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2511 RAISE OKL_API.G_EXCEPTION_ERROR;
2512 END IF;
2513 migrate(lx_vcr_rec, l_def_vcrv_rec);
2514 x_vcrv_rec := l_def_vcrv_rec;
2515 x_return_status := l_return_status;
2516 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2517 EXCEPTION
2518 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2519 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2520 (
2521 l_api_name,
2522 G_PKG_NAME,
2523 'OKL_API.G_RET_STS_ERROR',
2524 x_msg_count,
2525 x_msg_data,
2526 '_PVT'
2527 );
2528 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2529 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2530 (
2531 l_api_name,
2532 G_PKG_NAME,
2533 'OKL_API.G_RET_STS_UNEXP_ERROR',
2534 x_msg_count,
2535 x_msg_data,
2536 '_PVT'
2537 );
2538 WHEN OTHERS THEN
2539 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2540 (
2541 l_api_name,
2542 G_PKG_NAME,
2543 'OTHERS',
2544 x_msg_count,
2545 x_msg_data,
2546 '_PVT'
2547 );
2548 END update_row;
2549 ----------------------------------------
2550 -- PL/SQL TBL update_row for:vcrv_tbl --
2551 ----------------------------------------
2552 PROCEDURE update_row(
2553 p_api_version IN NUMBER,
2554 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2555 x_return_status OUT NOCOPY VARCHAR2,
2556 x_msg_count OUT NOCOPY NUMBER,
2557 x_msg_data OUT NOCOPY VARCHAR2,
2558 p_vcrv_tbl IN vcrv_tbl_type,
2559 x_vcrv_tbl OUT NOCOPY vcrv_tbl_type,
2560 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2561
2562 l_api_version CONSTANT NUMBER := 1;
2563 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2564 i NUMBER := 0;
2565 BEGIN
2566 OKL_API.init_msg_list(p_init_msg_list);
2567 -- Make sure PL/SQL table has records in it before passing
2568 IF (p_vcrv_tbl.COUNT > 0) THEN
2569 i := p_vcrv_tbl.FIRST;
2570 LOOP
2571 DECLARE
2572 l_error_rec OKL_API.ERROR_REC_TYPE;
2573 BEGIN
2574 l_error_rec.api_name := l_api_name;
2575 l_error_rec.api_package := G_PKG_NAME;
2576 l_error_rec.idx := i;
2577 update_row (
2578 p_api_version => p_api_version,
2579 p_init_msg_list => OKL_API.G_FALSE,
2580 x_return_status => l_error_rec.error_type,
2581 x_msg_count => l_error_rec.msg_count,
2582 x_msg_data => l_error_rec.msg_data,
2583 p_vcrv_rec => p_vcrv_tbl(i),
2584 x_vcrv_rec => x_vcrv_tbl(i));
2585 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2586 l_error_rec.sqlcode := SQLCODE;
2587 load_error_tbl(l_error_rec, px_error_tbl);
2588 ELSE
2589 x_msg_count := l_error_rec.msg_count;
2590 x_msg_data := l_error_rec.msg_data;
2591 END IF;
2592 EXCEPTION
2593 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2594 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2595 l_error_rec.sqlcode := SQLCODE;
2596 load_error_tbl(l_error_rec, px_error_tbl);
2597 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2598 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2599 l_error_rec.sqlcode := SQLCODE;
2600 load_error_tbl(l_error_rec, px_error_tbl);
2601 WHEN OTHERS THEN
2602 l_error_rec.error_type := 'OTHERS';
2603 l_error_rec.sqlcode := SQLCODE;
2604 load_error_tbl(l_error_rec, px_error_tbl);
2605 END;
2606 EXIT WHEN (i = p_vcrv_tbl.LAST);
2607 i := p_vcrv_tbl.NEXT(i);
2608 END LOOP;
2609 END IF;
2610 -- Loop through the error_tbl to find the error with the highest severity
2611 -- and return it.
2612 x_return_status := find_highest_exception(px_error_tbl);
2613 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2614 EXCEPTION
2615 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2616 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2617 (
2618 l_api_name,
2619 G_PKG_NAME,
2620 'OKL_API.G_RET_STS_ERROR',
2621 x_msg_count,
2622 x_msg_data,
2623 '_PVT'
2624 );
2625 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2626 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2627 (
2628 l_api_name,
2629 G_PKG_NAME,
2630 'OKL_API.G_RET_STS_UNEXP_ERROR',
2631 x_msg_count,
2632 x_msg_data,
2633 '_PVT'
2634 );
2635 WHEN OTHERS THEN
2636 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2637 (
2638 l_api_name,
2639 G_PKG_NAME,
2640 'OTHERS',
2641 x_msg_count,
2642 x_msg_data,
2643 '_PVT'
2644 );
2645 END update_row;
2646
2647 ----------------------------------------
2648 -- PL/SQL TBL update_row for:VCRV_TBL --
2649 ----------------------------------------
2650 PROCEDURE update_row(
2651 p_api_version IN NUMBER,
2652 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2653 x_return_status OUT NOCOPY VARCHAR2,
2654 x_msg_count OUT NOCOPY NUMBER,
2655 x_msg_data OUT NOCOPY VARCHAR2,
2656 p_vcrv_tbl IN vcrv_tbl_type,
2657 x_vcrv_tbl OUT NOCOPY vcrv_tbl_type) IS
2658
2659 l_api_version CONSTANT NUMBER := 1;
2660 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2661 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2662 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2663 BEGIN
2664 OKL_API.init_msg_list(p_init_msg_list);
2665 -- Make sure PL/SQL table has records in it before passing
2666 IF (p_vcrv_tbl.COUNT > 0) THEN
2667 update_row (
2668 p_api_version => p_api_version,
2669 p_init_msg_list => OKL_API.G_FALSE,
2670 x_return_status => x_return_status,
2671 x_msg_count => x_msg_count,
2672 x_msg_data => x_msg_data,
2673 p_vcrv_tbl => p_vcrv_tbl,
2674 x_vcrv_tbl => x_vcrv_tbl,
2675 px_error_tbl => l_error_tbl);
2676 END IF;
2677 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2678 EXCEPTION
2679 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2680 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2681 (
2682 l_api_name,
2683 G_PKG_NAME,
2684 'OKL_API.G_RET_STS_ERROR',
2685 x_msg_count,
2686 x_msg_data,
2687 '_PVT'
2688 );
2689 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2690 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2691 (
2692 l_api_name,
2693 G_PKG_NAME,
2694 'OKL_API.G_RET_STS_UNEXP_ERROR',
2695 x_msg_count,
2696 x_msg_data,
2697 '_PVT'
2698 );
2699 WHEN OTHERS THEN
2700 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2701 (
2702 l_api_name,
2703 G_PKG_NAME,
2704 'OTHERS',
2705 x_msg_count,
2706 x_msg_data,
2707 '_PVT'
2708 );
2709 END update_row;
2710
2711 ---------------------------------------------------------------------------
2712 -- PROCEDURE delete_row
2713 ---------------------------------------------------------------------------
2714 -------------------------------------------
2715 -- delete_row for:OKL_VP_CHANGE_REQUESTS --
2716 -------------------------------------------
2717 PROCEDURE delete_row(
2718 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2719 x_return_status OUT NOCOPY VARCHAR2,
2720 x_msg_count OUT NOCOPY NUMBER,
2721 x_msg_data OUT NOCOPY VARCHAR2,
2722 p_vcr_rec IN vcr_rec_type) IS
2723
2724 l_api_version CONSTANT NUMBER := 1;
2725 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2726 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2727 l_vcr_rec vcr_rec_type := p_vcr_rec;
2728 l_row_notfound BOOLEAN := TRUE;
2729 BEGIN
2730 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2731 p_init_msg_list,
2732 '_PVT',
2733 x_return_status);
2734 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2735 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2736 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2737 RAISE OKL_API.G_EXCEPTION_ERROR;
2738 END IF;
2739
2740 DELETE FROM OKL_VP_CHANGE_REQUESTS
2741 WHERE ID = p_vcr_rec.id;
2742
2743 x_return_status := l_return_status;
2744 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2745 EXCEPTION
2746 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2747 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2748 (
2749 l_api_name,
2750 G_PKG_NAME,
2751 'OKL_API.G_RET_STS_ERROR',
2752 x_msg_count,
2753 x_msg_data,
2754 '_PVT'
2755 );
2756 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2757 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2758 (
2759 l_api_name,
2760 G_PKG_NAME,
2761 'OKL_API.G_RET_STS_UNEXP_ERROR',
2762 x_msg_count,
2763 x_msg_data,
2764 '_PVT'
2765 );
2766 WHEN OTHERS THEN
2767 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2768 (
2769 l_api_name,
2770 G_PKG_NAME,
2771 'OTHERS',
2772 x_msg_count,
2773 x_msg_data,
2774 '_PVT'
2775 );
2776 END delete_row;
2777 ---------------------------------------------
2778 -- delete_row for:OKL_VP_CHANGE_REQUESTS_V --
2779 ---------------------------------------------
2780 PROCEDURE delete_row(
2781 p_api_version IN NUMBER,
2782 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2783 x_return_status OUT NOCOPY VARCHAR2,
2784 x_msg_count OUT NOCOPY NUMBER,
2785 x_msg_data OUT NOCOPY VARCHAR2,
2786 p_vcrv_rec IN vcrv_rec_type) IS
2787
2788 l_api_version CONSTANT NUMBER := 1;
2789 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2790 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2791 l_vcrv_rec vcrv_rec_type := p_vcrv_rec;
2792 l_vcr_rec vcr_rec_type;
2793 BEGIN
2794 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2795 G_PKG_NAME,
2796 p_init_msg_list,
2797 l_api_version,
2798 p_api_version,
2799 '_PVT',
2800 x_return_status);
2801 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2802 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2803 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2804 RAISE OKL_API.G_EXCEPTION_ERROR;
2805 END IF;
2806 -----------------------------------------
2807 -- Move VIEW record to "Child" records --
2808 -----------------------------------------
2809 migrate(l_vcrv_rec, l_vcr_rec);
2810 -----------------------------------------------
2811 -- Call the DELETE_ROW for each child record --
2812 -----------------------------------------------
2813 delete_row(
2814 p_init_msg_list,
2815 l_return_status,
2816 x_msg_count,
2817 x_msg_data,
2818 l_vcr_rec
2819 );
2820 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2821 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2822 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2823 RAISE OKL_API.G_EXCEPTION_ERROR;
2824 END IF;
2825 x_return_status := l_return_status;
2826 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2827 EXCEPTION
2828 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2829 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2830 (
2831 l_api_name,
2832 G_PKG_NAME,
2833 'OKL_API.G_RET_STS_ERROR',
2834 x_msg_count,
2835 x_msg_data,
2836 '_PVT'
2837 );
2838 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2839 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2840 (
2841 l_api_name,
2842 G_PKG_NAME,
2843 'OKL_API.G_RET_STS_UNEXP_ERROR',
2844 x_msg_count,
2845 x_msg_data,
2846 '_PVT'
2847 );
2848 WHEN OTHERS THEN
2849 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2850 (
2851 l_api_name,
2852 G_PKG_NAME,
2853 'OTHERS',
2854 x_msg_count,
2855 x_msg_data,
2856 '_PVT'
2857 );
2858 END delete_row;
2859 --------------------------------------------------------
2860 -- PL/SQL TBL delete_row for:OKL_VP_CHANGE_REQUESTS_V --
2861 --------------------------------------------------------
2862 PROCEDURE delete_row(
2863 p_api_version IN NUMBER,
2864 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2865 x_return_status OUT NOCOPY VARCHAR2,
2866 x_msg_count OUT NOCOPY NUMBER,
2867 x_msg_data OUT NOCOPY VARCHAR2,
2868 p_vcrv_tbl IN vcrv_tbl_type,
2869 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2870
2871 l_api_version CONSTANT NUMBER := 1;
2872 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2873 i NUMBER := 0;
2874 BEGIN
2875 OKL_API.init_msg_list(p_init_msg_list);
2876 -- Make sure PL/SQL table has records in it before passing
2877 IF (p_vcrv_tbl.COUNT > 0) THEN
2878 i := p_vcrv_tbl.FIRST;
2879 LOOP
2880 DECLARE
2881 l_error_rec OKL_API.ERROR_REC_TYPE;
2882 BEGIN
2883 l_error_rec.api_name := l_api_name;
2884 l_error_rec.api_package := G_PKG_NAME;
2885 l_error_rec.idx := i;
2886 delete_row (
2887 p_api_version => p_api_version,
2888 p_init_msg_list => OKL_API.G_FALSE,
2889 x_return_status => l_error_rec.error_type,
2890 x_msg_count => l_error_rec.msg_count,
2891 x_msg_data => l_error_rec.msg_data,
2892 p_vcrv_rec => p_vcrv_tbl(i));
2893 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2894 l_error_rec.sqlcode := SQLCODE;
2895 load_error_tbl(l_error_rec, px_error_tbl);
2896 ELSE
2897 x_msg_count := l_error_rec.msg_count;
2898 x_msg_data := l_error_rec.msg_data;
2899 END IF;
2900 EXCEPTION
2901 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2902 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2903 l_error_rec.sqlcode := SQLCODE;
2904 load_error_tbl(l_error_rec, px_error_tbl);
2905 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2906 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2907 l_error_rec.sqlcode := SQLCODE;
2908 load_error_tbl(l_error_rec, px_error_tbl);
2909 WHEN OTHERS THEN
2910 l_error_rec.error_type := 'OTHERS';
2911 l_error_rec.sqlcode := SQLCODE;
2912 load_error_tbl(l_error_rec, px_error_tbl);
2913 END;
2914 EXIT WHEN (i = p_vcrv_tbl.LAST);
2915 i := p_vcrv_tbl.NEXT(i);
2916 END LOOP;
2917 END IF;
2918 -- Loop through the error_tbl to find the error with the highest severity
2919 -- and return it.
2920 x_return_status := find_highest_exception(px_error_tbl);
2921 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2922 EXCEPTION
2923 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2924 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2925 (
2926 l_api_name,
2927 G_PKG_NAME,
2928 'OKL_API.G_RET_STS_ERROR',
2929 x_msg_count,
2930 x_msg_data,
2931 '_PVT'
2932 );
2933 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2934 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2935 (
2936 l_api_name,
2937 G_PKG_NAME,
2938 'OKL_API.G_RET_STS_UNEXP_ERROR',
2939 x_msg_count,
2940 x_msg_data,
2941 '_PVT'
2942 );
2943 WHEN OTHERS THEN
2944 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2945 (
2946 l_api_name,
2947 G_PKG_NAME,
2948 'OTHERS',
2949 x_msg_count,
2950 x_msg_data,
2951 '_PVT'
2952 );
2953 END delete_row;
2954
2955 --------------------------------------------------------
2956 -- PL/SQL TBL delete_row for:OKL_VP_CHANGE_REQUESTS_V --
2957 --------------------------------------------------------
2958 PROCEDURE delete_row(
2959 p_api_version IN NUMBER,
2960 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2961 x_return_status OUT NOCOPY VARCHAR2,
2962 x_msg_count OUT NOCOPY NUMBER,
2963 x_msg_data OUT NOCOPY VARCHAR2,
2964 p_vcrv_tbl IN vcrv_tbl_type) IS
2965
2966 l_api_version CONSTANT NUMBER := 1;
2967 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2968 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2969 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2970 BEGIN
2971 OKL_API.init_msg_list(p_init_msg_list);
2972 -- Make sure PL/SQL table has records in it before passing
2973 IF (p_vcrv_tbl.COUNT > 0) THEN
2974 delete_row (
2975 p_api_version => p_api_version,
2976 p_init_msg_list => OKL_API.G_FALSE,
2977 x_return_status => x_return_status,
2978 x_msg_count => x_msg_count,
2979 x_msg_data => x_msg_data,
2980 p_vcrv_tbl => p_vcrv_tbl,
2981 px_error_tbl => l_error_tbl);
2982 END IF;
2983 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2984 EXCEPTION
2985 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2986 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2987 (
2988 l_api_name,
2989 G_PKG_NAME,
2990 'OKL_API.G_RET_STS_ERROR',
2991 x_msg_count,
2992 x_msg_data,
2993 '_PVT'
2994 );
2995 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2996 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2997 (
2998 l_api_name,
2999 G_PKG_NAME,
3000 'OKL_API.G_RET_STS_UNEXP_ERROR',
3001 x_msg_count,
3002 x_msg_data,
3003 '_PVT'
3004 );
3005 WHEN OTHERS THEN
3006 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3007 (
3008 l_api_name,
3009 G_PKG_NAME,
3010 'OTHERS',
3011 x_msg_count,
3012 x_msg_data,
3013 '_PVT'
3014 );
3015 END delete_row;
3016
3017 END OKL_VCR_PVT;