[Home] [Help]
PACKAGE BODY: APPS.OKL_STY_PVT
Source
1 PACKAGE BODY OKL_STY_PVT AS
2 /* $Header: OKLSSTYB.pls 120.28 2008/01/30 00:39:37 gkadarka noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 l_temp NUMBER := 0;
8 BEGIN
9 RETURN(okc_p_util.raw_to_number(sys_guid()));
10 END get_seq_id;
11
12 ---------------------------------------------------------------------------
13 -- PROCEDURE qc
14 ---------------------------------------------------------------------------
15 PROCEDURE qc IS
16 BEGIN
17 null;
18 END qc;
19
20 ---------------------------------------------------------------------------
21 -- PROCEDURE change_version
22 ---------------------------------------------------------------------------
23 PROCEDURE change_version IS
24 BEGIN
25 null;
26 END change_version;
27
28 ---------------------------------------------------------------------------
29 -- PROCEDURE api_copy
30 ---------------------------------------------------------------------------
31 PROCEDURE api_copy IS
32 BEGIN
33 null;
34 END api_copy;
35
36 ---------------------------------------------------------------------------
37 -- PROCEDURE add_language
38 ---------------------------------------------------------------------------
39 PROCEDURE add_language IS
40 BEGIN
41 DELETE FROM OKL_STRM_TYPE_TL T
42 WHERE NOT EXISTS (
43 SELECT NULL
44 FROM OKL_STRM_TYPE_B B --fixed bug 3321017 by kmotepal
45 WHERE B.ID = T.ID
46 );
47
48 UPDATE OKL_STRM_TYPE_TL T SET (
49 NAME,
50 DESCRIPTION) = (SELECT
51 B.NAME,
52 B.DESCRIPTION
53 FROM OKL_STRM_TYPE_TL B
54 WHERE B.ID = T.ID
55 AND B.LANGUAGE = T.SOURCE_LANG)
56 WHERE (
57 T.ID,
58 T.LANGUAGE)
59 IN (SELECT
60 SUBT.ID,
61 SUBT.LANGUAGE
62 FROM OKL_STRM_TYPE_TL SUBB, OKL_STRM_TYPE_TL SUBT
63 WHERE SUBB.ID = SUBT.ID
64 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
65 AND (SUBB.NAME <> SUBT.NAME
66 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
67 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
68 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
69 ));
70
71 INSERT INTO OKL_STRM_TYPE_TL (
72 ID,
73 LANGUAGE,
74 SOURCE_LANG,
75 SFWT_FLAG,
76 NAME,
77 DESCRIPTION,
78 CREATED_BY,
79 CREATION_DATE,
80 LAST_UPDATED_BY,
81 LAST_UPDATE_DATE,
82 LAST_UPDATE_LOGIN,
83 -- Added by RGOOTY for ER 3935682: Start
84 SHORT_DESCRIPTION
85 -- Added by RGOOTY for ER 3935682: End
86 )
87 SELECT
88 B.ID,
89 L.LANGUAGE_CODE,
90 B.SOURCE_LANG,
91 B.SFWT_FLAG,
92 B.NAME,
93 B.DESCRIPTION,
94 B.CREATED_BY,
95 B.CREATION_DATE,
96 B.LAST_UPDATED_BY,
97 B.LAST_UPDATE_DATE,
98 B.LAST_UPDATE_LOGIN,
99 -- Added by RGOOTY for ER 3935682: Start
100 B.SHORT_DESCRIPTION
101 -- Added by RGOOTY for ER 3935682: End
102 FROM OKL_STRM_TYPE_TL B, FND_LANGUAGES L
103 WHERE L.INSTALLED_FLAG IN ('I', 'B')
104 AND B.LANGUAGE = USERENV('LANG')
105 AND NOT EXISTS(
106 SELECT NULL
107 FROM OKL_STRM_TYPE_TL T
108 WHERE T.ID = B.ID
109 AND T.LANGUAGE = L.LANGUAGE_CODE
110 );
111
112 END add_language;
113
114 ---------------------------------------------------------------------------
115 -- FUNCTION get_rec for: OKL_STRM_TYPE_B
116 ---------------------------------------------------------------------------
117 FUNCTION get_rec (
118 p_sty_rec IN sty_rec_type,
119 x_no_data_found OUT NOCOPY BOOLEAN
120 ) RETURN sty_rec_type IS
121 CURSOR okl_strm_type_b_pk_csr (p_id IN NUMBER) IS
122 SELECT
123 VERSION,
124 CODE,
125 CUSTOMIZATION_LEVEL,
126 STREAM_TYPE_SCOPE,
127 OBJECT_VERSION_NUMBER,
128 ACCRUAL_YN,
129 TAXABLE_DEFAULT_YN,
130 STREAM_TYPE_CLASS,
131 -- hkpatel 04/15/2003
132 STREAM_TYPE_SUBCLASS,
133 --
134 START_DATE,
135 END_DATE,
136 BILLABLE_YN,
137 CAPITALIZE_YN,
138 PERIODIC_YN,
139 FUNDABLE_YN,
140 -- mvasudev , 05/13/2002
141 ALLOCATION_FACTOR,
142 --
143 ATTRIBUTE_CATEGORY,
144 ATTRIBUTE1,
145 ATTRIBUTE2,
146 ATTRIBUTE3,
147 ATTRIBUTE4,
148 ATTRIBUTE5,
149 ATTRIBUTE6,
150 ATTRIBUTE7,
151 ATTRIBUTE8,
152 ATTRIBUTE9,
153 ATTRIBUTE10,
154 ATTRIBUTE11,
155 ATTRIBUTE12,
156 ATTRIBUTE13,
157 ATTRIBUTE14,
158 ATTRIBUTE15,
159 CREATED_BY,
160 CREATION_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATE_LOGIN,
164 STREAM_TYPE_PURPOSE,
165 CONTINGENCY,
166 -- Added by SNANDIKO for Bug 6744584 Start
167 CONTINGENCY_ID
168 -- Added by SNANDIKO for Bug 6744584 End
169 FROM OKL_STRM_TYPE_B
170 WHERE OKL_STRM_TYPE_B.id = p_id;
171 l_okl_strm_type_b_pk okl_strm_type_b_pk_csr%ROWTYPE;
172 l_sty_rec sty_rec_type;
173 BEGIN
174 x_no_data_found := TRUE;
175 -- Get current database values
176 OPEN okl_strm_type_b_pk_csr (p_sty_rec.id);
177 FETCH okl_strm_type_b_pk_csr INTO
178 l_sty_rec.VERSION,
179 l_sty_rec.CODE,
180 l_sty_rec.CUSTOMIZATION_LEVEL,
181 l_sty_rec.STREAM_TYPE_SCOPE,
182 l_sty_rec.OBJECT_VERSION_NUMBER,
183 l_sty_rec.ACCRUAL_YN,
184 l_sty_rec.TAXABLE_DEFAULT_YN,
185 l_sty_rec.STREAM_TYPE_CLASS,
186 -- hkpatel 04/15/2003
187 l_sty_rec.STREAM_TYPE_SUBCLASS,
188 --
189 l_sty_rec.START_DATE,
190 l_sty_rec.END_DATE,
191 l_sty_rec.BILLABLE_YN,
192 l_sty_rec.CAPITALIZE_YN,
193 l_sty_rec.PERIODIC_YN,
194 l_sty_rec.FUNDABLE_YN,
195 -- mvasudev , 05/13/2002
196 l_sty_rec.ALLOCATION_FACTOR,
197 --
198 l_sty_rec.ATTRIBUTE_CATEGORY,
199 l_sty_rec.ATTRIBUTE1,
200 l_sty_rec.ATTRIBUTE2,
201 l_sty_rec.ATTRIBUTE3,
202 l_sty_rec.ATTRIBUTE4,
203 l_sty_rec.ATTRIBUTE5,
204 l_sty_rec.ATTRIBUTE6,
205 l_sty_rec.ATTRIBUTE7,
206 l_sty_rec.ATTRIBUTE8,
207 l_sty_rec.ATTRIBUTE9,
208 l_sty_rec.ATTRIBUTE10,
209 l_sty_rec.ATTRIBUTE11,
210 l_sty_rec.ATTRIBUTE12,
211 l_sty_rec.ATTRIBUTE13,
212 l_sty_rec.ATTRIBUTE14,
213 l_sty_rec.ATTRIBUTE15,
214 l_sty_rec.CREATED_BY,
215 l_sty_rec.CREATION_DATE,
216 l_sty_rec.LAST_UPDATED_BY,
217 l_sty_rec.LAST_UPDATE_DATE,
218 l_sty_rec.LAST_UPDATE_LOGIN,
219 -- Added by RGOOTY for ER 3935682: Start
220 l_sty_rec.STREAM_TYPE_PURPOSE,
221 l_sty_rec.CONTINGENCY,
222 -- Added by RGOOTY for ER 3935682: End
223
224 -- Added by SNANDIKO for Bug 6744584 Start
225 l_sty_rec.CONTINGENCY_ID;
226 -- Added by SNANDIKO for Bug 6744584 End
227 x_no_data_found := okl_strm_type_b_pk_csr%NOTFOUND;
228 CLOSE okl_strm_type_b_pk_csr;
229 RETURN(l_sty_rec);
230 END get_rec;
231
232 FUNCTION get_rec (
233 p_sty_rec IN sty_rec_type
234 ) RETURN sty_rec_type IS
235 l_row_notfound BOOLEAN := TRUE;
236 BEGIN
237 RETURN(get_rec(p_sty_rec, l_row_notfound));
238 END get_rec;
239 ---------------------------------------------------------------------------
240 -- FUNCTION get_rec for: OKL_STRM_TYPE_TL
241 ---------------------------------------------------------------------------
242 FUNCTION get_rec (
243 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
244 x_no_data_found OUT NOCOPY BOOLEAN
245 ) RETURN okl_strm_type_tl_rec_type IS
246 CURSOR okl_strm_type_tl_pk_csr (p_id IN NUMBER,
247 p_language IN VARCHAR2) IS
248 SELECT
249 ID,
250 LANGUAGE,
251 SOURCE_LANG,
252 SFWT_FLAG,
253 NAME,
254 DESCRIPTION,
255 CREATED_BY,
256 CREATION_DATE,
257 LAST_UPDATED_BY,
258 LAST_UPDATE_DATE,
259 LAST_UPDATE_LOGIN,
260 -- Added by RGOOTY for ER 3935682: Start
261 SHORT_DESCRIPTION
262 -- Added by RGOOTY for ER 3935682: Start
263 FROM Okl_Strm_Type_Tl
264 WHERE okl_strm_type_tl.id = p_id
265 AND okl_strm_type_tl.language = p_language;
266 l_okl_strm_type_tl_pk okl_strm_type_tl_pk_csr%ROWTYPE;
267 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
268 BEGIN
269 x_no_data_found := TRUE;
270 -- Get current database values
271 OPEN okl_strm_type_tl_pk_csr (p_okl_strm_type_tl_rec.id,
272 p_okl_strm_type_tl_rec.language);
273 FETCH okl_strm_type_tl_pk_csr INTO
274 l_okl_strm_type_tl_rec.ID,
275 l_okl_strm_type_tl_rec.LANGUAGE,
276 l_okl_strm_type_tl_rec.SOURCE_LANG,
277 l_okl_strm_type_tl_rec.SFWT_FLAG,
278 l_okl_strm_type_tl_rec.NAME,
279 l_okl_strm_type_tl_rec.DESCRIPTION,
280 l_okl_strm_type_tl_rec.CREATED_BY,
281 l_okl_strm_type_tl_rec.CREATION_DATE,
282 l_okl_strm_type_tl_rec.LAST_UPDATED_BY,
283 l_okl_strm_type_tl_rec.LAST_UPDATE_DATE,
284 l_okl_strm_type_tl_rec.LAST_UPDATE_LOGIN,
285 -- Added by RGOOTY for ER 3935682: Start
286 l_okl_strm_type_tl_rec.SHORT_DESCRIPTION;
287 -- Added by RGOOTY for ER 3935682: End
288 x_no_data_found := okl_strm_type_tl_pk_csr%NOTFOUND;
289 CLOSE okl_strm_type_tl_pk_csr;
290 RETURN(l_okl_strm_type_tl_rec);
291 END get_rec;
292
293 FUNCTION get_rec (
294 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type
295 ) RETURN okl_strm_type_tl_rec_type IS
296 l_row_notfound BOOLEAN := TRUE;
297 BEGIN
298 RETURN(get_rec(p_okl_strm_type_tl_rec, l_row_notfound));
299 END get_rec;
300 ---------------------------------------------------------------------------
301 -- FUNCTION get_rec for: OKL_STRM_TYPE_V
302 ---------------------------------------------------------------------------
303 FUNCTION get_rec (
304 p_styv_rec IN styv_rec_type,
305 x_no_data_found OUT NOCOPY BOOLEAN
306 ) RETURN styv_rec_type IS
307 CURSOR okl_styv_pk_csr (p_id IN NUMBER) IS
308 SELECT
309 ID,
310 NAME,
311 VERSION,
312 OBJECT_VERSION_NUMBER,
313 CODE,
314 SFWT_FLAG,
315 STREAM_TYPE_SCOPE,
316 DESCRIPTION,
317 START_DATE,
318 END_DATE,
319 BILLABLE_YN,
320 CAPITALIZE_YN,
321 PERIODIC_YN,
322 FUNDABLE_YN,
323 -- mvasudev , 05/13/2002
324 ALLOCATION_FACTOR,
325 --
326 TAXABLE_DEFAULT_YN,
327 CUSTOMIZATION_LEVEL,
328 STREAM_TYPE_CLASS,
329 -- hkpatel 04/15/2003
330 STREAM_TYPE_SUBCLASS,
331 --
332 ACCRUAL_YN,
333 ATTRIBUTE_CATEGORY,
334 ATTRIBUTE1,
335 ATTRIBUTE2,
336 ATTRIBUTE3,
337 ATTRIBUTE4,
338 ATTRIBUTE5,
339 ATTRIBUTE6,
340 ATTRIBUTE7,
341 ATTRIBUTE8,
342 ATTRIBUTE9,
343 ATTRIBUTE10,
344 ATTRIBUTE11,
345 ATTRIBUTE12,
346 ATTRIBUTE13,
347 ATTRIBUTE14,
348 ATTRIBUTE15,
349 CREATED_BY,
350 CREATION_DATE,
351 LAST_UPDATED_BY,
352 LAST_UPDATE_DATE,
353 LAST_UPDATE_LOGIN,
354 -- Added by RGOOTY for ER 3935682: Start
355 STREAM_TYPE_PURPOSE,
356 CONTINGENCY,
357 SHORT_DESCRIPTION,
358 -- Added by RGOOTY for ER 3935682: End
359
360 -- Added by SNANDIKO for Bug 6744584 Start
361 CONTINGENCY_ID
362 -- Added by SNANDIKO for Bug 6744584 End
363 FROM OKL_STRM_TYPE_V
364 WHERE OKL_STRM_TYPE_V.id = p_id;
365 l_okl_styv_pk okl_styv_pk_csr%ROWTYPE;
366 l_styv_rec styv_rec_type;
367 BEGIN
368 x_no_data_found := TRUE;
369 -- Get current database values
370 OPEN okl_styv_pk_csr (p_styv_rec.id);
371 FETCH okl_styv_pk_csr INTO
372 l_styv_rec.ID,
373 l_styv_rec.NAME,
374 l_styv_rec.VERSION,
375 l_styv_rec.OBJECT_VERSION_NUMBER,
376 l_styv_rec.CODE,
377 l_styv_rec.SFWT_FLAG,
378 l_styv_rec.STREAM_TYPE_SCOPE,
379 l_styv_rec.DESCRIPTION,
380 l_styv_rec.START_DATE,
381 l_styv_rec.END_DATE,
382 l_styv_rec.BILLABLE_YN,
383 l_styv_rec.CAPITALIZE_YN,
384 l_styv_rec.PERIODIC_YN,
385 l_styv_rec.FUNDABLE_YN,
386 -- mvasudev , 05/13/2002
387 l_styv_rec.ALLOCATION_FACTOR,
388 --
389 l_styv_rec.TAXABLE_DEFAULT_YN,
390 l_styv_rec.CUSTOMIZATION_LEVEL,
391 l_styv_rec.STREAM_TYPE_CLASS,
392 -- hkpatel 04/15/2003
393 l_styv_rec.STREAM_TYPE_SUBCLASS,
394 --
395 l_styv_rec.ACCRUAL_YN,
396 l_styv_rec.ATTRIBUTE_CATEGORY,
397 l_styv_rec.ATTRIBUTE1,
398 l_styv_rec.ATTRIBUTE2,
399 l_styv_rec.ATTRIBUTE3,
400 l_styv_rec.ATTRIBUTE4,
401 l_styv_rec.ATTRIBUTE5,
402 l_styv_rec.ATTRIBUTE6,
403 l_styv_rec.ATTRIBUTE7,
404 l_styv_rec.ATTRIBUTE8,
405 l_styv_rec.ATTRIBUTE9,
406 l_styv_rec.ATTRIBUTE10,
407 l_styv_rec.ATTRIBUTE11,
408 l_styv_rec.ATTRIBUTE12,
409 l_styv_rec.ATTRIBUTE13,
410 l_styv_rec.ATTRIBUTE14,
411 l_styv_rec.ATTRIBUTE15,
412 l_styv_rec.CREATED_BY,
413 l_styv_rec.CREATION_DATE,
414 l_styv_rec.LAST_UPDATED_BY,
415 l_styv_rec.LAST_UPDATE_DATE,
416 l_styv_rec.LAST_UPDATE_LOGIN,
417 -- Added by RGOOTY for ER 3935682: Start
418 l_styv_rec.STREAM_TYPE_PURPOSE,
419 l_styv_rec.CONTINGENCY,
420 l_styv_rec.SHORT_DESCRIPTION,
421 -- Added by RGOOTY for ER 3935682: End
422
423 -- Added by SNANDIKO for Bug 6744584 Start
424 l_styv_rec.CONTINGENCY_ID;
425 -- Added by SNANDIKO for Bug 6744584 End
426
427 x_no_data_found := okl_styv_pk_csr%NOTFOUND;
428 CLOSE okl_styv_pk_csr;
429 RETURN(l_styv_rec);
430 END get_rec;
431
432 FUNCTION get_rec (
433 p_styv_rec IN styv_rec_type
434 ) RETURN styv_rec_type IS
435 l_row_notfound BOOLEAN := TRUE;
436 BEGIN
437 RETURN(get_rec(p_styv_rec, l_row_notfound));
438 END get_rec;
439
440 -----------------------------------------------------
441 -- FUNCTION null_out_defaults for: OKL_STRM_TYPE_V --
442 -----------------------------------------------------
443 FUNCTION null_out_defaults (
444 p_styv_rec IN styv_rec_type
445 ) RETURN styv_rec_type IS
446 l_styv_rec styv_rec_type := p_styv_rec;
447 BEGIN
448 IF (l_styv_rec.name = OKC_API.G_MISS_CHAR) THEN
449 l_styv_rec.name := NULL;
450 END IF;
451 IF (l_styv_rec.version = OKC_API.G_MISS_CHAR) THEN
452 l_styv_rec.version := NULL;
453 END IF;
454 IF (l_styv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
455 l_styv_rec.object_version_number := NULL;
456 END IF;
457 IF (l_styv_rec.code = OKC_API.G_MISS_CHAR) THEN
458 l_styv_rec.code := NULL;
459 END IF;
460 IF (l_styv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
461 l_styv_rec.sfwt_flag := NULL;
462 END IF;
463 IF (l_styv_rec.stream_type_scope = OKC_API.G_MISS_CHAR) THEN
464 -- l_styv_rec.stream_type_scope := NULL;
465 l_styv_rec.stream_type_scope := 'BOTH'; -- Modified by RGOOTY for bug 4036080
466 END IF;
467 IF (l_styv_rec.description = OKC_API.G_MISS_CHAR) THEN
468 l_styv_rec.description := NULL;
469 END IF;
470 IF (l_styv_rec.start_date = OKC_API.G_MISS_DATE) THEN
471 l_styv_rec.start_date := NULL;
472 END IF;
473 IF (l_styv_rec.end_date = OKC_API.G_MISS_DATE) THEN
474 l_styv_rec.end_date := NULL;
475 END IF;
476 IF (l_styv_rec.billable_yn = OKC_API.G_MISS_CHAR) THEN
477 l_styv_rec.billable_yn := NULL;
478 END IF;
479 IF (l_styv_rec.capitalize_yn = OKC_API.G_MISS_CHAR) THEN
480 l_styv_rec.capitalize_yn := NULL;
481 END IF;
482 IF (l_styv_rec.periodic_yn = OKC_API.G_MISS_CHAR) THEN
483 l_styv_rec.periodic_yn := NULL;
484 END IF;
485 -- mvasudev , 05/13/2002
486 IF (l_styv_rec.fundable_yn = OKC_API.G_MISS_CHAR) THEN
487 l_styv_rec.fundable_yn := NULL;
488 END IF;
489 IF (l_styv_rec.ALLOCATION_FACTOR = OKC_API.G_MISS_CHAR) THEN
490 l_styv_rec.ALLOCATION_FACTOR := NULL;
491 END IF;
492 --
493 IF (l_styv_rec.taxable_default_yn = OKC_API.G_MISS_CHAR) THEN
494 l_styv_rec.taxable_default_yn := NULL;
495 END IF;
496 IF (l_styv_rec.customization_level = OKC_API.G_MISS_CHAR) THEN
497 -- l_styv_rec.customization_level := NULL;
498 l_styv_rec.customization_level := 'S'; -- Modified by RGOOTY for ER 3935682
499 END IF;
500 IF (l_styv_rec.stream_type_class = OKC_API.G_MISS_CHAR) THEN
501 -- l_styv_rec.stream_type_class := NULL;
502 l_styv_rec.stream_type_class := 'GENERAL'; -- Modified by RGOOTY for ER 3935682
503 END IF;
504 -- hkpatel 04/15/2003
505 IF (l_styv_rec.stream_type_subclass = OKC_API.G_MISS_CHAR) THEN
506 l_styv_rec.stream_type_subclass := NULL;
507 END IF;
508 --
509 IF (l_styv_rec.accrual_yn = OKC_API.G_MISS_CHAR) THEN
510 l_styv_rec.accrual_yn := NULL;
511 END IF;
512 IF (l_styv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
513 l_styv_rec.attribute_category := NULL;
514 END IF;
515 IF (l_styv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
516 l_styv_rec.attribute1 := NULL;
517 END IF;
518 IF (l_styv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
519 l_styv_rec.attribute2 := NULL;
520 END IF;
521 IF (l_styv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
522 l_styv_rec.attribute3 := NULL;
523 END IF;
524 IF (l_styv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
525 l_styv_rec.attribute4 := NULL;
526 END IF;
527 IF (l_styv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
528 l_styv_rec.attribute5 := NULL;
529 END IF;
530 IF (l_styv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
531 l_styv_rec.attribute6 := NULL;
532 END IF;
533 IF (l_styv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
534 l_styv_rec.attribute7 := NULL;
535 END IF;
536 IF (l_styv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
537 l_styv_rec.attribute8 := NULL;
538 END IF;
539 IF (l_styv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
540 l_styv_rec.attribute9 := NULL;
541 END IF;
542 IF (l_styv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
543 l_styv_rec.attribute10 := NULL;
544 END IF;
545 IF (l_styv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
546 l_styv_rec.attribute11 := NULL;
547 END IF;
548 IF (l_styv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
549 l_styv_rec.attribute12 := NULL;
550 END IF;
551 IF (l_styv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
552 l_styv_rec.attribute13 := NULL;
553 END IF;
554 IF (l_styv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
555 l_styv_rec.attribute14 := NULL;
556 END IF;
557 IF (l_styv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
558 l_styv_rec.attribute15 := NULL;
559 END IF;
560 IF (l_styv_rec.created_by = OKC_API.G_MISS_NUM) THEN
561 l_styv_rec.created_by := NULL;
562 END IF;
563 IF (l_styv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
564 l_styv_rec.creation_date := NULL;
565 END IF;
566 IF (l_styv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
567 l_styv_rec.last_updated_by := NULL;
568 END IF;
569 IF (l_styv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
570 l_styv_rec.last_update_date := NULL;
571 END IF;
572 IF (l_styv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
573 l_styv_rec.last_update_login := NULL;
574 END IF;
575 -- Added by RGOOTY for ER 3935682: Start
576 IF (l_styv_rec.stream_type_purpose = OKC_API.G_MISS_CHAR) THEN
577 l_styv_rec.stream_type_purpose := NULL;
578 END IF;
579 IF (l_styv_rec.contingency = OKC_API.G_MISS_CHAR) THEN
580 l_styv_rec.contingency := NULL;
581 END IF;
582 IF (l_styv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
583 l_styv_rec.short_description := NULL;
584 END IF;
585 -- Added by RGOOTY for ER 3935682: End
586
587 -- Added by SNANDIKO for Bug 6744584 Start
588 IF (l_styv_rec.contingency_id = OKC_API.G_MISS_NUM) THEN
589 l_styv_rec.contingency_id := NULL;
590 END IF;
591 -- Added by SNANDIKO for Bug 6744584 End
592 RETURN(l_styv_rec);
593 END null_out_defaults;
594
595 ---------------------------------------------------------------------------
596 -- PROCEDURE Validate_Id
597 ---------------------------------------------------------------------------
598 -- Start of comments
599 -- Author : mvasudev
600 -- Procedure Name : Validate_Id
601 -- Description :
602 -- Business Rules :
603 -- Parameters :
604 -- Version : 1.0
605 -- End of comments
606 ---------------------------------------------------------------------------
607 PROCEDURE Validate_Id(p_styv_rec IN styv_rec_type,
608 x_return_status OUT NOCOPY VARCHAR2)
609
610 IS
611
612 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
613
614 BEGIN
615 -- initialize return status
616 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
617 -- check for data before processing
618 IF (p_styv_rec.id = OKC_API.G_MISS_NUM) OR
619 (p_styv_rec.id IS NULL) THEN
620
621 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
622 ,p_msg_name => g_required_value
623 ,p_token1 => g_col_name_token
624 ,p_token1_value => 'id');
625 x_return_status := Okc_Api.G_RET_STS_ERROR;
626 RAISE G_EXCEPTION_HALT_VALIDATION;
627 END IF;
628
629 EXCEPTION
630 WHEN G_EXCEPTION_HALT_VALIDATION THEN
631 -- no processing necessary; validation can continue
632 -- with the next column
633 NULL;
634
635 WHEN OTHERS THEN
636 -- store SQL error message on message stack for caller
637 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
638 p_msg_name => G_OKL_UNEXPECTED_ERROR,
639 p_token1 => G_OKL_SQLCODE_TOKEN,
640 p_token1_value => SQLCODE,
641 p_token2 => G_OKL_SQLERRM_TOKEN,
642 p_token2_value => SQLERRM);
643
644 -- notify caller of an UNEXPECTED error
645 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
646
647 END Validate_Id;
648
649 ---------------------------------------------------------------------------
650 -- PROCEDURE Validate_Name
651 --------------------------------------------------------------------------
652 -- Start of comments
653 --Author : mvasudev
654 -- Procedure Name : Validate_Name
655 -- Description :
656 -- Business Rules :
657 -- Parameters :
658 -- Version : 1.0
659 -- End of comments
660 ---------------------------------------------------------------------------
661
662 Procedure Validate_Name( p_styv_rec IN styv_rec_type,
663 x_return_status OUT NOCOPY VARCHAR2)
664 IS
665 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
666 BEGIN
667 x_return_status := OKC_API.G_RET_STS_SUCCESS;
668
669 -- check for data before processing
670 IF (p_styv_rec.name IS NULL) OR
671 (p_styv_rec.name = Okc_Api.G_MISS_CHAR) THEN
672 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
673 ,p_msg_name => g_required_value
674 ,p_token1 => g_col_name_token
675 ,p_token1_value => 'name');
676 x_return_status := Okc_Api.G_RET_STS_ERROR;
677 RAISE G_EXCEPTION_HALT_VALIDATION;
678 END IF;
679
680 EXCEPTION
681 WHEN G_EXCEPTION_HALT_VALIDATION THEN
682 -- no processing necessary; validation can continue
683 -- with the next column
684 NULL;
685 WHEN OTHERS THEN
686 -- store SQL error message on message stack for caller
687 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
688 p_msg_name => G_OKL_UNEXPECTED_ERROR,
689 p_token1 => G_OKL_SQLCODE_TOKEN,
690 p_token1_value => SQLCODE,
691 p_token2 => G_OKL_SQLERRM_TOKEN,
692 p_token2_value => SQLERRM);
693
694 -- notify caller of an UNEXPECTED error
695 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
696 END Validate_Name;
697
698 ---------------------------------------------------------------------------
699 -- PROCEDURE Validate_Version
700 --------------------------------------------------------------------------
701 -- Start of comments
702 --Author : mvasudev
703 -- Procedure Name : Validate_Version
704 -- Description :
705 -- Business Rules :
706 -- Parameters :
707 -- Version : 1.0
708 -- End of comments
709 ---------------------------------------------------------------------------
710
711 Procedure Validate_Version( p_styv_rec IN styv_rec_type,
712 x_return_status OUT NOCOPY VARCHAR2)
713 IS
714 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
715 BEGIN
716 x_return_status := OKC_API.G_RET_STS_SUCCESS;
717
718 -- check for data before processing
719 IF (p_styv_rec.Version IS NULL) OR
720 (p_styv_rec.Version = Okc_Api.G_MISS_CHAR) THEN
721 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
722 ,p_msg_name => g_required_value
723 ,p_token1 => g_col_name_token
724 ,p_token1_value => 'Version');
725 x_return_status := Okc_Api.G_RET_STS_ERROR;
726 RAISE G_EXCEPTION_HALT_VALIDATION;
727 END IF;
728
729 EXCEPTION
730 WHEN G_EXCEPTION_HALT_VALIDATION THEN
731 -- no processing necessary; validation can continue
732 -- with the next column
733 NULL;
734 WHEN OTHERS THEN
735 -- store SQL error message on message stack for caller
736 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
737 p_msg_name => G_OKL_UNEXPECTED_ERROR,
738 p_token1 => G_OKL_SQLCODE_TOKEN,
739 p_token1_value => SQLCODE,
740 p_token2 => G_OKL_SQLERRM_TOKEN,
741 p_token2_value => SQLERRM);
742 -- notify caller of an UNEXPECTED error
743 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
744 END Validate_Version;
745
746
747 ---------------------------------------------------------------------------
748 -- PROCEDURE Validate_Object_Version_Number
749 ---------------------------------------------------------------------------
750 -- Start of comments
751 -- Author :Ajay
752 -- Procedure Name : Validate_Object_Version_Number
753 -- Description :
754 -- Business Rules :
755 -- Parameters :
756 -- Version : 1.0
757 -- End of comments
758 -----------------------------------------------------------------------------
759 Procedure Validate_Object_Version_Number( p_styv_rec IN styv_rec_type,
760 x_return_status OUT NOCOPY VARCHAR2)
761
762 IS
763
764 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
765
766 BEGIN
767 -- initialize return status
768 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
769 -- check for data before processing
770 IF (p_styv_rec.object_version_number IS NULL) OR
771 (p_styv_rec.object_version_number = Okc_Api.G_MISS_NUM) THEN
772 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
773 ,p_msg_name => g_required_value
774 ,p_token1 => g_col_name_token
775 ,p_token1_value => 'Object_Version_Number');
776 x_return_status := Okc_Api.G_RET_STS_ERROR;
777 RAISE G_EXCEPTION_HALT_VALIDATION;
778 END IF;
779
780 EXCEPTION
781 WHEN G_EXCEPTION_HALT_VALIDATION THEN
782 -- no processing necessary; validation can continue
783 -- with the next column
784 NULL;
785
786 WHEN OTHERS THEN
787 -- store SQL error message on message stack for caller
788 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
789 p_msg_name => G_OKL_UNEXPECTED_ERROR,
790 p_token1 => G_OKL_SQLCODE_TOKEN,
791 p_token1_value => SQLCODE,
792 p_token2 => G_OKL_SQLERRM_TOKEN,
793 p_token2_value => SQLERRM);
794
795 -- notify caller of an UNEXPECTED error
796 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
797
798 END Validate_Object_Version_Number;
799
800
801 ---------------------------------------------------------------------------
802 -- PROCEDURE Validate_Code
803 --------------------------------------------------------------------------
804 -- Start of comments
805 --Author : mvasudev
806 -- Procedure Name : Validate_Code
807 -- Description :
808 -- Business Rules :
809 -- Parameters :
810 -- Version : 1.0
811 -- End of comments
812 ---------------------------------------------------------------------------
813
814 Procedure Validate_Code( p_styv_rec IN styv_rec_type,
815 x_return_status OUT NOCOPY VARCHAR2)
816 IS
817 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
818 BEGIN
819 x_return_status := OKC_API.G_RET_STS_SUCCESS;
820
821 -- check for data before processing
822 IF (p_styv_rec.Code IS NULL) OR
823 (p_styv_rec.Code = Okc_Api.G_MISS_CHAR) THEN
824 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
825 ,p_msg_name => g_required_value
826 ,p_token1 => g_col_name_token
827 ,p_token1_value => 'Code');
828 x_return_status := Okc_Api.G_RET_STS_ERROR;
829 RAISE G_EXCEPTION_HALT_VALIDATION;
830 END IF;
831
832 EXCEPTION
833 WHEN G_EXCEPTION_HALT_VALIDATION THEN
834 -- no processing necessary; validation can continue
835 -- with the next column
836 NULL;
837 WHEN OTHERS THEN
838 -- store SQL error message on message stack for caller
839 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
840 p_msg_name => G_OKL_UNEXPECTED_ERROR,
841 p_token1 => G_OKL_SQLCODE_TOKEN,
842 p_token1_value => SQLCODE,
843 p_token2 => G_OKL_SQLERRM_TOKEN,
844 p_token2_value => SQLERRM);
845
846 -- notify caller of an UNEXPECTED error
847 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
848 END Validate_Code;
849
850 ---------------------------------------------------------------------------
851 -- PROCEDURE Validate_Stream_Type_Scope
852 --------------------------------------------------------------------------
853 -- Start of comments
854 -- Author : mvasudev
855 -- Procedure Name : Validate_Stream_Type_Scope
856 -- Description :
857 -- Business Rules :
858 -- Parameters :
859 -- Version : 1.0
860 -- End of comments
861 ---------------------------------------------------------------------------
862
863 PROCEDURE Validate_Stream_Type_Scope(
864 p_styv_rec IN styv_rec_type,
865 x_return_status OUT NOCOPY VARCHAR2
866 ) IS
867
868 l_found VARCHAR2(1);
869
870 BEGIN
871 -- initialize return status
872 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
873
874 -- check for data before processing
875 IF (p_styv_rec.stream_type_scope IS NULL) OR
876 (p_styv_rec.stream_type_scope = Okc_Api.G_MISS_CHAR) THEN
877 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
878 ,p_msg_name => g_required_value
879 ,p_token1 => g_col_name_token
880 ,p_token1_value => 'Stream_Type_Scope');
881 x_return_status := Okc_Api.G_RET_STS_ERROR;
882 RAISE G_EXCEPTION_HALT_VALIDATION;
883
884 ELSE
885 --Check if Stream_Type_Scope exists in the fnd_common_lookups or not
886 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_STREAM_TYPE_SCOPE',
887 p_lookup_code => p_styv_rec.stream_type_scope);
888
889
890 IF (l_found <> OKL_API.G_TRUE ) THEN
891 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_Scope');
892 x_return_status := Okc_Api.G_RET_STS_ERROR;
893 -- raise the exception as there's no matching foreign key value
894 RAISE G_EXCEPTION_HALT_VALIDATION;
895 END IF;
896
897 END IF;
898
899 EXCEPTION
900 WHEN G_EXCEPTION_HALT_VALIDATION THEN
901 -- no processing necessary; validation can continue
902 -- with the next column
903 NULL;
904
905 WHEN OTHERS THEN
906 -- store SQL error message on message stack for caller
907 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
908 p_msg_name => G_OKL_UNEXPECTED_ERROR,
909 p_token1 => G_OKL_SQLCODE_TOKEN,
910 p_token1_value => SQLCODE,
911 p_token2 => G_OKL_SQLERRM_TOKEN,
912 p_token2_value => SQLERRM);
913 -- notify caller of an UNEXPECTED error
914 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
915
916 END Validate_Stream_Type_Scope;
917
918 ---------------------------------------------------------------------------
919 -- PROCEDURE Validate_Start_Date
920 --------------------------------------------------------------------------
921 -- Start of comments
922 -- Procedure Name : Validate_Start_Date
923 -- Description :
924 -- Business Rules :
925 -- Parameters :
926 -- Version : 1.0
927 -- End of comments
928 ---------------------------------------------------------------------------
929
930 Procedure Validate_Start_Date(p_styv_rec IN styv_rec_type,
931 x_return_status OUT NOCOPY VARCHAR2) IS
932
933 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
934
935 BEGIN
936 -- initialize return status
937 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
938 -- check for data before processing
939
940 IF p_styv_rec.start_date = OKC_API.G_MISS_DATE OR
941 (p_styv_rec.start_date) IS NULL THEN
942
943 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
944 ,p_msg_name => g_required_value
945 ,p_token1 => g_col_name_token
946 ,p_token1_value => 'Start_Date');
947
948 x_return_status := Okc_Api.G_RET_STS_ERROR;
949 RAISE G_EXCEPTION_HALT_VALIDATION;
950 END IF;
951
952 EXCEPTION
953 WHEN G_EXCEPTION_HALT_VALIDATION THEN
954 -- no processing necessary; validation can continue
955 -- with the next column
956 NULL;
957
958 WHEN OTHERS THEN
959 -- store SQL error message on message stack for caller
960 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
961 p_msg_name => G_OKL_UNEXPECTED_ERROR,
962 p_token1 => G_OKL_SQLCODE_TOKEN,
963 p_token1_value => SQLCODE,
964 p_token2 => G_OKL_SQLERRM_TOKEN,
965 p_token2_value => SQLERRM);
966
967
968 -- notify caller of an UNEXPECTED error
969 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
970
971 END Validate_Start_Date;
972
973 ---------------------------------------------------------------------------
974 -- PROCEDURE Validate_Enddate
975 --------------------------------------------------------------------------
976 -- Start of comments
977 -- Procedure Name : Validate_Enddate
978 -- Description :
979 -- Business Rules :
980 -- Parameters :
981 -- Version : 1.0
982 -- End of comments
983 ---------------------------------------------------------------------------
984
985
986 PROCEDURE Validate_End_Date(p_styv_rec IN styv_rec_type,
987 x_return_status OUT NOCOPY VARCHAR2)
988 IS
989 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
990 l_start_date DATE := Okc_Api.G_MISS_DATE;
991 BEGIN
992 -- initialize return status
993 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
994 l_start_date := p_styv_rec.start_date;
995 -- check for data before processing
996 IF p_styv_rec.end_date <> OKC_API.G_MISS_DATE
997 OR p_styv_rec.end_date IS NOT NULL
998 THEN
999 IF p_styv_rec.end_date < l_start_date
1000 THEN
1001 Okc_Api.SET_MESSAGE( p_app_name => G_OKC_APP,
1002 p_msg_name => g_invalid_value,
1003 p_token1 => g_col_name_token,
1004 p_token1_value => 'end_date' );
1005 x_return_status := OKC_API.G_RET_STS_ERROR;
1006 RAISE G_EXCEPTION_HALT_VALIDATION;
1007 END IF;
1008 END IF;
1009
1010 EXCEPTION
1011 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1012 -- no processing required ; validation can continue
1013 -- with the next column
1014 NULL;
1015
1016 WHEN OTHERS THEN
1017 -- store SQL error message on message stack for caller
1018 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1019 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1020 p_token1 => G_OKL_SQLCODE_TOKEN,
1021 p_token1_value => SQLCODE,
1022 p_token2 => G_OKL_SQLERRM_TOKEN,
1023 p_token2_value => SQLERRM);
1024 -- notify caller of an UNEXPECTED error
1025
1026 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1027 END Validate_End_Date;
1028
1029 ---------------------------------------------------------------------------
1030 -- PROCEDURE Validate_Billable_Yn
1031 --------------------------------------------------------------------------
1032 -- Start of comments
1033 --Author : mvasudev
1034 -- Procedure Name : Validate_Billable_Yn
1035 -- Description :
1036 -- Business Rules :
1037 -- Parameters :
1038 -- Version : 1.0
1039 -- End of comments
1040 ---------------------------------------------------------------------------
1041
1042 Procedure Validate_Billable_Yn( p_styv_rec IN styv_rec_type,
1043 x_return_status OUT NOCOPY VARCHAR2)
1044
1045 IS
1046 l_found VARCHAR2(1);
1047
1048 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1049 BEGIN
1050 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1051
1052 -- check for data before processing
1053 IF (p_styv_rec.billable_yn IS NULL) OR
1054 (p_styv_rec.billable_yn = Okc_Api.G_MISS_CHAR) THEN
1055 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1056 ,p_msg_name => g_required_value
1057 ,p_token1 => g_col_name_token
1058 ,p_token1_value => 'Billable_YN');
1059 x_return_status := Okc_Api.G_RET_STS_ERROR;
1060 RAISE G_EXCEPTION_HALT_VALIDATION;
1061 ELSE
1062 --Check if billable_yn exists in the fnd_common_lookups or not
1063 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'YES_NO',
1064 p_lookup_code => p_styv_rec.billable_yn,
1065 p_app_id => 0,
1066 p_view_app_id => 0);
1067
1068
1069 IF (l_found <> OKL_API.G_TRUE ) THEN
1070 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Billable_YN');
1071 x_return_status := Okc_Api.G_RET_STS_ERROR;
1072 -- raise the exception as there's no matching foreign key value
1073 RAISE G_EXCEPTION_HALT_VALIDATION;
1074 END IF;
1075
1076 --VTHIRUVA Bug 4273953..21-Apr-05..removed purpose based validations
1077 --on billable,taxable and capitalize fields..
1078
1079 END IF;
1080
1081 EXCEPTION
1082 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1083 -- no processing necessary; validation can continue
1084 -- with the next column
1085 NULL;
1086 WHEN OTHERS THEN
1087 -- store SQL error message on message stack for caller
1088 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1089 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1090 p_token1 => G_OKL_SQLCODE_TOKEN,
1091 p_token1_value => SQLCODE,
1092 p_token2 => G_OKL_SQLERRM_TOKEN,
1093 p_token2_value => SQLERRM);
1094
1095 -- notify caller of an UNEXPECTED error
1096 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1097 END Validate_Billable_Yn;
1098
1099 ---------------------------------------------------------------------------
1100 -- PROCEDURE Validate_Taxable_Default_Yn
1101 --------------------------------------------------------------------------
1102 -- Start of comments
1103 --Author : mvasudev
1104 -- Procedure Name : Validate_Taxable_Default_Yn
1105 -- Description :
1106 -- Business Rules :
1107 -- Parameters :
1108 -- Version : 1.0
1109 -- End of comments
1110 ---------------------------------------------------------------------------
1111
1112 Procedure Validate_Taxable_Default_Yn( p_styv_rec IN styv_rec_type,
1113 x_return_status OUT NOCOPY VARCHAR2)
1114
1115 IS
1116
1117 l_found VARCHAR2(1);
1118 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1119
1120 BEGIN
1121 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1122
1123 -- check for data before processing
1124 IF (p_styv_rec.taxable_default_yn IS NULL) OR
1125 (p_styv_rec.taxable_default_yn = Okc_Api.G_MISS_CHAR) THEN
1126 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1127 ,p_msg_name => g_required_value
1128 ,p_token1 => g_col_name_token
1129 ,p_token1_value => 'Taxable_Default_YN');
1130 x_return_status := Okc_Api.G_RET_STS_ERROR;
1131 RAISE G_EXCEPTION_HALT_VALIDATION;
1132 ELSE
1133 --Check if Taxable_Default_YN exists in the fnd_common_lookups or not
1134 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'YES_NO',
1135 p_lookup_code => p_styv_rec.taxable_default_yn,
1136 p_app_id => 0,
1137 p_view_app_id => 0);
1138
1139
1140 IF (l_found <> OKL_API.G_TRUE ) THEN
1141 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Taxable_Default_YN');
1142 x_return_status := Okc_Api.G_RET_STS_ERROR;
1143 -- raise the exception as there's no matching foreign key value
1144 RAISE G_EXCEPTION_HALT_VALIDATION;
1145 END IF;
1146 END IF;
1147
1148 EXCEPTION
1149 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1150 -- no processing necessary; validation can continue
1151 -- with the next column
1152 NULL;
1153 WHEN OTHERS THEN
1154 -- store SQL error message on message stack for caller
1155 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1156 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1157 p_token1 => G_OKL_SQLCODE_TOKEN,
1158 p_token1_value => SQLCODE,
1159 p_token2 => G_OKL_SQLERRM_TOKEN,
1160 p_token2_value => SQLERRM);
1161 -- notify caller of an UNEXPECTED error
1162 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1163 END Validate_Taxable_Default_YN;
1164
1165 ---------------------------------------------------------------------------
1166 -- PROCEDURE Validate_Customization Level
1167 --------------------------------------------------------------------------
1168 -- Start of comments
1169 -- Author : mvasudev
1170 -- Procedure Name : Validate_Customization_Level
1171 -- Description :
1172 -- Business Rules :
1173 -- Parameters :
1174 -- Version : 1.0
1175 -- End of comments
1176 ---------------------------------------------------------------------------
1177
1178 PROCEDURE Validate_Customization_Level(
1179 p_styv_rec IN styv_rec_type,
1180 x_return_status OUT NOCOPY VARCHAR2
1181 ) IS
1182 l_found VARCHAR2(1);
1183
1184 --mvasudev, 01/31/2002
1185 -- This constant to alleviate differences between DB Column Name and Display Name
1186 l_display_col_name CONSTANT VARCHAR2(30) := 'Access Level';
1187
1188 BEGIN
1189 -- initialize return status
1190 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1191
1192 -- check for data before processing
1193 IF (p_styv_rec.customization_level IS NULL) OR
1194 (p_styv_rec.customization_level = Okc_Api.G_MISS_CHAR) THEN
1195 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1196 ,p_msg_name => g_required_value
1197 ,p_token1 => g_col_name_token
1198 ,p_token1_value => l_display_col_name);
1199 x_return_status := Okc_Api.G_RET_STS_ERROR;
1200 RAISE G_EXCEPTION_HALT_VALIDATION;
1201
1202 ELSE
1203 --Check if customization_level exists in the fnd_common_lookups or not
1204 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_STY_CUSTOMIZATION_LEVEL',
1205 p_lookup_code => p_styv_rec.customization_level);
1206
1207
1208 IF (l_found <> OKL_API.G_TRUE ) THEN
1209 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,l_display_col_name);
1210 x_return_status := Okc_Api.G_RET_STS_ERROR;
1211 -- raise the exception as there's no matching foreign key value
1212 RAISE G_EXCEPTION_HALT_VALIDATION;
1213 END IF;
1214
1215 END IF;
1216
1217 EXCEPTION
1218 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1219 -- no processing necessary; validation can continue
1220 -- with the next column
1221 NULL;
1222
1223 WHEN OTHERS THEN
1224 -- store SQL error message on message stack for caller
1225 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1226 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1227 p_token1 => G_OKL_SQLCODE_TOKEN,
1228 p_token1_value => SQLCODE,
1229 p_token2 => G_OKL_SQLERRM_TOKEN,
1230 p_token2_value => SQLERRM);
1231 -- notify caller of an UNEXPECTED error
1232 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1233
1234 END Validate_Customization_Level;
1235
1236 ---------------------------------------------------------------------------
1237 -- PROCEDURE Validate_Stream_Type_class
1238 --------------------------------------------------------------------------
1239 -- Start of comments
1240 -- Author : akjain
1241 -- Procedure Name : Validate_Stream_Type_Class
1242 -- Description :
1243 -- Business Rules :
1244 -- Parameters :
1245 -- Version : 1.0
1246 -- End of comments
1247 ---------------------------------------------------------------------------
1248
1249 PROCEDURE Validate_Stream_Type_Class(
1250 p_styv_rec IN styv_rec_type,
1251 x_return_status OUT NOCOPY VARCHAR2
1252 ) IS
1253
1254 l_found VARCHAR2(1);
1255
1256 BEGIN
1257 -- initialize return status
1258 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1259
1260 -- check for data before processing
1261 IF (p_styv_rec.stream_type_class IS NULL) OR
1262 (p_styv_rec.stream_type_class = Okc_Api.G_MISS_CHAR) THEN
1263 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1264 ,p_msg_name => g_required_value
1265 ,p_token1 => g_col_name_token
1266 ,p_token1_value => 'Stream_Type_Class');
1267 x_return_status := Okc_Api.G_RET_STS_ERROR;
1268 RAISE G_EXCEPTION_HALT_VALIDATION;
1269
1270 ELSE
1271 --Check if stream_type_class exists in the fnd_common_lookups or not
1272 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_STREAM_TYPE_CLASS',
1273 p_lookup_code => p_styv_rec.stream_type_class);
1274
1275 IF (l_found <> OKL_API.G_TRUE ) THEN
1276 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_Class');
1277 x_return_status := Okc_Api.G_RET_STS_ERROR;
1278 -- raise the exception as there's no matching foreign key value
1279 RAISE G_EXCEPTION_HALT_VALIDATION;
1280 END IF;
1281 END IF;
1282
1283 EXCEPTION
1284 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1285 -- no processing necessary; validation can continue
1286 -- with the next column
1287 NULL;
1288
1289 WHEN OTHERS THEN
1290 -- store SQL error message on message stack for caller
1291 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1292 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1293 p_token1 => G_OKL_SQLCODE_TOKEN,
1294 p_token1_value => SQLCODE,
1295 p_token2 => G_OKL_SQLERRM_TOKEN,
1296 p_token2_value => SQLERRM);
1297 -- notify caller of an UNEXPECTED error
1298 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1299
1300 END Validate_Stream_Type_Class;
1301
1302 ---------------------------------------------------------------------------
1303 -- PROCEDURE Validate_Stream_Type_SubClass
1304 --------------------------------------------------------------------------
1305 -- Start of comments
1306 -- Author : hkpatel
1307 -- Procedure Name : Validate_Stream_Type_SubClass
1308 -- Description :
1309 -- Business Rules :
1310 -- Parameters :
1311 -- Version : 1.0
1312 -- End of comments
1313 ---------------------------------------------------------------------------
1314
1315 PROCEDURE Validate_Stream_Type_SubClass(
1316 p_styv_rec IN styv_rec_type,
1317 x_return_status OUT NOCOPY VARCHAR2
1318 ) IS
1319
1320 l_found VARCHAR2(1);
1321
1322 BEGIN
1323 -- initialize return status
1324 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1325
1326 -- check for data before processing
1327
1328 IF (p_styv_rec.stream_type_subclass IS NOT NULL) AND
1329 (p_styv_rec.stream_type_subclass <> Okc_Api.G_MISS_CHAR) THEN
1330 /* Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1331 ,p_msg_name => g_required_value
1332 ,p_token1 => g_col_name_token
1333 ,p_token1_value => 'Stream_Type_Class');
1334 x_return_status := Okc_Api.G_RET_STS_ERROR;
1335 RAISE G_EXCEPTION_HALT_VALIDATION;
1336
1337 ELSE
1338 */
1339 --Check if stream_type_subclass exists in the fnd_common_lookups or not
1340 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_STREAM_TYPE_SUBCLASS',
1341 p_lookup_code => p_styv_rec.stream_type_subclass);
1342
1343 IF (l_found <> OKL_API.G_TRUE ) THEN
1344 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_SubClass');
1345 x_return_status := Okc_Api.G_RET_STS_ERROR;
1346 -- raise the exception as there's no matching foreign key value
1347 RAISE G_EXCEPTION_HALT_VALIDATION;
1348 END IF;
1349
1350 END IF;
1351
1352 IF ((p_styv_rec.stream_type_purpose = 'ADVANCE_RENT' OR
1353 p_styv_rec.stream_type_purpose = 'RENT' OR
1354 p_styv_rec.stream_type_purpose = 'RENEWAL_RENT') AND
1355 (p_styv_rec.stream_type_subclass IS NULL OR
1356 p_styv_rec.stream_type_subclass <> 'RENT')) THEN
1357 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_SubClass');
1358 x_return_status := Okc_Api.G_RET_STS_ERROR;
1359 -- raise the exception as there's no matching foreign key value
1360 RAISE G_EXCEPTION_HALT_VALIDATION;
1361 END IF;
1362
1363 --Bug # 6740000 ssdeshpa Changed Start
1364 IF (((p_styv_rec.stream_type_purpose = 'INVESTOR_CNTRCT_OBLIGATION_PAY' OR
1365 p_styv_rec.stream_type_purpose = 'INVESTOR_RESIDUAL_PAY' OR
1366 p_styv_rec.stream_type_purpose = 'INVESTOR_LATE_FEE_PAYABLE' OR
1367 p_styv_rec.stream_type_purpose = 'INVESTOR_RENT_BUYBACK' OR
1368 p_styv_rec.stream_type_purpose = 'INVESTOR_RESIDUAL_BUYBACK' OR
1369 p_styv_rec.stream_type_purpose = 'INVESTOR_DISB_ADJUSTMENT' OR
1370 p_styv_rec.stream_type_purpose = 'INVESTOR_EVERGREEN_RENT_PAY' OR
1371 p_styv_rec.stream_type_purpose = 'INVESTOR_LATE_INTEREST_PAY' OR
1372 p_styv_rec.stream_type_purpose = 'INVESTOR_PRINCIPAL_BUYBACK' OR
1373 p_styv_rec.stream_type_purpose = 'INVESTOR_INTEREST_BUYBACK' OR
1374 p_styv_rec.stream_type_purpose = 'INVESTOR_PAYDOWN_BUYBACK') AND
1375 (p_styv_rec.stream_type_subclass IS NULL OR
1376 p_styv_rec.stream_type_subclass <> 'INVESTOR_DISBURSEMENT')) OR
1377 ((p_styv_rec.stream_type_purpose = 'INVESTOR_PRINCIPAL_DISB_BASIS' OR
1378 p_styv_rec.stream_type_purpose = 'INVESTOR_INTEREST_DISB_BASIS' OR
1379 p_styv_rec.stream_type_purpose = 'INVESTOR_PPD_DISB_BASIS') AND
1380
1381 (p_styv_rec.stream_type_subclass IS NULL OR
1382 p_styv_rec.stream_type_subclass <> 'LOAN_PAYMENT'))) THEN
1383 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_SubClass');
1384 x_return_status := Okc_Api.G_RET_STS_ERROR;
1385 -- raise the exception as there's no matching foreign key value
1386 RAISE G_EXCEPTION_HALT_VALIDATION;
1387 END IF;
1388
1389 IF (p_styv_rec.stream_type_purpose = 'RESIDUAL_VALUE' AND
1390 (p_styv_rec.stream_type_subclass IS NULL OR
1391 p_styv_rec.stream_type_subclass <> 'RESIDUAL')) THEN
1392 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_SubClass');
1393 x_return_status := Okc_Api.G_RET_STS_ERROR;
1394 -- raise the exception as there's no matching foreign key value
1395 RAISE G_EXCEPTION_HALT_VALIDATION;
1396 END IF;
1397 --Bug # 6740000 ssdeshpa Changed Start
1398
1399 IF (p_styv_rec.stream_type_purpose <> 'ADVANCE_RENT' AND
1400 p_styv_rec.stream_type_purpose <> 'RENT' AND
1401 p_styv_rec.stream_type_purpose <> 'RESIDUAL_VALUE' AND
1402 p_styv_rec.stream_type_purpose <> 'INVESTOR_CNTRCT_OBLIGATION_PAY' AND
1403 p_styv_rec.stream_type_purpose <> 'INVESTOR_RESIDUAL_PAY' AND
1404 p_styv_rec.stream_type_purpose <> 'INVESTOR_LATE_FEE_PAYABLE' AND
1405 p_styv_rec.stream_type_purpose <> 'INVESTOR_RENT_BUYBACK' AND
1406 p_styv_rec.stream_type_purpose <> 'INVESTOR_RESIDUAL_BUYBACK' AND
1407 p_styv_rec.stream_type_purpose <> 'INVESTOR_DISB_ADJUSTMENT' AND
1408 p_styv_rec.stream_type_purpose <> 'INVESTOR_EVERGREEN_RENT_PAY' AND
1409 p_styv_rec.stream_type_purpose <> 'INVESTOR_LATE_INTEREST_PAY' AND
1410 p_styv_rec.stream_type_purpose <> 'RENEWAL_RENT' AND
1411 p_styv_rec.stream_type_purpose <> 'INVESTOR_PRINCIPAL_BUYBACK' AND
1412 p_styv_rec.stream_type_purpose <> 'INVESTOR_INTEREST_BUYBACK' AND
1413 p_styv_rec.stream_type_purpose <> 'INVESTOR_PAYDOWN_BUYBACK' AND
1414 p_styv_rec.stream_type_purpose <> 'INVESTOR_PRINCIPAL_DISB_BASIS' AND
1415 p_styv_rec.stream_type_purpose <> 'INVESTOR_INTEREST_DISB_BASIS' AND
1416 p_styv_rec.stream_type_purpose <> 'INVESTOR_PPD_DISB_BASIS' AND
1417 p_styv_rec.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT' AND
1418 p_styv_rec.stream_type_purpose = 'PRINCIPAL_PAYMENT' AND
1419 p_styv_rec.stream_type_purpose = 'INTEREST_PAYMENT' AND
1420
1421 p_styv_rec.stream_type_subclass IS NOT NULL) THEN
1422 IF ((p_styv_rec.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT' OR
1423 p_styv_rec.stream_type_purpose = 'PRINCIPAL_PAYMENT' OR
1424 p_styv_rec.stream_type_purpose = 'INTEREST_PAYMENT') AND
1425 (p_styv_rec.stream_type_subclass IS NOT NULL AND
1426 p_styv_rec.stream_type_subclass <>'LOAN_PAYMENT')) THEN
1427
1428 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_SubClass');
1429 x_return_status := Okc_Api.G_RET_STS_ERROR;
1430 -- raise the exception as there's no matching foreign key value
1431 RAISE G_EXCEPTION_HALT_VALIDATION;
1432 ELSIF (p_styv_rec.stream_type_purpose <> 'ADVANCE_RENT' AND
1433 p_styv_rec.stream_type_purpose <> 'RENT' AND
1434 p_styv_rec.stream_type_purpose <> 'RESIDUAL_VALUE' AND
1435 p_styv_rec.stream_type_purpose <> 'INVESTOR_CNTRCT_OBLIGATION_PAY' AND
1436 p_styv_rec.stream_type_purpose <> 'INVESTOR_RESIDUAL_PAY' AND
1437 p_styv_rec.stream_type_purpose <> 'INVESTOR_LATE_FEE_PAYABLE' AND
1438 p_styv_rec.stream_type_purpose <> 'INVESTOR_RENT_BUYBACK' AND
1439 p_styv_rec.stream_type_purpose <> 'INVESTOR_RESIDUAL_BUYBACK' AND
1440 p_styv_rec.stream_type_purpose <> 'INVESTOR_DISB_ADJUSTMENT' AND
1441 p_styv_rec.stream_type_purpose <> 'INVESTOR_EVERGREEN_RENT_PAY' AND
1442 p_styv_rec.stream_type_purpose <> 'INVESTOR_LATE_INTEREST_PAY' AND
1443 p_styv_rec.stream_type_purpose <> 'RENEWAL_RENT' AND
1444 p_styv_rec.stream_type_purpose <> 'INVESTOR_PRINCIPAL_BUYBACK' AND
1445 p_styv_rec.stream_type_purpose <> 'INVESTOR_INTEREST_BUYBACK' AND
1446 p_styv_rec.stream_type_purpose <> 'INVESTOR_PAYDOWN_BUYBACK' AND
1447 p_styv_rec.stream_type_purpose <> 'INVESTOR_PRINCIPAL_DISB_BASIS' AND
1448 p_styv_rec.stream_type_purpose <> 'INVESTOR_INTEREST_DISB_BASIS' AND
1449 p_styv_rec.stream_type_purpose <> 'INVESTOR_PPD_DISB_BASIS' AND
1450 p_styv_rec.stream_type_subclass IS NOT NULL) THEN
1451
1452 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Stream_Type_SubClass');
1453 x_return_status := Okc_Api.G_RET_STS_ERROR;
1454
1455 END IF;
1456
1457 END IF;
1458
1459 EXCEPTION
1460 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1461 -- no processing necessary; validation can continue
1462 -- with the next column
1463 NULL;
1464
1465 WHEN OTHERS THEN
1466 -- store SQL error message on message stack for caller
1467 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1468 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1469 p_token1 => G_OKL_SQLCODE_TOKEN,
1470 p_token1_value => SQLCODE,
1471 p_token2 => G_OKL_SQLERRM_TOKEN,
1472 p_token2_value => SQLERRM);
1473 -- notify caller of an UNEXPECTED error
1474 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1475
1476 END Validate_Stream_Type_SubClass;
1477
1478 ---------------------------------------------------------------------------
1479 -- PROCEDURE Validate_Accrual_Yn
1480 --------------------------------------------------------------------------
1481 -- Start of comments
1482 --Author : mvasudev
1483 -- Procedure Name : Validate_Accrual_Yn
1484 -- Description :
1485 -- Business Rules :
1486
1487 -- Parameters :
1488 -- Version : 1.0
1489 -- End of comments
1490 ---------------------------------------------------------------------------
1491 Procedure Validate_Accrual_Yn( p_styv_rec IN styv_rec_type,
1492 x_return_status OUT NOCOPY VARCHAR2)
1493 IS
1494 l_found VARCHAR2(1);
1495 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1496
1497 -- prasjain - Bug#5762913 - Commented - Start
1498 /*
1499 CURSOR prev_strm_type_csr( p_sty_id OKL_STRM_TYPE_V.ID%TYPE) IS
1500 SELECT ACCRUAL_YN
1501 FROM OKL_STRM_TYPE_V
1502 WHERE ID = p_sty_id;
1503 */
1504 -- prasjain - Bug#5762913 - Commented - End
1505
1506 BEGIN
1507 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1508 -- check for data before processing
1509 IF (p_styv_rec.accrual_yn IS NULL) OR
1510 (p_styv_rec .accrual_yn = Okc_Api.G_MISS_CHAR) THEN
1511 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1512 ,p_msg_name => g_required_value
1513 ,p_token1 => g_col_name_token
1514 ,p_token1_value => 'accrual_yn');
1515 x_return_status := Okc_Api.G_RET_STS_ERROR;
1516 RAISE G_EXCEPTION_HALT_VALIDATION;
1517 ELSE
1518 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type =>
1519 'OKL_STREAM_TYPE_CASH_BASIS',
1520 p_lookup_code => p_styv_rec.accrual_yn);
1521 IF (l_found <> OKL_API.G_TRUE ) THEN
1522 -- Modified by RGOOTY
1523 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'REVENUE_RECOGNITION_BASIS');
1524 x_return_status := Okc_Api.G_RET_STS_ERROR;
1525 -- raise the exception as there's no matching foreign key value
1526 RAISE G_EXCEPTION_HALT_VALIDATION;
1527 END IF;
1528
1529 -- prasjain - Bug#5762913 - Commented - Start
1530 -- Cash Basis related fields on a stream type to be updatable always
1531 -- Decision taken to ensure that customers migrated to OKL.H can enable
1532 -- cash basis on their stream types if they had not used it in earlier
1533 -- codelines
1534 /*
1535 --Modified by RGOOTY
1536 --Bug 4075113: Start
1537 FOR prev_strm_type_rec in prev_strm_type_csr( p_styv_rec.id )
1538 LOOP
1539 -- Stream Types with Accrual_Yn as 'ACRL_WITH_RULE' or 'ACRL_WITHOUT_RULE'
1540 -- cannot be changed to 'CASH_RECEIPT' and vice-versa
1541 IF ( (
1542 ( prev_strm_type_rec.accrual_yn = 'ACRL_WITH_RULE' OR
1543 prev_strm_type_rec.accrual_yn = 'ACRL_WITHOUT_RULE' ) AND
1544 p_styv_rec.accrual_yn = 'CASH_RECEIPT'
1545 )
1546 OR (
1547 ( p_styv_rec.accrual_yn = 'ACRL_WITH_RULE' OR
1548 p_styv_rec.accrual_yn = 'ACRL_WITHOUT_RULE' ) AND
1549 prev_strm_type_rec.accrual_yn = 'CASH_RECEIPT'
1550 )
1551 )
1552 THEN
1553 OKC_API.set_message( G_OKC_APP,
1554 G_INVALID_VALUE,
1555 G_COL_NAME_TOKEN,
1556 'REVENUE_RECOGNITION_BASIS');
1557 x_return_status := Okc_Api.G_RET_STS_ERROR;
1558 RAISE G_EXCEPTION_HALT_VALIDATION;
1559 END IF;
1560 END LOOP;
1561 --Bug 4075113: End
1562 */
1563 -- prasjain - Bug#5762913 - Commented - End
1564
1565
1566 -- Modified by RGOOTY
1567 -- Bug 4137988: Start
1568
1569 IF( p_styv_rec.accrual_yn = 'CASH_RECEIPT' AND
1570 -- Added by SNANDIKO for Bug 6744584 Start
1571 ( p_styv_rec.contingency_id IS NULL OR
1572 p_styv_rec.contingency_id = Okc_Api.G_MISS_NUM ) )
1573 -- Added by SNANDIKO for Bug 6744584 End
1574 THEN
1575 OKC_API.set_message( G_OKC_APP,
1576 G_REQUIRED_VALUE,
1577 G_COL_NAME_TOKEN,
1578 'CONTINGENCY');
1579 x_return_status := Okc_Api.G_RET_STS_ERROR;
1580 RAISE G_EXCEPTION_HALT_VALIDATION;
1581 ELSIF ( ( p_styv_rec.accrual_yn = 'ACRL_WITH_RULE' OR
1582 p_styv_rec.accrual_yn = 'ACRL_WITHOUT_RULE' ) AND
1583 -- Added by SNANDIKO for Bug 6744584 Start
1584 ( p_styv_rec.contingency_id IS NOT NULL OR
1585 p_styv_rec.contingency_id = Okc_Api.G_MISS_NUM ) )
1586 -- Added by SNANDIKO for Bug 6744584 End
1587 THEN
1588 OKC_API.set_message( G_OKC_APP,
1589 G_INVALID_VALUE,
1590 G_COL_NAME_TOKEN,
1591 'CONTINGENCY');
1592 x_return_status := Okc_Api.G_RET_STS_ERROR;
1593 RAISE G_EXCEPTION_HALT_VALIDATION;
1594 END IF;
1595 -- Bug 4137988: End
1596 END IF;
1597 EXCEPTION
1598 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1599 -- no processing necessary; validation can continue
1600 -- with the next column
1601 NULL;
1602 WHEN OTHERS THEN
1603 -- store SQL error message on message stack for caller
1604 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1605 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1606 p_token1 => G_OKL_SQLCODE_TOKEN,
1607 p_token1_value => SQLCODE,
1608 p_token2 => G_OKL_SQLERRM_TOKEN,
1609 p_token2_value => SQLERRM);
1610
1611 -- notify caller of an UNEXPECTED error
1612 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1613 END Validate_Accrual_YN;
1614
1615
1616 ---------------------------------------------------------------------------
1617 -- PROCEDURE Validate_Allocation_Factor
1618 --------------------------------------------------------------------------
1619 -- Start of comments
1620 --Author : mvasudev
1621 -- Procedure Name : Validate_Allocation_Factor
1622 -- Description :
1623 -- Business Rules :
1624
1625 -- Parameters :
1626 -- Version : 1.0
1627 -- End of comments
1628 ---------------------------------------------------------------------------
1629
1630 Procedure Validate_Allocation_Factor( p_styv_rec IN styv_rec_type,
1631 x_return_status OUT NOCOPY VARCHAR2)
1632
1633 IS
1634
1635 l_found VARCHAR2(1);
1636 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1637
1638 BEGIN
1639 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1640
1641 -- check for data before processing
1642 IF (p_styv_rec.Allocation_Factor IS NOT NULL) AND
1643 (p_styv_rec .Allocation_Factor <> Okc_Api.G_MISS_CHAR) THEN
1644 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_STY_ALLOCATION_BASIS',
1645 p_lookup_code => p_styv_rec.Allocation_Factor);
1646
1647 IF (l_found <> OKL_API.G_TRUE ) THEN
1648 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Allocation_Factor');
1649 x_return_status := Okc_Api.G_RET_STS_ERROR;
1650 -- raise the exception as there's no matching foreign key value
1651 RAISE G_EXCEPTION_HALT_VALIDATION;
1652 END IF;
1653
1654 END IF;
1655
1656
1657 EXCEPTION
1658 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1659 -- no processing necessary; validation can continue
1660 -- with the next column
1661 NULL;
1662 WHEN OTHERS THEN
1663 -- store SQL error message on message stack for caller
1664 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1665 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1666 p_token1 => G_OKL_SQLCODE_TOKEN,
1667 p_token1_value => SQLCODE,
1668 p_token2 => G_OKL_SQLERRM_TOKEN,
1669 p_token2_value => SQLERRM);
1670
1671 -- notify caller of an UNEXPECTED error
1672 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1673 END Validate_Allocation_Factor;
1674
1675 ---------------------------------------------------------------------------
1676 -- PROCEDURE validate_stream_type_purpose
1677 --------------------------------------------------------------------------
1678 --Author : RGOOTY
1679 -- Procedure Name : validate_stream_type_purpose
1680 -- Description : Adding this one so as to purpose fetches only from the
1681 -- OKL_STREAM_TYPE_PURPOSE values
1682 -- Business Rules :
1683
1684 -- Parameters :
1685 -- Version : 1.0
1686 ---------------------------------------------------------------------------
1687
1688 Procedure validate_stream_type_purpose( p_styv_rec IN styv_rec_type,
1689 x_return_status OUT NOCOPY VARCHAR2)
1690
1691 IS
1692
1693 l_found VARCHAR2(1);
1694 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1695
1696 BEGIN
1697 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1698
1699 -- check for data before processing
1700 IF (p_styv_rec.stream_type_purpose IS NOT NULL) AND
1701 (p_styv_rec.stream_type_purpose <> Okc_Api.G_MISS_CHAR) THEN
1702 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_STREAM_TYPE_PURPOSE',
1703 p_lookup_code => p_styv_rec.stream_type_purpose);
1704
1705 IF (l_found <> OKL_API.G_TRUE ) THEN
1706 -- Modified by RGOOTY
1707 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'PURPOSE');
1708 x_return_status := Okc_Api.G_RET_STS_ERROR;
1709 -- raise the exception as there's no matching foreign key value
1710 RAISE G_EXCEPTION_HALT_VALIDATION;
1711 END IF;
1712
1713 END IF;
1714
1715 -- Added by kthiruva for Bug 3935682
1716
1717 IF (p_styv_rec.stream_type_purpose IS NULL) OR
1718 (p_styv_rec.stream_type_purpose = Okc_Api.G_MISS_CHAR) THEN
1719 Okc_Api.SET_MESSAGE(p_app_name => G_OKC_APP
1720 ,p_msg_name => g_required_value
1721 ,p_token1 => g_col_name_token
1722 ,p_token1_value => 'stream_type_purpose');
1723 x_return_status := Okc_Api.G_RET_STS_ERROR;
1724 RAISE G_EXCEPTION_HALT_VALIDATION;
1725 END IF;
1726
1727 EXCEPTION
1728 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1729 -- no processing necessary; validation can continue
1730 -- with the next column
1731 NULL;
1732 WHEN OTHERS THEN
1733 -- store SQL error message on message stack for caller
1734 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1735 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1736 p_token1 => G_OKL_SQLCODE_TOKEN,
1737 p_token1_value => SQLCODE,
1738 p_token2 => G_OKL_SQLERRM_TOKEN,
1739 p_token2_value => SQLERRM);
1740
1741 -- notify caller of an UNEXPECTED error
1742 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1743 END validate_stream_type_purpose;
1744 ---------------------------------------------------------------------------
1745 -- FUNCTION Validate_Attributes
1746 ---------------------------------------------------------------------------
1747 -- Start of comments
1748 -- Procedure Name : Validate_Attributes
1749 -- Description :
1750 -- Business Rules :
1751 -- Parameters :
1752 -- Version : 1.0
1753 -- End of comments
1754 ---------------------------------------------------------------------------
1755 Function Validate_Attributes (
1756 p_styv_rec IN styv_rec_type
1757 ) RETURN VARCHAR2 IS
1758
1759 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1760 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1761 BEGIN
1762
1763 -- call each column-level validation
1764
1765 -- Validate_Id
1766 Validate_Id(p_styv_rec , x_return_status);
1767 -- store the highest degree of error
1768 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1769 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1770 -- need to leave
1771 l_return_status := x_return_status;
1772 RAISE G_EXCEPTION_HALT_VALIDATION;
1773 ELSE
1774 -- record that there was an error
1775 l_return_status := x_return_status;
1776 END IF;
1777 END IF;
1778
1779 -- Validate_Name
1780 Validate_Name(p_styv_rec , x_return_status);
1781 -- store the highest degree of error
1782 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1783 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1784 -- need to leave
1785 l_return_status := x_return_status;
1786 RAISE G_EXCEPTION_HALT_VALIDATION;
1787 ELSE
1788 -- record that there was an error
1789 l_return_status := x_return_status;
1790 END IF;
1791
1792 -- Validate_Code only if Name_Validation went throgh fine
1793 -- as we always auto-update CODE := NAME
1794 ELSE
1795 -- Validate_Code
1796 Validate_Code(p_styv_rec, x_return_status);
1797 -- store the highest degree of error
1798 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1799 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1800 -- need to leave
1801 l_return_status := x_return_status;
1802 RAISE G_EXCEPTION_HALT_VALIDATION;
1803 ELSE
1804 -- record that there was an error
1805 l_return_status := x_return_status;
1806 END IF;
1807 END IF;
1808 END IF;
1809
1810 -- Validate_Version
1811 Validate_Version(p_styv_rec , x_return_status);
1812 -- store the highest degree of error
1813 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1814 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1815 -- need to leave
1816 l_return_status := x_return_status;
1817 RAISE G_EXCEPTION_HALT_VALIDATION;
1818 ELSE
1819 -- record that there was an error
1820 l_return_status := x_return_status;
1821 END IF;
1822 END IF;
1823
1824 -- Validate_Object_Version_Number
1825 Validate_Object_Version_Number(p_styv_rec , x_return_status);
1826 -- store the highest degree of error
1827 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1828 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1829 -- need to leave
1830 l_return_status := x_return_status;
1831 RAISE G_EXCEPTION_HALT_VALIDATION;
1832 ELSE
1833 -- record that there was an error
1834 l_return_status := x_return_status;
1835 END IF;
1836 END IF;
1837 -- Commented by RGOOTY for ER 3935682: Start
1838 /*
1839
1840 -- Validate_Stream_Type_Scope
1841 Validate_Stream_Type_Scope(p_styv_rec, x_return_status);
1842 -- store the highest degree of error
1843 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1844 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1845 -- need to leave
1846 l_return_status := x_return_status;
1847 RAISE G_EXCEPTION_HALT_VALIDATION;
1848 ELSE
1849 -- record that there was an error
1850 l_return_status := x_return_status;
1851 END IF;
1852 END IF;
1853 */
1854
1855 -- Validate_Start_Date
1856 Validate_Start_Date(p_styv_rec, x_return_status);
1857 -- store the highest degree of error
1858 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1859 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1860 -- need to leave
1861 l_return_status := x_return_status;
1862 RAISE G_EXCEPTION_HALT_VALIDATION;
1863 ELSE
1864 -- record that there was an error
1865 l_return_status := x_return_status;
1866 END IF;
1867 END IF;
1868
1869 -- Validate_End_Date
1870 Validate_End_Date(p_styv_rec, x_return_status);
1871 -- store the highest degree of error
1872 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1873 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1874 -- need to leave
1875 l_return_status := x_return_status;
1876 RAISE G_EXCEPTION_HALT_VALIDATION;
1877 ELSE
1878 -- record that there was an error
1879 l_return_status := x_return_status;
1880 END IF;
1881 END IF;
1882
1883 -- Validate_Billable_Yn
1884 Validate_Billable_Yn(p_styv_rec, x_return_status);
1885 -- store the highest degree of error
1886 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1887 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1888 -- need to leave
1889 l_return_status := x_return_status;
1890 RAISE G_EXCEPTION_HALT_VALIDATION;
1891 ELSE
1892 -- record that there was an error
1893 l_return_status := x_return_status;
1894 END IF;
1895 END IF;
1896
1897 -- Validate_Taxable_Default_Yn
1898 Validate_Taxable_Default_Yn(p_styv_rec, x_return_status);
1899 -- store the highest degree of error
1900 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1901 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1902 -- need to leave
1903 l_return_status := x_return_status;
1904 RAISE G_EXCEPTION_HALT_VALIDATION;
1905 ELSE
1906 -- record that there was an error
1907 l_return_status := x_return_status;
1908 END IF;
1909 END IF;
1910
1911 -- Commented by RGOOTY for ER 3935682: Start
1912 /*
1913 -- Validate_Customization_Level
1914 Validate_Customization_Level(p_styv_rec, x_return_status);
1915 -- store the highest degree of error
1916 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1917 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1918 -- need to leave
1919 l_return_status := x_return_status;
1920 RAISE G_EXCEPTION_HALT_VALIDATION;
1921 ELSE
1922 -- record that there was an error
1923 l_return_status := x_return_status;
1924 END IF;
1925 END IF;
1926
1927 -- Validate_Stream_Type_Class
1928 Validate_Stream_Type_Class(p_styv_rec, x_return_status);
1929 -- store the highest degree of error
1930 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1931 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1932 -- need to leave
1933 l_return_status := x_return_status;
1934 RAISE G_EXCEPTION_HALT_VALIDATION;
1935 ELSE
1936 -- record that there was an error
1937 l_return_status := x_return_status;
1938 END IF;
1939 END IF;
1940 */
1941 -- Commented by RGOOTY for ER 3935682: End
1942
1943 -- Validate_Stream_Type_SubClass
1944 Validate_Stream_Type_SubClass(p_styv_rec, x_return_status);
1945 -- store the highest degree of error
1946 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1947 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1948 -- need to leave
1949 l_return_status := x_return_status;
1950 RAISE G_EXCEPTION_HALT_VALIDATION;
1951 ELSE
1952 -- record that there was an error
1953 l_return_status := x_return_status;
1954 END IF;
1955 END IF;
1956
1957 -- Validate_Accrual_Yn
1958 Validate_Accrual_Yn(p_styv_rec, x_return_status);
1959 -- store the highest degree of error
1960 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1961 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1962 -- need to leave
1963 l_return_status := x_return_status;
1964 RAISE G_EXCEPTION_HALT_VALIDATION;
1965 ELSE
1966 -- record that there was an error
1967 l_return_status := x_return_status;
1968 END IF;
1969 END IF;
1970
1971 -- Commented by RGOOTY for ER 3935682: Start
1972 /*
1973 -- Validate_Allocation_Factor
1974 Validate_Allocation_Factor(p_styv_rec, x_return_status);
1975 -- store the highest degree of error
1976 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1977 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1978 -- need to leave
1979 l_return_status := x_return_status;
1980 RAISE G_EXCEPTION_HALT_VALIDATION;
1981 ELSE
1982 -- record that there was an error
1983 l_return_status := x_return_status;
1984 END IF;
1985 END IF;
1986 */
1987 -- Commented by RGOOTY for ER 3935682: End
1988 -- validate_stream_type_purpose
1989 validate_stream_type_purpose(p_styv_rec, x_return_status);
1990 -- store the highest degree of error
1991 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1992 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1993 -- need to leave
1994 l_return_status := x_return_status;
1995 RAISE G_EXCEPTION_HALT_VALIDATION;
1996 ELSE
1997 -- record that there was an error
1998 l_return_status := x_return_status;
1999 END IF;
2000 END IF;
2001
2002
2003 RETURN(l_return_status);
2004 EXCEPTION
2005 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2006 -- just come out with return status
2007 null;
2008 RETURN (l_return_status);
2009
2010 WHEN OTHERS THEN
2011 -- store SQL error message on message stack for caller
2012 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2013 p_msg_name => G_OKL_UNEXPECTED_ERROR,
2014 p_token1 => G_OKL_SQLCODE_TOKEN,
2015 p_token1_value => SQLCODE,
2016 p_token2 => G_OKL_SQLERRM_TOKEN,
2017 p_token2_value => SQLERRM);
2018 -- notify caller of an UNEXPECTED error
2019 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
2020 RETURN(l_return_status);
2021
2022 END Validate_Attributes;
2023
2024 ---------------------------------------------------------------------------
2025 -- PROCEDURE Validate_Unique_Sty_Record
2026 ---------------------------------------------------------------------------
2027 -- Start of comments
2028 --
2029 -- Procedure Name : Validate_Unique_Sty_Record
2030 -- Description :
2031 -- Business Rules :
2032 -- Parameters :
2033 -- Version : 1.0
2034 -- End of comments
2035 ---------------------------------------------------------------------------
2036 PROCEDURE Validate_Unique_Sty_Record(p_styv_rec IN styv_rec_type
2037 ,x_return_status OUT NOCOPY VARCHAR2)
2038 IS
2039
2040 l_dummy VARCHAR2(1) := '?';
2041 l_row_found BOOLEAN := FALSE;
2042
2043 -- Cursor for sty Unique Key
2044 CURSOR okl_sty_uk_csr(p_rec styv_rec_type) IS
2045 SELECT '1'
2046 FROM OKL_STRM_TYPE_V
2047 WHERE code = p_rec.code
2048 AND version = p_rec.version
2049 AND stream_type_purpose = p_rec.stream_type_purpose
2050 AND id <> NVL(p_rec.id,-9999);
2051
2052 BEGIN
2053 -- initialize return status
2054 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
2055 OPEN okl_sty_uk_csr(p_styv_rec);
2056 FETCH okl_sty_uk_csr INTO l_dummy;
2057 l_row_found := okl_sty_uk_csr%FOUND;
2058 CLOSE okl_sty_uk_csr;
2059 IF l_row_found THEN
2060 Okc_Api.set_message(G_APP_NAME,G_OKL_UNQS);
2061 x_return_status := Okc_Api.G_RET_STS_ERROR;
2062 END IF;
2063
2064 EXCEPTION
2065 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2066 -- no processing necessary; validation can continue
2067 -- with the next column
2068 NULL;
2069
2070 WHEN OTHERS THEN
2071 -- store SQL error message on message stack for caller
2072 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2073 p_msg_name => G_OKL_UNEXPECTED_ERROR,
2074 p_token1 => G_OKL_SQLCODE_TOKEN,
2075 p_token1_value => SQLCODE,
2076 p_token2 => G_OKL_SQLERRM_TOKEN,
2077 p_token2_value => SQLERRM);
2078 -- notify caller of an UNEXPECTED error
2079 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
2080
2081 END Validate_Unique_sty_Record;
2082
2083 ---------------------------------------------------------------------------
2084 -- PROCEDURE Validate_Record
2085 ---------------------------------------------------------------------------
2086 -- Start of comments
2087 --
2088 -- Procedure Name : Validate_Record
2089 -- Description :
2090 -- Business Rules :
2091 -- Parameters :
2092 -- Version : 1.0
2093 -- End of comments
2094 ---------------------------------------------------------------------------
2095
2096 FUNCTION Validate_Record (
2097 p_styv_rec IN styv_rec_type
2098 ) RETURN VARCHAR2 IS
2099 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2100 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2101 BEGIN
2102 -- Validate_Unique_sty_Record
2103 Validate_Unique_sty_Record(p_styv_rec, x_return_status);
2104 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
2105 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
2106 -- need to leave
2107 l_return_status := x_return_status;
2108 RAISE G_EXCEPTION_HALT_VALIDATION;
2109 ELSE
2110 -- record that there was an error
2111 l_return_status := x_return_status;
2112 END IF;
2113 END IF;
2114
2115 RETURN(l_return_status);
2116 EXCEPTION
2117 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2118 -- exit with return status
2119 NULL;
2120 RETURN (l_return_status);
2121
2122 WHEN OTHERS THEN
2123 -- store SQL error message on message stack for caller
2124 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2125 p_msg_name => G_OKL_UNEXPECTED_ERROR,
2126 p_token1 => G_OKL_SQLCODE_TOKEN,
2127 p_token1_value => SQLCODE,
2128 p_token2 => G_OKL_SQLERRM_TOKEN,
2129 p_token2_value => SQLERRM);
2130 -- notify caller of an UNEXPECTED error
2131 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
2132 RETURN(l_return_status);
2133 END Validate_Record;
2134 -- END change : mvasudev
2135
2136 ---------------------------------------------------------------------------
2137 -- PROCEDURE Migrate
2138 ---------------------------------------------------------------------------
2139 PROCEDURE migrate (
2140 p_from IN styv_rec_type,
2141 p_to IN OUT NOCOPY sty_rec_type
2142 ) IS
2143 BEGIN
2144 p_to.id := p_from.id;
2145 p_to.version := p_from.version;
2146 p_to.object_version_number := p_from.object_version_number;
2147 p_to.code := p_from.code;
2148 p_to.stream_type_scope := p_from.stream_type_scope;
2149 p_to.start_date := p_from.start_date;
2150 p_to.end_date := p_from.end_date;
2151 p_to.billable_yn := p_from.billable_yn;
2152 p_to.capitalize_yn := p_from.capitalize_yn;
2153 p_to.periodic_yn := p_from.periodic_yn;
2154 p_to.fundable_yn := p_from.fundable_yn;
2155 -- mvasudev , 05/13/2002
2156 p_to.allocation_factor := p_from.allocation_factor;
2157 --
2158 p_to.taxable_default_yn := p_from.taxable_default_yn;
2159 p_to.customization_level := p_from.customization_level;
2160 p_to.stream_type_class := p_from.stream_type_class;
2161 -- hkpatel 04/15/2002
2162 p_to.stream_type_subclass := p_from.stream_type_subclass;
2163 --
2164 p_to.accrual_yn := p_from.accrual_yn;
2165 p_to.attribute_category := p_from.attribute_category;
2166 p_to.attribute1 := p_from.attribute1;
2167 p_to.attribute2 := p_from.attribute2;
2168 p_to.attribute3 := p_from.attribute3;
2169 p_to.attribute4 := p_from.attribute4;
2170 p_to.attribute5 := p_from.attribute5;
2171 p_to.attribute6 := p_from.attribute6;
2172 p_to.attribute7 := p_from.attribute7;
2173 p_to.attribute8 := p_from.attribute8;
2174 p_to.attribute9 := p_from.attribute9;
2175 p_to.attribute10 := p_from.attribute10;
2176 p_to.attribute11 := p_from.attribute11;
2177 p_to.attribute12 := p_from.attribute12;
2178 p_to.attribute13 := p_from.attribute13;
2179 p_to.attribute14 := p_from.attribute14;
2180 p_to.attribute15 := p_from.attribute15;
2181 p_to.created_by := p_from.created_by;
2182 p_to.creation_date := p_from.creation_date;
2183 p_to.last_updated_by := p_from.last_updated_by;
2184 p_to.last_update_date := p_from.last_update_date;
2185 p_to.last_update_login := p_from.last_update_login;
2186 -- Added by RGOOTY for ER 3935682: Start
2187 p_to.stream_type_purpose := p_from.stream_type_purpose;
2188 p_to.contingency := p_from.contingency;
2189 -- Added by RGOOTY for ER 3935682: End
2190
2191 -- Added by SNANDIKO for Bug 6744584 Start
2192 p_to.contingency_id := p_from.contingency_id;
2193 -- Added by SNANDIKO for Bug 6744584 End
2194
2195 END migrate;
2196 PROCEDURE migrate (
2197 p_from IN sty_rec_type,
2198 p_to IN OUT NOCOPY styv_rec_type
2199 ) IS
2200 BEGIN
2201 p_to.id := p_from.id;
2202 p_to.version := p_from.version;
2203 p_to.code := p_from.code;
2204 p_to.customization_level := p_from.customization_level;
2205 p_to.stream_type_scope := p_from.stream_type_scope;
2206 p_to.object_version_number := p_from.object_version_number;
2207 p_to.accrual_yn := p_from.accrual_yn;
2208 p_to.taxable_default_yn := p_from.taxable_default_yn;
2209 p_to.stream_type_class := p_from.stream_type_class;
2210 -- hkpatel 04/15/2003
2211 p_to.stream_type_subclass := p_from.stream_type_subclass;
2212 --
2213 p_to.start_date := p_from.start_date;
2214 p_to.end_date := p_from.end_date;
2215 p_to.billable_yn := p_from.billable_yn;
2216 p_to.capitalize_yn := p_from.capitalize_yn;
2217 p_to.periodic_yn := p_from.periodic_yn;
2218 p_to.fundable_yn := p_from.fundable_yn;
2219 -- mvasudev , 05/13/2002
2220 p_to.allocation_factor := p_from.allocation_factor;
2221 --
2222 p_to.attribute_category := p_from.attribute_category;
2223 p_to.attribute1 := p_from.attribute1;
2224 p_to.attribute2 := p_from.attribute2;
2225 p_to.attribute3 := p_from.attribute3;
2226 p_to.attribute4 := p_from.attribute4;
2227 p_to.attribute5 := p_from.attribute5;
2228 p_to.attribute6 := p_from.attribute6;
2229 p_to.attribute7 := p_from.attribute7;
2230 p_to.attribute8 := p_from.attribute8;
2231 p_to.attribute9 := p_from.attribute9;
2232 p_to.attribute10 := p_from.attribute10;
2233 p_to.attribute11 := p_from.attribute11;
2234 p_to.attribute12 := p_from.attribute12;
2235 p_to.attribute13 := p_from.attribute13;
2236 p_to.attribute14 := p_from.attribute14;
2237 p_to.attribute15 := p_from.attribute15;
2238 p_to.created_by := p_from.created_by;
2239 p_to.creation_date := p_from.creation_date;
2240 p_to.last_updated_by := p_from.last_updated_by;
2241 p_to.last_update_date := p_from.last_update_date;
2242 p_to.last_update_login := p_from.last_update_login;
2243 -- Added by RGOOTY for ER 3935682: Start
2244 p_to.stream_type_purpose := p_from.stream_type_purpose;
2245 p_to.contingency := p_from.contingency;
2246 -- Added by RGOOTY for ER 3935682: End
2247
2248 -- Added by SNANDIKO for Bug 6744584 Start
2249 p_to.contingency_id := p_from.contingency_id;
2250 -- Added by SNANDIKO for Bug 6744584 End
2251
2252 END migrate;
2253 PROCEDURE migrate (
2254 p_from IN styv_rec_type,
2255 p_to IN OUT NOCOPY okl_strm_type_tl_rec_type
2256 ) IS
2257 BEGIN
2258 p_to.id := p_from.id;
2259 p_to.sfwt_flag := p_from.sfwt_flag;
2260 p_to.name := p_from.name;
2261 p_to.description := p_from.description;
2262 p_to.created_by := p_from.created_by;
2263 p_to.creation_date := p_from.creation_date;
2264 p_to.last_updated_by := p_from.last_updated_by;
2265 p_to.last_update_date := p_from.last_update_date;
2266 p_to.last_update_login := p_from.last_update_login;
2267 -- Added by RGOOTY for ER 3935682: Start
2268 p_to.short_description := p_from.short_description;
2269 -- Added by RGOOTY for ER 3935682: End
2270 END migrate;
2271 PROCEDURE migrate (
2272 p_from IN okl_strm_type_tl_rec_type,
2273 p_to IN OUT NOCOPY styv_rec_type
2274 ) IS
2275 BEGIN
2276 p_to.id := p_from.id;
2277 p_to.sfwt_flag := p_from.sfwt_flag;
2278 p_to.name := p_from.name;
2279 p_to.description := p_from.description;
2280 p_to.created_by := p_from.created_by;
2281 p_to.creation_date := p_from.creation_date;
2282 p_to.last_updated_by := p_from.last_updated_by;
2283 p_to.last_update_date := p_from.last_update_date;
2284 p_to.last_update_login := p_from.last_update_login;
2285 -- Added by RGOOTY for ER 3935682: Start
2286 p_to.short_description := p_from.short_description;
2287 -- Added by RGOOTY for ER 3935682: End
2288 END migrate;
2289
2290 ---------------------------------------------------------------------------
2291 -- PROCEDURE validate_row
2292 ---------------------------------------------------------------------------
2293 --------------------------------------
2294 -- validate_row for:OKL_STRM_TYPE_V --
2295 --------------------------------------
2296 PROCEDURE validate_row(
2297 p_api_version IN NUMBER,
2298 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2299 x_return_status OUT NOCOPY VARCHAR2,
2300 x_msg_count OUT NOCOPY NUMBER,
2301 x_msg_data OUT NOCOPY VARCHAR2,
2302 p_styv_rec IN styv_rec_type) IS
2303
2304 l_api_version CONSTANT NUMBER := 1;
2305 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2306 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2307 l_styv_rec styv_rec_type := p_styv_rec;
2308 l_sty_rec sty_rec_type;
2309 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
2310 BEGIN
2311 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2312 G_PKG_NAME,
2313 p_init_msg_list,
2314 l_api_version,
2315 p_api_version,
2316 '_PVT',
2317 x_return_status);
2318 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2319 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2320 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2321 RAISE OKC_API.G_EXCEPTION_ERROR;
2322 END IF;
2323 --- Validate all non-missing attributes (Item Level Validation)
2324 l_return_status := Validate_Attributes(l_styv_rec);
2325 --- If any errors happen abort API
2326 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2327 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2328 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2329 RAISE OKC_API.G_EXCEPTION_ERROR;
2330 END IF;
2331 l_return_status := Validate_Record(l_styv_rec);
2332 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2333 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2334 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2335 RAISE OKC_API.G_EXCEPTION_ERROR;
2336 END IF;
2337 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2338 EXCEPTION
2339 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2340 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2341 (
2342 l_api_name,
2343 G_PKG_NAME,
2344 'OKC_API.G_RET_STS_ERROR',
2345 x_msg_count,
2346 x_msg_data,
2347 '_PVT'
2348 );
2349 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2350 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2351 (
2352 l_api_name,
2353 G_PKG_NAME,
2354 'OKC_API.G_RET_STS_UNEXP_ERROR',
2355 x_msg_count,
2356 x_msg_data,
2357 '_PVT'
2358 );
2359 WHEN OTHERS THEN
2360 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2361 (
2362 l_api_name,
2363 G_PKG_NAME,
2364 'OTHERS',
2365 x_msg_count,
2366 x_msg_data,
2367 '_PVT'
2368 );
2369 END validate_row;
2370 ------------------------------------------
2371 -- PL/SQL TBL validate_row for:STYV_TBL --
2372 ------------------------------------------
2373 PROCEDURE validate_row(
2374 p_api_version IN NUMBER,
2375 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2376 x_return_status OUT NOCOPY VARCHAR2,
2377 x_msg_count OUT NOCOPY NUMBER,
2378 x_msg_data OUT NOCOPY VARCHAR2,
2379 p_styv_tbl IN styv_tbl_type) IS
2380
2381 l_api_version CONSTANT NUMBER := 1;
2382 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2383 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2384 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2385 i NUMBER := 0;
2386 BEGIN
2387 OKC_API.init_msg_list(p_init_msg_list);
2388 -- Make sure PL/SQL table has records in it before passing
2389 IF (p_styv_tbl.COUNT > 0) THEN
2390 i := p_styv_tbl.FIRST;
2391 LOOP
2392 validate_row (
2393 p_api_version => p_api_version,
2394 p_init_msg_list => OKC_API.G_FALSE,
2395 x_return_status => x_return_status,
2396 x_msg_count => x_msg_count,
2397 x_msg_data => x_msg_data,
2398 p_styv_rec => p_styv_tbl(i));
2399
2400 /* Begin Post Generation Change */
2401 -- store the highest degree of error
2402 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2403 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2404 l_overall_status := x_return_status;
2405 END IF;
2406 END IF;
2407 /* End Post Generation Change */
2408
2409 EXIT WHEN (i = p_styv_tbl.LAST);
2410 i := p_styv_tbl.NEXT(i);
2411 END LOOP;
2412
2413 -- return the overall status
2414 x_return_status :=l_overall_status;
2415 END IF;
2416 EXCEPTION
2417 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2418 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'OKC_API.G_RET_STS_ERROR',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2428 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2429 (
2430 l_api_name,
2431 G_PKG_NAME,
2432 'OKC_API.G_RET_STS_UNEXP_ERROR',
2433 x_msg_count,
2434 x_msg_data,
2435 '_PVT'
2436 );
2437 WHEN OTHERS THEN
2438 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 G_PKG_NAME,
2442 'OTHERS',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT'
2446 );
2447 END validate_row;
2448
2449 ---------------------------------------------------------------------------
2450 -- PROCEDURE insert_row
2451 ---------------------------------------------------------------------------
2452 ------------------------------------
2453 -- insert_row for:OKL_STRM_TYPE_B --
2454 ------------------------------------
2455 PROCEDURE insert_row(
2456 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2457 x_return_status OUT NOCOPY VARCHAR2,
2458 x_msg_count OUT NOCOPY NUMBER,
2459 x_msg_data OUT NOCOPY VARCHAR2,
2460 p_sty_rec IN sty_rec_type,
2461 x_sty_rec OUT NOCOPY sty_rec_type) IS
2462
2463 l_api_version CONSTANT NUMBER := 1;
2464 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
2465 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2466 l_sty_rec sty_rec_type := p_sty_rec;
2467 l_def_sty_rec sty_rec_type;
2468 ----------------------------------------
2469 -- Set_Attributes for:OKL_STRM_TYPE_B --
2470 ----------------------------------------
2471 FUNCTION Set_Attributes (
2472 p_sty_rec IN sty_rec_type,
2473 x_sty_rec OUT NOCOPY sty_rec_type
2474 ) RETURN VARCHAR2 IS
2475 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2476 BEGIN
2477 x_sty_rec := p_sty_rec;
2478 RETURN(l_return_status);
2479 END Set_Attributes;
2480 BEGIN
2481 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2482 p_init_msg_list,
2483 '_PVT',
2484 x_return_status);
2485 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2486 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2487 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2488 RAISE OKC_API.G_EXCEPTION_ERROR;
2489 END IF;
2490 --- Setting item attributes
2491 l_return_status := Set_Attributes(
2492 p_sty_rec, -- IN
2493 l_sty_rec); -- OUT
2494 --- If any errors happen abort API
2495 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2496 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2497 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2498 RAISE OKC_API.G_EXCEPTION_ERROR;
2499 END IF;
2500 INSERT INTO OKL_STRM_TYPE_B(
2501 id,
2502 version,
2503 code,
2504 customization_level,
2505 stream_type_scope,
2506 object_version_number,
2507 accrual_yn,
2508 taxable_default_yn,
2509 stream_type_class,
2510 -- hkpatel 04/15/2003
2511 stream_type_subclass,
2512 --
2513 start_date,
2514 end_date,
2515 billable_yn,
2516 capitalize_yn,
2517 periodic_yn,
2518 fundable_yn,
2519 -- mvasudev , 05/13/2002
2520 allocation_factor,
2521 --
2522 attribute_category,
2523 attribute1,
2524 attribute2,
2525 attribute3,
2526 attribute4,
2527 attribute5,
2528 attribute6,
2529 attribute7,
2530 attribute8,
2531 attribute9,
2532 attribute10,
2533 attribute11,
2534 attribute12,
2535 attribute13,
2536 attribute14,
2537 attribute15,
2538 created_by,
2539 creation_date,
2540 last_updated_by,
2541 last_update_date,
2542 last_update_login,
2543 -- Added by RGOOTY for ER 3935682: Start
2544 stream_type_purpose,
2545 contingency,
2546 -- Added by RGOOTY for ER 3935682: End
2547
2548 -- Added by SNANDIKO for Bug 6744584 Start
2549 contingency_id
2550 -- Added by SNANDIKO for Bug 6744584 End
2551 )
2552 VALUES (
2553 l_sty_rec.id,
2554 l_sty_rec.version,
2555 l_sty_rec.code,
2556 l_sty_rec.customization_level,
2557 l_sty_rec.stream_type_scope,
2558 l_sty_rec.object_version_number,
2559 l_sty_rec.accrual_yn,
2560 l_sty_rec.taxable_default_yn,
2561 l_sty_rec.stream_type_class,
2562 -- hkpatel 04/15/2003
2563 l_sty_rec.stream_type_subclass,
2564 --
2565 l_sty_rec.start_date,
2566 l_sty_rec.end_date,
2567 l_sty_rec.billable_yn,
2568 l_sty_rec.capitalize_yn,
2569 l_sty_rec.periodic_yn,
2570 l_sty_rec.fundable_yn,
2571 -- mvasudev , 05/13/2002
2572 l_sty_rec.allocation_factor,
2573 --
2574 l_sty_rec.attribute_category,
2575 l_sty_rec.attribute1,
2576 l_sty_rec.attribute2,
2577 l_sty_rec.attribute3,
2578 l_sty_rec.attribute4,
2579 l_sty_rec.attribute5,
2580 l_sty_rec.attribute6,
2581 l_sty_rec.attribute7,
2582 l_sty_rec.attribute8,
2583 l_sty_rec.attribute9,
2584 l_sty_rec.attribute10,
2585 l_sty_rec.attribute11,
2586 l_sty_rec.attribute12,
2587 l_sty_rec.attribute13,
2588 l_sty_rec.attribute14,
2589 l_sty_rec.attribute15,
2590 l_sty_rec.created_by,
2591 l_sty_rec.creation_date,
2592 l_sty_rec.last_updated_by,
2593 l_sty_rec.last_update_date,
2594 l_sty_rec.last_update_login,
2595 -- Added by RGOOTY for ER 3935682: Start
2596 l_sty_rec.stream_type_purpose,
2597 l_sty_rec.contingency,
2598 -- Added by RGOOTY for ER 3935682: End
2599 -- Added by SNANDIKO for Bug 6744584 Start
2600 l_sty_rec.contingency_id
2601 -- Added by SNANDIKO for Bug 6744584 End
2602
2603 );
2604 -- Set OUT values
2605 x_sty_rec := l_sty_rec;
2606 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2607 EXCEPTION
2608 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2609 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2610 (
2611 l_api_name,
2612 G_PKG_NAME,
2613 'OKC_API.G_RET_STS_ERROR',
2614 x_msg_count,
2615 x_msg_data,
2616 '_PVT'
2617 );
2618 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2619 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2620 (
2621 l_api_name,
2622 G_PKG_NAME,
2623 'OKC_API.G_RET_STS_UNEXP_ERROR',
2624 x_msg_count,
2625 x_msg_data,
2626 '_PVT'
2627 );
2628 WHEN OTHERS THEN
2629 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2630 (
2631 l_api_name,
2632 G_PKG_NAME,
2633 'OTHERS',
2634 x_msg_count,
2635 x_msg_data,
2636 '_PVT'
2637 );
2638 END insert_row;
2639 -------------------------------------
2640 -- insert_row for:OKL_STRM_TYPE_TL --
2641 -------------------------------------
2642 PROCEDURE insert_row(
2643 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2644 x_return_status OUT NOCOPY VARCHAR2,
2645 x_msg_count OUT NOCOPY NUMBER,
2646 x_msg_data OUT NOCOPY VARCHAR2,
2647 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
2648 x_okl_strm_type_tl_rec OUT NOCOPY okl_strm_type_tl_rec_type) IS
2649
2650 l_api_version CONSTANT NUMBER := 1;
2651 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
2652 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2653 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type := p_okl_strm_type_tl_rec;
2654 l_def_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
2655 CURSOR get_languages IS
2656 SELECT *
2657 FROM FND_LANGUAGES
2658 WHERE INSTALLED_FLAG IN ('I', 'B');
2659 -----------------------------------------
2660 -- Set_Attributes for:OKL_STRM_TYPE_TL --
2661 -----------------------------------------
2662 FUNCTION Set_Attributes (
2663 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
2664 x_okl_strm_type_tl_rec OUT NOCOPY okl_strm_type_tl_rec_type
2665 ) RETURN VARCHAR2 IS
2666 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2667 BEGIN
2668 x_okl_strm_type_tl_rec := p_okl_strm_type_tl_rec;
2669 x_okl_strm_type_tl_rec.LANGUAGE := USERENV('LANG');
2670 x_okl_strm_type_tl_rec.SOURCE_LANG := USERENV('LANG');
2671 RETURN(l_return_status);
2672 END Set_Attributes;
2673 BEGIN
2674 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2675 p_init_msg_list,
2676 '_PVT',
2677 x_return_status);
2678 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2679 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2680 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2681 RAISE OKC_API.G_EXCEPTION_ERROR;
2682 END IF;
2683 --- Setting item attributes
2684 l_return_status := Set_Attributes(
2685 p_okl_strm_type_tl_rec, -- IN
2686 l_okl_strm_type_tl_rec); -- OUT
2687 --- If any errors happen abort API
2688 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2689 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2690 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2691 RAISE OKC_API.G_EXCEPTION_ERROR;
2692 END IF;
2693 FOR l_lang_rec IN get_languages LOOP
2694 l_okl_strm_type_tl_rec.language := l_lang_rec.language_code;
2695 INSERT INTO OKL_STRM_TYPE_TL(
2696 id,
2697 language,
2698 source_lang,
2699 sfwt_flag,
2700 name,
2701 description,
2702 created_by,
2703 creation_date,
2704 last_updated_by,
2705 last_update_date,
2706 last_update_login,
2707 -- Added by RGOOTY for ER 3935682: Start
2708 short_description
2709 -- Added by RGOOTY for ER 3935682: End
2710 )
2711 VALUES (
2712 l_okl_strm_type_tl_rec.id,
2713 l_okl_strm_type_tl_rec.language,
2714 l_okl_strm_type_tl_rec.source_lang,
2715 l_okl_strm_type_tl_rec.sfwt_flag,
2716 l_okl_strm_type_tl_rec.name,
2717 l_okl_strm_type_tl_rec.description,
2718 l_okl_strm_type_tl_rec.created_by,
2719 l_okl_strm_type_tl_rec.creation_date,
2720 l_okl_strm_type_tl_rec.last_updated_by,
2721 l_okl_strm_type_tl_rec.last_update_date,
2722 l_okl_strm_type_tl_rec.last_update_login,
2723 -- Added by RGOOTY for ER 3935682: Start
2724 l_okl_strm_type_tl_rec.short_description
2725 -- Added by RGOOTY for ER 3935682: End
2726 );
2727 END LOOP;
2728 -- Set OUT values
2729 x_okl_strm_type_tl_rec := l_okl_strm_type_tl_rec;
2730 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2731 EXCEPTION
2732 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2733 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2734 (
2735 l_api_name,
2736 G_PKG_NAME,
2737 'OKC_API.G_RET_STS_ERROR',
2738 x_msg_count,
2739 x_msg_data,
2740 '_PVT'
2741 );
2742 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2743 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2744 (
2745 l_api_name,
2746 G_PKG_NAME,
2747 'OKC_API.G_RET_STS_UNEXP_ERROR',
2748 x_msg_count,
2749 x_msg_data,
2750 '_PVT'
2751 );
2752 WHEN OTHERS THEN
2753 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2754 (
2755 l_api_name,
2756 G_PKG_NAME,
2757 'OTHERS',
2758 x_msg_count,
2759 x_msg_data,
2760 '_PVT'
2761 );
2762 END insert_row;
2763 ------------------------------------
2764 -- insert_row for:OKL_STRM_TYPE_V --
2765 ------------------------------------
2766 PROCEDURE insert_row(
2767 p_api_version IN NUMBER,
2768 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2769 x_return_status OUT NOCOPY VARCHAR2,
2770 x_msg_count OUT NOCOPY NUMBER,
2771 x_msg_data OUT NOCOPY VARCHAR2,
2772 p_styv_rec IN styv_rec_type,
2773 x_styv_rec OUT NOCOPY styv_rec_type) IS
2774
2775 l_api_version CONSTANT NUMBER := 1;
2776 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2777 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2778 l_styv_rec styv_rec_type;
2779 l_def_styv_rec styv_rec_type;
2780 l_sty_rec sty_rec_type;
2781 lx_sty_rec sty_rec_type;
2782 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
2783 lx_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
2784 -------------------------------
2785 -- FUNCTION fill_who_columns --
2786 -------------------------------
2787 FUNCTION fill_who_columns (
2788 p_styv_rec IN styv_rec_type
2789 ) RETURN styv_rec_type IS
2790 l_styv_rec styv_rec_type := p_styv_rec;
2791 BEGIN
2792 l_styv_rec.CREATION_DATE := SYSDATE;
2793 l_styv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2794 l_styv_rec.LAST_UPDATE_DATE := SYSDATE;
2795 l_styv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2796 l_styv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2797 RETURN(l_styv_rec);
2798 END fill_who_columns;
2799 ----------------------------------------
2800 -- Set_Attributes for:OKL_STRM_TYPE_V --
2801 ----------------------------------------
2802 FUNCTION Set_Attributes (
2803 p_styv_rec IN styv_rec_type,
2804 x_styv_rec OUT NOCOPY styv_rec_type
2805 ) RETURN VARCHAR2 IS
2806 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2807 BEGIN
2808 x_styv_rec := p_styv_rec;
2809 x_styv_rec.OBJECT_VERSION_NUMBER := 1;
2810 x_styv_rec.SFWT_FLAG := 'N';
2811 RETURN(l_return_status);
2812 END Set_Attributes;
2813 BEGIN
2814 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2815 G_PKG_NAME,
2816 p_init_msg_list,
2817 l_api_version,
2818 p_api_version,
2819 '_PVT',
2820 x_return_status);
2821 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2822 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2823 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2824 RAISE OKC_API.G_EXCEPTION_ERROR;
2825 END IF;
2826 l_styv_rec := null_out_defaults(p_styv_rec);
2827 -- Set primary key value
2828 l_styv_rec.ID := get_seq_id;
2829 --- Setting item attributes
2830 l_return_status := Set_Attributes(
2831 l_styv_rec, -- IN
2832 l_def_styv_rec); -- OUT
2833 --- If any errors happen abort API
2834 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2835 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2836 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2837 RAISE OKC_API.G_EXCEPTION_ERROR;
2838 END IF;
2839 l_def_styv_rec := fill_who_columns(l_def_styv_rec);
2840 --- Validate all non-missing attributes (Item Level Validation)
2841 l_return_status := Validate_Attributes(l_def_styv_rec);
2842 --- If any errors happen abort API
2843 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2844 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2845 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2846 RAISE OKC_API.G_EXCEPTION_ERROR;
2847 END IF;
2848 l_return_status := Validate_Record(l_def_styv_rec);
2849 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2850 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2851 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2852 RAISE OKC_API.G_EXCEPTION_ERROR;
2853 END IF;
2854 --------------------------------------
2855 -- Move VIEW record to "Child" records
2856 --------------------------------------
2857 migrate(l_def_styv_rec, l_sty_rec);
2858 migrate(l_def_styv_rec, l_okl_strm_type_tl_rec);
2859 --------------------------------------------
2860 -- Call the INSERT_ROW for each child record
2861 --------------------------------------------
2862 insert_row(
2863 p_init_msg_list,
2864 x_return_status,
2865 x_msg_count,
2866 x_msg_data,
2867 l_sty_rec,
2868 lx_sty_rec
2869 );
2870 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2871 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2872 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2873 RAISE OKC_API.G_EXCEPTION_ERROR;
2874 END IF;
2875 migrate(lx_sty_rec, l_def_styv_rec);
2876 insert_row(
2877 p_init_msg_list,
2878 x_return_status,
2879 x_msg_count,
2880 x_msg_data,
2881 l_okl_strm_type_tl_rec,
2882 lx_okl_strm_type_tl_rec
2883 );
2884 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2885 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2886 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2887 RAISE OKC_API.G_EXCEPTION_ERROR;
2888 END IF;
2889 migrate(lx_okl_strm_type_tl_rec, l_def_styv_rec);
2890 -- Set OUT values
2891 x_styv_rec := l_def_styv_rec;
2892 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2893 EXCEPTION
2894 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2895 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2896 (
2897 l_api_name,
2898 G_PKG_NAME,
2899 'OKC_API.G_RET_STS_ERROR',
2900 x_msg_count,
2901 x_msg_data,
2902 '_PVT'
2903 );
2904 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2905 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2906 (
2907 l_api_name,
2908 G_PKG_NAME,
2909 'OKC_API.G_RET_STS_UNEXP_ERROR',
2910 x_msg_count,
2911 x_msg_data,
2912 '_PVT'
2913 );
2914 WHEN OTHERS THEN
2915 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2916 (
2917 l_api_name,
2918 G_PKG_NAME,
2919 'OTHERS',
2920 x_msg_count,
2921 x_msg_data,
2922 '_PVT'
2923 );
2924 END insert_row;
2925 ----------------------------------------
2926 -- PL/SQL TBL insert_row for:STYV_TBL --
2927 ----------------------------------------
2928 PROCEDURE insert_row(
2929 p_api_version IN NUMBER,
2930 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2931 x_return_status OUT NOCOPY VARCHAR2,
2932 x_msg_count OUT NOCOPY NUMBER,
2933 x_msg_data OUT NOCOPY VARCHAR2,
2934 p_styv_tbl IN styv_tbl_type,
2935 x_styv_tbl OUT NOCOPY styv_tbl_type) IS
2936
2937 l_api_version CONSTANT NUMBER := 1;
2938 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2939 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2940 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2941
2942 i NUMBER := 0;
2943 BEGIN
2944 OKC_API.init_msg_list(p_init_msg_list);
2945 -- Make sure PL/SQL table has records in it before passing
2946 IF (p_styv_tbl.COUNT > 0) THEN
2947 i := p_styv_tbl.FIRST;
2948 LOOP
2949 insert_row (
2950 p_api_version => p_api_version,
2951 p_init_msg_list => OKC_API.G_FALSE,
2952 x_return_status => x_return_status,
2953 x_msg_count => x_msg_count,
2954 x_msg_data => x_msg_data,
2955 p_styv_rec => p_styv_tbl(i),
2956 x_styv_rec => x_styv_tbl(i));
2957
2958 /* Begin Post Generation Change */
2959 -- store the highest degree of error
2960 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2961 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2962 l_overall_status := x_return_status;
2963 END IF;
2964 END IF;
2965 /* End Post Generation Change */
2966
2967 EXIT WHEN (i = p_styv_tbl.LAST);
2968 i := p_styv_tbl.NEXT(i);
2969 END LOOP;
2970 -- return the overall status
2971 x_return_status :=l_overall_status;
2972 END IF;
2973 EXCEPTION
2974 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2975 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2976 (
2977 l_api_name,
2978 G_PKG_NAME,
2979 'OKC_API.G_RET_STS_ERROR',
2980 x_msg_count,
2981 x_msg_data,
2982 '_PVT'
2983 );
2984 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2985 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2986 (
2987 l_api_name,
2988 G_PKG_NAME,
2989 'OKC_API.G_RET_STS_UNEXP_ERROR',
2990 x_msg_count,
2991 x_msg_data,
2992 '_PVT'
2993 );
2994 WHEN OTHERS THEN
2995 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2996 (
2997 l_api_name,
2998 G_PKG_NAME,
2999 'OTHERS',
3000 x_msg_count,
3001 x_msg_data,
3002 '_PVT'
3003 );
3004 END insert_row;
3005
3006 ---------------------------------------------------------------------------
3007 -- PROCEDURE lock_row
3008 ---------------------------------------------------------------------------
3009 ----------------------------------
3010 -- lock_row for:OKL_STRM_TYPE_B --
3011 ----------------------------------
3012 PROCEDURE lock_row(
3013 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3014 x_return_status OUT NOCOPY VARCHAR2,
3015 x_msg_count OUT NOCOPY NUMBER,
3016 x_msg_data OUT NOCOPY VARCHAR2,
3017 p_sty_rec IN sty_rec_type) IS
3018
3019 E_Resource_Busy EXCEPTION;
3020 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3021 CURSOR lock_csr (p_sty_rec IN sty_rec_type) IS
3022 SELECT OBJECT_VERSION_NUMBER
3023 FROM OKL_STRM_TYPE_B
3024 WHERE ID = p_sty_rec.id
3025 AND OBJECT_VERSION_NUMBER = p_sty_rec.object_version_number
3026 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3027
3028 CURSOR lchk_csr (p_sty_rec IN sty_rec_type) IS
3029 SELECT OBJECT_VERSION_NUMBER
3030 FROM OKL_STRM_TYPE_B
3031 WHERE ID = p_sty_rec.id;
3032 l_api_version CONSTANT NUMBER := 1;
3033 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3034 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3035 l_object_version_number OKL_STRM_TYPE_B.OBJECT_VERSION_NUMBER%TYPE;
3036 lc_object_version_number OKL_STRM_TYPE_B.OBJECT_VERSION_NUMBER%TYPE;
3037 l_row_notfound BOOLEAN := FALSE;
3038 lc_row_notfound BOOLEAN := FALSE;
3039 BEGIN
3040 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3041 p_init_msg_list,
3042 '_PVT',
3043 x_return_status);
3044 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3045 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3046 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3047 RAISE OKC_API.G_EXCEPTION_ERROR;
3048 END IF;
3049 BEGIN
3050 OPEN lock_csr(p_sty_rec);
3051 FETCH lock_csr INTO l_object_version_number;
3052 l_row_notfound := lock_csr%NOTFOUND;
3053 CLOSE lock_csr;
3054 EXCEPTION
3055 WHEN E_Resource_Busy THEN
3056 IF (lock_csr%ISOPEN) THEN
3057 CLOSE lock_csr;
3058 END IF;
3059 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3060 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3061 END;
3062
3063 IF ( l_row_notfound ) THEN
3064 OPEN lchk_csr(p_sty_rec);
3065 FETCH lchk_csr INTO lc_object_version_number;
3066 lc_row_notfound := lchk_csr%NOTFOUND;
3067 CLOSE lchk_csr;
3068 END IF;
3069 IF (lc_row_notfound) THEN
3070 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3071 RAISE OKC_API.G_EXCEPTION_ERROR;
3072 ELSIF lc_object_version_number > p_sty_rec.object_version_number THEN
3073 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3074 RAISE OKC_API.G_EXCEPTION_ERROR;
3075 ELSIF lc_object_version_number <> p_sty_rec.object_version_number THEN
3076 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3077 RAISE OKC_API.G_EXCEPTION_ERROR;
3078 ELSIF lc_object_version_number = -1 THEN
3079 OKC_API.set_message(G_OKC_APP,G_RECORD_LOGICALLY_DELETED);
3080 RAISE OKC_API.G_EXCEPTION_ERROR;
3081 END IF;
3082 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3083 EXCEPTION
3084 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3085 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3086 (
3087 l_api_name,
3088 G_PKG_NAME,
3089 'OKC_API.G_RET_STS_ERROR',
3090 x_msg_count,
3091 x_msg_data,
3092 '_PVT'
3093 );
3094 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3095 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3096 (
3097 l_api_name,
3098 G_PKG_NAME,
3099 'OKC_API.G_RET_STS_UNEXP_ERROR',
3100 x_msg_count,
3101 x_msg_data,
3102 '_PVT'
3103 );
3104 WHEN OTHERS THEN
3105 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3106 (
3107 l_api_name,
3108 G_PKG_NAME,
3109 'OTHERS',
3110 x_msg_count,
3111 x_msg_data,
3112 '_PVT'
3113 );
3114 END lock_row;
3115 -----------------------------------
3116 -- lock_row for:OKL_STRM_TYPE_TL --
3117 -----------------------------------
3118 PROCEDURE lock_row(
3119 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3120 x_return_status OUT NOCOPY VARCHAR2,
3121 x_msg_count OUT NOCOPY NUMBER,
3122 x_msg_data OUT NOCOPY VARCHAR2,
3123 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type) IS
3124
3125 E_Resource_Busy EXCEPTION;
3126 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3127 CURSOR lock_csr (p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type) IS
3128 SELECT *
3129 FROM OKL_STRM_TYPE_TL
3130 WHERE ID = p_okl_strm_type_tl_rec.id
3131 FOR UPDATE NOWAIT;
3132
3133 l_api_version CONSTANT NUMBER := 1;
3134 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3135 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3136 l_lock_var lock_csr%ROWTYPE;
3137 l_row_notfound BOOLEAN := FALSE;
3138 lc_row_notfound BOOLEAN := FALSE;
3139 BEGIN
3140 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3141 p_init_msg_list,
3142 '_PVT',
3143 x_return_status);
3144 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3145 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3146 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3147 RAISE OKC_API.G_EXCEPTION_ERROR;
3148 END IF;
3149 BEGIN
3150 OPEN lock_csr(p_okl_strm_type_tl_rec);
3151 FETCH lock_csr INTO l_lock_var;
3152 l_row_notfound := lock_csr%NOTFOUND;
3153 CLOSE lock_csr;
3154 EXCEPTION
3155 WHEN E_Resource_Busy THEN
3156 IF (lock_csr%ISOPEN) THEN
3157 CLOSE lock_csr;
3158 END IF;
3159 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3160 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3161 END;
3162
3163 IF ( l_row_notfound ) THEN
3164 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3165 RAISE OKC_API.G_EXCEPTION_ERROR;
3166 END IF;
3167 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3168 EXCEPTION
3169 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3170 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3171 (
3172 l_api_name,
3173 G_PKG_NAME,
3174 'OKC_API.G_RET_STS_ERROR',
3175 x_msg_count,
3176 x_msg_data,
3177 '_PVT'
3178 );
3179 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3180 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3181 (
3182 l_api_name,
3183 G_PKG_NAME,
3184 'OKC_API.G_RET_STS_UNEXP_ERROR',
3185 x_msg_count,
3186 x_msg_data,
3187 '_PVT'
3188 );
3189 WHEN OTHERS THEN
3190 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3191 (
3192 l_api_name,
3193 G_PKG_NAME,
3194 'OTHERS',
3195 x_msg_count,
3196 x_msg_data,
3197 '_PVT'
3198 );
3199 END lock_row;
3200 ----------------------------------
3201 -- lock_row for:OKL_STRM_TYPE_V --
3202 ----------------------------------
3203 PROCEDURE lock_row(
3204 p_api_version IN NUMBER,
3205 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3206 x_return_status OUT NOCOPY VARCHAR2,
3207 x_msg_count OUT NOCOPY NUMBER,
3208 x_msg_data OUT NOCOPY VARCHAR2,
3209 p_styv_rec IN styv_rec_type) IS
3210
3211 l_api_version CONSTANT NUMBER := 1;
3212 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3213 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3214 l_sty_rec sty_rec_type;
3215 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
3216 BEGIN
3217 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3218 G_PKG_NAME,
3219 p_init_msg_list,
3220 l_api_version,
3221 p_api_version,
3222 '_PVT',
3223 x_return_status);
3224 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3225 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3226 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3227 RAISE OKC_API.G_EXCEPTION_ERROR;
3228 END IF;
3229 --------------------------------------
3230 -- Move VIEW record to "Child" records
3231 --------------------------------------
3232 migrate(p_styv_rec, l_sty_rec);
3233 migrate(p_styv_rec, l_okl_strm_type_tl_rec);
3234 --------------------------------------------
3235 -- Call the LOCK_ROW for each child record
3236 --------------------------------------------
3237 lock_row(
3238 p_init_msg_list,
3239 x_return_status,
3240 x_msg_count,
3241 x_msg_data,
3242 l_sty_rec
3243 );
3244 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3245 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3246 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3247 RAISE OKC_API.G_EXCEPTION_ERROR;
3248 END IF;
3249 lock_row(
3250 p_init_msg_list,
3251 x_return_status,
3252 x_msg_count,
3253 x_msg_data,
3254 l_okl_strm_type_tl_rec
3255 );
3256 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3257 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3258 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3259 RAISE OKC_API.G_EXCEPTION_ERROR;
3260 END IF;
3261 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3262 EXCEPTION
3263 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3264 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3265 (
3266 l_api_name,
3267 G_PKG_NAME,
3268 'OKC_API.G_RET_STS_ERROR',
3269 x_msg_count,
3270 x_msg_data,
3271 '_PVT'
3272 );
3273 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3274 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3275 (
3276 l_api_name,
3277 G_PKG_NAME,
3278 'OKC_API.G_RET_STS_UNEXP_ERROR',
3279 x_msg_count,
3280 x_msg_data,
3281 '_PVT'
3282 );
3283 WHEN OTHERS THEN
3284 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3285 (
3286 l_api_name,
3287 G_PKG_NAME,
3288 'OTHERS',
3289 x_msg_count,
3290 x_msg_data,
3291 '_PVT'
3292 );
3293 END lock_row;
3294 --------------------------------------
3295 -- PL/SQL TBL lock_row for:STYV_TBL --
3296 --------------------------------------
3297 PROCEDURE lock_row(
3298 p_api_version IN NUMBER,
3299 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3300 x_return_status OUT NOCOPY VARCHAR2,
3301 x_msg_count OUT NOCOPY NUMBER,
3302 x_msg_data OUT NOCOPY VARCHAR2,
3303 p_styv_tbl IN styv_tbl_type) IS
3304
3305 l_api_version CONSTANT NUMBER := 1;
3306 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
3307 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3308 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3309 i NUMBER := 0;
3310 BEGIN
3311 OKC_API.init_msg_list(p_init_msg_list);
3312 -- Make sure PL/SQL table has records in it before passing
3313 IF (p_styv_tbl.COUNT > 0) THEN
3314 i := p_styv_tbl.FIRST;
3315 LOOP
3316 lock_row (
3317 p_api_version => p_api_version,
3318 p_init_msg_list => OKC_API.G_FALSE,
3319 x_return_status => x_return_status,
3320 x_msg_count => x_msg_count,
3321 x_msg_data => x_msg_data,
3322 p_styv_rec => p_styv_tbl(i));
3323 /* Begin Post Generation Change */
3324 -- store the highest degree of error
3325 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3326 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3327 l_overall_status := x_return_status;
3328 END IF;
3329 END IF;
3330 /* End Post Generation Change */
3331
3332 EXIT WHEN (i = p_styv_tbl.LAST);
3333 i := p_styv_tbl.NEXT(i);
3334 END LOOP;
3335
3336 -- return the overall status
3337 x_return_status :=l_overall_status;
3338 END IF;
3339 EXCEPTION
3340 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3341 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3342 (
3343 l_api_name,
3344 G_PKG_NAME,
3345 'OKC_API.G_RET_STS_ERROR',
3346 x_msg_count,
3347 x_msg_data,
3348 '_PVT'
3349 );
3350 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3351 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3352 (
3353 l_api_name,
3354 G_PKG_NAME,
3355 'OKC_API.G_RET_STS_UNEXP_ERROR',
3356 x_msg_count,
3357 x_msg_data,
3358 '_PVT'
3359 );
3360 WHEN OTHERS THEN
3361 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3362 (
3363 l_api_name,
3364 G_PKG_NAME,
3365 'OTHERS',
3366 x_msg_count,
3367 x_msg_data,
3368 '_PVT'
3369 );
3370 END lock_row;
3371
3372 ---------------------------------------------------------------------------
3373 -- PROCEDURE update_row
3374 ---------------------------------------------------------------------------
3375 ------------------------------------
3376 -- update_row for:OKL_STRM_TYPE_B --
3377 ------------------------------------
3378 PROCEDURE update_row(
3379 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3380 x_return_status OUT NOCOPY VARCHAR2,
3381 x_msg_count OUT NOCOPY NUMBER,
3382 x_msg_data OUT NOCOPY VARCHAR2,
3383 p_sty_rec IN sty_rec_type,
3384 x_sty_rec OUT NOCOPY sty_rec_type) IS
3385
3386 l_api_version CONSTANT NUMBER := 1;
3387 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
3388 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3389 l_sty_rec sty_rec_type := p_sty_rec;
3390 l_def_sty_rec sty_rec_type;
3391 l_row_notfound BOOLEAN := TRUE;
3392 ----------------------------------
3393 -- FUNCTION populate_new_record --
3394 ----------------------------------
3395 FUNCTION populate_new_record (
3396 p_sty_rec IN sty_rec_type,
3397 x_sty_rec OUT NOCOPY sty_rec_type
3398 ) RETURN VARCHAR2 IS
3399 l_sty_rec sty_rec_type;
3400 l_row_notfound BOOLEAN := TRUE;
3401 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3402 BEGIN
3403 x_sty_rec := p_sty_rec;
3404 -- Get current database values
3405 l_sty_rec := get_rec(p_sty_rec, l_row_notfound);
3406 IF (l_row_notfound) THEN
3407 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3408 END IF;
3409 IF (x_sty_rec.id = OKC_API.G_MISS_NUM)
3410 THEN
3411 x_sty_rec.id := l_sty_rec.id;
3412 END IF;
3413 IF (x_sty_rec.version = OKC_API.G_MISS_CHAR)
3414 THEN
3415 x_sty_rec.version := l_sty_rec.version;
3416 END IF;
3417 IF (x_sty_rec.code = OKC_API.G_MISS_CHAR)
3418 THEN
3419 x_sty_rec.code := l_sty_rec.code;
3420 END IF;
3421 IF (x_sty_rec.customization_level = OKC_API.G_MISS_CHAR)
3422 THEN
3423 x_sty_rec.customization_level := l_sty_rec.customization_level;
3424 END IF;
3425 IF (x_sty_rec.stream_type_scope = OKC_API.G_MISS_CHAR)
3426 THEN
3427 x_sty_rec.stream_type_scope := l_sty_rec.stream_type_scope;
3428 END IF;
3429 IF (x_sty_rec.object_version_number = OKC_API.G_MISS_NUM)
3430 THEN
3431 x_sty_rec.object_version_number := l_sty_rec.object_version_number;
3432 END IF;
3433 IF (x_sty_rec.accrual_yn = OKC_API.G_MISS_CHAR)
3434 THEN
3435 x_sty_rec.accrual_yn := l_sty_rec.accrual_yn;
3436 END IF;
3437 IF (x_sty_rec.taxable_default_yn = OKC_API.G_MISS_CHAR)
3438 THEN
3439 x_sty_rec.taxable_default_yn := l_sty_rec.taxable_default_yn;
3440 END IF;
3441 IF (x_sty_rec.stream_type_class = OKC_API.G_MISS_CHAR)
3442 THEN
3443 x_sty_rec.stream_type_class := l_sty_rec.stream_type_class;
3444 END IF;
3445 -- hkpatel 04/15/2003
3446 IF (x_sty_rec.stream_type_subclass = OKC_API.G_MISS_CHAR)
3447 THEN
3448 x_sty_rec.stream_type_subclass := l_sty_rec.stream_type_subclass;
3449 END IF;
3450 --
3451 IF (x_sty_rec.start_date = OKC_API.G_MISS_DATE)
3452 THEN
3453 x_sty_rec.start_date := l_sty_rec.start_date;
3454 END IF;
3455 IF (x_sty_rec.end_date = OKC_API.G_MISS_DATE)
3456 THEN
3457 x_sty_rec.end_date := l_sty_rec.end_date;
3458 END IF;
3459 IF (x_sty_rec.billable_yn = OKC_API.G_MISS_CHAR)
3460 THEN
3461 x_sty_rec.billable_yn := l_sty_rec.billable_yn;
3462 END IF;
3463 IF (x_sty_rec.capitalize_yn = OKC_API.G_MISS_CHAR)
3464 THEN
3465 x_sty_rec.capitalize_yn := l_sty_rec.capitalize_yn;
3466 END IF;
3467 IF (x_sty_rec.periodic_yn = OKC_API.G_MISS_CHAR)
3468 THEN
3469 x_sty_rec.periodic_yn := l_sty_rec.periodic_yn;
3470 END IF;
3471 -- mvasudev , 05/13/2002
3472 IF (x_sty_rec.fundable_yn = OKC_API.G_MISS_CHAR)
3473 THEN
3474 x_sty_rec.fundable_yn := l_sty_rec.fundable_yn;
3475 END IF;
3476 IF (x_sty_rec.allocation_factor = OKC_API.G_MISS_CHAR)
3477 THEN
3478 x_sty_rec.allocation_factor := l_sty_rec.allocation_factor;
3479 END IF;
3480 --
3481 IF (x_sty_rec.attribute_category = OKC_API.G_MISS_CHAR)
3482 THEN
3483 x_sty_rec.attribute_category := l_sty_rec.attribute_category;
3484 END IF;
3485 IF (x_sty_rec.attribute1 = OKC_API.G_MISS_CHAR)
3486 THEN
3487 x_sty_rec.attribute1 := l_sty_rec.attribute1;
3488 END IF;
3489 IF (x_sty_rec.attribute2 = OKC_API.G_MISS_CHAR)
3490 THEN
3491 x_sty_rec.attribute2 := l_sty_rec.attribute2;
3492 END IF;
3493 IF (x_sty_rec.attribute3 = OKC_API.G_MISS_CHAR)
3494 THEN
3495 x_sty_rec.attribute3 := l_sty_rec.attribute3;
3496 END IF;
3497 IF (x_sty_rec.attribute4 = OKC_API.G_MISS_CHAR)
3498 THEN
3499 x_sty_rec.attribute4 := l_sty_rec.attribute4;
3500 END IF;
3501 IF (x_sty_rec.attribute5 = OKC_API.G_MISS_CHAR)
3502 THEN
3503 x_sty_rec.attribute5 := l_sty_rec.attribute5;
3504 END IF;
3505 IF (x_sty_rec.attribute6 = OKC_API.G_MISS_CHAR)
3506 THEN
3507 x_sty_rec.attribute6 := l_sty_rec.attribute6;
3508 END IF;
3509 IF (x_sty_rec.attribute7 = OKC_API.G_MISS_CHAR)
3510 THEN
3511 x_sty_rec.attribute7 := l_sty_rec.attribute7;
3512 END IF;
3513 IF (x_sty_rec.attribute8 = OKC_API.G_MISS_CHAR)
3514 THEN
3515 x_sty_rec.attribute8 := l_sty_rec.attribute8;
3516 END IF;
3517 IF (x_sty_rec.attribute9 = OKC_API.G_MISS_CHAR)
3518 THEN
3519 x_sty_rec.attribute9 := l_sty_rec.attribute9;
3520 END IF;
3521 IF (x_sty_rec.attribute10 = OKC_API.G_MISS_CHAR)
3522 THEN
3523 x_sty_rec.attribute10 := l_sty_rec.attribute10;
3524 END IF;
3525 IF (x_sty_rec.attribute11 = OKC_API.G_MISS_CHAR)
3526 THEN
3527 x_sty_rec.attribute11 := l_sty_rec.attribute11;
3528 END IF;
3529 IF (x_sty_rec.attribute12 = OKC_API.G_MISS_CHAR)
3530 THEN
3531 x_sty_rec.attribute12 := l_sty_rec.attribute12;
3532 END IF;
3533 IF (x_sty_rec.attribute13 = OKC_API.G_MISS_CHAR)
3534 THEN
3535 x_sty_rec.attribute13 := l_sty_rec.attribute13;
3536 END IF;
3537 IF (x_sty_rec.attribute14 = OKC_API.G_MISS_CHAR)
3538 THEN
3539 x_sty_rec.attribute14 := l_sty_rec.attribute14;
3540 END IF;
3541 IF (x_sty_rec.attribute15 = OKC_API.G_MISS_CHAR)
3542 THEN
3543 x_sty_rec.attribute15 := l_sty_rec.attribute15;
3544 END IF;
3545 IF (x_sty_rec.created_by = OKC_API.G_MISS_NUM)
3546 THEN
3547 x_sty_rec.created_by := l_sty_rec.created_by;
3548 END IF;
3549 IF (x_sty_rec.creation_date = OKC_API.G_MISS_DATE)
3550 THEN
3551 x_sty_rec.creation_date := l_sty_rec.creation_date;
3552 END IF;
3553 IF (x_sty_rec.last_updated_by = OKC_API.G_MISS_NUM)
3554 THEN
3555 x_sty_rec.last_updated_by := l_sty_rec.last_updated_by;
3556 END IF;
3557 IF (x_sty_rec.last_update_date = OKC_API.G_MISS_DATE)
3558 THEN
3559 x_sty_rec.last_update_date := l_sty_rec.last_update_date;
3560 END IF;
3561 IF (x_sty_rec.last_update_login = OKC_API.G_MISS_NUM)
3562 THEN
3563 x_sty_rec.last_update_login := l_sty_rec.last_update_login;
3564 END IF;
3565 -- Added by RGOOTY for ER 3935682: Start
3566 IF (x_sty_rec.stream_type_purpose = OKC_API.G_MISS_CHAR)
3567 THEN
3568 x_sty_rec.stream_type_purpose := l_sty_rec.stream_type_purpose;
3569 END IF;
3570 IF (x_sty_rec.contingency = OKC_API.G_MISS_CHAR)
3571 THEN
3572 x_sty_rec.contingency := l_sty_rec.contingency;
3573 END IF;
3574 -- Added by RGOOTY for ER 3935682: End
3575 -- Added by SNANDIKO for Bug 6744584 Start
3576 IF (x_sty_rec.contingency_id = OKC_API.G_MISS_NUM)
3577 THEN
3578 x_sty_rec.contingency_id := l_sty_rec.contingency_id;
3579 END IF;
3580 -- Added by SNANDIKO for Bug 6744584 End
3581 RETURN(l_return_status);
3582 END populate_new_record;
3583 ----------------------------------------
3584 -- Set_Attributes for:OKL_STRM_TYPE_B --
3585 ----------------------------------------
3586 FUNCTION Set_Attributes (
3587 p_sty_rec IN sty_rec_type,
3588 x_sty_rec OUT NOCOPY sty_rec_type
3589 ) RETURN VARCHAR2 IS
3590 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3591 BEGIN
3592 x_sty_rec := p_sty_rec;
3593 RETURN(l_return_status);
3594 END Set_Attributes;
3595 BEGIN
3596 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3597 p_init_msg_list,
3598 '_PVT',
3599 x_return_status);
3600 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3601 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3602 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3603 RAISE OKC_API.G_EXCEPTION_ERROR;
3604 END IF;
3605 --- Setting item attributes
3606 l_return_status := Set_Attributes(
3607 p_sty_rec, -- IN
3608 l_sty_rec); -- OUT
3609 --- If any errors happen abort API
3610 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3611 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3612 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3613 RAISE OKC_API.G_EXCEPTION_ERROR;
3614 END IF;
3615 l_return_status := populate_new_record(l_sty_rec, l_def_sty_rec);
3616 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3617 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3618 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3619 RAISE OKC_API.G_EXCEPTION_ERROR;
3620 END IF;
3621 UPDATE OKL_STRM_TYPE_B
3622 SET
3623 VERSION = l_def_sty_rec.version,
3624 CODE = l_def_sty_rec.code,
3625 CUSTOMIZATION_LEVEL = l_def_sty_rec.customization_level,
3626 STREAM_TYPE_SCOPE = l_def_sty_rec.stream_type_scope,
3627 OBJECT_VERSION_NUMBER = l_def_sty_rec.object_version_number,
3628 ACCRUAL_YN = l_def_sty_rec.accrual_yn,
3629 TAXABLE_DEFAULT_YN = l_def_sty_rec.taxable_default_yn,
3630 STREAM_TYPE_CLASS = l_def_sty_rec.stream_type_class,
3631 -- hkpatel 04/15/2003
3632 STREAM_TYPE_SUBCLASS = l_def_sty_rec.stream_type_subclass,
3633 --
3634 START_DATE = l_def_sty_rec.start_date,
3635 END_DATE = l_def_sty_rec.end_date,
3636 BILLABLE_YN = l_def_sty_rec.billable_yn,
3637 CAPITALIZE_YN = l_def_sty_rec.capitalize_yn,
3638 PERIODIC_YN = l_def_sty_rec.periodic_yn,
3639 -- mvasudev , 05/13/2002
3640 FUNDABLE_YN = l_def_sty_rec.fundable_yn,
3641 ALLOCATION_FACTOR = l_def_sty_rec.allocation_factor,
3642 --
3643 ATTRIBUTE_CATEGORY = l_def_sty_rec.attribute_category,
3644 ATTRIBUTE1 = l_def_sty_rec.attribute1,
3645 ATTRIBUTE2 = l_def_sty_rec.attribute2,
3646 ATTRIBUTE3 = l_def_sty_rec.attribute3,
3647 ATTRIBUTE4 = l_def_sty_rec.attribute4,
3648 ATTRIBUTE5 = l_def_sty_rec.attribute5,
3649 ATTRIBUTE6 = l_def_sty_rec.attribute6,
3650 ATTRIBUTE7 = l_def_sty_rec.attribute7,
3651 ATTRIBUTE8 = l_def_sty_rec.attribute8,
3652 ATTRIBUTE9 = l_def_sty_rec.attribute9,
3653 ATTRIBUTE10 = l_def_sty_rec.attribute10,
3654 ATTRIBUTE11 = l_def_sty_rec.attribute11,
3655 ATTRIBUTE12 = l_def_sty_rec.attribute12,
3656 ATTRIBUTE13 = l_def_sty_rec.attribute13,
3657 ATTRIBUTE14 = l_def_sty_rec.attribute14,
3658 ATTRIBUTE15 = l_def_sty_rec.attribute15,
3659 CREATED_BY = l_def_sty_rec.created_by,
3660 CREATION_DATE = l_def_sty_rec.creation_date,
3661 LAST_UPDATED_BY = l_def_sty_rec.last_updated_by,
3662 LAST_UPDATE_DATE = l_def_sty_rec.last_update_date,
3663 LAST_UPDATE_LOGIN = l_def_sty_rec.last_update_login,
3664 -- Added by RGOOTY for ER 3935682: Start
3665 STREAM_TYPE_PURPOSE = l_def_sty_rec.stream_type_purpose,
3666 CONTINGENCY = l_def_sty_rec.contingency,
3667 -- Added by RGOOTY for ER 3935682: End
3668 -- Added by SNANDIKO for Bug 6744584 Start
3669 CONTINGENCY_ID = l_def_sty_rec.contingency_id
3670 -- Added by SNANDIKO for Bug 6744584 End
3671 WHERE ID = l_def_sty_rec.id;
3672
3673 x_sty_rec := l_def_sty_rec;
3674 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3675 EXCEPTION
3676 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3677 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3678 (
3679 l_api_name,
3680 G_PKG_NAME,
3681 'OKC_API.G_RET_STS_ERROR',
3682 x_msg_count,
3683 x_msg_data,
3684 '_PVT'
3685 );
3686 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3687 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3688 (
3689 l_api_name,
3690 G_PKG_NAME,
3691 'OKC_API.G_RET_STS_UNEXP_ERROR',
3692 x_msg_count,
3693 x_msg_data,
3694 '_PVT'
3695 );
3696 WHEN OTHERS THEN
3697 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3698 (
3699 l_api_name,
3700 G_PKG_NAME,
3701 'OTHERS',
3702 x_msg_count,
3703 x_msg_data,
3704 '_PVT'
3705 );
3706 END update_row;
3707 -------------------------------------
3708 -- update_row for:OKL_STRM_TYPE_TL --
3709 -------------------------------------
3710 PROCEDURE update_row(
3711 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3712 x_return_status OUT NOCOPY VARCHAR2,
3713 x_msg_count OUT NOCOPY NUMBER,
3714 x_msg_data OUT NOCOPY VARCHAR2,
3715 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
3716 x_okl_strm_type_tl_rec OUT NOCOPY okl_strm_type_tl_rec_type) IS
3717
3718 l_api_version CONSTANT NUMBER := 1;
3719 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
3720 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3721 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type := p_okl_strm_type_tl_rec;
3722 l_def_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
3723 l_row_notfound BOOLEAN := TRUE;
3724 ----------------------------------
3725 -- FUNCTION populate_new_record --
3726 ----------------------------------
3727 FUNCTION populate_new_record (
3728 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
3729 x_okl_strm_type_tl_rec OUT NOCOPY okl_strm_type_tl_rec_type
3730 ) RETURN VARCHAR2 IS
3731 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
3732 l_row_notfound BOOLEAN := TRUE;
3733 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3734 BEGIN
3735 x_okl_strm_type_tl_rec := p_okl_strm_type_tl_rec;
3736 -- Get current database values
3737 l_okl_strm_type_tl_rec := get_rec(p_okl_strm_type_tl_rec, l_row_notfound);
3738 IF (l_row_notfound) THEN
3739 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3740 END IF;
3741 IF (x_okl_strm_type_tl_rec.id = OKC_API.G_MISS_NUM)
3742 THEN
3743 x_okl_strm_type_tl_rec.id := l_okl_strm_type_tl_rec.id;
3744 END IF;
3745 IF (x_okl_strm_type_tl_rec.language = OKC_API.G_MISS_CHAR)
3746 THEN
3747 x_okl_strm_type_tl_rec.language := l_okl_strm_type_tl_rec.language;
3748 END IF;
3749 IF (x_okl_strm_type_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
3750 THEN
3751 x_okl_strm_type_tl_rec.source_lang := l_okl_strm_type_tl_rec.source_lang;
3752 END IF;
3753 IF (x_okl_strm_type_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3754 THEN
3755 x_okl_strm_type_tl_rec.sfwt_flag := l_okl_strm_type_tl_rec.sfwt_flag;
3756 END IF;
3757 IF (x_okl_strm_type_tl_rec.name = OKC_API.G_MISS_CHAR)
3758 THEN
3759 x_okl_strm_type_tl_rec.name := l_okl_strm_type_tl_rec.name;
3760 END IF;
3761 IF (x_okl_strm_type_tl_rec.description = OKC_API.G_MISS_CHAR)
3762 THEN
3763 x_okl_strm_type_tl_rec.description := l_okl_strm_type_tl_rec.description;
3764 END IF;
3765 IF (x_okl_strm_type_tl_rec.created_by = OKC_API.G_MISS_NUM)
3766 THEN
3767 x_okl_strm_type_tl_rec.created_by := l_okl_strm_type_tl_rec.created_by;
3768 END IF;
3769 IF (x_okl_strm_type_tl_rec.creation_date = OKC_API.G_MISS_DATE)
3770 THEN
3771 x_okl_strm_type_tl_rec.creation_date := l_okl_strm_type_tl_rec.creation_date;
3772 END IF;
3773 IF (x_okl_strm_type_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
3774 THEN
3775 x_okl_strm_type_tl_rec.last_updated_by := l_okl_strm_type_tl_rec.last_updated_by;
3776 END IF;
3777 IF (x_okl_strm_type_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
3778 THEN
3779 x_okl_strm_type_tl_rec.last_update_date := l_okl_strm_type_tl_rec.last_update_date;
3780 END IF;
3781 IF (x_okl_strm_type_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
3782 THEN
3783 x_okl_strm_type_tl_rec.last_update_login := l_okl_strm_type_tl_rec.last_update_login;
3784 END IF;
3785 -- Added by RGOOTY for ER 3935682: Start
3786 IF (x_okl_strm_type_tl_rec.short_description= OKC_API.G_MISS_CHAR)
3787 THEN
3788 x_okl_strm_type_tl_rec.short_description := l_okl_strm_type_tl_rec.short_description;
3789 END IF;
3790 -- Added by RGOOTY for ER 3935682: End
3791 RETURN(l_return_status);
3792 END populate_new_record;
3793 -----------------------------------------
3794 -- Set_Attributes for:OKL_STRM_TYPE_TL --
3795 -----------------------------------------
3796 FUNCTION Set_Attributes (
3797 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
3798 x_okl_strm_type_tl_rec OUT NOCOPY okl_strm_type_tl_rec_type
3799 ) RETURN VARCHAR2 IS
3800 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3801 BEGIN
3802 x_okl_strm_type_tl_rec := p_okl_strm_type_tl_rec;
3803 x_okl_strm_type_tl_rec.LANGUAGE := USERENV('LANG');
3804 x_okl_strm_type_tl_rec.SOURCE_LANG := USERENV('LANG');
3805 RETURN(l_return_status);
3806 END Set_Attributes;
3807 BEGIN
3808 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3809 p_init_msg_list,
3810 '_PVT',
3811 x_return_status);
3812 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3813 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3814 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3815 RAISE OKC_API.G_EXCEPTION_ERROR;
3816 END IF;
3817 --- Setting item attributes
3818 l_return_status := Set_Attributes(
3819 p_okl_strm_type_tl_rec, -- IN
3820 l_okl_strm_type_tl_rec); -- OUT
3821 --- If any errors happen abort API
3822 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3823 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3824 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3825 RAISE OKC_API.G_EXCEPTION_ERROR;
3826 END IF;
3827 l_return_status := populate_new_record(l_okl_strm_type_tl_rec, l_def_okl_strm_type_tl_rec);
3828 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3829 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3830 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3831 RAISE OKC_API.G_EXCEPTION_ERROR;
3832 END IF;
3833 UPDATE OKL_STRM_TYPE_TL
3834 SET NAME = l_def_okl_strm_type_tl_rec.name,
3835 DESCRIPTION = l_def_okl_strm_type_tl_rec.description,
3836 CREATED_BY = l_def_okl_strm_type_tl_rec.created_by,
3837 SOURCE_LANG = l_def_okl_strm_type_tl_rec.source_lang,
3838 CREATION_DATE = l_def_okl_strm_type_tl_rec.creation_date,
3839 LAST_UPDATED_BY = l_def_okl_strm_type_tl_rec.last_updated_by,
3840 LAST_UPDATE_DATE = l_def_okl_strm_type_tl_rec.last_update_date,
3841 LAST_UPDATE_LOGIN = l_def_okl_strm_type_tl_rec.last_update_login,
3842 -- Added by RGOOTY for ER 3935682: Start
3843 SHORT_DESCRIPTION = l_def_okl_strm_type_tl_rec.short_description
3844 -- Added by RGOOTY for ER 3935682: End
3845 WHERE ID = l_def_okl_strm_type_tl_rec.id
3846 AND USERENV('LANG') in (SOURCE_LANG, LANGUAGE);
3847 -- AND SOURCE_LANG = USERENV('LANG');
3848
3849 UPDATE OKL_STRM_TYPE_TL
3850 SET SFWT_FLAG = 'Y'
3851 WHERE ID = l_def_okl_strm_type_tl_rec.id
3852 AND SOURCE_LANG <> USERENV('LANG');
3853
3854 x_okl_strm_type_tl_rec := l_def_okl_strm_type_tl_rec;
3855 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3856 EXCEPTION
3857 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3858 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3859 (
3860 l_api_name,
3861 G_PKG_NAME,
3862 'OKC_API.G_RET_STS_ERROR',
3863 x_msg_count,
3864 x_msg_data,
3865 '_PVT'
3866 );
3867 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3868 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3869 (
3870 l_api_name,
3871 G_PKG_NAME,
3872 'OKC_API.G_RET_STS_UNEXP_ERROR',
3873 x_msg_count,
3874 x_msg_data,
3875 '_PVT'
3876 );
3877 WHEN OTHERS THEN
3878 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3879 (
3880 l_api_name,
3881 G_PKG_NAME,
3882 'OTHERS',
3883 x_msg_count,
3884 x_msg_data,
3885 '_PVT'
3886 );
3887 END update_row;
3888 ------------------------------------
3889 -- update_row for:OKL_STRM_TYPE_V --
3890 ------------------------------------
3891 PROCEDURE update_row(
3892 p_api_version IN NUMBER,
3893 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3894 x_return_status OUT NOCOPY VARCHAR2,
3895 x_msg_count OUT NOCOPY NUMBER,
3896 x_msg_data OUT NOCOPY VARCHAR2,
3897 p_styv_rec IN styv_rec_type,
3898 x_styv_rec OUT NOCOPY styv_rec_type) IS
3899
3900 l_api_version CONSTANT NUMBER := 1;
3901 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3902 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3903 l_styv_rec styv_rec_type := p_styv_rec;
3904 l_def_styv_rec styv_rec_type;
3905 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
3906 lx_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
3907 l_sty_rec sty_rec_type;
3908 lx_sty_rec sty_rec_type;
3909 -------------------------------
3910 -- FUNCTION fill_who_columns --
3911 -------------------------------
3912 FUNCTION fill_who_columns (
3913 p_styv_rec IN styv_rec_type
3914 ) RETURN styv_rec_type IS
3915 l_styv_rec styv_rec_type := p_styv_rec;
3916 BEGIN
3917 l_styv_rec.LAST_UPDATE_DATE := SYSDATE;
3918 l_styv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3919 l_styv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3920 RETURN(l_styv_rec);
3921 END fill_who_columns;
3922 ----------------------------------
3923 -- FUNCTION populate_new_record --
3924 ----------------------------------
3925 FUNCTION populate_new_record (
3926 p_styv_rec IN styv_rec_type,
3927 x_styv_rec OUT NOCOPY styv_rec_type
3928 ) RETURN VARCHAR2 IS
3929 l_styv_rec styv_rec_type;
3930 l_row_notfound BOOLEAN := TRUE;
3931 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3932 BEGIN
3933 x_styv_rec := p_styv_rec;
3934 -- Get current database values
3935 l_styv_rec := get_rec(p_styv_rec, l_row_notfound);
3936 IF (l_row_notfound) THEN
3937 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3938 END IF;
3939 IF (x_styv_rec.id = OKC_API.G_MISS_NUM)
3940 THEN
3941 x_styv_rec.id := l_styv_rec.id;
3942 END IF;
3943 IF (x_styv_rec.name = OKC_API.G_MISS_CHAR)
3944 THEN
3945 x_styv_rec.name := l_styv_rec.name;
3946 END IF;
3947 IF (x_styv_rec.version = OKC_API.G_MISS_CHAR)
3948 THEN
3949 x_styv_rec.version := l_styv_rec.version;
3950 END IF;
3951 IF (x_styv_rec.object_version_number = OKC_API.G_MISS_NUM)
3952 THEN
3953 x_styv_rec.object_version_number := l_styv_rec.object_version_number;
3954 END IF;
3955 IF (x_styv_rec.code = OKC_API.G_MISS_CHAR)
3956 THEN
3957 x_styv_rec.code := l_styv_rec.code;
3958 END IF;
3959 IF (x_styv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3960 THEN
3961 x_styv_rec.sfwt_flag := l_styv_rec.sfwt_flag;
3962 END IF;
3963 IF (x_styv_rec.stream_type_scope = OKC_API.G_MISS_CHAR)
3964 THEN
3965 x_styv_rec.stream_type_scope := l_styv_rec.stream_type_scope;
3966 END IF;
3967 IF (x_styv_rec.description = OKC_API.G_MISS_CHAR)
3968 THEN
3969 x_styv_rec.description := l_styv_rec.description;
3970 END IF;
3971 IF (x_styv_rec.start_date = OKC_API.G_MISS_DATE)
3972 THEN
3973 x_styv_rec.start_date := l_styv_rec.start_date;
3974 END IF;
3975 IF (x_styv_rec.end_date = OKC_API.G_MISS_DATE)
3976 THEN
3977 x_styv_rec.end_date := l_styv_rec.end_date;
3978 END IF;
3979 IF (x_styv_rec.billable_yn = OKC_API.G_MISS_CHAR)
3980 THEN
3981 x_styv_rec.billable_yn := l_styv_rec.billable_yn;
3982 END IF;
3983 IF (x_styv_rec.capitalize_yn = OKC_API.G_MISS_CHAR)
3984 THEN
3985 x_styv_rec.capitalize_yn := l_styv_rec.capitalize_yn;
3986 END IF;
3987 IF (x_styv_rec.periodic_yn = OKC_API.G_MISS_CHAR)
3988 THEN
3989 x_styv_rec.periodic_yn := l_styv_rec.periodic_yn;
3990 END IF;
3991 -- mvasudev , 05/13/2002
3992 IF (x_styv_rec.fundable_yn = OKC_API.G_MISS_CHAR)
3993 THEN
3994 x_styv_rec.fundable_yn := l_styv_rec.fundable_yn;
3995 END IF;
3996 IF (x_styv_rec.allocation_factor = OKC_API.G_MISS_CHAR)
3997 THEN
3998 x_styv_rec.allocation_factor := l_styv_rec.allocation_factor;
3999 END IF;
4000 --
4001 IF (x_styv_rec.taxable_default_yn = OKC_API.G_MISS_CHAR)
4002 THEN
4003 x_styv_rec.taxable_default_yn := l_styv_rec.taxable_default_yn;
4004 END IF;
4005 IF (x_styv_rec.customization_level = OKC_API.G_MISS_CHAR)
4006 THEN
4007 x_styv_rec.customization_level := l_styv_rec.customization_level;
4008 END IF;
4009 IF (x_styv_rec.stream_type_class = OKC_API.G_MISS_CHAR)
4010 THEN
4011 x_styv_rec.stream_type_class := l_styv_rec.stream_type_class;
4012 END IF;
4013 -- hkpatel 04/15/2003
4014 IF (x_styv_rec.stream_type_subclass = OKC_API.G_MISS_CHAR)
4015 THEN
4016 x_styv_rec.stream_type_subclass := l_styv_rec.stream_type_subclass;
4017 END IF;
4018 --
4019 IF (x_styv_rec.accrual_yn = OKC_API.G_MISS_CHAR)
4020 THEN
4021 x_styv_rec.accrual_yn := l_styv_rec.accrual_yn;
4022 END IF;
4023 IF (x_styv_rec.attribute_category = OKC_API.G_MISS_CHAR)
4024 THEN
4025 x_styv_rec.attribute_category := l_styv_rec.attribute_category;
4026 END IF;
4027 IF (x_styv_rec.attribute1 = OKC_API.G_MISS_CHAR)
4028 THEN
4029 x_styv_rec.attribute1 := l_styv_rec.attribute1;
4030 END IF;
4031 IF (x_styv_rec.attribute2 = OKC_API.G_MISS_CHAR)
4032 THEN
4033 x_styv_rec.attribute2 := l_styv_rec.attribute2;
4034 END IF;
4035 IF (x_styv_rec.attribute3 = OKC_API.G_MISS_CHAR)
4036 THEN
4037 x_styv_rec.attribute3 := l_styv_rec.attribute3;
4038 END IF;
4039 IF (x_styv_rec.attribute4 = OKC_API.G_MISS_CHAR)
4040 THEN
4041 x_styv_rec.attribute4 := l_styv_rec.attribute4;
4042 END IF;
4043 IF (x_styv_rec.attribute5 = OKC_API.G_MISS_CHAR)
4044 THEN
4045 x_styv_rec.attribute5 := l_styv_rec.attribute5;
4046 END IF;
4047 IF (x_styv_rec.attribute6 = OKC_API.G_MISS_CHAR)
4048 THEN
4049 x_styv_rec.attribute6 := l_styv_rec.attribute6;
4050 END IF;
4051 IF (x_styv_rec.attribute7 = OKC_API.G_MISS_CHAR)
4052 THEN
4053 x_styv_rec.attribute7 := l_styv_rec.attribute7;
4054 END IF;
4055 IF (x_styv_rec.attribute8 = OKC_API.G_MISS_CHAR)
4056 THEN
4057 x_styv_rec.attribute8 := l_styv_rec.attribute8;
4058 END IF;
4059 IF (x_styv_rec.attribute9 = OKC_API.G_MISS_CHAR)
4060 THEN
4061 x_styv_rec.attribute9 := l_styv_rec.attribute9;
4062 END IF;
4063 IF (x_styv_rec.attribute10 = OKC_API.G_MISS_CHAR)
4064 THEN
4065 x_styv_rec.attribute10 := l_styv_rec.attribute10;
4066 END IF;
4067 IF (x_styv_rec.attribute11 = OKC_API.G_MISS_CHAR)
4068 THEN
4069 x_styv_rec.attribute11 := l_styv_rec.attribute11;
4070 END IF;
4071 IF (x_styv_rec.attribute12 = OKC_API.G_MISS_CHAR)
4072 THEN
4073 x_styv_rec.attribute12 := l_styv_rec.attribute12;
4074 END IF;
4075 IF (x_styv_rec.attribute13 = OKC_API.G_MISS_CHAR)
4076 THEN
4077 x_styv_rec.attribute13 := l_styv_rec.attribute13;
4078 END IF;
4079 IF (x_styv_rec.attribute14 = OKC_API.G_MISS_CHAR)
4080 THEN
4081 x_styv_rec.attribute14 := l_styv_rec.attribute14;
4082 END IF;
4083 IF (x_styv_rec.attribute15 = OKC_API.G_MISS_CHAR)
4084 THEN
4085 x_styv_rec.attribute15 := l_styv_rec.attribute15;
4086 END IF;
4087 IF (x_styv_rec.created_by = OKC_API.G_MISS_NUM)
4088 THEN
4089 x_styv_rec.created_by := l_styv_rec.created_by;
4090 END IF;
4091 IF (x_styv_rec.creation_date = OKC_API.G_MISS_DATE)
4092 THEN
4093 x_styv_rec.creation_date := l_styv_rec.creation_date;
4094 END IF;
4095 IF (x_styv_rec.last_updated_by = OKC_API.G_MISS_NUM)
4096 THEN
4097 x_styv_rec.last_updated_by := l_styv_rec.last_updated_by;
4098 END IF;
4099 IF (x_styv_rec.last_update_date = OKC_API.G_MISS_DATE)
4100 THEN
4101 x_styv_rec.last_update_date := l_styv_rec.last_update_date;
4102 END IF;
4103 IF (x_styv_rec.last_update_login = OKC_API.G_MISS_NUM)
4104 THEN
4105 x_styv_rec.last_update_login := l_styv_rec.last_update_login;
4106 END IF;
4107 -- Added by RGOOTY for ER 3935682: Start
4108 IF (x_styv_rec.stream_type_purpose = OKC_API.G_MISS_CHAR)
4109 THEN
4110 x_styv_rec.stream_type_purpose := l_styv_rec.stream_type_purpose;
4111 END IF;
4112 IF (x_styv_rec.contingency = OKC_API.G_MISS_CHAR)
4113 THEN
4114 x_styv_rec.contingency := l_styv_rec.contingency;
4115 END IF;
4116 IF (x_styv_rec.short_description = OKC_API.G_MISS_CHAR)
4117 THEN
4118 x_styv_rec.short_description := l_styv_rec.short_description;
4119 END IF;
4120 -- Added by RGOOTY for ER 3935682: End
4121 -- Added by SNANDIKO for Bug 6744584 Start
4122 IF (x_styv_rec.contingency_id = OKC_API.G_MISS_NUM)
4123 THEN
4124 x_styv_rec.contingency_id := l_styv_rec.contingency_id;
4125 END IF;
4126 -- Added by SNANDIKO for Bug 6744584 End
4127 RETURN(l_return_status);
4128 END populate_new_record;
4129 ----------------------------------------
4130 -- Set_Attributes for:OKL_STRM_TYPE_V --
4131 ----------------------------------------
4132 FUNCTION Set_Attributes (
4133 p_styv_rec IN styv_rec_type,
4134 x_styv_rec OUT NOCOPY styv_rec_type
4135 ) RETURN VARCHAR2 IS
4136 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4137 BEGIN
4138 x_styv_rec := p_styv_rec;
4139 x_styv_rec.OBJECT_VERSION_NUMBER := NVL(x_styv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
4140 RETURN(l_return_status);
4141 END Set_Attributes;
4142 BEGIN
4143 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4144 G_PKG_NAME,
4145 p_init_msg_list,
4146 l_api_version,
4147 p_api_version,
4148 '_PVT',
4149 x_return_status);
4150 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4151 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4152 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4153 RAISE OKC_API.G_EXCEPTION_ERROR;
4154 END IF;
4155 --- Setting item attributes
4156 l_return_status := Set_Attributes(
4157 p_styv_rec, -- IN
4158 l_styv_rec); -- OUT
4159 --- If any errors happen abort API
4160 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4161 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4162 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4163 RAISE OKC_API.G_EXCEPTION_ERROR;
4164 END IF;
4165 l_return_status := populate_new_record(l_styv_rec, l_def_styv_rec);
4166 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4167 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4168 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4169 RAISE OKC_API.G_EXCEPTION_ERROR;
4170 END IF;
4171 l_def_styv_rec := fill_who_columns(l_def_styv_rec);
4172 --- Validate all non-missing attributes (Item Level Validation)
4173 l_return_status := Validate_Attributes(l_def_styv_rec);
4174 --- If any errors happen abort API
4175 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4177 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4178 RAISE OKC_API.G_EXCEPTION_ERROR;
4179 END IF;
4180 l_return_status := Validate_Record(l_def_styv_rec);
4181 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4182 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4183 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4184 RAISE OKC_API.G_EXCEPTION_ERROR;
4185 END IF;
4186
4187 --------------------------------------
4188 -- Move VIEW record to "Child" records
4189 --------------------------------------
4190 migrate(l_def_styv_rec, l_okl_strm_type_tl_rec);
4191 migrate(l_def_styv_rec, l_sty_rec);
4192 --------------------------------------------
4193 -- Call the UPDATE_ROW for each child record
4194 --------------------------------------------
4195 update_row(
4196 p_init_msg_list,
4197 x_return_status,
4198 x_msg_count,
4199 x_msg_data,
4200 l_okl_strm_type_tl_rec,
4201 lx_okl_strm_type_tl_rec
4202 );
4203 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4204 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4205 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4206 RAISE OKC_API.G_EXCEPTION_ERROR;
4207 END IF;
4208 migrate(lx_okl_strm_type_tl_rec, l_def_styv_rec);
4209 update_row(
4210 p_init_msg_list,
4211 x_return_status,
4212 x_msg_count,
4213 x_msg_data,
4214 l_sty_rec,
4215 lx_sty_rec
4216 );
4217 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4218 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4219 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4220 RAISE OKC_API.G_EXCEPTION_ERROR;
4221 END IF;
4222 migrate(lx_sty_rec, l_def_styv_rec);
4223 x_styv_rec := l_def_styv_rec;
4224 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4225 EXCEPTION
4226 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4227 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4228 (
4229 l_api_name,
4230 G_PKG_NAME,
4231 'OKC_API.G_RET_STS_ERROR',
4232 x_msg_count,
4233 x_msg_data,
4234 '_PVT'
4235 );
4236 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4237 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4238 (
4239 l_api_name,
4240 G_PKG_NAME,
4241 'OKC_API.G_RET_STS_UNEXP_ERROR',
4242 x_msg_count,
4243 x_msg_data,
4244 '_PVT'
4245 );
4246 WHEN OTHERS THEN
4247 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4248 (
4249 l_api_name,
4250 G_PKG_NAME,
4251 'OTHERS',
4252 x_msg_count,
4253 x_msg_data,
4254 '_PVT'
4255 );
4256 END update_row;
4257 ----------------------------------------
4258 -- PL/SQL TBL update_row for:STYV_TBL --
4259 ----------------------------------------
4260 PROCEDURE update_row(
4261 p_api_version IN NUMBER,
4262 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4263 x_return_status OUT NOCOPY VARCHAR2,
4264 x_msg_count OUT NOCOPY NUMBER,
4265 x_msg_data OUT NOCOPY VARCHAR2,
4266 p_styv_tbl IN styv_tbl_type,
4267 x_styv_tbl OUT NOCOPY styv_tbl_type) IS
4268
4269 l_api_version CONSTANT NUMBER := 1;
4270 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
4271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4272 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
4273 i NUMBER := 0;
4274 BEGIN
4275 OKC_API.init_msg_list(p_init_msg_list);
4276 -- Make sure PL/SQL table has records in it before passing
4277 IF (p_styv_tbl.COUNT > 0) THEN
4278 i := p_styv_tbl.FIRST;
4279 LOOP
4280 update_row (
4281 p_api_version => p_api_version,
4282 p_init_msg_list => OKC_API.G_FALSE,
4283 x_return_status => x_return_status,
4284 x_msg_count => x_msg_count,
4285 x_msg_data => x_msg_data,
4286 p_styv_rec => p_styv_tbl(i),
4287 x_styv_rec => x_styv_tbl(i));
4288 /* Begin Post Generation Change */
4289 -- store the highest degree of error
4290 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4291 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
4292 l_overall_status := x_return_status;
4293 END IF;
4294 END IF;
4295 /* End Post Generation Change */
4296
4297 EXIT WHEN (i = p_styv_tbl.LAST);
4298 i := p_styv_tbl.NEXT(i);
4299 END LOOP;
4300
4301 -- return the overall status
4302 x_return_status :=l_overall_status;
4303 END IF;
4304 EXCEPTION
4305 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4306 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4307 (
4308 l_api_name,
4309 G_PKG_NAME,
4310 'OKC_API.G_RET_STS_ERROR',
4311 x_msg_count,
4312 x_msg_data,
4313 '_PVT'
4314 );
4315 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4316 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4317 (
4318 l_api_name,
4319 G_PKG_NAME,
4320 'OKC_API.G_RET_STS_UNEXP_ERROR',
4321 x_msg_count,
4322 x_msg_data,
4323 '_PVT'
4324 );
4325 WHEN OTHERS THEN
4326 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4327 (
4328 l_api_name,
4329 G_PKG_NAME,
4330 'OTHERS',
4331 x_msg_count,
4332 x_msg_data,
4333 '_PVT'
4334 );
4335 END update_row;
4336
4337 ---------------------------------------------------------------------------
4338 -- PROCEDURE delete_row
4339 ---------------------------------------------------------------------------
4340 ------------------------------------
4341 -- delete_row for:OKL_STRM_TYPE_B --
4342 ------------------------------------
4343 PROCEDURE delete_row(
4344 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4345 x_return_status OUT NOCOPY VARCHAR2,
4346 x_msg_count OUT NOCOPY NUMBER,
4347 x_msg_data OUT NOCOPY VARCHAR2,
4348 p_sty_rec IN sty_rec_type) IS
4349
4350 l_api_version CONSTANT NUMBER := 1;
4351 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
4352 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4353 l_sty_rec sty_rec_type:= p_sty_rec;
4354 l_row_notfound BOOLEAN := TRUE;
4355 BEGIN
4356 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4357 p_init_msg_list,
4358 '_PVT',
4359 x_return_status);
4360 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4361 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4362 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4363 RAISE OKC_API.G_EXCEPTION_ERROR;
4364 END IF;
4365 DELETE FROM OKL_STRM_TYPE_B
4366 WHERE ID = l_sty_rec.id;
4367
4368 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4369 EXCEPTION
4370 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4371 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4372 (
4373 l_api_name,
4374 G_PKG_NAME,
4375 'OKC_API.G_RET_STS_ERROR',
4376 x_msg_count,
4377 x_msg_data,
4378 '_PVT'
4379 );
4380 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4381 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4382 (
4383 l_api_name,
4384 G_PKG_NAME,
4385 'OKC_API.G_RET_STS_UNEXP_ERROR',
4386 x_msg_count,
4387 x_msg_data,
4388 '_PVT'
4389 );
4390 WHEN OTHERS THEN
4391 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4392 (
4393 l_api_name,
4394 G_PKG_NAME,
4395 'OTHERS',
4396 x_msg_count,
4397 x_msg_data,
4398 '_PVT'
4399 );
4400 END delete_row;
4401 -------------------------------------
4402 -- delete_row for:OKL_STRM_TYPE_TL --
4403 -------------------------------------
4404 PROCEDURE delete_row(
4405 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4406 x_return_status OUT NOCOPY VARCHAR2,
4407 x_msg_count OUT NOCOPY NUMBER,
4408 x_msg_data OUT NOCOPY VARCHAR2,
4409 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type) IS
4410
4411 l_api_version CONSTANT NUMBER := 1;
4412 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
4413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4414 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type:= p_okl_strm_type_tl_rec;
4415 l_row_notfound BOOLEAN := TRUE;
4416 -----------------------------------------
4417 -- Set_Attributes for:OKL_STRM_TYPE_TL --
4418 -----------------------------------------
4419 FUNCTION Set_Attributes (
4420 p_okl_strm_type_tl_rec IN okl_strm_type_tl_rec_type,
4421 x_okl_strm_type_tl_rec OUT NOCOPY okl_strm_type_tl_rec_type
4422 ) RETURN VARCHAR2 IS
4423 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4424 BEGIN
4425 x_okl_strm_type_tl_rec := p_okl_strm_type_tl_rec;
4426 x_okl_strm_type_tl_rec.LANGUAGE := USERENV('LANG');
4427 RETURN(l_return_status);
4428 END Set_Attributes;
4429 BEGIN
4430 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4431 p_init_msg_list,
4432 '_PVT',
4433 x_return_status);
4434 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4435 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4436 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4437 RAISE OKC_API.G_EXCEPTION_ERROR;
4438 END IF;
4439 --- Setting item attributes
4440 l_return_status := Set_Attributes(
4441 p_okl_strm_type_tl_rec, -- IN
4442 l_okl_strm_type_tl_rec); -- OUT
4443 --- If any errors happen abort API
4444 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4445 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4446 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4447 RAISE OKC_API.G_EXCEPTION_ERROR;
4448 END IF;
4449 DELETE FROM OKL_STRM_TYPE_TL
4450 WHERE ID = l_okl_strm_type_tl_rec.id;
4451
4452 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4453 EXCEPTION
4454 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4455 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4456 (
4457 l_api_name,
4458 G_PKG_NAME,
4459 'OKC_API.G_RET_STS_ERROR',
4460 x_msg_count,
4461 x_msg_data,
4462 '_PVT'
4463 );
4464 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4465 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4466 (
4467 l_api_name,
4468 G_PKG_NAME,
4469 'OKC_API.G_RET_STS_UNEXP_ERROR',
4470 x_msg_count,
4471 x_msg_data,
4472 '_PVT'
4473 );
4474 WHEN OTHERS THEN
4475 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4476 (
4477 l_api_name,
4478 G_PKG_NAME,
4479 'OTHERS',
4480 x_msg_count,
4481 x_msg_data,
4482 '_PVT'
4483 );
4484 END delete_row;
4485 ------------------------------------
4486 -- delete_row for:OKL_STRM_TYPE_V --
4487 ------------------------------------
4488 PROCEDURE delete_row(
4489 p_api_version IN NUMBER,
4490 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4491 x_return_status OUT NOCOPY VARCHAR2,
4492 x_msg_count OUT NOCOPY NUMBER,
4493 x_msg_data OUT NOCOPY VARCHAR2,
4494 p_styv_rec IN styv_rec_type) IS
4495
4496 l_api_version CONSTANT NUMBER := 1;
4497 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
4498 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4499 l_styv_rec styv_rec_type := p_styv_rec;
4500 l_okl_strm_type_tl_rec okl_strm_type_tl_rec_type;
4501 l_sty_rec sty_rec_type;
4502 BEGIN
4503 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4504 G_PKG_NAME,
4505 p_init_msg_list,
4506 l_api_version,
4507 p_api_version,
4508 '_PVT',
4509 x_return_status);
4510 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4511 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4512 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4513 RAISE OKC_API.G_EXCEPTION_ERROR;
4514 END IF;
4515 --------------------------------------
4516 -- Move VIEW record to "Child" records
4517 --------------------------------------
4518 migrate(l_styv_rec, l_okl_strm_type_tl_rec);
4519 migrate(l_styv_rec, l_sty_rec);
4520 --------------------------------------------
4521 -- Call the DELETE_ROW for each child record
4522 --------------------------------------------
4523 delete_row(
4524 p_init_msg_list,
4525 x_return_status,
4526 x_msg_count,
4527 x_msg_data,
4528 l_okl_strm_type_tl_rec
4529 );
4530 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4531 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4532 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4533 RAISE OKC_API.G_EXCEPTION_ERROR;
4534 END IF;
4535 delete_row(
4536 p_init_msg_list,
4537 x_return_status,
4538 x_msg_count,
4539 x_msg_data,
4540 l_sty_rec
4541 );
4542 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4543 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4544 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4545 RAISE OKC_API.G_EXCEPTION_ERROR;
4546 END IF;
4547 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4548 EXCEPTION
4549 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4550 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4551 (
4552 l_api_name,
4553 G_PKG_NAME,
4554 'OKC_API.G_RET_STS_ERROR',
4555 x_msg_count,
4556 x_msg_data,
4557 '_PVT'
4558 );
4559 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4560 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4561 (
4562 l_api_name,
4563 G_PKG_NAME,
4564 'OKC_API.G_RET_STS_UNEXP_ERROR',
4565 x_msg_count,
4566 x_msg_data,
4567 '_PVT'
4568 );
4569 WHEN OTHERS THEN
4570 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4571 (
4572 l_api_name,
4573 G_PKG_NAME,
4574 'OTHERS',
4575 x_msg_count,
4576 x_msg_data,
4577 '_PVT'
4578 );
4579 END delete_row;
4580 ----------------------------------------
4581 -- PL/SQL TBL delete_row for:STYV_TBL --
4582 ----------------------------------------
4583 PROCEDURE delete_row(
4584 p_api_version IN NUMBER,
4585 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4586 x_return_status OUT NOCOPY VARCHAR2,
4587 x_msg_count OUT NOCOPY NUMBER,
4588 x_msg_data OUT NOCOPY VARCHAR2,
4589 p_styv_tbl IN styv_tbl_type) IS
4590
4591 l_api_version CONSTANT NUMBER := 1;
4592 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
4593 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4594 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
4595
4596 i NUMBER := 0;
4597 BEGIN
4598 OKC_API.init_msg_list(p_init_msg_list);
4599 -- Make sure PL/SQL table has records in it before passing
4600 IF (p_styv_tbl.COUNT > 0) THEN
4601 i := p_styv_tbl.FIRST;
4602 LOOP
4603 delete_row (
4604 p_api_version => p_api_version,
4605 p_init_msg_list => OKC_API.G_FALSE,
4606 x_return_status => x_return_status,
4607 x_msg_count => x_msg_count,
4608 x_msg_data => x_msg_data,
4609 p_styv_rec => p_styv_tbl(i));
4610
4611 /* Begin Post Generation Change */
4612 -- store the highest degree of error
4613 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
4614 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
4615 l_overall_status := x_return_status;
4616 END IF;
4617 END IF;
4618 /* End Post Generation Change */
4619
4620 EXIT WHEN (i = p_styv_tbl.LAST);
4621 i := p_styv_tbl.NEXT(i);
4622 END LOOP;
4623
4624 -- return the overall status
4625 x_return_status :=l_overall_status;
4626 END IF;
4627 EXCEPTION
4628 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4629 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4630 (
4631 l_api_name,
4632 G_PKG_NAME,
4633 'OKC_API.G_RET_STS_ERROR',
4634 x_msg_count,
4635 x_msg_data,
4636 '_PVT'
4637 );
4638 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4639 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4640 (
4641 l_api_name,
4642 G_PKG_NAME,
4643 'OKC_API.G_RET_STS_UNEXP_ERROR',
4644 x_msg_count,
4645 x_msg_data,
4646 '_PVT'
4647 );
4648 WHEN OTHERS THEN
4649 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4650 (
4651 l_api_name,
4652 G_PKG_NAME,
4653 'OTHERS',
4654 x_msg_count,
4655 x_msg_data,
4656 '_PVT'
4657 );
4658 END delete_row;
4659
4660
4661 -------------------------------------------------------------------------------
4662 -- Procedure TRANSLATE_ROW
4663 -------------------------------------------------------------------------------
4664 PROCEDURE TRANSLATE_ROW(p_styv_rec IN styv_rec_type,
4665 p_owner IN VARCHAR2,
4666 p_last_update_date IN VARCHAR2,
4667 x_return_status OUT NOCOPY VARCHAR2) IS
4668 f_luby NUMBER; -- entity owner in file
4669 f_ludate DATE; -- entity update date in file
4670 db_luby NUMBER; -- entity owner in db
4671 db_ludate DATE; -- entity update date in db
4672
4673 BEGIN
4674 -- Translate owner to file_last_updated_by
4675 f_luby := fnd_load_util.owner_id(p_owner);
4676
4677 -- Translate char last_update_date to date
4678 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
4679
4680 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
4681 INTO db_luby, db_ludate
4682 FROM OKL_STRM_TYPE_TL
4683 where ID = to_number(p_styv_rec.id)
4684 and USERENV('LANG') =language;
4685
4686 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
4687 db_ludate, '')) then
4688 UPDATE OKL_STRM_TYPE_TL
4689 SET
4690 ID = TO_NUMBER(p_styv_rec.id),
4691 DESCRIPTION = p_styv_rec.description,
4692 NAME = p_styv_rec.name,
4693 LAST_UPDATE_DATE = f_ludate,
4694 LAST_UPDATED_BY = f_luby,
4695 LAST_UPDATE_LOGIN = 0,
4696 SOURCE_LANG = USERENV('LANG')
4697 WHERE ID = to_number(p_styv_rec.id)
4698 AND USERENV('LANG') IN (language,source_lang);
4699 END IF;
4700 END TRANSLATE_ROW;
4701
4702 -------------------------------------------------------------------------------
4703 -- Procedure LOAD_ROW
4704 -------------------------------------------------------------------------------
4705
4706 PROCEDURE LOAD_ROW(p_styv_rec IN styv_rec_type,
4707 p_owner IN VARCHAR2,
4708 p_last_update_date IN VARCHAR2,
4709 x_return_status OUT NOCOPY VARCHAR2) IS
4710 id NUMBER;
4711 f_luby NUMBER; -- entity owner in file
4712 f_ludate DATE; -- entity update date in file
4713 db_luby NUMBER; -- entity owner in db
4714 db_ludate DATE; -- entity update date in db
4715 BEGIN
4716
4717 -- Translate owner to file_last_updated_by
4718 f_luby := fnd_load_util.owner_id(p_owner);
4719
4720 -- Translate char last_update_date to date
4721 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
4722
4723 BEGIN
4724 SELECT ID , LAST_UPDATED_BY, LAST_UPDATE_DATE
4725 INTO id, db_luby, db_ludate
4726 FROM OKL_STRM_TYPE_B
4727 where ID = p_styv_rec.id;
4728
4729 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
4730 db_ludate, '')) then
4731 --Update _b
4732 UPDATE OKL_STRM_TYPE_B
4733 SET
4734 OBJECT_VERSION_NUMBER = p_styv_rec.object_version_number,
4735 START_DATE = p_styv_rec.start_date,
4736 END_DATE = p_styv_rec.end_date,
4737 CUSTOMIZATION_LEVEL = p_styv_rec.customization_level,
4738 STREAM_TYPE_SCOPE = p_styv_rec.stream_type_scope,
4739 ACCRUAL_YN = p_styv_rec.accrual_yn,
4740 TAXABLE_DEFAULT_YN = p_styv_rec.taxable_default_yn,
4741 STREAM_TYPE_CLASS = p_styv_rec.stream_type_class,
4742 STREAM_TYPE_SUBCLASS = p_styv_rec.stream_type_subclass,
4743 BILLABLE_YN = p_styv_rec.billable_yn,
4744 CAPITALIZE_YN = p_styv_rec.capitalize_yn,
4745 PERIODIC_YN = p_styv_rec.periodic_yn,
4746 FUNDABLE_YN = p_styv_rec.fundable_yn,
4747 ALLOCATION_FACTOR = p_styv_rec.allocation_factor,
4748 LAST_UPDATE_DATE = f_ludate,
4749 LAST_UPDATED_BY = f_luby,
4750 LAST_UPDATE_LOGIN = 0,
4751 VERSION = p_styv_rec.version,
4752 CODE = p_styv_rec.code,
4753 ATTRIBUTE_CATEGORY = p_styv_rec.attribute_category ,
4754 ATTRIBUTE1 = p_styv_rec.attribute1,
4755 ATTRIBUTE2 = p_styv_rec.attribute2,
4756 ATTRIBUTE3 = p_styv_rec.attribute3,
4757 ATTRIBUTE4 = p_styv_rec.attribute4,
4758 ATTRIBUTE5 = p_styv_rec.attribute5,
4759 ATTRIBUTE6 = p_styv_rec.attribute6,
4760 ATTRIBUTE7 = p_styv_rec.attribute7,
4761 ATTRIBUTE8 = p_styv_rec.attribute8,
4762 ATTRIBUTE9 = p_styv_rec.attribute9,
4763 ATTRIBUTE10 = p_styv_rec.attribute10,
4764 ATTRIBUTE11 = p_styv_rec.attribute11,
4765 ATTRIBUTE12 = p_styv_rec.attribute12,
4766 ATTRIBUTE13 = p_styv_rec.attribute13,
4767 ATTRIBUTE14 = p_styv_rec.attribute14,
4768 ATTRIBUTE15 = p_styv_rec.attribute15 ,
4769 STREAM_TYPE_PURPOSE = p_styv_rec.stream_type_purpose,
4770 CONTINGENCY = p_styv_rec.contingency,
4771 -- Added by SNANDIKO for Bug 6744584 Start
4772 CONTINGENCY_ID = p_styv_rec.contingency_id
4773 -- Added by SNANDIKO for Bug 6744584 End
4774 WHERE ID = to_number(p_styv_rec.ID);
4775 --Update TL
4776 UPDATE OKL_STRM_TYPE_TL
4777 SET
4778 ID = p_styv_rec.ID,
4779 NAME = p_styv_rec.NAME,
4780 DESCRIPTION = p_styv_rec.DESCRIPTION,
4781 LAST_UPDATE_DATE = f_ludate,
4782 LAST_UPDATED_BY = f_luby,
4783 LAST_UPDATE_LOGIN = 0,
4784 SOURCE_LANG = USERENV('LANG')
4785 WHERE ID = TO_NUMBER(p_styv_rec.ID)
4786 AND USERENV('LANG') IN (language,source_lang);
4787
4788 IF(sql%notfound) THEN
4789 INSERT INTO OKL_STRM_TYPE_TL
4790 ( ID,
4791 NAME,
4792 LANGUAGE,
4793 SOURCE_LANG,
4794 SFWT_FLAG,
4795 DESCRIPTION,
4796 CREATED_BY,
4797 CREATION_DATE,
4798 LAST_UPDATED_BY,
4799 LAST_UPDATE_DATE,
4800 LAST_UPDATE_LOGIN
4801 ) select
4802 p_styv_rec.ID,
4803 p_styv_rec.NAME,
4804 L.LANGUAGE_CODE,
4805 USERENV('LANG'),
4806 decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
4807 p_styv_rec.DESCRIPTION,
4808 f_luby,
4809 f_ludate,
4810 f_luby,
4811 f_ludate,
4812 0
4813 from FND_LANGUAGES L
4814 where L.INSTALLED_FLAG IN ('I','B')
4815 and not exists
4816 ( select NULL
4817 from okl_strm_type_tl TL
4818 where TL.ID = TO_NUMBER(p_styv_rec.ID)
4819 and TL.LANGUAGE = L.LANGUAGE_CODE );
4820
4821 end if;
4822
4823 END IF;
4824 END;
4825 EXCEPTION
4826 when no_data_found then
4827 INSERT INTO OKL_STRM_TYPE_B
4828 (ID,
4829 VERSION,
4830 CODE,
4831 CUSTOMIZATION_LEVEL,
4832 STREAM_TYPE_SCOPE,
4833 OBJECT_VERSION_NUMBER,
4834 ACCRUAL_YN,
4835 TAXABLE_DEFAULT_YN,
4836 STREAM_TYPE_CLASS,
4837 STREAM_TYPE_SUBCLASS,
4838 START_DATE,
4839 END_DATE,
4840 BILLABLE_YN,
4841 CAPITALIZE_YN,
4842 PERIODIC_YN,
4843 FUNDABLE_YN,
4844 ALLOCATION_FACTOR,
4845 ATTRIBUTE_CATEGORY,
4846 ATTRIBUTE1,
4847 ATTRIBUTE2,
4848 ATTRIBUTE3,
4849 ATTRIBUTE4,
4850 ATTRIBUTE5,
4851 ATTRIBUTE6,
4852 ATTRIBUTE7,
4853 ATTRIBUTE8,
4854 ATTRIBUTE9,
4855 ATTRIBUTE10,
4856 ATTRIBUTE11,
4857 ATTRIBUTE12,
4858 ATTRIBUTE13,
4859 ATTRIBUTE14,
4860 ATTRIBUTE15,
4861 CREATED_BY,
4862 CREATION_DATE,
4863 LAST_UPDATED_BY,
4864 LAST_UPDATE_DATE,
4865 LAST_UPDATE_LOGIN,
4866 STREAM_TYPE_PURPOSE,
4867 CONTINGENCY,
4868 -- Added by SNANDIKO for Bug 6744584 Start
4869 CONTINGENCY_ID )
4870 -- Added by SNANDIKO for Bug 6744584 End
4871 Select
4872 p_styv_rec.ID,
4873 p_styv_rec.VERSION,
4874 p_styv_rec.CODE,
4875 p_styv_rec.CUSTOMIZATION_LEVEL,
4876 p_styv_rec.STREAM_TYPE_SCOPE,
4877 p_styv_rec.OBJECT_VERSION_NUMBER,
4878 p_styv_rec.ACCRUAL_YN,
4879 p_styv_rec.TAXABLE_DEFAULT_YN,
4880 p_styv_rec.STREAM_TYPE_CLASS,
4881 p_styv_rec.STREAM_TYPE_SUBCLASS,
4882 p_styv_rec.START_DATE,
4883 p_styv_rec.END_DATE,
4884 p_styv_rec.BILLABLE_YN,
4885 p_styv_rec.CAPITALIZE_YN,
4886 p_styv_rec.PERIODIC_YN,
4887 p_styv_rec.FUNDABLE_YN,
4888 p_styv_rec.ALLOCATION_FACTOR,
4889 p_styv_rec.ATTRIBUTE_CATEGORY,
4890 p_styv_rec.ATTRIBUTE1,
4891 p_styv_rec.ATTRIBUTE2,
4892 p_styv_rec.ATTRIBUTE3,
4893 p_styv_rec.ATTRIBUTE4,
4894 p_styv_rec.ATTRIBUTE5,
4895 p_styv_rec.ATTRIBUTE6,
4896 p_styv_rec.ATTRIBUTE7,
4897 p_styv_rec.ATTRIBUTE8,
4898 p_styv_rec.ATTRIBUTE9,
4899 p_styv_rec.ATTRIBUTE10,
4900 p_styv_rec.ATTRIBUTE11,
4901 p_styv_rec.ATTRIBUTE12,
4902 p_styv_rec.ATTRIBUTE13,
4903 p_styv_rec.ATTRIBUTE14,
4904 p_styv_rec.ATTRIBUTE15,
4905 f_luby,
4906 f_ludate,
4907 f_luby,
4908 f_ludate,
4909 0,
4910 p_styv_rec.STREAM_TYPE_PURPOSE,
4911 p_styv_rec.CONTINGENCY,
4912 -- Added by SNANDIKO for Bug 6744584 Start
4913 p_styv_rec.CONTINGENCY_ID
4914 -- Added by SNANDIKO for Bug 6744584 End
4915 from dual
4916 where not exists (select 1
4917 from okl_strm_type_b
4918 where ID = TO_NUMBER(p_styv_rec.ID));
4919
4920 INSERT INTO OKL_STRM_TYPE_TL
4921 (
4922 ID,
4923 LANGUAGE,
4924 SOURCE_LANG,
4925 SFWT_FLAG,
4926 NAME,
4927 DESCRIPTION,
4928 CREATED_BY,
4929 CREATION_DATE,
4930 LAST_UPDATED_BY,
4931 LAST_UPDATE_DATE,
4932 LAST_UPDATE_LOGIN
4933 )
4934 SELECT
4935 p_styv_rec.id,
4936 L.LANGUAGE_CODE,
4937 userenv('LANG'),
4938 decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
4939 p_styv_rec.NAME,
4940 p_styv_rec.DESCRIPTION,
4941 f_luby,
4942 f_ludate,
4943 f_luby,
4944 f_ludate,
4945 0
4946 FROM FND_LANGUAGES L
4947 WHERE L.INSTALLED_FLAG IN ('I','B')
4948 AND NOT EXISTS
4949 (SELECT NULL
4950 FROM OKL_STRM_TYPE_TL TL
4951 WHERE TL.ID = TO_NUMBER(p_styv_rec.ID)
4952 AND TL.LANGUAGE = L.LANGUAGE_CODE);
4953 END LOAD_ROW;
4954
4955 -------------------------------------------------------------------------------
4956 -- Procedure LOAD_SEED_ROW
4957 -------------------------------------------------------------------------------
4958
4959 PROCEDURE LOAD_SEED_ROW(
4960 p_upload_mode IN VARCHAR2,
4961 p_id IN VARCHAR2,
4962 p_version IN VARCHAR2,
4963 p_code IN VARCHAR2,
4964 p_customization_level IN VARCHAR2,
4965 p_stream_type_scope IN VARCHAR2,
4966 p_object_version_number IN VARCHAR2,
4967 p_accrual_yn IN VARCHAR2,
4968 p_taxable_default_yn IN VARCHAR2,
4969 p_stream_type_class IN VARCHAR2,
4970 p_stream_type_subclass IN VARCHAR2,
4971 p_start_date IN VARCHAR2,
4972 p_end_date IN VARCHAR2,
4973 p_billable_yn IN VARCHAR2,
4974 p_capitalize_yn IN VARCHAR2,
4975 p_periodic_yn IN VARCHAR2,
4976 p_fundable_yn IN VARCHAR2,
4977 p_allocation_factor IN VARCHAR2,
4978 p_attribute_category IN VARCHAR2,
4979 p_attribute1 IN VARCHAR2,
4980 p_attribute2 IN VARCHAR2,
4981 p_attribute3 IN VARCHAR2,
4982 p_attribute4 IN VARCHAR2,
4983 p_attribute5 IN VARCHAR2,
4984 p_attribute6 IN VARCHAR2,
4985 p_attribute7 IN VARCHAR2,
4986 p_attribute8 IN VARCHAR2,
4987 p_attribute9 IN VARCHAR2,
4988 p_attribute10 IN VARCHAR2,
4989 p_attribute11 IN VARCHAR2,
4990 p_attribute12 IN VARCHAR2,
4991 p_attribute13 IN VARCHAR2,
4992 p_attribute14 IN VARCHAR2,
4993 p_attribute15 IN VARCHAR2,
4994 p_stream_type_purpose IN VARCHAR2,
4995 p_contingency IN VARCHAR2,
4996 p_name IN VARCHAR2,
4997 p_description IN VARCHAR2,
4998 p_owner IN VARCHAR2,
4999 p_last_update_date IN VARCHAR2,
5000 -- Added by SNANDIKO for Bug 6744584 Start
5001 p_contingency_id IN VARCHAR2) IS
5002 -- Added by SNANDIKO for Bug 6744584 End
5003
5004 l_api_version CONSTANT number := 1;
5005 l_api_name CONSTANT varchar2(30) := 'LOAD_SEED_ROW';
5006 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
5007 l_msg_count number;
5008 l_msg_data varchar2(4000);
5009 l_init_msg_list VARCHAR2(1):= 'T';
5010 l_styv_rec styv_rec_type;
5011 BEGIN
5012
5013 --Prepare Record Structure for Insert/Update
5014 l_styv_rec.id := TO_NUMBER(p_id);
5015 l_styv_rec.name := p_name;
5016 l_styv_rec.version := p_version;
5017 l_styv_rec.object_version_number := p_object_version_number;
5018 l_styv_rec.code := p_code;
5019 l_styv_rec.stream_type_scope := p_stream_type_scope;
5020 l_styv_rec.description := p_description;
5021 l_styv_rec.start_date := TO_DATE(p_start_date,'YYYY/MM/DD');
5022 l_styv_rec.end_date := TO_DATE(p_end_date,'YYYY/MM/DD');
5023 l_styv_rec.billable_yn := p_billable_yn;
5024 l_styv_rec.taxable_default_yn := p_taxable_default_yn;
5025 l_styv_rec.customization_level := p_customization_level;
5026 l_styv_rec.stream_type_class := p_stream_type_class;
5027 l_styv_rec.stream_type_subclass := p_stream_type_subclass;
5028 l_styv_rec.accrual_yn := p_accrual_yn;
5029 l_styv_rec.capitalize_yn := p_capitalize_yn;
5030 l_styv_rec.periodic_yn := p_periodic_yn;
5031 l_styv_rec.fundable_yn := p_fundable_yn;
5032 l_styv_rec.allocation_factor := p_allocation_factor;
5033 l_styv_rec.attribute_category := p_attribute_category;
5034 l_styv_rec.attribute1 := p_attribute1;
5035 l_styv_rec.attribute2 := p_attribute2;
5036 l_styv_rec.attribute3 := p_attribute3;
5037 l_styv_rec.attribute4 := p_attribute4;
5038 l_styv_rec.attribute5 := p_attribute5;
5039 l_styv_rec.attribute6 := p_attribute6;
5040 l_styv_rec.attribute7 := p_attribute7;
5041 l_styv_rec.attribute8 := p_attribute8;
5042 l_styv_rec.attribute9 := p_attribute9;
5043 l_styv_rec.attribute10 := p_attribute10;
5044 l_styv_rec.attribute11 := p_attribute11;
5045 l_styv_rec.attribute12 := p_attribute12;
5046 l_styv_rec.attribute13 := p_attribute13;
5047 l_styv_rec.attribute14 := p_attribute14;
5048 l_styv_rec.attribute15 := p_attribute15;
5049 l_styv_rec.contingency := p_contingency;
5050 l_styv_rec.stream_type_purpose := p_stream_type_purpose;
5051
5052 -- Added by SNANDIKO for Bug 6744584 Start
5053 l_styv_rec.contingency_id := p_contingency_id;
5054 -- Added by SNANDIKO for Bug 6744584 End
5055 IF(p_upload_mode = 'NLS') then
5056 OKL_STY_PVT.TRANSLATE_ROW(p_styv_rec => l_styv_rec,
5057 p_owner => p_owner,
5058 p_last_update_date => p_last_update_date,
5059 x_return_status => l_return_status);
5060 ELSE
5061 OKL_STY_PVT.LOAD_ROW(p_styv_rec => l_styv_rec,
5062 p_owner => p_owner,
5063 p_last_update_date => p_last_update_date,
5064 x_return_status => l_return_status);
5065 END IF;
5066 END LOAD_SEED_ROW;
5067
5068 END OKL_STY_PVT;