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