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