[Home] [Help]
PACKAGE BODY: APPS.OKS_BSL_PVT
Source
1 PACKAGE BODY OKS_BSL_PVT AS
2 /* $Header: OKSSBSLB.pls 120.0 2005/05/25 18:24:13 appldev noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 null;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 null;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 null;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKS_BILL_SUB_LINES
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_bsl_rec IN bsl_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN bsl_rec_type IS
42 CURSOR bsl_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 BCL_ID,
46 CLE_ID,
47 OBJECT_VERSION_NUMBER,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_DATE,
52 AVERAGE,
53 AMOUNT,
54 MANUAL_CREDIT,
55 DATE_BILLED_FROM,
56 DATE_BILLED_TO,
57 DATE_TO_INTERFACE,
58 LAST_UPDATE_LOGIN,
59 ATTRIBUTE_CATEGORY,
60 ATTRIBUTE1,
61 ATTRIBUTE2,
62 ATTRIBUTE3,
63 ATTRIBUTE4,
64 ATTRIBUTE5,
65 ATTRIBUTE6,
66 ATTRIBUTE7,
67 ATTRIBUTE8,
68 ATTRIBUTE9,
69 ATTRIBUTE10,
70 ATTRIBUTE11,
71 ATTRIBUTE12,
72 ATTRIBUTE13,
73 ATTRIBUTE14,
74 ATTRIBUTE15
75 FROM Oks_Bill_Sub_Lines
76 WHERE oks_bill_sub_lines.id = p_id;
77 l_bsl_pk bsl_pk_csr%ROWTYPE;
78 l_bsl_rec bsl_rec_type;
79 BEGIN
80 x_no_data_found := TRUE;
81 -- Get current database values
82 OPEN bsl_pk_csr (p_bsl_rec.id);
83 FETCH bsl_pk_csr INTO
84 l_bsl_rec.ID,
85 l_bsl_rec.BCL_ID,
86 l_bsl_rec.CLE_ID,
87 l_bsl_rec.OBJECT_VERSION_NUMBER,
88 l_bsl_rec.CREATED_BY,
89 l_bsl_rec.CREATION_DATE,
90 l_bsl_rec.LAST_UPDATED_BY,
91 l_bsl_rec.LAST_UPDATE_DATE,
92 l_bsl_rec.AVERAGE,
93 l_bsl_rec.AMOUNT,
94 l_bsl_rec.MANUAL_CREDIT,
95 l_bsl_rec.DATE_BILLED_FROM,
96 l_bsl_rec.DATE_BILLED_TO,
97 l_bsl_rec.DATE_TO_INTERFACE,
98 l_bsl_rec.LAST_UPDATE_LOGIN,
99 l_bsl_rec.ATTRIBUTE_CATEGORY,
100 l_bsl_rec.ATTRIBUTE1,
101 l_bsl_rec.ATTRIBUTE2,
102 l_bsl_rec.ATTRIBUTE3,
103 l_bsl_rec.ATTRIBUTE4,
104 l_bsl_rec.ATTRIBUTE5,
105 l_bsl_rec.ATTRIBUTE6,
106 l_bsl_rec.ATTRIBUTE7,
107 l_bsl_rec.ATTRIBUTE8,
108 l_bsl_rec.ATTRIBUTE9,
109 l_bsl_rec.ATTRIBUTE10,
110 l_bsl_rec.ATTRIBUTE11,
111 l_bsl_rec.ATTRIBUTE12,
112 l_bsl_rec.ATTRIBUTE13,
113 l_bsl_rec.ATTRIBUTE14,
114 l_bsl_rec.ATTRIBUTE15;
115 x_no_data_found := bsl_pk_csr%NOTFOUND;
116 CLOSE bsl_pk_csr;
117 RETURN(l_bsl_rec);
118 END get_rec;
119
120 FUNCTION get_rec (
121 p_bsl_rec IN bsl_rec_type
122 ) RETURN bsl_rec_type IS
123 l_row_notfound BOOLEAN := TRUE;
124 BEGIN
125 RETURN(get_rec(p_bsl_rec, l_row_notfound));
126 END get_rec;
127 ---------------------------------------------------------------------------
128 -- FUNCTION get_rec for: OKS_BILL_SUB_LINES_V
129 ---------------------------------------------------------------------------
130 FUNCTION get_rec (
131 p_bslv_rec IN bslv_rec_type,
132 x_no_data_found OUT NOCOPY BOOLEAN
133 ) RETURN bslv_rec_type IS
134 CURSOR oks_bslv_pk_csr (p_id IN NUMBER) IS
135 SELECT
136 ID,
137 OBJECT_VERSION_NUMBER,
138 BCL_ID,
139 CLE_ID,
140 AVERAGE,
141 AMOUNT,
142 MANUAL_CREDIT,
143 DATE_BILLED_FROM,
144 DATE_BILLED_TO,
145 DATE_TO_INTERFACE,
146 ATTRIBUTE_CATEGORY,
147 ATTRIBUTE1,
148 ATTRIBUTE2,
149 ATTRIBUTE3,
150 ATTRIBUTE4,
151 ATTRIBUTE5,
152 ATTRIBUTE6,
153 ATTRIBUTE7,
154 ATTRIBUTE8,
155 ATTRIBUTE9,
156 ATTRIBUTE10,
157 ATTRIBUTE11,
158 ATTRIBUTE12,
159 ATTRIBUTE13,
160 ATTRIBUTE14,
161 ATTRIBUTE15,
162 CREATED_BY,
163 CREATION_DATE,
164 LAST_UPDATED_BY,
165 LAST_UPDATE_DATE,
166 LAST_UPDATE_LOGIN
167 FROM Oks_Bill_Sub_Lines_V
168 WHERE oks_bill_sub_lines_v.id = p_id;
169 l_oks_bslv_pk oks_bslv_pk_csr%ROWTYPE;
170 l_bslv_rec bslv_rec_type;
171 BEGIN
172 x_no_data_found := TRUE;
173 -- Get current database values
174 OPEN oks_bslv_pk_csr (p_bslv_rec.id);
175 FETCH oks_bslv_pk_csr INTO
176 l_bslv_rec.ID,
177 l_bslv_rec.OBJECT_VERSION_NUMBER,
178 l_bslv_rec.BCL_ID,
179 l_bslv_rec.CLE_ID,
180 l_bslv_rec.AVERAGE,
181 l_bslv_rec.AMOUNT,
182 l_bslv_rec.MANUAL_CREDIT,
183 l_bslv_rec.DATE_BILLED_FROM,
184 l_bslv_rec.DATE_BILLED_TO,
185 l_bslv_rec.DATE_TO_INTERFACE,
186 l_bslv_rec.ATTRIBUTE_CATEGORY,
187 l_bslv_rec.ATTRIBUTE1,
188 l_bslv_rec.ATTRIBUTE2,
189 l_bslv_rec.ATTRIBUTE3,
190 l_bslv_rec.ATTRIBUTE4,
191 l_bslv_rec.ATTRIBUTE5,
192 l_bslv_rec.ATTRIBUTE6,
193 l_bslv_rec.ATTRIBUTE7,
194 l_bslv_rec.ATTRIBUTE8,
195 l_bslv_rec.ATTRIBUTE9,
196 l_bslv_rec.ATTRIBUTE10,
197 l_bslv_rec.ATTRIBUTE11,
198 l_bslv_rec.ATTRIBUTE12,
199 l_bslv_rec.ATTRIBUTE13,
200 l_bslv_rec.ATTRIBUTE14,
201 l_bslv_rec.ATTRIBUTE15,
202 l_bslv_rec.CREATED_BY,
203 l_bslv_rec.CREATION_DATE,
204 l_bslv_rec.LAST_UPDATED_BY,
205 l_bslv_rec.LAST_UPDATE_DATE,
206 l_bslv_rec.LAST_UPDATE_LOGIN;
207 x_no_data_found := oks_bslv_pk_csr%NOTFOUND;
208 CLOSE oks_bslv_pk_csr;
209 RETURN(l_bslv_rec);
210 END get_rec;
211
212 FUNCTION get_rec (
213 p_bslv_rec IN bslv_rec_type
214 ) RETURN bslv_rec_type IS
215 l_row_notfound BOOLEAN := TRUE;
216 BEGIN
217 RETURN(get_rec(p_bslv_rec, l_row_notfound));
218 END get_rec;
219
220 ----------------------------------------------------------
221 -- FUNCTION null_out_defaults for: OKS_BILL_SUB_LINES_V --
222 ----------------------------------------------------------
223 FUNCTION null_out_defaults (
224 p_bslv_rec IN bslv_rec_type
225 ) RETURN bslv_rec_type IS
226 l_bslv_rec bslv_rec_type := p_bslv_rec;
227 BEGIN
228 IF (l_bslv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
229 l_bslv_rec.object_version_number := NULL;
230 END IF;
231 IF (l_bslv_rec.bcl_id = OKC_API.G_MISS_NUM) THEN
232 l_bslv_rec.bcl_id := NULL;
233 END IF;
234 IF (l_bslv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
235 l_bslv_rec.cle_id := NULL;
236 END IF;
237 IF (l_bslv_rec.average = OKC_API.G_MISS_NUM) THEN
238 l_bslv_rec.average := NULL;
239 END IF;
240 IF (l_bslv_rec.amount = OKC_API.G_MISS_NUM) THEN
241 l_bslv_rec.amount := NULL;
242 END IF;
243 IF (l_bslv_rec.MANUAL_CREDIT = OKC_API.G_MISS_NUM) THEN
244 l_bslv_rec.MANUAL_CREDIT := NULL;
245 END IF;
246 IF (l_bslv_rec.date_billed_from = OKC_API.G_MISS_DATE) THEN
247 l_bslv_rec.date_billed_from := NULL;
248 END IF;
249 IF (l_bslv_rec.date_billed_to = OKC_API.G_MISS_DATE) THEN
250 l_bslv_rec.date_billed_to := NULL;
251 END IF;
252 IF (l_bslv_rec.date_to_interface = OKC_API.G_MISS_DATE) THEN
253 l_bslv_rec.date_to_interface := NULL;
254 END IF;
255 IF (l_bslv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
256 l_bslv_rec.attribute_category := NULL;
257 END IF;
258 IF (l_bslv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
259 l_bslv_rec.attribute1 := NULL;
260 END IF;
261 IF (l_bslv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
262 l_bslv_rec.attribute2 := NULL;
263 END IF;
264 IF (l_bslv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
265 l_bslv_rec.attribute3 := NULL;
266 END IF;
267 IF (l_bslv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
268 l_bslv_rec.attribute4 := NULL;
269 END IF;
270 IF (l_bslv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
271 l_bslv_rec.attribute5 := NULL;
272 END IF;
273 IF (l_bslv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
274 l_bslv_rec.attribute6 := NULL;
275 END IF;
276 IF (l_bslv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
277 l_bslv_rec.attribute7 := NULL;
278 END IF;
279 IF (l_bslv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
280 l_bslv_rec.attribute8 := NULL;
281 END IF;
282 IF (l_bslv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
283 l_bslv_rec.attribute9 := NULL;
284 END IF;
285 IF (l_bslv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
286 l_bslv_rec.attribute10 := NULL;
287 END IF;
288 IF (l_bslv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
289 l_bslv_rec.attribute11 := NULL;
290 END IF;
291 IF (l_bslv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
292 l_bslv_rec.attribute12 := NULL;
293 END IF;
294 IF (l_bslv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
295 l_bslv_rec.attribute13 := NULL;
296 END IF;
297 IF (l_bslv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
298 l_bslv_rec.attribute14 := NULL;
299 END IF;
300 IF (l_bslv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
301 l_bslv_rec.attribute15 := NULL;
302 END IF;
303 IF (l_bslv_rec.created_by = OKC_API.G_MISS_NUM) THEN
304 l_bslv_rec.created_by := NULL;
305 END IF;
306 IF (l_bslv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
307 l_bslv_rec.creation_date := NULL;
308 END IF;
309 IF (l_bslv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
310 l_bslv_rec.last_updated_by := NULL;
311 END IF;
312 IF (l_bslv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
313 l_bslv_rec.last_update_date := NULL;
314 END IF;
315 IF (l_bslv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
316 l_bslv_rec.last_update_login := NULL;
317 END IF;
318 RETURN(l_bslv_rec);
319 END null_out_defaults;
320 ---------------------------------------------------------------------------
321 -- PROCEDURE Validate_Attributes
322 ---------------------------------------------------------------------------
323 PROCEDURE validate_id(x_return_status OUT NOCOPY varchar2,
324 p_id IN Number)
325 Is
326 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
327 Begin
328
329 x_return_status := OKC_API.G_RET_STS_SUCCESS;
330
331 If p_id = OKC_API.G_MISS_NUM OR
332 p_id IS NULL
333 Then
334 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
335 l_return_status := OKC_API.G_RET_STS_ERROR;
336 End If;
337
338 Exception
339 When G_EXCEPTION_HALT_VALIDATION THEN
340 x_return_status := l_return_status;
341 NULL;
342 When OTHERS THEN
343 -- store SQL error message on message stack for caller
344 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
345 p_msg_name => g_unexpected_error,
346 p_token1 => g_sqlcode_token,
347 p_token1_value => sqlcode,
348 p_token2 => g_sqlerrm_token,
349 p_token2_value => sqlerrm);
350
351 -- notify caller of an UNEXPECTED error
352 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
353 End validate_id;
354
355 PROCEDURE validate_bcl_id(x_return_status OUT NOCOPY varchar2,
356 P_bcl_id IN Number)
357 Is
358 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
359 Begin
360 x_return_status := OKC_API.G_RET_STS_SUCCESS;
361
362 -- call column length utility
363
364 ---------giving prob so commented
365 Exception
366 When G_EXCEPTION_HALT_VALIDATION Then
367 NULL;
368 When OTHERS Then
369 -- store SQL error message on message stack for caller
370 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
371 p_msg_name => g_unexpected_error,
372 p_token1 => g_sqlcode_token,
373 p_token1_value => sqlcode,
374 p_token2 => g_sqlerrm_token,
375 p_token2_value => sqlerrm);
376
377 -- notify caller of an UNEXPECTED error
378 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
379 END validate_bcl_id;
380
381
382 PROCEDURE validate_cle_id(x_return_status OUT NOCOPY varchar2,
383 P_cle_id IN Number)
384 Is
385 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
386 l_dummy_var VARCHAR2(1) := '?';
387 Cursor l_cle_Csr Is
388 select 'x'
389 from OKC_K_LINES_V
390 where id = P_cle_id;
391
392 Begin
393 If p_cle_id = OKC_API.G_MISS_NUM OR
394 p_cle_id IS NULL
395 Then
396 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cle_id');
397
398 l_return_status := OKC_API.G_RET_STS_ERROR;
399 RAISE G_EXCEPTION_HALT_VALIDATION;
400 End If;
401
402 If (p_cle_id <> OKC_API.G_MISS_NUM and
403 p_cle_id IS NOT NULL)
404 Then
405 Open l_cle_csr;
406 Fetch l_cle_csr Into l_dummy_var;
407 Close l_cle_csr;
408 -- if l_dummy_var still set to default, data was not found
409 If (l_dummy_var = '?') Then
410 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
411 p_msg_name => g_unexpected_error,
412 p_token1 => g_sqlcode_token,
413 p_token1_value => sqlcode,
414 p_token2 => g_sqlerrm_token,
415 p_token2_value => 'cle_id ');
416
417 -- notify caller of an error
418 x_return_status := OKC_API.G_RET_STS_ERROR;
419 End If;
420 End If;
421 Exception
422 When G_EXCEPTION_HALT_VALIDATION Then
423 NULL;
424 When OTHERS Then
425 -- store SQL error message on message stack for caller
426 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
427 p_msg_name => g_unexpected_error,
428 p_token1 => g_sqlcode_token,
429 p_token1_value => sqlcode,
430 p_token2 => g_sqlerrm_token,
431 p_token2_value => sqlerrm);
432
433 -- notify caller of an UNEXPECTED error
434 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
435 END validate_cle_id;
436
437 PROCEDURE validate_objvernum(x_return_status OUT NOCOPY varchar2,
438 P_object_version_number IN Number)
439 Is
440 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
441 Begin
442
443 x_return_status := OKC_API.G_RET_STS_SUCCESS;
444
445 If p_object_version_number = OKC_API.G_MISS_NUM OR
446 p_object_version_number IS NULL
447 Then
448 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
449 x_return_status := OKC_API.G_RET_STS_ERROR;
450 RAISE G_EXCEPTION_HALT_VALIDATION;
451 End If;
452
453 Exception
454 When G_EXCEPTION_HALT_VALIDATION Then
455 NULL;
456 When OTHERS Then
457 -- store SQL error message on message stack for caller
458 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
459 p_msg_name => g_unexpected_error,
460 p_token1 => g_sqlcode_token,
461 p_token1_value => sqlcode,
462 p_token2 => g_sqlerrm_token,
463 p_token2_value => sqlerrm);
464
465 -- notify caller of an UNEXPECTED error
466 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
467 END validate_objvernum;
468
469 PROCEDURE validate_average( x_return_status OUT NOCOPY varchar2,
470 P_average IN NUMBER)
471 Is
472 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
473 Begin
474
475
476 -- call column length utility
477 /*
478 OKC_UTIL.CHECK_LENGTH
479 (
480 p_view_name => 'OKS_BILL_SUB_LINES_V'
481 ,p_col_name => 'Average'
482 ,p_col_value => P_average
483 ,x_return_status => l_return_status
484 );
485 */
486
487 -- verify that length is within allowed limits
488 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
489 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
490 p_msg_name => g_unexpected_error,
491 p_token1 => g_sqlcode_token,
492 p_token1_value => sqlcode,
493 p_token2 => g_sqlerrm_token,
494 p_token2_value => 'Average Length');
495
496
497 -- notify caller of an error
498 x_return_status := OKC_API.G_RET_STS_ERROR;
499 -- halt further validation of this column
500 RAISE G_EXCEPTION_HALT_VALIDATION;
501 End If;
502
503 Exception
504 When G_EXCEPTION_HALT_VALIDATION Then
505 NULL;
506 When OTHERS Then
507 -- store SQL error message on message stack for caller
508 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
509 p_msg_name => g_unexpected_error,
510 p_token1 => g_sqlcode_token,
511 p_token1_value => sqlcode,
512 p_token2 => g_sqlerrm_token,
513 p_token2_value => sqlerrm);
514
515 -- notify caller of an UNEXPECTED error
516 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
517 END validate_average;
518
519
520 PROCEDURE validate_amount( x_return_status OUT NOCOPY varchar2,
521 P_amount IN NUMBER)
522 Is
523 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
524 Begin
525
526
527 -- call column length utility
528 /*
529 OKC_UTIL.CHECK_LENGTH
530 (
531 p_view_name => 'OKS_BILL_SUB_LINES_V'
532 ,p_col_name => 'Amount'
533 ,p_col_value => P_amount
534 ,x_return_status => l_return_status
535 );
536 */
537
538 -- verify that length is within allowed limits
539 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
540 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
541 p_msg_name => g_unexpected_error,
542 p_token1 => g_sqlcode_token,
543 p_token1_value => sqlcode,
544 p_token2 => g_sqlerrm_token,
545 p_token2_value => 'Amount Length');
546
547
548 -- notify caller of an error
549 x_return_status := OKC_API.G_RET_STS_ERROR;
550 -- halt further validation of this column
551 RAISE G_EXCEPTION_HALT_VALIDATION;
552 End If;
553
554 Exception
555 When G_EXCEPTION_HALT_VALIDATION Then
556 NULL;
557 When OTHERS Then
558 -- store SQL error message on message stack for caller
559 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
560 p_msg_name => g_unexpected_error,
561 p_token1 => g_sqlcode_token,
562 p_token1_value => sqlcode,
563 p_token2 => g_sqlerrm_token,
564 p_token2_value => sqlerrm);
565
566 -- notify caller of an UNEXPECTED error
567 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
568 END validate_amount;
569
570 PROCEDURE validate_Date_Billed_from( x_return_status OUT NOCOPY varchar2,
571 P_Date_Billed_from IN DATE)
572 Is
573 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
574
575 Begin
576
577
578 x_return_status := OKC_API.G_RET_STS_SUCCESS;
579
580 Exception
581 When G_EXCEPTION_HALT_VALIDATION Then
582 NULL;
583 When OTHERS Then
584 -- store SQL error message on message stack for caller
585 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
586 p_msg_name => g_unexpected_error,
587 p_token1 => g_sqlcode_token,
588 p_token1_value => sqlcode,
589 p_token2 => g_sqlerrm_token,
590 p_token2_value => sqlerrm);
591
592 -- notify caller of an UNEXPECTED error
593 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
594
595
596 END validate_Date_Billed_from;
597
598
599 PROCEDURE validate_Date_Billed_to( x_return_status OUT NOCOPY varchar2,
600 P_Date_Billed_to IN DATE)
601 Is
602 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
603
604 Begin
605
606
607 x_return_status := OKC_API.G_RET_STS_SUCCESS;
608
609 Exception
610 When G_EXCEPTION_HALT_VALIDATION Then
611 NULL;
612 When OTHERS Then
613 -- store SQL error message on message stack for caller
614 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
615 p_msg_name => g_unexpected_error,
616 p_token1 => g_sqlcode_token,
617 p_token1_value => sqlcode,
618 p_token2 => g_sqlerrm_token,
619 p_token2_value => sqlerrm);
620
621 -- notify caller of an UNEXPECTED error
622 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
623
624
625 END validate_Date_Billed_to;
626
627 PROCEDURE validate_Date_to_Interface( x_return_status OUT NOCOPY varchar2,
628 P_Date_to_Interface IN DATE)
629 Is
630 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
631
632 Begin
633
634
635 x_return_status := OKC_API.G_RET_STS_SUCCESS;
636
637 Exception
638 When G_EXCEPTION_HALT_VALIDATION Then
639 NULL;
640 When OTHERS Then
641 -- store SQL error message on message stack for caller
642 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
643 p_msg_name => g_unexpected_error,
644 p_token1 => g_sqlcode_token,
645 p_token1_value => sqlcode,
646 p_token2 => g_sqlerrm_token,
647 p_token2_value => sqlerrm);
648
649 -- notify caller of an UNEXPECTED error
650 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
651
652
653 END validate_Date_to_Interface;
654
655 PROCEDURE validate_attribute_category( x_return_status OUT NOCOPY varchar2,
656 P_attribute_category IN varchar)
657 Is
658 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
659 Begin
660
661 x_return_status := OKC_API.G_RET_STS_SUCCESS;
662
663 -- call column length utility
664
665 Exception
666 When G_EXCEPTION_HALT_VALIDATION Then
667 NULL;
668 When OTHERS Then
669 -- store SQL error message on message stack for caller
670 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
671 p_msg_name => g_unexpected_error,
672 p_token1 => g_sqlcode_token,
673 p_token1_value => sqlcode,
674 p_token2 => g_sqlerrm_token,
675 p_token2_value => sqlerrm);
676
677 -- notify caller of an UNEXPECTED error
678 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
679 END validate_attribute_category;
680
681
682 PROCEDURE validate_attribute1( x_return_status OUT NOCOPY varchar2,
683 P_attribute1 IN varchar2)
684 Is
685 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
686 Begin
687
688 x_return_status := OKC_API.G_RET_STS_SUCCESS;
689
690 -- call column length utility
691 /*
692 OKC_UTIL.CHECK_LENGTH
693 (
694 p_view_name => 'OKS_BILL_SUB_LINES_V'
695 ,p_col_name => 'attribute1'
696 ,p_col_value => p_attribute1
697 ,x_return_status => l_return_status
698 );
699 */
700
701 -- verify that length is within allowed limits
702 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
703 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
704 p_msg_name => g_unexpected_error,
705 p_token1 => g_sqlcode_token,
706 p_token1_value => sqlcode,
707 p_token2 => g_sqlerrm_token,
708 p_token2_value => 'attribute 1 Length');
709
710
711 -- notify caller of an error
712 x_return_status := OKC_API.G_RET_STS_ERROR;
713 -- halt further validation of this column
714 RAISE G_EXCEPTION_HALT_VALIDATION;
715 End If;
716
717 Exception
718 When G_EXCEPTION_HALT_VALIDATION Then
719 NULL;
720 When OTHERS Then
721 -- store SQL error message on message stack for caller
722 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
723 p_msg_name => g_unexpected_error,
724 p_token1 => g_sqlcode_token,
725 p_token1_value => sqlcode,
726 p_token2 => g_sqlerrm_token,
727 p_token2_value => sqlerrm);
728
729 -- notify caller of an UNEXPECTED error
730 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
731 END validate_attribute1;
732 PROCEDURE validate_attribute2( x_return_status OUT NOCOPY varchar2,
733 P_attribute2 IN varchar2)
734 Is
735 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
736 Begin
737
738 x_return_status := OKC_API.G_RET_STS_SUCCESS;
739
740 -- call column length utility
741 /*OKC_UTIL.CHECK_LENGTH
742 (
743 p_view_name => 'OKS_BILL_SUB_LINES_V'
744 ,p_col_name => 'attribute2'
745 ,p_col_value => p_attribute2
746 ,x_return_status => l_return_status
747 );
748 */
749 -- verify that length is within allowed limits
750 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
751 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
752 p_msg_name => g_unexpected_error,
753 p_token1 => g_sqlcode_token,
754 p_token1_value => sqlcode,
755 p_token2 => g_sqlerrm_token,
756 p_token2_value => 'attribute 2 Length');
757
758 -- notify caller of an error
759 x_return_status := OKC_API.G_RET_STS_ERROR;
760 -- halt further validation of this column
761 RAISE G_EXCEPTION_HALT_VALIDATION;
762 End If;
763
764 Exception
765 When G_EXCEPTION_HALT_VALIDATION Then
766 NULL;
767 When OTHERS Then
768 -- store SQL error message on message stack for caller
769 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
770 p_msg_name => g_unexpected_error,
771 p_token1 => g_sqlcode_token,
772 p_token1_value => sqlcode,
773 p_token2 => g_sqlerrm_token,
774 p_token2_value => sqlerrm);
775
776 -- notify caller of an UNEXPECTED error
777 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
778 END validate_attribute2;
779
780
781 PROCEDURE validate_attribute3( x_return_status OUT NOCOPY varchar2,
782 P_attribute3 IN varchar2)
783 Is
784 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
785 Begin
786
787 x_return_status := OKC_API.G_RET_STS_SUCCESS;
788
789 -- call column length utility
790 /*
791 OKC_UTIL.CHECK_LENGTH
792 (
793 p_view_name => 'OKS_BILL_SUB_LINES_V'
794 ,p_col_name => 'attribute3'
795 ,p_col_value => p_attribute3
796 ,x_return_status => l_return_status
797 );
798 */
799
800 -- verify that length is within allowed limits
801 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
802 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
803 p_msg_name => g_unexpected_error,
804 p_token1 => g_sqlcode_token,
805 p_token1_value => sqlcode,
806 p_token2 => g_sqlerrm_token,
807 p_token2_value => 'attribute 3 Length');
808
809 -- notify caller of an error
810 x_return_status := OKC_API.G_RET_STS_ERROR;
811 -- halt further validation of this column
812 RAISE G_EXCEPTION_HALT_VALIDATION;
813 End If;
814
815 Exception
816 When G_EXCEPTION_HALT_VALIDATION Then
817 NULL;
818 When OTHERS Then
819 -- store SQL error message on message stack for caller
820 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
821 p_msg_name => g_unexpected_error,
822 p_token1 => g_sqlcode_token,
823 p_token1_value => sqlcode,
824 p_token2 => g_sqlerrm_token,
825 p_token2_value => sqlerrm);
826
827 -- notify caller of an UNEXPECTED error
828 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
829 END validate_attribute3;
830
831 PROCEDURE validate_attribute4 (x_return_status OUT NOCOPY varchar2,
832 P_attribute4 IN varchar2)
833 Is
834 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
835 Begin
836
837 x_return_status := OKC_API.G_RET_STS_SUCCESS;
838
839 -- call column length utility
840 /*
841 OKC_UTIL.CHECK_LENGTH
842 (
843 p_view_name => 'OKS_BILL_SUB_LINES_V'
844 ,p_col_name => 'attribute4'
845 ,p_col_value => p_attribute4
846 ,x_return_status => l_return_status
847 );
848 */
849
850 -- verify that length is within allowed limits
851 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
852 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
853 p_msg_name => g_unexpected_error,
854 p_token1 => g_sqlcode_token,
855 p_token1_value => sqlcode,
856 p_token2 => g_sqlerrm_token,
857 p_token2_value => 'attribute 4 Length');
858
859 -- notify caller of an error
860 x_return_status := OKC_API.G_RET_STS_ERROR;
861 -- halt further validation of this column
862 RAISE G_EXCEPTION_HALT_VALIDATION;
863 End If;
864
865 Exception
866 When G_EXCEPTION_HALT_VALIDATION Then
867 NULL;
868 When OTHERS Then
869 -- store SQL error message on message stack for caller
870 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
871 p_msg_name => g_unexpected_error,
872 p_token1 => g_sqlcode_token,
873 p_token1_value => sqlcode,
874 p_token2 => g_sqlerrm_token,
875 p_token2_value => sqlerrm);
876
877 -- notify caller of an UNEXPECTED error
878 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
879 END validate_attribute4;
880
881
882 PROCEDURE validate_attribute5( x_return_status OUT NOCOPY varchar2,
883 P_attribute5 IN varchar2)
884 Is
885 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
886 Begin
887
888 x_return_status := OKC_API.G_RET_STS_SUCCESS;
889
890 -- call column length utility
891 /*
892 OKC_UTIL.CHECK_LENGTH
893 (
894 p_view_name => 'OKS_BILL_SUB_LINES_V'
895 ,p_col_name => 'attribute5'
896 ,p_col_value => p_attribute5
897 ,x_return_status => l_return_status
898 );
899 */
900
901 -- verify that length is within allowed limits
902 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
903 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
904 p_msg_name => g_unexpected_error,
905 p_token1 => g_sqlcode_token,
906 p_token1_value => sqlcode,
907 p_token2 => g_sqlerrm_token,
908 p_token2_value => 'attribute 5 Length');
909
910 -- notify caller of an error
911 x_return_status := OKC_API.G_RET_STS_ERROR;
912 -- halt further validation of this column
913 RAISE G_EXCEPTION_HALT_VALIDATION;
914 End If;
915
916 Exception
917 When G_EXCEPTION_HALT_VALIDATION Then
918 NULL;
919 When OTHERS Then
920 -- store SQL error message on message stack for caller
921 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
922 p_msg_name => g_unexpected_error,
923 p_token1 => g_sqlcode_token,
924 p_token1_value => sqlcode,
925 p_token2 => g_sqlerrm_token,
926 p_token2_value => sqlerrm);
927
928 -- notify caller of an UNEXPECTED error
929 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
930 END validate_attribute5;
931
932
933 PROCEDURE validate_attribute6( x_return_status OUT NOCOPY varchar2,
934 P_attribute6 IN varchar2)
935 Is
936 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
937 Begin
938
939 x_return_status := OKC_API.G_RET_STS_SUCCESS;
940
941 -- call column length utility
942 /*
943 OKC_UTIL.CHECK_LENGTH
944 (
945 p_view_name => 'OKS_BILL_SUB_LINES_V'
946 ,p_col_name => 'attribute6'
947 ,p_col_value => p_attribute6
948 ,x_return_status => l_return_status
949 );
950 */
951
952 -- verify that length is within allowed limits
953 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
954 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
955 p_msg_name => g_unexpected_error,
956 p_token1 => g_sqlcode_token,
957 p_token1_value => sqlcode,
958 p_token2 => g_sqlerrm_token,
959 p_token2_value => 'attribute 6 Length');
960
961 -- notify caller of an error
962 x_return_status := OKC_API.G_RET_STS_ERROR;
963 -- halt further validation of this column
964 RAISE G_EXCEPTION_HALT_VALIDATION;
965 End If;
966
967 Exception
968 When G_EXCEPTION_HALT_VALIDATION Then
969 NULL;
970 When OTHERS Then
971 -- store SQL error message on message stack for caller
972 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
973 p_msg_name => g_unexpected_error,
974 p_token1 => g_sqlcode_token,
975 p_token1_value => sqlcode,
976 p_token2 => g_sqlerrm_token,
977 p_token2_value => sqlerrm);
978
979 -- notify caller of an UNEXPECTED error
980 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
981 END validate_attribute6;
982
983
984 PROCEDURE validate_attribute7( x_return_status OUT NOCOPY varchar2,
985 P_attribute7 IN varchar2)
986 Is
987 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
988 Begin
989
990 x_return_status := OKC_API.G_RET_STS_SUCCESS;
991
992 -- call column length utility
993 /*
994 OKC_UTIL.CHECK_LENGTH
995 (
996 p_view_name => 'OKS_BILL_SUB_LINES_V'
997 ,p_col_name => 'attribute7'
998 ,p_col_value => p_attribute7
999 ,x_return_status => l_return_status
1000 );
1001 */
1002
1003 -- verify that length is within allowed limits
1004 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1005 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1006 p_msg_name => g_unexpected_error,
1007 p_token1 => g_sqlcode_token,
1008 p_token1_value => sqlcode,
1009 p_token2 => g_sqlerrm_token,
1010 p_token2_value => 'attribute 7 Length');
1011
1012 -- notify caller of an error
1013 x_return_status := OKC_API.G_RET_STS_ERROR;
1014 -- halt further validation of this column
1015 RAISE G_EXCEPTION_HALT_VALIDATION;
1016 End If;
1017
1018 Exception
1019 When G_EXCEPTION_HALT_VALIDATION Then
1020 NULL;
1021 When OTHERS Then
1022 -- store SQL error message on message stack for caller
1023 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1024 p_msg_name => g_unexpected_error,
1025 p_token1 => g_sqlcode_token,
1026 p_token1_value => sqlcode,
1027 p_token2 => g_sqlerrm_token,
1028 p_token2_value => sqlerrm);
1029
1030 -- notify caller of an UNEXPECTED error
1031 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1032 END validate_attribute7;
1033
1034
1035
1036 PROCEDURE validate_attribute8 (x_return_status OUT NOCOPY varchar2,
1037 P_attribute8 IN varchar2)
1038 Is
1039 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1040 Begin
1041
1042 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1043
1044 -- call column length utility
1045 /*
1046 OKC_UTIL.CHECK_LENGTH
1047 (
1048 p_view_name => 'OKS_BILL_SUB_LINES_V'
1049 ,p_col_name => 'attribute8'
1050 ,p_col_value => p_attribute8
1051 ,x_return_status => l_return_status
1052 );
1053 */
1054
1055 -- verify that length is within allowed limits
1056 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1057 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1058 p_msg_name => g_unexpected_error,
1059 p_token1 => g_sqlcode_token,
1060 p_token1_value => sqlcode,
1061 p_token2 => g_sqlerrm_token,
1062 p_token2_value => 'attribute 8 Length');
1063
1064 -- notify caller of an error
1065 x_return_status := OKC_API.G_RET_STS_ERROR;
1066 -- halt further validation of this column
1067 RAISE G_EXCEPTION_HALT_VALIDATION;
1068 End If;
1069
1070 Exception
1071 When G_EXCEPTION_HALT_VALIDATION Then
1072 NULL;
1073 When OTHERS Then
1074 -- store SQL error message on message stack for caller
1075 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1076 p_msg_name => g_unexpected_error,
1077 p_token1 => g_sqlcode_token,
1078 p_token1_value => sqlcode,
1079 p_token2 => g_sqlerrm_token,
1080 p_token2_value => sqlerrm);
1081
1082 -- notify caller of an UNEXPECTED error
1083 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1084 END validate_attribute8;
1085
1086
1087
1088 PROCEDURE validate_attribute9( x_return_status OUT NOCOPY varchar2,
1089 P_attribute9 IN varchar2)
1090 Is
1091 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1092 Begin
1093
1094 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1095
1096 -- call column length utility
1097 /*
1098 OKC_UTIL.CHECK_LENGTH
1099 (
1100 p_view_name => 'OKS_BILL_SUB_LINES_V'
1101 ,p_col_name => 'attribute9'
1102 ,p_col_value => p_attribute9
1103 ,x_return_status => l_return_status
1104 );
1105 */
1106
1107 -- verify that length is within allowed limits
1108 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1109 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1110 p_msg_name => g_unexpected_error,
1111 p_token1 => g_sqlcode_token,
1112 p_token1_value => sqlcode,
1113 p_token2 => g_sqlerrm_token,
1114 p_token2_value => 'attribute 9 Length');
1115
1116 -- notify caller of an error
1117 x_return_status := OKC_API.G_RET_STS_ERROR;
1118 -- halt further validation of this column
1119 RAISE G_EXCEPTION_HALT_VALIDATION;
1120 End If;
1121
1122 Exception
1123 When G_EXCEPTION_HALT_VALIDATION Then
1124 NULL;
1125 When OTHERS Then
1126 -- store SQL error message on message stack for caller
1127 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1128 p_msg_name => g_unexpected_error,
1129 p_token1 => g_sqlcode_token,
1130 p_token1_value => sqlcode,
1131 p_token2 => g_sqlerrm_token,
1132 p_token2_value => sqlerrm);
1133
1134 -- notify caller of an UNEXPECTED error
1135 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1136 END validate_attribute9;
1137
1138
1139 PROCEDURE validate_attribute10( x_return_status OUT NOCOPY varchar2,
1140 P_attribute10 IN varchar2)
1141 Is
1142 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1143 Begin
1144
1145 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1146
1147 -- call column length utility
1148 /*
1149 OKC_UTIL.CHECK_LENGTH
1150 (
1151 p_view_name => 'OKS_BILL_SUB_LINES_V'
1152 ,p_col_name => 'attribute10'
1153 ,p_col_value => p_attribute10
1154 ,x_return_status => l_return_status
1155 );
1156 */
1157
1158 -- verify that length is within allowed limits
1159 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1160 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1161 p_msg_name => g_unexpected_error,
1162 p_token1 => g_sqlcode_token,
1163 p_token1_value => sqlcode,
1164 p_token2 => g_sqlerrm_token,
1165 p_token2_value => 'attribute 10 Length');
1166
1167 -- notify caller of an error
1168 x_return_status := OKC_API.G_RET_STS_ERROR;
1169 -- halt further validation of this column
1170 RAISE G_EXCEPTION_HALT_VALIDATION;
1171 End If;
1172
1173 Exception
1174 When G_EXCEPTION_HALT_VALIDATION Then
1175 NULL;
1176 When OTHERS Then
1177 -- store SQL error message on message stack for caller
1178 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1179 p_msg_name => g_unexpected_error,
1180 p_token1 => g_sqlcode_token,
1181 p_token1_value => sqlcode,
1182 p_token2 => g_sqlerrm_token,
1183 p_token2_value => sqlerrm);
1184
1185 -- notify caller of an UNEXPECTED error
1186 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1187 END validate_attribute10;
1188
1189
1190
1191 PROCEDURE validate_attribute11( x_return_status OUT NOCOPY varchar2,
1192 P_attribute11 IN varchar2)
1193 Is
1194 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1195 Begin
1196
1197 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1198
1199 -- call column length utility
1200
1201 /*
1202 OKC_UTIL.CHECK_LENGTH
1203 (
1204 p_view_name => 'OKS_BILL_SUB_LINES_V'
1205 ,p_col_name => 'attribute11'
1206 ,p_col_value => p_attribute11
1207 ,x_return_status => l_return_status
1208 );
1209 */
1210
1211 -- verify that length is within allowed limits
1212 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1213
1214 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1215 p_msg_name => g_unexpected_error,
1216 p_token1 => g_sqlcode_token,
1217 p_token1_value => sqlcode,
1218 p_token2 => g_sqlerrm_token,
1219 p_token2_value => 'attribute 11 Length');
1220
1221 -- notify caller of an error
1222 x_return_status := OKC_API.G_RET_STS_ERROR;
1223 -- halt further validation of this column
1224 RAISE G_EXCEPTION_HALT_VALIDATION;
1225 End If;
1226
1227 Exception
1228 When G_EXCEPTION_HALT_VALIDATION Then
1229 NULL;
1230 When OTHERS Then
1231 -- store SQL error message on message stack for caller
1232 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1233 p_msg_name => g_unexpected_error,
1234 p_token1 => g_sqlcode_token,
1235 p_token1_value => sqlcode,
1236 p_token2 => g_sqlerrm_token,
1237 p_token2_value => sqlerrm);
1238
1239 -- notify caller of an UNEXPECTED error
1240 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1241 END validate_attribute11;
1242
1243
1244
1245
1246 PROCEDURE validate_attribute12( x_return_status OUT NOCOPY varchar2,
1247 P_attribute12 IN varchar2)
1248 Is
1249 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1250 Begin
1251
1252 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1253
1254 -- call column length utility
1255 /*
1256 OKC_UTIL.CHECK_LENGTH
1257 (
1258 p_view_name => 'OKS_BILL_SUB_LINES_V'
1259 ,p_col_name => 'attribute12'
1260 ,p_col_value => p_attribute12
1261 ,x_return_status => l_return_status
1262 );
1263 */
1264
1265 -- verify that length is within allowed limits
1266 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1267 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1268 p_msg_name => g_unexpected_error,
1269 p_token1 => g_sqlcode_token,
1270 p_token1_value => sqlcode,
1271 p_token2 => g_sqlerrm_token,
1272 p_token2_value => 'attribute 12 Length');
1273
1274 -- notify caller of an error
1275 x_return_status := OKC_API.G_RET_STS_ERROR;
1276 -- halt further validation of this column
1277 RAISE G_EXCEPTION_HALT_VALIDATION;
1278 End If;
1279
1280 Exception
1281 When G_EXCEPTION_HALT_VALIDATION Then
1282 NULL;
1283 When OTHERS Then
1284 -- store SQL error message on message stack for caller
1285 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1286 p_msg_name => g_unexpected_error,
1287 p_token1 => g_sqlcode_token,
1288 p_token1_value => sqlcode,
1289 p_token2 => g_sqlerrm_token,
1290 p_token2_value => sqlerrm);
1291
1292 -- notify caller of an UNEXPECTED error
1293 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1294 END validate_attribute12;
1295
1296
1297
1298
1299 PROCEDURE validate_attribute13( x_return_status OUT NOCOPY varchar2,
1300 P_attribute13 IN varchar2)
1301 Is
1302 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1303 Begin
1304
1305 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1306
1307 -- call column length utility
1308 /*
1309 OKC_UTIL.CHECK_LENGTH
1310 (
1311 p_view_name => 'OKS_BILL_SUB_LINES_V'
1312 ,p_col_name => 'attribute13'
1313 ,p_col_value => p_attribute13
1314 ,x_return_status => l_return_status
1315 );
1316 */
1317
1318 -- verify that length is within allowed limits
1319 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1320 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1321 p_msg_name => g_unexpected_error,
1322 p_token1 => g_sqlcode_token,
1323 p_token1_value => sqlcode,
1324 p_token2 => g_sqlerrm_token,
1325 p_token2_value => 'attribute 13 Length');
1326
1327 -- notify caller of an error
1328 x_return_status := OKC_API.G_RET_STS_ERROR;
1329 -- halt further validation of this column
1330 RAISE G_EXCEPTION_HALT_VALIDATION;
1331 End If;
1332
1333 Exception
1334 When G_EXCEPTION_HALT_VALIDATION Then
1335 NULL;
1336 When OTHERS Then
1337 -- store SQL error message on message stack for caller
1338 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1339 p_msg_name => g_unexpected_error,
1340 p_token1 => g_sqlcode_token,
1341 p_token1_value => sqlcode,
1342 p_token2 => g_sqlerrm_token,
1343 p_token2_value => sqlerrm);
1344
1345 -- notify caller of an UNEXPECTED error
1346 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1347 END validate_attribute13;
1348
1349
1350
1351
1352 PROCEDURE validate_attribute14( x_return_status OUT NOCOPY varchar2,
1353 P_attribute14 IN varchar2)
1354 Is
1355 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1356 Begin
1357
1358 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1359
1360 -- call column length utility
1361 /*
1362 OKC_UTIL.CHECK_LENGTH
1363 (
1364 p_view_name => 'OKS_BILL_SUB_LINES_V'
1365 ,p_col_name => 'attribute14'
1366 ,p_col_value => p_attribute14
1367 ,x_return_status => l_return_status
1368 );
1369 */
1370
1371 -- verify that length is within allowed limits
1372 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1373 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1374 p_msg_name => g_unexpected_error,
1375 p_token1 => g_sqlcode_token,
1376 p_token1_value => sqlcode,
1377 p_token2 => g_sqlerrm_token,
1378 p_token2_value => 'attribute 14 Length');
1379
1380 -- notify caller of an error
1381 x_return_status := OKC_API.G_RET_STS_ERROR;
1382 -- halt further validation of this column
1383 RAISE G_EXCEPTION_HALT_VALIDATION;
1384 End If;
1385
1386 Exception
1387 When G_EXCEPTION_HALT_VALIDATION Then
1388 NULL;
1389 When OTHERS Then
1390 -- store SQL error message on message stack for caller
1391 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1392 p_msg_name => g_unexpected_error,
1393 p_token1 => g_sqlcode_token,
1394 p_token1_value => sqlcode,
1395 p_token2 => g_sqlerrm_token,
1396 p_token2_value => sqlerrm);
1397
1398 -- notify caller of an UNEXPECTED error
1399 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1400 END validate_attribute14;
1401
1402
1403
1404 PROCEDURE validate_attribute15( x_return_status OUT NOCOPY varchar2,
1405 P_attribute15 IN varchar2)
1406 Is
1407 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1408 Begin
1409 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1410
1411 -- call column length utility
1412 /*
1413 OKC_UTIL.CHECK_LENGTH
1414 (
1415 p_view_name => 'OKS_BILL_SUB_LINES_V'
1416 ,p_col_name => 'attribute15'
1417 ,p_col_value => p_attribute15
1418 ,x_return_status => l_return_status
1419 );
1420 */
1421
1422 -- verify that length is within allowed limits
1423 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1424 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1425 p_msg_name => g_unexpected_error,
1426 p_token1 => g_sqlcode_token,
1427 p_token1_value => sqlcode,
1428 p_token2 => g_sqlerrm_token,
1429 p_token2_value => 'attribute 15 Length');
1430
1431 -- notify caller of an error
1432 x_return_status := OKC_API.G_RET_STS_ERROR;
1433 -- halt further validation of this column
1434 RAISE G_EXCEPTION_HALT_VALIDATION;
1435 End If;
1436
1437 Exception
1438 When G_EXCEPTION_HALT_VALIDATION Then
1439 NULL;
1440 When OTHERS Then
1441 -- store SQL error message on message stack for caller
1442 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1443 p_msg_name => g_unexpected_error,
1444 p_token1 => g_sqlcode_token,
1445 p_token1_value => sqlcode,
1446 p_token2 => g_sqlerrm_token,
1447 p_token2_value => sqlerrm);
1448
1449 -- notify caller of an UNEXPECTED error
1450 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1451 END validate_attribute15;
1452
1453 --------------------------------------------------
1454 -- Validate_Attributes for:OKS_BILL_SUB_LINES_V --
1455 --------------------------------------------------
1456 /*
1457 FUNCTION Validate_Attributes (
1458 p_bslv_rec IN bslv_rec_type
1459 ) RETURN VARCHAR2 IS
1460 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1461 BEGIN
1462 IF p_bslv_rec.id = OKC_API.G_MISS_NUM OR
1463 p_bslv_rec.id IS NULL
1464 THEN
1465 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1466 l_return_status := OKC_API.G_RET_STS_ERROR;
1467 ELSIF p_bslv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1468 p_bslv_rec.object_version_number IS NULL
1469 THEN
1470 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1471 l_return_status := OKC_API.G_RET_STS_ERROR;
1472 ELSIF p_bslv_rec.cle_id = OKC_API.G_MISS_NUM OR
1473 p_bslv_rec.cle_id IS NULL
1474 THEN
1475 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cle_id');
1476 l_return_status := OKC_API.G_RET_STS_ERROR;
1477 ELSIF p_bslv_rec.date_billed_from = OKC_API.G_MISS_DATE OR
1478 p_bslv_rec.date_billed_from IS NULL
1479 THEN
1480 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'date_billed_from');
1481 l_return_status := OKC_API.G_RET_STS_ERROR;
1482 ELSIF p_bslv_rec.date_billed_to = OKC_API.G_MISS_DATE OR
1483 p_bslv_rec.date_billed_to IS NULL
1484 THEN
1485 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'date_billed_to');
1486 l_return_status := OKC_API.G_RET_STS_ERROR;
1487 END IF;
1488 RETURN(l_return_status);
1489 END Validate_Attributes;
1490 */
1491
1492 FUNCTION Validate_Attributes (
1493 p_bslv_rec IN bslv_rec_type
1494 )
1495 Return VARCHAR2 Is
1496 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1497 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1498
1499 Begin
1500 -- call OKC_UTIL.ADD_VIEW to prepare the PL/SQL table to hold columns of view
1501
1502 OKC_UTIL.ADD_VIEW('OKS_BILL_SUB_LINES_V',x_return_status);
1503
1504 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1505 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1506 -- need to leave
1507 l_return_status := x_return_status;
1508 RAISE G_EXCEPTION_HALT_VALIDATION;
1509 ELSE
1510 -- record that there is a error
1511 l_return_status := x_return_status;
1512 END IF;
1513 END IF;
1514
1515 --Column Level Validation
1516
1517 --ID
1518 validate_id(x_return_status, p_bslv_rec.id);
1519
1520 -- store the highest degree of error
1521 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1522 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1523 -- need to leave
1524 l_return_status := x_return_status;
1525 RAISE G_EXCEPTION_HALT_VALIDATION;
1526 ELSE
1527 -- record that there was an error
1528 l_return_status := x_return_status;
1529 END IF;
1530 END IF;
1531
1532 --bcl_id
1533 validate_bcl_id(x_return_status, p_bslv_rec.bcl_id);
1534 -- store the highest degree of error
1535 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1536
1537 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1538
1539 -- need to leave
1540 l_return_status := x_return_status;
1541 RAISE G_EXCEPTION_HALT_VALIDATION;
1542 ELSE
1543 -- record that there was an error
1544 l_return_status := x_return_status;
1545 END IF;
1546 END IF;
1547
1548 --Cle_ID
1549 validate_cle_id(x_return_status, p_bslv_rec.cle_id);
1550
1551 -- store the highest degree of error
1552 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1553 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1554 -- need to leave
1555 l_return_status := x_return_status;
1556 RAISE G_EXCEPTION_HALT_VALIDATION;
1557 ELSE
1558 -- record that there was an error
1559 l_return_status := x_return_status;
1560 END IF;
1561 END IF;
1562
1563 --OBJECT_VERSION_NUMBER
1564 validate_objvernum(x_return_status, p_bslv_rec.object_version_number);
1565
1566 -- store the highest degree of error
1567 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1568 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1569 -- need to leave
1570 l_return_status := x_return_status;
1571 RAISE G_EXCEPTION_HALT_VALIDATION;
1572 ELSE
1573 -- record that there was an error
1574 l_return_status := x_return_status;
1575 END IF;
1576 END IF;
1577
1578 --Average
1579 validate_average (x_return_status, p_bslv_rec.average);
1580
1581 -- store the highest degree of error
1582 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1583 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1584 -- need to leave
1585 l_return_status := x_return_status;
1586 RAISE G_EXCEPTION_HALT_VALIDATION;
1587 ELSE
1588 -- record that there was an error
1589 l_return_status := x_return_status;
1590 END IF;
1591 END IF;
1592
1593 --AMOUNT
1594 validate_amount(x_return_status, p_bslv_rec.amount);
1595
1596 -- store the highest degree of error
1597 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1598 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1599 -- need to leave
1600 l_return_status := x_return_status;
1601 RAISE G_EXCEPTION_HALT_VALIDATION;
1602 ELSE
1603 -- record that there was an error
1604 l_return_status := x_return_status;
1605 END IF;
1606 END IF;
1607 --DATE_BILLED_FROM
1608 validate_date_billed_from(x_return_status, p_bslv_rec.date_billed_from);
1609
1610 -- store the highest degree of error
1611 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1612 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1613
1614
1615 -- need to leave
1616 l_return_status := x_return_status;
1617 RAISE G_EXCEPTION_HALT_VALIDATION;
1618 ELSE
1619 -- record that there was an error
1620 l_return_status := x_return_status;
1621 END IF;
1622 END IF;
1623
1624 --DATE_TO_INTERFACE
1625 validate_date_to_interface(x_return_status, p_bslv_rec.date_to_interface);
1626
1627 -- store the highest degree of error
1628 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1629 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1630
1631
1632 -- need to leave
1633 l_return_status := x_return_status;
1634 RAISE G_EXCEPTION_HALT_VALIDATION;
1635 ELSE
1636 -- record that there was an error
1637 l_return_status := x_return_status;
1638 END IF;
1639 END IF;
1640
1641
1642
1643 --DATE_BILLED_TO
1644 validate_date_billed_to(x_return_status, p_bslv_rec.date_billed_to);
1645
1646 -- store the highest degree of error
1647 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1648 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1649
1650 -- need to leave
1651 l_return_status := x_return_status;
1652 RAISE G_EXCEPTION_HALT_VALIDATION;
1653 ELSE
1654 -- record that there was an error
1655 l_return_status := x_return_status;
1656 END IF;
1657 END IF;
1658
1659 --ATTRIBUTE_CATEGORY
1660
1661 validate_attribute_category(x_return_status, p_bslv_rec.attribute_category);
1662
1663 -- store the highest degree of error
1664 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1665 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1666 -- need to leave
1667 l_return_status := x_return_status;
1668 RAISE G_EXCEPTION_HALT_VALIDATION;
1669 ELSE
1670 -- record that there was an error
1671 l_return_status := x_return_status;
1672 END IF;
1673 END IF;
1674
1675
1676 --ATTRIBUTE1
1677
1678 validate_attribute1(x_return_status, p_bslv_rec.attribute1);
1679
1680 -- store the highest degree of error
1681 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1682 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1683 -- need to leave
1684 l_return_status := x_return_status;
1685 RAISE G_EXCEPTION_HALT_VALIDATION;
1686 ELSE
1687 -- record that there was an error
1688 l_return_status := x_return_status;
1689 END IF;
1690 END IF;
1691
1692 --ATTRIBUTE2
1693
1694 validate_attribute2(x_return_status, p_bslv_rec.attribute2);
1695
1696 -- store the highest degree of error
1697 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1698 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1699 -- need to leave
1700 l_return_status := x_return_status;
1701 RAISE G_EXCEPTION_HALT_VALIDATION;
1702 ELSE
1703 -- record that there was an error
1704 l_return_status := x_return_status;
1705 END IF;
1706 END IF;
1707
1708
1709 --ATTRIBUTE3
1710
1711 validate_attribute3(x_return_status, p_bslv_rec.attribute3);
1712
1713 -- store the highest degree of error
1714 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1715 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1716 -- need to leave
1717 l_return_status := x_return_status;
1718 RAISE G_EXCEPTION_HALT_VALIDATION;
1719 ELSE
1720 -- record that there was an error
1721 l_return_status := x_return_status;
1722 END IF;
1723 END IF;
1724
1725
1726 --ATTRIBUTE4
1727 validate_attribute4(x_return_status, p_bslv_rec.attribute4);
1728
1729 -- store the highest degree of error
1730 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1731 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1732 -- need to leave
1733 l_return_status := x_return_status;
1734 RAISE G_EXCEPTION_HALT_VALIDATION;
1735 ELSE
1736 -- record that there was an error
1737 l_return_status := x_return_status;
1738 END IF;
1739 END IF;
1740
1741
1742 --ATTRIBUTE5
1743 validate_attribute5(x_return_status, p_bslv_rec.attribute5);
1744
1745 -- store the highest degree of error
1746 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1747 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1748 -- need to leave
1749 l_return_status := x_return_status;
1750 RAISE G_EXCEPTION_HALT_VALIDATION;
1751 ELSE
1752 -- record that there was an error
1753 l_return_status := x_return_status;
1754 END IF;
1755 END IF;
1756
1757
1758 --ATTRIBUTE6
1759
1760 validate_attribute6(x_return_status, p_bslv_rec.attribute6);
1761
1762 -- store the highest degree of error
1763 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1764 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1765 -- need to leave
1766 l_return_status := x_return_status;
1767 RAISE G_EXCEPTION_HALT_VALIDATION;
1768 ELSE
1769 -- record that there was an error
1770 l_return_status := x_return_status;
1771 END IF;
1772 END IF;
1773
1774
1775 --ATTRIBUTE7
1776
1777 validate_attribute7(x_return_status, p_bslv_rec.attribute7);
1778
1779 -- store the highest degree of error
1780 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1781 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1782 -- need to leave
1783 l_return_status := x_return_status;
1784 RAISE G_EXCEPTION_HALT_VALIDATION;
1785 ELSE
1786 -- record that there was an error
1787 l_return_status := x_return_status;
1788 END IF;
1789 END IF;
1790
1791
1792 --ATTRIBUTE8
1793 validate_attribute8(x_return_status, p_bslv_rec.attribute8);
1794
1795 -- store the highest degree of error
1796 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1797 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1798 -- need to leave
1799 l_return_status := x_return_status;
1800 RAISE G_EXCEPTION_HALT_VALIDATION;
1801 ELSE
1802 -- record that there was an error
1803 l_return_status := x_return_status;
1804 END IF;
1805 END IF;
1806
1807
1808 --ATTRIBUTE9
1809 validate_attribute9(x_return_status, p_bslv_rec.attribute9);
1810
1811 -- store the highest degree of error
1812 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1813 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1814 -- need to leave
1815 l_return_status := x_return_status;
1816 RAISE G_EXCEPTION_HALT_VALIDATION;
1817 ELSE
1818 -- record that there was an error
1819 l_return_status := x_return_status;
1820 END IF;
1821 END IF;
1822
1823
1824 --ATTRIBUTE10
1825
1826 validate_attribute10(x_return_status, p_bslv_rec.attribute10);
1827
1828 -- store the highest degree of error
1829 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1830 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1831 -- need to leave
1832 l_return_status := x_return_status;
1833 RAISE G_EXCEPTION_HALT_VALIDATION;
1834 ELSE
1835 -- record that there was an error
1836 l_return_status := x_return_status;
1837 END IF;
1838 END IF;
1839
1840
1841 --ATTRIBUTE11
1842
1843 validate_attribute11(x_return_status, p_bslv_rec.attribute11);
1844
1845 -- store the highest degree of error
1846 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1847 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1848 -- need to leave
1849 l_return_status := x_return_status;
1850 RAISE G_EXCEPTION_HALT_VALIDATION;
1851 ELSE
1852 -- record that there was an error
1853 l_return_status := x_return_status;
1854 END IF;
1855 END IF;
1856
1857
1858 --ATTRIBUTE12
1859
1860 validate_attribute12(x_return_status, p_bslv_rec.attribute12);
1861
1862 -- store the highest degree of error
1863 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1864 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1865 -- need to leave
1866 l_return_status := x_return_status;
1867 RAISE G_EXCEPTION_HALT_VALIDATION;
1868 ELSE
1869 -- record that there was an error
1870 l_return_status := x_return_status;
1871 END IF;
1872 END IF;
1873
1874
1875 --ATTRIBUTE13
1876 validate_attribute13(x_return_status, p_bslv_rec.attribute13);
1877
1878 -- store the highest degree of error
1879 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1880 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1881 -- need to leave
1882 l_return_status := x_return_status;
1883 RAISE G_EXCEPTION_HALT_VALIDATION;
1884 ELSE
1885 -- record that there was an error
1886 l_return_status := x_return_status;
1887 END IF;
1888 END IF;
1889
1890
1891 --ATTRIBUTE14
1892
1893 validate_attribute14(x_return_status, p_bslv_rec.attribute14);
1894
1895 -- store the highest degree of error
1896 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1897 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1898 -- need to leave
1899 l_return_status := x_return_status;
1900 RAISE G_EXCEPTION_HALT_VALIDATION;
1901 ELSE
1902 -- record that there was an error
1903 l_return_status := x_return_status;
1904 END IF;
1905 END IF;
1906
1907 --ATTRIBUTE15
1908
1909 validate_attribute15(x_return_status, p_bslv_rec.attribute15);
1910
1911 -- store the highest degree of error
1912 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1913 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1914 -- need to leave
1915 l_return_status := x_return_status;
1916 RAISE G_EXCEPTION_HALT_VALIDATION;
1917 ELSE
1918 -- record that there was an error
1919 l_return_status := x_return_status;
1920 END IF;
1921 END IF;
1922
1923 Raise G_EXCEPTION_HALT_VALIDATION;
1924
1925 Exception
1926
1927 When G_EXCEPTION_HALT_VALIDATION Then
1928
1929 Return (l_return_status);
1930
1931 When OTHERS Then
1932 -- store SQL error message on message stack for caller
1933 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1934 p_msg_name => g_unexpected_error,
1935 p_token1 => g_sqlcode_token,
1936 p_token1_value => sqlcode,
1937 p_token2 => g_sqlerrm_token,
1938 p_token2_value => sqlerrm);
1939
1940 -- notify caller of an UNEXPECTED error
1941 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1942 Return(l_return_status);
1943
1944 END validate_attributes;
1945
1946 ---------------------------------------------------------------------------
1947 -- PROCEDURE Validate_Record
1948 ---------------------------------------------------------------------------
1949 ----------------------------------------------
1950 -- Validate_Record for:OKS_BILL_SUB_LINES_V --
1951 ----------------------------------------------
1952 FUNCTION Validate_Record (
1953 p_bslv_rec IN bslv_rec_type
1954 ) RETURN VARCHAR2 IS
1955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1956 ------------------------------------
1957 -- FUNCTION validate_foreign_keys --
1958 ------------------------------------
1959 FUNCTION validate_foreign_keys (
1960 p_bslv_rec IN bslv_rec_type
1961 ) RETURN VARCHAR2 IS
1962 item_not_found_error EXCEPTION;
1963 CURSOR okc_bclv_pk_csr (p_id IN NUMBER) IS
1964 SELECT
1965 ID,
1966 OBJECT_VERSION_NUMBER,
1967 CLE_ID,
1968 BTN_ID,
1969 DATE_BILLED_FROM,
1970 DATE_BILLED_TO,
1971 DATE_NEXT_INVOICE,
1972 AMOUNT,
1973 BILL_ACTION,
1974 ATTRIBUTE_CATEGORY,
1975 ATTRIBUTE1,
1976 ATTRIBUTE2,
1977 ATTRIBUTE3,
1978 ATTRIBUTE4,
1979 ATTRIBUTE5,
1980 ATTRIBUTE6,
1981 ATTRIBUTE7,
1982 ATTRIBUTE8,
1983 ATTRIBUTE9,
1984 ATTRIBUTE10,
1985 ATTRIBUTE11,
1986 ATTRIBUTE12,
1987 ATTRIBUTE13,
1988 ATTRIBUTE14,
1989 ATTRIBUTE15,
1990 CREATED_BY,
1991 CREATION_DATE,
1992 LAST_UPDATED_BY,
1993 LAST_UPDATE_DATE,
1994 LAST_UPDATE_LOGIN
1995 FROM Oks_Bill_Cont_Lines_V
1996 WHERE oks_bill_cont_lines_v.id = p_id;
1997 l_okc_bclv_pk okc_bclv_pk_csr%ROWTYPE;
1998 CURSOR okc_clev_pk_csr (p_id IN NUMBER) IS
1999 SELECT
2000 ID,
2001 OBJECT_VERSION_NUMBER,
2002 SFWT_FLAG,
2003 CHR_ID,
2004 CLE_ID,
2005 LSE_ID,
2006 LINE_NUMBER,
2007 STS_CODE,
2008 DISPLAY_SEQUENCE,
2009 TRN_CODE,
2010 DNZ_CHR_ID,
2011 COMMENTS,
2012 ITEM_DESCRIPTION,
2013 HIDDEN_IND,
2014 PRICE_UNIT,
2015 PRICE_UNIT_PERCENT,
2016 PRICE_NEGOTIATED,
2017 PRICE_LEVEL_IND,
2018 INVOICE_LINE_LEVEL_IND,
2019 DPAS_RATING,
2020 BLOCK23TEXT,
2021 EXCEPTION_YN,
2022 TEMPLATE_USED,
2023 DATE_TERMINATED,
2024 NAME,
2025 START_DATE,
2026 END_DATE,
2027 ATTRIBUTE_CATEGORY,
2028 ATTRIBUTE1,
2029 ATTRIBUTE2,
2030 ATTRIBUTE3,
2031 ATTRIBUTE4,
2032 ATTRIBUTE5,
2033 ATTRIBUTE6,
2034 ATTRIBUTE7,
2035 ATTRIBUTE8,
2036 ATTRIBUTE9,
2037 ATTRIBUTE10,
2038 ATTRIBUTE11,
2039 ATTRIBUTE12,
2040 ATTRIBUTE13,
2041 ATTRIBUTE14,
2042 ATTRIBUTE15,
2043 CREATED_BY,
2044 CREATION_DATE,
2045 LAST_UPDATED_BY,
2046 LAST_UPDATE_DATE,
2047 PRICE_TYPE,
2048 --UOM_CODE,
2049 CURRENCY_CODE,
2050 LAST_UPDATE_LOGIN
2051 FROM Okc_K_Lines_V
2052 WHERE okc_k_lines_v.id = p_id;
2053 l_okc_clev_pk okc_clev_pk_csr%ROWTYPE;
2054 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2055 l_row_notfound BOOLEAN := TRUE;
2056 BEGIN
2057 IF (p_bslv_rec.BCL_ID IS NOT NULL)
2058 THEN
2059 OPEN okc_bclv_pk_csr(p_bslv_rec.BCL_ID);
2060 FETCH okc_bclv_pk_csr INTO l_okc_bclv_pk;
2061 l_row_notfound := okc_bclv_pk_csr%NOTFOUND;
2062 CLOSE okc_bclv_pk_csr;
2063 IF (l_row_notfound) THEN
2064 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'BCL_ID');
2065 RAISE item_not_found_error;
2066 END IF;
2067 END IF;
2068 IF (p_bslv_rec.CLE_ID IS NOT NULL)
2069 THEN
2070 OPEN okc_clev_pk_csr(p_bslv_rec.CLE_ID);
2071 FETCH okc_clev_pk_csr INTO l_okc_clev_pk;
2072 l_row_notfound := okc_clev_pk_csr%NOTFOUND;
2073 CLOSE okc_clev_pk_csr;
2074 IF (l_row_notfound) THEN
2075 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CLE_ID');
2076 RAISE item_not_found_error;
2077 END IF;
2078 END IF;
2079 RETURN (l_return_status);
2080 EXCEPTION
2081 WHEN item_not_found_error THEN
2082 l_return_status := OKC_API.G_RET_STS_ERROR;
2083 RETURN (l_return_status);
2084 END validate_foreign_keys;
2085 BEGIN
2086 l_return_status := validate_foreign_keys (p_bslv_rec);
2087 RETURN (l_return_status);
2088 END Validate_Record;
2089
2090 ---------------------------------------------------------------------------
2091 -- PROCEDURE Migrate
2092 ---------------------------------------------------------------------------
2093 PROCEDURE migrate (
2094 p_from IN bslv_rec_type,
2095 p_to OUT NOCOPY bsl_rec_type
2096 ) IS
2097 BEGIN
2098 p_to.id := p_from.id;
2099 p_to.bcl_id := p_from.bcl_id;
2100 p_to.cle_id := p_from.cle_id;
2101 p_to.object_version_number := p_from.object_version_number;
2102 p_to.created_by := p_from.created_by;
2103 p_to.creation_date := p_from.creation_date;
2104 p_to.last_updated_by := p_from.last_updated_by;
2105 p_to.last_update_date := p_from.last_update_date;
2106 p_to.average := p_from.average;
2107 p_to.amount := p_from.amount;
2108 p_to.MANUAL_CREDIT := p_from.MANUAL_CREDIT;
2109 p_to.date_billed_from := p_from.date_billed_from;
2110 p_to.date_billed_to := p_from.date_billed_to;
2111 p_to.date_to_interface := p_from.date_to_interface;
2112 p_to.last_update_login := p_from.last_update_login;
2113 p_to.attribute_category := p_from.attribute_category;
2114 p_to.attribute1 := p_from.attribute1;
2115 p_to.attribute2 := p_from.attribute2;
2116 p_to.attribute3 := p_from.attribute3;
2117 p_to.attribute4 := p_from.attribute4;
2118 p_to.attribute5 := p_from.attribute5;
2119 p_to.attribute6 := p_from.attribute6;
2120 p_to.attribute7 := p_from.attribute7;
2121 p_to.attribute8 := p_from.attribute8;
2122 p_to.attribute9 := p_from.attribute9;
2123 p_to.attribute10 := p_from.attribute10;
2124 p_to.attribute11 := p_from.attribute11;
2125 p_to.attribute12 := p_from.attribute12;
2126 p_to.attribute13 := p_from.attribute13;
2127 p_to.attribute14 := p_from.attribute14;
2128 p_to.attribute15 := p_from.attribute15;
2129 END migrate;
2130 PROCEDURE migrate (
2131 p_from IN bsl_rec_type,
2132 p_to OUT NOCOPY bslv_rec_type
2133 ) IS
2134 BEGIN
2135 p_to.id := p_from.id;
2136 p_to.bcl_id := p_from.bcl_id;
2137 p_to.cle_id := p_from.cle_id;
2138 p_to.object_version_number := p_from.object_version_number;
2139 p_to.created_by := p_from.created_by;
2140 p_to.creation_date := p_from.creation_date;
2141 p_to.last_updated_by := p_from.last_updated_by;
2142 p_to.last_update_date := p_from.last_update_date;
2143 p_to.average := p_from.average;
2144 p_to.MANUAL_CREDIT := p_from.MANUAL_CREDIT;
2145 p_to.date_billed_from := p_from.date_billed_from;
2146 p_to.date_billed_to := p_from.date_billed_to;
2147 p_to.date_to_interface := p_from.date_to_interface;
2148 p_to.last_update_login := p_from.last_update_login;
2149 p_to.attribute_category := p_from.attribute_category;
2150 p_to.attribute1 := p_from.attribute1;
2151 p_to.attribute2 := p_from.attribute2;
2152 p_to.attribute3 := p_from.attribute3;
2153 p_to.attribute4 := p_from.attribute4;
2154 p_to.attribute5 := p_from.attribute5;
2155 p_to.attribute6 := p_from.attribute6;
2156 p_to.attribute7 := p_from.attribute7;
2157 p_to.attribute8 := p_from.attribute8;
2158 p_to.attribute9 := p_from.attribute9;
2159 p_to.attribute10 := p_from.attribute10;
2160 p_to.attribute11 := p_from.attribute11;
2161 p_to.attribute12 := p_from.attribute12;
2162 p_to.attribute13 := p_from.attribute13;
2163 p_to.attribute14 := p_from.attribute14;
2164 p_to.attribute15 := p_from.attribute15;
2165 END migrate;
2166
2167 ---------------------------------------------------------------------------
2168 -- PROCEDURE validate_row
2169 ---------------------------------------------------------------------------
2170 -------------------------------------------
2171 -- validate_row for:OKS_BILL_SUB_LINES_V --
2172 -------------------------------------------
2173 PROCEDURE validate_row(
2174 p_api_version IN NUMBER,
2175 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2176 x_return_status OUT NOCOPY VARCHAR2,
2177 x_msg_count OUT NOCOPY NUMBER,
2178 x_msg_data OUT NOCOPY VARCHAR2,
2179 p_bslv_rec IN bslv_rec_type) IS
2180
2181 l_api_version CONSTANT NUMBER := 1;
2182 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2183 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2184 l_bslv_rec bslv_rec_type := p_bslv_rec;
2185 l_bsl_rec bsl_rec_type;
2186 BEGIN
2187 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2188 G_PKG_NAME,
2189 p_init_msg_list,
2190 l_api_version,
2191 p_api_version,
2192 '_PVT',
2193 x_return_status);
2194 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2195 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2196 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2197 RAISE OKC_API.G_EXCEPTION_ERROR;
2198 END IF;
2199 --- Validate all non-missing attributes (Item Level Validation)
2200 l_return_status := Validate_Attributes(l_bslv_rec);
2201 --- If any errors happen abort API
2202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2203 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2204 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2205 RAISE OKC_API.G_EXCEPTION_ERROR;
2206 END IF;
2207 l_return_status := Validate_Record(l_bslv_rec);
2208 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2209 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2210 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2211 RAISE OKC_API.G_EXCEPTION_ERROR;
2212 END IF;
2213 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2214 EXCEPTION
2215 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2216 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2217 (
2218 l_api_name,
2219 G_PKG_NAME,
2220 'OKC_API.G_RET_STS_ERROR',
2221 x_msg_count,
2222 x_msg_data,
2223 '_PVT'
2224 );
2225 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2226 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2227 (
2228 l_api_name,
2229 G_PKG_NAME,
2230 'OKC_API.G_RET_STS_UNEXP_ERROR',
2231 x_msg_count,
2232 x_msg_data,
2233 '_PVT'
2234 );
2235 WHEN OTHERS THEN
2236 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2237 (
2238 l_api_name,
2239 G_PKG_NAME,
2240 'OTHERS',
2241 x_msg_count,
2242 x_msg_data,
2243 '_PVT'
2244 );
2245 END validate_row;
2246 ------------------------------------------
2247 -- PL/SQL TBL validate_row for:BSLV_TBL --
2248 ------------------------------------------
2249 PROCEDURE validate_row(
2250 p_api_version IN NUMBER,
2251 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2252 x_return_status OUT NOCOPY VARCHAR2,
2253 x_msg_count OUT NOCOPY NUMBER,
2254 x_msg_data OUT NOCOPY VARCHAR2,
2255 p_bslv_tbl IN bslv_tbl_type) IS
2256
2257 l_api_version CONSTANT NUMBER := 1;
2258 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2259 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2260 i NUMBER := 0;
2261 BEGIN
2262 OKC_API.init_msg_list(p_init_msg_list);
2263 -- Make sure PL/SQL table has records in it before passing
2264 IF (p_bslv_tbl.COUNT > 0) THEN
2265 i := p_bslv_tbl.FIRST;
2266 LOOP
2267 validate_row (
2268 p_api_version => p_api_version,
2269 p_init_msg_list => OKC_API.G_FALSE,
2270 x_return_status => x_return_status,
2271 x_msg_count => x_msg_count,
2272 x_msg_data => x_msg_data,
2273 p_bslv_rec => p_bslv_tbl(i));
2274 EXIT WHEN (i = p_bslv_tbl.LAST);
2275 i := p_bslv_tbl.NEXT(i);
2276 END LOOP;
2277 END IF;
2278 EXCEPTION
2279 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2280 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2281 (
2282 l_api_name,
2283 G_PKG_NAME,
2284 'OKC_API.G_RET_STS_ERROR',
2285 x_msg_count,
2286 x_msg_data,
2287 '_PVT'
2288 );
2289 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2290 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2291 (
2292 l_api_name,
2293 G_PKG_NAME,
2294 'OKC_API.G_RET_STS_UNEXP_ERROR',
2295 x_msg_count,
2296 x_msg_data,
2297 '_PVT'
2298 );
2299 WHEN OTHERS THEN
2300 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2301 (
2302 l_api_name,
2303 G_PKG_NAME,
2304 'OTHERS',
2305 x_msg_count,
2306 x_msg_data,
2307 '_PVT'
2308 );
2309 END validate_row;
2310
2311 ---------------------------------------------------------------------------
2312 -- PROCEDURE insert_row
2313 ---------------------------------------------------------------------------
2314 ---------------------------------------
2315 -- insert_row for:OKS_BILL_SUB_LINES --
2316 ---------------------------------------
2317 PROCEDURE insert_row(
2318 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2319 x_return_status OUT NOCOPY VARCHAR2,
2320 x_msg_count OUT NOCOPY NUMBER,
2321 x_msg_data OUT NOCOPY VARCHAR2,
2322 p_bsl_rec IN bsl_rec_type,
2323 x_bsl_rec OUT NOCOPY bsl_rec_type) IS
2324
2325 l_api_version CONSTANT NUMBER := 1;
2326 l_api_name CONSTANT VARCHAR2(30) := 'LINES_insert_row';
2327 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2328 l_bsl_rec bsl_rec_type := p_bsl_rec;
2329 l_def_bsl_rec bsl_rec_type;
2330 -------------------------------------------
2331 -- Set_Attributes for:OKS_BILL_SUB_LINES --
2332 -------------------------------------------
2333 FUNCTION Set_Attributes (
2334 p_bsl_rec IN bsl_rec_type,
2335 x_bsl_rec OUT NOCOPY bsl_rec_type
2336 ) RETURN VARCHAR2 IS
2337 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2338 BEGIN
2339 x_bsl_rec := p_bsl_rec;
2340 RETURN(l_return_status);
2341 END Set_Attributes;
2342 BEGIN
2343 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2344 p_init_msg_list,
2345 '_PVT',
2346 x_return_status);
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 --- Setting item attributes
2353 l_return_status := Set_Attributes(
2354 p_bsl_rec, -- IN
2355 l_bsl_rec); -- OUT
2356 --- If any errors happen abort API
2357 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2358 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2359 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2360 RAISE OKC_API.G_EXCEPTION_ERROR;
2361 END IF;
2362 INSERT INTO OKS_BILL_SUB_LINES(
2363 id,
2364 bcl_id,
2365 cle_id,
2366 object_version_number,
2367 created_by,
2368 creation_date,
2369 last_updated_by,
2370 last_update_date,
2371 average,
2372 amount,
2373 MANUAL_CREDIT,
2374 date_billed_from,
2375 date_billed_to,
2376 date_to_interface,
2377 last_update_login,
2378 attribute_category,
2379 attribute1,
2380 attribute2,
2381 attribute3,
2382 attribute4,
2383 attribute5,
2384 attribute6,
2385 attribute7,
2386 attribute8,
2387 attribute9,
2388 attribute10,
2389 attribute11,
2390 attribute12,
2391 attribute13,
2392 attribute14,
2393 attribute15)
2394 VALUES (
2395 l_bsl_rec.id,
2396 l_bsl_rec.bcl_id,
2397 l_bsl_rec.cle_id,
2398 l_bsl_rec.object_version_number,
2399 l_bsl_rec.created_by,
2400 l_bsl_rec.creation_date,
2401 l_bsl_rec.last_updated_by,
2402 l_bsl_rec.last_update_date,
2403 l_bsl_rec.average,
2404 l_bsl_rec.amount,
2405 l_bsl_rec.MANUAL_CREDIT,
2406 l_bsl_rec.date_billed_from,
2407 l_bsl_rec.date_billed_to,
2408 l_bsl_rec.date_to_interface,
2409 l_bsl_rec.last_update_login,
2410 l_bsl_rec.attribute_category,
2411 l_bsl_rec.attribute1,
2412 l_bsl_rec.attribute2,
2413 l_bsl_rec.attribute3,
2414 l_bsl_rec.attribute4,
2415 l_bsl_rec.attribute5,
2416 l_bsl_rec.attribute6,
2417 l_bsl_rec.attribute7,
2418 l_bsl_rec.attribute8,
2419 l_bsl_rec.attribute9,
2420 l_bsl_rec.attribute10,
2421 l_bsl_rec.attribute11,
2422 l_bsl_rec.attribute12,
2423 l_bsl_rec.attribute13,
2424 l_bsl_rec.attribute14,
2425 l_bsl_rec.attribute15);
2426 -- Set OUT values
2427 x_bsl_rec := l_bsl_rec;
2428 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2429 EXCEPTION
2430 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2431 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2432 (
2433 l_api_name,
2434 G_PKG_NAME,
2435 'OKC_API.G_RET_STS_ERROR',
2436 x_msg_count,
2437 x_msg_data,
2438 '_PVT'
2439 );
2440 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2441 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2442 (
2443 l_api_name,
2444 G_PKG_NAME,
2445 'OKC_API.G_RET_STS_UNEXP_ERROR',
2446 x_msg_count,
2447 x_msg_data,
2448 '_PVT'
2449 );
2450 WHEN OTHERS THEN
2451 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2452 (
2453 l_api_name,
2454 G_PKG_NAME,
2455 'OTHERS',
2456 x_msg_count,
2457 x_msg_data,
2458 '_PVT'
2459 );
2460 END insert_row;
2461 -----------------------------------------
2462 -- insert_row for:OKS_BILL_SUB_LINES_V --
2463 -----------------------------------------
2464 PROCEDURE insert_row(
2465 p_api_version IN NUMBER,
2466 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2467 x_return_status OUT NOCOPY VARCHAR2,
2468 x_msg_count OUT NOCOPY NUMBER,
2469 x_msg_data OUT NOCOPY VARCHAR2,
2470 p_bslv_rec IN bslv_rec_type,
2471 x_bslv_rec OUT NOCOPY bslv_rec_type) IS
2472
2473 l_api_version CONSTANT NUMBER := 1;
2474 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2475 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2476 l_bslv_rec bslv_rec_type;
2477 l_def_bslv_rec bslv_rec_type;
2478 l_bsl_rec bsl_rec_type;
2479 lx_bsl_rec bsl_rec_type;
2480 -------------------------------
2481 -- FUNCTION fill_who_columns --
2482 -------------------------------
2483 FUNCTION fill_who_columns (
2484 p_bslv_rec IN bslv_rec_type
2485 ) RETURN bslv_rec_type IS
2486 l_bslv_rec bslv_rec_type := p_bslv_rec;
2487 BEGIN
2488 l_bslv_rec.CREATION_DATE := SYSDATE;
2489 l_bslv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2490 l_bslv_rec.LAST_UPDATE_DATE := SYSDATE;
2491 l_bslv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2492 l_bslv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2493 RETURN(l_bslv_rec);
2494 END fill_who_columns;
2495 ---------------------------------------------
2496 -- Set_Attributes for:OKS_BILL_SUB_LINES_V --
2497 ---------------------------------------------
2498 FUNCTION Set_Attributes (
2499 p_bslv_rec IN bslv_rec_type,
2500 x_bslv_rec OUT NOCOPY bslv_rec_type
2501 ) RETURN VARCHAR2 IS
2502 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2503 BEGIN
2504 x_bslv_rec := p_bslv_rec;
2505 x_bslv_rec.OBJECT_VERSION_NUMBER := 1;
2506 RETURN(l_return_status);
2507 END Set_Attributes;
2508 BEGIN
2509 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2510 G_PKG_NAME,
2511 p_init_msg_list,
2512 l_api_version,
2513 p_api_version,
2514 '_PVT',
2515 x_return_status);
2516 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2517 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2518 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2519 RAISE OKC_API.G_EXCEPTION_ERROR;
2520 END IF;
2521 l_bslv_rec := null_out_defaults(p_bslv_rec);
2522 -- Set primary key value
2523 l_bslv_rec.ID := get_seq_id;
2524 --- Setting item attributes
2525 l_return_status := Set_Attributes(
2526 l_bslv_rec, -- IN
2527 l_def_bslv_rec); -- OUT
2528 --- If any errors happen abort API
2529 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2530 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2531 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2532 RAISE OKC_API.G_EXCEPTION_ERROR;
2533 END IF;
2534 l_def_bslv_rec := fill_who_columns(l_def_bslv_rec);
2535 --- Validate all non-missing attributes (Item Level Validation)
2536 l_return_status := Validate_Attributes(l_def_bslv_rec);
2537 --- If any errors happen abort API
2538 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2540 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2541 RAISE OKC_API.G_EXCEPTION_ERROR;
2542 END IF;
2543 l_return_status := Validate_Record(l_def_bslv_rec);
2544 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2545 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2546 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2547 RAISE OKC_API.G_EXCEPTION_ERROR;
2548 END IF;
2549 --------------------------------------
2550 -- Move VIEW record to "Child" records
2551 --------------------------------------
2552 migrate(l_def_bslv_rec, l_bsl_rec);
2553 --------------------------------------------
2554 -- Call the INSERT_ROW for each child record
2555 --------------------------------------------
2556 insert_row(
2557 p_init_msg_list,
2558 x_return_status,
2559 x_msg_count,
2560 x_msg_data,
2561 l_bsl_rec,
2562 lx_bsl_rec
2563 );
2564 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2566 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2567 RAISE OKC_API.G_EXCEPTION_ERROR;
2568 END IF;
2569 migrate(lx_bsl_rec, l_def_bslv_rec);
2570 -- Set OUT values
2571 x_bslv_rec := l_def_bslv_rec;
2572 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2573 EXCEPTION
2574 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2575 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2576 (
2577 l_api_name,
2578 G_PKG_NAME,
2579 'OKC_API.G_RET_STS_ERROR',
2580 x_msg_count,
2581 x_msg_data,
2582 '_PVT'
2583 );
2584 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2585 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2586 (
2587 l_api_name,
2588 G_PKG_NAME,
2589 'OKC_API.G_RET_STS_UNEXP_ERROR',
2590 x_msg_count,
2591 x_msg_data,
2592 '_PVT'
2593 );
2594 WHEN OTHERS THEN
2595 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2596 (
2597 l_api_name,
2598 G_PKG_NAME,
2599 'OTHERS',
2600 x_msg_count,
2601 x_msg_data,
2602 '_PVT'
2603 );
2604 END insert_row;
2605 ----------------------------------------
2606 -- PL/SQL TBL insert_row for:BSLV_TBL --
2607 ----------------------------------------
2608 PROCEDURE insert_row(
2609 p_api_version IN NUMBER,
2610 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2611 x_return_status OUT NOCOPY VARCHAR2,
2612 x_msg_count OUT NOCOPY NUMBER,
2613 x_msg_data OUT NOCOPY VARCHAR2,
2614 p_bslv_tbl IN bslv_tbl_type,
2615 x_bslv_tbl OUT NOCOPY bslv_tbl_type) IS
2616
2617 l_api_version CONSTANT NUMBER := 1;
2618 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2619 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2620 i NUMBER := 0;
2621 BEGIN
2622 OKC_API.init_msg_list(p_init_msg_list);
2623 -- Make sure PL/SQL table has records in it before passing
2624 IF (p_bslv_tbl.COUNT > 0) THEN
2625 i := p_bslv_tbl.FIRST;
2626 LOOP
2627 insert_row (
2628 p_api_version => p_api_version,
2629 p_init_msg_list => OKC_API.G_FALSE,
2630 x_return_status => x_return_status,
2631 x_msg_count => x_msg_count,
2632 x_msg_data => x_msg_data,
2633 p_bslv_rec => p_bslv_tbl(i),
2634 x_bslv_rec => x_bslv_tbl(i));
2635 EXIT WHEN (i = p_bslv_tbl.LAST);
2636 i := p_bslv_tbl.NEXT(i);
2637 END LOOP;
2638 END IF;
2639 EXCEPTION
2640 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2641 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2642 (
2643 l_api_name,
2644 G_PKG_NAME,
2645 'OKC_API.G_RET_STS_ERROR',
2646 x_msg_count,
2647 x_msg_data,
2648 '_PVT'
2649 );
2650 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2651 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2652 (
2653 l_api_name,
2654 G_PKG_NAME,
2655 'OKC_API.G_RET_STS_UNEXP_ERROR',
2656 x_msg_count,
2657 x_msg_data,
2658 '_PVT'
2659 );
2660 WHEN OTHERS THEN
2661 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2662 (
2663 l_api_name,
2664 G_PKG_NAME,
2665 'OTHERS',
2666 x_msg_count,
2667 x_msg_data,
2668 '_PVT'
2669 );
2670 END insert_row;
2671
2672 ---------------------------------------------------------------------------
2673 -- PROCEDURE lock_row
2674 ---------------------------------------------------------------------------
2675 -------------------------------------
2676 -- lock_row for:OKS_BILL_SUB_LINES --
2677 -------------------------------------
2678 PROCEDURE lock_row(
2679 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2680 x_return_status OUT NOCOPY VARCHAR2,
2681 x_msg_count OUT NOCOPY NUMBER,
2682 x_msg_data OUT NOCOPY VARCHAR2,
2683 p_bsl_rec IN bsl_rec_type) IS
2684
2685 E_Resource_Busy EXCEPTION;
2686 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2687 CURSOR lock_csr (p_bsl_rec IN bsl_rec_type) IS
2688 SELECT OBJECT_VERSION_NUMBER
2689 FROM OKS_BILL_SUB_LINES
2690 WHERE ID = p_bsl_rec.id
2691 AND OBJECT_VERSION_NUMBER = p_bsl_rec.object_version_number
2692 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2693
2694 CURSOR lchk_csr (p_bsl_rec IN bsl_rec_type) IS
2695 SELECT OBJECT_VERSION_NUMBER
2696 FROM OKS_BILL_SUB_LINES
2697 WHERE ID = p_bsl_rec.id;
2698 l_api_version CONSTANT NUMBER := 1;
2699 l_api_name CONSTANT VARCHAR2(30) := 'LINES_lock_row';
2700 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2701 l_object_version_number OKS_BILL_SUB_LINES.OBJECT_VERSION_NUMBER%TYPE;
2702 lc_object_version_number OKS_BILL_SUB_LINES.OBJECT_VERSION_NUMBER%TYPE;
2703 l_row_notfound BOOLEAN := FALSE;
2704 lc_row_notfound BOOLEAN := FALSE;
2705 BEGIN
2706 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2707 p_init_msg_list,
2708 '_PVT',
2709 x_return_status);
2710 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2711 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2712 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2713 RAISE OKC_API.G_EXCEPTION_ERROR;
2714 END IF;
2715 BEGIN
2716 OPEN lock_csr(p_bsl_rec);
2717 FETCH lock_csr INTO l_object_version_number;
2718 l_row_notfound := lock_csr%NOTFOUND;
2719 CLOSE lock_csr;
2720 EXCEPTION
2721 WHEN E_Resource_Busy THEN
2722 IF (lock_csr%ISOPEN) THEN
2723 CLOSE lock_csr;
2724 END IF;
2725 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2726 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2727 END;
2728
2729 IF ( l_row_notfound ) THEN
2730 OPEN lchk_csr(p_bsl_rec);
2731 FETCH lchk_csr INTO lc_object_version_number;
2732 lc_row_notfound := lchk_csr%NOTFOUND;
2733 CLOSE lchk_csr;
2734 END IF;
2735 IF (lc_row_notfound) THEN
2736 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2737 RAISE OKC_API.G_EXCEPTION_ERROR;
2738 ELSIF lc_object_version_number > p_bsl_rec.object_version_number THEN
2739 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2740 RAISE OKC_API.G_EXCEPTION_ERROR;
2741 ELSIF lc_object_version_number <> p_bsl_rec.object_version_number THEN
2742 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2743 RAISE OKC_API.G_EXCEPTION_ERROR;
2744 ELSIF lc_object_version_number = -1 THEN
2745 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2746 RAISE OKC_API.G_EXCEPTION_ERROR;
2747 END IF;
2748 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2749 EXCEPTION
2750 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2751 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2752 (
2753 l_api_name,
2754 G_PKG_NAME,
2755 'OKC_API.G_RET_STS_ERROR',
2756 x_msg_count,
2757 x_msg_data,
2758 '_PVT'
2759 );
2760 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2761 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2762 (
2763 l_api_name,
2764 G_PKG_NAME,
2765 'OKC_API.G_RET_STS_UNEXP_ERROR',
2766 x_msg_count,
2767 x_msg_data,
2768 '_PVT'
2769 );
2770 WHEN OTHERS THEN
2771 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2772 (
2773 l_api_name,
2774 G_PKG_NAME,
2775 'OTHERS',
2776 x_msg_count,
2777 x_msg_data,
2778 '_PVT'
2779 );
2780 END lock_row;
2781 ---------------------------------------
2782 -- lock_row for:OKS_BILL_SUB_LINES_V --
2783 ---------------------------------------
2784 PROCEDURE lock_row(
2785 p_api_version IN NUMBER,
2786 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2787 x_return_status OUT NOCOPY VARCHAR2,
2788 x_msg_count OUT NOCOPY NUMBER,
2789 x_msg_data OUT NOCOPY VARCHAR2,
2790 p_bslv_rec IN bslv_rec_type) IS
2791
2792 l_api_version CONSTANT NUMBER := 1;
2793 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2794 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2795 l_bsl_rec bsl_rec_type;
2796 BEGIN
2797 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2798 G_PKG_NAME,
2799 p_init_msg_list,
2800 l_api_version,
2801 p_api_version,
2802 '_PVT',
2803 x_return_status);
2804 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2805 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2806 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2807 RAISE OKC_API.G_EXCEPTION_ERROR;
2808 END IF;
2809 --------------------------------------
2810 -- Move VIEW record to "Child" records
2811 --------------------------------------
2812 migrate(p_bslv_rec, l_bsl_rec);
2813 --------------------------------------------
2814 -- Call the LOCK_ROW for each child record
2815 --------------------------------------------
2816 lock_row(
2817 p_init_msg_list,
2818 x_return_status,
2819 x_msg_count,
2820 x_msg_data,
2821 l_bsl_rec
2822 );
2823 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2824 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2825 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2826 RAISE OKC_API.G_EXCEPTION_ERROR;
2827 END IF;
2828 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2829 EXCEPTION
2830 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2831 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2832 (
2833 l_api_name,
2834 G_PKG_NAME,
2835 'OKC_API.G_RET_STS_ERROR',
2836 x_msg_count,
2837 x_msg_data,
2838 '_PVT'
2839 );
2840 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2841 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2842 (
2843 l_api_name,
2844 G_PKG_NAME,
2845 'OKC_API.G_RET_STS_UNEXP_ERROR',
2846 x_msg_count,
2847 x_msg_data,
2848 '_PVT'
2849 );
2850 WHEN OTHERS THEN
2851 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2852 (
2853 l_api_name,
2854 G_PKG_NAME,
2855 'OTHERS',
2856 x_msg_count,
2857 x_msg_data,
2858 '_PVT'
2859 );
2860 END lock_row;
2861 --------------------------------------
2862 -- PL/SQL TBL lock_row for:BSLV_TBL --
2863 --------------------------------------
2864 PROCEDURE lock_row(
2865 p_api_version IN NUMBER,
2866 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2867 x_return_status OUT NOCOPY VARCHAR2,
2868 x_msg_count OUT NOCOPY NUMBER,
2869 x_msg_data OUT NOCOPY VARCHAR2,
2870 p_bslv_tbl IN bslv_tbl_type) IS
2871
2872 l_api_version CONSTANT NUMBER := 1;
2873 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2874 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2875 i NUMBER := 0;
2876 BEGIN
2877 OKC_API.init_msg_list(p_init_msg_list);
2878 -- Make sure PL/SQL table has records in it before passing
2879 IF (p_bslv_tbl.COUNT > 0) THEN
2880 i := p_bslv_tbl.FIRST;
2881 LOOP
2882 lock_row (
2883 p_api_version => p_api_version,
2884 p_init_msg_list => OKC_API.G_FALSE,
2885 x_return_status => x_return_status,
2886 x_msg_count => x_msg_count,
2887 x_msg_data => x_msg_data,
2888 p_bslv_rec => p_bslv_tbl(i));
2889 EXIT WHEN (i = p_bslv_tbl.LAST);
2890 i := p_bslv_tbl.NEXT(i);
2891 END LOOP;
2892 END IF;
2893 EXCEPTION
2894 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2895 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2896 (
2897 l_api_name,
2898 G_PKG_NAME,
2899 'OKC_API.G_RET_STS_ERROR',
2900 x_msg_count,
2901 x_msg_data,
2902 '_PVT'
2903 );
2904 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2905 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2906 (
2907 l_api_name,
2908 G_PKG_NAME,
2909 'OKC_API.G_RET_STS_UNEXP_ERROR',
2910 x_msg_count,
2911 x_msg_data,
2912 '_PVT'
2913 );
2914 WHEN OTHERS THEN
2915 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2916 (
2917 l_api_name,
2918 G_PKG_NAME,
2919 'OTHERS',
2920 x_msg_count,
2921 x_msg_data,
2922 '_PVT'
2923 );
2924 END lock_row;
2925
2926 ---------------------------------------------------------------------------
2927 -- PROCEDURE update_row
2928 ---------------------------------------------------------------------------
2929 ---------------------------------------
2930 -- update_row for:OKS_BILL_SUB_LINES --
2931 ---------------------------------------
2932 PROCEDURE update_row(
2933 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2934 x_return_status OUT NOCOPY VARCHAR2,
2935 x_msg_count OUT NOCOPY NUMBER,
2936 x_msg_data OUT NOCOPY VARCHAR2,
2937 p_bsl_rec IN bsl_rec_type,
2938 x_bsl_rec OUT NOCOPY bsl_rec_type) IS
2939
2940 l_api_version CONSTANT NUMBER := 1;
2941 l_api_name CONSTANT VARCHAR2(30) := 'LINES_update_row';
2942 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2943 l_bsl_rec bsl_rec_type := p_bsl_rec;
2944 l_def_bsl_rec bsl_rec_type;
2945 l_row_notfound BOOLEAN := TRUE;
2946 ----------------------------------
2947 -- FUNCTION populate_new_record --
2948 ----------------------------------
2949 FUNCTION populate_new_record (
2950 p_bsl_rec IN bsl_rec_type,
2951 x_bsl_rec OUT NOCOPY bsl_rec_type
2952 ) RETURN VARCHAR2 IS
2953 l_bsl_rec bsl_rec_type;
2954 l_row_notfound BOOLEAN := TRUE;
2955 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2956 BEGIN
2957 x_bsl_rec := p_bsl_rec;
2958 -- Get current database values
2959 l_bsl_rec := get_rec(p_bsl_rec, l_row_notfound);
2960 IF (l_row_notfound) THEN
2961 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2962 END IF;
2963 IF (x_bsl_rec.id = OKC_API.G_MISS_NUM)
2964 THEN
2965 x_bsl_rec.id := l_bsl_rec.id;
2966 END IF;
2967 IF (x_bsl_rec.bcl_id = OKC_API.G_MISS_NUM)
2968 THEN
2969 x_bsl_rec.bcl_id := l_bsl_rec.bcl_id;
2970 END IF;
2971 IF (x_bsl_rec.cle_id = OKC_API.G_MISS_NUM)
2972 THEN
2973 x_bsl_rec.cle_id := l_bsl_rec.cle_id;
2974 END IF;
2975 IF (x_bsl_rec.object_version_number = OKC_API.G_MISS_NUM)
2976 THEN
2977 x_bsl_rec.object_version_number := l_bsl_rec.object_version_number;
2978 END IF;
2979 IF (x_bsl_rec.created_by = OKC_API.G_MISS_NUM)
2980 THEN
2981 x_bsl_rec.created_by := l_bsl_rec.created_by;
2982 END IF;
2983 IF (x_bsl_rec.creation_date = OKC_API.G_MISS_DATE)
2984 THEN
2985 x_bsl_rec.creation_date := l_bsl_rec.creation_date;
2986 END IF;
2987 IF (x_bsl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2988 THEN
2989 x_bsl_rec.last_updated_by := l_bsl_rec.last_updated_by;
2990 END IF;
2991 IF (x_bsl_rec.last_update_date = OKC_API.G_MISS_DATE)
2992 THEN
2993 x_bsl_rec.last_update_date := l_bsl_rec.last_update_date;
2994 END IF;
2995 IF (x_bsl_rec.average = OKC_API.G_MISS_NUM)
2996 THEN
2997 x_bsl_rec.average := l_bsl_rec.average;
2998 END IF;
2999 IF (x_bsl_rec.amount = OKC_API.G_MISS_NUM)
3000 THEN
3001 x_bsl_rec.amount := l_bsl_rec.amount;
3002 END IF;
3003 IF (x_bsl_rec.MANUAL_CREDIT = OKC_API.G_MISS_NUM)
3004 THEN
3005 x_bsl_rec.MANUAL_CREDIT := l_bsl_rec.MANUAL_CREDIT;
3006 END IF;
3007 IF (x_bsl_rec.date_billed_from = OKC_API.G_MISS_DATE)
3008 THEN
3009 x_bsl_rec.date_billed_from := l_bsl_rec.date_billed_from;
3010 END IF;
3011 IF (x_bsl_rec.date_billed_to = OKC_API.G_MISS_DATE)
3012 THEN
3013 x_bsl_rec.date_billed_to := l_bsl_rec.date_billed_to;
3014 END IF;
3015 IF (x_bsl_rec.date_to_interface = OKC_API.G_MISS_DATE)
3016 THEN
3017 x_bsl_rec.date_to_interface := l_bsl_rec.date_to_interface;
3018 END IF;
3019 IF (x_bsl_rec.last_update_login = OKC_API.G_MISS_NUM)
3020 THEN
3021 x_bsl_rec.last_update_login := l_bsl_rec.last_update_login;
3022 END IF;
3023 IF (x_bsl_rec.attribute_category = OKC_API.G_MISS_CHAR)
3024 THEN
3025 x_bsl_rec.attribute_category := l_bsl_rec.attribute_category;
3026 END IF;
3027 IF (x_bsl_rec.attribute1 = OKC_API.G_MISS_CHAR)
3028 THEN
3029 x_bsl_rec.attribute1 := l_bsl_rec.attribute1;
3030 END IF;
3031 IF (x_bsl_rec.attribute2 = OKC_API.G_MISS_CHAR)
3032 THEN
3033 x_bsl_rec.attribute2 := l_bsl_rec.attribute2;
3034 END IF;
3035 IF (x_bsl_rec.attribute3 = OKC_API.G_MISS_CHAR)
3036 THEN
3037 x_bsl_rec.attribute3 := l_bsl_rec.attribute3;
3038 END IF;
3039 IF (x_bsl_rec.attribute4 = OKC_API.G_MISS_CHAR)
3040 THEN
3041 x_bsl_rec.attribute4 := l_bsl_rec.attribute4;
3042 END IF;
3043 IF (x_bsl_rec.attribute5 = OKC_API.G_MISS_CHAR)
3044 THEN
3045 x_bsl_rec.attribute5 := l_bsl_rec.attribute5;
3046 END IF;
3047 IF (x_bsl_rec.attribute6 = OKC_API.G_MISS_CHAR)
3048 THEN
3049 x_bsl_rec.attribute6 := l_bsl_rec.attribute6;
3050 END IF;
3051 IF (x_bsl_rec.attribute7 = OKC_API.G_MISS_CHAR)
3052 THEN
3053 x_bsl_rec.attribute7 := l_bsl_rec.attribute7;
3054 END IF;
3055 IF (x_bsl_rec.attribute8 = OKC_API.G_MISS_CHAR)
3056 THEN
3057 x_bsl_rec.attribute8 := l_bsl_rec.attribute8;
3058 END IF;
3059 IF (x_bsl_rec.attribute9 = OKC_API.G_MISS_CHAR)
3060 THEN
3061 x_bsl_rec.attribute9 := l_bsl_rec.attribute9;
3062 END IF;
3063 IF (x_bsl_rec.attribute10 = OKC_API.G_MISS_CHAR)
3064 THEN
3065 x_bsl_rec.attribute10 := l_bsl_rec.attribute10;
3066 END IF;
3067 IF (x_bsl_rec.attribute11 = OKC_API.G_MISS_CHAR)
3068 THEN
3069 x_bsl_rec.attribute11 := l_bsl_rec.attribute11;
3070 END IF;
3071 IF (x_bsl_rec.attribute12 = OKC_API.G_MISS_CHAR)
3072 THEN
3073 x_bsl_rec.attribute12 := l_bsl_rec.attribute12;
3074 END IF;
3075 IF (x_bsl_rec.attribute13 = OKC_API.G_MISS_CHAR)
3076 THEN
3077 x_bsl_rec.attribute13 := l_bsl_rec.attribute13;
3078 END IF;
3079 IF (x_bsl_rec.attribute14 = OKC_API.G_MISS_CHAR)
3080 THEN
3081 x_bsl_rec.attribute14 := l_bsl_rec.attribute14;
3082 END IF;
3083 IF (x_bsl_rec.attribute15 = OKC_API.G_MISS_CHAR)
3084 THEN
3085 x_bsl_rec.attribute15 := l_bsl_rec.attribute15;
3086 END IF;
3087 RETURN(l_return_status);
3088 END populate_new_record;
3089 -------------------------------------------
3090 -- Set_Attributes for:OKS_BILL_SUB_LINES --
3091 -------------------------------------------
3092 FUNCTION Set_Attributes (
3093 p_bsl_rec IN bsl_rec_type,
3094 x_bsl_rec OUT NOCOPY bsl_rec_type
3095 ) RETURN VARCHAR2 IS
3096 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3097 BEGIN
3098 x_bsl_rec := p_bsl_rec;
3099 RETURN(l_return_status);
3100 END Set_Attributes;
3101 BEGIN
3102 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3103 p_init_msg_list,
3104 '_PVT',
3105 x_return_status);
3106 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3107 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3108 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3109 RAISE OKC_API.G_EXCEPTION_ERROR;
3110 END IF;
3111 --- Setting item attributes
3112 l_return_status := Set_Attributes(
3113 p_bsl_rec, -- IN
3114 l_bsl_rec); -- OUT
3115 --- If any errors happen abort API
3116 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3117 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3118 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3119 RAISE OKC_API.G_EXCEPTION_ERROR;
3120 END IF;
3121 l_return_status := populate_new_record(l_bsl_rec, l_def_bsl_rec);
3122 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3123 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3124 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3125 RAISE OKC_API.G_EXCEPTION_ERROR;
3126 END IF;
3127 UPDATE OKS_BILL_SUB_LINES
3128 SET BCL_ID = l_def_bsl_rec.bcl_id,
3129 CLE_ID = l_def_bsl_rec.cle_id,
3130 OBJECT_VERSION_NUMBER = l_def_bsl_rec.object_version_number,
3131 CREATED_BY = l_def_bsl_rec.created_by,
3132 CREATION_DATE = l_def_bsl_rec.creation_date,
3133 LAST_UPDATED_BY = l_def_bsl_rec.last_updated_by,
3134 LAST_UPDATE_DATE = l_def_bsl_rec.last_update_date,
3135 AVERAGE = l_def_bsl_rec.average,
3136 AMOUNT = l_def_bsl_rec.amount,
3137 MANUAL_CREDIT = l_def_bsl_rec.MANUAL_CREDIT,
3138 DATE_BILLED_FROM = l_def_bsl_rec.date_billed_from,
3139 DATE_BILLED_TO = l_def_bsl_rec.date_billed_to,
3140 DATE_TO_INTERFACE = l_def_bsl_rec.date_to_interface,
3141 LAST_UPDATE_LOGIN = l_def_bsl_rec.last_update_login,
3142 ATTRIBUTE_CATEGORY = l_def_bsl_rec.attribute_category,
3143 ATTRIBUTE1 = l_def_bsl_rec.attribute1,
3144 ATTRIBUTE2 = l_def_bsl_rec.attribute2,
3145 ATTRIBUTE3 = l_def_bsl_rec.attribute3,
3146 ATTRIBUTE4 = l_def_bsl_rec.attribute4,
3147 ATTRIBUTE5 = l_def_bsl_rec.attribute5,
3148 ATTRIBUTE6 = l_def_bsl_rec.attribute6,
3149 ATTRIBUTE7 = l_def_bsl_rec.attribute7,
3150 ATTRIBUTE8 = l_def_bsl_rec.attribute8,
3151 ATTRIBUTE9 = l_def_bsl_rec.attribute9,
3152 ATTRIBUTE10 = l_def_bsl_rec.attribute10,
3153 ATTRIBUTE11 = l_def_bsl_rec.attribute11,
3154 ATTRIBUTE12 = l_def_bsl_rec.attribute12,
3155 ATTRIBUTE13 = l_def_bsl_rec.attribute13,
3156 ATTRIBUTE14 = l_def_bsl_rec.attribute14,
3157 ATTRIBUTE15 = l_def_bsl_rec.attribute15
3158 WHERE ID = l_def_bsl_rec.id;
3159
3160 x_bsl_rec := l_def_bsl_rec;
3161 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3162 EXCEPTION
3163 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3164 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3165 (
3166 l_api_name,
3167 G_PKG_NAME,
3168 'OKC_API.G_RET_STS_ERROR',
3169 x_msg_count,
3170 x_msg_data,
3171 '_PVT'
3172 );
3173 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3174 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3175 (
3176 l_api_name,
3177 G_PKG_NAME,
3178 'OKC_API.G_RET_STS_UNEXP_ERROR',
3179 x_msg_count,
3180 x_msg_data,
3181 '_PVT'
3182 );
3183 WHEN OTHERS THEN
3184 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3185 (
3186 l_api_name,
3187 G_PKG_NAME,
3188 'OTHERS',
3189 x_msg_count,
3190 x_msg_data,
3191 '_PVT'
3192 );
3193 END update_row;
3194 -----------------------------------------
3195 -- update_row for:OKS_BILL_SUB_LINES_V --
3196 -----------------------------------------
3197 PROCEDURE update_row(
3198 p_api_version IN NUMBER,
3199 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3200 x_return_status OUT NOCOPY VARCHAR2,
3201 x_msg_count OUT NOCOPY NUMBER,
3202 x_msg_data OUT NOCOPY VARCHAR2,
3203 p_bslv_rec IN bslv_rec_type,
3204 x_bslv_rec OUT NOCOPY bslv_rec_type) IS
3205
3206 l_api_version CONSTANT NUMBER := 1;
3207 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3208 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3209 l_bslv_rec bslv_rec_type := p_bslv_rec;
3210 l_def_bslv_rec bslv_rec_type;
3211 l_bsl_rec bsl_rec_type;
3212 lx_bsl_rec bsl_rec_type;
3213 -------------------------------
3214 -- FUNCTION fill_who_columns --
3215 -------------------------------
3216 FUNCTION fill_who_columns (
3217 p_bslv_rec IN bslv_rec_type
3218 ) RETURN bslv_rec_type IS
3219 l_bslv_rec bslv_rec_type := p_bslv_rec;
3220 BEGIN
3221 l_bslv_rec.LAST_UPDATE_DATE := SYSDATE;
3222 l_bslv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3223 l_bslv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3224 RETURN(l_bslv_rec);
3225 END fill_who_columns;
3226 ----------------------------------
3227 -- FUNCTION populate_new_record --
3228 ----------------------------------
3229 FUNCTION populate_new_record (
3230 p_bslv_rec IN bslv_rec_type,
3231 x_bslv_rec OUT NOCOPY bslv_rec_type
3232 ) RETURN VARCHAR2 IS
3233 l_bslv_rec bslv_rec_type;
3234 l_row_notfound BOOLEAN := TRUE;
3235 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3236 BEGIN
3237 x_bslv_rec := p_bslv_rec;
3238 -- Get current database values
3239 l_bslv_rec := get_rec(p_bslv_rec, l_row_notfound);
3240 IF (l_row_notfound) THEN
3241 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3242 END IF;
3243 IF (x_bslv_rec.id = OKC_API.G_MISS_NUM)
3244 THEN
3245 x_bslv_rec.id := l_bslv_rec.id;
3246 END IF;
3247 IF (x_bslv_rec.object_version_number = OKC_API.G_MISS_NUM)
3248 THEN
3249 x_bslv_rec.object_version_number := l_bslv_rec.object_version_number;
3250 END IF;
3251 IF (x_bslv_rec.bcl_id = OKC_API.G_MISS_NUM)
3252 THEN
3253 x_bslv_rec.bcl_id := l_bslv_rec.bcl_id;
3254 END IF;
3255 IF (x_bslv_rec.cle_id = OKC_API.G_MISS_NUM)
3256 THEN
3257 x_bslv_rec.cle_id := l_bslv_rec.cle_id;
3258 END IF;
3259 IF (x_bslv_rec.average = OKC_API.G_MISS_NUM)
3260 THEN
3261 x_bslv_rec.average := l_bslv_rec.average;
3262 END IF;
3263 IF (x_bslv_rec.amount = OKC_API.G_MISS_NUM)
3264 THEN
3265 x_bslv_rec.amount := l_bslv_rec.amount;
3266 END IF;
3267 IF (x_bslv_rec.MANUAL_CREDIT = OKC_API.G_MISS_NUM)
3268 THEN
3269 x_bslv_rec.MANUAL_CREDIT := l_bslv_rec.MANUAL_CREDIT;
3270 END IF;
3271 IF (x_bslv_rec.date_billed_from = OKC_API.G_MISS_DATE)
3272 THEN
3273 x_bslv_rec.date_billed_from := l_bslv_rec.date_billed_from;
3274 END IF;
3275 IF (x_bslv_rec.date_billed_to = OKC_API.G_MISS_DATE)
3276 THEN
3277 x_bslv_rec.date_billed_to := l_bslv_rec.date_billed_to;
3278 END IF;
3279 IF (x_bslv_rec.date_to_interface = OKC_API.G_MISS_DATE)
3280 THEN
3281 x_bslv_rec.date_to_interface := l_bslv_rec.date_to_interface;
3282 END IF;
3283 IF (x_bslv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3284 THEN
3285 x_bslv_rec.attribute_category := l_bslv_rec.attribute_category;
3286 END IF;
3287 IF (x_bslv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3288 THEN
3289 x_bslv_rec.attribute1 := l_bslv_rec.attribute1;
3290 END IF;
3291 IF (x_bslv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3292 THEN
3293 x_bslv_rec.attribute2 := l_bslv_rec.attribute2;
3294 END IF;
3295 IF (x_bslv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3296 THEN
3297 x_bslv_rec.attribute3 := l_bslv_rec.attribute3;
3298 END IF;
3299 IF (x_bslv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3300 THEN
3301 x_bslv_rec.attribute4 := l_bslv_rec.attribute4;
3302 END IF;
3303 IF (x_bslv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3304 THEN
3305 x_bslv_rec.attribute5 := l_bslv_rec.attribute5;
3306 END IF;
3307 IF (x_bslv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3308 THEN
3309 x_bslv_rec.attribute6 := l_bslv_rec.attribute6;
3310 END IF;
3311 IF (x_bslv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3312 THEN
3313 x_bslv_rec.attribute7 := l_bslv_rec.attribute7;
3314 END IF;
3315 IF (x_bslv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3316 THEN
3317 x_bslv_rec.attribute8 := l_bslv_rec.attribute8;
3318 END IF;
3319 IF (x_bslv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3320 THEN
3321 x_bslv_rec.attribute9 := l_bslv_rec.attribute9;
3322 END IF;
3323 IF (x_bslv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3324 THEN
3325 x_bslv_rec.attribute10 := l_bslv_rec.attribute10;
3326 END IF;
3327 IF (x_bslv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3328 THEN
3329 x_bslv_rec.attribute11 := l_bslv_rec.attribute11;
3330 END IF;
3331 IF (x_bslv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3332 THEN
3333 x_bslv_rec.attribute12 := l_bslv_rec.attribute12;
3334 END IF;
3335 IF (x_bslv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3336 THEN
3337 x_bslv_rec.attribute13 := l_bslv_rec.attribute13;
3338 END IF;
3339 IF (x_bslv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3340 THEN
3341 x_bslv_rec.attribute14 := l_bslv_rec.attribute14;
3342 END IF;
3343 IF (x_bslv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3344 THEN
3345 x_bslv_rec.attribute15 := l_bslv_rec.attribute15;
3346 END IF;
3347 IF (x_bslv_rec.created_by = OKC_API.G_MISS_NUM)
3348 THEN
3349 x_bslv_rec.created_by := l_bslv_rec.created_by;
3350 END IF;
3351 IF (x_bslv_rec.creation_date = OKC_API.G_MISS_DATE)
3352 THEN
3353 x_bslv_rec.creation_date := l_bslv_rec.creation_date;
3354 END IF;
3355 IF (x_bslv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3356 THEN
3357 x_bslv_rec.last_updated_by := l_bslv_rec.last_updated_by;
3358 END IF;
3359 IF (x_bslv_rec.last_update_date = OKC_API.G_MISS_DATE)
3360 THEN
3361 x_bslv_rec.last_update_date := l_bslv_rec.last_update_date;
3362 END IF;
3363 IF (x_bslv_rec.last_update_login = OKC_API.G_MISS_NUM)
3364 THEN
3365 x_bslv_rec.last_update_login := l_bslv_rec.last_update_login;
3366 END IF;
3367 RETURN(l_return_status);
3368 END populate_new_record;
3369 ---------------------------------------------
3370 -- Set_Attributes for:OKS_BILL_SUB_LINES_V --
3371 ---------------------------------------------
3372 FUNCTION Set_Attributes (
3373 p_bslv_rec IN bslv_rec_type,
3374 x_bslv_rec OUT NOCOPY bslv_rec_type
3375 ) RETURN VARCHAR2 IS
3376 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3377 BEGIN
3378 x_bslv_rec := p_bslv_rec;
3379 x_bslv_rec.OBJECT_VERSION_NUMBER := NVL(x_bslv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3380 RETURN(l_return_status);
3381 END Set_Attributes;
3382 BEGIN
3383 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3384 G_PKG_NAME,
3385 p_init_msg_list,
3386 l_api_version,
3387 p_api_version,
3388 '_PVT',
3389 x_return_status);
3390 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3392 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3393 RAISE OKC_API.G_EXCEPTION_ERROR;
3394 END IF;
3395 --- Setting item attributes
3396 l_return_status := Set_Attributes(
3397 p_bslv_rec, -- IN
3398 l_bslv_rec); -- OUT
3399 --- If any errors happen abort API
3400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3403 RAISE OKC_API.G_EXCEPTION_ERROR;
3404 END IF;
3405 l_return_status := populate_new_record(l_bslv_rec, l_def_bslv_rec);
3406 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3407 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3408 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3409 RAISE OKC_API.G_EXCEPTION_ERROR;
3410 END IF;
3411 l_def_bslv_rec := fill_who_columns(l_def_bslv_rec);
3412 --- Validate all non-missing attributes (Item Level Validation)
3413 l_return_status := Validate_Attributes(l_def_bslv_rec);
3414 --- If any errors happen abort API
3415 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3416 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3417 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3418 RAISE OKC_API.G_EXCEPTION_ERROR;
3419 END IF;
3420 l_return_status := Validate_Record(l_def_bslv_rec);
3421 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3422 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3423 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3424 RAISE OKC_API.G_EXCEPTION_ERROR;
3425 END IF;
3426
3427 --------------------------------------
3428 -- Move VIEW record to "Child" records
3429 --------------------------------------
3430 migrate(l_def_bslv_rec, l_bsl_rec);
3431 --------------------------------------------
3432 -- Call the UPDATE_ROW for each child record
3433 --------------------------------------------
3434 update_row(
3435 p_init_msg_list,
3436 x_return_status,
3437 x_msg_count,
3438 x_msg_data,
3439 l_bsl_rec,
3440 lx_bsl_rec
3441 );
3442 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3443 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3444 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3445 RAISE OKC_API.G_EXCEPTION_ERROR;
3446 END IF;
3447 migrate(lx_bsl_rec, l_def_bslv_rec);
3448 x_bslv_rec := l_def_bslv_rec;
3449 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3450 EXCEPTION
3451 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3452 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3453 (
3454 l_api_name,
3455 G_PKG_NAME,
3456 'OKC_API.G_RET_STS_ERROR',
3457 x_msg_count,
3458 x_msg_data,
3459 '_PVT'
3460 );
3461 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3462 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3463 (
3464 l_api_name,
3465 G_PKG_NAME,
3466 'OKC_API.G_RET_STS_UNEXP_ERROR',
3467 x_msg_count,
3468 x_msg_data,
3469 '_PVT'
3470 );
3471 WHEN OTHERS THEN
3472 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3473 (
3474 l_api_name,
3475 G_PKG_NAME,
3476 'OTHERS',
3477 x_msg_count,
3478 x_msg_data,
3479 '_PVT'
3480 );
3481 END update_row;
3482 ----------------------------------------
3483 -- PL/SQL TBL update_row for:BSLV_TBL --
3484 ----------------------------------------
3485 PROCEDURE update_row(
3486 p_api_version IN NUMBER,
3487 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3488 x_return_status OUT NOCOPY VARCHAR2,
3489 x_msg_count OUT NOCOPY NUMBER,
3490 x_msg_data OUT NOCOPY VARCHAR2,
3491 p_bslv_tbl IN bslv_tbl_type,
3492 x_bslv_tbl OUT NOCOPY bslv_tbl_type) IS
3493
3494 l_api_version CONSTANT NUMBER := 1;
3495 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3496 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3497 i NUMBER := 0;
3498 BEGIN
3499 OKC_API.init_msg_list(p_init_msg_list);
3500 -- Make sure PL/SQL table has records in it before passing
3501 IF (p_bslv_tbl.COUNT > 0) THEN
3502 i := p_bslv_tbl.FIRST;
3503 LOOP
3504 update_row (
3505 p_api_version => p_api_version,
3506 p_init_msg_list => OKC_API.G_FALSE,
3507 x_return_status => x_return_status,
3508 x_msg_count => x_msg_count,
3509 x_msg_data => x_msg_data,
3510 p_bslv_rec => p_bslv_tbl(i),
3511 x_bslv_rec => x_bslv_tbl(i));
3512 EXIT WHEN (i = p_bslv_tbl.LAST);
3513 i := p_bslv_tbl.NEXT(i);
3514 END LOOP;
3515 END IF;
3516 EXCEPTION
3517 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3518 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3519 (
3520 l_api_name,
3521 G_PKG_NAME,
3522 'OKC_API.G_RET_STS_ERROR',
3523 x_msg_count,
3524 x_msg_data,
3525 '_PVT'
3526 );
3527 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3528 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3529 (
3530 l_api_name,
3531 G_PKG_NAME,
3532 'OKC_API.G_RET_STS_UNEXP_ERROR',
3533 x_msg_count,
3534 x_msg_data,
3535 '_PVT'
3536 );
3537 WHEN OTHERS THEN
3538 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3539 (
3540 l_api_name,
3541 G_PKG_NAME,
3542 'OTHERS',
3543 x_msg_count,
3544 x_msg_data,
3545 '_PVT'
3546 );
3547 END update_row;
3548
3549 ---------------------------------------------------------------------------
3550 -- PROCEDURE delete_row
3551 ---------------------------------------------------------------------------
3552 ---------------------------------------
3553 -- delete_row for:OKS_BILL_SUB_LINES --
3554 ---------------------------------------
3555 PROCEDURE delete_row(
3556 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3557 x_return_status OUT NOCOPY VARCHAR2,
3558 x_msg_count OUT NOCOPY NUMBER,
3559 x_msg_data OUT NOCOPY VARCHAR2,
3560 p_bsl_rec IN bsl_rec_type) IS
3561
3562 l_api_version CONSTANT NUMBER := 1;
3563 l_api_name CONSTANT VARCHAR2(30) := 'LINES_delete_row';
3564 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3565 l_bsl_rec bsl_rec_type:= p_bsl_rec;
3566 l_row_notfound BOOLEAN := TRUE;
3567 BEGIN
3568 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3569 p_init_msg_list,
3570 '_PVT',
3571 x_return_status);
3572 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3573 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3574 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3575 RAISE OKC_API.G_EXCEPTION_ERROR;
3576 END IF;
3577 DELETE FROM OKS_BILL_SUB_LINES
3578 WHERE ID = l_bsl_rec.id;
3579
3580 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3581 EXCEPTION
3582 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3583 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3584 (
3585 l_api_name,
3586 G_PKG_NAME,
3587 'OKC_API.G_RET_STS_ERROR',
3588 x_msg_count,
3589 x_msg_data,
3590 '_PVT'
3591 );
3592 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3593 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3594 (
3595 l_api_name,
3596 G_PKG_NAME,
3597 'OKC_API.G_RET_STS_UNEXP_ERROR',
3598 x_msg_count,
3599 x_msg_data,
3600 '_PVT'
3601 );
3602 WHEN OTHERS THEN
3603 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3604 (
3605 l_api_name,
3606 G_PKG_NAME,
3607 'OTHERS',
3608 x_msg_count,
3609 x_msg_data,
3610 '_PVT'
3611 );
3612 END delete_row;
3613 -----------------------------------------
3614 -- delete_row for:OKS_BILL_SUB_LINES_V --
3615 -----------------------------------------
3616 PROCEDURE delete_row(
3617 p_api_version IN NUMBER,
3618 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3619 x_return_status OUT NOCOPY VARCHAR2,
3620 x_msg_count OUT NOCOPY NUMBER,
3621 x_msg_data OUT NOCOPY VARCHAR2,
3622 p_bslv_rec IN bslv_rec_type) IS
3623
3624 l_api_version CONSTANT NUMBER := 1;
3625 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3626 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3627 l_bslv_rec bslv_rec_type := p_bslv_rec;
3628 l_bsl_rec bsl_rec_type;
3629 BEGIN
3630 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3631 G_PKG_NAME,
3632 p_init_msg_list,
3633 l_api_version,
3634 p_api_version,
3635 '_PVT',
3636 x_return_status);
3637 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3638 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3639 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3640 RAISE OKC_API.G_EXCEPTION_ERROR;
3641 END IF;
3642 --------------------------------------
3643 -- Move VIEW record to "Child" records
3644 --------------------------------------
3645 migrate(l_bslv_rec, l_bsl_rec);
3646 --------------------------------------------
3647 -- Call the DELETE_ROW for each child record
3648 --------------------------------------------
3649 delete_row(
3650 p_init_msg_list,
3651 x_return_status,
3652 x_msg_count,
3653 x_msg_data,
3654 l_bsl_rec
3655 );
3656 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3657 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3658 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3659 RAISE OKC_API.G_EXCEPTION_ERROR;
3660 END IF;
3661 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3662 EXCEPTION
3663 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3664 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3665 (
3666 l_api_name,
3667 G_PKG_NAME,
3668 'OKC_API.G_RET_STS_ERROR',
3669 x_msg_count,
3670 x_msg_data,
3671 '_PVT'
3672 );
3673 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3674 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3675 (
3676 l_api_name,
3677 G_PKG_NAME,
3678 'OKC_API.G_RET_STS_UNEXP_ERROR',
3679 x_msg_count,
3680 x_msg_data,
3681 '_PVT'
3682 );
3683 WHEN OTHERS THEN
3684 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3685 (
3686 l_api_name,
3687 G_PKG_NAME,
3688 'OTHERS',
3689 x_msg_count,
3690 x_msg_data,
3691 '_PVT'
3692 );
3693 END delete_row;
3694 ----------------------------------------
3695 -- PL/SQL TBL delete_row for:BSLV_TBL --
3696 ----------------------------------------
3697 PROCEDURE delete_row(
3698 p_api_version IN NUMBER,
3699 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3700 x_return_status OUT NOCOPY VARCHAR2,
3701 x_msg_count OUT NOCOPY NUMBER,
3702 x_msg_data OUT NOCOPY VARCHAR2,
3703 p_bslv_tbl IN bslv_tbl_type) IS
3704
3705 l_api_version CONSTANT NUMBER := 1;
3706 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3707 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3708 i NUMBER := 0;
3709 BEGIN
3710 OKC_API.init_msg_list(p_init_msg_list);
3711 -- Make sure PL/SQL table has records in it before passing
3712 IF (p_bslv_tbl.COUNT > 0) THEN
3713 i := p_bslv_tbl.FIRST;
3714 LOOP
3715 delete_row (
3716 p_api_version => p_api_version,
3717 p_init_msg_list => OKC_API.G_FALSE,
3718 x_return_status => x_return_status,
3719 x_msg_count => x_msg_count,
3720 x_msg_data => x_msg_data,
3721 p_bslv_rec => p_bslv_tbl(i));
3722 EXIT WHEN (i = p_bslv_tbl.LAST);
3723 i := p_bslv_tbl.NEXT(i);
3724 END LOOP;
3725 END IF;
3726 EXCEPTION
3727 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3728 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3729 (
3730 l_api_name,
3731 G_PKG_NAME,
3732 'OKC_API.G_RET_STS_ERROR',
3733 x_msg_count,
3734 x_msg_data,
3735 '_PVT'
3736 );
3737 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3738 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3739 (
3740 l_api_name,
3741 G_PKG_NAME,
3742 'OKC_API.G_RET_STS_UNEXP_ERROR',
3743 x_msg_count,
3744 x_msg_data,
3745 '_PVT'
3746 );
3747 WHEN OTHERS THEN
3748 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3749 (
3750 l_api_name,
3751 G_PKG_NAME,
3752 'OTHERS',
3753 x_msg_count,
3754 x_msg_data,
3755 '_PVT'
3756 );
3757 END delete_row;
3758 END OKS_BSL_PVT;