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