[Home] [Help]
PACKAGE BODY: APPS.OKL_STREAMS_PVT
Source
1 PACKAGE BODY Okl_Streams_Pvt AS
2 /* $Header: OKLCSTMB.pls 120.3 2005/07/05 05:25:47 mansrini noship $ */
3
4 PROCEDURE create_streams(
5 p_api_version IN NUMBER
6 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
7 ,x_return_status OUT NOCOPY VARCHAR2
8 ,x_msg_count OUT NOCOPY NUMBER
9 ,x_msg_data OUT NOCOPY VARCHAR2
10 ,p_stmv_rec IN stmv_rec_type
11 ,p_selv_tbl IN selv_tbl_type
12 ,x_stmv_rec OUT NOCOPY stmv_rec_type
13 ,x_selv_tbl OUT NOCOPY selv_tbl_type
14 ) IS
15
16 i NUMBER;
17 l_stmv_rec stmv_rec_type;
18 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
19 l_selv_tbl selv_tbl_type := p_selv_tbl;
20
21 BEGIN
22 -- Populate streams table
23 create_streams(
24 p_api_version => p_api_version
25 ,p_init_msg_list => p_init_msg_list
26 ,x_return_status => x_return_status
27 ,x_msg_count => x_msg_count
28 ,x_msg_data => x_msg_data
29 ,p_stmv_rec => p_stmv_rec
30 ,x_stmv_rec => x_stmv_rec);
31 -- rabhupat bug#4371472 user defined exceptions are not handled
32 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
33 RAISE G_EXCEPTION_HALT_VALIDATION;
34 END IF;
35 -- rabhupat bug#4371472 end
36 -- populate the foreign key for the stream elements
37 IF (l_selv_tbl.COUNT > 0) THEN
38 i:= l_selv_tbl.FIRST;
39 LOOP
40 l_selv_tbl(i).stm_id := x_stmv_rec.id;
41 EXIT WHEN(i = l_selv_tbl.LAST);
42 i := l_selv_tbl.NEXT(i);
43 END LOOP;
44 END IF;
45
46 -- populate the detail
47 create_stream_elements(
48 p_api_version => p_api_version
49 ,p_init_msg_list => p_init_msg_list
50 ,x_return_status => x_return_status
51 ,x_msg_count => x_msg_count
52 ,x_msg_data => x_msg_data
53 ,p_selv_tbl => l_selv_tbl
54 ,x_selv_tbl => x_selv_tbl);
55 -- rabhupat bug#4371472 user defined exceptions are not handled
56 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
57 RAISE G_EXCEPTION_HALT_VALIDATION;
58 END IF;
59 -- rabhupat bug#4371472 end
60 EXCEPTION
61 WHEN G_EXCEPTION_HALT_VALIDATION THEN
62 NULL;
63
64 WHEN OTHERS THEN
65 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
66 ,p_msg_name => g_unexpected_error
67 ,p_token1 => g_sqlcode_token
68 ,p_token1_value => SQLCODE
69 ,p_token2 => g_sqlerrm_token
70 ,p_token2_value => SQLERRM);
71 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
72 END create_streams;
73
74
75 -- create streams(master-table,detail-table)
76 PROCEDURE create_streams(
77 p_api_version IN NUMBER
78 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
79 ,x_return_status OUT NOCOPY VARCHAR2
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2
82 ,p_stmv_tbl IN stmv_tbl_type
83 ,p_selv_tbl IN selv_tbl_type
84 ,x_stmv_tbl OUT NOCOPY stmv_tbl_type
85 ,x_selv_tbl OUT NOCOPY selv_tbl_type
86 )
87 IS
88 l_return_status VARCHAR2(1) :=OKC_API.G_RET_STS_SUCCESS;
89 l_stmv_tbl stmv_tbl_type;
90 l_selv_tbl selv_tbl_type := p_selv_tbl;
91
92 i BINARY_INTEGER;
93 j BINARY_INTEGER;
94 BEGIN
95 -- populate the master
96 create_streams(
97 p_api_version,
98 p_init_msg_list,
99 x_return_status,
100 x_msg_count,
101 x_msg_data,
102 p_stmv_tbl,
103 x_stmv_tbl);
104 okl_accounting_util.get_error_message(x_msg_count, x_msg_data);
105
106 -- rabhupat bug#4371472 user defined exceptions are not handled
107 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
108 RAISE G_EXCEPTION_HALT_VALIDATION;
109 END IF;
110 -- rabhupat bug#4371472 end
111
112 FOR i IN x_stmv_tbl.first..x_stmv_tbl.last
113 LOOP
114 FOR j IN l_selv_tbl.first..l_selv_tbl.last
115 LOOP
116 IF l_selv_tbl(j).parent_index = i
117 THEN
118 l_selv_tbl(j).stm_id := x_stmv_tbl(i).id;
119 END IF;
120 END LOOP;
121 END LOOP;
122 /*
123 -- populate the foreign keys for the detail
124 if(l_selv_tbl.count > 0) then
125 i:=l_selv_tbl.FIRST;
126 loop
127 -- assuming that stm_id in the okl_strm_elements table is referring to id in the okl_streams table
128 l_selv_tbl(i).stm_id:=x_stmv_tbl(i).id;
129 exit when(i=l_selv_tbl.last);
130 i :=l_selv_tbl.next(i);
131 end loop;
132 end if; */
133
134 -- populate the detail
135 create_stream_elements(
136 p_api_version,
137 p_init_msg_list,
138 x_return_status,
139 x_msg_count,
140 x_msg_data,
141 l_selv_tbl,
142 x_selv_tbl);
143
144 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
145 RAISE G_EXCEPTION_HALT_VALIDATION;
146 ELSE
147 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
148 l_return_status :=x_return_status;
149 END IF;
150 END IF;
151 EXCEPTION
152 WHEN G_EXCEPTION_HALT_VALIDATION THEN
153 NULL;
154 WHEN OTHERS THEN
155 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
156 p_msg_name => g_unexpected_error,
157 p_token1 => g_sqlcode_token,
158 p_token1_value => SQLCODE,
159 p_token2 => g_sqlerrm_token,
160 p_token2_value => SQLERRM);
161
162 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
163
164 END create_streams;
165
166 PROCEDURE create_streams(
167 p_api_version IN NUMBER
168 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
169 ,x_return_status OUT NOCOPY VARCHAR2
170 ,x_msg_count OUT NOCOPY NUMBER
171 ,x_msg_data OUT NOCOPY VARCHAR2
172 ,p_stmv_rec IN stmv_rec_type
173 ,x_stmv_rec OUT NOCOPY stmv_rec_type) IS
174 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
175 BEGIN
176 Okl_Stm_Pvt.insert_row(
177 p_api_version => p_api_version
178 ,p_init_msg_list => p_init_msg_list
179 ,x_return_status => x_return_status
180 ,x_msg_count => x_msg_count
181 ,x_msg_data => x_msg_data
182 ,p_stmv_rec => p_stmv_rec
183 ,x_stmv_rec => x_stmv_rec
184 );
185
186 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
187 RAISE G_EXCEPTION_HALT_VALIDATION;
188 ELSE
189 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
190 l_return_status := x_return_status;
191 END IF;
192 END IF;
193 -- Custom code if any
194
195 EXCEPTION
196 WHEN G_EXCEPTION_HALT_VALIDATION THEN
197 NULL;
198
199 WHEN OTHERS THEN
200 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
201 ,p_msg_name => g_unexpected_error
202 ,p_token1 => g_sqlcode_token
203 ,p_token1_value => SQLCODE
204 ,p_token2 => g_sqlerrm_token
205 ,p_token2_value => SQLERRM);
206 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
207 END create_streams;
208
209
210
211 PROCEDURE create_streams(
212 p_api_version IN NUMBER
213 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
214 ,x_return_status OUT NOCOPY VARCHAR2
215 ,x_msg_count OUT NOCOPY NUMBER
216 ,x_msg_data OUT NOCOPY VARCHAR2
217 ,p_stmv_tbl IN stmv_tbl_type
218 ,x_stmv_tbl OUT NOCOPY stmv_tbl_type) IS
219 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
220
221 BEGIN
222 Okl_Stm_Pvt.insert_row(
223 p_api_version => p_api_version
224 ,p_init_msg_list => p_init_msg_list
225 ,x_return_status => x_return_status
226 ,x_msg_count => x_msg_count
227 ,x_msg_data => x_msg_data
228 ,p_stmv_tbl => p_stmv_tbl
229 ,x_stmv_tbl => x_stmv_tbl
230 );
231 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
232 RAISE G_EXCEPTION_HALT_VALIDATION;
233 ELSE
234 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
235 l_return_status := x_return_status;
236 END IF;
237 END IF;
238 -- Custom code if any
239
240 EXCEPTION
241 WHEN G_EXCEPTION_HALT_VALIDATION THEN
242 NULL;
243
244 WHEN OTHERS THEN
245 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
246 ,p_msg_name => g_unexpected_error
247 ,p_token1 => g_sqlcode_token
248 ,p_token1_value => SQLCODE
249 ,p_token2 => g_sqlerrm_token
250 ,p_token2_value => SQLERRM);
251 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
252
253 END create_streams;
254
255 -- Added by kthiruva on 12-May-2005 for Streams Performance
256 -- Bug 4346646 - Start of Changes
257 -- create streams(master-table,detail-table)
258 PROCEDURE create_streams_perf(
259 p_api_version IN NUMBER
260 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
261 ,x_return_status OUT NOCOPY VARCHAR2
262 ,x_msg_count OUT NOCOPY NUMBER
263 ,x_msg_data OUT NOCOPY VARCHAR2
264 ,p_stmv_tbl IN stmv_tbl_type
265 ,p_selv_tbl IN selv_tbl_type
266 ,x_stmv_tbl OUT NOCOPY stmv_tbl_type
267 ,x_selv_tbl OUT NOCOPY selv_tbl_type
268 )
269 IS
270 l_return_status VARCHAR2(1) :=OKC_API.G_RET_STS_SUCCESS;
271 l_stmv_tbl stmv_tbl_type;
272 l_selv_tbl selv_tbl_type := p_selv_tbl;
273
274 i BINARY_INTEGER;
275 j BINARY_INTEGER;
276 l_new_rec BOOLEAN := true;
277 BEGIN
278 -- calling the bulk update method of the TAPI
279 okl_stm_pvt.insert_row_perf(
280 p_api_version,
281 p_init_msg_list,
282 x_return_status,
283 x_msg_count,
284 x_msg_data,
285 p_stmv_tbl,
286 x_stmv_tbl);
287 okl_accounting_util.get_error_message(x_msg_count, x_msg_data);
288
289 -- rabhupat bug#4371472 user defined exceptions are not handled
290 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
291 RAISE G_EXCEPTION_HALT_VALIDATION;
292 END IF;
293 -- rabhupat bug#4371472 end
294
295 -- This procedure call receives a table of stream headers and a table of stream
296 -- elements.
297
298 -- Populate the stm_id by using the x_stmv_tbl( l_selv_tbl(i).parent_index).id
299 -- Modified by RGOOTY
300 IF l_selv_tbl.COUNT > 0
301 THEN
302 i := l_selv_tbl.FIRST;
303 WHILE i <= l_selv_tbl.LAST
304 LOOP
305 l_selv_tbl(i).stm_id := x_stmv_tbl( l_selv_tbl(i).parent_index ).id;
306 i := l_selv_tbl.NEXT(i);
307 END LOOP;
308 END IF;
309
310 -- populate the detail
311 create_stream_elements(
312 p_api_version,
313 p_init_msg_list,
314 x_return_status,
315 x_msg_count,
316 x_msg_data,
317 l_selv_tbl,
318 x_selv_tbl);
319
320 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
321 RAISE G_EXCEPTION_HALT_VALIDATION;
322 ELSE
323 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
324 l_return_status :=x_return_status;
325 END IF;
326 END IF;
327 EXCEPTION
328 WHEN G_EXCEPTION_HALT_VALIDATION THEN
329 NULL;
330 WHEN OTHERS THEN
331 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
332 p_msg_name => g_unexpected_error,
333 p_token1 => g_sqlcode_token,
334 p_token1_value => SQLCODE,
335 p_token2 => g_sqlerrm_token,
336 p_token2_value => SQLERRM);
337
338 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
339
340 END create_streams_perf;
341 --Bug 4346646-End of Changes
342
343 -- Object type procedure for update
344 PROCEDURE update_streams(
345 p_api_version IN NUMBER,
346 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2,
350 p_stmv_rec IN stmv_rec_type,
351 p_selv_tbl IN selv_tbl_type,
352 x_stmv_rec OUT NOCOPY stmv_rec_type,
353 x_selv_tbl OUT NOCOPY selv_tbl_type) IS
354 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
355 BEGIN
356 -- Update the master
357 update_streams(
358 p_api_version => p_api_version
359 ,p_init_msg_list => p_init_msg_list
360 ,x_return_status => x_return_status
361 ,x_msg_count => x_msg_count
362 ,x_msg_data => x_msg_data
363 ,p_stmv_rec => p_stmv_rec
364 ,x_stmv_rec => x_stmv_rec
365 );
366 -- rabhupat bug#4371472 user defined exceptions are not handled
367 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
368 RAISE G_EXCEPTION_HALT_VALIDATION;
369 END IF;
370 -- rabhupat bug#4371472 end
371
372 -- Update the stream elements
373 update_stream_elements(
374 p_api_version => p_api_version
375 ,p_init_msg_list => p_init_msg_list
376 ,x_return_status => x_return_status
377 ,x_msg_count => x_msg_count
378 ,x_msg_data => x_msg_data
379 ,p_selv_tbl => p_selv_tbl
380 ,x_selv_tbl => x_selv_tbl
381 );
382
383 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
384 RAISE G_EXCEPTION_HALT_VALIDATION;
385 ELSE
386 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
387 l_return_status := x_return_status;
388 END IF;
389 END IF;
390
391 EXCEPTION
392 WHEN G_EXCEPTION_HALT_VALIDATION THEN
393 NULL;
394
395 WHEN OTHERS THEN
396 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
397 ,p_msg_name => g_unexpected_error
398 ,p_token1 => g_sqlcode_token
399 ,p_token1_value => SQLCODE
400 ,p_token2 => g_sqlerrm_token
401 ,p_token2_value => SQLERRM);
402 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
403
404 END update_streams;
405
406 PROCEDURE validate_streams(
407 p_api_version IN NUMBER
408 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
409 ,x_return_status OUT NOCOPY VARCHAR2
410 ,x_msg_count OUT NOCOPY NUMBER
411 ,x_msg_data OUT NOCOPY VARCHAR2
412 ,p_stmv_rec IN stmv_rec_type
413 ,p_selv_tbl IN selv_tbl_type) IS
414
415 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
416 BEGIN
417 -- Validate the master
418 validate_streams(
419 p_api_version => p_api_version
420 ,p_init_msg_list => p_init_msg_list
421 ,x_return_status => x_return_status
422 ,x_msg_count => x_msg_count
423 ,x_msg_data => x_msg_data
424 ,p_stmv_rec => p_stmv_rec
425 );
426
427 -- rabhupat bug#4371472 user defined exceptions are not handled
428 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
429 RAISE G_EXCEPTION_HALT_VALIDATION;
430 END IF;
431 -- rabhupat bug#4371472 end
432
433 -- Validate the stream elements
434 validate_stream_elements(
435 p_api_version => p_api_version
436 ,p_init_msg_list => p_init_msg_list
437 ,x_return_status => x_return_status
438 ,x_msg_count => x_msg_count
439 ,x_msg_data => x_msg_data
440 ,p_selv_tbl => p_selv_tbl
441 );
442
443 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
444 RAISE G_EXCEPTION_HALT_VALIDATION;
445 ELSE
446 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
447 l_return_status := x_return_status;
448 END IF;
449 END IF;
450
451 EXCEPTION
452 WHEN G_EXCEPTION_HALT_VALIDATION THEN
453 NULL;
454 WHEN OTHERS THEN
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 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
462
463 END validate_streams;
464
465 PROCEDURE lock_streams(
466 p_api_version IN NUMBER
467 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
468 ,x_return_status OUT NOCOPY VARCHAR2
469 ,x_msg_count OUT NOCOPY NUMBER
470 ,x_msg_data OUT NOCOPY VARCHAR2
471 ,p_stmv_rec IN stmv_rec_type) IS
472 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
473
474 BEGIN
475 Okl_Stm_Pvt.lock_row(
476 p_api_version => p_api_version
477 ,p_init_msg_list => p_init_msg_list
478 ,x_return_status => x_return_status
479 ,x_msg_count => x_msg_count
480 ,x_msg_data => x_msg_data
481 ,p_stmv_rec => p_stmv_rec
482 );
483 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
484 RAISE G_EXCEPTION_HALT_VALIDATION;
485 ELSE
486 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
487 l_return_status := x_return_status;
488 END IF;
489 END IF;
490
491 EXCEPTION
492 WHEN G_EXCEPTION_HALT_VALIDATION THEN
493 NULL;
494 WHEN OTHERS THEN
495 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
496 ,p_msg_name => g_unexpected_error
497 ,p_token1 => g_sqlcode_token
498 ,p_token1_value => SQLCODE
499 ,p_token2 => g_sqlerrm_token
500 ,p_token2_value => SQLERRM);
501 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
502 END lock_streams;
503
504 PROCEDURE lock_streams(
505 p_api_version IN NUMBER
506 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
507 ,x_return_status OUT NOCOPY VARCHAR2
508 ,x_msg_count OUT NOCOPY NUMBER
509 ,x_msg_data OUT NOCOPY VARCHAR2
510 ,p_stmv_tbl IN stmv_tbl_type) IS
511 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
512
513 BEGIN
514 Okl_Stm_Pvt.lock_row(
515 p_api_version => p_api_version
516 ,p_init_msg_list => p_init_msg_list
517 ,x_return_status => x_return_status
518 ,x_msg_count => x_msg_count
519 ,x_msg_data => x_msg_data
520 ,p_stmv_tbl => p_stmv_tbl
521 );
522 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
523 RAISE G_EXCEPTION_HALT_VALIDATION;
524 ELSE
525 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
526 l_return_status := x_return_status;
527 END IF;
528 END IF;
529
530 EXCEPTION
531 WHEN G_EXCEPTION_HALT_VALIDATION THEN
532 NULL;
533 WHEN OTHERS THEN
534 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
535 ,p_msg_name => g_unexpected_error
536 ,p_token1 => g_sqlcode_token
537 ,p_token1_value => SQLCODE
538 ,p_token2 => g_sqlerrm_token
539 ,p_token2_value => SQLERRM);
540 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
541 END lock_streams;
542
543 PROCEDURE update_streams(
544 p_api_version IN NUMBER
545 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
546 ,x_return_status OUT NOCOPY VARCHAR2
547 ,x_msg_count OUT NOCOPY NUMBER
548 ,x_msg_data OUT NOCOPY VARCHAR2
549 ,p_stmv_rec IN stmv_rec_type
550 ,x_stmv_rec OUT NOCOPY stmv_rec_type) IS
551 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
552
553 BEGIN
554 Okl_Stm_Pvt.update_row(
555 p_api_version => p_api_version
556 ,p_init_msg_list => p_init_msg_list
557 ,x_return_status => x_return_status
558 ,x_msg_count => x_msg_count
559 ,x_msg_data => x_msg_data
560 ,p_stmv_rec => p_stmv_rec
561 ,x_stmv_rec => x_stmv_rec
562 );
563 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
564 RAISE G_EXCEPTION_HALT_VALIDATION;
565 ELSE
566 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
567 l_return_status := x_return_status;
568 END IF;
569 END IF;
570 EXCEPTION
571 WHEN G_EXCEPTION_HALT_VALIDATION THEN
572 NULL;
573 WHEN OTHERS THEN
574 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
575 ,p_msg_name => g_unexpected_error
576 ,p_token1 => g_sqlcode_token
577 ,p_token1_value => SQLCODE
578 ,p_token2 => g_sqlerrm_token
579 ,p_token2_value => SQLERRM);
580 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
581 END update_streams;
582
583 PROCEDURE update_streams(
584 p_api_version IN NUMBER
585 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
586 ,x_return_status OUT NOCOPY VARCHAR2
587 ,x_msg_count OUT NOCOPY NUMBER
588 ,x_msg_data OUT NOCOPY VARCHAR2
589 ,p_stmv_tbl IN stmv_tbl_type
590 ,x_stmv_tbl OUT NOCOPY stmv_tbl_type) IS
591 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
592 BEGIN
593 Okl_Stm_Pvt.update_row(
594 p_api_version => p_api_version
595 ,p_init_msg_list => p_init_msg_list
596 ,x_return_status => x_return_status
597 ,x_msg_count => x_msg_count
598 ,x_msg_data => x_msg_data
599 ,p_stmv_tbl => p_stmv_tbl
600 ,x_stmv_tbl => x_stmv_tbl
601 );
602 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
603 RAISE G_EXCEPTION_HALT_VALIDATION;
604 ELSE
605 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
606 l_return_status := x_return_status;
607 END IF;
608 END IF;
609 EXCEPTION
610 WHEN G_EXCEPTION_HALT_VALIDATION THEN
611 NULL;
612 WHEN OTHERS THEN
613 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
614 ,p_msg_name => g_unexpected_error
615 ,p_token1 => g_sqlcode_token
616 ,p_token1_value => SQLCODE
617 ,p_token2 => g_sqlerrm_token
618 ,p_token2_value => SQLERRM);
619 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
620 END update_streams;
621
622 --Put custom code for cascade delete by developer
623 PROCEDURE delete_streams(
624 p_api_version IN NUMBER
625 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
626 ,x_return_status OUT NOCOPY VARCHAR2
627 ,x_msg_count OUT NOCOPY NUMBER
628 ,x_msg_data OUT NOCOPY VARCHAR2
629 ,p_stmv_rec IN stmv_rec_type) IS
630 i NUMBER :=0;
631 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
632 l_selv_tbl selv_tbl_type;
633
634 CURSOR sel_csr IS
635 SELECT sel.id
636 FROM OKL_STRM_ELEMENTS sel
637 WHERE sel.stm_id = p_stmv_rec.id;
638 BEGIN
639 FOR sel_rec IN sel_csr
640 LOOP
641 i := i + 1;
642 l_selv_tbl(i).id := sel_rec.id;
643 END LOOP;
644 IF l_selv_tbl.COUNT > 0 THEN
645 delete_stream_elements(
646 p_api_version => p_api_version
647 ,p_init_msg_list => p_init_msg_list
648 ,x_return_status => x_return_status
649 ,x_msg_count => x_msg_count
650 ,x_msg_data => x_msg_data
651 ,p_selv_tbl => l_selv_tbl
652 );
653
654 -- rabhupat bug#4371472 user defined exceptions are not handled
655 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
656 RAISE G_EXCEPTION_HALT_VALIDATION;
657 END IF;
658 -- rabhupat bug#4371472 end
659 END IF;
660 --Delete the Master
661 Okl_Stm_Pvt.delete_row(
662 p_api_version => p_api_version
663 ,p_init_msg_list => p_init_msg_list
664 ,x_return_status => x_return_status
665 ,x_msg_count => x_msg_count
666 ,x_msg_data => x_msg_data
667 ,p_stmv_rec => p_stmv_rec
668 );
669
670 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
671 RAISE G_EXCEPTION_HALT_VALIDATION;
672 ELSE
673 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
674 l_return_status := x_return_status;
675 END IF;
676 END IF;
677 EXCEPTION
678 WHEN G_EXCEPTION_HALT_VALIDATION THEN
679 NULL;
680 WHEN OTHERS THEN
681 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
682 ,p_msg_name => g_unexpected_error
683 ,p_token1 => g_sqlcode_token
684 ,p_token1_value => SQLCODE
685 ,p_token2 => g_sqlerrm_token
686 ,p_token2_value => SQLERRM);
687
688 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
689 END delete_streams;
690
691 PROCEDURE delete_streams(
692 p_api_version IN NUMBER
693 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
694 ,x_return_status OUT NOCOPY VARCHAR2
695 ,x_msg_count OUT NOCOPY NUMBER
696 ,x_msg_data OUT NOCOPY VARCHAR2
697 ,p_stmv_tbl IN stmv_tbl_type) IS
698 i NUMBER :=0;
699 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
700 BEGIN
701 --Initialize the return status
702 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
703 IF (p_stmv_tbl.COUNT > 0) THEN
704 i := p_stmv_tbl.FIRST;
705 LOOP
706 delete_streams(
707 p_api_version => p_api_version
708 ,p_init_msg_list => p_init_msg_list
709 ,x_return_status => x_return_status
710 ,x_msg_count => x_msg_count
711 ,x_msg_data => x_msg_data
712 ,p_stmv_rec => p_stmv_tbl(i)
713 );
714
715 EXIT WHEN (i = p_stmv_tbl.LAST);
716 i := p_stmv_tbl.NEXT(i);
717 END LOOP;
718 END IF;
719 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
720 RAISE G_EXCEPTION_HALT_VALIDATION;
721 ELSE
722 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
723 l_return_status := x_return_status;
724 END IF;
725 END IF;
726 EXCEPTION
727 WHEN G_EXCEPTION_HALT_VALIDATION THEN
728 NULL;
729 WHEN OTHERS THEN
730 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
731 ,p_msg_name => g_unexpected_error
732 ,p_token1 => g_sqlcode_token
733 ,p_token1_value => SQLCODE
734 ,p_token2 => g_sqlerrm_token
735 ,p_token2_value => SQLERRM);
736 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
737 END delete_streams;
738
739 PROCEDURE validate_streams(
740 p_api_version IN NUMBER
741 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
742 ,x_return_status OUT NOCOPY VARCHAR2
743 ,x_msg_count OUT NOCOPY NUMBER
744 ,x_msg_data OUT NOCOPY VARCHAR2
745 ,p_stmv_rec IN stmv_rec_type) IS
746 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
747 BEGIN
748 Okl_Stm_Pvt.validate_row(
749 p_api_version => p_api_version
750 ,p_init_msg_list => p_init_msg_list
751 ,x_return_status => x_return_status
752 ,x_msg_count => x_msg_count
753 ,x_msg_data => x_msg_data
754 ,p_stmv_rec => p_stmv_rec
755 );
756 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
757 RAISE G_EXCEPTION_HALT_VALIDATION;
758 ELSE
759 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
760 l_return_status := x_return_status;
761 END IF;
762 END IF;
763 EXCEPTION
764 WHEN G_EXCEPTION_HALT_VALIDATION THEN
765 NULL;
766 WHEN OTHERS THEN
767 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
768 ,p_msg_name => g_unexpected_error
769 ,p_token1 => g_sqlcode_token
770 ,p_token1_value => SQLCODE
771 ,p_token2 => g_sqlerrm_token
772 ,p_token2_value => SQLERRM);
773 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
774 END validate_streams;
775
776 PROCEDURE validate_streams(
777 p_api_version IN NUMBER,
778 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
779 x_return_status OUT NOCOPY VARCHAR2,
780 x_msg_count OUT NOCOPY NUMBER,
781 x_msg_data OUT NOCOPY VARCHAR2,
782 p_stmv_tbl IN stmv_tbl_type) IS
783 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
784
785 BEGIN
786 Okl_Stm_Pvt.validate_row(
787 p_api_version => p_api_version
788 ,p_init_msg_list => p_init_msg_list
789 ,x_return_status => x_return_status
790 ,x_msg_count => x_msg_count
791 ,x_msg_data => x_msg_data
792 ,p_stmv_tbl => p_stmv_tbl
793 );
794 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
795 RAISE G_EXCEPTION_HALT_VALIDATION;
796 ELSE
797 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
798 l_return_status := x_return_status;
799 END IF;
800 END IF;
801 EXCEPTION
802 WHEN G_EXCEPTION_HALT_VALIDATION THEN
803 NULL;
804 WHEN OTHERS THEN
805 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
806 ,p_msg_name => g_unexpected_error
807 ,p_token1 => g_sqlcode_token
808 ,p_token1_value => SQLCODE
809 ,p_token2 => g_sqlerrm_token
810 ,p_token2_value => SQLERRM);
811 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
812 END validate_streams;
813
814 PROCEDURE create_stream_elements(
815 p_api_version IN NUMBER
816 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
817 ,x_return_status OUT NOCOPY VARCHAR2
818 ,x_msg_count OUT NOCOPY NUMBER
819 ,x_msg_data OUT NOCOPY VARCHAR2
820 ,p_selv_rec IN selv_rec_type
821 ,x_selv_rec OUT NOCOPY selv_rec_type) IS
822 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
823
824 BEGIN
825 Okl_Sel_Pvt.insert_row(
826 p_api_version => p_api_version
827 ,p_init_msg_list => p_init_msg_list
828 ,x_return_status => x_return_status
829 ,x_msg_count => x_msg_count
830 ,x_msg_data => x_msg_data
831 ,p_selv_rec => p_selv_rec
832 ,x_selv_rec => x_selv_rec
833 );
834 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
835 RAISE G_EXCEPTION_HALT_VALIDATION;
836 ELSE
837 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
838 l_return_status := x_return_status;
839 END IF;
840 END IF;
841 EXCEPTION
842 WHEN G_EXCEPTION_HALT_VALIDATION THEN
843 NULL;
844 WHEN OTHERS THEN
845 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
846 ,p_msg_name => g_unexpected_error
847 ,p_token1 => g_sqlcode_token
848 ,p_token1_value => SQLCODE
849 ,p_token2 => g_sqlerrm_token
850 ,p_token2_value => SQLERRM);
851 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
852 END create_stream_elements;
853
854 PROCEDURE create_stream_elements(
855 p_api_version IN NUMBER
856 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
857 ,x_return_status OUT NOCOPY VARCHAR2
858 ,x_msg_count OUT NOCOPY NUMBER
859 ,x_msg_data OUT NOCOPY VARCHAR2
860 ,p_selv_tbl IN selv_tbl_type
861 ,x_selv_tbl OUT NOCOPY selv_tbl_type) IS
862 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
863
864 BEGIN
865 Okl_Sel_Pvt.insert_row(
866 p_api_version => p_api_version
867 ,p_init_msg_list => p_init_msg_list
868 ,x_return_status => x_return_status
869 ,x_msg_count => x_msg_count
870 ,x_msg_data => x_msg_data
871 ,p_selv_tbl => p_selv_tbl
872 ,x_selv_tbl => x_selv_tbl
873 );
874 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
875 RAISE G_EXCEPTION_HALT_VALIDATION;
876 ELSE
877 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
878 l_return_status := x_return_status;
879 END IF;
880 END IF;
881 EXCEPTION
882 WHEN G_EXCEPTION_HALT_VALIDATION THEN
883 NULL;
884 WHEN OTHERS THEN
885 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
886 ,p_msg_name => g_unexpected_error
887 ,p_token1 => g_sqlcode_token
888 ,p_token1_value => SQLCODE
889 ,p_token2 => g_sqlerrm_token
890 ,p_token2_value => SQLERRM);
891 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
892 END create_stream_elements;
893
894 PROCEDURE lock_stream_elements(
895 p_api_version IN NUMBER
896 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
897 ,x_return_status OUT NOCOPY VARCHAR2
898 ,x_msg_count OUT NOCOPY NUMBER
899 ,x_msg_data OUT NOCOPY VARCHAR2
900 ,p_selv_rec IN selv_rec_type) IS
901 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
902 BEGIN
903 Okl_Sel_Pvt.lock_row(
904 p_api_version => p_api_version
905 ,p_init_msg_list => p_init_msg_list
906 ,x_return_status => x_return_status
907 ,x_msg_count => x_msg_count
908 ,x_msg_data => x_msg_data
909 ,p_selv_rec => p_selv_rec
910 );
911 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
912 RAISE G_EXCEPTION_HALT_VALIDATION;
913 ELSE
914 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
915 l_return_status := x_return_status;
916 END IF;
917 END IF;
918 EXCEPTION
919 WHEN G_EXCEPTION_HALT_VALIDATION THEN
920 NULL;
921 WHEN OTHERS THEN
922 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
923 ,p_msg_name => g_unexpected_error
924 ,p_token1 => g_sqlcode_token
925 ,p_token1_value => SQLCODE
926 ,p_token2 => g_sqlerrm_token
927 ,p_token2_value => SQLERRM);
928 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
929
930 END lock_stream_elements;
931
932 PROCEDURE lock_stream_elements(
933 p_api_version IN NUMBER
934 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
935 ,x_return_status OUT NOCOPY VARCHAR2
936 ,x_msg_count OUT NOCOPY NUMBER
937 ,x_msg_data OUT NOCOPY VARCHAR2
938 ,p_selv_tbl IN selv_tbl_type) IS
939 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
940
941 BEGIN
942 Okl_Sel_Pvt.lock_row(
943 p_api_version => p_api_version
944 ,p_init_msg_list => p_init_msg_list
945 ,x_return_status => x_return_status
946 ,x_msg_count => x_msg_count
947 ,x_msg_data => x_msg_data
948 ,p_selv_tbl => p_selv_tbl
949 );
950 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
951 RAISE G_EXCEPTION_HALT_VALIDATION;
952 ELSE
953 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
954 l_return_status := x_return_status;
955 END IF;
956 END IF;
957 EXCEPTION
958 WHEN G_EXCEPTION_HALT_VALIDATION THEN
959 NULL;
960 WHEN OTHERS THEN
961 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
962 ,p_msg_name => g_unexpected_error
963 ,p_token1 => g_sqlcode_token
964 ,p_token1_value => SQLCODE
965 ,p_token2 => g_sqlerrm_token
966 ,p_token2_value => SQLERRM);
967 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
968 END lock_stream_elements;
969
970 PROCEDURE update_stream_elements(
971 p_api_version IN NUMBER
972 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
973 ,x_return_status OUT NOCOPY VARCHAR2
974 ,x_msg_count OUT NOCOPY NUMBER
975 ,x_msg_data OUT NOCOPY VARCHAR2
976 ,p_selv_rec IN selv_rec_type
977 ,x_selv_rec OUT NOCOPY selv_rec_type) IS
978 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
979
980 BEGIN
981 Okl_Sel_Pvt.update_row(
982 p_api_version => p_api_version
983 ,p_init_msg_list => p_init_msg_list
984 ,x_return_status => x_return_status
985 ,x_msg_count => x_msg_count
986 ,x_msg_data => x_msg_data
987 ,p_selv_rec => p_selv_rec
988 ,x_selv_rec => x_selv_rec
989 );
990 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
991 RAISE G_EXCEPTION_HALT_VALIDATION;
992 ELSE
993 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
994 l_return_status := x_return_status;
995 END IF;
996 END IF;
997 EXCEPTION
998 WHEN G_EXCEPTION_HALT_VALIDATION THEN
999 NULL;
1000 WHEN OTHERS THEN
1001 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
1002 ,p_msg_name => g_unexpected_error
1003 ,p_token1 => g_sqlcode_token
1004 ,p_token1_value => SQLCODE
1005 ,p_token2 => g_sqlerrm_token
1006 ,p_token2_value => SQLERRM);
1007 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1008 END update_stream_elements;
1009
1010 PROCEDURE update_stream_elements(
1011 p_api_version IN NUMBER
1012 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1013 ,x_return_status OUT NOCOPY VARCHAR2
1014 ,x_msg_count OUT NOCOPY NUMBER
1015 ,x_msg_data OUT NOCOPY VARCHAR2
1016 ,p_selv_tbl IN selv_tbl_type
1017 ,x_selv_tbl OUT NOCOPY selv_tbl_type) IS
1018 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1019
1020 BEGIN
1021 Okl_Sel_Pvt.update_row(
1022 p_api_version => p_api_version
1023 ,p_init_msg_list => p_init_msg_list
1024 ,x_return_status => x_return_status
1025 ,x_msg_count => x_msg_count
1026 ,x_msg_data => x_msg_data
1027 ,p_selv_tbl => p_selv_tbl
1028 ,x_selv_tbl => x_selv_tbl
1029 );
1030 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1031 RAISE G_EXCEPTION_HALT_VALIDATION;
1032 ELSE
1033 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1034 l_return_status := x_return_status;
1035 END IF;
1036 END IF;
1037 EXCEPTION
1038 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1039 NULL;
1040 WHEN OTHERS THEN
1041 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
1042 ,p_msg_name => g_unexpected_error
1043 ,p_token1 => g_sqlcode_token
1044 ,p_token1_value => SQLCODE
1045 ,p_token2 => g_sqlerrm_token
1046 ,p_token2_value => SQLERRM);
1047 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1048 END update_stream_elements;
1049
1050 --Put custom code for cascade delete by developer
1051 PROCEDURE delete_stream_elements(
1052 p_api_version IN NUMBER
1053 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1054 ,x_return_status OUT NOCOPY VARCHAR2
1055 ,x_msg_count OUT NOCOPY NUMBER
1056 ,x_msg_data OUT NOCOPY VARCHAR2
1057 ,p_selv_rec IN selv_rec_type) IS
1058 i NUMBER :=0;
1059 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1060 BEGIN
1061 Okl_Sel_Pvt.delete_row(
1062 p_api_version => p_api_version
1063 ,p_init_msg_list => p_init_msg_list
1064 ,x_return_status => x_return_status
1065 ,x_msg_count => x_msg_count
1066 ,x_msg_data => x_msg_data
1067 ,p_selv_rec => p_selv_rec);
1068 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1069 RAISE G_EXCEPTION_HALT_VALIDATION;
1070 ELSE
1071 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1072 l_return_status := x_return_status;
1073 END IF;
1074 END IF;
1075 EXCEPTION
1076 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1077 NULL;
1078 WHEN OTHERS THEN
1079 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
1080 ,p_msg_name => g_unexpected_error
1081 ,p_token1 => g_sqlcode_token
1082 ,p_token1_value => SQLCODE
1083 ,p_token2 => g_sqlerrm_token
1084 ,p_token2_value => SQLERRM);
1085 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1086 END delete_stream_elements;
1087
1088 PROCEDURE delete_stream_elements(
1089 p_api_version IN NUMBER
1090 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1091 ,x_return_status OUT NOCOPY VARCHAR2
1092 ,x_msg_count OUT NOCOPY NUMBER
1093 ,x_msg_data OUT NOCOPY VARCHAR2
1094 ,p_selv_tbl IN selv_tbl_type) IS
1095
1096 i NUMBER :=0;
1097 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1098 BEGIN
1099 --Initialize the return status
1100 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1101 IF (p_selv_tbl.COUNT > 0) THEN
1102 i := p_selv_tbl.FIRST;
1103 LOOP
1104 delete_stream_elements(
1105 p_api_version => p_api_version
1106 ,p_init_msg_list => p_init_msg_list
1107 ,x_return_status => x_return_status
1108 ,x_msg_count => x_msg_count
1109 ,x_msg_data => x_msg_data
1110 ,p_selv_rec => p_selv_tbl(i));
1111 EXIT WHEN (i = p_selv_tbl.LAST);
1112 i := p_selv_tbl.NEXT(i);
1113 END LOOP;
1114 END IF;
1115 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1116 RAISE G_EXCEPTION_HALT_VALIDATION;
1117 ELSE
1118 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1119 l_return_status := x_return_status;
1120 END IF;
1121 END IF;
1122 EXCEPTION
1123 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1124 NULL;
1125 WHEN OTHERS THEN
1126 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
1127 ,p_msg_name => g_unexpected_error
1128 ,p_token1 => g_sqlcode_token
1129 ,p_token1_value => SQLCODE
1130 ,p_token2 => g_sqlerrm_token
1131 ,p_token2_value => SQLERRM);
1132 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1133 END delete_stream_elements;
1134
1135 PROCEDURE validate_stream_elements(
1136 p_api_version IN NUMBER
1137 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1138 ,x_return_status OUT NOCOPY VARCHAR2
1139 ,x_msg_count OUT NOCOPY NUMBER
1140 ,x_msg_data OUT NOCOPY VARCHAR2
1141 ,p_selv_rec IN selv_rec_type) IS
1142 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1143 BEGIN
1144 Okl_Sel_Pvt.validate_row(
1145 p_api_version => p_api_version
1146 ,p_init_msg_list => p_init_msg_list
1147 ,x_return_status => x_return_status
1148 ,x_msg_count => x_msg_count
1149 ,x_msg_data => x_msg_data
1150 ,p_selv_rec => p_selv_rec
1151 );
1152 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1153 RAISE G_EXCEPTION_HALT_VALIDATION;
1154 ELSE
1155 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1156 l_return_status := x_return_status;
1157 END IF;
1158 END IF;
1159 EXCEPTION
1160 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1161 NULL;
1162 WHEN OTHERS THEN
1163 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
1164 ,p_msg_name => g_unexpected_error
1165 ,p_token1 => g_sqlcode_token
1166 ,p_token1_value => SQLCODE
1167 ,p_token2 => g_sqlerrm_token
1168 ,p_token2_value => SQLERRM);
1169 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1170 END validate_stream_elements;
1171
1172 PROCEDURE validate_stream_elements(
1173 p_api_version IN NUMBER
1174 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
1175 ,x_return_status OUT NOCOPY VARCHAR2
1176 ,x_msg_count OUT NOCOPY NUMBER
1177 ,x_msg_data OUT NOCOPY VARCHAR2
1178 ,p_selv_tbl IN selv_tbl_type) IS
1179 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1180 BEGIN
1181 Okl_Sel_Pvt.validate_row(
1182 p_api_version => p_api_version
1183 ,p_init_msg_list => p_init_msg_list
1184 ,x_return_status => x_return_status
1185 ,x_msg_count => x_msg_count
1186 ,x_msg_data => x_msg_data
1187 ,p_selv_tbl => p_selv_tbl
1188 );
1189 IF x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
1190 RAISE G_EXCEPTION_HALT_VALIDATION;
1191 ELSE
1192 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
1193 l_return_status := x_return_status;
1194 END IF;
1195 END IF;
1196 EXCEPTION
1197 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1198 NULL;
1199 WHEN OTHERS THEN
1200 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
1201 ,p_msg_name => g_unexpected_error
1202 ,p_token1 => g_sqlcode_token
1203 ,p_token1_value => SQLCODE
1204 ,p_token2 => g_sqlerrm_token
1205 ,p_token2_value => SQLERRM);
1206 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1207 END validate_stream_elements;
1208
1209 PROCEDURE create_version(
1210 p_api_version IN NUMBER,
1211 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2,
1215 p_khr_id IN NUMBER,
1216 p_major_version IN NUMBER) IS
1217
1218
1219 BEGIN
1220
1221 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1222
1223
1224 INSERT INTO OKL_STREAMS_H
1225 (
1226 major_version,
1227 ID,
1228 TRANSACTION_NUMBER,
1229 OBJECT_VERSION_NUMBER,
1230 SGN_CODE,
1231 SAY_CODE,
1232 STY_ID,
1233 KLE_ID,
1234 KHR_ID,
1235 ACTIVE_YN,
1236 PURPOSE_CODE,
1237 DATE_CURRENT,
1238 DATE_WORKING,
1239 DATE_HISTORY,
1240 COMMENTS,
1241 CREATED_BY,
1242 CREATION_DATE,
1243 LAST_UPDATED_BY,
1244 LAST_UPDATE_DATE,
1245 PROGRAM_ID,
1246 REQUEST_ID,
1247 PROGRAM_APPLICATION_ID,
1248 PROGRAM_UPDATE_DATE,
1249 LAST_UPDATE_LOGIN ,
1250 STM_ID,
1251 SOURCE_ID,
1252 SOURCE_TABLE,
1253 --HKPATEL changed for bug 4212626
1254 TRX_ID,
1255 LINK_HIST_STREAM_ID
1256
1257 )
1258 SELECT
1259 p_major_version,
1260 ID,
1261 TRANSACTION_NUMBER,
1262 OBJECT_VERSION_NUMBER,
1263 SGN_CODE,
1264 SAY_CODE,
1265 STY_ID,
1266 KLE_ID,
1267 KHR_ID,
1268 ACTIVE_YN,
1269 PURPOSE_CODE,
1270 DATE_CURRENT,
1271 DATE_WORKING,
1272 DATE_HISTORY,
1273 COMMENTS,
1274 CREATED_BY,
1275 CREATION_DATE,
1276 LAST_UPDATED_BY,
1277 LAST_UPDATE_DATE,
1278 PROGRAM_ID,
1279 REQUEST_ID,
1280 PROGRAM_APPLICATION_ID,
1281 PROGRAM_UPDATE_DATE,
1282 LAST_UPDATE_LOGIN ,
1283 STM_ID,
1284 SOURCE_ID,
1285 SOURCE_TABLE,
1286 --HKPATEL changed for bug 4212626
1287 TRX_ID,
1288 LINK_HIST_STREAM_ID
1289
1290 FROM OKL_STREAMS
1291 WHERE
1292 say_code = 'CURR' and
1293 khr_id = p_khr_id;
1294
1295
1296 -- x_return_status := l_return_status;
1297
1298 EXCEPTION
1299 -- other appropriate handlers
1300 WHEN OTHERS THEN
1301 -- store SQL error message on message stack
1302 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
1303 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
1304 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
1305 p_token1_value => sqlcode,
1306 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
1307 p_token2_value => sqlerrm);
1308
1309 -- notify UNEXPECTED error
1310 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1311
1312
1313 END create_version;
1314
1315 PROCEDURE create_strm_element_version(
1316 p_api_version IN NUMBER,
1317 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1318 x_return_status OUT NOCOPY VARCHAR2,
1319 x_msg_count OUT NOCOPY NUMBER,
1320 x_msg_data OUT NOCOPY VARCHAR2,
1321 p_khr_id IN NUMBER,
1322 p_major_version IN NUMBER) IS
1323
1324 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
1325
1326 BEGIN
1327
1328
1329 INSERT INTO OKL_STRM_ELEMENTS_H
1330 (
1331 MAJOR_VERSION,
1332 ID ,
1333 SE_LINE_NUMBER ,
1334 DATE_BILLED ,
1335 STM_ID ,
1336 OBJECT_VERSION_NUMBER ,
1337 STREAM_ELEMENT_DATE ,
1338 AMOUNT ,
1339 COMMENTS ,
1340 ACCRUED_YN ,
1341 PROGRAM_ID ,
1342 REQUEST_ID,
1343 PROGRAM_APPLICATION_ID,
1344 PROGRAM_UPDATE_DATE ,
1345 CREATED_BY ,
1346 CREATION_DATE ,
1347 LAST_UPDATED_BY ,
1348 LAST_UPDATE_DATE ,
1349 LAST_UPDATE_LOGIN ,
1350 SEL_ID ,
1351 SOURCE_ID ,
1352 SOURCE_TABLE,
1353 --HKPATEL changed for bug 4212626
1354 BILL_ADJ_FLAG,
1355 ACCRUAL_ADJ_FLAG
1356
1357 )
1358 SELECT
1359 p_major_version,
1360 ID ,
1361 SE_LINE_NUMBER ,
1362 DATE_BILLED ,
1363 STM_ID ,
1364 OBJECT_VERSION_NUMBER ,
1365 STREAM_ELEMENT_DATE ,
1366 AMOUNT ,
1367 COMMENTS ,
1368 ACCRUED_YN ,
1369 PROGRAM_ID ,
1370 REQUEST_ID,
1371 PROGRAM_APPLICATION_ID,
1372 PROGRAM_UPDATE_DATE ,
1373 CREATED_BY ,
1374 CREATION_DATE ,
1375 LAST_UPDATED_BY ,
1376 LAST_UPDATE_DATE ,
1377 LAST_UPDATE_LOGIN ,
1378 SEL_ID ,
1379 SOURCE_ID,
1380 SOURCE_TABLE,
1381 --HKPATEL changed for bug 4212626
1382 BILL_ADJ_FLAG,
1383 ACCRUAL_ADJ_FLAG
1384
1385 FROM OKL_STRM_ELEMENTS
1386 WHERE stm_id in (Select id from OKL_STREAMS where khr_id = p_khr_id and say_code ='CURR');
1387
1388 EXCEPTION
1389 -- other appropriate handlers
1390 WHEN OTHERS THEN
1391 -- store SQL error message on message stack
1392 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
1393 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
1394 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
1395 p_token1_value => sqlcode,
1396 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
1397 p_token2_value => sqlerrm);
1398
1399 -- notify UNEXPECTED error
1400 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1401
1402 END create_strm_element_version;
1403
1404 PROCEDURE version_stream(
1405 p_api_version IN NUMBER,
1406 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1407 x_return_status OUT NOCOPY VARCHAR2,
1408 x_msg_count OUT NOCOPY NUMBER,
1409 x_msg_data OUT NOCOPY VARCHAR2,
1410 p_khr_id IN NUMBER,
1411 p_major_version IN NUMBER) IS
1412
1413 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
1414
1415 Cursor l_valid_contract_csr(l_khr_id NUMBER) Is
1416 select 'x'
1417 from OKL_K_HEADERS
1418 where id = l_khr_id;
1419
1420 Cursor l_strm_csr(l_khr_id NUMBER, l_major_version NUMBER) Is
1421 select 'x'
1422 from OKL_STREAMS_H
1423 where khr_id = l_khr_id and major_version = l_major_version ;
1424
1425 l_strm_val varchar2(1);
1426 l_cntrct_val varchar2(1);
1427 BEGIN
1428 OPEN l_valid_contract_csr(p_khr_id);
1429 FETCH l_valid_contract_csr into l_cntrct_val;
1430
1431
1432 IF l_valid_contract_csr%NOTFOUND THEN
1433 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1434 p_msg_name => 'OKL_INVALID_CONTRACT_ID',
1435 p_token1 => 'CONT_ID',
1436 p_token1_value => to_char(p_khr_id));
1437
1438 l_return_status := OKC_API.G_RET_STS_ERROR;
1439 RAISE G_EXCEPTION_HALT_VALIDATION;
1440
1441 ELSE
1442 null;
1443
1444
1445 END IF;
1446 CLOSE l_valid_contract_csr;
1447
1448 OPEN l_strm_csr(p_khr_id , p_major_version );
1449 FETCH l_strm_csr into l_strm_val;
1450
1451 IF l_strm_csr%NOTFOUND THEN
1452 null;
1453 ELSE
1454
1455 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1456 p_msg_name => 'OKL_UNIQUE_CONTRACT_VERSION',
1457 p_token1 => 'CONT_VERSION',
1458 p_token1_value => p_major_version);
1459
1460 l_return_status := OKC_API.G_RET_STS_ERROR;
1461 RAISE G_EXCEPTION_HALT_VALIDATION;
1462
1463 END IF;
1464 CLOSE l_strm_csr;
1465
1466
1467 create_version(
1468 p_api_version,
1469 p_init_msg_list,
1470 x_return_status,
1471 x_msg_count,
1472 x_msg_data,
1473 p_khr_id ,
1474 p_major_version);
1475
1476
1477
1478
1479 create_strm_element_version(
1480 p_api_version,
1481 p_init_msg_list,
1482 x_return_status,
1483 x_msg_count,
1484 x_msg_data,
1485 p_khr_id ,
1486 p_major_version);
1487
1488
1489
1490 EXCEPTION
1491 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1492 NULL;
1493 -- other appropriate handlers
1494 WHEN OTHERS THEN
1495 -- store SQL error message on message stack
1496 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
1497 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
1498 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
1499 p_token1_value => sqlcode,
1500 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
1501 p_token2_value => sqlerrm);
1502
1503 -- notify UNEXPECTED error
1504 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1505
1506
1507
1508
1509 END version_stream;
1510
1511 END Okl_Streams_Pvt;