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