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