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