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