[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUP_STREAMTYPES_PVT
Source
1 PACKAGE BODY Okl_Setup_Streamtypes_Pvt AS
2 /* $Header: OKLRSMTB.pls 120.3 2005/10/30 04:38:06 appldev noship $ */
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 l_styv_rec.CODE := l_styv_rec.NAME;
781
782 -- mvasudev, 04/20/2002
783
784 -- END_DATE needs to be after START_DATE (sanity check)
785 -- and Cannot be less than SysDate
786 /*
787 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 start
788 ** Changed to_date(to_char()) to trunc() for date comparisions.
789 */
790 IF l_styv_rec.end_date IS NOT NULL
791 AND l_styv_rec.end_date <> G_MISS_DATE
792 AND
793 (TRUNC(l_styv_rec.end_date) < TRUNC(l_styv_rec.start_date)
794 OR TRUNC(l_styv_rec.end_date) < l_sysdate
795 )
796 THEN
797 /*
798 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 end
799 */
800 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
801 p_msg_name => G_INVALID_VALUE,
802 p_token1 => G_COL_NAME_TOKEN,
803 p_token1_value => 'END_DATE' );
804 RAISE G_EXCEPTION_ERROR;
805 END IF;
806
807 -- Get current database values
808 OPEN l_okl_styv_pk_csr (p_styv_rec.id);
809 FETCH l_okl_styv_pk_csr INTO
810 l_db_styv_rec.START_DATE,
811 l_db_styv_rec.END_DATE;
812 l_no_data_found := l_okl_styv_pk_csr%NOTFOUND;
813 CLOSE l_okl_styv_pk_csr;
814
815 IF l_no_data_found THEN
816 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
817 END IF;
818
819 /*
820 -- mvasudev COMMENTED , 06/13/2002
821 -- Start-Date cannot be CHANGED for records that have already started being effective
822 -- Neither Can the new Start_Date be in the Past
823 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')
824 AND
825 ( to_date(to_char(l_db_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate
826 OR to_date(to_char(l_styv_rec.start_date,'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate
827 )
828 THEN
829 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
830 p_msg_name => G_INVALID_VALUE,
831 p_token1 => G_COL_NAME_TOKEN,
832 p_token1_value => 'START_DATE' );
833 RAISE G_EXCEPTION_ERROR;
834 END IF;
835 */
836
837 -- start date can not be greater than old start date if the record is active
838 /*
839 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 start
840 ** Changed to_date(to_char()) to trunc() for date comparisions.
841 */
842 IF TRUNC(l_db_styv_rec.start_date) < l_sysdate
843 AND TRUNC(l_styv_rec.start_date) > TRUNC(l_db_styv_rec.start_date)
844 THEN
845 /*
846 ** 25-Oct-2004 vthiruva -- Fix for Bug#3731453 end
847 */
848 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
849 p_msg_name => G_INVALID_VALUE,
850 p_token1 => G_COL_NAME_TOKEN,
851 p_token1_value => 'START_DATE' );
852 RAISE G_EXCEPTION_ERROR;
853 END IF;
854
855
856 -- public api to update_stream_type
857 OKL_STRM_TYPE_PUB.update_strm_type(p_api_version => p_api_version,
858 p_init_msg_list => p_init_msg_list,
859 x_return_status => l_return_status,
860 x_msg_count => x_msg_count,
861 x_msg_data => x_msg_data,
862 p_styv_rec => l_styv_rec,
863 x_styv_rec => x_styv_rec);
864 IF l_return_status = G_RET_STS_ERROR THEN
865 RAISE G_EXCEPTION_ERROR;
866 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
867 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
868 END IF;
869
870 /*******************************************************************
871 * FOLLOWING CODE COMMENTED TO DISABLE MULTIPLE VERSIONING
872 * Apr-20-2002, mvasudev
873 *
874 -- mvasudev -- 02/17/2002
875 -- END_DATE needs to be after START_DATE (sanity check)
876 IF l_styv_rec.end_date IS NOT NULL
877 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')
878 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')
879 THEN
880 OKC_API.SET_MESSAGE( p_app_name => OKC_API.G_APP_NAME,
881 p_msg_name => G_INVALID_VALUE,
882 p_token1 => G_COL_NAME_TOKEN,
883 p_token1_value => 'END_DATE' );
884 END IF;
885 -- end, mvasudev -- 02/17/2002
886
887 -- fetch old details from the database *
888 get_rec(p_styv_rec => l_styv_rec,
889 x_return_status => l_return_status,
890 x_no_data_found => l_no_data_found,
891 x_styv_rec => l_db_styv_rec);
892 IF l_return_status <> G_RET_STS_SUCCESS OR
893 l_no_data_found = TRUE THEN
894 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
895 END IF;
896
897 /* check for the records if start and end dates are in the past *
898 IF to_date(to_char(l_db_styv_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate AND
899 to_date(to_char(l_db_styv_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
900 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
901 p_msg_name => G_PAST_RECORDS);
902 RAISE G_EXCEPTION_ERROR;
903 END IF;
904
905
906 /* retain the details that has been changed only *
907 get_changes_only(p_styv_rec => p_styv_rec,
908 p_db_rec => l_db_styv_rec,
909 x_styv_rec => l_upd_styv_rec);
910
911 /* mvasudev, 02/17/2002
912
913 -- check for start date greater than sysdate
914 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
915 to_date(to_char(l_upd_styv_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
916 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
917 p_msg_name => G_START_DATE);
918 RAISE G_EXCEPTION_ERROR;
919 END IF;
920
921 -- check for end date greater than sysdate
922 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
923 to_date(to_char(l_upd_styv_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
924 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
925 p_msg_name => G_END_DATE);
926 RAISE G_EXCEPTION_ERROR;
927 END IF;
928
929 *
930
931 -- START_DATE , if changed, can only be later than TODAY
932 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
933 to_date(to_char(l_upd_styv_rec.start_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= l_sysdate THEN
934 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
935 p_msg_name => G_START_DATE);
936 RAISE G_EXCEPTION_ERROR;
937 END IF;
938
939 -- END_DATE, if changed, cannot be earlier than TODAY
940 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
941 to_date(to_char(l_upd_styv_rec.end_date,'DD/MM/YYYY'),'DD/MM/YYYY') < l_sysdate THEN
942 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
943 p_msg_name => G_END_DATE);
944 RAISE G_EXCEPTION_ERROR;
945 END IF;
946
947 -- end, mvasudev -- 02/17/2002
948
949 /* determine how the processing to be done *
950 l_action := determine_action(p_upd_styv_rec => l_upd_styv_rec,
951 p_db_styv_rec => l_db_styv_rec,
952 p_date => l_sysdate);
953
954 /* Scenario 1: The Changed Field-Values can by-pass Validation *
955 IF l_action = '1' THEN
956 /* public api to update_stream_type *
957 okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
958 p_init_msg_list => p_init_msg_list,
959 x_return_status => l_return_status,
960 x_msg_count => x_msg_count,
961 x_msg_data => x_msg_data,
962 p_styv_rec => l_upd_styv_rec,
963 x_styv_rec => x_styv_rec);
964 IF l_return_status = G_RET_STS_ERROR THEN
965 RAISE G_EXCEPTION_ERROR;
966 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
967 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
968 END IF;
969
970 /* Scenario 2: The Changed Field-Values include that needs Validation and Update *
971 ELSIF l_action = '2' THEN
972 check_updates( p_styv_rec => l_styv_rec,
973 x_return_status => l_return_status,
974 x_msg_data => x_msg_data);
975
976 IF l_return_status = G_RET_STS_ERROR THEN
977 RAISE G_EXCEPTION_ERROR;
978 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
979 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
980 END IF;
981
982 /* public api to update formulae *
983 okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
984 p_init_msg_list => p_init_msg_list,
985 x_return_status => l_return_status,
986 x_msg_count => x_msg_count,
987 x_msg_data => x_msg_data,
988 p_styv_rec => l_upd_styv_rec,
989 x_styv_rec => x_styv_rec);
990 IF l_return_status = G_RET_STS_ERROR THEN
991 RAISE G_EXCEPTION_ERROR;
992 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
993 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
994 END IF;
995
996 /* Scenario 3: The Changed Field-Values mandate Creation of a New Version/Record *
997 ELSIF l_action = '3' THEN
998
999 -- mvasudev -- 02/17/2002
1000 -- DO NOT Update Old-record if new Start_Date is after Old End_Date
1001 IF l_upd_styv_rec.start_date <> G_MISS_DATE
1002 AND l_db_styv_rec.end_date IS NOT NULL
1003 AND l_upd_styv_rec.start_date > l_db_styv_rec.end_date
1004 THEN
1005 -- determine_action() updated on 04/11/2002 never yields this scenario
1006 NULL;
1007 ELSE
1008 /* for old version *
1009 IF l_upd_styv_rec.start_date <> G_MISS_DATE THEN
1010 l_oldversion_enddate := l_upd_styv_rec.start_date - 1;
1011 ELSE
1012 --mvasudev , 02/17/2002
1013 -- The earliest end_date, if changed , can be TODAY.
1014
1015 --l_oldversion_enddate := l_sysdate - 1;
1016 l_oldversion_enddate := l_sysdate;
1017
1018 -- end, mvasudev -- 02/17/2002
1019 END IF;
1020
1021 l_styv_rec := l_db_styv_rec;
1022 l_styv_rec.end_date := l_oldversion_enddate;
1023
1024 /* call verify changes to update the database *
1025 IF l_oldversion_enddate > l_db_styv_rec.end_date THEN
1026 check_updates( p_styv_rec => l_styv_rec,
1027 x_return_status => l_return_status,
1028 x_msg_data => x_msg_data);
1029 IF l_return_status = G_RET_STS_ERROR THEN
1030 RAISE G_EXCEPTION_ERROR;
1031 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1032 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1033 END IF;
1034 END IF;
1035
1036 /* public api to update stream types *
1037 okl_strm_type_pub.update_strm_type(p_api_version => p_api_version,
1038 p_init_msg_list => p_init_msg_list,
1039 x_return_status => l_return_status,
1040 x_msg_count => x_msg_count,
1041 x_msg_data => x_msg_data,
1042 p_styv_rec => l_styv_rec,
1043 x_styv_rec => x_styv_rec);
1044
1045 IF l_return_status = G_RET_STS_ERROR THEN
1046 RAISE G_EXCEPTION_ERROR;
1047 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1048 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1049 END IF;
1050 END IF;
1051 -- end,mvasudev -- 02/17/2002
1052
1053 /* for new version *
1054 l_styv_rec := p_styv_rec;
1055 -- auto_update code with name
1056 l_styv_rec.CODE := l_styv_rec.NAME;
1057
1058 -- mvasudev , 02/17/2002
1059 -- The earliest START_DATE, when Update, can be TOMORROW only
1060 IF l_upd_styv_rec.start_date = G_MISS_DATE THEN
1061 --l_styv_rec.start_date := l_sysdate ;
1062 l_styv_rec.start_date := l_sysdate + 1 ;
1063 END IF;
1064
1065 l_attrib_tbl(1).attribute := 'CODE';
1066 l_attrib_tbl(1).attrib_type := okl_accounting_util.G_VARCHAR2;
1067 l_attrib_tbl(1).value := l_styv_rec.code;
1068
1069 okl_accounting_util.get_version(p_attrib_tbl => l_attrib_tbl,
1070 p_cur_version => l_styv_rec.version,
1071 p_end_date_attribute_name => 'END_DATE',
1072 p_end_date => l_styv_rec.end_date,
1073 p_view => 'OKL_STRM_TYPE_V',
1074 x_return_status => l_return_status,
1075 x_new_version => l_new_version);
1076 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1077 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1078 ELSE
1079 l_styv_rec.version := l_new_version;
1080 END IF;
1081
1082 l_styv_rec.id := G_MISS_NUM;
1083 /* call verify changes to update the database *
1084 IF l_styv_rec.end_date > l_db_styv_rec.end_date THEN
1085 check_updates( p_styv_rec => l_styv_rec,
1086 x_return_status => l_return_status,
1087 x_msg_data => x_msg_data);
1088 IF l_return_status = G_RET_STS_ERROR THEN
1089 RAISE G_EXCEPTION_ERROR;
1090 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1091 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1092 END IF;
1093 END IF;
1094
1095 /* public api to insert stream type *
1096 okl_strm_type_pub.insert_strm_type(p_api_version => p_api_version,
1097 p_init_msg_list => p_init_msg_list,
1098 x_return_status => l_return_status,
1099 x_msg_count => x_msg_count,
1100 x_msg_data => x_msg_data,
1101 p_styv_rec => l_styv_rec,
1102 x_styv_rec => x_styv_rec);
1103 IF l_return_status = G_RET_STS_ERROR THEN
1104 RAISE G_EXCEPTION_ERROR;
1105 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1106 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1107 END IF;
1108
1109 /* copy output to input structure to get the id *
1110 l_styv_rec := x_styv_rec;
1111
1112 END IF;
1113 *******************************************************************/
1114 -- end, 04/20/2002 , mvasudev
1115
1116 x_return_status := l_return_status;
1117
1118 EXCEPTION
1119 WHEN G_EXCEPTION_ERROR THEN
1120 x_return_status := G_RET_STS_ERROR;
1121 IF (l_okl_styv_pk_csr%ISOPEN) THEN
1122 CLOSE l_okl_styv_pk_csr;
1123 END IF;
1124 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1125 x_return_status := G_RET_STS_UNEXP_ERROR;
1126 IF (l_okl_styv_pk_csr%ISOPEN) THEN
1127 CLOSE l_okl_styv_pk_csr;
1128 END IF;
1129 WHEN OTHERS THEN
1130 -- store SQL error message on message stack for caller
1131 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1132 p_msg_name => G_UNEXPECTED_ERROR,
1133 p_token1 => G_SQLCODE_TOKEN,
1134 p_token1_value => SQLCODE,
1135 p_token2 => G_SQLERRM_TOKEN,
1136 p_token2_value => SQLERRM );
1137 -- notify caller of an UNEXPECTED error
1138 x_return_status := G_RET_STS_UNEXP_ERROR;
1139 IF (l_okl_styv_pk_csr%ISOPEN) THEN
1140 CLOSE l_okl_styv_pk_csr;
1141 END IF;
1142
1143 END update_stream_type;
1144
1145 PROCEDURE create_stream_type(
1146 p_api_version IN NUMBER,
1147 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1148 x_return_status OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER,
1150 x_msg_data OUT NOCOPY VARCHAR2,
1151 p_styv_tbl IN styv_tbl_type,
1152 x_styv_tbl OUT NOCOPY styv_tbl_type)
1153 IS
1154 l_api_name CONSTANT VARCHAR2(30) := 'create_stream_type_tbl';
1155 rec_num INTEGER := 0;
1156 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1157 l_api_version CONSTANT NUMBER := 1;
1158 BEGIN
1159
1160 FOR rec_num IN 1..p_styv_tbl.COUNT
1161 LOOP
1162 create_stream_type(
1163 p_api_version => p_api_version,
1164 p_init_msg_list => p_init_msg_list,
1165 x_return_status => l_return_status,
1166 x_msg_count => x_msg_count,
1167 x_msg_data => x_msg_data,
1168 p_styv_rec => p_styv_tbl(rec_num),
1169 x_styv_rec => x_styv_tbl(rec_num) );
1170 IF l_return_status = G_RET_STS_ERROR THEN
1171 RAISE G_EXCEPTION_ERROR;
1172 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1173 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1174 END IF;
1175 END LOOP;
1176 x_return_status := l_return_status;
1177 EXCEPTION
1178 WHEN G_EXCEPTION_ERROR THEN
1179 x_return_status := G_RET_STS_ERROR;
1180 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1181 x_return_status := G_RET_STS_UNEXP_ERROR;
1182 WHEN OTHERS THEN
1183 -- store SQL error message on message stack for caller
1184 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1185 p_msg_name => G_UNEXPECTED_ERROR,
1186 p_token1 => G_SQLCODE_TOKEN,
1187 p_token1_value => SQLCODE,
1188 p_token2 => G_SQLERRM_TOKEN,
1189 p_token2_value => SQLERRM );
1190 -- notify caller of an UNEXPECTED error
1191 x_return_status := G_RET_STS_UNEXP_ERROR;
1192
1193 END create_stream_type;
1194
1195
1196 PROCEDURE update_stream_type(
1197 p_api_version IN NUMBER,
1198 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1199 x_return_status OUT NOCOPY VARCHAR2,
1200 x_msg_count OUT NOCOPY NUMBER,
1201 x_msg_data OUT NOCOPY VARCHAR2,
1202 p_styv_tbl IN styv_tbl_type,
1203 x_styv_tbl OUT NOCOPY styv_tbl_type)
1204 IS
1205 l_api_name CONSTANT VARCHAR2(30) := 'update_stream_type_tbl';
1206 rec_num INTEGER := 0;
1207 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1208 l_api_version CONSTANT NUMBER := 1;
1209 BEGIN
1210
1211 FOR rec_num IN 1.. p_styv_tbl.COUNT
1212 LOOP
1213 update_stream_type(
1214 p_api_version => p_api_version,
1215 p_init_msg_list => p_init_msg_list,
1216 x_return_status => l_return_status,
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data,
1219 p_styv_rec => p_styv_tbl(rec_num),
1220 x_styv_rec => x_styv_tbl(rec_num) );
1221 IF l_return_status = G_RET_STS_ERROR THEN
1222 RAISE G_EXCEPTION_ERROR;
1223 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1224 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
1225 END IF;
1226 END LOOP;
1227
1228 x_return_status := l_return_status;
1229 EXCEPTION
1230 WHEN G_EXCEPTION_ERROR THEN
1231 x_return_status := G_RET_STS_ERROR;
1232 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
1233 x_return_status := G_RET_STS_UNEXP_ERROR;
1234 WHEN OTHERS THEN
1235 -- store SQL error message on message stack for caller
1236 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1237 p_msg_name => G_UNEXPECTED_ERROR,
1238 p_token1 => G_SQLCODE_TOKEN,
1239 p_token1_value => SQLCODE,
1240 p_token2 => G_SQLERRM_TOKEN,
1241 p_token2_value => SQLERRM );
1242 -- notify caller of an UNEXPECTED error
1243 x_return_status := G_RET_STS_UNEXP_ERROR;
1244
1245 END update_stream_type;
1246
1247 END Okl_Setup_Streamtypes_Pvt;