[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_STREAMTYPES_PVT
Source
1 PACKAGE BODY Okl_Setup_Streamtypes_Pvt AS
2 /* $Header: OKLRSMTB.pls 120.4 2011/01/30 19:04:16 gkadarka ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_STRM_TYPE_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (
8 p_styv_rec IN styv_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_no_data_found OUT NOCOPY BOOLEAN,
11 x_styv_rec OUT NOCOPY styv_rec_type
12 ) IS
13 CURSOR okl_styv_pk_csr (p_id IN NUMBER) IS
14 SELECT
15 ID,
16 NAME,
17 VERSION,
18 OBJECT_VERSION_NUMBER,
19 CODE,
20 SFWT_FLAG,
21 STREAM_TYPE_SCOPE,
22 DESCRIPTION,
23 START_DATE,
24 END_DATE,
25 BILLABLE_YN,
26 TAXABLE_DEFAULT_YN,
27 CUSTOMIZATION_LEVEL,
28 STREAM_TYPE_CLASS,
29 ACCRUAL_YN,
30 ALLOCATION_FACTOR,
31 ATTRIBUTE_CATEGORY,
32 ATTRIBUTE1,
33 ATTRIBUTE2,
34 ATTRIBUTE3,
35 ATTRIBUTE4,
36 ATTRIBUTE5,
37 ATTRIBUTE6,
38 ATTRIBUTE7,
39 ATTRIBUTE8,
40 ATTRIBUTE9,
41 ATTRIBUTE10,
42 ATTRIBUTE11,
43 ATTRIBUTE12,
44 ATTRIBUTE13,
45 ATTRIBUTE14,
46 ATTRIBUTE15,
47 CREATED_BY,
48 CREATION_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN,
52 -- Added by RGOOTY for ER 3935682: Start
53 STREAM_TYPE_PURPOSE,
54 CONTINGENCY,
55 SHORT_DESCRIPTION
56 -- Added by RGOOTY for ER 3935682: End
57 FROM OKL_STRM_TYPE_V
58 WHERE OKL_STRM_TYPE_V.id = p_id;
59
60 l_okl_styv_pk okl_styv_pk_csr%ROWTYPE;
61 l_styv_rec styv_rec_type;
62 BEGIN
63 x_return_status := G_RET_STS_SUCCESS;
64 x_no_data_found := TRUE;
65
66 -- Get current database values
67 OPEN okl_styv_pk_csr (p_styv_rec.id);
68 FETCH okl_styv_pk_csr INTO
69 l_styv_rec.ID,
70 l_styv_rec.NAME,
71 l_styv_rec.VERSION,
72 l_styv_rec.OBJECT_VERSION_NUMBER,
73 l_styv_rec.CODE,
74 l_styv_rec.SFWT_FLAG,
75 l_styv_rec.STREAM_TYPE_SCOPE,
76 l_styv_rec.DESCRIPTION,
77 l_styv_rec.START_DATE,
78 l_styv_rec.END_DATE,
79 l_styv_rec.BILLABLE_YN,
80 l_styv_rec.TAXABLE_DEFAULT_YN,
81 l_styv_rec.CUSTOMIZATION_LEVEL,
82 l_styv_rec.STREAM_TYPE_CLASS,
83 l_styv_rec.ACCRUAL_YN,
84 l_styv_rec.ALLOCATION_FACTOR,
85 l_styv_rec.ATTRIBUTE_CATEGORY,
86 l_styv_rec.ATTRIBUTE1,
87 l_styv_rec.ATTRIBUTE2,
88 l_styv_rec.ATTRIBUTE3,
89 l_styv_rec.ATTRIBUTE4,
90 l_styv_rec.ATTRIBUTE5,
91 l_styv_rec.ATTRIBUTE6,
92 l_styv_rec.ATTRIBUTE7,
93 l_styv_rec.ATTRIBUTE8,
94 l_styv_rec.ATTRIBUTE9,
95 l_styv_rec.ATTRIBUTE10,
96 l_styv_rec.ATTRIBUTE11,
97 l_styv_rec.ATTRIBUTE12,
98 l_styv_rec.ATTRIBUTE13,
99 l_styv_rec.ATTRIBUTE14,
100 l_styv_rec.ATTRIBUTE15,
101 l_styv_rec.CREATED_BY,
102 l_styv_rec.CREATION_DATE,
103 l_styv_rec.LAST_UPDATED_BY,
104 l_styv_rec.LAST_UPDATE_DATE,
105 l_styv_rec.LAST_UPDATE_LOGIN,
106 -- Added by RGOOTY for ER 3935682: Start
107 l_styv_rec.stream_type_purpose,
108 l_styv_rec.contingency,
109 l_styv_rec.short_description;
110 -- Added by RGOOTY for ER 3935682: End
111 x_no_data_found := okl_styv_pk_csr%NOTFOUND;
112 CLOSE okl_styv_pk_csr;
113
114 x_styv_rec := l_styv_rec;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 -- store SQL error message on message stack
119 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
120 p_msg_name => G_UNEXPECTED_ERROR,
121 p_token1 => G_SQLCODE_TOKEN,
122 p_token1_value => SQLCODE,
123 p_token2 => G_SQLERRM_TOKEN,
124 p_token2_value => SQLERRM);
125 -- notify UNEXPECTED error for calling API.
126 x_return_status := G_RET_STS_UNEXP_ERROR;
127
128 IF (okl_styv_pk_csr%ISOPEN) THEN
129 CLOSE okl_styv_pk_csr;
130 END IF;
131
132 END get_rec;
133
134 ---------------------------------------------------------------------------
135 -- PROCEDURE get_changes_only
136 -- To take care of the assumption that Everything
137 -- except the Changed Fields have G_MISS values in them
138 ---------------------------------------------------------------------------
139 PROCEDURE get_changes_only (
140 p_styv_rec IN styv_rec_type,
141 p_db_rec IN styv_rec_type,
142 x_styv_rec OUT NOCOPY styv_rec_type )
143 IS
144 l_styv_rec styv_rec_type;
145 BEGIN
146 l_styv_rec := p_styv_rec;
147
148 IF p_db_rec.NAME = p_styv_rec.NAME THEN
149 l_styv_rec.NAME := G_MISS_CHAR;
150 END IF;
151
152 IF p_db_rec.CODE = p_styv_rec.CODE THEN
153 l_styv_rec.CODE := G_MISS_CHAR;
154 END IF;
155
156 IF p_db_rec.VERSION = p_styv_rec.VERSION THEN
157 l_styv_rec.VERSION := G_MISS_CHAR;
158 END IF;
159
160 IF p_db_rec.STREAM_TYPE_SCOPE = p_styv_rec.STREAM_TYPE_SCOPE THEN
161 l_styv_rec.STREAM_TYPE_SCOPE := G_MISS_CHAR;
162 END IF;
163
164 IF p_db_rec.START_DATE = p_styv_rec.START_DATE THEN
165 l_styv_rec.START_DATE := G_MISS_DATE;
166 END IF;
167
168 IF p_db_rec.BILLABLE_YN = p_styv_rec.BILLABLE_YN THEN
169 l_styv_rec.BILLABLE_YN := G_MISS_CHAR;
170 END IF;
171 IF p_db_rec.TAXABLE_DEFAULT_YN = p_styv_rec.TAXABLE_DEFAULT_YN THEN
172 l_styv_rec.TAXABLE_DEFAULT_YN := G_MISS_CHAR;
173 END IF;
174 IF p_db_rec.CUSTOMIZATION_LEVEL = p_styv_rec.CUSTOMIZATION_LEVEL THEN
175 l_styv_rec.CUSTOMIZATION_LEVEL := G_MISS_CHAR;
176 END IF;
177 IF p_db_rec.STREAM_TYPE_CLASS = p_styv_rec.STREAM_TYPE_CLASS THEN
178 l_styv_rec.STREAM_TYPE_CLASS := G_MISS_CHAR;
179 END IF;
180 IF p_db_rec.ACCRUAL_YN = p_styv_rec.ACCRUAL_YN THEN
181 l_styv_rec.ACCRUAL_YN := G_MISS_CHAR;
182 END IF;
183
184 IF p_db_rec.END_DATE IS NULL
185 THEN
186 IF p_styv_rec.END_DATE IS NULL
187 THEN
188 l_styv_rec.END_DATE := G_MISS_DATE;
189 END IF;
190 ELSIF p_db_rec.END_DATE = p_styv_rec.END_DATE
191 THEN
192 l_styv_rec.END_DATE := G_MISS_DATE;
193 END IF;
194
195 IF p_db_rec.DESCRIPTION IS NULL
196 THEN
197 IF p_styv_rec.DESCRIPTION IS NULL
198 THEN
199 l_styv_rec.DESCRIPTION := G_MISS_CHAR;
200 END IF;
201 ELSIF p_db_rec.DESCRIPTION = p_styv_rec.DESCRIPTION
202 THEN
203 l_styv_rec.DESCRIPTION := G_MISS_CHAR;
204 END IF;
205
206 IF p_db_rec.FUNDABLE_YN IS NULL
207 THEN
208 IF p_styv_rec.FUNDABLE_YN IS NULL
209 THEN
210 l_styv_rec.FUNDABLE_YN := G_MISS_CHAR;
211 END IF;
212 ELSIF p_db_rec.FUNDABLE_YN = p_styv_rec.FUNDABLE_YN
213 THEN
214 l_styv_rec.FUNDABLE_YN := G_MISS_CHAR;
215 END IF;
216
217 IF p_db_rec.PERIODIC_YN IS NULL
218 THEN
219 IF p_styv_rec.PERIODIC_YN IS NULL
220 THEN
221 l_styv_rec.PERIODIC_YN := G_MISS_CHAR;
222 END IF;
223 ELSIF p_db_rec.PERIODIC_YN = p_styv_rec.PERIODIC_YN
224 THEN
225 l_styv_rec.PERIODIC_YN := G_MISS_CHAR;
226 END IF;
227
228 IF p_db_rec.CAPITALIZE_YN IS NULL
229 THEN
230 IF p_styv_rec.CAPITALIZE_YN IS NULL
231 THEN
232 l_styv_rec.CAPITALIZE_YN := G_MISS_CHAR;
233 END IF;
234 ELSIF p_db_rec.CAPITALIZE_YN = p_styv_rec.CAPITALIZE_YN
235 THEN
236 l_styv_rec.CAPITALIZE_YN := G_MISS_CHAR;
237 END IF;
238
239 IF p_db_rec.ALLOCATION_FACTOR IS NULL
240 THEN
241 IF p_styv_rec.ALLOCATION_FACTOR IS NULL
242 THEN
243 l_styv_rec.ALLOCATION_FACTOR := G_MISS_CHAR;
244 END IF;
245 ELSIF p_db_rec.ALLOCATION_FACTOR = p_styv_rec.ALLOCATION_FACTOR
246 THEN
247 l_styv_rec.ALLOCATION_FACTOR := G_MISS_CHAR;
248 END IF;
249
250 IF p_db_rec.ATTRIBUTE_CATEGORY IS NULL
251 THEN
252 IF p_styv_rec.ATTRIBUTE_CATEGORY IS NULL
253 THEN
254 l_styv_rec.ATTRIBUTE_CATEGORY := G_MISS_CHAR;
255 END IF;
256 ELSIF p_db_rec.ATTRIBUTE_CATEGORY = p_styv_rec.ATTRIBUTE_CATEGORY
257 THEN
258 l_styv_rec.ATTRIBUTE_CATEGORY := G_MISS_CHAR;
259 END IF;
260
261 IF p_db_rec.ATTRIBUTE1 IS NULL
262 THEN
263 IF p_styv_rec.ATTRIBUTE1 IS NULL
264 THEN
265 l_styv_rec.ATTRIBUTE1 := G_MISS_CHAR;
266 END IF;
267 ELSIF p_db_rec.ATTRIBUTE1 = p_styv_rec.ATTRIBUTE1
268 THEN
269 l_styv_rec.ATTRIBUTE1 := G_MISS_CHAR;
270 END IF;
271
272
273 IF p_db_rec.ATTRIBUTE2 IS NULL
274 THEN
275 IF p_styv_rec.ATTRIBUTE2 IS NULL
276 THEN
277 l_styv_rec.ATTRIBUTE2 := G_MISS_CHAR;
278 END IF;
279 ELSIF p_db_rec.ATTRIBUTE2 = p_styv_rec.ATTRIBUTE2
280 THEN
281 l_styv_rec.ATTRIBUTE2 := G_MISS_CHAR;
282 END IF;
283
284 IF p_db_rec.ATTRIBUTE3 IS NULL
285 THEN
286 IF p_styv_rec.ATTRIBUTE3 IS NULL
287 THEN
288 l_styv_rec.ATTRIBUTE3 := G_MISS_CHAR;
289 END IF;
290 ELSIF p_db_rec.ATTRIBUTE3 = p_styv_rec.ATTRIBUTE3
291 THEN
292 l_styv_rec.ATTRIBUTE3 := G_MISS_CHAR;
293 END IF;
294
295 IF p_db_rec.ATTRIBUTE4 IS NULL
296 THEN
297 IF p_styv_rec.ATTRIBUTE4 IS NULL
298 THEN
299 l_styv_rec.ATTRIBUTE4 := G_MISS_CHAR;
300 END IF;
301 ELSIF p_db_rec.ATTRIBUTE4 = p_styv_rec.ATTRIBUTE4
302 THEN
303 l_styv_rec.ATTRIBUTE4 := G_MISS_CHAR;
304 END IF;
305
306 IF p_db_rec.ATTRIBUTE5 IS NULL
307 THEN
308 IF p_styv_rec.ATTRIBUTE5 IS NULL
309 THEN
310 l_styv_rec.ATTRIBUTE5 := G_MISS_CHAR;
311 END IF;
312 ELSIF p_db_rec.ATTRIBUTE5 = p_styv_rec.ATTRIBUTE5
313 THEN
314 l_styv_rec.ATTRIBUTE5 := G_MISS_CHAR;
315 END IF;
316
317 IF p_db_rec.ATTRIBUTE6 IS NULL
318 THEN
319 IF p_styv_rec.ATTRIBUTE6 IS NULL
320 THEN
321 l_styv_rec.ATTRIBUTE6 := G_MISS_CHAR;
322 END IF;
323 ELSIF p_db_rec.ATTRIBUTE6 = p_styv_rec.ATTRIBUTE6
324 THEN
325 l_styv_rec.ATTRIBUTE6 := G_MISS_CHAR;
326 END IF;
327
328 IF p_db_rec.ATTRIBUTE7 IS NULL
329 THEN
330 IF p_styv_rec.ATTRIBUTE7 IS NULL
331 THEN
332 l_styv_rec.ATTRIBUTE7 := G_MISS_CHAR;
333 END IF;
334 ELSIF p_db_rec.ATTRIBUTE7 = p_styv_rec.ATTRIBUTE7
335 THEN
336 l_styv_rec.ATTRIBUTE7 := G_MISS_CHAR;
337 END IF;
338
339 IF p_db_rec.ATTRIBUTE8 IS NULL
340 THEN
341 IF p_styv_rec.ATTRIBUTE8 IS NULL
342 THEN
343 l_styv_rec.ATTRIBUTE8 := G_MISS_CHAR;
344 END IF;
345 ELSIF p_db_rec.ATTRIBUTE8 = p_styv_rec.ATTRIBUTE8
346 THEN
347 l_styv_rec.ATTRIBUTE8 := G_MISS_CHAR;
348 END IF;
349
350 IF p_db_rec.ATTRIBUTE9 IS NULL
351 THEN
352 IF p_styv_rec.ATTRIBUTE9 IS NULL
353 THEN
354 l_styv_rec.ATTRIBUTE9 := G_MISS_CHAR;
355 END IF;
356 ELSIF p_db_rec.ATTRIBUTE9 = p_styv_rec.ATTRIBUTE9
357 THEN
358 l_styv_rec.ATTRIBUTE9 := G_MISS_CHAR;
359 END IF;
360
361 IF p_db_rec.ATTRIBUTE10 IS NULL
362 THEN
363 IF p_styv_rec.ATTRIBUTE10 IS NULL
364 THEN
365 l_styv_rec.ATTRIBUTE10 := G_MISS_CHAR;
366 END IF;
367 ELSIF p_db_rec.ATTRIBUTE10 = p_styv_rec.ATTRIBUTE10
368 THEN
369 l_styv_rec.ATTRIBUTE10 := G_MISS_CHAR;
370 END IF;
371
372 IF p_db_rec.ATTRIBUTE11 IS NULL
373 THEN
374 IF p_styv_rec.ATTRIBUTE11 IS NULL
375 THEN
376 l_styv_rec.ATTRIBUTE11 := G_MISS_CHAR;
377 END IF;
378 ELSIF p_db_rec.ATTRIBUTE11 = p_styv_rec.ATTRIBUTE11
379 THEN
380 l_styv_rec.ATTRIBUTE11 := G_MISS_CHAR;
381 END IF;
382
383 IF p_db_rec.ATTRIBUTE12 IS NULL
384 THEN
385 IF p_styv_rec.ATTRIBUTE12 IS NULL
386 THEN
387 l_styv_rec.ATTRIBUTE12 := G_MISS_CHAR;
388 END IF;
389 ELSIF p_db_rec.ATTRIBUTE12 = p_styv_rec.ATTRIBUTE12
390 THEN
391 l_styv_rec.ATTRIBUTE12 := G_MISS_CHAR;
392 END IF;
393
394 IF p_db_rec.ATTRIBUTE13 IS NULL
395 THEN
396 IF p_styv_rec.ATTRIBUTE13 IS NULL
397 THEN
398 l_styv_rec.ATTRIBUTE13 := G_MISS_CHAR;
399 END IF;
400 ELSIF p_db_rec.ATTRIBUTE13 = p_styv_rec.ATTRIBUTE13
401 THEN
402 l_styv_rec.ATTRIBUTE13 := G_MISS_CHAR;
403 END IF;
404
405 IF p_db_rec.ATTRIBUTE14 IS NULL
406 THEN
407 IF p_styv_rec.ATTRIBUTE14 IS NULL
408 THEN
409 l_styv_rec.ATTRIBUTE14 := G_MISS_CHAR;
410 END IF;
411 ELSIF p_db_rec.ATTRIBUTE14 = p_styv_rec.ATTRIBUTE14
412 THEN
413 l_styv_rec.ATTRIBUTE14 := G_MISS_CHAR;
414 END IF;
415
416 IF p_db_rec.ATTRIBUTE15 IS NULL
417 THEN
418 IF p_styv_rec.ATTRIBUTE15 IS NULL
419 THEN
420 l_styv_rec.ATTRIBUTE15 := G_MISS_CHAR;
421 END IF;
422 ELSIF p_db_rec.ATTRIBUTE15 = p_styv_rec.ATTRIBUTE15
423 THEN
424 l_styv_rec.ATTRIBUTE15 := G_MISS_CHAR;
425 END IF;
426 -- Added by RGOOTY for ER 3935682: Start
427 IF p_db_rec.stream_type_purpose IS NULL
428 THEN
429 IF p_styv_rec.stream_type_purpose IS NULL
430 THEN
431 l_styv_rec.stream_type_purpose := G_MISS_CHAR;
432 END IF;
433 ELSIF p_db_rec.stream_type_purpose = p_styv_rec.stream_type_purpose
434 THEN
435 l_styv_rec.stream_type_purpose := G_MISS_CHAR;
436 END IF;
437
438 IF p_db_rec.contingency IS NULL
439 THEN
440 IF p_styv_rec.contingency IS NULL
441 THEN
442 l_styv_rec.contingency := G_MISS_CHAR;
443 END IF;
444 ELSIF p_db_rec.contingency = p_styv_rec.contingency
445 THEN
446 l_styv_rec.contingency := G_MISS_CHAR;
447 END IF;
448
449 IF p_db_rec.short_description IS NULL
450 THEN
451 IF p_styv_rec.short_description IS NULL
452 THEN
453 l_styv_rec.short_description := G_MISS_CHAR;
454 END IF;
455 ELSIF p_db_rec.short_description = p_styv_rec.short_description
456 THEN
457 l_styv_rec.contingency := G_MISS_CHAR;
458 END IF;
459 -- Added by RGOOTY for ER 3935682: End
460 x_styv_rec := l_styv_rec;
461
462 END get_changes_only;
463
464 ---------------------------------------------------------------------------
465 -- PROCEDURE determine_action for: OKL_STRM_TYPE_V
466 -- This function helps in determining the various checks to be performed
467 -- for the new/updated record and also helps in determining whether a new
468 -- version is required or not
469 ---------------------------------------------------------------------------
470 FUNCTION determine_action (
471 p_upd_styv_rec IN styv_rec_type,
472 p_db_styv_rec IN styv_rec_type,
473 p_date IN DATE
474 ) RETURN VARCHAR2 IS
475 l_action VARCHAR2(1);
476 l_sysdate DATE := TRUNC(SYSDATE);
477 BEGIN
478
479 -- Scenario 1: The Changed Field-Values can by-pass Validation
480 IF p_upd_styv_rec.start_date = G_MISS_DATE AND
481 p_upd_styv_rec.end_date = G_MISS_DATE AND
482 p_upd_styv_rec.stream_type_scope = G_MISS_CHAR AND
483 p_upd_styv_rec.taxable_default_yn = G_MISS_CHAR AND
484 p_upd_styv_rec.stream_type_class = G_MISS_CHAR AND
485 p_upd_styv_rec.accrual_yn = G_MISS_CHAR AND
486 p_upd_styv_rec.capitalize_yn = G_MISS_CHAR AND
487 p_upd_styv_rec.periodic_yn = G_MISS_CHAR AND
488 p_upd_styv_rec.fundable_yn = G_MISS_CHAR AND
489 p_upd_styv_rec.allocation_factor = G_MISS_CHAR THEN
490 l_action := '1';
491 -- Scenario 2: The Changed Field-Values include that needs Validation and Update
492
493 -- 1) End_Date is Changed
494 ELSIF (p_upd_styv_rec.start_date = G_MISS_DATE AND
495 (p_upd_styv_rec.end_date <> G_MISS_DATE OR
496 -- IS NULL Condition has been added in case end_date was updated to NULL
497 p_upd_styv_rec.end_date IS NULL ) AND
498 p_upd_styv_rec.stream_type_scope = G_MISS_CHAR AND
499 p_upd_styv_rec.taxable_default_yn = G_MISS_CHAR AND
500 p_upd_styv_rec.stream_type_class = G_MISS_CHAR AND
501 p_upd_styv_rec.accrual_yn = G_MISS_CHAR AND
502 p_upd_styv_rec.capitalize_yn = G_MISS_CHAR AND
503 p_upd_styv_rec.periodic_yn = G_MISS_CHAR AND
504 p_upd_styv_rec.fundable_yn = G_MISS_CHAR AND
505 p_upd_styv_rec.allocation_factor = G_MISS_CHAR) OR
506 -- 2) Critical Attributes are Changed but does not mandate new version
507 -- as Start_Date is in Future and Not Changied
508 (p_upd_styv_rec.start_date = G_MISS_DATE AND
509 p_db_styv_rec.start_date >= p_date AND
510 (p_upd_styv_rec.stream_type_scope <> G_MISS_CHAR OR
511 p_upd_styv_rec.taxable_default_yn <> G_MISS_CHAR OR
512 p_upd_styv_rec.stream_type_class <> G_MISS_CHAR OR
513 -- mvasudev, 02/25/2002
514 -- IS NULL Condition has been added in case these attributes were updated to NULL
515 (p_upd_styv_rec.capitalize_yn <> G_MISS_CHAR OR p_upd_styv_rec.capitalize_yn IS NULL ) OR
516 (p_upd_styv_rec.periodic_yn <> G_MISS_CHAR OR p_upd_styv_rec.periodic_yn IS NULL ) OR
517 (p_upd_styv_rec.fundable_yn <> G_MISS_CHAR OR p_upd_styv_rec.fundable_yn IS NULL ) OR
518 (p_upd_styv_rec.allocation_factor <> G_MISS_CHAR OR p_upd_styv_rec.allocation_factor IS NULL ) OR
519 -- end,mvasudev, 02/25/2002
520 p_upd_styv_rec.accrual_yn <> G_MISS_CHAR)) OR
521 -- 3) Start_Date is Shifted , but in Future
522 (p_upd_styv_rec.start_date <> G_MISS_DATE AND
523 p_db_styv_rec.start_date > p_date)
524 -- Commented out to disregard multiple versions in Future , 04/11/2002
525 --AND p_upd_styv_rec.start_date < p_db_styv_rec.start_date)
526 THEN
527 l_action := '2';
528
529 ELSE
530 -- Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record
531 l_action := '3';
532 END IF;
533
534
535 RETURN(l_action);
536 END determine_action;
537
538 ---------------------------------------------------------------------------
539 -- PROCEDURE check_updates
540 -- To verify whether the requested changes from the screen are valid or not
541 ---------------------------------------------------------------------------
542 PROCEDURE check_updates (
543 p_styv_rec IN styv_rec_type,
544 x_return_status OUT NOCOPY VARCHAR2,
545 x_msg_data OUT NOCOPY VARCHAR2
546 ) IS
547
548 /* Commented till final decision made regarding Versioning
549 -- 04/11/2002
550 -- Cursor to fetch streams that would be impacted by stream-type update
551 CURSOR l_okl_stm_csr(p_sty_id NUMBER,p_sysdate DATE)
552 IS
553 SELECT '1' FROM dual
554 WHERE EXISTS
555 (SELECT '1'
556 FROM OKL_STRM_TYPE_TL STYL,
557 OKL_STREAMS STMB,
558 OKL_STRM_ELEMENTS SELB
559 WHERE STMB.STY_ID = STYL.ID
560 AND STMB.SAY_CODE = 'CURR'
561 AND SELB.STM_ID = STMB.ID
562 AND SELB.STREAM_ELEMENT_DATE > p_sysdate
563 );
564
565 -- Cursor to fetch accounting_templates that would be impacted by stream-type update
566 CURSOR l_okl_avl_csr(p_sty_id NUMBER,p_sysdate DATE)
567 IS
568 SELECT '1' FROM dual
569 WHERE EXISTS
570 (SELECT '1'
571 FROM OKL_AE_TEMPLATES_V
572 WHERE sty_id = p_sty_id
573 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE, p_sysdate)
574 );
575 -- 04/11/2002
576 */
577
578 l_styv_rec styv_rec_type;
579 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
580 l_valid BOOLEAN;
581 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
582 l_sysdate DATE := TRUNC(SYSDATE);
583
584 BEGIN
585 x_return_status := G_RET_STS_SUCCESS;
586 l_styv_rec := p_styv_rec;
587
588 /* call check_overlaps */
589 l_attrib_tbl(1).attribute := 'CODE';
590 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
591 l_attrib_tbl(1).value := l_styv_rec.code;
592
593 okl_accounting_util.check_overlaps( p_id => l_styv_rec.id,
594 p_attrib_tbl => l_attrib_tbl,
595 p_start_date_attribute_name => 'START_DATE',
596 p_start_date => l_styv_rec.start_date,
597 p_end_date_attribute_name => 'END_DATE',
598 p_end_date => l_styv_rec.end_date,
599 p_view => 'Okl_Strm_Type_V',
600 x_return_status => l_return_status,
601 x_valid => l_valid);
602
603 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
604 x_return_status := G_RET_STS_UNEXP_ERROR;
605 RAISE G_EXCEPTION_HALT_PROCESSING;
606 ELSIF (l_return_status = G_RET_STS_ERROR) OR
607 (l_return_status = G_RET_STS_SUCCESS AND
608 l_valid <> TRUE) THEN
609
610 x_return_status := G_RET_STS_ERROR;
611 RAISE G_EXCEPTION_HALT_PROCESSING;
612 END IF;
613
614 /* Check dependencies
615 -- 04/11/2002
616 -- Streams
617 FOR l_okl_stm_rec IN l_okl_stm_csr(l_styv_rec.id, l_sysdate)
618 LOOP
619 x_return_status := G_RET_STS_ERROR;
620 RAISE G_EXCEPTION_HALT_PROCESSING;
621 END LOOP;
622
623 -- Accounting Templates
624 FOR l_okl_avl_rec IN l_okl_avl_csr(l_styv_rec.id, l_sysdate)
625 LOOP
626 x_return_status := G_RET_STS_ERROR;
627 RAISE G_EXCEPTION_HALT_PROCESSING;
628 END LOOP;
629 -- end, 04/11/2002
630 */
631
632 EXCEPTION
633 WHEN G_EXCEPTION_HALT_PROCESSING THEN
634 -- no processing necessary; validation can continue
635 -- with the next column
636 NULL;
637 /* Commented till final decision made regarding Versioning
638 -- 04/11/2002
639 IF (l_okl_stm_csr%ISOPEN) THEN
640 CLOSE l_okl_stm_csr;
641 END IF;
642
643 IF (l_okl_avl_csr%ISOPEN) THEN
644 CLOSE l_okl_avl_csr;
645 END IF;
646 */
647
648 WHEN OTHERS THEN
649 -- store SQL error message on message stack for caller
650 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
651 p_msg_name => G_UNEXPECTED_ERROR,
652 p_token1 => G_SQLCODE_TOKEN,
653 p_token1_value => SQLCODE,
654 p_token2 => G_SQLERRM_TOKEN,
655 p_token2_value => SQLERRM );
656 -- notify caller of an UNEXPECTED error
657 x_return_status := G_RET_STS_UNEXP_ERROR;
658
659 END check_updates;
660
661 ---------------------------------------------------------------------------
662 -- PROCEDURE create_stream_type for: OKL_STRM_TYPE_V
663 ---------------------------------------------------------------------------
664 PROCEDURE create_stream_type( p_api_version IN NUMBER,
665 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 p_styv_rec IN styv_rec_type,
670 x_styv_rec OUT NOCOPY styv_rec_type
671 ) IS
672 l_api_version CONSTANT NUMBER := 1;
673 l_api_name CONSTANT VARCHAR2(30) := 'create_stream_type';
674 l_valid BOOLEAN := TRUE;
675 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
676 l_styv_rec styv_rec_type;
677 -- 25-Oct-2004 vthiruva -- Fix for Bug#3731453
678 -- Changed to_date(to_char()) to trunc() for date comparisions.
679 l_sysdate DATE := TRUNC(SYSDATE);
680
681 BEGIN
682 x_return_status := G_RET_STS_SUCCESS;
683 l_styv_rec := p_styv_rec;
684
685 -- mvasudev -- 02/17/2002
686 -- Store NAME in UPPER CASE always
687 l_styv_rec.NAME := UPPER(l_styv_rec.NAME);
688 -- end, mvasudev -- 02/17/2002
689
690 -- auto_update code with name
691 l_styv_rec.CODE := l_styv_rec.NAME;
692
693
694 /*
695 -- mvasudev COMMENTED , 06/13/2002
696 -- check for the records with start and end dates less than sysdate *
697 IF TO_DATE(to_char(l_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
698 TO_DATE(to_char(l_styv_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
699 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
700 p_msg_name => G_PAST_RECORDS);
701 RAISE G_EXCEPTION_ERROR;
702 END IF;
703 */
704
705
706
707 /* public api to insert streamtype */
708 okl_strm_type_pub.insert_strm_type(p_api_version => p_api_version,
709 p_init_msg_list => p_init_msg_list,
710 x_return_status => l_return_status,
711 x_msg_count => x_msg_count,
712 x_msg_data => x_msg_data,
713 p_styv_rec => l_styv_rec,
714 x_styv_rec => x_styv_rec);
715
716 IF l_return_status = G_RET_STS_ERROR THEN
717 RAISE G_EXCEPTION_ERROR;
718 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
719 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
720 END IF;
721
722 x_return_status := l_return_status;
723
724 EXCEPTION
725 WHEN G_EXCEPTION_ERROR THEN
726 x_return_status := G_RET_STS_ERROR;
727 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
728 x_return_status := G_RET_STS_UNEXP_ERROR;
729 WHEN OTHERS THEN
730 -- store SQL error message on message stack for caller
731 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
732 p_msg_name => G_UNEXPECTED_ERROR,
733 p_token1 => G_SQLCODE_TOKEN,
734 p_token1_value => SQLCODE,
735 p_token2 => G_SQLERRM_TOKEN,
736 p_token2_value => SQLERRM );
737 -- notify caller of an UNEXPECTED error
738 x_return_status := G_RET_STS_UNEXP_ERROR;
739 END create_stream_type;
740
741 ---------------------------------------------------------------------------
742 -- PROCEDURE update_stream_type for: OKL_STRM_TYPE_V
743 ---------------------------------------------------------------------------
744 PROCEDURE update_stream_type(p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
746 x_return_status OUT NOCOPY VARCHAR2,
747 x_msg_count OUT NOCOPY NUMBER,
748 x_msg_data OUT NOCOPY VARCHAR2,
749 p_styv_rec IN styv_rec_type,
750 x_styv_rec OUT NOCOPY styv_rec_type
751 ) IS
752
753 CURSOR l_okl_styv_pk_csr (p_id IN NUMBER) IS
754 SELECT
755 START_DATE,
756 END_DATE
757 FROM OKL_STRM_TYPE_B
758 WHERE OKL_STRM_TYPE_B.id = p_id;
759
760 l_api_version CONSTANT NUMBER := 1;
761 l_api_name CONSTANT VARCHAR2(30) := 'update_stream_type';
762 l_no_data_found BOOLEAN := TRUE;
763 l_valid BOOLEAN := TRUE;
764 -- 25-Oct-2004 vthiruva. Fix for Bug#3731453
765 -- Changed to_date(to_char()) to trunc() for date comparisions.
766 l_oldversion_enddate DATE := TRUNC(SYSDATE);
767 l_sysdate DATE := TRUNC(SYSDATE);
768 l_db_styv_rec styv_rec_type; /* database copy */
769 l_upd_styv_rec styv_rec_type; /* input copy */
770 l_styv_rec styv_rec_type; /* latest with the retained changes */
771 l_tmp_styv_rec styv_rec_type; /* for any other purposes */
772 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
773 l_action VARCHAR2(1);
774 l_new_version VARCHAR2(100);
775 l_attrib_tbl okl_accounting_util.overlap_attrib_tbl_type;
776 BEGIN
777 l_return_status := G_RET_STS_SUCCESS;
778 l_styv_rec := p_styv_rec;
779 -- auto_update code with name
780 -- sosharma changes for bug 11694679
781
782 If l_styv_rec.CODE is null then
783 l_styv_rec.CODE := l_styv_rec.NAME;
784 end if;
785 -- sosharma end changes for bug 11694679
786 -- mvasudev, 04/20/2002
787
788 -- END_DATE needs to be after START_DATE (sanity check)
789 -- and Cannot be less than SysDate
790 /*
791 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 start
792 ** Changed to_date(to_char()) to trunc() for date comparisions.
793 */
794 IF l_styv_rec.end_date IS NOT NULL
795 AND l_styv_rec.end_date <> G_MISS_DATE
796 AND
797 (TRUNC(l_styv_rec.end_date) < TRUNC(l_styv_rec.start_date)
798 OR TRUNC(l_styv_rec.end_date) < l_sysdate
799 )
800 THEN
801 /*
802 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 end
803 */
804 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
805 p_msg_name => G_INVALID_VALUE,
806 p_token1 => G_COL_NAME_TOKEN,
807 p_token1_value => 'END_DATE' );
808 RAISE G_EXCEPTION_ERROR;
809 END IF;
810
811 -- Get current database values
812 OPEN l_okl_styv_pk_csr (p_styv_rec.id);
813 FETCH l_okl_styv_pk_csr INTO
814 l_db_styv_rec.START_DATE,
815 l_db_styv_rec.END_DATE;
816 l_no_data_found := l_okl_styv_pk_csr%NOTFOUND;
817 CLOSE l_okl_styv_pk_csr;
818
819 IF l_no_data_found THEN
820 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
821 END IF;
822
823 /*
824 -- mvasudev COMMENTED , 06/13/2002
825 -- Start-Date cannot be CHANGED for records that have already started being effective
826 -- Neither Can the new Start_Date be in the Past
827 IF to_date(to_char(l_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(l_db_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY')
828 AND
829 ( to_date(to_char(l_db_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate
830 OR to_date(to_char(l_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate
831 )
832 THEN
833 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
834 p_msg_name => G_INVALID_VALUE,
835 p_token1 => G_COL_NAME_TOKEN,
836 p_token1_value => 'START_DATE' );
837 RAISE G_EXCEPTION_ERROR;
838 END IF;
839 */
840
841 -- start date can not be greater than old start date if the record is active
842 /*
843 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 start
844 ** Changed to_date(to_char()) to trunc() for date comparisions.
845 */
846 IF TRUNC(l_db_styv_rec.start_date) < l_sysdate
847 AND TRUNC(l_styv_rec.start_date) > TRUNC(l_db_styv_rec.start_date)
848 THEN
849 /*
850 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 end
851 */
852 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
853 p_msg_name => G_INVALID_VALUE,
854 p_token1 => G_COL_NAME_TOKEN,
855 p_token1_value => 'START_DATE' );
856 RAISE G_EXCEPTION_ERROR;
857 END IF;
858
859
860 -- public api to update_stream_type
861 OKL_STRM_TYPE_PUB.update_strm_type(p_api_version => p_api_version,
862 p_init_msg_list => p_init_msg_list,
863 x_return_status => l_return_status,
864 x_msg_count => x_msg_count,
865 x_msg_data => x_msg_data,
866 p_styv_rec => l_styv_rec,
867 x_styv_rec => x_styv_rec);
868 IF l_return_status = G_RET_STS_ERROR THEN
869 RAISE G_EXCEPTION_ERROR;
870 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
871 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
872 END IF;
873
874 /*******************************************************************
875 * FOLLOWING CODE COMMENTED TO DISABLE MULTIPLE VERSIONING
876 * Apr-20-2002, mvasudev
877 *
878 -- mvasudev -- 02/17/2002
879 -- END_DATE needs to be after START_DATE (sanity check)
880 IF l_styv_rec.end_date IS NOT NULL
881 AND to_date(to_char(l_styv_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY')
882 AND to_date(to_char(l_styv_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < to_date(to_char(l_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY')
883 THEN
884 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
885 p_msg_name => G_INVALID_VALUE,
886 p_token1 => G_COL_NAME_TOKEN,
887 p_token1_value => 'END_DATE' );
888 END IF;
889 -- end, mvasudev -- 02/17/2002
890
891 -- fetch old details from the database *
892 get_rec(p_styv_rec => l_styv_rec,
893 x_return_status => l_return_status,
894 x_no_data_found => l_no_data_found,
895 x_styv_rec => l_db_styv_rec);
896 IF l_return_status <> G_RET_STS_SUCCESS OR
897 l_no_data_found = TRUE THEN
898 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
899 END IF;
900
901 /* check for the records if start and end dates are in the past *
902 IF to_date(to_char(l_db_styv_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate AND
903 to_date(to_char(l_db_styv_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
904 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
905 p_msg_name => G_PAST_RECORDS);
906 RAISE G_EXCEPTION_ERROR;
907 END IF;
908
909
910 /* retain the details that has been changed only *
911 get_changes_only(p_styv_rec => p_styv_rec,
912 p_db_rec => l_db_styv_rec,
913 x_styv_rec => l_upd_styv_rec);
914
915 /* mvasudev, 02/17/2002
916
917 -- check for start date greater than sysdate
918 IF to_date(to_char(l_upd_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
919 to_date(to_char(l_upd_styv_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
920 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
921 p_msg_name => G_START_DATE);
922 RAISE G_EXCEPTION_ERROR;
923 END IF;
924
925 -- check for end date greater than sysdate
926 IF to_date(to_char(l_upd_styv_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
927 to_date(to_char(l_upd_styv_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
928 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
929 p_msg_name => G_END_DATE);
930 RAISE G_EXCEPTION_ERROR;
931 END IF;
932
933 *
934
935 -- START_DATE , if changed, can only be later than TODAY
936 IF to_date(to_char(l_upd_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
937 to_date(to_char(l_upd_styv_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= l_sysdate THEN
938 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
939 p_msg_name => G_START_DATE);
940 RAISE G_EXCEPTION_ERROR;
941 END IF;
942
943 -- END_DATE, if changed, cannot be earlier than TODAY
944 IF to_date(to_char(l_upd_styv_rec.end_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(G_MISS_DATE,'DD/MM/YYYY'), 'DD/MM/YYYY') AND
945 to_date(to_char(l_upd_styv_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
946 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
947 p_msg_name => G_END_DATE);
948 RAISE G_EXCEPTION_ERROR;
949 END IF;
950
951 -- end, mvasudev -- 02/17/2002
952
953 /* determine how the processing to be done *
954 l_action := determine_action(p_upd_styv_rec => l_upd_styv_rec,
955 p_db_styv_rec => l_db_styv_rec,
956 p_date => l_sysdate);
957
958 /* Scenario 1: The Changed Field-Values can by-pass Validation *
959 IF l_action = '1' THEN
960 /* public api to update_stream_type *
961 okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
962 p_init_msg_list => p_init_msg_list,
963 x_return_status => l_return_status,
964 x_msg_count => x_msg_count,
965 x_msg_data => x_msg_data,
966 p_styv_rec => l_upd_styv_rec,
967 x_styv_rec => x_styv_rec);
968 IF l_return_status = G_RET_STS_ERROR THEN
969 RAISE G_EXCEPTION_ERROR;
970 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
971 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
972 END IF;
973
974 /* Scenario 2: The Changed Field-Values include that needs Validation and Update *
975 ELSIF l_action = '2' THEN
976 check_updates( p_styv_rec => l_styv_rec,
977 x_return_status => l_return_status,
978 x_msg_data => x_msg_data);
979
980 IF l_return_status = G_RET_STS_ERROR THEN
981 RAISE G_EXCEPTION_ERROR;
982 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
983 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
984 END IF;
985
986 /* public api to update formulae *
987 okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
988 p_init_msg_list => p_init_msg_list,
989 x_return_status => l_return_status,
990 x_msg_count => x_msg_count,
991 x_msg_data => x_msg_data,
992 p_styv_rec => l_upd_styv_rec,
993 x_styv_rec => x_styv_rec);
994 IF l_return_status = G_RET_STS_ERROR THEN
995 RAISE G_EXCEPTION_ERROR;
996 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
997 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
998 END IF;
999
1000 /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record *
1001 ELSIF l_action = '3' THEN
1002
1003 -- mvasudev -- 02/17/2002
1004 -- DO NOT Update Old-record if new Start_Date is after Old End_Date
1005 IF l_upd_styv_rec.start_date <> G_MISS_DATE
1006 AND l_db_styv_rec.end_date IS NOT NULL
1007 AND l_upd_styv_rec.start_date > l_db_styv_rec.end_date
1008 THEN
1009 -- determine_action() updated on 04/11/2002 never yields this scenario
1010 NULL;
1011 ELSE
1012 /* for old version *
1013 IF l_upd_styv_rec.start_date <> G_MISS_DATE THEN
1014 l_oldversion_enddate := l_upd_styv_rec.start_date - 1;
1015 ELSE
1016 --mvasudev , 02/17/2002
1017 -- The earliest end_date, if changed , can be TODAY.
1018
1019 --l_oldversion_enddate := l_sysdate - 1;
1020 l_oldversion_enddate := l_sysdate;
1021
1022 -- end, mvasudev -- 02/17/2002
1023 END IF;
1024
1025 l_styv_rec := l_db_styv_rec;
1026 l_styv_rec.end_date := l_oldversion_enddate;
1027
1028 /* call verify changes to update the database *
1029 IF l_oldversion_enddate > l_db_styv_rec.end_date THEN
1030 check_updates( p_styv_rec => l_styv_rec,
1031 x_return_status => l_return_status,
1032 x_msg_data => x_msg_data);
1033 IF l_return_status = G_RET_STS_ERROR THEN
1034 RAISE G_EXCEPTION_ERROR;
1035 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1036 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1037 END IF;
1038 END IF;
1039
1040 /* public api to update stream types *
1041 okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
1042 p_init_msg_list => p_init_msg_list,
1043 x_return_status => l_return_status,
1044 x_msg_count => x_msg_count,
1045 x_msg_data => x_msg_data,
1046 p_styv_rec => l_styv_rec,
1047 x_styv_rec => x_styv_rec);
1048
1049 IF l_return_status = G_RET_STS_ERROR THEN
1050 RAISE G_EXCEPTION_ERROR;
1051 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1052 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1053 END IF;
1054 END IF;
1055 -- end,mvasudev -- 02/17/2002
1056
1057 /* for new version *
1058 l_styv_rec := p_styv_rec;
1059 -- auto_update code with name
1060 l_styv_rec.CODE := l_styv_rec.NAME;
1061
1062 -- mvasudev , 02/17/2002
1063 -- The earliest START_DATE, when Update, can be TOMORROW only
1064 IF l_upd_styv_rec.start_date = G_MISS_DATE THEN
1065 --l_styv_rec.start_date := l_sysdate ;
1066 l_styv_rec.start_date := l_sysdate + 1 ;
1067 END IF;
1068
1069 l_attrib_tbl(1).attribute := 'CODE';
1070 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
1071 l_attrib_tbl(1).value := l_styv_rec.code;
1072
1073 okl_accounting_util.get_version(p_attrib_tbl => l_attrib_tbl,
1074 p_cur_version => l_styv_rec.version,
1075 p_end_date_attribute_name => 'END_DATE',
1076 p_end_date => l_styv_rec.end_date,
1077 p_view => 'OKL_STRM_TYPE_V',
1078 x_return_status => l_return_status,
1079 x_new_version => l_new_version);
1080 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1081 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1082 ELSE
1083 l_styv_rec.version := l_new_version;
1084 END IF;
1085
1086 l_styv_rec.id := G_MISS_NUM;
1087 /* call verify changes to update the database *
1088 IF l_styv_rec.end_date > l_db_styv_rec.end_date THEN
1089 check_updates( p_styv_rec => l_styv_rec,
1090 x_return_status => l_return_status,
1091 x_msg_data => x_msg_data);
1092 IF l_return_status = G_RET_STS_ERROR THEN
1093 RAISE G_EXCEPTION_ERROR;
1094 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1095 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1096 END IF;
1097 END IF;
1098
1099 /* public api to insert stream type *
1100 okl_strm_type_pub.insert_strm_type(p_api_version => p_api_version,
1101 p_init_msg_list => p_init_msg_list,
1102 x_return_status => l_return_status,
1103 x_msg_count => x_msg_count,
1104 x_msg_data => x_msg_data,
1105 p_styv_rec => l_styv_rec,
1106 x_styv_rec => x_styv_rec);
1107 IF l_return_status = G_RET_STS_ERROR THEN
1108 RAISE G_EXCEPTION_ERROR;
1109 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1110 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1111 END IF;
1112
1113 /* copy output to input structure to get the id *
1114 l_styv_rec := x_styv_rec;
1115
1116 END IF;
1117 *******************************************************************/
1118 -- end, 04/20/2002 , mvasudev
1119
1120 x_return_status := l_return_status;
1121
1122 EXCEPTION
1123 WHEN G_EXCEPTION_ERROR THEN
1124 x_return_status := G_RET_STS_ERROR;
1125 IF (l_okl_styv_pk_csr%ISOPEN) THEN
1126 CLOSE l_okl_styv_pk_csr;
1127 END IF;
1128 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1129 x_return_status := G_RET_STS_UNEXP_ERROR;
1130 IF (l_okl_styv_pk_csr%ISOPEN) THEN
1131 CLOSE l_okl_styv_pk_csr;
1132 END IF;
1133 WHEN OTHERS THEN
1134 -- store SQL error message on message stack for caller
1135 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1136 p_msg_name => G_UNEXPECTED_ERROR,
1137 p_token1 => G_SQLCODE_TOKEN,
1138 p_token1_value => SQLCODE,
1139 p_token2 => G_SQLERRM_TOKEN,
1140 p_token2_value => SQLERRM );
1141 -- notify caller of an UNEXPECTED error
1142 x_return_status := G_RET_STS_UNEXP_ERROR;
1143 IF (l_okl_styv_pk_csr%ISOPEN) THEN
1144 CLOSE l_okl_styv_pk_csr;
1145 END IF;
1146
1147 END update_stream_type;
1148
1149 PROCEDURE create_stream_type(
1150 p_api_version IN NUMBER,
1151 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1152 x_return_status OUT NOCOPY VARCHAR2,
1153 x_msg_count OUT NOCOPY NUMBER,
1154 x_msg_data OUT NOCOPY VARCHAR2,
1155 p_styv_tbl IN styv_tbl_type,
1156 x_styv_tbl OUT NOCOPY styv_tbl_type)
1157 IS
1158 l_api_name CONSTANT VARCHAR2(30) := 'create_stream_type_tbl';
1159 rec_num INTEGER := 0;
1160 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1161 l_api_version CONSTANT NUMBER := 1;
1162 BEGIN
1163
1164 FOR rec_num IN 1..p_styv_tbl.COUNT
1165 LOOP
1166 create_stream_type(
1167 p_api_version => p_api_version,
1168 p_init_msg_list => p_init_msg_list,
1169 x_return_status => l_return_status,
1170 x_msg_count => x_msg_count,
1171 x_msg_data => x_msg_data,
1172 p_styv_rec => p_styv_tbl(rec_num),
1173 x_styv_rec => x_styv_tbl(rec_num) );
1174 IF l_return_status = G_RET_STS_ERROR THEN
1175 RAISE G_EXCEPTION_ERROR;
1176 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1177 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1178 END IF;
1179 END LOOP;
1180 x_return_status := l_return_status;
1181 EXCEPTION
1182 WHEN G_EXCEPTION_ERROR THEN
1183 x_return_status := G_RET_STS_ERROR;
1184 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1185 x_return_status := G_RET_STS_UNEXP_ERROR;
1186 WHEN OTHERS THEN
1187 -- store SQL error message on message stack for caller
1188 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1189 p_msg_name => G_UNEXPECTED_ERROR,
1190 p_token1 => G_SQLCODE_TOKEN,
1191 p_token1_value => SQLCODE,
1192 p_token2 => G_SQLERRM_TOKEN,
1193 p_token2_value => SQLERRM );
1194 -- notify caller of an UNEXPECTED error
1195 x_return_status := G_RET_STS_UNEXP_ERROR;
1196
1197 END create_stream_type;
1198
1199
1200 PROCEDURE update_stream_type(
1201 p_api_version IN NUMBER,
1202 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1203 x_return_status OUT NOCOPY VARCHAR2,
1204 x_msg_count OUT NOCOPY NUMBER,
1205 x_msg_data OUT NOCOPY VARCHAR2,
1206 p_styv_tbl IN styv_tbl_type,
1207 x_styv_tbl OUT NOCOPY styv_tbl_type)
1208 IS
1209 l_api_name CONSTANT VARCHAR2(30) := 'update_stream_type_tbl';
1210 rec_num INTEGER := 0;
1211 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1212 l_api_version CONSTANT NUMBER := 1;
1213 BEGIN
1214
1215 FOR rec_num IN 1.. p_styv_tbl.COUNT
1216 LOOP
1217 update_stream_type(
1218 p_api_version => p_api_version,
1219 p_init_msg_list => p_init_msg_list,
1220 x_return_status => l_return_status,
1221 x_msg_count => x_msg_count,
1222 x_msg_data => x_msg_data,
1223 p_styv_rec => p_styv_tbl(rec_num),
1224 x_styv_rec => x_styv_tbl(rec_num) );
1225 IF l_return_status = G_RET_STS_ERROR THEN
1226 RAISE G_EXCEPTION_ERROR;
1227 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1228 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1229 END IF;
1230 END LOOP;
1231
1232 x_return_status := l_return_status;
1233 EXCEPTION
1234 WHEN G_EXCEPTION_ERROR THEN
1235 x_return_status := G_RET_STS_ERROR;
1236 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1237 x_return_status := G_RET_STS_UNEXP_ERROR;
1238 WHEN OTHERS THEN
1239 -- store SQL error message on message stack for caller
1240 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1241 p_msg_name => G_UNEXPECTED_ERROR,
1242 p_token1 => G_SQLCODE_TOKEN,
1243 p_token1_value => SQLCODE,
1244 p_token2 => G_SQLERRM_TOKEN,
1245 p_token2_value => SQLERRM );
1246 -- notify caller of an UNEXPECTED error
1247 x_return_status := G_RET_STS_UNEXP_ERROR;
1248
1249 END update_stream_type;
1250
1251 END Okl_Setup_Streamtypes_Pvt;