[Home] [Help]
PACKAGE BODY: APPS.OKL_SEL_PVT
Source
1 PACKAGE BODY Okl_Sel_Pvt AS
2 /* $Header: OKLSSELB.pls 120.6 2007/03/13 08:42:42 prasjain 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: OKL_STRM_ELEMENTS
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_sel_rec IN sel_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN sel_rec_type IS
42 CURSOR okl_strm_elements_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 STM_ID,
46 OBJECT_VERSION_NUMBER,
47 STREAM_ELEMENT_DATE,
48 AMOUNT,
49 COMMENTS,
50 ACCRUED_YN,
51 PROGRAM_ID,
52 REQUEST_ID,
53 PROGRAM_APPLICATION_ID,
54 PROGRAM_UPDATE_DATE,
55 SE_LINE_NUMBER,
56 DATE_BILLED,
57 CREATED_BY,
58 CREATION_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATE_LOGIN,
62 SEL_ID,
63 --Added by Keerthi 15-Sep-2003
64 SOURCE_ID,
65 SOURCE_TABLE,
66 -- Added by rgooty: 4212626
67 bill_adj_flag,
68 accrual_adj_flag,
69 -- Added by hkpatel for bug 4350255
70 date_disbursed
71 FROM Okl_Strm_Elements
72 WHERE okl_strm_elements.id = p_id;
73 l_okl_strm_elements_pk okl_strm_elements_pk_csr%ROWTYPE;
74 l_sel_rec sel_rec_type;
75 BEGIN
76 x_no_data_found := TRUE;
77 -- Get current database values
78 OPEN okl_strm_elements_pk_csr (p_sel_rec.id);
79 FETCH okl_strm_elements_pk_csr INTO
80 l_sel_rec.ID,
81 l_sel_rec.STM_ID,
82 l_sel_rec.OBJECT_VERSION_NUMBER,
83 l_sel_rec.STREAM_ELEMENT_DATE,
84 l_sel_rec.AMOUNT,
85 l_sel_rec.COMMENTS,
86 l_sel_rec.ACCRUED_YN,
87 l_sel_rec.PROGRAM_ID,
88 l_sel_rec.REQUEST_ID,
89 l_sel_rec.PROGRAM_APPLICATION_ID,
90 l_sel_rec.PROGRAM_UPDATE_DATE,
91 l_sel_rec.SE_LINE_NUMBER,
92 l_sel_rec.DATE_BILLED,
93 l_sel_rec.CREATED_BY,
94 l_sel_rec.CREATION_DATE,
95 l_sel_rec.LAST_UPDATED_BY,
96 l_sel_rec.LAST_UPDATE_DATE,
97 l_sel_rec.LAST_UPDATE_LOGIN,
98 l_sel_rec.SEL_ID,
99 -- Added by Keerthi 15-Sep-2003
100 l_sel_rec.SOURCE_ID,
101 l_sel_rec.SOURCE_TABLE,
102 -- Added by rgooty: 4212626
103 l_sel_rec.bill_adj_flag,
104 l_sel_rec.accrual_adj_flag,
105 -- Added by hkpatel for bug 4350255
106 l_sel_rec.date_disbursed;
107 x_no_data_found := okl_strm_elements_pk_csr%NOTFOUND;
108 CLOSE okl_strm_elements_pk_csr;
109 RETURN(l_sel_rec);
110 END get_rec;
111
112 FUNCTION get_rec (
113 p_sel_rec IN sel_rec_type
114 ) RETURN sel_rec_type IS
115 l_row_notfound BOOLEAN := TRUE;
116 BEGIN
117 RETURN(get_rec(p_sel_rec, l_row_notfound));
118 END get_rec;
119 ---------------------------------------------------------------------------
120 -- FUNCTION get_rec for: OKL_STRM_ELEMENTS_V
121 ---------------------------------------------------------------------------
122 FUNCTION get_rec (
123 p_selv_rec IN selv_rec_type,
124 x_no_data_found OUT NOCOPY BOOLEAN
125 ) RETURN selv_rec_type IS
126 CURSOR okl_selv_pk_csr (p_id IN NUMBER) IS
127 SELECT
128 ID,
129 OBJECT_VERSION_NUMBER,
130 STM_ID,
131 AMOUNT,
132 COMMENTS,
133 ACCRUED_YN,
134 STREAM_ELEMENT_DATE,
135 PROGRAM_ID,
136 REQUEST_ID,
137 PROGRAM_APPLICATION_ID,
138 PROGRAM_UPDATE_DATE,
139 SE_LINE_NUMBER,
140 DATE_BILLED,
141 CREATED_BY,
142 CREATION_DATE,
143 LAST_UPDATED_BY,
144 LAST_UPDATE_DATE,
145 LAST_UPDATE_LOGIN,
146 SEL_ID,
147 --Added by Keerthi 15-Sep-2003
148 SOURCE_ID,
149 SOURCE_TABLE,
150 -- Added by rgooty: 4212626
151 BILL_ADJ_FLAG,
152 ACCRUAL_ADJ_FLAG,
153 -- Added by hkpatel for bug 4350255
154 DATE_DISBURSED
155 FROM Okl_Strm_Elements_V
156 WHERE okl_strm_elements_v.id = p_id;
157 l_okl_selv_pk okl_selv_pk_csr%ROWTYPE;
158 l_selv_rec selv_rec_type;
159 BEGIN
160 x_no_data_found := TRUE;
161 -- Get current database values
162 OPEN okl_selv_pk_csr (p_selv_rec.id);
163 FETCH okl_selv_pk_csr INTO
164 l_selv_rec.ID,
165 l_selv_rec.OBJECT_VERSION_NUMBER,
166 l_selv_rec.STM_ID,
167 l_selv_rec.AMOUNT,
168 l_selv_rec.COMMENTS,
169 l_selv_rec.ACCRUED_YN,
170 l_selv_rec.STREAM_ELEMENT_DATE,
171 l_selv_rec.PROGRAM_ID,
172 l_selv_rec.REQUEST_ID,
173 l_selv_rec.PROGRAM_APPLICATION_ID,
174 l_selv_rec.PROGRAM_UPDATE_DATE,
175 l_selv_rec.SE_LINE_NUMBER,
176 l_selv_rec.DATE_BILLED,
177 l_selv_rec.CREATED_BY,
178 l_selv_rec.CREATION_DATE,
179 l_selv_rec.LAST_UPDATED_BY,
180 l_selv_rec.LAST_UPDATE_DATE,
181 l_selv_rec.LAST_UPDATE_LOGIN,
182 l_selv_rec.SEL_ID,
183 -- Added by Keerthi 15-Sep-2003
184 l_selv_rec.SOURCE_ID,
185 l_selv_rec.SOURCE_TABLE,
186 -- Added by rgooty: 4212626
187 l_selv_rec.BILL_ADJ_FLAG,
188 l_selv_rec.ACCRUAL_ADJ_FLAG,
189 -- Added by hkpatel for bug 4350255
190 l_selv_rec.DATE_DISBURSED;
191 x_no_data_found := okl_selv_pk_csr%NOTFOUND;
192 CLOSE okl_selv_pk_csr;
193 RETURN(l_selv_rec);
194 END get_rec;
195
196 FUNCTION get_rec (
197 p_selv_rec IN selv_rec_type
198 ) RETURN selv_rec_type IS
199 l_row_notfound BOOLEAN := TRUE;
200 BEGIN
201 RETURN(get_rec(p_selv_rec, l_row_notfound));
202 END get_rec;
203
204 ---------------------------------------------------------
205 -- FUNCTION null_out_defaults for: OKL_STRM_ELEMENTS_V --
206 ---------------------------------------------------------
207 FUNCTION null_out_defaults (
208 p_selv_rec IN selv_rec_type
209 ) RETURN selv_rec_type IS
210 l_selv_rec selv_rec_type := p_selv_rec;
211 BEGIN
212 IF (l_selv_rec.object_version_number = Okc_Api.G_MISS_NUM) THEN
213 l_selv_rec.object_version_number := NULL;
214 END IF;
215 IF (l_selv_rec.stm_id = Okc_Api.G_MISS_NUM) THEN
216 l_selv_rec.stm_id := NULL;
217 END IF;
218 IF (l_selv_rec.amount = Okc_Api.G_MISS_NUM) THEN
219 l_selv_rec.amount := NULL;
220 END IF;
221 IF (l_selv_rec.comments = Okc_Api.G_MISS_CHAR) THEN
222 l_selv_rec.comments := NULL;
223 END IF;
224 IF (l_selv_rec.accrued_yn = Okc_Api.G_MISS_CHAR) THEN
225 l_selv_rec.accrued_yn := NULL;
226 END IF;
227 IF (l_selv_rec.stream_element_date = Okc_Api.G_MISS_DATE) THEN
228 l_selv_rec.stream_element_date := NULL;
229 END IF;
230
231
232 -- as per the standards - the concurrent program columns should not be nulled out
233 /*
234 IF (l_selv_rec.program_id = OKC_API.G_MISS_NUM) THEN
235 l_selv_rec.program_id := NULL;
236 END IF;
237 IF (l_selv_rec.request_id = OKC_API.G_MISS_NUM) THEN
238 l_selv_rec.request_id := NULL;
239 END IF;
240 IF (l_selv_rec.program_application_id = OKC_API.G_MISS_NUM) THEN
241 l_selv_rec.program_application_id := NULL;
242 END IF;
243 IF (l_selv_rec.program_update_date = OKC_API.G_MISS_DATE) THEN
244 l_selv_rec.program_update_date := NULL;
245 END IF;
246
247 */
248 IF (l_selv_rec.se_line_number = Okc_Api.G_MISS_NUM) THEN
249 l_selv_rec.se_line_number := NULL;
250 END IF;
251
252 IF (l_selv_rec.date_billed = Okc_Api.G_MISS_DATE) THEN
253 l_selv_rec.date_billed := NULL;
254 END IF;
255
256 IF (l_selv_rec.created_by = Okc_Api.G_MISS_NUM) THEN
257 l_selv_rec.created_by := NULL;
258 END IF;
259 IF (l_selv_rec.creation_date = Okc_Api.G_MISS_DATE) THEN
260 l_selv_rec.creation_date := NULL;
261 END IF;
262 IF (l_selv_rec.last_updated_by = Okc_Api.G_MISS_NUM) THEN
263 l_selv_rec.last_updated_by := NULL;
264 END IF;
265 IF (l_selv_rec.last_update_date = Okc_Api.G_MISS_DATE) THEN
266 l_selv_rec.last_update_date := NULL;
267 END IF;
268 IF (l_selv_rec.last_update_login = Okc_Api.G_MISS_NUM) THEN
269 l_selv_rec.last_update_login := NULL;
270 END IF;
271 IF (l_selv_rec.sel_id = Okc_Api.G_MISS_NUM) THEN
272 l_selv_rec.sel_id := NULL;
273 END IF;
274 -- Added by Keerthi 15-Sep-2003
275 IF (l_selv_rec.source_id = Okc_Api.G_MISS_NUM) THEN
276 l_selv_rec.source_id := NULL;
277 END IF;
278 IF (l_selv_rec.source_table = Okc_Api.G_MISS_CHAR) THEN
279 l_selv_rec.source_table := NULL;
280 END IF;
281 -- Added by rgooty: 4212626
282 IF (l_selv_rec.bill_adj_flag = Okc_Api.G_MISS_CHAR) THEN
283 l_selv_rec.bill_adj_flag := NULL;
284 END IF;
285 IF (l_selv_rec.accrual_adj_flag = Okc_Api.G_MISS_CHAR) THEN
286 l_selv_rec.accrual_adj_flag := NULL;
287 END IF;
288 -- Added by hkpatel for bug 4350255
289 IF (l_selv_rec.date_disbursed = Okc_Api.G_MISS_DATE) THEN
290 l_selv_rec.date_disbursed := NULL;
291 END IF;
292
293
294 RETURN(l_selv_rec);
295 END null_out_defaults;
296
297
298
299
300 -- START change : akjain , 05/07/2001
301 -- TAPI CODE COMMENTED OUT IN FAVOUR OF WRITING SEPARATE PROCEDURES FOR EACH ATTRIBUTE/COLUMN
302 /*
303 ---------------------------------------------------------------------------
304 -- PROCEDURE Validate_Attributes
305 ---------------------------------------------------------------------------
306 -------------------------------------------------
307 -- Validate_Attributes for:OKL_STRM_ELEMENTS_V --
308 -------------------------------------------------
309 FUNCTION Validate_Attributes (
310 p_selv_rec IN selv_rec_type
311 ) RETURN VARCHAR2 IS
312 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
313 BEGIN
314 IF p_selv_rec.id = OKC_API.G_MISS_NUM OR
315 p_selv_rec.id IS NULL
316 THEN
317 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
318 l_return_status := OKC_API.G_RET_STS_ERROR;
319 ELSIF p_selv_rec.object_version_number = OKC_API.G_MISS_NUM OR
320 p_selv_rec.object_version_number IS NULL
321 THEN
322 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
323 l_return_status := OKC_API.G_RET_STS_ERROR;
324 ELSIF p_selv_rec.stm_id = OKC_API.G_MISS_NUM OR
325 p_selv_rec.stm_id IS NULL
326 THEN
327 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'stm_id');
328 l_return_status := OKC_API.G_RET_STS_ERROR;
329 ELSIF p_selv_rec.amount = OKC_API.G_MISS_NUM OR
330 p_selv_rec.amount IS NULL
331 THEN
332 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'amount');
333 l_return_status := OKC_API.G_RET_STS_ERROR;
334 END IF;
335 RETURN(l_return_status);
336 END Validate_Attributes;
337
338
339 ---------------------------------------------------------------------------
340 -- PROCEDURE Validate_Record
341 ---------------------------------------------------------------------------
342 ---------------------------------------
343 -- Validate_Record for:OKL_STRM_ELEMENTS_V --
344 ---------------------------------------
345 FUNCTION Validate_Record (
346 p_selv_rec IN selv_rec_type
347 ) RETURN VARCHAR2 IS
348 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
349 BEGIN
350 RETURN (l_return_status);
351 END Validate_Record;
352 */
353
354 --------------------------------------------------------------------------
355 -- PROCEDURE Validate_Id
356 --------------------------------------------------------------------------
357 -- Start of comments
358 --
359 -- Procedure Name : Validate_Id
360 -- Description :
361 -- Business Rules :
362 -- Parameters :
363 -- Version : 1.0
364 -- End of comments
365 ---------------------------------------------------------------------------
366
367 PROCEDURE Validate_Id(p_selv_rec IN selv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
368
369 IS
370
371 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
372
373 BEGIN
374 -- initialize return status
375 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
376 -- check for data before processing
377 IF (p_selv_rec.id IS NULL) THEN
378
379 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
380 ,p_msg_name => g_required_value
381 ,p_token1 => g_col_name_token
382 ,p_token1_value => 'id');
383 x_return_status := Okc_Api.G_RET_STS_ERROR;
384 RAISE G_EXCEPTION_HALT_VALIDATION;
385 END IF;
386
387 EXCEPTION
388 WHEN G_EXCEPTION_HALT_VALIDATION THEN
389 -- no processing necessary; validation can continue
390 -- with the next column
391 NULL;
392
393 WHEN OTHERS THEN
394 -- store SQL error message on message stack for caller
395 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
396 p_msg_name => g_unexpected_error,
397 p_token1 => g_sqlcode_token,
398 p_token1_value => SQLCODE,
399 p_token2 => g_sqlerrm_token,
400 p_token2_value => SQLERRM);
401
402 -- notify caller of an UNEXPECTED error
403 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
404
405 END Validate_Id;
406 /*
407 This validation is not required.
408 ---------------------------------------------------------------------------
409 -- PROCEDURE Validate_Unique_Sel_Record
410 ---------------------------------------------------------------------------
411 -- Start of comments
412 --
413 -- Procedure Name : Validate_Unique_Sel_Record
414 -- Description :
415 -- Business Rules : The combination of Se_Line_Number and Stm_id should be unique.
416 -- Parameters :
417 -- Version : 1.0
418 -- End of comments
419 ---------------------------------------------------------------------------
420 PROCEDURE Validate_Unique_Sel_Record(p_selv_rec IN selv_rec_type
421 ,x_return_status OUT NOCOPY VARCHAR2)
422 IS
423
424 l_dummy VARCHAR2(1) := '?';
425 l_row_found BOOLEAN := FALSE;
426
427 -- Cursor for FOD Unique Key
428 CURSOR okl_sel_uk_csr(p_rec selv_rec_type) IS
429 SELECT '1'
430 FROM OKL_STRM_ELEMENTS_V
431 WHERE stm_id = p_rec.stm_id
432 AND stream_element_date = p_rec.stream_element_date
433 AND id <> NVL(p_rec.id,-9999);
434
435 BEGIN
436
437 -- initialize return status
438 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
439 OPEN okl_sel_uk_csr(p_selv_rec);
440 FETCH okl_sel_uk_csr INTO l_dummy;
441 l_row_found := okl_sel_uk_csr%FOUND;
442 CLOSE okl_sel_uk_csr;
443 IF l_row_found THEN
444 Okc_Api.set_message(G_APP_NAME,G_UNQS);
445 x_return_status := Okc_Api.G_RET_STS_ERROR;
446 END IF;
447 EXCEPTION
448 WHEN G_EXCEPTION_HALT_VALIDATION THEN
449 -- no processing necessary; validation can continue
450 -- with the next column
451 NULL;
452
453 WHEN OTHERS THEN
454 -- store SQL error message on message stack for caller
455 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
456 p_msg_name => g_unexpected_error,
457 p_token1 => g_sqlcode_token,
458 p_token1_value => SQLCODE,
459 p_token2 => g_sqlerrm_token,
460 p_token2_value => SQLERRM);
461
462 -- notify caller of an UNEXPECTED error
463 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
464 IF okl_sel_uk_csr%ISOPEN
465 THEN
466 CLOSE okl_sel_uk_csr;
467 END IF;
468
469 END Validate_Unique_Sel_Record;
470
471 */
472
473 ---------------------------------------------------------------------------
474 -- PROCEDURE Validate_Amount
475 ---------------------------------------------------------------------------
476 -- Start of comments
477 --
478 -- Procedure Name : Validate_Amount
479 -- Description :
480 -- Business Rules :Amount can not be null.
481 -- Parameters :
482 -- Version : 1.0
483 -- End of comments
484 ---------------------------------------------------------------------------
485
486 PROCEDURE Validate_Amount(p_selv_rec IN selv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
487
488 IS
489
490 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
491
492 BEGIN
493 -- initialize return status
494 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
495 -- check for data before processing
496 IF (p_selv_rec.amount IS NULL) THEN
497
498 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
499 ,p_msg_name => g_required_value
500 ,p_token1 => g_col_name_token
501 ,p_token1_value => 'amount');
502 x_return_status := Okc_Api.G_RET_STS_ERROR;
503 RAISE G_EXCEPTION_HALT_VALIDATION;
504 END IF;
505
506 EXCEPTION
507 WHEN G_EXCEPTION_HALT_VALIDATION THEN
508 -- no processing necessary; validation can continue
509 -- with the next column
510 NULL;
511
512 WHEN OTHERS THEN
513 -- store SQL error message on message stack for caller
514 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
515 p_msg_name => g_unexpected_error,
516 p_token1 => g_sqlcode_token,
517 p_token1_value => SQLCODE,
518 p_token2 => g_sqlerrm_token,
519 p_token2_value => SQLERRM);
520
521 -- notify caller of an UNEXPECTED error
522 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
523
524 END Validate_Amount;
525
526
527
528
529 --------------------------------------------------------------------------
530 -- PROCEDURE Validate_Stm_Id
531 ---------------------------------------------------------------------------
532 -- Start of comments
533 --
534 -- Procedure Name : Validate_Stm_Id
535 -- Description :
536 -- Business Rules :
537 -- Parameters :
538 -- Version : 1.0
539 -- End of comments
540 ---------------------------------------------------------------------------
541
542 PROCEDURE Validate_Stm_Id( p_selv_rec IN selv_rec_type
543 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
544
545 CURSOR l_stmid_csr IS
546 SELECT '1'
547 FROM OKL_STREAMS_V
548 WHERE ID=p_selv_rec.STM_ID;
549
550 l_dummy_stm_id VARCHAR2(1);
551
552 l_row_notfound BOOLEAN :=TRUE;
553 BEGIN
554 -- initialize return status
555 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
556
557 -- check for data before processing
558 IF (p_selv_rec.stm_id IS NULL) THEN
559 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
560 ,p_msg_name => g_required_value
561 ,p_token1 => g_col_name_token
562 ,p_token1_value => 'stm_id');
563 x_return_status := Okc_Api.G_RET_STS_ERROR;
564 RAISE G_EXCEPTION_HALT_VALIDATION;
565
566 ELSIF p_selv_rec.stm_id IS NOT NULL THEN
567 --Check if stm_id exists in the stream level table or not
568 OPEN l_stmid_csr;
569 FETCH l_stmid_csr INTO l_dummy_stm_id;
570 l_row_notfound :=l_stmid_csr%NOTFOUND;
571 CLOSE l_stmid_csr;
572
573 IF(l_row_notfound ) THEN
574 Okc_Api.set_message(g_app_name,g_no_parent_record,g_col_name_token,'stm_id',g_child_table_token,'okl_strm_elements_v',g_parent_table_token,'okl_stream_levels_v');
575
576 x_return_status := Okc_Api.G_RET_STS_ERROR;
577 -- raise the exception as there's no matching foreign key value
578
579 RAISE G_EXCEPTION_HALT_VALIDATION;
580 END IF;
581
582
583 END IF;
584 EXCEPTION
585 WHEN G_EXCEPTION_HALT_VALIDATION THEN
586 -- no processing necessary; validation can continue
587 -- with the next column
588 NULL;
589
590 WHEN OTHERS THEN
591 -- store SQL error message on message stack for caller
592 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
593 p_msg_name => g_unexpected_error,
594 p_token1 => g_sqlcode_token,
595 p_token1_value => SQLCODE,
596 p_token2 => g_sqlerrm_token,
597 p_token2_value => SQLERRM);
598 -- notify caller of an UNEXPECTED error
599 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
600
601 IF l_stmid_csr%ISOPEN
602 THEN
603 CLOSE l_stmid_csr;
604 END IF;
605 END Validate_stm_Id;
606
607
608
609
610 --------------------------------------------------------------------------
611 -- PROCEDURE Validate_Stream_Element_Date
612 ---------------------------------------------------------------------------
613 -- Start of comments
614 -- Procedure Name : Validate_Stream_Element_Date
615 -- Description :
616 -- Business Rules :
617 -- Parameters :
618 -- Version : 1.0
619 -- End of comments
620 ---------------------------------------------------------------------------
621
622 PROCEDURE Validate_Stream_Element_Date(p_selv_rec IN selv_rec_type,x_return_status OUT NOCOPY VARCHAR2) IS
623
624
625
626 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
627
628 BEGIN
629 -- initialize return status
630 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
631 -- check for data before processing
632
633
634 IF (p_selv_rec.stream_element_date) IS NULL THEN
635
636
637
638 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
639 ,p_msg_name => g_required_value
640 ,p_token1 => g_col_name_token
641 ,p_token1_value => 'stream_element_date');
642
643 x_return_status := Okc_Api.G_RET_STS_ERROR;
644 RAISE G_EXCEPTION_HALT_VALIDATION;
645 END IF;
646
647 EXCEPTION
648 WHEN G_EXCEPTION_HALT_VALIDATION THEN
649 -- no processing necessary; validation can continue
650 -- with the next column
651 NULL;
652
653 WHEN OTHERS THEN
654 -- store SQL error message on message stack for caller
655 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
656 p_msg_name => g_unexpected_error,
657 p_token1 => g_sqlcode_token,
658 p_token1_value => SQLCODE,
659 p_token2 => g_sqlerrm_token,
660 p_token2_value => SQLERRM);
661
662
663 -- notify caller of an UNEXPECTED error
664 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
665
666 END Validate_Stream_Element_Date;
667
668 ---------------------------------------------------------------------------
669 -- PROCEDURE Validate_Object_Version_Number
670 ---------------------------------------------------------------------------
671 -- Start of comments
672 --
673 -- Procedure Name : Validate_Object_Version_Number
674 -- Description :
675 -- Business Rules :
676 -- Parameters :
677 -- Version : 1.0
678 -- End of comments
679 ---------------------------------------------------------------------------
680
681 PROCEDURE Validate_Object_Version_Number(p_selv_rec IN selv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
682
683 IS
684
685 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
686
687 BEGIN
688 -- initialize return status
689 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
690 -- check for data before processing
691 IF (p_selv_rec.object_version_number IS NULL)THEN
692 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
693 ,p_msg_name => g_required_value
694 ,p_token1 => g_col_name_token
695 ,p_token1_value => 'object_version_number');
696 x_return_status := Okc_Api.G_RET_STS_ERROR;
697 RAISE G_EXCEPTION_HALT_VALIDATION;
698 END IF;
699
700 EXCEPTION
701 WHEN G_EXCEPTION_HALT_VALIDATION THEN
702 -- no processing necessary; validation can continue
703 -- with the next column
704 NULL;
705
706 WHEN OTHERS THEN
707 -- store SQL error message on message stack for caller
708 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
709 p_msg_name => g_unexpected_error,
710 p_token1 => g_sqlcode_token,
711 p_token1_value => SQLCODE,
712 p_token2 => g_sqlerrm_token,
713 p_token2_value => SQLERRM);
714
715 -- notify caller of an UNEXPECTED error
716 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
717
718 END Validate_Object_Version_Number;
719
720 --------------------------------------------------------------------------
721 -- PROCEDURE Validate_Se_Line_Number
722 --------------------------------------------------------------------------
723 -- Start of comments
724 --
725 -- Procedure Name : Validate_Se_Line_Number
726 -- Description :
727 -- Business Rules :
728 -- Parameters :
729 -- Version : 1.0
730 -- End of comments
731 ---------------------------------------------------------------------------
732
733 PROCEDURE Validate_Se_Line_Number(p_selv_rec IN selv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
734 IS
735 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
736 BEGIN
737 -- initialize return status
738 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
739 -- check for data before processing
740 IF (p_selv_rec.se_line_number IS NULL) THEN
741
742 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
743 ,p_msg_name => g_required_value
744 ,p_token1 => g_col_name_token
745 ,p_token1_value => 'se_line_number');
746 x_return_status := Okc_Api.G_RET_STS_ERROR;
747 RAISE G_EXCEPTION_HALT_VALIDATION;
748 END IF;
749
750 EXCEPTION
751 WHEN G_EXCEPTION_HALT_VALIDATION THEN
752 -- no processing necessary; validation can continue
753 -- with the next column
754 NULL;
755
756 WHEN OTHERS THEN
757 -- store SQL error message on message stack for caller
758 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
759 p_msg_name => g_unexpected_error,
760 p_token1 => g_sqlcode_token,
761 p_token1_value => SQLCODE,
762 p_token2 => g_sqlerrm_token,
763 p_token2_value => SQLERRM);
764
765 -- notify caller of an UNEXPECTED error
766 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
767
768 END Validate_Se_Line_Number;
769
770
771 ------------------------------------------------------------------------
772 -- PROCEDURE Validate_SEL_Id
773 ---------------------------------------------------------------------------
774 -- Start of comments
775 -- Author : Kanti
776 -- Procedure Name : Validate_SEL_Id
777 -- Description :
778 -- Business Rules :
779 -- Parameters :
780 -- Version : 1.0
781 -- End of comments
782 ---------------------------------------------------------------------------
783
784 PROCEDURE Validate_SEL_Id(p_selv_rec IN selv_rec_type,
785 x_return_status OUT NOCOPY VARCHAR2)
786
787 IS
788
789 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
790
791 CURSOR sel_csr (p_sel_id NUMBER)
792 IS
793 SELECT ID
794 FROM okl_strm_elements
795 WHERE id = p_sel_id;
796
797 l_sel_id NUMBER := NULL;
798
799 BEGIN
800
801 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
802
803 IF (p_selv_rec.sel_id IS NOT NULL) THEN
804
805 OPEN sel_csr(p_selv_rec.sel_id);
806 FETCH sel_csr INTO l_sel_id;
807 CLOSE sel_csr;
808
809 IF (l_sel_id IS NULL) THEN
810
811 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
812 ,p_msg_name => g_invalid_value
813 ,p_token1 => g_col_name_token
814 ,p_token1_value => 'sel_id');
815 x_return_status := Okc_Api.G_RET_STS_ERROR;
816 RAISE G_EXCEPTION_HALT_VALIDATION;
817 END IF;
818
819 END IF;
820
821 EXCEPTION
822 WHEN G_EXCEPTION_HALT_VALIDATION THEN
823 -- no processing necessary; validation can continue
824 -- with the next column
825 NULL;
826
827 WHEN OTHERS THEN
828 -- store SQL error message on message stack for caller
829 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
830 p_msg_name => g_unexpected_error,
831 p_token1 => g_sqlcode_token,
832 p_token1_value => SQLCODE,
833 p_token2 => g_sqlerrm_token,
834 p_token2_value => SQLERRM);
835
836 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
837
838 END Validate_sel_Id;
839
840
841 ---------------------------------------------------------------------------
842 -- PROCEDURE Validate_Source_Id_Table
843 ---------------------------------------------------------------------------
844 -- Start of comments
845 --
846 -- Procedure Name : Validate_Source_Id_table
847 -- Description :
848 -- Business Rules : If Source Id Exists then Source Table cannot be null.
849 -- Parameters :
850 -- Version : 1.0
851 -- End of comments
852 ---------------------------------------------------------------------------
853
854 PROCEDURE Validate_Source_Id_Table(p_selv_rec IN selv_rec_type,x_return_status OUT NOCOPY VARCHAR2)
855
856 IS
857
858 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
859 l_dummy VARCHAR2(1) := OKC_API.G_FALSE;
860
861 BEGIN
862 -- initialize return status
863 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
864 -- check for data before processing
865
866
867 IF (p_selv_rec.source_id IS NOT NULL) THEN
868
869 IF (p_selv_rec.source_table IS NULL) THEN
870 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
871 ,p_msg_name => g_invalid_value
872 ,p_token1 => g_col_name_token
873 ,p_token1_value => 'SOURCE_TABLE');
874 x_return_status := Okc_Api.G_RET_STS_ERROR;
875 RAISE G_EXCEPTION_HALT_VALIDATION;
876 ELSE
877 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_SEL_SOURCE',
878 p_lookup_code => p_selv_rec.source_table);
879
880 IF (l_dummy = Okc_Api.G_FALSE) THEN
881 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
882 ,p_msg_name => g_invalid_value
883 ,p_token1 => g_col_name_token
884 ,p_token1_value => 'SOURCE_TABLE');
885 x_return_status := Okc_Api.G_RET_STS_ERROR;
886 RAISE G_EXCEPTION_HALT_VALIDATION;
887 END IF;
888
889 END IF;
890
891 END IF;
892
893
894
895 EXCEPTION
896 WHEN G_EXCEPTION_HALT_VALIDATION THEN
897 -- no processing necessary; validation can continue
898 -- with the next column
899 NULL;
900
901 WHEN OTHERS THEN
902 -- store SQL error message on message stack for caller
903 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
904 p_msg_name => g_unexpected_error,
905 p_token1 => g_sqlcode_token,
906 p_token1_value => SQLCODE,
907 p_token2 => g_sqlerrm_token,
908 p_token2_value => SQLERRM);
909
910 -- notify caller of an UNEXPECTED error
911 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
912
913 END Validate_Source_Id_Table;
914
915
916
917
918
919 ---------------------------------------------------------------------------
920 -- FUNCTION Validate_Attributes
921 ---------------------------------------------------------------------------
922 -- Start of comments
923 --
924 -- Procedure Name : Validate_Attributes
925 -- Description :
926 -- Business Rules :
927 -- Parameters :
928 -- Version : 1.0
929 -- End of comments
930 ---------------------------------------------------------------------------
931 FUNCTION Validate_Attributes (
932 p_selv_rec IN selv_rec_type
933 ) RETURN VARCHAR2 IS
934
935 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
936 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
937 BEGIN
938
939 -- call each column-level validation
940
941
942 -- Validate_Id
943 Validate_Id(p_selv_rec, x_return_status);
944 -- store the highest degree of error
945 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
946 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
947 -- need to leave
948 l_return_status := x_return_status;
949 RAISE G_EXCEPTION_HALT_VALIDATION;
950 ELSE
951 -- record that there was an error
952 l_return_status := x_return_status;
953 END IF;
954 END IF;
955
956 -- Validate_Object_Version_Number
957 Validate_Object_Version_Number(p_selv_rec, x_return_status);
958 -- store the highest degree of error
959 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
960 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
961 -- need to leave
962 l_return_status := x_return_status;
963 RAISE G_EXCEPTION_HALT_VALIDATION;
964 ELSE
965 -- record that there was an error
966 l_return_status := x_return_status;
967 END IF;
968 END IF;
969
970 -- Validate_Amount
971 Validate_Amount(p_selv_rec, x_return_status);
972 -- store the highest degree of error
973 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
974 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
975 -- need to leave
976 l_return_status := x_return_status;
977 RAISE G_EXCEPTION_HALT_VALIDATION;
978 ELSE
979 -- record that there was an error
980 l_return_status := x_return_status;
981 END IF;
982 END IF;
983
984
985 -- Validate_Stm_Id
986 Validate_Stm_Id(p_selv_rec, x_return_status);
987
988 -- store the highest degree of error
989 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
990 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
991 -- need to leave
992 l_return_status := x_return_status;
993 RAISE G_EXCEPTION_HALT_VALIDATION;
994 ELSE
995 -- record that there was an error
996 l_return_status := x_return_status;
997 END IF;
998 END IF;
999
1000 -- Validate_Stream_Element_Date
1001 Validate_Stream_Element_Date(p_selv_rec, x_return_status);
1002 -- store the highest degree of error
1003 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1004 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1005 -- need to leave
1006 l_return_status := x_return_status;
1007 RAISE G_EXCEPTION_HALT_VALIDATION;
1008 ELSE
1009 -- record that there was an error
1010 l_return_status := x_return_status;
1011 END IF;
1012 END IF;
1013
1014 -- Validate_Se_Line_Number
1015 Validate_Se_Line_Number(p_selv_rec, x_return_status);
1016 -- store the highest degree of error
1017 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1018 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1019 -- need to leave
1020 l_return_status := x_return_status;
1021 RAISE G_EXCEPTION_HALT_VALIDATION;
1022 ELSE
1023 -- record that there was an error
1024 l_return_status := x_return_status;
1025 END IF;
1026 END IF;
1027
1028 -- Validate_sel_id
1029 Validate_Sel_ID(p_selv_rec, x_return_status);
1030 -- store the highest degree of error
1031 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1032 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1033 -- need to leave
1034 l_return_status := x_return_status;
1035 RAISE G_EXCEPTION_HALT_VALIDATION;
1036 ELSE
1037 -- record that there was an error
1038 l_return_status := x_return_status;
1039 END IF;
1040 END IF;
1041
1042 -- Validate source_id and Source_table (Added by Keerthi 15-Sep-2003)
1043 Validate_Source_Id_Table(p_selv_rec, x_return_status);
1044 -- store the highest degree of error
1045 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1046 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1047 -- need to leave
1048 l_return_status := x_return_status;
1049 RAISE G_EXCEPTION_HALT_VALIDATION;
1050 ELSE
1051 -- record that there was an error
1052 l_return_status := x_return_status;
1053 END IF;
1054 END IF;
1055
1056
1057
1058 RETURN(l_return_status);
1059 EXCEPTION
1060 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1061 -- just come out with return status
1062
1063 RETURN (l_return_status);
1064
1065 WHEN OTHERS THEN
1066 -- store SQL error message on message stack for caller
1067 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1068 p_msg_name => g_unexpected_error,
1069 p_token1 => g_sqlcode_token,
1070 p_token1_value => SQLCODE,
1071 p_token2 => g_sqlerrm_token,
1072 p_token2_value => SQLERRM);
1073 -- notify caller of an UNEXPECTED error
1074 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1075 RETURN(l_return_status);
1076
1077 END Validate_Attributes;
1078
1079 -- END CUSTOM CODE, akjain
1080
1081 -- mvasudev , 09/27/2001
1082 ---------------------------------------------------------------------------
1083 -- PROCEDURE Validate_Unique_Sel_Record
1084 ---------------------------------------------------------------------------
1085 -- Start of comments
1086 --
1087 -- Procedure Name : Validate_Unique_Sel_Record
1088 -- Description :
1089 -- Business Rules :
1090 -- Parameters :
1091 -- Version : 1.0
1092 -- End of comments
1093 ---------------------------------------------------------------------------
1094 PROCEDURE Validate_Unique_Sel_Record(p_selv_rec IN selv_rec_type
1095 ,x_return_status OUT NOCOPY VARCHAR2)
1096 IS
1097
1098 l_dummy VARCHAR2(1) := '?';
1099 l_row_found BOOLEAN := FALSE;
1100
1101 -- Cursor for sel Unique Key
1102 CURSOR okl_sel_uk_csr(p_rec selv_rec_type) IS
1103 SELECT '1'
1104 FROM okl_strm_elements_v
1105 WHERE stm_id = p_rec.stm_id
1106 AND se_line_number = p_rec.se_line_number
1107 AND id <> NVL(p_rec.id,-9999);
1108
1109 BEGIN
1110 -- initialize return status
1111 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1112 OPEN okl_sel_uk_csr(p_selv_rec);
1113 FETCH okl_sel_uk_csr INTO l_dummy;
1114 l_row_found := okl_sel_uk_csr%FOUND;
1115 CLOSE okl_sel_uk_csr;
1116 IF l_row_found THEN
1117 Okc_Api.set_message(G_APP_NAME,G_UNQS);
1118 x_return_status := Okc_Api.G_RET_STS_ERROR;
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1123 -- no processing necessary; validation can continue
1124 -- with the next column
1125 NULL;
1126
1127 WHEN OTHERS THEN
1128 -- store SQL error message on message stack for caller
1129 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1130 p_msg_name => g_unexpected_error,
1131 p_token1 => g_sqlcode_token,
1132 p_token1_value => SQLCODE,
1133 p_token2 => g_sqlerrm_token,
1134 p_token2_value => SQLERRM);
1135
1136 -- notify caller of an UNEXPECTED error
1137 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1138
1139 END Validate_Unique_Sel_Record;
1140
1141 ---------------------------------------------------------------------------
1142 -- PROCEDURE Validate_Record
1143 ---------------------------------------------------------------------------
1144 -- Start of comments
1145 --
1146 -- Procedure Name : Validate_Record
1147 -- Description :
1148 -- Business Rules :
1149 -- Parameters :
1150 -- Version : 1.0
1151 -- End of comments
1152 ---------------------------------------------------------------------------
1153
1154 FUNCTION Validate_Record (
1155 p_selv_rec IN selv_rec_type
1156 ) RETURN VARCHAR2 IS
1157 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1158 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1159 BEGIN
1160 -- Validate_Unique_sel_Record
1161 Validate_Unique_Sel_Record(p_selv_rec, x_return_status);
1162 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1163 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1164 -- need to leave
1165 l_return_status := x_return_status;
1166 RAISE G_EXCEPTION_HALT_VALIDATION;
1167 ELSE
1168 -- record that there was an error
1169 l_return_status := x_return_status;
1170 END IF;
1171 END IF;
1172
1173 RETURN(l_return_status);
1174 EXCEPTION
1175 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1176 -- exit with return status
1177 NULL;
1178 RETURN (l_return_status);
1179
1180 WHEN OTHERS THEN
1181 -- store SQL error message on message stack for caller
1182 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1183 p_msg_name => g_unexpected_error,
1184 p_token1 => g_sqlcode_token,
1185 p_token1_value => SQLCODE,
1186 p_token2 => g_sqlerrm_token,
1187 p_token2_value => SQLERRM);
1188 -- notify caller of an UNEXPECTED error
1189 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1190 RETURN(l_return_status);
1191 END Validate_Record;
1192 -- END change : mvasudev
1193
1194
1195 ---------------------------------------------------------------------------
1196 -- PROCEDURE Migrate
1197 ---------------------------------------------------------------------------
1198 PROCEDURE migrate (
1199 p_from IN selv_rec_type,
1200 p_to IN OUT NOCOPY sel_rec_type
1201 ) IS
1202 BEGIN
1203 p_to.id := p_from.id;
1204 p_to.stm_id := p_from.stm_id;
1205 p_to.object_version_number := p_from.object_version_number;
1206 p_to.stream_element_date := p_from.stream_element_date;
1207 p_to.amount := p_from.amount;
1208 p_to.comments := p_from.comments;
1209 p_to.accrued_yn := p_from.accrued_yn;
1210 p_to.program_id := p_from.program_id;
1211 p_to.request_id := p_from.request_id;
1212 p_to.program_application_id := p_from.program_application_id;
1213 p_to.program_update_date := p_from.program_update_date;
1214 p_to.se_line_number := p_from.se_line_number;
1215 p_to.date_billed := p_from.date_billed;
1216 p_to.created_by := p_from.created_by;
1217 p_to.creation_date := p_from.creation_date;
1218 p_to.last_updated_by := p_from.last_updated_by;
1219 p_to.last_update_date := p_from.last_update_date;
1220 p_to.last_update_login := p_from.last_update_login;
1221 p_to.sel_id := p_from.sel_id;
1222 -- Added by Keerthi 15-Sep-2003
1223 p_to.source_id := p_from.source_id;
1224 p_to.source_table := p_from.source_table;
1225 -- Added by rgooty: 4212626
1226 p_to.bill_adj_flag := p_from.bill_adj_flag;
1227 p_to.accrual_adj_flag := p_from.accrual_adj_flag;
1228 -- Added by hkpatel for bug 4350255
1229 p_to.date_disbursed := p_from.date_disbursed;
1230 END migrate;
1231 PROCEDURE migrate (
1232 p_from IN sel_rec_type,
1233 p_to IN OUT NOCOPY selv_rec_type
1234 ) IS
1235 BEGIN
1236 p_to.id := p_from.id;
1237 p_to.stm_id := p_from.stm_id;
1238 p_to.object_version_number := p_from.object_version_number;
1239 p_to.stream_element_date := p_from.stream_element_date;
1240 p_to.amount := p_from.amount;
1241 p_to.comments := p_from.comments;
1242 p_to.accrued_yn := p_from.accrued_yn;
1243 p_to.program_id := p_from.program_id;
1244 p_to.request_id := p_from.request_id;
1245 p_to.program_application_id := p_from.program_application_id;
1246 p_to.se_line_number := p_from.se_line_number;
1247 p_to.date_billed := p_from.date_billed;
1248 p_to.created_by := p_from.created_by;
1249 p_to.creation_date := p_from.creation_date;
1250 p_to.last_updated_by := p_from.last_updated_by;
1251 p_to.last_update_date := p_from.last_update_date;
1252 p_to.last_update_login := p_from.last_update_login;
1253 p_to.sel_id := p_from.sel_id;
1254 -- Added by Keerthi 15-Sep-2003
1255 p_to.source_id := p_from.source_id;
1256 p_to.source_table := p_from.source_table;
1257 -- Added by rgooty: 4212626
1258 p_to.bill_adj_flag := p_from.bill_adj_flag;
1259 p_to.accrual_adj_flag := p_from.accrual_adj_flag;
1260 -- Added by hkpatel for bug 4350255
1261 p_to.date_disbursed := p_from.date_disbursed;
1262
1263 END migrate;
1264
1265 ---------------------------------------------------------------------------
1266 -- PROCEDURE validate_row
1267 ---------------------------------------------------------------------------
1268 ------------------------------------------
1269 -- validate_row for:OKL_STRM_ELEMENTS_V --
1270 ------------------------------------------
1271 PROCEDURE validate_row(
1272 p_api_version IN NUMBER,
1273 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1274 x_return_status OUT NOCOPY VARCHAR2,
1275 x_msg_count OUT NOCOPY NUMBER,
1276 x_msg_data OUT NOCOPY VARCHAR2,
1277 p_selv_rec IN selv_rec_type) IS
1278
1279 l_api_version CONSTANT NUMBER := 1;
1280 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1281 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1282 l_selv_rec selv_rec_type := p_selv_rec;
1283 l_sel_rec sel_rec_type;
1284 BEGIN
1285 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
1286 G_PKG_NAME,
1287 p_init_msg_list,
1288 l_api_version,
1289 p_api_version,
1290 '_PVT',
1291 x_return_status);
1292 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1293 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1294 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1295 RAISE Okc_Api.G_EXCEPTION_ERROR;
1296 END IF;
1297 --- Validate all non-missing attributes (Item Level Validation)
1298 l_return_status := Validate_Attributes(l_selv_rec);
1299 --- If any errors happen abort API
1300 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1301 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1302 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1303 RAISE Okc_Api.G_EXCEPTION_ERROR;
1304 END IF;
1305 l_return_status := Validate_Record(l_selv_rec);
1306 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1307 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1308 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1309 RAISE Okc_Api.G_EXCEPTION_ERROR;
1310 END IF;
1311 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1312 EXCEPTION
1313 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1314 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1315 (
1316 l_api_name,
1317 G_PKG_NAME,
1318 'OKC_API.G_RET_STS_ERROR',
1319 x_msg_count,
1320 x_msg_data,
1321 '_PVT'
1322 );
1323 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1324 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1325 (
1326 l_api_name,
1327 G_PKG_NAME,
1328 'OKC_API.G_RET_STS_UNEXP_ERROR',
1329 x_msg_count,
1330 x_msg_data,
1331 '_PVT'
1332 );
1333 WHEN OTHERS THEN
1334 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1335 (
1336 l_api_name,
1337 G_PKG_NAME,
1338 'OTHERS',
1339 x_msg_count,
1340 x_msg_data,
1341 '_PVT'
1342 );
1343 END validate_row;
1344 ------------------------------------------
1345 -- PL/SQL TBL validate_row for:SELV_TBL --
1346 ------------------------------------------
1347 PROCEDURE validate_row(
1348 p_api_version IN NUMBER,
1349 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1350 x_return_status OUT NOCOPY VARCHAR2,
1351 x_msg_count OUT NOCOPY NUMBER,
1352 x_msg_data OUT NOCOPY VARCHAR2,
1353 p_selv_tbl IN selv_tbl_type) IS
1354
1355 l_api_version CONSTANT NUMBER := 1;
1356 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1357 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1358 i NUMBER := 0;
1359 -- START change : akjain, 05/15/2001
1360 -- Adding OverAll Status Flag
1361 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1362 -- END change : akjain
1363
1364 BEGIN
1365 Okc_Api.init_msg_list(p_init_msg_list);
1366 -- Make sure PL/SQL table has records in it before passing
1367 IF (p_selv_tbl.COUNT > 0) THEN
1368 i := p_selv_tbl.FIRST;
1369 LOOP
1370 validate_row (
1371 p_api_version => p_api_version,
1372 p_init_msg_list => Okc_Api.G_FALSE,
1373 x_return_status => x_return_status,
1374 x_msg_count => x_msg_count,
1375 x_msg_data => x_msg_data,
1376 p_selv_rec => p_selv_tbl(i));
1377 -- START change : akjain, 05/15/2001
1378 -- store the highest degree of error
1379 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1380 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1381 l_overall_status := x_return_status;
1382 END IF;
1383 END IF;
1384 -- END change : akjain
1385 EXIT WHEN (i = p_selv_tbl.LAST);
1386 i := p_selv_tbl.NEXT(i);
1387 END LOOP;
1388
1389 -- START change : akjain, 05/15/2001
1390 -- return overall status
1391 x_return_status := l_overall_status;
1392 -- END change : akjain
1393
1394 END IF;
1395 EXCEPTION
1396 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1397 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1398 (
1399 l_api_name,
1400 G_PKG_NAME,
1401 'OKC_API.G_RET_STS_ERROR',
1402 x_msg_count,
1403 x_msg_data,
1404 '_PVT'
1405 );
1406 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1407 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1408 (
1409 l_api_name,
1410 G_PKG_NAME,
1411 'OKC_API.G_RET_STS_UNEXP_ERROR',
1412 x_msg_count,
1413 x_msg_data,
1414 '_PVT'
1415 );
1416 WHEN OTHERS THEN
1417 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1418 (
1419 l_api_name,
1420 G_PKG_NAME,
1421 'OTHERS',
1422 x_msg_count,
1423 x_msg_data,
1424 '_PVT'
1425 );
1426 END validate_row;
1427
1428 ---------------------------------------------------------------------------
1429 -- PROCEDURE insert_row
1430 ---------------------------------------------------------------------------
1431 --------------------------------------
1432 -- insert_row for:OKL_STRM_ELEMENTS --
1433 --------------------------------------
1434 PROCEDURE insert_row(
1435 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1436 x_return_status OUT NOCOPY VARCHAR2,
1437 x_msg_count OUT NOCOPY NUMBER,
1438 x_msg_data OUT NOCOPY VARCHAR2,
1439 p_sel_rec IN sel_rec_type,
1440 x_sel_rec OUT NOCOPY sel_rec_type) IS
1441
1442 l_api_version CONSTANT NUMBER := 1;
1443 l_api_name CONSTANT VARCHAR2(30) := 'ELEMENTS_insert_row';
1444 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1445 l_sel_rec sel_rec_type := p_sel_rec;
1446 l_def_sel_rec sel_rec_type;
1447 ------------------------------------------
1448 -- Set_Attributes for:OKL_STRM_ELEMENTS --
1449 ------------------------------------------
1450 FUNCTION Set_Attributes (
1451 p_sel_rec IN sel_rec_type,
1452 x_sel_rec OUT NOCOPY sel_rec_type
1453 ) RETURN VARCHAR2 IS
1454 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1455 BEGIN
1456 x_sel_rec := p_sel_rec;
1457 RETURN(l_return_status);
1458 END Set_Attributes;
1459 BEGIN
1460 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
1461 p_init_msg_list,
1462 '_PVT',
1463 x_return_status);
1464 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1465 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1466 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1467 RAISE Okc_Api.G_EXCEPTION_ERROR;
1468 END IF;
1469 --- Setting item attributes
1470 l_return_status := Set_Attributes(
1471 p_sel_rec, -- IN
1472 l_sel_rec); -- OUT
1473 --- If any errors happen abort API
1474 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1475 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1476 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1477 RAISE Okc_Api.G_EXCEPTION_ERROR;
1478 END IF;
1479
1480
1481 INSERT INTO OKL_STRM_ELEMENTS(
1482 id,
1483 stm_id,
1484 object_version_number,
1485 stream_element_date,
1486 amount,
1487 comments,
1488 accrued_yn,
1489 program_id,
1490 request_id,
1491 program_application_id,
1492 program_update_date,
1493 se_line_number,
1494 date_billed,
1495 created_by,
1496 creation_date,
1497 last_updated_by,
1498 last_update_date,
1499 last_update_login,
1500 sel_id,
1501 -- Added by Keerthi 15-Sep-2003
1502 source_id,
1503 source_table,
1504 bill_adj_flag,
1505 accrual_adj_flag,
1506 -- Added by hkpatel for bug 4350255
1507 date_disbursed)
1508 VALUES (
1509 l_sel_rec.id,
1510 l_sel_rec.stm_id,
1511 l_sel_rec.object_version_number,
1512 l_sel_rec.stream_element_date,
1513 l_sel_rec.amount,
1514 l_sel_rec.comments,
1515 l_sel_rec.accrued_yn,
1516 l_sel_rec.program_id,
1517 l_sel_rec.request_id,
1518 l_sel_rec.program_application_id,
1519 l_sel_rec.program_update_date,
1520 l_sel_rec.se_line_number,
1521 l_sel_rec.date_billed,
1522 l_sel_rec.created_by,
1523 l_sel_rec.creation_date,
1524 l_sel_rec.last_updated_by,
1525 l_sel_rec.last_update_date,
1526 l_sel_rec.last_update_login,
1527 l_sel_rec.sel_id,
1528 -- Added by Keerthi 15-Sep-2003
1529 l_sel_rec.source_id,
1530 l_sel_rec.source_table,
1531 l_sel_rec.bill_adj_flag,
1532 l_sel_rec.accrual_adj_flag,
1533 -- Added by hkpatel for bug 4350255
1534 l_sel_rec.date_disbursed);
1535
1536 -- Set OUT values
1537 x_sel_rec := l_sel_rec;
1538 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1539 EXCEPTION
1540 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1541 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1542 (
1543 l_api_name,
1544 G_PKG_NAME,
1545 'OKC_API.G_RET_STS_ERROR',
1546 x_msg_count,
1547 x_msg_data,
1548 '_PVT'
1549 );
1550 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1551 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1552 (
1553 l_api_name,
1554 G_PKG_NAME,
1555 'OKC_API.G_RET_STS_UNEXP_ERROR',
1556 x_msg_count,
1557 x_msg_data,
1558 '_PVT'
1559 );
1560 WHEN OTHERS THEN
1561 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1562 (
1563 l_api_name,
1564 G_PKG_NAME,
1565 'OTHERS',
1566 x_msg_count,
1567 x_msg_data,
1568 '_PVT'
1569 );
1570 END insert_row;
1571 ----------------------------------------
1572 -- insert_row for:OKL_STRM_ELEMENTS_V --
1573 ----------------------------------------
1574 PROCEDURE insert_row(
1575 p_api_version IN NUMBER,
1576 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1577 x_return_status OUT NOCOPY VARCHAR2,
1578 x_msg_count OUT NOCOPY NUMBER,
1579 x_msg_data OUT NOCOPY VARCHAR2,
1580 p_selv_rec IN selv_rec_type,
1581 x_selv_rec OUT NOCOPY selv_rec_type) IS
1582
1583 l_api_version CONSTANT NUMBER := 1;
1584 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1585 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1586 l_selv_rec selv_rec_type;
1587 l_def_selv_rec selv_rec_type;
1588 l_sel_rec sel_rec_type;
1589 lx_sel_rec sel_rec_type;
1590 -------------------------------
1591 -- FUNCTION fill_who_columns --
1592 -------------------------------
1593 FUNCTION fill_who_columns (
1594 p_selv_rec IN selv_rec_type
1595 ) RETURN selv_rec_type IS
1596 l_selv_rec selv_rec_type := p_selv_rec;
1597 BEGIN
1598 l_selv_rec.CREATION_DATE := SYSDATE;
1599 l_selv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1600 l_selv_rec.LAST_UPDATE_DATE := SYSDATE;
1601 l_selv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1602 l_selv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1603 RETURN(l_selv_rec);
1604 END fill_who_columns;
1605 --------------------------------------------
1606 -- Set_Attributes for:OKL_STRM_ELEMENTS_V --
1607 --------------------------------------------
1608 FUNCTION Set_Attributes (
1609 p_selv_rec IN selv_rec_type,
1610 x_selv_rec OUT NOCOPY selv_rec_type
1611 ) RETURN VARCHAR2 IS
1612 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1613 BEGIN
1614 x_selv_rec := p_selv_rec;
1615 x_selv_rec.OBJECT_VERSION_NUMBER := 1;
1616
1617 -- fill Concurrent program columns
1618 /*** Concurrent Manager columns assignement ************/
1619
1620 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID),
1621 DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID),
1622 DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID),
1623 DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
1624 INTO x_selv_rec.REQUEST_ID
1625 ,x_selv_rec.PROGRAM_APPLICATION_ID
1626 ,x_selv_rec.PROGRAM_ID
1627 ,x_selv_rec.PROGRAM_UPDATE_DATE
1628 FROM DUAL;
1629 /***** END Concurrent Manager columns assignement ************/
1630
1631 RETURN(l_return_status);
1632 END Set_Attributes;
1633 BEGIN
1634 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
1635 G_PKG_NAME,
1636 p_init_msg_list,
1637 l_api_version,
1638 p_api_version,
1639 '_PVT',
1640 x_return_status);
1641 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1642 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1643 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1644 RAISE Okc_Api.G_EXCEPTION_ERROR;
1645 END IF;
1646 l_selv_rec := null_out_defaults(p_selv_rec);
1647 -- Set primary key value
1648 l_selv_rec.ID := get_seq_id;
1649 --- Setting item attributes
1650 l_return_status := Set_Attributes(
1651 l_selv_rec, -- IN
1652 l_def_selv_rec); -- OUT
1653 --- If any errors happen abort API
1654 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1655 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1656 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1657 RAISE Okc_Api.G_EXCEPTION_ERROR;
1658 END IF;
1659 l_def_selv_rec := fill_who_columns(l_def_selv_rec);
1660 --- Validate all non-missing attributes (Item Level Validation)
1661 l_return_status := Validate_Attributes(l_def_selv_rec);
1662 --- If any errors happen abort API
1663 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1664 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1665 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1666 RAISE Okc_Api.G_EXCEPTION_ERROR;
1667 END IF;
1668
1669 l_return_status := Validate_Record(l_def_selv_rec);
1670 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1671 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1672 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1673 RAISE Okc_Api.G_EXCEPTION_ERROR;
1674 END IF;
1675 --------------------------------------
1676 -- Move VIEW record to "Child" records
1677 --------------------------------------
1678 migrate(l_def_selv_rec, l_sel_rec);
1679 --------------------------------------------
1680 -- Call the INSERT_ROW for each child record
1681 --------------------------------------------
1682 insert_row(
1683 p_init_msg_list,
1684 x_return_status,
1685 x_msg_count,
1686 x_msg_data,
1687 l_sel_rec,
1688 lx_sel_rec
1689 );
1690 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1691 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1692 ELSIF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1693 RAISE Okc_Api.G_EXCEPTION_ERROR;
1694 END IF;
1695 migrate(lx_sel_rec, l_def_selv_rec);
1696 -- Set OUT values
1697 x_selv_rec := l_def_selv_rec;
1698 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1699 EXCEPTION
1700 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1701 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1702 (
1703 l_api_name,
1704 G_PKG_NAME,
1705 'OKC_API.G_RET_STS_ERROR',
1706 x_msg_count,
1707 x_msg_data,
1708 '_PVT'
1709 );
1710 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1711 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1712 (
1713 l_api_name,
1714 G_PKG_NAME,
1715 'OKC_API.G_RET_STS_UNEXP_ERROR',
1716 x_msg_count,
1717 x_msg_data,
1718 '_PVT'
1719 );
1720 WHEN OTHERS THEN
1721 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1722 (
1723 l_api_name,
1724 G_PKG_NAME,
1725 'OTHERS',
1726 x_msg_count,
1727 x_msg_data,
1728 '_PVT'
1729 );
1730 END insert_row;
1731 ----------------------------------------
1732 -- PL/SQL TBL insert_row for:SELV_TBL --
1733 ----------------------------------------
1734 PROCEDURE insert_row(
1735 p_api_version IN NUMBER,
1736 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1737 x_return_status OUT NOCOPY VARCHAR2,
1738 x_msg_count OUT NOCOPY NUMBER,
1739 x_msg_data OUT NOCOPY VARCHAR2,
1740 p_selv_tbl IN selv_tbl_type,
1741 x_selv_tbl OUT NOCOPY selv_tbl_type) IS
1742
1743 l_api_version CONSTANT NUMBER := 1;
1744 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1745 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1746
1747 -- START change : akjain, 05/15/2001
1748 -- Adding OverAll Status Flag
1749 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1750 -- END change : akjain
1751
1752 in_id Okl_Streams_Util.NumberTabTyp;
1753 in_stm_id Okl_Streams_Util.NumberTabTyp;
1754 in_stream_element_date Okl_Streams_Util.DateTabTyp;
1755 in_object_version_number Okl_Streams_Util.NumberTabTyp;
1756 in_amount Okl_Streams_Util.NumberTabTyp;
1757 in_comments Okl_Streams_Util.Var450TabTyp;
1758 in_accrued_yn Okl_Streams_Util.Var150TabTyp;
1759 in_program_id Okl_Streams_Util.NumberTabTyp;
1760 in_request_id Okl_Streams_Util.NumberTabTyp;
1761 in_program_application_id Okl_Streams_Util.NumberTabTyp;
1762 in_program_update_date Okl_Streams_Util.DateTabTyp;
1763 in_se_line_number Okl_Streams_Util.Var150TabTyp;
1764 in_date_billed Okl_Streams_Util.DateTabTyp;
1765
1766 in_created_by Okl_Streams_Util.NumberTabTyp;
1767 in_last_updated_by Okl_Streams_Util.NumberTabTyp;
1768 in_creation_date Okl_Streams_Util.DateTabTyp;
1769 in_last_update_date Okl_Streams_Util.DateTabTyp;
1770 in_last_update_login Okl_Streams_Util.NumberTabTyp;
1771
1772 in_sel_id Okl_Streams_Util.NumberTabTyp;
1773 in_source_id Okl_Streams_Util.NumberTabTyp;
1774 in_source_table Okl_Streams_Util.Var150TabTyp;
1775 -- Added by rgooty: 4212626
1776 in_bill_adj_flag Okl_Streams_Util.Var150TabTyp;
1777 in_accrual_adj_flag Okl_Streams_Util.Var150TabTyp;
1778 -- Added by hkpatel for bug 4350255
1779 in_date_disbursed Okl_Streams_Util.DateTabTyp;
1780 l_tabsize NUMBER := p_selv_tbl.COUNT;
1781 i number;
1782 j number;
1783
1784
1785 BEGIN
1786
1787 Okc_Api.init_msg_list(p_init_msg_list);
1788 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
1789 p_init_msg_list,
1790 '_PVT',
1791 x_return_status);
1792 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1793 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1794 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1795 RAISE Okc_Api.G_EXCEPTION_ERROR;
1796 END IF;
1797
1798 --prasjain :start for bug 5474827
1799 x_selv_tbl := p_selv_tbl;
1800 --prasjain:End
1801
1802 --Changed to bulk insert as a part of performance bug fix 3866049
1803 i := p_selv_tbl.FIRST; j:=0;
1804 WHILE i is not null LOOP
1805 j:=j+1;
1806 in_id(j) := get_seq_id;
1807
1808 --prasjain:Start for bug 5474827
1809 x_selv_tbl(i).id := in_id(j);
1810 --prasjain:End
1811
1812
1813 in_stm_id(j) := p_selv_tbl(i).stm_id;
1814 in_object_version_number(j) := p_selv_tbl(i).object_version_number;
1815 in_stream_element_date(j) := p_selv_tbl(i).stream_element_date;
1816 in_amount(j) := p_selv_tbl(i).amount;
1817 in_comments(j) := p_selv_tbl(i).comments;
1818 in_accrued_yn(j) := p_selv_tbl(i).accrued_yn;
1819 in_program_id(j) := p_selv_tbl(i).program_id;
1820 in_request_id(j) := p_selv_tbl(i).request_id;
1821 in_program_application_id(j) := p_selv_tbl(i).program_application_id;
1822 in_program_update_date(j) := p_selv_tbl(i).program_update_date;
1823 in_se_line_number(j) := p_selv_tbl(i).se_line_number;
1824 in_date_billed(j) := p_selv_tbl(i).date_billed;
1825 in_sel_id(j) := p_selv_tbl(i).sel_id;
1826 in_source_id(j) := p_selv_tbl(i).source_id;
1827 in_source_table(j) := p_selv_tbl(i).source_table;
1828 -- Added by rgooty: 4212626
1829 in_bill_adj_flag(j) := p_selv_tbl(i).bill_adj_flag;
1830 in_accrual_adj_flag(j) := p_selv_tbl(i).accrual_adj_flag;
1831 -- 4212626: End
1832 -- Added by hkpatel for bug 4350255
1833 in_date_disbursed(j) := p_selv_tbl(i).date_disbursed;
1834 in_CREATION_DATE(j) := SYSDATE;
1835 in_cREATED_BY(j) := FND_GLOBAL.USER_ID;
1836 in_LAST_UPDATE_DATE(j) := SYSDATE;
1837 in_LAST_UPDATED_BY(j) := FND_GLOBAL.USER_ID;
1838 in_LAST_UPDATE_LOGIN(j) := FND_GLOBAL.LOGIN_ID;
1839 in_object_version_number(j) := 1.0;
1840
1841
1842 --prasjain:Start for bug 5474827
1843 --i:= p_selv_tbl.next(j);
1844 i:= p_selv_tbl.next(i);
1845 --prasjain:end
1846
1847
1848 END LOOP;
1849
1850 FORALL i in 1..l_tabsize
1851
1852 INSERT INTO OKL_STRM_ELEMENTS(
1853 id,
1854 stm_id,
1855 object_version_number,
1856 stream_element_date,
1857 amount,
1858 comments,
1859 accrued_yn,
1860 program_id,
1861 request_id,
1862 program_application_id,
1863 program_update_date,
1864 se_line_number,
1865 date_billed,
1866 created_by,
1867 creation_date,
1868 last_updated_by,
1869 last_update_date,
1870 last_update_login,
1871 sel_id,
1872 source_id,
1873 source_table,
1874 -- Added by rgooty: 4212626
1875 bill_adj_flag,
1876 accrual_adj_flag,
1877 -- Added by hkpatel for bug 4350255
1878 date_disbursed)
1879 VALUES
1880 (in_id(i),
1881 in_stm_id(i),
1882 in_object_version_number(i),
1883 in_stream_element_date(i),
1884 in_amount(i),
1885 in_comments(i),
1886 in_accrued_yn(i),
1887 in_program_id(i),
1888 in_request_id(i),
1889 in_program_application_id(i),
1890 in_program_update_date(i),
1891 in_se_line_number(i),
1892 in_date_billed(i),
1893 in_created_by(i),
1894 in_creation_date(i),
1895 in_last_updated_by(i),
1896 in_last_update_date(i),
1897 in_last_update_login(i),
1898 in_sel_id(i),
1899 in_source_id(i),
1900 in_source_table(i),
1901 -- Added by rgooty: 4212626
1902 in_bill_adj_flag(i),
1903 in_accrual_adj_flag(i),
1904 in_date_disbursed(i));
1905
1906 --prasjain: Start for bug 5474827
1907 --x_selv_tbl := p_selv_tbl;
1908 --prasjain:End
1909
1910
1911 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1912 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
1913
1914 EXCEPTION
1915 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
1916 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
1917 (
1918 l_api_name,
1919 G_PKG_NAME,
1920 'OKC_API.G_RET_STS_ERROR',
1921 x_msg_count,
1922 x_msg_data,
1923 '_PVT'
1924 );
1925 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1926 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1927 (
1928 l_api_name,
1929 G_PKG_NAME,
1930 'OKC_API.G_RET_STS_UNEXP_ERROR',
1931 x_msg_count,
1932 x_msg_data,
1933 '_PVT'
1934 );
1935 WHEN OTHERS THEN
1936 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
1937 (
1938 l_api_name,
1939 G_PKG_NAME,
1940 'OTHERS',
1941 x_msg_count,
1942 x_msg_data,
1943 '_PVT'
1944 );
1945 END insert_row;
1946
1947 ---------------------------------------------------------------------------
1948 -- PROCEDURE lock_row
1949 ---------------------------------------------------------------------------
1950 ------------------------------------
1951 -- lock_row for:OKL_STRM_ELEMENTS --
1952 ------------------------------------
1953 PROCEDURE lock_row(
1954 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
1955 x_return_status OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER,
1957 x_msg_data OUT NOCOPY VARCHAR2,
1958 p_sel_rec IN sel_rec_type) IS
1959
1960 E_Resource_Busy EXCEPTION;
1961 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1962 CURSOR lock_csr (p_sel_rec IN sel_rec_type) IS
1963 SELECT OBJECT_VERSION_NUMBER
1964 FROM OKL_STRM_ELEMENTS
1965 WHERE ID = p_sel_rec.id
1966 AND OBJECT_VERSION_NUMBER = p_sel_rec.object_version_number
1967 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1968
1969 CURSOR lchk_csr (p_sel_rec IN sel_rec_type) IS
1970 SELECT OBJECT_VERSION_NUMBER
1971 FROM OKL_STRM_ELEMENTS
1972 WHERE ID = p_sel_rec.id;
1973 l_api_version CONSTANT NUMBER := 1;
1974 l_api_name CONSTANT VARCHAR2(30) := 'ELEMENTS_lock_row';
1975 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1976 l_object_version_number OKL_STRM_ELEMENTS.OBJECT_VERSION_NUMBER%TYPE;
1977 lc_object_version_number OKL_STRM_ELEMENTS.OBJECT_VERSION_NUMBER%TYPE;
1978 l_row_notfound BOOLEAN := FALSE;
1979 lc_row_notfound BOOLEAN := FALSE;
1980 BEGIN
1981 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
1982 p_init_msg_list,
1983 '_PVT',
1984 x_return_status);
1985 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1986 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1987 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
1988 RAISE Okc_Api.G_EXCEPTION_ERROR;
1989 END IF;
1990 BEGIN
1991 OPEN lock_csr(p_sel_rec);
1992 FETCH lock_csr INTO l_object_version_number;
1993 l_row_notfound := lock_csr%NOTFOUND;
1994 CLOSE lock_csr;
1995 EXCEPTION
1996 WHEN E_Resource_Busy THEN
1997 IF (lock_csr%ISOPEN) THEN
1998 CLOSE lock_csr;
1999 END IF;
2000 Okc_Api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2001 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2002 END;
2003
2004 IF ( l_row_notfound ) THEN
2005 OPEN lchk_csr(p_sel_rec);
2006 FETCH lchk_csr INTO lc_object_version_number;
2007 lc_row_notfound := lchk_csr%NOTFOUND;
2008 CLOSE lchk_csr;
2009 END IF;
2010 IF (lc_row_notfound) THEN
2011 Okc_Api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2012 RAISE Okc_Api.G_EXCEPTION_ERROR;
2013 ELSIF lc_object_version_number > p_sel_rec.object_version_number THEN
2014 Okc_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2015 RAISE Okc_Api.G_EXCEPTION_ERROR;
2016 ELSIF lc_object_version_number <> p_sel_rec.object_version_number THEN
2017 Okc_Api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2018 RAISE Okc_Api.G_EXCEPTION_ERROR;
2019 ELSIF lc_object_version_number = -1 THEN
2020 Okc_Api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2021 RAISE Okc_Api.G_EXCEPTION_ERROR;
2022 END IF;
2023 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2024 EXCEPTION
2025 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2026 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2027 (
2028 l_api_name,
2029 G_PKG_NAME,
2030 'OKC_API.G_RET_STS_ERROR',
2031 x_msg_count,
2032 x_msg_data,
2033 '_PVT'
2034 );
2035 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2036 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2037 (
2038 l_api_name,
2039 G_PKG_NAME,
2040 'OKC_API.G_RET_STS_UNEXP_ERROR',
2041 x_msg_count,
2042 x_msg_data,
2043 '_PVT'
2044 );
2045 WHEN OTHERS THEN
2046 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2047 (
2048 l_api_name,
2049 G_PKG_NAME,
2050 'OTHERS',
2051 x_msg_count,
2052 x_msg_data,
2053 '_PVT'
2054 );
2055 END lock_row;
2056 --------------------------------------
2057 -- lock_row for:OKL_STRM_ELEMENTS_V --
2058 --------------------------------------
2059 PROCEDURE lock_row(
2060 p_api_version IN NUMBER,
2061 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2062 x_return_status OUT NOCOPY VARCHAR2,
2063 x_msg_count OUT NOCOPY NUMBER,
2064 x_msg_data OUT NOCOPY VARCHAR2,
2065 p_selv_rec IN selv_rec_type) IS
2066
2067 l_api_version CONSTANT NUMBER := 1;
2068 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2069 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2070 l_sel_rec sel_rec_type;
2071 BEGIN
2072 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
2073 G_PKG_NAME,
2074 p_init_msg_list,
2075 l_api_version,
2076 p_api_version,
2077 '_PVT',
2078 x_return_status);
2079 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2080 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2081 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2082 RAISE Okc_Api.G_EXCEPTION_ERROR;
2083 END IF;
2084 --------------------------------------
2085 -- Move VIEW record to "Child" records
2086 --------------------------------------
2087 migrate(p_selv_rec, l_sel_rec);
2088 --------------------------------------------
2089 -- Call the LOCK_ROW for each child record
2090 --------------------------------------------
2091 lock_row(
2092 p_init_msg_list,
2093 x_return_status,
2094 x_msg_count,
2095 x_msg_data,
2096 l_sel_rec
2097 );
2098 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2099 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2100 ELSIF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2101 RAISE Okc_Api.G_EXCEPTION_ERROR;
2102 END IF;
2103 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2104 EXCEPTION
2105 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2106 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2107 (
2108 l_api_name,
2109 G_PKG_NAME,
2110 'OKC_API.G_RET_STS_ERROR',
2111 x_msg_count,
2112 x_msg_data,
2113 '_PVT'
2114 );
2115 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2116 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2117 (
2118 l_api_name,
2119 G_PKG_NAME,
2120 'OKC_API.G_RET_STS_UNEXP_ERROR',
2121 x_msg_count,
2122 x_msg_data,
2123 '_PVT'
2124 );
2125 WHEN OTHERS THEN
2126 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2127 (
2128 l_api_name,
2129 G_PKG_NAME,
2130 'OTHERS',
2131 x_msg_count,
2132 x_msg_data,
2133 '_PVT'
2134 );
2135 END lock_row;
2136 --------------------------------------
2137 -- PL/SQL TBL lock_row for:SELV_TBL --
2138 --------------------------------------
2139 PROCEDURE lock_row(
2140 p_api_version IN NUMBER,
2141 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2142 x_return_status OUT NOCOPY VARCHAR2,
2143 x_msg_count OUT NOCOPY NUMBER,
2144 x_msg_data OUT NOCOPY VARCHAR2,
2145 p_selv_tbl IN selv_tbl_type) IS
2146
2147 l_api_version CONSTANT NUMBER := 1;
2148 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2149 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2150 i NUMBER := 0;
2151 -- START change : akjain, 05/15/2001
2152 -- Adding OverAll Status Flag
2153 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2154 -- END change : akjain
2155
2156 BEGIN
2157 Okc_Api.init_msg_list(p_init_msg_list);
2158 -- Make sure PL/SQL table has records in it before passing
2159 IF (p_selv_tbl.COUNT > 0) THEN
2160 i := p_selv_tbl.FIRST;
2161 LOOP
2162 lock_row (
2163 p_api_version => p_api_version,
2164 p_init_msg_list => Okc_Api.G_FALSE,
2165 x_return_status => x_return_status,
2166 x_msg_count => x_msg_count,
2167 x_msg_data => x_msg_data,
2168 p_selv_rec => p_selv_tbl(i));
2169
2170 -- START change : akjain, 05/15/2001
2171 -- store the highest degree of error
2172 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2173 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2174 l_overall_status := x_return_status;
2175 END IF;
2176 END IF;
2177 -- END change : akjain
2178
2179 EXIT WHEN (i = p_selv_tbl.LAST);
2180 i := p_selv_tbl.NEXT(i);
2181 END LOOP;
2182
2183 -- START change : akjain, 05/15/2001
2184 -- return overall status
2185 x_return_status := l_overall_status;
2186 -- END change : akjain
2187 END IF;
2188 EXCEPTION
2189 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2190 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2191 (
2192 l_api_name,
2193 G_PKG_NAME,
2194 'OKC_API.G_RET_STS_ERROR',
2195 x_msg_count,
2196 x_msg_data,
2197 '_PVT'
2198 );
2199 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2200 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2201 (
2202 l_api_name,
2203 G_PKG_NAME,
2204 'OKC_API.G_RET_STS_UNEXP_ERROR',
2205 x_msg_count,
2206 x_msg_data,
2207 '_PVT'
2208 );
2209 WHEN OTHERS THEN
2210 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2211 (
2212 l_api_name,
2213 G_PKG_NAME,
2214 'OTHERS',
2215 x_msg_count,
2216 x_msg_data,
2217 '_PVT'
2218 );
2219 END lock_row;
2220
2221 ---------------------------------------------------------------------------
2222 -- PROCEDURE update_row
2223 ---------------------------------------------------------------------------
2224 --------------------------------------
2225 -- update_row for:OKL_STRM_ELEMENTS --
2226 --------------------------------------
2227 PROCEDURE update_row(
2228 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2229 x_return_status OUT NOCOPY VARCHAR2,
2230 x_msg_count OUT NOCOPY NUMBER,
2231 x_msg_data OUT NOCOPY VARCHAR2,
2232 p_sel_rec IN sel_rec_type,
2233 x_sel_rec OUT NOCOPY sel_rec_type) IS
2234
2235 l_api_version CONSTANT NUMBER := 1;
2236 l_api_name CONSTANT VARCHAR2(30) := 'ELEMENTS_update_row';
2237 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2238 l_sel_rec sel_rec_type := p_sel_rec;
2239 l_def_sel_rec sel_rec_type;
2240 l_row_notfound BOOLEAN := TRUE;
2241 ----------------------------------
2242 -- FUNCTION populate_new_record --
2243 ----------------------------------
2244 FUNCTION populate_new_record (
2245 p_sel_rec IN sel_rec_type,
2246 x_sel_rec OUT NOCOPY sel_rec_type
2247 ) RETURN VARCHAR2 IS
2248 l_sel_rec sel_rec_type;
2249 l_row_notfound BOOLEAN := TRUE;
2250 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2251 BEGIN
2252 x_sel_rec := p_sel_rec;
2253 -- Get current database values
2254 l_sel_rec := get_rec(p_sel_rec, l_row_notfound);
2255 IF (l_row_notfound) THEN
2256 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
2257 END IF;
2258 IF (x_sel_rec.id IS NULL)THEN
2259 x_sel_rec.id := l_sel_rec.id;
2260 ELSIF (x_sel_rec.id = Okc_Api.G_MISS_NUM) THEN
2261 x_sel_rec.id := NULL;
2262 END IF;
2263 IF (x_sel_rec.stm_id IS NULL)THEN
2264 x_sel_rec.stm_id := l_sel_rec.stm_id;
2265 ELSIF (x_sel_rec.stm_id = Okc_Api.G_MISS_NUM) THEN
2266 x_sel_rec.stm_id := NULL;
2267 END IF;
2268 IF (x_sel_rec.object_version_number IS NULL)THEN
2269 x_sel_rec.object_version_number := l_sel_rec.object_version_number;
2270 ELSIF (x_sel_rec.object_version_number = Okc_Api.G_MISS_NUM) THEN
2271 x_sel_rec.object_version_number := NULL;
2272 END IF;
2273 IF (x_sel_rec.stream_element_date IS NULL)THEN
2274 x_sel_rec.stream_element_date := l_sel_rec.stream_element_date;
2275 ELSIF (x_sel_rec.stream_element_date = Okc_Api.G_MISS_DATE) THEN
2276 x_sel_rec.stream_element_date := NULL;
2277 END IF;
2278 IF (x_sel_rec.amount IS NULL) THEN
2279 x_sel_rec.amount := l_sel_rec.amount;
2280 ELSIF (x_sel_rec.amount = Okc_Api.G_MISS_NUM) THEN
2281 x_sel_rec.amount := NULL;
2282 END IF;
2283 IF (x_sel_rec.comments IS NULL)THEN
2284 x_sel_rec.comments := l_sel_rec.comments;
2285 ELSIF (x_sel_rec.comments = Okc_Api.G_MISS_CHAR) THEN
2286 x_sel_rec.comments := NULL;
2287 END IF;
2288 IF (x_sel_rec.accrued_yn IS NULL) THEN
2289 x_sel_rec.accrued_yn := l_sel_rec.accrued_yn;
2290 ELSIF (x_sel_rec.accrued_yn = Okc_Api.G_MISS_CHAR) THEN
2291 x_sel_rec.accrued_yn := NULL;
2292 END IF;
2293 IF (x_sel_rec.program_id IS NULL)THEN
2294 x_sel_rec.program_id := l_sel_rec.program_id;
2295 ELSIF (x_sel_rec.program_id = Okc_Api.G_MISS_NUM) THEN
2296 x_sel_rec.program_id := NULL;
2297 END IF;
2298 IF (x_sel_rec.request_id IS NULL) THEN
2299 x_sel_rec.request_id := l_sel_rec.request_id;
2300 ELSIF (x_sel_rec.request_id = Okc_Api.G_MISS_NUM) THEN
2301 x_sel_rec.request_id := NULL;
2302 END IF;
2303 IF (x_sel_rec.program_application_id IS NULL)THEN
2304 x_sel_rec.program_application_id := l_sel_rec.program_application_id;
2305 ELSIF (x_sel_rec.program_application_id = Okc_Api.G_MISS_NUM) THEN
2306 x_sel_rec.program_application_id := NULL;
2307 END IF;
2308 IF (x_sel_rec.program_update_date IS NULL) THEN
2309 x_sel_rec.program_update_date := l_sel_rec.program_update_date;
2310 ELSIF (x_sel_rec.program_update_date = Okc_Api.G_MISS_DATE) THEN
2311 x_sel_rec.program_update_date := NULL;
2312 END IF;
2313 IF (x_sel_rec.se_line_number IS NULL)THEN
2314 x_sel_rec.se_line_number := l_sel_rec.se_line_number;
2315 ELSIF (x_sel_rec.se_line_number = Okc_Api.G_MISS_NUM) THEN
2316 x_sel_rec.se_line_number := NULL;
2317 END IF;
2318 IF (x_sel_rec.date_billed IS NULL)THEN
2319 x_sel_rec.date_billed := l_sel_rec.date_billed;
2320 ELSIF (x_sel_rec.date_billed = Okc_Api.G_MISS_DATE) THEN
2321 x_sel_rec.date_billed := NULL;
2322 END IF;
2323 IF (x_sel_rec.created_by IS NULL) THEN
2324 x_sel_rec.created_by := l_sel_rec.created_by;
2325 ELSIF (x_sel_rec.created_by = Okc_Api.G_MISS_NUM) THEN
2326 x_sel_rec.created_by := NULL;
2327 END IF;
2328 IF (x_sel_rec.creation_date IS NULL)THEN
2329 x_sel_rec.creation_date := l_sel_rec.creation_date;
2330 ELSIF (x_sel_rec.created_by = Okc_Api.G_MISS_NUM) THEN
2331 x_sel_rec.created_by := NULL;
2332 END IF;
2333 IF (x_sel_rec.last_updated_by IS NULL) THEN
2334 x_sel_rec.last_updated_by := l_sel_rec.last_updated_by;
2335 ELSIF (x_sel_rec.last_updated_by = Okc_Api.G_MISS_NUM) THEN
2336 x_sel_rec.last_updated_by := NULL;
2337 END IF;
2338 IF (x_sel_rec.last_update_date IS NULL)THEN
2339 x_sel_rec.last_update_date := l_sel_rec.last_update_date;
2340 ELSIF (x_sel_rec.last_update_date = Okc_Api.G_MISS_DATE) THEN
2341 x_sel_rec.last_update_date := NULL;
2342 END IF;
2343 IF (x_sel_rec.last_update_login IS NULL)THEN
2344 x_sel_rec.last_update_login := l_sel_rec.last_update_login;
2345 ELSIF (x_sel_rec.last_update_login = Okc_Api.G_MISS_NUM) THEN
2346 x_sel_rec.last_update_login := NULL;
2347 END IF;
2348 IF (x_sel_rec.sel_id IS NULL)THEN
2349 x_sel_rec.sel_id := l_sel_rec.sel_id;
2350 ELSIF (x_sel_rec.sel_id = Okc_Api.G_MISS_NUM) THEN
2351 x_sel_rec.sel_id := NULL;
2352 END IF;
2353 --Added by Keerthi 15-Sep-2003
2354 IF (x_sel_rec.source_id IS NULL)THEN
2355 x_sel_rec.source_id := l_sel_rec.source_id;
2356 ELSIF ( x_sel_rec.source_id = Okc_Api.G_MISS_NUM) THEN
2357 x_sel_rec.source_id := NULL;
2358 END IF;
2359 IF (x_sel_rec.source_table IS NULL)THEN
2360 x_sel_rec.source_table := l_sel_rec.source_table;
2361 ELSIF (x_sel_rec.source_table = Okc_Api.G_MISS_CHAR) THEN
2362 x_sel_rec.source_table := NULL;
2363 END IF;
2364 -- Added by rgooty: 4212626
2365 IF (x_sel_rec.bill_adj_flag IS NULL)THEN
2366 x_sel_rec.bill_adj_flag := l_sel_rec.bill_adj_flag;
2367 ELSIF (x_sel_rec.bill_adj_flag = Okc_Api.G_MISS_CHAR) THEN
2368 x_sel_rec.bill_adj_flag := NULL;
2369 END IF;
2370 IF (x_sel_rec.accrual_adj_flag IS NULL)THEN
2371 x_sel_rec.accrual_adj_flag := l_sel_rec.accrual_adj_flag;
2372 ELSIF (x_sel_rec.accrual_adj_flag = Okc_Api.G_MISS_CHAR) THEN
2373 x_sel_rec.accrual_adj_flag := NULL;
2374 END IF;
2375 -- Added by hkpatel for bug 4350255
2376 IF (x_sel_rec.date_disbursed IS NULL)THEN
2377 x_sel_rec.date_disbursed := l_sel_rec.date_disbursed;
2378 ELSIF (x_sel_rec.date_disbursed = Okc_Api.G_MISS_DATE) THEN
2379 x_sel_rec.date_disbursed := NULL;
2380 END IF;
2381
2382
2383 RETURN(l_return_status);
2384 END populate_new_record;
2385 ------------------------------------------
2386 -- Set_Attributes for:OKL_STRM_ELEMENTS --
2387 ------------------------------------------
2388 FUNCTION Set_Attributes (
2389 p_sel_rec IN sel_rec_type,
2390 x_sel_rec OUT NOCOPY sel_rec_type
2391 ) RETURN VARCHAR2 IS
2392 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2393 BEGIN
2394 x_sel_rec := p_sel_rec;
2395 RETURN(l_return_status);
2396 END Set_Attributes;
2397 BEGIN
2398 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
2399 p_init_msg_list,
2400 '_PVT',
2401 x_return_status);
2402 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2403 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2404 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2405 RAISE Okc_Api.G_EXCEPTION_ERROR;
2406 END IF;
2407 --- Setting item attributes
2408 l_return_status := Set_Attributes(
2409 p_sel_rec, -- IN
2410 l_sel_rec); -- OUT
2411 --- If any errors happen abort API
2412 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2413 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2414 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2415 RAISE Okc_Api.G_EXCEPTION_ERROR;
2416 END IF;
2417 l_return_status := populate_new_record(l_sel_rec, l_def_sel_rec);
2418 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2419 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2420 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2421 RAISE Okc_Api.G_EXCEPTION_ERROR;
2422 END IF;
2423 UPDATE OKL_STRM_ELEMENTS
2424 SET STM_ID = l_def_sel_rec.stm_id,
2425 OBJECT_VERSION_NUMBER = l_def_sel_rec.object_version_number,
2426 STREAM_ELEMENT_DATE = l_def_sel_rec.stream_element_date,
2427 AMOUNT = l_def_sel_rec.amount,
2428 COMMENTS = l_def_sel_rec.comments,
2429 ACCRUED_YN = l_def_sel_rec.accrued_yn,
2430 PROGRAM_ID = l_def_sel_rec.program_id,
2431 REQUEST_ID = l_def_sel_rec.request_id,
2432 PROGRAM_APPLICATION_ID = l_def_sel_rec.program_application_id,
2433 PROGRAM_UPDATE_DATE = l_def_sel_rec.program_update_date,
2434 SE_LINE_NUMBER = l_def_sel_rec.se_line_number,
2435 DATE_BILLED = l_def_sel_rec.date_billed,
2436 CREATED_BY = l_def_sel_rec.created_by,
2437 CREATION_DATE = l_def_sel_rec.creation_date,
2438 LAST_UPDATED_BY = l_def_sel_rec.last_updated_by,
2439 LAST_UPDATE_DATE = l_def_sel_rec.last_update_date,
2440 LAST_UPDATE_LOGIN = l_def_sel_rec.last_update_login,
2441 SEL_ID = l_def_sel_rec.sel_id,
2442 -- Added by Keerthi 15-Sep-2003
2443 SOURCE_ID = l_def_sel_rec.source_id,
2444 SOURCE_TABLE = l_def_sel_rec.source_table,
2445 -- Added by rgooty: 4212626
2446 BILL_ADJ_FLAG = l_def_sel_rec.bill_adj_flag,
2447 ACCRUAL_ADJ_FLAG = l_def_sel_rec.accrual_adj_flag,
2448 -- Added by hkpatel for bug 4350255
2449 DATE_DISBURSED = l_def_sel_rec.date_disbursed
2450 WHERE ID = l_def_sel_rec.id;
2451
2452 x_sel_rec := l_def_sel_rec;
2453 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2454 EXCEPTION
2455 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2456 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2457 (
2458 l_api_name,
2459 G_PKG_NAME,
2460 'OKC_API.G_RET_STS_ERROR',
2461 x_msg_count,
2462 x_msg_data,
2463 '_PVT'
2464 );
2465 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2466 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2467 (
2468 l_api_name,
2469 G_PKG_NAME,
2470 'OKC_API.G_RET_STS_UNEXP_ERROR',
2471 x_msg_count,
2472 x_msg_data,
2473 '_PVT'
2474 );
2475 WHEN OTHERS THEN
2476 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2477 (
2478 l_api_name,
2479 G_PKG_NAME,
2480 'OTHERS',
2481 x_msg_count,
2482 x_msg_data,
2483 '_PVT'
2484 );
2485 END update_row;
2486 ----------------------------------------
2487 -- update_row for:OKL_STRM_ELEMENTS_V --
2488 ----------------------------------------
2489 PROCEDURE update_row(
2490 p_api_version IN NUMBER,
2491 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2492 x_return_status OUT NOCOPY VARCHAR2,
2493 x_msg_count OUT NOCOPY NUMBER,
2494 x_msg_data OUT NOCOPY VARCHAR2,
2495 p_selv_rec IN selv_rec_type,
2496 x_selv_rec OUT NOCOPY selv_rec_type) IS
2497
2498 l_api_version CONSTANT NUMBER := 1;
2499 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2500 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2501 l_selv_rec selv_rec_type := p_selv_rec;
2502 l_def_selv_rec selv_rec_type;
2503 l_sel_rec sel_rec_type;
2504 lx_sel_rec sel_rec_type;
2505 -------------------------------
2506 -- FUNCTION fill_who_columns --
2507 -------------------------------
2508 FUNCTION fill_who_columns (
2509 p_selv_rec IN selv_rec_type
2510 ) RETURN selv_rec_type IS
2511 l_selv_rec selv_rec_type := p_selv_rec;
2512 BEGIN
2513 l_selv_rec.LAST_UPDATE_DATE := SYSDATE;
2514 l_selv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2515 l_selv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2516 RETURN(l_selv_rec);
2517 END fill_who_columns;
2518 ----------------------------------
2519 -- FUNCTION populate_new_record --
2520 ----------------------------------
2521 FUNCTION populate_new_record (
2522 p_selv_rec IN selv_rec_type,
2523 x_selv_rec OUT NOCOPY selv_rec_type
2524 ) RETURN VARCHAR2 IS
2525 l_selv_rec selv_rec_type;
2526 l_row_notfound BOOLEAN := TRUE;
2527 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2528 BEGIN
2529 x_selv_rec := p_selv_rec;
2530 -- Get current database values
2531 l_selv_rec := get_rec(p_selv_rec, l_row_notfound);
2532 IF (l_row_notfound) THEN
2533 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
2534 END IF;
2535 IF (x_selv_rec.id IS NULL)THEN
2536 x_selv_rec.id := l_selv_rec.id;
2537 ELSIF (x_selv_rec.id = Okc_Api.G_MISS_NUM) THEN
2538 x_selv_rec.id := NULL;
2539 END IF;
2540 IF (x_selv_rec.object_version_number IS NULL)THEN
2541 x_selv_rec.object_version_number := l_selv_rec.object_version_number;
2542 ELSIF (x_selv_rec.object_version_number = Okc_Api.G_MISS_NUM ) THEN
2543 x_selv_rec.object_version_number := NULL;
2544 END IF;
2545 IF (x_selv_rec.stm_id IS NULL)THEN
2546 x_selv_rec.stm_id := l_selv_rec.stm_id;
2547 ELSIF (x_selv_rec.stm_id = Okc_Api.G_MISS_NUM) THEN
2548 x_selv_rec.stm_id := NULL;
2549 END IF;
2550 IF (x_selv_rec.amount IS NULL)THEN
2551 x_selv_rec.amount := l_selv_rec.amount;
2552 ELSIF (x_selv_rec.amount = Okc_Api.G_MISS_NUM) THEN
2553 x_selv_rec.amount := NULL;
2554 END IF;
2555 IF (x_selv_rec.comments IS NULL)THEN
2556 x_selv_rec.comments := l_selv_rec.comments;
2557 ELSIF (x_selv_rec.comments = Okc_Api.G_MISS_CHAR) THEN
2558 x_selv_rec.comments := NULL ;
2559 END IF;
2560 IF (x_selv_rec.accrued_yn IS NULL) THEN
2561 x_selv_rec.accrued_yn := l_selv_rec.accrued_yn;
2562 ELSIF (x_selv_rec.accrued_yn = Okc_Api.G_MISS_CHAR) THEN
2563 x_selv_rec.accrued_yn := NULL;
2564 END IF;
2565 IF (x_selv_rec.stream_element_date IS NULL)THEN
2566 x_selv_rec.stream_element_date := l_selv_rec.stream_element_date;
2567 ELSIF (x_selv_rec.stream_element_date = Okc_Api.G_MISS_DATE) THEN
2568 x_selv_rec.stream_element_date := NULL;
2569 END IF;
2570 IF (x_selv_rec.program_id IS NULL) THEN
2571 x_selv_rec.program_id := l_selv_rec.program_id;
2572 ELSIF (x_selv_rec.program_id = Okc_Api.G_MISS_NUM) THEN
2573 x_selv_rec.program_id := NULL;
2574 END IF;
2575 IF (x_selv_rec.request_id IS NULL)THEN
2576 x_selv_rec.request_id := l_selv_rec.request_id;
2577 ELSIF (x_selv_rec.request_id = Okc_Api.G_MISS_NUM) THEN
2578 x_selv_rec.request_id := NULL;
2579 END IF;
2580 IF (x_selv_rec.program_application_id IS NULL)THEN
2581 x_selv_rec.program_application_id := l_selv_rec.program_application_id;
2582 ELSIF (x_selv_rec.program_application_id = Okc_Api.G_MISS_NUM) THEN
2583 x_selv_rec.program_application_id := NULL;
2584 END IF;
2585 IF (x_selv_rec.program_update_date IS NULL)THEN
2586 x_selv_rec.program_update_date := l_selv_rec.program_update_date;
2587 ELSIF (x_selv_rec.program_update_date = Okc_Api.G_MISS_DATE)THEN
2588 x_selv_rec.program_update_date := NULL;
2589 END IF;
2590 IF (x_selv_rec.se_line_number IS NULL)THEN
2591 x_selv_rec.se_line_number := l_selv_rec.se_line_number;
2592 ELSIF (x_selv_rec.se_line_number = Okc_Api.G_MISS_NUM) THEN
2593 x_selv_rec.se_line_number := NULL;
2594 END IF;
2595 IF (x_selv_rec.date_billed IS NULL)THEN
2596 x_selv_rec.date_billed := l_selv_rec.date_billed;
2597 ELSIF (x_selv_rec.date_billed = Okc_Api.G_MISS_DATE) THEN
2598 x_selv_rec.date_billed := NULL;
2599 END IF;
2600 IF (x_selv_rec.created_by IS NULL)THEN
2601 x_selv_rec.created_by := l_selv_rec.created_by;
2602 ELSIF (x_selv_rec.created_by = Okc_Api.G_MISS_NUM) THEN
2603 x_selv_rec.created_by := NULL;
2604 END IF;
2605 IF (x_selv_rec.creation_date IS NULL)THEN
2606 x_selv_rec.creation_date := l_selv_rec.creation_date;
2607 ELSIF (x_selv_rec.creation_date = Okc_Api.G_MISS_DATE) THEN
2608 x_selv_rec.creation_date := NULL;
2609 END IF;
2610 IF (x_selv_rec.last_updated_by IS NULL)THEN
2611 x_selv_rec.last_updated_by := l_selv_rec.last_updated_by;
2612 ELSIF (x_selv_rec.last_updated_by = Okc_Api.G_MISS_NUM) THEN
2613 x_selv_rec.last_updated_by := NULL;
2614 END IF;
2615 IF (x_selv_rec.last_update_date IS NULL)THEN
2616 x_selv_rec.last_update_date := l_selv_rec.last_update_date;
2617 ELSIF (x_selv_rec.last_update_date = Okc_Api.G_MISS_DATE) THEN
2618 x_selv_rec.last_update_date := NULL;
2619 END IF;
2620 IF (x_selv_rec.last_update_login IS NULL)THEN
2621 x_selv_rec.last_update_login := l_selv_rec.last_update_login;
2622 ELSIF (x_selv_rec.last_update_login = Okc_Api.G_MISS_NUM) THEN
2623 x_selv_rec.last_update_login := NULL;
2624 END IF;
2625 IF (x_selv_rec.sel_id IS NULL)THEN
2626 x_selv_rec.sel_id := l_selv_rec.sel_id;
2627 ELSIF (x_selv_rec.sel_id = Okc_Api.G_MISS_NUM) THEN
2628 x_selv_rec.sel_id := NULL;
2629 END IF;
2630 -- Added by Keerthi 15-Sep-2003
2631 IF (x_selv_rec.source_id IS NULL)THEN
2632 x_selv_rec.source_id := l_selv_rec.source_id;
2633 ELSIF (x_selv_rec.source_id = Okc_Api.G_MISS_NUM) THEN
2634 x_selv_rec.source_id := NULL;
2635 END IF;
2636 IF (x_selv_rec.source_table IS NULL) THEN
2637 x_selv_rec.source_table := l_selv_rec.source_table;
2638 ELSIF (x_selv_rec.source_table = Okc_Api.G_MISS_CHAR) THEN
2639 x_selv_rec.source_table := NULL;
2640 END IF;
2641 -- Added by rgooty: 4212626
2642 IF (x_selv_rec.bill_adj_flag IS NULL) THEN
2643 x_selv_rec.bill_adj_flag := l_selv_rec.bill_adj_flag;
2644 ELSIF (x_selv_rec.bill_adj_flag = Okc_Api.G_MISS_CHAR) THEN
2645 x_selv_rec.bill_adj_flag := NULL;
2646 END IF;
2647 IF (x_selv_rec.accrual_adj_flag IS NULL) THEN
2648 x_selv_rec.accrual_adj_flag := l_selv_rec.accrual_adj_flag;
2649 ELSIF (x_selv_rec.accrual_adj_flag = Okc_Api.G_MISS_CHAR) THEN
2650 x_selv_rec.accrual_adj_flag := NULL;
2651 END IF;
2652 -- Added by hkpatel for bug 4350255
2653 IF (x_selv_rec.date_billed IS NULL)THEN
2654 x_selv_rec.date_billed := l_selv_rec.date_billed;
2655 ELSIF (x_selv_rec.date_billed = Okc_Api.G_MISS_DATE) THEN
2656 x_selv_rec.date_billed := NULL;
2657 END IF;
2658
2659
2660
2661 /***** Concurrent Manager columns assignement ************/
2662 SELECT NVL(DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID)
2663 ,x_selv_rec.REQUEST_ID)
2664 ,NVL(DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID)
2665 ,x_selv_rec.PROGRAM_APPLICATION_ID)
2666 ,NVL(DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID)
2667 ,x_selv_rec.PROGRAM_ID)
2668 ,DECODE(DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
2669 ,NULL,x_selv_rec.PROGRAM_UPDATE_DATE,SYSDATE)
2670 INTO x_selv_rec.REQUEST_ID
2671 ,x_selv_rec.PROGRAM_APPLICATION_ID
2672 ,x_selv_rec.PROGRAM_ID
2673 ,x_selv_rec.PROGRAM_UPDATE_DATE
2674 FROM DUAL;
2675 /******* END Concurrent Manager COLUMN Assignment ******************/
2676
2677 RETURN(l_return_status);
2678 END populate_new_record;
2679 --------------------------------------------
2680 -- Set_Attributes for:OKL_STRM_ELEMENTS_V --
2681 --------------------------------------------
2682 FUNCTION Set_Attributes (
2683 p_selv_rec IN selv_rec_type,
2684 x_selv_rec OUT NOCOPY selv_rec_type
2685 ) RETURN VARCHAR2 IS
2686 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2687 BEGIN
2688 x_selv_rec := p_selv_rec;
2689 x_selv_rec.OBJECT_VERSION_NUMBER := NVL(x_selv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2690 RETURN(l_return_status);
2691 END Set_Attributes;
2692 BEGIN
2693 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
2694 G_PKG_NAME,
2695 p_init_msg_list,
2696 l_api_version,
2697 p_api_version,
2698 '_PVT',
2699 x_return_status);
2700 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2701 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2702 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2703 RAISE Okc_Api.G_EXCEPTION_ERROR;
2704 END IF;
2705 --- Setting item attributes
2706 l_return_status := Set_Attributes(
2707 p_selv_rec, -- IN
2708 l_selv_rec); -- OUT
2709 --- If any errors happen abort API
2710 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2711 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2712 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2713 RAISE Okc_Api.G_EXCEPTION_ERROR;
2714 END IF;
2715 l_return_status := populate_new_record(l_selv_rec, l_def_selv_rec);
2716 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2717 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2718 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2719 RAISE Okc_Api.G_EXCEPTION_ERROR;
2720 END IF;
2721 l_def_selv_rec := fill_who_columns(l_def_selv_rec);
2722 --- Validate all non-missing attributes (Item Level Validation)
2723 l_return_status := Validate_Attributes(l_def_selv_rec);
2724 --- If any errors happen abort API
2725 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2726 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2727 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2728 RAISE Okc_Api.G_EXCEPTION_ERROR;
2729 END IF;
2730 l_return_status := Validate_Record(l_def_selv_rec);
2731 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2732 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2733 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2734 RAISE Okc_Api.G_EXCEPTION_ERROR;
2735 END IF;
2736
2737 --------------------------------------
2738 -- Move VIEW record to "Child" records
2739 --------------------------------------
2740 migrate(l_def_selv_rec, l_sel_rec);
2741 --------------------------------------------
2742 -- Call the UPDATE_ROW for each child record
2743 --------------------------------------------
2744 update_row(
2745 p_init_msg_list,
2746 x_return_status,
2747 x_msg_count,
2748 x_msg_data,
2749 l_sel_rec,
2750 lx_sel_rec
2751 );
2752 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2753 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2754 ELSIF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2755 RAISE Okc_Api.G_EXCEPTION_ERROR;
2756 END IF;
2757 migrate(lx_sel_rec, l_def_selv_rec);
2758 x_selv_rec := l_def_selv_rec;
2759 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2760 EXCEPTION
2761 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2762 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2763 (
2764 l_api_name,
2765 G_PKG_NAME,
2766 'OKC_API.G_RET_STS_ERROR',
2767 x_msg_count,
2768 x_msg_data,
2769 '_PVT'
2770 );
2771 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2772 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2773 (
2774 l_api_name,
2775 G_PKG_NAME,
2776 'OKC_API.G_RET_STS_UNEXP_ERROR',
2777 x_msg_count,
2778 x_msg_data,
2779 '_PVT'
2780 );
2781 WHEN OTHERS THEN
2782 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2783 (
2784 l_api_name,
2785 G_PKG_NAME,
2786 'OTHERS',
2787 x_msg_count,
2788 x_msg_data,
2789 '_PVT'
2790 );
2791 END update_row;
2792 ----------------------------------------
2793 -- PL/SQL TBL update_row for:SELV_TBL --
2794 ----------------------------------------
2795 PROCEDURE update_row(
2796 p_api_version IN NUMBER,
2797 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2798 x_return_status OUT NOCOPY VARCHAR2,
2799 x_msg_count OUT NOCOPY NUMBER,
2800 x_msg_data OUT NOCOPY VARCHAR2,
2801 p_selv_tbl IN selv_tbl_type,
2802 x_selv_tbl OUT NOCOPY selv_tbl_type) IS
2803
2804 l_api_version CONSTANT NUMBER := 1;
2805 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2806 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2807 i NUMBER := 0;
2808 -- START change : akjain, 05/15/2001
2809 -- Adding OverAll Status Flag
2810 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2811 -- END change : akjain
2812
2813
2814 BEGIN
2815 Okc_Api.init_msg_list(p_init_msg_list);
2816 -- Make sure PL/SQL table has records in it before passing
2817 IF (p_selv_tbl.COUNT > 0) THEN
2818 i := p_selv_tbl.FIRST;
2819 LOOP
2820 update_row (
2821 p_api_version => p_api_version,
2822 p_init_msg_list => Okc_Api.G_FALSE,
2823 x_return_status => x_return_status,
2824 x_msg_count => x_msg_count,
2825 x_msg_data => x_msg_data,
2826 p_selv_rec => p_selv_tbl(i),
2827 x_selv_rec => x_selv_tbl(i));
2828 -- START change : akjain, 05/15/2001
2829 -- store the highest degree of error
2830 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2831 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2832 l_overall_status := x_return_status;
2833 END IF;
2834 END IF;
2835 -- END change : akjain
2836
2837 EXIT WHEN (i = p_selv_tbl.LAST);
2838 i := p_selv_tbl.NEXT(i);
2839 END LOOP;
2840
2841 -- START change : akjain, 05/15/2001
2842 -- return overall status
2843 x_return_status := l_overall_status;
2844 -- END change : akjain
2845 END IF;
2846 EXCEPTION
2847 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2848 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2849 (
2850 l_api_name,
2851 G_PKG_NAME,
2852 'OKC_API.G_RET_STS_ERROR',
2853 x_msg_count,
2854 x_msg_data,
2855 '_PVT'
2856 );
2857 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2858 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2859 (
2860 l_api_name,
2861 G_PKG_NAME,
2862 'OKC_API.G_RET_STS_UNEXP_ERROR',
2863 x_msg_count,
2864 x_msg_data,
2865 '_PVT'
2866 );
2867 WHEN OTHERS THEN
2868 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2869 (
2870 l_api_name,
2871 G_PKG_NAME,
2872 'OTHERS',
2873 x_msg_count,
2874 x_msg_data,
2875 '_PVT'
2876 );
2877 END update_row;
2878
2879 ---------------------------------------------------------------------------
2880 -- PROCEDURE delete_row
2881 ---------------------------------------------------------------------------
2882 --------------------------------------
2883 -- delete_row for:OKL_STRM_ELEMENTS --
2884 --------------------------------------
2885 PROCEDURE delete_row(
2886 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2887 x_return_status OUT NOCOPY VARCHAR2,
2888 x_msg_count OUT NOCOPY NUMBER,
2889 x_msg_data OUT NOCOPY VARCHAR2,
2890 p_sel_rec IN sel_rec_type) IS
2891
2892 l_api_version CONSTANT NUMBER := 1;
2893 l_api_name CONSTANT VARCHAR2(30) := 'ELEMENTS_delete_row';
2894 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2895 l_sel_rec sel_rec_type:= p_sel_rec;
2896 l_row_notfound BOOLEAN := TRUE;
2897 BEGIN
2898 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
2899 p_init_msg_list,
2900 '_PVT',
2901 x_return_status);
2902 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2903 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2904 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2905 RAISE Okc_Api.G_EXCEPTION_ERROR;
2906 END IF;
2907 DELETE FROM OKL_STRM_ELEMENTS
2908 WHERE ID = l_sel_rec.id;
2909
2910 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2911 EXCEPTION
2912 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
2913 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
2914 (
2915 l_api_name,
2916 G_PKG_NAME,
2917 'OKC_API.G_RET_STS_ERROR',
2918 x_msg_count,
2919 x_msg_data,
2920 '_PVT'
2921 );
2922 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2923 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2924 (
2925 l_api_name,
2926 G_PKG_NAME,
2927 'OKC_API.G_RET_STS_UNEXP_ERROR',
2928 x_msg_count,
2929 x_msg_data,
2930 '_PVT'
2931 );
2932 WHEN OTHERS THEN
2933 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
2934 (
2935 l_api_name,
2936 G_PKG_NAME,
2937 'OTHERS',
2938 x_msg_count,
2939 x_msg_data,
2940 '_PVT'
2941 );
2942 END delete_row;
2943 ----------------------------------------
2944 -- delete_row for:OKL_STRM_ELEMENTS_V --
2945 ----------------------------------------
2946 PROCEDURE delete_row(
2947 p_api_version IN NUMBER,
2948 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
2949 x_return_status OUT NOCOPY VARCHAR2,
2950 x_msg_count OUT NOCOPY NUMBER,
2951 x_msg_data OUT NOCOPY VARCHAR2,
2952 p_selv_rec IN selv_rec_type) IS
2953
2954 l_api_version CONSTANT NUMBER := 1;
2955 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2956 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2957 l_selv_rec selv_rec_type := p_selv_rec;
2958 l_sel_rec sel_rec_type;
2959 BEGIN
2960 l_return_status := Okc_Api.START_ACTIVITY(l_api_name,
2961 G_PKG_NAME,
2962 p_init_msg_list,
2963 l_api_version,
2964 p_api_version,
2965 '_PVT',
2966 x_return_status);
2967 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2968 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2969 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2970 RAISE Okc_Api.G_EXCEPTION_ERROR;
2971 END IF;
2972 --------------------------------------
2973 -- Move VIEW record to "Child" records
2974 --------------------------------------
2975 migrate(l_selv_rec, l_sel_rec);
2976 --------------------------------------------
2977 -- Call the DELETE_ROW for each child record
2978 --------------------------------------------
2979 delete_row(
2980 p_init_msg_list,
2981 x_return_status,
2982 x_msg_count,
2983 x_msg_data,
2984 l_sel_rec
2985 );
2986 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2987 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2988 ELSIF (x_return_status = Okc_Api.G_RET_STS_ERROR) THEN
2989 RAISE Okc_Api.G_EXCEPTION_ERROR;
2990 END IF;
2991 Okc_Api.END_ACTIVITY(x_msg_count, x_msg_data);
2992 EXCEPTION
2993 WHEN Okc_Api.G_EXCEPTION_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_ERROR',
2999 x_msg_count,
3000 x_msg_data,
3001 '_PVT'
3002 );
3003 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3004 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
3005 (
3006 l_api_name,
3007 G_PKG_NAME,
3008 'OKC_API.G_RET_STS_UNEXP_ERROR',
3009 x_msg_count,
3010 x_msg_data,
3011 '_PVT'
3012 );
3013 WHEN OTHERS THEN
3014 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
3015 (
3016 l_api_name,
3017 G_PKG_NAME,
3018 'OTHERS',
3019 x_msg_count,
3020 x_msg_data,
3021 '_PVT'
3022 );
3023 END delete_row;
3024 ----------------------------------------
3025 -- PL/SQL TBL delete_row for:SELV_TBL --
3026 ----------------------------------------
3027 PROCEDURE delete_row(
3028 p_api_version IN NUMBER,
3029 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
3030 x_return_status OUT NOCOPY VARCHAR2,
3031 x_msg_count OUT NOCOPY NUMBER,
3032 x_msg_data OUT NOCOPY VARCHAR2,
3033 p_selv_tbl IN selv_tbl_type) IS
3034
3035 l_api_version CONSTANT NUMBER := 1;
3036 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3037 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3038 i NUMBER := 0;
3039 -- START change : akjain, 05/15/2001
3040 -- Adding OverAll Status Flag
3041 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3042 -- END change : akjain
3043
3044
3045 BEGIN
3046 Okc_Api.init_msg_list(p_init_msg_list);
3047 -- Make sure PL/SQL table has records in it before passing
3048 IF (p_selv_tbl.COUNT > 0) THEN
3049 i := p_selv_tbl.FIRST;
3050 LOOP
3051 delete_row (
3052 p_api_version => p_api_version,
3053 p_init_msg_list => Okc_Api.G_FALSE,
3054 x_return_status => x_return_status,
3055 x_msg_count => x_msg_count,
3056 x_msg_data => x_msg_data,
3057 p_selv_rec => p_selv_tbl(i));
3058 -- START change : akjain, 05/15/2001
3059 -- store the highest degree of error
3060 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
3061 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
3062 l_overall_status := x_return_status;
3063 END IF;
3064 END IF;
3065 -- END change : akjain
3066
3067 EXIT WHEN (i = p_selv_tbl.LAST);
3068 i := p_selv_tbl.NEXT(i);
3069 END LOOP;
3070
3071 -- START change : akjain, 05/15/2001
3072 -- return overall status
3073 x_return_status := l_overall_status;
3074 -- END change : akjain
3075 END IF;
3076 EXCEPTION
3077 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
3078 x_return_status := Okc_Api.HANDLE_EXCEPTIONS
3079 (
3080 l_api_name,
3081 G_PKG_NAME,
3082 'OKC_API.G_RET_STS_ERROR',
3083 x_msg_count,
3084 x_msg_data,
3085 '_PVT'
3086 );
3087 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3088 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
3089 (
3090 l_api_name,
3091 G_PKG_NAME,
3092 'OKC_API.G_RET_STS_UNEXP_ERROR',
3093 x_msg_count,
3094 x_msg_data,
3095 '_PVT'
3096 );
3097 WHEN OTHERS THEN
3098 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
3099 (
3100 l_api_name,
3101 G_PKG_NAME,
3102 'OTHERS',
3103 x_msg_count,
3104 x_msg_data,
3105 '_PVT'
3106 );
3107 END delete_row;
3108 END Okl_Sel_Pvt;