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